eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BSC_UPDATE_BASE dependencies on BSC_UPDATE_UTIL

Line 74: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity_base_table);

70: -- Always is possible to pass to Annual periodicity
71: -- From periodicity type 12 (Month Day) is possible to pass to any periodicity
72: -- From periodicity type 11 (Month Week) is possible to pass only to 7 (Week52)
73:
74: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity_base_table);
75: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_periodicity_base_table);
76:
77: h_periodicity_type_input_table := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_periodicity_input_table);
78: h_periodicity_type_base_table := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_periodicity_base_table);

Line 75: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_periodicity_base_table);

71: -- From periodicity type 12 (Month Day) is possible to pass to any periodicity
72: -- From periodicity type 11 (Month Week) is possible to pass only to 7 (Week52)
73:
74: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity_base_table);
75: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_periodicity_base_table);
76:
77: h_periodicity_type_input_table := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_periodicity_input_table);
78: h_periodicity_type_base_table := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_periodicity_base_table);
79:

Line 77: h_periodicity_type_input_table := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_periodicity_input_table);

73:
74: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity_base_table);
75: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_periodicity_base_table);
76:
77: h_periodicity_type_input_table := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_periodicity_input_table);
78: h_periodicity_type_base_table := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_periodicity_base_table);
79:
80: IF h_yearly_flag = 1 THEN
81: -- The base table has annual periodicity

Line 78: h_periodicity_type_base_table := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_periodicity_base_table);

74: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity_base_table);
75: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_periodicity_base_table);
76:
77: h_periodicity_type_input_table := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_periodicity_input_table);
78: h_periodicity_type_base_table := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_periodicity_base_table);
79:
80: IF h_yearly_flag = 1 THEN
81: -- The base table has annual periodicity
82: -- The period of an annual table is the current fiscal year

Line 102: h_edw_flag := BSC_UPDATE_UTIL.Get_Periodicity_EDW_Flag(x_periodicity_base_table);

98: END;
99:
100: ELSE
101: -- Other periodicity changes
102: h_edw_flag := BSC_UPDATE_UTIL.Get_Periodicity_EDW_Flag(x_periodicity_base_table);
103:
104: IF h_edw_flag = 0 THEN
105: -- BSC periodicity
106:

Line 108: h_base_calendar_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity_base_table);

104: IF h_edw_flag = 0 THEN
105: -- BSC periodicity
106:
107: -- Use bsc_db_calendar to make the transformation
108: h_base_calendar_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity_base_table);
109:
110: IF h_periodicity_type_input_table = 12 THEN
111: -- The input table is Month-Day
112: h_sql := 'SELECT '||h_base_calendar_col_name||' '||

Line 125: h_input_calendar_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity_input_table);

121: x_per_base_table := 0;
122: END IF;
123: CLOSE h_cursor;
124: ELSE
125: h_input_calendar_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity_input_table);
126: h_sql := 'SELECT MAX('||h_base_calendar_col_name||') '||
127: 'FROM bsc_db_calendar '||
128: 'WHERE year = :1 '||
129: 'AND '||h_input_calendar_col_name||' = :2 '||

Line 217: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_periodicity);

213: h_yearly_flag := 0;
214: h_calendar_id := NULL;
215: h_calendar_source := NULL;
216:
217: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_periodicity);
218: h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(h_calendar_id);
219:
220: -- Get Target_Flag of the input table
221: h_target_flag := BSC_UPDATE_UTIL.Get_Table_Target_Flag(x_input_table);

Line 218: h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(h_calendar_id);

214: h_calendar_id := NULL;
215: h_calendar_source := NULL;
216:
217: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_periodicity);
218: h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(h_calendar_id);
219:
220: -- Get Target_Flag of the input table
221: h_target_flag := BSC_UPDATE_UTIL.Get_Table_Target_Flag(x_input_table);
222:

Line 221: h_target_flag := BSC_UPDATE_UTIL.Get_Table_Target_Flag(x_input_table);

217: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_periodicity);
218: h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(h_calendar_id);
219:
220: -- Get Target_Flag of the input table
221: h_target_flag := BSC_UPDATE_UTIL.Get_Table_Target_Flag(x_input_table);
222:
223: -- Get the current period and subperiod of the input table
224: BEGIN
225: SELECT NVL(current_period, 0), NVL(current_subperiod, 0)

Line 236: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);

232: h_current_subperiod := 0;
233: END;
234:
235: -- Get yearly flag of the periodicity
236: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
237:
238: -- Get the maximun period of real data reported in the input table
239: IF h_yearly_flag = 1 THEN -- Annually
240: -- The update period of an annual table is always the current

Line 263: h_periodicity_type := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_periodicity);

259: ELSE
260: -- BIS periodicity
261: --BSC-BIS-DIMENSIONS: The input table has a column called TIME_FK instead of YEAR, PERIOD
262:
263: h_periodicity_type := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_periodicity);
264:
265: IF h_periodicity_type = 9 THEN
266: -- It is a daily periodicity. The TIME_FK column in the input table is of type DATE.
267: h_sql := 'SELECT MAX(p.period_id)'||

Line 402: h_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2;

398: h_projection_flag VARCHAR2(3); -- This indicates that at least one data column has projection
399: h_project_flag NUMBER; -- This indicates that the table has project_flag = 1 or 0
400:
401: -- Data columns information
402: h_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
403: h_data_formulas BSC_UPDATE_UTIL.t_array_of_varchar2;
404: h_data_proj_methods BSC_UPDATE_UTIL.t_array_of_number;
405: h_data_measure_types BSC_UPDATE_UTIL.t_array_of_number;
406: h_num_data_columns NUMBER;

Line 403: h_data_formulas BSC_UPDATE_UTIL.t_array_of_varchar2;

399: h_project_flag NUMBER; -- This indicates that the table has project_flag = 1 or 0
400:
401: -- Data columns information
402: h_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
403: h_data_formulas BSC_UPDATE_UTIL.t_array_of_varchar2;
404: h_data_proj_methods BSC_UPDATE_UTIL.t_array_of_number;
405: h_data_measure_types BSC_UPDATE_UTIL.t_array_of_number;
406: h_num_data_columns NUMBER;
407:

Line 404: h_data_proj_methods BSC_UPDATE_UTIL.t_array_of_number;

400:
401: -- Data columns information
402: h_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
403: h_data_formulas BSC_UPDATE_UTIL.t_array_of_varchar2;
404: h_data_proj_methods BSC_UPDATE_UTIL.t_array_of_number;
405: h_data_measure_types BSC_UPDATE_UTIL.t_array_of_number;
406: h_num_data_columns NUMBER;
407:
408: -- Key column information

Line 405: h_data_measure_types BSC_UPDATE_UTIL.t_array_of_number;

401: -- Data columns information
402: h_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
403: h_data_formulas BSC_UPDATE_UTIL.t_array_of_varchar2;
404: h_data_proj_methods BSC_UPDATE_UTIL.t_array_of_number;
405: h_data_measure_types BSC_UPDATE_UTIL.t_array_of_number;
406: h_num_data_columns NUMBER;
407:
408: -- Key column information
409: h_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2;

Line 409: h_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2;

405: h_data_measure_types BSC_UPDATE_UTIL.t_array_of_number;
406: h_num_data_columns NUMBER;
407:
408: -- Key column information
409: h_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
410: h_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
411: h_source_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
412: h_source_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
413: h_num_key_columns NUMBER;

Line 410: h_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;

406: h_num_data_columns NUMBER;
407:
408: -- Key column information
409: h_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
410: h_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
411: h_source_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
412: h_source_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
413: h_num_key_columns NUMBER;
414: h_lst_key_columns VARCHAR2(32700);

Line 411: h_source_columns BSC_UPDATE_UTIL.t_array_of_varchar2;

407:
408: -- Key column information
409: h_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
410: h_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
411: h_source_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
412: h_source_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
413: h_num_key_columns NUMBER;
414: h_lst_key_columns VARCHAR2(32700);
415:

Line 412: h_source_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;

408: -- Key column information
409: h_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
410: h_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
411: h_source_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
412: h_source_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
413: h_num_key_columns NUMBER;
414: h_lst_key_columns VARCHAR2(32700);
415:
416: -- Number of year and previous years of the table

Line 456: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;

452:
453: --AW_INTEGRATION: new variables
454: h_aw_table VARCHAR2(30);
455: h_proj_filter VARCHAR2(32000);
456: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
457: h_num_bind_vars NUMBER;
458: h_change_vector_value NUMBER;
459:
460: BEGIN

Line 475: h_periodicity_base_table := BSC_UPDATE_UTIL.Get_Table_Periodicity(x_base_table);

471: h_change_vector_value := bsc_aw_load.get_bt_next_change_vector(x_base_table);
472: END IF;
473:
474: -- Get the periodicity of the base table
475: h_periodicity_base_table := BSC_UPDATE_UTIL.Get_Table_Periodicity(x_base_table);
476:
477: IF h_periodicity_base_table IS NULL THEN
478: RAISE e_unexpected_error;
479: END IF;

Line 482: h_periodicity_input_table := BSC_UPDATE_UTIL.Get_Table_Periodicity(x_input_table);

478: RAISE e_unexpected_error;
479: END IF;
480:
481: -- Get the periodicity of the input table
482: h_periodicity_input_table := BSC_UPDATE_UTIL.Get_Table_Periodicity(x_input_table);
483:
484: IF h_periodicity_input_table IS NULL THEN
485: RAISE e_unexpected_error;
486: END IF;

Line 489: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(h_periodicity_base_table);

485: RAISE e_unexpected_error;
486: END IF;
487:
488: -- Get the calendar id of the input/base table
489: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(h_periodicity_base_table);
490:
491: -- Get the current fiscal year
492: h_current_fy := BSC_UPDATE_UTIL.Get_Calendar_Fiscal_Year(h_calendar_id);
493:

Line 492: h_current_fy := BSC_UPDATE_UTIL.Get_Calendar_Fiscal_Year(h_calendar_id);

