[Home] [Help]
PACKAGE BODY: APPS.BSC_ANALYSIS_OPTION_PUB
Source
1 PACKAGE BODY BSC_ANALYSIS_OPTION_PUB as
2 /* $Header: BSCPANOB.pls 120.12 2007/02/08 14:00:00 akoduri ship $ */
3 /*
4 +======================================================================================+
5 | Copyright (c) 2001 Oracle Corporation, Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +======================================================================================+
8 | FILENAME |
9 | BSCPANOB.pls |
10 | |
11 | Creation Date: |
12 | October 10, 2001 |
13 | |
14 | Creator: |
15 | Mario-Jair Campos |
16 | |
17 | Description: |
18 | Public Body version. |
19 | This package creates a BSC Analysis Option. |
20 | |
21 | 13-MAY-2003 PWALI Bug #2942895, SQL BIND COMPLIANCE |
22 | 14-NOV-2003 PAJOHRI Bug #3248729 |
23 | 02-jul-2004 rpenneru Modified for Enhancement#3532517 |
24 | 20-APR-2005 ADRAO Called APIs Cascade_Series_Default_Value |
25 | 28-APR-2005 ADRAO Fixed Bug#4327480 |
26 | 27-JUL-2005 ADRAO Fixed Bug#4357962 |
27 | 16-AUG-2005 akoduri Bug#4482355 Removing attribute_code and |
28 | attribute2 dependency in Report Designer |
29 | 22-aug-2005 ashankar bug#4220400 added the following APIs |
30 | 1.Default_Anal_Option_Changed |
31 | 2.Set_Default_Analysis_Option |
32 | 3.Get_Analysis_Group_Id |
33 | 4.Get_Num_Analysis_options |
34 | 03-jan-2006 rpenneru bug#4899020 comparison source is not updated properly |
35 | while Rearrange_Data_Series |
36 | 05-jan-2006 rpenneru bug#4683354 Modified to reset datasource both for BSC |
37 | and PMF type measures |
38 | 22-may-2006 akoduri bug#5104402 data source is getting updated wrongly for |
39 | PMF type measures |
40 | 12-Sep-2006 akoduri Bug#5526265 Issues iwth actual_data_source and |
41 | function name updation |
42 | 11-OCT-2006 akoduri Bug #5554168 Issue with Measures having different short |
43 | names in bis_indicators & bsc_sys_measures |
44 | 31-Jan-2007 akoduri Enh #5679096 Migration of multibar functionality from |
45 | VB to Html |
46 +======================================================================================+
47 */
48
49 G_PKG_NAME CONSTANT varchar2(30) := 'BSC_ANALYSIS_OPTION_PVT';
50
51 --: This procedure is used to create an analysis option. This is the entry point
52 --: for the Analysis Option API.
53 --: This procedure is part of the Analysis Option API.
54
55 PROCEDURE Create_Analysis_Options
56 ( p_commit IN varchar2 -- := FND_API.G_FALSE
57 , p_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
58 , x_return_status OUT NOCOPY VARCHAR2
59 , x_msg_count OUT NOCOPY NUMBER
60 , x_msg_data OUT NOCOPY VARCHAR2
61 ) IS
62 l_Anal_Opt_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
63 l_share_flag NUMBER;
64 l_count NUMBER;
65
66 --get shared indicators
67 CURSOR c_kpi_ids IS
68 SELECT indicator
69 FROM BSC_KPIS_B
70 WHERE Source_Indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
71 AND Prototype_Flag <> 2;
72 begin
73 FND_MSG_PUB.Initialize;
74 x_return_status := FND_API.G_RET_STS_SUCCESS;
75 -- Assign all values in the passed "Record" parameter to the locally defined
76 -- "Record" variable.
77 l_Anal_Opt_Rec := p_Anal_Opt_Rec;
78
79 -- Assign certain default values if they are currently null.
80 if l_Anal_Opt_Rec.Bsc_Dataset_Axis is null then
81 l_Anal_Opt_Rec.Bsc_Dataset_Axis := 1;
82 end if;
83 if l_Anal_Opt_Rec.Bsc_Dataset_Bm_Color is null then
84 l_Anal_Opt_Rec.Bsc_Dataset_Bm_Color := 10053171;
85 end if;
86 if l_Anal_Opt_Rec.Bsc_Dataset_Bm_Flag is null then
87 l_Anal_Opt_Rec.Bsc_Dataset_Bm_Flag := 1;
88 end if;
89 if l_Anal_Opt_Rec.Bsc_Dataset_Budget_Flag is null then
90 l_Anal_Opt_Rec.Bsc_Dataset_Budget_Flag := 1;
91 end if;
92 if l_Anal_Opt_Rec.Bsc_Dataset_Default_Value is null then
93 l_Anal_Opt_Rec.Bsc_Dataset_Default_Value := 1;
94 end if;
95 if l_Anal_Opt_Rec.Bsc_Dataset_Series_Color is null then
96 l_Anal_Opt_Rec.Bsc_Dataset_Series_Color := 10053171;
97 end if;
98 if l_Anal_Opt_Rec.Bsc_Dataset_Series_Id is null then
99 l_Anal_Opt_Rec.Bsc_Dataset_Series_Id := 0;
100 end if;
101 if l_Anal_Opt_Rec.Bsc_Dataset_Series_Type is null then
102 l_Anal_Opt_Rec.Bsc_Dataset_Series_Type := 1;
103 end if;
104 if l_Anal_Opt_Rec.Bsc_User_Level0 is null then
105 l_Anal_Opt_Rec.Bsc_User_Level0 := 2;
106 end if;
107 if l_Anal_Opt_Rec.Bsc_User_Level1 is null then
108 l_Anal_Opt_Rec.Bsc_User_Level1 := 2;
109 end if;
110 if l_Anal_Opt_Rec.Bsc_Option_Help is null then
111 l_Anal_Opt_Rec.Bsc_Option_Help := l_Anal_Opt_Rec.Bsc_Option_Name;
112 end if;
113
114 -- If there is no current Data set then set the data set equal to -1, and set the name
115 -- of the measure to a default name.
116 if l_Anal_Opt_Rec.Bsc_Dataset_Id is null then
117 l_Anal_Opt_Rec.Bsc_Dataset_Id := -1;
118 l_Anal_Opt_Rec.Bsc_Measure_Long_Name := BSC_APPS.GET_LOOKUP_VALUE('BSC_UI_COMMON', 'DEFAULT') ||
119 ' ' || BSC_APPS.GET_LOOKUP_VALUE('BSC_UI_COMMON', 'EDW_MEASURE');
120
121 l_Anal_Opt_Rec.Bsc_Measure_Help := l_Anal_Opt_Rec.Bsc_Measure_Long_Name;
122 end if;
123
124 -- If this is a new KPI then call private version right away with defaults passed.
125 -- If it is not a new KPI then do everything else.
126 if l_Anal_Opt_Rec.Bsc_New_Kpi = 'Y' then
127 BSC_ANALYSIS_OPTION_PVT.Create_Analysis_Options( p_commit
128 ,l_Anal_Opt_Rec
129 ,x_return_status
130 ,x_msg_count
131 ,x_msg_data);
132
133 else
134
135 -- Verify that this is not a Shared KPI.
136 select share_flag
137 into l_share_flag
138 from BSC_KPIS_B
139 where indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id;
140
141 if l_share_flag = 2 then
142 FND_MESSAGE.SET_NAME('BSC','BSC_SHARED_KPI');
143 FND_MESSAGE.SET_TOKEN('BSC_KPI', l_Anal_Opt_Rec.Bsc_Kpi_Id);
144 FND_MSG_PUB.ADD;
145 RAISE FND_API.G_EXC_ERROR;
146 end if;
147
148 -- Select the number of the last analysis option plus 1 more for the given KPI
149 -- and give Analysis Group.
150 select max(option_id) + 1
151 into l_Anal_Opt_Rec.Bsc_Analysis_Option_Id
152 from BSC_KPI_ANALYSIS_OPTIONS_B
153 where indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
154 and analysis_group_id = l_Anal_Opt_Rec.Bsc_Analysis_Group_Id;
155
156 -- Set the value for the Bsc_Option_Group0 equal to the value for the
157 -- Bsc_Analysis_Option_Id. The Bsc_Option_Group0 holds the values for the Analysis
158 -- Option IDs.
159 l_Anal_Opt_Rec.Bsc_Option_Group0 := l_Anal_Opt_Rec.Bsc_Analysis_Option_Id;
160
161 -- Set user level access.
162 l_Anal_Opt_Rec.Bsc_User_Level0 := 2;
163 l_Anal_Opt_Rec.Bsc_User_Level1 := 2;
164
165
166 -- Get the name for the Data Set Id given.
167 select name
168 into l_Anal_Opt_Rec.Bsc_Measure_Long_Name
169 from BSC_SYS_DATASETS_TL
170 where dataset_id = l_Anal_Opt_Rec.Bsc_Dataset_Id
171 and language = USERENV('LANG');
172
173 -- If help for the measure is null set it equal to the name.
174 if l_Anal_Opt_Rec.Bsc_Measure_Help is null then
175 l_Anal_Opt_Rec.Bsc_Measure_Help := l_Anal_Opt_Rec.Bsc_Measure_Long_Name;
176 end if;
177
178 -- Call private version of procedure.
179 BSC_ANALYSIS_OPTION_PVT.Create_Analysis_Options( p_commit
180 ,l_Anal_Opt_Rec
181 ,x_return_status
182 ,x_msg_count
183 ,x_msg_data);
184
185 end if;
186
187 -- Call the following procedure.
188 Create_Analysis_Measures( p_commit
189 ,l_Anal_Opt_Rec
190 ,x_return_status
191 ,x_msg_count
192 ,x_msg_data);
193
194 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
195 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
196 END IF;
197 -- repeat the steps for shared indicators also
198 FOR cd IN c_kpi_ids LOOP
199 l_Anal_Opt_Rec.Bsc_Kpi_Id := cd.Indicator;
200 BSC_ANALYSIS_OPTION_PVT.Create_Analysis_Options( p_commit
201 ,l_Anal_Opt_Rec
202 ,x_return_status
203 ,x_msg_count
204 ,x_msg_data);
205
206 Create_Analysis_Measures( p_commit
207 ,l_Anal_Opt_Rec
208 ,x_return_status
209 ,x_msg_count
210 ,x_msg_data);
211 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
212 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
213 END IF;
214 END LOOP;
215 EXCEPTION
216 WHEN FND_API.G_EXC_ERROR THEN
217 IF (x_msg_data IS NULL) THEN
218 FND_MSG_PUB.Count_And_Get
219 ( p_encoded => FND_API.G_FALSE
220 , p_count => x_msg_count
221 , p_data => x_msg_data
222 );
223 END IF;
224 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
225 x_return_status := FND_API.G_RET_STS_ERROR;
226 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
227 IF (x_msg_data IS NULL) THEN
228 FND_MSG_PUB.Count_And_Get
229 ( p_encoded => FND_API.G_FALSE
230 , p_count => x_msg_count
231 , p_data => x_msg_data
232 );
233 END IF;
234 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
235 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
236 WHEN NO_DATA_FOUND THEN
237 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
238 IF (x_msg_data IS NOT NULL) THEN
239 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Create_Analysis_Options ';
240 ELSE
241 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Create_Analysis_Options ';
242 END IF;
243 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
244 WHEN OTHERS THEN
245 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
246 IF (x_msg_data IS NOT NULL) THEN
247 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Create_Analysis_Options ';
248 ELSE
249 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Create_Analysis_Options ';
250 END IF;
251 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
252 end Create_Analysis_Options;
253
254
255 /************************************************************************************
256 ************************************************************************************/
257 procedure Retrieve_Analysis_Options(
258 p_commit IN varchar2 -- := FND_API.G_FALSE
259 ,p_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
260 ,x_Anal_Opt_Rec IN OUT NOCOPY BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
261 ,x_return_status OUT NOCOPY varchar2
262 ,x_msg_count OUT NOCOPY number
263 ,x_msg_data OUT NOCOPY varchar2
264 ) is
265
266 begin
267 x_return_status := FND_API.G_RET_STS_SUCCESS;
268 Retrieve_Analysis_Options
269 (
270 p_commit => p_commit
271 , p_Anal_Opt_Rec => p_Anal_Opt_Rec
272 , x_Anal_Opt_Rec => x_Anal_Opt_Rec
273 , p_data_source => NULL
274 , x_return_status => x_return_status
275 , x_msg_count => x_msg_count
276 , x_msg_data => x_msg_data
277 );
278 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
279 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
280 END IF;
281 EXCEPTION
282 WHEN FND_API.G_EXC_ERROR THEN
283 IF (x_msg_data IS NULL) THEN
284 FND_MSG_PUB.Count_And_Get
285 ( p_encoded => FND_API.G_FALSE
286 , p_count => x_msg_count
287 , p_data => x_msg_data
288 );
289 END IF;
290 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
291 x_return_status := FND_API.G_RET_STS_ERROR;
292 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
293 IF (x_msg_data IS NULL) THEN
294 FND_MSG_PUB.Count_And_Get
295 ( p_encoded => FND_API.G_FALSE
296 , p_count => x_msg_count
297 , p_data => x_msg_data
298 );
299 END IF;
300 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
301 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
302 WHEN NO_DATA_FOUND THEN
303 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
304 IF (x_msg_data IS NOT NULL) THEN
305 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Retrieve_Analysis_Options ';
306 ELSE
307 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Retrieve_Analysis_Options ';
308 END IF;
309 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
310 WHEN OTHERS THEN
311 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
312 IF (x_msg_data IS NOT NULL) THEN
313 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Retrieve_Analysis_Options ';
314 ELSE
315 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Retrieve_Analysis_Options ';
316 END IF;
317 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
318
319 end Retrieve_Analysis_Options;
320
321 /************************************************************************************
322 ************************************************************************************/
323
324 procedure Retrieve_Analysis_Options(
325 p_commit IN varchar2 -- := FND_API.G_FALSE
326 ,p_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
327 ,x_Anal_Opt_Rec IN OUT NOCOPY BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
328 ,p_data_source IN VARCHAR2
329 ,x_return_status OUT NOCOPY varchar2
330 ,x_msg_count OUT NOCOPY number
331 ,x_msg_data OUT NOCOPY varchar2
332 ) is
333 begin
334 x_return_status := FND_API.G_RET_STS_SUCCESS;
335 BSC_ANALYSIS_OPTION_PVT.Retrieve_Analysis_Options( p_commit
336 ,p_Anal_Opt_Rec
337 ,x_Anal_Opt_Rec
338 ,p_data_source
339 ,x_return_status
340 ,x_msg_count
341 ,x_msg_data);
342 Retrieve_Analysis_Measures( p_commit
343 ,p_Anal_Opt_Rec
344 ,x_Anal_Opt_Rec
345 ,x_return_status
346 ,x_msg_count
347 ,x_msg_data);
348 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
349 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
350 END IF;
351 EXCEPTION
352 WHEN FND_API.G_EXC_ERROR THEN
353 IF (x_msg_data IS NULL) THEN
354 FND_MSG_PUB.Count_And_Get
355 ( p_encoded => FND_API.G_FALSE
356 , p_count => x_msg_count
357 , p_data => x_msg_data
358 );
359 END IF;
360 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
361 x_return_status := FND_API.G_RET_STS_ERROR;
362 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
363 IF (x_msg_data IS NULL) THEN
364 FND_MSG_PUB.Count_And_Get
365 ( p_encoded => FND_API.G_FALSE
366 , p_count => x_msg_count
367 , p_data => x_msg_data
368 );
369 END IF;
370 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
371 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
372 WHEN NO_DATA_FOUND THEN
373 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
374 IF (x_msg_data IS NOT NULL) THEN
375 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Retrieve_Analysis_Options ';
376 ELSE
377 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Retrieve_Analysis_Options ';
378 END IF;
379 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
380 WHEN OTHERS THEN
381 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
382 IF (x_msg_data IS NOT NULL) THEN
383 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Retrieve_Analysis_Options ';
384 ELSE
385 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Retrieve_Analysis_Options ';
386 END IF;
387 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
388 end Retrieve_Analysis_Options;
389 /************************************************************************************
390 ************************************************************************************/
391
392 procedure Update_Analysis_Options
393 ( p_commit IN varchar2 -- := FND_API.G_FALSE
394 , p_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
395 , p_data_Source IN VARCHAR2
396 , x_return_status OUT NOCOPY VARCHAR2
397 , x_msg_count OUT NOCOPY NUMBER
398 , x_msg_data OUT NOCOPY VARCHAR2
399 ) IS
400 l_Anal_Opt_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
401 l_count NUMBER;
402
403 --get shared indicators
404 CURSOR c_kpi_ids IS
405 SELECT indicator
406 FROM BSC_KPIS_B
407 WHERE Source_Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
408 AND Prototype_Flag <> 2;
409 BEGIN
410 x_return_status := FND_API.G_RET_STS_SUCCESS;
411
412 l_Anal_Opt_Rec := p_Anal_Opt_Rec;
413
414 BSC_ANALYSIS_OPTION_PVT.Update_Analysis_Options( p_commit
415 ,p_Anal_Opt_Rec
416 ,p_data_source
417 ,x_return_status
418 ,x_msg_count
419 ,x_msg_data);
420
421 Update_Analysis_Measures( p_commit
422 ,p_Anal_Opt_Rec
423 ,x_return_status
424 ,x_msg_count
425 ,x_msg_data);
426 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
427 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
428 END IF;
429 -- if there are any shared KPIs update those also.
430 FOR cd IN c_kpi_ids LOOP
431 l_Anal_Opt_Rec.Bsc_Kpi_Id := cd.Indicator;
432 BSC_ANALYSIS_OPTION_PVT.Update_Analysis_Options( p_commit
433 ,l_Anal_Opt_Rec
434 ,p_data_source
435 ,x_return_status
436 ,x_msg_count
437 ,x_msg_data);
438
439 Update_Analysis_Measures( p_commit
440 ,l_Anal_Opt_Rec
441 ,x_return_status
442 ,x_msg_count
443 ,x_msg_data);
444 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
445 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
446 END IF;
447 END LOOP;
448 EXCEPTION
449 WHEN FND_API.G_EXC_ERROR THEN
450 IF (x_msg_data IS NULL) THEN
451 FND_MSG_PUB.Count_And_Get
452 ( p_encoded => FND_API.G_FALSE
453 , p_count => x_msg_count
454 , p_data => x_msg_data
455 );
456 END IF;
457 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
458 x_return_status := FND_API.G_RET_STS_ERROR;
459 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
460 IF (x_msg_data IS NULL) THEN
461 FND_MSG_PUB.Count_And_Get
462 ( p_encoded => FND_API.G_FALSE
463 , p_count => x_msg_count
464 , p_data => x_msg_data
465 );
466 END IF;
467 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
468 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
469 WHEN NO_DATA_FOUND 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_ANALYSIS_OPTION_PUB.Update_Analysis_Options ';
473 ELSE
474 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Update_Analysis_Options ';
475 END IF;
476 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
477 WHEN OTHERS THEN
478 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
479 IF (x_msg_data IS NOT NULL) THEN
480 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Update_Analysis_Options ';
481 ELSE
482 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Update_Analysis_Options ';
483 END IF;
484 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
485 end Update_Analysis_Options;
486
487 /************************************************************************************
488 ************************************************************************************/
489
490 procedure Update_Analysis_Options(
491 p_commit IN varchar2 -- := FND_API.G_FALSE
492 ,p_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
493 ,x_return_status OUT NOCOPY varchar2
494 ,x_msg_count OUT NOCOPY number
495 ,x_msg_data OUT NOCOPY varchar2
496 ) is
497 begin
498 x_return_status := FND_API.G_RET_STS_SUCCESS;
499 Update_Analysis_Options(
500 p_commit => p_commit
501 ,p_Anal_Opt_Rec => p_Anal_Opt_Rec
502 ,p_data_Source => NULL
503 ,x_return_status => x_return_status
504 ,x_msg_count => x_msg_count
505 ,x_msg_data => x_msg_data
506 );
507 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
508 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
509 END IF;
510 IF (p_commit = FND_API.G_TRUE) THEN
511 COMMIT;
512 END IF;
513 EXCEPTION
514 WHEN FND_API.G_EXC_ERROR THEN
515 IF (x_msg_data IS NULL) THEN
516 FND_MSG_PUB.Count_And_Get
517 ( p_encoded => FND_API.G_FALSE
518 , p_count => x_msg_count
519 , p_data => x_msg_data
520 );
521 END IF;
522 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
523 x_return_status := FND_API.G_RET_STS_ERROR;
524 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
525 IF (x_msg_data IS NULL) THEN
526 FND_MSG_PUB.Count_And_Get
527 ( p_encoded => FND_API.G_FALSE
528 , p_count => x_msg_count
529 , p_data => x_msg_data
530 );
531 END IF;
532 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
533 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
534 WHEN NO_DATA_FOUND THEN
535 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
536 IF (x_msg_data IS NOT NULL) THEN
537 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Update_Analysis_Options ';
538 ELSE
539 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Update_Analysis_Options ';
540 END IF;
541 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
542 WHEN OTHERS THEN
543 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
544 IF (x_msg_data IS NOT NULL) THEN
545 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Update_Analysis_Options ';
546 ELSE
547 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Update_Analysis_Options ';
548 END IF;
549 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
550 end Update_Analysis_Options;
551
552 /************************************************************************************
553 ************************************************************************************/
554
555
556 procedure Delete_Analysis_Options(
557 p_commit IN varchar2 -- := FND_API.G_FALSE
558 ,p_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
559 ,x_return_status OUT NOCOPY varchar2
560 ,x_msg_count OUT NOCOPY number
561 ,x_msg_data OUT NOCOPY varchar2
562 ) is
563
564 begin
565 x_return_status := FND_API.G_RET_STS_SUCCESS;
566 BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Options( p_commit
567 ,p_Anal_Opt_Rec
568 ,x_return_status
569 ,x_msg_count
570 ,x_msg_data);
571
572 EXCEPTION
573 WHEN FND_API.G_EXC_ERROR THEN
574 IF (x_msg_data IS NULL) THEN
575 FND_MSG_PUB.Count_And_Get
576 ( p_encoded => FND_API.G_FALSE
577 , p_count => x_msg_count
578 , p_data => x_msg_data
579 );
580 END IF;
581 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
582 x_return_status := FND_API.G_RET_STS_ERROR;
583 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
584 IF (x_msg_data IS NULL) THEN
585 FND_MSG_PUB.Count_And_Get
586 ( p_encoded => FND_API.G_FALSE
587 , p_count => x_msg_count
588 , p_data => x_msg_data
589 );
590 END IF;
591 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
592 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
593 WHEN NO_DATA_FOUND THEN
594 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
595 IF (x_msg_data IS NOT NULL) THEN
596 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Delete_Analysis_Options ';
597 ELSE
598 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Delete_Analysis_Options ';
599 END IF;
600 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
601 WHEN OTHERS THEN
602 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
603 IF (x_msg_data IS NOT NULL) THEN
604 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Delete_Analysis_Options ';
605 ELSE
606 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Delete_Analysis_Options ';
607 END IF;
608 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
609 end Delete_Analysis_Options;
610
611 /************************************************************************************
612 ************************************************************************************/
613
614 --: This procedure assigns the given measure to the given analysis option.
615 --: This procedure is part of the Analysis Option API.
616
617 procedure Create_Analysis_Measures(
618 p_commit IN VARCHAR2 -- := FND_API.G_FALSE
619 ,p_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
620 ,x_return_status OUT NOCOPY varchar2
621 ,x_msg_count OUT NOCOPY number
622 ,x_msg_data OUT NOCOPY varchar2
623 ) is
624
625 l_Anal_Opt_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
626 l_Kpi_Measure_Id bsc_kpi_analysis_measures_b.Kpi_Measure_Id%TYPE;
627 l_Default_Value NUMBER;
628 l_commit VARCHAR2(2) := FND_API.G_FALSE;
629 begin
630 x_return_status := FND_API.G_RET_STS_SUCCESS;
631 SAVEPOINT Create_Analysis_Measures_PUB;
632 -- set all values of local record equal to the ones passed.
633 l_Anal_Opt_Rec := p_Anal_Opt_Rec;
634
635 SELECT BSC_KPI_MEASURE_S.nextval
636 INTO l_Kpi_Measure_Id
637 FROM SYS.DUAL;
638
639 IF l_Anal_Opt_Rec.Bsc_Kpi_Measure_Id IS NULL THEN
640 l_Anal_Opt_Rec.Bsc_Kpi_Measure_Id := l_Kpi_Measure_Id;
641 END IF;
642
643 -- Default Prototype Flag for color calculation
644 IF l_Anal_Opt_Rec.Bsc_Measure_Prototype_Flag IS NULL THEN
645 l_Anal_Opt_Rec.Bsc_Measure_Prototype_Flag := BSC_DESIGNER_PVT.C_COLOR_CHANGE;
646 END IF;
647
648 BSC_ANALYSIS_OPTION_PVT.Cascade_Series_Default_Value (
649 p_Commit => l_commit
650 , p_Api_Mode => BSC_ANALYSIS_OPTION_PVT.C_API_CREATE
651 , p_Kpi_Id => l_Anal_Opt_Rec.Bsc_Kpi_Id
652 , p_Option0 => NVL(l_Anal_Opt_Rec.Bsc_Option_Group0, 0)
653 , p_Option1 => NVL(l_Anal_Opt_Rec.Bsc_Option_Group1, 0)
654 , p_Option2 => NVL(l_Anal_Opt_Rec.Bsc_Option_Group2, 0)
655 , p_Series_Id => NVL(l_Anal_Opt_Rec.Bsc_Dataset_Series_Id, 0)
656 , p_Default_Value => NVL(l_Anal_Opt_Rec.Bsc_Dataset_Default_Value, 0)
657 , x_Default_Value => l_Default_Value -- nocopied
658 , x_Return_Status => x_Return_Status
659 , x_Msg_Count => x_Msg_Count
660 , x_Msg_Data => x_Msg_Data
661 );
662
663 l_Anal_Opt_Rec.Bsc_Dataset_Default_Value := l_Default_Value;
664
665
666 -- Call private version of the procedure.
667 BSC_ANALYSIS_OPTION_PVT.Create_Analysis_Measures( l_commit
668 ,l_Anal_Opt_Rec
669 ,x_return_status
670 ,x_msg_count
671 ,x_msg_data);
672 --Use this and populate l_Anal_Opt_Rec.Bsc_Kpi_Measure_Id
673
674 BSC_KPI_MEASURE_PROPS_PUB.Create_Default_Kpi_Meas_Props (
675 p_commit => l_commit
676 ,p_objective_id => l_Anal_Opt_Rec.Bsc_Kpi_Id
677 ,p_kpi_measure_id => l_Kpi_Measure_Id
678 ,p_cascade_shared => FALSE
679 ,x_return_status => x_return_status
680 ,x_msg_count => x_msg_count
681 ,x_msg_data => x_Msg_Data
682 );
683
684 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
685 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
686 END IF;
687
688 BSC_COLOR_RANGES_PUB.Create_Def_Color_Prop_Ranges(
689 p_commit => l_commit
690 ,p_objective_id => l_Anal_Opt_Rec.Bsc_Kpi_Id
691 ,p_kpi_measure_id => l_Kpi_Measure_Id
692 ,p_cascade_shared => FALSE
693 ,x_return_status => x_return_status
694 ,x_msg_count => x_msg_count
695 ,x_msg_data => x_Msg_Data
696 );
697
698 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
699 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
700 END IF;
701
702 IF(l_commit = FND_API.G_TRUE) THEN
703 COMMIT;
704 END IF;
705
706 EXCEPTION
707 WHEN FND_API.G_EXC_ERROR THEN
708 ROLLBACK TO Create_Analysis_Measures_PUB;
709 IF (x_msg_data IS NULL) THEN
710 FND_MSG_PUB.Count_And_Get
711 ( p_encoded => FND_API.G_FALSE
712 , p_count => x_msg_count
713 , p_data => x_msg_data
714 );
715 END IF;
716 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
717 x_return_status := FND_API.G_RET_STS_ERROR;
718 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
719 ROLLBACK TO Create_Analysis_Measures_PUB;
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_UNEXP_ERROR;
728 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
729 WHEN NO_DATA_FOUND THEN
730 ROLLBACK TO Create_Analysis_Measures_PUB;
731 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
732 IF (x_msg_data IS NOT NULL) THEN
733 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Create_Analysis_Measures ';
734 ELSE
735 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Create_Analysis_Measures ';
736 END IF;
737 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
738 WHEN OTHERS THEN
739 ROLLBACK TO Create_Analysis_Measures_PUB;
740 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
741 IF (x_msg_data IS NOT NULL) THEN
742 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Create_Analysis_Measures ';
743 ELSE
744 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Create_Analysis_Measures ';
745 END IF;
746 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
747 end Create_Analysis_Measures;
748
749 /************************************************************************************
750 ************************************************************************************/
751
752 procedure Retrieve_Analysis_Measures(
753 p_commit IN varchar2 -- := FND_API.G_FALSE
754 ,p_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
755 ,x_Anal_Opt_Rec IN OUT NOCOPY BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
756 ,x_return_status OUT NOCOPY varchar2
757 ,x_msg_count OUT NOCOPY number
758 ,x_msg_data OUT NOCOPY varchar2
759 ) is
760
761 begin
762 x_return_status := FND_API.G_RET_STS_SUCCESS;
763 BSC_ANALYSIS_OPTION_PVT.Retrieve_Analysis_Measures( p_commit
764 ,p_Anal_Opt_Rec
765 ,x_Anal_Opt_Rec
766 ,x_return_status
767 ,x_msg_count
768 ,x_msg_data);
769
770 EXCEPTION
771 WHEN FND_API.G_EXC_ERROR THEN
772 IF (x_msg_data IS NULL) THEN
773 FND_MSG_PUB.Count_And_Get
774 ( p_encoded => FND_API.G_FALSE
775 , p_count => x_msg_count
776 , p_data => x_msg_data
777 );
778 END IF;
779 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
780 x_return_status := FND_API.G_RET_STS_ERROR;
781 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
782 IF (x_msg_data IS NULL) THEN
783 FND_MSG_PUB.Count_And_Get
784 ( p_encoded => FND_API.G_FALSE
785 , p_count => x_msg_count
786 , p_data => x_msg_data
787 );
788 END IF;
789 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
790 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
791 WHEN NO_DATA_FOUND THEN
792 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
793 IF (x_msg_data IS NOT NULL) THEN
794 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Retrieve_Analysis_Measures ';
795 ELSE
796 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Retrieve_Analysis_Measures ';
797 END IF;
798 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
799 WHEN OTHERS THEN
800 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
801 IF (x_msg_data IS NOT NULL) THEN
802 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Retrieve_Analysis_Measures ';
803 ELSE
804 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Retrieve_Analysis_Measures ';
805 END IF;
806 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
807 end Retrieve_Analysis_Measures;
808
809 /************************************************************************************
810 ************************************************************************************/
811
812 procedure Update_Analysis_Measures(
813 p_commit IN varchar2 -- := FND_API.G_FALSE
814 ,p_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
815 ,x_return_status OUT NOCOPY varchar2
816 ,x_msg_count OUT NOCOPY number
817 ,x_msg_data OUT NOCOPY varchar2
818 ) is
819
820 l_Anal_Opt_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
821 l_Default_Value NUMBER;
822
823 begin
824 x_return_status := FND_API.G_RET_STS_SUCCESS;
825
826 l_Anal_Opt_Rec := p_Anal_Opt_Rec;
827
828 BSC_ANALYSIS_OPTION_PVT.Cascade_Series_Default_Value (
829 p_Commit => p_Commit
830 , p_Api_Mode => BSC_ANALYSIS_OPTION_PVT.C_API_UPDATE
831 , p_Kpi_Id => l_Anal_Opt_Rec.Bsc_Kpi_Id
832 , p_Option0 => NVL(l_Anal_Opt_Rec.Bsc_Option_Group0, 0)
833 , p_Option1 => NVL(l_Anal_Opt_Rec.Bsc_Option_Group1, 0)
834 , p_Option2 => NVL(l_Anal_Opt_Rec.Bsc_Option_Group2, 0)
835 , p_Series_Id => NVL(l_Anal_Opt_Rec.Bsc_Dataset_Series_Id, 0)
836 , p_Default_Value => NVL(l_Anal_Opt_Rec.Bsc_Dataset_Default_Value, 0)
837 , x_Default_Value => l_Default_Value
838 , x_Return_Status => x_Return_Status
839 , x_Msg_Count => x_Msg_Count
840 , x_Msg_Data => x_Msg_Data
841 );
842
843 l_Anal_Opt_Rec.Bsc_Dataset_Default_Value := l_Default_Value;
844
845
846 BSC_ANALYSIS_OPTION_PVT.Update_Analysis_Measures( p_commit
847 ,l_Anal_Opt_Rec
848 ,x_return_status
849 ,x_msg_count
850 ,x_msg_data);
851 EXCEPTION
852 WHEN FND_API.G_EXC_ERROR THEN
853 IF (x_msg_data IS NULL) THEN
854 FND_MSG_PUB.Count_And_Get
855 ( p_encoded => FND_API.G_FALSE
856 , p_count => x_msg_count
857 , p_data => x_msg_data
858 );
859 END IF;
860 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
861 x_return_status := FND_API.G_RET_STS_ERROR;
862 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
863 IF (x_msg_data IS NULL) THEN
864 FND_MSG_PUB.Count_And_Get
865 ( p_encoded => FND_API.G_FALSE
866 , p_count => x_msg_count
867 , p_data => x_msg_data
868 );
869 END IF;
870 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
871 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
872 WHEN NO_DATA_FOUND THEN
873 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
874 IF (x_msg_data IS NOT NULL) THEN
875 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Update_Analysis_Measures ';
876 ELSE
877 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Update_Analysis_Measures ';
878 END IF;
879 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
880 WHEN OTHERS THEN
881 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
882 IF (x_msg_data IS NOT NULL) THEN
883 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Update_Analysis_Measures ';
884 ELSE
885 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Update_Analysis_Measures ';
886 END IF;
887 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
888 end Update_Analysis_Measures;
889
890 /************************************************************************************
891 -- API name : Cascade_Deletion_Color_Props
892 -- Type : Public
893 -- Function :
894 ************************************************************************************/
895
896 PROCEDURE Cascade_Deletion_Color_Props (
897 p_commit IN VARCHAR2 := FND_API.G_FALSE
898 ,p_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
899 ,x_return_status OUT NOCOPY varchar2
900 ,x_msg_count OUT NOCOPY number
901 ,x_msg_data OUT NOCOPY varchar2
902 ) IS
903
904 CURSOR c_Removed_Kpis IS
905 SELECT
906 kpi_measure_id
907 FROM
908 bsc_kpi_measure_props
909 WHERE
910 indicator = p_Anal_Opt_Rec.Bsc_Kpi_id
911 MINUS
912 SELECT
913 kpi_measure_id
914 FROM
915 bsc_kpi_analysis_measures_b
916 WHERE
917 indicator = p_Anal_Opt_Rec.Bsc_Kpi_id;
918 BEGIN
919
920 FOR cd in c_Removed_Kpis LOOP
921 BSC_KPI_MEASURE_PROPS_PUB.Delete_Kpi_Measure_Props (
922 p_commit => FND_API.G_FALSE
923 ,p_objective_id => p_Anal_Opt_Rec.Bsc_Kpi_Id
924 ,p_kpi_measure_id => cd.kpi_measure_id
925 ,p_cascade_shared => FALSE
926 ,x_return_status => x_return_status
927 ,x_msg_count => x_msg_count
928 ,x_msg_data => x_Msg_Data
929 );
930 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
931 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
932 END IF;
933
934 BSC_COLOR_RANGES_PUB.Delete_Color_Prop_Ranges (
935 p_commit => FND_API.G_FALSE
936 ,p_objective_id => p_Anal_Opt_Rec.Bsc_Kpi_Id
937 ,p_kpi_measure_id => cd.kpi_measure_id
938 ,p_cascade_shared => FALSE
939 ,x_return_status => x_return_status
940 ,x_msg_count => x_msg_count
941 ,x_msg_data => x_Msg_Data
942 );
943 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
944 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
945 END IF;
946
947 BSC_KPI_MEASURE_WEIGHTS_PUB.Del_Kpi_Measure_Weights (
948 p_commit => FND_API.G_FALSE
949 ,p_objective_id => p_Anal_Opt_Rec.Bsc_Kpi_Id
950 ,p_kpi_measure_id => cd.kpi_measure_id
951 ,p_cascade_shared => FALSE
952 ,x_return_status => x_return_status
953 ,x_msg_count => x_msg_count
954 ,x_msg_data => x_Msg_Data
955 );
956 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
957 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
958 END IF;
959
960 DELETE FROM bsc_sys_kpi_colors
961 WHERE indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id AND
962 kpi_measure_id = cd.kpi_measure_id;
963
964 END LOOP;
965
966 IF FND_API.To_Boolean( p_commit ) THEN
967 COMMIT;
968 END IF;
969
970 EXCEPTION
971 WHEN OTHERS THEN
972 ROLLBACK TO Create_Analayis_OptionObjPUB;
973 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
974 IF (x_msg_data IS NOT NULL) THEN
975 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PVT.Cascade_Deletion_Color_Props ';
976 ELSE
977 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PVT.Cascade_Deletion_Color_Props ';
978 END IF;
979 END Cascade_Deletion_Color_Props;
980
981 /************************************************************************************
982 ************************************************************************************/
983
984 procedure Delete_Analysis_Measures(
985 p_commit IN varchar2 -- := FND_API.G_FALSE
986 ,p_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
987 ,x_return_status OUT NOCOPY varchar2
988 ,x_msg_count OUT NOCOPY number
989 ,x_msg_data OUT NOCOPY varchar2
990 ) is
991 begin
992
993 x_return_status := FND_API.G_RET_STS_SUCCESS;
994
995
996 BSC_ANALYSIS_OPTION_PVT.Delete_Analysis_Measures( p_commit
997 ,p_Anal_Opt_Rec
998 ,x_return_status
999 ,x_msg_count
1000 ,x_msg_data);
1001
1002 Cascade_Deletion_Color_Props (
1003 p_commit => p_commit
1004 ,p_Anal_Opt_Rec => p_Anal_Opt_Rec
1005 ,x_return_status => x_return_status
1006 ,x_msg_count => x_msg_count
1007 ,x_msg_data => x_msg_data
1008 ) ;
1009
1010 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1011 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1012 END IF;
1013
1014 IF FND_API.To_Boolean( p_commit ) THEN
1015 COMMIT;
1016 END IF;
1017
1018 EXCEPTION
1019 WHEN FND_API.G_EXC_ERROR THEN
1020 IF (x_msg_data IS NULL) THEN
1021 FND_MSG_PUB.Count_And_Get
1022 ( p_encoded => FND_API.G_FALSE
1023 , p_count => x_msg_count
1024 , p_data => x_msg_data
1025 );
1026 END IF;
1027 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1028 x_return_status := FND_API.G_RET_STS_ERROR;
1029 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1030 IF (x_msg_data IS NULL) THEN
1031 FND_MSG_PUB.Count_And_Get
1032 ( p_encoded => FND_API.G_FALSE
1033 , p_count => x_msg_count
1034 , p_data => x_msg_data
1035 );
1036 END IF;
1037 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1038 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1039 WHEN NO_DATA_FOUND THEN
1040 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1041 IF (x_msg_data IS NOT NULL) THEN
1042 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Delete_Analysis_Measures ';
1043 ELSE
1044 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Delete_Analysis_Measures ';
1045 END IF;
1046 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1047 WHEN OTHERS THEN
1048 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1049 IF (x_msg_data IS NOT NULL) THEN
1050 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Delete_Analysis_Measures ';
1051 ELSE
1052 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Delete_Analysis_Measures ';
1053 END IF;
1054 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1055 end Delete_Analysis_Measures;
1056
1057 /************************************************************************************
1058 ************************************************************************************/
1059 PROCEDURE Delete_Ana_Opt_Mult_Groups
1060 ( p_commit IN VARCHAR2:=FND_API.G_FALSE
1061 , p_Kpi_id IN BSC_KPIS_B.indicator%TYPE
1062 , p_Anal_Opt_Tbl IN BSC_ANALYSIS_OPTION_PUB.Bsc_Anal_Opt_Tbl_Type
1063 , p_max_group_count IN NUMBER
1064 , p_Anal_Opt_Comb_Tbl IN BSC_ANALYSIS_OPTION_PUB.Anal_Opt_Comb_Num_Tbl_Type
1065 , p_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
1066 , x_return_status OUT NOCOPY VARCHAR2
1067 , x_msg_count OUT NOCOPY NUMBER
1068 , x_msg_data OUT NOCOPY VARCHAR2
1069 )IS
1070 BEGIN
1071 x_return_status := FND_API.G_RET_STS_SUCCESS;
1072 BSC_ANALYSIS_OPTION_PVT.Delete_Ana_Opt_Mult_Groups
1073 ( p_commit => FND_API.G_FALSE
1074 , p_Kpi_id => p_Kpi_id
1075 , p_Anal_Opt_Tbl => p_Anal_Opt_Tbl
1076 , p_max_group_count => p_max_group_count
1077 , p_Anal_Opt_Comb_Tbl => p_Anal_Opt_Comb_Tbl
1078 , p_Anal_Opt_Rec => p_Anal_Opt_Rec
1079 , x_return_status => x_return_status
1080 , x_msg_count => x_msg_count
1081 , x_msg_data => x_msg_data
1082 );
1083 EXCEPTION
1084 WHEN FND_API.G_EXC_ERROR THEN
1085 IF (x_msg_data IS NULL) THEN
1086 FND_MSG_PUB.Count_And_Get
1087 ( p_encoded => FND_API.G_FALSE
1088 , p_count => x_msg_count
1089 , p_data => x_msg_data
1090 );
1091 END IF;
1092 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1093 x_return_status := FND_API.G_RET_STS_ERROR;
1094 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1095 IF (x_msg_data IS NULL) THEN
1096 FND_MSG_PUB.Count_And_Get
1097 ( p_encoded => FND_API.G_FALSE
1098 , p_count => x_msg_count
1099 , p_data => x_msg_data
1100 );
1101 END IF;
1102 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1103 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1104 WHEN NO_DATA_FOUND THEN
1105 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1106 IF (x_msg_data IS NOT NULL) THEN
1107 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Delete_Ana_Opt_Mult_Groups ';
1108 ELSE
1109 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Delete_Ana_Opt_Mult_Groups ';
1110 END IF;
1111 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1112 WHEN OTHERS THEN
1113 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1114 IF (x_msg_data IS NOT NULL) THEN
1115 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Delete_Ana_Opt_Mult_Groups ';
1116 ELSE
1117 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Delete_Ana_Opt_Mult_Groups ';
1118 END IF;
1119 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1120 END Delete_Ana_Opt_Mult_Groups;
1121 /************************************************************************************
1122 ************************************************************************************/
1123
1124 PROCEDURE Synch_Kpi_Anal_Group
1125 (
1126 p_commit IN VARCHAR2:=FND_API.G_FALSE
1127 , p_Kpi_Id IN BSC_KPIS_B.indicator%TYPE
1128 , p_Anal_Opt_Tbl IN BSC_ANALYSIS_OPTION_PUB.Bsc_Anal_Opt_Tbl_Type
1129 , x_return_status OUT NOCOPY VARCHAR2
1130 , x_msg_count OUT NOCOPY NUMBER
1131 , x_msg_data OUT NOCOPY VARCHAR2
1132 )IS
1133 BEGIN
1134
1135 x_return_status := FND_API.G_RET_STS_SUCCESS;
1136 BSC_ANALYSIS_OPTION_PVT.Synch_Kpi_Anal_Group
1137 ( p_commit => FND_API.G_FALSE
1138 , p_Kpi_Id => p_Kpi_Id
1139 , p_Anal_Opt_Tbl => p_Anal_Opt_Tbl
1140 , x_return_status => x_return_status
1141 , x_msg_count => x_msg_count
1142 , x_msg_data => x_msg_data
1143 );
1144
1145 EXCEPTION
1146 WHEN FND_API.G_EXC_ERROR THEN
1147 IF (x_msg_data IS NULL) THEN
1148 FND_MSG_PUB.Count_And_Get
1149 ( p_encoded => FND_API.G_FALSE
1150 , p_count => x_msg_count
1151 , p_data => x_msg_data
1152 );
1153 END IF;
1154 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1155 x_return_status := FND_API.G_RET_STS_ERROR;
1156 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1157 IF (x_msg_data IS NULL) THEN
1158 FND_MSG_PUB.Count_And_Get
1159 ( p_encoded => FND_API.G_FALSE
1160 , p_count => x_msg_count
1161 , p_data => x_msg_data
1162 );
1163 END IF;
1164 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1165 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1166 WHEN NO_DATA_FOUND THEN
1167 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1168 IF (x_msg_data IS NOT NULL) THEN
1169 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Synch_Kpi_Anal_Group ';
1170 ELSE
1171 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Synch_Kpi_Anal_Group ';
1172 END IF;
1173 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1174 WHEN OTHERS THEN
1175 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1176 IF (x_msg_data IS NOT NULL) THEN
1177 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Synch_Kpi_Anal_Group ';
1178 ELSE
1179 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Synch_Kpi_Anal_Group ';
1180 END IF;
1181 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1182 END Synch_Kpi_Anal_Group;
1183
1184 --ADDED BY RAVI
1185
1186
1187 PROCEDURE store_anal_opt_grp_count
1188 ( p_kpi_id IN NUMBER
1189 , x_Anal_Opt_Tbl IN OUT NOCOPY BSC_ANALYSIS_OPTION_PUB.Bsc_Anal_Opt_Tbl_Type
1190 ) IS
1191 BEGIN
1192
1193 BSC_ANALYSIS_OPTION_PVT.store_anal_opt_grp_count
1194 (
1195 p_kpi_id => p_kpi_id
1196 , x_Anal_Opt_Tbl => x_Anal_Opt_Tbl
1197 );
1198
1199 END store_anal_opt_grp_count;
1200
1201
1202 /************************************************************************************
1203 ************************************************************************************/
1204 PROCEDURE Validate_Custom_Measure
1205 ( p_kpi_id IN BSC_OAF_ANALYSYS_OPT_COMB_V.INDICATOR%TYPE
1206 , p_option0 IN BSC_OAF_ANALYSYS_OPT_COMB_V.ANALYSIS_OPTION0%TYPE
1207 , p_option1 IN BSC_OAF_ANALYSYS_OPT_COMB_V.ANALYSIS_OPTION1%TYPE
1208 , p_option2 IN BSC_OAF_ANALYSYS_OPT_COMB_V.ANALYSIS_OPTION2%TYPE
1209 , p_series_id IN BSC_OAF_ANALYSYS_OPT_COMB_V.SERIES_ID%TYPE
1210 , x_return_status OUT NOCOPY VARCHAR2
1211 , x_msg_count OUT NOCOPY NUMBER
1212 , x_msg_data OUT NOCOPY VARCHAR2
1213 ) IS
1214 l_Kpi_Short_Name BSC_KPIS_B.SHORT_NAME%TYPE;
1215 l_Kpi_Name BSC_KPIS_VL.NAME%TYPE;
1216 l_Dataseries_Name BSC_KPI_ANALYSIS_MEASURES_VL.NAME%TYPE;
1217
1218 CURSOR c_Objective_Name_Details IS
1219 SELECT OBJ.NAME OBJ_NAME,
1220 OBJ.SHORT_NAME SHORT_NAME,
1221 DS.NAME KPI_NAME
1222 FROM BSC_KPIS_VL OBJ,
1223 BSC_KPI_ANALYSIS_MEASURES_VL DS
1224 WHERE DS.INDICATOR = p_kpi_id
1225 AND DS.ANALYSIS_OPTION0 = p_option0
1226 AND DS.ANALYSIS_OPTION1 = p_option1
1227 AND DS.ANALYSIS_OPTION2 = p_option2
1228 AND DS.SERIES_ID = p_series_id
1229 AND OBJ.INDICATOR = DS.INDICATOR;
1230 BEGIN
1231 FND_MSG_PUB.Initialize;
1232 x_return_status := FND_API.G_RET_STS_SUCCESS;
1233
1234 -- adrao modified cursor and introduced new call to is_Objective_Report_Type, which is appropriate.
1235 -- Bug#4357962
1236 FOR cSN IN c_Objective_Name_Details LOOP
1237 l_Kpi_Short_Name := cSN.SHORT_NAME;
1238 l_Kpi_Name := cSN.OBJ_NAME;
1239 l_Dataseries_Name := cSN.KPI_NAME;
1240
1241 -- Changed message for Bug#4590994
1242 IF (l_Kpi_Short_Name IS NOT NULL) THEN
1243 IF (BSC_BIS_CUSTOM_KPI_UTIL_PUB.is_Objective_Report_Type(l_Kpi_Short_Name) = FND_API.G_TRUE) THEN
1244 FND_MESSAGE.SET_NAME('BSC','BSC_D_DELETE_RPT_KPI_OBJ');
1245 FND_MESSAGE.SET_TOKEN('OBJECTIVE', l_Kpi_Name);
1246 FND_MSG_PUB.ADD;
1247 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1248 END IF;
1249 END IF;
1250 END LOOP;
1251
1252 BSC_ANALYSIS_OPTION_PVT.Validate_Custom_Measure
1253 ( p_Kpi_id => p_Kpi_id
1254 , p_option0 => p_option0
1255 , p_option1 => p_option1
1256 , p_option2 => p_option2
1257 , p_series_id => p_series_id
1258 , x_return_status => x_return_status
1259 , x_msg_count => x_msg_count
1260 , x_msg_data => x_msg_data
1261 );
1262 EXCEPTION
1263 WHEN FND_API.G_EXC_ERROR THEN
1264 IF (x_msg_data IS NULL) THEN
1265 FND_MSG_PUB.Count_And_Get
1266 ( p_encoded => FND_API.G_FALSE
1267 , p_count => x_msg_count
1268 , p_data => x_msg_data
1269 );
1270 END IF;
1271 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1272 x_return_status := FND_API.G_RET_STS_ERROR;
1273 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1274 IF (x_msg_data IS NULL) THEN
1275 FND_MSG_PUB.Count_And_Get
1276 ( p_encoded => FND_API.G_FALSE
1277 , p_count => x_msg_count
1278 , p_data => x_msg_data
1279 );
1280 END IF;
1281 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1282 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1283 WHEN NO_DATA_FOUND THEN
1284 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1285 IF (x_msg_data IS NOT NULL) THEN
1286 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Delete_Ana_Opt_Mult_Groups ';
1287 ELSE
1288 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Delete_Ana_Opt_Mult_Groups ';
1289 END IF;
1290 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1291 WHEN OTHERS THEN
1292 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1293 IF (x_msg_data IS NOT NULL) THEN
1294 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Delete_Ana_Opt_Mult_Groups ';
1295 ELSE
1296 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Delete_Ana_Opt_Mult_Groups ';
1297 END IF;
1298 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1299 END Validate_Custom_Measure;
1300 /************************************************************************************
1301 ************************************************************************************/
1302
1303 PROCEDURE delete_extra_series(
1304 p_Bsc_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
1305 , x_return_status OUT NOCOPY VARCHAR2
1306 , x_msg_count OUT NOCOPY NUMBER
1307 , x_msg_data OUT NOCOPY VARCHAR2
1308 ) IS
1309 BEGIN
1310 x_return_status := FND_API.G_RET_STS_SUCCESS;
1311 FND_MSG_PUB.Initialize;
1312
1313 BSC_ANALYSIS_OPTION_PVT.delete_extra_series(
1314 p_Bsc_Anal_Opt_Rec => p_Bsc_Anal_Opt_Rec
1315 , x_return_status => x_return_status
1316 , x_msg_count => x_msg_count
1317 , x_msg_data => x_msg_data
1318 );
1319 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1320 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1321 END IF;
1322
1323
1324 EXCEPTION
1325 WHEN FND_API.G_EXC_ERROR THEN
1326 IF (x_msg_data IS NULL) THEN
1327 FND_MSG_PUB.Count_And_Get
1328 ( p_encoded => FND_API.G_FALSE
1329 , p_count => x_msg_count
1330 , p_data => x_msg_data
1331 );
1332 END IF;
1333 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1334 x_return_status := FND_API.G_RET_STS_ERROR;
1335 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1336 IF (x_msg_data IS NULL) THEN
1337 FND_MSG_PUB.Count_And_Get
1338 ( p_encoded => FND_API.G_FALSE
1339 , p_count => x_msg_count
1340 , p_data => x_msg_data
1341 );
1342 END IF;
1343 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1344 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1345 WHEN NO_DATA_FOUND THEN
1346 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1347 IF (x_msg_data IS NOT NULL) THEN
1348 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Delete_Ana_Opt_Mult_Groups ';
1349 ELSE
1350 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Delete_Ana_Opt_Mult_Groups ';
1351 END IF;
1352 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1353 WHEN OTHERS THEN
1354 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1355 IF (x_msg_data IS NOT NULL) THEN
1356 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Delete_Ana_Opt_Mult_Groups ';
1357 ELSE
1358 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Delete_Ana_Opt_Mult_Groups ';
1359 END IF;
1360 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1361 END delete_extra_series;
1362
1363 --------------------------------------------------------------------------------
1364
1365 PROCEDURE Create_Data_Series
1366 ( p_commit IN VARCHAR2 -- FND_API.G_FALSE
1367 , p_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
1368 , x_Anal_Opt_Rec OUT NOCOPY BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
1369 , x_return_status OUT NOCOPY VARCHAR2
1370 , x_msg_count OUT NOCOPY NUMBER
1371 , x_msg_data OUT NOCOPY VARCHAR2
1372 ) IS
1373 l_Anal_Opt_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
1374 l_share_flag NUMBER;
1375 l_count NUMBER;
1376 l_series_color NUMBER;
1377 l_BM_color NUMBER;
1378 l_max_series_id NUMBER;
1379 l_series_id NUMBER;
1380
1381 --get shared indicators
1382 CURSOR c_kpi_ids IS
1383 SELECT indicator
1384 FROM BSC_KPIS_B
1385 WHERE Source_Indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
1386 AND Prototype_Flag <> 2;
1387
1388 CURSOR c_Series_color IS
1389 SELECT SERIES_COLOR, BM_COLOR
1390 FROM BSC_SYS_SERIES_COLORS
1391 WHERE SERIES_ID = l_series_id;
1392
1393 -- Get the Data Series Ids using Default mesures
1394 CURSOR c_Default_Data_Series IS
1395 SELECT SERIES_ID
1396 INTO l_count
1397 FROM BSC_KPI_ANALYSIS_MEASURES_B
1398 WHERE indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
1399 AND analysis_option0 = l_Anal_Opt_Rec.Bsc_Option_Group0
1400 AND analysis_option1 = l_Anal_Opt_Rec.Bsc_Option_Group1
1401 AND analysis_option2 = l_Anal_Opt_Rec.Bsc_Option_Group2
1402 AND dataset_id = -1
1403 ORDER BY SERIES_ID DESC;
1404
1405
1406 begin
1407 FND_MSG_PUB.Initialize;
1408 x_return_status := FND_API.G_RET_STS_SUCCESS;
1409 -- Assign all values in the passed "Record" parameter to the locally defined
1410 -- "Record" variable.
1411 l_Anal_Opt_Rec := p_Anal_Opt_Rec;
1412
1413 --- Check Objective Id
1414 if p_Anal_Opt_Rec.Bsc_Kpi_Id is not null then
1415 SELECT COUNT(0)
1416 INTO l_count
1417 FROM BSC_KPIS_B
1418 WHERE INDICATOR = p_Anal_Opt_Rec.Bsc_Kpi_Id;
1419 if l_count = 0 then
1420 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_KPI_ID');
1421 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Anal_Opt_Rec.Bsc_Kpi_Id);
1422 FND_MSG_PUB.ADD;
1423 RAISE FND_API.G_EXC_ERROR;
1424 end if;
1425 else
1426 FND_MESSAGE.SET_NAME('BSC','BSC_NO_KPI_ID_ENTERED');
1427 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Anal_Opt_Rec.Bsc_Kpi_Id);
1428 FND_MSG_PUB.ADD;
1429 RAISE FND_API.G_EXC_ERROR;
1430 end if;
1431
1432 -- Verify that this is not a Shared KPI.
1433 select share_flag
1434 into l_share_flag
1435 from BSC_KPIS_B
1436 where indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id;
1437
1438 if l_share_flag = 2 then
1439 FND_MESSAGE.SET_NAME('BSC','BSC_SHARED_KPI');
1440 FND_MESSAGE.SET_TOKEN('BSC_KPI', l_Anal_Opt_Rec.Bsc_Kpi_Id);
1441 FND_MSG_PUB.ADD;
1442 RAISE FND_API.G_EXC_ERROR;
1443 end if;
1444
1445 -- Set Default values for Anaysis options parameter
1446 if l_Anal_Opt_Rec.Bsc_Option_Group0 is null then
1447 l_Anal_Opt_Rec.Bsc_Option_Group0 := 0;
1448 end if;
1449 if l_Anal_Opt_Rec.Bsc_Option_Group1 is null then
1450 l_Anal_Opt_Rec.Bsc_Option_Group1 := 0;
1451 end if;
1452 if l_Anal_Opt_Rec.Bsc_Option_Group2 is null then
1453 l_Anal_Opt_Rec.Bsc_Option_Group2 := 0;
1454 end if;
1455
1456 -- If there is no current Data set then set the data set equal to -1, and set the name
1457 -- of the measure to a default name.
1458 if l_Anal_Opt_Rec.Bsc_Dataset_Id is null then
1459 l_Anal_Opt_Rec.Bsc_Dataset_Id := -1;
1460 l_Anal_Opt_Rec.Bsc_Measure_Long_Name := BSC_APPS.GET_LOOKUP_VALUE('BSC_UI_COMMON', 'DEFAULT') ||
1461 ' ' || BSC_APPS.GET_LOOKUP_VALUE('BSC_UI_COMMON', 'EDW_MEASURE');
1462
1463 l_Anal_Opt_Rec.Bsc_Measure_Help := l_Anal_Opt_Rec.Bsc_Measure_Long_Name;
1464 end if;
1465
1466 -- Delete Default measures asociated to the Analysis Option
1467 if l_Anal_Opt_Rec.Bsc_Dataset_Id <> -1 then
1468 FOR CD IN c_Default_Data_Series LOOP
1469 l_Anal_Opt_Rec.Bsc_Dataset_Series_Id := CD.SERIES_ID;
1470 l_Anal_Opt_Rec.Bsc_New_Kpi := 'Y';
1471 Delete_Data_Series(
1472 p_commit => p_commit
1473 ,p_Anal_Opt_Rec => l_Anal_Opt_Rec
1474 ,x_return_status => x_return_status
1475 ,x_msg_count => x_msg_count
1476 ,x_msg_data => x_msg_data
1477 );
1478 END LOOP;
1479 end if;
1480 l_Anal_Opt_Rec.Bsc_Dataset_Series_Id := p_Anal_Opt_Rec.Bsc_Dataset_Series_Id;
1481
1482 -- set the Series Id
1483 if l_Anal_Opt_Rec.Bsc_Dataset_Series_Id is null then
1484 SELECT COUNT (SERIES_ID)
1485 INTO l_count
1486 FROM BSC_KPI_ANALYSIS_MEASURES_B
1487 WHERE indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
1488 AND analysis_option0 = l_Anal_Opt_Rec.Bsc_Option_Group0
1489 AND analysis_option1 = l_Anal_Opt_Rec.Bsc_Option_Group1
1490 AND analysis_option2 = l_Anal_Opt_Rec.Bsc_Option_Group2;
1491 IF l_count <> 0 then
1492 SELECT MAX(SERIES_ID) + 1
1493 into l_Anal_Opt_Rec.Bsc_Dataset_Series_Id
1494 from BSC_KPI_ANALYSIS_MEASURES_B
1495 WHERE indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
1496 AND analysis_option0 = l_Anal_Opt_Rec.Bsc_Option_Group0
1497 AND analysis_option1 = l_Anal_Opt_Rec.Bsc_Option_Group1
1498 AND analysis_option2 = l_Anal_Opt_Rec.Bsc_Option_Group2;
1499 ELSE
1500 l_Anal_Opt_Rec.Bsc_Dataset_Series_Id := 0;
1501 END IF;
1502 end if;
1503 -- Check if it needs to update the Default DataSeries instead of create a New
1504 -- Data Series
1505 --- Get Default Color for the Serie:
1506 -- Assign certain default values if they are currently null.
1507 if l_Anal_Opt_Rec.Bsc_Dataset_Axis is null then
1508 l_Anal_Opt_Rec.Bsc_Dataset_Axis := 1;
1509 end if;
1510 if l_Anal_Opt_Rec.Bsc_Dataset_Bm_Flag is null then
1511 l_Anal_Opt_Rec.Bsc_Dataset_Bm_Flag := 1;
1512 end if;
1513 if l_Anal_Opt_Rec.Bsc_Dataset_Budget_Flag is null then
1514 l_Anal_Opt_Rec.Bsc_Dataset_Budget_Flag := 1;
1515 end if;
1516 if l_Anal_Opt_Rec.Bsc_Dataset_Default_Value is null then
1517 l_Anal_Opt_Rec.Bsc_Dataset_Default_Value := 0;
1518 end if;
1519 if l_Anal_Opt_Rec.Bsc_Dataset_Series_Type is null then
1520 l_Anal_Opt_Rec.Bsc_Dataset_Series_Type := 1;
1521 end if;
1522 if l_Anal_Opt_Rec.Bsc_Option_Help is null then
1523 l_Anal_Opt_Rec.Bsc_Option_Help := l_Anal_Opt_Rec.Bsc_Option_Name;
1524 end if;
1525
1526 if l_Anal_Opt_Rec.Bsc_Dataset_Series_Color is null
1527 or l_Anal_Opt_Rec.Bsc_Dataset_Series_Color is null then
1528 -- Get the Default Color for the Series
1529 l_series_color := 10053171;
1530 l_BM_color := 10053171;
1531 l_series_id := l_Anal_Opt_Rec.Bsc_Dataset_Series_Id;
1532 SELECT MAX(SERIES_ID)
1533 INTO l_max_series_id
1534 FROM BSC_SYS_SERIES_COLORS;
1535 WHILE l_series_id > l_max_series_id LOOP
1536 l_series_id := l_series_id - l_max_series_id -1;
1537 END LOOP;
1538 IF (c_Series_color%ISOPEN) THEN
1539 CLOSE c_Series_color;
1540 END IF;
1541 FOR cd IN c_Series_color LOOP
1542 l_series_color := cd.SERIES_COLOR;
1543 l_BM_color := cd.BM_COLOR;
1544 END LOOP;
1545 --
1546 if l_Anal_Opt_Rec.Bsc_Dataset_Series_Color is null then
1547 l_Anal_Opt_Rec.Bsc_Dataset_Series_Color := l_series_color;
1548 end if;
1549 if l_Anal_Opt_Rec.Bsc_Dataset_Bm_Color is null then
1550 l_Anal_Opt_Rec.Bsc_Dataset_Bm_Color := l_BM_color;
1551 end if;
1552 end if;
1553
1554 -- Get the name for the Data Set Id given.
1555 if l_Anal_Opt_Rec.Bsc_Measure_Long_Name is null then
1556 select name
1557 into l_Anal_Opt_Rec.Bsc_Measure_Long_Name
1558 from BSC_SYS_DATASETS_VL
1559 where dataset_id = l_Anal_Opt_Rec.Bsc_Dataset_Id;
1560 end if;
1561 -- If help for the measure is null set it equal to the name.
1562 if l_Anal_Opt_Rec.Bsc_Measure_Help is null then
1563 l_Anal_Opt_Rec.Bsc_Measure_Help := l_Anal_Opt_Rec.Bsc_Measure_Long_Name;
1564 end if;
1565
1566 -- Call the following procedure.
1567 Create_Analysis_Measures( p_commit
1568 ,l_Anal_Opt_Rec
1569 ,x_return_status
1570 ,x_msg_count
1571 ,x_msg_data);
1572 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1573 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1574 END IF;
1575 BSC_DESIGNER_PVT.ActionFlag_Change( p_Anal_Opt_Rec.Bsc_Kpi_Id ,
1576 BSC_DESIGNER_PVT.G_ActionFlag.GAA_Structure );
1577
1578 x_Anal_Opt_Rec := l_Anal_Opt_Rec;
1579
1580 -- repeat the steps for shared indicators also
1581 FOR cd IN c_kpi_ids LOOP
1582 l_Anal_Opt_Rec.Bsc_Kpi_Id := cd.Indicator;
1583 Create_Analysis_Measures( p_commit
1584 ,l_Anal_Opt_Rec
1585 ,x_return_status
1586 ,x_msg_count
1587 ,x_msg_data);
1588 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1589 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1590 END IF;
1591 BSC_DESIGNER_PVT.ActionFlag_Change( p_Anal_Opt_Rec.Bsc_Kpi_Id ,
1592 BSC_DESIGNER_PVT.G_ActionFlag.GAA_Structure );
1593 END LOOP;
1594 -----
1595
1596 if (p_commit = FND_API.G_TRUE) then
1597 commit;
1598 end if;
1599
1600 EXCEPTION
1601 WHEN FND_API.G_EXC_ERROR THEN
1602 IF (c_Series_color%ISOPEN) THEN
1603 CLOSE c_Series_color;
1604 END IF;
1605 IF (x_msg_data IS NULL) THEN
1606 FND_MSG_PUB.Count_And_Get
1607 ( p_encoded => FND_API.G_FALSE
1608 , p_count => x_msg_count
1609 , p_data => x_msg_data
1610 );
1611 END IF;
1612 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1613 x_return_status := FND_API.G_RET_STS_ERROR;
1614 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1615 IF (c_Series_color%ISOPEN) THEN
1616 CLOSE c_Series_color;
1617 END IF;
1618 IF (x_msg_data IS NULL) THEN
1619 FND_MSG_PUB.Count_And_Get
1620 ( p_encoded => FND_API.G_FALSE
1621 , p_count => x_msg_count
1622 , p_data => x_msg_data
1623 );
1624 END IF;
1625 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1626 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1627 WHEN NO_DATA_FOUND THEN
1628 IF (c_Series_color%ISOPEN) THEN
1629 CLOSE c_Series_color;
1630 END IF;
1631 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1632 IF (x_msg_data IS NOT NULL) THEN
1633 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Create_Data_Series ';
1634 ELSE
1635 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Create_Data_Series ';
1636 END IF;
1637 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1638 WHEN OTHERS THEN
1639 IF (c_Series_color%ISOPEN) THEN
1640 CLOSE c_Series_color;
1641 END IF;
1642 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1643 IF (x_msg_data IS NOT NULL) THEN
1644 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Create_Data_Series ';
1645 ELSE
1646 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Create_Data_Series ';
1647 END IF;
1648 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1649 end Create_Data_Series;
1650
1651
1652 procedure Update_Data_Series
1653 ( p_commit IN VARCHAR2 -- FND_API.G_FALSE
1654 , p_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
1655 , x_return_status OUT NOCOPY VARCHAR2
1656 , x_msg_count OUT NOCOPY NUMBER
1657 , x_msg_data OUT NOCOPY VARCHAR2
1658 ) IS
1659 l_Anal_Opt_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
1660 l_count NUMBER;
1661 l_share_flag number;
1662
1663 --get shared indicators
1664 CURSOR c_kpi_ids IS
1665 SELECT indicator
1666 FROM BSC_KPIS_B
1667 WHERE Source_Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
1668 AND Prototype_Flag <> 2;
1669 BEGIN
1670 x_return_status := FND_API.G_RET_STS_SUCCESS;
1671 l_Anal_Opt_Rec := p_Anal_Opt_Rec;
1672
1673 --- Check Objective Id
1674 if p_Anal_Opt_Rec.Bsc_Kpi_Id is not null then
1675 SELECT COUNT(0)
1676 INTO l_count
1677 FROM BSC_KPIS_B
1678 WHERE INDICATOR = p_Anal_Opt_Rec.Bsc_Kpi_Id;
1679 if l_count = 0 then
1680 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_KPI_ID');
1681 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Anal_Opt_Rec.Bsc_Kpi_Id);
1682 FND_MSG_PUB.ADD;
1683 RAISE FND_API.G_EXC_ERROR;
1684 end if;
1685 else
1686 FND_MESSAGE.SET_NAME('BSC','BSC_NO_KPI_ID_ENTERED');
1687 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Anal_Opt_Rec.Bsc_Kpi_Id);
1688 FND_MSG_PUB.ADD;
1689 RAISE FND_API.G_EXC_ERROR;
1690 end if;
1691
1692 -- Verify that this is not a Shared KPI.
1693 select share_flag
1694 into l_share_flag
1695 from BSC_KPIS_B
1696 where indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id;
1697
1698 if l_share_flag = 2 then
1699 FND_MESSAGE.SET_NAME('BSC','BSC_SHARED_KPI');
1700 FND_MESSAGE.SET_TOKEN('BSC_KPI', l_Anal_Opt_Rec.Bsc_Kpi_Id);
1701 FND_MSG_PUB.ADD;
1702 RAISE FND_API.G_EXC_ERROR;
1703 end if;
1704
1705 -- Set Default vaues for Anaysis options parameter
1706 if l_Anal_Opt_Rec.Bsc_Option_Group0 is null then
1707 l_Anal_Opt_Rec.Bsc_Option_Group0 := 0;
1708 end if;
1709 if l_Anal_Opt_Rec.Bsc_Option_Group1 is null then
1710 l_Anal_Opt_Rec.Bsc_Option_Group1 := 0;
1711 end if;
1712 if l_Anal_Opt_Rec.Bsc_Option_Group2 is null then
1713 l_Anal_Opt_Rec.Bsc_Option_Group2 := 0;
1714 end if;
1715 if l_Anal_Opt_Rec.Bsc_Dataset_Series_Id is null then
1716 l_Anal_Opt_Rec.Bsc_Dataset_Series_Id := 0;
1717 -- THROUGH ERROR
1718 end if;
1719
1720 BSC_ANALYSIS_OPTION_PUB.Update_Analysis_Measures( FND_API.G_FALSE
1721 ,l_Anal_Opt_Rec
1722 ,x_return_status
1723 ,x_msg_count
1724 ,x_msg_data);
1725 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1726 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1727 END IF;
1728
1729 -- if there are any shared KPIs update those also.
1730 FOR cd IN c_kpi_ids LOOP
1731 l_Anal_Opt_Rec.Bsc_Kpi_Id := cd.Indicator;
1732 BSC_ANALYSIS_OPTION_PUB.Update_Analysis_Measures( FND_API.G_FALSE
1733 ,l_Anal_Opt_Rec
1734 ,x_return_status
1735 ,x_msg_count
1736 ,x_msg_data);
1737 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1738 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1739 END IF;
1740 END LOOP;
1741
1742 if (p_commit = FND_API.G_TRUE) then
1743 commit;
1744 end if;
1745
1746 EXCEPTION
1747 WHEN FND_API.G_EXC_ERROR THEN
1748 IF (x_msg_data IS NULL) THEN
1749 FND_MSG_PUB.Count_And_Get
1750 ( p_encoded => FND_API.G_FALSE
1751 , p_count => x_msg_count
1752 , p_data => x_msg_data
1753 );
1754 END IF;
1755 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1756 x_return_status := FND_API.G_RET_STS_ERROR;
1757 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1758 IF (x_msg_data IS NULL) THEN
1759 FND_MSG_PUB.Count_And_Get
1760 ( p_encoded => FND_API.G_FALSE
1761 , p_count => x_msg_count
1762 , p_data => x_msg_data
1763 );
1764 END IF;
1765 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1766 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1767 WHEN NO_DATA_FOUND THEN
1768 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1769 IF (x_msg_data IS NOT NULL) THEN
1770 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Update_Data_Series ';
1771 ELSE
1772 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Update_Data_Series ';
1773 END IF;
1774 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1775 WHEN OTHERS THEN
1776 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1777 IF (x_msg_data IS NOT NULL) THEN
1778 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Update_Data_Series ';
1779 ELSE
1780 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Update_Data_Series ';
1781 END IF;
1782 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1783 end Update_Data_Series;
1784
1785 procedure Delete_Data_Series(
1786 p_commit IN varchar2 -- := FND_API.G_FALSE
1787 ,p_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
1788 ,x_return_status OUT NOCOPY varchar2
1789 ,x_msg_count OUT NOCOPY number
1790 ,x_msg_data OUT NOCOPY varchar2
1791 ) is
1792
1793 l_Num_Series NUMBER;
1794 l_count NUMBER;
1795 l_Anal_Opt_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
1796 x_Anal_Opt_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
1797 l_share_flag number;
1798
1799 --get shared indicators
1800 CURSOR c_kpi_ids IS
1801 SELECT indicator
1802 FROM BSC_KPIS_B
1803 WHERE Source_Indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
1804 AND Prototype_Flag <> 2;
1805
1806
1807 BEGIN
1808 FND_MSG_PUB.Initialize;
1809 SAVEPOINT DeleteBSCDataSeriesPUB;
1810 x_return_status := FND_API.G_RET_STS_SUCCESS;
1811 l_Anal_Opt_Rec := p_Anal_Opt_Rec;
1812
1813
1814 --- Check Objective Id
1815 if p_Anal_Opt_Rec.Bsc_Kpi_Id is not null then
1816 SELECT COUNT(0)
1817 INTO l_count
1818 FROM BSC_KPIS_B
1819 WHERE INDICATOR = p_Anal_Opt_Rec.Bsc_Kpi_Id;
1820 if l_count = 0 then
1821 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_KPI_ID');
1822 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Anal_Opt_Rec.Bsc_Kpi_Id);
1823 FND_MSG_PUB.ADD;
1824 RAISE FND_API.G_EXC_ERROR;
1825 end if;
1826 else
1827 FND_MESSAGE.SET_NAME('BSC','BSC_NO_KPI_ID_ENTERED');
1828 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Anal_Opt_Rec.Bsc_Kpi_Id);
1829 FND_MSG_PUB.ADD;
1830 RAISE FND_API.G_EXC_ERROR;
1831 end if;
1832
1833 -- Verify that this is not a Shared KPI.
1834 select share_flag
1835 into l_share_flag
1836 from BSC_KPIS_B
1837 where indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id;
1838
1839 if l_share_flag = 2 then
1840 FND_MESSAGE.SET_NAME('BSC','BSC_SHARED_KPI');
1841 FND_MESSAGE.SET_TOKEN('BSC_KPI', l_Anal_Opt_Rec.Bsc_Kpi_Id);
1842 FND_MSG_PUB.ADD;
1843 RAISE FND_API.G_EXC_ERROR;
1844 end if;
1845
1846 -- Set Default vaues for Anaysis options parameter
1847 if l_Anal_Opt_Rec.Bsc_Option_Group0 is null then
1848 l_Anal_Opt_Rec.Bsc_Option_Group0 := 0;
1849 end if;
1850 if l_Anal_Opt_Rec.Bsc_Option_Group1 is null then
1851 l_Anal_Opt_Rec.Bsc_Option_Group1 := 0;
1852 end if;
1853 if l_Anal_Opt_Rec.Bsc_Option_Group2 is null then
1854 l_Anal_Opt_Rec.Bsc_Option_Group2 := 0;
1855 end if;
1856
1857 ---Check if the number of Series before delete
1858 SELECT COUNT(SERIES_ID)
1859 INTO l_Num_Series
1860 FROM BSC_KPI_ANALYSIS_MEASURES_B
1861 WHERE indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
1862 AND analysis_option0 = p_Anal_Opt_Rec.Bsc_Option_Group0
1863 AND analysis_option1 = p_Anal_Opt_Rec.Bsc_Option_Group1
1864 AND analysis_option2 = p_Anal_Opt_Rec.Bsc_Option_Group2;
1865
1866 -- delete the dataseries metadata
1867 BSC_ANALYSIS_OPTION_PVT.delete_Data_Series(
1868 p_commit => FND_API.G_FALSE
1869 ,p_Anal_Opt_Rec => l_Anal_Opt_Rec
1870 ,x_return_status => x_return_status
1871 ,x_msg_count => x_msg_count
1872 ,x_msg_data => x_msg_data
1873 );
1874
1875 Cascade_Deletion_Color_Props (
1876 p_commit => p_commit
1877 ,p_Anal_Opt_Rec => p_Anal_Opt_Rec
1878 ,x_return_status => x_return_status
1879 ,x_msg_count => x_msg_count
1880 ,x_msg_data => x_msg_data
1881 ) ;
1882 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1883 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1884 END IF;
1885
1886 BSC_DESIGNER_PVT.ActionFlag_Change( p_Anal_Opt_Rec.Bsc_Kpi_Id ,
1887 BSC_DESIGNER_PVT.G_ActionFlag.GAA_Structure );
1888 -- repeat the steps for shared indicators also
1889
1890 FOR cd IN c_kpi_ids LOOP
1891 l_Anal_Opt_Rec.Bsc_Kpi_Id := cd.Indicator;
1892 BSC_ANALYSIS_OPTION_PVT.delete_Data_Series(
1893 p_commit => FND_API.G_FALSE
1894 ,p_Anal_Opt_Rec => l_Anal_Opt_Rec
1895 ,x_return_status => x_return_status
1896 ,x_msg_count => x_msg_count
1897 ,x_msg_data => x_msg_data
1898 );
1899
1900 Cascade_Deletion_Color_Props (
1901 p_commit => p_commit
1902 ,p_Anal_Opt_Rec => l_Anal_Opt_Rec
1903 ,x_return_status => x_return_status
1904 ,x_msg_count => x_msg_count
1905 ,x_msg_data => x_msg_data
1906 ) ;
1907 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1908 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1909 END IF;
1910
1911 BSC_DESIGNER_PVT.ActionFlag_Change( l_Anal_Opt_Rec.Bsc_Kpi_Id ,
1912 BSC_DESIGNER_PVT.G_ActionFlag.GAA_Structure );
1913 END LOOP;
1914
1915 ---Check if the number of Series is zero in order to inser the Deafault
1916 ---Data Serie
1917
1918 IF l_Anal_Opt_Rec.Bsc_New_Kpi IS NULL THEN
1919 l_Anal_Opt_Rec.Bsc_New_Kpi := 'N';
1920 END IF;
1921 IF l_Num_Series = 1 and l_Anal_Opt_Rec.Bsc_New_Kpi <> 'Y' then
1922
1923 SELECT COUNT(SERIES_ID)
1924 INTO l_Num_Series
1925 FROM BSC_KPI_ANALYSIS_MEASURES_B
1926 WHERE indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id
1927 AND analysis_option0 = p_Anal_Opt_Rec.Bsc_Option_Group0
1928 AND analysis_option1 = p_Anal_Opt_Rec.Bsc_Option_Group1
1929 AND analysis_option2 = p_Anal_Opt_Rec.Bsc_Option_Group2;
1930 -- Insert the Default Data Series
1931 IF l_Num_Series = 0 then
1932 l_Anal_Opt_Rec := p_Anal_Opt_Rec;
1933 l_Anal_Opt_Rec.Bsc_Dataset_Series_Id := null;
1934 l_Anal_Opt_Rec.Bsc_Dataset_Id := null;
1935 l_Anal_Opt_Rec.Bsc_New_Kpi := 'Y';
1936
1937 Create_Data_Series(
1938 p_commit => p_commit
1939 ,p_Anal_Opt_Rec => l_Anal_Opt_Rec
1940 ,x_Anal_Opt_Rec => x_Anal_Opt_Rec
1941 ,x_return_status => x_return_status
1942 ,x_msg_count => x_msg_count
1943 ,x_msg_data => x_msg_data
1944 );
1945
1946 END IF;
1947
1948 END IF;
1949
1950 if (p_commit = FND_API.G_TRUE) then
1951 commit;
1952 end if;
1953
1954
1955 EXCEPTION
1956 WHEN FND_API.G_EXC_ERROR THEN
1957 ROLLBACK TO DeleteBSCDataSeriesPUB;
1958 FND_MSG_PUB.Count_And_Get
1959 ( p_encoded => FND_API.G_FALSE
1960 , p_count => x_msg_count
1961 , p_data => x_msg_data
1962 );
1963 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1964 x_return_status := FND_API.G_RET_STS_ERROR;
1965 RAISE;
1966 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1967 ROLLBACK TO DeleteBSCDataSeriesPUB;
1968 FND_MSG_PUB.Count_And_Get
1969 ( p_encoded => FND_API.G_FALSE
1970 , p_count => x_msg_count
1971 , p_data => x_msg_data
1972 );
1973 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1974 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1975 RAISE;
1976 WHEN OTHERS THEN
1977 ROLLBACK TO DeleteBSCDataSeriesPUB;
1978 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1979 IF (x_msg_data IS NOT NULL) THEN
1980 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Delete_Data_Series ';
1981 ELSE
1982 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Delete_Data_Series ';
1983 END IF;
1984 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1985 RAISE;
1986
1987 End Delete_Data_Series;
1988
1989
1990 FUNCTION Is_More
1991 ( p_names IN OUT NOCOPY VARCHAR2
1992 , p_name OUT NOCOPY VARCHAR2
1993 ) RETURN BOOLEAN
1994 IS
1995 l_pos_ids NUMBER;
1996 l_pos_rel_types NUMBER;
1997 l_pos_rel_columns NUMBER;
1998 BEGIN
1999 IF (p_names IS NOT NULL) THEN
2000 l_pos_ids := INSTR(p_names, ',');
2001 IF (l_pos_ids > 0) THEN
2002 p_name := TRIM(SUBSTR(p_names, 1, l_pos_ids - 1));
2003 p_names := TRIM(SUBSTR(p_names, l_pos_ids + 1));
2004 ELSE
2005 p_name := TRIM(p_names);
2006 p_names := NULL;
2007 END IF;
2008 RETURN TRUE;
2009 ELSE
2010 RETURN FALSE;
2011 END IF;
2012 END Is_More;
2013
2014 /*---------------------------------------------------------------------------
2015 Swap_Data_Series : Swap the Data Series Id between two DataSerid
2016
2017 Use Parameters:
2018 p_Anal_Opt_Rec.Bsc_Kpi_Id
2019 p_Anal_Opt_Rec.Bsc_Option_Group0
2020 p_Anal_Opt_Rec.Bsc_Option_Group1
2021 Anal_Opt_Rec.Bsc_Option_Group2
2022 p_Anal_Opt_Rec.Bsc_Dataset_Series_Id;
2023 p_Anal_Opt_Rec.Bsc_Dataset_New_Series_Id;
2024 ----------------------------------------------------------------------------*/
2025 procedure Swap_Data_Series_Id(
2026 p_commit IN varchar2 -- := FND_API.G_FALSE
2027 ,p_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
2028 ,x_return_status OUT NOCOPY varchar2
2029 ,x_msg_count OUT NOCOPY number
2030 ,x_msg_data OUT NOCOPY varchar2
2031 ) is
2032
2033 l_Anal_Opt_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
2034 --get shared indicators
2035 CURSOR c_kpi_ids IS
2036 SELECT indicator
2037 FROM BSC_KPIS_B
2038 WHERE Source_Indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
2039 AND Prototype_Flag <> 2;
2040
2041 BEGIN
2042 FND_MSG_PUB.Initialize;
2043 SAVEPOINT SwapDataSeriesPUB;
2044
2045 x_return_status := FND_API.G_RET_STS_SUCCESS;
2046
2047 l_Anal_Opt_Rec := p_Anal_Opt_Rec;
2048 -- Swaping
2049 BSC_ANALYSIS_OPTION_PVT.Swap_Data_Series_Id(
2050 p_commit => FND_API.G_FALSE
2051 ,p_Anal_Opt_Rec => l_Anal_Opt_Rec
2052 ,x_return_status => x_return_status
2053 ,x_msg_count => x_msg_count
2054 ,x_msg_data => x_msg_data
2055 );
2056 -- Cascading Swaping
2057 FOR cd IN c_kpi_ids LOOP
2058 l_Anal_Opt_Rec.Bsc_Kpi_Id := cd.Indicator;
2059 BSC_ANALYSIS_OPTION_PVT.Swap_Data_Series_Id(
2060 p_commit => FND_API.G_FALSE
2061 ,p_Anal_Opt_Rec => l_Anal_Opt_Rec
2062 ,x_return_status => x_return_status
2063 ,x_msg_count => x_msg_count
2064 ,x_msg_data => x_msg_data
2065 );
2066 END LOOP;
2067
2068 IF p_commit = FND_API.G_TRUE THEN
2069 COMMIT;
2070 END IF;
2071
2072 EXCEPTION
2073 WHEN FND_API.G_EXC_ERROR THEN
2074 IF (c_kpi_ids%ISOPEN) THEN
2075 CLOSE c_kpi_ids;
2076 END IF;
2077 ROLLBACK TO SwapDataSeriesPUB;
2078 FND_MSG_PUB.Count_And_Get
2079 ( p_encoded => FND_API.G_FALSE
2080 , p_count => x_msg_count
2081 , p_data => x_msg_data
2082 );
2083 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2084 x_return_status := FND_API.G_RET_STS_ERROR;
2085 RAISE;
2086 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2087 IF (c_kpi_ids%ISOPEN) THEN
2088 CLOSE c_kpi_ids;
2089 END IF;
2090 ROLLBACK TO SwapDataSeriesPUB;
2091 FND_MSG_PUB.Count_And_Get
2092 ( p_encoded => FND_API.G_FALSE
2093 , p_count => x_msg_count
2094 , p_data => x_msg_data
2095 );
2096 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2097 IF (x_msg_data IS NOT NULL) THEN
2098 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Swap_Data_Series_Id ';
2099 ELSE
2100 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Swap_Data_Series_Id ';
2101 END IF;
2102 RAISE;
2103 WHEN OTHERS THEN
2104 IF (c_kpi_ids%ISOPEN) THEN
2105 CLOSE c_kpi_ids;
2106 END IF;
2107 ROLLBACK TO SwapDataSeriesPUB;
2108 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2109 IF (x_msg_data IS NOT NULL) THEN
2110 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Swap_Data_Series_Id ';
2111 ELSE
2112 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Swap_Data_Series_Id ';
2113 END IF;
2114 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2115 RAISE;
2116
2117 End Swap_Data_Series_Id;
2118
2119 /*-----------------------------------------------------------------------
2120 Rearrange_Data_Series:
2121 Rearrange the Data Series Id following same order that the Measure
2122
2123 p_Measure_Seq : contains the sh
2124
2125 ------------------------------------------------------------------------*/
2126 procedure Rearrange_Data_Series(
2127 p_commit IN varchar2 -- FND_API.G_FALSE
2128 ,p_Kpi_Id IN number
2129 ,p_option_group0 IN number
2130 ,p_option_group1 IN number
2131 ,p_option_group2 IN number
2132 ,p_Measure_Seq IN varchar2
2133 ,p_add_flag IN varchar2 -- FND_API.G_FALSE
2134 ,p_remove_flag IN varchar2 -- FND_API.G_FALSE
2135 ,x_return_status OUT NOCOPY varchar2
2136 ,x_msg_count OUT NOCOPY number
2137 ,x_msg_data OUT NOCOPY varchar2
2138 ) is
2139
2140 l_short_name VARCHAR2(100);
2141 l_short_names VARCHAR2(3000);
2142 l_Anal_Opt_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
2143 x_Anal_Opt_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
2144 l_attribute_code VARCHAR2(100);
2145 l_count NUMBER;
2146
2147 l_Measure_Short_Name BIS_INDICATORS.SHORT_NAME%TYPE;
2148 l_Objective_Short_Name BSC_KPIS_B.SHORT_NAME%TYPE;
2149 l_Measure_Source BSC_SYS_DATASETS_B.SOURCE%TYPE;
2150
2151 l_Comparison_Source BIS_INDICATORS.COMPARISON_SOURCE%TYPE;
2152 l_Compare_Attribute_Code AK_REGION_ITEMS.ATTRIBUTE_CODE%TYPE;
2153
2154 -- Cursor to get the Data Set Id correspondig to each Short_name
2155 Cursor c_Dataset is
2156 SELECT
2157 i.dataset_id
2158 FROM
2159 bis_indicators i
2160 WHERE
2161 i.short_name = l_short_name;
2162
2163 -- Cursor to get the Data Series Id correspondig each Data Set
2164 Cursor c_Data_Series is
2165 SELECT SERIES_ID, DATASET_ID
2166 FROM BSC_KPI_ANALYSIS_MEASURES_B
2167 WHERE indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
2168 AND ANALYSIS_OPTION0 = l_Anal_Opt_Rec.Bsc_Option_Group0
2169 AND ANALYSIS_OPTION1 = l_Anal_Opt_Rec.Bsc_Option_Group1
2170 AND ANALYSIS_OPTION2 = l_Anal_Opt_Rec.Bsc_Option_Group2
2171 AND DATASET_ID = l_Anal_Opt_Rec.Bsc_Dataset_Id;
2172
2173 -- Cursor to get the Data Series not applied any more
2174 Cursor c_Data_Series_Remove is
2175 SELECT SERIES_ID, DATASET_ID
2176 FROM BSC_KPI_ANALYSIS_MEASURES_B
2177 WHERE indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
2178 AND ANALYSIS_OPTION0 = l_Anal_Opt_Rec.Bsc_Option_Group0
2179 AND ANALYSIS_OPTION1 = l_Anal_Opt_Rec.Bsc_Option_Group1
2180 AND ANALYSIS_OPTION2 = l_Anal_Opt_Rec.Bsc_Option_Group2
2181 AND SERIES_ID >= l_count
2182 ORDER BY SERIES_ID DESC;
2183
2184 BEGIN
2185 FND_MSG_PUB.Initialize;
2186 SAVEPOINT Rearrange_Data_SeriesVT;
2187
2188 l_Anal_Opt_Rec.Bsc_Option_Group0 := 0;
2189 l_Anal_Opt_Rec.Bsc_Option_Group1 := 0;
2190 l_Anal_Opt_Rec.Bsc_Option_Group2 := 0;
2191
2192 l_Anal_Opt_Rec.Bsc_Kpi_Id := p_Kpi_Id;
2193 IF p_option_group0 IS NOT NULL THEN
2194 l_Anal_Opt_Rec.Bsc_Option_Group0 := p_option_group0;
2195 END IF;
2196 IF p_option_group1 IS NOT NULL THEN
2197 l_Anal_Opt_Rec.Bsc_Option_Group1 := p_option_group1;
2198 END IF;
2199 IF p_option_group2 IS NOT NULL THEN
2200 l_Anal_Opt_Rec.Bsc_Option_Group2 := p_option_group2;
2201 END IF;
2202 l_count := 0;
2203
2204 IF (p_Measure_Seq IS NOT NULL) THEN
2205 l_short_names := p_Measure_Seq;
2206 WHILE (is_more( p_names => l_short_names
2207 , p_name => l_short_name))
2208 LOOP
2209 l_count := l_count + 1;
2210 -- Get the dataset associte the the Measure Shorename
2211 l_Anal_Opt_Rec.Bsc_Dataset_Id := NULL;
2212 l_Anal_Opt_Rec.Bsc_Dataset_Series_Id := NULL;
2213 l_Anal_Opt_Rec.Bsc_Dataset_New_Series_Id := NULL;
2214 FOR CD IN c_Dataset LOOP
2215 l_Anal_Opt_Rec.Bsc_Dataset_Id := CD.DATASET_ID;
2216 END LOOP;
2217 IF l_Anal_Opt_Rec.Bsc_Dataset_Id IS NOT NULL THEN
2218 -- Get the Series Id
2219 FOR CD1 IN c_Data_Series LOOP
2220 l_Anal_Opt_Rec.Bsc_Dataset_Series_Id := CD1.SERIES_ID;
2221 END LOOP;
2222 IF p_add_flag = FND_API.G_TRUE
2223 AND l_Anal_Opt_Rec.Bsc_Dataset_Series_Id IS NULL THEN
2224 --- Create the Data Series for the new Data Set ID
2225 Create_Data_Series(
2226 p_commit => p_commit
2227 ,p_Anal_Opt_Rec => l_Anal_Opt_Rec
2228 ,x_Anal_Opt_Rec => x_Anal_Opt_Rec
2229 ,x_return_status => x_return_status
2230 ,x_msg_count => x_msg_count
2231 ,x_msg_data => x_msg_data
2232 );
2233 l_Anal_Opt_Rec.Bsc_Dataset_Series_Id :=
2234 x_Anal_Opt_Rec.Bsc_Dataset_Series_Id;
2235 END IF;
2236 IF l_Anal_Opt_Rec.Bsc_Dataset_Series_Id IS NOT NULL THEN
2237 -- Swap the dataseries. Set the Series_id = l_count-1
2238 l_Anal_Opt_Rec.Bsc_Dataset_New_Series_Id := l_count-1;
2239 Swap_Data_Series_Id(
2240 p_commit => p_commit
2241 ,p_Anal_Opt_Rec => l_Anal_Opt_Rec
2242 ,x_return_status => x_return_status
2243 ,x_msg_count => x_msg_count
2244 ,x_msg_data => x_msg_data
2245 );
2246 END IF;
2247 ELSE
2248 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_MEAS_ID');
2249 FND_MESSAGE.SET_TOKEN('BSC_MEAS', l_short_name);
2250 FND_MSG_PUB.ADD;
2251 RAISE FND_API.G_EXC_ERROR;
2252 END IF;
2253
2254 END LOOP;
2255 END IF;
2256 -- Remove the Data Series not used
2257 IF p_remove_flag = FND_API.G_TRUE THEN
2258 FOR CD IN c_Data_Series_Remove LOOP
2259 l_Anal_Opt_Rec.Bsc_Dataset_Series_Id := CD.SERIES_ID;
2260 Delete_Data_Series(
2261 p_commit => p_commit
2262 ,p_Anal_Opt_Rec => l_Anal_Opt_Rec
2263 ,x_return_status => x_return_status
2264 ,x_msg_count => x_msg_count
2265 ,x_msg_data => x_msg_data
2266 );
2267 --Bug 5526265 Moved updation of the data source logic to Java Layer
2268 END LOOP;
2269 END IF;
2270
2271
2272 EXCEPTION
2273 WHEN FND_API.G_EXC_ERROR THEN
2274 IF (c_Data_Series%ISOPEN) THEN
2275 CLOSE c_Data_Series;
2276 END IF;
2277 ROLLBACK TO SwapDataSeriesPVT;
2278 FND_MSG_PUB.Count_And_Get
2279 ( p_encoded => FND_API.G_FALSE
2280 , p_count => x_msg_count
2281 , p_data => x_msg_data
2282 );
2283 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2284 x_return_status := FND_API.G_RET_STS_ERROR;
2285 RAISE;
2286 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2287 IF (c_Data_Series%ISOPEN) THEN
2288 CLOSE c_Data_Series;
2289 END IF;
2290 ROLLBACK TO RearrangeDataSeriesPVT;
2291 FND_MSG_PUB.Count_And_Get
2292 ( p_encoded => FND_API.G_FALSE
2293 , p_count => x_msg_count
2294 , p_data => x_msg_data
2295 );
2296 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2297 RAISE;
2298 WHEN OTHERS THEN
2299 IF (c_Data_Series%ISOPEN) THEN
2300 CLOSE c_Data_Series;
2301 END IF;
2302 ROLLBACK TO RearrangeDataSeriesPVT;
2303 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2304 IF (x_msg_data IS NOT NULL) THEN
2305 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Rearrange_Data_Series ';
2306 ELSE
2307 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Rearrange_Data_Series ';
2308 END IF;
2309 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2310 RAISE;
2311
2312 End Rearrange_Data_Series;
2313
2314 /***********************************************************
2315 Name : Get_Num_Analysis_options
2316 Description: This Function returns the number of analysis options in the current
2317 Analysis Group
2318 Input : p_obj_id --> Objective Id
2319 p_anal_grp_Id --> Analysis Group Id
2320 Created BY : ashankar For bug 4220400
2321 /**********************************************************/
2322
2323 FUNCTION Get_Num_Analysis_options
2324 (
2325 p_obj_id IN BSC_KPIS_B.indicator%TYPE
2326 , p_anal_grp_Id IN BSC_KPI_ANALYSIS_GROUPS.analysis_group_id%TYPE
2327 )RETURN NUMBER IS
2328 l_count NUMBER;
2329 BEGIN
2330 SELECT num_of_options
2331 INTO l_count
2332 FROM BSC_KPI_ANALYSIS_GROUPS
2333 WHERE indicator =p_obj_id
2334 AND analysis_group_id = p_anal_grp_Id;
2335
2336 RETURN l_count;
2337 END Get_Num_Analysis_options;
2338
2339
2340 /***********************************************************
2341 Name : Get_Analysis_Group_Id
2342 Description: This Function returns the current Analysis Group Id based on the current Analysis
2343 option combination.
2344 Input : p_obj_id --> Objective Id
2345 p_Anal_Opt_Comb_Tbl --> Analysis option combination table.
2346 p_max_group_count --> Maximum analysis groups in the current objective
2347
2348 Created BY : ashankar For bug 4220400
2349 /**********************************************************/
2350
2351 FUNCTION Get_Analysis_Group_Id
2352 (
2353 p_Anal_Opt_Comb_Tbl IN BSC_ANALYSIS_OPTION_PUB.Anal_Opt_Comb_Num_Tbl_Type
2354 , p_obj_id IN BSC_KPIS_B.indicator%TYPE
2355 ) RETURN NUMBER IS
2356
2357 l_Anal_Opt_Tbl BSC_ANALYSIS_OPTION_PUB.Bsc_Anal_Opt_Tbl_Type;
2358 l_Anal_Grp_Id NUMBER;
2359 l_Anal_Det_Opt_Tbl BSC_ANALYSIS_OPTION_PUB.Bsc_Anal_Opt_Det_Tbl_Type;
2360 l_count NUMBER;
2361 l_option_count NUMBER;
2362 l_max_group_count NUMBER;
2363
2364 BEGIN
2365 SELECT COUNT(0)
2366 INTO l_max_group_count
2367 FROM bsc_kpi_analysis_groups
2368 WHERE indicator = p_obj_id;
2369
2370 IF(l_max_group_count>1) THEN
2371 BSC_BIS_KPI_MEAS_PUB.store_kpi_anal_group(p_obj_id, l_Anal_Opt_Tbl);
2372
2373 IF(BSC_ANALYSIS_OPTION_PVT.Validate_If_single_Anal_Opt(l_Anal_Opt_Tbl)) THEN
2374 l_Anal_Grp_Id := BSC_ANALYSIS_OPTION_PUB.c_ANALYSIS_GROUP0;
2375 RETURN l_Anal_Grp_Id;
2376 END IF;
2377
2378 BSC_ANALYSIS_OPTION_PVT.Initialize_Anal_Opt_Tbl
2379 (
2380 p_Kpi_id => p_obj_id
2381 , p_Anal_Opt_Tbl => l_Anal_Opt_Tbl
2382 , p_max_group_count => l_max_group_count
2383 , p_Anal_Opt_Comb_Tbl => p_Anal_Opt_Comb_Tbl
2384 , p_Anal_Det_Opt_Tbl => l_Anal_Det_Opt_Tbl
2385 );
2386
2387 l_count := l_Anal_Det_Opt_Tbl.COUNT - 1 ;
2388 IF(l_count=1)THEN
2389 IF((l_Anal_Det_Opt_Tbl(l_count).Bsc_dependency_flag = 1)) THEN
2390 IF((l_Anal_Det_Opt_Tbl.EXISTS(l_count-1))AND(l_Anal_Det_Opt_Tbl(l_count-1).No_of_child=1)) THEN
2391 l_Anal_Grp_Id := BSC_ANALYSIS_OPTION_PUB.c_ANALYSIS_GROUP0;
2392 ELSE
2393 l_Anal_Grp_Id := BSC_ANALYSIS_OPTION_PUB.c_ANALYSIS_GROUP1;
2394 END IF;
2395 ELSE
2396 l_option_count := Get_Num_Analysis_options
2397 (
2398 p_obj_id => p_obj_id
2399 , p_anal_grp_Id => BSC_ANALYSIS_OPTION_PUB.c_ANALYSIS_GROUP1
2400 );
2401 IF(l_option_count >1) THEN
2402 l_Anal_Grp_Id := BSC_ANALYSIS_OPTION_PUB.c_ANALYSIS_GROUP1;
2403 ELSE
2404 l_Anal_Grp_Id := BSC_ANALYSIS_OPTION_PUB.c_ANALYSIS_GROUP0;
2405 END IF;
2406 END IF;
2407 ELSE
2408 IF((l_Anal_Det_Opt_Tbl(l_count).Bsc_dependency_flag = 1)) THEN
2409 IF((l_Anal_Det_Opt_Tbl.EXISTS(l_count-1))AND(l_Anal_Det_Opt_Tbl(l_count-1).No_of_child=1)) THEN
2410 IF((l_Anal_Det_Opt_Tbl(l_count-1).Bsc_dependency_flag = 1)) THEN
2411 IF((l_Anal_Det_Opt_Tbl.EXISTS(l_count-2))AND(l_Anal_Det_Opt_Tbl(l_count-2).No_of_child=1)) THEN
2412 l_Anal_Grp_Id := BSC_ANALYSIS_OPTION_PUB.c_ANALYSIS_GROUP0;
2413 ELSE
2414 l_Anal_Grp_Id := BSC_ANALYSIS_OPTION_PUB.c_ANALYSIS_GROUP1;
2415 END IF;
2416 ELSE
2417 l_option_count := Get_Num_Analysis_options
2418 (
2419 p_obj_id => p_obj_id
2420 , p_anal_grp_Id => BSC_ANALYSIS_OPTION_PUB.c_ANALYSIS_GROUP1
2421 );
2422 IF(l_option_count >1) THEN
2423 l_Anal_Grp_Id := BSC_ANALYSIS_OPTION_PUB.c_ANALYSIS_GROUP1;
2424 ELSE
2425 l_Anal_Grp_Id := BSC_ANALYSIS_OPTION_PUB.c_ANALYSIS_GROUP0;
2426 END IF;
2427 END IF;
2428
2429 ELSE
2430 l_Anal_Grp_Id := BSC_ANALYSIS_OPTION_PUB.c_ANALYSIS_GROUP2;
2431 END IF;
2432 ELSE
2433 l_option_count := Get_Num_Analysis_options
2434 (
2435 p_obj_id => p_obj_id
2436 , p_anal_grp_Id => BSC_ANALYSIS_OPTION_PUB.c_ANALYSIS_GROUP2
2437 );
2438 IF(l_option_count >1) THEN
2439 l_Anal_Grp_Id := BSC_ANALYSIS_OPTION_PUB.c_ANALYSIS_GROUP2;
2440 ELSE
2441 IF((l_Anal_Det_Opt_Tbl(l_count-1).Bsc_dependency_flag = 1)) THEN
2442 IF((l_Anal_Det_Opt_Tbl.EXISTS(l_count-2))AND(l_Anal_Det_Opt_Tbl(l_count-2).No_of_child=1)) THEN
2443 l_Anal_Grp_Id := BSC_ANALYSIS_OPTION_PUB.c_ANALYSIS_GROUP0;
2444 ELSE
2445 l_Anal_Grp_Id := BSC_ANALYSIS_OPTION_PUB.c_ANALYSIS_GROUP1;
2446 END IF;
2447 ELSE
2448 l_option_count := Get_Num_Analysis_options
2449 (
2450 p_obj_id => p_obj_id
2451 , p_anal_grp_Id => BSC_ANALYSIS_OPTION_PUB.c_ANALYSIS_GROUP1
2452 );
2453 IF(l_option_count >1) THEN
2454 l_Anal_Grp_Id := BSC_ANALYSIS_OPTION_PUB.c_ANALYSIS_GROUP1;
2455 ELSE
2456 l_Anal_Grp_Id := BSC_ANALYSIS_OPTION_PUB.c_ANALYSIS_GROUP0;
2457 END IF;
2458 END IF;
2459 END IF;
2460 END IF;
2461 END IF;
2462 ELSE
2463 l_Anal_Grp_Id := BSC_ANALYSIS_OPTION_PUB.c_ANALYSIS_GROUP0;
2464 END IF;
2465 RETURN l_Anal_Grp_Id;
2466 END Get_Analysis_Group_Id;
2467
2468 /***********************************************************
2469 Name : Set_Default_Analysis_Option
2470 Description: This Function sets the default Analysis option combination for the objective.
2471 Input : p_obj_id --> Objective Id
2472 p_Anal_Opt_Comb_Tbl --> Analysis option combination table.
2473 p_Anal_Grp_Id --> Analysis Group Id
2474
2475 Created BY : ashankar For bug 4220400
2476 /**********************************************************/
2477 PROCEDURE Set_Default_Analysis_Option
2478 (
2479 p_commit IN VARCHAR
2480 , p_obj_id IN BSC_KPIS_B.indicator%TYPE
2481 , p_Anal_Opt_Comb_Tbl IN BSC_ANALYSIS_OPTION_PUB.Anal_Opt_Comb_Num_Tbl_Type
2482 , p_Anal_Grp_Id IN BSC_KPIS_B.ind_group_id%TYPE
2483 , x_return_status OUT NOCOPY VARCHAR2
2484 , x_msg_count OUT NOCOPY NUMBER
2485 , x_msg_data OUT NOCOPY VARCHAR2
2486 )IS
2487 BEGIN
2488 BSC_ANALYSIS_OPTION_PVT.Set_Default_Analysis_Option
2489 (
2490 p_commit => p_commit
2491 , p_obj_id => p_obj_id
2492 , p_Anal_Opt_Comb_Tbl => p_Anal_Opt_Comb_Tbl
2493 , p_Anal_Grp_Id => p_Anal_Grp_Id
2494 , x_return_status => x_return_status
2495 , x_msg_count => x_msg_count
2496 , x_msg_data => x_msg_data
2497 );
2498
2499
2500 EXCEPTION
2501 WHEN FND_API.G_EXC_ERROR THEN
2502 FND_MSG_PUB.Count_And_Get
2503 ( p_encoded => FND_API.G_FALSE
2504 , p_count => x_msg_count
2505 , p_data => x_msg_data
2506 );
2507 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2508 x_return_status := FND_API.G_RET_STS_ERROR;
2509 RAISE;
2510 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2511 FND_MSG_PUB.Count_And_Get
2512 ( p_encoded => FND_API.G_FALSE
2513 , p_count => x_msg_count
2514 , p_data => x_msg_data
2515 );
2516 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2517 RAISE;
2518 WHEN OTHERS THEN
2519 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2520 IF (x_msg_data IS NOT NULL) THEN
2521 x_msg_data := x_msg_data||' -> BSC_ANALYSIS_OPTION_PUB.Set_Default_Analysis_Option ';
2522 ELSE
2523 x_msg_data := SQLERRM||' at BSC_ANALYSIS_OPTION_PUB.Set_Default_Analysis_Option ';
2524 END IF;
2525 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2526 RAISE;
2527 END Set_Default_Analysis_Option;
2528
2529 /***********************************************************
2530 Name : Default_Anal_Option_Changed
2531 Description: This Function compares the old default analysis option combination
2532 with the one selected by the user.If it has changed then it return
2533 True otherwise it returns false.
2534 Input : p_Anal_Num_Tbl --> New analysis option combination table.
2535 p_Old_Anal_Num_Tbl --> Old Analysis option combination table.
2536 Output : True --> means changed.
2537 False --> means not changed.
2538 Created BY : ashankar For bug 4220400
2539 /**********************************************************/
2540 FUNCTION Default_Anal_Option_Changed
2541 (
2542 p_Anal_Num_Tbl IN BSC_ANALYSIS_OPTION_PUB.Anal_Opt_Comb_Num_Tbl_Type
2543 , p_Old_Anal_Num_Tbl IN BSC_ANALYSIS_OPTION_PUB.Anal_Opt_Comb_Num_Tbl_Type
2544 )RETURN BOOLEAN IS
2545 l_return BOOLEAN;
2546 l_count NUMBER;
2547 BEGIN
2548 l_return := FALSE;
2549 IF((p_Anal_Num_Tbl IS NOT NULL) AND (p_Old_Anal_Num_Tbl IS NOT NULL))THEN
2550 l_count := p_Anal_Num_Tbl.COUNT -1;
2551 FOR counter IN 0..l_count LOOP
2552 IF(p_Anal_Num_Tbl(counter)<>p_Old_Anal_Num_Tbl(counter))THEN
2553 l_return := TRUE;
2554 EXIT;
2555 END IF;
2556 END LOOP;
2557 END IF;
2558
2559 RETURN l_return;
2560 END Default_Anal_Option_Changed;
2561
2562
2563
2564 end BSC_ANALYSIS_OPTION_PUB;