Description
Learn how to build a Dunning Letter Delivery Details Report in Oracle Fusion using SQL. This query retrieves customer account and site details, collector names, and site contact information (email, phone) for efficient AR collection follow-ups.
Introduction
In Oracle Fusion Cloud Financials, the Dunning Letter Delivery Details Report is an essential tool used by Accounts Receivable (AR) and Collection teams to track the status of Dunning Letters — reminders sent to customers for overdue invoices.
However, the standard seeded report often lacks complete contact-level details such as email addresses and phone numbers associated with each customer site. Without this, collection agents must manually look up contacts, making follow-up inefficient.
This article provides a custom SQL query that enriches the Dunning Letter Delivery Details Report by fetching customer account numbers, site properties, dunning details, and primary contact information directly from Oracle Fusion HZ and AR tables.
Business Use Case
The Dunning Letter Delivery Details Report helps AR departments to:
-
Track when and how dunning letters were sent (email, print, or manual).
-
Verify delivery status (e.g., “Sent”, “Failed”, “In Process”).
-
Identify customer contacts responsible for communication at the site level.
-
Align collectors with specific accounts and sites for better accountability.
This customized SQL extends the functionality of the standard report by integrating HZ (Customer Master), IEX (Collections), and AR (Payments) tables — offering a single unified view.
Oracle SQL – Dunning Letter Delivery Details Report
Below is the full SQL query that fetches Dunning Letter details, Account and Site information, and primary contact email/phone for each site.
SELECT hca.account_number AS ACCOUNT_NUMBER,
hp.party_name AS PARTY_NAME,
hca.account_name AS ACCOUNT_NAME,
hro.NAME AS BU_NAME,
hps.party_site_number AS BILL_TO_SITE_NUMBER,
hps.party_site_name AS PROPERTY_NAME,
rsua.location AS LOCATION_SITE_USE_IDENTIFIER,
Sum(aps.amount_due_original) AS TOTAL_AMOUNT_ORIGINAL,
d.amount_due_remaining,
d.letter_name,
d.status,
d.contact_destination,
d.delivery_status AS EMAIL_DELIVERY_STATUS,
d.created_by,
d.creation_date,
d.dunning_method,
coll_acc.NAME AS Account_Collector_Name,
coll_site.NAME AS Site_Collector_Name,
hp_contact.person_first_name
|| ' '
|| hp_contact.person_last_name AS CONTACT_FULL_NAME,
hcp_email.email_address AS CONTACT_EMAIL_ADDRESS,
hcp_phone.phone_number AS CONTACT_PHONE_NUMBER
FROM iex_dunnings d
JOIN hz_cust_accounts hca
ON d.cust_account_id = hca.cust_account_id
JOIN hr_organization_units_f_tl hro
ON d.bu_id = hro.organization_id
LEFT JOIN hz_cust_site_uses_all rsua
ON d.site_use_id = rsua.site_use_id
LEFT JOIN hz_cust_acct_sites_all hcasa
ON hcasa.cust_acct_site_id = rsua.cust_acct_site_id
LEFT JOIN hz_party_sites hps
ON hcasa.party_site_id = hps.party_site_id
LEFT JOIN iex_dunning_transactions dt
ON dt.dunning_id = d.dunning_id
LEFT JOIN ar_payment_schedules_all aps
ON aps.customer_trx_id = dt.cust_trx_id
LEFT JOIN hz_customer_profiles_f prof_acc
ON prof_acc.cust_account_id = d.cust_account_id
AND prof_acc.site_use_id IS NULL
AND Trunc(sysdate) BETWEEN Trunc(prof_acc.effective_start_date)
AND
Trunc(prof_acc.effective_end_date)
LEFT JOIN ar_collectors coll_acc
ON prof_acc.collector_id = coll_acc.collector_id
LEFT JOIN hz_customer_profiles_f prof_site
ON prof_site.cust_account_id = d.cust_account_id
AND prof_site.site_use_id = d.site_use_id
LEFT JOIN ar_collectors coll_site
ON prof_site.collector_id = coll_site.collector_id
LEFT JOIN hz_cust_account_roles hcr
ON hcr.cust_acct_site_id = hcasa.cust_acct_site_id
AND hcr.status = 'A'
AND hcr.role_type = 'CONTACT'
LEFT JOIN hz_relationships hr
ON hr.relationship_id = hcr.relationship_id
AND hr.relationship_type = 'CONTACT'
AND hr.directional_flag = 'F'
LEFT JOIN hz_parties hp_contact
ON hr.subject_id = hp_contact.party_id
LEFT JOIN hz_contact_points hcp_email
ON hcp_email.owner_table_name = 'HZ_PARTIES'
AND hcp_email.owner_table_id = hp_contact.party_id
AND hcp_email.contact_point_type = 'EMAIL'
AND hcp_email.primary_flag = 'Y'
LEFT JOIN hz_contact_points hcp_phone
ON hcp_phone.owner_table_name = 'HZ_PARTIES'
AND hcp_phone.owner_table_id = hp_contact.party_id
AND hcp_phone.contact_point_type = 'PHONE'
AND hcp_phone.primary_flag = 'Y'
WHERE 1 = 1
--and d.creation_date > sysdate - 200
AND hca.account_number = :account_number
AND hps.party_site_number = :party_site_number
GROUP BY hca.account_number,
hca.account_name,
hro.NAME,
hps.party_site_number,
hps.party_site_name,
rsua.location,
d.amount_due_remaining,
d.letter_name,
d.status,
d.contact_destination,
d.delivery_status,
d.created_by,
d.creation_date,
d.dunning_method,
coll_acc.NAME,
coll_site.NAME,
hp_contact.person_first_name,
hp_contact.person_last_name,
hcp_email.email_address,
hcp_phone.phone_number
ORDER BY d.creation_date DESC
No comments:
Post a Comment