VIEW: APPS.BSC_DB_BASIC_DIM_SETS_V

Source

View Text - Preformatted

SELECT AM.INDICATOR, AM.ANALYSIS_OPTION0 AS A0, AM.ANALYSIS_OPTION1 AS A1, AM.ANALYSIS_OPTION2 AS A2, AM.SERIES_ID AS SERIES_ID, OPT.DIM_SET_ID, AM.DATASET_ID FROM BSC_KPI_ANALYSIS_MEASURES_B AM, ( /* Obtain the Analysis option in Analysis Group 0 that can change dimension set */ SELECT AO.INDICATOR, AO.OPTION_ID AS A0, -1 AS A1, -1 AS A2, AO.DIM_SET_ID AS DIM_SET_ID FROM BSC_KPI_ANALYSIS_OPTIONS_B AO, BSC_KPI_ANALYSIS_GROUPS AG WHERE AO.INDICATOR = AG.INDICATOR AND AO.ANALYSIS_GROUP_ID = AG.ANALYSIS_GROUP_ID AND AG.ANALYSIS_GROUP_ID = 0 AND AG.CHANGE_DIM_SET =1 UNION ALL /* Obtain the Analysis option in Analysis Group 1 that can change dimension set */ SELECT AO.INDICATOR, DECODE(AG.DEPENDENCY_FLAG, 1, AO.PARENT_OPTION_ID, -1) AS A0, AO.OPTION_ID AS A1, -1 AS A2, AO.DIM_SET_ID AS DIM_SET_ID FROM BSC_KPI_ANALYSIS_OPTIONS_B AO, BSC_KPI_ANALYSIS_GROUPS AG WHERE AO.INDICATOR = AG.INDICATOR AND AO.ANALYSIS_GROUP_ID = AG.ANALYSIS_GROUP_ID AND AG.ANALYSIS_GROUP_ID = 1 AND AG.CHANGE_DIM_SET =1 UNION ALL /* Obtain the Analysis option in Analysis Group 2 that can change dimension set */ SELECT AO.INDICATOR, DECODE(AG2.DEPENDENCY_FLAG, 1, AO.GRANDPARENT_OPTION_ID, -1) AS A0, DECODE(AG1.DEPENDENCY_FLAG, 1, AO.PARENT_OPTION_ID, -1) AS A1, AO.OPTION_ID AS A2, AO.DIM_SET_ID AS DIM_SET_ID FROM BSC_KPI_ANALYSIS_OPTIONS_B AO, BSC_KPI_ANALYSIS_GROUPS AG1, BSC_KPI_ANALYSIS_GROUPS AG2 WHERE AO.INDICATOR = AG1.INDICATOR AND AO.ANALYSIS_GROUP_ID = AG1.ANALYSIS_GROUP_ID AND AG1.ANALYSIS_GROUP_ID = 2 AND AG1.CHANGE_DIM_SET= 1 AND AG2.ANALYSIS_GROUP_ID = 1 AND AG1.INDICATOR = AG2.INDICATOR ) OPT WHERE AM.INDICATOR = OPT.INDICATOR AND (OPT.A0 =AM.ANALYSIS_OPTION0 OR OPT.A0 = -1) AND (OPT.A1 = AM.ANALYSIS_OPTION1 OR OPT.A1 = -1) AND (OPT.A2 = AM.ANALYSIS_OPTION2 OR OPT.A2 = -1)

View Text - HTML Formatted

SELECT AM.INDICATOR
, AM.ANALYSIS_OPTION0 AS A0
, AM.ANALYSIS_OPTION1 AS A1
, AM.ANALYSIS_OPTION2 AS A2
, AM.SERIES_ID AS SERIES_ID
, OPT.DIM_SET_ID
, AM.DATASET_ID
FROM BSC_KPI_ANALYSIS_MEASURES_B AM
, ( /* OBTAIN THE ANALYSIS OPTION IN ANALYSIS GROUP 0 THAT CAN CHANGE DIMENSION SET */ SELECT AO.INDICATOR
, AO.OPTION_ID AS A0
, -1 AS A1
, -1 AS A2
, AO.DIM_SET_ID AS DIM_SET_ID
FROM BSC_KPI_ANALYSIS_OPTIONS_B AO
, BSC_KPI_ANALYSIS_GROUPS AG
WHERE AO.INDICATOR = AG.INDICATOR
AND AO.ANALYSIS_GROUP_ID = AG.ANALYSIS_GROUP_ID
AND AG.ANALYSIS_GROUP_ID = 0
AND AG.CHANGE_DIM_SET =1 UNION ALL /* OBTAIN THE ANALYSIS OPTION IN ANALYSIS GROUP 1 THAT CAN CHANGE DIMENSION SET */ SELECT AO.INDICATOR
, DECODE(AG.DEPENDENCY_FLAG
, 1
, AO.PARENT_OPTION_ID
, -1) AS A0
, AO.OPTION_ID AS A1
, -1 AS A2
, AO.DIM_SET_ID AS DIM_SET_ID
FROM BSC_KPI_ANALYSIS_OPTIONS_B AO
, BSC_KPI_ANALYSIS_GROUPS AG
WHERE AO.INDICATOR = AG.INDICATOR
AND AO.ANALYSIS_GROUP_ID = AG.ANALYSIS_GROUP_ID
AND AG.ANALYSIS_GROUP_ID = 1
AND AG.CHANGE_DIM_SET =1 UNION ALL /* OBTAIN THE ANALYSIS OPTION IN ANALYSIS GROUP 2 THAT CAN CHANGE DIMENSION SET */ SELECT AO.INDICATOR
, DECODE(AG2.DEPENDENCY_FLAG
, 1
, AO.GRANDPARENT_OPTION_ID
, -1) AS A0
, DECODE(AG1.DEPENDENCY_FLAG
, 1
, AO.PARENT_OPTION_ID
, -1) AS A1
, AO.OPTION_ID AS A2
, AO.DIM_SET_ID AS DIM_SET_ID
FROM BSC_KPI_ANALYSIS_OPTIONS_B AO
, BSC_KPI_ANALYSIS_GROUPS AG1
, BSC_KPI_ANALYSIS_GROUPS AG2
WHERE AO.INDICATOR = AG1.INDICATOR
AND AO.ANALYSIS_GROUP_ID = AG1.ANALYSIS_GROUP_ID
AND AG1.ANALYSIS_GROUP_ID = 2
AND AG1.CHANGE_DIM_SET= 1
AND AG2.ANALYSIS_GROUP_ID = 1
AND AG1.INDICATOR = AG2.INDICATOR ) OPT
WHERE AM.INDICATOR = OPT.INDICATOR
AND (OPT.A0 =AM.ANALYSIS_OPTION0 OR OPT.A0 = -1)
AND (OPT.A1 = AM.ANALYSIS_OPTION1 OR OPT.A1 = -1)
AND (OPT.A2 = AM.ANALYSIS_OPTION2 OR OPT.A2 = -1)