AR Customer Account Relationships (Oracle Fusion SQL)

Enterprise Resource Planning (ERP) Oracle Account Receivables (AR-Fusion-SQL's)
1

This AR Customer Account Relationships query retrieves relationship details between customer accounts, including primary and related customer names and numbers, the type and status of the relationship, reciprocal status, additional comments, and customer category codes.

#sqlquery

SELECT
  --SQL4Fusion (An Orbit Analytics Project)
  --This query retrieves relationship details between customer accounts
  HOP.ORGANIZATION_NAME,
  SUBSTR(HP.PARTY_NAME, 1, 50) PRIMARY_CUSTOMER_NAME,
  HCA.ACCOUNT_NUMBER PRIMARY_CUSTOMER_NUMBER,
  SUBSTR(HP2.PARTY_NAME, 1, 50) RELATED_CUSTOMER_NAME,
  HCA2.ACCOUNT_NUMBER RELATED_CUSTOMER_NUMBER,
  AL3.MEANING RELATIONSHIP_TYPE,
  AL.MEANING RELATIONSHIP_STATUS,
  AL2.MEANING RECIPROCAL_STATUS,
  HCARA.COMMENTS  ADDITIONAL_COMMENTS,
  HP.CATEGORY_CODE CATEGORY_CODE
FROM  
  HZ_CUST_ACCT_RELATE_ALL HCARA,
  HZ_CUST_ACCOUNTS HCA,
  HZ_CUST_ACCOUNTS HCA2,
  HZ_PARTIES HP,
  HZ_PARTIES HP2,
  AR_LOOKUPS AL,
  AR_LOOKUPS AL2,
  AR_LOOKUPS AL3,
  HZ_ORGANIZATION_PROFILES HOP
WHERE
  1 = 1
  AND HCARA.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
  AND HCARA.RELATED_CUST_ACCOUNT_ID = HCA2.CUST_ACCOUNT_ID
  AND HCA.PARTY_ID = HP.PARTY_ID
  AND HCA2.PARTY_ID = HP2.PARTY_ID
  AND HCARA.STATUS = AL.LOOKUP_CODE
  AND AL.LOOKUP_TYPE = 'CODE_STATUS'
  AND HCARA.CUSTOMER_RECIPROCAL_FLAG = AL2.LOOKUP_CODE
  AND AL2.LOOKUP_TYPE = 'YES/NO'
  AND HCARA.RELATIONSHIP_TYPE =AL3.LOOKUP_CODE
  AND AL3.LOOKUP_TYPE='RELATIONSHIP_TYPE'
  AND HP.PARTY_ID =HOP.PARTY_ID