(
SELECT HN.PARENT_ID PARTY_ID
, RELACC.PARTY_ID RELATED_PARTY_ID
, RELACC.CUST_ACCOUNT_ID RELATED_CUST_ACCOUNT_ID
, RELACC.ACCOUNT_NAME CUST_ACCOUNT_NAME
, RELACC.ACCOUNT_NUMBER CUST_ACCOUNT_NUMBER
, HN.HIERARCHY_TYPE
, 'PARTY_REL_GRP_AR_PAY_TOP_DOWN' RELATIONSHIP_TYPE_GROUP_NAME
FROM HZ_HIERARCHY_NODES HN
, HZ_CUST_ACCOUNTS RELACC
WHERE /** GET ALL CHILDREN FOR GIVEN PARTY USING HIERARCHY NODES *** ADDED FOR THE INDEX USE **/
HN.PARENT_TABLE_NAME = 'HZ_PARTIES'
AND HN.PARENT_OBJECT_TYPE = 'ORGANIZATION'
/** GET ACCOUNTS FOR ALL CHILDREN ***/
AND HN.CHILD_ID = RELACC.PARTY_ID
AND HN.CHILD_TABLE_NAME = 'HZ_PARTIES'
AND HN.CHILD_OBJECT_TYPE = 'ORGANIZATION'
AND SYSDATE BETWEEN HN.EFFECTIVE_START_DATE AND HN.EFFECTIVE_END_DATE
/** CONSIDER ALL RELATIONSHIP TYPES FROM PAY TOPDOWN RELTYPEGRP **/
AND EXISTS ( SELECT 1
FROM HZ_CODE_ASSIGNMENTS CA
, HZ_RELATIONSHIP_TYPES RT
WHERE CA.CLASS_CATEGORY = 'RELATIONSHIP_TYPE_GROUP'
AND CA.OWNER_TABLE_NAME = 'HZ_RELATIONSHIP_TYPES'
AND CA.CLASS_CODE = 'PARTY_REL_GRP_AR_PAY_TOP_DOWN'
AND CA.STATUS = 'A'
AND CA.OWNER_TABLE_ID = RT.RELATIONSHIP_TYPE_ID
/** MAKE SURE THAT SUBJECT AND OBJECT ARE ORG PARTIES ***/
AND HN.HIERARCHY_TYPE = RT.RELATIONSHIP_TYPE
AND RT.OBJECT_TYPE = 'ORGANIZATION'
AND RT.SUBJECT_TYPE = 'ORGANIZATION' )
/*** --------- ***/ UNION ALL /*** --------- ***/
SELECT GETTOP.CHILD_ID PARTY_ID
, RELACC.PARTY_ID RELATED_PARTY_ID
, RELACC.CUST_ACCOUNT_ID RELATED_CUST_ACCOUNT_ID
, RELACC.ACCOUNT_NAME CUST_ACCOUNT_NAME
, RELACC.ACCOUNT_NUMBER CUST_ACCOUNT_NUMBER
, HN.HIERARCHY_TYPE , 'PARTY_REL_GRP_AR_PAY_ANY' RELATIONSHIP_TYPE_GROUP_NAME
FROM HZ_HIERARCHY_NODES GETTOP
, HZ_HIERARCHY_NODES TOP
, HZ_HIERARCHY_NODES HN
, HZ_CUST_ACCOUNTS RELACC
WHERE /** GET THE TOP PARENT/S FOR THE GIVEN PARTY FOR PAYANY HIERARCHIES **/
TOP.HIERARCHY_TYPE = GETTOP.HIERARCHY_TYPE
AND TOP.PARENT_ID = GETTOP.PARENT_ID
AND GETTOP.PARENT_TABLE_NAME = 'HZ_PARTIES'
AND GETTOP.PARENT_OBJECT_TYPE = 'ORGANIZATION'
AND TOP.PARENT_TABLE_NAME = 'HZ_PARTIES'
AND TOP.PARENT_OBJECT_TYPE = 'ORGANIZATION'
AND TOP.TOP_PARENT_FLAG = 'Y'
/** GET ALL CHILDREN FOR GIVEN ACCOUNT USING HIERARCHY NODES **/
AND HN.HIERARCHY_TYPE = TOP.HIERARCHY_TYPE
AND HN.PARENT_ID = TOP.PARENT_ID
AND HN.PARENT_TABLE_NAME = 'HZ_PARTIES'
AND HN.PARENT_OBJECT_TYPE = 'ORGANIZATION'
/** GET ACCOUNTS FOR ALL CHILDREN ***/
AND HN.CHILD_ID = RELACC.PARTY_ID
AND HN.CHILD_TABLE_NAME = 'HZ_PARTIES'
AND HN.CHILD_OBJECT_TYPE = 'ORGANIZATION'
AND SYSDATE BETWEEN GREATEST(HN.EFFECTIVE_START_DATE , TOP.EFFECTIVE_START_DATE , GETTOP.EFFECTIVE_START_DATE) AND
LEAST(HN.EFFECTIVE_END_DATE , TOP.EFFECTIVE_END_DATE , GETTOP.EFFECTIVE_END_DATE , HN.EFFECTIVE_END_DATE)
/** CONSIDER ALL RELATIONSHIP TYPES FROM PAY ANY RELTYPEGRP **/
AND EXISTS ( SELECT 1
FROM HZ_CODE_ASSIGNMENTS CA
, HZ_RELATIONSHIP_TYPES RT
WHERE CA.CLASS_CATEGORY = 'RELATIONSHIP_TYPE_GROUP'
AND CA.OWNER_TABLE_NAME = 'HZ_RELATIONSHIP_TYPES'
AND CA.CLASS_CODE = 'PARTY_REL_GRP_AR_PAY_ANY'
AND CA.STATUS = 'A'
AND CA.OWNER_TABLE_ID = RT.RELATIONSHIP_TYPE_ID
/** MAKE SURE THAT SUBJECT AND OBJECT ARE ORG PARTIES ***/
AND HN.HIERARCHY_TYPE = RT.RELATIONSHIP_TYPE
AND RT.OBJECT_TYPE = 'ORGANIZATION'
AND RT.SUBJECT_TYPE = 'ORGANIZATION')
UNION ALL
SELECT SUB.PARTY_ID ,
PARTY2.PARTY_ID RELATED_PARTY_ID ,
CU2.CUST_ACCOUNT_ID RELATED_CUST_ACCOUNT_ID
, CU2.ACCOUNT_NAME CUST_ACCOUNT_NAME
, CU2.ACCOUNT_NUMBER CUST_ACCOUNT_NUMBER
, NULL HIERARCHY_TYPE
, NULL RELATIONSHIP_TYPE_GROUP_NAME
FROM HZ_CUST_ACCT_RELATE_ALL RA ,
HZ_CUST_ACCOUNTS CU1 ,
HZ_CUST_ACCOUNTS CU2 ,
HZ_PARTIES SUB ,
HZ_PARTIES PARTY2
WHERE RA.CUST_ACCOUNT_ID = CU1.CUST_ACCOUNT_ID(+)
AND RA.RELATED_CUST_ACCOUNT_ID = CU2.CUST_ACCOUNT_ID(+)
AND RA.BILL_TO_FLAG = 'Y'
AND CU1.PARTY_ID = SUB.PARTY_ID(+)
AND CU2.PARTY_ID = PARTY2.PARTY_ID(+)
)