VIEW: APPS.MSC_REGION_LOCATIONS_V

Source

View Text - Preformatted

SELECT REGION_ID , LOCATION_ID , LOCATION_SOURCE , (10 * (10 - region_type)) REGION_TYPE , PARENT_REGION_FLAG , SR_INSTANCE_ID, 2 partner_type FROM MSC_REGION_LOCATIONS WHERE location_source = 'HZ' and region_id is not null UNION select /*+ ORDERED */ a.REGION_ID, c.LOCATION_ID, c.LOCATION_SOURCE, ((10 * (10 - a.zone_level)) + 1) REGION_TYPE, c.PARENT_REGION_FLAG, a.SR_INSTANCE_ID, 2 PARTNER_TYPE FROM MSC_REGIONS a, MSC_ZONE_REGIONS b, msc_region_locations c WHERE a.region_id = b.parent_region_id AND a.region_type = 10 AND a.zone_level IS NOT NULL AND a.sr_instance_id = b.sr_instance_id AND b.region_id = c.region_id and b.sr_instance_id = c.sr_instance_id and c.region_id is not null and c.location_source = 'HZ'

View Text - HTML Formatted

SELECT REGION_ID
, LOCATION_ID
, LOCATION_SOURCE
, (10 * (10 - REGION_TYPE)) REGION_TYPE
, PARENT_REGION_FLAG
, SR_INSTANCE_ID
, 2 PARTNER_TYPE
FROM MSC_REGION_LOCATIONS
WHERE LOCATION_SOURCE = 'HZ'
AND REGION_ID IS NOT NULL UNION SELECT /*+ ORDERED */ A.REGION_ID
, C.LOCATION_ID
, C.LOCATION_SOURCE
, ((10 * (10 - A.ZONE_LEVEL)) + 1) REGION_TYPE
, C.PARENT_REGION_FLAG
, A.SR_INSTANCE_ID
, 2 PARTNER_TYPE
FROM MSC_REGIONS A
, MSC_ZONE_REGIONS B
, MSC_REGION_LOCATIONS C
WHERE A.REGION_ID = B.PARENT_REGION_ID
AND A.REGION_TYPE = 10
AND A.ZONE_LEVEL IS NOT NULL
AND A.SR_INSTANCE_ID = B.SR_INSTANCE_ID
AND B.REGION_ID = C.REGION_ID
AND B.SR_INSTANCE_ID = C.SR_INSTANCE_ID
AND C.REGION_ID IS NOT NULL
AND C.LOCATION_SOURCE = 'HZ'