488: -- Get the calendar id of the input/base table
489: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(h_periodicity_base_table);
490:
491: -- Get the current fiscal year
492: h_current_fy := BSC_UPDATE_UTIL.Get_Calendar_Fiscal_Year(h_calendar_id);
493:
494: -- Get the number of years and previous years of the table
495: IF NOT BSC_UPDATE_UTIL.Get_Table_Range_Of_Years(x_base_table, h_num_of_years, h_previous_years) THEN
496: RAISE e_unexpected_error;

Line 495: IF NOT BSC_UPDATE_UTIL.Get_Table_Range_Of_Years(x_base_table, h_num_of_years, h_previous_years) THEN

491: -- Get the current fiscal year
492: h_current_fy := BSC_UPDATE_UTIL.Get_Calendar_Fiscal_Year(h_calendar_id);
493:
494: -- Get the number of years and previous years of the table
495: IF NOT BSC_UPDATE_UTIL.Get_Table_Range_Of_Years(x_base_table, h_num_of_years, h_previous_years) THEN
496: RAISE e_unexpected_error;
497: END IF;
498:
499: -- Get period column name and subperiod column name in the input table

Line 500: IF NOT BSC_UPDATE_UTIL.Get_Period_Cols_Names(h_periodicity_input_table, h_period_col_name, h_subperiod_col_name) THEN

496: RAISE e_unexpected_error;
497: END IF;
498:
499: -- Get period column name and subperiod column name in the input table
500: IF NOT BSC_UPDATE_UTIL.Get_Period_Cols_Names(h_periodicity_input_table, h_period_col_name, h_subperiod_col_name) THEN
501: RAISE e_unexpected_error;
502: END IF;
503:
504: -- Create BSC_EDW_TIME_MAP table, in case there is change of periodicity

Line 540: h_generation_type := BSC_UPDATE_UTIL.Get_Table_Generation_Type(x_base_table);

536:
537: -- Retrieve information of the input and base table to be processed.
538:
539: -- Base table generation type
540: h_generation_type := BSC_UPDATE_UTIL.Get_Table_Generation_Type(x_base_table);
541:
542: IF h_generation_type IS NULL THEN
543: RAISE e_unexpected_error;
544: END IF;

