Friday, May 8, 2020

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
 



No comments:

Post a Comment

How to improve blog performance

Improving the performance of a blog can involve a variety of strategies, including optimizing the website's technical infrastructure, im...