In this post, we shall look at the details of Creating a Customer and the tables affecting it.

Navigation: AR Superuser -> Customers -> Customers

In the OAF page that opened, fill out the following details:

Organization Name, Account Number, Profile Class: Default (Oracle provides this profile class) and Account Site Address: Give a correct address that exists as per HZ_GEOGRAPHIES hierarchy.

Upon creating the account, a record now exists in the Hz_Cust_Accounts table

In the example below – created a Customer with Account number – 10820181

HZ_CUST_ACCOUNTS

SELECT *
  FROM hz_cust_accounts
 WHERE account_number = '10820181';

As can be seen a Party is also created with a party Id – 51284811

HZ_PARTIES

SELECT PARTY_ID, PARTY_NUMBER, PARTY_NAME, PARTY_TYPE, STATUS, JGZZ_FISCAL_CODE, H.* 
  FROM hz_parties H
 WHERE party_id = 51284811;
--- JGZZ_FISCAL_CODE holds the tax payer id.

HZ_PARTY_SITES

 SELECT PARTY_SITE_ID, PARTY_ID, LOCATION_ID, PARTY_SITE_NUMBER, STATUS, CREATED_BY_MODULE, H.*
  FROM HZ_PARTY_SITES H
 WHERE PARTY_ID = 51284811;

HZ_LOCATIONS

Get the location_id from the Hz_Party_Sites

SELECT location_id,
       ORIG_SYSTEM_REFERENCE,
       country,
       address1,
       address2,
       city,
       state,
       county,
       postal_code,
       h.*
  FROM hz_locations h
 WHERE location_id = 38779066;

HZ_CUST_ACCT_SITES_ALL

Hz_Cust_Acct_Sites_all table details about the customer account sites across all the operating units. A Customer site has addresses where the customer intends to do business at. A Customer can have more than one site and across more than one operating unit. The site signifies whether the site is a Primary Bill_To or a Primary Ship_To site. Join between the Customer table and Customer site table is the Cust_acct_id.

A Bill_To_Flag indicates if it is a bill to site.

• Y for a Bill-To site
• P for the primary Bill-To site
• N for a site that is not a Bill-To site.

A Ship_to_Flag indicates if it is a Ship to site.

• Y for a Ship-To site
• P for the primary Ship-To site
• N for a site that is not a Ship-To site

SELECT cust_acct_site_id,
       cust_account_id,
       party_site_id,
       status,
       bill_to_flag,
       h.*
  FROM hz_cust_acct_sites_all h
 WHERE cust_account_id = 46149842;

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.

SELECT site_use_id,
       cust_acct_site_id,
       site_use_code,
       primary_flag     status,
       location,
       h.*
  FROM HZ_CUST_SITE_USES_ALL h
 WHERE cust_acct_site_id = 49348980;