eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.FUN_NET_RELATED_CUSTOMERS_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
	   ,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(+)
)

View Text - HTML Formatted

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