AP Invoice is something Oracle used to pay the supplier for the goods or the services provided by them. You can say that creation and payment of AP Invoice is the last leg in P2P Cycle. So if you have implemented P2P Cycle, chances are that you will need to use Account Payables Invoice query to fetch the important details. In this post, we will share the query you can use to fetch the basic Account Payable Details. Since we are querying the AP Invoices, query is mainly based on AP_INVOICES_ALL table which is the base table for AP Invoices. But in case you have moved one step forward and have reached the payment stage of the invoice, we suggest you to refer to these posts
How To Fetch (Query) The Payment Details In Fusion
Payment Process Request Query in Fusion
Journal Reconciliation Query in Oracle Fusion
Accounting Detail Query for AP Invoice
By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...
Account Payables Invoice Query Column Details
Query given below will return following details of the invoice:
1. Invoice Number
2. Supplier Name
3. Vendor Site Code
4. Invoice Currency
5. Payment Currency
6. Invoice Amount
7. Amount Paid
8. Term Name
9. Term Date
10. Invoice Type
11. Pay Group
12. Payment Method
13. GL Date
Query
select aia.invoice_num,
hp.party_name Supplier_Name
,site.vendor_site_code,
aia.invoice_currency_code Invoice_Currency,
aia.payment_currency_code Payment_Currency,
aia.invoice_amount,
aia.amount_paid,
TO_CHAR(aia.invoice_date, ‘MM/DD/YYYY’) Invoice_Date,
ats.name terms,
TO_CHAR(aia.terms_date, ‘MM/DD/YYYY’) Terms_Date,
inv_typ_lookup.meaning Invoice_Type,
pay_grp_lookup.meaning Pay_Group,
pmnt.meaning Payment_Method,
TO_CHAR(aia.gl_date, ‘MM/DD/YYYY’) GL_DATE,
aia.vendor_site_id
from ap_Invoices_all aia,
fnd_lookup_values pmnt,
ap_terms ats,
fnd_lookup_values pay_grp_lookup,
hz_parties hp
, poz_supplier_sites_all_m site
,fnd_lookup_values Inv_Typ_lookup
Where aia.invoice_num = ‘10416111_SAPC INTERNAL’
AND pmnt.lookup_type(+) = ‘PAYMENT METHOD’
AND pmnt.lookup_code(+) = aia.payment_method_code
AND ats.term_id (+) = aia.terms_id
AND pay_grp_lookup.lookup_type(+) = ‘PAY GROUP’
AND pay_grp_lookup.lookup_code (+) = aia.PAY_GROUP_LOOKUP_CODE
AND hp.party_id = aia.party_id
AND site.vendor_site_id = aia.vendor_site_id
AND Inv_Typ_lookup.lookup_type = ‘INVOICE TYPE’
AND inv_typ_lookup.lookup_code = aia.INVOICE_TYPE_LOOKUP_CODE
Related Posts
Invoice Table in Oracle- AP_INVOICES_ALL
AP_INVOICE_LINES_ALL: Columns, Index & Salient Points
How To Fetch (Query) The Payment Details In Fusion
Payment Process Request Query in Fusion
Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.
Reference:
support.oracle.com
Check out the Amazon Deals of the day based on your interests..
