R12 Bank Data

1.What are the new tables in R12?

The following tables were obsoleted in R12:

AP_BANK_BRANCHES
AP_BANK_ACCOUNTS_ALL
AP_BANK_ACCOUNTS_USES_ALL

The new tables in R12 are now under Cash Management module: Here are the tables

CE_BANK_ACCOUNTS: This table contains Legal Entity Level bank account information. Each bank account must be affiliated with one bank branch.

CE_BANK_ACCT_USES_ALL: This table stores Operating Unit level bank account use information

CE_PAYMENT_DOCUMENTS: This table Payment Documents to be used for Printed type Payments

CE_BANK_BRANCHES_V: view: Bank/Branches Info

CE_BANK_ACCT_USES_OU_V: view: Internal Bank Account Uses Info

The bank setup has been moved from AP to CE module, one should use Cash Management responsibility with all grants and permissions in order to create and maintain bank accounts. All the grants and permissions are done by Sysadmin using User management responsibility.

R12 Supplier Bank Accounts Slide Show

Here is the slide show of all the screens used for creating Supplier bank.

R12 Supplier Bank Accounts

So far we have seen creation of Bank, Bank Branch and Bank Account. Lets now focus on technical information.

In R12 Banks and their Branches are stored as Parties in HZ_PARTIES. Both are linked together through relationships in HZ_RELATIONSHIP table. The Bank accounts are stored in the Oracle Payments Application. All tables in this application are prefixed with IBY. The bank accounts are stored in the IBY_EXT_BANK_ACCOUNTS table. The bank_id and branch_id fields link the Bank Account to the relevant Bank and Branch Parties in the HZ_PARTIES table.

The table IBY_ACCOUNT_OWNERS is used to identify the Supplier Party using the ext_bank_account_id to link the 2 IBY tables that the Bank Account belongs to. Finding the Bank account attached to supplier site can be done through linking the tables IBY_EXTERNAL_PAYEES_ALL and IBY_PMT_INSTR_USES_ALL. When a Bank account is assigned a record is created in the IBY_PMT_INSTR_USES_ALL table (Payment Instruments table) and is linked to the bank account by matching the ext_bank_account_id to the instrument_id. Each instrument record is linked to an external payee record in IBY_EXTERNAL_PAYEES_ALL using the ext_pmt_party_id column. The external payee record links us to supplier party ID (payee_party_id), supplier party site ID (party_site_id) and supplier site ID (supplier_site_id). The IBY_EXTERNAL_PAYEES_ALL table stores every supplier site defined and the supplier itself. The IBY_PMT_INSTR_USES_ALL is the main table which lets us know the specific Site/Supplier that the Bank Account has been assigned to.

The query below gives you the links required to get a matching Bank account assigned to supplier site.

SELECT hp.party_name supplier_name
, as.segment1 supplier_number
, assa.vendor_site_code supplier_site
, ieb.bank_account_num
, ieb.bank_account_name
, party_bank.party_name bank_name
, branch_prof.bank_or_branch_number bank_number
, party_branch.party_name branch_name
, branch_prof.bank_or_branch_number branch_number
FROM hz_parties hp
, ap_suppliers as
, hz_party_sites hps
, ap_supplier_sites_all assa
, iby_external_payees_all iep
, iby_pmt_instr_uses_all ipi
, iby_ext_bank_accounts ieb
, hz_parties party_bank
, hz_parties party_branch
, hz_organization_profiles bank_prof
, hz_organization_profiles branch_prof
WHERE hp.party_id = as.party_id
AND hp.party_id = hps.party_id
AND hps.party_site_id = assa.party_site_id
AND assa.vendor_id = as.vendor_id
AND iep.payee_party_id = hp.party_id
AND iep.party_site_id = hps.party_site_id
AND iep.supplier_site_id = assa.vendor_site_id
AND iep.ext_payee_id = ipi.ext_pmt_party_id
AND ipi.instrument_id = ieb.ext_bank_account_id
AND ieb.bank_id = party_bank.party_id
AND ieb.bank_id = party_branch.party_id
AND party_branch.party_id = branch_prof.party_id
AND party_bank.party_id = bank_prof.party_id
ORDER BY 1,3

R12 Supplier Bank Accounts

R12 Supplier Bank Accounts

R12 Supplier Bank Accounts

R12 Supplier Bank Accounts

R12 Supplier Bank Accounts

R12 Supplier Bank Accounts

R12 Supplier Bank Accounts

In my earlier posting we have seen how to create a supplier and supplier site, now lets go thru the steps involved in creating bank, bank branch and bank accounts. Also will go thru the technical information underlying the bank model.

R12 Bank Account model provides a single access point for defining and managing internal bank accounts for Oracle Payables and Oracle Receivables. A single Legal Entity is granted ownership for each internal bank account. In order to grant access to the Legal Entities navigate to Roles &
Role Inheritance using User Management responsibility.

Fill in the required information like type (roles & Responsibilities), name (role name) and
application (Payables) and click on GO. Click on update icon for your selection of role and code.
In the next page click on Security wizards button which will navigate you to wizards where you have to click on Run wizard for CE UMX Security wizard. Select Add Legal Entities button to give access to Bank Accounts. Once you choose your Legal entity select all the options like Use,
Bank Accounts Grants Maintenance and Bank Accounts Transfer, click on Apply button.

Please follow the screens for creating Bank, Bank Branch and Bank Account.