[Home] [Help]
PACKAGE BODY: APPS.AMS_LISTENTRY_PUB
Source
1 Package Body AMS_LISTENTRY_PUB AS
2 /* $Header: amsplseb.pls 120.1 2006/01/05 05:25:59 bmuthukr noship $ */
3
4
5 g_pkg_name CONSTANT VARCHAR2(30):='AMS_ListEntry_PUB';
6
7 ---------------------------------------------------------------------
8 -- PROCEDURE
9 -- create_listentry
10 --
11 -- PURPOSE
12 -- Create a new list entry.
13 --
14 -- PARAMETERS
15 -- p_entry_rec: the new record to be inserted
16 -- x_entry_id: return the list_entry_id of the new list entry
17 --
18 -- NOTES
19 -- 1. object_version_number will be set to 1.
20 -- 2. If list_entry_id is passed in, the uniqueness will be checked.
21 -- Raise exception in case of duplicates.
22 -- 3. If list_entry_id is not passed in, generate a unique one from
23 -- the sequence.
24 -- 4. If a flag column is passed in, check if it is 'Y' or 'N'.
25 -- Raise exception for invalid flag.
26 -- 5. If a flag column is not passed in, default it to 'Y' or 'N'.
27 -- 6. Please don't pass in any FND_API.g_mess_char/num/date.
28 ---------------------------------------------------------------------
29 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
30 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
31 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
32
33 PROCEDURE create_listentry(
34 p_api_version IN NUMBER,
35 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
36 p_commit IN VARCHAR2 := FND_API.g_false,
37 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
38
39 x_return_status OUT NOCOPY VARCHAR2,
40 x_msg_count OUT NOCOPY NUMBER,
41 x_msg_data OUT NOCOPY VARCHAR2,
42
43 p_entry_rec IN AMS_LISTENTRY_PVT.entry_rec_type,
44 x_entry_id OUT NOCOPY NUMBER
45 ) IS
46
47 l_api_name CONSTANT VARCHAR2(30) := 'Create_ListEntry';
48 l_api_version CONSTANT NUMBER := 1.0;
49
50 -- Status Local Variables
51 l_return_status VARCHAR2(1); -- Return value from procedures
52 l_listentry_id NUMBER;
53 l_listentry_rec AMS_LISTENTRY_PVT.entry_rec_type := p_entry_rec;
54 Begin
55
56 SAVEPOINT create_listentry_pub;
57
58 -- initialize the message list;
59 -- won't do it again when calling private API
60 IF FND_API.to_boolean(p_init_msg_list) THEN
61 FND_MSG_PUB.initialize;
62 END IF;
63
64 AMS_LISTENTRY_PVT.create_listentry(
65 p_api_version => p_api_version,
66 p_init_msg_list => FND_API.g_false,
67 p_commit => FND_API.g_false,
68 --p_validation_level => FND_API.g_valid_level_full,-- bug 4761988
69 p_validation_level => p_validation_level,
70 x_return_status => l_return_status,
71 x_msg_count => x_msg_count,
72 x_msg_data => x_msg_data,
73
74 p_entry_rec => l_listentry_rec,
75 x_entry_id => l_listentry_id);
76
77
78 IF l_return_status = FND_API.g_ret_sts_error THEN
79 RAISE FND_API.g_exc_error;
80 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
81 RAISE FND_API.g_exc_unexpected_error;
82 END IF;
83
84 x_entry_id := l_listentry_id;
85
86 IF FND_API.to_boolean(p_commit) THEN
87 COMMIT;
88 END IF;
89 x_return_status := FND_API.g_ret_sts_success;
90 FND_MSG_PUB.count_and_get(
91 p_encoded => FND_API.g_false,
92 p_count => x_msg_count,
93 p_data => x_msg_data
94 );
95
96 EXCEPTION
97
98 WHEN FND_API.g_exc_error THEN
99 ROLLBACK TO create_listentry_pub;
100 x_return_status := FND_API.g_ret_sts_error;
101 FND_MSG_PUB.count_and_get(
102 p_encoded => FND_API.g_false,
103 p_count => x_msg_count,
104 p_data => x_msg_data
105 );
106
107 WHEN FND_API.g_exc_unexpected_error THEN
108 ROLLBACK TO create_listentry_pub;
109 x_return_status := FND_API.g_ret_sts_unexp_error ;
110 FND_MSG_PUB.count_and_get(
111 p_encoded => FND_API.g_false,
112 p_count => x_msg_count,
113 p_data => x_msg_data
114 );
115
116
117 WHEN OTHERS THEN
118 ROLLBACK TO create_listentry_pub;
119 x_return_status := FND_API.g_ret_sts_unexp_error ;
120
121 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
122 THEN
123 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
124 END IF;
125
126 FND_MSG_PUB.count_and_get(
127 p_encoded => FND_API.g_false,
128 p_count => x_msg_count,
129 p_data => x_msg_data
130 );
131 End create_listentry;
132
133 ---------------------------------------------------------------------
134 -- PROCEDURE
135 -- update_listentry
136 --
137 -- PURPOSE
138 -- Update a listentry.
139 --
140 -- PARAMETERS
141 -- p_entry_rec: the record with new items
142 --
143 -- NOTES
144 -- 1. Raise exception if the object_version_number doesn't match.
145 -- 2. If an attribute is passed in as FND_API.g_miss_char/num/date,
146 -- that column won't be updated.
147 ----------------------------------------------------------------------
148 PROCEDURE update_listentry(
149 p_api_version IN NUMBER,
150 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
151 p_commit IN VARCHAR2 := FND_API.g_false,
152 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
153
154 x_return_status OUT NOCOPY VARCHAR2,
155 x_msg_count OUT NOCOPY NUMBER,
156 x_msg_data OUT NOCOPY VARCHAR2,
157
158 p_entry_rec IN AMS_LISTENTRY_PVT.entry_rec_type
159 ) IS
160
161 l_api_name CONSTANT VARCHAR2(30) := 'Update_ListEntry';
162 l_api_version CONSTANT NUMBER := 1.0;
163
164 -- Status Local Variables
165 l_return_status VARCHAR2(1); -- Return value from procedures
166 l_listentry_rec AMS_LISTENTRY_PVT.entry_rec_type := p_entry_rec;
167
168
169 BEGIN
170
171 SAVEPOINT update_listentry_pub;
172
173 -- initialize the message list;
174 -- won't do it again when calling private API
175 IF FND_API.to_boolean(p_init_msg_list) THEN
176 FND_MSG_PUB.initialize;
177 END IF;
178
179 AMS_LISTENTRY_PVT.update_listentry
180 ( p_api_version => p_api_version,
181 p_init_msg_list => FND_API.g_false,
182 p_commit => FND_API.g_false,
183 p_validation_level => FND_API.g_valid_level_full,
184 x_return_status => l_return_status,
185 x_msg_count => x_msg_count,
186 x_msg_data => x_msg_data,
187 p_entry_rec => l_listentry_rec);
188
189
190 IF l_return_status = FND_API.g_ret_sts_error THEN
191 RAISE FND_API.g_exc_error;
192 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
193 RAISE FND_API.g_exc_unexpected_error;
194 END IF;
195
196 IF FND_API.to_boolean(p_commit) THEN
197 COMMIT;
198 END IF;
199 x_return_status := FND_API.g_ret_sts_success;
200 FND_MSG_PUB.count_and_get(
201 p_encoded => FND_API.g_false,
202 p_count => x_msg_count,
203 p_data => x_msg_data
204 );
205
206 EXCEPTION
207
208 WHEN FND_API.g_exc_error THEN
209 ROLLBACK TO update_listentry_pub;
210 x_return_status := FND_API.g_ret_sts_error;
211 FND_MSG_PUB.count_and_get(
212 p_encoded => FND_API.g_false,
213 p_count => x_msg_count,
214 p_data => x_msg_data
215 );
216
217 WHEN FND_API.g_exc_unexpected_error THEN
218 ROLLBACK TO update_listentry_pub;
219 x_return_status := FND_API.g_ret_sts_unexp_error ;
220 FND_MSG_PUB.count_and_get(
221 p_encoded => FND_API.g_false,
222 p_count => x_msg_count,
223 p_data => x_msg_data
224 );
225
226
227 WHEN OTHERS THEN
228 ROLLBACK TO update_listentry_pub;
229 x_return_status := FND_API.g_ret_sts_unexp_error ;
230
231 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
232 THEN
233 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
234 END IF;
235
236 FND_MSG_PUB.count_and_get(
237 p_encoded => FND_API.g_false,
238 p_count => x_msg_count,
239 p_data => x_msg_data
240 );
241
242
243 END update_listentry;
244
245 --------------------------------------------------------------------
246 -- PROCEDURE
247 -- delete_listentry
248 --
249 -- PURPOSE
250 -- Delete a listentry.
251 --
252 -- PARAMETERS
253 -- p_entry_id: the listentry_id
254 -- p_object_version: the object_version_number
255 --
256 -- NOTES
257 -- 1. Raise exception if the object_version_number doesn't match.
258 --------------------------------------------------------------------
259 PROCEDURE delete_listentry(
260 p_api_version IN NUMBER,
261 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
262 p_commit IN VARCHAR2 := FND_API.g_false,
263 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
264
265 x_return_status OUT NOCOPY VARCHAR2,
266 x_msg_count OUT NOCOPY NUMBER,
267 x_msg_data OUT NOCOPY VARCHAR2,
268
269 p_entry_id IN NUMBER,
270 p_object_version_number IN NUMBER
271 ) IS
272
273 l_api_name CONSTANT VARCHAR2(30) := 'Delete_ListEntry';
274 l_api_version CONSTANT NUMBER := 1.0;
275
276 -- Status Local Variables
277 l_return_status VARCHAR2(1); -- Return value from procedures
278 l_entry_id NUMBER := P_ENTRY_ID;
279 l_object_version_number NUMBER := p_object_version_number;
280
281 BEGIN
282
283 SAVEPOINT delete_listentry_pub;
284
285 -- initialize the message list;
286 -- won't do it again when calling private API
287 IF FND_API.to_boolean(p_init_msg_list) THEN
288 FND_MSG_PUB.initialize;
289 END IF;
290
291 AMS_LISTENTRY_PVT.delete_listentry(
292 p_api_version => p_api_version,
293 p_init_msg_list => FND_API.g_false,
294 p_commit => FND_API.g_false,
295 p_validation_level => FND_API.g_valid_level_full,
296
297 x_return_status => l_return_status,
298 x_msg_count => x_msg_count,
299 x_msg_data => x_msg_data,
300
301 p_entry_id => l_entry_id,
302 p_object_version_number => l_object_version_number);
303
304
305 IF l_return_status = FND_API.g_ret_sts_error THEN
306 RAISE FND_API.g_exc_error;
307 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
308 RAISE FND_API.g_exc_unexpected_error;
309 END IF;
310
311 IF FND_API.to_boolean(p_commit) THEN
312 COMMIT;
313 END IF;
314 x_return_status := FND_API.g_ret_sts_success;
315 FND_MSG_PUB.count_and_get(
316 p_encoded => FND_API.g_false,
317 p_count => x_msg_count,
318 p_data => x_msg_data
319 );
320
321 EXCEPTION
322
323 WHEN FND_API.g_exc_error THEN
324 ROLLBACK TO delete_listentry_pub;
325 x_return_status := FND_API.g_ret_sts_error;
326 FND_MSG_PUB.count_and_get(
327 p_encoded => FND_API.g_false,
328 p_count => x_msg_count,
329 p_data => x_msg_data
330 );
331
332 WHEN FND_API.g_exc_unexpected_error THEN
333 ROLLBACK TO delete_listentry_pub;
334 x_return_status := FND_API.g_ret_sts_unexp_error ;
335 FND_MSG_PUB.count_and_get(
336 p_encoded => FND_API.g_false,
337 p_count => x_msg_count,
338 p_data => x_msg_data
339 );
340
341
342 WHEN OTHERS THEN
343 ROLLBACK TO delete_listentry_pub;
344 x_return_status := FND_API.g_ret_sts_unexp_error ;
345
346 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
347 THEN
348 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
349 END IF;
350
351 FND_MSG_PUB.count_and_get(
352 p_encoded => FND_API.g_false,
353 p_count => x_msg_count,
354 p_data => x_msg_data
355 );
356 END delete_listentry;
357
358
359
360 -------------------------------------------------------------------
361 -- PROCEDURE
362 -- lock_listentry
363 --
364 -- PURPOSE
365 -- Lock a List Entry.
366 --
367 -- PARAMETERS
368 -- p_entry_id: the list_entry_id
369 -- p_object_version: the object_version_number
370 --
371 -- NOTES
372 -- 1. Raise exception if the object_version_number doesn't match.
373 --------------------------------------------------------------------
374 PROCEDURE lock_listentry(
375 p_api_version IN NUMBER,
376 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
377 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
378
379 x_return_status OUT NOCOPY VARCHAR2,
380 x_msg_count OUT NOCOPY NUMBER,
381 x_msg_data OUT NOCOPY VARCHAR2,
382
383 p_entry_id IN NUMBER,
384 p_object_version IN NUMBER
385 ) IS
386
387 l_api_name CONSTANT VARCHAR2(30) := 'Lock_ListEntry';
388 l_api_version CONSTANT NUMBER := 1.0;
389 l_return_status VARCHAR2(1);
390 l_entry_id NUMBER := P_ENTRY_ID;
391 l_object_version NUMBER := P_OBJECT_VERSION;
392
393 BEGIN
394
395 SAVEPOINT lock_listentry_pub;
396
397 -- initialize the message list;
398 -- won't do it again when calling private API
399 IF FND_API.to_boolean(p_init_msg_list) THEN
400 FND_MSG_PUB.initialize;
401 END IF;
402
403 lock_listentry
404 ( p_api_version => p_api_version ,
405 p_init_msg_list => FND_API.g_false,
406 p_validation_level => FND_API.g_valid_level_full,
410 x_msg_data => x_msg_data,
407
408 x_return_status => l_return_status,
409 x_msg_count => x_msg_count,
411
412 p_entry_id => l_entry_id,
413 p_object_version => l_object_version );
414
415
416 IF l_return_status = FND_API.g_ret_sts_error THEN
417 RAISE FND_API.g_exc_error;
418 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
419 RAISE FND_API.g_exc_unexpected_error;
420 END IF;
421
422
423 x_return_status := FND_API.g_ret_sts_success;
424 FND_MSG_PUB.count_and_get(
425 p_encoded => FND_API.g_false,
426 p_count => x_msg_count,
427 p_data => x_msg_data
428 );
429
430 EXCEPTION
431
432 WHEN FND_API.g_exc_error THEN
433 ROLLBACK TO lock_listentry_pub;
434 x_return_status := FND_API.g_ret_sts_error;
435 FND_MSG_PUB.count_and_get(
436 p_encoded => FND_API.g_false,
437 p_count => x_msg_count,
438 p_data => x_msg_data
439 );
440
441 WHEN FND_API.g_exc_unexpected_error THEN
442 ROLLBACK TO lock_listentry_pub;
443 x_return_status := FND_API.g_ret_sts_unexp_error ;
444 FND_MSG_PUB.count_and_get(
445 p_encoded => FND_API.g_false,
446 p_count => x_msg_count,
447 p_data => x_msg_data
448 );
449
450
451 WHEN OTHERS THEN
452 ROLLBACK TO lock_listentry_pub;
453 x_return_status := FND_API.g_ret_sts_unexp_error ;
454
455 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
456 THEN
457 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
458 END IF;
459
460 FND_MSG_PUB.count_and_get(
461 p_encoded => FND_API.g_false,
462 p_count => x_msg_count,
463 p_data => x_msg_data
464 );
465 END lock_listentry;
466
467 ---------------------------------------------------------------------
468 -- PROCEDURE
469 -- validate_listentry
470 --
471 -- PURPOSE
472 -- Validate a listentry record.
473 --
474 -- PARAMETERS
475 -- p_entry_rec: the listentry record to be validated
476 --
477 -- NOTES
478 -- 1. p_entry_rec should be the complete list entry record. There
479 -- should not be any FND_API.g_miss_char/num/date in it.
480 ----------------------------------------------------------------------
481 PROCEDURE validate_listentry(
482 p_api_version IN NUMBER,
483 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
484 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
485
486 x_return_status OUT NOCOPY VARCHAR2,
487 x_msg_count OUT NOCOPY NUMBER,
488 x_msg_data OUT NOCOPY VARCHAR2,
489
490 p_entry_rec IN AMS_LISTENTRY_PVT.entry_rec_type
491 ) IS
492
493 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Entry';
494 l_api_version CONSTANT NUMBER := 1.0;
495
496 -- Status Local Variables
497 l_return_status VARCHAR2(1); -- Return value from procedures
498 l_entry_rec AMS_LISTENTRY_PVT.entry_rec_type := p_entry_rec;
499
500 BEGIN
501
502 SAVEPOINT validate_listentry_pub;
503
504 -- initialize the message list;
505 -- won't do it again when calling private API
506 IF FND_API.to_boolean(p_init_msg_list) THEN
507 FND_MSG_PUB.initialize;
508 END IF;
509
510
511 AMS_LISTENTRY_PVT.validate_listentry
512 ( p_api_version => p_api_version,
513 p_init_msg_list => FND_API.g_false,
514 p_validation_level => FND_API.g_valid_level_full,
515
516 x_return_status => l_return_status,
517 x_msg_count => x_msg_count,
518 x_msg_data => x_msg_data,
519
520 p_entry_rec => l_entry_rec);
521
522 IF l_return_status = FND_API.g_ret_sts_error THEN
523 RAISE FND_API.g_exc_error;
524 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
525 RAISE FND_API.g_exc_unexpected_error;
526 END IF;
527
528
529 x_return_status := FND_API.g_ret_sts_success;
530 FND_MSG_PUB.count_and_get(
531 p_encoded => FND_API.g_false,
532 p_count => x_msg_count,
533 p_data => x_msg_data
534 );
535
536 EXCEPTION
537
538 WHEN FND_API.g_exc_error THEN
539 ROLLBACK TO validate_listentry_pub;
540 x_return_status := FND_API.g_ret_sts_error;
541 FND_MSG_PUB.count_and_get(
542 p_encoded => FND_API.g_false,
543 p_count => x_msg_count,
544 p_data => x_msg_data
545 );
546
547 WHEN FND_API.g_exc_unexpected_error THEN
548 ROLLBACK TO validate_listentry_pub;
549 x_return_status := FND_API.g_ret_sts_unexp_error ;
550
551 FND_MSG_PUB.count_and_get(
552 p_encoded => FND_API.g_false,
553 p_count => x_msg_count,
554 p_data => x_msg_data
555 );
556
557
558 WHEN OTHERS THEN
562 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
559 ROLLBACK TO validate_listentry_pub;
560 x_return_status := FND_API.g_ret_sts_unexp_error ;
561
563 THEN
564 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
565 END IF;
566
567 FND_MSG_PUB.count_and_get(
568 p_encoded => FND_API.g_false,
569 p_count => x_msg_count,
573
570 p_data => x_msg_data
571 );
572 END validate_listentry;
574
575 ---------------------------------------------------------------------
576 -- PROCEDURE
577 -- init_entry_rec
578 --
579 -- PURPOSE
580 -- Initialize all attributes to be FND_API.g_miss_char/num/date.
581 ---------------------------------------------------------------------
582 PROCEDURE init_entry_rec(x_entry_rec OUT NOCOPY AMS_LISTENTRY_PVT.entry_rec_type)
583 IS
584 l_api_name CONSTANT VARCHAR2(30) := 'Init_Entry_Rec';
585 l_api_version CONSTANT NUMBER := 1.0;
586 BEGIN
587
588
589 AMS_LISTENTRY_PVT.init_entry_rec(x_entry_rec => x_entry_rec);
590
591 END init_entry_rec;
592
593 END;