[Home] [Help]
View: AR_LOCATION_COMBINATIONS
View Text
SELECT G.LOCATION_ID LOCATION_ID
,
SUBSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, 1
,
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 1)-1) LOCATION_ID_SEGMENT_1
,
DECODE(SIGN(V.LEVEL1-2)
, -1
, NULL
, 0
,
SUBSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
,
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 1)+1
, 1000)
,
SUBSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
,
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 1)+1
,
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 2) -
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 1)-1)) LOCATION_ID_SEGMENT_2
,
DECODE(SIGN(V.LEVEL1-3)
, -1
, NULL
, 0
,
SUBSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
,
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 2)+1
, 1000)
,
SUBSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
,
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 2)+1
,
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 3) -
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 2)-1)) LOCATION_ID_SEGMENT_3
,
DECODE(SIGN(V.LEVEL1-4)
, -1
, NULL
, 0
,
SUBSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
,
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 3)+1
, 1000)
,
SUBSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
,
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 3)+1
,
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 4) -
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 3)-1)) LOCATION_ID_SEGMENT_4
,
DECODE(SIGN(V.LEVEL1-5)
, -1
, NULL
, 0
,
SUBSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
,
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 4)+1
, 1000)
,
SUBSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
,
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 4)+1
,
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 5) -
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 4)-1)) LOCATION_ID_SEGMENT_5
,
DECODE(SIGN(V.LEVEL1-6)
, -1
, NULL
, 0
,
SUBSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
,
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 5)+1
, 1000)
,
SUBSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
,
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 5)+1
,
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 6) -
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 5)-1)) LOCATION_ID_SEGMENT_6
,
DECODE(SIGN(V.LEVEL1-7)
, -1
, NULL
, 0
,
SUBSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
,
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 6)+1
, 1000)
,
SUBSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
,
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 6)+1
,
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 7) -
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 6)-1)) LOCATION_ID_SEGMENT_7
,
DECODE(SIGN(V.LEVEL1-8)
, -1
, NULL
, 0
,
SUBSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
,
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 7)+1
, 1000)
,
SUBSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
,
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 7)+1
,
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 8) -
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 7)-1)) LOCATION_ID_SEGMENT_8
,
DECODE(SIGN(V.LEVEL1-9)
, -1
, NULL
, 0
,
SUBSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
,
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 8)+1
, 1000)
,
SUBSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
,
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 8)+1
,
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 9) -
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 8)-1)) LOCATION_ID_SEGMENT_9
,
DECODE(SIGN(V.LEVEL1-10)
, -1
, NULL
, 0
,
SUBSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
,
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 9)+1
, 1000)
,
SUBSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
,
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 9)+1
,
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 10) -
INSTR(REPLACE(SUBSTRB(V.GEO_ID_PATH
, 2)
, '|'
, '.')
, '.'
, 1
, 9)-1)) LOCATION_ID_SEGMENT_10
,
REPLACE(SUBSTRB(V.PATH
, 2)
, '|'
, '.') LOCATION_SEGMENTS
,
-99 LOCATION_STRUCTURE_ID
,
'Y' ENABLED_FLAG
,
TO_NUMBER(NULL) LAST_UPDATED_BY
,
TO_DATE(NULL) LAST_UPDATE_DATE
,
TO_DATE(NULL) START_DATE_ACTIVE
,
TO_DATE(NULL) END_DATE_ACTIVE
,
'Y' SUMMARY_FLAG
,
TO_NUMBER(NULL) PROGRAM_APPLICATION_ID
,
TO_NUMBER(NULL) PROGRAM_ID
,
TO_DATE(NULL) PROGRAM_UPDATE_DATE
,
NULL REQUEST_ID
,
NULL ATTRIBUTE_CATEGORY
,
NULL ATTRIBUTE1
,
NULL ATTRIBUTE2
,
NULL ATTRIBUTE3
,
NULL ATTRIBUTE4
,
NULL ATTRIBUTE5
,
NULL ATTRIBUTE6
,
NULL ATTRIBUTE7
,
NULL ATTRIBUTE8
,
NULL ATTRIBUTE9
,
NULL ATTRIBUTE10
,
NULL ATTRIBUTE11
,
NULL ATTRIBUTE12
,
NULL ATTRIBUTE13
,
NULL ATTRIBUTE14
,
NULL ATTRIBUTE15
,
TO_NUMBER(NULL) CREATED_BY
,
TO_DATE(NULL) CREATION_DATE/*
,
REVERSE_STRING (REPLACE(SUBSTRB(V.PATH
, 2)
, '|'
, '.')
, '.'
, V.LEVEL1) REVERSE_LOCATION_SEGMENTS*/
FROM
(SELECT SYS_CONNECT_BY_PATH(B.GEOGRAPHY_NAME
, '|') "PATH"
,
B.GEOGRAPHY_ELEMENT1 "COUNTRY"
,
SYS_CONNECT_BY_PATH(B.GEOGRAPHY_ID
, '|') "GEO_ID_PATH"
,
SYS_CONNECT_BY_PATH(B.GEOGRAPHY_TYPE
, '|') "GEO_TYPE_PATH"
,
B.GEOGRAPHY_ID "ID"
,
A.CHILD_OBJECT_TYPE "GEO_TYPE"
,
LEVEL "LEVEL1"
FROM HZ_HIERARCHY_NODES A
, HZ_GEOGRAPHIES B
WHERE A.CHILD_ID = B.GEOGRAPHY_ID
START WITH (PARENT_OBJECT_TYPE = 'COUNTRY'
AND LEVEL_NUMBER = 1
AND HIERARCHY_TYPE = 'MASTER_REF'
AND EXISTS (SELECT 'X'
FROM HZ_GEOGRAPHIES C
WHERE A.PARENT_ID = C.GEOGRAPHY_ID
))
CONNECT BY PARENT_ID = PRIOR CHILD_ID
AND LEVEL_NUMBER = 1
AND HIERARCHY_TYPE = 'MASTER_REF') V
, HZ_GEO_NAME_REFERENCES G
WHERE V.ID = G.GEOGRAPHY_ID
AND G.LOCATION_TABLE_NAME = 'HZ_LOCATIONS'
AND V.GEO_TYPE = (SELECT DISTINCT ST.GEOGRAPHY_TYPE
FROM HZ_GEO_STRUCTURE_LEVELS ST
WHERE NOT EXISTS (SELECT 'X'
FROM HZ_GEO_STRUCTURE_LEVELS ST1
WHERE ST1.PARENT_GEOGRAPHY_TYPE = ST.GEOGRAPHY_TYPE
AND ST1.COUNTRY_CODE = ST.COUNTRY_CODE))