VIEW: APPS.MSD_LEVEL_VALUES_DS_V

Source

View Text - Preformatted

SELECT mds.demand_plan_id, mlv1.LEVEL_ID, mlv1.LEVEL_VALUE, mlv1.LEVEL_PK, mlv2.LEVEL_ID, decode ( nvl(mla.dp_enabled_flag, 1), 3, msd_sr_util.get_null_desc, mlv2.LEVEL_VALUE), decode ( nvl(mla.dp_enabled_flag, 1), 3, msd_common_utilities.get_level_value_pk (mlv2.instance, msd_sr_util.get_null_pk, null, mlv2.level_id), mlv2.LEVEL_PK), mlv1.ATTRIBUTE1, mlv1.ATTRIBUTE2, mlv1.ATTRIBUTE3, mlv1.ATTRIBUTE4, mlv1.ATTRIBUTE5, mlv1.LAST_UPDATE_DATE, mlv1.LAST_UPDATED_BY, mlv1.CREATION_DATE, mlv1.CREATED_BY, mlv1.LAST_UPDATE_LOGIN, greatest(mlv1.last_refresh_num, mla.last_refresh_num), greatest(mlv1.created_by_refresh_num, mla.created_by_refresh_num), mlv1.instance, mlv1.sr_level_pk, decode ( nvl(mla.dp_enabled_flag, 1), 3, to_char(msd_sr_util.get_null_pk), mlv2.sr_level_pk), mlv1.level_value, nvl(mlv1.level_value_desc,mlv1.level_value), decode(mlv1.level_value_desc, null, mlv1.level_value, decode(ml.dimension_code, 'PRD', decode(mlv1.level_value_desc, mlv1.level_value, mlv1.level_value, mlv1.level_value ||':'|| mlv1.level_value_desc), mlv1.level_value_desc ) ), decode ( nvl(mla.dp_enabled_flag, 1), 3, msd_sr_util.get_null_desc, mlv2.LEVEL_VALUE), decode ( nvl(mla.dp_enabled_flag, 1), 3, msd_sr_util.get_null_desc, nvl(mlv2.level_value_desc, mlv2.level_value)), decode ( nvl(mla.dp_enabled_flag, 1), 3, msd_sr_util.get_null_desc, decode(mlv2.level_value_desc, null, mlv2.level_value, decode(ml.dimension_code, 'PRD', decode(mlv2.level_value_desc, mlv2.level_value, mlv2.level_value, mlv2.level_value ||':'|| mlv2.level_value_desc ), mlv2.level_value_desc ))), mlv1.system_attribute1, mlv1.system_attribute2 from msd_dp_session mds, msd_levels ml, msd_level_values mlv2, msd_level_values mlv1, msd_level_associations mla, msd_level_values_ds mld1, msd_level_values_ds mld2 WHERE ml.level_id = mlv1.level_id and mlv1.level_id = mla.level_id and mlv1.instance = mla.instance and mlv1.sr_level_pk = mla.sr_level_pk and mlv2.level_id = mla.parent_level_id and mlv2.instance = mla.instance and mlv2.sr_level_pk = mla.sr_parent_level_pk and mld1.level_pk = mlv1.level_pk and mld2.level_pk = mlv2.level_pk and mld1.demand_plan_id = mds.demand_plan_id and mld2.demand_plan_id = mds.demand_plan_id and nvl(ml.plan_type,'DP') <> 'LIABILITY' and nvl(mld1.dp_enabled_flag,1) = 1 and nvl(mld2.dp_enabled_flag,1) <> 2

View Text - HTML Formatted

