MSC_AP_REGIONS_V

Details

• Schema: FUSION

• Object owner: MSC

• Object type: VIEW

Columns

Name

SR_REGION_ID REGION_NAME REGION_TYPE REGION_LEVEL COUNTRY_CODE REGION_ZONE_FLAG PARENT_REGION_ID_1 PARENT_REGION_NAME_1 PARENT_REGION_ID_2 PARENT_REGION_NAME_2 PARENT_REGION_ID_3 PARENT_REGION_NAME_3 PARENT_REGION_ID_4 PARENT_REGION_NAME_4 PARENT_REGION_ID_5 PARENT_REGION_NAME_5 PARENT_REGION_ID_6 PARENT_REGION_NAME_6 PARENT_REGION_ID_7 PARENT_REGION_NAME_7 PARENT_REGION_ID_8 PARENT_REGION_NAME_8 PARENT_REGION_TYPE_1 PARENT_REGION_TYPE_2 PARENT_REGION_TYPE_3 PARENT_REGION_TYPE_4 PARENT_REGION_TYPE_5 PARENT_REGION_TYPE_6 PARENT_REGION_TYPE_7 PARENT_REGION_TYPE_8 IMMEDIATE_PARENT_REGION_ID LAST_UPDATE_DATE DELETED_FLAG

Query

SQL_Statement

WITH LVL_QRY AS (SELECT COUNTRY_CODE, GEOGRAPHY_TYPE GEOGRAPHY_TYPE_CODE, LEVEL+1 LVL FROM HZ_GEO_STRUCTURE_LEVELS CONNECT BY PRIOR GEOGRAPHY_TYPE = PARENT_GEOGRAPHY_TYPE AND PRIOR COUNTRY_CODE = COUNTRY_CODE START WITH PARENT_GEOGRAPHY_TYPE = 'COUNTRY') (SELECT G.GEOGRAPHY_ID SR_REGION_ID , G.GEOGRAPHY_NAME REGION_NAME , G.GEOGRAPHY_TYPE REGION_TYPE , NVL(L.LVL,1) REGION_LEVEL , G.COUNTRY_CODE , 'R' region_zone_flag, G.geography_element2_id PARENT_REGION_ID_1 , G.geography_element2 PARENT_REGION_NAME_1 , G.geography_element3_id PARENT_REGION_ID_2 , G.geography_element3 PARENT_REGION_NAME_2 , G.geography_element4_id PARENT_REGION_ID_3 , G.geography_element4 PARENT_REGION_NAME_3 , G.geography_element5_id PARENT_REGION_ID_4 , G.geography_element5 PARENT_REGION_NAME_4 , G.geography_element6_id PARENT_REGION_ID_5 , g.geography_element6 PARENT_REGION_NAME_5 , G.geography_element7_id PARENT_REGION_ID_6 , G.geography_element7 PARENT_REGION_NAME_6 , G.geography_element8_id PARENT_REGION_ID_7 , G.geography_element8 PARENT_REGION_NAME_7 , G.geography_element9_id PARENT_REGION_ID_8 , G.geography_element9 PARENT_REGION_NAME_8 , NVL2(geography_element2_id, (select G1.GEOGRAPHY_TYPE from HZ_GEOGRAPHIES G1 where G1.geography_id = G.geography_element2_id), null) PARENT_REGION_TYPE_1, NVL2(geography_element3_id, (select G1.GEOGRAPHY_TYPE from HZ_GEOGRAPHIES G1 where G1.geography_id = G.geography_element3_id), null) PARENT_REGION_TYPE_2, NVL2(geography_element4_id, (select G1.GEOGRAPHY_TYPE from HZ_GEOGRAPHIES G1 where G1.geography_id = G.geography_element4_id), null) PARENT_REGION_TYPE_3, NVL2(geography_element5_id, (select G1.GEOGRAPHY_TYPE from HZ_GEOGRAPHIES G1 where G1.geography_id = G.geography_element5_id), null) PARENT_REGION_TYPE_4, NVL2(geography_element6_id, (select G1.GEOGRAPHY_TYPE from HZ_GEOGRAPHIES G1 where G1.geography_id = G.geography_element6_id), null) PARENT_REGION_TYPE_5, NVL2(geography_element7_id, (select G1.GEOGRAPHY_TYPE from HZ_GEOGRAPHIES G1 where G1.geography_id = G.geography_element7_id), null) PARENT_REGION_TYPE_6, NVL2(geography_element8_id, (select G1.GEOGRAPHY_TYPE from HZ_GEOGRAPHIES G1 where G1.geography_id = G.geography_element8_id), null) PARENT_REGION_TYPE_7, NVL2(geography_element9_id, (select G1.GEOGRAPHY_TYPE from HZ_GEOGRAPHIES G1 where G1.geography_id = G.geography_element9_id), null) PARENT_REGION_TYPE_8, to_number(DECODE(L.LVL, 1,NULL, 2,G.geography_element1_id, 3,G.geography_element2_id, 4,G.geography_element3_id, 5,G.geography_element4_id, 6,G.geography_element5_id, 7,G.geography_element6_id, 8,G.geography_element7_id, 9,G.geography_element8_id, 10,G.geography_element9_id)) IMMEDIATE_PARENT_REGION_ID, G.last_update_date, CASE WHEN (G.END_DATE < SYSDATE OR G.START_DATE > SYSDATE) THEN 1 ELSE 2 END DELETED_FLAG FROM HZ_GEOGRAPHIES G , LVL_QRY L WHERE G.GEOGRAPHY_USE = 'MASTER_REF' AND G.COUNTRY_CODE = L.COUNTRY_CODE (+) AND G.GEOGRAPHY_TYPE = L.GEOGRAPHY_TYPE_CODE (+) AND ((L.LVL is not null) or (L.LVL is null and G.GEOGRAPHY_TYPE = 'COUNTRY')))