HZ_EXP_GEOGRAPHIES

Details

Schema: FUSION
Object owner: HZ
Object type: VIEW

Columns

Name
RECORD_TYPE_CODE PRIMARY_GEOGRAPHY_NAME COUNTRY_CODE GEOGRAPHY_ID LEVEL_NUMBER SOURCE_ID PARENT_SOURCE_ID PARENT_GEOGRAPHY_ID LANGUAGE_CODE PRIMARY_GEOGRAPHY_CODE PRIMARY_GEOGRAPHY_CODE_TYPE IDENTIFIER_TYPE IDENTIFIER_SUB_TYPE IDENTIFIER_VALUE START_DATE END_DATE GEOGRAPHY_TYPE GEOGRAPHY_CODE CREATED_BY_MODULE GEOGRAPHY_ELEMENT1 GEOGRAPHY_ELEMENT2 GEOGRAPHY_ELEMENT3 GEOGRAPHY_ELEMENT4 GEOGRAPHY_ELEMENT5 GEOGRAPHY_ELEMENT6 GEOGRAPHY_ELEMENT7 GEOGRAPHY_ELEMENT8 GEOGRAPHY_ELEMENT9 GEOGRAPHY_ELEMENT10 LAST_UPDATED_BY CREATION_DATE CREATED_BY LAST_UPDATE_DATE REQUEST_ID JOB_DEFINITION_NAME JOB_DEFINITION_PACKAGE GEOCODE_FLAG HIDDEN_FLAG SEED_DATA_SOURCE GEO_DATA_PROVIDER GEOGRAPHY_IDENTIFIER_ID PROVIDER_GEO_ID PARENT_PROVIDER_GEO_ID

Name

RECORD_TYPE_CODE

PRIMARY_GEOGRAPHY_NAME

COUNTRY_CODE

GEOGRAPHY_ID

LEVEL_NUMBER

SOURCE_ID

PARENT_SOURCE_ID

PARENT_GEOGRAPHY_ID

LANGUAGE_CODE

PRIMARY_GEOGRAPHY_CODE

PRIMARY_GEOGRAPHY_CODE_TYPE

IDENTIFIER_TYPE

IDENTIFIER_SUB_TYPE

IDENTIFIER_VALUE

START_DATE

END_DATE

GEOGRAPHY_TYPE

GEOGRAPHY_CODE

CREATED_BY_MODULE

GEOGRAPHY_ELEMENT1

GEOGRAPHY_ELEMENT2

GEOGRAPHY_ELEMENT3

GEOGRAPHY_ELEMENT4

GEOGRAPHY_ELEMENT5

GEOGRAPHY_ELEMENT6

GEOGRAPHY_ELEMENT7

GEOGRAPHY_ELEMENT8

GEOGRAPHY_ELEMENT9

GEOGRAPHY_ELEMENT10

LAST_UPDATED_BY

CREATION_DATE

CREATED_BY

LAST_UPDATE_DATE

REQUEST_ID

JOB_DEFINITION_NAME

JOB_DEFINITION_PACKAGE

GEOCODE_FLAG

HIDDEN_FLAG

SEED_DATA_SOURCE

GEO_DATA_PROVIDER

GEOGRAPHY_IDENTIFIER_ID

PROVIDER_GEO_ID

PARENT_PROVIDER_GEO_ID

Query

