1 PACKAGE BSC_UPDATE_DIM AS
2 /* $Header: BSCDDIMS.pls 120.1 2005/12/16 11:03:49 meastmon noship $ */
3
4 -- Global constants
5
6 DIM_TABLE_TYPE_1N CONSTANT NUMBER := 1;
7 DIM_TABLE_TYPE_MN CONSTANT NUMBER := 2;
8 DIM_TABLE_TYPE_UNKNOWN CONSTANT NUMBER := 3;
9
10
11 -- Global array to store information regarding DBI Dimensions Levels
12 TYPE t_dbi_dim_data IS RECORD (
13 short_name VARCHAR2(30),
14 table_name VARCHAR2(30),
15 from_clause VARCHAR2(4000),
16 where_clause VARCHAR2(10000),
17 recursive_dim VARCHAR2(20),
18 date_tracked_dim VARCHAR2(20),
19 source_object VARCHAR2(20000),
20 source_object_alias VARCHAR2(30),
21 materialized VARCHAR2(30),--'YES' means create/load table. 'NO' means do not create/load table
22 user_code_col VARCHAR2(30),
23 code_col VARCHAR2(30),
24 parent1_col VARCHAR2(30),
25 parent2_col VARCHAR2(30),
26 parent3_col VARCHAR2(30),
27 parent4_col VARCHAR2(30),
28 parent5_col VARCHAR2(30),
29 -------for recursive dims
30 child_col VARCHAR2(30), --the name of the child col in denorm table data type is varchar2(400)
31 parent_col VARCHAR2(30),--the name of the parent col in denorm table datatype is varchar2(400)
32 parent_level_col VARCHAR2(30), --datatype is varchar2(40)
33 denorm_table VARCHAR2(30),--the denorm table that the MV will use
34 top_n_levels number,--number of levels from the top to materialize(denormalize)
35 top_n_levels_in_mv number,--top n levels that are in the mv
36 --
37 child_col_src VARCHAR2(30),--the src column for the child col
38 parent_col_src VARCHAR2(30),--the src col for the parent col
39 parent_level_src_col VARCHAR2(30),--if not null, this col holds the col
40 --from the src table that contains the parent level info
41 denorm_src_object VARCHAR2(30),
42 -------------------------
43 source_to_check VARCHAR2(4000), -- List of tables (i.e DBIDIM_TABLE1,DBIDIM_TABLE2) to check last_update_date
44 -- before refreshing base table
45 denorm_source_to_check VARCHAR2(4000) -- List of tables (i.e DBIDIM_TABLE1,DBIDIM_TABLE2) to check last_update_date
46 -- before refreshing denorm table
47 );
48
49 TYPE t_array_dbi_dim_data IS TABLE OF t_dbi_dim_data
50 INDEX BY BINARY_INTEGER;
51
52 g_dbi_dim_data t_array_dbi_dim_data;
53 g_dbi_dim_data_set BOOLEAN := FALSE;
54 g_dbi_dim_tables_set BOOLEAN := FALSE;
55
56 --
57 -- Procedures and Functions
58 --
59 /*===========================================================================+
60 |
61 | Name: Any_Item_Changed_Any_Relation
62 |
63 | Description: This function returns TRUE if any item in the dimension
64 | changed any of the relation values.
65 | x_temp_table is the name of a temporal table which contains
66 | the previous dimension items.
67 | x_dimension_table has the current dimensions items.
68 |
69 |
70 | Notes:
71 |
72 +============================================================================*/
73 FUNCTION Any_Item_Changed_Any_Relation(
74 x_dimension_table IN VARCHAR2,
75 x_temp_table IN VARCHAR2,
76 x_relation_cols IN BSC_UPDATE_UTIL.t_array_of_varchar2,
77 x_num_relation_cols IN NUMBER
78 ) RETURN BOOLEAN;
79
80
81 /*===========================================================================+
82 |
83 | Name: Create_Dbi_Dim_Tables
84 |
85 | Description: This function creates the tables in BSC for the DBI
86 | dimensions. It creates the mv log too.
87 |
88 | Notes:
89 |
90 +============================================================================*/
91 FUNCTION Create_Dbi_Dim_Tables(
92 x_error_msg IN OUT NOCOPY VARCHAR2
93 ) RETURN BOOLEAN;
94
95
96 --AW_INTEGRATION: New function
97 /*===========================================================================+
98 |
99 | Name: Create_AW_Dim_Temp_Tables
100 |
101 | Description: This function creates global temporary tables
102 | needed for the AW dimension processing
103 | Returns FALSE in case of error.
104 |
105 | Notes:
106 |
107 +============================================================================*/
108 FUNCTION Create_AW_Dim_Temp_Tables RETURN BOOLEAN;
109
110 --LOCKING: new function
111 FUNCTION Create_AW_Dim_Temp_Tables_AT RETURN BOOLEAN;
112
113
114 /*===========================================================================+
115 |
116 | Name: Create_Dbi_Dim_Temp_Tables
117 |
118 | Description: This function creates global temporary tables
119 | needed for the process of refreshing the dbi dim tables
120 | Returns FALSE in case of error.
121 |
122 | Notes:
123 |
124 +============================================================================*/
125 FUNCTION Create_Dbi_Dim_Temp_Tables RETURN BOOLEAN;
126
127
128 /*===========================================================================+
129 |
130 | Name: Delete_Codes_Cascade
131 |
132 | Description: This function deletes in cascade the codes from the
133 | dimension table
134 | Returns FALSE in case of error.
135 |
136 | Notes:
137 |
138 +============================================================================*/
139 FUNCTION Delete_Codes_Cascade(
140 x_dim_table IN VARCHAR2,
141 x_deleted_codes IN BSC_UPDATE_UTIL.t_array_of_number,
142 x_num_deleted_codes IN NUMBER
143 ) RETURN BOOLEAN;
144
145
146 /*===========================================================================+
147 |
148 | Name: Delete_Codes_CascadeMN
149 |
150 | Description: This function deletes in cascade the codes from the
151 | MN dimension table
152 | Returns FALSE in case of error.
153 |
154 | Notes:
155 |
156 +============================================================================*/
157 FUNCTION Delete_Codes_CascadeMN(
158 x_dim_table IN VARCHAR2,
159 x_key_column1 IN VARCHAR2,
160 x_key_column2 IN VARCHAR2,
161 x_deleted_codes1 IN BSC_UPDATE_UTIL.t_array_of_varchar2,
162 x_deleted_codes2 IN BSC_UPDATE_UTIL.t_array_of_varchar2,
163 x_num_deleted_codes IN NUMBER
164 ) RETURN BOOLEAN;
165
166
167 /*===========================================================================+
168 |
169 | Name: Delete_Key_Values_In_Tables
170 |
171 | Description: This function deletes from system tables containig the given
172 | level pk column, the rows for the given condition.
173 | Returns FALSE in case of error.
174 |
175 | Notes:
176 |
177 +============================================================================*/
178 FUNCTION Delete_Key_Values_In_Tables(
179 x_level_pk_col IN VARCHAR2,
180 x_condition IN VARCHAR2
181 ) RETURN BOOLEAN;
182
183
184 /*===========================================================================+
185 |
186 | Name: Denorm_Eni_Item_Vbh_Cat
187 |
188 | Description: Refreshes the denormalized table for the dbi recursive
189 | dimension ENI_ITEM_VBH_CAT
190 |
191 | Notes:
192 |
193 +============================================================================*/
194 FUNCTION Denorm_Eni_Item_Vbh_Cat RETURN BOOLEAN;
195
196
197 /*===========================================================================+
198 |
199 | Name: Denorm_Eni_Item_Itm_Cat
200 |
201 | Description: Refreshes the denormalized table for the dbi recursive
202 | dimension ENI_ITEM_ITM_CAT
203 |
204 | Notes:
205 |
206 +============================================================================*/
207 FUNCTION Denorm_Eni_Item_Itm_Cat RETURN BOOLEAN;
208
209
210 /*===========================================================================+
211 |
212 | Name: Denorm_Hri_Per_Usrdr_H
213 |
214 | Description: Refreshes the denormalized table for the dbi recursive
215 | dimension HRI_PER_USRDR_H
216 |
217 | Notes:
218 |
219 +============================================================================*/
220 FUNCTION Denorm_Hri_Per_Usrdr_H RETURN BOOLEAN;
221
222
223 /*===========================================================================+
224 |
225 | Name: Denorm_Pji_Organizations
226 |
227 | Description: Refreshes the denormalized table for the dbi recursive
228 | dimension PJI_ORGANIZATIONS
229 |
230 | Notes:
231 |
232 +============================================================================*/
233 FUNCTION Denorm_Pji_Organizations RETURN BOOLEAN;
234
235
236 --AW_INTEGRATION: New function
237 /*===========================================================================+
238 |
239 | Name: Dimension_Used_In_AW_Kpi
240 |
241 | Description: Return TRUE if the given dimension table is used by any AW
242 | indicator
243 |
244 | Notes:
245 |
246 +============================================================================*/
247 FUNCTION Dimension_Used_In_AW_Kpi(
248 x_dim_table IN VARCHAR2
249 ) RETURN BOOLEAN;
250
251
252 --RECURSIVE_DIMS: New function
253 /*===========================================================================+
254 |
255 | Name: Dimension_Used_In_MV_Kpi
256 |
257 | Description: Return TRUE if the given dimension table is used by any AW
258 | indicator
259 |
260 | Notes:
261 |
262 +============================================================================*/
263 FUNCTION Dimension_Used_In_MV_Kpi(
264 x_dim_table IN VARCHAR2
265 ) RETURN BOOLEAN;
266
267
268 /*===========================================================================+
269 |
270 | Name: Get_All_Dbi_Dim_Data
271 |
272 | Description: This procedure returns in x_dbi_dim_data the info
273 | of all the dbi dimensions.
274 |
275 | Notes:
276 |
277 +============================================================================*/
278 PROCEDURE Get_All_Dbi_Dim_Data(
279 x_dbi_dim_data OUT NOCOPY BSC_UPDATE_DIM.t_array_dbi_dim_data
280 );
281
282
283 /*===========================================================================+
284 |
285 | Name: Get_Aux_Fields_Dim_Table
286 |
287 | Description: This function returns in the array x_aux_fields the aux
288 | fields of the dimension table. Return the number of them.
289 |
290 | Notes:
291 |
292 +============================================================================*/
293 FUNCTION Get_Aux_Fields_Dim_Table(
294 x_dim_table IN VARCHAR2,
295 x_aux_fields IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2
296 ) RETURN NUMBER;
297
298
299 /*===========================================================================+
300 |
301 | Name: Get_Child_Dimensions
302 |
303 | Description: This function fill the array x_child_dimensions with the name
304 | of the child dimensions for the given dimension table and return
305 | the number of them.
306 |
307 | Parameters:
308 |
309 |
310 | Notes:
311 |
312 +============================================================================*/
313 FUNCTION Get_Child_Dimensions(
314 x_dimension_table IN VARCHAR2,
315 x_child_dimensions IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2
316 ) RETURN NUMBER;
317
318
319 /*===========================================================================+
320 |
321 | Name: Get_Dbi_Dim_Parent_Columns
322 |
323 | Description: This function gets into the array x_parent_columns the name
324 | of the key columns of the parents of the given DBI dimension.
325 | It does not consider recursive parents (a dimension parent of itself).
326 | This function returns the number of parents.
327 |
328 | Notes:
329 |
330 +============================================================================*/
331 FUNCTION Get_Dbi_Dim_Parent_Columns(
332 x_dim_short_name IN VARCHAR2,
333 x_parent_columns IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
334 x_src_parent_columns IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2
335 ) RETURN NUMBER;
336
337
338 /*===========================================================================+
339 | FUNCTION Get_Dbi_Dim_View_Name
340 +============================================================================*/
341 FUNCTION Get_Dbi_Dim_View_Name(
342 x_dim_short_name IN VARCHAR2
343 ) RETURN VARCHAR2;
344
345
346 /*===========================================================================+
347 |
348 | Name: Get_Dbi_Dim_Data
349 |
350 | Description: This function gets into the record x_dbi_dim_data all the
351 | information regarding the dbi dimension.
352 |
353 +============================================================================*/
354 PROCEDURE Get_Dbi_Dim_Data(
355 x_dim_short_name IN VARCHAR2,
356 x_dbi_dim_data OUT NOCOPY BSC_UPDATE_DIM.t_dbi_dim_data
357 );
358
359
360 /*===========================================================================+
361 |
362 | Name: Get_Dbi_Dims_Kpis
363 |
364 | Description: This function gets into the array x_dbi_dimensions the
365 | short name of the DBI dimensions used by the
366 | given indicators.
367 |
368 +============================================================================*/
369 FUNCTION Get_Dbi_Dims_Kpis(
370 x_indicators IN BSC_UPDATE_UTIL.t_array_of_number,
371 x_num_indicators IN NUMBER,
372 x_dbi_dimensions IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
376
373 x_num_dbi_dimensions IN OUT NOCOPY NUMBER
374 ) RETURN BOOLEAN;
375
377 /*===========================================================================+
378 |
379 | Name: Get_Deleted_Records
380 |
381 | Description: This function returns in the array x_deleted_records the codes
382 | of the deleted dimension items.
383 | x_temp_table is the name of a temporal table which contains
384 | the previous dimension items.
385 | x_dimension_table has the current dimensions items.
386 |
387 | Notes:
388 |
389 +============================================================================*/
390 FUNCTION Get_Deleted_Records(
391 x_dimension_table IN VARCHAR2,
392 x_temp_table IN VARCHAR2,
393 x_deleted_records IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number
394 ) RETURN NUMBER;
395
396
397 /*===========================================================================+
398 |
399 | Name: Get_Dim_Table_of_Input_Table
400 |
401 | Description: This function returns the dimension table corresponding
402 | to the given input table
403 |
404 | Notes:
405 |
406 +============================================================================*/
407 FUNCTION Get_Dim_Table_of_Input_Table(
408 x_input_table IN VARCHAR2
409 ) RETURN VARCHAR2;
410
411
412 /*===========================================================================+
413 |
414 | Name: Get_Dim_Table_Type
415 |
416 | Description: This function returns the dimension type: MN or 1N
417 | of the given dimension table
418 |
419 | Notes:
420 |
421 +============================================================================*/
422 FUNCTION Get_Dim_Table_Type(
423 x_dim_table IN VARCHAR2
424 ) RETURN NUMBER;
425
426
427 /*===========================================================================+
428 |
429 | Name: Get_Info_Parents_Dimensions
430 |
431 | Description: This function returns in the array x_parent_tables the
432 | table name of each parent of the dimension table. Also,
433 | in the array x_parent_keys retunrs the pk column name of
434 | the parents. Return the number of parents.
435 |
436 | Notes:
437 |
438 +============================================================================*/
439 FUNCTION Get_Info_Parents_Dimensions(
440 x_dim_table IN VARCHAR2,
441 x_parent_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
445
442 x_parent_keys IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2
443 ) RETURN NUMBER;
444
446 /*===========================================================================+
447 |
448 | Name: Get_Level_PK_Col
449 |
450 | Description: This function returns the name of the pk columns for the
451 | given dimension table.
452 |
453 | Parameters:
454 |
455 |
456 | Notes:
457 |
458 +============================================================================*/
459 FUNCTION Get_Level_PK_Col(
460 x_dimension_table IN VARCHAR2
461 ) RETURN VARCHAR2;
462
463
464 /*===========================================================================+
465 |
466 | Name: Get_New_Code
467 |
468 | Description: This function next available code from the dimension table.
469 | Returns -1 in case of error.
470 |
471 | Parameters:
472 |
473 |
474 | Notes:
475 |
476 +============================================================================*/
477 FUNCTION Get_New_Code(
478 x_dim_table IN VARCHAR2
479 ) RETURN NUMBER;
480
481
482 /*===========================================================================+
483 |
484 | Name: Get_Parent_Dimensions
485 |
486 | Description: This function fill the array x_parent_dimensions with the name
487 | of the parent dimensions fo the given dimension table and return
488 | the number of them.
489 |
490 | Parameters:
491 |
492 |
493 | Notes:
494 |
495 +============================================================================*/
496 FUNCTION Get_Parent_Dimensions(
497 x_dimension_table IN VARCHAR2,
498 x_parent_dimensions IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2
499 ) RETURN NUMBER;
500
501
502 /*===========================================================================+
503 |
504 | Name: Get_Relation_Cols
505 |
506 | Description: This function fill the array x_relation_cols with the name
507 | of the relatin columns for the given dimension table and return
508 | the number of them.
509 |
510 | Parameters:
511 |
512 |
513 | Notes:
514 |
515 +============================================================================*/
516 FUNCTION Get_Relation_Cols(
517 x_dimension_table IN VARCHAR2,
518 x_relation_cols IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2
519 ) RETURN NUMBER;
520
521
522 /*===========================================================================+
523 |
524 | Name: Import_Dbi_Plans
525 |
526 | Description: This functions bring dbi plans into bsc benchhmarks
527 | in a incremental way.
528 +============================================================================*/
529 FUNCTION Import_Dbi_Plans(
530 x_error_msg IN OUT NOCOPY VARCHAR2
531 ) RETURN BOOLEAN;
532
533 --LOCKING: New function
534 FUNCTION Import_Dbi_Plans_AT(
535 x_error_msg IN OUT NOCOPY VARCHAR2
536 ) RETURN BOOLEAN;
537
538
539 /*===========================================================================+
540 |
541 | Name: Init_Dbi_Dim_Data
542 |
543 | Description: This procedure initializes the global array
544 | g_dbi_dim_data with the complete list of DBI
545 | dimensions and its properties.
546 |
547 | Parameters:
548 |
549 | Notes:
550 |
551 +============================================================================*/
552 PROCEDURE Init_Dbi_Dim_Data;
553
554
555 /*===========================================================================+
556 |
557 | Name: Insert_Children_Denorm_Table
558 |
559 | Description: This procedure inserts into the denorm table
560 | all the children of the ids given in x_ids
561 |
562 | Parameters:
563 |
564 | Notes:
565 |
566 +============================================================================*/
567 FUNCTION Insert_Children_Denorm_Table(
568 x_parent_id IN number,
569 x_ids IN BSC_UPDATE_UTIL.t_array_of_number,
570 x_num_ids IN NUMBER,
571 x_level IN NUMBER,
572 x_denorm_table IN VARCHAR2,
573 x_child_col IN VARCHAR2,
574 x_parent_col IN VARCHAR2,
575 x_parent_level_col IN VARCHAR2,
576 x_denorm_src_object IN VARCHAR2,
577 x_child_col_src IN VARCHAR2,
578 x_parent_col_src IN VARCHAR2,
579 x_src_condition IN VARCHAR2
580 ) RETURN BOOLEAN;
581
582
583 --RECURSIVE_DIMS: New function
584 /*===========================================================================+
585 | FUNCTION Is_Recursive_Dim
586 +============================================================================*/
587 FUNCTION Is_Recursive_Dim(
588 x_dim_table IN VARCHAR2
589 ) RETURN BOOLEAN;
590
591
592 --AW_INTEGRATION: New procedure
593 /*===========================================================================+
594 |
595 | Name: Insert_AW_Delete_Value
596 |
597 | Description: This procedure inserts (x_dim_table, x_delete_value) into
598 | table bsc_aw_dim_delete.
599 |
600 | Parameters:
601 |
602 |
603 | Notes:
604 |
605 +============================================================================*/
606 PROCEDURE Insert_AW_Delete_Value(
607 x_dim_table IN VARCHAR2,
608 x_delete_value IN VARCHAR2
612 --LOCKING: new procedure
609 );
610
611
613 /*===========================================================================+
614 | PROCEDURE Load_Dim_Into_AW_AT
615 +============================================================================*/
616 PROCEDURE Load_Dim_Into_AW_AT (
617 x_dim_table IN VARCHAR2
618 );
619
620
621 /*===========================================================================+
622 |
623 | Name: Load_Dim_Table
624 |
625 | Description: This function load the dimension table fromthe input table
626 | Return FALSE in case of error.
627 |
628 | Parameters:
629 |
630 |
631 | Notes:
632 |
633 +============================================================================*/
634 FUNCTION Load_Dim_Table(
635 x_dim_table IN VARCHAR2,
636 x_input_table IN VARCHAR2
637 ) RETURN BOOLEAN;
638
639 --LOCKING: new function
640 FUNCTION Load_Dim_Table_AT(
641 x_dim_table IN VARCHAR2,
642 x_input_table IN VARCHAR2
643 ) RETURN BOOLEAN;
644
645
646 --LOCKING: new procedure
647 /*===========================================================================+
648 |
649 | Name: Load_Type_Into_AW_AT
650 |
651 | Description: This function load TYPE dimension into AW.
652 | This is an Autonomous Transaction for Locking
653 |
654 +============================================================================*/
655 PROCEDURE Load_Type_Into_AW_AT;
656
657
658 /*===========================================================================+
659 |
660 | Name: Need_Refresh_Dbi_Table
661 |
662 | Description: This function compare the last update date of the table with
663 | maximum last update date of the source objects to decide
664 | if the table needs to be refreshed or not.
665 |
666 +============================================================================*/
667 FUNCTION Need_Refresh_Dbi_Table(
668 x_table_name IN VARCHAR2,
669 x_source_to_check IN VARCHAR2
670 ) RETURN BOOLEAN;
671
672
673 /*===========================================================================+
674 |
675 | Name: Refresh_Dbi_Dimension_Table
676 |
677 | Description: This function refreshes the table created in BSC to materialize
678 | the view of the given DBI dimension short name.
679 |
680 | Returns: If some error occurr this function returns FALSE
681 |
682 | Notes:
683 |
684 +============================================================================*/
685 FUNCTION Refresh_Dbi_Dimension_Table(
686 x_dim_short_name IN VARCHAR2
687 ) RETURN BOOLEAN;
688
689 --LOCKING: new function
690 FUNCTION Refresh_Dbi_Dimension_Table_AT(
691 x_dim_short_name IN VARCHAR2
692 ) RETURN BOOLEAN;
693
694
695 /*===========================================================================+
696 |
697 | Name: Refresh_Dbi_Dimension
698 |
699 | Description: This procedure is the executable for the concurrent program
700 | to refresh the table created in BSC to materialize
701 | the view of the given DBI dimension short name.
702 |
703 +============================================================================*/
704 PROCEDURE Refresh_Dbi_Dimension(
705 ERRBUF OUT NOCOPY VARCHAR2,
706 RETCODE OUT NOCOPY VARCHAR2,
707 x_dim_short_name IN VARCHAR2
708 );
709
710
711 -- RECURSIVE_DIMS: new function
712 /*===========================================================================+
713 | FUNCTION Refresh_Denorm_Table
714 +============================================================================*/
715 FUNCTION Refresh_Denorm_Table(
716 x_level_table_name IN VARCHAR2,
717 x_denorm_table_name IN VARCHAR2
718 ) RETURN BOOLEAN;
719
720
721 /*===========================================================================+
722 |
723 | Name: Refresh_EDW_Dimension
724 |
725 | Description: This function refresh the given EDW dimension and its childs
726 | and parents.
727 | For deleted dimension values, it deletes records in B,S tables.
728 | Add to the array x_modified_dimensions the modified dimensions
729 | (items deleted or item whose parent was changed)
730 | Add to the array x_checked_dimensions to avoid refrsh a dimesion
731 | several times.
732 |
733 | Parameters:
734 |
735 | Returns: If some error occurr this function returns FALSE
736 |
737 | Notes:
738 |
739 +============================================================================*/
740 FUNCTION Refresh_EDW_Dimension(
741 x_dimension_table IN VARCHAR2,
742 x_mod_dimensions IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
743 x_num_mod_dimensions IN OUT NOCOPY NUMBER,
744 x_checked_dimensions IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
745 x_num_checked_dimensions IN OUT NOCOPY NUMBER
746 ) RETURN BOOLEAN;
747
748
749 /*===========================================================================+
750 |
751 | Name: Refresh_EDW_Dimensions
752 |
753 | Description: This function refresh the given EDW dimensions and its childs.
754 | For deleted dimension values, it deletes records in B,S tables.
755 | Check Kpis using any modified dimension to be recalculated.
756 | Also synchronize sec assigments.
757 |
758 | Parameters:
759 |
760 | Returns: If some error occurr this function returns FALSE
761 |
762 | Notes:
763 |
764 +============================================================================*/
765 FUNCTION Refresh_EDW_Dimensions(
766 x_dimension_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
767 x_num_dimension_tables IN NUMBER
768 ) RETURN BOOLEAN;
769
770
771 /*===========================================================================+
772 |
773 | Name: Sync_Sec_Assigments
774 |
775 | Description: Syncronize security assigments
776 | Delete from BSC_USER_LIST_ACCESS the records which dimension
777 | value does not exist in the dimension table.
778 |
779 | Parameters:
780 |
781 | Returns: If some error occurr this function returns FALSE
782 |
783 | Notes:
784 |
785 +============================================================================*/
786 FUNCTION Sync_Sec_Assigments RETURN BOOLEAN;
787
788
789 /*===========================================================================+
790 |
791 | Name: Validate_Input_Table
792 |
793 | Description: Validate data of the dimension input table.
794 | If there are invalid codes then insert them into
795 | bsc_db_validation table.
796 |
797 | Parameters: x_input_table - input table name
798 |
799 | Returns: TRUE - input table doesn't have invalid codes
800 | FALSE - input table has invalid codes
801 | NULL - there was some error in the function. In
802 | this case this function add the error
803 | message in the error stack.
804 |
805 | Notes:
806 |
807 +============================================================================*/
808 FUNCTION Validate_Input_Table(
809 x_input_table IN VARCHAR2,
810 x_dim_table IN VARCHAR2
811 ) RETURN BOOLEAN;
812
813 --LOCKING: new function
814 FUNCTION Validate_Input_Table_AT(
815 x_input_table IN VARCHAR2,
816 x_dim_table IN VARCHAR2
817 ) RETURN BOOLEAN;
818
819
820 /*===========================================================================+
821 |
822 | Name: WriteRemovedKeyItems
823 |
824 | Description: Write in the output file the kpis which default key values
825 | were removed from the dimension table.
826 | Return FALSE in case of error.
827 |
828 | Notes:
829 |
830 +============================================================================*/
831 FUNCTION WriteRemovedKeyItems RETURN BOOLEAN;
832
833
834 procedure write_to_log_file(p_message varchar2);
835 procedure write_to_log_file_n(p_message varchar2);
836
837 END BSC_UPDATE_DIM;
[Home] [Help]