Relation between hz tables

Relation between hz tables

In this post , We will be discussing about relation between hz tables. As we know, HZ tables related to tca in oracle apps. We do most commonly uses the hz tables for extracting the customer information’s in oracle apps because in TCA, Oracle treats everything in Oracle application as party, which could be become its potential customer in future, so it stores the information’s about Suppliers, customers in HZ tables as per the TCA architecture. TCA hz tables help to extract the customer sites, addresses and locations details from oracle apps. Every hz tables do relate with other logically in terms of data. Some hz tables do store information about customer header, others do store information about customer profiles, others do stores customer sites and others do stores site addresses information’s. Here below is the complete detail explanation about relation between hz tables.

HZ Tables having data relation

1.HZ_PARTIES

2.HZ_CUST_ACCOUNTS_ALL

3.HZ_CUST_SITE_ACCTS_ALL

4.HZ_CUST_SITE_USES_ALL

5.HZ_LOCATIONS_ALL

6.HZ_PARTY_USG_ASSIGNMENTS

7.HZ_PARTY_SITES_ALL

SQL and Data relation between hz tables

1.      HZ_PARTIES:- This tables stores all the customer and Suppliers information’s. In this table we do get the information as party for supplier and customer. HZ_PARTIES relate with HZ_CUST_ACCOUNTS_ALL table with PARTY_ID column.

2.      HZ_CUST_ACCOUNTS_ALL:- This table store the information specific to customer headers. We do get the customer number, Customer start date. HZ_CUST_ACCOUNTS_ALL relate with PARTY_ID to HZ_PARTIES

HZ_CUST_ACCOUNTS_ALL relate with CUST_ACCOUNT_ID with HZ_CUST_SITE_ACCTS_ALL Relation between hz tables

3.      HZ_CUST_SITE_ACCTS_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.

It relate with CUST_ACCOUNT_ID to HZ_CUST_ACCOUNTS_ALL table.

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.

It related with CUST_ACCT_SITE_ID to HZ_CUST_ACCT_SITES_ALL table.

HZ_PARTY_SITES_ALL: - 

This table help to store the Customer Party sites information’s. We do get the party site number for the customer in this table. 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

It Join with PARTY_SITE_ID with HZ_CUST_ACCT_SITES_ALL table.

HZ_LOCATIONS_ALL: - 

This table stores the actual customer addresses information’s in oracle r12. We do get the complete address information in this table.

It Join with LOCATION_ID with HZ_PARTY_SITES_ALL table.

HZ_CUSTOMER_PROFILES: - 

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.

SQL Query using Relation between hz tables

SELECT  

 PARTY_NAME,

    ,hcsua.SITE_USE_CODE PURPOSE        -- *

    ,hcsua.location SITE

    ,hl.COUNTRY Country

    ,hl.ADDRESS1 Address_Line_1

      ,hl.ADDRESS2 Address_Line_2

      ,hl.ADDRESS3 Address_Line_3

      ,hl.ADDRESS4 Address_Line_4

      ,hl.CITY City

      ,hl.STATE STATE

      ,hl.PROVINCE Province

      ,hl.COUNTY County

      ,hl.POSTAL_CODE Postal_Code

      ,hl.LANGUAGE Location_Language

      ,hl.DESCRIPTION Description

FROM hz_parties hp

    ,hz_party_sites hps

    ,hz_cust_accounts hca

    ,hz_cust_acct_sites_all hcas

    ,hr_operating_units hou

    ,hz_party_usg_assignments hpua

    ,hz_cust_site_uses_all hcsua,

    HZ_LOCATIONS HL

WHERE hp.party_id = hca.party_id

    AND hp.party_id = hps.party_id

    AND hp.STATUS = 'A'

   

    AND hcsua.STATUS = 'A'

    AND hcsua.cust_acct_site_id = hcas.cust_acct_site_id

    AND hca.STATUS = 'A'

    AND hcas.STATUS = 'A'

   

    AND hcas.org_id = hou.organization_id

     AND hcas.org_id=:P_ORG_ID

    AND hp.party_id = hpua.party_id

    AND hpua.PARTY_USAGE_CODE = 'CUSTOMER'

    AND hca.cust_account_id = hcas.cust_account_id

Click To Know More