SQL Queries tables for the Customers in Oracle Fusion.

 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

1. HZ_PARTIES

   • Stores basic information about parties (which can include customers, suppliers, etc.).
   • Key columns: PARTY_ID, PARTY_NAME, PARTY_TYPE, etc.

2. HZ_CUST_ACCOUNTS

   • Contains details specific to customer accounts.
   • Key columns: CUST_ACCOUNT_ID, ACCOUNT_NUMBER, PARTY_ID, STATUS, etc.

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

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

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

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

7. HZ_LOCATIONS

   • Stores location details for various business entities, including customers.
   • Key columns: LOCATION_ID, ADDRESS, CITY, STATE, COUNTRY, etc.

8. HZ_CUSTOMERS

   • A table or view that might contain aggregated customer information.
   • Key columns: CUSTOMER_ID, CUSTOMER_NAME, ACCOUNT_NUMBER, STATUS, etc.

9. HZ_PARTY_SITES

   • Contains site information for parties, including customers.
   • Key columns: PARTY_SITE_ID, PARTY_ID, ADDRESS_ID, SITE_TYPE, etc.

10. 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;

If you have access to Oracle SQL Developer or another SQL querying tool, you can explore the available tables and views to find the exact structure and modify as per your tool needs.