eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.HRI_CS_GEO_LOCHR_V

Source

View Text - Preformatted


SELECT NVL(area.parent_territory_code,'NA_EDW') area_code , NVL(cnty.country_code,'NA_EDW') country_code , NVL(cnty.region_code,'NA_EDW') region_code , DECODE(cnty.city_code, 'NA_EDW', 'NA_EDW', NVL(area.parent_territory_code,'NA_EDW') || '+' || NVL(cnty.country_code,'NA_EDW') || '+' || NVL(cnty.region_code,'NA_EDW') || '+' || NVL(cnty.city_code,'NA_EDW')) city_cid , cnty.location_id location_id , NVL(area.parent_territory_code,'NA_EDW') geo_area_id , DECODE (cnty.country_code, 'NA_EDW', 'NA_EDW', NVL2(area.parent_territory_code ,area.parent_territory_code ||'+'|| cnty.country_code ,'NA_EDW')) geo_country_id , DECODE(cnty.region_code, 'NA_EDW', 'NA_EDW', area.parent_territory_code||'+'|| cnty.country_code||'+'|| cnty.region_code) geo_region_id , NVL(cnty.city_code,'NA_EDW') city_src_town_or_city_txt , hr_general.start_of_time effective_start_date , hr_general.end_of_time effective_end_date , cnty.business_group_id business_group_id , cnty.last_change_date last_change_date FROM bis_territory_hierarchies area , (SELECT DECODE(regn.region_code, 'NA_EDW', NVL(regn.loc_country_code,'NA_EDW'), NVL2(thc.child_territory_code, NVL(thc.PARENT_TERRITORY_CODE ,'NA_EDW'), 'NA_EDW')) country_code , regn.region_code region_code , regn.location_id location_id , regn.business_group_id business_group_id , regn.last_loc_change_date last_change_date , thc.child_territory_code btc_region_code , thc.parent_territory_code btc_country_code , regn.loc_country_code loc_country_code , regn.city_code city_code FROM bis_territory_hierarchies thc , (SELECT loc.country loc_country_code , DECODE(hri_oltp_view_geography.get_region_code(loc.location_id), ' ', 'NA_EDW', NVL(hri_oltp_view_geography.get_region_code(loc.location_id) ,'NA_EDW')) region_code , NVL(loc.town_or_city, 'NA_EDW') city_code , loc.location_id location_id , nvl(loc.business_group_id,-1) business_group_id , greatest(loc.last_update_date) last_loc_change_date FROM hr_locations_all loc ) regn WHERE regn.region_code = thc.child_territory_code (+) AND thc.parent_territory_type (+) = 'COUNTRY' ) cnty WHERE cnty.country_code = area.child_territory_code (+) AND area.parent_territory_type(+) = 'AREA' UNION ALL SELECT 'NA_EDW' area_code , 'NA_EDW' country_code , 'NA_EDW' region_code , 'NA_EDW' city_cid , -1 location_id , 'NA_EDW' geo_area_id , 'NA_EDW' geo_country_id , 'NA_EDW' geo_region_id , 'NA_EDW' city_src_town_or_city_name_txt , hr_general.start_of_time effective_start_date , hr_general.end_of_time effective_end_date , -1 business_group_id , hr_general.start_of_time last_change_date FROM dual WITH READ ONLY

View Text - HTML Formatted

