VIEW: APPS.XLE_ASSOCIATIONS_V

Source

View Text - Preformatted

SELECT
	       assoc_types.CONTEXT,
	       assoc_obj.NAME Entity_Type,
	       assoc_subj.NAME Legal_Construct,
	       assoc_types.ASSOCIATION_TYPE_ID,
	       assoc.ASSOCIATION_ID,
	       assoc.OBJECT_ID Entity_Id,
	       ent_prof.name Entity_Name,
	       assoc.SUBJECT_ID Legal_Construct_Id ,
           xep.party_id Legal_Party_Id,
	       xep.name Legal_construct_name,
           assoc.SUBJECT_PARENT_ID Legal_Parent_Id,
	       assoc.EFFECTIVE_FROM,
	       assoc.EFFECTIVE_TO
	FROM   XLE_ASSOC_OBJECT_TYPES assoc_obj,
	       XLE_ASSOC_OBJECT_TYPES assoc_subj,
	       XLE_ASSOCIATION_TYPES  assoc_types,
	       XLE_ASSOCIATIONS       assoc,
	       XLE_ENTITY_PROFILES    ent_prof,
           xle_entity_profiles xep
	WHERE
	   assoc_obj.OBJECT_TYPE_ID = assoc_types.OBJECT_TYPE_ID
	   AND assoc_subj.OBJECT_TYPE_ID = assoc_types.SUBJECT_TYPE_ID
	   AND assoc_types.ASSOCIATION_TYPE_ID = assoc.ASSOCIATION_TYPE_ID
	   AND ent_prof.legal_entity_id = assoc.object_id
       AND assoc_obj.NAME = 'LEGAL_ENTITY'
       AND assoc.SUBJECT_ID  = xep.legal_entity_id
    UNION
SELECT
	       assoc_types.CONTEXT,
	       assoc_obj.NAME Entity_Type,
	       assoc_subj.NAME Legal_Construct,
	       assoc_types.ASSOCIATION_TYPE_ID,
	       assoc.ASSOCIATION_ID,
	       assoc.OBJECT_ID Entity_Id,
	       org.name Entity_Name,
	       assoc.SUBJECT_ID Legal_Construct_id,
           xep.party_id Legal_Party_id,
	        xep.name Legal_construct_name,
           assoc.SUBJECT_PARENT_ID Legal_Parent_Id,
	       assoc.EFFECTIVE_FROM,
	       assoc.EFFECTIVE_TO
	FROM   XLE_ASSOC_OBJECT_TYPES assoc_obj,
	       XLE_ASSOC_OBJECT_TYPES assoc_subj,
	       XLE_ASSOCIATION_TYPES  assoc_types,
	       XLE_ASSOCIATIONS     assoc,
	       HR_ALL_ORGANIZATION_UNITS  org,
           xle_etb_profiles xep
	WHERE
	       assoc_obj.OBJECT_TYPE_ID = assoc_types.OBJECT_TYPE_ID
	   AND assoc_subj.OBJECT_TYPE_ID =  assoc_types.SUBJECT_TYPE_ID
	   AND assoc_types.ASSOCIATION_TYPE_ID = assoc.ASSOCIATION_TYPE_ID
	   AND org.organization_id = assoc.object_id
	   AND assoc_obj.NAME = 'OPERATING_UNIT'
       AND assoc.SUBJECT_ID  = xep.establishment_id
