未清销售订单及预测的所有工序物料需求(按BOM展开不考虑已发料)

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

DECLARE @dueDate DATETIME --截止交期

SELECT @dueDate = T0.T_DueDate FROM OFPR T0 WHERE T0.T_DueDate = [%0]

SELECT @dueDate = '[%0]'

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

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

-- 程序：库存分配表-所有物料及工序

-- wangtp

-- 2017.5.10

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

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

--DECLARE @dueDate DATETIME --截止交期

--SELECT @dueDate = T0.T_DueDate FROM OFPR T0 WHERE T0.T_DueDate = [%0]

--SELECT @dueDate = '[%0]'

--EXEC [U_P_STOCK2NEED_WITHOUT_ISSUED] @dueDate

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

ALTER PROCEDURE [dbo].[U_P_STOCK2NEED_WITHOUT_ISSUED]

(

@dueDate DATETIME

)

AS

BEGIN

DECLARE @owe_line_count INT

DECLARE @level INT

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

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

IF @dueDate IS NULL OR @dueDate = '19000101' OR @dueDate < CONVERT(VARCHAR(8),GETDATE(),112)

BEGIN

 SET @dueDate = DATEADD(dd,-1, DATEADD(MM,2,GETDATE()))

 SET @dueDate = DATEADD(dd,-DATEPART(dd,@dueDate),@dueDate)

 SET @dueDate = CONVERT(VARCHAR(8),@dueDate,112)

END

--SELECT @end

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

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

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

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

SET @level = 1

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

INSERT #BOM

SELECT T0.Code Father,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'

--WHERE T0.Code IN (SELECT DISTINCT T1.ITEMCODE FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocStatus= 'O' AND T1.LINESTATUS = 'O')

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 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 

(

[LEVEL] INT, --需求等级

Father NVARCHAR(20), --分解从

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

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

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

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

QtyIssued DECIMAL(19,6) --已发料（父件子件匹配 行发料数量 - 完工数*基数 舍负值为0）

)

--订单需求写入需求表

INSERT INTO #TEMP_NEED_ALL

SELECT DISTINCT CASE WHEN T21.Father IS NULL THEN 0 WHEN T21.ItemCode IS NOT NULL THEN T21.Level ELSE @maxLevel END [LEVEL]

,'' Father,T20.ItemCode, 

T20.OpenQty [QtyFather],1.00 [BaseQty],T20.OpenQty [QtyNeed],0.00 [QtyIssued]

FROM 

(

--合计需求

SELECT T10.[ItemCode],SUM(T10.OpenQty) OpenQty,MIN(T10.ShipDate) ShipDate

--INTO #TEMP_OPEN_NEED

FROM 

(

SELECT T1.[ItemCode],T1.[OpenCreQty] OpenQty,T1.ShipDate

FROM [dbo].ORDR T0

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

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

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

AND T1.ShipDate <= @dueDate

UNION ALL

SELECT 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  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.ItemCode,T10.Quantity,T10.ShipDate

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

)T10

GROUP BY T10.[ItemCode]

) T20

