VIEW: APPS.AR_PAYING_RELATIONSHIPS_V

Source

View Text - Preformatted

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' )

View Text - HTML Formatted

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' )