HZ_EXP_TERR_GEOGRAPHIES_V

Details

• Schema: FUSION

• Object owner: HZ

• Object type: VIEW

Columns

Name

LEVEL_NUMBER GEOGRAPHY_ID GEOGRAPHY_TYPE GEOGRAPHY_NAME GEOGRAPHY_USE GEOGRAPHY_CODE START_DATE END_DATE MULTIPLE_PARENT_FLAG TOP_PARENT_FLAG CREATED_BY_MODULE COUNTRY_CODE GEOGRAPHY_ELEMENT1 GEOGRAPHY_ELEMENT2 GEOGRAPHY_ELEMENT3 GEOGRAPHY_ELEMENT4 GEOGRAPHY_ELEMENT5 GEOGRAPHY_ELEMENT6 GEOGRAPHY_ELEMENT7 GEOGRAPHY_ELEMENT8 GEOGRAPHY_ELEMENT9 GEOGRAPHY_ELEMENT10 GEO_NUMBER PARENT_GEOGRAPHY_ID PARENT_GEOGRAPHY_TYPE PARENT_GEOGRAPHY_NAME PARENT_GEOGRAPHY_USE PARENT_GEOGRAPHY_CODE PARENT_START_DATE PARENT_END_DATE PARENT_MULTIPLE_PARENT_FLAG PARENT_CREATED_BY_MODULE PARENT_COUNTRY_CODE PARENT_GEOGRAPHY_ELEMENT1 PARENT_GEOGRAPHY_ELEMENT2 PARENT_GEOGRAPHY_ELEMENT3 PARENT_GEOGRAPHY_ELEMENT4 PARENT_GEOGRAPHY_ELEMENT5 PARENT_GEOGRAPHY_ELEMENT6 PARENT_GEOGRAPHY_ELEMENT7 PARENT_GEOGRAPHY_ELEMENT8 PARENT_GEOGRAPHY_ELEMENT9 PARENT_GEOGRAPHY_ELEMENT10 PARENT_GEO_NUMBER RELATIONSHIP_ID INCLUDE_CHILD_GEOGRAPHY_TYPE1 INCLUDE_CHILD_GEOGRAPHY_TYPE2 INCLUDE_CHILD_GEOGRAPHY_TYPE3 INCLUDE_CHILD_GEOGRAPHY_TYPE4 INCLUDE_CHILD_GEOGRAPHY_TYPE5 INCLUDE_CHILD_GEOGRAPHY_TYPE6 INCLUDE_CHILD_GEOGRAPHY_TYPE7 INCLUDE_CHILD_GEOGRAPHY_TYPE8 INCLUDE_CHILD_GEOGRAPHY_TYPE9 INCLUDE_CHILD_GEOGRAPHY_TYPE10 HIERARCHY_NODE_ID LAST_UPDATE_DATE

Query

SQL_Statement

