SQL query to find paid invoices in oracle apps

SQL query to find paid invoices in oracle apps

 

 

This below query helps to get the Paid Invoices in the system. As per invoice workbench , to find the Invoice Header Information you can check AP_INVOICES_ALL table. To find the Invoice Lines Information , you can go to AP_INVOICE_LINES_ALL . To find the Invoice Distributions Information AP_INVOICE_DISTRIBUTIONS_ALL. In all tables , INVOICE_ID is link to move between the tables. To get Supplier information for the Invoice, you can refer the AP_SUPPLIERS_ALL and AP_SUPPLIER_SITES_ALL.

To get the Join between Invoice and Suppliers Table is Vendor Id and Supplier Site Id.

 

 

SELECT * FROM AP_INVOICES_ALL A1,AP_SUPPLIERS A2, AP_SUPPLIER_SITES_ALL A3

WHERE A1.VENDOR_ID=A2.VENDOR_ID

AND A2.SUPPLIER_SITE_ID=A3.SUPPLIER_SITE_ID

AND A1.SUPPLIER_SITE_ID=A3.SUPPLIER_SITE_ID

AND payment_status_flag='Y'

and org_id=:P_ORG_ID