SELECT HN.PARENT_ID PARTY_ID , RELACC.PARTY_ID RELATED_PARTY_ID , RELACC.CUST_ACCOUNT_ID RELATED_CUST_ACCOUNT_ID , HN.HIERARCHY_TYPE , 'PARTY_REL_GRP_AR_PAY_TOP_DOWN' RELATIONSHIP_TYPE_GROUP_NAME , HN.EFFECTIVE_START_DATE EFFECTIVE_START_DATE , HN.EFFECTIVE_END_DATE EFFECTIVE_END_DATE 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' /** CONSIDER ALL RELATIONSHIP TYPES FROM PAY TOPDOWN RELTYPEGRP **/ AND EXISTS ( SELECT /*+ PUSH_SUBQ */ 1 /*6775018 */ 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 , HN.HIERARCHY_TYPE , 'PARTY_REL_GRP_AR_PAY_ANY' RELATIONSHIP_TYPE_GROUP_NAME , GREATEST(HN.EFFECTIVE_START_DATE , TOP.EFFECTIVE_START_DATE , GETTOP.EFFECTIVE_START_DATE) EFFECTIVE_START_DATE , LEAST(HN.EFFECTIVE_END_DATE , TOP.EFFECTIVE_END_DATE , GETTOP.EFFECTIVE_END_DATE , HN.EFFECTIVE_END_DATE) EFFECTIVE_END_DATE 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' /** 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 GETTOP.HIERARCHY_TYPE = RT.RELATIONSHIP_TYPE AND RT.OBJECT_TYPE = 'ORGANIZATION' AND RT.SUBJECT_TYPE = 'ORGANIZATION' )