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_V2 dependencies on BSC_UPDATE_UTIL

Line 66: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;

62: h_current_per_base_table NUMBER;
63: h_per_base_table NUMBER;
64:
65: h_sql VARCHAR2(32000);
66: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
67: h_num_bind_vars NUMBER;
68:
69: h_num_rows NUMBER;
70: h_num_loads NUMBER;

Line 72: h_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2;

68:
69: h_num_rows NUMBER;
70: h_num_loads NUMBER;
71:
72: h_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
73: h_data_formulas BSC_UPDATE_UTIL.t_array_of_varchar2;
74: h_data_proj_methods BSC_UPDATE_UTIL.t_array_of_number;
75: h_data_measure_types BSC_UPDATE_UTIL.t_array_of_number;
76: h_num_data_columns NUMBER;

Line 73: h_data_formulas BSC_UPDATE_UTIL.t_array_of_varchar2;

69: h_num_rows NUMBER;
70: h_num_loads NUMBER;
71:
72: h_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
73: h_data_formulas BSC_UPDATE_UTIL.t_array_of_varchar2;
74: h_data_proj_methods BSC_UPDATE_UTIL.t_array_of_number;
75: h_data_measure_types BSC_UPDATE_UTIL.t_array_of_number;
76: h_num_data_columns NUMBER;
77:

Line 74: h_data_proj_methods BSC_UPDATE_UTIL.t_array_of_number;

70: h_num_loads NUMBER;
71:
72: h_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
73: h_data_formulas BSC_UPDATE_UTIL.t_array_of_varchar2;
74: h_data_proj_methods BSC_UPDATE_UTIL.t_array_of_number;
75: h_data_measure_types BSC_UPDATE_UTIL.t_array_of_number;
76: h_num_data_columns NUMBER;
77:
78: h_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2;

Line 75: h_data_measure_types BSC_UPDATE_UTIL.t_array_of_number;

71:
72: h_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
73: h_data_formulas BSC_UPDATE_UTIL.t_array_of_varchar2;
74: h_data_proj_methods BSC_UPDATE_UTIL.t_array_of_number;
75: h_data_measure_types BSC_UPDATE_UTIL.t_array_of_number;
76: h_num_data_columns NUMBER;
77:
78: h_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
79: h_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;

Line 78: h_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2;

74: h_data_proj_methods BSC_UPDATE_UTIL.t_array_of_number;
75: h_data_measure_types BSC_UPDATE_UTIL.t_array_of_number;
76: h_num_data_columns NUMBER;
77:
78: h_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
79: h_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
80: h_source_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
81: h_source_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
82: h_num_key_columns NUMBER;

Line 79: h_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;

75: h_data_measure_types BSC_UPDATE_UTIL.t_array_of_number;
76: h_num_data_columns NUMBER;
77:
78: h_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
79: h_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
80: h_source_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
81: h_source_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
82: h_num_key_columns NUMBER;
83:

Line 80: h_source_columns BSC_UPDATE_UTIL.t_array_of_varchar2;

76: h_num_data_columns NUMBER;
77:
78: h_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
79: h_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
80: h_source_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
81: h_source_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
82: h_num_key_columns NUMBER;
83:
84: BEGIN

Line 81: h_source_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;

77:
78: h_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
79: h_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
80: h_source_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
81: h_source_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
82: h_num_key_columns NUMBER;
83:
84: BEGIN
85:

Line 100: h_periodicity := BSC_UPDATE_UTIL.Get_Table_Periodicity(x_base_table);

96:
97:
98: -- Get the periodicity of the base table
99: -- Note: By design the periodicity of the input table and the base table are the same
100: h_periodicity := BSC_UPDATE_UTIL.Get_Table_Periodicity(x_base_table);
101: IF h_periodicity IS NULL THEN
102: RAISE e_periodicity_null;
103: END IF;
104:

Line 106: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(h_periodicity);

102: RAISE e_periodicity_null;
103: END IF;
104:
105: -- Get the calendar id of the input/base table
106: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(h_periodicity);
107: -- Get the current fiscal year
108: h_current_fy := BSC_UPDATE_UTIL.Get_Calendar_Fiscal_Year(h_calendar_id);
109:
110: -- Get the new period of the input table

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

104:
105: -- Get the calendar id of the input/base table
106: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(h_periodicity);
107: -- Get the current fiscal year
108: h_current_fy := BSC_UPDATE_UTIL.Get_Calendar_Fiscal_Year(h_calendar_id);
109:
110: -- Get the new period of the input table
111: h_per_input_table := 0;
112: -- If the base table is being re-calculated for incremental changes

Line 140: IF NOT BSC_UPDATE_UTIL.Get_Information_Data_Columns(x_base_table,

136: RAISE e_calc_period_base_table;
137: END IF;
138:
139: -- Get data columns
140: IF NOT BSC_UPDATE_UTIL.Get_Information_Data_Columns(x_base_table,
141: h_data_columns,
142: h_data_formulas,
143: h_data_proj_methods,
144: h_data_measure_types,

Line 150: IF NOT BSC_UPDATE_UTIL.Get_Information_Key_Columns(x_base_table,

146: RAISE e_get_info_data_columns;
147: END IF;
148:
149: -- Get key columns
150: IF NOT BSC_UPDATE_UTIL.Get_Information_Key_Columns(x_base_table,
151: h_key_columns,
152: h_key_dim_tables,
153: h_source_columns,
154: h_source_dim_tables,

Line 181: BSC_UPDATE_UTIL.Truncate_Table(h_proj_tbl_name);

177: h_batch_values(h_j) := h_partition_info.partition_info(h_j).partition_value;
178: END LOOP;
179:
180: -- Truncate projection table. It needs to be done here outside each job. We always recalculate projection
181: BSC_UPDATE_UTIL.Truncate_Table(h_proj_tbl_name);
182:
183: -- Initialize the row id table. It needs to be done here outside each job.
184: IF NOT x_correction_flag THEN
185: BSC_UPDATE_UTIL.Truncate_Table(h_rowid_tbl_name);

Line 185: BSC_UPDATE_UTIL.Truncate_Table(h_rowid_tbl_name);

181: BSC_UPDATE_UTIL.Truncate_Table(h_proj_tbl_name);
182:
183: -- Initialize the row id table. It needs to be done here outside each job.
184: IF NOT x_correction_flag THEN
185: BSC_UPDATE_UTIL.Truncate_Table(h_rowid_tbl_name);
186: h_sql := 'insert /*+ append';
187: IF BSC_UPDATE_UTIL.is_parallel THEN
188: h_sql := h_sql||' parallel('||h_rowid_tbl_name||')';
189: END IF;

Line 187: IF BSC_UPDATE_UTIL.is_parallel THEN

183: -- Initialize the row id table. It needs to be done here outside each job.
184: IF NOT x_correction_flag THEN
185: BSC_UPDATE_UTIL.Truncate_Table(h_rowid_tbl_name);
186: h_sql := 'insert /*+ append';
187: IF BSC_UPDATE_UTIL.is_parallel THEN
188: h_sql := h_sql||' parallel('||h_rowid_tbl_name||')';
189: END IF;
190: h_sql := h_sql||' */ into '||h_rowid_tbl_name||
191: ' select';

Line 192: IF BSC_UPDATE_UTIL.is_parallel THEN

188: h_sql := h_sql||' parallel('||h_rowid_tbl_name||')';
189: END IF;
190: h_sql := h_sql||' */ into '||h_rowid_tbl_name||
191: ' select';
192: IF BSC_UPDATE_UTIL.is_parallel THEN
193: h_sql := h_sql||' /*+ parallel('||x_input_table||') */';
194: END IF;
195: h_sql := h_sql||' rowid, trunc((rownum - :1)/ :2)'||
196: ' from '||x_input_table;

Line 200: h_num_rows := BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, 2);

196: ' from '||x_input_table;
197: h_bind_vars_values.delete;
198: h_bind_vars_values(1) := 1;
199: h_bind_vars_values(2) := 1000000;
200: h_num_rows := BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, 2);
201: commit;
202: h_num_loads := trunc((h_num_rows - 1)/1000000);
203:
204: END IF;

Line 328: BSC_UPDATE_UTIL.Truncate_Table(x_input_table);

324:
325: commit;
326:
327: -- Delete data from input table
328: BSC_UPDATE_UTIL.Truncate_Table(x_input_table);
329: END IF;
330:
331: --Fix bug#4962928: add this call
332: IF x_aw_flag THEN

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

345: RETURN FALSE;
346:
347: WHEN e_periodicity_null THEN
348: ROLLBACK;
349: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_BTABLE_CALCULATION_FAILED'),
350: x_source => 'BSC_UPDATE_BASE_V2.Calculate_Base_Table');
351: RETURN FALSE;
352:
353: WHEN e_calc_period_input_table THEN

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

363: RETURN FALSE;
364:
365: WHEN e_get_info_data_columns THEN
366: ROLLBACK;
367: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_BTABLE_CALCULATION_FAILED'),
368: x_source => 'BSC_UPDATE_BASE_V2.Calculate_Base_Table');
369: RETURN FALSE;
370:
371: WHEN e_get_info_key_columns THEN

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

