Bank Tables in Oracle apps r12 ; Bank tables in oracle apps r12;Error tables in CE Types of Banks in Oracle apps;Important Tables of Internal Banks;Important External Bank Tables;CE Banks_interface tables oracle apps R12 ;Links Between bank_Tables oracle apps r12

Bank Tables in Oracle apps r12 

 

Internal Banks and External Banks.

Internal banks are those banks, Which we create in the Oracle applications to do the payments for suppliers and receive the payment from the customers.  Bank tables in oracle apps r12 stores all about Bank, Branch information's as well as Bank Accounts too.

Here below is the complete list of the Bank tables in oracle apps r12.

Bank tables in oracle apps r12

2 Types of Banks in Oracle apps

1) Internal Bank

2) External Bank

 

Internal Bank

 

Internal Bank is the Oracle our Internal Banks or Business Unit /Organization Internals Banks which they use to Pay Customer Invoice's and to do the receiving of the Customer Payments.

 

Important Tables of Internal Banks

 

select * from CE_BANK_ACCOUNTS 

select * from CE_BANK_ACCT_USES_ALL

select * from CE_BANKS_V

select * from CE_BANK_ACCT_USES_ALL;

select * from CE_BANK_BRANCHES_V

 

External Banks:-

 

External Banks are those Banks, 

Which is related to Our or Organization/ Business unit Customers and Suppliers.

Both Customer and Suppliers are external Entities so Oracle has stored this Banking Information in different tables as compared to the Internal Banks.

 

Important External Bank Tables

1 Select * from iby_external_payers_all

2 Select * from iby_pmt_instr_uses_all

3 Select * from iby_ext_bank_accounts

Externa banks query 

the link between AP and IBY tables 

Ap vendor banks

select APS.VENDOR_NAME "VERDOR NAME",

  APSS.VENDOR_SITE_CODE "VENDOR SITE CODE",

  IEB.BANK_NAME "BANK NAME",

  IEBB.BANK_BRANCH_NAME "BANK BRANCH NAME",

  IEBB.BRANCH_NUMBER "BRANCH NUMBER",

  IEBA.BANK_ACCOUNT_NUM "BANK ACCOUNT NUMBER",

  IEBA.BANK_ACCOUNT_NAME "BANK ACCOUNT NAME"

from AP.AP_SUPPLIERS APS,

  AP.AP_SUPPLIER_SITES_ALL APSS,

  APPS.IBY_EXT_BANK_ACCOUNTS IEBA,

  APPS.IBY_ACCOUNT_OWNERS IAO,

  APPS.IBY_EXT_BANKS_V IEB,

  APPS.IBY_EXT_BANK_BRANCHES_V IEBB

where APS.VENDOR_ID            = APSS.VENDOR_ID

and IAO.ACCOUNT_OWNER_PARTY_ID = APS.PARTY_ID

and IEBA.EXT_BANK_ACCOUNT_ID   = IAO.EXT_BANK_ACCOUNT_ID

and IEB.BANK_PARTY_ID          = IEBB.BANK_PARTY_ID

and IEBA.BRANCH_ID             = IEBB.BRANCH_PARTY_ID

and IEBA.BANK_ID               = IEB.BANK_PARTY_I

Few Important SQL Query for Banks in oracle apps

select CB.BANK_NAME, CBA.bank_account_name

from CE_BANK_ACCOUNTS CBA ,CE_BANKS_V CB

WHERE CBA.bank_account_id IN (SELECT CBAU.bank_account_id

                             FROM CE_BANK_ACCT_USES_ALL CBAU

                             WHERE 1=1

                             AND (NVL(AP_USE_ENABLE_FLAG,'N' ) = 'Y' OR NVL(AR_USE_ENABLE_FLAG,'N' ) = 'Y' )

)

and CB.BANK_PARTY_ID =CBA.BANK_ID

ORDER BY CB.BANK_NAME,CBA.bank_account_name;

Links Between bank_Tables oracle apps r12 

CE_statment_headers and ce_statment_lines 

Banks and banks_branches 

Status of statement lines 

select  CBA.bank_account_num ,CSL.Statment_line_id        

FROM       ce_statement_headers CSH

          ,ce_statement_lines CSL

          ,ce_bank_branches_v CBB

          ,ce_bank_accounts CBA

          ,ce_lookups CL

          ,ce_transaction_codes CTC

WHERE CSH.statement_header_id=CSL.statement_header_id

AND CSH.bank_account_id=CBA.bank_account_id

AND CBB.BRANCH_PARTY_ID(+)         = CBA.BANK_BRANCH_ID

AND CTC.trx_code = CSL.trx_code                             

AND CTC.bank_account_id = CBA.bank_account_id  

AND CSL.status  ='UNRECONCILED'

AND CSL.trx_date BETWEEN '01-JAN-20' AND '07-MAY-20'

AND CL.lookup_code = CSL.trx_type

AND CL.lookup_type  = 'BANK_TRX_TYPE'

AND upper (cba.bank_account_name) LIKE '*********'

and CBB.bank_branch_name='********';

CE Banks_interface tables oracle apps R12 

Oracle apps r12 bank statements import program 

bank statements error  tables 

bank_interface tables 

Oracle Cash management important tables 

SELECT * FROM APPS.CE_STATEMENT_HEADERS ;

SELECT * FROM APPS.CE_STATEMNET_LINES ;

SELECT * FROM APPS.CE_STATEMENT_HEADERS_INT;

SELECT * FROM  APPS.CE_STATEMENT_LINES_INTERFACE;

SELECT * FROM APPS.CE_HEADER_INTERFACE_ERRORS ;

SELECT * FROM APPS.CE_LINE_INTERFACE_ERRORS ;

Error tables in CE 

bank Statements import error tables 

CE_HEADER_INTERFACE_ERRORS

CE_LINE_INTERFACE_ERROR

bank statement interface tables 

CE_STATEMENT_HEADERS_INT

CE_STATEMENT_LINES_INTERFACE

Bank statement bank tables

CE_STATEMENT_HEADERS

CE_STATEMNET_LINES