SELECT NVL(AREA.PARENT_TERRITORY_CODE
, 'NA_EDW') AREA_CODE
, NVL(CNTY.COUNTRY_CODE
, 'NA_EDW') COUNTRY_CODE
, NVL(CNTY.REGION_CODE
, 'NA_EDW') REGION_CODE
, DECODE(CNTY.CITY_CODE
, 'NA_EDW'
, 'NA_EDW'
, NVL(AREA.PARENT_TERRITORY_CODE
, 'NA_EDW') || '+' || NVL(CNTY.COUNTRY_CODE
, 'NA_EDW') || '+' || NVL(CNTY.REGION_CODE
, 'NA_EDW') || '+' || NVL(CNTY.CITY_CODE
, 'NA_EDW')) CITY_CID
, CNTY.LOCATION_ID LOCATION_ID
, NVL(AREA.PARENT_TERRITORY_CODE
, 'NA_EDW') GEO_AREA_ID
, DECODE (CNTY.COUNTRY_CODE
, 'NA_EDW'
, 'NA_EDW'
, NVL2(AREA.PARENT_TERRITORY_CODE
, AREA.PARENT_TERRITORY_CODE ||'+'|| CNTY.COUNTRY_CODE
, 'NA_EDW')) GEO_COUNTRY_ID
, DECODE(CNTY.REGION_CODE
, 'NA_EDW'
, 'NA_EDW'
, AREA.PARENT_TERRITORY_CODE||'+'|| CNTY.COUNTRY_CODE||'+'|| CNTY.REGION_CODE) GEO_REGION_ID
, NVL(CNTY.CITY_CODE
, 'NA_EDW') CITY_SRC_TOWN_OR_CITY_TXT
, HR_GENERAL.START_OF_TIME EFFECTIVE_START_DATE
, HR_GENERAL.END_OF_TIME EFFECTIVE_END_DATE
, CNTY.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, CNTY.LAST_CHANGE_DATE LAST_CHANGE_DATE
FROM BIS_TERRITORY_HIERARCHIES AREA
, (SELECT DECODE(REGN.REGION_CODE
, 'NA_EDW'
, NVL(REGN.LOC_COUNTRY_CODE
, 'NA_EDW')
, NVL2(THC.CHILD_TERRITORY_CODE
, NVL(THC.PARENT_TERRITORY_CODE
, 'NA_EDW')
, 'NA_EDW')) COUNTRY_CODE
, REGN.REGION_CODE REGION_CODE
, REGN.LOCATION_ID LOCATION_ID
, REGN.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, REGN.LAST_LOC_CHANGE_DATE LAST_CHANGE_DATE
, THC.CHILD_TERRITORY_CODE BTC_REGION_CODE
, THC.PARENT_TERRITORY_CODE BTC_COUNTRY_CODE
, REGN.LOC_COUNTRY_CODE LOC_COUNTRY_CODE
, REGN.CITY_CODE CITY_CODE
FROM BIS_TERRITORY_HIERARCHIES THC
, (SELECT LOC.COUNTRY LOC_COUNTRY_CODE
, DECODE(HRI_OLTP_VIEW_GEOGRAPHY.GET_REGION_CODE(LOC.LOCATION_ID)
, ' '
, 'NA_EDW'
, NVL(HRI_OLTP_VIEW_GEOGRAPHY.GET_REGION_CODE(LOC.LOCATION_ID)
, 'NA_EDW')) REGION_CODE
, NVL(LOC.TOWN_OR_CITY
, 'NA_EDW') CITY_CODE
, LOC.LOCATION_ID LOCATION_ID
, NVL(LOC.BUSINESS_GROUP_ID
, -1) BUSINESS_GROUP_ID
, GREATEST(LOC.LAST_UPDATE_DATE) LAST_LOC_CHANGE_DATE
FROM HR_LOCATIONS_ALL LOC ) REGN
WHERE REGN.REGION_CODE = THC.CHILD_TERRITORY_CODE (+)
AND THC.PARENT_TERRITORY_TYPE (+) = 'COUNTRY' ) CNTY
WHERE CNTY.COUNTRY_CODE = AREA.CHILD_TERRITORY_CODE (+)
AND AREA.PARENT_TERRITORY_TYPE(+) = 'AREA' UNION ALL SELECT 'NA_EDW' AREA_CODE
, 'NA_EDW' COUNTRY_CODE
, 'NA_EDW' REGION_CODE
, 'NA_EDW' CITY_CID
, -1 LOCATION_ID
, 'NA_EDW' GEO_AREA_ID
, 'NA_EDW' GEO_COUNTRY_ID
, 'NA_EDW' GEO_REGION_ID
, 'NA_EDW' CITY_SRC_TOWN_OR_CITY_NAME_TXT
, HR_GENERAL.START_OF_TIME EFFECTIVE_START_DATE
, HR_GENERAL.END_OF_TIME EFFECTIVE_END_DATE
, -1 BUSINESS_GROUP_ID
, HR_GENERAL.START_OF_TIME LAST_CHANGE_DATE
FROM DUAL WITH READ ONLY