[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_objective_color_rec IN BSC_UPDATE_COLOR.t_objective_color_rec
,x_kpi_measure_id OUT NOCOPY NUMBER
,x_color_flag OUT NOCOPY BOOLEAN
);
SELECT default_calculation
FROM bsc_kpi_measure_props
WHERE indicator = p_indicator
AND kpi_measure_id = p_kpi_measure_id;
x_source => 'BSC_UPDATE_COLOR.is_ytd_default_calc');
SELECT kpi_props.kpi_measure_id,
kpi_props.disable_color,
sys_dset.source
FROM bsc_kpi_measure_props kpi_props,
bsc_kpi_analysis_measures_b kpi_meas,
bsc_sys_datasets_b sys_dset
WHERE kpi_props.indicator = p_indicator
AND kpi_props.kpi_measure_id = kpi_meas.kpi_measure_id
AND kpi_meas.dataset_id = sys_dset.dataset_id;
h_dim_combination BSC_UPDATE_UTIL.t_array_of_number;
SELECT config_type, short_name into l_config_type,l_short_name
FROM bsc_kpis_b
WHERE indicator=p_objective_color_rec.objective_id;
l_message := BSC_UPDATE_UTIL.Get_Message('BSC_COLOR_KPI_START');
l_message := BSC_UPDATE_UTIL.Replace_Token(l_message, 'KPI_MEASURE_ID', TO_CHAR(l_objective_kpis.kpi_measure_id));
BSC_UPDATE_LOG.Write_Line_log(l_message, BSC_UPDATE_LOG.OUTPUT);
h_sql := 'SELECT COM_INDEX, FAMILY_INDEX, DIM_INDEX'||
' FROM BSC_TMP_TAB_COM'||
' WHERE TAB_ID = :1'||
' ORDER BY COM_INDEX, FAMILY_INDEX';
l_message := BSC_UPDATE_UTIL.Get_Message('BSC_COLOR_KPI_COMPLETE');
l_message := BSC_UPDATE_UTIL.Replace_Token(l_message, 'KPI_MEASURE_ID', TO_CHAR(l_objective_kpis.kpi_measure_id));
BSC_UPDATE_LOG.Write_Line_log(l_message, BSC_UPDATE_LOG.OUTPUT);
l_message := BSC_UPDATE_UTIL.Get_Message('BSC_COLOR_KPI_SKIP');
l_message := BSC_UPDATE_UTIL.Replace_Token(l_message, 'KPI_MEASURE_ID', TO_CHAR(l_objective_kpis.kpi_measure_id));
BSC_UPDATE_LOG.Write_Line_log(l_message, BSC_UPDATE_LOG.OUTPUT);
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_KPICOLOR_FAILED'),
x_source => 'BSC_UPDATE_COLOR.Color_Kpis_In_Objective');
x_source => 'BSC_UPDATE_COLOR.Color_Kpis_In_Objective');
, p_dim_combination IN BSC_UPDATE_UTIL.t_array_of_number
, p_num_families IN NUMBER
)
RETURN BOOLEAN IS
e_unexpected_error EXCEPTION;
l_kpi_dim_props BSC_UPDATE_UTIL.t_kpi_dim_props_rec;
BSC_UPDATE_UTIL.Get_Kpi_Dim_Props ( p_objective_id => p_objective_color_rec.objective_id
, p_kpi_measure_id => p_kpi_measure_id
, x_dim_props_rec => l_kpi_dim_props
);
l_measure_formula := BSC_UPDATE_UTIL.Get_Measure_Formula ( p_objective_id => p_objective_color_rec.objective_id
, p_kpi_measure_id => p_kpi_measure_id
, p_sim_objective => p_objective_color_rec.sim_flag
);
l_measure_formula := BSC_UPDATE_UTIL.Get_Free_Div_Zero_Expression(l_measure_formula);
l_color_by_total := BSC_UPDATE_UTIL.Get_Color_By_Total ( p_objective_id => p_objective_color_rec.objective_id
, p_kpi_measure_id => p_kpi_measure_id
);
l_apply_color_flag := BSC_UPDATE_UTIL.Get_Apply_Color_Flag ( p_objective_id => p_objective_color_rec.objective_id
, p_kpi_measure_id => p_kpi_measure_id
--, p_sim_objective => p_objective_color_rec.sim_flag
);
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_KPICOLOR_FAILED'),
x_source => 'BSC_UPDATE_COLOR.Color_Kpi_Measure');
x_source => 'BSC_UPDATE_COLOR.Color_Kpi_Measure');
SELECT config_type
INTO l_config_type
FROM bsc_kpis_b
WHERE indicator=p_objective_id;
SELECT color_method
INTO l_color_method
FROM bsc_sys_datasets_b ds
,bsc_kpi_analysis_measures_b am
WHERE ds.dataset_id = am.dataset_id
AND am.indicator = p_objective_id
AND am.kpi_measure_id = p_kpi_measure_id;
FUNCTION update_actual_budget_for_mcc (
p_objective_id IN NUMBER
, p_kpi_measure_id IN NUMBER
, p_tab_id IN NUMBER
, p_lst_keys_insert IN VARCHAR2
) RETURN BOOLEAN
IS
TYPE t_cursor IS REF CURSOR;
l_lst_keys_insert VARCHAR2(100);
l_lst_keys_insert_array BSC_UPDATE_UTIL.t_array_of_varchar2;
l_lst_keys_array BSC_UPDATE_UTIL.t_array_of_varchar2;
l_lst_keys_insert := SUBSTR(p_lst_keys_insert, 1, instr(p_lst_keys_insert, ',', -1) - 1);
l_num_keys := BSC_UPDATE_UTIL.decompose_varchar2_list(l_lst_keys_insert, l_lst_keys_insert_array, ',');
FOR l_index IN 1 .. l_lst_keys_insert_array.COUNT LOOP
IF (l_lst_keys_insert_array(l_index) = '0') THEN
l_lst_keys_insert_array(l_index) := '''$#''';
l_lst_keys_insert := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(l_lst_keys_insert_array, l_num_keys);
l_sql_mcc := 'SELECT ' || l_lst_keys_insert || ', period, col_b.color_id FROM ( ' ||
' SELECT ' || l_lst_keys_insert || ', period , MAX(col_b.perf_sequence) mcc ' ||
' FROM bsc_tmp_colors tem_c, bsc_sys_colors_b col_b ' ||
' WHERE tem_c.color = col_b.color_id ' ||
' GROUP BY ' || l_lst_keys_insert || ' , period ' ||
' ) bsc_tmp_colors_mcc, bsc_sys_colors_b col_b ' ||
' WHERE bsc_tmp_colors_mcc.mcc = col_b.perf_sequence ';
l_sql_mcc_actual_plan := 'SELECT vreal, vplan, cumpercent ' ||
' FROM bsc_tmp_colors tem_c ' ||
' WHERE tem_c.color = ' || l_mcc_color;
FOR l_index IN 1 .. l_lst_keys_insert_array.COUNT LOOP
l_sql_mcc_actual_plan := l_sql_mcc_actual_plan ||
' AND ' || l_lst_keys_insert_array(l_index) || ' = DECODE(''' || l_lst_keys_array(l_index) || ''', ''$#'', ' || l_lst_keys_insert_array(l_index) || ', ''' || l_lst_keys_array(l_index) || ''') ';
UPDATE bsc_sys_kpi_colors
SET actual_data = l_mcc_real, budget_data = l_mcc_plan
WHERE tab_id = p_tab_id
AND indicator = p_objective_id
AND kpi_measure_id = p_kpi_measure_id
AND dim_level1 = DECODE(l_lst_keys_array(1), '$#', '0', l_lst_keys_array(1))
AND dim_level2 = DECODE(l_lst_keys_array(2), '$#', '0', l_lst_keys_array(2))
AND dim_level3 = DECODE(l_lst_keys_array(3), '$#', '0', l_lst_keys_array(3))
AND dim_level4 = DECODE(l_lst_keys_array(4), '$#', '0', l_lst_keys_array(4))
AND dim_level5 = DECODE(l_lst_keys_array(5), '$#', '0', l_lst_keys_array(5))
AND dim_level6 = DECODE(l_lst_keys_array(6), '$#', '0', l_lst_keys_array(6))
AND dim_level7 = DECODE(l_lst_keys_array(7), '$#', '0', l_lst_keys_array(7))
AND dim_level8 = DECODE(l_lst_keys_array(8), '$#', '0', l_lst_keys_array(8))
AND period_id = l_period;
x_source => 'BSC_UPDATE_COLOR.update_actual_budget_for_mcc');
END update_actual_budget_for_mcc;
x_source => 'BSC_UPDATE_COLOR.get_trend_flag');
PROCEDURE update_trend_for_comparison(
p_indic_code IN NUMBER,
p_kpi_measure_id IN NUMBER,
p_tab_id IN NUMBER,
p_lst_keys_insert IN VARCHAR2,
p_sql_mcc IN VARCHAR2
) IS
TYPE t_cursor IS REF CURSOR;
l_key_tbl BSC_UPDATE_COLOR.t_key_tbl_type;
h_sql_trend := ' SELECT '||p_lst_keys_insert||'PERIOD, COL_B.COLOR_ID '||
' FROM ('||p_sql_mcc||') BSC_TMP_COLORS_MCC, BSC_SYS_COLORS_B COL_B'||
' WHERE BSC_TMP_COLORS_MCC.MCC = COL_B.PERF_SEQUENCE '||
' ORDER BY PERIOD ';
h_actualvalue_sql := 'SELECT VREAL, nvl(key1,0)||nvl(key2,0)||nvl(key3,0)||nvl(key4,0)||nvl(key5,0)||nvl(key6,0)||nvl(key7,0)||nvl(key8,0) DV_COMB '||
' FROM BSC_TMP_COLORS '||
' WHERE PERIOD=:1 AND COLOR=:2 '||
' AND ('||h_key1||'=0 OR nvl(key1,0)='||h_key1||')'||
' AND ('||h_key2||'=0 OR nvl(key2,0)='||h_key2||')'||
' AND ('||h_key3||'=0 OR nvl(key3,0)='||h_key3||')'||
' AND ('||h_key4||'=0 OR nvl(key4,0)='||h_key4||')'||
' AND ('||h_key5||'=0 OR nvl(key5,0)='||h_key5||')'||
' AND ('||h_key6||'=0 OR nvl(key6,0)='||h_key6||')'||
' AND ('||h_key7||'=0 OR nvl(key7,0)='||h_key7||')'||
' AND ('||h_key8||'=0 OR nvl(key8,0)='||h_key8||')'||
' ORDER BY PERIOD, DV_COMB ';
h_match_sql := ' SELECT count(*) FROM bsc_sys_kpi_colors '||
' WHERE tab_id=:1 AND indicator=:2 AND kpi_measure_id=:3 '||
' AND PERIOD_ID=:4 AND KPI_COLOR=:5 '||
' AND ACTUAL_DATA=:6 '||
' AND ('||h_key1||'=0 OR nvl(DIM_LEVEL1,0)='||h_key1||')'||
' AND ('||h_key2||'=0 OR nvl(DIM_LEVEL2,0)='||h_key2||')'||
' AND ('||h_key3||'=0 OR nvl(DIM_LEVEL3,0)='||h_key3||')'||
' AND ('||h_key4||'=0 OR nvl(DIM_LEVEL4,0)='||h_key4||')'||
' AND ('||h_key5||'=0 OR nvl(DIM_LEVEL5,0)='||h_key5||')'||
' AND ('||h_key6||'=0 OR nvl(DIM_LEVEL6,0)='||h_key6||')'||
' AND ('||h_key7||'=0 OR nvl(DIM_LEVEL7,0)='||h_key7||')'||
' AND ('||h_key8||'=0 OR nvl(DIM_LEVEL8,0)='||h_key8||')';
h_priorvalue_sql := 'SELECT VREAL, nvl(key1,0)||nvl(key2,0)||nvl(key3,0)||nvl(key4,0)||nvl(key5,0)||nvl(key6,0)||nvl(key7,0)||nvl(key8,0) DV_COMB '||
' FROM BSC_TMP_COLORS '||
' WHERE PERIOD=:1 '||
' AND nvl(key1,0)||nvl(key2,0)||nvl(key3,0)||nvl(key4,0)||nvl(key5,0)||nvl(key6,0)||nvl(key7,0)||nvl(key8,0)=:2 '||
' ORDER BY PERIOD, DV_COMB ';
UPDATE bsc_sys_kpi_colors
SET kpi_trend = l_key_rec.trend
WHERE tab_id = p_tab_id
AND indicator = p_indic_code
AND kpi_measure_id = p_kpi_measure_id
AND period_id = l_key_rec.period
AND nvl(dim_level1,0)||nvl(dim_level2,0)||nvl(dim_level3,0)||nvl(dim_level4,0)||nvl(dim_level5,0)||nvl(dim_level6,0)||nvl(dim_level7,0)||nvl(dim_level8,0) = l_key_rec.dimvalues;
x_source => 'BSC_UPDATE_COLOR.update_trend_for_comparison');
END update_trend_for_comparison;
x_dim_combination IN BSC_UPDATE_UTIL.t_array_of_number,
x_num_families IN NUMBER,
x_periodicity_id IN NUMBER,
x_comp_level_pk_col IN VARCHAR2,
x_dim_set_id IN NUMBER,
x_color_by_total IN NUMBER,
x_measure_formula IN VARCHAR2,
x_current_fy IN NUMBER,
x_aw_flag IN BOOLEAN -- AW_INTEGRATION: need this new parameter
)
RETURN BOOLEAN IS
e_unexpected_error EXCEPTION;
h_dim_com_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
h_dim_com_keys_1 BSC_UPDATE_UTIL.t_array_of_varchar2;
h_arr_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
h_key_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
h_lst_select VARCHAR2(2000);
h_lst_keys_insert VARCHAR2(2000);
h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_varchar2;
h_bind_vars_values_n BSC_UPDATE_UTIL.t_array_of_number;
SELECT column_name
FROM bsc_db_tables_cols
WHERE table_name = p_table_name AND column_type = p_column_type;
h_lst_select := NULL;
h_lst_keys_insert := NULL;
h_aw_limit_tbl.delete;
h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity_id);
h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_periodicity_id);
h_sql := 'SELECT FAMILY_INDEX, LEVEL_PK_COL'||
' FROM BSC_TMP_TAB_DEF'||
' WHERE TAB_ID = :1'||
' AND ('||h_where||')'||
' ORDER BY FAMILY_INDEX';
select min(year), max(year)
into h_min_per, h_max_per
from bsc_db_calendar
where calendar_id = h_calendar_id;
h_sql := 'select min('||BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity_id)||'),'||
' max('||BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity_id)||')'||
' from bsc_db_calendar'||
' where calendar_id = :1 and year = :2';
BSC_UPDATE_LOG.Write_Line_log(h_aw_limit_rec.parameter_name||' '||
h_aw_limit_rec.parameter_value||' '||
h_aw_limit_rec.dimension, BSC_UPDATE_LOG.OUTPUT);
h_sql := 'SELECT DISTINCT mv_name, data_source, sql_stmt'||
' FROM bsc_kpi_data_tables'||
' WHERE indicator = :1 AND periodicity_id = :2'||
' AND dim_set_id = :3 AND table_name = :4';
h_sql := 'SELECT 1 FROM '||h_mv_name||
' WHERE '||h_condition_b||' AND ROWNUM = 1';
h_sql := 'SELECT 1 FROM ('||h_sql_stmt||') F'||
' WHERE '||h_condition_b||' AND ROWNUM = 1';
' SELECT '||h_lst_tab_columns||
' FROM '||h_mv_name;
' SELECT '||h_lst_tab_columns||
' FROM ('||h_sql_stmt||')';
h_sql := 'SELECT mv_name, data_source, sql_stmt'||
' FROM bsc_kpi_data_tables'||
' WHERE indicator = :1 AND periodicity_id = :2'||
' AND dim_set_id = :3 AND level_comb = :4';
h_lst_keys := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(h_arr_keys, h_num_keys);
h_lst_keys_temp := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', h_num_keys);
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_DATA_COLOR');
h_sql := 'INSERT /*+ append */ INTO BSC_TMP_DATA_COLOR ('||h_lst_keys_temp||'PERIOD, TYPE, TOTAL)'||
' SELECT '||h_lst_keys||'YEAR AS PERIOD, TYPE, '||x_measure_formula||' AS TOTAL';
h_sql := 'INSERT /*+ append */ INTO BSC_TMP_DATA_COLOR ('||h_lst_keys_temp||'PERIOD, TYPE, TOTAL)'||
' SELECT '||h_lst_keys||'PERIOD, TYPE, '||x_measure_formula||' AS TOTAL';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars_1);
l_ytd_flag := BSC_UPDATE_UTIL.get_ytd_flag(x_indic_code, x_kpi_measure_id);
h_lst_where := BSC_UPDATE_UTIL.Make_Lst_Cond_Join('B', h_arr_keys, 'A', h_key_columns_temp, h_num_keys, 'AND');
h_sql := 'UPDATE BSC_TMP_DATA_COLOR A'||
' SET TOTAL = ('||
' SELECT '||x_measure_formula;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars_1);
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_COLORS');
h_sql := 'INSERT /*+ append */ INTO BSC_TMP_COLORS ('||h_lst_keys_temp||'PERIOD, VPLAN, VREAL, CUMPERCENT, COLOR)'||
' SELECT '||h_lst_keys_temp||'BSC_TMP_ALL_PERIODS.PERIOD, '||
' NULL AS VPLAN, NULL AS VREAL, NULL AS CUMPERCENT, '||GRAY||' AS COLOR'||
' FROM BSC_TMP_DATA_COLOR, BSC_TMP_ALL_PERIODS'||
' GROUP BY '||h_lst_keys_temp||'BSC_TMP_ALL_PERIODS.PERIOD';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
h_lst_where := BSC_UPDATE_UTIL.Make_Lst_Cond_Join('BSC_TMP_COLORS', h_key_columns_temp, 'BSC_TMP_DATA_COLOR', h_key_columns_temp, h_num_keys, 'AND');
h_sql := 'UPDATE BSC_TMP_COLORS'||
' SET VPLAN = ('||
' SELECT TOTAL'||
' FROM BSC_TMP_DATA_COLOR'||
' WHERE '||h_lst_where||' BSC_TMP_COLORS.PERIOD = BSC_TMP_DATA_COLOR.PERIOD'||
' AND BSC_TMP_DATA_COLOR.TYPE = 1'||
')'||
' WHERE 0 = ('||
' SELECT 0'||
' FROM BSC_TMP_DATA_COLOR'||
' WHERE '||h_lst_where||' BSC_TMP_COLORS.PERIOD = BSC_TMP_DATA_COLOR.PERIOD'||
' AND BSC_TMP_DATA_COLOR.TYPE = 1'||
')';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
h_sql := 'UPDATE BSC_TMP_COLORS'||
' SET VREAL = ('||
' SELECT TOTAL'||
' FROM BSC_TMP_DATA_COLOR'||
' WHERE '||h_lst_where||' BSC_TMP_COLORS.PERIOD = BSC_TMP_DATA_COLOR.PERIOD'||
' AND BSC_TMP_DATA_COLOR.TYPE = 0'||
')'||
' WHERE 0 = ('||
' SELECT 0'||
' FROM BSC_TMP_DATA_COLOR'||
' WHERE '||h_lst_where||' BSC_TMP_COLORS.PERIOD = BSC_TMP_DATA_COLOR.PERIOD'||
' AND BSC_TMP_DATA_COLOR.TYPE = 0'||
')';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
h_lst_keys_insert := NULL;
h_sql := 'SELECT DIM_LEVEL_INDEX, LEVEL_PK_COL'||
' FROM BSC_TMP_TAB_DEF'||
' WHERE TAB_ID = :1'||
' ORDER BY DIM_LEVEL_INDEX';
IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_level_pk_col, h_arr_keys, h_num_keys) THEN
h_lst_keys_insert := h_lst_keys_insert||'KEY'||h_key_index||', ';
h_lst_keys_insert := h_lst_keys_insert||'0, ';
h_lst_keys_insert := h_lst_keys_insert||'KEY'||h_key_index||', ';
h_lst_keys_insert := h_lst_keys_insert||'0, ';
h_lst_select := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', x_num_families);
IF h_lst_select IS NOT NULL THEN
h_lst_select := h_lst_select||', ';
h_sql_mcc := 'SELECT '||h_lst_select||'PERIOD, '||
' MAX(COL_B.PERF_SEQUENCE) MCC'||
' FROM BSC_TMP_COLORS TEM_C, BSC_SYS_COLORS_B COL_B'||
' WHERE TEM_C.COLOR = COL_B.COLOR_ID'||
' GROUP BY '||h_lst_select||'PERIOD';
h_sql := 'INSERT INTO BSC_SYS_KPI_COLORS (TAB_ID, INDICATOR, KPI_MEASURE_ID, DIM_LEVEL1, DIM_LEVEL2,'||
' DIM_LEVEL3, DIM_LEVEL4, DIM_LEVEL5, DIM_LEVEL6, DIM_LEVEL7, DIM_LEVEL8,'||
' PERIOD_ID, KPI_COLOR, USER_COLOR)'||
' SELECT :1, :2, :3, '||h_lst_keys_insert||'PERIOD,'|| ' COL_B.COLOR_ID, COL_B.COLOR_ID ' ||
' FROM ('||h_sql_mcc||') BSC_TMP_COLORS_MCC, BSC_SYS_COLORS_B COL_B'||
' WHERE BSC_TMP_COLORS_MCC.MCC = COL_B.PERF_SEQUENCE';
h_bind_vars_values_n.delete;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values_n, h_num_bind_vars_n);
IF NOT update_actual_budget_for_mcc( p_objective_id => x_indic_code
, p_kpi_measure_id => x_kpi_measure_id
, p_tab_id => x_tab_id
, p_lst_keys_insert => h_lst_keys_insert
) THEN
RAISE e_unexpected_error;
update_trend_for_comparison( p_indic_code => x_indic_code
, p_kpi_measure_id => x_kpi_measure_id
, p_tab_id => x_tab_id
, p_lst_keys_insert => h_lst_keys_insert
, p_sql_mcc => h_sql_mcc);
h_sql := 'INSERT /*+ append */ INTO BSC_SYS_KPI_COLORS (TAB_ID, INDICATOR, KPI_MEASURE_ID, DIM_LEVEL1, DIM_LEVEL2,'||
' DIM_LEVEL3, DIM_LEVEL4, DIM_LEVEL5, DIM_LEVEL6, DIM_LEVEL7, DIM_LEVEL8,'||
' PERIOD_ID, KPI_COLOR, USER_COLOR, ACTUAL_DATA, BUDGET_DATA)'||
' SELECT :1, :2, :3, '||h_lst_keys_insert||'PERIOD,'||
' COLOR, COLOR, VREAL, VPLAN'||
' FROM BSC_TMP_COLORS';
h_bind_vars_values_n.delete;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values_n, h_num_bind_vars_n);
h_sql := 'SELECT DIM_LEVEL_INDEX, LEVEL_PK_COL, LEVEL_TABLE_NAME'||
' FROM BSC_TMP_TAB_DEF'||
' WHERE TAB_ID = :1'||
' AND FAMILY_INDEX = :2'||
' AND DIM_INDEX = :3';
h_sql := 'SELECT DIM_LEVEL_INDEX, LEVEL_PK_COL, LEVEL_TABLE_NAME'||
' FROM BSC_TMP_TAB_DEF'||
' WHERE TAB_ID = :1'||
' AND FAMILY_INDEX = :2'||
' AND DIM_INDEX = :3';
h_sql := 'UPDATE BSC_SYS_KPI_COLORS'||
' SET DIM_LEVEL'||(h_dim_level_index_parent + 1)||' = ('||
' SELECT '||h_level_pk_col_parent||
' FROM '||h_level_table_name_child||
' WHERE BSC_SYS_KPI_COLORS.DIM_LEVEL'||(h_dim_level_index_child + 1)||
' = '||h_level_table_name_child||'.CODE'||
')'||
' WHERE TAB_ID = :1 AND '||
' INDICATOR = :2 AND '||
' DIM_LEVEL'||(h_dim_level_index_child + 1)||' <> ''0''';
h_bind_vars_values_n.delete;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values_n, h_num_bind_vars_n);
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_DIMDATA_NOT_FOUND'),
x_source => 'BSC_UPDATE_COLOR.Color_Indic_Dim_Combination');
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_KPICOLOR_FAILED'),
x_source => 'BSC_UPDATE_COLOR.Color_Indic_Dim_Combination');
x_source => 'BSC_UPDATE_COLOR.Color_Indic_Dim_Combination');
x_source => 'BSC_UPDATE_COLOR.Color_Indic_Dim_Combination');
x_source => 'BSC_UPDATE_COLOR.Color_Indic_Dim_Combination');
x_source => 'BSC_UPDATE_COLOR.Color_Indic_Dim_Combination');
x_source => 'BSC_UPDATE_COLOR.Color_Indic_Dim_Combination');
x_source => 'BSC_UPDATE_COLOR.Color_Indic_Dim_Combination');
h_bind_vars_values_n BSC_UPDATE_UTIL.t_array_of_number;
SELECT COLOR_ID, PERF_SEQUENCE, COLOR
FROM bsc_sys_colors_b;
h_sql := 'UPDATE BSC_TMP_COLORS'||
' SET VREAL = DECODE(VREAL, NULL, VREAL, TRUNC((VREAL + '||C_ALFA||') * '||C_BETA||') / '||C_BETA||'), '||
' VPLAN = DECODE(VPLAN, NULL, VPLAN, TRUNC((VPLAN + '||C_ALFA||') * '||C_BETA||') / '||C_BETA||')';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
h_sql := 'UPDATE BSC_TMP_COLORS'||
' SET CUMPERCENT = 100'||
' WHERE (VREAL = 0) AND (VPLAN = 0)';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
h_sql := 'UPDATE BSC_TMP_COLORS'||
' SET CUMPERCENT = TRUNC((((VREAL / VPLAN)*100) + '||C_ALFA||') * '||C_BETA||') / '||C_BETA||
' WHERE (VREAL IS NOT NULL) AND (VPLAN > 0)';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
h_sql := 'UPDATE BSC_TMP_COLORS'||
' SET CUMPERCENT = TRUNC((((2 + ABS(VREAL / VPLAN))*100) + '||C_ALFA||') * '||C_BETA||') / '||C_BETA||
' WHERE (VREAL > 0) AND (VPLAN < 0)';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
h_sql := 'UPDATE BSC_TMP_COLORS'||
' SET CUMPERCENT = TRUNC((((2 - (VREAL / VPLAN))*100) + '||C_ALFA||') * '||C_BETA||') / '||C_BETA||
' WHERE (VREAL <= 0) AND (VPLAN < 0)';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
h_sql := 'UPDATE BSC_TMP_COLORS'||
' SET COLOR = CASE WHEN VREAL > VPLAN THEN '||max_perf_color_id||
' ELSE '||min_perf_color_id||' END '||
' WHERE (VREAL = 0 OR VPLAN = 0) '||
' AND VREAL <> VPLAN';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
h_sql := 'UPDATE BSC_TMP_COLORS'||
' SET COLOR = CASE WHEN VREAL > VPLAN THEN '||max_perf_color_id||
' ELSE '||min_perf_color_id||' END '||
' WHERE (VREAL = 0 OR VPLAN = 0) '||
' AND VREAL <> VPLAN';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
h_sql := 'UPDATE BSC_TMP_COLORS'||
' SET COLOR = '||min_perf_color_id||
' WHERE ((VREAL = 0) AND (VPLAN <> 0)) OR ((VREAL <> 0) AND (VPLAN = 0))';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
h_bind_vars_values_n.delete;
h_sql := 'UPDATE BSC_TMP_COLORS'||
' SET COLOR = CASE';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values_n, h_num_bind_vars_n);
x_source => 'BSC_UPDATE_COLOR.Calculate_KPI_Color');
SELECT kpi_measure_id
FROM bsc_kpi_measure_props kpi_meas
WHERE kpi_meas.indicator = p_indicator;
x_source => 'BSC_UPDATE_COLOR.calculate_trend_icon');
SELECT ti.tab_id,
kpi.periodicity_id,
--kpi.apply_color_flag,
kpi.indicator_type,
kpi.config_type
FROM bsc_tab_indicators ti,
bsc_kpis_b kpi
WHERE ti.indicator = kpi.indicator
AND kpi.prototype_flag <> 2
AND kpi.indicator = p_indicator;
SELECT indicator_type, config_type
FROM bsc_kpis_b
WHERE indicator = pIndicator ;
SELECT property_value
FROM bsc_kpi_properties
WHERE indicator = pIndicator
AND property_code = pPropertyCode ;
h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
IF BSC_UPDATE_UTIL.Get_Kpi_Impl_Type(x_indic_code) = 2 THEN
h_aw_flag := TRUE;
h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(l_objective_color_props.periodicity_id);
h_calendar_edw_flag := BSC_UPDATE_UTIL.Get_Calendar_EDW_Flag(h_calendar_id);
h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(l_objective_color_props.periodicity_id);
h_current_fy := BSC_UPDATE_UTIL.Get_Calendar_Fiscal_Year(h_calendar_id);
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_ALL_PERIODS');
IF NOT BSC_UPDATE_UTIL.Get_Indic_Range_Of_Years(x_indic_code,
l_objective_color_props.periodicity_id,
h_num_of_years,
h_previous_years) THEN
RAISE e_unexpected_error;
h_sql := 'INSERT /*+ append */ INTO BSC_TMP_ALL_PERIODS (PERIOD)'||
' VALUES (:1)';
h_bind_vars_values.delete;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
h_sql := 'INSERT /*+ append */ INTO BSC_TMP_ALL_PERIODS'||
' SELECT DISTINCT ' || BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(l_objective_color_props.periodicity_id) ||
' AS PERIOD'||
' FROM BSC_DB_CALENDAR'||
' WHERE YEAR = :1 AND CALENDAR_ID = :2';
h_bind_vars_values.delete;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
h_sql := 'INSERT /*+ append */ INTO BSC_TMP_ALL_PERIODS (PERIOD)'||
' VALUES (:1)';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
DELETE FROM bsc_sys_kpi_colors
WHERE tab_id = l_objective_color_props.tab_id AND indicator = x_indic_code;
DELETE FROM bsc_sys_objective_colors
WHERE tab_id = l_objective_color_props.tab_id AND indicator = x_indic_code;
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_KPICOLOR_FAILED'),
x_source => 'BSC_UPDATE_COLOR.Color_Indicator');
x_source => 'BSC_UPDATE_COLOR.Color_Indicator');
SELECT tab_id, dim_level_index, parent_level_index
FROM bsc_sys_com_dim_levels
ORDER BY tab_id, dim_level_index;
h_num_dimensions_by_family BSC_UPDATE_UTIL.t_array_of_number;
h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
h_table_columns BSC_UPDATE_UTIL.t_array_temp_table_cols;
h_table_columns.delete;
IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
RAISE e_unexpected_error;
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TAB_DEF');
h_sql := 'INSERT /*+ append */ INTO BSC_TMP_TAB_DEF'||
' SELECT C.TAB_ID, C.DIM_LEVEL_INDEX,'||
' D.LEVEL_PK_COL, D.LEVEL_VIEW_NAME,'||
' 0 AS FAMILY_INDEX, 0 AS DIM_INDEX'||
' FROM BSC_SYS_COM_DIM_LEVELS C, BSC_SYS_DIM_LEVELS_B D'||
' WHERE C.DIM_LEVEL_ID = D.DIM_LEVEL_ID';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
h_sql := 'UPDATE BSC_TMP_TAB_DEF'||
' SET FAMILY_INDEX = :1,'||
' DIM_INDEX = :2'||
' WHERE TAB_ID = :3'||
' AND DIM_LEVEL_INDEX = :4';
h_bind_vars_values.delete;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
h_table_columns.delete;
IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
RAISE e_unexpected_error;
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TAB_COM');
h_sql := 'SELECT TAB_ID, FAMILY_INDEX, COUNT(DIM_INDEX)'||
' FROM BSC_TMP_TAB_DEF'||
' GROUP BY TAB_ID, FAMILY_INDEX'||
' ORDER BY TAB_ID, FAMILY_INDEX';
IF NOT Insert_Tab_Combinations(h_last_tab_id, h_num_dimensions_by_family, h_max_family_index) THEN
RAISE e_unexpected_error;
IF NOT Insert_Tab_Combinations(h_last_tab_id, h_num_dimensions_by_family, h_max_family_index) THEN
RAISE e_unexpected_error;
h_table_columns.delete;
IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
RAISE e_unexpected_error;
h_table_columns.delete;
IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
RAISE e_unexpected_error;
h_table_columns.delete;
IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
RAISE e_unexpected_error;
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_TEMP_TABTABLES_FAILED'),
x_source => 'BSC_UPDATE_COLOR.Create_Temp_Tab_Tables');
x_source => 'BSC_UPDATE_COLOR.Create_Temp_Tab_Tables');
x_source => 'BSC_UPDATE_COLOR.Drop_Temp_Tab_Tables');
x_source => 'BSC_UPDATE_COLOR.Drop_Temp_Tab_Tables');
x_dim_combination IN BSC_UPDATE_UTIL.t_array_of_number,
x_dim_com_keys IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_families IN NUMBER,
x_comp_level_pk_col IN VARCHAR2,
x_color_by_total IN NUMBER,
x_condition OUT NOCOPY VARCHAR2,
x_bind_vars_values OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_bind_vars OUT NOCOPY NUMBER,
x_aw_limit_tbl IN OUT NOCOPY BIS_PMV_PAGE_PARAMETER_TBL --AW_INTEGRATION: new parameter
) RETURN BOOLEAN IS
-- BSC-BIS-DIMENSIONS: I have changed the type of x_bind_vars_values to use VARCHAR2
-- This is to support NUMBER/VHARCHAR2 in key columns
e_unexpected_error EXCEPTION;
SELECT e.level_view_name, r.relation_col
FROM bsc_sys_dim_levels_b e, bsc_sys_dim_level_rels r
WHERE e.dim_level_id = r.dim_level_id AND
e.level_pk_col = pLevelPkCol ;
SELECT column_name
FROM bsc_db_tables_cols
WHERE table_name = pTableName
AND column_type = pColType ;
SELECT default_key_value
FROM bsc_kpi_dim_levels_b
WHERE indicator = pIndicator
AND dim_set_id = pDimSetId
AND level_pk_col = pLevelPkCol
AND default_key_value IS NOT NULL ;
h_sql := 'SELECT CODE'||
' FROM '||h_pl_account_table_name||
' WHERE '||h_pl_type_of_account_key||' = :1';
h_aw_limit_rec.parameter_name := BSC_UPDATE_UTIL.Get_Dim_Level_Table_Name(x_comp_level_pk_col);
h_aw_limit_rec.parameter_name := BSC_UPDATE_UTIL.Get_Dim_Level_Table_Name(h_table_key);
h_aw_limit_rec.parameter_name := BSC_UPDATE_UTIL.Get_Dim_Level_Table_Name(h_table_key);
h_aw_limit_rec.parameter_name := BSC_UPDATE_UTIL.Get_Dim_Level_Table_Name(h_table_key);
h_aw_limit_rec.parameter_name := BSC_UPDATE_UTIL.Get_Dim_Level_Table_Name(h_table_key);
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_COLORTABLE_COND_FAILED'),
x_source => 'BSC_UPDATE_COLOR.Get_Condition_On_Color_Table');
x_source => 'BSC_UPDATE_COLOR.Get_Condition_On_Color_Table');
SELECT level_comb, table_name
FROM bsc_kpi_data_tables
WHERE indicator = pIndicator
AND periodicity_id = pPeriodicity
AND dim_set_id = pDimSetId
AND table_name IS NOT NULL;
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_RETR_COLORTABLE_FAILED'),
x_source => 'BSC_UPDATE_COLOR.Get_Table_For_Drill_Comb');
x_source => 'BSC_UPDATE_COLOR.Get_Table_For_Drill_Comb');
x_selected_dim_keys IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_selected_dim_keys IN NUMBER,
x_level_comb OUT NOCOPY VARCHAR2
) RETURN VARCHAR2 IS
e_unexpected_error EXCEPTION;
SELECT dim_level_index
FROM bsc_kpi_dim_levels_b
WHERE indicator = pIndicator
AND dim_set_id = pDimSetId
AND level_pk_col = pLevelPkCol;
SELECT dim_level_index
FROM bsc_kpi_dim_levels_b
WHERE indicator = pIndicator
AND dim_set_id = pDimSetId
AND status = pStatus
AND default_key_value IS NOT NULL ;
SELECT dim_level_index
FROM bsc_kpi_dim_levels_b
WHERE indicator = pIndicator
AND dim_set_id = pDImSetId
AND status = pStatus
ORDER BY dim_level_index ;
h_selected_drills BSC_UPDATE_UTIL.t_array_of_number;
h_num_selected_drills NUMBER;
h_num_selected_drills := 0;
FOR h_i IN 0 .. x_num_selected_dim_keys - 1 LOOP
h_level_pk_col := x_selected_dim_keys(h_i);
h_num_selected_drills := h_num_selected_drills + 1;
h_selected_drills(h_num_selected_drills):= h_dim_level_index;
h_num_selected_drills := h_num_selected_drills + 1;
h_selected_drills(h_num_selected_drills) := h_dim_level_index;
IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Number(h_dim_level_index,
h_selected_drills,
h_num_selected_drills) THEN
h_num_selected_drills := h_num_selected_drills + 1;
h_selected_drills(h_num_selected_drills) := h_dim_level_index;
IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Number(h_dim_level_index,
h_selected_drills,
h_num_selected_drills) THEN
h_drill_comb := h_drill_comb||'0';
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_RETR_COLORTABLE_FAILED'),
x_source => 'BSC_UPDATE_COLOR.Get_Table_Used_To_Color');
x_source => 'BSC_UPDATE_COLOR.Get_Table_Used_To_Color');
| FUNCTION Insert_Tab_Combinations
+============================================================================*/
FUNCTION Insert_Tab_Combinations(
x_tab_id IN NUMBER,
x_num_dimensions_by_family IN BSC_UPDATE_UTIL.t_array_of_number,
x_max_family_index IN NUMBER
) RETURN BOOLEAN IS
h_num_combinations NUMBER;
h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
h_sql := 'INSERT /*+ append */ INTO BSC_TMP_TAB_COM (TAB_ID, COM_INDEX, FAMILY_INDEX, DIM_INDEX)'||
' VALUES (:1, :2, :3, :4)';
h_bind_vars_values.delete;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
x_source => 'BSC_UPDATE_COLOR.Insert_Tab_Combinations');
END Insert_Tab_Combinations;
SELECT PROPERTY_VALUE
FROM BSC_KPI_PROPERTIES
WHERE INDICATOR = x_indicator
AND PROPERTY_CODE = x_property_code;
p_objective_color_rec IN BSC_UPDATE_COLOR.t_objective_color_rec
,x_kpi_measure_id OUT NOCOPY NUMBER
,x_color_flag OUT NOCOPY BOOLEAN
) IS
l_rollup_type bsc_kpis_b.color_rollup_type%TYPE;
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_KPICOLOR_FAILED'),
x_source => 'BSC_UPDATE_COLOR.Calculate_Objective_Color');
x_source => 'BSC_UPDATE_COLOR.Calculate_Objective_Color');
SELECT DISTINCT
dim_level1 || dim_level2 || dim_level3 || dim_level4 || dim_level5 || dim_level6 || dim_level7 || dim_level8 dim_comb,
dim_level1, dim_level2, dim_level3, dim_level4, dim_level5, dim_level6, dim_level7, dim_level8,
period_id, actual_data, budget_data, kpi_color, kpi_trend
FROM bsc_sys_kpi_colors
WHERE indicator = x_indicator
AND tab_id = x_tab_id
AND kpi_measure_id = x_measure_id
ORDER BY dim_comb,period_id;
SELECT DISTINCT
dim_level1 || dim_level2 || dim_level3 || dim_level4 || dim_level5 || dim_level6 || dim_level7 || dim_level8 dim_comb,
period_id, obj_color, obj_trend
FROM bsc_sys_objective_colors
WHERE indicator = x_indicator
AND tab_id = x_tab_id
AND dim_level1 || dim_level2 || dim_level3 || dim_level4 || dim_level5 || dim_level6 || dim_level7 || dim_level8 = cp_dim_comb
AND period_id = cp_period_id
ORDER BY dim_comb, period_id;
h_bind_vars_values_n BSC_UPDATE_UTIL.t_array_of_number;
h_obj_bind_vars_values_n BSC_UPDATE_UTIL.t_array_of_number;
l_kpi_dim_props BSC_UPDATE_UTIL.t_kpi_dim_props_rec;
BSC_UPDATE_UTIL.Get_Kpi_Dim_Props ( p_objective_id => x_indicator
, p_kpi_measure_id => x_measure_id
, x_dim_props_rec => l_kpi_dim_props
);
l_color_by_total := BSC_UPDATE_UTIL.Get_Color_By_Total ( p_objective_id => x_indicator
, p_kpi_measure_id => x_measure_id
);
h_sql := 'UPDATE bsc_sys_kpi_colors'||
' SET kpi_trend = '||l_trendflag||
' WHERE indicator =:1 '||
' AND tab_id = :2 '||
' AND kpi_measure_id = :3 '||
' AND period_id = :4 '||
' AND dim_level1 || dim_level2 || dim_level3 || dim_level4 || dim_level5 || dim_level6 || dim_level7 || dim_level8 = :5';
h_bind_vars_values_n.delete;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values_n, h_num_bind_vars_n);
h_obj_sql := 'UPDATE bsc_sys_objective_colors'||
' SET obj_trend = '||l_trendflag||
' WHERE indicator =:1 '||
' AND tab_id = :2 '||
' AND period_id = :3 '||
' AND dim_level1 || dim_level2 || dim_level3 || dim_level4 || dim_level5 || dim_level6 || dim_level7 || dim_level8 = :4';
h_obj_bind_vars_values_n.delete;
BSC_UPDATE_UTIL.Execute_Immediate(h_obj_sql, h_obj_bind_vars_values_n, h_obj_num_bind_vars_n);
x_source => 'BSC_UPDATE_COLOR.calculate_kpi_trend_icon');