TCA – Trading Community Architecture:
Oracle Trading Community Architecture (TCA) is a data model that allows you to manage complex information about the parties, or customers.
Trading Community Architecture is the implementation of technology and applications to allow users to create and maintain relationships among entities. It is a way to understand who your customer interacts with inside and outside the enterprise.
How TCA Matters?
Before TCA:
There are multiple customer definitions across the enterprise.
It was very difficult to track current and historical information about the customers.
There was a lack of support for mixed business.
It was quite tough to understand relationships between customers and others (suppliers, partners, competitors)
After TCA:
Create a central repository for the entire E-Business Suite to store information relating to all members of a trading community versus separate tables for each member-Prospects, Customers, Contacts, Employees, Partners, Distributors, Suppliers, Banks, etc.
Record complex business relationships between Trading Community entities (including 3rd party relationships).
Support all business models, industries, and geographies.
TCA in R12 Includes:
New trading entities
–>Suppliers
–>Banks & Bank Branches
–>Legal Entity
TCA in R12: Supplier Representation
Supplier organizations are in TCA
Terms of doing business with the supplier are in Purchasing / Payables
Supplier organization, address, contact, phone, email etc. are all in TCA
Employees are already in TCA, Payables using the same employee records in TCA
TCA in R12: Bank Model
New Bank Account Model
Central place to define internal bank accounts
–>Keep track of all bank accounts in one place
–>Explicitly grant account access to multiple operating units/functions and users
Multi-Org Access
–>In the new model, bank accounts are owned by Legal Entities with the option to grant account use to Operating Unit (Payables, Receivables), Legal Entity (Treasury), Business Group (Payroll) .
R12 Multi-org access control
MOAC is new enhancement to Multiple Organization feature of Oracle Application
Enables Users to access the data from one or many Operating Units within a set of given responsibility
A relationship represents the way two entities interact with each other, based on the role that each entity takes with respect to the other. For example, the employment relationship between a person and an organization is defined by the role of the person as the employee and the organization as the employer.
Relation ship type
Each relationship phrase and role pair belongs to a relationship type, which categorizes the types of relationships that you can create.
Relationship Group
Relationship groups are used to determine which relationship roles and phrases are displayed in specific application user interfaces. Groups can also be used to categorize roles and phrases for other functional uses.
TCA Registry
The TCA Registry is the central repository of party and other information for all Oracle applications. The party information includes details about organizations and people, the relationships among the parties, and the places where the parties do business.
Hz_parties –
Party_id -- This table mainly stores the party_id, party_number, Party_type(Person/Organization) and the name(First Name/Last Name and Party_name(first-last name together)) of the party/customer. The first name or last name should be given to create a party.
Hz_cust_accounts/Hz_cust_accounts_all – This has mainly the party_id and Cust_account_id -- (called as Customer_id) Account_number -- (called as Customer_number) (The hz_cust_accounts_all(This table can have multiple records pertaining to one party_id. This means a party can have multiple accounts. Ie; ,we can have multiple cust_account_ids created for a single party in hz_cust_account table. And also under each cust_account_id we can have multiple addresses,
To create another customer_account(differenct cust_account_id), select the customer with the Name from the standard customer creation search screen,(Take care not to select the customer from LOV, since it directly selects the cust_account_id/account_number and once you press OK, you get only that cust_account_id/account number and not all the customer accounts. So enter manually the customer name, press OK and this takes you to the screen,
Select the top most records. Here we can see 4 records. 1 st is the Main Line, which needs to be selected for creating new customer accounts for this customer. We can see that there are 2 different customer numbers(account number, in this screen cust_account_ids won’t be shown) here(1441, and 1442). 1442 is repeating because that account is having 2 different sites(address sites).
Hz_cust_acct_sites_all –
HZ_CUST_ACCT_SITES_ALL stores information about customer sites. One customer account can have multiple sites. The physical address is maintained in HZ_LOCATIONS.
Gives you all the sites or address defined pertaining to an account. (although the cust_account_id is same, the org_id's might be different.) [ If the records are related to an order, you can take the ship_to_org_id, which is nothing but the cust_account_id related to the Bill_to address and link it with the table, hz_cust_accounts (using cust_account_id) and then link it with the cust_account_id of hz_cust_acct_sites_all table like this,
FROM OE_ORDER_HEADERS_ALL OH,
HZ_CUST_ACCOUNTS HCA,
HZ_CUST_ACCT_SITES_ALL HCAS,
hz_cust_site_uses_all HCSU
WHERE OH.SOLD_TO_ORG_ID = HCA.CUST_ACCOUNT_ID
AND HCA.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID
AND HCAS.CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID
AND HCAS.BILL_TO_FLAG = 'P'
Hz_cust_site_uses_all -- HZ_CUST_SITE_USES_ALL stores information about site uses or business purposes. A single customer site can have multiple site uses, such as bill to or ship to, and each site use is stored as a record in this table.
Link this table with hz_cust_account_site_all with the field, CUST_ACCT_SITE_ID. Here is where the and each address can have one(only one primary) Bill_to or Ship_to address. The primary bill_to, ship_to will be the ones which are normally used for ‘site use’ level transactions. For one cust_account_id with different addresses,(see it in hz_cust_acct_sites_all table for these different address lines), we can make only one bill_to or ship_to as primary.
If we get multiple records pertaining to an account, for a Bill_to, check for the field, BILL_TO_FLAG = 'P' (Means primary). IF we have multiple address/site for a particular cust_account_id, Only one bill_to address can be 'P', under a cust_account_id, although we can define multiple bill_to addresses(Note : one address/site can have only one bill_to)and all the transactions through the system will be done with the primary bill_to address.
Hz_contact_points –
This table stores the contact details of the customer. The contact details are stored against a site, if owner_table_name is equated against the hz_party_sites table. If it is equated against the hz_parties then the contacts are against the customer not against the customer site.
If a contact point is created against a customer site(address).
SELECT LTRIM (
hcp.phone_country_code
|| '-'
|| hcp.phone_area_code
|| '- '
|| hcp.phone_number,
'-'
)
Home_Phone,
hcp.PRIMARY_BY_PURPOSE,
hcas.org_id,
hcas.party_site_id,
hca.cust_account_id,
hcas.cust_acct_site_id,
hcp.CONTACT_POINT_ID,
hcp.CONTACT_POINT_TYPE,
hcp.PHONE_LINE_TYPE,
hcp.OBJECT_VERSION_NUMBER
FROM hz_parties hp,
hz_party_sites hps,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcas,
hz_contact_points hcp
WHERE hp.party_id = hps.party_id
AND hca.party_id = hp.party_id
AND hp.party_type IN ('ORGANIZATION', 'PERSON')
AND hp.status = 'A'
AND hcas.party_site_id = hps.party_site_id
AND hca.cust_account_id = hcas.cust_account_id
AND hcp.contact_point_type = 'PHONE'
AND (hcp.OWNER_TABLE_NAME = 'HZ_PARTY_SITES'
AND hps.party_site_id = hcp.owner_table_id)
AND hcp.phone_line_type = 'TELHOME'
AND hcp.status = 'A'If a contact point is create against a customer.
SELECT LTRIM (
hcp.phone_country_code
|| '-'
|| hcp.phone_area_code
|| '- '
|| hcp.phone_number,
'-'
)
Home_Phone,
hcp.PRIMARY_BY_PURPOSE,
hca.cust_account_id,
hcp.CONTACT_POINT_ID,
hcp.CONTACT_POINT_TYPE,
hcp.PHONE_L INE_TYPE,
hcp.OBJECT_VERSION_NUMBER
FROM hz_parties hp, hz_cust_accounts hca, hz_contact_points hcp
WHERE hca.party_id = hp.party_id
AND hp.party_type IN ('ORGANIZATION', 'PERSON')
AND hp.status = 'A'
AND hcp.contact_point_type = 'PHONE'
AND (hcp.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND hp.party_id = hcp.owner_table_id)
AND hcp.status = 'A'HZ_customer_profiles --
There will normally be a default profile attached against a customer as soon as the customer is created.
Since the default profile is attached against the customer(and not against the site_use level), when you query the hz_customer_profiles tables we can see that that site_use_id for this particular profile would be null.
If the profile is created against the customer site_use level, then the corresponding site_use_id will be populated for this customer profile record in the hz_customer_profiles table.
See the below query for reference,
SELECT hcp.CUST_ACCOUNT_PROFILE_ID,
hcp.OBJECT_VERSION_NUMBER,
hcp.cust_account_id
FROM hz_customer_profiles hcp,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu
WHERE hcas.cust_account_id = hcp.cust_account_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND hcp.site_use_id = hcsu.site_use_id
AND hcp.status = 'A'
Trading community architecture Part 2
