payment tables in oracle apps r12

Payment tables in oracle apps r12

In this post , we will be discuss about the Payment Tables in Oracle apps r12. Payment tables related to supplier invoices payment information's. These tables helps to stores all information about all the Payments which we have created in oracle apps for the supplier invoices. We do get the information like check No, check Date , Supplier Banking Information , Internal Banking Information, Payment Status , Payment Invoice , Payee Information and many other important payment information's. These Important payments stores in the Payment Tables in oracle apps application. These tables helps to write the custom Report and write the sql queries to extract the data from the back end. Here below , will share all the Payment Tables in oracle apps r12.

9 Important Payment Tables in Oracle apps r12

 1. AP_CHECKS_ALL
 2.AP_INVOICE_PAYMENTS_ALL
 3.AP_HOLDS_ALL
 4.ce_bank_accounts
 5.ce_banks_v
 6.IBY_PAYMENTS_ALL
 7. IBY_DOCS_PAYABLE_ALL
 8.CE_PAYMENT_DOCUMENTS
 9.IBY_TRXN_DOCUMENTS

AP_CHECKS_ALL

THIS TABLE STORE ALL THE INFORMATION ABOUT PAYMENTS, Here you will get all the payment information ,Check Number , Banks Detail Vendor etc.

AP_INVOICE_PAYMENTS_ALL

IF YOU WANT TO SEE ONE CHECK IS DONE AGAINST WHICH PAYABLES INVOICES THEN YOU CAN CHECK FROM THIS TABLE. Here you can see invoices paid against check.

AP_HOLDS_ALL

THIS TABLE STORE INFORMATION ABOUT INVOICES HOLDS.

ce_bank_accounts 

This table store internal bank accounts information

ce_banks_v

This table store only bank and branch information's

select * from apps.ce_bank_acct_uses_all

This table store information's about bank account and operating units. It Means this bank accounts is using with which operating units and for which modules like (AP & AR)

Detail SQL query using the payment tables in oracle apps r12

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 find paid invoices in oracle apps

SQL query to find paid invoices in oracle apps

 

 

This below query helps to get the Paid Invoices in the system. As per invoice workbench , to find the Invoice Header Information you can check AP_INVOICES_ALL table. To find the Invoice Lines Information , you can go to AP_INVOICE_LINES_ALL . To find the Invoice Distributions Information AP_INVOICE_DISTRIBUTIONS_ALL. In all tables , INVOICE_ID is link to move between the tables. To get Supplier information for the Invoice, you can refer the AP_SUPPLIERS_ALL and AP_SUPPLIER_SITES_ALL.

To get the Join between Invoice and Suppliers Table is Vendor Id and Supplier Site Id.

 

 

SELECT * FROM AP_INVOICES_ALL A1,AP_SUPPLIERS A2, AP_SUPPLIER_SITES_ALL A3

WHERE A1.VENDOR_ID=A2.VENDOR_ID

AND A2.SUPPLIER_SITE_ID=A3.SUPPLIER_SITE_ID

AND A1.SUPPLIER_SITE_ID=A3.SUPPLIER_SITE_ID

AND payment_status_flag='Y'

and org_id=:P_ORG_ID