[Home] [Help]
PACKAGE BODY: APPS.BSC_CALCULATIONS_PUB
Source
1 PACKAGE BODY BSC_CALCULATIONS_PUB AS
2 /* $Header: BSCPCLCB.pls 120.2.12000000.1 2007/07/17 07:43:45 appldev noship $ */
3 /*
4 +======================================================================================+
5 | Copyright (c) 2001 Oracle Corporation, Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +======================================================================================+
8 | FILENAME |
9 | BSCPCLCB.pls |
10 | |
11 | Creation Date: |
12 | December 28, 2006 |
13 | |
14 | Creator: |
15 | Pradeep Kishore Somesula |
16 | |
17 | Description: |
18 | Public Specs version. |
19 | This package handles calculations |
20 | 10/04/2007 Bug#5968033 psomesul - CALCULATION IS NOT REFLECTING FOR THE SHARED |
21 | OBJECTIVE/SCORECARD |
22 +======================================================================================+
23 */
24
25 PROCEDURE save_obj_calculations(
26 p_obj_id IN NUMBER
27 , p_params IN VARCHAR2
28 , p_ytd_as_default IN VARCHAR2
29 , p_commit IN VARCHAR2 := FND_API.G_FALSE
30 , x_return_status OUT NOCOPY VARCHAR2
31 , x_msg_count OUT NOCOPY NUMBER
32 , x_msg_data OUT NOCOPY VARCHAR2
33 )
34 IS
35 l_calc_recs BSC_UTILITY.varchar_tabletype;
36 l_rec_cnt NUMBER;
37 l_calc_props BSC_UTILITY.varchar_tabletype;
38 l_prop_cnt NUMBER;
39 l_calc_id NUMBER;
40 l_is_sel VARCHAR2(3);
41 l_def_val NUMBER;
42 l_cnt NUMBER;
43 l_user_level0 NUMBER;
44
45 CURSOR c_def_val IS
46 SELECT default_value
47 FROM bsc_kpi_calculations
48 WHERE indicator = p_obj_id
49 AND calculation_id = l_calc_id;
50
51 CURSOR c_shared_objectives IS
52 SELECT indicator
53 FROM bsc_kpis_b
54 WHERE source_indicator = p_obj_id
55 AND share_flag = 2
56 AND prototype_flag <> 2;
57
58 BEGIN
59
60 FND_MSG_PUB.Initialize;
61 x_return_status := FND_API.G_RET_STS_SUCCESS;
62
63 IF (p_obj_id IS NULL OR p_params IS NULL) THEN
64 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
65 x_msg_data := x_msg_data || ' INSUFFICIENT DATA ';
66 RETURN;
67 ELSE
68 BSC_UTILITY.Parse_String(
69 p_List => p_params,
70 p_Separator => ';',
71 p_List_Data => l_calc_recs,
72 p_List_number => l_rec_cnt
73 );
74 SAVEPOINT BSCPCLCB_save_obj_calculations;
75
76 FOR i IN 1..l_rec_cnt LOOP
77 BSC_UTILITY.Parse_String(
78 p_List => l_calc_recs(i),
79 p_Separator => ',',
80 p_List_Data => l_calc_props,
81 p_List_number => l_prop_cnt
82 );
83
84
85 IF (l_prop_cnt = 2) THEN
86 l_calc_id := TO_NUMBER(l_calc_props(1));
87 l_is_sel := l_calc_props(2);
88
89
90
91 l_def_val := 0;
92 l_cnt := 0;
93 l_user_level0 := 2;
94
95 SELECT count(0) INTO l_cnt
96 FROM bsc_kpi_calculations
97 WHERE indicator = p_obj_id
98 AND calculation_id = l_calc_id;
99
100
101 IF (l_cnt = 1) THEN
102 IF c_def_val%ISOPEN THEN
103 CLOSE c_def_val;
104 END IF;
105
106 FOR cd IN c_def_val LOOP
107 l_def_val := cd.default_value;
108 END LOOP;
109
110
111 IF (l_def_val = 1) THEN
112 IF (l_is_sel = 'Y') THEN
113 l_user_level0 := 1;
114 l_def_val := 1;
115 ELSE
116 l_user_level0 := 0;
117 l_def_val := 0;
118 END IF;
119
120 ELSE
121 IF (l_is_sel = 'Y') THEN
122 l_user_level0 := 2;
123 l_def_val := 0;
124 ELSE
125 l_user_level0 := 0;
126 l_def_val := 0;
127 END IF;
128
129 END IF;
130
131 BSC_CALCULATIONS_PVT.delete_objective_calculation (
132 p_indicator => p_obj_id
133 ,p_calculation_id => l_calc_id
134 ,x_return_status => x_return_status
135 ,x_msg_count => x_msg_count
136 ,x_msg_data => x_msg_data
137 );
138 --DELETE bsc_kpi_calculations WHERE indicator = p_obj_id AND calculation_id = l_calc_id;
139
140 BSC_CALCULATIONS_PVT.insert_objective_calculation (
141 p_indicator => p_obj_id
142 ,p_calculation_id => l_calc_id
143 ,p_user_level0 => l_user_level0
144 ,p_user_level1 => l_user_level0
145 ,p_user_level1_default => l_user_level0
146 ,p_user_level2 => NULL
147 ,p_user_level2_default => NULL
148 ,p_default_value => l_def_val
149 ,x_return_status => x_return_status
150 ,x_msg_count => x_msg_count
151 ,x_msg_data => x_msg_data
152 );
153 -- INSERT INTO bsc_kpi_calculations(INDICATOR,CALCULATION_ID,USER_LEVEL0,USER_LEVEL1,USER_LEVEL1_DEFAULT,USER_LEVEL2,USER_LEVEL2_DEFAULT,DEFAULT_VALUE)
154 -- VALUES(p_obj_id,l_calc_id, l_user_level0,l_user_level0,NULL,NULL,NULL,l_def_val);
155
156 -- Cascade the changes to shared objectives also.
157 FOR shared_ind_cd IN c_shared_objectives LOOP
158 BSC_CALCULATIONS_PVT.delete_objective_calculation (
159 p_indicator => shared_ind_cd.indicator
160 ,p_calculation_id => l_calc_id
161 ,x_return_status => x_return_status
162 ,x_msg_count => x_msg_count
163 ,x_msg_data => x_msg_data
164 );
165
166 BSC_CALCULATIONS_PVT.insert_objective_calculation (
167 p_indicator => shared_ind_cd.indicator
168 ,p_calculation_id => l_calc_id
169 ,p_user_level0 => l_user_level0
170 ,p_user_level1 => l_user_level0
171 ,p_user_level1_default => l_user_level0
172 ,p_user_level2 => NULL
173 ,p_user_level2_default => NULL
174 ,p_default_value => l_def_val
175 ,x_return_status => x_return_status
176 ,x_msg_count => x_msg_count
177 ,x_msg_data => x_msg_data
178 );
179
180
181 END LOOP;
182
183 END IF;
184 END IF;
185 END LOOP;
186
187 IF (p_ytd_as_default IS NOT NULL ) THEN
188 save_ytd_as_default_calc(
189 p_obj_id => p_obj_id
190 ,p_ytd_as_default => p_ytd_as_default
191 ,p_commit => p_commit
192 ,x_return_status => x_return_status
193 ,x_msg_count => x_msg_count
194 ,x_msg_data => x_msg_data
195 );
196
197 IF (p_commit = FND_API.G_TRUE) THEN
198 COMMIT;
199 END IF;
200 x_return_status := FND_API.G_RET_STS_SUCCESS;
201 END IF;
202
203 END IF;
204
205 EXCEPTION
206
207 WHEN FND_API.G_EXC_ERROR THEN
208 ROLLBACK TO BSCPCLCB_save_obj_calculations;
209 FND_MSG_PUB.Count_And_Get
210 ( p_encoded => FND_API.G_FALSE
211 , p_count => x_msg_count
212 , p_data => x_msg_data
213 );
214 x_return_status := FND_API.G_RET_STS_ERROR;
215 RAISE;
216
217 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
218 ROLLBACK TO BSCPCLCB_save_obj_calculations;
219 FND_MSG_PUB.Count_And_Get
220 ( p_encoded => FND_API.G_FALSE
221 , p_count => x_msg_count
222 , p_data => x_msg_data
223 );
224 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
225 RAISE;
226
227 WHEN NO_DATA_FOUND THEN
228 ROLLBACK TO BSCPCLCB_save_obj_calculations;
229 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
230 IF (x_msg_data IS NOT NULL) THEN
231 x_msg_data := x_msg_data||' -> BSC_CALCULATIONS_PUB.save_obj_calculations ';
232 ELSE
233 x_msg_data := SQLERRM||' at BSC_CALCULATIONS_PUB.save_obj_calculations ';
234 END IF;
235 RAISE;
236
237 WHEN OTHERS THEN
238 ROLLBACK TO BSCPCLCB_save_obj_calculations;
239 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
240 IF (x_msg_data IS NOT NULL) THEN
241 x_msg_data := x_msg_data||' -> BSC_CALCULATIONS_PUB.save_obj_calculations ';
242 ELSE
243 x_msg_data := SQLERRM||' at BSC_CALCULATIONS_PUB.save_obj_calculations ';
244 END IF;
245 RAISE;
246
247 END save_obj_calculations;
248
249
250
251
252 PROCEDURE save_ytd_as_default_calc(
253 p_obj_id IN NUMBER
254 , p_ytd_as_default IN VARCHAR2
255 , p_commit IN VARCHAR2 := FND_API.G_FALSE
256 , x_return_status OUT NOCOPY VARCHAR2
257 , x_msg_count OUT NOCOPY NUMBER
258 , x_msg_data OUT NOCOPY VARCHAR2
259 ) IS
260
261 CURSOR c_shared_objectives IS
262 SELECT indicator
263 FROM bsc_kpis_b
264 WHERE source_indicator = p_obj_id
265 AND share_flag = 2
266 AND prototype_flag <> 2;
267
268 l_user_level0 NUMBER;
269 l_user_level1 NUMBER;
270 l_def_value NUMBER;
271
272 BEGIN
273
274 IF (p_obj_id IS NOT NULL AND p_ytd_as_default IS NOT NULL) THEN
275 IF (p_ytd_as_default = 'Y') THEN
276 l_user_level0 := 1;
277 l_user_level1 := 1;
278 l_def_value := 1;
279
280 UPDATE bsc_kpi_calculations
281 SET USER_LEVEL0 = l_user_level0,
282 USER_LEVEL1 = l_user_level1,
283 DEFAULT_VALUE = l_def_value
284 WHERE indicator = p_obj_id AND calculation_id = 2;
285
286 ELSE
287 l_def_value := 0;
288 UPDATE bsc_kpi_calculations
289 SET DEFAULT_VALUE = l_def_value
290 WHERE indicator = p_obj_id AND calculation_id = 2;
291
292 END IF;
293 BSC_DESIGNER_PVT.ActionFlag_change(
294 x_indicator => p_obj_id,
295 x_newflag => BSC_DESIGNER_PVT.G_ActionFlag.GAA_Color
296 );
297
298 FOR cd IN c_shared_objectives LOOP
299 IF (cd.indicator IS NOT NULL) THEN
300 IF (p_ytd_as_default = 'Y') THEN
301 l_def_value := 1;
302
303 UPDATE bsc_kpi_calculations
304 SET DEFAULT_VALUE = l_def_value
305 WHERE indicator = cd.indicator AND calculation_id = 2;
306
307 ELSE
308 l_def_value := 0;
309
310 UPDATE bsc_kpi_calculations
311 SET DEFAULT_VALUE = l_def_value
312 WHERE indicator = cd.indicator AND calculation_id = 2;
313
314 END IF;
315 END IF;
316 END LOOP;
317
318 END IF;
319
320 EXCEPTION
321 WHEN FND_API.G_EXC_ERROR THEN
322 ROLLBACK TO BSCPCLCB_save_obj_calculations;
323 FND_MSG_PUB.Count_And_Get
324 ( p_encoded => FND_API.G_FALSE
325 , p_count => x_msg_count
326 , p_data => x_msg_data
327 );
328 x_return_status := FND_API.G_RET_STS_ERROR;
329 RAISE;
330
331 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
332 ROLLBACK TO BSCPCLCB_save_obj_calculations;
333 FND_MSG_PUB.Count_And_Get
334 ( p_encoded => FND_API.G_FALSE
335 , p_count => x_msg_count
336 , p_data => x_msg_data
337 );
338 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
339 RAISE;
340
341 WHEN NO_DATA_FOUND THEN
342 ROLLBACK TO BSCPCLCB_save_obj_calculations;
343 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
344 IF (x_msg_data IS NOT NULL) THEN
345 x_msg_data := x_msg_data||' -> BSC_CALCULATIONS_PUB.save_ytd_as_default_calc ';
346 ELSE
347 x_msg_data := SQLERRM||' at BSC_CALCULATIONS_PUB.save_ytd_as_default_calc ';
348 END IF;
349 RAISE;
350
351 WHEN OTHERS THEN
352 ROLLBACK TO BSCPCLCB_save_obj_calculations;
353 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
354 IF (x_msg_data IS NOT NULL) THEN
355 x_msg_data := x_msg_data||' -> BSC_CALCULATIONS_PUB.save_ytd_as_default_calc ';
356 ELSE
357 x_msg_data := SQLERRM||' at BSC_CALCULATIONS_PUB.save_ytd_as_default_calc ';
358 END IF;
359 RAISE;
360
361 END save_ytd_as_default_calc;
362
363
364
365 PROCEDURE save_user_wizard_calculations
366 (p_tab_id IN NUMBER
367 ,p_obj_id IN NUMBER
368 ,p_calcs_list IN VARCHAR2
369 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
370 ,x_return_status OUT NOCOPY VARCHAR2
371 ,x_msg_count OUT NOCOPY NUMBER
372 ,x_msg_data OUT NOCOPY VARCHAR2
373 ) IS
374
375 l_calc_recs BSC_UTILITY.varchar_tabletype;
376 l_calc_props BSC_UTILITY.varchar_tabletype;
377 l_calc_recs_cnt NUMBER;
378 l_calc_props_cnt NUMBER;
379 l_calc_rec VARCHAR2(500);
380 ulv0 NUMBER;
381 ulv1 NUMBER;
382 ulvd1 NUMBER;
383 ulvd2 NUMBER;
384 l_calc_id NUMBER;
385 l_calc_enabled VARCHAR2(10);
386
387
388 CURSOR c_calcs (cp_calc_id NUMBER) IS
389 SELECT * FROM bsc_kpi_calculations where indicator = p_obj_id AND calculation_id = cp_calc_id;
390
391 CURSOR c_all_calcs IS
392 SELECT * FROM bsc_kpi_calculations where indicator = p_obj_id;
393
394
395 BEGIN
396
397 FND_MSG_PUB.Initialize;
398 x_return_status := FND_API.G_RET_STS_SUCCESS;
399
400
401 IF (p_obj_id is null OR p_tab_id IS NULL) THEN
402 RETURN;
403 END IF;
404
405
406 SAVEPOINT bscpclcb_savepoint_save_calcs;
407
408 BSC_UTILITY.Parse_String (
409 p_List => p_calcs_list,
410 p_Separator => ';',
411 p_List_Data => l_calc_recs,
412 p_List_number => l_calc_recs_cnt
413 );
414
415 FOR i IN 1..l_calc_recs_cnt LOOP
416 IF (l_calc_recs(i) IS NOT NULL) THEN
417
418 BSC_UTILITY.Parse_String (
419 p_List => l_calc_recs(i),
420 p_Separator => ',',
421 p_List_Data => l_calc_props,
422 p_List_number => l_calc_props_cnt
423 );
424
425
426 l_calc_id := TO_NUMBER(RTRIM(LTRIM(l_calc_props(1))));
430 FOR cd IN c_calcs (l_calc_id) LOOP
427 l_calc_enabled := l_calc_props(2);
428
429 IF (l_calc_id IS NOT NULL) THEN
431
432 IF (cd.user_level0 IS NOT NULL) THEN
433 ulv0 := cd.user_level0;
434 ELSE
435 ulv0 := 2;
436 END IF;
437 IF (cd.user_level1_default IS NOT NULL) THEN
438 ulvd1 := cd.user_level1_default;
439 ELSE
440 ulvd1 := ulv0;
441 END IF;
442
443 IF (ulvd1 > ulv0) THEN
444 ulvd1 := ulv0;
445 END IF;
446
447 IF (l_calc_enabled IS NOT NULL AND l_calc_enabled = 'Y') THEN
448 IF (cd.default_value = 1) THEN
449 ulv1 := 1;
450 ELSE
451 ulv1 := 2;
452 END IF;
453 ELSE
454 ulv1 := 0;
455 END IF;
456
457 ulvd2 := ulv1;
458
459 UPDATE bsc_kpi_calculations
460 SET
461 user_level1 = ulv1,
462 user_level1_default = ulvd1,
463 user_level2_default = ulvd2
464 WHERE
465 indicator = p_obj_id
466 AND calculation_id = l_calc_id;
467
468 EXIT;
469 END LOOP;
470 END IF;
471 END IF;
472 END LOOP;
473
474 FOR cd IN c_all_calcs LOOP
475 IF (cd.user_level1 IS NULL OR cd.user_level1_default IS NULL OR cd.user_level2_default IS NULL) THEN
476
477 ulv0 := cd.user_level0;
478 IF (ulv0 IS NULL) THEN
479 ulv0 := 2;
480 END IF;
481
482 IF (cd.user_level1_default IS NOT NULL) THEN
483 ulvd1 := cd.user_level1_default;
484 ELSE
485 ulvd1 := ulv0;
486 END IF;
487
488 IF (ulvd1 > ulv0) THEN
489 ulvd1 := ulv0;
490 END IF;
491
492 IF (cd.user_level1 IS NOT NULL) THEN
493 ulv1 := cd.user_level1;
494 ELSE
495 ulv1 := 0;
496 END IF;
497
498 ulvd2 := ulv1;
499
500 UPDATE bsc_kpi_calculations
501 SET
502 user_level1 = ulv1,
503 user_level1_default = ulvd1,
504 user_level2_default = ulvd2
505 WHERE
506 indicator = p_obj_id
507 AND calculation_id = cd.calculation_id;
508 END IF;
509 END LOOP;
510
511 IF (p_commit = FND_API.G_TRUE) THEN
512 COMMIT;
513 END IF;
514
515 EXCEPTION
516 WHEN FND_API.G_EXC_ERROR THEN
517 ROLLBACK TO bscpclcb_savepoint_save_calcs;
518 FND_MSG_PUB.Count_And_Get
519 ( p_encoded => FND_API.G_FALSE
520 , p_count => x_msg_count
521 , p_data => x_msg_data
522 );
523 x_return_status := FND_API.G_RET_STS_ERROR;
524 RAISE;
525
526 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
527 ROLLBACK TO bscpclcb_savepoint_save_calcs;
528 FND_MSG_PUB.Count_And_Get
529 ( p_encoded => FND_API.G_FALSE
530 , p_count => x_msg_count
531 , p_data => x_msg_data
532 );
533 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
534 RAISE;
535
539 IF (x_msg_data IS NOT NULL) THEN
536 WHEN NO_DATA_FOUND THEN
537 ROLLBACK TO bscpclcb_savepoint_save_calcs;
538 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
540 x_msg_data := x_msg_data||' -> BSC_CALCULATIONS_PUB.save_user_wizard_calculations ';
541 ELSE
542 x_msg_data := SQLERRM||' at BSC_CALCULATIONS_PUB.save_user_wizard_calculations ';
543 END IF;
544
545 RAISE;
546
547 WHEN OTHERS THEN
548 ROLLBACK TO bscpclcb_savepoint_save_calcs;
549 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
550 IF (x_msg_data IS NOT NULL) THEN
551 x_msg_data := x_msg_data||' -> BSC_CALCULATIONS_PUB.save_user_wizard_calculations ';
552 ELSE
553 x_msg_data := SQLERRM||' at BSC_CALCULATIONS_PUB.save_user_wizard_calculations ';
554 END IF;
555 RAISE;
556
557 END save_user_wizard_calculations;
558
559
560
561
562
563
564 FUNCTION is_calculation_default(
565 p_obj_id IN NUMBER
566 ,p_cal_id IN NUMBER
567 ) RETURN VARCHAR2
568 IS
569 l_default NUMBER;
570 l_result VARCHAR2(1);
571 CURSOR c_cal_def IS
572 SELECT default_value INTO l_default
573 FROM bsc_kpi_calculations
574 WHERE indicator = p_obj_id
575 AND calculation_id = p_cal_id;
576 BEGIN
577 l_result := 'N';
578 IF p_obj_id IS NOT NULL AND p_cal_id IS NOT NULL THEN
579 FOR cd IN c_cal_def LOOP
580 IF (cd.default_value = 1) THEN
581 l_result := 'Y';
582 END IF;
583 EXIT;
584 END LOOP;
585 END IF;
586 return l_result;
587 EXCEPTION
588 WHEN OTHERS THEN
589 RETURN 'N';
590 END is_calculation_default;
591
592
593
594
595
596
597 FUNCTION is_YTD_enabled_in_def_measure(
598 p_obj_id IN NUMBER
599 ) RETURN VARCHAR2
600 IS
601 l_def_meas_id NUMBER;
602 l_result VARCHAR2(1);
603 l_cnt NUMBER;
604 BEGIN
605
606 l_result := 'N';
607
608 IF p_obj_id IS NOT NULL THEN
609 l_def_meas_id := BSC_COLOR_CALC_UTIL.Get_Default_Kpi_Measure_Id (p_objective_id => p_obj_id);
610 IF (l_def_meas_id IS NOT NULL) THEN
611 SELECT count(0) INTO l_cnt
612 FROM bsc_sys_dataset_calc
613 WHERE dataset_id = l_def_meas_id
614 AND disabled_calc_id = 2;
615 IF (l_cnt = 0) THEN
616 l_result := 'Y';
617 END IF;
618
619 IF (is_balance_measure(l_def_meas_id) = 'Y') THEN
620 l_result := 'N';
621 END IF;
622
623 END IF;
624 END IF;
625 return l_result;
626
627 EXCEPTION
628 WHEN OTHERS THEN
629 RETURN 'N';
630 END is_YTD_enabled_in_def_measure;
631
632
633 FUNCTION is_balance_measure(
634 p_kpi_measure_id IN NUMBER
635 ) RETURN VARCHAR2
636 IS
637 l_dataset_id bsc_sys_datasets_b.dataset_id%TYPE;
638 BEGIN
639 IF p_kpi_measure_id IS NOT NULL THEN
640 SELECT dataset_id
641 INTO
642 l_dataset_id
643 FROM
644 bsc_kpi_analysis_measures_b
645 WHERE
646 kpi_measure_id = p_kpi_measure_id;
647 IF l_dataset_id IS NOT NULL THEN
648 RETURN is_dataset_balance_type(l_dataset_id);
649 END IF;
650 END IF;
651 RETURN 'N';
652 EXCEPTION
653 WHEN OTHERS THEN
654 RETURN 'N';
655 END is_balance_measure;
656
657 /************************************************************************************
658 -- API name : Is_Dataset_Balance_Type
659 -- Type : Public
660 -- This API will check if any of the measure cols associated with a dataset
661 -- are of balance type. In that case it will return true else false
662 ************************************************************************************/
663 FUNCTION Is_Dataset_Balance_Type(
664 p_dataset_id IN NUMBER
665 ) RETURN VARCHAR2
666 IS
667
668 CURSOR c_data_set IS
669 SELECT measure_col FROM bsc_sys_measures
670 WHERE measure_id IN (SELECT measure_id1 FROM bsc_sys_datasets_b
671 WHERE dataset_id = p_dataset_id
672 UNION
673 SELECT measure_id2 FROM bsc_sys_datasets_b
674 WHERE dataset_id = p_dataset_id);
675
676 CURSOR c_db_measure_cols (p_measure_col VARCHAR2) IS
677 SELECT measure_type
678 FROM bsc_db_measure_cols_vl
679 WHERE measure_col = p_measure_col;
680
681 l_measure_col_formula varchar2(1000);
682 l_measure_col varchar2(100);
683
684 BEGIN
685 IF (p_dataset_id IS NOT NULL) THEN
686 FOR cd IN c_data_set LOOP
687 IF (cd.measure_col IS NOT NULL) THEN
688 l_measure_col_formula := REPLACE (cd.measure_col, ' ');
689 l_measure_col_formula := REPLACE (l_measure_col_formula, '(',',');
690 l_measure_col_formula := REPLACE (l_measure_col_formula, ')',',');
691 l_measure_col_formula := REPLACE (l_measure_col_formula, '+',',');
692 l_measure_col_formula := REPLACE (l_measure_col_formula, '-',',');
693 l_measure_col_formula := REPLACE (l_measure_col_formula, '*',',');
694 l_measure_col_formula := REPLACE (l_measure_col_formula, '/',',');
695
699 FOR cd1 IN c_db_measure_cols (l_measure_col) LOOP
696
697 WHILE (bsc_utility.Is_More(l_measure_col_formula, l_measure_col)) LOOP
698 IF (NOT FALSE) THEN
700 IF (cd1.measure_type = 2) THEN
701 RETURN 'Y';
702 END IF;
703 EXIT;
704 END LOOP;
705 END IF;
706 END LOOP;
707 RETURN 'N';
708 END IF;
709 EXIT;
710 END LOOP;
711 END IF;
712 RETURN NULL;
713 EXCEPTION
714 WHEN OTHERS THEN
715 RETURN 'N';
716 END Is_Dataset_Balance_Type;
717
718 /************************************************************************************
719 -- API name : Is_Calculation_Enabled
720 -- Type : Public
721 -- Checks whether a calculation is enabled for a dataset or not
722 -- As of now it supports only Year to Date. Other calculation conditions
723 -- can be added per requirement
724 ************************************************************************************/
725 FUNCTION Is_Calculation_Enabled(
726 p_dataset_id IN NUMBER
727 ,p_calculation_id IN NUMBER
728 ) RETURN VARCHAR2
729 IS
730 l_Count NUMBER := 0;
731 BEGIN
732 SELECT COUNT(1)
733 INTO
734 l_Count
735 FROM
736 bsc_sys_dataset_calc
737 WHERE
738 dataset_id = p_dataset_id AND
739 disabled_calc_id = p_calculation_id;
740
741 IF l_Count = 1 OR BSC_DATASETS_PUB.Get_DataSet_Source(p_dataset_id) = BSC_BIS_MEASURE_PUB.c_PMF OR
742 (p_calculation_id = 2 AND Is_Dataset_Balance_Type(p_dataset_id) = 'Y')THEN
743 RETURN 'N';
744 END IF;
745
746 RETURN 'Y';
747 EXCEPTION
748 WHEN OTHERS THEN
749 RETURN 'Y';
750 END Is_Calculation_Enabled;
751
752
753
754
755 END BSC_CALCULATIONS_PUB;