1 Package Body BSC_DBGEN_METADATA_READER AS
2 /* $Header: BSCMDRDB.pls 120.13 2006/01/12 13:58:22 arsantha noship $ */
3
4 g_metadata_source VARCHAR2(100) := 'BSC';
5
6 -- PUBLIC APIs
7 PROCEDURE Initialize(p_metadata_source IN VARCHAR2) IS
8 BEGIN
9 g_metadata_source := p_metadata_source;
10 END;
11
12 FUNCTION Get_Fact_Source(p_fact_id IN NUMBER) RETURN VARCHAR2 IS
13 /*CURSOR cSource IS
14 select source from BSC_DB_FACT_KPI_MAPS
15 where indicator= p_fact_id;*/
16 l_source VARCHAR2(1000);
17 BEGIN
18 /* OPEN cSource;
19 FETCH cSource INTO l_source;
20 CLOSE cSource;
21 RETURN l_source;*/
22 return 'BSC';
23 END;
24
25 FUNCTION Get_Fact_Name(p_fact_id IN NUMBER) RETURN VARCHAR2 IS
26
27 /*CURSOR cSource IS
28 select object_name from BSC_DB_FACT_KPI_MAPS
29 where indicator= p_fact_id;*/
30 CURSOR cSource IS
31 select name from bsc_kpis_vl
32 where indicator= p_fact_id;
33 l_name VARCHAR2(1000);
34 BEGIN
35 OPEN cSource;
36 FETCH cSource INTO l_name;
37 CLOSE cSource;
38 RETURN l_name;
39 END;
40
41 PROCEDURE Get_Info_For_Fact_ID(p_fact_id IN NUMBER, p_fact OUT NOCOPY VARCHAR2,
42 p_fact_Type OUT NOCOPY VARCHAR2, p_fact_source OUT NOCOPY VARCHAR2) IS
43
44 /*CURSOR cFact IS
45 select object_name, object_type, source from BSC_DB_FACT_KPI_MAPS
46 where indicator= p_fact_id;
47 */
48 CURSOR cSource IS
49 select name, 1, 'BSC' from BSC_KPIS_VL
50 where indicator= p_fact_id;
51 BEGIN
52 OPEN cSource;
53 FETCH cSource INTO p_fact, p_fact_type, p_fact_source;
54 CLOSE cSource;
55 END;
56
57 -- Get the list of facts
58
59 FUNCTION Get_Facts_To_Process(p_process_id IN NUMBER) return BSC_DBGEN_STD_METADATA.tab_clsFact IS
60
61
62 l_facts BSC_DBGEN_STD_METADATA.tab_clsFact ;
63 l_fact_tmp BSC_DBGEN_STD_METADATA.tab_clsFact ;
64 BEGIN
65
66 l_facts := BSC_DBGEN_BSC_READER.Get_Facts_To_Process(p_process_id);
67 --l_fact_tmp := BSC_DB_AK_READER.Get_Facts_To_Process(p_process_id);
68 FOR i IN l_fact_tmp.first..l_fact_tmp.last LOOP
69 l_facts(l_facts.count) := l_fact_tmp(i);
70 END LOOP;
71 commit;
72 return l_facts;
73 END;
74
75
76 FUNCTION Get_Measures_For_Fact(p_fact IN VARCHAR2, p_dim_set IN NUMBER, p_include_derived_columns IN BOOLEAN default false) return BSC_DBGEN_STD_METADATA.tab_clsMeasure IS
77 BEGIN
78 --IF (g_metadata_source = BSC_DBGEN_STD_METADATA.BSC) THEN
79 return BSC_DBGEN_BSC_READER.Get_Measures_For_Fact(p_fact, p_dim_set, p_include_derived_columns);
80 --ELSE
81 -- return BSC_DB_AK_READER.Get_Measures_For_Fact(p_fact, p_dim_set);
82 -- END IF;
83
84 EXCEPTION WHEN OTHERS THEN
85 raise;
86
87 END;
88
89
90 --****************************************************************************
91 --Get_Periodicities_For_Fact
92 -- DESCRIPTION:
93 -- Get the collection of periodicity codes of the indicator
94 -- PARAMETERS:
95 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
96 --***************************************************************************
97
98 FUNCTION Get_Periodicities_For_Fact(p_fact IN VARCHAR2) RETURN BSC_DBGEN_STD_METADATA.tab_ClsPeriodicity IS
99
100
101 BEGIN
102
103 -- IF (g_metadata_source = BSC_DBGEN_STD_METADATA.BSC) THEN
104 return BSC_DBGEN_BSC_READER.Get_Periodicities_For_Fact(p_fact);
105 --ELSE
106 --return BSC_DB_AK_READER.Get_Periodicities_For_Fact(p_fact);
107 --END IF;
108
109 EXCEPTION WHEN OTHERS THEN
110 BSC_MO_HELPER_PKG.TerminateWithError('BSC_RETR_KPI_PERIOD_FAILED');
111 fnd_message.set_name('BSC', 'BSC_RETR_KPI_PERIOD_FAILED');
112 fnd_message.set_token('INDICATOR', p_fact);
113 app_exception.raise_exception;
114 End;
115
116 --****************************************************************************
117 --
118 --
119 -- DESCRIPTION:
120 -- Get the collection of levels for the indicator
121 --
122 -- PARAMETERS:
123 -- p_fact: indicator code
124 -- p_dim_set: p_dim_set
125 --
126 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
127 --***************************************************************************
128 Function get_dimensions_for_fact(p_fact IN VARCHAR2, p_dim_set IN NUMBER, p_include_missing_levels IN BOOLEAN default false)
129 RETURN BSC_DBGEN_STD_METADATA.tab_clsDimension IS
130
131
132 BEGIN
133
134 --IF (g_metadata_source = BSC_DBGEN_STD_METADATA.BSC) THEN
135 return BSC_DBGEN_BSC_READER.get_dimensions_for_fact(p_fact, p_dim_set, p_include_missing_levels);
136 --ELSE
137 --return BSC_DB_AK_READER.get_dimensions_for_fact(p_fact, p_dim_set);
138 --END IF;
139 EXCEPTION WHEN OTHERS THEN
140 fnd_message.set_name('BSC', 'BSC_RETR_DIM_KPI_FAILED');
141 fnd_message.set_token('INDICATOR', p_fact);
142 fnd_message.set_token('DIMENSION_SET', p_dim_set);
143 app_exception.raise_exception;
144 raise;
145 END;
146
147 /*
148 GetLevelsForDimension: Return the levels associated with a dimension
149 GetHierarchiesForDimension: Return the hierarchies associated with a dimension
150 GetLevelRelationships: Return the level and hierarchy information for a specified dimension
151 GetObjectProperty: Return the value of a property for a given object
152
153 GetSummaryTablesForFact: Return the tables created by the database generator for the fact
154 GetBaseTablesFor: Return the base tables created by the database generator for the fact
155 GetInputTablesFact: Return the base tables created by the database generator for the fact
156 GetDimensionTablesFact: Return the base tables created by the database generator for the fact
157 GetPropertiesForTable: Return the list of properties for a given table
158 GetPropertyValueForTable: Return the property value for a given table and property code
159 GetColumnsForTable: Return the list of columns for a given table
160 GetPropertiesForColumn: Return the list of properties for a given table and column
161 */
162
163 -- Public APIs needed by AW module
164 /* requested signature
165 procedure get_parent_level(
166 p_level varchar2,
167 p_parents out nocopy BSC_AW_ADAPTER_DIM.dim_parent_child_tb
168 ) is */
169 function get_parents_for_level(
170 p_level_name varchar2,
171 p_num_levels number default 1000000
172 ) RETURN BSC_DBGEN_STD_METADATA.tab_ClsLevelRelationship is
173
174
175 Begin
176 return BSC_DBGEN_BSC_READER.get_parents_for_level(p_level_name, p_num_levels);
177 Exception when others then
178 raise;
179 End;
180
181 /* Requested Signature
182 procedure get_child_level(
183 p_level varchar2,
184 p_children out nocopy BSC_AW_ADAPTER_DIM.dim_parent_child_tb
185 ) is */
186 function get_children_for_level(
187 p_level_name varchar2,
188 p_num_levels number default 1000000
189 ) RETURN BSC_DBGEN_STD_METADATA.tab_ClsLevelRelationship is
190
191
192 Begin
193 return BSC_DBGEN_BSC_READER.get_children_for_level(p_level_name, p_num_levels);
194 Exception when others then
195 raise;
196 End;
197
198
199 /*
200 we have to hardcode VARCHAR2(200) for the levels. please note that in our implementation, dim
201 are TEXT. so when we create olap table function views, the datatype muct be varchar2
202 */
203 /* Requested Signature
204 procedure get_level_pk(
205 p_level varchar2,
206 p_level_id out nocopy number,
207 p_level_pk out nocopy varchar2,
208 p_level_pk_datatype out nocopy varchar2*/
209
210 -- note that this will populate only the following attributes
211 -- level_id, level_pk, level_pk_datatype
212 function get_level_info(
213 p_level varchar2
214 ) return BSC_DBGEN_STD_METADATA.clsLevel is
215
216
217 l_level BSC_DBGEN_STD_METADATA.clsLevel ;
218
219 Begin
220 return BSC_DBGEN_BSC_READER.get_level_info(p_level);
221 Exception when others then
222 raise;
223 End;
224
225 /*
226 given a set of levels, find out the kpi/dimset which has ANY of the levels specified
227 */
228 /* Requested Signature
229 procedure get_kpi_for_dim(
230 p_levels dbms_sql.varchar2_table,
231 p_kpi out nocopy bsc_aw_adapter_dim.kpi_for_dim_tb) */
232 function get_facts_for_levels(p_levels dbms_sql.varchar2_table) return BSC_DBGEN_STD_METADATA.tab_clsFact is
233
234
235 l_facts BSC_DBGEN_STD_METADATA.tab_clsFact ;
236 l_fact_tmp BSC_DBGEN_STD_METADATA.tab_clsFact;
237 Begin
238 l_facts := BSC_DBGEN_BSC_READER.get_facts_for_levels(p_levels);
239 --dbms_output.put_line('Chk1');
240 --l_fact_tmp := BSC_DB_AK_READER.get_kpis_for_levels;
241 /*FOR i IN l_fact_tmp.first..l_fact_tmp.last LOOP
242 l_facts(l_facts.count) := l_fact_tmp(i);
243 END LOOP;*/
244 return l_facts;
245 Exception when others then
246 raise;
247 End;
248 /* Requested Signature
249 procedure get_dims_for_kpis(
250 p_kpi_list dbms_sql.varchar2_table,
251 p_dim_list out nocopy dbms_sql.varchar2_table)*/
252 -- use existing api
253
254 /* Requested signature function is_dim_recursive(p_dim_level varchar2) return varchar2 is */
255 function is_dim_recursive(p_dim_level varchar2) return boolean is
256 l_dim_list bsc_varchar2_table_type;
257 l_num_dim_list number := 0;
258 l_error varchar2(1000);
259 l_count number;
260 cursor cRec is
261 select count(1) from bsc_sys_dim_level_rels
262 where dim_level_id=parent_dim_level_id
263 and dim_level_id in (select dim_level_id from bsc_sys_dim_levels_b where level_table_name=p_dim_level);
264 BEGIN
265 bsc_olap_main.get_list_of_rec_dim(gRecDims, l_num_dim_list, l_error);
266 FOR i IN gRecDims.first..gRecDims.last LOOP
267 IF (p_dim_level = gRecDims(i)) THEN
268 return true;
269 END IF;
270 END LOOP;
271 open cRec;
272 fetch cRec into l_count;
273 close cRec;
274 if (l_count>0) then
275 return true;
276 end if;
277 return false;
278 Exception when others then
279 raise;
280 End;
281
282 /*
283 for DBI dim. read the static package
284 */
285 -- Venu asked me to ignore this - 31 Jan 2005
286 /*
287 procedure get_dim_data_source(
288 p_level_list dbms_sql.varchar2_table,
289 p_level_pk_col out nocopy dbms_sql.varchar2_table,
290 p_data_source out nocopy varchar2,
291 p_inc_data_source out nocopy varchar2
292 ) is
293 Begin
294 if p_level_list(1)='HRI_PER' then
295 p_level_pk_col(1):='CODE';
296 p_data_source:='(select code from hri_table)';
297 p_inc_data_source:='(select code from hri_table)';
298 end if;
299 Exception when others then
300 write_to_log_file_n('Error in get_dim_data_source '||sqlerrm);
301 raise;
302 End;
303 */
304 /*
305 for DBI recursive dim. read the static package
306 there is no p_denorm_inc_data_source. we always full refresh the rec dim. this does not mean kpi will need
307 full agg. dim load will figure out if there is hier change
308 */
309 -- Venu asked me to ignore this - 31 Jan 2005
310 /*procedure get_denorm_data_source(
311 p_dim_level varchar2,
312 p_child_col out nocopy varchar2,
313 p_parent_col out nocopy varchar2,
314 p_denorm_data_source out nocopy varchar2,
315 p_denorm_change_data_source out nocopy varchar2
316 ) is
317 Begin
318 if p_dim_level='HRI_PER' then
319 p_child_col:='employee';
320 p_parent_col:='manager';
321 p_denorm_data_source:='(select employee, manager from hri_denorm_table)';
322 p_denorm_change_data_source:='(select employee, manager from hri_denorm_change_table)';
323 end if;
324 Exception when others then
325 write_to_log_file_n('Error in get_denorm_data_source '||sqlerrm);
326 raise;
327 End;*/
328
329 /* requested signature
330 procedure get_kpi_for_calendar(
331 p_calendar_id number,
332 p_kpi_list out nocopy dbms_sql.varchar2_table) is
333 */
334 function get_fact_ids_for_calendar(
335 p_calendar_id number) return dbms_sql.number_table
336 is
337
338
339 cursor cCalendars IS
340 select kpi.indicator from bsc_kpi_periodicities kpi,
341 bsc_sys_periodicities sysper
342 where kpi.periodicity_id = sysper.periodicity_id
343 and sysper.calendar_id= p_calendar_id;
344 l_fact_ids dbms_sql.number_table;
345 l_fact_id number;
346 Begin
347 OPEN cCalendars;
348 LOOP
349 FETCH cCalendars INTO l_fact_id;
350 EXIT WHEN cCalendars%NOTFOUND;
351 l_fact_ids(l_fact_ids.count+1) := l_fact_id;
352 END LOOP;
353 CLOSE cCalendars;
354 return l_fact_ids;
355 Exception when others then
356 raise;
357 End;
358 /*Req signature
359
360
361 procedure get_kpi_calendar(
362 p_kpi varchar2,
363 p_calendar out nocopy number) is */
364
365 function get_calendar_id_for_fact(
366 p_fact IN VARCHAR2) return number is
367
368
369 l_calendar_id NUMBER;
370 cursor cCal is
371 SELECT calendar_id
372 FROM bsc_sys_periodicities sysper,
373 bsc_kpi_periodicities kpi
374 where kpi.periodicity_id = sysper.periodicity_id
375 and kpi.indicator = to_number(p_fact);
376 Begin
377 OPEN cCal;
378 FETCH cCal INTO l_calendar_id;
379 CLOSE cCal;
380 return l_calendar_id;
381
382 Exception when others then
383 raise;
384 End;
385 /*Req signature
386 procedure get_kpi_dim_sets(
387 p_kpi varchar2,
388 p_dim_set out nocopy dbms_sql.varchar2_table
389 */
390
391 function get_dim_sets_for_fact(
392 p_fact varchar2
393 ) return dbms_sql.number_table
394 is
395
396
397 l_dim_sets dbms_sql.number_table;
398 Begin
399 --IF get_fact_source(to_number(p_kpi)) = BSC_DBGEN_STD_METADATA.BSC THEN
400 l_dim_sets := BSC_DBGEN_BSC_READER.get_dim_sets_for_fact(to_number(p_fact));
401 --ELSE -- AK report
402 --l_dim_sets(1) := 0;
403 --END IF;
404 return l_dim_sets;
405 Exception when others then
406 raise;
407 End;
408
409 /*
410 this returns the level list in the lowest to the highest order
411 lowest levels come first. if the levels are
412 City State Country
413 Prod ProdCat ProdCatType
414 Day Month
415 then first three levels are
416 City, Prod, Day. others in any order
417 in api must get periodicity info also and have that in p_dim_level
418 */
419
420 /*Requested Signature
421 procedure get_dim_set_dims(
422 p_kpi varchar2,
423 p_dim_set varchar2,
424 p_dim_level out nocopy dbms_sql.varchar2_table)*/
425
426 -- use get_dimensions_for_fact
427
428 /*
429 populate
430 measure name
431 measure type --normal or balance
432 formula
433 */
434 /*procedure get_dim_set_measures(
435 p_kpi varchar2,
436 p_dim_set varchar2,
437 p_measure out nocopy dbms_sql.varchar2_table,
438 p_measure_type out nocopy dbms_sql.varchar2_table,
439 p_data_type out nocopy dbms_sql.varchar2_table,
440 p_agg_formula out nocopy dbms_sql.varchar2_table)
441 Use get_measures_for_fact
442 */
443
444 /* Requested
445 function is_target_at_higher_level(
446 p_kpi varchar2,
447 p_dim_set varchar2) return varchar2 is */
448
449 function is_target_at_higher_level(
450 p_fact varchar2,
451 p_dim_set varchar2) return boolean is
452
453
454 Begin
455 IF bsc_dbgen_utils.get_kpi_property_value(p_fact, 'DB_TRANSFORM', 1) <> 2 THEN
456 return false;
457 ELSE
458 return true;
459 END IF;
460 Exception when others then
461 raise;
462 End;
463
464 /*
465 for now, we dont support forecast in AW
466 */
467 /* ignored
468 function is_forecast_implemented(
469 p_kpi varchar2,
470 p_dim_set varchar2) return varchar2 is
471 Begin
472 return 'N';
473 Exception when others then
474 write_to_log_file_n('Error in is_forecast_implemented '||sqlerrm);
475 raise;
476 End;
477 */
478
479 /* Reuse get levels for fact API and check target_level property to be 1
480 procedure get_target_levels(
481 p_kpi varchar2,
482 p_dim_set varchar2,
483 p_levels out nocopy dbms_sql.varchar2_table) is
484 Begin
485 if p_kpi='3014' then
486 return;
487 end if;
488 Exception when others then
489 write_to_log_file_n('Error in get_target_levels '||sqlerrm);
490 raise;
491 End;
492 */
493
494 /* Reuse get_level_info API
495 procedure get_dim_level_properties(
496 p_level varchar2,
497 p_pk out nocopy varchar2,
498 p_fk out nocopy varchar2,
499 p_datatype out nocopy varchar2) is
500 --
501 l_level_id number;
502 Begin
503 get_level_pk(p_level,l_level_id,p_pk,p_datatype);
504 if p_level='BSC_D_BUG_COMPONENTS' then
505 p_fk:='COMPO_CODE';
506 elsif p_level='BSC_D_BUG_PRODUCTS' then
507 p_fk:='PROD_CODE';
508 elsif p_level='BSC_D_PRODUCT_FAMILY' then
509 p_fk:='FAMILY_CODE';
510 elsif p_level='BSC_D_MANAGER' then
511 p_fk:='MANAGER_CODE';
512 end if;
513 if p_level='HRI_PER' then
514 p_fk:='PER_CODE';
515 end if;
516 Exception when others then
517 write_to_log_file_n('Error in get_dim_level_properties '||sqlerrm);
518 raise;
519 End;
520 */
521
522 /*
523 given a kpi and the dim level, get the filter
524 Req sig
525 procedure get_dim_level_filter(
526 p_kpi varchar2,
527 p_level varchar2,
528 p_filter out nocopy dbms_sql.varchar2_table) is*/
529 function get_filter_for_dim_level(
530 p_fact varchar2,
531 p_level varchar2) return varchar2 is
532
533
534 Begin
535 return BSC_DBGEN_BSC_READER.get_filter_for_dim_level(p_fact, p_level);
536 Exception when others then
537 raise;
538 End;
539
540
541 /*Requested Signature
542 procedure get_s_views(
543 p_fact varchar2,
544 p_dim_set varchar2,
545 p_s_views out nocopy dbms_sql.varchar2_table) is*/
546 function get_s_views(
547 p_fact IN VARCHAR2,
548 p_dim_set IN NUMBER)
549 return dbms_sql.varchar2_table is
550
551
552 Begin
553 return BSC_DBGEN_BSC_READER.get_s_views(p_fact, p_dim_set);
554 Exception when others then
555 raise;
556 End;
557
558 /* Req sig
559 procedure get_s_view_levels(
560 p_s_view varchar2,
561 p_levels out nocopy dbms_sql.varchar2_table) is
562 Use following api */
563
564 /*
565 return all the levels of the base table. the dim set may have 5 levels. the base table may have 10. we
566 are not interested in the 5 keys not in the dim set. but we need to know that we have 10 keys in the base table so we can aggregate
567 p_bt_level_fks contains the column from the base table to this level. , like BUG_COMPO_CODE, RELEASEF_CODE etc
568 SQL> desc bsc_b_406_aw
569 Name Null? Type
570 ------------------------------- -------- ----
571 BUG_COMPO_CODE VARCHAR2(40)
572 RELEASEF_CODE VARCHAR2(40)
573 BUG_PRIOR_CODE VARCHAR2(40)
574 BUG_ASSIG_CODE VARCHAR2(40)
575 YEAR NOT NULL NUMBER(5)
576 TYPE VARCHAR2(40)
577 PERIOD NOT NULL NUMBER(5)
578 BUGOPEN NUMBER
579 p_bt_level_pks contain the level pk like CODE
580 Req sig
581 procedure get_base_table_levels(
582 p_fact varchar2,
583 p_dim_set varchar2,
584 p_base_table varchar2,
585 p_bt_levels out nocopy dbms_sql.varchar2_table,
586 p_bt_level_fks out nocopy dbms_sql.varchar2_table,
587 p_bt_level_pks out nocopy dbms_sql.varchar2_table
588 ) is*/
589 function get_levels_for_table(
590 p_table_name varchar2
591 ) return BSC_DBGEN_STD_METADATA.tab_clsLevel is
592
593
594 l_table_type VARCHAR2(100):='TABLE';
595 Begin
596 IF (p_table_name like 'BSC%MV') THEN
597 l_table_type := 'MV';
598 END IF;
599 return BSC_DBGEN_BSC_READER.get_levels_for_table(p_table_name, l_table_type);
600 Exception when others then
601 raise;
602 End;
603
604 /*
605 get the measures relevant to the dim set and mapped from this base table
606 Req sig
607 procedure get_base_table_measures(
608 p_fact varchar2,
609 p_dim_set varchar2,
610 p_base_table varchar2,
611 p_measures out nocopy dbms_sql.varchar2_table,
612 p_bt_formula out nocopy dbms_sql.varchar2_table) */
613
614 function get_b_table_measures_for_fact(
615 p_fact varchar2,
616 p_dim_set varchar2,
617 p_base_table varchar2,
618 p_include_derived_columns boolean )
619 return BSC_DBGEN_STD_METADATA.tab_clsMeasure IS
620
621
622 Begin
623 return BSC_DBGEN_BSC_READER.get_b_table_measures_for_fact(p_fact, p_dim_set, p_base_table, p_include_derived_columns);
624 Exception when others then
625 raise;
626 End;
627
628 /*
629 for a kpi, get the calendar info and the periodicity info.
630 we assume here that periodicities are not missing!!!! make sure the optimizer api gives without missing periodicity
631 assume that the lowest periodicity is first element in the array!!
632 */
633
634 /* Use get_periodicities_for_fact */
635 /*
636 procedure get_kpi_periodicities(
637 p_kpi varchar2,
638 p_dim_set varchar2,
639 p_periodicity out nocopy dbms_sql.number_table
640 ) is
641 Begin
642 if p_kpi='3014' then
643 p_periodicity(1):=9;
644 p_periodicity(2):=5;
645 p_periodicity(3):=3;
646 end if;
647 Exception when others then
648 write_to_log_file_n('Error in get_kpi_periodicities '||sqlerrm);
649 raise;
650 End;
651 */
652
653 /* Req sig
654 procedure get_base_table_periodicity(
655 p_base_table varchar2,
656 p_periodicity out nocopy number) is*/
657 function get_periodicity_for_table(
658 p_table varchar2) return NUMBER is
659
660
661 Begin
662 return BSC_DBGEN_BSC_READER.get_periodicity_for_table(p_table);
663 Exception when others then
664 raise;
665 End;
666
667 /*
668 given a calendar and periodicity, get the column name from bsc_db_calendar
669 */
670 /* Req Sig
671 function get_db_calendar_column(
672 p_calendar number,
673 p_periodicity number) return varchar2 */
674 function get_db_calendar_column(
675 p_calendar_id number,
676 p_periodicity_id number) return varchar2 is
677
678
679 Begin
680 return BSC_DBGEN_BSC_READER.get_db_calendar_column(p_calendar_id, p_periodicity_id);
681 Exception when others then
682 raise;
683 End;
684
685 /*Req sig
686 procedure get_zero_code_levels(
687 p_kpi varchar2,
688 p_dim_set varchar2,
689 p_levels out nocopy dbms_sql.varchar2_table) is*/
690 function get_zero_code_levels(
691 p_fact varchar2,
692 p_dim_set varchar2) return BSC_DBGEN_STD_METADATA.tab_clsLevel is
693
694
695 Begin
696 return BSC_DBGEN_BSC_READER.get_zero_code_levels(p_fact, p_dim_set) ;
697 Exception when others then
698 raise;
699 End;
700
701 /* Req sig
702 procedure get_dim_set_base_tables(
703 p_kpi varchar2,
704 p_dim_set varchar2,
705 p_base_tables out nocopy dbms_sql.varchar2_table) is*/
706 function get_base_tables_for_dim_set(
707 p_fact in varchar2,
708 p_dim_set in varchar2,
709 p_targets in boolean) return dbms_sql.varchar2_table is
710
711
712 Begin
713 return BSC_DBGEN_BSC_READER.get_base_tables_for_dim_set(to_number(p_fact), to_number(p_dim_set), p_targets);
714 Exception when others then
715 raise;
716 End;
717
718 /* Req sig
719 procedure get_dim_set_target_base_tables(
720 p_kpi varchar2,
721 p_dim_set varchar2,
722 p_base_tables out nocopy dbms_sql.varchar2_table) is
723 Use get_base_tables_for_dim_set with p_targets=true
724 */
725
726
727 /*
728 this procedure gives the period in which there is a mix of forecast and real data
729 we need the following
730 kpi and periodicity : we use this to hit bsc_db_tables that will indicate the current period
731 Req sig
732 procedure get_kpi_current_period(
733 p_kpi varchar2,
734 p_periodicity number,
735 p_period out nocopy number) is */
736 function get_current_period_for_fact(
737 p_fact varchar2,
738 --p_dim_set NUMBER,
739 p_periodicity number) return number is
740
741
742 Begin
743 return BSC_DBGEN_BSC_READER.get_current_period_for_fact(p_fact, p_periodicity);
744 Exception when others then
745 raise;
746 End;
747
748
749 function get_current_year_for_fact(
750 p_fact varchar2) return number is
751 Begin
752 return BSC_DBGEN_BSC_READER.get_current_year_for_fact(p_fact);
753 Exception when others then
754 raise;
755 End;
756
757 /*
758 this API is only called from the BSC Metadata Optimizer UI for AW support
759 */
760 function is_projection_enabled_for_kpi(
761 p_kpi in varchar2
762 ) return varchar2 is
763 Begin
764 return BSC_DBGEN_BSC_READER.is_projection_enabled_for_kpi(p_kpi);
765 Exception when others then
766 raise;
767 End;
768
769 /*
770 returns all the kpi that have been implemented in AW
771 */
772 function get_all_facts_in_aw return dbms_sql.varchar2_table is
773
774
775 Begin
776 return BSC_DBGEN_BSC_READER.get_all_facts_in_aw;
777 Exception when others then
778 raise;
779 End;
780 --get the ZMV for a kpi and dimset
781 /* Req sig
782 procedure get_z_s_views(
783 p_kpi varchar2,
784 p_dim_set varchar2,
785 p_s_views out nocopy dbms_sql.varchar2_table)*/
786 function get_z_s_views(
787 p_fact IN VARCHAR2,
788 p_dim_set IN NUMBER)
789 return dbms_sql.varchar2_table is
790
791
792 Begin
793 return BSC_DBGEN_BSC_READER.get_z_s_views(p_fact, p_dim_set);
794 Exception when others then
795 raise;
796 End;
797
798
799 --****************************************************************************
800 --
801 --
802 -- DESCRIPTION:
803 -- Get the collection of levels for the indicator
804 --
805 -- PARAMETERS:
806 -- p_fact: indicator code
807 -- this is needed by AW specifically, no need to order or
808 -- find missing levels.
809 -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
810 -- Arun.Santhanam March 10, 2005
811 --***************************************************************************
812 Function get_all_levels_for_fact(p_fact IN VARCHAR2)
813 RETURN DBMS_SQL.VARCHAR2_TABLE IS
814
815 BEGIN
816 return BSC_DBGEN_BSC_READER.get_all_levels_for_fact(p_fact);
817 END;
818
819
820 function get_dimension_level_short_name(p_dim_level_table_name IN VARCHAR2) return VARCHAR2
821 IS
822 BEGIN
823 return BSC_DBGEN_BSC_READER.get_dimension_level_short_name(p_dim_level_table_name);
824 END;
825
826 function get_measures_for_short_names(p_short_names in dbms_sql.varchar2_table) return dbms_sql.varchar2_table is
827 begin
828 return BSC_DBGEN_BSC_READER.get_measures_for_short_names(p_short_names);
829 end;
830
831 function get_dim_levels_for_short_names(p_short_names in dbms_sql.varchar2_table) return dbms_sql.varchar2_table is
832 begin
833 return BSC_DBGEN_BSC_READER.get_dim_levels_for_short_names(p_short_names);
834 end;
835
836 function get_fact_implementation_type(p_fact in varchar2) return varchar2 is
837 begin
838 return BSC_DBGEN_BSC_READER.get_fact_implementation_type(p_fact);
839 end;
840
841
842 function is_level_used_by_aw_fact(p_level_name in varchar2) return boolean is
843 begin
844 return bsc_dbgen_bsc_reader.is_level_used_by_aw_fact(p_level_name);
845 end;
846
847 -- reqd by venu to identify dim level changes
848 -- will return only the children used by facts implemented as AW
849 function get_parents_for_level_aw(p_level_name varchar2, p_num_levels number default 1000000) RETURN BSC_DBGEN_STD_METADATA.tab_ClsLevelRelationship is
850 begin
851 return bsc_dbgen_bsc_reader.get_parents_for_level_aw(p_level_name, p_num_levels);
852 end;
853
854 -- reqd by venu to identify dim level changes
855 -- will return only the children used by facts implemented as AW
856 function get_children_for_level_aw(p_level_name varchar2, p_num_levels number default 1000000) RETURN BSC_DBGEN_STD_METADATA.tab_ClsLevelRelationship is
857 begin
858 return bsc_dbgen_bsc_reader.get_children_for_level_aw(p_level_name, p_num_levels);
859 end;
860
861
862 procedure mark_facts_in_process(p_facts in dbms_sql.varchar2_table) is
863 begin
864 g_assume_production_facts.delete;
865 for i in 1..p_facts.count loop
866 g_assume_production_facts(i):=p_facts(i);
867 end loop;
868 end;
869
870 function get_target_per_for_b_table(p_fact in varchar2, p_dim_set in number, p_b_table in varchar2) return dbms_sql.varchar2_table is
871 begin
872 return bsc_dbgen_bsc_reader.get_target_per_for_b_table(p_fact, p_dim_set, p_b_table);
873
874 end;
875
876
877 function get_initora_parameter(p_parameter in varchar2) return varchar2 is
878 CURSOR cValue is
879 select value from v$parameter where name=p_parameter;
880 begin
881 if g_initora_parameters.exists(p_parameter) then
882 return g_initora_parameters(p_parameter).value;
883 end if;
884 open cValue;
885 fetch cValue INTO g_initora_parameters(p_parameter).value;
886 close cValue;
887 return g_initora_parameters(p_parameter).value;
888 end;
889
890
891 function get_partition_clause return varchar2 is
892 l_stmt varchar2(1000);
893 begin
894 if g_num_partitions = -1 then
895 g_num_partitions := get_max_partitions;
896 end if;
897 if g_num_partitions <2 then
898 return null;
899 end if;
900 l_stmt := ' partition by list('|| BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME||') (';
901 for i in 1..g_num_partitions loop
902 if (i>1) then -- need comma
903 l_stmt := l_stmt ||',';
904 end if;
905 l_stmt := l_stmt ||' partition p_'||(i-1)||' values('||(i-1)||')';
906 end loop;
907 l_stmt := l_stmt ||')';
908 return l_stmt;
909 end;
910
911 function get_max_partitions return number is
912 l_cpus number;
913 begin
914 if (g_num_partitions <> -1) then
915 return g_num_partitions;
916 end if;
917 l_cpus := get_initora_parameter('cpu_count');
918 -- if there are 2 or less cpus, then dont partition
919 if (l_cpus <2) then
920 g_num_partitions := 0;
921 return g_num_partitions;
922 end if;
923
924 -- set the # of partitions to the max possible 2^x such that 2^x is less than max cpus
925 -- eg. if there are 7 cpus, then 4
926 -- eg. if there are 4 cpus, then 4
927 -- eg. if there are 3 cpus, then 2
928 g_num_partitions := 1;
929 for i in 1..l_cpus loop
930 if (g_num_partitions*2 > l_cpus) then
931 exit;
932 else
933 g_num_partitions := g_num_partitions*2;
934 end if;
935 end loop;
936 return g_num_partitions;
937
938 end;
939
940 function get_table_properties(p_table_name in VARCHAR2, p_property varchar2) return varchar2 is
941
942 l_properties dbms_sql.varchar2_table;
943 l_property_values dbms_sql.varchar2_table;
944 begin
945 l_properties(1) := p_property;
946 l_property_values := get_table_properties(p_table_name, l_properties);
947 return l_property_values(1);
948 end;
949
950
951 function get_table_properties(p_table_name in VARCHAR2, p_property_list in dbms_sql.VARCHAR2_table) return dbms_sql.VARCHAR2_table IS
952 cursor cProperty is
953 select properties from bsc_db_tables
954 where table_name = p_table_name;
955 l_db_property varchar2(4000);
956 l_index number;
957
958 l_property_values dbms_sql.varchar2_table;
959 begin
960 if p_property_list.count = 0 then
961 return p_property_list;
962 end if;
963 open cProperty;
964 fetch cProperty into l_db_property;
965 close cProperty;
966 l_index := p_property_list.first;
967 loop
968 if (p_property_list(l_index) is not null) then
969 --dbms_output.put_line('calling parse with prop='||l_db_property||' name='||p_property_list(l_index)||', '|| BSC_DBGEN_STD_METADATA.BSC_ASSIGNMENT
970 -- ||', null, '||BSC_DBGEN_STD_METADATA.BSC_PROPERTY_SEPARATOR);
971 l_property_values(l_index) := bsc_dbgen_utils.parse_value(l_db_property, p_property_list(l_index),
972 BSC_DBGEN_STD_METADATA.BSC_ASSIGNMENT,
973 null,
974 BSC_DBGEN_STD_METADATA.BSC_PROPERTY_SEPARATOR);
975 else
976 l_property_values(l_index) := null;
977 end if;
978 exit when l_index=p_property_list.last;
979 l_index := p_property_list.next(l_index);
980 end loop;
981 return l_property_values;
982
983 end;
984
985 function get_partition_info(p_table_name varchar2) return BSC_DBGEN_STD_METADATA.clsTablePartition IS
986 cursor cPartitionType(p_owner varchar2) IS
987 select table_name, partitioning_type, partition_count from all_part_tables where table_name=p_table_name
988 and owner=p_owner;
989
990 cursor cPartCols(p_owner varchar2, p_table varchar2) IS
991 SELECT part_cols.column_name, tab_cols.data_type
992 FROM all_tab_columns tab_cols
993 , all_part_key_columns part_cols
994 WHERE part_cols.name=p_table_name
995 AND part_cols.owner = p_owner
996 AND part_cols.name = tab_cols.table_name
997 AND part_cols.column_name = tab_cols.column_name
998 AND part_cols.owner = tab_cols.owner
999 AND part_cols.object_type = p_table;
1000
1001 cursor cPartitionInfo(pOwner varchar2) IS
1002 select partition_name, high_value, partition_position
1003 from all_tab_partitions where table_name=p_table_name
1004 and table_owner = pOwner
1005 order by partition_position;
1006
1007 l_table_partition BSC_DBGEN_STD_METADATA.clsTablePartition;
1008 l_partition BSC_DBGEN_STD_METADATA.clsPartitionInfo;
1009 l_partition_null BSC_DBGEN_STD_METADATA.clsPartitionInfo;
1010 l_count number := 1;
1011 begin
1012 if (g_bsc_schema is null) then
1013 bsc_apps.init_bsc_apps;
1014 g_bsc_schema := bsc_apps.get_user_schema;
1015 end if;
1016
1017 open cPartitionType(g_bsc_schema);
1018 fetch cPartitionType into l_table_partition.table_name, l_table_partition.partitioning_type, l_table_partition.partition_count;
1019 close cPartitionType;
1020 if l_table_partition.table_name is null then
1021 return l_table_partition;
1022 end if;
1023
1024 -- get the partitioning column and its data type
1025 for i in cPartCols(g_bsc_schema, 'TABLE') loop
1026 l_table_partition.partitioning_column := l_table_partition.partitioning_column||i.column_name||',';
1027 l_table_partition.partitioning_column_datatype := l_table_partition.partitioning_column_datatype||i.data_type||',';
1028 end loop;
1029 l_table_partition.partitioning_column := substr(l_table_partition.partitioning_column, 1, length(l_table_partition.partitioning_column)-1);
1030 l_table_partition.partitioning_column_datatype := substr(l_table_partition.partitioning_column_datatype, 1,
1031 length(l_table_partition.partitioning_column_datatype)-1);
1032 open cPartitionInfo(g_bsc_schema);
1033 loop
1034 fetch cPartitionInfo into l_partition.partition_name, l_partition.partition_value, l_partition.partition_position;
1035 exit when cPartitionInfo%NOTFOUND;
1036 l_table_partition.partition_info(l_count) := l_partition;
1037 l_count := l_count + 1;
1038 l_partition := l_partition_null;
1039 end loop;
1040 close cPartitionInfo;
1041 return l_table_partition;
1042 end;
1043
1044
1045 function get_last_update_date_for_fact(p_fact in varchar2) return date is
1046 begin
1047 return bsc_dbgen_bsc_reader.get_last_update_date_for_fact(p_fact);
1048 end;
1049
1050 function get_fact_cols_from_b_table(
1051 p_fact in varchar2,
1052 p_dim_set in number,
1053 p_b_table_name in varchar2,
1054 p_col_type in varchar2
1055 ) return BSC_DBGEN_STD_METADATA.tab_clsColumnMaps IS
1056 --BSC_DBGEN_STD_METADATA.tab_clsLevel
1057 Begin
1058 return bsc_dbgen_bsc_reader.get_fact_cols_from_b_table(p_fact, p_dim_set, p_b_table_name, p_col_type);
1059 Exception when others then
1060 raise;
1061 End;
1062
1063
1064 procedure set_table_property(p_table_name in varchar2, p_property_name in varchar2, p_property_value in varchar2) is
1065 begin
1066 bsc_dbgen_bsc_reader.set_table_property(p_table_name, p_property_name, p_property_value);
1067 end;
1068
1069
1070 FUNCTION get_denorm_dimension_table(p_dim_short_name VARCHAR2) return VARCHAR2 IS
1071 l_denorm_table varchar2(100);
1072 l_reverse varchar2(100);
1073 BEGIN
1074 l_denorm_table := 'BSC_DN_';
1075 select reverse(p_dim_short_name) into l_reverse from dual;
1076 l_denorm_table := l_denorm_table|| bsc_aw_utility.get_hash_value(p_dim_short_name,100,1073741824)||'_'||
1077 bsc_aw_utility.get_hash_value('*'||l_reverse,100,1073741824);
1078 return l_denorm_table;
1079 END;
1080
1081
1082
1083 --added Jan 12, 2006 for Venu
1084 function get_current_period_for_table( p_table_name varchar2) return number is
1085 begin
1086 return bsc_dbgen_bsc_reader.get_current_period_for_table(p_table_name);
1087 end;
1088
1089
1090 function get_current_year_for_table(p_table_name varchar2) return number is
1091 begin
1092 return bsc_dbgen_bsc_reader.get_current_year_for_table(p_table_name);
1093 end;
1094
1095 END BSC_DBGEN_METADATA_READER;
[Home] [Help]