SQL_Statement
SELECT geomaster.record_type_code,geomaster.primary_geography_name,geomaster.country_code,geomaster.geography_id,geomaster.level_number,geomaster.source_id,geomaster.parent_source_id,geomaster.parent_geography_id,geomaster.language_code,geomaster.primary_geography_code,geomaster.primary_geography_code_type,geomaster.identifier_type,geomaster.identifier_sub_type,geomaster.identifier_value,geomaster.start_date,geomaster.end_date,geomaster.geography_type,geomaster.geography_code,geomaster.created_by_module,geomaster.geography_element1,geomaster.geography_element2,geomaster.geography_element3,geomaster.geography_element4,geomaster.geography_element5,geomaster.geography_element6,geomaster.geography_element7,geomaster.geography_element8,geomaster.geography_element9,geomaster.geography_element10,geomaster.last_updated_by,geomaster.creation_date,geomaster.created_by,geomaster.last_update_date,geomaster.request_id,geomaster.job_definition_name,geomaster.job_definition_package,geomaster.geocode_flag,geomaster.hidden_flag,geomaster.seed_data_source,geomaster.geo_data_provider,geomaster.geography_identifier_id,geomaster.provider_geo_id,geomaster.parent_provider_geo_id FROM ( SELECT CASE WHEN geo.geography_type='COUNTRY' THEN 0 ELSE 1 END record_type_code, geo.geography_name primary_geography_name, geo.country_code, geo.geography_id, nvl((SELECT substr(geography_element_column, -1) FROM hz_geo_structure_levels geo_struct WHERE geo_struct.geography_type=geo.geography_type AND geo_struct.country_code=geo.country_code AND ROWNUM=1),1) level_number, geo.geography_id source_id, CASE WHEN geo.geography_id=geography_element1_id THEN NULL WHEN geo.geography_id=geography_element2_id THEN geography_element1_id WHEN geo.geography_id=geography_element3_id THEN geography_element2_id WHEN geo.geography_id=geography_element4_id THEN geography_element3_id WHEN geo.geography_id=geography_element5_id THEN geography_element4_id WHEN geo.geography_id=geography_element6_id THEN geography_element5_id WHEN geo.geography_id=geography_element7_id THEN geography_element6_id WHEN geo.geography_id=geography_element8_id THEN geography_element7_id WHEN geo.geography_id=geography_element9_id THEN geography_element8_id END parent_source_id, CASE WHEN geo.geography_id=geography_element1_id THEN NULL WHEN geo.geography_id=geography_element2_id THEN geography_element1_id WHEN geo.geography_id=geography_element3_id THEN geography_element2_id WHEN geo.geography_id=geography_element4_id THEN geography_element3_id WHEN geo.geography_id=geography_element5_id THEN geography_element4_id WHEN geo.geography_id=geography_element6_id THEN geography_element5_id WHEN geo.geography_id=geography_element7_id THEN geography_element6_id WHEN geo.geography_id=geography_element8_id THEN geography_element7_id WHEN geo.geography_id=geography_element9_id THEN geography_element8_id END parent_geography_id, primaryNameIden.language_code, primaryCodeIden.identifier_value primary_geography_code, primaryCodeIden.identifier_subtype primary_geography_code_type, NULL identifier_type, NULL identifier_sub_type, NULL identifier_value, geo.start_date, geo.end_date, geo.geography_type, geo.geography_code, geo.created_by_module, geo.geography_element1, geo.geography_element2, geo.geography_element3, geo.geography_element4, geo.geography_element5, geo.geography_element6, geo.geography_element7, geo.geography_element8, geo.geography_element9, geo.geography_element10, geo.last_updated_by, geo.creation_date, geo.created_by, geo.last_update_date, geo.request_id, geo.job_definition_name, geo.job_definition_package, geo.geocode_flag, geo.hidden_flag, geo.seed_data_source, nvl(idenprovdtls.provider_code, primarynameiden.geo_data_provider) geo_data_provider, primaryNameIden.geography_identifier_id,idenprovdtls.provider_geo_id,idenprovdtls.parent_provider_geo_id FROM hz_geographies geo, hz_geography_identifiers primaryNameIden,hz_geo_iden_prov_dtls idenprovdtls, (SELECT geography_id, identifier_value, identifier_subtype, geography_identifier_id FROM hz_geography_identifiers WHERE primary_flag = 'Y' AND identifier_type = 'CODE') primaryCodeIden WHERE geo.geography_id = primaryCodeIden.geography_id (+) AND primarynameiden.geography_identifier_id = idenprovdtls.geography_identifier_id (+) AND geo.geography_use = 'MASTER_REF' AND trunc(sysdate) BETWEEN geo.start_date AND geo.end_date AND geo.geography_id = primaryNameIden.geography_id AND geo.country_code = primaryNameIden.country_code AND primaryNameIden.identifier_type = 'NAME' AND primaryNameIden.primary_flag = 'Y' UNION ALL SELECT 2 record_type_code, geo.geography_name primary_geography_name, geo.country_code, geo.geography_id, nvl((SELECT substr(geography_element_column, -1) FROM hz_geo_structure_levels geo_struct WHERE geo_struct.geography_type=geo.geography_type AND geo_struct.country_code=geo.country_code AND ROWNUM=1),1) level_number, geo.geography_id source_id, CASE WHEN geo.geography_id=geography_element1_id THEN NULL WHEN geo.geography_id=geography_element2_id THEN geography_element1_id WHEN geo.geography_id=geography_element3_id THEN geography_element2_id WHEN geo.geography_id=geography_element4_id THEN geography_element3_id WHEN geo.geography_id=geography_element5_id THEN geography_element4_id WHEN geo.geography_id=geography_element6_id THEN geography_element5_id WHEN geo.geography_id=geography_element7_id THEN geography_element6_id WHEN geo.geography_id=geography_element8_id THEN geography_element7_id WHEN geo.geography_id=geography_element9_id THEN geography_element8_id END parent_source_id, CASE WHEN geo.geography_id=geography_element1_id THEN NULL WHEN geo.geography_id=geography_element2_id THEN geography_element1_id WHEN geo.geography_id=geography_element3_id THEN geography_element2_id WHEN geo.geography_id=geography_element4_id THEN geography_element3_id WHEN geo.geography_id=geography_element5_id THEN geography_element4_id WHEN geo.geography_id=geography_element6_id THEN geography_element5_id WHEN geo.geography_id=geography_element7_id THEN geography_element6_id WHEN geo.geography_id=geography_element8_id THEN geography_element7_id WHEN geo.geography_id=geography_element9_id THEN geography_element8_id END parent_geography_id, language_code, NULL primary_geography_code, NULL primary_geography_code_type, altiden.identifier_type identifier_type, altiden.identifier_subtype identifier_sub_type, altiden.identifier_value identifier_value, geo.start_date, geo.end_date, geo.geography_type, geo.geography_code, geo.created_by_module, geo.geography_element1, geo.geography_element2, geo.geography_element3, geo.geography_element4, geo.geography_element5, geo.geography_element6, geo.geography_element7, geo.geography_element8, geo.geography_element9, geo.geography_element10, geo.last_updated_by, geo.creation_date, geo.created_by, geo.last_update_date, geo.request_id, geo.job_definition_name, geo.job_definition_package, geo.geocode_flag, geo.hidden_flag, geo.seed_data_source,nvl(idenprovdtls.provider_code, altiden.geo_data_provider)geo_data_provider, altiden.geography_identifier_id,idenprovdtls.provider_geo_id,idenprovdtls.parent_provider_geo_id FROM hz_geographies geo,hz_geo_iden_prov_dtls idenprovdtls, (SELECT geography_id, language_code , identifier_value, identifier_subtype, identifier_type, geo_data_provider, geography_identifier_id FROM hz_geography_identifiers WHERE primary_flag = 'N') altiden WHERE geo.geography_id = altiden.geography_id AND altiden.geography_identifier_id = idenprovdtls.geography_identifier_id (+) AND geo.geography_use = 'MASTER_REF' AND trunc(sysdate) BETWEEN geo.start_date AND geo.end_date ) geomaster