Line 546: IF NOT BSC_UPDATE_UTIL.Get_Information_Data_Columns(x_base_table,

542: IF h_generation_type IS NULL THEN
543: RAISE e_unexpected_error;
544: END IF;
545:
546: IF NOT BSC_UPDATE_UTIL.Get_Information_Data_Columns(x_base_table,
547: h_data_columns,
548: h_data_formulas,
549: h_data_proj_methods,
550: h_data_measure_types,

Line 555: IF NOT BSC_UPDATE_UTIL.Get_Information_Key_Columns(x_base_table,

551: h_num_data_columns) THEN
552: RAISE e_unexpected_error;
553: END IF;
554:
555: IF NOT BSC_UPDATE_UTIL.Get_Information_Key_Columns(x_base_table,
556: h_key_columns,
557: h_key_dim_tables,
558: h_source_columns,
559: h_source_dim_tables,

Line 592: h_current_period := BSC_UPDATE_UTIL.Get_Period_Other_Periodicity(

588: LOOP
589: FETCH c_other_periodicities INTO h_other_periodicity_id, h_yearly_flag;
590: EXIT WHEN c_other_periodicities%NOTFOUND;
591:
592: h_current_period := BSC_UPDATE_UTIL.Get_Period_Other_Periodicity(
593: h_other_periodicity_id,
594: h_calendar_id,
595: h_yearly_flag,
596: h_current_fy,

Line 607: h_current_period := BSC_UPDATE_UTIL.Get_Period_Other_Periodicity(

603: h_arr_other_periodicities(h_num_other_periodicities).current_period := h_current_period;
604:
605: -- Fix bug: In calculate projection for other periodicities we need to pass the new
606: -- current period not the current current period!!!
607: h_current_period := BSC_UPDATE_UTIL.Get_Period_Other_Periodicity(
608: h_other_periodicity_id,
609: h_calendar_id,
610: h_yearly_flag,
611: h_current_fy,

Line 676: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(h_periodicity_base_table);

672: -- Fix bug#4653405: AW_INTEGRATION: PROJECTION flag is now set to Y for all the rows beyond
673: -- current period no matter if it is target or actual. So if the current period changes
674: -- we need to update to N betwen old current period and new current period for type <> 0
675: IF x_aw_flag THEN
676: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(h_periodicity_base_table);
677: IF (h_yearly_flag <> 1) AND (h_per_base_table > h_current_per_base_table) THEN
678: h_num_bind_vars := 0;
679: h_bind_vars_values.delete;
680:

Line 690: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);

686: h_bind_vars_values(3) := h_current_per_base_table;
687: h_bind_vars_values(4) := h_per_base_table;
688: h_bind_vars_values(5) := 0;
689: h_num_bind_vars := 5 ;
690: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
691: commit;
692: END IF;
693: END IF;
694:

Line 849: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJ_CALC');

845: RAISE e_unexpected_error;
846: END IF;
847:
848: -- Fix bug#4463132: Truncate temporary table after use
849: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJ_CALC');
850: commit;
851: END IF;
852: END IF;
853:

Line 910: BSC_UPDATE_UTIL.Truncate_Table(x_input_table);

906:
907: COMMIT;
908:
909: -- Delete data from input table
910: BSC_UPDATE_UTIL.Truncate_Table(x_input_table);
911: END IF;
912:
913: COMMIT;
914:

Line 922: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE');

918: commit;
919: END IF;
920:
921: -- Fix bug#4463132: Truncate temporary table after use
922: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE');
923: commit;
924:
925: RETURN TRUE;
926:

Line 930: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_BTABLE_CALCULATION_FAILED'),

926:
927: EXCEPTION
928: WHEN e_unexpected_error THEN
929: ROLLBACK;
930: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_BTABLE_CALCULATION_FAILED'),
931: x_source => 'BSC_UPDATE_BASE.Calculate_Base_Table');
932: RETURN FALSE;
933:
934: WHEN OTHERS THEN

Line 975: h_table_columns BSC_UPDATE_UTIL.t_array_temp_table_cols;

971: h_num_data_columns NUMBER;
972: h_i NUMBER;
973:
974: h_table_name VARCHAR2(30);
975: h_table_columns BSC_UPDATE_UTIL.t_array_temp_table_cols;
976: h_num_columns NUMBER;
977:
978: BEGIN
979:

Line 1048: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN

1044: h_table_columns(h_num_columns).column_name := 'CHANGE_VECTOR';
1045: h_table_columns(h_num_columns).data_type := 'NUMBER';
1046: h_table_columns(h_num_columns).data_size := NULL;
1047: h_table_columns(h_num_columns).add_to_index := 'N';
1048: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
1049: RAISE e_unexpected_error;
1050: END IF;
1051:
1052: -- BSC-MV Note: These temp tables are used only in bsc-mv architecture

Line 1061: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN

1057: IF BSC_APPS.bsc_mv THEN
1058: -- BSC_TMP_BASE_BU
1059: --Bug#3875046: Do not create index on temporary tables
1060: h_table_name := 'BSC_TMP_BASE_BU';
1061: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
1062: RAISE e_unexpected_error;
1063: END IF;
1064:
1065: ---------------venu--------------------------------------------

Line 1070: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN

1066: --we need this table for balance calculations
1067: -- BSC_TMP_BASE_BAL
1068: h_table_name := 'BSC_TMP_BASE_BAL';
1069: --Bug#3875046: Do not create index on temporary tables
1070: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
1071: RAISE e_unexpected_error;
1072: END IF;
1073: -----------------------------------------------------------------
1074: END IF;

Line 1079: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN

1075:
1076: -- BSC_TMP_PROJECTIONS (Note this table has same structure as previous table
1077: --Bug#3875046: Do not create index on temporary tables
1078: h_table_name := 'BSC_TMP_PROJECTIONS';
1079: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
1080: RAISE e_unexpected_error;
1081: END IF;
1082:
1083: --ENH_PROJECTION_4235711: New temporary table used to calculate projection

Line 1087: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN

1083: --ENH_PROJECTION_4235711: New temporary table used to calculate projection
1084: -- BSC_TMP_PROJ_CALC (Note this table has same structure as previous table
1085: --Bug#3875046: Do not create index on temporary tables
1086: h_table_name := 'BSC_TMP_PROJ_CALC';
1087: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
1088: RAISE e_unexpected_error;
1089: END IF;
1090:
1091: -- BSC_TMP_TOT_DATA (Note this table has same structure as previous table

Line 1094: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN

1090:
1091: -- BSC_TMP_TOT_DATA (Note this table has same structure as previous table
1092: --Bug#3875046: Do not create index on temporary tables
1093: h_table_name := 'BSC_TMP_TOT_DATA';
1094: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
1095: RAISE e_unexpected_error;
1096: END IF;
1097:
1098: -- BSC_TMP_BAL_DATA (Note this table has same structure as previous table

Line 1101: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN

1097:
1098: -- BSC_TMP_BAL_DATA (Note this table has same structure as previous table
1099: --Bug#3875046: Do not create index on temporary tables
1100: h_table_name := 'BSC_TMP_BAL_DATA';
1101: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
1102: RAISE e_unexpected_error;
1103: END IF;
1104:
1105: -- BSC_TMP_PER_CHANGE

Line 1125: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN

1121: h_table_columns(h_num_columns).column_name := 'TRG_PER';
1122: h_table_columns(h_num_columns).data_type := 'NUMBER';
1123: h_table_columns(h_num_columns).data_size := 5;
1124: h_table_columns(h_num_columns).add_to_index := 'N';
1125: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
1126: RAISE e_unexpected_error;
1127: END IF;
1128:
1129: -- BSC_TMP_PER_CHANGE_BAL (Note it has the same strucutre as the previouos table)

Line 1132: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN

1128:
1129: -- BSC_TMP_PER_CHANGE_BAL (Note it has the same strucutre as the previouos table)
1130: --Bug#3875046: Do not create index on temporary tables
1131: h_table_name := 'BSC_TMP_PER_CHANGE_BAL';
1132: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
1133: RAISE e_unexpected_error;
1134: END IF;
1135:
1136: -- BSC_TMP_ALL_PERIODS

Line 1145: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN

1141: h_table_columns(h_num_columns).column_name := 'PERIOD';
1142: h_table_columns(h_num_columns).data_type := 'NUMBER';
1143: h_table_columns(h_num_columns).data_size := 5;
1144: h_table_columns(h_num_columns).add_to_index := 'N';
1145: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
1146: RAISE e_unexpected_error;
1147: END IF;
1148:
1149: -- BSC_TMP_DISAG_ALL_PERIODS

Line 1188: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN

1184: h_table_columns(h_num_columns).column_name := 'PERIOD';
1185: h_table_columns(h_num_columns).data_type := 'NUMBER';
1186: h_table_columns(h_num_columns).data_size := 5;
1187: h_table_columns(h_num_columns).add_to_index := 'N';
1188: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
1189: RAISE e_unexpected_error;
1190: END IF;
1191:
1192: -- BSC_TMP_TOT_PLAN

Line 1216: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN

1212: h_table_columns(h_num_columns).data_type := 'NUMBER';
1213: h_table_columns(h_num_columns).data_size := NULL;
1214: h_table_columns(h_num_columns).add_to_index := 'N';
1215: END LOOP;
1216: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
1217: RAISE e_unexpected_error;
1218: END IF;
1219:
1220: -- BSC_TMP_TOT_REAL

Line 1244: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN

1240: h_table_columns(h_num_columns).data_type := 'NUMBER';
1241: h_table_columns(h_num_columns).data_size := NULL;
1242: h_table_columns(h_num_columns).add_to_index := 'N';
1243: END LOOP;
1244: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
1245: RAISE e_unexpected_error;
1246: END IF;
1247:
1248: -- BSC_TMP_PLAN_PROJECTIONS

Line 1282: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN

1278: h_table_columns(h_num_columns).data_type := 'NUMBER';
1279: h_table_columns(h_num_columns).data_size := NULL;
1280: h_table_columns(h_num_columns).add_to_index := 'N';
1281: END LOOP;
1282: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
1283: RAISE e_unexpected_error;
1284: END IF;
1285:
1286: -- BSC_TMP_PROJECTIONS_Y

Line 1302: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN

1298: h_table_columns(h_num_columns).data_type := 'NUMBER';
1299: h_table_columns(h_num_columns).data_size := NULL;
1300: h_table_columns(h_num_columns).add_to_index := 'N';
1301: END LOOP;
1302: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
1303: RAISE e_unexpected_error;
1304: END IF;
1305:
1306: -- BSC_TMP_XMD

Line 1347: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN

1343: h_table_columns(h_num_columns).data_type := 'NUMBER';
1344: h_table_columns(h_num_columns).data_size := NULL;
1345: h_table_columns(h_num_columns).add_to_index := 'N';
1346: END LOOP;
1347: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
1348: RAISE e_unexpected_error;
1349: END IF;
1350:
1351: -- BSC_TMP_XMD_Y

Line 1367: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN

1363: h_table_columns(h_num_columns).data_type := 'NUMBER';
1364: h_table_columns(h_num_columns).data_size := NULL;
1365: h_table_columns(h_num_columns).add_to_index := 'N';
1366: END LOOP;
1367: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
1368: RAISE e_unexpected_error;
1369: END IF;
1370:
1371: -- BSC_TMP_UNION

Line 1398: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN

1394: h_table_columns(h_num_columns).column_name := 'PERIOD';
1395: h_table_columns(h_num_columns).data_type := 'NUMBER';
1396: h_table_columns(h_num_columns).data_size := 5;
1397: h_table_columns(h_num_columns).add_to_index := 'N';
1398: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
1399: RAISE e_unexpected_error;
1400: END IF;
1401:
1402: -- Bug#3842096 Need this new temporary table

Line 1430: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN

1426: h_table_columns(h_num_columns).column_name := 'CHANGE_VECTOR';
1427: h_table_columns(h_num_columns).data_type := 'NUMBER';
1428: h_table_columns(h_num_columns).data_size := NULL;
1429: h_table_columns(h_num_columns).add_to_index := 'N';
1430: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
1431: RAISE e_unexpected_error;
1432: END IF;
1433:
1434: -- Bug#3875046 Need this new temporary table. No index needed

Line 1449: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN

1445: h_table_columns(h_num_columns).column_name := 'ROW_ID_BASE';
1446: h_table_columns(h_num_columns).data_type := 'ROWID';
1447: h_table_columns(h_num_columns).data_size := NULL;
1448: h_table_columns(h_num_columns).add_to_index := 'N';
1449: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
1450: RAISE e_unexpected_error;
1451: END IF;
1452:
1453: RETURN TRUE;

Line 1457: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR'),

1453: RETURN TRUE;
1454:
1455: EXCEPTION
1456: WHEN e_unexpected_error THEN
1457: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR'),
1458: x_source => 'BSC_UPDATE_BASE.Create_Generic_Temp_Tables');
1459: RETURN FALSE;
1460:
1461: WHEN OTHERS THEN

Line 1490: p_periodicity OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,

1486: -- Bug Fix for #3236356
1487:
1488: FUNCTION Get_Base_Higher_Periodicities(
1489: p_table_name VARCHAR2,
1490: p_periodicity OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
1491: p_calendar_id OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
1492: p_column_name OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
1493: p_number_periodicity OUT NOCOPY NUMBER
1494: ) RETURN BOOLEAN IS

Line 1491: p_calendar_id OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,

1487:
1488: FUNCTION Get_Base_Higher_Periodicities(
1489: p_table_name VARCHAR2,
1490: p_periodicity OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
1491: p_calendar_id OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
1492: p_column_name OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
1493: p_number_periodicity OUT NOCOPY NUMBER
1494: ) RETURN BOOLEAN IS
1495:

Line 1492: p_column_name OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,

1488: FUNCTION Get_Base_Higher_Periodicities(
1489: p_table_name VARCHAR2,
1490: p_periodicity OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
1491: p_calendar_id OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
1492: p_column_name OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
1493: p_number_periodicity OUT NOCOPY NUMBER
1494: ) RETURN BOOLEAN IS
1495:
1496: CURSOR c_Calc_Period IS

Line 1535: x_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2,

1531: +============================================================================*/
1532: FUNCTION Update_Base_Table(
1533: x_base_tbl VARCHAR2,
1534: x_in_tbl VARCHAR2,
1535: x_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2,
1536: x_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2,
1537: x_num_key_columns NUMBER,
1538: x_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2,
1539: x_data_formulas BSC_UPDATE_UTIL.t_array_of_varchar2,

Line 1536: x_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2,

1532: FUNCTION Update_Base_Table(
1533: x_base_tbl VARCHAR2,
1534: x_in_tbl VARCHAR2,
1535: x_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2,
1536: x_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2,
1537: x_num_key_columns NUMBER,
1538: x_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2,
1539: x_data_formulas BSC_UPDATE_UTIL.t_array_of_varchar2,
1540: x_data_measure_types BSC_UPDATE_UTIL.t_array_of_number,

Line 1538: x_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2,

1534: x_in_tbl VARCHAR2,
1535: x_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2,
1536: x_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2,
1537: x_num_key_columns NUMBER,
1538: x_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2,
1539: x_data_formulas BSC_UPDATE_UTIL.t_array_of_varchar2,
1540: x_data_measure_types BSC_UPDATE_UTIL.t_array_of_number,
1541: x_num_data_columns NUMBER,
1542: x_base_percode NUMBER,

Line 1539: x_data_formulas BSC_UPDATE_UTIL.t_array_of_varchar2,

1535: x_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2,
1536: x_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2,
1537: x_num_key_columns NUMBER,
1538: x_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2,
1539: x_data_formulas BSC_UPDATE_UTIL.t_array_of_varchar2,
1540: x_data_measure_types BSC_UPDATE_UTIL.t_array_of_number,
1541: x_num_data_columns NUMBER,
1542: x_base_percode NUMBER,
1543: x_in_percode NUMBER,

Line 1540: x_data_measure_types BSC_UPDATE_UTIL.t_array_of_number,

1536: x_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2,
1537: x_num_key_columns NUMBER,
1538: x_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2,
1539: x_data_formulas BSC_UPDATE_UTIL.t_array_of_varchar2,
1540: x_data_measure_types BSC_UPDATE_UTIL.t_array_of_number,
1541: x_num_data_columns NUMBER,
1542: x_base_percode NUMBER,
1543: x_in_percode NUMBER,
1544: x_in_per_fld VARCHAR2,

Line 1581: h_key_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;

1577: h_periodicity_type_base_table NUMBER;
1578:
1579: h_calendar_id NUMBER;
1580:
1581: h_key_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
1582: h_data_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
1583:
1584: -- Posco bind variable fix
1585: l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;

Line 1582: h_data_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;

1578:
1579: h_calendar_id NUMBER;
1580:
1581: h_key_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
1582: h_data_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
1583:
1584: -- Posco bind variable fix
1585: l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
1586: l_num_bind_vars NUMBER;

Line 1585: l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;

1581: h_key_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
1582: h_data_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
1583:
1584: -- Posco bind variable fix
1585: l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
1586: l_num_bind_vars NUMBER;
1587:
1588: l_bind_var_per NUMBER;
1589:

Line 1593: l_periodicity BSC_UPDATE_UTIL.t_array_of_number;

1589:
1590: l_parallel_hint varchar2(20000);
1591:
1592: ------------------venu---------------------
1593: l_periodicity BSC_UPDATE_UTIL.t_array_of_number;
1594: l_calendar_id BSC_UPDATE_UTIL.t_array_of_number;
1595: l_column_name BSC_UPDATE_UTIL.t_array_of_varchar2;
1596: l_number_periodicity number;
1597: -------------------------------------

Line 1594: l_calendar_id BSC_UPDATE_UTIL.t_array_of_number;

1590: l_parallel_hint varchar2(20000);
1591:
1592: ------------------venu---------------------
1593: l_periodicity BSC_UPDATE_UTIL.t_array_of_number;
1594: l_calendar_id BSC_UPDATE_UTIL.t_array_of_number;
1595: l_column_name BSC_UPDATE_UTIL.t_array_of_varchar2;
1596: l_number_periodicity number;
1597: -------------------------------------
1598: l_bf_columns BSC_UPDATE_UTIL.t_array_of_varchar2;

Line 1595: l_column_name BSC_UPDATE_UTIL.t_array_of_varchar2;

1591:
1592: ------------------venu---------------------
1593: l_periodicity BSC_UPDATE_UTIL.t_array_of_number;
1594: l_calendar_id BSC_UPDATE_UTIL.t_array_of_number;
1595: l_column_name BSC_UPDATE_UTIL.t_array_of_varchar2;
1596: l_number_periodicity number;
1597: -------------------------------------
1598: l_bf_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
1599: l_bf_column_formulas BSC_UPDATE_UTIL.t_array_of_varchar2;

Line 1598: l_bf_columns BSC_UPDATE_UTIL.t_array_of_varchar2;

1594: l_calendar_id BSC_UPDATE_UTIL.t_array_of_number;
1595: l_column_name BSC_UPDATE_UTIL.t_array_of_varchar2;
1596: l_number_periodicity number;
1597: -------------------------------------
1598: l_bf_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
1599: l_bf_column_formulas BSC_UPDATE_UTIL.t_array_of_varchar2;
1600: -------------------------------------
1601: l_stmt varchar2(32000);
1602: l_table varchar2(320);

Line 1599: l_bf_column_formulas BSC_UPDATE_UTIL.t_array_of_varchar2;

1595: l_column_name BSC_UPDATE_UTIL.t_array_of_varchar2;
1596: l_number_periodicity number;
1597: -------------------------------------
1598: l_bf_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
1599: l_bf_column_formulas BSC_UPDATE_UTIL.t_array_of_varchar2;
1600: -------------------------------------
1601: l_stmt varchar2(32000);
1602: l_table varchar2(320);
1603: l_op varchar2(32);

Line 1659: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE');

1655: -- Use temporal table BSC_TMP_BASE
1656: -- In this table we will insert all data from the input table but doing user_codes transformation
1657: -- and change of periodicity if it is necessary.
1658:
1659: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE');
1660: commit;
1661:
1662: --Fix bug#3875046 Need to delete bsc_tmp_base_rowid. this is used one time at the end.
1663: -- Better to truncate here that delete later

Line 1664: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_ROWID');

1660: commit;
1661:
1662: --Fix bug#3875046 Need to delete bsc_tmp_base_rowid. this is used one time at the end.
1663: -- Better to truncate here that delete later
1664: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_ROWID');
1665: commit;
1666:
1667: -- BSC-MV Note: This table only used in this architecture
1668: -- AW_INTEGRATION: If the base table is for AW there are not higher periodicities.

Line 1671: --BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

1667: -- BSC-MV Note: This table only used in this architecture
1668: -- AW_INTEGRATION: If the base table is for AW there are not higher periodicities.
1669: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1670: --h_sql := 'DELETE FROM BSC_TMP_BASE_BU';
1671: --BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1672: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_BU');
1673: commit;
1674: END IF;
1675:

Line 1672: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_BU');

1668: -- AW_INTEGRATION: If the base table is for AW there are not higher periodicities.
1669: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
1670: --h_sql := 'DELETE FROM BSC_TMP_BASE_BU';
1671: --BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1672: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_BU');
1673: commit;
1674: END IF;
1675:
1676: -- Fix bug#4480258 Perf Issues: analyze the input table before loading it

Line 1686: h_base_calendar_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_base_percode);

1682: | user codes into codes |
1683: +--------------------------------------------------------------*/
1684:
1685: -- Fix bug#4653405: Need to move the initialization of these variable here
1686: h_base_calendar_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_base_percode);
1687: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_base_percode);
1688: h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(h_calendar_id);
1689: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_base_percode);
1690:

Line 1687: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_base_percode);

1683: +--------------------------------------------------------------*/
1684:
1685: -- Fix bug#4653405: Need to move the initialization of these variable here
1686: h_base_calendar_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_base_percode);
1687: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_base_percode);
1688: h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(h_calendar_id);
1689: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_base_percode);
1690:
1691:

Line 1688: h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(h_calendar_id);

1684:
1685: -- Fix bug#4653405: Need to move the initialization of these variable here
1686: h_base_calendar_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_base_percode);
1687: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_base_percode);
1688: h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(h_calendar_id);
1689: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_base_percode);
1690:
1691:
1692: h_lst_key_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns, x_num_key_columns);

