ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223EFAF3869E053B46B63588994 SELECT ghv.HIERARCHY_NAME, ghv.DESCRIPTION, fev.ENTITY_NAME, LEVEL, gcr.start_date, gcr.end_date, decode( gcr.entity_type_code, 'E', to_number(''), decode( gcr.operassoc_parent_entity_id, to_number(''), gcr.ownership_percent, decode(gcr.entity_type_code, 'O',-1,to_number('')))) ownership_percent, decode( gcr.operassoc_parent_entity_id, to_number(''),to_number(''), decode(gcr.entity_type_code, 'O',-1,to_number(''))) ownership_order, gtv.treatment_name, gctv.curr_treatment_name, gcr.entity_type_name ENTITY_TYPE_NAME, gcr.currency_name, decode(gcr.entity_type_code,'C',2,'O',1,'E',3) entity_type FROM gcs_hierarchies_vl ghv, fem_entities_vl fev, (select gcr.hierarchy_id, gcr.parent_entity_id, gcr.child_entity_id, treatment_id, curr_treatment_id, ownership_percent, start_date, end_date, fetv.entity_type_code, fetv.entity_type_name ENTITY_TYPE_NAME, fcv.name CURRENCY_NAME, operassoc.parent_entity_id operassoc_parent_entity_id FROM gcs_cons_relationships gcr, fem_entities_attr fea, fem_entity_types_vl fetv, fnd_currencies_vl fcv, gcs_entity_cons_attrs geca, (SELECT operea.entity_id AS parent_entity_id, operea.dim_attribute_numeric_member AS child_entity_id FROM FEM_ENTITIES_ATTR operea, FEM_DIM_ATTRIBUTES_B operda, FEM_DIM_ATTR_VERSIONS_B oper_dav WHERE operda.attribute_varchar_label = 'OPERATING_ENTITY' AND operda.attribute_id = operea.attribute_id AND oper_dav.attribute_id = operda.attribute_id AND oper_dav.default_version_flag = 'Y' AND oper_dav.version_id = operea.version_id) operassoc WHERE gcr.hierarchy_id = :pHierarchy AND gcr.child_entity_id = fea.entity_id AND gcr.actual_ownership_flag = 'Y' AND fea.attribute_id = :pAttribute AND fea.version_id = :pVersion AND fea.dim_attribute_varchar_member = fetv.entity_type_code AND geca.entity_id = gcr.child_entity_id AND geca.hierarchy_id = gcr.hierarchy_id AND fcv.currency_code = geca.currency_code AND operassoc.child_entity_id (+)= gcr.child_entity_id AND operassoc.parent_entity_id (+)= gcr.parent_entity_id UNION ALL SELECT ghv.hierarchy_id, null parent_entity_id, top_entity_id child_entity_id, null treatment_id, null curr_treatment_id, null ownership_percent, start_date, null end_date, fetv.entity_type_code, fetv.entity_type_name ENTITY_TYPE_NAME, fcv.name CURRENCY_NAME, null operassoc_parent_entity_id FROM gcs_hierarchies_vl ghv, fem_entities_attr fea, fem_entity_types_vl fetv, fnd_currencies_vl fcv, gcs_entity_cons_attrs geca WHERE ghv.hierarchy_id = :pHierarchy AND fea.entity_id = top_entity_id AND fea.attribute_id = :pAttribute AND fea.version_id = :pVersion AND fea.dim_attribute_varchar_member = fetv.entity_type_code AND geca.entity_id = top_entity_id AND geca.hierarchy_id = ghv.hierarchy_id AND fcv.currency_code = geca.currency_code ) gcr, gcs_treatments_vl gtv, gcs_curr_treatments_vl gctv WHERE ghv.hierarchy_id = :pHierarchy AND gcr.hierarchy_id = ghv.hierarchy_id AND gcr.child_entity_id = fev.entity_id AND gcr.treatment_id = gtv.treatment_id(+) AND gcr.curr_treatment_id = gctv.curr_treatment_id(+) start WITH gcr.child_entity_id = :pXmlFileId AND gcr.hierarchy_id = :pHierarchy AND :pEffectiveDate BETWEEN gcr.start_date and NVL(gcr.end_date, :pEffectiveDate) CONNECT BY PRIOR gcr.child_entity_id = gcr.parent_entity_id AND gcr.hierarchy_id = :pHierarchy AND :pEffectiveDate BETWEEN gcr.start_date and NVL(gcr.end_date, :pEffectiveDate) ORDER SIBLINGS BY entity_type, ownership_order,fev.ENTITY_NAME Y GCS GCS_HIER_LIST_SOURCE US GCS Hierarchy Listing Data Source Application: Financial Consolidation Hub (Obsolete) Source: Short Name: GCS_HIER_LIST_SOURCE DB package: BI Publisher :pattribute :peffectivedate :phierarchy :pversion :pxmlfileid