[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
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_periodicity := BSC_UPDATE_UTIL.Get_Table_Periodicity(x_base_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);
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_get_info_data_columns;
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_get_info_key_columns;
h_list.delete;
BSC_UPDATE_UTIL.Truncate_Table(h_proj_tbl_name);
BSC_UPDATE_UTIL.Truncate_Table(h_rowid_tbl_name);
h_sql := 'insert /*+ append';
IF BSC_UPDATE_UTIL.is_parallel THEN
h_sql := h_sql||' parallel('||h_rowid_tbl_name||')';
' select';
IF BSC_UPDATE_UTIL.is_parallel THEN
h_sql := h_sql||' /*+ parallel('||x_input_table||') */';
h_bind_vars_values.delete;
h_num_rows := BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, 2);
h_process := 'BSC_UPDATE_BASE_V2.Update_Base_Table_Job('||
''''||x_base_table||''','||
''''||x_input_table||''','||
h_correction_flag_t||','||
h_aw_flag_t||','||
h_change_vector_value||','||
h_periodicity||','||
h_calendar_id||','||
h_current_fy||','||
h_per_base_table||','||
h_current_per_base_table||','||
''''||h_proj_tbl_name||''','||
''''||h_rowid_tbl_name||''','||
''''||h_partition_names(h_j)||''','||
h_batch_values(h_j)||','||
h_num_partitions||','||
h_num_loads||','||
''''||h_job_name||''''||
');';
Update_Base_Table(x_base_table,
x_input_table,
x_correction_flag,
x_aw_flag,
h_change_vector_value,
h_periodicity,
h_calendar_id,
h_current_fy,
h_per_base_table,
h_current_per_base_table,
h_key_columns,
h_key_dim_tables,
h_num_key_columns,
h_data_columns,
h_data_formulas,
h_data_proj_methods,
h_data_measure_types,
h_num_data_columns,
h_proj_tbl_name,
h_rowid_tbl_name,
null,
null,
h_num_partitions,
h_num_loads,
h_parallel_jobs,
h_return_status,
h_error_message);
bsc_aw_load.update_bt_change_vector(x_base_table, h_change_vector_value);
bsc_aw_load.update_bt_change_vector(h_proj_tbl_name, h_change_vector_value);
update bsc_db_tables
set current_period = h_per_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);
x_source => 'BSC_UPDATE_BASE_V2.Calculate_Base_Table');
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_BTABLE_CALCULATION_FAILED'),
x_source => 'BSC_UPDATE_BASE_V2.Calculate_Base_Table');
x_source => 'BSC_UPDATE_BASE_V2.Calculate_Base_Table');
x_source => 'BSC_UPDATE_BASE_V2.Calculate_Base_Table');
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_BTABLE_CALCULATION_FAILED'),
x_source => 'BSC_UPDATE_BASE_V2.Calculate_Base_Table');
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_BTABLE_CALCULATION_FAILED'),
x_source => 'BSC_UPDATE_BASE_V2.Calculate_Base_Table');
x_source => 'BSC_UPDATE_BASE_V2.Calculate_Base_Table');
x_source => 'BSC_UPDATE_BASE_V2.Calculate_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)
INTO h_current_period
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(PERIOD) '||
'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';
SELECT NVL(current_period, 0)
INTO h_current_period
FROM bsc_db_tables
WHERE table_name = x_base_table;
x_key_columns 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_proj_methods IN BSC_UPDATE_UTIL.t_array_of_number,
x_num_data_columns IN NUMBER,
x_partition_name IN VARCHAR2,
x_batch_value IN NUMBER,
x_parallel_jobs IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_error_message OUT NOCOPY VARCHAR2
) IS
--h_sql clob;
h_avg_cols BSC_UPDATE_UTIL.t_array_of_varchar2;
h_perf_cols BSC_UPDATE_UTIL.t_array_of_varchar2;
h_custom_cols BSC_UPDATE_UTIL.t_array_of_varchar2;
h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
h_per_column := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity);
IF NOT BSC_UPDATE_UTIL.Get_Table_Range_Of_Years(x_base_table, h_num_of_years, h_previous_years) THEN
h_num_of_years := 2;
bsc_dbgen_utils.add_string(h_sql, 'insert /*+ append');
IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
bsc_dbgen_utils.add_string(h_sql, ' parallel('||x_proj_table||')');
bsc_dbgen_utils.add_string(h_sql, ' select');
IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(p) */');
bsc_dbgen_utils.add_string(h_sql, ' select');
IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(p) parallel(tp) parallel(tr) parallel(pp) */');
bsc_dbgen_utils.add_string(h_sql, ' from (select');
IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(a) parallel(bsc_tmp_all_periods) */');
bsc_dbgen_utils.add_string(h_sql, ' from (select');
IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_base_table||') */');
bsc_dbgen_utils.add_string(h_sql, ' (select');
IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(bsc_db_calendar) */');
bsc_dbgen_utils.add_string(h_sql, ' (select');
IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(bsc_db_calendar) */');
' (select ');
IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
bsc_dbgen_utils.add_string(h_sql, '/*+ parallel('||x_base_table||') */ ');
' (select ');
IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
bsc_dbgen_utils.add_string(h_sql, '/*+ parallel('||x_base_table||') */ ');
' (select ');
IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
bsc_dbgen_utils.add_string(h_sql, '/*+ parallel('||x_base_table||') */ ');
bsc_dbgen_utils.add_string(h_sql, ' select');
IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(p) parallel(b) */');
bsc_dbgen_utils.add_string(h_sql, ' from (select');
IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(a) parallel(bsc_tmp_all_periods) */');
bsc_dbgen_utils.add_string(h_sql, ' from (select');
IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_base_table||') */');
bsc_dbgen_utils.add_string(h_sql, ' (select');
IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(bsc_db_calendar) */');
bsc_dbgen_utils.add_string(h_sql, ' (select');
IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(bsc_db_calendar) */');
' (select ');
IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
bsc_dbgen_utils.add_string(h_sql, '/*+ parallel('||x_base_table||') */ ');
h_sql.delete;
h_end_per := BSC_UPDATE_UTIL.Get_Num_Periods_Periodicity(x_periodicity, x_current_fy);
bsc_dbgen_utils.add_string(h_sql, 'update '||x_proj_table||' p'||
' set ('||h_avg_cols(1));
' select ');
IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(u) */ ');
' select ');
IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_base_table||') */ ');
' select ');
IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_proj_table||') */ ');
h_bind_vars_values.delete;
bsc_dbgen_utils.add_string(h_sql, 'insert /*+ append');
IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
bsc_dbgen_utils.add_string(h_sql, ' parallel('||x_proj_table||')');
' select');
IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(u) */');
' select');
IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_base_table||') */');
' select ');
IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_proj_table||') */ ');
h_bind_vars_values.delete;
h_table_columns BSC_UPDATE_UTIL.t_array_temp_table_cols;
h_table_columns.delete;
IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
RAISE e_unexpected_error;
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR'),
x_source => 'BSC_UPDATE_BASE_V2.Create_Generic_Temp_Tables');
x_source => 'BSC_UPDATE_BASE_V2.Create_Generic_Temp_Tables');
x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_key_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_partition_name IN VARCHAR2,
x_batch_value IN NUMBER,
x_num_partitions IN NUMBER,
x_parallel_jobs IN VARCHAR2,
x_rowid_table IN VARCHAR2,
x_num_loads IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_error_message OUT NOCOPY VARCHAR2
) IS
--h_sql CLOB;
h_bal_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(x_calendar_id);
h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
h_periodicity_type := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_periodicity);
' SELECT ');
' FROM (select /*+ordered*/ ');
bsc_dbgen_utils.add_string(h_sql, ') select ');
' FROM (SELECT ');
' select '||l_newline);
', (select * from '||x_base_table||l_newline);
' select '||l_newline);
' select * from bsc_db_calendar_temp'||l_newline||
' order by periodicity_id,year,lower_period;'||l_newline||
' select ');
bsc_dbgen_utils.add_string(h_sql, ' l_cb_data'||h_i||'.delete;'||l_newline);
' l_cb_rowid.delete;'||l_newline||
' update '||x_base_table||l_newline||
' set '||h_bal_columns(1)||' = l_cb_data1(k)');
' v1.delete;'||l_newline||
' v1_join_rollup.delete;'||l_newline||
' v1_rollup.delete;'||l_newline);
bsc_dbgen_utils.add_string(h_sql, ' u_data'||h_i||'.delete;'||l_newline);
bsc_dbgen_utils.add_string(h_sql, ' u_rowid.delete;'||l_newline);
bsc_dbgen_utils.add_string(h_sql, ' i_'||x_key_columns(h_i)||'.delete;'||l_newline);
' i_year.delete;'||l_newline||
' i_type.delete;'||l_newline||
' i_period.delete;'||l_newline||
' i_periodicity_id.delete;'||l_newline);
bsc_dbgen_utils.add_string(h_sql, ' i_data'||h_i||'.delete;'||l_newline);
' i_batch.delete;'||l_newline||
' update '||x_base_table||l_newline;
' insert /*+append*/ into '||x_base_table||l_newline;
' v1_rollup.delete;'||l_newline||
' u_rowid.delete;'||l_newline;
l_sql := l_sql||' u_data'||h_i||'.delete;'||l_newline;
' update '||x_base_table||l_newline;
x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_key_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_num_data_columns IN NUMBER,
x_partition_name IN VARCHAR2,
x_batch_value IN NUMBER,
x_num_partitions IN NUMBER,
x_parallel_jobs IN VARCHAR2,
x_rowid_table IN VARCHAR2,
x_num_loads IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_error_message OUT NOCOPY VARCHAR2
) IS
--h_sql CLOB;
h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(x_calendar_id);
h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
h_periodicity_type := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_periodicity);
' select ');
' FROM (SELECT ');
' FROM (SELECT /*+ ordered */ ');
', (select * from '||x_base_table);
' v1.delete;');
bsc_dbgen_utils.add_string(h_sql, ' u_data'||h_i||'_table.delete;');
bsc_dbgen_utils.add_string(h_sql, ' u_rowid_table.delete;');
bsc_dbgen_utils.add_string(h_sql, ' i_'||x_key_columns(h_i)||'.delete;');
' i_YEAR.delete;'||
' i_TYPE.delete;'||
' i_PERIOD.delete;');
bsc_dbgen_utils.add_string(h_sql, ' i_data'||h_i||'.delete;');
' i_BATCH.delete;');
' i_PROJECTION.delete;');
' update '||x_base_table);
' insert /*+append*/ into '||x_base_table);
x_key_columns 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_num_data_columns IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_error_message OUT NOCOPY VARCHAR2
) IS
h_i NUMBER;
h_list.delete;
SELECT c.parameter1, p.yearly_flag, p.db_column_name
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;
BSC_UPDATE_UTIL.Truncate_Table('BSC_DB_CALENDAR_TEMP');
h_column_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity);
h_sql := 'insert into bsc_db_calendar_temp ('||
' lower_period, upper_period, year, last_period, periodicity_id)'||
' select cal.'||h_column_name||', 0, cal.year,'||
' case when cal.'||h_column_name||' = :1 and cal.year = :2 then :3'||
' when cal.year <> :4 and bal.'||h_column_name||' is not null then :5'||
' else :6 end case, :7'||
' from '||
' (select distinct '||h_column_name||', year'||
' from bsc_db_calendar'||
' where calendar_id = :8) cal,'||
' (select max('||h_column_name||') '||h_column_name||', year'||
' from bsc_db_calendar'||
' where calendar_id = :9'||
' group by year) bal'||
' where cal.'||h_column_name||' = bal.'||h_column_name||' (+) and'||
' cal.year = bal.year (+)';
h_sql := 'select max('||h_up_column_name||')'||
' from bsc_db_calendar'||
' where calendar_id = :1 and '||h_column_name||' = :2 and year = :3';
h_sql := 'insert into bsc_db_calendar_temp ('||
' lower_period, upper_period, year, last_period, periodicity_id)'||
' select cal.'||h_column_name||', cal.'||h_up_column_name||', cal.year,'||
' case when cal.'||h_column_name||' = :1 and cal.year = :2 then :3'||
' when not (cal.'||h_up_column_name||' = :4 and cal.year = :5) and'||
' bal.'||h_column_name||' is not null then :6'||
' else :7 end case, :8'||
' from'||
' (select distinct '||h_column_name||', '||h_up_column_name||', year'||
' from bsc_db_calendar'||
' where calendar_id = :9) cal,'||
' (select max('||h_column_name||') '||h_column_name||', '||h_up_column_name||', year'||
' from bsc_db_calendar'||
' where calendar_id = :10'||
' group by '||h_up_column_name||', year) bal'||
' where cal.'||h_column_name||' = bal.'||h_column_name||' (+) and'||
' cal.'||h_up_column_name||' = bal.'||h_up_column_name||' (+) and'||
' cal.year = bal.year (+)';
SELECT c.parameter1, p.yearly_flag, p.db_column_name
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;
BSC_UPDATE_UTIL.Truncate_Table('BSC_DB_CALENDAR_TEMP');
h_column_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity);
h_sql := 'select max('||h_up_column_name||')'||
' from bsc_db_calendar'||
' where calendar_id = :1 and '||h_column_name||' = :2 and year = :3';
h_sql := 'insert into bsc_db_calendar_temp ('||
' lower_period, upper_period, year, last_period, periodicity_id)'||
' select cal.'||h_column_name||', cal.'||h_up_column_name||', cal.year,'||
' case when bal.'||h_column_name||' is not null then :1'||
' else :2 end case, :3'||
' from (select distinct '||h_column_name||', '||h_up_column_name||', year'||
' from bsc_db_calendar'||
' where calendar_id = :4 and year = :5 and '||h_up_column_name||' > :6) cal,'||
' (select max('||h_column_name||') '||h_column_name||', '||h_up_column_name||', year'||
' from bsc_db_calendar'||
' where calendar_id = :7 and year = :8 and '||h_up_column_name||' > :9'||
' group by '||h_up_column_name||', year) bal'||
' where cal.'||h_column_name||' = bal.'||h_column_name||' (+) and'||
' cal.'||h_up_column_name||' = bal.'||h_up_column_name||' (+) and'||
' cal.year = bal.year (+)';
x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_key_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_partition_name IN VARCHAR2,
x_batch_value IN NUMBER,
x_num_partitions IN NUMBER,
x_parallel_jobs IN VARCHAR2,
x_rowid_table IN VARCHAR2,
x_num_loads IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_error_message OUT NOCOPY VARCHAR2
) IS
e_create_proc_load_tbl_sum_aw EXCEPTION;
h_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
h_dbi_dim_data BSC_UPDATE_DIM.t_dbi_dim_data;
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_sql:='select count (*) from USER_OBJECTS where OBJECT_TYPE=''PROCEDURE'''||
'and OBJECT_NAME=:1';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_key_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_partition_name IN VARCHAR2,
x_batch_value IN NUMBER,
x_num_partitions IN NUMBER,
x_parallel_jobs IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_error_message OUT NOCOPY VARCHAR2
) IS
h_sql VARCHAR2(32000);
h_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
h_dbi_dim_data BSC_UPDATE_DIM.t_dbi_dim_data;
h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(x_calendar_id);
h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
h_periodicity_type := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_periodicity);
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_bind_vars_values.delete;
h_sql := 'INSERT /*+ append';
IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
h_sql := h_sql||' parallel ('||x_base_table||')';
' SELECT';
IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
h_sql := h_sql||' /*+ parallel ('||x_input_table||')';
h_sql := h_sql||' FROM (SELECT';
IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
h_sql := h_sql||' /*+ parallel ('||x_input_table||') */';
' SELECT';
IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
h_sql := h_sql||' /*+ parallel ('||x_input_table||') */';
' SELECT';
' SELECT';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
h_bind_vars_values.delete;
h_sql := 'INSERT /*+ append';
IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
h_sql := h_sql||' parallel ('||x_base_table||')';
' SELECT';
IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
h_sql := h_sql||' /*+ parallel ('||x_input_table||')';
h_sql := h_sql||' FROM (SELECT';
IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
h_sql := h_sql||' /*+ parallel ('||x_input_table||') */';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
x_key_columns 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_partition_name IN VARCHAR2,
x_batch_value IN NUMBER,
x_parallel_jobs IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_error_message OUT NOCOPY VARCHAR2
) IS
h_sql VARCHAR2(32000);
h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
h_bind_vars_values.delete;
h_sql := 'INSERT /*+ append';
IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
h_sql := h_sql||' parallel ('||x_base_table||')';
' SELECT';
IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
-- Fix bug#5155523 Do not use parallel hint on BSC_DB_CALENDAR_TEMP
h_sql := h_sql||' /*+ parallel (BSC_B_DATA) */';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
| PROCEDURE Update_Base_Table_Job
+============================================================================*/
PROCEDURE Update_Base_Table_Job (
x_base_table IN VARCHAR2,
x_input_table IN VARCHAR2,
x_correction_flag IN BOOLEAN,
x_aw_flag IN BOOLEAN,
x_change_vector_value IN NUMBER,
x_periodicity IN NUMBER,
x_calendar_id IN NUMBER,
x_current_fy IN NUMBER,
x_current_period IN NUMBER,
x_old_current_period IN NUMBER,
x_proj_table IN VARCHAR2,
x_rowid_table IN VARCHAR2,
x_partition_name IN VARCHAR2,
x_batch_value IN NUMBER,
x_num_partitions IN NUMBER,
x_num_loads IN NUMBER,
x_job_name IN VARCHAR2
) IS
h_return_status VARCHAR2(50);
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;
IF NOT BSC_UPDATE.Init_Env_Values THEN
RAISE e_unexpected_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);
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_get_info_data_columns;
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_get_info_key_columns;
Update_Base_Table(x_base_table,
x_input_table,
x_correction_flag,
x_aw_flag,
x_change_vector_value,
x_periodicity,
x_calendar_id,
x_current_fy,
x_current_period,
x_old_current_period,
h_key_columns,
h_key_dim_tables,
h_num_key_columns,
h_data_columns,
h_data_formulas,
h_data_proj_methods,
h_data_measure_types,
h_num_data_columns,
x_proj_table,
x_rowid_table,
x_partition_name,
x_batch_value,
x_num_partitions,
x_num_loads,
'Y',
h_return_status,
h_error_message);
END Update_Base_Table_Job;
| PROCEDURE Update_Base_Table
+============================================================================*/
PROCEDURE Update_Base_Table (
x_base_table IN VARCHAR2,
x_input_table IN VARCHAR2,
x_correction_flag IN BOOLEAN,
x_aw_flag IN BOOLEAN,
x_change_vector_value IN NUMBER,
x_periodicity IN NUMBER,
x_calendar_id IN NUMBER,
x_current_fy IN NUMBER,
x_current_period IN NUMBER,
x_old_current_period IN NUMBER,
x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_key_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_proj_methods IN BSC_UPDATE_UTIL.t_array_of_number,
x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,
x_num_data_columns IN NUMBER,
x_proj_table IN VARCHAR2,
x_rowid_table IN VARCHAR2,
x_partition_name IN VARCHAR2,
x_batch_value IN NUMBER,
x_num_partitions IN NUMBER,
x_num_loads IN NUMBER,
x_parallel_jobs IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_error_message OUT NOCOPY VARCHAR2
) IS
e_init_calendar_temp_tbl EXCEPTION;
h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
h_sql := 'select count(*) from '||x_base_table||' where rownum < :1';
h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
h_bind_vars_values.delete;
h_sql := 'UPDATE '||x_base_table;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
h_sql := 'DELETE FROM '||x_base_table;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
IF BSC_UPDATE_CALC.Table_Has_Proj_Calc(x_base_table) THEN
-- Calculate projection for base periodicity
Calc_Projection(x_base_table,
x_proj_table,
x_aw_flag,
x_change_vector_value,
x_periodicity,
x_calendar_id,
x_current_period,
x_current_fy,
x_key_columns,
x_num_key_columns,
x_data_columns,
x_data_proj_methods,
x_num_data_columns,
x_partition_name,
x_batch_value,
x_parallel_jobs,
h_return_status,
h_error_message);
select c.parameter1
into h_yearly_periodicity
from bsc_db_calculations c, bsc_sys_periodicities p
where c.table_name = x_base_table and
c.calculation_type = 6 and
c.parameter1 = p.periodicity_id and
p.yearly_flag = 1;
x_error_message := 'Update_Base_Table.e_init_calendar_temp_tbl: '||h_error_message;
x_error_message := 'Update_Base_Table.e_load_input_table_initial: '||h_error_message;
x_error_message := 'Update_Base_Table.e_load_input_table_inc: '||h_error_message;
x_error_message := 'Update_Base_Table.e_calc_higher_periodicities: '||h_error_message;
x_error_message := 'Update_Base_Table.e_init_calendar_temp_tbl_proj: '||h_error_message;
x_error_message := 'Update_Base_Table.e_calc_projection: '||h_error_message;
x_error_message := 'Update_Base_Table.e_others: '||SQLERRM;
END Update_Base_Table;