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
SELECT * FROM hz_cust_accounts WHERE account_number = '10820181';
As can be seen a Party is also created with a party Id – 51284811
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.
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;
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 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;
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;