What are the new tables/columns in R12 that correspond with the following list of 11i tables?
AP_BANK_ACCOUNTS_ALL
BANK_ACCOUNT_NAME
AP_BANK_ACCOUNT_USES_ALL
AP_BANK_BRANCHES
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.
- CE_BANK_ACCOUNTS
- IBY_EXT_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)
INSTRUMENT_ID (previously EXTERNAL_BANK_ACCOUNT_ID)
3) The data that was in the AP_BANK_BRANCHES in now stored in the HZ schema.
- 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)
Mapping
You can access these fields by running the following SQL statement:
select p.party_name, a.party_usage_code, o.bank_or_branch_number
BANK_ACCOUNT_NUM
BANK_ACCOUNT_ID
BANK_BRANCH_ID
BANK_ACCOUNT_USES_ID
EXTERNAL_BANK_ACCOUNT_ID
VENDOR_ID
SOLUTION
The newer tables in R12 are
and they are listed below with the applicable fields:
CE_BANK_ACCOUNTS (for internal bank accounts)
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.
To access all four of the fields you listed, you would have to do a join between three different tables:
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');
When the Bank is assigned to Vendors then it will be updated in a table called HZ_CODE_ASSIGNMENTS. Payment related details of supplier are also inserted in iby_external_payees_all as well as iby_ext_party_pmt_mthds.
The bank branch number can be found in the table: HZ_ORGANIZATION_PROFILES .The HZ_ORGANIZATION_PROFILES table stores a variety of information about a party. This table gets populated when a party of the Organization type is created. IBY_EXTERNAL_PAYEES_ALL stores payment-related attributes for the funds disbursement payment process for external party payees.
Queries:
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_id;
SELECT party_supp.party_name supplier_name
, aps.segment1 supplier_number
, ass.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 party_supp
, ap_suppliers aps
, hz_party_sites site_supp
, ap_supplier_sites_all ass
, 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 party_supp.party_id = aps.party_id
AND party_supp.party_id = site_supp.party_id
AND site_supp.party_site_id = ass.party_site_id
AND ass.vendor_id = aps.vendor_id
AND iep.payee_party_id = party_supp.party_id
AND iep.party_site_id = site_supp.party_site_id
AND iep.supplier_site_id = ass.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 party_supp.party_name
, ass.vendor_site_code;
When the Bank is assigned to Vendors then it will be updated in a table called HZ_CODE_ASSIGNMENTS. Payment related details of supplier are also inserted in iby_external_payees_all as well as iby_ext_party_pmt_mthds.
The bank branch number can be found in the table: HZ_ORGANIZATION_PROFILES .The HZ_ORGANIZATION_PROFILES table stores a variety of information about a party. This table gets populated when a party of the Organization type is created. IBY_EXTERNAL_PAYEES_ALL stores payment-related attributes for the funds disbursement payment process for external party payees.
Queries:
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_id;
SELECT party_supp.party_name supplier_name
, aps.segment1 supplier_number
, ass.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 party_supp
, ap_suppliers aps
, hz_party_sites site_supp
, ap_supplier_sites_all ass
, 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 party_supp.party_id = aps.party_id
AND party_supp.party_id = site_supp.party_id
AND site_supp.party_site_id = ass.party_site_id
AND ass.vendor_id = aps.vendor_id
AND iep.payee_party_id = party_supp.party_id
AND iep.party_site_id = site_supp.party_site_id
AND iep.supplier_site_id = ass.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 party_supp.party_name
, ass.vendor_site_code;