369: RETURN FALSE;
370:
371: WHEN e_get_info_key_columns THEN
372: ROLLBACK;
373: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_BTABLE_CALCULATION_FAILED'),
374: x_source => 'BSC_UPDATE_BASE_V2.Calculate_Base_Table');
375: RETURN FALSE;
376:
377: WHEN e_create_types_for_mv_load THEN

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

436: h_yearly_flag := 0;
437: h_calendar_id := NULL;
438: h_calendar_source := NULL;
439:
440: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_periodicity);
441: h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(h_calendar_id);
442:
443: -- Get Target_Flag of the input table
444: h_target_flag := BSC_UPDATE_UTIL.Get_Table_Target_Flag(x_input_table);

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

437: h_calendar_id := NULL;
438: h_calendar_source := NULL;
439:
440: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_periodicity);
441: h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(h_calendar_id);
442:
443: -- Get Target_Flag of the input table
444: h_target_flag := BSC_UPDATE_UTIL.Get_Table_Target_Flag(x_input_table);
445:

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

440: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_periodicity);
441: h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(h_calendar_id);
442:
443: -- Get Target_Flag of the input table
444: h_target_flag := BSC_UPDATE_UTIL.Get_Table_Target_Flag(x_input_table);
445:
446: -- Get the current period of the input table
447: BEGIN
448: SELECT NVL(current_period, 0)

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

454: h_current_period := 0;
455: END;
456:
457: -- Get yearly flag of the periodicity
458: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
459:
460: -- Get the maximun period of real data reported in the input table
461: IF h_yearly_flag = 1 THEN -- Annually
462: -- The update period of an annual table is always the current

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

481: ELSE
482: -- BIS periodicity
483: --BSC-BIS-DIMENSIONS: The input table has a column called TIME_FK instead of YEAR, PERIOD
484:
485: h_periodicity_type := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_periodicity);
486:
487: IF h_periodicity_type = 9 THEN
488: -- It is a daily periodicity. The TIME_FK column in the input table is of type DATE.
489: h_sql := 'SELECT MAX(p.period_id)'||

Line 614: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,

610: x_periodicity IN NUMBER,
611: x_calendar_id IN NUMBER,
612: x_current_period IN NUMBER,
613: x_current_fy IN NUMBER,
614: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
615: x_num_key_columns IN NUMBER,
616: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
617: x_data_proj_methods IN BSC_UPDATE_UTIL.t_array_of_number,
618: x_num_data_columns IN NUMBER,

Line 616: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,

612: x_current_period IN NUMBER,
613: x_current_fy IN NUMBER,
614: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
615: x_num_key_columns IN NUMBER,
616: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
617: x_data_proj_methods IN BSC_UPDATE_UTIL.t_array_of_number,
618: x_num_data_columns IN NUMBER,
619: x_partition_name IN VARCHAR2,
620: x_batch_value IN NUMBER,

Line 617: x_data_proj_methods IN BSC_UPDATE_UTIL.t_array_of_number,

613: x_current_fy IN NUMBER,
614: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
615: x_num_key_columns IN NUMBER,
616: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
617: x_data_proj_methods IN BSC_UPDATE_UTIL.t_array_of_number,
618: x_num_data_columns IN NUMBER,
619: x_partition_name IN VARCHAR2,
620: x_batch_value IN NUMBER,
621: x_parallel_jobs IN VARCHAR2,

Line 628: --h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_varchar2;

624: ) IS
625:
626: --h_sql clob;
627: h_sql dbms_sql.varchar2A;
628: --h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_varchar2;
629: h_bind_vars_values dbms_sql.varchar2_table;
630: h_num_bind_vars NUMBER;
631: h_i NUMBER;
632: h_j NUMBER;

Line 634: h_avg_cols BSC_UPDATE_UTIL.t_array_of_varchar2;

630: h_num_bind_vars NUMBER;
631: h_i NUMBER;
632: h_j NUMBER;
633: h_many_methods BOOLEAN;
634: h_avg_cols BSC_UPDATE_UTIL.t_array_of_varchar2;
635: h_num_avg_cols NUMBER;
636: h_perf_cols BSC_UPDATE_UTIL.t_array_of_varchar2;
637: h_num_perf_cols NUMBER;
638: h_custom_cols BSC_UPDATE_UTIL.t_array_of_varchar2;

Line 636: h_perf_cols BSC_UPDATE_UTIL.t_array_of_varchar2;

632: h_j NUMBER;
633: h_many_methods BOOLEAN;
634: h_avg_cols BSC_UPDATE_UTIL.t_array_of_varchar2;
635: h_num_avg_cols NUMBER;
636: h_perf_cols BSC_UPDATE_UTIL.t_array_of_varchar2;
637: h_num_perf_cols NUMBER;
638: h_custom_cols BSC_UPDATE_UTIL.t_array_of_varchar2;
639: h_num_custom_cols NUMBER;
640: h_num_proj_cols NUMBER;

Line 638: h_custom_cols BSC_UPDATE_UTIL.t_array_of_varchar2;

634: h_avg_cols BSC_UPDATE_UTIL.t_array_of_varchar2;
635: h_num_avg_cols NUMBER;
636: h_perf_cols BSC_UPDATE_UTIL.t_array_of_varchar2;
637: h_num_perf_cols NUMBER;
638: h_custom_cols BSC_UPDATE_UTIL.t_array_of_varchar2;
639: h_num_custom_cols NUMBER;
640: h_num_proj_cols NUMBER;
641:
642: h_yearly_flag NUMBER;

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

675: h_num_proj_cols := h_num_proj_cols + 1;
676: END IF;
677: END LOOP;
678:
679: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
680: h_per_column := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity);
681: IF h_yearly_flag = 1 THEN
682: IF NOT BSC_UPDATE_UTIL.Get_Table_Range_Of_Years(x_base_table, h_num_of_years, h_previous_years) THEN
683: h_num_of_years := 2;

