[Home] [Help]
PACKAGE BODY: APPS.BSC_UPDATE_COLOR
Source
1 PACKAGE BODY BSC_UPDATE_COLOR AS
2 /* $Header: BSCDCOLB.pls 120.15.12010000.3 2008/09/18 09:40:23 bijain ship $ */
3
4
5 FUNCTION Color_Kpi_Measure (
6 p_kpi_measure_id IN NUMBER
7 , p_objective_color_rec IN t_objective_color_rec
8 , p_dim_combination IN BSC_UPDATE_UTIL.t_array_of_number
9 , p_num_families IN NUMBER
10 )
11 RETURN BOOLEAN;
12
13 FUNCTION Calculate_KPI_Color (
14 p_objective_id IN NUMBER
15 , p_kpi_measure_id IN NUMBER
16 , p_calc_color_flag IN BOOLEAN
17 )
18 RETURN BOOLEAN;
19
20 PROCEDURE Calculate_Objective_Color (
21 p_objective_color_rec IN BSC_UPDATE_COLOR.t_objective_color_rec
22 ,x_kpi_measure_id OUT NOCOPY NUMBER
23 ,x_color_flag OUT NOCOPY BOOLEAN
24 );
25
26 FUNCTION calculate_kpi_trend_icon (
27 x_tab_id IN NUMBER
28 , x_indicator IN NUMBER
29 , x_measure_id IN NUMBER
30 , x_color_method IN NUMBER
31 , x_calc_obj_trend IN BOOLEAN
32 , x_not_pl_not_initiative in BOOLEAN
33 ) RETURN BOOLEAN;
34
35 --BugFix 6142563
36 FUNCTION is_ytd_default_calc(
37 p_indicator IN NUMBER
38 , p_kpi_measure_id IN NUMBER
39 ) RETURN BOOLEAN IS
40
41 CURSOR c_default_calc IS
42 SELECT default_calculation
43 FROM bsc_kpi_measure_props
44 WHERE indicator = p_indicator
45 AND kpi_measure_id = p_kpi_measure_id;
46
47 l_default_calc bsc_kpi_measure_props.default_calculation%TYPE;
48 BEGIN
49 OPEN c_default_calc;
50 FETCH c_default_calc INTO l_default_calc;
51 CLOSE c_default_calc;
52
53 IF l_default_calc = 2 THEN
54 RETURN TRUE;
55 ELSE
56 RETURN FALSE;
57 END IF;
58
59 EXCEPTION
60 WHEN OTHERS THEN
61 ROLLBACK;
62 BSC_MESSAGE.Add(x_message => SQLERRM,
63 x_source => 'BSC_UPDATE_COLOR.is_ytd_default_calc');
64 RETURN FALSE;
65 END is_ytd_default_calc;
66
67
68
69 FUNCTION Color_Kpis_In_Objective (
70 p_objective_color_rec IN t_objective_color_rec
71 )
72 RETURN BOOLEAN IS
73
74 e_unexpected_error EXCEPTION;
75 /* Modified the code to not calculate colors for
76 * calculated Kpi for AGReport but do calculate
77 * colors for calculated kpi in case of simtree.
78 */
79 CURSOR c_objective_kpis(p_indicator NUMBER) IS
80 SELECT kpi_props.kpi_measure_id,
81 kpi_props.disable_color,
82 sys_dset.source
83 FROM bsc_kpi_measure_props kpi_props,
84 bsc_kpi_analysis_measures_b kpi_meas,
85 bsc_sys_datasets_b sys_dset
86 WHERE kpi_props.indicator = p_indicator
87 AND kpi_props.kpi_measure_id = kpi_meas.kpi_measure_id
88 AND kpi_meas.dataset_id = sys_dset.dataset_id;
89
90 l_objective_kpis c_objective_kpis%ROWTYPE;
91
92 TYPE t_cursor IS REF CURSOR;
93 h_cursor t_cursor;
94
95 h_sql VARCHAR2(2000);
96 h_dim_combination BSC_UPDATE_UTIL.t_array_of_number;
97 h_num_families NUMBER;
98 h_last_com_index NUMBER;
99 h_com_index NUMBER;
100 h_family_index NUMBER;
101 h_dim_index NUMBER;
102 l_message VARCHAR2(4000);
103
104 l_ag_report BOOLEAN;
105 l_config_type BSC_KPIS_B.CONFIG_TYPE%TYPE;
106 l_short_name BSC_KPIS_B.SHORT_NAME%TYPE;
107 BEGIN
108
109 SELECT config_type, short_name into l_config_type,l_short_name
110 FROM bsc_kpis_b
111 WHERE indicator=p_objective_color_rec.objective_id;
112
113 l_ag_report := (l_short_name is not null) and l_config_type <> 7;
114 FOR l_objective_kpis IN c_objective_kpis(p_objective_color_rec.objective_id) LOOP
115
116 -- We will force the color calculation of all the KPIs under the Objective irrespective of the
117 -- prototype_flag being 7 or not.
118 IF (l_objective_kpis.disable_color IS NULL OR l_objective_kpis.disable_color <> 'T') THEN
119 IF(l_ag_report = FALSE OR (l_ag_report = TRUE and l_objective_kpis.source <> 'CDS')) THEN
120 l_message := BSC_UPDATE_UTIL.Get_Message('BSC_COLOR_KPI_START');
121 l_message := BSC_UPDATE_UTIL.Replace_Token(l_message, 'KPI_MEASURE_ID', TO_CHAR(l_objective_kpis.kpi_measure_id));
122 BSC_UPDATE_LOG.Write_Line_log(l_message, BSC_UPDATE_LOG.OUTPUT);
123
124 -- color per each combination of dimension of different families
125 -- in the tab list
126
127 -- Number of families of the tab list
128 h_num_families := 0;
129
130 h_sql := 'SELECT COM_INDEX, FAMILY_INDEX, DIM_INDEX'||
131 ' FROM BSC_TMP_TAB_COM'||
132 ' WHERE TAB_ID = :1'||
133 ' ORDER BY COM_INDEX, FAMILY_INDEX';
134
135 h_last_com_index := -1;
136
137 OPEN h_cursor FOR h_sql USING p_objective_color_rec.tab_id;
138 FETCH h_cursor INTO h_com_index, h_family_index, h_dim_index;
139 WHILE h_cursor%FOUND LOOP
140 IF (h_last_com_index <> h_com_index) AND (h_last_com_index <> -1) THEN
141 --AW_INTEGRATION: pass h_aw_flag to this function
142 IF NOT Color_Kpi_Measure ( l_objective_kpis.kpi_measure_id
143 , p_objective_color_rec
144 , h_dim_combination
145 , h_num_families) THEN
146 RAISE e_unexpected_error;
147 END IF;
148 END IF;
149
150 h_num_families := h_family_index + 1;
151 h_dim_combination(h_family_index) := h_dim_index;
152
153 h_last_com_index := h_com_index;
154
155 FETCH h_cursor INTO h_com_index, h_family_index, h_dim_index;
156
157 END LOOP;
158 CLOSE h_cursor;
159
160 --AW_INTEGRATION: pass h_aw_flag to this function
161 IF NOT Color_Kpi_Measure ( l_objective_kpis.kpi_measure_id
162 , p_objective_color_rec
163 , h_dim_combination
164 , h_num_families) THEN
165 RAISE e_unexpected_error;
166 END IF;
167
168 l_message := BSC_UPDATE_UTIL.Get_Message('BSC_COLOR_KPI_COMPLETE');
169 l_message := BSC_UPDATE_UTIL.Replace_Token(l_message, 'KPI_MEASURE_ID', TO_CHAR(l_objective_kpis.kpi_measure_id));
170 BSC_UPDATE_LOG.Write_Line_log(l_message, BSC_UPDATE_LOG.OUTPUT);
171
172 COMMIT;
173
174 ELSE
175 l_message := BSC_UPDATE_UTIL.Get_Message('BSC_COLOR_KPI_SKIP');
176 l_message := BSC_UPDATE_UTIL.Replace_Token(l_message, 'KPI_MEASURE_ID', TO_CHAR(l_objective_kpis.kpi_measure_id));
177 BSC_UPDATE_LOG.Write_Line_log(l_message, BSC_UPDATE_LOG.OUTPUT);
178 END IF;
179 END IF;
180 END LOOP;
181
182 RETURN TRUE;
183
184 EXCEPTION
185 WHEN e_unexpected_error THEN
186 ROLLBACK;
187 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_KPICOLOR_FAILED'),
188 x_source => 'BSC_UPDATE_COLOR.Color_Kpis_In_Objective');
189 RETURN FALSE;
190 WHEN OTHERS THEN
191 ROLLBACK;
192 BSC_MESSAGE.Add(x_message => SQLERRM,
193 x_source => 'BSC_UPDATE_COLOR.Color_Kpis_In_Objective');
194 RETURN FALSE;
195 END Color_Kpis_In_Objective;
196
197
198 FUNCTION Color_Kpi_Measure (
199 p_kpi_measure_id IN NUMBER
200 , p_objective_color_rec IN t_objective_color_rec
201 , p_dim_combination IN BSC_UPDATE_UTIL.t_array_of_number
202 , p_num_families IN NUMBER
203 )
204 RETURN BOOLEAN IS
205
206 e_unexpected_error EXCEPTION;
207 l_kpi_dim_props BSC_UPDATE_UTIL.t_kpi_dim_props_rec;
208 l_measure_formula bsc_db_color_measures_v.measure_formula%TYPE;
209 l_color_by_total bsc_kpi_measure_props.color_by_total%TYPE;
210 l_apply_color_flag bsc_kpi_measure_props.apply_color_flag%TYPE;
211 l_calc_color_flag BOOLEAN;
212
213 BEGIN
214
215 -- Get the dim_set_id and comp_level_pk_col for the KPI Measure
216 BSC_UPDATE_UTIL.Get_Kpi_Dim_Props ( p_objective_id => p_objective_color_rec.objective_id
217 , p_kpi_measure_id => p_kpi_measure_id
218 , x_dim_props_rec => l_kpi_dim_props
219 );
220
221 -- Get the measure formula
222 l_measure_formula := BSC_UPDATE_UTIL.Get_Measure_Formula ( p_objective_id => p_objective_color_rec.objective_id
223 , p_kpi_measure_id => p_kpi_measure_id
224 , p_sim_objective => p_objective_color_rec.sim_flag
225 );
226
227 l_measure_formula := BSC_UPDATE_UTIL.Get_Free_Div_Zero_Expression(l_measure_formula);
228 IF l_measure_formula IS NULL THEN
229 RAISE e_unexpected_error;
230 END IF;
231
232 l_color_by_total := BSC_UPDATE_UTIL.Get_Color_By_Total ( p_objective_id => p_objective_color_rec.objective_id
233 , p_kpi_measure_id => p_kpi_measure_id
234 );
235
236 IF l_color_by_total IS NULL THEN
237 RAISE e_unexpected_error;
238 END IF;
239
240 l_apply_color_flag := BSC_UPDATE_UTIL.Get_Apply_Color_Flag ( p_objective_id => p_objective_color_rec.objective_id
241 , p_kpi_measure_id => p_kpi_measure_id
242 --, p_sim_objective => p_objective_color_rec.sim_flag
243 );
244
245 IF l_apply_color_flag IS NULL THEN
246 RAISE e_unexpected_error;
247 ELSE
248 IF l_apply_color_flag = 1 THEN
249 l_calc_color_flag := TRUE;
250 ELSE
251 l_calc_color_flag := FALSE;
252 END IF;
253 END IF;
254
255 IF NOT Color_Indic_Dim_Combination( p_objective_color_rec.objective_id
256 , p_kpi_measure_id
257 , l_calc_color_flag
258 , p_objective_color_rec.obj_pl_flag
259 , p_objective_color_rec.obj_initiatives_flag
260 , p_objective_color_rec.obj_precalculated_flag
261 , p_objective_color_rec.tab_id
262 , p_dim_combination
263 , p_num_families
264 , p_objective_color_rec.periodicity_id
265 , l_kpi_dim_props.comp_level_pk_col
266 , l_kpi_dim_props.dim_set_id
267 , l_color_by_total
268 , l_measure_formula
269 , p_objective_color_rec.current_fy
270 , p_objective_color_rec.aw_flag
271 ) THEN
272 RAISE e_unexpected_error;
273 END IF;
274
275 RETURN TRUE;
276
277 EXCEPTION
278 WHEN e_unexpected_error THEN
279 ROLLBACK;
280 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_KPICOLOR_FAILED'),
281 x_source => 'BSC_UPDATE_COLOR.Color_Kpi_Measure');
282 RETURN FALSE;
283 WHEN OTHERS THEN
284 ROLLBACK;
285 BSC_MESSAGE.Add(x_message => SQLERRM,
286 x_source => 'BSC_UPDATE_COLOR.Color_Kpi_Measure');
287 RETURN FALSE;
288 END Color_Kpi_Measure;
289
290
291 FUNCTION Get_Color_Method (
292 p_objective_id IN NUMBER
293 ,p_kpi_measure_id IN NUMBER
294 ) RETURN NUMBER IS
295 l_color_method NUMBER;
296 l_config_type NUMBER;
297 BEGIN
298 SELECT config_type
299 INTO l_config_type
300 FROM bsc_kpis_b
301 WHERE indicator=p_objective_id;
302
303 IF( l_config_type = 3) THEN
304 l_color_method := 1;
305
306 ELSE
307 SELECT color_method
308 INTO l_color_method
309 FROM bsc_sys_datasets_b ds
310 ,bsc_kpi_analysis_measures_b am
311 WHERE ds.dataset_id = am.dataset_id
312 AND am.indicator = p_objective_id
313 AND am.kpi_measure_id = p_kpi_measure_id;
314 END IF;
315
316 RETURN l_color_method;
317 END;
318
319
320 FUNCTION update_actual_budget_for_mcc (
321 p_objective_id IN NUMBER
322 , p_kpi_measure_id IN NUMBER
323 , p_tab_id IN NUMBER
324 , p_lst_keys_insert IN VARCHAR2
325 ) RETURN BOOLEAN
326 IS
327 TYPE t_cursor IS REF CURSOR;
328 l_color_method NUMBER;
329 l_sql_mcc VARCHAR2(32700);
330 l_sql_mcc_actual_plan VARCHAR2(32700);
331 l_dim_comb VARCHAR2(100);
332 l_lst_keys_insert VARCHAR2(100);
333 l_period NUMBER;
334 l_mcc_color NUMBER;
335 l_mcc_perf_seq NUMBER;
336 l_real NUMBER;
337 l_plan NUMBER;
338 l_cumpercent NUMBER;
339 l_tmp_cumpercent NUMBER;
340 l_mcc_real NUMBER;
341 l_mcc_plan NUMBER;
342 l_tmp_cumpercent_diff NUMBER;
343 l_num_keys NUMBER;
344 l_mcc_cursor t_cursor;
345 l_mcc_actual_plan_cursor t_cursor;
346 l_lst_keys_insert_array BSC_UPDATE_UTIL.t_array_of_varchar2;
347 l_lst_keys_array BSC_UPDATE_UTIL.t_array_of_varchar2;
348
349 BEGIN
350
351 -- p_lst_keys_insert can be of the following format:
352 -- KEY1, 0, 0, 0, 0, 0, 0, 0,
353 -- 0, KEY1, 0, 0, 0, 0, 0, 0,
354 -- Remove trailing comma (,)
355 l_lst_keys_insert := SUBSTR(p_lst_keys_insert, 1, instr(p_lst_keys_insert, ',', -1) - 1);
356 l_num_keys := BSC_UPDATE_UTIL.decompose_varchar2_list(l_lst_keys_insert, l_lst_keys_insert_array, ',');
357 FOR l_index IN 1 .. l_lst_keys_insert_array.COUNT LOOP
358 IF (l_lst_keys_insert_array(l_index) = '0') THEN
359 l_lst_keys_insert_array(l_index) := '''$#''';
360 END IF;
361 END LOOP;
362 l_lst_keys_insert := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(l_lst_keys_insert_array, l_num_keys);
363
364 l_color_method := get_color_method(p_objective_id, p_kpi_measure_id);
365
366 l_sql_mcc := 'SELECT ' || l_lst_keys_insert || ', period, col_b.color_id FROM ( ' ||
367 ' SELECT ' || l_lst_keys_insert || ', period , MAX(col_b.perf_sequence) mcc ' ||
368 ' FROM bsc_tmp_colors tem_c, bsc_sys_colors_b col_b ' ||
369 ' WHERE tem_c.color = col_b.color_id ' ||
370 ' GROUP BY ' || l_lst_keys_insert || ' , period ' ||
371 ' ) bsc_tmp_colors_mcc, bsc_sys_colors_b col_b ' ||
372 ' WHERE bsc_tmp_colors_mcc.mcc = col_b.perf_sequence ';
373
374 OPEN l_mcc_cursor FOR l_sql_mcc;
375 FETCH l_mcc_cursor INTO l_lst_keys_array(1), l_lst_keys_array(2), l_lst_keys_array(3), l_lst_keys_array(4), l_lst_keys_array(5), l_lst_keys_array(6), l_lst_keys_array(7), l_lst_keys_array(8) , l_period, l_mcc_color;
376 WHILE l_mcc_cursor%FOUND LOOP
377
378 l_tmp_cumpercent := NULL;
379 l_tmp_cumpercent_diff := NULL;
380 l_mcc_real := NULL;
381 l_mcc_plan := NULL;
382
383 l_sql_mcc_actual_plan := 'SELECT vreal, vplan, cumpercent ' ||
384 ' FROM bsc_tmp_colors tem_c ' ||
385 ' WHERE tem_c.color = ' || l_mcc_color;
386
387 FOR l_index IN 1 .. l_lst_keys_insert_array.COUNT LOOP
388 l_sql_mcc_actual_plan := l_sql_mcc_actual_plan ||
389 ' AND ' || l_lst_keys_insert_array(l_index) || ' = DECODE(''' || l_lst_keys_array(l_index) || ''', ''$#'', ' || l_lst_keys_insert_array(l_index) || ', ''' || l_lst_keys_array(l_index) || ''') ';
390 END LOOP;
391 l_sql_mcc_actual_plan := l_sql_mcc_actual_plan ||
392 ' AND period = ' || l_period;
393
394 OPEN l_mcc_actual_plan_cursor FOR l_sql_mcc_actual_plan;
395 FETCH l_mcc_actual_plan_cursor INTO l_real, l_plan, l_cumpercent;
396 WHILE l_mcc_actual_plan_cursor%FOUND LOOP
397 -- For a single call to this API, only one of the following conditions will be TRUE
398 IF (l_color_method = 1) THEN
399 -- the one with lowest cumpercent will be chosen
400 IF l_tmp_cumpercent IS NULL THEN
401 l_tmp_cumpercent := l_cumpercent;
402 l_mcc_plan := l_plan;
403 l_mcc_real := l_real;
404 ELSE
405 -- (l_real < l_mcc_real) is just a convention being followed so that we get unique values
406 IF (l_cumpercent < l_tmp_cumpercent) OR (l_cumpercent = l_tmp_cumpercent AND l_real < l_mcc_real) THEN
407 l_mcc_plan := l_plan;
408 l_mcc_real := l_real;
409 l_tmp_cumpercent := l_cumpercent;
410 END IF;
411 END IF;
412 ELSIF (l_color_method = 2) THEN
413 -- the one with highest cumpercent will be chosen
414 IF l_tmp_cumpercent IS NULL THEN
415 l_tmp_cumpercent := l_cumpercent;
416 l_mcc_plan := l_plan;
417 l_mcc_real := l_real;
418 ELSE
419 -- (l_real > l_mcc_real) is just a convention being followed so that we get unique values
420 IF (l_cumpercent > l_tmp_cumpercent) OR (l_cumpercent = l_tmp_cumpercent AND l_real > l_mcc_real) THEN
421 l_mcc_plan := l_plan;
422 l_mcc_real := l_real;
423 l_tmp_cumpercent := l_cumpercent;
424 END IF;
425 END IF;
426 ELSIF (l_color_method = 3) THEN
427 -- the one with cumpercent closest to 100 will be chosen
428 IF l_tmp_cumpercent_diff IS NULL THEN
429 l_tmp_cumpercent_diff := ABS(l_cumpercent - 100);
430 l_mcc_plan := l_plan;
431 l_mcc_real := l_real;
432 ELSE
433 -- (l_real > l_mcc_real) is just a convention being followed so that we get unique values
434 IF (ABS(l_cumpercent - 100) > l_tmp_cumpercent_diff) OR (ABS(l_cumpercent - 100) = l_tmp_cumpercent_diff AND l_real > l_mcc_real) THEN
435 l_mcc_plan := l_plan;
436 l_mcc_real := l_real;
437 l_tmp_cumpercent_diff := ABS(l_cumpercent - 100);
438 END IF;
439 END IF;
440 END IF;
441
442 FETCH l_mcc_actual_plan_cursor INTO l_real, l_plan, l_cumpercent;
443 END LOOP;
444 CLOSE l_mcc_actual_plan_cursor;
445
446 IF l_mcc_real IS NOT NULL THEN
447
448 UPDATE bsc_sys_kpi_colors
449 SET actual_data = l_mcc_real, budget_data = l_mcc_plan
450 WHERE tab_id = p_tab_id
451 AND indicator = p_objective_id
452 AND kpi_measure_id = p_kpi_measure_id
453 AND dim_level1 = DECODE(l_lst_keys_array(1), '$#', '0', l_lst_keys_array(1))
454 AND dim_level2 = DECODE(l_lst_keys_array(2), '$#', '0', l_lst_keys_array(2))
455 AND dim_level3 = DECODE(l_lst_keys_array(3), '$#', '0', l_lst_keys_array(3))
456 AND dim_level4 = DECODE(l_lst_keys_array(4), '$#', '0', l_lst_keys_array(4))
457 AND dim_level5 = DECODE(l_lst_keys_array(5), '$#', '0', l_lst_keys_array(5))
458 AND dim_level6 = DECODE(l_lst_keys_array(6), '$#', '0', l_lst_keys_array(6))
459 AND dim_level7 = DECODE(l_lst_keys_array(7), '$#', '0', l_lst_keys_array(7))
460 AND dim_level8 = DECODE(l_lst_keys_array(8), '$#', '0', l_lst_keys_array(8))
461 AND period_id = l_period;
462
463 END IF;
464
465 FETCH l_mcc_cursor INTO l_lst_keys_array(1), l_lst_keys_array(2), l_lst_keys_array(3), l_lst_keys_array(4), l_lst_keys_array(5), l_lst_keys_array(6), l_lst_keys_array(7), l_lst_keys_array(8) , l_period, l_mcc_color;
466 END LOOP;
467 CLOSE l_mcc_cursor;
468
469 RETURN TRUE;
470
471 EXCEPTION
472 WHEN OTHERS THEN
473 BSC_MESSAGE.Add(x_message => SQLERRM,
474 x_source => 'BSC_UPDATE_COLOR.update_actual_budget_for_mcc');
475 RETURN FALSE;
476 END update_actual_budget_for_mcc;
477
478
479 FUNCTION get_trend_flag(p_color_method IN NUMBER,
480 p_actual IN NUMBER,
481 p_prior IN NUMBER
482 ) RETURN NUMBER
483 IS
484 l_trendflag NUMBER;
485 BEGIN
486 IF(p_color_method=1) THEN
487 IF (p_actual>p_prior) THEN
488 l_trendflag := 0;
489 ELSIF (p_actual<p_prior) THEN
490 l_trendflag := 3;
491 ELSE
492 l_trendflag := 4;
493 END IF;
494 ELSIF (p_color_method=2) THEN
495 IF (p_actual>p_prior) THEN
496 l_trendflag := 2;
497 ELSIF (p_actual<p_prior) THEN
498 l_trendflag := 1;
499 ELSE
500 l_trendflag := 4;
501 END IF;
502 ELSIF (p_color_method=3) THEN
503 IF (p_actual>p_prior) THEN
504 l_trendflag := 2;
505 ELSIF (p_actual<p_prior) THEN
506 l_trendflag := 3;
507 ELSE
508 l_trendflag := 4;
509 END IF;
510 END IF;
511
512 RETURN l_trendflag;
513
514 EXCEPTION
515 WHEN OTHERS THEN
516 ROLLBACK;
517 BSC_MESSAGE.Add(x_message => SQLERRM,
518 x_source => 'BSC_UPDATE_COLOR.get_trend_flag');
519 RETURN 5;--No Data
520 END get_trend_flag;
521
522 --BugFix 6000042 Trend for Comparison Mode
523 PROCEDURE update_trend_for_comparison(
524 p_indic_code IN NUMBER,
525 p_kpi_measure_id IN NUMBER,
526 p_tab_id IN NUMBER,
527 p_lst_keys_insert IN VARCHAR2,
528 p_sql_mcc IN VARCHAR2
529 ) IS
530
531 TYPE t_cursor IS REF CURSOR;
532 h_sql_trend VARCHAR2(32700);
533 h_actualvalue_sql VARCHAR2(32700);
534 h_priorvalue_sql VARCHAR2(32700);
535 h_match_sql VARCHAR2(32700);
536 h_match_cursor t_cursor;
537 h_trend_cursor t_cursor;
538 h_actual_value_cursor t_cursor;
539 h_prior_value_cursor t_cursor;
540 h_trend_period NUMBER;
541 h_trend_color NUMBER;
542
543 h_trend_vreal NUMBER;
544 h_color_method NUMBER;
545 h_key1 NUMBER;
546 h_key2 NUMBER;
547 h_key3 NUMBER;
548 h_key4 NUMBER;
549 h_key5 NUMBER;
550 h_key6 NUMBER;
551 h_key7 NUMBER;
552 h_key8 NUMBER;
553 --Added today
554 h_prev_period NUMBER;
555 h_trend_keys VARCHAR2(100);
556 l_index NUMBER := 1;
557 l_key_rec t_key_rec;
558 l_key_tbl BSC_UPDATE_COLOR.t_key_tbl_type;
559 l_first_time BOOLEAN := true;
560 l_prev_real NUMBER;
561 l_count NUMBER;
562 --l_starttime timestamp;
563 --l_endtime timestamp;
564 BEGIN
565 --l_starttime := sysdate;
566 --BugFix 6142563
567 IF is_ytd_default_calc( p_indicator => p_indic_code
568 , p_kpi_measure_id => p_kpi_measure_id) THEN
569 RETURN;
570 END IF;
571 h_color_method := Get_Color_Method(p_indic_code,p_kpi_measure_id);
572 h_sql_trend := ' SELECT '||p_lst_keys_insert||'PERIOD, COL_B.COLOR_ID '||
573 ' FROM ('||p_sql_mcc||') BSC_TMP_COLORS_MCC, BSC_SYS_COLORS_B COL_B'||
574 ' WHERE BSC_TMP_COLORS_MCC.MCC = COL_B.PERF_SEQUENCE '||
575 ' ORDER BY PERIOD ';
576 OPEN h_trend_cursor FOR h_sql_trend;
577 FETCH h_trend_cursor INTO h_key1,h_key2,h_key3,h_key4,h_key5,h_key6,h_key7,h_key8,h_trend_period, h_trend_color;
578 WHILE h_trend_cursor%FOUND LOOP
579 h_actualvalue_sql := 'SELECT VREAL, nvl(key1,0)||nvl(key2,0)||nvl(key3,0)||nvl(key4,0)||nvl(key5,0)||nvl(key6,0)||nvl(key7,0)||nvl(key8,0) DV_COMB '||
580 ' FROM BSC_TMP_COLORS '||
581 ' WHERE PERIOD=:1 AND COLOR=:2 '||
582 ' AND ('||h_key1||'=0 OR nvl(key1,0)='||h_key1||')'||
583 ' AND ('||h_key2||'=0 OR nvl(key2,0)='||h_key2||')'||
584 ' AND ('||h_key3||'=0 OR nvl(key3,0)='||h_key3||')'||
585 ' AND ('||h_key4||'=0 OR nvl(key4,0)='||h_key4||')'||
586 ' AND ('||h_key5||'=0 OR nvl(key5,0)='||h_key5||')'||
587 ' AND ('||h_key6||'=0 OR nvl(key6,0)='||h_key6||')'||
588 ' AND ('||h_key7||'=0 OR nvl(key7,0)='||h_key7||')'||
589 ' AND ('||h_key8||'=0 OR nvl(key8,0)='||h_key8||')'||
590 ' ORDER BY PERIOD, DV_COMB ';
591 l_first_time := true;
592
593 OPEN h_actual_value_cursor FOR h_actualvalue_sql USING h_trend_period, h_trend_color;-- USING h_key1,h_key2,h_key3,h_key4,h_key5,h_key6,h_key7,h_key8,h_trend_period, h_trend_color;
594 FETCH h_actual_value_cursor INTO h_trend_vreal, h_trend_keys;
595 WHILE h_actual_value_cursor%FOUND LOOP
596 l_count := 0;
597 h_match_sql := ' SELECT count(*) FROM bsc_sys_kpi_colors '||
598 ' WHERE tab_id=:1 AND indicator=:2 AND kpi_measure_id=:3 '||
599 ' AND PERIOD_ID=:4 AND KPI_COLOR=:5 '||
600 ' AND ACTUAL_DATA=:6 '||
601 ' AND ('||h_key1||'=0 OR nvl(DIM_LEVEL1,0)='||h_key1||')'||
602 ' AND ('||h_key2||'=0 OR nvl(DIM_LEVEL2,0)='||h_key2||')'||
603 ' AND ('||h_key3||'=0 OR nvl(DIM_LEVEL3,0)='||h_key3||')'||
604 ' AND ('||h_key4||'=0 OR nvl(DIM_LEVEL4,0)='||h_key4||')'||
605 ' AND ('||h_key5||'=0 OR nvl(DIM_LEVEL5,0)='||h_key5||')'||
606 ' AND ('||h_key6||'=0 OR nvl(DIM_LEVEL6,0)='||h_key6||')'||
607 ' AND ('||h_key7||'=0 OR nvl(DIM_LEVEL7,0)='||h_key7||')'||
608 ' AND ('||h_key8||'=0 OR nvl(DIM_LEVEL8,0)='||h_key8||')';
609
610 OPEN h_match_cursor FOR h_match_sql USING p_tab_id, p_indic_code, p_kpi_measure_id, h_trend_period, h_trend_color, h_trend_vreal;
611 FETCH h_match_cursor INTO l_count;
612 CLOSE h_match_cursor;
613
614 IF ( (l_count > 0) AND (l_first_time) ) THEN
615 l_key_rec.dimvalues := h_key1||h_key2||h_key3||h_key4||h_key5||h_key6||h_key7||h_key8;
616 l_key_rec.period := h_trend_period;
617 l_key_rec.vreal := h_trend_vreal;
618 IF (h_trend_period = 1) THEN
619 l_key_rec.trend := 5;
620 l_key_rec.vprev := null;
621 ELSE
622 l_key_rec.trend := 5;
623 h_priorvalue_sql := 'SELECT VREAL, nvl(key1,0)||nvl(key2,0)||nvl(key3,0)||nvl(key4,0)||nvl(key5,0)||nvl(key6,0)||nvl(key7,0)||nvl(key8,0) DV_COMB '||
624 ' FROM BSC_TMP_COLORS '||
625 ' WHERE PERIOD=:1 '||
626 ' AND nvl(key1,0)||nvl(key2,0)||nvl(key3,0)||nvl(key4,0)||nvl(key5,0)||nvl(key6,0)||nvl(key7,0)||nvl(key8,0)=:2 '||
627 ' ORDER BY PERIOD, DV_COMB ';
628 h_prev_period := h_trend_period - 1;
629 OPEN h_prior_value_cursor FOR h_priorvalue_sql USING h_prev_period, h_trend_keys;
630 FETCH h_prior_value_cursor INTO l_prev_real, h_trend_keys;
631 WHILE h_prior_value_cursor%FOUND LOOP
632 IF (l_prev_real IS NULL) THEN
633 l_key_rec.trend := 5;
634 l_key_rec.vprev := null;
635 ELSE
636 l_key_rec.trend := get_trend_flag(h_color_method, h_trend_vreal, l_prev_real);
637 l_key_rec.vprev := l_prev_real;
638 END IF;
639 FETCH h_prior_value_cursor INTO l_prev_real, h_trend_keys;
640 END LOOP;
641 CLOSE h_prior_value_cursor;
642 END IF;
643 l_key_tbl(l_index) := l_key_rec;
644 l_index := l_index + 1;
645 l_first_time := false;
646
647 END IF;
648 FETCH h_actual_value_cursor INTO h_trend_vreal, h_trend_keys;
649 END LOOP;
650 CLOSE h_actual_value_cursor;
651 FETCH h_trend_cursor INTO h_key1,h_key2,h_key3,h_key4,h_key5,h_key6,h_key7,h_key8, h_trend_period, h_trend_color;
652
653 END LOOP;
654 CLOSE h_trend_cursor;
655
656 IF (l_key_tbl.COUNT > 0) THEN
657 FOR i in l_key_tbl.FIRST..l_key_tbl.LAST LOOP
658 l_key_rec := l_key_tbl(i);
659
660 UPDATE bsc_sys_kpi_colors
661 SET kpi_trend = l_key_rec.trend
662 WHERE tab_id = p_tab_id
663 AND indicator = p_indic_code
664 AND kpi_measure_id = p_kpi_measure_id
665 AND period_id = l_key_rec.period
666 AND nvl(dim_level1,0)||nvl(dim_level2,0)||nvl(dim_level3,0)||nvl(dim_level4,0)||nvl(dim_level5,0)||nvl(dim_level6,0)||nvl(dim_level7,0)||nvl(dim_level8,0) = l_key_rec.dimvalues;
667 END LOOP;
668 END IF;
669
670 --l_endtime := sysdate;
671
672 EXCEPTION
673 WHEN OTHERS THEN
674 ROLLBACK;
675 BSC_MESSAGE.Add(x_message => SQLERRM,
676 x_source => 'BSC_UPDATE_COLOR.update_trend_for_comparison');
677 END update_trend_for_comparison;
678
679 /*===========================================================================+
680 | FUNCTION Color_Indic_Dim_Combination
681 +============================================================================*/
682
683 FUNCTION Color_Indic_Dim_Combination(
684 x_indic_code IN NUMBER,
685 x_kpi_measure_id IN NUMBER,
686 x_calc_color_flag IN BOOLEAN,
687 x_indic_pl_flag IN BOOLEAN,
688 x_indic_initiatives_flag IN BOOLEAN,
689 x_indic_precalculated_flag IN BOOLEAN,
690 x_tab_id IN NUMBER,
691 x_dim_combination IN BSC_UPDATE_UTIL.t_array_of_number,
692 x_num_families IN NUMBER,
693 x_periodicity_id IN NUMBER,
694 x_comp_level_pk_col IN VARCHAR2,
695 x_dim_set_id IN NUMBER,
696 x_color_by_total IN NUMBER,
697 x_measure_formula IN VARCHAR2,
698 x_current_fy IN NUMBER,
699 x_aw_flag IN BOOLEAN -- AW_INTEGRATION: need this new parameter
700 )
701 RETURN BOOLEAN IS
702
703 e_unexpected_error EXCEPTION;
704 e_no_data_table_error EXCEPTION;
705
706 TYPE t_cursor IS REF CURSOR;
707 h_cursor t_cursor;
708 h_cursor1 t_cursor;
709
710 h_i NUMBER;
711
712 h_sql VARCHAR2(32700);
713 h_where VARCHAR2(32700);
714
715 h_dim_level_index NUMBER;
716 h_family_index NUMBER;
717 h_level_pk_col VARCHAR2(30);
718 h_level_table_name VARCHAR2(30);
719
720 h_dim_com_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
721
722 -- same info than h_dim_com_keys but the array is from 1 to x_num_families
723 h_dim_com_keys_1 BSC_UPDATE_UTIL.t_array_of_varchar2;
724
725 h_table_name VARCHAR2(30);
726
727 h_condition VARCHAR2(2000);
728 h_condition_b VARCHAR2(2000);
729
730 h_arr_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
731 h_key_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
732 h_num_keys NUMBER;
733 h_lst_keys VARCHAR2(2000);
734 h_lst_keys_temp VARCHAR2(2000);
735 h_key_index NUMBER;
736
737 h_lst_select VARCHAR2(2000);
738 h_lst_where VARCHAR2(2000);
739
740 h_lst_keys_insert VARCHAR2(2000);
741
742 h_i_family NUMBER;
743
744 h_i_dimension NUMBER;
745
746 h_dim_level_index_child NUMBER;
747 h_level_pk_col_child VARCHAR2(30);
748 h_level_table_name_child VARCHAR2(30);
749
750 h_dim_level_index_parent NUMBER;
751 h_level_pk_col_parent VARCHAR2(30);
752 h_level_table_name_parent VARCHAR2(30);
753
754 h_yearly_flag NUMBER;
755
756 h_sql_mcc VARCHAR2(32000);
757
758 -- BSC-BIS-DIMENSIONS: Need to use varchar2 to suppoer NUMBER/VARCHAR2
759 h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_varchar2;
760 h_num_bind_vars NUMBER;
761
762 h_num_bind_vars_1 NUMBER;
763 h_level_comb VARCHAR2(30);
764
765 h_bind_vars_values_n BSC_UPDATE_UTIL.t_array_of_number;
766 h_num_bind_vars_n NUMBER;
767
768 CURSOR c_table_columns (p_table_name VARCHAR2, p_column_type VARCHAR2) IS
769 SELECT column_name
770 FROM bsc_db_tables_cols
771 WHERE table_name = p_table_name AND column_type = p_column_type;
772
773 h_column_type VARCHAR2(1);
774 h_column_name VARCHAR2(30);
775 h_lst_tab_columns VARCHAR2(32700);
776 h_fact_table VARCHAR2(32700);
777 h_mv_name VARCHAR2(30);
778 h_data_source VARCHAR2(10);
779 h_sql_stmt VARCHAR2(32700);
780
781 --AW_INTEGRATION: new variables
782 h_aw_limit_tbl BIS_PMV_PAGE_PARAMETER_TBL;
783 h_aw_limit_rec BIS_PMV_PAGE_PARAMETER_REC;
784 h_calendar_id NUMBER;
785 h_min_per NUMBER;
786 h_max_per NUMBER;
787 h_per_parameter_value VARCHAR2(100);
788 l_ytd_flag NUMBER;
789
790
791 BEGIN
792
793 h_sql := NULL;
794 h_where := NULL;
795 h_table_name := NULL;
796 h_condition := NULL;
797 h_condition_b := NULL;
798 h_num_keys := 0;
799 h_lst_keys := NULL;
800 h_lst_keys_temp := NULL;
801 h_lst_select := NULL;
802 h_lst_where := NULL;
803 h_lst_keys_insert := NULL;
804
805 -- AW_INTEGRATION: init h_aw_limit_tbl
806 h_aw_limit_tbl := BIS_PMV_PAGE_PARAMETER_TBL();
807 h_aw_limit_tbl.delete;
808
809 -- AW_INTEGRATION: move this line here
810 h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity_id);
811 h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_periodicity_id);
812
813 -- Initialize the array h_dim_com_keys: key column names of dimension combinations
814 -- Example: BRACH_CODE|PROD_CODE
815
816 IF x_num_families > 0 THEN
817 FOR h_i IN 0 .. x_num_families - 1 LOOP
818 IF h_where IS NULL THEN
819 h_where := '(FAMILY_INDEX = '||h_i||' AND DIM_INDEX = '||x_dim_combination(h_i)||')';
820 ELSE
821 h_where := h_where||' OR'||
822 '(FAMILY_INDEX = '||h_i||' AND DIM_INDEX = '||x_dim_combination(h_i)||')';
823 END IF;
824 END LOOP;
825
826 h_sql := 'SELECT FAMILY_INDEX, LEVEL_PK_COL'||
827 ' FROM BSC_TMP_TAB_DEF'||
828 ' WHERE TAB_ID = :1'||
829 ' AND ('||h_where||')'||
830 ' ORDER BY FAMILY_INDEX';
831 OPEN h_cursor FOR h_sql USING x_tab_id;
832 FETCH h_cursor INTO h_family_index, h_level_pk_col;
833 WHILE h_cursor%FOUND LOOP
834 h_dim_com_keys(h_family_index) := h_level_pk_col;
835
836 FETCH h_cursor INTO h_family_index, h_level_pk_col;
837 END LOOP;
838 CLOSE h_cursor;
839 END IF;
840
841 -- Get the table used by the indicator in this drill combination
842
843 -- NOTE about special indicators
844 -- PL Indicators ALWAYS have x_comp_level_pk_col = ACCOUNT_CODE and x_color_by_total = 0.
845 -- So the table is going to have ACCOUNT_CODE.
846 -- Initiatives Strategic Indicators ALWAYS have x_comp_level_pk_col = PROJECT_CODE
847 -- and x_color_by_total = 0. So the table is going to have PROJECT_CODE.
848 h_table_name := Get_Table_Used_To_Color(x_indic_code,
849 x_periodicity_id,
850 x_dim_set_id,
851 x_comp_level_pk_col,
852 x_color_by_total,
853 h_dim_com_keys,
854 x_num_families,
855 h_level_comb);
856 IF h_table_name IS NULL THEN
857 -- SUPPORT_BSC_BIS_MEASURES: if there is no data table we simply do not calculate the color
858 RETURN TRUE;
859 END IF;
860
861 -- I don't need the current period of the indicator because i'm going to color
862 -- all the current year
863
864 -- Get the condition on the table to get the records for this drill combination
865
866 --AW_INTEGRATION: pass x_aw_flag and h_aw_limit
867 IF NOT Get_Condition_On_Color_Table(x_indic_code,
868 x_aw_flag,
869 x_indic_pl_flag,
870 x_indic_precalculated_flag,
871 x_dim_set_id,
872 h_table_name,
873 x_dim_combination,
874 h_dim_com_keys,
875 x_num_families,
876 x_comp_level_pk_col,
877 x_color_by_total,
878 h_condition,
879 h_bind_vars_values,
880 h_num_bind_vars,
881 h_aw_limit_tbl
882 ) THEN
883 RAISE e_unexpected_error;
884 END IF;
885
886 IF h_condition IS NOT NULL THEN
887 h_condition := h_condition||' AND ';
888 END IF;
889
890 --BSC-MV Note: In this architecture the data is in the MV.
891 --I will color only the periods existing in the MV
892 --If the summary table is used to store projections, I wont color
893 --projected periods. Currently, BSC does not use those colors.
894 IF BSC_APPS.bsc_mv THEN
895 h_condition := h_condition||'PERIODICITY_ID = :'||(h_num_bind_vars + 1);
896 h_num_bind_vars := h_num_bind_vars + 1;
897 h_bind_vars_values(h_num_bind_vars) := x_periodicity_id;
898
899 -- AW_INTEGRATION: Limit type, periodicity_id and measures
900 IF x_aw_flag THEN
901 -- limit TYPE with 0 and 1
902 -- Fix bug#4574713: package become invalid after bis team added a new property in this record
903 -- actually we do not need this call
904 h_aw_limit_rec := BIS_PMV_PAGE_PARAMETER_REC(null,null,null,null,null,null);
905 h_aw_limit_rec.parameter_name := 'TYPE';
906 h_aw_limit_rec.parameter_value := '0';
907 h_aw_limit_rec.dimension := 'DIMENSION';
908 h_aw_limit_tbl.extend;
909 h_aw_limit_tbl(h_aw_limit_tbl.LAST) := h_aw_limit_rec;
910
911 h_aw_limit_rec := BIS_PMV_PAGE_PARAMETER_REC(null,null,null,null,null,null);
912 h_aw_limit_rec.parameter_name := 'TYPE';
913 h_aw_limit_rec.parameter_value := '1';
914 h_aw_limit_rec.dimension := 'DIMENSION';
915 h_aw_limit_tbl.extend;
916 h_aw_limit_tbl(h_aw_limit_tbl.LAST) := h_aw_limit_rec;
917
918 --limit periodicity
919 IF h_yearly_flag = 1 THEN
920 -- Annual periodicity
921 select min(year), max(year)
922 into h_min_per, h_max_per
923 from bsc_db_calendar
924 where calendar_id = h_calendar_id;
925
926 h_per_parameter_value := h_min_per||'.'||h_min_per||' TO '||h_max_per||'.'||h_max_per;
927 ELSE
928 -- Other periodicity
929 h_sql := 'select min('||BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity_id)||'),'||
930 ' max('||BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity_id)||')'||
931 ' from bsc_db_calendar'||
932 ' where calendar_id = :1 and year = :2';
933 OPEN h_cursor FOR h_sql USING h_calendar_id, x_current_fy;
934 FETCH h_cursor INTO h_min_per, h_max_per;
935 CLOSE h_cursor;
936
937 h_per_parameter_value := h_min_per||'.'||x_current_fy||' TO '||
938 h_max_per||'.'||x_current_fy;
939 END IF;
940 h_aw_limit_rec := BIS_PMV_PAGE_PARAMETER_REC(null,null,null,null,null,null);
941 h_aw_limit_rec.parameter_name := x_periodicity_id;
942 h_aw_limit_rec.parameter_value := h_per_parameter_value;
943 h_aw_limit_rec.dimension := 'PERIODICITY';
944 h_aw_limit_tbl.extend;
945 h_aw_limit_tbl(h_aw_limit_tbl.LAST) := h_aw_limit_rec;
946
947 -- limit the measures
948 h_column_type := 'A';
949 OPEN c_table_columns(h_table_name, h_column_type);
950 LOOP
951 FETCH c_table_columns INTO h_column_name;
952 EXIT WHEN c_table_columns%NOTFOUND;
953 h_aw_limit_rec := BIS_PMV_PAGE_PARAMETER_REC(null,null,null,null,null,null);
954 h_aw_limit_rec.parameter_name := h_column_name;
955 h_aw_limit_rec.dimension := 'MEASURE';
956 h_aw_limit_tbl.extend;
957 h_aw_limit_tbl(h_aw_limit_tbl.LAST) := h_aw_limit_rec;
958 END LOOP;
959 CLOSE c_table_columns;
960
961 FOR h_i IN 1..h_aw_limit_tbl.LAST LOOP
962 h_aw_limit_rec := h_aw_limit_tbl(h_i);
963 BSC_UPDATE_LOG.Write_Line_log(h_aw_limit_rec.parameter_name||' '||
964 h_aw_limit_rec.parameter_value||' '||
965 h_aw_limit_rec.dimension, BSC_UPDATE_LOG.OUTPUT);
966 END LOOP;
967 -- Now we are ready to limit the dimensions of the indicator
968 bsc_aw_read.limit_dimensions(x_indic_code, x_dim_set_id, h_aw_limit_tbl);
969 END IF;
970
971 -- BSC-BIS-DIMENSIONS: Bind variables are varchar2
972 h_condition_b := h_condition;
973 FOR h_i IN REVERSE 1..h_num_bind_vars LOOP
974 h_condition_b := REPLACE(h_condition_b, ':'||h_i, ''''||h_bind_vars_values(h_i)||'''');
975 END LOOP;
976
977 -- BSC_MV Note: If there is no list button I can use only the data source
978 -- specified in h_level_comb.
979 -- If there is list button, I need to do UNION ALL to have all the zero codes
980 -- and detailed info in the same source. List button need to color detailed
981 -- info as well as total info
982 IF x_num_families > 0 THEN
983 -- There is list button
984
985 -- Get list of columns of the table
986 h_column_type := 'P';
987 h_lst_tab_columns := NULL;
988 OPEN c_table_columns(h_table_name, h_column_type);
989 LOOP
990 FETCH c_table_columns INTO h_column_name;
991 EXIT WHEN c_table_columns%NOTFOUND;
992 -- Fix bug#3779410: Since End To End Kpi (BSC-BIS-DIMENSIONS), the key columns in the zero mv
993 -- are always varchar2 and may differ from the mv. So we are going to use
994 -- to_char in the key columns because the UNION ALL fails if the
995 -- data type of the key columns is not the same.
996 h_lst_tab_columns := h_lst_tab_columns||'TO_CHAR('||h_column_name||') '||h_column_name||', ';
997 END LOOP;
998 CLOSE c_table_columns;
999 h_lst_tab_columns := h_lst_tab_columns||'PERIODICITY_ID, YEAR, TYPE, PERIOD';
1000
1001 h_column_type := 'A';
1002 OPEN c_table_columns(h_table_name, h_column_type);
1003 LOOP
1004 FETCH c_table_columns INTO h_column_name;
1005 EXIT WHEN c_table_columns%NOTFOUND;
1006 h_lst_tab_columns := h_lst_tab_columns||', '||h_column_name;
1007 END LOOP;
1008 CLOSE c_table_columns;
1009
1010 h_fact_table := NULL;
1011 h_sql := 'SELECT DISTINCT mv_name, data_source, sql_stmt'||
1012 ' FROM bsc_kpi_data_tables'||
1013 ' WHERE indicator = :1 AND periodicity_id = :2'||
1014 ' AND dim_set_id = :3 AND table_name = :4';
1015
1016 OPEN h_cursor FOR h_sql USING x_indic_code, x_periodicity_id, x_dim_set_id, h_table_name;
1017 LOOP
1018 FETCH h_cursor INTO h_mv_name, h_data_source, h_sql_stmt;
1019 EXIT WHEN h_cursor%NOTFOUND;
1020
1021 -- Not all the zero codes queries are needed. There are few of them that
1022 -- we really need acccording to the condition
1023 -- Check if the sql or MV has rows for that condition. In that case we
1024 -- involve it in the union.
1025
1026 IF h_data_source = 'MV' THEN
1027 h_sql := 'SELECT 1 FROM '||h_mv_name||
1028 ' WHERE '||h_condition_b||' AND ROWNUM = 1';
1029 ELSE
1030 h_sql := 'SELECT 1 FROM ('||h_sql_stmt||') F'||
1031 ' WHERE '||h_condition_b||' AND ROWNUM = 1';
1032 END IF;
1033 h_i := NULL;
1034 OPEN h_cursor1 FOR h_sql;
1035 FETCH h_cursor1 INTO h_i;
1036 CLOSE h_cursor1;
1037
1038 IF NOT (h_i IS NULL) THEN
1039 -- The sql or mv has records for the condition, then add it to the union
1040 IF NOT (h_fact_table IS NULL) THEN
1041 h_fact_table := h_fact_table||' UNION ALL ';
1042 END IF;
1043
1044 IF h_data_source = 'MV' THEN
1045 h_fact_table := h_fact_table||
1046 ' SELECT '||h_lst_tab_columns||
1047 ' FROM '||h_mv_name;
1048 ELSE
1049 -- data source is 'SQL'
1050 h_fact_table := h_fact_table||
1051 ' SELECT '||h_lst_tab_columns||
1052 ' FROM ('||h_sql_stmt||')';
1053 END IF;
1054 END IF;
1055 END LOOP;
1056 CLOSE h_cursor;
1057
1058 IF h_fact_table IS NULL THEN
1059 -- There is no data to color, no reason to continue.. and also the query will be invalid
1060 RETURN TRUE;
1061 END IF;
1062
1063 h_fact_table := '('||h_fact_table||')';
1064
1065 ELSE
1066 -- There is no list button (Common case)
1067 h_sql := 'SELECT mv_name, data_source, sql_stmt'||
1068 ' FROM bsc_kpi_data_tables'||
1069 ' WHERE indicator = :1 AND periodicity_id = :2'||
1070 ' AND dim_set_id = :3 AND level_comb = :4';
1071 OPEN h_cursor FOR h_sql USING x_indic_code, x_periodicity_id, x_dim_set_id, h_level_comb;
1072 FETCH h_cursor INTO h_mv_name, h_data_source, h_sql_stmt;
1073 CLOSE h_cursor;
1074 IF h_data_source = 'MV' THEN
1075 h_fact_table := h_mv_name;
1076 ELSE
1077 h_fact_table := '('||h_sql_stmt||')';
1078 END IF;
1079 END IF;
1080
1081 h_condition := h_condition||' AND ';
1082 END IF;
1083
1084 -- Make the array and list of keys of the combination and comparison key
1085 FOR h_i IN 0 .. x_num_families - 1 LOOP
1086 h_num_keys := h_num_keys + 1;
1087 h_arr_keys(h_num_keys) := h_dim_com_keys(h_i);
1088 h_key_columns_temp(h_num_keys) := 'KEY'||h_num_keys;
1089 h_dim_com_keys_1(h_i + 1) := h_dim_com_keys(h_i);
1090 END LOOP;
1091
1092 IF (x_comp_level_pk_col IS NOT NULL) AND (x_color_by_total = 0) AND (NOT x_indic_pl_flag) THEN
1093 -- If the indicator enters in comparison we need the data by that drill also.
1094 -- Note: I exclude the PL indicator because this indicator has x_comp_level_pk_col = 'ACCOUNT_CODE'
1095 -- and x_color_by_total = 0, but the color of a PL indicator is based on the profit account.
1096 -- Note: If the indicator is Initiatives Strategic then x_comp_level_pk_col = 'PROJECT_CODE'
1097 -- and x_color_by_total = 0. So we include the project key to get data by this dimension also.
1098
1099 h_num_keys := h_num_keys + 1;
1100 h_arr_keys(h_num_keys) := x_comp_level_pk_col;
1101 h_key_columns_temp(h_num_keys) := 'KEY'||h_num_keys;
1102 END IF;
1103
1104 h_lst_keys := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(h_arr_keys, h_num_keys);
1105 h_lst_keys_temp := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', h_num_keys);
1106 IF h_lst_keys IS NOT NULL THEN
1107 h_lst_keys := h_lst_keys||', ';
1108 h_lst_keys_temp := h_lst_keys_temp||', ';
1109 END IF;
1110
1111 -- Insert into temporal table BSC_TMP_DATA_COLOR the base data to calculate the color
1112 -- Clean current records
1113 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_DATA_COLOR');
1114
1115 -- I use h_num_bind_vars_1 because h_num_bind_vars is used in the next query
1116 h_num_bind_vars_1 := h_num_bind_vars;
1117
1118 IF h_yearly_flag = 1 THEN
1119
1120 h_sql := 'INSERT /*+ append */ INTO BSC_TMP_DATA_COLOR ('||h_lst_keys_temp||'PERIOD, TYPE, TOTAL)'||
1121 ' SELECT '||h_lst_keys||'YEAR AS PERIOD, TYPE, '||x_measure_formula||' AS TOTAL';
1122
1123 IF BSC_APPS.bsc_mv THEN
1124 h_sql := h_sql||
1125 ' FROM '||h_fact_table||' F';
1126 ELSE
1127 h_sql := h_sql||
1128 ' FROM '||h_table_name;
1129 END IF;
1130 h_sql := h_sql||
1131 ' WHERE '||h_condition||'(TYPE = 0 OR TYPE = 1)'||
1132 ' GROUP BY '||h_lst_keys||'YEAR, TYPE';
1133 ELSE
1134
1135 h_sql := 'INSERT /*+ append */ INTO BSC_TMP_DATA_COLOR ('||h_lst_keys_temp||'PERIOD, TYPE, TOTAL)'||
1136 ' SELECT '||h_lst_keys||'PERIOD, TYPE, '||x_measure_formula||' AS TOTAL';
1137
1138 IF BSC_APPS.bsc_mv THEN
1139 h_sql := h_sql||
1140 ' FROM '||h_fact_table||' F';
1141 ELSE
1142 h_sql := h_sql||
1143 ' FROM '||h_table_name;
1144 END IF;
1145 h_sql := h_sql||
1146 ' WHERE '||h_condition||'YEAR = :'||(h_num_bind_vars_1 + 1)||
1147 ' AND (TYPE = 0 OR TYPE = 1)'||
1148 ' GROUP BY '||h_lst_keys||'PERIOD, TYPE';
1149 h_num_bind_vars_1 := h_num_bind_vars_1 + 1;
1150 h_bind_vars_values(h_num_bind_vars_1) := x_current_fy;
1151
1152 END IF;
1153
1154 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars_1);
1155 COMMIT;
1156
1157 -- Get the YTD Flag
1158 l_ytd_flag := BSC_UPDATE_UTIL.get_ytd_flag(x_indic_code, x_kpi_measure_id);
1159
1160 -- Calculate YTD Data if the indicator enter in YTD calculation
1161 IF l_ytd_flag = 1 THEN
1162 -- Update BSC_TMP_DATA_COLOR with the YTD data
1163 -- Drop table if exits
1164
1165 h_lst_where := BSC_UPDATE_UTIL.Make_Lst_Cond_Join('B', h_arr_keys, 'A', h_key_columns_temp, h_num_keys, 'AND');
1166
1167 IF h_num_keys > 0 THEN
1168 h_lst_where := h_lst_where||' AND ';
1169 END IF;
1170
1171 h_num_bind_vars_1 := h_num_bind_vars;
1172
1173 h_sql := 'UPDATE BSC_TMP_DATA_COLOR A'||
1174 ' SET TOTAL = ('||
1175 ' SELECT '||x_measure_formula;
1176 IF BSC_APPS.bsc_mv THEN
1177 h_sql := h_sql||
1178 ' FROM '||h_fact_table||' B';
1179 ELSE
1180 h_sql := h_sql||
1181 ' FROM '||h_table_name||' B';
1182 END IF;
1183 h_sql := h_sql||
1184 ' WHERE '||h_lst_where||h_condition||'B.YEAR=:'||(h_num_bind_vars_1 + 1)||
1185 ' AND B.TYPE=A.TYPE AND B.PERIOD<=A.PERIOD'||
1186 ' )';
1187 h_num_bind_vars_1 := h_num_bind_vars_1 + 1;
1188 h_bind_vars_values(h_num_bind_vars_1) := x_current_fy;
1189 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars_1);
1190 commit;
1191 END IF;
1192
1193 -- Initialize temporal table BSC_TMP_COLORS
1194 -- Clean current records
1195 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_COLORS');
1196
1197 h_sql := 'INSERT /*+ append */ INTO BSC_TMP_COLORS ('||h_lst_keys_temp||'PERIOD, VPLAN, VREAL, CUMPERCENT, COLOR)'||
1198 ' SELECT '||h_lst_keys_temp||'BSC_TMP_ALL_PERIODS.PERIOD, '||
1199 ' NULL AS VPLAN, NULL AS VREAL, NULL AS CUMPERCENT, '||GRAY||' AS COLOR'||
1200 ' FROM BSC_TMP_DATA_COLOR, BSC_TMP_ALL_PERIODS'||
1201 ' GROUP BY '||h_lst_keys_temp||'BSC_TMP_ALL_PERIODS.PERIOD';
1202
1203 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1204
1205 commit;
1206
1207 -- Get plan data
1208 h_lst_where := BSC_UPDATE_UTIL.Make_Lst_Cond_Join('BSC_TMP_COLORS', h_key_columns_temp, 'BSC_TMP_DATA_COLOR', h_key_columns_temp, h_num_keys, 'AND');
1209
1210 IF h_num_keys > 0 THEN
1211 h_lst_where := h_lst_where||' AND ';
1212 END IF;
1213
1214 h_sql := 'UPDATE BSC_TMP_COLORS'||
1215 ' SET VPLAN = ('||
1216 ' SELECT TOTAL'||
1217 ' FROM BSC_TMP_DATA_COLOR'||
1218 ' WHERE '||h_lst_where||' BSC_TMP_COLORS.PERIOD = BSC_TMP_DATA_COLOR.PERIOD'||
1219 ' AND BSC_TMP_DATA_COLOR.TYPE = 1'||
1220 ')'||
1221 ' WHERE 0 = ('||
1222 ' SELECT 0'||
1223 ' FROM BSC_TMP_DATA_COLOR'||
1224 ' WHERE '||h_lst_where||' BSC_TMP_COLORS.PERIOD = BSC_TMP_DATA_COLOR.PERIOD'||
1225 ' AND BSC_TMP_DATA_COLOR.TYPE = 1'||
1226 ')';
1227 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1228 COMMIT;
1229
1230 -- Get real data
1231 h_sql := 'UPDATE BSC_TMP_COLORS'||
1232 ' SET VREAL = ('||
1233 ' SELECT TOTAL'||
1234 ' FROM BSC_TMP_DATA_COLOR'||
1235 ' WHERE '||h_lst_where||' BSC_TMP_COLORS.PERIOD = BSC_TMP_DATA_COLOR.PERIOD'||
1236 ' AND BSC_TMP_DATA_COLOR.TYPE = 0'||
1237 ')'||
1238 ' WHERE 0 = ('||
1239 ' SELECT 0'||
1240 ' FROM BSC_TMP_DATA_COLOR'||
1241 ' WHERE '||h_lst_where||' BSC_TMP_COLORS.PERIOD = BSC_TMP_DATA_COLOR.PERIOD'||
1242 ' AND BSC_TMP_DATA_COLOR.TYPE = 0'||
1243 ')';
1244 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1245 COMMIT;
1246
1247 -- Calculate the color and store in TEMP table BSC_TMP_COLORS
1248 IF NOT Calculate_KPI_Color
1249 ( p_objective_id => x_indic_code
1250 , p_kpi_measure_id => x_kpi_measure_id
1251 , p_calc_color_flag => x_calc_color_flag
1252 ) THEN
1253 RAISE e_unexpected_error;
1254 END IF;
1255
1256 -- Make the list of keys to insert into BSC_SYS_KPI_COLORS (h_lst_keys_insert)
1257 -- Example: '0, BRANCH_CODE, 0, 0, 0, 0, 0, 0' (8 dim levels in the table)
1258 h_lst_keys_insert := NULL;
1259 h_i := 0;
1260 h_key_index := 1;
1261
1262 h_sql := 'SELECT DIM_LEVEL_INDEX, LEVEL_PK_COL'||
1263 ' FROM BSC_TMP_TAB_DEF'||
1264 ' WHERE TAB_ID = :1'||
1265 ' ORDER BY DIM_LEVEL_INDEX';
1266
1267 OPEN h_cursor FOR h_sql USING x_tab_id;
1268 FETCH h_cursor INTO h_dim_level_index, h_level_pk_col;
1269 WHILE h_cursor%FOUND LOOP
1270 IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_level_pk_col, h_arr_keys, h_num_keys) THEN
1271 h_lst_keys_insert := h_lst_keys_insert||'KEY'||h_key_index||', ';
1272 h_key_index := h_key_index + 1;
1273 ELSE
1274 h_lst_keys_insert := h_lst_keys_insert||'0, ';
1275 END IF;
1276
1277 h_i := h_i + 1;
1278
1279 FETCH h_cursor INTO h_dim_level_index, h_level_pk_col;
1280 END LOOP;
1281 CLOSE h_cursor;
1282
1283 IF x_indic_initiatives_flag THEN
1284 -- The indicator is a Initiatives indicator. The project dimension is added to the end of the
1285 -- list dimension.
1286 h_lst_keys_insert := h_lst_keys_insert||'KEY'||h_key_index||', ';
1287 h_key_index := h_key_index + 1;
1288 h_i := h_i + 1;
1289 END IF;
1290
1291 WHILE h_i < 8 LOOP
1292 h_lst_keys_insert := h_lst_keys_insert||'0, ';
1293 h_i := h_i + 1;
1294 END LOOP;
1295
1296
1297 -- If the indicator enter in comparison we need to calculate the minimum common color
1298 -- Otherwise we are ready to insert the colors directly in BSC_SYS_KPI_COLORS
1299 IF (x_comp_level_pk_col IS NOT NULL) AND (x_color_by_total = 0) AND
1300 (NOT x_indic_pl_flag) AND (NOT x_indic_initiatives_flag) THEN
1301 -- The indicator enter in comparison.
1302 -- Note: I exclude the PL indicator because this indicator has x_comp_level_pk_col = 'ACCOUNT_CODE'
1303 -- and x_color_by_total = 0, but the color of a PL indicator is based on the profit account.
1304 -- Note: I exclude the Initiatives indicator because this indicator has x_comp_level_pk_col = 'PROJECT_CODE'
1305 -- and x_color_by_total = 0 but we dont calculte the minimum color. Instead we put the color of each project
1306 -- directly in BSC_SYS_KPI_COLORS in the last dimension level.
1307
1308 h_lst_select := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', x_num_families);
1309 IF h_lst_select IS NOT NULL THEN
1310 h_lst_select := h_lst_select||', ';
1311 END IF;
1312
1313 h_sql_mcc := 'SELECT '||h_lst_select||'PERIOD, '||
1314 ' MAX(COL_B.PERF_SEQUENCE) MCC'||
1315 ' FROM BSC_TMP_COLORS TEM_C, BSC_SYS_COLORS_B COL_B'||
1316 ' WHERE TEM_C.COLOR = COL_B.COLOR_ID'||
1317 ' GROUP BY '||h_lst_select||'PERIOD';
1318
1319 -- We are ready to insert the colors directly in BSC_SYS_KPI_COLORS
1320 h_sql := 'INSERT INTO BSC_SYS_KPI_COLORS (TAB_ID, INDICATOR, KPI_MEASURE_ID, DIM_LEVEL1, DIM_LEVEL2,'||
1321 ' DIM_LEVEL3, DIM_LEVEL4, DIM_LEVEL5, DIM_LEVEL6, DIM_LEVEL7, DIM_LEVEL8,'||
1322 ' PERIOD_ID, KPI_COLOR, USER_COLOR)'||
1323 ' SELECT :1, :2, :3, '||h_lst_keys_insert||'PERIOD,'|| ' COL_B.COLOR_ID, COL_B.COLOR_ID ' ||
1324 ' FROM ('||h_sql_mcc||') BSC_TMP_COLORS_MCC, BSC_SYS_COLORS_B COL_B'||
1325 ' WHERE BSC_TMP_COLORS_MCC.MCC = COL_B.PERF_SEQUENCE';
1326
1327 h_bind_vars_values_n.delete;
1328 h_bind_vars_values_n(1) := x_tab_id;
1329 h_bind_vars_values_n(2) := x_indic_code;
1330 h_bind_vars_values_n(3) := x_kpi_measure_id;
1331 h_num_bind_vars_n := 3;
1332
1333 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values_n, h_num_bind_vars_n);
1334
1335 COMMIT;
1336
1337 IF NOT update_actual_budget_for_mcc( p_objective_id => x_indic_code
1338 , p_kpi_measure_id => x_kpi_measure_id
1339 , p_tab_id => x_tab_id
1340 , p_lst_keys_insert => h_lst_keys_insert
1341 ) THEN
1342 RAISE e_unexpected_error;
1343 END IF;
1344
1345 COMMIT;
1346
1347 update_trend_for_comparison( p_indic_code => x_indic_code
1348 , p_kpi_measure_id => x_kpi_measure_id
1349 , p_tab_id => x_tab_id
1350 , p_lst_keys_insert => h_lst_keys_insert
1351 , p_sql_mcc => h_sql_mcc);
1352
1353 COMMIT;
1354 ELSE
1355
1356 -- The indicator doesnt enter in comparison. We are ready to insert the colors directly in BSC_SYS_KPI_COLORS
1357 h_sql := 'INSERT /*+ append */ INTO BSC_SYS_KPI_COLORS (TAB_ID, INDICATOR, KPI_MEASURE_ID, DIM_LEVEL1, DIM_LEVEL2,'||
1358 ' DIM_LEVEL3, DIM_LEVEL4, DIM_LEVEL5, DIM_LEVEL6, DIM_LEVEL7, DIM_LEVEL8,'||
1359 ' PERIOD_ID, KPI_COLOR, USER_COLOR, ACTUAL_DATA, BUDGET_DATA)'||
1360 ' SELECT :1, :2, :3, '||h_lst_keys_insert||'PERIOD,'||
1361 ' COLOR, COLOR, VREAL, VPLAN'||
1362 ' FROM BSC_TMP_COLORS';
1363 h_bind_vars_values_n.delete;
1364 h_bind_vars_values_n(1) := x_tab_id;
1365 h_bind_vars_values_n(2) := x_indic_code;
1366 h_bind_vars_values_n(3) := x_kpi_measure_id;
1367 h_num_bind_vars_n := 3;
1368
1369 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values_n, h_num_bind_vars_n);
1370
1371 COMMIT;
1372 END IF;
1373
1374 --
1375 -- Complete the values for parent dimensions
1376 --
1377 FOR h_i_family IN 0 .. x_num_families - 1 LOOP
1378
1379 IF x_dim_combination(h_i_family) > 0 THEN
1380 -- Get the following information of the child dimension in the family:
1381 -- dim_level_index
1382 -- level_pk_col
1383 -- level_table_name
1384
1385 h_sql := 'SELECT DIM_LEVEL_INDEX, LEVEL_PK_COL, LEVEL_TABLE_NAME'||
1386 ' FROM BSC_TMP_TAB_DEF'||
1387 ' WHERE TAB_ID = :1'||
1388 ' AND FAMILY_INDEX = :2'||
1389 ' AND DIM_INDEX = :3';
1390
1391 OPEN h_cursor FOR h_sql USING x_tab_id, h_i_family, x_dim_combination(h_i_family);
1392 FETCH h_cursor INTO h_dim_level_index_child, h_level_pk_col_child, h_level_table_name_child;
1393 IF h_cursor%NOTFOUND THEN
1394 RAISE e_unexpected_error;
1395 END IF;
1396 CLOSE h_cursor;
1397
1398 FOR h_i_dimension IN REVERSE 0 .. x_dim_combination(h_i_family) - 1 LOOP
1399 -- Get the following information of the parent dimension in the family:
1400 -- dim_level_index
1401 -- level_pk_col
1402 -- level_table_name
1403
1404 h_sql := 'SELECT DIM_LEVEL_INDEX, LEVEL_PK_COL, LEVEL_TABLE_NAME'||
1405 ' FROM BSC_TMP_TAB_DEF'||
1406 ' WHERE TAB_ID = :1'||
1407 ' AND FAMILY_INDEX = :2'||
1408 ' AND DIM_INDEX = :3';
1409
1410 OPEN h_cursor FOR h_sql USING x_tab_id, h_i_family, h_i_dimension;
1411 FETCH h_cursor INTO h_dim_level_index_parent, h_level_pk_col_parent, h_level_table_name_parent;
1412 IF h_cursor%NOTFOUND THEN
1413 RAISE e_unexpected_error;
1414 END IF;
1415 CLOSE h_cursor;
1416
1417 h_sql := 'UPDATE BSC_SYS_KPI_COLORS'||
1418 ' SET DIM_LEVEL'||(h_dim_level_index_parent + 1)||' = ('||
1419 ' SELECT '||h_level_pk_col_parent||
1420 ' FROM '||h_level_table_name_child||
1421 ' WHERE BSC_SYS_KPI_COLORS.DIM_LEVEL'||(h_dim_level_index_child + 1)||
1422 ' = '||h_level_table_name_child||'.CODE'||
1423 ')'||
1424 ' WHERE TAB_ID = :1 AND '||
1425 ' INDICATOR = :2 AND '||
1426 ' DIM_LEVEL'||(h_dim_level_index_child + 1)||' <> ''0''';
1427 h_bind_vars_values_n.delete;
1428 h_bind_vars_values_n(1) := x_tab_id;
1429 h_bind_vars_values_n(2) := x_indic_code;
1430 h_num_bind_vars_n := 2;
1431 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values_n, h_num_bind_vars_n);
1432 commit;
1433
1434 h_dim_level_index_child := h_dim_level_index_parent;
1435 h_level_pk_col_child := h_level_pk_col_parent;
1436 h_level_table_name_child := h_level_table_name_parent;
1437
1438 END LOOP;
1439 END IF;
1440 END LOOP;
1441
1442 RETURN TRUE;
1443
1444 EXCEPTION
1445 WHEN e_no_data_table_error THEN
1446 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_DIMDATA_NOT_FOUND'),
1447 x_source => 'BSC_UPDATE_COLOR.Color_Indic_Dim_Combination');
1448 RETURN FALSE;
1449
1450 WHEN e_unexpected_error THEN
1451 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_KPICOLOR_FAILED'),
1452 x_source => 'BSC_UPDATE_COLOR.Color_Indic_Dim_Combination');
1453 RETURN FALSE;
1454
1455 WHEN OTHERS THEN
1456 BSC_MESSAGE.Add(x_message => 'x_indic_code='||x_indic_code,
1457 x_source => 'BSC_UPDATE_COLOR.Color_Indic_Dim_Combination');
1458 BSC_MESSAGE.Add(x_message => 'h_table_name='||h_table_name,
1459 x_source => 'BSC_UPDATE_COLOR.Color_Indic_Dim_Combination');
1460 BSC_MESSAGE.Add(x_message => 'h_level_comb='||h_level_comb,
1461 x_source => 'BSC_UPDATE_COLOR.Color_Indic_Dim_Combination');
1462 BSC_MESSAGE.Add(x_message => 'h_condition='||h_condition,
1463 x_source => 'BSC_UPDATE_COLOR.Color_Indic_Dim_Combination');
1464 BSC_MESSAGE.Add(x_message => 'h_sql='||SUBSTR(h_sql, 1, 200),
1465 x_source => 'BSC_UPDATE_COLOR.Color_Indic_Dim_Combination');
1466 BSC_MESSAGE.Add(x_message => SQLERRM,
1467 x_source => 'BSC_UPDATE_COLOR.Color_Indic_Dim_Combination');
1468 RETURN FALSE;
1469
1470 END Color_Indic_Dim_Combination;
1471
1472
1473 FUNCTION Calculate_KPI_Color (
1474 p_objective_id IN NUMBER
1475 , p_kpi_measure_id IN NUMBER
1476 , p_calc_color_flag IN BOOLEAN
1477 )
1478 RETURN BOOLEAN
1479 IS
1480
1481 h_sql VARCHAR2(32700);
1482 h_bind_vars_values_n BSC_UPDATE_UTIL.t_array_of_number;
1483 h_num_bind_vars_n NUMBER;
1484 l_color_method NUMBER;
1485 C_ALFA VARCHAR2(10);
1486 C_BETA VARCHAR2(10);
1487 x_color_level1 NUMBER;
1488 x_color_level2 NUMBER;
1489 x_color_level3 NUMBER;
1490 x_color_level4 NUMBER;
1491
1492 threshold_Prop_Table BSC_COLOR_CALC_UTIL.Threshold_Prop_Table;
1493
1494 min_perf BSC_SYS_COLORS_B.PERF_SEQUENCE%TYPE;
1495 max_perf BSC_SYS_COLORS_B.PERF_SEQUENCE%TYPE;
1496 min_perf_color_id BSC_SYS_COLORS_B.COLOR_ID%TYPE;
1497 max_perf_color_id BSC_SYS_COLORS_B.COLOR_ID%TYPE;
1498 nodata_color_id BSC_SYS_COLORS_B.COLOR_ID%TYPE;
1499
1500 h_sql_else VARCHAR2(500);
1501 l_comp_op VARCHAR2(2); --> different operator for color_method
1502 --1. <=, 2. <, 3 < any other case <=
1503 l_array_colors BSC_COLOR_REPOSITORY.t_array_colors;
1504 l_color_rec BSC_COLOR_REPOSITORY.t_color_rec;
1505
1506
1507 CURSOR c_sys_colors IS
1508 SELECT COLOR_ID, PERF_SEQUENCE, COLOR
1509 FROM bsc_sys_colors_b;
1510
1511 BEGIN
1512
1513 -- ppandey -> Get the system color properties Enh #4012218
1514 l_array_colors := BSC_COLOR_REPOSITORY.get_color_props();
1515
1516 FOR l_index IN 1 .. l_array_colors.COUNT LOOP
1517 l_color_rec := l_array_colors(l_index);
1518
1519 IF (l_color_rec.PERF_SEQ IS NULL) THEN
1520 nodata_color_id := l_color_rec.COLOR_ID;
1521 ELSE
1522 IF (min_perf IS NULL OR min_perf < l_color_rec.PERF_SEQ) THEN
1523 min_perf_color_id := l_color_rec.COLOR_ID;
1524 min_perf := l_color_rec.PERF_SEQ;
1525 --ELS
1526 END IF;
1527 IF (max_perf IS NULL OR max_perf > l_color_rec.PERF_SEQ) THEN
1528 max_perf_color_id := l_color_rec.COLOR_ID;
1529 max_perf := l_color_rec.PERF_SEQ;
1530 END IF;
1531 END IF;
1532 END LOOP;
1533
1534
1535 C_ALFA := '0.000005';
1536 C_BETA := '100000';
1537
1538 -- Trunc real and plan to 5 decimal
1539 h_sql := 'UPDATE BSC_TMP_COLORS'||
1540 ' SET VREAL = DECODE(VREAL, NULL, VREAL, TRUNC((VREAL + '||C_ALFA||') * '||C_BETA||') / '||C_BETA||'), '||
1541 ' VPLAN = DECODE(VPLAN, NULL, VPLAN, TRUNC((VPLAN + '||C_ALFA||') * '||C_BETA||') / '||C_BETA||')';
1542 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1543 commit;
1544
1545 -- Calculate percent of variation
1546 -- 0 Fix Bug# 2580240 Case Plan=Real=0
1547 h_sql := 'UPDATE BSC_TMP_COLORS'||
1548 ' SET CUMPERCENT = 100'||
1549 ' WHERE (VREAL = 0) AND (VPLAN = 0)';
1550 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1551 commit;
1552
1553 -- 1
1554 h_sql := 'UPDATE BSC_TMP_COLORS'||
1555 ' SET CUMPERCENT = TRUNC((((VREAL / VPLAN)*100) + '||C_ALFA||') * '||C_BETA||') / '||C_BETA||
1556 ' WHERE (VREAL IS NOT NULL) AND (VPLAN > 0)';
1557 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1558 commit;
1559 -- 2
1560 h_sql := 'UPDATE BSC_TMP_COLORS'||
1561 ' SET CUMPERCENT = TRUNC((((2 + ABS(VREAL / VPLAN))*100) + '||C_ALFA||') * '||C_BETA||') / '||C_BETA||
1562 ' WHERE (VREAL > 0) AND (VPLAN < 0)';
1563 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1564 commit;
1565
1566 -- 3
1567 h_sql := 'UPDATE BSC_TMP_COLORS'||
1568 ' SET CUMPERCENT = TRUNC((((2 - (VREAL / VPLAN))*100) + '||C_ALFA||') * '||C_BETA||') / '||C_BETA||
1569 ' WHERE (VREAL <= 0) AND (VPLAN < 0)';
1570 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1571 commit;
1572
1573 l_color_method := Get_Color_Method(p_objective_id, p_kpi_measure_id);
1574
1575 -- Calculate the color
1576 IF p_calc_color_flag THEN
1577
1578 threshold_Prop_Table := BSC_COLOR_CALC_UTIL.Get_Kpi_Measure_Threshold (p_objective_id, p_kpi_measure_id);
1579
1580 l_comp_op := '<=';
1581
1582 IF l_color_method = 1 THEN
1583
1584 h_sql := 'UPDATE BSC_TMP_COLORS'||
1585 ' SET COLOR = CASE WHEN VREAL > VPLAN THEN '||max_perf_color_id||
1586 ' ELSE '||min_perf_color_id||' END '||
1587 ' WHERE (VREAL = 0 OR VPLAN = 0) '||
1588 ' AND VREAL <> VPLAN';
1589
1590 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1591 commit;
1592 l_comp_op := '<=';
1593
1594 ELSIF l_color_method = 2 THEN
1595
1596 h_sql := 'UPDATE BSC_TMP_COLORS'||
1597 ' SET COLOR = CASE WHEN VREAL > VPLAN THEN '||max_perf_color_id||
1598 ' ELSE '||min_perf_color_id||' END '||
1599 ' WHERE (VREAL = 0 OR VPLAN = 0) '||
1600 ' AND VREAL <> VPLAN';
1601 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1602 commit;
1603 l_comp_op := '<';
1604
1605
1606 ELSIF l_color_method = 3 THEN
1607 h_sql := 'UPDATE BSC_TMP_COLORS'||
1608 ' SET COLOR = '||min_perf_color_id||
1609 ' WHERE ((VREAL = 0) AND (VPLAN <> 0)) OR ((VREAL <> 0) AND (VPLAN = 0))';
1610 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1611 commit;
1612
1613 l_comp_op := '<';
1614
1615
1616 END IF;
1617
1618 IF (threshold_Prop_Table.COUNT > 0) THEN -- COUNT will not be 0 ideally.
1619 h_bind_vars_values_n.delete;
1620 h_num_bind_vars_n := 0;
1621
1622 h_sql := 'UPDATE BSC_TMP_COLORS'||
1623 ' SET COLOR = CASE';
1624
1625 FOR th IN 1..threshold_Prop_Table.COUNT LOOP
1626 IF (threshold_Prop_Table(th).threshold IS NOT NULL) THEN
1627 h_sql := h_sql || ' WHEN CUMPERCENT '|| l_comp_op || threshold_Prop_Table(th).threshold ||' THEN '||threshold_Prop_Table(th).color_id;
1628 ELSE
1629 h_sql := h_sql || ' ELSE '|| threshold_Prop_Table(th).color_id || ' END';
1630 END IF;
1631 END LOOP;
1632
1633 h_sql := h_sql || ' WHERE ((VREAL = 0 AND VPLAN = 0) OR (VREAL <> 0 AND VPLAN <> 0))';
1634 h_num_bind_vars_n := 0;
1635
1636 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values_n, h_num_bind_vars_n);
1637 commit;
1638 END IF;
1639 END IF;
1640
1641 RETURN TRUE;
1642
1643 EXCEPTION
1644 WHEN OTHERS THEN
1645 ROLLBACK;
1646 BSC_MESSAGE.Add(x_message => SQLERRM,
1647 x_source => 'BSC_UPDATE_COLOR.Calculate_KPI_Color');
1648 RETURN FALSE;
1649 END Calculate_KPI_Color;
1650
1651
1652 FUNCTION calculate_trend_icon (
1653 p_tab_id IN NUMBER
1654 , p_objective_id IN NUMBER
1655 , p_kpi_measure_id IN NUMBER
1656 , p_not_pl_not_initiative IN BOOLEAN
1657 ) RETURN BOOLEAN
1658 IS
1659 CURSOR c_objective_kpis(p_indicator NUMBER) IS
1660 SELECT kpi_measure_id
1661 FROM bsc_kpi_measure_props kpi_meas
1662 WHERE kpi_meas.indicator = p_indicator;
1663 l_objective_kpis c_objective_kpis%ROWTYPE;
1664
1665 l_color_method NUMBER;
1666 e_unexpected_error EXCEPTION;
1667 l_calculate_obj_trend BOOLEAN := false;
1668
1669
1670 BEGIN
1671
1672 FOR l_objective_kpis IN c_objective_kpis(p_objective_id) LOOP
1673 l_color_method := Get_Color_Method ( p_objective_id => p_objective_id
1674 , p_kpi_measure_id => l_objective_kpis.kpi_measure_id);
1675 IF l_objective_kpis.kpi_measure_id = p_kpi_measure_id THEN
1676 l_calculate_obj_trend := true;
1677 ELSE
1678 l_calculate_obj_trend := false;
1679 END IF;
1680 IF NOT calculate_kpi_trend_icon( x_tab_id => p_tab_id
1681 , x_indicator => p_objective_id
1682 , x_measure_id => l_objective_kpis.kpi_measure_id
1683 , x_color_method => l_color_method
1684 , x_calc_obj_trend => l_calculate_obj_trend
1685 , x_not_pl_not_initiative => p_not_pl_not_initiative) THEN
1686 RAISE e_unexpected_error;
1687 END IF;
1688 END LOOP;
1689
1690 RETURN TRUE;
1691
1692 EXCEPTION
1693 WHEN OTHERS THEN
1694 ROLLBACK;
1695 BSC_MESSAGE.Add(x_message => SQLERRM,
1696 x_source => 'BSC_UPDATE_COLOR.calculate_trend_icon');
1697 RETURN FALSE;
1698 END calculate_trend_icon;
1699
1700 /*===========================================================================+
1701 | FUNCTION Color_Indicator
1702 |
1703 | This API will calculate the color of all the KPIs under an Objective.
1704 | Also, it will then roll-up the KPI colors to get the Objective color.
1705 | Objective color can have the following roll-ups on the KPI colors:
1706 | WORST, BEST, MOST_FREQUENT, WEIGHTED_AVERAGE, DEFAULT_KPI
1707 |
1708 +============================================================================*/
1709 FUNCTION Color_Indicator (
1710 x_indic_code IN NUMBER
1711 ) RETURN BOOLEAN IS
1712
1713 e_unexpected_error EXCEPTION;
1714
1715 CURSOR c_objective_color_props(p_indicator NUMBER) IS
1716 SELECT ti.tab_id,
1717 kpi.periodicity_id,
1718 --kpi.apply_color_flag,
1719 kpi.indicator_type,
1720 kpi.config_type
1721 FROM bsc_tab_indicators ti,
1722 bsc_kpis_b kpi
1723 WHERE ti.indicator = kpi.indicator
1724 AND kpi.prototype_flag <> 2
1725 AND kpi.indicator = p_indicator;
1726 l_objective_color_props c_objective_color_props%ROWTYPE;
1727
1728 l_objective_color_rec t_objective_color_rec;
1729
1730 TYPE t_cursor IS REF CURSOR;
1731 h_cursor t_cursor;
1732
1733 CURSOR c_indic_type ( pIndicator number ) is
1734 SELECT indicator_type, config_type
1735 FROM bsc_kpis_b
1736 WHERE indicator = pIndicator ;
1737
1738 TYPE t_indic_type IS RECORD (
1739 indicator_type bsc_kpis_b.indicator_type%TYPE
1740 , config_type bsc_kpis_b.config_type%TYPE
1741 );
1742 h_indic_type t_indic_type;
1743
1744 CURSOR c_indic_transformation(pIndicator number,pPropertyCode varchar2) is
1745 SELECT property_value
1746 FROM bsc_kpi_properties
1747 WHERE indicator = pIndicator
1748 AND property_code = pPropertyCode ;
1749
1750 h_db_transform VARCHAR2(50);
1751 h_indic_transformation NUMBER;
1752 h_indic_precalculated_flag BOOLEAN;
1753 h_indic_pl_flag BOOLEAN;
1754 h_indic_initiatives_flag BOOLEAN;
1755 h_sql VARCHAR2(2000);
1756 h_current_fy NUMBER;
1757 h_calc_color_flag BOOLEAN;
1758 h_yearly_flag NUMBER;
1759 h_calendar_id NUMBER;
1760 h_calendar_edw_flag NUMBER;
1761 h_edw_flag NUMBER;
1762 h_num_of_years NUMBER;
1763 h_previous_years NUMBER;
1764 h_init_period NUMBER;
1765 h_end_period NUMBER;
1766 h_i NUMBER;
1767 h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
1768 h_num_bind_vars NUMBER;
1769 -- AW_INTEGRATION: new variables
1770 h_aw_flag BOOLEAN;
1771 l_sim_indicator_flag BOOLEAN;
1772 l_kpi_measure_id NUMBER;
1773 l_color_flag BOOLEAN;
1774 l_not_pl_not_initiative BOOLEAN := false;
1775
1776 BEGIN
1777
1778 h_indic_pl_flag := FALSE;
1779 h_indic_initiatives_flag := FALSE;
1780 h_db_transform := 'DB_TRANSFORM';
1781 h_indic_precalculated_flag := FALSE;
1782 h_yearly_flag := 0;
1783 h_edw_flag := 0;
1784
1785 -- AW_INTEGRATION: Get implementation type
1786 IF BSC_UPDATE_UTIL.Get_Kpi_Impl_Type(x_indic_code) = 2 THEN
1787 h_aw_flag := TRUE;
1788 ELSE
1789 h_aw_flag := FALSE;
1790 END IF;
1791
1792 -- Get the type of the indicator
1793 OPEN c_indic_type (x_indic_code);
1794 FETCH c_indic_type INTO h_indic_type;
1795 IF c_indic_type%NOTFOUND THEN
1796 RAISE e_unexpected_error;
1797 END IF;
1798 CLOSE c_indic_type;
1799
1800 IF (h_indic_type.indicator_type = 1) AND (h_indic_type.config_type = 3) THEN
1801 h_indic_pl_flag := TRUE;
1802 ELSIF (h_indic_type.indicator_type = 1) AND (h_indic_type.config_type = 4) THEN
1803 h_indic_initiatives_flag := TRUE;
1804 END IF;
1805
1806 -- Know if the indicator is precalculated or not
1807 OPEN c_indic_transformation (x_indic_code, h_db_transform);
1808 FETCH c_indic_transformation INTO h_indic_transformation;
1809 IF c_indic_transformation%FOUND THEN
1810 IF h_indic_transformation = 0 THEN
1811 h_indic_precalculated_flag := TRUE;
1812 END IF;
1813 END IF;
1814 CLOSE c_indic_transformation;
1815
1816
1817 OPEN c_objective_color_props (x_indic_code);
1818 FETCH c_objective_color_props INTO l_objective_color_props;
1819 IF c_objective_color_props%FOUND THEN
1820
1821 l_sim_indicator_flag := FALSE;
1822 IF l_objective_color_props.INDICATOR_TYPE = 1 AND l_objective_color_props.CONFIG_TYPE = 7 THEN
1823 l_sim_indicator_flag := TRUE;
1824 END IF;
1825
1826 -- Get information about the periodicity
1827 h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(l_objective_color_props.periodicity_id);
1828 h_calendar_edw_flag := BSC_UPDATE_UTIL.Get_Calendar_EDW_Flag(h_calendar_id);
1829 h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(l_objective_color_props.periodicity_id);
1830
1831 -- Get the current fiscal year
1832 h_current_fy := BSC_UPDATE_UTIL.Get_Calendar_Fiscal_Year(h_calendar_id);
1833
1834 -- Insert into BSC_TMP_ALL_PERIODS all periods
1835 -- of the periodicity by which the indicator is going to be colored in
1836 -- this tab.
1837
1838 -- Delete current records
1839 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_ALL_PERIODS');
1840
1841 IF h_yearly_flag = 1 THEN
1842 -- Get the number of years and previous years of the indicator
1843 IF NOT BSC_UPDATE_UTIL.Get_Indic_Range_Of_Years(x_indic_code,
1844 l_objective_color_props.periodicity_id,
1845 h_num_of_years,
1846 h_previous_years) THEN
1847 RAISE e_unexpected_error;
1848 END IF;
1849
1850 h_init_period := h_current_fy - h_previous_years;
1851 h_end_period := h_init_period + h_num_of_years - 1;
1852
1853 FOR h_i IN h_init_period..h_end_period LOOP
1854 h_sql := 'INSERT /*+ append */ INTO BSC_TMP_ALL_PERIODS (PERIOD)'||
1855 ' VALUES (:1)';
1856 h_bind_vars_values.delete;
1857 h_bind_vars_values(1) := h_i;
1858 h_num_bind_vars := 1;
1859
1860 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
1861 COMMIT;
1862 END LOOP;
1863
1864 ELSE
1865 -- Periodicity different to Annual
1866 IF h_calendar_edw_flag = 0 THEN
1867 -- BSC periodicity
1868 h_sql := 'INSERT /*+ append */ INTO BSC_TMP_ALL_PERIODS'||
1869 ' SELECT DISTINCT ' || BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(l_objective_color_props.periodicity_id) ||
1870 ' AS PERIOD'||
1871 ' FROM BSC_DB_CALENDAR'||
1872 ' WHERE YEAR = :1 AND CALENDAR_ID = :2';
1873 h_bind_vars_values.delete;
1874 h_bind_vars_values(1) := h_current_fy;
1875 h_bind_vars_values(2) := h_calendar_id;
1876 h_num_bind_vars := 2;
1877 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
1878 COMMIT;
1879
1880 ELSE
1881 -- EDW periodicity
1882 h_init_period := 1;
1883 h_end_period := BSC_INTEGRATION_APIS.Get_Number_Of_Periods(h_current_fy,
1884 l_objective_color_props.periodicity_id,
1885 h_calendar_id);
1886 IF BSC_APPS.CheckError('BSC_INTEGRATION_APIS.Get_Number_Of_Periods') THEN
1887 RAISE e_unexpected_error;
1888 END IF;
1889
1890 FOR h_i IN h_init_period..h_end_period LOOP
1891 h_sql := 'INSERT /*+ append */ INTO BSC_TMP_ALL_PERIODS (PERIOD)'||
1892 ' VALUES (:1)';
1893 h_bind_vars_values(1) := h_i;
1894 h_num_bind_vars := 1;
1895 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
1896 COMMIT;
1897 END LOOP;
1898
1899 END IF;
1900
1901 END IF;
1902
1903 END IF;
1904 CLOSE c_objective_color_props;
1905
1906 -- Delete the colors of the indicator for this tab
1907 DELETE FROM bsc_sys_kpi_colors
1908 WHERE tab_id = l_objective_color_props.tab_id AND indicator = x_indic_code;
1909 DELETE FROM bsc_sys_objective_colors
1910 WHERE tab_id = l_objective_color_props.tab_id AND indicator = x_indic_code;
1911 COMMIT;
1912
1913 l_objective_color_rec.tab_id := l_objective_color_props.tab_id;
1914 l_objective_color_rec.objective_id := x_indic_code;
1915 l_objective_color_rec.obj_pl_flag := h_indic_pl_flag;
1916 l_objective_color_rec.obj_initiatives_flag := h_indic_initiatives_flag;
1917 l_objective_color_rec.obj_precalculated_flag := h_indic_precalculated_flag;
1918 l_objective_color_rec.periodicity_id := l_objective_color_props.periodicity_id;
1919 l_objective_color_rec.current_fy := h_current_fy;
1920 l_objective_color_rec.aw_flag := h_aw_flag;
1921 l_objective_color_rec.sim_flag := l_sim_indicator_flag;
1922
1923
1924 -- Calculate color for all KPIS in the Objective
1925 IF NOT Color_Kpis_In_Objective( p_objective_color_rec => l_objective_color_rec) THEN
1926 RAISE e_unexpected_error;
1927 END IF;
1928
1929 -- Roll-up KPI colors to get the Objective color
1930 Calculate_Objective_Color( p_objective_color_rec => l_objective_color_rec
1931 ,x_kpi_measure_id => l_kpi_measure_id
1932 ,x_color_flag => l_color_flag);
1933 IF NOT l_color_flag THEN
1934 RAISE e_unexpected_error;
1935 END IF;
1936
1937 COMMIT;
1938
1939 IF ( (NOT l_objective_color_rec.obj_pl_flag) AND (NOT l_objective_color_rec.obj_initiatives_flag) ) THEN
1940 l_not_pl_not_initiative := true;
1941 END IF;
1942
1943 -- Calculate Trend icons
1944 IF NOT calculate_trend_icon(p_tab_id => l_objective_color_props.tab_id, p_objective_id => x_indic_code,
1945 p_kpi_measure_id => l_kpi_measure_id, p_not_pl_not_initiative => l_not_pl_not_initiative) THEN
1946 RAISE e_unexpected_error;
1947 END IF;
1948
1949 COMMIT;
1950
1951 RETURN TRUE;
1952
1953 EXCEPTION
1954 WHEN e_unexpected_error THEN
1955 IF c_indic_type%ISOPEN THEN
1956 CLOSE c_indic_type;
1957 END IF;
1958 IF c_indic_transformation%ISOPEN THEN
1959 CLOSE c_indic_transformation;
1960 END IF;
1961 IF c_objective_color_props%ISOPEN THEN
1962 CLOSE c_objective_color_props;
1963 END IF;
1964 ROLLBACK;
1965 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_KPICOLOR_FAILED'),
1966 x_source => 'BSC_UPDATE_COLOR.Color_Indicator');
1967 RETURN FALSE;
1968
1969 WHEN OTHERS THEN
1970 IF c_indic_type%ISOPEN THEN
1971 CLOSE c_indic_type;
1972 END IF;
1973 IF c_indic_transformation%ISOPEN THEN
1974 CLOSE c_indic_transformation;
1975 END IF;
1976 IF c_objective_color_props%ISOPEN THEN
1977 CLOSE c_objective_color_props;
1978 END IF;
1979 ROLLBACK;
1980 BSC_MESSAGE.Add(x_message => SQLERRM,
1981 x_source => 'BSC_UPDATE_COLOR.Color_Indicator');
1982 RETURN FALSE;
1983
1984 END Color_Indicator;
1985
1986
1987 --LOCKING: new function
1988 /*===========================================================================+
1989 | FUNCTION Color_Indicator_AT
1990 +============================================================================*/
1991 FUNCTION Color_Indicator_AT(
1992 x_indic_code IN NUMBER
1993 ) RETURN BOOLEAN IS
1994 PRAGMA AUTONOMOUS_TRANSACTION;
1995 h_b BOOLEAN;
1996 BEGIN
1997 h_b := Color_Indicator(x_indic_code);
1998 commit; -- all autonomous transaction needs to commit
1999 RETURN h_b;
2000 END Color_Indicator_AT;
2001
2002
2003 /*===========================================================================+
2004 | FUNCTION Create_Temp_Tab_Tables
2005 +============================================================================*/
2006 FUNCTION Create_Temp_Tab_Tables RETURN BOOLEAN IS
2007 e_unexpected_error EXCEPTION;
2008
2009 h_sql VARCHAR2(2000);
2010
2011 TYPE t_cursor IS REF CURSOR;
2012 h_cursor t_cursor;
2013
2014 cursor c_tabs is
2015 SELECT tab_id, dim_level_index, parent_level_index
2016 FROM bsc_sys_com_dim_levels
2017 ORDER BY tab_id, dim_level_index;
2018
2019 TYPE t_tabs IS RECORD (
2020 tab_id bsc_sys_com_dim_levels.tab_id%TYPE,
2021 dim_level_index bsc_sys_com_dim_levels.dim_level_index%TYPE,
2022 parent_level_index bsc_sys_com_dim_levels.parent_level_index%TYPE
2023 );
2024 h_tab t_tabs;
2025
2026 h_last_tab_id NUMBER;
2027 h_family_index NUMBER;
2028 h_dim_index NUMBER;
2029
2030 h_tab_id NUMBER;
2031 h_num_dimensions NUMBER;
2032
2033 -- Array with the number of dimensions of each family
2034 h_num_dimensions_by_family BSC_UPDATE_UTIL.t_array_of_number;
2035 h_max_family_index NUMBER;
2036
2037 h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
2038 h_num_bind_vars NUMBER;
2039
2040 h_table_name VARCHAR2(30);
2041 h_table_columns BSC_UPDATE_UTIL.t_array_temp_table_cols;
2042 h_num_columns NUMBER;
2043
2044 BEGIN
2045 h_max_family_index := 0;
2046
2047 -- Create temporal table BSC_TMP_TAB_DEF with the name of key column and table
2048 -- name of each dimension of the list of each tab. Additionally I create a column
2049 -- with the family id and index within the family for the dimensions in the list.
2050 -- Example
2051 -- TAB_ID DIM_LEVEL_INDEX LEVEL_PK_COL LEVEL_TABLE_NAME FAMILY_INDEX DIM_INDEX
2052 -- ------ --------------- ------------ ---------------- ------------ ---------
2053 -- 0 0 REGION_CODE BSC_MREGION 0 0
2054 -- 0 1 BRANCH_CODE BSC_MBRANCH 0 1
2055 -- 0 2 PROD_CODE BSC_MPRODUCT 1 0
2056
2057 h_table_name := 'BSC_TMP_TAB_DEF';
2058 h_table_columns.delete;
2059 h_num_columns := 0;
2060 h_num_columns := h_num_columns + 1;
2061 h_table_columns(h_num_columns).column_name := 'TAB_ID';
2062 h_table_columns(h_num_columns).data_type := 'NUMBER';
2063 h_table_columns(h_num_columns).data_size := NULL;
2064 h_table_columns(h_num_columns).add_to_index := 'N';
2065 h_num_columns := h_num_columns + 1;
2066 h_table_columns(h_num_columns).column_name := 'DIM_LEVEL_INDEX';
2067 h_table_columns(h_num_columns).data_type := 'NUMBER';
2068 h_table_columns(h_num_columns).data_size := 3;
2069 h_table_columns(h_num_columns).add_to_index := 'N';
2070 h_num_columns := h_num_columns + 1;
2071 h_table_columns(h_num_columns).column_name := 'LEVEL_PK_COL';
2072 h_table_columns(h_num_columns).data_type := 'VARCHAR2';
2073 h_table_columns(h_num_columns).data_size := 30;
2074 h_table_columns(h_num_columns).add_to_index := 'N';
2075 h_num_columns := h_num_columns + 1;
2076 h_table_columns(h_num_columns).column_name := 'LEVEL_TABLE_NAME';
2077 h_table_columns(h_num_columns).data_type := 'VARCHAR2';
2078 h_table_columns(h_num_columns).data_size := 30;
2079 h_table_columns(h_num_columns).add_to_index := 'N';
2080 h_num_columns := h_num_columns + 1;
2081 h_table_columns(h_num_columns).column_name := 'FAMILY_INDEX';
2082 h_table_columns(h_num_columns).data_type := 'NUMBER';
2083 h_table_columns(h_num_columns).data_size := NULL;
2084 h_table_columns(h_num_columns).add_to_index := 'N';
2085 h_num_columns := h_num_columns + 1;
2086 h_table_columns(h_num_columns).column_name := 'DIM_INDEX';
2087 h_table_columns(h_num_columns).data_type := 'NUMBER';
2088 h_table_columns(h_num_columns).data_size := NULL;
2089 h_table_columns(h_num_columns).add_to_index := 'N';
2090 IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
2091 RAISE e_unexpected_error;
2092 END IF;
2093
2094 --Fix bug#4139837
2095 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TAB_DEF');
2096
2097 h_sql := 'INSERT /*+ append */ INTO BSC_TMP_TAB_DEF'||
2098 ' SELECT C.TAB_ID, C.DIM_LEVEL_INDEX,'||
2099 ' D.LEVEL_PK_COL, D.LEVEL_VIEW_NAME,'||
2100 ' 0 AS FAMILY_INDEX, 0 AS DIM_INDEX'||
2101 ' FROM BSC_SYS_COM_DIM_LEVELS C, BSC_SYS_DIM_LEVELS_B D'||
2102 ' WHERE C.DIM_LEVEL_ID = D.DIM_LEVEL_ID';
2103 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2104 commit;
2105
2106 -- Update columns FAMILY_ID and DIM_WITHIN_FAMILY
2107 -- RESTRICTION: The list only can be as following:
2108 -- dim1 --< dim2 --< dim3 dim4 --< dim5 ...
2109 -- - only 1-n relationships
2110 -- - no more than one parent
2111 -- - The dimension specified in (dim_level_index + 1) can be child
2112 -- - of the dimension specified id (dim_level_index) or the father
2113 -- - of the next familiy.
2114
2115 h_last_tab_id := -1;
2116
2117 -- OPEN c_tabs FOR c_tabs_sql;
2118 OPEN c_tabs ;
2119 FETCH c_tabs INTO h_tab;
2120 WHILE c_tabs%FOUND LOOP
2121 IF h_tab.tab_id <> h_last_tab_id THEN
2122 h_family_index := 0;
2123 h_dim_index := 0;
2124 ELSIF (h_tab.parent_level_index IS NULL) THEN
2125 h_family_index := h_family_index + 1;
2126 h_dim_index := 0;
2127 ELSE
2128 h_dim_index := h_dim_index + 1;
2129 END IF;
2130
2131 h_sql := 'UPDATE BSC_TMP_TAB_DEF'||
2132 ' SET FAMILY_INDEX = :1,'||
2133 ' DIM_INDEX = :2'||
2134 ' WHERE TAB_ID = :3'||
2135 ' AND DIM_LEVEL_INDEX = :4';
2136 h_bind_vars_values.delete;
2137 h_bind_vars_values(1) := h_family_index;
2138 h_bind_vars_values(2) := h_dim_index;
2139 h_bind_vars_values(3) := h_tab.tab_id;
2140 h_bind_vars_values(4) := h_tab.dim_level_index;
2141 h_num_bind_vars := 4;
2142 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
2143 commit;
2144
2145 h_last_tab_id := h_tab.tab_id;
2146
2147 FETCH c_tabs INTO h_tab;
2148 END LOOP;
2149 CLOSE c_tabs;
2150
2151 COMMIT;
2152
2153
2154 -- Create temporal table BSC_TMP_TAB_COM with the different combinations
2155 -- of tab dimensions.
2156 -- Example:
2157 -- Suppose tab 0 have a list with two families:
2158 -- Family 0: dimension 0 - Region, dimension 1 - Branch
2159 -- Family 1: dimension 0 - Product
2160 -- There is two combination:
2161 -- Combination 0: Region, Product
2162 -- Combination 1: Branch, Product
2163
2164 -- TAB_ID COM_INDEX FAMILY_INDEX DIM_INDEX
2165 -- ------ --------- ------------ ---------
2166 -- 0 0 0 0
2167 -- 0 0 1 0
2168 -- 0 1 0 1
2169 -- 0 1 1 0
2170
2171 h_table_name := 'BSC_TMP_TAB_COM';
2172 h_table_columns.delete;
2173 h_num_columns := 0;
2174 h_num_columns := h_num_columns + 1;
2175 h_table_columns(h_num_columns).column_name := 'TAB_ID';
2176 h_table_columns(h_num_columns).data_type := 'NUMBER';
2177 h_table_columns(h_num_columns).data_size := NULL;
2178 h_table_columns(h_num_columns).add_to_index := 'N';
2179 h_num_columns := h_num_columns + 1;
2180 h_table_columns(h_num_columns).column_name := 'COM_INDEX';
2181 h_table_columns(h_num_columns).data_type := 'NUMBER';
2182 h_table_columns(h_num_columns).data_size := NULL;
2183 h_table_columns(h_num_columns).add_to_index := 'N';
2184 h_num_columns := h_num_columns + 1;
2185 h_table_columns(h_num_columns).column_name := 'FAMILY_INDEX';
2186 h_table_columns(h_num_columns).data_type := 'NUMBER';
2187 h_table_columns(h_num_columns).data_size := NULL;
2188 h_table_columns(h_num_columns).add_to_index := 'N';
2189 h_num_columns := h_num_columns + 1;
2190 h_table_columns(h_num_columns).column_name := 'DIM_INDEX';
2191 h_table_columns(h_num_columns).data_type := 'NUMBER';
2192 h_table_columns(h_num_columns).data_size := NULL;
2193 h_table_columns(h_num_columns).add_to_index := 'N';
2194 IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
2195 RAISE e_unexpected_error;
2196 END IF;
2197
2198 --Fix bug#4139837
2199 BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TAB_COM');
2200
2201 -- Insert records
2202 h_sql := 'SELECT TAB_ID, FAMILY_INDEX, COUNT(DIM_INDEX)'||
2203 ' FROM BSC_TMP_TAB_DEF'||
2204 ' GROUP BY TAB_ID, FAMILY_INDEX'||
2205 ' ORDER BY TAB_ID, FAMILY_INDEX';
2206
2207 h_last_tab_id := -1;
2208
2209 OPEN h_cursor FOR h_sql;
2210 FETCH h_cursor INTO h_tab_id, h_family_index, h_num_dimensions;
2211 WHILE h_cursor%FOUND LOOP
2212 IF (h_last_tab_id <> h_tab_id) AND (h_last_tab_id <> -1) THEN
2213 IF NOT Insert_Tab_Combinations(h_last_tab_id, h_num_dimensions_by_family, h_max_family_index) THEN
2214 RAISE e_unexpected_error;
2215 END IF;
2216 END IF;
2217
2218 h_max_family_index := h_family_index;
2219 h_num_dimensions_by_family(h_family_index) := h_num_dimensions;
2220
2221 h_last_tab_id := h_tab_id;
2222
2223 FETCH h_cursor INTO h_tab_id, h_family_index, h_num_dimensions;
2224 END LOOP;
2225 CLOSE h_cursor;
2226
2227 IF h_last_tab_id <> -1 THEN
2228 IF NOT Insert_Tab_Combinations(h_last_tab_id, h_num_dimensions_by_family, h_max_family_index) THEN
2229 RAISE e_unexpected_error;
2230 END IF;
2231 END IF;
2232
2233 COMMIT;
2234
2235 -- Create temporal table BSC_TMP_ALL_PERIODS
2236 h_table_name := 'BSC_TMP_ALL_PERIODS';
2237 h_table_columns.delete;
2238 h_num_columns := 0;
2239 h_num_columns := h_num_columns + 1;
2240 h_table_columns(h_num_columns).column_name := 'PERIOD';
2241 h_table_columns(h_num_columns).data_type := 'NUMBER';
2242 h_table_columns(h_num_columns).data_size := 5;
2243 h_table_columns(h_num_columns).add_to_index := 'N';
2244 IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
2245 RAISE e_unexpected_error;
2246 END IF;
2247
2248
2249 -- Create temporal table BSC_TMP_DATA_COLOR
2250 -- Key columns are for common dimensions. Like BSC_SYS_KPI_COLORS I will
2251 -- create 8 key columns
2252 h_table_name := 'BSC_TMP_DATA_COLOR';
2253 h_table_columns.delete;
2254 h_num_columns := 0;
2255 h_num_columns := h_num_columns + 1;
2256 h_table_columns(h_num_columns).column_name := 'PERIOD';
2257 h_table_columns(h_num_columns).data_type := 'NUMBER';
2258 h_table_columns(h_num_columns).data_size := 5;
2259 h_table_columns(h_num_columns).add_to_index := 'Y';
2260 h_num_columns := h_num_columns + 1;
2261 h_table_columns(h_num_columns).column_name := 'TYPE';
2262 h_table_columns(h_num_columns).data_type := 'NUMBER';
2263 h_table_columns(h_num_columns).data_size := 3;
2264 h_table_columns(h_num_columns).add_to_index := 'Y';
2265 --Bug#4099338 move the key columns here, since the generic function that create the index
2266 -- will start taking off last columns if the index cannot be created due to error ORA-01450
2267 FOR h_i IN 1..8 LOOP
2268 h_num_columns := h_num_columns + 1;
2269 h_table_columns(h_num_columns).column_name := 'KEY'||h_i;
2270 h_table_columns(h_num_columns).data_type := 'VARCHAR2';
2271 h_table_columns(h_num_columns).data_size := 400;
2272 h_table_columns(h_num_columns).add_to_index := 'Y';
2273 END LOOP;
2274 h_num_columns := h_num_columns + 1;
2275 h_table_columns(h_num_columns).column_name := 'TOTAL';
2276 h_table_columns(h_num_columns).data_type := 'NUMBER';
2277 h_table_columns(h_num_columns).data_size := NULL;
2278 h_table_columns(h_num_columns).add_to_index := 'N';
2279 IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
2280 RAISE e_unexpected_error;
2281 END IF;
2282
2283 -- Create temporal table BSC_TMP_COLORS
2284 -- Key columns are for common dimensions. Like BSC_SYS_KPI_COLORS I will
2285 -- create 8 key columns
2286 h_table_name := 'BSC_TMP_COLORS';
2287 h_table_columns.delete;
2288 h_num_columns := 0;
2289 h_num_columns := h_num_columns + 1;
2290 h_table_columns(h_num_columns).column_name := 'PERIOD';
2291 h_table_columns(h_num_columns).data_type := 'NUMBER';
2292 h_table_columns(h_num_columns).data_size := 5;
2293 h_table_columns(h_num_columns).add_to_index := 'Y';
2294 --Bug#4099338 move the key columns here, since the generic function that create the index
2295 -- will start taking off last columns if the index cannot be created due to error ORA-01450
2296 FOR h_i IN 1..8 LOOP
2297 h_num_columns := h_num_columns + 1;
2298 h_table_columns(h_num_columns).column_name := 'KEY'||h_i;
2299 h_table_columns(h_num_columns).data_type := 'VARCHAR2';
2300 h_table_columns(h_num_columns).data_size := 400;
2301 h_table_columns(h_num_columns).add_to_index := 'Y';
2302 END LOOP;
2303 h_num_columns := h_num_columns + 1;
2304 h_table_columns(h_num_columns).column_name := 'VPLAN';
2305 h_table_columns(h_num_columns).data_type := 'NUMBER';
2306 h_table_columns(h_num_columns).data_size := NULL;
2307 h_table_columns(h_num_columns).add_to_index := 'N';
2308 h_num_columns := h_num_columns + 1;
2309 h_table_columns(h_num_columns).column_name := 'VREAL';
2310 h_table_columns(h_num_columns).data_type := 'NUMBER';
2311 h_table_columns(h_num_columns).data_size := NULL;
2312 h_table_columns(h_num_columns).add_to_index := 'N';
2313 h_num_columns := h_num_columns + 1;
2314 h_table_columns(h_num_columns).column_name := 'CUMPERCENT';
2315 h_table_columns(h_num_columns).data_type := 'NUMBER';
2316 h_table_columns(h_num_columns).data_size := NULL;
2317 h_table_columns(h_num_columns).add_to_index := 'N';
2318 h_num_columns := h_num_columns + 1;
2319 h_table_columns(h_num_columns).column_name := 'COLOR';
2320 h_table_columns(h_num_columns).data_type := 'NUMBER';
2321 h_table_columns(h_num_columns).data_size := NULL;
2322 h_table_columns(h_num_columns).add_to_index := 'N';
2323 IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
2324 RAISE e_unexpected_error;
2325 END IF;
2326
2327 RETURN TRUE;
2328
2329 EXCEPTION
2330 WHEN e_unexpected_error THEN
2331 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_TEMP_TABTABLES_FAILED'),
2332 x_source => 'BSC_UPDATE_COLOR.Create_Temp_Tab_Tables');
2333 RETURN FALSE;
2334
2335 WHEN OTHERS THEN
2336 BSC_MESSAGE.Add(x_message => SQLERRM,
2337 x_source => 'BSC_UPDATE_COLOR.Create_Temp_Tab_Tables');
2338
2339 RETURN FALSE;
2340
2341 END Create_Temp_Tab_Tables;
2342
2343 --LOCKING: new function
2344 /*===========================================================================+
2345 | FUNCTION Create_Temp_Tab_Tables_AT
2346 +============================================================================*/
2347 FUNCTION Create_Temp_Tab_Tables_AT RETURN BOOLEAN IS
2348 PRAGMA AUTONOMOUS_TRANSACTION;
2349 h_b BOOLEAN;
2350 BEGIN
2351 h_b := Create_Temp_Tab_Tables;
2352 commit; -- all autonomous transaction needs to commit
2353 RETURN h_b;
2354 END Create_Temp_Tab_Tables_AT;
2355
2356
2357 /*===========================================================================+
2358 | FUNCTION Drop_Temp_Tab_Tables
2359 +============================================================================*/
2360 FUNCTION Drop_Temp_Tab_Tables RETURN BOOLEAN IS
2361
2362 e_unexpected_error EXCEPTION;
2363
2364 BEGIN
2365 -- We are not going to drop global temporary tables
2366 RETURN TRUE;
2367
2368 EXCEPTION
2369 WHEN e_unexpected_error THEN
2370 BSC_MESSAGE.Add(x_message => SQLERRM,
2371 x_source => 'BSC_UPDATE_COLOR.Drop_Temp_Tab_Tables');
2372 RETURN FALSE;
2373
2374 WHEN OTHERS THEN
2375 BSC_MESSAGE.Add(x_message => SQLERRM,
2376 x_source => 'BSC_UPDATE_COLOR.Drop_Temp_Tab_Tables');
2377 RETURN FALSE;
2378
2379 END Drop_Temp_Tab_Tables;
2380
2381
2382 /*===========================================================================+
2383 | FUNCTION Get_Condition_On_Color_Table
2384 +============================================================================*/
2385 FUNCTION Get_Condition_On_Color_Table(
2386 x_indic_code IN NUMBER,
2387 x_aw_flag IN BOOLEAN, -- AW_INTEGRATION: new parameter
2388 x_indic_pl_flag IN BOOLEAN,
2389 x_indic_precalculated_flag IN BOOLEAN,
2390 x_dim_set_id IN NUMBER,
2391 x_table_name IN VARCHAR2,
2392 x_dim_combination IN BSC_UPDATE_UTIL.t_array_of_number,
2393 x_dim_com_keys IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2394 x_num_families IN NUMBER,
2395 x_comp_level_pk_col IN VARCHAR2,
2396 x_color_by_total IN NUMBER,
2397 x_condition OUT NOCOPY VARCHAR2,
2398 x_bind_vars_values OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2399 x_num_bind_vars OUT NOCOPY NUMBER,
2400 x_aw_limit_tbl IN OUT NOCOPY BIS_PMV_PAGE_PARAMETER_TBL --AW_INTEGRATION: new parameter
2401 ) RETURN BOOLEAN IS
2402
2403 -- BSC-BIS-DIMENSIONS: I have changed the type of x_bind_vars_values to use VARCHAR2
2404 -- This is to support NUMBER/VHARCHAR2 in key columns
2405
2406 e_unexpected_error EXCEPTION;
2407
2408 h_i NUMBER;
2409 h_sql VARCHAR2(2000);
2410
2411 TYPE t_cursor IS REF CURSOR;
2412 h_cursor t_cursor;
2413
2414 cursor c_pl_dimension_info ( pLevelPkCol varchar2) is
2415 SELECT e.level_view_name, r.relation_col
2416 FROM bsc_sys_dim_levels_b e, bsc_sys_dim_level_rels r
2417 WHERE e.dim_level_id = r.dim_level_id AND
2418 e.level_pk_col = pLevelPkCol ;
2419
2420 h_pl_account_table_name VARCHAR2(30);
2421 h_pl_type_of_account_key VARCHAR2(30);
2422 h_pl_profit_account NUMBER;
2423
2424 cursor c_table_keys (pTableName varchar2, pColType varchar2) is
2425 SELECT column_name
2426 FROM bsc_db_tables_cols
2427 WHERE table_name = pTableName
2428 AND column_type = pColType ;
2429
2430 h_column_type_p VARCHAR2(1);
2431
2432 h_table_key VARCHAR2(30);
2433 h_key_belong_to_list BOOLEAN;
2434
2435 cursor c_key_item ( pIndicator number, pDimSetId number, pLevelPkCol varchar2) is
2436 SELECT default_key_value
2437 FROM bsc_kpi_dim_levels_b
2438 WHERE indicator = pIndicator
2439 AND dim_set_id = pDimSetId
2440 AND level_pk_col = pLevelPkCol
2441 AND default_key_value IS NOT NULL ;
2442
2443 -- BSC-BIS-DIMENSIONS: The default key value may be NUMBER or VARCHAR2. So changing the type
2444 -- of this variable.
2445 h_default_key_value VARCHAR2(4000);
2446
2447 h_new_condition VARCHAR2(200);
2448
2449 h_key_value NUMBER;
2450
2451 --AW_INTEGRATION: new variables
2452 h_aw_limit_rec BIS_PMV_PAGE_PARAMETER_REC;
2453
2454 BEGIN
2455 h_column_type_p := 'P';
2456
2457 x_num_bind_vars := 0;
2458 x_condition := NULL;
2459
2460 IF x_indic_pl_flag THEN
2461 -- The indicator is a PL indicator. We need the condition
2462 -- ACCOUNT_CODE = 6 (In the example 6 is the profit account)
2463
2464 -- In a PL indicator the name of the accont key (example: ACCOUNT_CODE)
2465 -- is in x_comp_level_pk_col parameter
2466
2467 IF x_comp_level_pk_col IS NULL THEN
2468 RAISE e_unexpected_error;
2469 END IF;
2470
2471 -- Get the name of account dimension table and the name of type of account column
2472 --OPEN c_pl_dimension_info FOR c_pl_dimension_info_sql USING x_comp_level_pk_col;
2473 OPEN c_pl_dimension_info (x_comp_level_pk_col);
2474 FETCH c_pl_dimension_info INTO h_pl_account_table_name, h_pl_type_of_account_key;
2475 IF c_pl_dimension_info%NOTFOUND THEN
2476 RAISE e_unexpected_error;
2477 END IF;
2478 CLOSE c_pl_dimension_info;
2479
2480 -- Get the profit account
2481 h_sql := 'SELECT CODE'||
2482 ' FROM '||h_pl_account_table_name||
2483 ' WHERE '||h_pl_type_of_account_key||' = :1';
2484
2485 OPEN h_cursor FOR h_sql USING 3;
2486 FETCH h_cursor INTO h_pl_profit_account;
2487 IF h_cursor%NOTFOUND THEN
2488 RAISE e_unexpected_error;
2489 END IF;
2490 CLOSE h_cursor;
2491
2492 -- Make the condition
2493 x_num_bind_vars := x_num_bind_vars + 1;
2494 x_condition := '('||x_comp_level_pk_col||' = :'||x_num_bind_vars||')';
2495 x_bind_vars_values(x_num_bind_vars) := h_pl_profit_account;
2496
2497 --AW_INTEGRATION: This is to limit the dimension before querying the view.
2498 IF x_aw_flag THEN
2499 h_aw_limit_rec := BIS_PMV_PAGE_PARAMETER_REC(null,null,null,null,null,null);
2500 h_aw_limit_rec.parameter_name := BSC_UPDATE_UTIL.Get_Dim_Level_Table_Name(x_comp_level_pk_col);
2501 h_aw_limit_rec.parameter_value := h_pl_profit_account;
2502 h_aw_limit_rec.dimension := 'DIMENSION';
2503 x_aw_limit_tbl.extend;
2504 x_aw_limit_tbl(x_aw_limit_tbl.LAST) := h_aw_limit_rec;
2505 END IF;
2506 END IF;
2507
2508
2509 -- Get the table keys and try to figure out the condition on each one of them
2510 OPEN c_table_keys (x_table_name, h_column_type_p);
2511 FETCH c_table_keys INTO h_table_key;
2512 WHILE c_table_keys%FOUND LOOP
2513 h_new_condition := NULL;
2514 h_key_belong_to_list := FALSE;
2515
2516 -- If the key is part of the current condition we dont add another condition on this key
2517 IF NVL(INSTR(x_condition, h_table_key), 0) = 0 THEN
2518 FOR h_i IN 0 .. x_num_families - 1 LOOP
2519 IF h_table_key = x_dim_com_keys(h_i) THEN
2520 -- If the key belong to the list and is the first drill in his family then
2521 -- we need all records in the table including the zero code.
2522 -- Otherwise we dont need the zero code.
2523
2524 -- BSC-BIS-DIMENSIONS: To support NUMBER/VARCHAR2 I will use '0' and
2525 -- instead of > '0' I will use <> '0' that will not impact the purpose of the
2526 -- query
2527 IF x_dim_combination(h_i) > 0 THEN
2528 x_num_bind_vars := x_num_bind_vars + 1;
2529 h_new_condition := h_table_key||' <> :'||x_num_bind_vars;
2530 x_bind_vars_values(x_num_bind_vars) := '0';
2531 END IF;
2532
2533 --AW_INTEGRATION: Add all the values (including 0 code) of this dimension to the limit table
2534 IF x_aw_flag THEN
2535 h_aw_limit_rec := BIS_PMV_PAGE_PARAMETER_REC(null,null,null,null,null,null);
2536 h_aw_limit_rec.parameter_name := BSC_UPDATE_UTIL.Get_Dim_Level_Table_Name(h_table_key);
2537 h_aw_limit_rec.parameter_value := '^ALL';
2538 h_aw_limit_rec.dimension := 'DIMENSION';
2539 x_aw_limit_tbl.extend;
2540 x_aw_limit_tbl(x_aw_limit_tbl.LAST) := h_aw_limit_rec;
2541 END IF;
2542
2543 h_key_belong_to_list := TRUE;
2544
2545 EXIT;
2546 END IF;
2547 END LOOP;
2548
2549 IF NOT h_key_belong_to_list THEN
2550 IF (h_table_key = x_comp_level_pk_col) AND (x_color_by_total = 0) THEN
2551 -- If the key is the drill that is in comparison then we dont need the zero code
2552
2553 -- BSC-BIS-DIMENSIONS: To support NUMBER/VARCHAR2 I will use '0' and
2554 -- instead of > '0' I will use <> '0' that will not impact the purpose of the
2555 -- query
2556 x_num_bind_vars := x_num_bind_vars + 1;
2557 h_new_condition := h_table_key||' <> :'||x_num_bind_vars;
2558 x_bind_vars_values(x_num_bind_vars) := '0';
2559
2560 --AW_INTEGRATION: Add all the values of this dimension (including 0 code) to the limit table
2561 IF x_aw_flag THEN
2562 h_aw_limit_rec := BIS_PMV_PAGE_PARAMETER_REC(null,null,null,null,null,null);
2563 h_aw_limit_rec.parameter_name := BSC_UPDATE_UTIL.Get_Dim_Level_Table_Name(h_table_key);
2564 h_aw_limit_rec.parameter_value := '^ALL';
2565 h_aw_limit_rec.dimension := 'DIMENSION';
2566 x_aw_limit_tbl.extend;
2567 x_aw_limit_tbl(x_aw_limit_tbl.LAST) := h_aw_limit_rec;
2568 END IF;
2569
2570 ELSE
2571 OPEN c_key_item (x_indic_code, x_dim_set_id, h_table_key);
2572 FETCH c_key_item INTO h_default_key_value;
2573 IF c_key_item%FOUND THEN
2574 -- If the drill enter in an item the condition is that
2575 x_num_bind_vars := x_num_bind_vars + 1;
2576 h_new_condition := h_table_key||' = :'||x_num_bind_vars;
2577 x_bind_vars_values(x_num_bind_vars) := h_default_key_value;
2578
2579 --AW_INTEGRATION: Add this key item to the limit table
2580 IF x_aw_flag THEN
2581 h_aw_limit_rec := BIS_PMV_PAGE_PARAMETER_REC(null,null,null,null,null,null);
2582 h_aw_limit_rec.parameter_name := BSC_UPDATE_UTIL.Get_Dim_Level_Table_Name(h_table_key);
2583 h_aw_limit_rec.parameter_value := h_default_key_value;
2584 h_aw_limit_rec.dimension := 'DIMENSION';
2585 x_aw_limit_tbl.extend;
2586 x_aw_limit_tbl(x_aw_limit_tbl.LAST) := h_aw_limit_rec;
2587 END IF;
2588
2589 ELSE
2590 --BSC-MV Note: I am reviewing this logic.
2591 -- If this drill is not part of the list button, it is not in comparison,
2592 -- and there is no key item then
2593 -- by design the table used to color is the one where the drill is in total
2594 -- BSC Calculate zero codes always so the condition should be to look
2595 -- for the zero code. Even if the indicator is precalculated
2596 -- we request the user to input all the zero codes combinations
2597
2598 -- BSC-BIS-DIMENSIONS: To support NUMBER/VARCHAR2 I will use '0'
2599
2600 x_num_bind_vars := x_num_bind_vars + 1;
2601 h_new_condition := h_table_key||' = :'||x_num_bind_vars;
2602 x_bind_vars_values(x_num_bind_vars) := '0';
2603
2604 -- AW_INTEGRATION: Add to the limit table
2605 IF x_aw_flag THEN
2606 h_aw_limit_rec := BIS_PMV_PAGE_PARAMETER_REC(null,null,null,null,null,null);
2607 h_aw_limit_rec.parameter_name := BSC_UPDATE_UTIL.Get_Dim_Level_Table_Name(h_table_key);
2608 h_aw_limit_rec.parameter_value := '0';
2609 h_aw_limit_rec.dimension := 'DIMENSION';
2610 x_aw_limit_tbl.extend;
2611 x_aw_limit_tbl(x_aw_limit_tbl.LAST) := h_aw_limit_rec;
2612 END IF;
2613
2614 END IF;
2615 CLOSE c_key_item;
2616 END IF;
2617 END IF;
2618 END IF;
2619
2620 IF h_new_condition IS NOT NULL THEN
2621 IF x_condition IS NULL THEN
2622 x_condition := '('||h_new_condition||')';
2623 ELSE
2624 x_condition := x_condition||' AND ('||h_new_condition||')';
2625 END IF;
2626 END IF;
2627
2628 FETCH c_table_keys INTO h_table_key;
2629 END LOOP;
2630 CLOSE c_table_keys;
2631
2632 RETURN TRUE;
2633
2634 EXCEPTION
2635 WHEN e_unexpected_error THEN
2636 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_COLORTABLE_COND_FAILED'),
2637 x_source => 'BSC_UPDATE_COLOR.Get_Condition_On_Color_Table');
2638 RETURN FALSE;
2639
2640 WHEN OTHERS THEN
2641 BSC_MESSAGE.Add(x_message => SQLERRM,
2642 x_source => 'BSC_UPDATE_COLOR.Get_Condition_On_Color_Table');
2643 RETURN FALSE;
2644
2645 END Get_Condition_On_Color_Table;
2646
2647
2648 FUNCTION Get_Table_For_Drill_Comb (
2649 p_indic_code IN NUMBER
2650 , p_periodicity_id IN NUMBER
2651 , p_dim_set_id IN NUMBER
2652 , p_drill_comb IN VARCHAR2
2653 , x_level_comb OUT NOCOPY VARCHAR2
2654 )
2655 RETURN VARCHAR2
2656 IS
2657 e_unexpected_error EXCEPTION;
2658
2659 h_level_comb VARCHAR2(20);
2660 h_table_name VARCHAR2(30);
2661 h_i NUMBER;
2662 h_match BOOLEAN;
2663 h_status_1 VARCHAR2(1);
2664 h_status_2 VARCHAR2(1);
2665
2666
2667 CURSOR c_indic_tables(pIndicator NUMBER, pPeriodicity NUMBER, pDimSetId NUMBER) IS
2668 SELECT level_comb, table_name
2669 FROM bsc_kpi_data_tables
2670 WHERE indicator = pIndicator
2671 AND periodicity_id = pPeriodicity
2672 AND dim_set_id = pDimSetId
2673 AND table_name IS NOT NULL;
2674
2675 BEGIN
2676
2677 OPEN c_indic_tables (p_indic_code, p_periodicity_id, p_dim_set_id);
2678 FETCH c_indic_tables INTO h_level_comb, h_table_name;
2679 IF c_indic_tables%NOTFOUND THEN
2680 RAISE e_unexpected_error;
2681 END IF;
2682
2683 WHILE c_indic_tables%FOUND LOOP
2684
2685 IF LENGTH(p_drill_comb) = LENGTH(h_level_comb) THEN
2686
2687 h_match := TRUE;
2688
2689 FOR h_i IN 1 .. LENGTH(p_drill_comb) LOOP
2690
2691 h_status_1 := SUBSTR(p_drill_comb, h_i, 1);
2692 h_status_2 := SUBSTR(h_level_comb, h_i, 1);
2693
2694 IF NOT((h_status_1 = h_status_2) OR (h_status_1 = '?') OR (h_status_2 = '?')) THEN
2695 h_match := FALSE;
2696 EXIT;
2697 END IF;
2698
2699 END LOOP;
2700
2701 IF h_match THEN
2702
2703 CLOSE c_indic_tables;
2704 x_level_comb := h_level_comb;
2705
2706 RETURN h_table_name;
2707
2708 END IF;
2709
2710 END IF;
2711
2712 FETCH c_indic_tables INTO h_level_comb, h_table_name;
2713
2714 END LOOP;
2715
2716 CLOSE c_indic_tables;
2717
2718 RETURN NULL;
2719
2720 EXCEPTION
2721 WHEN e_unexpected_error THEN
2722 IF c_indic_tables%ISOPEN THEN
2723 CLOSE c_indic_tables;
2724 END IF;
2725 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_RETR_COLORTABLE_FAILED'),
2726 x_source => 'BSC_UPDATE_COLOR.Get_Table_For_Drill_Comb');
2727 RETURN NULL;
2728
2729 WHEN OTHERS THEN
2730 IF c_indic_tables%ISOPEN THEN
2731 CLOSE c_indic_tables;
2732 END IF;
2733 BSC_MESSAGE.Add(x_message => SQLERRM,
2734 x_source => 'BSC_UPDATE_COLOR.Get_Table_For_Drill_Comb');
2735 RETURN NULL;
2736 END Get_Table_For_Drill_Comb;
2737
2738
2739 /*===========================================================================+
2740 | FUNCTION Get_Table_Used_To_Color
2741 +============================================================================*/
2742 FUNCTION Get_Table_Used_To_Color(
2743 x_indic_code IN NUMBER,
2744 x_periodicity_id IN NUMBER,
2745 x_dim_set_id IN NUMBER,
2746 x_comp_level_pk_col IN VARCHAR2,
2747 x_color_by_total IN NUMBER,
2748 x_selected_dim_keys IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2749 x_num_selected_dim_keys IN NUMBER,
2750 x_level_comb OUT NOCOPY VARCHAR2
2751 ) RETURN VARCHAR2 IS
2752
2753 e_unexpected_error EXCEPTION;
2754
2755 h_level_pk_col VARCHAR2(30);
2756
2757 TYPE t_cursor IS REF CURSOR;
2758
2759 cursor c_drill_index(pIndicator number, pDimSetId number, pLevelPkCol varchar2) is
2760 SELECT dim_level_index
2761 FROM bsc_kpi_dim_levels_b
2762 WHERE indicator = pIndicator
2763 AND dim_set_id = pDimSetId
2764 AND level_pk_col = pLevelPkCol;
2765
2766 cursor c_key_items(pIndicator number, pDimSetId number, pStatus varchar2) is
2767 SELECT dim_level_index
2768 FROM bsc_kpi_dim_levels_b
2769 WHERE indicator = pIndicator
2770 AND dim_set_id = pDimSetId
2771 AND status = pStatus
2772 AND default_key_value IS NOT NULL ;
2773
2774 cursor c_indic_drills(pIndicator number, pDImSetId number, pStatus varchar2) is
2775 SELECT dim_level_index
2776 FROM bsc_kpi_dim_levels_b
2777 WHERE indicator = pIndicator
2778 AND dim_set_id = pDImSetId
2779 AND status = pStatus
2780 ORDER BY dim_level_index ;
2781
2782 h_dim_level_index NUMBER;
2783
2784 h_level_comb VARCHAR2(20);
2785 h_table_name VARCHAR2(30);
2786
2787 h_selected_drills BSC_UPDATE_UTIL.t_array_of_number;
2788 h_num_selected_drills NUMBER;
2789
2790 h_drill_comb VARCHAR2(20);
2791
2792 h_i NUMBER;
2793
2794 BEGIN
2795 h_num_selected_drills := 0;
2796 h_drill_comb := NULL;
2797
2798 -- Insert into h_selected_drills the internal drill index of selected drills
2799
2800 FOR h_i IN 0 .. x_num_selected_dim_keys - 1 LOOP
2801 h_level_pk_col := x_selected_dim_keys(h_i);
2802
2803 OPEN c_drill_index (x_indic_code, x_dim_set_id, h_level_pk_col);
2804 FETCH c_drill_index INTO h_dim_level_index;
2805 IF c_drill_index%NOTFOUND THEN
2806 RAISE e_unexpected_error;
2807 END IF;
2808 CLOSE c_drill_index;
2809
2810 h_num_selected_drills := h_num_selected_drills + 1;
2811 h_selected_drills(h_num_selected_drills):= h_dim_level_index;
2812
2813 END LOOP;
2814
2815 -- Insert into h_selected_drill the internal drill index of comparison drill
2816
2817 IF (x_comp_level_pk_col IS NOT NULL) AND (x_color_by_total = 0) THEN
2818 h_level_pk_col := x_comp_level_pk_col;
2819
2820 OPEN c_drill_index (x_indic_code, x_dim_set_id, h_level_pk_col);
2821 FETCH c_drill_index INTO h_dim_level_index;
2822 IF c_drill_index%NOTFOUND THEN
2823 RAISE e_unexpected_error;
2824 END IF;
2825 CLOSE c_drill_index;
2826
2827 h_num_selected_drills := h_num_selected_drills + 1;
2828 h_selected_drills(h_num_selected_drills) := h_dim_level_index;
2829
2830 END IF;
2831
2832 -- Insert into h_selected_drill the internal drill index of drills that
2833 -- enter in a specific item
2834 OPEN c_key_items (x_indic_code, x_dim_set_id, 2);
2835 FETCH c_key_items INTO h_dim_level_index;
2836 WHILE c_key_items%FOUND LOOP
2837 IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Number(h_dim_level_index,
2838 h_selected_drills,
2839 h_num_selected_drills) THEN
2840 h_num_selected_drills := h_num_selected_drills + 1;
2841 h_selected_drills(h_num_selected_drills) := h_dim_level_index;
2842
2843 END IF;
2844
2845 FETCH c_key_items INTO h_dim_level_index;
2846 END LOOP;
2847 CLOSE c_key_items;
2848
2849 -- Get the list of drills of indicator
2850 OPEN c_indic_drills (x_indic_code, x_dim_set_id, 2);
2851 FETCH c_indic_drills INTO h_dim_level_index;
2852
2853 IF c_indic_drills%NOTFOUND THEN
2854 -- The indicator in the given configuration (dimension set)
2855 -- doesnt have any drill. So, the table that use the indicator
2856 -- is the only one it has.
2857
2858 h_drill_comb := '?';
2859
2860 h_table_name := Get_Table_For_Drill_Comb
2861 ( p_indic_code => x_indic_code
2862 , p_periodicity_id => x_periodicity_id
2863 , p_dim_set_id => x_dim_set_id
2864 , p_drill_comb => h_drill_comb
2865 , x_level_comb => x_level_comb
2866 );
2867
2868 IF h_table_name IS NULL THEN
2869
2870 RAISE e_unexpected_error;
2871 END IF;
2872
2873 CLOSE c_indic_drills;
2874
2875 RETURN h_table_name;
2876 END IF;
2877
2878 -- Create a string with the combination of drills base on the selected drills
2879 h_drill_comb := NULL;
2880
2881 WHILE c_indic_drills%FOUND LOOP
2882 IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Number(h_dim_level_index,
2883 h_selected_drills,
2884 h_num_selected_drills) THEN
2885 h_drill_comb := h_drill_comb||'0';
2886 ELSE
2887 h_drill_comb := h_drill_comb||'1';
2888 END IF;
2889
2890 FETCH c_indic_drills INTO h_dim_level_index;
2891 END LOOP;
2892 CLOSE c_indic_drills;
2893
2894 -- Look into indicator tables to see which table match the drill combination
2895 h_table_name := Get_Table_For_Drill_Comb
2896 ( p_indic_code => x_indic_code
2897 , p_periodicity_id => x_periodicity_id
2898 , p_dim_set_id => x_dim_set_id
2899 , p_drill_comb => h_drill_comb
2900 , x_level_comb => x_level_comb
2901 );
2902
2903 RETURN h_table_name;
2904
2905 EXCEPTION
2906 WHEN e_unexpected_error THEN
2907 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_RETR_COLORTABLE_FAILED'),
2908 x_source => 'BSC_UPDATE_COLOR.Get_Table_Used_To_Color');
2909 RETURN NULL;
2910
2911 WHEN OTHERS THEN
2912 BSC_MESSAGE.Add(x_message => SQLERRM,
2913 x_source => 'BSC_UPDATE_COLOR.Get_Table_Used_To_Color');
2914 RETURN NULL;
2915 END Get_Table_Used_To_Color;
2916
2917
2918 /*===========================================================================+
2919 | FUNCTION Insert_Tab_Combinations
2920 +============================================================================*/
2921 FUNCTION Insert_Tab_Combinations(
2922 x_tab_id IN NUMBER,
2923 x_num_dimensions_by_family IN BSC_UPDATE_UTIL.t_array_of_number,
2924 x_max_family_index IN NUMBER
2925 ) RETURN BOOLEAN IS
2926
2927 h_num_combinations NUMBER;
2928
2929 h_i_family NUMBER;
2930 h_i_combination NUMBER;
2931
2932 h_times NUMBER;
2933 h_repeat NUMBER;
2934
2935 h_i_times NUMBER;
2936 h_i_repeat NUMBER;
2937
2938 h_i_dim NUMBER;
2939
2940 h_sql VARCHAR2(2000);
2941
2942 h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
2943 h_num_bind_vars NUMBER;
2944
2945 BEGIN
2946
2947 h_num_combinations := 1;
2948 FOR h_i_family IN 0 .. x_max_family_index LOOP
2949 h_num_combinations := h_num_combinations * x_num_dimensions_by_family(h_i_family);
2950 END LOOP;
2951
2952
2953 h_times := 1;
2954 h_repeat := h_num_combinations;
2955
2956 FOR h_i_family IN 0 .. x_max_family_index LOOP
2957 h_i_combination := 0;
2958
2959 h_repeat := h_repeat / x_num_dimensions_by_family(h_i_family);
2960 FOR h_i_times IN 1 .. h_times LOOP
2961 FOR h_i_dim IN 0 .. x_num_dimensions_by_family(h_i_family) - 1 LOOP
2962 FOR h_i_repeat IN 1 .. h_repeat LOOP
2963 h_sql := 'INSERT /*+ append */ INTO BSC_TMP_TAB_COM (TAB_ID, COM_INDEX, FAMILY_INDEX, DIM_INDEX)'||
2964 ' VALUES (:1, :2, :3, :4)';
2965 h_bind_vars_values.delete;
2966 h_bind_vars_values(1) := x_tab_id;
2967 h_bind_vars_values(2) := h_i_combination;
2968 h_bind_vars_values(3) := h_i_family;
2969 h_bind_vars_values(4) := h_i_dim;
2970 h_num_bind_vars := 4;
2971 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
2972 commit;
2973
2974 h_i_combination := h_i_combination + 1;
2975 END LOOP;
2976 END LOOP;
2977 END LOOP;
2978
2979 h_times := h_times * x_num_dimensions_by_family(h_i_family);
2980 END LOOP;
2981
2982 RETURN TRUE;
2983
2984 EXCEPTION
2985 WHEN OTHERS THEN
2986 BSC_MESSAGE.Add(x_message => SQLERRM,
2987 x_source => 'BSC_UPDATE_COLOR.Insert_Tab_Combinations');
2988
2989
2990 RETURN FALSE;
2991
2992 END Insert_Tab_Combinations;
2993
2994 /*===========================================================================+
2995 |
2996 | Name: Get_KPI_Property_Value
2997 |
2998 | Description: This function return the property vaue for a given kpi and
2999 | property_code
3000 |
3001 | Returns: It return the property value
3002 | Notes: Bug #3236356
3003 |
3004 +============================================================================*/
3005 FUNCTION Get_KPI_Property_Value(
3006 x_indicator NUMBER,
3007 x_property_code VARCHAR2,
3008 x_default_value NUMBER
3009 ) RETURN NUMBER is
3010
3011 l_property_value NUMBER;
3012
3013 CURSOR c_Kpi_Property_Value IS
3014 SELECT PROPERTY_VALUE
3015 FROM BSC_KPI_PROPERTIES
3016 WHERE INDICATOR = x_indicator
3017 AND PROPERTY_CODE = x_property_code;
3018
3019 BEGIN
3020
3021 OPEN c_Kpi_Property_Value;
3022 FETCH c_Kpi_Property_Value INTO l_property_value;
3023 IF c_Kpi_Property_Value%NOTFOUND THEN
3024 l_property_value := x_default_value;
3025 END IF;
3026 CLOSE c_Kpi_Property_Value;
3027
3028 RETURN l_property_value;
3029
3030 END Get_KPI_Property_Value;
3031
3032
3033 /* Once the KPI colors are calculated and stored in BSC_SYS_KPI_COLORS,
3034 * this API will calculate the Objective color based on the rollup type.
3035 * The objective color will be stored in BSC_SYS_OBJECTIVE_COLORS.
3036 * Rollup type can be one of: BEST, WORST, MOST_FREQUENT, WEIGHTED_AVERAGE,
3037 * DEFAULT_KPI. For Simulation Objective, the color will be based on the
3038 * default (color) node as of today.
3039 */
3040 PROCEDURE Calculate_Objective_Color (
3041 p_objective_color_rec IN BSC_UPDATE_COLOR.t_objective_color_rec
3042 ,x_kpi_measure_id OUT NOCOPY NUMBER
3043 ,x_color_flag OUT NOCOPY BOOLEAN
3044 ) IS
3045
3046 l_rollup_type bsc_kpis_b.color_rollup_type%TYPE;
3047 e_unexpected_error EXCEPTION;
3048
3049 BEGIN
3050
3051 l_rollup_type := BSC_COLOR_CALC_UTIL.Get_Obj_Color_Rollup_Type(p_objective_color_rec.objective_id);
3052
3053 IF l_rollup_type IS NOT NULL THEN
3054 IF l_rollup_type = BSC_COLOR_CALC_UTIL.DEFAULT_KPI THEN
3055 BSC_COLOR_CALC_UTIL.Calc_Obj_Color_By_Default_Kpi( p_objective_color_rec => p_objective_color_rec
3056 , x_kpi_measure_id => x_kpi_measure_id
3057 , x_color_flag => x_color_flag
3058 );
3059 ELSIF l_rollup_type = BSC_COLOR_CALC_UTIL.BEST OR l_rollup_type = BSC_COLOR_CALC_UTIL.WORST OR l_rollup_type = BSC_COLOR_CALC_UTIL.MOST_FREQUENT THEN
3060 BSC_COLOR_CALC_UTIL.Calc_Obj_Color_By_Single_Kpi( p_objective_color_rec => p_objective_color_rec
3061 , p_rollup_type => l_rollup_type
3062 , x_kpi_measure_id => x_kpi_measure_id
3063 , x_color_flag => x_color_flag
3064 );
3065 ELSIF l_rollup_type = BSC_COLOR_CALC_UTIL.WEIGHTED_AVERAGE THEN
3066 x_color_flag := BSC_COLOR_CALC_UTIL.Calc_Obj_Color_By_Weights( p_objective_color_rec => p_objective_color_rec
3067 );
3068 END IF;
3069 END IF;
3070
3071 --RETURN TRUE;
3072
3073 EXCEPTION
3074 WHEN e_unexpected_error THEN
3075 ROLLBACK;
3076 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_KPICOLOR_FAILED'),
3077 x_source => 'BSC_UPDATE_COLOR.Calculate_Objective_Color');
3078 x_color_flag := false;
3079 x_kpi_measure_id := -1;
3080 --RETURN FALSE;
3081 WHEN OTHERS THEN
3082 ROLLBACK;
3083 BSC_MESSAGE.Add(x_message => SQLERRM,
3084 x_source => 'BSC_UPDATE_COLOR.Calculate_Objective_Color');
3085 x_color_flag := false;
3086 x_kpi_measure_id := -1;
3087 --RETURN FALSE;
3088 END Calculate_Objective_Color;
3089
3090 FUNCTION calculate_kpi_trend_icon (
3091 x_tab_id IN NUMBER
3092 , x_indicator IN NUMBER
3093 , x_measure_id IN NUMBER
3094 , x_color_method IN NUMBER
3095 , x_calc_obj_trend IN BOOLEAN
3096 , x_not_pl_not_initiative IN BOOLEAN
3097 ) RETURN BOOLEAN
3098 IS
3099 CURSOR c_dim_comb IS
3100 SELECT DISTINCT
3101 dim_level1 || dim_level2 || dim_level3 || dim_level4 || dim_level5 || dim_level6 || dim_level7 || dim_level8 dim_comb,
3102 dim_level1, dim_level2, dim_level3, dim_level4, dim_level5, dim_level6, dim_level7, dim_level8,
3103 period_id, actual_data, budget_data, kpi_color, kpi_trend
3104 FROM bsc_sys_kpi_colors
3105 WHERE indicator = x_indicator
3106 AND tab_id = x_tab_id
3107 AND kpi_measure_id = x_measure_id
3108 ORDER BY dim_comb,period_id;
3109
3110 CURSOR c_obj_dim_comb(cp_dim_comb VARCHAR2, cp_period_id NUMBER) IS
3111 SELECT DISTINCT
3112 dim_level1 || dim_level2 || dim_level3 || dim_level4 || dim_level5 || dim_level6 || dim_level7 || dim_level8 dim_comb,
3113 period_id, obj_color, obj_trend
3114 FROM bsc_sys_objective_colors
3115 WHERE indicator = x_indicator
3116 AND tab_id = x_tab_id
3117 AND dim_level1 || dim_level2 || dim_level3 || dim_level4 || dim_level5 || dim_level6 || dim_level7 || dim_level8 = cp_dim_comb
3118 AND period_id = cp_period_id
3119 ORDER BY dim_comb, period_id;
3120 l_dim_comb c_dim_comb%ROWTYPE;
3121 l_prev_period NUMBER;
3122 l_prev_value NUMBER;
3123 l_prev_dim_levels VARCHAR2(240);
3124 l_trendflag NUMBER;
3125 l_obj_dimcomb VARCHAR2(240);
3126 l_obj_periodid NUMBER;
3127 l_obj_color NUMBER;
3128 l_obj_trend NUMBER;
3129 l_calc_trend BOOLEAN := false;
3130 l_rollup_type VARCHAR2(240);
3131 h_bind_vars_values_n BSC_UPDATE_UTIL.t_array_of_number;
3132 h_num_bind_vars_n NUMBER;
3133 h_sql VARCHAR2(3000);
3134 h_obj_bind_vars_values_n BSC_UPDATE_UTIL.t_array_of_number;
3135 h_obj_num_bind_vars_n NUMBER;
3136 h_obj_sql VARCHAR2(3000);
3137 l_kpi_dim_props BSC_UPDATE_UTIL.t_kpi_dim_props_rec;
3138 l_color_by_total bsc_kpi_measure_props.color_by_total%TYPE;
3139 l_comparison BOOLEAN := false;
3140 l_values_obtained BOOLEAN := false;
3141
3142 BEGIN
3143
3144 --BugFix 6142563
3145 IF is_ytd_default_calc( p_indicator => x_indicator
3146 , p_kpi_measure_id => x_measure_id) THEN
3147 RETURN TRUE;
3148 END IF;
3149 l_prev_period := -999;
3150 l_prev_dim_levels :='xxxxxxxx';
3151 l_rollup_type := BSC_COLOR_CALC_UTIL.Get_Obj_Color_Rollup_Type(x_indicator);
3152 l_values_obtained := false;
3153
3154 FOR l_dim_comb IN c_dim_comb LOOP
3155 IF( (l_prev_period=-999) OR (l_prev_dim_levels<>l_dim_comb.dim_comb)
3156 OR (l_dim_comb.period_id<=l_prev_period) OR (l_dim_comb.actual_data IS NULL)
3157 OR (l_prev_value IS NULL)) THEN
3158 l_trendflag := 5;
3159 ELSE
3160 l_trendflag := get_trend_flag(p_color_method => x_color_method
3161 ,p_actual => l_dim_comb.actual_data
3162 ,p_prior => l_prev_value);
3163 END IF;
3164 --BugFix 6000042 Trend for Comparison Mode
3165 IF ( (l_dim_comb.kpi_trend IS NOT NULL) AND (x_not_pl_not_initiative)
3166 AND (NOT l_values_obtained)) THEN
3167 BSC_UPDATE_UTIL.Get_Kpi_Dim_Props ( p_objective_id => x_indicator
3168 , p_kpi_measure_id => x_measure_id
3169 , x_dim_props_rec => l_kpi_dim_props
3170 );
3171 l_color_by_total := BSC_UPDATE_UTIL.Get_Color_By_Total ( p_objective_id => x_indicator
3172 , p_kpi_measure_id => x_measure_id
3173 );
3174
3175 IF ((l_kpi_dim_props.comp_level_pk_col IS NOT NULL) AND (l_color_by_total = 0)) THEN
3176 l_comparison := true;
3177 END IF;
3178 l_values_obtained := true;
3179
3180 END IF;
3181
3182 IF (NOT l_comparison) THEN
3183
3184 h_sql := 'UPDATE bsc_sys_kpi_colors'||
3185 ' SET kpi_trend = '||l_trendflag||
3186 ' WHERE indicator =:1 '||
3187 ' AND tab_id = :2 '||
3188 ' AND kpi_measure_id = :3 '||
3189 ' AND period_id = :4 '||
3190 ' AND dim_level1 || dim_level2 || dim_level3 || dim_level4 || dim_level5 || dim_level6 || dim_level7 || dim_level8 = :5';
3191
3192 h_bind_vars_values_n.delete;
3193 h_bind_vars_values_n(1) := x_indicator;
3194 h_bind_vars_values_n(2) := x_tab_id;
3195 h_bind_vars_values_n(3) := x_measure_id;
3196 h_bind_vars_values_n(4) := l_dim_comb.period_id;
3197 h_bind_vars_values_n(5) := l_dim_comb.dim_comb;
3198 h_num_bind_vars_n := 5;
3199 BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values_n, h_num_bind_vars_n);
3200
3201 COMMIT;
3202 END IF;
3203
3204
3205 IF c_obj_dim_comb%ISOPEN THEN
3206 CLOSE c_obj_dim_comb;
3207 END IF;
3208
3209 IF l_rollup_type = BSC_COLOR_CALC_UTIL.DEFAULT_KPI THEN
3210 l_calc_trend := x_calc_obj_trend;
3211
3212 IF (l_comparison) THEN
3213 l_trendflag := l_dim_comb.kpi_trend;
3214 END IF;
3215
3216 ELSE
3217
3218 OPEN c_obj_dim_comb(l_dim_comb.dim_comb , l_dim_comb.period_id);
3219 FETCH c_obj_dim_comb INTO l_obj_dimcomb, l_obj_periodid, l_obj_color, l_obj_trend;
3220 IF c_obj_dim_comb%NOTFOUND THEN
3221 RETURN NULL;
3222 END IF;
3223 CLOSE c_obj_dim_comb;
3224
3225 IF l_obj_color=l_dim_comb.kpi_color AND l_obj_trend IS NULL THEN
3226 l_calc_trend := true;
3227 ELSE
3228 l_calc_trend := false;
3229 END IF;
3230
3231 IF ((l_comparison) AND (l_calc_trend) AND (l_dim_comb.kpi_trend IS NOT NULL)) THEN
3232 l_trendflag := l_dim_comb.kpi_trend;
3233 END IF;
3234 END IF;
3235 --BugFix 6137542
3236 IF l_rollup_type = BSC_COLOR_CALC_UTIL.WEIGHTED_AVERAGE THEN
3237 l_calc_trend := false;
3238 END IF;
3239
3240 IF l_calc_trend THEN
3241
3242 h_obj_sql := 'UPDATE bsc_sys_objective_colors'||
3243 ' SET obj_trend = '||l_trendflag||
3244 ' WHERE indicator =:1 '||
3245 ' AND tab_id = :2 '||
3246 ' AND period_id = :3 '||
3247 ' AND dim_level1 || dim_level2 || dim_level3 || dim_level4 || dim_level5 || dim_level6 || dim_level7 || dim_level8 = :4';
3248 h_obj_bind_vars_values_n.delete;
3249 h_obj_bind_vars_values_n(1) := x_indicator;
3250 h_obj_bind_vars_values_n(2) := x_tab_id;
3251 h_obj_bind_vars_values_n(3) := l_dim_comb.period_id;
3252 h_obj_bind_vars_values_n(4) := l_dim_comb.dim_comb;
3253 h_obj_num_bind_vars_n := 4;
3254 BSC_UPDATE_UTIL.Execute_Immediate(h_obj_sql, h_obj_bind_vars_values_n, h_obj_num_bind_vars_n);
3255
3256 COMMIT;
3257 END IF;
3258
3259 l_prev_value := l_dim_comb.actual_data;
3260 l_prev_period := l_dim_comb.period_id;
3261 l_prev_dim_levels := l_dim_comb.dim_comb;
3262
3263 END LOOP;
3264
3265 COMMIT;
3266
3267 RETURN TRUE;
3268
3269 EXCEPTION
3270 WHEN OTHERS THEN
3271 ROLLBACK;
3272 BSC_MESSAGE.Add(x_message => SQLERRM,
3273 x_source => 'BSC_UPDATE_COLOR.calculate_kpi_trend_icon');
3274 RETURN FALSE;
3275 END calculate_kpi_trend_icon;
3276
3277 END BSC_UPDATE_COLOR;