MSC_AP_REGION_LOCATIONS_V

Details

• Schema: FUSION

• Object owner: MSC

• Object type: VIEW

Columns

Name

LOCATION_CODE SR_LOCATION_ID REGION_NAME COUNTRY_CODE PARENT_REGION_NAME_1 PARENT_REGION_NAME_2 PARENT_REGION_NAME_3 PARENT_REGION_NAME_4 PARENT_REGION_NAME_5 PARENT_REGION_NAME_6 PARENT_REGION_NAME_7 PARENT_REGION_NAME_8 DESCRIPTION ADDRESS1 ADDRESS2 ADDRESS3 ADDRESS4 FLOOR_NUMBER BUILDING CITY STATE PROVINCE COUNTY POSTAL_CODE COUNTRY ADDRESS_EFFECTIVE_DATE ADDRESS_EXPIRATION_DATE DELETED_FLAG LAST_UPDATE_DATE LOCATION_TYPE TIMEZONE_CODE

Query

SQL_Statement

SELECT to_char(loc.LOCATION_ID) LOCATION_CODE, loc.LOCATION_ID SR_LOCATION_ID, geo.geography_name REGION_NAME, NVL(geo.country_code, loc.COUNTRY) COUNTRY_CODE, geo.geography_element2 PARENT_REGION_NAME_1 , geo.geography_element3 PARENT_REGION_NAME_2 , geo.geography_element4 PARENT_REGION_NAME_3 , geo.geography_element5 PARENT_REGION_NAME_4 , geo.geography_element6 PARENT_REGION_NAME_5 , geo.geography_element7 PARENT_REGION_NAME_6 , geo.geography_element8 PARENT_REGION_NAME_7 , geo.geography_element9 PARENT_REGION_NAME_8 , loc.DESCRIPTION, loc.ADDRESS1, loc.ADDRESS2, loc.ADDRESS3, loc.ADDRESS4, loc.FLOOR_NUMBER, loc.BUILDING, loc.CITY, loc.STATE, loc.PROVINCE, loc.COUNTY, loc.POSTAL_CODE, loc.COUNTRY, loc.ADDRESS_EFFECTIVE_DATE, loc.ADDRESS_EXPIRATION_DATE, (CASE WHEN ADDRESS_EXPIRATION_DATE < SYSDATE OR ADDRESS_EFFECTIVE_DATE > SYSDATE THEN 1 ELSE 2 END) DELETED_FLAG, loc.LAST_UPDATE_DATE, 'R' LOCATION_TYPE, loc.TIMEZONE_CODE FROM hz_locations loc, (SELECT LOCATION_ID, GEOGRAPHY_ID, loc_effective_end_date, loc_effective_start_date, ROW_NUMBER() OVER (PARTITION BY LOCATION_ID ORDER BY level_number desc) rank FROM HZ_GEO_NAME_REFERENCES WHERE location_table_name = 'HZ_LOCATIONS' AND (loc_effective_end_date IS NULL OR (TRUNC(sysdate) BETWEEN loc_effective_start_date AND loc_effective_end_date)) ) georef, HZ_GEOGRAPHIES geo WHERE loc.location_id = georef.LOCATION_ID(+) AND georef.GEOGRAPHY_ID = geo.GEOGRAPHY_ID(+) AND NVL(georef.rank,1) = 1 AND NVL(geo.country_code, loc.COUNTRY) IS NOT NULL AND NOT EXISTS (SELECT 1 FROM HZ_ORIG_SYS_REFERENCES href where href.owner_table_name = 'HZ_LOCATIONS' AND href.owner_table_id = loc.location_id AND href.orig_system IN ('FUSION_HCM'))