[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
c_indicator_sql VARCHAR2(2000) := 'SELECT indicator, dim_set_id'||
' FROM bsc_kpi_data_tables'||
' WHERE table_name = :1 OR'||
' table_name = ('||
' SELECT DISTINCT table_name'||
' FROM bsc_db_calculations'||
' WHERE parameter1 = :2 AND'||
' calculation_type = :3)'; */
SELECT indicator, dim_set_id
FROM bsc_kpi_data_tables
WHERE table_name = pTableName
OR table_name = (
SELECT DISTINCT table_name
FROM bsc_db_calculations
WHERE parameter1 = pParam1
AND calculation_type = pCalcType );
c_filters_sql VARCHAR2(2000) := 'SELECT d.level_pk_col, d.level_view_name'||
' FROM bsc_kpi_dim_levels_b d, bsc_db_tables_cols c'||
' WHERE d.indicator = :1 AND d.dim_set_id = :2 AND d.status = :3 AND'||
' d.level_view_name <> ('||
' SELECT level_view_name'||
' FROM bsc_sys_dim_levels_b s'||
' WHERE d.level_pk_col = s.level_pk_col) AND'||
' c.table_name = :4 AND'||
' c.column_name = d.level_pk_col AND'||
' c.column_type = :5'; */
SELECT d.level_pk_col, d.level_view_name
FROM bsc_kpi_dim_levels_b d, bsc_db_tables_cols c
WHERE d.indicator = pIndicator AND d.dim_set_id = pDimSetId
AND d.status = pStatus
AND d.level_view_name <> (SELECT level_view_name
FROM bsc_sys_dim_levels_b s
WHERE d.level_pk_col = s.level_pk_col)
AND c.table_name = pTableName
AND c.column_name = d.level_pk_col
AND c.column_type = pColumnType;
h_sql := 'DELETE FROM '||x_table_name||
' WHERE '||h_key_column_name||' NOT IN ('||
' SELECT CODE FROM '||h_view_name||
')';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
X_Message => BSC_UPDATE_UTIL.Get_Message('BSC_APPLY_FILTER_FAILED'),
X_Source => 'BSC_UPDATE_CALC.Apply_Filters');
X_Source => 'BSC_UPDATE_CALC.Apply_Filters');
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_aw_flag IN BOOLEAN,
x_change_vector_value IN NUMBER
) RETURN BOOLEAN IS
e_unexpected_error EXCEPTION;
SELECT parameter1
FROM bsc_db_calculations
WHERE table_name = pTableName AND
calculation_type = pCalcType ;
SELECT r.relation_col
FROM bsc_sys_dim_levels_b e, bsc_sys_dim_level_rels r
WHERE e.dim_level_id = r.dim_level_id AND
e.level_pk_col = pLevelPkCol;
h_arr_keys_no_account BSC_UPDATE_UTIL.t_array_of_varchar2;
l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
SELECT level_view_name
FROM bsc_sys_dim_levels_b
WHERE level_pk_col = pLevelPkCol;
h_sql := 'SELECT code'||
' FROM '||h_account_dim_table||
' WHERE '||h_type_of_account_key||' = :1';
h_sql := 'SELECT code'||
' FROM '||h_account_dim_table||
' WHERE '||h_acc_key||' = ('||
' SELECT code'||
' FROM '||h_acc_table||
' WHERE '||h_type_of_account_key||' = :1)';
l_bind_vars_values.delete;
h_sql := 'DELETE FROM '||x_table_name||
' WHERE '||h_account_key||' = :1';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
h_lst_data_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_columns, x_num_data_columns);
h_sql := 'INSERT /*+ append ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'parallel ('||x_table_name||') ';
h_sql:=h_sql||') SELECT ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'/*+ parallel ('||x_table_name||') parallel ('||h_account_dim_table||')*/ ';
BSC_UPDATE_UTIL.Make_Lst_Table_Column(x_table_name, h_arr_keys_no_account, h_num_keys_no_account)||', '||
h_lst_sum_profit;
BSC_UPDATE_UTIL.Make_Lst_Table_Column(x_table_name, h_arr_keys_no_account, h_num_keys_no_account);
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
X_Message => BSC_UPDATE_UTIL.Get_Message('BSC_PROFIT_CALC_FAILED'),
X_Source => 'BSC_UPDATE_CALC.Calculate_Profit');
X_Source => 'BSC_UPDATE_CALC.Calculate_Profit');
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_lst_data_temp IN VARCHAR2, -- list of data columns in the projection table i.e: 'DATA1, DATA5'
x_current_fy IN NUMBER,
x_num_of_years IN NUMBER,
x_previous_years IN NUMBER,
x_is_base IN BOOLEAN,
x_aw_flag IN BOOLEAN
) RETURN BOOLEAN IS
e_unexpected_error EXCEPTION;
l_bind_vars_union BSC_UPDATE_UTIL.t_array_of_number;
l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
l_bind_vars_post BSC_UPDATE_UTIL.t_array_of_number;
h_key_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
h_lst_select VARCHAR2(32000);
h_lst_select := NULL;
h_lst_select := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', x_num_key_columns);
h_lst_keys := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns, x_num_key_columns);
h_lst_select := h_lst_select||', ';
h_lst_data_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_columns, x_num_data_columns);
h_lst_xmed_columns := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('XMED', x_num_data_columns);
h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
l_bind_vars_union.delete;
h_uni_table := '(SELECT '||h_lst_keys_tochar||'YEAR, TYPE, PERIOD, ';
'SELECT '||h_lst_keys_temp||'YEAR, TYPE, PERIOD, ';
h_num_periods := BSC_UPDATE_UTIL.Get_Num_Periods_Periodicity(x_periodicity, x_current_fy);
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_XMD');
l_bind_vars_values.delete;
l_bind_vars_post.delete;
l_bind_vars_values.delete ;
h_sql := 'INSERT /*+ append ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'parallel (bsc_tmp_xmd) ';
h_sql:=h_sql||'INTO bsc_tmp_xmd ('||h_lst_select||'YEAR, TYPE, PERIOD, ';
h_sql := h_sql||' SELECT ';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,l_num_bind_vars);
l_bind_vars_values.delete;
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_XMD_Y');
h_sql := 'INSERT /*+ append ';
IF BSC_UPDATE_UTIL.is_parallel THEN
h_sql := h_sql||'parallel (BSC_TMP_XMD_Y) ';
' SELECT B.ROWID, '||h_lst_xmed_columns_p||
' FROM BSC_TMP_PROJ_CALC B, BSC_TMP_XMD P'||
' WHERE ';
BSC_UPDATE_UTIL.Make_Lst_Cond_Join('B',
h_key_columns_temp,
'P',
h_key_columns_temp,
x_num_key_columns,
'AND')||
' AND';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
h_sql := 'UPDATE /*+ORDERED USE_NL(B)*/ BSC_TMP_PROJ_CALC B'||
' SET ('||x_lst_data_temp;
' SELECT '||h_lst_xmed_columns;
' WHERE B.ROWID IN (SELECT ROW_ID FROM BSC_TMP_XMD_Y)';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_XMD');
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_XMD_Y');
X_Message => BSC_UPDATE_UTIL.Get_Message('BSC_PROJ_ALY_FAILED'),
X_Source => 'BSC_UPDATE_CALC.Calculate_Proj_Avg_Last_Year');
X_Source => 'BSC_UPDATE_CALC.Calculate_Proj_Avg_Last_Year');
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_lst_data_temp IN VARCHAR2, -- list of data columns in the projection table i.e: 'DATA1, DATA5'
x_current_fy IN NUMBER,
x_is_base IN BOOLEAN,
x_aw_flag IN BOOLEAN
) RETURN BOOLEAN IS
e_unexpected_error EXCEPTION;
l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
h_key_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
h_lst_select VARCHAR2(32700);
h_lst_select := NULL;
h_lst_select := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', x_num_key_columns);
h_lst_keys := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns, x_num_key_columns);
h_lst_keys_p := BSC_UPDATE_UTIL.Make_Lst_Table_Column('P', h_key_columns_temp, x_num_key_columns);
h_lst_select := h_lst_select||', ';
h_lst_totplan := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('TOTPLAN', x_num_data_columns);
h_lst_totreal := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('TOTREAL', x_num_data_columns);
h_lst_plan := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('PLAN', x_num_data_columns);
h_lst_data := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('DATA', x_num_data_columns);
h_lst_data_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_columns, x_num_data_columns);
h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_PLAN');
l_bind_vars_values.delete ;
h_sql := 'INSERT /*+ append ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'parallel (bsc_tmp_tot_plan) ';
h_sql:=h_sql||'INTO BSC_TMP_TOT_PLAN ('||h_lst_select||'TYPE, '||h_lst_totplan||')'||
' SELECT ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'/*+ parallel ('||h_uni_table||')*/ ';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,l_num_bind_vars);
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_REAL');
l_bind_vars_values.delete ;
h_sql := 'INSERT /*+ append ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'parallel (bsc_tmp_tot_real) ';
h_sql:=h_sql||'INTO BSC_TMP_TOT_REAL('||h_lst_select||'TYPE, '||h_lst_totreal||')'||
' SELECT ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'/*+ parallel ('||h_uni_table||')*/ ';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,l_num_bind_vars );
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PLAN_PROJECTIONS');
l_bind_vars_values.delete;
h_sql := 'INSERT /*+ append ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'parallel (BSC_TMP_PLAN_PROJECTIONS) ';
h_sql:=h_sql||'INTO BSC_TMP_PLAN_PROJECTIONS ('||h_lst_select||'YEAR, TYPE, PERIOD, '||h_lst_plan||')'||
' SELECT ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'/*+ parallel ('||h_uni_table||')*/ ';
l_bind_vars_values.delete ;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,l_num_bind_vars);
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJECTIONS');
l_bind_vars_values.delete;
h_sql := 'INSERT /*+ append ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'parallel (BSC_TMP_PROJECTIONS) ';
h_sql:=h_sql||'INTO BSC_TMP_PROJECTIONS ('||h_lst_select||'YEAR, TYPE, PERIOD, ';
' SELECT '||h_lst_keys_p||'P.YEAR, P.TYPE, P.PERIOD, ';
BSC_UPDATE_UTIL.Make_Lst_Cond_Left_Join('P',
h_key_columns_temp,
'TP',
h_key_columns_temp,
x_num_key_columns,
'AND')||
' AND ';
BSC_UPDATE_UTIL.Make_Lst_Cond_Left_Join('P',
h_key_columns_temp,
'TR',
h_key_columns_temp,
x_num_key_columns,
'AND')||
' AND ';
BSC_UPDATE_UTIL.Make_Lst_Cond_Left_Join('P',
h_key_columns_temp,
'PP',
h_key_columns_temp,
x_num_key_columns,
'AND')||
' AND ';
l_bind_vars_values.delete ;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,l_num_bind_vars);
l_bind_vars_values.delete;
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJECTIONS_Y');
h_sql := 'INSERT /*+ append ';
IF BSC_UPDATE_UTIL.is_parallel THEN
h_sql := h_sql||'parallel (BSC_TMP_PROJECTIONS_Y) ';
' SELECT B.ROWID, '||h_lst_data_p||
' FROM BSC_TMP_PROJ_CALC B, BSC_TMP_PROJECTIONS P'||
' WHERE ';
BSC_UPDATE_UTIL.Make_Lst_Cond_Join('B',
h_key_columns_temp,
'P',
h_key_columns_temp,
x_num_key_columns,
'AND')||
' AND';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
h_sql := 'UPDATE /*+ORDERED USE_NL(B)*/ BSC_TMP_PROJ_CALC B'||
' SET ('||x_lst_data_temp;
' SELECT '||h_lst_data;
' WHERE B.ROWID IN (SELECT ROW_ID FROM BSC_TMP_PROJECTIONS_Y)';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_PLAN');
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_REAL');
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PLAN_PROJECTIONS');
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJECTIONS');
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJECTIONS_Y');
X_Message => BSC_UPDATE_UTIL.Get_Message('BSC_PROJ_THREEMONTH_FAILED'),
X_Source => 'BSC_UPDATE_CALC.Calculate_Proj_3_Periods_Perf');
X_Source => 'BSC_UPDATE_CALC.Calculate_Proj_3_Periods_Perf');
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_lst_data_temp IN VARCHAR2, -- list of data columns in the projection table i.e: 'DATA1, DATA5'
x_current_fy IN NUMBER,
x_is_base IN BOOLEAN,
x_aw_flag IN BOOLEAN
) RETURN BOOLEAN IS
e_unexpected_error EXCEPTION;
l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
h_key_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
h_lst_data_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_columns, x_num_data_columns);
l_bind_vars_values.delete;
h_sql := 'UPDATE BSC_TMP_PROJ_CALC T'||
' SET ('||x_lst_data_temp;
' SELECT '||h_lst_data_columns;
BSC_UPDATE_UTIL.Make_Lst_Cond_Join('T',
h_key_columns_temp,
'B',
x_key_columns,
x_num_key_columns,
'AND')||
' AND ';
h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,l_num_bind_vars);
X_Message => BSC_UPDATE_UTIL.Get_Message('BSC_PROJ_UD_FAILED'),
X_Source => 'BSC_UPDATE_CALC.Calculate_Proj_User_Defined');
X_Source => 'BSC_UPDATE_CALC.Calculate_Proj_User_Defined');
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_current_fy IN NUMBER,
x_num_of_years IN NUMBER,
x_previous_years IN NUMBER,
x_is_base IN BOOLEAN,
x_aw_flag IN BOOLEAN,
x_change_vector_value IN NUMBER
) RETURN BOOLEAN IS
e_unexpected_error EXCEPTION;
h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
h_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJ_CALC');
X_Message => BSC_UPDATE_UTIL.Get_Message('BSC_PROJ_FAILED'),
X_Source => 'BSC_UPDATE_CALC.Calculate_Projection');
X_Source => 'BSC_UPDATE_CALC.Calculate_Projection');
x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_key_columns IN NUMBER,
x_src_table IN VARCHAR2
) RETURN BOOLEAN IS
e_unexpected_error EXCEPTION;
SELECT parameter1
FROM bsc_db_calculations
WHERE table_name = pTableName
AND calculation_type = pCalcType
AND parameter1 NOT IN (
SELECT parameter1
FROM bsc_db_calculations
WHERE table_name = pSrcTableName
AND calculation_type = pSrcCalcType
)
GROUP BY parameter1, TO_NUMBER(parameter2)
ORDER BY TO_NUMBER(parameter2);
SELECT DISTINCT c.parameter1
FROM bsc_db_calculations c, bsc_kpi_data_tables kt, bsc_kpi_data_tables ktp
WHERE c.table_name = kt.table_name AND
c.calculation_type = 4 AND
kt.indicator = ktp.indicator AND
kt.dim_set_id = ktp.dim_set_id AND
ktp.table_name = pTableName;
SELECT column_name, source_formula
FROM bsc_db_tables_cols
WHERE table_name = pTableName AND column_type = pColumnType;
h_lst_select VARCHAR2(32700);
SELECT DISTINCT projection_data
FROM bsc_kpi_data_tables
WHERE table_name = p_sum_table;
h_zero_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
h_lst_select := NULL;
SELECT count(table_name)
INTO h_count
FROM bsc_db_calculations
WHERE table_name = x_table_name AND calculation_type = 5;
IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_key, x_key_columns, x_num_key_columns) THEN
IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_key, h_zero_key_columns, h_num_zero_key_columns) THEN
h_num_zero_key_columns := h_num_zero_key_columns + 1;
h_sql := 'DELETE FROM '||h_ref_table||
' WHERE '||h_lst_where;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
h_lst_select := '''0''';
h_lst_select := x_key_columns(1);
h_lst_select := h_lst_select||', ''0''';
h_lst_select := h_lst_select||', '||x_key_columns(h_i);
h_lst_select := h_lst_select||', YEAR, TYPE, PERIOD';
h_lst_select := h_lst_select||', PERIODICITY_ID, PERIOD_TYPE_ID';
h_sql := 'INSERT /*+ append ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'parallel ('||h_ref_table||') ';
' SELECT ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'/*+ parallel ('||h_ref_table||')*/ ';
h_sql:=h_sql||h_lst_select||', '||h_lst_expressions||
' FROM '||h_ref_table;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
X_Message => BSC_UPDATE_UTIL.Get_Message('BSC_ZEROCODE_CALC_FAILED'),
X_Source => 'BSC_UPDATE_CALC.Calculate_Zero_Code');
X_Source => 'BSC_UPDATE_CALC.Calculate_Zero_Code');
l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
h_edw_flag := BSC_UPDATE_UTIL.Get_Periodicity_EDW_Flag(x_periodicity);
h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_periodicity);
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_ALL_PERIODS');
l_bind_vars_values.delete ;
h_sql := 'INSERT INTO BSC_TMP_ALL_PERIODS (PERIOD)'||
' VALUES (:1)';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
h_calendar_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity);
l_bind_vars_values.delete ;
h_sql := 'INSERT INTO BSC_TMP_ALL_PERIODS'||
' SELECT DISTINCT '||h_calendar_col_name||
' FROM bsc_db_calendar'||
' WHERE YEAR = :1'||' AND CALENDAR_ID = :2'||
' GROUP BY '||h_calendar_col_name;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
l_bind_vars_values.delete ;
h_sql := 'INSERT INTO BSC_TMP_ALL_PERIODS (PERIOD)'||
' VALUES (:1)';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJ_CALC');
X_Message => BSC_UPDATE_UTIL.Get_Message('BSC_PROJ_TTABLES_FAILED'),
X_Source => 'BSC_UPDATE_CALC.Create_Proj_Temps');
X_Source => 'BSC_UPDATE_CALC.Create_Proj_Temps');
x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_key_columns IN NUMBER,
x_current_fy IN NUMBER,
x_current_period IN NUMBER,
x_is_base IN BOOLEAN,
x_aw_flag IN BOOLEAN,
x_change_vector_value IN NUMBER
) RETURN BOOLEAN IS
h_i NUMBER;
l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
h_key_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
h_lst_select VARCHAR2(32000);
h_lst_select := NULL;
SELECT current_period
INTO h_old_current_period
FROM bsc_db_tables
WHERE table_name = x_table_name;
h_lst_table_keys := BSC_UPDATE_UTIL.Make_Lst_Table_Column(h_ref_table,
x_key_columns,
x_num_key_columns);
h_lst_table_keys_a := BSC_UPDATE_UTIL.Make_Lst_Table_Column('A',
x_key_columns,
x_num_key_columns);
h_lst_keys_nc := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns, x_num_key_columns);
h_lst_table_keys := BSC_UPDATE_UTIL.Make_Lst_Table_Column(h_ref_table,
h_key_columns_temp,
x_num_key_columns);
h_lst_table_keys_a := BSC_UPDATE_UTIL.Make_Lst_Table_Column('A',
h_key_columns_temp,
x_num_key_columns);
h_lst_keys_nc := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', x_num_key_columns);
h_lst_table_keys := BSC_UPDATE_UTIL.Make_Lst_Table_Column(h_ref_table,
x_key_columns,
x_num_key_columns);
h_lst_table_keys_a := BSC_UPDATE_UTIL.Make_Lst_Table_Column('A',
x_key_columns,
x_num_key_columns);
h_lst_keys_nc := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns, x_num_key_columns);
h_lst_select := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', x_num_key_columns);
h_lst_select := h_lst_select||', ';
h_lst_select := h_lst_select||'PERIODICITY_ID, ';
h_lst_select := h_lst_select||'PERIOD_TYPE_ID, ';
h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
l_bind_vars_values.delete ;
h_sql := 'INSERT /*+ append ';
IF BSC_UPDATE_UTIL.is_parallel THEN
h_sql := h_sql||'parallel (BSC_TMP_PROJ_CALC) ';
' INTO BSC_TMP_PROJ_CALC ('||h_lst_select||'YEAR, TYPE, PERIOD';
' SELECT /*+ ordered */ '||h_lst_table_keys_a||'bsc_tmp_all_periods.period, 0, 0';
' SELECT DISTINCT '||h_lst_keys_nc||
' FROM '||h_ref_table;
h_sql := 'INSERT /*+ append ';
IF BSC_UPDATE_UTIL.is_parallel THEN
h_sql := h_sql||'parallel (BSC_TMP_PROJ_CALC) ';
' INTO BSC_TMP_PROJ_CALC ('||h_lst_select||'YEAR, TYPE, PERIOD';
' SELECT /*+ ordered */ '||h_lst_table_keys_a||':1, 0, bsc_tmp_all_periods.period';
' SELECT DISTINCT '||h_lst_keys_nc||
' FROM '||h_ref_table;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
X_Source => 'BSC_UPDATE_CALC.Init_Projection_Table');
| FUNCTION Delete_Projection
+============================================================================*/
FUNCTION Delete_Projection(
x_table_name IN VARCHAR2,
x_periodicity IN NUMBER,
x_period 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_current_fy IN NUMBER,
x_is_base IN BOOLEAN
) RETURN BOOLEAN IS
h_i NUMBER;
l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
l_bind_vars_values.delete ;
h_sql := 'UPDATE '||x_table_name||
' SET '||h_lst_set||
' WHERE YEAR > :1'||' AND TYPE = :2';
h_sql := 'UPDATE '||x_table_name||
' SET '||h_lst_set||
' WHERE YEAR = :1'||' AND PERIOD > :2'||' AND TYPE = :3';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
X_Source => 'BSC_UPDATE_CALC.Delete_Projection');
END Delete_Projection;
| FUNCTION Delete_Projection_Base_Table
+============================================================================*/
FUNCTION Delete_Projection_Base_Table(
x_table_name IN VARCHAR2,
x_periodicity IN NUMBER,
x_current_period IN NUMBER,
x_new_current_period 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_current_fy IN NUMBER,
x_aw_flag IN BOOLEAN,
x_change_vector_value IN NUMBER
) RETURN BOOLEAN IS
h_i NUMBER;
l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
l_bind_vars_values.delete ;
h_sql := 'UPDATE '||x_table_name||
' SET '||h_lst_set||
' WHERE YEAR = :1 AND PERIOD > :2 AND PERIOD <= :3 AND TYPE = :4';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
l_bind_vars_values.delete ;
h_sql := 'UPDATE '||x_table_name||
' SET '||h_lst_set||
' WHERE YEAR > :1'||' AND TYPE = :2';
h_sql := 'UPDATE '||x_table_name||
' SET '||h_lst_set||
' WHERE YEAR = :1'||' AND PERIOD > :2'||' AND TYPE = :3';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
X_Source => 'BSC_UPDATE_CALC.Delete_Projection_Base_Table');
END Delete_Projection_Base_Table;
X_Source => 'BSC_UPDATE_CALC.Drop_Proj_Temps');
X_Source => 'BSC_UPDATE_CALC.Drop_Proj_Temps');
c_calculation_type_sql VARCHAR2(2000) := 'SELECT calculation_type'||
' FROM bsc_db_calculations'||
' WHERE table_name = :1 AND'||
' (calculation_type = :2 OR calculation_type = :3)'; */
SELECT calculation_type
FROM bsc_db_calculations
WHERE table_name = pTableName
AND (calculation_type = pCalcType OR calculation_type = pCalcType2) ;
X_Source => 'BSC_UPDATE_CALC.Get_Zero_Code_Calc_Method');
x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_key_columns IN NUMBER
) RETURN BOOLEAN IS
TYPE t_cursor IS REF CURSOR;
c_source_tables_sql VARCHAR2(2000) := 'SELECT DISTINCT parameter1'||
' FROM bsc_db_calculations'||
' WHERE table_name = :1 AND'||
' calculation_type = :2'; */
SELECT DISTINCT parameter1
FROM bsc_db_calculations
WHERE table_name = pTableName
AND calculation_type = pCalcType ;
c_data_columns_sql VARCHAR2(2000) := 'SELECT parameter2'||
' FROM bsc_db_calculations'||
' WHERE table_name = :1 AND'||
' calculation_type = :2 AND'||
' parameter1 = :3'; */
SELECT parameter2
FROM bsc_db_calculations
WHERE table_name = pTableName
AND calculation_type = pCalcType
AND parameter1 = pParam1 ;
h_lst_key_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns, x_num_key_columns);
h_sql := 'UPDATE '||x_table_name||' T'||
' SET ('||h_lst_data_columns||') = ('||
' SELECT '||h_lst_data_columns||
' FROM '||h_source_table||' S'||
' WHERE ';
BSC_UPDATE_UTIL.Make_Lst_Cond_Join('T', x_key_columns,
'S', x_key_columns,
x_num_key_columns, 'AND')||
' AND ';
' SELECT '||h_lst_key_columns||'YEAR, TYPE, PERIOD'||
' FROM '||h_source_table||
' )';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
h_sql := 'INSERT /*+ append ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'parallel ('||x_table_name||') ';
' SELECT ';
if BSC_UPDATE_UTIL.is_parallel then
h_sql:=h_sql||'/*+ parallel ('||h_source_table||')*/ ';
' SELECT '||h_lst_key_columns||'YEAR, TYPE, PERIOD'||
' FROM '||x_table_name||
' )';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
X_Source => 'BSC_UPDATE_CALC.Merge_Data_From_Tables');
x_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_key_columns NUMBER,
x_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_data_columns NUMBER,
x_is_base BOOLEAN,
x_aw_flag BOOLEAN
) RETURN BOOLEAN IS
h_sql VARCHAR2(32700);
h_key_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
h_lst_keys_temp := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', x_num_key_columns);
h_cond_join := BSC_UPDATE_UTIL.Make_Lst_Cond_Join('A', x_key_columns,
'B', h_key_columns_temp,
x_num_key_columns, 'AND');
h_lst_keys_a := BSC_UPDATE_UTIL.Make_Lst_Table_Column('A', x_key_columns, x_num_key_columns);
h_lst_keys_b := BSC_UPDATE_UTIL.Make_Lst_Table_Column('B', h_key_columns_temp, x_num_key_columns);
h_lst_data_temp := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('DATA', x_num_data_columns);
' USING (SELECT '||h_lst_keys_temp||', '||h_lst_data_temp||' FROM BSC_TMP_PROJ_CALC) B'||
' ON ('||h_cond_join||')'||
' WHEN MATCHED THEN UPDATE SET '||h_lst_set_data||
' WHEN NOT MATCHED THEN'||
' INSERT ('||h_lst_keys_a||', '||h_lst_data_a||')'||
' VALUES ('||h_lst_keys_b||', '||h_lst_data_b||')';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
X_Source => 'BSC_UPDATE_CALC.Merge_Projection');
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_current_fy IN NUMBER,
x_periodicity IN NUMBER,
x_current_period OUT NOCOPY NUMBER
) RETURN BOOLEAN IS
e_unexpected_error EXCEPTION;
IF NOT BSC_UPDATE_UTIL.Get_Table_Range_Of_Years(x_table_name, h_num_of_years, h_previous_years) THEN
RAISE e_unexpected_error;
h_edw_mv_name := BSC_UPDATE_UTIL.Get_EDW_Materialized_View_Name(x_table_name);
h_lst_key_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns, x_num_key_columns);
h_sql := 'DELETE FROM '||x_table_name||
' WHERE ('||h_lst_key_columns||'YEAR, TYPE, PERIOD) IN ('||
' SELECT '||h_lst_key_columns||'YEAR, TYPE, PERIOD '||
' FROM '||h_edw_mv_name||')';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
h_edw_uv_name := BSC_UPDATE_UTIL.Get_EDW_Union_View_Name(x_table_name);
h_lst_data_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_columns, x_num_data_columns);
' SELECT '||h_lst_key_columns||'YEAR, TYPE, PERIOD, '||h_lst_data_columns||
' FROM '||x_table_name||
' UNION '||
' SELECT '||h_lst_key_columns||'YEAR, TYPE, PERIOD, '||h_lst_data_columns||
' FROM '||h_edw_mv_name||')';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
h_sql := 'SELECT NVL(MAX(period),1) '||
' FROM '||h_edw_mv_name||
' WHERE year = :1'||
' AND type = :2';
X_Message => BSC_UPDATE_UTIL.Get_Message('BSC_REFRESH_EDW_VIEWS_FAILED'),
X_Source => 'BSC_UPDATE_CALC.Refresh_EDW_Views');
X_Source => 'BSC_UPDATE_CALC.Refresh_EDW_Views');
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_current_fy IN NUMBER,
x_is_base IN BOOLEAN
) 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_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
h_lst_select_disag := NULL;
h_lst_select_per := NULL;
select period_type_id
into h_period_type_id
from bsc_sys_periodicities
where periodicity_id = 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_base_periodicity);
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 (bsc_db_calendar)*/ ';
l_bind_vars_values.delete;
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 (bsc_db_calendar)*/ ';
l_bind_vars_values.delete;
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');
h_lst_key_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(h_key_columns_temp, 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_select_disag := h_lst_select_disag||'BSC_TMP_PROJ_CALC.'||h_key_columns_temp(h_i)||', ';
h_lst_select_per := 'BSC_TMP_PROJ_CALC.YEAR, BSC_TMP_PROJ_CALC.TYPE, BSC_TMP_PER_CHANGE.TRG_PER';
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||', :1, ';
' GROUP BY '||h_lst_select_disag||h_lst_select_per;
l_bind_vars_values.delete;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
h_lst_select_per := 'BSC_TMP_PROJ_CALC.YEAR, BSC_TMP_PROJ_CALC.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||', :1, ';
' GROUP BY '||h_lst_select_disag||h_lst_select_per;
l_bind_vars_values.delete;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
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 (BSC_TMP_PROJ_CALC) ';
' 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, T.PERIODICITY_ID, ';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
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');
X_Message => BSC_UPDATE_UTIL.Get_Message('BSC_PROJ_FAILED'),
X_Source => 'BSC_UPDATE_CALC.Rollup_Projection');
X_Source => 'BSC_UPDATE_CALC.Rollup_Projection');
SELECT calculation_type
FROM bsc_db_calculations
WHERE table_name = pTableName
AND calculation_type = pCalcType;
X_Source => 'BSC_UPDATE_CALC.Table_Has_Profit_Calc');
SELECT table_name
FROM bsc_db_tables t
WHERE table_name = pTableName AND project_flag = pProjFlag AND
pColCnt <> (SELECT COUNT(tc.column_name)
FROM bsc_db_tables_cols tc, bsc_db_measure_cols_vl m
WHERE tc.table_name = t.table_name AND
tc.column_type = pColType AND
NVL(tc.source, pBSCSource) = pBSCSource1 AND
tc.column_name = m.measure_col (+) AND
NVL(m.projection_id, pProjId) <> pProjId2);
X_Source => 'BSC_UPDATE_CALC.Table_Has_Proj_Calc');