[Home] [Help]
PACKAGE BODY: APPS.BIS_DIMENSION_LEVEL_PUB
Source
1 PACKAGE BODY BIS_DIMENSION_LEVEL_PUB AS
2 /* $Header: BISPDMLB.pls 120.6 2006/09/29 14:28:50 ppandey ship $ */
3 /*
4 REM +=======================================================================+
5 REM | Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA |
6 REM | All rights reserved. |
7 REM +=======================================================================+
8 REM | FILENAME |
9 REM | BISPDMLB.pls |
10 REM | |
11 REM | DESCRIPTION |
12 REM | Public API for managing dimension levels for the
13 REM | Key Performance Framework.
14 REM |
15 REM | This package should be maintaind by EDW once it gets integrated
16 REM | with BIS.
17 REM |
18 REM | NOTES |
19 REM | |
20 REM | 27-JAN-03 arhegde For having different local variables for IN and OUT |
21 REM | parameters (bug#2758428) |
22 REM | 20-FEB-2003 PAJOHRI Added Procedure UPDATE_DIMENSION_LEVEL |
23 REM | 23-FEB-2003 PAJOHRI , Added procedures DELETE_DIMENSION_LEVEL |
24 REM | CREATE_DIMENSION_LEVEL |
25 REM | 13-JUN-03 MAHRAO Added Procedure Load_Dimension_Level |
26 REM | 20-JUN-03 arhegde bug# 3015484 create relationship when |
27 REM | it is not present, else update it |
28 REM | 09-JUL-2003 arhegde bug#3028436 Moved logic to BSC API from here |
29 REM | 10-JUL-2003 mahrao bug#3042968 Added extra parameter to |
30 REM | Load_Dimension_Level |
31 REM | 02-SEP-2003 mahrao bug#3099977 Changed code in load_dim_levels |
32 REM | so that table_type flag is set to -1 |
33 REM | if underlying view doesnot exist. |
34 REM | 29-OCT-03 MAHRAO enh of adding new attributes to dim objects |
35 REM | 14-NOV-03 RCHANDRA enh 2997632, customization APIs |
36 REM | 25-NOV-03 MAHRAO enh of populated DimLvlList and where_clause_list |
37 REM | 04-DEC-03 ANKGOEL bug#3264490 Made changes for performance issues |
38 REM | in uploading dimension levels |
39 REM | 07-JAN-04 rpenneru bug#3459443 Modified for getting where clause |
40 REM | from BSC data model |
41 REM | arhegde 07/23/2004 bug# 3760735 dim object caching. |
42 REM | 30-Jul-04 rpenneru Modified for enhancemen#3748519 |
43 REM | 13-Oct-04 rpenneru Modified for bug#3945655 |
44 REM | 21-DEC-04 vtulasi Modified for bug#4045278 - Addtion of LUD |
45 REM | 08-Feb-04 skchoudh Enh#3873195 drill_to_form_function column |
46 REM | is added |
47 REM | 08-Feb-05 ankgoel Enh#4172034 DD Seeding by Product Teams |
48 REM | 09-Feb-05 ankgoel Bug#4172055 LUD validations for dim_lvls_by_group
49 REM | 02-MAR-05 ashankar Bug#3583110 Modifed load_dim_level |
50 REM | 08-APR-2005 kyadamak generating unique master table for PMF dimension |
51 REM | objects for the bug# 4290359 |
52 REM | 22-Aug-05 ankgoel Bug#4557713 LUD validation for dim_lvls_by_group|
53 REM | 07-NOV-05 akoduri Bug#4696105,Added overloaded API |
54 REM | get_customized_enabled |
55 REM | 12-Dec-05 ankgoel Enh#4640165 - Select dim objects from Report |
56 REM | 06-Jan-06 akoduri Enh#4739401 - Hide Dimensions/Dim Objects |
57 REM | 24-May-06 akoduri Bug#5128863 - LUD of bsc_sys_dim_levels should |
58 REM | also get updated |
59 REM +=======================================================================+
60 */
61 --
62 G_PKG_NAME CONSTANT VARCHAR2(30):='BIS_DIMENSION_LEVEL_PUB';
63 --
64 PROCEDURE load_dim_levels_in_group(
65 p_commit IN VARCHAR2 := FND_API.G_FALSE
66 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
67 ,p_Bsc_Pmf_Dim_Rec IN BSC_PMF_UI_API_PUB.Bsc_Pmf_Dim_Rec_Type
68 ,x_return_status OUT NOCOPY VARCHAR2
69 ,x_msg_count OUT NOCOPY NUMBER
70 ,x_msg_data OUT NOCOPY VARCHAR2
71 );
72
73 --=======================================================================
74
75 PROCEDURE load_dim_levels(
76 p_commit IN VARCHAR2 := FND_API.G_FALSE
77 ,p_bsc_pmf_dim_rec IN BSC_PMF_UI_API_PUB.Bsc_Pmf_Dim_Rec_Type
78 ,p_Bsc_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
79 ,x_return_status OUT NOCOPY VARCHAR2
80 ,x_msg_count OUT NOCOPY NUMBER
81 ,x_msg_data OUT NOCOPY VARCHAR2
82 );
83
84 --
85
86 Procedure Load_Dimension_Level_Wrapper
87 ( p_commit IN VARCHAR2 := FND_API.G_FALSE
88 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
89 , p_Dimension_Level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
90 , p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
91 , p_Bsc_Pmf_Dim_Rec IN BSC_PMF_UI_API_PUB.Bsc_Pmf_Dim_Rec_Type
92 , p_Bsc_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
93 , p_Owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
94 , p_force_mode IN BOOLEAN := FALSE
95 , x_return_status OUT NOCOPY VARCHAR2
96 , x_msg_count OUT NOCOPY NUMBER
97 , x_msg_data OUT NOCOPY VARCHAR2
98 )
99 IS
100 l_return_status VARCHAR2(10);
101 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
102 l_msg_count NUMBER;
103 l_msg_data VARCHAR2(4000);
104 BEGIN
105
106 BIS_DIMENSION_LEVEL_PUB.Load_Dimension_Level
107 ( p_api_version => 1.0
108 , p_commit => FND_API.G_FALSE
109 , p_Dimension_Level_Rec => p_Dimension_Level_Rec
110 , p_owner => p_Owner
111 , x_return_status => l_return_status
112 , x_error_Tbl => l_error_Tbl
113 , p_force_mode => p_force_mode
114 );
115
116 IF((l_return_status IS NOT NULL) AND (l_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
117 IF(l_error_Tbl.COUNT > 0) THEN
118 l_msg_data := l_error_Tbl(l_error_Tbl.FIRST).Error_Description;
119 END IF;
120 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
121 END IF;
122
123 BIS_DIMENSION_LEVEL_PUB.Load_Dimension_Level
124 ( p_Commit => FND_API.G_FALSE
125 , p_Dim_Grp_Rec => p_Dim_Grp_Rec
126 , p_Bsc_Pmf_Dim_Rec => p_Bsc_Pmf_Dim_Rec
127 , p_Bsc_Dim_Level_Rec => p_Bsc_Dim_Level_Rec
128 , x_return_status => l_return_status
129 , x_msg_count => l_msg_count
130 , x_msg_data => l_msg_data
131 , p_force_mode => p_force_mode
132 );
133
134 IF((l_return_status IS NOT NULL) AND (l_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
135 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
136 END IF;
137
138 EXCEPTION
139 WHEN FND_API.G_EXC_ERROR THEN
140 x_return_status := FND_API.G_RET_STS_ERROR ;
141 IF(x_msg_data IS NULL) THEN
142 FND_MSG_PUB.Count_And_Get( p_encoded => 'F'
143 ,p_count => x_msg_count
144 ,p_data => x_msg_data);
145 END IF;
146 RAISE;
147 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
148 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
149 IF(x_msg_data IS NULL) THEN
150 FND_MSG_PUB.Count_And_Get( p_encoded => 'F'
151 ,p_count => x_msg_count
152 ,p_data => x_msg_data);
153 END IF;
154 RAISE;
155 WHEN OTHERS THEN
156 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
157 IF(x_msg_data IS NULL) THEN
158 FND_MSG_PUB.Count_And_Get( p_encoded => 'F'
159 ,p_count => x_msg_count
160 ,p_data => x_msg_data);
161 END IF;
162 RAISE;
163 END Load_Dimension_Level_Wrapper;
164 --
165
166 Procedure Translate_Dim_Level_Wrapper
167 ( p_commit IN VARCHAR2 := FND_API.G_FALSE
168 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
169 , p_Dimension_Level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
170 , p_Bsc_Pmf_Dim_Rec IN BSC_PMF_UI_API_PUB.Bsc_Pmf_Dim_Rec_Type
171 , p_Bsc_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
172 , p_Owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
173 , x_return_status OUT NOCOPY VARCHAR2
174 , x_msg_count OUT NOCOPY NUMBER
175 , x_msg_data OUT NOCOPY VARCHAR2
176 )
177 IS
178 l_return_status VARCHAR2(10);
179 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
180 l_msg_count NUMBER;
181 l_msg_data VARCHAR2(4000);
182 BEGIN
183
184 BIS_DIMENSION_LEVEL_PUB.Translate_dimension_level
185 ( p_api_version => 1.0
186 , p_commit => FND_API.G_FALSE
187 , p_Dimension_Level_Rec => p_Dimension_Level_Rec
188 , p_owner => p_Owner
189 , x_return_status => l_return_status
190 , x_error_Tbl => l_error_Tbl
191 );
192
193 IF((l_return_status IS NOT NULL) AND (l_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
194 IF(l_error_Tbl.COUNT > 0) THEN
195 l_msg_data := l_error_Tbl(l_error_Tbl.FIRST).Error_Description;
196 END IF;
197 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
198 END IF;
199
200 BSC_DIMENSION_LEVELS_PUB.Translate_dimension_level
201 ( p_commit => FND_API.G_FALSE
202 , p_Bsc_Pmf_Dim_Rec => p_Bsc_Pmf_Dim_Rec
203 , p_Bsc_Dim_Level_Rec => p_Bsc_Dim_Level_Rec
204 , x_return_status => l_return_status
205 , x_msg_count => l_msg_count
206 , x_msg_data => l_msg_data
207 );
208
209 IF((l_return_status IS NOT NULL) AND (l_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
210 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
211 END IF;
212
213 EXCEPTION
214 WHEN FND_API.G_EXC_ERROR THEN
215 x_return_status := FND_API.G_RET_STS_ERROR ;
216 IF(x_msg_data IS NULL) THEN
217 FND_MSG_PUB.Count_And_Get( p_encoded => 'F'
218 ,p_count => x_msg_count
219 ,p_data => x_msg_data);
220 END IF;
221 RAISE;
222 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
223 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
224 IF(x_msg_data IS NULL) THEN
225 FND_MSG_PUB.Count_And_Get( p_encoded => 'F'
226 ,p_count => x_msg_count
227 ,p_data => x_msg_data);
228 END IF;
229 RAISE;
230 WHEN OTHERS THEN
231 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
232 IF(x_msg_data IS NULL) THEN
233 FND_MSG_PUB.Count_And_Get( p_encoded => 'F'
234 ,p_count => x_msg_count
235 ,p_data => x_msg_data);
236 END IF;
237 RAISE;
238 END Translate_Dim_Level_Wrapper;
239 --
240
241 Procedure Retrieve_Dimension_Levels
242 ( p_api_version IN NUMBER
243 , p_Dimension_Rec IN BIS_DIMENSION_PUB.Dimension_Rec_Type
244 , x_Dimension_Level_Tbl OUT NOCOPY BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Tbl_Type
245 , x_return_status OUT NOCOPY VARCHAR2
246 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
247 )
248 IS
249 l_Dimension_Rec BIS_DIMENSION_PUB.Dimension_Rec_Type;
250 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
251 BEGIN
252 --commented out NOCOPY this section
253 /*
254 BIS_DIMENSION_PVT.Value_ID_Conversion
255 ( p_api_version => 1.0
256 , p_Dimension_Rec => p_Dimension_Rec
257 , x_Dimension_Rec => l_Dimension_Rec
258 , x_return_status => x_return_status
259 , x_error_Tbl => x_error_tbl
260 );
261
262 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
263 -- POPULATE THE ERROR TABLE
264 --added this message
265 BIS_UTILITIES_PVT.Add_Error_Message
266 ( p_error_msg_name => 'BIS_INVALID_DIMENSION_VALUE'
267 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
268 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Dimension_Levels'
269 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
270 , p_error_table => x_error_tbl
271 , x_error_table => x_error_tbl
272 );
273 RAISE FND_API.G_EXC_ERROR;
274 end if;
275 */
276 BIS_DIMENSION_LEVEL_PVT.Retrieve_Dimension_Levels
277 ( p_api_version => 1.0
278 , p_Dimension_Rec => l_Dimension_Rec
279 , x_Dimension_Level_Tbl => x_Dimension_Level_Tbl
280 , x_return_status => x_return_status
281 , x_error_Tbl => x_error_tbl
282 );
283
284 EXCEPTION
285 WHEN NO_DATA_FOUND THEN
286 x_return_status := FND_API.G_RET_STS_ERROR ;
287 when FND_API.G_EXC_ERROR then
288 x_return_status := FND_API.G_RET_STS_ERROR ;
289 when FND_API.G_EXC_UNEXPECTED_ERROR then
290 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
291 when others then
292 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
293 l_error_tbl := x_error_tbl;
294 BIS_UTILITIES_PVT.Add_Error_Message
295 ( p_error_msg_id => SQLCODE
296 , p_error_description => SQLERRM
297 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Dimension_Levels'
298 , p_error_table => l_error_tbl
299 , x_error_table => x_error_tbl
300 );
301
302
303 END Retrieve_Dimension_Levels;
304 --
305 Procedure Retrieve_Dimension_Level
306 ( p_api_version IN NUMBER
307 , p_Dimension_Level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
308 , x_Dimension_Level_Rec IN OUT NOCOPY BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
309 , x_return_status OUT NOCOPY VARCHAR2
310 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
311 )
312 IS
313 l_Dimension_Level_Rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
314 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
315 BEGIN
316
317 BIS_DIMENSION_LEVEL_PVT.Value_ID_Conversion
318 ( p_api_version => 1.0
319 , p_Dimension_Level_Rec => p_Dimension_Level_Rec
320 , x_Dimension_Level_Rec => l_Dimension_Level_Rec
321 , x_return_status => x_return_status
322 , x_error_Tbl => x_error_tbl
323 );
324 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
325 -- POPULATE THE ERROR TABLE
326 --added the error message
327 l_error_tbl := x_error_tbl;
328 BIS_UTILITIES_PVT.Add_Error_Message
329 ( p_error_msg_name => 'BIS_INVALID_DIM_LEVEL_VALUE'
330 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
331 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Dimension_Level'
332 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
333 , p_error_table => l_error_tbl
334 , x_error_table => x_error_tbl
335 );
336 RAISE FND_API.G_EXC_ERROR;
337 end if;
338
339 BIS_DIMENSION_LEVEL_PVT.Retrieve_Dimension_Level
340 ( p_api_version => 1.0
341 , p_Dimension_Level_Rec => l_Dimension_Level_Rec
342 , x_Dimension_Level_Rec => x_Dimension_Level_Rec
343 , x_return_status => x_return_status
344 , x_error_Tbl => x_error_tbl
345 );
346
347
348 EXCEPTION
349 WHEN NO_DATA_FOUND THEN
350 x_return_status := FND_API.G_RET_STS_ERROR ;
351 when FND_API.G_EXC_ERROR then
352 x_return_status := FND_API.G_RET_STS_ERROR ;
353 when FND_API.G_EXC_UNEXPECTED_ERROR then
354 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
355 when others then
356 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
357 l_error_tbl := x_error_tbl;
358 BIS_UTILITIES_PVT.Add_Error_Message
359 ( p_error_msg_id => SQLCODE
360 , p_error_description => SQLERRM
361 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Dimension_Level'
362 , p_error_table => l_error_tbl
363 , x_error_table => x_error_tbl
364 );
365
366 END Retrieve_Dimension_Level;
367 --
368 --
369 Procedure Translate_Dimension_Level
370 ( p_api_version IN NUMBER
371 , p_commit IN VARCHAR2 := FND_API.G_FALSE
372 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
373 , p_Dimension_Level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
374 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
375 , x_return_status OUT NOCOPY VARCHAR2
376 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
377 )
378 IS
379 l_Dimension_Level_Rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
380 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
381 BEGIN
382
383 BIS_DIMENSION_LEVEL_PVT.Value_ID_Conversion
384 ( p_api_version => p_api_version
385 , p_Dimension_Level_Rec => p_Dimension_Level_Rec
386 , x_Dimension_Level_Rec => l_Dimension_Level_Rec
387 , x_return_status => x_return_status
388 , x_error_Tbl => x_error_tbl
389 );
390
391 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
392 --added last two parameters
393 l_error_tbl := x_error_tbl;
394 BIS_UTILITIES_PVT.Add_Error_Message
395 ( p_error_msg_name => 'BIS_INVALID_DIMENSION_LEVEL_ID'
396 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
397 , p_error_proc_name => G_PKG_NAME||'.Translate_Dimension_Level'
398 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
399 , p_error_table => l_error_tbl
400 , x_error_table => x_error_tbl
401 );
402 RAISE FND_API.G_EXC_ERROR;
403 end if;
404
405 l_Dimension_Level_Rec.Last_Update_Date := NVL(p_Dimension_Level_Rec.Last_Update_Date, SYSDATE);
406 BIS_DIMENSION_LEVEL_PVT.Translate_Dimension_Level
407 ( p_api_version => p_api_version
408 , p_commit => p_commit
409 , p_validation_level => p_validation_level
410 , p_Dimension_Level_Rec => l_Dimension_Level_Rec
411 , p_owner => p_owner
412 , x_return_status => x_return_status
413 , x_error_Tbl => x_error_Tbl
414 );
415
416 EXCEPTION
417 WHEN NO_DATA_FOUND THEN
418 x_return_status := FND_API.G_RET_STS_ERROR ;
419 when FND_API.G_EXC_ERROR then
420 x_return_status := FND_API.G_RET_STS_ERROR ;
421 when FND_API.G_EXC_UNEXPECTED_ERROR then
422 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
423 when others then
424 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
425 l_error_tbl := x_error_tbl;
426 BIS_UTILITIES_PVT.Add_Error_Message
427 ( p_error_msg_id => SQLCODE
428 , p_error_description => SQLERRM
429 , p_error_proc_name => G_PKG_NAME||'.Translate_Dimension_Level'
430 , p_error_table => l_error_tbl
431 , x_error_table => x_error_tbl
432 );
433
434 END Translate_Dimension_Level ;
435 --
436 Procedure Load_Dimension_Level
437 ( p_api_version IN NUMBER
438 , p_commit IN VARCHAR2 := FND_API.G_FALSE
439 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
443 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
440 , p_Dimension_Level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
441 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
442 , x_return_status OUT NOCOPY VARCHAR2
444 , p_force_mode IN BOOLEAN := FALSE
445 )
446 IS
447 l_Dimension_Level_Rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
448 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
449 l_return_status VARCHAR2(10);
450 l_return_msg VARCHAR2(32000);
451 l_count NUMBER;
452 l_ret_code BOOLEAN;
453 BEGIN
454
455 l_Dimension_level_Rec := p_Dimension_level_Rec;
456
457 BEGIN
458
459 BIS_DIMENSION_LEVEL_PVT.Value_ID_Conversion
460 ( p_api_version => p_api_version
461 , p_Dimension_Level_Rec => p_Dimension_Level_Rec
462 , x_Dimension_Level_Rec => l_Dimension_Level_Rec
463 , x_return_status => x_return_status
464 , x_error_Tbl => x_error_tbl
465 );
466
467 -- commented out NOCOPY since we want to continue anyway
468 /*
469 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
470 --added last two parameters
471 BIS_UTILITIES_PVT.Add_Error_Message
472 ( p_error_msg_name => 'BIS_INVALID_DIMENSION_LEVEL_ID'
473 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
474 , p_error_proc_name => G_PKG_NAME||'.Load_Dimension_Level'
475 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
476 , p_error_table => x_error_tbl
477 , x_error_table => x_error_tbl
478 );
479 RAISE FND_API.G_EXC_ERROR;
480 END IF;
481 */
482
483 l_Dimension_Level_Rec.Last_Update_Date := NVL(p_Dimension_Level_Rec.Last_Update_Date, SYSDATE);
484
485 SELECT COUNT(short_name) INTO l_count
486 FROM BIS_LEVELS
487 WHERE short_name = p_Dimension_Level_Rec.Dimension_Level_Short_Name;
488
489 IF (l_count > 0) THEN
490 BIS_UTIL.Validate_For_Update ( p_last_update_date => l_Dimension_Level_Rec.Last_Update_Date
491 ,p_owner => p_owner
492 ,p_force_mode => p_force_mode
493 ,p_table_name => 'BIS_LEVELS'
494 ,p_key_value => p_Dimension_Level_Rec.Dimension_Level_Short_Name
495 ,x_ret_code => l_ret_code
496 ,x_return_status => x_return_status
497 ,x_msg_data => l_return_msg
498 );
499 IF (l_ret_code) THEN
500 BIS_DIMENSION_LEVEL_PVT.Update_Dimension_Level
501 ( p_api_version => p_api_version
502 , p_commit => p_commit
503 , p_validation_level => p_validation_level
504 , p_Dimension_Level_Rec => l_Dimension_Level_Rec
505 , p_owner => p_owner
506 , x_return_status => x_return_status
507 , x_error_Tbl => x_error_Tbl
508 );
509 END IF;
510 ELSE
511 BIS_DIMENSION_LEVEL_PVT.Create_Dimension_Level
512 ( p_api_version => p_api_version
513 , p_commit => p_commit
514 , p_validation_level => p_validation_level
515 , p_Dimension_Level_Rec => l_Dimension_Level_Rec
516 , p_owner => p_owner
517 , x_return_status => x_return_status
518 , x_error_Tbl => x_error_Tbl
519 );
520
521 END IF;
522 /*
523 WHEN OTHERS THEN
524 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
525 --added last two parameters
526 BIS_UTILITIES_PVT.Add_Error_Message
527 ( p_error_msg_id => SQLCODE
528 , p_error_description => SQLERRM
529 , p_error_proc_name => G_PKG_NAME||'.Load_Dimension_Level'
530 , p_error_table => x_error_tbl
531 , x_error_table => x_error_tbl
532 );
533 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
534 */
535 END;
536
537 EXCEPTION
538 WHEN NO_DATA_FOUND THEN
539 x_return_status := FND_API.G_RET_STS_ERROR ;
540 when FND_API.G_EXC_ERROR then
541 x_return_status := FND_API.G_RET_STS_ERROR ;
542 when FND_API.G_EXC_UNEXPECTED_ERROR then
543 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
544 when others then
545 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
546 l_error_tbl := x_error_tbl;
547 BIS_UTILITIES_PVT.Add_Error_Message
548 ( p_error_msg_id => SQLCODE
549 , p_error_description => SQLERRM
550 , p_error_proc_name => G_PKG_NAME||'.Load_Dimension_Level'
551 , p_error_table => l_error_tbl
552 , x_error_table => x_error_tbl
553 );
554
555 END Load_Dimension_Level ;
556 --
557 PROCEDURE Create_Dimension_Level
558 ( p_api_version IN NUMBER
559 , p_commit IN VARCHAR2 := FND_API.G_FALSE
560 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
561 , p_Dimension_Level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
562 , x_return_status OUT NOCOPY VARCHAR2
563 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
564 )
565 IS
566 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
567 BEGIN
568
572 , p_commit => p_commit
569 BIS_DIMENSION_LEVEL_PVT.Create_Dimension_Level
570 (
571 p_api_version => p_api_version
573 , p_validation_level => p_validation_level
574 , p_Dimension_Level_Rec => p_Dimension_Level_Rec
575 , x_return_status => x_return_status
576 , x_error_Tbl => x_error_Tbl
577 );
578 EXCEPTION
579 WHEN NO_DATA_FOUND THEN
580 x_return_status := FND_API.G_RET_STS_ERROR ;
581 WHEN FND_API.G_EXC_ERROR THEN
582 x_return_status := FND_API.G_RET_STS_ERROR ;
583 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
584 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
585 WHEN OTHERS THEN
586 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
587 l_error_tbl := x_error_tbl;
588 BIS_UTILITIES_PVT.Add_Error_Message
589 ( p_error_msg_id => SQLCODE
590 , p_error_description => SQLERRM
591 , p_error_proc_name => G_PKG_NAME||'.Create_Dimension_Level'
592 , p_error_table => l_error_tbl
593 , x_error_table => x_error_tbl
594 );
595 END Create_Dimension_Level;
596 --
597 PROCEDURE Update_Dimension_Level
598 ( p_api_version IN NUMBER
599 , p_commit IN VARCHAR2 := FND_API.G_FALSE
600 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
601 , p_Dimension_Level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
602 , x_return_status OUT NOCOPY VARCHAR2
603 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
604 )
605 IS
606 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
607 BEGIN
608
609 BIS_DIMENSION_LEVEL_PVT.Update_Dimension_Level
610 (
611 p_api_version => p_api_version
612 , p_commit => p_commit
613 , p_validation_level => p_validation_level
614 , p_Dimension_Level_Rec => p_Dimension_Level_Rec
615 , x_return_status => x_return_status
616 , x_error_Tbl => x_error_Tbl
617 );
618 EXCEPTION
619 WHEN NO_DATA_FOUND THEN
620 x_return_status := FND_API.G_RET_STS_ERROR ;
621 WHEN FND_API.G_EXC_ERROR THEN
622 x_return_status := FND_API.G_RET_STS_ERROR ;
623 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
624 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
625 WHEN OTHERS THEN
626 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
627 l_error_tbl := x_error_tbl;
628 BIS_UTILITIES_PVT.Add_Error_Message
629 ( p_error_msg_id => SQLCODE
630 , p_error_description => SQLERRM
631 , p_error_proc_name => G_PKG_NAME||'.Update_Dimension_Level'
632 , p_error_table => l_error_tbl
633 , x_error_table => x_error_tbl
634 );
635 END Update_Dimension_Level;
636 --
637 PROCEDURE Delete_Dimension_Level
638 (
639 p_commit IN VARCHAR2 := FND_API.G_FALSE
640 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
641 , p_Dimension_Level_Rec IN BIS_Dimension_Level_PUB.Dimension_Level_Rec_Type
642 , x_return_status OUT NOCOPY VARCHAR2
643 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
644 ) IS
645 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
646 BEGIN
647 BIS_DIMENSION_LEVEL_PVT.Delete_Dimension_Level
648 (
649 p_commit => p_commit
650 , p_validation_level => p_validation_level
651 , p_Dimension_Level_Rec => p_Dimension_Level_Rec
652 , x_return_status => x_return_status
653 , x_error_Tbl => x_error_Tbl
654 );
655 EXCEPTION
656 WHEN FND_API.G_EXC_ERROR THEN
657 x_return_status := FND_API.G_RET_STS_ERROR ;
658 WHEN OTHERS THEN
659 l_error_tbl := x_error_tbl;
660 BIS_UTILITIES_PVT.Add_Error_Message
661 ( p_error_msg_id => SQLCODE
662 , p_error_description => SQLERRM
663 , p_error_proc_name => G_PKG_NAME||'.Delete_Dimension_Level'
664 , p_error_table => l_error_tbl
665 , x_error_table => x_error_tbl
666 );
667 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
668 END Delete_Dimension_Level;
669
670 --============================================================================
671 -- Load into BSC's bsc_sys_dim_levels_b/tl table by calling update/insert API of BSC
672
673 PROCEDURE load_dim_levels(
674 p_commit IN VARCHAR2 := FND_API.G_FALSE
675 ,p_bsc_pmf_dim_rec IN BSC_PMF_UI_API_PUB.Bsc_Pmf_Dim_Rec_Type
676 ,p_Bsc_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
677 ,x_return_status OUT NOCOPY VARCHAR2
678 ,x_msg_count OUT NOCOPY NUMBER
679 ,x_msg_data OUT NOCOPY VARCHAR2
680 )
681 IS
682 l_Bsc_Pmf_Dim_Rec BSC_PMF_UI_API_PUB.Bsc_Pmf_Dim_Rec_Type;
683 l_Bsc_Dim_Rec BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
684
685 l_alternate_level_view VARCHAR2(80);
686 l_edw_sql VARCHAR2(1000);
687 l_dim_lvl_sql VARCHAR2(2000);
688 l_prefix VARCHAR2(2000);
689 l_dimshortname VARCHAR2(80);
690 l_rel_count NUMBER;
691 TYPE Recdc_value IS REF CURSOR;
692 dl_value Recdc_value;
693 l_count NUMBER;
694 BEGIN
698 l_Bsc_Pmf_Dim_Rec.Dimension_Level_Short_Name := p_Bsc_Pmf_Dim_Rec.Dimension_Level_Short_Name;
695 l_Bsc_Pmf_Dim_Rec.Dimension_Long_Name := p_Bsc_Pmf_Dim_Rec.Dimension_Long_Name;
696 l_Bsc_Pmf_Dim_Rec.Dimension_Short_Name := p_Bsc_Pmf_Dim_Rec.Dimension_Short_Name;
697 l_Bsc_Pmf_Dim_Rec.Dimension_Level_Long_Name := p_Bsc_Pmf_Dim_Rec.Dimension_Level_Long_Name;
699
700 -- If the source is oltp, retrieve all values in 1 sql, else if EDW, it will be overwritten below.
701 SELECT source, name, level_values_view_name, 'ID', 'value'
702 INTO l_Bsc_Pmf_Dim_Rec.Dimension_Level_Source,
703 l_Bsc_Pmf_Dim_Rec.Dimension_Level_Long_Name,
704 l_Bsc_Pmf_Dim_Rec.Dimension_Level_View_Name,
705 l_Bsc_Pmf_Dim_Rec.Dimension_Level_Pk_Key,
706 l_Bsc_Pmf_Dim_Rec.Dimension_Level_Name_Column
707 FROM bis_levels_vl
708 WHERE UPPER(short_name) = UPPER(p_Bsc_Pmf_Dim_Rec.Dimension_Level_Short_Name);
709
710 l_Bsc_Pmf_Dim_Rec.Dimension_Level_Disp_Size := 250;
711
712 l_Bsc_Dim_Rec.Bsc_Level_Short_Name := l_Bsc_Pmf_Dim_Rec.Dimension_Level_Short_Name;
713 l_Bsc_Dim_Rec.Bsc_Dim_Level_Long_Name := l_Bsc_Pmf_Dim_Rec.Dimension_Level_Long_Name;
714 l_Bsc_Dim_Rec.Bsc_Level_Disp_Key_Size := l_Bsc_Pmf_Dim_Rec.Dimension_Level_Disp_Size;
715
716 l_Bsc_Dim_Rec.Bsc_Level_Name := bsc_utility.get_valid_bsc_master_tbl_name(l_Bsc_Dim_Rec.Bsc_Level_Short_Name);
717 l_Bsc_Dim_Rec.Bsc_Level_View_Name := l_Bsc_Pmf_Dim_Rec.Dimension_Level_View_Name;
718 IF (l_Bsc_Pmf_Dim_Rec.Dimension_Level_Source = 'OLTP') THEN
719 l_Bsc_Dim_Rec.Bsc_Level_Pk_Key := l_Bsc_Pmf_Dim_Rec.Dimension_Level_Pk_Key;
720 l_Bsc_Dim_Rec.Bsc_Pk_Col := REPLACE(l_Bsc_Pmf_Dim_Rec.Dimension_Level_Short_Name,' ','_');
721 ELSE
722 l_Bsc_Dim_Rec.Bsc_Level_Pk_Key := l_Bsc_Pmf_Dim_Rec.Dimension_Level_Pk_Key_edw;
723 l_Bsc_Dim_Rec.Bsc_Pk_Col := l_Bsc_Pmf_Dim_Rec.Dimension_Level_Pk_Key_edw;
724 END IF;
725 l_Bsc_Dim_Rec.Bsc_Source := 'PMF';
726 l_Bsc_Dim_Rec.Source := l_Bsc_Pmf_Dim_Rec.Dimension_Level_Source;
727 l_Bsc_Dim_Rec.Bsc_Level_Name_Column := l_Bsc_Pmf_Dim_Rec.Dimension_Level_Name_Column;
728 l_Bsc_Dim_Rec.Bsc_Language := 'US';
729 l_Bsc_Dim_Rec.Bsc_Source_Language := userenv('LANG'); --'US';
730
731 -- Total display name, Comparison display name and Help
732 l_Bsc_Dim_Rec.Bsc_Dim_Comp_Disp_Name := p_Bsc_Dim_Level_Rec.Bsc_Dim_Comp_Disp_Name;
733 l_Bsc_Dim_Rec.Bsc_Dim_Tot_Disp_Name := p_Bsc_Dim_Level_Rec.Bsc_Dim_Tot_Disp_Name;
734 l_Bsc_Dim_Rec.Bsc_Dim_Level_Help := p_Bsc_Dim_Level_Rec.Bsc_Dim_Level_Help;
735
736 -- Who columns are populated from the lct file.
737 l_Bsc_Dim_Rec.Bsc_Created_By := p_Bsc_Dim_Level_Rec.Bsc_Created_By;
738 l_Bsc_Dim_Rec.Bsc_Last_Updated_By := p_Bsc_Dim_Level_Rec.Bsc_Last_Updated_By;
739
740 -- Defaulting all dim levels ,set this to bypass bsc view creation #3264490
741 l_Bsc_Dim_Rec.Bsc_Level_Table_Type := -1;
742
743 IF ( (l_Bsc_Dim_Rec.Bsc_Level_Pk_Key IS NULL) AND (l_Bsc_Dim_Rec.Bsc_Pk_Col IS NULL) ) THEN
744 l_Bsc_Dim_Rec.Bsc_Level_Table_Type := -1;
745 l_Bsc_Dim_Rec.Bsc_Level_Pk_Key := SUBSTR(l_Bsc_Dim_Rec.bsc_Level_Short_Name, 1, 23) ||'_PK_COL';
746 l_Bsc_Dim_Rec.Bsc_Pk_Col := l_Bsc_Dim_Rec.Bsc_Level_Pk_Key;
747 END IF;
748
749 -- This will either update/insert dimension levels
750
751 l_Bsc_Dim_Rec.Bsc_Last_Update_Date := NVL(p_Bsc_Dim_Level_Rec.Bsc_Last_Update_Date, SYSDATE);
752
753 BSC_DIMENSION_LEVELS_PUB.load_dimension_level(
754 p_commit => p_Commit
755 ,p_Dim_Level_Rec => l_Bsc_Dim_Rec
756 ,x_return_status => x_return_status
757 ,x_msg_count => x_msg_count
758 ,x_msg_data => x_msg_data
759 );
760
761
762 EXCEPTION
763 WHEN NO_DATA_FOUND THEN
764 IF (dl_value%ISOPEN) THEN
765 CLOSE dl_value;
766 END IF;
767 x_return_status := FND_API.G_RET_STS_ERROR ;
768 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
769 ,p_data => x_msg_data);
770 WHEN FND_API.G_EXC_ERROR THEN
771 IF (dl_value%ISOPEN) THEN
772 CLOSE dl_value;
773 END IF;
774 x_return_status := FND_API.G_RET_STS_ERROR ;
775 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
776 ,p_data => x_msg_data);
777 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
778 IF (dl_value%ISOPEN) THEN
779 CLOSE dl_value;
780 END IF;
781 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
782 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
783 ,p_data => x_msg_data);
784 WHEN OTHERS THEN
785 IF (dl_value%ISOPEN) THEN
786 CLOSE dl_value;
787 END IF;
788 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
789 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
790 ,p_data => x_msg_data);
791
792 END load_dim_levels;
793
794
795 --============================================================================
796 -- Load into BSC's bsc_sys_dim_levels_by_group table by calling update/insert API of BSC
797
798 PROCEDURE load_dim_levels_in_group(
799 p_commit IN VARCHAR2 := FND_API.G_FALSE
800 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
801 ,p_Bsc_Pmf_Dim_Rec IN BSC_PMF_UI_API_PUB.Bsc_Pmf_Dim_Rec_Type
802 ,x_return_status OUT NOCOPY VARCHAR2
806 IS
803 ,x_msg_count OUT NOCOPY NUMBER
804 ,x_msg_data OUT NOCOPY VARCHAR2
805 )
807 l_Bsc_Dim_Group_Rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
808 BEGIN
809
810 l_Bsc_Dim_Group_Rec.Bsc_Dim_Level_Group_Name := p_Bsc_Pmf_Dim_Rec.Dimension_Long_Name;
811 l_Bsc_Dim_Group_Rec.Bsc_Dim_Level_Group_Short_Name := p_Bsc_Pmf_Dim_Rec.Dimension_Short_Name;
812 l_Bsc_Dim_Group_Rec.Bsc_Language := p_Dim_Grp_Rec.Bsc_Language;
813 l_Bsc_Dim_Group_Rec.Bsc_Source_Language := p_Dim_Grp_Rec.Bsc_Source_Language;
814 l_Bsc_Dim_Group_Rec.Bsc_Group_Level_Default_Value := p_Dim_Grp_Rec.Bsc_Group_Level_Default_Value;
815 l_Bsc_Dim_Group_Rec.Bsc_Group_Level_Default_Type := p_Dim_Grp_Rec.Bsc_Group_Level_Default_Type;
816 l_Bsc_Dim_Group_Rec.Bsc_Group_Level_Filter_Col := p_Dim_Grp_Rec.Bsc_Group_Level_Filter_Col;
817 l_Bsc_Dim_Group_Rec.Bsc_Group_Level_Filter_Value := p_Dim_Grp_Rec.Bsc_Group_Level_Filter_Value;
818 l_Bsc_Dim_Group_Rec.Bsc_Group_Level_No_Items := p_Dim_Grp_Rec.Bsc_Group_Level_No_Items;
819 l_Bsc_Dim_Group_Rec.Bsc_Group_Level_Parent_In_Tot := p_Dim_Grp_Rec.Bsc_Group_Level_Parent_In_Tot;
820 l_Bsc_Dim_Group_Rec.Bsc_Group_Level_Total_Flag := p_Dim_Grp_Rec.Bsc_Group_Level_Total_Flag;
821 l_Bsc_Dim_Group_Rec.Bsc_Group_Level_Comp_Flag := p_Dim_Grp_Rec.Bsc_Group_Level_Comp_Flag;
822 l_Bsc_Dim_Group_Rec.Bsc_Group_Level_Where_Clause := p_Dim_Grp_Rec.Bsc_Group_Level_Where_Clause;
823 l_Bsc_Dim_Group_Rec.Bsc_Dim_Level_Index := p_Dim_Grp_Rec.Bsc_Dim_Level_Index;
824
825 -- call BSC API to load dim levels in group table (either insert/update)
826 BSC_DIMENSION_GROUPS_PUB.load_dim_levels_in_group(
827 p_commit => p_commit
828 ,p_Bsc_Pmf_Dim_Rec => p_Bsc_Pmf_Dim_Rec
829 ,p_Dim_Grp_Rec => l_bsc_dim_group_rec
830 ,x_return_status => x_return_status
831 ,x_msg_count => x_msg_count
832 ,x_msg_data => x_msg_data
833 );
834
835 EXCEPTION
836 WHEN NO_DATA_FOUND THEN
837 x_return_status := FND_API.G_RET_STS_ERROR ;
838 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
839 ,p_data => x_msg_data);
840 WHEN FND_API.G_EXC_ERROR THEN
841 x_return_status := FND_API.G_RET_STS_ERROR ;
842 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
843 ,p_data => x_msg_data);
844 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
845 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
846 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
847 ,p_data => x_msg_data);
848 WHEN OTHERS THEN
849 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
850 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
851 ,p_data => x_msg_data);
852 END load_dim_levels_in_group;
853
854
855 --=============================================================================
856 PROCEDURE Load_Dimension_Level (
857 p_Commit IN VARCHAR2 := FND_API.G_FALSE
858 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
859 ,p_Bsc_Pmf_Dim_Rec IN BSC_PMF_UI_API_PUB.Bsc_Pmf_Dim_Rec_Type
860 ,p_Bsc_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
861 ,x_return_status OUT NOCOPY VARCHAR2
862 ,x_msg_count OUT NOCOPY NUMBER
863 ,x_msg_data OUT NOCOPY VARCHAR2
864 ,p_force_mode IN BOOLEAN := FALSE
865 )
866 IS
867 CURSOR c_bsc_dim_obj_exists IS
868 SELECT short_name FROM bsc_sys_dim_levels_b
869 WHERE short_name = p_Bsc_Pmf_Dim_Rec.Dimension_Level_Short_Name;
870
871 CURSOR c_dim_dimobj_mapping_exists IS
872 SELECT 1
873 FROM BSC_SYS_DIM_LEVELS_BY_GROUP A,
874 BSC_SYS_DIM_LEVELS_B B,
875 BIS_DIMENSIONS C
876 WHERE C.short_name = p_Bsc_Pmf_Dim_Rec.Dimension_Short_Name
877 AND B.short_name = p_Bsc_Pmf_Dim_Rec.Dimension_Level_Short_Name
878 AND A.dim_group_id = C.dim_grp_id
879 AND A.dim_level_id = B.dim_level_id;
880
881 l_short_name BIS_LEVELS.Short_Name%TYPE;
882 l_owner_name VARCHAR2(100);
883 l_last_update_date DATE;
884 l_ret_code BOOLEAN := TRUE;
885 BEGIN
886 /*
887 Bug#4172055: Even though BSC_SYS_DIM_LEVELS_BY_GROUP does not has LUD column
888 still the upload of a dimension object should not modify this table unless
889 other LUDs are validated. For this, LUD check is done here both for
890 BSC_SYS_DIM_LEVELS_B and BSC_SYS_DIM_LEVELS_BY_GROUP, based on the LUD
891 in BSC_SYS_DIM_LEVELS_B table.
892 */
893
894 IF (c_bsc_dim_obj_exists%ISOPEN) THEN
895 CLOSE c_bsc_dim_obj_exists;
896 END IF;
897 OPEN c_bsc_dim_obj_exists;
898 FETCH c_bsc_dim_obj_exists INTO l_short_name;
899
900 IF (c_bsc_dim_obj_exists%FOUND) THEN -- Update mode
901 l_last_update_date := NVL(p_Bsc_Dim_Level_Rec.Bsc_Last_Update_Date, SYSDATE);
905 ( p_last_update_date => l_last_update_date
902 l_owner_name := BIS_UTILITIES_PUB.Get_Owner_Name(p_Bsc_Dim_Level_Rec.Bsc_Last_Updated_By);
903
904 /*BIS_UTIL.Validate_For_Update
906 , p_owner => l_owner_name
907 , p_force_mode => p_force_mode
908 , p_table_name => 'BSC_SYS_DIM_LEVELS_B'
909 , p_key_value => p_Bsc_Pmf_Dim_Rec.Dimension_Level_Short_Name
910 , x_ret_code => l_ret_code
911 , x_return_status => x_return_status
912 , x_msg_data => x_msg_data);*/
913 l_ret_code := TRUE;
914 END IF;
915
916 IF (l_ret_code) THEN
917
918 -- Load into BSC's bsc_sys_dim_levels_b/tl table by calling update/insert API of BSC
919 load_dim_levels(
920 p_commit => p_commit
921 ,p_bsc_pmf_dim_rec => p_bsc_pmf_dim_rec
922 ,p_Bsc_Dim_Level_Rec => p_Bsc_Dim_Level_Rec
923 ,x_return_status => x_return_status
924 ,x_msg_count => x_msg_count
925 ,x_msg_data => x_msg_data
926 );
927
928 -- Load into BSC's bsc_sys_dim_levels_by_group table by calling update/insert API of BSC
929 load_dim_levels_in_group(
930 p_commit => p_commit
931 ,p_Dim_Grp_Rec => p_Dim_Grp_Rec
932 ,p_Bsc_Pmf_Dim_Rec => p_Bsc_Pmf_Dim_Rec
933 ,x_return_status => x_return_status
934 ,x_msg_count => x_msg_count
935 ,x_msg_data => x_msg_data
936 );
937
938 ELSE
939
940 IF (c_dim_dimobj_mapping_exists%ISOPEN) THEN
941 CLOSE c_dim_dimobj_mapping_exists;
942 END IF;
943 OPEN c_dim_dimobj_mapping_exists;
944 FETCH c_dim_dimobj_mapping_exists INTO l_short_name;
945
946 IF (c_dim_dimobj_mapping_exists%NOTFOUND) THEN
947
948 -- Load into BSC's bsc_sys_dim_levels_by_group table by calling update/insert API of BSC
949 load_dim_levels_in_group(
950 p_commit => p_commit
951 ,p_Dim_Grp_Rec => p_Dim_Grp_Rec
952 ,p_Bsc_Pmf_Dim_Rec => p_Bsc_Pmf_Dim_Rec
953 ,x_return_status => x_return_status
954 ,x_msg_count => x_msg_count
955 ,x_msg_data => x_msg_data
956 );
957
958 END IF;
959
960 CLOSE c_dim_dimobj_mapping_exists;
961
962 END IF;
963
964 CLOSE c_bsc_dim_obj_exists;
965
966 EXCEPTION
967 WHEN NO_DATA_FOUND THEN
968 x_return_status := FND_API.G_RET_STS_ERROR ;
969 IF(x_msg_data IS NULL) THEN
970 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
971 ,p_data => x_msg_data);
972 END IF;
973 IF(c_bsc_dim_obj_exists%ISOPEN) THEN
974 CLOSE c_bsc_dim_obj_exists;
975 END IF;
976 IF(c_dim_dimobj_mapping_exists%ISOPEN) THEN
977 CLOSE c_dim_dimobj_mapping_exists;
978 END IF;
979 WHEN FND_API.G_EXC_ERROR THEN
980 x_return_status := FND_API.G_RET_STS_ERROR ;
981 IF(x_msg_data IS NULL) THEN
982 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
983 ,p_data => x_msg_data);
984 END IF;
985 IF(c_bsc_dim_obj_exists%ISOPEN) THEN
986 CLOSE c_bsc_dim_obj_exists;
987 END IF;
988 IF(c_dim_dimobj_mapping_exists%ISOPEN) THEN
989 CLOSE c_dim_dimobj_mapping_exists;
990 END IF;
991 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
992 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
993 IF(x_msg_data IS NULL) THEN
994 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
995 ,p_data => x_msg_data);
996 END IF;
997 IF(c_bsc_dim_obj_exists%ISOPEN) THEN
998 CLOSE c_bsc_dim_obj_exists;
999 END IF;
1000 IF(c_dim_dimobj_mapping_exists%ISOPEN) THEN
1001 CLOSE c_dim_dimobj_mapping_exists;
1002 END IF;
1003 WHEN OTHERS THEN
1004 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1005 IF(x_msg_data IS NULL) THEN
1006 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1007 ,p_data => x_msg_data);
1008 END IF;
1009 IF(c_bsc_dim_obj_exists%ISOPEN) THEN
1010 CLOSE c_bsc_dim_obj_exists;
1011 END IF;
1012 IF(c_dim_dimobj_mapping_exists%ISOPEN) THEN
1013 CLOSE c_dim_dimobj_mapping_exists;
1014 END IF;
1015 END;
1016
1017 --=============================================================================
1018
1019 PROCEDURE Trans_DimObj_By_Given_Lang
1020 (
1021 p_commit IN VARCHAR2 := FND_API.G_FALSE
1022 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1023 , p_Dimension_Level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
1024 , x_return_status OUT NOCOPY VARCHAR2
1025 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1026 ) IS
1027 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1028 BEGIN
1029 BIS_DIMENSION_LEVEL_PVT.Trans_DimObj_By_Given_Lang
1030 (
1031 p_commit => p_commit
1032 , p_validation_level => p_validation_level
1033 , p_Dimension_Level_Rec => p_Dimension_Level_Rec
1034 , x_return_status => x_return_status
1035 , x_error_Tbl => x_error_Tbl
1036 );
1037
1038 EXCEPTION
1039 WHEN NO_DATA_FOUND THEN
1040 x_return_status := FND_API.G_RET_STS_ERROR ;
1041 WHEN FND_API.G_EXC_ERROR THEN
1042 x_return_status := FND_API.G_RET_STS_ERROR ;
1046 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1043 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1044 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1045 WHEN OTHERS THEN
1047 l_error_tbl := x_error_tbl;
1048 BIS_UTILITIES_PVT.Add_Error_Message
1049 ( p_error_msg_id => SQLCODE
1050 , p_error_description => SQLERRM
1051 , p_error_proc_name => G_PKG_NAME||'.Trans_DimObj_By_Given_Lang'
1052 , p_error_table => l_error_tbl
1053 , x_error_table => x_error_tbl
1054 );
1055 END Trans_DimObj_By_Given_Lang;
1056
1057 --=============================================================================
1058 FUNCTION Return_Master_Level (
1059 p_dim_level_short_name IN VARCHAR2
1060 ) RETURN VARCHAR2 IS
1061
1062 CURSOR c_master IS
1063 SELECT master_level
1064 FROM bis_levels
1065 WHERE short_name = p_dim_level_short_name;
1066
1067 l_master_dim_level VARCHAR2(30);
1068
1069 BEGIN
1070 IF (c_master%ISOPEN) THEN
1071 CLOSE c_master;
1072 END IF;
1073 OPEN c_master;
1074 FETCH c_master INTO l_master_dim_level;
1075 CLOSE c_master;
1076
1077 RETURN l_master_dim_level;
1078
1079 EXCEPTION
1080 WHEN OTHERS THEN
1081 IF (c_master%ISOPEN) THEN
1082 CLOSE c_master;
1083 END IF;
1084 RETURN NULL;
1085 END;
1086 --=============================================================================
1087 FUNCTION Is_Related_By_Master (
1088 p_dim_level_short_name IN VARCHAR2
1089 ,p_master_dim_level_short_name IN VARCHAR2
1090 ) RETURN VARCHAR2 IS
1091
1092 l_master1 VARCHAR2(30);
1093 l_master2 VARCHAR2(30);
1094
1095 BEGIN
1096
1097 l_master1 := Return_Master_Level(p_dim_level_short_name);
1098
1099 IF ( (p_master_dim_level_short_name IS NULL) OR (l_master1 IS NULL) ) THEN
1100 RETURN C_NO_MASTER;
1101 ELSE
1102 l_master2 := Return_Master_Level(p_master_dim_level_short_name);
1103 IF (l_master1 = l_master2) THEN
1104 RETURN C_SIBLING;
1105 ELSIF (l_master1 = p_master_dim_level_short_name) THEN
1106 RETURN C_PARENT;
1107 ELSE
1108 RETURN C_NO_REL;
1109 END IF;
1110 END IF;
1111
1112 EXCEPTION
1113 WHEN OTHERS THEN
1114 RETURN C_NO_REL;
1115 END;
1116
1117 --=============================================================================
1118 Procedure Retrieve_Dimension_Level_Wrap
1119 ( p_dim_level_short_name IN VARCHAR2
1120 , p_master_dim_level_short_name IN VARCHAR2
1121 , p_dim_short_name IN VARCHAR2
1122 , x_dim_level_name OUT NOCOPY VARCHAR2
1123 , x_dim_level_desc OUT NOCOPY VARCHAR2
1124 , x_default_search OUT NOCOPY VARCHAR2
1125 , x_long_lov OUT NOCOPY VARCHAR2
1126 , x_master_level OUT NOCOPY VARCHAR2
1127 , x_is_related_by_master OUT NOCOPY VARCHAR2
1128 , x_view_object_name OUT NOCOPY VARCHAR2
1129 , x_default_values_api OUT NOCOPY VARCHAR2
1130 , x_enabled OUT NOCOPY VARCHAR2
1131 , x_hide OUT NOCOPY VARCHAR2
1132 , x_dim_group_id OUT NOCOPY NUMBER
1133 , x_dim_level_id OUT NOCOPY NUMBER
1134 , x_dim_level_index OUT NOCOPY NUMBER
1135 , x_total_flag OUT NOCOPY NUMBER
1136 , x_total_disp_name OUT NOCOPY VARCHAR2
1137 , x_dim_level_where_clause OUT NOCOPY VARCHAR2
1138 , x_comparison_flag OUT NOCOPY NUMBER
1139 , x_comp_disp_name OUT NOCOPY VARCHAR2
1140 , x_filter_column OUT NOCOPY VARCHAR2
1141 , x_filter_value OUT NOCOPY NUMBER
1142 , x_default_value OUT NOCOPY VARCHAR2
1143 , x_default_type OUT NOCOPY NUMBER
1144 , x_parent_in_total OUT NOCOPY NUMBER
1145 , x_no_items OUT NOCOPY NUMBER
1146 , x_pmf_dim_id OUT NOCOPY NUMBER
1147 , x_pmf_dim_level_id OUT NOCOPY NUMBER
1148 , x_comparison_label_code OUT NOCOPY VARCHAR2
1149 , x_level_values_view_name OUT NOCOPY VARCHAR2
1150 , x_source OUT NOCOPY VARCHAR2
1151 , x_attribute_code OUT NOCOPY VARCHAR2
1152 , x_application_id OUT NOCOPY NUMBER
1153 , x_drill_to_form_function OUT NOCOPY VARCHAR2
1154 , x_dim_name OUT NOCOPY VARCHAR2
1155 , x_return_status OUT NOCOPY VARCHAR2
1156 , x_msg_count OUT NOCOPY VARCHAR2
1157 , x_msg_data OUT NOCOPY VARCHAR2
1158 )
1159 IS
1160 l_Dimension_Level_Rec_In BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
1161 l_Dimension_Level_Rec_Out BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
1162 l_return_status VARCHAR2(10);
1163 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1164 l_count NUMBER;
1165 BEGIN
1166
1167 FND_MSG_PUB.Initialize;
1168 x_return_status := FND_API.G_RET_STS_SUCCESS;
1169
1170 l_Dimension_Level_Rec_In.dimension_level_short_name := p_dim_level_short_name;
1171
1172 BIS_DIMENSION_LEVEL_PUB.Retrieve_Dimension_Level
1173 ( p_api_version => 1.0
1174 , p_Dimension_Level_Rec => l_Dimension_Level_Rec_In
1175 , x_Dimension_Level_Rec => l_Dimension_Level_Rec_Out
1176 , x_return_status => l_return_status
1177 , x_error_Tbl => l_error_tbl
1178 );
1179
1180 IF NOT ( (l_return_status = 'S') OR (l_return_status IS NULL) ) THEN
1181 IF (l_error_tbl.COUNT > 0) THEN
1182 FND_MESSAGE.SET_NAME('BIS',l_error_tbl(l_count).error_msg_name);
1183 FND_MSG_PUB.Add;
1184 END IF;
1185 RAISE FND_API.G_EXC_ERROR;
1186 END IF;
1187
1188 x_pmf_dim_id := l_Dimension_Level_Rec_Out.dimension_id;
1189 x_pmf_dim_level_id := l_Dimension_Level_Rec_Out.dimension_level_id;
1190 x_dim_name := l_Dimension_Level_Rec_Out.dimension_name;
1191 x_default_search := l_Dimension_Level_Rec_Out.default_search;
1192 x_long_lov := l_Dimension_Level_Rec_Out.Long_Lov;
1193 x_master_level := l_Dimension_Level_Rec_Out.Master_Level;
1194 x_view_object_name := l_Dimension_Level_Rec_Out.View_Object_Name;
1198 x_dim_level_name := l_Dimension_Level_Rec_Out.Dimension_Level_Name;
1195 x_default_values_api := l_Dimension_Level_Rec_Out.Default_Values_Api;
1196 x_enabled := l_Dimension_Level_Rec_Out.Enabled;
1197 x_hide := l_Dimension_Level_Rec_Out.Hide;
1199 x_dim_level_desc := l_Dimension_Level_Rec_Out.Description;
1200 x_comparison_label_code := l_Dimension_Level_Rec_Out.Comparison_Label_Code;
1201 x_level_values_view_name:= l_Dimension_Level_Rec_Out.Level_Values_View_Name;
1202 x_source := l_Dimension_Level_Rec_Out.source;
1203 x_attribute_code := l_Dimension_Level_Rec_Out.Attribute_Code;
1204 x_application_id := l_Dimension_Level_Rec_Out.Application_ID;
1205 x_drill_to_form_function := l_Dimension_Level_Rec_Out.Drill_To_Form_Function;
1206
1207 BSC_DIMENSION_GROUPS_PUB.Retrieve_Sys_Dim_Lvls_Grp_Wrap (
1208 p_dim_level_shortname => p_dim_level_short_name
1209 ,p_dim_shortname => p_dim_short_name
1210 ,x_dim_group_id => x_dim_group_id
1214 ,x_total_disp_name => x_total_disp_name
1211 ,x_dim_level_id => x_dim_level_id
1212 ,x_dim_level_index => x_dim_level_index
1213 ,x_total_flag => x_total_flag
1215 ,x_dim_level_where_clause => x_dim_level_where_clause
1216 ,x_comparison_flag => x_comparison_flag
1217 ,x_comp_disp_name => x_comp_disp_name
1218 ,x_filter_column => x_filter_column
1219 ,x_filter_value => x_filter_value
1220 ,x_default_value => x_default_value
1221 ,x_default_type => x_default_type
1222 ,x_parent_in_total => x_parent_in_total
1223 ,x_no_items => x_no_items
1224 ,x_return_status => x_return_status
1225 ,x_msg_count => x_msg_count
1226 ,x_msg_data => x_msg_data
1227 );
1228
1229 IF NOT ( (l_return_status = 'S') OR (l_return_status IS NULL) ) THEN
1230 RAISE FND_API.G_EXC_ERROR;
1231 END IF;
1232
1233 x_is_related_by_master := Is_Related_By_Master (
1234 p_dim_level_short_name => p_dim_level_short_name
1235 ,p_master_dim_level_short_name => p_master_dim_level_short_name);
1236
1237 EXCEPTION
1238 WHEN FND_API.G_EXC_ERROR THEN
1239 IF (x_msg_data IS NULL) THEN
1240 FND_MSG_PUB.Count_And_Get(
1241 p_encoded => 'F'
1242 ,p_count => x_msg_count
1243 ,p_data => x_msg_data
1244 );
1245 END IF;
1246 x_return_status := FND_API.G_RET_STS_ERROR;
1247 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1248 IF (x_msg_data IS NULL) THEN
1249 FND_MSG_PUB.Count_And_Get(
1250 p_encoded => 'F'
1251 ,p_count => x_msg_count
1252 ,p_data => x_msg_data
1253 );
1254 END IF;
1255 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1256 WHEN OTHERS THEN
1257 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1258 IF (x_msg_data IS NOT NULL) THEN
1259 x_msg_data := x_msg_data||' -> BIS_DIMENSION_LEVEL_PUB.Retrieve_Dimension_Level_Wrap ';
1260 ELSE
1261 x_msg_data := SQLERRM||' at BIS_DIMENSION_LEVEL_PUB.Retrieve_Dimension_Level_Wrap ';
1262 END IF;
1263 END Retrieve_Dimension_Level_Wrap;
1264 --
1265 --=============================================================================
1266 --
1267
1268 -- get customized values for name , description and enabled
1269 FUNCTION get_customized_name( p_dim_level_id IN NUMBER) RETURN VARCHAR2 AS
1270 BEGIN
1271 RETURN BIS_DIMENSION_LEVEL_PVT.get_customized_name( p_dim_level_id => p_dim_level_id);
1272 END get_customized_name;
1273
1274 FUNCTION get_customized_description( p_dim_level_id IN NUMBER) RETURN VARCHAR2 AS
1275 BEGIN
1276 RETURN BIS_DIMENSION_LEVEL_PVT.get_customized_description( p_dim_level_id => p_dim_level_id);
1277 END get_customized_description;
1278
1279 FUNCTION get_customized_enabled( p_dim_level_id IN NUMBER) RETURN VARCHAR2 AS
1280 BEGIN
1281 RETURN BIS_DIMENSION_LEVEL_PVT.get_customized_enabled( p_dim_level_id => p_dim_level_id);
1282 END get_customized_enabled;
1283
1284 FUNCTION get_customized_enabled( p_dim_level_sht_name IN VARCHAR2) RETURN VARCHAR2 AS
1285 BEGIN
1286 RETURN BIS_DIMENSION_LEVEL_PVT.get_customized_enabled( p_dim_level_sht_name => p_dim_level_sht_name);
1287 END get_customized_enabled;
1288
1289 PROCEDURE validate_disabling (p_dim_level_id IN NUMBER) IS
1290 BEGIN
1291 BIS_DIMENSION_LEVEL_PVT.validate_disabling(p_dim_level_id => p_dim_level_id );
1292 END validate_disabling;
1293
1294 Procedure Retrieve_Dim_Level_Cust_Wrap
1295 ( p_dim_level_short_name IN VARCHAR2
1296 , p_dim_short_name IN VARCHAR2
1297 , x_dim_level_cust_name OUT NOCOPY VARCHAR2
1298 , x_dim_level_cust_desc OUT NOCOPY VARCHAR2
1299 , x_dim_level_cust_enabled OUT NOCOPY VARCHAR2
1300 ) AS
1301 CURSOR c_cust IS SELECT level_id FROM bis_levels WHERE short_name = p_dim_level_short_name;
1302 l_level_id NUMBER;
1303 BEGIN
1304 IF (c_cust%ISOPEN) THEN
1305 CLOSE c_cust;
1306 END IF;
1307
1308 OPEN c_cust;
1309 FETCH c_cust INTO l_level_id;
1310 CLOSE c_cust;
1311
1312 IF (l_level_id IS NOT NULL) THEN
1313 x_dim_level_cust_name := get_customized_name( p_dim_level_id => l_level_id);
1314 x_dim_level_cust_desc := get_customized_description( p_dim_level_id => l_level_id);
1315 x_dim_level_cust_enabled := get_customized_enabled( p_dim_level_id => l_level_id);
1316 END IF;
1317
1318 EXCEPTION
1319 WHEN OTHERS THEN
1320 IF (c_cust%ISOPEN) THEN
1321 CLOSE c_cust;
1322 END IF;
1323 END Retrieve_Dim_Level_Cust_Wrap;
1324
1325 PROCEDURE Update_Dim_Obj_Obsolete_Flag (
1326 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1327 p_dim_obj_short_name IN VARCHAR2,
1328 p_hide IN VARCHAR2,
1329 x_return_status OUT nocopy VARCHAR2,
1330 x_Msg_Count OUT NOCOPY NUMBER,
1331 x_msg_data OUT nocopy VARCHAR2
1332 ) IS
1333 l_Dimension_Level_Rec BIS_Dimension_Level_PUB.Dimension_Level_Rec_Type;
1334 x_Dimension_Level_Rec BIS_Dimension_Level_PUB.Dimension_Level_Rec_Type;
1335 l_Bsc_Dim_Level_Rec_Type BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
1336 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1337
1338 BEGIN
1339 SAVEPOINT DimObjObsoleteUpdate;
1340 IF (p_dim_obj_short_name IS NULL OR p_dim_obj_short_name = '') THEN
1341 FND_MESSAGE.SET_NAME('BIS','BIS_PMF_INVALID_DIM_OBJECT_VALUE');
1342 FND_MSG_PUB.ADD;
1343 RAISE FND_API.G_EXC_ERROR;
1344 END IF;
1345
1346 IF (p_hide IS NULL OR (p_hide <> FND_API.G_TRUE AND p_hide <> FND_API.G_FALSE)) THEN
1347 FND_MESSAGE.SET_NAME('BIS','BIS_PMF_INVALID_OBSOLETE_FLAG');
1348 FND_MSG_PUB.ADD;
1349 RAISE FND_API.G_EXC_ERROR;
1350 END IF;
1351
1352 l_Dimension_Level_Rec.Dimension_Level_Short_Name := p_dim_obj_short_name;
1353
1354 BIS_Dimension_Level_PVT.Retrieve_Dimension_Level
1355 ( p_api_version => 1.0
1356 , p_Dimension_Level_Rec => l_Dimension_Level_Rec
1357 , x_Dimension_Level_Rec => x_Dimension_Level_Rec
1358 , x_return_status => x_return_status
1359 , x_error_Tbl => l_error_tbl
1360 );
1361
1362 x_Dimension_Level_Rec.Hide := p_Hide;
1363
1364 BIS_Dimension_Level_PVT.Update_Dimension_Level
1365 ( p_api_version => 1.0
1366 , p_Dimension_Level_Rec => x_Dimension_Level_Rec
1367 , x_return_status => x_return_status
1368 , x_error_Tbl => l_error_tbl
1369 );
1370
1371 SELECT dim_level_id
1372 INTO l_Bsc_Dim_Level_Rec_Type.Bsc_Level_Id
1373 FROM bsc_sys_dim_levels_vl
1374 WHERE short_name = p_dim_obj_short_name;
1375
1376 IF(l_Bsc_Dim_Level_Rec_Type.Bsc_Level_Id IS NOT NULL) THEN
1377 BSC_DIMENSION_LEVELS_PUB.Update_Dim_Level
1378 ( p_commit => FND_API.G_FALSE
1379 , p_Dim_Level_Rec => l_Bsc_Dim_Level_Rec_Type
1380 , x_return_status => x_return_status
1381 , x_msg_count => x_msg_count
1382 , x_msg_data => x_msg_data
1383 );
1384 IF((x_return_status IS NOT NULL) AND (x_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
1385 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1386 END IF;
1387 END IF;
1388
1389 IF(p_Commit = FND_API.G_TRUE) THEN
1390 COMMIT;
1391 END IF;
1392
1393 x_return_status := FND_API.G_RET_STS_SUCCESS;
1394 RETURN;
1395
1396 EXCEPTION
1397 WHEN FND_API.G_EXC_ERROR THEN
1398 IF (x_msg_data IS NULL) THEN
1399 FND_MSG_PUB.Count_And_Get
1400 ( p_encoded => FND_API.G_FALSE
1401 , p_count => x_msg_count
1402 , p_data => x_msg_data
1403 );
1404 END IF;
1405 x_Return_Status := FND_API.G_RET_STS_ERROR;
1406 WHEN OTHERS THEN
1407 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1408 ROLLBACK TO DimObjObsoleteUpdate;
1409 IF (x_msg_data IS NOT NULL) THEN
1410 x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Update_Dim_Obj_Obsolete_Flag ';
1411 ELSE
1412 x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Update_Dim_Obj_Obsolete_Flag ';
1413 END IF;
1414 END Update_Dim_Obj_Obsolete_Flag;
1415
1416
1417 END BIS_DIMENSION_LEVEL_PUB;