1 PACKAGE BODY BSC_UPDATE_SUM AS
2 /* $Header: BSCDSUMB.pls 120.5 2006/04/03 14:08:22 meastmon noship $ */
3
4
5 /*===========================================================================+
6 | FUNCTION Calculate_Period_Summary_Table
7 +============================================================================*/
8 FUNCTION Calculate_Period_Summary_Table(
9 x_periodicity IN NUMBER,
10 x_origin_periodicity IN NUMBER,
11 x_origin_period IN NUMBER,
12 x_current_fy IN NUMBER
13 ) RETURN NUMBER IS
14
15 e_unexpected_error EXCEPTION;
16
17 TYPE t_cursor IS REF CURSOR;
18 h_cursor t_cursor;
19
20 h_sql VARCHAR2(2000);
21
22 h_period_summary_table NUMBER;
23 h_origin_calendar_col_name VARCHAR2(30);
24 h_calendar_col_name VARCHAR2(30);
25
26 h_yearly_flag NUMBER;
27 h_edw_flag NUMBER;
28 h_calendar_id NUMBER;
29
30 BEGIN
31 h_yearly_flag := 0;
32 h_edw_flag := 0;
33
34 h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
35 h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_periodicity);
36
37 IF x_origin_periodicity = x_periodicity THEN
38 -- There is no periodicity change. The period of summary table
39 -- is the origin period
40 h_period_summary_table := x_origin_period;
41
42 ELSIF h_yearly_flag = 1 THEN
43 -- If periodicity of summary table is Annual the period is
44 -- the current fiscal year
45 h_period_summary_table := x_current_fy;
46
47 ELSE
48 -- There is periodicity change
49 h_edw_flag := BSC_UPDATE_UTIL.Get_Periodicity_EDW_Flag(x_periodicity);
50
51 IF h_edw_flag = 0 THEN
52 -- BSC Periodicity
53 -- Note: We suppose that the change of periodicity is allowed
54 -- (see bsc_sys_periodicites)
55 h_origin_calendar_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_origin_periodicity);
56 h_calendar_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity);
57
58 h_sql := 'SELECT DISTINCT '||h_calendar_col_name||
59 ' FROM bsc_db_calendar'||
60 ' WHERE calendar_id = :1 AND year = :2'||
61 ' AND '||h_origin_calendar_col_name||' = :3';
62
63 OPEN h_cursor FOR h_sql USING h_calendar_id, x_current_fy, x_origin_period;
64 FETCH h_cursor INTO h_period_summary_table;
65 IF h_cursor%NOTFOUND THEN
66 RAISE e_unexpected_error;
67 END IF;
68 CLOSE h_cursor;
69 ELSE
70 -- EDW Periodicity
71 -- Use BSC_EDW_TIME_MAP table which was previously created for
72 -- x_periodicity_input_table --> x_periodicity_base_table
73 h_sql := 'SELECT bsc_target'||
74 ' FROM bsc_edw_time_map'||
75 ' WHERE year = :1 AND bsc_source = :2';
76 OPEN h_cursor FOR h_sql USING x_current_fy, x_origin_period;
77 FETCH h_cursor INTO h_period_summary_table;
78 IF h_cursor%NOTFOUND THEN
79 RAISE e_unexpected_error;
80 END IF;
81 CLOSE h_cursor;
82 END IF;
83 END IF;
84
85 RETURN h_period_summary_table;
86
87 EXCEPTION
88 WHEN e_unexpected_error THEN
89 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_SUMTBLE_PERIOD_CALC_FAILED'),
90 x_source => 'BSC_UPDATE_SUM.Calculate_Period_Summary_Table');
91 RETURN NULL;
92
93 WHEN OTHERS THEN
94 BSC_MESSAGE.Add(x_message => SQLERRM,
95 x_source => 'BSC_UPDATE_SUM.Calculate_Period_Summary_Table');
96 RETURN NULL;
97
98 END Calculate_Period_Summary_Table;
99
100
101 /*===========================================================================+
102 | FUNCTION Calculate_Sum_Table
103 +============================================================================*/
104 FUNCTION Calculate_Sum_Table(
105 x_sum_table IN VARCHAR2
106 ) RETURN BOOLEAN IS
107
108 e_unexpected_error EXCEPTION;
109
110 h_b BOOLEAN;
111
112 h_current_fy NUMBER;
113
114 h_origin_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
115 h_num_origin_tables NUMBER;
116
117 h_origin_period NUMBER;
118 h_origin_periodicity NUMBER;
119
120 h_period NUMBER;
121 h_periodicity NUMBER;
122
123 -- Generation type: Total or balance
124 h_generation_type NUMBER;
125
126 -- Data columns information
127 h_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
128 h_data_formulas BSC_UPDATE_UTIL.t_array_of_varchar2;
129 h_data_proj_methods BSC_UPDATE_UTIL.t_array_of_number;
130 h_data_measure_types BSC_UPDATE_UTIL.t_array_of_number;
131 h_num_data_columns NUMBER;
132
133 -- Key column information
134 h_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
135 h_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
136 h_source_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
137 h_source_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
138 h_num_key_columns NUMBER;
139
140 -- Key column information for the origin tables
141 -- All origin tables have the same dissagregation
142 h_key_columns_ori BSC_UPDATE_UTIL.t_array_of_varchar2;
143 h_key_dim_tables_ori BSC_UPDATE_UTIL.t_array_of_varchar2;
144 h_source_columns_ori BSC_UPDATE_UTIL.t_array_of_varchar2;
145 h_source_dim_tables_ori BSC_UPDATE_UTIL.t_array_of_varchar2;
146 h_num_key_columns_ori NUMBER;
147
148 -- Projection flag
149 h_projection_flag VARCHAR2(3);
150
151 -- Number of year and previous years of the table
152 h_num_of_years NUMBER;
153 h_previous_years NUMBER;
154
155 -- Zero code calculation method
156 h_zero_code_calc_method NUMBER;
157
158 h_calendar_id NUMBER;
159 h_calendar_edw_flag NUMBER;
160 h_yearly_flag NUMBER;
161 h_start_year NUMBER;
162 h_end_year NUMBER;
163
164 h_target_flag NUMBER;
165 BEGIN
166 h_num_origin_tables := 0;
167 h_num_data_columns := 0;
168 h_num_key_columns := 0;
169 h_num_key_columns_ori := 0;
170 h_projection_flag := 'NO';
171 h_yearly_flag := 0;
172
173
174 -- Initialize the array h_origin_tables with the tables from where
175 -- the summary table is generated. There is at least one origin table.
176 IF NOT Get_Origin_Tables(x_sum_table, h_origin_tables, h_num_origin_tables) THEN
177 RAISE e_unexpected_error;
178 END IF;
179
180 -- Get the minimum period of the origin tables
181 h_origin_period := Get_Minimun_Origin_Period(h_origin_tables, h_num_origin_tables);
182 IF h_origin_period IS NULL THEN
183 RAISE e_unexpected_error;
184 END IF;
185
186 -- Get the periodicity of the origin tables.
187 -- If a summary table comes from several tables, all origin tables have the
188 -- same periodicity
189 h_origin_periodicity := BSC_UPDATE_UTIL.Get_Table_Periodicity(h_origin_tables(1));
190 IF h_origin_periodicity IS NULL THEN
191 RAISE e_unexpected_error;
192 END IF;
193
194 -- Get the periodicity of the summary table
195 h_periodicity := BSC_UPDATE_UTIL.Get_Table_Periodicity(x_sum_table);
196 IF h_periodicity IS NULL THEN
197 RAISE e_unexpected_error;
198 END IF;
199
200 -- Get the calendar id of the summary table
201 h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(h_periodicity);
202 h_calendar_edw_flag := BSC_UPDATE_UTIL.Get_Calendar_EDW_Flag(h_calendar_id);
203
204 -- Get the current fiscal year
205 h_current_fy := BSC_UPDATE_UTIL.Get_Calendar_Fiscal_Year(h_calendar_id);
206
207 -- Get the number of years and previous years of the table
208 IF NOT BSC_UPDATE_UTIL.Get_Table_Range_Of_Years(x_sum_table, h_num_of_years, h_previous_years) THEN
209 RAISE e_unexpected_error;
210 END IF;
211
212 --BSC-MV Note: EDW logic need to be reviews in the future
213 -- Create BSC_EDW_TIME_MAP table, in case there is change of periodicity
214 -- on EDW tables.
215 --IF (h_periodicity <> h_origin_periodicity) AND (h_calendar_edw_flag = 1) THEN
216 -- -- There is change of periodicity in a EDW calendar
217 -- h_start_year := h_current_fy - h_previous_years;
218 -- h_end_year := h_start_year + h_num_of_years - 1;
219 --
220 -- -- Create table to transform EDW periodicities
221 -- BSC_INTEGRATION_APIS.Translate_EDW_Time(h_calendar_id,
222 -- TO_CHAR(h_start_year)||'-'||TO_CHAR(h_end_year),
223 -- h_origin_periodicity,
224 -- h_periodicity);
225 -- IF BSC_APPS.CheckError('BSC_INTEGRATION_APIS.Translate_EDW_Time') THEN
226 -- RAISE e_unexpected_error;
227 -- END IF;
228 --END IF;
229
230 -- Calculate the current period of summary table
231 h_period := Calculate_Period_Summary_Table(h_periodicity,
232 h_origin_periodicity,
233 h_origin_period,
234 h_current_fy);
235 IF h_period IS NULL THEN
236 RAISE e_unexpected_error;
237 END IF;
238
239 -- Retrieve information of summary table to be processed
240
241 -- Summary table generation type: Normal (Total or Balance data fields = 1 (default))
242 h_generation_type := BSC_UPDATE_UTIL.Get_Table_Generation_Type(x_sum_table);
243 IF h_generation_type IS NULL THEN
244 RAISE e_unexpected_error;
245 END IF;
246
247 -- Information of data columns of summary table
248 IF NOT BSC_UPDATE_UTIL.Get_Information_Data_Columns(x_sum_table,
249 h_data_columns,
250 h_data_formulas,
251 h_data_proj_methods,
252 h_data_measure_types,
253 h_num_data_columns) THEN
254 RAISE e_unexpected_error;
255 END IF;
256
257 -- Information of key columns of summary table
258 IF NOT BSC_UPDATE_UTIL.Get_Information_Key_Columns(x_sum_table,
259 h_key_columns,
260 h_key_dim_tables,
261 h_source_columns,
262 h_source_dim_tables,
263 h_num_key_columns) THEN
264 RAISE e_unexpected_error;
265 END IF;
266
267 -- Information of key columns of origin tables
268 -- All origin tables have the same dissagregation
269 -- I just need the array h_key_columns_ori.
270 IF NOT BSC_UPDATE_UTIL.Get_Information_Key_Columns(h_origin_tables(1),
271 h_key_columns_ori,
272 h_key_dim_tables_ori,
273 h_source_columns_ori,
274 h_source_dim_tables_ori,
275 h_num_key_columns_ori) THEN
276 RAISE e_unexpected_error;
277 END IF;
278
279 -- Create temporary tables used for calculation and tranformations
280
281 -- Projection
282 h_b := BSC_UPDATE_CALC.Table_Has_Proj_Calc(x_sum_table);
283 IF h_b IS NULL THEN
284 RAISE e_unexpected_error;
285 END IF;
286
287 IF h_b THEN
288 h_projection_flag := 'YES';
289 --ENH_PROJECTION_4235711: pass TRUE in x_trunc_proj_table parameter
290 IF NOT BSC_UPDATE_CALC.Create_Proj_Temps(h_periodicity,
291 h_current_fy,
292 h_num_of_years,
293 h_previous_years,
294 TRUE) THEN
295 RAISE e_unexpected_error;
296 END IF;
297 ELSE
298 h_projection_flag := 'NO';
299 END IF;
300
301 -- Note: Support of special codes calculation is pending
302 -- No temporary table for profit calculation.
303 -- No temporary tables for code zero calculation.
304
305 COMMIT;
306
307 -- Calculate the summary table
308 IF h_generation_type = 1 THEN
309 -- Calculate the summary table (support total or balance fields)
310 -- Fix bug#4177794. Need to pass h_period
311 IF NOT Calculate_Sum_Table_Total(x_sum_table,
312 h_key_columns,
313 h_key_dim_tables,
314 h_source_columns,
315 h_source_dim_tables,
316 h_num_key_columns,
317 h_data_columns,
318 h_data_formulas,
319 h_data_measure_types,
320 h_num_data_columns,
321 h_origin_tables,
322 h_num_origin_tables,
323 h_key_columns_ori,
324 h_num_key_columns_ori,
325 h_periodicity,
326 h_origin_periodicity,
327 h_period,
328 h_origin_period,
329 h_current_fy) THEN
330 RAISE e_unexpected_error;
331 END IF;
332 COMMIT;
333 END IF;
334
335 --BSC-MV Note: EDW logic need to be reviewed in the future
336 -- EDW Note: If this table is used directly by an EDW Kpi then this table has a corresponding
337 -- materialized view with actuals
338 --IF BSC_UPDATE_UTIL.Is_EDW_Kpi_Table(x_sum_table) THEN
339 -- -- Refresh materialized view
340 -- -- Refresh union view
341 -- -- Delete from BSC table any data existing in the materialized view
342 -- -- Period of the base table is the maximun reported in the materialized view
343 -- IF NOT BSC_UPDATE_CALC.Refresh_EDW_Views(x_sum_table,
344 -- h_key_columns,
345 -- h_num_key_columns,
346 -- h_data_columns,
347 -- h_num_data_columns,
348 -- h_current_fy,
349 -- h_periodicity,
350 -- h_period) THEN
351 -- RAISE e_unexpected_error;
352 -- END IF;
353 --END IF;
354
355 -- EDW Note: Materialized view already was filtered and already has zero codes for actuals
356 -- So we dont need to calculate filters or zero codes for the materialized view
357 -- No changes in this two functions.
358
359
360 -- Filter the table
361 IF NOT BSC_UPDATE_CALC.Apply_Filters(x_sum_table) THEN
362 RAISE e_unexpected_error;
363 END IF;
364 COMMIT;
365
366
367 -- Merge data from target tables
368 IF NOT BSC_UPDATE_CALC.Merge_Data_From_Tables(x_sum_table,
369 h_key_columns,
370 h_num_key_columns) THEN
371 RAISE e_unexpected_error;
372 END IF;
373 COMMIT;
374
375 -- Calculate projection
376 IF h_projection_flag = 'YES' THEN
377 -- AW_INTEGRATION: Pass FALSE to x_aw_flag and change_vector parameter of Calculate_Projection. This is not for AW.
378 IF NOT BSC_UPDATE_CALC.Calculate_Projection(x_sum_table,
379 h_periodicity,
380 h_period,
381 h_key_columns,
382 h_num_key_columns,
383 h_data_columns,
384 h_data_proj_methods,
385 h_num_data_columns,
386 h_current_fy,
387 h_num_of_years,
388 h_previous_years,
389 FALSE,
390 FALSE,
391 NULL) THEN
392 RAISE e_unexpected_error;
393 END IF;
394
395 COMMIT;
396 END IF;
397
398 -- Calculate special codes is pending
399
400 -- EDW Note: Materialized view already has profits for actuals
401 -- So we dont need to calculate profit for the materialized view
402 -- No changes in this function.
403
404 -- Calculate Profit
405 h_b := BSC_UPDATE_CALC.Table_Has_Profit_Calc(x_sum_table);
406 IF h_b IS NULL THEN
407 RAISE e_unexpected_error;
408 END IF;
409
410 IF h_b THEN
411 --AW_INTEGRATION: Pass FALSE to x_aw_flag and change vector. This is not for AW
412 IF NOT BSC_UPDATE_CALC.Calculate_Profit(x_sum_table,
413 h_key_columns,
414 h_key_dim_tables,
415 h_num_key_columns,
416 h_data_columns,
417 h_num_data_columns,
418 FALSE,
419 NULL) THEN
420 RAISE e_unexpected_error;
421 END IF;
422
423 COMMIT;
424 END IF;
425
426 -- Calculate zero codes
427 --Fix bug#3542344 : Zero codes should be the last step to make it consistent with MV architecture
428 --Also:
429 -- - If this table is receiving targets we need to re-calculate zero code for the keys that already
430 -- has zero code in the summary table.
431 -- - We do not need to calculate zero code on tables used only for targets
432 h_target_flag := BSC_UPDATE_UTIL.Get_Table_Target_Flag(x_sum_table);
433 IF h_target_flag = 0 THEN
434 h_zero_code_calc_method := 4; -- This is the only zero code method supported;
435 IF NOT BSC_UPDATE_CALC.Calculate_Zero_Code(x_sum_table,
436 h_zero_code_calc_method,
437 h_key_columns,
438 h_num_key_columns,
439 NULL) THEN
440 RAISE e_unexpected_error;
441 END IF;
442 COMMIT;
443 END IF;
444
445 -- Store the update period of summary table
446 UPDATE
447 bsc_db_tables
448 SET
449 current_period = h_period
450 WHERE
451 table_name = x_sum_table;
452
453 COMMIT;
454
455 RETURN TRUE;
456
457 EXCEPTION
458 WHEN e_unexpected_error THEN
459 ROLLBACK;
460 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_SUMTABLE_CALC_FAILED'),
461 x_source => 'BSC_UPDATE_SUM.Calculate_Sum_Table');
462 RETURN FALSE;
463
464 WHEN OTHERS THEN
465 ROLLBACK;
466 BSC_MESSAGE.Add(x_message => SQLERRM,
467 x_source => 'BSC_UPDATE_SUM.Calculate_Sum_Table');
468 RETURN FALSE;
469
470 END Calculate_Sum_Table;
471
472 --LOCKING: new function
473 /*===========================================================================+
474 | FUNCTION Calculate_Sum_Table_AT
475 +============================================================================*/
476 FUNCTION Calculate_Sum_Table_AT(
477 x_sum_table IN VARCHAR2
478 ) RETURN BOOLEAN IS
479 PRAGMA AUTONOMOUS_TRANSACTION;
480 h_b BOOLEAN;
481 BEGIN
482 h_b := Calculate_Sum_Table(x_sum_table);
483 commit; -- all autonomous transaction needs to commit
484 RETURN h_b;
485 END Calculate_Sum_Table_AT;
486
487 /*===========================================================================+
488 | FUNCTION Calculate_Sum_Table_MV
489 +============================================================================*/
490 FUNCTION Calculate_Sum_Table_MV(
491 x_sum_table IN VARCHAR2,
492 x_calculated_sys_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
493 x_num_calculated_sys_tables IN NUMBER,
494 x_system_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
495 x_num_system_tables IN NUMBER
496 ) RETURN BOOLEAN IS
497
498 e_unexpected_error EXCEPTION;
499
500 h_b BOOLEAN;
501
502 h_current_fy NUMBER;
503
504 h_origin_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
505 h_num_origin_tables NUMBER;
506
507 h_origin_period NUMBER;
508 h_origin_periodicity NUMBER;
509
510 h_period NUMBER;
511 h_periodicity NUMBER;
512
513 -- Generation type: Total or balance
514 h_generation_type NUMBER;
515
516 -- Data columns information
517 h_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
518 h_data_formulas BSC_UPDATE_UTIL.t_array_of_varchar2;
519 h_data_proj_methods BSC_UPDATE_UTIL.t_array_of_number;
520 h_data_measure_types BSC_UPDATE_UTIL.t_array_of_number;
521 h_num_data_columns NUMBER;
522
523 -- Key column information
524 h_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
525 h_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
526 h_source_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
527 h_source_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
528 h_num_key_columns NUMBER;
529
530 -- Key column information for the origin tables
531 -- All origin tables have the same dissagregation
532 h_key_columns_ori BSC_UPDATE_UTIL.t_array_of_varchar2;
533 h_key_dim_tables_ori BSC_UPDATE_UTIL.t_array_of_varchar2;
534 h_source_columns_ori BSC_UPDATE_UTIL.t_array_of_varchar2;
535 h_source_dim_tables_ori BSC_UPDATE_UTIL.t_array_of_varchar2;
536 h_num_key_columns_ori NUMBER;
537
538 -- Projection flag
539 h_projection_flag VARCHAR2(3);
540
541 -- Number of year and previous years of the table
542 h_num_of_years NUMBER;
543 h_previous_years NUMBER;
544
545 -- Zero code calculation method
546 h_zero_code_calc_method NUMBER;
547
548 h_calendar_id NUMBER;
549 h_calendar_edw_flag NUMBER;
550 h_yearly_flag NUMBER;
551 h_start_year NUMBER;
552 h_end_year NUMBER;
553
554 h_mv_name VARCHAR2(30);
555 e_error_refresh EXCEPTION;
556 e_error_refresh_zero EXCEPTION;
557 h_error_refresh VARCHAR2(2000);
558
559 CURSOR c_pt_name (p_sum_table VARCHAR2) IS
560 SELECT DISTINCT projection_data
561 FROM bsc_kpi_data_tables
562 WHERE table_name = p_sum_table;
563
564 h_pt_name VARCHAR2(30);
565 h_process_pt BOOLEAN;
566 h_origin_pt_name VARCHAR2(30);
567 h_origin_pts BSC_UPDATE_UTIL.t_array_of_varchar2;
568 h_num_origin_pts NUMBER;
569
570 CURSOR c_other_tables (p_pt_name VARCHAR2, p_table_name VARCHAR2) IS
571 SELECT DISTINCT kt.table_name, t.project_flag
572 FROM bsc_kpi_data_tables kt, bsc_db_tables t
573 WHERE kt.projection_data = p_pt_name AND
574 kt.table_name <> p_table_name AND
575 kt.table_name = t.table_name;
576
577 h_other_table_name VARCHAR2(30);
578 h_other_project_flag NUMBER;
579
580 h_i NUMBER;
581 h_j NUMBER;
582
583 TYPE t_pt_periodicity IS RECORD (
584 periodicity_id NUMBER,
585 yearly_flag NUMBER,
586 current_period NUMBER,
587 source_periodicity NUMBER,
588 source_current_period NUMBER,
589 calculated BOOLEAN
590 );
591
592 TYPE t_array_pt_periodicities IS TABLE OF t_pt_periodicity
593 INDEX BY BINARY_INTEGER;
594
595 h_arr_pt_periodicities t_array_pt_periodicities;
596 h_num_pt_periodicities NUMBER;
597
598 CURSOR c_pt_periodicities (p_pt_name VARCHAR2) IS
599 SELECT DISTINCT p.periodicity_id, p.yearly_flag, t.current_period
600 FROM bsc_kpi_data_tables kt, bsc_sys_periodicities p, bsc_db_tables t
601 WHERE kt.projection_data = p_pt_name AND
602 kt.periodicity_id = p.periodicity_id AND
603 kt.table_name = t.table_name;
604
605 h_periodicity_id NUMBER;
606 h_current_period NUMBER;
607
608 h_exit_cond BOOLEAN;
609 h_can_calculate BOOLEAN;
610
611 h_project_flag NUMBER;
612
613 BEGIN
614 h_num_origin_tables := 0;
615 h_num_data_columns := 0;
616 h_num_key_columns := 0;
617 h_num_key_columns_ori := 0;
618 h_projection_flag := 'NO';
619 h_yearly_flag := 0;
620 h_pt_name := NULL;
621 h_origin_pt_name := NULL;
622 h_num_origin_pts := 0;
623 h_num_pt_periodicities := 0;
624
625
626 -- Initialize the array h_origin_tables with the tables from where
627 -- the summary table is generated. There is at least one origin table.
628 IF NOT Get_Origin_Tables(x_sum_table, h_origin_tables, h_num_origin_tables) THEN
629 RAISE e_unexpected_error;
630 END IF;
631
632 -- Get the minimum period of the origin tables
633 h_origin_period := Get_Minimun_Origin_Period(h_origin_tables, h_num_origin_tables);
634 IF h_origin_period IS NULL THEN
635 RAISE e_unexpected_error;
636 END IF;
637
638 -- Get the periodicity of the origin tables.
639 -- If a summary table comes from several tables, all origin tables have the
640 -- same periodicity
641 h_origin_periodicity := BSC_UPDATE_UTIL.Get_Table_Periodicity(h_origin_tables(1));
642 IF h_origin_periodicity IS NULL THEN
643 RAISE e_unexpected_error;
644 END IF;
645
646 -- Get the periodicity of the summary table
647 h_periodicity := BSC_UPDATE_UTIL.Get_Table_Periodicity(x_sum_table);
648 IF h_periodicity IS NULL THEN
649 RAISE e_unexpected_error;
650 END IF;
651
652 -- Get the calendar id of the summary table
653 h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(h_periodicity);
654 h_calendar_edw_flag := BSC_UPDATE_UTIL.Get_Calendar_EDW_Flag(h_calendar_id);
655
656 -- Get the current fiscal year
657 h_current_fy := BSC_UPDATE_UTIL.Get_Calendar_Fiscal_Year(h_calendar_id);
658
659 -- Get the number of years and previous years of the table
660 IF NOT BSC_UPDATE_UTIL.Get_Table_Range_Of_Years(x_sum_table, h_num_of_years, h_previous_years) THEN
661 RAISE e_unexpected_error;
662 END IF;
663
664 -- Calculate the current period of summary table
665 h_period := Calculate_Period_Summary_Table(h_periodicity,
666 h_origin_periodicity,
667 h_origin_period,
668 h_current_fy);
669 IF h_period IS NULL THEN
670 RAISE e_unexpected_error;
671 END IF;
672
673 -- Retrieve information of summary table to be processed
674
675 -- BSC-MV Note: In this architecture none of the summary tables exists and
676 -- generation type is always -1. For now this property is not used in this procedure.
677
678 -- BSC-MV Note: All summary tables are implemented as MV/Views
679 -- We need to refresh the MV corresponding to this summary table
680 -- Make sure to refresh the MV only one time. Remember that same MV
681 -- has data for different periodicities.
682 -- Note: The api to refresh the MV does not fail if the MV does not exists
683 -- or if the MV is actually a normal view
684 h_mv_name := BSC_BIA_WRAPPER.Get_Sum_Table_MV_Name(x_sum_table);
685 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_mv_name,
686 g_refreshed_mvs,
687 g_num_refreshed_mvs) THEN
688
689 IF NOT BSC_BIA_WRAPPER.Refresh_Summary_MV(h_mv_name, h_error_refresh) THEN
690 RAISE e_error_refresh;
691 END IF;
692 COMMIT;
693
694 -- Also refresh the MV created for zero code (if it exists)
695 IF NOT Refresh_Zero_MVs(x_sum_table, h_mv_name, h_error_refresh) THEN
696 RAISE e_error_refresh_zero;
697 END IF;
698 COMMIT;
699
700 -- Add mv to array of refreshed mvs
701 g_num_refreshed_mvs := g_num_refreshed_mvs + 1;
702 g_refreshed_mvs(g_num_refreshed_mvs) := h_mv_name;
703 END IF;
704
705 -- Store the update period of summary table
706 UPDATE
707 bsc_db_tables
708 SET
709 current_period = h_period
710 WHERE
711 table_name = x_sum_table;
712 COMMIT;
713
714 -- BSC-MV Note: In this architecture we create projection tables
715 -- for Targets at different level. This tables will store the projection
716 -- at Kpi level.
717
718 -- Check if the table has a projection table (PT table) and
719 -- see if we need to process it (the PT table is processed only one time,
720 -- remember that the same PT table can correspond to multiple
721 -- summary tables)
722 -- Also, the Projection Table must be processed only when all other summary
723 -- tables for the same Projection Table has been calcualted. We need that
724 -- the current period had been updated before we can calculate the projection.
725 h_process_pt := FALSE;
726 OPEN c_pt_name(x_sum_table);
727 FETCH c_pt_name INTO h_pt_name;
728 IF c_pt_name%NOTFOUND THEN
729 h_pt_name := NULL;
730 END IF;
731 CLOSE c_pt_name;
732
733 IF h_pt_name IS NOT NULL THEN
734 -- We can calculate the Projection Table only when all other
735 -- summary tables associated to the PT table has been calculated before
736
737 h_process_pt := TRUE;
738
739 -- Get the projection flag of this table
740 SELECT project_flag INTO h_project_flag
741 FROM bsc_db_tables
742 WHERE table_name = x_sum_table;
743
744 IF h_project_flag = 1 THEN
745 h_projection_flag := 'YES';
746 ELSE
747 h_projection_flag := 'NO';
748 END IF;
749
750 OPEN c_other_tables(h_pt_name, x_sum_table);
751 LOOP
752 FETCH c_other_tables INTO h_other_table_name, h_other_project_flag;
753 EXIT WHEN c_other_tables%NOTFOUND;
754
755 -- If at least one of the tables needs projection, then we calcualte projection
756 -- for all the periodicities
757 IF h_other_project_flag = 1 THEN
758 h_projection_flag := 'YES';
759 END IF;
760
761 IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_other_table_name,
762 x_system_tables,
763 x_num_system_tables) THEN
764 -- The other table was or is going to be calculated in this process
765 -- Now check that it was already calculated
766 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_other_table_name,
767 x_calculated_sys_tables,
768 x_num_calculated_sys_tables) THEN
769 -- The other table has not been calculated, so we cannot process the PT
770 -- table right now
771 h_process_pt := FALSE;
772 EXIT;
773 END IF;
774 END IF;
775 END LOOP;
776 CLOSE c_other_tables;
777 END IF;
778
779 IF h_process_pt THEN
780 -- Information of data columns of summary table
781 IF NOT BSC_UPDATE_UTIL.Get_Information_Data_Columns(x_sum_table,
782 h_data_columns,
783 h_data_formulas,
784 h_data_proj_methods,
785 h_data_measure_types,
786 h_num_data_columns) THEN
787 RAISE e_unexpected_error;
788 END IF;
789
790 -- Information of key columns of summary table
791 IF NOT BSC_UPDATE_UTIL.Get_Information_Key_Columns(x_sum_table,
792 h_key_columns,
793 h_key_dim_tables,
794 h_source_columns,
795 h_source_dim_tables,
796 h_num_key_columns) THEN
797 RAISE e_unexpected_error;
798 END IF;
799
800 -- Information of key columns of origin tables
801 -- All origin tables have the same dissagregation
802 -- I just need the array h_key_columns_ori.
803 IF NOT BSC_UPDATE_UTIL.Get_Information_Key_Columns(h_origin_tables(1),
804 h_key_columns_ori,
805 h_key_dim_tables_ori,
806 h_source_columns_ori,
807 h_source_dim_tables_ori,
808 h_num_key_columns_ori) THEN
809 RAISE e_unexpected_error;
810 END IF;
811
812 IF h_projection_flag = 'NO' THEN
813 -- Rollup the projection table
814 -- We know that there is no change of periodicity, all periodicities are in the table
815 -- just consider PERIODICITY_TYPE and PERIOD_TYPE_ID as other two key columns...
816
817 -- Get the origin projection table name
818 -- By design there is only one origin projection table
819 OPEN c_pt_name(h_origin_tables(1));
820 FETCH c_pt_name INTO h_origin_pt_name;
821 IF c_pt_name%NOTFOUND THEN
822 h_origin_pt_name := NULL;
823 END IF;
824 CLOSE c_pt_name;
825
826 IF h_origin_pt_name IS NOT NULL THEN
827 h_origin_pts(1) := h_origin_pt_name;
828 h_num_origin_pts := 1;
829
830 -- Fix bug#4177794. Need to pass h_period
831 IF NOT Calculate_Sum_Table_Total(h_pt_name,
832 h_key_columns,
833 h_key_dim_tables,
834 h_source_columns,
835 h_source_dim_tables,
836 h_num_key_columns,
837 h_data_columns,
838 h_data_formulas,
839 h_data_measure_types,
840 h_num_data_columns,
841 h_origin_pts,
842 h_num_origin_pts,
843 h_key_columns_ori,
844 h_num_key_columns_ori,
845 h_periodicity,
846 h_origin_periodicity,
847 h_period,
848 h_origin_period,
849 h_current_fy) THEN
850 RAISE e_unexpected_error;
851 END IF;
852 COMMIT;
853 END IF;
854 ELSE
855 -- BSC-MV Note: Need to calculate projection in the projection table for all the periodicities.
856 -- For new architecture, we need to calculate projection
857 -- before calculate the zero codes. Merging the targets already happened in the MV
858
859 -- We need to calcualte the projection on base periodicities. In a PT table
860 -- there can be multiple base periodicities Example Montlhy and Weekly. For
861 -- higer periodicities we rollup the projection.
862 -- We always calculate projection on yearly periodicity.
863 -- The projection calculation must be in order. Fisrt the base periodicities
864 -- and the the others.
865
866 -- Truncate the table
867 BSC_UPDATE_UTIL.Truncate_Table(h_pt_name);
868
869 -- Initilize a global array with the relations between periodicities
870 IF NOT BSC_UPDATE_UTIL.Load_Periodicity_Rels THEN
871 RAISE e_unexpected_error;
872 END IF;
873
874 -- Get all the periodicities of the PT table
875 OPEN c_pt_periodicities(h_pt_name);
876 LOOP
877 FETCH c_pt_periodicities INTO h_periodicity_id, h_yearly_flag, h_current_period;
878 EXIT WHEN c_pt_periodicities%NOTFOUND;
879
880 h_num_pt_periodicities := h_num_pt_periodicities + 1;
881 h_arr_pt_periodicities(h_num_pt_periodicities).periodicity_id := h_periodicity_id;
882 h_arr_pt_periodicities(h_num_pt_periodicities).yearly_flag := h_yearly_flag;
883 h_arr_pt_periodicities(h_num_pt_periodicities).current_period := h_current_period;
884 h_arr_pt_periodicities(h_num_pt_periodicities).source_periodicity := NULL;
885 h_arr_pt_periodicities(h_num_pt_periodicities).source_current_period := NULL;
886 h_arr_pt_periodicities(h_num_pt_periodicities).calculated := FALSE;
887 END LOOP;
888 CLOSE c_pt_periodicities;
889
890 -- Get the source periodicity and source current period of each periodicity.
891 FOR h_i IN 1..h_num_pt_periodicities LOOP
892 FOR h_j IN 1..h_num_pt_periodicities LOOP
893 IF h_i <> h_j THEN
894 IF BSC_UPDATE_UTIL.Exist_Periodicity_Rel(h_arr_pt_periodicities(h_i).periodicity_id,
895 h_arr_pt_periodicities(h_j).periodicity_id) THEN
896 h_arr_pt_periodicities(h_i).source_periodicity := h_arr_pt_periodicities(h_j).periodicity_id;
897 h_arr_pt_periodicities(h_i).source_current_period := h_arr_pt_periodicities(h_j).current_period;
898 END IF;
899 END IF;
900 END LOOP;
901 END LOOP;
902
903 -- Calculate the projection in order
904 --ENH_PROJECTION_4235711: We can only truncate the projection table here
905 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJ_CALC');
906 LOOP
907 FOR h_i IN 1..h_num_pt_periodicities LOOP
908 IF NOT h_arr_pt_periodicities(h_i).calculated THEN
909 IF (h_arr_pt_periodicities(h_i).yearly_flag = 1) OR
910 (h_arr_pt_periodicities(h_i).source_periodicity IS NULL) THEN
911 -- The periodicity is yearly or it is a base periodicity
912 -- Calculate projection for this periodicity
913 IF NOT BSC_UPDATE_CALC.Create_Proj_Temps(h_arr_pt_periodicities(h_i).periodicity_id,
914 h_current_fy,
915 h_num_of_years,
916 h_previous_years,
917 FALSE) THEN
918 RAISE e_unexpected_error;
919 END IF;
920
921 -- AW_INTEGRATION: Pass FALSE to x_aw_flag and change vector parameter. This is not for AW
922 IF NOT BSC_UPDATE_CALC.Calculate_Projection(h_pt_name,
923 h_arr_pt_periodicities(h_i).periodicity_id,
924 h_arr_pt_periodicities(h_i).current_period,
925 h_key_columns,
926 h_num_key_columns,
927 h_data_columns,
928 h_data_proj_methods,
929 h_num_data_columns,
930 h_current_fy,
931 h_num_of_years,
932 h_previous_years,
933 FALSE,
934 FALSE,
935 NULL) THEN
936 RAISE e_unexpected_error;
937 END IF;
938 COMMIT;
939
940 h_arr_pt_periodicities(h_i).calculated := TRUE;
941 ELSE
942 -- The projection of this periodicity is calculated by rolling
943 -- up from the source periodicity
944
945 -- We can calculate it now only if the source periodicity is already calculated
946 h_can_calculate := FALSE;
947 FOR h_j IN 1..h_num_pt_periodicities LOOP
948 IF h_arr_pt_periodicities(h_j).periodicity_id = h_arr_pt_periodicities(h_i).source_periodicity THEN
949 IF h_arr_pt_periodicities(h_j).calculated THEN
950 h_can_calculate := TRUE;
951 END IF;
952 END IF;
953 END LOOP;
954
955 IF h_can_calculate THEN
956 IF NOT BSC_UPDATE_CALC.Create_Proj_Temps(h_arr_pt_periodicities(h_i).periodicity_id,
957 h_current_fy,
958 h_num_of_years,
959 h_previous_years,
960 FALSE) THEN
961 RAISE e_unexpected_error;
962 END IF;
963
964 --ENH_PROJECTION_4235711: no need to pass table name
965 IF NOT BSC_UPDATE_CALC.Rollup_Projection(
966 h_arr_pt_periodicities(h_i).periodicity_id,
967 h_arr_pt_periodicities(h_i).current_period,
968 h_arr_pt_periodicities(h_i).source_periodicity,
969 h_arr_pt_periodicities(h_i).source_current_period,
970 h_key_columns,
971 h_num_key_columns,
972 h_data_columns,
973 h_data_formulas,
974 h_data_measure_types,
975 h_num_data_columns,
976 h_current_fy,
977 FALSE) THEN
978 RAISE e_unexpected_error;
979 END IF;
980 COMMIT;
981
982 h_arr_pt_periodicities(h_i).calculated := TRUE;
983 END IF;
984 END IF;
985 END IF;
986 END LOOP;
987
988 -- Check if all the periodicities has been calculated
989 h_exit_cond := TRUE;
990 FOR h_i IN 1..h_num_pt_periodicities LOOP
991 IF NOT h_arr_pt_periodicities(h_i).calculated THEN
992 h_exit_cond := FALSE;
993 END IF;
994 END LOOP;
995
996 EXIT WHEN h_exit_cond;
997 END LOOP;
998
999 --ENH_PROJECTION_4235711: Merge projection into the PT table. Projection is calculated
1000 --in BSC_TMP_PROJ_CALC
1001 IF NOT BSC_UPDATE_CALC.Merge_Projection(h_pt_name,
1002 h_key_columns,
1003 h_num_key_columns,
1004 h_data_columns,
1005 h_num_data_columns,
1006 FALSE,
1007 FALSE) THEN
1008 RAISE e_unexpected_error;
1009 END IF;
1010
1011 -- Fix bug#4463132: Truncate temporary table after use
1012 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PROJ_CALC');
1013 END IF;
1014
1015 -- BSC-MV Note: There is no need to calculate filters here. The MV already is filtered
1016 -- and there is no cases where the summary table is based on T or B tables.
1017
1018 -- Calculate zero codes
1019 h_zero_code_calc_method := BSC_UPDATE_CALC.Get_Zero_Code_Calc_Method(x_sum_table);
1020 IF h_zero_code_calc_method IS NULL THEN
1021 RAISE e_unexpected_error;
1022 END IF;
1023
1024 IF h_zero_code_calc_method <> 0 THEN
1025 IF h_projection_flag = 'YES' THEN
1026 -- BSC-MV Note: In this architecture if the projection table exists
1027 -- it is only to store projection at kpi level. If the table
1028 -- has projection it only contains data with no zero codes (from the MV)
1029 -- So in this case we can calcualte the zero code in all required keys.
1030 IF NOT BSC_UPDATE_CALC.Calculate_Zero_Code(x_sum_table,
1031 h_zero_code_calc_method,
1032 h_key_columns,
1033 h_num_key_columns,
1034 NULL) THEN
1035 RAISE e_unexpected_error;
1036 END IF;
1037 COMMIT;
1038 ELSE
1039 -- BSC-MV Note: In this new architecture if the table exist and does not
1040 -- has projection, this table is to rollup projection to another level.
1041 -- In this case we need to check to avoid calculating zero code on keys
1042 -- that already has zero codes inherit from the source table.
1043 IF NOT BSC_UPDATE_CALC.Calculate_Zero_Code(x_sum_table,
1044 h_zero_code_calc_method,
1045 h_key_columns,
1046 h_num_key_columns,
1047 h_origin_tables(1)) THEN
1048 RAISE e_unexpected_error;
1049 END IF;
1050 COMMIT;
1051 END IF;
1052 END IF;
1053
1054 -- Merge data from target tables
1055 -- BSC-MV Note: No need this calculation. Targets are already merged into the MV
1056
1057 -- Calculate Profit
1058 --BSC-MV Note: In this architectute profit is calculated in the base tables
1059 END IF;
1060
1061 RETURN TRUE;
1062
1063 EXCEPTION
1064 WHEN e_unexpected_error THEN
1065 ROLLBACK;
1066 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_SUMTABLE_CALC_FAILED'),
1067 x_source => 'BSC_UPDATE_SUM.Calculate_Sum_Table_MV');
1068 RETURN FALSE;
1069
1070 WHEN e_error_refresh THEN
1071 ROLLBACK;
1072 BSC_MESSAGE.Add(x_message => 'BSC_BIA_WRAPPER.Refresh_Summary_MV '||h_mv_name||' '||h_error_refresh,
1073 x_source => 'BSC_UPDATE_SUM.Calculate_Sum_Table_MV');
1074 RETURN FALSE;
1075
1076 WHEN e_error_refresh_zero THEN
1077 ROLLBACK;
1078 BSC_MESSAGE.Add(x_message => 'BSC_UPDATE_SUM.Refresh_Zero_MVs '||h_mv_name||' '||h_error_refresh,
1079 x_source => 'BSC_UPDATE_SUM.Calculate_Sum_Table_MV');
1080 RETURN FALSE;
1081
1082 WHEN OTHERS THEN
1083 ROLLBACK;
1084 BSC_MESSAGE.Add(x_message => SQLERRM,
1085 x_source => 'BSC_UPDATE_SUM.Calculate_Sum_Table_MV');
1086 RETURN FALSE;
1087
1088 END Calculate_Sum_Table_MV;
1089
1090 --LOCKING: new function
1091 /*===========================================================================+
1092 | FUNCTION Calculate_Sum_Table_MV_AT
1093 +============================================================================*/
1094 FUNCTION Calculate_Sum_Table_MV_AT(
1095 x_sum_table IN VARCHAR2,
1096 x_calculated_sys_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1097 x_num_calculated_sys_tables IN NUMBER,
1098 x_system_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1099 x_num_system_tables IN NUMBER
1100 ) RETURN BOOLEAN IS
1101 PRAGMA AUTONOMOUS_TRANSACTION;
1102 h_b BOOLEAN;
1103 BEGIN
1104 h_b := Calculate_Sum_Table_MV(x_sum_table, x_calculated_sys_tables, x_num_calculated_sys_tables,
1105 x_system_tables, x_num_system_tables);
1106 commit; -- all autonomous transaction needs to commit
1107 RETURN h_b;
1108 END Calculate_Sum_Table_MV_AT;
1109
1110
1111 -- AW_INTEGRATION: New function
1112 /*===========================================================================+
1113 | FUNCTION Calculate_Sum_Table_AW
1114 +============================================================================*/
1115 FUNCTION Calculate_Sum_Table_AW(
1116 x_sum_table IN VARCHAR2
1117 ) RETURN BOOLEAN IS
1118
1119 e_unexpected_error EXCEPTION;
1120
1121 h_current_fy NUMBER;
1122 h_origin_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
1123 h_num_origin_tables NUMBER;
1124 h_origin_period NUMBER;
1125 h_origin_periodicity NUMBER;
1126 h_period NUMBER;
1127 h_periodicity NUMBER;
1128 h_calendar_id NUMBER;
1129
1130 BEGIN
1131 h_num_origin_tables := 0;
1132
1133 -- AW_INTEGRATION: We only need to update the current period of the table
1134
1135 -- Initialize the array h_origin_tables with the tables from where
1136 -- the summary table is generated. There is at least one origin table.
1137 IF NOT Get_Origin_Tables(x_sum_table, h_origin_tables, h_num_origin_tables) THEN
1138 RAISE e_unexpected_error;
1139 END IF;
1140
1141 -- Get the minimum period of the origin tables
1142 h_origin_period := Get_Minimun_Origin_Period(h_origin_tables, h_num_origin_tables);
1143 IF h_origin_period IS NULL THEN
1144 RAISE e_unexpected_error;
1145 END IF;
1146
1147 -- Get the periodicity of the origin tables.
1148 -- If a summary table comes from several tables, all origin tables have the
1149 -- same periodicity
1150 h_origin_periodicity := BSC_UPDATE_UTIL.Get_Table_Periodicity(h_origin_tables(1));
1151 IF h_origin_periodicity IS NULL THEN
1152 RAISE e_unexpected_error;
1153 END IF;
1154
1155 -- Get the periodicity of the summary table
1156 h_periodicity := BSC_UPDATE_UTIL.Get_Table_Periodicity(x_sum_table);
1157 IF h_periodicity IS NULL THEN
1158 RAISE e_unexpected_error;
1159 END IF;
1160
1161 h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(h_periodicity);
1162
1163 -- Get the current fiscal year
1164 h_current_fy := BSC_UPDATE_UTIL.Get_Calendar_Fiscal_Year(h_calendar_id);
1165
1166 -- Calculate the current period of summary table
1167 h_period := Calculate_Period_Summary_Table(h_periodicity,
1168 h_origin_periodicity,
1169 h_origin_period,
1170 h_current_fy);
1171 IF h_period IS NULL THEN
1172 RAISE e_unexpected_error;
1173 END IF;
1174
1175 -- Store the update period of summary table
1176 UPDATE
1177 bsc_db_tables
1178 SET
1179 current_period = h_period
1180 WHERE
1181 table_name = x_sum_table;
1182 COMMIT;
1183
1184 RETURN TRUE;
1185
1186 EXCEPTION
1187 WHEN e_unexpected_error THEN
1188 ROLLBACK;
1189 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_SUMTABLE_CALC_FAILED'),
1190 x_source => 'BSC_UPDATE_SUM.Calculate_Sum_Table_AW');
1191 RETURN FALSE;
1192
1193 WHEN OTHERS THEN
1194 ROLLBACK;
1195 BSC_MESSAGE.Add(x_message => SQLERRM,
1196 x_source => 'BSC_UPDATE_SUM.Calculate_Sum_Table_AW');
1197 RETURN FALSE;
1198
1199 END Calculate_Sum_Table_AW;
1200
1201 --LOCKING: New function
1202 /*===========================================================================+
1203 | FUNCTION Calculate_Sum_Table_AW_AT
1204 +============================================================================*/
1205 FUNCTION Calculate_Sum_Table_AW_AT(
1206 x_sum_table IN VARCHAR2
1207 ) RETURN BOOLEAN IS
1208 PRAGMA AUTONOMOUS_TRANSACTION;
1209 h_b BOOLEAN;
1210 BEGIN
1211 h_b := Calculate_Sum_Table_AW(x_sum_table);
1212 commit; -- all autonomous transaction needs to commit
1213 RETURN h_b;
1214 END Calculate_Sum_Table_AW_AT;
1215
1216
1217 /*===========================================================================+
1218 | FUNCTION Calculate_Sum_Table_Total
1219 +============================================================================*/
1220 FUNCTION Calculate_Sum_Table_Total(
1221 x_sum_table IN VARCHAR2,
1222 x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1223 x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1224 x_source_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1225 x_source_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1226 x_num_key_columns IN NUMBER,
1227 x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1228 x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1229 x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,
1230 x_num_data_columns IN NUMBER,
1231 x_origin_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1232 x_num_origin_tables IN NUMBER,
1233 x_key_columns_ori IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1234 x_num_key_columns_ori IN NUMBER,
1235 x_periodicity IN NUMBER,
1236 x_origin_periodicity IN NUMBER,
1237 x_period IN NUMBER,
1238 x_origin_period IN NUMBER,
1239 x_current_fy IN NUMBER) RETURN BOOLEAN IS
1240
1241 e_unexpected_error EXCEPTION;
1242
1243 h_sql VARCHAR2(32700);
1244 h_i NUMBER;
1245 h_j NUMBER;
1246
1247 h_period_col_name VARCHAR2(30);
1248 h_origin_period_col_name VARCHAR2(30);
1249 h_period_map_table VARCHAR2(30);
1250 h_period_map_condition VARCHAR2(200);
1251
1252 h_lst_key_columns VARCHAR2(32700);
1253
1254 h_lst_data_columns VARCHAR2(32700);
1255 h_lst_data_formulas VARCHAR2(32700);
1256
1257 h_lst_key_columns_ori VARCHAR2(32700);
1258
1259 h_lst_from VARCHAR2(32700);
1260 h_lst_on VARCHAR2(32700);
1261 h_lst_where VARCHAR2(32700);
1262
1263 h_lst_select_disag VARCHAR2(32700);
1264 h_lst_select_per VARCHAR2(32700);
1265
1266 h_num_tot_data_columns NUMBER;
1267 h_lst_tot_data_columns VARCHAR2(32700);
1268 h_lst_tot_data_formulas VARCHAR2(32700);
1269 h_target_table_tot VARCHAR2(30);
1270 h_lst_from_tot VARCHAR2(32700);
1271 h_lst_on_tot VARCHAR2(32700);
1272
1273 h_num_bal_data_columns NUMBER;
1274 h_lst_bal_data_columns VARCHAR2(32700);
1275 h_lst_bal_data_formulas VARCHAR2(32700);
1276 h_target_table_bal VARCHAR2(30);
1277 h_lst_from_bal VARCHAR2(32700);
1278 h_lst_on_bal VARCHAR2(32700);
1279
1280 h_periodicity_edw_flag NUMBER;
1281 h_yearly_flag NUMBER;
1282 h_calendar_id NUMBER;
1283
1284 h_union_table VARCHAR2(30);
1285 h_cond_zero_codes_src VARCHAR2(32000);
1286
1287 -- Bind var fix for Posco
1288 l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
1289 l_num_bind_vars NUMBER;
1290
1291 l_parallel_hint varchar2(20000);
1292 l_parallel_hint1 varchar2(20000);
1293 l_parallel_hint2 varchar2(20000);
1294
1295 h_key_columns_ori_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
1296 h_lst_key_columns_ori_temp VARCHAR2(32000);
1297
1298 h_key VARCHAR(100);
1299
1300 h_key_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
1301 h_lst_key_columns_temp VARCHAR2(32000);
1302
1303 h_lst_tot_data_columns_temp VARCHAR2(32000);
1304 h_lst_bal_data_columns_temp VARCHAR2(32000);
1305 h_lst_tot_data_columns_temp_t VARCHAR2(32000);
1306 h_lst_bal_data_columns_temp_b VARCHAR2(32000);
1307
1308 --Fix bug#3895181: Need the following 2 variables
1309 h_lst_tot_data_columns_temp_p VARCHAR2(32000);
1310 h_lst_tot_data_columns_p VARCHAR2(32000);
1311
1312 -- ENH_B_TABLES_PERF: new variable
1313 h_origin_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
1314 h_proj_table_name VARCHAR2(30);
1315 h_union_table_from VARCHAR2(32000);
1316
1317 BEGIN
1318
1319 h_sql := NULL;
1320 h_lst_key_columns := NULL;
1321 h_lst_data_columns := NULL;
1322 h_lst_data_formulas := NULL;
1323 h_lst_key_columns_ori := NULL;
1324 h_lst_from := NULL;
1325 h_lst_on := NULL;
1326 h_lst_where := NULL;
1327 h_lst_select_disag := NULL;
1328 h_lst_select_per := NULL;
1329 h_num_tot_data_columns := 0;
1330 h_lst_tot_data_columns := NULL;
1331 h_lst_tot_data_formulas := NULL;
1332 h_target_table_tot := NULL;
1333 h_lst_from_tot := NULL;
1334 h_lst_on_tot := NULL;
1335 h_num_bal_data_columns := 0;
1336 h_lst_bal_data_columns := NULL;
1337 h_lst_bal_data_formulas := NULL;
1338 h_target_table_bal := NULL;
1339 h_lst_from_bal := NULL;
1340 h_lst_on_bal := NULL;
1341 h_periodicity_edw_flag := 0;
1342 h_yearly_flag := 0;
1343 h_union_table := NULL;
1344 h_cond_zero_codes_src := NULL;
1345 l_num_bind_vars := 0;
1346 h_lst_key_columns_temp := NULL;
1347 h_lst_tot_data_columns_temp := NULL;
1348 h_lst_bal_data_columns_temp := NULL;
1349 h_lst_tot_data_columns_temp_t := NULL;
1350 h_lst_bal_data_columns_temp_b := NULL;
1351 --Fix bug#3895181: Need the following 2 variables
1352 h_lst_tot_data_columns_temp_p := NULL;
1353 h_lst_tot_data_columns_p := NULL;
1354
1355 -- New array used with generic temporary tables
1356 FOR h_i IN 1..x_num_key_columns_ori LOOP
1357 h_key_columns_ori_temp(h_i) := 'KEY'||h_i;
1358 END LOOP;
1359 FOR h_i IN 1..x_num_key_columns LOOP
1360 h_key_columns_temp(h_i) := 'KEY'||h_i;
1361 END LOOP;
1362
1363 -- Some information about the periodicity
1364 h_periodicity_edw_flag := BSC_UPDATE_UTIL.Get_Periodicity_EDW_Flag(x_periodicity);
1365 h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity);
1366 h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_periodicity);
1367
1368 -- BSC-MV Note: In this architecture, the summary table is the projection table
1369 -- create for targets at different levels.
1370 -- It contains all periodicities and columns PERIODICITY_ID and PERIOD_TYPE_ID.
1371 -- We do not need to handle change of periodicity
1372 -- Also the origin table is also projection table with all the periodicities
1373 -- By design there is only one origin table
1374
1375 IF NOT BSC_APPS.bsc_mv THEN
1376 -- Initialize some variables required only when there is change of periodicity
1377 -- to handle balance and total data columns
1378 IF x_periodicity <> x_origin_periodicity THEN
1379 -- Calculate the number of balance and total data columns
1380 -- By the way initialize arrays for total and balance data columns
1381 FOR h_i IN 1..x_num_data_columns LOOP
1382 IF x_data_measure_types(h_i) = 1 THEN
1383 -- Total data column
1384 h_num_tot_data_columns := h_num_tot_data_columns + 1;
1385 IF h_num_tot_data_columns = 1 THEN
1386 h_lst_tot_data_columns := x_data_columns(h_i);
1387 h_lst_tot_data_columns_temp := 'DATA'||h_i;
1388 h_lst_tot_data_columns_temp_t := 'T.DATA'||h_i;
1389 h_lst_tot_data_formulas := x_data_formulas(h_i);
1390 --Fix bug#3895181: Need the following 2 variables
1391 h_lst_tot_data_columns_temp_p := 'P.DATA'||h_i;
1392 h_lst_tot_data_columns_p := 'P.'||x_data_columns(h_i);
1393 ELSE
1394 h_lst_tot_data_columns := h_lst_tot_data_columns||', '||x_data_columns(h_i);
1395 h_lst_tot_data_columns_temp := h_lst_tot_data_columns_temp||', DATA'||h_i;
1396 h_lst_tot_data_columns_temp_t := h_lst_tot_data_columns_temp_t||', T.DATA'||h_i;
1397 h_lst_tot_data_formulas := h_lst_tot_data_formulas||', '||x_data_formulas(h_i);
1398 --Fix bug#3895181: Need the following 2 variables
1399 h_lst_tot_data_columns_temp_p := h_lst_tot_data_columns_temp_p||', P.DATA'||h_i;
1400 h_lst_tot_data_columns_p := h_lst_tot_data_columns_p||', P.'||x_data_columns(h_i);
1401 END IF;
1402 ELSE
1403 -- Balance data column
1404 h_num_bal_data_columns := h_num_bal_data_columns + 1;
1405 IF h_num_bal_data_columns = 1 THEN
1406 h_lst_bal_data_columns := x_data_columns(h_i);
1407 h_lst_bal_data_columns_temp := 'DATA'||h_i;
1408 h_lst_bal_data_columns_temp_b := 'B.DATA'||h_i;
1409 h_lst_bal_data_formulas := x_data_formulas(h_i);
1410 ELSE
1411 h_lst_bal_data_columns := h_lst_bal_data_columns||', '||x_data_columns(h_i);
1412 h_lst_bal_data_columns_temp := h_lst_bal_data_columns_temp||', DATA'||h_i;
1413 h_lst_bal_data_columns_temp_b := h_lst_bal_data_columns_temp_b||', B.DATA'||h_i;
1414 h_lst_bal_data_formulas := h_lst_bal_data_formulas||', '||x_data_formulas(h_i);
1415 END IF;
1416 END IF;
1417 END LOOP;
1418
1419 -- Create a temporal table to make the change of periodicity between
1420 -- the origin tables and the summary table
1421 IF h_periodicity_edw_flag = 0 THEN
1422 -- BSC periodicity
1423 h_period_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity);
1424 h_origin_period_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_origin_periodicity);
1425 h_period_map_table := 'bsc_db_calendar';
1426 h_period_map_condition := 'calendar_id = :1';
1427 l_bind_vars_values.delete;
1428 l_bind_vars_values(1) := h_calendar_id;
1429 l_num_bind_vars := 1;
1430 ELSE
1431 -- EDW periodicity
1432 h_period_col_name := 'BSC_TARGET';
1433 h_origin_period_col_name := 'BSC_SOURCE';
1434 h_period_map_table := 'bsc_edw_time_map';
1435 h_period_map_condition := NULL;
1436 l_bind_vars_values.delete;
1437 l_num_bind_vars := 0;
1438 END IF;
1439
1440 IF h_num_tot_data_columns > 0 THEN
1441 -- Clean current records from bsc_tmp_per_change
1442 --h_sql := 'DELETE FROM bsc_tmp_per_change';
1443 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1444 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PER_CHANGE');
1445
1446 IF h_yearly_flag <> 1 THEN
1447 h_sql := 'INSERT /*+ append ';
1448 if BSC_UPDATE_UTIL.is_parallel then
1449 h_sql:=h_sql||'parallel (bsc_tmp_per_change) ';
1450 end if;
1451 h_sql:=h_sql||' */';
1452 h_sql:=h_sql||'INTO bsc_tmp_per_change (year, src_per, trg_per)'||
1453 ' SELECT ';
1454 if BSC_UPDATE_UTIL.is_parallel then
1455 h_sql:=h_sql||'/*+ parallel ('||h_period_map_table||')*/ ';
1456 end if;
1457 h_sql:=h_sql||
1458 'DISTINCT year, '||h_origin_period_col_name||' AS src_per, '||
1459 h_period_col_name||' AS trg_per'||
1460 ' FROM '||h_period_map_table;
1461
1462 IF h_period_map_condition IS NOT NULL THEN
1463 h_sql := h_sql||' WHERE '||h_period_map_condition;
1464 END IF;
1465 ELSE
1466 -- Anual periodicity
1467 h_sql := 'INSERT /*+ append ';
1468 if BSC_UPDATE_UTIL.is_parallel then
1469 h_sql:=h_sql||'parallel (bsc_tmp_per_change) ';
1470 end if;
1471 h_sql:=h_sql||' */';
1472 h_sql:=h_sql||'INTO bsc_tmp_per_change (year, src_per, trg_per)'||
1473 ' SELECT ';
1474 if BSC_UPDATE_UTIL.is_parallel then
1475 h_sql:=h_sql||'/*+ parallel ('||h_period_map_table||')*/ ';
1476 end if;
1477 h_sql:=h_sql||'DISTINCT year, '||h_origin_period_col_name||' AS src_per, 0 AS trg_per'||
1478 ' FROM '||h_period_map_table;
1479
1480 IF h_period_map_condition IS NOT NULL THEN
1481 h_sql := h_sql||' WHERE '||h_period_map_condition;
1482 END IF;
1483 END IF;
1484 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
1485 commit;
1486 END IF;
1487
1488 IF h_num_bal_data_columns > 0 THEN
1489 -- Clean current records from bsc_tmp_per_change_bal
1490 --h_sql := 'DELETE FROM bsc_tmp_per_change_bal';
1491 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1492 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PER_CHANGE_BAL');
1493
1494 IF h_yearly_flag <> 1 THEN
1495 h_sql := 'INSERT /*+ append ';
1496 if BSC_UPDATE_UTIL.is_parallel then
1497 h_sql:=h_sql||'parallel (bsc_tmp_per_change_bal) ';
1498 end if;
1499 h_sql:=h_sql||' */';
1500 h_sql:=h_sql||'INTO bsc_tmp_per_change_bal (year, src_per, trg_per)'||
1501 ' SELECT ';
1502 if BSC_UPDATE_UTIL.is_parallel then
1503 h_sql:=h_sql||'/*+ parallel ('||h_period_map_table||')*/ ';
1504 end if;
1505 h_sql:=h_sql||'year, MAX('||h_origin_period_col_name||') AS src_per, '||h_period_col_name||' AS trg_per'||
1506 ' FROM '||h_period_map_table;
1507 IF h_period_map_condition IS NOT NULL THEN
1508 h_sql := h_sql||' WHERE '||h_period_map_condition;
1509 END IF;
1510 h_sql := h_sql||' GROUP BY year, '||h_period_col_name;
1511 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
1512 commit;
1513
1514 l_bind_vars_values.delete;
1515 l_bind_vars_values(1) := (x_origin_period);
1516 l_bind_vars_values(2) := (x_current_fy) ;
1517 l_bind_vars_values(3) := (x_origin_period);
1518 l_bind_vars_values(4) := (x_current_fy);
1519 l_num_bind_vars := 4;
1520
1521 h_sql := 'UPDATE bsc_tmp_per_change_bal'||
1522 ' SET src_per = :1'||
1523 ' WHERE year = :2'||
1524 ' AND trg_per = ('||
1525 ' SELECT '||h_period_col_name||
1526 ' FROM '||h_period_map_table||
1527 ' WHERE '||h_origin_period_col_name||' = :3'||
1528 ' AND year = :4';
1529 IF h_period_map_condition IS NOT NULL THEN
1530 h_sql := h_sql||' AND '||h_period_map_condition;
1531 l_bind_vars_values(5) := (h_calendar_id);
1532 l_num_bind_vars := 5;
1533 END IF;
1534 h_sql := h_sql||' GROUP BY '||h_period_col_name||
1535 ')';
1536 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
1537 ELSE
1538 -- Anual periodicity
1539 h_sql := 'INSERT /*+ append ';
1540 if BSC_UPDATE_UTIL.is_parallel then
1541 h_sql:=h_sql||'parallel (bsc_tmp_per_change_bal) ';
1542 end if;
1543 h_sql:=h_sql||' */';
1544 h_sql:=h_sql||'INTO bsc_tmp_per_change_bal (year, src_per, trg_per)'||
1545 ' SELECT ';
1546 if BSC_UPDATE_UTIL.is_parallel then
1547 h_sql:=h_sql||'/*+ parallel ('||h_period_map_table||')*/ ';
1548 end if;
1549 h_sql:=h_sql||'year, MAX('||h_origin_period_col_name||') AS src_per, 0 AS trg_per'||
1550 ' FROM '||h_period_map_table;
1551 IF h_period_map_condition IS NOT NULL THEN
1552 h_sql := h_sql||' WHERE '||h_period_map_condition;
1553 END IF;
1554 h_sql := h_sql||' GROUP BY year';
1555 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
1556 commit;
1557
1558 l_bind_vars_values.delete;
1559 l_bind_vars_values(1) := (x_origin_period);
1560 l_bind_vars_values(2) := (x_current_fy);
1561 l_num_bind_vars := 2;
1562 h_sql := 'UPDATE bsc_tmp_per_change_bal'||
1563 ' SET src_per = :1'||
1564 ' WHERE year = :2';
1565 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
1566 END IF;
1567 END IF;
1568
1569 -- Create temporal tables to calculate total data columns and balance data columns separately
1570 -- and then merge them into the target summary table
1571 -- If all data columns are total or balance we dont need those temporal tables
1572 IF (h_num_tot_data_columns > 0) AND (h_num_bal_data_columns > 0) THEN
1573 -- BSC_TMP_TOT_DATA
1574 -- Clean temporal table
1575 --h_sql := 'DELETE FROM BSC_TMP_TOT_DATA';
1576 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1577 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_DATA');
1578
1579 h_target_table_tot := 'BSC_TMP_TOT_DATA';
1580
1581 -- BSC_TMP_BAL_DATA
1582 -- Clean temporal table
1583 --h_sql := 'DELETE FROM BSC_TMP_BAL_DATA';
1584 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1585 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BAL_DATA');
1586
1587 h_target_table_bal := 'BSC_TMP_BAL_DATA';
1588 ELSE
1589 h_target_table_tot := x_sum_table;
1590 h_target_table_bal := x_sum_table;
1591 END IF;
1592 END IF;
1593 END IF;
1594
1595 -- ENH_B_TABLES_PERF: If the origin table is a base table and the base table has a projection
1596 -- table then we need to do union all and that will be the origin table.
1597 -- I am going to initialize the array h_origin_tables with the proper origin and use it
1598 -- instead of x_origin_tables
1599 FOR h_i IN 1..x_num_origin_tables LOOP
1600 h_origin_tables(h_i) := x_origin_tables(h_i);
1601 IF NOT BSC_APPS.bsc_mv THEN
1602 IF BSC_UPDATE_UTIL.Is_Base_Table(x_origin_tables(h_i)) THEN
1603 h_proj_table_name := BSC_UPDATE_BASE_V2.Get_Base_Proj_Tbl_Name(x_origin_tables(h_i));
1604 IF h_proj_table_name IS NOT NULL THEN
1605 -- Base table is splitted in two: actuals and projection
1606 -- Note that actual and projection table has the same structure
1607 h_origin_tables(h_i) := '(SELECT * FROM '||x_origin_tables(h_i)||
1608 ' UNION ALL'||
1609 ' SELECT * FROM '||h_proj_table_name||
1610 ') '||x_origin_tables(h_i);
1611 END IF;
1612 END IF;
1613 END IF;
1614 END LOOP;
1615
1616 -- Create a temporal table bsc_tmp_union with all records from the origin tables
1617 -- Performance fix: Do not create BSC_TMP_UNION when the summary table is calculated
1618 -- from only one table.
1619 IF x_num_origin_tables > 1 THEN
1620 -- BSC-MV Note: By design this part is not executed because in this architecture
1621 -- there is only one origin table
1622
1623 h_lst_key_columns_ori := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns_ori,
1624 x_num_key_columns_ori);
1625 h_lst_key_columns_ori_temp := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(h_key_columns_ori_temp,
1626 x_num_key_columns_ori);
1627 IF h_lst_key_columns_ori IS NOT NULL THEN
1628 h_lst_key_columns_ori := h_lst_key_columns_ori||', ';
1629 h_lst_key_columns_ori_temp := h_lst_key_columns_ori_temp||', ';
1630 END IF;
1631
1632 -- Clean temporary table
1633 --h_sql := 'DELETE FROM BSC_TMP_UNION';
1634 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1635 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_UNION');
1636
1637 -- Insert data
1638 h_sql := 'INSERT /*+ append ';
1639 if BSC_UPDATE_UTIL.is_parallel then
1640 h_sql:=h_sql||'parallel (BSC_TMP_UNION) ';
1641 end if;
1642 h_sql:=h_sql||' */';
1643 h_sql:=h_sql||'INTO BSC_TMP_UNION ('||h_lst_key_columns_ori_temp||'YEAR, TYPE, PERIOD)'||
1644 ' SELECT ';
1645 if BSC_UPDATE_UTIL.is_parallel then
1646 h_sql:=h_sql||'/*+ parallel ('||x_origin_tables(1)||')*/ ';
1647 end if;
1648 -- ENH_B_TABLES_PERF: use h_origin_tables
1649 h_sql:=h_sql||h_lst_key_columns_ori||'YEAR, TYPE, PERIOD'||
1650 ' FROM '||h_origin_tables(1);
1651
1652 FOR h_i IN 2 .. x_num_origin_tables LOOP
1653 -- ENH_B_TABLES_PERF: use h_origin_tables
1654 h_sql := h_sql||' UNION'||
1655 ' SELECT '||h_lst_key_columns_ori||'YEAR, TYPE, PERIOD'||
1656 ' FROM '||h_origin_tables(h_i);
1657 END LOOP;
1658 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1659 commit;
1660
1661 -- We need to delete (this is no the general case but in the future could happen)
1662 -- from BSC_TMP_UNION those rows with any zero code in the key columns that are
1663 -- not part of the target table.
1664 -- Example: Keys in the source tables: REG_CODE PROD_CODE
1665 -- 1 0
1666 -- 1 1
1667 -- 1 2
1668 -- Keys in the target table: REG_CODE
1669 -- 1
1670 -- The total for REG_CODE 1 is PROD_CODE 1 + 2 (We cannot add the zero code
1671 -- because the total for REG_CODE 1 would be duplicated.
1672
1673 -- BSC-BIS-DIMENSIONS: Need to use '0' in the condiction to be compatible with
1674 -- NUMBER of VARCHAR2 in the key columns
1675
1676 h_lst_where := NULL;
1677 FOR h_i IN 1..x_num_key_columns_ori LOOP
1678 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(x_key_columns_ori(h_i),
1679 x_source_columns,
1680 x_num_key_columns) THEN
1681 IF h_lst_where IS NULL THEN
1682 h_lst_where := h_key_columns_ori_temp(h_i)||' = ''0''';
1683 ELSE
1684 h_lst_where := h_lst_where||' OR '||h_key_columns_ori_temp(h_i)||' = ''0''';
1685 END IF;
1686 END IF;
1687 END LOOP;
1688 IF h_lst_where IS NOT NULL THEN
1689 h_sql := 'DELETE FROM BSC_TMP_UNION'||
1690 ' WHERE '||h_lst_where;
1691 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1692 END IF;
1693
1694 -- ENH_B_TABLES_PERF: add this line, we need to differenciate between the name of the union
1695 -- table and the query for the union
1696 h_union_table_from := 'BSC_TMP_UNION';
1697 h_union_table := 'BSC_TMP_UNION';
1698 ELSE
1699 -- ENH_B_TABLES_PERF: add this line, we need to differenciate between the name of the union
1700 -- table and the query for the union
1701 h_union_table_from := h_origin_tables(1);
1702 h_union_table := x_origin_tables(1);
1703
1704 -- We need to filter off any zero code in the key columns that are
1705 -- not part of the target table.
1706 -- Example: Keys in the source tables: REG_CODE PROD_CODE
1707 -- 1 0
1708 -- 1 1
1709 -- 1 2
1710 -- Keys in the target table: REG_CODE
1711 -- 1
1712 -- The total for REG_CODE 1 is PROD_CODE 1 + 2 (We cannot add the zero code
1713 -- because the total for REG_CODE 1 would be duplicated.
1714
1715 -- BSC-BIS-DIMENSIONS: Need to use '0' in the condiction to be compatible with
1716 -- NUMBER of VARCHAR2 in the key columns
1717
1718 h_cond_zero_codes_src := NULL;
1719 FOR h_i IN 1..x_num_key_columns_ori LOOP
1720 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(x_key_columns_ori(h_i),
1721 x_source_columns,
1722 x_num_key_columns) THEN
1723 IF h_cond_zero_codes_src IS NULL THEN
1724 h_cond_zero_codes_src := x_key_columns_ori(h_i)||' <> ''0''';
1725 ELSE
1726 h_cond_zero_codes_src := h_cond_zero_codes_src||' AND '||x_key_columns_ori(h_i)||' <> ''0''';
1727 END IF;
1728 END IF;
1729 END LOOP;
1730 END IF;
1731
1732 -- create the string for FROM sub-statement
1733 -- ENH_B_TABLES_PERF: use h_union_table_from
1734 h_lst_from := h_union_table_from;
1735 l_parallel_hint:=l_parallel_hint||' parallel ('||h_union_table||')';
1736
1737 IF x_num_origin_tables > 1 THEN
1738 -- BSC-MV Note: By design this part is not executed because in this architecture
1739 -- there is only one origin table
1740
1741 FOR h_i IN 1 .. x_num_origin_tables LOOP
1742 -- ENH_B_TABLES_PERF: use h_origin_tables
1743 h_lst_from := h_lst_from||', '||h_origin_tables(h_i);
1744 l_parallel_hint:=l_parallel_hint||' parallel ('||x_origin_tables(h_i)||')';
1745 IF h_lst_on IS NOT NULL THEN
1746 h_lst_on := h_lst_on||' AND ';
1747 END IF;
1748 IF x_num_key_columns_ori > 0 THEN
1749 h_lst_on := h_lst_on||
1750 BSC_UPDATE_UTIL.Make_Lst_Cond_Left_Join('BSC_TMP_UNION',
1751 h_key_columns_ori_temp,
1752 x_origin_tables(h_i),
1753 x_key_columns_ori,
1754 x_num_key_columns_ori,
1755 'AND')||
1756 ' AND ';
1757 END IF;
1758 IF BSC_APPS.bsc_mv THEN
1759 h_lst_on := h_lst_on||'BSC_TMP_UNION.PERIODICITY_ID = '||x_origin_tables(h_i)||'.PERIODICITY_ID (+) AND ';
1760 END IF;
1761 h_lst_on := h_lst_on||'BSC_TMP_UNION.YEAR = '||x_origin_tables(h_i)||'.YEAR (+)'||
1762 ' AND '||'BSC_TMP_UNION.TYPE = '||x_origin_tables(h_i)||'.TYPE (+)'||
1763 ' AND '||'BSC_TMP_UNION.PERIOD = '||x_origin_tables(h_i)||'.PERIOD (+)';
1764 END LOOP;
1765 ELSE
1766 h_lst_on := h_cond_zero_codes_src;
1767 END IF;
1768
1769 -- Initialize some lists that will be part of the query to generate the summary table
1770 h_lst_key_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_key_columns, x_num_key_columns);
1771 h_lst_key_columns_temp := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(h_key_columns_temp, x_num_key_columns);
1772
1773 IF h_lst_key_columns IS NOT NULL THEN
1774 h_lst_key_columns := h_lst_key_columns||', ';
1775 h_lst_key_columns_temp := h_lst_key_columns_temp||', ';
1776 END IF;
1777
1778 h_lst_data_columns := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_columns, x_num_data_columns);
1779 h_lst_data_formulas := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(x_data_formulas, x_num_data_columns);
1780
1781 -- Delete all records of summary table.
1782 BSC_UPDATE_UTIL.Truncate_Table(x_sum_table);
1783
1784 -- Disable indexes for improve performance
1785 IF NOT BSC_UPDATE_UTIL.Drop_Index(x_sum_table||'_U1') THEN
1786 RAISE e_unexpected_error;
1787 END IF;
1788
1789 -- Create the string for the SELECT and FROM sub-statement
1790 FOR h_i IN 1 .. x_num_key_columns LOOP
1791 IF h_i > 1 THEN
1792 h_lst_select_disag := h_lst_select_disag||', ';
1793 END IF;
1794
1795 IF x_num_origin_tables > 1 THEN
1796 --Get KEY column of BSC_TMP_UNION that correspond to x_source_columns(h_i)
1797 FOR h_j IN 1..x_num_key_columns_ori LOOP
1798 IF x_key_columns_ori(h_j) = x_source_columns(h_i) THEN
1799 h_key := 'KEY'||h_j;
1800 EXIT;
1801 END IF;
1802 END LOOP;
1803 ELSE
1804 h_key := x_source_columns(h_i);
1805 END IF;
1806
1807 IF x_key_columns(h_i) = x_source_columns(h_i) THEN
1808 -- There is no change of dissagregation for this key
1809
1810 -- BSC-BIS-DIMENSIONS Note: From now on, even if there is no change of dissagregation
1811 -- we are going to join to the dimension table to make sure that we do not pass
1812 -- rows corresponding to items that were deleted from the dimension.
1813 -- This is implemeted no matter if the dimension is BSC of BIS.
1814
1815 --h_lst_select_disag := h_lst_select_disag||h_union_table||'.'||h_key;
1816 h_lst_select_disag := h_lst_select_disag||x_source_dim_tables(h_i)||'.CODE';
1817 h_lst_from := h_lst_from||', '||x_source_dim_tables(h_i);
1818 l_parallel_hint:=l_parallel_hint||' parallel ('||x_source_dim_tables(h_i)||')';
1819
1820 IF h_lst_on IS NOT NULL THEN
1821 h_lst_on := h_lst_on||' AND ';
1822 END IF;
1823 h_lst_on := h_lst_on||h_union_table||'.'||h_key||' = '||x_source_dim_tables(h_i)||'.CODE';
1824
1825 ELSE
1826 -- There is change of dissagregation for this key
1827 h_lst_select_disag := h_lst_select_disag||x_source_dim_tables(h_i)||'.'||x_key_columns(h_i);
1828 h_lst_from := h_lst_from||', '||x_source_dim_tables(h_i);
1829 l_parallel_hint:=l_parallel_hint||' parallel ('||x_source_dim_tables(h_i)||')';
1830
1831 IF h_lst_on IS NOT NULL THEN
1832 h_lst_on := h_lst_on||' AND ';
1833 END IF;
1834 h_lst_on := h_lst_on||h_union_table||'.'||h_key||' = '||x_source_dim_tables(h_i)||'.CODE';
1835 END IF;
1836 END LOOP;
1837
1838 IF h_lst_select_disag IS NOT NULL THEN
1839 h_lst_select_disag := h_lst_select_disag||', ';
1840 END IF;
1841
1842 -- Create the string for the SELECT and FROM sub-statement when there is periodicity change.
1843 IF (BSC_APPS.bsc_mv) OR (x_periodicity = x_origin_periodicity) THEN
1844 -- BSC-MV Note: In this architecture there is no change of periodicity
1845
1846 -- There is no change of periodicity
1847 h_lst_select_per := h_union_table||'.YEAR, '||h_union_table||'.TYPE, '||h_union_table||'.PERIOD';
1848
1849 -- Generates the summary table
1850 h_sql := 'INSERT /*+ append ';
1851 if BSC_UPDATE_UTIL.is_parallel then
1852 h_sql:=h_sql||'parallel ('||x_sum_table||') ';
1853 end if;
1854 h_sql:=h_sql||' */';
1855 h_sql:=h_sql||'INTO '||x_sum_table;
1856 IF BSC_APPS.bsc_mv THEN
1857 h_sql := h_sql||' ('||h_lst_key_columns||'YEAR, TYPE, PERIOD, PERIODICITY_ID, PERIOD_TYPE_ID, '||
1858 h_lst_data_columns||')';
1859 ELSE
1860 h_sql := h_sql||' ('||h_lst_key_columns||'YEAR, TYPE, PERIOD, '||
1861 h_lst_data_columns||')';
1862 END IF;
1863 h_sql := h_sql||' SELECT ';
1864 if BSC_UPDATE_UTIL.is_parallel then
1865 h_sql:=h_sql||'/*+'||l_parallel_hint||'*/ ';
1866 end if;
1867 h_sql := h_sql||h_lst_select_disag||h_lst_select_per;
1868 IF BSC_APPS.bsc_mv THEN
1869 h_sql := h_sql||', '||h_union_table||'.PERIODICITY_ID, '||h_union_table||'.PERIOD_TYPE_ID';
1870 END IF;
1871 h_sql := h_sql||', '||h_lst_data_formulas||
1872 ' FROM '||h_lst_from;
1873 IF h_lst_on IS NOT NULL THEN
1874 h_sql := h_sql||
1875 ' WHERE '||h_lst_on;
1876 END IF;
1877 h_sql := h_sql||
1878 ' GROUP BY '||h_lst_select_disag||h_lst_select_per;
1879 IF BSC_APPS.bsc_mv THEN
1880 h_sql := h_sql||', '||h_union_table||'.PERIODICITY_ID, '||h_union_table||'.PERIOD_TYPE_ID';
1881 END IF;
1882 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1883 COMMIT;
1884
1885 -- Enable indexes
1886 IF BSC_APPS.bsc_mv THEN
1887 IF NOT BSC_UPDATE_UTIL.Create_Unique_Index(x_sum_table,
1888 x_sum_table||'_U1',
1889 h_lst_key_columns||
1890 'YEAR, TYPE, PERIOD, PERIODICITY_ID, PERIOD_TYPE_ID',
1891 BSC_APPS.summary_index_tbs_type) THEN
1892 RAISE e_unexpected_error;
1893 END IF;
1894 ELSE
1895 IF NOT BSC_UPDATE_UTIL.Create_Unique_Index(x_sum_table,
1896 x_sum_table||'_U1',
1897 h_lst_key_columns||'YEAR, TYPE, PERIOD',
1898 BSC_APPS.summary_index_tbs_type) THEN
1899 RAISE e_unexpected_error;
1900 END IF;
1901 END IF;
1902
1903 ELSE
1904 -- There is periodicity change
1905 -- Note: We suppose that the change of periodicity is allowed
1906 -- (see bsc_sys_periodicites)
1907 IF h_num_tot_data_columns > 0 THEN
1908 --Fix bug#4177794: Loader is not fixing correctly the real value for the
1909 -- current period. I am changing this code. The change is also a better
1910 -- approach for perfomance.
1911 -- We need to do this because we dont want to show the wrong value
1912 -- because projection in the origin tables.
1913
1914 h_lst_from_tot := h_lst_from||', BSC_TMP_PER_CHANGE';
1915 l_parallel_hint1:=l_parallel_hint||' parallel (BSC_TMP_PER_CHANGE)';
1916 IF h_lst_on IS NOT NULL THEN
1917 h_lst_on_tot := h_lst_on||' AND ';
1918 END IF;
1919 h_lst_on_tot := h_lst_on_tot||h_union_table||'.YEAR = BSC_TMP_PER_CHANGE.YEAR'||
1920 ' AND '||h_union_table||'.PERIOD = BSC_TMP_PER_CHANGE.SRC_PER';
1921
1922 h_lst_select_per := h_union_table||'.YEAR, '||h_union_table||'.TYPE, BSC_TMP_PER_CHANGE.TRG_PER';
1923
1924 -- First, insert the rows for real data of current. We do not take into account projection
1925 h_lst_where := '('||h_union_table||'.YEAR = :1'||
1926 ' AND '||h_union_table||'.TYPE = :2 '||
1927 ' AND '||h_union_table||'.PERIOD <= :3)';
1928
1929 l_bind_vars_values.delete;
1930 l_bind_vars_values(1) := x_current_fy ;
1931 l_bind_vars_values(2) := 0 ;
1932 l_bind_vars_values(3) := x_origin_period ;
1933 l_num_bind_vars := 3;
1934
1935 h_sql := 'INSERT /*+ append ';
1936 if BSC_UPDATE_UTIL.is_parallel then
1937 h_sql:=h_sql||'parallel ('||h_target_table_tot||') ';
1938 end if;
1939 h_sql:=h_sql||' */';
1940 h_sql:=h_sql||'INTO '||h_target_table_tot;
1941 IF h_target_table_tot = 'BSC_TMP_TOT_DATA' THEN
1942 h_sql:=h_sql||' ('||h_lst_key_columns_temp||'YEAR, TYPE, PERIOD, '||h_lst_tot_data_columns_temp||')';
1943 ELSE
1944 h_sql:=h_sql||' ('||h_lst_key_columns||'YEAR, TYPE, PERIOD, '||h_lst_tot_data_columns||')';
1945 END IF;
1946 h_sql:=h_sql||' SELECT ';
1947 if BSC_UPDATE_UTIL.is_parallel then
1948 h_sql:=h_sql||'/*+'||l_parallel_hint1||'*/ ';
1949 end if;
1950 h_sql:=h_sql||h_lst_select_disag||h_lst_select_per||', '||h_lst_tot_data_formulas||
1951 ' FROM '||h_lst_from_tot||
1952 ' WHERE '||h_lst_on_tot||' AND '||h_lst_where||
1953 ' GROUP BY '||h_lst_select_disag||h_lst_select_per;
1954 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
1955 commit;
1956
1957 -- Now, insert rows for projection, previous year and other types
1958 IF h_yearly_flag <> 1 THEN
1959 h_lst_where := '(('||h_union_table||'.YEAR <> :1) OR'||
1960 ' ('||h_union_table||'.YEAR = :2 AND '||h_union_table||'.TYPE <> :3) OR'||
1961 ' ('||h_union_table||'.YEAR = :4 AND '||h_union_table||'.TYPE = :5 AND'||
1962 ' BSC_TMP_PER_CHANGE.TRG_PER > :6))';
1963
1964 l_bind_vars_values.delete;
1965 l_bind_vars_values(1) := x_current_fy;
1966 l_bind_vars_values(2) := x_current_fy;
1967 l_bind_vars_values(3) := 0;
1968 l_bind_vars_values(4) := x_current_fy;
1969 l_bind_vars_values(5) := 0;
1970 l_bind_vars_values(6) := x_period;
1971 l_num_bind_vars := 6;
1972 ELSE
1973 h_lst_where := '(('||h_union_table||'.YEAR <> :1) OR'||
1974 ' ('||h_union_table||'.YEAR = :2 AND '||h_union_table||'.TYPE <> :3))';
1975
1976 l_bind_vars_values.delete;
1977 l_bind_vars_values(1) := x_current_fy;
1978 l_bind_vars_values(2) := x_current_fy;
1979 l_bind_vars_values(3) := 0;
1980 l_num_bind_vars := 3;
1981 END IF;
1982
1983 h_sql := 'INSERT /*+ append ';
1984 if BSC_UPDATE_UTIL.is_parallel then
1985 h_sql:=h_sql||'parallel ('||h_target_table_tot||') ';
1986 end if;
1987 h_sql:=h_sql||' */';
1988 h_sql:=h_sql||'INTO '||h_target_table_tot;
1989 IF h_target_table_tot = 'BSC_TMP_TOT_DATA' THEN
1990 h_sql:=h_sql||' ('||h_lst_key_columns_temp||'YEAR, TYPE, PERIOD, '||h_lst_tot_data_columns_temp||')';
1991 ELSE
1992 h_sql:=h_sql||' ('||h_lst_key_columns||'YEAR, TYPE, PERIOD, '||h_lst_tot_data_columns||')';
1993 END IF;
1994 h_sql:=h_sql||' SELECT ';
1995 if BSC_UPDATE_UTIL.is_parallel then
1996 h_sql:=h_sql||'/*+'||l_parallel_hint1||'*/ ';
1997 end if;
1998 h_sql:=h_sql||h_lst_select_disag||h_lst_select_per||', '||h_lst_tot_data_formulas||
1999 ' FROM '||h_lst_from_tot||
2000 ' WHERE '||h_lst_on_tot||' AND '||h_lst_where||
2001 ' GROUP BY '||h_lst_select_disag||h_lst_select_per;
2002 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, l_bind_vars_values, l_num_bind_vars);
2003 commit;
2004
2005 -- Enable indexes
2006 IF h_target_table_tot <> 'BSC_TMP_TOT_DATA' THEN
2007 IF NOT BSC_UPDATE_UTIL.Create_Unique_Index(h_target_table_tot,
2008 h_target_table_tot||'_U1',
2009 h_lst_key_columns||'YEAR, TYPE, PERIOD',
2010 BSC_APPS.summary_index_tbs_type) THEN
2011 RAISE e_unexpected_error;
2012 END IF;
2013 END IF;
2014 END IF;
2015
2016 IF h_num_bal_data_columns > 0 THEN
2017 h_lst_from_bal := h_lst_from||', BSC_TMP_PER_CHANGE_BAL';
2018 l_parallel_hint2:=l_parallel_hint||' parallel (BSC_TMP_PER_CHANGE_BAL)';
2019 IF h_lst_on IS NOT NULL THEN
2020 h_lst_on_bal := h_lst_on||' AND ';
2021 END IF;
2022 h_lst_on_bal := h_lst_on_bal||h_union_table||'.YEAR = BSC_TMP_PER_CHANGE_BAL.YEAR'||
2023 ' AND '||h_union_table||'.PERIOD = BSC_TMP_PER_CHANGE_BAL.SRC_PER';
2024 h_lst_select_per := h_union_table||'.YEAR, '||h_union_table||'.TYPE, BSC_TMP_PER_CHANGE_BAL.TRG_PER';
2025
2026 -- Generates the summary table
2027 h_sql := 'INSERT /*+ append ';
2028 if BSC_UPDATE_UTIL.is_parallel then
2029 h_sql:=h_sql||'parallel ('||h_target_table_bal||') ';
2030 end if;
2031 h_sql:=h_sql||' */';
2032 h_sql:=h_sql||'INTO '||h_target_table_bal;
2033 IF h_target_table_bal = 'BSC_TMP_BAL_DATA' THEN
2034 h_sql:=h_sql||' ('||h_lst_key_columns_temp||'YEAR, TYPE, PERIOD, '||h_lst_bal_data_columns_temp||')';
2035 ELSE
2036 h_sql:=h_sql||' ('||h_lst_key_columns||'YEAR, TYPE, PERIOD, '||h_lst_bal_data_columns||')';
2037 END IF;
2038 h_sql := h_sql||' SELECT ';
2039 if BSC_UPDATE_UTIL.is_parallel then
2040 h_sql:=h_sql||'/*+'||l_parallel_hint2||'*/ ';
2041 end if;
2042 h_sql:=h_sql||h_lst_select_disag||h_lst_select_per||', '||h_lst_bal_data_formulas||
2043 ' FROM '||h_lst_from_bal||
2044 ' WHERE '||h_lst_on_bal||
2045 ' GROUP BY '||h_lst_select_disag||h_lst_select_per;
2046 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2047 commit;
2048
2049 -- Enable indexes
2050 IF h_target_table_bal <> 'BSC_TMP_BAL_DATA' THEN
2051 IF NOT BSC_UPDATE_UTIL.Create_Unique_Index(h_target_table_bal,
2052 h_target_table_bal||'_U1',
2053 h_lst_key_columns||'YEAR, TYPE, PERIOD',
2054 BSC_APPS.summary_index_tbs_type) THEN
2055 RAISE e_unexpected_error;
2056 END IF;
2057 END IF;
2058 END IF;
2059
2060 IF (h_num_tot_data_columns > 0) AND (h_num_bal_data_columns > 0) THEN
2061 -- We need to merge BSC_TMP_TOT_DATA and BSC_TMP_BAL_DATA into the summary table
2062 -- Fix Bug#3131339 Do left join
2063 h_lst_on := BSC_UPDATE_UTIL.Make_Lst_Cond_Left_Join('T', h_key_columns_temp, 'B', h_key_columns_temp,
2064 x_num_key_columns, 'AND');
2065 IF h_lst_on IS NOT NULL THEN
2066 h_lst_on := h_lst_on||' AND ';
2067 END IF;
2068
2069 h_lst_select_disag := BSC_UPDATE_UTIL.Make_Lst_Table_Column('T', h_key_columns_temp, x_num_key_columns);
2070 IF h_lst_select_disag IS NOT NULL THEN
2071 h_lst_select_disag := h_lst_select_disag||', ';
2072 END IF;
2073
2074 h_sql := 'INSERT /*+ append ';
2075 if BSC_UPDATE_UTIL.is_parallel then
2076 h_sql:=h_sql||'parallel ('||x_sum_table||') ';
2077 end if;
2078 h_sql:=h_sql||' */';
2079 h_sql:=h_sql||'INTO '||x_sum_table||
2080 ' ('||h_lst_key_columns||'YEAR, TYPE, PERIOD, '||
2081 h_lst_tot_data_columns||', '||h_lst_bal_data_columns||')'||
2082 ' SELECT ';
2083 if BSC_UPDATE_UTIL.is_parallel then
2084 h_sql:=h_sql||'/*+ parallel (T) parallel (B)*/ ';
2085 end if;
2086 h_sql:=h_sql||h_lst_select_disag||'T.YEAR, T.TYPE, T.PERIOD, '||
2087 h_lst_tot_data_columns_temp_t||', '||h_lst_bal_data_columns_temp_b||
2088 ' FROM BSC_TMP_TOT_DATA T, BSC_TMP_BAL_DATA B'||
2089 ' WHERE '||h_lst_on;
2090 h_sql := h_sql||'T.YEAR = B.YEAR (+) AND T.TYPE = B.TYPE (+) AND T.PERIOD = B.PERIOD (+)';
2091 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2092 commit;
2093
2094 -- Enable indexes
2095 IF NOT BSC_UPDATE_UTIL.Create_Unique_Index(x_sum_table,
2096 x_sum_table||'_U1',
2097 h_lst_key_columns||'YEAR, TYPE, PERIOD',
2098 BSC_APPS.summary_index_tbs_type) THEN
2099 RAISE e_unexpected_error;
2100 END IF;
2101
2102 END IF;
2103 END IF;
2104
2105 COMMIT;
2106
2107 -- Fix bug#4463132: Truncate temporary table after use
2108 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PER_CHANGE');
2109 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_PER_CHANGE_BAL');
2110 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TOT_DATA');
2111 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_BAL_DATA');
2112 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_UNION');
2113 commit;
2114
2115 RETURN TRUE;
2116 EXCEPTION
2117 WHEN e_unexpected_error THEN
2118 ROLLBACK;
2119 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_SUMTABLE_CALC_FAILED'),
2120 x_source => 'BSC_UPDATE_SUM.Calculate_Sum_Table_Total');
2121 RETURN FALSE;
2122
2123 WHEN OTHERS THEN
2124 ROLLBACK;
2125 BSC_MESSAGE.Add(x_message => SQLERRM,
2126 x_source => 'BSC_UPDATE_SUM.Calculate_Sum_Table_Total');
2127 RETURN FALSE;
2128
2129 END Calculate_Sum_Table_Total;
2130
2131
2132 /*===========================================================================+
2133 | FUNCTION Get_Minimun_Origin_Period
2134 +============================================================================*/
2135 FUNCTION Get_Minimun_Origin_Period(
2136 x_origin_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2137 x_num_origin_tables IN NUMBER
2138 ) RETURN NUMBER IS
2139
2140 h_table_name VARCHAR2(30);
2141
2142 TYPE t_cursor IS REF CURSOR;
2143
2144 /*
2145 c_current_period t_cursor; -- h_table_name
2146 c_current_period_sql VARCHAR2(2000) := 'SELECT NVL(current_period, 0)'||
2147 ' FROM bsc_db_tables'||
2148 ' WHERE table_name = :1';
2149 */
2150
2151 h_current_period NUMBER;
2152
2153 h_i NUMBER;
2154 h_ret NUMBER;
2155
2156 BEGIN
2157
2158 -- there is at least one origin table
2159
2160 h_table_name := x_origin_tables(1);
2161 /*
2162 OPEN c_current_period FOR c_current_period_sql USING h_table_name;
2163 FETCH c_current_period INTO h_current_period;
2164 IF c_current_period%NOTFOUND THEN
2165 h_current_period := 0;
2166 END IF;
2167 CLOSE c_current_period;
2168 */
2169 BEGIN
2170 SELECT NVL(current_period, 0)
2171 INTO h_current_period
2172 FROM bsc_db_tables
2173 WHERE table_name = h_table_name;
2174 EXCEPTION
2175 WHEN NO_DATA_FOUND THEN
2176 h_current_period := 0;
2177 END;
2178
2179 h_ret := h_current_period;
2180
2181
2182 FOR h_i IN 2 .. x_num_origin_tables LOOP
2183 h_table_name := x_origin_tables(h_i);
2184
2185 /*
2186 OPEN c_current_period FOR c_current_period_sql USING h_table_name;
2187 FETCH c_current_period INTO h_current_period;
2188 IF c_current_period%NOTFOUND THEN
2189 h_current_period := 0;
2190 END IF;
2191 CLOSE c_current_period;
2192 */
2193 BEGIN
2194 SELECT NVL(current_period, 0)
2195 INTO h_current_period
2196 FROM bsc_db_tables
2197 WHERE table_name = h_table_name;
2198 EXCEPTION
2199 WHEN NO_DATA_FOUND THEN
2200 h_current_period := 0;
2201 END;
2202
2203 IF h_current_period < h_ret THEN
2204 h_ret := h_current_period;
2205 END IF;
2206
2207 END LOOP;
2208
2209 RETURN h_ret;
2210
2211 EXCEPTION
2212 WHEN OTHERS THEN
2213 BSC_MESSAGE.Add(x_message => SQLERRM,
2214 x_source => 'BSC_UPDATE_SUM.Get_Minimun_Origin_Period');
2215 RETURN NULL;
2216
2217 END Get_Minimun_Origin_Period;
2218
2219
2220 /*===========================================================================+
2221 | FUNCTION Get_Origin_Tables
2222 +============================================================================*/
2223 FUNCTION Get_Origin_Tables(
2224 x_table_name IN VARCHAR2,
2225 x_origin_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2226 x_num_origin_tables IN OUT NOCOPY NUMBER
2227 ) RETURN BOOLEAN IS
2228
2229 TYPE t_cursor IS REF CURSOR;
2230
2231 /*
2232 c_origin_tables t_cursor; -- x_table_name, 0
2233 c_origin_tables_sql VARCHAR2(2000) := 'SELECT source_table_name'||
2234 ' FROM bsc_db_tables_rels'||
2235 ' WHERE table_name = :1 AND relation_type = :2';
2236 */
2237 CURSOR c_origin_tables (p_table_name VARCHAR2, p_relation_type NUMBER) IS
2238 SELECT source_table_name
2239 FROM bsc_db_tables_rels
2240 WHERE table_name = p_table_name AND relation_type = p_relation_type;
2241
2242 h_origin_table bsc_db_tables_rels.source_table_name%TYPE;
2243
2244 BEGIN
2245 --OPEN c_origin_tables FOR c_origin_tables_sql USING x_table_name, 0;
2246 OPEN c_origin_tables(x_table_name, 0);
2247 FETCH c_origin_tables INTO h_origin_table;
2248 WHILE c_origin_tables%FOUND LOOP
2249 x_num_origin_tables := x_num_origin_tables + 1;
2250 x_origin_tables(x_num_origin_tables) := h_origin_table;
2251
2252 FETCH c_origin_tables INTO h_origin_table;
2253 END LOOP;
2254 CLOSE c_origin_tables;
2255
2256 RETURN TRUE;
2257
2258 EXCEPTION
2259 WHEN OTHERS THEN
2260 BSC_MESSAGE.Add(x_message => SQLERRM,
2261 x_source => 'BSC_UPDATE_SUM.Get_Origin_Tables');
2262 RETURN FALSE;
2263
2264 END Get_Origin_Tables;
2265
2266
2267 --LOCKING: New procedure
2268 /*===========================================================================+
2269 | PROCEDURE Refresh_AW_Kpi_AT
2270 +============================================================================*/
2271 PROCEDURE Refresh_AW_Kpi_AT (
2272 x_indicator IN NUMBER
2273 ) IS
2274 PRAGMA AUTONOMOUS_TRANSACTION;
2275 h_kpi_list dbms_sql.varchar2_table;
2276 BEGIN
2277 -- Fix bug#5134927 verify the aw cubes exists for this kpi
2278 IF BSC_AW_MD_API.Is_Kpi_Present(x_indicator) THEN
2279 h_kpi_list.delete;
2280 h_kpi_list(1) := x_indicator;
2281 bsc_aw_load.load_kpi(
2282 p_kpi_list => h_kpi_list,
2283 p_options => 'DEBUG LOG'
2284 );
2285 END IF;
2286
2287 commit; -- autonomous transactions need to commit
2288 END Refresh_AW_Kpi_AT;
2289
2290
2291 /*===========================================================================+
2292 | FUNCTION Refresh_Zero_MVs
2293 +============================================================================*/
2294 FUNCTION Refresh_Zero_MVs(
2295 x_table_name IN VARCHAR2,
2296 x_mv_name IN VARCHAR2,
2297 x_error_message IN OUT NOCOPY VARCHAR2
2298 ) RETURN BOOLEAN IS
2299
2300 TYPE t_cursor IS REF CURSOR;
2301 h_cursor t_cursor;
2302 h_sql VARCHAR2(2000);
2303 h_data_source_mv VARCHAR2(10);
2304 e_error_refresh EXCEPTION;
2305 h_zero_mv VARCHAR2(30);
2306
2307 BEGIN
2308 h_data_source_mv := 'MV';
2309
2310 h_sql := 'SELECT DISTINCT mv_name'||
2311 ' FROM bsc_kpi_data_tables'||
2312 ' WHERE table_name = :1 AND data_source = :2'||
2313 ' AND mv_name <> :3';
2314 OPEN h_cursor FOR h_sql USING x_table_name, h_data_source_mv, x_mv_name;
2315 LOOP
2316 FETCH h_cursor INTO h_zero_mv;
2317 EXIT WHEN h_cursor%NOTFOUND;
2318
2319 IF NOT BSC_BIA_WRAPPER.Refresh_Summary_MV(h_zero_mv, x_error_message) THEN
2320 RAISE e_error_refresh;
2321 END IF;
2322 COMMIT;
2323
2324 END LOOP;
2325 CLOSE h_cursor;
2326
2327 RETURN TRUE;
2328
2329 EXCEPTION
2330 WHEN e_error_refresh THEN
2331 -- x_error_message should have the error
2332 RETURN FALSE;
2333
2334 WHEN OTHERS THEN
2335 x_error_message := SQLERRM;
2336 RETURN FALSE;
2337
2338 END Refresh_Zero_MVs;
2339
2340
2341 --LOCKING: new function
2342 /*===========================================================================+
2343 | FUNCTION Refresh_Zero_MVs_AT
2344 +============================================================================*/
2345 FUNCTION Refresh_Zero_MVs_AT(
2346 x_table_name IN VARCHAR2,
2347 x_mv_name IN VARCHAR2,
2348 x_error_message IN OUT NOCOPY VARCHAR2
2349 ) RETURN BOOLEAN IS
2350 PRAGMA AUTONOMOUS_TRANSACTION;
2351 h_b BOOLEAN;
2352 BEGIN
2353 h_b := Refresh_Zero_MVs(x_table_name, x_mv_name, x_error_message);
2354 commit; -- all autonomous transaction needs to commit
2355 RETURN h_b;
2356 END Refresh_Zero_MVs_AT;
2357
2358 END BSC_UPDATE_SUM;
[Home] [Help]