Oracle Fusion customer contacts query

Oracle Fusion customer contacts query

We will be discuss about oracle fusion customer contacts query. This Query will help to extract the oracle fusion customer contact details maintained at customer site level. We will get the complete customer contact information’s using this query. In this post , I will share some of the important tables of customer contacts. Please find below the details oracle fusion customer contacts query.

Most Important Tables used by oracle fusion customer contacts query

hz_cust_accounts

hz_contact_points

hz_relationships

hz_cust_account_roles

hz_party_sites

hz_cust_acct_sites_all

Detail oracle fusion customer contacts query

Here below is the SQL query working in oracle fusion , to extract the list of Customer Contact informations.

select hp.party_name,

hca.account_number,hps.party_site_number OUTPUT_NAME,

ROLE_TYPE,hcp.EMAIL_ADDRESS

,hcp.CONTACT_POINT_TYPE

,hr.RELATIONSHIP_CODE

from hz_cust_accounts hca, hz_contact_points hcp, hz_relationships hr, hz_parties hp,hz_cust_account_roles hcar,hz_cust_acct_sites_all hcsa,hz_party_sites hps

where hcp.relationship_id = hr.relationship_id and hcp.CONTACT_POINT_TYPE = 'EMAIL' and hr.RELATIONSHIP_CODE = 'CONTACT_OF' and hr.object_id = hp.party_id

and nvl(hcp.end_date,sysdate+5) >sysdate

and NVL(HCAR.STATUS,'A')='A'

and hcar.relationship_id = hcp.relationship_id and hp.party_id = hca.party_id

and hcar.CUST_ACCOUNT_ID=hca.CUST_ACCOUNT_ID

and hca.CUST_ACCOUNT_ID=hcsa.CUST_ACCOUNT_ID

and hcar.CUST_ACCT_SITE_ID=hcsa.CUST_ACCT_SITE_ID

and hcsa.party_site_id=hps.party_site_id