1 PACKAGE BSC_UPDATE_UTIL AS
2 /* $Header: BSCDUTIS.pls 120.6 2007/02/08 09:41:31 ankgoel ship $ */
3
4 -- Global constans
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'BSC_UPDATE_UTIL';
6
7 G_BSC CONSTANT VARCHAR2(3) := 'BSC';
8 G_BIA CONSTANT VARCHAR2(3) := 'BIA';
9 G_PMF CONSTANT VARCHAR2(3) := 'PMF';
10 G_PMV CONSTANT VARCHAR2(3) := 'PMV';
11
12 G_BSC_PATCH CONSTANT BSC_SYS_INIT.Property_Code%TYPE := 'PATCH_NUMBER';
13 G_BIA_PATCH CONSTANT BSC_SYS_INIT.Property_Code%TYPE := 'BIA_PATCH_NUM';
14 G_PMF_PATCH CONSTANT BSC_SYS_INIT.Property_Code%TYPE := 'PMF_PATCH_NUM';
15 G_PMV_PATCH CONSTANT BSC_SYS_INIT.Property_Code%TYPE := 'PMV_PATCH_NUM';
16
17 --
18 -- Global Types
19 --
20 TYPE t_array_of_number IS TABLE OF NUMBER
21 INDEX BY BINARY_INTEGER;
22
23 -- BSC-BIS-DIMENSIONS: Changin the size of this array since key columns could be longer
24 TYPE t_array_of_varchar2 IS TABLE OF VARCHAR2(4000)
25 INDEX BY BINARY_INTEGER;
26
27 TYPE t_periodicity_rec IS RECORD (
28 calendar_id NUMBER,
29 db_column_name VARCHAR2(30),
30 edw_flag NUMBER,
31 periodicity_type NUMBER,
32 yearly_flag NUMBER,
33 period_col_name VARCHAR2(15),
34 sub_period_col_name VARCHAR2(15)
35 );
36
37 TYPE t_array_periodicities IS TABLE OF t_periodicity_rec
38 INDEX BY BINARY_INTEGER;
39
40 TYPE t_calendar_rec IS RECORD (
41 edw_flag NUMBER,
42 fiscal_year NUMBER,
43 source VARCHAR2(10),
44 start_year NUMBER,
45 start_month NUMBER,
46 start_day NUMBER
47 );
48
49 TYPE t_array_calendars IS TABLE OF t_calendar_rec
50 INDEX BY BINARY_INTEGER;
51
52 TYPE t_periodicity_rel IS RECORD (
53 periodicity_id NUMBER,
54 source_periodicity_id NUMBER
55 );
56
57 TYPE t_array_periodicity_rels IS TABLE OF t_periodicity_rel
58 INDEX BY BINARY_INTEGER;
59
60 TYPE t_kpi_rec IS RECORD (
61 indicator NUMBER,
62 prototype_flag NUMBER
63 );
64
65 TYPE t_array_kpis IS TABLE OF t_kpi_rec
66 INDEX BY BINARY_INTEGER;
67
68 TYPE t_temp_table_col_rec IS RECORD (
69 column_name VARCHAR2(30),
70 data_type VARCHAR2(30),
71 data_size NUMBER,
72 add_to_index VARCHAR2(1)
73 );
74
75 TYPE t_array_temp_table_cols IS TABLE OF t_temp_table_col_rec
76 INDEX BY BINARY_INTEGER;
77
78 --
79 -- Global Variables
80 --
81
82 -- G_Disable_Base_Index:
83 -- Flag to indicate whether to disable basic table index or not upon
84 -- updating the basic system table from the input table. If this option
85 -- is set to YES, index on the basic table primary key will be disabled
86 -- before bulk insert/update operations are performed on the basic
87 -- table. Disabling the index improves the speed of record insertion,
88 -- but not the update operations. Index will be enabled again after
89 -- the bulk insert/update operations are completed. Once this flag is set
90 -- to YES, all the basic table indexes will be disabled before the
91 -- insert/update operations and re-enabled after the SQL operations,
92 -- until the flag is explicitly set back to NO. If the flag value is set
93 -- to NO, index is not disabled nor enabled. By default, the flag is
94 -- set to YES to improve performance of insertion.
95
96 /* Now in the client side.
97 G_Disable_Base_Index VARCHAR2(3) := 'NO';
98 */
99
100 g_array_periodicities t_array_periodicities;
101 g_array_calendars t_array_calendars;
102 g_array_periodicity_rels t_array_periodicity_rels;
103
104 -- G_Current_Fiscal_Yr:
105 -- Current fiscal year. This variable corresponds to the global variable,
106 -- Ano_Act in the Visual Basic Update Process code. This PL/SQL global
107 -- variable value should be set, when Visual Basic Update Process code
108 -- sets the global variable value, Ano_Act.
109
110 G_Current_Fiscal_Yr NUMBER;
111
112 --parallelism.
113 g_parallel boolean;
114
115
116 /*===========================================================================+
117 |
118 | Name: CloneBSCPeriodicitybyCalendar
119 |
120 | Description: This create new periodicity for this calendar from the
121 | BSC base periodicity of Gregorian Calendar(calendar_id =1)
122 | To be called from VB. Insert any error in BSC_MESSAGE_LOGS
123 |
124 | Parameters: x_calendar_id - calendar id
125 |
126 |
127 | Notes:
128 |
129 +============================================================================*/
130 PROCEDURE CloneBSCPeriodicitybyCalendar(
131 x_calendar_id NUMBER
132 );
133
134
135 /*===========================================================================+
136 |
137 | Name: Create_Unique_Index
138 |
139 | Description: This function creates a unique index on the given table.
140 |
141 | Parameters: x_table_name - table name
142 | x_index_name - index name
143 | x_lst_columns - list of columns of the primary key
144 |
145 | Returns: If any error ocurrs, this function add the error message
146 | to the error stack and return FALSE. Otherwise return
147 | TRUE
148 |
149 | Notes:
150 |
151 +============================================================================*/
152 FUNCTION Create_Unique_Index(
153 x_table_name IN VARCHAR2,
154 x_index_name IN VARCHAR2,
155 x_lst_columns IN VARCHAR2,
156 x_tbs_type IN VARCHAR2
157 ) RETURN BOOLEAN;
158
159
160 /*===========================================================================+
161 | FUNCTION getSmallerColumnList
162 | Added for Bug 4099338
163 +============================================================================*/
164 function getSmallerColumnList(h_lst_cols_index in varchar2) return varchar2;
165
166
167 /*===========================================================================+
168 |
169 | Name: Create_Global_Temp_Table
170 |
171 | Description: This function creates the given global temporary table.
172 | If the table already exists with the same structure
173 | then we do not create it again.
174 |
175 +============================================================================*/
176 FUNCTION Create_Global_Temp_Table(
177 x_table_name IN VARCHAR2,
178 x_table_columns IN BSC_UPDATE_UTIL.t_array_temp_table_cols,
179 x_num_columns IN NUMBER
180 ) RETURN BOOLEAN;
181
182
183 /*===========================================================================+
184 |
185 | Name: Create_Permanent_Table
186 |
187 | Description: This function creates the given table.
188 | If the table already exists with the same structure
189 | then we do not create it again.
190 |
191 +============================================================================*/
192 FUNCTION Create_Permanent_Table(
193 x_table_name IN VARCHAR2,
194 x_table_columns IN BSC_UPDATE_UTIL.t_array_temp_table_cols,
195 x_num_columns IN NUMBER,
196 x_tablespace IN VARCHAR2,
197 x_idx_tablespace IN VARCHAR2
198 ) RETURN BOOLEAN;
199
200
201 /*===========================================================================+
202 |
203 | Name: Decompose_Numeric_List
204 |
205 | Description: This function decompose a string into a number array and
206 | returns the number of items in the array.
207 | Example:
208 | If x_string = '3001, 3002, 3003' and x_separator = ','
209 | then x_number_array = 3001|3002|3003 and retunrs 3
210 |
211 | Notes:
212 |
213 +============================================================================*/
214 FUNCTION Decompose_Numeric_List(
215 x_string IN VARCHAR2,
216 x_number_array IN OUT NOCOPY t_array_of_number,
217 x_separator IN VARCHAR2
218 ) RETURN NUMBER;
219
220
221 /*===========================================================================+
222 |
223 | Name: Decompose_Varchar2_List
224 |
225 | Description: This function decompose a string into a varchar2 array and
226 | returns the number of items in the array.
227 | Example:
228 | If x_string = 'table1, table2, table3' and x_separator = ','
229 | then x_array = 'table1'|'table2'|'table3' and retunrs 3
230 |
231 | Notes:
232 |
233 +============================================================================*/
234 FUNCTION Decompose_Varchar2_List(
235 x_string IN VARCHAR2,
236 x_array IN OUT NOCOPY t_array_of_varchar2,
237 x_separator IN VARCHAR2
238 ) RETURN NUMBER;
239
240
241 /*===========================================================================+
242 |
243 | Name: Drop_Index
244 |
245 | Description: This function drop given index.
246 |
247 | Parameters: x_index_name - index name
248 |
249 | Returns: If any error ocurrs, this function add the error message
250 | to the error stack and return FALSE. Otherwise return
251 | TRUE
252 |
253 | Notes:
254 |
255 +============================================================================*/
256 FUNCTION Drop_Index(
257 x_index_name IN VARCHAR2
258 ) RETURN BOOLEAN;
259
260
261 /*===========================================================================+
262 |
263 | Name: Drop_Table
264 |
265 | Description: This function drop given table if exists.
266 |
267 | Parameters: x_table_name - table name
268 |
269 | Returns: If any error ocurrs, this function add the error message
270 | to the error stack and return FALSE. Otherwise return
271 | TRUE
272 |
273 | Notes:
274 |
275 +============================================================================*/
276 FUNCTION Drop_Table(
277 x_table_name IN VARCHAR2
278 ) RETURN BOOLEAN;
279
280
281 /*===========================================================================+
282 |
283 | Name: Execute_Immediate
284 |
285 | Description: Execute the given sql statmentent
286 |
287 | Parameters: x_sql - sql statement
288 |
289 | Notes:
290 |
291 +============================================================================*/
292 PROCEDURE Execute_Immediate(
293 x_sql IN VARCHAR2
294 );
295
296 --ENH_B_TABLES_PERF: new procedure
297 PROCEDURE Execute_Immediate(
298 x_sql IN clob
299 );
300
301 --Fix bug#3875046
302 /*===========================================================================+
303 |
304 | Name: Execute_Immediate
305 |
306 | Description: Execute the given sql statmentent. Returns SQL%ROWCOUNT
307 |
308 | Parameters: x_sql - sql statement
309 |
310 | Notes:
311 |
312 +============================================================================*/
313 FUNCTION Execute_Immediate(
314 x_sql IN VARCHAR2
315 ) RETURN NUMBER;
316
317
318 /*===========================================================================+
319 |
320 | Name: Execute_Immediate
321 |
322 | Description: Execute the given sql statmentent
323 |
324 | Parameters: x_sql - sql statement
325 |
326 | Notes:
327 |
328 +============================================================================*/
329 PROCEDURE Execute_Immediate(
330 x_sql IN VARCHAR2,
331 x_bind_vars_values IN BSC_UPDATE_UTIL.t_array_of_varchar2,
332 x_num_bind_vars IN NUMBER
333 );
334
335 -- ENH_B_TABLES_PERF: new procedure
336 PROCEDURE Execute_Immediate(
337 x_sql IN clob,
338 x_bind_vars_values IN BSC_UPDATE_UTIL.t_array_of_varchar2,
339 x_num_bind_vars IN NUMBER
340 );
341
342 --Fix bug#3875046
343 /*===========================================================================+
344 |
345 | Name: Execute_Immediate
346 |
347 | Description: Execute the given sql statmentent. Returns SQL%ROWCOUNT
348 |
349 | Parameters: x_sql - sql statement
350 |
351 | Notes:
352 |
353 +============================================================================*/
354 FUNCTION Execute_Immediate(
355 x_sql IN VARCHAR2,
356 x_bind_vars_values IN BSC_UPDATE_UTIL.t_array_of_varchar2,
357 x_num_bind_vars IN NUMBER
358 ) RETURN NUMBER;
359
360
361 /*===========================================================================+
362 |
363 | Name: Execute_Immediate
364 |
365 | Description: Execute the given sql statmentent
366 |
367 | Parameters: x_sql - sql statement
368 |
369 | Notes:
370 |
371 +============================================================================*/
372 PROCEDURE Execute_Immediate(
373 x_sql IN VARCHAR2,
374 x_bind_vars_values IN BSC_UPDATE_UTIL.t_array_of_number,
375 x_num_bind_vars IN NUMBER
376 );
377
378
379 -- ENH_B_TABLES_PERF: new procedure
380 PROCEDURE Execute_Immediate(
381 x_sql IN clob,
382 x_bind_vars_values IN BSC_UPDATE_UTIL.t_array_of_number,
383 x_num_bind_vars IN NUMBER
384 );
385
386 --Fix bug#3875046
387 /*===========================================================================+
388 |
389 | Name: Execute_Immediate
390 |
391 | Description: Execute the given sql statmentent. Returns SQL%ROWCOUNT
392 |
393 | Parameters: x_sql - sql statement
394 |
395 | Notes:
396 |
397 +============================================================================*/
398 FUNCTION Execute_Immediate(
399 x_sql IN VARCHAR2,
400 x_bind_vars_values IN BSC_UPDATE_UTIL.t_array_of_number,
401 x_num_bind_vars IN NUMBER
402 ) RETURN NUMBER;
403
404
405 /*===========================================================================+
406 |
407 | Name: Exist_Periodicity_Rel
408 |
409 | Description: Return TRUE is there is relation between the given
410 | periodicity id and source periodicity id.
411 | It looks in the global array g_array_periodicity_rels
412
413 | Notes:
414 |
415 +============================================================================*/
416 FUNCTION Exist_Periodicity_Rel(
417 x_periodicity_id IN NUMBER,
418 x_source_periodicity_id IN NUMBER
419 ) RETURN BOOLEAN;
420
421
422 /*===========================================================================+
423 |
424 | Name: Get_Calendar_EDW_Flag
425 |
426 | Description: This function returns the edw flag of the given calendar
427 |
428 | Parameters: x_calendar_id: calendar id
429 |
430 | Notes:
431 |
432 +============================================================================*/
433 FUNCTION Get_Calendar_EDW_Flag(
434 x_calendar_id IN NUMBER
435 ) RETURN NUMBER;
436
437
438 /*===========================================================================+
439 |
440 | Name: Get_Calendar_Source
441 |
442 | Description: This function returns the source ('PMF' or 'BSC') of
443 | the given calendar
444 |
445 | Parameters: x_calendar_id: calendar id
446 |
447 | Notes:
448 |
452 ) RETURN VARCHAR2;
449 +============================================================================*/
450 FUNCTION Get_Calendar_Source(
451 x_calendar_id IN NUMBER
453
454
455 /*===========================================================================+
456 |
457 | Name: Get_Calendar_Fiscal_Year
458 |
459 | Description: This function returns the current fiscal year of the given
460 | calendar
461 |
462 | Parameters: x_calendar_id: calendar id
463 |
464 | Notes:
465 |
466 +============================================================================*/
467 FUNCTION Get_Calendar_Fiscal_Year(
468 x_calendar_id IN NUMBER
469 ) RETURN NUMBER;
470
471
472 /*===========================================================================+
473 |
474 | Name: Get_Calendar_Id
475 |
476 | Description: This function returns the calendar id of the given
477 | periodicity.
478 |
479 | Parameters: x_periodicity_id: periodicity id
480 |
481 | Notes:
482 |
483 +============================================================================*/
484 FUNCTION Get_Calendar_Id(
485 x_periodicity_id IN NUMBER
486 ) RETURN NUMBER;
487
488
489 /*===========================================================================+
490 |
491 | Name: Get_Calendar_Name
492 |
493 | Description: This function returns the name of the given calendar
494 |
495 | Parameters: x_calendar_id: calendar id
496 |
497 | Notes:
498 |
499 +============================================================================*/
500 FUNCTION Get_Calendar_Name(
501 x_calendar_id IN NUMBER
502 ) RETURN VARCHAR2;
503
504
505 /*===========================================================================+
506 |
507 | Name: Get_Calendar_Start_Date
508 |
509 | Description: This function returns the start date of current fiscal year
510 | of the given calendar.
511 |
512 | Notes:
513 |
514 +============================================================================*/
515 FUNCTION Get_Calendar_Start_Date(
516 x_calendar_id IN NUMBER,
517 x_current_fy IN NUMBER,
518 x_start_year OUT NOCOPY NUMBER,
519 x_start_month OUT NOCOPY NUMBER,
520 x_start_day OUT NOCOPY NUMBER
521 ) RETURN BOOLEAN;
522
523
524 /*===========================================================================+
525 |
526 | Name: Get_Calendar_Table_Col_Name
527 |
528 | Description: This function returns the name of the column of
529 | bsc_db_calendar table that has the values for the given
530 | periodicity.
531 |
532 | Parameters: x_periodicity_id
533 |
534 | Notes:
535 |
536 +============================================================================*/
537 FUNCTION Get_Calendar_Table_Col_Name(
538 x_periodicity_id IN NUMBER
539 ) RETURN VARCHAR2;
540
541
542 --AW_INTEGRATION: New function
543 FUNCTION Get_Dim_Level_Table_Name(
544 x_level_pk_col IN VARCHAR2
545 ) RETURN VARCHAR2;
546
547 --AW_INTEGRATION: New function
548 FUNCTION Get_Dim_Level_View_Name(
549 x_level_pk_col IN VARCHAR2
550 ) RETURN VARCHAR2;
551
552
553 /*===========================================================================+
554 |
555 | Name: Get_EDW_Materialized_View_Name
556 |
557 | Description: This function returns the name of the materialized view
558 | of the given table
559 |
560 | Notes:
561 |
562 +============================================================================*/
563 FUNCTION Get_EDW_Materialized_View_Name(
564 x_table_name IN VARCHAR2
565 ) RETURN VARCHAR2;
566
567
568 /*===========================================================================+
569 |
570 | Name: Get_EDW_Union_View_Name
571 |
572 | Description: This function returns the name of the union view
573 | of the given table
574 |
575 | Notes:
576 |
577 +============================================================================*/
578 FUNCTION Get_EDW_Union_View_Name(
579 x_table_name IN VARCHAR2
580 ) RETURN VARCHAR2;
581
582
583 /*===========================================================================+
584 |
585 | Name: Get_Free_Div_Zero_Expression
586 |
587 | Description: This function return a expression that is a modification of
588 | the given expression to make it division by zero safe.
589 | Uses DECODE in the denominator to prevent division by zero.
590 |
591 | Parameters: x_expression
592 |
593 | Notes:
594 |
595 +============================================================================*/
596 FUNCTION Get_Free_Div_Zero_Expression(
597 x_expression IN VARCHAR2
598 ) RETURN VARCHAR2;
599 PRAGMA RESTRICT_REFERENCES(Get_Free_Div_Zero_Expression, WNDS);
600
601
602 /*===========================================================================+
603 |
604 | Name: Get_Indic_Range_Of_Years
605 |
609 |
606 | Description: This function get the number of years and number of
607 | previous years that the indicator uses for the given
608 | periodicity.
610 | Parameters: x_indicator - indicator code
611 | x_periodicity - periodicity id
612 | x_num_of_years - parameter to return number of years
613 | x_previous_years - parameter to return number of previous
614 | years.
615 |
616 | Returns: If any error ocurrs, this function add the error message
617 | to the error stack and return FALSE. Otherwise return
618 | TRUE
619 |
620 | Notes:
621 |
622 +============================================================================*/
623 FUNCTION Get_Indic_Range_Of_Years(
624 x_indicator IN NUMBER,
625 x_periodicity IN NUMBER,
626 x_num_of_years OUT NOCOPY NUMBER,
627 x_previous_years OUT NOCOPY NUMBER
628 ) RETURN BOOLEAN;
629
630
631 /*===========================================================================+
632 |
633 | Name: Get_Information_Data_Columns
634 |
635 | Description: This function fill the following arrays with the information
636 | of all data columns of the table:
637 |
638 | x_data_columns - data column names
639 | x_data_formulas - source formula of data columns
640 | x_data_proj_methods - projection methods of data columns
641 | x_data_measure_types - measure type of data columns (1:Total
642 | 2:Balance)
643 |
644 | Set the parameter x_num_data_columns with the number of
645 | data columns of the table.
646 |
647 | Returns: If any error ocurrs, this function add the error message
648 | to the error stack and return FALSE. Otherwise return
649 | TRUE
650 |
651 | Notes:
652 |
653 +============================================================================*/
654 FUNCTION Get_Information_Data_Columns(
655 x_table IN VARCHAR2,
656 x_data_columns IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
657 x_data_formulas IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
658 x_data_proj_methods IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
659 x_data_measure_types IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
660 x_num_data_columns IN OUT NOCOPY NUMBER) RETURN BOOLEAN;
661
662
663 /*===========================================================================+
664 |
665 | Name: Get_Information_Key_Columns
666 |
667 | Description: This function fill the following arrays with the information
668 | of all key columns of the table:
669 |
670 | x_key_columns - key column names
671 | x_key_dim_tables - dimension table name for key columns
672 | x_source_columns - source of key columns
673 | x_source_dim_tables - dimension table name for source columns
674 | Set the parameter x_num_key_columns with the number of
675 | key columns of the table.
676 |
677 | Returns: If any error ocurrs, this function add the error message
678 | to the error stack and return FALSE. Otherwise return
679 | TRUE
680 |
681 | Notes:
682 |
683 +============================================================================*/
684 FUNCTION Get_Information_Key_Columns(
685 x_table IN VARCHAR2,
686 x_key_columns IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
687 x_key_dim_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
688 x_source_columns IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
689 x_source_dim_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
690 x_num_key_columns IN OUT NOCOPY NUMBER) RETURN BOOLEAN;
691
692
693 /*===========================================================================+
694 |
695 | Name: Get_Init_Variable_Value
696 |
697 | Description: This function returns in x_variable_value parameter the
698 | value of the INIT variable whose name is given in
699 | x_variable_name parameter.
700 |
701 | Parameters: x_variable_name - name of the INIT variable
702 | x_variable_value - argument to set the value of the
703 | variable
704 |
705 | Returns: If any error ocurrs, this function add the error message
706 | to the error stack and return FALSE. Otherwise return
707 | TRUE
708 |
709 | Notes:
710 |
711 +============================================================================*/
712 FUNCTION Get_Init_Variable_Value(
713 x_variable_name IN VARCHAR2,
714 x_variable_value OUT NOCOPY VARCHAR2
715 ) RETURN BOOLEAN;
716
717
718 /*===========================================================================+
719 |
720 | Name: Get_Input_Table_Source
721 |
722 | Description: This function returns the source type and source name
723 | of the given input table.
724 |
725 | Notes:
726 |
727 +============================================================================*/
728 FUNCTION Get_Input_Table_Source(
729 x_input_table IN VARCHAR2,
733
730 x_source_type OUT NOCOPY NUMBER,
731 x_source_name OUT NOCOPY VARCHAR2
732 ) RETURN BOOLEAN;
734
735 /*===========================================================================+
736 |
737 | Name: Get_Installed_Languages
738 |
739 | Description: Fill the array x_languages with the installed languages.
740 | Returns the number of installed languages.
741 | Returns -1 in case of error.
742 |
743 | Notes:
744 |
745 +============================================================================*/
746 FUNCTION Get_Installed_Languages(
747 x_languages IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2
748 ) RETURN NUMBER;
749
750
751 /*===========================================================================+
752 |
753 | Name: Get_Lookup_Value
754 |
755 | Description: This function returns the LOOKUP value of the given
756 | LOOKUP type and LOOKUP code
757 |
758 | Notes:
759 |
760 +============================================================================*/
761 FUNCTION Get_Lookup_Value(
762 x_lookup_type IN VARCHAR2,
763 x_lookup_code IN VARCHAR2
764 ) RETURN VARCHAR2;
765
766
767 /*===========================================================================+
768 |
769 | Name: Get_Message
770 |
771 | Description: This function returns the translated message
772 | of the given message code
773 |
774 | Notes:
775 |
776 +============================================================================*/
777 FUNCTION Get_Message(
778 x_message_name IN VARCHAR2
779 ) RETURN VARCHAR2;
780
781
782 /*===========================================================================+
783 |
784 | Name: Get_Num_Periods_Periodicity
785 |
786 | Description: This function returns the number of periods of the given
787 | periodicity.
788 |
789 | Parameters: x_peridiocity - periodicity code
790 | x_current_fy - current fical year
791 |
792 | Returns: If any error ocurrs, this function add the error message
793 | to the error stack and return NULL.
794 |
795 | Notes:
796 |
797 +============================================================================*/
798 FUNCTION Get_Num_Periods_Periodicity(
799 x_periodicity IN NUMBER,
800 x_current_fy IN NUMBER
801 ) RETURN NUMBER;
802
803
804 /*===========================================================================+
805 |
806 | Name: Get_Period_Cols_Names
807 |
808 | Description: This function gets period and subperiod columns names
809 | for the given periodicity. This information is stored
810 | in the columns PERIOD_COL_NAME and SUBPERIOD_COL_NAME
811 | of BSC_SYS_PERIODICITIES table.
812 |
813 | Parameters: x_periodicity_id - periodicity id
814 | x_period_col_name - period column name
815 | x_subperiod_col_name - subperiod column name
816 |
817 | Returns: If any error ocurrs, this function add the error message
818 | to the error stack and return FALSE. Otherwise return
819 | TRUE
820 |
821 | Notes:
822 |
823 +============================================================================*/
824 FUNCTION Get_Period_Cols_Names(
825 x_periodicity_cod IN NUMBER,
826 x_period_col_name OUT NOCOPY VARCHAR2,
827 x_subperiod_col_name OUT NOCOPY VARCHAR2
828 ) RETURN BOOLEAN;
829
830
831 /*===========================================================================+
832 |
833 | Name: Get_Period_Other_Periodicity
834 |
835 | Description: This function gets period corresponding to the given
836 | period of a source periodicity.
837 |
838 | Notes:
839 |
840 +============================================================================*/
841 FUNCTION Get_Period_Other_Periodicity(
842 p_periodicity_id IN NUMBER,
843 p_calendar_id IN NUMBER,
844 p_yearly_flag IN NUMBER,
845 p_current_fy IN NUMBER,
846 p_source_periodicity_id IN NUMBER,
847 p_source_period IN NUMBER
848 ) RETURN NUMBER;
849
850
851 /*===========================================================================+
852 |
853 | Name: Get_Periodicity_EDW_Flag
854 |
855 | Description: This function returns the EDW flag of the given
856 | periodicity.
857 |
858 | Parameters: x_periodicity_id: periodicity id
859 |
860 | Notes:
861 |
862 +============================================================================*/
863 FUNCTION Get_Periodicity_EDW_Flag(
864 x_periodicity_id IN NUMBER
865 ) RETURN NUMBER;
866
867
868 /*===========================================================================+
869 |
870 | Name: Get_Periodicity_Type
871 |
872 | Description: This function returns the periodicity type of the given
873 | periodicity.
874 |
875 | Parameters: x_periodicity_id: periodicity id
876 |
877 | Notes:
881 x_periodicity_id IN NUMBER
878 |
879 +============================================================================*/
880 FUNCTION Get_Periodicity_Type(
882 ) RETURN NUMBER;
883
884
885 /*===========================================================================+
886 |
887 | Name: Get_Periodicity_Yearly_Flag
888 |
889 | Description: This function returns the yearly flag of the given
890 | periodicity.
891 |
892 | Parameters: x_periodicity_id: periodicity id
893 |
894 | Notes:
895 |
896 +============================================================================*/
897 FUNCTION Get_Periodicity_Yearly_Flag(
898 x_periodicity_id IN NUMBER
899 ) RETURN NUMBER;
900
901
902
903 /*===========================================================================+
904 |
905 | Name: Get_Source_Periodicities
906 |
907 | Description: This function returns the source periodicities of the given
908 | periodicity in the array x_source_periodicities.
909 | It reads from the global array g_array_periodicity_rels
910 |
911 | Notes:
912 |
913 +============================================================================*/
914 FUNCTION Get_Source_Periodicities(
915 x_periodicity_id IN NUMBER,
916 x_source_periodicities IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number
917 ) RETURN NUMBER;
918
919
920 /*===========================================================================+
921 |
922 | Name: Get_Table_EDW_Flag
923 |
924 | Description: This function returns the EDW flag of the table.
925 |
926 | Parameters: x_table_name - table name
927 |
928 | Notes:
929 |
930 +============================================================================*/
931 FUNCTION Get_Table_EDW_Flag(
932 x_table_name IN VARCHAR2
933 ) RETURN NUMBER;
934
935
936 /*===========================================================================+
937 |
938 | Name: Get_Table_Generation_Type
939 |
940 | Description: This function returns the generation type of the table.
941 | The generation type of a table is stored in
942 | the column GENERATION_TYPE of BSC_DB_TABLES table.
943 |
944 | Parameters: x_table_name - table name
945 |
946 | Returns: Return the table generation type.
947 | If any error ocurrs, this function add the error message
948 | to the error stack and return NULL.
949 |
950 | Notes:
951 |
952 +============================================================================*/
953 FUNCTION Get_Table_Generation_Type(
954 x_table_name IN VARCHAR2
955 ) RETURN NUMBER;
956
957
958 /*===========================================================================+
959 |
960 | Name: Get_Table_Type
961 |
962 | Description: This function returns the table type of the table.
963 | The table type is stored in the column TABLE_TYPE of
964 | BSC_DB_TABLES table.
965 |
966 | Parameters: x_table_name - table name
967 |
968 | Returns: Return the table type.
969 | If any error ocurrs, this function add the error message
970 | to the error stack and return NULL.
971 |
972 | Notes:
973 |
974 +============================================================================*/
975 FUNCTION Get_Table_Type(
976 x_table_name IN VARCHAR2
977 ) RETURN NUMBER;
978
979
980 /*===========================================================================+
981 |
982 | Name: Get_Table_Periodicity
983 |
984 | Description: This function returns the periodicity code of the given
985 | table. The periodicity of a table is stored in the column
986 | PERIODICITY_ID of BSC_DB_TABLES table.
987 |
988 | Parameters: x_table_name - table name
989 |
990 | Returns: Return the table periodicity code.
991 | If any error ocurrs, this function add the error message
992 | to the error stack and return NULL.
993 |
994 | Notes:
995 |
996 +============================================================================*/
997 FUNCTION Get_Table_Periodicity(
998 x_table_name IN VARCHAR2
999 ) RETURN NUMBER;
1000
1001
1002 /*===========================================================================+
1003 |
1004 | Name: Get_Table_Range_Of_Years
1005 |
1006 | Description: This function get the number of years and number of
1007 | previous years that the table uses.
1008 |
1009 | Parameters: x_table_name - table name
1010 | x_num_of_years - parameter to return number of years
1011 | x_previous_years - parameter to return number of previous
1012 | years.
1013 |
1014 | Returns: If any error ocurrs, this function add the error message
1015 | to the error stack and return FALSE. Otherwise return
1016 | TRUE
1017 |
1018 | Notes:
1019 |
1020 +============================================================================*/
1021 FUNCTION Get_Table_Range_Of_Years(
1025 ) RETURN BOOLEAN;
1022 x_table_name IN VARCHAR2,
1023 x_num_of_years OUT NOCOPY NUMBER,
1024 x_previous_years OUT NOCOPY NUMBER
1026
1027
1028 /*===========================================================================+
1029 |
1030 | Name: Get_Kpis_Using_Table
1031 |
1032 | Description: This function return an array with the Kpis using directly
1033 | the given table and their prototype flag.
1034 | It consider SB tables created for targets
1035 | at different levels as tables used direclty by the indicator.
1036 |
1037 | Notes:
1038 |
1039 +============================================================================*/
1040 FUNCTION Get_Kpis_Using_Table(
1041 x_table_name IN VARCHAR2,
1042 x_kpis IN OUT NOCOPY t_array_kpis
1043 ) RETURN NUMBER;
1044
1045
1046 /*===========================================================================+
1047 |
1048 | Name: Is_Kpi_In_Production
1049 |
1050 | Description: This function returns TRUE is the indicator is in production
1051 | (prototype_flag in 0, 6, 7)
1052 | Notes:
1053 |
1054 +============================================================================*/
1055 FUNCTION Is_Kpi_In_Production(
1056 x_kpi IN NUMBER
1057 ) RETURN BOOLEAN;
1058
1059 /*===========================================================================+
1060 |
1061 | Name: Is_Kpi_Measure_In_Production
1062 |
1063 | Description: This function returns TRUE is the kpi measure is in production
1064 | (prototype_flag = 7)
1065 | Notes:
1066 |
1067 +============================================================================*/
1068 /*FUNCTION Is_Kpi_Measure_In_Production (
1069 p_objective_id IN NUMBER
1070 , p_kpi_measure_id IN NUMBER
1071 ) RETURN BOOLEAN;*/
1072
1073
1074 /*===========================================================================+
1075 |
1076 | Name: Get_Table_Target_Flag
1077 |
1078 | Description: This function returns the Target flag of the table.
1079 |
1080 | Parameters: x_table_name - table name
1081 |
1082 | Notes:
1083 |
1084 +============================================================================*/
1085 FUNCTION Get_Table_Target_Flag(
1086 x_table_name IN VARCHAR2
1087 ) RETURN NUMBER;
1088
1089
1090 /*===========================================================================+
1091 |
1092 | Name: Init_Calendar_Tables
1093 |
1094 | Description: This function populate the tables BSC_DB_CALENDAR,
1095 | and BSC_DB_WEEK_MAPS.
1096 |
1097 | Parameters: x_action = 1 Drop indexes on calendar tables (commit)
1098 | x_action = 2 Populate calendar tables (no commit)
1099 | x_action = 3 Create indexes on calendar tables (commit)
1100 | x_action = NULL Execute all steps
1101 |
1102 | Returns: If any error ocurrs, this function add the error message
1103 | to the error stack and return FALSE. Otherwise return
1104 | TRUE
1105 |
1106 | Notes:
1107 |
1108 +============================================================================*/
1109 FUNCTION Init_Calendar_Tables (
1110 x_calendar_id IN NUMBER,
1111 x_action IN NUMBER
1112 ) RETURN BOOLEAN;
1113
1114
1115 /*===========================================================================+
1116 |
1117 | Name: Init_Calendar_Tables
1118 |
1119 +============================================================================*/
1120 FUNCTION Init_Calendar_Tables (
1121 x_calendar_id IN NUMBER
1122 ) RETURN BOOLEAN;
1123
1124 --LOCKING: new function
1125 FUNCTION Init_Calendar_Tables_AT(
1126 x_calendar_id IN NUMBER
1127 ) RETURN BOOLEAN;
1128
1129
1130 -- ENH_B_TABLES_PERF: new function
1131 /*===========================================================================+
1132 | FUNCTION Is_Base_Table
1133 +============================================================================*/
1134 FUNCTION Is_Base_Table(
1135 x_table_name IN VARCHAR2
1136 ) RETURN BOOLEAN;
1137
1138
1139 /*===========================================================================+
1140 |
1141 | Name: Is_EDW_Kpi_Table
1142 |
1143 | Description: This function TREU if the given table is used directly
1144 | by a EDW KPI
1145 |
1146 | Parameters: x_table_name Table name
1147 |
1148 | Notes:
1149 |
1150 +============================================================================*/
1151 FUNCTION Is_EDW_Kpi_Table(
1152 x_table_name IN VARCHAR2
1153 ) RETURN BOOLEAN;
1154
1155
1156 /*===========================================================================+
1157 |
1158 | Name: Item_Belong_To_Array_Number
1159 |
1160 | Description: This function says if a item belong to an array
1161 |
1162 | Parameters: x_item - item name
1163 | x_array - array
1164 | x_num_items - number of item in the array
1165 |
1166 | Returns: Return TRUE if the item belong to the array. Otherwise
1167 | return FALSE.
1168 |
1169 | Notes:
1170 |
1174 x_array IN t_array_of_number,
1171 +============================================================================*/
1172 FUNCTION Item_Belong_To_Array_Number(
1173 x_item IN NUMBER,
1175 x_num_items IN NUMBER
1176 ) RETURN BOOLEAN;
1177
1178
1179 /*===========================================================================+
1180 |
1181 | Name: Item_Belong_To_Array_Varchar2
1182 |
1183 | Description: This function says if a item belong to an array
1184 |
1185 | Parameters: x_item - item name
1186 | x_array - array
1187 | x_num_items - number of item in the array
1188 |
1189 | Returns: Return TRUE if the item belong to the array. Otherwise
1190 | return FALSE.
1191 |
1192 | Notes:
1193 |
1194 +============================================================================*/
1195 FUNCTION Item_Belong_To_Array_Varchar2(
1196 x_item IN VARCHAR2,
1197 x_array IN t_array_of_varchar2,
1198 x_num_items IN NUMBER
1199 ) RETURN BOOLEAN;
1200
1201
1202 --LOCKING: New procedure
1203 PROCEDURE Load_Calendar_Into_AW_AT(
1204 x_calendar_id IN NUMBER
1205 );
1206
1207
1208 /*===========================================================================+
1209 |
1210 | Name: Load_Periodicity_Rels
1211 |
1212 | Description: This function load in the array g_array_periodicity_rels()
1213 | all the relationships between periodicities
1214 |
1215 +============================================================================*/
1216 FUNCTION Load_Periodicity_Rels RETURN BOOLEAN;
1217
1218
1219 /*===========================================================================+
1220 |
1221 | Name: Make_Lst_Cond_Join
1222 |
1223 | Description: This function return a list with the condition for a
1224 | join between two tables.
1225 | Example: x_table_1 = 'table1'
1226 | x_key_columns_1 = 'col11'|'col12'|...|'col1N'
1227 | x_table_2 = 'table2'
1228 | x_key_columns_2 = 'col21'|'col22'|...|'col2N'
1229 | x_separator = 'AND'
1230 |
1231 | list = 'table1.col11 = table2.col21 AND
1232 | table2.col12 = table2.col22 AND
1233 | ...
1234 | table1.col1N = table2.col2N'
1235 |
1236 | Parameters: x_table_1 - name of table 1
1237 | x_key_columns_1 - array with table 1 columns
1238 | x_table_2 - name of table 2
1239 | x_key_columns_2 - array with table 2 columns
1240 | x_num_key_columns - number of columns
1241 | x_separator - boolean operator (AND, OR)
1242 |
1243 | Notes:
1244 |
1245 +============================================================================*/
1246 FUNCTION Make_Lst_Cond_Join(
1247 x_table_1 IN VARCHAR2,
1248 x_key_columns_1 IN t_array_of_varchar2,
1249 x_table_2 IN VARCHAR2,
1250 x_key_columns_2 IN t_array_of_varchar2,
1251 x_num_key_columns IN NUMBER,
1252 x_separator IN VARCHAR2
1253 ) RETURN VARCHAR2;
1254
1255
1256 /*===========================================================================+
1257 |
1258 | Name: Make_Lst_Cond_Left_Join
1259 |
1260 | Description: This function return a list with the condition for a left
1261 | join between two tables.
1262 | Example: x_table_1 = 'table1'
1263 | x_key_columns_1 = 'col11'|'col12'|...|'col1N'
1264 | x_table_2 = 'table2'
1265 | x_key_columns_2 = 'col21'|'col22'|...|'col2N'
1266 | x_separator = 'AND'
1267 |
1268 | list = 'table1.col11 = table2.col21 (+) AND
1269 | table2.col12 = table2.col22 (+) AND
1270 | ...
1271 | table1.col1N = table2.col2N'
1272 |
1273 | Parameters: x_table_1 - name of table 1
1274 | x_key_columns_1 - array with table 1 columns
1275 | x_table_2 - name of table 2
1276 | x_key_columns_2 - array with table 2 columns
1277 | x_num_key_columns - number of columns
1278 | x_separator - boolean operator (AND, OR)
1279 |
1280 | Notes:
1281 |
1282 +============================================================================*/
1283 FUNCTION Make_Lst_Cond_Left_Join(
1284 x_table_1 IN VARCHAR2,
1285 x_key_columns_1 IN t_array_of_varchar2,
1286 x_table_2 IN VARCHAR2,
1287 x_key_columns_2 IN t_array_of_varchar2,
1288 x_num_key_columns IN NUMBER,
1289 x_separator IN VARCHAR2
1290 ) RETURN VARCHAR2;
1291
1292
1293 /*===========================================================================+
1294 |
1295 | Name: Make_Lst_Cond_Null
1296 |
1297 | Description: This function return a list with the null condition.
1298 | Example: x_table = 'table'
1299 | x_key_columns = 'col1'|'col2'|...|'colN'
1300 | x_separator = 'AND'
1301 |
1302 | list = 'table.col1 IS NULL AND
1306 |
1303 | table.col2 IS NULL AND
1304 | ...
1305 | table.colN IS NULL'
1307 | Parameters: x_table - name of table
1308 | x_key_columns - array with table columns
1309 | x_num_key_columns - number of columns
1310 | x_separator - boolean operator (AND, OR)
1311 |
1312 | Notes:
1313 |
1314 +============================================================================*/
1315 FUNCTION Make_Lst_Cond_Null(
1316 x_table IN VARCHAR2,
1317 x_key_columns IN t_array_of_varchar2,
1318 x_num_key_columns IN NUMBER,
1319 x_separator IN VARCHAR2
1320 ) RETURN VARCHAR2;
1321
1322
1323 /*===========================================================================+
1324 |
1325 | Name: Make_Lst_Cond_Number
1326 |
1327 | Description: This function return a list with the number condition.
1328 | Example: x_column = 'column'
1329 | x_values = 1,2'
1330 | x_separator = 'AND'
1331 |
1332 | list = 'column=1 AND column=2'
1333 |
1334 | Parameters: x_column - column name
1335 | x_values - array with numeric values
1336 | x_num_values - number of values
1337 | x_separator - boolean operator (AND, OR)
1338 |
1339 | Notes:
1340 |
1341 +============================================================================*/
1342 FUNCTION Make_Lst_Cond_Number(
1343 x_column IN VARCHAR2,
1344 x_values IN t_array_of_number,
1345 x_num_values IN NUMBER,
1346 x_separator IN VARCHAR2
1347 ) RETURN VARCHAR2;
1348
1349
1350 /*===========================================================================+
1351 |
1352 | Name: Make_Lst_Description
1353 |
1354 | Description: This function return a description with the items in the array.
1355 | Example: x_array = 'column1'|'column2'|...|'columnN'
1356 | x_data_type = NUMBER
1357 | list = 'column1 NUMBER, column2 NUMBER, ..., columnN NUMBER'
1358 |
1359 | Parameters: x_array - array
1360 | x_num_items - number of items in the array
1361 | x_data_type - data type
1362 |
1363 | Notes:
1364 |
1365 +============================================================================*/
1366 FUNCTION Make_Lst_Description(
1367 x_array IN t_array_of_varchar2,
1368 x_num_items IN NUMBER,
1369 x_data_type IN VARCHAR2
1370 ) RETURN VARCHAR2;
1371
1372
1373 /*===========================================================================+
1374 |
1375 | Name: Make_Lst_From_Array_Varchar2
1376 |
1377 | Description: This function return a list with the items in the array.
1378 | Example: x_array = 'item1'|'item2'|...|'itemN'
1379 | list = 'item1, item2, ..., itemN'
1380 |
1381 | Parameters: x_array - array
1382 | x_num_items - number of items in the array
1383 |
1384 | Notes:
1385 |
1386 +============================================================================*/
1387 FUNCTION Make_Lst_From_Array_Varchar2(
1388 x_array IN t_array_of_varchar2,
1389 x_num_items IN NUMBER
1390 ) RETURN VARCHAR2;
1391
1392
1393 /*===========================================================================+
1394 |
1395 | Name: Make_Lst_Fixed_Column
1396 |
1397 | Description: This function return a list with fixed column name.
1398 | Example: x_fixed_column_name = 'KEY', x_num_items = 5
1399 | list = KEY1, KEY2, KEY3, KEY4, KEY5'
1400 |
1401 | Parameters:
1402 | x_num_items - number of items in the array
1403 |
1404 | Notes:
1405 |
1406 +============================================================================*/
1407 FUNCTION Make_Lst_Fixed_Column(
1408 x_fixed_column_name IN VARCHAR2,
1409 x_num_items IN NUMBER
1410 ) RETURN VARCHAR2;
1411
1412
1413 /*===========================================================================+
1414 |
1415 | Name: Make_Lst_Table_Column
1416 |
1417 | Description: This function return a list like this:
1418 | Example: x_table_name = 'table'
1419 | x_columns = 'col1'|'col2'|...|'colN'
1420 | list = 'table.col1, table.col2, ..., table.colN'
1421 |
1422 | Parameters: x_table_name - table name
1423 | x_columns - array of column names
1424 | x_num_columns - number of columns
1425 |
1426 | Notes:
1427 |
1428 +============================================================================*/
1429 FUNCTION Make_Lst_Table_Column(
1430 x_table_name IN VARCHAR2,
1431 x_columns IN t_array_of_varchar2,
1432 x_num_columns IN NUMBER
1433 ) RETURN VARCHAR2;
1434
1435
1436 /*===========================================================================+
1437 |
1438 | Name: Populate_Bsc_Db_Calendar
1439 |
1443 | Parameters: X_Current_Fiscal_Yr - Current fiscal year
1440 | Description: Populates the table BSC_DB_CALENDAR for the given calendar
1441 | for predefined periodicities.
1442 |
1444 | X_Fy_Start_Yr - Calendar year for the current fiscal
1445 | year start date.
1446 | X_Fy_Start_Mth - Calendar month for the current fiscal
1447 | year start date.
1448 | X_Fy_Start_Day - Calendar day for the current fiscal
1449 | year start date.
1450 |
1451 | Returns: If any error ocurrs, this function add the error message
1452 | to the error stack and return FALSE. Otherwise return
1453 | TRUE
1454 |
1455 | Notes:
1456 |
1457 +============================================================================*/
1458 FUNCTION Populate_Bsc_Db_Calendar(
1459 x_calendar_id NUMBER,
1460 X_Current_Fiscal_Yr NUMBER,
1461 X_Fy_Start_Yr NUMBER,
1462 X_Fy_Start_Mth NUMBER,
1463 X_Fy_Start_Day NUMBER
1464 ) RETURN BOOLEAN;
1465
1466
1467 /*===========================================================================+
1468 |
1469 | Name: Populate_Bsc_Db_Week_Maps
1470 |
1471 | Description: Populates the table BSC_DB_WEEK_MAPS for the given calendar.
1472 |
1473 | Parameters:
1474 |
1475 | Returns: If any error ocurrs, this function add the error message
1476 | to the error stack and return FALSE. Otherwise return
1477 | TRUE
1478 |
1479 | Notes:
1480 |
1481 +============================================================================*/
1482 FUNCTION Populate_Bsc_Db_Week_Maps(
1483 x_calendar_id IN NUMBER
1484 ) RETURN BOOLEAN;
1485
1486
1487 /*===========================================================================+
1488 |
1489 | Name: Populate_Bsc_Sys_Periods_Tl
1490 |
1491 | Description: Populates the table BSC_SYS_PERIODS_TL for the given calendar.
1492 |
1493 | Parameters:
1494 |
1495 | Returns: If any error ocurrs, this function add the error message
1496 | to the error stack and return FALSE. Otherwise return
1497 | TRUE
1498 |
1499 | Notes:
1500 |
1501 +============================================================================*/
1502 FUNCTION Populate_Bsc_Sys_Periods_Tl(
1503 x_calendar_id IN NUMBER
1504 ) RETURN BOOLEAN;
1505
1506
1507 /*===========================================================================+
1508 |
1509 | Name: Populate_Calendar_Tables
1510 |
1511 | Description: This is a procedure that populate the calendar tables
1512 | If some error ocurrs the error message is written in
1513 | BSC_MESSAGE_LOGS table with TYPE = 0.
1514 | This procedure is to be called from VB.
1515 |
1516 | Parameters: x_action = 1 Drop indexes on calendar tables (commit)
1517 | x_action = 2 Populate calendar tables (no commit)
1518 | x_action = 3 Create indexes on calendar tables (commit)
1519 | x_action = NULL Execute all steps
1520 |
1521 | Returns:
1522 |
1523 +============================================================================*/
1524 PROCEDURE Populate_Calendar_Tables (
1525 x_calendar_id IN NUMBER,
1526 x_action IN NUMBER
1527 );
1528
1529
1530 /*===========================================================================+
1531 |
1532 | Name: Populate_Calendar_Tables
1533 |
1534 +============================================================================*/
1535 PROCEDURE Populate_Calendar_Tables (
1536 x_calendar_id IN NUMBER
1537 );
1538
1539
1540 --Fix bug#4508980 : this api is provided to be called from OAF Calendar UI
1541 -- Note that from now on, load reporting calendar and load calendar into aw will be done in GDB
1542 /*===========================================================================+
1543 | PROCEDURE Populate_Calendar_Tables
1544 +============================================================================*/
1545 PROCEDURE Populate_Calendar_Tables (
1546 p_commit VARCHAR2,
1547 p_calendar_id NUMBER,
1548 x_return_status OUT NOCOPY VARCHAR2,
1549 x_msg_count OUT NOCOPY NUMBER,
1550 x_msg_data OUT NOCOPY VARCHAR2
1551 );
1552
1553
1554 /*===========================================================================+
1555 |
1556 | Name: Replace_Token
1557 |
1558 | Description: This function returns the message replacin the given token.
1559 |
1560 | Notes:
1561 |
1562 +============================================================================*/
1563 FUNCTION Replace_Token(
1564 x_message IN VARCHAR2,
1565 x_token_name IN VARCHAR2,
1566 x_token_value IN VARCHAR2
1567 ) RETURN VARCHAR2;
1568
1569
1570 /*===========================================================================+
1571 |
1572 | Name: Set_Calendar_Fiscal_Year
1573 |
1574 | Description: Set the given fiscal year for the calendar.
1575 |
1576 | Parameters: x_calendar_id: Calendar id
1577 | x_fiscal_year: new fiscal year
1578 |
1579 | Notes:
1580 |
1584 x_fiscal_year IN NUMBER
1581 +============================================================================*/
1582 FUNCTION Set_Calendar_Fiscal_Year(
1583 x_calendar_id IN NUMBER,
1585 ) RETURN BOOLEAN;
1586
1587
1588 /*===========================================================================+
1589 |
1590 | Name: Table_Exists
1591 |
1592 | Description: Checks whether the specific table exists in the
1593 | database.
1594 |
1595 | Parameters: X_Table - Table name.
1596 |
1597 | Returns: TRUE - Table exists in the database.
1598 | FALSE - Table does not exist in the database.
1599 |
1600 | Notes:
1601 |
1602 +============================================================================*/
1603 FUNCTION Table_Exists(
1604 X_Table VARCHAR2
1605 ) RETURN BOOLEAN;
1606
1607
1608 /*===========================================================================+
1609 |
1610 | Name: Table_Has_Any_Row
1611 |
1612 | Description: This function say if there is any row in a table that
1613 | accomplishes with the given condition.
1614 |
1615 | Parameters: x_table_name - Table name.
1616 | x_condition - condition
1617 |
1618 | Returns: TRUE - Table has any row that accomplish the condition
1619 | FALSE - Table doesn't have any row.
1620 | NULL - There was some error executing the function
1621 |
1622 | Notes:
1623 |
1624 +============================================================================*/
1625 FUNCTION Table_Has_Any_Row(
1626 x_table_name IN VARCHAR2,
1627 x_condition IN VARCHAR2
1628 ) RETURN BOOLEAN;
1629
1630
1631 /*===========================================================================+
1632 |
1633 | Name: Table_Has_Any_Row
1634 |
1635 +============================================================================*/
1636 FUNCTION Table_Has_Any_Row(
1637 x_table_name IN VARCHAR2
1638 ) RETURN BOOLEAN;
1639
1640
1641 /*===========================================================================+
1642 |
1643 | Name: Truncate_Table
1644 |
1645 | Description: Truncates the given table
1646 |
1647 | Parameters: x_table_name - Table name.
1648 |
1649 | Notes:
1650 |
1651 +============================================================================*/
1652 PROCEDURE Truncate_Table(
1653 x_table_name IN VARCHAR2
1654 );
1655
1656 --LOCKING: new procedure
1657 PROCEDURE Truncate_Table_AT(
1658 x_table_name IN VARCHAR2
1659 );
1660
1661
1662 /*===========================================================================+
1663 |
1664 | Name: Update_AnualPeriodicity_Src
1665 |
1666 | Description: This function creates a unique index on the given table.
1667 |
1668 | Parameters: x_table_name - table name
1669 | x_calendar_id - Calendar id
1670 | x_periodicity_id - Periodicity id to add/update or delete
1671 | Action - 1: Add-Update 2: Delete
1672 | Returns: If any error ocurrs, this function add the error message
1673 | to the error stack and return FALSE. Otherwise return
1674 | TRUE
1675 |
1676 | Notes:
1677 |
1678 +============================================================================*/
1679 PROCEDURE Update_AnualPeriodicity_Src(
1680 x_calendar_id NUMBER,
1681 x_periodicity_id NUMBER,
1682 x_action NUMBER
1683 );
1684 /*===========================================================================+
1685 |
1686 | Name: Update_Kpi_Period_Name
1687 |
1688 | Description: This function update the period name in BSC_KPI_DEFAULTS_TL
1689 | with the name of the current period of the given indicator
1690 |
1691 | Parameters: x_indicator
1692 |
1693 | Returns: FALSE there was an error
1694 |
1695 | Notes:
1696 |
1697 +============================================================================*/
1698 FUNCTION Update_Kpi_Period_Name(
1699 x_indicator IN NUMBER
1700 ) RETURN BOOLEAN;
1701
1702
1703 /*===========================================================================+
1704 |
1705 | Name: Update_Kpi_Time_Stamp
1706 |
1707 | Description: Update the time stamp of the given Kpi
1708 |
1709 | Parameters: indicator - kpi code
1710 |
1711 | Notes:
1712 |
1713 +============================================================================*/
1714 PROCEDURE Update_Kpi_Time_Stamp(
1715 x_indicator IN NUMBER
1716 );
1717
1718
1719 /*===========================================================================+
1720 |
1721 | Name: Update_Kpi_Time_Stamp
1722 |
1723 | Description: Update the time stamp of the Kpis based on the given condition
1724 |
1725 | Parameters: x_condition - Example: 'indicator = 3001 or indicator = 3017'
1726 |
1727 | Notes:
1728 |
1729 +============================================================================*/
1730 PROCEDURE Update_Kpi_Time_Stamp(
1731 x_condition IN VARCHAR2
1732 );
1733
1734
1735 /*===========================================================================+
1736 |
1737 | Name: Update_Kpi_Tab_Time_Stamp
1738 |
1739 | Description: Update the time stamp of the tab which the given kpi belongs to.
1740 |
1741 | Parameters: indicator - kpi code
1742 |
1743 | Notes:
1744 |
1745 +============================================================================*/
1746 PROCEDURE Update_Kpi_Tab_Time_Stamp(
1747 x_indicator IN NUMBER
1748 );
1749
1750
1751 /*===========================================================================+
1752 |
1753 | Name: Update_Kpi_Tab_Time_Stamp
1754 |
1755 | Description: Update the time stamp of the tab which the given kpi belongs to.
1756 | The given kpis are given in the condition
1757 |
1758 | Parameters: x_condition - Example: 'indicator = 3001 or indicator = 3017'
1759 |
1760 | Notes:
1761 |
1762 +============================================================================*/
1763 PROCEDURE Update_Kpi_Tab_Time_Stamp(
1764 x_condition IN VARCHAR2
1765 );
1766
1767
1768 /*===========================================================================+
1769 |
1770 | Name: Update_Kpi_Table_Time_Stamp
1771 |
1772 | Description: Update the time stamp of the kpis that read directly from
1773 | the given table
1774 |
1775 | Parameters: x_condition - Example: 'indicator = 3001 or indicator = 3017'
1776 |
1777 | Notes:
1778 |
1779 +============================================================================*/
1780 PROCEDURE Update_Kpi_Table_Time_Stamp(
1781 x_table_name IN VARCHAR2
1782 );
1783
1784
1785 /*===========================================================================+
1786 |
1787 | Name: Update_System_Time_Stamp
1788 |
1789 | Description: Update the system time stamp
1790 |
1791 | Parameters:
1792 |
1793 | Notes:
1794 |
1795 +============================================================================*/
1796 PROCEDURE Update_System_Time_Stamp;
1797
1798
1799 /*===========================================================================+
1800 |
1801 | Name: Verify_Custom_Periodicity
1802 |
1803 | Description: It check that there are records for all fiscal years
1804 | in BSC_SYS_PERIODS. In case a fiscal year dont have
1805 | records, it generate them automatically taking the
1806 | parameters of the current fiscal year.
1807 | After that it updates the corresponding column
1808 | in BSC_DB_CALENDAR table.
1809 |
1810 | Parameters: x_calendar_id - calendar id
1811 | x_periodicity_id - periodicity id
1812 | x_custom_code - 1 (based on start and end date)
1813 | 2 (based on start and end period of a base periodicity)
1814 |
1815 | Returns: If any error occurs, this function add the error message
1816 | to the error stack and return FALSE. Otherwise return
1817 | TRUE
1818 |
1819 | Notes:
1820 |
1821 +============================================================================*/
1822 FUNCTION Verify_Custom_Periodicity(
1823 x_calendar_id IN NUMBER,
1824 x_periodicity_id IN NUMBER,
1825 x_custom_code IN NUMBER
1826 ) RETURN BOOLEAN;
1827
1828
1829 /*===========================================================================+
1830 |
1831 | Name: Write_Init_Variable_Value
1832 |
1833 | Description: This function write in the table INIT the given value for
1834 | of the given variable. If the variable doesn't exist then
1835 | it's added. Otherwise it's updated.
1836 |
1837 | Parameters: x_variable_name - name of the INIT variable
1838 | x_variable_value - variable value
1839 |
1840 | Returns: If any error ocurrs, this function add the error message
1841 | to the error stack and return FALSE. Otherwise return
1842 | TRUE
1843 |
1844 | Notes:
1845 |
1846 +============================================================================*/
1847 FUNCTION Write_Init_Variable_Value(
1848 x_variable_name IN VARCHAR2,
1849 x_variable_value IN VARCHAR2
1850 ) RETURN BOOLEAN;
1851
1852 /*===========================================================================+
1853 |
1854 | Name: is_parallel
1855 |
1856 | Description: this function checks to see if parallelism is enabled
1857 |
1858 | Parameters:
1859 |
1860 | Returns: true if parallelism is set. false otherwise
1861 |
1862 | Notes:
1863 |
1864 +============================================================================*/
1865 function is_parallel return boolean;
1866
1867 /*******************************************************************************
1868 ********************************************************************************/
1869 FUNCTION set_Product_Version
1870 ( p_Product IN VARCHAR2
1871 , p_Version IN VARCHAR2
1872 ) RETURN BOOLEAN;
1873
1874 /*******************************************************************************
1875 ********************************************************************************/
1876 FUNCTION get_Product_Version
1877 (
1878 p_Product IN VARCHAR2
1879 ) RETURN VARCHAR2;
1880
1881
1882 -- AW_INTEGRATION: New function
1883 /*===========================================================================+
1884 |
1885 | Name: Is_Table_For_AW_Kpi
1886 |
1887 | Description: This function returns TRUE is the given table is used
1888 | by any AW indicator. Otherwise returns FALSE.
1889 | The given table can be base, t, or summary table.
1890 |
1891 +============================================================================*/
1892 FUNCTION Is_Table_For_AW_Kpi(
1893 x_table_name IN VARCHAR2
1894 ) RETURN BOOLEAN;
1895
1896
1897 -- AW_INTEGRATION: New function
1898 /*===========================================================================+
1899 |
1900 | Name: Get_Kpi_Impl_Type
1901 |
1902 | Description: This function returns the implementation type of the given
1903 | indicator: 0 summary tables, 1 MVs, 2 AWs
1904 |
1905 +============================================================================*/
1906 FUNCTION Get_Kpi_Impl_Type(
1907 x_kpi IN NUMBER
1908 ) RETURN NUMBER;
1909
1910
1911 -- AW_INTEGRATION: New function
1912 FUNCTION Exists_AW_Kpi RETURN BOOLEAN;
1913
1914 -- AW_INTEGRATION: New function
1915 FUNCTION Calendar_Used_In_AW_Kpi(
1916 x_calendar_id IN VARCHAR2
1917 ) RETURN BOOLEAN;
1918
1919 TYPE t_kpi_dim_props_rec IS RECORD (
1920 dim_set_id bsc_kpi_analysis_options_b.dim_set_id%TYPE,
1921 comp_level_pk_col bsc_kpi_dim_levels_b.level_pk_col%TYPE
1922 );
1923
1924 PROCEDURE Get_Kpi_Dim_Props (
1925 p_objective_id IN NUMBER
1926 , p_kpi_measure_id IN NUMBER
1927 , x_dim_props_rec OUT NOCOPY BSC_UPDATE_UTIL.t_kpi_dim_props_rec
1928 );
1929
1930 FUNCTION Get_Measure_Formula (
1931 p_objective_id IN NUMBER
1932 , p_kpi_measure_id IN NUMBER := NULL
1933 , p_sim_objective IN BOOLEAN := FALSE
1934 )
1935 RETURN VARCHAR2;
1936
1937 FUNCTION Get_Color_By_Total (
1938 p_objective_id IN NUMBER
1939 , p_kpi_measure_id IN NUMBER := NULL
1940 )
1941 RETURN NUMBER;
1942
1943 FUNCTION get_ytd_flag (
1944 p_objective_id IN NUMBER
1945 , p_kpi_measure_id IN NUMBER
1946 )
1947 RETURN NUMBER;
1948
1949 FUNCTION Get_Apply_Color_Flag (
1950 p_objective_id IN NUMBER
1951 , p_kpi_measure_id IN NUMBER := NULL
1952 )
1953 RETURN NUMBER;
1954
1955 END BSC_UPDATE_UTIL;
[Home] [Help]