listagg all the things

SELECT DISTINCT hp.party_name,(SELECT hp3.party_name
          FROM hz_parties hp3,
               hz_party_relationship_v hprv
          WHERE hp3.party_id = hprv.subject_id
          AND hprv.party_id = hp2.party_id) "CONTACT",
          (SELECT NVL2(listagg(hcp2.phone_number, ', ') within GROUP (ORDER BY hcp2.phone_number), listagg(hcp2.phone_country_code || '-' || hcp2.phone_area_code || '-' || hcp2.phone_number, ', ') within GROUP (ORDER BY hcp2.phone_country_code || '-' || hcp2.phone_area_code || '-' || hcp2.phone_number), NULL)
            FROM hz_contact_points hcp2
            WHERE hcp2.owner_table_id = hp2.party_id AND hcp2.owner_table_name = 'HZ_PARTIES'
          ) phone_numbers_agg,
          (SELECT listagg(hcp2.email_address, ', ') within GROUP (ORDER BY hcp2.email_address)
            FROM hz_contact_points hcp2
            WHERE hcp2.owner_table_id = hp2.party_id AND hcp2.owner_table_name = 'HZ_PARTIES'
          ) email_addresses_agg
          FROM hz_parties hp
       inner join hz_party_sites hps ON hps.party_id = hp.party_id
       inner join hz_cust_accounts hca ON hca.party_id = hp.party_id
       inner join hz_cust_acct_sites_all hcasa ON hcasa.cust_account_id = hca.cust_account_id AND hcasa.party_site_id = hps.party_site_id
       inner join hz_locations hl ON hl.location_id = hps.location_id
       inner join hz_cust_account_roles hcar ON hcar.cust_account_id = hca.cust_account_id AND hcar.cust_acct_site_id = hcasa.cust_acct_site_id
       inner join hz_parties hp2 ON hp2.party_id = hcar.party_id
       inner join hz_cust_site_uses_all hcsua ON hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
       inner join hz_contact_points hcp ON hcp.owner_table_id = hp2.party_id AND hcp.owner_table_name = 'HZ_PARTIES'
        WHERE 1=1
        AND hcp.status = 'A'
         AND hca.status = 'A'
         AND hp.status = 'A'
         AND hcasa.status = 'A'
         AND hps.status = 'A'
         AND hcar.status = 'A'
         AND hca.account_number = 18751
         AND hcsua.status = 'A'
         AND hcsua.org_id IN ('115') -- specify organization ids