[Home] [Help]
PACKAGE BODY: APPS.BIS_DIMENSION_PVT
Source
1 PACKAGE BODY BIS_DIMENSION_PVT AS
2 /* $Header: BISVDIMB.pls 120.1 2006/01/06 03:24:15 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 | BISVDIMB.pls |
10 REM | |
11 REM | DESCRIPTION |
12 REM | Private 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 | 04-JAN-03 mahrao Changed OUT parameter to IN OUT in Valu_Id_Conevrsion
24 REM | as fix for bug 2735908
25 REM | 27-JAN-03 arhegde For having different local variables for IN and OUT |
26 REM | parameters (bug#2758428) |
27 REM | 19-MAR-03 PAJOHRI Bug #2856554, Added 'description' as one more |
28 REM | selection parameter in procedure Retrieve_Dimension|
29 REM | cursor's select query. |
30 REM | 20-MAR-03 PAJOHRI Bug #2860782, Added 'description' in |
31 REM | Retrieve_Dimensions API |
32 REM | 23-FEB-03 PAJOHRI Modified the package, to handle Application_ID |
33 REM | which is added into the bis_levels |
34 REM | 23-FEB-03 PAJOHRI Added procedures DELETE_DIMENSION |
35 REM | 10-JUN-03 rchandra use -1 as dimension_id if short name is UNASSIGNED |
36 REM | for bug 2994108
37 REM | 07-JUL-2003 arhegde bug#3028436 Added get_unique_dim_group_name() |
38 REM | 09-JUL-2003 arhegde bug#3028436 Moved logic to BSC API from here |
39 REM | Removed get_unique_dim_group_name() |
40 REM | 11-JUL-03 MAHRAO Modified the package, to handle dim_grp_ID |
41 REM | which is added into the bis_dimensions |
42 REM | 29-JUN-2004 ankgoel bug#3711250 Handle translation of dimension_id=-1 |
43 REM | 30-Jul-04 rpenneru Modified for enhancemen#3748519 |
44 REM | 29-SEP-2004 ankgoel Added WHO columns in Rec for Bug#3891748 |
45 REM | 21-DEC-04 vtulasi Modified for bug#4045278 - Addtion of LUD |
46 REM | 09-FEB-05 ankgoel Bug#4172055 Dimension name validations |
47 REM | 06-Jan-06 akoduri Enh#4739401 - Hide Dimensions/Dim Objects |
48 REM +=======================================================================+
49 */
50 --
51 G_PKG_NAME CONSTANT VARCHAR2(30):='BIS_DIMENSION_PVT';
52 C_UNASSIGNED CONSTANT VARCHAR2(30):='UNASSIGNED';
53 C_PMF CONSTANT VARCHAR2(10) := '_PMF';
54
55 --
56 --
57 PROCEDURE Rename_BSC_Dimension
58 ( p_Dimension_Short_Name IN VARCHAR2
59 , p_Dimension_Name IN VARCHAR2
60 );
61 --
62 -- returns the record with the G_MISS_CHAR/G_MISS_NUM replaced
63 -- by null
64 --
65 PROCEDURE SetNULL
66 ( p_Dimension_Rec IN BIS_DIMENSION_PUB.Dimension_Rec_Type
67 , x_Dimension_Rec OUT NOCOPY BIS_DIMENSION_PUB.Dimension_Rec_Type
68 );
69 --
70 -- queries database to retrieve the dimension from the database
71 -- updates the record with the changes sent in
72 --
73 PROCEDURE UpdateRecord
74 ( p_Dimension_Rec BIS_Dimension_PUB.Dimension_Rec_Type
75 , x_Dimension_Rec OUT NOCOPY BIS_Dimension_PUB.Dimension_Rec_Type
76 , x_return_status OUT NOCOPY VARCHAR2
77 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
78 );
79 --
80 PROCEDURE Create_New_Dimension
81 ( p_dimension_id IN NUMBER, -- l_id
82 p_dimension_short_name IN VARCHAR2, -- l_Dimension_Rec.Dimension_Short_Name
83 p_application_id IN NUMBER := NULL,
84 p_dim_grp_id IN NUMBER,
85 p_hide IN VARCHAR2 := FND_API.G_FALSE,
86 p_created_by IN NUMBER, -- created_by
87 p_last_updated_by IN NUMBER, -- last_updated_by
88 p_login_id IN NUMBER, -- l_login_id
89 p_dimension_name IN VARCHAR2, -- l_Dimension_Rec.Dimension_Name
90 p_description IN VARCHAR2, -- l_Dimension_Rec.Description
91 p_last_update_date IN DATE := SYSDATE
92 );
93 --
94 PROCEDURE SetNULL
95 ( p_Dimension_Rec IN BIS_DIMENSION_PUB.Dimension_Rec_Type
96 , x_Dimension_Rec OUT NOCOPY BIS_DIMENSION_PUB.Dimension_Rec_Type
97 )
98 IS
99 BEGIN
100
101 x_Dimension_rec.Dimension_ID
102 := BIS_UTILITIES_PVT.CheckMissNum(p_Dimension_rec.Dimension_ID);
103 x_Dimension_rec.Dimension_Short_Name
104 := BIS_UTILITIES_PVT.CheckMissChar
105 (p_Dimension_rec.Dimension_Short_Name);
106 x_Dimension_rec.Dimension_Name
107 := BIS_UTILITIES_PVT.CheckMissChar(p_Dimension_rec.Dimension_Name);
108 x_Dimension_rec.Description
109 := BIS_UTILITIES_PVT.CheckMissChar(p_Dimension_rec.Description);
110 x_Dimension_rec.Application_ID
111 := BIS_UTILITIES_PVT.CheckMissChar(p_Dimension_rec.Application_ID);
112 x_Dimension_rec.dim_grp_id
113 := BIS_UTILITIES_PVT.CheckMissChar(p_Dimension_rec.dim_grp_id);
114 x_Dimension_rec.hide
115 := BIS_UTILITIES_PVT.CheckMissChar(p_Dimension_rec.hide);
116 x_Dimension_rec.Created_By := BIS_UTILITIES_PVT.CheckMissNum(p_Dimension_Rec.Created_By);
117 x_Dimension_rec.Creation_Date := BIS_UTILITIES_PVT.CheckMissDate(p_Dimension_Rec.Creation_Date);
118 x_Dimension_rec.Last_Updated_By := BIS_UTILITIES_PVT.CheckMissNum(p_Dimension_Rec.Last_Updated_By);
119 x_Dimension_rec.Last_Update_Date := BIS_UTILITIES_PVT.CheckMissDate(p_Dimension_Rec.Last_Update_Date);
120 x_Dimension_rec.Last_Update_Login := BIS_UTILITIES_PVT.CheckMissNum(p_Dimension_Rec.Last_Update_Login);
121
122 EXCEPTION
123 WHEN FND_API.G_EXC_ERROR THEN
124 RAISE;
125 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
126 RAISE;
127 WHEN OTHERS THEN
128 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
129
130 END SetNULL;
131 --
132 PROCEDURE UpdateRecord
133 ( p_Dimension_Rec BIS_Dimension_PUB.Dimension_Rec_Type
134 , x_Dimension_Rec OUT NOCOPY BIS_Dimension_PUB.Dimension_Rec_Type
135 , x_return_status OUT NOCOPY VARCHAR2
136 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
137 )
138 IS
139 --
140 l_Dimension_Rec BIS_Dimension_PUB.Dimension_Rec_Type;
141 l_return_status VARCHAR2(10);
142 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
143 --
144 BEGIN
145 x_return_status:= FND_API.G_RET_STS_SUCCESS;
146
147 -- retrieve record from db
148 BIS_Dimension_PVT.Retrieve_Dimension
149 ( p_api_version => 1.0
150 , p_Dimension_Rec => p_Dimension_Rec
151 , x_Dimension_Rec => l_Dimension_Rec
152 , x_return_status => x_return_status
153 , x_error_Tbl => x_error_Tbl
154 );
155
156 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
157 RAISE FND_API.G_EXC_ERROR;
158 END IF;
159
160 -- apply changes
161 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Dimension_Rec.Dimension_ID)
162 = FND_API.G_TRUE
163 ) THEN
164 l_Dimension_Rec.Dimension_ID := p_Dimension_Rec.Dimension_ID;
165 END IF;
166 --
167 IF( BIS_UTILITIES_PUB.Value_Not_Missing
168 (p_Dimension_Rec.Dimension_Short_Name)
169 = FND_API.G_TRUE
170 ) THEN
171 l_Dimension_Rec.Dimension_Short_Name
172 := p_Dimension_Rec.Dimension_Short_Name ;
173 END IF;
174 --
175 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Dimension_Rec.Dimension_Name)
176 = FND_API.G_TRUE
177 ) THEN
178 l_Dimension_Rec.Dimension_Name := p_Dimension_Rec.Dimension_Name;
179 END IF;
180 --
181 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Dimension_Rec.Description)
182 = FND_API.G_TRUE
183 ) THEN
184 l_Dimension_Rec.Description := p_Dimension_Rec.Description;
185 END IF;
186 --
187 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Dimension_Rec.Application_ID)
188 = FND_API.G_TRUE
189 ) THEN
190 l_Dimension_Rec.Application_ID := p_Dimension_Rec.Application_ID;
191 END IF;
192 --
193 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Dimension_Rec.dim_grp_id)
194 = FND_API.G_TRUE
195 ) THEN
196 l_Dimension_Rec.dim_grp_id := p_Dimension_Rec.dim_grp_id;
197 END IF;
198
199 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Dimension_Rec.hide)
200 = FND_API.G_TRUE
201 ) THEN
202 l_Dimension_Rec.hide := p_Dimension_Rec.hide;
203 END IF;
204
205 --
206 x_Dimension_Rec := l_Dimension_Rec;
207
208 --
209 --commented out NOCOPY RAISE
210 EXCEPTION
211 WHEN FND_API.G_EXC_ERROR THEN
212 x_return_status:= FND_API.G_RET_STS_ERROR;
213 --RAISE;
214 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
215 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
216 --RAISE;
217 WHEN OTHERS THEN
218 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
219 l_error_tbl := x_error_tbl;
220 BIS_UTILITIES_PVT.Add_Error_Message
221 ( p_error_table => l_error_tbl
222 , p_error_msg_id => SQLCODE
223 , p_error_description => SQLERRM
224 , x_error_table => x_error_Tbl
225 );
226 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
227
228 END UpdateRecord;
229 --
230 --
231
232 PROCEDURE Retrieve_Dimensions
233 ( p_api_version IN NUMBER
234 , x_Dimension_Tbl OUT NOCOPY BIS_DIMENSION_PUB.Dimension_Tbl_Type
235 , x_return_status OUT NOCOPY VARCHAR2
236 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
237 )
238 IS
239 l_Dimension_Rec BIS_DIMENSION_PUB.Dimension_Rec_Type;
240 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
241
242 cursor cr_all_dimensions is
243 SELECT dimension_id
244 , dimension_short_name
245 , dimension_name
246 , description
247 , application_id
248 , dim_grp_id
249 , hide_in_design
250 from bisbv_dimensions;
251
252 BEGIN
253
254 x_return_status := FND_API.G_RET_STS_SUCCESS;
255
256 for cr in cr_all_dimensions loop
257 l_Dimension_Rec.dimension_id := cr.dimension_id;
258 l_Dimension_Rec.dimension_short_name := cr.dimension_short_name;
259 l_Dimension_Rec.dimension_name := cr.dimension_name;
260 l_Dimension_Rec.description := cr.description;
261 l_Dimension_Rec.application_id := cr.application_id;
262 l_Dimension_Rec.dim_grp_id := cr.dim_grp_id;
263 l_Dimension_Rec.hide := cr.hide_in_design;
264
265 x_dimension_tbl(x_dimension_tbl.count + 1) := l_Dimension_Rec;
266 END loop;
267
268 --commented out NOCOPY RAISE
269 EXCEPTION
270 WHEN NO_DATA_FOUND THEN
271 x_return_status := FND_API.G_RET_STS_ERROR ;
272 --RAISE FND_API.G_EXC_ERROR;
273 WHEN FND_API.G_EXC_ERROR THEN
274 x_return_status := FND_API.G_RET_STS_ERROR ;
275 --RAISE FND_API.G_EXC_ERROR;
276 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
277 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
278 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
279 WHEN OTHERS THEN
280 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
281 l_error_tbl := x_error_tbl;
282 BIS_UTILITIES_PVT.Add_Error_Message
283 ( p_error_msg_id => SQLCODE
284 , p_error_description => SQLERRM
285 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Dimensions'
286 , p_error_table => l_error_tbl
287 , x_error_table => x_error_tbl
288 );
289 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
290
291 END Retrieve_Dimensions;
292 --
293
294 PROCEDURE Retrieve_Dimension
295 ( p_api_version IN NUMBER
296 , p_Dimension_Rec IN BIS_DIMENSION_PUB.Dimension_Rec_Type
297 , x_Dimension_Rec OUT NOCOPY BIS_DIMENSION_PUB.Dimension_Rec_Type
298 , x_return_status OUT NOCOPY VARCHAR2
299 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
300 )
301 is
302 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
303 CURSOR cr_dim_id IS
304 SELECT dimension_id, short_name, name, description,
305 application_id, dim_grp_id, hide_in_design
306 FROM bis_dimensions_vl
307 WHERE dimension_id=p_Dimension_Rec.dimension_id;
308
309 CURSOR cr_dim_short_name IS
310 SELECT dimension_id, short_name, name, description,
311 application_id, dim_grp_id, hide_in_design
312 FROM bis_dimensions_vl
313 WHERE short_name=p_Dimension_Rec.dimension_short_name;
314
315 CURSOR cr_dim_name IS
316 SELECT dimension_id, short_name, name, description,
317 application_id, dim_grp_id, hide_in_design
318 FROM bis_dimensions_vl
319 WHERE name=p_Dimension_Rec.dimension_name;
320
321 begin
322
323 x_return_status := FND_API.G_RET_STS_SUCCESS;
324 x_Dimension_Rec := p_Dimension_Rec;
325
326 IF BIS_UTILITIES_PUB.Value_Not_Missing(p_Dimension_Rec.dimension_id)
327 = FND_API.G_TRUE
328 THEN
329 OPEN cr_dim_id;
330 FETCH cr_dim_id
331 INTO x_Dimension_Rec.dimension_id
332 , x_Dimension_Rec.dimension_short_name
333 , x_Dimension_Rec.dimension_name
334 , x_Dimension_Rec.description
335 , x_Dimension_Rec.Application_ID
336 , x_Dimension_Rec.dim_grp_id
337 , x_Dimension_Rec.hide;
338 IF cr_dim_id%ROWCOUNT = 0 THEN
339 x_return_status := FND_API.G_RET_STS_ERROR;
340 END IF;
341 CLOSE cr_dim_id;
342
343 ELSIF
344 BIS_UTILITIES_PUB.Value_Not_Missing(p_Dimension_Rec.dimension_short_name)
345 = FND_API.G_TRUE
346 THEN
347
348 OPEN cr_dim_short_name;
349 FETCH cr_dim_short_name
350 INTO x_Dimension_Rec.dimension_id
351 , x_Dimension_Rec.dimension_short_name
352 , x_Dimension_Rec.dimension_name
353 , x_Dimension_Rec.description
354 , x_Dimension_Rec.Application_ID
355 , x_Dimension_Rec.dim_grp_id
356 , x_Dimension_Rec.hide;
357 IF cr_dim_short_name%ROWCOUNT = 0 THEN
358 x_return_status := FND_API.G_RET_STS_ERROR;
359 END IF;
360 CLOSE cr_dim_short_name;
361
362 ELSIF
363 BIS_UTILITIES_PUB.Value_Not_Missing(p_Dimension_Rec.dimension_name)
364 = FND_API.G_TRUE
365 THEN
366 OPEN cr_dim_name;
367 FETCH cr_dim_short_name
368 INTO x_Dimension_Rec.dimension_id
369 , x_Dimension_Rec.dimension_short_name
370 , x_Dimension_Rec.dimension_name
371 , x_Dimension_Rec.description
372 , x_Dimension_Rec.Application_ID
373 , x_Dimension_Rec.dim_grp_id
374 , x_Dimension_Rec.hide;
375 IF cr_dim_name%ROWCOUNT = 0 THEN
376 x_return_status := FND_API.G_RET_STS_ERROR;
377 END IF;
378 CLOSE cr_dim_name;
379
380 ELSE
381 --added Add Error Message
382 l_error_tbl := x_error_tbl;
383 BIS_UTILITIES_PVT.Add_Error_Message
384 ( p_error_msg_name => 'BIS_INVALID_DIMENSION_VALUE'
385 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
386 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Dimension'
387 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
388 , p_error_table => l_error_tbl
389 , x_error_table => x_error_tbl
390 );
391 RAISE FND_API.G_EXC_ERROR;
395 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
392 END IF;
393
394 --added this check
396 l_error_tbl := x_error_tbl;
397 BIS_UTILITIES_PVT.Add_Error_Message
398 ( p_error_msg_name => 'BIS_INVALID_DIMENSION__VALUE'
399 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
400 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Dimension'
401 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
402 , p_error_table => l_error_tbl
403 , x_error_table => x_error_tbl
404 );
405 RAISE FND_API.G_EXC_ERROR;
406 END IF;
407
408
409 -- commented the RAISE
410 EXCEPTION
411 WHEN NO_DATA_FOUND THEN
412 x_return_status := FND_API.G_RET_STS_ERROR ;
413 --RAISE FND_API.G_EXC_ERROR;
414 WHEN FND_API.G_EXC_ERROR THEN
415 x_return_status := FND_API.G_RET_STS_ERROR ;
416 --RAISE FND_API.G_EXC_ERROR;
417 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
418 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
419 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
420 WHEN OTHERS THEN
421 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
422 l_error_tbl := x_error_tbl;
423 -- added last two parameters
424 BIS_UTILITIES_PVT.Add_Error_Message
425 ( p_error_msg_id => SQLCODE
426 , p_error_description => SQLERRM
427 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Dimension'
428 , p_error_table => l_error_tbl
429 , x_error_table => x_error_tbl
430 );
431 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
432
433 END Retrieve_Dimension;
434 --
435 PROCEDURE Create_Dimension
436 ( p_api_version IN NUMBER
437 , p_commit IN VARCHAR2 := FND_API.G_FALSE
438 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
439 , p_Dimension_Rec IN BIS_DIMENSION_PUB.Dimension_Rec_Type
440 , x_return_status OUT NOCOPY VARCHAR2
441 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
442 )
443 IS
444 l_Dimension_Rec BIS_DIMENSION_PUB.Dimension_Rec_Type;
445 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
446 BEGIN
447 l_Dimension_Rec := p_Dimension_Rec;
448 l_Dimension_Rec.Last_Update_Date := NVL(p_Dimension_Rec.Last_Update_Date, SYSDATE);
449 Create_Dimension
450 ( p_api_version => p_api_version
451 , p_commit => p_commit
452 , p_validation_level => p_validation_level
453 , p_Dimension_Rec => l_Dimension_Rec
454 , p_owner => BIS_UTILITIES_PUB.G_CUSTOM_OWNER
455 , x_return_status => x_return_status
456 , x_error_Tbl => x_error_Tbl
457 );
458
459 --commented out NOCOPY RAISE
460 EXCEPTION
461 WHEN OTHERS THEN
462 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
463 l_error_tbl := x_error_tbl;
464 BIS_UTILITIES_PVT.Add_Error_Message (
465 p_error_msg_id => SQLCODE
466 , p_error_description => SQLERRM
467 , p_error_proc_name => G_PKG_NAME||'.Create_Dimension'
468 , p_error_table => l_error_tbl
469 , x_error_table => x_error_tbl
470 );
471 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
472
473 END Create_Dimension;
474 --
475 PROCEDURE Create_Dimension
476 ( p_api_version IN NUMBER
477 , p_commit IN VARCHAR2 := FND_API.G_FALSE
478 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
479 , p_Dimension_Rec IN BIS_DIMENSION_PUB.Dimension_Rec_Type
480 , p_owner IN VARCHAR2
481 , x_return_status OUT NOCOPY VARCHAR2
482 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
483 )
484 IS
485 l_user_id NUMBER;
486 l_login_id NUMBER;
487 l_id NUMBER;
488 l_Dimension_Rec BIS_DIMENSION_PUB.Dimension_Rec_Type;
489 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
490
491 DUPLICATE_DIMENSION_VALUE EXCEPTION;
492 PRAGMA EXCEPTION_INIT(DUPLICATE_DIMENSION_VALUE, -1);
493
494 BEGIN
495
496 x_return_status := FND_API.G_RET_STS_SUCCESS;
497 l_Dimension_Rec := p_dimension_Rec;
498
499 SetNULL
500 ( p_dimension_Rec => p_dimension_Rec
501 , x_dimension_Rec => l_Dimension_Rec
502 );
503
504 Validate_Dimension( p_api_version
505 , p_validation_level
506 , l_Dimension_Rec
507 , x_return_status
508 , x_error_Tbl
509 );
510
511 IF( x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
512 l_error_tbl := x_error_tbl;
513 BIS_UTILITIES_PVT.Add_Error_Message
514 ( p_error_msg_name => 'BIS_NAME_SHORT_NAME_MISSING'
515 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
516 , p_error_proc_name => G_PKG_NAME||'.Create_Dimension'
517 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
518 , p_error_table => l_error_tbl
519 , x_error_table => x_error_tbl
520 );
521 RAISE FND_API.G_EXC_ERROR;
522 END IF;
523
524 --
525 -- ankgoel: bug#3891748 - Created_By will take precedence over Owner.
526 -- Last_Updated_By can be different from Created_By while creating dimensions
527 -- during sync-up
528 IF (l_Dimension_Rec.Created_By IS NULL) THEN
532 l_Dimension_Rec.Last_Updated_By := l_Dimension_Rec.Created_By;
529 l_Dimension_Rec.Created_By := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
530 END IF;
531 IF (l_Dimension_Rec.Last_Updated_By IS NULL) THEN
533 END IF;
534 IF (l_Dimension_Rec.Last_Update_Login IS NULL) THEN
535 l_Dimension_Rec.Last_Update_Login := fnd_global.LOGIN_ID;
536 END IF;
537
538 --
539
540 IF ( l_Dimension_Rec.Dimension_Short_Name = C_UNASSIGNED ) THEN
541 l_id := -1;
542 ELSE
543 SELECT bis_dimensions_s.NextVal INTO l_id from dual;
544 END IF;
545
546 l_Dimension_Rec.Last_Update_Date := NVL(p_Dimension_Rec.Last_Update_Date, SYSDATE);
547
548 Create_New_Dimension
549 ( p_dimension_id => l_id
550 , p_dimension_short_name => l_Dimension_Rec.Dimension_Short_Name
551 , p_application_id => l_Dimension_Rec.Application_ID
552 , p_dim_grp_id => l_Dimension_Rec.dim_grp_id
553 , p_hide => l_Dimension_Rec.hide
554 , p_created_by => l_Dimension_Rec.Created_By
555 , p_last_updated_by => l_Dimension_Rec.Last_Updated_By
556 , p_login_id => l_Dimension_Rec.Last_Update_Login
557 , p_dimension_name => l_Dimension_Rec.Dimension_Name
558 , p_description => l_Dimension_Rec.Description
559 , p_last_update_date => l_Dimension_Rec.Last_Update_Date
560 );
561
562 IF (p_commit = FND_API.G_TRUE) THEN
563 COMMIT;
564 END if;
565
566 EXCEPTION
567 WHEN DUPLICATE_DIMENSION_VALUE THEN
568 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
569 l_error_tbl := x_error_tbl;
570 BIS_UTILITIES_PVT.Add_Error_Message
571 ( p_error_msg_name => 'BIS_DIMENSION_UNIQUENESS_ERROR'
572 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
573 , p_error_proc_name => G_PKG_NAME||'.Create_Dimension'
574 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
575 , p_error_table => l_error_tbl
576 , x_error_table => x_error_tbl
577 );
578
579 WHEN NO_DATA_FOUND THEN
580 x_return_status := FND_API.G_RET_STS_ERROR ;
581
582 WHEN FND_API.G_EXC_ERROR THEN
583 x_return_status := FND_API.G_RET_STS_ERROR ;
584
585 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
586 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
587
588 WHEN OTHERS THEN
589 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
590 l_error_tbl := x_error_tbl;
591 BIS_UTILITIES_PVT.Add_Error_Message
592 ( p_error_msg_id => SQLCODE
593 , p_error_description => SQLERRM
594 , p_error_proc_name => G_PKG_NAME||'.Create_Dimension'
595 , p_error_table => l_error_tbl
596 , x_error_table => x_error_tbl
597 );
598
599
600 END Create_Dimension;
601
602 --
603
604 PROCEDURE Create_New_Dimension
605 ( p_dimension_id IN NUMBER, -- l_id
606 p_dimension_short_name IN VARCHAR2, -- l_Dimension_Rec.Dimension_Short_Name
607 p_application_id IN NUMBER := NULL,
608 p_dim_grp_id IN NUMBER,
609 p_hide IN VARCHAR2 := FND_API.G_FALSE,
610 p_created_by IN NUMBER, -- created_by
611 p_last_updated_by IN NUMBER, -- last_updated_by
612 p_login_id IN NUMBER, -- l_login_id
613 p_dimension_name IN VARCHAR2, -- l_Dimension_Rec.Dimension_Name
614 p_description IN VARCHAR2, -- l_Dimension_Rec.Description
615 p_last_update_date IN DATE := SYSDATE
616 )
617 IS
618
619 l_msg VARCHAR2(3000);
620
621 BEGIN
622
623 SAVEPOINT InsertIntoBISDims;
624
625 INSERT INTO bis_dimensions(
626 DIMENSION_ID
627 , SHORT_NAME
628 , APPLICATION_ID
629 , DIM_GRP_ID
630 , HIDE_IN_DESIGN
631 , CREATION_DATE
632 , CREATED_BY
633 , LAST_UPDATE_DATE
634 , LAST_UPDATED_BY
635 , LAST_UPDATE_LOGIN
636 )
637 VALUES
638 ( p_dimension_id
639 , p_dimension_short_name
640 , p_application_id
641 , p_dim_grp_id
642 , p_hide
643 , p_last_update_date
644 , p_created_by
645 , p_last_update_date
646 , p_last_updated_by
647 , p_login_id
648 );
649
650
651 INSERT INTO bis_dimensions_tl (
652 DIMENSION_ID,
653 LANGUAGE,
654 NAME,
655 DESCRIPTION,
656 CREATION_DATE,
657 CREATED_BY,
658 LAST_UPDATE_DATE,
659 LAST_UPDATED_BY,
660 LAST_UPDATE_LOGIN,
661 TRANSLATED,
662 SOURCE_LANG
663 )
664 SELECT
665 p_dimension_id
666 , L.LANGUAGE_CODE
667 , p_dimension_name
668 , p_description
669 , p_last_update_date
670 , p_created_by
671 , p_last_update_date
672 , p_last_updated_by
673 , p_login_id
674 , 'Y'
675 , userenv('LANG')
676 FROM FND_LANGUAGES L
677 , BIS_DIMENSIONS D
681 (SELECT 'EXISTS'
678 WHERE L.INSTALLED_FLAG IN ('I', 'B')
679 AND D.SHORT_NAME = p_dimension_short_name
680 AND NOT EXISTS
682 FROM BIS_DIMENSIONS_TL TL
683 , BIS_DIMENSIONS D
684 WHERE TL.DIMENSION_ID = D.DIMENSION_ID
685 AND D.SHORT_NAME = p_dimension_short_name
686 AND TL.LANGUAGE = L.LANGUAGE_CODE) ;
687
688 EXCEPTION
689
690 WHEN OTHERS THEN
691
692 /*
693 fnd_message.set_name('BIS', 'BIS_DIM_UPLD_FAIL');
694 fnd_message.set_token('SHORT_NAME', p_dimension_short_name);
695 fnd_message.set_token('NAME', p_dimension_name);
696 l_msg := fnd_message.get;
697 */
698 l_msg := 'Failed to upload ' || p_dimension_short_name;
699 l_msg := l_msg || ' . Dimension name: ' || p_dimension_name ;
700 l_msg := l_msg || ' already exists in the database.' ;
701 BIS_UTILITIES_PUB.put_line(p_text =>l_msg);
702
703 ROLLBACK TO InsertIntoBISDims;
704 RAISE;
705
706 END Create_New_Dimension;
707
708 --
709
710 PROCEDURE Update_Dimension
711 ( p_api_version IN NUMBER
712 , p_commit IN VARCHAR2 := FND_API.G_FALSE
713 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
714 , p_Dimension_Rec IN BIS_DIMENSION_PUB.Dimension_Rec_Type
715 , x_return_status OUT NOCOPY VARCHAR2
716 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
717 )
718 IS
719 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
720 l_Dimension_Rec BIS_DIMENSION_PUB.Dimension_Rec_Type;
721 BEGIN
722
723 l_Dimension_Rec := p_Dimension_Rec;
724 l_Dimension_Rec.Last_Update_Date := NVL(p_Dimension_Rec.Last_Update_Date, SYSDATE);
725 Update_Dimension
726 ( p_api_version => p_api_version
727 , p_commit => p_commit
728 , p_validation_level => p_validation_level
729 , p_Dimension_Rec => l_Dimension_Rec
730 , p_owner => BIS_UTILITIES_PUB.G_CUSTOM_OWNER
731 , x_return_status => x_return_status
732 , x_error_Tbl => x_error_Tbl
733 );
734
735 --commented out NOCOPY RAISE
736 EXCEPTION
737 WHEN OTHERS THEN
738 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
739 --added last two parameters
740 l_error_tbl := x_error_tbl;
741 BIS_UTILITIES_PVT.Add_Error_Message
742 ( p_error_msg_id => SQLCODE
743 , p_error_description => SQLERRM
744 , p_error_proc_name => G_PKG_NAME||'.Update_Dimension'
745 , p_error_table => l_error_tbl
746 , x_error_table => x_error_tbl
747 );
748 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
749
750 END Update_Dimension;
751 --
752 PROCEDURE Update_Dimension
753 ( p_api_version IN NUMBER
754 , p_commit IN VARCHAR2 := FND_API.G_FALSE
755 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
756 , p_Dimension_Rec IN BIS_DIMENSION_PUB.Dimension_Rec_Type
757 , p_owner IN VARCHAR2
758 , x_return_status OUT NOCOPY VARCHAR2
759 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
760 )
761 IS
762
763 l_user_id number;
764 l_login_id number;
765 l_count NUMBER := 0;
766 l_Dimension_Rec BIS_DIMENSION_PUB.Dimension_Rec_Type;
767 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
768
769 DUPLICATE_DIMENSION_VALUE EXCEPTION;
770 PRAGMA EXCEPTION_INIT(DUPLICATE_DIMENSION_VALUE, -1);
771
772 BEGIN
773
774 -- retrieve record from database and apply changes
775 UpdateRecord
776 ( p_Dimension_Rec => p_Dimension_Rec
777 , x_Dimension_Rec => l_Dimension_Rec
778 , x_return_status => x_return_status
779 , x_error_Tbl => x_error_Tbl
780 );
781
782 Validate_Dimension
783 ( p_api_version
784 , p_validation_level
785 , l_Dimension_Rec
786 , x_return_status
787 , x_error_Tbl
788 );
789
790 --added Add_Error_Message
791 IF( x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
792 l_error_tbl := x_error_tbl;
793 BIS_UTILITIES_PVT.Add_Error_Message
794 ( p_error_msg_name => 'BIS_INVALID_DIMENSION_ID'
795 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
796 , p_error_proc_name => G_PKG_NAME||'.Update_Dimension'
797 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
798 , p_error_table => l_error_tbl
799 , x_error_table => x_error_tbl
800 );
801 RAISE FND_API.G_EXC_ERROR;
802 END IF;
803 --
804 l_user_id := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
805 l_login_id := fnd_global.LOGIN_ID;
806 --
807
808 l_Dimension_Rec.Last_Update_Date := NVL(p_Dimension_Rec.Last_Update_Date, SYSDATE);
809
810 Update bis_dimensions
811 set
812 SHORT_NAME = l_Dimension_Rec.Dimension_Short_Name
813 , APPLICATION_ID = l_Dimension_Rec.Application_ID
814 , DIM_GRP_ID = l_Dimension_Rec.dim_grp_id
815 , HIDE_IN_DESIGN = l_Dimension_Rec.hide
816 , LAST_UPDATE_DATE = l_Dimension_Rec.Last_Update_Date
817 , LAST_UPDATED_BY = l_user_id
818 , LAST_UPDATE_LOGIN = l_login_id
822 COMMIT;
819 where dimension_ID = l_Dimension_Rec.Dimension_Id;
820
821 IF (p_commit = FND_API.G_TRUE) THEN
823 END if;
824
825 Translate_dimension
826 ( p_api_version => p_api_version
827 , p_commit => p_commit
828 , p_validation_level => p_validation_level
829 , p_Dimension_Rec => l_Dimension_Rec
830 , p_owner => p_owner
831 , x_return_status => x_return_status
832 , x_error_Tbl => x_error_Tbl
833 );
834
835 --commented out NOCOPY RAISE
836 EXCEPTION
837 WHEN DUPLICATE_DIMENSION_VALUE THEN
838 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
839 l_error_tbl := x_error_tbl;
840 BIS_UTILITIES_PVT.Add_Error_Message
841 ( p_error_msg_name => 'BIS_DIMENSION_UNIQUENESS_ERROR'
842 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
843 , p_error_proc_name => G_PKG_NAME||'.Update_Dimension'
844 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
845 , p_error_table => l_error_tbl
846 , x_error_table => x_error_tbl
847 );
848 WHEN NO_DATA_FOUND THEN
849 x_return_status := FND_API.G_RET_STS_ERROR ;
850 --RAISE FND_API.G_EXC_ERROR;
851 WHEN FND_API.G_EXC_ERROR THEN
852 x_return_status := FND_API.G_RET_STS_ERROR ;
853 --RAISE FND_API.G_EXC_ERROR;
854 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
855 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
856 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
857 WHEN OTHERS THEN
858 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
859 l_error_tbl := x_error_tbl;
860 BIS_UTILITIES_PVT.Add_Error_Message
861 ( p_error_msg_id => SQLCODE
862 , p_error_description => SQLERRM
863 , p_error_proc_name => G_PKG_NAME||'.Update_Dimension'
864 , p_error_table => l_error_tbl
865 , x_error_table => x_error_tbl
866 );
867 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
868
869 END Update_Dimension;
870 --
871 --
872 PROCEDURE Translate_Dimension
873 ( p_api_version IN NUMBER
874 , p_commit IN VARCHAR2 := FND_API.G_FALSE
875 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
876 , p_Dimension_Rec IN BIS_DIMENSION_PUB.Dimension_Rec_Type
877 , x_return_status OUT NOCOPY VARCHAR2
878 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
879 )
880 IS
881 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
882 l_Dimension_Rec BIS_DIMENSION_PUB.Dimension_Rec_Type;
883 BEGIN
884
885 l_Dimension_Rec := p_Dimension_Rec;
886 l_Dimension_Rec.Last_Update_Date := NVL(p_Dimension_Rec.Last_Update_Date, SYSDATE);
887 Translate_Dimension
888 ( p_api_version => p_api_version
889 , p_commit => p_commit
890 , p_validation_level => p_validation_level
891 , p_Dimension_Rec => l_Dimension_Rec
892 , p_owner => BIS_UTILITIES_PUB.G_CUSTOM_OWNER
893 , x_return_status => x_return_status
894 , x_error_Tbl => x_error_Tbl
895 );
896
897 --commented out NOCOPY RAISE
898 EXCEPTION
899 WHEN OTHERS THEN
900 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
901 l_error_tbl := x_error_tbl;
902 BIS_UTILITIES_PVT.Add_Error_Message
903 ( p_error_msg_id => SQLCODE
904 , p_error_description => SQLERRM
905 , p_error_proc_name => G_PKG_NAME||'.Translate_Dimension'
906 , p_error_table => l_error_tbl
907 , x_error_table => x_error_tbl
908 );
909
910 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
911
912 END Translate_Dimension;
913 --
914 PROCEDURE Translate_Dimension
915 ( p_api_version IN NUMBER
916 , p_commit IN VARCHAR2 := FND_API.G_FALSE
917 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
918 , p_Dimension_Rec IN BIS_DIMENSION_PUB.Dimension_Rec_Type
919 , p_owner IN VARCHAR2
920 , x_return_status OUT NOCOPY VARCHAR2
921 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
922 )
923 IS
924 l_user_id NUMBER;
925 l_login_id NUMBER;
926 l_count NUMBER := 0;
927 l_Dimension_Rec BIS_DIMENSION_PUB.Dimension_Rec_Type;
928 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
929
930 BEGIN
931
932 -- retrieve record from database and apply changes
933 UpdateRecord
934 ( p_Dimension_Rec => p_Dimension_Rec
935 , x_Dimension_Rec => l_Dimension_Rec
936 , x_return_status => x_return_status
937 , x_error_Tbl => x_error_Tbl
938 );
939
940 Validate_Dimension
941 ( p_api_version
942 , p_validation_level
943 , l_Dimension_Rec
944 , x_return_status
945 , x_error_Tbl
946 );
947
948 IF( x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
949 RAISE FND_API.G_EXC_ERROR;
950 END IF;
951 --
952 l_user_id := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
953 l_login_id := fnd_global.LOGIN_ID;
954 --
955
956 l_Dimension_Rec.Last_Update_Date := NVL(p_Dimension_Rec.Last_Update_Date, SYSDATE);
957
958 Update bis_dimensions_TL
959 set
960 NAME = l_Dimension_Rec.Dimension_Name
964 , LAST_UPDATE_LOGIN = l_login_id
961 , DESCRIPTION = l_Dimension_Rec.description
962 , LAST_UPDATE_DATE = l_Dimension_Rec.Last_Update_Date
963 , LAST_UPDATED_BY = l_user_id
965 , SOURCE_LANG = userenv('LANG')
966 where DIMENSION_ID = l_Dimension_Rec.Dimension_Id
967 and userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
968
969 IF (p_commit = FND_API.G_TRUE) THEN
970 COMMIT;
971 END if;
972
973 --commented out NOCOPY RAISE
974 EXCEPTION
975 WHEN NO_DATA_FOUND THEN
976 x_return_status := FND_API.G_RET_STS_ERROR ;
977 --RAISE FND_API.G_EXC_ERROR;
978 WHEN FND_API.G_EXC_ERROR THEN
979 x_return_status := FND_API.G_RET_STS_ERROR ;
980 --RAISE FND_API.G_EXC_ERROR;
981 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
982 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
983 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
984 WHEN OTHERS THEN
985 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
986 l_error_tbl := x_error_tbl;
987 BIS_UTILITIES_PVT.Add_Error_Message
988 ( p_error_msg_id => SQLCODE
989 , p_error_description => SQLERRM
990 , p_error_proc_name => G_PKG_NAME||'.Translate_Dimension'
991 , p_error_table => l_error_tbl
992 , x_error_table => x_error_tbl
993 );
994 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
995
996 END Translate_Dimension;
997 --
998 --
999 PROCEDURE Validate_Dimension
1000 ( p_api_version IN NUMBER
1001 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1002 , p_Dimension_Rec IN BIS_DIMENSION_PUB.Dimension_Rec_Type
1003 , x_return_status OUT NOCOPY VARCHAR2
1004 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1005 )
1006 IS
1007 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1008 l_error_tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
1009 BEGIN
1010
1011 BEGIN
1012
1013 BIS_DIMENSION_VALIDATE_PVT.Validate_Record
1014 ( p_api_version => p_api_version
1015 , p_validation_level => p_validation_level
1016 , p_Dimension_Rec => p_Dimension_Rec
1017 , x_return_status => x_return_status
1018 , x_error_tbl => l_error_Tbl
1019 );
1020
1021 EXCEPTION
1022 WHEN FND_API.G_EXC_ERROR THEN
1023 l_error_tbl_p := x_error_tbl;
1024 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_tbl_p
1025 , l_error_Tbl
1026 , x_error_tbl
1027 );
1028 x_return_status := FND_API.G_RET_STS_ERROR;
1029 END;
1030
1031 IF (x_error_tbl.count > 0) THEN
1032 RAISE FND_API.G_EXC_ERROR;
1033 END if;
1034
1035 --commented out NOCOPY RAISE
1036 EXCEPTION
1037 WHEN NO_DATA_FOUND THEN
1038 x_return_status := FND_API.G_RET_STS_ERROR ;
1039 --RAISE FND_API.G_EXC_ERROR;
1040 WHEN FND_API.G_EXC_ERROR THEN
1041 x_return_status := FND_API.G_RET_STS_ERROR ;
1042 --RAISE FND_API.G_EXC_ERROR;
1043 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1044 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1045 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1046 WHEN OTHERS THEN
1047 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1048 l_error_tbl_p := x_error_tbl;
1049 BIS_UTILITIES_PVT.Add_Error_Message
1050 ( p_error_msg_id => SQLCODE
1051 , p_error_description => SQLERRM
1052 , p_error_proc_name => G_PKG_NAME||'.Validate_Dimension'
1053 , p_error_table => l_error_tbl_p
1054 , x_error_table => x_error_tbl
1055 );
1056 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1057
1058 END Validate_Dimension;
1059 --
1060 -- Value - ID conversion
1061 PROCEDURE Value_ID_Conversion
1062 ( p_api_version IN NUMBER
1063 , p_Dimension_Rec IN BIS_DIMENSION_PUB.Dimension_Rec_Type
1064 , x_Dimension_Rec IN OUT NOCOPY BIS_DIMENSION_PUB.Dimension_Rec_Type
1065 , x_return_status OUT NOCOPY VARCHAR2
1066 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1067 )
1068 IS
1069 BEGIN
1070
1071 x_return_status := FND_API.G_RET_STS_SUCCESS;
1072 x_Dimension_Rec := p_Dimension_Rec;
1073
1074 IF (BIS_UTILITIES_PUB.Value_Missing(x_Dimension_Rec.Dimension_id)
1075 = FND_API.G_TRUE) THEN
1076 BIS_DIMENSION_PVT.Value_ID_Conversion
1077 ( p_api_version
1078 , x_Dimension_Rec.Dimension_Short_Name
1079 , x_Dimension_Rec.Dimension_Name
1080 , x_Dimension_Rec.Dimension_ID
1081 , x_return_status
1082 , x_error_Tbl
1083 );
1084 END if;
1085
1086 --comment out NOCOPY RAISE
1087 EXCEPTION
1088 WHEN NO_DATA_FOUND THEN
1089 x_return_status := FND_API.G_RET_STS_ERROR ;
1090 --RAISE FND_API.G_EXC_ERROR;
1091 WHEN FND_API.G_EXC_ERROR THEN
1092 x_return_status := FND_API.G_RET_STS_ERROR ;
1093 --RAISE FND_API.G_EXC_ERROR;
1094 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1095 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1096 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1097 WHEN OTHERS THEN
1098 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1099 BIS_UTILITIES_PVT.Add_Error_Message
1103 );
1100 ( p_error_msg_id => SQLCODE
1101 , p_error_description => SQLERRM
1102 , p_error_proc_name => G_PKG_NAME||'.Value_ID_Conversion'
1104 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1105
1106 END Value_ID_Conversion;
1107 --
1108 PROCEDURE Value_ID_Conversion
1109 ( p_api_version IN NUMBER
1110 , p_Dimension_Short_Name IN VARCHAR2
1111 , p_Dimension_Name IN VARCHAR2
1112 , x_Dimension_ID OUT NOCOPY NUMBER
1113 , x_return_status OUT NOCOPY VARCHAR2
1114 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1115 )
1116 is
1117 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1118 begin
1119
1120 x_return_status := FND_API.G_RET_STS_SUCCESS;
1121
1122 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_Dimension_Short_Name)
1123 = FND_API.G_TRUE) THEN
1124 SELECT dimension_id INTO x_Dimension_ID
1125 FROM bis_dimensions_vl
1126 WHERE short_name = p_Dimension_Short_Name;
1127 elsIF (BIS_UTILITIES_PUB.Value_Not_Missing(p_Dimension_Name)
1128 = FND_API.G_TRUE) THEN
1129 SELECT dimension_id INTO x_Dimension_ID
1130 FROM bis_dimensions_vl
1131 WHERE name = p_Dimension_Name;
1132 else
1133 l_error_tbl := x_error_tbl;
1134 BIS_UTILITIES_PVT.Add_Error_Message
1135 ( p_error_msg_name => 'BIS_NAME_SHORT_NAME_MISSING'
1136 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
1137 , p_error_proc_name => G_PKG_NAME||'.Value_ID_Conversion'
1138 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
1139 , p_error_table => l_error_tbl
1140 , x_error_table => x_error_tbl
1141 );
1142
1143 RAISE FND_API.G_EXC_ERROR;
1144 END if;
1145
1146
1147 EXCEPTION
1148 WHEN NO_DATA_FOUND THEN
1149 x_return_status := FND_API.G_RET_STS_ERROR ;
1150 WHEN FND_API.G_EXC_ERROR THEN
1151 x_return_status := FND_API.G_RET_STS_ERROR ;
1152 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1153 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1154 WHEN OTHERS THEN
1155 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1156 l_error_tbl := x_error_tbl;
1157 BIS_UTILITIES_PVT.Add_Error_Message
1158 ( p_error_msg_id => SQLCODE
1159 , p_error_description => SQLERRM
1160 , p_error_proc_name => G_PKG_NAME||'.Value_ID_Conversion'
1161 , p_error_table => l_error_tbl
1162 , x_error_table => x_error_tbl
1163 );
1164
1165 END Value_ID_Conversion;
1166 --
1167 /* modified from ansingha's FUNCTION */
1168 FUNCTION DuplicateDimension
1169 ( p_dimension_rec BIS_DIMENSION_PUB.Dimension_Rec_Type
1170 , p_dimensions_tbl BIS_DIMENSION_PUB.Dimension_Tbl_Type
1171 ) return BOOLEAN
1172 is
1173 begin
1174 for i in 1 .. p_dimensions_tbl.count loop
1175 IF (p_dimensions_tbl(i).dimension_id = p_dimension_rec.dimension_id) THEN
1176 return TRUE;
1177 END if;
1178 END loop;
1179 return FALSE;
1180
1181 EXCEPTION
1182 WHEN NO_DATA_FOUND THEN
1183 RAISE FND_API.G_EXC_ERROR;
1184 WHEN FND_API.G_EXC_ERROR THEN
1185 RAISE FND_API.G_EXC_ERROR;
1186 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1187 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1188 WHEN OTHERS THEN
1189 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1190
1191 END DuplicateDimension;
1192 --
1193 PROCEDURE RemoveDuplicates
1194 ( p_dimension_table in BIS_DIMENSION_PUB.Dimension_tbl_type
1195 , p_all_dimension_table in BIS_DIMENSION_PUB.Dimension_tbl_type
1196 , x_all_dimension_table out NOCOPY BIS_DIMENSION_PUB.Dimension_tbl_type
1197 )
1198 is
1199 l_unique BOOLEAN;
1200 l_rec BIS_DIMENSION_PUB.Dimension_Rec_Type;
1201 begin
1202 --
1203 for i in 1 .. p_all_dimension_table.count loop
1204 l_rec := p_all_dimension_table(i);
1205 l_unique := true;
1206 --
1207 for j in 1 .. p_dimension_table.count loop
1208 IF (p_dimension_table(j).Dimension_ID = l_rec.Dimension_ID) THEN
1209 l_unique := false;
1210 exit;
1211 END if;
1212 END loop;
1213 --
1214 IF (l_unique) THEN
1215 x_all_dimension_table(x_all_dimension_table.count + 1) := l_rec;
1216 END if;
1217 --
1218 END loop;
1219 --
1220 END RemoveDuplicates;
1221 --
1222
1223 PROCEDURE Delete_Dimension
1224 (
1225 p_commit IN VARCHAR2 := FND_API.G_FALSE
1226 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1227 , p_Dimension_Rec IN BIS_DIMENSION_PUB.Dimension_Rec_Type
1228 , x_return_status OUT NOCOPY VARCHAR2
1229 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1230 ) IS
1231 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1232 l_Dimension_Rec BIS_DIMENSION_PUB.Dimension_Rec_Type;
1233 BEGIN
1234 SAVEPOINT DeleteFromBISDims;
1235
1236 BIS_DIMENSION_PVT.Retrieve_Dimension
1237 ( p_api_version => 1.0
1238 , p_Dimension_Rec => p_Dimension_Rec
1239 , x_Dimension_Rec => l_Dimension_Rec
1240 , x_return_status => x_return_status
1241 , x_error_Tbl => x_error_Tbl
1242 );
1243 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1247 DELETE FROM bis_dimensions WHERE
1244 RAISE FND_API.G_EXC_ERROR;
1245 END IF;
1246
1248 DIMENSION_ID = l_Dimension_Rec.dimension_id;
1249
1250 DELETE FROM bis_dimensions_tl WHERE
1251 DIMENSION_ID = l_Dimension_Rec.dimension_id;
1252 IF (p_commit = FND_API.G_TRUE) THEN
1253 COMMIT;
1254 END if;
1255 x_return_status := FND_API.G_RET_STS_SUCCESS;
1256 EXCEPTION
1257 WHEN NO_DATA_FOUND THEN
1258 x_return_status := FND_API.G_RET_STS_ERROR ;
1259 ROLLBACK TO DeleteFromBISDims;
1260 WHEN FND_API.G_EXC_ERROR THEN
1261 x_return_status := FND_API.G_RET_STS_ERROR ;
1262 ROLLBACK TO DeleteFromBISDims;
1263 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1264 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1265 ROLLBACK TO DeleteFromBISDims;
1266 WHEN OTHERS THEN
1267 l_error_tbl := x_error_tbl;
1268 BIS_UTILITIES_PVT.Add_Error_Message
1269 ( p_error_msg_id => SQLCODE
1270 , p_error_description => SQLERRM
1271 , p_error_proc_name => G_PKG_NAME||'.Delete_Dimension'
1272 , p_error_table => l_error_tbl
1273 , x_error_table => x_error_tbl
1274 );
1275 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1276 ROLLBACK TO DeleteFromBISDims;
1277 END Delete_Dimension;
1278 --
1279
1280 PROCEDURE Translate_Dim_By_Given_Lang
1281 (
1282 p_commit IN VARCHAR2 := FND_API.G_FALSE
1283 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1284 , p_Dimension_Rec IN BIS_DIMENSION_PUB.Dimension_Rec_Type
1285 , x_return_status OUT NOCOPY VARCHAR2
1286 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1287 ) IS
1288
1289 l_dim_id NUMBER;
1290 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1291 l_user_id NUMBER;
1292 l_login_id NUMBER;
1293
1294 BEGIN
1295 SAVEPOINT TransDimByLangPvt;
1296
1297 l_user_id := FND_GLOBAL.USER_ID;
1298 l_login_id := fnd_global.LOGIN_ID;
1299
1300 SELECT DIMENSION_ID
1301 INTO l_dim_id
1302 FROM BIS_DIMENSIONS
1303 WHERE SHORT_NAME = p_Dimension_Rec.Dimension_Short_Name;
1304
1305 UPDATE BIS_DIMENSIONS_TL
1306 SET NAME = p_Dimension_Rec.Dimension_Name
1307 ,DESCRIPTION = p_Dimension_Rec.Description
1308 ,LAST_UPDATE_DATE = p_Dimension_Rec.Last_Update_Date
1309 ,LAST_UPDATED_BY = l_user_id
1310 ,LAST_UPDATE_LOGIN = l_login_id
1311 ,SOURCE_LANG = p_Dimension_Rec.Source_Lang
1312 WHERE DIMENSION_ID = l_dim_id
1313 AND LANGUAGE = p_Dimension_Rec.Language;
1314
1315 IF (p_commit = FND_API.G_TRUE) THEN
1316 COMMIT;
1317 END if;
1318 x_return_status := FND_API.G_RET_STS_SUCCESS;
1319
1320 EXCEPTION
1321 WHEN NO_DATA_FOUND THEN
1322 x_return_status := FND_API.G_RET_STS_ERROR ;
1323 ROLLBACK TO TransDimByLangPvt;
1324 WHEN FND_API.G_EXC_ERROR THEN
1325 x_return_status := FND_API.G_RET_STS_ERROR ;
1326 ROLLBACK TO TransDimByLangPvt;
1327 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1328 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1329 ROLLBACK TO TransDimByLangPvt;
1330 WHEN OTHERS THEN
1331 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1332 l_error_tbl := x_error_tbl;
1333 BIS_UTILITIES_PVT.Add_Error_Message
1334 ( p_error_msg_id => SQLCODE
1335 , p_error_description => SQLERRM
1336 , p_error_proc_name => G_PKG_NAME||'.Translate_Dim_By_Given_Lang'
1337 , p_error_table => l_error_tbl
1338 , x_error_table => x_error_tbl
1339 );
1340 ROLLBACK TO TransDimByLangPvt;
1341 END Translate_Dim_By_Given_Lang;
1342
1343 -- Bug#4172055: This API validates only PMF type dimensions.
1344 PROCEDURE Validate_PMF_Unique_Name
1345 ( p_Dimension_Short_Name IN VARCHAR2
1346 , p_Dimension_Name IN VARCHAR2
1347 , x_return_status OUT NOCOPY VARCHAR2
1348 )
1349 IS
1350 CURSOR c_unique_name IS
1351 SELECT BD.short_name, BD.name, DECODE((SELECT count(1)
1352 FROM bsc_sys_dim_levels_by_group DLG, bsc_sys_dim_levels_b DLB
1353 WHERE DLB.source = 'PMF'
1354 AND DLG.dim_level_id = DLB.dim_level_id
1355 AND BG.dim_group_id = DLG.dim_group_id), 0, 'BSC', 'PMF') type
1356 FROM bis_dimensions_vl BD, bsc_sys_dim_groups_vl BG
1357 WHERE UPPER(BD.Name) = UPPER(p_Dimension_Name)
1358 AND BD.dim_grp_id = BG.dim_group_id
1359 AND BD.short_name <> p_Dimension_Short_Name;
1360
1361 l_unique_name_rec c_unique_name%ROWTYPE;
1362 l_count NUMBER;
1363 BEGIN
1364 SELECT COUNT(1) INTO l_count
1365 FROM bis_dimensions_vl
1366 WHERE UPPER(name) = UPPER(p_Dimension_Name)
1367 AND short_name <> p_Dimension_Short_Name;
1368
1369 IF (l_count <> 0) THEN
1370 FOR l_unique_name_rec IN c_unique_name LOOP
1371 IF (l_unique_name_rec.type = 'PMF') THEN
1372 x_return_status := FND_API.G_RET_STS_ERROR;
1373 ELSE
1374 Rename_BSC_Dimension(l_unique_name_rec.Short_Name, l_unique_name_rec.Name);
1375 END IF;
1376 END LOOP;
1377 END IF;
1378 EXCEPTION
1379 WHEN OTHERS THEN
1380 NULL;
1381 END Validate_PMF_Unique_Name;
1382
1383 PROCEDURE Rename_BSC_Dimension
1384 ( p_Dimension_Short_Name IN VARCHAR2
1385 , p_Dimension_Name IN VARCHAR2
1386 )
1387 IS
1388 l_new_disp_name VARCHAR2(255);
1389 l_count NUMBER := 1;
1390 BEGIN
1391 l_new_disp_name := p_Dimension_Name;
1392 WHILE (l_count > 0) LOOP
1393 l_new_disp_name := BSC_UTILITY.get_Next_DispName(l_new_disp_name);
1394
1395 SELECT COUNT(1) INTO l_count
1396 FROM bis_dimensions_vl
1397 WHERE UPPER(name) = UPPER(l_new_disp_name);
1398
1399 END LOOP;
1400
1401 UPDATE bis_dimensions_tl
1402 SET name = l_new_disp_name
1403 WHERE dimension_id = (SELECT dimension_id FROM bis_dimensions WHERE short_name = p_Dimension_Short_Name)
1404 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
1405
1406 EXCEPTION
1407 WHEN OTHERS THEN
1408 NULL;
1409 END Rename_BSC_Dimension;
1410
1411 END BIS_DIMENSION_PVT;