AR Customer Relationship Groups (Oracle Fusion SQL)

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

This AR customer relationship groups query retrieves customer relationship details in oracle fusion, including customer name, account number, account name, relationship type and relationship status.

#sqlquery

SELECT
  --SQL4Fusion (An Orbit Analytics Project)
  --This query retrieves customer relationship details
  HP.PARTY_NAME CUSTOMER_NAME,
  HCAS.ACCOUNT_NUMBER CUSTOMER_NUMBER,
  HCAS.ACCOUNT_NAME CUSTOMER_ACCOUNT_NAME,
  FLV.MEANING RELATIONSHIP_TYPE,
  FLV2.MEANING RELATIONSHIP_TYPE_GROUP,
  HR.STATUS RELATIONSHIP_STATUS,
  HR.COMMENTS,
  HRT.FORWARD_REL_CODE FORWARD_RELATIONSHIP_CODE,
  HRT.BACKWARD_REL_CODE BACKWARD_RELATIONSHIP_CODE,
  FLV3.MEANING RELATIONSHIP_ROLE
FROM
  HZ_CODE_ASSIGNMENTS HCA,
  HZ_RELATIONSHIP_TYPES HRT,
  FND_LOOKUP_VALUES_VL FLV,
  FND_LOOKUP_VALUES_VL FLV2,
  HZ_RELATIONSHIPS HR,
  HZ_PARTIES HP,
  HZ_CUST_ACCOUNTS HCAS,
  FND_LOOKUP_VALUES_VL FLV3
WHERE
  1 = 1
  AND HCA.OWNER_TABLE_ID = HRT.RELATIONSHIP_TYPE_ID
  AND HCA.CLASS_CATEGORY = 'RELATIONSHIP_TYPE_GROUP'
  AND HCA.OWNER_TABLE_NAME = 'HZ_RELATIONSHIP_TYPES'
  AND HCA.STATUS = 'A'
  AND HRT.RELATIONSHIP_TYPE = FLV.LOOKUP_CODE
  AND FLV.LOOKUP_TYPE = 'HZ_RELATIONSHIP_TYPE'
  AND HCA.CLASS_CODE = FLV2.LOOKUP_CODE
  AND FLV2.LOOKUP_TYPE = 'RELATIONSHIP_TYPE_GROUP'
  AND HRT.OBJECT_TYPE = 'ORGANIZATION'
  AND HRT.SUBJECT_TYPE = 'ORGANIZATION'
  AND HRT.RELATIONSHIP_TYPE = HR.RELATIONSHIP_TYPE
  AND HR.SUBJECT_ID = HP.PARTY_ID
  AND HP.PARTY_ID = HCAS.PARTY_ID
  AND HRT.ROLE=FLV3.LOOKUP_CODE
  AND FLV3.LOOKUP_TYPE='HZ_RELATIONSHIP_ROLE'