Line 680: h_per_column := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity);

676: END IF;
677: END LOOP;
678:
679: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
680: h_per_column := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity);
681: IF h_yearly_flag = 1 THEN
682: IF NOT BSC_UPDATE_UTIL.Get_Table_Range_Of_Years(x_base_table, h_num_of_years, h_previous_years) THEN
683: h_num_of_years := 2;
684: h_previous_years := 1;

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

678:
679: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
680: h_per_column := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity);
681: IF h_yearly_flag = 1 THEN
682: IF NOT BSC_UPDATE_UTIL.Get_Table_Range_Of_Years(x_base_table, h_num_of_years, h_previous_years) THEN
683: h_num_of_years := 2;
684: h_previous_years := 1;
685: END IF;
686: h_min_year := x_current_fy - h_previous_years;

Line 700: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

696: h_many_methods := TRUE;
697: END IF;
698:
699: bsc_dbgen_utils.add_string(h_sql, 'insert /*+ append');
700: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
701: bsc_dbgen_utils.add_string(h_sql, ' parallel('||x_proj_table||')');
702: END IF;
703: bsc_dbgen_utils.add_string(h_sql, ' */ into '||x_proj_table);
704: IF x_parallel_jobs = 'Y' THEN

Line 724: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

720: END IF;
721: bsc_dbgen_utils.add_string(h_sql, ')');
722: IF h_many_methods THEN
723: bsc_dbgen_utils.add_string(h_sql, ' select');
724: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
725: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(p) */');
726: END IF;
727: bsc_dbgen_utils.add_string(h_sql, ' p.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
728: FOR h_i IN 1..x_num_key_columns LOOP

Line 755: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

751: h_per_ini := 1;
752: END IF;
753: END IF;
754: bsc_dbgen_utils.add_string(h_sql, ' select');
755: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
756: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(p) parallel(tp) parallel(tr) parallel(pp) */');
757: END IF;
758: bsc_dbgen_utils.add_string(h_sql, ' p.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
759: FOR h_i IN 1..x_num_key_columns LOOP

Line 797: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

793: IF x_aw_flag THEN
794: bsc_dbgen_utils.add_string(h_sql, ', ''Y'' projection, '||x_change_vector_value||' change_vector');
795: END IF;
796: bsc_dbgen_utils.add_string(h_sql, ' from (select');
797: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
798: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(a) parallel(bsc_tmp_all_periods) */');
799: END IF;
800: bsc_dbgen_utils.add_string(h_sql, ' a.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
801: FOR h_i IN 1..x_num_key_columns LOOP

Line 809: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

805: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
806: bsc_dbgen_utils.add_string(h_sql, ', '||x_periodicity||' periodicity_id');
807: END IF;
808: bsc_dbgen_utils.add_string(h_sql, ' from (select');
809: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
810: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_base_table||') */');
811: END IF;
812: bsc_dbgen_utils.add_string(h_sql, ' distinct '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
813: FOR h_i IN 1..x_num_key_columns LOOP

Line 830: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

826: END IF;
827: bsc_dbgen_utils.add_string(h_sql, ') a,');
828: IF h_yearly_flag <> 1 THEN
829: bsc_dbgen_utils.add_string(h_sql, ' (select');
830: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
831: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(bsc_db_calendar) */');
832: END IF;
833: bsc_dbgen_utils.add_string(h_sql, ' distinct year, '||h_per_column||' period'||
834: ' from bsc_db_calendar'||

Line 840: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

836: ' and '||h_per_column||' > '||x_current_period||
837: ' ) bsc_tmp_all_periods');
838: ELSE
839: bsc_dbgen_utils.add_string(h_sql, ' (select');
840: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
841: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(bsc_db_calendar) */');
842: END IF;
843: bsc_dbgen_utils.add_string(h_sql, ' distinct year, 0 period'||
844: ' from bsc_db_calendar'||

Line 851: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

847: ' ) bsc_tmp_all_periods');
848: END IF;
849: bsc_dbgen_utils.add_string(h_sql, ' ) p,'||
850: ' (select ');
851: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
852: bsc_dbgen_utils.add_string(h_sql, '/*+ parallel('||x_base_table||') */ ');
853: END IF;
854: FOR h_i IN 1..x_num_key_columns LOOP
855: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||' key'||h_i||', ');

Line 884: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

880: END LOOP;
881: END IF;
882: bsc_dbgen_utils.add_string(h_sql, ') tp,'||
883: ' (select ');
884: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
885: bsc_dbgen_utils.add_string(h_sql, '/*+ parallel('||x_base_table||') */ ');
886: END IF;
887: FOR h_i IN 1..x_num_key_columns LOOP
888: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||' key'||h_i||', ');

Line 917: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

913: END LOOP;
914: END IF;
915: bsc_dbgen_utils.add_string(h_sql, ') tr,'||
916: ' (select ');
917: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
918: bsc_dbgen_utils.add_string(h_sql, '/*+ parallel('||x_base_table||') */ ');
919: END IF;
920: FOR h_i IN 1..x_num_key_columns LOOP
921: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||' key'||h_i||', ');

Line 964: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

