Ratakan

Informations :

SP untuk mencari data PO Status, Receivings berdasarkan Cutoff PO Date

Declare @CUTOFFDATE DATETIME
SET @CUTOFFDATE = '2019-06-30'    --Tentukan Tanggal PO Cutoff disini ( Contoh di ambil tanggal 30 Juni 2019 ) 

select distinct a.PONUMBER as 'PO Number',c.DOCDATE as 'PO Date',

CASE c.POSTATUS
    when 1 then 'New'
    when 2 then 'Released'
    when 3 then 'Change Order'
    when 4 then 'Received'
    when 5 then 'Closed'
    when 6 then 'Cancelled'
    else ''
    end 'PO Status',

b.POPRCTNM as 'Receipt Number',b.receiptdate as 'Receipt Date' from POP10500 a
left join (select POPRCTNM,receiptdate from POP10300 
   union 
     select POPRCTNM,receiptdate from POP30300 ) b on a.POPRCTNM = b.POPRCTNM 
left join (select PONUMBER,POSTATUS,DOCDATE from POP10100 union 
   select PONUMBER,POSTATUS,DOCDATE from POP30100 ) c on a.PONUMBER = c.PONUMBER

where a.PONUMBER <> ''
and c.DOCDATE <= @CUTOFFDATE -- Notes Parameter Cutoff adalah Tanggal PO
order by b.receiptdate desc




Hasilnya sebagai berikut :


No comments