[Home] [Help]
PACKAGE BODY: APPS.AMS_DELIVKITITEM_PVT
Source
1 PACKAGE BODY AMS_DelivKitItem_PVT AS
2 /* $Header: amsvdkib.pls 120.0 2005/06/01 03:28:05 appldev noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30):='AMS_DelivKitItem_PVT';
5
6 ---------------------------------------------------------------------
7 -- PROCEDURE
8 -- create_deliv_kit_item
9 --
10 -- HISTORY
11 -- 10/09/99 khung Create.
12 ---------------------------------------------------------------------
13
14 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
15 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
16 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
17
18 PROCEDURE create_deliv_kit_item
19 (
20 p_api_version IN NUMBER,
21 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
22 p_commit IN VARCHAR2 := FND_API.g_false,
23 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
24
25 x_return_status OUT NOCOPY VARCHAR2,
26 x_msg_count OUT NOCOPY NUMBER,
27 x_msg_data OUT NOCOPY VARCHAR2,
28
29 p_deliv_kit_item_rec IN deliv_kit_item_rec_type,
30 x_deliv_kit_item_id OUT NOCOPY NUMBER
31 )
32 IS
33
34 l_api_version CONSTANT NUMBER := 1.0;
35 l_api_name CONSTANT VARCHAR2(30) := 'create_deliv_kit_item';
36 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
37
38 l_return_status VARCHAR2(1);
39 l_deliv_kit_item_rec deliv_kit_item_rec_type := p_deliv_kit_item_rec;
40 l_deliv_kit_item_count NUMBER;
41 l_kit_flag VARCHAR2(1);
42 l_kit_part_count NUMBER;
43
44 CURSOR c_deliv_kit_item_seq IS
45 SELECT ams_deliv_kit_items_s.NEXTVAL
46 FROM DUAL;
47
48 CURSOR c_deliv_kit_item_count(deliv_kit_item_id IN NUMBER) IS
49 SELECT COUNT(*)
50 FROM ams_deliv_kit_items
51 WHERE deliverable_kit_item_id = deliv_kit_item_id;
52
53 -- AMS_KIT_PART_CANNOT_BE_KIT
54 CURSOR c_deliv_kit_part_flag(deliv_kit_item_id IN NUMBER) IS
55 SELECT kit_flag
56 FROM ams_deliverables_all_b
57 WHERE deliverable_id = deliv_kit_item_id;
58
59 -- AMS_KIT_CANNOT_BE_KIT_PART
60 /*
61 CURSOR c_deliv_kit_part_count(deliv_kit_id IN NUMBER) IS
62 SELECT COUNT(*)
63 FROM ams_deliv_kit_items
64 WHERE deliverable_kit_part_id = deliv_kit_id;*/
65
66 BEGIN
67
68 --------------------- initialize -----------------------
69 SAVEPOINT create_deliv_kit_item;
70
71 IF (AMS_DEBUG_HIGH_ON) THEN
72 AMS_Utility_PVT.debug_message(l_full_name||': start');
73 END IF;
74
75 IF FND_API.to_boolean(p_init_msg_list) THEN
76 FND_MSG_PUB.initialize;
77 END IF;
78
79 IF NOT FND_API.compatible_api_call
80 (
81 l_api_version,
82 p_api_version,
83 l_api_name,
84 g_pkg_name
85 ) THEN
86 RAISE FND_API.g_exc_unexpected_error;
87 END IF;
88
89 x_return_status := FND_API.g_ret_sts_success;
90
91 ----------------------- validate -----------------------
92 IF (AMS_DEBUG_HIGH_ON) THEN
93
94 AMS_Utility_PVT.debug_message(l_full_name ||': validate');
95 END IF;
96
97 validate_deliv_kit_item
98 (
99 p_api_version => l_api_version,
100 p_init_msg_list => p_init_msg_list,
101 p_validation_level => p_validation_level,
102 x_return_status => l_return_status,
103 x_msg_count => x_msg_count,
104 x_msg_data => x_msg_data,
105 p_deliv_kit_item_rec => l_deliv_kit_item_rec
106 );
107
108 IF l_return_status = FND_API.g_ret_sts_error THEN
109 RAISE FND_API.g_exc_error;
110 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
111 RAISE FND_API.g_exc_unexpected_error;
112 END IF;
113
114 -------------------------- insert --------------------------
115 IF (AMS_DEBUG_HIGH_ON) THEN
116 AMS_Utility_PVT.debug_message(l_full_name ||': insert');
117 END IF;
118
119 -- check the kit_flag for kit_part_item
120 -- return false if this kit_part_item is also a kit
121 OPEN c_deliv_kit_part_flag(l_deliv_kit_item_rec.deliverable_kit_part_id);
122 FETCH c_deliv_kit_part_flag INTO l_kit_flag;
123 CLOSE c_deliv_kit_part_flag;
124
125 IF (l_kit_flag = 'Y')
126 THEN
127 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
128 THEN
129 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_KITPART_NOT_KIT');
130 FND_MSG_PUB.add;
131 END IF;
132 RAISE FND_API.g_exc_error;
133 END IF;
134
135 /*
136 OPEN c_deliv_kit_part_count(l_deliv_kit_item_rec.deliverable_kit_id);
137 FETCH c_deliv_kit_part_count INTO l_kit_part_count;
138 CLOSE c_deliv_kit_part_count;
139
140 IF (l_kit_part_count <> 0)
141 THEN
142 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
143 THEN
144 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_KIT_NOT_KITPART');
145 FND_MSG_PUB.add;
146 END IF;
147 RAISE FND_API.g_exc_error;
148 END IF;
149 */
150
151 IF l_deliv_kit_item_rec.deliverable_kit_item_id IS NULL THEN
152 LOOP
153 OPEN c_deliv_kit_item_seq;
154 FETCH c_deliv_kit_item_seq INTO l_deliv_kit_item_rec.deliverable_kit_item_id;
155 CLOSE c_deliv_kit_item_seq;
156
157 OPEN c_deliv_kit_item_count(l_deliv_kit_item_rec.deliverable_kit_item_id);
158 FETCH c_deliv_kit_item_count INTO l_deliv_kit_item_count;
159 CLOSE c_deliv_kit_item_count;
160
161 EXIT WHEN l_deliv_kit_item_count = 0;
162 END LOOP;
163 END IF;
164
165 INSERT INTO ams_deliv_kit_items
166 (
167 deliverable_kit_item_id,
168 last_update_date,
169 last_updated_by,
170 creation_date,
171 created_by,
172 last_update_login,
173 object_version_number,
174 deliverable_kit_id,
175 deliverable_kit_part_id,
176 kit_part_included_from_kit_id,
177 quantity,
178 attribute_category,
179 attribute1,
180 attribute2,
181 attribute3,
182 attribute4,
183 attribute5,
184 attribute6,
185 attribute7,
186 attribute8,
187 attribute9,
188 attribute10,
189 attribute11,
190 attribute12,
191 attribute13,
192 attribute14,
193 attribute15
194 ) VALUES (
195 l_deliv_kit_item_rec.deliverable_kit_item_id,
196 SYSDATE,
197 FND_GLOBAL.user_id,
198 SYSDATE,
199 FND_GLOBAL.user_id,
200 FND_GLOBAL.conc_login_id,
201 1, -- object_version_number
202 l_deliv_kit_item_rec.deliverable_kit_id,
203 l_deliv_kit_item_rec.deliverable_kit_part_id,
204 l_deliv_kit_item_rec.kit_part_included_from_kit_id,
205 l_deliv_kit_item_rec.quantity,
206 l_deliv_kit_item_rec.attribute_category,
207 l_deliv_kit_item_rec.attribute1,
208 l_deliv_kit_item_rec.attribute2,
209 l_deliv_kit_item_rec.attribute3,
210 l_deliv_kit_item_rec.attribute4,
211 l_deliv_kit_item_rec.attribute5,
212 l_deliv_kit_item_rec.attribute6,
213 l_deliv_kit_item_rec.attribute7,
214 l_deliv_kit_item_rec.attribute8,
215 l_deliv_kit_item_rec.attribute9,
216 l_deliv_kit_item_rec.attribute10,
217 l_deliv_kit_item_rec.attribute11,
218 l_deliv_kit_item_rec.attribute12,
219 l_deliv_kit_item_rec.attribute13,
220 l_deliv_kit_item_rec.attribute14,
221 l_deliv_kit_item_rec.attribute15
222 );
223
224
225 /*
226 -- musman 02/13/2002
227 -- Commenting the call to update kit flag
228 -- because the user would be allowed to change
229 -- the kit flag from the screen.
230
231 -- modified by khung 12/13/1999
232 -- change ams_deliverables_all_b.kit_flag to 'Y'
233 UPDATE ams_deliverables_all_b
234 SET kit_flag = 'Y'
235 WHERE deliverable_id = l_deliv_kit_item_rec.deliverable_kit_id;
236
237
238 */
239 /* commented by musman
240 -- added by khung 03/22/2000
241 -- indicate kit has been defined for this deliverable
242 -- AMS_ObjectAttribute_PVT.modify_object_attribute(
243 -- p_api_version => l_api_version,
244 -- p_init_msg_list => FND_API.g_false,
245 -- p_commit => FND_API.g_false,
246 -- p_validation_level => FND_API.g_valid_level_full,
247 --
248 -- x_return_status => l_return_status,
249 -- x_msg_count => x_msg_count,
250 -- x_msg_data => x_msg_data,
251 --
252 -- p_object_type => 'DELV',
253 -- p_object_id => l_deliv_kit_item_rec.deliverable_kit_id,
254 -- p_attr => 'INVK',
255 -- p_attr_defined_flag => 'Y'
256 -- );
257 --IF l_return_status = FND_API.g_ret_sts_error THEN
258 -- RAISE FND_API.g_exc_error;
259 --ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
260 -- RAISE FND_API.g_exc_unexpected_error;
261 --END IF;
262 */
263 ------------------------- finish -------------------------------
264 x_deliv_kit_item_id := l_deliv_kit_item_rec.deliverable_kit_item_id;
265
266 IF FND_API.to_boolean(p_commit) THEN
267 COMMIT;
268 END IF;
269
270 FND_MSG_PUB.count_and_get(
271 p_encoded => FND_API.g_false,
272 p_count => x_msg_count,
273 p_data => x_msg_data
274 );
275
276 IF (AMS_DEBUG_HIGH_ON) THEN
277
278
279
280 AMS_Utility_PVT.debug_message(l_full_name ||': end');
281
282 END IF;
283
284 EXCEPTION
285
286 WHEN FND_API.g_exc_error THEN
287 ROLLBACK TO create_deliv_kit_item;
288 x_return_status := FND_API.g_ret_sts_error;
289 FND_MSG_PUB.count_and_get(
290 p_encoded => FND_API.g_false,
291 p_count => x_msg_count,
292 p_data => x_msg_data
293 );
294
295 WHEN FND_API.g_exc_unexpected_error THEN
296 ROLLBACK TO create_deliv_kit_item;
297 x_return_status := FND_API.g_ret_sts_unexp_error ;
298 FND_MSG_PUB.count_and_get(
299 p_encoded => FND_API.g_false,
300 p_count => x_msg_count,
301 p_data => x_msg_data
302 );
303
304
305 WHEN OTHERS THEN
306 ROLLBACK TO create_deliv_kit_item;
307 x_return_status := FND_API.g_ret_sts_unexp_error ;
308
309 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
310 THEN
311 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
312 END IF;
313
314 FND_MSG_PUB.count_and_get(
315 p_encoded => FND_API.g_false,
316 p_count => x_msg_count,
317 p_data => x_msg_data
318 );
319
320 END create_deliv_kit_item;
321
322
323 --------------------------------------------------------------------
324 -- PROCEDURE
325 -- delete_deliv_kit_item
326 --
327 -- HISTORY
328 -- 10/09/99 khung Create.
329 --------------------------------------------------------------------
330
331 PROCEDURE delete_deliv_kit_item
332 (
333 p_api_version IN NUMBER,
334 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
335 p_commit IN VARCHAR2 := FND_API.g_false,
336
337 x_return_status OUT NOCOPY VARCHAR2,
338 x_msg_count OUT NOCOPY NUMBER,
339 x_msg_data OUT NOCOPY VARCHAR2,
340
341 p_deliv_kit_item_id IN NUMBER,
342 p_object_version IN NUMBER
343 )
344
345 IS
346
347 l_api_version CONSTANT NUMBER := 1.0;
348 l_api_name CONSTANT VARCHAR2(30) := 'delete_deliv_kit_item';
349 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
350
351 l_kit_count NUMBER;
352 l_kit_id NUMBER;
353
354 CURSOR c_kit_count(kit_item_id IN NUMBER) IS
355 SELECT COUNT(*)
356 FROM ams_deliv_kit_items
357 WHERE deliverable_kit_id =
358 ( SELECT deliverable_kit_id
359 FROM ams_deliv_kit_items
360 WHERE deliverable_kit_item_id = kit_item_id);
361
362 CURSOR c_kit_id(kit_item_id IN NUMBER) IS
363 SELECT deliverable_kit_id
364 FROM ams_deliv_kit_items
365 WHERE deliverable_kit_item_id = kit_item_id;
366
367 BEGIN
368
369 --------------------- initialize -----------------------
370 SAVEPOINT delete_deliv_kit_item;
371
372 IF (AMS_DEBUG_HIGH_ON) THEN
373
374
375
376 AMS_Utility_PVT.debug_message(l_full_name||': start');
377
378 END IF;
379
380 IF FND_API.to_boolean(p_init_msg_list) THEN
381 FND_MSG_PUB.initialize;
382 END IF;
383
384 IF NOT FND_API.compatible_api_call(
385 l_api_version,
386 p_api_version,
387 l_api_name,
388 g_pkg_name
389 ) THEN
390 RAISE FND_API.g_exc_unexpected_error;
391 END IF;
392
393 x_return_status := FND_API.G_RET_STS_SUCCESS;
394
395 ------------------------ delete ------------------------
396 IF (AMS_DEBUG_HIGH_ON) THEN
397
398 AMS_Utility_PVT.debug_message(l_full_name ||': delete');
399 END IF;
400
401 OPEN c_kit_id(p_deliv_kit_item_id);
402 FETCH c_kit_id INTO l_kit_id;
403 CLOSE c_kit_id;
404
405 OPEN c_kit_count(p_deliv_kit_item_id);
406 FETCH c_kit_count INTO l_kit_count;
407 CLOSE c_kit_count;
408
409 DELETE FROM ams_deliv_kit_items
410 WHERE deliverable_kit_item_id = p_deliv_kit_item_id
411 AND object_version_number = p_object_version;
412
413 IF (SQL%NOTFOUND) THEN
414 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
415 THEN
416 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
417 FND_MSG_PUB.add;
418 END IF;
419 RAISE FND_API.g_exc_error;
420 END IF;
421
422 IF (AMS_DEBUG_HIGH_ON) THEN
423
424
425
426 AMS_Utility_PVT.debug_message(l_full_name ||': after delete');
427
428 END IF;
429
430 -- check if there are kit_part associated with this kit after the delete
431 -- if not, change the deliverable's kit_flag to 'N'
432
433 l_kit_count := l_kit_count - 1;
434 /*
435 -- musman 02/13/2002
436 -- Commenting the call to update kit flag
437 -- because the user would be allowed to change
438 -- the kit flag from the screen.
439
440 -- IF (l_kit_count < 1)
441 -- THEN
442 -- UPDATE ams_deliverables_all_b
443 -- SET kit_flag = 'N'
444 -- WHERE deliverable_id = l_kit_id;
445
446 -- commented by musman
447
448 -- remove the 'check' icon when there is no kit item associated with
449 -- this deliverable
450 -- AMS_ObjectAttribute_PVT.modify_object_attribute(
451 -- p_api_version => l_api_version,
452 -- p_init_msg_list => FND_API.g_false,
453 -- p_commit => FND_API.g_false,
454 -- x_return_status => x_return_status,
455 -- x_msg_count => x_msg_count,
459 -- p_attr => 'INVK',
456 -- x_msg_data => x_msg_data,
457 -- p_object_type => 'DELV',
458 -- p_object_id => l_kit_id,
460 -- p_attr_defined_flag => 'N'
461 -- );
462 -- IF x_return_status = FND_API.g_ret_sts_error THEN
463 -- RAISE FND_API.g_exc_error;
464 -- ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
465 -- RAISE FND_API.g_exc_unexpected_error;
466 -- END IF;
467
468 -- END IF;
469
470 */
471
472 -------------------- finish --------------------------
473 IF FND_API.to_boolean(p_commit) THEN
474 COMMIT;
475 END IF;
476
477 FND_MSG_PUB.count_and_get(
478 p_encoded => FND_API.g_false,
479 p_count => x_msg_count,
480 p_data => x_msg_data
481 );
482
483 IF (AMS_DEBUG_HIGH_ON) THEN
484
485
486
487 AMS_Utility_PVT.debug_message(l_full_name ||': end');
488
489 END IF;
490
491 EXCEPTION
492
493 WHEN FND_API.g_exc_error THEN
494 ROLLBACK TO delete_deliv_kit_item;
495 x_return_status := FND_API.g_ret_sts_error;
496 FND_MSG_PUB.count_and_get(
497 p_encoded => FND_API.g_false,
498 p_count => x_msg_count,
499 p_data => x_msg_data
500 );
501
502 WHEN FND_API.g_exc_unexpected_error THEN
503 ROLLBACK TO delete_deliv_kit_item;
504 x_return_status := FND_API.g_ret_sts_unexp_error ;
505 FND_MSG_PUB.count_and_get(
506 p_encoded => FND_API.g_false,
507 p_count => x_msg_count,
508 p_data => x_msg_data
509 );
510
511 WHEN OTHERS THEN
512 ROLLBACK TO delete_deliv_kit_item;
513 x_return_status := FND_API.g_ret_sts_unexp_error ;
514
515 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
516 THEN
517 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
518 END IF;
519
520 FND_MSG_PUB.count_and_get(
521 p_encoded => FND_API.g_false,
522 p_count => x_msg_count,
523 p_data => x_msg_data
524 );
525
526 END delete_deliv_kit_item;
527
528 -------------------------------------------------------------------
529 -- PROCEDURE
530 -- lock_deliv_kit_item
531 --
532 -- PURPOSE
533 -- Lock a deliverable kit item.
534 --------------------------------------------------------------------
535
536 PROCEDURE lock_deliv_kit_item
537 (
538 p_api_version IN NUMBER,
539 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
540
541 x_return_status OUT NOCOPY VARCHAR2,
542 x_msg_count OUT NOCOPY NUMBER,
543 x_msg_data OUT NOCOPY VARCHAR2,
544
545 p_deliv_kit_item_id IN NUMBER,
546 p_object_version IN NUMBER
547 )
548
549 IS
550
551 l_api_version CONSTANT NUMBER := 1.0;
552 l_api_name CONSTANT VARCHAR2(30) := 'lock_deliv_kit_item';
553 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
554
555 l_deliv_kit_item_id NUMBER;
556
557 CURSOR c_deliv_kit_item IS
558 SELECT deliverable_kit_item_id
559 FROM ams_deliv_kit_items
560 WHERE deliverable_kit_item_id = p_deliv_kit_item_id
561 AND object_version_number = p_object_version
562 FOR UPDATE OF deliverable_kit_item_id NOWAIT;
563
564 BEGIN
565
566 -------------------- initialize ------------------------
567 IF (AMS_DEBUG_HIGH_ON) THEN
568
569 AMS_Utility_PVT.debug_message(l_full_name||': start');
570 END IF;
571
572 IF FND_API.to_boolean(p_init_msg_list) THEN
573 FND_MSG_PUB.initialize;
574 END IF;
575
576 IF NOT FND_API.compatible_api_call(
577 l_api_version,
578 p_api_version,
579 l_api_name,
580 g_pkg_name
581 ) THEN
582 RAISE FND_API.g_exc_unexpected_error;
583 END IF;
584
585 x_return_status := FND_API.G_RET_STS_SUCCESS;
586
587 ------------------------ lock -------------------------
588 IF (AMS_DEBUG_HIGH_ON) THEN
589
590 AMS_Utility_PVT.debug_message(l_full_name||': lock');
591 END IF;
592
593 OPEN c_deliv_kit_item;
594 FETCH c_deliv_kit_item INTO l_deliv_kit_item_id;
595 IF (c_deliv_kit_item%NOTFOUND) THEN
596 CLOSE c_deliv_kit_item;
597 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
598 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
599 FND_MSG_PUB.add;
600 END IF;
601 RAISE FND_API.g_exc_error;
602 END IF;
603 CLOSE c_deliv_kit_item;
604
605 -------------------- finish --------------------------
606 FND_MSG_PUB.count_and_get(
607 p_encoded => FND_API.g_false,
608 p_count => x_msg_count,
609 p_data => x_msg_data
610 );
611
612 IF (AMS_DEBUG_HIGH_ON) THEN
613
614
615
616 AMS_Utility_PVT.debug_message(l_full_name ||': end');
620 EXCEPTION
617
618 END IF;
619
621
622 WHEN AMS_Utility_PVT.resource_locked THEN
623 x_return_status := FND_API.g_ret_sts_error;
624 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
625 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
626 FND_MSG_PUB.add;
627 END IF;
628
629 FND_MSG_PUB.count_and_get(
630 p_encoded => FND_API.g_false,
631 p_count => x_msg_count,
632 p_data => x_msg_data
633 );
634
635 WHEN FND_API.g_exc_error THEN
636 x_return_status := FND_API.g_ret_sts_error;
637 FND_MSG_PUB.count_and_get(
638 p_encoded => FND_API.g_false,
639 p_count => x_msg_count,
640 p_data => x_msg_data
641 );
642
643 WHEN FND_API.g_exc_unexpected_error THEN
644 x_return_status := FND_API.g_ret_sts_unexp_error ;
645 FND_MSG_PUB.count_and_get(
646 p_encoded => FND_API.g_false,
647 p_count => x_msg_count,
648 p_data => x_msg_data
649 );
650
651 WHEN OTHERS THEN
652 x_return_status := FND_API.g_ret_sts_unexp_error ;
653 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
654 THEN
655 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
656 END IF;
657
658 FND_MSG_PUB.count_and_get(
659 p_encoded => FND_API.g_false,
660 p_count => x_msg_count,
661 p_data => x_msg_data
662 );
663
664 END lock_deliv_kit_item;
665
666
667 ---------------------------------------------------------------------
668 -- PROCEDURE
669 -- update_deliv_kit_item
670 --
671 -- HISTORY
672 -- 10/09/99 khung Create.
673 ----------------------------------------------------------------------
674
675 PROCEDURE update_deliv_kit_item
676 (
677 p_api_version IN NUMBER,
678 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
679 p_commit IN VARCHAR2 := FND_API.g_false,
680 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
681
682 x_return_status OUT NOCOPY VARCHAR2,
683 x_msg_count OUT NOCOPY NUMBER,
684 x_msg_data OUT NOCOPY VARCHAR2,
685
686 p_deliv_kit_item_rec IN deliv_kit_item_rec_type
687 )
688
689 IS
690
691 l_api_version CONSTANT NUMBER := 1.0;
692 l_api_name CONSTANT VARCHAR2(30) := 'update_deliv_kit_item';
693 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
694
695 l_deliv_kit_item_rec deliv_kit_item_rec_type;
696 l_return_status VARCHAR2(1);
697
698 BEGIN
699
700 -------------------- initialize -------------------------
701 SAVEPOINT update_deliv_kit_item;
702
703 IF (AMS_DEBUG_HIGH_ON) THEN
704
705
706
707 AMS_Utility_PVT.debug_message(l_full_name||': start');
708
709 END IF;
710
711 IF FND_API.to_boolean(p_init_msg_list) THEN
712 FND_MSG_PUB.initialize;
713 END IF;
714
715 IF NOT FND_API.compatible_api_call(
716 l_api_version,
717 p_api_version,
718 l_api_name,
719 g_pkg_name
720 ) THEN
721 RAISE FND_API.g_exc_unexpected_error;
722 END IF;
723
724 x_return_status := FND_API.G_RET_STS_SUCCESS;
725
726 ----------------------- validate ----------------------
727 IF (AMS_DEBUG_HIGH_ON) THEN
728
729 AMS_Utility_PVT.debug_message(l_full_name ||': validate');
730 END IF;
731
732 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
733 check_deliv_kit_item_items
734 (
735 p_deliv_kit_item_rec => p_deliv_kit_item_rec,
736 p_validation_mode => JTF_PLSQL_API.g_update,
737 x_return_status => l_return_status
738 );
739
740 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
741 RAISE FND_API.g_exc_unexpected_error;
742 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
743 RAISE FND_API.g_exc_error;
744 END IF;
745 END IF;
746
747 -- replace g_miss_char/num/date with current column values
748 complete_deliv_kit_item_rec(p_deliv_kit_item_rec, l_deliv_kit_item_rec);
749
750 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
751 check_deliv_kit_item_record
752 (
753 p_deliv_kit_item_rec => p_deliv_kit_item_rec,
754 p_complete_rec => l_deliv_kit_item_rec,
755 x_return_status => l_return_status
756 );
757
758 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
759 RAISE FND_API.g_exc_unexpected_error;
760 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
761 RAISE FND_API.g_exc_error;
762 END IF;
763 END IF;
764
765 -------------------------- update --------------------
766 IF (AMS_DEBUG_HIGH_ON) THEN
767
768 AMS_Utility_PVT.debug_message(l_full_name ||': update');
772 last_update_date = SYSDATE,
769 END IF;
770
771 UPDATE ams_deliv_kit_items SET
773 last_updated_by = FND_GLOBAL.user_id,
774 last_update_login = FND_GLOBAL.conc_login_id,
775 object_version_number = l_deliv_kit_item_rec.object_version_number + 1,
776 deliverable_kit_id = l_deliv_kit_item_rec.deliverable_kit_id,
777 deliverable_kit_part_id = l_deliv_kit_item_rec.deliverable_kit_part_id,
778 kit_part_included_from_kit_id = l_deliv_kit_item_rec.kit_part_included_from_kit_id,
779 quantity = l_deliv_kit_item_rec.quantity,
780 attribute_category = l_deliv_kit_item_rec.attribute_category,
781 attribute1 = l_deliv_kit_item_rec.attribute1,
782 attribute2 = l_deliv_kit_item_rec.attribute2,
783 attribute3 = l_deliv_kit_item_rec.attribute3,
784 attribute4 = l_deliv_kit_item_rec.attribute4,
785 attribute5 = l_deliv_kit_item_rec.attribute5,
786 attribute6 = l_deliv_kit_item_rec.attribute6,
787 attribute7 = l_deliv_kit_item_rec.attribute7,
788 attribute8 = l_deliv_kit_item_rec.attribute8,
789 attribute9 = l_deliv_kit_item_rec.attribute9,
790 attribute10 = l_deliv_kit_item_rec.attribute10,
791 attribute11 = l_deliv_kit_item_rec.attribute11,
792 attribute12 = l_deliv_kit_item_rec.attribute12,
793 attribute13 = l_deliv_kit_item_rec.attribute13,
794 attribute14 = l_deliv_kit_item_rec.attribute14,
795 attribute15 = l_deliv_kit_item_rec.attribute15
796 WHERE deliverable_kit_item_id = l_deliv_kit_item_rec.deliverable_kit_item_id
797 AND object_version_number = l_deliv_kit_item_rec.object_version_number;
798
799 IF (SQL%NOTFOUND) THEN
800 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
801 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
802 FND_MSG_PUB.add;
803 END IF;
804 RAISE FND_API.g_exc_error;
805 END IF;
806
807 -------------------- finish --------------------------
808 IF FND_API.to_boolean(p_commit) THEN
809 COMMIT;
810 END IF;
811
812 FND_MSG_PUB.count_and_get(
813 p_encoded => FND_API.g_false,
814 p_count => x_msg_count,
815 p_data => x_msg_data
816 );
817
818 IF (AMS_DEBUG_HIGH_ON) THEN
819
820
821
822 AMS_Utility_PVT.debug_message(l_full_name ||': end');
823
824 END IF;
825
826 EXCEPTION
827
828 WHEN FND_API.g_exc_error THEN
829 ROLLBACK TO update_deliv_kit_item;
830 x_return_status := FND_API.g_ret_sts_error;
831 FND_MSG_PUB.count_and_get(
832 p_encoded => FND_API.g_false,
833 p_count => x_msg_count,
834 p_data => x_msg_data
835 );
836
837 WHEN FND_API.g_exc_unexpected_error THEN
838 ROLLBACK TO update_deliv_kit_item;
839 x_return_status := FND_API.g_ret_sts_unexp_error ;
840 FND_MSG_PUB.count_and_get(
841 p_encoded => FND_API.g_false,
842 p_count => x_msg_count,
843 p_data => x_msg_data
844 );
845
846 WHEN OTHERS THEN
847 ROLLBACK TO update_deliv_kit_item;
848 x_return_status := FND_API.g_ret_sts_unexp_error ;
849
850 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
851 THEN
852 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
853 END IF;
854
855 FND_MSG_PUB.count_and_get(
856 p_encoded => FND_API.g_false,
857 p_count => x_msg_count,
858 p_data => x_msg_data
859 );
860
861 END update_deliv_kit_item;
862
863 ---------------------------------------------------------------------
864 -- PROCEDURE
865 -- validate_deliv_kit_item
866 --
867 -- HISTORY
868 -- 10/09/99 khung Create.
869 ----------------------------------------------------------------------
870
871 PROCEDURE validate_deliv_kit_item
872 (
873 p_api_version IN NUMBER,
874 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
875 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
876
877 x_return_status OUT NOCOPY VARCHAR2,
878 x_msg_count OUT NOCOPY NUMBER,
879 x_msg_data OUT NOCOPY VARCHAR2,
880
881 p_deliv_kit_item_rec IN deliv_kit_item_rec_type
882 )
883
884 IS
885
886 l_api_version CONSTANT NUMBER := 1.0;
887 l_api_name CONSTANT VARCHAR2(30) := 'validate_deliv_kit_item';
888 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
889 l_return_status VARCHAR2(1);
890
891 BEGIN
892
893 ----------------------- initialize --------------------
894 IF (AMS_DEBUG_HIGH_ON) THEN
895 AMS_Utility_PVT.debug_message(l_full_name||': start');
896 END IF;
897
898 IF FND_API.to_boolean(p_init_msg_list) THEN
899 FND_MSG_PUB.initialize;
900 END IF;
901
902 IF NOT FND_API.compatible_api_call(
903 l_api_version,
904 p_api_version,
905 l_api_name,
906 g_pkg_name
907 ) THEN
908 RAISE FND_API.g_exc_unexpected_error;
912
909 END IF;
910
911 x_return_status := FND_API.g_ret_sts_success;
913 ---------------------- validate ------------------------
914 IF (AMS_DEBUG_HIGH_ON) THEN
915 AMS_Utility_PVT.debug_message(l_full_name||': check items');
916 END IF;
917
918 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
919 check_deliv_kit_item_items
920 (
921 p_deliv_kit_item_rec => p_deliv_kit_item_rec,
922 p_validation_mode => JTF_PLSQL_API.g_create,
923 x_return_status => l_return_status
924 );
925
926 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
927 RAISE FND_API.g_exc_unexpected_error;
928 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
929 RAISE FND_API.g_exc_error;
930 END IF;
931 END IF;
932
933 IF (AMS_DEBUG_HIGH_ON) THEN
934 AMS_Utility_PVT.debug_message(l_full_name||': check record');
935 END IF;
936
937 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
938 check_deliv_kit_item_record
939 (
940 p_deliv_kit_item_rec => p_deliv_kit_item_rec,
941 p_complete_rec => NULL,
942 x_return_status => l_return_status
943 );
944
945 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
946 RAISE FND_API.g_exc_unexpected_error;
947 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
948 RAISE FND_API.g_exc_error;
949 END IF;
950 END IF;
951
952 -------------------- finish --------------------------
953 FND_MSG_PUB.count_and_get
954 (
955 p_encoded => FND_API.g_false,
956 p_count => x_msg_count,
957 p_data => x_msg_data
958 );
959
960 IF (AMS_DEBUG_HIGH_ON) THEN
961
962
963
964 AMS_Utility_PVT.debug_message(l_full_name ||': end');
965
966 END IF;
967
968 EXCEPTION
969
970 WHEN FND_API.g_exc_error THEN
971 x_return_status := FND_API.g_ret_sts_error;
972 FND_MSG_PUB.count_and_get(
973 p_encoded => FND_API.g_false,
974 p_count => x_msg_count,
975 p_data => x_msg_data
976 );
977
978 WHEN FND_API.g_exc_unexpected_error THEN
979 x_return_status := FND_API.g_ret_sts_unexp_error ;
980 FND_MSG_PUB.count_and_get(
981 p_encoded => FND_API.g_false,
982 p_count => x_msg_count,
983 p_data => x_msg_data
984 );
985
986 WHEN OTHERS THEN
987 x_return_status := FND_API.g_ret_sts_unexp_error;
988 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
989 THEN
990 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
991 END IF;
992
993 FND_MSG_PUB.count_and_get
994 (
995 p_encoded => FND_API.g_false,
996 p_count => x_msg_count,
997 p_data => x_msg_data
998 );
999
1000 END validate_deliv_kit_item;
1001
1002 ---------------------------------------------------------------------
1003 -- PROCEDURE
1004 -- check_deliv_kit_item_req_items
1005 --
1006 -- HISTORY
1007 -- 10/11/99 khung Create.
1008 ---------------------------------------------------------------------
1009
1010 PROCEDURE check_deliv_kit_item_req_items
1011 (
1012 p_deliv_kit_item_rec IN deliv_kit_item_rec_type,
1013 x_return_status OUT NOCOPY VARCHAR2
1014 )
1015
1016 IS
1017
1018 BEGIN
1019
1020 x_return_status := FND_API.g_ret_sts_success;
1021
1022 -------------------- put required items here ---------------------
1023
1024 IF p_deliv_kit_item_rec.deliverable_kit_id IS NULL THEN
1025 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1026 THEN
1027 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_KIT_NO_KIT_ID');
1028 FND_MSG_PUB.add;
1029 END IF;
1030
1031 x_return_status := FND_API.g_ret_sts_error;
1032 RETURN;
1033 END IF;
1034
1035 IF p_deliv_kit_item_rec.deliverable_kit_part_id IS NULL THEN
1036 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1037 THEN
1038 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_KIT_NO_KIT_PART_ID');
1039 FND_MSG_PUB.add;
1040 END IF;
1041
1042 x_return_status := FND_API.g_ret_sts_error;
1043 RETURN;
1044 END IF;
1045
1046 END check_deliv_kit_item_req_items;
1047
1048 ---------------------------------------------------------------------
1049 -- PROCEDURE
1050 -- check_deliv_kit_item_uk_items
1051 --
1052 -- HISTORY
1053 -- 10/11/99 khung Create.
1054 ---------------------------------------------------------------------
1055
1056 PROCEDURE check_deliv_kit_item_uk_items(
1057 p_deliv_kit_item_rec IN deliv_kit_item_rec_type,
1058 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1059 x_return_status OUT NOCOPY VARCHAR2
1060 )
1061 IS
1062
1063 l_valid_flag VARCHAR2(1);
1064
1065 BEGIN
1066
1070 -- check if this deliverable_kit_item_id is unique.
1067 x_return_status := FND_API.g_ret_sts_success;
1068
1069 -- For create_deliv_kit_item, when deliverable_kit_item_id is passed in, we need to
1071 IF p_validation_mode = JTF_PLSQL_API.g_create
1072 AND p_deliv_kit_item_rec.deliverable_kit_item_id IS NOT NULL
1073 THEN
1074 IF AMS_Utility_PVT.check_uniqueness(
1075 'ams_deliv_kit_items',
1076 'deliverable_kit_item_id = ' || p_deliv_kit_item_rec.deliverable_kit_item_id
1077 ) = FND_API.g_false
1078 THEN
1079 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1080 THEN
1081 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_KIT_ITEM_DUP_ID');
1082 FND_MSG_PUB.add;
1083 END IF;
1084 x_return_status := FND_API.g_ret_sts_error;
1085 RETURN;
1086 END IF;
1087 END IF;
1088
1089 -- check other unique items
1090
1091 END check_deliv_kit_item_uk_items;
1092
1093 ---------------------------------------------------------------------
1094 -- PROCEDURE
1095 -- check_deliv_kit_item_fk_items
1096 --
1097 -- HISTORY
1098 -- 10/11/99 khung Create.
1099 ---------------------------------------------------------------------
1100 PROCEDURE check_deliv_kit_item_fk_items
1101 (
1102 p_deliv_kit_item_rec IN deliv_kit_item_rec_type,
1103 x_return_status OUT NOCOPY VARCHAR2
1104 )
1105 IS
1106
1107 BEGIN
1108
1109 x_return_status := FND_API.g_ret_sts_success;
1110
1111 --------------------- deliverable_kit_id ----------------------------
1112 IF p_deliv_kit_item_rec.deliverable_kit_id <> FND_API.g_miss_num
1113 AND p_deliv_kit_item_rec.deliverable_kit_id IS NOT NULL
1114 THEN
1115 IF AMS_Utility_PVT.check_fk_exists(
1116 'ams_deliverables_all_b',
1117 'deliverable_id',
1118 p_deliv_kit_item_rec.deliverable_kit_id,
1119 AMS_Utility_PVT.g_number,
1120 NULL
1121 ) = FND_API.g_false
1122 THEN
1123 AMS_Utility_PVT.Error_Message('AMS_DELIV_KIT_NO_KIT_ID');
1124 x_return_status := FND_API.g_ret_sts_error;
1125 RETURN;
1126 END IF;
1127 END IF;
1128
1129 --------------------- deliverable_kit_part_id ----------------------------
1130 IF p_deliv_kit_item_rec.deliverable_kit_part_id <> FND_API.g_miss_num
1131 AND p_deliv_kit_item_rec.deliverable_kit_part_id IS NOT NULL
1132 THEN
1133 IF AMS_Utility_PVT.check_fk_exists(
1134 'ams_deliverables_all_b',
1135 'deliverable_id',
1136 p_deliv_kit_item_rec.deliverable_kit_part_id,
1137 AMS_Utility_PVT.g_number,
1138 NULL
1139 ) = FND_API.g_false
1140 THEN
1141 AMS_Utility_PVT.Error_Message('AMS_DELV_KIT_TIP');
1142 x_return_status := FND_API.g_ret_sts_error;
1143 RETURN;
1144 END IF;
1145 END IF;
1146
1147 -- check other fk items
1148
1149 END check_deliv_kit_item_fk_items;
1150
1151 ---------------------------------------------------------------------
1152 -- PROCEDURE
1153 -- check_deliv_kit_item_lk_items (lookup)
1154 --
1155 -- HISTORY
1156 -- 10/11/99 khung Create.
1157 ---------------------------------------------------------------------
1158 PROCEDURE check_deliv_kit_item_lk_items
1159 (
1160 p_deliv_kit_item_rec IN deliv_kit_item_rec_type,
1161 x_return_status OUT NOCOPY VARCHAR2
1162 )
1163 IS
1164
1165 BEGIN
1166
1167 x_return_status := FND_API.g_ret_sts_success;
1168
1169 -- check other lookup codes
1170
1171 END check_deliv_kit_item_lk_items;
1172
1173 ---------------------------------------------------------------------
1174 -- PROCEDURE
1175 -- check_kit_types
1176 -- This method is used to find out whether the combination
1177 -- HISTORY
1178 -- 10/11/99 khung Create.
1179 ---------------------------------------------------------------------
1180 PROCEDURE check_kit_types
1181 (
1182 p_deliv_kit_item_rec IN deliv_kit_item_rec_type,
1183 x_return_status OUT NOCOPY VARCHAR2
1184 )
1185 IS
1186
1187 CURSOR get_type(p_Deliv_id IN NUMBER)
1188 IS
1189 SELECT DECODE(can_fulfill_electronic_flag, 'Y', 'ELEC','PHYS')
1190 FROM ams_Deliverables_all_b
1191 where deliverable_id = p_deliv_id;
1192
1193
1194 CURSOR get_detail(p_deliv_id IN NUMBER)
1195 IS
1196 SELECT DECODE(inventory_flag, 'Y', 'INVN','PHYS') TYPE
1197 ,nvl(non_inv_quantity_on_hand,-99999) Quantity
1198 FROM ams_Deliverables_all_b
1199 where deliverable_id = p_deliv_id;
1200
1201 l_kit_type VARCHAR2(4);
1202 l_kit_part_type VARCHAR2(4);
1203
1204 l_kit_rec get_detail%ROWTYPE;
1205 l_kit_part_rec get_detail%ROWTYPE;
1206
1207 BEGIN
1208
1209 x_return_status := FND_API.g_ret_sts_success;
1210
1211 -- getting the type of kit
1212 OPEN get_type(p_deliv_kit_item_rec.deliverable_kit_id);
1213 FETCH get_type INTO l_kit_type;
1214 CLOSE get_type;
1215
1216 -- getting the type of kit part
1217 OPEN get_type(p_deliv_kit_item_rec.deliverable_kit_part_id);
1221 IF l_kit_type <> l_kit_part_type
1218 FETCH get_type INTO l_kit_part_type;
1219 CLOSE get_type;
1220
1222 THEN
1223 AMS_Utility_PVT.Error_Message('AMS_DELV_PHY_ELE_MISMATCH');
1224 -- The Deliverable kit and kit part are of different types. Please select you want to create kit for electronic
1225 -- deliverable or physical deliverable.
1226 x_return_status := FND_API.g_ret_sts_error;
1227 RETURN;
1228 END IF;
1229
1230 IF l_kit_type ='PHYS'
1231 THEN
1232 --collecting the details of Kit
1233 OPEN get_detail(p_deliv_kit_item_rec.deliverable_kit_id);
1234 FETCH get_detail INTO l_kit_rec;
1235 CLOSE get_detail;
1236
1237 --collecting the details of Kit part
1238 OPEN get_detail(p_deliv_kit_item_rec.deliverable_kit_part_id);
1239 FETCH get_detail INTO l_kit_part_rec;
1240 CLOSE get_detail;
1241
1242 IF l_kit_rec.type <> l_kit_part_rec.type
1243 THEN
1244 AMS_Utility_PVT.Error_Message('AMS_DELV_STO_INV_MISMATCH');
1245 -- The Deliverable kit and kit part are of different types. Please select whether you want to create kit for physically inventoried
1246 -- deliverable or physically stocked manual deliverable.
1247 x_return_status := FND_API.g_ret_sts_error;
1248 RETURN;
1249 END IF;
1250
1251 IF l_kit_rec.type = 'PHYS'
1252 AND( l_kit_rec.quantity = -99999
1253 OR l_kit_part_rec.quantity = -99999)
1254 THEN
1255 AMS_Utility_PVT.Error_Message('AMS_DELV_KIT_NO_STOCK');
1256 -- Please make this physical deliverable kit or kit part as stock manually, by adding the quantity .
1257 x_return_status := FND_API.g_ret_sts_error;
1258 RETURN;
1259 END IF;
1260
1261 END IF;
1262
1263 END check_kit_types;
1264
1265 ---------------------------------------------------------------------
1266 -- PROCEDURE
1267 -- check_deliv_kit_item_fg_items (flag)
1268 --
1269 -- HISTORY
1270 -- 10/11/99 khung Create.
1271 ---------------------------------------------------------------------
1272 PROCEDURE check_deliv_kit_item_fg_items
1273 (
1274 p_deliv_kit_item_rec IN deliv_kit_item_rec_type,
1275 x_return_status OUT NOCOPY VARCHAR2
1276 )
1277 IS
1278
1279 BEGIN
1280
1281 x_return_status := FND_API.g_ret_sts_success;
1282
1283 -- check other flags
1284
1285 END check_deliv_kit_item_fg_items;
1286
1287
1288 ---------------------------------------------------------------------
1289 -- PROCEDURE
1290 -- check_deliv_kit_item_items
1291 --
1292 -- HISTORY
1293 -- 10/11/99 khung Create.
1294 ---------------------------------------------------------------------
1295 PROCEDURE check_deliv_kit_item_items
1296 (
1297 p_deliv_kit_item_rec IN deliv_kit_item_rec_type,
1298 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1299 x_return_status OUT NOCOPY VARCHAR2
1300 )
1301 IS
1302
1303
1304 BEGIN
1305
1306 check_deliv_kit_item_req_items
1307 (
1308 p_deliv_kit_item_rec => p_deliv_kit_item_rec,
1309 x_return_status => x_return_status
1310 );
1311
1312 IF x_return_status <> FND_API.g_ret_sts_success THEN
1313 RETURN;
1314 END IF;
1315
1316 check_deliv_kit_item_uk_items
1317 (
1318 p_deliv_kit_item_rec => p_deliv_kit_item_rec,
1319 p_validation_mode => p_validation_mode,
1320 x_return_status => x_return_status
1321 );
1322
1323 IF x_return_status <> FND_API.g_ret_sts_success THEN
1324 RETURN;
1325 END IF;
1326
1327 check_deliv_kit_item_fk_items
1328 (
1329 p_deliv_kit_item_rec => p_deliv_kit_item_rec,
1330 x_return_status => x_return_status
1331 );
1332
1333 IF x_return_status <> FND_API.g_ret_sts_success THEN
1334 RETURN;
1335 END IF;
1336
1337 check_deliv_kit_item_lk_items
1338 (
1339 p_deliv_kit_item_rec => p_deliv_kit_item_rec,
1340 x_return_status => x_return_status
1341 );
1342
1343 IF x_return_status <> FND_API.g_ret_sts_success THEN
1344 RETURN;
1345 END IF;
1346
1347 check_deliv_kit_item_fg_items
1348 (
1349 p_deliv_kit_item_rec => p_deliv_kit_item_rec,
1350 x_return_status => x_return_status
1351 );
1352
1353 IF x_return_status <> FND_API.g_ret_sts_success THEN
1354 RETURN;
1355 END IF;
1356
1357 check_kit_types
1358 (
1359 p_deliv_kit_item_rec => p_deliv_kit_item_rec,
1360 x_return_status => x_return_status
1361 );
1362
1363 IF x_return_status <> FND_API.g_ret_sts_success THEN
1364 RETURN;
1365 END IF;
1366
1367
1368 END check_deliv_kit_item_items;
1369
1370
1371 ---------------------------------------------------------------------
1372 -- PROCEDURE
1373 -- check_deliv_kit_item_record
1374 --
1375 -- HISTORY
1376 -- 10/11/99 khung Create.
1377 ---------------------------------------------------------------------
1378
1379 PROCEDURE check_deliv_kit_item_record
1380 (
1381 p_deliv_kit_item_rec IN deliv_kit_item_rec_type,
1382 p_complete_rec IN deliv_kit_item_rec_type := NULL,
1383 x_return_status OUT NOCOPY VARCHAR2
1384 )
1385
1386 IS
1387
1388 BEGIN
1389
1390 x_return_status := FND_API.g_ret_sts_success;
1391
1392 -- do other record level checkings
1393
1394 END check_deliv_kit_item_record;
1395
1396
1397 ---------------------------------------------------------------------
1398 -- PROCEDURE
1399 -- init_deliv_kit_ite_rec
1400 --
1401 -- HISTORY
1402 -- 10/11/99 khung Create.
1403 ---------------------------------------------------------------------
1404
1405 PROCEDURE init_deliv_kit_ite_rec
1406 (
1407 x_deliv_kit_item_rec OUT NOCOPY deliv_kit_item_rec_type
1408 )
1409 IS
1410
1411 BEGIN
1412
1413 x_deliv_kit_item_rec.deliverable_kit_item_id := FND_API.g_miss_num;
1414 x_deliv_kit_item_rec.last_update_date := FND_API.g_miss_date;
1415 x_deliv_kit_item_rec.last_updated_by := FND_API.g_miss_num;
1416 x_deliv_kit_item_rec.creation_date := FND_API.g_miss_date;
1417 x_deliv_kit_item_rec.created_by := FND_API.g_miss_num;
1418 x_deliv_kit_item_rec.last_update_login := FND_API.g_miss_num;
1419 x_deliv_kit_item_rec.object_version_number := FND_API.g_miss_num;
1420 x_deliv_kit_item_rec.deliverable_kit_id := FND_API.g_miss_num;
1421 x_deliv_kit_item_rec.deliverable_kit_part_id := FND_API.g_miss_num;
1422 x_deliv_kit_item_rec.kit_part_included_from_kit_id := FND_API.g_miss_num;
1423 x_deliv_kit_item_rec.quantity := FND_API.g_miss_num;
1424
1425 x_deliv_kit_item_rec.attribute_category := FND_API.g_miss_char;
1426 x_deliv_kit_item_rec.attribute1 := FND_API.g_miss_char;
1427 x_deliv_kit_item_rec.attribute2 := FND_API.g_miss_char;
1428 x_deliv_kit_item_rec.attribute3 := FND_API.g_miss_char;
1429 x_deliv_kit_item_rec.attribute4 := FND_API.g_miss_char;
1430 x_deliv_kit_item_rec.attribute5 := FND_API.g_miss_char;
1431 x_deliv_kit_item_rec.attribute6 := FND_API.g_miss_char;
1432 x_deliv_kit_item_rec.attribute7 := FND_API.g_miss_char;
1433 x_deliv_kit_item_rec.attribute8 := FND_API.g_miss_char;
1434 x_deliv_kit_item_rec.attribute9 := FND_API.g_miss_char;
1435 x_deliv_kit_item_rec.attribute10 := FND_API.g_miss_char;
1436 x_deliv_kit_item_rec.attribute11 := FND_API.g_miss_char;
1437 x_deliv_kit_item_rec.attribute12 := FND_API.g_miss_char;
1438 x_deliv_kit_item_rec.attribute13 := FND_API.g_miss_char;
1439 x_deliv_kit_item_rec.attribute14 := FND_API.g_miss_char;
1440 x_deliv_kit_item_rec.attribute15 := FND_API.g_miss_char;
1441
1442 END init_deliv_kit_ite_rec;
1443
1444
1445 ---------------------------------------------------------------------
1446 -- PROCEDURE
1447 -- complete_deliv_kit_item_rec
1448 --
1449 -- HISTORY
1450 -- 10/11/99 khung Create.
1451 ---------------------------------------------------------------------
1452 PROCEDURE complete_deliv_kit_item_rec
1453 (
1454 p_deliv_kit_item_rec IN deliv_kit_item_rec_type,
1455 x_complete_rec OUT NOCOPY deliv_kit_item_rec_type
1456 )
1457
1458 IS
1459
1460 CURSOR c_deliv_kit_item IS
1461 SELECT *
1462 FROM ams_deliv_kit_items
1463 WHERE deliverable_kit_item_id = p_deliv_kit_item_rec.deliverable_kit_item_id;
1464
1465 l_deliv_kit_item_rec c_deliv_kit_item%ROWTYPE;
1466
1467 BEGIN
1471 OPEN c_deliv_kit_item;
1468
1469 x_complete_rec := p_deliv_kit_item_rec;
1470
1472 FETCH c_deliv_kit_item INTO l_deliv_kit_item_rec;
1473 IF c_deliv_kit_item%NOTFOUND THEN
1474 CLOSE c_deliv_kit_item;
1475 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1476 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1477 FND_MSG_PUB.add;
1478 END IF;
1479 RAISE FND_API.g_exc_error;
1480 END IF;
1481 CLOSE c_deliv_kit_item;
1482
1483 IF p_deliv_kit_item_rec.deliverable_kit_id = FND_API.g_miss_num THEN
1484 x_complete_rec.deliverable_kit_id := l_deliv_kit_item_rec.deliverable_kit_id;
1485 END IF;
1486
1487 IF p_deliv_kit_item_rec.deliverable_kit_part_id = FND_API.g_miss_num THEN
1488 x_complete_rec.deliverable_kit_part_id := l_deliv_kit_item_rec.deliverable_kit_part_id;
1489 END IF;
1490
1491 IF p_deliv_kit_item_rec.kit_part_included_from_kit_id = FND_API.g_miss_num THEN
1492 x_complete_rec.kit_part_included_from_kit_id := l_deliv_kit_item_rec.kit_part_included_from_kit_id;
1493 END IF;
1494
1495 IF p_deliv_kit_item_rec.quantity = FND_API.g_miss_num THEN
1496 x_complete_rec.quantity := l_deliv_kit_item_rec.quantity;
1497 END IF;
1498
1499 IF p_deliv_kit_item_rec.attribute_category = FND_API.g_miss_char THEN
1500 x_complete_rec.attribute_category := l_deliv_kit_item_rec.attribute_category;
1501 END IF;
1502
1503 IF p_deliv_kit_item_rec.attribute1 = FND_API.g_miss_char THEN
1504 x_complete_rec.attribute1 := l_deliv_kit_item_rec.attribute1;
1505 END IF;
1506
1507 IF p_deliv_kit_item_rec.attribute2 = FND_API.g_miss_char THEN
1508 x_complete_rec.attribute2 := l_deliv_kit_item_rec.attribute2;
1509 END IF;
1510
1511 IF p_deliv_kit_item_rec.attribute3 = FND_API.g_miss_char THEN
1512 x_complete_rec.attribute3 := l_deliv_kit_item_rec.attribute3;
1513 END IF;
1514
1515 IF p_deliv_kit_item_rec.attribute4 = FND_API.g_miss_char THEN
1516 x_complete_rec.attribute4 := l_deliv_kit_item_rec.attribute4;
1517 END IF;
1518
1519 IF p_deliv_kit_item_rec.attribute5 = FND_API.g_miss_char THEN
1520 x_complete_rec.attribute5 := l_deliv_kit_item_rec.attribute5;
1521 END IF;
1522
1523 IF p_deliv_kit_item_rec.attribute6 = FND_API.g_miss_char THEN
1524 x_complete_rec.attribute6 := l_deliv_kit_item_rec.attribute6;
1525 END IF;
1526
1527 IF p_deliv_kit_item_rec.attribute7 = FND_API.g_miss_char THEN
1528 x_complete_rec.attribute7 := l_deliv_kit_item_rec.attribute7;
1529 END IF;
1530
1531 IF p_deliv_kit_item_rec.attribute8 = FND_API.g_miss_char THEN
1532 x_complete_rec.attribute8 := l_deliv_kit_item_rec.attribute8;
1533 END IF;
1534
1535 IF p_deliv_kit_item_rec.attribute9 = FND_API.g_miss_char THEN
1536 x_complete_rec.attribute9 := l_deliv_kit_item_rec.attribute9;
1537 END IF;
1538
1539 IF p_deliv_kit_item_rec.attribute10 = FND_API.g_miss_char THEN
1540 x_complete_rec.attribute10 := l_deliv_kit_item_rec.attribute10;
1541 END IF;
1542
1543 IF p_deliv_kit_item_rec.attribute11 = FND_API.g_miss_char THEN
1544 x_complete_rec.attribute11 := l_deliv_kit_item_rec.attribute11;
1545 END IF;
1546
1547 IF p_deliv_kit_item_rec.attribute12 = FND_API.g_miss_char THEN
1548 x_complete_rec.attribute12 := l_deliv_kit_item_rec.attribute12;
1549 END IF;
1550
1551 IF p_deliv_kit_item_rec.attribute13 = FND_API.g_miss_char THEN
1552 x_complete_rec.attribute13 := l_deliv_kit_item_rec.attribute13;
1553 END IF;
1554
1555 IF p_deliv_kit_item_rec.attribute14 = FND_API.g_miss_char THEN
1556 x_complete_rec.attribute14 := l_deliv_kit_item_rec.attribute14;
1557 END IF;
1558
1559 IF p_deliv_kit_item_rec.attribute15 = FND_API.g_miss_char THEN
1560 x_complete_rec.attribute15 := l_deliv_kit_item_rec.attribute15;
1561 END IF;
1562
1563
1564 END complete_deliv_kit_item_rec;
1565
1566
1567 END AMS_DelivKitItem_PVT;