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