query to get invoice details in oracle apps

query to get invoice details in oracle apps

In this post, I am sharing the SQL query to get invoice details in oracle apps. This sql query will give you all the Payables Invoice details in oracle apps.

Tables Involved to get invoice details in oracle apps

ap_invoices_all

ap_invoice_distributions_all

po_vendors

po_vendor_sites_all

fnd_lookup_values

ap_terms

gl_code_combinations

Sql query to get invoice details in oracle apps

select ap.invoice_num Invoice_num,ap.invoice_date Invoice_date,ap.invoice_currency_code Invoice_currency,ap.invoice_amount invoice_amount,aid.amount Amount,aid.accounting_date Gl_date,pv.vendor_name Supplier,pvs.vendor_site_code Site_name,at.name Payment_terms,ap.terms_date Term_date,alc.meaning Invoice_type,alc.lookup_code,alc1.meaning Invoice_distribution_type,alc2.meaning Payment_method,gcc.segment1'.'gcc.segment2'.'gcc.segment3'.'gcc.segment4'.'gcc.segment5 Account

from ap_invoices_all ap,

ap_invoice_distributions_all aid,

po_vendors pv,

po_vendor_sites_all pvs,

fnd_lookup_values alc,

fnd_lookup_values alc1,

fnd_lookup_values alc2,

ap_terms at,

gl_code_combinations gcc

where alc.lookup_type='INVOICE TYPE'

and alc.lookup_code=ap.INVOICE_TYPE_LOOKUP_CODE

and alc1.lookup_type='INVOICE DISTRIBUTION TYPE'

and alc1.lookup_code=aid.LINE_TYPE_LOOKUP_CODE

and alc2.lookup_type='PAYMENT METHOD'

and alc2.lookup_code=ap.PAYMENT_METHOD_LOOKUP_CODE

and ap.invoice_id=aid.invoice_id

and ap.org_id=:P_ORG_ID

and ap.vendor_id = pv.vendor_id

and gcc.code_combination_id=aid.DIST_CODE_COMBINATION_ID

and pv.vendor_id=pvs.vendor_id

and ap.vendor_site_id=pvs.vendor_site_id