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.MSD_LEVEL_ASSOCIATIONS_V1

Source

View Text - Preformatted


SELECT /* For DP level values and level associations */ mla.rowid row_id, mla.instance, mai.instance_code, mla.level_id, ml1.level_name, ml1.dimension_code, mlv1.level_value, mlv1.level_pk, mla.sr_level_pk, mla.parent_level_id, ml2.level_name "PARENT_LEVEL_NAME", /* mlv2.level_value */ decode ( nvl(mla.dp_enabled_flag, 1), 3, msd_sr_util.get_null_desc, mlv2.LEVEL_VALUE) "PARENT_LEVEL_VALUE", /* mla.sr_parent_level_pk */ decode ( nvl(mla.dp_enabled_flag, 1), 3, to_char(msd_sr_util.get_null_pk), mla.sr_parent_level_pk), mla.last_update_date, mla.last_updated_by, mla.creation_date, mla.created_by, mla.last_update_login, mla.request_id, mla.program_application_id, mla.program_id, mla.program_update_date, mla.last_refresh_num, mla.created_by_refresh_num, mlv1.dp_enabled_flag, mlv2.dp_enabled_flag, 'DP' plan_type from msd_level_values mlv1, msd_level_values mlv2, msd_levels ml1, msd_levels ml2, msd_level_associations mla, msc_apps_instances mai WHERE ml1.level_id = mla.level_id and mlv1.level_id = mla.level_id and mlv1.instance = mla.instance and mlv1.sr_level_pk = mla.sr_level_pk and ml1.level_id = mlv1.level_id and ml2.level_id = mlv2.level_id and mlv2.level_id = mla.parent_level_id and mlv2.instance = mla.instance and mlv2.sr_level_pk = mla.sr_parent_level_pk and ml2.level_id = mla.parent_level_id and mla.instance = mai.instance_id and ml1.plan_type is null and ml2.plan_type is null and nvl(mlv1.dp_enabled_flag,1) = 1 and nvl(mlv2.dp_enabled_flag,1) <> 2 UNION ALL /* For SOP level values and level associations */ SELECT mla.rowid row_id, mla.instance, mai.instance_code, mla.level_id, ml1.level_name, ml1.dimension_code, mlv1.level_value, mlv1.level_pk, mla.sr_level_pk, mla.parent_level_id, ml2.level_name "PARENT_LEVEL_NAME", /* mlv2.level_value */ decode ( nvl(mla.dp_enabled_flag, 1), 3, msd_sr_util.get_null_desc, mlv2.LEVEL_VALUE) "PARENT_LEVEL_VALUE", /* mla.sr_parent_level_pk */ decode ( nvl(mla.dp_enabled_flag, 1), 3, to_char(msd_sr_util.get_null_pk), mla.sr_parent_level_pk), mla.last_update_date, mla.last_updated_by, mla.creation_date, mla.created_by, mla.last_update_login, mla.request_id, mla.program_application_id, mla.program_id, mla.program_update_date, mla.last_refresh_num, mla.created_by_refresh_num, mlv1.dp_enabled_flag, mlv2.dp_enabled_flag, 'SOP' plan_type from msd_level_values mlv1, msd_level_values mlv2, msd_levels ml1, msd_levels ml2, msd_level_associations mla, msc_apps_instances mai WHERE ml1.level_id = mla.level_id and mlv1.level_id = mla.level_id and mlv1.instance = mla.instance and mlv1.sr_level_pk = mla.sr_level_pk and ml1.level_id = mlv1.level_id and ml2.level_id = mlv2.level_id and mlv2.level_id = mla.parent_level_id and mlv2.instance = mla.instance and mlv2.sr_level_pk = mla.sr_parent_level_pk and ml2.level_id = mla.parent_level_id and mla.instance = mai.instance_id and ml1.plan_type is null and ml2.plan_type is null and nvl(mlv1.dp_enabled_flag,1) <> 3 UNION ALL /* For EOL level values and level associations */ SELECT mla.rowid row_id, mla.instance, mai.instance_code, mla.level_id, ml1.level_name, ml1.dimension_code, mlv1.level_value, mlv1.level_pk, mla.sr_level_pk, mla.parent_level_id, ml2.level_name "PARENT_LEVEL_NAME", mlv2.level_value "PARENT_LEVEL_VALUE", mla.sr_parent_level_pk, mla.last_update_date, mla.last_updated_by, mla.creation_date, mla.created_by, mla.last_update_login, mla.request_id, mla.program_application_id, mla.program_id, mla.program_update_date, mla.last_refresh_num, mla.created_by_refresh_num, mlv1.dp_enabled_flag, mlv2.dp_enabled_flag, 'EOL' plan_type from msd_level_values mlv1, msd_level_values mlv2, msd_levels ml1, msd_levels ml2, msd_level_associations mla, msc_apps_instances mai WHERE ml1.level_id = mla.level_id and mlv1.level_id = mla.level_id and mlv1.instance = mla.instance and mlv1.sr_level_pk = mla.sr_level_pk and ml1.level_id = mlv1.level_id and ml2.level_id = mlv2.level_id and mlv2.level_id = mla.parent_level_id and mlv2.instance = mla.instance and mlv2.sr_level_pk = mla.sr_parent_level_pk and ml2.level_id = mla.parent_level_id and mla.instance = mai.instance_id and ml1.plan_type is null and ml2.plan_type is null

