The following lines contain the word 'select', 'insert', 'update' or 'delete':
h_new_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(x_tables(h_i), x_purge_tables, x_num_purge_tables) THEN
x_num_purge_tables := x_num_purge_tables + 1;
h_sql := 'SELECT table_name';
IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_table_name, x_purge_tables, x_num_purge_tables) THEN
x_num_purge_tables := x_num_purge_tables + 1;
h_sql := 'SELECT source_table_name';
IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_table_name, x_purge_tables, x_num_purge_tables) THEN
x_num_purge_tables := x_num_purge_tables + 1;
h_sql := 'SELECT table_name';
h_sql := h_sql||' SELECT indicator';
IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_table_name, x_purge_tables, x_num_purge_tables) THEN
x_num_purge_tables := x_num_purge_tables + 1;
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_ADD_REL_TABLES_FAILED'),
x_source => 'BSC_UPDATE_INC.Add_Related_Tables');
x_source => 'BSC_UPDATE_INC.Add_Related_Tables');
h_input_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
h_color_indicators BSC_UPDATE_UTIL.t_array_of_number;
h_changed_calendars BSC_UPDATE_UTIL.t_array_of_number;
BSC_UPDATE_LOG.Write_Line_log(BSC_UPDATE_UTIL.Get_Message('BSC_INCR_CHANGES_REVISION'), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_log(BSC_UPDATE_UTIL.Get_Message('BSC_FISCAL_YEAR_CHANGE'), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'CALENDAR_NAME')||
BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||' '||
BSC_UPDATE_UTIL.Get_Calendar_Name(h_calendar_id), BSC_UPDATE_LOG.OUTPUT);
IF BSC_UPDATE_UTIL.Get_Calendar_EDW_Flag(h_calendar_id) = 0 THEN
-- This is just for BSC Calendars
--LOCKING: Lock the calendar
IF NOT BSC_UPDATE_LOCK.Lock_Calendar(h_calendar_id) THEN
RAISE e_could_not_get_lock;
IF BSC_UPDATE_UTIL.Get_Calendar_Source(h_calendar_id) = 'BSC' THEN
BSC_UPDATE_LOG.Write_Line_log(BSC_UPDATE_UTIL.Get_Message('BSC_CALTABLES_INIT')||
' ('||BSC_UPDATE_UTIL.Get_Calendar_Name(h_calendar_id)||')', BSC_UPDATE_LOG.OUTPUT);
IF NOT BSC_UPDATE_UTIL.Init_Calendar_Tables_AT(h_calendar_id) THEN
RAISE e_unexpected_error;
BSC_UPDATE_LOG.Write_Line_log(BSC_UPDATE_UTIL.Get_Message('BSC_CALTABLES_INITIALIZED'), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_UTIL.Load_Calendar_Into_AW_AT(h_calendar_id);
IF NOT BSC_UPDATE_LOCK.Lock_Calendar(h_calendar_id) THEN
RAISE e_could_not_get_lock;
UPDATE bsc_sys_calendars_b
SET fiscal_change = 0
WHERE calendar_id = h_calendar_id;
IF NOT BSC_UPDATE_LOCK.Lock_Prototype_Indicators(h_calendar_id) THEN
RAISE e_could_not_get_lock;
UPDATE bsc_kpis_b
SET prototype_flag = 0, last_update_date = SYSDATE
WHERE prototype_flag IN (6, 7) AND calendar_id = h_calendar_id;
UPDATE bsc_kpi_analysis_measures_b
SET prototype_flag = 7
WHERE indicator IN (SELECT indicator FROM bsc_kpis_b WHERE calendar_id = h_calendar_id);
BSC_UPDATE_LOG.Write_Line_log(BSC_UPDATE_UTIL.Get_Message('BSC_SUMTAB_RECALC_REQUIRED'),
BSC_UPDATE_LOG.OUTPUT);
IF NOT BSC_UPDATE.Process_Input_Tables(h_input_tables, h_num_input_tables, 1) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_LOCK.Lock_Temp_Tables('COLOR') THEN
RAISE e_could_not_get_lock;
IF NOT BSC_UPDATE_COLOR.Create_Temp_Tab_Tables_AT() THEN
RAISE e_unexpected_error;
BSC_UPDATE_LOG.Write_Line_log(BSC_UPDATE_UTIL.Get_Message('BSC_KPICOLOR_RECALC_REQUIRED'),
BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_log(BSC_UPDATE_UTIL.Get_Message('BSC_COLOR_CALC')||' '||h_color_indicators(h_i),
BSC_UPDATE_LOG.OUTPUT);
IF NOT BSC_UPDATE_LOCK.Lock_Color_Indicator(h_color_indicators(h_i)) THEN
RAISE e_could_not_get_lock;
IF NOT BSC_UPDATE_COLOR.Color_Indicator_AT(h_color_indicators(h_i)) THEN
RAISE e_unexpected_error;
h_message := BSC_UPDATE_UTIL.Get_Message('BSC_COLOR_CALC_COMPLETED');
h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'INDICATOR', TO_CHAR(h_color_indicators(h_i)));
BSC_UPDATE_LOG.Write_Line_log(h_message,
BSC_UPDATE_LOG.OUTPUT);
IF NOT BSC_UPDATE_LOCK.Lock_Period_Indicator(h_color_indicators(h_i)) THEN
RAISE e_could_not_get_lock;
IF NOT BSC_UPDATE_UTIL.Update_Kpi_Period_Name(h_color_indicators(h_i)) THEN
RAISE e_unexpected_error;
BSC_UPDATE_UTIL.Update_Kpi_Time_Stamp(h_color_indicators(h_i));
BSC_UPDATE_UTIL.Update_Kpi_Tab_Time_Stamp(h_color_indicators(h_i));
IF NOT BSC_UPDATE_LOCK.Lock_Prototype_Indicators THEN
RAISE e_could_not_get_lock;
BSC_UPDATE_LOG.Write_Line_log(BSC_UPDATE_UTIL.Get_Message('BSC_INCR_CHANGES_VERIF'), BSC_UPDATE_LOG.OUTPUT);
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_INCR_CHANGE_REV_FAILED'),
x_source => 'BSC_UPDATE_INC.Do_Incremental');
x_source => 'BSC_UPDATE_INC.Do_Incremental');
x_source => 'BSC_UPDATE_INC.Do_Incremental');
x_source => 'BSC_UPDATE_INC.Do_Incremental');
x_changed_calendars IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
x_num_changed_calendars IN OUT NOCOPY NUMBER
) RETURN BOOLEAN IS
TYPE t_cursor IS REF CURSOR;
SELECT calendar_id
FROM bsc_sys_calendars_b
WHERE fiscal_change = pFiscalChg ;
x_source => 'BSC_UPDATE_INC.Get_Changed_Calendars');
x_color_indicators IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
x_num_color_indicators IN OUT NOCOPY NUMBER
) RETURN BOOLEAN IS
TYPE t_cursor IS REF CURSOR;
h_sql := 'SELECT DISTINCT indicator ' ||
'FROM bsc_kpis_b obj ' ||
'WHERE ( obj.prototype_flag = :1 ' ||
'OR ( obj.prototype_flag = :2 AND EXISTS ( ' ||
'SELECT 1 FROM bsc_kpi_analysis_measures_b kpi_meas ' ||
'WHERE kpi_meas.indicator = obj.indicator ' ||
'AND kpi_meas.prototype_flag = :3 ' ||
')' ||
')' ||
') ';
IF BSC_UPDATE.g_kpi_mode THEN
IF BSC_UPDATE.g_num_indicators > 0 THEN
h_where_indics := BSC_APPS.Get_New_Big_In_Cond_Number(1, 'indicator');
FOR h_i IN 1 .. BSC_UPDATE.g_num_indicators LOOP
BSC_APPS.Add_Value_Big_In_Cond(1, BSC_UPDATE.g_indicators(h_i));
x_source => 'BSC_UPDATE_INC.Get_Color_Indics_Incremental');
x_input_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_input_tables IN OUT NOCOPY NUMBER,
x_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_tables IN NUMBER
) RETURN BOOLEAN IS
e_unexpected_error EXCEPTION;
h_new_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
SELECT source_table_name
FROM bsc_db_tables_rels
WHERE table_name = pTableName ;
IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_table, x_input_tables, x_num_input_tables) THEN
x_num_input_tables := x_num_input_tables + 1;
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_RETR_ITABLES_FAILED'),
x_source => 'BSC_UPDATE_INC.Get_Input_Tables');
x_source => 'BSC_UPDATE_INC.Get_Input_Tables');
x_input_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_input_tables IN OUT NOCOPY NUMBER
) RETURN BOOLEAN IS
e_unexpected_error EXCEPTION;
h_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
h_sql := 'SELECT DISTINCT t.table_name'||
' FROM bsc_kpis_vl k, bsc_kpi_data_tables t'||
' WHERE k.indicator = t.indicator AND'||
' k.prototype_flag = :1 AND t.table_name IS NOT NULL';
IF BSC_UPDATE.g_kpi_mode THEN
IF BSC_UPDATE.g_num_indicators > 0 THEN
h_where_indics := BSC_APPS.Get_New_Big_In_Cond_Number(1, 'k.indicator');
FOR h_i IN 1 .. BSC_UPDATE.g_num_indicators LOOP
BSC_APPS.Add_Value_Big_In_Cond(1, BSC_UPDATE.g_indicators(h_i));
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_RETR_INCR_ITABLES_FAILED'),
x_source => 'BSC_UPDATE_INC.Get_Input_Tables_Incremental');
x_source => 'BSC_UPDATE_INC.Get_Input_Tables_Incremental');
h_purge_indicators BSC_UPDATE_UTIL.t_array_of_number;
SELECT indicator
FROM bsc_kpis_vl;
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_PURGE_KPIS_FAILED'),
x_source => 'BSC_UPDATE_INC.Purge_Data_All_Indicators');
x_source => 'BSC_UPDATE_INC.Purge_Data_All_Indicators');
h_purge_indicators BSC_UPDATE_UTIL.t_array_of_number;
SELECT indicator
FROM bsc_kpis_vl
WHERE calendar_id = pCalId ;
x_source => 'BSC_UPDATE_INC.Purge_Data_Indicators_Calendar');
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_PURGE_KPIS_FAILED'),
x_source => 'BSC_UPDATE_INC.Purge_Data_Indicators_Calendar');
x_source => 'BSC_UPDATE_INC.Purge_Data_Indicators_Calendar');
x_purge_indicators IN BSC_UPDATE_UTIL.t_array_of_number,
x_num_purge_indicators IN NUMBER
) RETURN BOOLEAN IS
e_unexpected_error EXCEPTION;
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_PURGE_KPIS_FAILED'),
x_source => 'BSC_UPDATE_INC.Purge_Indicators_Data');
x_source => 'BSC_UPDATE_INC.Purge_Indicators_Data');
x_purge_indicators IN BSC_UPDATE_UTIL.t_array_of_number,
x_num_purge_indicators IN NUMBER,
x_keep_input_data varchar2
) RETURN BOOLEAN IS
e_unexpected_error EXCEPTION;
h_purge_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
h_system_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
h_base_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
h_aw_indicators BSC_UPDATE_UTIL.t_array_of_number;
h_lock_indicators BSC_UPDATE_UTIL.t_array_of_number;
IF BSC_UPDATE_UTIL.Get_Kpi_Impl_Type(x_purge_indicators(h_i)) = 2 THEN
h_num_aw_indicators := h_num_aw_indicators + 1;
h_sql := 'SELECT DISTINCT table_name';
BSC_UPDATE_LOG.Write_Line_log(BSC_UPDATE_UTIL.Get_Message('BSC_KPIDATA_DELETION'), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_log(x_purge_indicators(h_i), BSC_UPDATE_LOG.OUTPUT);
h_sql := 'SELECT DISTINCT indicator';
BSC_UPDATE_LOG.Write_Line_log(h_indicator, BSC_UPDATE_LOG.OUTPUT);
IF BSC_UPDATE_UTIL.Get_Kpi_Impl_Type(h_indicator) = 2 THEN
IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Number(h_indicator, h_aw_indicators, h_num_aw_indicators) THEN
h_num_aw_indicators := h_num_aw_indicators + 1;
IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Number(h_indicator, h_lock_indicators, h_num_lock_indicators) THEN
h_num_lock_indicators := h_num_lock_indicators + 1;
IF BSC_UPDATE_UTIL.Get_Table_Generation_Type(h_purge_tables(h_i)) <> -1 THEN
-- It is an input, base table or a table created to store the projection at kpi level
--added for 5.2 when launched from RSG, we should not truncate the input tables
--input tables will be populated first. then rsg called. for initial load, rsg will call
--purge. then immediately, it will call load. if we truncate input table, there are no rows
--to process
IF x_keep_input_data='Y' THEN
IF BSC_UPDATE_UTIL.Get_Table_Type(h_purge_tables(h_i)) <> 0 THEN
-- It is not an input table, we can truncate it.
--LOCKING: Lock the table
IF NOT BSC_UPDATE_LOCK.Lock_Table(h_purge_tables(h_i)) THEN
RAISE e_could_not_get_lock;
BSC_UPDATE_UTIL.Truncate_Table_AT(h_purge_tables(h_i));
h_proj_tbl_name := BSC_UPDATE_BASE_V2.Get_Base_Proj_Tbl_Name(h_purge_tables(h_i));
BSC_UPDATE_UTIL.Truncate_Table_AT(h_proj_tbl_name);
h_message := BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_NAME_DELETION');
h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_purge_tables(h_i));
BSC_UPDATE_LOG.Write_Line_log(h_message, BSC_UPDATE_LOG.OUTPUT);
IF NOT BSC_UPDATE_LOCK.Lock_Table(h_purge_tables(h_i)) THEN
RAISE e_could_not_get_lock;
BSC_UPDATE_UTIL.Truncate_Table_AT(h_purge_tables(h_i));
h_proj_tbl_name := BSC_UPDATE_BASE_V2.Get_Base_Proj_Tbl_Name(h_purge_tables(h_i));
BSC_UPDATE_UTIL.Truncate_Table_AT(h_proj_tbl_name);
h_message := BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_NAME_DELETION');
h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_purge_tables(h_i));
BSC_UPDATE_LOG.Write_Line_log(h_message, BSC_UPDATE_LOG.OUTPUT);
IF BSC_UPDATE_UTIL.Get_Table_Type(h_purge_tables(h_i)) <> 0 THEN
-- It is not an input table, we can truncate it.
--LOCKING: Lock the table
IF NOT BSC_UPDATE_LOCK.Lock_Table(h_purge_tables(h_i)) THEN
RAISE e_could_not_get_lock;
BSC_UPDATE_UTIL.Truncate_Table_AT(h_purge_tables(h_i));
h_proj_tbl_name := BSC_UPDATE_BASE_V2.Get_Base_Proj_Tbl_Name(h_purge_tables(h_i));
BSC_UPDATE_UTIL.Truncate_Table_AT(h_proj_tbl_name);
h_message := BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_NAME_DELETION');
h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_purge_tables(h_i));
BSC_UPDATE_LOG.Write_Line_log(h_message, BSC_UPDATE_LOG.OUTPUT);
IF NOT BSC_UPDATE_LOCK.Lock_Table(h_purge_tables(h_i)) THEN
RAISE e_could_not_get_lock;
BSC_UPDATE_UTIL.Truncate_Table_AT(h_purge_tables(h_i));
h_proj_tbl_name := BSC_UPDATE_BASE_V2.Get_Base_Proj_Tbl_Name(h_purge_tables(h_i));
BSC_UPDATE_UTIL.Truncate_Table_AT(h_proj_tbl_name);
h_message := BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_NAME_DELETION');
h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_purge_tables(h_i));
BSC_UPDATE_LOG.Write_Line_log(h_message, BSC_UPDATE_LOG.OUTPUT);
h_sql := 'SELECT r.table_name'||
' FROM bsc_db_tables_rels r, bsc_db_tables t'||
' WHERE r.source_table_name = t.table_name AND'||
' t.table_type = :1 AND ('||h_where_tables_mv||')';
IF BSC_UPDATE_UTIL.Is_Table_For_AW_Kpi(h_table_name) THEN
-- Base table for AW indicators
-- Fix bug#4567847: there is no aw table created for the base table any more.
NULL;
IF NOT BSC_UPDATE.Refresh_System_MVs(h_base_tables, h_num_base_tables) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_LOCK.Lock_AW_Indicator_Cubes(h_aw_indicators(h_i)) THEN
RAISE e_could_not_get_lock;
IF NOT BSC_UPDATE_LOCK.Lock_Color_Indicators(h_lock_indicators, h_num_lock_indicators) THEN
RAISE e_could_not_get_lock;
h_sql := 'UPDATE bsc_sys_kpi_colors';
l_bind_vars_values.delete;
l_bind_vars_values(1) := BSC_UPDATE_COLOR.GRAY;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
h_sql := 'UPDATE bsc_sys_objective_colors';
l_bind_vars_values.delete;
l_bind_vars_values(1) := BSC_UPDATE_COLOR.GRAY;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
IF NOT BSC_UPDATE_LOCK.Lock_Tables(h_purge_tables, h_num_purge_tables) THEN
RAISE e_could_not_get_lock;
IF NOT BSC_UPDATE_LOCK.Lock_Period_Indicators(h_lock_indicators, h_num_lock_indicators) THEN
RAISE e_could_not_get_lock;
h_sql := 'SELECT DISTINCT calendar_id'||
' FROM bsc_kpis_b'||
' WHERE ('||h_where_indics||')';
h_current_fy := BSC_UPDATE_UTIL.Get_Calendar_Fiscal_Year(h_calendar_id);
h_sql := 'UPDATE'||
' bsc_db_tables '||
'SET '||
' current_period = :1, '||
' current_subperiod = 0 '||
'WHERE '||
' table_type <> 2 AND '||
' periodicity_id IN ('||
' SELECT '||
' periodicity_id '||
' FROM '||
' bsc_sys_periodicities_vl'||
' WHERE '||
' calendar_id = :2 AND '||
' yearly_flag = 1) AND '||
' ('||h_where_tables||')';
l_bind_vars_values.delete;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
h_sql := 'UPDATE'||
' bsc_db_tables '||
'SET '||
' current_period = 1, '||
' current_subperiod = 0 '||
'WHERE '||
' table_type <> 2 AND '||
' periodicity_id IN ('||
' SELECT '||
' periodicity_id '||
' FROM '||
' bsc_sys_periodicities_vl'||
' WHERE '||
' calendar_id = :1 AND '||
' yearly_flag = 0) AND '||
' ('||h_where_tables||')';
l_bind_vars_values.delete;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
h_sql := 'UPDATE '||
' bsc_kpi_periodicities '||
'SET '||
' current_period = :1 '||
'WHERE '||
' periodicity_id IN ('||
' SELECT '||
' periodicity_id '||
' FROM '||
' bsc_sys_periodicities_vl'||
' WHERE '||
' calendar_id = :2 AND '||
' yearly_flag = 1) AND '||
' ('||h_where_indics||')';
l_bind_vars_values.delete;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
h_sql := 'UPDATE '||
' bsc_kpi_periodicities '||
'SET '||
' current_period = 1 '||
'WHERE '||
' periodicity_id IN ('||
' SELECT '||
' periodicity_id '||
' FROM '||
' bsc_sys_periodicities_vl'||
' WHERE '||
' calendar_id = :1 AND '||
' yearly_flag = 0) AND '||
' ('||h_where_indics||')';
l_bind_vars_values.delete;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
IF NOT BSC_UPDATE_UTIL.Update_Kpi_Period_Name(x_purge_indicators(h_i)) THEN
RAISE e_unexpected_error;
h_sql := 'UPDATE bsc_kpi_defaults_b SET last_update_date = SYSDATE';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
BSC_UPDATE_UTIL.Update_Kpi_Time_Stamp(h_where_indics);
BSC_UPDATE_UTIL.Update_Kpi_Tab_Time_Stamp(h_where_indics);
x_source => 'BSC_UPDATE_INC.Purge_Indicators_Data');
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_PURGE_KPIS_FAILED'),
x_source => 'BSC_UPDATE_INC.Purge_Indicators_Data');
x_source => 'BSC_UPDATE_INC.Purge_Indicators_Data');
x_purge_indicators IN BSC_UPDATE_UTIL.t_array_of_number,
x_num_purge_indicators IN NUMBER,
x_keep_input_data varchar2
) RETURN BOOLEAN IS
PRAGMA AUTONOMOUS_TRANSACTION;
IF (BSC_UPDATE.g_kpi_mode) AND (BSC_UPDATE.g_num_indicators > 0) THEN
h_sql := 'UPDATE bsc_kpis_b'||
' SET prototype_flag = 0, last_update_date = SYSDATE'||
' WHERE prototype_flag IN (6, 7)';
h_sql_kpi := 'UPDATE bsc_kpi_analysis_measures_b ' ||
' SET prototype_flag = 0 ' ||
' WHERE prototype_flag = 7 ';
FOR h_i IN 1 .. BSC_UPDATE.g_num_indicators LOOP
BSC_APPS.Add_Value_Big_In_Cond(1, BSC_UPDATE.g_indicators(h_i));
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
BSC_UPDATE_UTIL.Execute_Immediate(h_sql_kpi);
UPDATE bsc_kpis_b
SET prototype_flag = 0, last_update_date = SYSDATE
WHERE prototype_flag IN (6, 7);
UPDATE bsc_kpi_analysis_measures_b
SET prototype_flag = 0
WHERE prototype_flag = 7;
x_source => 'BSC_UPDATE_INC.Reset_Flag_Indicators');