[Home] [Help]
PACKAGE BODY: APPS.BIS_TARGET_LEVEL_PUB
Source
1 PACKAGE BODY BIS_Target_Level_PUB AS
2 /* $Header: BISPTALB.pls 120.0 2005/06/01 15:39:59 appldev noship $ */
3 --
4 /*
5 REM +=======================================================================+
6 REM | Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA |
7 REM | All rights reserved. |
8 REM +=======================================================================+
9 REM | FILENAME |
10 REM | BISPMEAB.pls |
11 REM | |
12 REM | DESCRIPTION |
13 REM | Public API for creating and managing Performance Measurements
14 REM |
15 REM | NOTES |
16 REM | |
17 REM | HISTORY |
18 REM | 28-NOV-98 irchen Creation
19 REM | 23-JAN-03 mahrao For having different local variables for IN and OUT
20 REM | parameters.
21 REM | 30-JAN-03 sugopal FND_API.G_MISS_xxx should not be used in |
22 REM | initialization or declaration (bug#2774644) |
23 REM | 28-JUN-04 ankgoel Removed Retrieve_Measure_Notify_Resps for |
24 REM | bug#3634587 |
25 REM +=======================================================================+
26 */
27 --
28 G_PKG_NAME CONSTANT VARCHAR2(30):='BIS_TARGET_LEVEL_PUB';
29
30
31 --
32 -- creates one Indicator Level
33 PROCEDURE Create_Target_Level
34 ( p_api_version IN NUMBER
35 , p_commit IN VARCHAR2 := FND_API.G_FALSE
36 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
37 , x_return_status OUT NOCOPY VARCHAR2
38 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
39 )
40 IS
41 l_Target_Level_Rec BIS_Target_Level_PUB.Target_Level_Rec_Type;
42 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
43 BEGIN
44
45 BIS_Target_Level_PVT.Value_ID_Conversion
46 ( p_api_version => p_api_version
47 , p_Target_Level_Rec => p_Target_Level_Rec
48 , x_Target_Level_Rec => l_Target_Level_Rec
49 , x_return_status => x_return_status
50 , x_error_Tbl => x_error_Tbl
51 );
52
53 BIS_Target_Level_PVT.Create_Target_Level
54 ( p_api_version => p_api_version
55 , p_commit => p_commit
56 , p_Target_Level_Rec => l_Target_Level_Rec
57 , p_owner => BIS_UTILITIES_PUB.G_CUSTOM_OWNER
58 , x_return_status => x_return_status
59 , x_error_Tbl => x_error_Tbl
60 );
61
62
63 EXCEPTION
64 WHEN NO_DATA_FOUND THEN
65 x_return_status := FND_API.G_RET_STS_ERROR ;
66 when FND_API.G_EXC_ERROR then
67 x_return_status := FND_API.G_RET_STS_ERROR ;
68 when FND_API.G_EXC_UNEXPECTED_ERROR then
69 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
70 when others then
71 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
72 l_error_tbl := x_error_Tbl;
73 BIS_UTILITIES_PVT.Add_Error_Message
74 ( p_error_msg_id => SQLCODE
75 , p_error_description => SQLERRM
76 , p_error_proc_name => G_PKG_NAME||'.Create_Target_Level'
77 , p_error_table => l_error_tbl
78 , x_error_table => x_error_tbl
79 );
80
81 END Create_Target_Level;
82 --
83 --
84 -- Gets All Indicator Levels
85 -- If information about the dimensions are not required, set all_info to
86 -- FALSE
87 PROCEDURE Retrieve_Target_Levels
88 ( p_api_version IN NUMBER
89 , p_all_info IN VARCHAR2 := FND_API.G_TRUE
90 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
91 , x_Target_Level_tbl OUT NOCOPY BIS_Target_Level_PUB.Target_Level_Tbl_Type
92 , x_return_status OUT NOCOPY VARCHAR2
93 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
94 )
95 IS
96 l_Measure_Rec BIS_MEASURE_PUB.Measure_Rec_Type;
97 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
98 l_target_level_tbl BIS_Target_Level_PUB.Target_Level_Tbl_Type;
99 BEGIN
100
101 l_measure_rec := p_measure_rec;
102
103 if (BIS_UTILITIES_PUB.Value_Missing
104 (l_Measure_Rec.Measure_id) = FND_API.G_TRUE
105 OR BIS_UTILITIES_PUB.Value_NULL(l_Measure_Rec.Measure_id)
106 = FND_API.G_TRUE) then
107 BIS_MEASURE_PVT.Value_ID_Conversion
108 ( p_api_version => p_api_version
109 , p_Measure_Short_Name => l_Measure_Rec.Measure_Short_Name
110 , p_Measure_Name => l_Measure_Rec.Measure_Name
111 , x_Measure_ID => l_Measure_Rec.Measure_Id
112 , x_return_status => x_return_status
113 , x_error_Tbl => x_error_Tbl
114 );
115 end if;
116
117 BIS_Target_Level_PVT.Retrieve_Target_Levels
118 ( p_api_version => p_api_version
119 , p_all_info => p_all_info
120 , p_MeasurE_rec => l_Measure_Rec
121 , x_Target_Level_tbl => x_Target_Level_tbl
122 , x_return_status => x_return_status
123 , x_error_Tbl => x_error_Tbl
124 );
125 -- Put the values in Org and Time Levels for the product teams still using this
126 IF (x_target_level_tbl.COUNT > 0) THEN
127 l_target_level_tbl := x_target_level_tbl;
128 FOR l_count IN 1..l_target_level_tbl.COUNT LOOP
129 BIS_UTILITIES_PVT.resequence_dim_levels(l_target_level_tbl(l_count),
130 'R',
131 x_target_level_tbl(l_count),
132 x_Error_tbl);
133 END LOOP;
134 END IF;
135 --
136 EXCEPTION
137 WHEN NO_DATA_FOUND THEN
138 x_return_status := FND_API.G_RET_STS_ERROR ;
139 when FND_API.G_EXC_ERROR then
140 x_return_status := FND_API.G_RET_STS_ERROR ;
141 when FND_API.G_EXC_UNEXPECTED_ERROR then
142 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
143 when others then
144 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
145 l_error_tbl := x_error_Tbl;
146 BIS_UTILITIES_PVT.Add_Error_Message
147 ( p_error_msg_id => SQLCODE
148 , p_error_description => SQLERRM
149 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Target_Levels'
150 , p_error_table => l_error_tbl
151 , x_error_table => x_error_tbl
152 );
153
154 END Retrieve_Target_Levels;
155 --
156 --
157 -- Gets Information for one Indicator Level
158 -- If information about the dimension are not required, set all_info to FALSE.
159 PROCEDURE Retrieve_Target_Level
160 ( p_api_version IN NUMBER
161 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
162 , p_all_info IN VARCHAR2 := FND_API.G_TRUE
163 , x_Target_Level_Rec IN OUT NOCOPY BIS_Target_Level_PUB.Target_Level_Rec_Type
164 , x_return_status OUT NOCOPY VARCHAR2
165 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
166 )
167 IS
168 l_Target_Level_Rec BIS_Target_Level_PUB.Target_Level_Rec_Type;
169 l_Target_Level_Rec_p BIS_Target_Level_PUB.Target_Level_Rec_Type;
170 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
171 BEGIN
172
173 l_target_level_rec := p_target_level_rec;
174
175 BIS_Target_Level_PVT.Value_ID_Conversion
176 ( p_api_version => p_api_version
177 , p_Target_Level_Rec => p_Target_Level_Rec
178 , x_Target_Level_Rec => l_Target_Level_Rec
179 , x_return_status => x_return_status
180 , x_error_Tbl => x_error_Tbl
181 );
182 -- This is for backward compatibility. For the Product teams which still us Org/Time stuff
183 -- in their code , the data model changes will be transparent.
184 IF (l_target_level_rec.org_level_id IS NOT NULL) AND
185 (l_target_level_rec.time_level_id IS NOT NULL) THEN
186 --resequence the dimensions
187 l_target_level_rec_p := l_target_level_rec;
188 BIS_UTILITIES_PVT.resequence_dim_levels(l_target_level_rec_p,
189 'N',
190 l_target_level_Rec,
191 x_Error_tbl);
192 END IF;
193
194
195 BIS_Target_Level_PVT.Retrieve_Target_Level
196 ( p_api_version => p_api_version
197 , p_Target_Level_Rec => l_Target_Level_Rec
198 , p_all_info => p_all_info
199 , x_Target_Level_Rec => x_Target_Level_Rec
200 , x_return_status => x_return_status
201 , x_error_Tbl => x_error_Tbl
202 );
203
204
205 --For the time being
206 if(x_return_status = FND_API.G_RET_STS_SUCCESS) then
207
208 --Put the values back in Org/Time level stuff for product teams using this API
209 l_target_level_rec_p := x_target_level_rec;
210 BIS_UTILITIES_PVT.resequence_dim_levels(l_target_level_rec_p,
211 'R',
212 x_target_level_Rec,
213 x_error_tbl);
214 end if;
215
216
217 EXCEPTION
218 WHEN NO_DATA_FOUND THEN
219 x_return_status := FND_API.G_RET_STS_ERROR ;
220 when FND_API.G_EXC_ERROR then
221 x_return_status := FND_API.G_RET_STS_ERROR ;
222 when FND_API.G_EXC_UNEXPECTED_ERROR then
223 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
224 when others then
225 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
226 l_error_tbl := x_error_Tbl;
227 BIS_UTILITIES_PVT.Add_Error_Message
228 ( p_error_msg_id => SQLCODE
229 , p_error_description => SQLERRM
230 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Target_Level'
231 , p_error_table => l_error_tbl
232 , x_error_table => x_error_tbl
233 );
234
235 END Retrieve_Target_Level;
236 --
237 --
238 -- Update_Target_Levels
239 PROCEDURE Update_Target_Level
240 ( p_api_version IN NUMBER
241 , p_commit IN VARCHAR2 := FND_API.G_FALSE
242 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
243 , x_return_status OUT NOCOPY VARCHAR2
244 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
245 )
246 IS
247 l_Target_Level_Rec BIS_Target_Level_PUB.Target_Level_Rec_Type;
248 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
249 BEGIN
250
251 BIS_Target_Level_PVT.Value_ID_Conversion
252 ( p_api_version => p_api_version
253 , p_Target_Level_Rec => p_Target_Level_Rec
254 , x_Target_Level_Rec => l_Target_Level_Rec
255 , x_return_status => x_return_status
256 , x_error_Tbl => x_error_Tbl
257 );
258
259 BIS_Target_Level_PVT.Update_Target_Level
260 ( p_api_version => p_api_version
261 , p_commit => p_commit
262 , p_Target_Level_Rec => l_Target_Level_Rec
263 , p_owner => BIS_UTILITIES_PUB.G_CUSTOM_OWNER
264 , x_return_status => x_return_status
265 , x_error_Tbl => x_error_Tbl
266 );
267
268 EXCEPTION
269 WHEN NO_DATA_FOUND THEN
270 x_return_status := FND_API.G_RET_STS_ERROR ;
271 when FND_API.G_EXC_ERROR then
272 x_return_status := FND_API.G_RET_STS_ERROR ;
273 when FND_API.G_EXC_UNEXPECTED_ERROR then
274 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
275 when others then
276 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
277 l_error_tbl := x_error_Tbl;
278 BIS_UTILITIES_PVT.Add_Error_Message
279 ( p_error_msg_id => SQLCODE
280 , p_error_description => SQLERRM
281 , p_error_proc_name => G_PKG_NAME||'.Update_Target_Level'
282 , p_error_table => l_error_tbl
283 , x_error_table => x_error_tbl
284 );
285
286 END Update_Target_Level;
287 --
288 --
289 -- deletes one Target_Level
290 PROCEDURE Delete_Target_Level
291 ( p_api_version IN NUMBER
292 , p_commit IN VARCHAR2 := FND_API.G_FALSE
293 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
294 , x_return_status OUT NOCOPY VARCHAR2
295 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
296 )
297 IS
298 l_Target_Level_Rec BIS_Target_Level_PUB.Target_Level_Rec_Type;
299 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
300 BEGIN
301
302 x_return_status := FND_API.G_RET_STS_SUCCESS;
303
304 l_target_level_rec := p_target_level_rec;
305
306 if ( BIS_UTILITIES_PUB.Value_Missing
307 (p_Target_Level_Rec.Target_Level_id) = FND_API.G_TRUE
308 OR BIS_UTILITIES_PUB.Value_NULL
309 (p_Target_Level_Rec.Target_Level_id) = FND_API.G_TRUE) then
310 BIS_Target_Level_PVT.Value_ID_Conversion
311 ( p_api_version
312 , p_Target_Level_Rec.Target_Level_Short_Name
313 , p_Target_Level_Rec.Target_Level_Name
314 , l_Target_Level_Rec.Target_Level_ID
315 , x_return_status
316 , x_error_Tbl
317 );
318 end if;
319
320 BIS_Target_Level_PVT.Delete_Target_Level
321 ( p_api_version => p_api_version
322 , p_commit => p_commit
323 , p_Target_Level_Rec => l_Target_Level_Rec
324 , x_return_status => x_return_status
325 , x_error_Tbl => x_error_Tbl
326 );
327
328
329 EXCEPTION
330 WHEN NO_DATA_FOUND THEN
331 x_return_status := FND_API.G_RET_STS_ERROR ;
332 when FND_API.G_EXC_ERROR then
333 x_return_status := FND_API.G_RET_STS_ERROR ;
334 when FND_API.G_EXC_UNEXPECTED_ERROR then
335 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
336 when others then
337 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
338 l_error_tbl := x_error_Tbl;
339 BIS_UTILITIES_PVT.Add_Error_Message
340 ( p_error_msg_id => SQLCODE
341 , p_error_description => SQLERRM
342 , p_error_proc_name => G_PKG_NAME||'.Delete_Target_Level'
343 , p_error_table => l_error_tbl
344 , x_error_table => x_error_tbl
345 );
346
347 END Delete_Target_Level;
348 --
349 --
350 -- Validates measure
351 PROCEDURE Validate_Target_Level
352 ( p_api_version IN NUMBER
353 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
354 , x_return_status OUT NOCOPY VARCHAR2
355 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
356 )
357 IS
358 l_Target_Level_Rec BIS_Target_Level_PUB.Target_Level_Rec_Type;
359 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
360 BEGIN
361
362 BIS_Target_Level_PVT.Value_ID_Conversion
363 ( p_api_version => p_api_version
364 , p_Target_Level_Rec => p_Target_Level_Rec
365 , x_Target_Level_Rec => l_Target_Level_Rec
366 , x_return_status => x_return_status
367 , x_error_Tbl => x_error_Tbl
368 );
369
370 BIS_Target_Level_PVT.Validate_Target_Level
371 ( p_api_version => p_api_version
372 , p_Target_Level_Rec => p_Target_Level_Rec
373 , x_return_status => x_return_status
374 , x_error_Tbl => x_error_Tbl
375 );
376
377 EXCEPTION
378 WHEN NO_DATA_FOUND THEN
379 x_return_status := FND_API.G_RET_STS_ERROR ;
380 when FND_API.G_EXC_ERROR then
381 x_return_status := FND_API.G_RET_STS_ERROR ;
382 when FND_API.G_EXC_UNEXPECTED_ERROR then
383 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
384 when others then
385 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
386 l_error_tbl := x_error_Tbl;
387 BIS_UTILITIES_PVT.Add_Error_Message
388 ( p_error_msg_id => SQLCODE
389 , p_error_description => SQLERRM
390 , p_error_proc_name => G_PKG_NAME||'.Validate_Target_Level'
391 , p_error_table => l_error_tbl
392 , x_error_table => x_error_tbl
393 );
394
395 END Validate_Target_Level;
396 --
397 PROCEDURE Get_User_Id
398 ( p_api_version IN NUMBER
399 , p_user_name IN VARCHAR2 := BIS_UTILITIES_PUB.G_NULL_CHAR
400 , x_user_id OUT NOCOPY NUMBER
401 , x_return_status OUT NOCOPY VARCHAR2
402 , x_error_tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
403 )
404 is
405 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
406 BEGIN
407 x_return_status := FND_API.G_RET_STS_SUCCESS;
408
409 select user_id into x_user_id
410 from fnd_user where user_name = p_user_name
411 and start_date <= sysdate
412 and NVL(end_date,sysdate) >= sysdate;
413
414
415 EXCEPTION
416 WHEN NO_DATA_FOUND THEN
417 x_return_status := FND_API.G_RET_STS_ERROR ;
418 when FND_API.G_EXC_ERROR then
419 x_return_status := FND_API.G_RET_STS_ERROR ;
420 when FND_API.G_EXC_UNEXPECTED_ERROR then
421 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
422 when others then
423 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
424 l_error_tbl := x_error_Tbl;
425 BIS_UTILITIES_PVT.Add_Error_Message
426 ( p_error_msg_id => SQLCODE
427 , p_error_description => SQLERRM
428 , p_error_proc_name => G_PKG_NAME||'.Get_User_Id'
429 , p_error_table => l_error_tbl
430 , x_error_table => x_error_tbl
431 );
432
433 END Get_User_Id;
434 --
435 Procedure Retrieve_User_Target_Levels
436 ( p_api_version IN NUMBER
437 , p_user_id IN NUMBER
438 , p_user_name IN VARCHAR2 := BIS_UTILITIES_PUB.G_NULL_CHAR
439 , p_all_info IN VARCHAR2 := FND_API.G_TRUE
440 , x_Target_Level_Tbl OUT NOCOPY BIS_Target_LEVEL_PUB.Target_Level_Tbl_Type
441 , x_return_status OUT NOCOPY VARCHAR2
442 , x_error_tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
443 )
444 IS
445 l_user_id number := p_user_id;
446 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
447 BEGIN
448 x_return_status := FND_API.G_RET_STS_SUCCESS ;
449
450 --checked for null also
451 if (BIS_UTILITIES_PUB.Value_Missing(l_user_id) = FND_API.G_TRUE
452 OR BIS_UTILITIES_PUB.Value_NULL(l_user_id)
453 = FND_API.G_TRUE)
454 then
455
456 Get_User_Id( p_api_version => 1.0
457 , p_user_name => p_user_name
458 , x_user_id => l_user_id
459 , x_return_status => x_return_status
460 , x_error_tbl => x_error_tbl
461 );
462
463
464
465 end if;
466
467 --passed in l_user_id instead of p_user_id
468 BIS_Target_Level_PVT.Retrieve_User_Target_Levels
469 ( p_api_version
470 , l_user_id
471 , p_all_info
472 , x_Target_Level_Tbl
473 , x_return_status
474 , x_error_tbl
475 );
476
477
478 EXCEPTION
479 WHEN NO_DATA_FOUND THEN
480 x_return_status := FND_API.G_RET_STS_ERROR ;
481 when FND_API.G_EXC_ERROR then
482 x_return_status := FND_API.G_RET_STS_ERROR ;
483 when FND_API.G_EXC_UNEXPECTED_ERROR then
484 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
485 when others then
486 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
487 l_error_tbl := x_error_Tbl;
488 BIS_UTILITIES_PVT.Add_Error_Message
489 ( p_error_msg_id => SQLCODE
490 , p_error_description => SQLERRM
491 , p_error_proc_name => G_PKG_NAME||'.Retrieve_User_Target_Levels'
492 , p_error_table => l_error_tbl
493 , x_error_table => x_error_tbl
494 );
495
496 END Retrieve_User_Target_Levels;
497
498 --
499 Procedure Translate_Target_Level
500 ( p_api_version IN NUMBER
501 , p_commit IN VARCHAR2 := FND_API.G_FALSE
502 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
503 , p_Target_Level_Rec IN BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type
504 , p_owner IN VARCHAR2
505 , x_return_status OUT NOCOPY VARCHAR2
506 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
507 )
508 IS
509 l_Target_Level_Rec BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
510 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
511 BEGIN
512
513 BIS_TARGET_LEVEL_PVT.Value_ID_Conversion
514 ( p_api_version => p_api_version
515 , p_Target_Level_Rec => p_Target_Level_Rec
516 , x_Target_Level_Rec => l_Target_Level_Rec
517 , x_return_status => x_return_status
518 , x_error_Tbl => x_error_tbl
519 );
520
521 --added last two parameters
522 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
523 l_error_tbl := x_error_Tbl;
524 BIS_UTILITIES_PVT.Add_Error_Message
525 ( p_error_msg_name => 'BIS_INVALID_TAR_LEVEL_ID'
526 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
527 , p_error_proc_name => G_PKG_NAME||'.Translate_Target_Level'
528 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
529 , p_error_table => l_error_tbl
530 , x_error_table => x_error_tbl
531 );
532 RAISE FND_API.G_EXC_ERROR;
533 end if;
534
535 BIS_TARGET_LEVEL_PVT.Translate_Target_Level
536 ( p_api_version => p_api_version
537 , p_commit => p_commit
538 , p_validation_level => p_validation_level
539 , p_Target_Level_Rec => l_Target_Level_Rec
540 , p_owner => p_owner
541 , x_return_status => x_return_status
542 , x_error_Tbl => x_error_Tbl
543 );
544
545 EXCEPTION
546 WHEN NO_DATA_FOUND THEN
547 x_return_status := FND_API.G_RET_STS_ERROR ;
548 when FND_API.G_EXC_ERROR then
549 x_return_status := FND_API.G_RET_STS_ERROR ;
550 when FND_API.G_EXC_UNEXPECTED_ERROR then
551 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
552 when others then
553 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
554 l_error_tbl := x_error_Tbl;
555 BIS_UTILITIES_PVT.Add_Error_Message
556 ( p_error_msg_id => SQLCODE
557 , p_error_description => SQLERRM
558 , p_error_proc_name => G_PKG_NAME||'.Translate_Target_Level'
559 , p_error_table => l_error_tbl
560 , x_error_table => x_error_tbl
561 );
562
563 END Translate_Target_Level ;
564 --
565 Procedure Load_Target_Level
566 ( p_api_version IN NUMBER
567 , p_commit IN VARCHAR2 := FND_API.G_FALSE
568 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
569 , p_Target_Level_Rec IN BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type
570 , p_owner IN VARCHAR2
571 , p_up_loaded IN VARCHAR2 := FND_API.G_FALSE
572 , x_return_status OUT NOCOPY VARCHAR2
573 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
574 )
575 IS
576 l_Target_Level_Rec BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
577 l_return_status VARCHAR2(100); -- 2486702
578 l_return_msg VARCHAR2(3000); -- 2486702
579 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
580
581 BEGIN
582
583 l_Target_Level_Rec := p_Target_Level_Rec;
584
585 BIS_TARGET_LEVEL_PVT.Value_ID_Conversion
586 ( p_api_version => p_api_version
587 , p_Target_Level_Rec => p_Target_Level_Rec
588 , x_Target_Level_Rec => l_Target_Level_Rec
589 , x_return_status => x_return_status
590 , x_error_Tbl => x_error_tbl
591 );
592
593 BIS_TARGET_LEVEL_PVT.Validate_Dimensions -- 2486702
594 (
595 p_target_level_rec => l_Target_Level_Rec
596 , x_return_status => l_return_status
597 , x_return_msg => l_return_msg
598 ); -- BIS_UTILITIES_PUB.put_line(p_text => ' ok 2 x_return_status = ' || x_return_status ) ;
599
600 IF ( l_return_status <> 'S' ) THEN -- 2486702
601 RAISE FND_API.G_EXC_ERROR;
602 END IF;
603
604 BIS_TARGET_LEVEL_PVT.Update_Target_Level
605 ( p_api_version => p_api_version
606 , p_commit => p_commit
607 , p_validation_level => p_validation_level
608 , p_Target_Level_Rec => l_Target_Level_Rec
609 , p_owner => p_owner
610 , p_up_loaded => p_up_loaded
611 , x_return_status => x_return_status
612 , x_error_Tbl => x_error_Tbl
613 );
614
615 IF x_return_status = FND_API.G_RET_STS_ERROR then
616
617 BIS_TARGET_LEVEL_PVT.Create_Target_Level
618 ( p_api_version => p_api_version
619 , p_commit => p_commit
620 , p_validation_level => p_validation_level
621 , p_Target_Level_Rec => l_Target_Level_Rec
622 , p_owner => p_owner
623 , x_return_status => x_return_status
624 , x_error_Tbl => x_error_Tbl
625 );
626 END IF;
627
628 EXCEPTION
629 WHEN NO_DATA_FOUND THEN
630 x_return_status := FND_API.G_RET_STS_ERROR ;
631 when FND_API.G_EXC_ERROR then
632 x_return_status := FND_API.G_RET_STS_ERROR ;
633 when FND_API.G_EXC_UNEXPECTED_ERROR then
634 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
635 when others then
636 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
637 l_error_tbl := x_error_Tbl;
638 BIS_UTILITIES_PVT.Add_Error_Message
639 ( p_error_msg_id => SQLCODE
640 , p_error_description => SQLERRM
641 , p_error_proc_name => G_PKG_NAME||'.Load_Target_Level'
642 , p_error_table => l_error_tbl
643 , x_error_table => x_error_tbl
644 );
645
646 END Load_Target_Level ;
647
648 --New Function to return target level id from shortname
649
650 FUNCTION Get_Id_From_DimLevelShortNames
651 ( p_target_level_rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
652 ) RETURN NUMBER
653 IS
654 l_target_level_id NUMBER;
655 BEGIN
656 l_target_level_id :=BIS_TARGET_LEVEL_PVT.Get_Id_From_DimLevelShortNames(p_target_level_rec);
657 return l_target_level_id;
658 END Get_Id_From_DimLevelShortNames;
659
660 -- New Procedure to return TargetLevel given the DimensionLevel ShortNames in any sequence
661 -- and the Measure Short Name
662
663 PROCEDURE Retrieve_TL_From_DimLvlShNms
664 (p_api_version IN NUMBER
665 ,p_target_level_rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
666 , x_Target_Level_Rec OUT NOCOPY BIS_Target_Level_PUB.Target_Level_Rec_Type
667 , x_return_status OUT NOCOPY VARCHAR2
668 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
669 )
670 IS
671 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
672
673 BEGIN
674 BIS_Target_Level_PVT.Retrieve_TL_From_DimLvlShNms
675 (
676 p_api_version => p_api_version
677 , p_Target_Level_Rec => p_Target_Level_Rec
678 , x_Target_Level_Rec => x_Target_Level_Rec
679 , x_return_status => x_return_status
680 , x_error_Tbl => x_error_Tbl
681 );
682
683 EXCEPTION
684 WHEN NO_DATA_FOUND THEN
685 x_return_status := FND_API.G_RET_STS_ERROR ;
686 when FND_API.G_EXC_ERROR then
687 x_return_status := FND_API.G_RET_STS_ERROR ;
688 when FND_API.G_EXC_UNEXPECTED_ERROR then
689 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
690 when others then
691 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
692 l_error_tbl := x_error_Tbl;
693 BIS_UTILITIES_PVT.Add_Error_Message
694 ( p_error_msg_id => SQLCODE
695 , p_error_description => SQLERRM
696 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Target_Level'
697 , p_error_table => l_error_tbl
698 , x_error_table => x_error_tbl
699 );
700 END Retrieve_TL_From_DimLvlShNms;
701
702 -- Given a target level short name update the
703 -- bis_target_levels, bis_target_levels_tl
704 -- for last_updated_by , created_by as 1
705 PROCEDURE updt_tl_attributes(p_tl_short_name IN VARCHAR2
706 ,p_tl_new_short_name IN VARCHAR2
707 ,x_return_status OUT NOCOPY VARCHAR2) AS
708 BEGIN
709 BIS_TARGET_LEVEL_PVT.updt_tl_attributes(p_tl_short_name => p_tl_short_name
710 ,p_tl_new_short_name => p_tl_new_short_name
711 ,x_return_status => x_return_status);
712 EXCEPTION
713 WHEN OTHERS THEN
714 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
715 END updt_tl_attributes;
716
717 --
718 --
719 END BIS_Target_Level_PUB;
720
721