MSC_REGIONS_TCA_V

Details

• Schema: FUSION

• Object owner: MSC

• Object type: VIEW

Columns

Name

REGION_ID REGION_NAME REGION_TYPE REGION_LEVEL COUNTRY_CODE IMMEDIATE_PARENT_REGION_ID 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 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 PARENT_REGION_NAME_LIST 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 REGION_ID , G.GEOGRAPHY_NAME REGION_NAME , G.GEOGRAPHY_TYPE REGION_TYPE , NVL(L.LVL,1) REGION_LEVEL , G.COUNTRY_CODE , 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.geography_element2 PARENT_REGION_NAME_1 , G.geography_element3 PARENT_REGION_NAME_2 , G.geography_element4 PARENT_REGION_NAME_3 , G.geography_element5 PARENT_REGION_NAME_4 , g.geography_element6 PARENT_REGION_NAME_5 , G.geography_element7 PARENT_REGION_NAME_6 , G.geography_element8 PARENT_REGION_NAME_7 , 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, (G.COUNTRY_CODE||NVL2(G.geography_element2,(','||G.geography_element2||NVL2(G.geography_element3,(','||G.geography_element3||NVL2(G.geography_element4,(','||G.geography_element4||NVL2(G.geography_element5,(','||G.geography_element5||NVL2(G.geography_element6,(','||G.geography_element6||NVL2(G.geography_element7,(','||G.geography_element7||NVL2(G.geography_element8,(','||G.geography_element8 ||NVL2(G.geography_element9,(','||G.geography_element9),NULL)),NULL)),NULL)),NULL)),NULL)),NULL)),NULL)),NULL)) PARENT_REGION_NAME_LIST, 2 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')))