AP Invoice query in Oracle Fusion

AP Invoice query in Oracle Fusion

Hi friends , we are going to discuss about the AP invoice query in oracle fusion. We will share the detail sql query which helps to extract the complete AP invoices information in oracle fusion. We will also share some of the important AP invoice tables too which helps to store the AP invoices details in oracle fusion. In this post , I will try to share the complete sql query which helps to extract the complete AP invoices information's in oracle fusion. Using this sql query we can develop the custom BIP report related to oracle fusion AP Invoices. Please find below the complete detail about AP Invoice query in Oracle Fusion.

AP Invoice query in Oracle Fusion

Top 7 Important AP Invoices tables in Oracle Fusion

1.AP_INVOICES_ALL

2.AP_INVOICE_LINES_ALL

3.AP_INVOICE_DISTRIBUTIONS_ALL

4.AP_INVOICE_PAYMENTS_ALL

5.AP_CHECKS_ALL

6.POZ_SUPPLIERS

7.POZ_SUPPLIER_SITES_ALL

Detail AP Invoice query in Oracle Fusion

Here below is the complete sql query which helps to extract the AP invoices details in oracle fusion.

select aia.invoice_num,     

aia.invoice_date,

aia.invoice_amount,

aila.amount line_amount,

pha.segment1 po_number,

aila.line_type_lookup_code,

aps.vendor_name,

apss.vendor_site_code,

aca.check_number "Payment Number",

aipa.amount "Check Amount",

apsa.amount_remaining "Payment Left",

hou.name operating_unit,

gl.name ledger_name,

aila.line_number,

apt.name Term_name,     

gcc.segment1||'.'||gcc.segment2 distributed_code_combinations,

DECODE(aia.PAYMENT_STATUS_FLAG,'N','UN-PAID','P','Partial Paid','Y','PAID') PAYMENT_STATUS_FLAG

from apps.ap_invoices_all         aia,

ap_invoice_lines_all         aila,

ap_invoice_distributions_all aida,

poz_suppliers aps,

poz_supplier_sites_all apss,

po_headers_all pha,

gl_code_combinations gcc,

ap_invoice_payments_all aipa,

ap_checks_all aca,

ap_payment_schedules_all apsa,

ap_terms apt,

hr_operating_units hou,

gl_ledgers gl

where 1=1

and aia.vendor_id=aps.vendor_id

and aia.VENDOR_SITE_ID=APSS.VENDOR_SITE_ID

AND aps.vendor_id=apss.VENDOR_ID

and aia.po_header_id=pha.po_header_id(+)

aia.invoice_id = aila.invoice_id

and aila.invoice_id = aida.invoice_id

and aila.line_number = aida.invoice_line_number

and aca.check_id   (+)=aipa.check_id

and apsa.invoice_id=aia.invoice_id

and apt.term_id=aia.terms_id

and hou.organization_id=aia.org_id

and gl.ledger_id=aia.set_of_books_id

and aida.dist_code_combination_id=gcc.code_combination_id

and aipa.invoice_id(+)= aia.invoice_id

AP Invoice query in Oracle Fusion