Understanding Banks in Oracle R12

Bank Details such as Bank Account Name, Bank Account Number etc. are stored in multiple tables in Oracle Apps R12 environment. It may be confusing to see that sometimes the same information such as bank account name is stored in different tables. Here, we shall learn the differences between those tables and their uses.

There are two types of Banks and related bank accounts in R12.

1. Internal
2. External

The basic difference between an Internal bank/bank account and an External bank/bank account is that an internal bank account is an account that is created from the Cash Management module and the External Bank/bank accounts are the ones that are created from the sub-ledger modules such as the Payables or Receivables. Bank Accounts related to Suppliers or Customers are the externals bank accounts.

The Internal bank accounts and its details are stored in the CE tables like “CE_BANK_ACCOUNTS” and the external bank accounts and its details are stored in IBY tables like “IBY_EXT_BANK_ACCOUNTS”.

Internal banks and Bank Accounts – Technical details:

Steps to create a Bank, Bank Branch and a Bank Account

Navigate to Cash Management Super User -> Setup -> Banks -> Banks. Click Create Button to create a new bank.

Create Bank:

Create Bank Address: In the Step 2 – Click on Create button to create Bank Address and click Apply.

Create Contact: On step 3 – click on Create button to create a contact for the bank.

 That’s it – A new Bank with the name Test Bank 4 is created.

Creating a Bank Branch

Navigate to Cash Management Super User -> Setup -> Banks -> Banks – Click on Bank Branches and click Create Button.

First the following oracle page asks to enter the bank name for which the branch is being created for. And Click continue. We get two options here – either to “Create a new Branch” or to “Select an Existing party”. Let’s create a new branch.

Create Branch Address

Create a Contact for the Bank Branch

A bank branch is also created.

Now that a Bank, its Bank Branch, Branch Address and a Contact point is created, lets create a Bank Account and Assign it to the Bank that was created earlier.

Go to the navigation – Cash Management Super User -> Setup -> Banks -> Bank Accounts -> Click Create. The following oracle page asks for the Country, Bank name and Branch name for which the account is being created for. Select the Bank and Branch Name that we created before.

Identify the Bank Account Owner – the corresponding Legal Entity identifier and the Uses of the Bank Account. For which the Bank account will be used for:

Create Bank Account information like Bank Account Name, Account Number and Currency code.

Create the Bank Account Controls that has General control information like Cash Account, Bank Charges, Realized gain, Foreign Exchange charges, Cash Clearing account, Bank Errors, Realized Loss account etc. Click Save and Next and Click Finish.

Add Organization Access – in order to Assign a bank account to an operating unit.

Technical information about Banks and Bank Branches for CE (Cash Management)

SELECT *
  FROM CEFV_BANK_BRANCHES
 WHERE bank_name = 'Test Bank 4';

SELECT *
  FROM ce_bank_accounts
 WHERE bank_account_name = 'Test Bank Account 4';

SELECT org_id              "Operating Unit ID",
       ap_use_enable_flag  "Bank Acc used for Payables",
       ar_use_enable_flag  "Bank Acc used for Receivables",
       xtr_use_enable_flag "Bank Acc used for Treasury",
       PAY_USE_ENABLE_FLAG "Bank Acc used for Payroll",
       bank_account_id,
       bank_acct_use_id,
       c.*
  FROM ce_bank_acct_uses_all c
 WHERE bank_account_id = (SELECT bank_account_id
                            FROM ce_bank_accounts
                           WHERE bank_account_name = 'Test Bank Account 4');

SELECT hou.name                  "Operating Unit Name",
       xep.name                  "Legal Entity Name",
       cebb.bank_name,
       cebb.bank_number,
       cebb.bank_branch_name,
       cebb.bank_branch_number,
       cebb.bank_branch_type, -- Branch type indicates which list the bank routing number is on. Valid types are ABA, CHIPS, SWIFT, OTHER.
       cebb.address_line1,    -- Branch details - Branch Address details below
       cebb.address_line2,
       cebb.address_line3,
       cebb.address_line4,
       cebb.city,                                               -- Branch City
       cebb.state,                                             -- Branch State
       cebb.zip_code,
       cebb.country,
       --ceba.bank_account_number,
       ceba.asset_code_combination_id,
       glcc.concatenated_segments,
       cebau.ap_use_enable_flag  "Bank Acc used for Payables",
       cebau.ar_use_enable_flag  "Bank Acc used for Receivables",
       cebau.xtr_use_enable_flag "Bank Acc used for Treasury",
       cebau.PAY_USE_ENABLE_FLAG "Bank Acc used for Payroll"
  FROM cefv_bank_branches        cebb,
       ce_bank_accounts          ceba,
       ce_bank_acct_uses_all     cebau,
       gl_code_combinations_kfv  glcc,
       hr_operating_units        hou,
       xle_entity_profiles       xep
 WHERE     1 = 1
       AND cebb.bank_branch_id = ceba.bank_branch_id
       AND ceba.asset_code_combination_id = glcc.code_combination_id
       AND ceba.bank_account_id = cebau.bank_account_id
       AND cebau.org_id = hou.organization_id
       AND ceba.account_owner_org_id = xep.legal_entity_id
       AND bank_name = 'Test Bank 4';