What are the new tables/columns in R12 that correspond with the following list of 11i tables?
BANK_BRANCH_ID BANK_NAME BANK_BRANCH_NAME BANK_NUM
1) AP_BANK_ACCOUNTS_ALL was separated into two separate tables to identify what is external external and what is internal bank accounts. The newer tables in R12 are CE_BANK_ACCOUNTS
and they are listed below with the applicable fields:
CE_BANK_ACCOUNTS (for internal bank accounts)
BANK_ACCOUNT_NAME (same as in 11i) BANK_ACCOUNT_NUM (same as in 11i) BANK_ACCOUNT_ID (same as in 11i) BANK_BRANCH_ID (same as in 11i)
IBY_EXT_BANK_ACCOUNTS (for external bank accounts)
BANK_ACCOUNT_NAME (same as in 11i) BANK_ACCOUNT_NUM (same as in 11i) EXT_BANK_ACCOUNT_ID (formerly BANK_ACCOUNT_ID) BRANCH_ID (formerly BANK_BRANCH_ID)
2) AP_BANK_ACCOUNT_USES_ALL corresponds with the R12 table below. Please note that the fields BANK_ACCOUNT_USES_ID and VENDOR_ID no longer apply since the bank accounts were broken down into separate tables for internal and external accounts.
INSTRUMENT_ID (previously EXTERNAL_BANK_ACCOUNT_ID)
3) The data that was in the AP_BANK_BRANCHES in now stored in the HZ schema.
To access all four of the fields you listed, you would have to do a join between three different tables:
- HZ_PARTIES.PARTY_ID (formerly AP_BANK_BRANCHES.BANK_BRANCH_ID)
- HZ_PARTIES.PARTY_NAME when HZ_PARTY_USG_ASSIGNMENTS.PARTY_USAGE_CODE = 'BANK' (formerly AP_BANK_BRANCHES.BANK_NAME)
- HZ_PARTIES.PARTY_NAME when HZ_PARTY_USG_ASSIGNMENTS.PARTY_USAGE_CODE = 'BANK_BRANCH' (formerly AP_BANK_BRANCHES.BANK_BRANCH_NAME)
- HZ_ORGANIZATION_PROFILES.BANK_OR_BRANCH_NUMBER (formerly AP_BANK_BRANCHES. BANK_NUM)
You can access these fields by running the following SQL statement:
select p.party_name, a.party_usage_code, o.bank_or_branch_number
from hz_parties p, hz_party_usg_assignments a, hz_organization_profiles o
where p.party_id = a.party_id
and p.party_id = o.party_id
and a.party_usage_code in ('BANK', 'BANK_BRANCH');