960: END IF;
961:
962: -- all measures with this projection method goes here other goes with null
963: bsc_dbgen_utils.add_string(h_sql, ' select');
964: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
965: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(p) parallel(b) */');
966: END IF;
967: bsc_dbgen_utils.add_string(h_sql, ' p.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
968: FOR h_i IN 1..x_num_key_columns LOOP

Line 986: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

982: IF x_aw_flag THEN
983: bsc_dbgen_utils.add_string(h_sql, ', ''Y'' projection, '||x_change_vector_value||' change_vector');
984: END IF;
985: bsc_dbgen_utils.add_string(h_sql, ' from (select');
986: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
987: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(a) parallel(bsc_tmp_all_periods) */');
988: END IF;
989: bsc_dbgen_utils.add_string(h_sql, ' a.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
990: FOR h_i IN 1..x_num_key_columns LOOP

Line 998: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

994: IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
995: bsc_dbgen_utils.add_string(h_sql, ', '||x_periodicity||' periodicity_id');
996: END IF;
997: bsc_dbgen_utils.add_string(h_sql, ' from (select');
998: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
999: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_base_table||') */');
1000: END IF;
1001: bsc_dbgen_utils.add_string(h_sql, ' distinct '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
1002: FOR h_i IN 1..x_num_key_columns LOOP

Line 1019: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

1015: END IF;
1016: bsc_dbgen_utils.add_string(h_sql, ') a,');
1017: IF h_yearly_flag <> 1 THEN
1018: bsc_dbgen_utils.add_string(h_sql, ' (select');
1019: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1020: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(bsc_db_calendar) */');
1021: END IF;
1022: bsc_dbgen_utils.add_string(h_sql, ' distinct year, '||h_per_column||' period'||
1023: ' from bsc_db_calendar'||

Line 1029: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

1025: ' and '||h_per_column||' > '||x_current_period||
1026: ' ) bsc_tmp_all_periods');
1027: ELSE
1028: bsc_dbgen_utils.add_string(h_sql, ' (select');
1029: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1030: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(bsc_db_calendar) */');
1031: END IF;
1032: bsc_dbgen_utils.add_string(h_sql, ' distinct year, 0 period'||
1033: ' from bsc_db_calendar'||

Line 1040: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

1036: ' ) bsc_tmp_all_periods');
1037: END IF;
1038: bsc_dbgen_utils.add_string(h_sql, ') p,'||
1039: ' (select ');
1040: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1041: bsc_dbgen_utils.add_string(h_sql, '/*+ parallel('||x_base_table||') */ ');
1042: END IF;
1043: FOR h_i IN 1..x_num_key_columns LOOP
1044: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');

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

1082: IF x_aw_flag THEN
1083: bsc_dbgen_utils.add_string(h_sql, ', p.projection, p.change_vector');
1084: END IF;
1085: END IF;
1086: --BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1087: BSC_DBGEN_UTILS.Execute_Immediate(h_sql);
1088: commit;
1089: END IF;
1090:

Line 1101: h_end_per := BSC_UPDATE_UTIL.Get_Num_Periods_Periodicity(x_periodicity, x_current_fy);

1097: h_init_per := x_current_fy + 1;
1098: h_end_per := h_max_year;
1099: ELSE
1100: h_init_per := x_current_period + 1;
1101: h_end_per := BSC_UPDATE_UTIL.Get_Num_Periods_Periodicity(x_periodicity, x_current_fy);
1102: END IF;
1103: IF (h_num_perf_cols > 0) OR (h_num_custom_cols > 0) THEN
1104: -- There are rows in the projection table already. So we need to update.
1105: bsc_dbgen_utils.add_string(h_sql, 'update '||x_proj_table||' p'||

Line 1112: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

1108: bsc_dbgen_utils.add_string(h_sql, ', '||h_avg_cols(h_i));
1109: END LOOP;
1110: bsc_dbgen_utils.add_string(h_sql, ') = ('||
1111: ' select ');
1112: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1113: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(u) */ ');
1114: END IF;
1115: bsc_dbgen_utils.add_string(h_sql, 'avg('||h_avg_cols(1)||')');
1116: FOR h_i IN 2..h_num_avg_cols LOOP

Line 1121: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

1117: bsc_dbgen_utils.add_string(h_sql, ', avg('||h_avg_cols(h_i)||')');
1118: END LOOP;
1119: bsc_dbgen_utils.add_string(h_sql, ' from ('||
1120: ' select ');
1121: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1122: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_base_table||') */ ');
1123: END IF;
1124: FOR h_i IN 1..x_num_key_columns LOOP
1125: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');

Line 1144: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

1140: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = :3');
1141: END IF;
1142: bsc_dbgen_utils.add_string(h_sql, ' union all'||
1143: ' select ');
1144: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1145: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_proj_table||') */ ');
1146: END IF;
1147: FOR h_i IN 1..x_num_key_columns LOOP
1148: bsc_dbgen_utils.add_string(h_sql, x_key_columns(h_i)||', ');

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

1241: IF x_parallel_jobs = 'Y' THEN
1242: h_num_bind_vars := h_num_bind_vars + 1;
1243: h_bind_vars_values(h_num_bind_vars) := x_batch_value;
1244: END IF;
1245: --BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
1246: BSC_DBGEN_UTILS.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
1247: commit;
1248: END LOOP;
1249: ELSE

Line 1252: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

1248: END LOOP;
1249: ELSE
1250: -- There is no rows in the projection table. So we need to insert.
1251: bsc_dbgen_utils.add_string(h_sql, 'insert /*+ append');
1252: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1253: bsc_dbgen_utils.add_string(h_sql, ' parallel('||x_proj_table||')');
1254: END IF;
1255: bsc_dbgen_utils.add_string(h_sql, ' */ into '||x_proj_table);
1256: IF x_parallel_jobs = 'Y' THEN

Line 1275: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

1271: bsc_dbgen_utils.add_string(h_sql, ', projection, change_vector');
1272: END IF;
1273: bsc_dbgen_utils.add_string(h_sql, ')'||
1274: ' select');
1275: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1276: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel(u) */');
1277: END IF;
1278: bsc_dbgen_utils.add_string(h_sql, ' '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
1279: FOR h_i IN 1..x_num_key_columns LOOP

Line 1298: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

1294: bsc_dbgen_utils.add_string(h_sql, ', :3, :4');
1295: END IF;
1296: bsc_dbgen_utils.add_string(h_sql, ' from ('||
1297: ' select');
1298: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1299: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_base_table||') */');
1300: END IF;
1301: bsc_dbgen_utils.add_string(h_sql, ' '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
1302: FOR h_i IN 1..x_num_key_columns LOOP

Line 1322: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

1318: bsc_dbgen_utils.add_string(h_sql, ' and '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = :7');
1319: END IF;
1320: bsc_dbgen_utils.add_string(h_sql, ' union all'||
1321: ' select ');
1322: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
1323: bsc_dbgen_utils.add_string(h_sql, ' /*+ parallel('||x_proj_table||') */ ');
1324: END IF;
1325: bsc_dbgen_utils.add_string(h_sql, BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ');
1326: FOR h_i IN 1..x_num_key_columns LOOP

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

1409: h_bind_vars_values(h_num_bind_vars) := x_current_fy;
1410: h_num_bind_vars := h_num_bind_vars + 1;
1411: h_bind_vars_values(h_num_bind_vars) := (h_j - 1);
1412: END IF;
1413: --BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
1414: BSC_DBGEN_UTILS.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
1415: commit;
1416: END LOOP;
1417: END IF;

Line 1439: h_table_columns BSC_UPDATE_UTIL.t_array_temp_table_cols;

1435:
1436: e_unexpected_error EXCEPTION;
1437:
1438: h_table_name VARCHAR2(30);
1439: h_table_columns BSC_UPDATE_UTIL.t_array_temp_table_cols;
1440: h_num_columns NUMBER;
1441:
1442: BEGIN
1443:

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

1469: h_table_columns(h_num_columns).column_name := 'PERIODICITY_ID';
1470: h_table_columns(h_num_columns).data_type := 'NUMBER';
1471: h_table_columns(h_num_columns).data_size := NULL;
1472: h_table_columns(h_num_columns).add_to_index := 'N';
1473: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
1474: RAISE e_unexpected_error;
1475: END IF;
1476:
1477: RETURN TRUE;

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

1477: RETURN TRUE;
1478:
1479: EXCEPTION
1480: WHEN e_unexpected_error THEN
1481: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR'),
1482: x_source => 'BSC_UPDATE_BASE_V2.Create_Generic_Temp_Tables');
1483: RETURN FALSE;
1484:
1485: WHEN OTHERS THEN

Line 1517: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,

1513: x_calendar_id IN NUMBER,
1514: x_current_period IN NUMBER,
1515: x_old_current_period IN NUMBER,
1516: x_current_fy IN NUMBER,
1517: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1518: x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1519: x_num_key_columns IN NUMBER,
1520: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1521: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,

Line 1518: x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,

1514: x_current_period IN NUMBER,
1515: x_old_current_period IN NUMBER,
1516: x_current_fy IN NUMBER,
1517: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1518: x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1519: x_num_key_columns IN NUMBER,
1520: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1521: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1522: x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,

Line 1520: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,

1516: x_current_fy IN NUMBER,
1517: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1518: x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1519: x_num_key_columns IN NUMBER,
1520: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1521: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1522: x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,
1523: x_num_data_columns IN NUMBER,
1524: x_partition_name IN VARCHAR2,

Line 1521: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,

1517: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1518: x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1519: x_num_key_columns IN NUMBER,
1520: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1521: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1522: x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,
1523: x_num_data_columns IN NUMBER,
1524: x_partition_name IN VARCHAR2,
1525: x_batch_value IN NUMBER,

Line 1522: x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,

1518: x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1519: x_num_key_columns IN NUMBER,
1520: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1521: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1522: x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,
1523: x_num_data_columns IN NUMBER,
1524: x_partition_name IN VARCHAR2,
1525: x_batch_value IN NUMBER,
1526: x_num_partitions IN NUMBER,

Line 1539: h_bal_columns BSC_UPDATE_UTIL.t_array_of_varchar2;

1535: h_i NUMBER;
1536: h_calendar_source VARCHAR2(20);
1537: h_yearly_flag NUMBER;
1538: h_periodicity_type NUMBER;
1539: h_bal_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
1540: h_num_bal_columns NUMBER;
1541: l_sql varchar2(10000);
1542: l_newline varchar2(10):='
1543: ';

Line 1546: h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(x_calendar_id);

1542: l_newline varchar2(10):='
1543: ';
1544: BEGIN
1545:
1546: h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(x_calendar_id);
1547: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
1548: h_periodicity_type := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_periodicity);
1549: h_num_bal_columns := 0;
1550: FOR h_i IN 1..x_num_data_columns LOOP

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

1543: ';
1544: BEGIN
1545:
1546: h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(x_calendar_id);
1547: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
1548: h_periodicity_type := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_periodicity);
1549: h_num_bal_columns := 0;
1550: FOR h_i IN 1..x_num_data_columns LOOP
1551: IF x_data_measure_types(h_i) <> 1 THEN

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

1544: BEGIN
1545:
1546: h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(x_calendar_id);
1547: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
1548: h_periodicity_type := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_periodicity);
1549: h_num_bal_columns := 0;
1550: FOR h_i IN 1..x_num_data_columns LOOP
1551: IF x_data_measure_types(h_i) <> 1 THEN
1552: h_num_bal_columns := h_num_bal_columns + 1;

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

