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 siteHZ_ROLE_RESPONSIBILITY → BILL_TO / SHIP_TO duties
4. Contact Person
HZ_RELATIONSHIPS → maps account to personHZ_PARTIES → person profileHZ_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.