LEFT JOIN (SELECT DISTINCT Father,ItemCode,Level FROM #BOM) T21 ON T20.[ItemCode] = T21.ItemCode

--最终逐级汇总需求

CREATE TABLE #TEMP_NEED_SUM

(

[LEVEL] INT, --需求等级

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

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

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

OnHand_GK DECIMAL(19,6), --灌口库存

OnHand_TA DECIMAL(19,6), --同安库存

QtyEnd DECIMAL(19,6), --溢缺

OnQC_GK DECIMAL(19,6), --灌口待检数量

OnQC_TA DECIMAL(19,6) --同安行检数量

)

--将0级汇总写入最终表

SET @level = 0;

INSERT INTO #TEMP_NEED_SUM

SELECT T10.[LEVEL],T10.ItemCode,T10.QtyNeed,T11.OnHand,T11.OnHand_GK

,T11.OnHand_TA,ISNULL(T11.OnHand,0) - T10.QtyNeed [QtyEnd],T11.OnQC_GK,T11.OnQC_TA

FROM #TEMP_NEED_ALL T10

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

WHERE T10.[LEVEL] = @level

--循环计算下级需求

WHILE(1=1)

BEGIN

--合计需求求下阶需求

INSERT INTO #TEMP_NEED_ALL 

SELECT T1.[LEVEL], T0.ItemCode [Father],T1.ItemCode 

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

FROM #TEMP_NEED_SUM T0

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

WHERE T0.[Level] = @level AND T0.QtyEnd < 0 --上级库存及发料不足时时才计算下阶物料

GROUP BY  T1.[LEVEL],T0.ItemCode,T1.ItemCode,T0.QtyEnd,T1.[BaseQty]

--汇总需求下阶需求

SELECT @level = @level + 1

INSERT INTO #TEMP_NEED_SUM

SELECT T10.[LEVEL],T10.ItemCode,SUM(T10.QtyNeed) QtyNeed,T11.OnHand,T11.OnHand_GK

,T11.OnHand_TA,ISNULL(T11.OnHand,0) - SUM(T10.QtyNeed ) [QtyEnd],T11.OnQC_GK,T11.OnQC_TA

FROM #TEMP_NEED_ALL T10

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

WHERE T10.[LEVEL] = @level 

AND T10.QtyNeed >= T10.QtyIssued --针对该父件-子件对 发料已超过需求数量 则不进入汇总了(不需要分摊库存量)

GROUP BY T10.[LEVEL],T10.ItemCode,T11.OnHand,T11.OnHand_GK,T11.OnHand_TA,T11.OnQC_GK,T11.OnQC_TA

IF @level > @maxLevel+1 BREAK

END

SELECT DISTINCT

@dueDate [截止日期]

,T10.ItemCode [物料编号]

,T10.InvntryUom [单位]

,T11.QtyNeed [毛需求]

,T12.OnHand [可用库存]

,T12.OnHand_GK [灌口库存]

,T12.OnHand_TA [同安库存]

,ISNULL(T11.[QtyEnd],T12.OnHand) [溢缺]

,ISNULL(T13.OnOrder,0) [采购/生产在途]

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

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

,T10.ItemName [物料描述]

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

--,T10.ShipDate [交期]

,T10.Spec [工序]

,T10.U_Location [储位]

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

,T10.TaxCtg [工作中心]

,T10.U_Buyer [物料采购员]

,T15.[Level] [需求等级]

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

FROM OITM T10

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

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

LEFT JOIN

(

SELECT U0.ItemCode,SUM(U0.OnOrder) OnOrder

FROM(

SELECT ItemCode,PlannedQty - CmpltQty OnOrder

FROM OWOR 

WHERE Status IN ('P','R') AND DueDate <= @dueDate AND PlannedQty > CmpltQty 

UNION ALL

SELECT T1.ItemCode ,OpenCreQty

FROM OPOR T0

INNER JOIN POR1 T1 On T0.DocEntry = T1.DocEntry 

WHERE T0.DocStatus = 'O' AND T0.DocType = 'I' AND T1.LineStatus = 'O' AND T1.ShipDate <= @dueDate

)U0

GROUP BY ItemCode 

)T13 ON T10.ItemCode = T13.ItemCode

LEFT JOIN 

(

SELECT ItemCode,SUM(QtyNeed) QtyNeed0,SUM(QtyIssued) QtyIssued0

FROM #TEMP_NEED_ALL 

GROUP BY ItemCode

)T14 ON T10.ItemCode = T14.ItemCode 

LEFT JOIN 

(

SELECT DISTINCT ItemCode ,[LEVEL] FROM #BOM

)T15 ON T10.ItemCode = T15.ItemCode

ORDER BY T10.ItemCode

FOR BROWSE

/* 数据追溯

SELECT * FROM #TEMP_NEED_SUM WHERE ItemCode IN ('TDB2112-00-41C-5030','2DB2112-00-41A-000', '6G03-DB2112-01-41A0')

SELECT * FROM #TEMP_NEED_ALL WHERE ItemCode IN ('TDB2112-00-41C-5030','2DB2112-00-41A-000', '6G03-DB2112-01-41A0')

*/

DROP TABLE #TEMP_NEED_SUM

DROP TABLE #TEMP_NEED_ALL

DROP TABLE #TMP_ONHAND

DROP TABLE #BOM

END