库存在各生产订单间分配

浅浅的花香味﹌ 2021-09-17 09:02 244阅读 0赞

IF OBJECT\_ID('Tempdb..\#T1','U') IS NOT NULL DROP TABLE \#T1  
IF OBJECT\_ID('Tempdb..\#T1','U') IS NOT NULL DROP TABLE \#T2  
IF OBJECT\_ID('Tempdb..\#TEMP','U') IS NOT NULL DROP TABLE \#TEMP  
DECLARE @guid VARCHAR(100)  
SET @guid = NEWID()  
EXEC \[U\_P\_STOCK2NEED\_TABLE\] @guid  
SELECT \* INTO \#TEMP FROM U\_T\_STOCK2NEED WHERE \[GUID\] = @guid

SELECT T0.DocEntry,  
ROW\_NUMBER()OVER(PARTITION BY T1.ItemCode ORDER BY T0.DueDate) Lineid,  
T0.Status,T0.PostDate,T0.DueDate,T0.PickRmrk ,T0.ItemCode Fanther,T0.PlannedQty FtPlanQty,T0.CmpltQty,T0.U\_QtyEnd,  
T1.LineNum,T1.ItemCode ChildCode,T1.PlannedQty ChdPlanQty,T1.IssuedQty,T1.PlannedQty-T1.IssuedQty OrderQty,  
ISNULL(U0.可用库存,0) ValidQty,  
0 AS QtyFP,  
ISNULL(U0.可用库存,0) AS SurplQty,  
U0.\[溢缺.库存\],  
U0.\[采购/生产在途\] ,  
CASE WHEN U0.\[溢缺.库存\]>0 THEN 0  
WHEN U0.\[溢缺.库存\]<0 AND (U0.\[溢缺.库存\]+U0.\[采购/生产在途\])>0 THEN 0  
WHEN U0.\[溢缺.库存\]<0 AND (U0.\[溢缺.库存\]+U0.\[采购/生产在途\])<0 THEN (U0.\[溢缺.库存\]+U0.\[采购/生产在途\])  
END 待生产量

INTO \#T1  
FROM OWOR T0  
JOIN WOR1 T1 ON T0.DocEntry =T1.DocEntry  
JOIN OITM R0 ON T0.ItemCode =R0.ItemCode  
JOIN OITM R1 ON T1.ItemCode =R1.ItemCode  
LEFT JOIN \#TEMP U0 ON T1.ItemCode =U0.物料编号  
WHERE R0.QryGroup49='Y' AND T0.Status IN ('R','P') AND (T1.PlannedQty-T1.IssuedQty)>0  
ORDER BY T0.ItemCode,T0.DueDate

\--逐行累计数量（含本行）  
SELECT T0.DocEntry,  
T0.Lineid,  
T0.ChildCode,  
MAX(T0.OrderQty) OrderQty,  
MAX(T0.SurplQty) SurplQty,  
SUM(T1.OrderQty) NeedQty,  
CASE WHEN MAX(T0.SurplQty)-SUM(T1.OrderQty)>=0 THEN MAX(T0.SurplQty)-SUM(T1.OrderQty)  
ELSE 0 END KfbQty  
INTO \#T2  
FROM \#T1 T0  
LEFT JOIN \#T1 T1 ON T0.ChildCode = T1.ChildCode AND T0.\[Lineid\] >= T1.Lineid  
GROUP BY T0.DocEntry,T0.Lineid,T0.ChildCode

UPDATE \#T1 SET QtyFP = CAST(T21.QtyFP AS DECIMAL(19,6)),SurplQty=T21.KfbQty  
FROM \#T1 T20  
LEFT JOIN (  
\--区间划分确定是否足够分配 累计量比总量大 随便分; 总量在上行累计量和本行累计量之间 总量-上行累计量 分一点；总量小于上行累计量，没的分。  
SELECT T10.DocEntry,  
T10.Lineid,  
T10.ChildCode,  
CASE WHEN T10.KfbQty >0 THEN T10.OrderQty  
WHEN T10.KfbQty >0 AND T10.KfbQty<T10.NeedQty THEN T10.KfbQty  
WHEN T10.KfbQty=0 AND T10.KfbQty<T10.NeedQty THEN ISNULL(T11.KfbQty,0)  
ELSE 0.00 END \[QtyFP\],  
T10.KfbQty  
FROM \#T2 T10 LEFT JOIN \#T2 T11 ON T10.ChildCode =T11.ChildCode AND T11.Lineid =T10.Lineid-1  
)T21 ON T20.Lineid= T21.Lineid AND T20.ChildCode =T21.ChildCode

SELECT U0.DocEntry 生产订单号  
,U0.Lineid 行号  
,CASE WHEN U0.Status='R' THEN '已审核' ELSE '已计划' END 状态  
,U0.PostDate 订单日期  
,U0.DueDate 完工日期  
,U0.PickRmrk 业务订单号  
,U0.Fanther 产品编号  
,U0.FtPlanQty 订单量  
,U0.CmpltQty 完工量  
,U0.U\_QtyEnd 最终完工  
,U0.LineNum 子件行号  
,U0.ChildCode 子件料号  
,U0.ChdPlanQty 计划发料  
,U0.IssuedQty 实际已发  
,U0.OrderQty 待发料量  
,U0.ValidQty 可用库存  
,U0.QtyFP 分配量  
,U0.SurplQty 剩余分配量  
,U0.\[溢缺.库存\] 总库存  
,U0.\[采购/生产在途\] 总在途  
,U0.待生产量 待补工单量  
,U1.ItemName 子件名称  
FROM \#T1 U0  
LEFT JOIN OITM U1 ON U0.ChildCode =U1.ItemCode  
ORDER BY U0.DueDate

DROP TABLE \#TEMP  
DROP TABLE \#T1  
DROP TABLE \#T2