2076: ' end loop;'||l_newline||
2077: ' end;';
2078: bsc_dbgen_utils.add_string(h_sql, l_sql);
2079:
2080: --BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2081: BSC_DBGEN_UTILS.Execute_Immediate(h_sql);
2082: commit;
2083: x_return_status := 'success';
2084:

Line 2106: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,

2102: x_periodicity IN NUMBER,
2103: x_calendar_id IN NUMBER,
2104: x_current_period IN NUMBER,
2105: x_current_fy IN NUMBER,
2106: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2107: x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2108: x_num_key_columns IN NUMBER,
2109: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2110: x_num_data_columns IN NUMBER,

Line 2107: x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,

2103: x_calendar_id IN NUMBER,
2104: x_current_period IN NUMBER,
2105: x_current_fy IN NUMBER,
2106: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2107: x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2108: x_num_key_columns IN NUMBER,
2109: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2110: x_num_data_columns IN NUMBER,
2111: x_partition_name IN VARCHAR2,

Line 2109: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,

2105: x_current_fy IN NUMBER,
2106: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2107: x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2108: x_num_key_columns IN NUMBER,
2109: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2110: x_num_data_columns IN NUMBER,
2111: x_partition_name IN VARCHAR2,
2112: x_batch_value IN NUMBER,
2113: x_num_partitions IN NUMBER,

Line 2128: h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(x_calendar_id);

2124: h_yearly_flag NUMBER;
2125: h_periodicity_type NUMBER;
2126:
2127: BEGIN
2128: h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(x_calendar_id);
2129: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
2130: h_periodicity_type := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_periodicity);
2131:
2132: bsc_dbgen_utils.add_string(h_sql, 'create or replace procedure '||x_proc_name||' as'||

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

2125: h_periodicity_type NUMBER;
2126:
2127: BEGIN
2128: h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(x_calendar_id);
2129: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
2130: h_periodicity_type := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_periodicity);
2131:
2132: bsc_dbgen_utils.add_string(h_sql, 'create or replace procedure '||x_proc_name||' as'||
2133: ' type bsc_b_r is record(');

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

2126:
2127: BEGIN
2128: h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(x_calendar_id);
2129: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
2130: h_periodicity_type := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_periodicity);
2131:
2132: bsc_dbgen_utils.add_string(h_sql, 'create or replace procedure '||x_proc_name||' as'||
2133: ' type bsc_b_r is record(');
2134: FOR h_i IN 1..x_num_key_columns LOOP

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

2417: ' end loop;'||
2418: ' end;');
2419:
2420: -- Create the stored procedure
2421: --BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2422: BSC_DBGEN_UTILS.Execute_Immediate(h_sql);
2423: commit;
2424:
2425: x_return_status := 'success';

Line 2440: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,

2436: | PROCEDURE Create_Types_for_MV_Load
2437: +============================================================================*/
2438: PROCEDURE Create_Types_For_MV_Load(
2439: x_base_table IN VARCHAR2,
2440: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2441: x_num_key_columns IN NUMBER,
2442: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2443: x_num_data_columns IN NUMBER,
2444: x_return_status OUT NOCOPY VARCHAR2,

Line 2442: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,

2438: PROCEDURE Create_Types_For_MV_Load(
2439: x_base_table IN VARCHAR2,
2440: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2441: x_num_key_columns IN NUMBER,
2442: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2443: x_num_data_columns IN NUMBER,
2444: x_return_status OUT NOCOPY VARCHAR2,
2445: x_error_message OUT NOCOPY VARCHAR2
2446: ) IS

Line 2532: BSC_UPDATE_UTIL.Truncate_Table('BSC_DB_CALENDAR_TEMP');

2528: h_y VARCHAR2(1);
2529: h_n VARCHAR2(1);
2530:
2531: BEGIN
2532: BSC_UPDATE_UTIL.Truncate_Table('BSC_DB_CALENDAR_TEMP');
2533: commit;
2534:
2535: h_column_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity);
2536: h_y := 'Y';

Line 2535: h_column_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity);

2531: BEGIN
2532: BSC_UPDATE_UTIL.Truncate_Table('BSC_DB_CALENDAR_TEMP');
2533: commit;
2534:
2535: h_column_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity);
2536: h_y := 'Y';
2537: h_n := 'N';
2538:
2539: OPEN c_upper_periodicities(x_base_table, 6);

Line 2640: BSC_UPDATE_UTIL.Truncate_Table('BSC_DB_CALENDAR_TEMP');

2636: h_y VARCHAR2(1);
2637: h_n VARCHAR2(1);
2638:
2639: BEGIN
2640: BSC_UPDATE_UTIL.Truncate_Table('BSC_DB_CALENDAR_TEMP');
2641: commit;
2642:
2643: h_column_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity);
2644: h_y := 'Y';

Line 2643: h_column_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity);

2639: BEGIN
2640: BSC_UPDATE_UTIL.Truncate_Table('BSC_DB_CALENDAR_TEMP');
2641: commit;
2642:
2643: h_column_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity);
2644: h_y := 'Y';
2645: h_n := 'N';
2646:
2647: OPEN c_upper_periodicities(x_base_table, 6);

Line 2709: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,

2705: x_calendar_id IN NUMBER,
2706: x_current_period IN NUMBER,
2707: x_old_current_period IN NUMBER,
2708: x_current_fy IN NUMBER,
2709: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2710: x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2711: x_num_key_columns IN NUMBER,
2712: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2713: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,

Line 2710: x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,

2706: x_current_period IN NUMBER,
2707: x_old_current_period IN NUMBER,
2708: x_current_fy IN NUMBER,
2709: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2710: x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2711: x_num_key_columns IN NUMBER,
2712: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2713: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2714: x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,

