[Home] [Help]
PACKAGE BODY: APPS.BSC_COLOR_UPGRADE
Source
1 PACKAGE BODY BSC_COLOR_UPGRADE AS
2 /* $Header: BSCCOLUB.pls 120.11.12000000.1 2007/07/17 07:43:28 appldev noship $ */
3
4
5 FUNCTION set_kpi_measure_ids (
6 x_error_msg OUT NOCOPY VARCHAR2
7 ) RETURN BOOLEAN
8 IS
9
10 CURSOR c_kpi_measure IS
11 SELECT DISTINCT indicator, analysis_option0, analysis_option1, analysis_option2, series_id
12 FROM bsc_kpi_analysis_measures_b
13 WHERE kpi_measure_id IS NULL
14 ORDER BY indicator, analysis_option0, analysis_option1, analysis_option2, series_id;
15 l_kpi_measure_rec c_kpi_measure%ROWTYPE;
16
17 l_id NUMBER;
18
19 BEGIN
20
21 FOR l_kpi_measure_rec IN c_kpi_measure LOOP
22 BEGIN
23 SELECT bsc_kpi_measure_s.NEXTVAL INTO l_id from dual;
24 UPDATE bsc_kpi_analysis_measures_b
25 SET kpi_measure_id = l_id
26 WHERE indicator = l_kpi_measure_rec.indicator
27 AND analysis_option0 = l_kpi_measure_rec.analysis_option0
28 AND analysis_option1 = l_kpi_measure_rec.analysis_option1
29 AND analysis_option2 = l_kpi_measure_rec.analysis_option2
30 AND series_id = l_kpi_measure_rec.series_id
31 AND kpi_measure_id IS NULL;
32 EXCEPTION
33 WHEN OTHERS THEN
34 BSC_MESSAGE.Add( x_message => 'set_kpi_measure_ids() failed for objective= ' || l_kpi_measure_rec.indicator || ' :-' ||SQLERRM
35 , x_source => 'BSCCOLUB.pls'
36 , x_mode => 'I'
37 );
38 END;
39 END LOOP;
40
41 --COMMIT;
42 RETURN TRUE;
43
44 EXCEPTION
45 WHEN OTHERS THEN
46 --ROLLBACK;
47 x_error_msg := SQLERRM;
48 x_error_msg := 'BSC_COLOR_UPGRADE.set_kpi_measure_ids -> ' || x_error_msg;
49 RETURN FALSE;
50 END set_kpi_measure_ids;
51
52
53 FUNCTION set_default_color_rollup (
54 x_error_msg OUT NOCOPY VARCHAR2
55 ) RETURN BOOLEAN
56 IS
57 BEGIN
58
59 UPDATE bsc_kpis_b
60 SET color_rollup_type = 'DEFAULT_KPI',
61 last_update_date = SYSDATE,
62 last_updated_by = FND_GLOBAL.USER_ID
63 WHERE color_rollup_type IS NULL;
64
65 --COMMIT;
66 RETURN TRUE;
67
68 EXCEPTION
69 WHEN OTHERS THEN
70 --ROLLBACK;
71 x_error_msg := SQLERRM;
72 x_error_msg := 'BSC_COLOR_UPGRADE.set_default_color_rollup -> ' || x_error_msg;
73 RETURN FALSE;
74 END set_default_color_rollup;
75
76
77 FUNCTION set_obj_prototype_color (
78 x_error_msg OUT NOCOPY VARCHAR2
79 ) RETURN BOOLEAN
80 IS
81 BEGIN
82 UPDATE bsc_kpis_b
83 SET prototype_color_id = DECODE(prototype_color,
84 'G', 24865,
85 'Y', 49919,
86 'R', 192,
87 'X', 8421504,
88 8421504
89 ),
90 last_update_date = SYSDATE,
91 last_updated_by = FND_GLOBAL.USER_ID
92 WHERE prototype_color_id IS NULL;
93 --COMMIT;
94 RETURN TRUE;
95 EXCEPTION
96 WHEN OTHERS THEN
97 --ROLLBACK;
98 x_error_msg := SQLERRM;
99 x_error_msg := 'BSC_COLOR_UPGRADE.set_obj_prototype_color -> ' || x_error_msg;
100 RETURN FALSE;
101 END set_obj_prototype_color;
102
103
104 FUNCTION get_prototype_color (
105 p_objective_id IN NUMBER
106 , p_kpi_measure_id IN NUMBER
107 , p_default_kpi_measure_id IN NUMBER
108 ) RETURN NUMBER
109 IS
110 l_prototype_color_id NUMBER;
111 BEGIN
112
113 l_prototype_color_id := 24865; -- default to Acceptable
114
115 IF (p_kpi_measure_id = p_default_kpi_measure_id) THEN
116 SELECT prototype_color_id
117 INTO l_prototype_color_id
118 FROM bsc_kpis_b
119 WHERE indicator = p_objective_id;
120 END IF;
121
122 RETURN l_prototype_color_id;
123
124 EXCEPTION
125 WHEN OTHERS THEN
126 -- dont let the caller fail
127 RETURN l_prototype_color_id;
128 END get_prototype_color;
129
130
131 FUNCTION get_color_by_total (
132 p_objective_id IN NUMBER
133 , p_kpi_measure_id IN NUMBER
134 , p_default_kpi_measure_id IN NUMBER
135 ) RETURN NUMBER
136 IS
137 l_color_by_total NUMBER;
138 BEGIN
139
140 l_color_by_total := 1; -- default to ALL
141
142 IF (p_kpi_measure_id = p_default_kpi_measure_id) THEN
143 SELECT property_value
144 INTO l_color_by_total
145 FROM bsc_kpi_properties
146 WHERE indicator = p_objective_id
147 AND property_code = 'COLOR_BY_TOTAL';
148 END IF;
149
150 RETURN l_color_by_total;
151
152 EXCEPTION
153 WHEN OTHERS THEN
154 -- dont let the caller fail
155 RETURN l_color_by_total;
156 END get_color_by_total;
157
158
159 FUNCTION get_disable_color (
160 p_objective_id IN NUMBER
161 , p_kpi_measure_id IN NUMBER
162 , p_default_kpi_measure_id IN NUMBER
163 ) RETURN VARCHAR2
164 IS
165 l_disable_color VARCHAR2(1);
166 l_source VARCHAR2(10);
167 l_obj_sh_name BSC_KPIS_B.short_name%TYPE;
168
169 CURSOR c_measure_source(pkpi_measure_id NUMBER) IS
170 SELECT source
171 FROM bsc_sys_datasets_b dts,
172 bsc_kpi_analysis_measures_b am
173 WHERE dts.dataset_id = am.dataset_id
174 AND am.kpi_measure_id = pkpi_measure_id;
175
176 CURSOR c_objective_short_name(pIndicator NUMBER) IS
177 SELECT short_name
178 FROM bsc_kpis_b
179 WHERE indicator = pIndicator;
180 BEGIN
181
182 l_disable_color := 'T'; -- default to TRUE
183 l_obj_sh_name := NULL;
184
185 IF c_measure_source%ISOPEN THEN
186 CLOSE c_measure_source;
187 END IF;
188 OPEN c_measure_source(p_kpi_measure_id);
189 IF c_measure_source%NOTFOUND THEN
190 RETURN l_disable_color;
191 END IF;
192 FETCH c_measure_source INTO l_source;
193 CLOSE c_measure_source;
194
195 IF c_objective_short_name%ISOPEN THEN
196 CLOSE c_objective_short_name;
197 END IF;
198 OPEN c_objective_short_name(p_objective_id);
199 IF c_objective_short_name%NOTFOUND THEN
200 RETURN l_disable_color;
201 END IF;
202 FETCH c_objective_short_name INTO l_obj_sh_name;
203 CLOSE c_objective_short_name;
204
205 IF l_obj_sh_name IS NULL AND l_source = 'PMF' THEN
206 -- Only those BIS KPIs which are non-AG and non-S2E will have color disabled
207 l_disable_color := 'T';
208 ELSE
209 IF (p_kpi_measure_id = p_default_kpi_measure_id) THEN
210 l_disable_color := 'F';
211 END IF;
212 END IF;
213
214 RETURN l_disable_color;
215
216 EXCEPTION
217 WHEN OTHERS THEN
218 IF c_measure_source%ISOPEN THEN
219 CLOSE c_measure_source;
220 END IF;
221 IF c_objective_short_name%ISOPEN THEN
222 CLOSE c_objective_short_name;
223 END IF;
224 -- dont let the caller fail
225 RETURN l_disable_color;
226 END get_disable_color;
227
228
229 FUNCTION get_apply_color_flag (
230 p_objective_id IN NUMBER
231 , p_kpi_measure_id IN NUMBER
232 ) RETURN NUMBER
233 IS
234 CURSOR c_objective_type(p_indicator NUMBER) IS
235 SELECT indicator_type
236 FROM bsc_kpis_b
237 WHERE indicator = p_indicator;
238 l_apply_color_flag NUMBER;
239 l_multi_series NUMBER;
240 BEGIN
241
242 l_apply_color_flag := 0; -- default to FALSE
243 l_multi_series := 0;
244
245 IF c_objective_type%ISOPEN THEN
246 CLOSE c_objective_type;
247 END IF;
248 OPEN c_objective_type(p_objective_id);
249 FETCH c_objective_type INTO l_multi_series;
250 CLOSE c_objective_type;
251
252 IF (l_multi_series = 10) THEN
253 -- For multi-series, get the budget_flag from bsc_kpi_analysis_measures_b and push to KPI level
254 SELECT budget_flag INTO l_apply_color_flag
255 FROM bsc_kpi_analysis_measures_b
256 WHERE indicator = p_objective_id
257 AND kpi_measure_id = p_kpi_measure_id;
258 ELSE
259 -- For single-bar Objective, get the apply_color_flag from Objective and push to KPI level
260 SELECT apply_color_flag INTO l_apply_color_flag
261 FROM bsc_kpis_b
262 WHERE indicator = p_objective_id;
263 END IF;
264
265 RETURN l_apply_color_flag;
266
267 EXCEPTION
268 WHEN OTHERS THEN
269 IF (c_objective_type%ISOPEN) THEN
270 CLOSE c_objective_type;
271 END IF;
272 -- dont let the caller fail
273 RETURN l_apply_color_flag;
274 END get_apply_color_flag;
275
276
277 FUNCTION set_kpi_measure_props (
278 x_error_msg OUT NOCOPY VARCHAR2
279 ) RETURN BOOLEAN
280 IS
281 CURSOR c_kpi_measure IS
282 SELECT indicator, kpi_measure_id
283 FROM bsc_kpi_analysis_measures_b
284 ORDER BY kpi_measure_id;
285 l_kpi_measure_rec c_kpi_measure%ROWTYPE;
286
287 CURSOR c_kpi_measure_props_exist(p_indicator NUMBER, p_kpi_measure_id NUMBER) IS
288 SELECT COUNT(1)
289 FROM bsc_kpi_measure_props
290 WHERE indicator = p_indicator
291 AND kpi_measure_id = p_kpi_measure_id;
292
293 l_kpi_measure_props_rec BSC_KPI_MEASURE_PROPS_PUB.kpi_measure_props_rec;
294 l_default_kpi_measure_id NUMBER;
295 l_kpi_measure_props_exist NUMBER;
296 l_return_status VARCHAR2(1);
297 l_msg_count NUMBER;
298 l_msg_data VARCHAR2(4000);
299
300 BEGIN
301
302 FOR l_kpi_measure_rec IN c_kpi_measure LOOP
303
304 BEGIN
305 l_kpi_measure_props_exist := 0;
306
307 IF c_kpi_measure_props_exist%ISOPEN THEN
308 CLOSE c_kpi_measure_props_exist;
309 END IF;
310 OPEN c_kpi_measure_props_exist(l_kpi_measure_rec.indicator, l_kpi_measure_rec.kpi_measure_id);
311 FETCH c_kpi_measure_props_exist INTO l_kpi_measure_props_exist;
312 IF c_kpi_measure_props_exist%NOTFOUND THEN
313 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
314 END IF;
315 CLOSE c_kpi_measure_props_exist;
316
317 IF l_kpi_measure_props_exist = 0 THEN
318
319 l_kpi_measure_props_rec.objective_id := l_kpi_measure_rec.indicator;
320 l_kpi_measure_props_rec.kpi_measure_id := l_kpi_measure_rec.kpi_measure_id;
321 l_kpi_measure_props_rec.prototype_trend := BSC_KPI_MEASURE_PROPS_PUB.C_TREND_UNACC_DECREASE;
322 l_kpi_measure_props_rec.disable_color := 'T';
323 l_kpi_measure_props_rec.disable_trend := 'T';
324 l_kpi_measure_props_rec.prototype_color := 24865;
325 l_kpi_measure_props_rec.color_by_total := 1;
326 l_kpi_measure_props_rec.created_by := FND_GLOBAL.USER_ID;
327 l_kpi_measure_props_rec.creation_date := SYSDATE;
328 l_kpi_measure_props_rec.last_updated_by := FND_GLOBAL.USER_ID;
329 l_kpi_measure_props_rec.last_update_date := SYSDATE;
330 l_kpi_measure_props_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
331
332 l_kpi_measure_props_rec.apply_color_flag := get_apply_color_flag
333 ( l_kpi_measure_rec.indicator
334 , l_kpi_measure_rec.kpi_measure_id
335 );
336
337 l_default_kpi_measure_id := BSC_COLOR_CALC_UTIL.Get_Default_Kpi_Measure_Id(l_kpi_measure_rec.indicator);
338 IF l_default_kpi_measure_id IS NULL THEN
339 x_error_msg := 'l_default_kpi_measure_id is NULL for Objective = ' || l_kpi_measure_rec.indicator || ' ;';
340 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
341 END IF;
342
343 IF l_default_kpi_measure_id IS NOT NULL THEN
344 l_kpi_measure_props_rec.disable_color := get_disable_color
345 ( l_kpi_measure_rec.indicator
346 , l_kpi_measure_rec.kpi_measure_id
347 , l_default_kpi_measure_id
348 );
349
350 l_kpi_measure_props_rec.prototype_color := get_prototype_color
351 ( l_kpi_measure_rec.indicator
352 , l_kpi_measure_rec.kpi_measure_id
353 , l_default_kpi_measure_id
354 );
355
356 l_kpi_measure_props_rec.color_by_total := get_color_by_total
357 ( l_kpi_measure_rec.indicator
358 , l_kpi_measure_rec.kpi_measure_id
359 , l_default_kpi_measure_id
360 );
361 END IF;
362
363 BSC_KPI_MEASURE_PROPS_PUB.Create_Kpi_Measure_Props
364 ( p_commit => FND_API.G_FALSE
365 , p_kpi_measure_rec => l_kpi_measure_props_rec
366 , p_cascade_shared => FALSE
367 , x_return_status => l_return_status
368 , x_msg_count => l_msg_count
369 , x_msg_data => l_msg_data
370 );
371 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
372 x_error_msg := l_msg_data;
373 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
374 END IF;
375
376 END IF;
377
378 EXCEPTION
379 WHEN OTHERS THEN
380 BSC_MESSAGE.Add( x_message => 'set_kpi_measure_props() failed for objective= ' || l_kpi_measure_rec.indicator || ' :-' ||SQLERRM
381 , x_source => 'BSCCOLUB.pls'
382 , x_mode => 'I'
383 );
384 END;
385 END LOOP;
386
387 --COMMIT;
388 RETURN TRUE;
389
390 EXCEPTION
391 WHEN OTHERS THEN
392 --ROLLBACK;
393 IF c_kpi_measure_props_exist%ISOPEN THEN
394 CLOSE c_kpi_measure_props_exist;
395 END IF;
396 IF (x_error_msg IS NULL) THEN
397 x_error_msg := SQLERRM;
398 END IF;
399 x_error_msg := 'BSC_COLOR_UPGRADE.set_kpi_measure_props -> ' || x_error_msg;
400 RETURN FALSE;
401 END set_kpi_measure_props;
402
403
404 FUNCTION set_kpimeasure_prototype_flag (
405 x_error_msg OUT NOCOPY VARCHAR2
406 ) RETURN BOOLEAN
407 IS
408 CURSOR c_obj_prototype_flag IS
409 SELECT DISTINCT indicator objective_id
410 , prototype_flag prototype_flag
411 FROM bsc_kpis_b
412 WHERE prototype_flag <> 2;
413 l_obj_prototype_flag_rec c_obj_prototype_flag%ROWTYPE;
414 l_default_kpi_measure_id bsc_kpi_measure_props.kpi_measure_id%TYPE;
415 l_update_flag BOOLEAN;
416 l_count NUMBER;
417
418 BEGIN
419
420 FOR l_obj_prototype_flag_rec IN c_obj_prototype_flag LOOP
421
422 BEGIN
423
424 l_update_flag := FALSE;
425
426 SELECT COUNT(1)
427 INTO l_count
428 FROM bsc_sys_kpi_colors
429 WHERE kpi_measure_id IS NOT NULL
430 AND indicator = l_obj_prototype_flag_rec.objective_id;
431
432 IF l_count = 0 THEN
433 l_update_flag := TRUE; -- either first time upgrade or no color has been calculated as yet.
434 END IF;
435
436 IF l_update_flag THEN
437
438 l_default_kpi_measure_id := BSC_COLOR_CALC_UTIL.Get_Default_Kpi_Measure_Id(l_obj_prototype_flag_rec.objective_id);
439 IF l_default_kpi_measure_id IS NULL THEN
440 x_error_msg := 'l_default_kpi_measure_id is NULL for Objective = ' || l_obj_prototype_flag_rec.objective_id || ' ;';
441 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
442 END IF;
443
444 -- We will update bsc_kpi_analysis_measures_b.prototype_flag always irrespective of the fact
445 -- that whether this script has run before or not. We cannot check for prototype_flag = NULL
446 -- before updating this column, since this column will not be NULL because of a generic UPDATE
447 -- in bscup.sql to value 0. Anyway, updating this column everytime will not affect since we
448 -- are updating it to 7 (color re-calculate). Only for default KPI, we are picking the flag
449 -- from the Objective level value when it is 0.
450 IF l_default_kpi_measure_id IS NOT NULL THEN
451
452 UPDATE bsc_kpi_analysis_measures_b
453 SET prototype_flag = DECODE(l_obj_prototype_flag_rec.prototype_flag,
454 0, 0,
455 7)
456 WHERE indicator = l_obj_prototype_flag_rec.objective_id
457 AND kpi_measure_id = l_default_kpi_measure_id;
458
459 UPDATE bsc_kpi_analysis_measures_b
460 SET prototype_flag = 7
461 WHERE indicator = l_obj_prototype_flag_rec.objective_id
462 AND kpi_measure_id <> l_default_kpi_measure_id;
463
464 ELSE
465
466 UPDATE bsc_kpi_analysis_measures_b
467 SET prototype_flag = 7
468 WHERE indicator = l_obj_prototype_flag_rec.objective_id;
469
470 END IF;
471
472 END IF;
473
474 EXCEPTION
475 WHEN OTHERS THEN
476 BSC_MESSAGE.Add( x_message => 'set_kpimeasure_prototype_flag() failed for objective= ' || l_obj_prototype_flag_rec.objective_id || ' :-' ||SQLERRM
477 , x_source => 'BSCCOLUB.pls'
478 , x_mode => 'I'
479 );
480 END;
481
482 END LOOP;
483
484 RETURN TRUE;
485
486 EXCEPTION
487 WHEN OTHERS THEN
488 --ROLLBACK;
489 IF (x_error_msg IS NULL) THEN
490 x_error_msg := SQLERRM;
491 END IF;
492 x_error_msg := 'BSC_COLOR_UPGRADE.set_kpimeasure_prototype_flag -> ' || x_error_msg;
493 RETURN FALSE;
494 END set_kpimeasure_prototype_flag;
495
496
497 FUNCTION set_kpi_measure_default_calc (
498 x_error_msg OUT NOCOPY VARCHAR2
499 ) RETURN BOOLEAN
500 IS
501 CURSOR c_obj_default_calc IS
502 SELECT indicator objective_id
503 , calculation_id
504 FROM bsc_kpi_calculations
505 WHERE default_value = 1;
506
507 l_obj_default_calc_rec c_obj_default_calc%ROWTYPE;
508 l_default_kpi_measure_id bsc_kpi_measure_props.kpi_measure_id%TYPE;
509
510 BEGIN
511
512 FOR l_obj_default_calc_rec IN c_obj_default_calc LOOP
513 BEGIN
514
515 l_default_kpi_measure_id := BSC_COLOR_CALC_UTIL.Get_Default_Kpi_Measure_Id(l_obj_default_calc_rec.objective_id);
516 IF l_default_kpi_measure_id IS NULL THEN
517 x_error_msg := 'l_default_kpi_measure_id is NULL for Objective = ' || l_obj_default_calc_rec.objective_id || ' ;';
518 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
519 END IF;
520
521 IF l_default_kpi_measure_id IS NOT NULL THEN
522 UPDATE bsc_kpi_measure_props
523 SET default_calculation = l_obj_default_calc_rec.calculation_id,
524 last_update_date = SYSDATE,
525 last_updated_by = FND_GLOBAL.USER_ID
526 WHERE indicator = l_obj_default_calc_rec.objective_id
527 AND kpi_measure_id = l_default_kpi_measure_id
528 AND default_calculation IS NULL;
529 END IF;
530 EXCEPTION
531 WHEN OTHERS THEN
532 BSC_MESSAGE.Add( x_message => 'set_kpi_measure_default_calc() failed for objective= ' || l_obj_default_calc_rec.objective_id || ' :-' ||SQLERRM
533 , x_source => 'BSCCOLUB.pls'
534 , x_mode => 'I'
535 );
536 END;
537
538 END LOOP;
539
540 RETURN TRUE;
541
542 EXCEPTION
543 WHEN OTHERS THEN
544 --ROLLBACK;
545 IF (x_error_msg IS NULL) THEN
546 x_error_msg := SQLERRM;
547 END IF;
548 x_error_msg := 'BSC_COLOR_UPGRADE.set_kpi_measure_default_calc -> ' || x_error_msg;
549 RETURN FALSE;
550 END set_kpi_measure_default_calc;
551
552
553 FUNCTION set_default_kpi_measure_id (
554 x_error_msg OUT NOCOPY VARCHAR2
555 ) RETURN BOOLEAN
556 IS
557 CURSOR c_default_kpi_color IS
558 SELECT DISTINCT indicator
559 FROM bsc_sys_kpi_colors
560 ORDER BY indicator;
561 l_default_kpi_color c_default_kpi_color%ROWTYPE;
562
563 l_default_kpi_measure_id NUMBER;
564 BEGIN
565
566 FOR l_default_kpi_color IN c_default_kpi_color LOOP
567 BEGIN
568
569 l_default_kpi_measure_id := BSC_COLOR_CALC_UTIL.Get_Default_Kpi_Measure_Id(l_default_kpi_color.indicator);
570 IF l_default_kpi_measure_id IS NULL THEN
571 x_error_msg := 'l_default_kpi_measure_id is NULL for Objective = ' || l_default_kpi_color.indicator || ' ;';
572 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
573 END IF;
574 IF l_default_kpi_measure_id IS NOT NULL THEN
575 UPDATE bsc_sys_kpi_colors
576 SET kpi_measure_id = l_default_kpi_measure_id
577 WHERE indicator = l_default_kpi_color.indicator
578 AND kpi_measure_id IS NULL;
579 END IF;
580 EXCEPTION
581 WHEN OTHERS THEN
582 BSC_MESSAGE.Add( x_message => 'set_default_kpi_measure_id() failed for objective= ' || l_default_kpi_color.indicator || ' :-' ||SQLERRM
583 , x_source => 'BSCCOLUB.pls'
584 , x_mode => 'I'
585 );
586 END;
587
588 END LOOP;
589
590 --COMMIT;
591 RETURN TRUE;
592
593 EXCEPTION
594 WHEN OTHERS THEN
595 --ROLLBACK;
596 IF (x_error_msg IS NULL) THEN
597 x_error_msg := SQLERRM;
598 END IF;
599 x_error_msg := 'BSC_COLOR_UPGRADE.set_default_kpi_measure_id -> ' || x_error_msg;
600 RETURN FALSE;
601 END set_default_kpi_measure_id;
602
603
604 FUNCTION set_objective_color (
605 x_error_msg OUT NOCOPY VARCHAR2
606 ) RETURN BOOLEAN
607 IS
608 CURSOR c_obj_colors_count IS
609 SELECT COUNT(1)
610 FROM bsc_sys_objective_colors;
611
612 CURSOR c_kpi_colors_count IS
613 SELECT COUNT(1)
614 FROM bsc_sys_kpi_colors;
615
616 l_obj_color_count NUMBER := 0;
617 l_kpi_color_count NUMBER := 0;
618 BEGIN
619
620 IF c_obj_colors_count%ISOPEN THEN
621 CLOSE c_obj_colors_count;
622 END IF;
623 OPEN c_obj_colors_count;
624 FETCH c_obj_colors_count INTO l_obj_color_count;
625 IF c_obj_colors_count%NOTFOUND THEN
626 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
627 END IF;
628 CLOSE c_obj_colors_count;
629
630 IF l_obj_color_count = 0 THEN
631
632 IF c_kpi_colors_count%ISOPEN THEN
633 CLOSE c_kpi_colors_count;
634 END IF;
635 OPEN c_kpi_colors_count;
636 FETCH c_kpi_colors_count INTO l_kpi_color_count;
637 IF c_kpi_colors_count%NOTFOUND THEN
638 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
639 END IF;
640 CLOSE c_kpi_colors_count;
641
642 IF l_kpi_color_count > 0 THEN
643 BEGIN
644
645 INSERT INTO
646 bsc_sys_objective_colors
647 ( tab_id
648 , indicator
649 , dim_level1
650 , dim_level2
651 , dim_level3
652 , dim_level4
653 , dim_level5
654 , dim_level6
655 , dim_level7
656 , dim_level8
657 , period_id
658 , obj_color
659 , obj_trend
660 , driving_kpi_measure_id
661 )
662 SELECT
663 tab_id
664 , indicator
665 , dim_level1
666 , dim_level2
667 , dim_level3
668 , dim_level4
669 , dim_level5
670 , dim_level6
671 , dim_level7
672 , dim_level8
673 , period_id
674 , kpi_color
675 , kpi_trend
676 , kpi_measure_id
677 FROM bsc_sys_kpi_colors
678 ORDER BY tab_id, indicator;
679 EXCEPTION
680 WHEN OTHERS THEN
681 BSC_MESSAGE.Add( x_message => 'set_objective_color() failed upgrade data to bsc_sys_objective_colors' ||SQLERRM
682 , x_source => 'BSCCOLUB.pls'
683 , x_mode => 'I'
684 );
685 END;
686
687 END IF;
688
689 END IF;
690
691 --COMMIT;
692 RETURN TRUE;
693
694 EXCEPTION
695 WHEN OTHERS THEN
696 --ROLLBACK;
697 IF c_obj_colors_count%ISOPEN THEN
698 CLOSE c_obj_colors_count;
699 END IF;
700 IF c_kpi_colors_count%ISOPEN THEN
701 CLOSE c_kpi_colors_count;
702 END IF;
703 IF (x_error_msg IS NULL) THEN
704 x_error_msg := SQLERRM;
705 END IF;
706 x_error_msg := 'BSC_COLOR_UPGRADE.set_objective_color -> ' || x_error_msg;
707 RETURN FALSE;
708 END set_objective_color;
709
710
711
712 FUNCTION upgrade_kpi_measures (
713 x_error_msg OUT NOCOPY VARCHAR2
714 ) RETURN BOOLEAN
715 IS
716
717 BEGIN
718
719 -- Set KPI_Measure_Ids in BSC_KPI_ANALYSIS_MEASURES_B based on sequence
720 IF NOT set_kpi_measure_ids(x_error_msg) THEN
721 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
722 END IF;
723
724 -- Populate BSC_KPI_MEASURE_PROPS
725 IF NOT set_kpi_measure_props(x_error_msg) THEN
726 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
727 END IF;
728
729 -- Set Prototype Flag at KPI level
730 IF NOT set_kpimeasure_prototype_flag(x_error_msg) THEN
731 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
732 END IF;
733
734 -- Set Default Calculation at KPI level
735 IF NOT set_kpi_measure_default_calc(x_error_msg) THEN
736 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
737 END IF;
738
739 --COMMIT;
740 RETURN TRUE;
741
742 EXCEPTION
743 WHEN OTHERS THEN
744 --ROLLBACK;
745 x_error_msg := 'BSC_COLOR_UPGRADE.upgrade_kpi_measures -> ' || x_error_msg;
746 RETURN FALSE;
747 END upgrade_kpi_measures;
748
749
750 FUNCTION upgrade_objectives (
751 x_error_msg OUT NOCOPY VARCHAR2
752 ) RETURN BOOLEAN
753 IS
754
755 BEGIN
756
757 -- Set BSC_KPIS_B.COLOR_ROLLUP_TYPE as DEFAULT_KPI for all existing Objectives
758 IF NOT set_default_color_rollup(x_error_msg) THEN
759 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
760 END IF;
761
762 -- Set BSC_KPIS_B.PROTOTYPE_COLOR to BSC_SYS_COLORS_B.COLOR_ID instead of G,Y,R,X
763 IF NOT set_obj_prototype_color(x_error_msg) THEN
764 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
765 END IF;
766
767 --COMMIT;
768 RETURN TRUE;
769
770 EXCEPTION
771 WHEN OTHERS THEN
772 --ROLLBACK;
773 x_error_msg := 'BSC_COLOR_UPGRADE.upgrade_objectives -> ' || x_error_msg;
774 RETURN FALSE;
775 END upgrade_objectives;
776
777
778 FUNCTION upgrade_calculated_colors (
779 x_error_msg OUT NOCOPY VARCHAR2
780 ) RETURN BOOLEAN
781 IS
782
783 BEGIN
784
785 -- We need to populate BSC_SYS_KPI_COLORS.KPI_MEASURE_ID with the default KPI for the
786 -- corresponding Objective.
787 IF NOT set_default_kpi_measure_id(x_error_msg) THEN
788 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
789 END IF;
790
791 -- From BSC_SYS_KPI_COLORS, we need to move all the colors to BSC_SYS_OBJECTIVE_COLORS
792 -- (based on DEFAULT_KPI rollup). We dont actually need to find the default KPI for an Objective.
793 -- Just simply moving all rows from BSC_SYS_KPI_COLORS to BSC_SYS_OBJECTIVE_COLORS will do.
794 IF NOT set_objective_color(x_error_msg) THEN
795 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
796 END IF;
797
798 --COMMIT;
799 RETURN TRUE;
800
801 EXCEPTION
802 WHEN OTHERS THEN
803 --ROLLBACK;
804 x_error_msg := 'BSC_COLOR_UPGRADE.upgrade_calculated_colors -> ' || x_error_msg;
805 RETURN FALSE;
806 END upgrade_calculated_colors;
807
808
809 FUNCTION upgrade_ag_calculated_kpis (
810 x_error_msg OUT NOCOPY VARCHAR2
811 ) RETURN BOOLEAN
812 IS
813 CURSOR c_calc_meas IS
814 SELECT bis_ind.short_name short_name,
815 bsc_dts.name name,
816 bsc_dts.help description,
817 bsc_dts.dataset_id dataset_id,
818 bis_ind.actual_data_source actual_data_source
819 FROM bis_indicators bis_ind,
820 bsc_sys_datasets_vl bsc_dts
821 WHERE bis_ind.dataset_id = bsc_dts.dataset_id
822 AND bis_ind.measure_type = 'CDS_CALC';
823
824 CURSOR c_region_obj(p_region_code VARCHAR2) IS
825 SELECT attribute8
826 FROM ak_regions
827 WHERE region_code = p_region_code
828 AND attribute10 = 'BSC_DATA_SOURCE'
829 AND attribute8 IS NOT NULL;
830
831 l_region_code AK_REGIONS.region_code%TYPE;
832 l_attribute8 AK_REGIONS.attribute8%TYPE;
833 l_objective_id NUMBER;
834 l_anal_opt_rec BSC_ANALYSIS_OPTION_PUB.bsc_option_rec_type;
835 x_anal_opt_rec BSC_ANALYSIS_OPTION_PUB.bsc_option_rec_type;
836 x_return_status VARCHAR2(1);
837 x_msg_count NUMBER;
838
839 BEGIN
840 -- Upgrade AG reports' calculated KPIs to have an entry as KPI measure and
841 -- populate default color properties.
842
843 FOR c_calc_meas_rec IN c_calc_meas LOOP
844 BEGIN
845
846 IF c_calc_meas_rec.actual_data_source IS NOT NULL THEN
847
848 l_region_code := NULL;
849 l_attribute8 := NULL;
850
851 l_region_code := SUBSTR(c_calc_meas_rec.actual_data_source, 1, INSTR(c_calc_meas_rec.actual_data_source, '.') - 1);
852
853 IF l_region_code IS NOT NULL THEN
854 FOR c_region_obj_rec IN c_region_obj(l_region_code) LOOP
855
856 l_attribute8 := c_region_obj_rec.attribute8;
857 l_objective_id := TO_NUMBER(SUBSTR(l_attribute8, 1, INSTR(l_attribute8, '.') - 1));
858
859 l_anal_opt_rec.Bsc_Kpi_Id := l_objective_id;
860 l_anal_opt_rec.Bsc_Dataset_Id := c_calc_meas_rec.dataset_id;
861 l_anal_opt_rec.Bsc_Dataset_Default_Value := 1;
862 l_anal_opt_rec.Bsc_Measure_Long_Name := c_calc_meas_rec.name;
863 l_anal_opt_rec.Bsc_Measure_Help := c_calc_meas_rec.description;
864
865 BSC_ANALYSIS_OPTION_PUB.Create_Data_Series (
866 p_commit => FND_API.G_FALSE
867 , p_anal_opt_rec => l_anal_opt_rec
868 , x_anal_opt_rec => x_anal_opt_rec
869 , x_return_status => x_return_status
870 , x_msg_count => x_msg_count
871 , x_msg_data => x_error_msg
872 );
873 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
874 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
875 END IF;
876
877 END LOOP;
878 END IF;
879
880 END IF;
881 EXCEPTION
882 WHEN OTHERS THEN
883 BSC_MESSAGE.Add( x_message => 'upgrade_ag_calculated_kpis() failed for Measure actual data source = ' || c_calc_meas_rec.actual_data_source || ' :-' ||SQLERRM
884 , x_source => 'BSCCOLUB.pls'
885 , x_mode => 'I'
886 );
887 END;
888
889 END LOOP;
890
891 --COMMIT;
892 RETURN TRUE;
893
894 EXCEPTION
895 WHEN OTHERS THEN
896 --ROLLBACK;
897 x_error_msg := 'BSC_COLOR_UPGRADE.upgrade_ag_calculated_kpis -> ' || x_error_msg;
898 RETURN FALSE;
899 END upgrade_ag_calculated_kpis;
900
901
902 FUNCTION upgrade_sys_colors (
903 x_error_msg OUT NOCOPY VARCHAR2
904 ) RETURN BOOLEAN IS
905
906 CURSOR c_sys_init_color IS
907 SELECT property_code, property_value, created_by,
908 creation_date, last_updated_by, last_update_date, last_update_login
909 FROM bsc_sys_init
910 WHERE property_code IN ('LGREEN_COLOR', 'GREEN_COLOR', 'LYELLOW_COLOR'
911 ,'YELLOW_COLOR', 'LRED_COLOR', 'RED_COLOR'
912 ,'LGRAY_COLOR', 'DGRAY_COLOR');
913 BEGIN
914 FOR c_init_colors IN c_sys_init_color LOOP
915 BEGIN
916 IF (c_init_colors.property_code = 'LGREEN_COLOR') THEN
917 UPDATE bsc_sys_colors_b
918 SET user_forecast_color = c_init_colors.property_value,
919 last_update_date = SYSDATE,
920 last_updated_by = FND_GLOBAL.USER_ID
921 WHERE perf_sequence = (SELECT
922 MIN(perf_sequence) FROM bsc_sys_colors_b);
923 ELSIF (c_init_colors.property_code = 'GREEN_COLOR') THEN
924 UPDATE bsc_sys_colors_b
925 SET user_color = c_init_colors.property_value,
926 last_update_date = SYSDATE,
927 last_updated_by = FND_GLOBAL.USER_ID
928 WHERE perf_sequence = (SELECT
929 MIN(perf_sequence) FROM bsc_sys_colors_b);
930 ELSIF (c_init_colors.property_code = 'LYELLOW_COLOR') THEN
931 UPDATE bsc_sys_colors_b
932 SET user_forecast_color = c_init_colors.property_value,
933 last_update_date = SYSDATE,
934 last_updated_by = FND_GLOBAL.USER_ID
935 WHERE short_name = 'AVERAGE_COLOR';
936 ELSIF (c_init_colors.property_code = 'YELLOW_COLOR') THEN
937 UPDATE bsc_sys_colors_b
938 SET user_color = c_init_colors.property_value,
939 last_update_date = SYSDATE,
940 last_updated_by = FND_GLOBAL.USER_ID
941 WHERE short_name = 'AVERAGE_COLOR';
942 ELSIF (c_init_colors.property_code = 'LRED_COLOR') THEN
943 UPDATE bsc_sys_colors_b
944 SET user_forecast_color = c_init_colors.property_value,
945 last_update_date = SYSDATE,
946 last_updated_by = FND_GLOBAL.USER_ID
947 WHERE perf_sequence = (SELECT
948 MAX(perf_sequence) FROM bsc_sys_colors_b);
949 ELSIF (c_init_colors.property_code = 'RED_COLOR') THEN
950 UPDATE bsc_sys_colors_b
951 SET user_color = c_init_colors.property_value,
952 last_update_date = SYSDATE,
953 last_updated_by = FND_GLOBAL.USER_ID
954 WHERE perf_sequence = (SELECT
955 MAX(perf_sequence) FROM bsc_sys_colors_b);
956 ELSIF (c_init_colors.property_code = 'LGRAY_COLOR') THEN
957 UPDATE bsc_sys_colors_b
958 SET user_forecast_color = c_init_colors.property_value,
959 last_update_date = SYSDATE,
960 last_updated_by = FND_GLOBAL.USER_ID
961 WHERE perf_sequence IS NULL;
962 ELSIF (c_init_colors.property_code = 'DGRAY_COLOR') THEN
963 UPDATE bsc_sys_colors_b
964 SET user_color = c_init_colors.property_value,
965 last_update_date = SYSDATE,
966 last_updated_by = FND_GLOBAL.USER_ID
967 WHERE perf_sequence IS NULL;
968 END IF;
969 EXCEPTION
970 WHEN OTHERS THEN
971 BSC_MESSAGE.Add( x_message => 'upgrade_sys_colors() system color upgraded fail for = ' || c_init_colors.property_code || ' :-' ||SQLERRM
972 , x_source => 'BSCCOLUB.pls'
973 , x_mode => 'I'
974 );
975 END;
976 END LOOP;
977
978 -- Delete the data moved.
979 BEGIN
980 DELETE bsc_sys_init
981 WHERE property_code IN ('LGREEN_COLOR', 'GREEN_COLOR', 'LYELLOW_COLOR'
982 ,'YELLOW_COLOR', 'LRED_COLOR', 'RED_COLOR'
983 ,'LGRAY_COLOR', 'DGRAY_COLOR');
984 EXCEPTION
985 WHEN OTHERS THEN
986 BSC_MESSAGE.Add( x_message => 'set_kpi_measure_ids() failed for delete of old system colors :-' ||SQLERRM
987 , x_source => 'BSCCOLUB.pls'
988 , x_mode => 'I'
989 );
990 END;
991 RETURN TRUE;
992 EXCEPTION
993 WHEN OTHERS THEN
994 x_error_msg := 'BSC_COLOR_UPGRADE.upgrade_sys_colors -> ' || SQLERRM;
995 RETURN FALSE;
996 END upgrade_sys_colors;
997
998
999 PROCEDURE create_color_thresholds (
1000 p_objective_id IN NUMBER
1001 , p_kpi_measure_id IN NUMBER
1002 , p_color_method IN NUMBER
1003 , p_color_type IN VARCHAR2
1004 , p_m1_l1 IN NUMBER
1005 , p_m1_l2 IN NUMBER
1006 , p_m2_l1 IN NUMBER
1007 , p_m2_l2 IN NUMBER
1008 , p_m3_l1 IN NUMBER
1009 , p_m3_l2 IN NUMBER
1010 , p_m3_l3 IN NUMBER
1011 , p_m3_l4 IN NUMBER
1012 )
1013 IS
1014 l_threshold THRESHOLD_ARRAY;
1015 l_property_value NUMBER;
1016 x_return_status VARCHAR2(1);
1017 x_msg_count NUMBER(3);
1018 x_msg_data VARCHAR2(2000);
1019 BEGIN
1020 IF (p_color_method = 1 OR p_color_method IS NULL) THEN -- Target Met Above plan
1021 l_threshold := threshold_array(1,2,3);
1022 l_threshold(1) := '1::'|| p_m1_l2 ||':'|| 192; -- Red
1023 l_threshold(2) := '2:'|| p_m1_l2||':'|| p_m1_l1 ||':'|| 49919; -- Yellow
1024 l_threshold(3) := '3:'|| p_m1_l1||'::'|| 24865; --Green
1025 IF (p_color_method IS NULL) THEN
1026 l_property_value := 1;
1027 END IF;
1028 BSC_COLOR_RANGES_PUB.Create_Color_Prop_Ranges(p_objective_id => p_objective_id
1029 ,p_kpi_measure_id => p_kpi_measure_id
1030 ,p_color_type => p_color_type
1031 ,p_threshold_color => l_threshold
1032 ,p_property_value => l_property_value
1033 ,x_return_status => x_return_status
1034 ,x_msg_count => x_msg_count
1035 ,x_msg_data => x_msg_data );
1036 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1037 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1038 END IF;
1039 END IF;
1040 IF (p_color_method = 2 OR p_color_method IS NULL) THEN -- Target Met Below plan
1041 l_threshold := threshold_array(1,2,3);
1042 l_threshold(1) := '1::'|| p_m2_l2 ||':'|| 24865; -- Green
1043 l_threshold(2) := '2:'|| p_m2_l2||':'|| p_m2_l1 ||':'|| 49919; -- Yellow
1044 l_threshold(3) := '3:'||p_m2_l1||'::'|| 192; --Red
1045 IF (p_color_method IS NULL) THEN
1046 l_property_value := 2;
1047 END IF;
1048 BSC_COLOR_RANGES_PUB.Create_Color_Prop_Ranges(p_objective_id => p_objective_id
1049 ,p_kpi_measure_id => p_kpi_measure_id
1050 ,p_color_type => p_color_type
1051 ,p_threshold_color => l_threshold
1052 ,p_property_value => l_property_value
1053 ,x_return_status => x_return_status
1054 ,x_msg_count => x_msg_count
1055 ,x_msg_data => x_msg_data );
1056 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1057 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1058 END IF;
1059 END IF;
1060 IF (p_color_method = 3 OR p_color_method IS NULL) THEN -- Target Met In Between
1061 l_threshold := threshold_array(1,2,3,4,5);
1062 l_threshold(1) := '1::'|| p_m3_l4 ||':'|| 192; -- Red
1063 l_threshold(2) := '2:'|| p_m3_l4||':'|| p_m3_l3 ||':'|| 49919; -- Yellow
1064 l_threshold(3) := '3:'|| p_m3_l3||':'|| p_m3_l2 ||':'|| 24865; --Green
1065 l_threshold(4) := '4:'|| p_m3_l2||':'|| p_m3_l1 ||':'|| 49919; --Yellow
1066 l_threshold(5) := '5:'|| p_m3_l1||'::'|| 192; --Red
1067 IF (p_color_method IS NULL) THEN
1068 l_property_value := 3;
1069 END IF;
1070 BSC_COLOR_RANGES_PUB.Create_Color_Prop_Ranges(p_objective_id => p_objective_id
1071 ,p_kpi_measure_id => p_kpi_measure_id
1072 ,p_color_type => p_color_type
1073 ,p_threshold_color => l_threshold
1074 ,p_property_value => l_property_value
1075 ,x_return_status => x_return_status
1076 ,x_msg_count => x_msg_count
1077 ,x_msg_data => x_msg_data );
1078 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1079 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1080 END IF;
1081 END IF;
1082 END create_color_thresholds;
1083
1084
1085 FUNCTION upgrade_color_thresholds (
1086 x_error_msg OUT NOCOPY VARCHAR2
1087 ) RETURN BOOLEAN IS
1088 l_indicator VARCHAR2(100);
1089 l_m1_l1 NUMBER;
1090 l_m1_l2 NUMBER;
1091 l_m2_l1 NUMBER;
1092 l_m2_l2 NUMBER;
1093 l_m3_l1 NUMBER;
1094 l_m3_l2 NUMBER;
1095 l_m3_l3 NUMBER;
1096 l_m3_l4 NUMBER;
1097 l_color_method NUMBER;
1098 x_return_status VARCHAR2(1);
1099 x_msg_count NUMBER(3);
1100 x_msg_data VARCHAR2(2000);
1101
1102 CURSOR c_objectives IS
1103 SELECT indicator, config_type
1104 FROM bsc_kpis_b
1105 WHERE prototype_flag <> 2;
1106
1107 CURSOR c_anal_measures IS
1108 SELECT an.kpi_measure_id
1109 ,ds.color_method
1110 FROM bsc_kpi_analysis_measures_b an
1111 ,bsc_sys_datasets_b ds
1112 WHERE an.dataset_id = ds.dataset_id
1113 AND an.indicator = l_indicator
1114 AND NOT EXISTS (SELECT
1115 NULL from bsc_color_type_props p
1116 WHERE p.kpi_measure_id = an.kpi_measure_id
1117 );
1118
1119 CURSOR c_obj_threshold IS
1120 SELECT property_code, property_value
1121 FROM bsc_kpi_properties
1122 WHERE property_code in ('COL_M1_LEVEL1', 'COL_M1_LEVEL2', 'COL_M2_LEVEL1', 'COL_M2_LEVEL2',
1123 'COL_M3_LEVEL1', 'COL_M3_LEVEL2', 'COL_M3_LEVEL3', 'COL_M3_LEVEL4')
1124 AND indicator = l_indicator;
1125
1126 BEGIN
1127
1128 FOR c_obj IN c_objectives LOOP
1129
1130 BEGIN
1131
1132 l_indicator := c_obj.indicator;
1133 FOR c_obj_thr IN c_obj_threshold LOOP
1134 IF (c_obj_thr.property_code = 'COL_M1_LEVEL1') THEN
1135 l_m1_l1 := c_obj_thr.property_value;
1136 ELSIF (c_obj_thr.property_code = 'COL_M1_LEVEL2') THEN
1137 l_m1_l2 := c_obj_thr.property_value;
1138 ELSIF (c_obj_thr.property_code = 'COL_M2_LEVEL1') THEN
1139 l_m2_l1 := c_obj_thr.property_value;
1140 ELSIF (c_obj_thr.property_code = 'COL_M2_LEVEL2') THEN
1141 l_m2_l2 := c_obj_thr.property_value;
1142 ELSIF (c_obj_thr.property_code = 'COL_M3_LEVEL1') THEN
1143 l_m3_l1 := c_obj_thr.property_value;
1144 ELSIF (c_obj_thr.property_code = 'COL_M3_LEVEL2') THEN
1145 l_m3_l2 := c_obj_thr.property_value;
1146 ELSIF (c_obj_thr.property_code = 'COL_M3_LEVEL3') THEN
1147 l_m3_l3 := c_obj_thr.property_value;
1148 ELSIF (c_obj_thr.property_code = 'COL_M3_LEVEL4') THEN
1149 l_m3_l4 := c_obj_thr.property_value;
1150 END IF;
1151 END LOOP;
1152
1153
1154 FOR c_anal_mes IN c_anal_measures LOOP
1155
1156 IF (c_obj.config_type = 3) THEN
1157 l_color_method := NULL;
1158 ELSE
1159 l_color_method := c_anal_mes.color_method;
1160 END IF;
1161
1162 create_color_thresholds (
1163 p_objective_id => l_indicator
1164 , p_kpi_measure_id => c_anal_mes.kpi_measure_id
1165 , p_color_method => l_color_method
1166 , p_color_type => 'PERCENT_OF_TARGET'
1167 , p_m1_l1 => l_m1_l1
1168 , p_m1_l2 => l_m1_l2
1169 , p_m2_l1 => l_m2_l1
1170 , p_m2_l2 => l_m2_l2
1171 , p_m3_l1 => l_m3_l1
1172 , p_m3_l2 => l_m3_l2
1173 , p_m3_l3 => l_m3_l3
1174 , p_m3_l4 => l_m3_l4
1175 );
1176
1177 END LOOP;
1178
1179 EXCEPTION
1180 WHEN OTHERS THEN
1181 BSC_MESSAGE.Add( x_message => 'upgrade_color_thresholds() failed for objective= ' || l_indicator || ' :-' ||SQLERRM
1182 , x_source => 'BSCCOLUB.pls'
1183 , x_mode => 'I'
1184 );
1185 END;
1186 END LOOP;
1187
1188 -- Delete the data moved.
1189 /*DELETE bsc_kpi_properties
1190 WHERE property_code in ('COL_M1_LEVEL1', 'COL_M1_LEVEL2', 'COL_M2_LEVEL1', 'COL_M2_LEVEL2',
1191 'COL_M3_LEVEL1', 'COL_M3_LEVEL2', 'COL_M3_LEVEL3', 'COL_M3_LEVEL4');*/
1192
1193 RETURN TRUE;
1194 EXCEPTION
1195 WHEN OTHERS THEN
1196 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1197 x_error_msg := 'BSC_COLOR_UPGRADE.upgrade_color_thresholds ->' || x_msg_data;
1198 ELSE
1199 x_error_msg := 'BSC_COLOR_UPGRADE.upgrade_color_thresholds -> ' || SQLERRM;
1200 END IF;
1201 RETURN FALSE;
1202 END upgrade_color_thresholds;
1203
1204
1205 FUNCTION upgrade_simulation_objectives (
1206 x_error_msg OUT NOCOPY VARCHAR2
1207 ) RETURN BOOLEAN
1208 IS
1209 CURSOR c_old_sims IS
1210 SELECT indicator
1211 FROM bsc_kpis_b
1212 WHERE config_type = 7
1213 AND prototype_flag <> 2
1214 AND short_name IS NULL;
1215
1216 l_return_status VARCHAR2(1);
1217 l_msg_count NUMBER;
1218 l_msg_data VARCHAR2(4000);
1219
1220 BEGIN
1221
1222 FOR cd IN c_old_sims LOOP
1223
1224 BEGIN
1225
1226 BSC_PMF_UI_WRAPPER.Delete_Kpi
1227 ( p_commit => FND_API.G_FALSE
1228 , p_kpi_id => cd.indicator
1229 , x_return_status => l_return_status
1230 , x_msg_count => l_msg_count
1231 , x_msg_data => l_msg_data
1232 );
1233
1234
1235 IF ((l_return_status IS NOT NULL) AND (l_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
1236 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1237 END IF;
1238
1239 EXCEPTION
1240 WHEN OTHERS THEN
1241 BSC_MESSAGE.Add( x_message => 'upgrade_simulation_objectives() failed for objective= ' || cd.indicator || ' :-' || SQLERRM
1242 , x_source => 'BSCCOLUB.pls'
1243 , x_mode => 'I'
1244 );
1245 END;
1246
1247 END LOOP;
1248
1249 DELETE FROM bsc_sys_files
1250 WHERE file_type = 'F1'
1251 AND INDICATOR = 0;
1252
1253 RETURN TRUE;
1254
1255 EXCEPTION
1256 WHEN OTHERS THEN
1257 IF ((l_return_status IS NOT NULL) AND (l_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
1258 x_error_msg := 'BSC_COLOR_UPGRADE.upgrade_simulation_objectives ->' || l_msg_data;
1259 ELSE
1260 x_error_msg := 'BSC_COLOR_UPGRADE.upgrade_simulation_objectives -> ' || SQLERRM;
1261 END IF;
1262 RETURN FALSE;
1263 END upgrade_simulation_objectives;
1264
1265
1266 FUNCTION upgrade_assessments (
1267 x_error_msg OUT NOCOPY VARCHAR2
1268 ) RETURN BOOLEAN IS
1269
1270 CURSOR c_old_kpi_comments IS
1271 SELECT comment_id
1272 ,indicator
1273 ,trend_flag
1274 FROM bsc_kpi_comments
1275 WHERE nvl(trend_flag,0)<10
1276 AND trend_flag <>0
1277 AND indicator IS NOT NULL;
1278
1279 l_old_trend NUMBER;
1280 l_new_trend NUMBER;
1281 l_color NUMBER;
1282 BEGIN
1283 FOR c_kpi_comm IN c_old_kpi_comments LOOP
1284 l_old_trend := c_kpi_comm.trend_flag;
1285 l_color := 0;
1286 l_new_trend := 0;
1287 IF l_old_trend = 1 THEN
1288 l_color := 10;
1289 l_new_trend := 10;
1290 ELSIF l_old_trend = 2 THEN
1291 l_color := 10;
1292 l_new_trend := 14;
1293 ELSIF l_old_trend = 3 THEN
1294 l_color := 10;
1295 l_new_trend := 11;
1296 ELSIF l_old_trend = 4 THEN
1297 l_color := 12;
1298 l_new_trend := 10;
1299 ELSIF l_old_trend = 5 THEN
1300 l_color := 12;
1301 l_new_trend := 14;
1302 ELSIF l_old_trend = 6 THEN
1303 l_color := 12;
1304 l_new_trend := 11;
1305 ELSIF l_old_trend = 7 THEN
1306 l_color := 14;
1307 l_new_trend := 12;
1308 ELSIF l_old_trend = 8 THEN
1309 l_color := 14;
1310 l_new_trend := 14;
1311 ELSIF l_old_trend = 9 THEN
1312 l_color := 14;
1313 l_new_trend := 13;
1314 END IF;
1315
1316 IF l_color<>0 AND l_new_trend <>0 THEN
1317 UPDATE bsc_kpi_comments
1318 SET color_flag=l_color, trend_flag=l_new_trend
1319 WHERE comment_id = c_kpi_comm.comment_id
1320 AND indicator = c_kpi_comm.indicator;
1321 END IF;
1322
1323 END LOOP;
1324
1325 RETURN TRUE;
1326
1327 EXCEPTION
1328 WHEN OTHERS THEN
1329 x_error_msg := 'BSC_COLOR_UPGRADE.upgrade_assessments -> ' || SQLERRM;
1330 RETURN FALSE;
1331 END upgrade_assessments;
1332
1333
1334 END BSC_COLOR_UPGRADE;