HZ Tables and ER Diagrams in Oracle Application

HZ_PARTIES: (PK: PARTY_ID)

Party is a generic concept about an entity that can establish relationships with another entity.

For example, a party can be an Organization like Oracle, a Person like Jane Doe, a Group like World Wide Web Consortium, or a Relationship like Jane Doe at Oracle.
Based on this concept, a person is a party of the Person type. The

HZ_PARTIES table contains denormalized information from the HZ_LOCATIONS, HZ_PERSON_PROFILES, HZ_ORGANIZATION_PROFILES, HZ_CONTACT_POINTS and HZ_PERSON_LANGUAGE tables.

Note: A group is a party of the Group type. There is no specific table for groups. The group parties are also stored in the HZ_PARTIES table with column GROUP_TYPE as “GROUP”.

HZ_PERSON_PROFILES:

(PK: PERSON_PROFILE_ID and  FK:PARTY_ID):

HZ_PERSON_PROFILES table stores personal and family information about a party of the Person type.

For example, this table could contain the correct spelling and phonetic pronunciation of the person's name.

HZ_ORGANIZATION_PROFILES:

(PK: ORGANIZATION_PROFILE_ID and  FK:PARTY_ID)

An organization is a party of the Organization type.
HZ_ORGANIZATION_PROFILES table stores a variety of information about a party. This table gets populated during the creation of a party of the Organization type. This table can also store historical data for the organization. Each time an organization's information is updated, the effective end date column for the original record is updated and a new record that contains the updated information is created.

HZ_PARTY_SITES:

(PK: PARTY_SITE_ID and FK: PARTY_ID)

A party can have one or more locations and a location can be used by one or more parties. The Party Site relational entity represents the association of a party and its locations.
The HZ_PARTY_SITES table links a party (HZ_PARTIES) and a location (HZ_LOCATIONS), and stores location-specific party information such as MAILSTOP and ADDRESSEE.
For example, 500 Oracle Parkway can be specified as a party site for Oracle. This party site can be used for multiple customer accounts with the same party.

HZ_LOCATIONS: (PK: LOCATION_ID and FK: PARTY_ID, PARTY_SITE_ID)

HZ_LOCATIONS table stores information about a delivery or postal address such as building number, street address, postal code, and directions to location. This table provides physical location information about parties (organizations and persons) and customer accounts.

HZ_LOC_ASSIGNMENTS: (PK: LOC_ID FK: LOCATION_ID)

HZ_LOC_ASSIGNMENTS table stores information about the relationship between a location defined in the HZ_LOCATIONS table and a tax authority defined in the AR_LOCATION_COMBINATIONS table.

The appropriate sales tax can be calculated when you assign a location to a tax authority.

In a multi–org environment, a record is created for each organization at the location.

HZ_PARTY_SITE_USES:

(PK: PARTY_SITE_USE_ID and FK:PARTY_SITE_ID)

A relation between a party and a location can have several uses such as delivery or billing. The HZ_PARTY_SITE_USES table stores information about how a party site is used. Party sites can have multiple uses, for example; Ship-To and Bill-To.

HZ_RELATIONSHIPS:

(PK: RELATIONSHIP_ID, SUBJECT_ID and OBJECT_ID and FK:PARTY_ID)

The HZ_RELATIONSHIPS table stores information about relationships between one party and another party. The table specifies the subject and object, as well as the relationship that exists between the two parties.

Key columns: SUBJECT_TYPE, OBJECT_TYPE and RELATIONSHIP_CODE

Columns SUBJECT_ID and SUBJECT_TYPE are represent the columns of PATY_ID and PARTY_TYPE from HZ_PARTIES.

Columns OBJECT_ID and OBJECT_TYPE are represent the relationship of PATY_ID from HZ_PARTIES.

For example, if the party relationship is a Parent, the holding company could be the subject in the relationship while one of its subsidiaries could be the object.

If you we take employee relationship between Organization (Oracle Inc.) and Person (Mr. Xyz), here Organization is a Subject and Person is an Object.

Similarly member relationship between Group and Person

Branch relationship between Bank and Braches etc…

HZ_RELATIONSHIP_TYPES: PK: RELATIONSHIP_TYPE_ID

The HZ_RELATIONSHIP_TYPES table defines the business rules that are associated with a relationship type.

A non-directional relationship type consists of a single record with the same forward (FORWARD_REL_CODE) and backward (BACKWARD_REL_CODE) relationship codes. A directional relationship type consists of two records: one for the parent (DIRECTION_CODE is P) and the other for a child (DIRECTION_CODE is C) of that parent. Forward and backward relationship codes are validated against the PARTY_RELATIONS_TYPE lookup type.

HZ_PARTY_RELATIONSHIPS:

(PK: PARTY_RELATIONSHIP_ID, SUBJECT_ID and OBJECT_ID and FK:PARTY_ID).

HZ_PARTY_RELATIONSHIPS table stores parent – child information and the relationship between parties.

HZ_ORG_CONTACTS:

(PK: ORG_CONTACT_ID FK: PARTY_RELATIONSHIP_ID and PARTY_SITE_ID).

HZ_ORG_CONTACTS table stores information about the position of the contact for a party or party site. The records in this table provide information about a contact's position such as JOB_TITLE, RANK and general contact information. This table is not used to store information about a specific person or organization.

HZ_ORG_CONTACT_ROLES:

(PK:ORG_CONTACT_ROLE_ID FK:ORG_CONTACT_ID).

