This AR Customer Registry Relationships query retrieves active customer relationship hierarchy details, including the primary and related customers, their account numbers, relationship types and codes, and relationship duration.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves active customer relationship hierarchy details
HOP.ORGANIZATION_NAME,
HP.PARTY_NAME PRIMARY_CUSTOMER,
HP.PARTY_NUMBER PRIMARY_CUSTOMER_NUMBER,
HP.COUNTRY,
HP.CATEGORY_CODE,
HCA.ACCOUNT_NUMBER PRIMARY_CUSTOMER_ACCOUNT,
HCA.CUSTOMER_TYPE,
HCA.STATUS CUSTOMER_ACCOUNT_STATUS,
HCA.TAX_ROUNDING_RULE,
HP2.PARTY_NAME RELATED_CUSTOMER_NAME,
HP2.PARTY_NUMBER RELATED_CUSTOMER_NUMBER,
HR.START_DATE FROM_DATE,
HR.END_DATE TO_DATE,
HR.RELATIONSHIP_CODE,
HR.RELATIONSHIP_TYPE
FROM
HZ_RELATIONSHIPS HR,
HZ_PARTIES HP,
HZ_PARTIES HP2,
HZ_CUST_ACCOUNTS HCA,
AR_LOOKUPS AL,
AR_LOOKUPS AL2,
AR_LOOKUPS AL3,
HZ_RELATIONSHIP_TYPES HRT,
HZ_ORGANIZATION_PROFILES HOP
WHERE
HR.SUBJECT_ID = HP.PARTY_ID
AND HR.OBJECT_ID = HP2.PARTY_ID
AND HCA.PARTY_ID = HP.PARTY_ID
AND HR.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HR.SUBJECT_TYPE = 'ORGANIZATION'
AND AL.LOOKUP_TYPE(+) = 'HZ_RELATIONSHIP_TYPE'
AND AL.LOOKUP_CODE(+) = HR.RELATIONSHIP_TYPE
AND AL2.LOOKUP_TYPE(+) = 'PARTY_RELATIONS_TYPE'
AND AL2.LOOKUP_CODE(+) = HR.RELATIONSHIP_CODE
AND HRT.RELATIONSHIP_TYPE(+) = HR.RELATIONSHIP_TYPE
AND HRT.SUBJECT_TYPE(+) = HR.SUBJECT_TYPE
AND HRT.OBJECT_TYPE(+) = HR.OBJECT_TYPE
AND HRT.FORWARD_REL_CODE(+) = HR.RELATIONSHIP_CODE
AND HRT.HIERARCHICAL_FLAG(+) = 'Y'
AND AL3.LOOKUP_TYPE(+) = 'PARTY_RELATIONS_TYPE'
AND AL3.LOOKUP_CODE(+) = HRT.BACKWARD_REL_CODE
AND HP.PARTY_ID = HOP.PARTY_ID
AND HCA.ACCOUNT_TERMINATION_DATE>=SYSDATE
AND EXISTS (
SELECT 1 FROM HZ_CUST_ACCOUNTS WHERE PARTY_ID = HR.SUBJECT_ID
)
AND EXISTS (
SELECT 1 FROM HZ_CUST_ACCOUNTS WHERE PARTY_ID = HR.OBJECT_ID
)