用友U8在制订单缺料分析.sql

select Row_Number() over(order by 母件编码,子件编码) as id ,szdc_BOM.*,
ap.apqty as '请购数',po.poqty as '采购在途',om.omqty as '委外在途',mo.moqty as '生产在制',omm.ommqty as '委外未领数',mom.momqty as '生产未领数' into #a from szdc_BOM  left join (select cInvCode,sum(isnull(fQuantity,0)-isnull(iReceivedQTY,0)) as apqty from PU_AppVouch a inner join PU_AppVouchs b on a.ID=b.ID where isnull(iReceivedQTY,0)<isnull(fQuantity,0) and cCloser is null and cbcloser is null group by cInvCode ) ap on szdc_BOM.子件编码=ap.cInvCode left join (select cInvCode, sum(isnull(iQuantity,0)-isnull(ireceivedqty,0)-isnull(freceivedqty,0)) as poqty from po_pomain a inner join po_podetails b on a.POID=b.POID where  isnull(ireceivedqty,0) isnull(freceivedqty,0)<isnull(iQuantity,0) and cCloser is null and cbCloser is null group by cInvCode ) po on szdc_BOM.子件编码=po.cInvCode left join (select InvCode,SUM(ISNULL(qty,0)-isnull(QualifiedInQty,0)) as moqty from mom_orderdetail where Status<>'4' and ISNULL(QualifiedInQty,0)<ISNULL(qty,0) group by InvCode) mo on szdc_BOM.子件编码=mo.InvCode left join (select b.InvCode,sum(isnull(b.Qty ,0)-ISNULL(IssQty,0)) as momqty  from mom_orderdetail a left join mom_moallocate b on b.modid=a.modid where isnull(IssQty,0)<isnull(b.qty,0) and Status='3' group by b.invcode ) mom on szdc_BOM.子件编码=mom.InvCode left join (select cInvCode,sum(isnull(iQuantity,0)-ISNULL(freceivedqty,0)-ISNULL(iReceivedQTY,0)) as omqty from OM_MOMain a inner join OM_MODetails b on a.MOID=b.MOID where isnull(freceivedqty,0) ISNULL(iReceivedQTY,0)<isnull(iQuantity,0) and cCloser is null and cbCloser is null group by cInvCode) om on om.cInvCode=szdc_BOM.子件编码 left join (select b.cInvCode,sum(isnull(b.iQuantity,0)-ISNULL(iSendQTY,0)) as ommqty from OM_MODetails a left join om_momaterials b on b.MoDetailsID=a.MODetailsID inner join OM_MOMain c on a.MOID=c.MOID where isnull(iSendQTY,0)<isnull(b.iQuantity,0) and cbCloser is null and cCloser is null group by b.cInvCode) omm on szdc_BOM.子件编码=omm.cInvCode where 单据号=@v_ccode

select a.*,
       (select sum(isnull(订单用量,0))
        from #A b
        where b.子件编码=a.子件编码 and b.id<=a.id) 'qty'
 into #b from #a a
 
  select (case when isnull(isnull(c.iquantity,0)-isnull(b.qty,0)-isnull(委外未领数,0)-isnull(生产未领数,0),0)>0 then 0 else isnull(isnull(c.iquantity,0)-isnull(b.qty,0)-isnull(委外未领数,0)-isnull(生产未领数,0),0) end) as '短缺数量',c.iquantity as '现存量',b.* into #c from #b b left join (select cInvCode,SUM(ISNULL(iquantity,0)) as iquantity from currentstock group by cinvcode ) c on b.子件编码=c.cinvcode
 
 select 子件编码,子件名称,子件规格,单位,Min(短缺数量) as '短缺数量',请购数,采购在途,委外在途,生产在制,委外未领数,生产未领数,现存量 from #c where 短缺数量<0 group by 子件编码,子件名称,子件规格,单位,请购数,采购在途,委外在途,生产在制,委外未领数,生产未领数,现存量