[Home] [Help]
PACKAGE BODY: APPS.BSC_ANALYSIS_OPTION_PVT
Source
1 package body BSC_ANALYSIS_OPTION_PVT as
2 /* $Header: BSCVANOB.pls 120.7 2007/04/13 13:07:14 ppandey ship $ */
3 /*
4 +======================================================================================+
5 | Copyright (c) 2001 Oracle Corporation, Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +======================================================================================+
8 | FILENAME |
9 | BSCVANOB.pls |
10 | |
11 | Creation Date: |
12 | October 10, 2001 |
13 | |
14 | Creator: |
15 | Mario-Jair Campos |
16 | |
17 | Description: |
18 | Private Body version. |
19 | This package creates a BSC Analysis Option. |
20 | |
21 | History: |
22 | 05-MAR-2003 ADEULGAO fixed MLS issue bug#2721899 |
23 | changed BSC_KPI_ANALYSIS_OPTIONS_TL to |
24 | BSC_KPI_ANALYSIS_OPTIONS_VL in select statement |
25 | 13-MAY-2003 PWALI Bug #2942895, SQL BIND COMPLIANCE |
26 | |
27 | 08-SEP-2003 kyadamak FIX THE BUG 3124010 |
28 | 14-NOV-2003 PAJOHRI Bug #3248729 |
29 | 17-NOV-2003 wcano Bug #3248729 |
30 | 09-DEC-2003 PAJOHRI Bug #3293895 |
31 | Added new procedures Set_Default_Value & |
32 | Swap_Option_Id |
33 | and modified the procedures Delete_Analysis_Measures |
34 | Delete_Analysis_Options |
35 | 23-DEC-2003 ashankar Bug#3327016 |
36 | Modified the procedure Set_Default_Value |
37 | to update BSC_KPI_ANALYSIS_GROUPS |
38 | 14-JUN-2004 adrao Enh#3540302, added SHORT_NAME column to the Analysis |
39 | Options table. Tracked in Bug#3691035 |
40 | 02-jul-2004 rpenneru Modified for Enhancement#3532517 |
41 | 14-jul-2004 rpenneru Modified for bug#3746564 |
42 | 07-JAN-2005 ashankar Fix for the bug #4099597 |
43 | 20-APR-2005 adrao added API Cascade_Series_Default_Value |
44 | 11-MAY-2005 adrao Removed incremental change during series cascading |
45 | 22-AUG-2005 ashankar Bug#4220400 added the method |
46 | Set_Default_Analysis_Option |
47 | 11-APR-2006 visuri Bug#5151997 Changes for Protoype Flag change during |
48 | update of PMF Measure in Objective |
49 | 31-Jan-2007 akoduri Enh #5679096 Migration of multibar functionality from |
50 | VB to Html |
51 +======================================================================================+
52 */
53 G_PKG_NAME CONSTANT varchar2(30) := 'BSC_ANALYSIS_OPTION_PVT';
54 g_db_object varchar2(30) := null;
55
56
57 TYPE Swap_Ana_Opts_Type IS Record
58 ( p_AnaOpt_Prev_Id NUMBER
59 , p_AnaOpt_Next_Id NUMBER
60 );
61 --==============================================================
62 TYPE Swap_Ana_Opts_Table IS TABLE OF Swap_Ana_Opts_Type INDEX BY BINARY_INTEGER;
63
64 /**************************************************************************************/
65 FUNCTION is_Parent_Exists
66 ( p_kpi_Id NUMBER,
67 p_Parent NUMBER,
68 p_Group NUMBER
69 ) RETURN BOOLEAN IS
70 l_Count NUMBER;
71 BEGIN
72 SELECT COUNT(*) INTO l_Count
73 FROM BSC_KPI_ANALYSIS_OPTIONS_B
74 WHERE Parent_Option_Id = p_Parent
75 AND Analysis_Group_Id = p_Group
76 AND Indicator = p_kpi_Id;
77 IF (l_Count <> 0) THEN
78 RETURN TRUE;
79 ELSE
80 RETURN FALSE;
81 END IF;
82 END is_Parent_Exists;
83 /*********************************************************************************/
84
85 FUNCTION is_not_Child
86 (p_kpi_Id NUMBER,
87 p_Parent NUMBER,
88 p_child NUMBER,
89 p_Group NUMBER
90 ) RETURN BOOLEAN
91 IS
92 l_Count NUMBER;
93 BEGIN
94 SELECT COUNT(*) INTO l_Count
95 FROM BSC_KPI_ANALYSIS_OPTIONS_B
96 WHERE Parent_Option_Id = p_Parent
97 AND OPTION_ID = p_child
98 AND Analysis_Group_Id = p_Group
99 AND Indicator = p_kpi_Id;
100 IF (l_Count = 0) THEN
101 RETURN TRUE;
102 ELSE
103 RETURN FALSE;
104 END IF;
105 END is_not_Child;
106 /**************************************************************************************/
107 FUNCTION is_GrandParent_Exists
108 ( p_kpi_Id NUMBER,
109 p_GrandParent NUMBER,
110 p_Group NUMBER
111 ) RETURN BOOLEAN IS
112 l_Count NUMBER;
113 BEGIN
114 SELECT COUNT(*) INTO l_Count
115 FROM BSC_KPI_ANALYSIS_OPTIONS_B
116 WHERE GrandParent_Option_Id = p_GrandParent
117 AND Analysis_Group_Id = p_Group
118 AND Indicator =p_kpi_Id;
119 IF (l_Count <> 0) THEN
120 RETURN TRUE;
121 ELSE
122 RETURN FALSE;
123 END IF;
124 END is_GrandParent_Exists;
125 /***************************************************************************
126 Name :- get_number_of_child
127 This fucntion will return the number of child for the parent.
128 /**************************************************************************/
129 FUNCTION get_number_of_child
130 ( p_Kpi_id IN NUMBER--BSC_KPIS_B.indicator%TYPE
131 , p_group_count IN NUMBER
132 , p_Anal_Opt_Tbl IN BSC_ANALYSIS_OPTION_PUB.Bsc_Anal_Opt_Tbl_Type
133 , p_Anal_Opt_Comb_Tbl IN BSC_ANALYSIS_OPTION_PUB.Anal_Opt_Comb_Num_Tbl_Type
134 )RETURN NUMBER IS
135 l_count NUMBER;
136 BEGIN
137 IF (p_Anal_Opt_Tbl(p_group_count + 1).Bsc_dependency_flag = 1) THEN
138 IF((p_group_count = 1)AND(p_Anal_Opt_Tbl(p_group_count).Bsc_dependency_flag = 1))THEN
139 SELECT COUNT(0)
140 INTO l_count
141 FROM BSC_KPI_ANALYSIS_OPTIONS_B
142 WHERE Indicator = p_Kpi_id
143 AND Analysis_Group_Id = p_group_count + 1
144 AND Parent_Option_Id = p_Anal_Opt_Comb_Tbl(p_group_count)
145 AND Grandparent_Option_Id = p_Anal_Opt_Comb_Tbl(p_group_count - 1);
146 ELSIF((p_group_count = 1)AND(p_Anal_Opt_Tbl(p_group_count).Bsc_dependency_flag = 0)) THEN
147 SELECT COUNT(0)
148 INTO l_count
149 FROM BSC_KPI_ANALYSIS_OPTIONS_B
150 WHERE Indicator = p_Kpi_id
151 AND Analysis_Group_Id = p_group_count + 1
152 AND Parent_Option_Id = p_Anal_Opt_Comb_Tbl(p_group_count);
153 ELSE
154 SELECT COUNT(0)
155 INTO l_count
156 FROM BSC_KPI_ANALYSIS_OPTIONS_B
157 WHERE Indicator = p_Kpi_id
158 AND Analysis_Group_Id = p_group_count + 1
159 AND Parent_Option_Id = p_Anal_Opt_Comb_Tbl(p_group_count);
160 END IF;
161 RETURN l_count;
162 ELSE
163 RETURN 0;
164 END IF;
165 END get_number_of_child;
166
167 /*******************************************************************************/
168 FUNCTION get_parent_level_id
169 ( p_Kpi_id IN BSC_KPIS_B.indicator%TYPE
170 , p_Group_id IN BSC_KPI_ANALYSIS_OPTIONS_B.Analysis_Group_Id%TYPE
171 , p_Option_id IN BSC_KPI_ANALYSIS_OPTIONS_B.Option_Id%TYPE
172 ) RETURN NUMBER IS
173 l_parent_option BSC_KPI_ANALYSIS_OPTIONS_B.Parent_Option_Id%TYPE;
174 BEGIN
175 SELECT PARENT_OPTION_ID
176 INTO l_parent_option
177 FROM BSC_KPI_ANALYSIS_OPTIONS_B
178 WHERE Indicator = p_Kpi_id
179 AND Analysis_Group_Id = p_Group_id
180 AND Option_Id = p_Option_id;
181
182 RETURN l_parent_option;
183 END get_parent_level_id;
184
185 /*******************************************************************************/
186 FUNCTION is_custom_kpi
187 ( p_Kpi_id IN BSC_KPIS_B.indicator%TYPE
188 , p_Kpi_Name OUT NOCOPY BSC_KPIS_VL.NAME%TYPE
189 ) RETURN BOOLEAN IS
190 l_Kpi_ShortName VARCHAR2(50);
191 l_Kpi_Name BSC_KPIS_VL.NAME%TYPE;
192
193 CURSOR c_kpis IS
194 SELECT name, short_name
195 FROM BSC_KPIS_VL WHERE Indicator = p_Kpi_id;
196 BEGIN
197 IF (c_kpis%ISOPEN) THEN
198 CLOSE c_kpis;
199 END IF;
200
201 OPEN c_kpis;
202 FETCH c_kpis INTO l_Kpi_Name,l_Kpi_ShortName;
203 CLOSE c_kpis;
204
205 p_Kpi_Name := l_Kpi_Name;
206 IF l_Kpi_ShortName IS NOT NULL THEN
207 RETURN TRUE;
208 END IF;
209 RETURN FALSE;
210 EXCEPTION
211 WHEN OTHERS THEN
212 IF (c_kpis%ISOPEN) THEN
213 CLOSE c_kpis;
214 END IF;
215 RETURN FALSE;
216 END is_custom_kpi;
217
218 /*******************************************************************************/
219 PROCEDURE Store_Anal_Opt_Grp_Count
220 ( p_kpi_id IN NUMBER
221 , x_Anal_Opt_Tbl IN OUT NOCOPY BSC_ANALYSIS_OPTION_PUB.Bsc_Anal_Opt_Tbl_Type
222 ) IS
223 l_count NUMBER ;
224
225 CURSOR c_anal_grp_opt_count IS
226 SELECT analysis_group_id
227 , COUNT(option_id) option_count
228 FROM BSC_KPI_ANALYSIS_OPTIONS_B
229 WHERE indicator = p_Kpi_Id
230 GROUP BY analysis_group_id;
231 BEGIN
232 l_count := 0;
233 FOR cd IN c_anal_grp_opt_count LOOP
234 x_Anal_Opt_Tbl(l_count).Bsc_analysis_group_id := cd.analysis_group_id;
235 x_Anal_Opt_Tbl(l_count).Bsc_no_option_id := cd.option_count;
236 l_count := l_count +1;
237 END LOOP;
238 END store_anal_opt_grp_count;
239
240 /*******************************************************************************/
241 PROCEDURE Set_Default_Value
242 ( p_Kpi_Id NUMBER
243 , p_group_Id NUMBER
244 , p_parent_option_Id NUMBER
245 , p_grand_parent_option_Id NUMBER
246 , p_option_Id NUMBER
247 ) IS
248 l_Dependency_Flag NUMBER := 0;
249 l_User_Default NUMBER := 0;
250 l_next_option NUMBER := 0;
251 l_Default_Modified BOOLEAN := FALSE;
252 l_Default_Value NUMBER := 0;
253 l_count NUMBER := 0;
254
255 CURSOR c_option_id IS
256 SELECT Option_Id
257 FROM BSC_KPI_ANALYSIS_OPTIONS_B
258 WHERE Indicator = p_Kpi_Id
259 AND Analysis_Group_ID = p_group_Id
260 AND ROWNUM < 2;
261 BEGIN
262 SAVEPOINT BSCSeefaulValPVT;
263 SELECT Dependency_Flag, Default_Value
264 INTO l_Dependency_Flag, l_Default_Value
265 FROM BSC_KPI_ANALYSIS_GROUPS
266 WHERE Indicator = p_Kpi_Id
267 AND Analysis_Group_Id = p_group_Id;
268
269 IF (l_Dependency_Flag = 0) THEN -- for indenpendent
270 SELECT COUNT(*) INTO l_User_Default
271 FROM BSC_KPI_ANALYSIS_OPTIONS_B
272 WHERE Indicator = p_Kpi_Id
273 AND Analysis_Group_Id = p_group_Id
274 AND User_Level0 = 1;
275 IF (l_User_Default = 0) THEN
276 IF (c_option_id%ISOPEN) THEN
277 CLOSE c_option_id;
278 END IF;
279 OPEN c_option_id;
280 FETCH c_option_id INTO l_next_option;
281 IF (c_option_id%NOTFOUND) THEN
282 l_next_option := 0;
283 END IF;
284 CLOSE c_option_id;
285
286 UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
287 SET User_Level0 = 1
288 , User_Level1 = 1
289 WHERE Indicator = p_Kpi_Id
290 AND Analysis_Group_Id = p_group_Id
291 AND Option_Id = l_next_option;
292
293 UPDATE BSC_KPI_ANALYSIS_GROUPS
294 SET Default_Value = l_next_option
295 WHERE Indicator = p_Kpi_Id
296 AND Analysis_Group_Id = p_group_Id;
297 l_Default_Modified := TRUE;
298 END IF;
299
300 ELSE -- for dependent
301 IF (p_group_Id = 0) THEN
302 SELECT COUNT(*) INTO l_User_Default
303 FROM BSC_KPI_ANALYSIS_OPTIONS_B
304 WHERE Indicator = p_Kpi_Id
305 AND Analysis_Group_Id = p_group_Id
306 AND User_Level0 = 1;
307 IF (l_User_Default = 0) THEN
308 IF (c_option_id%ISOPEN) THEN
309 CLOSE c_option_id;
310 END IF;
311 OPEN c_option_id;
312 FETCH c_option_id INTO l_next_option;
313 IF (c_option_id%NOTFOUND) THEN
314 l_next_option := 0;
315 END IF;
316 CLOSE c_option_id;
317
318 UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
319 SET User_Level0 = 1
320 , User_Level1 = 1
321 WHERE Indicator = p_Kpi_Id
322 AND Analysis_Group_Id = p_group_Id
323 AND Option_Id = l_next_option;
324
325 UPDATE BSC_KPI_ANALYSIS_GROUPS
326 SET Default_Value = l_next_option
327 WHERE Indicator = p_Kpi_Id
328 AND Analysis_Group_Id = p_group_Id;
329 l_Default_Modified := TRUE;
330 END IF;
331 ELSIF (p_group_Id = 1) THEN
332 SELECT COUNT(*) INTO l_User_Default
333 FROM BSC_KPI_ANALYSIS_OPTIONS_B
334 WHERE Indicator = p_Kpi_Id
335 AND Analysis_Group_Id = p_group_Id
336 AND User_Level0 = 1;
337 IF (l_User_Default = 0) THEN
338 IF (c_option_id%ISOPEN) THEN
339 CLOSE c_option_id;
340 END IF;
341 OPEN c_option_id;
342 FETCH c_option_id INTO l_next_option;
343 IF (c_option_id%NOTFOUND) THEN
344 l_next_option := 0;
345 END IF;
346 CLOSE c_option_id;
347
348 UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
349 SET User_Level0 = 1
350 , User_Level1 = 1
351 WHERE Indicator = p_Kpi_Id
352 AND Analysis_Group_Id = p_group_Id
353 AND Option_Id = l_next_option
354 AND Parent_Option_Id = p_Parent_Option_Id;
355
356 UPDATE BSC_KPI_ANALYSIS_GROUPS
357 SET Default_Value = l_next_option
358 WHERE Indicator = p_Kpi_Id
359 AND Analysis_Group_Id = p_group_Id;
360 l_Default_Modified := TRUE;
361 END IF;
362 ELSIF (p_group_Id = 2) THEN
363 SELECT COUNT(*) INTO l_User_Default
364 FROM BSC_KPI_ANALYSIS_OPTIONS_B
365 WHERE Indicator = p_Kpi_Id
366 AND Analysis_Group_Id = p_group_Id
367 AND User_Level0 = 1;
368 IF (l_User_Default = 0) THEN
369 IF (c_option_id%ISOPEN) THEN
370 CLOSE c_option_id;
371 END IF;
372 OPEN c_option_id;
373 FETCH c_option_id INTO l_next_option;
374 IF (c_option_id%NOTFOUND) THEN
375 l_next_option := 0;
376 END IF;
377 CLOSE c_option_id;
378
379 UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
380 SET User_Level0 = 1
381 , User_Level1 = 1
382 WHERE Indicator = p_Kpi_Id
383 AND Analysis_Group_Id = p_group_Id
384 AND Option_Id = l_next_option
385 AND Parent_Option_Id = p_Parent_Option_Id
386 AND Grandparent_Option_Id = p_Grand_Parent_Option_Id;
387
388 UPDATE BSC_KPI_ANALYSIS_GROUPS
389 SET Default_Value = l_next_option
390 WHERE Indicator = p_Kpi_Id
391 AND Analysis_Group_Id = p_group_Id;
392 l_Default_Modified := TRUE;
393 END IF;
394 END IF;
395 END IF;
396
397 SELECT COUNT(*)
398 INTO l_count
399 FROM BSC_KPI_ANALYSIS_OPTIONS_B
400 WHERE Indicator = p_Kpi_Id
401 AND Analysis_Group_Id = p_group_Id;
402 IF(l_count =0) THEN
403 l_Default_Modified := TRUE;
404 END IF;
405
406 IF (NOT l_Default_Modified) THEN
407 IF (l_default_value = p_option_Id) THEN
408 l_default_value := 0;
409 ELSIF(l_default_value > p_option_Id) THEN
410 l_default_value := l_default_value - 1 ;
411 IF(l_default_value<0) THEN
412 l_default_value := 0;
413 END IF;
414 END IF;
415
416 UPDATE BSC_KPI_ANALYSIS_GROUPS
417 SET DEFAULT_VALUE = l_default_value
418 WHERE INDiCATOR = p_Kpi_Id
419 AND ANALYSIS_GROUP_ID = p_group_Id;
420 END IF;
421 EXCEPTION
422 WHEN OTHERS THEN
423 ROLLBACK TO BSCSeefaulValPVT;
424 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS at Set_Default_Value '||SQLERRM);
425 RAISE;
426 END Set_Default_Value;
427
428 /*******************************************************************************/
429 PROCEDURE Swap_Option_Id
430 ( p_Kpi_Id NUMBER
431 , p_group_Id NUMBER
432 , p_parent_option_Id NUMBER
433 , p_grand_parent_option_Id NUMBER
434 ) IS
435 l_Swap_Table BSC_ANALYSIS_OPTION_PVT.Swap_Ana_Opts_Table;
436 l_Count NUMBER := 0;
437 l_Table_Count NUMBER := 0;
438 l_Dependency_Flag NUMBER := 0;
439 l_parent_option_Id NUMBER := -1;
440 l_grand_parent_option_Id NUMBER := -1;
441
442 CURSOR c_Kpi_InDependent_Opts IS
443 SELECT Option_ID
444 FROM BSC_KPI_ANALYSIS_OPTIONS_B
445 WHERE Indicator = p_Kpi_Id
446 AND Analysis_Group_Id = p_group_Id
447 ORDER BY Option_ID;
448
449 CURSOR c_Kpi_Dependent_Opts IS
450 SELECT Option_ID
451 FROM BSC_KPI_ANALYSIS_OPTIONS_B
452 WHERE Indicator = p_Kpi_Id
453 AND Analysis_Group_Id = p_group_Id
454 AND Parent_Option_Id = p_parent_option_Id
455 AND GrandParent_Option_Id = p_grand_parent_option_Id
456 ORDER BY Option_ID;
457
458 CURSOR c_Kpi_Par_Dependent_Opts IS
459 SELECT Parent_Option_Id
460 FROM BSC_KPI_ANALYSIS_OPTIONS_B
461 WHERE Indicator = p_Kpi_Id
462 AND Analysis_Group_Id = 1
463 AND Parent_Option_Id = l_parent_option_Id
464 ORDER BY Parent_Option_Id;
465
466 CURSOR c_Kpi_Gra_Par_Dep_Opts IS
467 SELECT Parent_Option_Id
468 FROM BSC_KPI_ANALYSIS_OPTIONS_B
469 WHERE Indicator = p_Kpi_Id
470 AND Analysis_Group_Id = 2
471 AND Parent_Option_Id = l_parent_option_Id
472 ORDER BY Parent_Option_Id;
473
474 CURSOR c_Kpi_GraPar_Dependent_Opts IS
475 SELECT GrandParent_Option_Id
476 FROM BSC_KPI_ANALYSIS_OPTIONS_B
477 WHERE Indicator = p_Kpi_Id
478 AND Analysis_Group_Id = 2
479 AND GrandParent_Option_Id = l_grand_parent_option_Id
480 ORDER BY GrandParent_Option_Id;
481
482 CURSOR c_Dependency_Flag IS
483 SELECT Dependency_Flag
484 FROM BSC_KPI_ANALYSIS_GROUPS
485 WHERE Indicator = p_Kpi_Id
486 AND Analysis_Group_Id = DECODE(p_Group_Id, 0, 1, p_Group_Id);
487 BEGIN
488 --DBMS_OUTPUT.PUT_LINE('entered inside Swap_Option_Id '||p_Group_Id);
489 SAVEPOINT BSCSwapOptIdPVT;
490 IF (c_Dependency_Flag%ISOPEN) THEN
491 CLOSE c_Dependency_Flag;
492 END IF;
493 OPEN c_Dependency_Flag;
494 FETCH c_Dependency_Flag INTO l_Dependency_Flag;
495 IF (c_Dependency_Flag%NOTFOUND) THEN
496 l_Dependency_Flag := 0;
497 END IF;
498 CLOSE c_Dependency_Flag;
499 --DBMS_OUTPUT.PUT_LINE('l_Dependency_Flag <'||l_Dependency_Flag||'>');
500 IF (l_Dependency_Flag = 0) THEN -- for indenpendent
501 l_Table_Count := 0;
502 l_Count := 0;
503 FOR cd IN c_Kpi_InDependent_Opts LOOP
504 IF (l_Count <> cd.Option_Id) THEN
505 l_swap_Table(l_Table_Count).p_AnaOpt_Prev_Id := cd.Option_Id;
506 l_swap_Table(l_Table_Count).p_AnaOpt_Next_Id := l_Count;
507
508 UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
509 SET Option_ID = l_Count
510 WHERE Indicator = p_Kpi_Id
511 AND Analysis_Group_ID = p_group_Id
512 AND Option_Id = cd.Option_Id;
513
514 UPDATE BSC_KPI_ANALYSIS_OPTIONS_TL
515 SET Option_ID = l_Count
516 WHERE Indicator = p_Kpi_Id
517 AND Analysis_Group_ID = p_group_Id
518 AND Option_Id = cd.Option_Id;
519
520 l_Parent_Option_Id := cd.Option_Id;
521 l_Grand_Parent_Option_Id := cd.Option_Id;
522 --DBMS_OUTPUT.PUT_LINE('p_Group_Id <'||p_Group_Id||'>');
523 --DBMS_OUTPUT.PUT_LINE('l_Parent_Option_Id <'||l_Parent_Option_Id||'>');
524 --DBMS_OUTPUT.PUT_LINE('l_Grand_Parent_Option_Id <'||l_Grand_Parent_Option_Id||'>');
525 --DBMS_OUTPUT.PUT_LINE('l_swap_Table('||l_Table_Count||').p_AnaOpt_Prev_Id <'||cd.Option_Id||'>');
526 --DBMS_OUTPUT.PUT_LINE('l_swap_Table('||l_Table_Count||').p_AnaOpt_Next_Id <'||l_Count||'>');
527 FOR ck IN c_Kpi_Gra_Par_Dep_Opts LOOP
528 UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
529 SET parent_option_id = l_Count
530 WHERE Indicator = p_Kpi_Id
531 AND Analysis_Group_ID = 2
532 AND Parent_Option_Id = ck.Parent_Option_Id;
533
534 UPDATE BSC_KPI_ANALYSIS_OPTIONS_TL
535 SET parent_option_id = l_Count
536 WHERE Indicator = p_Kpi_Id
537 AND Analysis_Group_ID = 2
538 AND Parent_Option_Id = ck.Parent_Option_Id;
539 END LOOP;
540
541 FOR cn1 IN c_Kpi_GraPar_Dependent_Opts LOOP
542 UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
543 SET Grandparent_Option_Id = l_Count
544 WHERE Indicator = p_Kpi_Id
545 AND Analysis_Group_ID = 2
546 AND Grandparent_Option_Id = cn1.GrandParent_Option_Id;
547
548 UPDATE BSC_KPI_ANALYSIS_OPTIONS_TL
549 SET Grandparent_Option_Id = l_Count
550 WHERE Indicator = p_Kpi_Id
551 AND Analysis_Group_ID = 2
552 AND Grandparent_Option_Id = cn1.GrandParent_Option_Id;
553 END LOOP;
554 l_Table_Count := l_Table_Count + 1;
555 END IF;
556 l_Count := l_Count + 1;
557 END LOOP;
558 IF (l_Table_Count <> 0) THEN
559 IF (p_group_Id = 0) THEN
560 FOR i IN 0..(l_swap_Table.COUNT-1) LOOP
561 UPDATE BSC_KPI_ANALYSIS_MEASURES_B
562 SET Analysis_Option0 = l_swap_Table(i).p_AnaOpt_Next_Id
563 WHERE Indicator = p_Kpi_Id
564 AND Analysis_Option0 = l_swap_Table(i).p_AnaOpt_Prev_Id;
565
566 UPDATE BSC_KPI_ANALYSIS_MEASURES_TL
567 SET Analysis_Option0 = l_swap_Table(i).p_AnaOpt_Next_Id
568 WHERE Indicator = p_Kpi_Id
569 AND Analysis_Option0 = l_swap_Table(i).p_AnaOpt_Prev_Id;
570 END LOOP;
571 ELSIF (p_group_Id = 1) THEN
572 FOR i IN 0..(l_swap_Table.COUNT-1) LOOP
573 UPDATE BSC_KPI_ANALYSIS_MEASURES_B
574 SET Analysis_Option1 = l_swap_Table(i).p_AnaOpt_Next_Id
575 WHERE Indicator = p_Kpi_Id
576 AND Analysis_Option1 = l_swap_Table(i).p_AnaOpt_Prev_Id;
577
578 UPDATE BSC_KPI_ANALYSIS_MEASURES_TL
579 SET Analysis_Option1 = l_swap_Table(i).p_AnaOpt_Next_Id
580 WHERE Indicator = p_Kpi_Id
581 AND Analysis_Option1 = l_swap_Table(i).p_AnaOpt_Prev_Id;
582 END LOOP;
583 ELSIF (p_group_Id = 2) THEN
584 FOR i IN 0..(l_swap_Table.COUNT-1) LOOP
585 UPDATE BSC_KPI_ANALYSIS_MEASURES_B
586 SET Analysis_Option2 = l_swap_Table(i).p_AnaOpt_Next_Id
587 WHERE Indicator = p_Kpi_Id
588 AND Analysis_Option2 = l_swap_Table(i).p_AnaOpt_Prev_Id;
589
590 UPDATE BSC_KPI_ANALYSIS_MEASURES_TL
591 SET Analysis_Option2 = l_swap_Table(i).p_AnaOpt_Next_Id
592 WHERE Indicator = p_Kpi_Id
593 AND Analysis_Option2 = l_swap_Table(i).p_AnaOpt_Prev_Id;
594 END LOOP;
595 END IF;
596 END IF;
597 ELSE -- for dependent
598 l_Table_Count := 0;
599 l_Count := 0;
600 FOR cd IN c_Kpi_Dependent_Opts LOOP
601 --DBMS_OUTPUT.PUT_LINE('l_swap_Table('||l_Table_Count||').p_AnaOpt_Prev_Id <'||cd.Option_Id||'>');
602 --DBMS_OUTPUT.PUT_LINE('l_swap_Table('||l_Table_Count||').p_AnaOpt_Next_Id <'||l_Count||'>');
603 IF (l_Count <> cd.Option_Id) THEN
604 l_swap_Table(l_Table_Count).p_AnaOpt_Prev_Id := cd.Option_Id;
605 l_swap_Table(l_Table_Count).p_AnaOpt_Next_Id := l_Count;
606 --DBMS_OUTPUT.PUT_LINE('*** SWAP ***');
607 IF (p_Group_Id = 0) THEN
608 UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
609 SET Option_ID = l_Count
610 WHERE Indicator = p_Kpi_Id
611 AND Option_Id = cd.Option_Id
612 AND Analysis_Group_ID = p_Group_Id
613 AND parent_option_id = p_Parent_Option_Id
614 AND Grandparent_Option_Id = p_Grand_Parent_Option_Id;
615
616 UPDATE BSC_KPI_ANALYSIS_OPTIONS_TL
617 SET Option_ID = l_Count
618 WHERE Indicator = p_Kpi_Id
619 AND Analysis_Group_ID = p_group_Id
620 AND Option_Id = cd.Option_Id
621 AND parent_option_id = p_Parent_Option_Id
622 AND Grandparent_Option_Id = p_Grand_Parent_Option_Id;
623 l_Parent_Option_Id := cd.Option_Id;
624 l_Grand_Parent_Option_Id := cd.Option_Id;
625 FOR cm IN c_Kpi_Par_Dependent_Opts LOOP
626 UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
627 SET parent_option_id = l_Count
628 WHERE Indicator = p_Kpi_Id
629 AND Analysis_Group_ID = 1
630 AND Parent_Option_Id = cm.Parent_Option_Id;
631
632 UPDATE BSC_KPI_ANALYSIS_OPTIONS_TL
633 SET parent_option_id = l_Count
634 WHERE Indicator = p_Kpi_Id
635 AND Analysis_Group_ID = 1
636 AND Parent_Option_Id = cm.Parent_Option_Id;
637 END LOOP;
638 FOR ck IN c_Kpi_Gra_Par_Dep_Opts LOOP
639 UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
640 SET parent_option_id = l_Count
641 WHERE Indicator = p_Kpi_Id
642 AND Analysis_Group_ID = 2
643 AND Parent_Option_Id = ck.Parent_Option_Id;
644
645 UPDATE BSC_KPI_ANALYSIS_OPTIONS_TL
646 SET parent_option_id = l_Count
647 WHERE Indicator = p_Kpi_Id
648 AND Analysis_Group_ID = 2
649 AND Parent_Option_Id = ck.Parent_Option_Id;
650 END LOOP;
651 FOR cn1 IN c_Kpi_GraPar_Dependent_Opts LOOP
652 UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
653 SET Grandparent_Option_Id = l_Count
654 WHERE Indicator = p_Kpi_Id
655 AND Analysis_Group_ID = 2
656 AND Grandparent_Option_Id = cn1.GrandParent_Option_Id;
657
658 UPDATE BSC_KPI_ANALYSIS_OPTIONS_TL
659 SET Grandparent_Option_Id = l_Count
660 WHERE Indicator = p_Kpi_Id
661 AND Analysis_Group_ID = 2
662 AND Grandparent_Option_Id = cn1.GrandParent_Option_Id;
663 END LOOP;
664 ELSIF (p_Group_Id = 1) THEN
665 UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
666 SET Option_ID = l_Count
667 WHERE Indicator = p_Kpi_Id
668 AND Option_Id = cd.Option_Id
669 AND Analysis_Group_ID = p_Group_Id
670 AND parent_option_id = p_Parent_Option_Id
671 AND Grandparent_Option_Id = p_Grand_Parent_Option_Id;
672
673 UPDATE BSC_KPI_ANALYSIS_OPTIONS_TL
674 SET Option_ID = l_Count
675 WHERE Indicator = p_Kpi_Id
676 AND Analysis_Group_ID = p_group_Id
677 AND Option_Id = cd.Option_Id
678 AND parent_option_id = p_Parent_Option_Id
679 AND Grandparent_Option_Id = p_Grand_Parent_Option_Id;
680
681 l_Parent_Option_Id := cd.Option_Id;
682 FOR cm IN c_Kpi_Par_Dependent_Opts LOOP
683 UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
684 SET parent_option_id = l_Count
685 WHERE Indicator = p_Kpi_Id
686 AND Analysis_Group_ID = 1
687 AND parent_option_id = cm.Parent_Option_Id;
688
689 UPDATE BSC_KPI_ANALYSIS_OPTIONS_TL
690 SET parent_option_id = l_Count
691 WHERE Indicator = p_Kpi_Id
692 AND Analysis_Group_ID = 1
693 AND parent_option_id = cm.Parent_Option_Id;
694 END LOOP;
695 ELSIF (p_Group_Id = 2) THEN
696 UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
697 SET Option_ID = l_Count
698 WHERE Indicator = p_Kpi_Id
699 AND Option_Id = cd.Option_Id
700 AND Analysis_Group_ID = p_Group_Id
701 AND parent_option_id = p_Parent_Option_Id
702 AND Grandparent_Option_Id = p_Grand_Parent_Option_Id;
703
704 UPDATE BSC_KPI_ANALYSIS_OPTIONS_TL
705 SET Option_ID = l_Count
706 WHERE Indicator = p_Kpi_Id
707 AND Analysis_Group_ID = p_group_Id
708 AND Option_Id = cd.Option_Id
709 AND parent_option_id = p_Parent_Option_Id
710 AND Grandparent_Option_Id = p_Grand_Parent_Option_Id;
711 END IF;
712 l_Table_Count := l_Table_Count + 1;
713 END IF;
714 l_Count := l_Count + 1;
715 END LOOP;
716 IF (l_Table_Count <> 0) THEN
717 IF (p_group_Id = 0) THEN
718 FOR i IN 0..(l_swap_Table.COUNT-1) LOOP
719 UPDATE BSC_KPI_ANALYSIS_MEASURES_B
720 SET Analysis_Option0 = l_swap_Table(i).p_AnaOpt_Next_Id
721 WHERE Indicator = p_Kpi_Id
722 AND Analysis_Option0 = l_swap_Table(i).p_AnaOpt_Prev_Id;
723
724 UPDATE BSC_KPI_ANALYSIS_MEASURES_TL
725 SET Analysis_Option0 = l_swap_Table(i).p_AnaOpt_Next_Id
726 WHERE Indicator = p_Kpi_Id
727 AND Analysis_Option0 = l_swap_Table(i).p_AnaOpt_Prev_Id;
728 END LOOP;
729 ELSIF (p_group_Id = 1) THEN
730 FOR i IN 0..(l_swap_Table.COUNT-1) LOOP
731 UPDATE BSC_KPI_ANALYSIS_MEASURES_B
732 SET Analysis_Option1 = l_swap_Table(i).p_AnaOpt_Next_Id
733 WHERE Indicator = p_Kpi_Id
734 AND Analysis_Option1 = l_swap_Table(i).p_AnaOpt_Prev_Id
735 AND Analysis_Option0 = p_Parent_Option_Id;
736
737 UPDATE BSC_KPI_ANALYSIS_MEASURES_TL
738 SET Analysis_Option1 = l_swap_Table(i).p_AnaOpt_Next_Id
739 WHERE Indicator = p_Kpi_Id
740 AND Analysis_Option1 = l_swap_Table(i).p_AnaOpt_Prev_Id
741 AND Analysis_Option0 = p_Parent_Option_Id;
742 END LOOP;
743 ELSIF (p_group_Id = 2) THEN
744 FOR i IN 0..(l_swap_Table.COUNT-1) LOOP
745 UPDATE BSC_KPI_ANALYSIS_MEASURES_B
746 SET Analysis_Option2 = l_swap_Table(i).p_AnaOpt_Next_Id
747 WHERE Indicator = p_Kpi_Id
748 AND Analysis_Option2 = l_swap_Table(i).p_AnaOpt_Prev_Id
749 AND Analysis_Option1 = p_Parent_Option_Id
750 AND Analysis_Option0 = p_Grand_Parent_Option_Id;
751
752 UPDATE BSC_KPI_ANALYSIS_MEASURES_TL
753 SET Analysis_Option2 = l_swap_Table(i).p_AnaOpt_Next_Id
754 WHERE Indicator = p_Kpi_Id
755 AND Analysis_Option2 = l_swap_Table(i).p_AnaOpt_Prev_Id
756 AND Analysis_Option1 = p_parent_option_Id
757 AND Analysis_Option0 = p_grand_parent_option_Id;
758 END LOOP;
759 END IF;
760 END IF;
761 END IF;
762 EXCEPTION
763 WHEN OTHERS THEN
764 IF (c_Dependency_Flag%ISOPEN) THEN
765 CLOSE c_Dependency_Flag;
766 END IF;
767 ROLLBACK TO BSCSwapOptIdPVT;
768 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS at Swap_Option_Id '||SQLERRM);
769 RAISE;
770 END Swap_Option_Id;
771 /**************************************************************************************************/
772 --: This procedure is used to create an analysis option. This is the entry point
773 --: for the Analysis Option API.
774 --: This procedure is part of the Analysis Option API.
775
776 procedure Create_Analysis_Options(
777 p_commit IN varchar2 -- := FND_API.G_FALSE
778 ,p_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
779 ,x_return_status OUT NOCOPY varchar2
780 ,x_msg_count OUT NOCOPY number
781 ,x_msg_data OUT NOCOPY varchar2
782 ) is
783
784 l_count number;
785 l_Anal_Opt_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
786 begin
787 FND_MSG_PUB.Initialize;
788 x_return_status := FND_API.G_RET_STS_SUCCESS;
789 SAVEPOINT CreateBSCAnaOptPVT;
790 -- Check that valid Kpi id was entered.
791 if p_Anal_Opt_Rec.Bsc_Kpi_Id is not null then
792 /*l_count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_KPIS_B'
793 ,'indicator'
794 ,p_Anal_Opt_Rec.Bsc_Kpi_Id);*/
795 SELECT COUNT(0)
796 INTO l_count
797 FROM BSC_KPIS_B
798 WHERE INDICATOR = p_Anal_Opt_Rec.Bsc_Kpi_Id;
799
800 if l_count = 0 then
801 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_KPI_ID');
802 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Anal_Opt_Rec.Bsc_Kpi_Id);
803 FND_MSG_PUB.ADD;
804 RAISE FND_API.G_EXC_ERROR;
805 end if;
806 else
807 FND_MESSAGE.SET_NAME('BSC','BSC_NO_KPI_ID_ENTERED');
808 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Anal_Opt_Rec.Bsc_Kpi_Id);
809 FND_MSG_PUB.ADD;
810 RAISE FND_API.G_EXC_ERROR;
811 end if;
812
813 g_db_object := 'BSC_KPI_ANALYSIS_OPTIONS_B';
814
815 -- Insert pertaining values into table bsc_kpi_analysis_options_b.
816 INSERT INTO BSC_KPI_ANALYSIS_OPTIONS_B( INDICATOR
817 ,ANALYSIS_GROUP_ID
818 ,OPTION_ID
819 ,PARENT_OPTION_ID
820 ,GRANDPARENT_OPTION_ID
821 ,DIM_SET_ID
822 ,USER_LEVEL0
823 ,USER_LEVEL1
824 ,USER_LEVEL1_DEFAULT
825 ,USER_LEVEL2
826 ,USER_LEVEL2_DEFAULT
827 ,SHORT_NAME)
828 VALUES( p_Anal_Opt_Rec.Bsc_Kpi_Id
829 ,p_Anal_Opt_Rec.Bsc_Analysis_Group_Id
830 ,p_Anal_Opt_Rec.Bsc_Analysis_Option_Id
831 ,p_Anal_Opt_Rec.Bsc_Parent_Option_Id
832 ,p_Anal_Opt_Rec.Bsc_Grandparent_Option_Id
833 ,p_Anal_Opt_Rec.Bsc_Dim_Set_Id
834 ,p_Anal_Opt_Rec.Bsc_User_Level0
835 ,p_Anal_Opt_Rec.Bsc_User_Level1
836 ,p_Anal_Opt_Rec.Bsc_User_Level1_Default
837 ,p_Anal_Opt_Rec.Bsc_User_Level2
838 ,p_Anal_Opt_Rec.Bsc_User_Level2_Default
839 ,p_Anal_Opt_Rec.Bsc_Option_Short_Name);
840
841 g_db_object := 'BSC_KPI_ANALYSIS_OPTIONS_TL';
842
843 -- Insert pertaining values into table bsc_kpi_analysis_options_tl.
844 INSERT INTO BSC_KPI_ANALYSIS_OPTIONS_TL( INDICATOR
845 ,ANALYSIS_GROUP_ID
846 ,OPTION_ID
847 ,PARENT_OPTION_ID
848 ,GRANDPARENT_OPTION_ID
849 ,LANGUAGE
850 ,SOURCE_LANG
851 ,NAME
852 ,HELP)
853 select p_Anal_Opt_Rec.Bsc_Kpi_Id
854 ,p_Anal_Opt_Rec.Bsc_Analysis_Group_Id
855 ,p_Anal_Opt_Rec.Bsc_Analysis_Option_Id
856 ,p_Anal_Opt_Rec.Bsc_Parent_Option_Id
857 ,p_Anal_Opt_Rec.Bsc_Grandparent_Option_Id
858 ,L.LANGUAGE_CODE
859 ,userenv('LANG')
860 ,p_Anal_Opt_Rec.Bsc_Option_Name
861 ,p_Anal_Opt_Rec.Bsc_Option_Help
862 from FND_LANGUAGES L
863 where L.INSTALLED_FLAG in ('I', 'B')
864 and not exists
865 (select NULL
866 from BSC_KPI_ANALYSIS_OPTIONS_TL T
867 where T.indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
868 and T.analysis_group_id = p_Anal_Opt_Rec.Bsc_Analysis_Group_Id
869 and T.option_id = p_Anal_Opt_Rec.Bsc_Analysis_Option_Id
870 and T.parent_option_id = p_Anal_Opt_Rec.Bsc_Parent_Option_Id
871 and T.grandparent_option_id = p_Anal_Opt_Rec.Bsc_Grandparent_Option_Id
872 and T.LANGUAGE = L.LANGUAGE_CODE);
873
874 -- Update table bsc_kpi_analysis_groups with the current number of options.
875 update BSC_KPI_ANALYSIS_GROUPS
876 set num_of_options = p_Anal_Opt_Rec.Bsc_Analysis_Option_Id + 1
877 where indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id;
878
879 if (p_commit = FND_API.G_TRUE) then
880 commit;
881 end if;
882
883 EXCEPTION
884 WHEN FND_API.G_EXC_ERROR THEN
885 ROLLBACK TO CreateBSCAnaOptPVT;
886 FND_MSG_PUB.Count_And_Get
887 ( p_encoded => 'F'
888 , p_count => x_msg_count
889 , p_data => x_msg_data
890 );
891 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
892 x_return_status := FND_API.G_RET_STS_ERROR;
893 RAISE;
894 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
895 ROLLBACK TO CreateBSCAnaOptPVT;
896 FND_MSG_PUB.Count_And_Get
897 ( p_encoded => 'F'
898 , p_count => x_msg_count
899 , p_data => x_msg_data
900 );
901 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
902 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
903 RAISE;
904 WHEN NO_DATA_FOUND THEN
905 ROLLBACK TO CreateBSCAnaOptPVT;
906 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
907 IF (x_msg_data IS NOT NULL) THEN
908 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Create_Analysis_Options ';
909 ELSE
910 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Create_Analysis_Options ';
911 END IF;
912 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
913 RAISE;
914 WHEN OTHERS THEN
915 ROLLBACK TO CreateBSCAnaOptPVT;
916 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
917 IF (x_msg_data IS NOT NULL) THEN
918 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Create_Analysis_Options ';
919 ELSE
920 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Create_Analysis_Options ';
921 END IF;
922 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
923 RAISE;
924 end Create_Analysis_Options;
925
926 /************************************************************************************
927 ************************************************************************************/
928
929 -- added code to retrive Short_Name as well.
930 procedure Retrieve_Analysis_Options
931 (
932 p_commit IN varchar2 -- := FND_API.G_FALSE
933 , p_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
934 , x_Anal_Opt_Rec IN OUT NOCOPY BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
935 , p_data_source IN VARCHAR2
936 , x_return_status OUT NOCOPY varchar2
937 , x_msg_count OUT NOCOPY number
938 , x_msg_data OUT NOCOPY varchar2
939 ) is
940
941 begin
942 FND_MSG_PUB.Initialize;
943 x_return_status := FND_API.G_RET_STS_SUCCESS;
944 g_db_object := 'Retrieve_Analysis_Options';
945 IF ((p_Data_Source IS NOT NULL) AND
946 (p_Data_Source = 'BSC') AND
947 (p_Anal_Opt_Rec.Bsc_Grandparent_Option_Id IS NULL) AND
948 (p_Anal_Opt_Rec.Bsc_Parent_Option_Id IS NOT NULL )) THEN
949 SELECT DISTINCT GRANDPARENT_OPTION_ID
950 ,DIM_SET_ID
951 ,USER_LEVEL0
952 ,USER_LEVEL1
953 ,USER_LEVEL1_DEFAULT
954 ,USER_LEVEL2
955 ,USER_LEVEL2_DEFAULT
956 ,NAME
957 ,HELP
958 ,SHORT_NAME
959 into x_Anal_Opt_Rec.Bsc_Grandparent_Option_Id
960 ,x_Anal_Opt_Rec.Bsc_Dim_Set_Id
961 ,x_Anal_Opt_Rec.Bsc_User_Level0
962 ,x_Anal_Opt_Rec.Bsc_User_Level1
963 ,x_Anal_Opt_Rec.Bsc_User_Level1_Default
964 ,x_Anal_Opt_Rec.Bsc_User_Level2
965 ,x_Anal_Opt_Rec.Bsc_User_Level2_Default
966 ,x_Anal_Opt_Rec.Bsc_Option_Name
967 ,x_Anal_Opt_Rec.Bsc_Option_Help
968 ,x_Anal_Opt_Rec.Bsc_Option_Short_Name
969 from BSC_KPI_ANALYSIS_OPTIONS_VL
970 where indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
971 and analysis_group_id = p_Anal_Opt_Rec.Bsc_Analysis_Group_Id
972 and option_id = p_Anal_Opt_Rec.Bsc_Analysis_Option_Id
973 and parent_option_id = p_Anal_Opt_Rec.Bsc_Parent_Option_Id;
974 ELSIF ((p_Data_Source IS NOT NULL) AND
975 (p_Data_Source = 'BSC') AND
976 (p_Anal_Opt_Rec.Bsc_Grandparent_Option_Id IS NOT NULL) AND
977 (p_Anal_Opt_Rec.Bsc_Parent_Option_Id IS NOT NULL )) THEN
978 SELECT DISTINCT DIM_SET_ID
979 ,USER_LEVEL0
980 ,USER_LEVEL1
981 ,USER_LEVEL1_DEFAULT
982 ,USER_LEVEL2
983 ,USER_LEVEL2_DEFAULT
984 ,NAME
985 ,HELP
986 ,SHORT_NAME
987 into x_Anal_Opt_Rec.Bsc_Dim_Set_Id
988 ,x_Anal_Opt_Rec.Bsc_User_Level0
989 ,x_Anal_Opt_Rec.Bsc_User_Level1
990 ,x_Anal_Opt_Rec.Bsc_User_Level1_Default
991 ,x_Anal_Opt_Rec.Bsc_User_Level2
992 ,x_Anal_Opt_Rec.Bsc_User_Level2_Default
993 ,x_Anal_Opt_Rec.Bsc_Option_Name
994 ,x_Anal_Opt_Rec.Bsc_Option_Help
995 ,x_Anal_Opt_Rec.Bsc_Option_Short_Name
996 from BSC_KPI_ANALYSIS_OPTIONS_VL
997 where indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
998 and analysis_group_id = p_Anal_Opt_Rec.Bsc_Analysis_Group_Id
999 and option_id = p_Anal_Opt_Rec.Bsc_Analysis_Option_Id
1000 and parent_option_id = p_Anal_Opt_Rec.Bsc_Parent_Option_Id
1001 and grandparent_option_id = p_Anal_Opt_Rec.Bsc_Grandparent_Option_Id;
1002 ELSE
1003 SELECT DISTINCT PARENT_OPTION_ID
1004 ,GRANDPARENT_OPTION_ID
1005 ,DIM_SET_ID
1006 ,USER_LEVEL0
1007 ,USER_LEVEL1
1008 ,USER_LEVEL1_DEFAULT
1009 ,USER_LEVEL2
1010 ,USER_LEVEL2_DEFAULT
1011 ,NAME
1012 ,HELP
1013 ,SHORT_NAME
1014 into x_Anal_Opt_Rec.Bsc_Parent_Option_Id
1015 ,x_Anal_Opt_Rec.Bsc_Grandparent_Option_Id
1016 ,x_Anal_Opt_Rec.Bsc_Dim_Set_Id
1017 ,x_Anal_Opt_Rec.Bsc_User_Level0
1018 ,x_Anal_Opt_Rec.Bsc_User_Level1
1019 ,x_Anal_Opt_Rec.Bsc_User_Level1_Default
1020 ,x_Anal_Opt_Rec.Bsc_User_Level2
1021 ,x_Anal_Opt_Rec.Bsc_User_Level2_Default
1022 ,x_Anal_Opt_Rec.Bsc_Option_Name
1023 ,x_Anal_Opt_Rec.Bsc_Option_Help
1024 ,x_Anal_Opt_Rec.Bsc_Option_Short_Name
1025 from BSC_KPI_ANALYSIS_OPTIONS_VL
1026 where indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
1027 and analysis_group_id = p_Anal_Opt_Rec.Bsc_Analysis_Group_Id
1028 and option_id = p_Anal_Opt_Rec.Bsc_Analysis_Option_Id;
1029
1030 END IF;
1031 if (p_commit = FND_API.G_TRUE) then
1032 commit;
1033 end if;
1034
1035 EXCEPTION
1036 WHEN FND_API.G_EXC_ERROR THEN
1037 FND_MSG_PUB.Count_And_Get
1038 ( p_encoded => FND_API.G_FALSE
1039 , p_count => x_msg_count
1040 , p_data => x_msg_data
1041 );
1042 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1043 x_return_status := FND_API.G_RET_STS_ERROR;
1044 RAISE;
1045 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1046 FND_MSG_PUB.Count_And_Get
1047 ( p_encoded => FND_API.G_FALSE
1048 , p_count => x_msg_count
1049 , p_data => x_msg_data
1050 );
1051 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1052 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1053 RAISE;
1054 WHEN NO_DATA_FOUND THEN
1055 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1056 IF (x_msg_data IS NOT NULL) THEN
1057 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Retrieve_Analysis_Options ';
1058 ELSE
1059 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Retrieve_Analysis_Options ';
1060 END IF;
1061 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1062 RAISE;
1063 WHEN OTHERS THEN
1064 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1065 IF (x_msg_data IS NOT NULL) THEN
1066 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Retrieve_Analysis_Options ';
1067 ELSE
1068 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Retrieve_Analysis_Options ';
1069 END IF;
1070 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1071 RAISE;
1072 end Retrieve_Analysis_Options;
1073
1074 /************************************************************************************
1075 ************************************************************************************/
1076
1077 procedure Update_Analysis_Options
1078 (
1079 p_commit IN varchar2 -- := FND_API.G_FALSE
1080 , p_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
1081 , p_data_source IN VARCHAR2
1082 , x_return_status OUT NOCOPY VARCHAR2
1083 , x_msg_count OUT NOCOPY NUMBER
1084 , x_msg_data OUT NOCOPY VARCHAR2
1085 ) IS
1086 l_Anal_Opt_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
1087 l_count number;
1088 begin
1089 FND_MSG_PUB.Initialize;
1090 x_return_status := FND_API.G_RET_STS_SUCCESS;
1091 SAVEPOINT UpdateBSCAnaOptPVT;
1092 -- Check that valid Kpi id was entered.
1093 if p_Anal_Opt_Rec.Bsc_Kpi_Id is not null then
1094 /*l_count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_KPIS_B'
1095 ,'indicator'
1096 ,p_Anal_Opt_Rec.Bsc_Kpi_Id);*/
1097 SELECT COUNT(0)
1098 INTO l_count
1099 FROM BSC_KPIS_B
1100 WHERE INDICATOR = p_Anal_Opt_Rec.Bsc_Kpi_Id;
1101
1102 if l_count = 0 then
1103 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_KPI_ID');
1104 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Anal_Opt_Rec.Bsc_Kpi_Id);
1105 FND_MSG_PUB.ADD;
1106 RAISE FND_API.G_EXC_ERROR;
1107 end if;
1108 else
1109 FND_MESSAGE.SET_NAME('BSC','BSC_NO_KPI_ID_ENTERED');
1110 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Anal_Opt_Rec.Bsc_Kpi_Id);
1111 FND_MSG_PUB.ADD;
1112 RAISE FND_API.G_EXC_ERROR;
1113 end if;
1114 -- update LOCAL language ,source language, group id and level Id values with PASSED values.
1115 l_Anal_Opt_Rec.Bsc_Language := p_Anal_Opt_Rec.Bsc_Language;
1116 l_Anal_Opt_Rec.Bsc_Source_Language := p_Anal_Opt_Rec.Bsc_Source_Language;
1117 l_Anal_Opt_Rec.Bsc_Kpi_Id := p_Anal_Opt_Rec.Bsc_Kpi_Id;
1118 l_Anal_Opt_Rec.Bsc_Analysis_Group_Id := p_Anal_Opt_Rec.Bsc_Analysis_Group_Id;
1119 l_Anal_Opt_Rec.Bsc_Analysis_Option_Id := p_Anal_Opt_Rec.Bsc_Analysis_Option_Id;
1120 l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id := p_Anal_Opt_Rec.Bsc_Grandparent_Option_Id;
1121 l_Anal_Opt_Rec.Bsc_Parent_Option_Id := p_Anal_Opt_Rec.Bsc_Parent_Option_Id;
1122
1123 -- Not all values will be passed. We need to make sure values not passed are not
1124 -- changed by procedure, therefore we get what is there before we do any updates.
1125 Retrieve_Analysis_Options( p_commit
1126 ,p_Anal_Opt_Rec
1127 ,l_Anal_Opt_Rec
1128 ,p_data_source
1129 ,x_return_status
1130 ,x_msg_count
1131 ,x_msg_data);
1132
1133 -- Copy PASSED Record values into LOCAL Record values for the PASSED Record values
1134 -- which are NOT NULL.
1135 if p_Anal_Opt_Rec.Bsc_Parent_Option_Id is not null then
1136 l_Anal_Opt_Rec.Bsc_Parent_Option_Id := p_Anal_Opt_Rec.Bsc_Parent_Option_Id;
1137 end if;
1138 if p_Anal_Opt_Rec.Bsc_Grandparent_Option_Id is not null then
1139 l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id := p_Anal_Opt_Rec.Bsc_Grandparent_Option_Id;
1140 end if;
1141 if p_Anal_Opt_Rec.Bsc_Dim_Set_Id is not null then
1142 l_Anal_Opt_Rec.Bsc_Dim_Set_Id := p_Anal_Opt_Rec.Bsc_Dim_Set_Id;
1143 end if;
1144 if p_Anal_Opt_Rec.Bsc_User_Level0 is not null then
1145 l_Anal_Opt_Rec.Bsc_User_Level0 := p_Anal_Opt_Rec.Bsc_User_Level0;
1146 end if;
1147 if p_Anal_Opt_Rec.Bsc_User_Level1 is not null then
1148 l_Anal_Opt_Rec.Bsc_User_Level1 := p_Anal_Opt_Rec.Bsc_User_Level1;
1149 end if;
1150 if p_Anal_Opt_Rec.Bsc_User_Level1_Default is not null then
1151 l_Anal_Opt_Rec.Bsc_User_Level1_Default := p_Anal_Opt_Rec.Bsc_User_Level1_Default;
1152 end if;
1153 if p_Anal_Opt_Rec.Bsc_User_Level2 is not null then
1154 l_Anal_Opt_Rec.Bsc_User_Level2 := p_Anal_Opt_Rec.Bsc_User_Level2;
1155 end if;
1156 if p_Anal_Opt_Rec.Bsc_User_Level2_Default is not null then
1157 l_Anal_Opt_Rec.Bsc_User_Level2_Default := p_Anal_Opt_Rec.Bsc_User_Level2_Default;
1158 end if;
1159 if p_Anal_Opt_Rec.Bsc_Option_Name is not null then
1160 l_Anal_Opt_Rec.Bsc_Option_Name := p_Anal_Opt_Rec.Bsc_Option_Name;
1161 end if;
1162 if p_Anal_Opt_Rec.Bsc_Option_Help is not null then
1163 l_Anal_Opt_Rec.Bsc_Option_Help := p_Anal_Opt_Rec.Bsc_Option_Help;
1164 end if;
1165
1166 -- adrao added for Enh#3540302 and Bug#3691035
1167 if p_Anal_Opt_Rec.Bsc_Option_Short_Name is not null then
1168 l_Anal_Opt_Rec.Bsc_Option_Short_Name := p_Anal_Opt_Rec.Bsc_Option_Short_Name;
1169 end if;
1170
1171 IF ((p_Data_Source IS NOT NULL) AND
1172 (p_Data_Source = 'BSC') AND
1173 (p_Anal_Opt_Rec.Bsc_Grandparent_Option_Id IS NULL) AND
1174 (p_Anal_Opt_Rec.Bsc_Parent_Option_Id IS NOT NULL )) THEN
1175 update BSC_KPI_ANALYSIS_OPTIONS_B
1176 set grandparent_option_id = l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id
1177 ,dim_set_id = l_Anal_Opt_Rec.Bsc_Dim_Set_Id
1178 ,user_level0 = l_Anal_Opt_Rec.Bsc_User_Level0
1179 ,user_level1 = l_Anal_Opt_Rec.Bsc_User_Level1
1180 ,user_level1_default = l_Anal_Opt_Rec.Bsc_User_Level1_Default
1181 ,user_level2 = l_Anal_Opt_Rec.Bsc_User_Level2
1182 ,user_level2_default = l_Anal_Opt_Rec.Bsc_User_Level2_Default
1183 ,short_name = l_Anal_Opt_Rec.Bsc_Option_Short_Name
1184 where indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
1185 and analysis_group_id = p_Anal_Opt_Rec.Bsc_Analysis_Group_Id
1186 and option_Id = p_Anal_Opt_Rec.Bsc_Analysis_Option_Id
1187 and parent_option_id = p_Anal_Opt_Rec.Bsc_Parent_Option_Id;
1188
1189 update BSC_KPI_ANALYSIS_OPTIONS_TL
1190 set name = l_Anal_Opt_Rec.Bsc_Option_Name
1191 ,help = l_Anal_Opt_Rec.Bsc_Option_Help
1192 ,source_lang = userenv('LANG')
1193 where indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
1194 and analysis_group_id = p_Anal_Opt_Rec.Bsc_Analysis_Group_Id
1195 and option_Id = p_Anal_Opt_Rec.Bsc_Analysis_Option_Id
1196 and parent_option_id = p_Anal_Opt_Rec.Bsc_Parent_Option_Id
1197 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1198 ELSIF ((p_Data_Source IS NOT NULL) AND
1199 (p_Data_Source = 'BSC') AND
1200 (p_Anal_Opt_Rec.Bsc_Grandparent_Option_Id IS NOT NULL) AND
1201 (p_Anal_Opt_Rec.Bsc_Parent_Option_Id IS NOT NULL )) THEN
1202 update BSC_KPI_ANALYSIS_OPTIONS_B
1203 set dim_set_id = l_Anal_Opt_Rec.Bsc_Dim_Set_Id
1204 ,user_level0 = l_Anal_Opt_Rec.Bsc_User_Level0
1205 ,user_level1 = l_Anal_Opt_Rec.Bsc_User_Level1
1206 ,user_level1_default = l_Anal_Opt_Rec.Bsc_User_Level1_Default
1207 ,user_level2 = l_Anal_Opt_Rec.Bsc_User_Level2
1208 ,user_level2_default = l_Anal_Opt_Rec.Bsc_User_Level2_Default
1209 ,short_name = l_Anal_Opt_Rec.Bsc_Option_Short_Name
1210 where indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
1211 and analysis_group_id = p_Anal_Opt_Rec.Bsc_Analysis_Group_Id
1212 and option_Id = p_Anal_Opt_Rec.Bsc_Analysis_Option_Id
1213 and parent_option_id = p_Anal_Opt_Rec.Bsc_Parent_Option_Id
1214 and grandparent_option_id = p_Anal_Opt_Rec.Bsc_Grandparent_Option_Id;
1215
1216 update BSC_KPI_ANALYSIS_OPTIONS_TL
1217 set name = l_Anal_Opt_Rec.Bsc_Option_Name
1218 ,help = l_Anal_Opt_Rec.Bsc_Option_Help
1219 ,source_lang = userenv('LANG')
1220 where indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
1221 and analysis_group_id = p_Anal_Opt_Rec.Bsc_Analysis_Group_Id
1222 and option_Id = p_Anal_Opt_Rec.Bsc_Analysis_Option_Id
1223 and parent_option_id = p_Anal_Opt_Rec.Bsc_Parent_Option_Id
1224 and grandparent_option_id = p_Anal_Opt_Rec.Bsc_Grandparent_Option_Id
1225 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1226 ELSE
1227 update BSC_KPI_ANALYSIS_OPTIONS_B
1228 set parent_option_id = l_Anal_Opt_Rec.Bsc_Parent_Option_Id
1229 ,grandparent_option_id = l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id
1230 ,dim_set_id = l_Anal_Opt_Rec.Bsc_Dim_Set_Id
1231 ,user_level0 = l_Anal_Opt_Rec.Bsc_User_Level0
1232 ,user_level1 = l_Anal_Opt_Rec.Bsc_User_Level1
1233 ,user_level1_default = l_Anal_Opt_Rec.Bsc_User_Level1_Default
1234 ,user_level2 = l_Anal_Opt_Rec.Bsc_User_Level2
1235 ,user_level2_default = l_Anal_Opt_Rec.Bsc_User_Level2_Default
1236 ,short_name = l_Anal_Opt_Rec.Bsc_Option_Short_Name
1237 where indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
1238 and analysis_group_id = p_Anal_Opt_Rec.Bsc_Analysis_Group_Id
1239 and option_Id = p_Anal_Opt_Rec.Bsc_Analysis_Option_Id;
1240
1241 update BSC_KPI_ANALYSIS_OPTIONS_TL
1242 set name = l_Anal_Opt_Rec.Bsc_Option_Name
1243 ,help = l_Anal_Opt_Rec.Bsc_Option_Help
1244 ,source_lang = userenv('LANG')
1245 where indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
1246 and analysis_group_id = p_Anal_Opt_Rec.Bsc_Analysis_Group_Id
1247 and option_Id = p_Anal_Opt_Rec.Bsc_Analysis_Option_Id
1248 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1249 END IF;
1250 IF (p_commit = FND_API.G_TRUE) THEN
1251 COMMIT;
1252 END IF;
1253 EXCEPTION
1254 WHEN FND_API.G_EXC_ERROR THEN
1255 ROLLBACK TO UpdateBSCAnaOptPVT;
1256 FND_MSG_PUB.Count_And_Get
1257 ( p_encoded => FND_API.G_FALSE
1258 , p_count => x_msg_count
1259 , p_data => x_msg_data
1260 );
1261 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1262 x_return_status := FND_API.G_RET_STS_ERROR;
1263 RAISE;
1264 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1265 ROLLBACK TO UpdateBSCAnaOptPVT;
1266 FND_MSG_PUB.Count_And_Get
1267 ( p_encoded => FND_API.G_FALSE
1268 , p_count => x_msg_count
1269 , p_data => x_msg_data
1270 );
1271 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1272 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1273 RAISE;
1274 WHEN NO_DATA_FOUND THEN
1275 ROLLBACK TO UpdateBSCAnaOptPVT;
1276 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1277 IF (x_msg_data IS NOT NULL) THEN
1278 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Update_Analysis_Options ';
1279 ELSE
1280 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Update_Analysis_Options ';
1281 END IF;
1282 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1283 RAISE;
1284 WHEN OTHERS THEN
1285 ROLLBACK TO UpdateBSCAnaOptPVT;
1286 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1287 IF (x_msg_data IS NOT NULL) THEN
1288 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Update_Analysis_Options ';
1289 ELSE
1290 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Update_Analysis_Options ';
1291 END IF;
1292 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1293 RAISE;
1294 end Update_Analysis_Options;
1295
1296 /************************************************************************************
1297 ************************************************************************************/
1298
1299 PROCEDURE Delete_Analysis_Options
1300 ( p_commit IN varchar2 -- := FND_API.G_FALSE
1301 , p_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
1302 , x_return_status OUT NOCOPY VARCHAR2
1303 , x_msg_count OUT NOCOPY NUMBER
1304 , x_msg_data OUT NOCOPY VARCHAR2
1305 ) IS
1306 l_Group_ID BSC_KPI_ANALYSIS_OPTIONS_B.Analysis_Group_Id%TYPE;
1307 l_Anal_Opt_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
1308 l_Bsc_Kpi_Entity_Rec BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
1309
1310 l_AnaOpt_Delete BOOLEAN := TRUE;
1311 l_delete VARCHAR2(1);
1312 l_count NUMBER;
1313 l_shared_count NUMBER;
1314 l_default_option NUMBER;
1315 l_next_option NUMBER;
1316
1317 l_Parent_Opt_Id NUMBER;
1318 l_Gra_Parent_Opt_Id NUMBER;
1319 l_default_value NUMBER;
1320 l_Kpi_Name BSC_KPIS_VL.NAME%TYPE;
1321
1322 CURSOR c_kpi_ids IS
1323 SELECT indicator
1324 FROM BSC_KPIS_B
1325 WHERE Source_Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
1326 AND Prototype_Flag <> BSC_KPI_PUB.Delete_Kpi_Flag;
1327 BEGIN
1328 FND_MSG_PUB.Initialize;
1329 x_return_status := FND_API.G_RET_STS_SUCCESS;
1330 SAVEPOINT DeleteBSCAnaOptPVT;
1331 l_Anal_Opt_Rec := p_Anal_Opt_Rec;
1332 -- Check that valid Kpi id was entered.
1333 IF (p_Anal_Opt_Rec.Bsc_Kpi_Id IS NOT NULL) THEN
1334 l_count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_KPIS_B', 'indicator', p_Anal_Opt_Rec.Bsc_Kpi_Id);
1335 IF l_count = 0 THEN
1336 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_KPI_ID');
1337 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Anal_Opt_Rec.Bsc_Kpi_Id);
1338 FND_MSG_PUB.ADD;
1339 RAISE FND_API.G_EXC_ERROR;
1340 END IF;
1341 ELSE
1342 FND_MESSAGE.SET_NAME('BSC','BSC_NO_KPI_ID_ENTERED');
1343 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Anal_Opt_Rec.Bsc_Kpi_Id);
1344 FND_MSG_PUB.ADD;
1345 RAISE FND_API.G_EXC_ERROR;
1346 END IF;
1347 l_delete := Delete_Analysis_Option( l_Anal_Opt_Rec.Bsc_Kpi_Id
1348 ,l_Anal_Opt_Rec.Bsc_Analysis_Option_Id
1349 ,x_return_status
1350 ,x_msg_count
1351 ,x_msg_data
1352 ,l_Anal_Opt_Rec.Bsc_Analysis_Group_Id);
1353 IF (l_delete = 'S') THEN
1354 FND_MESSAGE.SET_NAME('BSC','BSC_SHARED_KPI');
1355 FND_MESSAGE.SET_TOKEN('BSC_AO_DELETE', l_Anal_Opt_Rec.Bsc_Kpi_Id);
1356 FND_MSG_PUB.ADD;
1357 RAISE FND_API.G_EXC_ERROR;
1358 END IF;
1359 IF l_delete = 'L' THEN
1360 IF is_custom_kpi(l_Anal_Opt_Rec.Bsc_Kpi_Id,l_Kpi_Name) = FALSE THEN
1361 FND_MESSAGE.SET_NAME('BSC','BSC_LAST_AO_IN_KPI');
1362 FND_MSG_PUB.ADD;
1363 RAISE FND_API.G_EXC_ERROR;
1364 ELSE
1365 FND_MESSAGE.SET_NAME('BSC','BSC_LAST_AO_IN_CUST_KPI');
1366 FND_MESSAGE.SET_TOKEN('OBJ_NAME', l_Kpi_Name);
1367 FND_MSG_PUB.ADD;
1368 RAISE FND_API.G_EXC_ERROR;
1369 END IF;
1370 END IF;
1371
1372 IF (l_Anal_Opt_Rec.Bsc_Analysis_Group_Id IS NULL) THEN
1373 l_Anal_Opt_Rec.Bsc_Analysis_Group_Id := 0;
1374 END IF;
1375 l_AnaOpt_Delete := TRUE;
1376 l_Parent_Opt_Id := l_Anal_Opt_Rec.Bsc_Parent_Option_Id;
1377 l_Gra_Parent_Opt_Id := l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id;
1378 --DBMS_OUTPUT.PUT_LINE('l_Anal_Opt_Rec.Bsc_Analysis_Option_Id <'||l_Anal_Opt_Rec.Bsc_Analysis_Option_Id||'>');
1379 --DBMS_OUTPUT.PUT_LINE('l_Anal_Opt_Rec.Bsc_Parent_Option_Id <'||l_Anal_Opt_Rec.Bsc_Parent_Option_Id||'>');
1380 --DBMS_OUTPUT.PUT_LINE('l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id <'||l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id||'>');
1381 --DBMS_OUTPUT.PUT_LINE('l_Anal_Opt_Rec.Bsc_Analysis_Group_Id <'||l_Anal_Opt_Rec.Bsc_Analysis_Group_Id||'>');
1382 IF (l_AnaOpt_Delete) THEN
1383 SELECT MAX(Analysis_Group_Id) INTO l_Group_ID
1384 FROM BSC_KPI_ANALYSIS_OPTIONS_B
1385 WHERE Indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id;
1386
1387 IF (l_Group_ID = 0) THEN
1388 l_Anal_Opt_Rec.Bsc_Parent_Option_Id := NULL;
1389 l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id := NULL;
1390 ELSIF (l_Group_ID = 1) THEN
1391 l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id := NULL;
1392 IF (l_Anal_Opt_Rec.Bsc_Parent_Option_Id IS NULL) THEN
1393 l_Anal_Opt_Rec.Bsc_Parent_Option_Id := 0;
1394 END IF;
1395 ELSE
1396 IF (l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id IS NULL) THEN
1397 l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id := 0;
1398 END IF;
1399 IF (l_Anal_Opt_Rec.Bsc_Parent_Option_Id IS NULL) THEN
1400 l_Anal_Opt_Rec.Bsc_Parent_Option_Id := 0;
1401 END IF;
1402 END IF;
1403 IF ((l_Anal_Opt_Rec.Bsc_Analysis_Option_Id IS NOT NULL) AND
1404 (l_Anal_Opt_Rec.Bsc_Parent_Option_Id IS NOT NULL) AND
1405 (l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id IS NOT NULL)) THEN
1406 DELETE FROM BSC_KPI_ANALYSIS_OPTIONS_B
1407 WHERE indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
1408 AND analysis_group_id = NVL(l_Anal_Opt_Rec.Bsc_Analysis_Group_Id, 2)
1409 AND option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id
1410 AND parent_option_id = l_Anal_Opt_Rec.Bsc_Parent_Option_Id
1411 AND grandparent_option_id = l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id;
1412
1413 DELETE FROM BSC_KPI_ANALYSIS_OPTIONS_TL
1414 WHERE indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
1415 AND analysis_group_id = NVL(l_Anal_Opt_Rec.Bsc_Analysis_Group_Id, 2)
1416 AND option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id
1417 AND parent_option_id = l_Anal_Opt_Rec.Bsc_Parent_Option_Id
1418 AND grandparent_option_id = l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id;
1419
1420 ELSIF ((l_Anal_Opt_Rec.Bsc_Analysis_Option_Id IS NOT NULL) AND
1421 (l_Anal_Opt_Rec.Bsc_Parent_Option_Id IS NOT NULL)) THEN
1422 DELETE FROM BSC_KPI_ANALYSIS_OPTIONS_B
1423 WHERE indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
1424 AND analysis_group_id = NVL(l_Anal_Opt_Rec.Bsc_Analysis_Group_Id, 1)
1425 AND option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id
1426 AND parent_option_id = l_Anal_Opt_Rec.Bsc_Parent_Option_Id;
1427
1428 DELETE FROM BSC_KPI_ANALYSIS_OPTIONS_TL
1429 WHERE indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
1430 AND analysis_group_id = NVL(l_Anal_Opt_Rec.Bsc_Analysis_Group_Id, 1)
1431 AND option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id
1432 AND parent_option_id = l_Anal_Opt_Rec.Bsc_Parent_Option_Id;
1433
1434 ELSE
1435 DELETE FROM BSC_KPI_ANALYSIS_OPTIONS_B
1436 WHERE indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
1437 AND analysis_group_id = NVL(l_Anal_Opt_Rec.Bsc_Analysis_Group_Id, 0)
1438 AND option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id;
1439
1440 DELETE FROM BSC_KPI_ANALYSIS_OPTIONS_TL
1441 WHERE indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
1442 AND analysis_group_id = NVL(l_Anal_Opt_Rec.Bsc_Analysis_Group_Id, 0)
1443 AND option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id;
1444
1445 END IF;
1446 END IF;
1447 BSC_ANALYSIS_OPTION_PUB.Delete_Analysis_Measures( p_commit
1448 ,l_Anal_Opt_Rec
1449 ,x_return_status
1450 ,x_msg_count
1451 ,x_msg_data);
1452
1453 --DBMS_OUTPUT.PUT_LINE(' Swap_Option_Id( p_Kpi_Id <'||l_Anal_Opt_Rec.Bsc_Kpi_Id ||'>');
1454 --DBMS_OUTPUT.PUT_LINE(' Swap_Option_Id( p_group_id <'||l_Anal_Opt_Rec.Bsc_Analysis_Group_Id||'>');
1455 --DBMS_OUTPUT.PUT_LINE(' Swap_Option_Id( p_parent_option_Id <'||NVL(l_Parent_Opt_Id, 0)||'>');
1456 --DBMS_OUTPUT.PUT_LINE(' Swap_Option_Id( p_grand_parent_option_Id <'||NVL(l_Gra_Parent_Opt_Id, 0)||'>');
1457 BSC_ANALYSIS_OPTION_PVT.Swap_Option_Id
1458 ( p_Kpi_Id => l_Anal_Opt_Rec.Bsc_Kpi_Id
1459 , p_group_id => l_Anal_Opt_Rec.Bsc_Analysis_Group_Id
1460 , p_parent_option_Id => NVL(l_Parent_Opt_Id, 0)
1461 , p_grand_parent_option_Id => NVL(l_Gra_Parent_Opt_Id, 0)
1462 );
1463 BSC_ANALYSIS_OPTION_PVT.Set_Default_Value
1464 ( p_Kpi_Id => l_Anal_Opt_Rec.Bsc_Kpi_Id
1465 , p_group_id => l_Anal_Opt_Rec.Bsc_Analysis_Group_Id
1466 , p_parent_option_Id => NVL(l_Parent_Opt_Id, 0)
1467 , p_grand_parent_option_Id => NVL(l_Gra_Parent_Opt_Id, 0)
1468 , p_option_Id => NVL(l_Anal_Opt_Rec.Bsc_Analysis_Option_Id,0)
1469 );
1470
1471 /*IF(l_Anal_Opt_Rec.Bsc_Analysis_Group_Id =0) THEN
1472 SELECT DEFAULT_VALUE
1473 INTO l_default_value
1474 FROM BSC_KPI_ANALYSIS_GROUPS
1475 WHERE INDICATOR = l_Anal_Opt_Rec.Bsc_Kpi_Id
1476 AND ANALYSIS_GROUP_ID = 0;
1477
1478 IF (l_default_value = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id) THEN
1479 l_default_value := 0;
1480 ELSIF(l_default_value>l_Anal_Opt_Rec.Bsc_Analysis_Option_Id) THEN
1481 l_default_value := l_default_value - 1 ;
1482 END IF;
1483
1484 UPDATE BSC_KPI_ANALYSIS_GROUPS
1485 SET DEFAULT_VALUE = l_default_value
1486 WHERE INDiCATOR = l_Anal_Opt_Rec.Bsc_Kpi_Id
1487 AND ANALYSIS_GROUP_ID = 0;
1488 END IF;*/
1489 -- if there are any shared KPIs update those also.
1490 FOR cd IN c_kpi_ids LOOP
1491 l_Anal_Opt_Rec.Bsc_Kpi_Id := cd.Indicator;
1492 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id := cd.Indicator;
1493 IF (l_AnaOpt_Delete) THEN
1494 IF ((l_Anal_Opt_Rec.Bsc_Analysis_Option_Id IS NOT NULL) AND
1495 (l_Anal_Opt_Rec.Bsc_Parent_Option_Id IS NOT NULL) AND
1496 (l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id IS NOT NULL)) THEN
1497 delete from BSC_KPI_ANALYSIS_OPTIONS_B
1498 where indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
1499 and analysis_group_id = NVL(l_Anal_Opt_Rec.Bsc_Analysis_Group_Id, 2)
1500 and option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id
1501 and parent_option_id = l_Anal_Opt_Rec.Bsc_Parent_Option_Id
1502 and grandparent_option_id = l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id;
1503
1504 delete from BSC_KPI_ANALYSIS_OPTIONS_TL
1505 where indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
1506 and analysis_group_id = NVL(l_Anal_Opt_Rec.Bsc_Analysis_Group_Id, 2)
1507 and option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id
1508 and parent_option_id = l_Anal_Opt_Rec.Bsc_Parent_Option_Id
1509 and grandparent_option_id = l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id;
1510 ELSIF ((l_Anal_Opt_Rec.Bsc_Analysis_Option_Id IS NOT NULL) AND
1511 (l_Anal_Opt_Rec.Bsc_Parent_Option_Id IS NOT NULL)) THEN
1512 delete from BSC_KPI_ANALYSIS_OPTIONS_B
1513 where indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
1514 and analysis_group_id = NVL(l_Anal_Opt_Rec.Bsc_Analysis_Group_Id, 1)
1515 and option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id
1516 and parent_option_id = l_Anal_Opt_Rec.Bsc_Parent_Option_Id;
1517
1518 delete from BSC_KPI_ANALYSIS_OPTIONS_TL
1519 where indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
1520 and analysis_group_id = NVL(l_Anal_Opt_Rec.Bsc_Analysis_Group_Id, 1)
1521 and option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id
1522 and parent_option_id = l_Anal_Opt_Rec.Bsc_Parent_Option_Id;
1523 ELSE
1524 delete from BSC_KPI_ANALYSIS_OPTIONS_B
1525 where indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
1526 and analysis_group_id = NVL(l_Anal_Opt_Rec.Bsc_Analysis_Group_Id, 0)
1527 and option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id;
1528
1529 delete from BSC_KPI_ANALYSIS_OPTIONS_TL
1530 where indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
1531 and analysis_group_id = NVL(l_Anal_Opt_Rec.Bsc_Analysis_Group_Id, 0)
1532 and option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id;
1533 END IF;
1534 END IF;
1535 BSC_ANALYSIS_OPTION_PUB.Delete_Analysis_Measures( p_commit
1536 ,l_Anal_Opt_Rec
1537 ,x_return_status
1538 ,x_msg_count
1539 ,x_msg_data);
1540
1541 --BSC_ANALYSIS_OPTION_PVT.Swap_Option_Id( p_Kpi_Id => l_Anal_Opt_Rec.Bsc_Kpi_Id);
1542 BSC_ANALYSIS_OPTION_PVT.Swap_Option_Id
1543 ( p_Kpi_Id => l_Anal_Opt_Rec.Bsc_Kpi_Id
1544 , p_group_id => l_Anal_Opt_Rec.Bsc_Analysis_Group_Id
1545 , p_parent_option_Id => NVL(l_Parent_Opt_Id, 0)
1546 , p_grand_parent_option_Id => NVL(l_Gra_Parent_Opt_Id, 0)
1547 );
1548 BSC_ANALYSIS_OPTION_PVT.Set_Default_Value
1549 ( p_Kpi_Id => l_Anal_Opt_Rec.Bsc_Kpi_Id
1550 , p_group_id => l_Anal_Opt_Rec.Bsc_Analysis_Group_Id
1551 , p_parent_option_Id => NVL(l_Parent_Opt_Id, 0)
1552 , p_grand_parent_option_Id => NVL(l_Gra_Parent_Opt_Id, 0)
1553 , p_option_Id => NVL(l_Anal_Opt_Rec.Bsc_Analysis_Option_Id,0)
1554 );
1555 -- update default option for the shared KPIs
1556 BSC_KPI_PVT.Set_Default_Option
1557 ( p_commit => FND_API.G_FALSE
1558 , p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
1559 , x_return_status => x_return_status
1560 , x_msg_count => x_msg_count
1561 , x_msg_data => x_msg_data
1562 );
1563 END LOOP;
1564 IF (p_commit = FND_API.G_TRUE) THEN
1565 COMMIT;
1566 END IF;
1567 EXCEPTION
1568 WHEN FND_API.G_EXC_ERROR THEN
1569 ROLLBACK TO DeleteBSCAnaOptPVT;
1570 FND_MSG_PUB.Count_And_Get
1571 ( p_encoded => FND_API.G_FALSE
1572 , p_count => x_msg_count
1573 , p_data => x_msg_data
1574 );
1575 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1576 x_return_status := FND_API.G_RET_STS_ERROR;
1577 RAISE;
1578 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1579 ROLLBACK TO DeleteBSCAnaOptPVT;
1580 FND_MSG_PUB.Count_And_Get
1581 ( p_encoded => FND_API.G_FALSE
1582 , p_count => x_msg_count
1583 , p_data => x_msg_data
1584 );
1585 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1586 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1587 RAISE;
1588 WHEN NO_DATA_FOUND THEN
1589 ROLLBACK TO DeleteBSCAnaOptPVT;
1590 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1591 IF (x_msg_data IS NOT NULL) THEN
1592 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Options ';
1593 ELSE
1594 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Options ';
1595 END IF;
1596 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1597 RAISE;
1598 WHEN OTHERS THEN
1599 ROLLBACK TO DeleteBSCAnaOptPVT;
1600 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1601 IF (x_msg_data IS NOT NULL) THEN
1602 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Options ';
1603 ELSE
1604 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Options ';
1605 END IF;
1606 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1607 RAISE;
1608 END Delete_Analysis_Options;
1609
1610 /************************************************************************************
1611 ************************************************************************************/
1612
1613 --: This procedure assigns the given measure to the given analysis option.
1614 --: This procedure is part of the Analysis Option API.
1615
1616 procedure Create_Analysis_Measures(
1617 p_commit IN varchar2 -- := FND_API.G_FALSE
1618 ,p_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
1619 ,x_return_status OUT NOCOPY varchar2
1620 ,x_msg_count OUT NOCOPY number
1621 ,x_msg_data OUT NOCOPY varchar2
1622 ) is
1623
1624 l_count number;
1625
1626 begin
1627 FND_MSG_PUB.Initialize;
1628 x_return_status := FND_API.G_RET_STS_SUCCESS;
1629 SAVEPOINT CreateBSCAnaMeasPVT;
1630 -- Check that valid Kpi id was entered.
1631 if p_Anal_Opt_Rec.Bsc_Kpi_Id is not null then
1632 /*l_count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_KPIS_B'
1633 ,'indicator'
1634 ,p_Anal_Opt_Rec.Bsc_Kpi_Id);*/
1635 SELECT COUNT(0)
1636 INTO l_count
1637 FROM BSC_KPIS_B
1638 WHERE INDICATOR = p_Anal_Opt_Rec.Bsc_Kpi_Id;
1639 if l_count = 0 then
1640 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_KPI_ID');
1641 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Anal_Opt_Rec.Bsc_Kpi_Id);
1642 FND_MSG_PUB.ADD;
1643 RAISE FND_API.G_EXC_ERROR;
1644 end if;
1645 else
1646 FND_MESSAGE.SET_NAME('BSC','BSC_NO_KPI_ID_ENTERED');
1647 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Anal_Opt_Rec.Bsc_Kpi_Id);
1648 FND_MSG_PUB.ADD;
1649 RAISE FND_API.G_EXC_ERROR;
1650 end if;
1651
1652
1653 -- If the Option Id is zero for all groups then there is nothing to do.
1654 if (p_Anal_Opt_Rec.Bsc_Option_Group0 = 0 and
1655 p_Anal_Opt_Rec.Bsc_Option_Group1 = 0 and
1656 p_Anal_Opt_Rec.Bsc_Option_Group2 = 0 and
1657 p_Anal_Opt_Rec.Bsc_Dataset_Series_Id = 0 and
1658 p_Anal_Opt_Rec.Bsc_New_Kpi <> 'Y') then
1659
1660 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_OPTION_ID');
1661 FND_MESSAGE.SET_TOKEN('BSC_OPTION', p_Anal_Opt_Rec.Bsc_Option_Group0);
1662 FND_MSG_PUB.ADD;
1663 RAISE FND_API.G_EXC_ERROR;
1664
1665 else
1666
1667 g_db_object := 'BSC_KPI_ANALYSIS_MEASURES_B';
1668
1669 -- Insert pertaining values into table bsc_kpi_analysis_measures_b.
1670 insert into BSC_KPI_ANALYSIS_MEASURES_B( indicator
1671 ,ANALYSIS_OPTION0
1672 ,ANALYSIS_OPTION1
1673 ,ANALYSIS_OPTION2
1674 ,SERIES_ID
1675 ,DATASET_ID
1676 ,AXIS
1677 ,SERIES_TYPE
1678 ,STACK_SERIES_ID
1679 ,BM_FLAG
1680 ,BUDGET_FLAG
1681 ,DEFAULT_VALUE
1682 ,SERIES_COLOR
1683 ,BM_COLOR
1684 ,PROTOTYPE_FLAG
1685 ,KPI_MEASURE_ID)
1686 values( p_Anal_Opt_Rec.Bsc_Kpi_Id
1687 ,p_Anal_Opt_Rec.Bsc_Option_Group0
1688 ,p_Anal_Opt_Rec.Bsc_Option_Group1
1689 ,p_Anal_Opt_Rec.Bsc_Option_Group2
1690 ,p_Anal_Opt_Rec.Bsc_Dataset_Series_Id
1691 ,p_Anal_Opt_Rec.Bsc_Dataset_Id
1692 ,p_Anal_Opt_Rec.Bsc_Dataset_Axis
1693 ,p_Anal_Opt_Rec.Bsc_Dataset_Series_Type
1694 ,p_Anal_Opt_Rec.Bsc_Dataset_Stack_Series_Id
1695 ,p_Anal_Opt_Rec.Bsc_Dataset_Bm_Flag
1696 ,p_Anal_Opt_Rec.Bsc_Dataset_Budget_Flag
1697 ,p_Anal_Opt_Rec.Bsc_Dataset_Default_Value
1698 ,p_Anal_Opt_Rec.Bsc_Dataset_Series_Color
1699 ,p_Anal_Opt_Rec.Bsc_Dataset_Bm_Color
1700 ,p_Anal_Opt_Rec.Bsc_Measure_Prototype_Flag
1701 ,p_Anal_Opt_Rec.Bsc_Kpi_Measure_Id);
1702
1703 g_db_object := 'BSC_KPI_ANALYSIS_MEASURES_TL';
1704
1705 -- Insert pertaining values into table bsc_kpi_analysis_measures_tl.
1706 insert into BSC_KPI_ANALYSIS_MEASURES_TL( indicator
1707 ,analysis_option0
1708 ,analysis_option1
1709 ,analysis_option2
1710 ,series_id
1711 ,language
1712 ,source_lang
1713 ,name
1714 ,help)
1715 select p_Anal_Opt_Rec.Bsc_Kpi_Id
1716 ,p_Anal_Opt_Rec.Bsc_Option_Group0
1717 ,p_Anal_Opt_Rec.Bsc_Option_Group1
1718 ,p_Anal_Opt_Rec.Bsc_Option_Group2
1719 ,p_Anal_Opt_Rec.Bsc_Dataset_Series_Id
1720 ,L.LANGUAGE_CODE
1721 ,userenv('LANG')
1722 ,p_Anal_Opt_Rec.Bsc_Measure_Long_Name
1723 ,p_Anal_Opt_Rec.Bsc_Measure_Help
1724 from FND_LANGUAGES L
1725 where L.INSTALLED_FLAG in ('I', 'B')
1726 and not exists
1727 (select NULL
1728 from BSC_KPI_ANALYSIS_MEASURES_TL T
1729 where T.indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
1730 and T.analysis_option0 = p_Anal_Opt_Rec.Bsc_Option_Group0
1731 and T.analysis_option1 = p_Anal_Opt_Rec.Bsc_Option_Group1
1732 and T.analysis_option2 = p_Anal_Opt_Rec.Bsc_Option_Group2
1733 and T.series_id = p_Anal_Opt_Rec.Bsc_Dataset_Series_Id
1734 and T.LANGUAGE = L.LANGUAGE_CODE);
1735
1736 if (p_commit = FND_API.G_TRUE) then
1737 commit;
1738 end if;
1739
1740 end if;
1741
1742 EXCEPTION
1743 WHEN FND_API.G_EXC_ERROR THEN
1744 ROLLBACK TO CreateBSCAnaMeasPVT;
1745 FND_MSG_PUB.Count_And_Get
1746 ( p_encoded => FND_API.G_FALSE
1747 , p_count => x_msg_count
1748 , p_data => x_msg_data
1749 );
1750 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1751 x_return_status := FND_API.G_RET_STS_ERROR;
1752 RAISE;
1753 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1754 ROLLBACK TO CreateBSCAnaMeasPVT;
1755 FND_MSG_PUB.Count_And_Get
1756 ( p_encoded => FND_API.G_FALSE
1757 , p_count => x_msg_count
1758 , p_data => x_msg_data
1759 );
1760 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1761 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1762 RAISE;
1763 WHEN NO_DATA_FOUND THEN
1764 ROLLBACK TO CreateBSCAnaMeasPVT;
1765 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1766 IF (x_msg_data IS NOT NULL) THEN
1767 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Create_Analysis_Measures ';
1768 ELSE
1769 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Create_Analysis_Measures ';
1770 END IF;
1771 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1772 RAISE;
1773 WHEN OTHERS THEN
1774 ROLLBACK TO CreateBSCAnaMeasPVT;
1775 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1776 IF (x_msg_data IS NOT NULL) THEN
1777 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Create_Analysis_Measures ';
1778 ELSE
1779 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Create_Analysis_Measures ';
1780 END IF;
1781 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1782 RAISE;
1783 end Create_Analysis_Measures;
1784
1785 /************************************************************************************
1786 ************************************************************************************/
1787
1788 procedure Retrieve_Analysis_Measures
1789 (
1790 p_commit IN varchar2 -- := FND_API.G_FALSE
1791 , p_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
1792 , x_Anal_Opt_Rec IN OUT NOCOPY BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
1793 , x_return_status OUT NOCOPY varchar2
1794 , x_msg_count OUT NOCOPY number
1795 , x_msg_data OUT NOCOPY varchar2
1796 ) is
1797 begin
1798 FND_MSG_PUB.Initialize;
1799 x_return_status := FND_API.G_RET_STS_SUCCESS;
1800 g_db_object := 'Retrieve_Analysis_Measures';
1801 IF (p_Anal_Opt_Rec.Bsc_Dataset_Series_Id IS NULL) THEN
1802 select distinct series_id
1803 ,dataset_id
1804 ,axis
1805 ,series_type
1806 ,stack_series_id
1807 ,bm_flag
1808 ,budget_flag
1809 ,default_value
1810 ,series_color
1811 ,bm_color
1812 ,prototype_flag
1813 ,name
1814 ,help
1815 ,kpi_measure_id
1816 into x_Anal_Opt_Rec.Bsc_Dataset_Series_Id
1817 ,x_Anal_Opt_Rec.Bsc_Dataset_Id
1818 ,x_Anal_Opt_Rec.Bsc_Dataset_Axis
1819 ,x_Anal_Opt_Rec.Bsc_Dataset_Series_Type
1820 ,x_Anal_Opt_Rec.Bsc_Dataset_Stack_Series_Id
1821 ,x_Anal_Opt_Rec.Bsc_Dataset_Bm_Flag
1822 ,x_Anal_Opt_Rec.Bsc_Dataset_Budget_Flag
1823 ,x_Anal_Opt_Rec.Bsc_Dataset_Default_Value
1824 ,x_Anal_Opt_Rec.Bsc_Dataset_Series_Color
1825 ,x_Anal_Opt_Rec.Bsc_Dataset_Bm_Color
1826 ,x_Anal_Opt_Rec.Bsc_Measure_Prototype_Flag
1827 ,x_Anal_Opt_Rec.Bsc_Measure_Long_Name
1828 ,x_Anal_Opt_Rec.Bsc_Measure_Help
1829 ,x_Anal_Opt_Rec.Bsc_Kpi_Measure_Id
1830 from BSC_KPI_ANALYSIS_MEASURES_VL
1831 where indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
1832 and analysis_option0 = p_Anal_Opt_Rec.Bsc_Option_Group0
1833 and analysis_option1 = p_Anal_Opt_Rec.Bsc_Option_Group1
1834 and analysis_option2 = p_Anal_Opt_Rec.Bsc_Option_Group2;
1835 ELSE
1836
1837 select distinct dataset_id
1838 ,axis
1839 ,series_type
1840 ,stack_series_id
1841 ,bm_flag
1842 ,budget_flag
1843 ,default_value
1844 ,series_color
1845 ,bm_color
1846 ,prototype_flag
1847 ,name
1848 ,help
1849 into x_Anal_Opt_Rec.Bsc_Dataset_Id
1850 ,x_Anal_Opt_Rec.Bsc_Dataset_Axis
1851 ,x_Anal_Opt_Rec.Bsc_Dataset_Series_Type
1852 ,x_Anal_Opt_Rec.Bsc_Dataset_Stack_Series_Id
1853 ,x_Anal_Opt_Rec.Bsc_Dataset_Bm_Flag
1854 ,x_Anal_Opt_Rec.Bsc_Dataset_Budget_Flag
1855 ,x_Anal_Opt_Rec.Bsc_Dataset_Default_Value
1856 ,x_Anal_Opt_Rec.Bsc_Dataset_Series_Color
1857 ,x_Anal_Opt_Rec.Bsc_Dataset_Bm_Color
1858 ,x_Anal_Opt_Rec.Bsc_Measure_Prototype_Flag
1859 ,x_Anal_Opt_Rec.Bsc_Measure_Long_Name
1860 ,x_Anal_Opt_Rec.Bsc_Measure_Help
1861 from BSC_KPI_ANALYSIS_MEASURES_VL
1862 where indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
1863 and analysis_option0 = p_Anal_Opt_Rec.Bsc_Option_Group0
1864 and analysis_option1 = p_Anal_Opt_Rec.Bsc_Option_Group1
1865 and analysis_option2 = p_Anal_Opt_Rec.Bsc_Option_Group2
1866 and series_id = p_Anal_Opt_Rec.Bsc_Dataset_Series_Id;
1867
1868 END IF;
1869
1870 if (p_commit = FND_API.G_TRUE) then
1871 commit;
1872 end if;
1873
1874 EXCEPTION
1875 WHEN FND_API.G_EXC_ERROR THEN
1876 FND_MSG_PUB.Count_And_Get
1877 ( p_encoded => FND_API.G_FALSE
1878 , p_count => x_msg_count
1879 , p_data => x_msg_data
1880 );
1881 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1882 x_return_status := FND_API.G_RET_STS_ERROR;
1883 RAISE;
1884 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1885 FND_MSG_PUB.Count_And_Get
1886 ( p_encoded => FND_API.G_FALSE
1887 , p_count => x_msg_count
1888 , p_data => x_msg_data
1889 );
1890 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1891 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1892 RAISE;
1893 WHEN NO_DATA_FOUND THEN
1894 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1895 FND_MSG_PUB.Initialize;
1896 FND_MESSAGE.SET_NAME('BSC','BSC_NO_VALUE_FOUND');
1897 FND_MESSAGE.SET_TOKEN('BSC_OBJECT', g_db_object);
1898 FND_MSG_PUB.ADD;
1899 RAISE FND_API.G_EXC_ERROR;
1900 WHEN OTHERS THEN
1901 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1902 IF (x_msg_data IS NOT NULL) THEN
1903 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Retrieve_Analysis_Measures ';
1904 ELSE
1905 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Retrieve_Analysis_Measures ';
1906 END IF;
1907 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1908 RAISE;
1909 end Retrieve_Analysis_Measures;
1910
1911 /************************************************************************************
1912 ************************************************************************************/
1913
1914 procedure Update_Analysis_Measures
1915 (
1916 p_commit IN varchar2 -- := FND_API.G_FALSE
1917 , p_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
1918 , x_return_status OUT NOCOPY VARCHAR2
1919 , x_msg_count OUT NOCOPY NUMBER
1920 , x_msg_data OUT NOCOPY VARCHAR2
1921 ) is
1922 l_Anal_Opt_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
1923 l_count number;
1924 l_source BSC_SYS_DATASETS_B.SOURCE%TYPE;
1925 l_sname BSC_KPIS_B.SHORT_NAME%TYPE;
1926 l_kpi_measure_id BSC_KPI_ANALYSIS_MEASURES_B.KPI_MEASURE_ID%TYPE;
1927 l_dataset_color_change BOOLEAN := FALSE;
1928 l_old_color_method bsc_sys_datasets_b.color_method%TYPE;
1929 l_new_color_method bsc_sys_datasets_b.color_method%TYPE;
1930 begin
1931 FND_MSG_PUB.Initialize;
1932 x_return_status := FND_API.G_RET_STS_SUCCESS;
1933 SAVEPOINT UpdateBSCAnaMeasPVT;
1934 -- Check that valid Kpi id was entered.
1935 if p_Anal_Opt_Rec.Bsc_Kpi_Id is not null then
1936 /*l_count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_KPIS_B'
1937 ,'indicator'
1938 ,p_Anal_Opt_Rec.Bsc_Kpi_Id); */
1939 SELECT COUNT(0)
1940 INTO l_count
1941 FROM BSC_KPIS_B
1942 WHERE INDICATOR = p_Anal_Opt_Rec.Bsc_Kpi_Id;
1943 if l_count = 0 then
1944 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_KPI_ID');
1945 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Anal_Opt_Rec.Bsc_Kpi_Id);
1946 FND_MSG_PUB.ADD;
1947 RAISE FND_API.G_EXC_ERROR;
1948 end if;
1949 else
1950 FND_MESSAGE.SET_NAME('BSC','BSC_NO_KPI_ID_ENTERED');
1951 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Anal_Opt_Rec.Bsc_Kpi_Id);
1952 FND_MSG_PUB.ADD;
1953 RAISE FND_API.G_EXC_ERROR;
1954 end if;
1955
1956 -- Not all values will be passed. We need to make sure values not passed are not
1957 -- changed by procedure, therefore we get what is there before we do any updates.
1958 Retrieve_Analysis_Measures ( p_commit
1959 ,p_Anal_Opt_Rec
1960 ,l_Anal_Opt_Rec
1961 ,x_return_status
1962 ,x_msg_count
1963 ,x_msg_data);
1964
1965 -- update LOCAL language ,source language, group id and level Id values with PASSED values.
1966 l_Anal_Opt_Rec.Bsc_Language := p_Anal_Opt_Rec.Bsc_Language;
1967 l_Anal_Opt_Rec.Bsc_Source_Language := p_Anal_Opt_Rec.Bsc_Source_Language;
1968 l_Anal_Opt_Rec.Bsc_Kpi_Id := p_Anal_Opt_Rec.Bsc_Kpi_Id;
1969 l_Anal_Opt_Rec.Bsc_Option_Group0 := p_Anal_Opt_Rec.Bsc_Option_Group0;
1970 l_Anal_Opt_Rec.Bsc_Option_Group1 := p_Anal_Opt_Rec.Bsc_Option_Group1;
1971 l_Anal_Opt_Rec.Bsc_Option_Group2 := p_Anal_Opt_Rec.Bsc_Option_Group2;
1972
1973
1974 -- Copy PASSED Record values into LOCAL Record values for the PASSED Record values
1975 -- which are NOT NULL.
1976 if p_Anal_Opt_Rec.Bsc_Dataset_Series_Id is not null then
1977 l_Anal_Opt_Rec.Bsc_Dataset_Series_Id := p_Anal_Opt_Rec.Bsc_Dataset_Series_Id;
1978 end if;
1979 if p_Anal_Opt_Rec.Bsc_Dataset_Id is not null then
1980 if l_Anal_Opt_Rec.Bsc_Dataset_Id <> p_Anal_Opt_Rec.Bsc_Dataset_Id then
1981
1982 SELECT color_method
1983 INTO l_old_color_method
1984 FROM bsc_sys_datasets_b
1985 WHERE dataset_id = p_Anal_Opt_Rec.Bsc_Dataset_Id;
1986
1987 SELECT color_method
1988 INTO l_new_color_method
1989 FROM bsc_sys_datasets_b
1990 WHERE dataset_id = l_Anal_Opt_Rec.Bsc_Dataset_Id;
1991
1992 -- ppandey - Even if dataset id is changed, reset the color method
1993 -- only if color method of two dataset are different.
1994 IF(l_old_color_method <> l_new_color_method) THEN
1995 l_dataset_color_change := TRUE;
1996 END IF;
1997
1998 l_Anal_Opt_Rec.Bsc_Dataset_Id := p_Anal_Opt_Rec.Bsc_Dataset_Id;
1999
2000 -- Set Objective Structural Change
2001
2002 SELECT source
2003 INTO l_source
2004 FROM bsc_sys_datasets_b
2005 WHERE dataset_id = p_Anal_Opt_Rec.Bsc_Dataset_Id;
2006
2007 SELECT SHORT_NAME
2008 INTO l_sname
2009 FROM BSC_KPIS_B
2010 WHERE INDICATOR = l_Anal_Opt_Rec.Bsc_Kpi_Id;
2011
2012 IF (p_Anal_Opt_Rec.Bsc_Change_Action_Flag = FND_API.G_TRUE AND ((l_source = 'BSC') OR (l_sname IS NOT NULL) )) THEN
2013 BSC_DESIGNER_PVT.ActionFlag_Change( l_Anal_Opt_Rec.Bsc_Kpi_Id ,
2014 BSC_DESIGNER_PVT.G_ActionFlag.GAA_Structure );
2015 END IF;
2016 end if;
2017 end if;
2018 if p_Anal_Opt_Rec.Bsc_Dataset_Axis is not null then
2019 l_Anal_Opt_Rec.Bsc_Dataset_Axis := p_Anal_Opt_Rec.Bsc_Dataset_Axis;
2020 end if;
2021 if p_Anal_Opt_Rec.Bsc_Dataset_Series_Type is not null then
2022 l_Anal_Opt_Rec.Bsc_Dataset_Series_Type := p_Anal_Opt_Rec.Bsc_Dataset_Series_Type;
2023 end if;
2024 l_Anal_Opt_Rec.Bsc_Dataset_Stack_Series_Id := p_Anal_Opt_Rec.Bsc_Dataset_Stack_Series_Id;
2025 if p_Anal_Opt_Rec.Bsc_Dataset_Bm_Flag is not null then
2026 l_Anal_Opt_Rec.Bsc_Dataset_Bm_Flag := p_Anal_Opt_Rec.Bsc_Dataset_Bm_Flag;
2027 end if;
2028 if p_Anal_Opt_Rec.Bsc_Dataset_Budget_Flag is not null then
2029 l_Anal_Opt_Rec.Bsc_Dataset_Budget_Flag := p_Anal_Opt_Rec.Bsc_Dataset_Budget_Flag;
2030 end if;
2031
2032 if p_Anal_Opt_Rec.Bsc_Dataset_Default_Value is not null then
2033 l_Anal_Opt_Rec.Bsc_Dataset_Default_Value := p_Anal_Opt_Rec.Bsc_Dataset_Default_Value;
2034 end if;
2035
2036 if p_Anal_Opt_Rec.Bsc_Dataset_Series_Color is not null then
2037 l_Anal_Opt_Rec.Bsc_Dataset_Series_Color := p_Anal_Opt_Rec.Bsc_Dataset_Series_Color;
2038 end if;
2039 if p_Anal_Opt_Rec.Bsc_Dataset_Bm_Color is not null then
2040 l_Anal_Opt_Rec.Bsc_Dataset_Bm_Color := p_Anal_Opt_Rec.Bsc_Dataset_Bm_Color;
2041 end if;
2042 if p_Anal_Opt_Rec.Bsc_Measure_Prototype_Flag is not null then
2043 l_Anal_Opt_Rec.Bsc_Measure_Prototype_Flag := p_Anal_Opt_Rec.Bsc_Measure_Prototype_Flag;
2044 end if;
2045 if p_Anal_Opt_Rec.Bsc_Measure_Long_Name is not null then
2046 l_Anal_Opt_Rec.Bsc_Measure_Long_Name := p_Anal_Opt_Rec.Bsc_Measure_Long_Name;
2047 end if;
2048 if p_Anal_Opt_Rec.Bsc_Measure_Help is not null then
2049 l_Anal_Opt_Rec.Bsc_Measure_Help := p_Anal_Opt_Rec.Bsc_Measure_Help;
2050 end if;
2051 IF (p_Anal_Opt_Rec.Bsc_Dataset_Series_Id IS NULL) THEN
2052 update BSC_KPI_ANALYSIS_MEASURES_B
2053 set series_id = l_Anal_Opt_Rec.Bsc_Dataset_Series_Id
2054 ,dataset_id = l_Anal_Opt_Rec.Bsc_Dataset_Id
2055 ,axis = l_Anal_Opt_Rec.Bsc_Dataset_Axis
2056 ,series_type = l_Anal_Opt_Rec.Bsc_Dataset_Series_Type
2057 ,stack_series_id = l_Anal_Opt_Rec.Bsc_Dataset_Stack_Series_Id
2058 ,bm_flag = l_Anal_Opt_Rec.Bsc_Dataset_Bm_Flag
2059 ,budget_flag = l_Anal_Opt_Rec.Bsc_Dataset_Budget_Flag
2060 ,default_value = l_Anal_Opt_Rec.Bsc_Dataset_Default_Value
2061 ,series_color = l_Anal_Opt_Rec.Bsc_Dataset_Series_Color
2062 ,bm_color = l_Anal_Opt_Rec.Bsc_Dataset_Bm_Color
2063 ,prototype_flag = l_Anal_Opt_Rec.Bsc_Measure_Prototype_Flag
2064 where indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
2065 and analysis_option0 = l_Anal_Opt_Rec.Bsc_Option_Group0
2066 and analysis_option1 = l_Anal_Opt_Rec.Bsc_Option_Group1
2067 and analysis_option2 = l_Anal_Opt_Rec.Bsc_Option_Group2;
2068
2069 update BSC_KPI_ANALYSIS_MEASURES_TL
2070 set name = l_Anal_Opt_Rec.Bsc_Measure_Long_Name
2071 ,help = l_Anal_Opt_Rec.Bsc_Measure_Help
2072 ,source_lang = userenv('LANG')
2073 where indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
2074 and analysis_option0 = l_Anal_Opt_Rec.Bsc_Option_Group0
2075 and analysis_option1 = l_Anal_Opt_Rec.Bsc_Option_Group1
2076 and analysis_option2 = l_Anal_Opt_Rec.Bsc_Option_Group2
2077 and indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
2078 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
2079 ELSE
2080 update BSC_KPI_ANALYSIS_MEASURES_B
2081 set series_id = l_Anal_Opt_Rec.Bsc_Dataset_Series_Id
2082 ,dataset_id = l_Anal_Opt_Rec.Bsc_Dataset_Id
2083 ,axis = l_Anal_Opt_Rec.Bsc_Dataset_Axis
2084 ,series_type = l_Anal_Opt_Rec.Bsc_Dataset_Series_Type
2085 ,stack_series_id = l_Anal_Opt_Rec.Bsc_Dataset_Stack_Series_Id
2086 ,bm_flag = l_Anal_Opt_Rec.Bsc_Dataset_Bm_Flag
2087 ,budget_flag = l_Anal_Opt_Rec.Bsc_Dataset_Budget_Flag
2088 ,default_value = l_Anal_Opt_Rec.Bsc_Dataset_Default_Value
2089 ,series_color = l_Anal_Opt_Rec.Bsc_Dataset_Series_Color
2090 ,bm_color = l_Anal_Opt_Rec.Bsc_Dataset_Bm_Color
2091 ,prototype_flag = l_Anal_Opt_Rec.Bsc_Measure_Prototype_Flag
2092 where indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
2093 and analysis_option0 = l_Anal_Opt_Rec.Bsc_Option_Group0
2094 and analysis_option1 = l_Anal_Opt_Rec.Bsc_Option_Group1
2095 and analysis_option2 = l_Anal_Opt_Rec.Bsc_Option_Group2
2096 and series_id = l_Anal_Opt_Rec.Bsc_Dataset_Series_Id;
2097
2098 update BSC_KPI_ANALYSIS_MEASURES_TL
2099 set name = l_Anal_Opt_Rec.Bsc_Measure_Long_Name
2100 ,help = l_Anal_Opt_Rec.Bsc_Measure_Help
2101 ,source_lang = userenv('LANG')
2102 where indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
2103 and analysis_option0 = l_Anal_Opt_Rec.Bsc_Option_Group0
2104 and analysis_option1 = l_Anal_Opt_Rec.Bsc_Option_Group1
2105 and analysis_option2 = l_Anal_Opt_Rec.Bsc_Option_Group2
2106 and indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
2107 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
2108 and series_id = l_Anal_Opt_Rec.Bsc_Dataset_Series_Id;
2109
2110 --DBMS_OUTPUT.PUT_LINE(' l_Anal_Opt_Rec.Bsc_Kpi_Id '||l_Anal_Opt_Rec.Bsc_Kpi_Id);
2111 --DBMS_OUTPUT.PUT_LINE(' l_Anal_Opt_Rec.Bsc_Option_Group0 '||l_Anal_Opt_Rec.Bsc_Option_Group0);
2112 --DBMS_OUTPUT.PUT_LINE(' l_Anal_Opt_Rec.Bsc_Option_Group1 '||l_Anal_Opt_Rec.Bsc_Option_Group1);
2113 --DBMS_OUTPUT.PUT_LINE(' l_Anal_Opt_Rec.Bsc_Option_Group2 '||l_Anal_Opt_Rec.Bsc_Option_Group2);
2114 --DBMS_OUTPUT.PUT_LINE(' l_Anal_Opt_Rec.Bsc_Dataset_Series_Id '||l_Anal_Opt_Rec.Bsc_Dataset_Series_Id);
2115 --DBMS_OUTPUT.PUT_LINE(' l_Anal_Opt_Rec.Bsc_Measure_Long_Name '||l_Anal_Opt_Rec.Bsc_Measure_Long_Name);
2116 --DBMS_OUTPUT.PUT_LINE(' l_Anal_Opt_Rec.Bsc_Dataset_Id '||l_Anal_Opt_Rec.Bsc_Dataset_Id);
2117 --DBMS_OUTPUT.PUT_LINE(' . ');
2118
2119 END IF;
2120
2121 IF (l_dataset_color_change) THEN
2122 SELECT kpi_measure_id
2123 INTO l_kpi_measure_id
2124 FROM bsc_kpi_analysis_measures_b
2125 WHERE indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
2126 AND dataset_id = l_Anal_Opt_Rec.Bsc_Dataset_Id;
2127
2128 BSC_COLOR_RANGES_PUB.Delete_Color_Prop_Ranges (p_objective_id => p_Anal_Opt_Rec.Bsc_Kpi_Id
2129 ,p_kpi_measure_id => l_kpi_measure_id
2130 ,p_cascade_shared => TRUE
2131 ,x_return_status => x_return_status
2132 ,x_msg_count => x_msg_count
2133 ,x_msg_data => x_msg_data);
2134
2135 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2136 RAISE FND_API.G_EXC_ERROR;
2137 END IF;
2138
2139 BSC_COLOR_RANGES_PUB.Create_Def_Color_Prop_Ranges(p_objective_id => p_Anal_Opt_Rec.Bsc_Kpi_Id
2140 ,p_kpi_measure_id => l_kpi_measure_id
2141 ,p_cascade_shared => TRUE
2142 ,x_return_status => x_return_status
2143 ,x_msg_count => x_msg_count
2144 ,x_msg_data => x_msg_data);
2145
2146 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2147 RAISE FND_API.G_EXC_ERROR;
2148 END IF;
2149 END IF;
2150 if (p_commit = FND_API.G_TRUE) then
2151 commit;
2152 end if;
2153
2154 EXCEPTION
2155 WHEN FND_API.G_EXC_ERROR THEN
2156 ROLLBACK TO UpdateBSCAnaMeasPVT;
2157 FND_MSG_PUB.Count_And_Get
2158 ( p_encoded => FND_API.G_FALSE
2159 , p_count => x_msg_count
2160 , p_data => x_msg_data
2161 );
2162 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2163 x_return_status := FND_API.G_RET_STS_ERROR;
2164 RAISE;
2165 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2166 ROLLBACK TO UpdateBSCAnaMeasPVT;
2167 FND_MSG_PUB.Count_And_Get
2168 ( p_encoded => FND_API.G_FALSE
2169 , p_count => x_msg_count
2170 , p_data => x_msg_data
2171 );
2172 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2173 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2174 RAISE;
2175 WHEN NO_DATA_FOUND THEN
2176 ROLLBACK TO UpdateBSCAnaMeasPVT;
2177 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2178 IF (x_msg_data IS NOT NULL) THEN
2179 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Update_Analysis_Measures ';
2180 ELSE
2181 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Update_Analysis_Measures ';
2182 END IF;
2183 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
2184 RAISE;
2185 WHEN OTHERS THEN
2186 ROLLBACK TO UpdateBSCAnaMeasPVT;
2187 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2188 IF (x_msg_data IS NOT NULL) THEN
2189 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Update_Analysis_Measures ';
2190 ELSE
2191 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Update_Analysis_Measures ';
2192 END IF;
2193 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2194 RAISE;
2195 end Update_Analysis_Measures;
2196
2197 --Dont call the private API directly. Color table data depending on the kpi_measure_id
2198 --need to be deleted
2199
2200 procedure Delete_Analysis_Measures(
2201 p_commit IN varchar2 -- := FND_API.G_FALSE
2202 ,p_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
2203 ,x_return_status OUT NOCOPY varchar2
2204 ,x_msg_count OUT NOCOPY number
2205 ,x_msg_data OUT NOCOPY varchar2
2206 ) is
2207
2208 l_count number;
2209
2210 CURSOR c_GrandParent_Option IS
2211 SELECT A.Option_ID Option_Id
2212 , B.Option_ID Parent_Option_Id
2213 , C.Option_ID GrandParent_Option_Id
2214 FROM BSC_KPI_ANALYSIS_OPTIONS_B A
2215 , BSC_KPI_ANALYSIS_OPTIONS_B B
2216 , BSC_KPI_ANALYSIS_OPTIONS_B C
2217 WHERE A.Indicator = B.Indicator
2218 AND A.Indicator = C.Indicator
2219 AND A.Analysis_Group_Id = 0
2220 AND B.Analysis_Group_Id = 1
2221 AND C.Analysis_Group_Id = 2
2222 AND A.Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id;
2223
2224 CURSOR c_Parent_Option IS
2225 SELECT A.Option_ID Option_Id
2226 , B.Option_ID Parent_Option_Id
2227 FROM BSC_KPI_ANALYSIS_OPTIONS_B A
2228 , BSC_KPI_ANALYSIS_OPTIONS_B B
2229 WHERE A.Indicator = B.Indicator
2230 AND A.Analysis_Group_Id = 0
2231 AND B.Analysis_Group_Id = 1
2232 AND A.Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id;
2233
2234
2235 CURSOR c_Grand_Parent_depend IS
2236 SELECT Dependency_Flag
2237 FROM BSC_KPI_ANALYSIS_GROUPS
2238 WHERE Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
2239 AND Analysis_Group_Id = 2;
2240
2241 CURSOR c_Parent_depend IS
2242 SELECT Dependency_Flag
2243 FROM BSC_KPI_ANALYSIS_GROUPS
2244 WHERE Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
2245 AND Analysis_Group_Id = 1;
2246
2247 l_Parent_Analysis NUMBER := 0;
2248 l_Grand_Parent_Analysis NUMBER := 0;
2249 l_Parent_Dependent NUMBER := -1;
2250 l_GrandParent_Dependent NUMBER := -1;
2251
2252 l_parent_Exist BOOLEAN;
2253 l_grand_parent_Exist BOOLEAN;
2254
2255 l_Temp number;
2256 l_Delete_Flag BOOLEAN := FALSE;
2257 BEGIN
2258 FND_MSG_PUB.Initialize;
2259 SAVEPOINT DeleteBSCAnaMeasPVT;
2260 x_return_status := FND_API.G_RET_STS_SUCCESS;
2261 -- Check that valid Kpi id was entered.
2262 IF (p_Anal_Opt_Rec.Bsc_Kpi_Id IS NOT NULL) THEN
2263 l_count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_KPIS_B', 'indicator', p_Anal_Opt_Rec.Bsc_Kpi_Id);
2264 IF (l_count = 0) THEN
2265 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_KPI_ID');
2266 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Anal_Opt_Rec.Bsc_Kpi_Id);
2267 FND_MSG_PUB.ADD;
2268 RAISE FND_API.G_EXC_ERROR;
2269 END IF;
2270 ELSE
2271 FND_MESSAGE.SET_NAME('BSC','BSC_NO_KPI_ID_ENTERED');
2272 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Anal_Opt_Rec.Bsc_Kpi_Id);
2273 FND_MSG_PUB.ADD;
2274 RAISE FND_API.G_EXC_ERROR;
2275 END IF;
2276 l_Count := 0;
2277 IF (c_Grand_Parent_depend%ISOPEN) THEN
2278 CLOSE c_Grand_Parent_depend;
2279 END IF;
2280 OPEN c_Grand_Parent_depend;
2281 FETCH c_Grand_Parent_depend INTO l_GrandParent_Dependent;
2282 IF (c_Grand_Parent_depend%NOTFOUND) THEN
2283 l_GrandParent_Dependent := 0;
2284 END IF;
2285 CLOSE c_Grand_Parent_depend;
2286
2287 IF (c_Parent_depend%ISOPEN) THEN
2288 CLOSE c_Parent_depend;
2289 END IF;
2290 OPEN c_Parent_depend;
2291 FETCH c_Parent_depend INTO l_Parent_Dependent;
2292 IF (c_Parent_depend%NOTFOUND) THEN
2293 l_Parent_Dependent := 0;
2294 END IF;
2295 CLOSE c_Parent_depend;
2296 --DBMS_OUTPUT.PUT_LINE('p_Anal_Opt_Rec.Bsc_Analysis_Option_Id <'||p_Anal_Opt_Rec.Bsc_Analysis_Option_Id||'>');
2297 --DBMS_OUTPUT.PUT_LINE('p_Anal_Opt_Rec.Bsc_Parent_Option_Id <'||p_Anal_Opt_Rec.Bsc_Parent_Option_Id||'>');
2298 --DBMS_OUTPUT.PUT_LINE('p_Anal_Opt_Rec.Bsc_Grandparent_Option_Id <'||p_Anal_Opt_Rec.Bsc_Grandparent_Option_Id||'>');
2299 --DBMS_OUTPUT.PUT_LINE('l_GrandParent_Dependent <'||l_GrandParent_Dependent||'>');
2300 --DBMS_OUTPUT.PUT_LINE('l_Parent_Dependent <'||l_Parent_Dependent||'>');
2301 IF p_Anal_Opt_Rec.Bsc_Dataset_Series_Id IS NOT NULL THEN
2302 DELETE FROM BSC_KPI_ANALYSIS_MEASURES_B
2303 WHERE indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
2304 AND analysis_option0 = p_Anal_Opt_Rec.Bsc_Option_Group0
2305 AND analysis_option1 = p_Anal_Opt_Rec.Bsc_Option_Group1
2306 AND analysis_option2 = p_Anal_Opt_Rec.Bsc_Option_Group2
2307 AND series_id = p_Anal_Opt_Rec.Bsc_Dataset_Series_Id;
2308
2309 DELETE FROM BSC_KPI_ANALYSIS_MEASURES_TL
2310 WHERE indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
2311 AND analysis_option0 = p_Anal_Opt_Rec.Bsc_Option_Group0
2312 AND analysis_option1 = p_Anal_Opt_Rec.Bsc_Option_Group1
2313 AND analysis_option2 = p_Anal_Opt_Rec.Bsc_Option_Group2
2314 AND series_id = p_Anal_Opt_Rec.Bsc_Dataset_Series_Id;
2315 ELSIF ((p_Anal_Opt_Rec.Bsc_Analysis_Option_Id IS NOT NULL) AND
2316 (p_Anal_Opt_Rec.Bsc_Parent_Option_Id IS NOT NULL) AND
2317 (p_Anal_Opt_Rec.Bsc_Grandparent_Option_Id IS NOT NULL)) THEN
2318 FOR cd IN c_GrandParent_Option LOOP
2319 SELECT COUNT(*) INTO l_COunt
2320 FROM BSC_KPI_ANALYSIS_MEASURES_B
2321 WHERE Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
2322 AND analysis_option0 = cd.Option_Id
2323 AND analysis_option1 = cd.Parent_Option_Id
2324 AND analysis_option2 = cd.GrandParent_Option_Id;
2325 IF (l_Count <> 0) THEN
2326 l_Delete_Flag := TRUE;
2327 l_Count := 0;
2328 IF ((l_GrandParent_Dependent > 0) AND (l_Parent_Dependent > 0)) THEN
2329 IF ((is_GrandParent_Exists(p_Anal_Opt_Rec.Bsc_Kpi_Id, cd.Option_Id, 2)) AND
2330 (is_Parent_Exists(p_Anal_Opt_Rec.Bsc_Kpi_Id, cd.Parent_Option_Id, 2))) THEN
2331 SELECT COUNT(*) INTO l_Count
2332 FROM BSC_KPI_ANALYSIS_OPTIONS_B A
2333 , BSC_KPI_ANALYSIS_MEASURES_B D
2334 WHERE D.Indicator = A.Indicator
2335 AND A.Analysis_Group_Id = 2
2336 AND A.Option_Id = D.Analysis_Option2
2337 AND A.Parent_Option_Id = D.Analysis_Option1
2338 AND A.GrandParent_Option_Id = D.Analysis_Option0
2339 AND D.Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
2340 AND D.Analysis_Option0 = cd.Option_Id
2341 AND D.Analysis_Option1 = cd.Parent_Option_Id
2342 AND D.Analysis_Option2 = cd.GrandParent_Option_Id;
2343
2344 l_Parent_Analysis := cd.Parent_Option_Id;
2345 l_Grand_Parent_Analysis := cd.GrandParent_Option_Id;
2346 ELSE
2347 l_Count := 1;
2348 l_Parent_Analysis := cd.Parent_Option_Id;
2349 l_Grand_Parent_Analysis := cd.GrandParent_Option_Id;
2350 IF (is_Parent_Exists(p_Anal_Opt_Rec.Bsc_Kpi_Id, cd.Parent_Option_Id, 2)) THEN
2351 IF(is_not_Child(p_Anal_Opt_Rec.Bsc_Kpi_Id, cd.Option_Id,cd.Parent_Option_Id, 1)) THEN
2352 l_Parent_Analysis := 0;
2353 END IF;
2354 ELSIF(is_not_Child(p_Anal_Opt_Rec.Bsc_Kpi_Id, cd.Option_Id,cd.Parent_Option_Id, 1)) THEN
2355 l_Parent_Analysis := 0;
2356 END IF;
2357 IF (NOT is_GrandParent_Exists(p_Anal_Opt_Rec.Bsc_Kpi_Id, cd.Option_Id, 2)) THEN
2358 l_Grand_Parent_Analysis := 0;
2359 END IF;
2360 END IF;
2361 --DBMS_OUTPUT.PUT_LINE('BOTH -- 0 <'||l_Count||'> <'||cd.Option_Id||'> <'||cd.Parent_Option_Id||'> <'||cd.GrandParent_Option_Id||'>');
2362 ELSIF (l_Parent_Dependent > 0) THEN
2363 IF (is_Parent_Exists(p_Anal_Opt_Rec.Bsc_Kpi_Id, cd.Option_Id, 1)) THEN
2364 SELECT COUNT(*) INTO l_Count
2365 FROM BSC_KPI_ANALYSIS_OPTIONS_B A
2366 , BSC_KPI_ANALYSIS_MEASURES_B D
2367 WHERE D.Indicator = A.Indicator
2368 AND A.Analysis_Group_Id = 1
2369 AND A.Option_Id = D.Analysis_Option1
2370 AND A.Parent_Option_Id = D.Analysis_Option0
2371 AND D.Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
2372 AND D.Analysis_Option0 = cd.Option_Id
2373 AND D.Analysis_Option1 = cd.Parent_Option_Id;
2374 l_Parent_Analysis := cd.Parent_Option_Id;
2375 l_Grand_Parent_Analysis := cd.GrandParent_Option_Id;
2376 ELSE
2377 l_Count := 1;
2378 l_Parent_Analysis := 0;
2379 l_Grand_Parent_Analysis := cd.GrandParent_Option_Id;
2380 END IF;
2381 --DBMS_OUTPUT.PUT_LINE('L_PARENT_DEPENDENT -- <'||l_Count||'> <'||cd.Option_Id||'> <'||cd.Parent_Option_Id||'> <'||cd.GrandParent_Option_Id||'>');
2382 ELSIF (l_GrandParent_Dependent > 0) THEN
2383 IF (is_Parent_Exists(p_Anal_Opt_Rec.Bsc_Kpi_Id, cd.Parent_Option_Id, 2)) THEN
2384 SELECT COUNT(*) INTO l_Count
2385 FROM BSC_KPI_ANALYSIS_OPTIONS_B A
2386 , BSC_KPI_ANALYSIS_MEASURES_B D
2387 WHERE D.Indicator = A.Indicator
2388 AND A.Analysis_Group_Id = 2
2389 AND A.Parent_Option_Id = D.Analysis_Option1
2390 AND A.GrandParent_Option_Id = 0
2391 AND D.Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
2392 AND D.Analysis_Option1 = cd.Parent_Option_Id
2393 AND D.Analysis_Option2 = cd.GrandParent_Option_Id;
2394
2395 l_Parent_Analysis := cd.Parent_Option_Id;
2396 l_Grand_Parent_Analysis := cd.GrandParent_Option_Id;
2397 IF(is_not_Child(p_Anal_Opt_Rec.Bsc_Kpi_Id, cd.Parent_Option_Id,cd.GrandParent_Option_Id, 2)) THEN
2398 l_Grand_Parent_Analysis := 0;
2399 END IF;
2400
2401 ELSE
2402 l_Count := 1;
2403 l_Parent_Analysis := cd.Parent_Option_Id;
2404 l_Grand_Parent_Analysis := cd.GrandParent_Option_Id;
2405 END IF;
2406 --DBMS_OUTPUT.PUT_LINE('L_GRANDPARENT_DEPENDENT -- <'||l_Count||'> <'||cd.Option_Id||'> <'||cd.Parent_Option_Id||'> <'||cd.GrandParent_Option_Id||'>');
2407 END IF;
2408 IF (((l_Parent_Dependent = 0) AND (l_GrandParent_Dependent = 0)) OR (l_Count <> 0)) THEN
2409 IF((l_Parent_Dependent = 0) AND (l_GrandParent_Dependent = 0)) THEN
2410 l_Parent_Analysis := cd.Parent_Option_Id;
2411 l_Grand_Parent_Analysis := cd.GrandParent_Option_Id;
2412 END IF;
2413 UPDATE BSC_KPI_ANALYSIS_MEASURES_B
2414 SET Indicator = -999
2415 WHERE Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
2416 AND analysis_option0 = cd.Option_Id
2417 AND analysis_option1 = l_Parent_Analysis
2418 AND analysis_option2 = l_Grand_Parent_Analysis;
2419
2420 UPDATE BSC_KPI_ANALYSIS_MEASURES_TL
2421 SET Indicator = -999
2422 WHERE Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
2423 AND analysis_option0 = cd.Option_Id
2424 AND analysis_option1 = l_Parent_Analysis
2425 AND analysis_option2 = l_Grand_Parent_Analysis;
2426 --DBMS_OUTPUT.PUT_LINE('INDEPENDENT -- <'||l_Count||'> <'||cd.Option_Id||'> <'||cd.Parent_Option_Id||'> <'||cd.GrandParent_Option_Id||'>');
2427 END IF;
2428 END IF;
2429 END LOOP;
2430 IF (l_Delete_Flag) THEN
2431 DELETE FROM BSC_KPI_ANALYSIS_MEASURES_B
2432 WHERE Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id;
2433
2434 DELETE FROM BSC_KPI_ANALYSIS_MEASURES_TL
2435 WHERE Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id;
2436
2437 UPDATE BSC_KPI_ANALYSIS_MEASURES_B
2438 SET Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
2439 WHERE Indicator = -999;
2440
2441 UPDATE BSC_KPI_ANALYSIS_MEASURES_TL
2442 SET Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
2443 WHERE Indicator = -999;
2444 END IF;
2445 ELSIF ((p_Anal_Opt_Rec.Bsc_Analysis_Option_Id IS NOT NULL) AND
2446 (p_Anal_Opt_Rec.Bsc_Parent_Option_Id IS NOT NULL)) THEN
2447 FOR cd IN c_Parent_Option LOOP
2448 l_Delete_Flag := TRUE;
2449 IF (l_Parent_Dependent = -1) THEN
2450 SELECT Dependency_Flag INTO l_Parent_Dependent
2451 FROM BSC_KPI_ANALYSIS_GROUPS
2452 WHERE Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
2453 AND Analysis_Group_Id = 1;
2454
2455 SELECT COUNT(*)
2456 INTO l_count
2457 FROM BSC_KPI_ANALYSIS_OPTIONS_B
2458 WHERE INDICATOR = p_Anal_Opt_Rec.Bsc_Kpi_Id
2459 AND Analysis_Group_Id = 1;
2460 END IF;
2461 IF (l_Parent_Dependent > 0) THEN
2462 IF (is_Parent_Exists(p_Anal_Opt_Rec.Bsc_Kpi_Id, cd.Option_Id, 1)) THEN
2463 SELECT COUNT(*) INTO l_Count
2464 FROM BSC_KPI_ANALYSIS_OPTIONS_B A
2465 , BSC_KPI_ANALYSIS_MEASURES_B D
2466 WHERE D.Indicator = A.Indicator
2467 AND A.Analysis_Group_Id = 1
2468 AND A.Option_Id = D.Analysis_Option1
2469 AND A.Parent_Option_Id = D.Analysis_Option0
2470 AND D.Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
2471 AND D.Analysis_Option0 = cd.Option_Id
2472 AND D.Analysis_Option1 = cd.Parent_Option_Id;
2473 l_Parent_Analysis := cd.Parent_Option_Id;
2474 ELSE
2475 l_Count := 1;
2476 l_Parent_Analysis := 0;
2477 END IF;
2478 END IF;
2479 IF ((l_Parent_Dependent = 0) OR (l_Count <> 0)) THEN
2480 IF(l_Parent_Dependent = 0) THEN
2481 l_Parent_Analysis := cd.Parent_Option_Id;
2482 END IF;
2483
2484 UPDATE BSC_KPI_ANALYSIS_MEASURES_B
2485 SET Indicator = -999
2486 WHERE Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
2487 AND analysis_option0 = cd.Option_Id
2488 AND analysis_option1 = l_Parent_Analysis
2489 AND analysis_option2 = 0;
2490
2491 UPDATE BSC_KPI_ANALYSIS_MEASURES_TL
2492 SET Indicator = -999
2493 WHERE Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
2494 AND analysis_option0 = cd.Option_Id
2495 AND analysis_option1 = l_Parent_Analysis
2496 AND analysis_option2 = 0;
2497 END IF;
2498 END LOOP;
2499 IF (l_Delete_Flag) THEN
2500 DELETE FROM BSC_KPI_ANALYSIS_MEASURES_B
2501 WHERE Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id;
2502
2503 DELETE FROM BSC_KPI_ANALYSIS_MEASURES_TL
2504 WHERE Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id;
2505
2506 UPDATE BSC_KPI_ANALYSIS_MEASURES_B
2507 SET Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
2508 WHERE Indicator = -999;
2509
2510 UPDATE BSC_KPI_ANALYSIS_MEASURES_TL
2511 SET Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
2512 WHERE Indicator = -999;
2513 END IF;
2514 ELSE
2515 DELETE FROM BSC_KPI_ANALYSIS_MEASURES_B
2516 WHERE indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
2517 AND analysis_option0 = p_Anal_Opt_Rec.Bsc_Option_Group0
2518 AND analysis_option1 = p_Anal_Opt_Rec.Bsc_Option_Group1
2519 AND analysis_option2 = p_Anal_Opt_Rec.Bsc_Option_Group2;
2520
2521 DELETE FROM BSC_KPI_ANALYSIS_MEASURES_TL
2522 WHERE indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
2523 AND analysis_option0 = p_Anal_Opt_Rec.Bsc_Option_Group0
2524 AND analysis_option1 = p_Anal_Opt_Rec.Bsc_Option_Group1
2525 AND analysis_option2 = p_Anal_Opt_Rec.Bsc_Option_Group2;
2526 END IF;
2527 IF (p_commit = FND_API.G_TRUE) THEN
2528 COMMIT;
2529 END IF;
2530 --DBMS_OUTPUT.PUT_LINE('coming out Delete_Analysis_Measures ');
2531 EXCEPTION
2532 WHEN FND_API.G_EXC_ERROR THEN
2533 IF (c_Grand_Parent_depend%ISOPEN) THEN
2534 CLOSE c_Grand_Parent_depend;
2535 END IF;
2536 IF (c_Parent_depend%ISOPEN) THEN
2537 CLOSE c_Parent_depend;
2538 END IF;
2539 ROLLBACK TO DeleteBSCAnaMeasPVT;
2540 FND_MSG_PUB.Count_And_Get
2541 ( p_encoded => FND_API.G_FALSE
2542 , p_count => x_msg_count
2543 , p_data => x_msg_data
2544 );
2545 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2546 x_return_status := FND_API.G_RET_STS_ERROR;
2547 RAISE;
2548 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2549 IF (c_Grand_Parent_depend%ISOPEN) THEN
2550 CLOSE c_Grand_Parent_depend;
2551 END IF;
2552 IF (c_Parent_depend%ISOPEN) THEN
2553 CLOSE c_Parent_depend;
2554 END IF;
2555 ROLLBACK TO DeleteBSCAnaMeasPVT;
2556 FND_MSG_PUB.Count_And_Get
2557 ( p_encoded => FND_API.G_FALSE
2558 , p_count => x_msg_count
2559 , p_data => x_msg_data
2560 );
2561 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2562 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2563 RAISE;
2564 WHEN NO_DATA_FOUND THEN
2565 IF (c_Grand_Parent_depend%ISOPEN) THEN
2566 CLOSE c_Grand_Parent_depend;
2567 END IF;
2568 IF (c_Parent_depend%ISOPEN) THEN
2569 CLOSE c_Parent_depend;
2570 END IF;
2571 ROLLBACK TO DeleteBSCAnaMeasPVT;
2572 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2573 IF (x_msg_data IS NOT NULL) THEN
2574 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Measures ';
2575 ELSE
2576 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Measures ';
2577 END IF;
2578 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
2579 RAISE;
2580 WHEN OTHERS THEN
2581 IF (c_Grand_Parent_depend%ISOPEN) THEN
2582 CLOSE c_Grand_Parent_depend;
2583 END IF;
2584 IF (c_Parent_depend%ISOPEN) THEN
2585 CLOSE c_Parent_depend;
2586 END IF;
2587 ROLLBACK TO DeleteBSCAnaMeasPVT;
2588 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2589 IF (x_msg_data IS NOT NULL) THEN
2590 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Measures ';
2591 ELSE
2592 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Measures ';
2593 END IF;
2594 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2595 RAISE;
2596 END Delete_Analysis_Measures;
2597
2598 /************************************************************************************
2599 ************************************************************************************/
2600
2601 FUNCTION Delete_Analysis_Option
2602 ( p_kpi_id IN NUMBER
2603 , p_anal_option_id IN NUMBER
2604 , x_return_status OUT NOCOPY VARCHAR2
2605 , x_msg_count OUT NOCOPY NUMBER
2606 , x_msg_data OUT NOCOPY VARCHAR2
2607 , p_anal_group_id IN NUMBER DEFAULT 0
2608 ) RETURN VARCHAR2
2609 IS
2610 -- This function checks if an analysis option may be deleted. The checks are: If
2611 -- this is the last analysis Option then it may not be deleted. If it is used by a
2612 -- shared KPI then if it is being displayed then it may not be deleted. Any other
2613 -- result allows deletion.
2614 l_kpi_id NUMBER;
2615 l_value NUMBER; -- This variable will be used to store values
2616 -- for number of items, or for value of the
2617 -- shared flag, or for the display value.
2618
2619 --get shared indicators
2620 CURSOR c_kpi_ids IS
2621 SELECT DISTINCT A.Indicator
2622 , B.User_Level1
2623 FROM BSC_KPIS_B A
2624 , BSC_KPI_ANALYSIS_OPTIONS_B B
2625 WHERE Source_Indicator = p_kpi_id
2626 AND A.Indicator = B.Indicator
2627 AND B.analysis_group_id = p_anal_group_id
2628 AND B.option_id = p_anal_option_id
2629 AND Prototype_Flag <> BSC_KPI_PUB.Delete_Kpi_Flag;
2630 BEGIN
2631 FND_MSG_PUB.Initialize;
2632 x_return_status := FND_API.G_RET_STS_SUCCESS;
2633 -- First check if it is a Shared Kpi. If it is then no deletion.
2634 SELECT DISTINCT(Share_Flag)
2635 INTO l_value
2636 FROM BSC_KPIS_B
2637 WHERE indicator = p_kpi_id;
2638 IF (l_value = 2) then
2639 RETURN 'S';-- it is a shared kpi
2640 END IF;
2641
2642 -- Now check that this is not the last analysis Option, if it is then no deletion.
2643 SELECT COUNT(option_id)
2644 INTO l_value
2645 FROM BSC_KPI_ANALYSIS_OPTIONS_B
2646 WHERE indicator = p_kpi_id;
2647 IF (l_value < 2) THEN
2648 RETURN 'L';
2649 END IF;
2650
2651 -- Now find out NOCOPY if the indicator has any shared indicators, if not then deletion
2652 -- may proceed.
2653 SELECT COUNT(indicator)
2654 INTO l_value
2655 FROM BSC_KPIS_B
2656 WHERE source_indicator = p_kpi_id
2657 AND Prototype_Flag <> BSC_KPI_PUB.Delete_Kpi_Flag;
2658 IF (l_value = 0) then
2659 RETURN 'Y';
2660 END IF;
2661
2662 -- Now, if the analysis has come to this point, then it means that the Analysis Option
2663 -- belongs to a KPI that it is being shared. In order to delete, no Shared KPI must be
2664 -- displaying this Analysis Option.
2665
2666 -- We need to get the ids for all Shared Kpis for this Master kpi.
2667 FOR cd IN c_kpi_ids LOOP
2668 -- if the value is not zero (for any shared KPI) then option may not be deleted.
2669 IF (cd.User_Level1 <> 0) THEN
2670 RETURN 'D';
2671 END IF;
2672 END LOOP;
2673 -- If the analysis has come to this point then it means that the Analysis Option
2674 -- belong to a KPI that is being shared, but none of the Shared KPI is actually
2675 -- displaying the option therefore it may be deleted.
2676 RETURN 'Y';
2677 EXCEPTION
2678 WHEN FND_API.G_EXC_ERROR THEN
2679 FND_MSG_PUB.Count_And_Get
2680 ( p_encoded => FND_API.G_FALSE
2681 , p_count => x_msg_count
2682 , p_data => x_msg_data
2683 );
2684 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2685 x_return_status := FND_API.G_RET_STS_ERROR;
2686 RAISE;
2687 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2688 FND_MSG_PUB.Count_And_Get
2689 ( p_encoded => FND_API.G_FALSE
2690 , p_count => x_msg_count
2691 , p_data => x_msg_data
2692 );
2693 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2694 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2695 RAISE;
2696 WHEN NO_DATA_FOUND THEN
2697 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2698 IF (x_msg_data IS NOT NULL) THEN
2699 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Option ';
2700 ELSE
2701 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Option ';
2702 END IF;
2703 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
2704 RAISE;
2705 WHEN OTHERS THEN
2706 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2707 IF (x_msg_data IS NOT NULL) THEN
2708 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Option ';
2709 ELSE
2710 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Option ';
2711 END IF;
2712 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2713 RAISE;
2714 end Delete_Analysis_Option;
2715
2716 /**************************************************************************/
2717 PROCEDURE Initialize_Anal_Opt_Tbl
2718 (
2719 p_Kpi_id IN BSC_KPIS_B.indicator%TYPE
2720 , p_Anal_Opt_Tbl IN BSC_ANALYSIS_OPTION_PUB.Bsc_Anal_Opt_Tbl_Type
2721 , p_max_group_count IN NUMBER
2722 , p_Anal_Opt_Comb_Tbl IN BSC_ANALYSIS_OPTION_PUB.Anal_Opt_Comb_Num_Tbl_Type
2723 , p_Anal_Det_Opt_Tbl IN OUT NOCOPY BSC_ANALYSIS_OPTION_PUB.Bsc_Anal_Opt_Det_Tbl_Type
2724 )IS
2725 l_group_count NUMBER;
2726 l_option_id BSC_KPI_ANALYSIS_OPTIONS_B.Option_Id%TYPE;
2727 l_parent_option_id BSC_KPI_ANALYSIS_OPTIONS_B.Parent_Option_Id%TYPE;
2728 l_grand_parent_option_id BSC_KPI_ANALYSIS_OPTIONS_B.Grandparent_Option_Id%TYPE;
2729 l_dependent BSC_KPI_ANALYSIS_GROUPS.dependency_flag%TYPE;
2730 l_no_child NUMBER;
2731 l_Anal_grp_Id BSC_KPI_ANALYSIS_OPTIONS_B.Analysis_Group_Id%TYPE;
2732
2733
2734 CURSOR c_grp_one_details IS
2735 SELECT Option_Id,Parent_Option_Id,Grandparent_Option_Id
2736 FROM BSC_KPI_ANALYSIS_OPTIONS_B
2737 WHERE Indicator = p_Kpi_id
2738 AND Analysis_Group_Id = l_group_count
2739 AND Option_Id = l_option_id
2740 AND Parent_Option_Id = l_parent_option_id;
2741
2742 CURSOR c_grp_two_details IS
2743 SELECT Option_Id,Parent_Option_Id,Grandparent_Option_Id
2744 FROM BSC_KPI_ANALYSIS_OPTIONS_B
2745 WHERE Indicator = p_Kpi_id
2746 AND Analysis_Group_Id = l_group_count
2747 AND Option_Id = l_option_id
2748 AND Parent_Option_Id = l_parent_option_id
2749 AND Grandparent_Option_Id = l_grand_parent_option_id;
2750
2751
2752 CURSOR c_grp_zero_details IS
2753 SELECT Option_Id,Parent_Option_Id,Grandparent_Option_Id
2754 FROM BSC_KPI_ANALYSIS_OPTIONS_B
2755 WHERE Indicator = p_Kpi_id
2756 AND Analysis_Group_Id = l_group_count
2757 AND Option_Id = l_option_id;
2758
2759 BEGIN
2760
2761 l_group_count := 0;
2762
2763 WHILE( l_group_count <= (p_Anal_Opt_Tbl.COUNT - 1)) LOOP
2764 IF( l_group_count = 2 ) THEN
2765 l_option_id := p_Anal_Opt_Comb_Tbl(l_group_count);
2766 l_parent_option_id := p_Anal_Opt_Comb_Tbl(l_group_count - 1);
2767 l_grand_parent_option_id := p_Anal_Opt_Comb_Tbl(l_group_count - 2);
2768
2769 IF(c_grp_two_details%ISOPEN) THEN
2770 CLOSE c_grp_two_details;
2771 END IF;
2772
2773 OPEN c_grp_two_details;
2774 FETCH c_grp_two_details INTO l_option_id,l_parent_option_id,l_grand_parent_option_id;
2775 IF(c_grp_two_details%NOTFOUND) THEN
2776 l_option_id := p_Anal_Opt_Comb_Tbl(l_group_count);
2777 l_parent_option_id := p_Anal_Opt_Comb_Tbl(l_group_count - 1);
2778 IF(c_grp_one_details%ISOPEN) THEN
2779 CLOSE c_grp_two_details;
2780 END IF;
2781 OPEN c_grp_one_details;
2782 FETCH c_grp_one_details INTO l_option_id,l_parent_option_id,l_grand_parent_option_id;
2783 IF(c_grp_one_details%NOTFOUND) THEN
2784 l_option_id := p_Anal_Opt_Comb_Tbl(l_group_count);
2785 l_parent_option_id := 0;
2786 l_grand_parent_option_id := 0;
2787 END IF;
2788 CLOSE c_grp_one_details;
2789 END IF;
2790 CLOSE c_grp_two_details;
2791
2792 l_dependent := p_Anal_Opt_Tbl(l_group_count).Bsc_dependency_flag;
2793 l_no_child := 0;
2794 ELSIF( l_group_count = 1)THEN
2795
2796 IF(c_grp_one_details%ISOPEN) THEN
2797 CLOSE c_grp_two_details;
2798 END IF;
2799 l_option_id := p_Anal_Opt_Comb_Tbl(l_group_count);
2800 l_parent_option_id := p_Anal_Opt_Comb_Tbl(l_group_count - 1);
2801 OPEN c_grp_one_details;
2802 FETCH c_grp_one_details INTO l_option_id,l_parent_option_id,l_grand_parent_option_id;
2803 IF(c_grp_one_details%NOTFOUND) THEN
2804 l_option_id := p_Anal_Opt_Comb_Tbl(l_group_count);
2805 l_parent_option_id := 0;
2806 l_grand_parent_option_id := 0;
2807 END IF;
2808
2809 CLOSE c_grp_one_details;
2810
2811 l_dependent := p_Anal_Opt_Tbl(l_group_count).Bsc_dependency_flag;
2812 IF (p_Anal_Opt_Tbl.EXISTS(l_group_count + 1) AND (p_Anal_Opt_Tbl(l_group_count + 1).Bsc_dependency_flag =1)) THEN
2813 l_no_child := get_number_of_child
2814 ( p_Kpi_id => p_Kpi_id
2815 ,p_group_count => l_group_count
2816 ,p_Anal_Opt_Tbl => p_Anal_Opt_Tbl
2817 ,p_Anal_Opt_Comb_Tbl => p_Anal_Opt_Comb_Tbl
2818 );
2819
2820 ELSE
2821 l_no_child := 0;
2822 END IF;
2823 ELSE
2824 l_option_id := p_Anal_Opt_Comb_Tbl(l_group_count);
2825 OPEN c_grp_zero_details;
2826 FETCH c_grp_zero_details INTO l_option_id,l_parent_option_id,l_grand_parent_option_id;
2827 IF(c_grp_zero_details%NOTFOUND) THEN
2828 l_option_id := 0;
2829 l_parent_option_id := 0;
2830 l_grand_parent_option_id := 0;
2831 END IF;
2832 CLOSE c_grp_zero_details;
2833
2834
2835 l_dependent := 0;
2836 l_no_child := get_number_of_child
2837 ( p_Kpi_id => p_Kpi_id
2838 ,p_group_count => l_group_count
2839 ,p_Anal_Opt_Tbl => p_Anal_Opt_Tbl
2840 ,p_Anal_Opt_Comb_Tbl => p_Anal_Opt_Comb_Tbl
2841 );
2842
2843 END IF;
2844 p_Anal_Det_Opt_Tbl(l_group_count).Bsc_Option_Id := l_option_id;
2845 p_Anal_Det_Opt_Tbl(l_group_count).Bsc_Parent_Option_Id := l_parent_option_id;
2846 p_Anal_Det_Opt_Tbl(l_group_count).Bsc_Grandparent_Option_Id := l_grand_parent_option_id;
2847 p_Anal_Det_Opt_Tbl(l_group_count).Bsc_dependency_flag := l_dependent;
2848 p_Anal_Det_Opt_Tbl(l_group_count).No_of_child := l_no_child;
2849
2850 l_group_count := l_group_count + 1;
2851
2852 END LOOP;
2853
2854 END Initialize_Anal_Opt_Tbl;
2855 /*******************************************************************************/
2856
2857 FUNCTION Validate_If_single_Anal_Opt
2858 (
2859 p_Anal_Opt_Tbl IN BSC_ANALYSIS_OPTION_PUB.Bsc_Anal_Opt_Tbl_Type
2860
2861 )RETURN BOOLEAN
2862 IS
2863 l_Anal_Opt_Tbl BSC_ANALYSIS_OPTION_PUB.Bsc_Anal_Opt_Tbl_Type;
2864 l_count NUMBER;
2865
2866 BEGIN
2867 l_Anal_Opt_Tbl := p_Anal_Opt_Tbl;
2868 l_count := l_Anal_Opt_Tbl.COUNT - 1;
2869 IF(l_count = 2)THEN
2870 IF(((l_Anal_Opt_Tbl.EXISTS(l_count))AND(l_Anal_Opt_Tbl(l_count).Bsc_no_option_id =1)AND (l_Anal_Opt_Tbl(l_count).Bsc_dependency_flag =1))) THEN
2871 IF((l_Anal_Opt_Tbl.EXISTS(l_count -1)) AND (l_Anal_Opt_Tbl(l_count - 1).Bsc_no_option_id =1) AND(l_Anal_Opt_Tbl(l_count - 1).Bsc_dependency_flag =1)) THEN
2872 IF((l_Anal_Opt_Tbl.EXISTS(l_count-2)) AND (l_Anal_Opt_Tbl(l_count - 2).Bsc_no_option_id =1)) THEN
2873 RETURN TRUE;
2874 END IF;
2875 END IF;
2876 END IF;
2877 ELSE
2878 IF((l_Anal_Opt_Tbl.EXISTS(l_count))AND(l_Anal_Opt_Tbl(l_count).Bsc_no_option_id =1)AND((l_Anal_Opt_Tbl(l_count).Bsc_dependency_flag =1))) THEN
2879 IF((l_Anal_Opt_Tbl.EXISTS(l_count -1)) AND (l_Anal_Opt_Tbl(l_count - 1).Bsc_no_option_id =1)) THEN
2880 RETURN TRUE;
2881 END IF;
2882 END IF;
2883 END IF;
2884 RETURN FALSE;
2885 END Validate_If_single_Anal_Opt;
2886
2887 /************************************************************************************
2888
2889 ************************************************************************************/
2890
2891 PROCEDURE Delete_Ana_Opt_Mult_Groups
2892 ( p_commit IN VARCHAR2:=FND_API.G_FALSE
2893 , p_Kpi_id IN BSC_KPIS_B.indicator%TYPE
2894 , p_Anal_Opt_Tbl IN BSC_ANALYSIS_OPTION_PUB.Bsc_Anal_Opt_Tbl_Type
2895 , p_max_group_count IN NUMBER
2896 , p_Anal_Opt_Comb_Tbl IN BSC_ANALYSIS_OPTION_PUB.Anal_Opt_Comb_Num_Tbl_Type
2897 , p_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
2898 , x_return_status OUT NOCOPY VARCHAR2
2899 , x_msg_count OUT NOCOPY NUMBER
2900 , x_msg_data OUT NOCOPY VARCHAR2
2901 )IS
2902 l_count NUMBER;
2903 l_Anal_Opt_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
2904 l_Source VARCHAR2(3) := 'BSC';
2905 l_parent_option_id BSC_KPI_ANALYSIS_OPTIONS_B.Parent_Option_Id%TYPE;
2906 l_Anal_Det_Opt_Tbl BSC_ANALYSIS_OPTION_PUB.Bsc_Anal_Opt_Det_Tbl_Type;
2907 l_anal_opt_name VARCHAR2(3000);
2908 BEGIN
2909 FND_MSG_PUB.Initialize;
2910 x_return_status := FND_API.G_RET_STS_SUCCESS;
2911 SAVEPOINT DeleteBSCAnaOptMultGroups;
2912 l_Anal_Opt_Rec.Bsc_Kpi_Id := p_Kpi_id;
2913
2914 IF(Validate_If_single_Anal_Opt(p_Anal_Opt_Tbl)) THEN
2915 FND_MESSAGE.SET_NAME('BSC','BSC_LAST_AO_IN_KPI');
2916 FND_MSG_PUB.ADD;
2917 RAISE FND_API.G_EXC_ERROR;
2918 END IF;
2919
2920
2921 --BSC_D_NOT_DELETE_AO_DEPEN
2922
2923 BSC_ANALYSIS_OPTION_PVT.Initialize_Anal_Opt_Tbl
2924 (
2925 p_Kpi_id => p_Kpi_id
2926 ,p_Anal_Opt_Tbl => p_Anal_Opt_Tbl
2927 ,p_max_group_count => p_max_group_count
2928 ,p_Anal_Opt_Comb_Tbl => p_Anal_Opt_Comb_Tbl
2929 ,p_Anal_Det_Opt_Tbl => l_Anal_Det_Opt_Tbl
2930 );
2931
2932 l_count := l_Anal_Det_Opt_Tbl.COUNT - 1 ;
2933
2934 l_Anal_Opt_Rec.Bsc_Option_Group0 := p_Anal_Opt_Rec.Bsc_Option_Group0;
2935 l_Anal_Opt_Rec.Bsc_Option_Group1 := p_Anal_Opt_Rec.Bsc_Option_Group1;
2936 l_Anal_Opt_Rec.Bsc_Option_Group2 := p_Anal_Opt_Rec.Bsc_Option_Group2;
2937 l_Anal_Opt_Rec.Bsc_Dataset_Series_Id := p_Anal_Opt_Rec.Bsc_Dataset_Series_Id;
2938
2939 IF((l_Anal_Det_Opt_Tbl(l_count).Bsc_dependency_flag = 1)AND(l_Anal_Det_Opt_Tbl.EXISTS(l_count-1))AND(l_Anal_Det_Opt_Tbl(l_count-1).No_of_child <>0)) THEN
2940
2941 IF((l_Anal_Det_Opt_Tbl(l_count-1).No_of_child >1)AND(l_Anal_Det_Opt_Tbl(l_count).Bsc_Option_Id=0)) THEN
2942 FND_MESSAGE.SET_NAME('BSC','BSC_D_NOT_DELETE_AO_DEPEN');
2943 FND_MSG_PUB.ADD;
2944 RAISE FND_API.G_EXC_ERROR;
2945 END IF;
2946
2947 l_Anal_Opt_Rec.Bsc_Analysis_Group_Id := l_count;
2948 l_Anal_Opt_Rec.Bsc_Analysis_Option_Id := l_Anal_Det_Opt_Tbl(l_count).Bsc_Option_Id ;
2949 l_Anal_Opt_Rec.Bsc_Parent_Option_Id := l_Anal_Det_Opt_Tbl(l_count).Bsc_Parent_Option_Id ;
2950 l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id := l_Anal_Det_Opt_Tbl(l_count).Bsc_Grandparent_Option_Id;
2951
2952 BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Options
2953 ( p_commit => FND_API.G_FALSE
2954 , p_Anal_Opt_Rec => l_Anal_Opt_Rec
2955 , x_return_status => x_return_status
2956 , x_msg_count => x_msg_count
2957 , x_msg_data => x_msg_data
2958 );
2959
2960 IF((l_Anal_Det_Opt_Tbl.EXISTS(l_count-1))AND(l_Anal_Det_Opt_Tbl(l_count-1).No_of_child =1)) THEN
2961
2962 IF((l_Anal_Det_Opt_Tbl(l_count).Bsc_dependency_flag = 1)AND(l_Anal_Det_Opt_Tbl.EXISTS(l_count-2))AND(l_Anal_Det_Opt_Tbl(l_count-2).No_of_child >1)AND(l_Anal_Det_Opt_Tbl(l_count -1).Bsc_Option_Id=0)) THEN
2963 FND_MESSAGE.SET_NAME('BSC','BSC_D_NOT_DELETE_AO_DEPEN');
2964 FND_MSG_PUB.ADD;
2965 RAISE FND_API.G_EXC_ERROR;
2966 END IF;
2967
2968 l_Anal_Opt_Rec.Bsc_Analysis_Group_Id := l_count -1;
2969 l_Anal_Opt_Rec.Bsc_Analysis_Option_Id := l_Anal_Det_Opt_Tbl(l_count -1).Bsc_Option_Id ;
2970 l_Anal_Opt_Rec.Bsc_Parent_Option_Id := l_Anal_Det_Opt_Tbl(l_count -1).Bsc_Parent_Option_Id ;
2971 l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id := l_Anal_Det_Opt_Tbl(l_count -1).Bsc_Grandparent_Option_Id;
2972
2973 BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Options
2974 ( p_commit => FND_API.G_FALSE
2975 , p_Anal_Opt_Rec => l_Anal_Opt_Rec
2976 , x_return_status => x_return_status
2977 , x_msg_count => x_msg_count
2978 , x_msg_data => x_msg_data
2979 );
2980
2981 IF((l_Anal_Det_Opt_Tbl.EXISTS(l_count-2))AND(l_Anal_Det_Opt_Tbl(l_count-2).No_of_child =1)AND(l_Anal_Det_Opt_Tbl(l_count-1).Bsc_dependency_flag =1)) THEN
2982 l_Anal_Opt_Rec.Bsc_Analysis_Group_Id := l_count -2;
2983 l_Anal_Opt_Rec.Bsc_Analysis_Option_Id := l_Anal_Det_Opt_Tbl(l_count -2).Bsc_Option_Id ;
2984 l_Anal_Opt_Rec.Bsc_Parent_Option_Id := l_Anal_Det_Opt_Tbl(l_count -2).Bsc_Parent_Option_Id ;
2985 l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id := l_Anal_Det_Opt_Tbl(l_count -2).Bsc_Grandparent_Option_Id;
2986
2987 BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Options
2988 ( p_commit => FND_API.G_FALSE
2989 , p_Anal_Opt_Rec => l_Anal_Opt_Rec
2990 , x_return_status => x_return_status
2991 , x_msg_count => x_msg_count
2992 , x_msg_data => x_msg_data
2993 );
2994
2995
2996 END IF;
2997 END IF;
2998 ELSIF((l_Anal_Det_Opt_Tbl(l_count).Bsc_dependency_flag = 1)AND(l_Anal_Det_Opt_Tbl.EXISTS(l_count-1))AND(l_Anal_Det_Opt_Tbl(l_count-1).No_of_child =0)) THEN
2999 IF((l_Anal_Det_Opt_Tbl(l_count-1).Bsc_dependency_flag = 1)AND(l_Anal_Det_Opt_Tbl.EXISTS(l_count-2))AND(l_Anal_Det_Opt_Tbl(l_count-2).No_of_child =0)) THEN
3000
3001 l_Anal_Opt_Rec.Bsc_Analysis_Group_Id := l_count -2;
3002 l_Anal_Opt_Rec.Bsc_Analysis_Option_Id := l_Anal_Det_Opt_Tbl(l_count -2).Bsc_Option_Id ;
3003 l_Anal_Opt_Rec.Bsc_Parent_Option_Id := l_Anal_Det_Opt_Tbl(l_count -2).Bsc_Parent_Option_Id ;
3004 l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id := l_Anal_Det_Opt_Tbl(l_count -2).Bsc_Grandparent_Option_Id;
3005
3006 BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Options
3007 ( p_commit => FND_API.G_FALSE
3008 , p_Anal_Opt_Rec => l_Anal_Opt_Rec
3009 , x_return_status => x_return_status
3010 , x_msg_count => x_msg_count
3011 , x_msg_data => x_msg_data
3012 );
3013 ELSE
3014 IF((l_Anal_Det_Opt_Tbl(l_count-1).Bsc_dependency_flag = 1)AND(l_Anal_Det_Opt_Tbl.EXISTS(l_count-2))AND(l_Anal_Det_Opt_Tbl(l_count-2).No_of_child >1)AND(l_Anal_Det_Opt_Tbl(l_count -1).Bsc_Option_Id=0)) THEN
3015 FND_MESSAGE.SET_NAME('BSC','BSC_D_NOT_DELETE_AO_DEPEN');
3016 FND_MSG_PUB.ADD;
3017 RAISE FND_API.G_EXC_ERROR;
3018 END IF;
3019
3020 l_Anal_Opt_Rec.Bsc_Analysis_Group_Id := l_count -1;
3021 l_Anal_Opt_Rec.Bsc_Analysis_Option_Id := l_Anal_Det_Opt_Tbl(l_count -1).Bsc_Option_Id ;
3022 l_Anal_Opt_Rec.Bsc_Parent_Option_Id := l_Anal_Det_Opt_Tbl(l_count -1).Bsc_Parent_Option_Id ;
3023 l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id := l_Anal_Det_Opt_Tbl(l_count -1).Bsc_Grandparent_Option_Id;
3024
3025 BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Options
3026 ( p_commit => FND_API.G_FALSE
3027 , p_Anal_Opt_Rec => l_Anal_Opt_Rec
3028 , x_return_status => x_return_status
3029 , x_msg_count => x_msg_count
3030 , x_msg_data => x_msg_data
3031 );
3032
3033
3034
3035
3036 IF((l_Anal_Det_Opt_Tbl.EXISTS(l_count-2))AND(l_Anal_Det_Opt_Tbl(l_count-2).No_of_child =1)AND(l_Anal_Det_Opt_Tbl(l_count-1).Bsc_dependency_flag =1)) THEN
3037 l_Anal_Opt_Rec.Bsc_Analysis_Group_Id := l_count -2;
3038 l_Anal_Opt_Rec.Bsc_Analysis_Option_Id := l_Anal_Det_Opt_Tbl(l_count -2).Bsc_Option_Id ;
3039 l_Anal_Opt_Rec.Bsc_Parent_Option_Id := l_Anal_Det_Opt_Tbl(l_count -2).Bsc_Parent_Option_Id ;
3040 l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id := l_Anal_Det_Opt_Tbl(l_count -2).Bsc_Grandparent_Option_Id;
3041 BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Options
3042 ( p_commit => FND_API.G_FALSE
3043 , p_Anal_Opt_Rec => l_Anal_Opt_Rec
3044 , x_return_status => x_return_status
3045 , x_msg_count => x_msg_count
3046 , x_msg_data => x_msg_data
3047 );
3048 END IF;
3049 END IF;
3050 ELSE
3051 l_Anal_Opt_Rec.Bsc_Analysis_Group_Id := l_count;
3052 l_Anal_Opt_Rec.Bsc_Analysis_Option_Id := l_Anal_Det_Opt_Tbl(l_count).Bsc_Option_Id ;
3053 l_Anal_Opt_Rec.Bsc_Parent_Option_Id := l_Anal_Det_Opt_Tbl(l_count).Bsc_Parent_Option_Id ;
3054 l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id := l_Anal_Det_Opt_Tbl(l_count).Bsc_Grandparent_Option_Id;
3055 BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Options
3056 ( p_commit => FND_API.G_FALSE
3057 , p_Anal_Opt_Rec => l_Anal_Opt_Rec
3058 , x_return_status => x_return_status
3059 , x_msg_count => x_msg_count
3060 , x_msg_data => x_msg_data
3061 );
3062 END IF;
3063 EXCEPTION
3064 WHEN FND_API.G_EXC_ERROR THEN
3065 ROLLBACK TO DeleteBSCAnaOptMultGroups;
3066 FND_MSG_PUB.Count_And_Get
3067 ( p_encoded => FND_API.G_FALSE
3068 , p_count => x_msg_count
3069 , p_data => x_msg_data
3070 );
3071 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
3072 x_return_status := FND_API.G_RET_STS_ERROR;
3073 RAISE;
3074 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3075 ROLLBACK TO DeleteBSCAnaOptMultGroups;
3076 FND_MSG_PUB.Count_And_Get
3077 ( p_encoded => FND_API.G_FALSE
3078 , p_count => x_msg_count
3079 , p_data => x_msg_data
3080 );
3081 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3082 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
3083 RAISE;
3084 WHEN NO_DATA_FOUND THEN
3085 ROLLBACK TO DeleteBSCAnaOptMultGroups;
3086 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3087 IF (x_msg_data IS NOT NULL) THEN
3088 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Delete_Ana_Opt_Mult_Groups ';
3089 ELSE
3090 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Delete_Ana_Opt_Mult_Groups ';
3091 END IF;
3092 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
3093 RAISE;
3094 WHEN OTHERS THEN
3095 ROLLBACK TO DeleteBSCAnaOptMultGroups;
3096 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3097 IF (x_msg_data IS NOT NULL) THEN
3098 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Delete_Ana_Opt_Mult_Groups ';
3099 ELSE
3100 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Delete_Ana_Opt_Mult_Groups ';
3101 END IF;
3102 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
3103 RAISE;
3104 END Delete_Ana_Opt_Mult_Groups;
3105 /*****************************************************************************************/
3106
3107 PROCEDURE Synch_Kpi_Anal_Group
3108 ( p_commit IN VARCHAR2:=FND_API.G_FALSE
3109 , p_Kpi_Id IN BSC_KPIS_B.indicator%TYPE
3110 , p_Anal_Opt_Tbl IN BSC_ANALYSIS_OPTION_PUB.Bsc_Anal_Opt_Tbl_Type
3111 , x_return_status OUT NOCOPY VARCHAR2
3112 , x_msg_count OUT NOCOPY NUMBER
3113 , x_msg_data OUT NOCOPY VARCHAR2
3114 )IS
3115 l_Anal_Grp_Opt_Tbl BSC_ANALYSIS_OPTION_PUB.Bsc_Anal_Opt_Tbl_Type;
3116 l_Anal_Opt_Tbl BSC_ANALYSIS_OPTION_PUB.Bsc_Anal_Opt_Tbl_Type;
3117 l_count NUMBER;
3118 l_old_group_count NUMBER;
3119 l_new_group_count NUMBER;
3120 l_group_count NUMBER;
3121 l_Num_Opt_Id NUMBER;
3122 BEGIN
3123 FND_MSG_PUB.Initialize;
3124 x_return_status := FND_API.G_RET_STS_SUCCESS;
3125 SAVEPOINT BSCSynchKpiAnalGroup;
3126
3127 l_Anal_Opt_Tbl := p_Anal_Opt_Tbl;
3128 FOR table_index in 0..l_Anal_Grp_Opt_Tbl.COUNT-1 LOOP
3129 l_Anal_Grp_Opt_Tbl.DELETE(table_index);
3130 END LOOP;
3131 BSC_ANALYSIS_OPTION_PVT.Store_Anal_Opt_Grp_Count(p_kpi_id, l_Anal_Grp_Opt_Tbl);
3132
3133 l_old_group_count := l_Anal_Opt_Tbl.COUNT;
3134 l_new_group_count := l_Anal_Grp_Opt_Tbl.COUNT;
3135
3136 WHILE (l_old_group_count <> l_new_group_count) LOOP
3137 EXIT WHEN (l_old_group_count < 0);
3138 DELETE FROM BSC_KPI_ANALYSIS_GROUPS
3139 WHERE Indicator = p_Kpi_Id
3140 AND Analysis_Group_Id = l_old_group_count - 1;
3141
3142 l_Anal_Opt_Tbl.DELETE(l_old_group_count - 1);
3143 l_old_group_count := l_old_group_count - 1;
3144 END LOOP;
3145
3146 l_group_count := 0;
3147 WHILE(l_group_count <= (l_Anal_Grp_Opt_Tbl.COUNT -1 )) LOOP
3148 IF(((l_Anal_Grp_Opt_Tbl(l_group_count).Bsc_analysis_group_id) =
3149 (l_Anal_Opt_Tbl(l_group_count).Bsc_analysis_group_id)) AND
3150 ((l_Anal_Grp_Opt_Tbl(l_group_count).Bsc_no_option_id) <>
3151 (l_Anal_Opt_Tbl(l_group_count).Bsc_no_option_id))) THEN
3152
3153 SELECT COUNT(DISTINCT(Option_Id)) INTO l_Num_Opt_Id
3154 FROM BSC_KPI_ANALYSIS_OPTIONS_B
3155 WHERE Indicator = p_Kpi_Id
3156 AND Analysis_Group_Id = l_group_count;
3157
3158 UPDATE BSC_KPI_ANALYSIS_GROUPS
3159 SET Num_Of_Options = l_Num_Opt_Id
3160 WHERE Indicator = p_Kpi_Id
3161 AND Analysis_Group_Id = l_group_count;
3162 END IF;
3163 l_group_count := l_group_count + 1;
3164 END LOOP;
3165 EXCEPTION
3166 WHEN FND_API.G_EXC_ERROR THEN
3167 ROLLBACK TO BSCSynchKpiAnalGroup;
3168 FND_MSG_PUB.Count_And_Get
3169 ( p_encoded => FND_API.G_FALSE
3170 , p_count => x_msg_count
3171 , p_data => x_msg_data
3172 );
3173 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
3174 x_return_status := FND_API.G_RET_STS_ERROR;
3175 RAISE;
3176 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3177 ROLLBACK TO BSCSynchKpiAnalGroup;
3178 FND_MSG_PUB.Count_And_Get
3179 ( p_encoded => FND_API.G_FALSE
3180 , p_count => x_msg_count
3181 , p_data => x_msg_data
3182 );
3183 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3184 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
3185 RAISE;
3186 WHEN NO_DATA_FOUND THEN
3187 ROLLBACK TO BSCSynchKpiAnalGroup;
3188 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3189 IF (x_msg_data IS NOT NULL) THEN
3190 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Synch_Kpi_Anal_Group ';
3191 ELSE
3192 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Synch_Kpi_Anal_Group ';
3193 END IF;
3194 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
3195 RAISE;
3196 WHEN OTHERS THEN
3197 ROLLBACK TO BSCSynchKpiAnalGroup;
3198 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3199 IF (x_msg_data IS NOT NULL) THEN
3200 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Synch_Kpi_Anal_Group ';
3201 ELSE
3202 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Synch_Kpi_Anal_Group ';
3203 END IF;
3204 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
3205 RAISE;
3206 END Synch_Kpi_Anal_Group;
3207
3208
3209 /*
3210 This API refreshes all the short_names to reflect the correct AK region its is pointing to.
3211 API is to be called after an Analysis Option has been deleted, etc from Start-end-KPI UI
3212
3213 WARNING: This should not be used from within PMD. Its been implemented only for START-TO-END KPI.
3214 */
3215
3216 -- Added for Start-to-End KPI Project, Bug#3691035
3217
3218 PROCEDURE Refresh_Short_Names (
3219 p_Commit IN VARCHAR2
3220 , p_Kpi_Id IN NUMBER
3221 , x_Return_Status OUT NOCOPY VARCHAR2
3222 , x_Msg_Count OUT NOCOPY NUMBER
3223 , x_Msg_Data OUT NOCOPY VARCHAR2
3224 ) IS
3225
3226 CURSOR c_Update_Short_Names IS
3227 SELECT INDICATOR, OPTION_ID
3228 FROM BSC_KPI_ANALYSIS_OPTIONS_B
3229 WHERE INDICATOR = p_Kpi_Id
3230 AND ANALYSIS_GROUP_ID = 0
3231 AND SHORT_NAME IS NOT NULL;
3232
3233 BEGIN
3234 SAVEPOINT AORefreshShortNamesPVT;
3235 FND_MSG_PUB.Initialize;
3236 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
3237
3238
3239 FOR cUSN IN c_Update_Short_Names LOOP
3240 UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
3241 SET SHORT_NAME = BSC_ANALYSIS_OPTION_PUB.C_BSC_UNDERSCORE || cUSN.INDICATOR || '_' || cUSN.OPTION_ID
3242 WHERE INDICATOR = cUSN.INDICATOR
3243 AND OPTION_ID = cUSN.OPTION_ID
3244 AND ANALYSIS_GROUP_ID = 0
3245 AND SHORT_NAME IS NOT NULL;
3246 END LOOP;
3247
3248 EXCEPTION
3249 WHEN FND_API.G_EXC_ERROR THEN
3250 ROLLBACK TO AORefreshShortNamesPVT;
3251 IF (x_msg_data IS NULL) THEN
3252 FND_MSG_PUB.Count_And_Get
3253 ( p_encoded => FND_API.G_FALSE
3254 , p_count => x_msg_count
3255 , p_data => x_msg_data
3256 );
3257 END IF;
3258 x_Return_Status := FND_API.G_RET_STS_ERROR;
3259 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3260 ROLLBACK TO AORefreshShortNamesPVT;
3261 IF (x_msg_data IS NULL) THEN
3262 FND_MSG_PUB.Count_And_Get
3263 ( p_encoded => FND_API.G_FALSE
3264 , p_count => x_msg_count
3265 , p_data => x_msg_data
3266 );
3267 END IF;
3268 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
3269 WHEN NO_DATA_FOUND THEN
3270 ROLLBACK TO AORefreshShortNamesPVT;
3271 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
3272 IF (x_msg_data IS NOT NULL) THEN
3273 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Refresh_Short_Names ';
3274 ELSE
3275 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Refresh_Short_Names ';
3276 END IF;
3277 WHEN OTHERS THEN
3278 ROLLBACK TO AORefreshShortNamesPVT;
3279 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
3280 IF (x_msg_data IS NOT NULL) THEN
3281 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Refresh_Short_Names ';
3282 ELSE
3283 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Refresh_Short_Names ';
3284 END IF;
3285
3286 END Refresh_Short_Names;
3287
3288
3289
3290
3291 /************************************************************************************
3292 ************************************************************************************/
3293
3294 PROCEDURE Validate_Custom_Measure
3295 ( p_kpi_id IN BSC_OAF_ANALYSYS_OPT_COMB_V.INDICATOR%TYPE
3296 , p_option0 IN BSC_OAF_ANALYSYS_OPT_COMB_V.ANALYSIS_OPTION0%TYPE
3297 , p_option1 IN BSC_OAF_ANALYSYS_OPT_COMB_V.ANALYSIS_OPTION1%TYPE
3298 , p_option2 IN BSC_OAF_ANALYSYS_OPT_COMB_V.ANALYSIS_OPTION2%TYPE
3299 , p_series_id IN BSC_OAF_ANALYSYS_OPT_COMB_V.SERIES_ID%TYPE
3300 , x_return_status OUT NOCOPY VARCHAR2
3301 , x_msg_count OUT NOCOPY NUMBER
3302 , x_msg_data OUT NOCOPY VARCHAR2
3303 ) IS
3304 l_Measure_AKRegion BIS_INDICATORS.ACTUAL_DATA_SOURCE%TYPE;
3305 l_Measure_Function BIS_INDICATORS.FUNCTION_NAME%TYPE;
3306 l_Measure_DatasetId BSC_OAF_ANALYSYS_OPT_COMB_V.DATASET_ID%TYPE;
3307 l_AnaOpt_AKRegion varchar2(50);
3308 l_position NUMBER;
3309 l_index integer;
3310 l_ret_status varchar2(10);
3311 l_msg_data varchar2(30);
3312 l_parent_obj_table BIS_RSG_PUB_API_PKG.t_BIA_RSG_Obj_Table;
3313
3314 l_Allow_Delete BOOLEAN;
3315 l_mess_count NUMBER;
3316 l_dep_obj_message varchar2(1000);
3317 l_message varchar2(1000);
3318 l_AnaOpt_Name BSC_OAF_ANALYSYS_OPT_COMB_V.FULL_NAME%TYPE;
3319 l_kpi_name BSC_KPI_ANALYSIS_MEASURES_VL.NAME%TYPE;
3320 l_objective_name BSC_KPIS_VL.NAME%TYPE;
3321 l_objective BSC_KPIS_VL.NAME%TYPE;
3322
3323 CURSOR c_Measure_ak IS
3324 SELECT c.actual_data_source actual_data_source , c.function_name function_name
3325 ,a.dataset_id dataset_id
3326 ,a.full_name name
3327 FROM bsc_oaf_analysys_opt_comb_v a,
3328 bsc_sys_datasets_b b,
3329 bis_indicators c
3330 WHERE a.dataset_id = b.dataset_id
3331 AND b.dataset_id = c.dataset_id
3332 AND a.Indicator = p_kpi_id
3333 AND a.Analysis_Option0 = p_option0
3334 AND a.Analysis_Option1 = p_option1
3335 AND a.Analysis_Option2 = p_option2
3336 AND a.SERIES_ID = p_series_id;
3337
3338 CURSOR c_AnaOpt_ak IS
3339 SELECT a.short_name , b.name
3340 FROM bsc_kpi_analysis_options_b a, bsc_kpis_vl b
3341 WHERE a.indicator = p_kpi_id
3342 AND a.option_id = p_option0
3343 AND a.parent_option_id = p_option1
3344 AND a.grandparent_option_id = p_option2
3345 AND a.indicator = b.indicator;
3346
3347 CURSOR c_KpiMeasure(p_dataset_id BSC_KPI_ANALYSIS_MEASURES_VL.dataset_id%TYPE) IS
3348 SELECT a.name KPI_NAME, b.name OBJECTVIE_NAME
3349 FROM BSC_KPI_ANALYSIS_MEASURES_VL a
3350 ,BSC_KPIS_VL b
3351 WHERE a.indicator = b.indicator
3352 AND a.dataset_id = p_dataset_id
3353 AND a.Indicator <> p_kpi_id
3354 AND a.Analysis_Option0 <> p_option0
3355 AND a.Analysis_Option1 <> p_option1
3356 AND a.Analysis_Option2 <> p_option2
3357 AND a.SERIES_ID <> p_series_id;
3358
3359 CURSOR c_KpiName(p_kpi_id BSC_KPIS_B.INDICATOR%TYPE) IS
3360 SELECT name
3361 FROM BSC_KPIS_VL
3362 WHERE indicator = p_kpi_id;
3363
3364 BEGIN
3365 FND_MSG_PUB.Initialize;
3366 x_return_status := FND_API.G_RET_STS_SUCCESS;
3367 l_Allow_Delete := TRUE;
3368
3369 IF (c_Measure_ak%ISOPEN) THEN
3370 CLOSE c_Measure_ak;
3371 END IF;
3372
3373 OPEN c_Measure_ak;
3374 FETCH c_Measure_ak into l_Measure_AKRegion,l_Measure_Function,l_Measure_DatasetId,l_AnaOpt_Name;
3375 CLOSE c_Measure_ak;
3376
3377 IF (c_AnaOpt_ak%ISOPEN) THEN
3378 CLOSE c_AnaOpt_ak;
3379 END IF;
3380
3381 OPEN c_AnaOpt_ak;
3382 FETCH c_AnaOpt_ak into l_AnaOpt_AKRegion,l_objective;
3383 CLOSE c_AnaOpt_ak;
3384
3385 IF (l_Measure_AKRegion IS NOT NULL) THEN
3386 l_position := INSTR(l_Measure_AKRegion,'.');
3387 IF l_position <> 0 THEN
3388 l_Measure_AKRegion := substr(l_Measure_AKRegion,1,l_position-1);
3389 END IF;
3390 END IF;
3391
3392 IF (l_Measure_AKRegion <> l_AnaOpt_AKRegion) THEN
3393 FND_MESSAGE.SET_NAME('BSC','BSC_KPI_NOT_PRIM_SOURCE');
3394 FND_MESSAGE.SET_TOKEN('AK_KPI', l_AnaOpt_AKRegion);
3395 FND_MESSAGE.SET_TOKEN('AK_MES', l_Measure_AKRegion);
3396 FND_MSG_PUB.ADD;
3397 RAISE FND_API.G_EXC_ERROR;
3398 END IF;
3399
3400 l_parent_obj_table := BIS_RSG_PUB_API_PKG.GetParentObjects(l_Measure_Function
3401 ,'REPORT','PORTLET',l_ret_status,l_msg_data);
3402 IF ((l_ret_status IS NOT NULL) AND (l_ret_status <> FND_API.G_RET_STS_SUCCESS)) THEN
3403 FND_MSG_PUB.Initialize;
3404 FND_MESSAGE.SET_NAME('BIS',l_msg_data);
3405 FND_MSG_PUB.ADD;
3406 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3407 END IF;
3408
3409 l_mess_count := 1;
3410 l_message := '<ol>';
3411 IF (l_parent_obj_table.COUNT > 0) THEN
3412 l_Allow_Delete := FALSE;
3413 l_index := l_parent_obj_table.first;
3414 LOOP
3415 FND_MESSAGE.SET_NAME('BSC','BSC_KPI_DEP_KPI_REGION');
3416 FND_MESSAGE.SET_TOKEN('DEP_OBJECT',BSC_BIS_CUSTOM_KPI_UTIL_PUB.Get_User_Function_Name(l_parent_obj_table(l_index).object_name));
3417 l_dep_obj_message := FND_MESSAGE.GET;
3418 l_message := l_message || '<li type=1>'||l_dep_obj_message || '</li>';
3419 l_mess_count := l_mess_count + 1;
3420 EXIT WHEN l_index = l_parent_obj_table.last;
3421 l_index := l_parent_obj_table.next(l_index);
3422 END LOOP;
3423 END IF;
3424
3425 IF (c_KpiMeasure%ISOPEN) THEN
3426 CLOSE c_KpiMeasure;
3427 END IF;
3428
3429 OPEN c_KpiMeasure(l_Measure_DatasetId);
3430 LOOP
3431 FETCH c_KpiMeasure INTO l_kpi_name,l_objective_name;
3432 EXIT WHEN c_KpiMeasure%NOTFOUND;
3433 l_Allow_Delete := FALSE;
3434 FND_MESSAGE.SET_NAME('BSC','BSC_KPI_DEP_KPI');
3435 FND_MESSAGE.SET_TOKEN('KPI_NAME',l_kpi_name);
3436 FND_MESSAGE.SET_TOKEN('OBJECTIVE_NAME',l_objective_name);
3437 l_dep_obj_message := FND_MESSAGE.GET;
3438 l_message := l_message || '<li type=1>'|| l_dep_obj_message ||'</li>';
3439 l_mess_count := l_mess_count + 1;
3440 END LOOP;
3441 CLOSE c_KpiMeasure;
3442
3443 l_message := l_message || '</ol>';
3444 IF (l_Allow_Delete = FALSE) THEN
3445
3446 IF (c_KpiName%ISOPEN) THEN
3447 CLOSE c_KpiMeasure;
3448 END IF;
3449
3450 OPEN c_KpiName(p_kpi_id);
3451 FETCH c_KpiName INTO l_objective_name;
3452 CLOSE c_KpiName;
3453
3454 FND_MESSAGE.SET_NAME('BSC','BSC_OBJ_DELETE');
3455 FND_MESSAGE.SET_TOKEN('OBJ_NAME', l_objective_name);
3456 FND_MESSAGE.SET_TOKEN('KPI_NAME', l_AnaOpt_Name);
3457 FND_MESSAGE.SET_TOKEN('DEP_OBJ_LIST', l_message);
3458 FND_MSG_PUB.ADD;
3459 RAISE FND_API.G_EXC_ERROR;
3460 END IF;
3461
3462 FND_MESSAGE.SET_NAME('BSC','BSC_MEASURE_DELETE');
3463 FND_MESSAGE.SET_TOKEN('MEASURE', l_AnaOpt_Name);
3464 FND_MESSAGE.SET_TOKEN('AK_REGION', l_Measure_AKRegion);
3465 FND_MESSAGE.SET_TOKEN('FORM_FUNCTION', l_Measure_Function);
3466 FND_MSG_PUB.ADD;
3467
3468 FND_MSG_PUB.Count_And_Get
3469 ( p_encoded => FND_API.G_FALSE
3470 , p_count => x_msg_count
3471 , p_data => x_msg_data
3472 );
3473
3474 EXCEPTION
3475 WHEN FND_API.G_EXC_ERROR THEN
3476 IF (c_AnaOpt_ak%ISOPEN) THEN
3477 CLOSE c_AnaOpt_ak;
3478 END IF;
3479 IF (c_KpiName%ISOPEN) THEN
3480 CLOSE c_KpiMeasure;
3481 END IF;
3482 FND_MSG_PUB.Count_And_Get
3483 ( p_encoded => FND_API.G_FALSE
3484 , p_count => x_msg_count
3485 , p_data => x_msg_data
3486 );
3487 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
3488 x_return_status := FND_API.G_RET_STS_ERROR;
3489 RAISE;
3490 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3491 IF (c_AnaOpt_ak%ISOPEN) THEN
3492 CLOSE c_AnaOpt_ak;
3493 END IF;
3494 IF (c_KpiName%ISOPEN) THEN
3495 CLOSE c_KpiMeasure;
3496 END IF;
3497 FND_MSG_PUB.Count_And_Get
3498 ( p_encoded => FND_API.G_FALSE
3499 , p_count => x_msg_count
3500 , p_data => x_msg_data
3501 );
3502 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3503 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
3504 RAISE;
3505 WHEN NO_DATA_FOUND THEN
3506 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3507 IF (c_AnaOpt_ak%ISOPEN) THEN
3508 CLOSE c_AnaOpt_ak;
3509 END IF;
3510 IF (c_KpiName%ISOPEN) THEN
3511 CLOSE c_KpiMeasure;
3512 END IF;
3513 IF (x_msg_data IS NOT NULL) THEN
3514 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.checkMeasure ';
3515 ELSE
3516 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.CheckMeasure ';
3517 END IF;
3518 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
3519 RAISE;
3520 WHEN OTHERS THEN
3521 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3522 IF (c_AnaOpt_ak%ISOPEN) THEN
3523 CLOSE c_AnaOpt_ak;
3524 END IF;
3525 IF (c_KpiName%ISOPEN) THEN
3526 CLOSE c_KpiMeasure;
3527 END IF;
3528 IF (x_msg_data IS NOT NULL) THEN
3529 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.checkMeasure ';
3530 ELSE
3531 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.checkMeasure ';
3532 END IF;
3533 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
3534 RAISE;
3535 END Validate_Custom_Measure;
3536
3537 PROCEDURE delete_extra_series(
3538 p_Bsc_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
3539 , x_return_status OUT NOCOPY VARCHAR2
3540 , x_msg_count OUT NOCOPY NUMBER
3541 , x_msg_data OUT NOCOPY VARCHAR2
3542 ) IS
3543 l_Bsc_Anal_Opt_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
3544 BEGIN
3545 x_return_status := FND_API.G_RET_STS_SUCCESS;
3546 FND_MSG_PUB.Initialize;
3547 --DBMS_OUTPUT.PUT_LINE('in private delte');
3548
3549 DELETE FROM BSC_KPI_ANALYSIS_MEASURES_B
3550 WHERE indicator = p_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
3551 AND analysis_option0 = 0
3552 AND analysis_option1 = p_Bsc_Anal_Opt_Rec.Bsc_Option_Group1
3553 AND analysis_option2 = p_Bsc_Anal_Opt_Rec.Bsc_Option_Group2
3554 AND series_id > 0;
3555 --DBMS_OUTPUT.PUT_LINE('after deleting baset table');
3556
3557 DELETE FROM BSC_KPI_ANALYSIS_MEASURES_TL
3558 WHERE indicator = p_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
3559 AND analysis_option0 = 0
3560 AND analysis_option1 = p_Bsc_Anal_Opt_Rec.Bsc_Option_Group1
3561 AND analysis_option2 = p_Bsc_Anal_Opt_Rec.Bsc_Option_Group2
3562 AND series_id > 0;
3563 --DBMS_OUTPUT.PUT_LINE('after deleting base table');
3564
3565
3566 EXCEPTION
3567 WHEN FND_API.G_EXC_ERROR THEN
3568 IF (x_msg_data IS NULL) THEN
3569 FND_MSG_PUB.Count_And_Get
3570 ( p_encoded => FND_API.G_FALSE
3571 , p_count => x_msg_count
3572 , p_data => x_msg_data
3573 );
3574 END IF;
3575 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
3576 x_return_status := FND_API.G_RET_STS_ERROR;
3577 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3578 IF (x_msg_data IS NULL) THEN
3579 FND_MSG_PUB.Count_And_Get
3580 ( p_encoded => FND_API.G_FALSE
3581 , p_count => x_msg_count
3582 , p_data => x_msg_data
3583 );
3584 END IF;
3585 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3586 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
3587 WHEN NO_DATA_FOUND THEN
3588 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3589 IF (x_msg_data IS NOT NULL) THEN
3590 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Delete_Ana_Opt_Mult_Groups ';
3591 ELSE
3592 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Delete_Ana_Opt_Mult_Groups ';
3593 END IF;
3594 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
3595 WHEN OTHERS THEN
3596 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3597 IF (x_msg_data IS NOT NULL) THEN
3598 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Delete_Ana_Opt_Mult_Groups ';
3599 ELSE
3600 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Delete_Ana_Opt_Mult_Groups ';
3601 END IF;
3602 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
3603 END delete_extra_series;
3604
3605 /************************************************************************************
3606 ************************************************************************************/
3607
3608 procedure Delete_Data_Series(
3609 p_commit IN varchar2 -- := FND_API.G_FALSE
3610 ,p_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
3611 ,x_return_status OUT NOCOPY varchar2
3612 ,x_msg_count OUT NOCOPY number
3613 ,x_msg_data OUT NOCOPY varchar2
3614 ) is
3615
3616 Cursor c_Dataseries is
3617 SELECT SERIES_ID
3618 FROM BSC_KPI_ANALYSIS_MEASURES_B
3619 WHERE indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
3620 AND analysis_option0 = p_Anal_Opt_Rec.Bsc_Option_Group0
3621 AND analysis_option1 = p_Anal_Opt_Rec.Bsc_Option_Group1
3622 AND analysis_option2 = p_Anal_Opt_Rec.Bsc_Option_Group2
3623 AND SERIES_ID > p_Anal_Opt_Rec.Bsc_Dataset_Series_Id
3624 ORDER BY SERIES_ID;
3625
3626 l_Anal_Opt_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
3627 l_new_series_id number;
3628
3629 l_Count NUMBER;
3630
3631 BEGIN
3632 FND_MSG_PUB.Initialize;
3633 SAVEPOINT DeleteBSCDataSeriesPVT;
3634 x_return_status := FND_API.G_RET_STS_SUCCESS;
3635
3636 -- Delete the Data Series
3637 DELETE FROM BSC_KPI_ANALYSIS_MEASURES_B
3638 WHERE indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
3639 AND analysis_option0 = p_Anal_Opt_Rec.Bsc_Option_Group0
3640 AND analysis_option1 = p_Anal_Opt_Rec.Bsc_Option_Group1
3641 AND analysis_option2 = p_Anal_Opt_Rec.Bsc_Option_Group2
3642 AND SERIES_ID = p_Anal_Opt_Rec.Bsc_Dataset_Series_Id;
3643
3644 DELETE FROM BSC_KPI_ANALYSIS_MEASURES_TL
3645 WHERE indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
3646 AND analysis_option0 = p_Anal_Opt_Rec.Bsc_Option_Group0
3647 AND analysis_option1 = p_Anal_Opt_Rec.Bsc_Option_Group1
3648 AND analysis_option2 = p_Anal_Opt_Rec.Bsc_Option_Group2
3649 AND SERIES_ID = p_Anal_Opt_Rec.Bsc_Dataset_Series_Id;
3650
3651 -- Renumerate the Series Id
3652 IF (c_Dataseries%ISOPEN) THEN
3653 CLOSE c_Dataseries;
3654 END IF;
3655
3656 -- Renumerate Data Series Id
3657 l_new_series_id := p_Anal_Opt_Rec.Bsc_Dataset_Series_Id;
3658 l_Anal_Opt_Rec := p_Anal_Opt_Rec;
3659 FOR CD IN c_Dataseries LOOP
3660 l_Anal_Opt_Rec.Bsc_Dataset_Series_Id := CD.SERIES_ID;
3661 l_Anal_Opt_Rec.Bsc_Dataset_New_Series_Id := l_new_series_id;
3662 Swap_Data_Series_Id(
3663 p_commit => FND_API.G_FALSE
3664 ,p_Anal_Opt_Rec => l_Anal_Opt_Rec
3665 ,x_return_status => x_return_status
3666 ,x_msg_count => x_msg_count
3667 ,x_msg_data => x_msg_data
3668 );
3669 l_new_series_id := l_new_series_id + 1;
3670 END LOOP;
3671
3672 -- This code need to moved to a better place.
3673 -- currently this is very crude and needs to be replaced and moved to
3674 -- a better place.
3675 BEGIN
3676 SELECT COUNT(1) INTO l_Count
3677 FROM BSC_KPI_ANALYSIS_MEASURES_B K
3678 WHERE K.INDICATOR = p_Anal_Opt_Rec.Bsc_Kpi_Id
3679 AND K.ANALYSIS_OPTION0 = p_Anal_Opt_Rec.Bsc_Option_Group0
3680 AND K.ANALYSIS_OPTION1 = p_Anal_Opt_Rec.Bsc_Option_Group1
3681 AND K.ANALYSIS_OPTION2 = p_Anal_Opt_Rec.Bsc_Option_Group2
3682 AND K.DEFAULT_VALUE = 1;
3683
3684 IF (l_Count = 0) THEN
3685 UPDATE BSC_KPI_ANALYSIS_MEASURES_B K
3686 SET K.DEFAULT_VALUE = 1
3687 WHERE K.INDICATOR = p_Anal_Opt_Rec.Bsc_Kpi_Id
3688 AND K.ANALYSIS_OPTION0 = p_Anal_Opt_Rec.Bsc_Option_Group0
3689 AND K.ANALYSIS_OPTION1 = p_Anal_Opt_Rec.Bsc_Option_Group1
3690 AND K.ANALYSIS_OPTION2 = p_Anal_Opt_Rec.Bsc_Option_Group2
3691 AND K.SERIES_ID = 0;
3692 END IF;
3693 EXCEPTION
3694 WHEN OTHERS THEN
3695 NULL;
3696 END;
3697
3698 IF p_commit = FND_API.G_TRUE THEN
3699 commit;
3700 END IF;
3701
3702 EXCEPTION
3703 WHEN FND_API.G_EXC_ERROR THEN
3704 IF (c_Dataseries%ISOPEN) THEN
3705 CLOSE c_Dataseries;
3706 END IF;
3707 ROLLBACK TO DeleteBSCDataSeriesPVT;
3708 FND_MSG_PUB.Count_And_Get
3709 ( p_encoded => FND_API.G_FALSE
3710 , p_count => x_msg_count
3711 , p_data => x_msg_data
3712 );
3713 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
3714 x_return_status := FND_API.G_RET_STS_ERROR;
3715 RAISE;
3716 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3717 IF (c_Dataseries%ISOPEN) THEN
3718 CLOSE c_Dataseries;
3719 END IF;
3720 ROLLBACK TO DeleteBSCDataSeriesPVT;
3721 FND_MSG_PUB.Count_And_Get
3722 ( p_encoded => FND_API.G_FALSE
3723 , p_count => x_msg_count
3724 , p_data => x_msg_data
3725 );
3726 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3727 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
3728 RAISE;
3729 WHEN OTHERS THEN
3730 IF (c_Dataseries%ISOPEN) THEN
3731 CLOSE c_Dataseries;
3732 END IF;
3733 ROLLBACK TO DeleteBSCDataSeriesPVT;
3734 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3735 IF (x_msg_data IS NOT NULL) THEN
3736 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Delete_Data_Series ';
3737 ELSE
3738 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Delete_Data_Series ';
3739 END IF;
3740 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
3741 RAISE;
3742
3743 End Delete_Data_Series;
3744
3745
3746 /*---------------------------------------------------------------------------
3747 Swap_Data_Series : Swap the Data Series Id between two DataSerid
3748
3749 Use Parameters:
3750 p_Anal_Opt_Rec.Bsc_Kpi_Id
3751 p_Anal_Opt_Rec.Bsc_Option_Group0
3752 p_Anal_Opt_Rec.Bsc_Option_Group1
3753 Anal_Opt_Rec.Bsc_Option_Group2
3754 p_Anal_Opt_Rec.Bsc_Dataset_Series_Id;
3755 p_Anal_Opt_Rec.Bsc_Dataset_New_Series_Id;
3756 ----------------------------------------------------------------------------*/
3757 procedure Swap_Data_Series_Id(
3758 p_commit IN varchar2 -- := FND_API.G_FALSE
3759 ,p_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
3760 ,x_return_status OUT NOCOPY varchar2
3761 ,x_msg_count OUT NOCOPY number
3762 ,x_msg_data OUT NOCOPY varchar2
3763 ) is
3764
3765
3766 Cursor c_Data_Series is
3767 SELECT SERIES_ID
3768 FROM BSC_KPI_ANALYSIS_MEASURES_B
3769 WHERE indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
3770 AND analysis_option0 = p_Anal_Opt_Rec.Bsc_Option_Group0
3771 AND analysis_option1 = p_Anal_Opt_Rec.Bsc_Option_Group1
3772 AND analysis_option2 = p_Anal_Opt_Rec.Bsc_Option_Group2
3773 AND SERIES_ID = p_Anal_Opt_Rec.Bsc_Dataset_New_Series_Id;
3774
3775 l_temp_data_series_id number;
3776 l_Anal_Opt_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
3777 l_temp_value number := -999;
3778
3779 BEGIN
3780 FND_MSG_PUB.Initialize;
3781 SAVEPOINT SwapDataSeriesPVT;
3782
3783 x_return_status := FND_API.G_RET_STS_SUCCESS;
3784
3785 IF p_Anal_Opt_Rec.Bsc_Dataset_New_Series_Id is not null
3786 and p_Anal_Opt_Rec.Bsc_Dataset_Series_Id is not null
3787 and p_Anal_Opt_Rec.Bsc_Dataset_New_Series_Id <>
3788 p_Anal_Opt_Rec.Bsc_Dataset_Series_Id THEN
3789
3790 l_Anal_Opt_Rec := p_Anal_Opt_Rec;
3791 l_Anal_Opt_Rec.Bsc_Dataset_New_Series_Id := NULL;
3792
3793 -- Check if the Bsc_Dataset_New_Series_Id exist to Swap to a temporaty value
3794 FOR cd IN c_Data_Series LOOP
3795 l_Anal_Opt_Rec.Bsc_Dataset_Series_Id := p_Anal_Opt_Rec.Bsc_Dataset_New_Series_Id;
3796 l_Anal_Opt_Rec.Bsc_Dataset_New_Series_Id := l_temp_value;
3797 Swap_Data_Series_Id(
3798 p_commit => p_commit
3799 ,p_Anal_Opt_Rec => l_Anal_Opt_Rec
3800 ,x_return_status => x_return_status
3801 ,x_msg_count => x_msg_count
3802 ,x_msg_data => x_msg_data
3803 );
3804 END LOOP;
3805
3806 -- Swap the Data Series Id
3807 UPDATE BSC_KPI_ANALYSIS_MEASURES_B
3808 SET SERIES_ID = p_Anal_Opt_Rec.Bsc_Dataset_New_Series_Id
3809 WHERE indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
3810 AND analysis_option0 = p_Anal_Opt_Rec.Bsc_Option_Group0
3811 AND analysis_option1 = p_Anal_Opt_Rec.Bsc_Option_Group1
3812 AND analysis_option2 = p_Anal_Opt_Rec.Bsc_Option_Group2
3813 AND SERIES_ID = p_Anal_Opt_Rec.Bsc_Dataset_Series_Id;
3814
3815 UPDATE BSC_KPI_ANALYSIS_MEASURES_TL
3816 SET SERIES_ID = p_Anal_Opt_Rec.Bsc_Dataset_New_Series_Id
3817 WHERE indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
3818 AND analysis_option0 = p_Anal_Opt_Rec.Bsc_Option_Group0
3819 AND analysis_option1 = p_Anal_Opt_Rec.Bsc_Option_Group1
3820 AND analysis_option2 = p_Anal_Opt_Rec.Bsc_Option_Group2
3821 AND SERIES_ID = p_Anal_Opt_Rec.Bsc_Dataset_Series_Id;
3822
3823 -- Swap the temporay Series Id
3824 IF l_Anal_Opt_Rec.Bsc_Dataset_New_Series_Id = l_temp_value THEN
3825 l_Anal_Opt_Rec.Bsc_Dataset_Series_Id := l_temp_value;
3826 l_Anal_Opt_Rec.Bsc_Dataset_New_Series_Id := p_Anal_Opt_Rec.Bsc_Dataset_Series_Id;
3827 Swap_Data_Series_Id(
3828 p_commit => p_commit
3829 ,p_Anal_Opt_Rec => l_Anal_Opt_Rec
3830 ,x_return_status => x_return_status
3831 ,x_msg_count => x_msg_count
3832 ,x_msg_data => x_msg_data
3833 );
3834 END IF;
3835
3836 END IF;
3837
3838 IF p_commit = FND_API.G_TRUE THEN
3839 commit;
3840 END IF;
3841
3842 EXCEPTION
3843 WHEN FND_API.G_EXC_ERROR THEN
3844 IF (c_Data_Series%ISOPEN) THEN
3845 CLOSE c_Data_Series;
3846 END IF;
3847 ROLLBACK TO SwapDataSeriesPVT;
3848 FND_MSG_PUB.Count_And_Get
3849 ( p_encoded => FND_API.G_FALSE
3850 , p_count => x_msg_count
3851 , p_data => x_msg_data
3852 );
3853 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
3854 x_return_status := FND_API.G_RET_STS_ERROR;
3855 RAISE;
3856 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3857 IF (c_Data_Series%ISOPEN) THEN
3858 CLOSE c_Data_Series;
3859 END IF;
3860 ROLLBACK TO SwapDataSeriesPVT;
3861 FND_MSG_PUB.Count_And_Get
3862 ( p_encoded => FND_API.G_FALSE
3863 , p_count => x_msg_count
3864 , p_data => x_msg_data
3865 );
3866 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3867 IF (x_msg_data IS NOT NULL) THEN
3868 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Swap_Data_Series_Id ';
3869 ELSE
3870 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Swap_Data_Series_Id ';
3871 END IF;
3872 RAISE;
3873 WHEN OTHERS THEN
3874 IF (c_Data_Series%ISOPEN) THEN
3875 CLOSE c_Data_Series;
3876 END IF;
3877 ROLLBACK TO SwapDataSeriesPVT;
3878 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3879 IF (x_msg_data IS NOT NULL) THEN
3880 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Swap_Data_Series_Id ';
3881 ELSE
3882 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Swap_Data_Series_Id ';
3883 END IF;
3884 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
3885 RAISE;
3886
3887 End Swap_Data_Series_Id;
3888
3889
3890 /*---------------------------------------------------------------------------------/
3891 API: Cascade_Series_Default_Value
3892
3893 This API cascades the correct DEFAULT_VALUE that exists in the table
3894 BSC_KPI_ANALYSIS_MEASURES_B. Only one value of the default value needs to be
3895 set to 1 and the rest needs to be 0, so the following API will ensure the following
3896
3897 If in "CREATE" mode
3898 -------------------
3899 1) If the table is empty for the combination (kpi,option0,option1,option2) then
3900 the return value x_Default_Value will always be returned as 1 (Assuming that
3901 DATASET_ID = -1 for the "Default Measure"
3902 2) If during a create the default_value is passed as 1, then the rest of the
3903 default_Values in the table by the comination (kpi,option0,option1,option2)
3904 will be set to 0 and the Series under consideration will be returned with
3905 x_Default_Value as 1
3906 3) If p_Default_Value is passed as 0, then no action is taken.
3907
3908 If in "UPDATE" mode
3909 -------------------
3910
3911 1) If we have p_Default_Value passed as 0 and the table BSC_KPI_ANALYSIS_MEASURES_B
3912 has only one single entry, then x_Default_Value will be returned as 1
3913 2) If we have p_Default_Value passed as 0 for a series which already has
3914 default_value as 1, then the next subsequent series is set with 1 and if the
3915 series being updated is already the last one, then SERIES_ID = 0 will be updated
3916 with default_value = 1.
3917 3) If One of the default value is being changed from 0 to 1, then the rest of
3918 default_Value is set to 0 and the current series is set to 1.
3919
3920 The API ensures that there is exactly one entry in BSC_kPI_ANALYSIS_OPTIONS_B
3921 table for DEFAULT_VALUE =1 for the (kpi,option0,option1,option2) combination.
3922
3923 Appropriate color changes are cascaded into the Objectives (and Shared)
3924 Also changes will be cascaded only if the current analysis option combination
3925 is the default combination
3926 /---------------------------------------------------------------------------------*/
3927
3928
3929 PROCEDURE Cascade_Series_Default_Value (
3930 p_Commit IN VARCHAR2
3931 , p_Api_Mode IN VARCHAR2
3932 , p_Kpi_Id IN NUMBER
3933 , p_Option0 IN NUMBER
3934 , p_Option1 IN NUMBER
3935 , p_Option2 IN NUMBER
3936 , p_Series_Id IN NUMBER
3937 , p_Default_Value IN NUMBER
3938 , x_Default_Value OUT NOCOPY NUMBER
3939 , x_Return_Status OUT NOCOPY VARCHAR2
3940 , x_Msg_Count OUT NOCOPY NUMBER
3941 , x_Msg_Data OUT NOCOPY VARCHAR2
3942 ) IS
3943 CURSOR c_Shared_Objectives IS
3944 SELECT K.INDICATOR
3945 FROM BSC_KPIS_B K
3946 WHERE K.SOURCE_INDICATOR = p_Kpi_Id
3947 AND K.PROTOTYPE_FLAG <> BSC_KPI_PUB.DELETE_KPI_FLAG;
3948
3949
3950 l_Default_Value BSC_KPI_ANALYSIS_MEASURES_B.DEFAULT_VALUE%TYPE;
3951 l_Count NUMBER;
3952 l_Max_Series_Id NUMBER;
3953 l_Upd_Series_Id NUMBER;
3954 BEGIN
3955 FND_MSG_PUB.Initialize;
3956 SAVEPOINT CascadedSeriesPVT;
3957
3958 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
3959
3960 l_Count := 0;
3961
3962 x_Default_Value := p_Default_Value;
3963
3964 SELECT COUNT(1) INTO l_Count
3965 FROM bsc_db_color_ao_defaults_v
3966 WHERE indicator = p_Kpi_Id
3967 AND a0_default = p_Option0
3968 AND a1_default = p_Option1
3969 AND a2_default = p_Option2;
3970
3971 IF l_Count = 0 THEN
3972 RETURN;
3973 END IF;
3974
3975 IF (p_Api_Mode = C_API_CREATE) THEN
3976
3977 SELECT COUNT(1) INTO l_Count
3978 FROM BSC_KPI_ANALYSIS_MEASURES_B K
3979 WHERE K.INDICATOR = p_Kpi_Id
3980 AND K.ANALYSIS_OPTION0 = p_Option0
3981 AND K.ANALYSIS_OPTION1 = p_Option1
3982 AND K.ANALYSIS_OPTION2 = p_Option2;
3983 --AND K.DATASET_ID <> -1; -- default measure dataset
3984
3985 IF (l_Count = 0) THEN
3986 x_Default_Value := 1; -- enabled
3987 ELSE
3988 IF p_Default_Value = 1 THEN
3989 UPDATE BSC_KPI_ANALYSIS_MEASURES_B K
3990 SET K.DEFAULT_VALUE = 0
3991 WHERE K.INDICATOR = p_Kpi_Id
3992 AND K.ANALYSIS_OPTION0 = p_Option0
3993 AND K.ANALYSIS_OPTION1 = p_Option1
3994 AND K.ANALYSIS_OPTION2 = p_Option2;
3995
3996 x_Default_Value := p_Default_Value;
3997 ELSE
3998 x_Default_Value := p_Default_Value;
3999 END IF;
4000 END IF;
4001 ELSIF (p_Api_Mode = C_API_UPDATE) THEN
4002
4003 SELECT K.DEFAULT_VALUE INTO l_Default_Value
4004 FROM BSC_KPI_ANALYSIS_MEASURES_B K
4005 WHERE K.INDICATOR = p_Kpi_Id
4006 AND K.ANALYSIS_OPTION0 = p_Option0
4007 AND K.ANALYSIS_OPTION1 = p_Option1
4008 AND K.ANALYSIS_OPTION2 = p_Option2
4009 AND K.SERIES_ID = p_Series_Id;
4010
4011 IF (l_Default_Value = 0 AND p_Default_Value = 1) THEN
4012 UPDATE BSC_KPI_ANALYSIS_MEASURES_B K
4013 SET K.DEFAULT_VALUE = 0
4014 WHERE K.INDICATOR = p_Kpi_Id
4015 AND K.ANALYSIS_OPTION0 = p_Option0
4016 AND K.ANALYSIS_OPTION1 = p_Option1
4017 AND K.ANALYSIS_OPTION2 = p_Option2;
4018
4019 ELSIF (l_Default_Value = 1 AND p_Default_Value = 0) THEN
4020
4021 SELECT NVL(MAX(K.SERIES_ID), 0) INTO l_Max_Series_Id
4022 FROM BSC_KPI_ANALYSIS_MEASURES_B K
4023 WHERE K.INDICATOR = p_Kpi_Id
4024 AND K.ANALYSIS_OPTION0 = p_Option0
4025 AND K.ANALYSIS_OPTION1 = p_Option1
4026 AND K.ANALYSIS_OPTION2 = p_Option2;
4027
4028 IF (l_Max_Series_Id = p_Series_Id) THEN
4029 l_Upd_Series_Id := 0;
4030 ELSE
4031 l_Upd_Series_Id := p_Series_Id + 1;
4032 END IF;
4033
4034 UPDATE BSC_KPI_ANALYSIS_MEASURES_B K
4035 SET K.DEFAULT_VALUE = 0
4036 WHERE K.INDICATOR = p_Kpi_Id
4037 AND K.ANALYSIS_OPTION0 = p_Option0
4038 AND K.ANALYSIS_OPTION1 = p_Option1
4039 AND K.ANALYSIS_OPTION2 = p_Option2;
4040
4041 UPDATE BSC_KPI_ANALYSIS_MEASURES_B K
4042 SET K.DEFAULT_VALUE = 1
4043 WHERE K.INDICATOR = p_Kpi_Id
4044 AND K.ANALYSIS_OPTION0 = p_Option0
4045 AND K.ANALYSIS_OPTION1 = p_Option1
4046 AND K.ANALYSIS_OPTION2 = p_Option2
4047 AND K.SERIES_ID = l_Upd_Series_Id;
4048
4049 BEGIN
4050 -- get the updated values of the series into x_Default_Value
4051 SELECT K.DEFAULT_VALUE INTO x_Default_Value
4052 FROM BSC_KPI_ANALYSIS_MEASURES_B K
4053 WHERE K.INDICATOR = p_Kpi_Id
4054 AND K.ANALYSIS_OPTION0 = p_Option0
4055 AND K.ANALYSIS_OPTION1 = p_Option1
4056 AND K.ANALYSIS_OPTION2 = p_Option2
4057 AND K.SERIES_ID = p_Series_Id;
4058 EXCEPTION
4059 WHEN OTHERS THEN
4060 x_Default_Value := p_Default_Value;
4061 END;
4062
4063 END IF;
4064 END IF;
4065
4066
4067 EXCEPTION
4068 WHEN NO_DATA_FOUND THEN
4069 ROLLBACK TO CascadedSeriesPVT;
4070 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
4071 IF (x_msg_data IS NOT NULL) THEN
4072 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Cascade_Series_Default_Value ';
4073 ELSE
4074 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Cascade_Series_Default_Value ';
4075 END IF;
4076 RAISE;
4077 WHEN OTHERS THEN
4078 ROLLBACK TO CascadedSeriesPVT;
4079 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
4080 IF (x_msg_data IS NOT NULL) THEN
4081 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Cascade_Series_Default_Value ';
4082 ELSE
4083 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Cascade_Series_Default_Value ';
4084 END IF;
4085 RAISE;
4086 END Cascade_Series_Default_Value;
4087
4088
4089
4090 /************************************************************************************
4091 ************************************************************************************/
4092
4093 -- added for Bug#4324947
4094 -- Returns the short_name of next associated Objective
4095 -- of type AG only.
4096 FUNCTION Get_Next_Associated_Obj_SN (
4097 p_Dataset_Id IN NUMBER
4098 ) RETURN VARCHAR2 IS
4099 l_Dataset_Id NUMBER;
4100 l_Short_Name BSC_KPIS_B.SHORT_NAME%TYPE;
4101
4102 CURSOR c_Objectives IS
4103 SELECT
4104 K.SHORT_NAME
4105 FROM
4106 BSC_KPIS_B K,
4107 BSC_KPI_ANALYSIS_MEASURES_B M
4108 WHERE
4109 K.INDICATOR = M.INDICATOR
4110 AND M.DATASET_ID = p_Dataset_Id
4111 AND K.SHORT_NAME IS NOT NULL
4112 AND ROWNUM <= 1
4113 ORDER BY K.CREATION_DATE;
4114 BEGIN
4115 l_Short_Name := NULL;
4116
4117 FOR cObjs IN c_Objectives LOOP
4118 l_Short_Name := cObjs.SHORT_NAME;
4119 END LOOP;
4120
4121 RETURN l_Short_Name;
4122
4123 EXCEPTION
4124 WHEN OTHERS THEN
4125 RETURN NULL;
4126 END Get_Next_Associated_Obj_SN;
4127
4128 /************************************************************************************
4129 ************************************************************************************/
4130
4131 -- Modified API for Bug#4638384 - changed signature to add p_Comparison_Source
4132 -- added for Bug#4324947
4133 PROCEDURE Cascade_Data_Src_Values (
4134 p_Commit IN VARCHAR2
4135 , p_Measure_Short_Name IN VARCHAR2
4136 , p_Empty_Source IN VARCHAR2
4137 , p_Actual_Data_Source_Type IN VARCHAR2
4138 , p_Actual_Data_Source IN VARCHAR2
4139 , p_Function_Name IN VARCHAR2
4140 , p_Enable_Link IN VARCHAR2
4141 , p_Comparison_Source IN VARCHAR2
4142 , x_Return_Status OUT NOCOPY VARCHAR2
4143 , x_Msg_Count OUT NOCOPY NUMBER
4144 , x_Msg_Data OUT NOCOPY VARCHAR2
4145 ) IS
4146 l_Actual_Data_Source_Type BIS_INDICATORS.ACTUAL_DATA_SOURCE_TYPE%TYPE;
4147 l_Actual_Data_Source BIS_INDICATORS.ACTUAL_DATA_SOURCE%TYPE;
4148 l_Function_Name BIS_INDICATORS.FUNCTION_NAME%TYPE;
4149 l_Enable_Link BIS_INDICATORS.ENABLE_LINK%TYPE;
4150 l_Comparison_Source BIS_INDICATORS.COMPARISON_SOURCE%TYPE;
4151
4152 BEGIN
4153 FND_MSG_PUB.Initialize;
4154 SAVEPOINT CascadedDataSrcPVT;
4155
4156 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
4157
4158
4159 IF (p_Measure_Short_Name IS NULL) THEN
4160 RETURN;
4161 END IF;
4162
4163 IF (p_Empty_Source = FND_API.G_TRUE) THEN
4164 l_Actual_Data_Source_Type := NULL;
4165 l_Actual_Data_Source := NULL;
4166 l_Function_Name := NULL;
4167 l_Enable_Link := 'N';
4168 l_Comparison_Source := NULL;
4169 ELSE
4170 l_Actual_Data_Source_Type := p_Actual_Data_Source_Type;
4171 l_Actual_Data_Source := p_Actual_Data_Source;
4172 l_Function_Name := p_Function_Name;
4173 l_Enable_Link := p_Enable_Link;
4174 l_Comparison_Source := p_Comparison_Source;
4175 END IF;
4176
4177 -- This API expects BIS_INDICATORS to be syncronized correctly with BSC_SYS_DATASETS_VL
4178 UPDATE BIS_INDICATORS I
4179 SET I.ACTUAL_DATA_SOURCE_TYPE = l_Actual_Data_Source_Type
4180 , I.ACTUAL_DATA_SOURCE = l_Actual_Data_Source
4181 , I.FUNCTION_NAME = l_Function_Name
4182 , I.ENABLE_LINK = l_Enable_Link
4183 , I.COMPARISON_SOURCE = l_Comparison_Source
4184 WHERE I.SHORT_NAME = p_Measure_Short_Name;
4185
4186 EXCEPTION
4187 WHEN NO_DATA_FOUND THEN
4188 ROLLBACK TO CascadedDataSrcPVT;
4189 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
4190 IF (x_msg_data IS NOT NULL) THEN
4191 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Cascade_Data_Src_Values ';
4192 ELSE
4193 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Cascade_Data_Src_Values ';
4194 END IF;
4195 RAISE;
4196 WHEN OTHERS THEN
4197 ROLLBACK TO CascadedDataSrcPVT;
4198 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
4199 IF (x_msg_data IS NOT NULL) THEN
4200 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Cascade_Data_Src_Values ';
4201 ELSE
4202 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Cascade_Data_Src_Values ';
4203 END IF;
4204 RAISE;
4205 END Cascade_Data_Src_Values;
4206
4207
4208 /***********************************************************
4209 Name : Set_Default_Analysis_Option
4210 Description: This Function sets the current default analysis combination.
4211 Input : p_obj_id --> Objective Id
4212 p_Anal_Opt_Comb_Tbl --> Analysis Option combination Table
4213 p_Anal_Grp_Id --> The current analysis group
4214 Created BY : ashankar For bug 4220400
4215 /**********************************************************/
4216 PROCEDURE Set_Default_Analysis_Option
4217 (
4218 p_commit IN VARCHAR2
4219 , p_obj_id IN BSC_KPIS_B.indicator%TYPE
4220 , p_Anal_Opt_Comb_Tbl IN BSC_ANALYSIS_OPTION_PUB.Anal_Opt_Comb_Num_Tbl_Type
4221 , p_Anal_Grp_Id IN BSC_KPIS_B.ind_group_id%TYPE
4222 , x_return_status OUT NOCOPY VARCHAR2
4223 , x_msg_count OUT NOCOPY NUMBER
4224 , x_msg_data OUT NOCOPY VARCHAR2
4225 )IS
4226 l_anal_grp_id BSC_KPIS_B.ind_group_id%TYPE;
4227 l_default_value BSC_KPI_ANALYSIS_GROUPS.default_value%TYPE;
4228 l_Anal_Opt_Comb_Tbl BSC_ANALYSIS_OPTION_PUB.Anal_Opt_Comb_Num_Tbl_Type;
4229 BEGIN
4230 SAVEPOINT SetDftAnalOption;
4231 x_return_status := FND_API.G_RET_STS_SUCCESS;
4232 FND_MSG_PUB.Initialize;
4233
4234 IF( p_Anal_Opt_Comb_Tbl IS NOT NULL) THEN
4235 l_Anal_Opt_Comb_Tbl := p_Anal_Opt_Comb_Tbl;
4236 l_anal_grp_id := p_Anal_Grp_Id;
4237 l_default_value := l_Anal_Opt_Comb_Tbl(l_anal_grp_id);
4238
4239
4240 UPDATE bsc_kpi_analysis_groups
4241 SET default_value = BSC_ANALYSIS_OPTION_PUB.c_ANAL_SERIES_DISABLED
4242 WHERE indicator = p_obj_id;
4243
4244 IF(l_anal_grp_id>=0)THEN
4245 WHILE (l_anal_grp_id>=0) LOOP
4246 UPDATE bsc_kpi_analysis_groups
4247 SET default_value = l_Anal_Opt_Comb_Tbl(l_anal_grp_id)
4248 WHERE indicator = p_obj_id
4249 AND analysis_group_id = l_anal_grp_id;
4250
4251 l_anal_grp_id := l_anal_grp_id - 1;
4252 END LOOP;
4253 END IF;
4254
4255 UPDATE bsc_kpi_analysis_measures_b
4256 SET default_value = BSC_ANALYSIS_OPTION_PUB.c_ANAL_SERIES_DISABLED
4257 WHERE indicator = p_obj_id
4258 AND analysis_option0 = l_Anal_Opt_Comb_Tbl(0)
4259 AND analysis_option1 = l_Anal_Opt_Comb_Tbl(1)
4260 AND analysis_option2 = l_Anal_Opt_Comb_Tbl(2);
4261
4262 UPDATE bsc_kpi_analysis_measures_b
4263 SET default_value = BSC_ANALYSIS_OPTION_PUB.c_ANAL_SERIES_ENABLED
4264 WHERE indicator = p_obj_id
4265 AND analysis_option0 = l_Anal_Opt_Comb_Tbl(0)
4266 AND analysis_option1 = l_Anal_Opt_Comb_Tbl(1)
4267 AND analysis_option2 = l_Anal_Opt_Comb_Tbl(2)
4268 AND series_id = l_Anal_Opt_Comb_Tbl(3);
4269 END IF;
4270
4271 IF(p_commit=FND_API.G_TRUE)THEN
4272 COMMIT;
4273 END IF;
4274
4275 EXCEPTION
4276 WHEN FND_API.G_EXC_ERROR THEN
4277 ROLLBACK TO SetDftAnalOption;
4278 FND_MSG_PUB.Count_And_Get
4279 ( p_encoded => FND_API.G_FALSE
4280 , p_count => x_msg_count
4281 , p_data => x_msg_data
4282 );
4283 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
4284 x_return_status := FND_API.G_RET_STS_ERROR;
4285 RAISE;
4286 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4287 ROLLBACK TO SetDftAnalOption;
4288 FND_MSG_PUB.Count_And_Get
4289 ( p_encoded => FND_API.G_FALSE
4290 , p_count => x_msg_count
4291 , p_data => x_msg_data
4292 );
4293 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4294 RAISE;
4295 WHEN OTHERS THEN
4296 ROLLBACK TO SetDftAnalOption;
4297 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4298 IF (x_msg_data IS NOT NULL) THEN
4299 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Set_Default_Analysis_Option ';
4300 ELSE
4301 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Set_Default_Analysis_Option ';
4302 END IF;
4303 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
4304 RAISE;
4305
4306 END Set_Default_Analysis_Option;
4307
4308
4309 /************************************************************************************
4310 ************************************************************************************/
4311
4312 end BSC_ANALYSIS_OPTION_PVT;