In Oracle Fusion Applications, customer information is primarily managed within several key tables and views. These tables store details about customers, their contacts, addresses, and related information. The exact table names can vary based on the version and any customization applied, but here are some commonly involved tables and views related to customer data:
Key Tables and Views for Customer Information
HZ_PARTIES- Stores basic information about parties (which can include customers, suppliers, etc.).
- Key columns:
PARTY_ID,PARTY_NAME,PARTY_TYPE, etc.
HZ_CUST_ACCOUNTS- Contains details specific to customer accounts.
- Key columns:
CUST_ACCOUNT_ID,ACCOUNT_NUMBER,PARTY_ID,STATUS, etc.
HZ_CUST_ACCOUNT_SITES_ALL- Stores address details for customer accounts.
- Key columns:
CUST_ACCOUNT_SITE_ID,CUST_ACCOUNT_ID,SITE_USE_ID,ADDRESS_ID, etc.
HZ_CUST_ACCOUNT_SITES_V- A view that provides a consolidated view of customer account sites including address information.
- Key columns:
CUST_ACCOUNT_ID,ACCOUNT_NUMBER,SITE_ID,ADDRESS, etc.
HZ_CUST_ACCT_REL- Manages relationships between different customer accounts (e.g., parent-child relationships).
- Key columns:
CUST_ACCT_REL_ID,CUST_ACCOUNT_ID,RELATIONSHIP_TYPE, etc.
HZ_CUST_ACCOUNT_CONTACTS- Contains contact information related to customer accounts.
- Key columns:
CUST_ACCOUNT_CONTACT_ID,CUST_ACCOUNT_ID,CONTACT_ID,CONTACT_NAME, etc.
HZ_LOCATIONS- Stores location details for various business entities, including customers.
- Key columns:
LOCATION_ID,ADDRESS,CITY,STATE,COUNTRY, etc.
HZ_CUSTOMERS- A table or view that might contain aggregated customer information.
- Key columns:
CUSTOMER_ID,CUSTOMER_NAME,ACCOUNT_NUMBER,STATUS, etc.
HZ_PARTY_SITES- Contains site information for parties, including customers.
- Key columns:
PARTY_SITE_ID,PARTY_ID,ADDRESS_ID,SITE_TYPE, etc.
HZ_PARTY_SITES_V- A view that aggregates party site information for easier querying.
- Key columns:
PARTY_ID,PARTY_NAME,SITE_ID,ADDRESS, etc.
Sample SQL Query
Here's a sample SQL query that joins some of these tables to retrieve basic customer information, including their account and address details:
SELECT hca.CUST_ACCOUNT_ID,
hca.ACCOUNT_NUMBER,
hca.STATUS AS ACCOUNT_STATUS,
hzp.PARTY_NAME,
hzs.SITE_USE_ID,
hzloc.ADDRESS AS CUSTOMER_ADDRESS,
hzloc.CITY,
hzloc.STATE,
hzloc.COUNTRY
FROM
HZ_CUST_ACCOUNTS hca
JOIN HZ_PARTIES hzp ON hca.PARTY_ID = hzp.PARTY_ID
JOIN HZ_CUST_ACCOUNT_SITES_ALL hzs ON hca.CUST_ACCOUNT_ID = hzs.CUST_ACCOUNT_ID
JOIN HZ_LOCATIONS hzloc ON hzs.ADDRESS_ID = hzloc.LOCATION_ID
WHERE
hca.STATUS = 'ACTIVE'
ORDER BY
hzp.PARTY_NAME;
