Query to find customer dunning contact email id

 SELECT
    account_number    account_number,
    obj.party_name    customer_name,
    sub.person_first_name,
    sub.person_last_name,
    sub.party_name    contact_name,
    LISTAGG(hcp.email_address, ',') WITHIN GROUP(
        ORDER BY
            hca.cust_account_id
    )                 emild_list,
    hrr.responsibility_type
FROM
    apps.hz_cust_accounts     hca,
    apps.hz_parties           obj,
    apps.hz_relationships     rel,
    apps.hz_contact_points    hcp,
    apps.hz_parties           sub,
    hz_cust_account_roles     hcar,
    hz_role_responsibility    hrr
WHERE
        hca.party_id = rel.object_id
    AND hca.party_id = obj.party_id
    AND rel.subject_id = sub.party_id
    AND rel.relationship_type = 'CONTACT'
    AND rel.directional_flag = 'F'
    AND rel.party_id = hcp.owner_table_id
   -- and hca.account_number=  '752588'
    AND hcp.owner_table_name = 'HZ_PARTIES'
    AND hcar.cust_account_id = hca.cust_account_id
    AND hcar.party_id = rel.party_id
    AND hrr.cust_account_role_id (+) = hcar.cust_account_role_id
    AND hrr.responsibility_type = 'DUN'
    AND hcp.contact_point_type = 'EMAIL'
GROUP BY
    account_number,
    obj.party_name,
    sub.person_first_name,
    sub.person_last_name,
    sub.party_name,
    hrr.responsibility_type