Line 2712: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,

2708: x_current_fy IN NUMBER,
2709: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2710: x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2711: x_num_key_columns IN NUMBER,
2712: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2713: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2714: x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,
2715: x_num_data_columns IN NUMBER,
2716: x_partition_name IN VARCHAR2,

Line 2713: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,

2709: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2710: x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2711: x_num_key_columns IN NUMBER,
2712: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2713: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2714: x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,
2715: x_num_data_columns IN NUMBER,
2716: x_partition_name IN VARCHAR2,
2717: x_batch_value IN NUMBER,

Line 2714: x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,

2710: x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2711: x_num_key_columns IN NUMBER,
2712: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2713: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2714: x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,
2715: x_num_data_columns IN NUMBER,
2716: x_partition_name IN VARCHAR2,
2717: x_batch_value IN NUMBER,
2718: x_num_partitions IN NUMBER,

Line 2735: h_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;

2731: h_error_message VARCHAR2(2000);
2732:
2733: h_i NUMBER;
2734: h_sql VARCHAR2(32000);
2735: h_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
2736:
2737: h_level_table_name VARCHAR2(100);
2738: h_level_short_name VARCHAR2(300);
2739: h_level_source VARCHAR2(100);

Line 2874: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

2870: END IF;
2871:
2872: -- Execute the stored procedure
2873: h_sql := 'BEGIN '||h_proc_name||'; END;';
2874: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2875: COMMIT;
2876:
2877: -- Drop the stored procedure
2878: h_sql := 'DROP PROCEDURE '||h_proc_name;

Line 2879: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

2875: COMMIT;
2876:
2877: -- Drop the stored procedure
2878: h_sql := 'DROP PROCEDURE '||h_proc_name;
2879: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2880: COMMIT;
2881: x_return_status := 'success';
2882:
2883: EXCEPTION

Line 2920: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,

2916: x_periodicity IN NUMBER,
2917: x_calendar_id IN NUMBER,
2918: x_current_period IN NUMBER,
2919: x_current_fy IN NUMBER,
2920: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2921: x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2922: x_num_key_columns IN NUMBER,
2923: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2924: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,

Line 2921: x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,

2917: x_calendar_id IN NUMBER,
2918: x_current_period IN NUMBER,
2919: x_current_fy IN NUMBER,
2920: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2921: x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2922: x_num_key_columns IN NUMBER,
2923: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2924: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2925: x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,

Line 2923: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,

2919: x_current_fy IN NUMBER,
2920: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2921: x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2922: x_num_key_columns IN NUMBER,
2923: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2924: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2925: x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,
2926: x_num_data_columns IN NUMBER,
2927: x_partition_name IN VARCHAR2,

Line 2924: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,

2920: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2921: x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2922: x_num_key_columns IN NUMBER,
2923: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2924: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2925: x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,
2926: x_num_data_columns IN NUMBER,
2927: x_partition_name IN VARCHAR2,
2928: x_batch_value IN NUMBER,

Line 2925: x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,

2921: x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2922: x_num_key_columns IN NUMBER,
2923: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2924: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2925: x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,
2926: x_num_data_columns IN NUMBER,
2927: x_partition_name IN VARCHAR2,
2928: x_batch_value IN NUMBER,
2929: x_num_partitions IN NUMBER,

Line 2937: h_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;

2933: ) IS
2934:
2935: h_sql VARCHAR2(32000);
2936: h_i NUMBER;
2937: h_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
2938:
2939: h_level_table_name VARCHAR2(100);
2940: h_level_short_name VARCHAR2(300);
2941: h_level_source VARCHAR2(100);

Line 2944: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;

2940: h_level_short_name VARCHAR2(300);
2941: h_level_source VARCHAR2(100);
2942: h_dbi_dim_data BSC_UPDATE_DIM.t_dbi_dim_data;
2943:
2944: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
2945: h_num_bind_vars NUMBER;
2946:
2947: h_calendar_source VARCHAR2(20);
2948: h_yearly_flag NUMBER;

Line 2954: h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(x_calendar_id);

2950:
2951: BEGIN
2952: h_num_bind_vars := 0;
2953:
2954: h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(x_calendar_id);
2955: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
2956: h_periodicity_type := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_periodicity);
2957:
2958: FOR h_i IN 1..x_num_key_columns LOOP

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

2951: BEGIN
2952: h_num_bind_vars := 0;
2953:
2954: h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(x_calendar_id);
2955: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
2956: h_periodicity_type := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_periodicity);
2957:
2958: FOR h_i IN 1..x_num_key_columns LOOP
2959: h_level_table_name := x_key_dim_tables(h_i);

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

2952: h_num_bind_vars := 0;
2953:
2954: h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(x_calendar_id);
2955: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
2956: h_periodicity_type := BSC_UPDATE_UTIL.Get_Periodicity_Type(x_periodicity);
2957:
2958: FOR h_i IN 1..x_num_key_columns LOOP
2959: h_level_table_name := x_key_dim_tables(h_i);
2960: -- BSC-BIS-DIMENSIONS: If the dimension is a DBI dimension and it is materialized and

Line 2990: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

2986: h_num_bind_vars := 0;
2987: h_bind_vars_values.delete;
2988:
2989: h_sql := 'INSERT /*+ append';
2990: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
2991: h_sql := h_sql||' parallel ('||x_base_table||')';
2992: END IF;
2993: h_sql := h_sql||' */ INTO '||x_base_table;
2994: IF x_parallel_jobs = 'Y' THEN

Line 3008: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

3004: END LOOP;
3005: h_sql := h_sql||')'||
3006: ' WITH BSC_I_DATA AS ('||
3007: ' SELECT';
3008: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
3009: h_sql := h_sql||' /*+ parallel ('||x_input_table||')';
3010: FOR h_i IN 1..x_num_key_columns LOOP
3011: h_sql := h_sql||' parallel('||h_key_dim_tables(h_i)||')';
3012: END LOOP;

Line 3040: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

3036: FOR h_i IN 1..x_num_data_columns LOOP
3037: h_sql := h_sql||', '||x_input_table||'.'||x_data_columns(h_i);
3038: END LOOP;
3039: h_sql := h_sql||' FROM (SELECT';
3040: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
3041: h_sql := h_sql||' /*+ parallel ('||x_input_table||') */';
3042: END IF;
3043: IF x_parallel_jobs = 'Y' THEN
3044: h_sql := h_sql||' :2 '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME;

Line 3121: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

3117: h_bind_vars_values(h_num_bind_vars) := x_periodicity;
3118: END IF;
3119: h_sql := h_sql||')'||
3120: ' SELECT';
3121: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
3122: h_sql := h_sql||' /*+ parallel ('||x_input_table||') */';
3123: END IF;
3124: h_sql := h_sql||' '||x_input_table||'.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ';
3125: FOR h_i IN 1..x_num_key_columns LOOP

Line 3136: --IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

3132: END LOOP;
3133: h_sql := h_sql||' FROM ('||
3134: ' SELECT';
3135: -- Fix bug#5155523 Do not use parallel hint on BSC_I_DATA
3136: --IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
3137: -- h_sql := h_sql||' /*+ parallel (BSC_I_DATA) */';
3138: --END IF;
3139: h_sql := h_sql||' BSC_I_DATA.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||', ';
3140: FOR h_i IN 1..x_num_key_columns LOOP

Line 3151: --IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

