HZ_EXP_ZONE_GEOGRAPHIES_V

Details

• Schema: FUSION

• Object owner: HZ

• Object type: VIEW

Columns

Name

PRIMARY_RECORD_NUM ZONE_GEOGRAPHY_ID ZONE_NAME ZONE_CODE ZONE_TYPE ZONE_GEOGRAPHY_USE ZONE_COUNTRY_CODE ZONE_TIMEZONE_CODE ZONE_GEOGRAPHY_NUMBER MASTER_GEOGRAPHY_ID MASTER_GEO_NAME MASTER_GEO_TYPE MASTER_GEO_USE MASTER_GEO_CODE MASTER_GEO_COUNTRY_CODE MASTER_GEO_ELEMENT1 MASTER_GEO_ELEMENT2 MASTER_GEO_ELEMENT3 MASTER_GEO_ELEMENT4 MASTER_GEO_ELEMENT5 MASTER_GEO_ELEMENT6 MASTER_GEO_ELEMENT7 MASTER_GEO_ELEMENT8 MASTER_GEO_ELEMENT9 MASTER_GEO_ELEMENT10 ZONE_RELATIONSHIP_ID FROM_POSTAL_CODE TO_POSTAL_CODE RELATIONSHIP_START_DATE RELATIONSHIP_END_DATE GEOGRAPHY_RANGE_ID ZONE_START_DATE ZONE_END_DATE POSTAL_CODE_START_DATE POSTAL_CODE_END_DATE CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN

Query

SQL_Statement

SELECT geo_zones.geography_id ||'-'||master_geo.geography_id||'-'||nvl(geo_range.geography_range_id,-1) as primary_record_num, geo_zones.geography_id AS zone_geography_id, geo_zones.geography_name AS zone_name, geo_zones.geography_code as zone_code, geo_zones.geography_type AS zone_type, geo_zones.geography_use as zone_geography_use, geo_zones.country_code as zone_country_code, geo_zones.timezone_code as zone_timezone_code, geo_zones.geography_number as zone_geography_number, master_geo.geography_id AS master_geography_id, master_geo.geography_name AS master_geo_name, master_geo.geography_type AS master_geo_type, master_geo.geography_use as master_geo_use, master_geo.geography_code as master_geo_code, master_geo.country_code as master_geo_country_code, master_geo.geography_element1 as master_geo_element1, master_geo.geography_element2 as master_geo_element2, master_geo.geography_element3 as master_geo_element3, master_geo.geography_element4 as master_geo_element4, master_geo.geography_element5 as master_geo_element5, master_geo.geography_element6 as master_geo_element6, master_geo.geography_element7 as master_geo_element7, master_geo.geography_element8 as master_geo_element8, master_geo.geography_element9 as master_geo_element9, master_geo.geography_element10 as master_geo_element10, rel.relationship_id AS zone_relationship_id, geo_range.geography_from as from_postal_code, geo_range.geography_to as to_postal_code, rel.start_date as relationship_start_date, rel.end_date as relationship_end_date, geo_range.geography_range_id as geography_range_id, geo_zones.start_date as zone_start_date, geo_zones.end_date as zone_end_date, geo_range.start_date as postal_code_start_date, geo_range.end_date as postal_code_end_date, geo_zones.creation_date as creation_date, geo_zones.created_by as created_by, geo_zones.last_update_date as last_update_date, geo_zones.last_updated_by as last_updated_by, geo_zones.last_update_login as last_update_login FROM hz_relationships rel, hz_geographies geo_zones, hz_geographies master_geo, hz_geography_ranges geo_range WHERE geo_zones.geography_id = rel.subject_id AND master_geo.geography_id = rel.object_id AND rel.subject_table_name = rel.object_table_name AND rel.subject_table_name = 'HZ_GEOGRAPHIES' AND rel.object_table_name = 'HZ_GEOGRAPHIES' AND geo_zones.geography_use =rel.relationship_type AND rel.relationship_type in (select lookup_code from hz_lookups geo_lookup where geo_lookup.lookup_type ='ORA_HZ_GEO_USAGE' AND geo_lookup.lookup_code NOT IN ('TM','MASTER_REF') AND geo_lookup.enabled_flag ='Y') AND rel.STATUS = 'A' AND rel.relationship_code = 'PARENT_OF' AND rel.directional_flag = 'F' AND geo_zones.geography_id = geo_range.geography_id(+) AND master_geo.geography_id = geo_range.master_ref_geography_id(+)