[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_periodicity);
h_edw_flag := BSC_UPDATE_UTIL.Get_Periodicity_EDW_Flag(x_periodicity);
h_origin_calendar_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_origin_periodicity);
h_calendar_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity);
h_sql := 'SELECT DISTINCT '||h_calendar_col_name||
' FROM bsc_db_calendar'||
' WHERE calendar_id = :1 AND year = :2'||
' AND '||h_origin_calendar_col_name||' = :3';
h_sql := 'SELECT bsc_target'||
' FROM bsc_edw_time_map'||
' WHERE year = :1 AND bsc_source = :2';
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_SUMTBLE_PERIOD_CALC_FAILED'),
x_source => 'BSC_UPDATE_SUM.Calculate_Period_Summary_Table');
x_source => 'BSC_UPDATE_SUM.Calculate_Period_Summary_Table');
h_origin_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
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;
h_key_columns_ori BSC_UPDATE_UTIL.t_array_of_varchar2;
h_key_dim_tables_ori BSC_UPDATE_UTIL.t_array_of_varchar2;
h_source_columns_ori BSC_UPDATE_UTIL.t_array_of_varchar2;
h_source_dim_tables_ori BSC_UPDATE_UTIL.t_array_of_varchar2;
h_origin_periodicity := BSC_UPDATE_UTIL.Get_Table_Periodicity(h_origin_tables(1));
h_periodicity := BSC_UPDATE_UTIL.Get_Table_Periodicity(x_sum_table);
h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(h_periodicity);
h_calendar_edw_flag := BSC_UPDATE_UTIL.Get_Calendar_EDW_Flag(h_calendar_id);
h_current_fy := BSC_UPDATE_UTIL.Get_Calendar_Fiscal_Year(h_calendar_id);
IF NOT BSC_UPDATE_UTIL.Get_Table_Range_Of_Years(x_sum_table, h_num_of_years, h_previous_years) THEN
RAISE e_unexpected_error;
h_generation_type := BSC_UPDATE_UTIL.Get_Table_Generation_Type(x_sum_table);
IF NOT BSC_UPDATE_UTIL.Get_Information_Data_Columns(x_sum_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_sum_table,
h_key_columns,
h_key_dim_tables,
h_source_columns,
h_source_dim_tables,
h_num_key_columns) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_UTIL.Get_Information_Key_Columns(h_origin_tables(1),
h_key_columns_ori,
h_key_dim_tables_ori,
h_source_columns_ori,
h_source_dim_tables_ori,
h_num_key_columns_ori) THEN
RAISE e_unexpected_error;
h_b := BSC_UPDATE_CALC.Table_Has_Proj_Calc(x_sum_table);
IF NOT BSC_UPDATE_CALC.Create_Proj_Temps(h_periodicity,
h_current_fy,
h_num_of_years,
h_previous_years,
TRUE) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_CALC.Apply_Filters(x_sum_table) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_CALC.Merge_Data_From_Tables(x_sum_table,
h_key_columns,
h_num_key_columns) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_CALC.Calculate_Projection(x_sum_table,
h_periodicity,
h_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,
FALSE,
FALSE,
NULL) THEN
RAISE e_unexpected_error;
h_b := BSC_UPDATE_CALC.Table_Has_Profit_Calc(x_sum_table);
IF NOT BSC_UPDATE_CALC.Calculate_Profit(x_sum_table,
h_key_columns,
h_key_dim_tables,
h_num_key_columns,
h_data_columns,
h_num_data_columns,
FALSE,
NULL) THEN
RAISE e_unexpected_error;
h_target_flag := BSC_UPDATE_UTIL.Get_Table_Target_Flag(x_sum_table);
IF NOT BSC_UPDATE_CALC.Calculate_Zero_Code(x_sum_table,
h_zero_code_calc_method,
h_key_columns,
h_num_key_columns,
NULL) THEN
RAISE e_unexpected_error;
UPDATE
bsc_db_tables
SET
current_period = h_period
WHERE
table_name = x_sum_table;
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_SUMTABLE_CALC_FAILED'),
x_source => 'BSC_UPDATE_SUM.Calculate_Sum_Table');
x_source => 'BSC_UPDATE_SUM.Calculate_Sum_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
e_unexpected_error EXCEPTION;
h_origin_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
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;
h_key_columns_ori BSC_UPDATE_UTIL.t_array_of_varchar2;
h_key_dim_tables_ori BSC_UPDATE_UTIL.t_array_of_varchar2;
h_source_columns_ori BSC_UPDATE_UTIL.t_array_of_varchar2;
h_source_dim_tables_ori BSC_UPDATE_UTIL.t_array_of_varchar2;
SELECT DISTINCT projection_data
FROM bsc_kpi_data_tables
WHERE table_name = p_sum_table;
h_origin_pts BSC_UPDATE_UTIL.t_array_of_varchar2;
SELECT DISTINCT kt.table_name, t.project_flag
FROM bsc_kpi_data_tables kt, bsc_db_tables t
WHERE kt.projection_data = p_pt_name AND
kt.table_name <> p_table_name AND
kt.table_name = t.table_name;
SELECT DISTINCT p.periodicity_id, p.yearly_flag, t.current_period
FROM bsc_kpi_data_tables kt, bsc_sys_periodicities p, bsc_db_tables t
WHERE kt.projection_data = p_pt_name AND
kt.periodicity_id = p.periodicity_id AND
kt.table_name = t.table_name;
h_origin_periodicity := BSC_UPDATE_UTIL.Get_Table_Periodicity(h_origin_tables(1));
h_periodicity := BSC_UPDATE_UTIL.Get_Table_Periodicity(x_sum_table);
h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(h_periodicity);
h_calendar_edw_flag := BSC_UPDATE_UTIL.Get_Calendar_EDW_Flag(h_calendar_id);
h_current_fy := BSC_UPDATE_UTIL.Get_Calendar_Fiscal_Year(h_calendar_id);
IF NOT BSC_UPDATE_UTIL.Get_Table_Range_Of_Years(x_sum_table, h_num_of_years, h_previous_years) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_mv_name,
g_refreshed_mvs,
g_num_refreshed_mvs) THEN
IF NOT BSC_BIA_WRAPPER.Refresh_Summary_MV(h_mv_name, h_error_refresh) THEN
RAISE e_error_refresh;
UPDATE
bsc_db_tables
SET
current_period = h_period
WHERE
table_name = x_sum_table;
SELECT project_flag INTO h_project_flag
FROM bsc_db_tables
WHERE table_name = x_sum_table;
IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_other_table_name,
x_system_tables,
x_num_system_tables) THEN
-- The other table was or is going to be calculated in this process
-- Now check that it was already calculated
IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_other_table_name,
x_calculated_sys_tables,
x_num_calculated_sys_tables) THEN
-- The other table has not been calculated, so we cannot process the PT
-- table right now
h_process_pt := FALSE;
IF NOT BSC_UPDATE_UTIL.Get_Information_Data_Columns(x_sum_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_sum_table,
h_key_columns,
h_key_dim_tables,
h_source_columns,
h_source_dim_tables,
h_num_key_columns) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_UTIL.Get_Information_Key_Columns(h_origin_tables(1),
h_key_columns_ori,
h_key_dim_tables_ori,
h_source_columns_ori,
h_source_dim_tables_ori,
h_num_key_columns_ori) THEN
RAISE e_unexpected_error;
BSC_UPDATE_UTIL.Truncate_Table(h_pt_name);
IF NOT BSC_UPDATE_UTIL.Load_Periodicity_Rels THEN
RAISE e_unexpected_error;
IF BSC_UPDATE_UTIL.Exist_Periodicity_Rel(h_arr_pt_periodicities(h_i).periodicity_id,
h_arr_pt_periodicities(h_j).periodicity_id) THEN
h_arr_pt_periodicities(h_i).source_periodicity := h_arr_pt_periodicities(h_j).periodicity_id;
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJ_CALC');
IF NOT BSC_UPDATE_CALC.Create_Proj_Temps(h_arr_pt_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(h_pt_name,
h_arr_pt_periodicities(h_i).periodicity_id,
h_arr_pt_periodicities(h_i).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,
FALSE,
FALSE,
NULL) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_CALC.Create_Proj_Temps(h_arr_pt_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.Rollup_Projection(
h_arr_pt_periodicities(h_i).periodicity_id,
h_arr_pt_periodicities(h_i).current_period,
h_arr_pt_periodicities(h_i).source_periodicity,
h_arr_pt_periodicities(h_i).source_current_period,
h_key_columns,
h_num_key_columns,
h_data_columns,
h_data_formulas,
h_data_measure_types,
h_num_data_columns,
h_current_fy,
FALSE) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_CALC.Merge_Projection(h_pt_name,
h_key_columns,
h_num_key_columns,
h_data_columns,
h_num_data_columns,
FALSE,
FALSE) THEN
RAISE e_unexpected_error;
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJ_CALC');
h_zero_code_calc_method := BSC_UPDATE_CALC.Get_Zero_Code_Calc_Method(x_sum_table);
IF NOT BSC_UPDATE_CALC.Calculate_Zero_Code(x_sum_table,
h_zero_code_calc_method,
h_key_columns,
h_num_key_columns,
NULL) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_CALC.Calculate_Zero_Code(x_sum_table,
h_zero_code_calc_method,
h_key_columns,
h_num_key_columns,
h_origin_tables(1)) THEN
RAISE e_unexpected_error;
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_SUMTABLE_CALC_FAILED'),
x_source => 'BSC_UPDATE_SUM.Calculate_Sum_Table_MV');
x_source => 'BSC_UPDATE_SUM.Calculate_Sum_Table_MV');
BSC_MESSAGE.Add(x_message => 'BSC_UPDATE_SUM.Refresh_Zero_MVs '||h_mv_name||' '||h_error_refresh,
x_source => 'BSC_UPDATE_SUM.Calculate_Sum_Table_MV');
x_source => 'BSC_UPDATE_SUM.Calculate_Sum_Table_MV');
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
PRAGMA AUTONOMOUS_TRANSACTION;
h_origin_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
h_origin_periodicity := BSC_UPDATE_UTIL.Get_Table_Periodicity(h_origin_tables(1));
h_periodicity := BSC_UPDATE_UTIL.Get_Table_Periodicity(x_sum_table);
h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(h_periodicity);
h_current_fy := BSC_UPDATE_UTIL.Get_Calendar_Fiscal_Year(h_calendar_id);
UPDATE
bsc_db_tables
SET
current_period = h_period
WHERE
table_name = x_sum_table;
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_SUMTABLE_CALC_FAILED'),
x_source => 'BSC_UPDATE_SUM.Calculate_Sum_Table_AW');
x_source => 'BSC_UPDATE_SUM.Calculate_Sum_Table_AW');
x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_source_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_source_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_key_columns IN NUMBER,
x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,
x_num_data_columns IN NUMBER,
x_origin_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_origin_tables IN NUMBER,
x_key_columns_ori IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_key_columns_ori IN NUMBER,
x_periodicity IN NUMBER,
x_origin_periodicity IN NUMBER,
x_period IN NUMBER,
x_origin_period IN NUMBER,
x_current_fy IN NUMBER) RETURN BOOLEAN IS
e_unexpected_error EXCEPTION;
h_lst_select_disag VARCHAR2(32700);
h_lst_select_per VARCHAR2(32700);
l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
h_key_columns_ori_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
h_key_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
h_origin_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
h_lst_select_disag := NULL;
h_lst_select_per := NULL;
h_periodicity_edw_flag := BSC_UPDATE_UTIL.Get_Periodicity_EDW_Flag(x_periodicity);
h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_periodicity);
h_period_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity);
h_origin_period_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_origin_periodicity);
l_bind_vars_values.delete;
l_bind_vars_values.delete;
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PER_CHANGE');
h_sql := 'INSERT /*+ append ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'parallel (bsc_tmp_per_change) ';
' SELECT ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'/*+ parallel ('||h_period_map_table||')*/ ';
h_sql := 'INSERT /*+ append ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'parallel (bsc_tmp_per_change) ';
' SELECT ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'/*+ parallel ('||h_period_map_table||')*/ ';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PER_CHANGE_BAL');
h_sql := 'INSERT /*+ append ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'parallel (bsc_tmp_per_change_bal) ';
' SELECT ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'/*+ parallel ('||h_period_map_table||')*/ ';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
l_bind_vars_values.delete;
h_sql := 'UPDATE bsc_tmp_per_change_bal'||
' SET src_per = :1'||
' WHERE year = :2'||
' AND trg_per = ('||
' SELECT '||h_period_col_name||
' FROM '||h_period_map_table||
' WHERE '||h_origin_period_col_name||' = :3'||
' AND year = :4';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
h_sql := 'INSERT /*+ append ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'parallel (bsc_tmp_per_change_bal) ';
' SELECT ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'/*+ parallel ('||h_period_map_table||')*/ ';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
l_bind_vars_values.delete;
h_sql := 'UPDATE bsc_tmp_per_change_bal'||
' SET src_per = :1'||
' WHERE year = :2';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_DATA');
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BAL_DATA');
IF BSC_UPDATE_UTIL.Is_Base_Table(x_origin_tables(h_i)) THEN
h_proj_table_name := BSC_UPDATE_BASE_V2.Get_Base_Proj_Tbl_Name(x_origin_tables(h_i));
h_origin_tables(h_i) := '(SELECT * FROM '||x_origin_tables(h_i)||
' UNION ALL'||
' SELECT * FROM '||h_proj_table_name||
') '||x_origin_tables(h_i);
h_lst_key_columns_ori := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns_ori,
x_num_key_columns_ori);
h_lst_key_columns_ori_temp := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(h_key_columns_ori_temp,
x_num_key_columns_ori);
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_UNION');
h_sql := 'INSERT /*+ append ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'parallel (BSC_TMP_UNION) ';
' SELECT ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'/*+ parallel ('||x_origin_tables(1)||')*/ ';
' SELECT '||h_lst_key_columns_ori||'YEAR, TYPE, PERIOD'||
' FROM '||h_origin_tables(h_i);
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(x_key_columns_ori(h_i),
x_source_columns,
x_num_key_columns) THEN
IF h_lst_where IS NULL THEN
h_lst_where := h_key_columns_ori_temp(h_i)||' = ''0''';
h_sql := 'DELETE FROM BSC_TMP_UNION'||
' WHERE '||h_lst_where;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(x_key_columns_ori(h_i),
x_source_columns,
x_num_key_columns) THEN
IF h_cond_zero_codes_src IS NULL THEN
h_cond_zero_codes_src := x_key_columns_ori(h_i)||' <> ''0''';
BSC_UPDATE_UTIL.Make_Lst_Cond_Left_Join('BSC_TMP_UNION',
h_key_columns_ori_temp,
x_origin_tables(h_i),
x_key_columns_ori,
x_num_key_columns_ori,
'AND')||
' AND ';
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_From_Array_Varchar2(h_key_columns_temp, 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_formulas := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_formulas, x_num_data_columns);
BSC_UPDATE_UTIL.Truncate_Table(x_sum_table);
IF NOT BSC_UPDATE_UTIL.Drop_Index(x_sum_table||'_U1') THEN
RAISE e_unexpected_error;
h_lst_select_disag := h_lst_select_disag||', ';
h_lst_select_disag := h_lst_select_disag||x_source_dim_tables(h_i)||'.CODE';
h_lst_select_disag := h_lst_select_disag||x_source_dim_tables(h_i)||'.'||x_key_columns(h_i);
IF h_lst_select_disag IS NOT NULL THEN
h_lst_select_disag := h_lst_select_disag||', ';
h_lst_select_per := h_union_table||'.YEAR, '||h_union_table||'.TYPE, '||h_union_table||'.PERIOD';
h_sql := 'INSERT /*+ append ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'parallel ('||x_sum_table||') ';
h_sql := h_sql||' SELECT ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'/*+'||l_parallel_hint||'*/ ';
h_sql := h_sql||h_lst_select_disag||h_lst_select_per;
' GROUP BY '||h_lst_select_disag||h_lst_select_per;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
IF NOT BSC_UPDATE_UTIL.Create_Unique_Index(x_sum_table,
x_sum_table||'_U1',
h_lst_key_columns||
'YEAR, TYPE, PERIOD, PERIODICITY_ID, PERIOD_TYPE_ID',
BSC_APPS.summary_index_tbs_type) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_UTIL.Create_Unique_Index(x_sum_table,
x_sum_table||'_U1',
h_lst_key_columns||'YEAR, TYPE, PERIOD',
BSC_APPS.summary_index_tbs_type) THEN
RAISE e_unexpected_error;
h_lst_select_per := h_union_table||'.YEAR, '||h_union_table||'.TYPE, BSC_TMP_PER_CHANGE.TRG_PER';
l_bind_vars_values.delete;
h_sql := 'INSERT /*+ append ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'parallel ('||h_target_table_tot||') ';
h_sql:=h_sql||' SELECT ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'/*+'||l_parallel_hint1||'*/ ';
h_sql:=h_sql||h_lst_select_disag||h_lst_select_per||', '||h_lst_tot_data_formulas||
' FROM '||h_lst_from_tot||
' WHERE '||h_lst_on_tot||' AND '||h_lst_where||
' GROUP BY '||h_lst_select_disag||h_lst_select_per;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
l_bind_vars_values.delete;
l_bind_vars_values.delete;
h_sql := 'INSERT /*+ append ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'parallel ('||h_target_table_tot||') ';
h_sql:=h_sql||' SELECT ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'/*+'||l_parallel_hint1||'*/ ';
h_sql:=h_sql||h_lst_select_disag||h_lst_select_per||', '||h_lst_tot_data_formulas||
' FROM '||h_lst_from_tot||
' WHERE '||h_lst_on_tot||' AND '||h_lst_where||
' GROUP BY '||h_lst_select_disag||h_lst_select_per;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
IF NOT BSC_UPDATE_UTIL.Create_Unique_Index(h_target_table_tot,
h_target_table_tot||'_U1',
h_lst_key_columns||'YEAR, TYPE, PERIOD',
BSC_APPS.summary_index_tbs_type) THEN
RAISE e_unexpected_error;
h_lst_select_per := h_union_table||'.YEAR, '||h_union_table||'.TYPE, BSC_TMP_PER_CHANGE_BAL.TRG_PER';
h_sql := 'INSERT /*+ append ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'parallel ('||h_target_table_bal||') ';
h_sql := h_sql||' SELECT ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'/*+'||l_parallel_hint2||'*/ ';
h_sql:=h_sql||h_lst_select_disag||h_lst_select_per||', '||h_lst_bal_data_formulas||
' FROM '||h_lst_from_bal||
' WHERE '||h_lst_on_bal||
' GROUP BY '||h_lst_select_disag||h_lst_select_per;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
IF NOT BSC_UPDATE_UTIL.Create_Unique_Index(h_target_table_bal,
h_target_table_bal||'_U1',
h_lst_key_columns||'YEAR, TYPE, PERIOD',
BSC_APPS.summary_index_tbs_type) THEN
RAISE e_unexpected_error;
h_lst_on := BSC_UPDATE_UTIL.Make_Lst_Cond_Left_Join('T', h_key_columns_temp, 'B', h_key_columns_temp,
x_num_key_columns, 'AND');
h_lst_select_disag := BSC_UPDATE_UTIL.Make_Lst_Table_Column('T', h_key_columns_temp, x_num_key_columns);
IF h_lst_select_disag IS NOT NULL THEN
h_lst_select_disag := h_lst_select_disag||', ';
h_sql := 'INSERT /*+ append ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'parallel ('||x_sum_table||') ';
' SELECT ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'/*+ parallel (T) parallel (B)*/ ';
h_sql:=h_sql||h_lst_select_disag||'T.YEAR, T.TYPE, T.PERIOD, '||
h_lst_tot_data_columns_temp_t||', '||h_lst_bal_data_columns_temp_b||
' FROM BSC_TMP_TOT_DATA T, BSC_TMP_BAL_DATA B'||
' WHERE '||h_lst_on;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
IF NOT BSC_UPDATE_UTIL.Create_Unique_Index(x_sum_table,
x_sum_table||'_U1',
h_lst_key_columns||'YEAR, TYPE, PERIOD',
BSC_APPS.summary_index_tbs_type) THEN
RAISE e_unexpected_error;
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PER_CHANGE');
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PER_CHANGE_BAL');
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_DATA');
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BAL_DATA');
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_UNION');
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_SUMTABLE_CALC_FAILED'),
x_source => 'BSC_UPDATE_SUM.Calculate_Sum_Table_Total');
x_source => 'BSC_UPDATE_SUM.Calculate_Sum_Table_Total');
x_origin_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_origin_tables IN NUMBER
) RETURN NUMBER IS
h_table_name VARCHAR2(30);
c_current_period_sql VARCHAR2(2000) := 'SELECT NVL(current_period, 0)'||
' FROM bsc_db_tables'||
' WHERE table_name = :1';
SELECT NVL(current_period, 0)
INTO h_current_period
FROM bsc_db_tables
WHERE table_name = h_table_name;
SELECT NVL(current_period, 0)
INTO h_current_period
FROM bsc_db_tables
WHERE table_name = h_table_name;
x_source => 'BSC_UPDATE_SUM.Get_Minimun_Origin_Period');
x_origin_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_origin_tables IN OUT NOCOPY 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 AND relation_type = :2';
SELECT source_table_name
FROM bsc_db_tables_rels
WHERE table_name = p_table_name AND relation_type = p_relation_type;
x_source => 'BSC_UPDATE_SUM.Get_Origin_Tables');
h_kpi_list.delete;
h_sql := 'SELECT DISTINCT mv_name'||
' FROM bsc_kpi_data_tables'||
' WHERE table_name = :1 AND data_source = :2'||
' AND mv_name <> :3';