-- ======================================

-- 程序：未清需求按BOM样式展开查看订单执行状态

-- wangtp

-- 2017.7.7

-- EXEC [U_P_STOCK2NEED_IN_MRP_TRACKING_EXPEND]

-- 1)5开头物料排除

-- 2)只体现未清工单

-- ======================================

ALTER PROCEDURE [dbo].[U_P_STOCK2NEED_IN_MRP_TRACKING_EXPEND]

AS

BEGIN

DECLARE @dueDate DATETIME

DECLARE @owe_line_count INT

DECLARE @level INT

DECLARE @maxLevel INT --需求计算的最大层级

DECLARE @doc INT --已跑MRP的最后一个销售订单

SELECT @doc = MAX(T1.DocEntry) 

FROM OWOR T0 

INNER JOIN ORDR T1 ON T0.PickRmrk = T1.NumAtCard

WHERE T0.Status IN ('P','R')

DECLARE @doc2 INT --已跑MRP的最后一个销售预测

SELECT @doc2 = MAX(T1.AbsId) 

FROM OWOR T0 

INNER JOIN OFCT T1 ON T0.PickRmrk = T1.Code

WHERE T0.Status IN ('P','R')

--截止日默认为下个月最后一天

SET @dueDate = '99991231'

;

--SELECT @end

--170706 未清销售订单和预测单 按BOM展开计算全部需求

WITH BOM2(NumAtCard,Father,ItemCode,Quantity,ShipDate,[LEVEL],Position)

AS

(

SELECT T10.NumAtCard,CAST('' AS NVARCHAR(20)), T10.[ItemCode]

,SUM(T10.Quantity) Quantity,MIN(T10.ShipDate) ShipDate,1 [LEVEL]

,CAST(1000+ROW_NUMBER() OVER(order by T10.NumAtCard,T10.[ItemCode]) AS VARCHAR(MAX))

--INTO #TEMP_OPEN_NEED

FROM 

(

SELECT T0.NumAtCard, T1.[ItemCode],T1.OpenCreQty Quantity ,T1.ShipDate

FROM [dbo].ORDR T0

INNER JOIN RDR1 T1 ON T1.DocEntry=T0.DocEntry AND T1.LineStatus='O'

--LEFT JOIN OSLP T2 ON T2.SlpCode=T0.SlpCode

WHERE T0.DocStatus = 'O' AND T0.DocType ='I'  

AND T0.DocEntry <= @doc --不考虑未跑MRP的销售订单

--AND T1.ShipDate <= @dueDate

UNION ALL

--SELECT T0.Code, T1.ItemCode,T1.Quantity ,T1.[Date] ShipDate

----SELECT T1.ItemCode,T1.Quantity ,T1.[Date] [ShipDate] 

--FROM OFCT T0 INNER JOIN FCT1 T1 ON T0.AbsID = T1.AbsID

--WHERE T0.AbsID <= @doc2 AND T1.[Date] BETWEEN '20170401' AND @dueDate AND T1.Quantity > 0

SELECT T10.Code,T10.ItemCode,T10.Quantity - ISNULL(SUM(T11.CmpltQty),0) Quantity,T10.[ShipDate]

FROM

(

SELECT T0.Code, T1.ItemCode,SUM(T1.Quantity) Quantity ,MIN(T1.[Date]) [ShipDate]

--SELECT T1.ItemCode,T1.Quantity ,T1.[Date] [ShipDate] 

FROM OFCT T0 INNER JOIN FCT1 T1 ON T0.AbsID = T1.AbsID

WHERE T0.AbsID <= @doc2 AND T1.[Date] BETWEEN '20170401' AND @dueDate AND T1.Quantity > 0 

GROUP BY T0.Code,T1.ItemCode

)T10 

LEFT JOIN OWOR T11 ON T10.Code = T11.PickRmrk AND T10.ItemCode = T11.ItemCode

GROUP BY T10.Code,T10.ItemCode,T10.Quantity,T10.ShipDate

HAVING T10.Quantity > ISNULL(SUM(T11.CmpltQty),0)

)T10

GROUP BY T10.NumAtCard,T10.[ItemCode]

UNION ALL

SELECT T0.NumAtCard,T1.Code,T2.Code

,T0.Quantity*T2.Quantity/T1.Qauntity,DATEADD(DD,-ISNULL(T3.LeadTime,0),T0.ShipDate) ,T0.[LEVEL]+1

,T0.Position+'-'+CAST(10+T2.ChildNum AS VARCHAR(3))

FROM BOM2 T0

INNER JOIN OITT T1 ON T0.ItemCode = T1.Code 

INNER JOIN ITT1 T2 ON T1.Code = T2.Father AND T2.Type = '4'

INNER JOIN OITM T3 ON T1.Code = T3.ItemCode

)

SELECT *

INTO #BOM2

FROM BOM2

WHERE ITEMCODE NOT LIKE '5%'

;

SELECT NumAtCard,ItemCode,CEILING(SUM(Quantity)) Quantity,MIN(ShipDate) ShipDate

