Using below query we can get dunning contact information.
SELECT
account_number customer_number,
party_name customer_name,
(
SELECT
LISTAGG(party_name, ‘,’) WITHIN GROUP(
ORDER BY
party_name
) party_name
FROM
apps.hz_org_contacts_v
WHERE
object_party_id = hp.party_id
AND party_site_id is null
AND contact_point_type in ( ‘PHONE’,’EMAIL’)
) collector_name,
(
SELECT LISTAGG(email_address, ‘,’) WITHIN GROUP(
ORDER BY
email_address
) email_address
FROM
apps.hz_org_contacts_v
WHERE
object_party_id = hp.party_id
AND party_site_id is null
AND contact_point_type = ‘EMAIL’
) collector_email,
hp.address1,
hp.address2,
hp.address3,
hp.address4,
hp.city,
hp.country,
hp.postal_code
FROM
apps.hz_cust_accounts hca,
apps.hz_parties hp
WHERE
1 = 1
and account_number=’1116721.EHD34′
AND hca.party_id = hp.party_id
AND EXISTS (
SELECT
1
FROM
apps.hz_org_contacts_v hocv1
WHERE
hocv1.object_party_id = hp.party_id
AND hocv1.party_site_id is null
AND hocv1.contact_point_type in ( ‘EMAIL’,’PHONE’)
AND hocv1.email_address IS NOT NULL
);
