Query to get non PO Invoices in oracle apps r12

Hi friends, we are going to discuss about the sql query to get non PO Invoices in oracle apps r12. We will share the detail steps which helps to extract those AP Invoices which are not matched with any PO(Purchase order) as well the PO receipts. These types of invoices we do call as non po invoices because these are not match against any oracle PO in oracle apps r12. In this post , We will also share those important tables too which helps to store the Invoices and PO details in Oracle apps r12. Basically to developed the non PO invoices sql query is quite easy in oracle apps r12. Please find below the more detail about Query to get non PO Invoices in oracle apps r12.

Query to get non PO Invoices in oracle apps r12

4 Important Tables to get non PO Invoices in Oracle Apps r12

1.AP_INVOICES_ALL

2.AP_INVOICES_LINES_ALL

3.AP_SUPPLIERS

4.AP_SUPPLIER_SITES_ALL

Detail SQL Query to get non PO Invoices in oracle apps r12

SELECT distinct a_sup.vendor_name,

apss.vendor_site_code,

aia.invoice_num invoice_number,

aia.invoice_date,

aia.invoice_amount

  FROM ap_invoices_all aia,

ap_invoice_lines_all aila,

ap_suppliers a_sup,

ap_supplier_sites_all apss

 WHERE aia.invoice_id=aila.invoice_id

and aia.vendor_id=a_sup.vendor_id

and a_sup.vendor_id=apss.vendor_id

and aia.vendor_site_id=apss.vendor_site_id

and (aila.po_header_id is not null or aila.po_line_id is not null or aila.rcv_transaction_id is not null)

order by 1,4

On the other site if you want to get the details of PO Invoices then you can refer this below sql 

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

Query to get non PO Invoices in oracle apps r12

Query to get non PO Invoices in oracle apps r12