Query to find Customer Billto/Shipto Address and contact email id and Phone number and information

SELECT distinct

    hp.party_id,

    hca.cust_account_id,

    hcsua.site_use_id,

    hcasa.org_id,

    hca.account_number     customer_number,

    hp.party_name          customer_name,

    hca.attribute5,

    hcp.send_statements,

    hcp.dunning_letters    send_dunning_letters,

    hps.party_site_number  billto_shipto_location,

    hcsua.site_use_code,

    decode(hcsua.site_use_code,'BILL_TO',hl.address1

    || ' '

    || hl.address2

    || ' '

    || hl.city

    || ' '

    || hl.state

    || ' '

    || hl.postal_code

    || ' '

    || hl.country)     bill_to_address,

    decode(hcsua.site_use_code,'SHIP_TO',hl.address1

    || ' '

    || hl.address2

    || ' '

    || hl.city

    || ' '

    || hl.state

    || ' '

    || hl.postal_code

    || ' '

    || hl.country)     ship_to_address,

    translate(TRIM(hcasa.attribute3), 'x'

                                           || CHR(13)

                                           || CHR(9)

                                           || '|',

              'x')         invoice_billto_email_address,

    decode(hcasa.attribute2, 'E', decode(hcasa.attribute3, NULL, 'Postal Mail', 'Email'), 'Postal Mail')   delivery_method,          

    (

        SELECT

            LISTAGG(hcp.email_address, ' , ') WITHIN GROUP(

                ORDER BY

                    hcar.cust_account_id

            ) emild_list

        FROM

            hz_relationships        rel,

            hz_contact_points       hcp,

            hz_cust_account_roles   hcar,

            hz_role_responsibility  hrr

        WHERE

                1 = 1

            AND rel.object_id = hp.party_id

            AND rel.party_id = hcp.owner_table_id

            AND rel.relationship_type = 'CONTACT'

            AND rel.directional_flag = 'F'

            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'

    )                      dunning_email_address,

    (

        SELECT

            LISTAGG(hcp.raw_phone_number, ' , ') WITHIN GROUP(

                ORDER BY

                    hcar.cust_account_id

            ) phone_list

        FROM

            hz_relationships        rel,

            hz_contact_points       hcp,

            hz_cust_account_roles   hcar,

            hz_role_responsibility  hrr

        WHERE

                1 = 1

            AND rel.object_id = hp.party_id

            AND rel.party_id = hcp.owner_table_id

            AND rel.relationship_type = 'CONTACT'

            AND rel.directional_flag = 'F'

            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 = 'PHONE'

    )                      dunning_phone

FROM

    hz_cust_accounts        hca,

    hz_parties              hp,

    hz_cust_acct_sites_all  hcasa,

    hz_cust_site_uses_all   hcsua,

    hz_party_sites          hps,

    hz_customer_profiles    hcp,

    hz_locations            hl

WHERE

        1 = 1

    AND hp.party_id = hca.party_id

    AND hca.status = 'A'

    AND hca.cust_account_id = hcasa.cust_account_id

    AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id

    AND hcasa.org_id = hcsua.org_id

    AND hcasa.party_site_id = hps.party_site_id

    AND hp.party_id = hps.party_id

    AND hps.location_id = hl.location_id

    AND hca.cust_account_id = hcp.cust_account_id

    AND hcp.site_use_id IS NULL

    AND hcsua.site_use_code in ('BILL_TO','SHIP_TO')

    AND hcasa.status = 'A'

    AND hcsua.status = 'A'

    AND hps.status = 'A'

    AND hp.party_type = 'ORGANIZATION'

    AND hcasa.org_id IN ( 103 )

    --AND hca.account_number in ('1432072','1498470','1448442')--'2408'

    ;