SQL query to Extract Contact Points from Bill-To Site, Using AR Transaction in Oracle Fusion Cloud (AR)

 

Introduction

In Oracle Fusion AR, reporting requirements often go beyond basic transaction data. Business users frequently need the complete customer communication hierarchy, especially the Bill-To contact person, their email address, site use attributes, and any assigned responsibility roles such as BILL_TO.

However, extracting this information is not straightforward because Oracle Fusion Customer Data Management (CDM) stores customer, site, role, and communication details across multiple HZ (Trading Community Architecture) tables.

This guide provides a single, end-to-end SQL query that starts with an AR Transaction (Invoice) and traces the entire path down to:

• Invoice details
• Customer account and site information
• Bill-To site use
• Contact role assignments
• Communication points such as email
• Responsibility types (e.g., BILL_TO)

Why This SQL Is Important

Most organizations need this extract to support:

• Customer communication audits
• Invoice delivery validation
• Billing compliance
• Master data troubleshooting
• AR reporting automation

Because contacts in Oracle Fusion are stored through relationships, roles, and contact points, you must traverse many joins to get the final result.

This post gives you a ready-to-use, optimized SQL that performs all these joins correctly—with ranking logic to prioritize BILL_TO contacts.

Data Hierarchy Explained

Here’s the full path the SQL follows:

1. AR Transaction

• RA_CUSTOMER_TRX_ALL → Invoice Number, Date
• Links to Bill-To Site Use

2. Customer Account & Site

• HZ_CUST_SITE_USES_ALL
• HZ_CUST_ACCT_SITES_ALL
• HZ_CUST_ACCOUNTS
• HZ_PARTY_SITES

3. Contact Role Mapping

• HZ_CUST_ACCOUNT_ROLES → role assignment at site
• HZ_ROLE_RESPONSIBILITY → BILL_TO / SHIP_TO duties

4. Contact Person

• HZ_RELATIONSHIPS → maps account to person
• HZ_PARTIES → person profile
• HZ_CONTACT_POINTS → email/phone/etc.

This ensures a reliable, hierarchy-accurate extraction.

Complete SQL: Contact Point Extraction from Bill-To Site

WITH raw_contact_data
     AS (SELECT RCTA.trx_number         AS INVOICE_NUMBER,
                RCTA.trx_date           AS INVOICE_DATE,
                -- Customer and Site
                HCA.account_number      AS CUSTOMER_ACCOUNT_NUMBER,
                HPS.party_site_number   AS SITE_NUMBER,
                HCSU.site_use_code      AS SITE_USE_CODE,
                -- Contact Details
                HP_PER.party_name       AS CONTACT_NAME,
                HCP.email_address       AS CONTACT_EMAIL_ADDRESS,
                HRR.responsibility_type AS RESPONSIBILITY_CHECK,
                -- If 'BILL_TO' exists, give it Rank 1. Else give Rank 2.
                Dense_rank()
                  over (
                    PARTITION BY RCTA.trx_number
                    ORDER BY CASE WHEN HRR.responsibility_type = 'BILL_TO' THEN
                  1
                  ELSE 2
                  END ASC
                  )                     AS rec_rank
         FROM   fusion.ra_customer_trx_all RCTA
                -- 1. Core Joins (Invoice to Account/Site)
                join fusion.hz_cust_site_uses_all HCSU
                  ON HCSU.site_use_id = RCTA.bill_to_site_use_id
                join fusion.hz_cust_acct_sites_all HCAS
                  ON HCAS.cust_acct_site_id = HCSU.cust_acct_site_id
                join fusion.hz_cust_accounts HCA
                  ON HCA.cust_account_id = HCAS.cust_account_id
                join fusion.hz_party_sites HPS
                  ON HPS.party_site_id = HCAS.party_site_id
                -- 2. Contact Roles
                left join fusion.hz_cust_account_roles HCAR
                       ON HCAR.cust_acct_site_id = HCAS.cust_acct_site_id
                          AND HCAR.role_type = 'CONTACT'
                          AND HCAR.status = 'A'
                -- 3. Responsibility Type (BILL_TO)
                left join fusion.hz_role_responsibility HRR
                       ON HRR.cust_account_role_id = HCAR.cust_account_role_id
                          AND HRR.responsibility_type = 'BILL_TO'
                          AND HRR.status_flag = 'A'
                -- 4. Links to Person
                left join fusion.hz_relationships HR
                       ON HR.relationship_id = HCAR.relationship_id
                          AND HR.subject_type = 'ORGANIZATION'
                          AND HR.object_type = 'PERSON'
                          AND HR.status = 'A'
                left join fusion.hz_parties HP_PER
                       ON HP_PER.party_id = HR.object_id
                          AND HP_PER.status = 'A'
                -- 5. Email Contact Point
                left join fusion.hz_contact_points HCP
                       ON HCP.owner_table_id = HP_PER.party_id
                          AND HCP.owner_table_name = 'HZ_PARTIES'
                          AND HCP.contact_point_type = 'EMAIL'
                          AND HCP.status = 'A'
                          AND HCP.primary_flag = 'Y'
         WHERE  RCTA.trx_number = '120251107182')

SELECT DISTINCT invoice_number,
                invoice_date,
                customer_account_number,
                site_number,
                site_use_code,
                contact_name,
                contact_email_address,
                responsibility_check
FROM   raw_contact_data
WHERE  rec_rank = 1  

How the SQL Works (Breakdown)

1. Ranking Logic

The DENSE_RANK() ensures that BILL_TO contacts appear first, falling back to any contact if none are defined.

2. Bi-Directional Relationship Handling

Oracle Fusion stores contacts using HZ_RELATIONSHIPS:

• Organization (account) → Person (contact)

The SQL handles both sides cleanly.

3. Site-Level Role Extraction

HZ_ROLE_RESPONSIBILITY is filtered to active BILL_TO responsibility types.

4. Communication Point Precision

Only the primary email is extracted to avoid duplicates.