FND Design Data [Home] [Help]

View: BSC_DB_BASIC_DIM_SETS_V

Product: BSC - Balanced Scorecard (Obsolete)
Description: View of dimension sets and datasets by analysis option
Implementation/DBA Data: Not implemented in this database
View Text

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_VL 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_VL 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 /* 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_VL 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 /* 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_VL 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)

Columns

Name
INDICATOR
A0
A1
A2
SERIES_ID
DIM_SET_ID
DATASET_ID