2022-01-04

select
to_char(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),0),'yyyyMM')  month1
,to_char(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),1),'yyyyMM')  month2
,to_char(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),2),'yyyyMM')  month3
,to_char(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),3),'yyyyMM')  month4
,to_char(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),4),'yyyyMM')  month5
,to_char(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),5),'yyyyMM')  month6
,to_char(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),6),'yyyyMM')  month7
,to_char(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),7),'yyyyMM')  month8
,to_char(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),8),'yyyyMM')  month9
,to_char(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),9),'yyyyMM')  month10
,to_char(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),10),'yyyyMM') month11
,to_char(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),11),'yyyyMM') month12
,to_char(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),-1),'yyyyMM') month13
,to_char(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),-2),'yyyyMM') month14
,to_char(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),-3),'yyyyMM') month15
,to_char(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),-4),'yyyyMM') month16
,to_char(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),-5),'yyyyMM') month17
,to_char(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),-6),'yyyyMM') month18
,to_char(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),-7),'yyyyMM') month19
,to_char(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),-8),'yyyyMM') month20
,to_char(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),-9),'yyyyMM') month21
,to_char(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),-10),'yyyyMM') month22
,to_char(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),-11),'yyyyMM') month23
,AA.pr_orispeccode
,cu_name
,em_name
,(nvl(BA_REMAIN,0)+nvl(zt_qty12,0) )  total
,nvl(BA_REMAIN,0) BA_REMAIN
,nvl(zt_qty0,0) zt_qty0
,nvl(zt_qty1,0) zt_qty1
,nvl(zt_qty2,0) zt_qty2
,nvl(zt_qty3,0) zt_qty3
,nvl(zt_qty4,0) zt_qty4
,nvl(zt_qty5,0) zt_qty5
,nvl(zt_qty6,0) zt_qty6
,nvl(zt_qty7,0) zt_qty7
,nvl(zt_qty8,0) zt_qty8
,nvl(zt_qty9,0) zt_qty9
,nvl(zt_qty10,0) zt_qty10
,nvl(zt_qty11,0) zt_qty11
,nvl(zt_qty12,0) zt_qty12
,nvl(wjdd_qty,0) wjdd_qty
,nvl(jh_qty0,0) jh_qty0
,nvl(jh_qty1,0) jh_qty1
,nvl(jh_qty2,0) jh_qty2
,nvl(jh_qty3,0) jh_qty3
,nvl(jh_qty4,0) jh_qty4
,nvl(jh_qty5,0) jh_qty5
,nvl(ch_qty0,0) ch_qty0
,nvl(ch_qty1,0) ch_qty1
,nvl(ch_qty2,0) ch_qty2
,nvl(ch_qty3,0) ch_qty3
,nvl(ch_qty4,0) ch_qty4
,nvl(ch_qty5,0) ch_qty5
,nvl(ch_qty6,0) ch_qty6
,nvl(ch_qty7,0) ch_qty7
,nvl(ch_qty8,0) ch_qty8
,nvl(ch_qty9,0) ch_qty9
,nvl(ch_qty10,0) ch_qty10
,nvl(ch_qty11,0) ch_qty11
    from (select pr_orispeccode,nvl(SELLERCODE,PRODCODE) SELLERCODE,nvl(CUSTCODE,PRODCODE) CUSTCODE from
         ( select BA_PRODCODE PRODCODE, BA_SELLERCODE SELLERCODE,BA_CUSTCODE CUSTCODE from batch
        union all select sd_prodcode PRODCODE, sa_sellercode SELLERCODE,sa_custcode CUSTCODE   FROM Sale left join  SaleDetail  on sa_id=sd_said where sa_status='已审核'        and sd_status <> '已结案'
        union all select pd_prodcode PRODCODE, pd_sellercode SELLERCODE,pd_custcode CUSTCODE FROM   purchase left join Purchasedetail on pu_id=pd_puid   where    nvl(pd_mrpstatus,'已审核') = '已审核' AND PU_STATUS='已审核' 
         union all select sd_prodcode PRODCODE, sf_sellercode SELLERCODE,sf_custcode CUSTCODE FROM SaleForecast left join SaleForecastDetail  on sf_id=sd_sfid where sf_status='已审核'      
        union all select pd_prodcode PRODCODE,pi_sellercode SELLERCODE,pi_cardcode CUSTCODE FROM prodinout left join  prodioDetail  on pi_id=pd_piid where pi_status = '已过账' and pi_class in ('出货单','销售退货单')        
         ) left join PRODUCT ON PRODCODE=PR_CODE
          group by  pr_orispeccode,nvl(SELLERCODE,PRODCODE),nvl(CUSTCODE,PRODCODE) ) AA
    LEFT JOIN CUSTOMER ON AA.CUSTCODE=CU_CODE 
    LEFT JOIN EMPLOYEE ON EM_CODE=AA.SELLERCODE
    left join 
     ( select pr_orispeccode pr_orispeccode2,BA_SELLERCODE,BA_CUSTCODE ,BA_SELLER,BA_CUSTNAME, sum(BA_REMAIN)  BA_REMAIN FROM batch left join PRODUCT on pr_code =BA_PRODCODE
             group by pr_orispeccode,BA_SELLERCODE,BA_CUSTCODE,BA_SELLER,BA_CUSTNAME 
    ) NN 
     ON AA.pr_orispeccode=NN.pr_orispeccode2 AND AA.SELLERCODE=NVL(NN.BA_SELLERCODE,AA.pr_orispeccode) AND AA.CUSTCODE =NVL(NN.BA_CUSTCODE,AA.pr_orispeccode) --库存 

     Left join   ( SELECT  UU.pd_prodcode pr_orispeccode3,UU.pd_sellercode,UU.pd_custcode,
     sum(CASE WHEN  TRUNC(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),0),'mm')>=trunc(PU_DATE,'mm') THEN  UU.pd_qty else 0 end) zt_qty0,      
     sum(CASE WHEN  TRUNC(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),1),'mm')=trunc(PU_DATE,'mm') then  UU.pd_qty else 0 end)  zt_qty1,
     sum(CASE WHEN  TRUNC(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),2),'mm')=trunc(PU_DATE,'mm') then  UU.pd_qty else 0 end)  zt_qty2,
     sum(CASE WHEN  TRUNC(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),3),'mm')=trunc(PU_DATE,'mm') then  UU.pd_qty else 0 end)  zt_qty3,
     sum(CASE WHEN  TRUNC(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),4),'mm')=trunc(PU_DATE,'mm') THEN   UU.pd_qty else 0 end) zt_qty4,      
     sum(CASE WHEN  TRUNC(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),5),'mm')=trunc(PU_DATE,'mm') then  UU.pd_qty else 0 end)  zt_qty5,
     sum(CASE WHEN  TRUNC(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),6),'mm')=trunc(PU_DATE,'mm') then  UU.pd_qty else 0 end)  zt_qty6,
     sum(CASE WHEN  TRUNC(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),7),'mm')=trunc(PU_DATE,'mm') then  UU.pd_qty else 0 end)  zt_qty7,
     sum(CASE WHEN  TRUNC(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),8),'mm')=trunc(PU_DATE,'mm') THEN   UU.pd_qty else 0 end) zt_qty8,      
     sum(CASE WHEN  TRUNC(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),9),'mm')=trunc(PU_DATE,'mm') then  UU.pd_qty else 0 end)  zt_qty9,
     sum(CASE WHEN  TRUNC(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),10),'mm')=trunc(PU_DATE,'mm') then  UU.pd_qty else 0 end) zt_qty10,
     sum(CASE WHEN  TRUNC(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),11),'mm')=trunc(PU_DATE,'mm') then  UU.pd_qty else 0 end) zt_qty11,    
     sum(UU.pd_qty) zt_qty12
     FROM ( select pu_date,pr_orispeccode pd_prodcode,pd_sellercode,pd_custcode,NVL(pd_qty,0)-NVL(pd_acceptqty,0)  pd_qty FROM  purchase left join Purchasedetail on pu_id=pd_puid left join PRODUCT on pr_code =pd_PRODCODE  where    nvl(pd_mrpstatus,'已审核') = '已审核'  AND PU_STATUS='已审核'   
     ) UU   GROUP BY  UU.pd_prodcode,UU.pd_sellercode,UU.pd_custcode  ) TT   
     
     on AA.pr_orispeccode=TT.pr_orispeccode3 and  AA.SELLERCODE=nvl(pd_sellercode,AA.pr_orispeccode)  and AA.CUSTCODE=nvl(pd_custcode,AA.pr_orispeccode)  --在途

     left join ( select pr_orispeccode pr_orispeccode4,sa_sellercode,sa_custcode,SUM(NVL(sd_qty,0) - NVL(sd_sendqty,0) ) wjdd_qty FROM Sale left join SaleDetail 
     left join PRODUCT on pr_code =SD_PRODCODE 
     on sa_id=sd_said where sa_status='已审核'  and sd_status <> '已结案'   AND trunc(to_date($P{DATE},'yyyyMM'),'mm') >= trunc(sa_date,'mm')  
     GROUP BY  pr_orispeccode,sa_sellercode,sa_custcode )     KK 
             on AA.pr_orispeccode=KK.pr_orispeccode4 and  AA.SELLERCODE=nvl(KK.sa_sellercode,AA.pr_orispeccode)  and AA.CUSTCODE=nvl(KK.sa_custcode,AA.pr_orispeccode) --采购未交订单

     Left join   (SELECT  WW.rbd_prodcode,WW.rb_sellercode,WW.RBD_CUSTCODE,
     sum(CASE WHEN  trunc(to_date($P{DATE},'yyyyMM'),'MM')>=trunc(RB_DATE,'MM') THEN               WW.sd_qty else 0 end) jh_qty0,     
     sum(CASE WHEN  trunc(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),1),'MM')=trunc(RB_DATE,'MM') then  WW.sd_qty else 0 end) jh_qty1,
     sum(CASE WHEN  trunc(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),2),'MM')=trunc(RB_DATE,'MM') then  WW.sd_qty else 0 end) jh_qty2,
     sum(CASE WHEN  trunc(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),3),'MM')=trunc(RB_DATE,'MM') then  WW.sd_qty else 0 end) jh_qty3,
     sum(CASE WHEN  trunc(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),4),'MM')=trunc(RB_DATE,'MM') then  WW.sd_qty else 0 end) jh_qty4,
     sum(CASE WHEN  trunc(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),5),'MM')=trunc(RB_DATE,'MM') then  WW.sd_qty else 0 end) jh_qty5   
        FROM     (select sd_needdate rb_date,pr_orispeccode rbd_prodcode,sf_sellercode rb_sellercode,sf_custcode RBD_CUSTCODE,sd_qty FROM SaleForecast left join  SaleForecastDetail  on sf_id=sd_sfid 
        left join PRODUCT on pr_code =sd_prodcode where sf_status<>'在录入' and trunc(sysdate,'DD')<=TRUNC(sd_enddate,'DD')
        )WW   GROUP BY  WW.rbd_prodcode,WW.rb_sellercode,WW.RBD_CUSTCODE  )VV   
     on AA.pr_orispeccode=VV.rbd_prodcode and  AA.SELLERCODE=nvl(rb_sellercode,rbd_prodcode)  and AA.CUSTCODE=nvl(RBD_CUSTCODE,rbd_prodcode)  --计划       

