短缺材料统计查询

dannis

SELECT QQQ.KHDM,QQQ.KHMC,PPP.GDRQ,QQQ.CPDM,QQQ.WLQD,QQQ.GG,QQQ.CPMC,QQQ.DMBZ,QQQ.JHSL,QQQ.JHXS,PPP.CLDM,PPP.CLMC,PPP.XQSL,PPP.KYSL-PPP.XQSL,
PPP.KCSL,PPP.ZYSL,PPP.GDH,PPP.GDZT,PPP.GDZZ FROM
(
SELECT MFGHEAD.ZMFGSTA_0 AS GDZZ,MFGHEAD.STRDAT_0 AS GDRQ,MFGHEAD.MFGSTA_0 AS GDZT,MFGHEAD.ZMFGSTA_0 AS QR,FFF.CLDM,FFF.XQSL,FFF.CLMC,NVL(FFF.KCSL,0) AS KCSL,NVL(FFF.ZYSL,0) AS ZYSL,NVL(FFF.KYSL,0) AS KYSL,FFF.GDH FROM
(
SELECT DDD.CLDM,DDD.CLMC AS CLMC,CCC.KYSL,CCC.KCSL,CCC.ZYSL,DDD.GDH,DDD.XQSL FROM
(SELECT TTT.GDH,TTT.CLDM,ITMMASTER.ITMDES1_0 AS CLMC,TTT.XQSL FROM
(SELECT MFGMAT.MFGNUM_0 AS GDH,MFGMAT.ITMREF_0 AS CLDM,MFGMAT.RETQTY_0 AS XQSL FROM MFGMAT
INNER JOIN MFGOPE
ON MFGMAT.MFGNUM_0=MFGOPE.MFGNUM_0
WHERE MFGOPE.XMMSTA_0<2 )TTT
INNER JOIN
ITMMASTER
ON TTT.CLDM=ITMMASTER.ITMREF_0)DDD
LEFT JOIN
(
SELECT EEE.CPDM,ITMMASTER.ITMDES1_0 AS CLMC,EEE.KYSL,EEE.KCSL,EEE.ZYSL FROM
(
SELECT BBB.CPDM,BBB.KYSL,BBB.KCSL,BBB.ZYSL, ITMBPS.BPSNUM_0 FROM
(
SELECT CPDM,NVL(SUM(KCSL) ,0) AS KCSL,NVL(SUM(XQSL),0)  AS ZYSL,SUM(NVL(KCSL,0)-NVL(XQSL,0)) AS KYSL FROM
(SELECT ITMREF_0 AS CPDM,SUM(QTYSTU_0) AS KCSL,0 AS XQSL  FROM STOCK WHERE SUBSTR(ITMREF_0,1,1) = '2' GROUP BY ITMREF_0
UNION ALL
SELECT MFGMAT.ITMREF_0 AS CPDM,0 AS KCSL,SUM(MFGMAT.RETQTY_0-MFGMAT.USEQTY_0) AS XQSL  FROM MFGOPE
INNER JOIN MFGMAT ON MFGOPE.MFGNUM_0=MFGMAT.MFGNUM_0  AND MFGOPE.OPENUM_0=MFGMAT.BOMOPE_0 AND MFGOPE.OPESPLNUM_0=0
WHERE MFGMAT.MATSTA_0<=2 AND MFGOPE.XMMSTA_0=1 AND SUBSTR(MFGMAT.ITMREF_0,1,1) = '2'
GROUP BY  MFGMAT.ITMREF_0)AAA
GROUP BY CPDM
)BBB
left JOIN
ITMBPS
ON BBB.CPDM=ITMBPS.ITMREF_0
WHERE (BBB.KYSL<=0 OR BBB.KYSL IS NULL)  AND (ITMBPS.BPSNUM_0<>'S14017' AND ITMBPS.BPSNUM_0<>'S10125' AND ITMBPS.BPSNUM_0<>'S10118' or  ITMBPS.BPSNUM_0 is null)
)EEE
left JOIN
ITMMASTER
ON EEE.CPDM=ITMMASTER.ITMREF_0
)CCC
ON DDD.CLDM=CCC.CPDM
)FFF
left JOIN
MFGHEAD
ON FFF.GDH=MFGHEAD.MFGNUM_0
WHERE MFGHEAD.ZMFGSTA_0<=2
)PPP
RIGHT JOIN
(SELECT  HHH.GDH,HHH.KHDM,BPCUSTOMER.BPCNAM_0 AS KHMC,HHH.CPDM,HHH.CPMC,HHH.GG,HHH.WLQD,HHH.JHSL,HHH.DMBZ,HHH.JHXS FROM
(SELECT GGG.DDH,GGG.KHDM,GGG.GDH,GGG.CPDM,ITMMASTER.ITMDES1_0 AS CPMC,ITMMASTER.ITMSTD_0 AS GG,GGG.WLQD,GGG.JHSL,GGG.DMBZ,
(GGG.JHSL/ITMMASTER.PUUSTUCOE_0) AS JHXS FROM
(SELECT SORDER.SOHNUM_0 AS DDH,SORDER.ZBPCORD_0 AS KHDM,MFGITM.ITMREF_0 AS CPDM,MFGITM.BOMALT_0 AS WLQD,MFGITM.XCODREM_0 AS DMBZ,
MFGITM.UOMEXTQTY_0 AS JHSL,MFGITM.MFGNUM_0 AS GDH  FROM MFGITM
LEFT JOIN SORDER ON MFGITM.VCRNUMORI_0=SORDER.SOHNUM_0
 WHERE SUBSTR(MFGITM.ITMREF_0,1,1)='1') GGG
 INNER JOIN ITMMASTER
 ON GGG.CPDM=ITMMASTER.ITMREF_0
 )HHH
 LEFT  JOIN
 BPCUSTOMER
 ON HHH.KHDM=BPCUSTOMER.BPCNUM_0
)QQQ
ON PPP.GDH=QQQ.GDH
WHERE PPP.GDZT<3 AND PPP.KYSL<0  AND SUBSTR(PPP.CLDM,1,1)='2'
ORDER BY PPP.GDRQ DESC

LiYa

你这个短缺材料是什么概念？？针对生产的原料短缺，还是 什么？？ 请给个说明啊，别光贴语句啊。

dannis

这个短缺材料功能是客户要求二次开发，算法有可能同其他客户要求不一样，

在生产工单中，增加了这一个功能，算法如下。

/*计算生产工单可用数量公式: 某材料库存总量-某材料要求总量+本工单某材料要求量=本工单某材料可用量*/
SELECT MFGMAT.ITMREF_0 AS 产品编码,SUM(MFGMAT.RETQTY_0-MFGMAT.USEQTY_0) AS 要求总量 FROM MFGOPE
INNER JOIN MFGMAT ON MFGOPE.MFGNUM_0=MFGMAT.MFGNUM_0 AND MFGOPE.OPENUM_0=MFGMAT.BOMOPE_0 AND MFGOPE.OPESPLNUM_0=0
WHERE MFGMAT.MATSTA_0<=2 AND MFGOPE.XMMSTA_0=2 AND MFGMAT.ITMREF_0='214046' AND MFGMAT.MFGNUM_0 <> 'WF08123102460'
GROUP BY MFGMAT.ITMREF_0;
/*计算产品材料库存总量*/
SELECT ITMREF_0 AS 材料编码,SUM(QTYSTU_0) AS 库存总量 FROM STOCK WHERE ITMREF_0 = '214046' GROUP BY ITMREF_0;

这个查询是根据要求写的。

LiYa

厉害啊，收藏了。