短缺材料统计查询
<P>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,<BR>PPP.KCSL,PPP.ZYSL,PPP.GDH,PPP.GDZT,PPP.GDZZ FROM<BR>(<BR>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<BR>(<BR>SELECT DDD.CLDM,DDD.CLMC AS CLMC,CCC.KYSL,CCC.KCSL,CCC.ZYSL,DDD.GDH,DDD.XQSL FROM<BR>(SELECT TTT.GDH,TTT.CLDM,ITMMASTER.ITMDES1_0 AS CLMC,TTT.XQSL FROM<BR>(SELECT MFGMAT.MFGNUM_0 AS GDH,MFGMAT.ITMREF_0 AS CLDM,MFGMAT.RETQTY_0 AS XQSL FROM MFGMAT<BR>INNER JOIN MFGOPE<BR>ON MFGMAT.MFGNUM_0=MFGOPE.MFGNUM_0<BR>WHERE MFGOPE.XMMSTA_0<2 )TTT<BR>INNER JOIN<BR>ITMMASTER<BR>ON TTT.CLDM=ITMMASTER.ITMREF_0)DDD<BR>LEFT JOIN<BR>(<BR>SELECT EEE.CPDM,ITMMASTER.ITMDES1_0 AS CLMC,EEE.KYSL,EEE.KCSL,EEE.ZYSL FROM<BR>(<BR>SELECT BBB.CPDM,BBB.KYSL,BBB.KCSL,BBB.ZYSL, ITMBPS.BPSNUM_0 FROM<BR>(<BR>SELECT CPDM,NVL(SUM(KCSL) ,0) AS KCSL,NVL(SUM(XQSL),0) AS ZYSL,SUM(NVL(KCSL,0)-NVL(XQSL,0)) AS KYSL FROM<BR>(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<BR>UNION ALL<BR>SELECT MFGMAT.ITMREF_0 AS CPDM,0 AS KCSL,SUM(MFGMAT.RETQTY_0-MFGMAT.USEQTY_0) AS XQSL FROM MFGOPE<BR>INNER JOIN MFGMAT ON MFGOPE.MFGNUM_0=MFGMAT.MFGNUM_0 AND MFGOPE.OPENUM_0=MFGMAT.BOMOPE_0 AND MFGOPE.OPESPLNUM_0=0<BR>WHERE MFGMAT.MATSTA_0<=2 AND MFGOPE.XMMSTA_0=1 AND SUBSTR(MFGMAT.ITMREF_0,1,1) = '2'<BR>GROUP BY MFGMAT.ITMREF_0)AAA<BR>GROUP BY CPDM<BR>)BBB<BR>left JOIN<BR>ITMBPS<BR>ON BBB.CPDM=ITMBPS.ITMREF_0<BR>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)<BR>)EEE<BR>left JOIN<BR>ITMMASTER<BR>ON EEE.CPDM=ITMMASTER.ITMREF_0<BR>)CCC<BR>ON DDD.CLDM=CCC.CPDM<BR>)FFF<BR>left JOIN<BR>MFGHEAD<BR>ON FFF.GDH=MFGHEAD.MFGNUM_0<BR>WHERE MFGHEAD.ZMFGSTA_0<=2<BR>)PPP<BR>RIGHT JOIN<BR>(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<BR>(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,<BR>(GGG.JHSL/ITMMASTER.PUUSTUCOE_0) AS JHXS FROM<BR>(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,<BR>MFGITM.UOMEXTQTY_0 AS JHSL,MFGITM.MFGNUM_0 AS GDH FROM MFGITM<BR>LEFT JOIN SORDER ON MFGITM.VCRNUMORI_0=SORDER.SOHNUM_0<BR> WHERE SUBSTR(MFGITM.ITMREF_0,1,1)='1') GGG<BR> INNER JOIN ITMMASTER<BR> ON GGG.CPDM=ITMMASTER.ITMREF_0<BR> )HHH<BR> LEFT JOIN<BR> BPCUSTOMER<BR> ON HHH.KHDM=BPCUSTOMER.BPCNUM_0<BR>)QQQ<BR>ON PPP.GDH=QQQ.GDH<BR>WHERE PPP.GDZT<3 AND PPP.KYSL<0 AND SUBSTR(PPP.CLDM,1,1)='2'<BR>ORDER BY PPP.GDRQ DESC<BR></P>Re:短缺材料统计查询
你这个短缺材料是什么概念？？针对生产的原料短缺，还是 什么？？ 请给个说明啊，别光贴语句啊。Re:短缺材料统计查询
<P>这个短缺材料功能是客户要求二次开发，算法有可能同其他客户要求不一样，</P><P>在生产工单中，增加了这一个功能，算法如下。</P><P>/*计算生产工单可用数量公式: 某材料库存总量-某材料要求总量+本工单某材料要求量=本工单某材料可用量*/<BR>SELECT MFGMAT.ITMREF_0 AS 产品编码,SUM(MFGMAT.RETQTY_0-MFGMAT.USEQTY_0) AS 要求总量 FROM MFGOPE <BR>INNER JOIN MFGMAT ON MFGOPE.MFGNUM_0=MFGMAT.MFGNUM_0 AND MFGOPE.OPENUM_0=MFGMAT.BOMOPE_0 AND MFGOPE.OPESPLNUM_0=0<BR>WHERE MFGMAT.MATSTA_0<=2 AND MFGOPE.XMMSTA_0=2 AND MFGMAT.ITMREF_0='214046' AND MFGMAT.MFGNUM_0 <> 'WF08123102460'<BR>GROUP BY MFGMAT.ITMREF_0;<BR>/*计算产品材料库存总量*/<BR>SELECT ITMREF_0 AS 材料编码,SUM(QTYSTU_0) AS 库存总量 FROM STOCK WHERE ITMREF_0 = '214046' GROUP BY ITMREF_0;</P><P>这个查询是根据要求写的。<BR></P>Re:短缺材料统计查询
厉害啊，收藏了。页:
[1]