Query to find Email-Id and Phone Number of Customer in APPS R12

 

--Email at Party Header Level
SELECT hca.cust_account_id,
       ac.customer_number,
       ac.customer_name,
       hcp.email_address,
       hcp.created_by_module,
       hcp.primary_flag,
       hcp.status
FROM   hz_contact_points hcp,
       hz_cust_accounts hca,
       ar_customers ac
WHERE  owner_table_name = 'HZ_PARTIES'
       AND ac.customer_id = hca.cust_account_id
       AND hcp.status = 'A'
       AND hca.party_id = hcp.owner_table_id
       AND hcp.contact_point_type = 'EMAIL'
       AND hca.cust_account_id IN (SELECT cust_account_id
                                   FROM   hz_cust_acct_sites_all hcas
                                   WHERE  hcas.status = 'A'

                                          AND org_id = <OU_ID>); 

--PHONE  NO AT PARTY HEADER LEVEL
SELECT hca.cust_account_id,
       ac.customer_number,
       ac.customer_name,
       hcp.phone_line_type,
       hcp.phone_number,
       hcp.created_by_module,
       hcp.primary_flag,
       hcp.status
FROM   hz_contact_points hcp,
       hz_cust_accounts hca,
       ar_customers ac
WHERE  owner_table_name = 'HZ_PARTIES'
       AND ac.customer_id = hca.cust_account_id
       AND hcp.status = 'A'
       AND hca.party_id = hcp.owner_table_id
       AND hcp.contact_point_type = 'PHONE'

       AND hca.cust_account_id IN (SELECT cust_account_id
                                   FROM   hz_cust_acct_sites_all hcas
                                   WHERE  hcas.status = 'A'

                                          AND org_id = <OU_ID>);;

--WHATSAPP Number At Party Header Level
SELECT hca.cust_account_id,
       ac.customer_number,
       ac.customer_name,
       hcp.phone_number WA_NO,
       hcp.created_by_module,
       hcp.primary_flag,
       hcp.status
FROM   hz_contact_points hcp,
       hz_cust_accounts hca,
       ar_customers ac
WHERE  owner_table_name = 'HZ_PARTIES'
       AND ac.customer_id = hca.cust_account_id
       AND hcp.status = 'A'
       AND hca.party_id = hcp.owner_table_id
       AND hcp.contact_point_type = 'PHONE'
       AND hcp.phone_line_type = 'WHATSAPP'; 

--Email Address At Customer's BillTo Site
SELECT   hp.party_name,
         Listagg(DISTINCT hcp.email_address, ', ') within GROUP(ORDER BY NULL) email
FROM     hz_parties hp,
         hz_cust_accounts_all hca,
         apps.hz_cust_acct_sites_all cas,
         apps.hz_cust_site_uses_all hcsu,
         apps.hz_contact_points hcp,
         apps.hz_party_sites hps,
         apps.hz_locations loc
WHERE    1 = 1
AND      hp.party_id = hca.party_id
AND      cas.cust_account_id = hca.cust_account_id
AND      hcp.owner_table_id = cas.party_site_id
AND      hcp.owner_table_name = 'HZ_PARTY_SITES'
AND      hcp.contact_point_type = 'EMAIL'
AND      site_use_code = 'BILL_TO'
AND      cas.party_site_id = hps.party_site_id
AND      hps.location_id = loc.location_id
AND      cas.cust_acct_site_id = hcsu.cust_acct_site_id
AND      hcp.status = 'A'
GROUP BY hp.party_name;

(OR)

SELECT customer_name,
       customer_number,
       a1.customer_id,
       (
                SELECT   listagg(email_address, ', ') within GROUP(ORDER BY NULL)
                FROM     apps.hz_contact_points
                WHERE    contact_point_type = 'EMAIL'
                AND      owner_table_name = 'HZ_PARTY_SITES'
                AND      status = 'A'
                AND      owner_table_id IN
                         (
                                SELECT party_site_id
                                FROM   hz_cust_acct_sites_all hcsa,
                                       hz_cust_site_uses_all hcsu
                                WHERE  1 = 1
                                AND    hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
                                AND    hcsu.site_use_code = 'BILL_TO'
                                AND    cust_account_id = a1.customer_id)) customer_site_email
FROM   apps.ar_customers a1;