query to get po matched invoices in oracle apps

Query to get po matched invoices in oracle apps

Here you fill the sql script to get the po matched invoices in oracle apps. This sql querry will give you the list of the invoices which we have matched with the purchase order in oracle apps. I am sharing the table involved in this sql query. When we match the purchase order with the Payables Invoice then PO_DISTRIBUTION_ID of the purchase order stores in the ap_invoice_distributions_all of the Invoice.

 

Tables Involved.

po_headers_all

po_distributions_all

ap_invoices_all

 

 

sql query to get po matched invoices in oracle apps

 

SELECT distinct pha.segment1 po_number

       ,aia.invoice_num invoice_number

       ,rsh.receipt_num receipt_number

  FROM po_headers_all pha

       ,po_distributions_all pda

       ,ap_invoice_distributions_all aid

       ,ap_invoices_all aia

 WHERE pha.po_header_id=pda.po_header_id

   AND aid.po_distribution_id=pda.po_distribution_id

   AND aia.invoice_id=aid.invoice_id

   AND pha.segment1=nvl(:P_PO_NUM,pha.segment1)

   AND aia.invoice_num=nvl(:P_INVOICE_NUM,aia.invoice_num)

 order by 2

;