SQL_Statement

SELECT geomaster.record_type_code,geomaster.primary_geography_name,geomaster.country_code,geomaster.geography_id,geomaster.level_number,geomaster.source_id,geomaster.parent_source_id,geomaster.parent_geography_id,geomaster.language_code,geomaster.primary_geography_code,geomaster.primary_geography_code_type,geomaster.identifier_type,geomaster.identifier_sub_type,geomaster.identifier_value,geomaster.start_date,geomaster.end_date,geomaster.geography_type,geomaster.geography_code,geomaster.created_by_module,geomaster.geography_element1,geomaster.geography_element2,geomaster.geography_element3,geomaster.geography_element4,geomaster.geography_element5,geomaster.geography_element6,geomaster.geography_element7,geomaster.geography_element8,geomaster.geography_element9,geomaster.geography_element10,geomaster.last_updated_by,geomaster.creation_date,geomaster.created_by,geomaster.last_update_date,geomaster.request_id,geomaster.job_definition_name,geomaster.job_definition_package,geomaster.geocode_flag,geomaster.hidden_flag,geomaster.seed_data_source,geomaster.geo_data_provider,geomaster.geography_identifier_id,geomaster.provider_geo_id,geomaster.parent_provider_geo_id FROM (

SELECT CASE WHEN geo.geography_type='COUNTRY' THEN 0 ELSE 1 END record_type_code, geo.geography_name primary_geography_name, geo.country_code, geo.geography_id,

nvl((SELECT substr(geography_element_column, -1) FROM hz_geo_structure_levels geo_struct WHERE geo_struct.geography_type=geo.geography_type AND geo_struct.country_code=geo.country_code AND ROWNUM=1),1) level_number,

geo.geography_id source_id,

CASE WHEN geo.geography_id=geography_element1_id THEN NULL

WHEN geo.geography_id=geography_element2_id THEN geography_element1_id

WHEN geo.geography_id=geography_element3_id THEN geography_element2_id

WHEN geo.geography_id=geography_element4_id THEN geography_element3_id

WHEN geo.geography_id=geography_element5_id THEN geography_element4_id

WHEN geo.geography_id=geography_element6_id THEN geography_element5_id

WHEN geo.geography_id=geography_element7_id THEN geography_element6_id

WHEN geo.geography_id=geography_element8_id THEN geography_element7_id

WHEN geo.geography_id=geography_element9_id THEN geography_element8_id END parent_source_id,

CASE WHEN geo.geography_id=geography_element1_id THEN NULL

WHEN geo.geography_id=geography_element2_id THEN geography_element1_id

WHEN geo.geography_id=geography_element3_id THEN geography_element2_id

WHEN geo.geography_id=geography_element4_id THEN geography_element3_id

WHEN geo.geography_id=geography_element5_id THEN geography_element4_id

WHEN geo.geography_id=geography_element6_id THEN geography_element5_id

WHEN geo.geography_id=geography_element7_id THEN geography_element6_id

WHEN geo.geography_id=geography_element8_id THEN geography_element7_id

WHEN geo.geography_id=geography_element9_id THEN geography_element8_id END parent_geography_id,

primaryNameIden.language_code, primaryCodeIden.identifier_value primary_geography_code, primaryCodeIden.identifier_subtype primary_geography_code_type,

NULL identifier_type, NULL identifier_sub_type, NULL identifier_value, geo.start_date, geo.end_date,

geo.geography_type, geo.geography_code, geo.created_by_module, geo.geography_element1, geo.geography_element2, geo.geography_element3, geo.geography_element4,

geo.geography_element5, geo.geography_element6, geo.geography_element7, geo.geography_element8, geo.geography_element9, geo.geography_element10, geo.last_updated_by, geo.creation_date,

geo.created_by, geo.last_update_date, geo.request_id, geo.job_definition_name, geo.job_definition_package, geo.geocode_flag, geo.hidden_flag, geo.seed_data_source,

nvl(idenprovdtls.provider_code, primarynameiden.geo_data_provider) geo_data_provider, primaryNameIden.geography_identifier_id,idenprovdtls.provider_geo_id,idenprovdtls.parent_provider_geo_id

FROM hz_geographies geo, hz_geography_identifiers primaryNameIden,hz_geo_iden_prov_dtls idenprovdtls,

(SELECT geography_id, identifier_value, identifier_subtype, geography_identifier_id FROM hz_geography_identifiers WHERE primary_flag = 'Y' AND identifier_type = 'CODE') primaryCodeIden

WHERE geo.geography_id = primaryCodeIden.geography_id (+) AND primarynameiden.geography_identifier_id = idenprovdtls.geography_identifier_id (+) AND geo.geography_use = 'MASTER_REF' AND trunc(sysdate) BETWEEN geo.start_date AND geo.end_date

AND geo.geography_id = primaryNameIden.geography_id AND geo.country_code = primaryNameIden.country_code AND primaryNameIden.identifier_type = 'NAME' AND primaryNameIden.primary_flag = 'Y'

UNION ALL

SELECT 2 record_type_code, geo.geography_name primary_geography_name, geo.country_code, geo.geography_id,