GCS Hierarchy Listing Data Source

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