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