VIEW: APPS.MST_REGION_LOCATIONS_V

Source

View Text - Preformatted

SELECT reg_loc.location_id, reg_loc.region_id, reg.region_type from wsh_region_locations reg_loc, wsh_regions reg WHERE reg_loc.region_id = reg.region_id AND reg.region_type not in ( 10, 11) AND nvl(reg_loc.EXCEPTION_TYPE ,'N') = 'N' UNION select wsh_loc.wsh_location_id, reg.region_id, 11 from wsh_regions reg, wsh_zone_regions zreg, wsh_locations wsh_loc , wsh_regions country_reg, wsh_regions_tl country_reg_tl WHERE reg.region_id = zreg.parent_region_id AND reg.region_type = 11 AND zreg.ZONE_FLAG = 'Y' AND wsh_loc.POSTAL_CODE >= zreg.POSTAL_CODE_FROM AND wsh_loc.POSTAL_CODE <= zreg.POSTAL_CODE_TO AND zreg.region_id = country_reg.region_id AND country_reg.region_id = country_reg_tl.region_id AND country_reg_tl.LANGUAGE = userenv('LANG') AND ( country_reg.country_code = wsh_loc.COUNTRY OR country_reg_tl.COUNTRY = wsh_loc.COUNTRY) UNION select reg_loc.location_id, reg_loc.region_id, 10 from wsh_region_locations reg_loc, wsh_regions reg, wsh_zone_regions zreg where zreg.zone_flag = 'Y' AND reg.region_type = 10 AND reg.region_id = zreg.parent_region_id AND reg_loc.region_id = zreg.region_id AND nvl(reg_loc.EXCEPTION_TYPE ,'N') = 'N'

View Text - HTML Formatted

SELECT REG_LOC.LOCATION_ID
, REG_LOC.REGION_ID
, REG.REGION_TYPE
FROM WSH_REGION_LOCATIONS REG_LOC
, WSH_REGIONS REG
WHERE REG_LOC.REGION_ID = REG.REGION_ID
AND REG.REGION_TYPE NOT IN ( 10
, 11)
AND NVL(REG_LOC.EXCEPTION_TYPE
, 'N') = 'N' UNION SELECT WSH_LOC.WSH_LOCATION_ID
, REG.REGION_ID
, 11
FROM WSH_REGIONS REG
, WSH_ZONE_REGIONS ZREG
, WSH_LOCATIONS WSH_LOC
, WSH_REGIONS COUNTRY_REG
, WSH_REGIONS_TL COUNTRY_REG_TL
WHERE REG.REGION_ID = ZREG.PARENT_REGION_ID
AND REG.REGION_TYPE = 11
AND ZREG.ZONE_FLAG = 'Y'
AND WSH_LOC.POSTAL_CODE >= ZREG.POSTAL_CODE_FROM
AND WSH_LOC.POSTAL_CODE <= ZREG.POSTAL_CODE_TO
AND ZREG.REGION_ID = COUNTRY_REG.REGION_ID
AND COUNTRY_REG.REGION_ID = COUNTRY_REG_TL.REGION_ID
AND COUNTRY_REG_TL.LANGUAGE = USERENV('LANG')
AND ( COUNTRY_REG.COUNTRY_CODE = WSH_LOC.COUNTRY OR COUNTRY_REG_TL.COUNTRY = WSH_LOC.COUNTRY) UNION SELECT REG_LOC.LOCATION_ID
, REG_LOC.REGION_ID
, 10
FROM WSH_REGION_LOCATIONS REG_LOC
, WSH_REGIONS REG
, WSH_ZONE_REGIONS ZREG
WHERE ZREG.ZONE_FLAG = 'Y'
AND REG.REGION_TYPE = 10
AND REG.REGION_ID = ZREG.PARENT_REGION_ID
AND REG_LOC.REGION_ID = ZREG.REGION_ID
AND NVL(REG_LOC.EXCEPTION_TYPE
, 'N') = 'N'