R12 : Trading Community Architecture(TCA)

Trading Community Architecture (TCA) is a structure which was based out of R11 Customer Model designed to support complex trading relationships to cater additional need which further extended in R12 with Supplier and Bank. So, TCA is a data model that allows you to manage complex information about the parties, or customers or suppliers or bank who belong to your commercial community, including organizations, locations, and the network of hierarchical relationships among them.

What is Trading Community Architecture (TCA)?

What is TCA, the Trading Community Architecture? Is TCA an Oracle Applications module? Is it functionality within an Oracle module? These are few common question, and there are often many answers given.

The TCA is a data model that supports the entry and management of entities that you interact with. So lets revisit the concept.

Trading Community Architecture is a Very flexible, very robust model which defines the components involve in trading within in E-business Suite.

The implementation of technology and applications to allow users to create and maintain relationships among entities

The universal data schema for customers, prospects, suppliers, distributors, resellers, consortiums, bank across all Oracle EBS applications

TCA not only allows for the tracking of relationships between the implementing organization and its trading partners, but also tracks relationships between the trading partners themselves.

You should also note, TCA is neither an Oracle Applications module nor requires separate license.

If you see TCA guide, you can find these are the key features of TCA

• Provides a foundation for a single source for customer information.
• Ability to represent all business entities as a “Party” (organizations, people, groups, relationships) and to handle them the same way. This approach provides flexibility to accommodate all B2B, B2C and hybrid models in the same repository.
• Many-to-many relationships between Parties and Locations, that allows for less duplication and easier updating.
• Capability for advanced relationship modeling between entities within the trading community. Any party can figure in any number of Party Relationships even within matrix hierarchies (relationship networks).
• Ability to setup and maintain any number of party classifications which can be used for reporting and assignment purposes.
• Extensible data model to enable various business data requirements.
• In reality , three entities Drive in the TCA model , which are Party, Account, and Relationships.

 TCA Terminologies

• Party
  • The concept of ‘Party’ enables the Customer Model to treat all business entities equally, regardless of type. It easily handles B2B, B2C.
    Parties of type ‘Group’ allow for the grouping of any number of other parties into a single entity which enable modeling of households and buying consortiums.
  • Parties of type ‘Relationship’ allow for the relationship between two parties to be viewed as a party in its own right
  • Party - A Party is an entity that can enter into a business relationship and can be of four types.
    • Person - A unique individual (dead or alive) of interest to the owner of the software.
    • Organization - A legal entity recognized by some government authority.
    • Group - a combination of two or more people, organizations or groups of created for the use of the owner of the software.
    • Relationship - The association between an individual person and an organization. Usually a contact at an organization or group.

Fig 1: TCA Logical Diagram

• Account
  • Account - Is a financial roll-up point to track the monitory portion of a customer’s purchases and payments. Stores details about a customer relationship between a Party and your business.
    • This Represents selling-buying relationship such as billing and shipping events
    • Accounts required for a transaction
    • A account cannot exist without a party
  • A Party may have one or more Customer Accounts
    • Account Role - The relationship that a Party has in regard to controlling or using an account.
    • Customer Account Site is a Party Site that is used within the context of a Customer Account (e.g., for billing or shipping purposes).
    • A Customer Account Contact is a Party Contact that is used in the context of a Customer Account.
• Customer
  A customer account represents the business relationship that a party can enter in to with another party. The account has information about the terms and conditions of doing business with the party. For example, you could open a commercial account for purchases to be made by Vision Distribution for its internal use and a reseller account for purchases made by Vision Distribution for sales of your products to end-users .

You can also define contact people, bank accounts, payment methods, telephone numbers, and relationships for each customer account.

You can also maintain multiple customer accounts for a customer that transacts business with more than one line of business in your organization. You maintain separate customer profiles, addresses, and contacts for each customer account.

A party site is the location where a particular party is physically located. Every party has only one identifying address, but a party can have multiple party sites.

A customer address is a party site used in the context of a customer account for billing, shipping, or other purposes.

A contact communicates for or acts on behalf of a party or customer account. A contact can exist for a customer at the account or address level. A person usually acts as a contact for an organization, but can also be a contact for another person. For example, an administrative assistant could be the contact for an executive.

Old Model vs New Customer Model

Fig 2; Customer old model and TCA model

• Locations/site :A Location is a point in geographical space described by an address. A party site is a location.
• Party Relationship :Any relationship between two parties of the above type (person and organization) that needs to be stored as its. own record. Data that directly corresponds to this relationship (contact info etc.) is stored as well. Relationships are stored in the HZ_PARTY_RELATIONSHIPS table.

Factors which you can consider for TCA entities

• Business requirement including your reporting
• System/application requirement
• Country or Organization Legal Requirement
• Global Consideration
• Process standardization

TCA Setup Considerations

