Customers in TCA Architecture in Release 12

Scope and Application

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

1. The TCA Architecture

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

2.1 RA_CUSTOMERS

·                     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

2.2 RA_ADDRESSES

·                     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

2.3 RA_SITE_USES

·                     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

2.4 RA_CONTACTS

·                     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

2.5 RA_CONTACT_ROLES

·                     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.