1 PACKAGE BODY BSC_UPDATE_BASE AS
2 /* $Header: BSCDBASB.pls 120.6 2006/02/16 09:08 meastmon noship $ */
3
4 --
5 -- Global Variables
6 --
7
8 /*===========================================================================+
9 | FUNCTION Calc_New_Period_Base_Table
10 +============================================================================*/
11 FUNCTION Calc_New_Period_Base_Table(
12 x_base_table IN VARCHAR2,
13 x_periodicity_base_table IN NUMBER,
14 x_periodicity_input_table IN NUMBER,
15 x_current_fy IN NUMBER,
16 x_per_input_table IN NUMBER,
17 x_subper_input_table IN NUMBER,
18 x_current_per_base_table OUT NOCOPY NUMBER,
19 x_per_base_table OUT NOCOPY NUMBER
20 ) RETURN BOOLEAN IS
21
22 TYPE t_cursor IS REF CURSOR;
23 h_cursor t_cursor;
24
25 h_sql VARCHAR2(32700);
26
27 h_current_period NUMBER;
28 h_calendar_id NUMBER;
29
30 h_base_calendar_col_name VARCHAR2(30);
31 h_input_calendar_col_name VARCHAR2(30);
32
33 h_yearly_flag NUMBER;
34 h_edw_flag NUMBER;
35
36 h_periodicity_type_base_table NUMBER;
37 h_periodicity_type_input_table NUMBER;
38
39 BEGIN
40
41 h_yearly_flag := 0;
42 h_edw_flag := 0;
43
44 -- Get the current period of the base table
45 BEGIN
46 SELECT NVL(current_period, 0)
47 INTO h_current_period
48 FROM bsc_db_tables
49 WHERE table_name = x_base_table;
50 EXCEPTION
51 WHEN NO_DATA_FOUND THEN
52 h_current_period := 0;
53 END;
54
55 -- Calculate the period of the base table based on
56 -- the period of the input table
57
58 IF x_periodicity_base_table = x_periodicity_input_table THEN
59 -- There is no periodicity change. The period of the base table is
60 -- the period of the input table.
61 x_per_base_table := x_per_input_table;
62 ELSE
63 -- BSC-BIS-DIMENSIONS Note: We know that never there is change of periodicity
64 -- between the input and the base table for BIS periodicities. So this code
65 -- is only for BSC periodicities.
66
67 -- There is periodicity change
68 -- Note: We suppose that the change of periodicity is allowed
69 -- (see bsc_sys_periodicites) plus:
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);
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
83 x_per_base_table := x_current_fy;
84
85 ELSIF h_periodicity_type_base_table = 7 AND h_periodicity_type_input_table = 11 THEN
86 -- The base table is Weekly52 and the input table is Month Week
87 -- This is the special case that use bsc_db_week_maps table to make the
88 -- transformation
89 BEGIN
90 SELECT week52
91 INTO x_per_base_table
92 FROM bsc_db_week_maps
93 WHERE year = x_current_fy AND month = x_per_input_table AND
94 week = x_subper_input_table AND calendar_id = h_calendar_id;
95 EXCEPTION
96 WHEN NO_DATA_FOUND THEN
97 x_per_base_table := 0;
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
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||' '||
113 'FROM bsc_db_calendar '||
114 'WHERE year = :1 '||
115 'AND month = :2 '||
116 'AND day30 = :3 '||
117 'AND calendar_id = :4';
118 OPEN h_cursor FOR h_sql USING x_current_fy, x_per_input_table, x_subper_input_table, h_calendar_id;
119 FETCH h_cursor INTO x_per_base_table;
120 IF h_cursor%NOTFOUND THEN
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 '||
130 'AND calendar_id = :3';
131 OPEN h_cursor FOR h_sql USING x_current_fy, x_per_input_table, h_calendar_id;
132 FETCH h_cursor INTO x_per_base_table;
133 IF h_cursor%NOTFOUND THEN
134 x_per_base_table := 0;
135 END IF;
136 CLOSE h_cursor;
137 END IF;
138 ELSE
139 -- EDW periodicity
140 -- Use BSC_EDW_TIME_MAP table which was previously created for
141 -- x_periodicity_input_table --> x_periodicity_base_table
142 h_sql := 'SELECT MAX(bsc_target) '||
143 'FROM bsc_edw_time_map '||
144 'WHERE year = :1 '||
145 'AND bsc_source = :2';
146 OPEN h_cursor FOR h_sql USING x_current_fy, x_per_input_table;
147 FETCH h_cursor INTO x_per_base_table;
148 IF h_cursor%NOTFOUND THEN
149 x_per_base_table := 0;
150 END IF;
151 CLOSE h_cursor;
152 END IF;
153 END IF;
154 END IF;
155
156 -- The update period of the base table is the maximun between the current
157 -- period and the period calculated from the input table,
158
159 IF h_current_period > x_per_base_table THEN
160 x_per_base_table := h_current_period;
161 END IF;
162
163 x_current_per_base_table := h_current_period;
164
165 RETURN TRUE;
166
167 EXCEPTION
168 WHEN OTHERS THEN
169 BSC_MESSAGE.Add(x_message => SQLERRM,
170 x_source => 'BSC_UPDATE_BASE.Calc_New_Period_Base_Table');
171 RETURN FALSE;
172
173 END Calc_New_Period_Base_Table;
174
175
176 /*===========================================================================+
177 | FUNCTION Calc_New_Period_Input_Table
178 +============================================================================*/
179 FUNCTION Calc_New_Period_Input_Table(
180 x_input_table IN VARCHAR2,
181 x_periodicity IN NUMBER,
182 x_period_col_name IN VARCHAR2,
183 x_subperiod_col_name IN VARCHAR2,
184 x_current_fy IN NUMBER,
185 x_period OUT NOCOPY NUMBER,
186 x_subperiod OUT NOCOPY NUMBER
187 ) RETURN BOOLEAN IS
188
189 TYPE t_cursor IS REF CURSOR;
190 h_cursor t_cursor;
191
192 h_sql VARCHAR2(32700);
193
194 h_current_period NUMBER;
195 h_current_subperiod NUMBER;
196
197 h_reported_period NUMBER;
198 h_reported_subperiod NUMBER;
199
200 h_yearly_flag NUMBER;
201
202 h_target_flag NUMBER;
203
204 h_calendar_id NUMBER;
205 h_calendar_source VARCHAR2(20);
206
207 h_periodicity_type NUMBER;
208
209 BEGIN
210
211 h_reported_period := 0;
212 h_reported_subperiod := 0;
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);
222
223 -- Get the current period and subperiod of the input table
224 BEGIN
225 SELECT NVL(current_period, 0), NVL(current_subperiod, 0)
226 INTO h_current_period, h_current_subperiod
227 FROM bsc_db_tables
228 WHERE table_name = x_input_table;
229 EXCEPTION
230 WHEN NO_DATA_FOUND THEN
231 h_current_period := 0;
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
241 -- fiscal year
242 h_reported_period := x_current_fy;
243 ELSE -- Other periodicities
244 IF h_calendar_source = 'BSC' THEN
245 h_sql := 'SELECT MAX('||x_period_col_name||') '||
246 'FROM '||x_input_table||' '||
247 'WHERE year = :1';
248
249 IF h_target_flag = 1 THEN
250 -- The input tables is used only for targets only
251 -- No condition on TYPE to get the update period of the input table.
252 OPEN h_cursor FOR h_sql USING x_current_fy;
253 ELSE
254 -- The input table is for fact and target data
255 -- The update period is calculated based on fact data only.
256 h_sql := h_sql||' AND type = :2';
257 OPEN h_cursor FOR h_sql USING x_current_fy, 0;
258 END IF;
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)'||
268 ' FROM '||x_input_table||' i, bsc_sys_periods p'||
269 ' WHERE p.periodicity_id = :1 AND TRUNC(i.time_fk) = TRUNC(TO_DATE(p.time_fk, ''MM/DD/YYYY''))'||
270 ' AND p.year =:2';
271 ELSE
272 -- Other periodicity. TIME_FK is VARCHAR2
273 h_sql := 'SELECT MAX(p.period_id)'||
274 ' FROM '||x_input_table||' i, bsc_sys_periods p'||
275 ' WHERE p.periodicity_id = :1 AND i.time_fk = p.time_fk AND p.year =:2';
276 END IF;
277
278 IF h_target_flag = 1 THEN
279 -- The input tables is used only for targets only
280 -- No condition on TYPE to get the update period of the input table.
281 OPEN h_cursor FOR h_sql USING x_periodicity, x_current_fy;
282 ELSE
283 -- The input table is for fact and target data
284 -- The update period is calculated based on fact data only.
285 h_sql := h_sql||' AND type = :3';
286 OPEN h_cursor FOR h_sql USING x_periodicity, x_current_fy, 0;
287 END IF;
288 END IF;
289
290 FETCH h_cursor INTO h_reported_period;
291 IF h_cursor%FOUND THEN
292 IF h_reported_period IS NULL THEN
293 h_reported_period := 0;
294 END IF;
295 ELSE
296 h_reported_period := 0;
297 END IF;
298 CLOSE h_cursor;
299
300 END IF;
301
302 -- Get the maximun sub-period of real data reported in the input table
303 -- BSC-BIS-DIMENSIONS: Input table never has sub-period for BIS periodicities
304 h_reported_subperiod := 0;
305 IF h_calendar_source = 'BSC' THEN
306 IF x_subperiod_col_name IS NULL THEN
307 h_reported_subperiod := 0;
308 ELSE
309 h_sql := 'SELECT MAX('||x_subperiod_col_name||') '||
310 'FROM '||x_input_table||' '||
311 'WHERE year = :1 '||
312 'AND '||x_period_col_name||' = :2';
313 IF h_target_flag = 1 THEN
314 -- The input tables is used only for targets only
315 -- No condition on TYPE to get the update period of the input table.
316 OPEN h_cursor FOR h_sql USING x_current_fy, h_reported_period;
317 ELSE
318 -- The input table is for fact and target data
319 -- The update period is calculated based on fact data only.
320 h_sql := h_sql||'AND type = :3';
321 OPEN h_cursor FOR h_sql USING x_current_fy, h_reported_period, 0;
322 END IF;
323
324 FETCH h_cursor INTO h_reported_subperiod;
325 IF h_cursor%FOUND THEN
326 IF h_reported_subperiod IS NULL THEN
327 h_reported_subperiod := 0;
328 END IF;
329 ELSE
330 h_reported_subperiod := 0;
331 END IF;
332 CLOSE h_cursor;
333 END IF;
334 END IF;
335
336 -- Assign the new update period and sub-period
337 IF h_current_period > h_reported_period THEN
338 x_period := h_current_period;
339 x_subperiod := h_current_subperiod;
340 ELSIF h_current_period < h_reported_period THEN
341 x_period := h_reported_period;
342 x_subperiod := h_reported_subperiod;
343 ELSE
344 x_period := h_current_period;
345 IF h_current_subperiod > h_reported_subperiod THEN
346 x_subperiod := h_current_subperiod;
347 ELSE
348 x_subperiod := h_reported_subperiod;
349 END IF;
350 END IF;
351
352 RETURN TRUE;
353
354 EXCEPTION
355 WHEN OTHERS THEN
356 BSC_MESSAGE.Add(x_message => SQLERRM,
357 x_source => 'BSC_UPDATE_BASE.Calc_New_Period_Input_Table');
358 RETURN FALSE;
359
360 END Calc_New_Period_Input_Table;
361
362
363 /*===========================================================================+
364 | FUNCTION Calculate_Base_Table
365 +============================================================================*/
366 FUNCTION Calculate_Base_Table(
367 x_base_table IN VARCHAR2,
368 x_input_table IN VARCHAR2,
369 x_correction_flag IN BOOLEAN,
370 x_aw_flag IN BOOLEAN
371 ) RETURN BOOLEAN IS
372
373 e_unexpected_error EXCEPTION;
374 h_b BOOLEAN;
375 h_sql VARCHAR2(32700);
376
377 -- Current fiscal year
378 h_current_fy NUMBER;
379
380 -- Table periodicities
381 h_periodicity_input_table NUMBER;
382 h_periodicity_base_table NUMBER;
383
384 -- Period and subperiod column name of input table
385 h_period_col_name VARCHAR2(15);
386 h_subperiod_col_name VARCHAR2(15);
387
388 -- Update period and subperiod of the tables
389 h_per_input_table NUMBER;
390 h_subper_input_table NUMBER;
391 h_current_per_base_table NUMBER;
392 h_per_base_table NUMBER;
393
394 -- Generation type (1: total or balance columns)
395 h_generation_type NUMBER;
396
397 -- Projection flag
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;
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
416 -- Number of year and previous years of the table
417 h_num_of_years NUMBER;
418 h_previous_years NUMBER;
419
420 -- Zero code calcualtion method
421 h_zero_code_calc_method NUMBER;
422
423 -- Calendar id used by the input/base table
424 h_calendar_id NUMBER;
425
426 h_start_year NUMBER;
427 h_end_year NUMBER;
428
429 CURSOR c_other_periodicities (p_table_name VARCHAR2, p_calc_type NUMBER) IS
430 SELECT c.parameter1, p.yearly_flag
431 FROM bsc_db_calculations c, bsc_sys_periodicities p
432 WHERE c.table_name = p_table_name AND c.calculation_type = p_calc_type AND
433 c.parameter1 = p.periodicity_id;
434
435 h_other_periodicity_id NUMBER;
436 h_yearly_flag NUMBER;
437 h_current_period NUMBER;
438
439 TYPE t_periodicity IS RECORD (
440 periodicity_id NUMBER,
441 yearly_flag NUMBER,
442 current_period NUMBER,
443 new_current_period NUMBER
444 );
445
446 TYPE t_array_periodicities IS TABLE OF t_periodicity
447 INDEX BY BINARY_INTEGER;
448
449 h_arr_other_periodicities t_array_periodicities;
450 h_num_other_periodicities NUMBER;
451 h_i 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
461
462 h_projection_flag := 'NO';
463 h_num_data_columns := 0;
464 h_num_key_columns := 0;
465 h_lst_key_columns := NULL;
466 h_num_other_periodicities := 0;
467
468 --AW_INTEGRATION: init change vector value
469 IF x_aw_flag THEN
470 bsc_aw_load.init_bt_change_vector(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;
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;
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
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
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
505 -- on EDW tables
506 -- I have deleted that code. EDW is not supported
507
508 -- Get the new period and subperiod of the input table
509 h_per_input_table := 0;
510 h_subper_input_table := 0;
511 -- If the base table is being re-calculated for incremental changes
512 -- then we do not consider the input table to calcualte new period of the base table.
513 IF NOT x_correction_flag THEN
514 IF NOT Calc_New_Period_Input_Table(x_input_table,
515 h_periodicity_input_table,
516 h_period_col_name,
517 h_subperiod_col_name,
518 h_current_fy,
519 h_per_input_table,
520 h_subper_input_table) THEN
521 RAISE e_unexpected_error;
522 END IF;
523 END IF;
524
525 -- Get the new period of the base table
526 IF NOT Calc_New_Period_Base_Table(x_base_table,
527 h_periodicity_base_table,
528 h_periodicity_input_table,
529 h_current_fy,
530 h_per_input_table,
531 h_subper_input_table,
532 h_current_per_base_table,
533 h_per_base_table) THEN
534 RAISE e_unexpected_error;
535 END IF;
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;
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,
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,
560 h_num_key_columns) THEN
561 RAISE e_unexpected_error;
562 END IF;
563
564
565 -- Create temporary tables used for calculation and tranformations
566 -- Projection
567 h_b := BSC_UPDATE_CALC.Table_Has_Proj_Calc(x_base_table);
568 IF h_b IS NULL THEN
569 RAISE e_unexpected_error;
570 END IF;
571
572 IF h_b THEN
573 h_projection_flag := 'YES';
574 ELSE
575 h_projection_flag := 'NO';
576 END IF;
577
578 -- No temporary tables for code zero calculation.
579
580 COMMIT;
581
582
583 --- BSC-MV Note: Get info other periodicities
584 -- AW_INTEGRATION: If the base table is for AW there are not higher periodicities
585 -- in the base table, so we do not need info for other periodicities.
586 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
587 OPEN c_other_periodicities(x_base_table, 6);
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,
597 h_periodicity_base_table,
598 h_current_per_base_table
599 );
600 h_num_other_periodicities := h_num_other_periodicities + 1;
601 h_arr_other_periodicities(h_num_other_periodicities).periodicity_id := h_other_periodicity_id;
602 h_arr_other_periodicities(h_num_other_periodicities).yearly_flag := h_yearly_flag;
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,
612 h_periodicity_base_table,
613 h_per_base_table
614 );
615 h_arr_other_periodicities(h_num_other_periodicities).new_current_period := h_current_period;
616
617 END LOOP;
618 CLOSE c_other_periodicities;
619 END IF;
620
621
622 -- Delete the current projection in the base table
623
624 -- With the optimization of the projection method we are going to delete projection (set NULL)
625 -- only the records for periods >current_period and <=new_current_period.
626
627 SELECT project_flag INTO h_project_flag
628 FROM bsc_db_tables
629 WHERE table_name = x_base_table;
630
631 IF h_project_flag = 1 THEN
632 -- Delete the projection from all the data columns in the table
633 --AW_INTEGRATION: Pass x_aw_flag and h_change_vector_value to Delete_Projection_Base_Table
634 IF NOT BSC_UPDATE_CALC.Delete_Projection_Base_Table(x_base_table,
635 h_periodicity_base_table,
636 h_current_per_base_table,
637 h_per_base_table,
638 h_data_columns,
639 h_data_proj_methods,
640 h_num_data_columns,
641 h_current_fy,
642 x_aw_flag,
643 h_change_vector_value) THEN
644 RAISE e_unexpected_error;
645 END IF;
646 commit;
647
648 -- BSC-MV Note: For this architecture we need to delete the projection
649 -- from all other periodicities stored in the base table.
650 -- AW_INTEGRATION: If the base table is for AW then there are not higher periodicities
651 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
652 FOR h_i IN 1..h_num_other_periodicities LOOP
653 --AW_INTEGRATION: Pass x_aw_flag to Delete_Projection_Base_Table and h_change_vector_value
654 IF NOT BSC_UPDATE_CALC.Delete_Projection_Base_Table(x_base_table,
655 h_arr_other_periodicities(h_i).periodicity_id,
656 h_arr_other_periodicities(h_i).current_period,
657 h_arr_other_periodicities(h_i).new_current_period,
658 h_data_columns,
659 h_data_proj_methods,
660 h_num_data_columns,
661 h_current_fy,
662 x_aw_flag,
663 h_change_vector_value) THEN
664 RAISE e_unexpected_error;
665 END IF;
666 commit;
667 END LOOP;
668 END IF;
669 END IF;
670 commit;
671
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
681 h_sql := 'UPDATE '||x_base_table||
682 ' SET projection = ''N'', change_vector = :1'||
683 ' WHERE YEAR = :2 AND PERIOD > :3 AND PERIOD <= :4 AND TYPE <> :5';
684 h_bind_vars_values(1) := h_change_vector_value;
685 h_bind_vars_values(2) := h_current_fy;
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
695 -- Update base table from the input data in the corresponding input table.
696 -- This step performs the bulk upload of input data into the base table.
697
698 --Fix bug#4235448 : Need to pass current current period of the base table h_current_per_base_table
699 --AW_INTEGRATION: pass x_aw_flag and h_change_vector_value
700 IF NOT Update_Base_Table(x_base_table,
701 x_input_table,
702 h_key_columns,
703 h_key_dim_tables,
704 h_num_key_columns,
705 h_data_columns,
706 h_data_formulas,
707 h_data_measure_types,
708 h_num_data_columns,
709 h_periodicity_base_table,
710 h_periodicity_input_table,
711 h_period_col_name,
712 h_subperiod_col_name,
713 h_projection_flag,
714 h_current_fy,
715 h_per_base_table,
716 h_current_per_base_table,
717 x_correction_flag,
718 x_aw_flag,
719 h_change_vector_value
720 ) THEN
721 RAISE e_unexpected_error;
722 END IF;
723
724 COMMIT;
725
726 -- A base table is never used direclty by an indicator.
727 -- So, there is no need to:
728 -- Refresh any EDW materialized view.
729 -- Calculate filters.
730 -- Merge benchmarks from another table
731 -- Calculate profit.
732 -- By design there is no zero code calculation on base tables
733
734 -- Calculate projection
735 IF h_projection_flag = 'YES' THEN
736 --ENH_PROJECTION_4235711: pass TRUE to x_trunc_proj_table parameter
737 IF NOT BSC_UPDATE_CALC.Create_Proj_Temps(h_periodicity_base_table,
738 h_current_fy,
739 h_num_of_years,
740 h_previous_years,
741 TRUE) THEN
742 RAISE e_unexpected_error;
743 END IF;
744
745 -- AW_INTEGRATION: Pass x_aw_flag and h_change_vector_value to Calculate_Projection
746 IF NOT BSC_UPDATE_CALC.Calculate_Projection(x_base_table,
747 h_periodicity_base_table,
748 h_per_base_table,
749 h_key_columns,
750 h_num_key_columns,
751 h_data_columns,
752 h_data_proj_methods,
753 h_num_data_columns,
754 h_current_fy,
755 h_num_of_years,
756 h_previous_years,
757 TRUE,
758 x_aw_flag,
759 h_change_vector_value) THEN
760 RAISE e_unexpected_error;
761 END IF;
762 COMMIT;
763
764 -- AW_INTEGRATION: If x_correction_flag is TRUE we need to set change_vector for the whole base table
765 IF x_aw_flag AND x_correction_flag THEN
766 h_sql := 'UPDATE '||x_base_table||' SET change_vector = :1';
767 execute immediate h_sql using h_change_vector_value;
768 commit;
769 END IF;
770
771 -- BSC-MV Note: For this architecture we need to calculate projection
772 -- from all other periodicities stored in the base table.
773 -- AW_INTEGRATION: If the base table is for AW there are no higher peridicities
774
775 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
776
777 -- For other periodicities, the projection is calculated by rolling up
778 -- the projection in the base periodicity.
779 -- We know that for Yearly periodicity we need to re-calculate projection.
780
781 FOR h_i IN 1..h_num_other_periodicities LOOP
782 --ENH_PROJECTION_4235711: pass FALSE to x_trunc_proj_table parameter
783 IF NOT BSC_UPDATE_CALC.Create_Proj_Temps(h_arr_other_periodicities(h_i).periodicity_id,
784 h_current_fy,
785 h_num_of_years,
786 h_previous_years,
787 FALSE) THEN
788 RAISE e_unexpected_error;
789 END IF;
790
791 IF h_arr_other_periodicities(h_i).yearly_flag = 1 THEN
792 -- We know that for yearly periodicity, we need to re-calculate
793 -- projection
794 -- AW_INTEGRATION: Pass x_aw_flag and change_vector value to Calculate_Projection
795 IF NOT BSC_UPDATE_CALC.Calculate_Projection(
796 x_base_table,
797 h_arr_other_periodicities(h_i).periodicity_id,
798 h_arr_other_periodicities(h_i).new_current_period,
799 h_key_columns,
800 h_num_key_columns,
801 h_data_columns,
802 h_data_proj_methods,
803 h_num_data_columns,
804 h_current_fy,
805 h_num_of_years,
806 h_previous_years,
807 TRUE,
808 x_aw_flag,
809 NULL) THEN
810 RAISE e_unexpected_error;
811 END IF;
812 COMMIT;
813 ELSE
814 -- For other periodicites, the projection is calculated by rolling up the projection
815 -- already calculated for the base periodicity.
816 -- ENH_PROJECTION_4235711: no need to pass table name
817 IF NOT BSC_UPDATE_CALC.Rollup_Projection(
818 h_arr_other_periodicities(h_i).periodicity_id,
819 h_arr_other_periodicities(h_i).new_current_period,
820 h_periodicity_base_table,
821 h_per_base_table,
822 h_key_columns,
823 h_num_key_columns,
824 h_data_columns,
825 h_data_formulas,
826 h_data_measure_types,
827 h_num_data_columns,
828 h_current_fy,
829 TRUE) THEN
830 RAISE e_unexpected_error;
831 END IF;
832 COMMIT;
833 END IF;
834 END LOOP;
835
836 --ENH_PROJECTION_4235711: Projection for all the periodicities is already calculated in
837 -- BSC_TMP_PROC_CALC table. We can now merge the projection into the base table
838 IF NOT BSC_UPDATE_CALC.Merge_Projection(x_base_table,
839 h_key_columns,
840 h_num_key_columns,
841 h_data_columns,
842 h_num_data_columns,
843 TRUE,
844 x_aw_flag) THEN
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
854 -- Calculate Profit
855 -- BSC-MV Note: Only in this architecture the profit is calculated in the base table
856 -- AW_INTEGRATION: Profit needs to be calculated in this architecture too
857 IF BSC_APPS.bsc_mv THEN
858 h_b := BSC_UPDATE_CALC.Table_Has_Profit_Calc(x_base_table);
859 IF h_b IS NULL THEN
860 RAISE e_unexpected_error;
861 END IF;
862
863 IF h_b THEN
864 -- AW_INTEGRATION: Pass x_aw_flag and change vector value to Calculate_Profit
865 IF NOT BSC_UPDATE_CALC.Calculate_Profit(x_base_table,
866 h_key_columns,
867 h_key_dim_tables,
868 h_num_key_columns,
869 h_data_columns,
870 h_num_data_columns,
871 x_aw_flag,
872 h_change_vector_value
873 ) THEN
874 RAISE e_unexpected_error;
875 END IF;
876 COMMIT;
877 END IF;
878 END IF;
879
880 -- Store the update period of input table and base table
881 -- BSC-MV Note: If the base table is being re-calculated for incremental changes
882 -- we do not need to update the current period of the table.
883 -- Also we do not need to deelte data from input table
884
885 --AW_INTEGRATION: update change vector value in aw metadata
886 IF x_aw_flag THEN
887 bsc_aw_load.update_bt_change_vector(x_base_table, h_change_vector_value);
888 commit;
889 END IF;
890
891 IF NOT x_correction_flag THEN
892 UPDATE
893 bsc_db_tables
894 SET
895 current_period = h_per_input_table,
896 current_subperiod = h_subper_input_table
897 WHERE
898 table_name = x_input_table;
899
900 UPDATE
901 bsc_db_tables
902 SET
903 current_period = h_per_base_table
904 WHERE
905 table_name = x_base_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
915 --Fix bug#4962928: add this call
916 IF x_aw_flag THEN
917 BSC_AW_LOAD.update_bt_current_period(x_base_table, h_per_base_table, h_current_fy);
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
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
935 ROLLBACK;
936 BSC_MESSAGE.Add(x_message => SQLERRM,
937 x_source => 'BSC_UPDATE_BASE.Calculate_Base_Table');
938 RETURN FALSE;
939
940 END Calculate_Base_Table;
941
942
943 --LOCKING: new function
944 /*===========================================================================+
945 | FUNCTION Calculate_Base_Table_AT
946 +============================================================================*/
947 FUNCTION Calculate_Base_Table_AT(
948 x_base_table IN VARCHAR2,
949 x_input_table IN VARCHAR2,
950 x_correction_flag IN BOOLEAN,
951 x_aw_flag IN BOOLEAN
952 ) RETURN BOOLEAN IS
953 PRAGMA AUTONOMOUS_TRANSACTION;
954 h_b BOOLEAN;
955 BEGIN
956 h_b := Calculate_Base_Table(x_base_table, x_input_table, x_correction_flag, x_aw_flag);
957 commit; -- all autonomous transaction needs to commit
958 RETURN h_b;
959 END Calculate_Base_Table_AT;
960
961
962 /*===========================================================================+
963 | FUNCTION Create_Generic_Temp_Tables |
964 +============================================================================*/
965 FUNCTION Create_Generic_Temp_Tables RETURN BOOLEAN IS
966
967 e_unexpected_error EXCEPTION;
968
969 h_num_key_columns NUMBER;
970 h_num_keys_for_index NUMBER;
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
980 h_num_key_columns := 100;
981 h_num_data_columns := 300;
982 h_num_keys_for_index := 8;
983
984 -- BSC-BIS-DIMENSIONS: In order to support NUMBER or VARCHAR2 in key columns
985 -- we need to create these temporary tables with VARCHAR2 in the key columns.
986
987 -- BSC_TMP_BASE:
988 -- Structure <KEY1...KEYN YEAR TYPE PERIOD DATA1...DATAN>
989 --Bug#3875046: Do not create index on temporary tables
990 h_table_name := 'BSC_TMP_BASE';
991 h_table_columns.delete;
992 h_num_columns := 0;
993 FOR h_i IN 1..h_num_key_columns LOOP
994 h_num_columns := h_num_columns + 1;
995 h_table_columns(h_num_columns).column_name := 'KEY'||h_i;
996 h_table_columns(h_num_columns).data_type := 'VARCHAR2';
997 h_table_columns(h_num_columns).data_size := 400;
998 h_table_columns(h_num_columns).add_to_index := 'N';
999 END LOOP;
1000 IF BSC_APPS.bsc_mv THEN
1001 h_num_columns := h_num_columns + 1;
1002 h_table_columns(h_num_columns).column_name := 'PERIODICITY_ID';
1003 h_table_columns(h_num_columns).data_type := 'NUMBER';
1004 h_table_columns(h_num_columns).data_size := NULL;
1005 h_table_columns(h_num_columns).add_to_index := 'N';
1006 --ENH_PROJECTION_4235711: need this column in BSC_TMP_PROJ_CALC.
1007 --It it not needed in other tables but it is OK
1008 h_num_columns := h_num_columns + 1;
1009 h_table_columns(h_num_columns).column_name := 'PERIOD_TYPE_ID';
1010 h_table_columns(h_num_columns).data_type := 'NUMBER';
1011 h_table_columns(h_num_columns).data_size := NULL;
1012 h_table_columns(h_num_columns).add_to_index := 'N';
1013 END IF;
1014 h_num_columns := h_num_columns + 1;
1015 h_table_columns(h_num_columns).column_name := 'YEAR';
1016 h_table_columns(h_num_columns).data_type := 'NUMBER';
1017 h_table_columns(h_num_columns).data_size := 5;
1018 h_table_columns(h_num_columns).add_to_index := 'N';
1019 h_num_columns := h_num_columns + 1;
1020 h_table_columns(h_num_columns).column_name := 'TYPE';
1021 h_table_columns(h_num_columns).data_type := 'NUMBER';
1022 h_table_columns(h_num_columns).data_size := 3;
1023 h_table_columns(h_num_columns).add_to_index := 'N';
1024 h_num_columns := h_num_columns + 1;
1025 h_table_columns(h_num_columns).column_name := 'PERIOD';
1026 h_table_columns(h_num_columns).data_type := 'NUMBER';
1027 h_table_columns(h_num_columns).data_size := 5;
1028 h_table_columns(h_num_columns).add_to_index := 'N';
1029 FOR h_i IN 1..h_num_data_columns LOOP
1030 h_num_columns := h_num_columns + 1;
1031 h_table_columns(h_num_columns).column_name := 'DATA'||h_i;
1032 h_table_columns(h_num_columns).data_type := 'NUMBER';
1033 h_table_columns(h_num_columns).data_size := NULL;
1034 h_table_columns(h_num_columns).add_to_index := 'N';
1035 END LOOP;
1036 -- AW_INTEGRATION: bsc_tmp_base needs an additional column called PROJECTION VARCHAR2(60)
1037 -- and CHANGE_VECTOR NUMBER
1038 h_num_columns := h_num_columns + 1;
1039 h_table_columns(h_num_columns).column_name := 'PROJECTION';
1040 h_table_columns(h_num_columns).data_type := 'VARCHAR2';
1041 h_table_columns(h_num_columns).data_size := 60;
1042 h_table_columns(h_num_columns).add_to_index := 'N';
1043 h_num_columns := h_num_columns + 1;
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
1053 ---------------venu--------------------------------------------
1054 --because we aggregate the data to higher periodicities, we need to
1055 --capture the before update signature from the base table and then
1056 --we use this to subtract from the base table
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--------------------------------------------
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;
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
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
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
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
1106 --Bug#3875046: Do not create index on temporary tables
1107 h_table_name := 'BSC_TMP_PER_CHANGE';
1108 h_table_columns.delete;
1109 h_num_columns := 0;
1110 h_num_columns := h_num_columns + 1;
1111 h_table_columns(h_num_columns).column_name := 'YEAR';
1112 h_table_columns(h_num_columns).data_type := 'NUMBER';
1113 h_table_columns(h_num_columns).data_size := 5;
1114 h_table_columns(h_num_columns).add_to_index := 'N';
1115 h_num_columns := h_num_columns + 1;
1116 h_table_columns(h_num_columns).column_name := 'SRC_PER';
1117 h_table_columns(h_num_columns).data_type := 'NUMBER';
1118 h_table_columns(h_num_columns).data_size := 5;
1119 h_table_columns(h_num_columns).add_to_index := 'N';
1120 h_num_columns := h_num_columns + 1;
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)
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
1137 h_table_name := 'BSC_TMP_ALL_PERIODS';
1138 h_table_columns.delete;
1139 h_num_columns := 0;
1140 h_num_columns := h_num_columns + 1;
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
1150 --Bug#3875046: Do not create index on temporary tables
1151 h_table_name := 'BSC_TMP_DISAG_ALL_PERIODS';
1152 h_table_columns.delete;
1153 h_num_columns := 0;
1154 FOR h_i IN 1..h_num_key_columns LOOP
1155 h_num_columns := h_num_columns + 1;
1156 h_table_columns(h_num_columns).column_name := 'KEY'||h_i;
1157 h_table_columns(h_num_columns).data_type := 'VARCHAR2';
1158 h_table_columns(h_num_columns).data_size := 400;
1159 h_table_columns(h_num_columns).add_to_index := 'N';
1160 END LOOP;
1161 IF BSC_APPS.bsc_mv THEN
1162 h_num_columns := h_num_columns + 1;
1163 h_table_columns(h_num_columns).column_name := 'PERIODICITY_ID';
1164 h_table_columns(h_num_columns).data_type := 'NUMBER';
1165 h_table_columns(h_num_columns).data_size := NULL;
1166 h_table_columns(h_num_columns).add_to_index := 'N';
1167 h_num_columns := h_num_columns + 1;
1168 h_table_columns(h_num_columns).column_name := 'PERIOD_TYPE_ID';
1169 h_table_columns(h_num_columns).data_type := 'NUMBER';
1170 h_table_columns(h_num_columns).data_size := NULL;
1171 h_table_columns(h_num_columns).add_to_index := 'N';
1172 END IF;
1173 h_num_columns := h_num_columns + 1;
1174 h_table_columns(h_num_columns).column_name := 'YEAR';
1175 h_table_columns(h_num_columns).data_type := 'NUMBER';
1176 h_table_columns(h_num_columns).data_size := 5;
1177 h_table_columns(h_num_columns).add_to_index := 'N';
1178 h_num_columns := h_num_columns + 1;
1179 h_table_columns(h_num_columns).column_name := 'TYPE';
1180 h_table_columns(h_num_columns).data_type := 'NUMBER';
1181 h_table_columns(h_num_columns).data_size := 3;
1182 h_table_columns(h_num_columns).add_to_index := 'N';
1183 h_num_columns := h_num_columns + 1;
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
1193 --Bug#3875046: Do not create index on temporary tables
1194 h_table_name := 'BSC_TMP_TOT_PLAN';
1195 h_table_columns.delete;
1196 h_num_columns := 0;
1197 FOR h_i IN 1..h_num_key_columns LOOP
1198 h_num_columns := h_num_columns + 1;
1199 h_table_columns(h_num_columns).column_name := 'KEY'||h_i;
1200 h_table_columns(h_num_columns).data_type := 'VARCHAR2';
1201 h_table_columns(h_num_columns).data_size := 400;
1202 h_table_columns(h_num_columns).add_to_index := 'N';
1203 END LOOP;
1204 h_num_columns := h_num_columns + 1;
1205 h_table_columns(h_num_columns).column_name := 'TYPE';
1206 h_table_columns(h_num_columns).data_type := 'NUMBER';
1207 h_table_columns(h_num_columns).data_size := 3;
1208 h_table_columns(h_num_columns).add_to_index := 'N';
1209 FOR h_i IN 1..h_num_data_columns LOOP
1210 h_num_columns := h_num_columns + 1;
1211 h_table_columns(h_num_columns).column_name := 'TOTPLAN'||h_i;
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
1221 --Bug#3875046: Do not create index on temporary tables
1222 h_table_name := 'BSC_TMP_TOT_REAL';
1223 h_table_columns.delete;
1224 h_num_columns := 0;
1225 FOR h_i IN 1..h_num_key_columns LOOP
1226 h_num_columns := h_num_columns + 1;
1227 h_table_columns(h_num_columns).column_name := 'KEY'||h_i;
1228 h_table_columns(h_num_columns).data_type := 'VARCHAR2';
1229 h_table_columns(h_num_columns).data_size := 400;
1230 h_table_columns(h_num_columns).add_to_index := 'N';
1231 END LOOP;
1232 h_num_columns := h_num_columns + 1;
1233 h_table_columns(h_num_columns).column_name := 'TYPE';
1234 h_table_columns(h_num_columns).data_type := 'NUMBER';
1235 h_table_columns(h_num_columns).data_size := 3;
1236 h_table_columns(h_num_columns).add_to_index := 'N';
1237 FOR h_i IN 1..h_num_data_columns LOOP
1238 h_num_columns := h_num_columns + 1;
1239 h_table_columns(h_num_columns).column_name := 'TOTREAL'||h_i;
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
1249 --Bug#3875046: Do not create index on temporary tables
1250 h_table_name := 'BSC_TMP_PLAN_PROJECTIONS';
1251 h_table_columns.delete;
1252 h_num_columns := 0;
1253 FOR h_i IN 1..h_num_key_columns LOOP
1254 h_num_columns := h_num_columns + 1;
1255 h_table_columns(h_num_columns).column_name := 'KEY'||h_i;
1256 h_table_columns(h_num_columns).data_type := 'VARCHAR2';
1257 h_table_columns(h_num_columns).data_size := 400;
1258 h_table_columns(h_num_columns).add_to_index := 'N';
1259 END LOOP;
1260 h_num_columns := h_num_columns + 1;
1261 h_table_columns(h_num_columns).column_name := 'YEAR';
1262 h_table_columns(h_num_columns).data_type := 'NUMBER';
1263 h_table_columns(h_num_columns).data_size := 5;
1264 h_table_columns(h_num_columns).add_to_index := 'N';
1265 h_num_columns := h_num_columns + 1;
1266 h_table_columns(h_num_columns).column_name := 'TYPE';
1267 h_table_columns(h_num_columns).data_type := 'NUMBER';
1268 h_table_columns(h_num_columns).data_size := 3;
1269 h_table_columns(h_num_columns).add_to_index := 'N';
1270 h_num_columns := h_num_columns + 1;
1271 h_table_columns(h_num_columns).column_name := 'PERIOD';
1272 h_table_columns(h_num_columns).data_type := 'NUMBER';
1273 h_table_columns(h_num_columns).data_size := 5;
1274 h_table_columns(h_num_columns).add_to_index := 'N';
1275 FOR h_i IN 1..h_num_data_columns LOOP
1276 h_num_columns := h_num_columns + 1;
1277 h_table_columns(h_num_columns).column_name := 'PLAN'||h_i;
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
1287 h_table_name := 'BSC_TMP_PROJECTIONS_Y';
1288 h_table_columns.delete;
1289 h_num_columns := 0;
1290 h_num_columns := h_num_columns + 1;
1291 h_table_columns(h_num_columns).column_name := 'ROW_ID';
1292 h_table_columns(h_num_columns).data_type := 'ROWID';
1293 h_table_columns(h_num_columns).data_size := NULL;
1294 h_table_columns(h_num_columns).add_to_index := 'Y';
1295 FOR h_i IN 1..h_num_data_columns LOOP
1296 h_num_columns := h_num_columns + 1;
1297 h_table_columns(h_num_columns).column_name := 'DATA'||h_i;
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
1307 --Bug#3875046: Do not create index on temporary tables
1308 h_table_name := 'BSC_TMP_XMD';
1309 h_table_columns.delete;
1310 h_num_columns := 0;
1311 FOR h_i IN 1..h_num_key_columns LOOP
1312 h_num_columns := h_num_columns + 1;
1313 h_table_columns(h_num_columns).column_name := 'KEY'||h_i;
1314 h_table_columns(h_num_columns).data_type := 'VARCHAR2';
1315 h_table_columns(h_num_columns).data_size := 400;
1316 h_table_columns(h_num_columns).add_to_index := 'N';
1317 END LOOP;
1318 IF BSC_APPS.bsc_mv THEN
1319 h_num_columns := h_num_columns + 1;
1320 h_table_columns(h_num_columns).column_name := 'PERIODICITY_ID';
1321 h_table_columns(h_num_columns).data_type := 'NUMBER';
1322 h_table_columns(h_num_columns).data_size := NULL;
1323 h_table_columns(h_num_columns).add_to_index := 'N';
1324 END IF;
1325 h_num_columns := h_num_columns + 1;
1326 h_table_columns(h_num_columns).column_name := 'YEAR';
1327 h_table_columns(h_num_columns).data_type := 'NUMBER';
1328 h_table_columns(h_num_columns).data_size := 5;
1329 h_table_columns(h_num_columns).add_to_index := 'N';
1330 h_num_columns := h_num_columns + 1;
1331 h_table_columns(h_num_columns).column_name := 'TYPE';
1332 h_table_columns(h_num_columns).data_type := 'NUMBER';
1333 h_table_columns(h_num_columns).data_size := 3;
1334 h_table_columns(h_num_columns).add_to_index := 'N';
1335 h_num_columns := h_num_columns + 1;
1336 h_table_columns(h_num_columns).column_name := 'PERIOD';
1337 h_table_columns(h_num_columns).data_type := 'NUMBER';
1338 h_table_columns(h_num_columns).data_size := 5;
1339 h_table_columns(h_num_columns).add_to_index := 'N';
1340 FOR h_i IN 1..h_num_data_columns LOOP
1341 h_num_columns := h_num_columns + 1;
1342 h_table_columns(h_num_columns).column_name := 'XMED'||h_i;
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
1352 h_table_name := 'BSC_TMP_XMD_Y';
1353 h_table_columns.delete;
1354 h_num_columns := 0;
1355 h_num_columns := h_num_columns + 1;
1356 h_table_columns(h_num_columns).column_name := 'ROW_ID';
1357 h_table_columns(h_num_columns).data_type := 'ROWID';
1358 h_table_columns(h_num_columns).data_size := NULL;
1359 h_table_columns(h_num_columns).add_to_index := 'Y';
1360 FOR h_i IN 1..h_num_data_columns LOOP
1361 h_num_columns := h_num_columns + 1;
1362 h_table_columns(h_num_columns).column_name := 'XMED'||h_i;
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
1372 --Bug#3875046: Do not create index on temporary tables
1373 h_table_name := 'BSC_TMP_UNION';
1374 h_table_columns.delete;
1375 h_num_columns := 0;
1376 FOR h_i IN 1..h_num_key_columns LOOP
1377 h_num_columns := h_num_columns + 1;
1378 h_table_columns(h_num_columns).column_name := 'KEY'||h_i;
1379 h_table_columns(h_num_columns).data_type := 'VARCHAR2';
1380 h_table_columns(h_num_columns).data_size := 400;
1381 h_table_columns(h_num_columns).add_to_index := 'N';
1382 END LOOP;
1383 h_num_columns := h_num_columns + 1;
1384 h_table_columns(h_num_columns).column_name := 'YEAR';
1385 h_table_columns(h_num_columns).data_type := 'NUMBER';
1386 h_table_columns(h_num_columns).data_size := 5;
1387 h_table_columns(h_num_columns).add_to_index := 'N';
1388 h_num_columns := h_num_columns + 1;
1389 h_table_columns(h_num_columns).column_name := 'TYPE';
1390 h_table_columns(h_num_columns).data_type := 'NUMBER';
1391 h_table_columns(h_num_columns).data_size := 3;
1392 h_table_columns(h_num_columns).add_to_index := 'N';
1393 h_num_columns := h_num_columns + 1;
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
1403 -- BSC_TMP_BASE_UPDATE
1404 h_table_name := 'BSC_TMP_BASE_UPDATE';
1405 h_table_columns.delete;
1406 h_num_columns := 0;
1407 h_num_columns := h_num_columns + 1;
1408 h_table_columns(h_num_columns).column_name := 'ROW_ID';
1409 h_table_columns(h_num_columns).data_type := 'ROWID';
1410 h_table_columns(h_num_columns).data_size := NULL;
1411 h_table_columns(h_num_columns).add_to_index := 'Y';
1412 FOR h_i IN 1..h_num_data_columns LOOP
1413 h_num_columns := h_num_columns + 1;
1414 h_table_columns(h_num_columns).column_name := 'DATA'||h_i;
1415 h_table_columns(h_num_columns).data_type := 'NUMBER';
1416 h_table_columns(h_num_columns).data_size := NULL;
1417 h_table_columns(h_num_columns).add_to_index := 'N';
1418 END LOOP;
1419 --AW_INTEGRATION: need PROJECTION column VARCHAR2(60) and CHANGE_VECTOR NUMBER
1420 h_num_columns := h_num_columns + 1;
1421 h_table_columns(h_num_columns).column_name := 'PROJECTION';
1422 h_table_columns(h_num_columns).data_type := 'VARCHAR2';
1423 h_table_columns(h_num_columns).data_size := 60;
1424 h_table_columns(h_num_columns).add_to_index := 'N';
1425 h_num_columns := h_num_columns + 1;
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
1435 -- BSC_TMP_BASE_ROWID
1436 h_table_name := 'BSC_TMP_BASE_ROWID';
1437 h_table_columns.delete;
1438 h_num_columns := 0;
1439 h_num_columns := h_num_columns + 1;
1440 h_table_columns(h_num_columns).column_name := 'ROW_ID_TMP';
1441 h_table_columns(h_num_columns).data_type := 'ROWID';
1442 h_table_columns(h_num_columns).data_size := NULL;
1443 h_table_columns(h_num_columns).add_to_index := 'N';
1444 h_num_columns := h_num_columns + 1;
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;
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
1462 BSC_MESSAGE.Add(x_message => SQLERRM,
1463 x_source => 'BSC_UPDATE_BASE.Create_Generic_Temp_Tables');
1464 RETURN FALSE;
1465 END Create_Generic_Temp_Tables;
1466
1467 --LOCKING: new function
1468 /*===========================================================================+
1469 | FUNCTION Create_Generic_Temp_Tables_AT
1470 +============================================================================*/
1471 FUNCTION Create_Generic_Temp_Tables_AT RETURN BOOLEAN IS
1472 PRAGMA AUTONOMOUS_TRANSACTION;
1473 h_b BOOLEAN;
1474 BEGIN
1475 h_b := Create_Generic_Temp_Tables;
1476 commit; -- all autonomous transaction needs to commit
1477 RETURN h_b;
1478 END Create_Generic_Temp_Tables_AT;
1479
1480
1481 /*
1482 given a bace table, this gives all the higher level periodicities for the base fact
1483 */
1484 -------------------------------venu-----------------
1485
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
1495
1496 CURSOR c_Calc_Period IS
1497 SELECT TO_NUMBER(C.PARAMETER1) Parameter1, P.CALENDAR_ID Calendar_Id, P.DB_COLUMN_NAME Db_Column_Name
1498 FROM BSC_DB_CALCULATIONS C, BSC_SYS_PERIODICITIES P
1499 WHERE C.Parameter1 = P.Periodicity_Id
1500 AND C.Calculation_Type = 6
1501 AND C.Table_Name = p_table_name;
1502
1503 BEGIN
1504
1505
1506 p_number_periodicity := 1;
1507
1508 FOR cr IN c_Calc_Period LOOP
1509 p_periodicity(p_number_periodicity) := cr.Parameter1;
1510 p_calendar_id(p_number_periodicity) := cr.Calendar_Id;
1511 p_column_name(p_number_periodicity) := cr.Db_Column_Name;
1512
1513 p_number_periodicity := p_number_periodicity + 1;
1514 END LOOP;
1515 p_number_periodicity := p_number_periodicity - 1;
1516
1517
1518 RETURN TRUE;
1519
1520 EXCEPTION
1521 WHEN OTHERS THEN
1522 BSC_MESSAGE.Add(x_message => SQLERRM,
1523 x_source => 'BSC_UPDATE_BASE.get_base_higher_periodicities');
1524 RETURN FALSE;
1525 End Get_Base_Higher_Periodicities;
1526 ------------------------venu------------------------------
1527
1528
1529 /*===========================================================================+
1530 | FUNCTION Update_Base_Table |
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,
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,
1545 x_in_subper_fld VARCHAR2,
1546 x_projection_flag VARCHAR2,
1547 x_current_fy NUMBER,
1548 x_current_per_base_table NUMBER,
1549 x_prev_current_period NUMBER, --Fix bug#4235448 Need this parameter
1550 x_correction_flag BOOLEAN,
1551 x_aw_flag BOOLEAN,
1552 x_change_vector_value NUMBER
1553 ) RETURN BOOLEAN IS
1554 e_unexpected_error EXCEPTION;
1555 h_sql VARCHAR2(32700);
1556 h_i NUMBER;
1557 h_lst_key_columns VARCHAR2(32700);
1558 h_lst_data_columns VARCHAR2(32700);
1559 h_lst_key_columns_temp VARCHAR2(32700);
1560 h_lst_data_columns_temp VARCHAR2(32700);
1561 h_lst_data_formulas VARCHAR2(32700);
1562 h_lst_select VARCHAR2(32700);
1563 h_lst_from VARCHAR2(32700);
1564 h_lst_where VARCHAR2(32700);
1565 h_lst_join VARCHAR2(32700);
1566 h_lst_cond_null VARCHAR2(32700);
1567 h_trg_table VARCHAR2(30);
1568
1569 -- Name of the column of bsc_db_calendar according to the periodicity
1570 -- of input and base tables
1571 h_input_calendar_col_name VARCHAR2(30);
1572 h_base_calendar_col_name VARCHAR2(30);
1573
1574 h_yearly_flag NUMBER;
1575 h_edw_flag NUMBER;
1576 h_periodicity_type_input_table NUMBER;
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;
1586 l_num_bind_vars NUMBER;
1587
1588 l_bind_var_per 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 -------------------------------------
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);
1604 l_periodicity_stmt varchar2(3000);
1605 l_balance_flag boolean;
1606 l_found boolean;
1607 l_calendar_sql varchar2(3000);
1608 l_yearly_flag number;
1609 ------------------venu---------------------
1610
1611 h_calendar_source VARCHAR2(20);
1612
1613 h_level_table_name VARCHAR2(30);
1614 h_level_short_name VARCHAR2(80);
1615 h_level_source VARCHAR2(10);
1616 h_dbi_dim_data BSC_UPDATE_DIM.t_dbi_dim_data;
1617
1618 --Fix bug#3875046: need this variables
1619 h_lst_data_columns_temp_p VARCHAR2(32700);
1620 h_row_count NUMBER;
1621 h_num_rows_tmp NUMBER;
1622 h_num_rows_base NUMBER;
1623 l_hint VARCHAR2(2000);
1624
1625 --AW_INTEGRATION: New variables
1626 h_projection_col_temp VARCHAR2(30);
1627 h_aw_table VARCHAR2(30);
1628
1629 --Fix bug#4235448: need this new variable
1630 l_current_period NUMBER;
1631 l_prev_current_period NUMBER;
1632 TYPE t_cursor IS REF CURSOR;
1633 h_cursor t_cursor;
1634
1635 BEGIN
1636 h_sql := NULL;
1637 h_lst_key_columns := NULL;
1638 h_lst_data_columns := NULL;
1639 h_lst_key_columns_temp := NULL;
1640 h_lst_data_columns_temp := NULL;
1641 h_lst_data_formulas := NULL;
1642 h_lst_select := NULL;
1643 h_lst_from := NULL;
1644 h_lst_where := NULL;
1645 h_lst_join := NULL;
1646 h_lst_cond_null := NULL;
1647 h_trg_table := NULL;
1648 h_yearly_flag := 0;
1649 h_edw_flag := 0; -- Fix bug#3875046
1650 h_lst_data_columns_temp_p := NULL;
1651 h_row_count := 0;
1652 h_num_rows_tmp := 0;
1653 h_num_rows_base := 0;
1654
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
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.
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
1677 BSC_BIA_WRAPPER.Analyze_Table(x_in_tbl);
1678 commit;
1679
1680 /*--------------------------------------------------------------+
1681 | Determine the values for the the sql components to translate|
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
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||', ';
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';
1707 h_lst_data_columns_temp := h_lst_data_columns_temp||', PROJECTION, CHANGE_VECTOR';
1708 -- Fix bug#4653405: AW_INTEGRATION: we need to set projection flag Y for any period
1709 -- beyond the current period no matter if it is target
1710 -- Note: we assume no change of periodicity between I and B tables
1711 IF h_calendar_source = 'BSC' THEN
1712 IF h_yearly_flag = 1 THEN
1713 h_lst_data_formulas := h_lst_data_formulas||', '||
1714 ' case when '||x_in_tbl||'.YEAR > '||x_current_fy||
1715 ' then ''Y'' else ''N'' end';
1716 ELSE
1717 h_lst_data_formulas := h_lst_data_formulas||', '||
1718 ' case when ('||x_in_tbl||'.YEAR = '||x_current_fy||' AND '||
1719 x_in_tbl||'.PERIOD > '||x_current_per_base_table||') OR ('||
1720 x_in_tbl||'.YEAR > '||x_current_fy||')'||
1721 ' then ''Y'' else ''N'' end';
1722 END IF;
1723 ELSE
1724 -- BIS calendar
1725 IF h_yearly_flag = 1 THEN
1726 h_lst_data_formulas := h_lst_data_formulas||', '||
1727 ' case when BSC_SYS_PERIODS.YEAR > '||x_current_fy||
1728 ' then ''Y'' else ''N'' end';
1729 ELSE
1730 h_lst_data_formulas := h_lst_data_formulas||', '||
1731 ' case when (BSC_SYS_PERIODS.YEAR = '||x_current_fy||' AND '||
1732 'BSC_SYS_PERIODS.PERIOD_ID > '||x_current_per_base_table||') OR ('||
1733 'BSC_SYS_PERIODS.YEAR > '||x_current_fy||')'||
1734 ' then ''Y'' else ''N'' end';
1735 END IF;
1736 END IF;
1737 h_lst_data_formulas := h_lst_data_formulas||', '||x_change_vector_value;
1738 END IF;
1739
1740 h_lst_select := NULL;
1741 h_lst_from := x_in_tbl;
1742 l_parallel_hint:='parallel ('||x_in_tbl||')';
1743 h_lst_where := NULL;
1744
1745 FOR h_i IN 1 .. x_num_key_columns LOOP
1746 h_level_table_name := x_key_dim_tables(h_i);
1747
1748 -- BSC-BIS-DIMENSIONS: If the dimension is a DBI dimension and it is materialized and
1749 -- the table exists then we use the table created in BSC to materialize
1750 -- the DBI dimension to translate from USER_CODE into CODE.
1751 -- Note that is only in MV Architecture.
1752 IF BSC_APPS.bsc_mv THEN
1753 SELECT short_name, source
1754 INTO h_level_short_name, h_level_source
1755 FROM bsc_sys_dim_levels_b
1756 WHERE level_view_name = h_level_table_name;
1757
1758 IF (h_level_source = 'PMF') AND (h_level_short_name IS NOT NULL) THEN
1759 BSC_UPDATE_DIM.Get_Dbi_Dim_Data(h_level_short_name, h_dbi_dim_data);
1760 IF (h_dbi_dim_data.short_name IS NOT NULL) AND
1761 (h_dbi_dim_data.table_name IS NOT NULL) AND
1762 (h_dbi_dim_data.materialized='YES') THEN
1763 IF BSC_APPS.Table_Exists(h_dbi_dim_data.table_name) THEN
1764 h_level_table_name := h_dbi_dim_data.table_name;
1765 END IF;
1766 END IF;
1767 END IF;
1768 END IF;
1769
1770 h_lst_select := h_lst_select||h_level_table_name||'.CODE, ';
1771
1772 h_lst_from := h_lst_from||', '||h_level_table_name;
1773 l_parallel_hint:=l_parallel_hint||' parallel ('||h_level_table_name||')';
1774
1775 IF h_lst_where IS NOT NULL THEN
1776 h_lst_where := h_lst_where||' AND ';
1777 END IF;
1778 h_lst_where := h_lst_where||x_in_tbl||'.'||x_key_columns(h_i)||' = '||h_level_table_name||'.USER_CODE';
1779
1780 h_key_columns_temp(h_i) := 'KEY'||h_i;
1781 END LOOP;
1782
1783 --Fix bug#3875046: Need to initialize h_lst_data_columns_temp_p
1784 h_lst_data_columns_temp_p := NULL;
1785 FOR h_i IN 1 .. x_num_data_columns LOOP
1786 h_data_columns_temp(h_i) := 'DATA'||h_i;
1787
1788 IF h_i > 1 THEN
1789 h_lst_data_columns_temp_p := h_lst_data_columns_temp_p||',';
1790 END IF;
1791 h_lst_data_columns_temp_p := h_lst_data_columns_temp_p||'BSC_TMP_BASE.DATA'||h_i;
1792 END LOOP;
1793 -- AW_INTEGRATION: Base table has an additional column called projection and change_vector
1794 IF BSC_APPS.bsc_mv AND x_aw_flag THEN
1795 h_lst_data_columns_temp_p := h_lst_data_columns_temp_p||',BSC_TMP_BASE.PROJECTION, BSC_TMP_BASE.CHANGE_VECTOR';
1796 END IF;
1797
1798 /*--------------------------------------------------------------+
1799 | Determine the values for the rest of the sql components |
1800 | based on the basic transformation type -- periodicity change |
1801 | and balance transfer. The following component values vary |
1802 | depending on the transformation type. |
1803 | |
1804 | Note: By design there is no change of periodicity between |
1805 | input table and base table. The only two cases are: |
1806 | month week --> week52 and month week --> daily345. So we |
1807 | dont care about balance data columns |
1808 +--------------------------------------------------------------*/
1809
1810 l_bind_var_per := NULL;
1811
1812 IF x_base_percode = x_in_percode THEN
1813 -- There is no change of periodicity
1814
1815 -- BSC-BIS-DIMENSIONS Note:
1816 -- We know that never there is change of periodicity from input to base table
1817 -- when the table is using a BIS calendar, so the code always get here.
1818
1819 IF h_calendar_source = 'BSC' THEN
1820 h_lst_select := h_lst_select||x_in_tbl||'.YEAR, '||x_in_tbl||'.TYPE, '||x_in_tbl||'.'||x_in_per_fld;
1821 ELSE
1822 -- Table is using a BIS calendar. We need to translate from TIME_FK (in input table) to
1823 -- YEAR, PERIOD in base table. It uses BSC_SYS_PERIODS to do so.
1824
1825 IF h_yearly_flag = 1 THEN
1826 h_lst_select := h_lst_select||'BSC_SYS_PERIODS.YEAR, '||x_in_tbl||'.TYPE, 0';
1827 ELSE
1828 h_lst_select := h_lst_select||'BSC_SYS_PERIODS.YEAR, '||x_in_tbl||'.TYPE, BSC_SYS_PERIODS.PERIOD_ID';
1829 END IF;
1830
1831 h_lst_from := h_lst_from||', BSC_SYS_PERIODS';
1832 l_parallel_hint:=l_parallel_hint||' parallel (BSC_SYS_PERIODS)';
1833
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||
1843 'TRUNC('||x_in_tbl||'.TIME_FK) = TRUNC(TO_DATE(BSC_SYS_PERIODS.TIME_FK, ''MM/DD/YYYY'')) AND '||
1844 'BSC_SYS_PERIODS.PERIODICITY_ID = :2';
1845 l_bind_var_per := x_base_percode;
1846 ELSE
1847 -- Other periodicity. TIME_FK is VARCHAR2
1848 h_lst_where := h_lst_where||
1849 x_in_tbl||'.TIME_FK = BSC_SYS_PERIODS.TIME_FK AND '||
1850 'BSC_SYS_PERIODS.PERIODICITY_ID = :2';
1851 l_bind_var_per := x_base_percode;
1852 END IF;
1853 END IF;
1854 ELSE
1855 -- BSC-BIS-DIMENSIONS Note:
1856 -- We know that never there is change of periodicity from input to base table
1857 -- when the table is using a BIS calendar. So I do not need to change this code
1858
1859 -- There is change of periodicity
1860 -- Note: We suppose that the change of periodicity is allowed
1861 -- (see bsc_sys_periodicites) plus:
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
1871 h_lst_select := h_lst_select||x_in_tbl||'.YEAR, '||x_in_tbl||'.TYPE, 0';
1872
1873 ELSIF h_periodicity_type_base_table = 7 AND h_periodicity_type_input_table = 11 THEN
1874 -- The base table is Weekly52 and the input table is Month Week
1875 -- This is the special case that use bsc_db_week_maps table to make the
1876 -- transformation
1877 h_lst_from := h_lst_from||', BSC_DB_WEEK_MAPS';
1878 l_parallel_hint:=l_parallel_hint||' parallel (BSC_DB_WEEK_MAPS)';
1879
1880 h_lst_select := h_lst_select||x_in_tbl||'.YEAR, '||x_in_tbl||'.TYPE, BSC_DB_WEEK_MAPS.WEEK52';
1881
1882 IF h_lst_where IS NOT NULL THEN
1883 h_lst_where := h_lst_where||' AND ';
1884 END IF;
1885 h_lst_where := h_lst_where||
1886 x_in_tbl||'.YEAR = BSC_DB_WEEK_MAPS.YEAR AND '||
1887 x_in_tbl||'.'||x_in_per_fld||' = BSC_DB_WEEK_MAPS.MONTH AND '||
1888 x_in_tbl||'.'||x_in_subper_fld||' = BSC_DB_WEEK_MAPS.WEEK AND '||
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
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;
1906
1907 IF h_lst_where IS NOT NULL THEN
1908 h_lst_where := h_lst_where||' AND ';
1909 END IF;
1910 h_lst_where := h_lst_where||
1911 x_in_tbl||'.YEAR = BSC_DB_CALENDAR.YEAR AND '||
1912 x_in_tbl||'.'||x_in_per_fld||' = BSC_DB_CALENDAR.MONTH AND '||
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
1922 h_lst_where := h_lst_where||' AND ';
1923 END IF;
1924 h_lst_where := h_lst_where||
1925 x_in_tbl||'.YEAR = BSC_DB_CALENDAR.YEAR AND '||
1926 x_in_tbl||'.'||x_in_per_fld||' = BSC_DB_CALENDAR.'||h_input_calendar_col_name||' AND '||
1927 'BSC_DB_CALENDAR.CALENDAR_ID = :2';
1928 l_bind_var_per := h_calendar_id;
1929 END IF;
1930
1931 ELSE
1932 -- EDW periodicity
1933 -- Use bsc_edw_time_map to make the transformation.
1934 -- This table was previously created for X_In_Percode --> X_Base_Percode
1935 h_lst_from := h_lst_from||', BSC_EDW_TIME_MAP';
1936 l_parallel_hint:=l_parallel_hint||' parallel (BSC_EDW_TIME_MAP)';
1937 h_lst_select := h_lst_select||x_in_tbl||'.YEAR, '||
1938 x_in_tbl||'.TYPE, BSC_EDW_TIME_MAP.BSC_TARGET';
1939
1940 IF h_lst_where IS NOT NULL THEN
1941 h_lst_where := h_lst_where||' AND ';
1942 END IF;
1943 h_lst_where := h_lst_where||
1944 x_in_tbl||'.YEAR = BSC_EDW_TIME_MAP.YEAR AND '||
1945 x_in_tbl||'.'||x_in_per_fld||' = BSC_EDW_TIME_MAP.BSC_SOURCE';
1946 END IF;
1947 END IF;
1948 END IF;
1949
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, ';
1959 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) 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
1969 h_sql := h_sql||':1, ';
1970 END IF;
1971 h_sql := h_sql||h_lst_data_formulas||
1972 ' FROM '||h_lst_from;
1973 IF h_lst_where IS NOT NULL THEN
1974 h_sql := h_sql||' WHERE '||h_lst_where;
1975 END IF;
1976 h_sql := h_sql||' GROUP BY '||h_lst_select;
1977 -------------------venu------------------------------
1978 if BSC_APPS.bsc_mv and x_correction_flag and (NOT x_aw_flag) then
1979 /*
1980 the user has made some mistake. say user entered sum(m1), then loaded BSC. later, user changes
1981 sum(m1) to avg(m1). in this case, the higher periodicities in the base table are first deleted,
1982 and then data from the base table is moved to the tmp table. so the step of pulling data from
1983 the imput table is skipped.
1984 */
1985 --delete data from the base table for higher periodicities
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
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, ';
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, ';
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 /*
2013 in summary tables architecture and incremental mode, no need to pull data from the
2014 input table. We jsut need to insert all the rows from the base table to the BSC_TMP_BASE table.
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, ';
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;
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;
2036 l_num_bind_vars := 0;
2037
2038 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
2039 l_bind_vars_values(1):= x_base_percode;
2040 l_num_bind_vars := 1;
2041 END IF;
2042 IF l_bind_var_per IS NOT NULL THEN
2043 l_num_bind_vars := l_num_bind_vars + 1;
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);
2053 h_num_rows_tmp := h_num_rows_tmp + h_row_count;
2054 END IF;
2055 commit;
2056 end if;
2057 commit;
2058
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,
2068 'AND');
2069 h_lst_join := h_lst_join||' AND ';
2070 END IF;
2071
2072 -- BSC-MV Note: Add periodicity_id column in the join
2073 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
2074 h_lst_join := h_lst_join||
2075 x_base_tbl||'.PERIODICITY_ID = BSC_TMP_BASE.PERIODICITY_ID AND ';
2076 END IF;
2077 h_lst_join := h_lst_join||
2078 x_base_tbl||'.YEAR = BSC_TMP_BASE.YEAR AND '||
2079 x_base_tbl||'.TYPE = BSC_TMP_BASE.TYPE AND '||
2080 x_base_tbl||'.PERIOD = BSC_TMP_BASE.PERIOD';
2081
2082 -----venu-------------------------------------------------------------------
2083 --BSC-MV Note: This code applies only for new architecture
2084 --AW_INTEGRATION: If the base table is for AW, it does not have higher periodicities
2085 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
2086 --get all the higher periodicities for the base table
2087 if get_base_higher_periodicities(
2088 x_base_tbl,
2089 l_periodicity,
2090 l_calendar_id,
2091 l_column_name,
2092 l_number_periodicity)=false then
2093 RAISE e_unexpected_error;
2094 end if;
2095 --before we update the data in the base fact, we need to capture the
2096 --data in the base fact. this is because, we will later need to subtract
2097 --this data from the higher level aggregation
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, '||
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
2114 l_stmt:=l_stmt||'bsc_tmp_base.'||h_key_columns_temp(i)||',';
2115 end loop;
2116 l_stmt:=l_stmt||'bsc_tmp_base.PERIODICITY_ID,bsc_tmp_base.YEAR,bsc_tmp_base.TYPE,bsc_tmp_base.PERIOD,';
2117 for i in 1..x_num_data_columns loop
2118 l_stmt:=l_stmt||x_base_tbl||'.'||x_data_columns(i)||',';
2119 end loop;
2120 l_stmt:=substr(l_stmt,1,length(l_stmt)-1);
2121 l_stmt:=l_stmt||' from bsc_tmp_base,'||x_base_tbl||' where ';
2122 for i in 1..x_num_key_columns loop
2123 l_stmt:=l_stmt||x_base_tbl||'.'||x_key_columns(i)||'=bsc_tmp_base.'||h_key_columns_temp(i)||' and ';
2124 end loop;
2125 l_stmt:=l_stmt||'bsc_tmp_base.PERIODICITY_ID='||x_base_tbl||'.PERIODICITY_ID and '||
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 ------------------------------------------------------------------------
2135 END IF;
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
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||')'||
2149 ' SELECT '||x_base_tbl||'.rowid, '||h_lst_data_columns_temp_p||
2150 ' FROM '||x_base_tbl||', bsc_tmp_base'||
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'||
2160 ' SET ('||h_lst_data_columns||')=('||
2161 ' SELECT '||h_lst_data_columns_temp||
2162 ' FROM bsc_tmp_base_update P'||
2163 ' WHERE P.row_id = B.rowid)'||
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
2173 h_lst_select := NULL;
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
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,
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 ';
2196
2197 END IF;
2198
2199 --BSC-MV Note: Add periodicity id
2200 --AW_INTEGRATION: no need periodicity_id
2201 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
2202 h_lst_select := h_lst_select||'BSC_TMP_BASE.PERIODICITY_ID,';
2203 END IF;
2204 h_lst_select := h_lst_select||'BSC_TMP_BASE.YEAR, BSC_TMP_BASE.TYPE, BSC_TMP_BASE.PERIOD, '||
2205 h_lst_data_columns_temp_p;
2206
2207 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
2208 h_lst_join := h_lst_join||
2209 'BSC_TMP_BASE.PERIODICITY_ID = '||x_base_tbl||'.PERIODICITY_ID (+) AND ';
2210 END IF;
2211 h_lst_join := h_lst_join||
2212 'BSC_TMP_BASE.YEAR = '||x_base_tbl||'.YEAR (+) AND '||
2213 'BSC_TMP_BASE.TYPE = '||x_base_tbl||'.TYPE (+) AND '||
2214 'BSC_TMP_BASE.PERIOD = '||x_base_tbl||'.PERIOD (+)';
2215
2216 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) THEN
2217 h_lst_cond_null := h_lst_cond_null||
2218 x_base_tbl||'.PERIODICITY_ID IS NULL OR ';
2219 END IF;
2220 h_lst_cond_null := h_lst_cond_null||
2221 x_base_tbl||'.YEAR IS NULL OR '||
2222 x_base_tbl||'.TYPE IS NULL OR '||
2223 x_base_tbl||'.PERIOD IS NULL';
2224 /*
2225 9/26/03
2226 before we insert into the base table, we need to see if we can use append hint.
2227 the logic is as follows.
2228 for I->B
2229 if the base table has data, then this is inc and do not use append if there is
2230 snapshot log on the base table.
2231 for Projections
2232 if the base table has a snapshot log on it and the snashot log has at-least
2233 one row of data in it, then this insert is incremental and we cannot use
2234 append hint. append hint will not write into the snapshot log
2235 */
2236 declare
2237 ll_use_append boolean;
2238 ll_base_count number;
2239 begin
2240 ll_use_append:=true;
2241 if BSC_IM_UTILS.check_snapshot_log(x_base_tbl,BSC_APPS.bsc_apps_schema) then
2242 --see if the base table has any data
2243 --does_table_have_data will return 2 if the snapshot log has data, 0 if there is some error and
2244 --1 if there is no data
2245 ll_base_count:=BSC_IM_UTILS.does_table_have_data(x_base_tbl,null);
2246 if ll_base_count<>1 then
2247 ll_use_append:=false;--to be on the safe side
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 /*+';
2257 end if;
2258 end;
2259 h_sql := h_sql||' */';
2260 h_sql := h_sql ||'INTO '||x_base_tbl||' ('||h_lst_key_columns;
2261 IF BSC_APPS.bsc_mv AND (NOT x_aw_flag) 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'||
2271 ' WHERE '||h_lst_join||' AND ('||h_lst_cond_null||')';
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
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
2290 --AW_INTEGRATION: This code does not apply if the base table of fo AW
2291 IF (NOT BSC_APPS.bsc_mv) OR x_aw_flag THEN
2292 RETURN TRUE;
2293 END IF;
2294
2295 -- Fix bug#4235448: If there is at least one balance column we need to
2296 -- insert into BSC_TMP_BASE the rows from current period to new current period
2297 -- that do not exist in BSC_TMP_PERIOD.
2298 -- We do this only when current_period < new_current_period and there are higher periodicities
2299 -- Also when x_correction_flag is TRUE we are doing full refresh so not need to do this.
2300 l_balance_flag:=false;
2301 for i in 1..x_num_data_columns loop
2302 if x_data_measure_types(i)<>1 then
2303 l_balance_flag:=true;
2304 exit;
2305 end if;
2306 end loop;
2307 IF (l_number_periodicity>0) AND l_balance_flag AND (NOT x_correction_flag) AND
2308 (x_current_per_base_table > x_prev_current_period) THEN
2309 for j in 1..2 loop
2310 if j=1 then
2311 l_table:='bsc_tmp_base';
2312 else
2313 l_table:='bsc_tmp_base_bu';
2314 end if;
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,
2324 h_key_columns_temp,
2325 x_num_key_columns,
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 ';
2335 END IF;
2336 h_lst_select := h_lst_select||x_base_tbl||'.PERIODICITY_ID, '||
2337 x_base_tbl||'.YEAR, '||x_base_tbl||'.TYPE, '||x_base_tbl||'.PERIOD';
2338 for i in 1..x_num_data_columns loop
2339 if x_data_measure_types(i)=1 then
2340 h_lst_select := h_lst_select||', 0';
2341 else
2342 h_lst_select := h_lst_select||', '||x_base_tbl||'.'||x_data_columns(i);
2343 end if;
2344 end loop;
2345 h_lst_join := h_lst_join||x_base_tbl||'.PERIODICITY_ID = '||l_table||'.PERIODICITY_ID (+) AND '||
2346 x_base_tbl||'.YEAR = '||l_table||'.YEAR (+) AND '||
2347 x_base_tbl||'.TYPE = '||l_table||'.TYPE (+) AND '||
2348 x_base_tbl||'.PERIOD = '||l_table||'.PERIOD (+)';
2349 -- Fix bug#4480258: perf issue. no need null condition on all the keys. We just
2350 --need on YEAR, if it is null then the other keys are null too.
2351 --h_lst_cond_null := h_lst_cond_null||l_table||'.PERIODICITY_ID IS NULL OR '||
2352 -- l_table||'.YEAR IS NULL OR '||l_table||'.TYPE IS NULL OR '||
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 ';
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||
2367 ' WHERE '||h_lst_join||' AND '||
2368 x_base_tbl||'.PERIODICITY_ID = :1 AND '||x_base_tbl||'.YEAR = :2 AND '||
2369 x_base_tbl||'.PERIOD >= :3 AND '||x_base_tbl||'.PERIOD <= :4 AND ('||h_lst_cond_null||')';
2370 l_bind_vars_values.delete;
2371 l_bind_vars_values(1) := x_base_percode;
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;
2381 end loop;
2382 END IF;
2383
2384 /*
2385 after the base periodicity data is inserted into the base table, we are going to
2386 rollup the data to higher periodicities and then perform an update / insert
2387 */
2388 if l_number_periodicity>0 then
2389 FOR h_i IN 1 .. x_num_data_columns LOOP
2390 --h_data_columns_temp(h_i) := 'DATA'||h_i;
2391 --we need this because we are going to aggregate the data in the tmp table and store it in the tmp table
2392 l_bf_columns(h_i):=x_data_columns(h_i);
2393 -- Fix bug#4026328:replace('sum(m)','m','data1') --> sudata1(data1) error!!
2394 -- The fix is: replace('sum(m)','(m)', '(data1)') --> sum(data1)
2395 l_bf_column_formulas(h_i):=replace(x_data_formulas(h_i),'('||x_data_columns(h_i)||')','('||h_data_columns_temp(h_i)||')');
2396 END LOOP;
2397 --for each of the periodicity, rollup
2398 --we always rollup from the base periodicity data in the tmp table.
2399 --if we rollup from month to qtr, we again rollup from month to year. this is for simplicity
2400 --we rollup the data in bsc_tmp_base and bsc_tmp_base_bu
2401 for i in 1..l_number_periodicity loop
2402 if l_periodicity(i)<>x_base_percode then
2403 l_calendar_sql:='select distinct calendar_id,year,';
2404 if lower(l_column_name(i))<>'year' and lower(l_column_name(i))<>lower(h_base_calendar_col_name) then
2405 l_calendar_sql:=l_calendar_sql||l_column_name(i)||',';
2406 end if;
2407 if lower(h_base_calendar_col_name)<>'year' then
2408 l_calendar_sql:=l_calendar_sql||h_base_calendar_col_name||',';
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;
2418 close h_cursor;
2419 l_stmt := 'select max('||l_column_name(i)||') from bsc_db_calendar'||
2420 ' where '||h_base_calendar_col_name||' = :1 and year = :2 and calendar_id = :3';
2421 open h_cursor for l_stmt using x_prev_current_period, x_current_fy, l_calendar_id(i);
2422 fetch h_cursor into l_prev_current_period;
2423 close h_cursor;
2424 end if;
2425 for j in 1..2 loop
2426 if j=1 then
2427 l_table:='bsc_tmp_base';
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,'||
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
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;
2450 for k in 1..x_num_data_columns loop
2451 if x_data_measure_types(k)=1 then --do the aggregation only for TOTAL columns and not balance
2452 l_stmt:=l_stmt||l_bf_column_formulas(k)||',';
2453 else --for balance columns, null for now.
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,';
2463 else
2464 l_stmt:=l_stmt||'case when '||l_table||'.YEAR = '||x_current_fy||
2465 ' then null else -999999999999 end case,';
2466 end if;
2467 else
2468 l_stmt:=l_stmt||'-999999999999,';
2469 end if;
2470 end if;
2471 end loop;
2472 l_stmt:=substr(l_stmt,1,length(l_stmt)-1);
2473 l_stmt:=l_stmt||' from '||l_table||',('||l_calendar_sql||') bsc_db_calendar where '||
2474 l_table||'.year=bsc_db_calendar.year and '||
2475 l_table||'.period=bsc_db_calendar.'||h_base_calendar_col_name||' and bsc_db_calendar.calendar_id=:2 and '||
2476 l_table||'.periodicity_id=:3 group by '||h_lst_key_columns_temp||l_table||'.periodicity_id,'||
2477 l_table||'.YEAR,'||l_table||'.TYPE,'||
2478 'bsc_db_calendar.'||l_column_name(i);
2479 l_bind_vars_values.delete;
2480 l_bind_vars_values(1):=l_periodicity(i);
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;
2490 end loop;--for j in 1..2
2491 end if;
2492 commit;
2493 end loop;
2494 ---------------------for balance-------------------------------
2495 --see if there are balance columns. if there are ba;ance columns, we need special logic
2496 --also this needs to happen in every loop
2497 l_balance_flag:=false;
2498 for i in 1..x_num_data_columns loop
2499 if x_data_measure_types(i)<>1 then
2500 l_balance_flag:=true;
2501 exit;
2502 end if;
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------------
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'||
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);
2530 l_bind_vars_values(4) := (x_current_fy);
2531 l_stmt:='UPDATE bsc_tmp_per_change_bal'||
2532 ' SET src_per = :1'||
2533 ' WHERE year = :2'||
2534 ' AND trg_per = ('||
2535 ' SELECT '||l_column_name(i)||
2536 ' FROM bsc_db_calendar '||
2537 ' WHERE '||h_base_calendar_col_name||' = :3'||
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'||
2547 ' FROM bsc_db_calendar ';
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'||
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';
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, '||
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, ';
2581 for j in 1..x_num_data_columns loop
2582 if x_data_measure_types(j)=1 then
2583 --if these are total columns, select null.
2584 l_stmt:=l_stmt||'null,';
2585 else
2586 --please note that there is no aggregation for balance values
2587 l_stmt:=l_stmt||h_data_columns_temp(j)||',';
2588 end if;
2589 end loop;
2590 l_stmt:=substr(l_stmt,1,length(l_stmt)-1);
2591 l_stmt:=l_stmt||' from bsc_tmp_base,bsc_tmp_per_change_bal where '||
2592 'bsc_tmp_base.year=bsc_tmp_per_change_bal.year and '||
2593 'bsc_tmp_base.period=bsc_tmp_per_change_bal.src_per and '||
2594 'bsc_tmp_base.periodicity_id=:2';
2595 --please note that we select the lowest level data from bsc_tmp_base
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
2605 --we are going to update bsc_tmp_base
2606 l_stmt:='insert into bsc_tmp_base_update (row_id,';
2607 for j in 1..x_num_data_columns loop
2608 if x_data_measure_types(j)<>1 then
2609 l_stmt:=l_stmt||h_data_columns_temp(j)||',';
2610 end if;
2611 end loop;
2612 -- Fix bug#4097873: perf fix. remove ordered hint
2613 l_stmt:=substr(l_stmt,1,length(l_stmt)-1)||')'||
2614 ' select /*+ use_hash(bsc_tmp_base) use_hash(bsc_tmp_base_bal)*/'||
2615 ' bsc_tmp_base.rowid,';
2616 for j in 1..x_num_data_columns loop
2617 if x_data_measure_types(j)<>1 then
2618 l_stmt:=l_stmt||'bsc_tmp_base_bal.'||h_data_columns_temp(j)||',';
2619 end if;
2620 end loop;
2621 l_stmt:=substr(l_stmt,1,length(l_stmt)-1)||
2622 ' from bsc_tmp_base, bsc_tmp_base_bal'||
2623 ' where ';
2624 for j in 1..x_num_key_columns loop
2625 l_stmt:=l_stmt||'bsc_tmp_base_bal.'||h_key_columns_temp(j)||'=bsc_tmp_base.'||
2626 h_key_columns_temp(j)||' and ';
2627 end loop;
2628 l_stmt:=l_stmt||'bsc_tmp_base_bal.periodicity_id=bsc_tmp_base.periodicity_id and ';
2629 l_stmt:=l_stmt||'bsc_tmp_base_bal.period=bsc_tmp_base.period and ';
2630 l_stmt:=l_stmt||'bsc_tmp_base_bal.year=bsc_tmp_base.year and ';
2631 l_stmt:=l_stmt||'bsc_tmp_base_bal.type=bsc_tmp_base.type and ';
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;
2641 end loop;--for i in 1..l_number_periodicity loop
2642 --Fix bug#3875046: Now we can update bsc_tmp_base
2643 l_stmt:='update /*+ordered use_nl(bsc_tmp_base)*/ bsc_tmp_base set(';
2644 for j in 1..x_num_data_columns loop
2645 if x_data_measure_types(j)<>1 then
2646 l_stmt:=l_stmt||h_data_columns_temp(j)||',';
2647 end if;
2648 end loop;
2649 l_stmt:=substr(l_stmt,1,length(l_stmt)-1)||')=(select ';
2650 for j in 1..x_num_data_columns loop
2651 if x_data_measure_types(j)<>1 then
2652 l_stmt:=l_stmt||h_data_columns_temp(j)||',';
2653 end if;
2654 end loop;
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,
2664 --we dont want to touch the lowest periodicity data in the base fact
2665 l_periodicity_stmt:=null;
2666 for i in 1..l_number_periodicity loop
2667 l_periodicity_stmt:=l_periodicity_stmt||l_periodicity(i)||',';
2668 end loop;
2669 l_periodicity_stmt:=substr(l_periodicity_stmt,1,length(l_periodicity_stmt)-1);
2670 --------------------------------------
2671 -- Fix bug#3911201: If x_correction_flag is TRUE (higher periodicities are being full refreshed)
2672 -- then we do not need to add/substract, we just update base table from bsc_tmp_base
2673 if x_correction_flag then
2674 l_table:='bsc_tmp_base';
2675
2676 -- Bug#3842096 Insert into a temporal table with row_id and then update the base table
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)||',';
2686 end loop;
2687 l_stmt := substr(l_stmt,1,length(l_stmt)-1);
2688 l_stmt := l_stmt||')'||
2689 ' SELECT '||x_base_tbl||'.rowid,';
2690 for j in 1..x_num_data_columns loop
2691 l_stmt:=l_stmt||l_table||'.'||h_data_columns_temp(j)||',';
2692 end loop;
2693 l_stmt := substr(l_stmt,1,length(l_stmt)-1)||
2694 ' FROM '||l_table||', '||x_base_tbl||
2695 ' WHERE ';
2696 for j in 1..x_num_key_columns loop
2697 l_stmt := l_stmt||x_base_tbl||'.'||x_key_columns(j)||'='||l_table||'.'||h_key_columns_temp(j)||' and ';
2698 end loop;
2699 l_stmt := l_stmt||x_base_tbl||'.periodicity_id='||l_table||'.periodicity_id and '||
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
2709 l_stmt:=l_stmt||x_data_columns(j)||',';
2710 end loop;
2711 l_stmt:=substr(l_stmt,1,length(l_stmt)-1)||')=(SELECT ';
2712 for j in 1..x_num_data_columns loop
2713 l_stmt:=l_stmt||'bsc_tmp_base_update.'||h_data_columns_temp(j)||',';
2714 end loop;
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
2724 if i=1 then
2725 l_table:='bsc_tmp_base';
2726 l_op:='+';
2727 else
2728 l_table:='bsc_tmp_base_bu';
2729 l_op:='-';
2730 end if;
2731 if i=2 then
2732 --if there are only balance columns, we dont need to do this step of subtraction
2733 l_found:=false;
2734 for j in 1..x_num_data_columns loop
2735 if x_data_measure_types(j)=1 then
2736 l_found:=true;
2737 exit;
2738 end if;
2739 end loop;
2740 if l_found=false then
2741 exit;--from the for loop for i in 1..2 loop
2742 end if;
2743 end if;
2744
2745 -- Bug#3842096 Insert into a temporal table with row_id and then update the base table
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
2755 l_stmt := 'INSERT INTO bsc_tmp_base_update (row_id,';
2756 for j in 1..x_num_data_columns loop
2757 if i=1 OR (i=2 and x_data_measure_types(j)=1) then
2758 l_stmt := l_stmt||h_data_columns_temp(j)||',';
2759 end if;
2760 end loop;
2761 l_stmt := substr(l_stmt,1,length(l_stmt)-1);
2762 l_stmt := l_stmt||')'||
2763 ' SELECT '||x_base_tbl||'.rowid,';
2764 for j in 1..x_num_data_columns loop
2765 if i=1 OR (i=2 and x_data_measure_types(j)=1) then
2766 --Fix bug#4235448: for balance measures we should allow null
2767 if x_data_measure_types(j) = 1 then
2768 l_stmt:=l_stmt||'nvl('||l_table||'.'||h_data_columns_temp(j)||',0),';
2769 else
2770 l_stmt:=l_stmt||l_table||'.'||h_data_columns_temp(j)||',';
2771 end if;
2772 end if;
2773 end loop;
2774 l_stmt := substr(l_stmt,1,length(l_stmt)-1)||
2775 ' FROM '||l_table||', '||x_base_tbl||
2776 ' WHERE ';
2777 for j in 1..x_num_key_columns loop
2778 l_stmt := l_stmt||x_base_tbl||'.'||x_key_columns(j)||'='||l_table||'.'||h_key_columns_temp(j)||' and ';
2779 end loop;
2780 l_stmt := l_stmt||x_base_tbl||'.periodicity_id='||l_table||'.periodicity_id and '||
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
2790 if i=1 OR (i=2 and x_data_measure_types(j)=1) then
2791 l_stmt:=l_stmt||x_data_columns(j)||',';
2792 end if;
2793 end loop;
2794 l_stmt:=substr(l_stmt,1,length(l_stmt)-1)||')=(SELECT ';
2795 for j in 1..x_num_data_columns loop
2796 if i=1 then
2797 if x_data_measure_types(j)=1 then
2798 --total column
2799 l_stmt := l_stmt||'nvl('||x_base_tbl||'.'||x_data_columns(j)||',0) '||l_op||
2800 ' bsc_tmp_base_update.'||h_data_columns_temp(j)||',';
2801 else
2802 --balance column
2803 --Fix bug#4235448: need decode.
2804 --l_stmt:=l_stmt||'bsc_tmp_base_update.'||h_data_columns_temp(j)||',';
2805 l_stmt:=l_stmt||'decode(bsc_tmp_base_update.'||h_data_columns_temp(j)||',-999999999999,'||
2806 x_base_tbl||'.'||x_data_columns(j)||',bsc_tmp_base_update.'||h_data_columns_temp(j)||'),';
2807 end if;
2808 else
2809 --here, there can be no balance since its bsc_tmp_base_bu
2810 --we must never subtract the balance column
2811 if x_data_measure_types(j)=1 then
2812 --total column
2813 l_stmt := l_stmt||'nvl('||x_base_tbl||'.'||x_data_columns(j)||',0) '||l_op||
2814 ' bsc_tmp_base_update.'||h_data_columns_temp(j)||',';
2815 end if;
2816 end if;
2817 end loop;
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!!!
2827 --we can now insert the new data
2828 --we cannot have parallel insert due to commit issue. we can have a commit only after the
2829 --insert is complete
2830 -- Fix bug#3875046: Replace the insert stmt with following logic
2831 -- a. get number of rows of the base table. Since the base table is analyzed we can do:
2832 select nvl(num_rows,0)
2833 into h_num_rows_base
2834 from all_tables
2835 where table_name = x_base_tbl and owner= BSC_APPS.BSC_APPS_SCHEMA;
2836 --b. see what hint to use
2837 -- Fix bug#4097873: remove ordered hint
2838 if h_num_rows_base = 0 then
2839 l_hint := '/*+ use_hash(bsc_tmp_base) use_hash('||x_base_tbl||') */';
2840 else
2841 if (h_num_rows_tmp/h_num_rows_base) > 0.1 then
2842 l_hint := '/*+ use_hash(bsc_tmp_base) use_hash('||x_base_tbl||') */';
2843 else
2844 l_hint:= '/*+ordered */';
2845 end if;
2846 end if;
2847 --c. insert into bsc_tmp_base_rowid
2848 l_stmt := 'insert into bsc_tmp_base_rowid(row_id_tmp, row_id_base)'||
2849 ' select '||l_hint||' bsc_tmp_base.rowid, '||x_base_tbl||'.rowid'||
2850 ' from bsc_tmp_base, '||x_base_tbl||
2851 ' where ';
2852 for i in 1..x_num_key_columns loop
2853 l_stmt:=l_stmt||'bsc_tmp_base.'||h_key_columns_temp(i)||'='||x_base_tbl||'.'||x_key_columns(i)||' (+) and ';
2854 end loop;
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||')'||
2864 ' select /*+ use_hash(bsc_tmp_base_rowid) use_hash(bsc_tmp_base)*/ '||
2865 h_lst_key_columns_temp||'PERIODICITY_ID,YEAR,TYPE,PERIOD,';
2866 --Fix bug#4235448: insert null when find -999999999 for balance measure.
2867 for k in 1..x_num_data_columns loop
2868 if x_data_measure_types(k)=1 then
2869 l_stmt:=l_stmt||h_data_columns_temp(k)||',';
2870 else --for balance
2871 l_stmt:=l_stmt||'decode('||h_data_columns_temp(k)||',-999999999999,null,'||h_data_columns_temp(k)||'),';
2872 end if;
2873 end loop;
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
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
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
2900 ROLLBACK;
2901
2902 BSC_MESSAGE.Add(
2903 X_Message => h_sql,
2904 X_Source => 'BSC_UPDATE_BASE.Update_Base_Table');
2905
2906 BSC_MESSAGE.Add(
2907 X_Message => SQLERRM,
2908 X_Source => 'BSC_UPDATE_BASE.Update_Base_Table');
2909 RETURN FALSE;
2910
2911 END Update_Base_Table;
2912
2913
2914 -- AW_INTEGRATION: New function
2915 /*===========================================================================+
2916 | FUNCTION Get_Base_AW_Table_Name
2917 +============================================================================*/
2918 FUNCTION Get_Base_AW_Table_Name(
2919 x_base_tbl IN VARCHAR2
2920 ) RETURN VARCHAR2 IS
2921 h_aw_table VARCHAR2(30);
2922 BEGIN
2923 h_aw_table := x_base_tbl||'_AW';
2924 RETURN h_aw_table;
2925 END Get_Base_AW_Table_Name;
2926
2927 END BSC_UPDATE_BASE;
[Home] [Help]