1 package body BSC_DIMENSION_GROUPS_PVT as
2 /* $Header: BSCVDMGB.pls 120.0 2005/06/01 14:37:31 appldev noship $ */
3 /*
4 +======================================================================================+
5 | Copyright (c) 2001 Oracle Corporation, Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +======================================================================================+
8 | FILENAME |
9 | BSCVDMGB.pls |
10 | |
11 | Creation Date: |
12 | October 9, 2001 |
13 | |
14 | Creator: |
15 | Mario-Jair Campos |
16 | |
17 | Description: |
18 | Private Body version. |
19 | This package creates a Dimension Group. |
20 | |
21 | History: |
22 | 04-MAR-2003 PAJOHRI MLS Bug #2721899 |
23 | Changed BSC_SYS_DIM_GROUPS_TL to BSC_SYS_DIM_GROUPS_VL in |
24 | select query. |
25 | 29-MAY-03 All Enhancement Phase I- Functions user group short_name if no id |
26 | 07-JUN-03 mahrao Modified for ALL enhancement |
27 | 13-JUN-03 ADEULGAO modified for BUG# 2878840 |
28 | 13-JUN-03 ADEULGAO modified procedure Create_Dimension_Group for BUG# 2878840 |
29 | 14-JUN-03 mahrao Added Translate_dimesnsion_group procedure |
30 | 17-JUL-03 mahrao Modified Translate_dimesnsion_group procedure |
31 | as part of forward porting of ALL enhancement to BSC 5.1 |
32 | Modified Retrieve_Dim_Group procedure |
33 | as part of forward porting of ALL enhancement to BSC 5.1 |
34 | 29-OCT-2003 mahrao bug#3209967 Added a column to bsc_sys_dim_levels_by_group |
35 | 17-NOV-2003 PAJOHRI Bug #3232366 |
36 | 17-NOV-2003 ADRAO Bug #3236356 - Removed comments which has Validate_Value() |
37 | 30-Jul-04 rpenneru Modified for enhancemen#3748519 |
38 | 13-Oct-04 rpenneru Modified for bug#3945655 |
39 | 21-DEC-04 vtulasi Modified for bug#4045278 - Addtion of LUD |
40 | 01-FEB-05 hengliu Modified for bug#4104065 - WHERE_CLAUSE can be null |
41 +======================================================================================+
42 */
43 G_PKG_NAME CONSTANT varchar2(30) := 'BSC_DIMENSION_GROUPS_PVT';
44 g_db_object varchar2(30) := null;
45
46 --
47 /**********************************************************************************/
48 FUNCTION Validate_Dim_Group_Id(
49 p_dim_group_id IN NUMBER
50 ) RETURN NUMBER
51 IS
52 l_count NUMBER := 0;
53 BEGIN
54
55 SELECT count(dim_group_id)
56 INTO l_count
57 FROM BSC_SYS_DIM_GROUPS_VL
58 WHERE dim_group_id = p_dim_group_id;
59
60 RETURN l_count;
61 EXCEPTION
62 WHEN OTHERS THEN
63 RETURN l_count;
64 END Validate_Dim_Group_Id;
65 /**********************************************************************************/
66 procedure Retrieve_Dim_Group(
67 p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
68 ,x_Dim_Grp_Rec OUT NOCOPY BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
69 ,x_return_status OUT NOCOPY VARCHAR2
70 ,x_msg_count OUT NOCOPY NUMBER
71 ,x_msg_data OUT NOCOPY VARCHAR2
72 );
73 --: This procedure is used to Create a Dimension Group. This is the entry point
74 --: for the API for the Dimension Group entity.
75 --: This procedure is part of the Dimension Group API.
76
77 procedure Create_Dimension_Group(
78 p_commit IN varchar2 := FND_API.G_FALSE
79 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
80 ,x_return_status OUT NOCOPY varchar2
81 ,x_msg_count OUT NOCOPY number
82 ,x_msg_data OUT NOCOPY varchar2
83 ) is
84
85 l_Dim_Grp_Rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
86
87 l_count number;
88 l_dim_group_short_name varchar2(40);
89 l_dim_level_short_name varchar2(30);
90
91 begin
92 SAVEPOINT CreateBSCDimGrpPVT;
93 FND_MSG_PUB.Initialize;
94 x_return_status := FND_API.G_RET_STS_SUCCESS;
95 -- Validate Group Id does not exist.
96 if p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id is not null then
97 l_count := BSC_DIMENSION_GROUPS_PVT.Validate_Dim_Group_Id(p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id);
98 if l_count <> 0 then
99 FND_MESSAGE.SET_NAME('BSC','BSC_GROUP_ID_EXISTS');
100 FND_MESSAGE.SET_TOKEN('BSC_GROUP_ID', p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id);
101 FND_MSG_PUB.ADD;
102 RAISE FND_API.G_EXC_ERROR;
103 end if;
104 else
105 FND_MESSAGE.SET_NAME('BSC','BSC_NO_GROUP_ID_ENTERED');
106 FND_MESSAGE.SET_TOKEN('BSC_GROUP_ID', p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id);
107 FND_MSG_PUB.ADD;
108 RAISE FND_API.G_EXC_ERROR;
109 end if;
110
111 -- Assign all values in the passed "Record" parameter to the locally defined
112 -- "Record" variable.
113 l_Dim_Grp_Rec := p_Dim_Grp_Rec;
114
115 -- Validate if this Group already exists, if it does not then create it and assign
116 -- the current dimension to the group, if the group already exists then just assign
117 -- the dimension level to it.
118 --if BSC_DIMENSION_LEVELS_PVT.Validate_Dim_Group(l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name) < 1 then
119
120 -- Get the next ID value for the current group.
121 -- Bug#2878840
122
123 g_db_object := 'BSC_SYS_DIM_GROUPS_TL';
124
125 -- PMD
126 if l_Dim_Grp_Rec.Bsc_Created_By is null then
127 l_Dim_Grp_Rec.Bsc_Created_By := 0;
128 end if;
129
130 if l_Dim_Grp_Rec.Bsc_Last_Updated_By is null then
131 l_Dim_Grp_Rec.Bsc_Last_Updated_By := 0;
132 end if;
133 -- PMD
134
135 l_Dim_Grp_Rec.Bsc_Last_Update_Date := NVL(p_Dim_Grp_Rec.Bsc_Last_Update_Date, SYSDATE);
136 -- Insert pertaining values into table bsc_sys_dim_groups_tl.
137 insert into BSC_SYS_DIM_GROUPS_TL( dim_group_id
138 ,language
139 ,source_lang
140 ,name
141 ,short_name
142 ,created_by
143 ,creation_date
144 ,last_updated_by
145 ,last_update_date
146 ,last_update_login)
147 select l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
148 ,L.LANGUAGE_CODE
149 ,userenv('LANG')
150 ,l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name
151 ,l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Short_Name
152 ,l_Dim_Grp_Rec.Bsc_Created_By -- PMD
153 ,l_Dim_Grp_Rec.Bsc_Last_Update_Date -- PMD
154 ,l_Dim_Grp_Rec.Bsc_Last_Updated_By -- PMD
155 ,l_Dim_Grp_Rec.Bsc_Last_Update_Date -- PMD
156 ,l_Dim_Grp_Rec.Bsc_Last_Update_Login -- PMD
157 from FND_LANGUAGES L
158 where L.INSTALLED_FLAG in ('I', 'B')
159 and not exists
160 (select NULL
161 from BSC_SYS_DIM_GROUPS_TL T
162 where T.dim_group_id = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
163 and T.LANGUAGE = L.LANGUAGE_CODE);
164
165 if (p_commit = FND_API.G_TRUE) then
166 commit;
167 end if;
168
169 --end if;
170
171 EXCEPTION
172 WHEN FND_API.G_EXC_ERROR THEN
173 ROLLBACK TO CreateBSCDimGrpPVT;
174 FND_MSG_PUB.Count_And_Get
175 ( p_encoded => 'F'
176 , p_count => x_msg_count
177 , p_data => x_msg_data
178 );
179 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
180 x_return_status := FND_API.G_RET_STS_ERROR;
181 RAISE;
182 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
183 ROLLBACK TO CreateBSCDimGrpPVT;
184 FND_MSG_PUB.Count_And_Get
185 ( p_encoded => 'F'
186 , p_count => x_msg_count
187 , p_data => x_msg_data
188 );
189 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
190 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
191 RAISE;
192 WHEN NO_DATA_FOUND THEN
193 ROLLBACK TO CreateBSCDimGrpPVT;
194 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
195 IF (x_msg_data IS NOT NULL) THEN
196 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PVT.Create_Dimension_Group ';
197 ELSE
198 x_msg_data := 'BSC_DIMENSION_GROUPS_PVT.Create_Dimension_Group '||SQLERRM;
199 END IF;
200 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
201 RAISE;
202 WHEN OTHERS THEN
203 ROLLBACK TO CreateBSCDimGrpPVT;
204 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
205 if (SQLCODE = -01400) then
206 FND_MESSAGE.SET_NAME('BSC','BSC_TABLE_NULL_VALUE');
207 FND_MESSAGE.SET_TOKEN('BSC_OBJECT', g_db_object);
208 FND_MSG_PUB.ADD;
209 RAISE FND_API.G_EXC_ERROR;
210 end if;
211 IF (x_msg_data IS NOT NULL) THEN
212 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PVT.Create_Dimension_Group ';
213 ELSE
214 x_msg_data := 'BSC_DIMENSION_GROUPS_PVT.Create_Dimension_Group '||SQLERRM;
215 END IF;
216 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
217 RAISE;
218 end Create_Dimension_Group;
219
220 /************************************************************************************
221 ************************************************************************************/
222
223 procedure Retrieve_Dimension_Group(
224 p_commit IN varchar2 := FND_API.G_FALSE
225 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
226 ,x_Dim_Grp_Rec IN OUT NOCOPY BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
227 ,x_return_status OUT NOCOPY varchar2
228 ,x_msg_count OUT NOCOPY number
229 ,x_msg_data OUT NOCOPY varchar2
230 ) is
231
232 begin
233
234 g_db_object := 'Retrieve_Dimension_Group';
235 FND_MSG_PUB.Initialize;
236 x_return_status := FND_API.G_RET_STS_SUCCESS;
237 select distinct name
238 into x_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name
239 from BSC_SYS_DIM_GROUPS_VL
240 where dim_group_id = p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id;
241
242 if (p_commit = FND_API.G_TRUE) then
243 commit;
244 end if;
245
246 EXCEPTION
247 WHEN FND_API.G_EXC_ERROR THEN
248 FND_MSG_PUB.Count_And_Get
249 ( p_encoded => 'F'
250 , p_count => x_msg_count
251 , p_data => x_msg_data
252 );
253 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
254 x_return_status := FND_API.G_RET_STS_ERROR;
255 RAISE;
256 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
257 FND_MSG_PUB.Count_And_Get
258 ( p_encoded => 'F'
259 , p_count => x_msg_count
260 , p_data => x_msg_data
261 );
262 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
263 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
264 RAISE;
265 WHEN NO_DATA_FOUND THEN
266 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
267 IF (x_msg_data IS NOT NULL) THEN
268 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PVT.Retrieve_Dimension_Group ';
269 ELSE
270 x_msg_data := 'BSC_DIMENSION_GROUPS_PVT.Retrieve_Dimension_Group '||SQLERRM;
271 END IF;
272 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
273 RAISE;
274 WHEN OTHERS THEN
275 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
276 IF (x_msg_data IS NOT NULL) THEN
277 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PVT.Retrieve_Dimension_Group ';
278 ELSE
279 x_msg_data := 'BSC_DIMENSION_GROUPS_PVT.Retrieve_Dimension_Group '||SQLERRM;
280 END IF;
281 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
282 RAISE;
283 end Retrieve_Dimension_Group;
284
285 /************************************************************************************
286 ************************************************************************************/
287
288 procedure Update_Dimension_Group(
289 p_commit IN varchar2 := FND_API.G_FALSE
290 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
291 ,x_return_status OUT NOCOPY varchar2
292 ,x_msg_count OUT NOCOPY number
293 ,x_msg_data OUT NOCOPY varchar2
294 ) is
295
296 l_count number;
297 l_Dim_Grp_Rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
298
299 CURSOR c_dim_lvls_by_group (p_grp_short_name VARCHAR2)
300 IS
301 SELECT dim_group_id
302 FROM BSC_SYS_DIM_GROUPS_TL
303 WHERE SHORT_NAME = p_grp_short_name;
304
305 begin
306 SAVEPOINT UpdateBSCDimGrpPVT;
307 FND_MSG_PUB.Initialize;
308 x_return_status := FND_API.G_RET_STS_SUCCESS;
309 -- Assign all values in the passed "Record" parameter to the locally defined
310 -- "Record" variable.
311 l_Dim_Grp_Rec := p_Dim_Grp_Rec;
312
313 -- Check that valid dimension group id was entered.
314 if p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id is not null then
315 l_count := BSC_DIMENSION_GROUPS_PVT.Validate_Dim_Group_Id(p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id);
316 if l_count = 0 then
317 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_GROUP_ID');
318 FND_MESSAGE.SET_TOKEN('BSC_GROUP_ID', p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id);
319 FND_MSG_PUB.ADD;
320 RAISE FND_API.G_EXC_ERROR;
321 end if;
322 -- if id is null then check for short name name is not null
323 elsif (l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Short_Name is not null) then
324 IF (c_dim_lvls_by_group%ISOPEN) THEN
325 CLOSE c_dim_lvls_by_group;
326 END IF;
327 OPEN c_dim_lvls_by_group(l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Short_Name);
328 FETCH c_dim_lvls_by_group INTO l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id;
329 IF (c_dim_lvls_by_group%NOTFOUND) THEN
330 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_GROUP_SHORT_NAME');
331 FND_MESSAGE.SET_TOKEN('BSC_GROUP_SHORT_NAME',l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Short_Name);
332 FND_MSG_PUB.ADD;
333 RAISE FND_API.G_EXC_ERROR;
334 END IF;
335 CLOSE c_dim_lvls_by_group;
336 else
337 FND_MESSAGE.SET_NAME('BSC','BSC_NO_GROUP_ID_ENTERED');
338 FND_MESSAGE.SET_TOKEN('BSC_GROUP_ID', l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id);
339 FND_MSG_PUB.ADD;
340 RAISE FND_API.G_EXC_ERROR;
341 end if;
342
343 -- In this case there is only one column that may be updated, therefore there is no
344 -- retrieval of previous values.
345
346 -- For PMD, we need to update the WHO Columns appropriately
347 -- PMD
348 IF p_Dim_Grp_Rec.Bsc_Last_Updated_By IS NULL THEN -- Cannot update p_Dim_Grp_Rec
349 l_Dim_Grp_Rec.Bsc_Last_Updated_By := FND_GLOBAL.USER_ID;
350 END IF;
351
352 IF p_Dim_Grp_Rec.Bsc_Last_Update_Login IS NULL THEN
353 l_Dim_Grp_Rec.Bsc_Last_Update_Login := FND_GLOBAL.LOGIN_ID;
354 END IF;
355 -- PMD
356
357 l_Dim_Grp_Rec.Bsc_Last_Update_Date := NVL(p_Dim_Grp_Rec.Bsc_Last_Update_Date, SYSDATE);
358 IF (p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name IS NOT NULL) THEN
359 update BSC_SYS_DIM_GROUPS_TL
360 set name = p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name
361 ,source_lang = userenv('LANG')
362 ,last_updated_by = l_Dim_Grp_Rec.Bsc_Last_Updated_By
363 ,last_update_date = l_Dim_Grp_Rec.Bsc_Last_Update_Date
364 ,last_update_login = p_Dim_Grp_Rec.Bsc_Last_Update_Login
365 where dim_group_id = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
366 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
367 END IF;
368 if (p_commit = FND_API.G_TRUE) then
369 commit;
370 end if;
371
372 EXCEPTION
373 WHEN FND_API.G_EXC_ERROR THEN
374 IF (c_dim_lvls_by_group%ISOPEN) THEN
375 CLOSE c_dim_lvls_by_group;
376 END IF;
377 ROLLBACK TO UpdateBSCDimGrpPVT;
378 FND_MSG_PUB.Count_And_Get
379 ( p_encoded => 'F'
380 , p_count => x_msg_count
381 , p_data => x_msg_data
382 );
383 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
384 x_return_status := FND_API.G_RET_STS_ERROR;
385 RAISE;
386 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
387 IF (c_dim_lvls_by_group%ISOPEN) THEN
388 CLOSE c_dim_lvls_by_group;
389 END IF;
390 ROLLBACK TO UpdateBSCDimGrpPVT;
391 FND_MSG_PUB.Count_And_Get
392 ( p_encoded => 'F'
393 , p_count => x_msg_count
394 , p_data => x_msg_data
395 );
396 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
397 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
398 RAISE;
399 WHEN NO_DATA_FOUND THEN
400 IF (c_dim_lvls_by_group%ISOPEN) THEN
401 CLOSE c_dim_lvls_by_group;
402 END IF;
403 ROLLBACK TO UpdateBSCDimGrpPVT;
404 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
405 IF (x_msg_data IS NOT NULL) THEN
406 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PVT.Update_Dimension_Group ';
407 ELSE
408 x_msg_data := 'BSC_DIMENSION_GROUPS_PVT.Update_Dimension_Group '||SQLERRM;
409 END IF;
410 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
411 RAISE;
412 WHEN OTHERS THEN
413 IF (c_dim_lvls_by_group%ISOPEN) THEN
414 CLOSE c_dim_lvls_by_group;
415 END IF;
416 ROLLBACK TO UpdateBSCDimGrpPVT;
417 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
418 IF (x_msg_data IS NOT NULL) THEN
419 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PVT.Update_Dimension_Group ';
420 ELSE
421 x_msg_data := 'BSC_DIMENSION_GROUPS_PVT.Update_Dimension_Group '||SQLERRM;
422 END IF;
423 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
424 RAISE;
425 end Update_Dimension_Group;
426
427 /************************************************************************************
428 ************************************************************************************/
429
430 procedure Delete_Dimension_Group(
431 p_commit IN varchar2 := FND_API.G_FALSE
432 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
433 ,x_return_status OUT NOCOPY varchar2
434 ,x_msg_count OUT NOCOPY number
435 ,x_msg_data OUT NOCOPY varchar2
436 ) is
437
438 l_Dim_Grp_Rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
439
440 l_count number;
441
442 CURSOR c_dim_lvls_by_group (p_grp_short_name VARCHAR2)
443 IS
444 SELECT dim_group_id
445 FROM BSC_SYS_DIM_GROUPS_TL
446 WHERE SHORT_NAME = p_grp_short_name;
447
448 begin
449 SAVEPOINT DeleteBSCDimGrpPVT;
450 FND_MSG_PUB.Initialize;
451 x_return_status := FND_API.G_RET_STS_SUCCESS;
452 -- Assign all values in the passed "Record" parameter to the locally defined
453 -- "Record" variable.
454 l_Dim_Grp_Rec := p_Dim_Grp_Rec;
455
456 if l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id is not null then
457 l_count := BSC_DIMENSION_GROUPS_PVT.Validate_Dim_Group_Id(l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id);
458 if l_count = 0 then
459 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_GROUP_ID');
460 FND_MESSAGE.SET_TOKEN('BSC_GROUP_ID', l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id);
461 FND_MSG_PUB.ADD;
462 RAISE FND_API.G_EXC_ERROR;
463 end if;
464 -- if id is null then check that short name is not null
465 elsif (l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Short_Name is not null) then
466 IF (c_dim_lvls_by_group%ISOPEN) THEN
467 CLOSE c_dim_lvls_by_group;
468 END IF;
469 OPEN c_dim_lvls_by_group(l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Short_Name);
470 FETCH c_dim_lvls_by_group INTO l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id;
471 IF (c_dim_lvls_by_group%NOTFOUND) THEN
472 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_GROUP_SHORT_NAME');
473 FND_MESSAGE.SET_TOKEN('BSC_GROUP_SHORT_NAME',l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Short_Name);
474 FND_MSG_PUB.ADD;
475 RAISE FND_API.G_EXC_ERROR;
476 END IF;
477 CLOSE c_dim_lvls_by_group;
478 else
479 -- if id and shortname both are null then check that name is not null
480 if l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name is not null then
481 select count(dim_group_id)
482 into l_count
483 from BSC_SYS_DIM_GROUPS_TL
484 where name = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name;
485 if l_count = 0 then
486 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_GROUP_NAME');
487 FND_MESSAGE.SET_TOKEN('BSC_GROUP_NAME', l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name);
488 FND_MSG_PUB.ADD;
489 RAISE FND_API.G_EXC_ERROR;
490 else -- get id for this name
491 select distinct dim_group_id
492 into l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
493 from BSC_SYS_DIM_GROUPS_VL
494 where name = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name;
495 end if;
496 else
497 FND_MESSAGE.SET_NAME('BSC','BSC_NO_GROUP_NAME_ENTERED');
498 FND_MESSAGE.SET_TOKEN('BSC_NO_GROUP', l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name);
499 FND_MSG_PUB.ADD;
500 RAISE FND_API.G_EXC_ERROR;
501 end if;
502 end if;
503
504 -- If there are no more dimensions assigned to this group then the group
505 -- can be deleted.
506 select count(dim_group_id)
507 into l_count
508 from BSC_SYS_DIM_LEVELS_BY_GROUP
509 where dim_group_id = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id;
510 if l_count = 0 then
511 delete from BSC_SYS_DIM_GROUPS_TL
512 where dim_group_id = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id;
513 end if;
514
515 if (p_commit = FND_API.G_TRUE) then
516 commit;
517 end if;
518
519 EXCEPTION
520 WHEN FND_API.G_EXC_ERROR THEN
521 IF (c_dim_lvls_by_group%ISOPEN) THEN
522 CLOSE c_dim_lvls_by_group;
523 END IF;
524 ROLLBACK TO DeleteBSCDimGrpPVT;
525 FND_MSG_PUB.Count_And_Get
526 ( p_encoded => 'F'
527 , p_count => x_msg_count
528 , p_data => x_msg_data
529 );
530 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
531 x_return_status := FND_API.G_RET_STS_ERROR;
532 RAISE;
533 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
534 IF (c_dim_lvls_by_group%ISOPEN) THEN
535 CLOSE c_dim_lvls_by_group;
536 END IF;
537 ROLLBACK TO DeleteBSCDimGrpPVT;
538 FND_MSG_PUB.Count_And_Get
539 ( p_encoded => 'F'
540 , p_count => x_msg_count
541 , p_data => x_msg_data
542 );
543 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
544 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
545 RAISE;
546 WHEN NO_DATA_FOUND THEN
547 IF (c_dim_lvls_by_group%ISOPEN) THEN
548 CLOSE c_dim_lvls_by_group;
549 END IF;
550 ROLLBACK TO DeleteBSCDimGrpPVT;
551 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
552 IF (x_msg_data IS NOT NULL) THEN
553 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PVT.Delete_Dimension_Group ';
554 ELSE
555 x_msg_data := 'BSC_DIMENSION_GROUPS_PVT.Delete_Dimension_Group '||SQLERRM;
556 END IF;
557 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
558 RAISE;
559 WHEN OTHERS THEN
560 IF (c_dim_lvls_by_group%ISOPEN) THEN
561 CLOSE c_dim_lvls_by_group;
562 END IF;
563 ROLLBACK TO DeleteBSCDimGrpPVT;
564 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
565 IF (x_msg_data IS NOT NULL) THEN
566 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PVT.Delete_Dimension_Group ';
567 ELSE
568 x_msg_data := 'BSC_DIMENSION_GROUPS_PVT.Delete_Dimension_Group '||SQLERRM;
569 END IF;
570 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
571 RAISE;
572 end Delete_Dimension_Group;
573
574 /************************************************************************************
575 ************************************************************************************/
576
577 --: This procedure assigns the dimension to the dimension group.
578 --: This procedure is part of the Dimension Group API.
579
580 procedure Create_Dim_Levels_In_Group(
581 p_commit IN varchar2 := FND_API.G_FALSE
582 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
583 ,x_return_status OUT NOCOPY varchar2
584 ,x_msg_count OUT NOCOPY number
585 ,x_msg_data OUT NOCOPY varchar2
586 ) is
587
588 l_Dim_Grp_Rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
589
590 l_count number;
591
592 CURSOR c_dim_lvls_by_group (p_grp_short_name VARCHAR2)
593 IS
594 SELECT dim_group_id
595 FROM BSC_SYS_DIM_GROUPS_TL
596 WHERE SHORT_NAME = p_grp_short_name;
597
598 begin
599 SAVEPOINT CreateBSCDimLevInGrpPVT;
600 FND_MSG_PUB.Initialize;
601 x_return_status := FND_API.G_RET_STS_SUCCESS;
602 -- Assign all values in the passed "Record" parameter to the locally defined
603 -- "Record" variable.
604 l_Dim_Grp_Rec := p_Dim_Grp_Rec;
605
606 -- Check that the group id is valid or that the name is valid.
607 if l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id is not null then
608 l_count := BSC_DIMENSION_GROUPS_PVT.Validate_Dim_Group_Id(l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id);
609 if l_count = 0 then
610 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_GROUP_ID');
611 FND_MESSAGE.SET_TOKEN('BSC_GROUP_ID', l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id);
612 FND_MSG_PUB.ADD;
613 RAISE FND_API.G_EXC_ERROR;
614 end if;
615 -- if id is null then check that short name is not null
616 elsif l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Short_Name is not null then
617 IF (c_dim_lvls_by_group%ISOPEN) THEN
618 CLOSE c_dim_lvls_by_group;
619 END IF;
620 OPEN c_dim_lvls_by_group(l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Short_Name);
621 FETCH c_dim_lvls_by_group INTO l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id;
622 IF (c_dim_lvls_by_group%NOTFOUND) THEN
623 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_GROUP_SHORT_NAME');
624 FND_MESSAGE.SET_TOKEN('BSC_GROUP_SHORT_NAME',l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Short_Name);
625 FND_MSG_PUB.ADD;
626 RAISE FND_API.G_EXC_ERROR;
627 END IF;
628 CLOSE c_dim_lvls_by_group;
629 else
630 -- if id and short name is null then check that name is not null
631 if l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name is not null then
632 select count(dim_group_id)
633 into l_count
634 from BSC_SYS_DIM_GROUPS_TL
635 where name = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name;
636 if l_count = 0 then
637 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_GROUP_NAME');
638 FND_MESSAGE.SET_TOKEN('BSC_GROUP_NAME', l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name);
639 FND_MSG_PUB.ADD;
640 RAISE FND_API.G_EXC_ERROR;
641 else -- get id for this name
642 select distinct dim_group_id
643 into l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
644 from BSC_SYS_DIM_GROUPS_VL
645 where name = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name;
646 end if;
647 else
648 FND_MESSAGE.SET_NAME('BSC','BSC_NO_GROUP_NAME_ENTERED');
649 FND_MESSAGE.SET_TOKEN('BSC_NO_GROUP', l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name);
650 FND_MSG_PUB.ADD;
651 RAISE FND_API.G_EXC_ERROR;
652 end if;
653 end if;
654
655 -- Check Dimension level id is valid.
656 if l_Dim_Grp_Rec.Bsc_Level_Id is not null then
657 l_count := BSC_DIMENSION_LEVELS_PVT.Validate_Dim_Level_Id(l_Dim_Grp_Rec.Bsc_Level_Id);
658 if l_count = 0 then
659 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_LEVEL_ID');
660 FND_MESSAGE.SET_TOKEN('BSC_LEVEL_ID', l_Dim_Grp_Rec.Bsc_Level_Id);
661 FND_MSG_PUB.ADD;
662 RAISE FND_API.G_EXC_ERROR;
663 end if;
664 else
665 FND_MESSAGE.SET_NAME('BSC','BSC_NO_LEVEL_ID_ENTERED');
666 FND_MESSAGE.SET_TOKEN('BSC_LEVEL_ID', l_Dim_Grp_Rec.Bsc_Level_Id);
667 FND_MSG_PUB.ADD;
668 RAISE FND_API.G_EXC_ERROR;
669 end if;
670
671
672
673 -- Determine if dimension is already part of the dimension group.
674 select count(*)
675 into l_count
676 from BSC_SYS_DIM_LEVELS_BY_GROUP
677 where dim_group_id = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
678 and dim_level_id = l_Dim_Grp_Rec.Bsc_Level_Id;
679
680 -- If the dimension does not belong to the group yet assign it.
681 if l_count = 0 then
682
683 -- Get the number of dimensions in the dimension group and add to it one.
684 -- This is used for the index for the dimension being added.
685 IF (l_Dim_Grp_Rec.Bsc_Dim_Level_Index IS NULL) THEN
686 select NVL((MAX(dim_level_index) + 1), 0)
687 into l_Dim_Grp_Rec.Bsc_Dim_Level_Index
688 from BSC_SYS_DIM_LEVELS_BY_GROUP
689 where dim_group_id = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id;
690 END IF;
691
692 g_db_object := 'BSC_SYS_DIM_LEVELS_BY_GROUP';
693
694 -- Insert pertaining values into table bsc_sys_dim_levels_by_group.
695 --Reminder: Hard coded values, need to get the source.
696 insert into BSC_SYS_DIM_LEVELS_BY_GROUP( dim_group_id
697 ,dim_level_id
698 ,dim_level_index
699 ,total_flag
700 ,comparison_flag
701 ,filter_column
702 ,filter_value
703 ,default_value
704 ,default_type
705 ,parent_in_total
706 ,no_items
707 ,where_clause)
708 values( l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
709 ,l_Dim_Grp_Rec.Bsc_Level_Id
710 ,l_Dim_Grp_Rec.Bsc_Dim_Level_Index
711 ,l_Dim_Grp_Rec.Bsc_Group_Level_Total_Flag
712 ,l_Dim_Grp_Rec.Bsc_Group_Level_Comp_Flag
713 ,l_Dim_Grp_Rec.Bsc_Group_Level_Filter_Col
714 ,l_Dim_Grp_Rec.Bsc_Group_Level_Filter_Value
715 ,l_Dim_Grp_Rec.Bsc_Group_Level_Default_Value
716 ,l_Dim_Grp_Rec.Bsc_Group_Level_Default_Type
717 ,l_Dim_Grp_Rec.Bsc_Group_Level_Parent_In_Tot
718 ,l_Dim_Grp_Rec.Bsc_Group_Level_No_Items
719 ,l_Dim_Grp_Rec.Bsc_Group_Level_Where_Clause);
720
721 if (p_commit = FND_API.G_TRUE) then
722 commit;
723 end if;
724
725 end if;
726
727 EXCEPTION
728 WHEN FND_API.G_EXC_ERROR THEN
729 ROLLBACK TO CreateBSCDimLevInGrpPVT;
730 FND_MSG_PUB.Count_And_Get
731 ( p_encoded => 'F'
732 , p_count => x_msg_count
733 , p_data => x_msg_data
734 );
735 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
736 x_return_status := FND_API.G_RET_STS_ERROR;
737 RAISE;
738 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
739 ROLLBACK TO CreateBSCDimLevInGrpPVT;
740 FND_MSG_PUB.Count_And_Get
741 ( p_encoded => 'F'
742 , p_count => x_msg_count
743 , p_data => x_msg_data
744 );
745 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
746 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
747 RAISE;
748 WHEN NO_DATA_FOUND THEN
749 ROLLBACK TO CreateBSCDimLevInGrpPVT;
750 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
751 IF (x_msg_data IS NOT NULL) THEN
752 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PVT.Create_Dim_Levels_In_Group ';
753 ELSE
754 x_msg_data := 'BSC_DIMENSION_GROUPS_PVT.Create_Dim_Levels_In_Group '||SQLERRM;
755 END IF;
756 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
757 RAISE;
758 WHEN OTHERS THEN
759 ROLLBACK TO CreateBSCDimLevInGrpPVT;
760 if (SQLCODE = -01400) then
761 FND_MESSAGE.SET_NAME('BSC','BSC_TABLE_NULL_VALUE');
762 FND_MESSAGE.SET_TOKEN('BSC_OBJECT', g_db_object);
763 FND_MSG_PUB.ADD;
764 RAISE FND_API.G_EXC_ERROR;
765 end if;
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_GROUPS_PVT.Create_Dim_Levels_In_Group ';
769 ELSE
770 x_msg_data := 'BSC_DIMENSION_GROUPS_PVT.Create_Dim_Levels_In_Group '||SQLERRM;
771 END IF;
772 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
773 RAISE;
774 end Create_Dim_Levels_In_Group;
775
776 /************************************************************************************
777 ************************************************************************************/
778
779 procedure Retrieve_Dim_Levels_In_Group(
780 p_commit IN varchar2 := FND_API.G_FALSE
781 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
782 ,x_Dim_Grp_Rec IN OUT NOCOPY BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
783 ,x_return_status OUT NOCOPY varchar2
784 ,x_msg_count OUT NOCOPY number
785 ,x_msg_data OUT NOCOPY varchar2
786 ) is
787
788 begin
789
790 x_return_status := FND_API.G_RET_STS_SUCCESS;
791 FND_MSG_PUB.Initialize;
792 g_db_object := 'Retrieve_Dim_Levels_In_Group';
793 -- Code added for ALL start here
794 -- PMF passes dimension level short name and dimension short name.
795 -- dim_level_id is found from bsc_sys_dim_levels_tl table.
796 -- As SHORT_NAME column that's being added to bsc_sys_dim_groups_tl table
797 -- is not populated, for ALL enhancement, query on BSC_SYS_DIM_LEVELS_BY_GROUP
798 -- is based only on dim_level_id.
799 -- This is required for PMF ALL enhancement (phase1).
800 SELECT DISTINCT dim_level_index
801 ,total_flag
802 ,comparison_flag
803 ,filter_column
804 ,filter_value
805 ,default_value
806 ,default_type
807 ,parent_in_total
808 ,no_items
809 ,where_clause
810 INTO x_Dim_Grp_Rec.Bsc_Dim_Level_Index
811 ,x_Dim_Grp_Rec.Bsc_Group_Level_Total_Flag
812 ,x_Dim_Grp_Rec.Bsc_Group_Level_Comp_Flag
813 ,x_Dim_Grp_Rec.Bsc_Group_Level_Filter_Col
814 ,x_Dim_Grp_Rec.Bsc_Group_Level_Filter_Value
815 ,x_Dim_Grp_Rec.Bsc_Group_Level_Default_Value
816 ,x_Dim_Grp_Rec.Bsc_Group_Level_Default_Type
817 ,x_Dim_Grp_Rec.Bsc_Group_Level_Parent_In_Tot
818 ,x_Dim_Grp_Rec.Bsc_Group_Level_No_Items
819 ,x_Dim_Grp_Rec.Bsc_Group_Level_Where_Clause
820 FROM BSC_SYS_DIM_LEVELS_BY_GROUP
821 WHERE dim_group_id = p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
822 AND dim_level_id = p_Dim_Grp_Rec.Bsc_Level_Id;
823 if (p_commit = FND_API.G_TRUE) then
824 commit;
825 end if;
826
827 EXCEPTION
828 WHEN FND_API.G_EXC_ERROR THEN
829 x_return_status := FND_API.G_RET_STS_ERROR;
830 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
831 ,p_data => x_msg_data);
832 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
833 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
834 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
835 ,p_data => x_msg_data);
836 WHEN NO_DATA_FOUND THEN
837 FND_MSG_PUB.Initialize;
838 FND_MESSAGE.SET_NAME('BSC','BSC_NO_VALUE_FOUND');
839 FND_MESSAGE.SET_TOKEN('BSC_OBJECT', g_db_object);
840 FND_MSG_PUB.ADD;
841 RAISE FND_API.G_EXC_ERROR;
842 WHEN OTHERS THEN
843 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
844 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
845 ,p_data => x_msg_data);
846
847 end Retrieve_Dim_Levels_In_Group;
848
849 /************************************************************************************
850 ************************************************************************************/
851
852 procedure Update_Dim_Levels_In_Group(
853 p_commit IN varchar2 := FND_API.G_FALSE
854 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
855 ,x_return_status OUT NOCOPY varchar2
856 ,x_msg_count OUT NOCOPY number
857 ,x_msg_data OUT NOCOPY varchar2
858 ) is
859
860 l_Dim_Grp_Rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
861 l_Dim_Grp_Rec_in BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
862
863 l_count number;
864
865 CURSOR c_dim_lvls_by_group (p_grp_short_name VARCHAR2)
866 IS
867 SELECT dim_group_id
868 FROM BSC_SYS_DIM_GROUPS_TL
869 WHERE SHORT_NAME = p_grp_short_name;
870
871 begin
872 SAVEPOINT UpdateBSCDimLevInGrpPVT;
873 FND_MSG_PUB.Initialize;
874 x_return_status := FND_API.G_RET_STS_SUCCESS;
875 l_Dim_Grp_Rec_in := p_Dim_Grp_Rec;
876 -- Check that the group id is valid or that the name is valid.
877 if p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id is not null then
878 l_count := BSC_DIMENSION_GROUPS_PVT.Validate_Dim_Group_Id(p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id);
879 if l_count = 0 then
880 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_GROUP_ID');
881 FND_MESSAGE.SET_TOKEN('BSC_GROUP_ID', p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id);
882 FND_MSG_PUB.ADD;
883 RAISE FND_API.G_EXC_ERROR;
884 end if;
885 -- if id is null then check that short name is not null
886 elsif p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Short_Name is not null then
887 IF (c_dim_lvls_by_group%ISOPEN) THEN
888 CLOSE c_dim_lvls_by_group;
889 END IF;
890 OPEN c_dim_lvls_by_group(l_Dim_Grp_Rec_in.Bsc_Dim_Level_Group_Short_Name);
891 FETCH c_dim_lvls_by_group INTO l_Dim_Grp_Rec_in.Bsc_Dim_Level_Group_Id;
892 IF (c_dim_lvls_by_group%NOTFOUND) THEN
893 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_GROUP_SHORT_NAME');
894 FND_MESSAGE.SET_TOKEN('BSC_GROUP_SHORT_NAME',l_Dim_Grp_Rec_in.Bsc_Dim_Level_Group_Short_Name);
895 FND_MSG_PUB.ADD;
896 RAISE FND_API.G_EXC_ERROR;
897 END IF;
898 CLOSE c_dim_lvls_by_group;
899 else
900 -- if id is null then check that name is not null
901 if p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name is not null then
902 select count(dim_group_id)
903 into l_count
904 from BSC_SYS_DIM_GROUPS_TL
905 where name = p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name;
906 if l_count = 0 then
907 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_GROUP_NAME');
908 FND_MESSAGE.SET_TOKEN('BSC_GROUP_NAME', p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name);
909 FND_MSG_PUB.ADD;
910 RAISE FND_API.G_EXC_ERROR;
911 else -- get id for this name
912 select distinct dim_group_id
913 into l_Dim_Grp_Rec_in.Bsc_Dim_Level_Group_Id
914 from BSC_SYS_DIM_GROUPS_VL
915 where name = p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name;
916 end if;
917 else
918 FND_MESSAGE.SET_NAME('BSC','BSC_NO_GROUP_NAME_ENTERED');
919 FND_MESSAGE.SET_TOKEN('BSC_NO_GROUP', p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name);
920 FND_MSG_PUB.ADD;
921 RAISE FND_API.G_EXC_ERROR;
922 end if;
923 end if;
924
925 -- Check Dimension level id is valid.
926 if p_Dim_Grp_Rec.Bsc_Level_Id is not null then
927 l_count := BSC_DIMENSION_LEVELS_PVT.Validate_Dim_Level_Id(p_Dim_Grp_Rec.Bsc_Level_Id);
928 if l_count = 0 then
929 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_LEVEL_ID');
930 FND_MESSAGE.SET_TOKEN('BSC_LEVEL_ID', p_Dim_Grp_Rec.Bsc_Level_Id);
931 FND_MSG_PUB.ADD;
932 RAISE FND_API.G_EXC_ERROR;
933 end if;
934 else
935 FND_MESSAGE.SET_NAME('BSC','BSC_NO_LEVEL_ID_ENTERED');
936 FND_MESSAGE.SET_TOKEN('BSC_LEVEL_ID', p_Dim_Grp_Rec.Bsc_Level_Id);
937 FND_MSG_PUB.ADD;
938 RAISE FND_API.G_EXC_ERROR;
939 end if;
940
941 -- Not all values will be passed. We need to make sure values not passed are not
942 -- changed by procedure, therefore we get what is there before we do any updates.
943 Retrieve_Dim_Levels_In_Group( p_commit
944 ,l_Dim_Grp_Rec_in
945 ,l_Dim_Grp_Rec
946 ,x_return_status
947 ,x_msg_count
948 ,x_msg_data);
949
950 -- update LOCAL language ,source language, group id and level Id values with PASSED values.
951 l_Dim_Grp_Rec.Bsc_Language := p_Dim_Grp_Rec.Bsc_Language;
952 l_Dim_Grp_Rec.Bsc_Source_Language := p_Dim_Grp_Rec.Bsc_Source_Language;
953 l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id := l_Dim_Grp_Rec_in.Bsc_Dim_Level_Group_Id;
954 l_Dim_Grp_Rec.Bsc_Level_Id := l_Dim_Grp_Rec_in.Bsc_Level_Id;
955
956 -- Copy PASSED Record values into LOCAL Record values for the PASSED Record values
957 -- which are NOT NULL.
958 if p_Dim_Grp_Rec.Bsc_Dim_Level_Index is not null then
959 l_Dim_Grp_Rec.Bsc_Dim_Level_Index := p_Dim_Grp_Rec.Bsc_Dim_Level_Index;
960 end if;
961 if p_Dim_Grp_Rec.Bsc_Group_Level_Total_Flag is not null then
962 l_Dim_Grp_Rec.Bsc_Group_Level_Total_Flag := p_Dim_Grp_Rec.Bsc_Group_Level_Total_Flag;
963 end if;
964 if p_Dim_Grp_Rec.Bsc_Group_Level_Comp_Flag is not null then
965 l_Dim_Grp_Rec.Bsc_Group_Level_Comp_Flag := p_Dim_Grp_Rec.Bsc_Group_Level_Comp_Flag;
966 end if;
967 if p_Dim_Grp_Rec.Bsc_Group_Level_Filter_Col is not null then
968 l_Dim_Grp_Rec.Bsc_Group_Level_Filter_Col := p_Dim_Grp_Rec.Bsc_Group_Level_Filter_Col;
969 end if;
970 if p_Dim_Grp_Rec.Bsc_Group_Level_Filter_Value is not null then
971 l_Dim_Grp_Rec.Bsc_Group_Level_Filter_Value := p_Dim_Grp_Rec.Bsc_Group_Level_Filter_Value;
972 end if;
973 if p_Dim_Grp_Rec.Bsc_Group_Level_Default_Value is not null then
974 l_Dim_Grp_Rec.Bsc_Group_Level_Default_Value := p_Dim_Grp_Rec.Bsc_Group_Level_Default_Value;
975 end if;
976 if p_Dim_Grp_Rec.Bsc_Group_Level_Default_Type is not null then
977 l_Dim_Grp_Rec.Bsc_Group_Level_Default_Type := p_Dim_Grp_Rec.Bsc_Group_Level_Default_Type;
978 end if;
979 if p_Dim_Grp_Rec.Bsc_Group_Level_Parent_In_Tot is not null then
980 l_Dim_Grp_Rec.Bsc_Group_Level_Parent_In_Tot := p_Dim_Grp_Rec.Bsc_Group_Level_Parent_In_Tot;
981 end if;
982 if p_Dim_Grp_Rec.Bsc_Group_Level_No_Items is not null then
983 l_Dim_Grp_Rec.Bsc_Group_Level_No_Items := p_Dim_Grp_Rec.Bsc_Group_Level_No_Items;
984 end if;
985 --Where_Clause can be null
986 --if p_Dim_Grp_Rec.Bsc_Group_Level_Where_Clause is not null then
987 l_Dim_Grp_Rec.Bsc_Group_Level_Where_Clause := p_Dim_Grp_Rec.Bsc_Group_Level_Where_Clause;
988 --end if;
989
990 update BSC_SYS_DIM_LEVELS_BY_GROUP
991 set dim_level_index = l_Dim_Grp_Rec.Bsc_Dim_Level_Index
992 ,total_flag = l_Dim_Grp_Rec.Bsc_Group_Level_Total_Flag
993 ,comparison_flag = l_Dim_Grp_Rec.Bsc_Group_Level_Comp_Flag
994 ,filter_column = l_Dim_Grp_Rec.Bsc_Group_Level_Filter_Col
995 ,filter_value = l_Dim_Grp_Rec.Bsc_Group_Level_Filter_Value
996 ,default_value = l_Dim_Grp_Rec.Bsc_Group_Level_Default_Value
997 ,default_type = l_Dim_Grp_Rec.Bsc_Group_Level_Default_Type
998 ,parent_in_total = l_Dim_Grp_Rec.Bsc_Group_Level_Parent_In_Tot
999 ,no_items = l_Dim_Grp_Rec.Bsc_Group_Level_No_Items
1000 ,where_clause = l_Dim_Grp_Rec.Bsc_Group_Level_Where_Clause
1001 where dim_group_id = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
1002 and dim_level_id = l_Dim_Grp_Rec.Bsc_Level_Id;
1003
1004 if (p_commit = FND_API.G_TRUE) then
1005 commit;
1006 end if;
1007
1008 EXCEPTION
1009 WHEN FND_API.G_EXC_ERROR THEN
1010 ROLLBACK TO UpdateBSCDimLevInGrpPVT;
1011 CLOSE c_dim_lvls_by_group;
1012 FND_MSG_PUB.Count_And_Get
1013 ( p_encoded => 'F'
1014 , p_count => x_msg_count
1015 , p_data => x_msg_data
1016 );
1017 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1018 x_return_status := FND_API.G_RET_STS_ERROR;
1019 RAISE;
1020 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1021 ROLLBACK TO UpdateBSCDimLevInGrpPVT;
1022 CLOSE c_dim_lvls_by_group;
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 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1029 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1030 RAISE;
1031 WHEN NO_DATA_FOUND THEN
1032 ROLLBACK TO UpdateBSCDimLevInGrpPVT;
1033 CLOSE c_dim_lvls_by_group;
1034 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1035 IF (x_msg_data IS NOT NULL) THEN
1036 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PVT.Update_Dim_Levels_In_Group ';
1037 ELSE
1038 x_msg_data := 'BSC_DIMENSION_GROUPS_PVT.Update_Dim_Levels_In_Group '||SQLERRM;
1039 END IF;
1040 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1041 RAISE;
1042 WHEN OTHERS THEN
1043 ROLLBACK TO UpdateBSCDimLevInGrpPVT;
1044 CLOSE c_dim_lvls_by_group;
1045 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1046 IF (x_msg_data IS NOT NULL) THEN
1047 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PVT.Update_Dim_Levels_In_Group ';
1048 ELSE
1049 x_msg_data := 'BSC_DIMENSION_GROUPS_PVT.Update_Dim_Levels_In_Group '||SQLERRM;
1050 END IF;
1051 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1052 RAISE;
1053 end Update_Dim_Levels_In_Group;
1054
1055 /************************************************************************************
1056 ************************************************************************************/
1057
1058 procedure Delete_Dim_Levels_In_Group(
1059 p_commit IN varchar2 := FND_API.G_FALSE
1060 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
1061 ,x_return_status OUT NOCOPY varchar2
1062 ,x_msg_count OUT NOCOPY number
1063 ,x_msg_data OUT NOCOPY varchar2
1064 ) is
1065
1066 l_Dim_Grp_Rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
1067
1068 l_count number;
1069
1070 CURSOR c_dim_lvls_by_group (p_grp_short_name VARCHAR2)
1071 IS
1072 SELECT dim_group_id
1073 FROM BSC_SYS_DIM_GROUPS_TL
1074 WHERE SHORT_NAME = p_grp_short_name;
1075
1076 begin
1077 SAVEPOINT DeleteBSCDimLevInGrpPVT;
1078 FND_MSG_PUB.Initialize;
1079 x_return_status := FND_API.G_RET_STS_SUCCESS;
1080 -- Assign all values in the passed "Record" parameter to the locally defined
1081 -- "Record" variable.
1082 l_Dim_Grp_Rec := p_Dim_Grp_Rec;
1083
1084 -- Check that the group id is valid or that the name is valid.
1085 if l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id is not null then
1086 l_count := BSC_DIMENSION_GROUPS_PVT.Validate_Dim_Group_Id(l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id);
1087 if l_count = 0 then
1088 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_GROUP_ID');
1089 FND_MESSAGE.SET_TOKEN('BSC_GROUP_ID', l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id);
1090 FND_MSG_PUB.ADD;
1091 RAISE FND_API.G_EXC_ERROR;
1092 end if;
1093 -- if id is null then check for short name name is not null
1094 elsif l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Short_Name is not null then
1095 IF (c_dim_lvls_by_group%ISOPEN) THEN
1096 CLOSE c_dim_lvls_by_group;
1097 END IF;
1098 OPEN c_dim_lvls_by_group(l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Short_Name);
1099 FETCH c_dim_lvls_by_group INTO l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id;
1100 IF (c_dim_lvls_by_group%NOTFOUND) THEN
1101 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_GROUP_SHORT_NAME');
1102 FND_MESSAGE.SET_TOKEN('BSC_GROUP_SHORT_NAME',l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Short_Name);
1103 FND_MSG_PUB.ADD;
1104 RAISE FND_API.G_EXC_ERROR;
1105 END IF;
1106 CLOSE c_dim_lvls_by_group;
1107 else
1108 -- if id and shortname both are null then check that name is not null
1109 if l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name is not null then
1110 select count(dim_group_id)
1111 into l_count
1112 from BSC_SYS_DIM_GROUPS_TL
1113 where name = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name;
1114 if l_count = 0 then
1115 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_GROUP_NAME');
1116 FND_MESSAGE.SET_TOKEN('BSC_GROUP_NAME', l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name);
1117 FND_MSG_PUB.ADD;
1118 RAISE FND_API.G_EXC_ERROR;
1119 else -- get id for this name
1120 select distinct dim_group_id
1121 into l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
1122 from BSC_SYS_DIM_GROUPS_VL
1123 where name = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name;
1124 end if;
1125 else
1126 FND_MESSAGE.SET_NAME('BSC','BSC_NO_GROUP_NAME_ENTERED');
1127 FND_MESSAGE.SET_TOKEN('BSC_NO_GROUP', l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name);
1128 FND_MSG_PUB.ADD;
1129 RAISE FND_API.G_EXC_ERROR;
1130 end if;
1131 end if;
1132
1133 -- Check Dimension level id is valid.
1134 if l_Dim_Grp_Rec.Bsc_Level_Id is not null then
1135 l_count := BSC_DIMENSION_LEVELS_PVT.Validate_Dim_Level_Id(l_Dim_Grp_Rec.Bsc_Level_Id);
1136 if l_count = 0 then
1137 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_LEVEL_ID');
1138 FND_MESSAGE.SET_TOKEN('BSC_LEVEL_ID', l_Dim_Grp_Rec.Bsc_Level_Id);
1139 FND_MSG_PUB.ADD;
1140 RAISE FND_API.G_EXC_ERROR;
1141 end if;
1142
1143 delete from BSC_SYS_DIM_LEVELS_BY_GROUP
1144 where dim_group_id = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
1145 and dim_level_id = l_Dim_Grp_Rec.Bsc_Level_Id;
1146 else
1147 delete from BSC_SYS_DIM_LEVELS_BY_GROUP
1148 where dim_group_id = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id;
1149 end if;
1150 if (p_commit = FND_API.G_TRUE) then
1151 commit;
1152 end if;
1153
1154 EXCEPTION
1155 WHEN FND_API.G_EXC_ERROR THEN
1156 CLOSE c_dim_lvls_by_group;
1157 ROLLBACK TO DeleteBSCDimLevInGrpPVT;
1158 FND_MSG_PUB.Count_And_Get
1159 ( p_encoded => 'F'
1160 , p_count => x_msg_count
1161 , p_data => x_msg_data
1162 );
1163 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1164 x_return_status := FND_API.G_RET_STS_ERROR;
1165 RAISE;
1166 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1167 CLOSE c_dim_lvls_by_group;
1168 ROLLBACK TO DeleteBSCDimLevInGrpPVT;
1169 FND_MSG_PUB.Count_And_Get
1170 ( p_encoded => 'F'
1171 , p_count => x_msg_count
1172 , p_data => x_msg_data
1173 );
1174 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1175 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1176 RAISE;
1177 WHEN NO_DATA_FOUND THEN
1178 CLOSE c_dim_lvls_by_group;
1179 ROLLBACK TO DeleteBSCDimLevInGrpPVT;
1180 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1181 IF (x_msg_data IS NOT NULL) THEN
1182 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PVT.Delete_Dim_Levels_In_Group ';
1183 ELSE
1184 x_msg_data := 'BSC_DIMENSION_GROUPS_PVT.Delete_Dim_Levels_In_Group '||SQLERRM;
1185 END IF;
1186 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1187 RAISE;
1188 WHEN OTHERS THEN
1189 CLOSE c_dim_lvls_by_group;
1190 ROLLBACK TO DeleteBSCDimLevInGrpPVT;
1191 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1192 IF (x_msg_data IS NOT NULL) THEN
1193 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PVT.Delete_Dim_Levels_In_Group ';
1194 ELSE
1195 x_msg_data := 'BSC_DIMENSION_GROUPS_PVT.Delete_Dim_Levels_In_Group '||SQLERRM;
1196 END IF;
1197 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1198 RAISE;
1199 end Delete_Dim_Levels_In_Group;
1200
1201 /************************************************************************************
1202 ************************************************************************************/
1203 /************************************************************************************
1204 ************************************************************************************/
1205
1206 /*-------------------------------------------------------------------------------------
1207 get_Dim_Group_Id:
1208 Return the Dimension Group ID, null is returned the Short Name Not exist
1209 ---------------------------------------------------------------------------------------*/
1210 FUNCTION get_Dim_Group_Id(
1211 p_Short_Name IN VARCHAR2
1212 ) RETURN number IS
1213 v_Id number;
1214
1215 BEGIN
1216 Select distinct DIM_GROUP_ID
1217 into v_Id
1218 from BSC_SYS_DIM_GROUPS_TL
1219 where SHORT_NAME = p_Short_Name;
1220 RETURN v_Id;
1221
1222 EXCEPTION
1223 WHEN OTHERS THEN
1224 RETURN null;
1225 END get_Dim_Group_Id;
1226
1227 --
1228 PROCEDURE Translate_Dimension_Group
1229 ( p_commit IN VARCHAR2 := FND_API.G_FALSE
1230 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
1231 ,x_return_status OUT NOCOPY VARCHAR2
1232 ,x_msg_count OUT NOCOPY NUMBER
1233 ,x_msg_data OUT NOCOPY VARCHAR2
1234 ) IS
1235 l_Dim_Grp_rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
1236 BEGIN
1237 SAVEPOINT TranslateBSCDimGrpPVT;
1238 x_return_status:= FND_API.G_RET_STS_SUCCESS;
1239
1240 Retrieve_Dim_Group(
1241 p_Dim_Grp_Rec => p_Dim_Grp_Rec
1242 ,x_Dim_Grp_Rec => l_Dim_Grp_rec
1243 ,x_return_status => x_return_status
1244 ,x_msg_count => x_msg_count
1245 ,x_msg_data => x_msg_data
1246 );
1247
1248 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1249 RAISE FND_API.G_EXC_ERROR;
1250 END IF;
1251
1252 IF (p_Dim_Grp_rec.Bsc_Dim_Level_Group_Name IS NOT NULL) THEN
1253 l_Dim_Grp_rec.Bsc_Dim_Level_Group_Name := p_Dim_Grp_rec.Bsc_Dim_Level_Group_Name;
1254 END IF;
1255
1256 l_Dim_Grp_rec.Bsc_Last_Update_Date := NVL(p_Dim_Grp_Rec.Bsc_Last_Update_Date, SYSDATE);
1257
1258 UPDATE bsc_sys_dim_groups_tl
1259 SET name = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name
1260 ,source_lang = userenv('LANG')
1261 ,last_updated_by = NVL(l_Dim_Grp_Rec.Bsc_Last_Updated_By, p_Dim_Grp_Rec.Bsc_Last_Updated_By)
1262 ,last_update_date = l_Dim_Grp_rec.Bsc_Last_Update_Date
1263 ,last_update_login = p_Dim_Grp_Rec.Bsc_Last_Update_Login
1264 WHERE short_name = p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Short_Name
1265 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1266
1267 IF (p_commit = FND_API.G_TRUE) THEN
1268 COMMIT;
1269 END IF;
1270
1271 EXCEPTION
1272 WHEN FND_API.G_EXC_ERROR THEN
1273 ROLLBACK TO TranslateBSCDimGrpPVT;
1274 FND_MSG_PUB.Count_And_Get(
1275 p_encoded => 'F'
1276 ,p_count => x_msg_count
1277 ,p_data => x_msg_data
1278 );
1279 x_return_status := FND_API.G_RET_STS_ERROR;
1280 RAISE;
1281 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1282 ROLLBACK TO TranslateBSCDimGrpPVT;
1283 FND_MSG_PUB.Count_And_Get(
1284 p_encoded => 'F'
1285 ,p_count => x_msg_count
1286 ,p_data => x_msg_data
1287 );
1288 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1289 RAISE;
1290 WHEN NO_DATA_FOUND THEN
1291 ROLLBACK TO TranslateBSCDimGrpPVT;
1292 x_return_status := FND_API.G_RET_STS_ERROR;
1293 IF (x_msg_data IS NOT NULL) THEN
1294 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PVT.Translate_Dimension_Group ';
1295 ELSE
1296 x_msg_data := 'BSC_DIMENSION_GROUPS_PVT.Translate_Dimension_Group '||SQLERRM;
1297 END IF;
1298 RAISE;
1299 WHEN OTHERS THEN
1300 ROLLBACK TO TranslateBSCDimGrpPVT;
1301 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1302 IF (x_msg_data IS NOT NULL) THEN
1303 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PVT.Translate_Dimension_Group ';
1304 ELSE
1305 x_msg_data := 'BSC_DIMENSION_GROUPS_PVT.Translate_Dimension_Group '||SQLERRM;
1306 END IF;
1307 RAISE;
1308
1309 END Translate_Dimension_Group;
1310
1311 --====================================================================
1312
1313 procedure Retrieve_Dim_Group(
1314 p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
1315 ,x_Dim_Grp_Rec OUT NOCOPY BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
1316 ,x_return_status OUT NOCOPY VARCHAR2
1317 ,x_msg_count OUT NOCOPY NUMBER
1318 ,x_msg_data OUT NOCOPY VARCHAR2
1319 ) is
1320
1321 begin
1322 x_return_status:= FND_API.G_RET_STS_SUCCESS;
1323
1324 SELECT distinct name, dim_group_id, short_name
1325 INTO
1326 x_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name
1327 ,x_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
1328 ,x_Dim_Grp_Rec.Bsc_Dim_Level_Group_Short_Name
1329 FROM BSC_SYS_DIM_GROUPS_VL
1330 WHERE short_name = p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Short_Name;
1331
1332 EXCEPTION
1333 WHEN FND_API.G_EXC_ERROR THEN
1334 x_return_status := FND_API.G_RET_STS_ERROR;
1335 FND_MSG_PUB.Count_And_Get(
1336 p_encoded => 'F'
1337 ,p_count => x_msg_count
1338 ,p_data => x_msg_data
1339 );
1340 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1341 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1342 FND_MSG_PUB.Count_And_Get(
1343 p_encoded => 'F'
1344 ,p_count => x_msg_count
1345 ,p_data => x_msg_data
1346 );
1347 WHEN NO_DATA_FOUND THEN
1348 x_return_status := FND_API.G_RET_STS_ERROR;
1349 IF (x_msg_data IS NOT NULL) THEN
1350 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PVT.Retrieve_Dim_Group ';
1351 ELSE
1352 x_msg_data := 'BSC_DIMENSION_GROUPS_PVT.Retrieve_Dim_Group '||SQLERRM;
1353 END IF;
1354 RAISE FND_API.G_EXC_ERROR;
1355 WHEN OTHERS THEN
1356 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1357 IF (x_msg_data IS NOT NULL) THEN
1358 x_msg_data := x_msg_data||' -> BSC_DIMENSION_GROUPS_PVT.Retrieve_Dim_Group ';
1359 ELSE
1360 x_msg_data := 'BSC_DIMENSION_GROUPS_PVT.Retrieve_Dim_Group '||SQLERRM;
1361 END IF;
1362
1363 END Retrieve_Dim_Group;
1364
1365
1366
1367 /*************************************************************************************
1368
1369 API TO SYNC UP THE DIMENSION GROUPS LANGUAGE DATA FROM PMF TO BSC
1370
1371 *************************************************************************************/
1372
1373 procedure Translate_Dim_by_given_lang
1374 ( p_commit IN VARCHAR2 := FND_API.G_FALSE
1375 , p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
1376 , x_return_status OUT NOCOPY VARCHAR2
1377 , x_msg_count OUT NOCOPY NUMBER
1378 , x_msg_data OUT NOCOPY VARCHAR2
1379 )
1380 IS
1381
1382 BEGIN
1383
1384 SAVEPOINT TransDimByLangBsc;
1385
1386 x_return_status := FND_API.G_RET_STS_SUCCESS;
1387
1388 FND_MSG_PUB.Initialize;
1389
1390 UPDATE BSC_SYS_DIM_GROUPS_TL
1391 SET NAME = p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name
1392 ,SOURCE_LANG = p_Dim_Grp_Rec.Bsc_Source_Language
1393 ,LAST_UPDATE_DATE = p_Dim_Grp_Rec.Bsc_Last_Update_Date
1394 WHERE SHORT_NAME = p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Short_Name
1395 AND LANGUAGE = p_Dim_Grp_Rec.Bsc_Language;
1396
1397 IF (p_commit = FND_API.G_TRUE) THEN
1398 COMMIT;
1399 END IF;
1400
1401
1402 EXCEPTION
1403 WHEN FND_API.G_EXC_ERROR THEN
1404 ROLLBACK TO TransDimByLangBsc;
1405 x_return_status := FND_API.G_RET_STS_ERROR;
1406 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1407 ,p_data => x_msg_data);
1408
1409 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1410 ROLLBACK TO TransDimByLangBsc;
1411 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1412 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1413 ,p_data => x_msg_data);
1414 WHEN NO_DATA_FOUND THEN
1415 ROLLBACK TO TransDimByLangBsc;
1416 x_return_status := FND_API.G_RET_STS_ERROR;
1417 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1418 ,p_data => x_msg_data);
1419 WHEN OTHERS THEN
1420 ROLLBACK TO TransDimByLangBsc;
1421 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1422 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1423 ,p_data => x_msg_data);
1424 END Translate_Dim_by_given_lang;
1425
1426 /******************************************************************************/
1427
1428 end BSC_DIMENSION_GROUPS_PVT;
[Home] [Help]