When you are doing TCA customer Modeling, keep these things in mind;

• Party be any real Person or Organization.
• Party sites are locations for Party or Organization.
• Relationships are generally used to construct hierarchical structure of Organizations.
• Party becomes a Customer/Account, once a selling relationship is established.
• An account should typically have at least one active ‘bill_to’ site. It helps for accounting and reporting purposes.
• When creating Parties, what all party sites can be or should be created as Parties.
• Generally, if you want to see activities for site level separately from your parent level party, you should create that Site as a separate Party/Entity.
• An account is a separate entity. Create account only where you have selling relationship i.e. only for customers. It identifies selling attributes e.g.payment terms, shipping and billing preferences etc. of the relationship.
• You can have multiple accounts, for each relationship between external party and your business entity. It enables you to have multiple
  sets of selling attributes e.g. payment terms etc.
• You can build relationship between accounts and have one account to pay for another.
• If transaction needs to be segregated within a party to perform granular analysis based on selling or business relation,separate accounts with a party should be created.

TCA Integration with Other Oracle Products

This is how TCA data is tighten with other Oracle products.

 TCA Technical Tables

• TCA - Customer : Here are Technical details for 11i/R12 customer in TCA. You can also refer old post for customer model.

• TCA - Suppliers

Here are Technical details for R12 Supplier in TCA. You can also refer old post for more details.

• TCA - Bank

How to work with Date Functions?? How to add days in current date?

How to work with Date Functions??

How to add days in current date?

 select sysdate + 3 from dual;

----------------------------------------------------

 select '07-dec-04' + 3 from dual; -- Gives error

select to_date('07-dec-04')  + 3 from dual;

select to_date('07-dec-04') - to_date('13-dec-04') from dual; -- Negative value

---------------------------------------------------------------

date + number/24 --> Adds a number of hours to a date

Suppose the time of sysdate is 9:30 pm and 5 hours are added in it then it will be2:30 am of the next date. So it shows the next date.

select sysdate + 4/24 from dual;

---------------------------------------------------

How to do Months_between(d1,d2)

If d1 > d2 then +ve else -ve

Select MONTHS_BETWEEN(TO_DATE('01-AUG-02'),to_date('01-dec-05')) from dual;

select months_between(sysdate,hiredate) from emp

select round(months_between(sysdate,hiredate),0) As "Months Between" from emp

 Assignment - Find the years between using months_between

select months_between('13-dec-04','24-jul-04') from dual

select round(months_between('13-dec-04','24-jul-04'),0) from dual

Add_months(date,n) [n cand be +ve or -ve]

select add_months('4-dec-04',3) from dual; Gives 04-Mar-05

select add_months('4-dec-04',-3) from dual; Gives 04-Sep-04

-----------------------------------------------------------------------------------------------

Next_Day(date,'char') -- Give the date of the next week for the specified day in the char parameter.

select next_day('04-Dec-04','Wed') from dual; Gives the date coming on next Wednesday after 4-Dec-04. The answer is 08-Dec-04

OR

Instead of character parameter a numeric value can be also given. It has Sunday as 1 Monday as 2  ...Saturday as 7

select next_day('04-Dec-04', 4) from dual. The answer is 08-Dec-04

 Note: The number cannot be negative.

----------------------------------------------------------------------

Last_Day(date) - Returns the last date of the month.

select last_day(sysdate) from dual;

--------------------------------------------------------------------------

Round(Date,fmt)

If month is the fmt, then rounds the date to the months extremities.If date is between 1 and 15 then it rounds the date to the first of the same month. If it is 16 to end then rounds to first date of the next date.

E.g select round(to_date('04-dec-04'),'Month') from dual  ----> 01-DEC-04

select round(to_date('15-dec-04'),'Month') from dual; ----> 01-DEC-04

select round(to_date('16-dec-04'),'Month') from dual; ---->  01-JAN-05

select round(to_date('02-Feb-04'), 'Year') from dual; --> 01-JAN-04

select round(to_date('30-Jun-04'), 'Year') from dual;  --> 01-JAN-04

 select round(to_date('1-Jul-04'), 'Year') from dual; --> 01-JAN-05

Trunc

select trunc(to_date('23-dec-04'),'Month') from dual;  Gives 01-Dec-04

select trunc(to_date('07-Dec-04'),'Month') from dual;  Gives 01-Dec-04

select trunc(to_date('23-Dec-04'),'Year') from dual;    Gives 01-Jan-04

select trunc(to_date('02-Feb-04'),'Year') from dual;     Gives 01-Jan-04

To_Char

To see all the records of employees joined after 1982

select * from emp

where to_char(hiredate,'yy') >= 82;

