VIEW: APPS.QPR_SG_ALLHIER_L_V

Source

View Text - Preformatted

SELECT level6_desc as ALL_DESC, level6_value as ALL_SR_LEVEL_PK, level6_attribute1 as ALL_ATTRIBUTE1, level6_attribute2 as ALL_ATTRIBUTE2, level6_attribute3 as ALL_ATTRIBUTE3, level6_attribute4 as ALL_ATTRIBUTE4, level6_attribute5 as ALL_ATTRIBUTE5, level5_desc as SG4_DESC, level5_value as SG4_SR_LEVEL_PK, level5_attribute1 as SG4_ATTRIBUTE1, level5_attribute2 as SG4_ATTRIBUTE2, level5_attribute3 as SG4_ATTRIBUTE3, level5_attribute4 as SG4_ATTRIBUTE4, level5_attribute5 as SG4_ATTRIBUTE5, level4_desc as SG3_DESC, level4_value as SG3_SR_LEVEL_PK, level4_attribute1 as SG3_ATTRIBUTE1, level4_attribute2 as SG3_ATTRIBUTE2, level4_attribute3 as SG3_ATTRIBUTE3, level4_attribute4 as SG3_ATTRIBUTE4, level4_attribute5 as SG3_ATTRIBUTE5, level3_desc as SG2_DESC, level3_value as SG2_SR_LEVEL_PK, level3_attribute1 as SG2_ATTRIBUTE1, level3_attribute2 as SG2_ATTRIBUTE2, level3_attribute3 as SG2_ATTRIBUTE3, level3_attribute4 as SG2_ATTRIBUTE4, level3_attribute5 as SG2_ATTRIBUTE5, level2_desc as SG1_DESC, level2_value as SG1_SR_LEVEL_PK, level2_attribute1 as SG1_ATTRIBUTE1, level2_attribute2 as SG1_ATTRIBUTE2, level2_attribute3 as SG1_ATTRIBUTE3, level2_attribute4 as SG1_ATTRIBUTE4, level2_attribute5 as SG1_ATTRIBUTE5, level1_desc as SR_DESC, level1_value as SR_SR_LEVEL_PK, level1_attribute1 as SR_ATTRIBUTE1, level1_attribute2 as SR_ATTRIBUTE2, level1_attribute3 as SR_ATTRIBUTE3, level1_attribute4 as SR_ATTRIBUTE4, level1_attribute5 as SR_ATTRIBUTE5, instance_id as INSTANCE_ID from qpr_dimension_values WHERE dim_code='REP' and hierarchy_code = 'SALESGROUP' and instance_id = qpr_maintain_aw.get_instance and (qpr_maintain_aw.get_sr is null or level1_value in (select qpm6.attribute_1 from qpr_plan_measures qpm6 where qpm6.price_plan_id=qpr_maintain_aw.get_price_plan_id and qpm6.price_plan_meas_grp_id=6 and qpm6.run_number=qpr_maintain_aw.get_run_number))

View Text - HTML Formatted

SELECT LEVEL6_DESC AS ALL_DESC
, LEVEL6_VALUE AS ALL_SR_LEVEL_PK
, LEVEL6_ATTRIBUTE1 AS ALL_ATTRIBUTE1
, LEVEL6_ATTRIBUTE2 AS ALL_ATTRIBUTE2
, LEVEL6_ATTRIBUTE3 AS ALL_ATTRIBUTE3
, LEVEL6_ATTRIBUTE4 AS ALL_ATTRIBUTE4
, LEVEL6_ATTRIBUTE5 AS ALL_ATTRIBUTE5
, LEVEL5_DESC AS SG4_DESC
, LEVEL5_VALUE AS SG4_SR_LEVEL_PK
, LEVEL5_ATTRIBUTE1 AS SG4_ATTRIBUTE1
, LEVEL5_ATTRIBUTE2 AS SG4_ATTRIBUTE2
, LEVEL5_ATTRIBUTE3 AS SG4_ATTRIBUTE3
, LEVEL5_ATTRIBUTE4 AS SG4_ATTRIBUTE4
, LEVEL5_ATTRIBUTE5 AS SG4_ATTRIBUTE5
, LEVEL4_DESC AS SG3_DESC
, LEVEL4_VALUE AS SG3_SR_LEVEL_PK
, LEVEL4_ATTRIBUTE1 AS SG3_ATTRIBUTE1
, LEVEL4_ATTRIBUTE2 AS SG3_ATTRIBUTE2
, LEVEL4_ATTRIBUTE3 AS SG3_ATTRIBUTE3
, LEVEL4_ATTRIBUTE4 AS SG3_ATTRIBUTE4
, LEVEL4_ATTRIBUTE5 AS SG3_ATTRIBUTE5
, LEVEL3_DESC AS SG2_DESC
, LEVEL3_VALUE AS SG2_SR_LEVEL_PK
, LEVEL3_ATTRIBUTE1 AS SG2_ATTRIBUTE1
, LEVEL3_ATTRIBUTE2 AS SG2_ATTRIBUTE2
, LEVEL3_ATTRIBUTE3 AS SG2_ATTRIBUTE3
, LEVEL3_ATTRIBUTE4 AS SG2_ATTRIBUTE4
, LEVEL3_ATTRIBUTE5 AS SG2_ATTRIBUTE5
, LEVEL2_DESC AS SG1_DESC
, LEVEL2_VALUE AS SG1_SR_LEVEL_PK
, LEVEL2_ATTRIBUTE1 AS SG1_ATTRIBUTE1
, LEVEL2_ATTRIBUTE2 AS SG1_ATTRIBUTE2
, LEVEL2_ATTRIBUTE3 AS SG1_ATTRIBUTE3
, LEVEL2_ATTRIBUTE4 AS SG1_ATTRIBUTE4
, LEVEL2_ATTRIBUTE5 AS SG1_ATTRIBUTE5
, LEVEL1_DESC AS SR_DESC
, LEVEL1_VALUE AS SR_SR_LEVEL_PK
, LEVEL1_ATTRIBUTE1 AS SR_ATTRIBUTE1
, LEVEL1_ATTRIBUTE2 AS SR_ATTRIBUTE2
, LEVEL1_ATTRIBUTE3 AS SR_ATTRIBUTE3
, LEVEL1_ATTRIBUTE4 AS SR_ATTRIBUTE4
, LEVEL1_ATTRIBUTE5 AS SR_ATTRIBUTE5
, INSTANCE_ID AS INSTANCE_ID
FROM QPR_DIMENSION_VALUES
WHERE DIM_CODE='REP'
AND HIERARCHY_CODE = 'SALESGROUP'
AND INSTANCE_ID = QPR_MAINTAIN_AW.GET_INSTANCE
AND (QPR_MAINTAIN_AW.GET_SR IS NULL OR LEVEL1_VALUE IN (SELECT QPM6.ATTRIBUTE_1
FROM QPR_PLAN_MEASURES QPM6
WHERE QPM6.PRICE_PLAN_ID=QPR_MAINTAIN_AW.GET_PRICE_PLAN_ID
AND QPM6.PRICE_PLAN_MEAS_GRP_ID=6
AND QPM6.RUN_NUMBER=QPR_MAINTAIN_AW.GET_RUN_NUMBER))