View: FUN_NET_RELATED_CUSTOMERS_V

Product:	FND - Application Object Library
Description:
Implementation/DBA Data:	APPS.FUN_NET_RELATED_CUSTOMERS_V

View Text

( 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(+) )