UNION
 SELECT
	       assoc_types.CONTEXT,
	       assoc_obj.NAME Entity_Type,
	       assoc_subj.NAME Legal_Construct,
	       assoc_types.ASSOCIATION_TYPE_ID,
	       assoc.ASSOCIATION_ID,
	       assoc.OBJECT_ID Entity_Id,
	       OOD.organization_name Entity_Name,
	       assoc.SUBJECT_ID Legal_Construct_Id,
           xep.party_id Legal_Party_Id,
           xep.name Legal_construct_name,
	       assoc.SUBJECT_PARENT_ID Legal_Parent_Id,
	       assoc.EFFECTIVE_FROM,
	       assoc.EFFECTIVE_TO
	FROM   XLE_ASSOC_OBJECT_TYPES assoc_obj,
	       XLE_ASSOC_OBJECT_TYPES assoc_subj,
	       XLE_ASSOCIATION_TYPES  assoc_types,
	       XLE_ASSOCIATIONS       assoc,
	       ORG_ORGANIZATION_DEFINITIONS OOD,
           xle_etb_profiles xep
	WHERE
	       assoc_obj.OBJECT_TYPE_ID = assoc_types.OBJECT_TYPE_ID
	   AND assoc_subj.OBJECT_TYPE_ID = assoc_types.SUBJECT_TYPE_ID
	   AND assoc_types.ASSOCIATION_TYPE_ID = assoc.ASSOCIATION_TYPE_ID
	   AND OOD.organization_id = assoc.object_id
	   AND assoc_obj.NAME = 'INVENTORY_ORGANIZATION'
       AND assoc.SUBJECT_ID  = xep.establishment_id
	 UNION
 SELECT
	       assoc_types.CONTEXT,
	       assoc_obj.NAME Entity_Type,
	       assoc_subj.NAME Legal_Construct,
	       assoc_types.ASSOCIATION_TYPE_ID,
	       assoc.ASSOCIATION_ID,
	       assoc.OBJECT_ID Entity_Id,
	       hr_loc.LOCATION_CODE Entity_Name,
	       assoc.SUBJECT_ID Legal_Construct_Id ,
           xep.party_id Legal_Party_Id,
	       xep.name Legal_construct_name,
           assoc.SUBJECT_PARENT_ID Legal_Parent_Id,
	       assoc.EFFECTIVE_FROM,
	       assoc.EFFECTIVE_TO
	FROM   XLE_ASSOC_OBJECT_TYPES assoc_obj,
	       XLE_ASSOC_OBJECT_TYPES assoc_subj,
	       XLE_ASSOCIATION_TYPES  assoc_types,
	       XLE_ASSOCIATIONS       assoc,
	       HR_LOCATIONS_ALL    hr_loc,
           xle_etb_profiles xep
	WHERE
	       assoc_obj.OBJECT_TYPE_ID = assoc_types.OBJECT_TYPE_ID
	   AND assoc_subj.OBJECT_TYPE_ID = assoc_types.SUBJECT_TYPE_ID
	   AND assoc_types.ASSOCIATION_TYPE_ID = assoc.ASSOCIATION_TYPE_ID
	   AND hr_loc.location_id = assoc.object_id
	   AND assoc_obj.NAME in
('INVENTORY_LOCATION','SHIP_TO_LOCATION','BILL_TO_LOCATION')
       AND assoc.SUBJECT_ID  = xep.establishment_id
  UNION
SELECT
	       assoc_types.CONTEXT,
	       assoc_obj.NAME Entity_Type,
	       assoc_subj.NAME Legal_Construct,
	       assoc_types.ASSOCIATION_TYPE_ID,
	       assoc.ASSOCIATION_ID,
	       assoc.OBJECT_ID Entity_Id,
	       flex_val.FLEX_VALUE Entity_Name,
	       assoc.SUBJECT_ID Legal_Construct_Id ,
           xep.party_id Legal_Party_Id,
	       xep.name Legal_construct_name,
           assoc.SUBJECT_PARENT_ID Legal_Parent_Id,
 	       assoc.EFFECTIVE_FROM,
	       assoc.EFFECTIVE_TO
	FROM   XLE_ASSOC_OBJECT_TYPES assoc_obj,
	       XLE_ASSOC_OBJECT_TYPES assoc_subj,
	       XLE_ASSOCIATION_TYPES  assoc_types,
	       XLE_ASSOCIATIONS       assoc,
	       FND_FLEX_VALUES    flex_val,
           xle_etb_profiles xep
	WHERE
	       assoc_obj.OBJECT_TYPE_ID = assoc_types.OBJECT_TYPE_ID
	   AND assoc_subj.OBJECT_TYPE_ID =
assoc_types.SUBJECT_TYPE_ID
	   AND assoc_types.ASSOCIATION_TYPE_ID =
assoc.ASSOCIATION_TYPE_ID
	   AND flex_val.flex_value_id = assoc.object_id
	   AND assoc_obj.NAME = 'BALANCING_SEGMENT_VALUE'
       AND assoc.SUBJECT_ID  = xep.establishment_id

View Text - HTML Formatted

