MSSQL2000多版本BOM表计算产品成本(bom表重复使用到该半成品(同版本号)) (可以升级为SQL2005)
该BOM表采用主、从表设计，当主、从表的BILLID相同时则表示同一阶BOM，
BOM表可能有多阶的，这里写出的数据比较简单，希望能循环计算出多阶BOM单价，
单价从最下阶开始往上累加计算得出。
注意: (数据库可以升级为SQL2005)
可参考如下网址:http://bbs.csdn.net/topics/350119105
http://bbs.csdn.net/topics/350207921
主表: M_BOM
单据ID ， 母件 ， 数量 ， 版本号
BILLID , GOODSID , QTY , BOMVER
1 , A , 1 , A
2 , D , 1 , D
3 , D , 1 , D2
4 , A , 1 , A2
细表: M_BOMD
单据ID ，子件序号, 子件 , 数量 ，关联主表BILLID
BILLID , ITEMNO ,GOODSID , QTY , BOMID
1 , 1 , B , 1 , NULL
1 , 2 , C , 1 , NULL
1 , 3 , D , 1 , 2 ---这个关联主表M_BOM中BILLID=2
1 , 4 , D , 1 , 2 -----2014-10-07加入此部份,物料清单中重复使用半成品(同一版本号)
2 , 1 , E , 1 , NULL
2 , 2 , F , 1 , NULL
3 , 1 , E , 1 , NULL
3 , 2 , F , 2 , NULL
4 , 1 , B , 1 , NULL
4 , 2 , C , 1 , NULL
4 , 3 , D , 1 , 3 ---这个关联主表M_BOM中BILLID=3
货品单价表 GOODS
货品 , 单价
GOODSID , PRICE
A , 1
B , 1
C , 1
D , 1
E , 1
F , 1
求函数计算出如下结果：
母件 ,数量 , 版本号 , 序号 , 子件 , 子数 , 子版本名 , 单价 , 金额
MGOODIS ,MQTY , BOMVER , ITEMNO , GOODSID , QTY , BOMIDNAME , PRICE , AM
A , 1 , A , 1 , B , 1 , NULL , 1 , 1
A , 1 , A , 2 , C , 1 , NULL , 1 , 1
A , 1 , A , 3 , D , 1 , D , 2 , 2
A , 1 , A , 4 , D , 1 , D , 2 , 2
A , 1 , A2 , 1 , B , 1 , NULL , 1 , 1
A , 1 , A2 , 2 , C , 1 , NULL , 1 , 1
A , 1 , A2 , 3 , D , 1 , D2 , 3 , 3
---以下是coleling兄回复 ,但现在2014-10-07加入此部份,物料清单中重复使用半成品(同一版本号) 就有点小问题,计算该半成品(多版本号的)的单价不对.
if object_id('GetBom','TF') is not null
drop function GetBom
go
create function GetBom()
returns @tb table(MGoodsId Varchar(10), MQty int, BomVer Varchar(10), ItemNo int, GoodsId Varchar(10), Qty int, BomIdName Varchar(10), Price int, AM int)
AS
begin
declare @i int
set @i = 1
declare @t table(id int,BillId int, MGoodsId Varchar(10), MQty int, BomVer Varchar(10), ItemNo int, GoodsId Varchar(10), Qty int, BomId int, BomIdName Varchar(10), Price int, AM int, LevelId int)
insert @t
select a.BillId,a.BillId,a.GoodsId, a.Qty, a.BomVer, b.ItemNo, b.GoodsId, b.Qty, b.BomId, c.BOMVER AS BomIdName, d.Price, b.Qty*d.Price AS AM, @i
from M_BOM a
join M_BOMD b ON a.BillId = b.BillId
left join M_BOM c ON b.BomId = c.BillId
join GOODS d ON b.GoodsId = d.GoodsId
while @i0
begin
set @i = @i + 1
insert @t
select a.Id,b.BillId, a.BomIdName, a.Qty, a.BomIdName, b.ItemNo, b.GoodsId, b.Qty, b.BomId, c.BOMVER AS BomIdName, d.Price, b.Qty*d.Price AS AM, @i
from @t a
join M_BOMD b ON a.BomId = b.BillId
left join M_BOM c ON b.BomId = c.BillId
join GOODS d ON b.GoodsId = d.GoodsId
where a.BomIdName IS NOT NULL AND a.LevelId = @i-1
end
select @i = max(levelid) from @t
while @i >= 2
begin
update a set a.price = b.total, a.AM = a.Qty*b.total from @t a join (select id,BillId, sum(Qty*Price) as total from @t where levelid = @i group by id,BillId) b on a.BomId = b.BillId and a.id = b.id where a.levelid = @i-1
set @i = @i - 1
end
insert @tb(MGoodsId, MQty, BomVer, ItemNo, GoodsId, Qty, BomIdName, Price, AM)
select MGoodsId, MQty, BomVer, ItemNo, GoodsId, Qty, BomIdName, Price, AM
from @t
where levelid = 1
order by billid, itemno
return
end
go
select * from dbo.getbom() where mgoodsid = 'x' |