Line 1689: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_base_percode);

1685: -- Fix bug#4653405: Need to move the initialization of these variable here
1686: h_base_calendar_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_base_percode);
1687: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_base_percode);
1688: h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(h_calendar_id);
1689: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_base_percode);
1690:
1691:
1692: h_lst_key_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns, x_num_key_columns);
1693: h_lst_key_columns_temp := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', x_num_key_columns);

Line 1692: h_lst_key_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns, x_num_key_columns);

1688: h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(h_calendar_id);
1689: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_base_percode);
1690:
1691:
1692: h_lst_key_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns, x_num_key_columns);
1693: h_lst_key_columns_temp := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', x_num_key_columns);
1694:
1695: IF h_lst_key_columns IS NOT NULL THEN
1696: h_lst_key_columns := h_lst_key_columns||', ';

Line 1693: h_lst_key_columns_temp := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', x_num_key_columns);

1689: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_base_percode);
1690:
1691:
1692: h_lst_key_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns, x_num_key_columns);
1693: h_lst_key_columns_temp := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', x_num_key_columns);
1694:
1695: IF h_lst_key_columns IS NOT NULL THEN
1696: h_lst_key_columns := h_lst_key_columns||', ';
1697: h_lst_key_columns_temp := h_lst_key_columns_temp||', ';

Line 1700: h_lst_data_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_columns, x_num_data_columns);

1696: h_lst_key_columns := h_lst_key_columns||', ';
1697: h_lst_key_columns_temp := h_lst_key_columns_temp||', ';
1698: END IF;
1699:
1700: h_lst_data_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_columns, x_num_data_columns);
1701: h_lst_data_columns_temp := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('DATA', x_num_data_columns);
1702: h_lst_data_formulas := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_formulas, x_num_data_columns);
1703:
1704: --AW_INTEGRATION: Base table has an additional column called PROJECTION and CHANGE_VECTOR

Line 1701: h_lst_data_columns_temp := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('DATA', x_num_data_columns);

1697: h_lst_key_columns_temp := h_lst_key_columns_temp||', ';
1698: END IF;
1699:
1700: h_lst_data_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_columns, x_num_data_columns);
1701: h_lst_data_columns_temp := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('DATA', x_num_data_columns);
1702: h_lst_data_formulas := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_formulas, x_num_data_columns);
1703:
1704: --AW_INTEGRATION: Base table has an additional column called PROJECTION and CHANGE_VECTOR
1705: IF BSC_APPS.bsc_mv AND x_aw_flag THEN

Line 1702: h_lst_data_formulas := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_formulas, x_num_data_columns);

1698: END IF;
1699:
1700: h_lst_data_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_columns, x_num_data_columns);
1701: h_lst_data_columns_temp := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('DATA', x_num_data_columns);
1702: h_lst_data_formulas := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_formulas, x_num_data_columns);
1703:
1704: --AW_INTEGRATION: Base table has an additional column called PROJECTION and CHANGE_VECTOR
1705: IF BSC_APPS.bsc_mv AND x_aw_flag THEN
1706: h_lst_data_columns := h_lst_data_columns||', PROJECTION, CHANGE_VECTOR';

Line 1838: h_periodicity_type_base_table := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_base_percode);

1834: IF h_lst_where IS NOT NULL THEN
1835: h_lst_where := h_lst_where||' AND ';
1836: END IF;
1837:
1838: h_periodicity_type_base_table := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_base_percode);
1839:
1840: IF h_periodicity_type_base_table = 9 THEN
1841: -- It is a daily periodicity. The TIME_FK column in the input table is of type DATE.
1842: h_lst_where := h_lst_where||

Line 1866: h_periodicity_type_input_table := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_in_percode);

1862: -- Always is possible to pass to periodicity 1 (Annual)
1863: -- From periodicity 12 (Month Day) is possible to pass to any periodicity
1864: -- From periodicity 11 (Month Week) is possible to pass only to 7 (Week52)
1865:
1866: h_periodicity_type_input_table := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_in_percode);
1867: h_periodicity_type_base_table := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_base_percode);
1868:
1869: IF h_yearly_flag = 1 THEN
1870: -- The periodicity of base table is annual

Line 1867: h_periodicity_type_base_table := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_base_percode);

1863: -- From periodicity 12 (Month Day) is possible to pass to any periodicity
1864: -- From periodicity 11 (Month Week) is possible to pass only to 7 (Week52)
1865:
1866: h_periodicity_type_input_table := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_in_percode);
1867: h_periodicity_type_base_table := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_base_percode);
1868:
1869: IF h_yearly_flag = 1 THEN
1870: -- The periodicity of base table is annual
1871: h_lst_select := h_lst_select||x_in_tbl||'.YEAR, '||x_in_tbl||'.TYPE, 0';

Line 1893: h_edw_flag := BSC_UPDATE_UTIL.Get_Periodicity_EDW_Flag(x_base_percode);

1889: 'BSC_DB_WEEK_MAPS.CALENDAR_ID = :2';
1890: l_bind_var_per := h_calendar_id;
1891: ELSE
1892: -- Other periodicities changes
1893: h_edw_flag := BSC_UPDATE_UTIL.Get_Periodicity_EDW_Flag(x_base_percode);
1894:
1895: IF h_edw_flag = 0 THEN
1896: -- BSC Periodicity
1897: -- Use bsc_db_calendar to make the transformation