SELECT ASSOC_TYPES.CONTEXT
, ASSOC_OBJ.NAME ENTITY_TYPE
, ASSOC_SUBJ.NAME LEGAL_CONSTRUCT
, ASSOC_TYPES.ASSOCIATION_TYPE_ID
, ASSOC.ASSOCIATION_ID
, ASSOC.OBJECT_ID ENTITY_ID
, ENT_PROF.NAME ENTITY_NAME
, ASSOC.SUBJECT_ID LEGAL_CONSTRUCT_ID
, XEP.PARTY_ID LEGAL_PARTY_ID
, XEP.NAME LEGAL_CONSTRUCT_NAME
, ASSOC.SUBJECT_PARENT_ID LEGAL_PARENT_ID
, ASSOC.EFFECTIVE_FROM
, ASSOC.EFFECTIVE_TO FROM XLE_ASSOC_OBJECT_TYPES ASSOC_OBJ
, XLE_ASSOC_OBJECT_TYPES ASSOC_SUBJ
, XLE_ASSOCIATION_TYPES ASSOC_TYPES
, XLE_ASSOCIATIONS ASSOC
, XLE_ENTITY_PROFILES ENT_PROF
, XLE_ENTITY_PROFILES XEP WHERE ASSOC_OBJ.OBJECT_TYPE_ID = ASSOC_TYPES.OBJECT_TYPE_ID
AND ASSOC_SUBJ.OBJECT_TYPE_ID = ASSOC_TYPES.SUBJECT_TYPE_ID
AND ASSOC_TYPES.ASSOCIATION_TYPE_ID = ASSOC.ASSOCIATION_TYPE_ID
AND ENT_PROF.LEGAL_ENTITY_ID = ASSOC.OBJECT_ID
AND ASSOC_OBJ.NAME = 'LEGAL_ENTITY'
AND ASSOC.SUBJECT_ID = XEP.LEGAL_ENTITY_ID UNION SELECT ASSOC_TYPES.CONTEXT
, ASSOC_OBJ.NAME ENTITY_TYPE
, ASSOC_SUBJ.NAME LEGAL_CONSTRUCT
, ASSOC_TYPES.ASSOCIATION_TYPE_ID
, ASSOC.ASSOCIATION_ID
, ASSOC.OBJECT_ID ENTITY_ID
, ORG.NAME ENTITY_NAME
, ASSOC.SUBJECT_ID LEGAL_CONSTRUCT_ID
, XEP.PARTY_ID LEGAL_PARTY_ID
, XEP.NAME LEGAL_CONSTRUCT_NAME
, ASSOC.SUBJECT_PARENT_ID LEGAL_PARENT_ID
, ASSOC.EFFECTIVE_FROM
, ASSOC.EFFECTIVE_TO FROM XLE_ASSOC_OBJECT_TYPES ASSOC_OBJ
, XLE_ASSOC_OBJECT_TYPES ASSOC_SUBJ
, XLE_ASSOCIATION_TYPES ASSOC_TYPES
, XLE_ASSOCIATIONS ASSOC
, HR_ALL_ORGANIZATION_UNITS ORG
, XLE_ETB_PROFILES XEP WHERE ASSOC_OBJ.OBJECT_TYPE_ID = ASSOC_TYPES.OBJECT_TYPE_ID
AND ASSOC_SUBJ.OBJECT_TYPE_ID = ASSOC_TYPES.SUBJECT_TYPE_ID
AND ASSOC_TYPES.ASSOCIATION_TYPE_ID = ASSOC.ASSOCIATION_TYPE_ID
AND ORG.ORGANIZATION_ID = ASSOC.OBJECT_ID
AND ASSOC_OBJ.NAME = 'OPERATING_UNIT'
AND ASSOC.SUBJECT_ID = XEP.ESTABLISHMENT_ID UNION SELECT ASSOC_TYPES.CONTEXT
, ASSOC_OBJ.NAME ENTITY_TYPE
, ASSOC_SUBJ.NAME LEGAL_CONSTRUCT
, ASSOC_TYPES.ASSOCIATION_TYPE_ID
, ASSOC.ASSOCIATION_ID
, ASSOC.OBJECT_ID ENTITY_ID
, OOD.ORGANIZATION_NAME ENTITY_NAME
, ASSOC.SUBJECT_ID LEGAL_CONSTRUCT_ID
, XEP.PARTY_ID LEGAL_PARTY_ID
, XEP.NAME LEGAL_CONSTRUCT_NAME
, ASSOC.SUBJECT_PARENT_ID LEGAL_PARENT_ID
, ASSOC.EFFECTIVE_FROM
, ASSOC.EFFECTIVE_TO FROM XLE_ASSOC_OBJECT_TYPES ASSOC_OBJ
, XLE_ASSOC_OBJECT_TYPES ASSOC_SUBJ
, XLE_ASSOCIATION_TYPES ASSOC_TYPES
, XLE_ASSOCIATIONS ASSOC
, ORG_ORGANIZATION_DEFINITIONS OOD
, XLE_ETB_PROFILES XEP WHERE ASSOC_OBJ.OBJECT_TYPE_ID = ASSOC_TYPES.OBJECT_TYPE_ID
AND ASSOC_SUBJ.OBJECT_TYPE_ID = ASSOC_TYPES.SUBJECT_TYPE_ID
AND ASSOC_TYPES.ASSOCIATION_TYPE_ID = ASSOC.ASSOCIATION_TYPE_ID
AND OOD.ORGANIZATION_ID = ASSOC.OBJECT_ID
AND ASSOC_OBJ.NAME = 'INVENTORY_ORGANIZATION'
AND ASSOC.SUBJECT_ID = XEP.ESTABLISHMENT_ID UNION SELECT ASSOC_TYPES.CONTEXT
, ASSOC_OBJ.NAME ENTITY_TYPE
, ASSOC_SUBJ.NAME LEGAL_CONSTRUCT
, ASSOC_TYPES.ASSOCIATION_TYPE_ID
, ASSOC.ASSOCIATION_ID
, ASSOC.OBJECT_ID ENTITY_ID
, HR_LOC.LOCATION_CODE ENTITY_NAME
, ASSOC.SUBJECT_ID LEGAL_CONSTRUCT_ID
, XEP.PARTY_ID LEGAL_PARTY_ID
, XEP.NAME LEGAL_CONSTRUCT_NAME
, ASSOC.SUBJECT_PARENT_ID LEGAL_PARENT_ID
, ASSOC.EFFECTIVE_FROM
, ASSOC.EFFECTIVE_TO FROM XLE_ASSOC_OBJECT_TYPES ASSOC_OBJ
, XLE_ASSOC_OBJECT_TYPES ASSOC_SUBJ
, XLE_ASSOCIATION_TYPES ASSOC_TYPES
, XLE_ASSOCIATIONS ASSOC
, HR_LOCATIONS_ALL HR_LOC
, XLE_ETB_PROFILES XEP WHERE ASSOC_OBJ.OBJECT_TYPE_ID = ASSOC_TYPES.OBJECT_TYPE_ID
AND ASSOC_SUBJ.OBJECT_TYPE_ID = ASSOC_TYPES.SUBJECT_TYPE_ID
AND ASSOC_TYPES.ASSOCIATION_TYPE_ID = ASSOC.ASSOCIATION_TYPE_ID
AND HR_LOC.LOCATION_ID = ASSOC.OBJECT_ID
AND ASSOC_OBJ.NAME IN ('INVENTORY_LOCATION'
, 'SHIP_TO_LOCATION'
, 'BILL_TO_LOCATION')
AND ASSOC.SUBJECT_ID = XEP.ESTABLISHMENT_ID UNION SELECT ASSOC_TYPES.CONTEXT
, ASSOC_OBJ.NAME ENTITY_TYPE
, ASSOC_SUBJ.NAME LEGAL_CONSTRUCT
, ASSOC_TYPES.ASSOCIATION_TYPE_ID
, ASSOC.ASSOCIATION_ID
, ASSOC.OBJECT_ID ENTITY_ID
, FLEX_VAL.FLEX_VALUE ENTITY_NAME
, ASSOC.SUBJECT_ID LEGAL_CONSTRUCT_ID
, XEP.PARTY_ID LEGAL_PARTY_ID
, XEP.NAME LEGAL_CONSTRUCT_NAME
, ASSOC.SUBJECT_PARENT_ID LEGAL_PARENT_ID
, ASSOC.EFFECTIVE_FROM
, ASSOC.EFFECTIVE_TO FROM XLE_ASSOC_OBJECT_TYPES ASSOC_OBJ
, XLE_ASSOC_OBJECT_TYPES ASSOC_SUBJ
, XLE_ASSOCIATION_TYPES ASSOC_TYPES
, XLE_ASSOCIATIONS ASSOC
, FND_FLEX_VALUES FLEX_VAL
, XLE_ETB_PROFILES XEP WHERE ASSOC_OBJ.OBJECT_TYPE_ID = ASSOC_TYPES.OBJECT_TYPE_ID
AND ASSOC_SUBJ.OBJECT_TYPE_ID = ASSOC_TYPES.SUBJECT_TYPE_ID
AND ASSOC_TYPES.ASSOCIATION_TYPE_ID = ASSOC.ASSOCIATION_TYPE_ID
AND FLEX_VAL.FLEX_VALUE_ID = ASSOC.OBJECT_ID
AND ASSOC_OBJ.NAME = 'BALANCING_SEGMENT_VALUE'
AND ASSOC.SUBJECT_ID = XEP.ESTABLISHMENT_ID