bom成本计算(多版本号) 计算由底层逐层向上计算半成品单价 求函数
BOM多版本 计算产品成本(注:bom表重复使用到该半成品(同版本号)) 求函数
(环境使用SQL2000或SQL2005)
表结构
1/BOM表采用主(m_bom)、从表(m_bomd)设计，当主、从表的billid相同时则表示同一阶BOM，
2/BOM表有多阶的最多不超10阶，下面列出的数据比较简单，希望能循环计算出多阶BOM单价，
3/单价从最低阶层开始往上计算累计得出(半成品)。
由最下层的物料清单开始向上计算,计算出半成品,再由半成品计算出再上一层的半成品.....如此累推,
主表: 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 即D是由E*1和F*1组成
1 , 4 , D , 1 , 2 -----物料清单中重复使用半成品(同一版本号)
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 即D是由E*1和F*2组成
物料单价表 goodsunit (m_bomd.goodsid=goodsunit.goodsid)
物料 , 单价
goodsid , price
A , 1
B , 1
C , 1
D , 1 ----参考单价
E , 1
F , 1
求函数计算出如下结果：(无参函数)
父件 ,父件数量 , 父版本号 , 序号 , 子件 , 子件数量 , 子版本名 , 单价 , 金额
mgoodsid ,mqty , bomver , itemno , goodsid , qty , bomidname , price , amount
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 ---计算出半成品的单价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 ---计算出半成品D的版本号D2的单价3
------解决思路----------------------
我倒不图这个，前段时间我也有点忙
下面，试试，我这边没有数据，还没测试过，如果有什么问题，一定要指清楚，方便我调整
CREATE FUNCTION FUNNAME(@mgoodsid char(32),@fromdate datetime,@todate datetime)
RETURNS TABLE
AS
RETURN
WITH CTEm_bomd AS(
SELECT billid,itemno,goodsid,qty,bomid FROM m_bomd WHERE goodsid=@mgoodsid
UNION ALL
SELECT T1.billid,T1.itemno,T1.goodsid,T1.qty,T1.bomid
FROM m_bomd T1 JOIN CTEm_bomd T2 ON T2.bomid=T1.billid
)
,TBm_bomd AS(
SELECT T1.billid,T1.itemno,T1.goodsid,T1.qty,T1.bomid,T2.price
FROM CTEm_bomd T1
JOIN goodsunit T2 ON T1.goodsid=T2.goodsid
WHERE T2.billdate BETWEEN @fromdate AND @todate
)
,CTE AS(
SELECT T2.billid,T2.itemno,T2.goodsid,T2.qty,T2.bomid,CAST(CAST(T1.price AS DECIMAL(19,6))*T1.qty/T3.qty AS DECIMAL(19,6))price
--,T1.qty,T1.price,T3.qty
FROM TBm_bomd T1
JOIN TBm_bomd T2 ON T1.billid=T2.bomid
JOIN m_bom T3 ON T2.billid=T3.billid
WHERE T1.bomid IS NULL
UNION ALL
SELECT T2.billid,T2.itemno,T2.goodsid,T2.qty,T2.bomid,CAST(T1.price*T1.qty/T3.qty AS DECIMAL(19,6))
FROM CTE T1
JOIN TBm_bomd T2 ON T1.billid=T2.bomid
JOIN m_bom T3 ON T2.billid=T3.billid
)
,TB AS(
SELECT * FROM CTEm_bomd
WHERE bomid IS NULL
UNION ALL
SELECT billid,itemno,goodsid,qty,bomid,SUM(price)price FROM CTE
GROUP BY billid,itemno,goodsid,qty,bomid
)
SELECT T1.goodsid mgoodsid
,T1.qty mqty
,T1.bomver
,T2.itemno
,T2.goodsid
,T2.qty
,T3.bomver bomidname
,T2.price
,T2.qty*T2.price amount
FROM m_bom T1
JOIN TB T2 ON T1.billid=T2.billid
LEFT JOIN m_bom T3 ON T2.bomid=T3.billid
GO