VIEW: APPS.QPR_PRD_ALLHIER_L_V

Source

View Text - Preformatted

SELECT a.level1_desc as ITEM_DESC, a.level1_value as ITEM_SR_LEVEL_PK, a.level1_attribute1 as ITEM_ATTRIBUTE1, a.level1_attribute2 as ITEM_ATTRIBUTE2, a.level1_attribute3 as ITEM_ATTRIBUTE3, a.level1_attribute4 as ITEM_ATTRIBUTE4, a.level1_attribute5 as ITEM_ATTRIBUTE5, a.instance_id as INSTANCE_ID, a.level2_desc as PC_DESC, a.level2_value as PC_SR_LEVEL_PK, a.level2_attribute1 as PC_ATTRIBUTE1, a.level2_attribute2 as PC_ATTRIBUTE2, a.level2_attribute3 as PC_ATTRIBUTE3, a.level2_attribute4 as PC_ATTRIBUTE4, a.level2_attribute5 as PC_ATTRIBUTE5, nvl(b.level2_desc, qpr_sr_util.get_null_desc) as PF_DESC, nvl(b.level2_value, qpr_sr_util.get_null_pk) as PF_SR_LEVEL_PK, b.level2_attribute1 as PF_ATTRIBUTE1, b.level2_attribute2 as PF_ATTRIBUTE2, b.level2_attribute3 as PF_ATTRIBUTE3, b.level2_attribute4 as PF_ATTRIBUTE4, b.level2_attribute5 as PF_ATTRIBUTE5, a.level3_desc as ALL_DESC, a.level3_value as ALL_SR_LEVEL_PK, a.level3_attribute1 as ALL_ATTRIBUTE1, a.level3_attribute2 as ALL_ATTRIBUTE2, a.level3_attribute3 as ALL_ATTRIBUTE3, a.level3_attribute4 as ALL_ATTRIBUTE4, a.level3_attribute5 as ALL_ATTRIBUTE5 from qpr_dimension_values a, qpr_dimension_values b WHERE a.dim_code='PRD' and a.hierarchy_code = 'PRODUCTCATEGORY' and b.dim_code(+)='PRD' and b.hierarchy_code(+) = 'PRODUCTFAMILY' and a.level1_value=b.level1_value(+) and a.instance_id=b.instance_id(+) and a.instance_id= qpr_maintain_aw.get_instance and(qpr_maintain_aw.get_item is null or a.level1_value in (select qpm2.attribute_1 from qpr_plan_measures qpm2 where qpm2.price_plan_id=qpr_maintain_aw.get_price_plan_id and qpm2.price_plan_meas_grp_id=2 and qpm2.run_number=qpr_maintain_aw.get_run_number))

View Text - HTML Formatted

SELECT A.LEVEL1_DESC AS ITEM_DESC
, A.LEVEL1_VALUE AS ITEM_SR_LEVEL_PK
, A.LEVEL1_ATTRIBUTE1 AS ITEM_ATTRIBUTE1
, A.LEVEL1_ATTRIBUTE2 AS ITEM_ATTRIBUTE2
, A.LEVEL1_ATTRIBUTE3 AS ITEM_ATTRIBUTE3
, A.LEVEL1_ATTRIBUTE4 AS ITEM_ATTRIBUTE4
, A.LEVEL1_ATTRIBUTE5 AS ITEM_ATTRIBUTE5
, A.INSTANCE_ID AS INSTANCE_ID
, A.LEVEL2_DESC AS PC_DESC
, A.LEVEL2_VALUE AS PC_SR_LEVEL_PK
, A.LEVEL2_ATTRIBUTE1 AS PC_ATTRIBUTE1
, A.LEVEL2_ATTRIBUTE2 AS PC_ATTRIBUTE2
, A.LEVEL2_ATTRIBUTE3 AS PC_ATTRIBUTE3
, A.LEVEL2_ATTRIBUTE4 AS PC_ATTRIBUTE4
, A.LEVEL2_ATTRIBUTE5 AS PC_ATTRIBUTE5
, NVL(B.LEVEL2_DESC
, QPR_SR_UTIL.GET_NULL_DESC) AS PF_DESC
, NVL(B.LEVEL2_VALUE
, QPR_SR_UTIL.GET_NULL_PK) AS PF_SR_LEVEL_PK
, B.LEVEL2_ATTRIBUTE1 AS PF_ATTRIBUTE1
, B.LEVEL2_ATTRIBUTE2 AS PF_ATTRIBUTE2
, B.LEVEL2_ATTRIBUTE3 AS PF_ATTRIBUTE3
, B.LEVEL2_ATTRIBUTE4 AS PF_ATTRIBUTE4
, B.LEVEL2_ATTRIBUTE5 AS PF_ATTRIBUTE5
, A.LEVEL3_DESC AS ALL_DESC
, A.LEVEL3_VALUE AS ALL_SR_LEVEL_PK
, A.LEVEL3_ATTRIBUTE1 AS ALL_ATTRIBUTE1
, A.LEVEL3_ATTRIBUTE2 AS ALL_ATTRIBUTE2
, A.LEVEL3_ATTRIBUTE3 AS ALL_ATTRIBUTE3
, A.LEVEL3_ATTRIBUTE4 AS ALL_ATTRIBUTE4
, A.LEVEL3_ATTRIBUTE5 AS ALL_ATTRIBUTE5
FROM QPR_DIMENSION_VALUES A
, QPR_DIMENSION_VALUES B
WHERE A.DIM_CODE='PRD'
AND A.HIERARCHY_CODE = 'PRODUCTCATEGORY'
AND B.DIM_CODE(+)='PRD'
AND B.HIERARCHY_CODE(+) = 'PRODUCTFAMILY'
AND A.LEVEL1_VALUE=B.LEVEL1_VALUE(+)
AND A.INSTANCE_ID=B.INSTANCE_ID(+)
AND A.INSTANCE_ID= QPR_MAINTAIN_AW.GET_INSTANCE AND(QPR_MAINTAIN_AW.GET_ITEM IS NULL OR A.LEVEL1_VALUE IN (SELECT QPM2.ATTRIBUTE_1
FROM QPR_PLAN_MEASURES QPM2
WHERE QPM2.PRICE_PLAN_ID=QPR_MAINTAIN_AW.GET_PRICE_PLAN_ID
AND QPM2.PRICE_PLAN_MEAS_GRP_ID=2
AND QPM2.RUN_NUMBER=QPR_MAINTAIN_AW.GET_RUN_NUMBER))