1 PACKAGE BODY BSC_UPDATE_INC AS
2 /* $Header: BSCDINCB.pls 120.6 2007/03/01 14:42:47 ankgoel ship $ */
3
4
5 /*===========================================================================+
6 | FUNCTION Add_Related_Tables
7 +============================================================================*/
8 FUNCTION Add_Related_Tables (
9 x_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
10 x_num_tables IN NUMBER,
11 x_purge_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
12 x_num_purge_tables IN OUT NOCOPY NUMBER
13 ) RETURN BOOLEAN IS
14
15 e_unexpected_error EXCEPTION;
16
17 TYPE t_cursor IS REF CURSOR;
18 h_cursor t_cursor;
19
20 h_new_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
21 h_num_new_tables NUMBER;
22
23 h_i NUMBER;
24 h_sql VARCHAR2(32700);
25
26 h_table_name VARCHAR2(30);
27 h_where_tables VARCHAR2(32700);
28
29 BEGIN
30 h_num_new_tables := 0;
31 h_where_tables := NULL;
32
33 FOR h_i IN 1 .. x_num_tables LOOP
34 -- Insert the table
35 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(x_tables(h_i), x_purge_tables, x_num_purge_tables) THEN
36 x_num_purge_tables := x_num_purge_tables + 1;
37 x_purge_tables(x_num_purge_tables) := x_tables(h_i);
38 END IF;
39 END LOOP;
40
41 h_where_tables := BSC_APPS.Get_New_Big_In_Cond_Varchar2(1, 'table_name');
42
43 FOR h_i IN 1 .. x_num_tables LOOP
44 -- Insert the child tables
45 h_sql := 'SELECT table_name';
46 h_sql := h_sql||' FROM bsc_db_tables_rels';
47 h_sql := h_sql||' WHERE source_table_name = :1';
48
49 OPEN h_cursor FOR h_sql USING x_tables(h_i);
50 FETCH h_cursor INTO h_table_name;
51 WHILE h_cursor%FOUND LOOP
52 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_table_name, x_purge_tables, x_num_purge_tables) THEN
53 x_num_purge_tables := x_num_purge_tables + 1;
54 x_purge_tables(x_num_purge_tables) := h_table_name;
55
56 h_num_new_tables := h_num_new_tables + 1;
57 h_new_tables(h_num_new_tables) := h_table_name;
58
59 BSC_APPS.Add_Value_Big_In_Cond(1, h_table_name);
60 END IF;
61
62 FETCH h_cursor INTO h_table_name;
63 END LOOP;
64 CLOSE h_cursor;
65
66 -- Insert the parent tables
67 h_sql := 'SELECT source_table_name';
68 h_sql := h_sql||' FROM bsc_db_tables_rels';
69 h_sql := h_sql||' WHERE table_name = :1';
70
71 OPEN h_cursor FOR h_sql USING x_tables(h_i);
72 FETCH h_cursor INTO h_table_name;
73 WHILE h_cursor%FOUND LOOP
74 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_table_name, x_purge_tables, x_num_purge_tables) THEN
75 x_num_purge_tables := x_num_purge_tables + 1;
76 x_purge_tables(x_num_purge_tables) := h_table_name;
77
78 h_num_new_tables := h_num_new_tables + 1;
79 h_new_tables(h_num_new_tables) := h_table_name;
80
81 BSC_APPS.Add_Value_Big_In_Cond(1, h_table_name);
82 END IF;
83
84 FETCH h_cursor INTO h_table_name;
85 END LOOP;
86 CLOSE h_cursor;
87
88 END LOOP;
89
90 IF h_num_new_tables > 0 THEN
91 -- If one table of one indicator is marked then all tables of that indicator are marked
92 h_sql := 'SELECT table_name';
93 h_sql := h_sql||' FROM bsc_kpi_data_tables';
94 h_sql := h_sql||' WHERE indicator IN (';
95 h_sql := h_sql||' SELECT indicator';
96 h_sql := h_sql||' FROM bsc_kpi_data_tables';
97 h_sql := h_sql||' WHERE '||h_where_tables;
98 h_sql := h_sql||' )';
99 h_sql := h_sql||' AND NOT ('||h_where_tables||')';
100 h_sql := h_sql||' AND table_name IS NOT NULL';
101
102 OPEN h_cursor FOR h_sql;
103 FETCH h_cursor INTO h_table_name;
104 WHILE h_cursor%FOUND LOOP
105 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_table_name, x_purge_tables, x_num_purge_tables) THEN
106 x_num_purge_tables := x_num_purge_tables + 1;
107 x_purge_tables(x_num_purge_tables) := h_table_name;
108
109 h_num_new_tables := h_num_new_tables + 1;
110 h_new_tables(h_num_new_tables) := h_table_name;
111 END IF;
112
113 FETCH h_cursor INTO h_table_name;
114 END LOOP;
115 CLOSE h_cursor;
116
117 IF NOT Add_Related_Tables(h_new_tables, h_num_new_tables, x_purge_tables, x_num_purge_tables) THEN
118 RAISE e_unexpected_error;
119 END IF;
120 END IF;
121
122 RETURN TRUE;
123
124 EXCEPTION
125 WHEN e_unexpected_error THEN
126 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_ADD_REL_TABLES_FAILED'),
127 x_source => 'BSC_UPDATE_INC.Add_Related_Tables');
128 RETURN FALSE;
129
130 WHEN OTHERS THEN
131 BSC_MESSAGE.Add(x_message => SQLERRM,
132 x_source => 'BSC_UPDATE_INC.Add_Related_Tables');
133 RETURN FALSE;
134
135 END Add_Related_Tables;
136
137
138 /*===========================================================================+
139 | FUNCTION Do_Incremental
140 +============================================================================*/
141 FUNCTION Do_Incremental RETURN BOOLEAN IS
142 e_unexpected_error EXCEPTION;
143 --LOCKING
144 e_could_not_get_lock EXCEPTION;
145 e_error_load_rpt_cal EXCEPTION;
146
147 h_i NUMBER;
148
149 -- array for update incremental changes
150 h_input_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
151 h_num_input_tables NUMBER;
152
153 h_color_indicators BSC_UPDATE_UTIL.t_array_of_number;
154 h_num_color_indicators NUMBER;
155
156 h_current_fy NUMBER;
157
158 h_message VARCHAR2(4000);
159
160 h_changed_calendars BSC_UPDATE_UTIL.t_array_of_number;
161 h_num_changed_calendars NUMBER;
162
163 h_calendar_id NUMBER;
164 h_error_message VARCHAR2(2000);
165
166 BEGIN
167 h_num_input_tables := 0;
168 h_num_color_indicators := 0;
169 h_num_changed_calendars := 0;
170
171 BSC_UPDATE_LOG.Write_Line_log(BSC_UPDATE_UTIL.Get_Message('BSC_INCR_CHANGES_REVISION'), BSC_UPDATE_LOG.OUTPUT);
172
173 -- Check for Fiscal year changes in all calendars
174
175 -- Initialize the array h_changed_calendars with the code of the calendars
176 -- whose fiscal year was changed
177 IF NOT Get_Changed_Calendars(h_changed_calendars, h_num_changed_calendars) THEN
178 RAISE e_unexpected_error;
179 END IF;
180
181 IF h_num_changed_calendars > 0 THEN
182 BSC_UPDATE_LOG.Write_Line_log(BSC_UPDATE_UTIL.Get_Message('BSC_FISCAL_YEAR_CHANGE'), BSC_UPDATE_LOG.OUTPUT);
183 END IF;
184
185 FOR h_i IN 1..h_num_changed_calendars LOOP
186 h_calendar_id := h_changed_calendars(h_i);
187
188 -- The beginning fiscal year or month was changed. This action invalidates
189 -- the current data for indicators using this calnedar.
190 -- BSC Loader will delete all current data for affected KPIs and recalculate the
191 -- calendar tables.
192 BSC_UPDATE_LOG.Write_Line_log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'CALENDAR_NAME')||
193 BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||' '||
194 BSC_UPDATE_UTIL.Get_Calendar_Name(h_calendar_id), BSC_UPDATE_LOG.OUTPUT);
195
196 IF BSC_UPDATE_UTIL.Get_Calendar_EDW_Flag(h_calendar_id) = 0 THEN
197 -- This is just for BSC Calendars
198 --LOCKING: Lock the calendar
199 IF NOT BSC_UPDATE_LOCK.Lock_Calendar(h_calendar_id) THEN
200 RAISE e_could_not_get_lock;
201 END IF;
202
203 -- Fix bug#3822940 We need to validate that this is not a DBI calendar
204 IF BSC_UPDATE_UTIL.Get_Calendar_Source(h_calendar_id) = 'BSC' THEN
205 BSC_UPDATE_LOG.Write_Line_log(BSC_UPDATE_UTIL.Get_Message('BSC_CALTABLES_INIT')||
206 ' ('||BSC_UPDATE_UTIL.Get_Calendar_Name(h_calendar_id)||')', BSC_UPDATE_LOG.OUTPUT);
207 --LOCKING: Call the autonomous transaction function
208 IF NOT BSC_UPDATE_UTIL.Init_Calendar_Tables_AT(h_calendar_id) THEN
209 RAISE e_unexpected_error;
210 END IF;
211
212 BSC_UPDATE_LOG.Write_Line_log(BSC_UPDATE_UTIL.Get_Message('BSC_CALTABLES_INITIALIZED'), BSC_UPDATE_LOG.OUTPUT);
213 END IF;
214
215 -- We need to load reporting calendar and load calendar into aw
216 IF BSC_APPS.bsc_mv THEN
217 --LOCKING: call the autonomous transaction
218 IF NOT BSC_BIA_WRAPPER.Load_Reporting_Calendar_AT(h_calendar_id, h_error_message) THEN
219 RAISE e_error_load_rpt_cal;
220 END IF;
221
222 --AW_INTEGRATION: call aw api to import calendars into aw world
223 --LOCKING: call the autonomous transaction
224 BSC_UPDATE_UTIL.Load_Calendar_Into_AW_AT(h_calendar_id);
225 END IF;
226
227 --LOCKING: commit to release the lock
228 COMMIT;
229 END IF;
230
231 -- Purge the data for all indicators using this calendar
232 IF NOT Purge_Data_Indicators_Calendar(h_calendar_id) THEN
233 RAISE e_unexpected_error;
234 END IF;
235
236 -- Reset FISCAL_CHANGE variable to 0
237 --LOCKING: Lock the calendar
238 IF NOT BSC_UPDATE_LOCK.Lock_Calendar(h_calendar_id) THEN
239 RAISE e_could_not_get_lock;
240 END IF;
241
242 UPDATE bsc_sys_calendars_b
243 SET fiscal_change = 0
244 WHERE calendar_id = h_calendar_id;
245
246 --LOCKING: commit to release the locks
247 COMMIT;
248
249 -- Reset the flags for the indicators (flag = 6 or 7) to 0 because now there
250 -- is no data to update.
251 --LOCKING: lock indicators prototype
252 IF NOT BSC_UPDATE_LOCK.Lock_Prototype_Indicators(h_calendar_id) THEN
253 RAISE e_could_not_get_lock;
254 END IF;
255
256 UPDATE bsc_kpis_b
257 SET prototype_flag = 0, last_update_date = SYSDATE
258 WHERE prototype_flag IN (6, 7) AND calendar_id = h_calendar_id;
259
260 -- Color By KPI: Mark KPIs for color re-calculation
261 -- We need to update KPI Prototype flag since it is a Calendar change.
262 -- We would not have done so had it been a Periodicity change.
263 UPDATE bsc_kpi_analysis_measures_b
264 SET prototype_flag = 7
265 WHERE indicator IN (SELECT indicator FROM bsc_kpis_b WHERE calendar_id = h_calendar_id);
266
267 --LOCKING: commit to release the lock
268 COMMIT;
269 END LOOP;
270
271 -- Check for indicators which need to be recalculated
272
273 --BSC-BIS-DIMENSIONS: If Loader is running in KPI_MODE, we do not want to automatically
274 -- refresh all the indicators in prototype 6 or 7. We only refresh the indicators in
275 -- g_indicators. We must to do this becasue the base table must be recreated.
276 -- This is implemented inside Get_Input_Tables_Incremental, Get_Color_Indics_Incremental
277 -- and Reset_Flag_Indicators
278
279 -- Initialize the array h_input_tables with the name of the input tables
280 -- of the indicators with flag 6 (non-structural changes)
281 IF NOT Get_Input_Tables_Incremental(h_input_tables, h_num_input_tables) THEN
282 RAISE e_unexpected_error;
283 END IF;
284
285 IF h_num_input_tables > 0 THEN
286 BSC_UPDATE_LOG.Write_Line_log(BSC_UPDATE_UTIL.Get_Message('BSC_SUMTAB_RECALC_REQUIRED'),
287 BSC_UPDATE_LOG.OUTPUT);
288 IF NOT BSC_UPDATE.Process_Input_Tables(h_input_tables, h_num_input_tables, 1) THEN
289 RAISE e_unexpected_error;
290 END IF;
291 END IF;
292
293 -- Initialize the array h_color_indicators with the code of the indicators
294 -- with flag 7 (re-color)
295 IF NOT Get_Color_Indics_Incremental(h_color_indicators, h_num_color_indicators) THEN
296 RAISE e_unexpected_error;
297 END IF;
298
299 IF h_num_color_indicators > 0 THEN
300 -- LOCKING: Lock temp tables for coloring
301 IF NOT BSC_UPDATE_LOCK.Lock_Temp_Tables('COLOR') THEN
302 RAISE e_could_not_get_lock;
303 END IF;
304
305 -- LOCKING: call the autonomous transaction function
306 IF NOT BSC_UPDATE_COLOR.Create_Temp_Tab_Tables_AT() THEN
307 RAISE e_unexpected_error;
308 END IF;
309
310 --LOCKING: Commit to release locks
311 COMMIT;
312
313 BSC_UPDATE_LOG.Write_Line_log(BSC_UPDATE_UTIL.Get_Message('BSC_KPICOLOR_RECALC_REQUIRED'),
314 BSC_UPDATE_LOG.OUTPUT);
315 FOR h_i IN 1 .. h_num_color_indicators LOOP
316 BSC_UPDATE_LOG.Write_Line_log(BSC_UPDATE_UTIL.Get_Message('BSC_COLOR_CALC')||' '||h_color_indicators(h_i),
317 BSC_UPDATE_LOG.OUTPUT);
318
319 --LOCKING: Lock indicator color
320 IF NOT BSC_UPDATE_LOCK.Lock_Color_Indicator(h_color_indicators(h_i)) THEN
321 RAISE e_could_not_get_lock;
322 END IF;
323
324 -- LOCKING: Call the autonomous transaction
325 IF NOT BSC_UPDATE_COLOR.Color_Indicator_AT(h_color_indicators(h_i)) THEN
326 RAISE e_unexpected_error;
327 END IF;
328
329 -- LOCKING: Commit to release the locks
330 COMMIT;
331
332 h_message := BSC_UPDATE_UTIL.Get_Message('BSC_COLOR_CALC_COMPLETED');
333 h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'INDICATOR', TO_CHAR(h_color_indicators(h_i)));
334 BSC_UPDATE_LOG.Write_Line_log(h_message,
335 BSC_UPDATE_LOG.OUTPUT);
336
337
338 --LOCKING: Lock the update period of the indicator
339 IF NOT BSC_UPDATE_LOCK.Lock_Period_Indicator(h_color_indicators(h_i)) THEN
340 RAISE e_could_not_get_lock;
341 END IF;
342
343 -- LOCKING: review not commit between this point and the commit to release the locks
344
345 -- Update the name of period of indicator in BSC_KPI_DEFAULTS_TL table
346 IF NOT BSC_UPDATE_UTIL.Update_Kpi_Period_Name(h_color_indicators(h_i)) THEN
347 RAISE e_unexpected_error;
348 END IF;
349
350 -- Update kpi time stamp
351 BSC_UPDATE_UTIL.Update_Kpi_Time_Stamp(h_color_indicators(h_i));
352
353 -- Update Tabs time stamp
354 BSC_UPDATE_UTIL.Update_Kpi_Tab_Time_Stamp(h_color_indicators(h_i));
355
356 --LOCKING: commit to release locks
357 COMMIT;
358 END LOOP;
359
360 END IF;
361
362 -- Reset the flags for the indicators (flag = 6 or 7) to 0
363 --LOCKING: lock the indicators with prototype flag 6 or 7
364 IF NOT BSC_UPDATE_LOCK.Lock_Prototype_Indicators THEN
365 RAISE e_could_not_get_lock;
366 END IF;
367
368 IF NOT Reset_Flag_Indicators() THEN
369 RAISE e_unexpected_error;
370 END IF;
371
372 --LOCKING: commit to release locks
373 COMMIT;
374
375 BSC_UPDATE_LOG.Write_Line_log(BSC_UPDATE_UTIL.Get_Message('BSC_INCR_CHANGES_VERIF'), BSC_UPDATE_LOG.OUTPUT);
376
377 RETURN TRUE;
378
379 EXCEPTION
380 WHEN e_unexpected_error THEN
381 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_INCR_CHANGE_REV_FAILED'),
382 x_source => 'BSC_UPDATE_INC.Do_Incremental');
383 RETURN FALSE;
384
385 --LOCKING
386 WHEN e_could_not_get_lock THEN
387 BSC_MESSAGE.Add(x_message => 'Loader could not get the required locks to continue.',
388 x_source => 'BSC_UPDATE_INC.Do_Incremental');
389 RETURN FALSE;
390
391 WHEN e_error_load_rpt_cal THEN
392 BSC_MESSAGE.Add(x_message => 'BSC_BIA_WRAPPER.Load_Reporting_Calendar: '||h_error_message,
393 x_source => 'BSC_UPDATE_INC.Do_Incremental');
394 RETURN FALSE;
395
396 WHEN OTHERS THEN
397 BSC_MESSAGE.Add(x_message => SQLERRM,
398 x_source => 'BSC_UPDATE_INC.Do_Incremental');
399 RETURN FALSE;
400
401 END Do_Incremental;
402
403
404 --LOCKING: new function
405 /*===========================================================================+
406 | FUNCTION Do_Incremental_AT
407 +============================================================================*/
408 FUNCTION Do_Incremental_AT RETURN BOOLEAN IS
409 PRAGMA AUTONOMOUS_TRANSACTION;
410 h_b BOOLEAN;
411 BEGIN
412 h_b := Do_Incremental;
413 commit; -- all autonomous transaction needs to commit
414 RETURN h_b;
415 END Do_Incremental_AT;
416
417
418 /*===========================================================================+
419 | FUNCTION Get_Changed_Calendars
420 +============================================================================*/
421 FUNCTION Get_Changed_Calendars (
422 x_changed_calendars IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
423 x_num_changed_calendars IN OUT NOCOPY NUMBER
424 ) RETURN BOOLEAN IS
425
426 TYPE t_cursor IS REF CURSOR;
427
428 cursor c_calendars( pFiscalChg number) is
429 SELECT calendar_id
430 FROM bsc_sys_calendars_b
431 WHERE fiscal_change = pFiscalChg ;
432
433 h_calendar NUMBER;
434
435 BEGIN
436 -- OPEN c_calendars FOR c_calendars_sql USING 1;
437 OPEN c_calendars (1);
438 FETCH c_calendars INTO h_calendar;
439 WHILE c_calendars%FOUND LOOP
440 x_num_changed_calendars := x_num_changed_calendars + 1;
441 x_changed_calendars(x_num_changed_calendars) := h_calendar;
442
443 FETCH c_calendars INTO h_calendar;
444 END LOOP;
445 CLOSE c_calendars;
446
447 RETURN TRUE;
448
449 EXCEPTION
450 WHEN OTHERS THEN
451 BSC_MESSAGE.Add(x_message => SQLERRM,
452 x_source => 'BSC_UPDATE_INC.Get_Changed_Calendars');
453 RETURN FALSE;
454
455 END Get_Changed_Calendars;
456
457
458 /*===========================================================================+
459 | FUNCTION Get_Color_Indics_Incremental
460 +============================================================================*/
461 FUNCTION Get_Color_Indics_Incremental (
462 x_color_indicators IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
463 x_num_color_indicators IN OUT NOCOPY NUMBER
464 ) RETURN BOOLEAN IS
465
466 TYPE t_cursor IS REF CURSOR;
467 c_indicators t_cursor;
468 h_sql VARCHAR2(32000);
469
470 h_indicator NUMBER;
471 h_i NUMBER;
472 h_where_indics VARCHAR2(32000);
473
474 BEGIN
475 -- Insert into the array x_color_indicators the indicators
476 -- indicators with prototype flag = 7
477
478 h_sql := 'SELECT DISTINCT indicator ' ||
479 'FROM bsc_kpis_b obj ' ||
480 'WHERE ( obj.prototype_flag = :1 ' ||
481 'OR ( obj.prototype_flag = :2 AND EXISTS ( ' ||
482 'SELECT 1 FROM bsc_kpi_analysis_measures_b kpi_meas ' ||
483 'WHERE kpi_meas.indicator = obj.indicator ' ||
484 'AND kpi_meas.prototype_flag = :3 ' ||
485 ')' ||
486 ')' ||
487 ') ';
488
489 --BSC-BIS-DIMENSIONS: If Loader is running in KPI_MODE only consider indicators
490 -- in g_indicators
491 IF BSC_UPDATE.g_kpi_mode THEN
492
493 IF BSC_UPDATE.g_num_indicators > 0 THEN
494
495 h_where_indics := BSC_APPS.Get_New_Big_In_Cond_Number(1, 'indicator');
496 FOR h_i IN 1 .. BSC_UPDATE.g_num_indicators LOOP
497 BSC_APPS.Add_Value_Big_In_Cond(1, BSC_UPDATE.g_indicators(h_i));
498 END LOOP;
499 h_sql := h_sql||' AND ('||h_where_indics||')';
500 END IF;
501 END IF;
502
503 OPEN c_indicators FOR h_sql USING 7, 0, 7;
504 FETCH c_indicators INTO h_indicator;
505 WHILE c_indicators%FOUND LOOP
506 x_num_color_indicators := x_num_color_indicators + 1;
507 x_color_indicators(x_num_color_indicators) := h_indicator;
508
509 FETCH c_indicators INTO h_indicator;
510 END LOOP;
511 CLOSE c_indicators;
512
513 RETURN TRUE;
514 EXCEPTION
515 WHEN OTHERS THEN
516 BSC_MESSAGE.Add(x_message => SQLERRM,
517 x_source => 'BSC_UPDATE_INC.Get_Color_Indics_Incremental');
518 RETURN FALSE;
519
520 END Get_Color_Indics_Incremental;
521
522
523 /*===========================================================================+
524 | FUNCTION Get_Input_Tables
525 +============================================================================*/
526 FUNCTION Get_Input_Tables(
527 x_input_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
528 x_num_input_tables IN OUT NOCOPY NUMBER,
529 x_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
530 x_num_tables IN NUMBER
531 ) RETURN BOOLEAN IS
532 e_unexpected_error EXCEPTION;
533
534 h_i NUMBER;
535
536 h_new_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
537 h_num_new_tables NUMBER;
538
539 h_table VARCHAR2(30);
540
541 TYPE t_cursor IS REF CURSOR;
542
543 cursor c_source_tables(pTableName varchar2) is
544 SELECT source_table_name
545 FROM bsc_db_tables_rels
546 WHERE table_name = pTableName ;
547
548 h_source_table VARCHAR2(30);
549
550 BEGIN
551 h_num_new_tables:= 0;
552
553 FOR h_i IN 1 .. x_num_tables LOOP
554 h_num_new_tables := 0;
555
556 h_table := x_tables(h_i);
557 -- OPEN c_source_tables FOR c_source_tables_sql USING h_table;
558 OPEN c_source_tables (h_table);
559 FETCH c_source_tables INTO h_source_table;
560 IF c_source_tables%NOTFOUND THEN
561 -- h_table is a input table => add to x_input_tables
562 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_table, x_input_tables, x_num_input_tables) THEN
563 x_num_input_tables := x_num_input_tables + 1;
564 x_input_tables(x_num_input_tables) := h_table;
565 END IF;
566 ELSE
567 WHILE c_source_tables%FOUND LOOP
568 h_num_new_tables := h_num_new_tables + 1;
569 h_new_tables(h_num_new_tables) := h_source_table;
570
571 FETCH c_source_tables INTO h_source_table;
572 END LOOP;
573 END IF;
574 CLOSE c_source_tables;
575
576 IF h_num_new_tables > 0 THEN
577 IF NOT Get_Input_Tables(x_input_tables, x_num_input_tables, h_new_tables, h_num_new_tables) THEN
578 RAISE e_unexpected_error;
579 END IF;
580 END IF;
581 END LOOP;
582
583 RETURN TRUE;
584
585 EXCEPTION
586 WHEN e_unexpected_error THEN
587 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_RETR_ITABLES_FAILED'),
588 x_source => 'BSC_UPDATE_INC.Get_Input_Tables');
589 RETURN FALSE;
590
591 WHEN OTHERS THEN
592 BSC_MESSAGE.Add(x_message => SQLERRM,
593 x_source => 'BSC_UPDATE_INC.Get_Input_Tables');
594 RETURN FALSE;
595 END Get_Input_Tables;
596
597
598 /*===========================================================================+
599 | FUNCTION Get_Input_Tables_Incremental
600 +============================================================================*/
601 FUNCTION Get_Input_Tables_Incremental (
602 x_input_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
603 x_num_input_tables IN OUT NOCOPY NUMBER
604 ) RETURN BOOLEAN IS
605
606 e_unexpected_error EXCEPTION;
607
608 TYPE t_cursor IS REF CURSOR;
609 c_tables t_cursor;
610 h_sql VARCHAR2(32700);
611
612 h_table VARCHAR2(30);
613
614 h_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
615 h_num_tables NUMBER;
616
617 h_i NUMBER;
618 h_where_indics VARCHAR2(32700);
619
620 BEGIN
621 h_num_tables := 0;
622
623 -- Insert into the local array h_tables the system tables used
624 -- by the indicators with prototype flag = 6
625
626 h_sql := 'SELECT DISTINCT t.table_name'||
627 ' FROM bsc_kpis_vl k, bsc_kpi_data_tables t'||
628 ' WHERE k.indicator = t.indicator AND'||
629 ' k.prototype_flag = :1 AND t.table_name IS NOT NULL';
630
631 --BSC-BIS-DIMENSIONS: If Loader is running in KPI_MODE only consider indicators
632 -- in g_indicators
633 IF BSC_UPDATE.g_kpi_mode THEN
634 IF BSC_UPDATE.g_num_indicators > 0 THEN
635 h_where_indics := BSC_APPS.Get_New_Big_In_Cond_Number(1, 'k.indicator');
636 FOR h_i IN 1 .. BSC_UPDATE.g_num_indicators LOOP
637 BSC_APPS.Add_Value_Big_In_Cond(1, BSC_UPDATE.g_indicators(h_i));
638 END LOOP;
639 h_sql := h_sql||' AND ('||h_where_indics||')';
640 END IF;
641 END IF;
642
643 OPEN c_tables FOR h_sql USING 6;
644 FETCH c_tables INTO h_table;
645 WHILE c_tables%FOUND LOOP
646 h_num_tables := h_num_tables + 1;
647 h_tables(h_num_tables) := h_table;
648
649 FETCH c_tables INTO h_table;
650 END LOOP;
651 CLOSE c_tables;
652
653 -- Insert into the array x_input_tables the input tables from
654 -- where the system tables are originated.
655 IF NOT Get_Input_Tables(x_input_tables, x_num_input_tables, h_tables, h_num_tables) THEN
656 RAISE e_unexpected_error;
657 END IF;
658
659 RETURN TRUE;
660 EXCEPTION
661 WHEN e_unexpected_error THEN
662 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_RETR_INCR_ITABLES_FAILED'),
663 x_source => 'BSC_UPDATE_INC.Get_Input_Tables_Incremental');
664 RETURN FALSE;
665
666 WHEN OTHERS THEN
667 BSC_MESSAGE.Add(x_message => SQLERRM,
668 x_source => 'BSC_UPDATE_INC.Get_Input_Tables_Incremental');
669 RETURN FALSE;
670
671 END Get_Input_Tables_Incremental;
672
673
674 /*===========================================================================+
675 | FUNCTION Purge_Data_All_Indicators
676 +============================================================================*/
677 FUNCTION Purge_Data_All_Indicators RETURN BOOLEAN IS
678 e_unexpected_error EXCEPTION;
679
680 h_purge_indicators BSC_UPDATE_UTIL.t_array_of_number;
681 h_num_purge_indicators NUMBER;
682
683 TYPE t_cursor IS REF CURSOR;
684
685 cursor c_All_Indicators is
686 SELECT indicator
687 FROM bsc_kpis_vl;
688
689 h_indicator NUMBER;
690
691 BEGIN
692 h_num_purge_indicators := 0;
693
694 -- Initialize the array h_purge_indicators with all the indicators
695 -- in the system.
696
697 OPEN c_All_Indicators ;
698 FETCH c_All_Indicators INTO h_indicator;
699 WHILE c_All_Indicators%FOUND LOOP
700 h_num_purge_indicators := h_num_purge_indicators + 1;
701 h_purge_indicators(h_num_purge_indicators) := h_indicator;
702
703 FETCH c_All_Indicators INTO h_indicator;
704 END LOOP;
705 CLOSE c_All_Indicators;
706
707 -- Purge the indicators in the array h_purge_indicators
708 IF NOT Purge_Indicators_Data(h_purge_indicators, h_num_purge_indicators) THEN
709 RAISE e_unexpected_error;
710 END IF;
711
712 RETURN TRUE;
713
714 EXCEPTION
715 WHEN e_unexpected_error THEN
716 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_PURGE_KPIS_FAILED'),
717 x_source => 'BSC_UPDATE_INC.Purge_Data_All_Indicators');
718 RETURN FALSE;
719
720 WHEN OTHERS THEN
721 BSC_MESSAGE.Add(x_message => SQLERRM,
722 x_source => 'BSC_UPDATE_INC.Purge_Data_All_Indicators');
723 RETURN FALSE;
724
725 END Purge_Data_All_Indicators;
726
727
728 /*===========================================================================+
729 | FUNCTION Purge_Data_Indicators_Calendar
730 +============================================================================*/
731 FUNCTION Purge_Data_Indicators_Calendar(
732 x_calendar_id IN NUMBER
733 ) RETURN BOOLEAN IS
734
735 e_unexpected_error EXCEPTION;
736 --LOCKING
737 e_could_not_get_lock EXCEPTION;
738
739 h_purge_indicators BSC_UPDATE_UTIL.t_array_of_number;
740 h_num_purge_indicators NUMBER;
741
742 TYPE t_cursor IS REF CURSOR;
743
744 cursor c_indicators(pCalId number) is
745 SELECT indicator
746 FROM bsc_kpis_vl
747 WHERE calendar_id = pCalId ;
748
749 h_indicator NUMBER;
750
751 BEGIN
752
753 h_num_purge_indicators := 0;
754
755 -- Initialize the array h_purge_indicators
756
757 OPEN c_indicators (x_calendar_id);
758 FETCH c_indicators INTO h_indicator;
759 WHILE c_indicators%FOUND LOOP
760 h_num_purge_indicators := h_num_purge_indicators + 1;
761 h_purge_indicators(h_num_purge_indicators) := h_indicator;
762
763 FETCH c_indicators INTO h_indicator;
764 END LOOP;
765 CLOSE c_indicators;
766
767 -- Purge the indicators in the array h_purge_indicators
768 IF NOT Purge_Indicators_Data(h_purge_indicators, h_num_purge_indicators) THEN
769 RAISE e_unexpected_error;
770 END IF;
771
772 RETURN TRUE;
773
774 EXCEPTION
775 --LOCKING
776 WHEN e_could_not_get_lock THEN
777 BSC_MESSAGE.Add(x_message => 'Loader could not get the required locks to continue.',
778 x_source => 'BSC_UPDATE_INC.Purge_Data_Indicators_Calendar');
779 RETURN FALSE;
780
781 WHEN e_unexpected_error THEN
782 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_PURGE_KPIS_FAILED'),
783 x_source => 'BSC_UPDATE_INC.Purge_Data_Indicators_Calendar');
784 RETURN FALSE;
785
786 WHEN OTHERS THEN
787 BSC_MESSAGE.Add(x_message => SQLERRM,
788 x_source => 'BSC_UPDATE_INC.Purge_Data_Indicators_Calendar');
789 RETURN FALSE;
790
791 END Purge_Data_Indicators_Calendar;
792
793
794 /*===========================================================================+
795 | FUNCTION Purge_Indicators_Data
796 +============================================================================*/
797 FUNCTION Purge_Indicators_Data (
798 x_purge_indicators IN BSC_UPDATE_UTIL.t_array_of_number,
799 x_num_purge_indicators IN NUMBER
800 ) RETURN BOOLEAN IS
801 e_unexpected_error EXCEPTION;
802 Begin
803 return Purge_Indicators_Data(x_purge_indicators,x_num_purge_indicators,'N');
804 EXCEPTION
805 WHEN e_unexpected_error THEN
806 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_PURGE_KPIS_FAILED'),
807 x_source => 'BSC_UPDATE_INC.Purge_Indicators_Data');
808 RETURN FALSE;
809
810 WHEN OTHERS THEN
811 BSC_MESSAGE.Add(x_message => SQLERRM,
812 x_source => 'BSC_UPDATE_INC.Purge_Indicators_Data');
813 RETURN FALSE;
814
815 END Purge_Indicators_Data;
816
817 FUNCTION Purge_Indicators_Data (
818 x_purge_indicators IN BSC_UPDATE_UTIL.t_array_of_number,
819 x_num_purge_indicators IN NUMBER,
820 x_keep_input_data varchar2
821 ) RETURN BOOLEAN IS
822
823 e_unexpected_error EXCEPTION;
824 --LOCKING
825 e_could_not_get_lock EXCEPTION;
826
827 h_purge_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
828 h_num_purge_tables NUMBER;
829
830 h_system_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
831 h_num_system_tables NUMBER;
832
833 TYPE t_cursor IS REF CURSOR;
834 h_cursor t_cursor;
835
836 h_where_indics VARCHAR2(32700);
837 h_where_tables VARCHAR2(32700);
838 h_sql VARCHAR2(32700);
839
840 h_i NUMBER;
841 h_table_name VARCHAR2(30);
842 h_indicator NUMBER;
843
844 h_current_fy NUMBER;
845 h_message VARCHAR2(4000);
846
847 h_calendar_id NUMBER;
848 l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
849 l_num_bind_vars NUMBER;
850
851 h_where_tables_mv VARCHAR2(32700);
852 h_base_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
853 h_num_base_tables NUMBER;
854
855 --AW_INTEGRATION: New variables
856 h_aw_indicators BSC_UPDATE_UTIL.t_array_of_number;
857 h_num_aw_indicators NUMBER;
858 h_aw_table_name VARCHAR2(30);
859
860 --LOCKING: new variables
861 h_lock_indicators BSC_UPDATE_UTIL.t_array_of_number;
862 h_num_lock_indicators NUMBER;
863
864 -- ENH_B_TABLES_PERF: new variable
865 h_proj_tbl_name VARCHAR2(30);
866
867 BEGIN
868 h_num_purge_tables := 0;
869 h_num_system_tables := 0;
870 h_where_indics := NULL;
871 h_where_tables := NULL;
872 h_sql := NULL;
873 l_num_bind_vars := 0;
874 h_where_tables_mv := NULL;
875 h_num_base_tables := 0;
876 --AW_INTEGRATION: init this variable
877 h_num_aw_indicators := 0;
878 --LOCKING
879 h_num_lock_indicators := 0;
880
881 -- Initialize the array h_system_tables with the tables used for the
882 -- indicators in x_purge_indicators
883 IF x_num_purge_indicators > 0 THEN
884 h_where_indics := BSC_APPS.Get_New_Big_In_Cond_Number(1, 'indicator');
885 FOR h_i IN 1 .. x_num_purge_indicators LOOP
886 BSC_APPS.Add_Value_Big_In_Cond(1, x_purge_indicators(h_i));
887
888 --AW_INTEGRATION: We need to truncate the cubes of the Aw indicators.
889 --I am going to add aw kpis in the array h_aw_indicators to be truncated later.
890 IF BSC_UPDATE_UTIL.Get_Kpi_Impl_Type(x_purge_indicators(h_i)) = 2 THEN
891 h_num_aw_indicators := h_num_aw_indicators + 1;
892 h_aw_indicators(h_num_aw_indicators) := x_purge_indicators(h_i);
893 END IF;
894
895 --LOCKING: Add kpis to array h_lock_indicators. Later I need to lock the
896 -- period of those indicators
897 h_num_lock_indicators := h_num_lock_indicators + 1;
898 h_lock_indicators(h_num_lock_indicators) := x_purge_indicators(h_i);
899 END LOOP;
900
901 h_sql := 'SELECT DISTINCT table_name';
902 h_sql := h_sql||' FROM bsc_kpi_data_tables';
903 h_sql := h_sql||' WHERE ('||h_where_indics||') AND table_name IS NOT NULL';
904
905 OPEN h_cursor FOR h_sql;
906 FETCH h_cursor INTO h_table_name;
907 WHILE h_cursor%FOUND LOOP
908 h_num_system_tables := h_num_system_tables + 1;
909 h_system_tables(h_num_system_tables) := h_table_name;
910
911 FETCH h_cursor INTO h_table_name;
912 END LOOP;
913 CLOSE h_cursor;
914
915 END IF;
916
917 IF h_num_system_tables > 0 Then
918 -- Insert in the array h_purge_tables all the tables in the current graph that have
919 -- any relation with the system tables in the array h_system_tables.
920
921 IF NOT Add_Related_Tables(h_system_tables, h_num_system_tables, h_purge_tables, h_num_purge_tables) THEN
922 RAISE e_unexpected_error;
923 END IF;
924
925 -- Build the condition string on the tables names to purge
926 h_where_tables := BSC_APPS.Get_New_Big_In_Cond_Varchar2(2, 'table_name');
927 FOR h_i IN 1 .. h_num_purge_tables LOOP
928 BSC_APPS.Add_Value_Big_In_Cond(2, h_purge_tables(h_i));
929 END LOOP;
930
931 BSC_UPDATE_LOG.Write_Line_log(BSC_UPDATE_UTIL.Get_Message('BSC_KPIDATA_DELETION'), BSC_UPDATE_LOG.OUTPUT);
932
933 FOR h_i IN 1 .. x_num_purge_indicators LOOP
934 BSC_UPDATE_LOG.Write_Line_log(x_purge_indicators(h_i), BSC_UPDATE_LOG.OUTPUT);
935 END LOOP;
936
937 -- Add to the condition string of the indicators the interrelated
938 -- indicators
939 h_sql := 'SELECT DISTINCT indicator';
940 h_sql := h_sql||' FROM bsc_kpi_data_tables';
941 h_sql := h_sql||' WHERE ('||h_where_tables||')';
942 h_sql := h_sql||' AND NOT ('||h_where_indics||')';
943
944 OPEN h_cursor FOR h_sql;
945 FETCH h_cursor INTO h_indicator;
946 WHILE h_cursor%FOUND LOOP
947 BSC_APPS.Add_Value_Big_In_Cond(1, h_indicator);
948
949 BSC_UPDATE_LOG.Write_Line_log(h_indicator, BSC_UPDATE_LOG.OUTPUT);
950
951 --AW_INTEGRATION: We need to truncate the cubes of the Aw indicators.
952 --I am going to add aw kpis in the array h_aw_indicators to be truncated later.
953 IF BSC_UPDATE_UTIL.Get_Kpi_Impl_Type(h_indicator) = 2 THEN
954 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Number(h_indicator, h_aw_indicators, h_num_aw_indicators) THEN
955 h_num_aw_indicators := h_num_aw_indicators + 1;
956 h_aw_indicators(h_num_aw_indicators) := h_indicator;
957 END IF;
958 END IF;
959
960 --LOCKING: Add kpis to array h_lock_indicators. Later I need to lock the
961 -- period of those indicators
962 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Number(h_indicator, h_lock_indicators, h_num_lock_indicators) THEN
963 h_num_lock_indicators := h_num_lock_indicators + 1;
964 h_lock_indicators(h_num_lock_indicators) := h_indicator;
965 END IF;
966
967 FETCH h_cursor INTO h_indicator;
968 END LOOP;
969 CLOSE h_cursor;
970
971 -- Delete the tables
972 -- BSC-MV Note: For new architecture we need to truncate only the base tables
973 -- and summary tables created for projections at kpi level.
974 -- By design those tables has generation_type <> -1
975 -- Then we need to refresh all the MVs affected by those base tables to delete the data.
976 -- AW_INTEGRATION: no changes to this portion of code, same logic applies to AW
977 IF BSC_APPS.bsc_mv THEN
978 h_where_tables_mv := BSC_APPS.Get_New_Big_In_Cond_Varchar2(3, 'r.table_name');
979 END IF;
980
981 FOR h_i IN 1 .. h_num_purge_tables LOOP
982 IF BSC_APPS.bsc_mv THEN
983 -- BSC-MV Architecture
984
985 IF BSC_UPDATE_UTIL.Get_Table_Generation_Type(h_purge_tables(h_i)) <> -1 THEN
986 -- It is an input, base table or a table created to store the projection at kpi level
987
988 --added for 5.2 when launched from RSG, we should not truncate the input tables
989 --input tables will be populated first. then rsg called. for initial load, rsg will call
990 --purge. then immediately, it will call load. if we truncate input table, there are no rows
991 --to process
992
993 IF x_keep_input_data='Y' THEN
994 IF BSC_UPDATE_UTIL.Get_Table_Type(h_purge_tables(h_i)) <> 0 THEN
995 -- It is not an input table, we can truncate it.
996
997 --LOCKING: Lock the table
998 IF NOT BSC_UPDATE_LOCK.Lock_Table(h_purge_tables(h_i)) THEN
999 RAISE e_could_not_get_lock;
1000 END IF;
1001
1002 --LOCKING: Call the autonomous transaction function
1003 BSC_UPDATE_UTIL.Truncate_Table_AT(h_purge_tables(h_i));
1004
1005 -- ENH_B_TABLES_PERF: with the new strategy the B table may have a projection table
1006 -- We need to truncate the projection table too.
1007 h_proj_tbl_name := BSC_UPDATE_BASE_V2.Get_Base_Proj_Tbl_Name(h_purge_tables(h_i));
1008 IF h_proj_tbl_name IS NOT NULL THEN
1009 BSC_UPDATE_UTIL.Truncate_Table_AT(h_proj_tbl_name);
1010 END IF;
1011
1012 --LOCKING: commit to release locks
1013 COMMIT;
1014
1015 h_message := BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_NAME_DELETION');
1016 h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_purge_tables(h_i));
1017 BSC_UPDATE_LOG.Write_Line_log(h_message, BSC_UPDATE_LOG.OUTPUT);
1018
1019 BSC_APPS.Add_Value_Big_In_Cond(3, h_purge_tables(h_i));
1020 END IF;
1021 ELSE
1022 -- We can truncate the table no matter if it is an input table
1023
1024 --LOCKING: Lock the table
1025 IF NOT BSC_UPDATE_LOCK.Lock_Table(h_purge_tables(h_i)) THEN
1026 RAISE e_could_not_get_lock;
1027 END IF;
1028
1029 --LOCKING: Call the autonomous transaction function
1030 BSC_UPDATE_UTIL.Truncate_Table_AT(h_purge_tables(h_i));
1031
1032 -- ENH_B_TABLES_PERF: with the new strategy the B table may have a projection table
1033 -- We need to truncate the projection table too.
1034 h_proj_tbl_name := BSC_UPDATE_BASE_V2.Get_Base_Proj_Tbl_Name(h_purge_tables(h_i));
1035 IF h_proj_tbl_name IS NOT NULL THEN
1036 BSC_UPDATE_UTIL.Truncate_Table_AT(h_proj_tbl_name);
1037 END IF;
1038
1039 --LOCKING: commit to release locks
1040 COMMIT;
1041
1042 h_message := BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_NAME_DELETION');
1043 h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_purge_tables(h_i));
1044 BSC_UPDATE_LOG.Write_Line_log(h_message, BSC_UPDATE_LOG.OUTPUT);
1045
1046 BSC_APPS.Add_Value_Big_In_Cond(3, h_purge_tables(h_i));
1047 END IF;
1048 END IF;
1049 ELSE
1050 -- Summary tables architecture
1051
1052 --added for 5.2 when launched from RSG, we should not truncate the input tables
1053 --input tables will be populated first. then rsg called. for initial load, rsg will call
1054 --purge. then immediately, it will call load. if we truncate input table, there are no rows
1055 --to process
1056
1057 IF x_keep_input_data='Y' THEN
1058 IF BSC_UPDATE_UTIL.Get_Table_Type(h_purge_tables(h_i)) <> 0 THEN
1059 -- It is not an input table, we can truncate it.
1060
1061 --LOCKING: Lock the table
1062 IF NOT BSC_UPDATE_LOCK.Lock_Table(h_purge_tables(h_i)) THEN
1063 RAISE e_could_not_get_lock;
1064 END IF;
1065
1066 --LOCKING: Call the autonomous transaction function
1067 BSC_UPDATE_UTIL.Truncate_Table_AT(h_purge_tables(h_i));
1068
1069 -- ENH_B_TABLES_PERF: with the new strategy the B table may have a projection table
1070 -- We need to truncate the projection table too.
1071 h_proj_tbl_name := BSC_UPDATE_BASE_V2.Get_Base_Proj_Tbl_Name(h_purge_tables(h_i));
1072 IF h_proj_tbl_name IS NOT NULL THEN
1073 BSC_UPDATE_UTIL.Truncate_Table_AT(h_proj_tbl_name);
1074 END IF;
1075
1076 --LOCKING: commit to release locks
1077 COMMIT;
1078
1079 h_message := BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_NAME_DELETION');
1080 h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_purge_tables(h_i));
1081 BSC_UPDATE_LOG.Write_Line_log(h_message, BSC_UPDATE_LOG.OUTPUT);
1082 END IF;
1083 ELSE
1084 -- We can truncate all tables no matter if it is an input table
1085 --LOCKING: Lock the table
1086 IF NOT BSC_UPDATE_LOCK.Lock_Table(h_purge_tables(h_i)) THEN
1087 RAISE e_could_not_get_lock;
1088 END IF;
1089
1090 --LOCKING: Call the autonomous transaction function
1091 BSC_UPDATE_UTIL.Truncate_Table_AT(h_purge_tables(h_i));
1092
1093 -- ENH_B_TABLES_PERF: with the new strategy the B table may have a projection table
1094 -- We need to truncate the projection table too.
1095 h_proj_tbl_name := BSC_UPDATE_BASE_V2.Get_Base_Proj_Tbl_Name(h_purge_tables(h_i));
1096 IF h_proj_tbl_name IS NOT NULL THEN
1097 BSC_UPDATE_UTIL.Truncate_Table_AT(h_proj_tbl_name);
1098 END IF;
1099
1100 --LOCKING: commit to release locks
1101 COMMIT;
1102
1103 h_message := BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_NAME_DELETION');
1104 h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_purge_tables(h_i));
1105 BSC_UPDATE_LOG.Write_Line_log(h_message, BSC_UPDATE_LOG.OUTPUT);
1106 END IF;
1107 END IF;
1108 END LOOP;
1109
1110 -- BSC-MV Note: Refresh all MVs affected by the base tables
1111 -- AW_INTEGRATION: For Aw kpis, we need to truncate the AW table created for the base table
1112 -- and also we need to tuncate the kpi cubes.
1113 IF BSC_APPS.bsc_mv THEN
1114 -- Get the base tables
1115 h_sql := 'SELECT r.table_name'||
1116 ' FROM bsc_db_tables_rels r, bsc_db_tables t'||
1117 ' WHERE r.source_table_name = t.table_name AND'||
1118 ' t.table_type = :1 AND ('||h_where_tables_mv||')';
1119 OPEN h_cursor FOR h_sql USING 0;
1120 LOOP
1121 FETCH h_cursor INTO h_table_name;
1122 EXIT WHEN h_cursor%NOTFOUND;
1123
1124 IF BSC_UPDATE_UTIL.Is_Table_For_AW_Kpi(h_table_name) THEN
1125 -- Base table for AW indicators
1126
1127 -- Fix bug#4567847: there is no aw table created for the base table any more.
1128 NULL;
1129 ELSE
1130 -- Base table for MV indicators
1131 h_num_base_tables := h_num_base_tables + 1;
1132 h_base_tables(h_num_base_tables) := h_table_name;
1133 END IF;
1134 END LOOP;
1135 CLOSE h_cursor;
1136
1137 -- Refresh Mvs
1138 IF h_num_base_tables > 0 THEN
1139 IF NOT BSC_UPDATE.Refresh_System_MVs(h_base_tables, h_num_base_tables) THEN
1140 RAISE e_unexpected_error;
1141 END IF;
1142 END IF;
1143
1144 -- AW_INTEGRATION: Truncate kpis cubes
1145 FOR h_i IN 1..h_num_aw_indicators LOOP
1146 --LOCKING: lock the aw cubes of the indicator
1147 IF NOT BSC_UPDATE_LOCK.Lock_AW_Indicator_Cubes(h_aw_indicators(h_i)) THEN
1148 RAISE e_could_not_get_lock;
1149 END IF;
1150
1151 --LOCKING: call the autonomous transaction procedure
1152 Purge_AW_Indicator_AT(h_aw_indicators(h_i));
1153
1154 --LOCKING: commit to release the locks
1155 COMMIT;
1156 END LOOP;
1157 END IF;
1158
1159 -- Reset to gray the color of the indicators
1160 --LOCKING: Lock the color of the indicators
1161 IF NOT BSC_UPDATE_LOCK.Lock_Color_Indicators(h_lock_indicators, h_num_lock_indicators) THEN
1162 RAISE e_could_not_get_lock;
1163 END IF;
1164
1165 h_sql := 'UPDATE bsc_sys_kpi_colors';
1166 h_sql := h_sql||' SET kpi_color = :1,';
1167 h_sql := h_sql||' actual_data = NULL,';
1168 h_sql := h_sql||' budget_data = NULL';
1169 h_sql := h_sql||' WHERE ('||h_where_indics||')';
1170 l_bind_vars_values.delete;
1171 l_bind_vars_values(1) := BSC_UPDATE_COLOR.GRAY;
1172 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
1173
1174 h_sql := 'UPDATE bsc_sys_objective_colors';
1175 h_sql := h_sql||' SET obj_color = :1 ';
1176 h_sql := h_sql||' WHERE ('||h_where_indics||')';
1177 l_bind_vars_values.delete;
1178 l_bind_vars_values(1) := BSC_UPDATE_COLOR.GRAY;
1179 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
1180
1181
1182 --LOCKING: commit to release the locks
1183 COMMIT;
1184
1185 -- Reset some information by calendar
1186 --LOCKING: Lock the tables. We are going to udate the current period of
1187 -- all the tables and we need to prevent loader is processing those tables at the same time
1188 IF NOT BSC_UPDATE_LOCK.Lock_Tables(h_purge_tables, h_num_purge_tables) THEN
1189 RAISE e_could_not_get_lock;
1190 END IF;
1191
1192 --LOCKING: Lock the update period of the indicators. We are going to upadte the current period
1193 -- of all the affected indicators
1194 IF NOT BSC_UPDATE_LOCK.Lock_Period_Indicators(h_lock_indicators, h_num_lock_indicators) THEN
1195 RAISE e_could_not_get_lock;
1196 END IF;
1197
1198 -- LOCKING: review not commit between this point and the commit to release the locks
1199
1200 h_sql := 'SELECT DISTINCT calendar_id'||
1201 ' FROM bsc_kpis_b'||
1202 ' WHERE ('||h_where_indics||')';
1203
1204 OPEN h_cursor FOR h_sql;
1205 FETCH h_cursor INTO h_calendar_id;
1206 WHILE h_cursor%FOUND LOOP
1207 -- Get the current fiscal year
1208 h_current_fy := BSC_UPDATE_UTIL.Get_Calendar_Fiscal_Year(h_calendar_id);
1209
1210 -- Reset the current period of the tables
1211 h_sql := 'UPDATE'||
1212 ' bsc_db_tables '||
1213 'SET '||
1214 ' current_period = :1, '||
1215 ' current_subperiod = 0 '||
1216 'WHERE '||
1217 ' table_type <> 2 AND '||
1218 ' periodicity_id IN ('||
1219 ' SELECT '||
1220 ' periodicity_id '||
1221 ' FROM '||
1222 ' bsc_sys_periodicities_vl'||
1223 ' WHERE '||
1224 ' calendar_id = :2 AND '||
1225 ' yearly_flag = 1) AND '||
1226 ' ('||h_where_tables||')';
1227 l_bind_vars_values.delete;
1228 l_bind_vars_values(1) := h_current_fy;
1229 l_bind_vars_values(2) := h_calendar_id;
1230 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
1231
1232 h_sql := 'UPDATE'||
1233 ' bsc_db_tables '||
1234 'SET '||
1235 ' current_period = 1, '||
1236 ' current_subperiod = 0 '||
1237 'WHERE '||
1238 ' table_type <> 2 AND '||
1239 ' periodicity_id IN ('||
1240 ' SELECT '||
1241 ' periodicity_id '||
1242 ' FROM '||
1243 ' bsc_sys_periodicities_vl'||
1244 ' WHERE '||
1245 ' calendar_id = :1 AND '||
1246 ' yearly_flag = 0) AND '||
1247 ' ('||h_where_tables||')';
1248 l_bind_vars_values.delete;
1249 l_bind_vars_values(1) := h_calendar_id;
1250 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
1251
1252 -- Reset the current period of the indicators
1253 h_sql := 'UPDATE '||
1254 ' bsc_kpi_periodicities '||
1255 'SET '||
1256 ' current_period = :1 '||
1257 'WHERE '||
1258 ' periodicity_id IN ('||
1259 ' SELECT '||
1260 ' periodicity_id '||
1261 ' FROM '||
1262 ' bsc_sys_periodicities_vl'||
1263 ' WHERE '||
1264 ' calendar_id = :2 AND '||
1265 ' yearly_flag = 1) AND '||
1266 ' ('||h_where_indics||')';
1267 l_bind_vars_values.delete;
1268 l_bind_vars_values(1) := h_current_fy;
1269 l_bind_vars_values(2) := h_calendar_id;
1270 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
1271
1272 h_sql := 'UPDATE '||
1273 ' bsc_kpi_periodicities '||
1274 'SET '||
1275 ' current_period = 1 '||
1276 'WHERE '||
1277 ' periodicity_id IN ('||
1278 ' SELECT '||
1279 ' periodicity_id '||
1280 ' FROM '||
1281 ' bsc_sys_periodicities_vl'||
1282 ' WHERE '||
1283 ' calendar_id = :1 AND '||
1284 ' yearly_flag = 0) AND '||
1285 ' ('||h_where_indics||')';
1286 l_bind_vars_values.delete;
1287 l_bind_vars_values(1) := h_calendar_id;
1288 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
1289
1290 FETCH h_cursor INTO h_calendar_id;
1291 END LOOP;
1292 CLOSE h_cursor;
1293
1294 -- Update the name of period of indicators in BSC_KPI_DEFAULTS_TL table
1295 FOR h_i IN 1 .. x_num_purge_indicators LOOP
1296 --LOCKING: there is not commit inside this function, so no need to call
1297 -- an autonomous transaction
1298 IF NOT BSC_UPDATE_UTIL.Update_Kpi_Period_Name(x_purge_indicators(h_i)) THEN
1299 RAISE e_unexpected_error;
1300 END IF;
1301 END LOOP;
1302
1303 -- Update date of indicators
1304 h_sql := 'UPDATE bsc_kpi_defaults_b SET last_update_date = SYSDATE';
1305 h_sql := h_sql||' WHERE ('||h_where_indics||')';
1306 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1307
1308 -- Update Kpis time stamp
1309 BSC_UPDATE_UTIL.Update_Kpi_Time_Stamp(h_where_indics);
1310
1311 -- Update Tabs time stamp
1312 BSC_UPDATE_UTIL.Update_Kpi_Tab_Time_Stamp(h_where_indics);
1313
1314 -- LOCKING: commit to release the locks
1315 COMMIT;
1316 END IF;
1317
1318 RETURN TRUE;
1319
1320 EXCEPTION
1321 --LOCKING
1322 WHEN e_could_not_get_lock THEN
1323 BSC_MESSAGE.Add(x_message => 'Loader could not get the required locks to continue.',
1324 x_source => 'BSC_UPDATE_INC.Purge_Indicators_Data');
1325 RETURN FALSE;
1326
1327 WHEN e_unexpected_error THEN
1328 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_PURGE_KPIS_FAILED'),
1329 x_source => 'BSC_UPDATE_INC.Purge_Indicators_Data');
1330 RETURN FALSE;
1331
1332 WHEN OTHERS THEN
1333 BSC_MESSAGE.Add(x_message => SQLERRM,
1334 x_source => 'BSC_UPDATE_INC.Purge_Indicators_Data');
1335 RETURN FALSE;
1336
1337 END Purge_Indicators_Data;
1338
1339 --LOCKING: new function
1340 /*===========================================================================+
1341 | FUNCTION Purge_Indicators_Data_AT
1342 +============================================================================*/
1343 FUNCTION Purge_Indicators_Data_AT (
1344 x_purge_indicators IN BSC_UPDATE_UTIL.t_array_of_number,
1345 x_num_purge_indicators IN NUMBER,
1346 x_keep_input_data varchar2
1347 ) RETURN BOOLEAN IS
1348 PRAGMA AUTONOMOUS_TRANSACTION;
1349 h_b BOOLEAN;
1350 BEGIN
1351 h_b := Purge_Indicators_Data(x_purge_indicators, x_num_purge_indicators, x_keep_input_data);
1352 commit; -- all autonomous transaction needs to commit
1353 RETURN h_b;
1354 END Purge_Indicators_Data_AT;
1355
1356
1357 /*===========================================================================+
1358 | FUNCTION Reset_Flag_Indicators
1359 +============================================================================*/
1360 FUNCTION Reset_Flag_Indicators
1361 RETURN BOOLEAN
1362 IS
1363 h_sql VARCHAR2(32000);
1364 h_sql_kpi VARCHAR2(32000);
1365 h_where_indics VARCHAR2(32000);
1366 h_i NUMBER;
1367 BEGIN
1368 IF (BSC_UPDATE.g_kpi_mode) AND (BSC_UPDATE.g_num_indicators > 0) THEN
1369 h_sql := 'UPDATE bsc_kpis_b'||
1370 ' SET prototype_flag = 0, last_update_date = SYSDATE'||
1371 ' WHERE prototype_flag IN (6, 7)';
1372
1373 -- Color By KPI: Mark KPIs for color re-calculation
1374 h_sql_kpi := 'UPDATE bsc_kpi_analysis_measures_b ' ||
1375 ' SET prototype_flag = 0 ' ||
1376 ' WHERE prototype_flag = 7 ';
1377
1378 h_where_indics := BSC_APPS.Get_New_Big_In_Cond_Number(1, 'indicator');
1379 FOR h_i IN 1 .. BSC_UPDATE.g_num_indicators LOOP
1380 BSC_APPS.Add_Value_Big_In_Cond(1, BSC_UPDATE.g_indicators(h_i));
1381 END LOOP;
1382 h_sql := h_sql || ' AND (' || h_where_indics || ')';
1383 h_sql_kpi := h_sql_kpi || ' AND (' || h_where_indics || ')';
1384 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1385 BSC_UPDATE_UTIL.Execute_Immediate(h_sql_kpi);
1386 ELSE
1387 UPDATE bsc_kpis_b
1388 SET prototype_flag = 0, last_update_date = SYSDATE
1389 WHERE prototype_flag IN (6, 7);
1390
1391 -- Color By KPI: Mark KPIs for color re-calculation
1392 UPDATE bsc_kpi_analysis_measures_b
1393 SET prototype_flag = 0
1394 WHERE prototype_flag = 7;
1395 END IF;
1396
1397 RETURN TRUE;
1398 EXCEPTION
1399 WHEN OTHERS THEN
1400 BSC_MESSAGE.Add(x_message => SQLERRM,
1401 x_source => 'BSC_UPDATE_INC.Reset_Flag_Indicators');
1402 RETURN FALSE;
1403 END Reset_Flag_Indicators;
1404
1405
1406 --LOCKING: new procedure
1407 /*===========================================================================+
1408 | PROCEDURE Purge_AW_Indicator_AT
1409 +============================================================================*/
1410 PROCEDURE Purge_AW_Indicator_AT (
1411 x_indicator IN NUMBER
1412 ) IS
1413 PRAGMA AUTONOMOUS_TRANSACTION;
1414 BEGIN
1415 bsc_aw_load.purge_kpi(
1416 p_kpi => x_indicator,
1417 p_options => 'DEBUG LOG'
1418 );
1419 commit; -- autonomous transactions need to commit
1420 END Purge_AW_Indicator_AT;
1421
1422 END BSC_UPDATE_INC;
[Home] [Help]