The HZ_ORG_CONTACT_ROLES table stores information about the role of the contact position that is specified in HZ_ORG_CONTACTS table.
For example, a vice president of manufacturing may have a custom-defined role as a member of a capital expenditures review board.

HZ_CUST_ACCOUNTS:

(PK:CUST_ACCOUNT_ID FK:PARTY_ID, CUST_ACCOUNT_PROFILE_ID).

A customer account is a customer relationship established with a party.
The HZ_CUST_ACCOUNTS table stores information about customer relationships established with a party. Because a party can have multiple customer accounts, this table could contain several records for a same party.
For example, an individual person may establish a personal account, a family account, and a professional account for a consulting practice.

HZ_CUST_ACCT_SITE_ALL:

(PK:CUST_ACCT_SITE_ID FK: CUST_ACCOUNT_ID and PARTY_SITE_ID)

A customer account can have several customer account sites.
Each customer account site is located in one location. HZ_CUST_ACCT_SITE_ALL table stores information about customer sites, or locations, for customer accounts. One customer account can have multiple sites. Address information for a site is stored in HZ_LOCATIONS table.

HZ_CUST_SITE_USES_ALL:

(PK: SITE_USE_ID FK: CUST_ACCT_SITE_ID)

A customer site in one location can have several business purposes.
HZ_CUST_SITE_USES_ALL table stores information about the business purposes assigned to a customer account site. A customer account site can have multiple purposes for a customer account site; however each record in this table only specifies one purpose for a customer account site.
For example, a customer account site may be assigned as the Ship-To site in one record and as the Bill-To site in another.

Note that address information is stored in the HZ_LOCATIONS table.

HZ_CUST_ACCOUNT_ROLES: (PK:CUST_ACCOUNT_ROLE_ID FK:PARTY_ID, CUST_ACCOUNT_ID and CUST_ACCT_SITE_ID).

A party can play a role in a customer account.
HZ_CUST_ACCOUNT_ROLES table stores information about the role or function that a party performs in relation to a customer account.
For example, Jane Doe might be a legal contact for Vision Corporation.

HZ_ROLE_RESPONSIBILITY

(PK:RESPONSIBILITY_ID FK: CUST_ACCOUNT_ROLE_ID)

A party can play a role in a customer account.
A role in a customer account can have several responsibilities. HZ_ROLE_RESPONSIBILITY table stores information about the required or expected activities of a party based on the party's role or function in relation to an account.

For example, in this table you may store "Accepts quality of received materials" as a responsibility of the Incoming Material Quality Control role specified in the HZ_CUST_ACCOUNT_ROLES table.

HZ_CUST_ACCT_RELATE_ALL

(PK: CUST_ACCOUNT_RELATE_ID FK: CUST_ACCOUNT_ID, RELATED_CUST_ACCOUNT_ID)

Customer Accounts can have relationships between m.
HZ_CUST_ACCT_RELATE_ALL table stores information about relationships between customer accounts. A flag lets you indicate whether a relationship is reciprocal.

HZ_CUST_PROFILE_AMTS:

(PK: CUST_ACCT_PROFILE_AMT_ID FK: CUST_ACCOUNT_PROFILE_ID, CUST_ACCOUNT_ID, SITE_USE_ID)

A customer account has an updateable, defaulted credit limit, which is part of the customer's profile information.
HZ_CUST_PROFILE_AMTS table stores information about the credit limits specified for a customer profile class for a single currency. The credit limits of the profile class can be assigned to specific customer accounts or customer account sites. Many of the values in this table may be default values from the HZ_CUST_PROF_CLASS_AMTS table, which can be modified in this table for a specific customer account or customer account site.
For example while the profile class Large Independent Retail Stores may have an overall credit limit of 250,000 Irish punts, you may limit a new customer in this class to an overall credit limit of 50,000 Irish punts.

HZ_CUST_PROFILE_CLASSES:

(PK: PROFILE_CLASS_ID)

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.

HZ_CUSTOMER_PROFILES

(FK: CUST_ACCOUNT_PROFILE_ID, CUST_ACCOUNT_ID, PARTY_ID, SITE_USE_ID and PROFILE_CLASS_ID)

A customer account has a customer profile class that provides default customer account attributes.
The HZ_CUSTOMER_PROFILES table stores information about 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.
For example, you can create a profile class called Large Independent Retail Stores and specify several attributes that describe this class of customer. In future, you can assign new customers to this class so that new customer inherits characteristics of class.

HZ_CONTACT_POINT:(PK: CONTACT_POINT_ID, FK: PARTY_ID, CUST_ACCOUNT_ID and CUST_ACCT_SITE_ID)

The HZ_CONTACT_POINT table stores information about how to communicate to parties or party sites using electronic media such as e-mail, Electronic Data Exchange Interface (EDI), telephone, telex and the Internet.
For example, telephone-related data can include the type of telephone line, a touch-tone indicator, a country code, the area code, the telephone number, and an extension number to a specific handset.

HZ_CUST_CONTACT_POINTS:(PK: CUST_CONTACT_POINT_ID, FK: CONTACT_POINT_ID, CUST_ACCOUNT_ID, CUST_ACCT_SITE_ID and CUST_ACCT_ROLE_ID)

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

HZ_CONTACT_RESTRICTIONS: (PK: CONTACT_RESTRICTION_ID)

The HZ_CONTACT_RESTRICTIONS table stores information about restrictions on contacting parties.

Different Entity Relations Between HZ Tables