WITH BOM(Father,ItemCode,Quantity,iLevel)

AS

(  

SELECT T0.Code,T1.Code ,CONVERT(NUMERIC(23,15), T1.Quantity/T0.Qauntity) Quantity , 1 iLevel

FROM OITT T0

     JOIN ITT1 T1 ON T0.Code = T1.Father

UNION ALL

SELECT T0.Father,T2.Code,CONVERT(NUMERIC(23,15),(T2.Quantity/T1.Qauntity)*T0.Quantity) ,T0.iLevel+1

FROM BOM T0

      JOIN OITT T1 ON T0.ItemCode = T1.Code

      JOIN ITT1 T2 ON T1.Code = T2.Father

)

SELECT T1.SlpName 业务员,T1.CardCode 客户代码,T1.CardName 客户名称,T1.DocEntry 单号,T1.NumAtCard 业务订单号,

       T1.ItemCode 成品编号,T1.Dscription 产品描述,T1.SubcatNum 客户件号,MAX(T1.Quantity) 订单量,MAX(T1.DvlQty)已发货量,

       MAX(T1.OpenCreQty) 缺货量,T2.U_Buyer 采购员,T3.CardName 供应商名称,

       CASE WHEN T2.TaxCtg='CG01' THEN '外购' 

               WHEN  T2.TaxCtg='WX01' THEN '委外'

               WHEN  T2.TaxCtg='ZP01' THEN '装配'

               WHEN  T2.TaxCtg='PG01' THEN '抛光'

               WHEN  T2.TaxCtg='JJ01' THEN '机加'

               WHEN  T2.TaxCtg='YZ01' THEN '压铸'

               WHEN  T2.TaxCtg='ZS01' THEN '注塑'

                ELSE '其它' END 属性 ,

      T0.ItemCode 物料编号,T2.ItemName 物料描述,CEILING(MAX(T1.OpenCreQty) *MAX(T0.quantity)) 生产需求量

 FROM (

      SELECT T0.[DocEntry], T2.[SlpName], T1.[LineStatus],T1.[LineNum], T0.[CardCode], T0.[CardName], 

                 T0.[NumAtCard], T1.[ItemCode], T1.[Dscription], T1.[SubCatNum], T1.[Quantity], T1.Quantity-T1.OpenCreQty DvlQty,

             T1.[OpenCreQty], T1.[ShipDate]

             FROM [dbo].ORDR T0 JOIN RDR1 T1 ON T1.DocEntry=T0.DocEntry JOIN OSLP T2 ON T2.SlpCode=T0.SlpCode

             WHERE T1.LineStatus='O'

        ) T1 

    JOIN BOM T0 ON  T1.ItemCode =T0.father  

    JOIN OITM T2 ON T0.ItemCode=T2.ItemCode

    LEFT JOIN OCRD T3 ON T3.cardcode=T2.cardcode

WHERE  T0.ItemCode NOT LIKE '5%' 

GROUP BY T1.SlpName,T1.CardCode,T1.CardName,T1.DocEntry,T1.NumAtCard,T1.ItemCode,T1.Dscription ,

         T1.SubcatNum,T2.U_Buyer,T3.CardName,T0.Itemcode,T2.ItemName,T2.TaxCtg

ORDER BY T1.SlpName