[Home] [Help]
PACKAGE BODY: APPS.BSC_KPI_SERIES_PUB
Source
1 PACKAGE BODY BSC_KPI_SERIES_PUB as
2 /* $Header: BSCPSERB.pls 120.1.12000000.2 2007/07/27 10:04:25 akoduri noship $ */
3
4
5 /************************************************************************************
6 -- API name : Check_Color_Props
7 -- Type : Public
8 -- Sets the disable_color flag of bsc_kpi_measure_props depending on the
9 -- following conditions
10 -- 1. apply_color_flag will be set to FALSE if Plan is disabled
11 -- 2. disable_color will be set to FALSE if the color method is default KPI
12 -- based and disable_color was TRUE earlier (The current series should have
13 -- p_Default_Flag set to 1)
14 ************************************************************************************/
15 PROCEDURE Check_Color_Props(
16 p_commit IN VARCHAR2 := FND_API.G_FALSE
17 ,p_Indicator IN NUMBER
18 ,p_Analysis_Option0 IN NUMBER
19 ,p_Analysis_Option1 IN NUMBER
20 ,p_Analysis_Option2 IN NUMBER
21 ,p_Series_Id IN NUMBER
22 ,p_Budget_Flag IN NUMBER := 0
23 ,p_Default_Flag IN NUMBER := 0
24 ,p_Dataset_Id IN NUMBER := -1
25 ,x_return_status OUT NOCOPY VARCHAR2
26 ,x_msg_count OUT NOCOPY NUMBER
27 ,x_msg_data OUT NOCOPY VARCHAR2
28 ) IS
29
30 l_kpi_measure_props_rec bsc_kpi_measure_props_pub.kpi_measure_props_rec;
31 l_kpi_measure_id bsc_kpi_analysis_measures_b.kpi_measure_id%TYPE;
32 l_A0_Def bsc_kpi_analysis_measures_b.analysis_option0%TYPE;
33 l_A1_Def bsc_kpi_analysis_measures_b.analysis_option0%TYPE;
34 l_A2_Def bsc_kpi_analysis_measures_b.analysis_option0%TYPE;
35 l_Source bsc_sys_datasets_vl.source%TYPE := 'BSC';
36
37 CURSOR c_kpi_measure_id IS
38 SELECT
39 km.kpi_measure_id
40 FROM
41 bsc_kpi_analysis_measures_b km
42 WHERE km.indicator = p_Indicator AND
43 km.analysis_option0 = p_Analysis_Option0 AND
44 km.analysis_option1 = p_Analysis_Option1 AND
45 km.analysis_option2 = p_Analysis_Option2 AND
46 km.series_id = p_Series_Id ;
47
48 CURSOR c_Default_AO_Comb IS
49 SELECT
50 a0_default, a1_default, a2_default
51 FROM
52 bsc_db_color_ao_defaults_v
53 WHERE
54 indicator = p_Indicator;
55
56 CURSOR c_Source IS
57 SELECT
58 source
59 FROM
60 bsc_sys_datasets_vl
61 WHERE
62 dataset_id = p_Dataset_Id;
63
64 BEGIN
65
66 SAVEPOINT Check_Color_Props_PUB;
67 x_return_status := FND_API.G_RET_STS_SUCCESS;
68 FND_MSG_PUB.Initialize;
69
70 OPEN c_kpi_measure_id;
71 FETCH c_kpi_measure_id INTO l_kpi_measure_id;
72 CLOSE c_kpi_measure_id;
73
74 BSC_KPI_MEASURE_PROPS_PUB.Retrieve_Kpi_Measure_Props (
75 p_objective_id => p_Indicator
76 , p_kpi_measure_id => l_kpi_measure_id
77 , x_kpi_measure_rec => l_kpi_measure_props_rec
78 , x_return_status => x_return_status
79 , x_msg_count => x_msg_count
80 , x_msg_data => x_msg_data
81 ) ;
82 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
83 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
84 END IF;
85
86 OPEN c_Default_AO_Comb;
87 FETCH c_Default_AO_Comb INTO l_A0_Def, l_A1_Def, l_A2_Def;
88 CLOSE c_Default_AO_Comb;
89
90 OPEN c_Source;
91 FETCH c_Source INTO l_Source;
92 CLOSE c_Source;
93
94 IF p_Budget_Flag = 0 THEN
95 l_kpi_measure_props_rec.apply_color_flag := 0;
96 ELSE
97 l_kpi_measure_props_rec.apply_color_flag := 1;
98 END IF;
99
100 IF (l_A0_Def = p_Analysis_Option0 AND l_A1_Def = p_Analysis_Option1
101 AND l_A2_Def = p_Analysis_Option2 AND p_Default_Flag = 1
102 AND l_kpi_measure_props_rec.disable_color = 'T' AND l_Source <> 'PMF') THEN
103 l_kpi_measure_props_rec.disable_color := 'F';
104 END IF;
105
106 BSC_KPI_MEASURE_PROPS_PUB.Update_Kpi_Measure_Props (
107 p_Commit => FND_API.G_FALSE
108 , p_kpi_measure_rec => l_kpi_measure_props_rec
109 , p_cascade_shared => FALSE
110 , x_return_status => x_return_status
111 , x_msg_count => x_msg_count
112 , x_msg_data => x_msg_data
113 ) ;
114 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
115 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
116 END IF;
117
118 IF FND_API.To_Boolean(p_Commit) THEN
119 COMMIT;
120 END IF;
121
122 EXCEPTION
123 WHEN FND_API.G_EXC_ERROR THEN
124 ROLLBACK TO Check_Color_Props_PUB;
125 IF (x_msg_data IS NULL) THEN
126 FND_MSG_PUB.Count_And_Get
127 ( p_encoded => FND_API.G_FALSE
128 , p_count => x_msg_count
129 , p_data => x_msg_data
130 );
131 END IF;
132 x_return_status := FND_API.G_RET_STS_ERROR;
133 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
134 ROLLBACK TO Check_Color_Props_PUB;
135 IF (x_msg_data IS NULL) THEN
136 FND_MSG_PUB.Count_And_Get
137 ( p_encoded => FND_API.G_FALSE
138 , p_count => x_msg_count
139 , p_data => x_msg_data
140 );
141 END IF;
142 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
143 WHEN NO_DATA_FOUND THEN
144 ROLLBACK TO Check_Color_Props_PUB;
145 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
146 IF (x_msg_data IS NOT NULL) THEN
147 x_msg_data := x_msg_data||' ->BSC_KPI_SERIES_PUB.Check_Color_Props ';
148 ELSE
149 x_msg_data := SQLERRM||'BSC_KPI_SERIES_PUB.Check_Color_Props ';
150 END IF;
151 WHEN OTHERS THEN
152 ROLLBACK TO Check_Color_Props_PUB;
153 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
154 IF (x_msg_data IS NOT NULL) THEN
155 x_msg_data := x_msg_data||' ->BSC_KPI_SERIES_PUB.Check_Color_Props ';
156 ELSE
157 x_msg_data := SQLERRM||' at BSC_KPI_SERIES_PUB.Check_Color_Props ';
158 END IF;
159 END Check_Color_Props;
160
161 /************************************************************************************
162 -- API name : Save_Default_Calculation
163 -- Type : Public
164 -- Sets the default calculation at the kpi level
165 -- populates the default_calculation of bsc_kpi_measure_props
166 ************************************************************************************/
167 PROCEDURE Save_Default_Calculation(
168 p_commit IN VARCHAR2 := FND_API.G_FALSE
169 ,p_Indicator IN NUMBER
170 ,p_Analysis_Option0 IN NUMBER
171 ,p_Analysis_Option1 IN NUMBER
172 ,p_Analysis_Option2 IN NUMBER
173 ,p_Series_Id IN NUMBER
174 ,p_default_calculation IN NUMBER := NULL
175 ,p_casacade_shared IN VARCHAR2 := FND_API.G_TRUE
176 ,x_return_status OUT NOCOPY VARCHAR2
177 ,x_msg_count OUT NOCOPY NUMBER
178 ,x_msg_data OUT NOCOPY VARCHAR2
179 ) IS
180
181 l_kpi_measure_props_rec bsc_kpi_measure_props_pub.kpi_measure_props_rec;
182 l_kpi_measure_id bsc_kpi_analysis_measures_b.kpi_measure_id%TYPE;
183 l_color_rollup_type bsc_kpis_b.color_rollup_type%TYPE;
184
185 CURSOR c_kpi_measure_id IS
186 SELECT
187 km.kpi_measure_id
188 FROM
189 bsc_kpi_analysis_measures_b km
190 WHERE km.indicator = p_Indicator AND
191 km.analysis_option0 = p_Analysis_Option0 AND
192 km.analysis_option1 = p_Analysis_Option1 AND
193 km.analysis_option2 = p_Analysis_Option2 AND
194 km.series_id = p_Series_Id ;
195
196 CURSOR c_shared_objs IS
197 SELECT
198 indicator
199 FROM
200 bsc_kpis_b
201 WHERE
202 source_indicator = p_Indicator AND
203 prototype_flag <> 2 AND
204 share_flag = 2;
205
206 BEGIN
207
208 SAVEPOINT Save_Default_Calculation_PUB;
209 x_return_status := FND_API.G_RET_STS_SUCCESS;
210 FND_MSG_PUB.Initialize;
211
212 OPEN c_kpi_measure_id;
213 FETCH c_kpi_measure_id INTO l_kpi_measure_id;
214 CLOSE c_kpi_measure_id;
215
216 BSC_KPI_MEASURE_PROPS_PUB.Retrieve_Kpi_Measure_Props (
217 p_objective_id => p_Indicator
218 , p_kpi_measure_id => l_kpi_measure_id
219 , x_kpi_measure_rec => l_kpi_measure_props_rec
220 , x_return_status => x_return_status
221 , x_msg_count => x_msg_count
222 , x_msg_data => x_msg_data
223 ) ;
224 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
225 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
226 END IF;
227
228 IF NOT BSC_COPY_INDICATOR_PUB.Is_Numeric_Field_Equal(l_kpi_measure_props_rec.default_calculation, p_default_calculation) THEN
229 l_kpi_measure_props_rec.default_calculation := p_default_calculation;
230
231 BSC_KPI_MEASURE_PROPS_PUB.Update_Kpi_Measure_Props (
232 p_Commit => FND_API.G_FALSE
233 , p_kpi_measure_rec => l_kpi_measure_props_rec
234 , p_cascade_shared => FALSE
235 , x_return_status => x_return_status
236 , x_msg_count => x_msg_count
237 , x_msg_data => x_msg_data
238 ) ;
239 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
240 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
241 END IF;
242
243 l_color_rollup_type := BSC_COLOR_CALC_UTIL.Get_Obj_Color_Rollup_Type(p_Indicator);
244 IF l_color_rollup_type <> BSC_COLOR_CALC_UTIL.DEFAULT_KPI OR
245 (l_color_rollup_type = BSC_COLOR_CALC_UTIL.DEFAULT_KPI AND
246 BSC_COLOR_CALC_UTIL.Get_Default_Kpi_Measure_Id(p_Indicator) = l_kpi_measure_id)THEN
247 BSC_DESIGNER_PVT.ActionFlag_Change (
248 x_indicator => p_Indicator
249 ,x_newflag => BSC_DESIGNER_PVT.G_ActionFlag.GAA_Color
250 );
251 END IF;
252
253 IF p_casacade_shared = FND_API.G_TRUE THEN
254 FOR cd IN c_shared_objs LOOP
255 Save_Default_Calculation(
256 p_commit => FND_API.G_FALSE
257 ,p_Indicator => cd.Indicator
258 ,p_Analysis_Option0 => p_Analysis_Option0
259 ,p_Analysis_Option1 => p_Analysis_Option1
260 ,p_Analysis_Option2 => p_Analysis_Option2
261 ,p_Series_Id => p_Series_Id
262 ,p_default_calculation => p_default_calculation
263 ,p_casacade_shared => FND_API.G_FALSE
264 ,x_return_status => x_return_status
265 ,x_msg_count => x_msg_count
266 ,x_msg_data => x_msg_data
267 );
268 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
269 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
270 END IF;
271
272 END LOOP;
273 END IF;
274
275 IF FND_API.To_Boolean(p_Commit) THEN
276 COMMIT;
277 END IF;
278 END IF;
279 EXCEPTION
280 WHEN FND_API.G_EXC_ERROR THEN
281 ROLLBACK TO Save_Default_Calculation_PUB;
282 IF (x_msg_data IS NULL) THEN
283 FND_MSG_PUB.Count_And_Get
284 ( p_encoded => FND_API.G_FALSE
285 , p_count => x_msg_count
286 , p_data => x_msg_data
287 );
288 END IF;
289 x_return_status := FND_API.G_RET_STS_ERROR;
290 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
291 ROLLBACK TO Save_Default_Calculation_PUB;
292 IF (x_msg_data IS NULL) THEN
293 FND_MSG_PUB.Count_And_Get
294 ( p_encoded => FND_API.G_FALSE
295 , p_count => x_msg_count
296 , p_data => x_msg_data
297 );
298 END IF;
299 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
300 WHEN NO_DATA_FOUND THEN
301 ROLLBACK TO Save_Default_Calculation_PUB;
302 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
303 IF (x_msg_data IS NOT NULL) THEN
304 x_msg_data := x_msg_data||' ->BSC_KPI_SERIES_PUB.Save_Default_Calculation ';
305 ELSE
306 x_msg_data := SQLERRM||'BSC_KPI_SERIES_PUB.Save_Default_Calculation ';
307 END IF;
308 WHEN OTHERS THEN
309 ROLLBACK TO Save_Default_Calculation_PUB;
310 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
311 IF (x_msg_data IS NOT NULL) THEN
312 x_msg_data := x_msg_data||' ->BSC_KPI_SERIES_PUB.Save_Default_Calculation ';
313 ELSE
314 x_msg_data := SQLERRM||' at BSC_KPI_SERIES_PUB.Save_Default_Calculation ';
315 END IF;
316 END Save_Default_Calculation;
317
318 /************************************************************************************
319 -- API name : Check_Series_Default_Props
320 -- Type : Public
321 -- Function : Validates whether the default analysis option combination
322 -- has atleast one series set as default
323 ************************************************************************************/
324
325 PROCEDURE Check_Series_Default_Props(
326 p_commit IN VARCHAR2 := FND_API.G_FALSE
327 ,p_Indicator IN NUMBER
328 ,x_return_status OUT NOCOPY VARCHAR2
329 ,x_msg_count OUT NOCOPY NUMBER
330 ,x_msg_data OUT NOCOPY VARCHAR2
331 ) IS
332
333 l_AnaOpt0_Default bsc_kpi_analysis_groups.default_value%TYPE := 0;
334 l_AnaOpt1_Default bsc_kpi_analysis_groups.default_value%TYPE := 0;
335 l_AnaOpt2_Default bsc_kpi_analysis_groups.default_value%TYPE := 0;
336 l_First_Series_Id bsc_kpi_analysis_measures_b.series_id%TYPE := 0;
337
338 CURSOR c_Preselected_Series(p_Analysis_Option0 NUMBER,p_Analysis_Option1 NUMBER,p_Analysis_Option2 NUMBER) IS
339 SELECT
340 series_id
341 FROM
342 bsc_kpi_analysis_measures_b
343 WHERE
344 indicator = p_Indicator AND
345 analysis_option0 = p_Analysis_Option0 AND
346 analysis_option1 = p_Analysis_Option1 AND
347 analysis_option2 = p_Analysis_Option2 AND
348 default_value = 1;
349
350 BEGIN
351 SAVEPOINT Check_Series_Default_Props_PUB;
352 x_return_status := FND_API.G_RET_STS_SUCCESS;
353 FND_MSG_PUB.Initialize;
354
355 SELECT
356 a0_default,a1_default,a2_default
357 INTO
358 l_AnaOpt0_Default, l_AnaOpt1_Default, l_AnaOpt2_Default
359 FROM
360 bsc_db_color_ao_defaults_v
361 WHERE
362 indicator = p_Indicator;
363
364 OPEN c_Preselected_Series (l_AnaOpt0_Default,l_AnaOpt1_Default,l_AnaOpt2_Default);
365 FETCH c_Preselected_Series INTO l_First_Series_Id;
366 UPDATE
367 bsc_kpi_analysis_measures_b
368 SET
369 default_value = 0
370 WHERE indicator = p_Indicator AND
371 analysis_option0 = l_AnaOpt0_Default AND
372 analysis_option1 = l_AnaOpt1_Default AND
373 analysis_option2 = l_AnaOpt2_Default AND
374 series_id <> l_First_Series_Id;
375
376 UPDATE
377 bsc_kpi_analysis_measures_b
378 SET
379 default_value = 1
380 WHERE indicator = p_Indicator AND
381 analysis_option0 = l_AnaOpt0_Default AND
382 analysis_option1 = l_AnaOpt1_Default AND
383 analysis_option2 = l_AnaOpt2_Default AND
384 series_id = l_First_Series_Id;
385 CLOSE c_Preselected_Series;
386
387 IF FND_API.To_Boolean( p_commit ) THEN
388 COMMIT;
389 END IF;
390
391 EXCEPTION
392 WHEN OTHERS THEN
393 ROLLBACK TO Check_Series_Default_Props_PUB;
394 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
395 IF (x_msg_data IS NOT NULL) THEN
396 x_msg_data := x_msg_data||' -> BSC_OBJ_ANALYSIS_OPTIONS_PUB.Check_Series_Default_Props ';
397 ELSE
398 x_msg_data := SQLERRM||' at BSC_OBJ_ANALYSIS_OPTIONS_PUB.Check_Series_Default_Props ';
399 END IF;
400 END Check_Series_Default_Props;
401
402 /************************************************************************************
403 -- API name : Update_Color_Structure_Flags
404 -- Type : Private
405 ************************************************************************************/
406 PROCEDURE Update_Color_Structure_Flags (
407 p_commit IN VARCHAR2 := FND_API.G_FALSE
408 ,p_Indicator IN NUMBER
409 ,p_Action_Flag IN NUMBER := 3
410 ,x_return_status OUT NOCOPY VARCHAR2
411 ,x_msg_count OUT NOCOPY NUMBER
412 ,x_msg_data OUT NOCOPY VARCHAR2
413 ) IS
414
415 CURSOR c_shared_objs IS
416 SELECT
417 indicator
418 FROM
419 bsc_kpis_b
420 WHERE
421 source_indicator = p_Indicator AND
422 prototype_flag <> 2 AND
423 share_flag = 2;
424
425 BEGIN
426
427 x_return_status := FND_API.G_RET_STS_SUCCESS;
428
429 IF p_Action_Flag = 7 THEN
430 BSC_DESIGNER_PVT.ActionFlag_Change (
431 x_indicator => p_Indicator
432 ,x_newflag => BSC_DESIGNER_PVT.G_ActionFlag.GAA_Color
433 );
434 FOR cd IN c_shared_objs LOOP
435 BSC_DESIGNER_PVT.ActionFlag_Change (
436 x_indicator => cd.indicator
437 ,x_newflag => BSC_DESIGNER_PVT.G_ActionFlag.GAA_Color
438 );
439 END LOOP;
440 END IF;
441
442 IF p_Action_Flag = 3 THEN
443 BSC_DESIGNER_PVT.ActionFlag_Change (
444 x_indicator => p_Indicator
445 ,x_newflag => BSC_DESIGNER_PVT.G_ActionFlag.GAA_Structure
446 );
447 FOR cd IN c_shared_objs LOOP
448 BSC_DESIGNER_PVT.ActionFlag_Change (
449 x_indicator => cd.indicator
450 ,x_newflag => BSC_DESIGNER_PVT.G_ActionFlag.GAA_Structure
451 );
452 END LOOP;
453 END IF;
454
455 IF (p_commit = FND_API.G_TRUE) THEN
456 COMMIT;
457 END IF;
458
459 EXCEPTION
460 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
461 IF (x_msg_data IS NULL) THEN
462 FND_MSG_PUB.Count_And_Get
463 ( p_encoded => FND_API.G_FALSE
464 , p_count => x_msg_count
465 , p_data => x_msg_data
466 );
467 END IF;
468 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
469 WHEN OTHERS THEN
470 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
471 IF (x_msg_data IS NOT NULL) THEN
472 x_msg_data := x_msg_data||' ->BSC_KPI_SERIES_PUB.Update_Color_Structure_Flags ';
473 ELSE
474 x_msg_data := SQLERRM||' at BSC_KPI_SERIES_PUB.Update_Color_Structure_Flags ';
475 END IF;
476 END Update_Color_Structure_Flags;
477
478 /************************************************************************************
479 -- API name : Get_Series_Actual_Color
480 -- Type : Private
481 ************************************************************************************/
482 FUNCTION Get_Series_Color (
483 p_Color_Values IN FND_TABLE_OF_NUMBER := NULL
484 ,p_Get_Actual_Color IN VARCHAR2 := FND_API.G_TRUE
485 ) RETURN NUMBER IS
486
487 l_Color_Value bsc_kpi_analysis_measures_b.series_color%TYPE := 0;
488 l_bm_id bsc_sys_benchmarks_b.bm_id%TYPE;
489 i NUMBER := 0;
490 found BOOLEAN := FALSE;
491 BEGIN
492
493 WHILE (NOT found AND i < p_Color_Values.COUNT) LOOP
494 l_bm_id := p_Color_Values(i);
495 IF (FND_API.To_Boolean( p_Get_Actual_Color) AND l_bm_id = 0) THEN
496 l_Color_Value := p_Color_Values(i + 1);
497 found := TRUE;
498 END IF;
499 IF (NOT FND_API.To_Boolean( p_Get_Actual_Color) AND l_bm_id = 0) THEN
500 l_Color_Value := p_Color_Values(i + 1);
501 found := TRUE;
502 END IF;
503 i := i + 2;
504 END LOOP;
505 EXCEPTION
506 WHEN OTHERS THEN
507 RETURN l_Color_Value;
508 END Get_Series_Color;
509
510 /************************************************************************************
511 -- API name : Create_Analysis_Measure_UI
512 -- Type : Public
513 -- Procedure :
514 -- 1. Creates an analysis measure entry in bsc_kpi_analysis_measures table
515 -- 2. Populates the series color properties into bsc_kpi_series_colors
516 -- 3. Also sets the color enable/disable properties
517 ************************************************************************************/
518 PROCEDURE Create_Analysis_Measure_UI(
519 p_commit IN VARCHAR2 := FND_API.G_FALSE
520 ,p_Indicator IN NUMBER
521 ,p_Analysis_Option0 IN NUMBER
522 ,p_Analysis_Option1 IN NUMBER
523 ,p_Analysis_Option2 IN NUMBER
524 ,p_Series_Id IN NUMBER
525 ,p_Axis IN NUMBER := 0
526 ,p_Series_Type IN NUMBER := 0
527 ,p_Bm_Flag IN NUMBER := 0
528 ,p_Budget_Flag IN NUMBER := 0
529 ,p_Default_Flag IN NUMBER := 0
530 ,p_Stack_Series_Id IN NUMBER := NULL
531 ,p_Series_Name IN VARCHAR2
532 ,p_Series_Help IN VARCHAR2
533 ,p_dataset_Id IN NUMBER := -1
534 ,p_Color_Values IN FND_TABLE_OF_NUMBER := NULL
535 ,p_default_calculation IN NUMBER := NULL
536 ,p_time_stamp IN VARCHAR2 := NULL
537 ,x_return_status OUT NOCOPY VARCHAR2
538 ,x_msg_count OUT NOCOPY NUMBER
539 ,x_msg_data OUT NOCOPY VARCHAR2
540 ) IS
541 l_Anal_Opt_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
542 l_Bsc_Kpi_Entity_Rec BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
543 l_Count NUMBER := 0;
544 l_old_default_kpi bsc_kpi_analysis_measures_b.kpi_measure_id%TYPE;
545
546 CURSOR c_shared_objs IS
547 SELECT
548 indicator
549 FROM
550 bsc_kpis_b
551 WHERE
552 source_indicator = p_Indicator AND
553 prototype_flag <> 2 AND
554 share_flag = 2;
555
556 BEGIN
557 x_return_status := FND_API.G_RET_STS_SUCCESS;
558
559 IF p_Indicator IS NOT NULL THEN
560 l_Count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_KPIS_B'
561 ,'indicator'
562 , p_Indicator);
563 IF l_Count = 0 THEN
564 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_KPI_ID');
565 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Indicator);
566 FND_MSG_PUB.ADD;
567 RAISE FND_API.G_EXC_ERROR;
568 END IF;
569 ELSE
570 FND_MESSAGE.SET_NAME('BSC','BSC_NO_KPI_ID_ENTERED');
571 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Indicator);
572 FND_MSG_PUB.ADD;
573 RAISE FND_API.G_EXC_ERROR;
574 END IF;
575 BSC_BIS_LOCKS_PUB.LOCK_KPI
576 ( p_Kpi_Id => p_Indicator
577 , p_time_stamp => p_time_stamp
578 , p_Full_Lock_Flag => NULL
579 , x_return_status => x_return_status
580 , x_msg_count => x_msg_count
581 , x_msg_data => x_msg_data
582 );
583 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
584 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
585 END IF;
586
587 l_Anal_Opt_Rec.Bsc_Kpi_Id := p_Indicator;
588 l_Anal_Opt_Rec.Bsc_Option_Group0 := p_Analysis_Option0;
589 l_Anal_Opt_Rec.Bsc_Option_Group1 := p_Analysis_Option1;
590 l_Anal_Opt_Rec.Bsc_Option_Group2 := p_Analysis_Option2;
591 l_Anal_Opt_Rec.Bsc_Dataset_Series_Id := p_Series_Id;
592 l_Anal_Opt_Rec.Bsc_Dataset_Id := p_dataset_Id;
593 l_Anal_Opt_Rec.Bsc_Dataset_Axis := p_Axis;
594 l_Anal_Opt_Rec.Bsc_Dataset_Series_Type := p_Series_Type;
595 l_Anal_Opt_Rec.Bsc_Dataset_Bm_Flag := p_Bm_Flag;
596 l_Anal_Opt_Rec.Bsc_Dataset_Budget_Flag := p_Budget_Flag;
597 l_Anal_Opt_Rec.Bsc_Dataset_Default_Value := p_Default_Flag;
598 l_Anal_Opt_Rec.Bsc_Measure_Long_Name := p_Series_Name;
599 l_Anal_Opt_Rec.Bsc_Measure_Help := p_Series_Help;
600 l_Anal_Opt_Rec.Bsc_Dataset_Stack_Series_Id := p_Stack_Series_Id;
601 l_Anal_Opt_Rec.Bsc_Dataset_Series_Color := Get_Series_Color (p_Color_Values, FND_API.G_TRUE);
602 l_Anal_Opt_Rec.Bsc_Dataset_Bm_Color := Get_Series_Color (p_Color_Values, FND_API.G_TRUE);
603
604 BSC_ANALYSIS_OPTION_PUB.Create_Analysis_Measures(
605 p_commit => FND_API.G_FALSE
606 ,p_Anal_Opt_Rec => l_Anal_Opt_Rec
607 ,x_return_status => x_return_status
608 ,x_msg_count => x_msg_count
609 ,x_msg_data => x_msg_data
610 );
611 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
612 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
613 END IF;
614
615
616 --Populate bsc_kpi_series_colors table
617 IF p_Color_Values IS NOT NULL THEN
618 --Populate bsc_kpi_series_colors table
619 Populate_Kpi_Series_Colors (
620 p_commit => FND_API.G_FALSE
621 ,p_Anal_Opt_Rec => l_Anal_Opt_Rec
622 ,p_Color_Values => p_Color_Values
623 ,x_return_status => x_return_status
624 ,x_msg_count => x_msg_count
625 ,x_msg_data => x_msg_data
626 );
627 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
628 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
629 END IF;
630 END IF;
631
632 Check_Color_Props(
633 p_commit => FND_API.G_FALSE
634 ,p_Indicator => p_Indicator
635 ,p_Analysis_Option0 => p_Analysis_Option0
636 ,p_Analysis_Option1 => p_Analysis_Option1
637 ,p_Analysis_Option2 => p_Analysis_Option2
638 ,p_Series_Id => p_Series_Id
639 ,p_Budget_Flag => p_Budget_Flag
640 ,p_Default_Flag => p_Default_Flag
641 ,p_Dataset_Id => p_Dataset_Id
642 ,x_return_status => x_return_status
643 ,x_msg_count => x_msg_count
644 ,x_msg_data => x_msg_data
645 );
646 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
647 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
648 END IF;
649
650 FOR cd in c_shared_objs LOOP
651 l_Anal_Opt_Rec.Bsc_Kpi_Id := cd.indicator;
652 BSC_ANALYSIS_OPTION_PUB.Create_Analysis_Measures(
653 p_commit => FND_API.G_FALSE
654 ,p_Anal_Opt_Rec => l_Anal_Opt_Rec
655 ,x_return_status => x_return_status
656 ,x_msg_count => x_msg_count
657 ,x_msg_data => x_msg_data
658 );
659 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
660 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
661 END IF;
662 --Populate bsc_kpi_series_colors table
663 IF p_Color_Values IS NOT NULL THEN
664 --Populate bsc_kpi_series_colors table
665 Populate_Kpi_Series_Colors (
666 p_commit => FND_API.G_FALSE
667 ,p_Anal_Opt_Rec => l_Anal_Opt_Rec
668 ,p_Color_Values => p_Color_Values
669 ,x_return_status => x_return_status
670 ,x_msg_count => x_msg_count
671 ,x_msg_data => x_msg_data
672 );
673 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
674 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
675 END IF;
676 END IF;
677
678 Check_Color_Props(
679 p_commit => FND_API.G_FALSE
680 ,p_Indicator => cd.indicator
681 ,p_Analysis_Option0 => p_Analysis_Option0
682 ,p_Analysis_Option1 => p_Analysis_Option1
683 ,p_Analysis_Option2 => p_Analysis_Option2
684 ,p_Series_Id => p_Series_Id
685 ,p_Budget_Flag => p_Budget_Flag
686 ,p_Default_Flag => p_Default_Flag
687 ,p_Dataset_Id => p_Dataset_Id
688 ,x_return_status => x_return_status
689 ,x_msg_count => x_msg_count
690 ,x_msg_data => x_msg_data
691 );
692 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
693 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
694 END IF;
695 END LOOP;
696
697 Save_Default_Calculation(
698 p_commit => FND_API.G_FALSE
699 ,p_Indicator => p_Indicator
700 ,p_Analysis_Option0 => p_Analysis_Option0
701 ,p_Analysis_Option1 => p_Analysis_Option1
702 ,p_Analysis_Option2 => p_Analysis_Option2
703 ,p_Series_Id => p_Series_Id
704 ,p_default_calculation => p_default_calculation
705 ,x_return_status => x_return_status
706 ,x_msg_count => x_msg_count
707 ,x_msg_data => x_msg_data
708 );
709 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
710 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
711 END IF;
712
713
714 IF (p_commit = FND_API.G_TRUE) THEN
715 COMMIT;
716 END IF;
717
718 EXCEPTION
719 WHEN FND_API.G_EXC_ERROR THEN
720 IF (x_msg_data IS NULL) THEN
721 FND_MSG_PUB.Count_And_Get
722 ( p_encoded => FND_API.G_FALSE
723 , p_count => x_msg_count
724 , p_data => x_msg_data
725 );
726 END IF;
727 x_return_status := FND_API.G_RET_STS_ERROR;
728 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
729 IF (x_msg_data IS NULL) THEN
730 FND_MSG_PUB.Count_And_Get
731 ( p_encoded => FND_API.G_FALSE
732 , p_count => x_msg_count
733 , p_data => x_msg_data
734 );
735 END IF;
736 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
737 WHEN NO_DATA_FOUND THEN
738 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
739 IF (x_msg_data IS NOT NULL) THEN
740 x_msg_data := x_msg_data||' ->BSC_KPI_SERIES_PUB.Create_Analysis_Measure_UI ';
741 ELSE
742 x_msg_data := SQLERRM||'BSC_KPI_SERIES_PUB.Create_Analysis_Measure_UI ';
743 END IF;
744 WHEN OTHERS THEN
745 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
746 IF (x_msg_data IS NOT NULL) THEN
747 x_msg_data := x_msg_data||' ->BSC_KPI_SERIES_PUB.Create_Analysis_Measure_UI ';
748 ELSE
749 x_msg_data := SQLERRM||' at BSC_KPI_SERIES_PUB.Create_Analysis_Measure_UI ';
750 END IF;
751 END Create_Analysis_Measure_UI;
752
753 /************************************************************************************
754 -- API name : Update_Analysis_Measure_UI
755 -- Type : Public
756 -- Procedure :
757 -- 1. Updates the properties in bsc_kpi_analysis_measures tables
758 -- If the user maps the series to a BIS measure then the bis measure import
759 -- API will be called
760 -- 2. Updates the series color properties
761 -- 3. Also checks for the color enable/disable properties
762 ************************************************************************************/
763 PROCEDURE Update_Analysis_Measure_UI(
764 p_commit IN VARCHAR2 := FND_API.G_FALSE
765 ,p_Indicator IN NUMBER
766 ,p_Analysis_Option0 IN NUMBER
767 ,p_Analysis_Option1 IN NUMBER
768 ,p_Analysis_Option2 IN NUMBER
769 ,p_Series_Id IN NUMBER
770 ,p_Axis IN NUMBER := 0
771 ,p_Series_Type IN NUMBER := 0
772 ,p_Bm_Flag IN NUMBER := 0
773 ,p_Budget_Flag IN NUMBER := 0
774 ,p_Default_Flag IN NUMBER := 0
775 ,p_Stack_Series_Id IN NUMBER := NULL
776 ,p_Series_Name IN VARCHAR2
777 ,p_Series_Help IN VARCHAR2
778 ,p_dataset_Id IN NUMBER := -1
779 ,p_Color_Values IN FND_TABLE_OF_NUMBER := NULL
780 ,p_default_calculation IN NUMBER := NULL
781 ,p_time_stamp IN VARCHAR2 := NULL
782 ,x_return_status OUT NOCOPY VARCHAR2
783 ,x_msg_count OUT NOCOPY NUMBER
784 ,x_msg_data OUT NOCOPY VARCHAR2
785 ) IS
786 l_Anal_Opt_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
787 l_Bsc_Kpi_Entity_Rec BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
788 l_Count NUMBER := 0;
789 l_Measure_Source bsc_sys_datasets_vl.source%TYPE;
790 l_DimSet_Id bsc_kpi_analysis_options_b.dim_set_id%TYPE := 0;
791 l_Option_Name bsc_kpi_analysis_options_vl.name%TYPE;
792 l_old_default_kpi bsc_kpi_analysis_measures_b.kpi_measure_id%TYPE;
793
794 CURSOR c_shared_objs IS
795 SELECT
796 indicator
797 FROM
798 bsc_kpis_b
799 WHERE
800 source_indicator = p_Indicator AND
801 prototype_flag <> 2 AND
802 share_flag = 2;
803
804 BEGIN
805 x_return_status := FND_API.G_RET_STS_SUCCESS;
806
807 IF p_Indicator IS NOT NULL THEN
808 l_Count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_KPIS_B'
809 ,'indicator'
810 , p_Indicator);
811 IF l_Count = 0 THEN
812 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_KPI_ID');
813 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Indicator);
814 FND_MSG_PUB.ADD;
815 RAISE FND_API.G_EXC_ERROR;
816 END IF;
817 ELSE
818 FND_MESSAGE.SET_NAME('BSC','BSC_NO_KPI_ID_ENTERED');
819 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Indicator);
820 FND_MSG_PUB.ADD;
821 RAISE FND_API.G_EXC_ERROR;
822 END IF;
823 BSC_BIS_LOCKS_PUB.LOCK_KPI
824 ( p_Kpi_Id => p_Indicator
825 , p_time_stamp => p_time_stamp
826 , p_Full_Lock_Flag => NULL
827 , x_return_status => x_return_status
828 , x_msg_count => x_msg_count
829 , x_msg_data => x_msg_data
830 );
831 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
832 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
833 END IF;
834
835 l_Measure_Source := bsc_Oaf_Views_Pvt.Get_Dataset_Source(x_Dataset_Id => p_DataSet_Id);
836 l_old_default_kpi := BSC_COLOR_CALC_UTIL.Get_Default_Kpi_Measure_Id(p_Indicator);
837
838 IF l_Measure_Source = 'PMF' THEN
839 SELECT
840 dim_set_id
841 INTO
842 l_DimSet_Id
843 FROM
844 bsc_db_dataset_dim_sets_v v
845 WHERE
846 v.indicator = p_indicator AND
847 v.A0 = p_Analysis_Option0 AND
848 v.A1 = p_Analysis_Option1 AND
849 v.A2 = p_Analysis_Option2;
850
851 SELECT
852 name
853 INTO
854 l_Option_Name
855 FROM
856 bsc_kpi_analysis_options_vl
857 WHERE
858 indicator = p_Indicator AND
859 analysis_group_id = 0 AND
860 option_id = p_Analysis_Option0;
861
862 BSC_BIS_KPI_MEAS_PUB.Update_KPI_Analysis_Options -- This will cascade to the shared
863 ( p_commit => FND_API.G_FALSE
864 , p_kpi_id => p_Indicator
865 , p_data_source => l_Measure_Source
866 , p_analysis_group_id => 0
867 , p_analysis_option_id0 => p_Analysis_Option0
868 , p_analysis_option_id1 => p_Analysis_Option1
869 , p_analysis_option_id2 => p_Analysis_Option2
870 , p_series_id => 0
871 , p_data_set_id => p_DataSet_Id
872 , p_dim_set_id => l_DimSet_Id
873 , p_option0_Name => l_Option_Name
874 , p_option1_Name => NULL
875 , p_option2_Name => NULL
876 , p_measure_short_name => NULL
877 , p_dim_obj_short_names => NULL
878 , p_default_short_names => NULL
879 , p_view_by_name => NULL
880 , p_measure_name => p_Series_Name
881 , p_measure_help => p_Series_Help
882 , p_default_value => p_Default_Flag
883 , p_time_stamp => NULL
884 , p_update_ana_opt => TRUE
885 , x_return_status => x_return_status
886 , x_msg_count => x_msg_count
887 , x_msg_data => x_msg_data
888 );
889 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
890 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
891 END IF;
892 ELSE
893 l_Anal_Opt_Rec.Bsc_Kpi_Id := p_Indicator;
894 l_Anal_Opt_Rec.Bsc_Option_Group0 := p_Analysis_Option0;
895 l_Anal_Opt_Rec.Bsc_Option_Group1 := p_Analysis_Option1;
896 l_Anal_Opt_Rec.Bsc_Option_Group2 := p_Analysis_Option2;
897 l_Anal_Opt_Rec.Bsc_Dataset_Series_Id := p_Series_Id;
898 l_Anal_Opt_Rec.Bsc_Dataset_Id := p_dataset_Id;
899 l_Anal_Opt_Rec.Bsc_Dataset_Axis := p_Axis;
900 l_Anal_Opt_Rec.Bsc_Dataset_Series_Type := p_Series_Type;
901 l_Anal_Opt_Rec.Bsc_Dataset_Bm_Flag := p_Bm_Flag;
902 l_Anal_Opt_Rec.Bsc_Dataset_Budget_Flag := p_Budget_Flag;
903 l_Anal_Opt_Rec.Bsc_Dataset_Default_Value := p_Default_Flag;
904 l_Anal_Opt_Rec.Bsc_Measure_Long_Name := p_Series_Name;
905 l_Anal_Opt_Rec.Bsc_Measure_Help := p_Series_Help;
906 l_Anal_Opt_Rec.Bsc_Dataset_Stack_Series_Id := p_Stack_Series_Id;
907 l_Anal_Opt_Rec.Bsc_Change_Action_Flag := FND_API.G_FALSE;
908
909 BSC_ANALYSIS_OPTION_PUB.Update_Analysis_Measures(
910 p_commit => FND_API.G_FALSE
911 ,p_Anal_Opt_Rec => l_Anal_Opt_Rec
912 ,x_return_status => x_return_status
913 ,x_msg_count => x_msg_count
914 ,x_msg_data => x_msg_data
915 );
916 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
917 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
918 END IF;
919
920
921 IF p_Color_Values IS NOT NULL THEN
922 --Populate bsc_kpi_series_colors table
923 Populate_Kpi_Series_Colors (
924 p_commit => FND_API.G_FALSE
925 ,p_Anal_Opt_Rec => l_Anal_Opt_Rec
926 ,p_Color_Values => p_Color_Values
927 ,x_return_status => x_return_status
928 ,x_msg_count => x_msg_count
929 ,x_msg_data => x_msg_data
930 );
931 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
932 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
933 END IF;
934 END IF;
935
936 FOR cd in c_shared_objs LOOP
937 l_Anal_Opt_Rec.Bsc_Kpi_Id := cd.indicator;
938 BSC_ANALYSIS_OPTION_PUB.Update_Analysis_Measures(
939 p_commit => FND_API.G_FALSE
940 ,p_Anal_Opt_Rec => l_Anal_Opt_Rec
941 ,x_return_status => x_return_status
942 ,x_msg_count => x_msg_count
943 ,x_msg_data => x_msg_data
944 );
945 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
946 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
947 END IF;
948
949 IF p_Color_Values IS NOT NULL THEN
950 --Populate bsc_kpi_series_colors table
951 Populate_Kpi_Series_Colors (
952 p_commit => FND_API.G_FALSE
953 ,p_Anal_Opt_Rec => l_Anal_Opt_Rec
954 ,p_Color_Values => p_Color_Values
955 ,x_return_status => x_return_status
956 ,x_msg_count => x_msg_count
957 ,x_msg_data => x_msg_data
958 );
959 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
960 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
961 END IF;
962 END IF;
963 END LOOP;
964 END IF;
965
966 Save_Default_Calculation(
967 p_commit => FND_API.G_FALSE
968 ,p_Indicator => p_Indicator
969 ,p_Analysis_Option0 => p_Analysis_Option0
970 ,p_Analysis_Option1 => p_Analysis_Option1
971 ,p_Analysis_Option2 => p_Analysis_Option2
972 ,p_Series_Id => p_Series_Id
973 ,p_default_calculation => p_default_calculation
974 ,x_return_status => x_return_status
975 ,x_msg_count => x_msg_count
976 ,x_msg_data => x_msg_data
977 );
978 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
979 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
980 END IF;
981
982 -- This API should be called for both BIS and non-BIS type measures
983 Check_Color_Props(
984 p_commit => FND_API.G_FALSE
985 ,p_Indicator => p_Indicator
986 ,p_Analysis_Option0 => p_Analysis_Option0
987 ,p_Analysis_Option1 => p_Analysis_Option1
988 ,p_Analysis_Option2 => p_Analysis_Option2
989 ,p_Series_Id => p_Series_Id
990 ,p_Budget_Flag => p_Budget_Flag
991 ,p_Default_Flag => p_Default_Flag
992 ,p_Dataset_Id => p_Dataset_Id
993 ,x_return_status => x_return_status
994 ,x_msg_count => x_msg_count
995 ,x_msg_data => x_msg_data
996 );
997 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
998 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
999 END IF;
1000
1001 FOR cd in c_shared_objs LOOP
1002 Check_Color_Props(
1003 p_commit => FND_API.G_FALSE
1004 ,p_Indicator => cd.indicator
1005 ,p_Analysis_Option0 => p_Analysis_Option0
1006 ,p_Analysis_Option1 => p_Analysis_Option1
1007 ,p_Analysis_Option2 => p_Analysis_Option2
1008 ,p_Series_Id => p_Series_Id
1009 ,p_Budget_Flag => p_Budget_Flag
1010 ,p_Default_Flag => p_Default_Flag
1011 ,p_Dataset_Id => p_Dataset_Id
1012 ,x_return_status => x_return_status
1013 ,x_msg_count => x_msg_count
1014 ,x_msg_data => x_msg_data
1015 );
1016 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1017 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1018 END IF;
1019 END LOOP;
1020
1021 IF (p_commit = FND_API.G_TRUE) THEN
1022 COMMIT;
1023 END IF;
1024
1025 EXCEPTION
1026 WHEN FND_API.G_EXC_ERROR THEN
1027 IF (x_msg_data IS NULL) THEN
1028 FND_MSG_PUB.Count_And_Get
1029 ( p_encoded => FND_API.G_FALSE
1030 , p_count => x_msg_count
1031 , p_data => x_msg_data
1032 );
1033 END IF;
1034 x_return_status := FND_API.G_RET_STS_ERROR;
1035 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1036 IF (x_msg_data IS NULL) THEN
1037 FND_MSG_PUB.Count_And_Get
1038 ( p_encoded => FND_API.G_FALSE
1039 , p_count => x_msg_count
1040 , p_data => x_msg_data
1041 );
1042 END IF;
1043 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1044 WHEN NO_DATA_FOUND THEN
1045 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1046 IF (x_msg_data IS NOT NULL) THEN
1047 x_msg_data := x_msg_data||' ->BSC_KPI_SERIES_PUB.Update_Analysis_Measure_UI ';
1048 ELSE
1049 x_msg_data := SQLERRM||'BSC_KPI_SERIES_PUB.Update_Analysis_Measure_UI ';
1050 END IF;
1051 WHEN OTHERS THEN
1052 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1053 IF (x_msg_data IS NOT NULL) THEN
1054 x_msg_data := x_msg_data||' ->BSC_KPI_SERIES_PUB.Update_Analysis_Measure_UI ';
1055 ELSE
1056 x_msg_data := SQLERRM||' at BSC_KPI_SERIES_PUB.Update_Analysis_Measure_UI ';
1057 END IF;
1058 END Update_Analysis_Measure_UI;
1059
1060 /************************************************************************************
1061 -- API name : Delete_Analysis_Measure_UI
1062 -- Type : Public
1063 -- Procedure :
1064 -- 1. Deltes the entries from bsc_kpi_analysis_measures tables
1065 -- 2. Deletes the series color properties
1066 ************************************************************************************/
1067 PROCEDURE Delete_Analysis_Measure_UI(
1068 p_commit IN VARCHAR2 := FND_API.G_FALSE
1069 ,p_Indicator IN NUMBER
1070 ,p_Analysis_Option0 IN NUMBER
1071 ,p_Analysis_Option1 IN NUMBER
1072 ,p_Analysis_Option2 IN NUMBER
1073 ,p_Series_Id IN NUMBER
1074 ,p_time_stamp IN VARCHAR2 := NULL
1075 ,x_return_status OUT NOCOPY VARCHAR2
1076 ,x_msg_count OUT NOCOPY NUMBER
1077 ,x_msg_data OUT NOCOPY VARCHAR2
1078 ) IS
1079 l_Anal_Opt_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
1080 l_Bsc_Kpi_Entity_Rec BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
1081 l_Count NUMBER := 0;
1082 l_old_default_kpi bsc_kpi_analysis_measures_b.kpi_measure_id%TYPE;
1083 l_kpi_measure_id bsc_kpi_analysis_measures_b.kpi_measure_id%TYPE;
1084 l_Reset_Default BOOLEAN := FALSE;
1085
1086 CURSOR c_shared_objs IS
1087 SELECT
1088 indicator
1089 FROM
1090 bsc_kpis_b
1091 WHERE
1092 source_indicator = p_Indicator AND
1093 prototype_flag <> 2 AND
1094 share_flag = 2;
1095 BEGIN
1096 x_return_status := FND_API.G_RET_STS_SUCCESS;
1097
1098 IF p_Indicator IS NOT NULL THEN
1099 l_Count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_KPIS_B'
1100 ,'indicator'
1101 , p_Indicator);
1102 IF l_Count = 0 THEN
1103 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_KPI_ID');
1104 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Indicator);
1105 FND_MSG_PUB.ADD;
1106 RAISE FND_API.G_EXC_ERROR;
1107 END IF;
1108 ELSE
1109 FND_MESSAGE.SET_NAME('BSC','BSC_NO_KPI_ID_ENTERED');
1110 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Indicator);
1111 FND_MSG_PUB.ADD;
1112 RAISE FND_API.G_EXC_ERROR;
1113 END IF;
1114 BSC_BIS_LOCKS_PUB.LOCK_KPI
1115 ( p_Kpi_Id => p_Indicator
1116 , p_time_stamp => p_time_stamp
1117 , p_Full_Lock_Flag => NULL
1118 , x_return_status => x_return_status
1119 , x_msg_count => x_msg_count
1120 , x_msg_data => x_msg_data
1121 );
1122 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1123 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1124 END IF;
1125
1126 SELECT
1127 kpi_measure_id
1128 INTO
1129 l_kpi_measure_id
1130 FROM
1131 bsc_kpi_analysis_measures_b
1132 WHERE
1133 indicator = p_Indicator AND
1134 analysis_option0 = p_Analysis_Option0 AND
1135 analysis_option1 = p_Analysis_Option1 AND
1136 analysis_option2 = p_Analysis_Option2 AND
1137 series_id = p_Series_Id;
1138
1139
1140 l_Anal_Opt_Rec.Bsc_Kpi_Id := p_Indicator;
1141 l_Anal_Opt_Rec.Bsc_Option_Group0 := p_Analysis_Option0;
1142 l_Anal_Opt_Rec.Bsc_Option_Group1 := p_Analysis_Option1;
1143 l_Anal_Opt_Rec.Bsc_Option_Group2 := p_Analysis_Option2;
1144 l_Anal_Opt_Rec.Bsc_Dataset_Series_Id := p_Series_Id;
1145
1146 IF l_kpi_measure_id = BSC_COLOR_CALC_UTIL.Get_Default_Kpi_Measure_Id(p_Indicator) THEN
1147 l_Reset_Default := TRUE;
1148 END IF;
1149
1150 BSC_ANALYSIS_OPTION_PUB.Delete_Analysis_Measures(
1151 p_commit => FND_API.G_FALSE
1152 ,p_Anal_Opt_Rec => l_Anal_Opt_Rec
1153 ,x_return_status => x_return_status
1154 ,x_msg_count => x_msg_count
1155 ,x_msg_data => x_msg_data
1156 );
1157 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1158 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1159 END IF;
1160
1161 Delete_Kpi_Series_Colors(
1162 p_commit => FND_API.G_FALSE
1163 ,p_Anal_Opt_Rec => l_Anal_Opt_Rec
1164 ,x_return_status => x_return_status
1165 ,x_msg_count => x_msg_count
1166 ,x_msg_data => x_msg_data
1167 );
1168 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1169 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1170 END IF;
1171
1172 IF l_Reset_Default THEN
1173 Check_Series_Default_Props (
1174 p_commit => FND_API.G_FALSE
1175 ,p_Indicator => p_Indicator
1176 ,x_return_status => x_return_status
1177 ,x_msg_count => x_msg_count
1178 ,x_msg_data => x_msg_data
1179 );
1180 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1181 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1182 END IF;
1183 END IF;
1184
1185 FOR cd in c_shared_objs LOOP
1186 l_Anal_Opt_Rec.Bsc_Kpi_Id := cd.indicator;
1187 BSC_ANALYSIS_OPTION_PUB.Delete_Analysis_Measures(
1188 p_commit => FND_API.G_FALSE
1189 ,p_Anal_Opt_Rec => l_Anal_Opt_Rec
1190 ,x_return_status => x_return_status
1191 ,x_msg_count => x_msg_count
1192 ,x_msg_data => x_msg_data
1193 );
1194 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1195 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1196 END IF;
1197
1198 Delete_Kpi_Series_Colors(
1199 p_commit => FND_API.G_FALSE
1200 ,p_Anal_Opt_Rec => l_Anal_Opt_Rec
1201 ,x_return_status => x_return_status
1202 ,x_msg_count => x_msg_count
1203 ,x_msg_data => x_msg_data
1204 );
1205 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1206 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1207 END IF;
1208
1209 IF l_Reset_Default THEN
1210 Check_Series_Default_Props (
1211 p_commit => FND_API.G_FALSE
1212 ,p_Indicator => cd.Indicator
1213 ,x_return_status => x_return_status
1214 ,x_msg_count => x_msg_count
1215 ,x_msg_data => x_msg_data
1216 );
1217 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1218 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1219 END IF;
1220 END IF;
1221
1222 END LOOP;
1223
1224 IF (p_commit = FND_API.G_TRUE) THEN
1225 COMMIT;
1226 END IF;
1227
1228 EXCEPTION
1229 WHEN FND_API.G_EXC_ERROR THEN
1230 IF (x_msg_data IS NULL) THEN
1231 FND_MSG_PUB.Count_And_Get
1232 ( p_encoded => FND_API.G_FALSE
1233 , p_count => x_msg_count
1234 , p_data => x_msg_data
1235 );
1236 END IF;
1237 x_return_status := FND_API.G_RET_STS_ERROR;
1238 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1239 IF (x_msg_data IS NULL) THEN
1240 FND_MSG_PUB.Count_And_Get
1241 ( p_encoded => FND_API.G_FALSE
1242 , p_count => x_msg_count
1243 , p_data => x_msg_data
1244 );
1245 END IF;
1246 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1247 WHEN NO_DATA_FOUND THEN
1248 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1249 IF (x_msg_data IS NOT NULL) THEN
1250 x_msg_data := x_msg_data||' ->BSC_KPI_SERIES_PUB.Delete_Analysis_Measure_UI ';
1251 ELSE
1252 x_msg_data := SQLERRM||'BSC_KPI_SERIES_PUB.Delete_Analysis_Measure_UI ';
1253 END IF;
1254 WHEN OTHERS THEN
1255 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1256 IF (x_msg_data IS NOT NULL) THEN
1257 x_msg_data := x_msg_data||' ->BSC_KPI_SERIES_PUB.Delete_Analysis_Measure_UI ';
1258 ELSE
1259 x_msg_data := SQLERRM||' at BSC_KPI_SERIES_PUB.Delete_Analysis_Measure_UI ';
1260 END IF;
1261 END Delete_Analysis_Measure_UI;
1262
1263 /************************************************************************************
1264 -- API name : Populate_Kpi_Series_Colors
1265 -- Type : Private
1266 -- Function:
1267 -- Deletes the old entries from bsc_kpi_series_colors and creates new entries using
1268 -- p_Color_Values
1269 ************************************************************************************/
1270
1271 PROCEDURE Populate_Kpi_Series_Colors(
1272 p_commit IN VARCHAR2 := FND_API.G_FALSE
1273 ,p_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
1274 ,p_Color_Values IN FND_TABLE_OF_NUMBER
1275 ,x_return_status OUT NOCOPY VARCHAR2
1276 ,x_msg_count OUT NOCOPY NUMBER
1277 ,x_msg_data OUT NOCOPY VARCHAR2
1278 ) IS
1279 i NUMBER;
1280 l_bm_id bsc_kpi_series_colors.bm_id%TYPE;
1281 l_bm_color bsc_kpi_series_colors.color%TYPE;
1282 BEGIN
1283
1284 SAVEPOINT Pop_Kpi_Series_PUB;
1285 FND_MSG_PUB.Initialize;
1286 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
1287
1288 DELETE FROM bsc_kpi_series_colors
1289 WHERE indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id AND
1290 analysis_option0 = p_Anal_Opt_Rec.Bsc_Option_Group0 AND
1291 analysis_option1 = p_Anal_Opt_Rec.Bsc_Option_Group1 AND
1292 analysis_option2 = p_Anal_Opt_Rec.Bsc_Option_Group2 AND
1293 series_id = p_Anal_Opt_Rec.Bsc_Dataset_Series_Id;
1294 i := 1;
1295 WHILE (i <p_Color_Values.COUNT) LOOP
1296 l_bm_id := p_Color_Values(i);
1297 l_bm_color := p_Color_Values(i + 1);
1298
1299 INSERT INTO bsc_kpi_series_colors (indicator
1300 ,analysis_option0
1301 ,analysis_option1
1302 ,analysis_option2
1303 ,series_id
1304 ,bm_id
1305 ,color
1306 )
1307 VALUES( p_Anal_Opt_Rec.Bsc_Kpi_Id
1308 ,p_Anal_Opt_Rec.Bsc_Option_Group0
1309 ,p_Anal_Opt_Rec.Bsc_Option_Group1
1310 ,p_Anal_Opt_Rec.Bsc_Option_Group2
1311 ,p_Anal_Opt_Rec.Bsc_Dataset_Series_Id
1312 ,l_bm_id
1313 ,l_bm_color
1314 );
1315 i := i + 2;
1316 END LOOP;
1317
1318
1319 IF (p_commit = FND_API.G_TRUE) THEN
1320 COMMIT;
1321 END IF;
1322
1323
1324 EXCEPTION
1325 WHEN FND_API.G_EXC_ERROR THEN
1326 ROLLBACK TO Pop_Kpi_Series_PUB;
1327 IF (x_msg_data IS NULL) THEN
1328 FND_MSG_PUB.Count_And_Get
1329 ( p_encoded => FND_API.G_FALSE
1330 , p_count => x_msg_count
1331 , p_data => x_msg_data
1332 );
1333 END IF;
1334 x_return_status := FND_API.G_RET_STS_ERROR;
1335 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1336 ROLLBACK TO Pop_Kpi_Series_PUB;
1337 IF (x_msg_data IS NULL) THEN
1338 FND_MSG_PUB.Count_And_Get
1339 ( p_encoded => FND_API.G_FALSE
1340 , p_count => x_msg_count
1341 , p_data => x_msg_data
1342 );
1343 END IF;
1344 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1345 WHEN NO_DATA_FOUND THEN
1346 ROLLBACK TO Pop_Kpi_Series_PUB;
1347 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1348 IF (x_msg_data IS NOT NULL) THEN
1349 x_msg_data := x_msg_data||' ->BSC_KPI_SERIES_PUB.Populate_Kpi_Series_Colors ';
1350 ELSE
1351 x_msg_data := SQLERRM||'BSC_KPI_SERIES_PUB.Populate_Kpi_Series_Colors ';
1352 END IF;
1353 WHEN OTHERS THEN
1354 ROLLBACK TO Pop_Kpi_Series_PUB;
1355 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1356 IF (x_msg_data IS NOT NULL) THEN
1357 x_msg_data := x_msg_data||' ->BSC_KPI_SERIES_PUB.Populate_Kpi_Series_Colors ';
1358 ELSE
1359 x_msg_data := SQLERRM||' at BSC_KPI_SERIES_PUB.Populate_Kpi_Series_Colors ';
1360 END IF;
1361 END Populate_Kpi_Series_Colors;
1362
1363 /************************************************************************************
1364 -- API name : Delete_Kpi_Series_Colors
1365 -- Type : Private
1366 -- Function:
1367 -- Deletes the entries from bsc_kpi_series_colors
1368 ************************************************************************************/
1369
1370 PROCEDURE Delete_Kpi_Series_Colors(
1371 p_commit IN VARCHAR2 := FND_API.G_FALSE
1372 ,p_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
1373 ,x_return_status OUT NOCOPY VARCHAR2
1374 ,x_msg_count OUT NOCOPY NUMBER
1375 ,x_msg_data OUT NOCOPY VARCHAR2
1376 ) IS
1377 BEGIN
1378
1379 SAVEPOINT Delete_Kpi_SeriesColor_PUB;
1380 FND_MSG_PUB.Initialize;
1381 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
1382
1383 DELETE FROM bsc_kpi_series_colors
1384 WHERE indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id AND
1385 analysis_option0 = p_Anal_Opt_Rec.Bsc_Option_Group0 AND
1386 analysis_option1 = p_Anal_Opt_Rec.Bsc_Option_Group1 AND
1387 analysis_option2 = p_Anal_Opt_Rec.Bsc_Option_Group2 AND
1388 series_id = p_Anal_Opt_Rec.Bsc_Dataset_Series_Id;
1389
1390 IF (p_commit = FND_API.G_TRUE) THEN
1391 COMMIT;
1392 END IF;
1393
1394
1395 EXCEPTION
1396 WHEN FND_API.G_EXC_ERROR THEN
1397 ROLLBACK TO Delete_Kpi_SeriesColor_PUB;
1398 IF (x_msg_data IS NULL) THEN
1399 FND_MSG_PUB.Count_And_Get
1400 ( p_encoded => FND_API.G_FALSE
1401 , p_count => x_msg_count
1402 , p_data => x_msg_data
1403 );
1404 END IF;
1405 x_return_status := FND_API.G_RET_STS_ERROR;
1406 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1407 ROLLBACK TO Delete_Kpi_SeriesColor_PUB;
1408 IF (x_msg_data IS NULL) THEN
1409 FND_MSG_PUB.Count_And_Get
1410 ( p_encoded => FND_API.G_FALSE
1411 , p_count => x_msg_count
1412 , p_data => x_msg_data
1413 );
1414 END IF;
1415 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1416 WHEN NO_DATA_FOUND THEN
1417 ROLLBACK TO Delete_Kpi_SeriesColor_PUB;
1418 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1419 IF (x_msg_data IS NOT NULL) THEN
1420 x_msg_data := x_msg_data||' ->BSC_KPI_SERIES_PUB.Delete_Kpi_Series_Colors ';
1421 ELSE
1422 x_msg_data := SQLERRM||'BSC_KPI_SERIES_PUB.Delete_Kpi_Series_Colors ';
1423 END IF;
1424 WHEN OTHERS THEN
1425 ROLLBACK TO Delete_Kpi_SeriesColor_PUB;
1426 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1427 IF (x_msg_data IS NOT NULL) THEN
1428 x_msg_data := x_msg_data||' ->BSC_KPI_SERIES_PUB.Delete_Kpi_Series_Colors ';
1429 ELSE
1430 x_msg_data := SQLERRM||' at BSC_KPI_SERIES_PUB.Delete_Kpi_Series_Colors ';
1431 END IF;
1432
1433 END Delete_Kpi_Series_Colors;
1434
1435 /************************************************************************************
1436 -- API name : Check_DimSet_DataSet_Exists
1437 -- Type : Private
1438 -- Function : This is a helper API used in determining strucutural changes
1439 ************************************************************************************/
1440
1441 FUNCTION Check_DimSet_DataSet_Exists(
1442 p_newdim_Dataset_map IN Bsc_Dim_DataSet_Table
1443 ,p_dim_set_id IN NUMBER
1444 ,p_dataset_id IN NUMBER
1445 ) RETURN NUMBER IS
1446
1447 l_Count NUMBER := 0;
1448 l_newdim_Dataset_map Bsc_Dim_DataSet_Table;
1449 i NUMBER;
1450 BEGIN
1451
1452 FOR i IN p_newdim_Dataset_map.FIRST..p_newdim_Dataset_map.LAST LOOP
1453 IF (p_newdim_Dataset_map.EXISTS(i) AND p_newdim_Dataset_map(i).dim_set_id = p_dim_set_id
1454 AND p_newdim_Dataset_map(i).dataset_id = p_dataset_id) THEN
1455 RETURN p_newdim_Dataset_map(i).rec_count;
1456 END IF;
1457 END LOOP;
1458
1459 RETURN l_Count;
1460
1461 EXCEPTION
1462 WHEN OTHERS THEN
1463 RETURN 0;
1464 END Check_DimSet_DataSet_Exists;
1465
1466 /************************************************************************************
1467 -- API name : Check_Structure_Change
1468 -- Type : Public
1469 -- Function : This API will check for structural changes
1470 -- Parameters :
1471 --
1472 -- p_Analysis_Option0,p_Analysis_Option1,p_Analysis_Option2 is the current
1473 -- analysis option combination
1474 -- p_Series_Id - series_id if called from Update Series and as -1 if called
1475 -- define series
1476 -- p_New_Dataset_Map - The new dataset set ids mapped for the current analysis
1477 -- option combination
1478 -- p_Delete_Mode - Set to 1 when a series is deleted from the HGridw
1479 ************************************************************************************/
1480
1481 PROCEDURE Check_Series_Structure_Change (
1482 p_commit IN VARCHAR2 := FND_API.G_FALSE
1483 ,p_Indicator IN NUMBER
1484 ,p_Analysis_Option0 IN NUMBER
1485 ,p_Analysis_Option1 IN NUMBER
1486 ,p_Analysis_Option2 IN NUMBER
1487 ,p_Series_Id IN NUMBER
1488 ,p_New_Dataset_Map IN FND_TABLE_OF_NUMBER
1489 ,p_Delete_Mode IN NUMBER := 0
1490 ,x_return_status OUT NOCOPY VARCHAR2
1491 ,x_msg_count OUT NOCOPY NUMBER
1492 ,x_msg_data OUT NOCOPY VARCHAR2
1493 ) IS
1494
1495 l_Structure_Change BOOLEAN := FALSE;
1496 l_Count NUMBER := 0;
1497
1498 l_All_Comb_Map_Old Bsc_Dim_Dataset_Table;
1499 l_All_Comb_Map_New Bsc_Dim_Dataset_Table;
1500 l_AO_Comb_Map Bsc_Dim_Dataset_Table;
1501
1502 l_Series_DataSet_Id bsc_kpi_analysis_measures_b.dataset_id%TYPE;
1503 l_Series_DimSet_Id bsc_kpi_analysis_options_b.dim_set_id%TYPE;
1504 l_Combination_Cnt NUMBER;
1505 l_Found BOOLEAN;
1506 i NUMBER;
1507 j NUMBER;
1508
1509 l_Current_DimSet bsc_kpi_analysis_options_b.dim_set_id%TYPE;
1510
1511 CURSOR c_dimset_dataset_map IS
1512 SELECT
1513 db.dim_set_id, db.dataset_id, count(1)
1514 FROM
1515 bsc_db_dataset_dim_sets_v db,
1516 bsc_sys_datasets_vl ds
1517 WHERE
1518 ds.source = 'BSC' AND
1519 db.indicator = p_indicator AND db.dataset_id = ds.dataset_id
1520 GROUP BY db.dim_set_id, db.dataset_id
1521 ORDER by db.dim_set_id, db.dataset_id;
1522
1523 CURSOR c_AO_Comb IS
1524 SELECT
1525 db.dim_set_id, db.dataset_id, count(1)
1526 FROM
1527 bsc_db_dataset_dim_sets_v db,
1528 bsc_sys_datasets_vl ds
1529 WHERE
1530 ds.source = 'BSC' AND
1531 db.indicator = p_indicator AND
1532 db.dataset_id = ds.dataset_id AND
1533 db.A0 = p_Analysis_Option0 AND
1534 db.A1 = p_Analysis_Option1 AND
1535 db.A2 = p_Analysis_Option2
1536 GROUP BY db.dim_set_id, db.dataset_id
1537 ORDER BY db.dim_set_id, db.dataset_id;
1538
1539 CURSOR c_AO_Comb_Series IS
1540 SELECT
1541 db.dim_set_id, db.dataset_id
1542 FROM
1543 bsc_db_dataset_dim_sets_v db,
1544 bsc_sys_datasets_vl ds
1545 WHERE
1546 ds.source = 'BSC' AND
1547 db.indicator = p_indicator AND
1548 db.dataset_id = ds.dataset_id AND
1549 db.A0 = p_Analysis_Option0 AND
1550 db.A1 = p_Analysis_Option1 AND
1551 db.A2 = p_Analysis_Option2 AND
1552 db.series_id = p_Series_Id
1553 ORDER BY db.dim_set_id, db.dataset_id;
1554
1555 CURSOR c_Dim_Set IS
1556 SELECT DISTINCT
1557 dim_set_id
1558 FROM
1559 bsc_db_dataset_dim_sets_v
1560 WHERE
1561 indicator = p_Indicator AND
1562 A0 = p_Analysis_Option0 AND
1563 A1 = p_Analysis_Option1 AND
1564 A2 = p_Analysis_Option2;
1565
1566
1567 CURSOR c_shared_objs IS
1568 SELECT
1569 indicator
1570 FROM
1571 bsc_kpis_b
1572 WHERE
1573 source_indicator = p_Indicator AND
1574 prototype_flag <> 2 AND
1575 share_flag = 2;
1576 BEGIN
1577
1578 FND_MSG_PUB.Initialize;
1579
1580 OPEN c_Dim_Set;
1581 FETCH c_Dim_Set INTO l_Current_DimSet;
1582 CLOSE c_Dim_Set;
1583
1584 OPEN c_dimset_dataset_map;
1585 FETCH c_dimset_dataset_map BULK COLLECT INTO l_All_Comb_Map_Old;
1586 CLOSE c_dimset_dataset_map;
1587
1588 OPEN c_dimset_dataset_map;
1589 FETCH c_dimset_dataset_map BULK COLLECT INTO l_All_Comb_Map_New;
1590 CLOSE c_dimset_dataset_map;
1591
1592
1593 IF p_Series_Id IS NULL THEN
1594 OPEN c_AO_Comb;
1595 FETCH c_AO_Comb BULK COLLECT INTO l_AO_Comb_Map;
1596 CLOSE c_AO_Comb;
1597
1598 FOR i in 1..l_AO_Comb_Map.COUNT LOOP
1599 l_Combination_Cnt := Check_DimSet_DataSet_Exists (
1600 l_All_Comb_Map_New,
1601 l_AO_Comb_Map(i).Dim_Set_Id,
1602 l_AO_Comb_Map(i).DataSet_Id
1603 );
1604 IF l_Combination_Cnt = 1 THEN
1605
1606 FOR j IN l_All_Comb_Map_New.FIRST..l_All_Comb_Map_New.LAST LOOP
1607 IF (l_All_Comb_Map_New.EXISTS(j) AND
1608 l_All_Comb_Map_New(j).dim_set_id = l_AO_Comb_Map(i).Dim_Set_Id AND
1609 l_All_Comb_Map_New(j).dataset_id = l_AO_Comb_Map(i).DataSet_Id) THEN
1610 l_All_Comb_Map_New.DELETE(j);
1611 EXIT;
1612 END IF;
1613 END LOOP;
1614
1615 END IF;
1616 END LOOP;
1617 ELSE
1618 OPEN c_AO_Comb_Series;
1619 FETCH c_AO_Comb_Series INTO l_Series_DimSet_Id,l_Series_DataSet_Id;
1620 CLOSE c_AO_Comb_Series;
1621 l_Combination_Cnt := Check_DimSet_DataSet_Exists (
1622 l_All_Comb_Map_New,
1623 l_Series_DimSet_Id,
1624 l_Series_DataSet_Id
1625 );
1626 IF l_Combination_Cnt = 1 THEN
1627 FOR j in 1..l_All_Comb_Map_New.COUNT LOOP
1628 IF (l_All_Comb_Map_New(j).dim_set_id = l_Series_DimSet_Id AND
1629 l_All_Comb_Map_New(j).dataset_id = l_Series_DataSet_Id) THEN
1630
1631 l_All_Comb_Map_New.DELETE(j);
1632
1633
1634 EXIT;
1635 END IF;
1636 END LOOP;
1637 END IF;
1638 END IF;
1639
1640
1641
1642 IF p_Delete_Mode = 0 THEN
1643 FOR i in 1..p_new_dataset_map.COUNT LOOP
1644 l_Combination_Cnt := Check_DimSet_DataSet_Exists (
1645 l_All_Comb_Map_New,
1646 l_Current_DimSet,
1647 p_new_dataset_map(i)
1648 );
1649 IF l_Combination_Cnt = 0 THEN
1650 l_All_Comb_Map_New.EXTEND;
1651 j := l_All_Comb_Map_New.LAST;
1652 l_All_Comb_Map_New(j).dataset_id := p_new_dataset_map(i);
1653 l_All_Comb_Map_New(j).dim_set_id := l_Current_DimSet;
1654 l_All_Comb_Map_New(j).rec_count := 1;
1655 END IF;
1656 END LOOP;
1657 END IF;
1658
1659
1660 FOR i IN l_All_Comb_Map_New.FIRST..l_All_Comb_Map_New.LAST LOOP
1661 l_Found := FALSE;
1662 IF l_All_Comb_Map_New.EXISTS(i) THEN
1663
1664 FOR j IN l_All_Comb_Map_Old.FIRST..l_All_Comb_Map_Old.LAST LOOP
1665 IF (l_All_Comb_Map_New(i).dim_set_id = l_All_Comb_Map_Old(j).dim_set_id AND
1666 l_All_Comb_Map_New(i).dataset_id = l_All_Comb_Map_Old(j).dataset_id) THEN
1667 l_Found := TRUE;
1668 END IF;
1669 END LOOP;
1670
1671 IF l_Found = FALSE AND l_All_Comb_Map_New(i).dim_set_id IS NOT NULL
1672 AND l_All_Comb_Map_New(i).dataset_id IS NOT NULL THEN
1673 l_Structure_Change := TRUE;
1674 EXIT;
1675 END IF;
1676 END IF;
1677 END LOOP;
1678
1679
1680
1681 IF NOT l_Structure_Change THEN
1682 FOR i IN l_All_Comb_Map_Old.FIRST..l_All_Comb_Map_Old.LAST LOOP
1683 l_Found := FALSE;
1684 FOR j IN l_All_Comb_Map_New.FIRST..l_All_Comb_Map_New.LAST LOOP
1685 IF (l_All_Comb_Map_New.EXISTS(j) AND
1686 l_All_Comb_Map_Old(i).dim_set_id = l_All_Comb_Map_New(j).dim_set_id AND
1687 l_All_Comb_Map_Old(i).dataset_id = l_All_Comb_Map_New(j).dataset_id) THEN
1688 l_Found := TRUE;
1689 END IF;
1690 END LOOP;
1691
1692 IF l_Found = FALSE AND l_All_Comb_Map_Old(i).dim_set_id IS NOT NULL
1693 AND l_All_Comb_Map_Old(i).dataset_id IS NOT NULL THEN
1694 l_Structure_Change := TRUE;
1695 EXIT;
1696 END IF;
1697 END LOOP;
1698 END IF;
1699
1700 IF l_Structure_Change THEN
1701 FND_MESSAGE.SET_NAME('BSC','BSC_PMD_KPI_STRUCT_INVALID');
1702 FND_MESSAGE.SET_TOKEN('INDICATORS', BSC_BIS_LOCKS_PVT.Get_Kpi_Name(p_Indicator));
1703 FND_MSG_PUB.ADD;
1704 RAISE FND_API.G_EXC_ERROR;
1705 END IF;
1706
1707
1708 EXCEPTION
1709 WHEN FND_API.G_EXC_ERROR THEN
1710 IF (x_msg_data IS NULL) THEN
1711 FND_MSG_PUB.Count_And_Get
1712 ( p_encoded => FND_API.G_FALSE
1713 , p_count => x_msg_count
1714 , p_data => x_msg_data
1715 );
1716 END IF;
1717 x_return_status := FND_API.G_RET_STS_ERROR;
1718 WHEN OTHERS THEN
1719 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1720 IF (x_msg_data IS NOT NULL) THEN
1721 x_msg_data := x_msg_data||' ->BSC_KPI_SERIES_PUB.Check_Series_Structure_Change ';
1722 ELSE
1723 x_msg_data := SQLERRM||'BSC_KPI_SERIES_PUB.Check_Series_Structure_Change ';
1724 END IF;
1725 END Check_Series_Structure_Change;
1726
1727 /************************************************************************************
1728 -- API name : Update_Kpi_Time_Stamp
1729 -- Type : Public
1730 ************************************************************************************/
1731
1732 PROCEDURE Update_Kpi_Time_Stamp(
1733 p_commit IN varchar2 := FND_API.G_FALSE
1734 ,p_Indicator IN NUMBER
1735 ,x_return_status OUT NOCOPY varchar2
1736 ,x_msg_count OUT NOCOPY number
1737 ,x_msg_data OUT NOCOPY varchar2
1738 ) IS
1739
1740 l_Bsc_Kpi_Entity_Rec BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
1741
1742 CURSOR c_shared_objs IS
1743 SELECT
1744 indicator
1745 FROM
1746 bsc_kpis_b
1747 WHERE
1748 source_indicator = p_Indicator AND
1749 prototype_flag <> 2 AND
1750 share_flag = 2;
1751
1752 BEGIN
1753
1754 SAVEPOINT ObjKpiTimeStampPUB;
1755
1756 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id := p_Indicator;
1757 BSC_KPI_PUB.Update_Kpi_Time_Stamp(
1758 p_commit => FND_API.G_FALSE
1759 ,p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
1760 ,x_return_status => x_return_status
1761 ,x_msg_count => x_msg_count
1762 ,x_msg_data => x_msg_data
1763 );
1764 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1765 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1766 END IF;
1767 BSC_DESIGNER_PVT.Deflt_Update_AOPTS ( x_indicator => l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id);
1768
1769 FOR cd in c_shared_objs LOOP
1770 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id := cd.indicator;
1771 BSC_KPI_PUB.Update_Kpi_Time_Stamp(
1772 p_commit => FND_API.G_FALSE
1773 ,p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
1774 ,x_return_status => x_return_status
1775 ,x_msg_count => x_msg_count
1776 ,x_msg_data => x_msg_data
1777 );
1778 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1779 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1780 END IF;
1781 BSC_DESIGNER_PVT.Deflt_Update_AOPTS ( x_indicator => cd.indicator );
1782 END LOOP;
1783
1784 IF fnd_api.to_boolean(p_commit) THEN
1785 COMMIT;
1786 END IF;
1787
1788 EXCEPTION
1789 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1790 ROLLBACK TO ObjKpiTimeStampPUB;
1791 IF (x_msg_data IS NULL) THEN
1792 FND_MSG_PUB.Count_And_Get
1793 ( p_encoded => FND_API.G_FALSE
1794 , p_count => x_msg_count
1795 , p_data => x_msg_data
1796 );
1797 END IF;
1798 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1799 WHEN OTHERS THEN
1800 ROLLBACK TO ObjKpiTimeStampPUB;
1801 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1802 IF (x_msg_data IS NOT NULL) THEN
1803 x_msg_data := x_msg_data||' ->BSC_KPI_SERIES_PUB.Update_Kpi_Time_Stamp ';
1804 ELSE
1805 x_msg_data := SQLERRM||' at BSC_KPI_SERIES_PUB.Update_Kpi_Time_Stamp ';
1806 END IF;
1807 END Update_Kpi_Time_Stamp;
1808
1809 END BSC_KPI_SERIES_PUB;