The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_loaded_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_loaded_tables IN NUMBER,
x_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_dim_tables IN NUMBER
) RETURN BOOLEAN IS
TYPE t_cursor IS REF CURSOR;
SELECT dp.level_table_name
FROM bsc_sys_dim_levels_b d, bsc_sys_dim_levels_b dp, bsc_sys_dim_level_rels r
WHERE d.dim_level_id = r.dim_level_id AND
r.parent_dim_level_id = dp.dim_level_id AND
DECODE(r.relation_type, 2, r.relation_col, d.level_table_name) = p_dim_table;
IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_parent_table,
x_dim_tables,
x_num_dim_tables) THEN
-- The parent table was or is going to be loaded in this process
-- So, we need to check if this parent table is already loaded or not
-- If it is not already loaded the dimension table cannot be
-- loaded right now.
IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_parent_table,
x_loaded_tables,
x_num_loaded_tables) THEN
h_ret := FALSE;
x_source => 'BSC_UPDATE.Can_Load_Dim_Table');
x_calculated_sys_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_calculated_sys_tables IN NUMBER,
x_system_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_system_tables IN NUMBER
) RETURN BOOLEAN IS
TYPE t_cursor IS REF CURSOR;
c_origin_tables_sql VARCHAR2(2000) := 'SELECT source_table_name'||
' FROM bsc_db_tables_rels'||
' WHERE table_name = :1';
SELECT source_table_name
FROM bsc_db_tables_rels
WHERE table_name = p_table_name;
IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_origin_table,
x_system_tables,
x_num_system_tables) THEN
-- The origin table was or is going to be calculated in this process
-- So, we need to check if this origin table is already calculated or not
-- If it is not already calculated the the system table cannot be
-- calculated right now.
IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_origin_table,
x_calculated_sys_tables,
x_num_calculated_sys_tables) THEN
h_ret := FALSE;
x_source => 'BSC_UPDATE.Can_Calculate_Sys_Table');
h_base_table BSC_UPDATE_UTIL.t_array_of_varchar2;
h_system_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
DELETE FROM bsc_db_calculations
WHERE table_name = p_base_table AND calculation_type = c_calculation_type;
h_base_table_periodicity := BSC_UPDATE_UTIL.Get_Table_Periodicity(p_base_table);
IF NOT Insert_Affected_Tables(h_base_table, 1, h_system_tables, h_num_system_tables) THEN
RAISE e_unexpected_error;
h_sql := 'SELECT DISTINCT periodicity_id '||
'FROM bsc_db_tables '||
'WHERE '||h_lst_where;
INSERT INTO bsc_db_calculations (table_name, calculation_type, parameter1)
VALUES (p_base_table, c_calculation_type, h_periodicity_id);
x_error_message := BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR');
x_source => 'BSC_UPDATE.Configure_Periodicity_Calc_VB',
x_mode => 'I');
x_source => 'BSC_UPDATE.Configure_Periodicity_Calc_VB',
x_mode => 'I');
SELECT table_name, column_name
FROM bsc_db_tables_cols
WHERE table_name IN (
SELECT table_name
FROM bsc_db_tables_rels
WHERE source_table_name IN (
SELECT table_name
FROM bsc_db_tables
WHERE table_type = p_table_type
)
) AND
column_type = p_column_type AND
column_name IN (
SELECT level_pk_col
FROM bsc_kpi_dim_levels_b
WHERE indicator IN (
SELECT indicator
FROM bsc_kpis_b
WHERE indicator_type = p_indic_type AND config_type = p_config_type
) AND
dim_level_index = p_dim_level_index
);
DELETE FROM bsc_db_calculations WHERE calculation_type = h_calculation_type;
INSERT INTO bsc_db_calculations (table_name, calculation_type, parameter1)
VALUES(h_base_table, h_calculation_type, h_pk_level_subaccount);
x_source => 'BSC_UPDATE.Configure_Profit_Calc_VB',
x_mode => 'I');
x_source => 'BSC_UPDATE.Configure_Profit_Calc_VB',
x_mode => 'I');
| PROCEDURE Execute_Update_Process
+============================================================================*/
PROCEDURE Execute_Update_Process (
x_process_id IN NUMBER,
x_process_name IN VARCHAR2,
x_parameter_1 IN VARCHAR2
) IS
e_update_error EXCEPTION;
h_input_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
h_indicators BSC_UPDATE_UTIL.t_array_of_number;
h_calendars BSC_UPDATE_UTIL.t_array_of_number;
h_edw_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
SELECT input_table_name
FROM bsc_db_loader_control
WHERE process_id = p_process_id;
RAISE e_update_error;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
IF BSC_UPDATE_UTIL.is_parallel THEN
COMMIT;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
RAISE e_update_error;
IF NOT BSC_UPDATE_LOG.Init_Log_File(h_log_file_name) THEN
RAISE e_update_error;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'PROCESS_ID')||
BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
' '||TO_CHAR(g_process_id), BSC_UPDATE_LOG.LOG);
BSC_UPDATE_LOG.Write_Line_Log(USERENV('SESSIONID'), BSC_UPDATE_LOG.LOG);
RAISE e_update_error;
g_num_indicators := BSC_UPDATE_UTIL.Decompose_Numeric_List(x_parameter_1,
g_indicators,
',');
IF NOT BSC_UPDATE_LOCK.Lock_Import_Dbi_Plans THEN
RAISE e_could_not_get_lock;
IF NOT BSC_UPDATE_DIM.Import_Dbi_Plans_AT(h_error_message) THEN
RAISE e_import_dbi_plans;
IF BSC_UPDATE_UTIL.Get_Kpi_Impl_Type(g_indicators(h_i)) = 2 THEN
h_load_type_into_aw := TRUE;
IF BSC_UPDATE_UTIL.Exists_AW_Kpi THEN
h_load_type_into_aw := TRUE;
BSC_UPDATE_DIM.Load_Type_Into_AW_AT;
IF NOT BSC_UPDATE_LOCK.Lock_Temp_Tables('DIMENSION') THEN
RAISE e_could_not_get_lock;
IF NOT BSC_UPDATE_DIM.Create_AW_Dim_Temp_Tables_AT THEN
RAISE e_update_error;
RAISE e_update_error;
RAISE e_update_error;
RAISE e_update_error;
IF NOT BSC_UPDATE_LOCK.Lock_Incremental_Indicators THEN
RAISE e_could_not_get_lock;
IF NOT BSC_UPDATE_INC.Do_Incremental_AT() THEN
RAISE e_update_error;
RAISE e_update_error;
IF NOT BSC_UPDATE_LOCK.Lock_Indicators(h_input_tables, h_num_input_tables) THEN
RAISE e_could_not_get_lock;
RAISE e_update_error;
RAISE e_update_error;
IF NOT BSC_UPDATE.Process_Input_Tables_AT(h_input_tables, h_num_input_tables, 0) THEN
RAISE e_update_error;
h_num_calendars := BSC_UPDATE_UTIL.Decompose_Numeric_List(x_parameter_1,
h_calendars,
',');
IF NOT BSC_UPDATE_LOCK.Lock_Indicators_by_Calendar(h_calendars, h_num_calendars) THEN
RAISE e_could_not_get_lock;
RAISE e_update_error;
ELSIF x_process_name = PC_DELETE_KPI_DATA_PROCESS THEN
-- Run delete kpis data process
h_num_indicators := 0;
h_sql := 'SELECT input_table_name'||
' FROM bsc_db_loader_control'||
' WHERE process_id = :1';
IF NOT BSC_UPDATE_LOCK.Lock_Indicators_To_Delete(h_indicators, h_num_indicators) THEN
RAISE e_could_not_get_lock;
IF NOT BSC_UPDATE_INC.Purge_Indicators_Data_AT(h_indicators, h_num_indicators, g_keep_input_table_data) THEN
RAISE e_update_error;
RAISE e_update_error;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
IF BSC_UPDATE_UTIL.is_parallel THEN
h_sql := 'alter session disable parallel dml';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
-- Delete records in the temporal table used for big 'in' conditions
BSC_APPS.Init_Big_In_Cond_Table;
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_NO_PENDING_PROCESS'),
x_source => 'BSC_UPDATE.Execute_Update_Process',
x_mode => 'I');
BSC_UPDATE_LOG.Write_Errors_To_Log;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
-- Delete records in the temporal table used for big 'in' conditions
BSC_APPS.Init_Big_In_Cond_Table;
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_METADATA_PEND_CHANGES'),
x_source => 'BSC_UPDATE.Execute_Update_Process',
x_mode => 'I');
BSC_UPDATE_LOG.Write_Errors_To_Log;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
WHEN e_update_error THEN
ROLLBACK;
-- Delete records in the temporal table used for big 'in' conditions
BSC_APPS.Init_Big_In_Cond_Table;
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED'),
x_source => 'BSC_UPDATE.Execute_Update_Process',
x_mode => 'I');
BSC_UPDATE_LOG.Write_Errors_To_Log;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
-- Delete records in the temporal table used for big 'in' conditions
BSC_APPS.Init_Big_In_Cond_Table;
x_source => 'BSC_UPDATE.Execute_Update_Process',
x_mode => 'I');
BSC_UPDATE_LOG.Write_Errors_To_Log;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
-- Delete records in the temporal table used for big 'in' conditions
BSC_APPS.Init_Big_In_Cond_Table;
BSC_MESSAGE.Add(x_message => 'BSC_UPDATE_DIM.Import_Dbi_Plans: '||h_error_message,
x_source => 'BSC_UPDATE.Execute_Update_Process',
x_mode => 'I');
BSC_UPDATE_LOG.Write_Errors_To_Log;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
-- Delete records in the temporal table used for big 'in' conditions
BSC_APPS.Init_Big_In_Cond_Table;
x_source => 'BSC_UPDATE.Execute_Update_Process',
x_mode => 'I');
BSC_UPDATE_LOG.Write_Errors_To_Log;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
-- Delete records in the temporal table used for big 'in' conditions
BSC_APPS.Init_Big_In_Cond_Table;
x_source => 'BSC_UPDATE.Execute_Update_Process',
x_mode => 'I');
BSC_UPDATE_LOG.Write_Errors_To_Log;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
END Execute_Update_Process;
SELECT t.table_name, t.periodicity_id, t.current_period, p.yearly_flag
FROM bsc_db_tables t, bsc_sys_periodicities p
WHERE t.periodicity_id = p.periodicity_id AND
t.table_type = p_table_type AND p.calendar_id = p_calendar_id;
SELECT t.table_name, t.periodicity_id, t.current_period, p.yearly_flag
FROM bsc_db_tables t, bsc_sys_periodicities p
WHERE t.periodicity_id = p.periodicity_id AND
t.table_type = p_table_type1 AND t.generation_type <> p_gen_type AND
p.calendar_id = p_calendar_id AND
t.table_name IN (
SELECT r.table_name
FROM bsc_db_tables_rels r, bsc_db_tables b
WHERE r.source_table_name = b.table_name and b.table_type = p_table_type0
);
SELECT t.table_name, t.periodicity_id, t.current_period, p.yearly_flag
FROM bsc_db_tables t, bsc_sys_periodicities_vl p
WHERE t.periodicity_id = p.periodicity_id AND
t.table_type = p_table_type1 AND t.generation_type <> p_gen_type AND
p.calendar_id = p_calendar_id AND
NOT (t.table_name IN (
SELECT r.table_name
FROM bsc_db_tables_rels r, bsc_db_tables b
WHERE r.source_table_name = b.table_name and b.table_type = p_table_type0
));
SELECT DISTINCT k.projection_data
FROM bsc_kpi_data_tables k, bsc_sys_periodicities p
WHERE k.periodicity_id = p.periodicity_id AND
p.calendar_id = p_calendar_id AND
projection_data IS NOT NULL;
SELECT c.parameter1, p.yearly_flag
FROM bsc_db_calculations c, bsc_sys_periodicities p
WHERE c.table_name = p_table_name AND c.calculation_type = p_calc_type AND
c.parameter1 = p.periodicity_id;
SELECT indicator
FROM bsc_kpis_vl
WHERE calendar_id = p_calendar_id;
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_aw_base_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
IF NOT BSC_UPDATE_LOCK.Lock_Calendar_Change(x_calendar_id) THEN
RAISE e_could_not_get_lock;
h_calendar_name := BSC_UPDATE_UTIL.Get_Calendar_Name(x_calendar_id);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_YEARCHANGE_PROCESS')||' ('||
h_calendar_name||')', BSC_UPDATE_LOG.OUTPUT);
h_current_fy := BSC_UPDATE_UTIL.Get_Calendar_Fiscal_Year(x_calendar_id);
h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(x_calendar_id);
SELECT count(year)
INTO h_count
FROM bsc_db_calendar
WHERE calendar_id = x_calendar_id AND year = h_new_fiscal_year;
h_message := BSC_UPDATE_UTIL.Get_Message('BSC_DBI_YEAR_NOT_AVAILABLE');
h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'YEAR', TO_CHAR(h_new_fiscal_year));
h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'CALENDAR_NAME', h_calendar_name);
BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
IF NOT BSC_UPDATE_UTIL.Set_Calendar_Fiscal_Year(x_calendar_id, (h_current_fy + 1)) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_UTIL.Init_Calendar_Tables(x_calendar_id) THEN
RAISE e_unexpected_error;
IF BSC_UPDATE_UTIL.Get_Kpi_Impl_Type(h_indicator) = 2 THEN
h_num_aw_indicators := h_num_aw_indicators + 1;
UPDATE
bsc_kpi_periodicities
SET
current_period = h_current_fy + 1
WHERE
indicator = h_indicator AND
periodicity_id IN (
SELECT
periodicity_id
FROM
bsc_sys_periodicities
WHERE
calendar_id = x_calendar_id AND
yearly_flag = 1);
UPDATE
bsc_kpi_periodicities
SET
current_period = 1
WHERE
indicator = h_indicator AND
periodicity_id IN (
SELECT
periodicity_id
FROM
bsc_sys_periodicities
WHERE
calendar_id = x_calendar_id AND
yearly_flag = 0);
UPDATE bsc_sys_kpi_colors
SET kpi_color = BSC_UPDATE_COLOR.GRAY,
actual_data = NULL,
budget_data = NULL
WHERE indicator = h_indicator;
UPDATE bsc_sys_objective_colors
SET obj_color = BSC_UPDATE_COLOR.GRAY
WHERE indicator = h_indicator;
IF NOT BSC_UPDATE_UTIL.Update_Kpi_Period_Name(h_indicator) THEN
RAISE e_unexpected_error;
UPDATE
bsc_kpi_defaults_b
SET
last_update_date = SYSDATE
WHERE
indicator = h_indicator;
h_aw_flag := BSC_UPDATE_UTIL.Is_Table_For_AW_Kpi(h_table_info.table_name);
h_proj_tbl_name := BSC_UPDATE_BASE_V2.Get_Base_Proj_Tbl_Name(h_table_info.table_name);
BSC_UPDATE_UTIL.Truncate_Table_AT(h_proj_tbl_name);
h_sql := 'DELETE FROM '||h_table_info.table_name||
' WHERE YEAR > :1 AND TYPE = 0';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
h_sql := 'DELETE FROM '||h_table_info.table_name||
' WHERE YEAR = :1 AND PERIOD > :2 AND TYPE = 0';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,3);
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
h_sql := 'DELETE FROM '||h_table_info.table_name||
' WHERE YEAR > :1 AND TYPE = 0 AND PERIODICITY_ID = :2';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
h_current_period := BSC_UPDATE_UTIL.Get_Period_Other_Periodicity(
h_periodicity_id,
x_calendar_id,
h_yearly_flag,
h_current_fy,
h_table_info.periodicity_id,
h_table_info.current_period
);
h_sql := 'DELETE FROM '||h_table_info.table_name||
' WHERE YEAR = :1 AND PERIOD > :2 AND TYPE = 0 AND PERIODICITY_ID = :3';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,3);
l_bind_vars_values.delete;
IF BSC_UPDATE_UTIL.Table_Exists(h_table_info.table_name) THEN
--ENH_B_TABLES_PERF: In the new strategy the base table may have a projection table
-- In this case we need to truncate the projection table and we do not need to touch
-- the base table since it contains only actuals.
h_proj_tbl_name := BSC_UPDATE_BASE_V2.Get_Base_Proj_Tbl_Name(h_table_info.table_name);
BSC_UPDATE_UTIL.Truncate_Table_AT(h_proj_tbl_name);
h_sql := 'DELETE FROM '||h_table_info.table_name||
' WHERE YEAR > :1 AND TYPE = 0';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
h_sql := 'DELETE FROM '||h_table_info.table_name||
' WHERE YEAR = :1 AND PERIOD > :2 AND TYPE = 0';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
UPDATE
bsc_db_tables
SET
current_period = h_current_fy + 1,
current_subperiod = 0
WHERE
table_type <> 2 AND
periodicity_id IN (
SELECT
periodicity_id
FROM
bsc_sys_periodicities
WHERE
calendar_id = x_calendar_id AND
yearly_flag = 1);
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
WHERE
calendar_id = x_calendar_id AND
yearly_flag = 0);
l_bind_vars_values.delete;
IF NOT BSC_UPDATE_LOCK.Lock_Table(h_table_name) THEN
RAISE e_could_not_get_lock;
BSC_UPDATE_UTIL.Truncate_Table_AT(h_table_name);
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_Table(h_aw_base_tables(h_i)) THEN
RAISE e_could_not_get_lock;
h_aw_table_name := BSC_UPDATE_BASE.Get_Base_AW_Table_Name(h_aw_base_tables(h_i));
BSC_UPDATE_UTIL.Truncate_Table_AT(h_aw_table_name);
IF BSC_UPDATE_UTIL.Is_Kpi_In_Production(h_aw_indicators(h_i)) THEN
--LOCKING: Lock the objects required to refresh the AW indicator cubes
IF NOT BSC_UPDATE_LOCK.Lock_Refresh_AW_Indicator(h_aw_indicators(h_i)) THEN
RAISE e_could_not_get_lock;
BSC_UPDATE_SUM.Refresh_AW_Kpi_AT(h_aw_indicators(h_i));
IF NOT BSC_UPDATE_LOCK.Lock_Update_Date THEN
RAISE e_could_not_get_lock;
IF NOT BSC_UPDATE_UTIL.Write_Init_Variable_Value('UPDATE_DATE', TO_CHAR(SYSDATE, 'DD/MM/YYYY')) THEN
RAISE e_unexpected_error;
BSC_UPDATE_UTIL.Update_System_Time_Stamp;
IF NOT BSC_UPDATE_LOCK.Lock_Calendar(x_calendar_id) THEN
RAISE e_could_not_get_lock;
BSC_UPDATE_UTIL.Load_Calendar_Into_AW_AT(x_calendar_id);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_YEARCHANGE_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
BSC_MESSAGE.Add (x_message => BSC_UPDATE_UTIL.Get_Message('BSC_YEARCHANGE_FAILED'),
x_source => 'BSC_UPDATE.Execute_Year_Change_Process');
x_source => 'BSC_UPDATE.Execute_Year_Change_Process');
x_source => 'BSC_UPDATE.Execute_Year_Change_Process');
x_source => 'BSC_UPDATE.Execute_Year_Change_Process');
c_prototype_sql VARCHAR2(2000) := 'SELECT COUNT(prototype_flag)'||
' FROM bsc_kpis_vl'||
' WHERE prototype_flag IN (1, 2, 3, 4, 5)';
SELECT COUNT(prototype_flag)
INTO h_count
FROM bsc_kpis_vl
WHERE prototype_flag IN (1, 2, 3, 4, 5);
x_source => 'BSC_UPDATE.Exists_Prototype_Indicators');
UPDATE
bsc_db_loader_control
SET
last_stage_flag = 0
WHERE
input_table_name = x_input_table;
UPDATE
bsc_db_loader_control
SET
last_stage_flag = 1
WHERE
input_table_name = x_input_table AND
process_id = g_process_id;
x_source => 'BSC_UPDATE.Flag_Last_Stage_Input_Table');
c_base_table_sql VARCHAR2(2000) := 'SELECT table_name'||
' FROM bsc_db_tables_rels'||
' WHERE UPPER(source_table_name) = UPPER(:1) AND relation_type = :2';
SELECT table_name
INTO x_base_table
FROM bsc_db_tables_rels
WHERE UPPER(source_table_name) = UPPER(x_input_table) AND relation_type = 0;
x_source => 'BSC_UPDATE.Get_Base_Table_Of_Input_Table');
x_edw_dim_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_edw_dim_tables IN OUT NOCOPY NUMBER
) RETURN BOOLEAN IS
TYPE t_cursor IS REF CURSOR;
c_edw_dimensions_sql VARCHAR2(2000) := 'SELECT level_table_name'||
' FROM bsc_sys_dim_levels_b'||
' WHERE NVL(edw_flag, 0) = :1 AND'||
' level_pk_col IN (SELECT column_name'||
' FROM bsc_db_tables_cols c, bsc_db_loader_control p'||
' WHERE c.table_name = p.input_table_name AND'||
' c.column_type = :2 AND p.process_id = :3 AND p.status = :4)';
SELECT level_table_name
FROM bsc_sys_dim_levels_b
WHERE NVL(edw_flag, 0) = p_edw_flag AND
level_pk_col IN (
SELECT column_name
FROM bsc_db_tables_cols c, bsc_db_loader_control p
WHERE c.table_name = p.input_table_name AND
c.column_type = p_column_type AND p.process_id = p_process_id AND p.status = p_status);
x_source => 'BSC_UPDATE.Get_EDW_Dims_In_Input_Tables');
x_base_tables_to_color IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_base_tables_to_color IN NUMBER,
x_color_indicators IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
x_num_color_indicators IN OUT NOCOPY NUMBER
) RETURN BOOLEAN IS
e_unexpected_error EXCEPTION;
h_system_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
IF NOT Insert_Affected_Tables(x_base_tables_to_color,
x_num_base_tables_to_color,
h_system_tables,
h_num_system_tables) THEN
RAISE e_unexpected_error;
h_sql := 'SELECT DISTINCT indicator '||
'FROM bsc_kpi_data_tables '||
'WHERE '||h_lst_where;
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_COLORKPILIST_FAILED'),
x_source => 'BSC_UPDATE.Get_Indicators_To_Color');
x_source => 'BSC_UPDATE.Get_Indicators_To_Color');
c_last_stage_sql VARCHAR2(2000) := 'SELECT stage'||
' FROM bsc_db_loader_control'||
' WHERE input_table_name = :1 AND last_stage_flag = :2';
SELECT stage
INTO x_last_stage
FROM bsc_db_loader_control
WHERE input_table_name = x_input_table AND last_stage_flag = 1;
x_source => 'BSC_UPDATE.Get_Last_Stage_Input_Table');
c_process_status_sql VARCHAR2(2000) := 'SELECT status'||
' FROM bsc_db_process_control'||
' WHERE process_id = :1';
SELECT status
INTO h_status
FROM bsc_db_process_control
WHERE process_id = x_process_id;
SELECT BSC_DB_PROCESS_ID_S.NEXTVAL
INTO h_process_id
FROM DUAL;
INSERT INTO BSC_DB_PROCESS_CONTROL (
PROCESS_ID,
PROCESS_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
STATUS,
DESCRIPTION
)
SELECT
h_process_id,
PROCESS_NAME,
g_user_id,
SYSDATE,
g_user_id,
SYSDATE,
g_session_id,
PC_PENDING_STATUS,
DESCRIPTION
FROM
BSC_DB_PROCESS_CONTROL
WHERE
PROCESS_ID = x_process_id;
INSERT INTO BSC_DB_LOADER_CONTROL (
PROCESS_ID,
INPUT_TABLE_NAME,
STATUS,
ERROR_CODE,
STAGE,
LAST_STAGE_FLAG
)
SELECT
h_process_id,
INPUT_TABLE_NAME,
LC_PENDING_STATUS,
NULL,
LC_PENDING_STAGE,
0
FROM
BSC_DB_LOADER_CONTROL
WHERE
PROCESS_ID = x_process_id AND
INPUT_TABLE_NAME IN (SELECT TABLE_NAME FROM BSC_DB_TABLES WHERE TABLE_TYPE = 0);
INSERT INTO BSC_DB_LOADER_CONTROL (
PROCESS_ID,
INPUT_TABLE_NAME,
STATUS,
ERROR_CODE,
STAGE,
LAST_STAGE_FLAG
)
SELECT
h_process_id,
INPUT_TABLE_NAME,
LC_PENDING_STATUS,
NULL,
LC_PENDING_STAGE,
0
FROM
BSC_DB_LOADER_CONTROL
WHERE
PROCESS_ID = x_process_id AND
INPUT_TABLE_NAME IN (SELECT TABLE_NAME FROM BSC_DB_TABLES WHERE TABLE_TYPE = 2);
x_source => 'BSC_UPDATE.Get_Process_Id');
x_input_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_input_tables IN OUT NOCOPY NUMBER,
x_status IN VARCHAR2
) RETURN BOOLEAN IS
TYPE t_cursor IS REF CURSOR;
c_input_tables_sql VARCHAR2(2000) := 'SELECT input_table_name'||
' FROM bsc_db_loader_control'||
' WHERE process_id = :1 AND status = :2';
SELECT input_table_name
FROM bsc_db_loader_control
WHERE process_id = p_process_id AND status = p_status;
x_source => 'BSC_UPDATE.Get_Process_Input_Tables');
x_source => 'BSC_UPDATE.Init_Env_Values');
x_input_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_input_tables IN NUMBER
) RETURN BOOLEAN IS
e_unexpected_error EXCEPTION;
IF NOT BSC_UPDATE_UTIL.Get_Input_Table_Source(h_input_table, h_source_type, h_source_name) THEN
RAISE e_unexpected_error;
h_message := BSC_UPDATE_UTIL.Get_Message('BSC_IMPORTING_TO_ITABLE');
h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'INPUT_TABLE', h_input_table);
BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
h_message := BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SOURCE')||
BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||' '||h_source_name;
BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
IF NOT BSC_UPDATE_LOCK.Lock_Import_ITable(h_input_table) THEN
RAISE e_could_not_get_lock;
x_source => 'BSC_UPDATE.Import_ITables_From_DBSrc');
BSC_MESSAGE.Add (x_message => BSC_UPDATE_UTIL.Get_Message('BSC_IMPORT_ITABLES_FAILED'),
x_source => 'BSC_UPDATE.Import_ITables_From_DBSrc');
x_source => 'BSC_UPDATE.Import_ITables_From_DBSrc');
x_input_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_input_tables IN NUMBER
) RETURN BOOLEAN IS
PRAGMA AUTONOMOUS_TRANSACTION;
c_status_sql VARCHAR2(2000) := 'SELECT status'||
' FROM user_objects'||
' WHERE object_name = :1 AND object_type = :2';
SELECT status
FROM user_objects
WHERE object_name = p_object_name AND object_type = p_object_type;
h_sql := 'DELETE FROM '||x_input_table;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
IF NOT Update_Stage_Input_Table(x_input_table, LC_UPLOADED_STAGE) THEN
RAISE e_unexpected_error;
IF NOT Update_Status_Input_Table(x_input_table, LC_ERROR_STATUS, LC_UPLOAD_SP_EXECUTION_ERR) THEN
RAISE e_unexpected_error;
h_message := BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'ERROR')||
BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||' '||
BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_FAILED');
BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(SQLERRM, BSC_UPDATE_LOG.OUTPUT);
IF NOT Update_Status_Input_Table(x_input_table, LC_ERROR_STATUS, LC_UPLOAD_SP_INVALID_ERR) THEN
RAISE e_unexpected_error;
h_message := BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'ERROR')||
BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||' '||
BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_IS_INVALID');
BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
IF NOT Update_Status_Input_Table(x_input_table, LC_ERROR_STATUS, LC_UPLOAD_SP_NOT_FOUND_ERR) THEN
RAISE e_unexpected_error;
h_message := BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'ERROR')||
BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||' '||
BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_NOT_FOUND');
BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
BSC_MESSAGE.Add (x_message => BSC_UPDATE_UTIL.Get_Message('BSC_IMPORT_ITABLES_FAILED'),
x_source => 'BSC_UPDATE.Import_ITable_StoredProc');
x_source => 'BSC_UPDATE.Import_ITable_StoredProc');
| FUNCTION Insert_Affected_Tables
+============================================================================*/
FUNCTION Insert_Affected_Tables (
x_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_tables IN NUMBER,
x_affected_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_affected_tables IN OUT NOCOPY NUMBER
) RETURN BOOLEAN IS
e_unexpected_error EXCEPTION;
SELECT table_name
FROM bsc_db_tables_rels
WHERE source_table_name = p_source_table_name;
h_new_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
h_sql := 'SELECT table_name'||
' FROM bsc_db_tables_rels'||
' WHERE source_table_name = :1';
IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_table_name, x_affected_tables, x_num_affected_tables) THEN
x_num_affected_tables := x_num_affected_tables + 1;
IF NOT Insert_Affected_Tables(h_new_tables, h_num_new_tables, x_affected_tables, x_num_affected_tables) THEN
RAISE e_unexpected_error;
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_TABLES_INSERT_FAILED'),
x_source => 'BSC_UPDATE.Insert_Affected_Tables');
x_source => 'BSC_UPDATE.Insert_Affected_Tables');
END Insert_Affected_Tables;
x_input_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_input_tables IN NUMBER
) RETURN BOOLEAN IS
e_unexpected_error EXCEPTION;
h_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
h_loaded_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
h_input_tables_err_status BSC_UPDATE_UTIL.t_array_of_varchar2;
SELECT input_table_name
FROM bsc_db_loader_control
WHERE process_id = g_process_id AND status = LC_ERROR_STATUS;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_DIMTABLE_UPD_PROCESS'), BSC_UPDATE_LOG.OUTPUT);
IF NOT Update_Status_All_Input_Tables(LC_PENDING_STATUS, LC_RUNNING_STATUS, NULL) THEN
RAISE e_unexpected_error;
h_dim_tables(h_i) := BSC_UPDATE_DIM.Get_Dim_Table_of_Input_Table(x_input_tables(h_i));
IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_dim_table,
h_loaded_tables,
h_num_loaded_tables) THEN
-- The table has not been calculated yet
-- Check if the table can be calculated now
h_b := Can_Load_Dim_Table(h_dim_table,
h_loaded_tables,
h_num_loaded_tables,
h_dim_tables,
h_num_dim_tables);
IF NOT BSC_UPDATE_LOCK.Lock_Load_Dimension_Table(h_dim_table, h_input_table) THEN
RAISE e_could_not_get_lock;
IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_input_table,
h_input_tables_err_status,
h_num_input_tables_err_status) THEN
-- Input table is ok, there was no error before loading it from stored procedure
-- Know if the input table is empty or not.
h_table_has_any_row := BSC_UPDATE_UTIL.Table_Has_Any_Row(h_input_table);
h_message := BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_NAME_VALIDATION');
h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
h_table_is_valid := BSC_UPDATE_DIM.Validate_Input_Table_AT(h_input_table, h_dim_table);
h_message := BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_NAME_VALIDCONF');
h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
IF NOT Update_Stage_Input_Table(h_input_table, LC_VALIDATED_STAGE) THEN
RAISE e_unexpected_error;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_UPDATING_DIM_TABLE')||
' '||h_dim_table, BSC_UPDATE_LOG.OUTPUT);
IF NOT BSC_UPDATE_DIM.Load_Dim_Table_AT(h_dim_table, h_input_table) THEN
RAISE e_unexpected_error;
IF NOT Update_Stage_Input_Table(h_input_table, LC_COMPLETED_STAGE) THEN
RAISE e_unexpected_error;
h_message := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATED_DIM_TABLE');
h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_dim_table);
BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
h_message := BSC_UPDATE_UTIL.Get_Message('BSC_INVALID_CODES_ITABLE');
h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
IF NOT Update_Status_Input_Table(h_input_table, LC_ERROR_STATUS, LC_INVALID_CODES_ERR) THEN
RAISE e_unexpected_error;
h_message := BSC_UPDATE_UTIL.Get_Message('BSC_MISSING_DATA_ITABLE');
h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
IF NOT Update_Status_Input_Table(h_input_table, LC_NO_DATA_STATUS, NULL) THEN
RAISE e_unexpected_error;
h_dim_table_type := BSC_UPDATE_DIM.Get_Dim_Table_Type(h_dim_table);
IF h_dim_table_type = BSC_UPDATE_DIM.DIM_TABLE_TYPE_1N THEN
IF BSC_UPDATE_DIM.Dimension_Used_In_AW_Kpi(h_dim_table) THEN
BSC_UPDATE_LOG.Write_Line_Log('Loading '||h_dim_table||' into AW', BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_DIM.Load_Dim_Into_AW_AT(h_dim_table);
IF NOT BSC_UPDATE_UTIL.Write_Init_Variable_Value('UPDATE_DATE_DIM', TO_CHAR(SYSDATE, 'DD/MM/YYYY')) THEN
RAISE e_unexpected_error;
IF NOT Update_Stage_Input_Tables(LC_RUNNING_STATUS, LC_COMPLETED_STAGE, FALSE) THEN
RAISE e_unexpected_error;
IF NOT Update_Status_All_Input_Tables(LC_RUNNING_STATUS, LC_COMPLETED_STATUS, NULL) THEN
RAISE e_unexpected_error;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_DIMTABLE_UPDATED'), BSC_UPDATE_LOG.OUTPUT);
IF NOT BSC_UPDATE_DIM.WriteRemovedKeyItems THEN
NULL;
x_source => 'BSC_UPDATE.Load_Dim_Input_Tables');
h_b := Update_Status_All_Input_Tables(LC_RUNNING_STATUS, LC_ERROR_STATUS, LC_PROGRAM_ERR);
BSC_MESSAGE.Add (x_message => BSC_UPDATE_UTIL.Get_Message('BSC_DIMTABLE_UPDATE_FAILED'),
x_source => 'BSC_UPDATE.Load_Dim_Input_Tables');
h_b := Update_Status_All_Input_Tables(LC_RUNNING_STATUS, LC_ERROR_STATUS, LC_PROGRAM_ERR);
x_source => 'BSC_UPDATE.Load_Dim_Input_Tables');
h_b := Update_Status_All_Input_Tables(LC_RUNNING_STATUS, LC_ERROR_STATUS, LC_PROGRAM_ERR);
x_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_dim_tables IN NUMBER
) RETURN BOOLEAN IS
e_unexpected_error EXCEPTION;
h_loaded_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_dim_table,
h_loaded_tables,
h_num_loaded_tables) THEN
-- The table has not been calculated yet
-- Check if the table can be calculated now
h_b := Can_Load_Dim_Table(h_dim_table,
h_loaded_tables,
h_num_loaded_tables,
x_dim_tables,
x_num_dim_tables);
h_dim_level_list.delete;
BSC_MESSAGE.Add (x_message => BSC_UPDATE_UTIL.Get_Message('BSC_DIMTABLE_UPDATE_FAILED'),
x_source => 'BSC_UPDATE.Load_Dims_Into_AW');
x_source => 'BSC_UPDATE.Load_Dims_Into_AW');
FUNCTION Update_Kpis_Prototype_Flag (
x_indicator IN NUMBER
) RETURN BOOLEAN IS
BEGIN
-- Color By KPI: Mark KPIs for color re-calculation
UPDATE bsc_kpi_analysis_measures_b
SET prototype_flag = 7
WHERE indicator = x_indicator;
, x_source => 'BSC_UPDATE.Update_Kpis_Prototype_Flag');
END Update_Kpis_Prototype_Flag;
x_input_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_input_tables IN NUMBER,
x_start_from IN NUMBER
) RETURN BOOLEAN IS
e_unexpected_error EXCEPTION;
h_base_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
h_base_tables_to_color BSC_UPDATE_UTIL.t_array_of_varchar2;
h_system_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
h_calculated_sys_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
h_color_indicators BSC_UPDATE_UTIL.t_array_of_number;
h_kpis BSC_UPDATE_UTIL.t_array_kpis;
h_base_tables_aw BSC_UPDATE_UTIL.t_array_of_varchar2;
h_system_tables_aw BSC_UPDATE_UTIL.t_array_of_varchar2;
h_indicators_aw BSC_UPDATE_UTIL.t_array_of_number;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_DATATABLE_UPD_PROCESS'), BSC_UPDATE_LOG.OUTPUT);
IF NOT BSC_UPDATE_LOCK.Lock_Temp_Tables('DATA') THEN
RAISE e_could_not_get_lock;
IF NOT BSC_UPDATE_BASE.Create_Generic_Temp_Tables_AT THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_BASE_V2.Create_Generic_Temp_Tables_AT THEN
RAISE e_unexpected_error;
IF NOT Update_Status_All_Input_Tables(LC_PENDING_STATUS, LC_RUNNING_STATUS, NULL) THEN
RAISE e_unexpected_error;
DELETE FROM bsc_db_validation
WHERE input_table_name = h_input_table;
h_aw_flag := BSC_UPDATE_UTIL.Is_Table_For_AW_Kpi(h_base_table);
IF NOT BSC_UPDATE_LOCK.Lock_Update_Base_Table(h_input_table, h_base_table) THEN
RAISE e_could_not_get_lock;
h_table_has_any_row := BSC_UPDATE_UTIL.Table_Has_Any_Row(h_input_table);
h_message := BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_NAME_VALIDATION');
h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
h_table_is_valid := BSC_UPDATE_VAL.Validate_Codes_AT(h_input_table);
h_message := BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_NAME_VALIDCONF');
h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
IF NOT Update_Stage_Input_Table(h_input_table, LC_VALIDATED_STAGE) THEN
RAISE e_unexpected_error;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_UPDATING_BASE_TABLE')||
' '||h_base_table, BSC_UPDATE_LOG.OUTPUT);
h_proj_tbl_name := BSC_UPDATE_BASE_V2.Get_Base_Proj_Tbl_Name(h_base_table);
IF NOT BSC_UPDATE_BASE.Calculate_Base_Table_AT(h_base_table, h_input_table, FALSE, h_aw_flag) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_BASE_V2.Calculate_Base_Table_AT(h_base_table, h_input_table, FALSE, h_aw_flag) THEN
RAISE e_unexpected_error;
IF NOT Update_Stage_Input_Table(h_input_table, LC_BASE_UPDATED_STAGE) THEN
RAISE e_unexpected_error;
h_message := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATED_BASE_TABLE');
h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_base_table);
BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
h_message := BSC_UPDATE_UTIL.Get_Message('BSC_INVALID_CODES_ITABLE');
h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
BSC_UPDATE_LOG.Write_Line_Log(h_message,
BSC_UPDATE_LOG.OUTPUT);
IF NOT Update_Status_Input_Table(h_input_table, LC_ERROR_STATUS, LC_INVALID_CODES_ERR) THEN
RAISE e_unexpected_error;
IF NOT Update_Stage_Input_Table(h_input_table, LC_BASE_UPDATED_STAGE) THEN
RAISE e_unexpected_error;
IF NOT Update_Status_Input_Table(h_input_table, LC_NO_DATA_STATUS, NULL) THEN
RAISE e_unexpected_error;
h_message := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATED_BASE_TABLE');
h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_base_table);
BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
h_message := BSC_UPDATE_UTIL.Get_Message('BSC_MISSING_DATA_ITABLE');
h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
IF NOT Update_Status_Input_Table(h_input_table, LC_NO_DATA_STATUS, NULL) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_LOCK.Lock_Update_Base_Table(x_input_tables(h_i), h_base_table) THEN
RAISE e_could_not_get_lock;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_UPDATING_BASE_TABLE')||
' '||h_base_table, BSC_UPDATE_LOG.OUTPUT);
h_aw_flag := BSC_UPDATE_UTIL.Is_Table_For_AW_Kpi(h_base_table);
h_proj_tbl_name := BSC_UPDATE_BASE_V2.Get_Base_Proj_Tbl_Name(h_base_table);
IF NOT BSC_UPDATE_BASE.Calculate_Base_Table_AT(h_base_table, x_input_tables(h_i), TRUE, h_aw_flag) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_BASE_V2.Calculate_Base_Table_AT(h_base_table, x_input_tables(h_i), TRUE, h_aw_flag) THEN
RAISE e_unexpected_error;
h_message := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATED_BASE_TABLE');
h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_base_table);
BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
IF NOT Insert_Affected_Tables(h_base_tables, h_num_base_tables, h_system_tables, h_num_system_tables) THEN
RAISE e_unexpected_error;
IF NOT Insert_Affected_Tables(h_base_tables_aw, h_num_base_tables_aw, h_system_tables_aw, h_num_system_tables_aw) THEN
RAISE e_unexpected_error;
BSC_UPDATE_SUM.g_refreshed_mvs.delete;
BSC_UPDATE_SUM.g_num_refreshed_mvs := 0;
IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_system_tables(h_i),
h_calculated_sys_tables,
h_num_calculated_sys_tables) THEN
-- The table has not been calculated yet
-- Check if the table can be calculated now
h_b := Can_Calculate_Sys_Table(h_system_tables(h_i),
h_calculated_sys_tables,
h_num_calculated_sys_tables,
h_system_tables,
h_num_system_tables);
h_num_kpis := BSC_UPDATE_UTIL.Get_Kpis_Using_Table(h_system_tables(h_i), h_kpis);
IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Number(h_kpis(h_i).indicator,
g_indicators,
g_num_indicators) THEN
h_calc_summary_table := TRUE;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_SUMTABLE_CALC_PROCESS')||
' '||h_system_tables(h_i),
BSC_UPDATE_LOG.OUTPUT);
IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_system_tables(h_i),
h_system_tables_aw,
h_num_system_tables_aw) THEN
-- This table is for an AW indicator
--LOCKING: lock the tables needed to refresh this table
IF NOT BSC_UPDATE_LOCK.Lock_Refresh_AW_Table(h_system_tables(h_i)) THEN
RAISE e_could_not_get_lock;
IF NOT BSC_UPDATE_SUM.Calculate_Sum_Table_AW_AT(h_system_tables(h_i)) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_LOCK.Lock_Refresh_MV(h_system_tables(h_i)) THEN
RAISE e_could_not_get_lock;
IF NOT BSC_UPDATE_SUM.Calculate_Sum_Table_MV_AT(h_system_tables(h_i),
h_calculated_sys_tables,
h_num_calculated_sys_tables,
h_system_tables,
h_num_system_tables) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_LOCK.Lock_Refresh_Sum_Table(h_system_tables(h_i)) THEN
RAISE e_could_not_get_lock;
IF NOT BSC_UPDATE_SUM.Calculate_Sum_Table_AT(h_system_tables(h_i)) THEN
RAISE e_unexpected_error;
h_message := BSC_UPDATE_UTIL.Get_Message('BSC_SUMTABLE_CALCULATED');
h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_system_tables(h_i));
BSC_UPDATE_LOG.Write_Line_Log(h_message,
BSC_UPDATE_LOG.OUTPUT);
IF NOT BSC_UPDATE_LOCK.Lock_Period_Indicators(h_system_tables(h_i)) THEN
RAISE e_could_not_get_lock;
BSC_UPDATE_UTIL.Update_Kpi_Table_Time_Stamp(h_system_tables(h_i));
IF BSC_UPDATE_UTIL.Is_Kpi_In_Production(h_indicators_aw(h_i)) THEN
IF g_kpi_mode THEN
IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Number(h_indicators_aw(h_i),
g_indicators,
g_num_indicators) THEN
h_calc_aw_kpi := TRUE;
BSC_UPDATE_LOG.Write_Line_log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_IVIEWER', 'REFRESH')||
BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
' '||h_indicators_aw(h_i),
BSC_UPDATE_LOG.OUTPUT);
IF NOT BSC_UPDATE_LOCK.Lock_Refresh_AW_Indicator(h_indicators_aw(h_i)) THEN
RAISE e_could_not_get_lock;
BSC_UPDATE_SUM.Refresh_AW_Kpi_AT(h_indicators_aw(h_i));
IF NOT Update_Stage_Input_Tables(LC_RUNNING_STATUS, LC_SYSTEM_UPDATED_STAGE, FALSE) 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;
IF BSC_UPDATE_UTIL.Is_Kpi_In_Production(h_color_indicators(h_i)) THEN
IF g_kpi_mode THEN
IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Number(h_color_indicators(h_i),
g_indicators,
g_num_indicators) THEN
h_calc_color := TRUE;
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_Period_Indicator(h_color_indicators(h_i)) THEN
RAISE e_could_not_get_lock;
IF NOT Update_Indicator_Period(h_color_indicators(h_i)) THEN
RAISE e_unexpected_error;
IF NOT Update_Kpis_Prototype_Flag(h_color_indicators(h_i)) THEN
RAISE e_unexpected_error;
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;
UPDATE bsc_kpi_defaults_b SET last_update_date = SYSDATE
WHERE indicator = h_color_indicators(h_i);
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_Indicator(h_color_indicators(h_i)) THEN
RAISE e_could_not_get_lock;
UPDATE bsc_kpis_b
SET prototype_flag = 0, last_updated_by = BSC_APPS.fnd_global_user_id, last_update_date = SYSDATE
WHERE indicator = h_color_indicators(h_i) AND prototype_flag IN (6, 7);
UPDATE bsc_kpi_analysis_measures_b
SET prototype_flag = 0
WHERE indicator = h_color_indicators(h_i) AND prototype_flag = 7;
IF NOT BSC_UPDATE_LOCK.Lock_Update_Date THEN
RAISE e_could_not_get_lock;
IF NOT BSC_UPDATE_UTIL.Write_Init_Variable_Value('UPDATE_DATE', TO_CHAR(SYSDATE, 'DD/MM/YYYY')) THEN
RAISE e_unexpected_error;
IF NOT Update_Stage_Input_Tables(LC_RUNNING_STATUS, LC_COMPLETED_STAGE, FALSE) THEN
RAISE e_unexpected_error;
IF NOT Update_Status_All_Input_Tables(LC_RUNNING_STATUS, LC_COMPLETED_STATUS, NULL) THEN
RAISE e_unexpected_error;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_DATATABLE_UPDATED'), BSC_UPDATE_LOG.OUTPUT);
BSC_MESSAGE.Add (x_message => BSC_UPDATE_UTIL.Get_Message('BSC_DATATABLE_UPDATE_FAILED'),
x_source => 'BSC_UPDATE.Process_Input_Tables');
h_b := Update_Status_All_Input_Tables(LC_RUNNING_STATUS, LC_ERROR_STATUS, LC_PROGRAM_ERR);
x_source => 'BSC_UPDATE.Process_Input_Tables');
h_b := Update_Status_All_Input_Tables(LC_RUNNING_STATUS, LC_ERROR_STATUS, LC_PROGRAM_ERR);
x_source => 'BSC_UPDATE.Process_Input_Tables');
h_b := Update_Status_All_Input_Tables(LC_RUNNING_STATUS, LC_ERROR_STATUS, LC_PROGRAM_ERR);
x_input_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_input_tables IN NUMBER,
x_start_from IN NUMBER
) RETURN BOOLEAN IS
PRAGMA AUTONOMOUS_TRANSACTION;
p_base_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
p_num_base_tables IN NUMBER
) RETURN BOOLEAN IS
e_unexpected_error EXCEPTION;
h_system_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
h_calculated_sys_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
h_refreshed_mvs BSC_UPDATE_UTIL.t_array_of_varchar2;
h_kpis BSC_UPDATE_UTIL.t_array_kpis;
IF NOT Insert_Affected_Tables(p_base_tables, p_num_base_tables, h_system_tables, h_num_system_tables) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_system_tables(h_i),
h_calculated_sys_tables,
h_num_calculated_sys_tables) THEN
-- The table has not been calculated yet
-- Check if the table can be calculated
h_b := Can_Calculate_Sys_Table(h_system_tables(h_i),
h_calculated_sys_tables,
h_num_calculated_sys_tables,
h_system_tables,
h_num_system_tables);
h_num_kpis := BSC_UPDATE_UTIL.Get_Kpis_Using_Table(h_system_tables(h_i), h_kpis);
IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(mv_name,
h_refreshed_mvs,
h_num_refreshed_mvs) THEN
--LOCKING: Lock the objects required for the mv refresh
--LOCKING: review that no commits between this point and the commit to
-- to release the locks
IF NOT BSC_UPDATE_LOCK.Lock_Refresh_MV(h_system_tables(h_i)) THEN
RAISE e_could_not_get_lock;
IF NOT BSC_UPDATE_SUM.Refresh_Zero_MVs_AT(h_system_tables(h_i),
mv_name, h_error_refresh) THEN
RAISE e_error_refresh_zero;
x_source => 'BSC_UPDATE.Refresh_System_MVs');
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_DATATABLE_UPDATE_FAILED'),
x_source => 'BSC_UPDATE.Refresh_System_MVs');
x_source => 'BSC_UPDATE.Refresh_System_MVs');
BSC_MESSAGE.Add(x_message => 'BSC_UPDATE_SUM.Refresh_Zero_MVs '||mv_name||' '||h_error_refresh,
x_source => 'BSC_UPDATE.Refresh_System_MVs');
x_source => 'BSC_UPDATE.Refresh_System_MVs');
p_base_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
p_num_base_tables IN NUMBER
) RETURN BOOLEAN IS
e_unexpected_error EXCEPTION;
h_system_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
h_calculated_sys_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
h_refreshed_mvs BSC_UPDATE_UTIL.t_array_of_varchar2;
IF NOT Insert_Affected_Tables(p_base_tables, p_num_base_tables, h_system_tables, h_num_system_tables) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_system_tables(h_i),
h_calculated_sys_tables,
h_num_calculated_sys_tables) THEN
-- The table has not been calculated yet
-- Check if the table can be calculated
h_b := Can_Calculate_Sys_Table(h_system_tables(h_i),
h_calculated_sys_tables,
h_num_calculated_sys_tables,
h_system_tables,
h_num_system_tables);
IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(mv_name,
h_refreshed_mvs,
h_num_refreshed_mvs) THEN
IF NOT BSC_BIA_WRAPPER.Refresh_Summary_MV(mv_name, h_error_refresh) THEN
RAISE e_error_refresh;
IF NOT BSC_UPDATE_SUM.Refresh_Zero_MVs(h_system_tables(h_i),
mv_name, h_error_refresh) THEN
RAISE e_error_refresh_zero;
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_DATATABLE_UPDATE_FAILED'),
x_source => 'BSC_UPDATE.Refresh_System_MVs_Mig');
x_source => 'BSC_UPDATE.Refresh_System_MVs_Mig');
BSC_MESSAGE.Add(x_message => 'BSC_UPDATE_SUM.Refresh_Zero_MVs '||mv_name||' '||h_error_refresh,
x_source => 'BSC_UPDATE.Refresh_System_MVs_Mig');
x_source => 'BSC_UPDATE.Refresh_System_MVs_Mig');
e_update_error EXCEPTION;
Execute_Update_Process(TO_NUMBER(x_process_id), x_process_name, x_parameter_1);
h_source := 'BSC_UPDATE.EXECUTE_UPDATE_PROCESS';
SELECT count(*)
INTO h_count
FROM bsc_message_logs
WHERE type = 0 AND UPPER(source) = h_source AND last_update_login = h_sessionid;
RAISE e_update_error;
SELECT count(*)
INTO h_count
FROM bsc_db_loader_control
WHERE process_id = g_process_id AND status IN (LC_ERROR_STATUS, LC_NO_DATA_STATUS);
X_Source => 'BSC_UPDATE.Run_Concurrent_Loader_Apps',
X_Mode => 'I'
);
BSC_UPDATE_LOG.Write_Errors_To_Log;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
WHEN e_update_error THEN
-- LOCKING
BSC_LOCKS_PUB.Remove_System_Lock;
ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
x_source => 'BSC_UPDATE.Run_Concurrent_Loader_Apps',
x_mode => 'I');
BSC_UPDATE_LOG.Write_Errors_To_Log;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
x_source => 'BSC_UPDATE.Run_Concurrent_Loader_Apps',
x_mode => 'I');
BSC_UPDATE_LOG.Write_Errors_To_Log;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
e_update_error EXCEPTION;
h_input_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
h_indicators BSC_UPDATE_UTIL.t_array_of_number;
RAISE e_update_error;
raise e_update_error;
raise e_update_error;
Execute_Update_Process(TO_NUMBER(x_process_id), PC_LOADER_PROCESS, NULL);
h_source := 'BSC_UPDATE.EXECUTE_UPDATE_PROCESS';
h_sql := 'SELECT count(*)'||
' FROM bsc_message_logs'||
' WHERE type = :1'||
' AND UPPER(source) = :2 AND last_update_login = :3';
SELECT count(*)
INTO h_count
FROM bsc_message_logs
WHERE type = 0 AND UPPER(source) = h_source AND last_update_login = h_sessionid;
RAISE e_update_error;
h_sql := 'SELECT count(*)'||
' FROM bsc_db_loader_control'||
' WHERE process_id = :1 AND status IN (:2, :3)';
SELECT count(*)
INTO h_count
FROM bsc_db_loader_control
WHERE process_id = g_process_id AND status IN (LC_ERROR_STATUS, LC_NO_DATA_STATUS);
X_Source => 'BSC_UPDATE.Run_Concurrent_Loader_Apps',
X_Mode => 'I'
);
BSC_UPDATE_LOG.Write_Errors_To_Log;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
WHEN e_update_error THEN
-- LOCKING
BSC_LOCKS_PUB.Remove_System_Lock;
ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
x_source => 'BSC_UPDATE.Run_Concurrent_Loader_Apps',
x_mode => 'I');
BSC_UPDATE_LOG.Write_Errors_To_Log;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
e_update_error EXCEPTION;
Execute_Update_Process(TO_NUMBER(x_process_id), PC_LOAD_DIMENSIONS, NULL);
h_source := 'BSC_UPDATE.EXECUTE_UPDATE_PROCESS';
h_sql := 'SELECT count(*)'||
' FROM bsc_message_logs'||
' WHERE type = :1'||
' AND UPPER(source) = :2 AND last_update_login = :3';
SELECT count(*)
INTO h_count
FROM bsc_message_logs
WHERE type = 0 AND UPPER(source) = h_source AND last_update_login = h_sessionid;
RAISE e_update_error;
h_sql := 'SELECT count(*)'||
' FROM bsc_db_loader_control'||
' WHERE process_id = :1 AND status IN (:2, :3)';
SELECT count(*)
INTO h_count
FROM bsc_db_loader_control
WHERE process_id = g_process_id AND status IN (LC_ERROR_STATUS, LC_NO_DATA_STATUS);
X_Source => 'BSC_UPDATE.Run_Concurrent_Loader_Apps',
X_Mode => 'I'
);
BSC_UPDATE_LOG.Write_Errors_To_Log;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
WHEN e_update_error THEN
-- LOCKING
BSC_LOCKS_PUB.Remove_System_Lock;
ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
x_source => 'BSC_UPDATE.Run_Concurrent_Loader_Dim_Apps',
x_mode => 'I');
BSC_UPDATE_LOG.Write_Errors_To_Log;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
e_update_error EXCEPTION;
RAISE e_update_error;
Execute_Update_Process(TO_NUMBER(x_process_id), PC_YEAR_CHANGE_PROCESS, x_calendars);
h_source := 'BSC_UPDATE.EXECUTE_UPDATE_PROCESS';
SELECT count(*)
INTO h_count
FROM bsc_message_logs
WHERE type = 0 AND UPPER(source) = h_source AND last_update_login = g_session_id;
RAISE e_update_error;
X_Source => 'BSC_UPDATE.Run_Concurrent_Loader_Apps',
X_Mode => 'I'
);
BSC_UPDATE_LOG.Write_Errors_To_Log;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
WHEN e_update_error THEN
-- LOCKING
BSC_LOCKS_PUB.Remove_System_Lock;
ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
x_source => 'BSC_UPDATE.Run_change_current_year',
x_mode => 'I');
BSC_UPDATE_LOG.Write_Errors_To_Log;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
e_update_error EXCEPTION;
h_indicators BSC_UPDATE_UTIL.t_array_of_number;
h_input_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
h_prod_indicators BSC_UPDATE_UTIL.t_array_of_number;
SELECT prototype_flag, name
FROM bsc_kpis_vl
WHERE indicator = p_kpi;
RAISE e_update_error;
h_num_indicators := BSC_UPDATE_UTIL.Decompose_Numeric_List(x_indicators,
h_indicators,
',');
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_CANNOT_LOAD_OBJS_IN_PROT'),
BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(h_indicators(h_i)||' '||h_indic_name, BSC_UPDATE_LOG.OUTPUT);
SELECT bsc_db_process_id_s.nextval
INTO h_process_id
FROM DUAL;
INSERT INTO bsc_db_process_control (process_id, process_name,
creation_date, created_by, last_update_date,last_updated_by,
last_update_login, status)
VALUES (h_process_id, PC_LOADER_PROCESS,
SYSDATE, g_user_id, SYSDATE, g_user_id, g_session_id, PC_PENDING_STATUS);
INSERT INTO bsc_db_loader_control (process_id, input_table_name, status,
error_code, stage, last_stage_flag)
VALUES (h_process_id, h_input_tables(h_i), LC_PENDING_STATUS, NULL, LC_PENDING_STAGE, 0);
Execute_Update_Process(h_process_id, PC_LOADER_PROCESS, NULL);
Execute_Update_Process(h_process_id, PC_LOADER_PROCESS, h_lst_prod_indicators);
h_source := 'BSC_UPDATE.EXECUTE_UPDATE_PROCESS';
SELECT count(*)
INTO h_count
FROM bsc_message_logs
WHERE type = 0 AND UPPER(source) = h_source AND last_update_login = g_session_id;
RAISE e_update_error;
SELECT count(*)
INTO h_count
FROM bsc_db_loader_control
WHERE process_id = g_process_id AND status IN (LC_ERROR_STATUS, LC_NO_DATA_STATUS);
X_Source => 'BSC_UPDATE.Run_Concurrent_Loader_Apps',
X_Mode => 'I'
);
BSC_UPDATE_LOG.Write_Errors_To_Log;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
WHEN e_update_error THEN
-- LOCKING
BSC_LOCKS_PUB.Remove_System_Lock;
ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
BSC_UPDATE_LOG.Write_Errors_To_Log;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
x_source => 'BSC_UPDATE.Load_Indicators_Data',
x_mode => 'I');
BSC_UPDATE_LOG.Write_Errors_To_Log;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
e_update_error EXCEPTION;
h_indicators BSC_UPDATE_UTIL.t_array_of_number;
h_input_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
l_input_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
h_dbi_dimensions BSC_UPDATE_UTIL.t_array_of_varchar2;
h_dbi_dim_requests BSC_UPDATE_UTIL.t_array_of_number;
RAISE e_update_error;
h_num_indicators := BSC_UPDATE_UTIL.Decompose_Numeric_List(x_indicators,
h_indicators,
',');
IF NOT BSC_UPDATE_DIM.Get_Dbi_Dims_Kpis(h_indicators, h_num_indicators, h_dbi_dimensions, h_num_dbi_dimensions) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_DIM.Create_Dbi_Dim_Temp_Tables THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_DIM.Create_AW_Dim_Temp_Tables THEN
RAISE e_unexpected_error;
h_dbi_dim_requests.delete;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_IVIEWER', 'REFRESH')||
' '||h_dbi_dimensions(h_i), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_SUBMMITREQ_FAILED'), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_LOADER_REQ_ID')||
' '||h_dbi_dim_requests(h_i), BSC_UPDATE_LOG.OUTPUT);
SELECT bsc_db_process_id_s.nextval
INTO h_process_id
FROM DUAL;
INSERT INTO bsc_db_process_control (process_id, process_name,
creation_date, created_by, last_update_date,last_updated_by,
last_update_login, status)
VALUES (h_process_id, PC_LOAD_DIMENSIONS,
SYSDATE, g_user_id, SYSDATE, g_user_id, g_session_id, PC_PENDING_STATUS);
INSERT INTO bsc_db_loader_control (process_id, input_table_name, status,
error_code, stage, last_stage_flag)
VALUES (h_process_id, h_input_tables(h_i), LC_PENDING_STATUS, NULL, LC_PENDING_STAGE, 0);
Execute_Update_Process(h_process_id, PC_LOAD_DIMENSIONS, x_indicators);
h_source := 'BSC_UPDATE.EXECUTE_UPDATE_PROCESS';
SELECT count(*)
INTO h_count
FROM bsc_message_logs
WHERE type = 0 AND UPPER(source) = h_source AND last_update_login = g_session_id;
RAISE e_update_error;
SELECT count(*)
INTO h_count
FROM bsc_db_loader_control
WHERE process_id = g_process_id AND status IN (LC_ERROR_STATUS, LC_NO_DATA_STATUS);
X_Source => 'BSC_UPDATE.Run_Concurrent_Loader_Apps',
X_Mode => 'I'
);
BSC_UPDATE_LOG.Write_Errors_To_Log;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
WHEN e_update_error THEN
-- LOCKING
BSC_LOCKS_PUB.Remove_System_Lock;
ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
BSC_UPDATE_LOG.Write_Errors_To_Log;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
x_source => 'BSC_UPDATE.Load_Indicators_Dims',
x_mode => 'I');
BSC_UPDATE_LOG.Write_Errors_To_Log;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
| PROCEDURE Delete_Indicators_Data
+============================================================================*/
PROCEDURE Delete_Indicators_Data (
ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2,
x_indicators IN VARCHAR2,
x_keep_input_table_data IN VARCHAR2
) IS
e_system_lock EXCEPTION;
e_update_error EXCEPTION;
h_indicators BSC_UPDATE_UTIL.t_array_of_number;
RAISE e_update_error;
h_num_indicators := BSC_UPDATE_UTIL.Decompose_Numeric_List(x_indicators,
h_indicators,
',');
SELECT bsc_db_process_id_s.nextval
INTO h_process_id
FROM DUAL;
INSERT INTO bsc_db_process_control (process_id, process_name,
creation_date, created_by, last_update_date,last_updated_by,
last_update_login, status)
VALUES (h_process_id, PC_DELETE_KPI_DATA_PROCESS,
SYSDATE, g_user_id, SYSDATE, g_user_id, g_session_id, PC_PENDING_STATUS);
INSERT INTO bsc_db_loader_control (process_id, input_table_name, status,
error_code, stage, last_stage_flag)
VALUES (h_process_id, h_indicators(h_i), NULL, NULL, NULL, 0);
Execute_Update_Process(h_process_id, PC_DELETE_KPI_DATA_PROCESS, NULL);
h_source := 'BSC_UPDATE.EXECUTE_UPDATE_PROCESS';
SELECT count(*)
INTO h_count
FROM bsc_message_logs
WHERE type = 0 AND UPPER(source) = h_source AND last_update_login = g_session_id;
RAISE e_update_error;
X_Source => 'BSC_UPDATE.Run_Concurrent_Loader_Apps',
X_Mode => 'I'
);
BSC_UPDATE_LOG.Write_Errors_To_Log;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
WHEN e_update_error THEN
-- LOCKING
BSC_LOCKS_PUB.Remove_System_Lock;
ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
x_source => 'BSC_UPDATE.Delete_Indicators_Data',
x_mode => 'I');
BSC_UPDATE_LOG.Write_Errors_To_Log;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
END Delete_Indicators_Data;
x_indicators IN BSC_UPDATE_UTIL.t_array_of_number,
x_num_indicators IN NUMBER,
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_system_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
h_sql := 'SELECT table_name'||
' FROM bsc_kpi_data_tables'||
' WHERE ('||h_where_indics||') AND table_name IS NOT NULL';
IF NOT BSC_UPDATE_INC.Get_Input_Tables(x_input_tables, x_num_input_tables, h_system_tables, h_num_system_tables) THEN
RAISE e_unexpected_error;
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_RETR_ITABLES_FAILED'),
x_source => 'BSC_UPDATE.Get_Input_Tables_Kpis');
x_source => 'BSC_UPDATE.Get_Input_Tables_Kpis');
x_input_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_input_tables IN NUMBER,
x_indicators IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
x_num_indicators IN OUT NOCOPY NUMBER
)return boolean is
--
e_unexpected_error exception;
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_RETR_ITABLES_FAILED'),
x_source => 'BSC_UPDATE.get_kpi_for_input_tables');
x_source => 'BSC_UPDATE.get_kpi_for_input_tables');
x_indicators IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
x_num_indicators IN OUT NOCOPY NUMBER
)return boolean is
--
cursor c1(p_table varchar2) is select table_name from bsc_db_tables_rels where source_table_name=p_table;
cursor c2(p_table varchar2) is select distinct indicator from bsc_kpi_data_tables where table_name=p_table;
l_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
x_array BSC_UPDATE_UTIL.t_array_of_number,
x_num_array NUMBER
)return boolean is
Begin
for i in 1..x_num_array loop
if x_array(i)=x_value then
return true;
x_indicators IN BSC_UPDATE_UTIL.t_array_of_number,
x_num_indicators IN NUMBER,
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_sql := 'SELECT DISTINCT source_table_name'||
' FROM bsc_kpi_dim_levels_vl k, bsc_db_tables_rels r'||
' WHERE ('||h_where_indics||') AND k.level_source = :1 '||
' AND k.level_table_name = r.table_name';
h_sql := 'SELECT DISTINCT source_table_name'||
' FROM bsc_kpi_dim_levels_vl k, bsc_db_tables_rels r'||
' WHERE ('||h_where_indics||') AND k.level_source = :1 '||
' AND k.table_relation = r.table_name';
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_RETR_ITABLES_FAILED'),
x_source => 'BSC_UPDATE.Get_Dim_Input_Tables_Kpis');
x_source => 'BSC_UPDATE.Get_Dim_Input_Tables_Kpis');
UPDATE
bsc_db_process_control
SET
last_update_date = SYSDATE,
last_updated_by = g_user_id,
last_update_login = g_session_id,
status = x_status,
end_time = SYSDATE
WHERE
process_id = g_process_id;
x_source => 'BSC_UPDATE.Set_PStatus_Finished');
IF BSC_UPDATE_LOG.Log_File_Name IS NULL THEN
h_complete_log_file_name := NULL;
h_complete_log_file_name := BSC_UPDATE_LOG.Log_File_Dir||'/'||BSC_UPDATE_LOG.Log_File_Name;
UPDATE
bsc_db_process_control
SET
last_update_date = SYSDATE,
last_updated_by = g_user_id,
last_update_login = g_session_id,
status = PC_RUNNING_STATUS,
log_file_location = h_complete_log_file_name,
start_time = SYSDATE
WHERE
process_id = g_process_id;
x_source => 'BSC_UPDATE.Set_PStatus_Running');
| FUNCTION Update_Indicator_Period
+============================================================================*/
FUNCTION Update_Indicator_Period (
x_indicator IN NUMBER
) RETURN BOOLEAN IS
BEGIN
-- The update period of an indicator is the minimun period of the tables
-- used by the indicator.
-- BSC-PMF Integration:Fix this query by adding NVL, to avoid assigning NULL
-- to the current period when all of the analysis options of a kpi are for PMF measures.
UPDATE bsc_kpi_periodicities p
SET current_period = (
SELECT DISTINCT
NVL(MIN(t.current_period), p.current_period)
FROM
bsc_kpi_data_tables k,
bsc_db_tables t
WHERE
k.table_name = t.table_name AND
k.indicator = p.indicator AND
t.periodicity_id = p.periodicity_id)
WHERE p.indicator = x_indicator;
x_source => 'BSC_UPDATE.Update_Indicator_Period');
END Update_Indicator_Period;
| FUNCTION Update_Indicators_Periods
+============================================================================*/
FUNCTION Update_Indicators_Periods RETURN BOOLEAN IS
BEGIN
-- The update period of an indicator is the minimun period of the tables
-- used by the indicator.
-- BSC-PMF Integration:Fix this query by adding NVL, to avoid assigning NULL
-- to the current period when all of the analysis options of a kpi are for PMF measures.
UPDATE bsc_kpi_periodicities p
SET current_period = (
SELECT DISTINCT
NVL(MIN(t.current_period), p.current_period)
FROM
bsc_kpi_data_tables k,
bsc_db_tables t
WHERE
k.table_name = t.table_name AND
k.indicator = p.indicator AND
t.periodicity_id = p.periodicity_id)
WHERE p.indicator IN (
SELECT
indicator
FROM
bsc_kpis_vl);
x_source => 'BSC_UPDATE.Update_Indicators_Periods');
END Update_Indicators_Periods;
| FUNCTION Update_Stage_Input_Table
+============================================================================*/
FUNCTION Update_Stage_Input_Table(
x_input_table IN VARCHAR2,
x_target_stage IN VARCHAR2
) RETURN BOOLEAN IS
BEGIN
UPDATE
bsc_db_loader_control
SET
stage = x_target_stage
WHERE
input_table_name = x_input_table AND
process_id = g_process_id;
x_source => 'BSC_UPDATE.Update_Stage_Input_Table');
END Update_Stage_Input_Table;
| FUNCTION Update_Stage_Input_Tables
+============================================================================*/
FUNCTION Update_Stage_Input_Tables(
x_current_status IN VARCHAR2,
x_target_stage IN VARCHAR2,
x_last_stage_flag IN BOOLEAN
) RETURN BOOLEAN IS
BEGIN
IF x_last_stage_flag THEN
UPDATE
bsc_db_loader_control
SET
last_stage_flag = 0
WHERE
input_table_name IN (
SELECT
input_table_name
FROM
bsc_db_loader_control
WHERE
process_id = g_process_id AND
status = x_current_status
);
UPDATE
bsc_db_loader_control
SET
stage = x_target_stage,
last_stage_flag = 1
WHERE
process_id = g_process_id AND
status = x_current_status;
UPDATE
bsc_db_loader_control
SET
stage = x_target_stage
WHERE
process_id = g_process_id AND
status = x_current_status;
x_source => 'BSC_UPDATE.Update_Stage_Input_Tables');
END Update_Stage_Input_Tables;
| FUNCTION Update_Status_All_Input_Tables
+============================================================================*/
FUNCTION Update_Status_All_Input_Tables(
x_current_status IN VARCHAR2,
x_target_status IN VARCHAR2,
x_error_code IN VARCHAR2
) RETURN BOOLEAN IS
BEGIN
UPDATE
bsc_db_loader_control
SET
status = x_target_status,
error_code = x_error_code
WHERE
status = x_current_status AND
process_id = g_process_id;
x_source => 'BSC_UPDATE.Update_Status_All_Input_Tables');
END Update_Status_All_Input_Tables;
| FUNCTION Update_Status_Input_Table
+============================================================================*/
FUNCTION Update_Status_Input_Table(
x_input_table IN VARCHAR2,
x_target_status IN VARCHAR2,
x_error_code IN VARCHAR2
) RETURN BOOLEAN IS
BEGIN
UPDATE
bsc_db_loader_control
SET
status = x_target_status,
error_code = x_error_code
WHERE
input_table_name = x_input_table AND
process_id = g_process_id;
x_source => 'BSC_UPDATE.Update_Status_Input_Table');
END Update_Status_Input_Table;
x_requests IN BSC_UPDATE_UTIL.t_array_of_number,
x_num_requests IN NUMBER
) RETURN BOOLEAN IS
h_i NUMBER;
x_source => 'BSC_UPDATE.Wait_For_Requests');
h_update_date VARCHAR2(200);
c_status_sql VARCHAR2(2000) := 'SELECT input_table_name, status, error_code'||
' FROM bsc_db_loader_control'||
' WHERE process_id = :1'||
' ORDER BY input_table_name';
SELECT input_table_name, status, error_code
FROM bsc_db_loader_control
WHERE process_id = p_process_id
ORDER BY input_table_name;
c_invalid_codes_sql VARCHAR2(2000) := 'SELECT input_table_name, column_name, invalid_code'||
' FROM bsc_db_validation'||
' WHERE input_table_name IN ('||
' SELECT table_name'||
' FROM bsc_db_tables'||
' WHERE table_type = DECODE(:1, :2, :3, :4))'||
' ORDER BY input_table_name';
SELECT input_table_name, column_name, invalid_code
FROM bsc_db_validation
WHERE input_table_name IN (
SELECT input_table_name
FROM bsc_db_loader_control
WHERE process_id = p_process_id
)
ORDER BY input_table_name;
c_tables_sql VARCHAR2(2000) := 'SELECT lc.input_table_name, t.periodicity_id,'||
' p.name, t.current_period, t.current_subperiod'||
' FROM bsc_db_tables t, bsc_db_loader_control lc, bsc_sys_periodicities_vl p'||
' WHERE lc.input_table_name = t.table_name AND'||
' lc.process_id = :1 AND t.periodicity_id = p.periodicity_id';
SELECT lc.input_table_name, t.periodicity_id,
p.name, t.current_period, t.current_subperiod
FROM bsc_db_tables t, bsc_db_loader_control lc, bsc_sys_periodicities_vl p
WHERE lc.input_table_name = t.table_name AND
lc.process_id = p_process_id AND t.periodicity_id = p.periodicity_id;
BSC_UPDATE_LOG.Write_Line_Log('', BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROCESS_RESULT'), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
BSC_UPDATE_LOG.OUTPUT);
IF NOT BSC_UPDATE_UTIL.Get_Init_Variable_Value('UPDATE_DATE', h_update_date) THEN
RAISE e_unexpected_error;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_LAST_UPDATE')||' '||h_update_date, BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log('', BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'INPUT_TABLE_NAME'), C_TABLE_W)||
RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'STATUS'), C_STATUS_W),
BSC_UPDATE_LOG.OUTPUT);
h_line := h_line||BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_UPDATE_FAILED');
h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_INVALID_CODES_FOUND');
h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_LOADER_PROC_FAILED');
h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_SRCFILE_FAILED');
h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_SRC_FIELDNUMBER_FAILED');
h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_SRC_NULL_KEYVALUES');
ELSIF h_error_code = LC_UPLOAD_INSERT_ERR THEN
h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_INVALID_DATATYPE');
h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_SRCFILE_NOT_FOUND');
h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_EXCEL_CONNECTION_FAILED');
h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_NOT_FOUND');
h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_IS_INVALID');
h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_FAILED');
h_line := h_line||BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_UPDATE_FAILED')||' '||
BSC_UPDATE_UTIL.Get_Message('BSC_ITABLE_EMPTY');
h_line := h_line||BSC_UPDATE_UTIL.Get_Message('BSC_UPDATED_STATUS');
BSC_UPDATE_LOG.Write_Line_Log(h_line, BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log('', BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'INVALID_RECORDS'),
BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'INPUT_TABLE_NAME'), C_TABLE_W)||
RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'COLUMN'), C_COLUMN_W)||
RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'INVALID_CODE'), C_INVALID_CODE_W),
BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(RPAD(h_input_table_name, C_TABLE_W)||
RPAD(h_column_name, C_COLUMN_W)||
RPAD(h_invalid_code, C_INVALID_CODE_W), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log('', BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'TABLE_UPDATE_PERIOD'),
BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'INPUT_TABLE_NAME'), C_TABLE_NAME_W)||
RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'PERIODICITY'), C_PERIODICITY_W)||
RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'PERIOD'), C_PERIOD_W),
BSC_UPDATE_LOG.OUTPUT);
h_periodicity_type := BSC_UPDATE_UTIL.Get_Periodicity_Type(h_periodicity_id);
BSC_UPDATE_LOG.Write_Line_Log(h_line, BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log('', BSC_UPDATE_LOG.OUTPUT);
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_WR_LOGFILE_RES_FAILED'),
x_source => 'BSC_UPDATE.Write_Result_Log');
x_source => 'BSC_UPDATE.Write_Result_Log');
FUNCTION Get_Indicator_List(x_number_array IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number)
RETURN NUMBER IS
CURSOR cIndics is
SELECT value_n
FROM BSC_TMP_BIG_IN_COND
WHERE session_id = -500 and variable_id = -500;
DELETE BSC_TMP_BIG_IN_COND where session_id = -500 and variable_id = -500;
x_system_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_system_tables IN NUMBER
) IS
h_where_cond VARCHAR2(32700);
h_sql := 'select indicator, name'||
' from bsc_kpis_vl'||
' where prototype_flag NOT IN (:1, :2, :3) and '||h_where_cond;
h_message := BSC_UPDATE_UTIL.Get_Message('BSC_CANNOT_LOAD_OBJS_IN_PROT');
BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(h_kpi||' '||h_name, BSC_UPDATE_LOG.OUTPUT);
h_sql := 'select indicator, name'||
' from bsc_kpis_vl'||
' where prototype_flag NOT IN (:1, :2, :3) and'||
' indicator in (select indicator'||
' from bsc_kpi_data_tables'||
' where '||h_where_cond||
' )';
h_message := BSC_UPDATE_UTIL.Get_Message('BSC_CANNOT_LOAD_OBJS_IN_PROT');
BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(h_kpi||' '||h_name, BSC_UPDATE_LOG.OUTPUT);