1 PACKAGE BODY BSC_OAF_VIEWS_PVT AS
2 /* $Header: BSCOAFVB.pls 120.2 2007/02/08 13:20:08 ppandey ship $ */
3 /*===========================================================================+
4 |
5 | Name: GET_AOPTS_SERIES_NAMES
6 |
7 | Description: Return a string with the all the Analysis option names in this format:
8 | A0_Name,A1_Name,A2_Name;Series Name
9 |
10 | Parameters:
11 +============================================================================*/
12 /*===========================================================================+
13 | Copyright (c) 1999 Oracle Corporation |
14 | Redwood Shores, California, USA |
15 | All rights reserved |
16 |============================================================================|
17 |
18 | Name: BSCOAFVB.pls
19 |
20 | Description: Package to support OA framework Views
21 |
22 |
23 | Dependencies:
24 |
25 | Example:
26 |
27 | Security:
28 |
29 | History: Created By: Henry Camacho Date: 30-NOV-01
30 | Bug #2660714 Pankaj 14-NOV-02
31 | Bug #2663355 Ashankar 30-DEC-02
32 | Bug #2943217 PWALI 15-MAY-03
33 | 31-JUL-2003 mahrao Increased the size of v_name in GET_LEVEL_PARENT_NAMES
34 | for bug# 3030788
35 | 16-NOV-2006 ankgoel Color By KPI enh#5244136
36 | 28-DEC-2006 ppandey For simulation display series name, enh#5386112
37 +============================================================================*/
38
39
40 FUNCTION GET_AOPTS_SERIES_NAMES(X_INDICATOR in NUMBER,
41 X_A0 in NUMBER,
42 X_A1 in NUMBER,
43 X_A2 in NUMBER,
44 X_SERIES_ID in NUMBER
45 ) RETURN VARCHAR2 IS
46
47 h_ag_count NUMBER;
48 h_ag1_depend NUMBER;
49 h_ag2_depend NUMBER;
50 h_val VARCHAR2(350);
51 h_a0_name BSC_KPI_ANALYSIS_OPTIONS_tl.Name%TYPE;
52 h_a1_name BSC_KPI_ANALYSIS_OPTIONS_tl.Name%TYPE;
53 h_a2_name BSC_KPI_ANALYSIS_OPTIONS_tl.Name%TYPE;
54 h_series_name BSC_KPI_ANALYSIS_MEASURES_TL.Name%TYPE;
55 h_series_count NUMBER;
56
57 l_al_count NUMBER; --added to solve the bug 2663355
58
59 h_err NUMBER;
60 CURSOR c_kpi IS
61 SELECT INDICATOR_TYPE,CONFIG_TYPE
62 FROM BSC_KPIS_B
63 WHERE INDICATOR=X_INDICATOR;
64
65 h_kpi_type NUMBER;
66 h_kpi_config NUMBER;
67
68 BEGIN
69 h_err := 0;
70 -- Number of Analysis Groups
71 SELECT MAX( ANALYSIS_GROUP_ID)
72 INTO h_ag_count
73 FROM BSC_KPI_ANALYSIS_GROUPS
74 WHERE INDICATOR=X_INDICATOR;
75
76
77 h_err := 1;
78 l_al_count := 0;
79 -- Get Name for A0
80 SELECT NAME
81 INTO h_a0_name
82 FROM BSC_KPI_ANALYSIS_OPTIONS_VL
83 WHERE ANALYSIS_GROUP_ID =0 AND
84 OPTION_ID=X_A0 AND
85 INDICATOR=X_INDICATOR;
86
87
88 -- Get Name for A1
89 IF h_ag_count >= 1 THEN
90 h_err := 2;
91
92 --h_ag_depend
93 SELECT DEPENDENCY_FLAG
94 INTO h_ag1_depend
95 FROM BSC_KPI_ANALYSIS_GROUPS
96 WHERE ANALYSIS_GROUP_ID =1 AND
97 INDICATOR=X_INDICATOR;
98
99
100
101 -- If depend it
102 IF h_ag1_depend = 0 THEN
103 h_err := 3;
104 SELECT NAME
105 INTO h_a1_name
106 FROM BSC_KPI_ANALYSIS_OPTIONS_VL
107 WHERE ANALYSIS_GROUP_ID =1 AND
108 OPTION_ID=X_A1 AND
109 INDICATOR=X_INDICATOR;
110 ELSE
111 h_err := 4;
112 BEGIN
113 SELECT NAME
114 INTO h_a1_name
115 FROM BSC_KPI_ANALYSIS_OPTIONS_VL
116 WHERE ANALYSIS_GROUP_ID =1 AND
117 OPTION_ID=X_A1 AND
118 PARENT_OPTION_ID = X_A0 AND
119 INDICATOR=X_INDICATOR;
120
121 ---Added to fix the bug 2663355 ----
122 SELECT COUNT(*)
123 INTO l_al_count
124 FROM BSC_KPI_ANALYSIS_OPTIONS_VL
125 WHERE ANALYSIS_GROUP_ID =1 AND
126 PARENT_OPTION_ID = X_A0 AND
127 INDICATOR =X_INDICATOR;
128 ------------------------------------
129
130
131 --Validation if there is not record
132 EXCEPTION WHEN OTHERS THEN NULL; END;
133 END IF;
134 END IF;
135 h_err := 5;
136 -- Concat A0 and A1
137 h_val := h_a0_name ;
138
139 IF h_a1_name IS NOT NULL THEN
140
141
142 IF l_al_count >1 or l_al_count =0 THEN -- condition aded to fix the bug 2663355
143
144 h_val := h_val || ',' || h_a1_name;
145 l_al_count := 0;
146 ELSE
147
148 h_val := h_val;
149 END IF;
150 END IF;
151
152
153
154
155
156 -- Get Name for A2
157 IF h_ag_count >= 2 THEN
158 h_err := 6;
159 --h_ag_depend
160 SELECT DEPENDENCY_FLAG
161 INTO h_ag2_depend
162 FROM BSC_KPI_ANALYSIS_GROUPS
163 WHERE ANALYSIS_GROUP_ID =2 AND
164 INDICATOR=X_INDICATOR;
165 -- If depend it
166 IF h_ag2_depend = 0 THEN
167 h_err := 7;
168 SELECT NAME
169 INTO h_a2_name
170 FROM BSC_KPI_ANALYSIS_OPTIONS_VL
171 WHERE ANALYSIS_GROUP_ID =2 AND
172 OPTION_ID=X_A2 AND
173 INDICATOR=X_INDICATOR;
174 ELSE
175 -- The AG2 is dependent, but AG1 is not
176 h_err := 8;
177 IF h_ag2_depend = 1 AND h_ag1_depend = 0 THEN
178 h_err := 9;
179 BEGIN
180 SELECT NAME
181 INTO h_a2_name
182 FROM BSC_KPI_ANALYSIS_OPTIONS_VL
183 WHERE ANALYSIS_GROUP_ID =2 AND
184 OPTION_ID=X_A2 AND
185 PARENT_OPTION_ID = X_A1 AND
186 INDICATOR=X_INDICATOR;
187
188
189 ---Added to fix the bug 2663355 ----
190 SELECT COUNT(*)
191 INTO l_al_count
192 FROM BSC_KPI_ANALYSIS_OPTIONS_VL
193 WHERE ANALYSIS_GROUP_ID =2 AND
194 PARENT_OPTION_ID = X_A1 AND
195 INDICATOR =X_INDICATOR;
196 ------------------------------------
197
198
199 --Validation if there is not record
200 EXCEPTION WHEN OTHERS THEN NULL; END;
201 ELSE
202 h_err := 10;
203 BEGIN
204 SELECT NAME
205 INTO h_a2_name
206 FROM BSC_KPI_ANALYSIS_OPTIONS_VL
207 WHERE ANALYSIS_GROUP_ID =2 AND
208 OPTION_ID=X_A2 AND
209 PARENT_OPTION_ID = X_A1 AND
210 GRANDPARENT_OPTION_ID = X_A0 AND
211 INDICATOR=X_INDICATOR;
212
213 -- Added to fix the bug 2663355 ----------
214 SELECT COUNT(*)
215 INTO l_al_count
216 FROM BSC_KPI_ANALYSIS_OPTIONS_VL
217 WHERE ANALYSIS_GROUP_ID =2 AND
218 PARENT_OPTION_ID = X_A1 AND
219 GRANDPARENT_OPTION_ID = X_A0 AND
220 INDICATOR =X_INDICATOR;
221 ------------------------------------------
222
223
224 --Validation if there is not record
225 EXCEPTION WHEN OTHERS THEN NULL; END;
226 END IF;
227 END IF;
228 END IF;
229 h_err := 11;
230 -- Concat A1 and A2
231
232 IF h_a2_name IS NOT NULL THEN
233
234 IF l_al_count > 1 or l_al_count =0 THEN -- added condition to fix the bug 2663355.
235 h_val := h_val || ',' || h_a2_name;
236 l_al_count := 0;
237 END IF;
238
239 END IF;
240
241
242 -- Get Series Name
243 OPEN c_kpi;
244 FETCH c_kpi INTO h_kpi_type,h_kpi_config;
245 IF (c_kpi%notfound) THEN
246 h_kpi_type := 1;
247 h_kpi_config := 1;
248 END IF;
249 CLOSE c_kpi;
250 --Multiple series
251 IF (h_kpi_type = 10 AND h_kpi_config = 1)
252 OR (h_kpi_type = 1 AND h_kpi_config = 7) THEN
253 BEGIN
254 SELECT COUNT(*) VAL
255 INTO h_series_count
256 FROM BSC_KPI_ANALYSIS_MEASURES_VL
257 WHERE ANALYSIS_OPTION0 =X_A0 AND
258 ANALYSIS_OPTION1 =X_A1 AND
259 ANALYSIS_OPTION2 =X_A2 AND
260 INDICATOR=X_INDICATOR;
261 IF h_series_count >1 THEN
262 SELECT NAME
263 INTO h_series_name
264 FROM BSC_KPI_ANALYSIS_MEASURES_VL
265 WHERE ANALYSIS_OPTION0 =X_A0 AND
266 ANALYSIS_OPTION1 =X_A1 AND
267 ANALYSIS_OPTION2 =X_A2 AND
268 SERIES_ID = X_SERIES_ID AND
269 INDICATOR=X_INDICATOR;
270 ELSE
271 h_series_name := '';
272 END IF;
273 --Validation if there is not record
274 EXCEPTION WHEN OTHERS THEN NULL; END;
275 -- Concat series name
276 IF (h_kpi_type = 1 AND h_kpi_config = 7 AND h_series_name IS NOT NULL) THEN
277 h_val := h_series_name;
278 ELSIF h_series_name IS NOT NULL THEN
279 h_val := h_val || ';' || h_series_name;
280 END IF;
281 END IF;
282
283
284 RETURN h_val;
285 EXCEPTION
286 WHEN OTHERS THEN
287 h_val := h_val || '/' || h_err || '/' || SQLERRM;
288 RETURN h_val;
289 END GET_AOPTS_SERIES_NAMES;
290
291 /* ===========================================================
292 | Description : Return the Alarm Color for Kpi measure.
293 | This color is showed buy Ibuilder
294 |
295 |Psuedo logic
296 | If DefaultMeasure = "BSC" Then
297 | Color = KPI Color -> BSC_DESIGNER_PVT.GET_KPI_COLOR
298 | Elseif Measure is BSC and not DEFault THEN
299 | Color = No Color 'WHITE'
300 | Elseif Measure is PMF and kpi <> PRODUCTION THEN
301 | Color = KPI Color -> BSC_DESIGNER_PVT.GET_KPI_COLOR
302 | Elseif Measure is PMF and kpi = PRODUCTION THEN
303 | Color = No Color 'WHITE'
304 | end if;
305 ===========================================================*/
306 /*FUNCTION GET_MEASURE_COLOR(X_INDICATOR in NUMBER,
307 X_A0 in NUMBER,
308 X_A1 in NUMBER,
309 X_A2 in NUMBER,
310 X_SERIES_ID in NUMBER
311 ) RETURN VARCHAR2 is
312
313 h_kpi_prototype VARCHAR2(10);
314 h_kpi_measure_source bsc_kpi_defaults_b.measure_source%TYPE;
315 h_measure_source BSC_SYS_DATASETS_B.source%type;
316 h_color bsc_kpis_b.prototype_color%TYPE;
317 h_RED bsc_kpis_b.prototype_color%TYPE;
318 h_GREEN bsc_kpis_b.prototype_color%TYPE;
319 h_YELLOW bsc_kpis_b.prototype_color%TYPE;
320 h_GRAY bsc_kpis_b.prototype_color%TYPE;
321 h_LIGHTGRAY bsc_kpis_b.prototype_color%TYPE;
322 h_WHITE bsc_kpis_b.prototype_color%TYPE;
323
324 d_A0 NUMBER;
325 d_A1 NUMBER;
326 d_A2 NUMBER;
327 d_SERIES_ID NUMBER;
328 h_com_default NUMBER(1);
329
330 BEGIN
331 h_RED :='R';
332 h_GREEN :='G';
333 h_YELLOW :='Y';
334 h_GRAY :='X';
335 h_LIGHTGRAY :='L';
336 h_WHITE :='W';
337
338 --Get Defaults
339 SELECT DISTINCT DF.A0_DEFAULT,DF.A1_DEFAULT,DF.A2_DEFAULT,MS.SERIES_ID
340 INTO d_A0,d_A1,d_A2,d_SERIES_ID
341 FROM BSC_DB_COLOR_AO_DEFAULTS_V DF,
342 BSC_KPI_ANALYSIS_MEASURES_B MS
343 WHERE
344 DEFAULT_VALUE =1 AND
345 DF.INDICATOR = MS.INDICATOR AND
346 DF.A0_DEFAULT = MS.ANALYSIS_OPTION0 AND
347 DF.A1_DEFAULT = MS.ANALYSIS_OPTION1 AND
348 DF.A2_DEFAULT = MS.ANALYSIS_OPTION2 AND
349 DF.INDICATOR =X_INDICATOR;
350
351 h_com_default :=0;
352 IF X_A0 = d_A0 AND X_A1 = d_A1 AND X_A2 = d_A2 AND X_SERIES_ID = d_SERIES_ID THEN
353 h_com_default := 1;
354 END IF;
355
356 --Get if the measure is BSC or PMF
357 --Bug #2660714
358 SELECT NVL(DS.SOURCE,'BSC') VAL
359 INTO h_measure_source
360 FROM BSC_KPI_ANALYSIS_MEASURES_B MS,
361 BSC_SYS_DATASETS_B DS
362 WHERE
363 ANALYSIS_OPTION0 =X_A0 AND
364 ANALYSIS_OPTION1 =X_A1 AND
365 ANALYSIS_OPTION2 =X_A2 AND
366 SERIES_ID = X_SERIES_ID AND
367 MS.DATASET_ID = DS.DATASET_ID AND
368 MS.INDICATOR =X_INDICATOR;
369
370 --Get if prototype for pmf
371 SELECT DECODE(PROTOTYPE_FLAG,
372 0,'FALSE',
373 5,'FALSE',
374 6,'FALSE',
375 'TRUE') PROTOTYPE
376 INTO h_kpi_prototype
377 FROM BSC_KPIS_B
378 WHERE INDICATOR =X_INDICATOR;
379 -- Calculate the Color
380 IF h_measure_source ='BSC' THEN
381 IF h_com_default = 1 THEN
382 h_color := BSC_DESIGNER_PVT.GET_KPI_COLOR(X_INDICATOR);
383 ELSE
384 h_color := h_WHITE;
385 END IF;
386 ELSE
387 IF h_kpi_prototype = 'TRUE' THEN
388 h_color := BSC_DESIGNER_PVT.GET_KPI_COLOR(X_INDICATOR);
389 ELSE
390 h_color := h_WHITE;
391 END IF;
392 END IF;
393 RETURN h_color;
394
395 EXCEPTION
396 WHEN OTHERS THEN
397 RETURN h_WHITE;
398 end GET_MEASURE_COLOR;*/
399
400
401 function Get_Aopts_Display_Flag(
402 x_indicator IN number
403 ,x_a0 IN number
404 ,x_a1 IN number
405 ,x_a2 IN number
406 ,x_series_id IN number
407 ) return number is
408
409 -- This function returns the flag value for the analysis option in a multi analysis
410 -- group Indicator. If it encounters a value of 0 (hide) for the flag at any level
411 -- then it returns this zero immediately (there is no need to keep checking for other
412 -- values in the same combination since the zero will hide the entire combination),
413 -- else it returns either 1 (default) or 2 (non-default).
414
415 cursor c_kpi is
416 select indicator_type,config_type
417 from BSC_KPIS_B
418 where indicator = x_indicator;
419
420 h_ag_count number;
421 h_ag1_depend number;
422 h_ag2_depend number;
423 h_series_count number;
424 h_err number;
425 l_disp_flag0 number;
426 l_def_flag0 number;
427 l_disp_flag1 number;
428 l_def_flag1 number;
429 l_disp_flag2 number;
430 l_def_flag2 number;
431 l_disp_flag_show number;
432 l_simul_tree number;
433 l_def_opt_count number;
434
435 begin
436
437 h_err := 0;
438 -- Number of Analysis Groups
439 select max(analysis_group_id)
440 into h_ag_count
441 from BSC_KPI_ANALYSIS_GROUPS
442 where indicator = x_indicator;
443
444 h_err := 1;
445
446 -- Get option id default value for Analysis Group 0.
447 select default_value
448 into l_def_flag0
449 from BSC_KPI_ANALYSIS_GROUPS
450 where indicator = x_indicator
451 and analysis_group_id = 0;
452
453 -- Get Name for A0
454 select user_level1
455 into l_disp_flag0
456 from BSC_KPI_ANALYSIS_OPTIONS_VL
457 where analysis_group_id = 0
458 and option_id = x_a0
459 and indicator = x_indicator;
460
461 -- if 0 then return value immediately.
462 if l_disp_flag0 = 0 then
463 return l_disp_flag0;
464 end if;
465
466 -- Get Name for A1
467 if h_ag_count >= 1 then -- if for A1
468
469 h_err := 2;
470
471 -- Get dependency flag and option id default value for Analysis group 1.
472 select dependency_flag, default_value
473 into h_ag1_depend, l_def_flag1
474 from BSC_KPI_ANALYSIS_GROUPS
475 where analysis_group_id = 1
476 and indicator = x_indicator;
477
478 -- If depend it
479 if h_ag1_depend = 0 then
480
481 h_err := 3;
482 select user_level1
483 into l_disp_flag1
484 from BSC_KPI_ANALYSIS_OPTIONS_VL
485 where analysis_group_id = 1
486 and option_id = x_a1
487 and indicator = x_indicator;
488
489 -- if 0 then return value immediately.
490 if l_disp_flag1 = 0 then
491 return l_disp_flag1;
492 end if;
493
494 else
495
496 h_err := 4;
497 begin
498
499 select user_level1
500 into l_disp_flag1
501 from BSC_KPI_ANALYSIS_OPTIONS_VL
502 where analysis_group_id = 1
503 and option_id = x_a1
504 and parent_option_id = x_a0
505 and indicator = x_indicator;
506
507
508 -- if 0 then return value immediately.
509 if l_disp_flag1 = 0 then
510 return l_disp_flag1;
511 end if;
512
513
514 --Validation if there is no record
515 EXCEPTION
516 WHEN OTHERS THEN
517 null;
518 end;
519
520 end if;
521
522 end if; -- end if for A1.
523
524
525
526 -- Get Name for A2
527 if h_ag_count >= 2 then
528
529 h_err := 6;
530 -- Get dependency flag and option id default value for Analysis group 2.
531 select dependency_flag, default_value
532 into h_ag2_depend, l_def_flag2
533 from BSC_KPI_ANALYSIS_GROUPS
534 where analysis_group_id = 2
535 and indicator = x_indicator;
536
537 -- If dependent
538 if h_ag2_depend = 0 then
539
540 h_err := 7;
541 select user_level1
542 into l_disp_flag2
543 from BSC_KPI_ANALYSIS_OPTIONS_VL
544 where analysis_group_id = 2
545 and option_id = x_a2
546 and indicator = x_indicator;
547
548 if l_disp_flag2 = 0 then
549 return l_disp_flag2;
550 end if;
551
552 else
553
554 -- The AG2 is dependent, but AG1 is not
555 h_err := 8;
556 if h_ag2_depend = 1 and h_ag1_depend = 0 then
557
558 h_err := 9;
559 begin
560 select user_level1
561 into l_disp_flag2
562 from BSC_KPI_ANALYSIS_OPTIONS_VL
563 where analysis_group_id = 2
564 and option_id = x_a2
565 and parent_option_id = x_a1
566 and indicator=x_indicator;
567
568 if l_disp_flag2 = 0 then
569 return l_disp_flag2;
570 end if;
571
572 --Validation if there is not record
573 EXCEPTION
574 WHEN OTHERS THEN
575 NULL;
576 end;
577
578 else
579
580 h_err := 10;
581 begin
582
583 select user_level1
584 into l_disp_flag2
585 from BSC_KPI_ANALYSIS_OPTIONS_VL
586 where analysis_group_id = 2
587 and option_id = x_a2
588 and parent_option_id = x_a1
589 and grandparent_option_id = x_a0
590 and indicator = x_indicator;
591
592 if l_disp_flag2 = 0 then
593 return l_disp_flag2;
594 end if;
595
596 --Validation if there is no record
597 EXCEPTION
598 WHEN OTHERS THEN
599 NULL;
600 END;
601
602 end if;
603
604 end if;
605
606 end if;
607
608 -- determine if this is a simulation tree indicator.
609 select count(indicator)
610 into l_simul_tree
611 from BSC_KPIS_B
612 where indicator = x_indicator
613 and indicator_type = 1
614 and config_type = 7;
615
616 -- Determine value to return.
617 -- First check if this is a simulation tree Indicator.
618 if l_simul_tree <> 0 then
619 select count(a.option_id)
620 into l_def_opt_count
621 from BSC_KPI_ANALYSIS_OPTIONS_B a,
622 BSC_KPI_ANALYSIS_GROUPS b
623 where a.indicator = x_indicator
624 and a.indicator = b.indicator
625 and a.analysis_group_id = b.analysis_group_id
626 and a.option_id = b.default_value
627 and a.user_level1 = 1;
628 if l_def_opt_count > 0 then
629 return 1;
630 else
631 return 0;
632 end if;
633 elsif h_ag_count < 1 then
634 if x_a0 = l_def_flag0 then
635 return 1;
636 else
637 return 2;
638 end if;
639 elsif h_ag_count < 2 then
640 if x_a0 = l_def_flag0 and x_a1 = l_def_flag1 then
641 return 1;
642 else
643 return 2;
644 end if;
645 else
646 if x_a0 = l_def_flag0 and x_a1 = l_def_flag1 and x_a2 = l_def_flag2 then
647 return 1;
648 else
649 return 2;
650 end if;
651 end if;
652
653
654 EXCEPTION
655 WHEN OTHERS THEN
656 return 0;
657 end Get_Aopts_Display_Flag;
658
659 /* ===========================================================
660 | Description : Return the parent level names of a given level
661 | in a string
662 |
663 |Psuedo logic
664 ===========================================================*/
665 FUNCTION GET_LEVEL_PARENT_NAMES(p_level_id IN NUMBER
666 ) RETURN VARCHAR2 IS
667 CURSOR c_parents IS
668 SELECT PL.NAME
669 FROM BSC_SYS_DIM_LEVEL_RELS LR,
670 BSC_SYS_DIM_LEVELS_VL PL
671 WHERE LR.DIM_LEVEL_ID = p_level_id
672 AND PL.DIM_LEVEL_ID = PARENT_DIM_LEVEL_ID;
673 v_name BSC_SYS_DIM_LEVELS_TL.NAME%TYPE;
674 v_parents VARCHAR2(3000) := '';
675 v_count INTEGER := 0;
676 BEGIN
677 OPEN c_parents;
678 LOOP
679 FETCH c_parents INTO v_name;
680 EXIT WHEN c_parents%NOTFOUND;
681 IF v_count > 0 THEN
682 v_parents := v_parents || ', ';
683 END IF;
684 v_parents := v_parents || v_name;
685 v_count := v_count + 1;
686 END LOOP;
687 CLOSE c_parents;
688 RETURN v_parents;
689 EXCEPTION
690 WHEN OTHERS THEN
691 RETURN v_parents;
692 END GET_LEVEL_PARENT_NAMES;
693
694
695 /* ===========================================================
696 | Description :
697 | This function returns the flag value that identify the default
698 | Analsyis option combination for the kpi.
699 |
700 ===========================================================*/
701 FUNCTION GET_AOPTS_DEFAULT_FLAG(
702 x_indicator IN number
703 ,x_a0 IN number
704 ,x_a1 IN number
705 ,x_a2 IN number
706 ,x_series_id IN number
707 ) return number is
708
709 d_A0 NUMBER;
710 d_A1 NUMBER;
711 d_A2 NUMBER;
712 d_SERIES_ID NUMBER;
713 h_com_default NUMBER(1);
714
715 BEGIN
716 --Get Defaults
717 SELECT DISTINCT DF.A0_DEFAULT,DF.A1_DEFAULT,DF.A2_DEFAULT,MS.SERIES_ID
718 INTO d_A0,d_A1,d_A2,d_SERIES_ID
719 FROM BSC_DB_COLOR_AO_DEFAULTS_V DF,
720 BSC_KPI_ANALYSIS_MEASURES_B MS
721 WHERE
722 DEFAULT_VALUE =1 AND
723 DF.INDICATOR = MS.INDICATOR AND
724 DF.A0_DEFAULT = MS.ANALYSIS_OPTION0 AND
725 DF.A1_DEFAULT = MS.ANALYSIS_OPTION1 AND
726 DF.A2_DEFAULT = MS.ANALYSIS_OPTION2 AND
727 DF.INDICATOR =X_INDICATOR;
728
729 h_com_default :=0;
730 IF X_A0 = d_A0 AND X_A1 = d_A1 AND X_A2 = d_A2 AND X_SERIES_ID = d_SERIES_ID THEN
731 h_com_default := 1;
732 END IF;
733 RETURN h_com_default;
734
735 EXCEPTION
736 WHEN OTHERS THEN
737 return 0;
738 end GET_AOPTS_DEFAULT_FLAG;
739
740 function Is_Parent_Tab(
741 p_tab_id number
742 ) return varchar2 is
743
744 l_count number;
745
746 begin
747 SELECT COUNT(*) INTO l_Count
748 FROM BSC_TABS_VL
749 WHERE Parent_Tab_Id = p_tab_id;
750
751
752 IF (l_Count > 1) THEN
753 RETURN 'Y';
754 ELSE
755 RETURN 'N';
756 END IF;
757 EXCEPTION
758 WHEN OTHERS THEN
759 return 'N';
760
761 end Is_Parent_Tab;
762
763
764 /*===========================================================================+
765 |
766 | Name: GET_DATASET_SOURCE
767 |
768 | Description: Return if the dataset_id is BSC OR PMV
769 | Return : 'BSC' : BSC measure
770 | 'PMF' : PMF measure
771 | Parameters: X_DATASET_ID Menu Id that will be inserted
772 +============================================================================*/
773 FUNCTION GET_DATASET_SOURCE(X_DATASET_ID in NUMBER
774 ) RETURN VARCHAR2 IS
775
776 l_tmp VARCHAR2(10);
777 begin
778 IF h_dataset = X_DATASET_ID THEN
779 l_tmp := h_source;
780 ELSE
781 l_tmp := 'BSC';
782 SELECT NVL(SOURCE,'BSC') SOURCE
783 into l_tmp
784 FROM BSC_SYS_DATASETS_B
785 WHERE
786 DATASET_ID = X_DATASET_ID;
787
788 h_dataset := X_DATASET_ID;
789 h_source := l_tmp;
790 END IF;
791 return l_tmp;
792
793 EXCEPTION
794 WHEN OTHERS THEN
795 return l_tmp;
796 end GET_DATASET_SOURCE;
797
798 END BSC_OAF_VIEWS_PVT;
799
[Home] [Help]