1 PACKAGE BODY BSC_COLOR_CALC_UTIL AS
2 /* $Header: BSCCUTLB.pls 120.6.12000000.1 2007/07/17 07:43:38 appldev noship $ */
3
4
5 FUNCTION get_weighted_obj_color (
6 p_pbjective_id IN NUMBER
7 , p_weighted_avg_val IN NUMBER
8 )
9 RETURN NUMBER;
10
11 PROCEDURE get_rollup_obj_color (
12 p_rollup_type IN bsc_kpis_b.color_rollup_type%TYPE
13 , p_colors IN BSC_UPDATE_UTIL.t_array_of_number
14 , p_num_colors IN NUMBER
15 , x_color OUT NOCOPY NUMBER
16 , x_kpi_index OUT NOCOPY NUMBER
17 );
18
19
20 /*
21 * Get_Kpi_Measure_Threshold: Returns the Threshold properties as table.
22 * First it checks threshold corresponding to KPI, in not defined then it
23 * picks the Objective threshold for the KPI (Measure) threshold for color calculation.
24 */
25 FUNCTION Get_Kpi_Measure_Threshold (
26 p_indicator IN NUMBER
27 , p_kpi_measure_id IN NUMBER
28 ) RETURN Threshold_Prop_Table IS
29
30 l_color_range_id NUMBER;
31 l_threshold_Table Threshold_Prop_Table;
32 l_count NUMBER;
33 l_config_type NUMBER;
34
35 -- For P & L Objective threshold.
36 CURSOR c_pl_color IS
37 SELECT color_range_id
38 FROM bsc_color_type_props
39 WHERE indicator = p_indicator
40 --AND kpi_measure_id = p_kpi_measure_id;
41 AND property_value = 1;
42 -- For kpi_measure threshold.
43 CURSOR c_kpi_measure_color IS
44 SELECT color_range_id
45 FROM bsc_color_type_props
46 WHERE indicator = p_indicator
47 AND kpi_measure_id = p_kpi_measure_id;
48
49 -- For weight Objective threshold.
50 CURSOR c_objective_color IS
51 SELECT color_range_id
52 FROM bsc_color_type_props
53 WHERE indicator = p_indicator
54 AND kpi_measure_id IS NULL;
55
56 CURSOR c_threshold_values IS
57 SELECT high, color_id
58 FROM bsc_color_ranges
59 WHERE color_range_id = l_color_range_id
60 ORDER BY color_range_sequence;
61
62 BEGIN
63 SELECT config_type
64 INTO l_config_type
65 FROM bsc_kpis_b
66 WHERE indicator = p_indicator;
67 IF (l_config_type = 3 ) THEN
68 FOR c_pl_color_range IN c_pl_color LOOP
69 l_color_range_id := c_pl_color_range.color_range_id;
70 END LOOP;
71 ELSIF (p_kpi_measure_id IS NULL) THEN -- Get Threshold for Weighted Objective.
72 FOR c_objective_range IN c_objective_color LOOP
73 l_color_range_id := c_objective_range.color_range_id;
74 END LOOP;
75 ELSE -- Get Threshold for kpi_measure.
76 FOR c_kpi_measure_range IN c_kpi_measure_color LOOP
77 l_color_range_id := c_kpi_measure_range.color_range_id;
78 END LOOP;
79 END IF;
80
81 l_count := 1;
82 FOR c_thresholds IN c_threshold_values LOOP
83 l_threshold_Table(l_count).THRESHOLD := c_thresholds.high;
84 l_threshold_Table(l_count).COLOR_ID := c_thresholds.color_id;
85 l_count := l_count + 1;
86 END LOOP;
87
88 RETURN l_threshold_Table;
89 EXCEPTION
90 WHEN OTHERS THEN
91 BSC_MESSAGE.Add(x_message => SQLERRM,
92 x_source => 'BSC_COLOR_CALC_UTIL.Get_Kpi_Measure_Threshold');
93 RETURN l_threshold_Table;
94 END Get_Kpi_Measure_Threshold;
95
96
97 FUNCTION get_color_perf_seq (
98 p_color_id IN bsc_sys_colors_b.color_id%TYPE
99 )
100 RETURN NUMBER IS
101
102 l_index NUMBER;
103 l_array_colors BSC_COLOR_REPOSITORY.t_array_colors;
104 l_color_rec BSC_COLOR_REPOSITORY.t_color_rec;
105
106 BEGIN
107
108 l_array_colors := BSC_COLOR_REPOSITORY.get_color_props();
109
110 FOR l_index IN 1 .. l_array_colors.COUNT LOOP
111 l_color_rec := l_array_colors(l_index);
112 IF l_color_rec.color_id = p_color_id THEN
113 RETURN l_color_rec.perf_seq;
114 END IF;
115 END LOOP;
116
117 RETURN NULL;
118
119 EXCEPTION
120 WHEN OTHERS THEN
121 BSC_MESSAGE.Add(x_message => SQLERRM,
122 x_source => 'BSC_COLOR_CALC_UTIL.get_color_perf_seq');
123 RETURN NULL;
124 END get_color_perf_seq;
125
126
127
128 FUNCTION get_objective_color_method (
129 p_pbjective_id IN NUMBER
130 )
131 RETURN NUMBER IS
132 CURSOR c_obj_color_prop IS
133 SELECT weighted_color_method
134 FROM bsc_kpis_b
135 WHERE indicator = p_pbjective_id;
136
137 l_color_method NUMBER;
138
139 BEGIN
140 IF c_obj_color_prop%ISOPEN THEN
141 CLOSE c_obj_color_prop;
142 END IF;
143
144 OPEN c_obj_color_prop;
145 FETCH c_obj_color_prop INTO l_color_method;
146 IF c_obj_color_prop%NOTFOUND THEN
147 RETURN NULL;
148 END IF;
149
150 CLOSE c_obj_color_prop;
151
152 RETURN l_color_method;
153
154 EXCEPTION
155 WHEN OTHERS THEN
156 IF c_obj_color_prop%ISOPEN THEN
157 CLOSE c_obj_color_prop;
158 END IF;
159 BSC_MESSAGE.Add(x_message => SQLERRM,
160 x_source => 'BSC_COLOR_CALC_UTIL.get_objective_color_method');
161 RETURN NULL;
162 END get_objective_color_method;
163
164
165 FUNCTION get_kpi_measure_weight (
166 p_objective_id IN bsc_kpis_b.indicator%TYPE
167 , p_kpi_measure_id IN bsc_kpi_measure_props.kpi_measure_id%TYPE
168 )
169 RETURN NUMBER IS
170 CURSOR c_kpi_measure_weight IS
171 SELECT weight
172 FROM bsc_kpi_measure_weights
173 WHERE kpi_measure_id = p_kpi_measure_id
174 AND indicator = p_objective_id;
175 l_weight NUMBER;
176 BEGIN
177 l_weight := 0;
178 IF c_kpi_measure_weight%ISOPEN THEN
179 CLOSE c_kpi_measure_weight;
180 END IF;
181
182 OPEN c_kpi_measure_weight;
183 FETCH c_kpi_measure_weight INTO l_weight;
184 IF c_kpi_measure_weight%NOTFOUND THEN
185 RETURN 0;
186 END IF;
187
188 CLOSE c_kpi_measure_weight;
189
190 RETURN l_weight;
191
192 EXCEPTION
193 WHEN OTHERS THEN
194 IF c_kpi_measure_weight%ISOPEN THEN
195 CLOSE c_kpi_measure_weight;
196 END IF;
197 BSC_MESSAGE.Add(x_message => SQLERRM,
198 x_source => 'BSC_COLOR_CALC_UTIL.get_kpi_measure_weight');
199 RETURN 0;
200 END get_kpi_measure_weight;
201
202
203 FUNCTION get_color_numeric_equivalent (
204 p_color_id IN NUMBER
205 )
206 RETURN NUMBER IS
207
208 l_index NUMBER;
209 l_array_colors BSC_COLOR_REPOSITORY.t_array_colors;
210 l_color_rec BSC_COLOR_REPOSITORY.t_color_rec;
211
212 BEGIN
213
214 l_array_colors := BSC_COLOR_REPOSITORY.get_color_props();
215 FOR l_index IN 1 .. l_array_colors.COUNT LOOP
216 l_color_rec := l_array_colors(l_index);
217 IF l_color_rec.color_id = p_color_id THEN
218 RETURN l_color_rec.numeric_eq;
219 END IF;
220 END LOOP;
221
222 RETURN 0;
223
224 EXCEPTION
225 WHEN OTHERS THEN
226 BSC_MESSAGE.Add(x_message => SQLERRM,
227 x_source => 'BSC_COLOR_CALC_UTIL.get_color_numeric_equivalent');
228 RETURN 0;
229 END get_color_numeric_equivalent;
230
231
232 FUNCTION get_max_count (
233 p_array_of_number IN BSC_UPDATE_UTIL.t_array_of_number
234 )
235 RETURN NUMBER IS
236 l_counter NUMBER;
237 l_max_count NUMBER;
238 BEGIN
239 l_max_count := 0;
240
241 IF p_array_of_number IS NOT NULL AND p_array_of_number.COUNT > 0 THEN
242 l_counter := p_array_of_number.FIRST;
243 WHILE l_counter IS NOT NULL LOOP
244 IF l_max_count < p_array_of_number(l_counter) THEN
245 l_max_count := p_array_of_number(l_counter);
246 END IF;
247 l_counter := p_array_of_number.NEXT(l_counter);
248 END LOOP;
249 END IF;
250
251 RETURN l_max_count;
252 EXCEPTION
253 WHEN OTHERS THEN
254 RETURN 0;
255 END get_max_count;
256
257
258
259 PROCEDURE initialize_color_array (
260 p_array_of_number IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number
261 )
262 IS
263 l_index NUMBER;
264 l_array_colors BSC_COLOR_REPOSITORY.t_array_colors;
265 l_color_rec BSC_COLOR_REPOSITORY.t_color_rec;
266
267 BEGIN
268
269 l_array_colors := BSC_COLOR_REPOSITORY.get_color_props();
270 FOR l_index IN 1 .. l_array_colors.COUNT LOOP
271 l_color_rec := l_array_colors(l_index);
272 p_array_of_number(l_color_rec.color_id) := 0;
273 END LOOP;
274
275 EXCEPTION
276 WHEN OTHERS THEN
277 NULL;
278 END initialize_color_array;
279
280
281 FUNCTION Calc_Obj_Color_By_Weights (
282 p_objective_color_rec IN BSC_UPDATE_COLOR.t_objective_color_rec
283 )
284 RETURN BOOLEAN IS
285 CURSOR c_dim_comb(p_indicator NUMBER, p_tab_id NUMBER) IS
286 SELECT DISTINCT
287 dim_level1 || dim_level2 || dim_level3 || dim_level4 || dim_level5 || dim_level6 || dim_level7 || dim_level8 || period_id dim_comb,
288 dim_level1, dim_level2, dim_level3, dim_level4, dim_level5, dim_level6, dim_level7, dim_level8,
289 period_id
290 FROM bsc_sys_kpi_colors
291 WHERE indicator = p_indicator
292 AND tab_id = p_tab_id;
293 l_dim_comb c_dim_comb%ROWTYPE;
294
295 CURSOR c_kpi_colors(p_indicator NUMBER, p_tab_id NUMBER, p_dim_comb VARCHAR2) IS
296 SELECT kpi_measure_id, kpi_color
297 FROM bsc_sys_kpi_colors
298 WHERE dim_level1 || dim_level2 || dim_level3 || dim_level4 || dim_level5 || dim_level6 || dim_level7 || dim_level8 || period_id = p_dim_comb
299 AND indicator = p_indicator
300 AND tab_id = p_tab_id
301 UNION -- bsc_sys_kpi_colors may not contain a color entry for 'color enabled' KPIs for which data has not been loaded
302 SELECT kpi_measure_id, BSC_COLOR_REPOSITORY.NO_COLOR kpi_color
303 FROM bsc_kpi_measure_props
304 WHERE indicator = p_indicator
305 AND disable_color = 'F'
306 AND kpi_measure_id NOT IN
307 ( SELECT kpi_measure_id FROM bsc_sys_kpi_colors
308 WHERE indicator = p_indicator
309 AND tab_id = p_tab_id
310 );
311 l_kpi_colors c_kpi_colors%ROWTYPE;
312
313 l_obj_color NUMBER;
314 l_kpi_weight NUMBER;
315 l_num_eq NUMBER;
316 l_result NUMBER;
317 l_weighted_average_val NUMBER;
318 l_set_no_color BOOLEAN;
319 e_unexpected_error EXCEPTION;
320
321 BEGIN
322
323 FOR l_dim_comb IN c_dim_comb(p_objective_color_rec.objective_id, p_objective_color_rec.tab_id) LOOP
324
325 IF l_dim_comb.dim_comb IS NOT NULL THEN
326
327 l_obj_color := BSC_COLOR_REPOSITORY.NO_COLOR;
328 l_set_no_color := FALSE;
329 l_weighted_average_val := 0;
330
331 FOR l_kpi_colors IN c_kpi_colors(p_objective_color_rec.objective_id, p_objective_color_rec.tab_id, l_dim_comb.dim_comb) LOOP
332
333 l_kpi_weight := get_kpi_measure_weight(p_objective_color_rec.objective_id, l_kpi_colors.kpi_measure_id);
334
335 IF l_kpi_colors.kpi_color = BSC_COLOR_REPOSITORY.NO_COLOR AND l_kpi_weight <> 0 THEN
336 l_obj_color := BSC_COLOR_REPOSITORY.NO_COLOR;
337 l_set_no_color := TRUE;
338 EXIT;
339 END IF;
340
341 l_num_eq := get_color_numeric_equivalent(l_kpi_colors.kpi_color);
342 l_result := (l_kpi_weight/100) * l_num_eq;
343 l_weighted_average_val := l_weighted_average_val + l_result;
344
345 END LOOP;
346
347 IF NOT l_set_no_color THEN
348 l_obj_color := get_weighted_obj_color( p_objective_color_rec.objective_id
349 , l_weighted_average_val
350 );
351 IF l_obj_color IS NULL THEN
352 RAISE e_unexpected_error;
353 END IF;
354 END IF;
355
356 INSERT INTO bsc_sys_objective_colors
357 (tab_id, indicator, dim_level1, dim_level2, dim_level3, dim_level4, dim_level5, dim_level6, dim_level7, dim_level8, period_id, obj_color, driving_kpi_measure_id)
358 VALUES
359 (p_objective_color_rec.tab_id, p_objective_color_rec.objective_id,
360 l_dim_comb.dim_level1, l_dim_comb.dim_level2, l_dim_comb.dim_level3, l_dim_comb.dim_level4,
361 l_dim_comb.dim_level5, l_dim_comb.dim_level6, l_dim_comb.dim_level7, l_dim_comb.dim_level8,
362 l_dim_comb.period_id, l_obj_color, NULL);
363
364 END IF;
365
366 END LOOP;
367
368 COMMIT;
369
370 RETURN TRUE;
371
372 EXCEPTION
373 WHEN e_unexpected_error THEN
374 ROLLBACK;
375 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_KPICOLOR_FAILED'),
376 x_source => 'BSC_COLOR_CALC_UTIL.Calc_Obj_Color_By_Weights');
377 RETURN FALSE;
378 WHEN OTHERS THEN
379 ROLLBACK;
380 BSC_MESSAGE.Add(x_message => SQLERRM,
381 x_source => 'BSC_COLOR_CALC_UTIL.Calc_Obj_Color_By_Weights');
382 RETURN FALSE;
383 END Calc_Obj_Color_By_Weights;
384
385
386 FUNCTION get_weighted_obj_color (
387 p_pbjective_id IN NUMBER
388 , p_weighted_avg_val IN NUMBER
389 )
390 RETURN NUMBER IS
391 l_obj_color NUMBER;
392 l_obj_color_method NUMBER;
393 l_threshold_prop_table threshold_prop_table;
394 BEGIN
395
396 l_obj_color := BSC_COLOR_REPOSITORY.NO_COLOR;
397
398 l_obj_color_method := get_objective_color_method(p_pbjective_id);
399
400 l_threshold_prop_table := Get_Kpi_Measure_Threshold (p_pbjective_id, NULL);
401
402 /*
403 * threshold_prop_table will be formulated as below:
404 *
405 * On the UI, user can enter values in any order of threshold values.
406 *
407 * INCREASING_GOOD :-
408 * The table of records will be ordered by the HIGH value of
409 * the color ranges in ascending order.
410 *
411 * threshold_prop_table:
412 * Operation Threshold Color_Id
413 * <= 20 1
414 * <= 30 2
415 * <= 50 3
416 * <= 90 4
417 * > 90 or ELSE NULL 5
418 *
419 *
420 * DECREASING_GOOD OR WITHIN_RANGE:-
421 * The table of records will be ordered by the HIGH value of
422 * the color ranges in ascending order.
423 *
424 * threshold_prop_table:
425 * Operation Threshold Color_Id
426 * < 20 1
427 * < 30 2
428 * < 50 3
429 * < 90 4
430 * >= 90 or ELSE NULL 5
431 */
432
433 IF l_threshold_prop_table.COUNT > 0 THEN
434
435 IF l_obj_color_method = 1 OR -- INCREASING_GOOD
436 l_obj_color_method = 4 THEN -- FLEXIBLE (not used today)
437
438 FOR l_index IN 1..l_threshold_prop_table.COUNT LOOP
439
440 IF l_threshold_prop_table(l_index).threshold IS NULL THEN
441 l_obj_color := l_threshold_prop_table(l_index).color_id;
442 EXIT;
443 ELSIF p_weighted_avg_val <= l_threshold_prop_table(l_index).threshold THEN
444 l_obj_color := l_threshold_prop_table(l_index).color_id;
445 EXIT;
446 END IF;
447
448 END LOOP;
449
450 ELSIF l_obj_color_method = 2 OR -- DECREASING_GOOD
451 l_obj_color_method = 3 THEN -- WITHIN_RANGE
452
453 FOR l_index IN 1..l_threshold_prop_table.COUNT LOOP
454
455 IF l_threshold_prop_table(l_index).threshold IS NULL THEN
456 l_obj_color := l_threshold_prop_table(l_index).color_id;
457 EXIT;
458 ELSIF p_weighted_avg_val < l_threshold_prop_table(l_index).threshold THEN
459 l_obj_color := l_threshold_prop_table(l_index).color_id;
460 EXIT;
461 END IF;
462
463 END LOOP;
464
465 END IF;
466
467 END IF;
468
469 RETURN l_obj_color;
473 BSC_MESSAGE.Add(x_message => SQLERRM,
470
471 EXCEPTION
472 WHEN OTHERS THEN
474 x_source => 'BSC_COLOR_CALC_UTIL.get_weighted_obj_color');
475 RETURN NULL;
476 END get_weighted_obj_color;
477
478 FUNCTION Get_Sim_Default_Kpi_Measure_Id (
479 p_objective_id IN NUMBER
480 )
481 RETURN NUMBER IS
482
483 CURSOR c_default_node_dataset(p_indicator NUMBER) IS
484 SELECT property_value dataset_id
485 FROM bsc_kpi_properties
486 WHERE property_code = BSC_SIMULATION_VIEW_PUB.c_SIM_NODE_ID
487 AND indicator = p_indicator;
488 l_default_node_dataset_id bsc_kpi_properties.property_value%TYPE;
489
490 CURSOR c_default_kpi(p_indicator NUMBER, p_dataset_id NUMBER) IS
491 SELECT kpi_measure_id
492 FROM bsc_kpi_analysis_measures_b anal_meas
493 WHERE anal_meas.dataset_id = p_dataset_id
494 AND anal_meas.indicator = p_indicator;
495 l_default_kpi_rec c_default_kpi%ROWTYPE;
496
497 l_default_kpi_measure_id bsc_kpi_measure_props.kpi_measure_id%TYPE;
498
499 BEGIN
500
501 l_default_kpi_measure_id := NULL;
502
503 IF c_default_node_dataset%ISOPEN THEN
504 CLOSE c_default_node_dataset;
505 END IF;
506 OPEN c_default_node_dataset (p_objective_id);
507 FETCH c_default_node_dataset INTO l_default_node_dataset_id;
508 IF c_default_node_dataset%NOTFOUND THEN
509 RETURN NULL;
510 END IF;
511 CLOSE c_default_node_dataset;
512
513 FOR l_default_kpi_rec IN c_default_kpi(p_objective_id, l_default_node_dataset_id) LOOP
514 -- Ideally only 1 row must be returned since duplicate datasets are not allowed in Simulation Objective
515 l_default_kpi_measure_id := l_default_kpi_rec.kpi_measure_id;
516 END LOOP;
517
518 RETURN l_default_kpi_measure_id;
519
520 EXCEPTION
521 WHEN OTHERS THEN
522 IF c_default_node_dataset%ISOPEN THEN
523 CLOSE c_default_node_dataset;
524 END IF;
525 BSC_MESSAGE.Add(x_message => SQLERRM,
526 x_source => 'BSC_COLOR_CALC_UTIL.Get_Sim_Default_Kpi_Measure_Id');
527 RETURN NULL;
528 END Get_Sim_Default_Kpi_Measure_Id;
529
530
531 PROCEDURE Calc_Obj_Color_By_Single_Kpi (
532 p_objective_color_rec IN BSC_UPDATE_COLOR.t_objective_color_rec
533 ,p_rollup_type IN bsc_kpis_b.color_rollup_type%TYPE
534 ,x_kpi_measure_id OUT NOCOPY NUMBER
535 ,x_color_flag OUT NOCOPY BOOLEAN
536
537 ) IS
538
539 CURSOR c_dim_comb(p_indicator NUMBER, p_tab_id NUMBER) IS
540 SELECT DISTINCT
541 dim_level1 || dim_level2 || dim_level3 || dim_level4 || dim_level5 || dim_level6 || dim_level7 || dim_level8 || period_id dim_comb,
542 dim_level1, dim_level2, dim_level3, dim_level4, dim_level5, dim_level6, dim_level7, dim_level8,
543 period_id
544 FROM bsc_sys_kpi_colors
545 WHERE indicator = p_indicator
546 AND tab_id = p_tab_id;
547 l_dim_comb c_dim_comb%ROWTYPE;
548
549 CURSOR c_kpi_colors(p_indicator NUMBER, p_tab_id NUMBER, p_dim_comb VARCHAR2) IS
550 SELECT kpi_color, kpi_measure_id
551 FROM bsc_sys_kpi_colors
552 WHERE dim_level1 || dim_level2 || dim_level3 || dim_level4 || dim_level5 || dim_level6 || dim_level7 || dim_level8 || period_id = p_dim_comb
553 AND indicator = p_indicator
554 AND tab_id = p_tab_id
555 UNION -- bsc_sys_kpi_colors may not contain a color entry for 'color enabled' KPIs for which data has not been loaded
556 SELECT BSC_COLOR_REPOSITORY.NO_COLOR kpi_color, kpi_measure_id
557 FROM bsc_kpi_measure_props
558 WHERE indicator = p_indicator
559 AND disable_color = 'F'
560 AND kpi_measure_id NOT IN
561 ( SELECT kpi_measure_id FROM bsc_sys_kpi_colors
562 WHERE indicator = p_indicator
563 AND tab_id = p_tab_id
564 );
565 l_kpi_colors c_kpi_colors%ROWTYPE;
566
567 e_unexpected_error EXCEPTION;
568 l_default_kpi_measure_id bsc_kpi_measure_props.kpi_measure_id%TYPE;
569 l_colors BSC_UPDATE_UTIL.t_array_of_number;
570 l_kpi_measures BSC_UPDATE_UTIL.t_array_of_number;
571 l_color_index NUMBER;
572 l_obj_color NUMBER;
573 l_kpi_index NUMBER := -1;
574
575 BEGIN
576
577 FOR l_dim_comb IN c_dim_comb(p_objective_color_rec.objective_id, p_objective_color_rec.tab_id) LOOP
578
579 IF l_dim_comb.dim_comb IS NOT NULL THEN
580
581 l_color_index := 0;
582 l_obj_color := BSC_COLOR_REPOSITORY.NO_COLOR;
583
584 FOR l_kpi_colors IN c_kpi_colors(p_objective_color_rec.objective_id, p_objective_color_rec.tab_id, l_dim_comb.dim_comb) LOOP
585
586 l_color_index := l_color_index + 1;
587 l_colors(l_color_index) := l_kpi_colors.kpi_color;
588 l_kpi_measures(l_color_index) := l_kpi_colors.kpi_measure_id;
589
590 END LOOP;
591
592 get_rollup_obj_color( p_rollup_type => p_rollup_type
593 , p_colors => l_colors
594 , p_num_colors => l_color_index
595 , x_color => l_obj_color
596 , x_kpi_index => l_kpi_index
597 );
598
599 IF l_obj_color IS NULL THEN
600 RAISE e_unexpected_error;
601 END IF;
602
603 IF l_kpi_index >= 0 THEN
604 x_kpi_measure_id := l_kpi_measures(l_kpi_index);
605 ELSE
609 INSERT INTO bsc_sys_objective_colors
606 x_kpi_measure_id := -1;
607 END IF;
608
610 (tab_id, indicator, dim_level1, dim_level2, dim_level3, dim_level4, dim_level5, dim_level6, dim_level7, dim_level8, period_id, obj_color, driving_kpi_measure_id)
611 VALUES
612 (p_objective_color_rec.tab_id, p_objective_color_rec.objective_id,
613 l_dim_comb.dim_level1, l_dim_comb.dim_level2, l_dim_comb.dim_level3, l_dim_comb.dim_level4,
614 l_dim_comb.dim_level5, l_dim_comb.dim_level6, l_dim_comb.dim_level7, l_dim_comb.dim_level8,
615 l_dim_comb.period_id, l_obj_color, x_kpi_measure_id);
616
617 END IF;
618
619 END LOOP;
620
621 COMMIT;
622
623 x_color_flag := TRUE;
624 --RETURN TRUE;
625
626 EXCEPTION
627 WHEN e_unexpected_error THEN
628 ROLLBACK;
629 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_KPICOLOR_FAILED'),
630 x_source => 'BSC_COLOR_CALC_UTIL.Calc_Obj_Color_By_Single_Kpi');
631 x_kpi_measure_id := -1;
632 x_color_flag := false;
633 --RETURN FALSE;
634 WHEN OTHERS THEN
635 ROLLBACK;
636 BSC_MESSAGE.Add(x_message => SQLERRM,
637 x_source => 'BSC_COLOR_CALC_UTIL.Calc_Obj_Color_By_Single_Kpi');
638 x_kpi_measure_id := -1;
639 x_color_flag := false;
640 --RETURN FALSE;
641 END Calc_Obj_Color_By_Single_Kpi;
642
643
644 /* Returns the worst color out of all the colors in the input array.
645 * Worst color is based on BSC_SYS_COLORS_B.PERF_SEQUENCE.
646 * If any of the KPI colors passed in is GRAY or NO_COLOR, color that
647 * will be returned will also be GRAY.
648 */
649 PROCEDURE get_worst_color (
650 p_colors IN BSC_UPDATE_UTIL.t_array_of_number
651 , p_num_colors IN NUMBER
652 , x_color OUT NOCOPY NUMBER
653 , x_kpi_index OUT NOCOPY NUMBER
654 ) IS
655
656 l_index NUMBER;
657 l_max_perf_seq NUMBER;
658 l_perf_seq NUMBER;
659 l_worst_color BSC_SYS_COLORS_B.color_id%TYPE;
660
661 BEGIN
662
663 IF p_num_colors = 0 THEN
664
665 x_color := BSC_COLOR_REPOSITORY.NO_COLOR;
666 x_kpi_index := -1;
667
668 ELSIF p_num_colors = 1 THEN
669
670 x_color := p_colors(1);
671 x_kpi_index := 1;
672
673 ELSE
674
675 l_max_perf_seq := get_color_perf_seq(p_colors(1));
676 l_worst_color := p_colors(1);
677 x_kpi_index := 1;
678
679 FOR l_index IN 1 .. p_num_colors LOOP
680 -- we could have started counter from 2, but we are doing from 1 since we want
681 -- to check for NO_COLOR for the first color p_colors(1) also.
682
683 IF p_colors(l_index) = BSC_COLOR_REPOSITORY.NO_COLOR THEN
684 x_color := BSC_COLOR_REPOSITORY.NO_COLOR;
685 x_kpi_index := l_index;
686 RETURN;
687 END IF;
688
689 l_perf_seq := get_color_perf_seq(p_colors(l_index));
690 IF l_max_perf_seq < l_perf_seq THEN
691 l_max_perf_seq := l_perf_seq;
692 l_worst_color := p_colors(l_index);
693 x_kpi_index := l_index;
694 END IF;
695
696 END LOOP;
697
698 x_color := l_worst_color;
699
700 END IF;
701
702 EXCEPTION
703 WHEN OTHERS THEN
704 BSC_MESSAGE.Add(x_message => SQLERRM,
705 x_source => 'BSC_COLOR_CALC_UTIL.get_worst_color');
706 --RETURN NULL;
707 END get_worst_color;
708
709 /* Returns the best color out of all the colors in the input array.
710 * Best color is based on BSC_SYS_COLORS_B.PERF_SEQUENCE.
711 * If any of the KPI colors passed in is GRAY or NO_COLOR, color that
712 * will be returned will also be GRAY.
713 */
714 PROCEDURE get_best_color (
715 p_colors IN BSC_UPDATE_UTIL.t_array_of_number
716 , p_num_colors IN NUMBER
717 , x_color OUT NOCOPY NUMBER
718 , x_kpi_index OUT NOCOPY NUMBER
719 ) IS
720
721 l_index NUMBER;
722 l_min_perf_seq NUMBER;
723 l_perf_seq NUMBER;
724 l_best_color BSC_SYS_COLORS_B.color_id%TYPE;
725
726 BEGIN
727
728 IF p_num_colors = 0 THEN
729
730 x_color := BSC_COLOR_REPOSITORY.NO_COLOR;
731 x_kpi_index := -1;
732 RETURN;
733
734 ELSIF p_num_colors = 1 THEN
735
736 x_color := p_colors(1);
737 x_kpi_index := 1;
738 RETURN;
739 ELSE
740
741 l_min_perf_seq := get_color_perf_seq(p_colors(1));
742 l_best_color := p_colors(1);
746 -- we could have started counter from 2, but we are doing from 1 since we want
743 x_kpi_index := 1;
744
745 FOR l_index IN 1 .. p_num_colors LOOP
747 -- to check for NO_COLOR for the first color p_colors(1) also.
748
749 IF p_colors(l_index) = BSC_COLOR_REPOSITORY.NO_COLOR THEN
750 x_color := BSC_COLOR_REPOSITORY.NO_COLOR;
751 x_kpi_index := l_index;
752 RETURN;
753 END IF;
754
755 l_perf_seq := get_color_perf_seq(p_colors(l_index));
756 IF l_min_perf_seq > l_perf_seq THEN
757 l_min_perf_seq := l_perf_seq;
758 l_best_color := p_colors(l_index);
759 x_kpi_index := l_index;
760 END IF;
761
762 END LOOP;
763
764 x_color := l_best_color;
765 RETURN;
766
767 END IF;
768
769 EXCEPTION
770 WHEN OTHERS THEN
771 ROLLBACK;
772 BSC_MESSAGE.Add(x_message => SQLERRM,
773 x_source => 'BSC_COLOR_CALC_UTIL.get_best_color');
774 --RETURN NULL;
775 END get_best_color;
776
777
778 PROCEDURE get_colors_with_max_count (
779 p_array_of_number IN BSC_UPDATE_UTIL.t_array_of_number
780 , p_max_count IN NUMBER
781 , p_colors_array OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number
782 , p_color_count OUT NOCOPY NUMBER
783 )
784 IS
785 l_index NUMBER;
786 l_array_colors BSC_COLOR_REPOSITORY.t_array_colors;
787 l_color_rec BSC_COLOR_REPOSITORY.t_color_rec;
788
789 BEGIN
790
791 p_color_count := 0;
792 l_array_colors := BSC_COLOR_REPOSITORY.get_color_props();
793 FOR l_index IN 1 .. l_array_colors.COUNT LOOP
794 l_color_rec := l_array_colors(l_index);
795
796 IF p_array_of_number(l_color_rec.color_id) = p_max_count THEN
797 p_color_count := p_color_count + 1;
798 p_colors_array(p_color_count) := l_color_rec.color_id;
799 END IF;
800
801 END LOOP;
802
803 EXCEPTION
804 WHEN OTHERS THEN
805 NULL;
806 END get_colors_with_max_count;
807
808
809 /* Returns the color which occurs maximum number of times out of all
810 * the colors in the input array. If 2 or more colors have the same number
811 * of occurences, then the worst color of those will be returned.
812 * If any of the KPI colors passed in is GRAY or NO_COLOR, color that
813 * will be returned will also be GRAY.
814 */
815 PROCEDURE get_most_frequent_color (
816 p_colors IN BSC_UPDATE_UTIL.t_array_of_number
817 , p_num_colors IN NUMBER
818 , x_color OUT NOCOPY NUMBER
819 , x_kpi_index OUT NOCOPY NUMBER
820 ) IS
821
822 l_index NUMBER;
823 l_min_perf_seq NUMBER;
824 l_perf_seq NUMBER;
825 l_most_freq_color BSC_SYS_COLORS_B.color_id%TYPE;
826 l_array_of_number BSC_UPDATE_UTIL.t_array_of_number;
827 l_colors_array BSC_UPDATE_UTIL.t_array_of_number;
828 l_color_count NUMBER;
829 l_max_count NUMBER;
830 i NUMBER;
831 BEGIN
832
833 IF p_num_colors = 0 THEN
834
835 x_color := BSC_COLOR_REPOSITORY.NO_COLOR;
836 x_kpi_index := -1;
837
838 ELSIF p_num_colors = 1 THEN
839
840 x_color := p_colors(1);
841 x_kpi_index := 1;
842
843 ELSE
844
845 l_most_freq_color := BSC_COLOR_REPOSITORY.NO_COLOR;
846 x_kpi_index := -1;
847 initialize_color_array(l_array_of_number);
848
849 FOR l_index IN 1 .. p_num_colors LOOP
850
851 IF p_colors(l_index) = BSC_COLOR_REPOSITORY.NO_COLOR THEN
852 x_color := BSC_COLOR_REPOSITORY.NO_COLOR;
853 x_kpi_index := l_index;
854 RETURN;
855 END IF;
856
857
858 IF l_array_of_number(p_colors(l_index)) > 0 THEN
859 l_array_of_number(p_colors(l_index)) := l_array_of_number(p_colors(l_index)) + 1;
860 ELSE
861 l_array_of_number(p_colors(l_index)) := 1;
862 END IF;
863
864 END LOOP;
865
866 l_max_count := get_max_count(l_array_of_number);
867
868 get_colors_with_max_count(l_array_of_number
869 , l_max_count
870 , l_colors_array
871 , l_color_count);
872
873
874
875 IF(l_color_count=1 and l_max_count > 1) THEN
876 x_color := l_colors_array(1);
877 FOR i in 1..p_colors.COUNT LOOP
878 if( p_colors(i)=x_color) THEN
879 x_kpi_index := i;
880 EXIT;
881 END IF;
882 END LOOP;
883
884
885 ELSE
886 FOR i IN REVERSE 1..l_colors_array.COUNT LOOP
887 x_color := l_colors_array(i);
888 EXIT;
889 END LOOP;
890 FOR i IN 1..p_colors.COUNT LOOP
891 IF(p_colors(i)=x_color) THEN
892 x_kpi_index := i;
893 EXIT;
894 END IF;
895 END LOOP;
896 END IF;
897 END IF;
898
899 EXCEPTION
900 WHEN OTHERS THEN
901 BSC_MESSAGE.Add(x_message => SQLERRM,
902 x_source => 'BSC_COLOR_CALC_UTIL.get_most_frequent_color');
903 --RETURN NULL;
904 END get_most_frequent_color;
905
906
907 PROCEDURE get_rollup_obj_color (
908 p_rollup_type IN bsc_kpis_b.color_rollup_type%TYPE
909 , p_colors IN BSC_UPDATE_UTIL.t_array_of_number
910 , p_num_colors IN NUMBER
911 , x_color OUT NOCOPY NUMBER
912 , x_kpi_index OUT NOCOPY NUMBER
913 ) IS
914 BEGIN
915 IF p_rollup_type = BEST THEN
916 get_best_color(p_colors, p_num_colors, x_color, x_kpi_index);
917 ELSIF p_rollup_type = WORST THEN
918 get_worst_color(p_colors, p_num_colors, x_color, x_kpi_index);
919 ELSIF p_rollup_type = MOST_FREQUENT THEN
920 get_most_frequent_color(p_colors, p_num_colors, x_color, x_kpi_index);
921 END IF;
922 EXCEPTION
923 WHEN OTHERS THEN
924 BSC_MESSAGE.Add(x_message => SQLERRM,
925 x_source => 'BSC_COLOR_CALC_UTIL.get_rollup_obj_color');
926 --RETURN NULL;
927 END get_rollup_obj_color;
928
929
930 FUNCTION Get_Default_Kpi_Measure_Id (
931 p_objective_id IN NUMBER
932 )
933 RETURN NUMBER IS
934
935 CURSOR c_default_kpi(p_indicator NUMBER) IS
936 SELECT m.kpi_measure_id
937 FROM bsc_db_dataset_dim_sets_v m,
938 bsc_db_color_ao_defaults_v d
939 WHERE m.indicator = d.indicator
940 AND m.a0 = d.a0_default
941 AND m.a1 = d.a1_default
942 AND m.a2 = d.a2_default
943 AND m.default_value = 1
944 AND m.indicator = p_indicator;
945
946 l_default_kpi_measure_id bsc_kpi_measure_props.kpi_measure_id%TYPE;
947
948 BEGIN
949
950 l_default_kpi_measure_id := NULL;
951
952 IF c_default_kpi%ISOPEN THEN
953 CLOSE c_default_kpi;
954 END IF;
955 OPEN c_default_kpi (p_objective_id);
956 FETCH c_default_kpi INTO l_default_kpi_measure_id;
957 IF c_default_kpi%NOTFOUND THEN
958 RETURN NULL;
959 END IF;
960 CLOSE c_default_kpi;
961
962 RETURN l_default_kpi_measure_id;
963
964 EXCEPTION
965 WHEN OTHERS THEN
966 IF c_default_kpi%ISOPEN THEN
967 CLOSE c_default_kpi;
968 END IF;
969 BSC_MESSAGE.Add(x_message => SQLERRM,
970 x_source => 'BSC_COLOR_CALC_UTIL.Get_Default_Kpi_Measure_Id');
971 RETURN NULL;
972 END Get_Default_Kpi_Measure_Id;
973
974
975 PROCEDURE Calc_Obj_Color_By_Default_Kpi (
976 p_objective_color_rec IN BSC_UPDATE_COLOR.t_objective_color_rec
977 ,x_kpi_measure_id OUT NOCOPY NUMBER
978 ,x_color_flag OUT NOCOPY BOOLEAN
979 ) IS
980
981 l_default_kpi_measure_id bsc_kpi_measure_props.kpi_measure_id%TYPE;
982
983 BEGIN
984
985 IF p_objective_color_rec.sim_flag THEN -- Simulation Objective
986 l_default_kpi_measure_id := Get_Sim_Default_Kpi_Measure_Id(p_objective_color_rec.objective_id);
987 ELSE
988 l_default_kpi_measure_id := Get_Default_Kpi_Measure_Id(p_objective_color_rec.objective_id);
989 END IF;
990
991 IF l_default_kpi_measure_id IS NULL THEN
992 RAISE FND_API.G_EXC_ERROR;
993 END IF;
994
995 INSERT /*+ append */
996 INTO bsc_sys_objective_colors
997 (tab_id, indicator, dim_level1, dim_level2, dim_level3, dim_level4, dim_level5, dim_level6, dim_level7, dim_level8, period_id, obj_color, driving_kpi_measure_id)
998 SELECT tab_id, indicator, dim_level1, dim_level2, dim_level3, dim_level4, dim_level5, dim_level6, dim_level7, dim_level8, period_id, kpi_color, kpi_measure_id
999 FROM bsc_sys_kpi_colors
1000 WHERE kpi_measure_id = l_default_kpi_measure_id
1001 AND tab_id = p_objective_color_rec.tab_id
1002 AND indicator = p_objective_color_rec.objective_id;
1003
1004 COMMIT;
1005
1006 x_kpi_measure_id := l_default_kpi_measure_id;
1007 x_color_flag := true;
1008
1009 EXCEPTION
1010 WHEN OTHERS THEN
1011 ROLLBACK;
1012 BSC_MESSAGE.Add(x_message => SQLERRM,
1013 x_source => 'BSC_COLOR_CALC_UTIL.Calc_Obj_Color_By_Default_Kpi');
1014 x_color_flag := false;
1015 x_kpi_measure_id := -1;
1016 --RETURN FALSE;
1017 END Calc_Obj_Color_By_Default_Kpi;
1018
1019
1020 FUNCTION Get_Obj_Color_Rollup_Type (
1021 p_objective_id IN NUMBER
1022 )
1023 RETURN VARCHAR2 IS
1024
1025 CURSOR c_rollup_type(p_indicator NUMBER) IS
1026 SELECT color_rollup_type
1027 FROM bsc_kpis_b
1028 WHERE indicator = p_indicator;
1029
1030 l_rollup_type bsc_kpis_b.color_rollup_type%TYPE;
1031
1032 BEGIN
1033
1034 l_rollup_type := NULL;
1035
1036 IF p_objective_id IS NOT NULL THEN
1037 IF c_rollup_type%ISOPEN THEN
1038 CLOSE c_rollup_type;
1039 END IF;
1040 OPEN c_rollup_type (p_objective_id);
1041 FETCH c_rollup_type INTO l_rollup_type;
1042 IF c_rollup_type%NOTFOUND THEN
1043 RETURN NULL;
1044 END IF;
1045 CLOSE c_rollup_type;
1046 END IF;
1047
1048 RETURN l_rollup_type;
1049
1050 EXCEPTION
1051 WHEN OTHERS THEN
1052 IF c_rollup_type%ISOPEN THEN
1053 CLOSE c_rollup_type;
1054 END IF;
1055 BSC_MESSAGE.Add(x_message => SQLERRM,
1056 x_source => 'BSC_COLOR_CALC_UTIL.Get_Obj_Color_Rollup_Type');
1057 RETURN NULL;
1058 END Get_Obj_Color_Rollup_Type;
1059
1060 END BSC_COLOR_CALC_UTIL;
[Home] [Help]