Query to get accounted invoices in oracle apps

Query to get accounted invoices in oracle apps

 

In this post, We will discuss about the sql query which will help to extract the Payables Invoices accounted Invoices in Oracle apps. This is a complete Query to get accounted invoices in oracle apps. Here accounted Means, those invoices which have been posted to general ledger in oracle apps. oracle has shared the standard API to ap_invoices_pkg.get_posting_status to get the invoice accounting status which we will use in the Query to get accounted invoices in oracle apps.

 

 

Example of Query to get accounted invoices in oracle apps

 

SELECT aia.invoice_num, aia.invoice_currency_code,
DECODE (aia.payment_status_flag,
'N', 'UN-PAID',
'P', 'Partial Paid',
'Y', 'PAID'
) payment_status_flag,
aia.invoice_date, aps.vendor_name, apss.vendor_site_code,
aila.line_number, aia.invoice_amount, aila.amount line_amount,
pha.segment1 po_number, aila.line_type_lookup_code, apt.NAME term_name,
gcc.concatenated_segments distributed_code_combinations,
aca.check_number, aipa.amount payment_amount, apsa.amount_remaining,
aipa.invoice_payment_type, hou.NAME operating_unit,
gl.NAME ledger_name
FROM apps.ap_invoices_all aia,
ap_invoice_lines_all aila,
ap_invoice_distributions_all aida,
ap_suppliers aps,
ap_supplier_sites_all apss,
po_headers_all pha,
gl_code_combinations_kfv 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 aia.invoice_id = aila.invoice_id
AND aila.invoice_id = aida.invoice_id
AND aila.line_number = aida.invoice_line_number
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(+)
AND aida.dist_code_combination_id = gcc.code_combination_id
AND aipa.invoice_id(+) = aia.invoice_id
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 aia.org_id = :p_org_id
and ap_invoices_pkg.get_posting_status(aia.invoice_id)='P'