Line 1901: --h_base_calendar_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_base_percode);

1897: -- Use bsc_db_calendar to make the transformation
1898: h_lst_from := h_lst_from||', BSC_DB_CALENDAR';
1899: l_parallel_hint:=l_parallel_hint||' parallel (BSC_DB_CALENDAR)';
1900:
1901: --h_base_calendar_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_base_percode);
1902: IF h_periodicity_type_input_table = 12 THEN
1903: -- The input table is Month-Day
1904: h_lst_select := h_lst_select||x_in_tbl||'.YEAR, '||
1905: x_in_tbl||'.TYPE, BSC_DB_CALENDAR.'||h_base_calendar_col_name;

Line 1917: h_input_calendar_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_in_percode);

1913: x_in_tbl||'.'||x_in_subper_fld||' = BSC_DB_CALENDAR.DAY30 AND '||
1914: 'BSC_DB_CALENDAR.CALENDAR_ID = :2';
1915: l_bind_var_per := h_calendar_id;
1916: ELSE
1917: h_input_calendar_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_in_percode);
1918: h_lst_select := h_lst_select||x_in_tbl||'.YEAR, '||
1919: x_in_tbl||'.TYPE, BSC_DB_CALENDAR.'||h_base_calendar_col_name;
1920:
1921: IF h_lst_where IS NOT NULL THEN

Line 1954: if BSC_UPDATE_UTIL.is_parallel then

1950: --BSC-MV Note: Add column periodicity_id
1951: --AW_INTEGRATION: If the base table is for AW, it does not have periodicity_id
1952: -- Insert records
1953: h_sql := 'INSERT /*+ append';
1954: if BSC_UPDATE_UTIL.is_parallel then
1955: h_sql:=h_sql||' parallel (bsc_tmp_base)';
1956: end if;
1957: h_sql := h_sql||' */';
1958: h_sql :=h_sql||'INTO BSC_TMP_BASE ('||h_lst_key_columns_temp||'YEAR, TYPE, PERIOD, ';

Line 1964: if BSC_UPDATE_UTIL.is_parallel then

1960: h_sql := h_sql||'PERIODICITY_ID, ';
1961: END IF;
1962: h_sql := h_sql||h_lst_data_columns_temp||')'||
1963: ' SELECT ';
1964: if BSC_UPDATE_UTIL.is_parallel then
1965: h_sql:=h_sql||'/*+ '||l_parallel_hint||' */ ';
1966: end if;
1967: h_sql:=h_sql||h_lst_select||', ';
1968: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN

Line 1990: --BSC_UPDATE_UTIL.Execute_Immediate(l_stmt,l_bind_vars_values,1);

1986: --Bug#3875046: We are not going to remove higher periodcities from the base table
1987: --l_stmt:='delete '||x_base_tbl||' where periodicity_id <> :1';
1988: l_bind_vars_values.delete;
1989: l_bind_vars_values(1):=x_base_percode;
1990: --BSC_UPDATE_UTIL.Execute_Immediate(l_stmt,l_bind_vars_values,1);
1991: --commit;
1992: ---------------move data from base into tmp--------
1993: h_sql := 'INSERT /*+ append';
1994: if BSC_UPDATE_UTIL.is_parallel then

Line 1994: if BSC_UPDATE_UTIL.is_parallel then

1990: --BSC_UPDATE_UTIL.Execute_Immediate(l_stmt,l_bind_vars_values,1);
1991: --commit;
1992: ---------------move data from base into tmp--------
1993: h_sql := 'INSERT /*+ append';
1994: if BSC_UPDATE_UTIL.is_parallel then
1995: h_sql:=h_sql||' parallel (bsc_tmp_base)';
1996: end if;
1997: h_sql := h_sql||' */';
1998: h_sql :=h_sql||'INTO BSC_TMP_BASE ('||h_lst_key_columns_temp||'YEAR, TYPE, PERIOD,PERIODICITY_ID, ';

Line 2000: if BSC_UPDATE_UTIL.is_parallel then

1996: end if;
1997: h_sql := h_sql||' */';
1998: h_sql :=h_sql||'INTO BSC_TMP_BASE ('||h_lst_key_columns_temp||'YEAR, TYPE, PERIOD,PERIODICITY_ID, ';
1999: h_sql := h_sql||h_lst_data_columns_temp||') SELECT ';
2000: if BSC_UPDATE_UTIL.is_parallel then
2001: h_sql := h_sql||' /*+ parallel('||x_base_tbl||')*/ ';
2002: end if;
2003: --Bug#3875046 Add condition on periodicity_id.(Changes next 3 lines)
2004: h_sql := h_sql||h_lst_key_columns||'YEAR, TYPE, PERIOD, PERIODICITY_ID, ';

Line 2008: h_row_count := BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);

2004: h_sql := h_sql||h_lst_key_columns||'YEAR, TYPE, PERIOD, PERIODICITY_ID, ';
2005: h_sql := h_sql||h_lst_data_columns||' from '||x_base_tbl;
2006: h_sql := h_sql||' where PERIODICITY_ID = :1';
2007: --Fix bug#3875046: Need to maintain track of number of rpws in tmp table to be used later
2008: h_row_count := BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
2009: h_num_rows_tmp := h_num_rows_tmp + h_row_count;
2010: ---------------------------------------------------
2011: elsif ((NOT BSC_APPS.bsc_mv) OR x_aw_flag) AND x_correction_flag then
2012: /*

Line 2019: if BSC_UPDATE_UTIL.is_parallel then

2015: AW_INTEGRATION: If the base table is for AW then there are not higher periodicities.
2016: It is the same structure as summary tables architecture
2017: */
2018: h_sql := 'INSERT /*+ append';
2019: if BSC_UPDATE_UTIL.is_parallel then
2020: h_sql:=h_sql||' parallel (bsc_tmp_base)';
2021: end if;
2022: h_sql := h_sql||' */';
2023: h_sql :=h_sql||'INTO BSC_TMP_BASE ('||h_lst_key_columns_temp||'YEAR, TYPE, PERIOD, ';

Line 2025: if BSC_UPDATE_UTIL.is_parallel then

2021: end if;
2022: h_sql := h_sql||' */';
2023: h_sql :=h_sql||'INTO BSC_TMP_BASE ('||h_lst_key_columns_temp||'YEAR, TYPE, PERIOD, ';
2024: h_sql := h_sql||h_lst_data_columns_temp||') SELECT ';
2025: if BSC_UPDATE_UTIL.is_parallel then
2026: h_sql := h_sql||' /*+ parallel('||x_base_tbl||')*/ ';
2027: end if;
2028: h_sql := h_sql||h_lst_key_columns||'YEAR, TYPE, PERIOD, ';
2029: h_sql := h_sql||h_lst_data_columns||' from '||x_base_tbl;

Line 2031: h_row_count := BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

2027: end if;
2028: h_sql := h_sql||h_lst_key_columns||'YEAR, TYPE, PERIOD, ';
2029: h_sql := h_sql||h_lst_data_columns||' from '||x_base_tbl;
2030: --Fix bug#3875046: Need to maintain track of number of rpws in tmp table to be used later
2031: h_row_count := BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2032: h_num_rows_tmp := h_num_rows_tmp + h_row_count;
2033: ---------------------------------------------------
2034: else
2035: l_bind_vars_values.delete;

Line 2048: h_row_count := BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,l_num_bind_vars);

2044: l_bind_vars_values(l_num_bind_vars) := l_bind_var_per;
2045: END IF;
2046: IF l_num_bind_vars > 0 THEN
2047: --Fix bug#3875046: Need to maintain track of number of rpws in tmp table to be used later
2048: h_row_count := BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,l_num_bind_vars);
2049: h_num_rows_tmp := h_num_rows_tmp + h_row_count;
2050: ELSE
2051: --Fix bug#3875046: Need to maintain track of number of rpws in tmp table to be used later
2052: h_row_count := BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

Line 2052: h_row_count := BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

2048: h_row_count := BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,l_num_bind_vars);
2049: h_num_rows_tmp := h_num_rows_tmp + h_row_count;
2050: ELSE
2051: --Fix bug#3875046: Need to maintain track of number of rpws in tmp table to be used later
2052: h_row_count := BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2053: h_num_rows_tmp := h_num_rows_tmp + h_row_count;
2054: END IF;
2055: commit;
2056: end if;

Line 2063: h_lst_join := BSC_UPDATE_UTIL.Make_Lst_Cond_Join(x_base_tbl,

2059: -- Update existing records in base table
2060: --AW_INTEGRATION: If the base table is for AW, it does not have periodicity_id
2061: h_lst_join := NULL;
2062: IF x_num_key_columns > 0 THEN
2063: h_lst_join := BSC_UPDATE_UTIL.Make_Lst_Cond_Join(x_base_tbl,
2064: x_key_columns,
2065: 'BSC_TMP_BASE',
2066: h_key_columns_temp,
2067: x_num_key_columns,

Line 2102: if BSC_UPDATE_UTIL.is_parallel then

2098: --if x_correction_flag then we dont need to process lowest periodicity data into the base table
2099: if x_correction_flag=false then
2100: if l_number_periodicity>0 then
2101: l_stmt:='insert /*+ append';
2102: if BSC_UPDATE_UTIL.is_parallel then
2103: l_stmt:=l_stmt||' parallel (bsc_tmp_base_bu)';
2104: end if;
2105: l_stmt := l_stmt||' */';
2106: l_stmt :=l_stmt||'INTO BSC_TMP_BASE_BU ('||h_lst_key_columns_temp||'PERIODICITY_ID,YEAR, TYPE, PERIOD, '||

Line 2109: if BSC_UPDATE_UTIL.is_parallel then

2105: l_stmt := l_stmt||' */';
2106: l_stmt :=l_stmt||'INTO BSC_TMP_BASE_BU ('||h_lst_key_columns_temp||'PERIODICITY_ID,YEAR, TYPE, PERIOD, '||
2107: h_lst_data_columns_temp||')'||
2108: ' SELECT ';
2109: if BSC_UPDATE_UTIL.is_parallel then
2110: l_stmt:=l_stmt||'/*+ parallel (bsc_tmp_base) parallel ('||x_base_tbl||') */ ';
2111: end if;
2112: l_stmt:=l_stmt||'/*+ ordered use_nl('||x_base_tbl||') */ ';
2113: for i in 1..x_num_key_columns loop

Line 2130: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);

2126: 'bsc_tmp_base.YEAR='||x_base_tbl||'.YEAR and '||
2127: 'bsc_tmp_base.TYPE='||x_base_tbl||'.TYPE and '||
2128: 'bsc_tmp_base.PERIOD='||x_base_tbl||'.PERIOD';
2129: commit;
2130: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);
2131: commit;
2132: end if;
2133: end if;
2134: ------------------------------------------------------------------------

