eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.AR_LOCATION_COMBINATIONS

Source

View Text - Preformatted


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))

View Text - HTML Formatted

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))