In this article we will discuss on HZ table details in Oracle R12. In Oracle R12, the HZ tables are referred to as TCA tables. In r12, the TCA information is stored in the HZ tables. Oracle regards everyone in the Oracle application as a prospective customer in the future, thus it saves information about suppliers and customers in HZ tables according to the TCA design. Customers are most typically represented using HZ tables. We use the HZ tables in Oracle r12 to retrieve the Customer In formations. Here’s where you can find it. I’ll go through some of the most essential hz tables in Oracle R12, as well as a detailed description of each table.
Below is a collection of HZ tables that we frequently use for customers and suppliers.
This table keeps track of the supplier’s and customers’ party registrations. The main field in this table is PARTY_ID, which is linked to AP_SUPPLIERS_ALL and HZ_CUST_ACCOUNTS_ALL.
This table is exclusively available to customers. It saves the information from the Customer Registration form. It keeps track of customer accounts and business interactions established by the deploying enterprise with a party of type Organization or Person.
All customer account sites across all operating units are stored in the HZ_CUST_ACCT_SITES_ALL table. Customer account sites are physical locations where the deployed company conducts business with its clients. Customer account sites for one customer account can belong to numerous operating units, and customer account sites for one customer account can have multiple operating units. CUST_ACCT_SITE_ID is the table’s primary key. It joins the HZ_CUST_ACCOUNTS_ALL table with CUST_ACCOUNT_ID.
Business reasons assigned to customer account sites, such as Bill-To, Ship-To, and Statements, are stored in the HZ_CUST_SITE_USES_ALL database. Each customer account site can serve a variety of functions. The HZ_CUST_ACCT_SITES_ALL table has this table as a child. It joins the HZ_CUST_ACCT_SITES_ALL database with CUST_ACCT_SITE_ID.
This table is used to keep information about Customer Party sites. In this table, we retrieve the customer’s party site number. One or more party sites are optional for one party. One or more parties can use a single location if they like. PARTY_SITE_ID is the main key for this table. It joins the HZ_CUST_ACCT_SITES_ALL table with PARTY_SITE_ID.
In Oracle r12, this table stores the actual customer address information. In this table, we get the whole address information. It joins the HZ_PARTY_SITES_ALL database with LOCATION_ID.
The HZ_CUSTOMER_PROFILES database stores information about a single customer account, a customer account site, or a party’s credit qualities. Default values for the properties in this table can be provided by a profile class defined in the HZ_CUSTOMER_PROFILE_CLASSES table. CUST_ACCOUNT_PROFILE_ID is the primary key for this table.
The HZ_RELATIONSHIPS table contains information on the relationships between two parties. The table identifies the subject and object, as well as the nature of the interaction between them.
The HZ CONTACT POINT table contains information about how to communicate with parties or party sites via electronic means such as e-mail, EDI, telephone, telex, and the Internet. The type of telephone line, a touch-tone indicator, a country code, an area code, the telephone number, and an extension number to a specific handset are all examples of telephone-related data.
Query to get HZ Party details in Oracle Apps R12
,hcsua.SITE_USE_CODE PURPOSE — *
,NULL ACCT_ADDR_PURPOSE_SET — *
FROM hz_parties hp
WHERE hp.party_id = hca.party_id
AND hp.party_id = hps.party_id
AND hp.STATUS = ‘A’
AND hca.STATUS = ‘A’
AND hcas.STATUS = ‘A’ AND hcsua.STATUS = ‘A’
AND hcsua.cust_acct_site_id = hcas.cust_acct_site_id
AND hp.party_id = hpua.party_id
AND hpua.PARTY_USAGE_CODE = ‘CUSTOMER’
AND hcas.org_id = hou.organization_id
AND hca.cust_account_id = hcas.cust_account_id
HZ Table Relationship ER Diagram