Invoice details query in oracle apps.Oracle invoice payment query

Invoice details query in oracle apps. Oracle invoice payment query. Query to get ap invoice details

In this post , we are sharing the Oracle Apps Invoice Detail Query in Oracle apps. This query will fetch the Oracle Invoice Payment Data.

 

ap invoice details query in r12

 

Query to get ap invoice details

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

Invoice details query in oracle apps. Oracle invoice payment query. Query to get ap invoice details