This note is intended for anyone who must upgrade queries or customizations from 11i which refer to any of the following views or anyone who requires an understanding of the TCA architecture to create new queries or customizations:
- RA_CUSTOMERS
- RA_ADDRESSES
- RA_SITE_USES
- RA_CONTACTS
- RA_CONTACT_ROLES
Projects Uptake of the TCA Architecture in Release 12
The following sections describe the individual views that are being replaced by the TCA (HZ) tables along with a mapping of view columns to HZ table columns and the query conditions that should be used to retrieve them
- In the 11i architecture RA_CUSTOMERS is a synonym in the APPS schema which points to the view RA_HCUSTOMERS.
- The source file for RA_HCUSTOMERS in 11i is archz.odf.
- The table below lists the corresponding HZ table and column for various columns in the current RA_CUSTOMERS view:
Column in RA_CUSTOMERS
|
Corresponding Table
|
Column
|
customer_name
|
hz_parties
|
substrb(party_name,1,50)
|
customer_id
|
hz_cust_accounts
|
cust_account_id
|
customer_number
|
hz_cust_accounts
|
account_number
|
status
|
hz_cust_accounts
|
status
|
- Source Tables: HZ_PARTIES, HZ_CUST_ACCOUNTS
- Join Conditions:
HZ_CUST_ACCOUNTS..Party_Id = HZ_PARTIES.Party_Id
- In the 11i architecture RA_ADDRESSES is a multi-org striped view based on RA_ADDRESSES_ALL. RA_ADDRESSES_ALL is a synonym for the view RA_ADDRESSES_MORG.
- The source file for view RA_ADDRESSES_MORG in 11i is archz.odf.
- The table below lists the corresponding HZ table and column for various columns in the current RA_ADDRESSES view:
Column in RA_ADDRESSES
|
Corresponding Table
|
Column
|
address_id
|
hz_cust_acct_sites_all
|
cust_acct_site_id
|
status
|
hz_cust_acct_sites_all
|
status
|
address1
|
hz_locations
|
address1
|
address2
|
hz_locations
|
address2
|
address3
|
hz_locations
|
address3
|
address4
|
hz_locations
|
address4
|
city
|
hz_locations
|
city
|
state
|
hz_locations
|
state
|
postal_code
|
hz_locations
|
postal_code
|
county
|
hz_locations
|
county
|
country
|
hz_locations
|
country
|
language
|
hz_locations
|
language
|
- Source Tables: HZ_PARTY_SITES, HZ_LOCATIONS, HZ_CUST_ACCT_SITES_ALL
- Join Conditions:
HZ_CUST_ACCT_SITES_ALL.party_site_id = HZ_PARTY_SITES.party_site_id
HZ_LOCATIONS.location_id = HZ_PARTY_SITES.location_id
- In the 11i architecture RA_SITE_USES is a multi-org striped view based on RA_SITE_USES_ALL. RA_SITE_USES_ALL is a synonym for the view RA_SITE_USES_MORG.
- The source file for view RA_SITE_USES_MORG in 11i is archz.odf
- The table below lists the corresponding HZ table and column for various columns in the current RA_SITE_USES view:
Column in RA_SITE_USES
|
Corresponding Table
|
Column
|
site_use_id
|
hz_cust_site_uses
|
site_use_id
|
site_use_code
|
hz_cust_site_uses
|
site_use_code
|
status
|
hz_cust_site_uses
|
status
|
address_id
|
hz_cust_site_uses
|
cust_acct_site_id
|
- Source Tables: HZ_CUST_SITE_USES
- Join Conditions: none
- In the 11i architecture RA_CONTACTS is a synonym for the view RA_HCONTACTS
- The source file for view RA_HCONTACTS in 11i is archz.odf
- The table below lists the corresponding HZ table and column for various columns in the current RA_CONTACTS view:
Column in RA_CONTACTS
|
Corresponding Table
|
Column
|
contact_id
|
hz_cust_account_roles
|
cust_account_role_id
|
status
|
hz_cust_account_roles
|
status
|
customer_id
|
hz_cust_account_roles
|
cust_account_id
|
address_id
|
hz_cust_account_roles
|
cust_acct_site_id
|
first_name
|
hz_parties
|
substrb(person_first_name,1,40)
|
last_name
|
hz_parties
|
substrb(person_last_name,1,50)
|
- Source Tables: HZ_CUST_ACCOUNT_ROLES, HZ_PARTY_RELATIONSHIPS, HZ_PARTIES
- Join Conditions:
HZ_CUST_ACCOUNT_ROLES.party_id = HZ_PARTY_RELATIONSHIPS.party_id
HZ_CUST_ACCOUNT_ROLES.role_type = 'CONTACT'
HZ_PARTIES.party_id = HZ_PARTY_RELATIONSHIPS.subject_id
- In the 11i architecture RA_CONTACT_ROLES is a synonym for the view RA_HCONTACT_ROLES
- The source file for view RA_HCONTACT_ROLES in 11i is archz.odf
- The table below lists the corresponding HZ table and column for various columns in the current RA_CONTACT_ROLES view:
Column in RA_CONTACT_ROLES
|
Corresponding Table
|
Column
|
contact_id
|
hz_role_responsibility
|
cust_account_role_id
|
usage_code
|
hz_role_responsibility
|
responsibility_type
|
primary_flag
|
hz_role_responsibility
|
primary_flag
|
- Source Tables: HZ_ROLE_RESPONSIBILITY
- Join Conditions: none.
11i Table
|
R12 Change
|
ra_addresses_all
|
SELECT acct_site.cust_account_id customer_id, acct_site.cust_acct_site_id address_id
FROM hz_party_sites party_site,
hz_loc_assignments loc_assign,
hz_locations loc,
hz_cust_acct_sites_all acct_site
WHERE acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND loc.location_id = loc_assign.location_id
AND NVL (acct_site.org_id, -99) = NVL (loc_assign.org_id, -99)
|
ra_site_uses_all
|
SELECT site_use_id, LOCATION, attribute1
FROM hz_cust_site_uses_all
|
ra_customers
|
SELECT cust_acct.cust_account_id customer_id,
SUBSTRB (party.party_name, 1, 50) customer_name,
cust_acct.account_number customer_number
FROM hz_parties party, hz_cust_accounts cust_acct
WHERE cust_acct.party_id = party.party_id
|
RA_ADDRESSES_ALL is obsolete table in Release 11.5.10 and Release 12. You can get the customer address information from the following tables in Release 12.
HZ_CUST_ACCT_SITES_ALL
HZ_PARTY_SITES
HZ_LOCATIONS
Here is the Linking information:
- HZ_CUST_ACCT_SITES_ALL & HZ_PARTY_SITES - Link column PARTY_SITE_ID
- HZ_PARTY_SITES & HZ_LOCATIONS - Link column is LOCATION_ID.
