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