3147: h_sql := h_sql||' FROM BSC_I_DATA'||
3148: ' UNION ALL'||
3149: ' SELECT';
3150: -- Fix bug#5155523 Do not use parallel hint on BSC_I_DATA or BSC_DB_CALENDAR_TEMP
3151: --IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
3152: -- h_sql := h_sql||' /*+ parallel (BSC_I_DATA) parallel (BSC_DB_CALENDAR_TEMP) */';
3153: --END IF;
3154: --Fix bug#5155523 Do not use max(bsc_i_data.batch_column_name) instead add it to the group by
3155: h_sql := h_sql||' BSC_I_DATA.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' '||

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

3179: h_sql := h_sql||'BSC_I_DATA.YEAR, BSC_I_DATA.TYPE, BSC_DB_CALENDAR_TEMP.UPPER_PERIOD,'||
3180: ' BSC_DB_CALENDAR_TEMP.PERIODICITY_ID'||
3181: ') '||x_input_table;
3182:
3183: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
3184: COMMIT;
3185: ELSE
3186: -- Summary or AW architecture. No higher periodicities in the base table
3187: h_num_bind_vars := 0;

Line 3191: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

3187: h_num_bind_vars := 0;
3188: h_bind_vars_values.delete;
3189:
3190: h_sql := 'INSERT /*+ append';
3191: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
3192: h_sql := h_sql||' parallel ('||x_base_table||')';
3193: END IF;
3194: h_sql := h_sql||' */ INTO '||x_base_table;
3195: IF x_parallel_jobs = 'Y' THEN

Line 3211: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

3207: h_sql := h_sql||', PROJECTION, CHANGE_VECTOR';
3208: END IF;
3209: h_sql := h_sql||')'||
3210: ' SELECT';
3211: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
3212: h_sql := h_sql||' /*+ parallel ('||x_input_table||')';
3213: FOR h_i IN 1..x_num_key_columns LOOP
3214: h_sql := h_sql||' parallel('||h_key_dim_tables(h_i)||')';
3215: END LOOP;

Line 3287: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

3283: h_num_bind_vars := h_num_bind_vars + 1;
3284: h_bind_vars_values(h_num_bind_vars) := x_change_vector_value;
3285: END IF;
3286: h_sql := h_sql||' FROM (SELECT';
3287: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
3288: h_sql := h_sql||' /*+ parallel ('||x_input_table||') */';
3289: END IF;
3290: IF x_parallel_jobs = 'Y' THEN
3291: h_sql := h_sql||' :5 '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME;

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

3363: h_num_bind_vars := h_num_bind_vars + 1;
3364: h_bind_vars_values(h_num_bind_vars) := x_periodicity;
3365: END IF;
3366:
3367: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
3368: COMMIT;
3369: END IF;
3370:
3371: x_return_status := 'success';

Line 3387: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,

3383: +============================================================================*/
3384: PROCEDURE Calc_Higher_Periodicities(
3385: x_base_table IN VARCHAR2,
3386: x_periodicity IN NUMBER,
3387: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3388: x_num_key_columns IN NUMBER,
3389: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3390: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3391: x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,

Line 3389: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,

3385: x_base_table IN VARCHAR2,
3386: x_periodicity IN NUMBER,
3387: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3388: x_num_key_columns IN NUMBER,
3389: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3390: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3391: x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,
3392: x_num_data_columns IN NUMBER,
3393: x_partition_name IN VARCHAR2,

Line 3390: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,

3386: x_periodicity IN NUMBER,
3387: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3388: x_num_key_columns IN NUMBER,
3389: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3390: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3391: x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,
3392: x_num_data_columns IN NUMBER,
3393: x_partition_name IN VARCHAR2,
3394: x_batch_value IN NUMBER,

Line 3391: x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,

3387: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3388: x_num_key_columns IN NUMBER,
3389: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3390: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3391: x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,
3392: x_num_data_columns IN NUMBER,
3393: x_partition_name IN VARCHAR2,
3394: x_batch_value IN NUMBER,
3395: x_parallel_jobs IN VARCHAR2,

Line 3402: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;

3398: ) IS
3399:
3400: h_sql VARCHAR2(32000);
3401: h_i NUMBER;
3402: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
3403: h_num_bind_vars NUMBER;
3404:
3405: BEGIN
3406: -- Insert higher periodicities

Line 3411: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

3407: h_bind_vars_values.delete;
3408: h_num_bind_vars := 0;
3409:
3410: h_sql := 'INSERT /*+ append';
3411: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
3412: h_sql := h_sql||' parallel ('||x_base_table||')';
3413: END IF;
3414: h_sql := h_sql||' */ INTO '||x_base_table;
3415: IF x_parallel_jobs = 'Y' THEN

Line 3428: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN

3424: h_sql := h_sql||', '||x_data_columns(h_i);
3425: END LOOP;
3426: h_sql := h_sql||')'||
3427: ' SELECT';
3428: IF BSC_UPDATE_UTIL.is_parallel AND x_parallel_jobs = 'N' THEN
3429: -- Fix bug#5155523 Do not use parallel hint on BSC_DB_CALENDAR_TEMP
3430: h_sql := h_sql||' /*+ parallel (BSC_B_DATA) */';
3431: END IF;
3432: h_sql := h_sql||' MAX(BSC_B_DATA.'||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||') '||

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

3462: h_sql := h_sql||'BSC_B_DATA.'||x_key_columns(h_i)||', ';
3463: END LOOP;
3464: h_sql := h_sql||'BSC_B_DATA.YEAR, BSC_B_DATA.TYPE, BSC_DB_CALENDAR_TEMP.UPPER_PERIOD,'||
3465: ' BSC_DB_CALENDAR_TEMP.PERIODICITY_ID';
3466: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
3467: COMMIT;
3468:
3469: x_return_status := 'success';
3470:

Line 3512: h_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2;

3508: e_get_info_data_columns EXCEPTION;
3509: e_get_info_key_columns EXCEPTION;
3510: e_unexpected_error EXCEPTION;
3511:
3512: h_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
3513: h_data_formulas BSC_UPDATE_UTIL.t_array_of_varchar2;
3514: h_data_proj_methods BSC_UPDATE_UTIL.t_array_of_number;
3515: h_data_measure_types BSC_UPDATE_UTIL.t_array_of_number;
3516: h_num_data_columns NUMBER;

Line 3513: h_data_formulas BSC_UPDATE_UTIL.t_array_of_varchar2;

3509: e_get_info_key_columns EXCEPTION;
3510: e_unexpected_error EXCEPTION;
3511:
3512: h_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
3513: h_data_formulas BSC_UPDATE_UTIL.t_array_of_varchar2;
3514: h_data_proj_methods BSC_UPDATE_UTIL.t_array_of_number;
3515: h_data_measure_types BSC_UPDATE_UTIL.t_array_of_number;
3516: h_num_data_columns NUMBER;
3517:

Line 3514: h_data_proj_methods BSC_UPDATE_UTIL.t_array_of_number;

3510: e_unexpected_error EXCEPTION;
3511:
3512: h_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
3513: h_data_formulas BSC_UPDATE_UTIL.t_array_of_varchar2;
3514: h_data_proj_methods BSC_UPDATE_UTIL.t_array_of_number;
3515: h_data_measure_types BSC_UPDATE_UTIL.t_array_of_number;
3516: h_num_data_columns NUMBER;
3517:
3518: h_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2;

Line 3515: h_data_measure_types BSC_UPDATE_UTIL.t_array_of_number;

