USE [DEL_DATA]

GO

/****** Object:  StoredProcedure [dbo].[U_P_STOCK2NEED_EXPEND2]    Script Date: 08/14/2017 10:40:30 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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

-- Fuction:按业务订单号查订单执行状态

-- wangtp

-- 2017.8.11

-- 预测未完工部分为未清需求 20170628

-- 采购生产在途 包含灌口C08待检数量 20170727

--按截止日考虑库存分配(所有物料及工序)

--EXEC [U_P_STOCK2NEED_EXPEND2]

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

ALTER PROCEDURE [dbo].[U_P_STOCK2NEED_EXPEND2]

AS

BEGIN

DECLARE @owe_line_count INT

DECLARE @level INT

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

DECLARE @id INT

----------------------------------------------------------------------------------------------------------------------------------------

--T1:仅涉及未清销售订单的产品取得BOM清单，并计算各组件在其中的需求优先级关系 BOM里面子件不能再扩展的，优先级最大，其父件优先级-1

--T1:父件、子件、基数、BOM展开层级、需求计算优先级

CREATE TABLE #BOM(Father NVARCHAR(20),ChildNum VARCHAR(3),ItemCode NVARCHAR(20),BaseQty DECIMAL(19,6),[Level] INT)

SET @level = 1

--2.1 订单涉及BOM分解首层

INSERT #BOM

SELECT T0.Code Father,CAST(10+MIN(T1.ChildNum) AS VARCHAR(3)),T1.Code ItemCode,CAST(SUM(T1.Quantity)/T0.Qauntity AS DECIMAL(19,6)) Quantity ,0 [Level]

--INTO #BOM

FROM OITT T0

INNER JOIN ITT1 T1 ON T0.Code = T1.Father AND T1.[Type]= '4'

GROUP BY T0.Code,T1.Code,T0.Qauntity

-- 第三步 排需求优先级 Level----------------------------------------

--3.1 末级叶子节点处理

SET @level = 99

UPDATE T0 SET T0.[Level] = @level

FROM #BOM T0

LEFT JOIN #BOM T1 ON T0.ItemCode = T1.Father 

WHERE T1.Father IS NULL

--3.2 非末级逐级处理

WHILE(@level > 0)

BEGIN

UPDATE #BOM SET [Level] = @level - 1 WHERE ItemCode IN (SELECT DISTINCT FATHER FROM #BOM WHERE [Level] = @level)

IF @@ROWCOUNT = 0 BREAK

SET  @level = @level - 1

END

--减去落差

UPDATE #BOM SET [Level] = [Level] - (@level - 1)

--求最大层级

SELECT @maxLevel = MAX([Level]) FROM #BOM

SELECT DISTINCT ItemCode,Level INTO #TMP_LEVEL FROM #BOM

--可用数量

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

INTO #TMP_ONHAND

FROM OITW T0 

WHERE T0.OnHand >0 

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

GROUP BY T0.ItemCode

----按BOM扩展--------------------------------------------

CREATE TABLE #TEMP_NEED_ALL 

(

NumAtCard NVARCHAR(100), --业务订单号

DocEntry INT , --ORDR.DocEntry

AbsId INT, --OFCT.AbsId

Position VARCHAR(MAX), --需求位置

[LEVEL] INT, --需求等级

Father NVARCHAR(20), --分解从

ItemCode NVARCHAR(20), --需求物料

QtyNeedF DECIMAL(19,6), --父件需求数量

BaseQty DECIMAL(19,6), --基数

QtyNeed DECIMAL(19,6) --按订单数展开毛需求量

,DocDueDate DATETIME --订单交期

,DueDate DATETIME --组件交期

)

--订单需求写入需求表

INSERT INTO #TEMP_NEED_ALL

SELECT T20.NumAtCard,T20.DocEntry,T20.AbsId,CAST(1000+ROW_NUMBER() OVER(order by T20.[ItemCode],T20.ShipDate ASC,T20.DocEntry,T20.AbsId) AS VARCHAR(MAX)) [Position],T21.[Level],

'' Father,T20.ItemCode,T20.Quantity [QtyFather],1.00 [BaseQty],T20.Quantity [QtyNeed]

,T20.ShipDate,T20.ShipDate

FROM 

(

--合计需求

SELECT T10.NumAtCard,T10.DocEntry,-1 AbsId,T10.ItemCode,SUM(T10.OpenQty) Quantity,MIN(T10.ShipDate) ShipDate

FROM(

SELECT T0.NumAtCard,T0.DocEntry,-1 [AbsId], T1.[ItemCode],T1.[OpenCreQty]*T1.NumPerMsr OpenQty,T1.ShipDate

FROM [dbo].ORDR T0

INNER JOIN RDR1 T1 ON T1.DocEntry=T0.DocEntry 

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

AND T0.DocEntry <= (SELECT MAX(T1.DocEntry) FROM OWOR T0 INNER JOIN ORDR T1 ON T0.PickRmrk = T1.NumAtCard WHERE T0.Status <> 'C')

AND T0.DocEntry NOT IN (SELECT T0.DocEntry FROM ORDR T0 LEFT JOIN MSN5 T1 ON T1.DocType = 17 AND T0.DocEntry = T1.DocEntry 

WHERE T0.DocEntry > 1520 AND T1.DocEntry IS NULL) --20170807 未跑MRP的销售订单

)T10

GROUP BY T10.NumAtCard,T10.DocEntry,T10.ItemCode

UNION ALL

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

FROM

(

SELECT T0.Code,T0.AbsID, 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  T1.U_Status = 'O' AND T1.Quantity > 0 

AND T0.AbsID <= (SELECT MAX(T1.AbsId) FROM OWOR T0 INNER JOIN OFCT T1 ON T0.PickRmrk = T1.Code WHERE T0.Status <> 'C')

GROUP BY T0.Code,T0.AbsID,T1.ItemCode

)T10 

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

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

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

) T20

LEFT JOIN #TMP_LEVEL T21 ON T20.[ItemCode] = T21.ItemCode

--顶层默认0级

UPDATE #TEMP_NEED_ALL SET LEVEL = 0 WHERE LEVEL IS NULL

--逐级计算已发料 按业务订单号、上级料号、子件料号 匹配计算  未清工单考虑库存收发

CREATE TABLE #TEMP_NEED_ISSUED

(

NumAtCard NVARCHAR(100), --业务订单号

DocEntry INT , --ORDR.DocEntry

AbsId INT, --OFCT.AbsId

Position VARCHAR(MAX), --需求位置

[LEVEL] INT, --需求等级

Father NVARCHAR(20), --父件

BaseQty DECIMAL(19,6), --基本数量

ItemCode NVARCHAR(20), --需求物料

QtyNeedOpen DECIMAL(19,6), --按订单数展开需求量

QtyIssued DECIMAL(19,6), --根据父件-子件对 计算已发料数量

DocDueDate DATETIME, --订单交期

DueDate DATETIME --组件交期

)

--最终逐级汇总需求

CREATE TABLE #TEMP_NEED_SUM

(

ID INT, --行编号

NumAtCard NVARCHAR(100), --业务订单号

DocEntry INT , --ORDR.DocEntry

AbsId INT, --OFCT.AbsId

Position VARCHAR(MAX), --需求位置

[LEVEL] INT, --需求等级

Father NVARCHAR(20), --父件

BaseQty DECIMAL(19,6), --基本数量

ItemCode NVARCHAR(20), --需求物料

QtyNeedOpen DECIMAL(19,6), --按订单数展开需求量

QtyIssued DECIMAL(19,6), --根据父件-子件对 计算已发料数量

QtyOrder DECIMAL(19,6), --在途数量

QtyOPRQ DECIMAL(19,6),--申请数量

QtyNeed DECIMAL(19,6), --欠料 = 总需求 - 已发料 - 在途 - 采购申请

QtyYQ DECIMAL(19,6), --溢缺 = 已发料 + 在途 + 采购申请 - 总需求

OnHandAll DECIMAL(19,6), --总可用库存

OnHand DECIMAL(19,6), --在库存数量

QtyEnd DECIMAL(19,6), --溢缺2 要传递到下层分解的数量=总需求 - 库存分配 - 已发料

DocDueDate DATETIME, --订单交期

DueDate DATETIME --组件交期

);

/*----0级-------------------------------------------------------------------------------*/