Line 2140: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_UPDATE');

2136:
2137: -- BSC-MV Note: add periodicity_id in condition
2138: --Fix bug#3875046: Replace this update stmt with the strategy of inserting into
2139: -- bsc_tmp_base_update with row id and then update the base table
2140: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_UPDATE');
2141: commit;
2142: l_bind_vars_values.delete;
2143: h_sql := 'INSERT /*+ append ';
2144: IF BSC_UPDATE_UTIL.is_parallel THEN

Line 2144: IF BSC_UPDATE_UTIL.is_parallel THEN

2140: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_UPDATE');
2141: commit;
2142: l_bind_vars_values.delete;
2143: h_sql := 'INSERT /*+ append ';
2144: IF BSC_UPDATE_UTIL.is_parallel THEN
2145: h_sql := h_sql||'parallel (bsc_tmp_base_update) ';
2146: END IF;
2147: h_sql := h_sql||' */'||
2148: ' INTO bsc_tmp_base_update (row_id, '||h_lst_data_columns_temp||')'||

Line 2155: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

2151: ' WHERE '||h_lst_join;
2152: --if x_correction_flag then we dont need to do updates and inserts into the base
2153: --table for lowest periodicity data
2154: if not x_correction_flag then
2155: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2156: commit;
2157: end if;
2158:
2159: h_sql := 'UPDATE /*+ordered use_nl(B)*/ '||x_base_tbl||' B'||

Line 2168: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

2164: ' WHERE B.rowid IN (SELECT row_id FROM bsc_tmp_base_update)';
2165: --if x_correction_flag then we dont need to do updates and inserts into the base
2166: --table for lowest periodicity data
2167: if not x_correction_flag then
2168: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2169: commit;
2170: end if;
2171:
2172: -- Insert new rows

Line 2178: h_lst_select := BSC_UPDATE_UTIL.Make_Lst_Table_Column('BSC_TMP_BASE',

2174: h_lst_join := NULL;
2175: h_lst_cond_null := NULL;
2176:
2177: IF x_num_key_columns > 0 THEN
2178: h_lst_select := BSC_UPDATE_UTIL.Make_Lst_Table_Column('BSC_TMP_BASE',
2179: h_key_columns_temp,
2180: x_num_key_columns);
2181: h_lst_select := h_lst_select||', ';
2182:

Line 2183: h_lst_join := BSC_UPDATE_UTIL.Make_Lst_Cond_Left_Join('BSC_TMP_BASE',

2179: h_key_columns_temp,
2180: x_num_key_columns);
2181: h_lst_select := h_lst_select||', ';
2182:
2183: h_lst_join := BSC_UPDATE_UTIL.Make_Lst_Cond_Left_Join('BSC_TMP_BASE',
2184: h_key_columns_temp,
2185: x_base_tbl,
2186: x_key_columns,
2187: x_num_key_columns,

Line 2191: h_lst_cond_null := BSC_UPDATE_UTIL.Make_Lst_Cond_Null(x_base_tbl,

2187: x_num_key_columns,
2188: 'AND');
2189: h_lst_join := h_lst_join||' AND ';
2190:
2191: h_lst_cond_null := BSC_UPDATE_UTIL.Make_Lst_Cond_Null(x_base_tbl,
2192: x_key_columns,
2193: x_num_key_columns,
2194: 'OR');
2195: h_lst_cond_null := h_lst_cond_null||' OR ';

Line 2252: if BSC_UPDATE_UTIL.is_parallel then

2248: end if;
2249: end if;
2250: if ll_use_append then
2251: h_sql := 'INSERT /*+append';
2252: if BSC_UPDATE_UTIL.is_parallel then
2253: h_sql:=h_sql||' parallel ('||x_base_tbl||')';
2254: end if;
2255: else
2256: h_sql := 'INSERT /*+';

Line 2266: if BSC_UPDATE_UTIL.is_parallel then

2262: h_sql := h_sql||'PERIODICITY_ID, ';
2263: END IF;
2264: h_sql := h_sql||'YEAR, TYPE, PERIOD, '||h_lst_data_columns||')'||
2265: ' SELECT ';
2266: if BSC_UPDATE_UTIL.is_parallel then
2267: h_sql:=h_sql||'/*+ parallel ('||x_base_tbl||') parallel (bsc_tmp_base) */ ';
2268: end if;
2269: h_sql:=h_sql||h_lst_select||
2270: ' FROM '||x_base_tbl||', BSC_TMP_BASE'||

Line 2276: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

2272:
2273: --if x_correction_flag then we dont need to do updates and inserts into the base
2274: if not x_correction_flag then
2275: commit;
2276: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2277: end if;
2278: commit;
2279:
2280: -- Fix bug#3875046: Analize base table

Line 2285: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_UPDATE');

2281: BSC_BIA_WRAPPER.Analyze_Table(x_base_tbl);
2282: commit;
2283:
2284: -- Fix bug#4463131: truncate temp tables after use
2285: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_UPDATE');
2286: -- note that we cannot truncate bsc_tmp_base since it is used in projection
2287:
2288: ---------------------venu------------------------------------------
2289: --BSC-MV Note: This code only applies for new architecture

Line 2319: h_lst_select := BSC_UPDATE_UTIL.Make_Lst_Table_Column(x_base_tbl, x_key_columns, x_num_key_columns);

2315: h_lst_select := NULL;
2316: h_lst_join := NULL;
2317: h_lst_cond_null := NULL;
2318: IF x_num_key_columns > 0 THEN
2319: h_lst_select := BSC_UPDATE_UTIL.Make_Lst_Table_Column(x_base_tbl, x_key_columns, x_num_key_columns);
2320: h_lst_select := h_lst_select||', ';
2321: h_lst_join := BSC_UPDATE_UTIL.Make_Lst_Cond_Left_Join(x_base_tbl,
2322: x_key_columns,
2323: l_table,

Line 2321: h_lst_join := BSC_UPDATE_UTIL.Make_Lst_Cond_Left_Join(x_base_tbl,

2317: h_lst_cond_null := NULL;
2318: IF x_num_key_columns > 0 THEN
2319: h_lst_select := BSC_UPDATE_UTIL.Make_Lst_Table_Column(x_base_tbl, x_key_columns, x_num_key_columns);
2320: h_lst_select := h_lst_select||', ';
2321: h_lst_join := BSC_UPDATE_UTIL.Make_Lst_Cond_Left_Join(x_base_tbl,
2322: x_key_columns,
2323: l_table,
2324: h_key_columns_temp,
2325: x_num_key_columns,

Line 2330: --h_lst_cond_null := BSC_UPDATE_UTIL.Make_Lst_Cond_Null(l_table,

2326: 'AND');
2327: h_lst_join := h_lst_join||' AND ';
2328: -- Fix bug#4480258: perf issue. no need null condition on all the keys. We just
2329: --need on YEAR, if it is null then the other keys are null too.
2330: --h_lst_cond_null := BSC_UPDATE_UTIL.Make_Lst_Cond_Null(l_table,
2331: -- h_key_columns_temp,
2332: -- x_num_key_columns,
2333: -- 'OR');
2334: --h_lst_cond_null := h_lst_cond_null||' OR ';

Line 2357: IF BSC_UPDATE_UTIL.is_parallel THEN

2353: -- l_table||'.PERIOD IS NULL';
2354: h_lst_cond_null := l_table||'.YEAR IS NULL';
2355:
2356: l_stmt := 'INSERT /*+append';
2357: IF BSC_UPDATE_UTIL.is_parallel THEN
2358: l_stmt := l_stmt||' parallel ('||l_table||')';
2359: END IF;
2360: l_stmt := l_stmt||' */ INTO '||l_table||' ('||h_lst_key_columns_temp||'PERIODICITY_ID, YEAR, TYPE, PERIOD, '||
2361: h_lst_data_columns_temp||') SELECT ';

Line 2362: IF BSC_UPDATE_UTIL.is_parallel THEN

2358: l_stmt := l_stmt||' parallel ('||l_table||')';
2359: END IF;
2360: l_stmt := l_stmt||' */ INTO '||l_table||' ('||h_lst_key_columns_temp||'PERIODICITY_ID, YEAR, TYPE, PERIOD, '||
2361: h_lst_data_columns_temp||') SELECT ';
2362: IF BSC_UPDATE_UTIL.is_parallel THEN
2363: l_stmt := l_stmt||'/*+ parallel ('||x_base_tbl||') parallel ('||l_table||') */';
2364: END IF;
2365: l_stmt := l_stmt||h_lst_select||
2366: ' FROM '||x_base_tbl||', '||l_table||

Line 2376: h_row_count := BSC_UPDATE_UTIL.Execute_Immediate(l_stmt,l_bind_vars_values,4);

2372: l_bind_vars_values(2) := x_current_fy;
2373: l_bind_vars_values(3) := x_prev_current_period;
2374: l_bind_vars_values(4) := x_current_per_base_table;
2375: -- need to maintain track of the number of rows in bsc_tmp_base
2376: h_row_count := BSC_UPDATE_UTIL.Execute_Immediate(l_stmt,l_bind_vars_values,4);
2377: if j=1 then
2378: h_num_rows_tmp := h_num_rows_tmp + h_row_count;
2379: end if;
2380: commit;

Line 2413: if BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(l_periodicity(i))<>1 then

2409: end if;
2410: l_calendar_sql:=substr(l_calendar_sql,1,length(l_calendar_sql)-1);
2411: l_calendar_sql:=l_calendar_sql||' from bsc_db_calendar';
2412: --Fix bug#4235448: get the current period and previous period in this periodicity
2413: if BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(l_periodicity(i))<>1 then
2414: l_stmt := 'select max('||l_column_name(i)||') from bsc_db_calendar'||
2415: ' where '||h_base_calendar_col_name||' = :1 and year = :2 and calendar_id = :3';
2416: open h_cursor for l_stmt using x_current_per_base_table, x_current_fy, l_calendar_id(i);
2417: fetch h_cursor into l_current_period;

Line 2432: if BSC_UPDATE_UTIL.is_parallel then

2428: else
2429: l_table:='bsc_tmp_base_bu';
2430: end if;
2431: l_stmt:='insert /*+ append';
2432: if BSC_UPDATE_UTIL.is_parallel then
2433: l_stmt:=l_stmt||' parallel ('||l_table||')';
2434: end if;
2435: l_stmt := l_stmt||' */';
2436: l_stmt:=l_stmt||'INTO '||l_table||'('||h_lst_key_columns_temp||'PERIODICITY_ID,YEAR, TYPE, PERIOD,'||

Line 2439: if BSC_UPDATE_UTIL.is_parallel then

2435: l_stmt := l_stmt||' */';
2436: l_stmt:=l_stmt||'INTO '||l_table||'('||h_lst_key_columns_temp||'PERIODICITY_ID,YEAR, TYPE, PERIOD,'||
2437: h_lst_data_columns_temp||')'||
2438: ' SELECT ';
2439: if BSC_UPDATE_UTIL.is_parallel then
2440: l_stmt:=l_stmt||'/*+ parallel ('||l_table||') parallel (bsc_db_calendar)*/ ';
2441: end if;
2442: l_stmt:=l_stmt||h_lst_key_columns_temp||':1,'||l_table||'.YEAR,'||l_table||'.TYPE,';
2443: --bug 3348797

Line 2445: if BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(l_periodicity(i))=1 then

2441: end if;
2442: l_stmt:=l_stmt||h_lst_key_columns_temp||':1,'||l_table||'.YEAR,'||l_table||'.TYPE,';
2443: --bug 3348797
2444: --if l_periodicity(i)=1 then --for year we need to put 0 into PERIOD column
2445: if BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(l_periodicity(i))=1 then
2446: l_stmt:=l_stmt||'0,';
2447: else
2448: l_stmt:=l_stmt||'bsc_db_calendar.'||l_column_name(i)||',';
2449: end if;

Line 2458: if BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(l_periodicity(i))<>1 then

2454: -- Fix bug#4235448: If there is no chnage of periodicity then we use -999999999999 for now.
2455: -- If there is chnage of periodicity we do this: For periods between l_prev_current_period and
2456: -- l_current_period we insert null for other periods we insert -999999999999
2457: if x_current_per_base_table > x_prev_current_period then
2458: if BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(l_periodicity(i))<>1 then
2459: l_stmt:=l_stmt||'case when '||l_table||'.YEAR = '||x_current_fy||' and'||
2460: ' bsc_db_calendar.'||l_column_name(i)||' >= '||l_prev_current_period||' and'||
2461: ' bsc_db_calendar.'||l_column_name(i)||' <= '||l_current_period||
2462: ' then null else -999999999999 end case,';

Line 2485: h_row_count := BSC_UPDATE_UTIL.Execute_Immediate(l_stmt,l_bind_vars_values,3);

2481: l_bind_vars_values(2):=l_calendar_id(i);
2482: l_bind_vars_values(3):=x_base_percode;
2483: commit;
2484: --Fix bug#3875046 need to maintain track of the number of rows in bsc_tmp_base
2485: h_row_count := BSC_UPDATE_UTIL.Execute_Immediate(l_stmt,l_bind_vars_values,3);
2486: if j=1 then
2487: h_num_rows_tmp := h_num_rows_tmp + h_row_count;
2488: end if;
2489: commit;

Line 2507: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_UPDATE');

2503: end loop;
2504: if l_balance_flag then
2505: --Fix bug#3875046 We need to truncate bsc_tmp_base_update because is going to be used
2506: -- to replace the update stmt at the end of the loop
2507: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_UPDATE');
2508: commit;
2509: for i in 1..l_number_periodicity loop
2510: if l_periodicity(i)<>x_base_percode then
2511: ------------first set bsc_tmp_per_change_bal------------

Line 2513: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);

2509: for i in 1..l_number_periodicity loop
2510: if l_periodicity(i)<>x_base_percode then
2511: ------------first set bsc_tmp_per_change_bal------------
2512: l_stmt:='DELETE FROM bsc_tmp_per_change_bal';
2513: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);
2514: l_yearly_flag:=BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(l_periodicity(i));
2515: IF l_yearly_flag <> 1 THEN
2516: --h_period_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity);
2517: --h_origin_period_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_origin_periodicity);

Line 2514: l_yearly_flag:=BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(l_periodicity(i));

2510: if l_periodicity(i)<>x_base_percode then
2511: ------------first set bsc_tmp_per_change_bal------------
2512: l_stmt:='DELETE FROM bsc_tmp_per_change_bal';
2513: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);
2514: l_yearly_flag:=BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(l_periodicity(i));
2515: IF l_yearly_flag <> 1 THEN
2516: --h_period_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity);
2517: --h_origin_period_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_origin_periodicity);
2518: l_stmt:='INSERT INTO bsc_tmp_per_change_bal (year, src_per, trg_per)'||