3511:
3512: h_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
3513: h_data_formulas BSC_UPDATE_UTIL.t_array_of_varchar2;
3514: h_data_proj_methods BSC_UPDATE_UTIL.t_array_of_number;
3515: h_data_measure_types BSC_UPDATE_UTIL.t_array_of_number;
3516: h_num_data_columns NUMBER;
3517:
3518: h_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
3519: h_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;

Line 3518: h_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2;

3514: h_data_proj_methods BSC_UPDATE_UTIL.t_array_of_number;
3515: h_data_measure_types BSC_UPDATE_UTIL.t_array_of_number;
3516: h_num_data_columns NUMBER;
3517:
3518: h_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
3519: h_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
3520: h_source_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
3521: h_source_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
3522: h_num_key_columns NUMBER;

Line 3519: h_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;

3515: h_data_measure_types BSC_UPDATE_UTIL.t_array_of_number;
3516: h_num_data_columns NUMBER;
3517:
3518: h_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
3519: h_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
3520: h_source_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
3521: h_source_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
3522: h_num_key_columns NUMBER;
3523:

Line 3520: h_source_columns BSC_UPDATE_UTIL.t_array_of_varchar2;

3516: h_num_data_columns NUMBER;
3517:
3518: h_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
3519: h_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
3520: h_source_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
3521: h_source_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
3522: h_num_key_columns NUMBER;
3523:
3524: BEGIN

Line 3521: h_source_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;

3517:
3518: h_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
3519: h_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
3520: h_source_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
3521: h_source_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
3522: h_num_key_columns NUMBER;
3523:
3524: BEGIN
3525:

Line 3553: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

3549: -- ----------------------------------------------------------------------------------
3550: */
3551:
3552: h_sql := 'alter session set hash_area_size=50000000';
3553: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
3554:
3555: h_sql := 'alter session set sort_area_size=50000000';
3556: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
3557:

Line 3556: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

3552: h_sql := 'alter session set hash_area_size=50000000';
3553: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
3554:
3555: h_sql := 'alter session set sort_area_size=50000000';
3556: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
3557:
3558: IF BSC_UPDATE_UTIL.is_parallel THEN
3559: COMMIT;
3560: h_sql := 'alter session enable parallel dml';

Line 3558: IF BSC_UPDATE_UTIL.is_parallel THEN

3554:
3555: h_sql := 'alter session set sort_area_size=50000000';
3556: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
3557:
3558: IF BSC_UPDATE_UTIL.is_parallel THEN
3559: COMMIT;
3560: h_sql := 'alter session enable parallel dml';
3561: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
3562: COMMIT;

Line 3561: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

3557:
3558: IF BSC_UPDATE_UTIL.is_parallel THEN
3559: COMMIT;
3560: h_sql := 'alter session enable parallel dml';
3561: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
3562: COMMIT;
3563: END IF;
3564:
3565: -- Get data columns

Line 3566: IF NOT BSC_UPDATE_UTIL.Get_Information_Data_Columns(x_base_table,

3562: COMMIT;
3563: END IF;
3564:
3565: -- Get data columns
3566: IF NOT BSC_UPDATE_UTIL.Get_Information_Data_Columns(x_base_table,
3567: h_data_columns,
3568: h_data_formulas,
3569: h_data_proj_methods,
3570: h_data_measure_types,

Line 3576: IF NOT BSC_UPDATE_UTIL.Get_Information_Key_Columns(x_base_table,

3572: RAISE e_get_info_data_columns;
3573: END IF;
3574:
3575: -- Get key columns
3576: IF NOT BSC_UPDATE_UTIL.Get_Information_Key_Columns(x_base_table,
3577: h_key_columns,
3578: h_key_dim_tables,
3579: h_source_columns,
3580: h_source_dim_tables,

Line 3657: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,

3653: x_calendar_id IN NUMBER,
3654: x_current_fy IN NUMBER,
3655: x_current_period IN NUMBER,
3656: x_old_current_period IN NUMBER,
3657: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3658: x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3659: x_num_key_columns IN NUMBER,
3660: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3661: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,

Line 3658: x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,

3654: x_current_fy IN NUMBER,
3655: x_current_period IN NUMBER,
3656: x_old_current_period IN NUMBER,
3657: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3658: x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3659: x_num_key_columns IN NUMBER,
3660: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3661: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3662: x_data_proj_methods IN BSC_UPDATE_UTIL.t_array_of_number,

Line 3660: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,

3656: x_old_current_period IN NUMBER,
3657: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3658: x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3659: x_num_key_columns IN NUMBER,
3660: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3661: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3662: x_data_proj_methods IN BSC_UPDATE_UTIL.t_array_of_number,
3663: x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,
3664: x_num_data_columns IN NUMBER,

Line 3661: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,

3657: x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3658: x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3659: x_num_key_columns IN NUMBER,
3660: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3661: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3662: x_data_proj_methods IN BSC_UPDATE_UTIL.t_array_of_number,
3663: x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,
3664: x_num_data_columns IN NUMBER,
3665: x_proj_table IN VARCHAR2,

Line 3662: x_data_proj_methods IN BSC_UPDATE_UTIL.t_array_of_number,

3658: x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3659: x_num_key_columns IN NUMBER,
3660: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3661: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3662: x_data_proj_methods IN BSC_UPDATE_UTIL.t_array_of_number,
3663: x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,
3664: x_num_data_columns IN NUMBER,
3665: x_proj_table IN VARCHAR2,
3666: x_rowid_table IN VARCHAR2,

Line 3663: x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,

3659: x_num_key_columns IN NUMBER,
3660: x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3661: x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3662: x_data_proj_methods IN BSC_UPDATE_UTIL.t_array_of_number,
3663: x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,
3664: x_num_data_columns IN NUMBER,
3665: x_proj_table IN VARCHAR2,
3666: x_rowid_table IN VARCHAR2,
3667: x_partition_name IN VARCHAR2,

Line 3688: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;

3684: h_error_message VARCHAR2(2000);
3685: h_sql VARCHAR2(32000);
3686: TYPE t_cursor IS REF CURSOR;
3687: h_cursor t_cursor;
3688: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
3689: h_num_bind_vars NUMBER;
3690: h_num_rows NUMBER;
3691: h_projection_flag VARCHAR2(3);
3692: h_yearly_periodicity NUMBER;

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

3731: -- AW_INTEGRATION: PROJECTION flag is now set to Y for all the rows beyond
3732: -- current period no matter if it is target or actual. So if the current period changes
3733: -- we need to update to N between old current period and new current period for type <> 0
3734: IF x_aw_flag THEN
3735: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
3736: IF (h_yearly_flag <> 1) AND (x_current_period > x_old_current_period) THEN
3737: h_num_bind_vars := 0;
3738: h_bind_vars_values.delete;
3739:

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

3754: h_sql := h_sql||' AND '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = :6';
3755: h_num_bind_vars := h_num_bind_vars + 1;
3756: h_bind_vars_values(h_num_bind_vars) := x_batch_value;
3757: END IF;
3758: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
3759: commit;
3760: END IF;
3761: END IF;
3762:

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

3833: h_sql := h_sql||' AND '||BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||' = :2';
3834: h_num_bind_vars := h_num_bind_vars + 1;
3835: h_bind_vars_values(h_num_bind_vars) := x_batch_value;
3836: END IF;
3837: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
3838: COMMIT;
3839:
3840: -- Insert rows for higer periodicities
3841: Calc_Higher_Periodicities(x_base_table,