[Home] [Help]
PACKAGE BODY: APPS.BSC_DIMENSION_SETS_PVT
Source
1 package body BSC_DIMENSION_SETS_PVT as
2 /* $Header: BSCVDMSB.pls 120.1 2005/07/21 05:31:50 ashankar noship $ */
3 /*
4 +======================================================================================+
5 | Copyright (c) 2001 Oracle Corporation, Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +======================================================================================+
8 | FILENAME |
9 | BSCVDMSB.pls |
10 | |
11 | Creation Date: |
12 | October 9, 2001 |
13 | |
14 | Creator: |
15 | Mario-Jair Campos |
16 | |
17 | Description: |
18 | Private Body Version. |
19 | This package creates Dimension Sets. |
20 | |
21 | History: |
22 | |
23 | 18-DEC-2002 ASHANKAR Bug Fix #2716498 |
24 | |
25 | 04-MAR-2003 PAJOHRI MLS Bug #2721899 |
26 | 1. Modified Update Query for BSC_KPI_DIM_SETS_TL, |
27 | BSC_KPI_DIM_LEVELS_TL, and Insert Query for |
28 | BSC_KPI_DIM_LEVELS_TL. |
29 | 2. Changed nvl(l_Dim_Set_Rec.Bsc_Language, userenv('LANG')) |
30 | to userenv('LANG') |
31 | 18-MAR-2003 PAJOHRI Added Condition to update l_Dim_Set_Rec.Bsc_Dim_Set_Name, only |
32 | when it is Not Null |
33 | 24-JULY-2003 ASHANKAR Fix for the bug 3060555 |
34 | 05-SEP-2003 Adeulgao Fixed bug#3128103 |
35 | 07-JAN-2004 PAJOHRI Bug #3343860, modified procedure Create_Dim_Levels for when |
36 | there is no dimension group associated with dimension set |
37 | 25-FEB-2004 PAJOHRI Bug #3446359, modified insertion logic for |
38 | BSC_KPI_DIM_LEVELS_TL to allow NULL value if NULL values are |
39 | passed from the Public APIs |
40 | 21-JUL-2005 ashankar Bug#4314386 |
41 +======================================================================================+
42 */
43 G_PKG_NAME CONSTANT varchar2(30) := 'BSC_DIMENSION_SETS_PVT';
44 g_db_object varchar2(30) := null;
45
46 --: This procedure is used to create dimension sets in a KPI. This is the entry
47 --: point for the Dimension Sets API.
48 --: This procedure is part of the Dimension Set API.
49
50 procedure Create_Dim_Group_In_Dset(
51 p_commit IN varchar2 := FND_API.G_FALSE
52 ,p_Dim_Set_Rec IN BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
53 ,x_return_status OUT NOCOPY varchar2
54 ,x_msg_count OUT NOCOPY number
55 ,x_msg_data OUT NOCOPY varchar2
56 ) is
57
58 l_count number;
59
60 begin
61 SAVEPOINT CreateBSCDimGrpInDSetPvt;
62 FND_MSG_PUB.Initialize;
63 x_return_status := FND_API.G_RET_STS_SUCCESS;
64 -- Verify value to be inserted does not exist.
65 select count(*)
66 into l_count
67 from BSC_KPI_DIM_GROUPS
68 where indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
69 and dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id
70 and dim_group_id = p_Dim_Set_Rec.Bsc_Dim_Level_Group_Id;
71
72 if l_count = 0 then
73
74 g_db_object := 'BSC_KPI_DIM_GROUPS';
75
76 -- Insert pertaining values into table bsc_kpi_dim_groups.
77 -- Reminder: Index value is hard coded.
78 insert into BSC_KPI_DIM_GROUPS( indicator
79 ,dim_set_id
80 ,dim_group_id
81 ,dim_group_index)
82 values( p_Dim_Set_Rec.Bsc_Kpi_Id
83 ,p_Dim_Set_Rec.Bsc_Dim_Set_Id
84 ,p_Dim_Set_Rec.Bsc_Dim_Level_Group_Id
85 ,p_Dim_Set_Rec.Bsc_Dim_Level_Group_Index);
86 if (p_commit = FND_API.G_TRUE) then
87 commit;
88 end if;
89 else
90 FND_MESSAGE.SET_NAME('BSC','BSC_DSDL_COMBO_EXISTS');
91 FND_MESSAGE.SET_TOKEN('BSC_COMBO', p_Dim_Set_Rec.Bsc_Dim_Set_Id);
92 FND_MSG_PUB.ADD;
93 RAISE FND_API.G_EXC_ERROR;
94 end if;
95 EXCEPTION
96 WHEN FND_API.G_EXC_ERROR THEN
97 ROLLBACK TO CreateBSCDimGrpInDSetPvt;
98 FND_MSG_PUB.Count_And_Get
99 ( p_encoded => FND_API.G_FALSE
100 , p_count => x_msg_count
101 , p_data => x_msg_data
102 );
103 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
104 x_return_status := FND_API.G_RET_STS_ERROR;
105 RAISE;
106 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
107 ROLLBACK TO CreateBSCDimGrpInDSetPvt;
108 FND_MSG_PUB.Count_And_Get
109 ( p_encoded => FND_API.G_FALSE
110 , p_count => x_msg_count
111 , p_data => x_msg_data
112 );
113 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
114 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
115 RAISE;
116 WHEN NO_DATA_FOUND THEN
117 ROLLBACK TO CreateBSCDimGrpInDSetPvt;
118 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
119 IF (x_msg_data IS NOT NULL) THEN
120 x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Create_Dim_Group_In_Dset ';
121 ELSE
122 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Create_Dim_Group_In_Dset ';
123 END IF;
124 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
125 RAISE;
126 WHEN OTHERS THEN
127 ROLLBACK TO CreateBSCDimGrpInDSetPvt;
128 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
129 IF (x_msg_data IS NOT NULL) THEN
130 x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Create_Dim_Group_In_Dset ';
131 ELSE
132 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Create_Dim_Group_In_Dset ';
133 END IF;
134 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
135 RAISE;
136 end Create_Dim_Group_In_Dset;
137
138 /************************************************************************************
139 ************************************************************************************/
140
141 procedure Retrieve_Dim_Group_In_Dset(
142 p_commit IN varchar2 := FND_API.G_FALSE
143 ,p_Dim_Set_Rec IN BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
144 ,x_Dim_Set_Rec IN OUT NOCOPY BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
145 ,x_return_status OUT NOCOPY varchar2
146 ,x_msg_count OUT NOCOPY number
147 ,x_msg_data OUT NOCOPY varchar2
148 ) is
149
150 begin
151 FND_MSG_PUB.Initialize;
152 x_return_status := FND_API.G_RET_STS_SUCCESS;
153 select dim_set_id
154 ,dim_group_index
155 into x_Dim_Set_Rec.Bsc_Dim_Set_Id
156 ,x_Dim_Set_Rec.Bsc_Dim_Level_Group_Index
157 from BSC_KPI_DIM_GROUPS
158 where indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
159 and dim_group_id = p_Dim_Set_Rec.Bsc_Dim_Level_Group_Id
160 and dim_set_id = NVL(p_Dim_Set_Rec.Bsc_Dim_Set_Id, 0);
161 if (p_commit = FND_API.G_TRUE) then
162 commit;
163 end if;
164
165 EXCEPTION
166 WHEN FND_API.G_EXC_ERROR THEN
167 FND_MSG_PUB.Count_And_Get
168 ( p_encoded => FND_API.G_FALSE
169 , p_count => x_msg_count
170 , p_data => x_msg_data
171 );
172 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
173 x_return_status := FND_API.G_RET_STS_ERROR;
174 RAISE;
175 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
176 FND_MSG_PUB.Count_And_Get
177 ( p_encoded => FND_API.G_FALSE
178 , p_count => x_msg_count
179 , p_data => x_msg_data
180 );
181 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
182 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
183 RAISE;
184 WHEN NO_DATA_FOUND THEN
185 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
186 IF (x_msg_data IS NOT NULL) THEN
187 x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Retrieve_Dim_Group_In_Dset ';
188 ELSE
189 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Retrieve_Dim_Group_In_Dset ';
190 END IF;
191 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
192 RAISE;
193 WHEN OTHERS THEN
194 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
195 IF (x_msg_data IS NOT NULL) THEN
196 x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Retrieve_Dim_Group_In_Dset ';
197 ELSE
198 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Retrieve_Dim_Group_In_Dset ';
199 END IF;
200 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
201 RAISE;
202 end Retrieve_Dim_Group_In_Dset;
203
204 /************************************************************************************
205 ************************************************************************************/
206
207 procedure Update_Dim_Group_In_Dset(
208 p_commit IN varchar2 := FND_API.G_FALSE
209 ,p_Dim_Set_Rec IN BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
210 ,x_return_status OUT NOCOPY varchar2
211 ,x_msg_count OUT NOCOPY number
212 ,x_msg_data OUT NOCOPY varchar2
213 ) is
214
215 l_Dim_Set_Rec BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type;
216
217 l_count number;
218
219 begin
220 SAVEPOINT UpdateBSCDimGrpInDSetPvt;
221 FND_MSG_PUB.Initialize;
222 x_return_status := FND_API.G_RET_STS_SUCCESS;
223 -- Check that valid kpi id entered.
224 if p_Dim_Set_Rec.Bsc_Kpi_Id is not null then
225 l_count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_KPIS_B'
226 ,'indicator'
227 ,p_Dim_Set_Rec.Bsc_Kpi_Id);
228 if l_count = 0 then
229 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_KPI_ID');
230 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Dim_Set_Rec.Bsc_Kpi_Id);
231 FND_MSG_PUB.ADD;
232 RAISE FND_API.G_EXC_ERROR;
233 end if;
234 else
235 FND_MESSAGE.SET_NAME('BSC','BSC_NO_KPI_ID_ENTERED');
236 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Dim_Set_Rec.Bsc_Kpi_Id);
237 FND_MSG_PUB.ADD;
238 RAISE FND_API.G_EXC_ERROR;
239 end if;
240
241 -- Check that valid group id entered.
242 if p_Dim_Set_Rec.Bsc_Dim_Level_Group_Id is not null then
243 l_count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_SYS_DIM_GROUPS_TL'
244 ,'dim_group_id'
245 ,p_Dim_Set_Rec.Bsc_Dim_Level_Group_Id);
246 if l_count = 0 then
247 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_GROUP_ID');
248 FND_MESSAGE.SET_TOKEN('BSC_GROUP_ID', p_Dim_Set_Rec.Bsc_Dim_Level_Group_Id);
249 FND_MSG_PUB.ADD;
250 RAISE FND_API.G_EXC_ERROR;
251 end if;
252 else
253 FND_MESSAGE.SET_NAME('BSC','BSC_NO_GROUP_ID_ENTERED');
254 FND_MESSAGE.SET_TOKEN('BSC_GROUP_ID', p_Dim_Set_Rec.Bsc_Dim_Level_Group_Id);
255 FND_MSG_PUB.ADD;
256 RAISE FND_API.G_EXC_ERROR;
257 end if;
258
259 -- Not all values will be passed. We need to make sure values not passed are not
260 -- changed by procedure, therefore we get what is there before we do any updates.
261 Retrieve_Dim_Group_In_Dset( p_commit
262 ,p_Dim_Set_Rec
263 ,l_Dim_Set_Rec
264 ,x_return_status
265 ,x_msg_count
266 ,x_msg_data);
267
268 -- update LOCAL language ,source language, group id and level Id values with PASSED values.
269 l_Dim_Set_Rec.Bsc_Language := p_Dim_Set_Rec.Bsc_Language;
270 l_Dim_Set_Rec.Bsc_Source_Language := p_Dim_Set_Rec.Bsc_Source_Language;
271 l_Dim_Set_Rec.Bsc_Dim_Level_Group_Id := p_Dim_Set_Rec.Bsc_Dim_Level_Group_Id;
272 l_Dim_Set_Rec.Bsc_Kpi_Id := p_Dim_Set_Rec.Bsc_Kpi_Id;
273
274 -- Copy PASSED Record values into LOCAL Record values for the PASSED Record values
275 -- which are NOT NULL.
276 if p_Dim_Set_Rec.Bsc_Dim_Set_Id is not null then
277 l_Dim_Set_Rec.Bsc_Dim_Set_Id := p_Dim_Set_Rec.Bsc_Dim_Set_Id;
278 end if;
279 if p_Dim_Set_Rec.Bsc_Dim_Level_Group_Index is not null then
280 l_Dim_Set_Rec.Bsc_Dim_Level_Group_Index := p_Dim_Set_Rec.Bsc_Dim_Level_Group_Index;
281 end if;
282
283 update BSC_KPI_DIM_GROUPS
284 set dim_group_index = l_Dim_Set_Rec.Bsc_Dim_Level_Group_Index
285 where indicator = l_Dim_Set_Rec.Bsc_Kpi_Id
286 and dim_group_id = l_Dim_Set_Rec.Bsc_Dim_Level_Group_Id
287 and dim_set_id = l_Dim_Set_Rec.Bsc_Dim_Set_Id;
288
289 if (p_commit = FND_API.G_TRUE) then
290 commit;
291 end if;
292
293 EXCEPTION
294 WHEN FND_API.G_EXC_ERROR THEN
295 ROLLBACK TO UpdateBSCDimGrpInDSetPvt;
296 FND_MSG_PUB.Count_And_Get
297 ( p_encoded => FND_API.G_FALSE
298 , p_count => x_msg_count
299 , p_data => x_msg_data
300 );
301 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
302 x_return_status := FND_API.G_RET_STS_ERROR;
303 RAISE;
304 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
305 ROLLBACK TO UpdateBSCDimGrpInDSetPvt;
306 FND_MSG_PUB.Count_And_Get
307 ( p_encoded => FND_API.G_FALSE
308 , p_count => x_msg_count
309 , p_data => x_msg_data
310 );
311 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
312 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
313 RAISE;
314 WHEN NO_DATA_FOUND THEN
315 ROLLBACK TO UpdateBSCDimGrpInDSetPvt;
316 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
317 IF (x_msg_data IS NOT NULL) THEN
318 x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Update_Dim_Group_In_Dset ';
319 ELSE
320 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Update_Dim_Group_In_Dset ';
321 END IF;
322 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
323 RAISE;
324 WHEN OTHERS THEN
325 ROLLBACK TO UpdateBSCDimGrpInDSetPvt;
326 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
327 IF (x_msg_data IS NOT NULL) THEN
328 x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Update_Dim_Group_In_Dset ';
329 ELSE
330 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Update_Dim_Group_In_Dset ';
331 END IF;
332 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
333 RAISE;
334 end Update_Dim_Group_In_Dset;
335
336 /************************************************************************************
337 ************************************************************************************/
338
339 procedure Delete_Dim_Group_In_Dset(
340 p_commit IN varchar2 := FND_API.G_FALSE
341 ,p_Dim_Set_Rec IN BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
342 ,x_return_status OUT NOCOPY varchar2
343 ,x_msg_count OUT NOCOPY number
344 ,x_msg_data OUT NOCOPY varchar2
345 ) is
346
347 l_count number;
348
349 begin
350 SAVEPOINT DeleteBSCDimGrpInDSetPvt;
351 FND_MSG_PUB.Initialize;
352 x_return_status := FND_API.G_RET_STS_SUCCESS;
353 -- Check that valid kpi id entered.
354 if p_Dim_Set_Rec.Bsc_Kpi_Id is not null then
355 l_count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_KPIS_B'
356 ,'indicator'
357 ,p_Dim_Set_Rec.Bsc_Kpi_Id);
358 if l_count = 0 then
359 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_KPI_ID');
360 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Dim_Set_Rec.Bsc_Kpi_Id);
361 FND_MSG_PUB.ADD;
362 RAISE FND_API.G_EXC_ERROR;
363 end if;
364 else
365 FND_MESSAGE.SET_NAME('BSC','BSC_NO_KPI_ID_ENTERED');
366 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Dim_Set_Rec.Bsc_Kpi_Id);
367 FND_MSG_PUB.ADD;
368 RAISE FND_API.G_EXC_ERROR;
369 end if;
370
371 -- Check that valid group id entered.
372 if p_Dim_Set_Rec.Bsc_Dim_Level_Group_Id is not null then
373 l_count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_SYS_DIM_GROUPS_TL'
374 ,'dim_group_id'
375 ,p_Dim_Set_Rec.Bsc_Dim_Level_Group_Id);
376 if l_count = 0 then
377 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_GROUP_ID');
378 FND_MESSAGE.SET_TOKEN('BSC_GROUP_ID', p_Dim_Set_Rec.Bsc_Dim_Level_Group_Id);
379 FND_MSG_PUB.ADD;
380 RAISE FND_API.G_EXC_ERROR;
381 end if;
382 else
383 FND_MESSAGE.SET_NAME('BSC','BSC_NO_GROUP_ID_ENTERED');
384 FND_MESSAGE.SET_TOKEN('BSC_GROUP_ID', p_Dim_Set_Rec.Bsc_Dim_Level_Group_Id);
385 FND_MSG_PUB.ADD;
386 RAISE FND_API.G_EXC_ERROR;
387 end if;
388
389 delete from BSC_KPI_DIM_GROUPS
390 where indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
391 and dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id
392 and dim_group_id = p_Dim_Set_Rec.Bsc_Dim_Level_Group_Id;
393
394 if (p_commit = FND_API.G_TRUE) then
395 commit;
396 end if;
397
398
399 EXCEPTION
400 WHEN FND_API.G_EXC_ERROR THEN
401 ROLLBACK TO DeleteBSCDimGrpInDSetPvt;
402 FND_MSG_PUB.Count_And_Get
403 ( p_encoded => FND_API.G_FALSE
404 , p_count => x_msg_count
405 , p_data => x_msg_data
406 );
407 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
408 x_return_status := FND_API.G_RET_STS_ERROR;
409 RAISE;
410 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
411 ROLLBACK TO DeleteBSCDimGrpInDSetPvt;
412 FND_MSG_PUB.Count_And_Get
413 ( p_encoded => FND_API.G_FALSE
414 , p_count => x_msg_count
415 , p_data => x_msg_data
416 );
417 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
418 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
419 RAISE;
420 WHEN NO_DATA_FOUND THEN
421 ROLLBACK TO DeleteBSCDimGrpInDSetPvt;
422 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
423 IF (x_msg_data IS NOT NULL) THEN
424 x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Delete_Dim_Group_In_Dset ';
425 ELSE
426 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Delete_Dim_Group_In_Dset ';
427 END IF;
428 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
429 RAISE;
430 WHEN OTHERS THEN
431 ROLLBACK TO DeleteBSCDimGrpInDSetPvt;
432 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
433 IF (x_msg_data IS NOT NULL) THEN
434 x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Delete_Dim_Group_In_Dset ';
435 ELSE
436 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Delete_Dim_Group_In_Dset ';
437 END IF;
438 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
439 RAISE;
440 end Delete_Dim_Group_In_Dset;
441
442 /************************************************************************************
443 ************************************************************************************/
444
445 --: This procedure creates the dimension set id and name for the KPI.
446 --: This procedure belongs to the Dimension Set API.
447
448 procedure Create_Bsc_Kpi_Dim_Sets_Tl(
449 p_commit IN varchar2 := FND_API.G_FALSE
450 ,p_Dim_Set_Rec IN BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
451 ,x_return_status OUT NOCOPY varchar2
452 ,x_msg_count OUT NOCOPY number
453 ,x_msg_data OUT NOCOPY varchar2
454 ) is
455
456 l_Dim_Set_Rec BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type;
457
458 l_count number;
459
460 begin
461 SAVEPOINT CreateBSCKpiDSetTlPvt;
462 FND_MSG_PUB.Initialize;
463 x_return_status := FND_API.G_RET_STS_SUCCESS;
464 -- Assign all values in the passed "Record" parameter to the locally defined
465 -- "Record" variable.
466 l_Dim_Set_Rec := p_Dim_Set_Rec;
467
468 -- set l_count to zero.
469 -- This will determine if the Dset for this KPI already exists.
470 l_count := 0;
471
472 -- If The dimension set Id is null then get the next available one.
473 if l_Dim_Set_Rec.Bsc_Dim_Set_Id is null then
474 select count(*)
475 into l_Dim_Set_Rec.Bsc_Dim_Set_Id
476 from BSC_KPI_DIM_SETS_TL
477 where indicator = l_Dim_Set_Rec.Bsc_Kpi_Id;
478 else -- Determine if the KPI already contains this dimension set id.
479 select count(*)
480 into l_count
481 from BSC_KPI_DIM_SETS_TL
482 where indicator = l_Dim_Set_Rec.Bsc_Kpi_Id
483 and dim_set_id = l_Dim_Set_Rec.Bsc_Dim_Set_Id;
484 end if;
485
486 -- Following current BSC standards name the dimension set with 'Dimension set '
487 -- concatenated with the dimension set id.
488 -- PAJOHRI Added the if condition
489 IF (l_Dim_Set_Rec.Bsc_Dim_Set_Name IS NULL) THEN
490 l_Dim_Set_Rec.Bsc_Dim_Set_Name := 'Dimension set ' || l_Dim_Set_rec.Bsc_Dim_Set_Id;
491 END IF;
492
493 -- If Dim Set Id not in KPI yet then insert pertaining values, else raise error.
494 if l_count = 0 then
495
496 g_db_object := 'BSC_KPI_DIM_SETS_TL';
497
498 -- Insert pertaining values into table bsc_kpi_dim_sets_tl.
499 /* Fixed bug 2787553 */
500 insert into BSC_KPI_DIM_SETS_TL( indicator
501 ,dim_set_id
502 ,language
503 ,source_lang
504 ,name
505 ,created_by -- Added for PMD
506 ,creation_date -- Added for PMD
507 ,last_updated_by -- Added for PMD
508 ,last_update_date -- Added for PMD
509 ,last_update_login -- Added for PMD
510 ,short_name
511 )
512 SELECT l_Dim_Set_Rec.Bsc_Kpi_Id
513 ,l_Dim_Set_Rec.Bsc_Dim_Set_Id
514 ,L.LANGUAGE_CODE
515 ,USERENV('LANG')
516 ,l_Dim_Set_Rec.Bsc_Dim_Set_Name
517 ,fnd_global.USER_ID -- Added for PMD
518 ,sysdate -- Added for PMD
519 ,fnd_global.USER_ID -- Added for PMD
520 ,sysdate -- Added for PMD
521 ,fnd_global.LOGIN_ID -- Added for PMD
522 ,l_Dim_Set_Rec.Bsc_Dim_Set_Short_Name
523 FROM FND_LANGUAGES L
524 WHERE L.INSTALLED_FLAG IN ('I', 'B')
525 AND NOT EXISTS
526 ( SELECT NULL
527 FROM BSC_KPI_DIM_SETS_TL T
528 WHERE T.indicator = l_Dim_Set_Rec.Bsc_Kpi_Id
529 AND T.dim_set_id = l_Dim_Set_Rec.Bsc_Dim_Set_Id
530 AND T.LANGUAGE = L.LANGUAGE_CODE);
531 --PAJOHRI added 23-JUL-2003
532 if(l_Dim_Set_Rec.Bsc_Dim_Set_Id <> 0) then
533 -- Reminder: For now we will assume that if more than one dimension set is created
534 -- then the user wants to be able to change dimension sets across
535 -- Analysis options.
536 update BSC_KPI_ANALYSIS_GROUPS
537 set change_dim_set = 1
538 where indicator = l_Dim_Set_Rec.Bsc_Kpi_Id
539 and analysis_group_id = 0;
540 end if;
541 if (p_commit = FND_API.G_TRUE) then
542 commit;
543 end if;
544
545 else
546 if l_Dim_Set_Rec.Bsc_New_Dset = 'Y' then
547 FND_MESSAGE.SET_NAME('BSC','BSC_DSET_KPI_EXISTS');
548 FND_MESSAGE.SET_TOKEN('BSC_KPI', l_Dim_Set_Rec.Bsc_Kpi_Id);
549 FND_MSG_PUB.ADD;
550 RAISE FND_API.G_EXC_ERROR;
551 end if;
552 end if;
553
554
555 EXCEPTION
556 WHEN FND_API.G_EXC_ERROR THEN
557 ROLLBACK TO CreateBSCKpiDSetTlPvt;
558 FND_MSG_PUB.Count_And_Get
559 ( p_encoded => FND_API.G_FALSE
560 , p_count => x_msg_count
561 , p_data => x_msg_data
562 );
563 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
564 x_return_status := FND_API.G_RET_STS_ERROR;
565 RAISE;
566 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
567 ROLLBACK TO CreateBSCKpiDSetTlPvt;
568 FND_MSG_PUB.Count_And_Get
569 ( p_encoded => FND_API.G_FALSE
570 , p_count => x_msg_count
571 , p_data => x_msg_data
572 );
573 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
574 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
575 RAISE;
576 WHEN NO_DATA_FOUND THEN
577 ROLLBACK TO CreateBSCKpiDSetTlPvt;
578 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
579 IF (x_msg_data IS NOT NULL) THEN
580 x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Create_Bsc_Kpi_Dim_Sets_Tl ';
581 ELSE
582 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Create_Bsc_Kpi_Dim_Sets_Tl ';
583 END IF;
584 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
585 RAISE;
586 WHEN OTHERS THEN
587 ROLLBACK TO CreateBSCKpiDSetTlPvt;
588 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
589 IF (x_msg_data IS NOT NULL) THEN
590 x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Create_Bsc_Kpi_Dim_Sets_Tl ';
591 ELSE
592 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Create_Bsc_Kpi_Dim_Sets_Tl ';
593 END IF;
594 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
595 RAISE;
596 end Create_Bsc_Kpi_Dim_Sets_Tl;
597
598 /************************************************************************************
599 ************************************************************************************/
600
601 procedure Retrieve_Bsc_Kpi_Dim_Sets_Tl(
602 p_commit IN varchar2 := FND_API.G_FALSE
603 ,p_Dim_Set_Rec IN BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
604 ,x_Dim_Set_Rec IN OUT NOCOPY BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
605 ,x_return_status OUT NOCOPY varchar2
606 ,x_msg_count OUT NOCOPY number
607 ,x_msg_data OUT NOCOPY varchar2
608 ) is
609
610 begin
611
612 g_db_object := 'Retrieve_Bsc_Kpi_Dim_Sets_Tl';
613 FND_MSG_PUB.Initialize;
614 x_return_status := FND_API.G_RET_STS_SUCCESS;
615 -- fix the bug 2716498 INCORRECT MESSAGE REGARDING DELETE MASTER KPI WHILE ATTEMPTING TO ADD KPI TO TAB
616 -- added the condition "and LANGUAGE = p_Dim_Set_Rec.Bsc_Language" because two rows were returned.
617
618
619 SELECT DISTINCT NAME
620 ,SHORT_NAME
621 INTO x_Dim_Set_Rec.Bsc_Dim_Set_Name
622 ,x_Dim_Set_Rec.Bsc_Dim_Set_Short_Name
623 FROM BSC_KPI_DIM_SETS_TL
624 WHERE INDICATOR = p_Dim_Set_Rec.Bsc_Kpi_Id
625 AND DIM_SET_ID = p_Dim_Set_Rec.Bsc_Dim_Set_Id
626 AND LANGUAGE = USERENV('LANG');
627
628
629
630 if (p_commit = FND_API.G_TRUE) then
631 commit;
632 end if;
633
634 EXCEPTION
635 WHEN FND_API.G_EXC_ERROR THEN
636 ROLLBACK TO CreateBSCDimLevMdPVT;
637 FND_MSG_PUB.Count_And_Get
638 ( p_encoded => FND_API.G_FALSE
639 , p_count => x_msg_count
640 , p_data => x_msg_data
641 );
642 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
643 x_return_status := FND_API.G_RET_STS_ERROR;
644 RAISE;
645 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
646 ROLLBACK TO CreateBSCDimLevMdPVT;
647 FND_MSG_PUB.Count_And_Get
648 ( p_encoded => FND_API.G_FALSE
649 , p_count => x_msg_count
650 , p_data => x_msg_data
651 );
652 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
653 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
654 RAISE;
655 WHEN NO_DATA_FOUND THEN
656 ROLLBACK TO CreateBSCDimLevMdPVT;
657 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
658 IF (x_msg_data IS NOT NULL) THEN
659 x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Retrieve_Bsc_Kpi_Dim_Sets_Tl ';
660 ELSE
661 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Retrieve_Bsc_Kpi_Dim_Sets_Tl ';
662 END IF;
663 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
664 RAISE;
665 WHEN OTHERS THEN
666 ROLLBACK TO CreateBSCDimLevMdPVT;
667 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
668 IF (x_msg_data IS NOT NULL) THEN
669 x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Retrieve_Bsc_Kpi_Dim_Sets_Tl ';
670 ELSE
671 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Retrieve_Bsc_Kpi_Dim_Sets_Tl ';
672 END IF;
673 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
674 RAISE;
675 end Retrieve_Bsc_Kpi_Dim_Sets_Tl;
676
677 /************************************************************************************
678 ************************************************************************************/
679
680 procedure Update_Bsc_Kpi_Dim_Sets_Tl(
681 p_commit IN varchar2 := FND_API.G_FALSE
682 ,p_Dim_Set_Rec IN BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
683 ,x_return_status OUT NOCOPY varchar2
684 ,x_msg_count OUT NOCOPY number
685 ,x_msg_data OUT NOCOPY varchar2
686 ) is
687
688 l_Dim_Set_Rec BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type;
689
690 l_count number;
691
692 begin
693 SAVEPOINT UpdateBSCKpiDSetTlPvt;
694 FND_MSG_PUB.Initialize;
695 x_return_status := FND_API.G_RET_STS_SUCCESS;
696 -- Check that valid KPI id was entered.
697 if p_Dim_Set_Rec.Bsc_Kpi_Id is not null then
698
699 SELECT COUNT(1) INTO l_Count
700 FROM BSC_KPIS_B
701 WHERE INDICATOR = p_Dim_Set_Rec.Bsc_Kpi_Id;
702
703 if l_count = 0 then
704 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_KPI_ID');
705 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Dim_Set_Rec.Bsc_Kpi_Id);
706 FND_MSG_PUB.ADD;
707 RAISE FND_API.G_EXC_ERROR;
708 else
709 select count(dim_set_id)
710 into l_count
711 from BSC_KPI_DIM_SETS_TL
712 where indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
713 and dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id;
714 if l_count = 0 then
715 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_DSET_ID');
716 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Dim_Set_Rec.Bsc_Kpi_Id);
717 FND_MSG_PUB.ADD;
718 RAISE FND_API.G_EXC_ERROR;
719 end if;
720 end if;
721 else
722 FND_MESSAGE.SET_NAME('BSC','BSC_NO_KPI_ID_ENTERED');
723 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Dim_Set_Rec.Bsc_Kpi_Id);
724 FND_MSG_PUB.ADD;
725 RAISE FND_API.G_EXC_ERROR;
726 end if;
727
728 l_Dim_Set_Rec := p_Dim_Set_Rec;
729
730 -- Added for PMD
731 IF p_Dim_Set_Rec.Bsc_Last_Updated_By IS NULL THEN
732 l_Dim_Set_Rec.Bsc_Last_Updated_By := fnd_global.USER_ID;
733 END IF;
734
735 IF p_Dim_Set_Rec.Bsc_Last_Update_Login IS NULL THEN
736 l_Dim_Set_Rec.Bsc_Last_Update_Login := fnd_global.LOGIN_ID;
737 END IF;
738
739 IF p_Dim_Set_Rec.Bsc_Dim_Set_Short_Name IS NOT NULL THEN
740 l_Dim_Set_Rec.Bsc_Dim_Set_Short_Name := p_Dim_Set_Rec.Bsc_Dim_Set_Short_Name;
741 END IF;
742
743
744 if l_Dim_Set_Rec.Bsc_Dim_Set_Name is not null then
745 UPDATE BSC_KPI_DIM_SETS_TL
746 SET NAME = l_Dim_Set_Rec.Bsc_Dim_Set_Name
747 ,SOURCE_LANG = userenv('LANG')
748 , LAST_UPDATED_BY = l_Dim_Set_Rec.Bsc_Last_Updated_By -- Added for PMD
749 , LAST_UPDATE_DATE = SYSDATE -- Added for PMD
750 , LAST_UPDATE_LOGIN = l_Dim_Set_Rec.Bsc_Last_Update_Login -- Added for PMD
751 , SHORT_NAME = l_Dim_Set_Rec.Bsc_Dim_Set_Short_Name
752 where indicator = l_Dim_Set_Rec.Bsc_Kpi_Id
753 and dim_set_id = l_Dim_Set_Rec.Bsc_Dim_Set_Id
754 and userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
755 end if;
756
757 if (p_commit = FND_API.G_TRUE) then
758 commit;
759 end if;
760
761 EXCEPTION
762 WHEN FND_API.G_EXC_ERROR THEN
763 ROLLBACK TO UpdateBSCKpiDSetTlPvt;
764 FND_MSG_PUB.Count_And_Get
765 ( p_encoded => FND_API.G_FALSE
766 , p_count => x_msg_count
767 , p_data => x_msg_data
768 );
769 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
770 x_return_status := FND_API.G_RET_STS_ERROR;
771 RAISE;
772 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
773 ROLLBACK TO UpdateBSCKpiDSetTlPvt;
774 FND_MSG_PUB.Count_And_Get
775 ( p_encoded => FND_API.G_FALSE
776 , p_count => x_msg_count
777 , p_data => x_msg_data
778 );
779 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
780 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
781 RAISE;
782 WHEN NO_DATA_FOUND THEN
783 ROLLBACK TO UpdateBSCKpiDSetTlPvt;
784 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
785 IF (x_msg_data IS NOT NULL) THEN
786 x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Update_Bsc_Kpi_Dim_Sets_Tl ';
787 ELSE
788 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Update_Bsc_Kpi_Dim_Sets_Tl ';
789 END IF;
790 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
791 RAISE;
792 WHEN OTHERS THEN
793 ROLLBACK TO UpdateBSCKpiDSetTlPvt;
794 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
795 IF (x_msg_data IS NOT NULL) THEN
796 x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Update_Bsc_Kpi_Dim_Sets_Tl ';
797 ELSE
798 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Update_Bsc_Kpi_Dim_Sets_Tl ';
799 END IF;
800 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
801 RAISE;
802 end Update_Bsc_Kpi_Dim_Sets_Tl;
803
804 /************************************************************************************
805 ************************************************************************************/
806
807 procedure Delete_Bsc_Kpi_Dim_Sets_Tl(
808 p_commit IN varchar2 := FND_API.G_FALSE
809 ,p_Dim_Set_Rec IN BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
810 ,x_return_status OUT NOCOPY varchar2
811 ,x_msg_count OUT NOCOPY number
812 ,x_msg_data OUT NOCOPY varchar2
813 ) is
814
815 l_count number;
816
817 begin
818 SAVEPOINT DeleteBSCKpiDSetTlPvt;
819 FND_MSG_PUB.Initialize;
820 x_return_status := FND_API.G_RET_STS_SUCCESS;
821 -- If there are no groups assigned to this dimension set then delete it.
822 select count(dim_group_id)
823 into l_count
824 from BSC_KPI_DIM_GROUPS
825 where indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
826 and dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id;
827
828 if l_count = 0 then
829 delete from BSC_KPI_DIM_SETS_TL
830 where indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
831 and dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id;
832
833 if (p_commit = FND_API.G_TRUE) then
834 commit;
835 end if;
836
837 end if;
838
839
840 EXCEPTION
841 WHEN FND_API.G_EXC_ERROR THEN
842 ROLLBACK TO DeleteBSCKpiDSetTlPvt;
843 FND_MSG_PUB.Count_And_Get
844 ( p_encoded => FND_API.G_FALSE
845 , p_count => x_msg_count
846 , p_data => x_msg_data
847 );
848 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
849 x_return_status := FND_API.G_RET_STS_ERROR;
850 RAISE;
851 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
852 ROLLBACK TO DeleteBSCKpiDSetTlPvt;
853 FND_MSG_PUB.Count_And_Get
854 ( p_encoded => FND_API.G_FALSE
855 , p_count => x_msg_count
856 , p_data => x_msg_data
857 );
858 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
859 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
860 RAISE;
861 WHEN NO_DATA_FOUND THEN
862 ROLLBACK TO DeleteBSCKpiDSetTlPvt;
863 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
864 IF (x_msg_data IS NOT NULL) THEN
865 x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Delete_Bsc_Kpi_Dim_Sets_Tl ';
866 ELSE
867 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Delete_Bsc_Kpi_Dim_Sets_Tl ';
868 END IF;
869 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
870 RAISE;
871 WHEN OTHERS THEN
872 ROLLBACK TO DeleteBSCKpiDSetTlPvt;
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_DIMENSION_SETS_PVT.Delete_Bsc_Kpi_Dim_Sets_Tl ';
876 ELSE
877 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Delete_Bsc_Kpi_Dim_Sets_Tl ';
878 END IF;
879 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
880 RAISE;
881 end Delete_Bsc_Kpi_Dim_Sets_Tl;
882
883 /************************************************************************************
884 ************************************************************************************/
885
886 --: This procedure creates the properties for the dimension set for the KPI.
887 --: This procedure belongs to the Dimension Set API.
888
889 procedure Create_Dim_Level_Properties(
890 p_commit IN varchar2 := FND_API.G_FALSE
891 ,p_Dim_Set_Rec IN BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
892 ,x_return_status OUT NOCOPY varchar2
893 ,x_msg_count OUT NOCOPY number
894 ,x_msg_data OUT NOCOPY varchar2
895 ) is
896
897 l_count number;
898
899 begin
900 SAVEPOINT CreateBSCDimLevPropPvt;
901 FND_MSG_PUB.Initialize;
902 x_return_status := FND_API.G_RET_STS_SUCCESS;
903 -- Determine if the properties for this KPI, dimension set and Dimension id
904 -- combination have been set.
905 IF (p_Dim_Set_Rec.Bsc_Level_Id IS NOT NULL) THEN
906 select count(*)
907 into l_count
908 from BSC_KPI_DIM_LEVEL_PROPERTIES
909 where indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
910 and dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id
911 and dim_level_id = p_Dim_Set_Rec.Bsc_Level_Id;
912
913 -- If properties have not been set then go ahead and set them, else raise an error.
914 if l_count = 0 then
915
916 g_db_object := 'BSC_KPI_DIM_LEVEL_PROPERTIES';
917
918 -- Insert pertaining values into table bsc_kpi_dim_level_properties.
919 -- Reminder: Some values are hard coded, need to get the source.
920 insert into BSC_KPI_DIM_LEVEL_PROPERTIES( indicator
921 ,dim_set_id
922 ,dim_level_id
923 ,position
924 ,total0
925 ,level_display
926 ,default_key_value
927 ,user_level0
928 ,user_level1
929 ,user_level1_default
930 ,user_level2
931 ,user_level2_default
932 ,target_level)
933 values( p_Dim_Set_Rec.Bsc_Kpi_Id
934 ,p_Dim_Set_Rec.Bsc_Dim_Set_Id
935 ,p_Dim_Set_Rec.Bsc_Level_Id
936 ,p_Dim_Set_Rec.Bsc_Dset_Position
937 ,p_Dim_Set_Rec.Bsc_Dset_Total0
938 ,p_Dim_Set_Rec.Bsc_Dset_Level_Display
939 ,p_Dim_Set_Rec.Bsc_Dset_Default_Key_Value
940 ,p_Dim_Set_Rec.Bsc_Dset_User_Level0
941 ,p_Dim_Set_Rec.Bsc_Dset_User_Level1
942 ,p_Dim_Set_Rec.Bsc_Dset_User_Level1_Default
943 ,p_Dim_Set_Rec.Bsc_Dset_User_Level2
944 ,p_Dim_Set_Rec.Bsc_Dset_User_Level2_Default
945 ,p_Dim_Set_Rec.Bsc_Dset_Target_Level);
946 else
947 FND_MESSAGE.SET_NAME('BSC','BSC_DSDL_COMBO_EXISTS');
948 FND_MESSAGE.SET_TOKEN('BSC_COMBO', p_Dim_Set_Rec.Bsc_Kpi_Id);
949 FND_MSG_PUB.ADD;
950 RAISE FND_API.G_EXC_ERROR;
951 end if;
952 END IF;
953 if (p_commit = FND_API.G_TRUE) then
954 commit;
955 end if;
956 EXCEPTION
957 WHEN FND_API.G_EXC_ERROR THEN
958 ROLLBACK TO CreateBSCDimLevPropPvt;
959 FND_MSG_PUB.Count_And_Get
960 ( p_encoded => FND_API.G_FALSE
961 , p_count => x_msg_count
962 , p_data => x_msg_data
963 );
964 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
965 x_return_status := FND_API.G_RET_STS_ERROR;
966 RAISE;
967 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
968 ROLLBACK TO CreateBSCDimLevPropPvt;
969 FND_MSG_PUB.Count_And_Get
970 ( p_encoded => FND_API.G_FALSE
971 , p_count => x_msg_count
972 , p_data => x_msg_data
973 );
974 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
975 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
976 RAISE;
977 WHEN NO_DATA_FOUND THEN
978 ROLLBACK TO CreateBSCDimLevPropPvt;
979 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
980 IF (x_msg_data IS NOT NULL) THEN
981 x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Create_Dim_Level_Properties ';
982 ELSE
983 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Create_Dim_Level_Properties ';
984 END IF;
985 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
986 RAISE;
987 WHEN OTHERS THEN
988 ROLLBACK TO CreateBSCDimLevPropPvt;
989 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
990 IF (x_msg_data IS NOT NULL) THEN
991 x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Create_Dim_Level_Properties ';
992 ELSE
993 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Create_Dim_Level_Properties ';
994 END IF;
995 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
996 RAISE;
997 end Create_Dim_Level_Properties;
998
999 /************************************************************************************
1000 ************************************************************************************/
1001
1002 procedure Retrieve_Dim_Level_Properties(
1003 p_commit IN varchar2 := FND_API.G_FALSE
1004 ,p_Dim_Set_Rec IN BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
1005 ,x_Dim_Set_Rec IN OUT NOCOPY BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
1006 ,x_return_status OUT NOCOPY varchar2
1007 ,x_msg_count OUT NOCOPY number
1008 ,x_msg_data OUT NOCOPY varchar2
1009 ) is
1010
1011 begin
1012
1013 g_db_object := 'Retrieve_Dim_Level_Properties';
1014 FND_MSG_PUB.Initialize;
1015 x_return_status := FND_API.G_RET_STS_SUCCESS;
1016 select distinct position
1017 ,total0
1018 ,level_display
1019 ,default_key_value
1020 ,user_level0
1021 ,user_level1
1022 ,user_level1_default
1023 ,user_level2
1024 ,user_level2_default
1025 into x_Dim_Set_Rec.Bsc_Dset_Position
1026 ,x_Dim_Set_Rec.Bsc_Dset_Total0
1027 ,x_Dim_Set_Rec.Bsc_Dset_Level_Display
1028 ,x_Dim_Set_Rec.Bsc_Dset_Default_Key_Value
1029 ,x_Dim_Set_Rec.Bsc_Dset_User_Level0
1030 ,x_Dim_Set_Rec.Bsc_Dset_User_Level1
1031 ,x_Dim_Set_Rec.Bsc_Dset_User_Level1_Default
1032 ,x_Dim_Set_Rec.Bsc_Dset_User_Level2
1033 ,x_Dim_Set_Rec.Bsc_Dset_User_Level2_Default
1034 from BSC_KPI_DIM_LEVEL_PROPERTIES
1035 where indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
1036 and dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id
1037 and dim_level_id = p_Dim_Set_Rec.Bsc_Level_Id;
1038
1039 if (p_commit = FND_API.G_TRUE) then
1040 commit;
1041 end if;
1042
1043 EXCEPTION
1044 WHEN FND_API.G_EXC_ERROR THEN
1045 FND_MSG_PUB.Count_And_Get
1046 ( p_encoded => FND_API.G_FALSE
1047 , p_count => x_msg_count
1048 , p_data => x_msg_data
1049 );
1050 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1051 x_return_status := FND_API.G_RET_STS_ERROR;
1052 RAISE;
1053 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1054 FND_MSG_PUB.Count_And_Get
1055 ( p_encoded => FND_API.G_FALSE
1056 , p_count => x_msg_count
1057 , p_data => x_msg_data
1058 );
1059 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1060 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1061 RAISE;
1062 WHEN NO_DATA_FOUND THEN
1063 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1064 IF (x_msg_data IS NOT NULL) THEN
1065 x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Retrieve_Dim_Level_Properties ';
1066 ELSE
1067 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Retrieve_Dim_Level_Properties ';
1068 END IF;
1069 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1070 RAISE;
1071 WHEN OTHERS THEN
1072 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1073 IF (x_msg_data IS NOT NULL) THEN
1074 x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Retrieve_Dim_Level_Properties ';
1075 ELSE
1076 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Retrieve_Dim_Level_Properties ';
1077 END IF;
1078 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1079 RAISE;
1080 end Retrieve_Dim_Level_Properties;
1081
1082 /************************************************************************************
1083 ************************************************************************************/
1084
1085 procedure Update_Dim_Level_Properties(
1086 p_commit IN varchar2 := FND_API.G_FALSE
1087 ,p_Dim_Set_Rec IN BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
1088 ,x_return_status OUT NOCOPY varchar2
1089 ,x_msg_count OUT NOCOPY number
1090 ,x_msg_data OUT NOCOPY varchar2
1091 ) is
1092
1093 l_Dim_Set_Rec BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type;
1094
1095 l_count number;
1096
1097 begin
1098
1099 SAVEPOINT UpdateBSCDimLevPropPvt;
1100 FND_MSG_PUB.Initialize;
1101 x_return_status := FND_API.G_RET_STS_SUCCESS;
1102 -- Check that valid KPI id was entered.
1103 if p_Dim_Set_Rec.Bsc_Kpi_Id is not null then
1104 l_count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_KPIS_B'
1105 ,'indicator'
1106 ,p_Dim_Set_Rec.Bsc_Kpi_Id);
1107 if l_count = 0 then
1108 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_KPI_ID');
1109 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Dim_Set_Rec.Bsc_Kpi_Id);
1110 FND_MSG_PUB.ADD;
1111 RAISE FND_API.G_EXC_ERROR;
1112 else
1113 select count(dim_set_id)
1114 into l_count
1115 from BSC_KPI_DIM_SETS_TL
1116 where indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
1117 and dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id;
1118 if l_count = 0 then
1119 FND_MESSAGE.SET_NAME('BSC','BSC_DSDL_COMBO_EXISTS');
1120 FND_MESSAGE.SET_TOKEN('BSC_COMBO', p_Dim_Set_Rec.Bsc_Kpi_Id);
1121 FND_MSG_PUB.ADD;
1122 RAISE FND_API.G_EXC_ERROR;
1123 end if;
1124 end if;
1125 else
1126 FND_MESSAGE.SET_NAME('BSC','BSC_NO_KPI_ID_ENTERED');
1127 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Dim_Set_Rec.Bsc_Kpi_Id);
1128 FND_MSG_PUB.ADD;
1129 RAISE FND_API.G_EXC_ERROR;
1130 end if;
1131
1132 -- Check that valid Level Id was entered.
1133 if p_Dim_Set_Rec.Bsc_Level_Id is not null then
1134 l_count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_SYS_DIM_LEVELS_B'
1135 ,'dim_level_id'
1136 ,p_Dim_Set_Rec.Bsc_Level_Id);
1137 if l_count = 0 then
1138 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_LEVEL_ID');
1139 FND_MESSAGE.SET_TOKEN('BSC_LEVEL_ID', p_Dim_Set_Rec.Bsc_Level_Id);
1140 FND_MSG_PUB.ADD;
1141 RAISE FND_API.G_EXC_ERROR;
1142 end if;
1143 else
1144 FND_MESSAGE.SET_NAME('BSC','BSC_NO_LEVEL_ID_ENTERED');
1145 FND_MESSAGE.SET_TOKEN('BSC_LEVEL_ID', p_Dim_Set_Rec.Bsc_Level_Id);
1146 FND_MSG_PUB.ADD;
1147 RAISE FND_API.G_EXC_ERROR;
1148 end if;
1149
1150 -- Not all values will be passed. We need to make sure values not passed are not
1151 -- changed by procedure, therefore we get what is there before we do any updates.
1152 Retrieve_Dim_Level_Properties( p_commit
1153 ,p_Dim_Set_Rec
1154 ,l_Dim_Set_Rec
1155 ,x_return_status
1156 ,x_msg_count
1157 ,x_msg_data);
1158
1159 -- update LOCAL language ,source language, kpi id, dim set id and level Id values with PASSED values.
1160 l_Dim_Set_Rec.Bsc_Language := p_Dim_Set_Rec.Bsc_Language;
1161 l_Dim_Set_Rec.Bsc_Source_Language := p_Dim_Set_Rec.Bsc_Source_Language;
1162 l_Dim_Set_Rec.Bsc_Kpi_Id := p_Dim_Set_Rec.Bsc_Kpi_Id;
1163 l_Dim_Set_Rec.Bsc_Dim_Set_Id := p_Dim_Set_Rec.Bsc_Dim_Set_Id;
1164 l_Dim_Set_Rec.Bsc_Level_Id := p_Dim_Set_Rec.Bsc_Level_Id;
1165
1166 -- Copy PASSED Record values into LOCAL Record values for the PASSED Record values
1167 -- which are NOT NULL.
1168 if p_Dim_Set_Rec.Bsc_Dset_Position is not null then
1169 l_Dim_Set_Rec.Bsc_Dset_Position := p_Dim_Set_Rec.Bsc_Dset_Position;
1170 end if;
1171 if p_Dim_Set_Rec.Bsc_Dset_Total0 is not null then
1172 l_Dim_Set_Rec.Bsc_Dset_Total0 := p_Dim_Set_Rec.Bsc_Dset_Total0;
1173 end if;
1174 if p_Dim_Set_Rec.Bsc_Dset_Level_Display is not null then
1175 l_Dim_Set_Rec.Bsc_Dset_Level_Display := p_Dim_Set_Rec.Bsc_Dset_Level_Display;
1176 end if;
1177 if p_Dim_Set_Rec.Bsc_Dset_Default_Key_Value is not null then
1178 l_Dim_Set_Rec.Bsc_Dset_Default_Key_Value := p_Dim_Set_Rec.Bsc_Dset_Default_Key_Value;
1179 end if;
1180 if p_Dim_Set_Rec.Bsc_Dset_User_Level0 is not null then
1181 l_Dim_Set_Rec.Bsc_Dset_User_Level0 := p_Dim_Set_Rec.Bsc_Dset_User_Level0;
1182 end if;
1183 if p_Dim_Set_Rec.Bsc_Dset_User_Level1 is not null then
1184 l_Dim_Set_Rec.Bsc_Dset_User_Level1 := p_Dim_Set_Rec.Bsc_Dset_User_Level1;
1185 end if;
1186 if p_Dim_Set_Rec.Bsc_Dset_User_Level1_Default is not null then
1187 l_Dim_Set_Rec.Bsc_Dset_User_Level1_Default := p_Dim_Set_Rec.Bsc_Dset_User_Level1_Default;
1188 end if;
1189 if p_Dim_Set_Rec.Bsc_Dset_User_Level2 is not null then
1190 l_Dim_Set_Rec.Bsc_Dset_User_Level2 := p_Dim_Set_Rec.Bsc_Dset_User_Level2;
1191 end if;
1192 if p_Dim_Set_Rec.Bsc_Dset_User_Level2_Default is not null then
1193 l_Dim_Set_Rec.Bsc_Dset_User_Level2_Default := p_Dim_Set_Rec.Bsc_Dset_User_Level2_Default;
1194 end if;
1195
1196 update BSC_KPI_DIM_LEVEL_PROPERTIES
1197 set position = l_Dim_Set_Rec.Bsc_Dset_Position
1198 ,total0 = l_Dim_Set_Rec.Bsc_Dset_Total0
1199 ,level_display = l_Dim_Set_Rec.Bsc_Dset_Level_Display
1200 ,default_key_value = l_Dim_Set_Rec.Bsc_Dset_Default_Key_Value
1201 ,user_level0 = l_Dim_Set_Rec.Bsc_Dset_User_Level0
1202 ,user_level1 = l_Dim_Set_Rec.Bsc_Dset_User_Level1
1203 ,user_level1_default = l_Dim_Set_Rec.Bsc_Dset_User_Level1_Default
1204 ,user_level2 =l_Dim_Set_Rec.Bsc_Dset_User_Level2
1205 ,user_level2_default = l_Dim_Set_Rec.Bsc_Dset_User_Level2_Default
1206 where indicator = l_Dim_Set_Rec.Bsc_Kpi_Id
1207 and dim_set_id = l_Dim_Set_Rec.Bsc_Dim_Set_Id
1208 and dim_level_id = l_Dim_Set_Rec.Bsc_Level_Id;
1209
1210 if (p_commit = FND_API.G_TRUE) then
1211 commit;
1212 end if;
1213
1214 EXCEPTION
1215 WHEN FND_API.G_EXC_ERROR THEN
1216 ROLLBACK TO UpdateBSCDimLevPropPvt;
1217 FND_MSG_PUB.Count_And_Get
1218 ( p_encoded => FND_API.G_FALSE
1219 , p_count => x_msg_count
1220 , p_data => x_msg_data
1221 );
1222 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1223 x_return_status := FND_API.G_RET_STS_ERROR;
1224 RAISE;
1225 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1226 ROLLBACK TO UpdateBSCDimLevPropPvt;
1227 FND_MSG_PUB.Count_And_Get
1228 ( p_encoded => FND_API.G_FALSE
1229 , p_count => x_msg_count
1230 , p_data => x_msg_data
1231 );
1232 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1233 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1234 RAISE;
1235 WHEN NO_DATA_FOUND THEN
1236 ROLLBACK TO UpdateBSCDimLevPropPvt;
1237 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1238 IF (x_msg_data IS NOT NULL) THEN
1239 x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Update_Dim_Level_Properties ';
1240 ELSE
1241 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Update_Dim_Level_Properties ';
1242 END IF;
1243 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1244 RAISE;
1245 WHEN OTHERS THEN
1246 ROLLBACK TO UpdateBSCDimLevPropPvt;
1247 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1248 IF (x_msg_data IS NOT NULL) THEN
1249 x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Update_Dim_Level_Properties ';
1250 ELSE
1251 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Update_Dim_Level_Properties ';
1252 END IF;
1253 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1254 RAISE;
1255 end Update_Dim_Level_Properties;
1256
1257 /************************************************************************************
1258 ************************************************************************************/
1259
1260 procedure Delete_Dim_Level_Properties(
1261 p_commit IN varchar2 := FND_API.G_FALSE
1262 ,p_Dim_Set_Rec IN BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
1263 ,x_return_status OUT NOCOPY varchar2
1264 ,x_msg_count OUT NOCOPY number
1265 ,x_msg_data OUT NOCOPY varchar2
1266 ) is
1267
1268 l_count number;
1269
1270 begin
1271
1272 SAVEPOINT DeleteBSCDimLevPropPvt;
1273 FND_MSG_PUB.Initialize;
1274 x_return_status := FND_API.G_RET_STS_SUCCESS;
1275 -- Check that valid KPI id was entered.
1276 if p_Dim_Set_Rec.Bsc_Kpi_Id is not null then
1277 l_count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_KPIS_B'
1278 ,'indicator'
1279 ,p_Dim_Set_Rec.Bsc_Kpi_Id);
1280 if l_count = 0 then
1281 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_KPI_ID');
1282 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Dim_Set_Rec.Bsc_Kpi_Id);
1283 FND_MSG_PUB.ADD;
1284 RAISE FND_API.G_EXC_ERROR;
1285 else
1286 select count(dim_set_id)
1287 into l_count
1288 from BSC_KPI_DIM_SETS_TL
1289 where indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
1290 and dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id;
1291 if l_count = 0 then
1292 FND_MESSAGE.SET_NAME('BSC','BSC_DSDL_COMBO_EXISTS');
1293 FND_MESSAGE.SET_TOKEN('BSC_COMBO', p_Dim_Set_Rec.Bsc_Kpi_Id);
1294 FND_MSG_PUB.ADD;
1295 RAISE FND_API.G_EXC_ERROR;
1296 end if;
1297 end if;
1298 else
1299 FND_MESSAGE.SET_NAME('BSC','BSC_NO_KPI_ID_ENTERED');
1300 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Dim_Set_Rec.Bsc_Kpi_Id);
1301 FND_MSG_PUB.ADD;
1302 RAISE FND_API.G_EXC_ERROR;
1303 end if;
1304
1305
1306 delete from BSC_KPI_DIM_LEVEL_PROPERTIES
1307 where indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
1308 and dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id
1309 and dim_level_id = p_Dim_Set_Rec.Bsc_Level_Id;
1310
1311 if (p_commit = FND_API.G_TRUE) then
1312 commit;
1313 end if;
1314
1315 EXCEPTION
1316 WHEN FND_API.G_EXC_ERROR THEN
1317 ROLLBACK TO DeleteBSCDimLevPropPvt;
1318 FND_MSG_PUB.Count_And_Get
1319 ( p_encoded => FND_API.G_FALSE
1320 , p_count => x_msg_count
1321 , p_data => x_msg_data
1322 );
1323 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1324 x_return_status := FND_API.G_RET_STS_ERROR;
1325 RAISE;
1326 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1327 ROLLBACK TO DeleteBSCDimLevPropPvt;
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 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1334 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1335 RAISE;
1336 WHEN NO_DATA_FOUND THEN
1337 ROLLBACK TO DeleteBSCDimLevPropPvt;
1338 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1339 IF (x_msg_data IS NOT NULL) THEN
1340 x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Delete_Dim_Level_Properties ';
1341 ELSE
1342 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Delete_Dim_Level_Properties ';
1343 END IF;
1344 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1345 RAISE;
1346 WHEN OTHERS THEN
1347 ROLLBACK TO DeleteBSCDimLevPropPvt;
1348 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1349 IF (x_msg_data IS NOT NULL) THEN
1350 x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Delete_Dim_Level_Properties ';
1351 ELSE
1352 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Delete_Dim_Level_Properties ';
1353 END IF;
1354 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1355 RAISE;
1356 end Delete_Dim_Level_Properties;
1357
1358 /************************************************************************************
1359 ************************************************************************************/
1360
1361 --: This procedure assigns the dimension ids to the dimension set.
1362 --: This procedure is part of the Dimension Set API.
1363
1364 PROCEDURE Create_Dim_Levels
1365 ( p_commit IN VARCHAR2 := FND_API.G_FALSE
1366 , p_Dim_Set_Rec IN BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
1367 , x_return_status OUT NOCOPY VARCHAR2
1368 , x_msg_count OUT NOCOPY NUMBER
1369 , x_msg_data OUT NOCOPY VARCHAR2
1370 ) IS
1371
1372 l_count NUMBER;
1373 kpi_dim_set_exists EXCEPTION;
1374 l_Dim_Set_Rec BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type;
1375
1376 CURSOR c_dim_sys_levels_tl IS
1377 SELECT Name
1378 , Help
1379 , Total_Disp_Name
1380 , Comp_Disp_Name
1381 , Language
1382 , Source_Lang
1383 FROM BSC_SYS_DIM_LEVELS_TL
1384 WHERE DIM_LEVEL_ID = p_Dim_Set_Rec.Bsc_Level_Id;
1385 BEGIN
1386 SAVEPOINT DeleteBSCDimLevsPVT;
1387 FND_MSG_PUB.Initialize;
1388 x_return_status := FND_API.G_RET_STS_SUCCESS;
1389 l_Dim_Set_Rec := p_Dim_Set_Rec;
1390 -- Determine if the dimension already belongs to this dimension set.
1391 IF (p_Dim_Set_Rec.Bsc_Level_Name IS NOT NULL) THEN
1392 SELECT count(*)
1393 INTO l_count
1394 FROM BSC_KPI_DIM_LEVELS_B
1395 WHERE indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
1396 AND dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id
1397 AND upper(level_table_name) = upper(p_Dim_Set_Rec.Bsc_Level_Name)
1398 AND dim_level_index = p_Dim_Set_Rec.Bsc_Dset_Dim_Level_Index - 1;
1399
1400 -- If the dimension does not belong to the dimension set assign it, else
1401 -- raise an error.
1402 IF l_count = 0 THEN
1403 -- Insert pertaining values into table bsc_kpi_dim_levels_b.
1404 -- Reminder: some values are hard coded, need to get source.
1405 INSERT INTO BSC_KPI_DIM_LEVELS_B( indicator
1406 ,dim_set_id
1407 ,dim_level_index
1408 ,level_table_name
1409 ,level_view_name
1410 ,filter_column
1411 ,filter_value
1412 ,default_value
1413 ,default_type
1414 ,value_order_by
1415 ,comp_order_by
1416 ,level_pk_col
1417 ,parent_level_index
1418 ,parent_level_rel
1419 ,table_relation
1420 ,parent_level_index2
1421 ,parent_level_rel2
1422 ,status
1423 ,parent_in_total
1424 ,position
1425 ,total0
1426 ,level_display
1427 ,no_items
1428 ,default_key_value
1429 ,user_level0
1430 ,user_level1
1431 ,user_level1_default
1432 ,user_level2
1433 ,user_level2_default
1434 ,target_level)
1435 VALUES( p_Dim_Set_Rec.Bsc_Kpi_Id
1436 ,p_Dim_Set_Rec.Bsc_Dim_Set_Id
1437 ,p_Dim_Set_Rec.Bsc_Dset_Dim_Level_Index - 1
1438 ,p_Dim_Set_Rec.Bsc_Level_Name
1439 ,p_Dim_Set_Rec.Bsc_View_Name
1440 ,p_Dim_Set_Rec.Bsc_Dset_Filter_Column
1441 ,p_Dim_Set_Rec.Bsc_Dset_Filter_Value
1442 ,p_Dim_Set_Rec.Bsc_Dset_Default_Value
1443 ,p_Dim_Set_Rec.Bsc_Dset_Default_Type
1444 ,p_Dim_Set_Rec.Bsc_Dset_Value_Order
1445 ,p_Dim_Set_Rec.Bsc_Dset_Comp_Order
1446 ,p_Dim_Set_Rec.Bsc_Pk_Col
1447 ,p_Dim_Set_Rec.Bsc_Dset_Parent_Level_Index
1448 ,p_Dim_Set_Rec.Bsc_Dset_Parent_Level_Rel
1449 ,p_Dim_Set_Rec.Bsc_Dset_Table_Relation
1450 ,p_Dim_Set_Rec.Bsc_Dset_Parent_Level_Index2
1451 ,p_Dim_Set_Rec.Bsc_Dset_Parent_Level_Rel2
1452 ,p_Dim_Set_Rec.Bsc_Dset_Status
1453 ,p_Dim_Set_Rec.Bsc_Dset_Parent_In_Total
1454 ,p_Dim_Set_Rec.Bsc_Dset_Position
1455 ,p_Dim_Set_Rec.Bsc_Dset_Total0
1456 ,p_Dim_Set_Rec.Bsc_Dset_Level_Display
1457 ,p_Dim_Set_Rec.Bsc_Dset_No_Items
1458 ,p_Dim_Set_Rec.Bsc_Dset_Default_Key_Value
1459 ,p_Dim_Set_Rec.Bsc_Dset_User_Level0
1460 ,p_Dim_Set_Rec.Bsc_Dset_User_Level1
1461 ,p_Dim_Set_Rec.Bsc_Dset_User_Level1_Default
1462 ,p_Dim_Set_Rec.Bsc_Dset_User_Level2
1463 ,p_Dim_Set_Rec.Bsc_Dset_User_Level2_Default
1464 ,p_Dim_Set_Rec.Bsc_Dset_Target_Level);
1465 -- Insert pertaining values into table bsc_kpi_dim_levels_tl.
1466 IF (p_Dim_Set_Rec.Bsc_Level_Id IS NULL) THEN
1467 INSERT INTO BSC_KPI_DIM_LEVELS_TL
1468 ( Indicator
1469 , Dim_Set_Id
1470 , Dim_Level_Index
1471 , Language
1472 , Source_Lang
1473 , Name
1474 , Help
1475 , Total_Disp_Name
1476 , Comp_Disp_Name
1477 )
1478 SELECT p_Dim_Set_Rec.Bsc_Kpi_Id
1479 , p_Dim_Set_Rec.Bsc_Dim_Set_Id
1480 , p_Dim_Set_Rec.Bsc_Dset_Dim_Level_Index - 1
1481 , L.LANGUAGE_CODE
1482 , USERENV('LANG')
1483 , p_Dim_Set_Rec.Bsc_Dim_Level_Long_Name
1484 , p_Dim_Set_Rec.Bsc_Dim_Level_Help
1485 , p_Dim_Set_Rec.Bsc_Dim_Tot_Disp_Name
1486 , p_Dim_Set_Rec.Bsc_Dim_Comp_Disp_Name
1487 FROM FND_LANGUAGES L
1488 WHERE L.INSTALLED_FLAG IN ('I', 'B')
1489 AND NOT EXISTS
1490 ( SELECT NULL
1491 FROM BSC_KPI_DIM_LEVELS_TL T
1492 WHERE T.indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
1493 AND dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id
1494 AND dim_level_index = p_Dim_Set_Rec.Bsc_Dset_Dim_Level_Index - 1
1495 AND T.LANGUAGE = L.LANGUAGE_CODE
1496 );
1497 ELSE
1498 FOR cd IN c_dim_sys_levels_tl LOOP
1499 l_Dim_Set_Rec.Bsc_Dim_Level_Long_Name := cd.Name;
1500 l_Dim_Set_Rec.Bsc_Dim_Level_Help := cd.Help;
1501 l_Dim_Set_Rec.Bsc_Dim_Tot_Disp_Name := cd.Total_Disp_Name;
1502 l_Dim_Set_Rec.Bsc_Dim_Comp_Disp_Name := cd.Comp_Disp_Name;
1503 l_Dim_Set_Rec.Bsc_Language := cd.Language;
1504 l_Dim_Set_Rec.Bsc_Source_Language := cd.Source_Lang;
1505 INSERT INTO BSC_KPI_DIM_LEVELS_TL
1506 ( indicator
1507 , dim_set_id
1508 , dim_level_index
1509 , language
1510 , source_lang
1511 , name
1512 , help
1513 , total_disp_name
1514 , comp_disp_name
1515 ) VALUES
1516 ( p_Dim_Set_Rec.Bsc_Kpi_Id
1517 , p_Dim_Set_Rec.Bsc_Dim_Set_Id
1518 , (p_Dim_Set_Rec.Bsc_Dset_Dim_Level_Index - 1)
1519 , l_Dim_Set_Rec.Bsc_Language
1520 , l_Dim_Set_Rec.Bsc_Source_Language
1521 , l_Dim_Set_Rec.Bsc_Dim_Level_Long_Name
1522 , l_Dim_Set_Rec.Bsc_Dim_Level_Help
1523 , DECODE(p_Dim_Set_Rec.Bsc_Dim_Tot_Disp_Name, NULL, NULL, l_Dim_Set_Rec.Bsc_Dim_Tot_Disp_Name)
1524 , DECODE(p_Dim_Set_Rec.Bsc_Dim_Comp_Disp_Name, NULL, NULL, l_Dim_Set_Rec.Bsc_Dim_Comp_Disp_Name)
1525 );
1526 END LOOP;
1527 END IF;
1528 ELSE
1529 FND_MESSAGE.SET_NAME('BSC','BSC_DSDL_COMBO_EXISTS');
1530 FND_MESSAGE.SET_TOKEN('BSC_COMBO', p_Dim_Set_Rec.Bsc_Kpi_Id);
1531 FND_MSG_PUB.ADD;
1532 RAISE FND_API.G_EXC_ERROR;
1533 END IF;
1534 END IF;
1535 IF (p_commit = FND_API.G_TRUE) THEN
1536 COMMIT;
1537 END IF;
1538 EXCEPTION
1539 WHEN FND_API.G_EXC_ERROR THEN
1540 ROLLBACK TO DeleteBSCDimLevsPVT;
1541 FND_MSG_PUB.Count_And_Get
1542 ( p_encoded => FND_API.G_FALSE
1543 , p_count => x_msg_count
1544 , p_data => x_msg_data
1545 );
1546 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1547 x_return_status := FND_API.G_RET_STS_ERROR;
1548 RAISE;
1549 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1550 ROLLBACK TO DeleteBSCDimLevsPVT;
1551 FND_MSG_PUB.Count_And_Get
1552 ( p_encoded => FND_API.G_FALSE
1553 , p_count => x_msg_count
1554 , p_data => x_msg_data
1555 );
1556 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1557 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1558 RAISE;
1559 WHEN NO_DATA_FOUND THEN
1560 ROLLBACK TO DeleteBSCDimLevsPVT;
1561 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1562 IF (x_msg_data IS NOT NULL) THEN
1563 x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Create_Dim_Levels ';
1564 ELSE
1565 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Create_Dim_Levels ';
1566 END IF;
1567 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1568 RAISE;
1569 WHEN OTHERS THEN
1570 ROLLBACK TO DeleteBSCDimLevsPVT;
1571 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1572 IF (x_msg_data IS NOT NULL) THEN
1573 x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Create_Dim_Levels ';
1574 ELSE
1575 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Create_Dim_Levels ';
1576 END IF;
1577 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1578 RAISE;
1579 end Create_Dim_Levels;
1580
1581 /************************************************************************************
1582 ************************************************************************************/
1583
1584 procedure Retrieve_Dim_Levels(
1585 p_commit IN varchar2 := FND_API.G_FALSE
1586 ,p_Dim_Set_Rec IN BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
1587 ,x_Dim_Set_Rec IN OUT NOCOPY BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
1588 ,x_return_status OUT NOCOPY varchar2
1589 ,x_msg_count OUT NOCOPY number
1590 ,x_msg_data OUT NOCOPY varchar2
1591 ) is
1592
1593 begin
1594 FND_MSG_PUB.Initialize;
1595 x_return_status := FND_API.G_RET_STS_SUCCESS;
1596 g_db_object := 'Retrieve_Dim_Levels';
1597
1598 select distinct a.indicator
1599 , a.dim_set_id
1600 , a.dim_level_index
1601 , a.level_table_name
1602 , a.level_view_name
1603 , a.filter_column
1604 , a.filter_value
1605 , a.default_value
1606 , a.default_type
1607 , a.value_order_by
1608 , a.comp_order_by
1609 , a.level_pk_col
1610 , a.parent_level_index
1611 , a.parent_level_rel
1612 , a.table_relation
1613 , a.parent_level_index2
1614 , a.parent_level_rel2
1615 , a.status
1616 , a.parent_in_total
1617 , a.position
1618 , a.total0
1619 , a.level_display
1620 , a.no_items
1621 , a.default_key_value
1622 , a.user_level0
1623 , a.user_level1
1624 , a.user_level1_default
1625 , a.user_level2
1626 , a.user_level2_default
1627 , a.target_level
1628 , b.name
1629 , b.help
1630 , b.total_disp_name
1631 , b.comp_disp_name
1632 into x_Dim_Set_Rec.Bsc_Kpi_Id
1633 , x_Dim_Set_Rec.Bsc_Dim_Set_Id
1634 , x_Dim_Set_Rec.Bsc_Dset_Dim_Level_Index
1635 , x_Dim_Set_Rec.Bsc_Level_Name
1636 , x_Dim_Set_Rec.Bsc_View_Name
1637 , x_Dim_Set_Rec.Bsc_Dset_Filter_Column
1638 , x_Dim_Set_Rec.Bsc_Dset_Filter_Value
1639 , x_Dim_Set_Rec.Bsc_Dset_Default_Value
1640 , x_Dim_Set_Rec.Bsc_Dset_Default_Type
1641 , x_Dim_Set_Rec.Bsc_Dset_Value_Order
1642 , x_Dim_Set_Rec.Bsc_Dset_Comp_Order
1643 , x_Dim_Set_Rec.Bsc_Pk_Col
1644 , x_Dim_Set_Rec.Bsc_Dset_Parent_Level_Index
1645 , x_Dim_Set_Rec.Bsc_Dset_Parent_Level_Rel
1646 , x_Dim_Set_Rec.Bsc_Dset_Table_Relation
1647 , x_Dim_Set_Rec.Bsc_Dset_Parent_Level_Index2
1648 , x_Dim_Set_Rec.Bsc_Dset_Parent_Level_Rel2
1649 , x_Dim_Set_Rec.Bsc_Dset_Status
1650 , x_Dim_Set_Rec.Bsc_Dset_Parent_In_Total
1651 , x_Dim_Set_Rec.Bsc_Dset_Position
1652 , x_Dim_Set_Rec.Bsc_Dset_Total0
1653 , x_Dim_Set_Rec.Bsc_Dset_Level_Display
1654 , x_Dim_Set_Rec.Bsc_Dset_No_Items
1655 , x_Dim_Set_Rec.Bsc_Dset_Default_Key_Value
1656 , x_Dim_Set_Rec.Bsc_Dset_User_Level0
1657 , x_Dim_Set_Rec.Bsc_Dset_User_Level1
1658 , x_Dim_Set_Rec.Bsc_Dset_User_Level1_Default
1659 , x_Dim_Set_Rec.Bsc_Dset_User_Level2
1660 , x_Dim_Set_Rec.Bsc_Dset_User_Level2_Default
1661 , x_Dim_Set_Rec.Bsc_Dset_Target_Level
1662 , x_Dim_Set_Rec.Bsc_Dim_Level_Long_Name
1663 , x_Dim_Set_Rec.Bsc_Dim_Level_Help
1664 , x_Dim_Set_Rec.Bsc_Dim_Tot_Disp_Name
1665 , x_Dim_Set_Rec.Bsc_Dim_Comp_Disp_Name
1666 from BSC_KPI_DIM_LEVELS_B a
1667 , BSC_KPI_DIM_LEVELS_TL b
1668 where a.indicator = b.indicator
1669 and a.dim_set_id = b.dim_set_id
1670 and a.dim_level_index = b.dim_level_index
1671 and a.indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
1672 and a.dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id
1673 and a.level_table_name = p_Dim_Set_Rec.Bsc_Level_Name
1674 and b.language = USERENV('LANG');
1675 if (p_commit = FND_API.G_TRUE) then
1676 commit;
1677 end if;
1678
1679 EXCEPTION
1680 WHEN FND_API.G_EXC_ERROR THEN
1681 FND_MSG_PUB.Count_And_Get
1682 ( p_encoded => FND_API.G_FALSE
1683 , p_count => x_msg_count
1684 , p_data => x_msg_data
1685 );
1686 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1687 x_return_status := FND_API.G_RET_STS_ERROR;
1688 RAISE;
1689 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1690 FND_MSG_PUB.Count_And_Get
1691 ( p_encoded => FND_API.G_FALSE
1692 , p_count => x_msg_count
1693 , p_data => x_msg_data
1694 );
1695 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1696 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1697 RAISE;
1698 WHEN NO_DATA_FOUND THEN
1699 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1700 IF (x_msg_data IS NOT NULL) THEN
1701 x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Retrieve_Dim_Levels ';
1702 ELSE
1703 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Retrieve_Dim_Levels ';
1704 END IF;
1705 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1706 RAISE;
1707 WHEN OTHERS THEN
1708 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1709 IF (x_msg_data IS NOT NULL) THEN
1710 x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Retrieve_Dim_Levels ';
1711 ELSE
1712 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Retrieve_Dim_Levels ';
1713 END IF;
1714 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1715 RAISE;
1716 end Retrieve_Dim_Levels;
1717
1718 /************************************************************************************
1719 ************************************************************************************/
1720
1721 procedure Update_Dim_Levels(
1722 p_commit IN varchar2 := FND_API.G_FALSE
1723 ,p_Dim_Set_Rec IN BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
1724 ,x_return_status OUT NOCOPY varchar2
1725 ,x_msg_count OUT NOCOPY number
1726 ,x_msg_data OUT NOCOPY varchar2
1727 ) is
1728
1729 l_Dim_Set_Rec BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type;
1730
1731 l_count number;
1732
1733 begin
1734
1735 SAVEPOINT UpdateBSCDimLevsPVT;
1736 FND_MSG_PUB.Initialize;
1737 x_return_status := FND_API.G_RET_STS_SUCCESS;
1738 -- Check that valid KPI id was entered.
1739 if p_Dim_Set_Rec.Bsc_Kpi_Id is not null then
1740 l_count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_KPIS_B'
1741 ,'indicator'
1742 ,p_Dim_Set_Rec.Bsc_Kpi_Id);
1743 if l_count = 0 then
1744 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_KPI_ID');
1745 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Dim_Set_Rec.Bsc_Kpi_Id);
1746 FND_MSG_PUB.ADD;
1747 RAISE FND_API.G_EXC_ERROR;
1748 else
1749 select count(dim_set_id)
1750 into l_count
1751 from BSC_KPI_DIM_SETS_TL
1752 where indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
1753 and dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id;
1754 if l_count = 0 then
1755 FND_MESSAGE.SET_NAME('BSC','BSC_DSDL_COMBO_EXISTS');
1756 FND_MESSAGE.SET_TOKEN('BSC_COMBO', p_Dim_Set_Rec.Bsc_Kpi_Id);
1757 FND_MSG_PUB.ADD;
1758 RAISE FND_API.G_EXC_ERROR;
1759 end if;
1760 end if;
1761 else
1762 FND_MESSAGE.SET_NAME('BSC','BSC_NO_KPI_ID_ENTERED');
1763 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Dim_Set_Rec.Bsc_Kpi_Id);
1764 FND_MSG_PUB.ADD;
1765 RAISE FND_API.G_EXC_ERROR;
1766 end if;
1767
1768 -- Check that valid Level name was entered.
1769 if p_Dim_Set_Rec.Bsc_Level_Name is not null then
1770 select count(distinct dim_level_id)
1771 into l_count
1772 from BSC_SYS_DIM_LEVELS_B
1773 where upper(level_table_name) = upper(p_Dim_Set_Rec.Bsc_Level_Name);
1774 if l_count = 0 then
1775 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_LEVEL_NAME');
1776 FND_MESSAGE.SET_TOKEN('BSC_LEVEL_NAME', p_Dim_Set_Rec.Bsc_Level_Name);
1777 FND_MSG_PUB.ADD;
1778 RAISE FND_API.G_EXC_ERROR;
1779 end if;
1780 else
1781 FND_MESSAGE.SET_NAME('BSC','BSC_NO_LEVEL_NAME_ENTERED');
1782 FND_MESSAGE.SET_TOKEN('BSC_LEVEL_NAME', p_Dim_Set_Rec.Bsc_Level_Name);
1783 FND_MSG_PUB.ADD;
1784 RAISE FND_API.G_EXC_ERROR;
1785 end if;
1786
1787 -- Not all values will be passed. We need to make sure values not passed are not
1788 -- changed by procedure, therefore we get what is there before we do any updates.
1789 Retrieve_Dim_Levels( p_commit
1790 ,p_Dim_Set_Rec
1791 ,l_Dim_Set_Rec
1792 ,x_return_status
1793 ,x_msg_count
1794 ,x_msg_data);
1795
1796 -- update LOCAL language ,source language, kpi id, dim set id and level Id values with PASSED values.
1797 l_Dim_Set_Rec.Bsc_Language := p_Dim_Set_Rec.Bsc_Language;
1798 l_Dim_Set_Rec.Bsc_Source_Language := p_Dim_Set_Rec.Bsc_Source_Language;
1799 l_Dim_Set_Rec.Bsc_Kpi_Id := p_Dim_Set_Rec.Bsc_Kpi_Id;
1800 l_Dim_Set_Rec.Bsc_Dim_Set_Id := p_Dim_Set_Rec.Bsc_Dim_Set_Id;
1801 l_Dim_Set_Rec.Bsc_Level_Name := p_Dim_Set_Rec.Bsc_Level_Name;
1802
1803 -- Copy PASSED Record values into LOCAL Record values for the PASSED Record values
1804 -- which are NOT NULL.
1805 if p_Dim_Set_Rec.Bsc_Dset_Dim_Level_Index is not null then
1806 l_Dim_Set_Rec.Bsc_Dset_Dim_Level_Index := p_Dim_Set_Rec.Bsc_Dset_Dim_Level_Index;
1807 end if;
1808 if p_Dim_Set_Rec.Bsc_View_Name is not null then
1809 l_Dim_Set_Rec.Bsc_View_Name := p_Dim_Set_Rec.Bsc_View_Name;
1810 end if;
1811 if p_Dim_Set_Rec.Bsc_Level_Name is not null then
1812 l_Dim_Set_Rec.Bsc_Level_Name := p_Dim_Set_Rec.Bsc_Level_Name;
1813 end if;
1814 if p_Dim_Set_Rec.Bsc_Dset_Filter_Column is not null then
1815 l_Dim_Set_Rec.Bsc_Dset_Filter_Column := p_Dim_Set_Rec.Bsc_Dset_Filter_Column ;
1816 end if;
1817 if p_Dim_Set_Rec.Bsc_Dset_Filter_Value is not null then
1818 l_Dim_Set_Rec.Bsc_Dset_Filter_Value := p_Dim_Set_Rec.Bsc_Dset_Filter_Value ;
1819 end if;
1820 if p_Dim_Set_Rec.Bsc_Dset_Default_Value is not null then
1821 l_Dim_Set_Rec.Bsc_Dset_Default_Value := p_Dim_Set_Rec.Bsc_Dset_Default_Value;
1822 end if;
1823 if p_Dim_Set_Rec.Bsc_Dset_Default_Type is not null then
1824 l_Dim_Set_Rec.Bsc_Dset_Default_Type := p_Dim_Set_Rec.Bsc_Dset_Default_Type;
1825 end if;
1826 if p_Dim_Set_Rec.Bsc_Dset_Value_Order is not null then
1827 l_Dim_Set_Rec.Bsc_Dset_Value_Order := p_Dim_Set_Rec.Bsc_Dset_Value_Order;
1828 end if;
1829 if p_Dim_Set_Rec.Bsc_Dset_Comp_Order is not null then
1830 l_Dim_Set_Rec.Bsc_Dset_Comp_Order := p_Dim_Set_Rec.Bsc_Dset_Comp_Order;
1831 end if;
1832 if p_Dim_Set_Rec.Bsc_Pk_Col is not null then
1833 l_Dim_Set_Rec.Bsc_Pk_Col := p_Dim_Set_Rec.Bsc_Pk_Col;
1834 end if;
1835 if p_Dim_Set_Rec.Bsc_Dset_Parent_Level_Index is not null then
1836 l_Dim_Set_Rec.Bsc_Dset_Parent_Level_Index := p_Dim_Set_Rec.Bsc_Dset_Parent_Level_Index;
1837 end if;
1838 if p_Dim_Set_Rec.Bsc_Dset_Parent_Level_Rel is not null then
1839 l_Dim_Set_Rec.Bsc_Dset_Parent_Level_Rel := p_Dim_Set_Rec.Bsc_Dset_Parent_Level_Rel;
1840 end if;
1841 if p_Dim_Set_Rec.Bsc_Dset_Table_Relation is not null then
1842 l_Dim_Set_Rec.Bsc_Dset_Table_Relation := p_Dim_Set_Rec.Bsc_Dset_Table_Relation;
1843 end if;
1844 if p_Dim_Set_Rec.Bsc_Dset_Parent_Level_Index2 is not null then
1845 l_Dim_Set_Rec.Bsc_Dset_Parent_Level_Index2 := p_Dim_Set_Rec.Bsc_Dset_Parent_Level_Index2;
1846 end if;
1847 if p_Dim_Set_Rec.Bsc_Dset_Parent_Level_Rel2 is not null then
1848 l_Dim_Set_Rec.Bsc_Dset_Parent_Level_Rel2 := p_Dim_Set_Rec.Bsc_Dset_Parent_Level_Rel2;
1849 end if;
1850 if p_Dim_Set_Rec.Bsc_Dset_Status is not null then
1851 l_Dim_Set_Rec.Bsc_Dset_Status := p_Dim_Set_Rec.Bsc_Dset_Status;
1852 end if;
1853 if p_Dim_Set_Rec.Bsc_Dset_Parent_In_Total is not null then
1854 l_Dim_Set_Rec.Bsc_Dset_Parent_In_Total := p_Dim_Set_Rec.Bsc_Dset_Parent_In_Total;
1855 end if;
1856 if p_Dim_Set_Rec.Bsc_Dset_Position is not null then
1857 l_Dim_Set_Rec.Bsc_Dset_Position := p_Dim_Set_Rec.Bsc_Dset_Position;
1858 end if;
1859 if p_Dim_Set_Rec.Bsc_Dset_Total0 is not null then
1860 l_Dim_Set_Rec.Bsc_Dset_Total0 := p_Dim_Set_Rec.Bsc_Dset_Total0;
1861 end if;
1862 if p_Dim_Set_Rec.Bsc_Dset_Level_Display is not null then
1863 l_Dim_Set_Rec.Bsc_Dset_Level_Display := p_Dim_Set_Rec.Bsc_Dset_Level_Display;
1864 end if;
1865 if p_Dim_Set_Rec.Bsc_Dset_No_Items is not null then
1866 l_Dim_Set_Rec.Bsc_Dset_No_Items := p_Dim_Set_Rec.Bsc_Dset_No_Items;
1867 end if;
1868 if p_Dim_Set_Rec.Bsc_Dset_Default_Key_Value is not null then
1869 l_Dim_Set_Rec.Bsc_Dset_Default_Key_Value := p_Dim_Set_Rec.Bsc_Dset_Default_Key_Value;
1870 end if;
1871 if p_Dim_Set_Rec.Bsc_Dset_User_Level0 is not null then
1872 l_Dim_Set_Rec.Bsc_Dset_User_Level0 := p_Dim_Set_Rec.Bsc_Dset_User_Level0;
1873 end if;
1874 if p_Dim_Set_Rec.Bsc_Dset_User_Level1 is not null then
1875 l_Dim_Set_Rec.Bsc_Dset_User_Level1 := p_Dim_Set_Rec.Bsc_Dset_User_Level1;
1876 end if;
1877 if p_Dim_Set_Rec.Bsc_Dset_User_Level1_Default is not null then
1878 l_Dim_Set_Rec.Bsc_Dset_User_Level1_Default := p_Dim_Set_Rec.Bsc_Dset_User_Level1_Default;
1879 end if;
1880 if p_Dim_Set_Rec.Bsc_Dset_User_Level2 is not null then
1881 l_Dim_Set_Rec.Bsc_Dset_User_Level2 := p_Dim_Set_Rec.Bsc_Dset_User_Level2;
1882 end if;
1883 if p_Dim_Set_Rec.Bsc_Dset_User_Level2_Default is not null then
1884 l_Dim_Set_Rec.Bsc_Dset_User_Level2_Default := p_Dim_Set_Rec.Bsc_Dset_User_Level2_Default;
1885 end if;
1886 if p_Dim_Set_Rec.Bsc_Dim_Level_Long_Name is not null then
1887 l_Dim_Set_Rec.Bsc_Dim_Level_Long_Name := p_Dim_Set_Rec.Bsc_Dim_Level_Long_Name;
1888 end if;
1889 if p_Dim_Set_Rec.Bsc_Dim_Level_Help is not null then
1890 l_Dim_Set_Rec.Bsc_Dim_Level_Help := p_Dim_Set_Rec.Bsc_Dim_Level_Help;
1891 end if;
1892 if p_Dim_Set_Rec.Bsc_Dim_Tot_Disp_Name is not null then
1893 l_Dim_Set_Rec.Bsc_Dim_Tot_Disp_Name := p_Dim_Set_Rec.Bsc_Dim_Tot_Disp_Name;
1894 end if;
1895 if p_Dim_Set_Rec.Bsc_Dim_Comp_Disp_Name is not null then
1896 l_Dim_Set_Rec.Bsc_Dim_Comp_Disp_Name := p_Dim_Set_Rec.Bsc_Dim_Comp_Disp_Name;
1897 end if;
1898 if p_Dim_Set_Rec.Bsc_Dset_Target_Level is not null then
1899 l_Dim_Set_Rec.Bsc_Dset_Target_Level := p_Dim_Set_Rec.Bsc_Dset_Target_Level;
1900 end if;
1901
1902 update BSC_KPI_DIM_LEVELS_B a
1903 set dim_level_index = l_Dim_Set_Rec.Bsc_Dset_Dim_Level_Index
1904 , level_view_name = l_Dim_Set_Rec.Bsc_View_Name
1905 , filter_column = l_Dim_Set_Rec.Bsc_Dset_Filter_Column
1906 , filter_value = l_Dim_Set_Rec.Bsc_Dset_Filter_Value
1907 , default_value = l_Dim_Set_Rec.Bsc_Dset_Default_Value
1908 , default_type = l_Dim_Set_Rec.Bsc_Dset_Default_Type
1909 , value_order_by = l_Dim_Set_Rec.Bsc_Dset_Value_Order
1910 , comp_order_by = l_Dim_Set_Rec.Bsc_Dset_Comp_Order
1911 , level_pk_col = l_Dim_Set_Rec.Bsc_Pk_Col
1912 , parent_level_index = l_Dim_Set_Rec.Bsc_Dset_Parent_Level_Index
1913 , parent_level_rel = l_Dim_Set_Rec.Bsc_Dset_Parent_Level_Rel
1914 , table_relation = l_Dim_Set_Rec.Bsc_Dset_Table_Relation
1915 , parent_level_index2 = l_Dim_Set_Rec.Bsc_Dset_Parent_Level_Index2
1916 , parent_level_rel2 = l_Dim_Set_Rec.Bsc_Dset_Parent_Level_Rel2
1917 , status = l_Dim_Set_Rec.Bsc_Dset_Status
1918 , parent_in_total = l_Dim_Set_Rec.Bsc_Dset_Parent_In_Total
1919 , position = l_Dim_Set_Rec.Bsc_Dset_Position
1920 , total0 = l_Dim_Set_Rec.Bsc_Dset_Total0
1921 , level_display = l_Dim_Set_Rec.Bsc_Dset_Level_Display
1922 , no_items = l_Dim_Set_Rec.Bsc_Dset_No_Items
1923 , default_key_value = l_Dim_Set_Rec.Bsc_Dset_Default_Key_Value
1924 , user_level0 = l_Dim_Set_Rec.Bsc_Dset_User_Level0
1925 , user_level1 = l_Dim_Set_Rec.Bsc_Dset_User_Level1
1926 , user_level1_default = l_Dim_Set_Rec.Bsc_Dset_User_Level1_Default
1927 , user_level2 = l_Dim_Set_Rec.Bsc_Dset_User_Level2
1928 , user_level2_default = l_Dim_Set_Rec.Bsc_Dset_User_Level2_Default
1929 , target_level = l_Dim_Set_Rec.Bsc_Dset_Target_Level
1930 where indicator = l_Dim_Set_Rec.Bsc_Kpi_Id
1931 and dim_set_id = l_Dim_Set_Rec.Bsc_Dim_Set_Id
1932 and level_table_name = l_Dim_Set_Rec.Bsc_Level_Name;
1933
1934 update BSC_KPI_DIM_LEVELS_TL
1935 set name = l_Dim_Set_Rec.Bsc_Dim_Level_Long_Name
1936 , help = l_Dim_Set_Rec.Bsc_Dim_Level_Help
1937 , total_disp_name = l_Dim_Set_Rec.Bsc_Dim_Tot_Disp_Name
1938 , comp_disp_name = l_Dim_Set_Rec.Bsc_Dim_Comp_Disp_Name
1939 , source_lang = USERENV('LANG')
1940 where indicator = l_Dim_Set_Rec.Bsc_Kpi_Id
1941 and dim_set_id = l_Dim_Set_Rec.Bsc_Dim_Set_Id
1942 and dim_level_index = l_Dim_Set_Rec.Bsc_Dset_Dim_Level_Index
1943 and USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
1944
1945 if (p_commit = FND_API.G_TRUE) then
1946 commit;
1947 end if;
1948
1949 EXCEPTION
1950 WHEN FND_API.G_EXC_ERROR THEN
1951 ROLLBACK TO UpdateBSCDimLevsPVT;
1952 FND_MSG_PUB.Count_And_Get
1953 ( p_encoded => FND_API.G_FALSE
1954 , p_count => x_msg_count
1955 , p_data => x_msg_data
1956 );
1957 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1958 x_return_status := FND_API.G_RET_STS_ERROR;
1959 RAISE;
1960 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1961 ROLLBACK TO UpdateBSCDimLevsPVT;
1962 FND_MSG_PUB.Count_And_Get
1963 ( p_encoded => FND_API.G_FALSE
1964 , p_count => x_msg_count
1965 , p_data => x_msg_data
1966 );
1967 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1968 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1969 RAISE;
1970 WHEN NO_DATA_FOUND THEN
1971 ROLLBACK TO UpdateBSCDimLevsPVT;
1972 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1973 IF (x_msg_data IS NOT NULL) THEN
1974 x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Update_Dim_Levels ';
1975 ELSE
1976 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Update_Dim_Levels ';
1977 END IF;
1978 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1979 RAISE;
1980 WHEN OTHERS THEN
1981 ROLLBACK TO UpdateBSCDimLevsPVT;
1982 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1983 IF (x_msg_data IS NOT NULL) THEN
1984 x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Update_Dim_Levels ';
1985 ELSE
1986 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Update_Dim_Levels ';
1987 END IF;
1988 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1989 RAISE;
1990 end Update_Dim_Levels;
1991
1992 /************************************************************************************
1993 ************************************************************************************/
1994
1995 procedure Delete_Dim_Levels(
1996 p_commit IN varchar2 := FND_API.G_FALSE
1997 ,p_Dim_Set_Rec IN BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
1998 ,x_return_status OUT NOCOPY varchar2
1999 ,x_msg_count OUT NOCOPY number
2000 ,x_msg_data OUT NOCOPY varchar2
2001 ) is
2002
2003 l_count number;
2004 --PAJOHRI ADDED 02-Jun-2003
2005 l_dim_index NUMBER;
2006
2007 CURSOR c_dim_index IS
2008 SELECT DIM_LEVEL_INDEX FROM BSC_KPI_DIM_LEVELS_B
2009 WHERE DIM_SET_ID = p_Dim_Set_Rec.Bsc_Dim_Set_Id
2010 AND INDICATOR = p_Dim_Set_Rec.Bsc_Kpi_Id
2011 AND (LEVEL_TABLE_NAME = (SELECT LEVEL_TABLE_NAME FROM BSC_SYS_DIM_LEVELS_B
2012 WHERE DIM_LEVEL_ID = p_Dim_Set_Rec.Bsc_Level_Id)
2013 OR LEVEL_TABLE_NAME = 'XXX')
2014 ORDER BY DIM_LEVEL_INDEX DESC;
2015 begin
2016 SAVEPOINT DeleteBSCDimLevsPVT;
2017 FND_MSG_PUB.Initialize;
2018 x_return_status := FND_API.G_RET_STS_SUCCESS;
2019 -- Check that valid KPI id was entered.
2020 if p_Dim_Set_Rec.Bsc_Kpi_Id is not null then
2021 l_count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_KPIS_B'
2022 ,'indicator'
2023 ,p_Dim_Set_Rec.Bsc_Kpi_Id);
2024 if l_count = 0 then
2025 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_KPI_ID');
2026 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Dim_Set_Rec.Bsc_Kpi_Id);
2027 FND_MSG_PUB.ADD;
2028 RAISE FND_API.G_EXC_ERROR;
2029 end if;
2030 else
2031 FND_MESSAGE.SET_NAME('BSC','BSC_NO_KPI_ID_ENTERED');
2032 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Dim_Set_Rec.Bsc_Kpi_Id);
2033 FND_MSG_PUB.ADD;
2034 RAISE FND_API.G_EXC_ERROR;
2035 end if;
2036
2037 FOR cd IN c_dim_index LOOP
2038 l_dim_index := cd.Dim_Level_Index;
2039
2040 DELETE
2041 FROM bsc_kpi_dim_levels_b
2042 WHERE indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
2043 AND dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id
2044 AND dim_level_index = l_dim_index;
2045
2046 DELETE
2047 FROM bsc_kpi_dim_levels_tl
2048 WHERE indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
2049 AND dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id
2050 AND dim_level_index = l_dim_index;
2051
2052 DELETE
2053 FROM bsc_kpi_dim_levels_user
2054 WHERE indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
2055 AND dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id
2056 AND dim_level_index = l_dim_index;
2057
2058 END LOOP;
2059
2060 if (p_commit = FND_API.G_TRUE) then
2061 commit;
2062 end if;
2063
2064 EXCEPTION
2065 WHEN FND_API.G_EXC_ERROR THEN
2066 ROLLBACK TO DeleteBSCDimLevsPVT;
2067 FND_MSG_PUB.Count_And_Get
2068 ( p_encoded => FND_API.G_FALSE
2069 , p_count => x_msg_count
2070 , p_data => x_msg_data
2071 );
2072 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2073 x_return_status := FND_API.G_RET_STS_ERROR;
2074 RAISE;
2075 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2076 ROLLBACK TO DeleteBSCDimLevsPVT;
2077 FND_MSG_PUB.Count_And_Get
2078 ( p_encoded => FND_API.G_FALSE
2079 , p_count => x_msg_count
2080 , p_data => x_msg_data
2081 );
2082 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2083 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2084 RAISE;
2085 WHEN NO_DATA_FOUND THEN
2086 ROLLBACK TO DeleteBSCDimLevsPVT;
2087 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2088 IF (x_msg_data IS NOT NULL) THEN
2089 x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Delete_Dim_Levels ';
2090 ELSE
2091 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Delete_Dim_Levels ';
2092 END IF;
2093 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
2094 RAISE;
2095 WHEN OTHERS THEN
2096 ROLLBACK TO DeleteBSCDimLevsPVT;
2097 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2098 IF (x_msg_data IS NOT NULL) THEN
2099 x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Delete_Dim_Levels ';
2100 ELSE
2101 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Delete_Dim_Levels ';
2102 END IF;
2103 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2104 RAISE;
2105 end Delete_Dim_Levels;
2106
2107 /************************************************************************************
2108 ************************************************************************************/
2109
2110 --: This procedure updates an analysis option with dimension set information.
2111 --: This procedure is part of the Dimension Set API.
2112
2113 procedure Update_Kpi_Analysis_Options_B(
2114 p_commit IN varchar2 := FND_API.G_FALSE
2115 ,p_Dim_Set_Rec IN BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
2116 ,x_return_status OUT NOCOPY varchar2
2117 ,x_msg_count OUT NOCOPY number
2118 ,x_msg_data OUT NOCOPY varchar2
2119 ) is
2120 l_option_id NUMBER := 0;
2121 begin
2122 SAVEPOINT UpdateBSCKpiAnaOptsPVT;
2123 FND_MSG_PUB.Initialize;
2124 x_return_status := FND_API.G_RET_STS_SUCCESS;
2125 -- If action value is 'RESET' then reset the values for dimension set 0,
2126 -- else update the current dimension set id.
2127 IF (p_Dim_Set_Rec.Bsc_Analysis_Id IS NOT NULL) THEN
2128 l_option_id := p_Dim_Set_Rec.Bsc_Analysis_Id;
2129 END IF;
2130 if upper(p_Dim_Set_Rec.Bsc_Action) = 'RESET' then
2131
2132 update BSC_KPI_ANALYSIS_OPTIONS_B
2133 set dim_set_id = 0
2134 where indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
2135 and analysis_group_id = l_option_id
2136 and dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id;
2137
2138 /*and option_id = p_Dim_Set_Rec.Bsc_Option_Id;*/
2139
2140 /* removed this from the where clause as this is not
2141 needed.
2142 When the user deletes the dimension set
2143 within an indicator, we assign all the analysis
2144 options the defualt dimension set i.e "0" */
2145 else
2146
2147 update BSC_KPI_ANALYSIS_OPTIONS_B
2148 set dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id
2149 where indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
2150 and analysis_group_id = l_option_id
2151 and option_id = p_Dim_Set_Rec.Bsc_Option_Id;
2152
2153 end if;
2154
2155
2156 if (p_commit = FND_API.G_TRUE) then
2157 commit;
2158 end if;
2159
2160 EXCEPTION
2161 WHEN FND_API.G_EXC_ERROR THEN
2162 ROLLBACK TO UpdateBSCKpiAnaOptsPVT;
2163 FND_MSG_PUB.Count_And_Get
2164 ( p_encoded => FND_API.G_FALSE
2165 , p_count => x_msg_count
2166 , p_data => x_msg_data
2167 );
2168 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2169 x_return_status := FND_API.G_RET_STS_ERROR;
2170 RAISE;
2171 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2172 ROLLBACK TO UpdateBSCKpiAnaOptsPVT;
2173 FND_MSG_PUB.Count_And_Get
2174 ( p_encoded => FND_API.G_FALSE
2175 , p_count => x_msg_count
2176 , p_data => x_msg_data
2177 );
2178 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2179 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2180 RAISE;
2181 WHEN NO_DATA_FOUND THEN
2182 ROLLBACK TO UpdateBSCKpiAnaOptsPVT;
2183 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2184 IF (x_msg_data IS NOT NULL) THEN
2185 x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Update_Kpi_Analysis_Options_B ';
2186 ELSE
2187 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Update_Kpi_Analysis_Options_B ';
2188 END IF;
2189 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
2190 RAISE;
2191 WHEN OTHERS THEN
2192 ROLLBACK TO UpdateBSCKpiAnaOptsPVT;
2193 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2194 IF (x_msg_data IS NOT NULL) THEN
2195 x_msg_data := x_msg_data||' -> BSC_DIMENSION_SETS_PVT.Update_Kpi_Analysis_Options_B ';
2196 ELSE
2197 x_msg_data := SQLERRM||' at BSC_DIMENSION_SETS_PVT.Update_Kpi_Analysis_Options_B ';
2198 END IF;
2199 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2200 RAISE;
2201 end Update_Kpi_Analysis_Options_B;
2202
2203 /************************************************************************************
2204 ************************************************************************************/
2205
2206 end BSC_DIMENSION_SETS_PVT;