[Home] [Help]
111: Function get_num_measures(p_indicator IN NUMBER, p_dim_set IN NUMBER) RETURN NUMBER IS
112: l_num_measures number;
113: CURSOR cNumCols IS
114: SELECT COUNT(M.MEASURE_COL) NUM_DATA_COLUMNS
115: FROM BSC_SYS_MEASURES M, BSC_DB_MEASURE_BY_DIM_SET_V I
116: WHERE I.MEASURE_ID = M.MEASURE_ID
117: AND I.DIM_SET_ID = p_dim_set
118: AND I.INDICATOR = p_indicator
119: AND M.TYPE = 0 AND NVL(M.SOURCE, 'BSC') in ( 'BSC', 'PMF');
997: BSC_DBGEN_UTILS.Add_Value_Big_In_Cond_Number( 9, l_fact_list(i).fact_id);
998: i:=i+1;
999: END LOOP;
1000: --PMF-BSC Integration: Filter out PMF measures
1001: l_stmt := 'SELECT DISTINCT M.MEASURE_COL FROM BSC_SYS_MEASURES M, BSC_DB_MEASURE_BY_DIM_SET_V I'
1002: || ' WHERE I.MEASURE_ID = M.MEASURE_ID AND ('|| strWhereInIndics ||' )'||
1003: ' AND M.TYPE = 0 AND NVL(M.SOURCE, ''BSC'') in (''PMF'', ''BSC'') ';
1004: OPEN cv FOR l_stmt;
1005: LOOP
1013: We need to see in all system measures if there is a formula using that measure.
1014: IF that happen we need to add that measure. Any kpi using that meaure should be flaged too.*/
1015: strWhereNotInIndics := ' NOT ( ' || strWhereInIndics || ')';
1016: l_stmt := 'SELECT DISTINCT M.MEASURE_ID, M.MEASURE_COL '
1017: ||'FROM BSC_SYS_MEASURES M, BSC_DB_MEASURE_BY_DIM_SET_V I '||
1018: ' WHERE I.MEASURE_ID = M.MEASURE_ID AND ('|| strWhereNotInIndics ||' ) '||
1019: ' AND M.TYPE = 0 AND NVL(M.SOURCE, ''BSC'') in (''BSC'', ''PMF'')';
1020: OPEN cv FOR l_stmt;
1021: LOOP
1044: EXIT WHEN i=arrRelatedMeasuresIds.last;
1045: BSC_DBGEN_UTILS.Add_Value_Big_In_Cond_Number( 9, arrRelatedMeasuresIds(i));
1046: i:= arrRelatedMeasuresIds.next(i);
1047: END LOOP;
1048: l_stmt := 'SELECT DISTINCT INDICATOR FROM BSC_DB_MEASURE_BY_DIM_SET_V '||
1049: ' WHERE ('|| strWhereInMeasures || ')';
1050: open cv for L_stmt;
1051: LOOP
1052: FETCH cv into l_indicator_id;/*Indicator*/
1178: NVL(BSC_APPS.GET_PROPERTY_VALUE(M.S_COLOR_FORMULA, :2 ),''N'') AS PAVGL,
1179: BSC_APPS.GET_PROPERTY_VALUE(M.S_COLOR_FORMULA, :3) AS PAVGLTOTAL,
1180: BSC_APPS.GET_PROPERTY_VALUE(M.S_COLOR_FORMULA, :4) AS PAVGLCOUNTER,
1181: M.source
1182: FROM BSC_SYS_MEASURES M, BSC_DB_MEASURE_BY_DIM_SET_V I
1183: WHERE I.MEASURE_ID = M.MEASURE_ID
1184: AND I.MEASURE_COL = M.MEASURE_COL
1185: AND I.DIM_SET_ID = :5
1186: AND I.INDICATOR = :6
1365: FUNCTION Get_Measures_For_Fact(p_fact IN VARCHAR2, p_dim_set IN NUMBER, p_include_derived_columns IN BOOLEAN default false) return BSC_DBGEN_STD_METADATA.tab_clsMeasure IS
1366:
1367: CURSOR cMeasureList IS
1368: SELECT M.MEASURE_COL, NVL(M.OPERATION, 'SUM') AS OPER, m.source measure_source
1369: FROM BSC_SYS_MEASURES M, BSC_DB_MEASURE_BY_DIM_SET_V I
1370: WHERE I.MEASURE_ID = M.MEASURE_ID
1371: AND I.DIM_SET_ID = p_dim_set
1372: AND I.INDICATOR = to_number(p_fact)
1373: AND M.TYPE = 0