1 PACKAGE BODY BSC_UPDATE AS
2 /* $Header: BSCDUPDB.pls 120.20 2007/12/07 10:03:19 phattarg ship $ */
3
4 --
5 -- Package constants
6 --
7 g_process_id NUMBER;
8 g_process_name VARCHAR2(1);
9
10 -- Formats
11 c_fto_long_date_time CONSTANT VARCHAR2(30) := 'Month DD, YYYY HH24:MI:SS';
12
13
14 /*===========================================================================+
15 | FUNCTION Can_Load_Dim_Table
16 +============================================================================*/
17 FUNCTION Can_Load_Dim_Table(
18 x_dim_table IN VARCHAR2,
19 x_loaded_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
20 x_num_loaded_tables IN NUMBER,
21 x_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
22 x_num_dim_tables IN NUMBER
23 ) RETURN BOOLEAN IS
24
25 TYPE t_cursor IS REF CURSOR;
26
27 CURSOR c_parent_tables(p_dim_table VARCHAR2) is
28 SELECT dp.level_table_name
29 FROM bsc_sys_dim_levels_b d, bsc_sys_dim_levels_b dp, bsc_sys_dim_level_rels r
30 WHERE d.dim_level_id = r.dim_level_id AND
31 r.parent_dim_level_id = dp.dim_level_id AND
32 DECODE(r.relation_type, 2, r.relation_col, d.level_table_name) = p_dim_table;
33
34 h_parent_table bsc_sys_dim_levels_b.level_table_name%TYPE;
35 h_ret BOOLEAN;
36
37 BEGIN
38 h_ret := TRUE;
39
40 OPEN c_parent_tables(x_dim_table);
41 FETCH c_parent_tables INTO h_parent_table;
42 WHILE c_parent_tables%FOUND LOOP
43 IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_parent_table,
44 x_dim_tables,
45 x_num_dim_tables) THEN
46
47 -- The parent table was or is going to be loaded in this process
48 -- So, we need to check if this parent table is already loaded or not
49 -- If it is not already loaded the dimension table cannot be
50 -- loaded right now.
51
52 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_parent_table,
53 x_loaded_tables,
54 x_num_loaded_tables) THEN
55 h_ret := FALSE;
56 EXIT;
57 END IF;
58 END IF;
59
60 FETCH c_parent_tables INTO h_parent_table;
61 END LOOP;
62
63 CLOSE c_parent_tables;
64
65 RETURN h_ret;
66
67 EXCEPTION
68 WHEN OTHERS THEN
69 BSC_MESSAGE.Add(x_message => SQLERRM,
70 x_source => 'BSC_UPDATE.Can_Load_Dim_Table');
71 RETURN NULL;
72
73 END Can_Load_Dim_Table;
74
75
76 /*===========================================================================+
77 | FUNCTION Can_Calculate_Sys_Table
78 +============================================================================*/
79 FUNCTION Can_Calculate_Sys_Table(
80 x_system_table IN VARCHAR2,
81 x_calculated_sys_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
82 x_num_calculated_sys_tables IN NUMBER,
83 x_system_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
84 x_num_system_tables IN NUMBER
85 ) RETURN BOOLEAN IS
86
87 TYPE t_cursor IS REF CURSOR;
88
89 /*
90 c_origin_tables t_cursor; -- x_system_table
91 c_origin_tables_sql VARCHAR2(2000) := 'SELECT source_table_name'||
92 ' FROM bsc_db_tables_rels'||
93 ' WHERE table_name = :1';
94 */
95 CURSOR c_origin_tables (p_table_name VARCHAR2) IS
96 SELECT source_table_name
97 FROM bsc_db_tables_rels
98 WHERE table_name = p_table_name;
99
100 h_origin_table bsc_db_tables_rels.source_table_name%TYPE;
101 h_ret BOOLEAN;
102
103 BEGIN
104 h_ret := TRUE;
105
106 --OPEN c_origin_tables FOR c_origin_tables_sql USING x_system_table;
107 OPEN c_origin_tables(x_system_table);
108 FETCH c_origin_tables INTO h_origin_table;
109 WHILE c_origin_tables%FOUND LOOP
110 IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_origin_table,
111 x_system_tables,
112 x_num_system_tables) THEN
113
114 -- The origin table was or is going to be calculated in this process
115 -- So, we need to check if this origin table is already calculated or not
116 -- If it is not already calculated the the system table cannot be
117 -- calculated right now.
118
119 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_origin_table,
120 x_calculated_sys_tables,
121 x_num_calculated_sys_tables) THEN
122 h_ret := FALSE;
123 EXIT;
124 END IF;
125 END IF;
126
127 FETCH c_origin_tables INTO h_origin_table;
128 END LOOP;
129
130 CLOSE c_origin_tables;
131
132 RETURN h_ret;
133
134 EXCEPTION
135 WHEN OTHERS THEN
136 BSC_MESSAGE.Add(x_message => SQLERRM,
137 x_source => 'BSC_UPDATE.Can_Calculate_Sys_Table');
138 RETURN NULL;
139
140 END Can_Calculate_Sys_Table;
141
142
143 /*===========================================================================+
144 | FUNCTION Configure_Periodicity_Calc
145 +============================================================================*/
146 FUNCTION Configure_Periodicity_Calc(
147 p_base_table IN VARCHAR2,
148 x_error_message OUT NOCOPY VARCHAR2
149 ) RETURN BOOLEAN IS
150
151 e_unexpected_error EXCEPTION;
152
153 c_calculation_type NUMBER;
154
155 h_base_table BSC_UPDATE_UTIL.t_array_of_varchar2;
156 h_base_table_periodicity NUMBER;
157
158 h_system_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
159 h_num_system_tables NUMBER;
160
161 h_lst_where VARCHAR2(32700);
162 h_i NUMBER;
163
164 h_sql VARCHAR2(32700);
165 TYPE t_cursor IS REF CURSOR;
166 h_cursor t_cursor;
167
168 h_periodicity_id NUMBER;
169
170 BEGIN
171 c_calculation_type := 6;
172 h_num_system_tables := 0;
173
174 -- Delete current configuration
175 DELETE FROM bsc_db_calculations
176 WHERE table_name = p_base_table AND calculation_type = c_calculation_type;
177
178 -- Get base table periodicity
179 h_base_table_periodicity := BSC_UPDATE_UTIL.Get_Table_Periodicity(p_base_table);
180 IF h_base_table_periodicity IS NULL THEN
181 RAISE e_unexpected_error;
182 END IF;
183
184 -- Initialize the array h_system_tables with the system tables that are affected by the base table
185 h_base_table(1) := p_base_table;
186
187 IF NOT Insert_Affected_Tables(h_base_table, 1, h_system_tables, h_num_system_tables) THEN
188 RAISE e_unexpected_error;
189 END IF;
190
191 -- Get all the periodicities required in the system tables affected by the base table
192
193 IF h_num_system_tables > 0 THEN
194 h_lst_where := BSC_APPS.Get_New_Big_In_Cond_Varchar2NU(1, 'table_name');
195 FOR h_i IN 1 .. h_num_system_tables LOOP
196 BSC_APPS.Add_Value_Big_In_Cond(1, h_system_tables(h_i));
197 END LOOP;
198
199 h_sql := 'SELECT DISTINCT periodicity_id '||
200 'FROM bsc_db_tables '||
201 'WHERE '||h_lst_where;
202
203 OPEN h_cursor FOR h_sql;
204 FETCH h_cursor INTO h_periodicity_id;
205 WHILE h_cursor%FOUND LOOP
206 IF h_periodicity_id <> h_base_table_periodicity THEN
207 INSERT INTO bsc_db_calculations (table_name, calculation_type, parameter1)
208 VALUES (p_base_table, c_calculation_type, h_periodicity_id);
209 END IF;
210
211 FETCH h_cursor INTO h_periodicity_id;
212 END LOOP;
213 CLOSE h_cursor;
214
215 END IF;
216
217 COMMIT;
218
219 RETURN TRUE;
220
221 EXCEPTION
222 WHEN e_unexpected_error THEN
223 x_error_message := BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR');
224 WHEN OTHERS THEN
225 x_error_message := SQLERRM;
226 RETURN FALSE;
227
228 END Configure_Periodicity_Calc;
229
230
231 /*===========================================================================+
232 | PROCEDURE Configure_Periodicity_Calc_VB
233 +============================================================================*/
234 PROCEDURE Configure_Periodicity_Calc_VB(
235 p_base_table IN VARCHAR2
236 ) IS
237
238 e_error EXCEPTION;
239 l_error_message VARCHAR2(2000);
240
241 BEGIN
242
243 IF NOT Configure_Periodicity_Calc(p_base_table, l_error_message) THEN
244 RAISE e_error;
245 END IF;
246
247 EXCEPTION
248 WHEN e_error THEN
249 BSC_MESSAGE.flush;
250 BSC_MESSAGE.Add(x_message => l_error_message,
251 x_source => 'BSC_UPDATE.Configure_Periodicity_Calc_VB',
252 x_mode => 'I');
253 COMMIT;
254
255 WHEN OTHERS THEN
256 BSC_MESSAGE.Add(x_message => SQLERRM,
257 x_source => 'BSC_UPDATE.Configure_Periodicity_Calc_VB',
258 x_mode => 'I');
259 COMMIT;
260
261 END Configure_Periodicity_Calc_VB;
262
263
264 /*===========================================================================+
265 | FUNCTION Configure_Profit_Calc
266 +============================================================================*/
267 FUNCTION Configure_Profit_Calc(
268 x_error_message OUT NOCOPY VARCHAR2
269 ) RETURN BOOLEAN IS
270
271 h_column_type_p VARCHAR2(1);
272 h_base_table VARCHAR2(30);
273 h_pk_level_subaccount VARCHAR2(50);
274 h_calculation_type NUMBER;
275
276 CURSOR h_cursor (p_table_type NUMBER, p_column_type VARCHAR2, p_indic_type NUMBER,
277 p_config_type NUMBER, p_dim_level_index NUMBER) IS
278 SELECT table_name, column_name
279 FROM bsc_db_tables_cols
280 WHERE table_name IN (
281 SELECT table_name
282 FROM bsc_db_tables_rels
283 WHERE source_table_name IN (
284 SELECT table_name
285 FROM bsc_db_tables
286 WHERE table_type = p_table_type
287 )
288 ) AND
289 column_type = p_column_type AND
290 column_name IN (
291 SELECT level_pk_col
292 FROM bsc_kpi_dim_levels_b
293 WHERE indicator IN (
294 SELECT indicator
295 FROM bsc_kpis_b
296 WHERE indicator_type = p_indic_type AND config_type = p_config_type
297 ) AND
298 dim_level_index = p_dim_level_index
299 );
300
301 BEGIN
302
303 h_column_type_p := 'P';
304 h_calculation_type := 1;
305
306 -- Fix bug#3796202,we need to delete all the profit calculations from bsc_db_calculations
307 DELETE FROM bsc_db_calculations WHERE calculation_type = h_calculation_type;
308
309 -- Next query find all the base tables using any sub-account dimension.
310 -- It looks in all the PL indicators to know what are the different sub-account
311 -- dimensions.
312 OPEN h_cursor(0, h_column_type_p, 1, 3, 2);
313 LOOP
314 FETCH h_cursor INTO h_base_table, h_pk_level_subaccount;
315 EXIT WHEN h_cursor%NOTFOUND;
316
317 INSERT INTO bsc_db_calculations (table_name, calculation_type, parameter1)
318 VALUES(h_base_table, h_calculation_type, h_pk_level_subaccount);
319 END LOOP;
320 CLOSE h_cursor;
321
322 COMMIT;
323
324 RETURN TRUE;
325
326 EXCEPTION
327 WHEN OTHERS THEN
328 x_error_message := SQLERRM;
329 RETURN FALSE;
330
331 END Configure_Profit_Calc;
332
333
334 /*===========================================================================+
335 | PROCEDURE Configure_Profit_Calc_VB
336 +============================================================================*/
337 PROCEDURE Configure_Profit_Calc_VB IS
338 e_error EXCEPTION;
339
340 l_error_message VARCHAR2(2000);
341
342 BEGIN
343
344 IF NOT Configure_Profit_Calc(l_error_message) THEN
345 RAISE e_error;
346 END IF;
347
348 EXCEPTION
349 WHEN e_error THEN
350 BSC_MESSAGE.flush;
351 BSC_MESSAGE.Add(x_message => l_error_message,
352 x_source => 'BSC_UPDATE.Configure_Profit_Calc_VB',
353 x_mode => 'I');
354 COMMIT;
355
356 WHEN OTHERS THEN
357 BSC_MESSAGE.Add(x_message => SQLERRM,
358 x_source => 'BSC_UPDATE.Configure_Profit_Calc_VB',
359 x_mode => 'I');
360 COMMIT;
361
362 END Configure_Profit_Calc_VB;
363
364
365 /*===========================================================================+
366 | PROCEDURE Execute_Update_Process
367 +============================================================================*/
368 PROCEDURE Execute_Update_Process (
369 x_process_id IN NUMBER,
370 x_process_name IN VARCHAR2,
371 x_parameter_1 IN VARCHAR2
372 ) IS
373
374 e_update_error EXCEPTION;
375 e_no_pending_process EXCEPTION;
376 e_exists_prototype_indicators EXCEPTION;
377 e_tmp_tbl_create_error EXCEPTION; --added for bug 3899523
378 --LOCKING
379 e_could_not_get_lock EXCEPTION;
380
381 h_log_file_name VARCHAR2(200);
382
383 h_b BOOLEAN;
384 h_i NUMBER;
385
386 h_input_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
387 h_num_input_tables NUMBER;
388
389 h_indicators BSC_UPDATE_UTIL.t_array_of_number;
390 h_num_indicators NUMBER;
391
392 h_calendars BSC_UPDATE_UTIL.t_array_of_number;
393 h_num_calendars NUMBER;
394
395 h_edw_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
396 h_num_edw_dim_tables NUMBER;
397
398 TYPE t_cursor IS REF CURSOR;
399 h_cursor t_cursor;
400 h_sql VARCHAR2(32000);
401
402 h_indicator VARCHAR2(30);
403
404 CURSOR c_indicators (p_process_id NUMBER) IS
405 SELECT input_table_name
406 FROM bsc_db_loader_control
407 WHERE process_id = p_process_id;
408
409 h_error_message VARCHAR2(2000);
410
411 -- Fix bug#3923207: import dbi plans into bsc benchmarks
412 e_import_dbi_plans EXCEPTION;
413
414 -- TRACE
415 h_stat_name VARCHAR2(100);
416 h_stat_value NUMBER;
417
418 h_load_type_into_aw BOOLEAN;
419
420 BEGIN
421 h_num_input_tables := 0;
422 h_num_indicators := 0;
423 h_num_calendars := 0;
424 h_num_edw_dim_tables := 0;
425
426 -- Initializes global variables
427
428 -- Initialize BSC/APPS global variables
429 BSC_APPS.Init_Bsc_Apps;
430
431 -- Initializes the error message stack
432 g_debug_flag := 'NO';
433 BSC_MESSAGE.Init(g_debug_flag);
434
435 -- Initialize the temporal table used for big 'in' conditions
436 BSC_APPS.Init_Big_In_Cond_Table;
437
438 -- Check system lock
439 -- This is done in two places:
440 -- 1. At the beginning of the VB loader.
441 -- 2. In function Run_Concurrent_Loader_Apps which is called when user launch the process
442 -- from apps forms.
443 -- So at this point the system is locked. WE DONT NEED TO LOCK THE SYSTEM HERE.
444
445 -- Initializes g_session_id, g_user_id and g_schema_name
446 IF NOT Init_Env_Values THEN
447 RAISE e_update_error;
448 END IF;
449
450 /*
451 -- TRACE ----------------------------------------------------------------
452 -- Set sql trace
453 execute immediate 'alter session set MAX_DUMP_FILE_SIZE=UNLIMITED';
454 execute immediate 'alter session set tracefile_identifier=''BSCLOADER''';
455 execute immediate 'alter session set sql_trace=true';
456 --execute immediate 'alter session set events= ''10046 trace name context forever, level 12''';
457 -- ----------------------------------------------------------------------------------
458 */
459
460 h_sql := 'alter session set hash_area_size=50000000';
461 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
462
463 h_sql := 'alter session set sort_area_size=50000000';
464 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
465
466 IF BSC_UPDATE_UTIL.is_parallel THEN
467 COMMIT;
468 h_sql := 'alter session enable parallel dml';
469 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
470 COMMIT;
471 END IF;
472
473 -- Get the pending process id
474 g_process_id := Get_Process_Id(x_process_id, x_process_name);
475 g_process_name := x_process_name;
476
477 IF g_process_id IS NULL THEN
478 RAISE e_update_error;
479 END IF;
480
481 IF g_process_id = -1 THEN
482 RAISE e_no_pending_process;
483 END IF;
484
485 -- Initiliaze log file
486 IF NOT BSC_APPS.APPS_ENV THEN
487 h_log_file_name := g_schema_name||g_process_id||'.log';
488
489 IF NOT BSC_UPDATE_LOG.Init_Log_File(h_log_file_name) THEN
490 RAISE e_update_error;
491 END IF;
492 END IF;
493
494 -- Write process_id to log file
495 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'PROCESS_ID')||
496 BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
497 ' '||TO_CHAR(g_process_id), BSC_UPDATE_LOG.LOG);
498
499 BSC_UPDATE_LOG.Write_Line_Log(USERENV('SESSIONID'), BSC_UPDATE_LOG.LOG);
500
501 -- Update the status of the pending process to running
502 IF NOT Set_PStatus_Running() THEN
503 RAISE e_update_error;
504 END IF;
505 COMMIT;
506
507 -- BSC-BIS-DIMENSIONS Note:
508 -- We are removing this validation. Loader process can load data for production indicators
509 -- even if there are indicators in prototype. When loader calculates summary tables
510 -- it will check that the indicator is in production.
511 -- IF x_process_name <> PC_LOAD_DIMENSIONS THEN
512 -- h_b := Exists_Prototype_Indicators();
513 -- IF h_b IS NULL THEN
514 -- RAISE e_update_error;
515 -- ELSIF h_b THEN
516 -- RAISE e_exists_prototype_indicators;
517 -- END IF;
518 -- END IF;
519
520
521 -- EDW Note: We need to refresh all EDW dimensions involved in the input tables
522 -- before check incremental changes.
523 -- BSC-MV Note: This code is not used. I will comment it out
524 -- IF x_process_name = PC_LOADER_PROCESS THEN
525 -- IF NOT Get_EDW_Dims_In_Input_Tables(h_edw_dim_tables, h_num_edw_dim_tables) THEN
526 -- RAISE e_update_error;
527 -- END IF;
528 --
529 -- IF h_num_edw_dim_tables > 0 THEN
530 -- IF NOT BSC_UPDATE_DIM.Refresh_EDW_Dimensions(h_edw_dim_tables, h_num_edw_dim_tables) THEN
531 -- RAISE e_update_error;
532 -- END IF;
533 -- END IF;
534 -- END IF;
535
536
537 -- BSC-BIS-DIMENSIONS: Starting from this implementation the parameter
538 -- x_parameter_1 may contain the list of indicators that we want to process.
539 -- If this parameter is given, Loader only calculate summary tables for those indicators.
540 g_num_indicators := 0;
541 g_kpi_mode := FALSE;
542 IF x_process_name = PC_LOADER_PROCESS OR x_process_name = PC_LOAD_DIMENSIONS THEN
543 -- Decompose the list in x_parameter_1 into the array g_indicators
544 IF x_parameter_1 IS NOT NULL THEN
545 g_num_indicators := BSC_UPDATE_UTIL.Decompose_Numeric_List(x_parameter_1,
546 g_indicators,
547 ',');
548 g_kpi_mode := TRUE;
549 END IF;
550 END IF;
551
552 -- create temp table to hold CODE COLUMN TYPE
553 --LOCKING: Note I review code and the table created in BSC_OLAP_MAIN.create_tmp_col_type_table
554 -- is created with the name BSC_TMP_COL_TYPE_<session_id>. So no problem with many sessions
555 -- dropping and creting this table
556 --Bug 3899523
557 --check if the temp table for col datatype has already been created
558 --if false drop the table and create it again
559 if(BSC_OLAP_MAIN.b_table_col_type_created=false)then
560 BSC_OLAP_MAIN.drop_tmp_col_type_table;
561 if(BSC_OLAP_MAIN.create_tmp_col_type_table(h_error_message)) then
562 BSC_OLAP_MAIN.b_table_col_type_created := true;
563 else
564 --Raise exception;
565 RAISE e_tmp_tbl_create_error;
566 end if;
567 end if;
568
569 -- Fix bug#3923207: Load dbi plans into bsc benchmarks whenever Loader is called to load data
570 IF x_process_name = PC_LOADER_PROCESS THEN
571 --LOCKING: Get the locks needed to import benchmarks
572 IF NOT BSC_UPDATE_LOCK.Lock_Import_Dbi_Plans THEN
573 RAISE e_could_not_get_lock;
574 END IF;
575
576 -- LOCKING: Call the autonomous transaction function
577 IF NOT BSC_UPDATE_DIM.Import_Dbi_Plans_AT(h_error_message) THEN
578 RAISE e_import_dbi_plans;
579 END IF;
580
581 -- AW_INTEGRATION: Load TYPE dimension into AW
582 -- LOCKING: encapsulate this code into an AT procedure
583 -- Fix bug#4491629: If loader is running 'By Objective' we do not need to load type into AW
584 -- if none of the objectives are implemented in AW
585 h_load_type_into_aw := FALSE;
586 IF g_kpi_mode THEN
587 FOR h_i IN 1..g_num_indicators LOOP
588 IF BSC_UPDATE_UTIL.Get_Kpi_Impl_Type(g_indicators(h_i)) = 2 THEN
589 h_load_type_into_aw := TRUE;
590 EXIT;
591 END IF;
592 END LOOP;
593 ELSE
594 -- running by input tables
595 IF BSC_UPDATE_UTIL.Exists_AW_Kpi THEN
596 h_load_type_into_aw := TRUE;
597 END IF;
598 END IF;
599 IF h_load_type_into_aw THEN
600 BSC_UPDATE_DIM.Load_Type_Into_AW_AT;
601 END IF;
602
603 --LOCKING: commit to release the locks
604 COMMIT;
605 END IF;
606
607 -- Load dimensions: We run this process here before incremental changes.
608 -- So, the indicators affected in this process will be refreshed
609 -- immediately in incremental changes.
610 IF x_process_name = PC_LOAD_DIMENSIONS THEN
611 -- Process input tables in BSC_LOADER_CONTROL table
612 -- Initialize the array h_input_tables with the input tables
613 -- of the current process whose status is pending
614
615 -- AW_INTEGRATION: Create temporary tables needed for AW dimension processing
616 --LOCKING: Lock the temporal tables for dimensions
617 IF NOT BSC_UPDATE_LOCK.Lock_Temp_Tables('DIMENSION') THEN
618 RAISE e_could_not_get_lock;
619 END IF;
620
621 -- LOCKING: call the autonomous trnasaction function
622 IF NOT BSC_UPDATE_DIM.Create_AW_Dim_Temp_Tables_AT THEN
623 RAISE e_update_error;
624 END IF;
625
626 --LOCKING: commit to release locks
627 COMMIT;
628
629 IF NOT Get_Process_Input_Tables(h_input_tables, h_num_input_tables, LC_PENDING_STATUS) THEN
630 RAISE e_update_error;
631 END IF;
632
633 -- Load input tables from database sources
634 IF h_num_input_tables > 0 THEN
635 IF NOT Import_ITables_From_DBSrc(h_input_tables, h_num_input_tables) THEN
636 RAISE e_update_error;
637 END IF;
638 END IF;
639
640 IF h_num_input_tables > 0 THEN
641 IF NOT Load_Dim_Input_Tables(h_input_tables, h_num_input_tables) THEN
642 RAISE e_update_error;
643 END IF;
644 END IF;
645
646 -- Bug#3322259 Need to print the status of the input tables processed here.
647 -- Write the result and invalid codes in the log
648 IF NOT Write_Result_Log THEN
649 NULL;
650 END IF;
651 END IF;
652
653 -- Incremental functionality
654 --LOCKING: Lock all the indicators that are going to be affected in the
655 -- incremental logic. This is in cascade mode and in READ mode.
656 -- This is to prevent Metadata Optimizer and Designer to modify those indicators
657 -- during this process
658 IF NOT BSC_UPDATE_LOCK.Lock_Incremental_Indicators THEN
659 RAISE e_could_not_get_lock;
660 END IF;
661
662 --LOCKING: call the autonomous transaction function
663 IF NOT BSC_UPDATE_INC.Do_Incremental_AT() THEN
664 RAISE e_update_error;
665 END IF;
666
667 --LOCKING: commit to release the locks
668 COMMIT;
669
670 -- Run the process
671 IF x_process_name = PC_LOADER_PROCESS THEN
672 -- Process input tables in BSC_LOADER_CONTROL table
673 -- Initialize the array h_input_tables with the input tables
674 -- of the current process whose status is pending
675 IF NOT Get_Process_Input_Tables(h_input_tables, h_num_input_tables, LC_PENDING_STATUS) THEN
676 RAISE e_update_error;
677 END IF;
678
679 --LOCKING: Lock all the indicators that are going to be affected.
680 -- This is in cascade mode and in READ mode.
681 -- This is to prevent Metadata Optimizer and Designer to modify those indicators
682 -- during this process.
683
684 IF NOT BSC_UPDATE_LOCK.Lock_Indicators(h_input_tables, h_num_input_tables) THEN
685 RAISE e_could_not_get_lock;
686 END IF;
687
688 --LOCKING: Review no commit between this point and the commit to release the locks
689 IF h_num_input_tables > 0 THEN
690 -- Load input tables from database sources
691 -- LOCKING: Call the autonomous transaction function
692 IF NOT Import_ITables_From_DBSrc_AT(h_input_tables, h_num_input_tables) THEN
693 RAISE e_update_error;
694 END IF;
695
696 -- Get again the input tables because some of them could be now in error status after importing from others sources
697 IF NOT Get_Process_Input_Tables(h_input_tables, h_num_input_tables, LC_PENDING_STATUS) THEN
698 RAISE e_update_error;
699 END IF;
700 END IF;
701
702 IF h_num_input_tables > 0 THEN
703 --LOCKING: Call the autonomous transaction function
704 IF NOT BSC_UPDATE.Process_Input_Tables_AT(h_input_tables, h_num_input_tables, 0) THEN
705 RAISE e_update_error;
706 END IF;
707 END IF;
708
709 -- Bug#3322259 Need to print the status of the input tables processed here.
710 -- Write the result, invalid codes and new input tables periods in the log
711 IF NOT Write_Result_Log THEN
712 NULL;
713 END IF;
714
715 --LOCKING: commit to release locks
716 COMMIT;
717
718 ELSIF x_process_name = PC_YEAR_CHANGE_PROCESS THEN
719 -- Run the year change process
720
721 -- Decompose the list in x_parameter_1 into the array h_calendars
722 h_num_calendars := BSC_UPDATE_UTIL.Decompose_Numeric_List(x_parameter_1,
723 h_calendars,
724 ',');
725
726 -- LOCKING: Lock the indicators in cascade mode and in read mode that are using
727 -- any of the calendars. This is to prevent Metadata Optimizer to run on those
728 -- indicators or Designer to midify those indicators, its measures and dimensions
729 IF NOT BSC_UPDATE_LOCK.Lock_Indicators_by_Calendar(h_calendars, h_num_calendars) THEN
730 RAISE e_could_not_get_lock;
731 END IF;
732
733 --LOCKING: Review no commit between this point and the commit to release the locks
734
735 -- Change fiscal year by calendar
736 FOR h_i IN 1..h_num_calendars LOOP
737 -- LOCKING: call the autonomous transaction function
738 IF NOT Execute_Year_Change_Process_AT(h_calendars(h_i)) THEN
739 RAISE e_update_error;
740 END IF;
741 END LOOP;
742
743 --LOCKING: commit to release the locks
744 COMMIT;
745
746 ELSIF x_process_name = PC_DELETE_KPI_DATA_PROCESS THEN
747 -- Run delete kpis data process
748
749 h_num_indicators := 0;
750
751 /*
752 h_sql := 'SELECT input_table_name'||
753 ' FROM bsc_db_loader_control'||
754 ' WHERE process_id = :1';
755 OPEN h_cursor FOR h_sql USING g_process_id;
756 */
757 OPEN c_indicators(g_process_id);
758 LOOP
759 FETCH c_indicators INTO h_indicator;
760 EXIT WHEN c_indicators%NOTFOUND;
761
762 h_num_indicators := h_num_indicators + 1;
763 h_indicators(h_num_indicators) := TO_NUMBER(h_indicator);
764 END LOOP;
765 CLOSE c_indicators;
766
767 -- LOCKING: lock all the affected indicators in casscade mode and in read mode to prevent
768 -- metadata optimizer and designers to run on those indicators during this process
769 IF NOT BSC_UPDATE_LOCK.Lock_Indicators_To_Delete(h_indicators, h_num_indicators) THEN
770 RAISE e_could_not_get_lock;
771 END IF;
772
773 IF g_keep_input_table_data IS NULL THEN
774 g_keep_input_table_data := 'N';
775 END IF;
776
777 --LOCKING: Call the autonomous transaction function
778 IF NOT BSC_UPDATE_INC.Purge_Indicators_Data_AT(h_indicators, h_num_indicators, g_keep_input_table_data) THEN
779 RAISE e_update_error;
780 END IF;
781
782 --LOCKING: commit to release locks
783 COMMIT;
784
785 --LOCKING: remove EDW code
786 --ELSIF x_process_name = PC_REFRESH_EDW_DIMENSION THEN
787 -- -- Refresh EDW dimension table
788 -- h_num_edw_dim_tables := 1;
789 -- h_edw_dim_tables(1) := x_parameter_1;
790 -- IF NOT BSC_UPDATE_DIM.Refresh_EDW_Dimensions(h_edw_dim_tables, h_num_edw_dim_tables) THEN
791 -- RAISE e_update_error;
792 -- END IF;
793 END IF;
794
795
796 -- Update the process status to Completed
797 IF NOT Set_PStatus_Finished(PC_COMPLETED_STATUS) THEN
798 RAISE e_update_error;
799 END IF;
800
801 COMMIT;
802
803 -- Write Program completed to log file
804 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
805 BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
806 ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
807 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
808
809 -- Delete records in the temporal table used for big 'in' conditions
810 BSC_APPS.Init_Big_In_Cond_Table;
811
812 --Delete the temp table created for storing CODE Column type
813 --LOCKING: Note I review code and the table created in BSC_OLAP_MAIN.create_tmp_col_type_table
814 -- is created with the name BSC_TMP_COL_TYPE_<session_id>. So no problem with many sessions
815 -- dropping and creting this table
816 --Bug 3899523
817 if(BSC_OLAP_MAIN.b_table_col_type_created) then
818 BSC_OLAP_MAIN.drop_tmp_col_type_table;
819 end if;
820
821 --Enable parallel: need to disable parallel here since I am getting ORA-12838 error when concurrent manager
822 -- is closing the concurrent program.
823 IF BSC_UPDATE_UTIL.is_parallel THEN
824 h_sql := 'alter session disable parallel dml';
825 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
826 commit;
827 END IF;
828
829 COMMIT;
830
831 EXCEPTION
832 WHEN e_no_pending_process THEN
833 -- Delete records in the temporal table used for big 'in' conditions
834 BSC_APPS.Init_Big_In_Cond_Table;
835
836 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_NO_PENDING_PROCESS'),
837 x_source => 'BSC_UPDATE.Execute_Update_Process',
838 x_mode => 'I');
839 COMMIT;
840
841 BSC_UPDATE_LOG.Write_Errors_To_Log;
842
843 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
844 BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
845 ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
846 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
847
848 WHEN e_exists_prototype_indicators THEN
849 -- Delete records in the temporal table used for big 'in' conditions
850 BSC_APPS.Init_Big_In_Cond_Table;
851
852 BSC_MESSAGE.flush;
853 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_METADATA_PEND_CHANGES'),
854 x_source => 'BSC_UPDATE.Execute_Update_Process',
855 x_mode => 'I');
856 COMMIT;
857
858 BSC_UPDATE_LOG.Write_Errors_To_Log;
859
860 IF g_process_id <> -1 THEN
861 h_b := Set_PStatus_Finished(PC_ERROR_STATUS);
862 COMMIT;
863 END IF;
864
865 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
866 BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
867 ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
868 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
869
870 WHEN e_update_error THEN
871 ROLLBACK;
872
873 -- Delete records in the temporal table used for big 'in' conditions
874 BSC_APPS.Init_Big_In_Cond_Table;
875
876 BSC_MESSAGE.flush;
877 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED'),
878 x_source => 'BSC_UPDATE.Execute_Update_Process',
879 x_mode => 'I');
880 COMMIT;
881
882 BSC_UPDATE_LOG.Write_Errors_To_Log;
883
884 IF g_process_id <> -1 THEN
885 h_b := Set_PStatus_Finished(PC_ERROR_STATUS);
886 COMMIT;
887 END IF;
888
889 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
890 BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
891 ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
892 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
893
894 --catching the error in creating table for bug 3899523
895 --added 3899523
896 WHEN e_tmp_tbl_create_error THEN
897 ROLLBACK;
898 -- Delete records in the temporal table used for big 'in' conditions
899 BSC_APPS.Init_Big_In_Cond_Table;
900
901 BSC_MESSAGE.flush;
902 BSC_MESSAGE.Add(x_message => 'BSC_OLAP_MAIN.create_tmp_col_type_table '||h_error_message,
903 x_source => 'BSC_UPDATE.Execute_Update_Process',
904 x_mode => 'I');
905 COMMIT;
906
907 BSC_UPDATE_LOG.Write_Errors_To_Log;
908
909 IF g_process_id <> -1 THEN
910 h_b := Set_PStatus_Finished(PC_ERROR_STATUS);
911 COMMIT;
912 END IF;
913
914 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
915 BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
916 ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
917 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
918
919 --Fix bug#3923207: import dbi plans into bsc benchmarks
920 WHEN e_import_dbi_plans THEN
921 ROLLBACK;
922
923 -- Delete records in the temporal table used for big 'in' conditions
924 BSC_APPS.Init_Big_In_Cond_Table;
925
926 BSC_MESSAGE.flush;
927 BSC_MESSAGE.Add(x_message => 'BSC_UPDATE_DIM.Import_Dbi_Plans: '||h_error_message,
928 x_source => 'BSC_UPDATE.Execute_Update_Process',
929 x_mode => 'I');
930 COMMIT;
931
932 BSC_UPDATE_LOG.Write_Errors_To_Log;
933
934 IF g_process_id <> -1 THEN
935 h_b := Set_PStatus_Finished(PC_ERROR_STATUS);
936 COMMIT;
937 END IF;
938
939 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
940 BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
941 ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
942 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
943
944 --LOCKING
945 WHEN e_could_not_get_lock THEN
946 ROLLBACK;
947
948 -- Delete records in the temporal table used for big 'in' conditions
949 BSC_APPS.Init_Big_In_Cond_Table;
950
951 BSC_MESSAGE.flush;
952
953 -- Error is already in the log file snet to the log file
954 BSC_MESSAGE.Add(x_message => 'Loader could not get the required locks to continue.',
955 x_source => 'BSC_UPDATE.Execute_Update_Process',
956 x_mode => 'I');
957 COMMIT;
958
959 BSC_UPDATE_LOG.Write_Errors_To_Log;
960
961 IF g_process_id <> -1 THEN
962 h_b := Set_PStatus_Finished(PC_ERROR_STATUS);
963 COMMIT;
964 END IF;
965
966 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
967 BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
968 ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
969 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
970
971 WHEN OTHERS THEN
972 ROLLBACK;
973
974 -- Delete records in the temporal table used for big 'in' conditions
975 BSC_APPS.Init_Big_In_Cond_Table;
976
977 BSC_MESSAGE.flush;
978 BSC_MESSAGE.Add(x_message => SQLERRM,
979 x_source => 'BSC_UPDATE.Execute_Update_Process',
980 x_mode => 'I');
981 COMMIT;
982
983 BSC_UPDATE_LOG.Write_Errors_To_Log;
984
985 IF g_process_id <> -1 THEN
986 h_b := Set_PStatus_Finished(PC_ERROR_STATUS);
987 COMMIT;
988 END IF;
989
990 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
991 BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
992 ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
993 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
994
995 END Execute_Update_Process;
996
997
998 /*===========================================================================+
999 | FUNCTION Execute_Year_Change_Process
1000 +============================================================================*/
1001 FUNCTION Execute_Year_Change_Process(
1002 x_calendar_id IN NUMBER
1003 ) RETURN BOOLEAN IS
1004
1005 e_unexpected_error EXCEPTION;
1006 --LOCKING
1007 e_could_not_get_lock EXCEPTION;
1008 e_error_load_rpt_cal EXCEPTION;
1009
1010 h_current_fy NUMBER;
1011
1012 TYPE t_cursor IS REF CURSOR;
1013 h_cursor t_cursor;
1014
1015 -- Fix perf bug#4924515 use bsc_sys_periodicities instead of bsc_sys_periodicities_vl
1016 CURSOR c_tables (p_table_type NUMBER, p_calendar_id NUMBER) IS
1017 SELECT t.table_name, t.periodicity_id, t.current_period, p.yearly_flag
1018 FROM bsc_db_tables t, bsc_sys_periodicities p
1019 WHERE t.periodicity_id = p.periodicity_id AND
1020 t.table_type = p_table_type AND p.calendar_id = p_calendar_id;
1021
1022 CURSOR c_base_tables_mv (p_table_type1 NUMBER, p_gen_type NUMBER,
1023 p_calendar_id NUMBER, p_table_type0 NUMBER) IS
1024 SELECT t.table_name, t.periodicity_id, t.current_period, p.yearly_flag
1025 FROM bsc_db_tables t, bsc_sys_periodicities p
1026 WHERE t.periodicity_id = p.periodicity_id AND
1027 t.table_type = p_table_type1 AND t.generation_type <> p_gen_type AND
1028 p.calendar_id = p_calendar_id AND
1029 t.table_name IN (
1030 SELECT r.table_name
1031 FROM bsc_db_tables_rels r, bsc_db_tables b
1032 WHERE r.source_table_name = b.table_name and b.table_type = p_table_type0
1033 );
1034
1035 -- BSC-BIS-DIMENSION: I am fixing this query. We should return the PT tables, that are the
1036 -- ones that store projections at kpi level in MV architecture
1037 /*
1038 CURSOR c_sum_tables_mv (p_table_type1 NUMBER, p_gen_type NUMBER,
1039 p_calendar_id NUMBER, p_table_type0 NUMBER) IS
1040 SELECT t.table_name, t.periodicity_id, t.current_period, p.yearly_flag
1041 FROM bsc_db_tables t, bsc_sys_periodicities_vl p
1042 WHERE t.periodicity_id = p.periodicity_id AND
1043 t.table_type = p_table_type1 AND t.generation_type <> p_gen_type AND
1044 p.calendar_id = p_calendar_id AND
1045 NOT (t.table_name IN (
1046 SELECT r.table_name
1047 FROM bsc_db_tables_rels r, bsc_db_tables b
1048 WHERE r.source_table_name = b.table_name and b.table_type = p_table_type0
1049 ));
1050 */
1051 --Fix perf bug#4924515 use bsc_sys_periodicities instead of bsc_sys_periodicities_vl
1052 CURSOR c_sum_tables_mv (p_calendar_id NUMBER) IS
1053 SELECT DISTINCT k.projection_data
1054 FROM bsc_kpi_data_tables k, bsc_sys_periodicities p
1055 WHERE k.periodicity_id = p.periodicity_id AND
1056 p.calendar_id = p_calendar_id AND
1057 projection_data IS NOT NULL;
1058
1059
1060 CURSOR c_other_periodicities (p_table_name VARCHAR2, p_calc_type NUMBER) IS
1061 SELECT c.parameter1, p.yearly_flag
1062 FROM bsc_db_calculations c, bsc_sys_periodicities p
1063 WHERE c.table_name = p_table_name AND c.calculation_type = p_calc_type AND
1064 c.parameter1 = p.periodicity_id;
1065
1066 TYPE t_tables IS RECORD (
1067 table_name bsc_db_tables.table_name%TYPE,
1068 periodicity_id bsc_db_tables.periodicity_id%TYPE,
1069 current_period bsc_db_tables.current_period%TYPE,
1070 yearly_flag bsc_sys_periodicities_vl.yearly_flag%TYPE
1071 );
1072
1073 h_table_info t_tables;
1074
1075 h_table_name VARCHAR2(50);
1076
1077 CURSOR c_indicators (p_calendar_id NUMBER) IS
1078 SELECT indicator
1079 FROM bsc_kpis_vl
1080 WHERE calendar_id = p_calendar_id;
1081
1082 h_indicator NUMBER;
1083
1084 h_max_previous NUMBER;
1085 h_max_foryear NUMBER;
1086 h_init_year NUMBER;
1087 h_end_year NUMBER;
1088
1089 h_sql VARCHAR2(2000);
1090 h_b BOOLEAN;
1091 l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
1092 l_num_bind_vars NUMBER;
1093
1094 h_periodicity_id NUMBER;
1095 h_yearly_flag NUMBER;
1096 h_current_period NUMBER;
1097
1098 h_base_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
1099 h_num_base_tables NUMBER;
1100
1101 h_calendar_source VARCHAR2(20);
1102 h_count NUMBER;
1103 h_new_fiscal_year NUMBER;
1104
1105 h_message VARCHAR2(4000);
1106 h_calendar_name VARCHAR2(2000);
1107
1108 --AW_INTEGRATION: New variables
1109 h_aw_indicators BSC_UPDATE_UTIL.t_array_of_number;
1110 h_num_aw_indicators NUMBER;
1111 h_aw_table_name VARCHAR2(30);
1112 h_aw_flag BOOLEAN;
1113 h_i NUMBER;
1114 h_kpi_list dbms_sql.varchar2_table;
1115 h_aw_base_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
1116 h_num_aw_base_tables NUMBER;
1117
1118 --LOCKING: new variables
1119 h_error_message VARCHAR2(2000);
1120
1121 -- ENH_B_TABLES_PERF: new variable
1122 h_proj_tbl_name VARCHAR2(30);
1123
1124 BEGIN
1125
1126 h_current_fy := 0;
1127 h_max_previous := 1;
1128 h_max_foryear := 1;
1129 l_num_bind_vars := 0;
1130 h_num_base_tables := 0;
1131 h_calendar_source := NULL;
1132 h_count := 0;
1133 -- AW_INTEGRATION: init this variable
1134 h_num_aw_indicators := 0;
1135 h_num_aw_base_tables := 0;
1136
1137 --LOCKING: Lock the objects required to change the calendar
1138 IF NOT BSC_UPDATE_LOCK.Lock_Calendar_Change(x_calendar_id) THEN
1139 RAISE e_could_not_get_lock;
1140 END IF;
1141
1142 --LOCKING: review no commit between this point and the commit to release the locks
1143
1144 h_calendar_name := BSC_UPDATE_UTIL.Get_Calendar_Name(x_calendar_id);
1145
1146 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_YEARCHANGE_PROCESS')||' ('||
1147 h_calendar_name||')', BSC_UPDATE_LOG.OUTPUT);
1148
1149 -- Get the current fiscal year
1150 h_current_fy := BSC_UPDATE_UTIL.Get_Calendar_Fiscal_Year(x_calendar_id);
1151 h_new_fiscal_year := h_current_fy + 1;
1152
1153 -- Get calendar source
1154 h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(x_calendar_id);
1155
1156 -- BSC-BIS-DIMENSIONS: If it is a BIS calendar we need to validate that the new fiscal
1157 -- year is already available in BSC
1158 IF h_calendar_source = 'PMF' THEN
1159 SELECT count(year)
1160 INTO h_count
1161 FROM bsc_db_calendar
1162 WHERE calendar_id = x_calendar_id AND year = h_new_fiscal_year;
1163
1164 IF h_count = 0 THEN
1165 -- The new fiscal year is not available
1166 h_message := BSC_UPDATE_UTIL.Get_Message('BSC_DBI_YEAR_NOT_AVAILABLE');
1167 h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'YEAR', TO_CHAR(h_new_fiscal_year));
1168 h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'CALENDAR_NAME', h_calendar_name);
1169 BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
1170 --LOCKING: commit to release locks
1171 COMMIT;
1172 RETURN FALSE;
1173 END IF;
1174 END IF;
1175
1176 -- Fix bug#3636273 We are going to drop indexes to avoid commit in the middle
1177 --LOCKING: we are not going to drop indexes in calendar tables. If we do so, we cannot
1178 --run year change process on different calendars at the same time
1179 --IF h_calendar_source = 'BSC' THEN
1180 -- IF NOT BSC_UPDATE_UTIL.Init_Calendar_Tables(x_calendar_id, 1) THEN
1181 -- RAISE e_unexpected_error;
1182 -- END IF;
1183 --END IF;
1184
1185 -- Write new current fiscal year
1186 -- LOCKING: There is not commit inside this function
1187 IF NOT BSC_UPDATE_UTIL.Set_Calendar_Fiscal_Year(x_calendar_id, (h_current_fy + 1)) THEN
1188 RAISE e_unexpected_error;
1189 END IF;
1190
1191 -- Init calendar tables (BSC Calendars)
1192 -- BSC-BIS-DIMENSIONS: If the calendar is from BIS we do not need to initialize calendar tables.
1193 -- Removing code for EDW. This was never supported.
1194 IF h_calendar_source = 'BSC' THEN
1195 -- Fix bug#3636273 add second parameter.
1196 -- LOCKING: no commit inside this function
1197 IF NOT BSC_UPDATE_UTIL.Init_Calendar_Tables(x_calendar_id) THEN
1198 RAISE e_unexpected_error;
1199 END IF;
1200 END IF;
1201
1202 -- Update indicators
1203 OPEN c_indicators(x_calendar_id);
1204 FETCH c_indicators INTO h_indicator;
1205 WHILE c_indicators%FOUND LOOP
1206
1207 -- AW_INTEGRATION: Add AW indicators to the array x_aw_indicators
1208 IF BSC_UPDATE_UTIL.Get_Kpi_Impl_Type(h_indicator) = 2 THEN
1209 h_num_aw_indicators := h_num_aw_indicators + 1;
1210 h_aw_indicators(h_num_aw_indicators) := h_indicator;
1211 END IF;
1212
1213 -- Update indicator current period
1214 --Fix perf bug#4924515: use bsc_sys_periodicities instead of bsc_sys_periodiicties_vl
1215 UPDATE
1216 bsc_kpi_periodicities
1217 SET
1218 current_period = h_current_fy + 1
1219 WHERE
1220 indicator = h_indicator AND
1221 periodicity_id IN (
1222 SELECT
1223 periodicity_id
1224 FROM
1225 bsc_sys_periodicities
1226 WHERE
1227 calendar_id = x_calendar_id AND
1228 yearly_flag = 1);
1229
1230 UPDATE
1231 bsc_kpi_periodicities
1232 SET
1233 current_period = 1
1234 WHERE
1235 indicator = h_indicator AND
1236 periodicity_id IN (
1237 SELECT
1238 periodicity_id
1239 FROM
1240 bsc_sys_periodicities
1241 WHERE
1242 calendar_id = x_calendar_id AND
1243 yearly_flag = 0);
1244
1245
1246 -- All colors in the panel have to be gray
1247 UPDATE bsc_sys_kpi_colors
1248 SET kpi_color = BSC_UPDATE_COLOR.GRAY,
1249 actual_data = NULL,
1250 budget_data = NULL
1251 WHERE indicator = h_indicator;
1252
1253 UPDATE bsc_sys_objective_colors
1254 SET obj_color = BSC_UPDATE_COLOR.GRAY
1255 WHERE indicator = h_indicator;
1256
1257
1258 -- Update the name of period of indicators in BSC_KPI_DEFAULTS_TL table
1259 IF NOT BSC_UPDATE_UTIL.Update_Kpi_Period_Name(h_indicator) THEN
1260 RAISE e_unexpected_error;
1261 END IF;
1262
1263 -- Update date of indicator
1264 UPDATE
1265 bsc_kpi_defaults_b
1266 SET
1267 last_update_date = SYSDATE
1268 WHERE
1269 indicator = h_indicator;
1270
1271 -- Fix bug#3636273 No commit in the middle
1272 --COMMIT;
1273
1274 FETCH c_indicators INTO h_indicator;
1275 END LOOP;
1276 CLOSE c_indicators;
1277
1278
1279 -- Delete projected data from tables
1280 IF BSC_APPS.bsc_mv THEN
1281 -- BSC-MV Note: Only delete projection from base tables
1282
1283 -- The following are base tables. We need to delete the projection
1284 -- from all the periodicities
1285 OPEN c_base_tables_mv(1, -1, x_calendar_id, 0);
1286 LOOP
1287 FETCH c_base_tables_mv INTO h_table_info;
1288 EXIT WHEN c_base_tables_mv%NOTFOUND;
1289
1290 -- AW_INTEGRATION: If the base table is for AW indicators, then we need to truncate
1291 -- the AW table created for the base table. I will add them to the array
1292 -- h_aw_base_tables to truncate them later. We cannot do DDL here.
1293 -- Also the base table is not added to
1294 -- to array h_base_tables because that array is for base table for MV indicators
1295 h_aw_flag := BSC_UPDATE_UTIL.Is_Table_For_AW_Kpi(h_table_info.table_name);
1296 IF h_aw_flag THEN
1297 -- Base table for AW indicators
1298 h_num_aw_base_tables := h_num_aw_base_tables + 1;
1299 h_aw_base_tables(h_num_aw_base_tables) := h_table_info.table_name;
1300 ELSE
1301 -- Base table for MV indicators
1302 -- Add the base tbale to the array h_base_tables
1303 h_num_base_tables := h_num_base_tables + 1;
1304 h_base_tables(h_num_base_tables) := h_table_info.table_name;
1305 END IF;
1306
1307 -- Delete projection for base periodicity
1308 --AW_INTEGRATION: Base table does not have periodicity_id
1309
1310 --ENH_B_TABLES_PERF: In the new strategy the base table may have a projection table
1311 -- In this case we need to truncate the projection table and we do not need to touch
1312 -- the base table since it contains only actuals.
1313 h_proj_tbl_name := BSC_UPDATE_BASE_V2.Get_Base_Proj_Tbl_Name(h_table_info.table_name);
1314 IF h_proj_tbl_name IS NOT NULL THEN
1315 BSC_UPDATE_UTIL.Truncate_Table_AT(h_proj_tbl_name);
1316 ELSE
1317 IF h_table_info.yearly_flag = 1 THEN
1318 -- Annual periodicity
1319 h_sql := 'DELETE FROM '||h_table_info.table_name||
1320 ' WHERE YEAR > :1 AND TYPE = 0';
1321 IF NOT h_aw_flag THEN
1322 h_sql := h_sql||' AND PERIODICITY_ID = :2';
1323 l_bind_vars_values(1):=h_table_info.current_period;
1324 l_bind_vars_values(2):=h_table_info.periodicity_id;
1325 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
1326 ELSE
1327 l_bind_vars_values(1):=h_table_info.current_period;
1328 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
1329 END IF;
1330 ELSE
1331 -- Other periodicity
1332 h_sql := 'DELETE FROM '||h_table_info.table_name||
1333 ' WHERE YEAR = :1 AND PERIOD > :2 AND TYPE = 0';
1334 IF NOT h_aw_flag THEN
1335 h_sql := h_sql||' AND PERIODICITY_ID = :3';
1336 l_bind_vars_values(1):=h_current_fy;
1337 l_bind_vars_values(2):=h_table_info.current_period;
1338 l_bind_vars_values(3):=h_table_info.periodicity_id;
1339 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,3);
1340 ELSE
1341 l_bind_vars_values(1):=h_current_fy;
1342 l_bind_vars_values(2):=h_table_info.current_period;
1343 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
1344 END IF;
1345 END IF;
1346
1347 -- Delete projection for other periodicities
1348 -- AW_INTEGRATION: Base table does not have other periodicities
1349 IF NOT h_aw_flag THEN
1350 OPEN c_other_periodicities(h_table_info.table_name, 6);
1351 LOOP
1352 FETCH c_other_periodicities INTO h_periodicity_id, h_yearly_flag;
1353 EXIT WHEN c_other_periodicities%NOTFOUND;
1354
1355 IF h_yearly_flag = 1 THEN
1356 -- Annual periodicity
1357 h_sql := 'DELETE FROM '||h_table_info.table_name||
1358 ' WHERE YEAR > :1 AND TYPE = 0 AND PERIODICITY_ID = :2';
1359 l_bind_vars_values(1):=h_current_fy;
1360 l_bind_vars_values(2):=h_periodicity_id;
1361 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
1362 ELSE
1363 -- Get current period of this periodicity based on the current period
1364 -- of the base periodicity of the base table
1365 h_current_period := BSC_UPDATE_UTIL.Get_Period_Other_Periodicity(
1366 h_periodicity_id,
1367 x_calendar_id,
1368 h_yearly_flag,
1369 h_current_fy,
1370 h_table_info.periodicity_id,
1371 h_table_info.current_period
1372 );
1373
1374 -- Other periodicity
1375 h_sql := 'DELETE FROM '||h_table_info.table_name||
1376 ' WHERE YEAR = :1 AND PERIOD > :2 AND TYPE = 0 AND PERIODICITY_ID = :3';
1377 l_bind_vars_values(1):=h_current_fy;
1378 l_bind_vars_values(2):=h_current_period;
1379 l_bind_vars_values(3):=h_periodicity_id;
1380 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,3);
1381 END IF;
1382 END LOOP;
1383 CLOSE c_other_periodicities;
1384 END IF;
1385 END IF;
1386 END LOOP;
1387 CLOSE c_base_tables_mv;
1388 ELSE
1389 l_bind_vars_values.delete;
1390 OPEN c_tables(1, x_calendar_id);
1391 FETCH c_tables INTO h_table_info;
1392 WHILE c_tables%FOUND LOOP
1393 IF BSC_UPDATE_UTIL.Table_Exists(h_table_info.table_name) THEN
1394 --ENH_B_TABLES_PERF: In the new strategy the base table may have a projection table
1395 -- In this case we need to truncate the projection table and we do not need to touch
1396 -- the base table since it contains only actuals.
1397 h_proj_tbl_name := BSC_UPDATE_BASE_V2.Get_Base_Proj_Tbl_Name(h_table_info.table_name);
1398 IF h_proj_tbl_name IS NOT NULL THEN
1399 BSC_UPDATE_UTIL.Truncate_Table_AT(h_proj_tbl_name);
1400 ELSE
1401 IF h_table_info.yearly_flag = 1 THEN
1402 -- Annual periodicity
1403 h_sql := 'DELETE FROM '||h_table_info.table_name||
1404 ' WHERE YEAR > :1 AND TYPE = 0';
1405 l_bind_vars_values(1):=h_table_info.current_period;
1406 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
1407 ELSE
1408 -- Other periodicity
1409 h_sql := 'DELETE FROM '||h_table_info.table_name||
1410 ' WHERE YEAR = :1 AND PERIOD > :2 AND TYPE = 0';
1411 l_bind_vars_values(1):=h_current_fy;
1412 l_bind_vars_values(2):=h_table_info.current_period;
1413 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
1414 END IF;
1415 END IF;
1416 END IF;
1417 FETCH c_tables INTO h_table_info;
1418 END LOOP;
1419 CLOSE c_tables;
1420 END IF;
1421
1422 -- Update the current period of data tables
1423 -- Fix perf bug#4924515 use bsc_sys_periodiicties instead of bsc_sys_periodicities_vl
1424 UPDATE
1425 bsc_db_tables
1426 SET
1427 current_period = h_current_fy + 1,
1428 current_subperiod = 0
1429 WHERE
1430 table_type <> 2 AND
1431 periodicity_id IN (
1432 SELECT
1433 periodicity_id
1434 FROM
1435 bsc_sys_periodicities
1436 WHERE
1437 calendar_id = x_calendar_id AND
1438 yearly_flag = 1);
1439
1440
1441 UPDATE
1442 bsc_db_tables
1443 SET
1444 current_period = 1,
1445 current_subperiod = 0
1446 WHERE
1447 table_type <> 2 AND
1448 periodicity_id IN (
1449 SELECT
1450 periodicity_id
1451 FROM
1452 bsc_sys_periodicities
1453 WHERE
1454 calendar_id = x_calendar_id AND
1455 yearly_flag = 0);
1456
1457 -- Fix bug#3636273 Now calendar and periods in the kpis and tables are ok.
1458 -- We can commit here
1459 --LOCKING: commit to release the locks
1460 COMMIT;
1461
1462 -- Fix bug#3636273 We are going to create indexes here to avoid commit in the middle
1463 --LOCKING: we are not going to drop indexes in calendar tables. If we do so, we cannot
1464 --run year change process on different calendars at the same time
1465 --IF h_calendar_source = 'BSC' THEN
1466 -- IF NOT BSC_UPDATE_UTIL.Init_Calendar_Tables(x_calendar_id, 3) THEN
1467 -- RAISE e_unexpected_error;
1468 -- END IF;
1469 --END IF;
1470
1471 -- Fix bug#3636273 We are going to truncate tables created to store projection
1472 -- at kpi level in MV architecture here to avoid commit in the middle
1473 -- also we refresh all MV affected by the base tables here
1474 -- AW_INTEGRATION: By design there are not PT tables for AW indicators.
1475 -- So no change here. Also remember that the array h_base_tables only
1476 -- has base tables for MV indicators.
1477 IF BSC_APPS.bsc_mv THEN
1478 -- BSC-MV Note: Only delete projection from base tables and tables created
1479 -- to store projection at kpi level
1480
1481 -- The following are summary tables used to store projection.
1482 -- We can just truncate those tables.
1483 l_bind_vars_values.delete;
1484 OPEN c_sum_tables_mv(x_calendar_id);
1485 LOOP
1486 FETCH c_sum_tables_mv INTO h_table_name;
1487 EXIT WHEN c_sum_tables_mv%NOTFOUND;
1488
1489 --LOCKING: Lock the table
1490 IF NOT BSC_UPDATE_LOCK.Lock_Table(h_table_name) THEN
1491 RAISE e_could_not_get_lock;
1492 END IF;
1493
1494 --LOCKING: Call the autonomous transaction function
1495 BSC_UPDATE_UTIL.Truncate_Table_AT(h_table_name);
1496
1497 --LOCKING: commit to release locks
1498 COMMIT;
1499 END LOOP;
1500 CLOSE c_sum_tables_mv;
1501
1502 -- Refresh all MVs in the system affected by base tables
1503 IF NOT BSC_UPDATE.Refresh_System_MVs(h_base_tables, h_num_base_tables) THEN
1504 RAISE e_unexpected_error;
1505 END IF;
1506 END IF;
1507 COMMIT;
1508
1509 -- AW_INTEGRATION: Truncate the AW tables created for the base table
1510 -- bug 5660053 we are not creating AW tables for base tables so we do not need to truncate
1511 /*FOR h_i IN 1..h_num_aw_base_tables LOOP
1512 -- We need to truncate the AW table created for the base table
1513 --LOCKING: Lock the table
1514 IF NOT BSC_UPDATE_LOCK.Lock_Table(h_aw_base_tables(h_i)) THEN
1515 RAISE e_could_not_get_lock;
1516 END IF;
1517
1518 h_aw_table_name := BSC_UPDATE_BASE.Get_Base_AW_Table_Name(h_aw_base_tables(h_i));
1519
1520 --LOCKING: call the autonomous transaction function
1521 BSC_UPDATE_UTIL.Truncate_Table_AT(h_aw_table_name);
1522
1523 --LOCKING: commit to release locks
1524 COMMIT;
1525 END LOOP;*/
1526
1527 -- AW_INTEGRATION: Refresh indicators cubes
1528 FOR h_i IN 1..h_num_aw_indicators LOOP
1529 IF BSC_UPDATE_UTIL.Is_Kpi_In_Production(h_aw_indicators(h_i)) THEN
1530 --LOCKING: Lock the objects required to refresh the AW indicator cubes
1531 IF NOT BSC_UPDATE_LOCK.Lock_Refresh_AW_Indicator(h_aw_indicators(h_i)) THEN
1532 RAISE e_could_not_get_lock;
1533 END IF;
1534
1535 --LOCKING: call the autonomous transaction procedure
1536 BSC_UPDATE_SUM.Refresh_AW_Kpi_AT(h_aw_indicators(h_i));
1537
1538 -- LOCKING: commit to release locks
1539 COMMIT;
1540 END IF;
1541 END LOOP;
1542 COMMIT;
1543
1544 -- Write the update date
1545 --LOCKING: Lock date of update process
1546 IF NOT BSC_UPDATE_LOCK.Lock_Update_Date THEN
1547 RAISE e_could_not_get_lock;
1548 END IF;
1549
1550 IF NOT BSC_UPDATE_UTIL.Write_Init_Variable_Value('UPDATE_DATE', TO_CHAR(SYSDATE, 'DD/MM/YYYY')) THEN
1551 RAISE e_unexpected_error;
1552 END IF;
1553
1554 -- Fix bug#3636273 Move this call here.
1555 -- Update the system time stamp
1556 BSC_UPDATE_UTIL.Update_System_Time_Stamp;
1557
1558 --LOCKING: commit to release locks
1559 COMMIT;
1560
1561 --LOCKING: mode this code here
1562 -- BSC_MV Note: Populate reporting calendar
1563 -- Fix bug#4027813: move load reporting calendar here and pass calendar id
1564 IF BSC_APPS.bsc_mv THEN
1565 -- LOCKING: lock the calendar
1566 IF NOT BSC_UPDATE_LOCK.Lock_Calendar(x_calendar_id) THEN
1567 RAISE e_could_not_get_lock;
1568 END IF;
1569
1570 --LOCKING: call the autonomous transaction
1571 IF NOT BSC_BIA_WRAPPER.Load_Reporting_Calendar_AT(x_calendar_id, h_error_message) THEN
1572 RAISE e_error_load_rpt_cal;
1573 END IF;
1574
1575 --AW_INTEGRATION: call aw api to import calendars into aw world
1576 --LOCKING: call the autonomous transaction
1577 BSC_UPDATE_UTIL.Load_Calendar_Into_AW_AT(x_calendar_id);
1578
1579 --LOCKING: commit to release locks
1580 COMMIT;
1581 END IF;
1582
1583 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_YEARCHANGE_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
1584
1585 RETURN TRUE;
1586
1587 EXCEPTION
1588 WHEN e_unexpected_error THEN
1589 ROLLBACK;
1590 BSC_MESSAGE.Add (x_message => BSC_UPDATE_UTIL.Get_Message('BSC_YEARCHANGE_FAILED'),
1591 x_source => 'BSC_UPDATE.Execute_Year_Change_Process');
1592
1593 RETURN FALSE;
1594
1595 --LOCKING
1596 WHEN e_could_not_get_lock THEN
1597 ROLLBACK;
1598 BSC_MESSAGE.Add (x_message => 'Loader could not get the required locks to continue.',
1599 x_source => 'BSC_UPDATE.Execute_Year_Change_Process');
1600
1601 RETURN FALSE;
1602
1603 --LOCKING
1604 WHEN e_error_load_rpt_cal THEN
1605 ROLLBACK;
1606 BSC_MESSAGE.Add (x_message => 'BSC_BIA_WRAPPER.Load_Reporting_Calendar: '||h_error_message,
1607 x_source => 'BSC_UPDATE.Execute_Year_Change_Process');
1608
1609 RETURN FALSE;
1610
1611 WHEN OTHERS THEN
1612 ROLLBACK;
1613 BSC_MESSAGE.Add (x_message => SQLERRM,
1614 x_source => 'BSC_UPDATE.Execute_Year_Change_Process');
1615
1616 RETURN FALSE;
1617
1618 END Execute_Year_Change_Process;
1619
1620
1621 --LOCKING: new function
1622 /*===========================================================================+
1623 | FUNCTION Execute_Year_Change_Process_AT
1624 +============================================================================*/
1625 FUNCTION Execute_Year_Change_Process_AT(
1626 x_calendar_id IN NUMBER
1627 ) RETURN BOOLEAN IS
1628 PRAGMA AUTONOMOUS_TRANSACTION;
1629 h_b BOOLEAN;
1630 BEGIN
1631 h_b := Execute_Year_Change_Process(x_calendar_id);
1632 commit; -- all autonomous transaction needs to commit
1633 RETURN h_b;
1634 END Execute_Year_Change_Process_AT;
1635
1636
1637 /*===========================================================================+
1638 | FUNCTION Exists_Prototype_Indicators
1639 +============================================================================*/
1640 FUNCTION Exists_Prototype_Indicators RETURN BOOLEAN IS
1641
1642 TYPE t_cursor IS REF CURSOR;
1643
1644 /*
1645 c_prototype t_cursor;
1646 c_prototype_sql VARCHAR2(2000) := 'SELECT COUNT(prototype_flag)'||
1647 ' FROM bsc_kpis_vl'||
1648 ' WHERE prototype_flag IN (1, 2, 3, 4, 5)';
1649 */
1650
1651 h_count NUMBER;
1652 h_res BOOLEAN;
1653
1654 BEGIN
1655
1656 h_res := FALSE;
1657
1658 /*
1659 OPEN c_prototype FOR c_prototype_sql;
1660 FETCH c_prototype INTO h_count;
1661 CLOSE c_prototype;
1662 */
1663 SELECT COUNT(prototype_flag)
1664 INTO h_count
1665 FROM bsc_kpis_vl
1666 WHERE prototype_flag IN (1, 2, 3, 4, 5);
1667
1668 IF h_count > 0 THEN
1669 h_res := TRUE;
1670 END IF;
1671
1672 RETURN h_res;
1673
1674 EXCEPTION
1675 WHEN OTHERS THEN
1676 BSC_MESSAGE.Add(x_message => SQLERRM,
1677 x_source => 'BSC_UPDATE.Exists_Prototype_Indicators');
1678 RETURN NULL;
1679
1680 END Exists_Prototype_Indicators;
1681
1682
1683 /*===========================================================================+
1684 | FUNCTION Flag_Last_Stage_Input_Table
1685 +============================================================================*/
1686 FUNCTION Flag_Last_Stage_Input_Table(
1687 x_input_table IN VARCHAR2
1688 ) RETURN BOOLEAN IS
1689 BEGIN
1690
1691 UPDATE
1692 bsc_db_loader_control
1693 SET
1694 last_stage_flag = 0
1695 WHERE
1696 input_table_name = x_input_table;
1697
1698 UPDATE
1699 bsc_db_loader_control
1700 SET
1701 last_stage_flag = 1
1702 WHERE
1703 input_table_name = x_input_table AND
1704 process_id = g_process_id;
1705
1706 RETURN TRUE;
1707
1708 EXCEPTION
1709 WHEN OTHERS THEN
1710 BSC_MESSAGE.Add(x_message => SQLERRM,
1711 x_source => 'BSC_UPDATE.Flag_Last_Stage_Input_Table');
1712 RETURN FALSE;
1713
1714 END Flag_Last_Stage_Input_Table;
1715
1716
1717 /*===========================================================================+
1718 | FUNCTION Get_Base_Table_Of_Input_Table
1719 +============================================================================*/
1720 FUNCTION Get_Base_Table_Of_Input_Table(
1721 x_input_table IN VARCHAR2,
1722 x_base_table OUT NOCOPY VARCHAR2
1723 ) RETURN BOOLEAN IS
1724
1725 TYPE t_cursor IS REF CURSOR;
1726
1727 /*
1728 c_base_table t_cursor; -- x_input_table, 0
1729 c_base_table_sql VARCHAR2(2000) := 'SELECT table_name'||
1730 ' FROM bsc_db_tables_rels'||
1731 ' WHERE UPPER(source_table_name) = UPPER(:1) AND relation_type = :2';
1732 */
1733
1734 BEGIN
1735 /*
1736 OPEN c_base_table FOR c_base_table_sql USING x_input_table, 0;
1737 FETCH c_base_table INTO x_base_table;
1738 IF c_base_table%NOTFOUND THEN
1739 x_base_table := NULL;
1740 END IF;
1741 CLOSE c_base_table;
1742 */
1743
1744 BEGIN
1745 SELECT table_name
1746 INTO x_base_table
1747 FROM bsc_db_tables_rels
1748 WHERE UPPER(source_table_name) = UPPER(x_input_table) AND relation_type = 0;
1749 EXCEPTION
1750 WHEN NO_DATA_FOUND THEN
1751 x_base_table := NULL;
1752 END;
1753
1754 RETURN TRUE;
1755
1756 EXCEPTION
1757 WHEN OTHERS THEN
1758 BSC_MESSAGE.Add(x_message => SQLERRM,
1759 x_source => 'BSC_UPDATE.Get_Base_Table_Of_Input_Table');
1760 RETURN FALSE;
1761
1762 END Get_Base_Table_Of_Input_Table;
1763
1764
1765 /*===========================================================================+
1766 | FUNCTION Get_EDW_Dims_In_Input_Tables
1767 +============================================================================*/
1768 FUNCTION Get_EDW_Dims_In_Input_Tables (
1769 x_edw_dim_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
1770 x_num_edw_dim_tables IN OUT NOCOPY NUMBER
1771 ) RETURN BOOLEAN IS
1772
1773 TYPE t_cursor IS REF CURSOR;
1774
1775 /*
1776 c_edw_dimensions t_cursor; -- 1, h_column_type_p, g_process_id, LC_PENDING_STATUS
1777 c_edw_dimensions_sql VARCHAR2(2000) := 'SELECT level_table_name'||
1778 ' FROM bsc_sys_dim_levels_b'||
1779 ' WHERE NVL(edw_flag, 0) = :1 AND'||
1780 ' level_pk_col IN (SELECT column_name'||
1781 ' FROM bsc_db_tables_cols c, bsc_db_loader_control p'||
1782 ' WHERE c.table_name = p.input_table_name AND'||
1783 ' c.column_type = :2 AND p.process_id = :3 AND p.status = :4)';
1784 */
1785 CURSOR c_edw_dimensions (p_edw_flag NUMBER, p_column_type VARCHAR2, p_process_id NUMBER, p_status VARCHAR2) IS
1786 SELECT level_table_name
1787 FROM bsc_sys_dim_levels_b
1788 WHERE NVL(edw_flag, 0) = p_edw_flag AND
1789 level_pk_col IN (
1790 SELECT column_name
1791 FROM bsc_db_tables_cols c, bsc_db_loader_control p
1792 WHERE c.table_name = p.input_table_name AND
1793 c.column_type = p_column_type AND p.process_id = p_process_id AND p.status = p_status);
1794
1795 h_column_type_p VARCHAR2(1);
1796
1797 h_dim_table VARCHAR2(30);
1798
1799 BEGIN
1800 h_column_type_p := 'P';
1801 x_num_edw_dim_tables := 0;
1802
1803 --OPEN c_edw_dimensions FOR c_edw_dimensions_sql USING 1, h_column_type_p, g_process_id, LC_PENDING_STATUS;
1804 OPEN c_edw_dimensions(1, h_column_type_p, g_process_id, LC_PENDING_STATUS);
1805 FETCH c_edw_dimensions INTO h_dim_table;
1806 WHILE c_edw_dimensions%FOUND LOOP
1807 x_num_edw_dim_tables := x_num_edw_dim_tables + 1;
1808 x_edw_dim_tables(x_num_edw_dim_tables) := h_dim_table;
1809
1810 FETCH c_edw_dimensions INTO h_dim_table;
1811 END LOOP;
1812 CLOSE c_edw_dimensions;
1813
1814 RETURN TRUE;
1815
1816 EXCEPTION
1817 WHEN OTHERS THEN
1818 BSC_MESSAGE.Add(x_message => SQLERRM,
1819 x_source => 'BSC_UPDATE.Get_EDW_Dims_In_Input_Tables');
1820 RETURN FALSE;
1821 END Get_EDW_Dims_In_Input_Tables;
1822
1823
1824 /*===========================================================================+
1825 | FUNCTION Get_Indicators_To_Color
1826 +============================================================================*/
1827 FUNCTION Get_Indicators_To_Color(
1828 x_base_tables_to_color IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1829 x_num_base_tables_to_color IN NUMBER,
1830 x_color_indicators IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
1831 x_num_color_indicators IN OUT NOCOPY NUMBER
1832 ) RETURN BOOLEAN IS
1833
1834 e_unexpected_error EXCEPTION;
1835
1836 h_system_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
1837 h_num_system_tables NUMBER;
1838 h_i NUMBER;
1839 h_lst_where VARCHAR2(32700);
1840
1841 TYPE t_cursor IS REF CURSOR;
1842 h_cursor t_cursor;
1843
1844 h_sql VARCHAR2(32700);
1845
1846 h_indicator NUMBER;
1847
1848 BEGIN
1849
1850 h_num_system_tables := 0;
1851 h_lst_where := NULL;
1852 h_sql := NULL;
1853
1854 -- If an indicator use a table that was calculated then that indicator
1855 -- will be colored
1856
1857 IF NOT Insert_Affected_Tables(x_base_tables_to_color,
1858 x_num_base_tables_to_color,
1859 h_system_tables,
1860 h_num_system_tables) THEN
1861 RAISE e_unexpected_error;
1862 END IF;
1863
1864 h_lst_where := BSC_APPS.Get_New_Big_In_Cond_Varchar2(1, 'table_name');
1865 FOR h_i IN 1 .. x_num_base_tables_to_color LOOP
1866 BSC_APPS.Add_Value_Big_In_Cond(1, x_base_tables_to_color(h_i));
1867 END LOOP;
1868
1869 FOR h_i IN 1 .. h_num_system_tables LOOP
1870 BSC_APPS.Add_Value_Big_In_Cond(1, h_system_tables(h_i));
1871 END LOOP;
1872
1873 h_sql := 'SELECT DISTINCT indicator '||
1874 'FROM bsc_kpi_data_tables '||
1875 'WHERE '||h_lst_where;
1876
1877 OPEN h_cursor FOR h_sql;
1878 FETCH h_cursor INTO h_indicator;
1879 WHILE h_cursor%FOUND LOOP
1880 x_num_color_indicators := x_num_color_indicators + 1;
1881 x_color_indicators(x_num_color_indicators) := h_indicator;
1882
1883 FETCH h_cursor INTO h_indicator;
1884 END LOOP;
1885 CLOSE h_cursor;
1886
1887 RETURN TRUE;
1888 EXCEPTION
1889 WHEN e_unexpected_error THEN
1890 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_COLORKPILIST_FAILED'),
1891 x_source => 'BSC_UPDATE.Get_Indicators_To_Color');
1892 RETURN FALSE;
1893
1894 WHEN OTHERS THEN
1895 BSC_MESSAGE.Add(x_message => SQLERRM,
1896 x_source => 'BSC_UPDATE.Get_Indicators_To_Color');
1897 RETURN FALSE;
1898
1899 END Get_Indicators_To_Color;
1900
1901
1902 /*===========================================================================+
1903 | FUNCTION Get_Last_Stage_Input_Table
1904 +============================================================================*/
1905 FUNCTION Get_Last_Stage_Input_Table(
1906 x_input_table IN VARCHAR2,
1907 x_last_stage OUT NOCOPY VARCHAR2
1908 ) RETURN BOOLEAN IS
1909
1910 TYPE t_cursor IS REF CURSOR;
1911
1912 /*
1913 c_last_stage t_cursor; -- x_input_table, 1
1914 c_last_stage_sql VARCHAR2(2000) := 'SELECT stage'||
1915 ' FROM bsc_db_loader_control'||
1916 ' WHERE input_table_name = :1 AND last_stage_flag = :2';
1917 */
1918
1919 BEGIN
1920 /*
1921 OPEN c_last_stage FOR c_last_stage_sql USING x_input_table, 1;
1922 FETCH c_last_stage INTO x_last_stage;
1923 IF c_last_stage%NOTFOUND THEN
1924 x_last_stage := '?';
1925 END IF;
1926 CLOSE c_last_stage;
1927 */
1928 BEGIN
1929 SELECT stage
1930 INTO x_last_stage
1931 FROM bsc_db_loader_control
1932 WHERE input_table_name = x_input_table AND last_stage_flag = 1;
1933 EXCEPTION
1934 WHEN NO_DATA_FOUND THEN
1935 x_last_stage := '?';
1936 END;
1937
1938 RETURN TRUE;
1939 EXCEPTION
1940 WHEN OTHERS THEN
1941 BSC_MESSAGE.Add(x_message => SQLERRM,
1942 x_source => 'BSC_UPDATE.Get_Last_Stage_Input_Table');
1943 RETURN FALSE;
1944
1945 END Get_Last_Stage_Input_Table;
1946
1947
1948 /*===========================================================================+
1949 | FUNCTION Get_Process_Id
1950 +============================================================================*/
1951 FUNCTION Get_Process_Id (
1952 x_process_id IN NUMBER,
1953 x_process_name IN VARCHAR2
1954 ) RETURN NUMBER IS
1955
1956 TYPE t_cursor IS REF CURSOR;
1957
1958 /*
1959 c_process_status t_cursor; -- x_process_id
1960 c_process_status_sql VARCHAR2(2000) := 'SELECT status'||
1961 ' FROM bsc_db_process_control'||
1962 ' WHERE process_id = :1';
1963 */
1964
1965 h_status VARCHAR2(1);
1966 h_process_id NUMBER;
1967
1968 BEGIN
1969 /*
1970 OPEN c_process_status FOR c_process_status_sql USING x_process_id;
1971 FETCH c_process_status INTO h_status;
1972 IF c_process_status%NOTFOUND THEN
1973 CLOSE c_process_status;
1974 -- The process does not exist
1975 h_process_id := -1;
1976 RETURN h_process_id;
1977 END IF;
1978 CLOSE c_process_status;
1979 */
1980 BEGIN
1981 SELECT status
1982 INTO h_status
1983 FROM bsc_db_process_control
1984 WHERE process_id = x_process_id;
1985 EXCEPTION
1986 WHEN NO_DATA_FOUND THEN
1987 -- The process does not exist
1988 h_process_id := -1;
1989 END;
1990
1991 IF h_process_id = -1 THEN
1992 -- The process does not exist
1993 RETURN h_process_id;
1994 END IF;
1995
1996 IF h_status = PC_PENDING_STATUS THEN
1997 --The process is pending so this is the porcess to be executed
1998 h_process_id := x_process_id;
1999 RETURN x_process_id;
2000 END IF;
2001
2002 -- Create a new process with same parameters as given one
2003 -- BSC_DB_PROCESS_CONTROL
2004
2005 --LOCKING: get the sequence nextval here and then use it
2006 SELECT BSC_DB_PROCESS_ID_S.NEXTVAL
2007 INTO h_process_id
2008 FROM DUAL;
2009
2010 INSERT INTO BSC_DB_PROCESS_CONTROL (
2011 PROCESS_ID,
2012 PROCESS_NAME,
2013 CREATED_BY,
2014 CREATION_DATE,
2015 LAST_UPDATED_BY,
2016 LAST_UPDATE_DATE,
2017 LAST_UPDATE_LOGIN,
2018 STATUS,
2019 DESCRIPTION
2020 )
2021 SELECT
2022 h_process_id,
2023 PROCESS_NAME,
2024 g_user_id,
2025 SYSDATE,
2026 g_user_id,
2027 SYSDATE,
2028 g_session_id,
2029 PC_PENDING_STATUS,
2030 DESCRIPTION
2031 FROM
2032 BSC_DB_PROCESS_CONTROL
2033 WHERE
2034 PROCESS_ID = x_process_id;
2035
2036 IF x_process_name = PC_LOADER_PROCESS THEN
2037 -- Associate same input tables to new process
2038 --BUG 1473202 Input tables from a previous process may have been dropped due to
2039 --some system change, so we cannot take those input tables
2040 --BSC_DB_LOADER_CONTROL
2041 INSERT INTO BSC_DB_LOADER_CONTROL (
2042 PROCESS_ID,
2043 INPUT_TABLE_NAME,
2044 STATUS,
2045 ERROR_CODE,
2046 STAGE,
2047 LAST_STAGE_FLAG
2048 )
2049 SELECT
2050 h_process_id,
2051 INPUT_TABLE_NAME,
2052 LC_PENDING_STATUS,
2053 NULL,
2054 LC_PENDING_STAGE,
2055 0
2056 FROM
2057 BSC_DB_LOADER_CONTROL
2058 WHERE
2059 PROCESS_ID = x_process_id AND
2060 INPUT_TABLE_NAME IN (SELECT TABLE_NAME FROM BSC_DB_TABLES WHERE TABLE_TYPE = 0);
2061 END IF;
2062
2063 IF x_process_name = PC_LOAD_DIMENSIONS THEN
2064 -- Associate same input tables to new process
2065 --BSC_DB_LOADER_CONTROL
2066 INSERT INTO BSC_DB_LOADER_CONTROL (
2067 PROCESS_ID,
2068 INPUT_TABLE_NAME,
2069 STATUS,
2070 ERROR_CODE,
2071 STAGE,
2072 LAST_STAGE_FLAG
2073 )
2074 SELECT
2075 h_process_id,
2076 INPUT_TABLE_NAME,
2077 LC_PENDING_STATUS,
2078 NULL,
2079 LC_PENDING_STAGE,
2080 0
2081 FROM
2082 BSC_DB_LOADER_CONTROL
2083 WHERE
2084 PROCESS_ID = x_process_id AND
2085 INPUT_TABLE_NAME IN (SELECT TABLE_NAME FROM BSC_DB_TABLES WHERE TABLE_TYPE = 2);
2086 END IF;
2087
2088 COMMIT;
2089
2090 RETURN h_process_id;
2091
2092 EXCEPTION
2093 WHEN OTHERS THEN
2094 BSC_MESSAGE.Add(x_message => SQLERRM,
2095 x_source => 'BSC_UPDATE.Get_Process_Id');
2096 RETURN NULL;
2097
2098 END Get_Process_Id;
2099
2100
2101 /*===========================================================================+
2102 | FUNCTION Get_Process_Input_Tables
2103 +============================================================================*/
2104 FUNCTION Get_Process_Input_Tables (
2105 x_input_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2106 x_num_input_tables IN OUT NOCOPY NUMBER,
2107 x_status IN VARCHAR2
2108 ) RETURN BOOLEAN IS
2109
2110 TYPE t_cursor IS REF CURSOR;
2111
2112 /*
2113 c_input_tables t_cursor; -- g_process_id, x_status
2114 c_input_tables_sql VARCHAR2(2000) := 'SELECT input_table_name'||
2115 ' FROM bsc_db_loader_control'||
2116 ' WHERE process_id = :1 AND status = :2';
2117 */
2118 CURSOR c_input_tables (p_process_id NUMBER, p_status VARCHAR2) IS
2119 SELECT input_table_name
2120 FROM bsc_db_loader_control
2121 WHERE process_id = p_process_id AND status = p_status;
2122
2123 h_input_table_name VARCHAR2(30);
2124
2125 BEGIN
2126 x_num_input_tables := 0;
2127 --OPEN c_input_tables FOR c_input_tables_sql USING g_process_id, x_status;
2128 OPEN c_input_tables(g_process_id, x_status);
2129 FETCH c_input_tables INTO h_input_table_name;
2130 WHILE c_input_tables%FOUND LOOP
2131 x_num_input_tables := x_num_input_tables + 1;
2132 x_input_tables(x_num_input_tables) := h_input_table_name;
2133
2134 FETCH c_input_tables INTO h_input_table_name;
2135 END LOOP;
2136 CLOSE c_input_tables;
2137
2138 RETURN TRUE;
2139
2140 EXCEPTION
2141 WHEN OTHERS THEN
2142 BSC_MESSAGE.Add(x_message => SQLERRM,
2143 x_source => 'BSC_UPDATE.Get_Process_Input_Tables');
2144 RETURN FALSE;
2145
2146 END Get_Process_Input_Tables;
2147
2148
2149 /*===========================================================================+
2150 | FUNCTION Init_Env_Values
2151 +============================================================================*/
2152 FUNCTION Init_Env_Values RETURN BOOLEAN IS
2153
2154 BEGIN
2155 -- Ref: bug#3482442 In corner cases this query can return more than one
2156 -- row and it will fail. AUDSID is not PK. After meeting with
2157 -- Vinod and Kris and Venu, we should use FNG_GLOBAL.user_id
2158 g_user_id := BSC_APPS.fnd_global_user_id;
2159 g_session_id := USERENV('SESSIONID');
2160 g_schema_name := BSC_APPS.fnd_apps_schema;
2161
2162 RETURN TRUE;
2163
2164 EXCEPTION
2165 WHEN OTHERS THEN
2166 BSC_MESSAGE.Add(x_message => SQLERRM,
2167 x_source => 'BSC_UPDATE.Init_Env_Values');
2168 RETURN FALSE;
2169
2170 END Init_Env_Values;
2171
2172
2173 /*===========================================================================+
2174 | FUNCTION Import_ITables_From_DBSrc
2175 +============================================================================*/
2176 FUNCTION Import_ITables_From_DBSrc(
2177 x_input_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2178 x_num_input_tables IN NUMBER
2179 ) RETURN BOOLEAN IS
2180
2181 e_unexpected_error EXCEPTION;
2182 --LOCKING
2183 e_could_not_get_lock EXCEPTION;
2184
2185 h_source_type NUMBER;
2186 h_source_name VARCHAR2(500);
2187
2188 h_i NUMBER;
2189 h_message VARCHAR2(4000);
2190
2191 h_input_table VARCHAR2(30);
2192
2193 BEGIN
2194 h_source_name := NULL;
2195
2196 FOR h_i IN 1 .. x_num_input_tables LOOP
2197 h_input_table := x_input_tables(h_i);
2198
2199 IF NOT BSC_UPDATE_UTIL.Get_Input_Table_Source(h_input_table, h_source_type, h_source_name) THEN
2200 RAISE e_unexpected_error;
2201 END IF;
2202
2203 IF h_source_type = 4 THEN
2204 -- Stored procedure
2205
2206 h_message := BSC_UPDATE_UTIL.Get_Message('BSC_IMPORTING_TO_ITABLE');
2207 h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'INPUT_TABLE', h_input_table);
2208 BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2209
2210 h_message := BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SOURCE')||
2211 BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||' '||h_source_name;
2212 BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2213
2214 -- LOCKING: Lock required objects
2215 IF NOT BSC_UPDATE_LOCK.Lock_Import_ITable(h_input_table) THEN
2216 RAISE e_could_not_get_lock;
2217 END IF;
2218
2219 -- LOCKING: call the autonomous transaction
2220 IF NOT Import_ITable_StoredProc_AT(h_input_table, h_source_name) THEN
2221 RAISE e_unexpected_error;
2222 END IF;
2223
2224 -- LOCKING: commit to release the lock
2225 COMMIT;
2226 END IF;
2227
2228 END LOOP;
2229
2230 RETURN TRUE;
2231
2232 EXCEPTION
2233 --LOCKING
2234 WHEN e_could_not_get_lock THEN
2235 BSC_MESSAGE.Add (x_message => 'Loader could not get the required locks to continue.',
2236 x_source => 'BSC_UPDATE.Import_ITables_From_DBSrc');
2237 RETURN FALSE;
2238
2239 WHEN e_unexpected_error THEN
2240 BSC_MESSAGE.Add (x_message => BSC_UPDATE_UTIL.Get_Message('BSC_IMPORT_ITABLES_FAILED'),
2241 x_source => 'BSC_UPDATE.Import_ITables_From_DBSrc');
2242 RETURN FALSE;
2243
2244 WHEN OTHERS THEN
2245 BSC_MESSAGE.Add (x_message => SQLERRM,
2246 x_source => 'BSC_UPDATE.Import_ITables_From_DBSrc');
2247 RETURN FALSE;
2248 END Import_ITables_From_DBSrc;
2249
2250
2251 --LOCKING: new function
2252 /*===========================================================================+
2253 | FUNCTION Import_ITables_From_DBSrc_AT
2254 +============================================================================*/
2255 FUNCTION Import_ITables_From_DBSrc_AT(
2256 x_input_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2257 x_num_input_tables IN NUMBER
2258 ) RETURN BOOLEAN IS
2259 PRAGMA AUTONOMOUS_TRANSACTION;
2260 h_b BOOLEAN;
2261 BEGIN
2262 h_b := Import_ITables_From_DBSrc(x_input_tables, x_num_input_tables);
2263 commit; -- all autonomous transaction needs to commit
2264 RETURN h_b;
2265 END Import_ITables_From_DBSrc_AT;
2266
2267
2268 /*===========================================================================+
2269 | FUNCTION Import_ITable_StoredProc
2270 +============================================================================*/
2271 FUNCTION Import_ITable_StoredProc(
2272 x_input_table IN VARCHAR2,
2273 x_stored_proc IN VARCHAR2
2274 ) RETURN BOOLEAN IS
2275
2276 e_unexpected_error EXCEPTION;
2277
2278 h_sql VARCHAR2(32700);
2279
2280 TYPE t_cursor IS REF CURSOR;
2281
2282 /*
2283 c_status t_cursor; -- x_stored_proc, h_procedure
2284 c_status_sql VARCHAR2(2000) := 'SELECT status'||
2285 ' FROM user_objects'||
2286 ' WHERE object_name = :1 AND object_type = :2';
2287 */
2288 /*BUG#6598575 - logic for validating the Data Load Program(i.e.checking the status) is disabled*/
2289 /*CURSOR c_status (p_object_name VARCHAR2, p_object_type VARCHAR2) IS
2290 SELECT status
2291 FROM user_objects
2292 WHERE object_name = p_object_name AND object_type = p_object_type;
2293
2294 h_procedure VARCHAR2(30);
2295 h_stored_proc VARCHAR(200);
2296 h_status VARCHAR2(20);*/
2297 h_message VARCHAR2(4000);
2298
2299 BEGIN
2300 h_sql := NULL;
2301 /*h_procedure := 'PROCEDURE';*/
2302
2303 -- Delete data from the input table
2304 h_sql := 'DELETE FROM '||x_input_table;
2305 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2306 COMMIT;
2307
2308 /*h_stored_proc := UPPER(x_stored_proc);
2309
2310 -- Validate the stored procedure
2311 --OPEN c_status FOR c_status_sql USING h_stored_proc, h_procedure;
2312 OPEN c_status(h_stored_proc, h_procedure);
2313 FETCH c_status INTO h_status;
2314 IF c_status%FOUND THEN
2315 IF h_status = 'VALID' THEN*/
2316 -- Execute the stored procedure to populate the input table
2317 BEGIN
2318 h_sql := 'BEGIN '||x_stored_proc||'; END;';
2319 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2320
2321 -- Update the stage of the input table to UPLOADED
2322 IF NOT Update_Stage_Input_Table(x_input_table, LC_UPLOADED_STAGE) THEN
2323 RAISE e_unexpected_error;
2324 END IF;
2325
2326 EXCEPTION
2327 WHEN OTHERS THEN
2328 -- Error executing the stored procedure
2329 IF NOT Update_Status_Input_Table(x_input_table, LC_ERROR_STATUS, LC_UPLOAD_SP_EXECUTION_ERR) THEN
2330 RAISE e_unexpected_error;
2331 END IF;
2332
2333 h_message := BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'ERROR')||
2334 BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||' '||
2335 BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_FAILED');
2336 BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2337 BSC_UPDATE_LOG.Write_Line_Log(SQLERRM, BSC_UPDATE_LOG.OUTPUT);
2338 END;
2339 /*
2340 ELSE
2341 -- Stored procedure is invalid
2342 IF NOT Update_Status_Input_Table(x_input_table, LC_ERROR_STATUS, LC_UPLOAD_SP_INVALID_ERR) THEN
2343 RAISE e_unexpected_error;
2344 END IF;
2345
2346 h_message := BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'ERROR')||
2347 BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||' '||
2348 BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_IS_INVALID');
2349 BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2350 END IF;
2351 ELSE
2352 -- Stored procedure does not exists
2353 IF NOT Update_Status_Input_Table(x_input_table, LC_ERROR_STATUS, LC_UPLOAD_SP_NOT_FOUND_ERR) THEN
2354 RAISE e_unexpected_error;
2355 END IF;
2356
2357 h_message := BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'ERROR')||
2358 BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||' '||
2359 BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_NOT_FOUND');
2360 BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2361 END IF;
2362 CLOSE c_status;
2363 */
2364 COMMIT;
2365
2366 RETURN TRUE;
2367
2368 EXCEPTION
2369 WHEN e_unexpected_error THEN
2370 BSC_MESSAGE.Add (x_message => BSC_UPDATE_UTIL.Get_Message('BSC_IMPORT_ITABLES_FAILED'),
2371 x_source => 'BSC_UPDATE.Import_ITable_StoredProc');
2372 RETURN FALSE;
2373
2374 WHEN OTHERS THEN
2375 BSC_MESSAGE.Add (x_message => SQLERRM,
2376 x_source => 'BSC_UPDATE.Import_ITable_StoredProc');
2377 RETURN FALSE;
2378 END Import_ITable_StoredProc;
2379
2380
2381 --LOCKING: new function
2382 /*===========================================================================+
2383 | FUNCTION Import_ITable_StoredProc_AT
2384 +============================================================================*/
2385 FUNCTION Import_ITable_StoredProc_AT(
2386 x_input_table IN VARCHAR2,
2387 x_stored_proc IN VARCHAR2
2388 ) RETURN BOOLEAN IS
2389 PRAGMA AUTONOMOUS_TRANSACTION;
2390 h_b BOOLEAN;
2391 BEGIN
2392 h_b := Import_ITable_StoredProc(x_input_table, x_stored_proc);
2393 commit; -- all autonomous transaction needs to commit
2394 RETURN h_b;
2395 END Import_ITable_StoredProc_AT;
2396
2397
2398 /*===========================================================================+
2399 | FUNCTION Insert_Affected_Tables
2400 +============================================================================*/
2401 FUNCTION Insert_Affected_Tables (
2402 x_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2403 x_num_tables IN NUMBER,
2404 x_affected_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2405 x_num_affected_tables IN OUT NOCOPY NUMBER
2406 ) RETURN BOOLEAN IS
2407
2408 e_unexpected_error EXCEPTION;
2409
2410 TYPE t_cursor IS REF CURSOR;
2411 --h_cursor t_cursor;
2412
2413 CURSOR c_tables (p_source_table_name VARCHAR2) IS
2414 SELECT table_name
2415 FROM bsc_db_tables_rels
2416 WHERE source_table_name = p_source_table_name;
2417
2418 h_new_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
2419 h_num_new_tables NUMBER;
2420
2421 h_i NUMBER;
2422 h_sql VARCHAR2(32700);
2423
2424 h_table_name VARCHAR2(30);
2425
2426 BEGIN
2427
2428 h_num_new_tables := 0;
2429
2430 FOR h_i IN 1 .. x_num_tables LOOP
2431 /*
2432 h_sql := 'SELECT table_name'||
2433 ' FROM bsc_db_tables_rels'||
2434 ' WHERE source_table_name = :1';
2435 --OPEN h_cursor FOR h_sql USING x_tables(h_i);
2436 */
2437 OPEN c_tables(x_tables(h_i));
2438 FETCH c_tables INTO h_table_name;
2439 WHILE c_tables%FOUND LOOP
2440 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_table_name, x_affected_tables, x_num_affected_tables) THEN
2441 x_num_affected_tables := x_num_affected_tables + 1;
2442 x_affected_tables(x_num_affected_tables) := h_table_name;
2443
2444 h_num_new_tables := h_num_new_tables + 1;
2445 h_new_tables(h_num_new_tables) := h_table_name;
2446 END IF;
2447
2448 FETCH c_tables INTO h_table_name;
2449 END LOOP;
2450 CLOSE c_tables;
2451
2452 END LOOP;
2453
2454 IF h_num_new_tables > 0 THEN
2455 IF NOT Insert_Affected_Tables(h_new_tables, h_num_new_tables, x_affected_tables, x_num_affected_tables) THEN
2456 RAISE e_unexpected_error;
2457 END IF;
2458 END IF;
2459
2460 RETURN TRUE;
2461
2462 EXCEPTION
2463 WHEN e_unexpected_error THEN
2464 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_TABLES_INSERT_FAILED'),
2465 x_source => 'BSC_UPDATE.Insert_Affected_Tables');
2466 RETURN FALSE;
2467
2468 WHEN OTHERS THEN
2469 BSC_MESSAGE.Add(x_message => SQLERRM,
2470 x_source => 'BSC_UPDATE.Insert_Affected_Tables');
2471 RETURN FALSE;
2472
2473 END Insert_Affected_Tables;
2474
2475
2476 /*===========================================================================+
2477 | FUNCTION Load_Dim_Input_Tables
2478 +============================================================================*/
2479 FUNCTION Load_Dim_Input_Tables(
2480 x_input_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2481 x_num_input_tables IN NUMBER
2482 ) RETURN BOOLEAN IS
2483
2484 e_unexpected_error EXCEPTION;
2485 --LOCKING
2486 e_could_not_get_lock EXCEPTION;
2487
2488 h_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
2489 h_num_dim_tables NUMBER;
2490
2491 h_loaded_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
2492 h_num_loaded_tables NUMBER;
2493
2494 h_i NUMBER;
2495 h_b BOOLEAN;
2496 h_message VARCHAR2(4000);
2497
2498 h_input_table VARCHAR2(30);
2499 h_dim_table VARCHAR2(30);
2500
2501 h_table_has_any_row BOOLEAN;
2502 h_table_is_valid BOOLEAN;
2503
2504 --AW_INTEGRATION: new variables
2505 h_dim_table_type NUMBER;
2506 h_dim_level_list dbms_sql.varchar2_table;
2507 h_dim_was_loaded BOOLEAN;
2508
2509 h_input_tables_err_status BSC_UPDATE_UTIL.t_array_of_varchar2;
2510 h_num_input_tables_err_status NUMBER;
2511
2512 CURSOR c_input_tables_err_status IS
2513 SELECT input_table_name
2514 FROM bsc_db_loader_control
2515 WHERE process_id = g_process_id AND status = LC_ERROR_STATUS;
2516
2517 BEGIN
2518 h_num_dim_tables := 0;
2519 h_num_loaded_tables := 0;
2520 h_num_input_tables_err_status := 0;
2521
2522 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_DIMTABLE_UPD_PROCESS'), BSC_UPDATE_LOG.OUTPUT);
2523
2524 -- Get input table with status error
2525 OPEN c_input_tables_err_status;
2526 LOOP
2527 FETCH c_input_tables_err_status INTO h_input_table;
2528 EXIT WHEN c_input_tables_err_status%NOTFOUND;
2529 h_num_input_tables_err_status := h_num_input_tables_err_status + 1;
2530 h_input_tables_err_status(h_num_input_tables_err_status) := h_input_table;
2531 END LOOP;
2532 CLOSE c_input_tables_err_status;
2533
2534 -- Update the status of all input tables from PENDING to RUNNING
2535 IF NOT Update_Status_All_Input_Tables(LC_PENDING_STATUS, LC_RUNNING_STATUS, NULL) THEN
2536 RAISE e_unexpected_error;
2537 END IF;
2538 COMMIT;
2539
2540 -- Init array h_dim_tables which contains the dimension table corrsponding to input
2541 -- tables in array x_input_tables (same order)
2542 FOR h_i IN 1 .. x_num_input_tables LOOP
2543 h_dim_tables(h_i) := BSC_UPDATE_DIM.Get_Dim_Table_of_Input_Table(x_input_tables(h_i));
2544 END LOOP;
2545 h_num_dim_tables := x_num_input_tables;
2546
2547 -- Load input tables
2548 -- We need to load the parent tables before child tables
2549 -- This is to avoid invalid relationships when a parent and a child table are going to be
2550 -- loaded in the same process.
2551
2552 -- Until all system tables have been calculated
2553 WHILE h_num_loaded_tables <> h_num_dim_tables LOOP
2554 FOR h_i IN 1 .. h_num_dim_tables LOOP
2555 h_dim_table := h_dim_tables(h_i);
2556
2557 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_dim_table,
2558 h_loaded_tables,
2559 h_num_loaded_tables) THEN
2560 -- The table has not been calculated yet
2561
2562 -- Check if the table can be calculated now
2563 h_b := Can_Load_Dim_Table(h_dim_table,
2564 h_loaded_tables,
2565 h_num_loaded_tables,
2566 h_dim_tables,
2567 h_num_dim_tables);
2568
2569 IF h_b IS NULL THEN
2570 RAISE e_unexpected_error;
2571 END IF;
2572
2573 IF h_b THEN
2574 -- Table can be loaded because all the parent tables for that table
2575 -- have already been loaded
2576
2577 h_input_table := x_input_tables(h_i);
2578
2579 --LOCKING: Lock the required objects to process this dimension table
2580 --LOCKING: Remove any commit between this point and the commit to release the locks
2581 IF NOT BSC_UPDATE_LOCK.Lock_Load_Dimension_Table(h_dim_table, h_input_table) THEN
2582 RAISE e_could_not_get_lock;
2583 END IF;
2584
2585 -- AW_INTEGRATION: init this variable
2586 h_dim_was_loaded := FALSE;
2587
2588 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_input_table,
2589 h_input_tables_err_status,
2590 h_num_input_tables_err_status) THEN
2591 -- Input table is ok, there was no error before loading it from stored procedure
2592
2593 -- Know if the input table is empty or not.
2594 h_table_has_any_row := BSC_UPDATE_UTIL.Table_Has_Any_Row(h_input_table);
2595
2596 IF h_table_has_any_row IS NULL THEN
2597 RAISE e_unexpected_error;
2598 END IF;
2599
2600 IF h_table_has_any_row THEN
2601 -- Input table has records. The table can be processed
2602
2603 -- Validate input table
2604 h_message := BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_NAME_VALIDATION');
2605 h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
2606 BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2607 --LOCKING: call the automous transaction function
2608 h_table_is_valid := BSC_UPDATE_DIM.Validate_Input_Table_AT(h_input_table, h_dim_table);
2609
2610 IF h_table_is_valid IS NULL THEN
2611 RAISE e_unexpected_error;
2612 END IF;
2613
2614 IF h_table_is_valid THEN
2615 -- Input table doesn't have invalid codes
2616 h_message := BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_NAME_VALIDCONF');
2617 h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
2618 BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2619
2620 -- Update the stage of the input table to VALIDATED
2621 IF NOT Update_Stage_Input_Table(h_input_table, LC_VALIDATED_STAGE) THEN
2622 RAISE e_unexpected_error;
2623 END IF;
2624
2625 -- Load the dimension table
2626 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_UPDATING_DIM_TABLE')||
2627 ' '||h_dim_table, BSC_UPDATE_LOG.OUTPUT);
2628
2629 --LOCKING: Call the autonomous trnsaction
2630 IF NOT BSC_UPDATE_DIM.Load_Dim_Table_AT(h_dim_table, h_input_table) THEN
2631 RAISE e_unexpected_error;
2632 END IF;
2633
2634 -- AW_INTEGRATION: init this variable
2635 h_dim_was_loaded := TRUE;
2636
2637 -- Update the stage of the input table to COMPLETED UPDATED
2638 IF NOT Update_Stage_Input_Table(h_input_table, LC_COMPLETED_STAGE) THEN
2639 RAISE e_unexpected_error;
2640 END IF;
2641
2642 h_message := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATED_DIM_TABLE');
2643 h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_dim_table);
2644 BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2645 ELSE
2646 -- Input table has invalid codes
2647 h_message := BSC_UPDATE_UTIL.Get_Message('BSC_INVALID_CODES_ITABLE');
2648 h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
2649 BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2650
2651 -- Update the status of input table to ERROR
2652 IF NOT Update_Status_Input_Table(h_input_table, LC_ERROR_STATUS, LC_INVALID_CODES_ERR) THEN
2653 RAISE e_unexpected_error;
2654 END IF;
2655 END IF;
2656 ELSE
2657 -- Input table doesn't have any record.
2658
2659 -- The input table cannot be processed because is an empty table
2660 h_message := BSC_UPDATE_UTIL.Get_Message('BSC_MISSING_DATA_ITABLE');
2661 h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
2662 BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2663
2664 -- Update the status of the input table to NO_DATA
2665 IF NOT Update_Status_Input_Table(h_input_table, LC_NO_DATA_STATUS, NULL) THEN
2666 RAISE e_unexpected_error;
2667 END IF;
2668 END IF;
2669 END IF;
2670
2671 --AW_INTEGRATION: We need to load the dimension into AW even if the table was not loaded
2672 IF NOT h_dim_was_loaded THEN
2673 h_dim_table_type := BSC_UPDATE_DIM.Get_Dim_Table_Type(h_dim_table);
2674 IF h_dim_table_type = BSC_UPDATE_DIM.DIM_TABLE_TYPE_1N THEN
2675 IF BSC_UPDATE_DIM.Dimension_Used_In_AW_Kpi(h_dim_table) THEN
2676 BSC_UPDATE_LOG.Write_Line_Log('Loading '||h_dim_table||' into AW', BSC_UPDATE_LOG.OUTPUT);
2677 --LOCKING: Call the autonomous transaction procedure
2678 BSC_UPDATE_DIM.Load_Dim_Into_AW_AT(h_dim_table);
2679 END IF;
2680 END IF;
2681 END IF;
2682
2683 -- Add table to array of loaded tables
2684 h_num_loaded_tables := h_num_loaded_tables + 1;
2685 h_loaded_tables(h_num_loaded_tables) := h_dim_tables(h_i);
2686
2687 --LOCKING: commit to release the locks
2688 COMMIT;
2689 END IF;
2690 END IF;
2691 END LOOP;
2692 END LOOP;
2693
2694 -- Update the date of update process
2695 IF NOT BSC_UPDATE_UTIL.Write_Init_Variable_Value('UPDATE_DATE_DIM', TO_CHAR(SYSDATE, 'DD/MM/YYYY')) THEN
2696 RAISE e_unexpected_error;
2697 END IF;
2698 COMMIT;
2699
2700 -- Update the status of input tables
2701 IF NOT Update_Stage_Input_Tables(LC_RUNNING_STATUS, LC_COMPLETED_STAGE, FALSE) THEN
2702 RAISE e_unexpected_error;
2703 END IF;
2704 COMMIT;
2705
2706 IF NOT Update_Status_All_Input_Tables(LC_RUNNING_STATUS, LC_COMPLETED_STATUS, NULL) THEN
2707 RAISE e_unexpected_error;
2708 END IF;
2709 COMMIT;
2710
2711 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_DIMTABLE_UPDATED'), BSC_UPDATE_LOG.OUTPUT);
2712
2713 -- Write indicator with unexisting default dimenision values
2714 IF NOT BSC_UPDATE_DIM.WriteRemovedKeyItems THEN
2715 NULL;
2716 END IF;
2717
2718 RETURN TRUE;
2719 EXCEPTION
2720 --LOCKING
2721 WHEN e_could_not_get_lock THEN
2722 ROLLBACK;
2723 BSC_MESSAGE.Add (x_message => 'Loader could not get the required locks to continue.',
2724 x_source => 'BSC_UPDATE.Load_Dim_Input_Tables');
2725 h_b := Update_Status_All_Input_Tables(LC_RUNNING_STATUS, LC_ERROR_STATUS, LC_PROGRAM_ERR);
2726 COMMIT;
2727
2728 RETURN FALSE;
2729
2730 WHEN e_unexpected_error THEN
2731 ROLLBACK;
2732 BSC_MESSAGE.Add (x_message => BSC_UPDATE_UTIL.Get_Message('BSC_DIMTABLE_UPDATE_FAILED'),
2733 x_source => 'BSC_UPDATE.Load_Dim_Input_Tables');
2734 h_b := Update_Status_All_Input_Tables(LC_RUNNING_STATUS, LC_ERROR_STATUS, LC_PROGRAM_ERR);
2735 COMMIT;
2736
2737 RETURN FALSE;
2738
2739 WHEN OTHERS THEN
2740 ROLLBACK;
2741 BSC_MESSAGE.Add (x_message => SQLERRM,
2742 x_source => 'BSC_UPDATE.Load_Dim_Input_Tables');
2743
2744 h_b := Update_Status_All_Input_Tables(LC_RUNNING_STATUS, LC_ERROR_STATUS, LC_PROGRAM_ERR);
2745 COMMIT;
2746
2747 RETURN FALSE;
2748 END Load_Dim_Input_Tables;
2749
2750
2751 --AW_INTEGRATION: New function
2752 /*===========================================================================+
2753 | FUNCTION Load_Dims_Into_AW
2754 +============================================================================*/
2755 FUNCTION Load_Dims_Into_AW(
2756 x_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2757 x_num_dim_tables IN NUMBER
2758 ) RETURN BOOLEAN IS
2759
2760 e_unexpected_error EXCEPTION;
2761
2762 h_loaded_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
2763 h_num_loaded_tables NUMBER;
2764
2765 h_i NUMBER;
2766 h_b BOOLEAN;
2767
2768 h_dim_table VARCHAR2(30);
2769
2770 h_dim_level_list dbms_sql.varchar2_table;
2771
2772 BEGIN
2773 h_num_loaded_tables := 0;
2774
2775 -- We need to load the parent tables before child tables
2776
2777 -- Until all dimensions have been loaded
2778 WHILE h_num_loaded_tables <> x_num_dim_tables LOOP
2779 FOR h_i IN 1 .. x_num_dim_tables LOOP
2780 h_dim_table := x_dim_tables(h_i);
2781
2782 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_dim_table,
2783 h_loaded_tables,
2784 h_num_loaded_tables) THEN
2785 -- The table has not been calculated yet
2786
2787 -- Check if the table can be calculated now
2788 h_b := Can_Load_Dim_Table(h_dim_table,
2789 h_loaded_tables,
2790 h_num_loaded_tables,
2791 x_dim_tables,
2792 x_num_dim_tables);
2793
2794 IF h_b IS NULL THEN
2795 RAISE e_unexpected_error;
2796 END IF;
2797
2798 IF h_b THEN
2799 -- Table can be loaded because all the parent tables for that table
2800 -- have already been loaded
2801
2802 h_dim_level_list.delete;
2803 h_dim_level_list(1) := h_dim_table;
2804 bsc_aw_load.load_dim(
2805 p_dim_level_list => h_dim_level_list,
2806 p_options => 'DEBUG LOG'
2807 );
2808
2809 -- Add table to array of loaded tables
2810 h_num_loaded_tables := h_num_loaded_tables + 1;
2811 h_loaded_tables(h_num_loaded_tables) := x_dim_tables(h_i);
2812
2813 commit;
2814 END IF;
2815 END IF;
2816 END LOOP;
2817 END LOOP;
2818
2819 RETURN TRUE;
2820 EXCEPTION
2821 WHEN e_unexpected_error THEN
2822 ROLLBACK;
2823 BSC_MESSAGE.Add (x_message => BSC_UPDATE_UTIL.Get_Message('BSC_DIMTABLE_UPDATE_FAILED'),
2824 x_source => 'BSC_UPDATE.Load_Dims_Into_AW');
2825 RETURN FALSE;
2826
2827 WHEN OTHERS THEN
2828 ROLLBACK;
2829 BSC_MESSAGE.Add (x_message => SQLERRM,
2830 x_source => 'BSC_UPDATE.Load_Dims_Into_AW');
2831 RETURN FALSE;
2832 END Load_Dims_Into_AW;
2833
2834
2835 -- Update Prototype Flag for underlying KPI Measures for the Objective to 7,
2836 -- so that color can be re-calculated for all of them.
2837 FUNCTION Update_Kpis_Prototype_Flag (
2838 x_indicator IN NUMBER
2839 ) RETURN BOOLEAN IS
2840 BEGIN
2841 -- Color By KPI: Mark KPIs for color re-calculation
2842 UPDATE bsc_kpi_analysis_measures_b
2843 SET prototype_flag = 7
2844 WHERE indicator = x_indicator;
2845
2846 RETURN TRUE;
2847
2848 EXCEPTION
2849 WHEN OTHERS THEN
2850 BSC_MESSAGE.Add( x_message => SQLERRM
2851 , x_source => 'BSC_UPDATE.Update_Kpis_Prototype_Flag');
2852 RETURN FALSE;
2853 END Update_Kpis_Prototype_Flag;
2854
2855
2856 /*===========================================================================+
2857 | FUNCTION Process_Input_Tables
2858 +============================================================================*/
2859 FUNCTION Process_Input_Tables(
2860 x_input_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2861 x_num_input_tables IN NUMBER,
2862 x_start_from IN NUMBER
2863 ) RETURN BOOLEAN IS
2864
2865 e_unexpected_error EXCEPTION;
2866 --LOCKING
2867 e_could_not_get_lock EXCEPTION;
2868
2869 h_b BOOLEAN;
2870
2871 h_i NUMBER;
2872
2873 h_input_table VARCHAR2(30);
2874 h_table_has_any_row BOOLEAN;
2875 h_table_is_valid BOOLEAN;
2876 h_last_stage VARCHAR2(1);
2877
2878 h_base_table VARCHAR2(30);
2879
2880 h_base_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
2881 h_num_base_tables NUMBER;
2882
2883 h_base_tables_to_color BSC_UPDATE_UTIL.t_array_of_varchar2;
2884
2885 h_num_base_tables_to_color NUMBER;
2886
2887 h_system_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
2888 h_num_system_tables NUMBER;
2889
2890 h_calculated_sys_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
2891 h_num_calculated_sys_tables NUMBER;
2892
2893 h_color_indicators BSC_UPDATE_UTIL.t_array_of_number;
2894 h_num_color_indicators NUMBER;
2895
2896 h_message VARCHAR2(4000);
2897
2898 h_table_edw_flag NUMBER;
2899
2900 h_sql VARCHAR2(32000);
2901
2902 h_kpis BSC_UPDATE_UTIL.t_array_kpis;
2903 h_num_kpis NUMBER;
2904 h_calc_summary_table BOOLEAN;
2905 h_calc_color BOOLEAN;
2906
2907 -- AW_INTEGRATION: need these new variables
2908 h_base_tables_aw BSC_UPDATE_UTIL.t_array_of_varchar2;
2909 h_num_base_tables_aw NUMBER;
2910 h_system_tables_aw BSC_UPDATE_UTIL.t_array_of_varchar2;
2911 h_num_system_tables_aw NUMBER;
2912 h_indicators_aw BSC_UPDATE_UTIL.t_array_of_number;
2913 h_num_indicators_aw NUMBER;
2914 h_aw_flag BOOLEAN;
2915 h_calc_aw_kpi BOOLEAN;
2916 h_kpi_list dbms_sql.varchar2_table;
2917
2918 -- ENH_B_TABLES_PERF: new variables
2919 h_proj_tbl_name VARCHAR2(30);
2920
2921 BEGIN
2922 h_num_base_tables := 0;
2923 h_num_base_tables_to_color := 0;
2924 h_num_system_tables := 0;
2925 h_num_calculated_sys_tables := 0;
2926 h_num_color_indicators := 0;
2927 -- AW_INTEGRATION: init these two variables
2928 h_num_base_tables_aw := 0;
2929 h_num_system_tables_aw := 0;
2930 h_num_indicators_aw := 0;
2931
2932 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_DATATABLE_UPD_PROCESS'), BSC_UPDATE_LOG.OUTPUT);
2933
2934 -- Create generic temporal tables for the process
2935 --LOCKING: lock the temporary tables
2936 IF NOT BSC_UPDATE_LOCK.Lock_Temp_Tables('DATA') THEN
2937 RAISE e_could_not_get_lock;
2938 END IF;
2939
2940 --LOCKING: call the autonomous transaction function
2941 IF NOT BSC_UPDATE_BASE.Create_Generic_Temp_Tables_AT THEN
2942 RAISE e_unexpected_error;
2943 END IF;
2944
2945 -- ENH_B_TABLES_PERF: create new temporary tables needed in the new strategy
2946 IF NOT BSC_UPDATE_BASE_V2.Create_Generic_Temp_Tables_AT THEN
2947 RAISE e_unexpected_error;
2948 END IF;
2949
2950 --LOCKING: commit to release the locks
2951 COMMIT;
2952
2953 IF x_start_from = 0 THEN
2954 -- The process starts from input tables. This means that the program
2955 -- takes input tables, make codes validation and updates base tables
2956
2957 -- Update the status of all input tables from PENDING to RUNNING
2958 IF NOT Update_Status_All_Input_Tables(LC_PENDING_STATUS, LC_RUNNING_STATUS, NULL) THEN
2959 RAISE e_unexpected_error;
2960 END IF;
2961 COMMIT;
2962
2963 -- Loop into the input tables in the array x_input_tables.
2964 -- These tables have status RUNNING;
2965 -- The tables with status ERROR are not processed.
2966
2967 FOR h_i IN 1 .. x_num_input_tables LOOP
2968 h_input_table := x_input_tables(h_i);
2969
2970 -- Fix bug#4341554. move this delete here, we need to commit this deletion
2971 -- Fix bug#3813853. Delete invalid codes for this input table from bsc_db_validation
2972 DELETE FROM bsc_db_validation
2973 WHERE input_table_name = h_input_table;
2974 commit;
2975
2976 --LOCKING: need to know the base table name here
2977 IF NOT Get_Base_Table_Of_Input_Table(h_input_table, h_base_table) THEN
2978 RAISE e_unexpected_error;
2979 END IF;
2980
2981 -- AW_INTEGRATION: Know if the base table belongs to an AW Kpi
2982 IF BSC_APPS.bsc_mv THEN
2983 h_aw_flag := BSC_UPDATE_UTIL.Is_Table_For_AW_Kpi(h_base_table);
2984 ELSE
2985 h_aw_flag := FALSE;
2986 END IF;
2987
2988 --LOCKING: lock the input table and the base table
2989 IF NOT BSC_UPDATE_LOCK.Lock_Update_Base_Table(h_input_table, h_base_table) THEN
2990 RAISE e_could_not_get_lock;
2991 END IF;
2992
2993 --LOCKING: review no commit between this point and the commit to release the locks
2994
2995 -- Know if the input table is empty or not.
2996 h_table_has_any_row := BSC_UPDATE_UTIL.Table_Has_Any_Row(h_input_table);
2997
2998 IF h_table_has_any_row IS NULL THEN
2999 RAISE e_unexpected_error;
3000 END IF;
3001
3002 IF h_table_has_any_row THEN
3003
3004 -- Input table has records. The table can be processed
3005 -- There is new data in the input table. Therefore, this is the last
3006 -- stage.
3007
3008 --LOCKING: no commit inside this function
3009 IF NOT Flag_Last_Stage_Input_Table(h_input_table) THEN
3010 RAISE e_unexpected_error;
3011 END IF;
3012
3013 -- Validate input table
3014 h_message := BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_NAME_VALIDATION');
3015 h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
3016 BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
3017 --LOCKING: Call the autonomous transaction function
3018 h_table_is_valid := BSC_UPDATE_VAL.Validate_Codes_AT(h_input_table);
3019
3020 IF h_table_is_valid IS NULL THEN
3021 RAISE e_unexpected_error;
3022 END IF;
3023
3024 IF h_table_is_valid THEN
3025 -- Input table doesn't have invalid codes
3026 h_message := BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_NAME_VALIDCONF');
3027 h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
3028 BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
3029
3030 -- Update the stage of the input table to VALIDATED
3031 --LOCKING: there is no commit inside this function so we do not need AT
3032 IF NOT Update_Stage_Input_Table(h_input_table, LC_VALIDATED_STAGE) THEN
3033 RAISE e_unexpected_error;
3034 END IF;
3035
3036 -- Update the base table
3037 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_UPDATING_BASE_TABLE')||
3038 ' '||h_base_table, BSC_UPDATE_LOG.OUTPUT);
3039
3040 -- ENH_B_TABLES_PERF: If the base table has a projection table then this is a new strategy.
3041 h_proj_tbl_name := BSC_UPDATE_BASE_V2.Get_Base_Proj_Tbl_Name(h_base_table);
3042 IF h_proj_tbl_name IS NULL THEN
3043 -- AW_INTEGRATION: pass h_aw_flag
3044 -- LOCKING: call the autonomous transaction function
3045 IF NOT BSC_UPDATE_BASE.Calculate_Base_Table_AT(h_base_table, h_input_table, FALSE, h_aw_flag) THEN
3046 RAISE e_unexpected_error;
3047 END IF;
3048 ELSE
3049 -- New strategy for better performance.
3050 IF NOT BSC_UPDATE_BASE_V2.Calculate_Base_Table_AT(h_base_table, h_input_table, FALSE, h_aw_flag) THEN
3051 RAISE e_unexpected_error;
3052 END IF;
3053 END IF;
3054
3055 -- Update the stage of the input table to BASE UPDATED
3056 --LOCKING: there is no commit inside this function so we do not need AT
3057 IF NOT Update_Stage_Input_Table(h_input_table, LC_BASE_UPDATED_STAGE) THEN
3058 RAISE e_unexpected_error;
3059 END IF;
3060
3061 -- The base table was calculated successfully. This is the last stage.
3062 --LOCKING: there is no commit inside this function so we do not need AT
3063 IF NOT Flag_Last_Stage_Input_Table(h_input_table) THEN
3064 RAISE e_unexpected_error;
3065 END IF;
3066
3067 -- Add the base table of the input table to the array h_base_tables. This array has the
3068 -- base tables that are going to be used to calculate all the summary tables in this process.
3069
3070 h_num_base_tables := h_num_base_tables + 1;
3071 h_base_tables(h_num_base_tables) := h_base_table;
3072
3073 -- AW_INTEGRATION: If the base table if for an AW kpi we add this table to the array
3074 -- h_base_tables_aw
3075 IF h_aw_flag THEN
3076 h_num_base_tables_aw := h_num_base_tables_aw + 1;
3077 h_base_tables_aw(h_num_base_tables_aw) := h_base_table;
3078 END IF;
3079
3080 -- Add the base table of the input table to the array h_base_tables_to_color. This array has the
3081 -- base tables that are going to be used to identify which indicators need to be
3082 -- colored.
3083 h_num_base_tables_to_color := h_num_base_tables_to_color + 1;
3084 h_base_tables_to_color(h_num_base_tables_to_color) := h_base_table;
3085
3086 h_message := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATED_BASE_TABLE');
3087 h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_base_table);
3088 BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
3089
3090 --LOCKING: we do not need to call this here. base tables are not used by any kpi
3091 -- Update Time Stamp of the indicators using this table directly
3092 --BSC_UPDATE_UTIL.Update_Kpi_Table_Time_Stamp(h_base_table);
3093
3094 ELSE
3095 -- Input table has invalid codes
3096 h_message := BSC_UPDATE_UTIL.Get_Message('BSC_INVALID_CODES_ITABLE');
3097 h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
3098 BSC_UPDATE_LOG.Write_Line_Log(h_message,
3099 BSC_UPDATE_LOG.OUTPUT);
3100
3101 -- Update the status of input table to ERROR
3102 --LOCKING: there is no commit inside this function so we do not need AT
3103 IF NOT Update_Status_Input_Table(h_input_table, LC_ERROR_STATUS, LC_INVALID_CODES_ERR) THEN
3104 RAISE e_unexpected_error;
3105 END IF;
3106 END IF;
3107
3108 ELSE
3109 -- Input table doesn't have any record.
3110
3111 -- Fix bug#4545799: If loader is running 'By Objective' we need to refresh
3112 -- the summaries of the objectives even that the input table is empty
3113 IF g_kpi_mode THEN
3114 -- loader running 'by objective'
3115
3116 --LOCKING: there is no commit inside this function so we do not need AT
3117 IF NOT Update_Stage_Input_Table(h_input_table, LC_BASE_UPDATED_STAGE) THEN
3118 RAISE e_unexpected_error;
3119 END IF;
3120
3121 -- Add the base table of the input table to the array h_base_tables. This array has the
3122 -- base tables that are going to be used to calculate all the summary tables in this process.
3123 h_num_base_tables := h_num_base_tables + 1;
3124 h_base_tables(h_num_base_tables) := h_base_table;
3125
3126 -- AW_INTEGRATION: If the base table if for an AW kpi we add this table to the array
3127 -- h_base_tables_aw
3128 IF h_aw_flag THEN
3129 h_num_base_tables_aw := h_num_base_tables_aw + 1;
3130 h_base_tables_aw(h_num_base_tables_aw) := h_base_table;
3131 END IF;
3132
3133 -- Add the base table of the input table to the array h_base_tables_to_color. This array has the
3134 -- base tables that are going to be used to identify which indicators need to be
3135 -- colored.
3136 h_num_base_tables_to_color := h_num_base_tables_to_color + 1;
3137 h_base_tables_to_color(h_num_base_tables_to_color) := h_base_table;
3138
3139 -- Fix bug#4630260: Even that this input table is going to be processed
3140 -- QA want that this input table appears in the log file as empty and complete
3141 -- with warning.
3142 -- Update the status of the input table to NO_DATA
3143 --LOCKING: there is no commit inside this function so we do not need AT
3144 IF NOT Update_Status_Input_Table(h_input_table, LC_NO_DATA_STATUS, NULL) THEN
3145 RAISE e_unexpected_error;
3146 END IF;
3147
3148 h_message := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATED_BASE_TABLE');
3149 h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_base_table);
3150 BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
3151 ELSE
3152 -- The input table cannot be processed because is an empty table
3153 h_message := BSC_UPDATE_UTIL.Get_Message('BSC_MISSING_DATA_ITABLE');
3154 h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
3155 BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
3156
3157 -- Update the status of the input table to NO_DATA
3158 --LOCKING: there is no commit inside this function so we do not need AT
3159 IF NOT Update_Status_Input_Table(h_input_table, LC_NO_DATA_STATUS, NULL) THEN
3160 RAISE e_unexpected_error;
3161 END IF;
3162 END IF;
3163 END IF;
3164
3165 --LOCKING: Commit to release locks
3166 COMMIT;
3167 END LOOP;
3168
3169 ELSE
3170 -- The process starts from system tables. This means that the base tables of the
3171 -- input tables given in the array already are updated and the process starts
3172
3173 -- calculating the system tables affected by those input tables.
3174 -- This mode is uused by incremental update before launch the real update process
3175 -- with the selected input tables.
3176
3177 FOR h_i IN 1 .. x_num_input_tables LOOP
3178 -- Add the base table of the input table to the array h_base_tables. This array has the
3179 -- base tables that are going to be used to calculate all the summary tables in this process.
3180
3181 IF NOT Get_Base_Table_Of_Input_Table(x_input_tables(h_i), h_base_table) THEN
3182 RAISE e_unexpected_error;
3183 END IF;
3184
3185 --LOCKING: lock the input table and the base table
3186 IF NOT BSC_UPDATE_LOCK.Lock_Update_Base_Table(x_input_tables(h_i), h_base_table) THEN
3187 RAISE e_could_not_get_lock;
3188 END IF;
3189
3190 --LOCKING: review no commit between this point and the commit to release the locks
3191
3192 -- BSC-MV Note: In this architecture we calculate higher periodicities in the base table.
3193 -- For that reason if some non-structural change like change form SUM to AVG
3194 -- happens, we need to recalculate the higher periodicities and projection.
3195 -- Bug#3768015: We need to do it also in summary tables architecture. The projections
3196 -- are calculated in the base tables and the user could have activated projection
3197 -- so it needs to be calculatated.
3198
3199 -- Update the base table
3200 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_UPDATING_BASE_TABLE')||
3201 ' '||h_base_table, BSC_UPDATE_LOG.OUTPUT);
3202
3203 -- AW_INTEGRATION: Know if the base table belongs to an AW Kpi
3204 IF BSC_APPS.bsc_mv THEN
3205 h_aw_flag := BSC_UPDATE_UTIL.Is_Table_For_AW_Kpi(h_base_table);
3206 ELSE
3207 h_aw_flag := FALSE;
3208 END IF;
3209
3210 -- ENH_B_TABLES_PERF: If the base table has a projection table then this is a new strategy.
3211 h_proj_tbl_name := BSC_UPDATE_BASE_V2.Get_Base_Proj_Tbl_Name(h_base_table);
3212 IF h_proj_tbl_name IS NULL THEN
3213 -- AW_INTEGRATION: pass h_aw_flag
3214 -- LOCKING: call the autonomous transaction function
3215 IF NOT BSC_UPDATE_BASE.Calculate_Base_Table_AT(h_base_table, x_input_tables(h_i), TRUE, h_aw_flag) THEN
3216 RAISE e_unexpected_error;
3217 END IF;
3218 ELSE
3219 -- New strategy for better performance.
3220 IF NOT BSC_UPDATE_BASE_V2.Calculate_Base_Table_AT(h_base_table, x_input_tables(h_i), TRUE, h_aw_flag) THEN
3221 RAISE e_unexpected_error;
3222 END IF;
3223 END IF;
3224
3225 h_message := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATED_BASE_TABLE');
3226 h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_base_table);
3227 BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
3228
3229 h_num_base_tables := h_num_base_tables + 1;
3230 h_base_tables(h_num_base_tables) := h_base_table;
3231
3232 -- AW_INTEGRATION: If the base table if for an AW kpi we add this table to the array
3233 -- h_base_tables_aw
3234 IF h_aw_flag THEN
3235 h_num_base_tables_aw := h_num_base_tables_aw + 1;
3236 h_base_tables_aw(h_num_base_tables_aw) := h_base_table;
3237 END IF;
3238
3239 -- Add the base table of the input table to the array h_base_tables_to_color. This array has the
3240 -- base tables that are going to be used to identify which indicators need to be
3241 -- colored.
3242 h_num_base_tables_to_color := h_num_base_tables_to_color + 1;
3243 h_base_tables_to_color(h_num_base_tables_to_color) := h_base_table;
3244
3245 --LOCKING: commit to release the lock
3246 COMMIT;
3247 END LOOP;
3248 END IF;
3249
3250 -- So far, we have in the array h_base_tables the base tables to take to calculate the system tables
3251
3252 -- Initialize the array h_system_tables with the system tables that are affected by the base tables
3253 -- in the array h_base_tables
3254
3255 IF NOT Insert_Affected_Tables(h_base_tables, h_num_base_tables, h_system_tables, h_num_system_tables) THEN
3256 RAISE e_unexpected_error;
3257 END IF;
3258
3259 -- Fix bug#4681065: write warning message with indicators that Laoder plan to calculate but
3260 -- are in prototype and cannot be calculated.
3261 -- Note that this procedure sets the global variable g_warning to true if there are
3262 -- indicators in prototype
3263 Write_Warning_Kpis_In_Prot(h_system_tables, h_num_system_tables);
3264
3265 -- AW_INTEGRATION: Initialize the array h_system_tables_aw with the system tables that are affected by
3266 -- the base tables for AW Kpis. By design we know that AW indicators does not share base tables
3267 -- with kpis implemented with MVs. By design AW is never implemented along Summary tables architecture
3268 IF BSC_APPS.bsc_mv THEN
3269 IF NOT Insert_Affected_Tables(h_base_tables_aw, h_num_base_tables_aw, h_system_tables_aw, h_num_system_tables_aw) THEN
3270 RAISE e_unexpected_error;
3271 END IF;
3272 END IF;
3273
3274 -- Calculate the system tables
3275 BSC_UPDATE_SUM.g_refreshed_mvs.delete;
3276 BSC_UPDATE_SUM.g_num_refreshed_mvs := 0;
3277
3278 -- Until all system tables have been calculated
3279 WHILE h_num_calculated_sys_tables <> h_num_system_tables LOOP
3280 FOR h_i IN 1 .. h_num_system_tables LOOP
3281 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_system_tables(h_i),
3282 h_calculated_sys_tables,
3283 h_num_calculated_sys_tables) THEN
3284 -- The table has not been calculated yet
3285
3286 -- Check if the table can be calculated now
3287 h_b := Can_Calculate_Sys_Table(h_system_tables(h_i),
3288 h_calculated_sys_tables,
3289 h_num_calculated_sys_tables,
3290 h_system_tables,
3291 h_num_system_tables);
3292
3293 IF h_b IS NULL THEN
3294 RAISE e_unexpected_error;
3295 END IF;
3296
3297 IF h_b THEN
3298 -- Table can be calculated because all the origin tables for that table
3299 -- have already been calculated
3300
3301 -- BSC-BIS-DIMENSIONS Note:
3302 -- From this implementation allow to run loader when there are indicators in prototype.
3303 -- If the table is used direclty by a Kpi and the Kpi is in production then we calculate
3304 -- the table.
3305
3306 -- If the table is not used directly by a Kpi then this fuctions returns 0;
3307 h_num_kpis := BSC_UPDATE_UTIL.Get_Kpis_Using_Table(h_system_tables(h_i), h_kpis);
3308
3309 IF h_num_kpis = 0 THEN
3310 -- This table is not used directly by any Kpi (T table) so we need to
3311 -- to calculate the table.
3312 h_calc_summary_table := TRUE;
3313 ELSE
3314 -- If at least one indicator using the table is in production then we calculate the table.
3315 -- Also, if we are running in KPI_MODE we need to validate that the Kpi belong
3316 -- to the list in g_indicators
3317 h_calc_summary_table := FALSE;
3318 FOR h_i IN 1..h_num_kpis LOOP
3319 IF h_kpis(h_i).prototype_flag IN (0,6,7) THEN
3320 IF g_kpi_mode THEN
3321 IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Number(h_kpis(h_i).indicator,
3322 g_indicators,
3323 g_num_indicators) THEN
3324 h_calc_summary_table := TRUE;
3325 EXIT;
3326 END IF;
3327 ELSE
3328 h_calc_summary_table := TRUE;
3329 EXIT;
3330 END IF;
3331 END IF;
3332 END LOOP;
3333 END IF;
3334
3335 IF h_calc_summary_table THEN
3336 -- Calculate the summary (system) table
3337 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_SUMTABLE_CALC_PROCESS')||
3338 ' '||h_system_tables(h_i),
3339 BSC_UPDATE_LOG.OUTPUT);
3340
3341
3342 -- BSC-MV Note: In new architecture, call Calculate_Sum_Table_MV()
3343 IF BSC_APPS.bsc_mv THEN
3344 -- AW_INTEGRATION: Know if the table is used in a AW indicator
3345 IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_system_tables(h_i),
3346 h_system_tables_aw,
3347 h_num_system_tables_aw) THEN
3348 -- This table is for an AW indicator
3349 --LOCKING: lock the tables needed to refresh this table
3350 IF NOT BSC_UPDATE_LOCK.Lock_Refresh_AW_Table(h_system_tables(h_i)) THEN
3351 RAISE e_could_not_get_lock;
3352 END IF;
3353
3354 --LOCKING: call the autonomous transaction function
3355 IF NOT BSC_UPDATE_SUM.Calculate_Sum_Table_AW_AT(h_system_tables(h_i)) THEN
3356 RAISE e_unexpected_error;
3357 END IF;
3358
3359 --LOCKING: commit to release locks
3360 COMMIT;
3361 ELSE
3362 -- This table is for a MV indicator
3363 --LOCKING: lock the tables needed to refresh this table
3364 IF NOT BSC_UPDATE_LOCK.Lock_Refresh_MV(h_system_tables(h_i)) THEN
3365 RAISE e_could_not_get_lock;
3366 END IF;
3367
3368 -- LOCKING: call the autonomous transaction function
3369 IF NOT BSC_UPDATE_SUM.Calculate_Sum_Table_MV_AT(h_system_tables(h_i),
3370 h_calculated_sys_tables,
3371 h_num_calculated_sys_tables,
3372 h_system_tables,
3373 h_num_system_tables) THEN
3374 RAISE e_unexpected_error;
3375 END IF;
3376
3377 --LOCKING: commit to release the locks
3378 COMMIT;
3379 END IF;
3380 ELSE
3381 --LOCKING: lock the tables needed to refresh this table
3382 IF NOT BSC_UPDATE_LOCK.Lock_Refresh_Sum_Table(h_system_tables(h_i)) THEN
3383 RAISE e_could_not_get_lock;
3384 END IF;
3385
3386 --LOCKING: call the autonomous transaction function
3387 IF NOT BSC_UPDATE_SUM.Calculate_Sum_Table_AT(h_system_tables(h_i)) THEN
3388 RAISE e_unexpected_error;
3389 END IF;
3390
3391 --LOCKING: commit to refresh the locks
3392 COMMIT;
3393 END IF;
3394 COMMIT;
3395
3396 h_message := BSC_UPDATE_UTIL.Get_Message('BSC_SUMTABLE_CALCULATED');
3397 h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_system_tables(h_i));
3398 BSC_UPDATE_LOG.Write_Line_Log(h_message,
3399 BSC_UPDATE_LOG.OUTPUT);
3400
3401 --LOCKING: remove this call
3402 -- Update Time Stamp of the indicators using this table directly
3403 --LOCKING: Lock the indicator period fo the indicators using this table
3404 IF NOT BSC_UPDATE_LOCK.Lock_Period_Indicators(h_system_tables(h_i)) THEN
3405 RAISE e_could_not_get_lock;
3406 END IF;
3407
3408 BSC_UPDATE_UTIL.Update_Kpi_Table_Time_Stamp(h_system_tables(h_i));
3409
3410 --LOCKING: commit to release lock
3411 COMMIT;
3412 END IF;
3413
3414 -- Add table to array of calculated tables
3415 h_num_calculated_sys_tables := h_num_calculated_sys_tables + 1;
3416 h_calculated_sys_tables(h_num_calculated_sys_tables) := h_system_tables(h_i);
3417
3418 END IF;
3419 END IF;
3420 END LOOP;
3421 END LOOP;
3422
3423 -- AW_INTEGRATION: Refresh AW indicators' cubes. I will use Get_Indicators_To_Color()
3424 -- to get the lost of indicators affected by the base tables of AW indicators
3425 IF BSC_APPS.bsc_mv THEN
3426
3427 IF NOT Get_Indicators_To_Color(h_base_tables_aw,
3428 h_num_base_tables_aw,
3429 h_indicators_aw,
3430 h_num_indicators_aw) THEN
3431 RAISE e_unexpected_error;
3432 END IF;
3433
3434 FOR h_i IN 1 .. h_num_indicators_aw LOOP
3435 -- BSC-BIS-DIMENSIONS: Starting from this implementation we can run Loader even if there
3436 -- are indicators in prototype. We need to check and only color indicators in production mode.
3437 -- Additionally if we are running in KPI_MODE we need to color only indicators
3438 -- that belong to the array g_indicators.
3439 h_calc_aw_kpi := FALSE;
3440 IF BSC_UPDATE_UTIL.Is_Kpi_In_Production(h_indicators_aw(h_i)) THEN
3441 IF g_kpi_mode THEN
3442 IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Number(h_indicators_aw(h_i),
3443 g_indicators,
3444 g_num_indicators) THEN
3445 h_calc_aw_kpi := TRUE;
3446 END IF;
3447 ELSE
3448 h_calc_aw_kpi := TRUE;
3449 END IF;
3450 END IF;
3451
3452 IF h_calc_aw_kpi THEN
3453 BSC_UPDATE_LOG.Write_Line_log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_IVIEWER', 'REFRESH')||
3454 BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
3455 ' '||h_indicators_aw(h_i),
3456 BSC_UPDATE_LOG.OUTPUT);
3457
3458 --LOCKING: Lock the objects required to refresh the AW indicator cubes
3459 IF NOT BSC_UPDATE_LOCK.Lock_Refresh_AW_Indicator(h_indicators_aw(h_i)) THEN
3460 RAISE e_could_not_get_lock;
3461 END IF;
3462
3463 -- AW_INTEGRATION: Call the aw api to refresh the indicator cubes
3464 --LOCKING: call the autonomous transaction procedure
3465 BSC_UPDATE_SUM.Refresh_AW_Kpi_AT(h_indicators_aw(h_i));
3466
3467 --LOCKING: commit to release the locks
3468 COMMIT;
3469 END IF;
3470 END LOOP;
3471 END IF;
3472
3473 --LOCKING: Comment this code. I am going to calculate the indicator period
3474 -- indicator by indicator just before calculate the color
3475 --IF NOT Update_Indicators_Periods() THEN
3476 -- RAISE e_unexpected_error;
3477 --END IF;
3478 --COMMIT;
3479
3480 -- Update the stage of the input tables whose status in RUNNING to SYSTEM UPDATED
3481 IF x_start_from = 0 THEN
3482 IF NOT Update_Stage_Input_Tables(LC_RUNNING_STATUS, LC_SYSTEM_UPDATED_STAGE, FALSE) THEN
3483 RAISE e_unexpected_error;
3484 END IF;
3485 COMMIT;
3486 END IF;
3487
3488 -- Coloring process
3489 -- We have in the array h_base_tables_to_color the base tables to take to identify the indicators to color
3490 -- Initialize the array h_color_indicators with the system indicators that are affected by the base tables
3491 -- in the array h_base_tables_to_color
3492 IF NOT Get_Indicators_To_Color(h_base_tables_to_color,
3493 h_num_base_tables_to_color,
3494 h_color_indicators,
3495 h_num_color_indicators) THEN
3496 RAISE e_unexpected_error;
3497 END IF;
3498
3499 IF h_num_color_indicators > 0 THEN
3500 -- LOCKING: Lock temp tables for coloring
3501 IF NOT BSC_UPDATE_LOCK.Lock_Temp_Tables('COLOR') THEN
3502 RAISE e_could_not_get_lock;
3503 END IF;
3504
3505 -- LOCKING: call the autonomous transaction function
3506 IF NOT BSC_UPDATE_COLOR.Create_Temp_Tab_Tables_AT() THEN
3507 RAISE e_unexpected_error;
3508 END IF;
3509
3510 --LOCKING: Commit to release locks
3511 COMMIT;
3512
3513 FOR h_i IN 1 .. h_num_color_indicators LOOP
3514 -- BSC-BIS-DIMENSIONS: Starting from this implementation we can run Loader even if there
3515 -- are indicators in prototype. We need to check and only color indicators in production mode.
3516 -- Additionally if we are runnign in KPI_MODE we need to color only indicators
3517 -- that belong to the array g_indicators.
3518 h_calc_color := FALSE;
3519 IF BSC_UPDATE_UTIL.Is_Kpi_In_Production(h_color_indicators(h_i)) THEN
3520 IF g_kpi_mode THEN
3521 IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Number(h_color_indicators(h_i),
3522 g_indicators,
3523 g_num_indicators) THEN
3524 h_calc_color := TRUE;
3525 END IF;
3526 ELSE
3527 h_calc_color := TRUE;
3528 END IF;
3529 END IF;
3530
3531 IF h_calc_color THEN
3532 BSC_UPDATE_LOG.Write_Line_log(BSC_UPDATE_UTIL.Get_Message('BSC_COLOR_CALC')||
3533 ' '||h_color_indicators(h_i),
3534 BSC_UPDATE_LOG.OUTPUT);
3535
3536 --LOCKING: Calculate indicator period here
3537 IF NOT BSC_UPDATE_LOCK.Lock_Period_Indicator(h_color_indicators(h_i)) THEN
3538 RAISE e_could_not_get_lock;
3539 END IF;
3540
3541 IF NOT Update_Indicator_Period(h_color_indicators(h_i)) THEN
3542 RAISE e_unexpected_error;
3543 END IF;
3544
3545 -- Update Prototype Flag for underlying KPI Measures for the Objective to 7.
3546 /* Not required since the prototype flag of KPI is not checked for inside Color_Indicator
3547 IF NOT Update_Kpis_Prototype_Flag(h_color_indicators(h_i)) THEN
3548 RAISE e_unexpected_error;
3549 END IF;*/
3550
3551 --LOCKING: commit to release lock
3552 COMMIT;
3553
3554 --LOCKING: Lock indicator color
3555 IF NOT BSC_UPDATE_LOCK.Lock_Color_Indicator(h_color_indicators(h_i)) THEN
3556 RAISE e_could_not_get_lock;
3557 END IF;
3558
3559 -- LOCKING: Call the autonomous transaction
3560 IF NOT BSC_UPDATE_COLOR.Color_Indicator_AT(h_color_indicators(h_i)) THEN
3561 RAISE e_unexpected_error;
3562 END IF;
3563
3564 -- LOCKING: Commit to release the locks
3565 COMMIT;
3566
3567 h_message := BSC_UPDATE_UTIL.Get_Message('BSC_COLOR_CALC_COMPLETED');
3568 h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'INDICATOR', TO_CHAR(h_color_indicators(h_i)));
3569 BSC_UPDATE_LOG.Write_Line_log(h_message,
3570 BSC_UPDATE_LOG.OUTPUT);
3571
3572 --LOCKING: Lock the update period of the indicator
3573 IF NOT BSC_UPDATE_LOCK.Lock_Period_Indicator(h_color_indicators(h_i)) THEN
3574 RAISE e_could_not_get_lock;
3575 END IF;
3576
3577 -- LOCKING: review not commit between this point and the commit to release the locks
3578
3579 -- Update the name of period of indicator in BSC_KPI_DEFAULTS_TL table
3580 IF NOT BSC_UPDATE_UTIL.Update_Kpi_Period_Name(h_color_indicators(h_i)) THEN
3581 RAISE e_unexpected_error;
3582 END IF;
3583
3584 -- Update date of indicator
3585 UPDATE bsc_kpi_defaults_b SET last_update_date = SYSDATE
3586 WHERE indicator = h_color_indicators(h_i);
3587
3588 -- Update kpi time stamp
3589 BSC_UPDATE_UTIL.Update_Kpi_Time_Stamp(h_color_indicators(h_i));
3590
3591 -- Update Tabs time stamp
3592 BSC_UPDATE_UTIL.Update_Kpi_Tab_Time_Stamp(h_color_indicators(h_i));
3593
3594 --LOCKING: commit to release locks
3595 COMMIT;
3596
3597 -- BSC-BIS-DIMENSIONS: Since we can run on indicators with prototype flag 6 or 7
3598 -- we need to update the indicator to prototype flag 0
3599 --LOCKING: lock the prototype flag of the indicator
3600 IF NOT BSC_UPDATE_LOCK.Lock_Prototype_Indicator(h_color_indicators(h_i)) THEN
3601 RAISE e_could_not_get_lock;
3602 END IF;
3603
3604 UPDATE bsc_kpis_b
3605 SET prototype_flag = 0, last_updated_by = BSC_APPS.fnd_global_user_id, last_update_date = SYSDATE
3606 WHERE indicator = h_color_indicators(h_i) AND prototype_flag IN (6, 7);
3607
3608 -- Color By KPI: Mark KPIs for color done
3609 UPDATE bsc_kpi_analysis_measures_b
3610 SET prototype_flag = 0
3611 WHERE indicator = h_color_indicators(h_i) AND prototype_flag = 7;
3612
3613 --LOCKING: commit to release the lock
3614 COMMIT;
3615
3616 END IF;
3617
3618 END LOOP;
3619 END IF;
3620
3621 -- Update the date of update process
3622 --LOCKING: Lock date of update process
3623 IF NOT BSC_UPDATE_LOCK.Lock_Update_Date THEN
3624 RAISE e_could_not_get_lock;
3625 END IF;
3626
3627 IF NOT BSC_UPDATE_UTIL.Write_Init_Variable_Value('UPDATE_DATE', TO_CHAR(SYSDATE, 'DD/MM/YYYY')) THEN
3628 RAISE e_unexpected_error;
3629 END IF;
3630
3631 --LOCKING: commit to release lock
3632 COMMIT;
3633
3634 IF x_start_from = 0 THEN
3635 IF NOT Update_Stage_Input_Tables(LC_RUNNING_STATUS, LC_COMPLETED_STAGE, FALSE) THEN
3636 RAISE e_unexpected_error;
3637 END IF;
3638 COMMIT;
3639
3640 IF NOT Update_Status_All_Input_Tables(LC_RUNNING_STATUS, LC_COMPLETED_STATUS, NULL) THEN
3641 RAISE e_unexpected_error;
3642 END IF;
3643 COMMIT;
3644 END IF;
3645
3646 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_DATATABLE_UPDATED'), BSC_UPDATE_LOG.OUTPUT);
3647
3648 RETURN TRUE;
3649 EXCEPTION
3650 WHEN e_unexpected_error THEN
3651 ROLLBACK;
3652 BSC_MESSAGE.Add (x_message => BSC_UPDATE_UTIL.Get_Message('BSC_DATATABLE_UPDATE_FAILED'),
3653 x_source => 'BSC_UPDATE.Process_Input_Tables');
3654 IF x_start_from = 0 THEN
3655 h_b := Update_Status_All_Input_Tables(LC_RUNNING_STATUS, LC_ERROR_STATUS, LC_PROGRAM_ERR);
3656 COMMIT;
3657 END IF;
3658
3659 RETURN FALSE;
3660
3661 --LOCKING
3662 WHEN e_could_not_get_lock THEN
3663 ROLLBACK;
3664 BSC_MESSAGE.Add (x_message => 'Loader could not get the required locks to continue.',
3665 x_source => 'BSC_UPDATE.Process_Input_Tables');
3666 IF x_start_from = 0 THEN
3667 h_b := Update_Status_All_Input_Tables(LC_RUNNING_STATUS, LC_ERROR_STATUS, LC_PROGRAM_ERR);
3668 COMMIT;
3669 END IF;
3670
3671 RETURN FALSE;
3672
3673 WHEN OTHERS THEN
3674 ROLLBACK;
3675 BSC_MESSAGE.Add (x_message => SQLERRM,
3676 x_source => 'BSC_UPDATE.Process_Input_Tables');
3677
3678 IF x_start_from = 0 THEN
3679 h_b := Update_Status_All_Input_Tables(LC_RUNNING_STATUS, LC_ERROR_STATUS, LC_PROGRAM_ERR);
3680 COMMIT;
3681 END IF;
3682
3683 RETURN FALSE;
3684 END Process_Input_Tables;
3685
3686
3687 --LOCKING: new function
3688 /*===========================================================================+
3689 | FUNCTION Process_Input_Tables_AT
3690 +============================================================================*/
3691 FUNCTION Process_Input_Tables_AT(
3692 x_input_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3693 x_num_input_tables IN NUMBER,
3694 x_start_from IN NUMBER
3695 ) RETURN BOOLEAN IS
3696 PRAGMA AUTONOMOUS_TRANSACTION;
3697 h_b BOOLEAN;
3698 BEGIN
3699 h_b := Process_Input_Tables(x_input_tables, x_num_input_tables, x_start_from);
3700 commit; -- all autonomous transaction needs to commit
3701 RETURN h_b;
3702 END Process_Input_Tables_AT;
3703
3704
3705 /*===========================================================================+
3706 | FUNCTION Refresh_System_MVs
3707 +============================================================================*/
3708 FUNCTION Refresh_System_MVs(
3709 p_base_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3710 p_num_base_tables IN NUMBER
3711 ) RETURN BOOLEAN IS
3712
3713 e_unexpected_error EXCEPTION;
3714 --LOCKING
3715 e_could_not_get_lock EXCEPTION;
3716
3717 h_system_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
3718 h_num_system_tables NUMBER;
3719
3720 h_calculated_sys_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
3721 h_num_calculated_sys_tables NUMBER;
3722
3723 h_refreshed_mvs BSC_UPDATE_UTIL.t_array_of_varchar2;
3724 h_num_refreshed_mvs NUMBER;
3725
3726 h_i NUMBER;
3727 h_b BOOLEAN;
3728
3729 mv_name VARCHAR2(50);
3730
3731 e_error_refresh EXCEPTION;
3732 e_error_refresh_zero EXCEPTION;
3733 h_error_refresh VARCHAR2(2000);
3734
3735 h_kpis BSC_UPDATE_UTIL.t_array_kpis;
3736 h_num_kpis NUMBER;
3737 h_calc_summary_table BOOLEAN;
3738
3739 BEGIN
3740 h_num_system_tables := 0;
3741 h_num_calculated_sys_tables := 0;
3742 h_num_refreshed_mvs := 0;
3743
3744
3745 -- Initialize the array h_system_tables with the system tables that are affected by the base tables
3746 -- in the array p_base_tables
3747
3748 IF NOT Insert_Affected_Tables(p_base_tables, p_num_base_tables, h_system_tables, h_num_system_tables) THEN
3749 RAISE e_unexpected_error;
3750 END IF;
3751
3752 -- Until all MVs have been refreshed
3753 WHILE h_num_calculated_sys_tables <> h_num_system_tables LOOP
3754 FOR h_i IN 1 .. h_num_system_tables LOOP
3755 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_system_tables(h_i),
3756 h_calculated_sys_tables,
3757 h_num_calculated_sys_tables) THEN
3758 -- The table has not been calculated yet
3759
3760 -- Check if the table can be calculated
3761 h_b := Can_Calculate_Sys_Table(h_system_tables(h_i),
3762 h_calculated_sys_tables,
3763 h_num_calculated_sys_tables,
3764 h_system_tables,
3765 h_num_system_tables);
3766
3767 IF h_b IS NULL THEN
3768 RAISE e_unexpected_error;
3769 END IF;
3770
3771 IF h_b THEN
3772 -- The table can be calculated because all the origin tables for that table
3773 -- have already been calculated
3774
3775 -- Fix bug#5023588 Only calculate the MV if it is used by an indicator in production mode
3776 -- If the table is not used directly by a Kpi then this fuctions returns 0;
3777 h_num_kpis := BSC_UPDATE_UTIL.Get_Kpis_Using_Table(h_system_tables(h_i), h_kpis);
3778 IF h_num_kpis > 0 THEN
3779 -- If at least one indicator using the table is in production then we calculate the table.
3780 h_calc_summary_table := FALSE;
3781 FOR i IN 1..h_num_kpis LOOP
3782 IF h_kpis(i).prototype_flag IN (0,6,7) THEN
3783 h_calc_summary_table := TRUE;
3784 EXIT;
3785 END IF;
3786 END LOOP;
3787 IF h_calc_summary_table THEN
3788 -- Refresh the MV.
3789 -- Make sure to refresh the MV only one time. Remember that same MV
3790 -- has data for different periodicities.
3791 -- Note: The api to refresh the MV does not fail if the MV does not exists
3792 -- or if the MV is actually a normal view
3793 mv_name := BSC_BIA_WRAPPER.Get_Sum_Table_MV_Name(h_system_tables(h_i));
3794 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(mv_name,
3795 h_refreshed_mvs,
3796 h_num_refreshed_mvs) THEN
3797
3798 --LOCKING: Lock the objects required for the mv refresh
3799 --LOCKING: review that no commits between this point and the commit to
3800 -- to release the locks
3801 IF NOT BSC_UPDATE_LOCK.Lock_Refresh_MV(h_system_tables(h_i)) THEN
3802 RAISE e_could_not_get_lock;
3803 END IF;
3804
3805 --LOCKING: call the autonomous transaction function
3806 IF NOT BSC_BIA_WRAPPER.Refresh_Summary_MV_AT(mv_name, h_error_refresh) THEN
3807 RAISE e_error_refresh;
3808 END IF;
3809
3810 -- Also refresh the MV created for zero code (if it exists)
3811 --LOCKING: call the autonomous transaction function
3812 IF NOT BSC_UPDATE_SUM.Refresh_Zero_MVs_AT(h_system_tables(h_i),
3813 mv_name, h_error_refresh) THEN
3814 RAISE e_error_refresh_zero;
3815 END IF;
3816
3817 --LOCKING: commit to release locks
3818 COMMIT;
3819
3820 -- Add mv to array of refreshed mvs
3821 h_num_refreshed_mvs := h_num_refreshed_mvs + 1;
3822 h_refreshed_mvs(h_num_refreshed_mvs) := mv_name;
3823 END IF;
3824 END IF;
3825 END IF;
3826
3827 -- Add table to array of calculated tables
3828 h_num_calculated_sys_tables := h_num_calculated_sys_tables + 1;
3829 h_calculated_sys_tables(h_num_calculated_sys_tables) := h_system_tables(h_i);
3830
3831 END IF;
3832 END IF;
3833 END LOOP;
3834 END LOOP;
3835
3836 RETURN TRUE;
3837
3838 EXCEPTION
3839 --LOCKING
3840 WHEN e_could_not_get_lock THEN
3841 BSC_MESSAGE.Add(x_message => 'Loader could not get the required locks to continue.',
3842 x_source => 'BSC_UPDATE.Refresh_System_MVs');
3843 RETURN FALSE;
3844
3845 WHEN e_unexpected_error THEN
3846 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_DATATABLE_UPDATE_FAILED'),
3847 x_source => 'BSC_UPDATE.Refresh_System_MVs');
3848 RETURN FALSE;
3849
3850 WHEN e_error_refresh THEN
3851 BSC_MESSAGE.Add(x_message => 'BSC_BIA_WRAPPER.Refresh_Summary_MV '||mv_name||' '||h_error_refresh,
3852 x_source => 'BSC_UPDATE.Refresh_System_MVs');
3853 RETURN FALSE;
3854
3855 WHEN e_error_refresh_zero THEN
3856 BSC_MESSAGE.Add(x_message => 'BSC_UPDATE_SUM.Refresh_Zero_MVs '||mv_name||' '||h_error_refresh,
3857 x_source => 'BSC_UPDATE.Refresh_System_MVs');
3858 RETURN FALSE;
3859
3860 WHEN OTHERS THEN
3861 BSC_MESSAGE.Add(x_message => SQLERRM,
3862 x_source => 'BSC_UPDATE.Refresh_System_MVs');
3863 RETURN FALSE;
3864 END Refresh_System_MVs;
3865
3866
3867 --LOCKING: new fucntion: In migration we cannot use lock inside this function
3868 /*===========================================================================+
3869 | FUNCTION Refresh_System_MVs_Mig
3870 +============================================================================*/
3871 FUNCTION Refresh_System_MVs_Mig(
3872 p_base_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3873 p_num_base_tables IN NUMBER
3874 ) RETURN BOOLEAN IS
3875
3876 e_unexpected_error EXCEPTION;
3877
3878 h_system_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
3879 h_num_system_tables NUMBER;
3880
3881 h_calculated_sys_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
3882 h_num_calculated_sys_tables NUMBER;
3883
3884 h_refreshed_mvs BSC_UPDATE_UTIL.t_array_of_varchar2;
3885 h_num_refreshed_mvs NUMBER;
3886
3887 h_i NUMBER;
3888 h_b BOOLEAN;
3889
3890 mv_name VARCHAR2(50);
3891
3892 e_error_refresh EXCEPTION;
3893 e_error_refresh_zero EXCEPTION;
3894 h_error_refresh VARCHAR2(2000);
3895
3896 BEGIN
3897 h_num_system_tables := 0;
3898 h_num_calculated_sys_tables := 0;
3899 h_num_refreshed_mvs := 0;
3900
3901
3902 -- Initialize the array h_system_tables with the system tables that are affected by the base tables
3903 -- in the array p_base_tables
3904
3905 IF NOT Insert_Affected_Tables(p_base_tables, p_num_base_tables, h_system_tables, h_num_system_tables) THEN
3906 RAISE e_unexpected_error;
3907 END IF;
3908
3909 -- Until all MVs have been refreshed
3910 WHILE h_num_calculated_sys_tables <> h_num_system_tables LOOP
3911 FOR h_i IN 1 .. h_num_system_tables LOOP
3912 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_system_tables(h_i),
3913 h_calculated_sys_tables,
3914 h_num_calculated_sys_tables) THEN
3915 -- The table has not been calculated yet
3916
3917 -- Check if the table can be calculated
3918 h_b := Can_Calculate_Sys_Table(h_system_tables(h_i),
3919 h_calculated_sys_tables,
3920 h_num_calculated_sys_tables,
3921 h_system_tables,
3922 h_num_system_tables);
3923
3924 IF h_b IS NULL THEN
3925 RAISE e_unexpected_error;
3926 END IF;
3927
3928 IF h_b THEN
3929 -- The table can be calculated because all the origin tables for that table
3930 -- have already been calculated
3931
3932 -- Refresh the MV.
3933 -- Make sure to refresh the MV only one time. Remember that same MV
3934 -- has data for different periodicities.
3935 -- Note: The api to refresh the MV does not fail if the MV does not exists
3936 -- or if the MV is actually a normal view
3937 mv_name := BSC_BIA_WRAPPER.Get_Sum_Table_MV_Name(h_system_tables(h_i));
3938 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(mv_name,
3939 h_refreshed_mvs,
3940 h_num_refreshed_mvs) THEN
3941
3942 IF NOT BSC_BIA_WRAPPER.Refresh_Summary_MV(mv_name, h_error_refresh) THEN
3943 RAISE e_error_refresh;
3944 END IF;
3945
3946 -- Also refresh the MV created for zero code (if it exists)
3947 IF NOT BSC_UPDATE_SUM.Refresh_Zero_MVs(h_system_tables(h_i),
3948 mv_name, h_error_refresh) THEN
3949 RAISE e_error_refresh_zero;
3950 END IF;
3951
3952 commit;
3953
3954 -- Add mv to array of refreshed mvs
3955 h_num_refreshed_mvs := h_num_refreshed_mvs + 1;
3956 h_refreshed_mvs(h_num_refreshed_mvs) := mv_name;
3957 END IF;
3958
3959 -- Add table to array of calculated tables
3960 h_num_calculated_sys_tables := h_num_calculated_sys_tables + 1;
3961 h_calculated_sys_tables(h_num_calculated_sys_tables) := h_system_tables(h_i);
3962
3963 END IF;
3964 END IF;
3965 END LOOP;
3966 END LOOP;
3967
3968 RETURN TRUE;
3969
3970 EXCEPTION
3971 WHEN e_unexpected_error THEN
3972 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_DATATABLE_UPDATE_FAILED'),
3973 x_source => 'BSC_UPDATE.Refresh_System_MVs_Mig');
3974 RETURN FALSE;
3975
3976 WHEN e_error_refresh THEN
3977 BSC_MESSAGE.Add(x_message => 'BSC_BIA_WRAPPER.Refresh_Summary_MV '||mv_name||' '||h_error_refresh,
3978 x_source => 'BSC_UPDATE.Refresh_System_MVs_Mig');
3979 RETURN FALSE;
3980
3981 WHEN e_error_refresh_zero THEN
3982 BSC_MESSAGE.Add(x_message => 'BSC_UPDATE_SUM.Refresh_Zero_MVs '||mv_name||' '||h_error_refresh,
3983 x_source => 'BSC_UPDATE.Refresh_System_MVs_Mig');
3984 RETURN FALSE;
3985
3986 WHEN OTHERS THEN
3987 BSC_MESSAGE.Add(x_message => SQLERRM,
3988 x_source => 'BSC_UPDATE.Refresh_System_MVs_Mig');
3989 RETURN FALSE;
3990 END Refresh_System_MVs_Mig;
3991
3992
3993 /*===========================================================================+
3994 | PROCEDURE Run_Concurrent_Loader
3995 +============================================================================*/
3996 PROCEDURE Run_Concurrent_Loader (
3997 ERRBUF OUT NOCOPY VARCHAR2,
3998 RETCODE OUT NOCOPY VARCHAR2,
3999 x_process_id IN VARCHAR2,
4000 x_process_name IN VARCHAR2,
4001 x_parameter_1 IN VARCHAR2
4002 ) IS
4003
4004 e_system_lock EXCEPTION;
4005 e_update_error EXCEPTION;
4006 e_warning EXCEPTION;
4007 h_count NUMBER;
4008
4009 TYPE t_cursor IS REF CURSOR;
4010 h_cursor t_cursor;
4011 h_sql VARCHAR2(32000);
4012
4013 h_source VARCHAR2(200);
4014 h_sessionid NUMBER;
4015
4016 --LOCKING: new variables
4017 h_return_status VARCHAR2(10);
4018 h_msg_count NUMBER;
4019 h_msg_data VARCHAR2(4000);
4020
4021 BEGIN
4022 h_sessionid := USERENV('SESSIONID');
4023 -- Bug#4681065
4024 g_warning := FALSE;
4025
4026 -- Initialize BSC/APPS global variables
4027 BSC_APPS.Init_Bsc_Apps;
4028
4029 -- LOCKING: call thi api
4030 FND_MSG_PUB.Initialize;
4031
4032 -- Initializes the error message stack
4033 g_debug_flag := 'NO';
4034 BSC_MESSAGE.Init(g_debug_flag);
4035
4036 -- Check system lock
4037 --LOCKING: Do not use BSC_SECURITY.
4038 --BSC_SECURITY.Check_System_Lock(-101, NULL, BSC_APPS.apps_user_id);
4039 --h_source := 'BSC_SECURITY.CHECK_SYSTEM_LOCK';
4040 --SELECT count(*)
4041 --INTO h_count
4042 --FROM bsc_message_logs
4043 --WHERE type = 0 AND UPPER(source) = h_source AND last_update_login = h_sessionid;
4044 --IF h_count > 0 THEN
4045 -- RAISE e_system_lock;
4046 --END IF;
4047 --LOCKING: Call this api for backward compatibility
4048 BSC_LOCKS_PUB.Get_System_Lock (
4049 p_program_id => -101,
4050 p_user_id => BSC_APPS.apps_user_id,
4051 p_icx_session_id => null,
4052 x_return_status => h_return_status,
4053 x_msg_count => h_msg_count,
4054 x_msg_data => h_msg_data
4055 );
4056 IF h_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4057 RAISE e_system_lock;
4058 END IF;
4059
4060 Execute_Update_Process(TO_NUMBER(x_process_id), x_process_name, x_parameter_1);
4061
4062 h_source := 'BSC_UPDATE.EXECUTE_UPDATE_PROCESS';
4063
4064 SELECT count(*)
4065 INTO h_count
4066 FROM bsc_message_logs
4067 WHERE type = 0 AND UPPER(source) = h_source AND last_update_login = h_sessionid;
4068
4069 IF h_count > 0 THEN
4070 RAISE e_update_error;
4071 END IF;
4072
4073 SELECT count(*)
4074 INTO h_count
4075 FROM bsc_db_loader_control
4076 WHERE process_id = g_process_id AND status IN (LC_ERROR_STATUS, LC_NO_DATA_STATUS);
4077
4078 IF h_count > 0 THEN
4079 RAISE e_warning;
4080 END IF;
4081
4082 -- Bug#4681065
4083 IF g_warning THEN
4084 RAISE e_warning;
4085 END IF;
4086
4087 -- LOCKING
4088 BSC_LOCKS_PUB.Remove_System_Lock;
4089
4090 EXCEPTION
4091 WHEN e_system_lock THEN
4092 --LOCKING: h_msg_data has the error message
4093 BSC_MESSAGE.Add(
4094 X_Message => h_msg_data,
4095 X_Source => 'BSC_UPDATE.Run_Concurrent_Loader_Apps',
4096 X_Mode => 'I'
4097 );
4098
4099 BSC_UPDATE_LOG.Write_Errors_To_Log;
4100
4101 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4102 BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4103 ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4104 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4105
4106 -- LOCKING
4107 BSC_LOCKS_PUB.Remove_System_Lock;
4108
4109 ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
4110 RETCODE := 2; -- Request completed with errors
4111
4112 WHEN e_update_error THEN
4113 -- LOCKING
4114 BSC_LOCKS_PUB.Remove_System_Lock;
4115
4116 ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
4117 RETCODE := 2; -- Request completed with errors
4118
4119 WHEN e_warning THEN
4120 -- LOCKING
4121 BSC_LOCKS_PUB.Remove_System_Lock;
4122
4123 ERRBUF := '';
4124 RETCODE := 1; -- Request completed with warning
4125
4126 WHEN OTHERS THEN
4127 BSC_MESSAGE.Add(x_message => SQLERRM,
4128 x_source => 'BSC_UPDATE.Run_Concurrent_Loader_Apps',
4129 x_mode => 'I');
4130 COMMIT;
4131
4132 BSC_UPDATE_LOG.Write_Errors_To_Log;
4133
4134 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4135 BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4136 ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4137 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4138
4139 -- LOCKING
4140 BSC_LOCKS_PUB.Remove_System_Lock;
4141
4142 ERRBUF := SQLERRM;
4143 RETCODE := 2; -- Request completed with errors
4144
4145 END Run_Concurrent_Loader;
4146
4147
4148 /*===========================================================================+
4149 | PROCEDURE Run_Concurrent_Loader_Apps
4150 +============================================================================*/
4151 PROCEDURE Run_Concurrent_Loader_Apps (
4152 ERRBUF OUT NOCOPY VARCHAR2,
4153 RETCODE OUT NOCOPY VARCHAR2,
4154 x_process_id IN VARCHAR2
4155 )IS
4156 Begin
4157 Run_Concurrent_Loader_Apps(ERRBUF,RETCODE,x_process_id,'N');
4158 EXCEPTION when others then
4159 BSC_MESSAGE.Add(x_message => SQLERRM,
4160 x_source => 'BSC_UPDATE.Run_Concurrent_Loader_Apps',
4161 x_mode => 'I');
4162 COMMIT;
4163
4164 BSC_UPDATE_LOG.Write_Errors_To_Log;
4165
4166 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4167 BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4168 ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4169 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4170
4171 ERRBUF := SQLERRM;
4172 RETCODE := 2; -- Request completed with errors
4173 End;
4174
4175 PROCEDURE Run_Concurrent_Loader_Apps (
4176 ERRBUF OUT NOCOPY VARCHAR2,
4177 RETCODE OUT NOCOPY VARCHAR2,
4178 x_process_id IN VARCHAR2,
4179 x_load_dim_affected_indicators varchar2
4180 ) IS
4181
4182 e_system_lock EXCEPTION;
4183 e_update_error EXCEPTION;
4184 e_warning EXCEPTION;
4185 h_count NUMBER;
4186
4187 TYPE t_cursor IS REF CURSOR;
4188 h_cursor t_cursor;
4189 h_sql VARCHAR2(32000);
4190
4191 h_source VARCHAR2(200);
4192 h_sessionid NUMBER;
4193 --
4194 h_input_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
4195 h_num_input_tables NUMBER;
4196 h_indicators BSC_UPDATE_UTIL.t_array_of_number;
4197 h_num_indicators NUMBER;
4198 h_indicator_string varchar2(32000);
4199 --
4200
4201 --LOCKING: new variables
4202 h_return_status VARCHAR2(10);
4203 h_msg_count NUMBER;
4204 h_msg_data VARCHAR2(4000);
4205
4206 BEGIN
4207 h_sessionid := USERENV('SESSIONID');
4208 -- Bug#4681065
4209 g_warning := FALSE;
4210
4211 -- Initialize BSC/APPS global variables
4212 BSC_APPS.Init_Bsc_Apps;
4213
4214 -- LOCKING: call thi api
4215 FND_MSG_PUB.Initialize;
4216
4217 -- Initializes the error message stack
4218 g_debug_flag := 'NO';
4219 BSC_MESSAGE.Init(g_debug_flag);
4220
4221 -- Check system lock
4222 --LOCKING: Do not use BSC_SECURITY.
4223 --BSC_SECURITY.Check_System_Lock(-101, NULL, BSC_APPS.apps_user_id);
4224 --h_source := 'BSC_SECURITY.CHECK_SYSTEM_LOCK';
4225 --SELECT count(*)
4226 --INTO h_count
4227 --FROM bsc_message_logs
4228 --WHERE type = 0 AND UPPER(source) = h_source AND last_update_login = h_sessionid;
4229 --IF h_count > 0 THEN
4230 -- RAISE e_system_lock;
4231 --END IF;
4232 --LOCKING: Call this api for backward compatibility
4233 BSC_LOCKS_PUB.Get_System_Lock (
4234 p_program_id => -101,
4235 p_user_id => BSC_APPS.apps_user_id,
4236 p_icx_session_id => null,
4237 x_return_status => h_return_status,
4238 x_msg_count => h_msg_count,
4239 x_msg_data => h_msg_data
4240 );
4241 IF h_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4242 RAISE e_system_lock;
4243 END IF;
4244
4245 if x_load_dim_affected_indicators is not null and x_load_dim_affected_indicators='Y' then
4246 g_process_id := Get_Process_Id(x_process_id, PC_LOADER_PROCESS);
4247 g_process_name := PC_LOADER_PROCESS;
4248
4249 IF NOT Get_Process_Input_Tables(h_input_tables, h_num_input_tables, LC_PENDING_STATUS) THEN
4250 RAISE e_update_error;
4251 END IF;
4252
4253 if h_num_input_tables>0 then
4254 h_num_indicators:=0;
4255 if get_kpi_for_input_tables(h_input_tables,h_num_input_tables,h_indicators,h_num_indicators)=false then
4256 raise e_update_error;
4257 end if;
4258 if h_num_indicators>0 then
4259 h_indicator_string:=null;
4260 for i in 1..h_num_indicators loop
4261 h_indicator_string:=h_indicator_string||h_indicators(i)||',';
4262 end loop;
4263 h_indicator_string:=substr(h_indicator_string,1,length(h_indicator_string)-1);
4264 --load the dimensions first
4265 Load_Indicators_Dims (ERRBUF,RETCODE,h_indicator_string,'N');
4266 if RETCODE='2' or RETCODE=2 then
4267 raise e_update_error;
4268 end if;
4269 --LOCKING: lock again since Load_Indicators_Dims removed the lock
4270 BSC_LOCKS_PUB.Get_System_Lock (
4271 p_program_id => -101,
4272 p_user_id => BSC_APPS.apps_user_id,
4273 p_icx_session_id => null,
4274 x_return_status => h_return_status,
4275 x_msg_count => h_msg_count,
4276 x_msg_data => h_msg_data
4277 );
4278 IF h_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4279 RAISE e_system_lock;
4280 END IF;
4281 end if;
4282 end if;
4283 end if;
4284
4285 -- clean bsc_message_logs
4286 BSC_MESSAGE.Clean;
4287
4288 -- Run loader
4289 Execute_Update_Process(TO_NUMBER(x_process_id), PC_LOADER_PROCESS, NULL);
4290
4291 h_source := 'BSC_UPDATE.EXECUTE_UPDATE_PROCESS';
4292
4293 /*
4294 h_sql := 'SELECT count(*)'||
4295 ' FROM bsc_message_logs'||
4296 ' WHERE type = :1'||
4297 ' AND UPPER(source) = :2 AND last_update_login = :3';
4298 OPEN h_cursor FOR h_sql USING 0, h_source, h_sessionid;
4299 FETCH h_cursor INTO h_count;
4300 CLOSE h_cursor;
4301 */
4302 SELECT count(*)
4303 INTO h_count
4304 FROM bsc_message_logs
4305 WHERE type = 0 AND UPPER(source) = h_source AND last_update_login = h_sessionid;
4306
4307 IF h_count > 0 THEN
4308 RAISE e_update_error;
4309 END IF;
4310
4311 /*
4312 h_sql := 'SELECT count(*)'||
4313 ' FROM bsc_db_loader_control'||
4314 ' WHERE process_id = :1 AND status IN (:2, :3)';
4315 OPEN h_cursor FOR h_sql USING g_process_id, LC_ERROR_STATUS, LC_NO_DATA_STATUS;
4316 FETCH h_cursor INTO h_count;
4317 CLOSE h_cursor;
4318 */
4319 SELECT count(*)
4320 INTO h_count
4321 FROM bsc_db_loader_control
4322 WHERE process_id = g_process_id AND status IN (LC_ERROR_STATUS, LC_NO_DATA_STATUS);
4323
4324 IF h_count > 0 THEN
4325 RAISE e_warning;
4326 END IF;
4327
4328 -- Bug#4681065
4329 IF g_warning THEN
4330 RAISE e_warning;
4331 END IF;
4332
4333 -- LOCKING
4334 BSC_LOCKS_PUB.Remove_System_Lock;
4335
4336 EXCEPTION
4337 WHEN e_system_lock THEN
4338 --LOCKING: h_msg_data has the error message
4339 BSC_MESSAGE.Add(
4340 X_Message => h_msg_data,
4341 X_Source => 'BSC_UPDATE.Run_Concurrent_Loader_Apps',
4342 X_Mode => 'I'
4343 );
4344
4345 BSC_UPDATE_LOG.Write_Errors_To_Log;
4346
4347 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4348 BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4349 ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4350 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4351
4352 -- LOCKING
4353 BSC_LOCKS_PUB.Remove_System_Lock;
4354
4355 ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
4356 RETCODE := 2; -- Request completed with errors
4357
4358 WHEN e_update_error THEN
4359 -- LOCKING
4360 BSC_LOCKS_PUB.Remove_System_Lock;
4361
4362 ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
4363 RETCODE := 2; -- Request completed with errors
4364
4365 WHEN e_warning THEN
4366 -- LOCKING
4367 BSC_LOCKS_PUB.Remove_System_Lock;
4368
4369 ERRBUF := '';
4370 RETCODE := 1; -- Request completed with warning
4371
4372 WHEN OTHERS THEN
4373 BSC_MESSAGE.Add(x_message => SQLERRM,
4374 x_source => 'BSC_UPDATE.Run_Concurrent_Loader_Apps',
4375 x_mode => 'I');
4376 COMMIT;
4377
4378 BSC_UPDATE_LOG.Write_Errors_To_Log;
4379
4380 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4381 BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4382 ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4383 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4384
4385 -- LOCKING
4386 BSC_LOCKS_PUB.Remove_System_Lock;
4387
4388 ERRBUF := SQLERRM;
4389 RETCODE := 2; -- Request completed with errors
4390
4391 END Run_Concurrent_Loader_Apps;
4392
4393
4394 /*===========================================================================+
4395 | PROCEDURE Run_Concurrent_Loader_Dim_Apps
4396 +============================================================================*/
4397 PROCEDURE Run_Concurrent_Loader_Dim_Apps (
4398 ERRBUF OUT NOCOPY VARCHAR2,
4399 RETCODE OUT NOCOPY VARCHAR2,
4400 x_process_id IN VARCHAR2
4401 ) IS
4402
4403 e_system_lock EXCEPTION;
4404 e_update_error EXCEPTION;
4405 e_warning EXCEPTION;
4406 h_count NUMBER;
4407
4408 TYPE t_cursor IS REF CURSOR;
4409 h_cursor t_cursor;
4410 h_sql VARCHAR2(32000);
4411
4412 h_source VARCHAR2(200);
4413 h_sessionid NUMBER;
4414
4415 --LOCKING: new variables
4416 h_return_status VARCHAR2(10);
4417 h_msg_count NUMBER;
4418 h_msg_data VARCHAR2(4000);
4419
4420 BEGIN
4421
4422 h_sessionid := USERENV('SESSIONID');
4423 -- Bug#4681065
4424 g_warning := FALSE;
4425
4426 -- Initialize BSC/APPS global variables
4427 BSC_APPS.Init_Bsc_Apps;
4428
4429 -- LOCKING: call thi api
4430 FND_MSG_PUB.Initialize;
4431
4432 -- Initializes the error message stack
4433 g_debug_flag := 'NO';
4434 BSC_MESSAGE.Init(g_debug_flag);
4435
4436 -- Check system lock
4437 --LOCKING: Do not use BSC_SECURITY.
4438 --BSC_SECURITY.Check_System_Lock(-101, NULL, BSC_APPS.apps_user_id);
4439 --h_source := 'BSC_SECURITY.CHECK_SYSTEM_LOCK';
4440 --SELECT count(*)
4441 --INTO h_count
4442 --FROM bsc_message_logs
4443 --WHERE type = 0 AND UPPER(source) = h_source AND last_update_login = h_sessionid;
4444 --IF h_count > 0 THEN
4445 -- RAISE e_system_lock;
4446 --END IF;
4447 --LOCKING: Call this api for backward compatibility
4448 BSC_LOCKS_PUB.Get_System_Lock (
4449 p_program_id => -101,
4450 p_user_id => BSC_APPS.apps_user_id,
4451 p_icx_session_id => null,
4452 x_return_status => h_return_status,
4453 x_msg_count => h_msg_count,
4454 x_msg_data => h_msg_data
4455 );
4456 IF h_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4457 RAISE e_system_lock;
4458 END IF;
4459
4460 -- Run loader
4461 Execute_Update_Process(TO_NUMBER(x_process_id), PC_LOAD_DIMENSIONS, NULL);
4462
4463 h_source := 'BSC_UPDATE.EXECUTE_UPDATE_PROCESS';
4464
4465 /*
4466 h_sql := 'SELECT count(*)'||
4467 ' FROM bsc_message_logs'||
4468 ' WHERE type = :1'||
4469 ' AND UPPER(source) = :2 AND last_update_login = :3';
4470 OPEN h_cursor FOR h_sql USING 0, h_source, h_sessionid;
4471 FETCH h_cursor INTO h_count;
4472 CLOSE h_cursor;
4473 */
4474 SELECT count(*)
4475 INTO h_count
4476 FROM bsc_message_logs
4477 WHERE type = 0 AND UPPER(source) = h_source AND last_update_login = h_sessionid;
4478
4479 IF h_count > 0 THEN
4480 RAISE e_update_error;
4481 END IF;
4482
4483 /*
4484 h_sql := 'SELECT count(*)'||
4485 ' FROM bsc_db_loader_control'||
4486 ' WHERE process_id = :1 AND status IN (:2, :3)';
4487 OPEN h_cursor FOR h_sql USING g_process_id, LC_ERROR_STATUS, LC_NO_DATA_STATUS;
4488 FETCH h_cursor INTO h_count;
4489 CLOSE h_cursor;
4490 */
4491 SELECT count(*)
4492 INTO h_count
4493 FROM bsc_db_loader_control
4494 WHERE process_id = g_process_id AND status IN (LC_ERROR_STATUS, LC_NO_DATA_STATUS);
4495
4496 IF h_count > 0 THEN
4497 RAISE e_warning;
4498 END IF;
4499
4500 -- Bug#4681065
4501 IF g_warning THEN
4502 RAISE e_warning;
4503 END IF;
4504
4505 -- LOCKING
4506 BSC_LOCKS_PUB.Remove_System_Lock;
4507
4508 EXCEPTION
4509 WHEN e_system_lock THEN
4510 --LOCKING: h_msg_data has the error message
4511 BSC_MESSAGE.Add(
4512 X_Message => h_msg_data,
4513 X_Source => 'BSC_UPDATE.Run_Concurrent_Loader_Apps',
4514 X_Mode => 'I'
4515 );
4516
4517 BSC_UPDATE_LOG.Write_Errors_To_Log;
4518
4519 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4520 BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4521 ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4522 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4523
4524 -- LOCKING
4525 BSC_LOCKS_PUB.Remove_System_Lock;
4526
4527 ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
4528 RETCODE := 2; -- Request completed with errors
4529
4530 WHEN e_update_error THEN
4531 -- LOCKING
4532 BSC_LOCKS_PUB.Remove_System_Lock;
4533
4534 ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
4535 RETCODE := 2; -- Request completed with errors
4536
4537 WHEN e_warning THEN
4538 -- LOCKING
4539 BSC_LOCKS_PUB.Remove_System_Lock;
4540
4541 ERRBUF := '';
4542 RETCODE := 1; -- Request completed with warning
4543
4544 WHEN OTHERS THEN
4545 BSC_MESSAGE.Add(x_message => SQLERRM,
4546 x_source => 'BSC_UPDATE.Run_Concurrent_Loader_Dim_Apps',
4547 x_mode => 'I');
4548 COMMIT;
4549
4550 BSC_UPDATE_LOG.Write_Errors_To_Log;
4551
4552 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4553 BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4554 ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4555 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4556
4557 -- LOCKING
4558 BSC_LOCKS_PUB.Remove_System_Lock;
4559
4560 ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
4561 RETCODE := 2; -- Request completed with errors
4562
4563 END Run_Concurrent_Loader_Dim_Apps;
4564
4565 /*
4566 This is the new procedure to change current year
4567 This is same as calling Submit Request to Load Input Tables (VB) (Flag = Y)
4568 This is for new OA UI. this api in contrast to Submit Request to Load Input Tables (VB) (Flag = Y)
4569 will acquire lock. Submit Request to Load Input Tables (VB) (Flag = Y) is launched from VB
4570 where the lock is already there with VB
4571 */
4572 PROCEDURE Run_change_current_year (
4573 ERRBUF OUT NOCOPY VARCHAR2,
4574 RETCODE OUT NOCOPY VARCHAR2,
4575 x_process_id IN VARCHAR2,
4576 x_calendars IN VARCHAR2
4577 )IS
4578 e_system_lock EXCEPTION;
4579 e_update_error EXCEPTION;
4580 h_count NUMBER;
4581
4582 TYPE t_cursor IS REF CURSOR;
4583 h_cursor t_cursor;
4584 h_sql VARCHAR2(32000);
4585
4586 h_source VARCHAR2(200);
4587
4588 --LOCKING: new variables
4589 h_return_status VARCHAR2(10);
4590 h_msg_count NUMBER;
4591 h_msg_data VARCHAR2(4000);
4592
4593 Begin
4594 -- Initialize BSC/APPS global variables
4595 BSC_APPS.Init_Bsc_Apps;
4596
4597 -- LOCKING: call thi api
4598 FND_MSG_PUB.Initialize;
4599
4600 -- Initializes the error message stack
4601 g_debug_flag := 'NO';
4602 BSC_MESSAGE.Init(g_debug_flag);
4603
4604 -- Initializes g_session_id, g_user_id and g_schema_name
4605 IF NOT Init_Env_Values THEN
4606 RAISE e_update_error;
4607 END IF;
4608
4609 IF x_calendars IS NULL THEN
4610 -- No calendars to be processed.
4611 RETURN;
4612 END IF;
4613
4614 -- Check system lock
4615 --LOCKING: Do not use BSC_SECURITY.
4616 --BSC_SECURITY.Check_System_Lock(-101, NULL, BSC_APPS.apps_user_id);
4617 --h_source := 'BSC_SECURITY.CHECK_SYSTEM_LOCK';
4618 --SELECT count(*)
4619 --INTO h_count
4620 --FROM bsc_message_logs
4621 --WHERE type = 0 AND UPPER(source) = h_source AND last_update_login = g_session_id;
4622 --IF h_count > 0 THEN
4623 -- RAISE e_system_lock;
4624 --END IF;
4625 --LOCKING: Call this api for backward compatibility
4626 BSC_LOCKS_PUB.Get_System_Lock (
4627 p_program_id => -101,
4628 p_user_id => BSC_APPS.apps_user_id,
4629 p_icx_session_id => null,
4630 x_return_status => h_return_status,
4631 x_msg_count => h_msg_count,
4632 x_msg_data => h_msg_data
4633 );
4634 IF h_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4635 RAISE e_system_lock;
4636 END IF;
4637
4638 -- Run loader
4639 Execute_Update_Process(TO_NUMBER(x_process_id), PC_YEAR_CHANGE_PROCESS, x_calendars);
4640
4641 h_source := 'BSC_UPDATE.EXECUTE_UPDATE_PROCESS';
4642
4643 SELECT count(*)
4644 INTO h_count
4645 FROM bsc_message_logs
4646 WHERE type = 0 AND UPPER(source) = h_source AND last_update_login = g_session_id;
4647
4648 IF h_count > 0 THEN
4649 RAISE e_update_error;
4650 END IF;
4651
4652 -- LOCKING
4653 BSC_LOCKS_PUB.Remove_System_Lock;
4654
4655 EXCEPTION
4656 WHEN e_system_lock THEN
4657 --LOCKING: h_msg_data has the error message
4658 BSC_MESSAGE.Add(
4659 X_Message => h_msg_data,
4660 X_Source => 'BSC_UPDATE.Run_Concurrent_Loader_Apps',
4661 X_Mode => 'I'
4662 );
4663
4664 BSC_UPDATE_LOG.Write_Errors_To_Log;
4665
4666 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4667 BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4668 ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4669 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4670
4671 -- LOCKING
4672 BSC_LOCKS_PUB.Remove_System_Lock;
4673
4674 ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
4675 RETCODE := 2; -- Request completed with errors
4676
4677 WHEN e_update_error THEN
4678 -- LOCKING
4679 BSC_LOCKS_PUB.Remove_System_Lock;
4680
4681 ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
4682 RETCODE := 2; -- Request completed with errors
4683
4684 WHEN OTHERS THEN
4685 BSC_MESSAGE.Add(x_message => SQLERRM,
4686 x_source => 'BSC_UPDATE.Run_change_current_year',
4687 x_mode => 'I');
4688 COMMIT;
4689
4690 BSC_UPDATE_LOG.Write_Errors_To_Log;
4691
4692 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4693 BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4694 ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4695 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4696
4697 -- LOCKING
4698 BSC_LOCKS_PUB.Remove_System_Lock;
4699
4700 ERRBUF := SQLERRM;
4701 RETCODE := 2; -- Request completed with errors
4702
4703 END Run_change_current_year;
4704
4705
4706 /*===========================================================================+
4707 | PROCEDURE Load_Indicators_Data
4708 +============================================================================*/
4709 PROCEDURE Load_Indicators_Data (
4710 ERRBUF OUT NOCOPY VARCHAR2,
4711 RETCODE OUT NOCOPY VARCHAR2,
4712 x_indicators IN VARCHAR2,
4713 x_load_affected_indicators varchar2
4714 ) IS
4715
4716 e_system_lock EXCEPTION;
4717 e_update_error EXCEPTION;
4718 e_unexpected_error EXCEPTION;
4719 e_warning EXCEPTION;
4720 --Fix bug#3923207: import dbi plans was moved to execute_update_process
4721 --e_import_dbi_plans EXCEPTION;
4722 h_count NUMBER;
4723
4724 TYPE t_cursor IS REF CURSOR;
4725 h_cursor t_cursor;
4726 h_sql VARCHAR2(32000);
4727
4728 h_source VARCHAR2(200);
4729
4730 h_indicators BSC_UPDATE_UTIL.t_array_of_number;
4731 h_num_indicators NUMBER;
4732
4733 h_input_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
4734 h_num_input_tables NUMBER;
4735
4736 h_process_id NUMBER;
4737 h_i NUMBER;
4738
4739 h_where_indics VARCHAR2(32000);
4740 h_plan_snapshot VARCHAR2(100);
4741 h_error_msg VARCHAR2(2000);
4742
4743 --LOCKING: new variables
4744 h_return_status VARCHAR2(10);
4745 h_msg_count NUMBER;
4746 h_msg_data VARCHAR2(4000);
4747
4748 -- Bug#4681065
4749 h_prod_indicators BSC_UPDATE_UTIL.t_array_of_number;
4750 h_num_prod_indicators NUMBER;
4751 h_lst_prod_indicators VARCHAR2(32000);
4752 h_first_prot_indic BOOLEAN;
4753 h_indic_name VARCHAR2(2000);
4754 h_prototype_flag NUMBER;
4755
4756 CURSOR c_indicator (p_kpi NUMBER) IS
4757 SELECT prototype_flag, name
4758 FROM bsc_kpis_vl
4759 WHERE indicator = p_kpi;
4760
4761 BEGIN
4762 h_num_indicators := 0;
4763 h_num_input_tables := 0;
4764 --Bug#4681065
4765 h_num_prod_indicators := 0;
4766 h_lst_prod_indicators := NULL;
4767 h_first_prot_indic := TRUE;
4768
4769 -- Bug#4681065
4770 g_warning := FALSE;
4771
4772 -- Initialize BSC/APPS global variables
4773 BSC_APPS.Init_Bsc_Apps;
4774
4775 -- LOCKING: call thi api
4776 FND_MSG_PUB.Initialize;
4777
4778 -- Initializes the error message stack
4779 g_debug_flag := 'NO';
4780 BSC_MESSAGE.Init(g_debug_flag);
4781
4782 -- Initialize the temporal table used for big 'in' conditions
4783 BSC_APPS.Init_Big_In_Cond_Table;
4784
4785 -- Initializes g_session_id, g_user_id and g_schema_name
4786 IF NOT Init_Env_Values THEN
4787 RAISE e_update_error;
4788 END IF;
4789
4790 IF x_indicators IS NULL THEN
4791 -- No indicators to be processed
4792 RETURN;
4793 END IF;
4794
4795 -- Check system lock
4796 --LOCKING: Do not use BSC_SECURITY.
4797 --BSC_SECURITY.Check_System_Lock(-101, NULL, BSC_APPS.apps_user_id);
4798 --h_source := 'BSC_SECURITY.CHECK_SYSTEM_LOCK';
4799 --SELECT count(*)
4800 --INTO h_count
4801 --FROM bsc_message_logs
4802 --WHERE type = 0 AND UPPER(source) = h_source AND last_update_login = g_session_id;
4803 --IF h_count > 0 THEN
4804 -- RAISE e_system_lock;
4805 --END IF;
4806 --LOCKING: Call this api for backward compatibility
4807 BSC_LOCKS_PUB.Get_System_Lock (
4808 p_program_id => -101,
4809 p_user_id => BSC_APPS.apps_user_id,
4810 p_icx_session_id => null,
4811 x_return_status => h_return_status,
4812 x_msg_count => h_msg_count,
4813 x_msg_data => h_msg_data
4814 );
4815 IF h_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4816 RAISE e_system_lock;
4817 END IF;
4818
4819 -- Decompose the list in x_parameter_1 into the array h_indicators
4820 h_num_indicators := BSC_UPDATE_UTIL.Decompose_Numeric_List(x_indicators,
4821 h_indicators,
4822 ',');
4823
4824 IF h_num_indicators = 0 THEN
4825 -- No indicators to be processed
4826 -- LOCKING
4827 BSC_LOCKS_PUB.Remove_System_Lock;
4828 RETURN;
4829 END IF;
4830
4831 --Fix bug#3923207: import dbi plans was moved to execute_update_process
4832 --IF NOT BSC_UPDATE_DIM.Import_Dbi_Plans(h_error_msg) THEN
4833 -- RAISE e_import_dbi_plans;
4834 --END IF;
4835
4836 -- Bug#4681065: we are going to process only indicators in production.
4837 -- Write warning message for indicators in prototype and complete with warning
4838 FOR h_i IN 1..h_num_indicators LOOP
4839 OPEN c_indicator(h_indicators(h_i));
4840 FETCH c_indicator INTO h_prototype_flag, h_indic_name;
4841 CLOSE c_indicator;
4842
4843 IF h_prototype_flag IN (0,6,7) THEN
4844 h_num_prod_indicators := h_num_prod_indicators + 1;
4845 h_prod_indicators(h_num_prod_indicators) := h_indicators(h_i);
4846 IF h_lst_prod_indicators IS NOT NULL THEN
4847 h_lst_prod_indicators := h_lst_prod_indicators||',';
4848 END IF;
4849 h_lst_prod_indicators := h_lst_prod_indicators||h_indicators(h_i);
4850 ELSE
4851 IF h_first_prot_indic THEN
4852 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_CANNOT_LOAD_OBJS_IN_PROT'),
4853 BSC_UPDATE_LOG.OUTPUT);
4854 h_first_prot_indic := FALSE;
4855 g_warning := TRUE;
4856 END IF;
4857 BSC_UPDATE_LOG.Write_Line_Log(h_indicators(h_i)||' '||h_indic_name, BSC_UPDATE_LOG.OUTPUT);
4858 END IF;
4859 END LOOP;
4860
4861 -- Get input tables used for the indicators
4862 IF NOT Get_Input_Tables_Kpis(h_prod_indicators, h_num_prod_indicators, h_input_tables, h_num_input_tables) THEN
4863 RAISE e_unexpected_error;
4864 END IF;
4865
4866 IF h_num_input_tables = 0 THEN
4867 -- No input tables to load
4868 -- LOCKING
4869 BSC_LOCKS_PUB.Remove_System_Lock;
4870 IF g_warning THEN
4871 ERRBUF := '';
4872 RETCODE := 1; -- Request completed with warning
4873 END IF;
4874 RETURN;
4875 END IF;
4876
4877 -- Create a new process in BSC_DB_PROCESS_CONTROL
4878 --LOCKING: get the sequence nextval here and then use it
4879 SELECT bsc_db_process_id_s.nextval
4880 INTO h_process_id
4881 FROM DUAL;
4882
4883 INSERT INTO bsc_db_process_control (process_id, process_name,
4884 creation_date, created_by, last_update_date,last_updated_by,
4885 last_update_login, status)
4886 VALUES (h_process_id, PC_LOADER_PROCESS,
4887 SYSDATE, g_user_id, SYSDATE, g_user_id, g_session_id, PC_PENDING_STATUS);
4888
4889 -- Insert input tables in BSC_DB_LOADER_CONTROL
4890 FOR h_i IN 1..h_num_input_tables LOOP
4891 INSERT INTO bsc_db_loader_control (process_id, input_table_name, status,
4892 error_code, stage, last_stage_flag)
4893 VALUES (h_process_id, h_input_tables(h_i), LC_PENDING_STATUS, NULL, LC_PENDING_STAGE, 0);
4894 END LOOP;
4895 COMMIT;
4896
4897 -- Run loader
4898 IF x_load_affected_indicators IS NOT NULL AND x_load_affected_indicators = 'Y' THEN
4899 -- Load all the indicators affected by the input tables
4900 Execute_Update_Process(h_process_id, PC_LOADER_PROCESS, NULL);
4901 ELSE
4902 -- Only load summary levels of the specified indicators
4903 Execute_Update_Process(h_process_id, PC_LOADER_PROCESS, h_lst_prod_indicators);
4904
4905 END IF;
4906
4907 h_source := 'BSC_UPDATE.EXECUTE_UPDATE_PROCESS';
4908
4909 SELECT count(*)
4910 INTO h_count
4911 FROM bsc_message_logs
4912 WHERE type = 0 AND UPPER(source) = h_source AND last_update_login = g_session_id;
4913
4914 IF h_count > 0 THEN
4915 RAISE e_update_error;
4916 END IF;
4917
4918 SELECT count(*)
4919 INTO h_count
4920 FROM bsc_db_loader_control
4921 WHERE process_id = g_process_id AND status IN (LC_ERROR_STATUS, LC_NO_DATA_STATUS);
4922
4923 IF h_count > 0 THEN
4924 RAISE e_warning;
4925 END IF;
4926
4927 -- Bug#4681065
4928 IF g_warning THEN
4929 RAISE e_warning;
4930 END IF;
4931
4932 -- LOCKING
4933 BSC_LOCKS_PUB.Remove_System_Lock;
4934
4935 EXCEPTION
4936 WHEN e_system_lock THEN
4937 --LOCKING: h_msg_data has the error message
4938 BSC_MESSAGE.Add(
4939 X_Message => h_msg_data,
4940 X_Source => 'BSC_UPDATE.Run_Concurrent_Loader_Apps',
4941 X_Mode => 'I'
4942 );
4943
4944 BSC_UPDATE_LOG.Write_Errors_To_Log;
4945
4946 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4947 BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4948 ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4949 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4950
4951 -- LOCKING
4952 BSC_LOCKS_PUB.Remove_System_Lock;
4953
4954 ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
4955 RETCODE := 2; -- Request completed with errors
4956
4957 WHEN e_update_error THEN
4958 -- LOCKING
4959 BSC_LOCKS_PUB.Remove_System_Lock;
4960
4961 ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
4962 RETCODE := 2; -- Request completed with errors
4963
4964 WHEN e_warning THEN
4965 -- LOCKING
4966 BSC_LOCKS_PUB.Remove_System_Lock;
4967
4968 ERRBUF := '';
4969 RETCODE := 1; -- Request completed with warning
4970
4971 WHEN e_unexpected_error THEN
4972 BSC_UPDATE_LOG.Write_Errors_To_Log;
4973
4974 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4975 BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4976 ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4977 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4978
4979 -- LOCKING
4980 BSC_LOCKS_PUB.Remove_System_Lock;
4981
4982 ERRBUF := SQLERRM;
4983 RETCODE := 2; -- Request completed with errors
4984
4985 --Fix bug#3923207: import dbi plans was moved to execute_update_process
4986 --WHEN e_import_dbi_plans THEN
4987
4988 WHEN OTHERS THEN
4989 BSC_MESSAGE.Add(x_message => SQLERRM,
4990 x_source => 'BSC_UPDATE.Load_Indicators_Data',
4991 x_mode => 'I');
4992 COMMIT;
4993
4994 BSC_UPDATE_LOG.Write_Errors_To_Log;
4995
4996 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4997 BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4998 ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4999 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
5000
5001 -- LOCKING
5002 BSC_LOCKS_PUB.Remove_System_Lock;
5003
5004 ERRBUF := SQLERRM;
5005 RETCODE := 2; -- Request completed with errors
5006
5007 END Load_Indicators_Data;
5008
5009
5010 /*===========================================================================+
5011 | PROCEDURE Load_Indicators_Dims
5012 +============================================================================*/
5013 PROCEDURE Load_Indicators_Dims (
5014 ERRBUF OUT NOCOPY VARCHAR2,
5015 RETCODE OUT NOCOPY VARCHAR2,
5016 x_indicators IN VARCHAR2,
5017 x_load_dim_affected_indicators varchar2
5018 ) IS
5019
5020 e_unexpected_error EXCEPTION;
5021 e_system_lock EXCEPTION;
5022 e_update_error EXCEPTION;
5023 e_warning EXCEPTION;
5024 h_count NUMBER;
5025
5026 TYPE t_cursor IS REF CURSOR;
5027 h_cursor t_cursor;
5028 h_sql VARCHAR2(32000);
5029
5030 h_source VARCHAR2(200);
5031
5032 h_indicators BSC_UPDATE_UTIL.t_array_of_number;
5033 h_num_indicators NUMBER;
5034
5035 h_input_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
5036 h_num_input_tables NUMBER;
5037
5038 l_input_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
5039 l_num_input_tables NUMBER;
5040
5041 h_process_id NUMBER;
5042 h_i NUMBER;
5043
5044 h_dbi_dimensions BSC_UPDATE_UTIL.t_array_of_varchar2;
5045 h_num_dbi_dimensions NUMBER;
5046 h_dbi_dim_requests BSC_UPDATE_UTIL.t_array_of_number;
5047
5048 --LOCKING: new variables
5049 h_return_status VARCHAR2(10);
5050 h_msg_count NUMBER;
5051 h_msg_data VARCHAR2(4000);
5052
5053 BEGIN
5054 h_num_indicators := 0;
5055 h_num_input_tables := 0;
5056
5057 -- Bug#4681065
5058 g_warning := FALSE;
5059
5060 -- Initialize BSC/APPS global variables
5061 BSC_APPS.Init_Bsc_Apps;
5062
5063 -- LOCKING: call thi api
5064 FND_MSG_PUB.Initialize;
5065
5066 -- Initializes the error message stack
5067 g_debug_flag := 'NO';
5068 BSC_MESSAGE.Init(g_debug_flag);
5069
5070 -- Initialize the temporal table used for big 'in' conditions
5071 BSC_APPS.Init_Big_In_Cond_Table;
5072
5073 -- Initializes g_session_id, g_user_id and g_schema_name
5074 IF NOT Init_Env_Values THEN
5075 RAISE e_update_error;
5076 END IF;
5077
5078 IF x_indicators IS NULL THEN
5079 -- No indicators to be processed
5080 RETURN;
5081 END IF;
5082
5083 -- Check system lock
5084 --LOCKING: Do not use BSC_SECURITY.
5085 --BSC_SECURITY.Check_System_Lock(-101, NULL, BSC_APPS.apps_user_id);
5086 --h_source := 'BSC_SECURITY.CHECK_SYSTEM_LOCK';
5087 --SELECT count(*)
5088 --INTO h_count
5089 --FROM bsc_message_logs
5090 --WHERE type = 0 AND UPPER(source) = h_source AND last_update_login = g_session_id;
5091 --IF h_count > 0 THEN
5092 -- RAISE e_system_lock;
5093 --END IF;
5094 --LOCKING: Call this api for backward compatibility
5095 BSC_LOCKS_PUB.Get_System_Lock (
5096 p_program_id => -101,
5097 p_user_id => BSC_APPS.apps_user_id,
5098 p_icx_session_id => null,
5099 x_return_status => h_return_status,
5100 x_msg_count => h_msg_count,
5101 x_msg_data => h_msg_data
5102 );
5103 IF h_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5104 RAISE e_system_lock;
5105 END IF;
5106
5107 -- Decompose the list in x_parameter_1 into the array h_indicators
5108 h_num_indicators := BSC_UPDATE_UTIL.Decompose_Numeric_List(x_indicators,
5109 h_indicators,
5110 ',');
5111
5112 IF h_num_indicators = 0 THEN
5113 -- No indicators to be processed
5114 -- LOCKING
5115 BSC_LOCKS_PUB.Remove_System_Lock;
5116 RETURN;
5117 END IF;
5118
5119 if x_load_dim_affected_indicators is not null and x_load_dim_affected_indicators='Y' then
5120 l_num_input_tables:=0;
5121 -- Get input tables used for the indicators
5122 IF NOT Get_Input_Tables_Kpis(h_indicators, h_num_indicators, l_input_tables, l_num_input_tables) THEN
5123 RAISE e_unexpected_error;
5124 END IF;
5125 /*
5126 for these input tables, get the affected indicators..these indicators also have to be loaded
5127 */
5128 if get_kpi_for_input_tables(l_input_tables,l_num_input_tables,h_indicators,h_num_indicators)=false then
5129 raise e_unexpected_error;
5130 end if;
5131 end if;
5132
5133 -- ---------------------- Refresh DBI dimensions ---------------------------
5134 -- Get short name of the DBI dimensions used by the indicators
5135 -- AW_INTEGRATION: Since we need to bring all the BIS dimensions used by AW indicators into AW world
5136 -- I need to change the next function to return all the BIS dimensions and not only the
5137 -- ones that are materialized in BSC
5138 IF NOT BSC_UPDATE_DIM.Get_Dbi_Dims_Kpis(h_indicators, h_num_indicators, h_dbi_dimensions, h_num_dbi_dimensions) THEN
5139 RAISE e_unexpected_error;
5140 END IF;
5141
5142 -- Launch a concurrent program to refresh each DBI dim table created in BSC to materialize
5143 -- the DBi dimension view.
5144
5145 -- Create the global temporary tables needed in this process (one time)
5146 IF h_num_dbi_dimensions > 0 THEN
5147 IF NOT BSC_UPDATE_DIM.Create_Dbi_Dim_Temp_Tables THEN
5148 RAISE e_unexpected_error;
5149 END IF;
5150 -- AW_INTEGRATION: Create temporary tables needed for AW dimension processing
5151 IF NOT BSC_UPDATE_DIM.Create_AW_Dim_Temp_Tables THEN
5152 RAISE e_unexpected_error;
5153 END IF;
5154 END IF;
5155
5156 h_dbi_dim_requests.delete;
5157 FOR h_i IN 1..h_num_dbi_dimensions LOOP
5158 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_IVIEWER', 'REFRESH')||
5159 ' '||h_dbi_dimensions(h_i), BSC_UPDATE_LOG.OUTPUT);
5160
5161 h_dbi_dim_requests(h_i) := FND_REQUEST.Submit_Request(application => BSC_APPS.bsc_apps_short_name,
5162 program => 'BSC_REFRESH_DBI_DIM',
5163 argument1 => h_dbi_dimensions(h_i));
5164
5165
5166 IF h_dbi_dim_requests(h_i) = 0 THEN
5167 -- Request error;
5168 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_SUBMMITREQ_FAILED'), BSC_UPDATE_LOG.OUTPUT);
5169 ELSE
5170 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_LOADER_REQ_ID')||
5171 ' '||h_dbi_dim_requests(h_i), BSC_UPDATE_LOG.OUTPUT);
5172 END IF;
5173 COMMIT;
5174 END LOOP;
5175 -- --------------------------------------------------------------------------------
5176
5177 -- Get dimension input tables used for the indicators
5178 IF NOT Get_Dim_Input_Tables_Kpis(h_indicators, h_num_indicators, h_input_tables, h_num_input_tables) THEN
5179 RAISE e_unexpected_error;
5180 END IF;
5181
5182 IF h_num_input_tables = 0 THEN
5183 -- No input tables to load
5184 IF h_num_dbi_dimensions > 0 THEN
5185 IF NOT Wait_For_Requests(h_dbi_dim_requests, h_num_dbi_dimensions) THEN
5186 RAISE e_unexpected_error;
5187 END IF;
5188 END IF;
5189 RETURN;
5190 END IF;
5191
5192 --LOCKING: get the sequence nextval here and then use it
5193 SELECT bsc_db_process_id_s.nextval
5194 INTO h_process_id
5195 FROM DUAL;
5196
5197 -- Create a new process in BSC_DB_PROCESS_CONTROL
5198 INSERT INTO bsc_db_process_control (process_id, process_name,
5199 creation_date, created_by, last_update_date,last_updated_by,
5200 last_update_login, status)
5201 VALUES (h_process_id, PC_LOAD_DIMENSIONS,
5202 SYSDATE, g_user_id, SYSDATE, g_user_id, g_session_id, PC_PENDING_STATUS);
5203
5204 -- Insert input tables in BSC_DB_LOADER_CONTROL
5205 FOR h_i IN 1..h_num_input_tables LOOP
5206 INSERT INTO bsc_db_loader_control (process_id, input_table_name, status,
5207 error_code, stage, last_stage_flag)
5208 VALUES (h_process_id, h_input_tables(h_i), LC_PENDING_STATUS, NULL, LC_PENDING_STAGE, 0);
5209 END LOOP;
5210 COMMIT;
5211
5212 -- Run loader
5213 Execute_Update_Process(h_process_id, PC_LOAD_DIMENSIONS, x_indicators);
5214
5215
5216 -- ------------- Wait for DBI Dimensions refresh ---------------------
5217 IF h_num_dbi_dimensions > 0 THEN
5218 IF NOT Wait_For_Requests(h_dbi_dim_requests, h_num_dbi_dimensions) THEN
5219 RAISE e_unexpected_error;
5220 END IF;
5221 END IF;
5222 -- -------------------------------------------------------------------
5223
5224 h_source := 'BSC_UPDATE.EXECUTE_UPDATE_PROCESS';
5225
5226 SELECT count(*)
5227 INTO h_count
5228 FROM bsc_message_logs
5229 WHERE type = 0 AND UPPER(source) = h_source AND last_update_login = g_session_id;
5230
5231 IF h_count > 0 THEN
5232 RAISE e_update_error;
5233 END IF;
5234
5235 SELECT count(*)
5236 INTO h_count
5237 FROM bsc_db_loader_control
5238 WHERE process_id = g_process_id AND status IN (LC_ERROR_STATUS, LC_NO_DATA_STATUS);
5239
5240 IF h_count > 0 THEN
5241 RAISE e_warning;
5242 END IF;
5243
5244 -- Bug#4681065
5245 IF g_warning THEN
5246 RAISE e_warning;
5247 END IF;
5248
5249 -- LOCKING
5250 BSC_LOCKS_PUB.Remove_System_Lock;
5251
5252 EXCEPTION
5253 WHEN e_system_lock THEN
5254 --LOCKING: h_msg_data has the error message
5255 BSC_MESSAGE.Add(
5256 X_Message => h_msg_data,
5257 X_Source => 'BSC_UPDATE.Run_Concurrent_Loader_Apps',
5258 X_Mode => 'I'
5259 );
5260
5261 BSC_UPDATE_LOG.Write_Errors_To_Log;
5262
5263 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
5264 BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
5265 ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
5266 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
5267
5268 -- LOCKING
5269 BSC_LOCKS_PUB.Remove_System_Lock;
5270
5271 ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
5272 RETCODE := 2; -- Request completed with errors
5273
5274 WHEN e_update_error THEN
5275 -- LOCKING
5276 BSC_LOCKS_PUB.Remove_System_Lock;
5277 ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
5278 RETCODE := 2; -- Request completed with errors
5279
5280 WHEN e_warning THEN
5281 -- LOCKING
5282 BSC_LOCKS_PUB.Remove_System_Lock;
5283 ERRBUF := '';
5284 RETCODE := 1; -- Request completed with warning
5285
5286 WHEN e_unexpected_error THEN
5287 BSC_UPDATE_LOG.Write_Errors_To_Log;
5288
5289 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
5290 BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
5291 ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
5292 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
5293
5294 -- LOCKING
5295 BSC_LOCKS_PUB.Remove_System_Lock;
5296 ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
5297 RETCODE := 2; -- Request completed with errors
5298
5299 WHEN OTHERS THEN
5300 BSC_MESSAGE.Add(x_message => SQLERRM,
5301 x_source => 'BSC_UPDATE.Load_Indicators_Dims',
5302 x_mode => 'I');
5303 COMMIT;
5304
5305 BSC_UPDATE_LOG.Write_Errors_To_Log;
5306
5307 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
5308 BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
5309 ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
5310 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
5311
5312 -- LOCKING
5313 BSC_LOCKS_PUB.Remove_System_Lock;
5314 ERRBUF := SQLERRM;
5315 RETCODE := 2; -- Request completed with errors
5316
5317 END Load_Indicators_Dims;
5318
5319
5320 /*===========================================================================+
5321 | PROCEDURE Delete_Indicators_Data
5322 +============================================================================*/
5323 PROCEDURE Delete_Indicators_Data (
5324 ERRBUF OUT NOCOPY VARCHAR2,
5325 RETCODE OUT NOCOPY VARCHAR2,
5326 x_indicators IN VARCHAR2,
5327 x_keep_input_table_data IN VARCHAR2
5328 ) IS
5329
5330 e_system_lock EXCEPTION;
5331 e_update_error EXCEPTION;
5332 h_count NUMBER;
5333
5334 TYPE t_cursor IS REF CURSOR;
5335 h_cursor t_cursor;
5336 h_sql VARCHAR2(32000);
5337
5338 h_source VARCHAR2(200);
5339
5340 h_indicators BSC_UPDATE_UTIL.t_array_of_number;
5341 h_num_indicators NUMBER;
5342
5343 h_process_id NUMBER;
5344 h_i NUMBER;
5345
5346 --LOCKING: new variables
5347 h_return_status VARCHAR2(10);
5348 h_msg_count NUMBER;
5349 h_msg_data VARCHAR2(4000);
5350
5351 BEGIN
5352 h_num_indicators := 0;
5353 g_keep_input_table_data := x_keep_input_table_data;
5354
5355 -- Initialize BSC/APPS global variables
5356 BSC_APPS.Init_Bsc_Apps;
5357
5358 -- LOCKING: call thi api
5359 FND_MSG_PUB.Initialize;
5360
5361 -- Initializes the error message stack
5362 g_debug_flag := 'NO';
5363 BSC_MESSAGE.Init(g_debug_flag);
5364
5365 -- Initialize the temporal table used for big 'in' conditions
5366 BSC_APPS.Init_Big_In_Cond_Table;
5367
5368 -- Initializes g_session_id, g_user_id and g_schema_name
5369 IF NOT Init_Env_Values THEN
5370 RAISE e_update_error;
5371 END IF;
5372
5373 -- Check system lock
5374 --LOCKING: Do not use BSC_SECURITY.
5375 --BSC_SECURITY.Check_System_Lock(-101, NULL, BSC_APPS.apps_user_id);
5376 --h_source := 'BSC_SECURITY.CHECK_SYSTEM_LOCK';
5377 --SELECT count(*)
5378 --INTO h_count
5379 --FROM bsc_message_logs
5380 --WHERE type = 0 AND UPPER(source) = h_source AND last_update_login = g_session_id;
5381 --IF h_count > 0 THEN
5382 -- RAISE e_system_lock;
5383 --END IF;
5384 --LOCKING: Call this api for backward compatibility
5385 BSC_LOCKS_PUB.Get_System_Lock (
5386 p_program_id => -101,
5387 p_user_id => BSC_APPS.apps_user_id,
5388 p_icx_session_id => null,
5389 x_return_status => h_return_status,
5390 x_msg_count => h_msg_count,
5391 x_msg_data => h_msg_data
5392 );
5393 IF h_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5394 RAISE e_system_lock;
5395 END IF;
5396
5397 -- Bug Fix: From now on if this concurrent program is called from Loader UI
5398 -- it is passing in the parameter x_indicators the process id in this way: PROCESS_ID:<process_id>
5399 -- Now, if this concurrent program is called from RSG it continue passing the list
5400 -- of indicators
5401
5402 IF SUBSTR(x_indicators, 1, 11) = 'PROCESS_ID:' THEN
5403 -- it is called from loader ui and it is passing the process id
5404 h_process_id := TO_NUMBER(SUBSTR(x_indicators, 12));
5405 ELSE
5406 -- called from RSG, it is passing a list of indicators
5407 --Decompose the list in x_indicators into the array h_indicators
5408 h_num_indicators := BSC_UPDATE_UTIL.Decompose_Numeric_List(x_indicators,
5409 h_indicators,
5410 ',');
5411
5412 IF h_num_indicators = 0 THEN
5413 -- No indicators to be processed
5414 -- LOCKING
5415 BSC_LOCKS_PUB.Remove_System_Lock;
5416 RETURN;
5417 END IF;
5418
5419 -- LOCKING: get the sequence next val here and then use it
5420 SELECT bsc_db_process_id_s.nextval
5421 INTO h_process_id
5422 FROM DUAL;
5423
5424 -- Create a new process in BSC_DB_PROCESS_CONTROL
5425 INSERT INTO bsc_db_process_control (process_id, process_name,
5426 creation_date, created_by, last_update_date,last_updated_by,
5427 last_update_login, status)
5428 VALUES (h_process_id, PC_DELETE_KPI_DATA_PROCESS,
5429 SYSDATE, g_user_id, SYSDATE, g_user_id, g_session_id, PC_PENDING_STATUS);
5430
5431 -- Insert indicators in BSC_DB_LOADER_CONTROL
5432 FOR h_i IN 1..h_num_indicators LOOP
5433 INSERT INTO bsc_db_loader_control (process_id, input_table_name, status,
5434 error_code, stage, last_stage_flag)
5435 VALUES (h_process_id, h_indicators(h_i), NULL, NULL, NULL, 0);
5436 END LOOP;
5437 COMMIT;
5438 END IF;
5439
5440 -- Run loader
5441 Execute_Update_Process(h_process_id, PC_DELETE_KPI_DATA_PROCESS, NULL);
5442
5443 h_source := 'BSC_UPDATE.EXECUTE_UPDATE_PROCESS';
5444
5445 SELECT count(*)
5446 INTO h_count
5447 FROM bsc_message_logs
5448 WHERE type = 0 AND UPPER(source) = h_source AND last_update_login = g_session_id;
5449
5450 IF h_count > 0 THEN
5451 RAISE e_update_error;
5452 END IF;
5453
5454 -- LOCKING
5455 BSC_LOCKS_PUB.Remove_System_Lock;
5456
5457 EXCEPTION
5458 WHEN e_system_lock THEN
5459 --LOCKING: h_msg_data has the error message
5460 BSC_MESSAGE.Add(
5461 X_Message => h_msg_data,
5462 X_Source => 'BSC_UPDATE.Run_Concurrent_Loader_Apps',
5463 X_Mode => 'I'
5464 );
5465
5466 BSC_UPDATE_LOG.Write_Errors_To_Log;
5467
5468 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
5469 BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
5470 ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
5471 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
5472
5473 -- LOCKING
5474 BSC_LOCKS_PUB.Remove_System_Lock;
5475
5476 ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
5477 RETCODE := 2; -- Request completed with errors
5478
5479 WHEN e_update_error THEN
5480 -- LOCKING
5481 BSC_LOCKS_PUB.Remove_System_Lock;
5482 ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
5483 RETCODE := 2; -- Request completed with errors
5484
5485 WHEN OTHERS THEN
5486 BSC_MESSAGE.Add(x_message => SQLERRM,
5487 x_source => 'BSC_UPDATE.Delete_Indicators_Data',
5488 x_mode => 'I');
5489 COMMIT;
5490
5491 BSC_UPDATE_LOG.Write_Errors_To_Log;
5492
5493 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
5494 BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
5495 ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
5496 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
5497
5498 -- LOCKING
5499 BSC_LOCKS_PUB.Remove_System_Lock;
5500
5501 ERRBUF := SQLERRM;
5502 RETCODE := 2; -- Request completed with errors
5503
5504 END Delete_Indicators_Data;
5505
5506
5507 /*===========================================================================+
5508 | FUNCTION Get_Input_Tables_Kpis
5509 +============================================================================*/
5510 FUNCTION Get_Input_Tables_Kpis(
5511 x_indicators IN BSC_UPDATE_UTIL.t_array_of_number,
5512 x_num_indicators IN NUMBER,
5513 x_input_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
5514 x_num_input_tables IN OUT NOCOPY NUMBER
5515 ) RETURN BOOLEAN IS
5516
5517 e_unexpected_error EXCEPTION;
5518
5519 h_where_indics VARCHAR2(32000);
5520 h_i NUMBER;
5521
5522 TYPE t_cursor IS REF CURSOR;
5523 h_cursor t_cursor;
5524 h_sql VARCHAR2(32700);
5525
5526 h_table_name VARCHAR2(50);
5527
5528 h_system_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
5529 h_num_system_tables NUMBER;
5530
5531 BEGIN
5532 h_where_indics := NULL;
5533 h_sql := NULL;
5534 h_num_system_tables := 0;
5535
5536 h_where_indics := BSC_APPS.Get_New_Big_In_Cond_Number(1, 'indicator');
5537 FOR h_i IN 1 .. x_num_indicators LOOP
5538 BSC_APPS.Add_Value_Big_In_Cond(1, x_indicators(h_i));
5539 END LOOP;
5540
5541 h_sql := 'SELECT table_name'||
5542 ' FROM bsc_kpi_data_tables'||
5543 ' WHERE ('||h_where_indics||') AND table_name IS NOT NULL';
5544
5545 OPEN h_cursor FOR h_sql;
5546 FETCH h_cursor INTO h_table_name;
5547 WHILE h_cursor%FOUND LOOP
5548 h_num_system_tables := h_num_system_tables + 1;
5549 h_system_tables(h_num_system_tables) := h_table_name;
5550
5551 FETCH h_cursor INTO h_table_name;
5552 END LOOP;
5553 CLOSE h_cursor;
5554
5555 -- Insert into the array x_input_tables the input tables from where the system tables are originated.
5556 IF NOT BSC_UPDATE_INC.Get_Input_Tables(x_input_tables, x_num_input_tables, h_system_tables, h_num_system_tables) THEN
5557 RAISE e_unexpected_error;
5558 END IF;
5559
5560 RETURN TRUE;
5561
5562 EXCEPTION
5563 WHEN e_unexpected_error THEN
5564 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_RETR_ITABLES_FAILED'),
5565 x_source => 'BSC_UPDATE.Get_Input_Tables_Kpis');
5566 RETURN FALSE;
5567
5568 WHEN OTHERS THEN
5569 BSC_MESSAGE.Add(x_message => SQLERRM,
5570 x_source => 'BSC_UPDATE.Get_Input_Tables_Kpis');
5571 RETURN FALSE;
5572
5573 END Get_Input_Tables_Kpis;
5574
5575 function get_kpi_for_input_tables(
5576 x_input_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
5577 x_num_input_tables IN NUMBER,
5578 x_indicators IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
5579 x_num_indicators IN OUT NOCOPY NUMBER
5580 )return boolean is
5581 --
5582 e_unexpected_error exception;
5583 --
5584 Begin
5585 if x_num_indicators is null then
5586 x_num_indicators:=0;
5587 end if;
5588 for i in 1..x_num_input_tables loop
5589 if get_kpi_for_input_tables(x_input_tables(i),x_indicators,x_num_indicators)=false then
5590 raise e_unexpected_error;
5591 end if;
5592 end loop;
5593 return true;
5594 EXCEPTION
5595 WHEN e_unexpected_error THEN
5596 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_RETR_ITABLES_FAILED'),
5597 x_source => 'BSC_UPDATE.get_kpi_for_input_tables');
5598 RETURN FALSE;
5599
5600 WHEN OTHERS THEN
5601 BSC_MESSAGE.Add(x_message => SQLERRM,
5602 x_source => 'BSC_UPDATE.get_kpi_for_input_tables');
5603 RETURN FALSE;
5604
5605 END get_kpi_for_input_tables;
5606
5607 --called recursively
5608 function get_kpi_for_input_tables(
5609 x_input_table varchar2,
5610 x_indicators IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
5611 x_num_indicators IN OUT NOCOPY NUMBER
5612 )return boolean is
5613 --
5614 cursor c1(p_table varchar2) is select table_name from bsc_db_tables_rels where source_table_name=p_table;
5615 --
5616 cursor c2(p_table varchar2) is select distinct indicator from bsc_kpi_data_tables where table_name=p_table;
5617 --
5618 l_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
5619 l_num_tables number;
5620 l_indicator number;
5621 --
5622 Begin
5623 l_num_tables:=1;
5624 open c1(x_input_table);
5625 loop
5626 fetch c1 into l_tables(l_num_tables);
5627 exit when c1%notfound;
5628 l_num_tables:=l_num_tables+1;
5629 end loop;
5630 l_num_tables:=l_num_tables-1;
5631 close c1;
5632 for i in 1..l_num_tables loop
5633 l_indicator:=null;
5634 open c2(l_tables(i));
5635 fetch c2 into l_indicator;
5636 close c2;
5637 if l_indicator is not null then
5638 if value_in_array(l_indicator,x_indicators,x_num_indicators)=false then
5639 x_num_indicators:=x_num_indicators+1;
5640 x_indicators(x_num_indicators):=l_indicator;
5641 end if;
5642 else
5643 --continue
5644 if get_kpi_for_input_tables(l_tables(i),x_indicators,x_num_indicators)=false then
5645 return false;
5646 end if;
5647 end if;
5648 end loop;
5649 return true;
5650 EXCEPTION when others then
5651 return false;
5652 END get_kpi_for_input_tables;
5653
5654 function value_in_array(
5655 x_value number,
5656 x_array BSC_UPDATE_UTIL.t_array_of_number,
5657 x_num_array NUMBER
5658 )return boolean is
5659 Begin
5660 for i in 1..x_num_array loop
5661 if x_array(i)=x_value then
5662 return true;
5663 end if;
5664 end loop;
5665 return false;
5666 EXCEPTION when others then
5667 raise;
5668 End;
5669
5670 /*===========================================================================+
5671 | FUNCTION Get_Dim_Input_Tables_Kpis
5672 +============================================================================*/
5673 FUNCTION Get_Dim_Input_Tables_Kpis(
5674 x_indicators IN BSC_UPDATE_UTIL.t_array_of_number,
5675 x_num_indicators IN NUMBER,
5676 x_input_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
5677 x_num_input_tables IN OUT NOCOPY NUMBER
5678 ) RETURN BOOLEAN IS
5679
5680 e_unexpected_error EXCEPTION;
5681
5682 h_where_indics VARCHAR2(32000);
5683 h_i NUMBER;
5684
5685 TYPE t_cursor IS REF CURSOR;
5686 h_cursor t_cursor;
5687 h_sql VARCHAR2(32700);
5688
5689 h_table_name VARCHAR2(50);
5690
5691 h_source VARCHAR2(10);
5692
5693 BEGIN
5694 h_where_indics := NULL;
5695 h_sql := NULL;
5696 h_source := 'BSC';
5697
5698 h_where_indics := BSC_APPS.Get_New_Big_In_Cond_Number(1, 'k.indicator');
5699 FOR h_i IN 1 .. x_num_indicators LOOP
5700 BSC_APPS.Add_Value_Big_In_Cond(1, x_indicators(h_i));
5701 END LOOP;
5702
5703 h_sql := 'SELECT DISTINCT source_table_name'||
5704 ' FROM bsc_kpi_dim_levels_vl k, bsc_db_tables_rels r'||
5705 ' WHERE ('||h_where_indics||') AND k.level_source = :1 '||
5706 ' AND k.level_table_name = r.table_name';
5707
5708 OPEN h_cursor FOR h_sql USING h_source;
5709 FETCH h_cursor INTO h_table_name;
5710 WHILE h_cursor%FOUND LOOP
5711 x_num_input_tables := x_num_input_tables + 1;
5712 x_input_tables(x_num_input_tables) := h_table_name;
5713
5714 FETCH h_cursor INTO h_table_name;
5715 END LOOP;
5716 CLOSE h_cursor;
5717
5718 -- Also include Input tables for MN dimensions
5719 h_sql := 'SELECT DISTINCT source_table_name'||
5720 ' FROM bsc_kpi_dim_levels_vl k, bsc_db_tables_rels r'||
5721 ' WHERE ('||h_where_indics||') AND k.level_source = :1 '||
5722 ' AND k.table_relation = r.table_name';
5723
5724 OPEN h_cursor FOR h_sql USING h_source;
5725 FETCH h_cursor INTO h_table_name;
5726 WHILE h_cursor%FOUND LOOP
5727 x_num_input_tables := x_num_input_tables + 1;
5728 x_input_tables(x_num_input_tables) := h_table_name;
5729
5730 FETCH h_cursor INTO h_table_name;
5731 END LOOP;
5732 CLOSE h_cursor;
5733
5734 RETURN TRUE;
5735
5736 EXCEPTION
5737 WHEN e_unexpected_error THEN
5738 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_RETR_ITABLES_FAILED'),
5739 x_source => 'BSC_UPDATE.Get_Dim_Input_Tables_Kpis');
5740 RETURN FALSE;
5741
5742 WHEN OTHERS THEN
5743 BSC_MESSAGE.Add(x_message => SQLERRM,
5744 x_source => 'BSC_UPDATE.Get_Dim_Input_Tables_Kpis');
5745 RETURN FALSE;
5746
5747 END Get_Dim_Input_Tables_Kpis;
5748
5749
5750 /*===========================================================================+
5751 | FUNCTION Set_PStatus_Finished
5752 +============================================================================*/
5753 FUNCTION Set_PStatus_Finished(
5754 x_status IN VARCHAR2
5755 ) RETURN BOOLEAN IS
5756 BEGIN
5757 UPDATE
5758 bsc_db_process_control
5759 SET
5760 last_update_date = SYSDATE,
5761 last_updated_by = g_user_id,
5762 last_update_login = g_session_id,
5763 status = x_status,
5764 end_time = SYSDATE
5765 WHERE
5766 process_id = g_process_id;
5767
5768 RETURN TRUE;
5769
5770 EXCEPTION
5771 WHEN OTHERS THEN
5772 BSC_MESSAGE.Add(x_message => SQLERRM,
5773 x_source => 'BSC_UPDATE.Set_PStatus_Finished');
5774 RETURN FALSE;
5775
5776 END Set_PStatus_Finished;
5777
5778
5779 /*===========================================================================+
5780 | FUNCTION Set_PStatus_Running
5781 +============================================================================*/
5782 FUNCTION Set_PStatus_Running RETURN BOOLEAN IS
5783
5784 h_complete_log_file_name VARCHAR2(500);
5785
5786 BEGIN
5787 IF BSC_UPDATE_LOG.Log_File_Name IS NULL THEN
5788 h_complete_log_file_name := NULL;
5789 ELSE
5790 h_complete_log_file_name := BSC_UPDATE_LOG.Log_File_Dir||'/'||BSC_UPDATE_LOG.Log_File_Name;
5791 END IF;
5792
5793 UPDATE
5794 bsc_db_process_control
5795 SET
5796 last_update_date = SYSDATE,
5797 last_updated_by = g_user_id,
5798 last_update_login = g_session_id,
5799 status = PC_RUNNING_STATUS,
5800 log_file_location = h_complete_log_file_name,
5801 start_time = SYSDATE
5802 WHERE
5803 process_id = g_process_id;
5804
5805 RETURN TRUE;
5806
5807 EXCEPTION
5808 WHEN OTHERS THEN
5809 BSC_MESSAGE.Add(x_message => SQLERRM,
5810 x_source => 'BSC_UPDATE.Set_PStatus_Running');
5811 RETURN FALSE;
5812
5813 END Set_PStatus_Running;
5814
5815
5816 --LOCKING: new function
5817 /*===========================================================================+
5818 | FUNCTION Update_Indicator_Period
5819 +============================================================================*/
5820 FUNCTION Update_Indicator_Period (
5821 x_indicator IN NUMBER
5822 ) RETURN BOOLEAN IS
5823 BEGIN
5824 -- The update period of an indicator is the minimun period of the tables
5825 -- used by the indicator.
5826
5827 -- BSC-PMF Integration:Fix this query by adding NVL, to avoid assigning NULL
5828 -- to the current period when all of the analysis options of a kpi are for PMF measures.
5829
5830 UPDATE bsc_kpi_periodicities p
5831 SET current_period = (
5832 SELECT DISTINCT
5833 NVL(MIN(t.current_period), p.current_period)
5834 FROM
5835 bsc_kpi_data_tables k,
5836 bsc_db_tables t
5837 WHERE
5838 k.table_name = t.table_name AND
5839 k.indicator = p.indicator AND
5840 t.periodicity_id = p.periodicity_id)
5841 WHERE p.indicator = x_indicator;
5842
5843 RETURN TRUE;
5844
5845 EXCEPTION
5846 WHEN OTHERS THEN
5847 BSC_MESSAGE.Add(x_message => SQLERRM,
5848 x_source => 'BSC_UPDATE.Update_Indicator_Period');
5849 RETURN FALSE;
5850
5851 END Update_Indicator_Period;
5852
5853
5854 /*===========================================================================+
5855 | FUNCTION Update_Indicators_Periods
5856 +============================================================================*/
5857 FUNCTION Update_Indicators_Periods RETURN BOOLEAN IS
5858 BEGIN
5859 -- The update period of an indicator is the minimun period of the tables
5860 -- used by the indicator.
5861
5862 -- BSC-PMF Integration:Fix this query by adding NVL, to avoid assigning NULL
5863 -- to the current period when all of the analysis options of a kpi are for PMF measures.
5864
5865 UPDATE bsc_kpi_periodicities p
5866 SET current_period = (
5867 SELECT DISTINCT
5868 NVL(MIN(t.current_period), p.current_period)
5869 FROM
5870 bsc_kpi_data_tables k,
5871 bsc_db_tables t
5872 WHERE
5873 k.table_name = t.table_name AND
5874 k.indicator = p.indicator AND
5875 t.periodicity_id = p.periodicity_id)
5876 WHERE p.indicator IN (
5877 SELECT
5878 indicator
5879 FROM
5880 bsc_kpis_vl);
5881
5882
5883 RETURN TRUE;
5884
5885 EXCEPTION
5886 WHEN OTHERS THEN
5887 BSC_MESSAGE.Add(x_message => SQLERRM,
5888 x_source => 'BSC_UPDATE.Update_Indicators_Periods');
5889 RETURN FALSE;
5890
5891 END Update_Indicators_Periods;
5892
5893
5894 /*===========================================================================+
5895 | FUNCTION Update_Stage_Input_Table
5896 +============================================================================*/
5897 FUNCTION Update_Stage_Input_Table(
5898 x_input_table IN VARCHAR2,
5899 x_target_stage IN VARCHAR2
5900 ) RETURN BOOLEAN IS
5901 BEGIN
5902 UPDATE
5903 bsc_db_loader_control
5904 SET
5905 stage = x_target_stage
5906 WHERE
5907 input_table_name = x_input_table AND
5908 process_id = g_process_id;
5909
5910 RETURN TRUE;
5911
5912 EXCEPTION
5913 WHEN OTHERS THEN
5914 BSC_MESSAGE.Add(x_message => SQLERRM,
5915 x_source => 'BSC_UPDATE.Update_Stage_Input_Table');
5916 RETURN FALSE;
5917
5918 END Update_Stage_Input_Table;
5919
5920
5921 /*===========================================================================+
5922 | FUNCTION Update_Stage_Input_Tables
5923 +============================================================================*/
5924 FUNCTION Update_Stage_Input_Tables(
5925 x_current_status IN VARCHAR2,
5926 x_target_stage IN VARCHAR2,
5927 x_last_stage_flag IN BOOLEAN
5928 ) RETURN BOOLEAN IS
5929 BEGIN
5930 IF x_last_stage_flag THEN
5931 UPDATE
5932 bsc_db_loader_control
5933 SET
5934 last_stage_flag = 0
5935 WHERE
5936 input_table_name IN (
5937 SELECT
5938 input_table_name
5939 FROM
5940 bsc_db_loader_control
5941 WHERE
5942 process_id = g_process_id AND
5943 status = x_current_status
5944 );
5945
5946 UPDATE
5947 bsc_db_loader_control
5948 SET
5949 stage = x_target_stage,
5950 last_stage_flag = 1
5951 WHERE
5952 process_id = g_process_id AND
5953 status = x_current_status;
5954
5955 ELSE
5956 UPDATE
5957 bsc_db_loader_control
5958 SET
5959 stage = x_target_stage
5960 WHERE
5961 process_id = g_process_id AND
5962 status = x_current_status;
5963
5964 END IF;
5965
5966 RETURN TRUE;
5967
5968 EXCEPTION
5969 WHEN OTHERS THEN
5970 BSC_MESSAGE.Add(x_message => SQLERRM,
5971 x_source => 'BSC_UPDATE.Update_Stage_Input_Tables');
5972 RETURN FALSE;
5973
5974 END Update_Stage_Input_Tables;
5975
5976
5977 /*===========================================================================+
5978 | FUNCTION Update_Status_All_Input_Tables
5979 +============================================================================*/
5980 FUNCTION Update_Status_All_Input_Tables(
5981 x_current_status IN VARCHAR2,
5982 x_target_status IN VARCHAR2,
5983 x_error_code IN VARCHAR2
5984 ) RETURN BOOLEAN IS
5985 BEGIN
5986 UPDATE
5987 bsc_db_loader_control
5988 SET
5989 status = x_target_status,
5990 error_code = x_error_code
5991 WHERE
5992 status = x_current_status AND
5993 process_id = g_process_id;
5994
5995 RETURN TRUE;
5996
5997 EXCEPTION
5998 WHEN OTHERS THEN
5999 BSC_MESSAGE.Add(x_message => SQLERRM,
6000 x_source => 'BSC_UPDATE.Update_Status_All_Input_Tables');
6001 RETURN FALSE;
6002
6003 END Update_Status_All_Input_Tables;
6004
6005
6006 /*===========================================================================+
6007 | FUNCTION Update_Status_Input_Table
6008 +============================================================================*/
6009 FUNCTION Update_Status_Input_Table(
6010 x_input_table IN VARCHAR2,
6011 x_target_status IN VARCHAR2,
6012 x_error_code IN VARCHAR2
6013 ) RETURN BOOLEAN IS
6014 BEGIN
6015 UPDATE
6016 bsc_db_loader_control
6017 SET
6018 status = x_target_status,
6019 error_code = x_error_code
6020 WHERE
6021 input_table_name = x_input_table AND
6022 process_id = g_process_id;
6023
6024 RETURN TRUE;
6025
6026 EXCEPTION
6027 WHEN OTHERS THEN
6028 BSC_MESSAGE.Add(x_message => SQLERRM,
6029 x_source => 'BSC_UPDATE.Update_Status_Input_Table');
6030 RETURN FALSE;
6031
6032 END Update_Status_Input_Table;
6033
6034
6035 /*===========================================================================+
6036 | FUNCTION Wait_For_Requests
6037 +============================================================================*/
6038 FUNCTION Wait_For_Requests(
6039 x_requests IN BSC_UPDATE_UTIL.t_array_of_number,
6040 x_num_requests IN NUMBER
6041 ) RETURN BOOLEAN IS
6042
6043 h_i NUMBER;
6044 h_b BOOLEAN;
6045 h_phase VARCHAR2(32000);
6046 h_status VARCHAR2(2000);
6047 h_dev_phase VARCHAR2(2000);
6048 h_dev_status VARCHAR2(2000);
6049 h_message VARCHAR2(2000);
6050
6051 BEGIN
6052 FOR h_i IN 1..x_num_requests LOOP
6053 IF x_requests(h_i) <> 0 THEN
6054 h_b := FND_CONCURRENT.Wait_For_Request(request_id => x_requests(h_i),
6055 phase => h_phase,
6056 status => h_status,
6057 dev_phase => h_dev_phase,
6058 dev_status => h_dev_status,
6059 message => h_message);
6060 COMMIT;
6061 END IF;
6062 END LOOP;
6063
6064 RETURN TRUE;
6065
6066 EXCEPTION
6067 WHEN OTHERS THEN
6068 BSC_MESSAGE.Add(x_message => SQLERRM,
6069 x_source => 'BSC_UPDATE.Wait_For_Requests');
6070 RETURN FALSE;
6071 END Wait_For_Requests;
6072
6073
6074 /*===========================================================================+
6075 | FUNCTION Write_Result_Log
6076 +============================================================================*/
6077 FUNCTION Write_Result_Log RETURN BOOLEAN IS
6078 e_unexpected_error EXCEPTION;
6079
6080 C_TABLE_W CONSTANT NUMBER := 32;
6081 C_STATUS_W CONSTANT NUMBER := 48;
6082 C_COLUMN_W CONSTANT NUMBER := 24;
6083 C_INVALID_CODE_W CONSTANT NUMBER := 24;
6084
6085 C_TABLE_NAME_W CONSTANT NUMBER := 32;
6086 C_PERIODICITY_W CONSTANT NUMBER := 33;
6087 C_PERIOD_W CONSTANT NUMBER := 15;
6088
6089 h_update_date VARCHAR2(200);
6090
6091 TYPE t_cursor IS REF CURSOR;
6092
6093 /*
6094 c_status t_cursor; -- g_process_id
6095 c_status_sql VARCHAR2(2000) := 'SELECT input_table_name, status, error_code'||
6096 ' FROM bsc_db_loader_control'||
6097 ' WHERE process_id = :1'||
6098 ' ORDER BY input_table_name';
6099 */
6100 CURSOR c_status (p_process_id NUMBER) IS
6101 SELECT input_table_name, status, error_code
6102 FROM bsc_db_loader_control
6103 WHERE process_id = p_process_id
6104 ORDER BY input_table_name;
6105
6106
6107 h_input_table_name VARCHAR2(30);
6108 h_status VARCHAR2(1);
6109 h_error_code VARCHAR2(25);
6110 h_line VARCHAR2(200);
6111
6112 /*
6113 c_invalid_codes t_cursor; -- g_process_name, PC_LOADER_PROCESS, 0, 2
6114 c_invalid_codes_sql VARCHAR2(2000) := 'SELECT input_table_name, column_name, invalid_code'||
6115 ' FROM bsc_db_validation'||
6116 ' WHERE input_table_name IN ('||
6117 ' SELECT table_name'||
6118 ' FROM bsc_db_tables'||
6119 ' WHERE table_type = DECODE(:1, :2, :3, :4))'||
6120 ' ORDER BY input_table_name';
6121 */
6122 --Fix bug#4581846: show invalid codes for input tables involved in this process only
6123 CURSOR c_invalid_codes (p_process_id NUMBER) IS
6124 SELECT input_table_name, column_name, invalid_code
6125 FROM bsc_db_validation
6126 WHERE input_table_name IN (
6127 SELECT input_table_name
6128 FROM bsc_db_loader_control
6129 WHERE process_id = p_process_id
6130 )
6131 ORDER BY input_table_name;
6132
6133 h_column_name VARCHAR2(250);
6134 h_invalid_code VARCHAR2(250);
6135
6136 /*
6137 c_tables t_cursor; -- g_process_id
6138 c_tables_sql VARCHAR2(2000) := 'SELECT lc.input_table_name, t.periodicity_id,'||
6139 ' p.name, t.current_period, t.current_subperiod'||
6140 ' FROM bsc_db_tables t, bsc_db_loader_control lc, bsc_sys_periodicities_vl p'||
6141 ' WHERE lc.input_table_name = t.table_name AND'||
6142 ' lc.process_id = :1 AND t.periodicity_id = p.periodicity_id';
6143 */
6144 CURSOR c_tables (p_process_id NUMBER) IS
6145 SELECT lc.input_table_name, t.periodicity_id,
6146 p.name, t.current_period, t.current_subperiod
6147 FROM bsc_db_tables t, bsc_db_loader_control lc, bsc_sys_periodicities_vl p
6148 WHERE lc.input_table_name = t.table_name AND
6149 lc.process_id = p_process_id AND t.periodicity_id = p.periodicity_id;
6150
6151 h_periodicity_id NUMBER;
6152 h_periodicity_name VARCHAR2(200);
6153 h_current_period NUMBER;
6154 h_current_subperiod NUMBER;
6155
6156 h_periodicity_type NUMBER;
6157
6158 BEGIN
6159 -- Result
6160 BSC_UPDATE_LOG.Write_Line_Log('', BSC_UPDATE_LOG.OUTPUT);
6161 BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
6162 BSC_UPDATE_LOG.OUTPUT);
6163 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROCESS_RESULT'), BSC_UPDATE_LOG.OUTPUT);
6164 BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
6165 BSC_UPDATE_LOG.OUTPUT);
6166 -- Get update date
6167 IF NOT BSC_UPDATE_UTIL.Get_Init_Variable_Value('UPDATE_DATE', h_update_date) THEN
6168 RAISE e_unexpected_error;
6169 END IF;
6170 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_LAST_UPDATE')||' '||h_update_date, BSC_UPDATE_LOG.OUTPUT);
6171 BSC_UPDATE_LOG.Write_Line_Log('', BSC_UPDATE_LOG.OUTPUT);
6172
6173 --OPEN c_status FOR c_status_sql USING g_process_id;
6174 OPEN c_status(g_process_id);
6175 FETCH c_status INTO h_input_table_name, h_status, h_error_code;
6176
6177 IF c_status%FOUND THEN
6178 BSC_UPDATE_LOG.Write_Line_Log(RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'INPUT_TABLE_NAME'), C_TABLE_W)||
6179 RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'STATUS'), C_STATUS_W),
6180 BSC_UPDATE_LOG.OUTPUT);
6181 END IF;
6182
6183 WHILE c_status%FOUND LOOP
6184 h_line := RPAD(h_input_table_name, C_TABLE_W);
6185 IF h_status = LC_ERROR_STATUS THEN
6186 h_line := h_line||BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_UPDATE_FAILED');
6187
6188 IF h_error_code = LC_INVALID_CODES_ERR THEN
6189 h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_INVALID_CODES_FOUND');
6190 ELSIF h_error_code = LC_PROGRAM_ERR THEN
6191 h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_LOADER_PROC_FAILED');
6192 ELSIF h_error_code = LC_UPLOAD_OPEN_ERR THEN
6193 h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_SRCFILE_FAILED');
6194 ELSIF h_error_code = LC_UPLOAD_NUM_COLS_ERR THEN
6195 h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_SRC_FIELDNUMBER_FAILED');
6196 ELSIF h_error_code = LC_UPLOAD_INV_KEY_ERR THEN
6197 h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_SRC_NULL_KEYVALUES');
6198 ELSIF h_error_code = LC_UPLOAD_INSERT_ERR THEN
6199 h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_INVALID_DATATYPE');
6200 ELSIF h_error_code = LC_UPLOAD_NOT_FOUND_ERR THEN
6201 h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_SRCFILE_NOT_FOUND');
6202 ELSIF h_error_code = LC_UPLOAD_EXCEL_ERR THEN
6203 h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_EXCEL_CONNECTION_FAILED');
6204 ELSIF h_error_code = LC_UPLOAD_SP_NOT_FOUND_ERR THEN
6205 h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_NOT_FOUND');
6206 ELSIF h_error_code = LC_UPLOAD_SP_INVALID_ERR THEN
6207 h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_IS_INVALID');
6208 ELSIF h_error_code = LC_UPLOAD_SP_EXECUTION_ERR THEN
6209 h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_FAILED');
6210 END IF;
6211
6212 ELSIF h_status = LC_NO_DATA_STATUS THEN
6213 h_line := h_line||BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_UPDATE_FAILED')||' '||
6214 BSC_UPDATE_UTIL.Get_Message('BSC_ITABLE_EMPTY');
6215 ELSIF h_status = LC_COMPLETED_STATUS THEN
6216 h_line := h_line||BSC_UPDATE_UTIL.Get_Message('BSC_UPDATED_STATUS');
6217 END IF;
6218
6219 BSC_UPDATE_LOG.Write_Line_Log(h_line, BSC_UPDATE_LOG.OUTPUT);
6220
6221 FETCH c_status INTO h_input_table_name, h_status, h_error_code;
6222 END LOOP;
6223 CLOSE c_status;
6224
6225 -- Invalid codes
6226 OPEN c_invalid_codes(g_process_id);
6227 FETCH c_invalid_codes INTO h_input_table_name, h_column_name, h_invalid_code;
6228 IF c_invalid_codes%FOUND THEN
6229 BSC_UPDATE_LOG.Write_Line_Log('', BSC_UPDATE_LOG.OUTPUT);
6230 BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
6231 BSC_UPDATE_LOG.OUTPUT);
6232 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'INVALID_RECORDS'),
6233 BSC_UPDATE_LOG.OUTPUT);
6234 BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
6235 BSC_UPDATE_LOG.OUTPUT);
6236 BSC_UPDATE_LOG.Write_Line_Log(RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'INPUT_TABLE_NAME'), C_TABLE_W)||
6237 RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'COLUMN'), C_COLUMN_W)||
6238 RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'INVALID_CODE'), C_INVALID_CODE_W),
6239 BSC_UPDATE_LOG.OUTPUT);
6240 END IF;
6241
6242 WHILE c_invalid_codes%FOUND LOOP
6243 BSC_UPDATE_LOG.Write_Line_Log(RPAD(h_input_table_name, C_TABLE_W)||
6244 RPAD(h_column_name, C_COLUMN_W)||
6245 RPAD(h_invalid_code, C_INVALID_CODE_W), BSC_UPDATE_LOG.OUTPUT);
6246 FETCH c_invalid_codes INTO h_input_table_name, h_column_name, h_invalid_code;
6247 END LOOP;
6248 CLOSE c_invalid_codes;
6249
6250
6251 -- Update periods
6252 IF g_process_name = PC_LOADER_PROCESS THEN
6253 BSC_UPDATE_LOG.Write_Line_Log('', BSC_UPDATE_LOG.OUTPUT);
6254 BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
6255 BSC_UPDATE_LOG.OUTPUT);
6256 BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'TABLE_UPDATE_PERIOD'),
6257 BSC_UPDATE_LOG.OUTPUT);
6258 BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
6259 BSC_UPDATE_LOG.OUTPUT);
6260 BSC_UPDATE_LOG.Write_Line_Log(RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'INPUT_TABLE_NAME'), C_TABLE_NAME_W)||
6261 RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'PERIODICITY'), C_PERIODICITY_W)||
6262 RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'PERIOD'), C_PERIOD_W),
6263 BSC_UPDATE_LOG.OUTPUT);
6264
6265 --OPEN c_tables FOR c_tables_sql USING g_process_id;
6266 OPEN c_tables(g_process_id);
6267 FETCH c_tables INTO h_input_table_name, h_periodicity_id, h_periodicity_name, h_current_period, h_current_subperiod;
6268
6269 WHILE c_tables%FOUND LOOP
6270 h_line := RPAD(h_input_table_name, C_TABLE_NAME_W)||RPAD(h_periodicity_name, C_PERIODICITY_W)||TO_CHAR(h_current_period);
6271
6272 h_periodicity_type := BSC_UPDATE_UTIL.Get_Periodicity_Type(h_periodicity_id);
6273
6274 IF h_periodicity_type = 11 OR h_periodicity_type = 12 THEN
6275 h_line := h_line||', '||TO_CHAR(h_current_subperiod);
6276 END IF;
6277
6278 BSC_UPDATE_LOG.Write_Line_Log(h_line, BSC_UPDATE_LOG.OUTPUT);
6279
6280 FETCH c_tables INTO h_input_table_name, h_periodicity_id, h_periodicity_name, h_current_period, h_current_subperiod;
6281 END LOOP;
6282 CLOSE c_tables;
6283 END IF;
6284
6285 BSC_UPDATE_LOG.Write_Line_Log('', BSC_UPDATE_LOG.OUTPUT);
6286
6287 RETURN TRUE;
6288
6289 EXCEPTION
6290 WHEN e_unexpected_error THEN
6291 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_WR_LOGFILE_RES_FAILED'),
6292 x_source => 'BSC_UPDATE.Write_Result_Log');
6293 RETURN FALSE;
6294
6295 WHEN OTHERS THEN
6296 BSC_MESSAGE.Add(x_message => SQLERRM,
6297 x_source => 'BSC_UPDATE.Write_Result_Log');
6298 RETURN FALSE;
6299
6300 END Write_Result_Log;
6301
6302 /*===========================================================================+
6303 | FUNCTION Get_Indicator_List
6304 +============================================================================*/
6305 FUNCTION Get_Indicator_List(x_number_array IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number)
6306 RETURN NUMBER IS
6307 CURSOR cIndics is
6308 SELECT value_n
6309 FROM BSC_TMP_BIG_IN_COND
6310 WHERE session_id = -500 and variable_id = -500;
6311 indicator NUMBER;
6312 h_num_items NUMBER;
6313 BEGIN
6314 h_num_items := 0;
6315 OPEN cIndics;
6316 FETCH cIndics INTO indicator;
6317 WHILE cIndics%FOUND LOOP
6318 h_num_items := h_num_items + 1;
6319 x_number_array(h_num_items) := indicator;
6320 FETCH cIndics INTO indicator;
6321 END LOOP;
6322 CLOSE cIndics;
6323 DELETE BSC_TMP_BIG_IN_COND where session_id = -500 and variable_id = -500;
6324 RETURN h_num_items;
6325 END Get_Indicator_List;
6326
6327
6328 --Fix bug#4681065
6329 /*===========================================================================+
6330 | PROCEDURE Write_Warning_Kpis_In_Prot
6331 +============================================================================*/
6332 PROCEDURE Write_Warning_Kpis_In_Prot (
6333 x_system_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
6334 x_num_system_tables IN NUMBER
6335 ) IS
6336
6337 h_where_cond VARCHAR2(32700);
6338 h_i NUMBER;
6339 h_sql VARCHAR2(32700);
6340 TYPE t_cursor IS REF CURSOR;
6341 h_cursor t_cursor;
6342 h_kpi NUMBER;
6343 h_name VARCHAR2(2000);
6344 h_message VARCHAR2(5000);
6345
6346 BEGIN
6347 IF g_kpi_mode THEN
6348 h_where_cond := BSC_APPS.Get_New_Big_In_Cond_Number(1, 'indicator');
6349 FOR h_i IN 1 .. g_num_indicators LOOP
6350 BSC_APPS.Add_Value_Big_In_Cond(1, g_indicators(h_i));
6351 END LOOP;
6352
6353 h_sql := 'select indicator, name'||
6354 ' from bsc_kpis_vl'||
6355 ' where prototype_flag NOT IN (:1, :2, :3) and '||h_where_cond;
6356 h_i := 0;
6357 OPEN h_cursor FOR h_sql USING 0, 6, 7;
6358 LOOP
6359 FETCH h_cursor INTO h_kpi, h_name;
6360 EXIT WHEN h_cursor%NOTFOUND;
6361
6362 IF h_i = 0 THEN
6363 h_message := BSC_UPDATE_UTIL.Get_Message('BSC_CANNOT_LOAD_OBJS_IN_PROT');
6364 BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
6365 h_i := 1;
6366 END IF;
6367 BSC_UPDATE_LOG.Write_Line_Log(h_kpi||' '||h_name, BSC_UPDATE_LOG.OUTPUT);
6368 g_warning := TRUE;
6369 END LOOP;
6370 CLOSE h_cursor;
6371 ELSE
6372 h_where_cond := BSC_APPS.Get_New_Big_In_Cond_Varchar2(1, 'table_name');
6373 FOR h_i IN 1 .. x_num_system_tables LOOP
6374 BSC_APPS.Add_Value_Big_In_Cond(1, x_system_tables(h_i));
6375 END LOOP;
6376
6377 h_sql := 'select indicator, name'||
6378 ' from bsc_kpis_vl'||
6379 ' where prototype_flag NOT IN (:1, :2, :3) and'||
6380 ' indicator in (select indicator'||
6381 ' from bsc_kpi_data_tables'||
6382 ' where '||h_where_cond||
6383 ' )';
6384 h_i := 0;
6385 OPEN h_cursor FOR h_sql USING 0, 6, 7;
6386 LOOP
6387 FETCH h_cursor INTO h_kpi, h_name;
6388 EXIT WHEN h_cursor%NOTFOUND;
6389
6390 IF h_i = 0 THEN
6391 h_message := BSC_UPDATE_UTIL.Get_Message('BSC_CANNOT_LOAD_OBJS_IN_PROT');
6392 BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
6393 h_i := 1;
6394 END IF;
6395 BSC_UPDATE_LOG.Write_Line_Log(h_kpi||' '||h_name, BSC_UPDATE_LOG.OUTPUT);
6396 g_warning := TRUE;
6397 END LOOP;
6398 CLOSE h_cursor;
6399 END IF;
6400 END Write_Warning_Kpis_In_Prot;
6401
6402
6403 END BSC_UPDATE;
[Home] [Help]