[Home] [Help]
PACKAGE BODY: APPS.BSC_DIMENSION_GROUPS_PUB
Source
1 package body BSC_DIMENSION_GROUPS_PUB as
2 /* $Header: BSCPDMGB.pls 120.0 2005/05/31 18:54:36 appldev noship $ */
3 /*
4 +==============================================================================+
5 | Copyright (c) 2001 Oracle Corporation, Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +==============================================================================+
8 | FILENAME |
9 | BSCPDMGB.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 Group. |
20 | History: |
21 | 23-FEB-03 PAJOHRI Added Short_Name to Bsc_Dim_Group_Rec_Type |
22 | Created Overloaded procedures CREATE_DIMENSION_GROUP |
23 | UPDATE_DIMENSION_GROUP |
24 | 29-MAY-03 All Enhancement Phase I- Functions added |
25 | "Retrieve_Sys_Dim_Lvls_Grp_Wrap" |
26 | and set_dim_lvl_grp_prop_wrap |
27 | 07-JUN-03 mahrao Modified for ALL enhancement |
28 | 13-JUN-03 Adeulgao fixed Bug#2878840,Added function Get_Next_Value to get |
29 | the next DIM GROUP ID |
30 | 13-JUN-03 Adeulgao Modified procedure Create_Dimension_Group for Bug2878840 |
31 | 14-JUN-03 mahrao Added Translate_dimesnsion_group procedure |
32 | 17-JUL-03 mahrao Modified exception handling section of |
33 | Translate_Dimension_Group as part of forward porting of |
34 | ALL enhancement to BSC 5.1 |
35 | Modified load_dimension_group as part of forward porting |
36 | of ALL enhancement to BSC 5.1. |
37 | Modified exception handling section of |
38 | load_dim_levels_in_group as part of forward porting of |
39 | ALL enhancement to BSC 5.1 |
40 | Modified exception handling section of as |
41 | ret_dimgrpid_fr_shname part of forward port of |
42 | ALL enhancement to BSC 5.1 |
43 | 22-JUL-2003 arhegde bug#3050270 Added dim_properties_default_values and calls|
44 | 29-OCT-2003 mahrao bug#3209967 Added a column to bsc_sys_dim_levels_by_group|
45 | 14-NOV-2003 mahrao x_dim_level_where_clause is removed from prcoedure |
46 | Retrieve_Sys_Dim_Lvls_Grp_Wrap as PMF 4.0.7 shouldn't |
47 | pick up any dependency on 5.1.1 |
48 | 07-JAN-2004 rpenneru bug#3459443 Modified for getting where clause from |
49 | BSC data model |
50 | 30-Jul-04 rpenneru Modified for enhancemen#3748519 |
51 | 21-DEC-04 vtulasi Modified for bug#4045278 - Addtion of LUD |
52 | 30-DEC-04 vtulasi For bug #4093926 |
53 +==============================================================================+
54 */
55 G_PKG_NAME CONSTANT varchar2(30) := 'BSC_DIMENSION_GROUPS_PUB';
56
57 --: This procedure is used to Create a Dimension Group. This is the entry point
58 --: for the API for the Dimension Group entity.
59 --: This procedure is part of the Dimension Group API.
60
61 procedure Create_Dimension_Group(
62 p_commit IN varchar2 := FND_API.G_FALSE
63 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
64 ,p_create_Dim_Levels IN BOOLEAN
65 ,x_return_status OUT NOCOPY varchar2
66 ,x_msg_count OUT NOCOPY number
67 ,x_msg_data OUT NOCOPY varchar2
68 ) is
69
70 l_Dim_Grp_Rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
71
72 begin
73 x_return_status := FND_API.G_RET_STS_SUCCESS;
74 FND_MSG_PUB.Initialize;
75 -- Assign all values in the passed "Record" parameter to the locally defined
76 -- "Record" variable.
77 l_Dim_Grp_Rec := p_Dim_Grp_Rec;
78
79 -- Assign certain default values if ther are currently null.
80 if l_Dim_Grp_Rec.Bsc_Group_Level_Comp_Flag is null then
81 l_Dim_Grp_Rec.Bsc_Group_Level_Comp_Flag := 1;
82 end if;
83 if l_Dim_Grp_Rec.Bsc_Group_Level_Default_Value is null then
84 l_Dim_Grp_Rec.Bsc_Group_Level_Default_Value := 'T';
85 end if;
86 if l_Dim_Grp_Rec.Bsc_Group_Level_Default_Type is null then
87 l_Dim_Grp_Rec.Bsc_Group_Level_Default_Type := 0;
88 end if;
89 if l_Dim_Grp_Rec.Bsc_Group_Level_Filter_Value is null then
90 l_Dim_Grp_Rec.Bsc_Group_Level_Filter_Value := 0;
91 end if;
92 if l_Dim_Grp_Rec.Bsc_Group_Level_No_Items is null then
93 l_Dim_Grp_Rec.Bsc_Group_Level_No_Items := 0;
94 end if;
95 if l_Dim_Grp_Rec.Bsc_Group_Level_Parent_In_Tot is null then
96 l_Dim_Grp_Rec.Bsc_Group_Level_Parent_In_Tot := 2;
97 end if;
98 if l_Dim_Grp_Rec.Bsc_Group_Level_Total_Flag is null then
99 l_Dim_Grp_Rec.Bsc_Group_Level_Total_Flag := -1;
100 end if;
101
102 -- PMD WHO Columns for Granular Locking
103 if l_Dim_Grp_Rec.Bsc_Created_By is null then
104 l_Dim_Grp_Rec.Bsc_Created_By := FND_GLOBAL.USER_ID;
105 end if;
106 l_Dim_Grp_Rec.Bsc_Last_Update_Date := NVL(p_Dim_Grp_Rec.Bsc_Last_Update_Date, SYSDATE);
107 if l_Dim_Grp_Rec.Bsc_Creation_Date is null then
108 l_Dim_Grp_Rec.Bsc_Creation_Date := l_Dim_Grp_Rec.Bsc_Last_Update_Date;
109 end if;
110 if l_Dim_Grp_Rec.Bsc_Last_Updated_By is null then
111 l_Dim_Grp_Rec.Bsc_Last_Updated_By := FND_GLOBAL.USER_ID;
112 end if;
113 if l_Dim_Grp_Rec.Bsc_Last_Update_Login is null then
114 l_Dim_Grp_Rec.Bsc_Last_Update_Login := FND_GLOBAL.LOGIN_ID;
115 end if;
116
117
118 -- Check that a group name has been entered.
119 if l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name is not null then
120
121 -- Validate if this Group already exists, if it does not then create it and assign
122 -- the current dimension to the group, if the group already exists then just assign
123 -- the dimension level to it.
124 --PAJOHRI Commented the condition below
125 --if BSC_DIMENSION_LEVELS_PVT.Validate_Dim_Group(p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name) < 1 then
126
127 -- Get the next ID value for the current group.
128
129 IF l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id IS NULL THEN
130 l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id := BSC_DIMENSION_LEVELS_PVT.Get_Next_Value( 'BSC_SYS_DIM_GROUPS_TL'
131 ,'DIM_GROUP_ID');
132 END IF;
133
134
135 -- Call private version of this procedure.
136 BSC_DIMENSION_GROUPS_PVT.Create_Dimension_Group( p_commit
137 ,l_Dim_Grp_Rec
138 ,x_return_status
139 ,x_msg_count
140 ,x_msg_data);
141
142
143 -- Call private version of this procedure.
144 IF (p_create_Dim_Levels) THEN
145 Create_Dim_Levels_In_Group( p_commit
146 ,l_Dim_Grp_Rec
147 ,x_return_status
148 ,x_msg_count
149 ,x_msg_data);
150 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
151 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
152 END IF;
153 END IF;
154
155 /* else
156 IF (p_create_Dim_Levels) THEN
157 -- Get the group id for the current group name.
158 select dim_group_id
159 into l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
160 from BSC_SYS_DIM_GROUPS_VL
161 where upper(name) = upper(p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name);
162
163 -- Call private version of this procedure.
164 Create_Dim_Levels_In_Group( p_commit
165 ,l_Dim_Grp_Rec
166 ,x_return_status
167 ,x_msg_count
168 ,x_msg_data);
169 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
170 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
171 END IF;
172 END IF;
173 end if;*/
174
175 else
176
177 FND_MESSAGE.SET_NAME('BSC','BSC_GROUP_NAME_NOT_ENTERED');
178 FND_MESSAGE.SET_TOKEN('BSC_GROUP_NAME', l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name);
179 FND_MSG_PUB.ADD;
180 RAISE FND_API.G_EXC_ERROR;
181
182 end if;
183
184
185 EXCEPTION
186 WHEN FND_API.G_EXC_ERROR THEN
187 IF (x_msg_data IS NULL) THEN
188 FND_MSG_PUB.Count_And_Get
189 ( p_encoded => 'F'
190 , p_count => x_msg_count
191 , p_data => x_msg_data
192 );
193 END IF;
194 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
195 x_return_status := FND_API.G_RET_STS_ERROR;
196 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
197 IF (x_msg_data IS NULL) THEN
198 FND_MSG_PUB.Count_And_Get
199 ( p_encoded => 'F'
200 , p_count => x_msg_count
201 , p_data => x_msg_data
202 );
203 END IF;
204 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
205 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
206 WHEN NO_DATA_FOUND THEN
207 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
208 IF (x_msg_data IS NOT NULL) THEN
209 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.Create_Dimension_Group ';
210 ELSE
211 x_msg_data := SQLERRM||' at BSC_DIMENSION_GROUPS_PUB.Create_Dimension_Group ';
212 END IF;
213 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
214 WHEN OTHERS THEN
215 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
216 IF (x_msg_data IS NOT NULL) THEN
217 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.Create_Dimension_Group ';
218 ELSE
219 x_msg_data := SQLERRM||' at BSC_DIMENSION_GROUPS_PUB.Create_Dimension_Group ';
220 END IF;
221 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
222 end Create_Dimension_Group;
223
224
225 procedure Create_Dimension_Group(
226 p_commit IN varchar2 := FND_API.G_FALSE
227 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
228 ,x_return_status OUT NOCOPY varchar2
229 ,x_msg_count OUT NOCOPY number
230 ,x_msg_data OUT NOCOPY varchar2
231 ) is
232
233 l_Dim_Grp_Rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
234
235 BEGIN
236 l_Dim_Grp_Rec := p_Dim_Grp_Rec;
237 l_Dim_Grp_Rec.Bsc_Last_Update_Date := NVL(p_Dim_Grp_Rec.Bsc_Last_Update_Date, SYSDATE);
238
239 x_return_status := FND_API.G_RET_STS_SUCCESS;
240 Create_Dimension_Group(
241 p_commit => p_commit
242 ,p_Dim_Grp_Rec => l_Dim_Grp_Rec
243 ,p_create_Dim_Levels => TRUE
244 ,x_return_status => x_return_status
245 ,x_msg_count => x_msg_count
246 ,x_msg_data => x_msg_data
247 );
248 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
249 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
250 END IF;
251 EXCEPTION
252 WHEN FND_API.G_EXC_ERROR THEN
253 IF (x_msg_data IS NULL) THEN
254 FND_MSG_PUB.Count_And_Get
255 ( p_encoded => 'F'
256 , p_count => x_msg_count
257 , p_data => x_msg_data
258 );
259 END IF;
260 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
261 x_return_status := FND_API.G_RET_STS_ERROR;
262 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
263 IF (x_msg_data IS NULL) THEN
264 FND_MSG_PUB.Count_And_Get
265 ( p_encoded => 'F'
266 , p_count => x_msg_count
267 , p_data => x_msg_data
268 );
269 END IF;
270 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
271 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
272 WHEN NO_DATA_FOUND THEN
273 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
274 IF (x_msg_data IS NOT NULL) THEN
275 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.Create_Dimension_Group ';
276 ELSE
277 x_msg_data := SQLERRM||' at BSC_DIMENSION_GROUPS_PUB.Create_Dimension_Group ';
278 END IF;
279 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
280 WHEN OTHERS THEN
281 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
282 IF (x_msg_data IS NOT NULL) THEN
283 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.Create_Dimension_Group ';
284 ELSE
285 x_msg_data := SQLERRM||' at BSC_DIMENSION_GROUPS_PUB.Create_Dimension_Group ';
286 END IF;
287 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
288 end Create_Dimension_Group;
289 /************************************************************************************
290 ************************************************************************************/
291
292 procedure Retrieve_Dimension_Group(
293 p_commit IN varchar2 := FND_API.G_FALSE
294 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
295 ,x_Dim_Grp_Rec IN OUT NOCOPY BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
296 ,x_return_status OUT NOCOPY varchar2
297 ,x_msg_count OUT NOCOPY number
298 ,x_msg_data OUT NOCOPY varchar2
299 ) is
300
301 begin
302 x_return_status := FND_API.G_RET_STS_SUCCESS;
303 BSC_DIMENSION_GROUPS_PVT.Retrieve_Dimension_Group( p_commit
304 ,p_Dim_Grp_Rec
305 ,x_Dim_Grp_Rec
306 ,x_return_status
307 ,x_msg_count
308 ,x_msg_data);
309
310 Retrieve_Dim_Levels_In_Group( p_commit
311 ,p_Dim_Grp_Rec
312 ,x_Dim_Grp_Rec
313 ,x_return_status
314 ,x_msg_count
315 ,x_msg_data);
316 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
317 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
318 END IF;
319 EXCEPTION
320 WHEN FND_API.G_EXC_ERROR THEN
321 IF (x_msg_data IS NULL) THEN
322 FND_MSG_PUB.Count_And_Get
323 ( p_encoded => 'F'
324 , p_count => x_msg_count
325 , p_data => x_msg_data
326 );
327 END IF;
328 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
329 x_return_status := FND_API.G_RET_STS_ERROR;
330 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
331 IF (x_msg_data IS NULL) THEN
332 FND_MSG_PUB.Count_And_Get
333 ( p_encoded => 'F'
334 , p_count => x_msg_count
335 , p_data => x_msg_data
336 );
337 END IF;
338 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
339 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
340 WHEN NO_DATA_FOUND THEN
341 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
342 IF (x_msg_data IS NOT NULL) THEN
343 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.Retrieve_Dimension_Group ';
344 ELSE
345 x_msg_data := SQLERRM||' at BSC_DIMENSION_GROUPS_PUB.Retrieve_Dimension_Group ';
346 END IF;
347 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
348 WHEN OTHERS THEN
349 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
350 IF (x_msg_data IS NOT NULL) THEN
351 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.Retrieve_Dimension_Group ';
352 ELSE
353 x_msg_data := SQLERRM||' at BSC_DIMENSION_GROUPS_PUB.Retrieve_Dimension_Group ';
354 END IF;
355 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
356
357 end Retrieve_Dimension_Group;
358
359 /************************************************************************************
360 ************************************************************************************/
361
362 procedure Update_Dimension_Group(
363 p_commit IN varchar2 := FND_API.G_FALSE
364 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
365 ,p_create_Dim_Levels IN BOOLEAN
366 ,x_return_status OUT NOCOPY varchar2
367 ,x_msg_count OUT NOCOPY number
368 ,x_msg_data OUT NOCOPY varchar2
369 ) IS
370
371 l_Dim_Grp_Rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
372 begin
373
374 l_Dim_Grp_Rec := p_Dim_Grp_Rec;
375 l_Dim_Grp_Rec.Bsc_Last_Update_Date := NVL(p_Dim_Grp_Rec.Bsc_Last_Update_Date, SYSDATE);
376 x_return_status := FND_API.G_RET_STS_SUCCESS;
377
378 BSC_DIMENSION_GROUPS_PVT.Update_Dimension_Group( p_commit
379 ,l_Dim_Grp_Rec
380 ,x_return_status
381 ,x_msg_count
382 ,x_msg_data);
383 IF (p_create_Dim_Levels) THEN
384 Update_Dim_Levels_In_Group( p_commit
385 ,p_Dim_Grp_Rec
386 ,x_return_status
387 ,x_msg_count
388 ,x_msg_data);
389 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
390 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
391 END IF;
392 END IF;
393
394 EXCEPTION
395 WHEN FND_API.G_EXC_ERROR THEN
396 IF (x_msg_data IS NULL) THEN
397 FND_MSG_PUB.Count_And_Get
398 ( p_encoded => 'F'
399 , p_count => x_msg_count
400 , p_data => x_msg_data
401 );
402 END IF;
403 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
404 x_return_status := FND_API.G_RET_STS_ERROR;
405 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
406 IF (x_msg_data IS NULL) THEN
407 FND_MSG_PUB.Count_And_Get
408 ( p_encoded => 'F'
409 , p_count => x_msg_count
410 , p_data => x_msg_data
411 );
412 END IF;
413 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
414 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
415 WHEN NO_DATA_FOUND THEN
416 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
417 IF (x_msg_data IS NOT NULL) THEN
418 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.Update_Dimension_Group ';
419 ELSE
420 x_msg_data := SQLERRM||' at BSC_DIMENSION_GROUPS_PUB.Update_Dimension_Group ';
421 END IF;
422 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
423 WHEN OTHERS THEN
424 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
425 IF (x_msg_data IS NOT NULL) THEN
426 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.Update_Dimension_Group ';
427 ELSE
428 x_msg_data := SQLERRM||' at BSC_DIMENSION_GROUPS_PUB.Update_Dimension_Group ';
429 END IF;
430 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
431
432 END Update_Dimension_Group;
433
434
435 procedure Update_Dimension_Group(
436 p_commit IN varchar2 := FND_API.G_FALSE
437 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
438 ,x_return_status OUT NOCOPY varchar2
439 ,x_msg_count OUT NOCOPY number
440 ,x_msg_data OUT NOCOPY varchar2
441 ) is
442
443 l_Dim_Grp_Rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
444 begin
445 l_Dim_Grp_Rec := p_Dim_Grp_Rec;
446 l_Dim_Grp_Rec.Bsc_Last_Update_Date := NVL(p_Dim_Grp_Rec.Bsc_Last_Update_Date, SYSDATE);
447
448 x_return_status := FND_API.G_RET_STS_SUCCESS;
449 Update_Dimension_Group(
450 p_commit => p_commit
451 ,p_Dim_Grp_Rec => l_Dim_Grp_Rec
452 ,p_create_Dim_Levels => TRUE
453 ,x_return_status => x_return_status
454 ,x_msg_count => x_msg_count
455 ,x_msg_data => x_msg_data
456 );
457 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
458 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
459 END IF;
460 EXCEPTION
461 WHEN FND_API.G_EXC_ERROR THEN
462 IF (x_msg_data IS NULL) THEN
463 FND_MSG_PUB.Count_And_Get
464 ( p_encoded => 'F'
465 , p_count => x_msg_count
466 , p_data => x_msg_data
467 );
468 END IF;
469 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
470 x_return_status := FND_API.G_RET_STS_ERROR;
471 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
472 IF (x_msg_data IS NULL) THEN
473 FND_MSG_PUB.Count_And_Get
474 ( p_encoded => 'F'
475 , p_count => x_msg_count
476 , p_data => x_msg_data
477 );
478 END IF;
479 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
480 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
481 WHEN NO_DATA_FOUND THEN
482 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
483 IF (x_msg_data IS NOT NULL) THEN
484 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.Update_Dimension_Group ';
485 ELSE
486 x_msg_data := SQLERRM||' at BSC_DIMENSION_GROUPS_PUB.Update_Dimension_Group ';
487 END IF;
488 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
489 WHEN OTHERS THEN
490 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
491 IF (x_msg_data IS NOT NULL) THEN
492 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.Update_Dimension_Group ';
493 ELSE
494 x_msg_data := SQLERRM||' at BSC_DIMENSION_GROUPS_PUB.Update_Dimension_Group ';
495 END IF;
496 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
497
498 end Update_Dimension_Group;
499
500 /************************************************************************************
501 ************************************************************************************/
502
503 --: This procedure is part of the Dimension Group API.
504
505 procedure Delete_Dimension_Group(
506 p_commit IN varchar2 := FND_API.G_FALSE
507 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
508 ,x_return_status OUT NOCOPY varchar2
509 ,x_msg_count OUT NOCOPY number
510 ,x_msg_data OUT NOCOPY varchar2
511 )is
512
513 begin
514 x_return_status := FND_API.G_RET_STS_SUCCESS;
515 -- When deleting dimension groups the first thing to do is to delete
516 -- the dimension levels from the group.
517 Delete_Dim_Levels_In_Group( p_commit
518 ,p_Dim_Grp_Rec
519 ,x_return_status
520 ,x_msg_count
521 ,x_msg_data);
522 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
523 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
524 END IF;
525 BSC_DIMENSION_GROUPS_PVT.Delete_Dimension_Group( p_commit
526 ,p_Dim_Grp_Rec
527 ,x_return_status
528 ,x_msg_count
529 ,x_msg_data);
530
531
532 EXCEPTION
533 WHEN FND_API.G_EXC_ERROR THEN
534 IF (x_msg_data IS NULL) THEN
535 FND_MSG_PUB.Count_And_Get
536 ( p_encoded => 'F'
537 , p_count => x_msg_count
538 , p_data => x_msg_data
539 );
540 END IF;
541 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
542 x_return_status := FND_API.G_RET_STS_ERROR;
543 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
544 IF (x_msg_data IS NULL) THEN
545 FND_MSG_PUB.Count_And_Get
546 ( p_encoded => 'F'
547 , p_count => x_msg_count
548 , p_data => x_msg_data
549 );
550 END IF;
551 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
552 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
553 WHEN NO_DATA_FOUND THEN
554 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
555 IF (x_msg_data IS NOT NULL) THEN
556 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.Delete_Dimension_Group ';
557 ELSE
558 x_msg_data := SQLERRM||' at BSC_DIMENSION_GROUPS_PUB.Delete_Dimension_Group ';
559 END IF;
560 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
561 WHEN OTHERS THEN
562 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
563 IF (x_msg_data IS NOT NULL) THEN
564 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.Delete_Dimension_Group ';
565 ELSE
566 x_msg_data := SQLERRM||' at BSC_DIMENSION_GROUPS_PUB.Delete_Dimension_Group ';
567 END IF;
568 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
569
570 end Delete_Dimension_Group;
571
572 /************************************************************************************
573 ************************************************************************************/
574
575 --: This procedure assigns the dimension to the dimension group.
576 --: This procedure is part of the Dimension Group API.
577
578 procedure Create_Dim_Levels_In_Group(
579 p_commit IN varchar2 := FND_API.G_FALSE
580 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
581 ,x_return_status OUT NOCOPY varchar2
582 ,x_msg_count OUT NOCOPY number
583 ,x_msg_data OUT NOCOPY varchar2
584 ) is
585 l_dim_level_index NUMBER;
586 l_total_flag NUMBER;
587 l_comparison_flag NUMBER;
588 l_filter_column bsc_sys_dim_levels_by_group.filter_column%TYPE;
589 l_filter_value NUMBER;
590 l_default_value bsc_sys_dim_levels_by_group.default_value%TYPE;
591 l_default_type NUMBER;
592 l_parent_in_total NUMBER;
593 l_no_items NUMBER;
594 l_total_disp_name bsc_sys_dim_levels_vl.total_disp_name%TYPE;
595 l_comp_disp_name bsc_sys_dim_levels_vl.comp_disp_name%TYPE;
596 l_Dim_Grp_Rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
597 begin
598
599 x_return_status := FND_API.G_RET_STS_SUCCESS;
600 l_Dim_Grp_Rec := p_Dim_Grp_Rec;
601
602 dim_properties_default_values (
603 x_dim_level_index => l_dim_level_index
604 ,x_total_flag => l_total_flag
605 ,x_comparison_flag => l_comparison_flag
606 ,x_filter_column => l_filter_column
607 ,x_filter_value => l_filter_value
608 ,x_default_value => l_default_value
609 ,x_default_type => l_default_type
610 ,x_parent_in_total => l_parent_in_total
611 ,x_no_items => l_no_items
612 ,x_total_disp_name => l_total_disp_name
613 ,x_comp_disp_name => l_comp_disp_name
614 );
615
616 IF (l_Dim_Grp_Rec.Bsc_Group_Level_Comp_Flag IS NULL) THEN
617 l_Dim_Grp_Rec.Bsc_Group_Level_Comp_Flag := l_comparison_flag;
618 END IF;
619 IF (l_Dim_Grp_Rec.Bsc_Group_Level_Default_Value IS NULL) THEN
620 l_Dim_Grp_Rec.Bsc_Group_Level_Default_Value := l_default_value;
621 END IF;
622 IF (l_Dim_Grp_Rec.Bsc_Group_Level_Default_Type IS NULL) THEN
623 l_Dim_Grp_Rec.Bsc_Group_Level_Default_Type := l_default_type;
624 END IF;
625 IF (l_Dim_Grp_Rec.Bsc_Group_Level_Filter_Value IS NULL) THEN
626 l_Dim_Grp_Rec.Bsc_Group_Level_Filter_Value := l_filter_value;
627 END IF;
628 IF (l_Dim_Grp_Rec.Bsc_Group_Level_No_Items IS NULL) THEN
629 l_Dim_Grp_Rec.Bsc_Group_Level_No_Items := l_no_items;
630 END IF;
631 IF (l_Dim_Grp_Rec.Bsc_Group_Level_Parent_In_Tot IS NULL) THEN
632 l_Dim_Grp_Rec.Bsc_Group_Level_Parent_In_Tot := l_parent_in_total;
633 END IF;
634 IF (l_Dim_Grp_Rec.Bsc_Group_Level_Total_Flag IS NULL) THEN
635 l_Dim_Grp_Rec.Bsc_Group_Level_Total_Flag := l_total_flag;
636 END IF;
637
638 -- Call private version of the procedure.
639 BSC_DIMENSION_GROUPS_PVT.Create_Dim_Levels_In_Group( p_commit
640 ,l_Dim_Grp_Rec
641 ,x_return_status
642 ,x_msg_count
643 ,x_msg_data);
644
645 EXCEPTION
646 WHEN FND_API.G_EXC_ERROR THEN
647 IF (x_msg_data IS NULL) THEN
648 FND_MSG_PUB.Count_And_Get
649 ( p_encoded => 'F'
650 , p_count => x_msg_count
651 , p_data => x_msg_data
652 );
653 END IF;
654 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
655 x_return_status := FND_API.G_RET_STS_ERROR;
656 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
657 IF (x_msg_data IS NULL) THEN
658 FND_MSG_PUB.Count_And_Get
659 ( p_encoded => 'F'
660 , p_count => x_msg_count
661 , p_data => x_msg_data
662 );
663 END IF;
664 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
665 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
666 WHEN NO_DATA_FOUND THEN
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_GROUPS_PUB.Create_Dim_Levels_In_Group ';
670 ELSE
671 x_msg_data := SQLERRM||' at BSC_DIMENSION_GROUPS_PUB.Create_Dim_Levels_In_Group ';
672 END IF;
673 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
674 WHEN OTHERS THEN
675 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
676 IF (x_msg_data IS NOT NULL) THEN
677 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.Create_Dim_Levels_In_Group ';
678 ELSE
679 x_msg_data := SQLERRM||' at BSC_DIMENSION_GROUPS_PUB.Create_Dim_Levels_In_Group ';
680 END IF;
681 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
682
683 end Create_Dim_Levels_In_Group;
684
685 /************************************************************************************
686 ************************************************************************************/
687
688 procedure Retrieve_Dim_Levels_In_Group(
689 p_commit IN varchar2 := FND_API.G_FALSE
690 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
691 ,x_Dim_Grp_Rec IN OUT NOCOPY BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
692 ,x_return_status OUT NOCOPY varchar2
693 ,x_msg_count OUT NOCOPY number
694 ,x_msg_data OUT NOCOPY varchar2
695 ) is
696
697 begin
698 x_return_status := FND_API.G_RET_STS_SUCCESS;
699 BSC_DIMENSION_GROUPS_PVT.Retrieve_Dim_Levels_In_Group( p_commit
700 ,p_Dim_Grp_Rec
701 ,x_Dim_Grp_Rec
702 ,x_return_status
703 ,x_msg_count
704 ,x_msg_data);
705
706 EXCEPTION
707 WHEN FND_API.G_EXC_ERROR THEN
708 IF (x_msg_data IS NULL) THEN
709 FND_MSG_PUB.Count_And_Get
710 ( p_encoded => 'F'
711 , p_count => x_msg_count
712 , p_data => x_msg_data
713 );
714 END IF;
715 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
716 x_return_status := FND_API.G_RET_STS_ERROR;
717 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
718 IF (x_msg_data IS NULL) THEN
719 FND_MSG_PUB.Count_And_Get
720 ( p_encoded => 'F'
721 , p_count => x_msg_count
722 , p_data => x_msg_data
723 );
724 END IF;
725 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
726 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
727 WHEN NO_DATA_FOUND THEN
728 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
729 IF (x_msg_data IS NOT NULL) THEN
730 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.Retrieve_Dim_Levels_In_Group ';
731 ELSE
732 x_msg_data := SQLERRM||' at BSC_DIMENSION_GROUPS_PUB.Retrieve_Dim_Levels_In_Group ';
733 END IF;
734 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
735 WHEN OTHERS THEN
736 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
737 IF (x_msg_data IS NOT NULL) THEN
738 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.Retrieve_Dim_Levels_In_Group ';
739 ELSE
740 x_msg_data := SQLERRM||' at BSC_DIMENSION_GROUPS_PUB.Retrieve_Dim_Levels_In_Group ';
741 END IF;
742 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
743
744 end Retrieve_Dim_Levels_In_Group;
745
746 /************************************************************************************
747 ************************************************************************************/
748
749 procedure Update_Dim_Levels_In_Group(
750 p_commit IN varchar2 := FND_API.G_FALSE
751 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
752 ,x_return_status OUT NOCOPY varchar2
753 ,x_msg_count OUT NOCOPY number
754 ,x_msg_data OUT NOCOPY varchar2
755 ) is
756
757 begin
758 x_return_status := FND_API.G_RET_STS_SUCCESS;
759 BSC_DIMENSION_GROUPS_PVT.Update_Dim_Levels_In_Group( p_commit
760 ,p_Dim_Grp_Rec
761 ,x_return_status
762 ,x_msg_count
763 ,x_msg_data);
764
765 EXCEPTION
766 WHEN FND_API.G_EXC_ERROR THEN
767 IF (x_msg_data IS NULL) THEN
768 FND_MSG_PUB.Count_And_Get
769 ( p_encoded => 'F'
770 , p_count => x_msg_count
771 , p_data => x_msg_data
772 );
773 END IF;
774 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
775 x_return_status := FND_API.G_RET_STS_ERROR;
776 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
777 IF (x_msg_data IS NULL) THEN
778 FND_MSG_PUB.Count_And_Get
779 ( p_encoded => 'F'
780 , p_count => x_msg_count
781 , p_data => x_msg_data
782 );
783 END IF;
784 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
785 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
786 WHEN NO_DATA_FOUND THEN
787 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
788 IF (x_msg_data IS NOT NULL) THEN
789 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.Update_Dim_Levels_In_Group ';
790 ELSE
791 x_msg_data := SQLERRM||' at BSC_DIMENSION_GROUPS_PUB.Update_Dim_Levels_In_Group ';
792 END IF;
793 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
794 WHEN OTHERS THEN
795 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
796 IF (x_msg_data IS NOT NULL) THEN
797 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.Update_Dim_Levels_In_Group ';
798 ELSE
799 x_msg_data := SQLERRM||' at BSC_DIMENSION_GROUPS_PUB.Update_Dim_Levels_In_Group ';
800 END IF;
801 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
802
803 end Update_Dim_Levels_In_Group;
804
805 /************************************************************************************
806 ************************************************************************************/
807
808 --: This procedure deletes dimensions from dimension groups.
809
810 procedure Delete_Dim_Levels_In_Group(
811 p_commit IN varchar2 := FND_API.G_FALSE
812 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
813 ,x_return_status OUT NOCOPY varchar2
814 ,x_msg_count OUT NOCOPY number
815 ,x_msg_data OUT NOCOPY varchar2
816 ) is
817
818 begin
819 x_return_status := FND_API.G_RET_STS_SUCCESS;
820 BSC_DIMENSION_GROUPS_PVT.Delete_Dim_Levels_In_Group( p_commit
821 ,p_Dim_Grp_Rec
822 ,x_return_status
823 ,x_msg_count
824 ,x_msg_data);
825
826 EXCEPTION
827 WHEN FND_API.G_EXC_ERROR THEN
828 IF (x_msg_data IS NULL) THEN
829 FND_MSG_PUB.Count_And_Get
830 ( p_encoded => 'F'
831 , p_count => x_msg_count
832 , p_data => x_msg_data
833 );
834 END IF;
835 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
836 x_return_status := FND_API.G_RET_STS_ERROR;
837 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
838 IF (x_msg_data IS NULL) THEN
839 FND_MSG_PUB.Count_And_Get
840 ( p_encoded => 'F'
841 , p_count => x_msg_count
842 , p_data => x_msg_data
843 );
844 END IF;
845 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
846 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
847 WHEN NO_DATA_FOUND THEN
848 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
849 IF (x_msg_data IS NOT NULL) THEN
850 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.Delete_Dim_Levels_In_Group ';
851 ELSE
852 x_msg_data := SQLERRM||' at BSC_DIMENSION_GROUPS_PUB.Delete_Dim_Levels_In_Group ';
853 END IF;
854 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
855 WHEN OTHERS THEN
856 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
857 IF (x_msg_data IS NOT NULL) THEN
858 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.Delete_Dim_Levels_In_Group ';
859 ELSE
860 x_msg_data := SQLERRM||' at BSC_DIMENSION_GROUPS_PUB.Delete_Dim_Levels_In_Group ';
861 END IF;
862 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
863
864 end Delete_Dim_Levels_In_Group;
865
866 --===================================================================
867 /*
868 * This is called from PMF - DimLevelUtil.java
869 * Returns the properties set in bsc_sys_dim_levels_by_group such as
870 * 'All' property. If no data is present in the tables, then it returns
871 * default values set at the record level
872 */
873
874 PROCEDURE Retrieve_Sys_Dim_Lvls_Grp_Wrap
875 (
876 p_dim_level_shortname IN VARCHAR2
877 , p_dim_shortname IN VARCHAR2
878 , x_dim_group_id OUT NOCOPY NUMBER
879 , x_dim_level_id OUT NOCOPY NUMBER
880 , x_dim_level_index OUT NOCOPY NUMBER
881 , x_total_flag OUT NOCOPY NUMBER
882 , x_total_disp_name OUT NOCOPY VARCHAR2
883 , x_dim_level_where_clause OUT NOCOPY VARCHAR2
884 , x_comparison_flag OUT NOCOPY NUMBER
885 , x_comp_disp_name OUT NOCOPY VARCHAR2
886 , x_filter_column OUT NOCOPY VARCHAR2
887 , x_filter_value OUT NOCOPY NUMBER
888 , x_default_value OUT NOCOPY VARCHAR2
889 , x_default_type OUT NOCOPY NUMBER
890 , x_parent_in_total OUT NOCOPY NUMBER
891 , x_no_items OUT NOCOPY NUMBER
892 , x_return_status OUT NOCOPY VARCHAR2
893 , x_msg_count OUT NOCOPY NUMBER
894 , x_msg_data OUT NOCOPY VARCHAR2
895 )
896 IS
897 l_dim_level_id NUMBER;
898 l_dim_group_id NUMBER;
899 l_dim_grp_rec BSC_DIMENSION_GROUPS_PUB.BSC_DIM_GROUP_REC_TYPE;
900 l_dim_grp_rec_out BSC_DIMENSION_GROUPS_PUB.BSC_DIM_GROUP_REC_TYPE;
901
902 CURSOR c_dim_group (cp_dim_shortname VARCHAR2) IS
903 SELECT dim_group_id
904 FROM bsc_sys_dim_groups_vl
905 WHERE short_name = cp_dim_shortname;
906
907 CURSOR c_dim_level (cp_dim_level_shortname VARCHAR2) IS
908 SELECT dim_level_id, total_disp_name, comp_disp_name
909 FROM bsc_sys_dim_levels_vl
910 WHERE short_name = cp_dim_level_shortname;
911
912 BEGIN
913
914 x_return_status := FND_API.G_RET_STS_SUCCESS;
915
916 -- group
917 IF (c_dim_group%ISOPEN) THEN
918 CLOSE c_dim_group;
919 END IF;
920
921 OPEN c_dim_group (cp_dim_shortname => p_dim_shortname);
922 FETCH c_dim_group INTO l_dim_group_id;
923 CLOSE c_dim_group;
924
925 -- level
926 IF (c_dim_level%ISOPEN) THEN
927 CLOSE c_dim_level;
928 END IF;
929
930 OPEN c_dim_level (cp_dim_level_shortname => p_dim_level_shortname);
931 FETCH c_dim_level INTO l_dim_level_id, x_total_disp_name, x_comp_disp_name;
932 CLOSE c_dim_level;
933
934 -- only if values are present in BSC tables
935 IF ((l_dim_group_id IS NOT NULL) AND (l_dim_level_id IS NOT NULL)) THEN
936 l_dim_grp_rec.bsc_level_id := l_dim_level_id;
937 l_dim_grp_rec.Bsc_Dim_Level_Group_Id := l_dim_group_id;
938
939 BEGIN
940
941 BSC_DIMENSION_GROUPS_PVT.Retrieve_Dim_Levels_In_Group(
942 p_commit => NULL
943 ,p_Dim_Grp_Rec => l_dim_grp_rec
944 ,x_Dim_Grp_Rec => l_dim_grp_rec_out
945 ,x_return_status => x_return_status
946 ,x_msg_count => x_msg_count
947 ,x_msg_data => x_msg_data
948 );
949 EXCEPTION
950 WHEN OTHERS THEN
951 x_return_status := C_DEFAULT_DATA;
952 END;
953
954 l_dim_grp_rec_out.bsc_level_id := l_dim_level_id;
955 l_dim_grp_rec_out.Bsc_Dim_Level_Group_Id := l_dim_group_id;
956
957 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
958 x_return_status := C_DEFAULT_DATA;
959 END IF;
960 ELSE
961 x_return_status := C_DEFAULT_DATA;
962 END IF;
963
964 IF (x_return_status = C_DEFAULT_DATA) THEN
965 -- All default values when error occurs
966 dim_properties_default_values (
967 x_dim_level_index => x_dim_level_index
968 ,x_total_flag => x_total_flag
969 ,x_comparison_flag => x_comparison_flag
970 ,x_filter_column => x_filter_column
971 ,x_filter_value => x_filter_value
972 ,x_default_value => x_default_value
973 ,x_default_type => x_default_type
974 ,x_parent_in_total => x_parent_in_total
975 ,x_no_items => x_no_items
976 ,x_total_disp_name => x_total_disp_name
977 ,x_comp_disp_name => x_comp_disp_name
978 );
979
980 ELSE
981
982 -- These are populated with values retrieved
983 x_dim_group_id := l_dim_grp_rec_out.bsc_dim_level_group_id;
984 x_dim_level_id := l_dim_grp_rec_out.bsc_level_Id;
985 x_dim_level_index := l_dim_grp_rec_out.bsc_dim_level_index;
986 x_total_flag := l_dim_grp_rec_out.bsc_group_level_total_flag;
987 x_dim_level_where_clause := l_dim_grp_rec_out.Bsc_Group_Level_Where_Clause;
988 x_comparison_flag := l_dim_grp_rec_out.bsc_group_level_comp_flag;
989 x_filter_column := l_dim_grp_rec_out.bsc_group_level_filter_col;
990 x_filter_value := l_dim_grp_rec_out.bsc_group_level_filter_value;
991 x_default_value := l_dim_grp_rec_out.bsc_group_level_default_value;
992 x_default_type := l_dim_grp_rec_out.bsc_group_level_default_type;
993 x_parent_in_total := l_dim_grp_rec_out.bsc_group_level_parent_in_tot;
994 x_no_items := l_dim_grp_rec_out.bsc_group_level_no_items;
995
996 END IF;
997
998 EXCEPTION
999 WHEN FND_API.G_EXC_ERROR THEN
1000 IF (c_dim_group%ISOPEN) THEN
1001 CLOSE c_dim_group;
1002 END IF;
1003 IF (c_dim_level%ISOPEN) THEN
1004 CLOSE c_dim_level;
1005 END IF;
1006 IF (x_msg_data IS NULL) THEN
1007 FND_MSG_PUB.Count_And_Get
1008 ( p_encoded => 'F'
1009 , p_count => x_msg_count
1010 , p_data => x_msg_data
1011 );
1012 END IF;
1013 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1014 x_return_status := FND_API.G_RET_STS_ERROR;
1015 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1016 IF (c_dim_group%ISOPEN) THEN
1017 CLOSE c_dim_group;
1018 END IF;
1019 IF (c_dim_level%ISOPEN) THEN
1020 CLOSE c_dim_level;
1021 END IF;
1022 IF (x_msg_data IS NULL) THEN
1023 FND_MSG_PUB.Count_And_Get
1024 ( p_encoded => 'F'
1025 , p_count => x_msg_count
1026 , p_data => x_msg_data
1027 );
1028 END IF;
1029 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1030 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1031 WHEN OTHERS THEN
1032 IF (c_dim_group%ISOPEN) THEN
1033 CLOSE c_dim_group;
1034 END IF;
1035 IF (c_dim_level%ISOPEN) THEN
1036 CLOSE c_dim_level;
1037 END IF;
1038 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1039 IF (x_msg_data IS NOT NULL) THEN
1040 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.Retrieve_Sys_Dim_Lvls_Grp_Wrap ';
1041 ELSE
1042 x_msg_data := SQLERRM||' at BSC_DIMENSION_GROUPS_PUB.Retrieve_Sys_Dim_Lvls_Grp_Wrap ';
1043 END IF;
1044 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1045 END Retrieve_Sys_Dim_Lvls_Grp_Wrap;
1046
1047 --========================================================================
1048
1049 PROCEDURE dim_properties_default_values (
1050 x_dim_level_index OUT NOCOPY NUMBER
1051 ,x_total_flag OUT NOCOPY NUMBER
1052 ,x_comparison_flag OUT NOCOPY NUMBER
1053 ,x_filter_column OUT NOCOPY VARCHAR2
1054 ,x_filter_value OUT NOCOPY NUMBER
1055 ,x_default_value OUT NOCOPY VARCHAR2
1056 ,x_default_type OUT NOCOPY NUMBER
1057 ,x_parent_in_total OUT NOCOPY NUMBER
1058 ,x_no_items OUT NOCOPY NUMBER
1059 ,x_total_disp_name OUT NOCOPY VARCHAR2
1060 ,x_comp_disp_name OUT NOCOPY VARCHAR2
1061 )
1062 IS
1063
1064 BEGIN
1065 x_dim_level_index := NULL;
1066 x_total_flag := c_total_flag;
1067 x_comparison_flag := c_comp_flag;
1068 x_filter_column := NULL;
1069 x_filter_value := c_filter_value;
1070 x_default_value := c_default_value;
1071 x_default_type := c_default_type;
1072 x_parent_in_total := c_parent_in_tot;
1073 x_no_items := c_no_items;
1074
1075 x_total_disp_name := NVL(BSC_APPS.get_lookup_value('BSC_UI_COMMON', 'ALL'), 'ALL');
1076 x_comp_disp_name := NVL(BSC_APPS.get_lookup_value('BSC_UI_COMMON', 'COMPARISON'), 'COMPARISON');
1077
1078 EXCEPTION
1079 WHEN OTHERS THEN
1080 NULL;
1081 END;
1082
1083 --======================================================================
1084
1085 PROCEDURE set_dim_lvl_grp_prop_wrap (
1086 p_dim_level_shortname IN VARCHAR2
1087 ,p_dim_shortname IN VARCHAR2
1088 ,p_all_id IN NUMBER
1089 ,x_return_status OUT NOCOPY VARCHAR2
1090 ,x_msg_count OUT NOCOPY NUMBER
1091 ,x_msg_data OUT NOCOPY VARCHAR2
1092 ) IS
1093
1094 l_dim_grp_rec BSC_DIMENSION_GROUPS_PUB.BSC_DIM_GROUP_REC_TYPE;
1095 l_dim_level_id NUMBER;
1096 l_dim_group_id NUMBER;
1097 BEGIN
1098 x_return_status := FND_API.G_RET_STS_SUCCESS;
1099 FND_MSG_PUB.Initialize;
1100 IF (p_all_id NOT IN (0, -1)) THEN
1101 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_ALL_ID');
1102 FND_MSG_PUB.ADD;
1103 RAISE FND_API.G_EXC_ERROR;
1104 END IF;
1105 -- Dimension level short name in bsc_sys_dim_levels_b should be same as dimension
1106 -- level short name in bis_levels.
1107 SELECT dim_level_id
1108 INTO l_dim_level_id
1109 FROM bsc_sys_dim_levels_b
1110 WHERE short_name = p_dim_level_shortname;
1111
1112 -- Passing dim_group_id is mandatory for calling the below procedure. As,
1113 -- dimension short name is not populated as of now in bsc_sys_dim_groups_tl
1114 -- dim_group_id will be retrieved from the bsc_sys_dim_levels_by_group table.
1115 -- This shouldn't create any problem as dim_level_id is unique in
1116 -- bsc_sys_dim_levels_by_group table for levels imported from PMF to BSC.
1117
1118 SELECT dim_group_id
1119 INTO l_dim_group_id
1120 FROM bsc_sys_dim_groups_vl
1121 WHERE short_name = p_dim_shortname;
1122
1123 l_dim_grp_rec.bsc_level_id := l_dim_level_id;
1124 l_dim_grp_rec.bsc_dim_level_group_id := l_dim_group_id;
1125 l_dim_grp_rec.bsc_group_level_total_flag := p_all_id;
1126
1127 BSC_DIMENSION_GROUPS_PVT.Update_Dim_Levels_In_Group(
1128 p_commit => FND_API.G_TRUE
1129 ,p_dim_grp_rec => l_dim_grp_rec
1130 ,x_return_status => x_return_status
1131 ,x_msg_count => x_msg_count
1132 ,x_msg_data => x_msg_data
1133 );
1134
1135 EXCEPTION
1136 WHEN FND_API.G_EXC_ERROR THEN
1137 IF (x_msg_data IS NULL) THEN
1138 FND_MSG_PUB.Count_And_Get
1139 ( p_encoded => 'F'
1140 , p_count => x_msg_count
1141 , p_data => x_msg_data
1142 );
1143 END IF;
1144 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1145 x_return_status := FND_API.G_RET_STS_ERROR;
1146 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1147 IF (x_msg_data IS NULL) THEN
1148 FND_MSG_PUB.Count_And_Get
1149 ( p_encoded => 'F'
1150 , p_count => x_msg_count
1151 , p_data => x_msg_data
1152 );
1153 END IF;
1154 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1155 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1156 WHEN NO_DATA_FOUND THEN
1157 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1158 IF (x_msg_data IS NOT NULL) THEN
1159 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.set_dim_lvl_grp_prop_wrap ';
1160 ELSE
1161 x_msg_data := SQLERRM||' at BSC_DIMENSION_GROUPS_PUB.set_dim_lvl_grp_prop_wrap ';
1162 END IF;
1163 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1164 WHEN OTHERS THEN
1165 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1166 IF (x_msg_data IS NOT NULL) THEN
1167 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.set_dim_lvl_grp_prop_wrap ';
1168 ELSE
1169 x_msg_data := SQLERRM||' at BSC_DIMENSION_GROUPS_PUB.set_dim_lvl_grp_prop_wrap ';
1170 END IF;
1171 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1172 END set_dim_lvl_grp_prop_wrap;
1173
1174 /************************************************************************************
1175 ************************************************************************************/
1176
1177 -- Amit Code
1178
1179 function Get_Next_Value(
1180 p_table_name IN varchar2
1181 ,p_column_name IN varchar2
1182 )return number is
1183
1184 l_return_status varchar2(100);
1185 l_msg_data varchar2(10);
1186 l_msg_count number;
1187
1188 begin
1189
1190 return BSC_DIMENSION_LEVELS_PVT.Get_Next_Value( p_table_name
1191 ,p_column_name);
1192 EXCEPTION
1193
1194 WHEN FND_API.G_EXC_ERROR THEN
1195 l_return_status := FND_API.G_RET_STS_ERROR;
1196 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1197 ,p_data => l_msg_data);
1198 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1199 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1200 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1201 ,p_data => l_msg_data);
1202 raise;
1203 WHEN NO_DATA_FOUND THEN
1204 l_return_status := FND_API.G_RET_STS_ERROR;
1205 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1206 ,p_data => l_msg_data);
1207 raise;
1208 WHEN OTHERS THEN
1209 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1210 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1211 ,p_data => l_msg_data);
1212 raise;
1213 end Get_Next_Value;
1214
1215 --=============================================================================
1216 /* Used only by PMF for "All" enhancement to upload bisdimlv.ldt.
1217 * Called from BISDIMLV.lct
1218 */
1219 PROCEDURE Translate_Dimension_Group
1220 ( p_commit IN VARCHAR2 := FND_API.G_FALSE
1221 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
1222 ,x_return_status OUT NOCOPY VARCHAR2
1223 ,x_msg_count OUT NOCOPY NUMBER
1224 ,x_msg_data OUT NOCOPY VARCHAR2
1225 )
1226 IS
1227 l_Dim_Grp_Rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
1228
1229 BEGIN
1230
1231 l_Dim_Grp_Rec := p_Dim_Grp_Rec;
1232 -- If the dimension group name in bsc is the same as that in pmf seeded dimensions, then use another name
1233 -- since name is used as a unique column in bsc_sys_dim_groups_tl. The name itself will not matter
1234 -- for PMF since short_name is used at all places. (bug# 3028436)
1235
1236 get_unique_dim_group_name(
1237 p_dim_group_name => p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name
1238 ,p_dim_group_short_name => p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Short_Name
1239 ,p_is_insert => 'N'
1240 ,p_counter => 0
1241 ,x_dim_group_name => l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name
1242 );
1243
1244 l_Dim_Grp_Rec.Bsc_Last_Update_Date := NVL(p_Dim_Grp_Rec.Bsc_Last_Update_Date, SYSDATE);
1245
1246 BSC_DIMENSION_GROUPS_PVT.Translate_Dimension_Group
1247 ( p_commit => p_Commit
1248 ,p_Dim_Grp_Rec => l_Dim_Grp_Rec
1249 ,x_return_status => x_return_status
1250 ,x_msg_count => x_msg_count
1251 ,x_msg_data => x_msg_data
1252 );
1253
1254 EXCEPTION
1255 WHEN FND_API.G_EXC_ERROR THEN
1256 x_return_status := FND_API.G_RET_STS_ERROR;
1257 FND_MSG_PUB.Count_And_Get(
1258 p_encoded => 'F'
1259 ,p_count => x_msg_count
1260 ,p_data => x_msg_data
1261 );
1262 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1263 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1264 FND_MSG_PUB.Count_And_Get(
1265 p_encoded => 'F'
1266 ,p_count => x_msg_count
1267 ,p_data => x_msg_data
1268 );
1269 WHEN OTHERS THEN
1270 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1271 IF (x_msg_data IS NOT NULL) THEN
1272 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.Translate_Dimension_Group ';
1273 ELSE
1274 x_msg_data := 'BSC_DIMENSION_GROUPS_PUB.Translate_Dimension_Group '||SQLERRM;
1275 END IF;
1276 END Translate_Dimension_Group;
1277
1278 --====================================================================
1279
1280 /*
1281 * Recursive function used to get unique name when
1282 * called from PMF for "All" enhancement.
1283 */
1284 PROCEDURE get_unique_dim_group_name(
1285 p_dim_group_name IN VARCHAR2
1286 ,p_dim_group_short_name IN VARCHAR2
1287 ,p_counter IN NUMBER
1288 ,p_is_insert IN VARCHAR2 := 'Y'
1289 ,x_dim_group_name OUT NOCOPY VARCHAR2
1290 )
1291 IS
1292 l_count NUMBER;
1293 l_counter NUMBER;
1294 l_dim_group_name bsc_sys_dim_groups_tl.name%TYPE;
1295 BEGIN
1296 l_dim_group_name := p_dim_group_name;
1297 l_counter := p_counter + 1;
1298
1299 IF (p_is_insert = 'Y') THEN
1300 SELECT count(dim_group_id)
1301 INTO l_count
1302 FROM bsc_sys_dim_groups_vl
1303 WHERE
1304 UPPER(name) = UPPER(p_dim_group_name);
1305 ELSE -- for update
1306 SELECT count(dim_group_id)
1307 INTO l_count
1308 FROM bsc_sys_dim_groups_vl
1309 WHERE
1310 UPPER(name) = UPPER(p_dim_group_name)
1311 AND
1312 UPPER(short_name) <> UPPER(p_dim_group_short_name);
1313 END IF;
1314 IF (l_count = 0) THEN
1315 x_dim_group_name := l_dim_group_name;
1316 ELSE
1317 get_unique_dim_group_name(
1318 p_dim_group_name => p_dim_group_name || ' (' || TO_CHAR(l_counter) || ')'
1319 ,p_dim_group_short_name => p_dim_group_short_name
1320 ,p_counter => l_counter
1321 ,p_is_insert => p_is_insert
1322 ,x_dim_group_name => x_dim_group_name
1323 );
1324 END IF;
1325
1326 EXCEPTION
1327 WHEN OTHERS THEN
1328 x_dim_group_name := l_dim_group_name;
1329 END;
1330
1331 --====================================================================
1332 /* This API is used only by PMF as of now for "All" enhancement
1333 * where the bisdimlv.ldt is uploaded to BSC data model.
1334 */
1335
1336 PROCEDURE load_dimension_group (
1337 p_commit IN VARCHAR2 := FND_API.G_FALSE
1338 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
1339 ,x_return_status OUT NOCOPY VARCHAR2
1340 ,x_msg_count OUT NOCOPY NUMBER
1341 ,x_msg_data OUT NOCOPY VARCHAR2
1342 ,p_force_mode IN BOOLEAN := FALSE
1343 )
1344 IS
1345 l_Bsc_Dim_Group_Rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
1346 l_is_insert VARCHAR2(10) := 'Y';
1347 l_count NUMBER;
1348 l_owner_name VARCHAR2(100);
1349 l_ret_code BOOLEAN;
1350 BEGIN
1351
1352 x_return_status := FND_API.G_RET_STS_SUCCESS;
1353 l_Bsc_Dim_Group_Rec.Bsc_Dim_Level_Group_Short_Name := p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Short_Name;
1354 l_Bsc_Dim_Group_Rec.Bsc_Created_By := p_Dim_Grp_Rec.Bsc_Created_By;
1355 l_Bsc_Dim_Group_Rec.Bsc_Last_Updated_By := p_Dim_Grp_Rec.Bsc_Last_Updated_By;
1356
1357 SELECT count(dim_group_id)
1358 INTO l_count
1359 FROM bsc_sys_dim_groups_vl
1360 WHERE
1361 short_name = l_Bsc_Dim_Group_Rec.Bsc_Dim_Level_Group_Short_Name;
1362
1363 IF (l_count > 0) THEN -- update
1364 l_is_insert := 'N';
1365 END IF;
1366
1367 -- If the dimension group name in bsc is the same as that in pmf seeded dimensions, then use another name
1368 -- since name is used as a unique column in bsc_sys_dim_groups_tl. The name itself will not matter
1369 -- for PMF since short_name is used at all places. (bug# 3028436)
1370
1371 get_unique_dim_group_name(
1372 p_dim_group_name => p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name
1373 ,p_dim_group_short_name => l_Bsc_Dim_Group_Rec.Bsc_Dim_Level_Group_Short_Name
1374 ,p_is_insert => l_is_insert
1375 ,p_counter => 0
1376 ,x_dim_group_name => l_Bsc_Dim_Group_Rec.Bsc_Dim_Level_Group_Name
1377 );
1378
1379
1380 l_Bsc_Dim_Group_Rec.Bsc_Last_Update_Date := NVL(p_Dim_Grp_Rec.Bsc_Last_Update_Date, SYSDATE);
1381
1382 IF (l_is_insert = 'N') THEN
1383
1384 l_owner_name := BIS_UTILITIES_PUB.Get_Owner_Name(p_Dim_Grp_Rec.Bsc_Last_Updated_By);
1385
1386 BIS_UTIL.Validate_For_Update (p_last_update_date => l_Bsc_Dim_Group_Rec.Bsc_Last_Update_Date
1387 ,p_owner => l_owner_name
1388 ,p_force_mode => p_force_mode
1389 ,p_table_name => 'BSC_SYS_DIM_GROUPS_VL'
1390 ,p_key_value => l_Bsc_Dim_Group_Rec.Bsc_Dim_Level_Group_Short_Name
1391 ,x_ret_code => l_ret_code
1392 ,x_return_status => x_return_status
1393 ,x_msg_data => x_msg_data
1394 );
1395 IF (l_ret_code) THEN
1396
1397 BSC_DIMENSION_GROUPS_PUB.Update_Dimension_Group(
1398 p_commit => FND_API.G_TRUE
1399 ,p_Dim_Grp_Rec => l_Bsc_Dim_Group_Rec
1400 ,p_create_Dim_Levels => FALSE
1401 ,x_return_status => x_return_status
1402 ,x_msg_count => x_msg_count
1403 ,x_msg_data => x_msg_data
1404 );
1405 END IF;
1406 ELSE
1407
1408 BSC_DIMENSION_GROUPS_PUB.Create_Dimension_Group(
1409 p_commit => FND_API.G_TRUE
1410 ,p_Dim_Grp_Rec => l_Bsc_Dim_Group_Rec
1411 ,p_create_Dim_Levels => FALSE
1412 ,x_return_status => x_return_status
1413 ,x_msg_count => x_msg_count
1414 ,x_msg_data => x_msg_data
1415 );
1416
1417 END IF;
1418
1419 EXCEPTION
1420 WHEN FND_API.G_EXC_ERROR THEN
1421 x_return_status := FND_API.G_RET_STS_ERROR;
1422 FND_MSG_PUB.Count_And_Get(
1423 p_encoded => 'F'
1424 ,p_count => x_msg_count
1425 ,p_data => x_msg_data
1426 );
1427 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1428 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1429 FND_MSG_PUB.Count_And_Get(
1430 p_encoded => 'F'
1431 ,p_count => x_msg_count
1432 ,p_data => x_msg_data
1433 );
1434 WHEN NO_DATA_FOUND THEN
1435 x_return_status := FND_API.G_RET_STS_ERROR;
1436 IF (x_msg_data IS NOT NULL) THEN
1437 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.load_dimension_group ';
1438 ELSE
1439 x_msg_data := 'BSC_DIMENSION_GROUPS_PUB.load_dimension_group '||SQLERRM;
1440 END IF;
1441 WHEN OTHERS THEN
1442 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1443 IF (x_msg_data IS NOT NULL) THEN
1444 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.load_dimension_group ';
1445 ELSE
1446 x_msg_data := 'BSC_DIMENSION_GROUPS_PUB.load_dimension_group '||SQLERRM;
1447 END IF;
1448
1449 END load_dimension_group;
1450
1451 --====================================================================
1452 /* This API is used only by PMF as of now for "All" enhancement
1453 * where the bisdimlv.ldt is uploaded to BSC data model.
1454 */
1455 PROCEDURE load_dim_levels_in_group(
1456 p_commit IN VARCHAR2 := FND_API.G_FALSE
1457 ,p_Bsc_Pmf_Dim_Rec IN BSC_PMF_UI_API_PUB.Bsc_Pmf_Dim_Rec_Type
1458 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
1459 ,x_return_status OUT NOCOPY VARCHAR2
1460 ,x_msg_count OUT NOCOPY NUMBER
1461 ,x_msg_data OUT NOCOPY VARCHAR2
1462 )
1463 IS
1464 l_rel_count NUMBER;
1465 l_Bsc_Dim_Group_Rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
1466 BEGIN
1467
1468 x_return_status := FND_API.G_RET_STS_SUCCESS;
1469 l_Bsc_Dim_Group_Rec := p_Dim_Grp_Rec;
1470
1471 SELECT dim_level_id
1472 INTO l_Bsc_Dim_Group_Rec.Bsc_Level_Id
1473 FROM bsc_sys_dim_levels_b
1474 WHERE short_name = p_Bsc_Pmf_Dim_Rec.Dimension_Level_Short_Name;
1475
1476 SELECT count(a.dim_level_id)
1477 INTO l_rel_count
1478 FROM
1479 bsc_sys_dim_levels_b a
1480 , bsc_sys_dim_groups_vl b
1481 , bsc_sys_dim_levels_by_group c
1482 WHERE
1483 a.short_name = p_Bsc_Pmf_Dim_Rec.Dimension_Level_Short_Name
1484 and b.short_name = l_Bsc_Dim_Group_Rec.Bsc_Dim_Level_Group_Short_Name
1485 and a.dim_level_id = c.dim_level_id
1486 and b.dim_group_id = c.dim_group_id;
1487
1488 IF (l_rel_count = 0) THEN
1489 -- no values in group-level relationship table; hence insert
1490 Create_Dim_Levels_In_Group(
1491 p_commit => p_Commit
1492 ,p_Dim_Grp_Rec => l_Bsc_Dim_Group_Rec
1493 ,x_return_status => x_return_status
1494 ,x_msg_count => x_msg_count
1495 ,x_msg_data => x_msg_data
1496 );
1497
1498 ELSE
1499 -- since group-level relationship is present, update
1500 Update_Dim_Levels_In_Group(
1501 p_commit => p_Commit
1502 ,p_Dim_Grp_Rec => l_Bsc_Dim_Group_Rec
1503 ,x_return_status => x_return_status
1504 ,x_msg_count => x_msg_count
1505 ,x_msg_data => x_msg_data
1506 );
1507
1508 END IF;
1509
1510 EXCEPTION
1511 WHEN FND_API.G_EXC_ERROR THEN
1512 x_return_status := FND_API.G_RET_STS_ERROR;
1513 FND_MSG_PUB.Count_And_Get
1514 ( p_encoded => 'F'
1515 , p_count => x_msg_count
1516 , p_data => x_msg_data
1517 );
1518 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1519 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1520 FND_MSG_PUB.Count_And_Get
1521 ( p_encoded => 'F'
1522 , p_count => x_msg_count
1523 , p_data => x_msg_data
1524 );
1525 WHEN NO_DATA_FOUND THEN
1526 x_return_status := FND_API.G_RET_STS_ERROR;
1527 IF (x_msg_data IS NOT NULL) THEN
1528 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.load_dim_levels_in_group ';
1529 ELSE
1530 x_msg_data := 'BSC_DIMENSION_GROUPS_PUB.load_dim_levels_in_group '||SQLERRM;
1531 END IF;
1532 WHEN OTHERS THEN
1533 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1534 IF (x_msg_data IS NOT NULL) THEN
1535 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.load_dim_levels_in_group ';
1536 ELSE
1537 x_msg_data := 'BSC_DIMENSION_GROUPS_PUB.load_dim_levels_in_group '||SQLERRM;
1538 END IF;
1539 END load_dim_levels_in_group;
1540
1541 --=============================================================================
1542 PROCEDURE ret_dimgrpid_fr_shname (
1543 p_dim_short_name IN VARCHAR2
1544 ,x_dim_grp_id OUT NOCOPY VARCHAR2
1545 ,x_return_status OUT NOCOPY VARCHAR2
1546 ,x_msg_count OUT NOCOPY NUMBER
1547 ,x_msg_data OUT NOCOPY VARCHAR2
1548 ) IS
1549
1550 CURSOR c_dim_grp_id (cp_short_name VARCHAR2) IS
1551 SELECT dim_group_id
1552 FROM bsc_sys_dim_groups_vl
1553 WHERE short_name = cp_short_name;
1554
1555 BEGIN
1556
1557 x_return_status := FND_API.G_RET_STS_SUCCESS;
1558
1559 IF (c_dim_grp_id%ISOPEN) THEN
1560 CLOSE c_dim_grp_id;
1561 END IF;
1562
1563 OPEN c_dim_grp_id (cp_short_name => p_dim_short_name);
1564 FETCH c_dim_grp_id INTO x_dim_grp_id;
1565 CLOSE c_dim_grp_id;
1566
1567 EXCEPTION
1568 WHEN NO_DATA_FOUND THEN
1569 IF (c_dim_grp_id%ISOPEN) THEN
1570 CLOSE c_dim_grp_id;
1571 END IF;
1572 x_return_status := FND_API.G_RET_STS_ERROR ;
1573 IF (x_msg_data IS NOT NULL) THEN
1574 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.ret_dimgrpid_fr_shname ';
1575 ELSE
1576 x_msg_data := 'BSC_DIMENSION_GROUPS_PUB.ret_dimgrpid_fr_shname '||SQLERRM;
1577 END IF;
1578 WHEN FND_API.G_EXC_ERROR THEN
1579 IF (c_dim_grp_id%ISOPEN) THEN
1580 CLOSE c_dim_grp_id;
1581 END IF;
1582 x_return_status := FND_API.G_RET_STS_ERROR ;
1583 FND_MSG_PUB.Count_And_Get(
1584 p_encoded => 'F'
1585 ,p_count => x_msg_count
1586 ,p_data => x_msg_data
1587 );
1588 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1589 IF (c_dim_grp_id%ISOPEN) THEN
1590 CLOSE c_dim_grp_id;
1591 END IF;
1592 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1593 FND_MSG_PUB.Count_And_Get(
1594 p_encoded => 'F'
1595 ,p_count => x_msg_count
1596 ,p_data => x_msg_data
1597 );
1598 WHEN OTHERS THEN
1599 IF (c_dim_grp_id%ISOPEN) THEN
1600 CLOSE c_dim_grp_id;
1601 END IF;
1602 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1603 IF (x_msg_data IS NOT NULL) THEN
1604 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.ret_dimgrpid_fr_shname ';
1605 ELSE
1606 x_msg_data := 'BSC_DIMENSION_GROUPS_PUB.ret_dimgrpid_fr_shname '||SQLERRM;
1607 END IF;
1608
1609 END ret_dimgrpid_fr_shname;
1610
1611
1612 /*************************************************************************************
1613
1614 API TO SYNC UP THE DIMENSION GROUPS LANGUAGE DATA FROM PMF TO BSC
1615
1616 *************************************************************************************/
1617
1618 procedure Translate_Dim_By_Given_Lang
1619 ( p_commit IN VARCHAR2
1620 , p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
1621 , x_return_status OUT NOCOPY VARCHAR2
1622 , x_msg_count OUT NOCOPY NUMBER
1623 , x_msg_data OUT NOCOPY VARCHAR2
1624 )
1625 IS
1626
1627 BEGIN
1628 BSC_DIMENSION_GROUPS_PVT.Translate_Dim_By_Given_Lang
1629 (
1630 p_commit => FND_API.G_FALSE
1631 , p_Dim_Grp_Rec => p_Dim_Grp_Rec
1632 , x_return_status => x_return_status
1633 , x_msg_count => x_msg_count
1634 , x_msg_data => x_msg_data
1635 );
1636
1637 EXCEPTION
1638 WHEN FND_API.G_EXC_ERROR THEN
1639 x_return_status := FND_API.G_RET_STS_ERROR;
1640 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1641 ,p_data => x_msg_data);
1642 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1643 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1644 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1645 ,p_data => x_msg_data);
1646 WHEN NO_DATA_FOUND THEN
1647 x_return_status := FND_API.G_RET_STS_ERROR;
1648 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1649 ,p_data => x_msg_data);
1650 WHEN OTHERS THEN
1651 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1652 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1653 ,p_data => x_msg_data);
1654 END Translate_Dim_By_Given_Lang;
1655
1656
1657
1658 end BSC_DIMENSION_GROUPS_PUB;