[Home] [Help]
PACKAGE BODY: APPS.BIS_DIMENSION_PUB
Source
1 PACKAGE BODY BIS_DIMENSION_PUB AS
2 /* $Header: BISPDIMB.pls 120.2 2006/01/06 03:22:23 akoduri noship $ */
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 | BISPDIMB.pls |
10 REM | |
11 REM | DESCRIPTION |
12 REM | Public API for managing Dimensions and 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 | HISTORY |
21 REM | 05-DEC-98 irchen Creation
22 REM | 01-FEB-99 ansingha added required dimension api
23 REM | 27-JAN-03 arhegde For having different local variables for IN and OUT |
24 REM | parameters (bug#2758428) |
25 REM | 20-FEB-03 PAJOHRI Added Procedure UPDATE_DIMENSION |
26 REM | 23-FEB-03 PAJOHRI Added Procedures DELETE_DIMENSION |
27 REM | CREATE_DIMENSION |
28 REM | 13-JUN-03 MAHRAO Added Procedure Load_Dimension_Group |
29 REM | 07-JUL-2003 arhegde bug#3028436 Added call get_unique_dim_group_name()|
30 REM | in Load_Dimension_Group() |
31 REM | 09-JUL-2003 arhegde bug#3028436 Moved logic to BSC API from here |
32 REM | Removed get_unique_dim_group_name() call |
33 REM | 10-JUL-2003 mahrao Added a call to BSC_DIMENSION_GROUPS_PUB. |
34 REM | ret_dimgrpid_fr_shname in Load_Dimension_Group |
35 REM | procedure |
36 REM | 30-Jul-04 rpenneru Modified for enhancemen#3748519 |
37 REM | 21-DEC-04 vtulasi Modified for bug#4045278 - Addtion of LUD |
38 REM | 08-Feb-05 ankgoel Enh#4172034 DD Seeding by Product Teams |
39 REM | 09-FEB-05 ankgoel Bug#4172055 Dimension name validations |
40 REM | 21-Jun-05 ankgoel Bug#4437121 bisdimld/v.ldt compatible in 409 |
41 REM | 06-Jan-06 akoduri Enh#4739401 - Hide Dimensions/Dim Objects |
42 REM +=======================================================================+
43 */
44 --
45 G_PKG_NAME CONSTANT VARCHAR2(30):='BIS_DIMENSION_PUB';
46
47
48 --
49 --
50 Procedure Load_Dimension_Wrapper
51 ( p_commit IN VARCHAR2 := FND_API.G_FALSE
52 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
53 , p_Dimension_Rec IN BIS_DIMENSION_PUB.Dimension_Rec_Type
54 , p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
55 , p_Owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
56 , p_force_mode IN BOOLEAN := FALSE
57 , x_return_status OUT NOCOPY VARCHAR2
58 , x_msg_count OUT NOCOPY NUMBER
59 , x_msg_data OUT NOCOPY VARCHAR2
60 )
61 IS
62 l_return_status VARCHAR2(10);
63 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
64 l_msg_count NUMBER;
65 l_msg_data VARCHAR2(4000);
66 BEGIN
67
68 -- Bug#4172055: Rename the dimension if a BSC type dimension already exists
69 -- with the same name. However, if a PMF type dimension exists with the same
70 -- name, throw an error.
71 BIS_DIMENSION_PVT.Validate_PMF_Unique_Name(
72 p_Dimension_Short_Name => p_Dimension_Rec.Dimension_Short_Name
73 , p_Dimension_Name => p_Dimension_Rec.Dimension_Name
74 , x_return_status => x_return_status
75 );
76 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
77 BIS_UTILITIES_PVT.Add_Error_Message(
78 p_error_msg_name => 'BIS_DIMENSION_UNIQUENESS_ERROR'
79 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
80 , p_error_proc_name => G_PKG_NAME||'.Create_Dimension'
81 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
82 );
83 RAISE FND_API.G_EXC_ERROR;
84 END IF;
85
86 BIS_DIMENSION_PUB.Load_dimension
87 ( p_api_version => 1.0
88 , p_commit => FND_API.G_FALSE
89 , p_Dimension_Rec => p_Dimension_Rec
90 , p_owner => p_Owner
91 , x_return_status => l_return_status
92 , x_error_Tbl => l_error_Tbl
93 , p_force_mode => p_force_mode
94 );
95
96 IF((l_return_status IS NOT NULL) AND (l_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
97 IF(l_error_Tbl.COUNT > 0) THEN
98 l_msg_data := l_error_Tbl(l_error_Tbl.FIRST).Error_Description;
99 END IF;
100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
101 END IF;
102
103 BIS_DIMENSION_PUB.Load_Dimension_Group
104 ( p_Commit => FND_API.G_FALSE
105 , p_dim_grp_rec => p_Dim_Grp_Rec
106 , x_return_status => l_return_status
107 , x_msg_count => l_msg_count
108 , x_msg_data => l_msg_data
109 , p_force_mode => p_force_mode
110 );
111
112 IF((l_return_status IS NOT NULL) AND (l_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
113 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
114 END IF;
115
116 EXCEPTION
117 WHEN FND_API.G_EXC_ERROR THEN
118 x_return_status := FND_API.G_RET_STS_ERROR ;
119 IF(x_msg_data IS NULL) THEN
120 FND_MSG_PUB.Count_And_Get( p_encoded => 'F'
121 ,p_count => x_msg_count
122 ,p_data => x_msg_data);
123 END IF;
124 RAISE;
125 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
126 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
127 IF(x_msg_data IS NULL) THEN
128 FND_MSG_PUB.Count_And_Get( p_encoded => 'F'
129 ,p_count => x_msg_count
130 ,p_data => x_msg_data);
131 END IF;
132 RAISE;
133 WHEN OTHERS THEN
134 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
135 IF(x_msg_data IS NULL) THEN
136 FND_MSG_PUB.Count_And_Get( p_encoded => 'F'
137 ,p_count => x_msg_count
138 ,p_data => x_msg_data);
139 END IF;
140 RAISE;
141 END Load_Dimension_Wrapper;
142 --
143
144 Procedure Translate_Dimension_Wrapper
145 ( p_commit IN VARCHAR2 := FND_API.G_FALSE
146 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
147 , p_Dimension_Rec IN BIS_DIMENSION_PUB.Dimension_Rec_Type
148 , p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
149 , p_Owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
150 , x_return_status OUT NOCOPY VARCHAR2
151 , x_msg_count OUT NOCOPY NUMBER
152 , x_msg_data OUT NOCOPY VARCHAR2
153 )
154 IS
155 l_return_status VARCHAR2(10);
156 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
157 l_msg_count NUMBER;
158 l_msg_data VARCHAR2(4000);
159 BEGIN
160
161 BIS_DIMENSION_PUB.Translate_dimension
162 ( p_api_version => 1.0
163 , p_commit => FND_API.G_FALSE
164 , p_Dimension_Rec => p_Dimension_Rec
165 , p_owner => p_Owner
166 , x_return_status => l_return_status
167 , x_error_Tbl => l_error_Tbl
168 );
169
170 IF((l_return_status IS NOT NULL) AND (l_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
171 IF(l_error_Tbl.COUNT > 0) THEN
172 l_msg_data := l_error_Tbl(l_error_Tbl.FIRST).Error_Description;
173 END IF;
174 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
175 END IF;
176
177 BSC_DIMENSION_GROUPS_PUB.Translate_Dimension_Group
178 ( p_commit => FND_API.G_FALSE
179 , p_Dim_Grp_Rec => p_Dim_Grp_Rec
180 , x_return_status => l_return_status
181 , x_msg_count => l_msg_count
182 , x_msg_data => l_msg_data
183 );
184
185 IF((l_return_status IS NOT NULL) AND (l_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
186 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
187 END IF;
188
189 EXCEPTION
190 WHEN FND_API.G_EXC_ERROR THEN
191 x_return_status := FND_API.G_RET_STS_ERROR ;
192 IF(x_msg_data IS NULL) THEN
193 FND_MSG_PUB.Count_And_Get( p_encoded => 'F'
194 ,p_count => x_msg_count
195 ,p_data => x_msg_data);
196 END IF;
197 RAISE;
198 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
199 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
200 IF(x_msg_data IS NULL) THEN
201 FND_MSG_PUB.Count_And_Get( p_encoded => 'F'
202 ,p_count => x_msg_count
203 ,p_data => x_msg_data);
204 END IF;
205 RAISE;
206 WHEN OTHERS THEN
207 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
208 IF(x_msg_data IS NULL) THEN
209 FND_MSG_PUB.Count_And_Get( p_encoded => 'F'
210 ,p_count => x_msg_count
211 ,p_data => x_msg_data);
212 END IF;
213 RAISE;
214 END Translate_Dimension_Wrapper;
215 --
216
217 Procedure Retrieve_Dimensions
218 ( p_api_version IN NUMBER
219 , x_Dimension_Tbl OUT NOCOPY BIS_DIMENSION_PUB.Dimension_Tbl_Type
220 , x_return_status OUT NOCOPY VARCHAR2
221 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
222 )
223 IS
224 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
225 BEGIN
226 BIS_DIMENSION_PVT.Retrieve_Dimensions
227 ( p_api_version => p_api_version
228 , x_Dimension_Tbl => x_Dimension_Tbl
229 , x_return_status => x_return_status
230 , x_error_Tbl => x_error_Tbl
231 );
232
233 EXCEPTION
234 WHEN NO_DATA_FOUND THEN
235 x_return_status := FND_API.G_RET_STS_ERROR ;
236 when FND_API.G_EXC_ERROR then
237 x_return_status := FND_API.G_RET_STS_ERROR ;
238 when FND_API.G_EXC_UNEXPECTED_ERROR then
239 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
240 when others then
241 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
242 l_error_tbl := x_error_Tbl;
243 BIS_UTILITIES_PVT.Add_Error_Message
244 ( p_error_msg_id => SQLCODE
245 , p_error_description => SQLERRM
246 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Dimensions'
247 , p_error_table => l_error_tbl
248 , x_error_table => x_error_tbl
249 );
250
251 END Retrieve_Dimensions;
252 --
253 Procedure Retrieve_Dimension
254 ( p_api_version IN NUMBER
255 , p_Dimension_Rec IN BIS_DIMENSION_PUB.Dimension_Rec_Type
256 , x_Dimension_Rec OUT NOCOPY BIS_DIMENSION_PUB.Dimension_Rec_Type
257 , x_return_status OUT NOCOPY VARCHAR2
258 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
259 )
260 IS
261 l_Dimension_Rec BIS_DIMENSION_PUB.Dimension_Rec_Type;
262 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
263 BEGIN
264
265 BIS_DIMENSION_PVT.Value_ID_Conversion
266 ( p_api_version => 1.0
267 , p_Dimension_Rec => p_Dimension_Rec
268 , x_Dimension_Rec => l_Dimension_Rec
269 , x_return_status => x_return_status
270 , x_error_Tbl => x_error_tbl
271 );
272
273 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
274 --Added last two parameters
275 l_error_tbl := x_error_Tbl;
276 BIS_UTILITIES_PVT.Add_Error_Message (
277 p_error_msg_name => 'BIS_INVALID_DIMENSION_VALUE'
278 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
279 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Dimension'
280 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
281 , p_error_table => l_error_tbl
282 , x_error_table => x_error_tbl
283 );
284 RAISE FND_API.G_EXC_ERROR;
285 end if;
286
287 BIS_DIMENSION_PVT.Retrieve_Dimension
288 ( p_api_version => 1.0
289 , p_Dimension_Rec => l_Dimension_Rec
290 , x_Dimension_Rec => x_Dimension_Rec
291 , x_return_status => x_return_status
292 , x_error_Tbl => x_error_Tbl
293 );
294
295
296 EXCEPTION
297 WHEN NO_DATA_FOUND THEN
298 x_return_status := FND_API.G_RET_STS_ERROR ;
299 when FND_API.G_EXC_ERROR then
300 x_return_status := FND_API.G_RET_STS_ERROR ;
301 when FND_API.G_EXC_UNEXPECTED_ERROR then
302 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
303 when others then
304 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
305 l_error_tbl := x_error_tbl;
306 BIS_UTILITIES_PVT.Add_Error_Message(
307 p_error_msg_id => SQLCODE
308 , p_error_description => SQLERRM
309 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Dimension'
310 , p_error_table => l_error_tbl
311 , x_error_table => x_error_tbl
312 );
313
314 END Retrieve_Dimension;
315 --
316 -- API to retrieve the first and the second required dimension
317 -- this p_num can be either 1 or 2
318 -- I wish I had enumerated types :-(
319 Procedure Retrieve_Required_Dimension
320 ( p_api_version IN NUMBER
321 , p_num IN NUMBER
322 , x_dimension_rec OUT NOCOPY BIS_DIMENSION_PUB.Dimension_Rec_Type
323 , x_return_status OUT NOCOPY VARCHAR2
324 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
325 )
326 IS
327 l_Dimension_Rec BIS_DIMENSION_PUB.Dimension_Rec_Type;
328 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
329 BEGIN
330 if (p_num = 1) then
331 l_Dimension_Rec.dimension_short_name := 'ORGANIZATION';
332 elsif (p_num = 2) then
333 l_Dimension_Rec.dimension_short_name := 'TIME';
334 else
335 BIS_UTILITIES_PVT.Add_Error_Message
336 ( p_error_msg_name => 'BIS_INVALID_REQUIRED_DIMENSION'
337 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
338 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Required_Dimension'
339 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
340 );
341 RAISE FND_API.G_EXC_ERROR;
342 end if;
343
344 BIS_DIMENSION_PUB.Retrieve_Dimension
345 ( p_api_version => 1.0
346 , p_Dimension_Rec => l_Dimension_Rec
347 , x_Dimension_Rec => x_Dimension_Rec
348 , x_return_status => x_return_status
349 , x_error_Tbl => x_error_Tbl
350 );
351
352
353 EXCEPTION
354 WHEN NO_DATA_FOUND THEN
355 x_return_status := FND_API.G_RET_STS_ERROR ;
356 when FND_API.G_EXC_ERROR then
357 x_return_status := FND_API.G_RET_STS_ERROR ;
358 when FND_API.G_EXC_UNEXPECTED_ERROR then
359 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
360 when others then
361 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
362 l_error_tbl := x_error_tbl;
363 BIS_UTILITIES_PVT.Add_Error_Message
364 ( p_error_msg_id => SQLCODE
365 , p_error_description => SQLERRM
366 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Required_Dimension'
367 , p_error_table => l_error_tbl
368 , x_error_table => x_error_tbl
369 );
370
371 END Retrieve_Required_Dimension;
372 --
373 --
374 Procedure Translate_Dimension
375 ( p_api_version IN NUMBER
376 , p_commit IN VARCHAR2 := FND_API.G_FALSE
377 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
378 , p_Dimension_Rec IN BIS_DIMENSION_PUB.Dimension_Rec_Type
379 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
380 , x_return_status OUT NOCOPY VARCHAR2
381 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
382 )
383 IS
384 l_Dimension_Rec BIS_DIMENSION_PUB.Dimension_Rec_Type;
385 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
386 l_return_status VARCHAR2(10);
387 l_return_msg VARCHAR2(32000);
388
389 BEGIN
390
391 bis_utilities_pvt.init_debug_flag -- 2694978
392 ( x_return_status => l_return_status
393 , x_return_msg => l_return_msg
394 );
395
396
397 BIS_DIMENSION_PVT.Value_ID_Conversion
398 ( p_api_version => p_api_version
399 , p_Dimension_Rec => p_Dimension_Rec
400 , x_Dimension_Rec => l_Dimension_Rec
401 , x_return_status => x_return_status
402 , x_error_Tbl => x_error_tbl
403 );
404
405 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
406 BIS_UTILITIES_PVT.Add_Error_Message
407 ( p_error_msg_name => 'BIS_INVALID_DIMENSION_VALUE'
408 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
409 , p_error_proc_name => G_PKG_NAME||'.Translate_Dimension'
410 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
411 );
412 RAISE FND_API.G_EXC_ERROR;
413 end if;
414
415 l_Dimension_Rec.Last_Update_Date := NVL(p_Dimension_Rec.Last_Update_Date, SYSDATE);
416 BIS_DIMENSION_PVT.Translate_Dimension
417 ( p_api_version => p_api_version
418 , p_commit => p_commit
419 , p_validation_level => p_validation_level
420 , p_Dimension_Rec => l_Dimension_Rec
421 , p_owner => p_owner
422 , x_return_status => x_return_status
423 , x_error_Tbl => x_error_Tbl
424 );
425
426 EXCEPTION
427 WHEN NO_DATA_FOUND THEN
428 x_return_status := FND_API.G_RET_STS_ERROR ;
429 when FND_API.G_EXC_ERROR then
430 x_return_status := FND_API.G_RET_STS_ERROR ;
431 when FND_API.G_EXC_UNEXPECTED_ERROR then
432 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
433 when others then
434 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
435 l_error_tbl := x_error_tbl;
436 BIS_UTILITIES_PVT.Add_Error_Message
437 ( p_error_msg_id => SQLCODE
438 , p_error_description => SQLERRM
439 , p_error_proc_name => G_PKG_NAME||'.Translate_Dimension'
440 , p_error_table => l_error_tbl
441 , x_error_table => x_error_tbl
442 );
443
444 END Translate_Dimension ;
445 --
446 Procedure Load_Dimension
447 ( p_api_version IN NUMBER
448 , p_commit IN VARCHAR2 := FND_API.G_FALSE
449 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
450 , p_Dimension_Rec IN BIS_DIMENSION_PUB.Dimension_Rec_Type
451 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
452 , x_return_status OUT NOCOPY VARCHAR2
453 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
454 , p_force_mode IN BOOLEAN := FALSE
455 )
456 IS
457 l_Dimension_Rec BIS_DIMENSION_PUB.Dimension_Rec_Type;
458 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
459 l_return_status VARCHAR2(10);
460 l_return_msg VARCHAR2(32000);
461 l_count NUMBER;
462 l_ret_code BOOLEAN;
463
464 BEGIN
465
466 bis_utilities_pvt.init_debug_flag -- 2694978
467 ( x_return_status => l_return_status
468 , x_return_msg => l_return_msg
469 );
470
471 -- BIS_UTILITIES_PUB.put_line(p_text => 'YYYYYYYYY' );
472
473 -- l_Dimension_Rec := p_Dimension_Rec;
474
475 BIS_DIMENSION_PVT.Value_ID_Conversion
476 ( p_api_version => p_api_version
477 , p_Dimension_Rec => p_Dimension_Rec
478 , x_Dimension_Rec => l_Dimension_Rec
479 , x_return_status => x_return_status
480 , x_error_Tbl => x_error_tbl
481 );
482
483 l_Dimension_Rec.Last_Update_Date := NVL(p_Dimension_Rec.Last_Update_Date, SYSDATE);
484
485 SELECT COUNT(short_name) INTO l_count
486 FROM BIS_DIMENSIONS
487 WHERE short_name = p_Dimension_Rec.Dimension_short_Name;
488
489 IF (l_count > 0) THEN
490
491 BIS_UTIL.Validate_For_Update (p_last_update_date => l_Dimension_Rec.Last_Update_Date
492 ,p_owner => p_owner
493 ,p_force_mode => p_force_mode
494 ,p_table_name => 'BIS_DIMENSIONS'
495 ,p_key_value => p_Dimension_Rec.Dimension_short_Name
496 ,x_ret_code => l_ret_code
497 ,x_return_status => x_return_status
498 ,x_msg_data => l_return_msg
499 );
500 IF (l_ret_code) THEN
501
502 BIS_DIMENSION_PVT.Update_Dimension
503 ( p_api_version => p_api_version
504 , p_commit => p_commit
505 , p_validation_level => p_validation_level
506 , p_Dimension_Rec => l_Dimension_Rec
507 , p_owner => p_owner
508 , x_return_status => x_return_status
509 , x_error_Tbl => x_error_Tbl
510 );
511
512 END IF;
513
514 ELSE
515
516 BIS_DIMENSION_PVT.Create_Dimension
517 ( p_api_version => p_api_version
518 , p_commit => p_commit
519 , p_validation_level => p_validation_level
520 , p_Dimension_Rec => l_Dimension_Rec
521 , p_owner => p_owner
522 , x_return_status => x_return_status
523 , x_error_Tbl => x_error_Tbl
524 );
525
526 END IF;
527
528
529 EXCEPTION
530 WHEN NO_DATA_FOUND THEN
531 x_return_status := FND_API.G_RET_STS_ERROR ;
532 when FND_API.G_EXC_ERROR then
533 x_return_status := FND_API.G_RET_STS_ERROR ;
534 when FND_API.G_EXC_UNEXPECTED_ERROR then
535 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
536 when others then
537 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
538 l_error_tbl := x_error_Tbl;
539 BIS_UTILITIES_PVT.Add_Error_Message
540 ( p_error_msg_id => SQLCODE
541 , p_error_description => SQLERRM
542 , p_error_proc_name => G_PKG_NAME||'.Load_Dimension'
543 , p_error_table => l_error_tbl
544 , x_error_table => x_error_tbl
545 );
546
547 END Load_Dimension ;
548 --
549 PROCEDURE Update_Dimension
550 ( p_api_version IN NUMBER
551 , p_commit IN VARCHAR2 := FND_API.G_FALSE
552 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
553 , p_Dimension_Rec IN BIS_DIMENSION_PUB.Dimension_Rec_Type
554 , x_return_status OUT NOCOPY VARCHAR2
555 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
556 )
557 IS
558 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
559 BEGIN
560
561 BIS_DIMENSION_PVT.Update_Dimension
562 ( p_api_version => p_api_version
563 , p_commit => p_commit
564 , p_validation_level => p_validation_level
565 , p_Dimension_Rec => p_Dimension_Rec
566 , x_return_status => x_return_status
567 , x_error_Tbl => x_error_Tbl
568 );
569 EXCEPTION
570 WHEN NO_DATA_FOUND THEN
571 x_return_status := FND_API.G_RET_STS_ERROR ;
572 WHEN FND_API.G_EXC_ERROR THEN
573 x_return_status := FND_API.G_RET_STS_ERROR ;
574 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
575 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
576 WHEN OTHERS THEN
577 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
578 l_error_tbl := x_error_tbl;
579 BIS_UTILITIES_PVT.Add_Error_Message
580 ( p_error_msg_id => SQLCODE
581 , p_error_description => SQLERRM
582 , p_error_proc_name => G_PKG_NAME||'.Update_Dimension'
583 , p_error_table => l_error_tbl
584 , x_error_table => x_error_tbl
585 );
586
587 END Update_Dimension;
588 --
589 PROCEDURE Delete_Dimension
590 (
591 p_commit IN VARCHAR2 := FND_API.G_FALSE
592 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
593 , p_Dimension_Rec IN BIS_DIMENSION_PUB.Dimension_Rec_Type
594 , x_return_status OUT NOCOPY VARCHAR2
595 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
596 )
597 IS
598 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
599 BEGIN
600 BIS_DIMENSION_PVT.Delete_Dimension
601 (
602 p_commit => p_commit
603 , p_validation_level => p_validation_level
604 , p_Dimension_Rec => p_Dimension_Rec
605 , x_return_status => x_return_status
606 , x_error_Tbl => x_error_Tbl
607 );
608 EXCEPTION
609 WHEN FND_API.G_EXC_ERROR THEN
610 x_return_status := FND_API.G_RET_STS_ERROR ;
611 WHEN OTHERS THEN
612 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
613 l_error_tbl := x_error_tbl;
614 BIS_UTILITIES_PVT.Add_Error_Message
615 ( p_error_msg_id => SQLCODE
616 , p_error_description => SQLERRM
617 , p_error_proc_name => G_PKG_NAME||'.Delete_Dimension'
618 , p_error_table => l_error_tbl
619 , x_error_table => x_error_tbl
620 );
621
622 END Delete_Dimension;
623 --
624
625 PROCEDURE Create_Dimension
626 (
627 p_api_version IN NUMBER
628 , p_commit IN VARCHAR2 := FND_API.G_FALSE
629 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
630 , p_Dimension_Rec IN BIS_DIMENSION_PUB.Dimension_Rec_Type
631 , x_return_status OUT NOCOPY VARCHAR2
632 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
633 )
634 IS
635 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
636 BEGIN
637 BIS_DIMENSION_PVT.Create_Dimension
638 (
639 p_api_version => p_api_version
640 , p_commit => p_commit
641 , p_validation_level => p_validation_level
642 , p_Dimension_Rec => p_Dimension_Rec
643 , x_return_status => x_return_status
644 , x_error_Tbl => x_error_Tbl
645 );
646 EXCEPTION
647 WHEN NO_DATA_FOUND THEN
648 x_return_status := FND_API.G_RET_STS_ERROR ;
649 WHEN FND_API.G_EXC_ERROR THEN
650 x_return_status := FND_API.G_RET_STS_ERROR ;
651 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
652 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
653 WHEN OTHERS THEN
654 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
655 l_error_tbl := x_error_tbl;
656 BIS_UTILITIES_PVT.Add_Error_Message
657 ( p_error_msg_id => SQLCODE
658 , p_error_description => SQLERRM
659 , p_error_proc_name => G_PKG_NAME||'.Create_Dimension'
660 , p_error_table => l_error_tbl
661 , x_error_table => x_error_tbl
662 );
663 END Create_Dimension;
664 --=============================================================================
665 /*
666 * Used for "All" enhancement from BISDIMLV.lct
667 */
668 PROCEDURE Load_Dimension_Group (
669 p_Commit IN VARCHAR2 := FND_API.G_FALSE
670 ,p_Dim_Grp_Rec IN BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
671 ,x_return_status OUT NOCOPY VARCHAR2
672 ,x_msg_count OUT NOCOPY NUMBER
673 ,x_msg_data OUT NOCOPY VARCHAR2
674 ,p_force_mode IN BOOLEAN := FALSE
675 )
676 IS
677 l_Bsc_Dim_Group_Rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
678 l_dim_grp_id NUMBER;
679 BEGIN
680
681 -- Information coming from ldt file
682 l_Bsc_Dim_Group_Rec.Bsc_Dim_Level_Group_Name := p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name;
683 l_Bsc_Dim_Group_Rec.Bsc_Dim_Level_Group_Short_Name := p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Short_Name;
684 -- WHO columns are populated from the Lct file.
685 l_Bsc_Dim_Group_Rec.Bsc_Created_By := p_Dim_Grp_Rec.Bsc_Created_By;
686 l_Bsc_Dim_Group_Rec.Bsc_Last_Updated_By := p_Dim_Grp_Rec.Bsc_Last_Updated_By;
687 l_Bsc_Dim_Group_Rec.Bsc_Last_Update_Date := NVL(p_Dim_Grp_Rec.Bsc_Last_Update_Date, SYSDATE);
688
689 BSC_DIMENSION_GROUPS_PUB.load_dimension_group (
690 p_commit => FND_API.G_TRUE
691 ,p_Dim_Grp_Rec => l_Bsc_Dim_Group_Rec
692 ,x_return_status => x_return_status
693 ,x_msg_count => x_msg_count
694 ,x_msg_data => x_msg_data
695 ,p_force_mode => p_force_mode
696 );
697
698 BSC_DIMENSION_GROUPS_PUB.ret_dimgrpid_fr_shname (
699 p_dim_short_name => l_Bsc_Dim_Group_Rec.Bsc_Dim_Level_Group_Short_Name
700 ,x_dim_grp_id => l_dim_grp_id
701 ,x_return_status => x_return_status
702 ,x_msg_count => x_msg_count
703 ,x_msg_data => x_msg_data
704 );
705
706 IF (l_dim_grp_id IS NOT NULL) THEN
707 UPDATE bis_dimensions
708 SET dim_grp_id = l_dim_grp_id
709 WHERE short_name = l_Bsc_Dim_Group_Rec.Bsc_Dim_Level_Group_Short_Name;
710 END IF;
711
712 EXCEPTION
713 WHEN NO_DATA_FOUND THEN
714 x_return_status := FND_API.G_RET_STS_ERROR ;
715 WHEN FND_API.G_EXC_ERROR THEN
716 x_return_status := FND_API.G_RET_STS_ERROR ;
717 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
718 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
719 WHEN OTHERS THEN
720 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
721 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
722 ,p_data => x_msg_data);
723 END;
724
725 --=============================================================================
726
727 PROCEDURE Translate_Dim_By_Given_Lang
728 (
729 p_commit IN VARCHAR2 := FND_API.G_FALSE
730 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
731 , p_Dimension_Rec IN BIS_DIMENSION_PUB.Dimension_Rec_Type
732 , x_return_status OUT NOCOPY VARCHAR2
733 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
734 ) IS
735 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
736 BEGIN
737 BIS_DIMENSION_PVT.Translate_Dim_By_Given_Lang
738 (
739 p_commit => p_commit
740 , p_validation_level => p_validation_level
741 , p_Dimension_Rec => p_Dimension_Rec
742 , x_return_status => x_return_status
743 , x_error_Tbl => x_error_Tbl
744 );
745
746 EXCEPTION
747 WHEN NO_DATA_FOUND THEN
748 x_return_status := FND_API.G_RET_STS_ERROR ;
749 WHEN FND_API.G_EXC_ERROR THEN
750 x_return_status := FND_API.G_RET_STS_ERROR ;
751 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
752 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
753 WHEN OTHERS THEN
754 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
755 l_error_tbl := x_error_tbl;
756 BIS_UTILITIES_PVT.Add_Error_Message
757 ( p_error_msg_id => SQLCODE
758 , p_error_description => SQLERRM
759 , p_error_proc_name => G_PKG_NAME||'.Translate_Dim_By_Given_Lang'
760 , p_error_table => l_error_tbl
761 , x_error_table => x_error_tbl
762 );
763 END Translate_Dim_By_Given_Lang;
764
765 PROCEDURE Update_Dimension_Obsolete_Flag (
766 p_commit IN VARCHAR2 := FND_API.G_FALSE,
767 p_dim_short_name IN VARCHAR2,
768 p_hide IN VARCHAR2,
769 x_return_status OUT nocopy VARCHAR2,
770 x_Msg_Count OUT NOCOPY NUMBER,
771 x_msg_data OUT nocopy VARCHAR2
772 ) IS
773 l_dim_rec BIS_DIMENSION_PUB.Dimension_Rec_Type;
774 x_dim_rec BIS_DIMENSION_PUB.Dimension_Rec_Type;
775 l_dim_obj_sht_name BSC_SYS_DIM_LEVELS_B.SHORT_NAME%TYPE;
776 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
777
778 CURSOR cr_dim_obj IS
779 SELECT
780 obj_short_name short_name
781 FROM
782 bsc_bis_dim_obj_by_dim_vl b
783 WHERE
784 dim_short_name = p_dim_short_name AND
785 (SELECT COUNT(1)
786 FROM
787 bsc_bis_dim_obj_by_dim_vl a
788 WHERE
789 a.obj_short_name = b.obj_short_name AND
790 a.dim_short_name <> b.dim_short_name AND
791 a.dim_short_name <> 'UNASSIGNED' AND
792 (SELECT NVL(hide_in_design,'F') FROM bis_dimensions WHERE short_name = a.dim_short_name) = 'F' AND
793 (SELECT bis_util.is_Seeded(created_by,'T','F') FROM bis_dimensions WHERE short_name = a.dim_short_name) = 'T') = 0;
794
795 BEGIN
796 SAVEPOINT DimObsoleteUpdate;
797 IF (p_dim_short_name IS NULL OR p_dim_short_name = '') THEN
798 FND_MESSAGE.SET_NAME('BIS','BIS_PMF_INVALID_DIMENSION_VALUE');
799 FND_MSG_PUB.ADD;
800 RAISE FND_API.G_EXC_ERROR;
801 END IF;
802
803 IF (p_hide IS NULL OR (p_hide <> FND_API.G_TRUE AND p_hide <> FND_API.G_FALSE)) THEN
804 FND_MESSAGE.SET_NAME('BIS','BIS_PMF_INVALID_OBSOLETE_FLAG');
805 FND_MSG_PUB.ADD;
806 RAISE FND_API.G_EXC_ERROR;
807 END IF;
808
809 FOR cr in cr_dim_obj LOOP
810 -- Hide or Unhide all the dimension objects that are attached only to this dimension(Cascade the obsoletion)
811 BIS_DIMENSION_LEVEL_PUB.Update_Dim_Obj_Obsolete_Flag (
812 p_dim_obj_short_name => cr.short_name
813 , p_hide => p_hide
814 , x_return_status => x_return_status
815 , x_Msg_Count => x_Msg_Count
816 , x_msg_data => x_msg_data
817 );
818 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
819 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
820 END IF;
821 END LOOP;
822
823 l_dim_rec.Dimension_Short_Name := p_dim_short_name;
824
825 BIS_DIMENSION_PVT.Retrieve_Dimension
826 ( p_api_version => 1.0
827 , p_Dimension_Rec => l_dim_rec
828 , x_Dimension_Rec => x_dim_rec
829 , x_return_status => x_return_status
830 , x_error_Tbl => l_error_tbl
831 );
832
833 x_dim_rec.Hide := p_Hide;
834
835 BIS_DIMENSION_PVT.Update_Dimension
836 ( p_api_version => 1.0
837 , p_Dimension_Rec => x_dim_rec
838 , x_return_status => x_return_status
839 , x_error_Tbl => l_error_tbl
840 );
841
842 IF(p_Commit = FND_API.G_TRUE) THEN
843 COMMIT;
844 END IF;
845
846 x_return_status := FND_API.G_RET_STS_SUCCESS;
847 RETURN;
848
849 EXCEPTION
850 WHEN FND_API.G_EXC_ERROR THEN
851 IF (x_msg_data IS NULL) THEN
852 FND_MSG_PUB.Count_And_Get
853 ( p_encoded => FND_API.G_FALSE
854 , p_count => x_msg_count
855 , p_data => x_msg_data
856 );
857 END IF;
858 x_Return_Status := FND_API.G_RET_STS_ERROR;
859 WHEN OTHERS THEN
860 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
861 ROLLBACK TO DimObsoleteUpdate;
862 IF (x_msg_data IS NOT NULL) THEN
863 x_msg_data := x_msg_data||' -> BIS_DIMENSION_PUB.Update_Dimension_Obsolete_Flag ';
864 ELSE
865 x_msg_data := SQLERRM||' at BIS_DIMENSION_PUB.Update_Dimension_Obsolete_Flag ';
866 END IF;
867 END Update_Dimension_Obsolete_Flag;
868
869 --=============================================================================
870 END BIS_DIMENSION_PUB;