Oracle Fusion Trading Community Architecture (TCA): A Deep Dive into Core Entities and Relationships (customers)

Oracle Fusion TCA is the backbone of customer and partner management, providing a flexible, unified model to represent complex business relationships. Below is an architectural breakdown using the tables and relationships .

---

ER-DIAGRAM

1. Party Layer: The Foundation of Business Relationships

Key Tables: HZ_PARTIES, HZ_PARTY_SITES, HZ_PARTY_SITE_USES, HZ_LOCATIONS

A. Party (HZ_PARTIES)

A Party represents any entity that can engage with your organization. There are three types:

• Person: A unique individual (e.g., John Doe).

• Organization: A legal entity (e.g., Acme Corp).

• Group: A collection of individuals/organizations (e.g., Household).

Example:

SELECT PARTY_NAME FROM HZ_PARTIES WHERE PARTY_TYPE = 'ORGANIZATION';

This retrieves all organizations in the system.

B. Location (HZ_LOCATIONS)

Purpose: Eliminates address redundancy by storing unique geographical points.

• Attributes: ADDRESS1, CITY, POSTAL_CODE, etc.

• No duplication: Multiple parties can reference the same location (e.g., shared office buildings).

• History Tracking: Maintains audit trails for address changes (e.g., billing history).

Example:

sql

Copy

SELECT ADDRESS1, CITY FROM HZ_LOCATIONS WHERE LOCATION_ID = 100;

C. Party Site (HZ_PARTY_SITES)

Purpose: Links a Party to a Location and defines its usage.

• Usage Types: Billing, Shipping, Legal, etc. (via HZ_PARTY_SITE_USES.SITE_USE_TYPE).

• Flexibility: A party can have multiple locations, and a location can serve multiple parties.

d. Party Site Uses (HZ_PARTY_SITE_USES)

1. Flexibility: Supports diverse business scenarios (e.g., a warehouse location can be used for both shipping and receiving).

2. Auditability: Tracks historical changes to site usage (e.g., when a SHIP_TO site becomes inactive).

3. Integration: Ensures CRM (party) and ERP (account) systems align on site purposes through shared codes.

Example:

-- Get all billing addresses for a party
SELECT HL.ADDRESS1 
FROM HZ_PARTY_SITES HPS, HZ_LOCATIONS HL, HZ_PARTY_SITE_USES HPSU
WHERE HPS.LOCATION_ID = HL.LOCATION_ID
  AND HPS.PARTY_SITE_ID = HPSU.PARTY_SITE_ID
  AND HPSU.SITE_USE_TYPE = 'BILL_TO';

---

2. Customer Account Layer: Managing Financial Relationships

Key Tables: HZ_CUST_ACCOUNTS, HZ_CUST_ACCT_SITES_ALL, HZ_CUST_SITE_USES_ALL

A. Customer Account (HZ_CUST_ACCOUNTS)

Purpose: Represents the financial relationship between your business and a Party.

• Stores financial terms, credit limits, and account hierarchies.

• Example: A single organization (PARTY_ID=123) can have multiple accounts for different business units.

Key Fields:

• CUST_ACCOUNT_ID: Unique identifier.

B. Customer Account Site (HZ_CUST_ACCT_SITES_ALL)

Purpose: Associates a Customer Account with a Party Site.

• Reuses the party’s location (e.g., billing address) for account-specific purposes.

• Example: Use PARTY_SITE_ID to link an account to a shared warehouse location.

C. Customer Site Use (HZ_CUST_SITE_USES_ALL)

Purpose: Defines how a site is used for the account (e.g., SITE_USE_CODE = 'BILL_TO').

• Aligns with HZ_PARTY_SITE_USES.SITE_USE_TYPE for consistency.

---

3. Critical Design Principles

A. Separation of Party and Account Layers

• CRM Systems (e.g., Salesforce): Focus on the Party Layer (e.g., HZ_PARTIES for customer profiles).

• ERP Systems (e.g., Oracle Financials): Use the Account Layer (e.g., HZ_CUST_ACCOUNTS for invoicing).

Example:

sql

Copy

-- CRM: Get all parties with their addresses
SELECT HP.PARTY_NAME, HL.ADDRESS1 
FROM HZ_PARTIES HP, HZ_PARTY_SITES HPS, HZ_LOCATIONS HL
WHERE HP.PARTY_ID = HPS.PARTY_ID 
  AND HPS.LOCATION_ID = HL.LOCATION_ID;

-- ERP: Get customer accounts with billing sites
SELECT HCA.ACCOUNT_NUMBER, HCSU.SITE_USE_CODE 
FROM HZ_CUST_ACCOUNTS HCA, HZ_CUST_ACCT_SITES_ALL HCAS, HZ_CUST_SITE_USES_ALL HCSU
WHERE HCA.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID 
  AND HCAS.CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID;

B. Address Reusability

• A single HZ_LOCATIONS record (e.g., LOCATION_ID=200) can serve:

  • Multiple parties (e.g., both Acme Corp and XYZ Ltd).

  • Multiple accounts (e.g., Acme Corp’s US and EU divisions).

C. Business Logic Alignment

• The SQL’s WHERE P.SITE_USE_TYPE = C.SITE_USE_CODE ensures alignment between party site usage (HZ_PARTY_SITE_USES) and account site usage (HZ_CUST_SITE_USES_ALL).

---

4. Why This Architecture Matters

1. Reduced Redundancy: Locations and party sites are shared, eliminating duplicate addresses.

2. Flexibility: A party can have multiple roles (e.g., supplier and customer).

3. Historical Tracking: Changes to addresses or account terms are auditable.

4. Integration: Supports CRM-ERP workflows (e.g., a "Customer" in Salesforce maps to an "Account" in Oracle Financials).

   ##