Oracle Fusion Customer Extract Transform Data with SQL PIVOT in Oracle Fusion: A Comprehensive Guide

Simplifying Multi-Site Customer Data for Reporting and Analysis

Oracle Fusion’s Trading Community Architecture (TCA) is a powerful framework for managing complex customer, partner, and supplier data. However, working with normalized data models often means dealing with multiple rows for related entities (e.g., shipping and billing addresses). This article dives into a practical SQL query that uses the PIVOT clause to restructure such data, making it more accessible for reporting and integrations.

---

The Challenge: Multi-Row Address Data

In Oracle Fusion, customer addresses and site uses are stored across normalized tables like HZ_CUST_SITE_USES_ALL and HZ_LOCATIONS. A single customer account might have multiple rows for different site purposes (e.g., SHIP_TO, BILL_TO), leading to fragmented data like this:

Excepted Output

Problem: To analyze or display shipping and billing details side by side, we need to consolidate these rows into a single record.

---

The Solution: SQL PIVOT Clause

The PIVOT operator in Oracle SQL transforms rows into columns, enabling a structured view of multi-use data. Let’s dissect the query step by step.

WITH
-- Party-related tables (core entities, locations, sites)
PartyCTE AS (
  SELECT
    HP.PARTY_ID,
    HP.PARTY_NUMBER,
    HP.PARTY_NAME,
    HPS.PARTY_SITE_ID,
    HPS.PARTY_SITE_NUMBER,
    HPS.LOCATION_ID,
    HPSU.PARTY_SITE_USE_ID,
    HPSU.SITE_USE_TYPE,
    HL.ADDRESS1,
    HL.ADDRESS2,
    HL.ADDRESS3,
    HL.ADDRESS4,
    HL.CITY,
    HL.POSTAL_CODE,
    HL.STATE,
    HL.COUNTRY
  FROM HZ_PARTIES HP,
       HZ_PARTY_SITES HPS,
       HZ_PARTY_SITE_USES HPSU,
       HZ_LOCATIONS HL
  WHERE HPS.PARTY_SITE_ID = HPSU.PARTY_SITE_ID
    AND HP.PARTY_ID = HPS.PARTY_ID
    AND HPS.LOCATION_ID = HL.LOCATION_ID
),

-- Customer-related tables (accounts, sites, uses)
CustomerCTE AS (
  SELECT
    HCA.CUST_ACCOUNT_ID,
    HCA.ACCOUNT_NUMBER,
    HCA.ATTRIBUTE10 AS SALESFORCE_ID,
    HCA.ACCOUNT_NAME,
    HCAS.CUST_ACCT_SITE_ID,
    HCAS.PARTY_SITE_ID,
    HCSU.SITE_USE_ID,
    HCSU.SITE_USE_CODE,
    HCSU.LOCATION
  FROM HZ_CUST_ACCOUNTS HCA,
       HZ_CUST_ACCT_SITES_ALL HCAS,
       HZ_CUST_SITE_USES_ALL HCSU
  WHERE HCA.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID
    AND HCAS.CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID
),

-- Joining PartyCTE and CustomerCTE
JoinedData AS (
  SELECT 
    C.CUST_ACCOUNT_ID,
    C.ACCOUNT_NUMBER,
    C.SALESFORCE_ID,
    C.ACCOUNT_NAME,
    P.PARTY_NAME,
    P.PARTY_SITE_USE_ID,
    P.ADDRESS1,
    P.ADDRESS2,
    P.CITY,
    P.POSTAL_CODE,
    P.STATE,
    P.COUNTRY,
    C.SITE_USE_CODE
  FROM PartyCTE P,
       CustomerCTE C
  WHERE P.PARTY_SITE_ID = C.PARTY_SITE_ID
    AND P.SITE_USE_TYPE = C.SITE_USE_CODE
)