Left join   ( SELECT  RR.pd_prodcode,RR.pi_sellercode,RR.pi_cardcode,
     sum(CASE WHEN  trunc(ADD_MONTHS(to_date('201908','yyyyMM'),0),'mm')>=trunc(pi_date,'mm') THEN   RR.pd_qty else 0 end) ch_qty0,      
     sum(CASE WHEN  trunc(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),-1),'mm')=trunc(pi_date,'mm') then  RR.pd_qty else 0 end) ch_qty1,
     sum(CASE WHEN  trunc(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),-2),'mm')=trunc(pi_date,'mm') then  RR.pd_qty else 0 end) ch_qty2,
     sum(CASE WHEN  trunc(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),-3),'mm')=trunc(pi_date,'mm') then  RR.pd_qty else 0 end) ch_qty3, 
     sum(CASE WHEN  trunc(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),-4),'mm')=trunc(pi_date,'mm') then  RR.pd_qty else 0 end) ch_qty4,
     sum(CASE WHEN  trunc(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),-5),'mm')=trunc(pi_date,'mm') then  RR.pd_qty else 0 end) ch_qty5,
     sum(CASE WHEN  trunc(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),-6),'mm')>=trunc(pi_date,'mm') THEN   RR.pd_qty else 0 end) ch_qty6,      
     sum(CASE WHEN  trunc(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),-7),'mm')=trunc(pi_date,'mm') then  RR.pd_qty else 0 end) ch_qty7,
     sum(CASE WHEN  trunc(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),-8),'mm')=trunc(pi_date,'mm') then  RR.pd_qty else 0 end) ch_qty8,
     sum(CASE WHEN  trunc(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),-9),'mm')=trunc(pi_date,'mm') then  RR.pd_qty else 0 end) ch_qty9, 
     sum(CASE WHEN  trunc(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),-10),'mm')=trunc(pi_date,'mm') then  RR.pd_qty else 0 end) ch_qty10,
     sum(CASE WHEN  trunc(ADD_MONTHS(to_date($P{DATE},'yyyyMM'),-11),'mm')=trunc(pi_date,'mm') then  RR.pd_qty else 0 end) ch_qty11
FROM     (select pi_date,pr_orispeccode pd_prodcode,pi_sellercode,pi_cardcode,NVL(pd_outqty,0)-nvl(pd_inqty,0)  pd_qty FROM prodinout left join  prodioDetail  on pi_id=pd_piid 
     left join PRODUCT on pr_code =pd_prodcode where pi_status = '已过账' and pi_class in ('出货单','销售退货单')    
     )RR   GROUP BY  RR.pd_prodcode,RR.pi_sellercode,RR.pi_cardcode  )QQ   
      on AA.pr_orispeccode=QQ.pd_prodcode and  AA.SELLERCODE=nvl(QQ.pi_sellercode,pd_prodcode)  and AA.CUSTCODE=nvl(QQ.pi_cardcode,pd_prodcode) --出货数量
$P!{WHERE_CONDITION} order by em_code,cu_code;