MSC_AP_INTERNAL_LOCATIONS_V

Details

• Schema: FUSION

• Object owner: MSC

• Object type: VIEW

Columns

Name

LOCATION_CODE SR_LOCATION_CODE LOCATION_NAME 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 LOCATION_TYPE

Query

SQL_Statement

SELECT to_char(LOC.LOCATION_ID) LOCATION_CODE, LOC.INTERNAL_LOCATION_CODE SR_LOCATION_CODE, LOC.LOCATION_NAME, LOC.LOCATION_ID SR_LOCATION_ID, geo.geography_name region_name, NVL(geo.country_code, SUBSTR(LOC.COUNTRY, 0, 2)) 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.ADDRESS_LINE_1 ADDRESS1, LOC.ADDRESS_LINE_2 ADDRESS2, LOC.ADDRESS_LINE_3 ADDRESS3, LOC.ADDRESS_LINE_4 ADDRESS4, LOC.FLOOR_NUMBER, LOC.BUILDING, LOC.TOWN_OR_CITY CITY, NULL STATE, NULL PROVINCE, NULL COUNTY, LOC.POSTAL_CODE, LOC.COUNTRY, LOC.EFFECTIVE_START_DATE ADDRESS_EFFECTIVE_DATE, LOC.EFFECTIVE_END_DATE ADDRESS_EXPIRATION_DATE, 'I' LOCATION_TYPE FROM HR_LOCATIONS_ALL_F_VL 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 = 'PER_ADDRESSES_F' 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.address_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 (TRUNC(sysdate) BETWEEN loc.effective_start_date AND loc.effective_end_date OR loc.effective_end_date IS NULL )