VIEW: APPS.QPR_GEO_ALLHIER_V

Source

View Text - Preformatted

SELECT a.level1_desc AS tp_site_desc, a.level1_value AS tp_site_sr_level_pk, a.level1_attribute1 AS tp_site_attribute1, a.level1_attribute2 AS tp_site_attribute2, a.level1_attribute3 AS tp_site_attribute3, a.level1_attribute4 AS tp_site_attribute4, a.level1_attribute5 AS tp_site_attribute5, a.level2_desc AS reg_desc, a.level2_value AS reg_sr_level_pk, a.level2_attribute1 AS reg_attribute1, a.level2_attribute2 AS reg_attribute2, a.level2_attribute3 AS reg_attribute3, a.level2_attribute4 AS reg_attribute4, a.level2_attribute5 AS reg_attribute5, b.level2_desc AS gs_desc, b.level2_value AS gs_sr_level_pk, b.level2_attribute1 AS gs_attribute1, b.level2_attribute2 AS gs_attribute2, b.level2_attribute3 AS gs_attribute3, b.level2_attribute4 AS gs_attribute4, b.level2_attribute5 AS gs_attribute5, a.level3_desc AS country_desc, a.level3_value AS country_sr_level_pk, a.level3_attribute1 AS country_attribute1, a.level3_attribute2 AS country_attribute2, a.level3_attribute3 AS country_attribute3, a.level3_attribute4 AS country_attribute4, a.level3_attribute5 AS country_attribute5, a.level4_desc AS area_desc, a.level4_value AS area_sr_level_pk, a.level4_attribute1 AS area_attribute1, a.level4_attribute2 AS area_attribute2, a.level4_attribute3 AS area_attribute3, a.level4_attribute4 AS area_attribute4, a.level4_attribute5 AS area_attribute5, a.level5_desc AS all_geo_desc, a.level5_value AS all_geo_sr_level_pk, a.level5_attribute1 AS all_geo_attribute1, a.level5_attribute2 AS all_geo_attribute2, a.level5_attribute3 AS all_geo_attribute3, a.level5_attribute4 AS all_geo_attribute4, a.level5_attribute5 AS all_geo_attribute5, a.instance_id AS instance_id FROM qpr_dimension_values a, qpr_dimension_values b WHERE a.level1_value = b.level1_value(+) AND a.dim_code = 'GEO' AND a.hierarchy_code = 'GEOGRAPHY' AND b.dim_code(+) = 'GEO' AND b.hierarchy_code(+) = 'GEOGRAPHYSEGMENT' AND a.instance_id = b.instance_id(+)

View Text - HTML Formatted

SELECT A.LEVEL1_DESC AS TP_SITE_DESC
, A.LEVEL1_VALUE AS TP_SITE_SR_LEVEL_PK
, A.LEVEL1_ATTRIBUTE1 AS TP_SITE_ATTRIBUTE1
, A.LEVEL1_ATTRIBUTE2 AS TP_SITE_ATTRIBUTE2
, A.LEVEL1_ATTRIBUTE3 AS TP_SITE_ATTRIBUTE3
, A.LEVEL1_ATTRIBUTE4 AS TP_SITE_ATTRIBUTE4
, A.LEVEL1_ATTRIBUTE5 AS TP_SITE_ATTRIBUTE5
, A.LEVEL2_DESC AS REG_DESC
, A.LEVEL2_VALUE AS REG_SR_LEVEL_PK
, A.LEVEL2_ATTRIBUTE1 AS REG_ATTRIBUTE1
, A.LEVEL2_ATTRIBUTE2 AS REG_ATTRIBUTE2
, A.LEVEL2_ATTRIBUTE3 AS REG_ATTRIBUTE3
, A.LEVEL2_ATTRIBUTE4 AS REG_ATTRIBUTE4
, A.LEVEL2_ATTRIBUTE5 AS REG_ATTRIBUTE5
, B.LEVEL2_DESC AS GS_DESC
, B.LEVEL2_VALUE AS GS_SR_LEVEL_PK
, B.LEVEL2_ATTRIBUTE1 AS GS_ATTRIBUTE1
, B.LEVEL2_ATTRIBUTE2 AS GS_ATTRIBUTE2
, B.LEVEL2_ATTRIBUTE3 AS GS_ATTRIBUTE3
, B.LEVEL2_ATTRIBUTE4 AS GS_ATTRIBUTE4
, B.LEVEL2_ATTRIBUTE5 AS GS_ATTRIBUTE5
, A.LEVEL3_DESC AS COUNTRY_DESC
, A.LEVEL3_VALUE AS COUNTRY_SR_LEVEL_PK
, A.LEVEL3_ATTRIBUTE1 AS COUNTRY_ATTRIBUTE1
, A.LEVEL3_ATTRIBUTE2 AS COUNTRY_ATTRIBUTE2
, A.LEVEL3_ATTRIBUTE3 AS COUNTRY_ATTRIBUTE3
, A.LEVEL3_ATTRIBUTE4 AS COUNTRY_ATTRIBUTE4
, A.LEVEL3_ATTRIBUTE5 AS COUNTRY_ATTRIBUTE5
, A.LEVEL4_DESC AS AREA_DESC
, A.LEVEL4_VALUE AS AREA_SR_LEVEL_PK
, A.LEVEL4_ATTRIBUTE1 AS AREA_ATTRIBUTE1
, A.LEVEL4_ATTRIBUTE2 AS AREA_ATTRIBUTE2
, A.LEVEL4_ATTRIBUTE3 AS AREA_ATTRIBUTE3
, A.LEVEL4_ATTRIBUTE4 AS AREA_ATTRIBUTE4
, A.LEVEL4_ATTRIBUTE5 AS AREA_ATTRIBUTE5
, A.LEVEL5_DESC AS ALL_GEO_DESC
, A.LEVEL5_VALUE AS ALL_GEO_SR_LEVEL_PK
, A.LEVEL5_ATTRIBUTE1 AS ALL_GEO_ATTRIBUTE1
, A.LEVEL5_ATTRIBUTE2 AS ALL_GEO_ATTRIBUTE2
, A.LEVEL5_ATTRIBUTE3 AS ALL_GEO_ATTRIBUTE3
, A.LEVEL5_ATTRIBUTE4 AS ALL_GEO_ATTRIBUTE4
, A.LEVEL5_ATTRIBUTE5 AS ALL_GEO_ATTRIBUTE5
, A.INSTANCE_ID AS INSTANCE_ID
FROM QPR_DIMENSION_VALUES A
, QPR_DIMENSION_VALUES B
WHERE A.LEVEL1_VALUE = B.LEVEL1_VALUE(+)
AND A.DIM_CODE = 'GEO'
AND A.HIERARCHY_CODE = 'GEOGRAPHY'
AND B.DIM_CODE(+) = 'GEO'
AND B.HIERARCHY_CODE(+) = 'GEOGRAPHYSEGMENT'
AND A.INSTANCE_ID = B.INSTANCE_ID(+)