Line 2516: --h_period_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity);

2512: l_stmt:='DELETE FROM bsc_tmp_per_change_bal';
2513: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);
2514: l_yearly_flag:=BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(l_periodicity(i));
2515: IF l_yearly_flag <> 1 THEN
2516: --h_period_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity);
2517: --h_origin_period_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_origin_periodicity);
2518: l_stmt:='INSERT INTO bsc_tmp_per_change_bal (year, src_per, trg_per)'||
2519: ' SELECT year, MAX('||h_base_calendar_col_name||') AS src_per, '||
2520: l_column_name(i)||' AS trg_per'||

Line 2517: --h_origin_period_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_origin_periodicity);

2513: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);
2514: l_yearly_flag:=BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(l_periodicity(i));
2515: IF l_yearly_flag <> 1 THEN
2516: --h_period_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity);
2517: --h_origin_period_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_origin_periodicity);
2518: l_stmt:='INSERT INTO bsc_tmp_per_change_bal (year, src_per, trg_per)'||
2519: ' SELECT year, MAX('||h_base_calendar_col_name||') AS src_per, '||
2520: l_column_name(i)||' AS trg_per'||
2521: ' FROM bsc_db_calendar where calendar_id=:1';

Line 2525: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt, l_bind_vars_values,1);

2521: ' FROM bsc_db_calendar where calendar_id=:1';
2522: l_stmt:=l_stmt||' GROUP BY year,'||l_column_name(i);
2523: l_bind_vars_values.delete;
2524: l_bind_vars_values(1):=l_calendar_id(i);
2525: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt, l_bind_vars_values,1);
2526: l_bind_vars_values.delete;
2527: l_bind_vars_values(1) := (x_current_per_base_table);
2528: l_bind_vars_values(2) := (x_current_fy) ;
2529: l_bind_vars_values(3) := (x_current_per_base_table);

Line 2542: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt, l_bind_vars_values,5);

2538: ' AND year = :4';
2539: l_stmt:=l_stmt||' AND calendar_id=:5';
2540: l_bind_vars_values(5) := l_calendar_id(i);
2541: l_stmt:=l_stmt||' GROUP BY '||l_column_name(i)||')';
2542: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt, l_bind_vars_values,5);
2543: ELSE
2544: -- Anual periodicity
2545: l_stmt:='INSERT INTO bsc_tmp_per_change_bal (year, src_per, trg_per)'||
2546: ' SELECT year, MAX('||h_base_calendar_col_name||') AS src_per, 0 AS trg_per'||

Line 2552: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt,l_bind_vars_values,1);

2548: l_stmt:=l_stmt||' WHERE calendar_id=:1 ';
2549: l_stmt:=l_stmt||' GROUP BY year';
2550: l_bind_vars_values.delete;
2551: l_bind_vars_values(1):=l_calendar_id(i);
2552: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt,l_bind_vars_values,1);
2553: l_bind_vars_values.delete;
2554: l_bind_vars_values(1) := (x_current_per_base_table);
2555: l_bind_vars_values(2) := (x_current_fy);
2556: l_stmt:='UPDATE bsc_tmp_per_change_bal'||

Line 2559: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt,l_bind_vars_values,2);

2555: l_bind_vars_values(2) := (x_current_fy);
2556: l_stmt:='UPDATE bsc_tmp_per_change_bal'||
2557: ' SET src_per = :1'||
2558: ' WHERE year = :2';
2559: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt,l_bind_vars_values,2);
2560: END IF;
2561: --this logic is happening for every higher level periodicity
2562: --delete the table first
2563: --l_stmt := 'DELETE FROM BSC_TMP_BASE_BAL';

Line 2564: --BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);

2560: END IF;
2561: --this logic is happening for every higher level periodicity
2562: --delete the table first
2563: --l_stmt := 'DELETE FROM BSC_TMP_BASE_BAL';
2564: --BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);
2565: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_BAL');
2566: commit;
2567: l_stmt:='insert /*+ append';
2568: if BSC_UPDATE_UTIL.is_parallel then

Line 2565: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_BAL');