SELECT NULL AS level_number, c.geography_id AS geography_id, c.geography_type AS geography_type, c.geography_name AS geography_name, c.geography_use as geography_use, c.geography_code as geography_code, c.start_date as start_date, c.end_date as end_date, c.multiple_parent_flag as multiple_parent_flag, 'N' AS top_parent_flag, c.created_by_module as created_by_module, c.country_code as country_code, c.geography_element1 as geography_element1, c.geography_element2 as geography_element2, c.geography_element3 as geography_element3, c.geography_element4 as geography_element4, c.geography_element5 as geography_element5, c.geography_element6 as geography_element6, c.geography_element7 as geography_element7, c.geography_element8 as geography_element8, c.geography_element9 as geography_element9, c.geography_element10 as geography_element10, c.geography_number as geo_number, p.geography_id AS parent_geography_id, p.geography_type as parent_geography_type, p.geography_name as parent_geography_name, p.geography_use as parent_geography_use, p.geography_code as parent_geography_code, p.start_date as parent_start_date, p.end_date as parent_end_date, p.multiple_parent_flag as parent_multiple_parent_flag, p.created_by_module as parent_created_by_module, p.country_code as parent_country_code, p.geography_element1 as parent_geography_element1, p.geography_element2 as parent_geography_element2, p.geography_element3 as parent_geography_element3, p.geography_element4 as parent_geography_element4, p.geography_element5 as parent_geography_element5, p.geography_element6 as parent_geography_element6, p.geography_element7 as parent_geography_element7, p.geography_element8 as parent_geography_element8, p.geography_element9 as parent_geography_element9, p.geography_element10 as parent_geography_element10, p.geography_number as parent_geo_number, rec.relationship_id AS relationship_id, NULL as INCLUDE_CHILD_GEOGRAPHY_TYPE1, NULL as INCLUDE_CHILD_GEOGRAPHY_TYPE2, NULL as INCLUDE_CHILD_GEOGRAPHY_TYPE3, NULL as INCLUDE_CHILD_GEOGRAPHY_TYPE4, NULL as INCLUDE_CHILD_GEOGRAPHY_TYPE5, NULL as INCLUDE_CHILD_GEOGRAPHY_TYPE6, NULL as INCLUDE_CHILD_GEOGRAPHY_TYPE7, NULL as INCLUDE_CHILD_GEOGRAPHY_TYPE8, NULL as INCLUDE_CHILD_GEOGRAPHY_TYPE9, NULL as INCLUDE_CHILD_GEOGRAPHY_TYPE10, NULL as hierarchy_node_id, rec.last_update_date as last_update_date FROM hz_relationships rec, hz_geographies c , hz_geographies p WHERE rec.subject_id = c.geography_id AND rec.object_id = p.geography_id AND rec.subject_table_name = rec.object_table_name AND rec.subject_table_name = 'HZ_GEOGRAPHIES' AND rec.RELATIONSHIP_TYPE = 'TM' AND rec.STATUS = 'A' AND trunc(sysdate) between trunc(rec.start_date) and trunc(rec.end_date) AND rec.relationship_code = 'CHILD_OF' AND rec.directional_flag = 'B' UNION ALL SELECT 0 AS level_number, c.geography_id AS geography_id, c.geography_type AS geography_type, c.geography_name AS geography_name, c.geography_use as geography_use, c.geography_code as geography_code, c.start_date as start_date, c.end_date as end_date, c.multiple_parent_flag as multiple_parent_flag, rec.top_parent_flag AS top_parent_flag, c.created_by_module as created_by_module, c.country_code as country_code, c.geography_element1 as geography_element1, c.geography_element2 as geography_element2, c.geography_element3 as geography_element3, c.geography_element4 as geography_element4, c.geography_element5 as geography_element5, c.geography_element6 as geography_element6, c.geography_element7 as geography_element7, c.geography_element8 as geography_element8, c.geography_element9 as geography_element9, c.geography_element10 as geography_element10, c.geography_number as geo_number, NULL as parent_geography_id, NULL as parent_geography_type, NULL as parent_geography_name, NULL as parent_geography_use, NULL as parent_geography_code, NULL as parent_start_date, NULL as parent_end_date, NULL as parent_multiple_parent_flag, NULL as parent_created_by_module, NULL as parent_country_code, NULL as parent_geography_element1, NULL as parent_geography_element2, NULL as parent_geography_element3, NULL as parent_geography_element4, NULL as parent_geography_element5, NULL as parent_geography_element6, NULL as parent_geography_element7, NULL as parent_geography_element8, NULL as parent_geography_element9, NULL as parent_geography_element10, NULL AS parent_geo_number, rec.relationship_id AS relationship_id, NULL as INCLUDE_CHILD_GEOGRAPHY_TYPE1, NULL as INCLUDE_CHILD_GEOGRAPHY_TYPE2, NULL as INCLUDE_CHILD_GEOGRAPHY_TYPE3, NULL as INCLUDE_CHILD_GEOGRAPHY_TYPE4, NULL as INCLUDE_CHILD_GEOGRAPHY_TYPE5, NULL as INCLUDE_CHILD_GEOGRAPHY_TYPE6, NULL as INCLUDE_CHILD_GEOGRAPHY_TYPE7, NULL as INCLUDE_CHILD_GEOGRAPHY_TYPE8, NULL as INCLUDE_CHILD_GEOGRAPHY_TYPE9, NULL as INCLUDE_CHILD_GEOGRAPHY_TYPE10, rec.hierarchy_node_id as hierarchy_node_id, rec.last_update_date as last_update_date FROM hz_hierarchy_nodes rec, hz_geographies c WHERE rec.parent_id = c.geography_id AND rec.top_parent_flag = 'Y' AND rec.hierarchy_type = 'TM' AND rec.level_number = 0 AND rec.parent_id = rec.child_id AND rec.child_table_name = 'HZ_GEOGRAPHIES' AND rec.parent_table_name = 'HZ_GEOGRAPHIES' AND trunc(sysdate) between trunc(rec.effective_start_date) and trunc(rec.effective_end_date) and NVL(rec.status,'A') = 'A'