MSC_LOCATIONS_TCA_V

Details

• Schema: FUSION

• Object owner: MSC

• Object type: VIEW

Columns

Name
LOCATION_CODE LOCATION_ID REGION_ID DESCRIPTION ADDRESS1 ADDRESS2 ADDRESS3 ADDRESS4 FLOOR_NUMBER BUILDING CITY STATE PROVINCE COUNTY POSTAL_CODE COUNTRY_CODE ADDRESS_EFFECTIVE_DATE ADDRESS_EXPIRATION_DATE LOCATION_TYPE

Query

SQL_Statement

SELECT to_char(loc.LOCATION_ID) LOCATION_CODE, loc.LOCATION_ID LOCATION_ID, geo.GEOGRAPHY_ID REGION_ID, 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 COUNTRY_CODE, loc.ADDRESS_EFFECTIVE_DATE, loc.ADDRESS_EXPIRATION_DATE, 'R' LOCATION_TYPE 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)) geo WHERE loc.location_id = geo.LOCATION_ID(+) AND NVL(rank,1) = 1