SELECT MDS.DEMAND_PLAN_ID
, MLV1.LEVEL_ID
, MLV1.LEVEL_VALUE
, MLV1.LEVEL_PK
, MLV2.LEVEL_ID
, DECODE ( NVL(MLA.DP_ENABLED_FLAG
, 1)
, 3
, MSD_SR_UTIL.GET_NULL_DESC
, MLV2.LEVEL_VALUE)
, DECODE ( NVL(MLA.DP_ENABLED_FLAG
, 1)
, 3
, MSD_COMMON_UTILITIES.GET_LEVEL_VALUE_PK (MLV2.INSTANCE
, MSD_SR_UTIL.GET_NULL_PK
, NULL
, MLV2.LEVEL_ID)
, MLV2.LEVEL_PK)
, MLV1.ATTRIBUTE1
, MLV1.ATTRIBUTE2
, MLV1.ATTRIBUTE3
, MLV1.ATTRIBUTE4
, MLV1.ATTRIBUTE5
, MLV1.LAST_UPDATE_DATE
, MLV1.LAST_UPDATED_BY
, MLV1.CREATION_DATE
, MLV1.CREATED_BY
, MLV1.LAST_UPDATE_LOGIN
, GREATEST(MLV1.LAST_REFRESH_NUM
, MLA.LAST_REFRESH_NUM)
, GREATEST(MLV1.CREATED_BY_REFRESH_NUM
, MLA.CREATED_BY_REFRESH_NUM)
, MLV1.INSTANCE
, MLV1.SR_LEVEL_PK
, DECODE ( NVL(MLA.DP_ENABLED_FLAG
, 1)
, 3
, TO_CHAR(MSD_SR_UTIL.GET_NULL_PK)
, MLV2.SR_LEVEL_PK)
, MLV1.LEVEL_VALUE
, NVL(MLV1.LEVEL_VALUE_DESC
, MLV1.LEVEL_VALUE)
, DECODE(MLV1.LEVEL_VALUE_DESC
, NULL
, MLV1.LEVEL_VALUE
, DECODE(ML.DIMENSION_CODE
, 'PRD'
, DECODE(MLV1.LEVEL_VALUE_DESC
, MLV1.LEVEL_VALUE
, MLV1.LEVEL_VALUE
, MLV1.LEVEL_VALUE ||':'|| MLV1.LEVEL_VALUE_DESC)
, MLV1.LEVEL_VALUE_DESC ) )
, DECODE ( NVL(MLA.DP_ENABLED_FLAG
, 1)
, 3
, MSD_SR_UTIL.GET_NULL_DESC
, MLV2.LEVEL_VALUE)
, DECODE ( NVL(MLA.DP_ENABLED_FLAG
, 1)
, 3
, MSD_SR_UTIL.GET_NULL_DESC
, NVL(MLV2.LEVEL_VALUE_DESC
, MLV2.LEVEL_VALUE))
, DECODE ( NVL(MLA.DP_ENABLED_FLAG
, 1)
, 3
, MSD_SR_UTIL.GET_NULL_DESC
, DECODE(MLV2.LEVEL_VALUE_DESC
, NULL
, MLV2.LEVEL_VALUE
, DECODE(ML.DIMENSION_CODE
, 'PRD'
, DECODE(MLV2.LEVEL_VALUE_DESC
, MLV2.LEVEL_VALUE
, MLV2.LEVEL_VALUE
, MLV2.LEVEL_VALUE ||':'|| MLV2.LEVEL_VALUE_DESC )
, MLV2.LEVEL_VALUE_DESC )))
, MLV1.SYSTEM_ATTRIBUTE1
, MLV1.SYSTEM_ATTRIBUTE2
FROM MSD_DP_SESSION MDS
, MSD_LEVELS ML
, MSD_LEVEL_VALUES MLV2
, MSD_LEVEL_VALUES MLV1
, MSD_LEVEL_ASSOCIATIONS MLA
, MSD_LEVEL_VALUES_DS MLD1
, MSD_LEVEL_VALUES_DS MLD2
WHERE ML.LEVEL_ID = MLV1.LEVEL_ID
AND MLV1.LEVEL_ID = MLA.LEVEL_ID
AND MLV1.INSTANCE = MLA.INSTANCE
AND MLV1.SR_LEVEL_PK = MLA.SR_LEVEL_PK
AND MLV2.LEVEL_ID = MLA.PARENT_LEVEL_ID
AND MLV2.INSTANCE = MLA.INSTANCE
AND MLV2.SR_LEVEL_PK = MLA.SR_PARENT_LEVEL_PK
AND MLD1.LEVEL_PK = MLV1.LEVEL_PK
AND MLD2.LEVEL_PK = MLV2.LEVEL_PK
AND MLD1.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID
AND MLD2.DEMAND_PLAN_ID = MDS.DEMAND_PLAN_ID
AND NVL(ML.PLAN_TYPE
, 'DP') <> 'LIABILITY'
AND NVL(MLD1.DP_ENABLED_FLAG
, 1) = 1
AND NVL(MLD2.DP_ENABLED_FLAG
, 1) <> 2