Bom中的实体物料与用量
在制造业BOM中存在一种“虚拟件”作为供应类型的应用。该部分应用主要用于实际不用于实际出入库的物料,在BOM中为了体现结构或其它应用的需要而设置的一种供应类型。
如下图BOM:
根据以上的情况,在实际生产订单或现场实体物料使用的BOM结构如下:
BOM的设计结构如下:
表结构为:
表名:
Bom
字段名称
字段属性
说明
P
Varchar(max)
父项
C
Varchar(max)
子项
Wt
Int
供应类型
Qty
int
用量
创建BOM
if OBJECT_ID('Bom') is not null
begin
drop tableBom
end
create table Bom
(
p varchar(max),
c varchar(max),
wt int,
qty float
)
insert into bom(p,c,wt,qty)values('A','A1',4,2)
insert into bom(p,c,wt,qty)values('A','A2',4,3)
insert into bom(p,c,wt,qty)values('A','A3',3,1)
insert into bom(p,c,wt,qty)values('A1','A11',3,2)
insert into bom(p,c,wt,qty)values('A1','A12',3,3)
insert into bom(p,c,wt,qty)values('A1','A13',3,1)
insert into bom(p,c,wt,qty)values('A2','A21',4,2)
insert into bom(p,c,wt,qty)values('A2','A22',3,3)
insert into bom(p,c,wt,qty)values('A2','A23',3,1)
insert into bom(p,c,wt,qty)values('A21','A211',3,2)
insert into bom(p,c,wt,qty)values('A21','A212',3,4)
表
拟算角本
IF OBJECT_ID('TEMPDB..#BOM') IS NOT NULL
BEGIN
DROP TABLE#BOM
END
SELECT P,C,wt,qty,0 AS L INTO #BOM FROM BOM WHERE 1=0 AND P='A'
INSERT INTO #BOM SELECT P,C,WT,QTY ,1 AS L FROM BOM WHERE P='A'
DECLARE @ID INT
SELECT @ID=COUNT( DISTINCT WT) FROM #BOM WHERE L IN(SELECT MAX(L) AS L FROM #BOM ) AND wt=4
WHILE @ID=1
BEGIN
INSERT INTO #BOM SELECT DISTINCT A.p,B.c,B.wt,B.qty*A.qty AS QTY,A.L+1 AS L FROM #BOM A INNER JOIN Bom B ON A.c=B.p INNER JOIN (SELECT * FROM(SELECT ROW_NUMBER()OVER(PARTITION BY C,WT,L ORDER BY C,WT,L DESC) AS ID,P,C,WT,L,QTY FROM #BOM WHERE wt=4)X WHERE ID=1)A1 ON A1.C=A1.C AND A.L=A1.L AND A.wt=A1.wt WHERE A.L IN(SELECT MAX(L) FROM #BOM)
SELECT @ID=COUNT( DISTINCT WT) FROM #BOM WHERE L IN(SELECT MAX(L) AS L FROM #BOM ) AND wt=4
END
SELECT * FROM #BOM WHERE WT=3 ORDER BY P,L
结果