How to get po number from invoice number in oracle apps

How to get po number from invoice number in oracle apps

Here below , I am sharing the sql query in which , we need to pass the AP invoice number and we can get the Matched PO number against this invoice. We can only get the po number from the AP invoice when the PO will be matched with the Invoice.

 

Tables Involved

 

po_headers_all

po_distributions_all

ap_invoice_distributions_all

ap_invoices_all

 

 

 

sql to get po number from invoice number 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 aia.invoice_num=nvl(:P_INVOICE_NUM,aia.invoice_num)

 order by 2

;