HZ tables in oracle r12

HZ tables in oracle r12

In this post , We will be discuss about the HZ tables in oracle r12. HZ Tables we do calls the TCA tables in oracle r12. HZ tables do store the TCA informations in r12. In TCA, Oracle treats everything in Oracle application as party, which could be become its potential customer in future, So it stores the In formations about Suppliers , customers in HZ tables as per the TCA architecture.HZ tables most commonly used for Customers. If we want to extract the Customer In formations , we do use the HZ tables in oracle r12. Here below .  I will share some of the important hz tables in oracle r12 and details explanation about these tables in r12.

Most Commonly Used HZ tables in oracle r12

Here below is the list of HZ tables , we do commonly used for customers and suppliers.

1.HZ_PARTIES

2.HZ_CUST_ACCOUNTS_ALL

3.HZ_CUST_SITE_ACCTS_ALL

4.HZ_CUST_SITE_USES_ALL

5.HZ_PARTY_SITES_ALL

5.HZ_LOCATIONS_ALL

6.HZ_PARTY_USG_ASSIGNMENTS

Detail Explanation of HZ tables in oracle r12

HZ_PARTIES :- 

This table store the party registration for the supplier and customers. PARTY_ID is the key column in this tabe to link with AP_SUPPLIERS_ALL and HZ_CUST_ACCOUNTS_ALL

HZ_CUST_ACCOUNTS_ALL :- 

This table specifc to Customer only. It stores the Customer Registration informations.It stores information about customer accounts , or business relationships that the deploying company establishes with a party of type Organization or Person.

It join with PARTY_ID with HZ_PARTIES table in oracle r12.

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 Join with CUST_ACCOUNT_ID with 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 Join with CUST_ACCT_SITE_ID with HZ_CUST_ACCT_SITES_ALL table.

HZ_PARTY_SITES_ALL :- 

This table help to store the Customer Party sites informations. 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 informations 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 :- 

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

Detail SQL Using the HZ tables in oracle r12

SELECT   

 PARTY_NAME,

    'DNB' ACCT_SITE_SRC_SYS

    ,hcas.ORIG_SYSTEM_REFERENCE ACCT_SITE_SRC_SYS_REF

    ,'DNB' ACCT_SITE_PUR_SRC_SYS

    ,hcsua.ORIG_SYSTEM_REFERENCE ACCT_SITE_PUR_SRC_SYS_REF

    ,hcsua.SITE_USE_CODE PURPOSE        -- *

    ,hcsua.primary_flag PRIMARY_INDICATOR

    ,'I' INSERT_UPDATE_INDICATOR

    ,hcsua.location SITE

    ,NULL ACCT_ADDR_PURPOSE_SET        -- *

    ,NULL PURPOSE_FROM_DATE

    ,NULL PURPOSE_TO_DATE

    ,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.POSTAL_PLUS4_CODE Postal_Code_Extension

      ,hl.LANGUAGE Location_Language

      ,hl.DESCRIPTION Description

      ,hl.SHORT_DESCRIPTION Short_Description

      ,hl.SALES_TAX_GEOCODE Sales_Tax_Geocode

      ,hl.SALES_TAX_INSIDE_CITY_LIMITS Sales_Tax_Inside_City_Limits

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 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 hcas.org_id=:P_ORG_ID

    AND hca.cust_account_id = hcas.cust_account_id