[E-Bussiness Suite] Query PO - RR - Voucher(INV) - Voucher(PAYMENT)

Deskripsi
   Query untuk melisting PO beserta dengan RECEIPT NUMBER , VOUCHER(INVOICE) , VOUCHER(PAYMENT).

Howto
1. Karena kita menggunakan view yang recordnya di hidden oleh oracle maka untuk melihatnya gunakan cara INI
2. Jalankan Query dibawah

select  distinct PRHA.segment1 as PO_NUMBER
       ,RVHV.RECEIPT_NUM as RECEIPT_NUMBER
       ,AI.DOC_SEQUENCE_VALUE as VOUCHER_INVOICE
       ,AC.DOC_SEQUENCE_VALUE AS VOUCHER_PAYMENT
 
from PO_HEADERS_ALL PRHA
    ,RCV_SHIPMENT_LINES RSL
    ,RCV_VRC_HDS_V RVHV
    ,AP_INVOICES AI
    ,AP_INVOICE_PAYMENTS_V AIP
    ,AP_CHECKS AC
 
where PRHA.segment1 = '129752'
AND PRHA.po_header_id = RSL.po_header_id
AND RSL.shipment_header_id = RVHV.shipment_header_id
AND AI.invoice_id IN (SELECT apid.invoice_id FROM ap_invoice_distributions apid, po_distributions pod WHERE apid.po_distribution_id = pod.po_distribution_id AND pod.po_header_id = prha.po_header_id AND (pod.po_release_id = '' OR '' is null) AND (pod.po_line_id = '' OR '' is null) AND (pod.line_location_id = '' OR '' is null) AND (pod.po_distribution_id = '' OR '' is null))
AND AI.INVOICE_ID = AIP.INVOICE_ID
AND AIP.CHECK_ID = AC.CHECK_ID;

3. jika masih bermasalah tidak menampilkan record dan menggunakan software Toad coba baca INI

4. jika hasil query ga cocok tolong infokan lewat comment ntar ane cek lagi :D, terima kasih.


--Update Query,
   1. Add Description of PO
   2. Filter by Date Range, Month and Year


select  distinct prha.po_header_id
       ,PRHA.segment1 as PO_NUMBER
       ,PRHA.comments as Description
       ,RVHV.RECEIPT_NUM as RECEIPT_NUMBER
       ,AI.INVOICE_NUM as NUMBER_INVOICE
       ,AI.DOC_SEQUENCE_VALUE as VOUCHER_INVOICE
       ,AC.DOC_SEQUENCE_VALUE AS VOUCHER_PAYMENT
       ,to_char(PDV.GL_ENCUMBERED_DATE,'dd/mm/yyyy') as GL_DATE
     
from PO_HEADERS_ALL PRHA
    ,PO_DISTRIBUTIONS_V PDV
    ,RCV_SHIPMENT_LINES RSL
    ,RCV_VRC_HDS_V RVHV
    ,AP_INVOICES AI
    ,AP_INVOICE_PAYMENTS_V AIP
    ,AP_CHECKS AC
 
where
--PRHA.segment1 = '129752'
PRHA.po_header_id = PDV.po_header_id
AND PRHA.po_header_id = RSL.po_header_id
AND RSL.shipment_header_id = RVHV.shipment_header_id
AND AI.invoice_id IN (SELECT apid.invoice_id FROM ap_invoice_distributions apid, po_distributions pod WHERE apid.po_distribution_id = pod.po_distribution_id AND pod.po_header_id = prha.po_header_id AND (pod.po_release_id = '' OR '' is null) AND (pod.po_line_id = '' OR '' is null) AND (pod.line_location_id = '' OR '' is null) AND (pod.po_distribution_id = '' OR '' is null))
AND AI.INVOICE_ID = AIP.INVOICE_ID
AND AIP.CHECK_ID = AC.CHECK_ID
-- Low Date Month
AND to_number(to_char(PDV.GL_ENCUMBERED_DATE,'mm')) >= '1'
-- High Date Month
AND to_number(to_char(PDV.GL_ENCUMBERED_DATE,'mm')) <= '12'
-- Year Date
AND to_char(PDV.GL_ENCUMBERED_DATE,'yyyy') = '2010'