-- Final Pivot Query
SELECT * FROM (
  SELECT 
    CUST_ACCOUNT_ID,
    ACCOUNT_NUMBER,
    SALESFORCE_ID,
    ACCOUNT_NAME,
    PARTY_NAME,
    PARTY_SITE_USE_ID,
    ADDRESS1,
    ADDRESS2,
    CITY,
    POSTAL_CODE,
    STATE,
    COUNTRY,
    SITE_USE_CODE
  FROM JoinedData
)
PIVOT (
  MIN(PARTY_SITE_USE_ID) AS PARTY_SITE_USE_ID,
  MIN(ADDRESS1) AS ADDRESS1,
  MIN(ADDRESS2) AS ADDRESS2,
  MIN(CITY) AS CITY,
  MIN(POSTAL_CODE) AS POSTAL_CODE,
  MIN(STATE) AS STATE,
  MIN(COUNTRY) AS COUNTRY
  FOR SITE_USE_CODE IN (
    'SHIP_TO' AS SHIP_TO,
    'BILL_TO' AS BILL_TO
  )
);

---

1. Subquery: Joining TCA Tables

The inner query joins seven key TCA tables to fetch customer accounts, addresses, and site uses:

Tables Involved

Join Logic

• Connect customer accounts (HCA) to parties (HP) via PARTY_ID.

• Link accounts to sites (HCAS, HCSU) using CUST_ACCOUNT_ID and PARTY_SITE_ID.

• Map party sites to addresses (HL) through LOCATION_ID.

Example Output of Subquery:

---

2. PIVOT Operation: Reshaping the Data

The PIVOT clause converts the SITE_USE_CODE values (SHIP_TO, BILL_TO) into columns.

PIVOT Syntax Breakdown

sql

Copy

PIVOT (
  MIN(PARTY_SITE_USE_ID) AS PARTY_SITE_USE_ID,
  MIN(ADDRESS1) AS ADDRESS1,
  MIN(CITY) AS CITY,
  ...  -- Other columns like STATE, POSTAL_CODE, etc.
  FOR SITE_USE_CODE IN ('SHIP_TO' AS SHIP_TO, 'BILL_TO' AS BILL_TO)
)

Why Use MIN()?

• Oracle requires an aggregate function in PIVOT. Even if there’s only one value per group, MIN() or MAX() ensures compliance with SQL syntax.

How Columns Are Transformed

• For each column in the PIVOT (e.g., ADDRESS1, CITY), two new columns are created:

  • SHIP_TO_ADDRESS1, SHIP_TO_CITY

  • BILL_TO_ADDRESS1, BILL_TO_CITY

• Other columns (e.g., PARTY_SITE_USE_ID) follow the same pattern.

---

3. Example Output After Pivoting

The pivoted result consolidates the two rows into a single, flattened structure:

Key Improvements:

• Simplified reporting: Address types are now columns, not rows.

• Easier integration with external systems (e.g., Salesforce, ERPs).

---

Why This Matters in Oracle Fusion

Use Cases

1. Customer Master Reports: Display shipping/billing details in a single row.

2. Invoice Generation: Fetch billing addresses efficiently for financial workflows.

3. Data Integration: Map pivoted columns directly to external system fields (e.g., SHIP_TO_ADDRESS1 → Salesforce ShippingStreet).

Performance Considerations

• Filter Early: Apply WHERE clauses in the subquery to reduce data before pivoting.

• Indexes: Ensure SITE_USE_CODE, CUST_ACCOUNT_ID, and PARTY_SITE_ID are indexed for faster joins.

---

Best Practices for Using PIVOT

1. Explicit Column Selection: Avoid SELECT * in production; list only necessary columns.

2. Handle NULLs: Use NVL() or COALESCE() to replace NULLs in pivoted columns.

3. Dynamic Pivoting: For variable SITE_USE_CODE values (e.g., regional codes), use dynamic SQL.

---

Conclusion

The PIVOT clause is a game-changer for working with multi-use data in Oracle Fusion’s TCA model. By transforming rows into columns, it simplifies reporting, integration, and analysis—critical for businesses managing complex customer relationships.

Next Steps:

• Experiment with pivoting other site types (e.g., DELIVER_TO, MARKETING).

• Explore UNPIVOT to reverse this transformation when needed.

With this approach, you can unlock the full potential of Oracle Fusion’s data model while keeping your queries clean and efficient.