BOM展开2

;WITH BOM (Father,ChildCode,BomQty,iLevel)
AS
( SELECT T0.Code Father,
T1.Code ChildCode,
CAST(T1.Quantity/T0.Qauntity AS DECIMAL(19,4)) BomQty,
1 iLevel
FROM OITT T0
JOIN ITT1 T1 ON T0.Code =T1.Father
UNION ALL
SELECT U0.Father,
T2.Code ,
CAST(U0.BomQty(T2.Quantity/T1.Qauntity) AS DECIMAL(19,4)),
U0.iLevel+1
FROM BOM U0
JOIN OITT T1 ON U0.ChildCode=T1.Code
JOIN ITT1 T2 ON T1.Code =T2.Father
)
SELECT T0.
FROM BOM T0
JOIN OITM T1 ON T0.Father=T1.ItemCode
JOIN OITB T2 ON T2.ItmsGrpCod =T1.ItmsGrpCod
WHERE T2.U_ItemType ='1406'