1 PACKAGE BSC_UPDATE AS
2 /* $Header: BSCDUPDS.pls 120.1 2005/11/16 12:26:13 meastmon noship $ */
3 --
4 -- Global Constants
5 --
6 -- Process name
7 PC_INCREMENTAL_PROCESS CONSTANT VARCHAR2(1) := 'I';
8 PC_LOADER_PROCESS CONSTANT VARCHAR2(1) := 'L';
9 PC_YEAR_CHANGE_PROCESS CONSTANT VARCHAR2(1) := 'Y';
10 PC_DELETE_KPI_DATA_PROCESS CONSTANT VARCHAR2(1) := 'D';
11 PC_REFRESH_EDW_DIMENSION CONSTANT VARCHAR2(1) := 'M';
12 PC_LOAD_DIMENSIONS CONSTANT VARCHAR2(1) := 'P';
13
14 -- Process status
15 PC_PENDING_STATUS CONSTANT VARCHAR2(1) := 'P';
16 PC_RUNNING_STATUS CONSTANT VARCHAR2(1) := 'R';
17 PC_ERROR_STATUS CONSTANT VARCHAR2(1) := 'E';
18 PC_COMPLETED_STATUS CONSTANT VARCHAR2(1) := 'C';
19
20 -- Input tables status
21 LC_PENDING_STATUS CONSTANT VARCHAR2(1) := 'P';
22 LC_RUNNING_STATUS CONSTANT VARCHAR2(1) := 'R';
23 LC_ERROR_STATUS CONSTANT VARCHAR2(1) := 'E';
24 LC_NO_DATA_STATUS CONSTANT VARCHAR2(1) := 'N';
25 LC_COMPLETED_STATUS CONSTANT VARCHAR2(1) := 'C';
26
27 -- Input tables stages
28 LC_PENDING_STAGE CONSTANT VARCHAR2(1) := 'P';
29 LC_UPLOADED_STAGE CONSTANT VARCHAR2(1) := 'U';
30 LC_VALIDATED_STAGE CONSTANT VARCHAR2(1) := 'V';
31 LC_BASE_UPDATED_STAGE CONSTANT VARCHAR2(1) := 'B';
32 LC_SYSTEM_UPDATED_STAGE CONSTANT VARCHAR2(1) := 'S';
33 LC_COMPLETED_STAGE CONSTANT VARCHAR2(1) := 'C';
34
35 -- Input tables error codes
36 LC_INVALID_CODES_ERR CONSTANT VARCHAR2(25) := 'INVALID_CODES_ERR';
37 LC_PROGRAM_ERR CONSTANT VARCHAR2(25) := 'PROGRAM_ERR';
38 LC_UPLOAD_OPEN_ERR CONSTANT VARCHAR2(25) := 'UPLOAD_OPEN_ERR';
39 LC_UPLOAD_NUM_COLS_ERR CONSTANT VARCHAR2(25) := 'UPLOAD_NUM_COLS_ERR';
40 LC_UPLOAD_INV_KEY_ERR CONSTANT VARCHAR2(25) := 'UPLOAD_INV_KEY_ERR';
41 LC_UPLOAD_INSERT_ERR CONSTANT VARCHAR2(25) := 'UPLOAD_INSERT_ERR';
42 LC_UPLOAD_NOT_FOUND_ERR CONSTANT VARCHAR2(25) := 'UPLOAD_NOT_FOUND_ERR';
43 LC_UPLOAD_EXCEL_ERR CONSTANT VARCHAR2(25) := 'UPLOAD_EXCEL_ERR';
44 LC_UPLOAD_SP_NOT_FOUND_ERR CONSTANT VARCHAR2(25) := 'UPLOAD_SP_NOT_FOUND_ERR';
45 LC_UPLOAD_SP_INVALID_ERR CONSTANT VARCHAR2(25) := 'UPLOAD_SP_INVALID_ERR';
46 LC_UPLOAD_SP_EXECUTION_ERR CONSTANT VARCHAR2(25) := 'UPLOAD_SP_EXECUTION_ERR';
47
48 --
49 -- Global variables
50 --
51 g_debug_flag VARCHAR2(3);
52
53 g_session_id NUMBER;
54 g_user_id NUMBER;
55 g_schema_name VARCHAR2(30);
56
57 g_indicators BSC_UPDATE_UTIL.t_array_of_number;
58 g_num_indicators NUMBER;
59 g_kpi_mode BOOLEAN;
60 g_keep_input_table_data varchar2(20);
61
62 --Bug#4681065
63 g_warning BOOLEAN;
64
65 --
66 -- Procedures and Functions
67 --
68
69 /*===========================================================================+
70 |
71 | Name: Can_Calculate_Sys_Table
72 |
73 | Description: This function returns TRUE when the given system table
74 | can be calculated. The table can be calculated if all tables
75 | from where the table is originated and are part of current
76 | process have been calculated previously.
77 | The tables that have been calculated previously are in the
78 | array x_calculated_sys_tables.
79 |
80 | Parameters:
81 |
82 | Returns: If some error occurr this function returns NULL
83 |
84 | Notes:
85 |
86 +============================================================================*/
87 FUNCTION Can_Calculate_Sys_Table(
88 x_system_table IN VARCHAR2,
89 x_calculated_sys_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
90 x_num_calculated_sys_tables IN NUMBER,
91 x_system_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
92 x_num_system_tables IN NUMBER
93 ) RETURN BOOLEAN;
94
95
96 /*===========================================================================+
97 |
98 | Name: Can_Load_Dim_Table
99 |
100 | Description: This function returns TRUE when the given dimension table
101 | can be loaded. The table can be loaded if all the parent
102 | tables which are part of current process have been loaded
103 | previously.
104 | The tables that have been loaded previously are in the
105 | array x_loaded_tables.
106 |
107 | Parameters:
108 |
109 | Returns: If some error occurr this function returns NULL
110 |
111 | Notes:
112 |
113 +============================================================================*/
114 FUNCTION Can_Load_Dim_Table(
115 x_dim_table IN VARCHAR2,
116 x_loaded_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
117 x_num_loaded_tables IN NUMBER,
118 x_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
119 x_num_dim_tables IN NUMBER
120 ) RETURN BOOLEAN;
121
122
123 /*===========================================================================+
124 |
125 | Name: Configure_Periodicity_Calc
126 |
127 | Description: This function configure the periodicity calculation
128 | in the given base table. It looks all the periodicities
129 | required in the dependent tables.
130 | Returns False in case of error along with the error
131 | message in x_error_message
132 |
133 | Notes:
134 |
135 +============================================================================*/
136 FUNCTION Configure_Periodicity_Calc(
137 p_base_table IN VARCHAR2,
138 x_error_message OUT NOCOPY VARCHAR2
139 ) RETURN BOOLEAN;
140
141
142 /*===========================================================================+
143 |
144 | Name: Configure_Periodicity_Calc_VB
145 |
146 | Description: This procedure configure the periodicity calculation
147 | in the given base table. It looks all the periodicities
148 | required in the dependent tables.
149 | It is called from Metadata Optmizer.
150 | In case of error, it insert the error message in
151 | BSC_MESSAGE_LOGS.
152 |
153 | Notes:
154 |
155 +============================================================================*/
156 PROCEDURE Configure_Periodicity_Calc_VB(
157 p_base_table IN VARCHAR2
158 );
159
160
161 /*===========================================================================+
162 |
163 | Name: Configure_Profit_Calc
164 |
165 | Description: This function configure the profit calculation in the
166 | base tables that require this calculation.
167 | Returns False in case of error along with the error
168 | message in x_error_message
169 |
170 | Notes:
171 |
172 +============================================================================*/
173 FUNCTION Configure_Profit_Calc(
174 x_error_message OUT NOCOPY VARCHAR2
175 ) RETURN BOOLEAN;
176
177
178 /*===========================================================================+
179 |
180 | Name: Configure_Profit_Calc_VB
181 |
182 | Description: This procedure configure the profit calculation in the
183 | base tables that require this calculation.
184 | It is called from Metadata Optmizer.
185 | In case of error, it insert the error message in
186 | BSC_MESSAGE_LOGS.
187 |
188 | Notes:
189 |
190 +============================================================================*/
191 PROCEDURE Configure_Profit_Calc_VB;
192
193
194 /*===========================================================================+
195 |
196 | Name: Execute_Update_Process
197 |
198 | Description: This is the main procedure that runs the update process.
199 | If some error ocurrs the error message is written in
200 | BSC_MESSAGE_LOGS table with TYPE = 0.
201 |
202 | Parameters: x_process_id - Process ID
203 | x_process_name - Process name: 'L' - Load input tables
204 | 'Y' - Year change process
205 | 'D' - Delete kpis data
206 | x_parameter_1 - Defaults to NULL. Applies only for delete
207 | kpis data process. In this case this
208 | parameter contains a list of kpi codes
209 | that are the indicators whose data is
210 | going to be deleted.
211 |
212 | Returns:
213 |
214 | Notes: The process to be run is in BSC_DB_PROCESS_CONTROL table
215 | with pending status. The input tables are registered in
216 | BSC_DB_LOADER_CONTROL table with the corresponding process id.
217 |
218 +============================================================================*/
219 PROCEDURE Execute_Update_Process (
220 x_process_id IN NUMBER,
221 x_process_name IN VARCHAR2,
222 x_parameter_1 IN VARCHAR2
223 );
224
225
226 /*===========================================================================+
227 |
228 | Name: Execute_Year_Change_Process
229 |
230 | Description: This function executrs the year change process for the given
231 | calendar.
232 |
233 | Parameters:
234 | x_calendar_id: calendar id (-1 for BSC calendar)
235 |
236 | Returns: Return true if the process completres successfully.
237 | Otherwise returns FALSE.
238 |
239 +============================================================================*/
240 FUNCTION Execute_Year_Change_Process (
241 x_calendar_id IN NUMBER
242 ) RETURN BOOLEAN;
243
244 --LOCKING: new function
245 FUNCTION Execute_Year_Change_Process_AT(
246 x_calendar_id IN NUMBER
247 ) RETURN BOOLEAN;
248
249
250 /*===========================================================================+
251 |
252 | Name: Exists_Prototype_Indicators
253 |
254 | Description: This function say if there is any indicator in prototype
255 | mode. In this case Loader cannot continue until Metadata
256 | be run on that indicators.
257 |
258 | Parameters:
259 |
260 | Returns:
261 | Return TRUE if there is any indicator in prototype mode.
262 | Otherwise return FALSE.
263 | If some error occurs return NULL.
264 |
265 | Notes:
266 |
267 +============================================================================*/
268 FUNCTION Exists_Prototype_Indicators RETURN BOOLEAN;
269
270
271 /*===========================================================================+
272 |
273 | Name: Flag_Last_Stage_Input_Table
274 |
275 | Description: This function set the last stage flag of the given input
276 | table of the current process.
277 |
278 | Parameters: x_input_table - input table name
279 |
280 | Returns: If any error ocurrs, this function add the error message
281 | to the error stack and return FALSE. Otherwise return
282 | TRUE
283 |
284 | Notes:
285 |
286 +============================================================================*/
287 FUNCTION Flag_Last_Stage_Input_Table(
288 x_input_table IN VARCHAR2
289 ) RETURN BOOLEAN;
290
291
292 /*===========================================================================+
293 |
294 | Name: Get_Base_Table_Of_Input_Table
295 |
296 | Description: This function return in the parameter x_base_table
297 | the base table of the input table given in the parameter
298 | x_input_table.
299 |
300 | Parameters: x_input_table - input table name
301 | x_base_table - base table name
302 |
303 | Returns: If any error ocurrs, this function add the error message
304 | to the error stack and return FALSE. Otherwise return
305 | TRUE
306 |
307 | Notes:
308 |
309 +============================================================================*/
310 FUNCTION Get_Base_Table_Of_Input_Table(
311 x_input_table IN VARCHAR2,
312 x_base_table OUT NOCOPY VARCHAR2
313 ) RETURN BOOLEAN;
314
315
316 /*===========================================================================+
317 |
318 | Name: Get_EDW_Dims_In_Input_Tables
319 |
320 | Description: This function initialize the array x_edw_dim_tables
321 | with the name of the edw dimensions in the input tables
322 | that belong to the current process.
323 |
324 | Parameters: x_edw_dim_tables - array to return the dimension tables
325 | x_num_edw_dim_tables - number of dimension tables in the array
326 |
327 | Returns: If any error ocurrs, this function add the error message
328 | to the error stack and return FALSE. Otherwise return
329 | TRUE
330 |
331 | Notes:
332 |
333 +============================================================================*/
334 FUNCTION Get_EDW_Dims_In_Input_Tables (
335 x_edw_dim_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
336 x_num_edw_dim_tables IN OUT NOCOPY NUMBER
337 ) RETURN BOOLEAN;
338
339
340 /*===========================================================================+
341 |
342 | Name: Get_Indicators_To_Color
343 |
344 | Description: This function returns in the array x_color_indicators the
345 | indicators to be colored because some table
346 | that use the indicator was calculated.
347 |
348 | Parameters: x_base_tables_to_color - base tables to identify which
349 | indicators need to be colored
350 | x_num_base_tables_to_color - number of base tables
351 | x_color_indicators - array where this function put the
352 | indicator to color
353 | x_num_color_indicators - number of indicators
354 |
355 | Returns: If any error ocurrs, this function add the error message
356 | to the error stack and return FALSE. Otherwise return
357 | TRUE
358 |
359 | Notes:
360 |
361 +============================================================================*/
362 FUNCTION Get_Indicators_To_Color(
366 x_num_color_indicators IN OUT NOCOPY NUMBER
363 x_base_tables_to_color IN BSC_UPDATE_UTIL.t_array_of_varchar2,
364 x_num_base_tables_to_color IN NUMBER,
365 x_color_indicators IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
367 ) RETURN BOOLEAN;
368
369
370 /*===========================================================================+
371 |
372 | Name: Get_Last_Stage_Input_Table
373 |
374 | Description: This function return in the parameter x_last_stage
375 | the last stage of the input table. The last stage has a
376 | flag in the column last_stage_flag of bsc_db_loader_control
377 | table. One input table has only one flagged row.
378 |
379 | Parameters: x_input_table - input table name
380 | x_last_stage - last stage of the input table. If the input
381 | table still doesn't have last stage then
382 | the function set it to '?'.
383 |
384 | Returns: If any error ocurrs, this function add the error message
385 | to the error stack and return FALSE. Otherwise return
386 | TRUE
387 |
388 | Notes:
389 |
390 +============================================================================*/
391 FUNCTION Get_Last_Stage_Input_Table(
392 x_input_table IN VARCHAR2,
393 x_last_stage OUT NOCOPY VARCHAR2
394 ) RETURN BOOLEAN;
395
396
397 /*===========================================================================+
398 |
399 | Name: Get_Process_Id
400 |
401 | Description: This function returns the process id to be executed.
402 | If the porcess identified by x_process_id has pending status
403 | then this is the process to be executed. Otherwise, this function
404 | creates a new process with the same configuration of the given one
405 | (example, same input tables, or same kpis to be deleted)
406 |
407 | If the given process does not exists then retunrns -1.
408 |
409 | Parameters: x_process_id - Process id
410 | x_process_name - Process name
411 |
412 | Returns: If any error ocurrs, this function add the error message
413 | to the error stack and return NULL. Otherwise return
414 | TRUE
415 |
416 | Notes:
417 |
418 +============================================================================*/
419 FUNCTION Get_Process_Id (
420 x_process_id IN NUMBER,
421 x_process_name IN VARCHAR2
422 ) RETURN NUMBER;
423
424
425 /*===========================================================================+
426 |
427 | Name: Get_Process_Input_Tables
428 |
429 | Description: This function initialize the array x_input_tables
430 | with the name of the input tables that belong to the
431 | current process and its status is the given in the
432 | parameter x_status.
433 | (see bsc_db_loader_control)
434 |
435 | Parameters: x_input_tables - array to return the input tables
436 | x_num_input_tables - number of input tables returned
437 | x_status - status
438 |
439 | Returns: If any error ocurrs, this function add the error message
440 | to the error stack and return FALSE. Otherwise return
441 | TRUE
442 |
443 | Notes:
444 |
445 +============================================================================*/
446 FUNCTION Get_Process_Input_Tables (
447 x_input_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
448 x_num_input_tables IN OUT NOCOPY NUMBER,
449 x_status IN VARCHAR2
450 ) RETURN BOOLEAN;
451
452
453 /*===========================================================================+
454 |
455 | Name: Init_Env_Values
456 |
457 | Description: This function get the session id, user id and schema name
458 | in the global variables g_session_id, g_user_id and
459 | g_schema_name.
460 |
461 | Returns: If any error occurs, this function add the error message
462 | to the error stack and return FALSE. Otherwise return
463 | TRUE.
464 |
465 | Notes:
466 |
467 +============================================================================*/
468 FUNCTION Init_Env_Values RETURN BOOLEAN;
469
470
471
472 /*===========================================================================+
473 |
474 | Name: Import_ITables_From_DBSrc
475 |
476 | Description: This function import data into input tables from database
477 | sources (i.e stored procedures)
478 |
479 | Returns: If any error occurs, this function add the error message
480 | to the error stack and return FALSE. Otherwise return
481 | TRUE.
482 |
483 | Notes:
484 |
485 +============================================================================*/
486 FUNCTION Import_ITables_From_DBSrc(
487 x_input_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
488 x_num_input_tables IN NUMBER
489 ) RETURN BOOLEAN;
490
491 --LOCKING: new function
492 FUNCTION Import_ITables_From_DBSrc_AT(
493 x_input_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
497
494 x_num_input_tables IN NUMBER
495 ) RETURN BOOLEAN;
496
498 /*===========================================================================+
499 |
500 | Name: Import_ITable_StoredProc
501 |
502 | Description: This function import data into the input table from a
503 | stored procedure.
504 |
505 | Returns: If any error occurs, this function add the error message
506 | to the error stack and return FALSE. Otherwise return
507 | TRUE.
508 |
509 | Notes:
510 |
511 +============================================================================*/
512 FUNCTION Import_ITable_StoredProc(
513 x_input_table IN VARCHAR2,
514 x_stored_proc IN VARCHAR2
515 ) RETURN BOOLEAN;
516
517
518 --LOCKING: new function
519 /*===========================================================================+
520 | FUNCTION Import_ITable_StoredProc_AT
521 +============================================================================*/
522 FUNCTION Import_ITable_StoredProc_AT(
523 x_input_table IN VARCHAR2,
524 x_stored_proc IN VARCHAR2
525 ) RETURN BOOLEAN;
526
527
528 /*===========================================================================+
529 |
530 | Name: Insert_Affected_Tables
531 |
532 | Description: This recursive function insert into the array
533 | x_affected_tables the tables in the graph that are
534 | affected by tables in the array x_tables.
535 |
536 | Parameters: x_tables -array of table names
537 | x_num_tables -number of tables
538 | x_affected_tables - array to add the affected tables
539 | x_num_affected_tables - number of tables
540 |
541 | Returns: If any error ocurrs, this function add the error message
542 | to the error stack and return FALSE. Otherwise return
543 | TRUE
544 | Notes:
545 |
546 +============================================================================*/
547 FUNCTION Insert_Affected_Tables (
548 x_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
549 x_num_tables IN NUMBER,
550 x_affected_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
551 x_num_affected_tables IN OUT NOCOPY NUMBER
552 ) RETURN BOOLEAN;
553
554
555 /*===========================================================================+
556 |
557 | Name: Load_Dim_Input_Tables
558 |
559 | Description: This function loads data into dimension tables from the
560 | input tables given in the array
561 |
562 | Parameters: x_input_tables - array of input tables to be processed
563 | x_num_input_tables - number of input tables
564 |
565 | Returns: If any error ocurrs, this function add the error message
566 | to the error stack and return FALSE. Otherwise return
567 | TRUE
568 |
569 | Notes:
570 |
571 +============================================================================*/
572 FUNCTION Load_Dim_Input_Tables(
573 x_input_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
574 x_num_input_tables IN NUMBER
575 ) RETURN BOOLEAN;
576
577
578 --AW_INTEGRATION: New function
579 /*===========================================================================+
580 |
581 | Name: Load_Dims_Into_AW
582 |
583 | Description: This function loads the given dimensions into AW
584 | It does in a proper oder parents first then the childs.
585 |
586 +============================================================================*/
587 FUNCTION Load_Dims_Into_AW(
588 x_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
589 x_num_dim_tables IN NUMBER
590 ) RETURN BOOLEAN;
591
592
593 /*===========================================================================+
594 |
595 | Name: Process_Input_Tables
596 |
597 | Description: This function runs the input tables update process.
598 | This function process the input tables in the array
599 | x_input_tables.
600 |
601 | Parameters: x_input_tables - array of input tables to be processed
602 | x_num_input_tables - number of input tables
603 | x_start_from - 0 The process starts from input
604 | tables. This means that the program
605 | takes input tables, make codes
606 | validation, updates base tables, etc.
607 | - 1 The process starts from system tables.
608 | input tables given in the array already
609 | are updated and the process starts
610 | calculating the system tables affected
611 | by those input tables.
612 |
613 | Returns: If any error ocurrs, this function add the error message
614 | to the error stack and return FALSE. Otherwise return
615 | TRUE
616 |
617 | Notes:
618 |
619 +============================================================================*/
620 FUNCTION Process_Input_Tables(
621 x_input_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
622 x_num_input_tables IN NUMBER,
626 --LOCKING: new function
623 x_start_from IN NUMBER
624 ) RETURN BOOLEAN;
625
627 FUNCTION Process_Input_Tables_AT(
628 x_input_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
629 x_num_input_tables IN NUMBER,
630 x_start_from IN NUMBER
631 ) RETURN BOOLEAN;
632
633
634 /*===========================================================================+
635 |
636 | Name: Refresh_System_MVs
637 |
638 | Description: Refresh the materialized views in the system that depends
639 | on the given base tables. It has to do it in the right order
640 |
641 | In case of error returns FALSE the error messase.
642 |
643 | Notes:
644 |
645 +============================================================================*/
646 FUNCTION Refresh_System_MVs(
647 p_base_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
648 p_num_base_tables IN NUMBER
649 ) RETURN BOOLEAN;
650
651 FUNCTION Refresh_System_MVs_Mig(
652 p_base_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
653 p_num_base_tables IN NUMBER
654 ) RETURN BOOLEAN;
655
656
657 /*===========================================================================+
658 |
659 | Name: Run_Concurrent_Loader
660 |
661 | Description: This procedure is to run the loader as a concurrent
662 | program from VB tools
663 |
664 | Parameters: ERRBUF This is a required argument. It is used
665 | to return any error message.
666 | RETCODE This is a required argument. It is used
667 | to return completion status: 0 for success
668 | 1 for success with warnings and 2 for error.
669 | After the concurrent program runs, the
670 | concurrent manager writes the contents of both
671 | ERRBUF and RETCODE to the log file associated
672 | with the concurrent request.
673 | x_process_id - Process id
674 | x_process_name - Process name: 'L' - Load input tables
675 | 'Y' - Year change process
676 | 'D' - Delete kpis data
677 | 'I' - Incremental
678 | x_parameter_1 - Defaults to NULL. Applies only for delete
679 | kpis data process. In this case this
680 | parameter contains a list of kpi codes
681 | that are the indicators whose data is
682 | going to be deleted.
683 |
684 | Note: For Standard Request Submission we only allow to pass x_process_name = 'L'
685 | and x_parameter_1 = NULL (Use Run_Concurrent_Loader_Apps)
686 |
687 +============================================================================*/
688 PROCEDURE Run_Concurrent_Loader (
689 ERRBUF OUT NOCOPY VARCHAR2,
690 RETCODE OUT NOCOPY VARCHAR2,
691 x_process_id IN VARCHAR2,
692 x_process_name IN VARCHAR2,
693 x_parameter_1 IN VARCHAR2
694 );
695
696
697 /*===========================================================================+
698 |
699 | Name: Run_Concurrent_Loader_Apps
700 |
701 | Description: This procedure is to run the loader as a concurrent
702 | program from standard request submission
703 |
704 | Parameters: ERRBUF This is a required argument. It is used
705 | to return any error message.
706 | RETCODE This is a required argument. It is used
707 | to return completion status: 0 for success
708 | 1 for success with warnings and 2 for error.
709 | After the concurrent program runs, the
710 | concurrent manager writes the contents of both
711 | ERRBUF and RETCODE to the log file associated
712 | with the concurrent request.
713 | x_process_id Process Id
714 |
715 | Note: For Standard Request Submission we only allow to pass x_process_name = 'L'
716 | and x_parameter_1 = NULL
717 |
718 +============================================================================*/
719 PROCEDURE Run_Concurrent_Loader_Apps (
720 ERRBUF OUT NOCOPY VARCHAR2,
721 RETCODE OUT NOCOPY VARCHAR2,
722 x_process_id IN VARCHAR2
723 );
724 PROCEDURE Run_Concurrent_Loader_Apps (
725 ERRBUF OUT NOCOPY VARCHAR2,
726 RETCODE OUT NOCOPY VARCHAR2,
727 x_process_id IN VARCHAR2,
728 x_load_dim_affected_indicators varchar2
729 );
730
731 /*===========================================================================+
732 |
733 | Name: Run_Concurrent_Loader_Dim_Apps
734 |
735 | Description: This procedure is to run the loader of dimension tables
736 | as a concurrent program from standard request submission
737 |
738 | Parameters: ERRBUF This is a required argument. It is used
739 | to return any error message.
740 | RETCODE This is a required argument. It is used
744 | concurrent manager writes the contents of both
741 | to return completion status: 0 for success
742 | 1 for success with warnings and 2 for error.
743 | After the concurrent program runs, the
745 | ERRBUF and RETCODE to the log file associated
746 | with the concurrent request.
747 | x_process_id Process Id
748 |
749 +============================================================================*/
750 PROCEDURE Run_Concurrent_Loader_Dim_Apps (
751 ERRBUF OUT NOCOPY VARCHAR2,
752 RETCODE OUT NOCOPY VARCHAR2,
753 x_process_id IN VARCHAR2
754 );
755
756 /*===========================================================================+
757 |
758 | Name: Run_change_current_year
759 |
760 | Description: This procedure is to run the change current year process
761 |
762 | Parameters: ERRBUF This is a required argument. It is used
763 | to return any error message.
764 | RETCODE This is a required argument. It is used
765 | to return completion status: 0 for success
766 | 1 for success with warnings and 2 for error.
767 | After the concurrent program runs, the
768 | concurrent manager writes the contents of both
769 | ERRBUF and RETCODE to the log file associated
770 | with the concurrent request.
771 | This is the new procedure to change current year
772 | This is same as calling Submit Request to Load Input Tables (VB) (Flag = Y)
773 | This is for new OA UI. this api in contrast to
774 | Submit Request to Load Input Tables (VB) (Flag = Y)
775 | will acquire lock. Submit Request to Load Input Tables (VB) (Flag = Y) is launched from VB
776 | where the lock is already there with VB
777 | x_process_id Process Id
778 |
779 +============================================================================*/
780 /*
781
782 */
783 PROCEDURE Run_change_current_year (
784 ERRBUF OUT NOCOPY VARCHAR2,
785 RETCODE OUT NOCOPY VARCHAR2,
786 x_process_id IN VARCHAR2,
787 x_calendars IN VARCHAR2
788 );
789
790 /*===========================================================================+
791 |
792 | Name: Load_Indicators_Data
793 |
794 | Description: This procedure is to run the loader for the input
795 | tables used by the given indicators.
796 |
797 | Parameters: ERRBUF This is a required argument. It is used
798 | to return any error message.
799 | RETCODE This is a required argument. It is used
800 | to return completion status: 0 for success
801 | 1 for success with warnings and 2 for error.
802 | After the concurrent program runs, the
803 | concurrent manager writes the contents of both
804 | ERRBUF and RETCODE to the log file associated
805 | with the concurrent request.
806 | x_indicators List of indicators. Example: 3001,3002,3004
807 |
808 +============================================================================*/
809 PROCEDURE Load_Indicators_Data (
810 ERRBUF OUT NOCOPY VARCHAR2,
811 RETCODE OUT NOCOPY VARCHAR2,
812 x_indicators IN VARCHAR2,
813 x_load_affected_indicators varchar2
814 );
815
816
817
818 /*===========================================================================+
819 |
820 | Name: Load_Indicators_Dims
821 |
822 | Description: This procedure is to run the loader for the input
823 | tables for dimensions used by the given indicators.
824 |
825 | Parameters: ERRBUF This is a required argument. It is used
826 | to return any error message.
827 | RETCODE This is a required argument. It is used
828 | to return completion status: 0 for success
829 | 1 for success with warnings and 2 for error.
830 | After the concurrent program runs, the
831 | concurrent manager writes the contents of both
832 | ERRBUF and RETCODE to the log file associated
833 | with the concurrent request.
834 | x_indicators List of indicators. Example: 3001,3002,3004
835 |
836 +============================================================================*/
837 PROCEDURE Load_Indicators_Dims (
838 ERRBUF OUT NOCOPY VARCHAR2,
839 RETCODE OUT NOCOPY VARCHAR2,
840 x_indicators IN VARCHAR2,
841 x_load_dim_affected_indicators varchar2
842 );
843
844
845 /*===========================================================================+
846 |
847 | Name: Delete_Indicators_Data
848 |
849 | Description: This procedure is to run the loader to delete the data
850 | of the given indicators.
851 |
852 | Parameters: ERRBUF This is a required argument. It is used
853 | to return any error message.
854 | RETCODE This is a required argument. It is used
855 | to return completion status: 0 for success
856 | 1 for success with warnings and 2 for error.
857 | After the concurrent program runs, the
858 | concurrent manager writes the contents of both
859 | ERRBUF and RETCODE to the log file associated
860 | with the concurrent request.
861 | x_indicators List of indicators. Example: 3001,3002,3004
862 |
863 +============================================================================*/
864 PROCEDURE Delete_Indicators_Data (
865 ERRBUF OUT NOCOPY VARCHAR2,
866 RETCODE OUT NOCOPY VARCHAR2,
867 x_indicators IN VARCHAR2,
868 x_keep_input_table_data IN VARCHAR2
869 );
870
871
872 /*===========================================================================+
873 |
874 | Name: Get_Input_Tables_Kpis
875 |
876 | Description: This procedure returns in the array x_input_tables
877 | the input tables used by the indicators given in the
878 | array x_indicators
879 |
880 +============================================================================*/
881 FUNCTION Get_Input_Tables_Kpis(
882 x_indicators IN BSC_UPDATE_UTIL.t_array_of_number,
883 x_num_indicators IN NUMBER,
884 x_input_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
888
885 x_num_input_tables IN OUT NOCOPY NUMBER
886 ) RETURN BOOLEAN;
887
889 /*===========================================================================+
890 |
891 | Name: Get_Dim_Input_Tables_Kpis
892 |
893 | Description: This procedure returns in the array x_input_tables
894 | the dimension input tables used by the indicators given
895 | in the array x_indicators
896 |
897 +============================================================================*/
898 FUNCTION Get_Dim_Input_Tables_Kpis(
899 x_indicators IN BSC_UPDATE_UTIL.t_array_of_number,
900 x_num_indicators IN NUMBER,
901 x_input_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
902 x_num_input_tables IN OUT NOCOPY NUMBER
903 ) RETURN BOOLEAN;
904
905
906 /*===========================================================================+
907 |
908 | Name: Set_PStatus_Finished
909 |
910 | Description: This function set the status of the current process to
911 | the given status. Additionally, set the end time to SYSDATE.
912 |
913 | Parameters: x_status - status
914 |
915 | Returns: If any error occurs, this function add the error message
916 | to the error stack and return FALSE. Otherwise return
917 | TRUE
918 |
919 | Notes:
920 |
921 +============================================================================*/
922 FUNCTION Set_PStatus_Finished(
923 x_status IN VARCHAR2
924 ) RETURN BOOLEAN;
925
926
927 /*===========================================================================+
928 |
929 | Name: Set_PStatus_Running
930 |
931 | Description: This function set the status of the current process to
932 | - Running - and update the field LOG_FILE_LOCATION with
933 | the complete name of the log file. Additionally, set the
934 | start time to SYSDATE.
935 |
936 | Parameters:
937 |
938 | Returns: If any error occurs, this function add the error message
939 | to the error stack and return FALSE. Otherwise return
940 | TRUE
941 |
942 | Notes:
943 |
944 +============================================================================*/
945 FUNCTION Set_PStatus_Running RETURN BOOLEAN;
946
947
948 --LOCKING: new function
949 FUNCTION Update_Indicator_Period (
950 x_indicator IN NUMBER
951 ) RETURN BOOLEAN;
952
953
954 /*===========================================================================+
955 |
956 | Name: Update_Indicators_Periods
957 |
958 | Description: This function update the current period of all indicators
959 | The current period of an indicator is the minimun current
960 | period of the tables used by the indicator.
961 |
962 | Parameters:
963 |
964 | Returns: If any error occurs, this function add the error message
965 | to the error stack and return FALSE. Otherwise return
966 | TRUE
967 |
968 | Notes:
969 |
970 +============================================================================*/
971 FUNCTION Update_Indicators_Periods RETURN BOOLEAN;
972
973
974 /*===========================================================================+
975 |
976 | Name: Update_Stage_Input_Table
977 |
978 | Description: This function set the stage of input table of the
979 | current process to x_target_stage.
980 |
981 | Parameters: x_input_table - input table name
982 | x_target_stage - target stage
983 |
984 | Returns: If any error occurs, this function add the error message
985 | to the error stack and return FALSE. Otherwise return
986 | TRUE
987 |
988 | Notes:
989 |
990 +============================================================================*/
991 FUNCTION Update_Stage_Input_Table(
992 x_input_table IN VARCHAR2,
993 x_target_stage IN VARCHAR2
994 ) RETURN BOOLEAN;
995
996
997 /*===========================================================================+
998 |
999 | Name: Update_Stage_Input_Tables
1000 |
1001 | Description: This function set the stage of input tables of the
1002 | current process whose current status is x_current_status
1003 | to x_target_stage. Additionally, if the parameter
1004 | x_last_stage_flag is TRUE, set the last stage flag to 1.
1005 |
1006 | Parameters: x_current_status - current status
1007 | x_target_stage - target stage
1008 | x_last_stage_flag - indicates of the new stage is going
1009 | to be the last stage
1010 |
1011 | Returns: If any error occurs, this function add the error message
1012 | to the error stack and return FALSE. Otherwise return
1013 | TRUE
1014 |
1015 | Notes:
1016 |
1017 +============================================================================*/
1018 FUNCTION Update_Stage_Input_Tables(
1019 x_current_status IN VARCHAR2,
1020 x_target_stage IN VARCHAR2,
1021 x_last_stage_flag IN BOOLEAN
1022 ) RETURN BOOLEAN;
1023
1024
1025 /*===========================================================================+
1026 |
1027 | Name: Update_Status_All_Input_Tables
1028 |
1029 | Description: This function set the status of all input tables of the
1030 | current process whose current status is x_current_status
1031 | to x_target_status. Additionally set the error code to
1032 | x_error_code if it is provided.
1033 |
1034 | Parameters: x_current_status - current status
1035 | x_target_status - target status
1036 | x_error_code - error code
1037 |
1038 | Returns: If any error occurs, this function add the error message
1039 | to the error stack and return FALSE. Otherwise return
1040 | TRUE
1041 |
1042 | Notes:
1043 |
1044 +============================================================================*/
1045 FUNCTION Update_Status_All_Input_Tables(
1046 x_current_status IN VARCHAR2,
1047 x_target_status IN VARCHAR2,
1048 x_error_code IN VARCHAR2
1049 ) RETURN BOOLEAN;
1050
1051
1052 /*===========================================================================+
1053 |
1054 | Name: Update_Status_Input_Table
1055 |
1056 | Description: This function set the status of input table of the
1057 | current process to x_target_status. Additionally set the
1058 | error code to x_error_code if it is provided.
1059 |
1060 | Parameters: x_input_table - input table
1061 | x_target_status - target status
1062 | x_error_code - error code
1063 |
1064 | Returns: If any error occurs, this function add the error message
1065 | to the error stack and return FALSE. Otherwise return
1066 | TRUE
1067 |
1068 | Notes:
1069 |
1070 +============================================================================*/
1071 FUNCTION Update_Status_Input_Table(
1072 x_input_table IN VARCHAR2,
1073 x_target_status IN VARCHAR2,
1074 x_error_code IN VARCHAR2
1075 ) RETURN BOOLEAN;
1076
1077
1078 /*===========================================================================+
1079 |
1080 | Name: Write_Result_Log
1081 |
1082 | Description: This function write the result of the update input tables
1083 | process into the log file. This includes status of each
1084 | input table, invalid codes and new current periods
1085 |
1086 | Returns: If any error occurs, this function add the error message
1087 | to the error stack and return FALSE. Otherwise return
1088 | TRUE
1089 |
1090 | Notes:
1091 |
1092 +============================================================================*/
1093 FUNCTION Write_Result_Log RETURN BOOLEAN;
1094
1095 /*===========================================================================+
1096 | FUNCTION Wait_For_Requests
1097 +============================================================================*/
1098 FUNCTION Wait_For_Requests(
1099 x_requests IN BSC_UPDATE_UTIL.t_array_of_number,
1100 x_num_requests IN NUMBER
1101 ) RETURN BOOLEAN;
1102
1103 function get_kpi_for_input_tables(
1104 x_input_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1105 x_num_input_tables IN NUMBER,
1106 x_indicators IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
1107 x_num_indicators IN OUT NOCOPY NUMBER
1108 )return boolean;
1109
1110 function get_kpi_for_input_tables(
1111 x_input_table varchar2,
1112 x_indicators IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
1113 x_num_indicators IN OUT NOCOPY NUMBER
1114 )return boolean;
1115
1116 function value_in_array(
1117 x_value number,
1118 x_array BSC_UPDATE_UTIL.t_array_of_number,
1119 x_num_array NUMBER
1120 )return boolean;
1121
1122 /*===========================================================================+
1123 | FUNCTION Get_Indicator_List
1124 +============================================================================*/
1125 function Get_Indicator_List(
1126 x_number_array IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number
1127 )return number;
1128
1129 --Fix bug#4681065
1130 PROCEDURE Write_Warning_Kpis_In_Prot (
1131 x_system_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1132 x_num_system_tables IN NUMBER
1133 );
1134
1135 END BSC_UPDATE;
[Home] [Help]