INTO #TMP_NEED_ALL_LINES --整个订单的所有需求

FROM #BOM2 

GROUP BY NumAtCard,ItemCode;

--SELECT T0.NumAtCard,T0.Father,T0.ItemCode,T2.Quantity/T1.Qauntity BaseQty

-- FROM (SELECT DISTINCT NumAtCard,Father,ItemCode FROM #BOM2 WHERE Father <> '')  T0

-- INNER JOIN OITT T1 ON T0.Father = T1.Code

-- INNER JOIN ITT1 T2 ON T1.Code = T2.Father

-- ORDER BY  T0.NumAtCard,T0.Father,T0.ItemCode

--DROP TABLE #BOM2

--DROP TABLE #TMP_NEED_ALL_LINES

--RETURN

--数据查询

SELECT DISTINCT

T14.Position [层次]

,'.'+replicate(' ',2*(T14.LEVEL-1))+ T14.ItemCode [物料(左缩进)]

,T10.NumAtCard [业务订单号]

,T19.DocEntry [销售订单]

,T18.AbsId [销售预测]

,T17.ItemCode [物料编号]

,T17.Spec [工序]

,T17.InvntryUom [单位]

,T16.BZ [已下达]

,T10.Quantity [未清订单需求]

,T15.QtyIssued [已发料]

--,T11.PlannedQty [采购/生产下达数量],T12.CmpltQty [采购到货/生产完工]

,T11.OpenQty [采购/生产在途]

,ISNULL(T15.QtyIssued,0)+ISNULL(T11.OpenQty,0) - T10.Quantity [溢缺(已发料+在途-需求)] 

,T12.Quantity [采购申请(未下达)]

,ISNULL(T15.QtyIssued,0)+ISNULL(T11.OpenQty,0) - T10.Quantity + ISNULL(T12.Quantity,0) [外购件溢缺2(溢缺+采购申请)]

,T13.OnHand [可用库存]

,T13.OnHand_GK [灌口库存]

,T13.OnHand_TA [同安库存]

,ISNULL(T13.OnQC_GK,0) [灌口待检]

,ISNULL(T13.OnQC_TA,0) [同安待检]

,T10.ShipDate [交期]

,T17.LeadTime [提前期-天(未考虑)]

--,T10.ShipDate [交期]

,T17.U_Location [储位]

,CASE T17.U_Factory WHEN 'GK' THEN '灌口' WHEN 'TN' THEN '同安' ELSE '-' END [工厂]

,T17.TaxCtg [工作中心]

,T17.U_Buyer [物料采购员]

,T17.ItemName [物料描述]

,'C02-原料仓库/C03-成品仓库/C05-委外仓库/C06-配件仓/C11-包材仓库/C17-高仪仓库/C20-非生产类仓库/C31-同安三楼龙头仓库/C32-同安四楼配件仓/C33-同安成品仓/C39-同安包材仓' [考虑库存的仓库]

FROM 

#TMP_NEED_ALL_LINES T10

INNER JOIN OITM T17 ON T10.ItemCode = T17.ItemCode

LEFT JOIN ORDR T19 ON T10.NumAtCard = T19.NumAtCard

LEFT JOIN OFCT T18 ON T10.NumAtCard = T18.Code

LEFT JOIN

(

SELECT T0.NumAtCard,T0.ItemCode,'Y' BZ

FROM #TMP_NEED_ALL_LINES T0

INNER JOIN OWOR T1 ON T0.NumAtCard = T1.PickRmrk AND T0.ItemCode = T1.ItemCode

UNION ALL

SELECT T0.NumAtCard,T0.ItemCode,'Y' BZ 

FROM #TMP_NEED_ALL_LINES T0

INNER JOIN POR1 T1 ON T0.NumAtCard = T1.U_OrderNo AND T0.ItemCode = T1.ItemCode

)T16 ON T10.NumAtCard = T16.NumAtCard AND T10.ItemCode = T16.ItemCode

LEFT JOIN(

--计划数量

SELECT U0.NumAtCard,U0.ItemCode,SUM(ISNULL(PlannedQty,0)) PlannedQty,CEILING(SUM(ISNULL(OpenQty,0))) OpenQty

FROM

(

--生产订单

SELECT T0.NumAtCard,T0.ItemCode,T1.PlannedQty PlannedQty ,CASE WHEN T1.Status IN ('P','R') AND T1.PlannedQty > T1.CmpltQty THEN T1.PlannedQty - T1.CmpltQty ELSE 0 END OpenQty

FROM #TMP_NEED_ALL_LINES T0

INNER JOIN OWOR T1 ON T0.NumAtCard = T1.PickRmrk AND T0.ItemCode = T1.ItemCode

WHERE T1.Status <> 'C'

UNION ALL

--采购订单

SELECT T0.NumAtCard,T0.ItemCode,T1.Quantity*T1.NumPerMsr PlannedQty ,T1.OpenCreQty*T1.NumPerMsr 

FROM #TMP_NEED_ALL_LINES T0

INNER JOIN POR1 T1 ON T0.NumAtCard = T1.U_OrderNo AND T0.ItemCode = T1.ItemCode

INNER JOIN OPOR T2 ON T1.DocEntry = T2.DocEntry AND T2.DocType = 'I'

WHERE T2.CANCELED <> 'Y'

--UNION ALL --采购退货单不会产生在途

--SELECT T0.NumAtCard,T0.ItemCode,T1.Quantity*T1.NumPerMsr PlannedQty ,T1.OpenCreQty*T1.NumPerMsr 

--FROM #TMP_NEED_ALL_LINES T0

--INNER JOIN RPD1 T1 ON T0.NumAtCard = T1.U_OrderNo AND T0.ItemCode = T1.ItemCode

--INNER JOIN ORPD T2 ON T1.DocEntry = T2.DocEntry AND T2.DocType = 'I'

)U0

GROUP BY U0.NumAtCard,U0.ItemCode

)T11 ON T10.NumAtCard = T11.NumAtCard AND T10.ItemCode = T11.ItemCode

LEFT JOIN 

(

--采购申请数量

SELECT T0.NumAtCard,T0.ItemCode,T1.OpenCreQty Quantity 

FROM #TMP_NEED_ALL_LINES T0

INNER JOIN PRQ1 T1 ON T0.NumAtCard = T1.U_OrderNo AND T0.ItemCode = T1.ItemCode AND T1.LineStatus = 'O'

)T12 ON T10.NumAtCard = T12.NumAtCard AND T10.ItemCode = T12.ItemCode

LEFT JOIN

(

--库存状况

SELECT T0.ItemCode,ISNULL(SUM(CASE WHEN T0.WHSCODE NOT IN('C08','C38') THEN T0.OnHand ELSE 0 END),0) OnHand

,ISNULL(SUM(CASE WHEN T0.WhsCode NOT LIKE 'C3%' AND T0.WHSCODE <> 'C08' THEN T0.OnHand ELSE 0 END ),0) AS OnHand_GK 

,ISNULL(SUM(CASE WHEN T0.WhsCode LIKE 'C3%' AND T0.WhsCode <> 'C38' THEN T0.OnHand ELSE 0 END ),0) AS OnHand_TA

,ISNULL(SUM(CASE WHEN T0.WhsCode = 'C08' THEN T0.OnHand ELSE 0 END ),0) AS OnQC_GK

,ISNULL(SUM(CASE WHEN T0.WhsCode = 'C38' THEN T0.OnHand ELSE 0 END ),0) AS OnQC_TA

FROM OITW T0 

WHERE T0.OnHand >0 

AND T0.ItemCode IN (SELECT DISTINCT ItemCode FROM #TMP_NEED_ALL_LINES)

AND T0.WhsCode NOT IN ('C01','C04','C10','C13','C14','C15','C16','C18','C19','C34','C99')

GROUP BY T0.ItemCode

)T13 ON T10.ItemCode = T13.ItemCode

LEFT JOIN

(

--已发料数量

SELECT T29.NumAtCard,T20.ItemCode

,ISNULL(SUM(CASE WHEN CEILING(T21.PlannedQty*T20.BaseQty) < T22.IssuedQty --超出计划数量发料 计划量 - 完工数*基数

THEN CEILING(T21.PlannedQty*T20.BaseQty) - T21.CmpltQty*T20.BaseQty 

ELSE T22.IssuedQty - CEILING(T21.CmpltQty*T20.BaseQty) END) --不超出 实发量 - 完工数*基数 若负数则可能没有领料或挪单领料，其它单理论上有正数补足

,0)  

AS [QtyIssued]

FROM 

(

--合计需求求下阶需求

SELECT T0.Father,T2.Code ItemCode,SUM(T2.Quantity/T1.Qauntity) BaseQty

FROM (SELECT DISTINCT Father FROM #BOM2 WHERE Father <> '')  T0

INNER JOIN OITT T1 ON T0.Father = T1.Code

INNER JOIN ITT1 T2 ON T1.Code = T2.Father

GROUP BY  T0.Father,T2.Code

)T20

INNER JOIN (SELECT DISTINCT NumAtCard,Father FROM #BOM2) T29 ON T20.Father = T29.Father --AND T20.ItemCode = T29.ItemCode

INNER JOIN OWOR T21 ON T29.NumAtCard = T21.PickRmrk AND T20.Father = T21.ItemCode AND T21.[Status] IN ('P','R') --AND T11.DueDate <= @dueDate

INNER JOIN WOR1 T22 ON T21.DocEntry = T22.DocEntry AND T20.ItemCode = T22.ItemCode

GROUP BY T29.NumAtCard,T20.ItemCode

) T15 ON T10.NumAtCard = T15.NumAtCard AND T10.ItemCode = T15.ItemCode

RIGHT JOIN #BOM2 T14 ON T10.NumAtCard = T14.NumAtCard AND T10.ItemCode = T14.ItemCode

ORDER BY T14.Position ASC

FOR BROWSE 

DROP TABLE #BOM2

DROP TABLE #TMP_NEED_ALL_LINES

RETURN

END