Query to get customer email address in Oracle apps r12

Query to get customer email address in Oracle apps r12

We will discuss about the query to get customer email address in oracle apps r12. This shared query will help to extract the Customer Email addresses maintained at any level in the customer master. This Query will help to extract the email address for customer from customers header, customer site and from customer contacts level. This will provide the complete email address information for customer in oracle r12. Please find below the detail query to get customer email address in oracle apps r12.

Most Important Tables Used by query to get customer email address in oracle apps r12

HZ_CONTACT_POINTS

HZ_CUST_ACCOUNTS

HZ_CUST_ACCT_SITES_ALL

HZ_CUST_ACCOUNT_ROLES

Detail SQL query to get customer email address in oracle apps r12

SELECT CUSTOMER_NAME,CUSTOMER_NUMBER,(SELECT EMAIL_ADDRESS

  FROM apps.hz_contact_points

 WHERE owner_table_name = 'HZ_PARTIES'

 and status = 'A'

 and CONTACT_POINT_TYPE='EMAIL'

   AND owner_table_id IN (SELECT party_id

                            FROM hz_cust_accounts

                           WHERE cust_account_id =A1.CUSTOMER_ID) and rownum=1) FIRST_EMAIL ,(SELECT EMAIL_ADDRESS

  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

                           WHERE cust_account_id =A1.CUSTOMER_ID) and rownum=1) SECOND_EMAIL,(SELECT EMAIL_ADDRESS

  FROM apps.hz_contact_points

  WHERE owner_table_name = 'HZ_PARTIES'

  and status = 'A'

  and CONTACT_POINT_TYPE='EMAIL'

  AND owner_table_id IN (

                        SELECT party_id

                          FROM apps.hz_cust_account_roles

                         WHERE role_type = 'CONTACT'

                         AND cust_account_id =A1.CUSTOMER_ID) and rownum=1) THIRD_EMAIL FROM apps.AR_CUSTOMERS A1