--将0级汇总写入最终表 已发料当成0 

SET @level = 0;

INSERT INTO #TEMP_NEED_SUM(ID,NumAtCard,DocEntry,AbsId,Position,[LEVEL],     Father,BaseQty,ItemCode,QtyNeedOpen,QtyNeed,QtyIssued,OnHandAll,    DocDueDate,DueDate )

SELECT  ROW_NUMBER() OVER(order by T10.[ItemCode],T10.DocDueDate ASC,T10.DocEntry,T10.AbsId) [ID]

,T10.NumAtCard ,T10.DocEntry,T10.AbsId,MIN(T10.Position) [Position],MIN(T10.LEVEL) [LEVEL]    

,T10.Father,1,T10.ItemCode,SUM(T10.QtyNeed),SUM(T10.QtyNeed),0 ,T11.OnHand ,T10.DocDueDate ,DATEADD(DD,-ISNULL(T12.LeadTime,0),T10.DocDueDate) [DueDate]

FROM #TEMP_NEED_ALL T10

LEFT JOIN #TMP_ONHAND T11 ON T10.ItemCode = T11.ItemCode

LEFT JOIN OITM T12 ON T10.ItemCode = T12.ItemCode

WHERE T10.[LEVEL] = @level

GROUP BY  T10.ItemCode,T10.NumAtCard ,T10.DocEntry,T10.AbsId,T10.Father,T11.OnHand ,T10.DocDueDate ,T12.LeadTime 