View Text - HTML Formatted

SELECT /* FOR DP LEVEL VALUES
AND LEVEL ASSOCIATIONS */ MLA.ROWID ROW_ID
, MLA.INSTANCE
, MAI.INSTANCE_CODE
, MLA.LEVEL_ID
, ML1.LEVEL_NAME
, ML1.DIMENSION_CODE
, MLV1.LEVEL_VALUE
, MLV1.LEVEL_PK
, MLA.SR_LEVEL_PK
, MLA.PARENT_LEVEL_ID
, ML2.LEVEL_NAME "PARENT_LEVEL_NAME"
, /* MLV2.LEVEL_VALUE */ DECODE ( NVL(MLA.DP_ENABLED_FLAG
, 1)
, 3
, MSD_SR_UTIL.GET_NULL_DESC
, MLV2.LEVEL_VALUE) "PARENT_LEVEL_VALUE"
, /* MLA.SR_PARENT_LEVEL_PK */ DECODE ( NVL(MLA.DP_ENABLED_FLAG
, 1)
, 3
, TO_CHAR(MSD_SR_UTIL.GET_NULL_PK)
, MLA.SR_PARENT_LEVEL_PK)
, MLA.LAST_UPDATE_DATE
, MLA.LAST_UPDATED_BY
, MLA.CREATION_DATE
, MLA.CREATED_BY
, MLA.LAST_UPDATE_LOGIN
, MLA.REQUEST_ID
, MLA.PROGRAM_APPLICATION_ID
, MLA.PROGRAM_ID
, MLA.PROGRAM_UPDATE_DATE
, MLA.LAST_REFRESH_NUM
, MLA.CREATED_BY_REFRESH_NUM
, MLV1.DP_ENABLED_FLAG
, MLV2.DP_ENABLED_FLAG
, 'DP' PLAN_TYPE
FROM MSD_LEVEL_VALUES MLV1
, MSD_LEVEL_VALUES MLV2
, MSD_LEVELS ML1
, MSD_LEVELS ML2
, MSD_LEVEL_ASSOCIATIONS MLA
, MSC_APPS_INSTANCES MAI
WHERE ML1.LEVEL_ID = MLA.LEVEL_ID
AND MLV1.LEVEL_ID = MLA.LEVEL_ID
AND MLV1.INSTANCE = MLA.INSTANCE
AND MLV1.SR_LEVEL_PK = MLA.SR_LEVEL_PK
AND ML1.LEVEL_ID = MLV1.LEVEL_ID
AND ML2.LEVEL_ID = MLV2.LEVEL_ID
AND MLV2.LEVEL_ID = MLA.PARENT_LEVEL_ID
AND MLV2.INSTANCE = MLA.INSTANCE
AND MLV2.SR_LEVEL_PK = MLA.SR_PARENT_LEVEL_PK
AND ML2.LEVEL_ID = MLA.PARENT_LEVEL_ID
AND MLA.INSTANCE = MAI.INSTANCE_ID
AND ML1.PLAN_TYPE IS NULL
AND ML2.PLAN_TYPE IS NULL
AND NVL(MLV1.DP_ENABLED_FLAG
, 1) = 1
AND NVL(MLV2.DP_ENABLED_FLAG
, 1) <> 2 UNION ALL /* FOR SOP LEVEL VALUES
AND LEVEL ASSOCIATIONS */ SELECT MLA.ROWID ROW_ID
, MLA.INSTANCE
, MAI.INSTANCE_CODE
, MLA.LEVEL_ID
, ML1.LEVEL_NAME
, ML1.DIMENSION_CODE
, MLV1.LEVEL_VALUE
, MLV1.LEVEL_PK
, MLA.SR_LEVEL_PK
, MLA.PARENT_LEVEL_ID
, ML2.LEVEL_NAME "PARENT_LEVEL_NAME"
, /* MLV2.LEVEL_VALUE */ DECODE ( NVL(MLA.DP_ENABLED_FLAG
, 1)
, 3
, MSD_SR_UTIL.GET_NULL_DESC
, MLV2.LEVEL_VALUE) "PARENT_LEVEL_VALUE"
, /* MLA.SR_PARENT_LEVEL_PK */ DECODE ( NVL(MLA.DP_ENABLED_FLAG
, 1)
, 3
, TO_CHAR(MSD_SR_UTIL.GET_NULL_PK)
, MLA.SR_PARENT_LEVEL_PK)
, MLA.LAST_UPDATE_DATE
, MLA.LAST_UPDATED_BY
, MLA.CREATION_DATE
, MLA.CREATED_BY
, MLA.LAST_UPDATE_LOGIN
, MLA.REQUEST_ID
, MLA.PROGRAM_APPLICATION_ID
, MLA.PROGRAM_ID
, MLA.PROGRAM_UPDATE_DATE
, MLA.LAST_REFRESH_NUM
, MLA.CREATED_BY_REFRESH_NUM
, MLV1.DP_ENABLED_FLAG
, MLV2.DP_ENABLED_FLAG
, 'SOP' PLAN_TYPE
FROM MSD_LEVEL_VALUES MLV1
, MSD_LEVEL_VALUES MLV2
, MSD_LEVELS ML1
, MSD_LEVELS ML2
, MSD_LEVEL_ASSOCIATIONS MLA
, MSC_APPS_INSTANCES MAI
WHERE ML1.LEVEL_ID = MLA.LEVEL_ID
AND MLV1.LEVEL_ID = MLA.LEVEL_ID
AND MLV1.INSTANCE = MLA.INSTANCE
AND MLV1.SR_LEVEL_PK = MLA.SR_LEVEL_PK
AND ML1.LEVEL_ID = MLV1.LEVEL_ID
AND ML2.LEVEL_ID = MLV2.LEVEL_ID
AND MLV2.LEVEL_ID = MLA.PARENT_LEVEL_ID
AND MLV2.INSTANCE = MLA.INSTANCE
AND MLV2.SR_LEVEL_PK = MLA.SR_PARENT_LEVEL_PK
AND ML2.LEVEL_ID = MLA.PARENT_LEVEL_ID
AND MLA.INSTANCE = MAI.INSTANCE_ID
AND ML1.PLAN_TYPE IS NULL
AND ML2.PLAN_TYPE IS NULL
AND NVL(MLV1.DP_ENABLED_FLAG
, 1) <> 3 UNION ALL /* FOR EOL LEVEL VALUES
AND LEVEL ASSOCIATIONS */ SELECT MLA.ROWID ROW_ID
, MLA.INSTANCE
, MAI.INSTANCE_CODE
, MLA.LEVEL_ID
, ML1.LEVEL_NAME
, ML1.DIMENSION_CODE
, MLV1.LEVEL_VALUE
, MLV1.LEVEL_PK
, MLA.SR_LEVEL_PK
, MLA.PARENT_LEVEL_ID
, ML2.LEVEL_NAME "PARENT_LEVEL_NAME"
, MLV2.LEVEL_VALUE "PARENT_LEVEL_VALUE"
, MLA.SR_PARENT_LEVEL_PK
, MLA.LAST_UPDATE_DATE
, MLA.LAST_UPDATED_BY
, MLA.CREATION_DATE
, MLA.CREATED_BY
, MLA.LAST_UPDATE_LOGIN
, MLA.REQUEST_ID
, MLA.PROGRAM_APPLICATION_ID
, MLA.PROGRAM_ID
, MLA.PROGRAM_UPDATE_DATE
, MLA.LAST_REFRESH_NUM
, MLA.CREATED_BY_REFRESH_NUM
, MLV1.DP_ENABLED_FLAG
, MLV2.DP_ENABLED_FLAG
, 'EOL' PLAN_TYPE
FROM MSD_LEVEL_VALUES MLV1
, MSD_LEVEL_VALUES MLV2
, MSD_LEVELS ML1
, MSD_LEVELS ML2
, MSD_LEVEL_ASSOCIATIONS MLA
, MSC_APPS_INSTANCES MAI
WHERE ML1.LEVEL_ID = MLA.LEVEL_ID
AND MLV1.LEVEL_ID = MLA.LEVEL_ID
AND MLV1.INSTANCE = MLA.INSTANCE
AND MLV1.SR_LEVEL_PK = MLA.SR_LEVEL_PK
AND ML1.LEVEL_ID = MLV1.LEVEL_ID
AND ML2.LEVEL_ID = MLV2.LEVEL_ID
AND MLV2.LEVEL_ID = MLA.PARENT_LEVEL_ID
AND MLV2.INSTANCE = MLA.INSTANCE
AND MLV2.SR_LEVEL_PK = MLA.SR_PARENT_LEVEL_PK
AND ML2.LEVEL_ID = MLA.PARENT_LEVEL_ID
AND MLA.INSTANCE = MAI.INSTANCE_ID
AND ML1.PLAN_TYPE IS NULL
AND ML2.PLAN_TYPE IS NULL