[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NVL(current_period, 0)
INTO h_current_period
FROM bsc_db_tables
WHERE table_name = x_base_table;
h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity_base_table);
h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_periodicity_base_table);
h_periodicity_type_input_table := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_periodicity_input_table);
h_periodicity_type_base_table := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_periodicity_base_table);
SELECT week52
INTO x_per_base_table
FROM bsc_db_week_maps
WHERE year = x_current_fy AND month = x_per_input_table AND
week = x_subper_input_table AND calendar_id = h_calendar_id;
h_edw_flag := BSC_UPDATE_UTIL.Get_Periodicity_EDW_Flag(x_periodicity_base_table);
h_base_calendar_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity_base_table);
h_sql := 'SELECT '||h_base_calendar_col_name||' '||
'FROM bsc_db_calendar '||
'WHERE year = :1 '||
'AND month = :2 '||
'AND day30 = :3 '||
'AND calendar_id = :4';
h_input_calendar_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity_input_table);
h_sql := 'SELECT MAX('||h_base_calendar_col_name||') '||
'FROM bsc_db_calendar '||
'WHERE year = :1 '||
'AND '||h_input_calendar_col_name||' = :2 '||
'AND calendar_id = :3';
h_sql := 'SELECT MAX(bsc_target) '||
'FROM bsc_edw_time_map '||
'WHERE year = :1 '||
'AND bsc_source = :2';
x_source => 'BSC_UPDATE_BASE.Calc_New_Period_Base_Table');
h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_periodicity);
h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(h_calendar_id);
h_target_flag := BSC_UPDATE_UTIL.Get_Table_Target_Flag(x_input_table);
SELECT NVL(current_period, 0), NVL(current_subperiod, 0)
INTO h_current_period, h_current_subperiod
FROM bsc_db_tables
WHERE table_name = x_input_table;
h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
h_sql := 'SELECT MAX('||x_period_col_name||') '||
'FROM '||x_input_table||' '||
'WHERE year = :1';
h_periodicity_type := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_periodicity);
h_sql := 'SELECT MAX(p.period_id)'||
' FROM '||x_input_table||' i, bsc_sys_periods p'||
' WHERE p.periodicity_id = :1 AND TRUNC(i.time_fk) = TRUNC(TO_DATE(p.time_fk, ''MM/DD/YYYY''))'||
' AND p.year =:2';
h_sql := 'SELECT MAX(p.period_id)'||
' FROM '||x_input_table||' i, bsc_sys_periods p'||
' WHERE p.periodicity_id = :1 AND i.time_fk = p.time_fk AND p.year =:2';
h_sql := 'SELECT MAX('||x_subperiod_col_name||') '||
'FROM '||x_input_table||' '||
'WHERE year = :1 '||
'AND '||x_period_col_name||' = :2';
x_source => 'BSC_UPDATE_BASE.Calc_New_Period_Input_Table');
h_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
h_data_formulas BSC_UPDATE_UTIL.t_array_of_varchar2;
h_data_proj_methods BSC_UPDATE_UTIL.t_array_of_number;
h_data_measure_types BSC_UPDATE_UTIL.t_array_of_number;
h_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
h_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
h_source_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
h_source_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
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;
h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
h_periodicity_base_table := BSC_UPDATE_UTIL.Get_Table_Periodicity(x_base_table);
h_periodicity_input_table := BSC_UPDATE_UTIL.Get_Table_Periodicity(x_input_table);
h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(h_periodicity_base_table);
h_current_fy := BSC_UPDATE_UTIL.Get_Calendar_Fiscal_Year(h_calendar_id);
IF NOT BSC_UPDATE_UTIL.Get_Table_Range_Of_Years(x_base_table, h_num_of_years, h_previous_years) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_UTIL.Get_Period_Cols_Names(h_periodicity_input_table, h_period_col_name, h_subperiod_col_name) THEN
RAISE e_unexpected_error;
h_generation_type := BSC_UPDATE_UTIL.Get_Table_Generation_Type(x_base_table);
IF NOT BSC_UPDATE_UTIL.Get_Information_Data_Columns(x_base_table,
h_data_columns,
h_data_formulas,
h_data_proj_methods,
h_data_measure_types,
h_num_data_columns) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_UTIL.Get_Information_Key_Columns(x_base_table,
h_key_columns,
h_key_dim_tables,
h_source_columns,
h_source_dim_tables,
h_num_key_columns) THEN
RAISE e_unexpected_error;
h_b := BSC_UPDATE_CALC.Table_Has_Proj_Calc(x_base_table);
h_current_period := BSC_UPDATE_UTIL.Get_Period_Other_Periodicity(
h_other_periodicity_id,
h_calendar_id,
h_yearly_flag,
h_current_fy,
h_periodicity_base_table,
h_current_per_base_table
);
h_current_period := BSC_UPDATE_UTIL.Get_Period_Other_Periodicity(
h_other_periodicity_id,
h_calendar_id,
h_yearly_flag,
h_current_fy,
h_periodicity_base_table,
h_per_base_table
);
SELECT project_flag INTO h_project_flag
FROM bsc_db_tables
WHERE table_name = x_base_table;
IF NOT BSC_UPDATE_CALC.Delete_Projection_Base_Table(x_base_table,
h_periodicity_base_table,
h_current_per_base_table,
h_per_base_table,
h_data_columns,
h_data_proj_methods,
h_num_data_columns,
h_current_fy,
x_aw_flag,
h_change_vector_value) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_CALC.Delete_Projection_Base_Table(x_base_table,
h_arr_other_periodicities(h_i).periodicity_id,
h_arr_other_periodicities(h_i).current_period,
h_arr_other_periodicities(h_i).new_current_period,
h_data_columns,
h_data_proj_methods,
h_num_data_columns,
h_current_fy,
x_aw_flag,
h_change_vector_value) THEN
RAISE e_unexpected_error;
h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(h_periodicity_base_table);
h_bind_vars_values.delete;
h_sql := 'UPDATE '||x_base_table||
' SET projection = ''N'', change_vector = :1'||
' WHERE YEAR = :2 AND PERIOD > :3 AND PERIOD <= :4 AND TYPE <> :5';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
IF NOT Update_Base_Table(x_base_table,
x_input_table,
h_key_columns,
h_key_dim_tables,
h_num_key_columns,
h_data_columns,
h_data_formulas,
h_data_measure_types,
h_num_data_columns,
h_periodicity_base_table,
h_periodicity_input_table,
h_period_col_name,
h_subperiod_col_name,
h_projection_flag,
h_current_fy,
h_per_base_table,
h_current_per_base_table,
x_correction_flag,
x_aw_flag,
h_change_vector_value
) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_CALC.Create_Proj_Temps(h_periodicity_base_table,
h_current_fy,
h_num_of_years,
h_previous_years,
TRUE) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_CALC.Calculate_Projection(x_base_table,
h_periodicity_base_table,
h_per_base_table,
h_key_columns,
h_num_key_columns,
h_data_columns,
h_data_proj_methods,
h_num_data_columns,
h_current_fy,
h_num_of_years,
h_previous_years,
TRUE,
x_aw_flag,
h_change_vector_value) THEN
RAISE e_unexpected_error;
h_sql := 'UPDATE '||x_base_table||' SET change_vector = :1';
IF NOT BSC_UPDATE_CALC.Create_Proj_Temps(h_arr_other_periodicities(h_i).periodicity_id,
h_current_fy,
h_num_of_years,
h_previous_years,
FALSE) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_CALC.Calculate_Projection(
x_base_table,
h_arr_other_periodicities(h_i).periodicity_id,
h_arr_other_periodicities(h_i).new_current_period,
h_key_columns,
h_num_key_columns,
h_data_columns,
h_data_proj_methods,
h_num_data_columns,
h_current_fy,
h_num_of_years,
h_previous_years,
TRUE,
x_aw_flag,
NULL) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_CALC.Rollup_Projection(
h_arr_other_periodicities(h_i).periodicity_id,
h_arr_other_periodicities(h_i).new_current_period,
h_periodicity_base_table,
h_per_base_table,
h_key_columns,
h_num_key_columns,
h_data_columns,
h_data_formulas,
h_data_measure_types,
h_num_data_columns,
h_current_fy,
TRUE) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_CALC.Merge_Projection(x_base_table,
h_key_columns,
h_num_key_columns,
h_data_columns,
h_num_data_columns,
TRUE,
x_aw_flag) THEN
RAISE e_unexpected_error;
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJ_CALC');
h_b := BSC_UPDATE_CALC.Table_Has_Profit_Calc(x_base_table);
IF NOT BSC_UPDATE_CALC.Calculate_Profit(x_base_table,
h_key_columns,
h_key_dim_tables,
h_num_key_columns,
h_data_columns,
h_num_data_columns,
x_aw_flag,
h_change_vector_value
) THEN
RAISE e_unexpected_error;
bsc_aw_load.update_bt_change_vector(x_base_table, h_change_vector_value);
UPDATE
bsc_db_tables
SET
current_period = h_per_input_table,
current_subperiod = h_subper_input_table
WHERE
table_name = x_input_table;
UPDATE
bsc_db_tables
SET
current_period = h_per_base_table
WHERE
table_name = x_base_table;
BSC_UPDATE_UTIL.Truncate_Table(x_input_table);
BSC_AW_LOAD.update_bt_current_period(x_base_table, h_per_base_table, h_current_fy);
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE');
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_BTABLE_CALCULATION_FAILED'),
x_source => 'BSC_UPDATE_BASE.Calculate_Base_Table');
x_source => 'BSC_UPDATE_BASE.Calculate_Base_Table');
h_table_columns BSC_UPDATE_UTIL.t_array_temp_table_cols;
h_table_columns.delete;
IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
RAISE e_unexpected_error;
h_table_columns.delete;
IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
RAISE e_unexpected_error;
h_table_columns.delete;
IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
RAISE e_unexpected_error;
h_table_columns.delete;
IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
RAISE e_unexpected_error;
h_table_columns.delete;
IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
RAISE e_unexpected_error;
h_table_columns.delete;
IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
RAISE e_unexpected_error;
h_table_columns.delete;
IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
RAISE e_unexpected_error;
h_table_columns.delete;
IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
RAISE e_unexpected_error;
h_table_columns.delete;
IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
RAISE e_unexpected_error;
h_table_columns.delete;
IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
RAISE e_unexpected_error;
h_table_columns.delete;
IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
RAISE e_unexpected_error;
h_table_name := 'BSC_TMP_BASE_UPDATE';
h_table_columns.delete;
IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
RAISE e_unexpected_error;
h_table_columns.delete;
IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
RAISE e_unexpected_error;
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR'),
x_source => 'BSC_UPDATE_BASE.Create_Generic_Temp_Tables');
x_source => 'BSC_UPDATE_BASE.Create_Generic_Temp_Tables');
p_periodicity OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
p_calendar_id OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
p_column_name OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
p_number_periodicity OUT NOCOPY NUMBER
) RETURN BOOLEAN IS
CURSOR c_Calc_Period IS
SELECT TO_NUMBER(C.PARAMETER1) Parameter1, P.CALENDAR_ID Calendar_Id, P.DB_COLUMN_NAME Db_Column_Name
FROM BSC_DB_CALCULATIONS C, BSC_SYS_PERIODICITIES P
WHERE C.Parameter1 = P.Periodicity_Id
AND C.Calculation_Type = 6
AND C.Table_Name = p_table_name;
x_source => 'BSC_UPDATE_BASE.get_base_higher_periodicities');
| FUNCTION Update_Base_Table |
+============================================================================*/
FUNCTION Update_Base_Table(
x_base_tbl VARCHAR2,
x_in_tbl VARCHAR2,
x_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2,
x_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_key_columns NUMBER,
x_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2,
x_data_formulas BSC_UPDATE_UTIL.t_array_of_varchar2,
x_data_measure_types BSC_UPDATE_UTIL.t_array_of_number,
x_num_data_columns NUMBER,
x_base_percode NUMBER,
x_in_percode NUMBER,
x_in_per_fld VARCHAR2,
x_in_subper_fld VARCHAR2,
x_projection_flag VARCHAR2,
x_current_fy NUMBER,
x_current_per_base_table NUMBER,
x_prev_current_period NUMBER, --Fix bug#4235448 Need this parameter
x_correction_flag BOOLEAN,
x_aw_flag BOOLEAN,
x_change_vector_value NUMBER
) RETURN BOOLEAN IS
e_unexpected_error EXCEPTION;
h_lst_select VARCHAR2(32700);
h_key_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
h_data_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
l_periodicity BSC_UPDATE_UTIL.t_array_of_number;
l_calendar_id BSC_UPDATE_UTIL.t_array_of_number;
l_column_name BSC_UPDATE_UTIL.t_array_of_varchar2;
l_bf_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
l_bf_column_formulas BSC_UPDATE_UTIL.t_array_of_varchar2;
h_dbi_dim_data BSC_UPDATE_DIM.t_dbi_dim_data;
h_lst_select := NULL;
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE');
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_ROWID');
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_BU');
h_base_calendar_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_base_percode);
h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_base_percode);
h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(h_calendar_id);
h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_base_percode);
h_lst_key_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns, x_num_key_columns);
h_lst_key_columns_temp := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', x_num_key_columns);
h_lst_data_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_columns, x_num_data_columns);
h_lst_data_columns_temp := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('DATA', x_num_data_columns);
h_lst_data_formulas := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_formulas, x_num_data_columns);
h_lst_select := NULL;
SELECT short_name, source
INTO h_level_short_name, h_level_source
FROM bsc_sys_dim_levels_b
WHERE level_view_name = h_level_table_name;
BSC_UPDATE_DIM.Get_Dbi_Dim_Data(h_level_short_name, h_dbi_dim_data);
h_lst_select := h_lst_select||h_level_table_name||'.CODE, ';
h_lst_select := h_lst_select||x_in_tbl||'.YEAR, '||x_in_tbl||'.TYPE, '||x_in_tbl||'.'||x_in_per_fld;
h_lst_select := h_lst_select||'BSC_SYS_PERIODS.YEAR, '||x_in_tbl||'.TYPE, 0';
h_lst_select := h_lst_select||'BSC_SYS_PERIODS.YEAR, '||x_in_tbl||'.TYPE, BSC_SYS_PERIODS.PERIOD_ID';
h_periodicity_type_base_table := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_base_percode);
h_periodicity_type_input_table := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_in_percode);
h_periodicity_type_base_table := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_base_percode);
h_lst_select := h_lst_select||x_in_tbl||'.YEAR, '||x_in_tbl||'.TYPE, 0';
h_lst_select := h_lst_select||x_in_tbl||'.YEAR, '||x_in_tbl||'.TYPE, BSC_DB_WEEK_MAPS.WEEK52';
h_edw_flag := BSC_UPDATE_UTIL.Get_Periodicity_EDW_Flag(x_base_percode);
h_lst_select := h_lst_select||x_in_tbl||'.YEAR, '||
x_in_tbl||'.TYPE, BSC_DB_CALENDAR.'||h_base_calendar_col_name;
h_input_calendar_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_in_percode);
h_lst_select := h_lst_select||x_in_tbl||'.YEAR, '||
x_in_tbl||'.TYPE, BSC_DB_CALENDAR.'||h_base_calendar_col_name;
h_lst_select := h_lst_select||x_in_tbl||'.YEAR, '||
x_in_tbl||'.TYPE, BSC_EDW_TIME_MAP.BSC_TARGET';
h_sql := 'INSERT /*+ append';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||' parallel (bsc_tmp_base)';
' SELECT ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'/*+ '||l_parallel_hint||' */ ';
h_sql:=h_sql||h_lst_select||', ';
h_sql := h_sql||' GROUP BY '||h_lst_select;
sum(m1) to avg(m1). in this case, the higher periodicities in the base table are first deleted,
and then data from the base table is moved to the tmp table. so the step of pulling data from
the imput table is skipped.
*/
--delete data from the base table for higher periodicities
--Bug#3875046: We are not going to remove higher periodcities from the base table
--l_stmt:='delete '||x_base_tbl||' where periodicity_id <> :1';
l_bind_vars_values.delete;
h_sql := 'INSERT /*+ append';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||' parallel (bsc_tmp_base)';
h_sql := h_sql||h_lst_data_columns_temp||') SELECT ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql := h_sql||' /*+ parallel('||x_base_tbl||')*/ ';
h_row_count := BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
input table. We jsut need to insert all the rows from the base table to the BSC_TMP_BASE table.
AW_INTEGRATION: If the base table is for AW then there are not higher periodicities.
It is the same structure as summary tables architecture
*/
h_sql := 'INSERT /*+ append';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||' parallel (bsc_tmp_base)';
h_sql := h_sql||h_lst_data_columns_temp||') SELECT ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql := h_sql||' /*+ parallel('||x_base_tbl||')*/ ';
h_row_count := BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
l_bind_vars_values.delete;
h_row_count := BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,l_num_bind_vars);
h_row_count := BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
h_lst_join := BSC_UPDATE_UTIL.Make_Lst_Cond_Join(x_base_tbl,
x_key_columns,
'BSC_TMP_BASE',
h_key_columns_temp,
x_num_key_columns,
'AND');
l_stmt:='insert /*+ append';
if BSC_UPDATE_UTIL.is_parallel then
l_stmt:=l_stmt||' parallel (bsc_tmp_base_bu)';
' SELECT ';
if BSC_UPDATE_UTIL.is_parallel then
l_stmt:=l_stmt||'/*+ parallel (bsc_tmp_base) parallel ('||x_base_tbl||') */ ';
BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_UPDATE');
l_bind_vars_values.delete;
h_sql := 'INSERT /*+ append ';
IF BSC_UPDATE_UTIL.is_parallel THEN
h_sql := h_sql||'parallel (bsc_tmp_base_update) ';
' INTO bsc_tmp_base_update (row_id, '||h_lst_data_columns_temp||')'||
' SELECT '||x_base_tbl||'.rowid, '||h_lst_data_columns_temp_p||
' FROM '||x_base_tbl||', bsc_tmp_base'||
' WHERE '||h_lst_join;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
h_sql := 'UPDATE /*+ordered use_nl(B)*/ '||x_base_tbl||' B'||
' SET ('||h_lst_data_columns||')=('||
' SELECT '||h_lst_data_columns_temp||
' FROM bsc_tmp_base_update P'||
' WHERE P.row_id = B.rowid)'||
' WHERE B.rowid IN (SELECT row_id FROM bsc_tmp_base_update)';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
h_lst_select := NULL;
h_lst_select := BSC_UPDATE_UTIL.Make_Lst_Table_Column('BSC_TMP_BASE',
h_key_columns_temp,
x_num_key_columns);
h_lst_select := h_lst_select||', ';
h_lst_join := BSC_UPDATE_UTIL.Make_Lst_Cond_Left_Join('BSC_TMP_BASE',
h_key_columns_temp,
x_base_tbl,
x_key_columns,
x_num_key_columns,
'AND');
h_lst_cond_null := BSC_UPDATE_UTIL.Make_Lst_Cond_Null(x_base_tbl,
x_key_columns,
x_num_key_columns,
'OR');
h_lst_select := h_lst_select||'BSC_TMP_BASE.PERIODICITY_ID,';
h_lst_select := h_lst_select||'BSC_TMP_BASE.YEAR, BSC_TMP_BASE.TYPE, BSC_TMP_BASE.PERIOD, '||
h_lst_data_columns_temp_p;
before we insert into the base table, we need to see if we can use append hint.
the logic is as follows.
for I->B
if the base table has data, then this is inc and do not use append if there is
snapshot log on the base table.
for Projections
if the base table has a snapshot log on it and the snashot log has at-least
one row of data in it, then this insert is incremental and we cannot use
append hint. append hint will not write into the snapshot log
*/
declare
ll_use_append boolean;
h_sql := 'INSERT /*+append';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||' parallel ('||x_base_tbl||')';
h_sql := 'INSERT /*+';
' SELECT ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'/*+ parallel ('||x_base_tbl||') parallel (bsc_tmp_base) */ ';
h_sql:=h_sql||h_lst_select||
' FROM '||x_base_tbl||', BSC_TMP_BASE'||
' WHERE '||h_lst_join||' AND ('||h_lst_cond_null||')';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_UPDATE');
h_lst_select := NULL;
h_lst_select := BSC_UPDATE_UTIL.Make_Lst_Table_Column(x_base_tbl, x_key_columns, x_num_key_columns);
h_lst_select := h_lst_select||', ';
h_lst_join := BSC_UPDATE_UTIL.Make_Lst_Cond_Left_Join(x_base_tbl,
x_key_columns,
l_table,
h_key_columns_temp,
x_num_key_columns,
'AND');
h_lst_select := h_lst_select||x_base_tbl||'.PERIODICITY_ID, '||
x_base_tbl||'.YEAR, '||x_base_tbl||'.TYPE, '||x_base_tbl||'.PERIOD';
h_lst_select := h_lst_select||', 0';
h_lst_select := h_lst_select||', '||x_base_tbl||'.'||x_data_columns(i);
l_stmt := 'INSERT /*+append';
IF BSC_UPDATE_UTIL.is_parallel THEN
l_stmt := l_stmt||' parallel ('||l_table||')';
h_lst_data_columns_temp||') SELECT ';
IF BSC_UPDATE_UTIL.is_parallel THEN
l_stmt := l_stmt||'/*+ parallel ('||x_base_tbl||') parallel ('||l_table||') */';
l_stmt := l_stmt||h_lst_select||
' FROM '||x_base_tbl||', '||l_table||
' WHERE '||h_lst_join||' AND '||
x_base_tbl||'.PERIODICITY_ID = :1 AND '||x_base_tbl||'.YEAR = :2 AND '||
x_base_tbl||'.PERIOD >= :3 AND '||x_base_tbl||'.PERIOD <= :4 AND ('||h_lst_cond_null||')';
l_bind_vars_values.delete;
h_row_count := BSC_UPDATE_UTIL.Execute_Immediate(l_stmt,l_bind_vars_values,4);
after the base periodicity data is inserted into the base table, we are going to
rollup the data to higher periodicities and then perform an update / insert
*/
if l_number_periodicity>0 then
FOR h_i IN 1 .. x_num_data_columns LOOP
--h_data_columns_temp(h_i) := 'DATA'||h_i;
l_calendar_sql:='select distinct calendar_id,year,';
if BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(l_periodicity(i))<>1 then
l_stmt := 'select max('||l_column_name(i)||') from bsc_db_calendar'||
' where '||h_base_calendar_col_name||' = :1 and year = :2 and calendar_id = :3';
l_stmt := 'select max('||l_column_name(i)||') from bsc_db_calendar'||
' where '||h_base_calendar_col_name||' = :1 and year = :2 and calendar_id = :3';
l_stmt:='insert /*+ append';
if BSC_UPDATE_UTIL.is_parallel then
l_stmt:=l_stmt||' parallel ('||l_table||')';
' SELECT ';
if BSC_UPDATE_UTIL.is_parallel then
l_stmt:=l_stmt||'/*+ parallel ('||l_table||') parallel (bsc_db_calendar)*/ ';
if BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(l_periodicity(i))=1 then
l_stmt:=l_stmt||'0,';
if BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(l_periodicity(i))<>1 then
l_stmt:=l_stmt||'case when '||l_table||'.YEAR = '||x_current_fy||' and'||
' bsc_db_calendar.'||l_column_name(i)||' >= '||l_prev_current_period||' and'||
' bsc_db_calendar.'||l_column_name(i)||' <= '||l_current_period||
' then null else -999999999999 end case,';
l_bind_vars_values.delete;
h_row_count := BSC_UPDATE_UTIL.Execute_Immediate(l_stmt,l_bind_vars_values,3);
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_UPDATE');
l_stmt:='DELETE FROM bsc_tmp_per_change_bal';
BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);
l_yearly_flag:=BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(l_periodicity(i));
l_stmt:='INSERT INTO bsc_tmp_per_change_bal (year, src_per, trg_per)'||
' SELECT year, MAX('||h_base_calendar_col_name||') AS src_per, '||
l_column_name(i)||' AS trg_per'||
' FROM bsc_db_calendar where calendar_id=:1';
l_bind_vars_values.delete;
BSC_UPDATE_UTIL.Execute_Immediate(l_stmt, l_bind_vars_values,1);
l_bind_vars_values.delete;
l_stmt:='UPDATE bsc_tmp_per_change_bal'||
' SET src_per = :1'||
' WHERE year = :2'||
' AND trg_per = ('||
' SELECT '||l_column_name(i)||
' FROM bsc_db_calendar '||
' WHERE '||h_base_calendar_col_name||' = :3'||
' AND year = :4';
BSC_UPDATE_UTIL.Execute_Immediate(l_stmt, l_bind_vars_values,5);
l_stmt:='INSERT INTO bsc_tmp_per_change_bal (year, src_per, trg_per)'||
' SELECT year, MAX('||h_base_calendar_col_name||') AS src_per, 0 AS trg_per'||
' FROM bsc_db_calendar ';
l_bind_vars_values.delete;
BSC_UPDATE_UTIL.Execute_Immediate(l_stmt,l_bind_vars_values,1);
l_bind_vars_values.delete;
l_stmt:='UPDATE bsc_tmp_per_change_bal'||
' SET src_per = :1'||
' WHERE year = :2';
BSC_UPDATE_UTIL.Execute_Immediate(l_stmt,l_bind_vars_values,2);
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_BAL');
l_stmt:='insert /*+ append';
if BSC_UPDATE_UTIL.is_parallel then
l_stmt:=l_stmt||' parallel (BSC_TMP_BASE_BAL)';
h_lst_data_columns_temp||') SELECT ';
if BSC_UPDATE_UTIL.is_parallel then
l_stmt:=l_stmt||'/*+ parallel (bsc_tmp_base) parallel (bsc_tmp_per_change_bal) */';
l_bind_vars_values.delete;
BSC_UPDATE_UTIL.Execute_Immediate(l_stmt,l_bind_vars_values,2);
l_stmt:='insert into bsc_tmp_base_update (row_id,';
' select /*+ use_hash(bsc_tmp_base) use_hash(bsc_tmp_base_bal)*/'||
' bsc_tmp_base.rowid,';
l_bind_vars_values.delete;
BSC_UPDATE_UTIL.Execute_Immediate(l_stmt,l_bind_vars_values,1);
l_stmt:='update /*+ordered use_nl(bsc_tmp_base)*/ bsc_tmp_base set(';
l_stmt:=substr(l_stmt,1,length(l_stmt)-1)||')=(select ';
' from bsc_tmp_base_update'||
' where bsc_tmp_base_update.row_id=bsc_tmp_base.rowid)'||
' where rowid in (select row_id from bsc_tmp_base_update)';
BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);
l_stmt := 'DELETE FROM bsc_tmp_base_update';
BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);
l_stmt := 'INSERT INTO bsc_tmp_base_update (row_id,';
' SELECT '||x_base_tbl||'.rowid,';
BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);
l_stmt := 'UPDATE /*+ORDERED USE_NL('||x_base_tbl||')*/ '||x_base_tbl||
' SET(';
l_stmt:=substr(l_stmt,1,length(l_stmt)-1)||')=(SELECT ';
l_stmt:=l_stmt||'bsc_tmp_base_update.'||h_data_columns_temp(j)||',';
' FROM bsc_tmp_base_update'||
' WHERE bsc_tmp_base_update.row_id='||x_base_tbl||'.rowid)'||
' WHERE rowid IN (SELECT row_id FROM bsc_tmp_base_update)';
BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);
l_stmt := 'DELETE FROM bsc_tmp_base_update';
BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);
l_stmt := 'INSERT INTO bsc_tmp_base_update (row_id,';
' SELECT '||x_base_tbl||'.rowid,';
BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);
l_stmt := 'UPDATE /*+ORDERED USE_NL('||x_base_tbl||')*/ '||x_base_tbl||
' SET(';
l_stmt:=substr(l_stmt,1,length(l_stmt)-1)||')=(SELECT ';
' bsc_tmp_base_update.'||h_data_columns_temp(j)||',';
l_stmt:=l_stmt||'decode(bsc_tmp_base_update.'||h_data_columns_temp(j)||',-999999999999,'||
x_base_tbl||'.'||x_data_columns(j)||',bsc_tmp_base_update.'||h_data_columns_temp(j)||'),';
' bsc_tmp_base_update.'||h_data_columns_temp(j)||',';
' FROM bsc_tmp_base_update'||
' WHERE bsc_tmp_base_update.row_id='||x_base_tbl||'.rowid)'||
' WHERE rowid IN (SELECT row_id FROM bsc_tmp_base_update)';
BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);
select nvl(num_rows,0)
into h_num_rows_base
from all_tables
where table_name = x_base_tbl and owner= BSC_APPS.BSC_APPS_SCHEMA;
l_stmt := 'insert into bsc_tmp_base_rowid(row_id_tmp, row_id_base)'||
' select '||l_hint||' bsc_tmp_base.rowid, '||x_base_tbl||'.rowid'||
' from bsc_tmp_base, '||x_base_tbl||
' where ';
BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);
l_stmt := 'insert into '||x_base_tbl||' ('||
h_lst_key_columns||'PERIODICITY_ID,YEAR,TYPE,PERIOD,'||h_lst_data_columns||')'||
' select /*+ use_hash(bsc_tmp_base_rowid) use_hash(bsc_tmp_base)*/ '||
h_lst_key_columns_temp||'PERIODICITY_ID,YEAR,TYPE,PERIOD,';
BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);
commit;--we can have a commit only after both update and insert, otherwise there is data corruption
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_BU');
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_UPDATE');
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_BAL');
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PER_CHANGE_BAL');
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_ROWID');
X_Message => BSC_UPDATE_UTIL.Get_Message('BSC_BTABLE_UPDATE_FAILED'),
X_Source => 'BSC_UPDATE_BASE.Update_Base_Table');
X_Source => 'BSC_UPDATE_BASE.Update_Base_Table');
X_Source => 'BSC_UPDATE_BASE.Update_Base_Table');
END Update_Base_Table;