;

--0级在途 --欠料 = 总需求 - 已发料 - 在途

UPDATE T10 SET T10.QtyOrder = ISNULL(T11.QtyOrder,0)

,T10.QtyOPRQ = ISNULL(T12.QtyOPRQ,0) --采购申请

,T10.QtyYQ = ISNULL(T11.QtyOrder,0)+ISNULL(T12.QtyOPRQ,0) - T10.QtyNeedOpen --溢缺 =  采购生产在途 + 已发料 + 采购申请 -  未清需求

,T10.QtyNeed = CASE WHEN T10.QtyNeedOpen > ISNULL(T11.QtyOrder,0)+ISNULL(T12.QtyOPRQ,0) --欠料 = 未清需求 - 采购生产在途 - 已发料 - 采购申请

THEN T10.QtyNeedOpen-(ISNULL(T11.QtyOrder,0)+ISNULL(T12.QtyOPRQ,0)) ELSE 0 END

FROM #TEMP_NEED_SUM T10

LEFT JOIN(

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

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 #TEMP_NEED_SUM T0

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

WHERE T0.LEVEL = @level AND T1.Status IN ('P','R')

UNION ALL

--采购订单

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

FROM (SELECT DISTINCT NumAtCard,ItemCode FROM #TEMP_NEED_SUM WHERE LEVEL = @level)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 

WHERE T2.DocType = 'I' AND T2.DocStatus = 'O' AND T1.LineStatus = 'O' AND T1.OpenCreQty > 0

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

)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,SUM(T1.OpenCreQty) QtyOPRQ 

FROM #TEMP_NEED_SUM T0

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

INNER JOIN OPRQ T2 ON T1.DocEntry = T2.DocEntry

WHERE T0.LEVEL = @level  AND T2.DocStatus = 'O' AND T1.LineStatus = 'O' AND  T1.OpenCreQty>0

GROUP BY T0.NumAtCard,T0.ItemCode

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

 

--0级分配库存

UPDATE T11 SET T11.OnHand = CASE WHEN T11.OnHandAll >= T10.QtyNeedSumUp1 THEN T11.QtyNeed 

WHEN T11.OnHandAll BETWEEN T10.QtyNeedSumUp1-T11.QtyNeed AND T10.QtyNeedSumUp1 THEN T11.QtyNeed - (T10.QtyNeedSumUp1 - T11.OnHandAll)

ELSE 0 END

--SELECT T11.*,T10.QtyNeedSumUp1,CASE WHEN T11.OnHandAll >= T10.QtyNeedSumUp1 THEN T11.QtyNeed 

-- WHEN T11.OnHandAll BETWEEN T10.QtyNeedSumUp1-T11.QtyNeed AND T10.QtyNeedSumUp1 THEN T11.QtyNeed - (T10.QtyNeedSumUp1 - T11.OnHandAll)

-- ELSE 0 END [FP]

FROM (

SELECT T1.ID,isnull(SUM(T0.QtyNeed),0) QtyNeedSumUp1

FROM #TEMP_NEED_SUM T0

INNER JOIN #TEMP_NEED_SUM T1 ON T0.ID <= T1.ID AND T0.ItemCode  = T1.ItemCode --AND T0.FATHER = T1.FATHER AND T0.ITEMCODE = T1.ITEMCODE

WHERE T0.LEVEL = @level AND T1.LEVEL = @level

GROUP BY T1.ID--,T1.ItemCode,T1.FATHER,T1.ITEMCODE

)T10

INNER JOIN #TEMP_NEED_SUM T11 ON T10.ID = T11.ID 

--计算最终下级分解需求量 = 总需求 - 库存分配 - 已发料

UPDATE #TEMP_NEED_SUM SET QtyEnd = CASE WHEN QtyNeedOpen > ISNULL(OnHand,0) + ISNULL(QtyIssued,0)  THEN  QtyNeedOpen - (ISNULL(OnHand,0) + ISNULL(QtyIssued,0)) ELSE 0 END WHERE LEVEL = @level

/*----0级结束-------------------------------------------------------------------------------*/

--循环计算下级需求

WHILE(1=1)

BEGIN

--展开下阶需求

INSERT INTO #TEMP_NEED_ALL

SELECT T0.NumAtCard ,T0.DocEntry,T0.AbsId ,T0.Position+'-'+T1.ChildNum [Position],T1.[LEVEL]

, T0.ItemCode [Father],T1.ItemCode,ABS(T0.QtyEnd) QtyNeedF,T1.[BaseQty],SUM(CEILING(ABS(T0.QtyEnd)*T1.[BaseQty])) [QtyNeed]

,T0.DocDueDate ,MIN(DATEADD(DD,-ISNULL(T2.LeadTime,0),T0.DueDate)) [DueDate]

FROM #TEMP_NEED_SUM T0

INNER JOIN #BOM T1 ON T0.ItemCode = T1.Father 

INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode 

LEFT JOIN #TMP_LEVEL T3 ON T1.ItemCode = T3.ItemCode 

WHERE T0.[Level] = @level

GROUP BY  T1.[LEVEL],T0.NumAtCard ,T0.DocEntry,T0.AbsId,T0.Position,T1.ChildNum,T0.ItemCode,T1.ItemCode,T0.QtyEnd,T1.[BaseQty],T0.DocDueDate,T2.LeadTime

--汇总需求及已发料

SET @level = @level + 1;

--小汇总

INSERT INTO #TEMP_NEED_ISSUED(NumAtCard,DocEntry,AbsId,Position,[LEVEL]    ,Father,BaseQty,ItemCode,QtyNeedOpen,QtyIssued     ,DocDueDate,DueDate )

SELECT  T10.NumAtCard ,T10.DocEntry,T10.AbsId,MIN(T10.Position) [Position],MIN(T10.LEVEL) [LEVEL]    

,T10.Father,T10.BaseQty,T10.ItemCode,SUM(T10.QtyNeed),0 

,T10.DocDueDate ,DATEADD(DD,-ISNULL(T12.LeadTime,0),T10.DocDueDate) [DueDate]

FROM #TEMP_NEED_ALL T10

LEFT JOIN #TMP_ONHAND T11 ON T10.ItemCode = T11.ItemCode

LEFT JOIN OITM T12 ON T10.ItemCode = T12.ItemCode

WHERE T10.[LEVEL] = @level

GROUP BY  T10.ItemCode,T10.NumAtCard ,T10.DocEntry,T10.AbsId,T10.Father,T10.BaseQty,T11.OnHand ,T10.DocDueDate ,T12.LeadTime 

;

--统计已发料

UPDATE T10 SET  T10.QtyIssued = ISNULL(T13.QtyIssued,0) --已发料

FROM #TEMP_NEED_ISSUED T10

LEFT JOIN 

(

SELECT T0.NumAtCard,T0.Father,T0.ItemCode

,ISNULL(SUM(

CASE WHEN T2.IssuedQty > CEILING(T1.CmpltQty*T0.BaseQty) THEN

CASE WHEN T2.IssuedQty < CEILING(T1.PlannedQty*T0.BaseQty) THEN T2.IssuedQty - CEILING(T1.CmpltQty*T0.BaseQty)

ELSE CEILING(T1.PlannedQty*T0.BaseQty) - CEILING(T1.CmpltQty*T0.BaseQty) 

END

ELSE 0 END

)

,0) AS  [QtyIssued]

FROM #TEMP_NEED_SUM T0

INNER JOIN OWOR T1 ON T0.NumAtCard = T1.PickRmrk AND T1.Status IN ('P','R') AND T0.Father = T1.ItemCode

INNER JOIN WOR1 T2 ON T1.DocEntry = T2.DocEntry AND T0.ItemCode = T2.ItemCode 

WHERE T0.LEVEL = @level

GROUP BY T0.NumAtCard,T0.Father,T0.ItemCode

)T13 ON T10.NumAtCard = T13.NumAtCard AND T10.Father = T13.Father AND T10.ItemCode = T13.ItemCode

INSERT INTO #TEMP_NEED_SUM(ID,NumAtCard,DocEntry,AbsId,Position,[LEVEL],ItemCode,QtyNeedOpen,QtyNeed,QtyIssued,OnHandAll,    DocDueDate,DueDate )

SELECT  ROW_NUMBER() OVER(order by T10.ItemCode,T10.NumAtCard ,T10.DocEntry,T10.AbsId,T11.OnHand ,T10.DocDueDate ASC) [ID]

,T10.NumAtCard ,T10.DocEntry,T10.AbsId,MIN(T10.Position) [Position],MIN(T10.LEVEL) [LEVEL]    

,T10.ItemCode,SUM(T10.QtyNeedOpen),SUM(T10.QtyNeedOpen),SUM(T10.QtyIssued) ,T11.OnHand ,T10.DocDueDate ,MIN(T10.[DueDate])

FROM #TEMP_NEED_ISSUED T10

LEFT JOIN #TMP_ONHAND T11 ON T10.ItemCode = T11.ItemCode

WHERE T10.[LEVEL] = @level

GROUP BY  T10.ItemCode,T10.NumAtCard ,T10.DocEntry,T10.AbsId,T11.OnHand ,T10.DocDueDate 

;

--更新已发料数量

UPDATE T10 SET T10.QtyOrder = ISNULL(T11.QtyOrder,0) --采购生产在途

,T10.QtyOPRQ = ISNULL(T12.QtyOPRQ,0) --采购申请

,T10.QtyYQ =ISNULL(T11.QtyOrder,0)+ISNULL(T10.QtyIssued,0)+ISNULL(T12.QtyOPRQ,0) - T10.QtyNeedOpen --溢缺 =  采购生产在途 + 已发料 + 采购申请 -  未清需求

,T10.QtyNeed = CASE WHEN T10.QtyNeedOpen > ISNULL(T11.QtyOrder,0)+ISNULL(T10.QtyIssued,0)+ISNULL(T12.QtyOPRQ,0) --欠料 = 未清需求 - 采购生产在途 - 已发料 - 采购申请

THEN T10.QtyNeedOpen-(ISNULL(T11.QtyOrder,0)+ISNULL(T10.QtyIssued,0)+ISNULL(T12.QtyOPRQ,0)) ELSE 0 END

FROM #TEMP_NEED_SUM T10

LEFT JOIN(

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

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 #TEMP_NEED_SUM T0

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

WHERE T0.LEVEL = @level AND T1.Status IN ('P','R')

UNION ALL

--采购订单

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

FROM (SELECT DISTINCT NumAtCard,ItemCode FROM #TEMP_NEED_SUM WHERE LEVEL = @level)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 

WHERE T2.DocType = 'I' AND T2.DocStatus = 'O' AND T1.LineStatus = 'O' AND T1.OpenCreQty > 0

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

)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,SUM(T1.OpenCreQty) QtyOPRQ 

FROM #TEMP_NEED_SUM T0

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

INNER JOIN OPRQ T2 ON T1.DocEntry = T2.DocEntry 

WHERE T0.LEVEL = @level AND T2.DocStatus = 'O' AND T1.LineStatus = 'O' AND  T1.OpenCreQty>0

GROUP BY T0.NumAtCard,T0.ItemCode

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

WHERE T10.LEVEL = @level

;

--库存分配

UPDATE T11 SET T11.OnHand = CASE WHEN T11.OnHandAll >= T10.QtyNeedSumUp1 THEN T11.QtyNeed 

WHEN T11.OnHandAll BETWEEN T10.QtyNeedSumUp1-T11.QtyNeed AND T10.QtyNeedSumUp1 THEN T11.QtyNeed - (T10.QtyNeedSumUp1 - T11.OnHandAll)

ELSE 0 END

FROM (

SELECT T1.ID,isnull(SUM(T0.QtyNeed),0) QtyNeedSumUp1

FROM #TEMP_NEED_SUM T0

INNER JOIN #TEMP_NEED_SUM T1 ON T0.ID <= T1.ID AND T0.ItemCode  = T1.ItemCode --AND T0.FATHER = T1.FATHER AND T0.ITEMCODE = T1.ITEMCODE

WHERE T0.LEVEL = @level AND T1.LEVEL = @level

GROUP BY T1.ID--,T1.ItemCode,T1.FATHER,T1.ITEMCODE

)T10

INNER JOIN #TEMP_NEED_SUM T11 ON T10.ID = T11.ID

WHERE T11.LEVEL =   @level

;

--计算最终下级分解需求量 = 总需求 - 库存分配 - 已发料

UPDATE #TEMP_NEED_SUM SET QtyEnd = CASE WHEN QtyNeedOpen > ISNULL(OnHand,0) + ISNULL(QtyIssued,0)  THEN  QtyNeedOpen - (ISNULL(OnHand,0) + ISNULL(QtyIssued,0)) ELSE 0 END WHERE LEVEL = @level

IF @level > @maxLevel+1 BREAK

END

--数据查询

SELECT DISTINCT

T10.Position [层次]

,'.'+replicate(' ',2*(LEN(T10.Position)-4)/3)+ T10.ItemCode [物料(左缩进)]

,T10.NumAtCard [业务订单号]

,T19.DocEntry [销售订单]

,T18.AbsId [销售预测]

,T17.ItemCode [物料编号]

,T17.Spec [工序]

,T17.InvntryUom [单位]

,T16.BZ [已下达]

,T10.QtyNeedOpen [未清需求]

,T10.QtyIssued [已发料]

,T10.QtyOrder [采购/生产在途]

,T10.QtyOPRQ [采购申请]

,T10.QtyNeed [欠料(未清需求-已发料-采购/生产在途-采购申请)]

,T10.OnHandAll [总库存]

,T10.OnHand [分配库存]

,T10.QtyYQ [溢缺(已发料+采购/生产在途+采购申请-未清需求)]

,T10.OnHand + T10.QtyYQ [溢缺2(分配库存+溢缺)] 

,T10.QtyEnd  [下层展开数量(未清需求-已发料-分配库存)]

,T14.DocNum [生产订单]

--,CASE WHEN T17.Spec = '外购' THEN T10.OnHand - T10.QtyNeed + ISNULL(T11.OpenQty,0) + ISNULL(T12.Quantity,0) ELSE NULL END [溢缺3(外购件 溢缺2+采购申请)]

--,T31.DocNum [生产订单]

--,T32.DocEntry [采购订单]

--,T32.LineNum + 1 [采购行] 

--,T33.DocEntry [采购申请]

--,T33.LineNum + 1 [申请行] 

,T13.OnHand_GK [灌口库存]

,T13.OnHand_TA [同安库存]

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

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

,T10.DocDueDate [订单交期]

,T10.DueDate [组件交期]

,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-同安包材仓' [考虑库存的仓库]

,T10.ID

FROM #TEMP_NEED_SUM T10

LEFT JOIN OITM T15 ON T10.Father = T15.ItemCode --父件

INNER JOIN OITM T17 ON T10.ItemCode = T17.ItemCode --需求物料

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

LEFT JOIN OFCT T18 ON T10.AbsId  = T18.AbsId

LEFT JOIN

(

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

FROM #TEMP_NEED_SUM 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 #TEMP_NEED_SUM 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 #TMP_ONHAND T13 ON T10.ItemCode = T13.ItemCode

LEFT JOIN OWOR T14 ON T10.ItemCode = T14.ItemCode AND T10.NumAtCard = T14.PickRmrk AND T14.Status IN ('P','R')

ORDER BY T10.Position ASC -- T17.ItemCode ,T10.DocDueDate ASC,T19.DocEntry ASC,T18.AbsId ASC

FOR BROWSE 

END