FND Design Data [Home] [Help]

View: AR_PAYING_RELATIONSHIPS_V

Product: AR - Receivables
Description: (Release 11.5 Only)
Implementation/DBA Data: ViewAPPS.AR_PAYING_RELATIONSHIPS_V
View Text

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
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 /*+ PUSH_SUBQ */ 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' )

Columns

Name
PARTY_ID
RELATED_PARTY_ID
RELATED_CUST_ACCOUNT_ID
RELATIONSHIP_TYPE
RELATIONSHIP_TYPE_GROUP_NAME
EFFECTIVE_START_DATE
EFFECTIVE_END_DATE