SQL Query to get payment details in oracle apps r12

SQL Query to get payment details in oracle apps r12

In this post , We will be discuss about the SQL query , which will help to extract the complete payments in oracle apps r12. This query will extract the important payment , Invoice details against which we have created those payments so we will get the complete information from Payments to Supplier Invoices in oracle apps r12. I will be share the Important Tables too in this post which we do commonly uses in the query for extracting these payment details in oracle apps. Please find the detail sql query as below.

Important Tables uses in the SQL Query to get payment details in oracle apps r12

1.ap_invoices_all 

2.ap_suppliers

3.ap_supplier_sites_all

4.gl_code_combinations_kfv

5.ap_invoice_payments_all

6.ap_checks_all

Detail SQL Query to get payment details in oracle apps r12

This is the complete sql query which help to extract the Payment details in oracle apps.

select aca.AMOUNT CHECK_AMOUNT,

aca.check_date,

aca.check_number,

aca.currency_code,

aca.status_lookup_code payment_status,

aca.bank_account_num,

aca.payment_method_code,

aca.bank_account_name,

aia.invoice_num,     

aia.invoice_type_lookup_code,

aia.invoice_currency_code,

aia.invoice_date,

aps.vendor_name,

apss.vendor_site_code,

aia.invoice_amount

  from apps.ap_invoices_all         aia,

       apps.ap_suppliers aps,

       apps.ap_supplier_sites_all apss,

       apps.gl_code_combinations_kfv gcc,

       apps.ap_invoice_payments_all aipa,

       apps.ap_checks_all aca

 where aia.vendor_id=aps.vendor_id

   and aia.VENDOR_SITE_ID=APSS.VENDOR_SITE_ID

   AND aps.vendor_id=apss.VENDOR_ID

   and aipa.invoice_id(+)=aia.invoice_id

   and aca.check_id   (+)=aipa.check_id

   and aia.ORG_ID=:P_ORG_ID

SQL Query to get payment details in oracle apps r12