This AR Customer Contact Details query retrieves active customer contact details in Oracle Fusion, focusing on contacts linked to customer accounts via relationships.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves contact details for active customers
HP.PARTY_NAME CUSTOMER_NAME,
HCA.ACCOUNT_NUMBER CUSTOMER_ACCOUNT_NUMBER,
HPS.PARTY_SITE_NUMBER CUSTOMER_SITE_NUMBER,
HCP.EMAIL_ADDRESS CUSTOMER_EMAIL_ADDRESS,
HCP.PHONE_TYPE CUSTOMER_PHONE_TYPE,
HCP.STATUS CUSTOMER_CONTACT_POINT_STATUS,
HCP.CONTACT_POINT_TYPE,
HCP.JOB_DEFINITION_NAME,
HCP.PHONE_AREA_CODE,
HCP.PHONE_NUMBER,
HR.RELATIONSHIP_CODE,
HR.RELATIONSHIP_TYPE,
HR.STATUS RELATIONSHIP_STATUS,
HCAR.ROLE_TYPE
FROM
HZ_CONTACT_POINTS HCP,
HZ_RELATIONSHIPS HR,
HZ_PARTIES HP,
HZ_CUST_ACCOUNTS HCA,
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(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
AND HCA.STATUS='A'