[Home] [Help]
PACKAGE BODY: APPS.BSC_OBJ_ANALYSIS_OPTIONS_PUB
Source
1 PACKAGE BODY BSC_OBJ_ANALYSIS_OPTIONS_PUB AS
2 /* $Header: BSCPOAOB.pls 120.2.12000000.2 2007/07/27 09:48:46 akoduri noship $ */
3
4
5 /************************************************************************************
6 -- API name : Check_UserLevel_Values
7 -- Type : Private
8 -- Function :
9 -- This API is used to set the user level values . This will be called whenever
10 -- the default is changed.
11 --
12 ************************************************************************************/
13
14 PROCEDURE Check_UserLevel_Values (
15 p_commit IN VARCHAR2 := FND_API.G_FALSE
16 ,p_Indicator IN NUMBER
17 ,p_cascade_shared IN BOOLEAN := FALSE
18 ,x_return_status OUT NOCOPY VARCHAR2
19 ,x_msg_count OUT NOCOPY NUMBER
20 ,x_msg_data OUT NOCOPY VARCHAR2
21 )IS
22
23 l_AnaOpt0_Default bsc_kpi_analysis_groups.default_value%TYPE := 0;
24 l_AnaOpt1_Default bsc_kpi_analysis_groups.default_value%TYPE := 0;
25 l_AnaOpt2_Default bsc_kpi_analysis_groups.default_value%TYPE := 0;
26
27 l_AO1_Parent_Id bsc_kpi_analysis_options_b.parent_option_id%TYPE := 0;
28 l_AO1_GrandParent_Id bsc_kpi_analysis_options_b.grandparent_option_id%TYPE := 0;
29 l_AO2_Parent_Id bsc_kpi_analysis_options_b.parent_option_id%TYPE := 0;
30 l_AO2_GrandParent_Id bsc_kpi_analysis_options_b.grandparent_option_id%TYPE := 0;
31 l_Max_Group_Id NUMBER := 0;
32 l_Parent_Group_Id bsc_kpi_analysis_groups.analysis_group_id%TYPE;
33 l_GrandParent_Group_Id bsc_kpi_analysis_groups.analysis_group_id%TYPE;
34
35
36 CURSOR c_shared_objs IS
37 SELECT
38 indicator
39 FROM
40 bsc_kpis_b
41 WHERE
42 source_indicator = p_Indicator AND
43 prototype_flag <> 2 AND
44 share_flag = 2;
45
46 BEGIN
47 SAVEPOINT Check_UserLevel_Values_PVT;
48 x_return_status := FND_API.G_RET_STS_SUCCESS;
49 FND_MSG_PUB.Initialize;
50
51 SELECT
52 a0_default,a1_default,a2_default
53 INTO
54 l_AnaOpt0_Default, l_AnaOpt1_Default, l_AnaOpt2_Default
55 FROM
56 bsc_db_color_ao_defaults_v
57 WHERE
58 indicator = p_Indicator;
59
60 SELECT
61 MAX(analysis_group_id)
62 INTO
63 l_Max_Group_Id
64 FROM
65 bsc_kpi_analysis_groups
66 WHERE
67 indicator = p_Indicator;
68
69 IF l_Max_Group_Id >= 1 THEN
70 Get_Parent_GrandParent_Ids(
71 p_Indicator => p_Indicator
72 ,p_Analysis_Group_Id => 1
73 ,p_Parent_Id => l_AnaOpt0_Default
74 ,p_GrandParent_Id => 0
75 ,p_Independent_Par_Id => 0
76 ,x_Parent_Id => l_AO1_Parent_Id
77 ,x_GrandParent_Id => l_AO1_GrandParent_Id
78 ,x_Parent_Group_Id => l_Parent_Group_Id
79 ,x_GrandParent_Group_Id => l_GrandParent_Group_Id
80 );
81 END IF;
82
83 IF l_Max_Group_Id = 2 THEN
84 Get_Parent_GrandParent_Ids(
85 p_Indicator => p_Indicator
86 ,p_Analysis_Group_Id => 2
87 ,p_Parent_Id => l_AnaOpt1_Default
88 ,p_GrandParent_Id => l_AnaOpt0_Default
89 ,p_Independent_Par_Id => 0
90 ,x_Parent_Id => l_AO2_Parent_Id
91 ,x_GrandParent_Id => l_AO2_GrandParent_Id
92 ,x_Parent_Group_Id => l_Parent_Group_Id
93 ,x_GrandParent_Group_Id => l_GrandParent_Group_Id
94 );
95 END IF;
96
97 UPDATE bsc_kpi_analysis_options_b
98 SET
99 user_level0 = 2
100 ,user_level1 = 2
101 WHERE indicator = p_Indicator;
102
103 UPDATE bsc_kpi_analysis_options_b
104 SET
105 user_level0 = 1
106 ,user_level1 = 1
107 WHERE
108 indicator = p_Indicator AND
109 ((analysis_group_id = 0 AND option_id = l_AnaOpt0_Default) OR
110 (analysis_group_id = 1 AND option_id = l_AnaOpt1_Default AND parent_option_id = l_AO1_Parent_Id) OR
111 (analysis_group_id = 2 AND option_id = l_AnaOpt2_Default AND parent_option_id = l_AO2_Parent_Id AND grandparent_option_id = l_AO2_GrandParent_Id));
112
113
114 IF p_cascade_shared THEN
115 FOR cd in c_shared_objs LOOP
116 UPDATE bsc_kpi_analysis_options_b
117 SET
118 user_level0 = 2
119 ,user_level1 = 2
120 WHERE indicator = cd.Indicator;
121
122 UPDATE bsc_kpi_analysis_options_b
123 SET
124 user_level0 = 1
125 ,user_level1 = 1
126 WHERE
127 indicator = cd.Indicator AND
128 ((analysis_group_id = 0 AND option_id = l_AnaOpt0_Default) OR
129 (analysis_group_id = 1 AND option_id = l_AnaOpt1_Default AND parent_option_id = l_AO1_Parent_Id) OR
130 (analysis_group_id = 2 AND option_id = l_AnaOpt2_Default AND parent_option_id = l_AO2_Parent_Id AND grandparent_option_id = l_AO2_GrandParent_Id));
131
132 END LOOP;
133 END IF;
134
135 IF FND_API.To_Boolean( p_commit ) THEN
136 COMMIT;
137 END IF;
138
139 EXCEPTION
140 WHEN OTHERS THEN
141 ROLLBACK TO Check_UserLevel_Values_PVT;
142 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
143 IF (x_msg_data IS NOT NULL) THEN
144 x_msg_data := x_msg_data||' -> BSC_OBJ_ANALYSIS_OPTIONS_PUB.Check_UserLevel_Values ';
145 ELSE
146 x_msg_data := SQLERRM||' at BSC_OBJ_ANALYSIS_OPTIONS_PUB.Check_UserLevel_Values ';
147 END IF;
148 END Check_UserLevel_Values;
149
150 /************************************************************************************
151 -- API name : Update_Change_DimSet
152 -- Type : Private
153 -- Function :
154 -- This API sets the change_dim_set flag of bsc_kpi_analysis_groups
155 -- If the current analysis group has this flag set to 1 , the flag corresponding
156 -- to other analysis groups will be reset to 0
157 -- change_dim_set decides , from which group the dimension set should be
158 -- picked up in the current analysis_option combination
159 -- If none of the analysis groups has this flag set to 0, then
160 -- dimension set 0 will be used.
161 ************************************************************************************/
162 PROCEDURE Update_Change_DimSet (
163 p_commit IN VARCHAR2 := FND_API.G_FALSE
164 ,p_Indicator IN NUMBER
165 ,p_Analysis_Group_Id IN NUMBER
166 ,p_Change_Dim_Set IN NUMBER
167 ,x_return_status OUT NOCOPY VARCHAR2
168 ,x_msg_count OUT NOCOPY NUMBER
169 ,x_msg_data OUT NOCOPY VARCHAR2
170 )IS
171
172 BEGIN
173 SAVEPOINT Update_Change_DimSet_PVT;
174 x_return_status := FND_API.G_RET_STS_SUCCESS;
175 FND_MSG_PUB.Initialize;
176
177 UPDATE
178 bsc_kpi_analysis_groups
179 SET
180 change_dim_set = p_Change_Dim_Set
181 WHERE
182 indicator = p_Indicator AND
183 analysis_group_id = p_Analysis_Group_Id;
184
185 IF p_Change_Dim_Set = 1 THEN
186 UPDATE
187 bsc_kpi_analysis_groups
188 SET
189 change_dim_set = 0
190 WHERE
191 indicator = p_Indicator AND
192 analysis_group_id <> p_Analysis_Group_Id;
193 END IF;
194
195 IF FND_API.To_Boolean( p_commit ) THEN
196 COMMIT;
197 END IF;
198
199 EXCEPTION
200 WHEN OTHERS THEN
201 ROLLBACK TO Update_Change_DimSet_PVT;
202 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
203 IF (x_msg_data IS NOT NULL) THEN
204 x_msg_data := x_msg_data||' -> BSC_OBJ_ANALYSIS_OPTIONS_PUB.Update_Change_DimSet ';
205 ELSE
206 x_msg_data := SQLERRM||' at BSC_OBJ_ANALYSIS_OPTIONS_PUB.Update_Change_DimSet ';
207 END IF;
208 END Update_Change_DimSet;
209
210
211 /************************************************************************************
212 -- API name : Update_Default_Flag_Val
213 -- Type : Private
214 -- Function :
215 -- Updates the bsc_kpi_analysis_groups with the current option id
216 -- Also Cascade the dependent default value of the related groups i.e
217 -- (bsc_kpi_analysis_groups dependency_flag = 1)
218 -- 1. When child is set as the default make its parent as the default for
219 -- the parent analysis group
220 -- 2. When parent is set as the default, reset the default in child group to 0
221
222 ************************************************************************************/
223
224 PROCEDURE Update_Default_Flag_Value(
225 p_commit IN VARCHAR2 := FND_API.G_FALSE
226 ,p_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
227 ,x_return_status OUT NOCOPY VARCHAR2
228 ,x_msg_count OUT NOCOPY NUMBER
229 ,x_msg_data OUT NOCOPY VARCHAR2
230 ) IS
231
232 CURSOR c_dependency_flag(p_ana_grp_id NUMBER) IS
233 SELECT
234 dependency_flag
235 FROM
236 bsc_kpi_analysis_groups
237 WHERE indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
238 AND analysis_group_id = p_ana_grp_id;
239
240 l_Dependency01 bsc_kpi_analysis_groups.dependency_flag%TYPE := 0;
241 l_Dependency12 bsc_kpi_analysis_groups.dependency_flag%TYPE := 0;
242
243 l_Temp_Ana_Grp_Id bsc_kpi_analysis_options_b.analysis_group_id%TYPE;
244 l_Temp_Ana_Option_Id bsc_kpi_analysis_options_b.option_id%TYPE;
245
246 BEGIN
247
248 SAVEPOINT Update_Default_Flag_Val_PVT;
249 x_return_status := FND_API.G_RET_STS_SUCCESS;
250 FND_MSG_PUB.Initialize;
251
252 --Only if the default value is modified
253 UPDATE
254 bsc_kpi_analysis_groups
255 SET
256 default_value = p_Anal_Opt_Rec.Bsc_Analysis_Option_Id
257 WHERE
258 indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id AND
259 analysis_group_id = p_Anal_Opt_Rec.Bsc_Analysis_Group_Id;
260
261
262 OPEN c_dependency_flag(1);
263 FETCH c_dependency_flag INTO l_Dependency01;
264 CLOSE c_dependency_flag;
265
266 OPEN c_dependency_flag(2);
267 FETCH c_dependency_flag INTO l_Dependency12;
268 CLOSE c_dependency_flag;
269
270 IF ( l_Dependency01 = 1) THEN
271 CASE p_Anal_Opt_Rec.Bsc_Analysis_Group_Id
272 WHEN 0 THEN
273 l_Temp_Ana_Grp_Id := 1;
274 l_Temp_Ana_Option_Id := 0;
275 WHEN 1 THEN
276 l_Temp_Ana_Grp_Id := 0;
277 l_Temp_Ana_Option_Id := p_Anal_Opt_Rec.Bsc_Parent_Option_Id;--l_Parent_Option_Id;
278 WHEN 2 THEN
279 l_Temp_Ana_Grp_Id := 0;
280 l_Temp_Ana_Option_Id := p_Anal_Opt_Rec.Bsc_Grandparent_Option_Id;--l_GrandParent_Opt_Id;
281 END CASE;
282
283 UPDATE
284 bsc_kpi_analysis_groups
285 SET
286 default_value = l_Temp_Ana_Option_Id
287 WHERE
288 indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id AND
289 analysis_group_id = l_Temp_Ana_Grp_Id;
290 END IF;
291
292 IF ( l_Dependency12 = 1) THEN
293 CASE p_Anal_Opt_Rec.Bsc_Analysis_Group_Id
294 WHEN 0 THEN
295 l_Temp_Ana_Grp_Id := 2;
296 l_Temp_Ana_Option_Id := 0;
297 WHEN 1 THEN
298 l_Temp_Ana_Grp_Id := 2;
299 l_Temp_Ana_Option_Id := 0;
300 WHEN 2 THEN
301 l_Temp_Ana_Grp_Id := 1;
302 l_Temp_Ana_Option_Id := p_Anal_Opt_Rec.Bsc_Parent_Option_Id;
303 END CASE;
304
305 UPDATE
306 bsc_kpi_analysis_groups
307 SET
308 default_value = l_Temp_Ana_Option_Id
309 WHERE
310 indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id AND
311 analysis_group_id = l_Temp_Ana_Grp_Id;
312 END IF;
313
314
315 IF FND_API.To_Boolean( p_commit ) THEN
316 COMMIT;
317 END IF;
318 EXCEPTION
319 WHEN OTHERS THEN
320 ROLLBACK TO Update_Default_Flag_Val_PVT;
321 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
322 IF (x_msg_data IS NOT NULL) THEN
323 x_msg_data := x_msg_data||' -> BSC_OBJ_ANALYSIS_OPTIONS_PUB.Update_Default_Flag_Val ';
324 ELSE
325 x_msg_data := SQLERRM||' at BSC_OBJ_ANALYSIS_OPTIONS_PUB.Update_Default_Flag_Val ';
326 END IF;
327 END Update_Default_Flag_Value;
328
329
330 /************************************************************************************
331 -- API name : Check_YTD_Apply
332 -- Type : Private
333 -- Function :
334 --
335 ************************************************************************************/
336 PROCEDURE Check_YTD_Apply(
337 p_commit IN VARCHAR2 := FND_API.G_FALSE
338 ,p_Indicator IN NUMBER
339 ,x_return_status OUT NOCOPY VARCHAR2
340 ,x_msg_count OUT NOCOPY NUMBER
341 ,x_msg_data OUT NOCOPY VARCHAR2
342 ) IS
343
344 l_YTD_Value bsc_kpi_calculations.default_Value%TYPE;
345 l_AO0_Default bsc_kpi_analysis_groups.default_value%TYPE;
346 l_AO1_Default bsc_kpi_analysis_groups.default_value%TYPE;
347 l_AO2_Default bsc_kpi_analysis_groups.default_value%TYPE;
348 l_Count NUMBER := 0;
349 CURSOR c_YTD_Value IS
350 SELECT
351 default_value
352 FROM
353 bsc_kpi_calculations
354 WHERE
355 indicator = p_Indicator AND
356 calculation_id = 2; -- YTD Default Value
357
358 CURSOR c_Is_YTD_Valid(p_AO0 NUMBER,p_AO1 NUMBER, p_AO2 NUMBER) IS
359 SELECT
360 COUNT(1)
361 FROM
362 bsc_kpi_analysis_measures_b km,
363 bsc_sys_dataset_calc bd
364 WHERE
365 km.indicator = p_Indicator AND
366 km.dataset_id = bd.dataset_id AND
367 km.analysis_option0 = p_AO0 AND
368 km.analysis_option1 = p_AO1 AND
369 km.analysis_option2 = p_AO2 AND
370 bd.disabled_calc_id = 2;
371
372
373 BEGIN
374 SAVEPOINT Check_YTD_Apply_PVT;
375 x_return_status := FND_API.G_RET_STS_SUCCESS;
376 FND_MSG_PUB.Initialize;
377
378
379 OPEN c_YTD_Value;
380 FETCH c_YTD_Value INTO l_YTD_Value;
381 CLOSE c_YTD_Value;
382
383 --If user didnt enable Color By Calulation based on YTD then no need to validate it
384 IF l_YTD_Value = 0 THEN
385 RETURN;
386 END IF;
387
388 SELECT
389 a0_default,a1_default,a2_default
390 INTO
391 l_AO0_Default, l_AO1_Default, l_AO2_Default
392 FROM
393 bsc_db_color_ao_defaults_v
394 WHERE
395 indicator = p_Indicator;
396
397 OPEN c_Is_YTD_Valid(l_AO0_Default, l_AO1_Default, l_AO2_Default);
398 FETCH c_Is_YTD_Valid INTO l_Count;
399 IF l_Count > 0 THEN
400 -- If YTD calculation is disabled at the measure level then disable it for the new default kpi
401 UPDATE
402 bsc_kpi_calculations
403 SET
404 default_value = 0,
405 user_level0 = 1,
406 user_level1 = 1
407 WHERE indicator = p_Indicator;
408
409 IF BSC_COLOR_CALC_UTIL.Get_Obj_Color_Rollup_Type(p_Indicator) = BSC_COLOR_CALC_UTIL.DEFAULT_KPI THEN
410 BSC_DESIGNER_PVT.ActionFlag_Change (
411 x_indicator => p_Indicator
412 ,x_newflag => BSC_DESIGNER_PVT.G_ActionFlag.GAA_Color
413 );
414 END IF;
415 END IF;
416
417 IF FND_API.To_Boolean( p_commit ) THEN
418 COMMIT;
419 END IF;
420 EXCEPTION
421 WHEN OTHERS THEN
422 ROLLBACK TO Check_YTD_Apply_PVT;
423 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
424 IF (x_msg_data IS NOT NULL) THEN
425 x_msg_data := x_msg_data||' -> BSC_OBJ_ANALYSIS_OPTIONS_PUB.Val_If_YTD_Apply ';
426 ELSE
427 x_msg_data := SQLERRM||' at BSC_OBJ_ANALYSIS_OPTIONS_PUB.Val_If_YTD_Apply ';
428 END IF;
429 END Check_YTD_Apply;
430
431
432 /************************************************************************************
433 -- API name : Check_Default_Props
434 -- Type : Private
435 -- Function :
436 -- 1. Cascades the default flag change
437 -- 2. Checks if atleast one series is selected as the default
438 -- 3. Performs the validations required for color by kpi
439 -- 4. If YTD is enabled, it checks whether this calculation holds good
440 -- for the current default combination
441 -- 5. Marks the objective for color recalculation if the coloring is default
442 -- kpi based
443 ************************************************************************************/
444 PROCEDURE Check_Default_Props(
445 p_commit IN VARCHAR2 := FND_API.G_FALSE
446 ,p_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
447 ,p_cascade_shared BOOLEAN := FALSE
448 ,x_return_status OUT NOCOPY VARCHAR2
449 ,x_msg_count OUT NOCOPY NUMBER
450 ,x_msg_data OUT NOCOPY VARCHAR2
451 )IS
452
453
454 l_Old_Default_Value bsc_kpi_analysis_groups.default_value%TYPE;
455 l_commit VARCHAR2(1) := FND_API.G_FALSE;
456 l_Anal_Opt_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
457 l_Series_Id bsc_kpi_analysis_measures_b.series_id%TYPE := 0;
458 l_Budget_Flag bsc_kpi_analysis_measures_b.budget_flag%TYPE := 1;
459 CURSOR c_Old_Default_Value IS
460 SELECT
461 default_value
462 FROM
463 bsc_kpi_analysis_groups
464 WHERE indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
465 AND analysis_group_id = p_Anal_Opt_Rec.Bsc_Analysis_Group_Id;
466
467 CURSOR c_Default_Series_Id (p_AO0 NUMBER, p_AO1 NUMBER, p_AO2 NUMBER) IS
468 SELECT
469 series_id,budget_flag
470 FROM
471 bsc_kpi_analysis_Measures_b
472 WHERE
473 analysis_option0 = p_AO0 AND
474 analysis_option1 = p_AO1 AND
475 analysis_option2 = p_AO2 AND
476 default_value = 1;
477
478
479 CURSOR c_shared_objs IS
480 SELECT
481 indicator
482 FROM
483 bsc_kpis_b
484 WHERE
485 source_indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id AND
486 prototype_flag <> 2 AND
487 share_flag = 2;
488
489 BEGIN
490 SAVEPOINT Check_Default_Props_PVT;
491 x_return_status := FND_API.G_RET_STS_SUCCESS;
492
493 OPEN c_Old_Default_Value;
494 FETCH c_Old_Default_Value INTO l_Old_Default_Value;
495 CLOSE c_Old_Default_Value;
496
497 l_Anal_Opt_Rec := p_Anal_Opt_Rec;
498 -- No need to cascade any changes. This is not the default
499 IF p_Anal_Opt_Rec.Bsc_Option_Default_Value = 0 OR l_Old_Default_Value = p_Anal_Opt_Rec.Bsc_Analysis_Option_Id THEN
500 RETURN;
501 END IF;
502
503 Update_Default_Flag_Value (
504 p_commit => l_commit
505 ,p_Anal_Opt_Rec => p_Anal_Opt_Rec
506 ,x_return_status => x_return_status
507 ,x_msg_count => x_msg_count
508 ,x_msg_data => x_msg_data
509 );
510 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
511 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
512 END IF;
513
514 BSC_KPI_SERIES_PUB.Check_Series_Default_Props(
515 p_commit => l_commit
516 ,p_Indicator => p_Anal_Opt_Rec.Bsc_Kpi_Id
517 ,x_return_status => x_return_status
518 ,x_msg_count => x_msg_count
519 ,x_msg_data => x_msg_data
520 );
521 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
522 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
523 END IF;
524
525 OPEN c_Default_Series_Id(p_Anal_Opt_Rec.Bsc_Option_Group0, p_Anal_Opt_Rec.Bsc_Option_Group1, p_Anal_Opt_Rec.Bsc_Option_Group2);
526 FETCH c_Default_Series_Id INTO l_Series_Id, l_Budget_Flag;
527 CLOSE c_Default_Series_Id;
528
529
530 BSC_KPI_SERIES_PUB.Check_Color_Props(
531 p_commit => FND_API.G_FALSE
532 ,p_Indicator => p_Anal_Opt_Rec.Bsc_Kpi_Id
533 ,p_Analysis_Option0 => p_Anal_Opt_Rec.Bsc_Option_Group0
534 ,p_Analysis_Option1 => p_Anal_Opt_Rec.Bsc_Option_Group1
535 ,p_Analysis_Option2 => p_Anal_Opt_Rec.Bsc_Option_Group2
536 ,p_Series_Id => l_Series_Id
537 ,p_Budget_Flag => l_Budget_Flag
538 ,p_Default_Flag => 1
539 ,p_Dataset_Id => p_Anal_Opt_Rec.Bsc_Dataset_Id
540 ,x_return_status => x_return_status
541 ,x_msg_count => x_msg_count
542 ,x_msg_data => x_msg_data
543 );
544 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
545 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
546 END IF;
547
548 -- Validate_Calculations
549 Check_YTD_Apply(
550 p_commit => l_commit
551 ,p_Indicator => p_Anal_Opt_Rec.Bsc_Kpi_Id
552 ,x_return_status => x_return_status
553 ,x_msg_count => x_msg_count
554 ,x_msg_data => x_msg_data
555 );
556 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
557 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
558 END IF;
559
560 -- Check Color Change
561 IF BSC_COLOR_CALC_UTIL.Get_Obj_Color_Rollup_Type(p_Anal_Opt_Rec.Bsc_Kpi_Id) = BSC_COLOR_CALC_UTIL.DEFAULT_KPI THEN
562 BSC_DESIGNER_PVT.ActionFlag_Change (
563 x_indicator => p_Anal_Opt_Rec.Bsc_Kpi_Id
564 ,x_newflag => BSC_DESIGNER_PVT.G_ActionFlag.GAA_Color
565 );
566 END IF;
567
568 IF p_cascade_shared THEN -- cascade to shared
569
570 FOR cd in c_shared_objs LOOP
571 l_Anal_Opt_Rec.Bsc_Kpi_Id := cd.Indicator;
572 Update_Default_Flag_Value (
573 p_commit => l_commit
574 ,p_Anal_Opt_Rec => l_Anal_Opt_Rec
575 ,x_return_status => x_return_status
576 ,x_msg_count => x_msg_count
577 ,x_msg_data => x_msg_data
578 );
579 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
580 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
581 END IF;
582
583 BSC_KPI_SERIES_PUB.Check_Series_Default_Props(
584 p_commit => l_commit
585 ,p_Indicator => l_Anal_Opt_Rec.Bsc_Kpi_Id
586 ,x_return_status => x_return_status
587 ,x_msg_count => x_msg_count
588 ,x_msg_data => x_msg_data
589 );
590 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
591 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
592 END IF;
593
594 Check_YTD_Apply(
595 p_commit => l_commit
596 ,p_Indicator => l_Anal_Opt_Rec.Bsc_Kpi_Id
597 ,x_return_status => x_return_status
598 ,x_msg_count => x_msg_count
599 ,x_msg_data => x_msg_data
600 );
601 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
602 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
603 END IF;
604
605 BSC_KPI_SERIES_PUB.Check_Color_Props(
606 p_commit => FND_API.G_FALSE
607 ,p_Indicator => l_Anal_Opt_Rec.Bsc_Kpi_Id
608 ,p_Analysis_Option0 => l_Anal_Opt_Rec.Bsc_Option_Group0
609 ,p_Analysis_Option1 => l_Anal_Opt_Rec.Bsc_Option_Group1
610 ,p_Analysis_Option2 => l_Anal_Opt_Rec.Bsc_Option_Group2
611 ,p_Series_Id => 0
612 ,p_Budget_Flag => 1
613 ,p_Default_Flag => 1
614 ,p_Dataset_Id => l_Anal_Opt_Rec.Bsc_Dataset_Id
615 ,x_return_status => x_return_status
616 ,x_msg_count => x_msg_count
617 ,x_msg_data => x_msg_data
618 );
619
620 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
621 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
622 END IF;
623
624 IF BSC_COLOR_CALC_UTIL.Get_Obj_Color_Rollup_Type(l_Anal_Opt_Rec.Bsc_Kpi_Id) = BSC_COLOR_CALC_UTIL.DEFAULT_KPI THEN
625 BSC_DESIGNER_PVT.ActionFlag_Change (
626 x_indicator => l_Anal_Opt_Rec.Bsc_Kpi_Id
627 ,x_newflag => BSC_DESIGNER_PVT.G_ActionFlag.GAA_Color
628 );
629 END IF;
630 END LOOP;
631
632 END IF;
633
634 IF FND_API.To_Boolean( p_commit ) THEN
635 COMMIT;
636 END IF;
637
638 EXCEPTION
639 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
640 ROLLBACK TO Check_Default_Props_PVT;
641 IF (x_msg_data IS NULL) THEN
642 FND_MSG_PUB.Count_And_Get
643 ( p_encoded => FND_API.G_FALSE
644 , p_count => x_msg_count
645 , p_data => x_msg_data
646 );
647 END IF;
648 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
649 WHEN OTHERS THEN
650 ROLLBACK TO Check_Default_Props_PVT;
651 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
652 IF (x_msg_data IS NOT NULL) THEN
653 x_msg_data := x_msg_data||' -> BSC_OBJ_ANALYSIS_OPTIONS_PUB.Check_Default_Props ';
654 ELSE
655 x_msg_data := SQLERRM||' at BSC_OBJ_ANALYSIS_OPTIONS_PUB.Check_Default_Props ';
656 END IF;
657 END Check_Default_Props;
658
659 /************************************************************************************
660 -- API name : Is_Analysis_Drill
661 -- Type : Private
662 -- Function :
663 -- Verifies whether the current analysis group has change_dim_set flag checked
664 -- or not
665 ************************************************************************************/
666
667 FUNCTION Is_Analysis_Drill (
668 p_Indicator IN NUMBER
669 ,p_Analysis_Group_Id IN NUMBER
670 ) RETURN VARCHAR2 IS
671 l_ana_drill bsc_kpi_analysis_groups.change_dim_set%TYPE := 0;
672 CURSOR c_Ana_Drill IS
673 SELECT
674 NVL(change_dim_set,0)
675 FROM
676 bsc_kpi_analysis_groups
677 WHERE
678 indicator = p_Indicator AND
679 analysis_group_id = p_Analysis_Group_Id;
680 BEGIN
681
682 OPEN c_Ana_Drill;
683 FETCH c_Ana_Drill INTO l_ana_drill;
684 CLOSE c_Ana_Drill;
685
686 IF l_ana_drill = 1 THEN
687 RETURN FND_API.G_TRUE;
688 END IF;
689 RETURN FND_API.G_FALSE;
690
691 EXCEPTION
692 WHEN OTHERS THEN
693 RETURN FND_API.G_FALSE;
694 END Is_Analysis_Drill;
695
696 /************************************************************************************
697 -- API name : Get_Analysis_Option_Default
698 -- Type : Public
699 -- Function : Function which returns the default analysis option id
700 -- for a given analysis group of an indicator
701 ************************************************************************************/
702 FUNCTION Get_Analysis_Option_Default (
703 p_Indicator IN NUMBER
704 ,p_Analysis_Group_Id IN NUMBER
705 ) RETURN NUMBER
706 IS
707 l_Default_Option_Id bsc_kpi_analysis_groups.default_value%TYPE := 0;
708 CURSOR c_Default_Option IS
709 SELECT
710 default_value
711 FROM
712 bsc_kpi_analysis_groups
713 WHERE
714 indicator = p_Indicator AND
715 analysis_group_id = p_Analysis_Group_Id ;
716 BEGIN
717
718 OPEN c_Default_Option;
719 FETCH c_Default_Option INTO l_Default_Option_Id;
720 CLOSE c_Default_Option;
721
722 RETURN l_Default_Option_Id;
723
724 EXCEPTION
725 WHEN OTHERS THEN
726 RETURN l_Default_Option_Id;
727 END Get_Analysis_Option_Default;
728
729 /************************************************************************************
730 -- API name : Get_Parent_GrandParent_Ids
731 -- Type : Private
732 -- Function :
733 -- This API takes as input the parent,grandparent analysis option ids that are
734 -- received from UI and finds out the corresponding entry in
735 -- bsc_kpi_analysis_options
736 -- This is specially required when the groups are having an independent
737 -- relationship. (In case of independent relationship the parent id will be
738 -- stored as zero)
739 -- This API takes care of all the possible combinations between groups
740 -- Dependent-Dependent
741 -- Dependent-Independent
742 -- Independent-Dependent
743 -- Independent-Independent
744 --
745 -- Parameters:
746 -- p_Analysis_Group_Id - The current level of the analysis option
747 -- p_Parent_Id, p_GrandParent_Id - The parent and grandparent analysis option ids
748 -- in the HGrid hierarchy
749 -- p_Independent_Par_Id - This indicates what the caller API expects in place
750 -- of parent id when there is an independent relationship.
751 -- x_Parent_Id,x_GrandParent_Id - Returns the parent and grandparent ids
752 -- maintained in bsc_kpi_analysis_options table
753 -- x_Parent_Group_Id,x_GrandParent_Group_Id - Also returns the parent and
754 -- grand parent group ids.
755 ************************************************************************************/
756
757 PROCEDURE Get_Parent_GrandParent_Ids(
758 p_Indicator IN NUMBER
759 ,p_Analysis_Group_Id IN NUMBER
760 ,p_Parent_Id IN NUMBER
761 ,p_GrandParent_Id IN NUMBER
762 ,p_Independent_Par_Id IN NUMBER := 0
763 ,x_Parent_Id OUT NOCOPY NUMBER
764 ,x_GrandParent_Id OUT NOCOPY NUMBER
765 ,x_Parent_Group_Id OUT NOCOPY NUMBER
766 ,x_GrandParent_Group_Id OUT NOCOPY NUMBER
767 ) IS
768
769 l_Dependency01 bsc_kpi_analysis_groups.dependency_flag%TYPE := 0;
770 l_Dependency12 bsc_kpi_analysis_groups.dependency_flag%TYPE := 0;
771
772 CURSOR c_dependency_flag(p_group_id NUMBER) IS
773 SELECT
774 ag.dependency_flag
775 FROM
776 bsc_kpi_analysis_groups ag
777 WHERe
778 ag.indicator = p_Indicator AND
779 ag.analysis_group_id = p_group_id;
780
781 BEGIN
782
783 x_Parent_Id := p_Parent_Id;
784 x_GrandParent_Id := p_GrandParent_Id;
785 x_Parent_Group_Id := -1;
786 x_GrandParent_Group_Id := -1;
787
788 OPEN c_dependency_flag(1);
789 FETCH c_dependency_flag INTO l_Dependency01;
790 CLOSE c_dependency_flag;
791
792 OPEN c_dependency_flag(2);
793 FETCH c_dependency_flag INTO l_Dependency12;
794 CLOSE c_dependency_flag;
795
796
797 CASE p_Analysis_Group_Id
798 WHEN 0 THEN
799 NULL;
800 WHEN 1 THEN
801 x_Parent_Group_Id := 0;
802 IF l_Dependency01 = 0 THEN
803 x_Parent_Id := p_Independent_Par_Id;
804 x_Parent_Group_Id := p_Independent_Par_Id;
805 END IF;
806 WHEN 2 THEN
807 x_GrandParent_Group_Id := 0;
808 x_Parent_Group_Id := 1;
809 IF l_Dependency12 = 0 THEN
810 x_GrandParent_Id := p_Independent_Par_Id;
811 x_GrandParent_Group_Id := p_Independent_Par_Id;
812 x_Parent_Id := p_Independent_Par_Id;
813 x_Parent_Group_Id := p_Independent_Par_Id;
814 ELSIF l_Dependency01 = 0 THEN
815 x_GrandParent_Id := p_Independent_Par_Id;
816 x_GrandParent_Group_Id := p_Independent_Par_Id;
817 END IF;
818 END CASE;
819
820
821 EXCEPTION
822 WHEN OTHERS THEN
823 NULL;
824 END Get_Parent_GrandParent_Ids;
825
826 /************************************************************************************
827 -- API name : Get_Current_Dim_DataSet_Map
828 -- Type : Private
829 ************************************************************************************/
830
831 PROCEDURE Get_Current_Dim_DataSet_Map (
832 p_Indicator IN NUMBER
833 ,x_dim_Dataset_map OUT NOCOPY BSC_KPI_SERIES_PUB.Bsc_Dim_Dataset_Table
834 ) IS
835 BEGIN
836 SELECT DISTINCT
837 dim.dim_set_id, dim.dataset_id,0
838 BULK COLLECT INTO
839 x_dim_Dataset_map
840 FROM
841 bsc_db_dataset_dim_sets_v dim,
842 bsc_sys_datasets_b ds
843 WHERE
844 dim.indicator = p_Indicator AND
845 dim.dataset_id = ds.dataset_id AND
846 ds.source = 'BSC'
847 ORDER BY
848 dim_set_id, dataset_id;
849 EXCEPTION
850 WHEN OTHERS THEN
851 NULL;
852 END Get_Current_Dim_DataSet_Map;
853 /************************************************************************************
854 -- API name : Check_Strucural_Flag_Change
855 -- Type : Private
856 ************************************************************************************/
857
858 PROCEDURE Check_Strucural_Flag_Change(
859 p_commit IN VARCHAR2 := FND_API.G_FALSE
860 ,p_Indicator IN NUMBER
861 ,p_olddim_Dataset_map IN BSC_KPI_SERIES_PUB.Bsc_Dim_Dataset_Table
862 ,p_cascade_shared BOOLEAN := FALSE
863 ,x_return_status OUT NOCOPY VARCHAR2
864 ,x_msg_count OUT NOCOPY NUMBER
865 ,x_msg_data OUT NOCOPY VARCHAR2
866 ) IS
867
868 l_newdim_Dataset_map BSC_KPI_SERIES_PUB.Bsc_Dim_Dataset_Table;
869 isStructureChange BOOLEAN;
870
871 CURSOR c_shared_objs IS
872 SELECT
873 indicator
874 FROM
875 bsc_kpis_b
876 WHERE
877 source_indicator = p_Indicator AND
878 prototype_flag <> 2 AND
879 share_flag = 2;
880 BEGIN
881 Get_Current_Dim_DataSet_Map (
882 p_Indicator => p_Indicator
883 ,x_dim_Dataset_map => l_newdim_Dataset_map
884 );
885
886 IF p_olddim_Dataset_map.COUNT <> l_newdim_Dataset_map.COUNT THEN
887 isStructureChange := TRUE;
888 ELSE
889 FOR i in 1..p_olddim_Dataset_map.COUNT LOOP
890 IF (p_olddim_Dataset_map(i).dim_set_id <> l_newdim_Dataset_map(i).dim_set_id OR
891 p_olddim_Dataset_map(i).dataset_id <> l_newdim_Dataset_map(i).dataset_id) THEN
892 isStructureChange := TRUE;
893 END IF;
894 END LOOP;
895 END IF;
896
897 IF isStructureChange THEN
898 BSC_DESIGNER_PVT.ActionFlag_Change(p_Indicator , BSC_DESIGNER_PVT.G_ActionFlag.GAA_Structure);
899 IF p_cascade_shared THEN
900 FOR cd IN c_shared_objs LOOP
901 BSC_DESIGNER_PVT.ActionFlag_Change(cd.indicator , BSC_DESIGNER_PVT.G_ActionFlag.GAA_Structure);
902 END LOOP;
903 END IF;
904 END IF;
905
906 EXCEPTION
907 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
908 ROLLBACK TO Update_Analayis_Option_PUB;
909 IF (x_msg_data IS NULL) THEN
910 FND_MSG_PUB.Count_And_Get
911 ( p_encoded => FND_API.G_FALSE
912 , p_count => x_msg_count
913 , p_data => x_msg_data
914 );
915 END IF;
916 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
917 WHEN OTHERS THEN
918 ROLLBACK TO Update_Analayis_Option_PUB;
919 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
920 IF (x_msg_data IS NOT NULL) THEN
921 x_msg_data := x_msg_data||' -> BSC_OBJ_ANALYSIS_OPTIONS_PUB.Check_Strucural_Flag_Change ';
922 ELSE
923 x_msg_data := SQLERRM||' at BSC_OBJ_ANALYSIS_OPTIONS_PUB.Check_Strucural_Flag_Change ';
924 END IF;
925 END Check_Strucural_Flag_Change;
926
927 /************************************************************************************
928 -- API name : Update_Analysis_Option_UI
929 -- Type : Public
930 -- Function :
931 -- 1. Validates and cascades the default flag updation
932 -- 2. Imports the measure as well as the dimensions incase of bis measure
933 -- Incase of bsc measure calls the analysis measure and analysis option
934 -- Update APIs to cascade the changes
935 -- 3. Updates the change_dim_set property
936 -- 4. Checks for the list button validation if the current analysis group
937 -- has the change_dim_set property set
938 -- 5. Refreshes the bsc_kpi_defaults tables with the current defaults
939 -- 6. Checks for structural changes and updates the prototype_flag accordingly
940 ************************************************************************************/
941
942 PROCEDURE Update_Analysis_Option_UI(
943 p_commit IN VARCHAR2 := FND_API.G_FALSE
944 ,p_Indicator IN NUMBER
945 ,p_Analysis_Group_Id IN NUMBER := 0
946 ,p_Option_Id IN NUMBER := 0
947 ,p_Parent_Option_Id IN NUMBER := 0
948 ,p_GrandParent_Option_Id IN NUMBER := 0
949 ,p_Dependency_Flag IN NUMBER := 0
950 ,p_DataSet_Id IN NUMBER := NULL
951 ,p_DimSet_Id IN NUMBER := 0
952 ,p_Default_Flag IN NUMBER := 0
953 ,p_Option_Name IN VARCHAR2
954 ,p_Option_Help IN VARCHAR2
955 ,p_Change_Dim_Set IN NUMBER := 0
956 ,p_default_calculation IN NUMBER := NULL
957 ,p_Create_Flow IN VARCHAR2 := FND_API.G_FALSE
958 ,p_time_stamp IN VARCHAR2 := NULL
959 ,p_olddim_Dataset_map IN BSC_KPI_SERIES_PUB.Bsc_Dim_Dataset_Table := NULL
960 ,x_return_status OUT NOCOPY VARCHAR2
961 ,x_msg_count OUT NOCOPY NUMBER
962 ,x_msg_data OUT NOCOPY VARCHAR2
963 ) IS
964 l_AO0 bsc_kpi_analysis_measures_b.analysis_option0%TYPE;
965 l_AO1 bsc_kpi_analysis_measures_b.analysis_option1%TYPE;
966 l_AO2 bsc_kpi_analysis_measures_b.analysis_option2%TYPE;
967
968 l_Bsc_Kpi_Entity_Rec BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
969 l_Bsc_AnaOpt_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
970 l_Count NUMBER := 0;
971 l_DimSet_Id bsc_kpi_analysis_options_b.dim_set_id%TYPE := 0;
972 l_Measure_Source bsc_sys_datasets_vl.source%TYPE;
973 l_commit VARCHAR2(2) := FND_API.G_FALSE;
974
975 l_config_type bsc_kpis_b.config_type%TYPE;
976 l_indicator_type bsc_kpis_b.indicator_type%TYPE;
977
978 l_old_DimSet_id bsc_kpi_analysis_options_b.dim_set_id%TYPE;
979 l_old_data_set_id bsc_kpi_analysis_measures_b.dataset_id%TYPE;
980
981 l_temp_Parent_Id NUMBER := NULL;
982 l_temp_GrandParent_Id NUMBER := NULL;
983 l_Parent_Group_Id bsc_kpi_analysis_groups.analysis_group_id%TYPE := 0;
984 l_GrandParent_Group_Id bsc_kpi_analysis_groups.analysis_group_id%TYPE := 0;
985
986 l_olddim_Dataset_map BSC_KPI_SERIES_PUB.Bsc_Dim_Dataset_Table;
987 l_newdim_Dataset_map BSC_KPI_SERIES_PUB.Bsc_Dim_Dataset_Table;
988 isStructureChange BOOLEAN := FALSE;
989 i NUMBER;
990
991
992 CURSOR c_old_data_set_id(p_AO0 VARCHAR2, p_AO1 VARCHAR2, p_AO2 VARCHAR2) IS
993 SELECT
994 dataset_id
995 FROM
996 BSC_KPI_ANALYSIS_MEASURES_B
997 WHERE indicator = p_Indicator
998 AND analysis_option0 = p_AO0
999 AND analysis_option1 = p_AO1
1000 AND analysis_option2 = p_AO2;
1001
1002 CURSOR
1003 c_old_dim_set_id IS
1004 SELECT
1005 dim_set_id
1006 FROM
1007 bsc_kpi_analysis_options_b
1008 WHERE analysis_group_id = p_Analysis_Group_Id
1009 AND option_id = p_Option_Id
1010 AND parent_option_id = p_Parent_Option_Id
1011 AND grandparent_option_id = p_GrandParent_Option_Id;
1012
1013 CURSOR c_shared_objs IS
1014 SELECT
1015 indicator
1016 FROM
1017 bsc_kpis_b
1018 WHERE
1019 source_indicator = p_Indicator AND
1020 prototype_flag <> 2 AND
1021 share_flag = 2;
1022
1023 BEGIN
1024 SAVEPOINT Update_Analayis_Option_PUB;
1025 -- Check that the indicator id passed is Valid
1026 IF NOT FND_API.To_Boolean(p_Create_Flow) THEN
1027 IF p_Indicator IS NOT NULL THEN
1028 l_Count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_KPIS_B'
1029 ,'indicator'
1030 , p_Indicator);
1031 IF l_Count = 0 THEN
1032 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_KPI_ID');
1033 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Indicator);
1034 FND_MSG_PUB.ADD;
1035 RAISE FND_API.G_EXC_ERROR;
1036 END IF;
1037 ELSE
1038 FND_MESSAGE.SET_NAME('BSC','BSC_NO_KPI_ID_ENTERED');
1039 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Indicator);
1040 FND_MSG_PUB.ADD;
1041 RAISE FND_API.G_EXC_ERROR;
1042 END IF;
1043 BSC_BIS_LOCKS_PUB.LOCK_KPI
1044 ( p_Kpi_Id => p_Indicator
1045 , p_time_stamp => p_time_stamp
1046 , p_Full_Lock_Flag => NULL
1047 , x_return_status => x_return_status
1048 , x_msg_count => x_msg_count
1049 , x_msg_data => x_msg_data
1050 );
1051 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1052 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1053 END IF;
1054 END IF;
1055
1056 SELECT indicator_type,config_type
1057 INTO l_indicator_type,l_config_type
1058 FROM bsc_kpis_b
1059 WHERE indicator = p_Indicator;
1060
1061 IF NOT FND_API.To_Boolean(p_Create_Flow) THEN
1062 Get_Current_Dim_DataSet_Map (
1063 p_Indicator => p_Indicator
1064 ,x_dim_Dataset_map => l_olddim_Dataset_map
1065 );
1066 ELSE
1067 l_olddim_Dataset_map := p_olddim_Dataset_map;
1068 END IF;
1069
1070
1071 CASE p_Analysis_Group_Id
1072 WHEN 0 THEN
1073 l_AO0 := p_Option_Id;
1074 l_AO1 := 0;
1075 l_AO2 := 0;
1076 WHEN 1 THEN
1077 l_AO0 := p_Parent_Option_Id;
1078 l_AO1 := p_Option_Id;
1079 l_AO2 := 0;
1080 WHEN 2 THEN
1081 l_AO0 := p_GrandParent_Option_Id;
1082 l_AO1 := p_Parent_Option_Id;
1083 l_AO2 := p_Option_Id;
1084 END CASE;
1085
1086 l_Bsc_AnaOpt_Rec.Bsc_Kpi_Id := p_Indicator;
1087 l_Bsc_AnaOpt_Rec.Bsc_Analysis_Group_Id := p_Analysis_Group_Id;
1088 l_Bsc_AnaOpt_Rec.Bsc_Analysis_Option_Id := p_Option_Id;
1089 l_Bsc_AnaOpt_Rec.Bsc_Parent_Option_Id := p_Parent_Option_Id;
1090 l_Bsc_AnaOpt_Rec.Bsc_Grandparent_Option_Id := p_GrandParent_Option_Id;
1091 l_Bsc_AnaOpt_Rec.Bsc_Dataset_Id := p_DataSet_Id;
1092 l_Bsc_AnaOpt_Rec.Bsc_Option_Default_Value := p_Default_Flag;
1093 l_Bsc_AnaOpt_Rec.Bsc_Option_Group0 := l_AO0;
1094 l_Bsc_AnaOpt_Rec.Bsc_Option_Group1 := l_AO1;
1095 l_Bsc_AnaOpt_Rec.Bsc_Option_Group2 := l_AO2;
1096 l_Bsc_AnaOpt_Rec.Bsc_Dim_Set_Id := p_DimSet_Id;
1097 l_Bsc_AnaOpt_Rec.Bsc_Option_Name := p_Option_Name;
1098 l_Bsc_AnaOpt_Rec.Bsc_Option_Help := p_Option_Help;
1099
1100 Check_Default_Props(
1101 p_commit => l_commit
1102 ,p_Anal_Opt_Rec => l_Bsc_AnaOpt_Rec
1103 ,p_cascade_shared => TRUE
1104 ,x_return_status => x_return_status
1105 ,x_msg_count => x_msg_count
1106 ,x_msg_data => x_msg_data
1107 );
1108 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1109 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1110 END IF;
1111
1112 IF l_indicator_type <> 10 AND p_DataSet_Id IS NOT NULL THEN
1113 BSC_KPI_SERIES_PUB.Save_Default_Calculation(
1114 p_commit => FND_API.G_FALSE
1115 ,p_Indicator => p_Indicator
1116 ,p_Analysis_Option0 => l_AO0
1117 ,p_Analysis_Option1 => l_AO1
1118 ,p_Analysis_Option2 => l_AO2
1119 ,p_Series_Id => 0
1120 ,p_default_calculation => p_default_calculation
1121 ,x_return_status => x_return_status
1122 ,x_msg_count => x_msg_count
1123 ,x_msg_data => x_msg_data
1124 );
1125 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1126 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1127 END IF;
1128 END IF;
1129
1130
1131 l_Measure_Source := bsc_Oaf_Views_Pvt.Get_Dataset_Source(x_Dataset_Id => p_DataSet_Id);
1132
1133 IF l_Measure_Source = 'PMF' THEN
1134 IF FND_API.To_Boolean(p_Create_Flow) THEN
1135 l_DimSet_Id := NULL;
1136 ELSE
1137 l_DimSet_Id := p_DimSet_Id;
1138 END IF;
1139 BSC_BIS_KPI_MEAS_PUB.Update_KPI_Analysis_Options -- This will cascade to the shared
1140 ( p_commit => l_commit
1141 , p_kpi_id => p_Indicator
1142 , p_data_source => l_Measure_Source
1143 , p_analysis_group_id => p_Analysis_Group_Id
1144 , p_analysis_option_id0 => p_Option_Id
1145 , p_analysis_option_id1 => p_Parent_Option_Id
1146 , p_analysis_option_id2 => p_GrandParent_Option_Id
1147 , p_series_id => 0
1148 , p_data_set_id => p_DataSet_Id
1149 , p_dim_set_id => l_DimSet_Id
1150 , p_option0_Name => p_Option_Name
1151 , p_option1_Name => NULL
1152 , p_option2_Name => NULL
1153 , p_measure_short_name => NULL
1154 , p_dim_obj_short_names => NULL
1155 , p_default_short_names => NULL
1156 , p_view_by_name => NULL
1157 , p_measure_name => p_Option_Name
1158 , p_measure_help => p_Option_Help
1159 , p_default_value => NULL
1160 , p_time_stamp => NULL
1161 , p_update_ana_opt => TRUE
1162 , x_return_status => x_return_status
1163 , x_msg_count => x_msg_count
1164 , x_msg_data => x_msg_data
1165 );
1166 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1167 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1168 END IF;
1169 ELSE --For BSC Measures
1170
1171 OPEN c_old_dim_set_id ;
1172 FETCH c_old_dim_set_id INTO l_old_DimSet_id;
1173 CLOSE c_old_dim_set_id;
1174
1175 OPEN c_old_data_set_id(l_AO0, l_AO1, l_AO2);
1176 FETCH c_old_data_set_id INTO l_old_data_set_id;
1177 CLOSE c_old_data_set_id;
1178
1179 Get_Parent_GrandParent_Ids(
1180 p_Indicator => p_Indicator
1181 ,p_Analysis_Group_Id => p_Analysis_Group_Id
1182 ,p_Parent_Id => l_Bsc_AnaOpt_Rec.Bsc_Parent_Option_Id
1183 ,p_GrandParent_Id => l_Bsc_AnaOpt_Rec.Bsc_Grandparent_Option_Id
1184 ,p_Independent_Par_Id => 0
1185 ,x_Parent_Id => l_temp_Parent_Id
1186 ,x_GrandParent_Id => l_temp_GrandParent_Id
1187 ,x_Parent_Group_Id => l_Parent_Group_Id
1188 ,x_GrandParent_Group_Id => l_GrandParent_Group_Id
1189 );
1190
1191
1192 l_Bsc_AnaOpt_Rec.Bsc_Parent_Option_Id := l_temp_Parent_Id;
1193 l_Bsc_AnaOpt_Rec.Bsc_Grandparent_Option_Id := l_temp_GrandParent_Id;
1194
1195 IF l_indicator_type <> 10 THEN
1196 -- For multibar dataset_id will not be updated at option level. They will
1197 --be updated at series level
1198 IF p_DataSet_Id IS NOT NULL THEN
1199 Bsc_Analysis_Option_Pvt.Update_Analysis_Measures (
1200 p_commit => l_commit
1201 ,p_Anal_Opt_Rec => l_Bsc_AnaOpt_Rec
1202 ,x_return_status => x_return_status
1203 ,x_msg_count => x_msg_count
1204 ,x_msg_data => x_Msg_Data
1205 );
1206 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1207 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1208 END IF;
1209 END IF;
1210 END IF;
1211 Bsc_Analysis_Option_Pvt.Update_Analysis_Options (
1212 p_commit => l_commit
1213 ,p_Anal_Opt_Rec => l_Bsc_AnaOpt_Rec
1214 ,p_data_source => l_Measure_Source
1215 ,x_return_status => x_return_status
1216 ,x_msg_count => x_msg_count
1217 ,x_msg_data => x_Msg_Data
1218 );
1219 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1220 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1221 END IF;
1222
1223
1224 FOR cd in c_shared_objs LOOP
1225 l_Bsc_AnaOpt_Rec.Bsc_Kpi_Id := cd.Indicator;
1226 IF l_indicator_type <> 10 THEN
1227 IF p_DataSet_Id IS NOT NULL THEN
1228 Bsc_Analysis_Option_Pvt.Update_Analysis_Measures (
1229 p_commit => l_commit
1230 ,p_Anal_Opt_Rec => l_Bsc_AnaOpt_Rec
1231 ,x_return_status => x_return_status
1232 ,x_msg_count => x_msg_count
1233 ,x_msg_data => x_Msg_Data
1234 );
1235 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1236 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1237 END IF;
1238 END IF;
1239 END IF;
1240 Bsc_Analysis_Option_Pvt.Update_Analysis_Options (
1241 p_commit => l_commit
1242 ,p_Anal_Opt_Rec => l_Bsc_AnaOpt_Rec
1243 ,p_data_source => l_Measure_Source
1244 ,x_return_status => x_return_status
1245 ,x_msg_count => x_msg_count
1246 ,x_msg_data => x_Msg_Data
1247 );
1248 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1249 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1250 END IF;
1251
1252 END LOOP;
1253
1254 IF FND_API.To_Boolean(p_Create_Flow) THEN
1255 BSC_DESIGNER_PVT.Deflt_Update_SN_FM_CM(x_indicator => p_Indicator);
1256 END IF;
1257
1258 IF ( Get_Analysis_Option_Default( p_Indicator => p_Indicator, p_Analysis_Group_Id => p_Analysis_Group_Id) = p_Option_Id AND
1259 FND_API.To_Boolean(Is_Analysis_Drill ( p_Indicator => p_Indicator, p_Analysis_Group_Id => p_Analysis_Group_Id))) THEN
1260 BSC_COMMON_DIM_LEVELS_PUB.Validate_List_Button (
1261 p_Kpi_Id => p_Indicator
1262 ,p_Dim_Level_Id => NULL
1263 ,x_return_status => x_return_status
1264 ,x_msg_count => x_msg_count
1265 ,x_msg_data => x_msg_data
1266 );
1267 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1268 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1269 END IF;
1270 END IF;
1271 END IF;
1272
1273 Update_Change_DimSet (
1274 p_commit => FND_API.G_FALSE
1275 ,p_Indicator => p_Indicator
1276 ,p_Analysis_Group_Id => p_Analysis_Group_Id
1277 ,p_Change_Dim_Set => p_Change_Dim_Set
1278 ,x_return_status => x_return_status
1279 ,x_msg_count => x_msg_count
1280 ,x_msg_data => x_msg_data
1281 );
1282 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1283 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1284 END IF;
1285
1286 Check_UserLevel_Values(
1287 p_commit => l_commit
1288 ,p_Indicator => p_Indicator
1289 ,p_cascade_shared => TRUE
1290 ,x_return_status => x_return_status
1291 ,x_msg_count => x_msg_count
1292 ,x_msg_data => x_msg_data
1293 );
1294 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1295 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1296 END IF;
1297
1298 Check_Strucural_Flag_Change (
1299 p_commit => l_commit
1300 ,p_Indicator => p_Indicator
1301 ,p_olddim_Dataset_map => l_olddim_Dataset_map
1302 ,p_cascade_shared => TRUE
1303 ,x_return_status => x_return_status
1304 ,x_msg_count => x_msg_count
1305 ,x_msg_data => x_msg_data
1306 );
1307 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1308 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1309 END IF;
1310
1311 -- Update TimeStamp
1312 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id := p_Indicator;
1313 BSC_KPI_PUB.Update_Kpi_Time_Stamp(
1314 p_commit => l_commit
1315 ,p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
1316 ,x_return_status => x_return_status
1317 ,x_msg_count => x_msg_count
1318 ,x_msg_data => x_msg_data
1319 );
1320 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1321 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1322 END IF;
1323 FOR cd IN c_shared_objs LOOP
1324 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id := cd.Indicator;
1325 Update_Change_DimSet (
1326 p_commit => FND_API.G_FALSE
1327 ,p_Indicator => cd.Indicator
1328 ,p_Analysis_Group_Id => p_Analysis_Group_Id
1329 ,p_Change_Dim_Set => p_Change_Dim_Set
1330 ,x_return_status => x_return_status
1331 ,x_msg_count => x_msg_count
1332 ,x_msg_data => x_msg_data
1333 );
1334 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1335 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1336 END IF;
1337
1338 BSC_KPI_PUB.Update_Kpi_Time_Stamp(
1339 p_commit => l_commit
1340 ,p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
1341 ,x_return_status => x_return_status
1342 ,x_msg_count => x_msg_count
1343 ,x_msg_data => x_msg_data
1344 );
1345 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1346 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1347 END IF;
1348 END LOOP;
1349
1350 BSC_DESIGNER_PVT.Deflt_Update_AOPTS ( x_indicator => p_Indicator);
1351 FOR cd in c_shared_objs LOOP
1352 BSC_DESIGNER_PVT.Deflt_Update_AOPTS ( x_indicator => cd.indicator );
1353 END LOOP;
1354
1355 IF FND_API.To_Boolean( p_commit ) THEN
1356 COMMIT;
1357 END IF;
1358
1359 EXCEPTION
1360 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1361 ROLLBACK TO Update_Analayis_Option_PUB;
1362 IF (x_msg_data IS NULL) THEN
1363 FND_MSG_PUB.Count_And_Get
1364 ( p_encoded => FND_API.G_FALSE
1365 , p_count => x_msg_count
1366 , p_data => x_msg_data
1367 );
1368 END IF;
1369 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1370 WHEN OTHERS THEN
1371 ROLLBACK TO Update_Analayis_Option_PUB;
1372 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1373 IF (x_msg_data IS NOT NULL) THEN
1374 x_msg_data := x_msg_data||' -> BSC_OBJ_ANALYSIS_OPTIONS_PUB.Update_Analysis_Option_UI ';
1375 ELSE
1376 x_msg_data := SQLERRM||' at BSC_OBJ_ANALYSIS_OPTIONS_PUB.Update_Analysis_Option_UI ';
1377 END IF;
1378 END Update_Analysis_Option_UI;
1379
1380 /************************************************************************************
1381 -- API name : Delete_Mind_Options
1382 -- Type : Private
1383 -- Deletes entries from bsc_kpi_analysis_options. This API also cascades the
1384 -- deletes the child analysis options.
1385 ************************************************************************************/
1386
1387 PROCEDURE Delete_Mind_Options(
1388 p_commit IN VARCHAR2 := FND_API.G_FALSE
1389 ,p_Anal_Opt_Rec IN Bsc_Analysis_Option_Pub.Bsc_Option_Rec_Type
1390 ,p_Dependency01 IN NUMBER
1391 ,p_Dependency12 IN NUMBER
1392 ,p_Initial_Group_Id IN NUMBER
1393 ,x_return_status OUT NOCOPY VARCHAR2
1394 ,x_msg_count OUT NOCOPY NUMBER
1395 ,x_msg_data OUT NOCOPY VARCHAR2
1396 ) IS
1397 l_Ana_Opt_Count NUMBER := 0;
1398 l_DeleteChildren BOOLEAN := FALSE;
1399 l_Deletegrandchildren BOOLEAN := FALSE;
1400 l_Next_Group_Id bsc_kpi_analysis_groups.analysis_group_id%TYPE;
1401 l_Grandchild_Group_Id bsc_kpi_analysis_groups.analysis_group_id%TYPE;
1402 l_criteria VARCHAR2(2000);
1403 l_sql VARCHAR2(2000);
1404
1405 l_Anal_Opt_Rec Bsc_Analysis_Option_Pub.Bsc_Option_Rec_Type;
1406 l_Par_Opt_Rec Bsc_Analysis_Option_Pub.Bsc_Option_Rec_Type;
1407 l_commit VARCHAR2(2) := FND_API.G_FALSE;
1408 CURSOR c_Ana_Opt_Count IS
1409 SELECT
1410 COUNT(1)
1411 FROM
1412 bsc_kpi_analysis_options_b
1413 WHERE indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id AND
1414 analysis_group_id = p_Anal_Opt_Rec.Bsc_Analysis_Group_Id;
1415
1416 CURSOR c_Child_Options IS
1417 SELECT
1418 option_id
1419 FROM
1420 bsc_kpi_analysis_options_b
1421 WHERE
1422 indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id AND
1423 analysis_group_id = 1 AND
1424 parent_option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id;
1425
1426 BEGIN
1427 SAVEPOINT Delete_Mind_Options_PVT;
1428 x_return_status := FND_API.G_RET_STS_SUCCESS;
1429 FND_MSG_PUB.Initialize;
1430
1431 l_Anal_Opt_Rec := p_Anal_Opt_Rec;
1432 l_Par_Opt_Rec := p_Anal_Opt_Rec;
1433 l_criteria := ' WHERE indicator = '||l_Anal_Opt_Rec.Bsc_Kpi_Id;
1434 l_criteria := l_criteria || ' AND analysis_group_id = '|| l_Anal_Opt_Rec.Bsc_Analysis_Group_Id;
1435 IF l_Anal_Opt_Rec.Bsc_Analysis_Option_Id <> -1 THEN
1436 l_criteria := l_criteria || ' AND option_id = '|| l_Anal_Opt_Rec.Bsc_Analysis_Option_Id;
1437 END IF;
1438
1439 IF l_Anal_Opt_Rec.Bsc_Parent_Option_Id <> -1 THEN
1440 l_criteria := l_criteria || ' AND parent_option_id = '|| l_Anal_Opt_Rec.Bsc_Parent_Option_Id;
1441 IF l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id <> -1 THEN
1442 l_criteria := l_criteria || ' AND grandparent_option_id = '|| l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id;
1443 END IF;
1444 END IF;
1445
1446 l_sql := 'DELETE FROM bsc_kpi_analysis_options_b ' || l_criteria;
1447 BSC_APPS.Execute_Immediate(l_sql);
1448
1449 l_sql := 'DELETE FROM bsc_kpi_analysis_options_tl ' || l_criteria;
1450 BSC_APPS.Execute_Immediate(l_sql);
1451
1452 OPEN c_Ana_Opt_Count;
1453 FETCH c_Ana_Opt_Count INTO l_Ana_Opt_Count;
1454 CLOSE c_Ana_Opt_Count;
1455
1456
1457 /*Delete the child analysis options recursively if the following conditions satisty
1458 1. If the current analysis group has a dependent relationship with the child group
1459 then delete the children
1460 2. If the current analysis option is the last analysis option in that particular
1461 group , the children should be deleted even if it is an independent relationship*/
1462 IF (l_Anal_Opt_Rec.Bsc_Analysis_Group_Id = 0 AND (p_Dependency01 = 1 OR l_Ana_Opt_Count = 0) ) THEN
1463 l_Next_Group_Id := 1;
1464 l_DeleteChildren := TRUE;
1465 IF ( p_Dependency01 = 1 AND p_Dependency12 = 1) THEN
1466 l_GrandChild_Group_Id := 2;
1467 l_DeleteGrandChildren := TRUE;
1468 END IF;
1469
1470 END IF;
1471
1472 IF (l_Anal_Opt_Rec.Bsc_Analysis_Group_Id = 1 AND ((p_Dependency12 = 1 AND p_Initial_Group_Id = 1) OR l_Ana_Opt_Count = 0) ) THEN
1473 l_Next_Group_Id := 2;
1474 l_DeleteChildren := TRUE;
1475 END IF;
1476
1477 IF l_DeleteGrandChildren THEN
1478 FOR cd in c_Child_Options LOOP
1479 l_Par_Opt_Rec.Bsc_Analysis_Group_Id := l_GrandChild_Group_Id;
1480 l_Par_Opt_Rec.Bsc_Grandparent_Option_Id := l_Anal_Opt_Rec.Bsc_Analysis_Option_Id;
1481 l_Par_Opt_Rec.Bsc_Parent_Option_Id := cd.Option_Id;
1482 l_Par_Opt_Rec.Bsc_Analysis_Option_Id := -1;
1483
1484 Delete_Mind_Options (
1485 p_commit => l_commit
1486 ,p_Anal_Opt_Rec => l_Par_Opt_Rec
1487 ,p_Dependency01 => p_Dependency01
1488 ,p_Dependency12 => p_Dependency12
1489 ,p_Initial_Group_Id => p_Initial_Group_Id
1490 ,x_return_status => x_return_status
1491 ,x_msg_count => x_msg_count
1492 ,x_msg_data => x_msg_data
1493 );
1494 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1495 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1496 END IF;
1497 END LOOP;
1498 END IF;
1499
1500 IF l_DeleteChildren = TRUE THEN
1501 l_Anal_Opt_Rec.Bsc_Analysis_Group_Id := l_Next_Group_Id;
1502 l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id := l_Anal_Opt_Rec.Bsc_Parent_Option_Id;
1503 l_Anal_Opt_Rec.Bsc_Parent_Option_Id := l_Anal_Opt_Rec.Bsc_Analysis_Option_Id;
1504 l_Anal_Opt_Rec.Bsc_Analysis_Option_Id := -1;
1505
1506 Delete_Mind_Options (
1507 p_commit => l_commit
1508 ,p_Anal_Opt_Rec => l_Anal_Opt_Rec
1509 ,p_Dependency01 => p_Dependency01
1510 ,p_Dependency12 => p_Dependency12
1511 ,p_Initial_Group_Id => p_Initial_Group_Id
1512 ,x_return_status => x_return_status
1513 ,x_msg_count => x_msg_count
1514 ,x_msg_data => x_msg_data
1515 );
1516 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1517 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1518 END IF;
1519 END IF;
1520
1521 IF FND_API.To_Boolean( p_commit ) THEN
1522 COMMIT;
1523 END IF;
1524 EXCEPTION
1525 WHEN OTHERS THEN
1526 ROLLBACK TO Delete_Mind_Options_PVT;
1527 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1528 IF (x_msg_data IS NOT NULL) THEN
1529 x_msg_data := x_msg_data||' ->BSC_OBJ_ANALYSIS_OPTIONS_PUB.Delete_Mind_Options ';
1530 ELSE
1531 x_msg_data := SQLERRM||' at BSC_OBJ_ANALYSIS_OPTIONS_PUB.Delete_Mind_Options ';
1532 END IF;
1533 END Delete_Mind_Options;
1534
1535 /************************************************************************************
1536 -- API name : Delete_Mind_Data
1537 -- Type : Private
1538 ************************************************************************************/
1539
1540
1541 PROCEDURE Delete_Mind_Data(
1542 p_commit IN VARCHAR2 := FND_API.G_FALSE
1543 ,p_Anal_Opt_Rec IN Bsc_Analysis_Option_Pub.Bsc_Option_Rec_Type
1544 ,p_Parent_Group_Id IN NUMBER
1545 ,p_Grandparent_Group_Id IN NUMBER
1546 ,x_return_status OUT NOCOPY VARCHAR2
1547 ,x_msg_count OUT NOCOPY NUMBER
1548 ,x_msg_data OUT NOCOPY VARCHAR2
1549 ) IS
1550
1551 TYPE cursor_type IS REF CURSOR;
1552 c_NumOptions cursor_type;
1553 l_NumOptions NUMBER := 0;
1554 l_criteria VARCHAR2(2000);
1555 l_sql VARCHAR2(2000);
1556 l_Anal_Opt_Rec Bsc_Analysis_Option_Pub.Bsc_Option_Rec_Type;
1557 l_commit VARCHAR2(2) := FND_API.G_FALSE;
1558 --l_Initial_Kpi_Meas FND_TABLE_OF_NUMBER;
1559 BEGIN
1560
1561 SAVEPOINT Delete_Mind_Data_PVT;
1562 x_return_status := FND_API.G_RET_STS_SUCCESS;
1563 FND_MSG_PUB.Initialize;
1564
1565 l_Anal_Opt_Rec := p_Anal_Opt_Rec;
1566
1567
1568 l_sql := 'SELECT COUNT(1) FROM bsc_kpi_analysis_options_b WHERE indicator = ' || l_Anal_Opt_Rec.Bsc_Kpi_Id ;
1569 l_sql := l_sql || ' AND analysis_group_id = ' || l_Anal_Opt_Rec.Bsc_Analysis_Group_Id;
1570
1571 IF p_Parent_Group_Id <> -1 THEN
1572 l_sql := l_sql || ' AND parent_option_id = '|| l_Anal_Opt_Rec.Bsc_Parent_Option_Id;
1573 IF p_Grandparent_Group_Id <> -1 THEN
1574 l_sql := l_sql || ' AND grandparent_option_id= ' || l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id;
1575 END IF;
1576 END IF;
1577
1578
1579 OPEN c_NumOptions FOR l_sql;
1580 FETCH c_NumOptions INTO l_NumOptions;
1581 CLOSE c_NumOptions;
1582
1583 IF l_NumOptions < 1 THEN
1584 RETURN ;
1585 END IF;
1586
1587
1588 l_criteria := ' WHERE indicator = '|| l_Anal_Opt_Rec.Bsc_Kpi_Id;
1589 IF l_Anal_Opt_Rec.Bsc_Analysis_Option_Id <> -1 THEN
1590 l_criteria := l_criteria || ' AND analysis_option'|| l_Anal_Opt_Rec.Bsc_Analysis_Group_Id || ' = '|| l_Anal_Opt_Rec.Bsc_Analysis_Option_Id;
1591 END IF;
1592 IF p_Parent_Group_Id <> -1 THEN
1593 l_criteria := l_criteria || ' AND analysis_option'|| p_Parent_Group_Id || ' = '|| l_Anal_Opt_Rec.Bsc_Parent_Option_Id;
1594 IF p_Grandparent_Group_Id <> -1 THEN
1595 l_criteria := l_criteria || ' AND analysis_option'|| p_Grandparent_Group_Id || ' = '|| l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id;
1596 END IF;
1597 END IF;
1598
1599 l_sql := 'DELETE FROM bsc_kpi_analysis_measures_b ' || l_criteria;
1600 BSC_APPS.Execute_Immediate(l_sql);
1601
1602 l_sql := 'DELETE FROM bsc_kpi_analysis_measures_tl ' || l_criteria;
1603 BSC_APPS.Execute_Immediate(l_sql);
1604
1605 BSC_ANALYSIS_OPTION_PUB.Cascade_Deletion_Color_Props (
1606 p_commit => p_commit
1607 ,p_Anal_Opt_Rec => p_Anal_Opt_Rec
1608 ,x_return_status => x_return_status
1609 ,x_msg_count => x_msg_count
1610 ,x_msg_data => x_msg_data
1611 ) ;
1612
1613 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1614 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1615 END IF;
1616
1617 l_sql := 'DELETE FROM bsc_kpi_subtitles_tl ' || l_criteria;
1618 BSC_APPS.Execute_Immediate(l_sql);
1619
1620 IF FND_API.To_Boolean( p_commit ) THEN
1621 COMMIT;
1622 END IF;
1623 EXCEPTION
1624 WHEN OTHERS THEN
1625 ROLLBACK TO Delete_Mind_Data_PVT;
1626 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1627 IF (x_msg_data IS NOT NULL) THEN
1628 x_msg_data := x_msg_data||' -> BSC_OBJ_ANALYSIS_OPTIONS_PUB.Delete_Mind_Data ';
1629 ELSE
1630 x_msg_data := SQLERRM||' at BSC_OBJ_ANALYSIS_OPTIONS_PUB.Delete_Mind_Data ';
1631 END IF;
1632 END Delete_Mind_Data;
1633
1634 /************************************************************************************
1635 -- API name : Renumerate_Options
1636 -- Type : Private
1637 -- If an analysis option is deleted then the other analysis options must be
1638 -- resequenced depending on the position of the analysis option
1639 -- Also the analysis measures using the resequenced analysis options have
1640 -- to be updated.
1641 ************************************************************************************/
1642
1643
1644 PROCEDURE Renumerate_Options(
1645 p_commit IN VARCHAR2 := FND_API.G_FALSE
1646 ,p_Anal_Opt_Rec IN Bsc_Analysis_Option_Pub.Bsc_Option_Rec_Type
1647 ,p_Parent_Group_Id IN NUMBER
1648 ,p_Grandparent_Group_Id IN NUMBER
1649 ,x_return_status OUT NOCOPY VARCHAR2
1650 ,x_msg_count OUT NOCOPY NUMBER
1651 ,x_msg_data OUT NOCOPY VARCHAR2
1652 ) IS
1653 l_criteria_grandparent VARCHAR2(2000);
1654 l_criteria_measures VARCHAR2(2000);
1655 l_criteria_options VARCHAR2(2000);
1656 l_criteria_parent VARCHAR2(2000);
1657
1658 l_cur_index NUMBER := 0;
1659 l_max_groups NUMBER := 0;
1660 l_option_id bsc_kpi_analysis_options_b.option_id%TYPE;
1661 l_options_sql VARCHAR2(2000);
1662 l_sql VARCHAR2(2000);
1663
1664 TYPE CursorType IS REF CURSOR;
1665 c_option CursorType;
1666
1667 CURSOR c_Max_Groups IS
1668 SELECT
1669 MAX(analysis_group_id)
1670 FROM
1671 bsc_kpi_analysis_groups
1672 WHERE
1673 indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id;
1674
1675 BEGIN
1676 SAVEPOINT Renumerate_Options_PVT;
1677 x_return_status := FND_API.G_RET_STS_SUCCESS;
1678 l_criteria_options := ' WHERE indicator = '|| p_Anal_Opt_Rec.Bsc_Kpi_Id;
1679 l_criteria_options := l_criteria_options || ' AND analysis_group_id = ' || p_Anal_Opt_Rec.Bsc_Analysis_Group_Id;
1680 IF p_Parent_Group_Id <> -1 THEN
1681 l_criteria_options := l_criteria_options || ' AND parent_option_id=' || p_Anal_Opt_Rec.Bsc_Parent_Option_Id;
1682 IF p_Grandparent_Group_Id <> -1 THEN
1683 l_criteria_options := l_criteria_options || ' AND grandparent_option_id='|| p_Anal_Opt_Rec.Bsc_Grandparent_Option_Id;
1684 END IF;
1685 END IF;
1686
1687 l_options_sql := 'SELECT option_id FROM bsc_kpi_analysis_options_b ';
1688 l_options_sql := l_options_sql || l_criteria_options || ' ORDER BY option_id';
1689
1690 OPEN c_option FOR l_options_sql;
1691 LOOP
1692 FETCH c_option INTO l_option_id;
1693 EXIT WHEN c_option%NOTFOUND;
1694 l_criteria_measures := ' WHERE indicator = '|| p_Anal_Opt_Rec.Bsc_Kpi_Id ;
1695 l_criteria_measures := l_criteria_measures ||' AND analysis_option' || p_Anal_Opt_Rec.Bsc_Analysis_Group_Id || ' = ' || l_option_id;
1696
1697 IF p_Parent_Group_Id <> -1 THEN
1698 l_criteria_measures := l_criteria_measures || ' AND analysis_option' || p_Parent_Group_Id || ' = ' || p_Anal_Opt_Rec.Bsc_Parent_Option_Id;
1699 IF p_Grandparent_Group_Id <> -1 THEN
1700 l_criteria_measures := l_criteria_measures || ' AND analysis_option' || p_Grandparent_Group_Id || ' = ' || p_Anal_Opt_Rec.Bsc_Grandparent_Option_Id;
1701 END IF;
1702 END IF;
1703
1704 IF l_option_id <> l_cur_index THEN
1705 l_sql := 'UPDATE bsc_kpi_analysis_measures_b SET analysis_option'|| p_Anal_Opt_Rec.Bsc_Analysis_Group_Id || '=' || l_cur_index;
1706 l_sql := l_sql || l_criteria_measures;
1707 BSC_APPS.Execute_Immediate(l_sql);
1708
1709 l_sql := 'UPDATE bsc_kpi_analysis_measures_tl SET analysis_option'|| p_Anal_Opt_Rec.Bsc_Analysis_Group_Id || '=' || l_cur_index;
1710 l_sql := l_sql || l_criteria_measures;
1711 BSC_APPS.Execute_Immediate(l_sql);
1712
1713 l_sql := 'UPDATE bsc_kpi_subtitles_tl SET analysis_option'|| p_Anal_Opt_Rec.Bsc_Analysis_Group_Id || '=' || l_cur_index;
1714 l_sql := l_sql || l_criteria_measures;
1715 BSC_APPS.Execute_Immediate(l_sql);
1716
1717 l_sql := 'UPDATE bsc_kpi_analysis_options_b SET option_id='|| l_cur_index;
1718 l_sql := l_sql || l_criteria_options || ' AND option_id = ' || l_option_id;
1719 BSC_APPS.Execute_Immediate(l_sql);
1720
1721 l_sql := 'UPDATE bsc_kpi_analysis_options_tl SET option_id='|| l_cur_index;
1722 l_sql := l_sql || l_criteria_options || ' AND option_id = ' || l_option_id;
1723 BSC_APPS.Execute_Immediate(l_sql);
1724
1725 OPEN c_Max_Groups;
1726 FETCH c_Max_Groups INTO l_max_groups;
1727 CLOSE c_Max_Groups;
1728
1729 IF (p_Anal_Opt_Rec.Bsc_Analysis_Group_Id + 1 <= l_max_groups) THEN
1730
1731 l_criteria_parent := ' WHERE indicator = '|| p_Anal_Opt_Rec.Bsc_Kpi_Id;
1732 l_criteria_parent := l_criteria_parent || ' AND analysis_group_id = ' || (p_Anal_Opt_Rec.Bsc_Analysis_Group_Id + 1) ||' AND parent_option_id = ' || l_option_id;
1733
1734 l_sql := 'UPDATE bsc_kpi_analysis_options_b SET parent_option_id = '|| l_cur_index ;
1735 l_sql := l_sql || l_criteria_parent;
1736 BSC_APPS.Execute_Immediate(l_sql);
1737
1738 l_sql := 'UPDATE bsc_kpi_analysis_options_tl SET parent_option_id = '|| l_cur_index ;
1739 l_sql := l_sql || l_criteria_parent;
1740 BSC_APPS.Execute_Immediate(l_sql);
1741
1742 END IF;
1743
1744 IF (p_Anal_Opt_Rec.Bsc_Analysis_Group_Id + 2 <= l_max_groups) THEN
1745
1746 l_criteria_parent := ' WHERE indicator = '|| p_Anal_Opt_Rec.Bsc_Kpi_Id;
1747 l_criteria_parent := l_criteria_parent || ' AND analysis_group_id = ' || (p_Anal_Opt_Rec.Bsc_Analysis_Group_Id + 1) ||' AND grandparent_option_id = ' || l_option_id;
1748
1749 l_sql := 'UPDATE bsc_kpi_analysis_options_b SET grandparent_option_id = '|| l_cur_index ;
1750 l_sql := l_sql || l_criteria_parent;
1751 BSC_APPS.Execute_Immediate(l_sql);
1752
1753 l_sql := 'UPDATE bsc_kpi_analysis_options_tl SET grandparent_option_id = '|| l_cur_index ;
1754 l_sql := l_sql || l_criteria_parent;
1755 BSC_APPS.Execute_Immediate(l_sql);
1756 END IF;
1757 END IF;
1758 l_cur_index := l_cur_index + 1;
1759 end loop;
1760 close c_option;
1761
1762 IF FND_API.To_Boolean( p_commit ) THEN
1763 COMMIT;
1764 END IF;
1765
1766 EXCEPTION
1767 WHEN OTHERS THEN
1768 ROLLBACK TO Renumerate_Options_PVT;
1769 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1770 IF (x_msg_data IS NOT NULL) THEN
1771 x_msg_data := x_msg_data||' -> BSC_OBJ_ANALYSIS_OPTIONS_PUB.Renumerate_Options ';
1772 ELSE
1773 x_msg_data := SQLERRM||' at BSC_OBJ_ANALYSIS_OPTIONS_PUB.Renumerate_Options ';
1774 END IF;
1775 END Renumerate_Options;
1776
1777 /************************************************************************************
1778 -- API name : Update_Analysis_Opt_Count
1779 -- Type : Private
1780 ************************************************************************************/
1781
1782 PROCEDURE Update_Analysis_Opt_Count (
1783 p_commit IN VARCHAR2 := FND_API.G_FALSE
1784 ,p_Indicator IN NUMBER
1785 ,x_return_status OUT NOCOPY VARCHAR2
1786 ,x_msg_count OUT NOCOPY NUMBER
1787 ,x_msg_data OUT NOCOPY VARCHAR2
1788 ) IS
1789
1790 l_Count NUMBER := 0;
1791 l_Max_Grp_Id NUMBER := -1;
1792
1793 CURSOR c_ana_opt_cnt(p_Analysis_Group_Id NUMBER) IS
1794 SELECT MAX(option_id)
1795 FROM
1796 bsc_kpi_analysis_options_b
1797 WHERE
1798 indicator = p_Indicator AND
1799 analysis_group_id = p_Analysis_Group_Id;
1800
1801 CURSOR c_max_grp_id IS
1802 SELECT max(analysis_group_id)
1803 FROM
1804 bsc_kpi_analysis_groups
1805 WHERE
1806 indicator = p_Indicator;
1807
1808 BEGIN
1809
1810 SAVEPOINT Update_Ana_Opt_Count_PVT;
1811 x_return_status := FND_API.G_RET_STS_SUCCESS;
1812 FND_MSG_PUB.Initialize;
1813
1814 OPEN c_max_grp_id;
1815 FETCH c_max_grp_id INTO l_Max_Grp_Id;
1816 CLOSE c_max_grp_id;
1817
1818 FOR i in 0..l_Max_Grp_Id LOOP
1819 OPEN c_ana_opt_cnt(i);
1820 FETCH c_ana_opt_cnt INTO l_Count;
1821 CLOSE c_ana_opt_cnt;
1822
1823 IF l_Count IS NULL THEN
1824 l_Count := 0;
1825 ELSE
1826 l_Count := l_Count + 1;
1827 END IF;
1828
1829 UPDATE
1830 bsc_kpi_analysis_groups
1831 SET
1832 num_of_options = l_Count
1833 WHERE
1834 indicator = p_Indicator
1835 AND analysis_group_id = i;
1836
1837 END LOOP;
1838
1839 IF FND_API.To_Boolean( p_commit ) THEN
1840 COMMIT;
1841 END IF;
1842 EXCEPTION
1843 WHEN OTHERS THEN
1844 ROLLBACK TO Update_Ana_Opt_Count_PVT;
1845 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1846 IF (x_msg_data IS NOT NULL) THEN
1847 x_msg_data := x_msg_data||' -> BSC_OBJ_ANALYSIS_OPTIONS_PUB.Update_Analysis_Opt_Count ';
1848 ELSE
1849 x_msg_data := SQLERRM||' at BSC_OBJ_ANALYSIS_OPTIONS_PUB.Update_Analysis_Opt_Count ';
1850 END IF;
1851 END Update_Analysis_Opt_Count;
1852
1853 /************************************************************************************
1854 -- API name : Get_Dependency
1855 -- Type : Private
1856 ************************************************************************************/
1857 FUNCTION Get_Dependency (
1858 p_Indicator IN NUMBER
1859 ,p_Analysis_Group_Id IN NUMBER
1860 ) RETURN NUMBER IS
1861
1862 CURSOR c_Is_Dependent IS
1863 SELECT
1864 dependency_flag
1865 FROM
1866 bsc_kpi_analysis_groups
1867 WHERE
1868 indicator = p_Indicator AND
1869 analysis_group_id = p_Analysis_Group_Id;
1870
1871 l_Dependent bsc_kpi_analysis_groups.dependency_flag%TYPE := 0;
1872 BEGIN
1873
1874 OPEN c_Is_Dependent;
1875 FETCH c_Is_Dependent INTO l_Dependent;
1876 CLOSE c_Is_Dependent;
1877
1878 RETURN l_Dependent;
1879 EXCEPTION
1880 WHEN OTHERS THEN
1881 RETURN l_Dependent;
1882 END Get_Dependency;
1883
1884 /************************************************************************************
1885 -- API name : Delete_Analysis_Option_Wrap
1886 -- Type : Private
1887 ************************************************************************************/
1888
1889
1890 PROCEDURE Delete_Analysis_Option_Wrap (
1891 p_commit IN VARCHAR2 := FND_API.G_FALSE
1892 ,p_Anal_Opt_Rec IN Bsc_Analysis_Option_Pub.Bsc_Option_Rec_Type
1893 ,p_cascade_shared BOOLEAN := FALSE
1894 ,x_return_status OUT NOCOPY VARCHAR2
1895 ,x_msg_count OUT NOCOPY NUMBER
1896 ,x_msg_data OUT NOCOPY VARCHAR2
1897 ) IS
1898
1899 l_Dependency01 bsc_kpi_analysis_groups.dependency_flag%TYPE := -1;
1900 l_Dependency12 bsc_kpi_analysis_groups.dependency_flag%TYPE := -1;
1901
1902 l_Parent_Id bsc_kpi_analysis_options_b.parent_option_id%TYPE := 0;
1903 l_GrandParent_Id bsc_kpi_analysis_options_b.grandparent_option_id%TYPE := 0;
1904 l_Parent_Group_Id bsc_kpi_analysis_groups.analysis_group_id%TYPE := 0;
1905 l_GrandParent_Group_Id bsc_kpi_analysis_groups.analysis_group_id%TYPE := 0;
1906
1907 l_Bsc_Anal_Opt_Rec Bsc_Analysis_Option_Pub.Bsc_Option_Rec_Type;
1908
1909 l_commit VARCHAR2(2) := FND_API.G_FALSE;
1910
1911 CURSOR c_shared_objs IS
1912 SELECT
1913 indicator
1914 FROM
1915 bsc_kpis_b
1916 WHERE
1917 source_indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id AND
1918 prototype_flag <> 2 AND
1919 share_flag = 2;
1920
1921 BEGIN
1922 SAVEPOINT BscObjDeleteAnaOptWrap;
1923 x_return_status := FND_API.G_RET_STS_SUCCESS;
1924 FND_MSG_PUB.Initialize;
1925
1926 l_Bsc_Anal_Opt_Rec := p_Anal_Opt_Rec;
1927 l_Dependency01 := Get_Dependency(p_Anal_Opt_Rec.Bsc_Kpi_Id, 1);
1928 l_Dependency12 := Get_Dependency(p_Anal_Opt_Rec.Bsc_Kpi_Id, 2);
1929
1930 Get_Parent_GrandParent_Ids(
1931 p_Indicator => p_Anal_Opt_Rec.Bsc_Kpi_Id
1932 ,p_Analysis_Group_Id => p_Anal_Opt_Rec.Bsc_Analysis_Group_Id
1933 ,p_Parent_Id => p_Anal_Opt_Rec.Bsc_Parent_Option_Id
1934 ,p_GrandParent_Id => p_Anal_Opt_Rec.Bsc_Grandparent_Option_Id
1935 ,p_Independent_Par_Id => -1
1936 ,x_Parent_Id => l_Parent_Id
1937 ,x_GrandParent_Id => l_GrandParent_Id
1938 ,x_Parent_Group_Id => l_Parent_Group_Id
1939 ,x_GrandParent_Group_Id => l_GrandParent_Group_Id
1940 );
1941
1942 l_Bsc_Anal_Opt_Rec.Bsc_Parent_Option_Id := l_Parent_Id;
1943 l_Bsc_Anal_Opt_Rec.Bsc_Grandparent_Option_Id := l_GrandParent_Id;
1944
1945 Delete_Mind_Options (
1946 p_commit => l_commit
1947 ,p_Anal_Opt_Rec => l_Bsc_Anal_Opt_Rec
1948 ,p_Dependency01 => l_Dependency01
1949 ,p_Dependency12 => l_Dependency12
1950 ,p_Initial_Group_Id => p_Anal_Opt_Rec.Bsc_Analysis_Group_Id
1951 ,x_return_status => x_return_status
1952 ,x_msg_count => x_msg_count
1953 ,x_msg_data => x_msg_data
1954 );
1955 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1956 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1957 END IF;
1958
1959 Delete_Mind_Data (
1960 p_commit => l_commit
1961 ,p_Anal_Opt_Rec => l_Bsc_Anal_Opt_Rec
1962 ,p_Parent_Group_Id => l_Parent_Group_Id
1963 ,p_Grandparent_Group_Id => l_GrandParent_Group_Id
1964 ,x_return_status => x_return_status
1965 ,x_msg_count => x_msg_count
1966 ,x_msg_data => x_msg_data
1967 );
1968 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1969 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1970 END IF;
1971
1972 Renumerate_Options (
1973 p_commit => l_commit
1974 ,p_Anal_Opt_Rec => l_Bsc_Anal_Opt_Rec
1975 ,p_Parent_Group_Id => l_Parent_Group_Id
1976 ,p_Grandparent_Group_Id => l_GrandParent_Group_Id
1977 ,x_return_status => x_return_status
1978 ,x_msg_count => x_msg_count
1979 ,x_msg_data => x_msg_data
1980 );
1981 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1982 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1983 END IF;
1984
1985 Update_Analysis_Opt_Count (
1986 p_commit => l_commit
1987 ,p_Indicator => p_Anal_Opt_Rec.Bsc_Kpi_Id
1988 ,x_return_status => x_return_status
1989 ,x_msg_count => x_msg_count
1990 ,x_msg_data => x_msg_data
1991 );
1992 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1993 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1994 END IF;
1995
1996 IF p_cascade_shared THEN
1997 FOR cd in c_shared_objs LOOP
1998 l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id := cd.Indicator;
1999 Delete_Mind_Options (
2000 p_commit => l_commit
2001 ,p_Anal_Opt_Rec => l_Bsc_Anal_Opt_Rec
2002 ,p_Dependency01 => l_Dependency01
2003 ,p_Dependency12 => l_Dependency12
2004 ,p_Initial_Group_Id => p_Anal_Opt_Rec.Bsc_Analysis_Group_Id
2005 ,x_return_status => x_return_status
2006 ,x_msg_count => x_msg_count
2007 ,x_msg_data => x_msg_data
2008 );
2009 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2010 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2011 END IF;
2012
2013 Delete_Mind_Data (
2014 p_commit => l_commit
2015 ,p_Anal_Opt_Rec => l_Bsc_Anal_Opt_Rec
2016 ,p_Parent_Group_Id => l_Parent_Group_Id
2017 ,p_Grandparent_Group_Id => l_GrandParent_Group_Id
2018 ,x_return_status => x_return_status
2019 ,x_msg_count => x_msg_count
2020 ,x_msg_data => x_msg_data
2021 );
2022 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2023 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2024 END IF;
2025
2026 Renumerate_Options (
2027 p_commit => l_commit
2028 ,p_Anal_Opt_Rec => l_Bsc_Anal_Opt_Rec
2029 ,p_Parent_Group_Id => l_Parent_Group_Id
2030 ,p_Grandparent_Group_Id => l_GrandParent_Group_Id
2031 ,x_return_status => x_return_status
2032 ,x_msg_count => x_msg_count
2033 ,x_msg_data => x_msg_data
2034 );
2035 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2036 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2037 END IF;
2038
2039 Update_Analysis_Opt_Count (
2040 p_commit => l_commit
2041 ,p_Indicator => l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
2042 ,x_return_status => x_return_status
2043 ,x_msg_count => x_msg_count
2044 ,x_msg_data => x_msg_data
2045 );
2046 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2047 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2048 END IF;
2049 END LOOP;
2050 END IF;
2051
2052 IF FND_API.To_Boolean( p_commit ) THEN
2053 COMMIT;
2054 END IF;
2055
2056 EXCEPTION
2057 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2058 ROLLBACK TO BscObjDeleteAnaOptWrap;
2059 IF (x_msg_data IS NULL) THEN
2060 FND_MSG_PUB.Count_And_Get
2061 ( p_encoded => FND_API.G_FALSE
2062 , p_count => x_msg_count
2063 , p_data => x_msg_data
2064 );
2065 END IF;
2066 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2067 WHEN OTHERS THEN
2068 ROLLBACK TO BscObjDeleteAnaOptWrap;
2069 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2070 IF (x_msg_data IS NOT NULL) THEN
2071 x_msg_data := x_msg_data||' -> BSC_OBJ_ANALYSIS_OPTIONS_PUB.Delete_Analysis_Option_Wrap ';
2072 ELSE
2073 x_msg_data := SQLERRM||' at BSC_OBJ_ANALYSIS_OPTIONS_PUB.Delete_Analysis_Option_Wrap ';
2074 END IF;
2075 END Delete_Analysis_Option_Wrap;
2076
2077 /************************************************************************************
2078 -- API name : Reset_Group_Defaults
2079 -- Type : Public
2080 ************************************************************************************/
2081 PROCEDURE Reset_Group_Defaults (
2082 p_commit IN VARCHAR2 := FND_API.G_FALSE
2083 ,p_Indicator IN NUMBER
2084 ,p_Analysis_Group_Id IN NUMBER
2085 ,p_Option_Id IN NUMBER
2086 ,x_return_status OUT NOCOPY VARCHAR2
2087 ,x_msg_count OUT NOCOPY NUMBER
2088 ,x_msg_data OUT NOCOPY VARCHAR2
2089 ) IS
2090
2091 l_Reset_Child_Defaults BOOLEAN := FALSE;
2092 l_new_Default bsc_kpi_analysis_groups.default_value%TYPE := 0;
2093 l_Dependency01 bsc_kpi_analysis_groups.dependency_flag%TYPE := -1;
2094 l_Dependency12 bsc_kpi_analysis_groups.dependency_flag%TYPE := -1;
2095
2096 BEGIN
2097 SAVEPOINT Reset_Group_Defaults_PVT;
2098 x_return_status := FND_API.G_RET_STS_SUCCESS;
2099 FND_MSG_PUB.Initialize;
2100
2101 l_Dependency01 := Get_Dependency(p_Indicator,1);
2102 l_Dependency12 := Get_Dependency(p_Indicator,2);
2103
2104 l_new_Default := Get_Analysis_Option_Default(p_Indicator, p_Analysis_Group_Id);
2105
2106 IF l_new_Default >= p_Option_Id THEN
2107 IF l_new_Default = p_Option_Id THEN
2108 l_Reset_Child_Defaults := TRUE;
2109 l_new_Default := 0;
2110 ELSE
2111 l_new_Default := l_new_Default - 1;
2112 IF l_new_Default < 0 THEN
2113 l_new_Default := 0;
2114 END IF;
2115 END IF;
2116 UPDATE
2117 bsc_kpi_analysis_groups
2118 SET
2119 default_value = l_new_Default
2120 WHERE
2121 indicator = p_Indicator
2122 AND analysis_Group_Id = p_Analysis_Group_Id;
2123 IF l_Reset_Child_Defaults = TRUE THEN
2124 CASE p_Analysis_Group_Id
2125 WHEN 0 THEN
2126 IF l_Dependency01 = 1 THEN
2127 UPDATE
2128 bsc_kpi_analysis_groups
2129 SET
2130 default_value = 0
2131 WHERE
2132 indicator = p_Indicator
2133 AND analysis_Group_Id = 1;
2134 END IF;
2135 IF l_Dependency12 = 1 THEN
2136 UPDATE
2137 bsc_kpi_analysis_groups
2138 SET
2139 default_value = 0
2140 WHERE
2141 indicator = p_Indicator
2142 AND analysis_Group_Id = 2;
2143 END IF;
2144 WHEN 1 THEN
2145 IF l_Dependency12 = 1 THEN
2146 UPDATE
2147 bsc_kpi_analysis_groups
2148 SET
2149 default_value = 0
2150 WHERE
2151 indicator = p_Indicator
2152 AND analysis_Group_Id = 2;
2153 END IF;
2154 WHEN 2 THEN
2155 NULL;
2156 END CASE;
2157 END IF;
2158 END IF;
2159
2160 IF FND_API.To_Boolean( p_commit ) THEN
2161 COMMIT;
2162 END IF;
2163
2164 EXCEPTION
2165 WHEN OTHERS THEN
2166 ROLLBACK TO Reset_Group_Defaults_PVT;
2167 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2168 IF (x_msg_data IS NOT NULL) THEN
2169 x_msg_data := x_msg_data||' -> BSC_OBJ_ANALYSIS_OPTIONS_PUB.Reset_Group_Defaults ';
2170 ELSE
2171 x_msg_data := SQLERRM||' at BSC_OBJ_ANALYSIS_OPTIONS_PUB.Reset_Group_Defaults ';
2172 END IF;
2173 END Reset_Group_Defaults;
2174
2175 /************************************************************************************
2176 -- API name : Remove_Empty_Groups
2177 -- Type : Public
2178 ************************************************************************************/
2179 PROCEDURE Remove_Empty_Groups (
2180 p_commit IN VARCHAR2 := FND_API.G_FALSE
2181 ,p_Indicator IN NUMBER
2182 ,p_Analysis_Group_Id IN NUMBER
2183 ,p_Option_Id IN NUMBER := 0
2184 ,p_Parent_Option_Id IN NUMBER := 0
2185 ,p_Initial_Options IN NUMBER
2186 ,x_return_status OUT NOCOPY VARCHAR2
2187 ,x_msg_count OUT NOCOPY NUMBER
2188 ,x_msg_data OUT NOCOPY VARCHAR2
2189 ) IS
2190
2191 l_Max_Groups NUMBER := 0;
2192 l_Cur_Group NUMBER := 0;
2193 l_Num_Of_Options NUMBER := 0;
2194
2195 l_Dependency01 bsc_kpi_analysis_groups.dependency_flag%TYPE := -1;
2196 l_Dependency12 bsc_kpi_analysis_groups.dependency_flag%TYPE := -1;
2197
2198 l_Is_Dependent BOOLEAN := FALSE;
2199 l_Change_Dim_Set bsc_kpi_analysis_groups.change_dim_set%TYPE := 0;
2200
2201 CURSOR c_Max_Groups IS
2202 SELECT
2203 MAX(analysis_group_id)
2204 FROM
2205 bsc_kpi_analysis_groups
2206 WHERE
2207 indicator = p_Indicator;
2208
2209 CURSOR c_Num_Options(p_Group_Id NUMBER) IS
2210 SELECT
2211 COUNT(1)
2212 FROM
2213 bsc_kpi_analysis_options_b
2214 WHERE
2215 indicator = p_Indicator AND
2216 analysis_group_id = p_Group_Id;
2217
2218 BEGIN
2219 SAVEPOINT Remove_Empty_Groups_PVT;
2220 x_return_status := FND_API.G_RET_STS_SUCCESS;
2221 FND_MSG_PUB.Initialize;
2222
2223 l_Dependency01 := Get_Dependency(p_Indicator,1);
2224 l_Dependency12 := Get_Dependency(p_Indicator,2);
2225
2226 OPEN c_Max_Groups;
2227 FETCH c_Max_Groups INTO l_Max_Groups;
2228 CLOSE c_Max_Groups;
2229
2230 l_Cur_Group := p_Analysis_Group_Id;
2231
2232 WHILE l_Cur_Group <= l_Max_Groups LOOP
2233 l_Num_Of_Options := 0;
2234 OPEN c_Num_Options(l_Cur_Group);
2235 FETCH c_Num_Options INTO l_Num_Of_Options;
2236 CLOSE c_Num_Options;
2237
2238 IF (l_Num_Of_Options = 0) THEN
2239 SELECT
2240 change_dim_set
2241 INTO
2242 l_Change_Dim_Set
2243 FROM
2244 bsc_kpi_analysis_groups
2245 WHERE
2246 indicator = p_Indicator AND
2247 analysis_group_id = l_Cur_Group;
2248
2249 DELETE FROM
2250 bsc_kpi_analysis_groups
2251 WHERE
2252 indicator = p_Indicator AND
2253 analysis_group_id = l_Cur_Group;
2254
2255 IF l_Change_Dim_Set = 1 THEN
2256 UPDATE
2257 bsc_kpi_analysis_groups
2258 SET
2259 change_dim_set = 1
2260 WHERE
2261 indicator = p_Indicator AND
2262 analysis_group_id = 0;
2263 END IF;
2264 END IF;
2265 l_Cur_Group := l_Cur_Group + 1;
2266 END LOOP;
2267
2268 IF FND_API.To_Boolean( p_commit ) THEN
2269 COMMIT;
2270 END IF;
2271
2272 EXCEPTION
2273 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2274 ROLLBACK TO Remove_Empty_Groups_PVT;
2275 IF (x_msg_data IS NULL) THEN
2276 FND_MSG_PUB.Count_And_Get
2277 ( p_encoded => FND_API.G_FALSE
2278 , p_count => x_msg_count
2279 , p_data => x_msg_data
2280 );
2281 END IF;
2282 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2283 WHEN OTHERS THEN
2284 ROLLBACK TO Remove_Empty_Groups_PVT;
2285 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2286 IF (x_msg_data IS NOT NULL) THEN
2287 x_msg_data := x_msg_data||' -> BSC_OBJ_ANALYSIS_OPTIONS_PUB.Remove_Empty_Groups ';
2288 ELSE
2289 x_msg_data := SQLERRM||' at BSC_OBJ_ANALYSIS_OPTIONS_PUB.Remove_Empty_Groups ';
2290 END IF;
2291 END Remove_Empty_Groups;
2292
2293 /************************************************************************************
2294 -- API name : Delete_Analysis_Option_UI
2295 -- Type : Public
2296 -- Function:
2297 -- 1. Deletes the analysis option and also the corresponding childrent
2298 -- 2. Deletes the analysis measures corresponding to these analysis options
2299 -- 3. Removes imported dimension set incase of bis measure
2300 -- 4. Checks for structural change and changes the prototype_flag
2301 -- 5. Resets the default kpi incase the default is deleted
2302 -- 6. Refreshes the entries in bsc_kpi_defaults tables
2303 ************************************************************************************/
2304 PROCEDURE Delete_Analysis_Option_UI(
2305 p_commit IN VARCHAR2 := FND_API.G_FALSE
2306 ,p_Indicator IN NUMBER
2307 ,p_Analysis_Group_Id IN NUMBER := 0
2308 ,p_Option_Id IN NUMBER := 0
2309 ,p_Parent_Option_Id IN NUMBER := 0
2310 ,p_GrandParent_Option_Id IN NUMBER := 0
2311 ,p_time_stamp IN VARCHAR2 := NULL
2312 ,x_return_status OUT NOCOPY VARCHAR2
2313 ,x_msg_count OUT NOCOPY NUMBER
2314 ,x_msg_data OUT NOCOPY VARCHAR2
2315 ) IS
2316 l_Bsc_Kpi_Entity_Rec BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
2317 l_Count NUMBER := 0;
2318
2319 l_Bsc_AnaOpt_Rec Bsc_Analysis_Option_Pub.Bsc_Option_Rec_Type;
2320 l_Initial_Options NUMBER := 0;
2321 l_Reset_Child_Defaults BOOLEAN := FALSE;
2322 l_new_Default bsc_kpi_analysis_groups.default_value%TYPE := 0;
2323 l_commit VARCHAR2(2) := FND_API.G_FALSE;
2324 l_olddim_set_ids FND_TABLE_OF_NUMBER;
2325 l_newdim_set_ids FND_TABLE_OF_NUMBER;
2326 l_Removed_Dim_Set_Ids FND_TABLE_OF_NUMBER;
2327
2328 l_olddim_Dataset_map BSC_KPI_SERIES_PUB.Bsc_Dim_Dataset_Table;
2329
2330 i NUMBER;
2331 CURSOR c_Shared_Objs IS
2332 SELECT
2333 indicator
2334 FROM
2335 bsc_kpis_b
2336 WHERE
2337 source_indicator = p_Indicator AND
2338 prototype_flag <> 2 AND
2339 share_flag = 2;
2340
2341 CURSOR c_imported_dims IS
2342 SELECT
2343 kpi_dim.dim_set_id
2344 FROM
2345 bsc_kpis_b kpi,
2346 bsc_kpi_dim_groups kpi_dim,
2347 bsc_sys_dim_groups_vl sys_dim,
2348 bsc_kpi_analysis_options_b kpi_opt
2349 WHERE
2350 kpi.indicator = p_Indicator AND
2351 kpi.short_name IS NULL AND
2352 kpi_dim.indicator = kpi.indicator AND
2353 sys_dim.dim_group_id = kpi_dim.dim_group_id AND
2354 kpi_opt.indicator = kpi.indicator AND
2355 kpi_opt.dim_set_id = kpi_dim.dim_set_id AND
2356 bsc_bis_dimension_pub.get_dimension_source(sys_dim.short_name) = BSC_UTILITY.c_PMF;
2357
2358 BEGIN
2359 SAVEPOINT Delete_Analysis_Opt_UI_PVT;
2360 -- Check that the indicator id passed is Valid
2361 IF p_Indicator IS NOT NULL THEN
2362 l_Count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_KPIS_B'
2363 ,'indicator'
2364 , p_Indicator);
2365 IF l_Count = 0 THEN
2366 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_KPI_ID');
2367 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Indicator);
2368 FND_MSG_PUB.ADD;
2369 RAISE FND_API.G_EXC_ERROR;
2370 END IF;
2371 ELSE
2372 FND_MESSAGE.SET_NAME('BSC','BSC_NO_KPI_ID_ENTERED');
2373 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Indicator);
2374 FND_MSG_PUB.ADD;
2375 RAISE FND_API.G_EXC_ERROR;
2376 END IF;
2377 BSC_BIS_LOCKS_PUB.LOCK_KPI
2378 ( p_Kpi_Id => p_Indicator
2379 , p_time_stamp => p_time_stamp
2380 , p_Full_Lock_Flag => NULL
2381 , x_return_status => x_return_status
2382 , x_msg_count => x_msg_count
2383 , x_msg_data => x_msg_data
2384 );
2385 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2386 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2387 END IF;
2388
2389 l_Bsc_AnaOpt_Rec.Bsc_Kpi_Id := p_Indicator;
2390 l_Bsc_AnaOpt_Rec.Bsc_Analysis_Group_Id := p_Analysis_Group_Id;
2391 l_Bsc_AnaOpt_Rec.Bsc_Analysis_Option_Id := p_Option_Id;
2392 l_Bsc_AnaOpt_Rec.Bsc_Parent_Option_Id := p_Parent_Option_Id;
2393 l_Bsc_AnaOpt_Rec.Bsc_Grandparent_Option_Id := p_GrandParent_Option_Id;
2394 l_Bsc_AnaOpt_Rec.Bsc_Dataset_Series_Id := 0;
2395 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id := p_Indicator;
2396
2397 Get_Current_Dim_DataSet_Map (
2398 p_Indicator => p_Indicator
2399 ,x_dim_Dataset_map => l_olddim_Dataset_map
2400 );
2401
2402 CASE p_Analysis_Group_Id
2403 WHEN 0 THEN
2404 l_Bsc_AnaOpt_Rec.Bsc_Option_Group0 := p_Option_Id;
2405 l_Bsc_AnaOpt_Rec.Bsc_Option_Group1 := 0;
2406 l_Bsc_AnaOpt_Rec.Bsc_Option_Group2 := 0;
2407 WHEN 1 THEN
2408 l_Bsc_AnaOpt_Rec.Bsc_Option_Group0 := p_Parent_Option_Id;
2409 l_Bsc_AnaOpt_Rec.Bsc_Option_Group1 := p_Option_Id;
2410 l_Bsc_AnaOpt_Rec.Bsc_Option_Group2 := 0;
2411 WHEN 2 THEN
2412 l_Bsc_AnaOpt_Rec.Bsc_Option_Group0 := p_GrandParent_Option_Id;
2413 l_Bsc_AnaOpt_Rec.Bsc_Option_Group1 := p_Parent_Option_Id;
2414 l_Bsc_AnaOpt_Rec.Bsc_Option_Group2 := p_Option_Id;
2415 END CASE;
2416
2417 OPEN c_imported_dims;
2418 FETCH c_imported_dims BULK COLLECT INTO l_olddim_set_ids;
2419 CLOSE c_imported_dims;
2420
2421 l_Initial_Options := BSC_ANALYSIS_OPTION_PUB.Get_Num_Analysis_options(p_Indicator,p_Analysis_Group_Id);
2422
2423 Delete_Analysis_Option_Wrap (
2424 p_commit => p_commit
2425 ,p_Anal_Opt_Rec => l_Bsc_AnaOpt_Rec
2426 ,p_cascade_shared => TRUE
2427 ,x_return_status => x_return_status
2428 ,x_msg_count => x_msg_count
2429 ,x_msg_data => x_msg_data
2430 );
2431 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2432 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2433 END IF;
2434
2435 OPEN c_imported_dims;
2436 FETCH c_imported_dims BULK COLLECT INTO l_newdim_set_ids;
2437 CLOSE c_imported_dims;
2438
2439 SELECT column_value dim_set_id
2440 BULK COLLECT
2441 INTO l_Removed_Dim_Set_Ids
2442 FROM
2443 (SELECT
2444 t.column_value
2445 FROM
2446 TABLE(CAST(l_olddim_set_ids AS FND_TABLE_OF_NUMBER)) t
2447 MINUS
2448 SELECT
2449 t.column_value
2450 FROM
2451 TABLE(CAST(l_newdim_set_ids AS FND_TABLE_OF_NUMBER)) t );
2452
2453 FOR i in 1..l_Removed_Dim_Set_Ids.COUNT LOOP
2454 BSC_BIS_KPI_MEAS_PUB.Remove_Unused_PMF_Dimenison
2455 ( p_commit => FND_API.G_FALSE
2456 , p_Kpi_Id => p_Indicator
2457 , p_dim_set_id => l_Removed_Dim_Set_Ids(i)
2458 , x_return_status => x_return_status
2459 , x_msg_count => x_msg_count
2460 , x_msg_data => x_msg_data
2461 );
2462 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2463 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2464 END IF;
2465 BSC_BIS_KPI_MEAS_PUB.Delete_Dim_Set
2466 ( p_commit => FND_API.G_FALSE
2467 , p_kpi_id => p_Indicator
2468 , p_dim_set_id => l_Removed_Dim_Set_Ids(i)
2469 , x_return_status => x_return_status
2470 , x_msg_count => x_msg_count
2471 , x_msg_data => x_msg_data
2472 );
2473 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2474 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2475 END IF;
2476 END LOOP;
2477
2478
2479 BSC_KPI_SERIES_PUB.Check_Series_Default_Props (
2480 p_commit => l_commit
2481 ,p_Indicator => p_Indicator
2482 ,x_return_status => x_return_status
2483 ,x_msg_count => x_msg_count
2484 ,x_msg_data => x_msg_data
2485 );
2486 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2487 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2488 END IF;
2489
2490 Reset_Group_Defaults (
2491 p_commit => l_commit
2492 ,p_Indicator => p_Indicator
2493 ,p_Analysis_Group_Id => p_Analysis_Group_Id
2494 ,p_Option_Id => p_Option_Id
2495 ,x_return_status => x_return_status
2496 ,x_msg_count => x_msg_count
2497 ,x_msg_data => x_msg_data
2498 );
2499 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2500 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2501 END IF;
2502
2503 Remove_Empty_Groups (
2504 p_commit => l_commit
2505 ,p_Indicator => p_Indicator
2506 ,p_Analysis_Group_Id => p_Analysis_Group_Id
2507 ,p_Option_Id => p_Option_Id
2508 ,p_Parent_Option_Id => p_Parent_Option_Id
2509 ,p_Initial_Options => l_Initial_Options
2510 ,x_return_status => x_return_status
2511 ,x_msg_count => x_msg_count
2512 ,x_msg_data => x_msg_data
2513 );
2514 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2515 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2516 END IF;
2517
2518 Check_UserLevel_Values(
2519 p_commit => l_commit
2520 ,p_Indicator => p_Indicator
2521 ,p_cascade_shared => TRUE
2522 ,x_return_status => x_return_status
2523 ,x_msg_count => x_msg_count
2524 ,x_msg_data => x_msg_data
2525 );
2526 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2527 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2528 END IF;
2529
2530 Check_Strucural_Flag_Change (
2531 p_commit => l_commit
2532 ,p_Indicator => p_Indicator
2533 ,p_olddim_Dataset_map => l_olddim_Dataset_map
2534 ,x_return_status => x_return_status
2535 ,x_msg_count => x_msg_count
2536 ,x_msg_data => x_msg_data
2537 );
2538 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2539 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2540 END IF;
2541
2542 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id := p_Indicator;
2543 BSC_KPI_PUB.Update_Kpi_Time_Stamp(
2544 p_commit => p_commit
2545 ,p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
2546 ,x_return_status => x_return_status
2547 ,x_msg_count => x_msg_count
2548 ,x_msg_data => x_msg_data
2549 );
2550 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2551 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2552 END IF;
2553
2554 BSC_DESIGNER_PVT.Deflt_RefreshKpi(l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id);
2555
2556 FOR cd IN c_Shared_Objs LOOP
2557 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id := cd.Indicator;
2558 FOR i in 1..l_Removed_Dim_Set_Ids.COUNT LOOP
2559 BSC_BIS_KPI_MEAS_PUB.Remove_Unused_PMF_Dimenison
2560 ( p_commit => FND_API.G_FALSE
2561 , p_Kpi_Id => cd.Indicator
2562 , p_dim_set_id => l_Removed_Dim_Set_Ids(i)
2563 , x_return_status => x_return_status
2564 , x_msg_count => x_msg_count
2565 , x_msg_data => x_msg_data
2566 );
2567 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2568 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2569 END IF;
2570 BSC_BIS_KPI_MEAS_PUB.Delete_Dim_Set
2571 ( p_commit => FND_API.G_FALSE
2572 , p_kpi_id => cd.Indicator
2573 , p_dim_set_id => l_Removed_Dim_Set_Ids(i)
2574 , x_return_status => x_return_status
2575 , x_msg_count => x_msg_count
2576 , x_msg_data => x_msg_data
2577 );
2578 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2579 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2580 END IF;
2581 END LOOP;
2582 BSC_KPI_SERIES_PUB.Check_Series_Default_Props(
2583 p_commit => l_commit
2584 ,p_Indicator => cd.Indicator
2585 ,x_return_status => x_return_status
2586 ,x_msg_count => x_msg_count
2587 ,x_msg_data => x_msg_data
2588 );
2589 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2590 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2591 END IF;
2592
2593 Reset_Group_Defaults (
2594 p_commit => l_commit
2595 ,p_Indicator => cd.Indicator
2596 ,p_Analysis_Group_Id => p_Analysis_Group_Id
2597 ,p_Option_Id => p_Option_Id
2598 ,x_return_status => x_return_status
2599 ,x_msg_count => x_msg_count
2600 ,x_msg_data => x_msg_data
2601 );
2602 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2603 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2604 END IF;
2605
2606 Remove_Empty_Groups (
2607 p_commit => l_commit
2608 ,p_Indicator => cd.Indicator
2609 ,p_Analysis_Group_Id => p_Analysis_Group_Id
2610 ,p_Option_Id => p_Option_Id
2611 ,p_Parent_Option_Id => p_Parent_Option_Id
2612 ,p_Initial_Options => l_Initial_Options
2613 ,x_return_status => x_return_status
2614 ,x_msg_count => x_msg_count
2615 ,x_msg_data => x_msg_data
2616 );
2617 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2618 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2619 END IF;
2620
2621 Check_UserLevel_Values(
2622 p_commit => l_commit
2623 ,p_Indicator => cd.Indicator
2624 ,p_cascade_shared => TRUE
2625 ,x_return_status => x_return_status
2626 ,x_msg_count => x_msg_count
2627 ,x_msg_data => x_msg_data
2628 );
2629 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2630 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2631 END IF;
2632
2633 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id := cd.Indicator;
2634 BSC_KPI_PUB.Update_Kpi_Time_Stamp(
2635 p_commit => p_commit
2636 ,p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
2637 ,x_return_status => x_return_status
2638 ,x_msg_count => x_msg_count
2639 ,x_msg_data => x_msg_data
2640 );
2641 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2642 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2643 END IF;
2644
2645 BSC_DESIGNER_PVT.Deflt_RefreshKpi(l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id);
2646
2647 END LOOP;
2648
2649 IF FND_API.To_Boolean( p_commit ) THEN
2650 COMMIT;
2651 END IF;
2652
2653 EXCEPTION
2654 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2655 ROLLBACK TO Delete_Analysis_Opt_UI_PVT;
2656 IF (x_msg_data IS NULL) THEN
2657 FND_MSG_PUB.Count_And_Get
2658 ( p_encoded => FND_API.G_FALSE
2659 , p_count => x_msg_count
2660 , p_data => x_msg_data
2661 );
2662 END IF;
2663 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2664 WHEN OTHERS THEN
2665 ROLLBACK TO Delete_Analysis_Opt_UI_PVT;
2666 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2667 IF (x_msg_data IS NOT NULL) THEN
2668 x_msg_data := x_msg_data||' -> BSC_OBJ_ANALYSIS_OPTIONS_PUB.Delete_Analysis_Option_UI ';
2669 ELSE
2670 x_msg_data := SQLERRM||' at BSC_OBJ_ANALYSIS_OPTIONS_PUB.Delete_Analysis_Option_UI ';
2671 END IF;
2672
2673 END Delete_Analysis_Option_UI;
2674
2675 /************************************************************************************
2676 -- API name : Create_Analysis_Group
2677 -- Type : Public
2678 ************************************************************************************/
2679
2680 PROCEDURE Create_Analysis_Group (
2681 p_commit IN VARCHAR2 := FND_API.G_FALSE
2682 ,p_Indicator IN NUMBER
2683 ,p_Analysis_Group_Id IN NUMBER
2684 ,p_Num_Of_Options IN NUMBER
2685 ,p_Dependency_Flag IN NUMBER
2686 ,p_Parent_Analysis_Id IN NUMBER
2687 ,p_Change_Dim_Set IN NUMBER
2688 ,p_Default_Value IN NUMBER
2689 ,p_Short_Name IN NUMBER
2690 ,x_return_status OUT NOCOPY VARCHAR2
2691 ,x_msg_count OUT NOCOPY NUMBER
2692 ,x_msg_data OUT NOCOPY VARCHAR2
2693 ) IS
2694 BEGIN
2695
2696 SAVEPOINT Create_Analysis_Group_PVT;
2697 x_return_status := FND_API.G_RET_STS_SUCCESS;
2698 FND_MSG_PUB.Initialize;
2699
2700 INSERT INTO bsc_kpi_analysis_groups (
2701 indicator,
2702 analysis_group_id,
2703 num_of_options,
2704 dependency_flag,
2705 parent_analysis_id,
2706 change_dim_set,
2707 default_value,
2708 short_name
2709 ) VALUES(
2710 p_Indicator
2711 ,p_Analysis_Group_Id
2712 ,p_Num_Of_Options
2713 ,p_Dependency_Flag
2714 ,p_Parent_Analysis_Id
2715 ,p_Change_Dim_Set
2716 ,p_Default_Value
2717 ,p_Short_Name
2718 );
2719
2720 IF FND_API.To_Boolean( p_commit ) THEN
2721 COMMIT;
2722 END IF;
2723
2724 EXCEPTION
2725 WHEN OTHERS THEN
2726 ROLLBACK TO Create_Analysis_Group_PVT;
2727 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2728 IF (x_msg_data IS NOT NULL) THEN
2729 x_msg_data := x_msg_data||' -> BSC_OBJ_ANALYSIS_OPTIONS_PUB.Create_Analysis_Group ';
2730 ELSE
2731 x_msg_data := SQLERRM||' at BSC_OBJ_ANALYSIS_OPTIONS_PUB.Create_Analysis_Group ';
2732 END IF;
2733 END Create_Analysis_Group;
2734
2735 /************************************************************************************
2736 -- API name : Generate_Analysis_Meas_Combs
2737 -- Type : Public
2738 -- Function :
2739 -- This API generates entries in bsc_kpi_analysis_measures by taking into
2740 -- consideration the dependency relationships between groups
2741 -- This is specially required when the groups have an independent relationship
2742 --
2743 ************************************************************************************/
2744
2745 PROCEDURE Generate_Analysis_Meas_Combs (
2746 p_commit IN VARCHAR2 := FND_API.G_FALSE
2747 ,p_Indicator IN NUMBER
2748 ,p_Analysis_Option0 IN NUMBER
2749 ,p_Analysis_Option1 IN NUMBER
2750 ,p_Analysis_Option2 IN NUMBER
2751 ,p_Dependency_01 IN NUMBER
2752 ,p_Dependency_12 IN NUMBER
2753 ,x_return_status OUT NOCOPY VARCHAR2
2754 ,x_msg_count OUT NOCOPY NUMBER
2755 ,x_msg_data OUT NOCOPY VARCHAR2
2756 ) IS
2757
2758 TYPE ana_opt_type IS REF CURSOR;
2759 c_ana_opt_type ana_opt_type;
2760 l_Count NUMBER := 0;
2761 l_Sql VARCHAR2(2000);
2762 l_ana_option_id bsc_kpi_analysis_options_b.option_id%TYPE;
2763 l_commit VARCHAR2(2) := FND_API.G_FALSE;
2764 l_Anal_Opt_Rec Bsc_Analysis_Option_Pub.Bsc_Option_Rec_Type;
2765 l_Generated_Row_Count NUMBER := 0;
2766
2767 CURSOR c_Exists_Ana_Opt IS
2768 SELECT
2769 COUNT(1)
2770 FROM
2771 bsc_kpi_analysis_measures_b
2772 WHERE
2773 indicator = p_Indicator AND
2774 analysis_option0 = p_Analysis_Option0 AND
2775 analysis_option1 = p_Analysis_Option1 AND
2776 analysis_option2 = p_Analysis_Option2;
2777
2778 BEGIN
2779 SAVEPOINT Generate_Ana_Meas_Combs_PVT;
2780 x_return_status := FND_API.G_RET_STS_SUCCESS;
2781
2782
2783 l_sql := 'SELECT option_id FROM bsc_kpi_analysis_options_b WHERE indicator = :1 ';
2784
2785 IF p_Analysis_Option0 = -1 THEN -- Permute with Group0 Analysis Options
2786
2787 l_sql := l_sql || ' AND analysis_group_id = 0';
2788 OPEN c_ana_opt_type FOR l_sql USING p_Indicator;
2789 LOOP
2790 FETCH c_ana_opt_type INTO l_ana_option_id;
2791 EXIT WHEN c_ana_opt_type%notfound;
2792 Generate_Analysis_Meas_Combs (
2793 p_commit => l_commit
2794 ,p_Indicator => p_Indicator
2795 ,p_Analysis_Option0 => l_ana_option_id
2796 ,p_Analysis_Option1 => p_Analysis_Option1
2797 ,p_Analysis_Option2 => p_Analysis_Option2
2798 ,p_Dependency_01 => p_Dependency_01
2799 ,p_Dependency_12 => p_Dependency_12
2800 ,x_return_status => x_return_status
2801 ,x_msg_count => x_msg_count
2802 ,x_msg_data => x_msg_data
2803 );
2804 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2805 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2806 END IF;
2807 END LOOP;
2808
2809 ELSIF p_Analysis_Option1 = -1 THEN -- Permute with Group1 Analysis Options
2810
2811 l_sql := l_sql || ' AND analysis_group_id = 1';
2812 OPEN c_ana_opt_type FOR l_sql USING p_Indicator;
2813 IF ( p_Dependency_01 = 1) THEN
2814 l_sql := l_sql || ' AND parent_option_id = :2';
2815 CLOSE c_ana_opt_type;
2816 OPEN c_ana_opt_type FOR l_sql USING p_Indicator,p_Analysis_Option0;
2817 END IF;
2818 LOOP
2819 FETCH c_ana_opt_type INTO l_ana_option_id;
2820 EXIT WHEN c_ana_opt_type%notfound;
2821 l_Generated_Row_Count := l_Generated_Row_Count + 1;
2822 Generate_Analysis_Meas_Combs (
2823 p_commit => l_commit
2824 ,p_Indicator => p_Indicator
2825 ,p_Analysis_Option0 => p_Analysis_Option0
2826 ,p_Analysis_Option1 => l_ana_option_id
2827 ,p_Analysis_Option2 => p_Analysis_Option2
2828 ,p_Dependency_01 => p_Dependency_01
2829 ,p_Dependency_12 => p_Dependency_12
2830 ,x_return_status => x_return_status
2831 ,x_msg_count => x_msg_count
2832 ,x_msg_data => x_msg_data
2833 );
2834 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2835 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2836 END IF;
2837 END LOOP;
2838 IF l_Generated_Row_Count = 0 THEN -- Generate atleast with 0
2839 Generate_Analysis_Meas_Combs (
2840 p_commit => l_commit
2841 ,p_Indicator => p_Indicator
2842 ,p_Analysis_Option0 => p_Analysis_Option0
2843 ,p_Analysis_Option1 => 0
2844 ,p_Analysis_Option2 => p_Analysis_Option2
2845 ,p_Dependency_01 => p_Dependency_01
2846 ,p_Dependency_12 => p_Dependency_12
2847 ,x_return_status => x_return_status
2848 ,x_msg_count => x_msg_count
2849 ,x_msg_data => x_msg_data
2850 );
2851 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2852 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2853 END IF;
2854 END IF;
2855
2856 ELSIF p_Analysis_Option2 = -1 THEN -- Permute with Group2 Analysis Options
2857
2858 l_sql := l_sql || ' AND analysis_group_id = 2';
2859 OPEN c_ana_opt_type FOR l_sql USING p_Indicator;
2860 IF ( p_Dependency_12 = 1) THEN
2861 l_sql := l_sql || ' AND parent_option_id = :2';
2862 IF ( p_Dependency_01 = 1) THEN
2863 l_sql := l_sql || ' AND grandparent_option_id = :3';
2864 CLOSE c_ana_opt_type;
2865 OPEN c_ana_opt_type FOR l_sql USING p_Indicator,p_Analysis_Option1,p_Analysis_Option0;
2866 ELSE
2867 CLOSE c_ana_opt_type;
2868 OPEN c_ana_opt_type FOR l_sql USING p_Indicator,p_Analysis_Option1;
2869 END IF;
2870 END IF;
2871
2872 LOOP
2873 FETCH c_ana_opt_type INTO l_ana_option_id;
2874 EXIT WHEN c_ana_opt_type%notfound;
2875 l_Generated_Row_Count := l_Generated_Row_Count + 1;
2876
2877 Generate_Analysis_Meas_Combs (
2878 p_commit => l_commit
2879 ,p_Indicator => p_Indicator
2880 ,p_Analysis_Option0 => p_Analysis_Option0
2881 ,p_Analysis_Option1 => p_Analysis_Option1
2882 ,p_Analysis_Option2 => l_ana_option_id
2883 ,p_Dependency_01 => p_Dependency_01
2884 ,p_Dependency_12 => p_Dependency_12
2885 ,x_return_status => x_return_status
2886 ,x_msg_count => x_msg_count
2887 ,x_msg_data => x_msg_data
2888 );
2889 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2890 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2891 END IF;
2892 END LOOP;
2893 IF l_Generated_Row_Count = 0 THEN
2894 Generate_Analysis_Meas_Combs (
2895 p_commit => l_commit
2896 ,p_Indicator => p_Indicator
2897 ,p_Analysis_Option0 => p_Analysis_Option0
2898 ,p_Analysis_Option1 => p_Analysis_Option1
2899 ,p_Analysis_Option2 => 0
2900 ,p_Dependency_01 => p_Dependency_01
2901 ,p_Dependency_12 => p_Dependency_12
2902 ,x_return_status => x_return_status
2903 ,x_msg_count => x_msg_count
2904 ,x_msg_data => x_msg_data
2905 );
2906 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2907 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2908 END IF;
2909 END IF;
2910
2911 ELSE
2912
2913 OPEN c_Exists_Ana_Opt;
2914 FETCH c_Exists_Ana_Opt INTO l_Count;
2915 CLOSE c_Exists_Ana_Opt;
2916
2917 IF l_Count = 0 THEN
2918 l_Anal_Opt_Rec.Bsc_Kpi_Id := p_Indicator;
2919 l_Anal_Opt_Rec.Bsc_Option_Group0 := p_Analysis_Option0;
2920 l_Anal_Opt_Rec.Bsc_Option_Group1 := p_Analysis_Option1;
2921 l_Anal_Opt_Rec.Bsc_Option_Group2 := p_Analysis_Option2;
2922 l_Anal_Opt_Rec.Bsc_Dataset_Series_Id := 0;
2923 l_Anal_Opt_Rec.Bsc_Dataset_Id := -1;
2924 l_Anal_Opt_Rec.Bsc_Dataset_Axis := 1;
2925 l_Anal_Opt_Rec.Bsc_Dataset_Series_Type := 1;
2926 l_Anal_Opt_Rec.Bsc_Dataset_Stack_Series_Id := NULL;
2927 l_Anal_Opt_Rec.Bsc_Dataset_Bm_Flag := 1;
2928 l_Anal_Opt_Rec.Bsc_Dataset_Budget_Flag := 1;
2929 l_Anal_Opt_Rec.Bsc_Dataset_Default_Value := 1;
2930 l_Anal_Opt_Rec.Bsc_Dataset_Series_Color := 10053171;
2931 l_Anal_Opt_Rec.Bsc_Dataset_Bm_Color := 10053171;
2932 l_Anal_Opt_Rec.Bsc_Measure_Long_Name := fnd_message.get_string('BSC','BSC_NEW_SERIES') || ' 0';
2933 l_Anal_Opt_Rec.Bsc_Measure_Help := fnd_message.get_string('BSC','BSC_NEW_SERIES') || ' 0';
2934 l_Anal_Opt_Rec.Bsc_Measure_Prototype_Flag := 7;
2935
2936 Bsc_Analysis_Option_Pub.Create_Analysis_Measures(
2937 p_commit => l_commit
2938 ,p_Anal_Opt_Rec => l_Anal_Opt_Rec
2939 ,x_return_status => x_return_status
2940 ,x_msg_count => x_msg_count
2941 ,x_msg_data => x_msg_data
2942 );
2943 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2944 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2945 END IF;
2946
2947 END IF;
2948 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2949 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2950 END IF;
2951 END IF;
2952
2953 IF FND_API.To_Boolean( p_commit ) THEN
2954 COMMIT;
2955 END IF;
2956 EXCEPTION
2957 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2958 ROLLBACK TO Generate_Ana_Meas_Combs_PVT;
2959 IF (x_msg_data IS NULL) THEN
2960 FND_MSG_PUB.Count_And_Get
2961 ( p_encoded => FND_API.G_FALSE
2962 , p_count => x_msg_count
2963 , p_data => x_msg_data
2964 );
2965 END IF;
2966 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2967 WHEN OTHERS THEN
2968 ROLLBACK TO Generate_Ana_Meas_Combs_PVT;
2969 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2970 IF (x_msg_data IS NOT NULL) THEN
2971 x_msg_data := x_msg_data||' -> BSC_OBJ_ANALYSIS_OPTIONS_PUB.Generate_Analysis_Meas_Combs ';
2972 ELSE
2973 x_msg_data := SQLERRM||' at BSC_OBJ_ANALYSIS_OPTIONS_PUB.Generate_Analysis_Meas_Combs ';
2974 END IF;
2975 END Generate_Analysis_Meas_Combs;
2976
2977 /************************************************************************************
2978 -- API name : Populate_Analysis_Meas_Combs
2979 -- Type : Public
2980 -- Function :
2981 --
2982 ************************************************************************************/
2983 PROCEDURE Populate_Analysis_Meas_Combs(
2984 p_commit IN VARCHAR2 := FND_API.G_FALSE
2985 ,p_Indicator IN NUMBER
2986 ,p_Analysis_Group_Id IN NUMBER
2987 ,p_Option_Id IN NUMBER
2988 ,p_Parent_Option_Id IN NUMBER
2989 ,p_Grandparent_Option_Id IN NUMBER
2990 ,p_Dependency_Flag IN NUMBER
2991 ,p_DataSet_Id IN NUMBER := -1
2992 ,x_return_status OUT NOCOPY VARCHAR2
2993 ,x_msg_count OUT NOCOPY NUMBER
2994 ,x_msg_data OUT NOCOPY VARCHAR2
2995 )IS
2996 l_Analysis_Opt0 bsc_kpi_analysis_measures_b.Analysis_Option0%TYPE := 0;
2997 l_Analysis_Opt1 bsc_kpi_analysis_measures_b.Analysis_Option1%TYPE := 0;
2998 l_Analysis_Opt2 bsc_kpi_analysis_measures_b.Analysis_Option2%TYPE := 0;
2999
3000 l_Dependency_01 bsc_kpi_analysis_groups.dependency_flag%TYPE := -1;
3001 l_Dependency_12 bsc_kpi_analysis_groups.dependency_flag%TYPE := -1;
3002 l_commit VARCHAR2(2) := FND_API.G_FALSE;
3003
3004 CURSOR c_dep_flag(p_Ana_Grp_Id VARCHAR2) IS
3005 SELECT dependency_flag
3006 FROM
3007 bsc_kpi_analysis_groups
3008 WHERE
3009 indicator = p_Indicator AND
3010 analysis_group_id = p_Ana_Grp_Id;
3011
3012 BEGIN
3013
3014 SAVEPOINT Populate_Ana_Meas_Combs_PVT;
3015 x_return_status := FND_API.G_RET_STS_SUCCESS;
3016
3017 OPEN c_dep_flag(1);
3018 FETCH c_dep_flag INTO l_Dependency_01;
3019 CLOSE c_dep_flag;
3020
3021 OPEN c_dep_flag(2);
3022 FETCH c_dep_flag INTO l_Dependency_12;
3023 CLOSE c_dep_flag;
3024
3025 CASE p_Analysis_Group_Id
3026 WHEN 0 THEN
3027 l_Analysis_Opt0 := p_Option_Id;
3028 IF l_Dependency_01 = 1 THEN
3029 l_Analysis_Opt1 := 0;
3030 END IF;
3031 IF l_Dependency_12 = 1 THEN
3032 l_Analysis_Opt2 := 0;
3033 END IF;
3034
3035 IF l_Dependency_01 = 0 THEN
3036 l_Analysis_Opt1 := -1;
3037 l_Analysis_Opt2 := -1;
3038 ELSIF l_Dependency_12 = 0 THEN
3039 l_Analysis_Opt2 := -1;
3040 END IF;
3041 WHEN 1 THEN
3042 l_Dependency_01 := p_Dependency_Flag;
3043 l_Analysis_Opt0 := p_Parent_Option_Id;
3044 IF l_Dependency_12 = 1 THEN
3045 l_Analysis_Opt2 := 0;
3046 END IF;
3047 IF l_Dependency_01 = 0 THEN
3048 l_Analysis_Opt0 := -1;
3049 END IF;
3050 IF l_Dependency_12 = 0 THEN
3051 l_Analysis_Opt2 := -1;
3052 END IF;
3053 l_Analysis_Opt1 := p_Option_Id;
3054 WHEN 2 THEN
3055 l_Dependency_12 := p_Dependency_Flag;
3056 l_Analysis_Opt0 := p_Grandparent_Option_Id;
3057 l_Analysis_Opt1 := p_Parent_Option_Id;
3058 IF l_Dependency_12 = 0 THEN
3059 l_Analysis_Opt0 := -1;
3060 l_Analysis_Opt1 := -1;
3061 ELSIF l_Dependency_01 = 0 THEN
3062 l_Analysis_Opt0 := -1;
3063 END IF;
3064
3065 l_Analysis_Opt2 := p_Option_Id;
3066 END CASE;
3067
3068 Generate_Analysis_Meas_Combs (
3069 p_commit => l_commit
3070 ,p_Indicator => p_Indicator
3071 ,p_Analysis_Option0 => l_Analysis_Opt0
3072 ,p_Analysis_Option1 => l_Analysis_Opt1
3073 ,p_Analysis_Option2 => l_Analysis_Opt2
3074 ,p_Dependency_01 => l_Dependency_01
3075 ,p_Dependency_12 => l_Dependency_12
3076 ,x_return_status => x_return_status
3077 ,x_msg_count => x_msg_count
3078 ,x_msg_data => x_msg_data
3079 );
3080 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3081 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3082 END IF;
3083
3084 IF FND_API.To_Boolean( p_commit ) THEN
3085 COMMIT;
3086 END IF;
3087 EXCEPTION
3088 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3089 ROLLBACK TO Populate_Ana_Meas_Combs_PVT;
3090 IF (x_msg_data IS NULL) THEN
3091 FND_MSG_PUB.Count_And_Get
3092 ( p_encoded => FND_API.G_FALSE
3093 , p_count => x_msg_count
3094 , p_data => x_msg_data
3095 );
3096 END IF;
3097 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3098 WHEN OTHERS THEN
3099 ROLLBACK TO Populate_Ana_Meas_Combs_PVT;
3100 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3101 IF (x_msg_data IS NOT NULL) THEN
3102 x_msg_data := x_msg_data||' -> BSC_OBJ_ANALYSIS_OPTIONS_PUB.Populate_Analysis_Meas_Combs ';
3103 ELSE
3104 x_msg_data := SQLERRM||' at BSC_OBJ_ANALYSIS_OPTIONS_PUB.Populate_Analysis_Meas_Combs ';
3105 END IF;
3106 END Populate_Analysis_Meas_Combs;
3107
3108 /************************************************************************************
3109 -- API name : Create_Analayis_Option
3110 -- Type : Public
3111 -- Function :
3112 -- 1. Creates the analysis group incase of this analysis option being the first
3113 -- in this group
3114 -- 2. Generates all the necessary combinations of analysis measures depending
3115 -- on the relationship between the groups
3116 -- 3. Calls the update analysis option API to set all the properties
3117 ************************************************************************************/
3118
3119 PROCEDURE Create_Analysis_Option_UI(
3120 p_commit IN VARCHAR2 := FND_API.G_FALSE
3121 ,p_Indicator IN NUMBER
3122 ,p_Analysis_Group_Id IN NUMBER := 0
3123 ,p_Option_Id IN NUMBER := 0
3124 ,p_Parent_Option_Id IN NUMBER := 0
3125 ,p_GrandParent_Option_Id IN NUMBER := 0
3126 ,p_Dependency_Flag IN NUMBER := 0
3127 ,p_DataSet_Id IN NUMBER := -1
3128 ,p_DimSet_Id IN NUMBER := 0
3129 ,p_Default_Flag IN NUMBER := 0
3130 ,p_Option_Name IN VARCHAR2
3131 ,p_Option_Help IN VARCHAR2
3132 ,p_Change_Dim_Set IN NUMBER := 0
3133 ,p_default_calculation IN NUMBER := NULL
3134 ,p_time_stamp IN VARCHAR2 := NULL
3135 ,x_return_status OUT NOCOPY VARCHAR2
3136 ,x_msg_count OUT NOCOPY NUMBER
3137 ,x_msg_data OUT NOCOPY VARCHAR2
3138 ) IS
3139
3140 l_Count NUMBER := 0;
3141 l_Parent_Analysis_Id bsc_kpi_analysis_groups.parent_analysis_id%TYPE := 0;
3142 l_Parent_Option_Id bsc_kpi_analysis_options_b.parent_option_id%TYPE;
3143 l_GrandParent_Option_Id bsc_kpi_analysis_options_b.grandparent_option_id%TYPE;
3144 l_Anal_Opt_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
3145 l_Create_Group BOOLEAN := FALSE;
3146 l_commit VARCHAR2(2) := FND_API.G_FALSE;
3147 l_Measure_Source bsc_sys_datasets_vl.source%TYPE := 'BSC';
3148
3149 l_temp_Parent_Id NUMBER := NULL;
3150 l_temp_GrandParent_Id NUMBER := NULL;
3151 l_Parent_Group_Id bsc_kpi_analysis_groups.analysis_group_id%TYPE := 0;
3152 l_GrandParent_Group_Id bsc_kpi_analysis_groups.analysis_group_id%TYPE := 0;
3153 l_olddim_Dataset_map BSC_KPI_SERIES_PUB.Bsc_Dim_Dataset_Table;
3154 CURSOR c_shared_objs IS
3155 SELECT
3156 indicator
3157 FROM
3158 bsc_kpis_b
3159 WHERE
3160 source_indicator = p_Indicator AND
3161 prototype_flag <> 2 AND
3162 share_flag = 2;
3163
3164
3165 BEGIN
3166 SAVEPOINT Create_Analayis_OptionObjPUB;
3167 -- Check that the indicator id passed is Valid
3168 IF p_Indicator IS NOT NULL THEN
3169 l_Count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_KPIS_B'
3170 ,'indicator'
3171 , p_Indicator);
3172 IF l_Count = 0 THEN
3173 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_KPI_ID');
3174 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Indicator);
3175 FND_MSG_PUB.ADD;
3176 RAISE FND_API.G_EXC_ERROR;
3177 END IF;
3178 ELSE
3179 FND_MESSAGE.SET_NAME('BSC','BSC_NO_KPI_ID_ENTERED');
3180 FND_MESSAGE.SET_TOKEN('BSC_KPI',p_Indicator);
3181 FND_MSG_PUB.ADD;
3182 RAISE FND_API.G_EXC_ERROR;
3183 END IF;
3184
3185 l_Anal_Opt_Rec.Bsc_Kpi_Id := p_Indicator;
3186 l_Anal_Opt_Rec.Bsc_Analysis_Group_Id := p_Analysis_Group_Id;
3187 l_Anal_Opt_Rec.Bsc_Analysis_Option_Id := p_Option_Id ;
3188 l_Anal_Opt_Rec.Bsc_Parent_Option_Id := p_Parent_Option_Id;
3189 l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id := p_GrandParent_Option_Id;
3190 l_Anal_Opt_Rec.Bsc_Dataset_Id := p_DataSet_Id;
3191 l_Anal_Opt_Rec.Bsc_Dim_Set_Id := p_DimSet_Id;
3192 l_Anal_Opt_Rec.Bsc_Option_Name := p_Option_Name;
3193 l_Anal_Opt_Rec.Bsc_Option_Help := p_Option_Help;
3194 l_Anal_Opt_Rec.Bsc_Option_Default_Value := p_Default_Flag;
3195
3196 SELECT DISTINCT
3197 dim.dim_set_id, dim.dataset_id,0
3198 BULK COLLECT INTO
3199 l_olddim_Dataset_map
3200 FROM
3201 bsc_db_dataset_dim_sets_v dim,
3202 bsc_sys_datasets_b ds
3203 WHERE
3204 dim.indicator = p_Indicator AND
3205 dim.dataset_id = ds.dataset_id AND
3206 ds.source = 'BSC'
3207 ORDER BY
3208 dim_set_id, dataset_id;
3209
3210 /* Check Lock on Indicator */
3211 IF p_Analysis_Group_Id IS NOT NULL THEN
3212 SELECT COUNT(1) INTO l_Count
3213 FROM bsc_kpi_analysis_groups
3214 WHERE indicator = p_Indicator AND
3215 analysis_group_id = p_Analysis_Group_Id;
3216
3217 IF l_Count = 0 THEN
3218 -- Add Analysis Group if not there
3219 IF(p_Dependency_Flag = 1) THEN
3220 l_Parent_Analysis_Id := p_Analysis_Group_Id - 1;
3221 END IF;
3222 Create_Analysis_Group (
3223 p_Indicator => p_Indicator
3224 , p_Analysis_Group_Id => p_Analysis_Group_Id
3225 , p_Num_Of_Options => 0
3226 , p_Dependency_Flag => p_Dependency_Flag
3227 , p_Parent_Analysis_Id => l_Parent_Analysis_Id
3228 , p_Change_Dim_Set => NULL
3229 , p_Default_Value => 0
3230 , p_Short_Name => NULL
3231 , x_return_status => x_return_status
3232 , x_msg_count => x_msg_count
3233 , x_msg_data => x_msg_data
3234 );
3235 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3236 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3237 END IF;
3238 l_Create_Group := TRUE;
3239 END IF;
3240 END IF;
3241
3242
3243 Get_Parent_GrandParent_Ids(
3244 p_Indicator => p_Indicator
3245 ,p_Analysis_Group_Id => p_Analysis_Group_Id
3246 ,p_Parent_Id => l_Anal_Opt_Rec.Bsc_Parent_Option_Id
3247 ,p_GrandParent_Id => l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id
3248 ,p_Independent_Par_Id => 0
3249 ,x_Parent_Id => l_temp_Parent_Id
3250 ,x_GrandParent_Id => l_temp_GrandParent_Id
3251 ,x_Parent_Group_Id => l_Parent_Group_Id
3252 ,x_GrandParent_Group_Id => l_GrandParent_Group_Id
3253 );
3254
3255 l_Anal_Opt_Rec.Bsc_Parent_Option_Id := l_temp_Parent_Id;
3256 l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id := l_temp_GrandParent_Id;
3257
3258 IF p_Dataset_id IS NOT NULL THEN
3259 SELECT source
3260 INTO l_measure_source
3261 FROM bsc_sys_datasets_vl
3262 WHERE dataset_id = p_Dataset_id;
3263 END IF;
3264 --IF l_measure_source = 'BSC' THEN
3265 Bsc_Analysis_Option_Pvt.Create_Analysis_Options (
3266 p_commit => l_commit
3267 ,p_Anal_Opt_Rec => l_Anal_Opt_Rec
3268 ,x_return_status => x_return_status
3269 ,x_msg_count => x_msg_count
3270 ,x_msg_data => x_msg_data
3271 );
3272 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3273 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3274 END IF;
3275
3276 -- Populate Measures
3277 Populate_Analysis_Meas_Combs(
3278 p_commit => l_commit
3279 ,p_Indicator => p_Indicator
3280 ,p_Analysis_Group_Id => p_Analysis_Group_Id
3281 ,p_Option_Id => p_Option_Id
3282 ,p_Parent_Option_Id => p_Parent_Option_Id
3283 ,p_Grandparent_Option_Id => p_Grandparent_Option_Id
3284 ,p_Dependency_Flag => p_Dependency_Flag
3285 ,p_DataSet_Id => -1
3286 ,x_return_status => x_return_status
3287 ,x_msg_count => x_msg_count
3288 ,x_msg_data => x_msg_data
3289 );
3290 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3291 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3292 END IF;
3293 --END IF;
3294
3295 -- Update Groups Table Data
3296 Update_Analysis_Opt_Count (
3297 p_Indicator => p_Indicator
3298 ,x_return_status => x_return_status
3299 ,x_msg_count => x_msg_count
3300 ,x_msg_data => x_msg_data
3301 );
3302 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3303 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3304 END IF;
3305
3306 FOR cd in c_shared_objs LOOP
3307 l_Anal_Opt_Rec.Bsc_Kpi_Id := cd.Indicator;
3308 IF l_Create_Group THEN
3309 Create_Analysis_Group (
3310 p_Indicator => cd.Indicator
3311 , p_Analysis_Group_Id => p_Analysis_Group_Id
3312 , p_Num_Of_Options => 0
3313 , p_Dependency_Flag => p_Dependency_Flag
3314 , p_Parent_Analysis_Id => l_Parent_Analysis_Id
3315 , p_Change_Dim_Set => NULL
3316 , p_Default_Value => 0
3317 , p_Short_Name => NULL
3318 , x_return_status => x_return_status
3319 , x_msg_count => x_msg_count
3320 , x_msg_data => x_msg_data
3321 );
3322 END IF;
3323 l_Anal_Opt_Rec.Bsc_Kpi_Id := cd.Indicator;
3324
3325 Bsc_Analysis_Option_Pvt.Create_Analysis_Options (
3326 p_commit => l_commit
3327 ,p_Anal_Opt_Rec => l_Anal_Opt_Rec
3328 ,x_return_status => x_return_status
3329 ,x_msg_count => x_msg_count
3330 ,x_msg_data => x_msg_data
3331 );
3332 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3333 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3334 END IF;
3335
3336
3337 -- Populate Measures
3338 Populate_Analysis_Meas_Combs(
3339 p_commit => l_commit
3340 ,p_Indicator => cd.Indicator
3341 ,p_Analysis_Group_Id => p_Analysis_Group_Id
3342 ,p_Option_Id => p_Option_Id
3343 ,p_Parent_Option_Id => p_Parent_Option_Id
3344 ,p_Grandparent_Option_Id => p_Grandparent_Option_Id
3345 ,p_Dependency_Flag => p_Dependency_Flag
3346 ,p_DataSet_Id => -1
3347 ,x_return_status => x_return_status
3348 ,x_msg_count => x_msg_count
3349 ,x_msg_data => x_msg_data
3350 );
3351 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3352 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3353 END IF;
3354
3355 -- Update Groups Table Data
3356 Update_Analysis_Opt_Count (
3357 p_Indicator => cd.indicator
3358 ,x_return_status => x_return_status
3359 ,x_msg_count => x_msg_count
3360 ,x_msg_data => x_msg_data
3361 );
3362 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3363 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3364 END IF;
3365 END LOOP;
3366 Update_Analysis_Option_UI(
3367 p_Indicator => p_Indicator
3368 ,p_Analysis_Group_Id => p_Analysis_Group_Id
3369 ,p_Option_Id => p_Option_Id
3370 ,p_Parent_Option_Id => p_Parent_Option_Id
3371 ,p_Grandparent_Option_Id => p_Grandparent_Option_Id
3372 ,p_Dependency_Flag => p_Dependency_Flag
3373 ,p_DataSet_Id => p_DataSet_Id
3374 ,p_DimSet_Id => p_DimSet_Id
3375 ,p_Default_Flag => p_Default_Flag
3376 ,p_Option_Name => p_Option_Name
3377 ,p_Option_Help => p_Option_Help
3378 ,p_Change_Dim_Set => p_Change_Dim_Set
3379 ,p_default_calculation=> p_default_calculation
3380 ,p_Create_Flow => FND_API.G_TRUE
3381 ,p_time_stamp => p_time_stamp
3382 ,p_olddim_Dataset_map => l_olddim_Dataset_map
3383 ,x_return_status => x_return_status
3384 ,x_msg_count => x_msg_count
3385 ,x_msg_data => x_msg_data
3386 );
3387 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3388 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3389 END IF;
3390
3391 IF FND_API.To_Boolean( p_commit ) THEN
3392 COMMIT;
3393 END IF;
3394 EXCEPTION
3395 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3396 ROLLBACK TO Create_Analayis_OptionObjPUB;
3397 IF (x_msg_data IS NULL) THEN
3398 FND_MSG_PUB.Count_And_Get
3399 ( p_encoded => FND_API.G_FALSE
3400 , p_count => x_msg_count
3401 , p_data => x_msg_data
3402 );
3403 END IF;
3404 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3405 WHEN OTHERS THEN
3406 ROLLBACK TO Create_Analayis_OptionObjPUB;
3407 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3408 IF (x_msg_data IS NOT NULL) THEN
3409 x_msg_data := x_msg_data||' -> BSC_OBJ_ANALYSIS_OPTIONS_PUB.Create_Analayis_Option ';
3410 ELSE
3411 x_msg_data := SQLERRM||' at BSC_OBJ_ANALYSIS_OPTIONS_PUB.Create_Analayis_Option ';
3412 END IF;
3413 END Create_Analysis_Option_UI;
3414
3415 /************************************************************************************
3416 -- API name : Val_Delete_Analysis_Option
3417 -- Type : Public
3418 -- Function :
3419 -- 1. Validates that there is atleast one analysis option in the objective
3420 -- 2. Validates that the parent analysis option is not deleted when there
3421 -- the child group is dependent and it has more than one analysis option
3422 -- 3. Validates that none of the kpis that will be deleted by this analysis
3423 -- option deletion have weight > 0
3424 ************************************************************************************/
3425
3426 PROCEDURE Val_Delete_Analysis_Option(
3427 p_Indicator IN NUMBER
3428 ,p_Analysis_Group_Id IN NUMBER := 0
3429 ,p_Option_Id IN NUMBER := 0
3430 ,p_Parent_Option_Id IN NUMBER := 0
3431 ,p_GrandParent_Option_Id IN NUMBER := 0
3432 ,x_return_status OUT NOCOPY VARCHAR2
3433 ,x_msg_count OUT NOCOPY NUMBER
3434 ,x_msg_data OUT NOCOPY VARCHAR2
3435 ) IS
3436
3437 l_Grp_Count NUMBER := 0;
3438 l_Next_Grp_Count NUMBER := 0;
3439 l_Total_Groups NUMBER := 0;
3440 l_Dependency01 bsc_kpi_analysis_groups.dependency_flag%TYPE := -1;
3441 l_Dependency12 bsc_kpi_analysis_groups.dependency_flag%TYPE := -1;
3442 l_IsDependent BOOLEAN := FALSE;
3443
3444 TYPE c_ref_cursor IS REF CURSOR;
3445 c_Weighted_Kpi c_ref_cursor;
3446 c_kpi_full_name c_ref_cursor;
3447 c_NumOptions c_ref_cursor;
3448 l_kpi_measure_id bsc_kpi_analysis_measures_b.kpi_measure_id%TYPE := NULL;
3449 l_kpi_full_name VARCHAR2(1024) := NULL;
3450 l_NumOptions NUMBER := 0;
3451 l_criteria VARCHAR2(2000);
3452 l_sql VARCHAR2(2000);
3453 l_Parent_Id bsc_kpi_analysis_options_b.parent_option_id%TYPE := 0;
3454 l_GrandParent_Id bsc_kpi_analysis_options_b.grandparent_option_id%TYPE := 0;
3455 l_Parent_Group_Id bsc_kpi_analysis_groups.analysis_group_id%TYPE := 0;
3456 l_GrandParent_Group_Id bsc_kpi_analysis_groups.analysis_group_id%TYPE := 0;
3457
3458 CURSOR c_Grp_Cnt(p_Ana_Grp NUMBER) IS
3459 SELECT
3460 num_of_options
3461 FROM
3462 bsc_kpi_analysis_groups
3463 WHERE
3464 indicator = p_Indicator AND
3465 Analysis_Group_Id = p_Ana_Grp;
3466
3467 CURSOR c_Num_Groups IS
3468 SELECT
3469 MAX(analysis_group_id)
3470 FROM
3471 bsc_kpi_analysis_groups
3472 WHERE
3473 indicator = p_Indicator;
3474 BEGIN
3475
3476 FND_MSG_PUB.Initialize;
3477
3478 OPEN c_Grp_Cnt(p_Analysis_Group_Id);
3479 FETCH c_Grp_Cnt INTO l_Grp_Count;
3480 CLOSE c_Grp_Cnt;
3481
3482 IF p_Analysis_Group_Id = 0 AND p_Option_Id = 0 AND l_Grp_Count = 1 THEN
3483 FND_MESSAGE.SET_NAME('BSC','BSC_D_AG_AT_LEAST_ONE_AO');
3484 FND_MSG_PUB.ADD;
3485 RAISE FND_API.G_EXC_ERROR;
3486 END IF;
3487
3488 l_Dependency01 := Get_Dependency(p_Indicator,1);
3489 l_Dependency12 := Get_Dependency(p_Indicator,2);
3490
3491 IF (p_Analysis_Group_Id = 1 AND l_Dependency01 = 1) THEN
3492 l_IsDependent := TRUE;
3493 ELSIF (p_Analysis_Group_Id = 2 AND (l_Dependency01 = 1 OR l_Dependency12 = 1 )) THEN
3494 l_IsDependent := TRUE;
3495 END IF;
3496
3497 OPEN c_Num_Groups;
3498 FETCH c_Num_Groups INTO l_Total_Groups;
3499 CLOSE c_Num_Groups;
3500
3501 IF p_Analysis_Group_Id < l_Total_Groups THEN
3502 OPEN c_Grp_Cnt(p_Analysis_Group_Id + 1);
3503 FETCH c_Grp_Cnt INTO l_Next_Grp_Count;
3504 CLOSE c_Grp_Cnt;
3505
3506 IF (l_Next_Grp_Count > 1 AND p_Option_Id = 0) THEN
3507 FND_MESSAGE.SET_NAME('BSC','BSC_D_NOT_DELETE_AG_DEPEN');
3508 FND_MSG_PUB.ADD;
3509 RAISE FND_API.G_EXC_ERROR;
3510 END IF;
3511 END IF;
3512
3513 Get_Parent_GrandParent_Ids(
3514 p_Indicator => p_Indicator
3515 ,p_Analysis_Group_Id => p_Analysis_Group_Id
3516 ,p_Parent_Id => p_Parent_Option_Id
3517 ,p_GrandParent_Id => p_GrandParent_Option_Id
3518 ,p_Independent_Par_Id => -1
3519 ,x_Parent_Id => l_Parent_Id
3520 ,x_GrandParent_Id => l_GrandParent_Id
3521 ,x_Parent_Group_Id => l_Parent_Group_Id
3522 ,x_GrandParent_Group_Id => l_GrandParent_Group_Id
3523 );
3524
3525 l_sql := 'SELECT COUNT(1) FROM bsc_kpi_analysis_options_b WHERE indicator = ' || p_Indicator ;
3526 l_sql := l_sql || ' AND analysis_group_id = ' || p_Analysis_Group_Id;
3527
3528 IF l_Parent_Group_Id <> -1 THEN
3529 l_sql := l_sql || ' AND parent_option_id = '|| l_Parent_Id;
3530 IF l_GrandParent_Group_Id <> -1 THEN
3531 l_sql := l_sql || ' AND grandparent_option_id= ' || l_GrandParent_Id;
3532 END IF;
3533 END IF;
3534
3535
3536 OPEN c_NumOptions FOR l_sql;
3537 FETCH c_NumOptions INTO l_NumOptions;
3538 CLOSE c_NumOptions;
3539
3540 IF l_NumOptions > 1 THEN
3541 l_criteria := ' WHERE indicator = '|| p_Indicator;
3542 IF p_Option_Id <> -1 THEN
3543 l_criteria := l_criteria || ' AND analysis_option'|| p_Analysis_Group_Id || ' = '|| p_Option_Id;
3544 END IF;
3545 IF l_Parent_Group_Id <> -1 THEN
3546 l_criteria := l_criteria || ' AND analysis_option'|| l_Parent_Group_Id || ' = '|| l_Parent_Id;
3547 IF l_GrandParent_Group_Id <> -1 THEN
3548 l_criteria := l_criteria || ' AND analysis_option'|| l_GrandParent_Group_Id || ' = '|| l_GrandParent_Id;
3549 END IF;
3550 END IF;
3551 l_sql := 'SELECT kpi_measure_id FROM bsc_kpi_measure_weights WHERE indicator = ' || p_Indicator;
3552 l_sql := l_sql || ' AND weight > 0 INTERSECT SELECT kpi_measure_id FROM bsc_kpi_analysis_measures_b ';
3553 l_sql := l_sql || l_criteria;
3554
3555 OPEN c_Weighted_Kpi FOR l_sql;
3556 FETCH c_Weighted_Kpi INTO l_kpi_measure_id;
3557 CLOSE c_Weighted_Kpi;
3558
3559 IF l_kpi_measure_id IS NOT NULL THEN
3560 l_sql := ' SELECT full_name FROM bsc_oaf_analysys_opt_comb_v '|| l_criteria;
3561 OPEN c_kpi_full_name FOR l_sql;
3562 FETCH c_kpi_full_name INTO l_kpi_full_name;
3563 CLOSE c_kpi_full_name;
3564
3565 IF l_kpi_full_name IS NOT NULL THEN
3566 FND_MESSAGE.SET_NAME('BSC','BSC_KPI_WEIGHT_ZERO ');
3567 FND_MESSAGE.SET_TOKEN('KPI_NAME', l_kpi_full_name);
3568 FND_MSG_PUB.ADD;
3569 RAISE FND_API.G_EXC_ERROR;
3570 END IF;
3571 END IF;
3572 END IF;
3573
3574 EXCEPTION
3575 WHEN FND_API.G_EXC_ERROR THEN
3576 IF (x_msg_data IS NULL) THEN
3577 FND_MSG_PUB.Count_And_Get
3578 ( p_encoded => FND_API.G_FALSE
3579 , p_count => x_msg_count
3580 , p_data => x_msg_data
3581 );
3582 END IF;
3583 x_return_status := FND_API.G_RET_STS_ERROR;
3584 WHEN OTHERS THEN
3585 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3586 IF (x_msg_data IS NOT NULL) THEN
3587 x_msg_data := x_msg_data||' -> BSC_OBJ_ANALYSIS_OPTIONS_PUB.Validate_Delete_Analysis_Option ';
3588 ELSE
3589 x_msg_data := SQLERRM||' at BSC_OBJ_ANALYSIS_OPTIONS_PUB.Validate_Delete_Analysis_Option ';
3590 END IF;
3591 END Val_Delete_Analysis_Option;
3592
3593 /************************************************************************************
3594 -- API name : Get_Next_Option_Id
3595 -- Type : Public
3596 ************************************************************************************/
3597
3598 PROCEDURE Get_Next_Option_Id (
3599 p_Indicator IN NUMBER
3600 ,p_Analysis_Group_Id IN NUMBER
3601 ,p_Parent_Option_Id IN NUMBER
3602 ,p_Grandparent_Option_Id IN NUMBER
3603 ,x_Option_Id OUT NOCOPY NUMBER
3604 ) IS
3605 l_Dependency01 bsc_kpi_analysis_groups.dependency_flag%TYPE;
3606 l_Dependency12 bsc_kpi_analysis_groups.dependency_flag%TYPE;
3607 l_Parent_Id bsc_kpi_analysis_options_b.parent_option_id%TYPE := 0;
3608 l_GrandParent_Id bsc_kpi_analysis_options_b.grandparent_option_id%TYPE := 0;
3609 l_Next_Option_Id bsc_kpi_analysis_options_b.option_id%TYPE := NULL;
3610 l_Parent_Group_Id bsc_kpi_analysis_groups.analysis_group_id%TYPE := 0;
3611 l_GrandParent_Group_Id bsc_kpi_analysis_groups.analysis_group_id%TYPE := 0;
3612
3613 CURSOR c_Next_Opt_Id(p_Parent NUMBER, p_GrandParent NUMBER) IS
3614 SELECT
3615 MAX(option_id) AS MAX
3616 FROM
3617 bsc_kpi_analysis_options_b
3618 WHERE
3619 indicator = p_Indicator
3620 AND analysis_group_id = p_Analysis_Group_Id
3621 AND parent_option_id = p_Parent
3622 AND grandparent_option_id = p_GrandParent
3623 GROUP BY
3624 indicator,analysis_group_id,parent_option_id,grandparent_option_id;
3625
3626 BEGIN
3627
3628 l_Dependency01 := Get_Dependency(p_Indicator,1);
3629 l_Dependency12 := Get_Dependency(p_Indicator,2);
3630 Get_Parent_GrandParent_Ids(
3631 p_Indicator => p_Indicator
3632 ,p_Analysis_Group_Id => p_Analysis_Group_Id
3633 ,p_Parent_Id => p_Parent_Option_Id
3634 ,p_GrandParent_Id => p_Grandparent_Option_Id
3635 ,p_Independent_Par_Id => 0
3636 ,x_Parent_Id => l_Parent_Id
3637 ,x_GrandParent_Id => l_GrandParent_Id
3638 ,x_Parent_Group_Id => l_Parent_Group_Id
3639 ,x_GrandParent_Group_Id => l_GrandParent_Group_Id
3640 );
3641
3642
3643 OPEN c_Next_Opt_Id(l_Parent_Id, l_GrandParent_Id);
3644 FETCH c_Next_Opt_Id INTO l_Next_Option_Id;
3645 CLOSE c_Next_Opt_Id;
3646
3647 IF l_Next_Option_Id IS NULL THEN
3648 l_Next_Option_Id := 0;
3649 ELSE
3650 l_Next_Option_Id := l_Next_Option_Id + 1;
3651 END IF;
3652
3653
3654 x_Option_Id := l_Next_Option_Id;
3655
3656 EXCEPTION
3657 WHEN OTHERS THEN
3658 x_Option_Id := NULL;
3659 END Get_Next_Option_Id;
3660
3661 /************************************************************************************
3662 -- API name : Get_DataSetId_For_AO_Comb
3663 -- Type : Public
3664 ************************************************************************************/
3665
3666 FUNCTION Get_DataSetId_For_AO_Comb (
3667 p_Indicator IN NUMBER
3668 ,p_Analayis_Group_Id IN NUMBER
3669 ,p_Option_Id IN NUMBER
3670 ,p_Parent_Option_Id IN NUMBER
3671 ,p_GrandParent_Option_Id IN NUMBER
3672 ) RETURN NUMBER IS
3673 CURSOR c_Indicator_Type IS
3674 SELECT
3675 indicator_type
3676 FROM
3677 bsc_kpis_b
3678 WHERE indicator = p_Indicator;
3679
3680 CURSOR c_dataset_id(p_AO0 NUMBER , p_AO1 NUMBER, p_AO2 NUMBER) IS
3681 SELECT
3682 dataset_id
3683 FROM
3684 bsc_kpi_analysis_measures_b
3685 WHERE
3686 indicator = p_Indicator AND
3687 analysis_option0 = p_AO0 AND
3688 analysis_option1 = p_AO1 AND
3689 analysis_option2 = p_AO2 AND
3690 series_id = 0;
3691
3692 l_Indicator_Type bsc_kpis_b.indicator_type%TYPE;
3693 l_Is_Leaf_Node VARCHAR2(1);
3694 l_Next_Group_Id NUMBER;
3695 l_AO0 bsc_kpi_analysis_measures_b.analysis_option0%TYPE := 0;
3696 l_AO1 bsc_kpi_analysis_measures_b.analysis_option1%TYPE := 0;
3697 l_AO2 bsc_kpi_analysis_measures_b.analysis_option2%TYPE := 0;
3698 l_dataset_id bsc_sys_datasets_b.dataset_id%TYPE;
3699 -- l_source bsc_sys_datasets_b.source%TYPE;
3700
3701 BEGIN
3702 OPEN c_Indicator_Type;
3703 FETCH c_Indicator_Type INTO l_Indicator_Type;
3704 CLOSE c_Indicator_Type;
3705
3706 --For MultiBar Series will always be defined in Update of Series
3707 IF l_Indicator_Type = 10THEN
3708 RETURN NULL;
3709 END IF;
3710
3711 CASE p_Analayis_Group_Id
3712 WHEN 0 THEN
3713 l_AO0 := p_Option_Id;
3714 WHEN 1 THEN
3715 l_AO1 := p_Option_Id;
3716 l_AO0 := p_Parent_Option_Id;
3717 WHEN 2 THEN
3718 l_AO2 := p_Option_Id;
3719 l_AO1 := p_Parent_Option_Id;
3720 l_AO0 := p_GrandParent_Option_Id;
3721 END CASE;
3722
3723 IF (p_Analayis_Group_Id = 0 OR p_Analayis_Group_Id = 1) THEN
3724 l_Next_Group_Id := p_Analayis_Group_Id + 1;
3725 IF (Is_Analayis_Option_Valid(p_Indicator, l_AO0, l_AO1, l_AO2, l_Next_Group_Id) = 'Y') THEN
3726 RETURN NULL;
3727 END IF;
3728 END IF;
3729
3730
3731 OPEN c_dataset_id(l_AO0, l_AO1, l_AO2) ;
3732 FETCH c_dataset_id INTO l_dataset_id;--,l_source;
3733 CLOSE c_dataset_id;
3734
3735
3736 RETURN l_dataset_id;
3737
3738 EXCEPTION
3739 WHEN OTHERS THEN
3740 RETURN NULL;
3741 END Get_DataSetId_For_AO_Comb;
3742
3743 /************************************************************************************
3744 -- API name : Check_Indicator_Plan
3745 -- Type : Private
3746 -- Function :
3747 --
3748 ************************************************************************************/
3749 FUNCTION Check_Indicator_Plan (
3750 p_Indicator IN NUMBER
3751 ) RETURN VARCHAR2 IS
3752 CURSOR c_Ind_BenchMark_Grp IS
3753 SELECT
3754 bk.bm_group_id
3755 FROM
3756 bsc_kpis_b bk, bsc_sys_bm_groups bg,bsc_sys_benchmarks_b be
3757 WHERE
3758 bk.bm_group_id = bg.bm_group_id AND
3759 be.bm_id = bg.bm_id AND
3760 be.data_type = 1 AND
3761 bk.indicator = p_Indicator;
3762
3763 BEGIN
3764 OPEN c_Ind_BenchMark_Grp;
3765 IF c_Ind_BenchMark_Grp%ROWCOUNT > 0 THEN
3766 RETURN FND_API.G_TRUE;
3767 END IF;
3768
3769 RETURN FND_API.G_FALSE;
3770 EXCEPTION
3771 WHEN OTHERS THEN
3772 RETURN FND_API.G_FALSE;
3773 END Check_Indicator_Plan;
3774
3775 /************************************************************************************
3776 -- API name : Check_Series_Default_Plan
3777 -- Type : Private
3778 -- Function :
3779 --
3780 ************************************************************************************/
3781 FUNCTION Check_Series_Default_Plan (
3782 p_Indicator IN NUMBER
3783 ) RETURN VARCHAR2 IS
3784 l_AO0_Default bsc_kpi_analysis_groups.default_value%TYPE;
3785 l_AO1_Default bsc_kpi_analysis_groups.default_value%TYPE;
3786 l_AO2_Default bsc_kpi_analysis_groups.default_value%TYPE;
3787 CURSOR c_Series_Default_Plan(p_AO0 NUMBER, p_AO1 NUMBER, p_AO2 NUMBER) IS
3788 SELECT
3789 COUNT(1)
3790 FROM
3791 bsc_kpi_analysis_measures_b
3792 WHERE
3793 indicator = p_indicator AND
3794 analysis_option0 = p_AO0 AND
3795 analysis_option1 = p_AO1 AND
3796 analysis_option2 = p_AO2 AND
3797 default_value = 1 AND
3798 budget_flag = 1;
3799
3800 BEGIN
3801 SELECT
3802 a0_default,a1_default,a2_default
3803 INTO
3804 l_AO0_Default, l_AO1_Default, l_AO2_Default
3805 FROM
3806 bsc_db_color_ao_defaults_v
3807 WHERE
3808 indicator = p_Indicator;
3809
3810 OPEN c_Series_Default_Plan(l_AO0_Default,l_AO1_Default,l_AO2_Default);
3811 IF c_Series_Default_Plan%ROWCOUNT > 0 THEN
3812 RETURN FND_API.G_TRUE;
3813 END IF;
3814
3815 RETURN FND_API.G_FALSE;
3816 EXCEPTION
3817 WHEN OTHERS THEN
3818 RETURN FND_API.G_FALSE;
3819 END Check_Series_Default_Plan;
3820
3821 /************************************************************************************
3822 -- API name : Set_Apply_Color
3823 -- Type : Private
3824 -- Function : Checks whether the indicator has plan defined or not
3825 -- Also checks whether series default has plan defined in
3826 -- case of multibar indicator. This will mark a color
3827 -- change for the indicator
3828 ************************************************************************************/
3829 PROCEDURE Set_Apply_Color (
3830 p_commit IN VARCHAR2 := FND_API.G_FALSE
3831 ,p_Indicator IN NUMBER
3832 ,x_return_status OUT NOCOPY VARCHAR2
3833 ,x_msg_count OUT NOCOPY NUMBER
3834 ,x_msg_data OUT NOCOPY VARCHAR2
3835 ) IS
3836 CURSOR c_Kpi_Info IS
3837 SELECT
3838 indicator_type,config_type
3839 FROM
3840 bsc_kpis_b
3841 WHERE
3842 indicator = p_Indicator;
3843
3844 CURSOR c_All_KPIs IS
3845 SELECT
3846 indicator
3847 FROM
3848 bsc_kpis_b
3849 WHERE
3850 indicator = p_Indicator OR
3851 (source_indicator = p_Indicator AND prototype_flag <> 2);
3852 l_indicator_type bsc_kpis_b.indicator_type%TYPE;
3853 l_config_type bsc_kpis_b.config_type%TYPE;
3854 l_apply_color bsc_kpis_b.apply_color_flag%TYPE;
3855 BEGIN
3856 SAVEPOINT Set_Apply_Color_PVT;
3857 x_return_status := FND_API.G_RET_STS_SUCCESS;
3858 FND_MSG_PUB.Initialize;
3859
3860 -- As of now there is place where plan benchmark can be disabled
3861 -- So this may always return true
3862 IF FND_API.To_Boolean(Check_Indicator_Plan(p_Indicator)) THEN
3863 OPEN c_Kpi_Info;
3864 FETCH c_Kpi_Info INTO l_indicator_type,l_config_type;
3865 CLOSE c_Kpi_Info;
3866 l_apply_color := 1;
3867 IF l_indicator_type = 10 THEN
3868 IF NOT FND_API.TO_Boolean(Check_Series_Default_Plan(p_Indicator)) THEN
3869 l_apply_color := 0;
3870 END IF;
3871 ELSIF l_config_type = 7 THEN
3872 /*Not Needed For Now*/
3873 NULL;
3874 END IF;
3875 END IF;
3876 FOR cd in c_All_KPIs LOOP
3877 UPDATE bsc_kpis_b
3878 SET apply_color_flag = l_apply_color
3879 WHERE indicator = cd.indicator;
3880
3881 BSC_DESIGNER_PVT.ActionFlag_Change (
3882 x_indicator => cd.indicator
3883 ,x_newflag => BSC_DESIGNER_PVT.G_ActionFlag.GAA_Color
3884 );
3885 END LOOP;
3886
3887 IF FND_API.To_Boolean( p_commit ) THEN
3888 COMMIT;
3889 END IF;
3890 EXCEPTION
3891 WHEN OTHERS THEN
3892 ROLLBACK TO Set_Apply_Color_PVT;
3893 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3894 IF (x_msg_data IS NOT NULL) THEN
3895 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Set_Apply_Color';
3896 ELSE
3897 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Set_Apply_Color ';
3898 END IF;
3899 END Set_Apply_Color;
3900
3901 /************************************************************************************
3902 -- API name : Get_Analysis_Option_Name
3903 -- Type : Public
3904 -- Function : This API is used in HGrid VO Queries
3905 ************************************************************************************/
3906
3907 FUNCTION Get_Analysis_Option_Name(
3908 p_Indicator NUMBER,
3909 p_Analysis_Option0 NUMBER,
3910 p_Analysis_Option1 NUMBER,
3911 p_Analysis_Option2 NUMBER,
3912 p_Group_Id NUMBER
3913 ) RETURN VARCHAR2
3914 IS
3915 l_Name bsc_kpi_analysis_options_vl.Name%TYPE;
3916 l_Count NUMBER := 0;
3917 l_Dependency01 bsc_kpi_analysis_groups.dependency_flag%TYPE := 0;
3918 l_Dependency12 bsc_kpi_analysis_groups.dependency_flag%TYPE := 0;
3919 l_option_id NUMBER := 0;
3920 l_parent_id NUMBER := 0;
3921 l_grandparent_id NUMBER := 0;
3922
3923 CURSOR c_dependency_flag(p_group_id NUMBER) IS
3924 SELECT
3925 ag.dependency_flag
3926 FROM
3927 bsc_kpi_analysis_groups ag
3928 WHERe
3929 ag.indicator = p_Indicator AND
3930 ag.analysis_group_id = p_group_id;
3931
3932 CURSOR c_Name(l_option_id NUMBER,l_parent_id NUMBER, l_grandParentId NUMBER) IS
3933 SELECT
3934 name
3935 FROM
3936 bsc_kpi_analysis_options_vl o
3937 WHERE
3938 o.indicator = p_Indicator AND
3939 o.analysis_group_id = p_Group_Id AND
3940 o.option_id = l_option_id AND
3941 o.parent_option_id = l_parent_id AND
3942 o.grandparent_option_id = l_grandParentId;
3943
3944 BEGIN
3945
3946 OPEN c_dependency_flag(1);
3947 FETCH c_dependency_flag INTO l_Dependency01;
3948 CLOSE c_dependency_flag;
3949
3950 OPEN c_dependency_flag(2);
3951 FETCH c_dependency_flag INTO l_Dependency12;
3952 CLOSE c_dependency_flag;
3953
3954 CASE p_Group_Id
3955 WHEN 0 THEN
3956 l_option_id := p_Analysis_Option0;
3957 WHEN 1 THEN
3958 l_option_id := p_Analysis_Option1;
3959 IF l_Dependency01 = 1 THEN
3960 l_parent_id := p_Analysis_Option0;
3961 END IF;
3962 WHEN 2 THEN
3963 l_option_id := p_Analysis_Option2;
3964 IF l_Dependency12 = 1 THEN
3965 l_parent_id := p_Analysis_Option1;
3966 IF l_Dependency01 = 1 THEN
3967 l_grandparent_id := p_Analysis_Option0;
3968 END IF;
3969 END IF;
3970 END CASE;
3971
3972 OPEN c_Name(l_Option_Id, l_parent_id, l_grandparent_id) ;
3973 FETCH c_Name INTO l_Name;
3974 CLOSE c_Name;
3975
3976 RETURN l_Name;
3977 EXCEPTION
3978 WHEN OTHERS THEN
3979 RETURN NULL;
3980 END Get_Analysis_Option_Name;
3981
3982 /************************************************************************************
3983 -- API name : Is_Analayis_Option_Valid
3984 -- Type : Public
3985 -- Function : This API is used in HGrid VO Queries
3986 ************************************************************************************/
3987
3988 FUNCTION Is_Analayis_Option_Valid(
3989 p_Indicator NUMBER,
3990 p_Analysis_Option0 NUMBER,
3991 p_Analysis_Option1 NUMBER,
3992 p_Analysis_Option2 NUMBER,
3993 p_Group_Id NUMBER
3994 ) RETURN VARCHAR2
3995 IS
3996 l_Count NUMBER := 0;
3997 l_Dependency01 bsc_kpi_analysis_groups.dependency_flag%TYPE := 0;
3998 l_Dependency12 bsc_kpi_analysis_groups.dependency_flag%TYPE := 0;
3999 l_option_id NUMBER := 0;
4000 l_parent_id NUMBER := 0;
4001 l_grandparent_id NUMBER := 0;
4002
4003 CURSOR c_dependency_flag(p_group_id NUMBER) IS
4004 SELECT
4005 ag.dependency_flag
4006 FROM
4007 bsc_kpi_analysis_groups ag
4008 WHERe
4009 ag.indicator = p_Indicator AND
4010 ag.analysis_group_id = p_group_id;
4011
4012 CURSOR c_AO_Count(l_option_id NUMBER,l_parent_id NUMBER, l_grandParentId NUMBER) IS
4013 SELECT
4014 COUNT(1)
4015 FROM
4016 bsc_kpi_analysis_options_vl o
4017 WHERE
4018 o.indicator = p_Indicator AND
4019 o.analysis_group_id = p_Group_Id AND
4020 o.option_id = l_option_id AND
4021 o.parent_option_id = l_parent_id AND
4022 o.grandparent_option_id = l_grandParentId;
4023
4024 BEGIN
4025
4026 OPEN c_dependency_flag(1);
4027 FETCH c_dependency_flag INTO l_Dependency01;
4028 CLOSE c_dependency_flag;
4029
4030 OPEN c_dependency_flag(2);
4031 FETCH c_dependency_flag INTO l_Dependency12;
4032 CLOSE c_dependency_flag;
4033
4034 CASE p_Group_Id
4035 WHEN 0 THEN
4036 l_option_id := p_Analysis_Option0;
4037 WHEN 1 THEN
4038 l_option_id := p_Analysis_Option1;
4039 IF l_Dependency01 = 1 THEN
4040 l_parent_id := p_Analysis_Option0;
4041 END IF;
4042 WHEN 2 THEN
4043 l_option_id := p_Analysis_Option2;
4044 IF l_Dependency12 = 1 THEN
4045 l_parent_id := p_Analysis_Option1;
4046 IF l_Dependency01 = 1 THEN
4047 l_grandparent_id := p_Analysis_Option0;
4048 END IF;
4049 END IF;
4050 END CASE;
4051
4052 OPEN c_AO_Count(l_Option_Id, l_parent_id, l_grandparent_id) ;
4053 FETCH c_AO_Count INTO l_Count;
4054 CLOSE c_AO_Count;
4055
4056 IF l_Count > 0 THEN
4057 RETURN 'Y';
4058 ELSE
4059 RETURN 'N';
4060 END IF;
4061 EXCEPTION
4062 WHEN OTHERS THEN
4063 RETURN 'Y';
4064 END Is_Analayis_Option_Valid;
4065
4066 /************************************************************************************
4067 -- API name : Get_Parent_Id
4068 -- Type : Public
4069 -- Function : This API is used in HGrid VO Queries
4070 ************************************************************************************/
4071
4072 FUNCTION Get_Parent_Id (
4073 p_Indicator NUMBER,
4074 p_Analysis_GroupId NUMBER,
4075 p_Option_Id NUMBER,
4076 p_Parent_Id NUMBER
4077 )RETURN NUMBER IS
4078 l_dependency NUMBER;
4079 BEGIN
4080 IF p_Analysis_GroupId <> 0 THEN
4081 l_dependency := Get_Dependency(p_Indicator,p_Analysis_GroupId);
4082 IF l_dependency = 0 THEN
4083 RETURN 0;
4084 ELSE
4085 RETURN p_Parent_Id;
4086 END IF;
4087 END IF;
4088 RETURN 0;
4089 EXCEPTION
4090 WHEN OTHERS THEN
4091 RETURN 0;
4092 END Get_Parent_Id;
4093
4094 /************************************************************************************
4095 -- API name : Get_Grand_Parent_Id
4096 -- Type : Public
4097 -- Function : This API is used in HGrid VO queries
4098 ************************************************************************************/
4099
4100 FUNCTION Get_Grand_Parent_Id (
4101 p_Indicator NUMBER,
4102 p_Analysis_GroupId NUMBER,
4103 p_Option_Id NUMBER,
4104 p_GrandParent_Id NUMBER
4105 )RETURN NUMBER IS
4106 l_dependency01 NUMBER;
4107 l_dependency12 NUMBER;
4108
4109 BEGIN
4110 IF p_Analysis_GroupId = 2 THEN
4111 l_dependency01 := Get_Dependency(p_Indicator, 1);
4112 l_dependency12 := Get_Dependency(p_Indicator, 2);
4113 IF l_dependency01 = 1 AND l_dependency12 = 1 THEN
4114 RETURN p_GrandParent_Id;
4115 END IF;
4116 END IF;
4117
4118 RETURN 0;
4119
4120 EXCEPTION
4121 WHEN OTHERS THEN
4122 RETURN 0;
4123 END Get_Grand_Parent_Id;
4124
4125
4126 /************************************************************************************
4127 -- API name : Get_Dim_Set_Id
4128 -- Type : Private
4129 -- Function : Returns the dimension set that will be used for a given
4130 -- analysis option combination
4131 -- If there is no dimension set associated at a particular level then it
4132 -- will return null (In which case zeroeth dimension set will be taken into
4133 -- consideration by IViewer as well as GDB
4134 ************************************************************************************/
4135
4136 FUNCTION Get_Dim_Set_Id(
4137 p_Indicator IN NUMBER
4138 ,p_Analysis_Option0 IN NUMBER := 0
4139 ,p_Analysis_Option1 IN NUMBER := 0
4140 ,p_Analysis_Option2 IN NUMBER := 0
4141 ,p_Dim_Set_Group IN NUMBER := 0
4142 ) RETURN NUMBER IS
4143
4144 l_DimSet_Id bsc_kpi_analysis_options_b.dim_set_id%TYPE := NULL;
4145 l_Option_Id bsc_kpi_analysis_options_b.option_id%TYPE := 0;
4146 l_Parent_Id bsc_kpi_analysis_options_b.option_id%TYPE := 0;
4147 l_GrandParent_Id bsc_kpi_analysis_options_b.option_id%TYPE := 0;
4148
4149 CURSOR c_Dim_Set_ID(p_Analysis_Group_Id NUMBER,
4150 p_Option_Id NUMBER, p_Parent_Id NUMBER, p_GrandParentId NUMBER) IS
4151 SELECT
4152 dim_set_id
4153 FROM
4154 bsc_kpi_analysis_options_b
4155 WHERE
4156 indicator = p_Indicator AND
4157 analysis_group_id = p_Analysis_Group_Id AND
4158 option_id = p_Option_Id AND
4159 parent_option_id = p_Parent_Id AND
4160 grandparent_option_id = p_GrandParentId;
4161
4162 BEGIN
4163
4164 CASE p_Dim_Set_Group
4165 WHEN 0 THEN
4166 l_Option_Id := p_Analysis_Option0;
4167 WHEN 1 THEN
4168 l_Option_Id := p_Analysis_Option1;
4169 l_Parent_Id := p_Analysis_Option0;
4170 WHEN 2 THEN
4171 l_Option_Id := p_Analysis_Option2;
4172 l_Parent_Id := p_Analysis_Option1;
4173 l_GrandParent_Id := p_Analysis_Option0;
4174 END CASE;
4175
4176 OPEN c_Dim_Set_ID(p_Dim_Set_Group, l_Option_Id, l_Parent_Id, l_GrandParent_Id);
4177 FETCH c_Dim_Set_ID INTO l_DimSet_Id;
4178 CLOSE c_Dim_Set_ID;
4179
4180 IF l_DimSet_Id IS NULL THEN
4181 l_DimSet_Id := Get_Dim_Set_Id (
4182 p_Indicator => p_Indicator
4183 ,p_Analysis_Option0 => p_Analysis_Option0
4184 ,p_Analysis_Option1 => p_Analysis_Option1
4185 ,p_Analysis_Option2 => p_Analysis_Option2
4186 ,p_Dim_Set_Group => p_Dim_Set_Group - 1
4187 );
4188 END IF;
4189 RETURN l_DimSet_Id;
4190 EXCEPTION
4191 WHEN OTHERS THEN
4192 RETURN 0;
4193 END Get_Dim_Set_Id ;
4194
4195 /************************************************************************************
4196 -- API name : Get_Kpi_Property
4197 -- Type : Public
4198 ************************************************************************************/
4199
4200 FUNCTION Get_Kpi_Property (
4201 p_Indicator IN NUMBER
4202 ,p_Analayis_Group_Id IN NUMBER
4203 ,p_Option_Id IN NUMBER
4204 ,p_Parent_Option_Id IN NUMBER
4205 ,p_GrandParent_Option_Id IN NUMBER
4206 ,p_Property_Name IN VARCHAR2
4207 ) RETURN NUMBER IS
4208 CURSOR c_Indicator_Type IS
4209 SELECT
4210 indicator_type
4211 FROM
4212 bsc_kpis_b
4213 WHERE indicator = p_Indicator;
4214
4215 CURSOR c_kpi_measure_id(p_AO0 NUMBER , p_AO1 NUMBER, p_AO2 NUMBER) IS
4216 SELECT
4217 kpi_measure_id
4218 FROM
4219 bsc_kpi_analysis_measures_b
4220 WHERE
4221 indicator = p_Indicator AND
4222 analysis_option0 = p_AO0 AND
4223 analysis_option1 = p_AO1 AND
4224 analysis_option2 = p_AO2 AND
4225 series_id = 0;
4226
4227 l_Indicator_Type bsc_kpis_b.indicator_type%TYPE;
4228 l_Next_Group_Id NUMBER;
4229 l_AO0 bsc_kpi_analysis_measures_b.analysis_option0%TYPE := 0;
4230 l_AO1 bsc_kpi_analysis_measures_b.analysis_option1%TYPE := 0;
4231 l_AO2 bsc_kpi_analysis_measures_b.analysis_option2%TYPE := 0;
4232 l_kpi_measure_id bsc_sys_datasets_b.dataset_id%TYPE;
4233 l_Property_Value NUMBER;
4234
4235 BEGIN
4236 OPEN c_Indicator_Type;
4237 FETCH c_Indicator_Type INTO l_Indicator_Type;
4238 CLOSE c_Indicator_Type;
4239
4240 --For MultiBar Series will always be defined in Update of Series
4241 IF l_Indicator_Type = 10 THEN
4242 RETURN NULL;
4243 END IF;
4244
4245 CASE p_Analayis_Group_Id
4246 WHEN 0 THEN
4247 l_AO0 := p_Option_Id;
4248 WHEN 1 THEN
4249 l_AO1 := p_Option_Id;
4250 l_AO0 := p_Parent_Option_Id;
4251 WHEN 2 THEN
4252 l_AO2 := p_Option_Id;
4253 l_AO1 := p_Parent_Option_Id;
4254 l_AO0 := p_GrandParent_Option_Id;
4255 END CASE;
4256
4257 IF (p_Analayis_Group_Id = 0 OR p_Analayis_Group_Id = 1) THEN
4258 l_Next_Group_Id := p_Analayis_Group_Id + 1;
4259 IF (Is_Analayis_Option_Valid(p_Indicator, l_AO0, l_AO1, l_AO2, l_Next_Group_Id) = 'Y') THEN
4260 RETURN NULL;
4261 END IF;
4262 END IF;
4263
4264
4265 OPEN c_kpi_measure_id(l_AO0, l_AO1, l_AO2) ;
4266 FETCH c_kpi_measure_id INTO l_kpi_measure_id;
4267 CLOSE c_kpi_measure_id;
4268
4269 IF l_kpi_measure_id IS NOT NULL THEN
4270 IF p_Property_Name = 'DATASET_ID' THEN
4271 SELECT
4272 dataset_id
4273 INTO
4274 l_Property_Value
4275 FROM
4276 bsc_kpi_analysis_measures_b
4277 WHERE
4278 indicator = p_Indicator
4279 AND kpi_measure_id = l_kpi_measure_id;
4280 ELSIF p_Property_Name = 'DEFAULT_CALCULATION' THEN
4281 SELECT
4282 default_calculation
4283 INTO
4284 l_Property_Value
4285 FROM
4286 bsc_kpi_measure_props
4287 WHERE
4288 indicator = p_Indicator
4289 AND kpi_measure_id = l_kpi_measure_id;
4290 END IF;
4291 END IF;
4292
4293 RETURN l_Property_Value;
4294
4295 EXCEPTION
4296 WHEN OTHERS THEN
4297 RETURN NULL;
4298 END Get_Kpi_Property;
4299
4300 END BSC_OBJ_ANALYSIS_OPTIONS_PUB;