Or  where to_char(hiredate,'yyyy’) >= 1982

To see all the records of employees who have joined from October of any month.

select * from emp

where to_char(hiredate,'mm') >= 10; 

(Note à Only number 1 to 12 can be used for comparison)

To see all the records of employees who have joined from 21st date.

select * from emp

where to_char(hiredate,'dd') >= 21;

To see all the records of employees who have joined from April-81

select * from emp

where to_char(hiredate,'mm') > 04 and to_char(hiredate,'yy') >= 81

To see all the records of employees who have joined after 28-Sep-81

select * from emp

where hiredate >  '28-Sep-81'

DATE FORMAT MODEL

YEARS

select to_char(hiredate,'YYYY') || '-' || to_char(hiredate,'YEAR') FROM EMP;

MONTHS

select to_char(hiredate,'MONTH') || '-' || to_char(hiredate,'MM')

 || '-' || TO_CHAR(hiredate,'MON') FROM EMP;

DAYS

select to_char(hiredate,'DAY') || '-' || to_char(hiredate,'DD')

 || '-' || TO_CHAR(hiredate,'DY') FROM EMP;

ADVANCED FORMATS -

CENTURY

SELECT TO_CHAR(SYSDATE,’SCC’) FROM DUAL;

select to_char(HIREDATE,'SCC') || '-' || TO_CHAR(HIREDATE,'YY') "CEN-YR"

FROM EMP;

SYEAR  The spelled out year.

select to_char(HIREDATE,'SYEAR') "YEAR"

FROM EMP

Q  To get the quarter of the year (1,2,3 and 4)

select to_char(HIREDATE,'Q') "Quarter"

FROM EMP;

 RM   Roman numeral month

select to_char(HIREDATE,'RM')"MONTH"

FROM EMP;

J Julian day - The number of days since 31Dec 4713 B.C.

select to_char(HIREDATE,'J')"JulianDay"

FROM EMP;

TIME

select to_char(sysdate,'hh:mi:ss') FROM DUAL;

To show AM or PM

select to_char(sysdate,'hh:mi:ss')|| '-' || to_char(sysdate,'PM') from dual;

SSSS Seconds past midnight (0-86399)  [ (60secs * 60 min * 24) - 1]

select to_char(sysdate,'ssss') from dual;

Suffixes -

select to_char(sysdate,'ddth') from dual;

select to_char(sysdate,'ddsp') from dual;

select to_char(sysdate,'ddspth') from dual;

 RR DATE FORMAT -

create table rryy

(name varchar2(10),

 dob date);

NAME

DOB

A

24-SEP-02

B

12-DEC-67

C

14-JAN-59

D

05-APR-04

  A AND D ARE IN 2000 CENTURY.

  B AND C ARE IN 1900 CENTURY.

To see the records who have dob of 19th century.

Suppose yy format is used -

SELECT * FROM RRYY

WHERE TO_CHAR(DOB,'YY') < 99.

It assumes all the years are in the 20th  century (2099). So all 4 records will come.

So to show only ,B and C records  --

SELECT * FROM RRYY

WHERE DOB < TO_DATE('01-JAN-90',  'DD-MON-RR');

Here 90 it understands as 1990.

If  YY is given instead of RR -

SELECT * FROM RRYY

WHERE DOB < TO_DATE('01-JAN-90', 'DD-MON-YY');

Here 90 it understands as 2090. So again all 4 records will come.

Similarly ---

SELECT * FROM RRYY

WHERE DOB > TO_DATE('01-JAN-90',  'DD-MON-RR');

Due to RR 90 will be 1990. So it shows all the records where DOB is above 1990.

A and D records will come.

If YY is given instead of RR -

SELECT * FROM RRYY

WHERE DOB > TO_DATE('01-JAN-90',  'DD-MON-YY');

Due to YY it takes 90 as 2090. It will not find any record above 2090.

HZ tables in Oracle Receivables

HZ tables in Oracle Receivables

HZ_PARTIES:
The HZ_PARTIES table stores basic information about parties that can be shared with any relationship that the party might establish with another party. The primary key for this table is PARTY_ID.
Few Important Columns are

• PARTY_ID: Party identifier
• PARTY_NUMBER: Unique identification number for this party
• PARTY_NAME: Name of the party
• PARTY_TYPE: The party type can only be Person, Organization, Group or Relationship.

HZ_PARTY_SITES:
The HZ_PARTY_SITES table links a party (HZ_PARTIES) and a location (HZ_LOCATIONS) and stores location-specific party information. One party can optionally have one or more party sites. One location can optionally be used by one or more parties. The primary key for this table is PARTY_SITE_ID.
Few Important Columns are

• PARTY_SITE_ID: Party site identifier.
• PARTY_ID: Identifier for the party. Foreign key to the HZ_PARTIES table.
• LOCATION_ID: Identifier for the party site. Foreign key to the HZ_LOCATIONS table.
• PARTY_SITE_NUMBER: Party site number.
• PARTY_SITE_NAME: User-defined name for the site.
• ADDRESSEE: Addressee information.

HZ_LOCATIONS:
The HZ_LOCATIONS table stores information about a delivery or postal address such as building number, street address, postal code, and directions to a location. This table provides physical location information about parties (organizations and people) and customer accounts. The primary key for this table is LOCATION_ID.
Few Important Columns are

• LOCATION_ID: Unique identifier for this location
• COUNTRY: Country code from the TERRITORY_CODE column in the FND_TERRITORY table
• ADDRESS1: First line for address
• ADDRESS2: Second line for address
• ADDRESS3: Third line for address
• ADDRESS4: Fourth line for address
• CITY: City
• POSTAL_CODE: Postal Code
• STATE: State
• ADDRESS_KEY: Derived key that facilitates fuzzy searches

HZ_CUST_ACCOUNTS:
The HZ_CUST_ACCOUNTS table stores information about customer accounts , or business relationships that the deploying company establishes with a party of type Organization or Person. This table focuses on business relationships and how transactions are conducted in the relationship. Since a party can have multiple customer accounts, this table might contain several records for a single party. For example, an individual person can establish a personal account, family account, and a professional account for a consulting practice. The primary key for this table is CUST_ACCOUNT_ID.
Few Important Columns are

• CUST_ACCOUNT_ID: Customer account identifier
• PARTY_ID: A foreign key to the HZ_PARTY table.
• ACCOUNT_NUMBER: Account Number
• CUSTOMER_TYPE: Receivables lookup code for the CUSTOMER_TYPE attribute. I for internal customers, R for revenue generating external customers.
• CUSTOMER_CLASS_CODE: Customer class identifier

HZ_CUST_ACCT_SITES_ALL:
The HZ_CUST_ACCT_SITES_ALL table stores all customer account sites across all operating units. Customer account sites are addresses, for customer accounts, where the deploying company does business with its customers. One customer account can have multiple customer account sites, and customer account sites for one customer account can belong to multiple operating units. The primary key for this table is CUST_ACCT_SITE_ID.
Few Important Columns are

• CUST_ACCT_SITE_ID: Customer site identifier
• CUST_ACCOUNT_ID: Identifier for a customer account. Foreign key to the HZ_CUST_ACCOUNTS table
• PARTY_SITE_ID: Identifier for a party site. Foreign key to the HZ_PARTY_SITES table
• BILL_TO_FLAG: Indicates if this is a Bill-To site.
• SHIP_TO_FLAG: Indicates if this is a Ship-To site.
• MARKET_FLAG: Indicates if this is a Marketing site.

HZ_CUST_SITE_USES_ALL:
The HZ_CUST_SITE_USES_ALL table stores business purposes assigned to customer account sites, for example Bill-To, Ship-To, and Statements. Each customer account site can have one or more purposes. This table is a child of the HZ_CUST_ACCT_SITES_ALL table, with the foreign
key CUST_ACCT_SITE_ID. The HZ_CUST_SITE_USES_ALL table also stores operating unit identifier, though the HZ_CUST_ACCT_SITES_ALL table itself stores the operating unit for customer account sites. The primary key for this table is SITE_USE_ID.
Few Important Columns are

• SITE_USE_ID: Site use identifier
• CUST_ACCT_SITE_ID: Identifier for the customer account site. Foreign key to the HZ_CUST_ACCT_SITES_ALL table
• SITE_USE_CODE: Business purpose assigned to customer site account, such as Bill-To, Market, and Statements.
• PRIMARY_FLAG: Indicates if this site is the primary site for this customer account. Y for the primary customer account site. N for other customer account sites.

HZ_CUSTOMER_PROFILES:
The HZ_CUSTOMER_PROFILES table stores information about the credit characteristics of a single customer account or a customer account site or a party. A profile class defined in the
HZ_CUSTOMER_PROFILE_CLASSES table can be used to provide default values for the attributes in this table. The primary key for this table is CUST_ACCOUNT_PROFILE_ID.
Few Important Columns are

• CUST_ACCOUNT_PROFILE_ID: Unique identifier of this customer profile
• CUST_ACCOUNT_ID: Identifier for the Customer Account. Foreign key to the HZ_CUST_ACCOUNTS table.
• STATUS: Indicates whether the profile is active or inactive

HZ_CUST_PROFILE_CLASSES:
The HZ_CUST_PROFILE_CLASSES table stores information about the credit characteristics that are common across a group of customer accounts. The characteristics specified in this table can be used as default characteristics for similar customer accounts. The primary key for this table is PROFILE_CLASS_ID.
HZ_PARTY_RELATIONSHIPS:
The HZ_PARTY_RELATIONSHIPS table stores information about relationships between parties.

Relationship between the tables