请热心的高手们帮忙(多版本BOM展开毛需求件) ,非常感谢!!
BOM表是多版本的,请注意.希望展开后我只需查看视图即可.
环境：MSSQL2000
订单毛需求件分别全部展开(BOM多版本号)
vip_sorderdetail -----来原视图，如何传到函数中？？？
物料ID， 产品数量，版本号
materilaid , quantity , bomno
A , 3000 , A
B , 4000 , BB
物料清单 视图 vip_bom
billid, materialid,bomno,quantity, childitemno, childmaterialid, childquantity, childbomno
1001 , A , AA , 1 , 1 , B , 1 , BB --这里表示物料B还有下阶清单，版本号是BB
1001 , A , AA , 1 , 2 , C , 1 , ' '
1001 , A , AA , 1 , 3 , D , 1 , ' '
1002 , B , BB , 1, 1 , E , 1 , ' '
1002 , B , BB , 1, 2 , F , 2 , FF ---FF是版本
1003 , F , FF , 1, 1 , G , 1 , ' '
1003 , F , FF , 1, 2 , H , 1 , ' '
1004 , A , AK , 1 , 1 , J , 1 , ' '---产品A有多个版本
1004 , A , AK , 1 , 2 , K , 1 , ' '
要求结果如下: 根据订单展开毛需求
1.以vip_sorderdetail视图作为源头数据计算
2.按视图 vip_bom全部展开所有物料(包括所有阶的物料)
3.格式要求如下: (材料合计数量=材料标准用量*订单数量)
产品ID, 产品版本号, 产品数量, 材料id， 标准用量， 材料版本号 , 订单数量 , 材料合计数量 ,阶层
materialid,bomno, quantity, childmaterialid, childquantity, childbomno , vip_sorderdetailquqntity ,sumquantity ,Level
以下是其它网上查询到的
结果类似 如下，但也有些很大的区别
--BOM算法
--产品配件清单查询示例(邹建)
CREATE TABLE Item(ID int,Name varchar(10),Wast decimal(2,2))
INSERT Item SELECT 1,N'A产品',0.01
UNION ALL SELECT 2,N'B产品',0.02
UNION ALL SELECT 3,N'C产品',0.10
UNION ALL SELECT 4,N'D配件',0.15
UNION ALL SELECT 5,N'E物料',0.03
UNION ALL SELECT 6,N'F物料',0.01
UNION ALL SELECT 7,N'G配件',0.02
go
CREATE TABLE Bom(ItemID int,ChildId int)
INSERT Bom SELECT 1,4
UNION ALL SELECT 1,7 --A产品由D配件和G配件组成
UNION ALL SELECT 2,1
UNION ALL SELECT 2,6
UNION ALL SELECT 2,7 --B产品由F物料及G配件组成
UNION ALL SELECT 4,5
UNION ALL SELECT 4,6 --D配件由F物料组成
UNION ALL SELECT 3,2
UNION ALL SELECT 3,1 --C产品由A产品和B产品组成
GO
CREATE FUNCTION f_Bom(
@ItemIDs varchar(1000), --要查询物料清单及生产量的产品编号列表(逗号分隔)
@Num int --要生产的数量
)RETURNS @t TABLE(ItemID int,ChildId int,Nums int,Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t SELECT a.ItemID,a.ChildId,ROUND(@Num/(1-b.Wast),0),@Level
FROM Bom a,Item b ---物料
WHERE a.ChildId=b.ID ---材料编码=物料编码
AND CHARINDEX(','+RTRIM(a.ItemID)+',',',[email protected]+',')>0
WHILE @@ROWCOUNT>0 and @Level<140
BEGIN
SET @[email protected]+1
INSERT @t SELECT a.ItemID,b.ChildId,ROUND(a.Nums/(1-c.Wast),0),@Level
FROM @t a,Bom b,Item c
WHERE a.ChildId=b.ItemID
AND b.ChildId=c.ID
AND [email protected]
END
RETURN
END
GO
SELECT a.ItemID,ItemName=b.Name,
a.ChildId,ChildName=c.Name,
a.Nums,a.Level
FROM f_Bom('1,2,3',10) a,Item b,Item c
WHERE a.ItemID=b.ID
AND a.ChildId=c.ID
ORDER BY a.ItemID,a.Level,a.ChildId
/*
ItemID ItemName ChildId ChildName Nums Level
----------- ---------- ----------- ---------- ----------- -----------
1 A产品 4 D配件 12 1
1 A产品 7 G配件 10 1
1 A产品 5 E物料 12 2
1 A产品 6 F物料 12 2
2 B产品 1 A产品 10 1
2 B产品 6 F物料 10 1
2 B产品 7 G配件 10 1
2 B产品 4 D配件 12 2
2 B产品 7 G配件 10 2
2 B产品 5 E物料 12 3
2 B产品 6 F物料 12 3
3 C产品 1 A产品 10 1
3 C产品 2 B产品 10 1
3 C产品 1 A产品 10 2
3 C产品 4 D配件 12 2
3 C产品 6 F物料 10 2
3 C产品 7 G配件 10 2
3 C产品 7 G配件 10 2
3 C产品 4 D配件 12 3
3 C产品 5 E物料 12 3
3 C产品 6 F物料 12 3
3 C产品 7 G配件 10 3
3 C产品 5 E物料 12 4
3 C产品 6 F物料 12 4
------解决方案--------------------
先把数据整理在一个临时表中，然后再递归。2000不能用公用表达式，可以用临时表替代。木有环境，不然可以试试
------解决方案--------------------
http://topic.csdn.net/u/20111129/14/1b3c1014-25eb-42c2-b293-e78cdaac737c.html