2561: --this logic is happening for every higher level periodicity
2562: --delete the table first
2563: --l_stmt := 'DELETE FROM BSC_TMP_BASE_BAL';
2564: --BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);
2565: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_BAL');
2566: commit;
2567: l_stmt:='insert /*+ append';
2568: if BSC_UPDATE_UTIL.is_parallel then
2569: l_stmt:=l_stmt||' parallel (BSC_TMP_BASE_BAL)';

Line 2568: if BSC_UPDATE_UTIL.is_parallel then

2564: --BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);
2565: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_BAL');
2566: commit;
2567: l_stmt:='insert /*+ append';
2568: if BSC_UPDATE_UTIL.is_parallel then
2569: l_stmt:=l_stmt||' parallel (BSC_TMP_BASE_BAL)';
2570: end if;
2571: l_stmt := l_stmt||' */ ';
2572: l_stmt := l_stmt||' into BSC_TMP_BASE_BAL('||h_lst_key_columns_temp||'PERIODICITY_ID, YEAR, TYPE, PERIOD, '||

Line 2576: if BSC_UPDATE_UTIL.is_parallel then

2572: l_stmt := l_stmt||' into BSC_TMP_BASE_BAL('||h_lst_key_columns_temp||'PERIODICITY_ID, YEAR, TYPE, PERIOD, '||
2573: h_lst_data_columns_temp||') SELECT ';
2574: -- Fix bug#3875046: use hash hint
2575: l_stmt := l_stmt||'/*+use_hash(bsc_tmp_base) use_hash(bsc_tmp_per_change_bal)*/ ';
2576: if BSC_UPDATE_UTIL.is_parallel then
2577: l_stmt:=l_stmt||'/*+ parallel (bsc_tmp_base) parallel (bsc_tmp_per_change_bal) */';
2578: end if;
2579: l_stmt:=l_stmt||h_lst_key_columns_temp||':1,bsc_tmp_base.YEAR,'||
2580: 'bsc_tmp_base.TYPE,bsc_tmp_per_change_bal.trg_per, ';

Line 2600: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt,l_bind_vars_values,2);

2596: l_bind_vars_values.delete;
2597: l_bind_vars_values(1):=l_periodicity(i);
2598: l_bind_vars_values(2):=x_base_percode;
2599: commit;
2600: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt,l_bind_vars_values,2);
2601: commit;
2602: --now we have to update bsc_tmp_base
2603: --Fix bug#3875046: Performance fix. We are going to replace the update statement.
2604: --We are going to insert into bsc_tmp_base_update with row_id. Then out of the loop

Line 2636: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt,l_bind_vars_values,1);

2632: l_stmt:=l_stmt||'bsc_tmp_base.periodicity_id=:1';
2633: l_bind_vars_values.delete;
2634: l_bind_vars_values(1):=l_periodicity(i);
2635: commit;
2636: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt,l_bind_vars_values,1);
2637: commit;
2638: --------------------
2639: ---------------------------------------------------------
2640: end if;

Line 2659: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);

2655: l_stmt:=substr(l_stmt,1,length(l_stmt)-1)||
2656: ' from bsc_tmp_base_update'||
2657: ' where bsc_tmp_base_update.row_id=bsc_tmp_base.rowid)'||
2658: ' where rowid in (select row_id from bsc_tmp_base_update)';
2659: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);
2660: commit;
2661: end if;--if l_balance_flag then
2662: ---------------------------------------------------------------
2663: --we need l_periodicity_stmt because when we update the base fact with higher level data,

Line 2681: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);

2677: -- instead of updating direclty to the base table
2678: -- Note: we cannot use append or parallel hint since we cannot commit until the end.
2679:
2680: l_stmt := 'DELETE FROM bsc_tmp_base_update';
2681: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);
2682:
2683: l_stmt := 'INSERT INTO bsc_tmp_base_update (row_id,';
2684: for j in 1..x_num_data_columns loop
2685: l_stmt := l_stmt||h_data_columns_temp(j)||',';

Line 2704: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);

2700: x_base_tbl||'.year='||l_table||'.year and '||
2701: x_base_tbl||'.type='||l_table||'.type and '||
2702: x_base_tbl||'.period='||l_table||'.period and '||
2703: l_table||'.periodicity_id in ('||l_periodicity_stmt||')';
2704: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);
2705:
2706: l_stmt := 'UPDATE /*+ORDERED USE_NL('||x_base_tbl||')*/ '||x_base_tbl||
2707: ' SET(';
2708: for j in 1..x_num_data_columns loop

Line 2719: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);

2715: l_stmt := substr(l_stmt,1,length(l_stmt)-1)||
2716: ' FROM bsc_tmp_base_update'||
2717: ' WHERE bsc_tmp_base_update.row_id='||x_base_tbl||'.rowid)'||
2718: ' WHERE rowid IN (SELECT row_id FROM bsc_tmp_base_update)';
2719: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);
2720: else
2721: --add bsc_tmp_base data to the base table and then subtract the data from bsc_tmp_base_bu
2722: --l_periodicity_stmt we need this because here, we only update the higher periodicity
2723: for i in 1..2 loop

Line 2750: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);

2746: -- instead of updating direclty to the base table
2747: -- Note: we cannot use append or parallel hint since we cannot commit until the end.
2748:
2749: l_stmt := 'DELETE FROM bsc_tmp_base_update';
2750: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);
2751:
2752: --i=1 is with bsc_tmp_base
2753: --i=2 is with bsc_tmp_base_bu
2754:

Line 2785: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);

2781: x_base_tbl||'.year='||l_table||'.year and '||
2782: x_base_tbl||'.type='||l_table||'.type and '||
2783: x_base_tbl||'.period='||l_table||'.period and '||
2784: l_table||'.periodicity_id in ('||l_periodicity_stmt||')';
2785: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);
2786:
2787: l_stmt := 'UPDATE /*+ORDERED USE_NL('||x_base_tbl||')*/ '||x_base_tbl||
2788: ' SET(';
2789: for j in 1..x_num_data_columns loop

Line 2822: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);

2818: l_stmt := substr(l_stmt,1,length(l_stmt)-1)||
2819: ' FROM bsc_tmp_base_update'||
2820: ' WHERE bsc_tmp_base_update.row_id='||x_base_tbl||'.rowid)'||
2821: ' WHERE rowid IN (SELECT row_id FROM bsc_tmp_base_update)';
2822: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);
2823: end loop;--for i in 1..2
2824: end if; --if x_correction_flag
2825: --in the base table
2826: --NO COMMIT HERE. COMMIT ONLY AFTER INSERT ALSO COMPLETE!!!

Line 2859: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);

2855: l_stmt:=l_stmt||'bsc_tmp_base.periodicity_id='||x_base_tbl||'.periodicity_id (+) and '||
2856: 'bsc_tmp_base.year='||x_base_tbl||'.year (+) and '||
2857: 'bsc_tmp_base.type='||x_base_tbl||'.type (+) and '||
2858: 'bsc_tmp_base.period='||x_base_tbl||'.period (+)';
2859: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);
2860: --d. insert into the base table
2861: --Fix bug#4097873: perf fix: use_hash instead of ordered hint
2862: l_stmt := 'insert into '||x_base_tbl||' ('||
2863: h_lst_key_columns||'PERIODICITY_ID,YEAR,TYPE,PERIOD,'||h_lst_data_columns||')'||

Line 2878: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);

2874: l_stmt:=substr(l_stmt,1,length(l_stmt)-1)||
2875: ' from bsc_tmp_base_rowid, bsc_tmp_base'||
2876: ' where bsc_tmp_base_rowid.row_id_tmp= bsc_tmp_base.rowid'||
2877: ' and bsc_tmp_base_rowid.row_id_base is null';
2878: BSC_UPDATE_UTIL.Execute_Immediate(l_stmt);
2879: commit;--we can have a commit only after both update and insert, otherwise there is data corruption
2880: end if;--if l_number_periodicity>0
2881: ---------------------------------------------------------------
2882:

Line 2884: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_BU');

2880: end if;--if l_number_periodicity>0
2881: ---------------------------------------------------------------
2882:
2883: -- Fix bug#4463132: Truncate temporary table after use
2884: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_BU');
2885: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_UPDATE');
2886: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_BAL');
2887: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PER_CHANGE_BAL');
2888: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_ROWID');

Line 2885: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_UPDATE');

2881: ---------------------------------------------------------------
2882:
2883: -- Fix bug#4463132: Truncate temporary table after use
2884: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_BU');
2885: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_UPDATE');
2886: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_BAL');
2887: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PER_CHANGE_BAL');
2888: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_ROWID');
2889:

Line 2886: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_BAL');

2882:
2883: -- Fix bug#4463132: Truncate temporary table after use
2884: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_BU');
2885: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_UPDATE');
2886: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_BAL');
2887: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PER_CHANGE_BAL');
2888: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_ROWID');
2889:
2890: RETURN TRUE;

Line 2887: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PER_CHANGE_BAL');

2883: -- Fix bug#4463132: Truncate temporary table after use
2884: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_BU');
2885: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_UPDATE');
2886: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_BAL');
2887: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PER_CHANGE_BAL');
2888: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_ROWID');
2889:
2890: RETURN TRUE;
2891: EXCEPTION

Line 2888: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_ROWID');

2884: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_BU');
2885: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_UPDATE');
2886: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_BAL');
2887: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PER_CHANGE_BAL');
2888: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BASE_ROWID');
2889:
2890: RETURN TRUE;
2891: EXCEPTION
2892: WHEN e_unexpected_error THEN

Line 2895: X_Message => BSC_UPDATE_UTIL.Get_Message('BSC_BTABLE_UPDATE_FAILED'),

2891: EXCEPTION
2892: WHEN e_unexpected_error THEN
2893: ROLLBACK;
2894: BSC_MESSAGE.Add(
2895: X_Message => BSC_UPDATE_UTIL.Get_Message('BSC_BTABLE_UPDATE_FAILED'),
2896: X_Source => 'BSC_UPDATE_BASE.Update_Base_Table');
2897: RETURN FALSE;
2898:
2899: WHEN OTHERS THEN