Important Oracle TCA Lookup Tables You Should Know

1. Lookup for Customer Class Code :
      select lookup_type,lookup_code,meaning
      from ar_lookups
      where lookup_type in (select class_category from HZ_CLASS_CATEGORIES);

2. Lookup for Customer Type :
       select lookup_type,lookup_code,meaning
       from ar_lookups
       where lookup_type='CUSTOMER_TYPE';

3. Lookup for Party Type :
       select lookup_type,lookup_code,meaning,description
       from ar_lookups
       where lookup_type='PARTY_TYPE';

4. Lookup for sales channel :
        select  lookup_type,lookup_code,meaning
        from oe_lookups
         where lookup_type='SALES_CHANNEL';

5. Lookup for SIC code :
        select  lookup_type,lookup_code,meaning
        from ar_lookups
        where lookup_type='SIC_CODE_TYPE';

6. Lookup for Status :
       SELECT lookup_type,lookup_code,
            meaning,
            start_date_active,
            end_date_active,
            description
     FROM ar_lookups
    WHERE lookup_type = 'REGISTRY_STATUS'
     AND enabled_flag  = 'Y';

7. Lookup for Countries :
       SELECT TERRITORY_CODE, NLS_TERRITORY
       FROM FND_TERRITORIES;

Trade Community Architecture Join Tables

Owner table name  hz_parties:

SELECT  acv.first_name, acv.email_address, acv.contact_number, acv.party_relationship_id, acv.rel_party_id, acv.contact_id, acv.contact_party_id, hcp.contact_point_id, hcp.owner_table_name, hp.party_number, hp.party_type, hcp.owner_table_id, hp.party_id, hrs.relationship_code, hrs.relationship_type, hps.party_site_id, hps.party_site_number

FROM ar_contacts_v acv,hz_contact_points hcp,

    hz_parties hp,hz_cust_accounts hca,

    hz_relationships hrs,hz_party_sites hps,

    hz_cust_acct_sites_all hcas,hz_locations hl

WHERE acv.contact_point_id=hcp.contact_point_id

and  hp.party_id=hca.party_id

and hca.cust_account_id=acv.customer_id

and hrs.relationship_id=acv.party_relationship_id

and hrs.object_id=hp.party_id

and hcas.party_site_id=hps.party_site_id

and hca.cust_account_id=hcas.cust_account_id

and hps.location_id = hl.location_id

and hca.account_number=&account_number;

owner table name - hz_party_sites:

SELECT  hp.party_name,hp.party_id,hp.party_type,hp.party_number,  hcp.phone_country_code,hcp.phone_area_code,hcp.phone_number,

         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.EMAIL_ADDRESS,

         hcp.owner_table_name,

         hl.location_id,hl.address1,hl.country,hl.city,hl.postal_code,hcp.owner_table_name

  FROM   hz_parties hp,

         hz_party_sites hps,

         hz_cust_accounts hca,

         hz_cust_acct_sites_all hcas,

         hz_contact_points hcp,

         hz_locations hl

        

 WHERE       hp.party_id = hps.party_id

         AND hca.party_id = hp.party_id

         AND hp.party_type IN ('ORGANIZATION', 'PERSON')

         AND hcas.party_site_id = hps.party_site_id

         AND hca.cust_account_id = hcas.cust_account_id

         AND hcp.contact_point_type in('PHONE','EMAIL')

         AND hps.party_site_id = hcp.owner_table_id

         AND hps.location_id          = hl.location_id

         AND hca.account_number=&account_number;

Oracle Trading Community Architecture (TCA) Explained with Key Tables and Real-Time Examples

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

TCA Key Entities:

   Parties:  Parties are the entities of type person/organization that enter into a business relationship

   Party Sites: Addresses of the parties

   Customers: Parties with whom you have a selling relationship

   Customer Accounts: The business relationships between you and your customers

   Customer Account Sites: Address of the party used in the case of customer accounts

   Locations: Geospatial points, usually defined by an address

   Contacts: People who have a contract or employment relationship with an organization or person

  Contact Points: Means of contact, for example, phone and e-mail address

TCA-Bank and Bank Branches:

CE_BANK_ACCOUNTS for bank accounts

CE_BANK_ACCT_USES_ALL for account uses by Operating Units & Legal Entities

CE_GL_ACCOUNTS_CCID for bank account use accounting data

TCA – Suppliers:

Ap_suppliers

• Hz _parties
• Hz_person_profiles
• Hz_party_relationships

Ap_supplier_sites_all

• Hz_locations
• Hz_location_profile
• Hz_party_sites
• Hz_party_site_uses

Ap_supplier_contacts

•      Hz _parties
•      Hz_relationships
•      Hz_party_sites
•      Hz_org_contacts
•      Hz_contact_points

PARTIES:

           The related tables are as follows:

                 Hz _parties: stores information about parties. (Party number, party name, address)

                 Hz_financial_profile: Stores information about the financial accounts.  

                 HZ_CREDIT_RATINGS: Stores information about the credit rating of parties

                 HZ_REFERENCES: Stores information about references given by one party to another.

                   

PARTIES – TYPE PERSON:

                 HZ_PERSON_PROFILES: Stores details information about people.

                 HZ_EMPLOYMENT_HISTORY: Stores information about where the person has been employed.

PARTIES – TYPE ORGANIZATION:

               HZ_ORGANIZATION_PROFILES: Stores details information about credit rating, financial statistics, and socio-economic and corporate linkage information. (organization_profile_id, party_id, organization_name)

               HZ_STOCK_MARKETS: Stores information about the selling and buying of financial instruments.

CUSTOMER ACCOUNTS:

             HZ_CUST_ACCOUNTS: Stores information about the relationship, if a party becomes a customer. Basically stores information about customer accounts. (cust_account_id, party_id, account_number)

             HZ_CUST_ACCT_SITES_ALL: Stores information about customer sites. One customer can have more than multiple sites.

              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.

             HZ_CUST_ACCT_RELATE_ALL: Stores information about relationships between customer accounts.

             HZ_CUST_ACCOUNT_ROLES: Stores information about the roles that parties perform in customer accounts.

             HZ_CUSTOMER_PROFILES: Stores credit information for a customer account and customer account sites.

                     

CONTACT POINTS:

            HZ_CONTACT_POINTS: Stores electronic methods of communicating with entities such as parties, and party sites. Each record in this table represents s different means of contacting an entity.

           HZ_CUST_CONTACT_POINTS: This table is used to tie a contact point to a customer account, customer account site, or customer account role.

      HZ_CONTACT_RESTRICTIONS: It stores information about restrictions on contracting parties.

  

SITES/LOCATIONS:

           HZ_PARTY_SITES: Stores information about parties and locations.

           Hz_locations: stores information about the locations, namely, address information.

Important tables:

            HZ_PARTIES

            HZ_CUST_ACCOUNTS

            HZ_CUST_SITE_USE_ALL

            HZ_PARTY_SITES

            HZ_LOCATIONS

            HZ_PARTY_SITE_USES

            HZ_CUSTOMER_PROFILES

            HZ_ORGANIZATION_PROFILE

            HZ_PERSON_PROFILES