Invoice payment query in oracle apps r12

Invoice payment query in oracle apps r12

In this post , we will discuss about Invoice payment query in oracle apps r12. We do Payment against Supplier Invoices in Oracle apps. This Query helps to Extract the All the Payment Information's against Invoices in oracle apps r12. This Helps to find the Invoice Payment information's in oracle apps r12. We are using the Standard Payments and Invoices tables to create this Invoice payment query in oracle apps r12.

 

 

Tables Uses in the Invoice payment query in oracle apps r12

1.ap_invoices_all   

2.ap_suppliers

3.ap_supplier_sites_all

4.po_headers_all

5.ap_invoice_payments_all

6.ap_checks_all

7.ap_payment_schedules_all

8.ap_terms

9.hr_operating_units

 

Example of Invoice payment query in oracle apps r12

 

select aps.vendor_name,
apss.vendor_site_code,
aia.invoice_num,     
DECODE(aia.PAYMENT_STATUS_FLAG,'N','UN-PAID','P','Partial Paid','Y','PAID') PAYMENT_STATUS_FLAG ,
aia.invoice_date,

aia.invoice_amount,

pha.segment1 po_number,

apt.name Term_name,     

aca.check_number,

aipa.amount payment_amount,

apsa.amount_remaining,
aipa.invoice_payment_type,
hou.name operating_unit,
from apps.ap_invoices_all       aia,

ap_suppliers aps,

ap_supplier_sites_all apss,

po_headers_all pha,

ap_invoice_payments_all aipa,

ap_checks_all aca,

ap_payment_schedules_all apsa,

ap_terms apt,

hr_operating_units hou

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(+)

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 aia.ORG_ID=:P_ORG_ID