[Home] [Help]
PACKAGE BODY: APPS.AMS_DELIVERABLE_PVT
Source
1 PACKAGE BODY AMS_Deliverable_PVT AS
2 /* $Header: amsvdelb.pls 120.2 2010/05/10 09:12:53 amlal ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30):='AMS_Deliverable_PVT';
5
6 ---------------------------------------------------------------------
7 -- PROCEDURE
8 -- create_pricelist_header
9 --
10 -- HISTORY
11 -- 02/16/2000 khung@us 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_pricelist_header
19 (
20 p_api_version IN NUMBER,
21 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
22 p_return_values IN VARCHAR2 := FND_API.g_false,
23 p_commit IN VARCHAR2 := FND_API.g_false,
24 p_deliv_rec IN deliv_rec_type,
25 x_return_status OUT NOCOPY VARCHAR2,
26 x_msg_count OUT NOCOPY NUMBER,
27 x_msg_data OUT NOCOPY VARCHAR2,
28 x_pricelist_header_id OUT NOCOPY NUMBER
29 );
30 --------------------------------------------------------------------
31 --
32 -- PROCEDURE
33 -- deliverable_cancellation
34 --
35 -- HISTORY
36 -- 10/04/2000 musman@us Create
37 --
38 ---------------------------------------------------------------------
39 PROCEDURE Deliverable_Cancellation
40 (
41 p_deliverable_rec IN deliv_rec_type,
42 x_return_status OUT NOCOPY VARCHAR2
43 );
44
45 ---------------------------------------------------------------------
46 -- PROCEDURE
47 -- create_pricelist_line
48 --
49 -- HISTORY
50 -- 02/17/2000 khung@us Create.
51 ---------------------------------------------------------------------
52
53 PROCEDURE create_pricelist_line
54 (
55 p_api_version IN NUMBER,
56 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
57 p_return_values IN VARCHAR2 := FND_API.g_false,
58 p_commit IN VARCHAR2 := FND_API.g_false,
59 p_price_hdr_id IN NUMBER,
60 p_deliv_rec IN deliv_rec_type,
61 x_return_status OUT NOCOPY VARCHAR2,
62 x_msg_count OUT NOCOPY NUMBER,
63 x_msg_data OUT NOCOPY VARCHAR2,
64 x_pricelist_line_id OUT NOCOPY NUMBER
65 );
66
67 ---------------------------------------------------------------------
68 -- PROCEDURE
69 -- create_jtf_attachment
70 --
71 -- HISTORY
72 -- 02/22/2000 khung@us Create.
73 ---------------------------------------------------------------------
74
75 PROCEDURE create_jtf_attachment
76 (
77 p_used_by IN VARCHAR2,
78 p_used_by_id IN NUMBER,
79 p_file_id IN NUMBER,
80 p_file_name IN VARCHAR2,
81 p_att_type IN VARCHAR2,
82 p_file_ver IN VARCHAR2,
83 x_return_status OUT NOCOPY VARCHAR2,
84 x_msg_count OUT NOCOPY NUMBER,
85 x_msg_data OUT NOCOPY VARCHAR2,
86 x_att_id OUT NOCOPY NUMBER
87 );
88
89 -------------------------------------------------------------------
90 -- PROCEDURE
91 -- check_owner_id
92 --
93 -- HISTORY
94 -- 03/14/2001 musman@us Created
95 -------------------------------------------------------------------
96
97 PROCEDURE check_owner_id
98 (
99 p_deliv_rec IN deliv_rec_type,
100 x_return_status OUT NOCOPY VARCHAR2
101 );
102 -------------------------------------------------------------------
103 -- PROCEDURE
104 -- check_budget_lines
105 --
106 -- HISTORY
107 -- 04/05/2001 musman@us Create
108 -------------------------------------------------------------------
109
110 PROCEDURE check_budget_lines
111 (
112 p_deliv_rec IN deliv_rec_type,
113 x_return_status OUT NOCOPY VARCHAR2
114 );
115 -------------------------------------------------------------------
116 -- PROCEDURE
117 -- check_inv_item
118 --
119 -- HISTORY
120 -- 11/26/2002 musman@us Create
121 -------------------------------------------------------------------
122
123 PROCEDURE check_inv_item
124 (
125 p_deliv_rec IN deliv_rec_type,
126 x_return_status OUT NOCOPY VARCHAR2
127 );
128
129 -------------------------------------------------------------------
130 -- PROCEDURE
131 -- create_inv_item
132 --
133 -- HISTORY
134 -- 02/25/2002 musman@us Create
135 -------------------------------------------------------------------
136
137 PROCEDURE creat_inv_item
138 (
139 p_deliv_rec IN deliv_rec_type,
140 x_inv_id OUT NOCOPY NUMBER,
141 x_org_id OUT NOCOPY NUMBER,
142 x_return_status OUT NOCOPY VARCHAR2,
143 x_msg_count OUT NOCOPY NUMBER,
144 x_msg_data OUT NOCOPY VARCHAR2
145 );
146 -------------------------------------------------------------------
147 -- PROCEDURE
148 -- check_inactive_deliv
149 --
150 -- HISTORY
151 -- 02/25/2002 musman@us Create
152 -------------------------------------------------------------------
153
154 PROCEDURE check_inactive_deliv
155 (
156 p_deliv_rec IN deliv_rec_type,
157 x_return_status OUT NOCOPY VARCHAR2
158 );
159
160 -------------------------------------------------------------------
161 -- PROCEDURE
162 -- check_periods
163 --
164 -- HISTORY
165 -- 02/21/2001 musman@us Created
166 -------------------------------------------------------------------
167 PROCEDURE check_periods
168 ( p_deliv_Rec IN deliv_rec_type
169 ,x_deliverable_calendar OUT NOCOPY VARCHAR2
170 ,x_return_status OUT NOCOPY VARCHAR2);
171
172 ------------------------------------------------------------------
173 -- Function
174 -- Approval_required_flag
175 -- Purpose
176 -- This function will return the approval required flag for the
177 -- given custom setup.
178 -- History
179 -- 07/31/2001 musman@us created
180 ------------------------------------------------------------------
181 FUNCTION Approval_Required_Flag
182 ( p_custom_setup_id IN NUMBER ,
183 p_approval_type IN VARCHAR2
184 ) RETURN VARCHAR2;
185
186
187
188 --------------------------------------------------------------------
189 -- PROCEDURE
190 -- create_deliverable
191 --
192 -- HISTORY
193 -- 10/09/1999 khung@us Create.
194 -- 24-Aug-2000 choang Added l_task_planned_start_date to
195 -- make the start date less than the
196 -- the end date.
197 ---------------------------------------------------------------------
198
199 PROCEDURE create_deliverable
200 (
201 p_api_version IN NUMBER,
202 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
203 p_commit IN VARCHAR2 := FND_API.g_false,
204 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
205
206 x_return_status OUT NOCOPY VARCHAR2,
207 x_msg_count OUT NOCOPY NUMBER,
208 x_msg_data OUT NOCOPY VARCHAR2,
209
210 p_deliv_rec IN deliv_rec_type,
211 x_deliv_id OUT NOCOPY NUMBER
212
213 )
214 IS
215
216 l_api_version CONSTANT NUMBER := 1.0;
217 l_api_name CONSTANT VARCHAR2(30) := 'create_deliverable';
218 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
219
220 l_return_status VARCHAR2(1);
221 l_deliv_rec deliv_rec_type := p_deliv_rec;
222 l_deliv_count NUMBER;
223 l_status_code VARCHAR2(30);
224
225 l_obj_type VARCHAR2(30);
226 l_obj_id NUMBER;
227 l_setup_id NUMBER;
228 l_task_planned_start_date DATE := SYSDATE;
229
230 CURSOR c_deliv_seq IS
231 SELECT ams_deliverables_all_b_s.NEXTVAL
232 FROM DUAL;
233
234 CURSOR c_deliv_count(deliv_id IN NUMBER) IS
235 SELECT COUNT(*)
236 FROM ams_deliverables_vl
237 WHERE deliverable_id = deliv_id;
238
239 CURSOR c_status_code(status_id IN NUMBER) IS
240 SELECT system_status_code
241 FROM ams_user_statuses_vl
242 WHERE system_status_type = 'AMS_DELIV_STATUS'
243 AND user_status_id = status_id;
244
245 CURSOR c_get_custom_setup_id IS
246 SELECT custom_setup_id
247 FROM ams_custom_setups_b
248 WHERE object_type = 'DELV';
249 /*
250 CURSOR c_get_status_id(status_code IN VARCHAR2) IS
251 SELECT user_status_id
252 -- SQLID: 11753160 fix FROM ams_user_statuses_vl a
253 FROM ams_user_statuses_b a
254 WHERE system_status_type = 'AMS_DELIV_STATUS'
255 AND system_Status_code = status_code
256 AND a.user_status_id = (SELECT MIN(b.user_status_id)
257 --SQLID: 11753160 fix FROM ams_user_statuses_vl b
258 FROM ams_user_statuses_b b
259 WHERE b.system_status_type = a.system_status_type
260 AND a.system_status_code = b.system_Status_code) ;
261 */
262
263 -- soagrawa replaced the above cursor with the following cursor, for R12 drop 4 sql id 14419805
264 -- refer bug 4956974
265 -- still not sure why MIN is being used instead of default flag = 'Y' : open issue
266 CURSOR c_get_status_id(status_code IN VARCHAR2) IS
267 SELECT min(user_status_id)
268 FROM ams_user_statuses_b a
269 WHERE system_status_type = 'AMS_DELIV_STATUS'
270 AND system_Status_code = status_code;
271
272
273 -- variables for task creation
274 x_task_id NUMBER;
275
276 -- variables for task assignment creation
277 x_task_assignment_id NUMBER;
278
279 x_deliv_rec_from_init deliv_rec_type ;
280 x_deliv_rec deliv_rec_type ;
281 l_msg_count NUMBER;
282 l_msg_data VARCHAR2(2000);
283
284 l_access_rec AMS_Access_PVT.access_rec_type;
285
286 l_association_rec AMS_Associations_PVT.association_rec_type;
287 l_object_association_id NUMBER;
288
289 l_trim_task_name VARCHAR2(80);
290 l_source_object_name VARCHAR2(80);
291
292 BEGIN
293
294 --------------------- initialize -----------------------
295 SAVEPOINT create_deliverable;
296
297 IF (AMS_DEBUG_HIGH_ON) THEN
298 AMS_Utility_PVT.debug_message(l_full_name||': start');
299 END IF;
300
301 IF FND_API.to_boolean(p_init_msg_list) THEN
302 FND_MSG_PUB.initialize;
303 END IF;
304
305 IF NOT FND_API.compatible_api_call
306 (
307 l_api_version,
308 p_api_version,
309 l_api_name,
310 g_pkg_name
311 ) THEN
312 RAISE FND_API.g_exc_unexpected_error;
313 END IF;
314
315 x_return_status := FND_API.g_ret_sts_success;
316
317 /* added by musman
318 if the periods exist then only the calendar values has to updated
319 */
320 IF ((l_deliv_rec.start_period_name IS NOT NULL)
321 OR (l_deliv_rec.end_period_name IS NOT NULL)) THEN
322
323 -- default deliverable_calendar
324 IF l_deliv_rec.deliverable_calendar IS NULL THEN
325 l_deliv_rec.deliverable_calendar := FND_PROFILE.value('AMS_CAMPAIGN_DEFAULT_CALENDER');
326 END IF;
327
328 END IF;
329
330 ----------------------- validate -----------------------
331 IF (AMS_DEBUG_HIGH_ON) THEN
332 AMS_Utility_PVT.debug_message(l_full_name ||': validate');
333 END IF;
334
335 IF l_deliv_rec.transaction_currency_code IS NULL
336 OR l_deliv_rec.transaction_currency_code = FND_API.G_MISS_CHAR
337 THEN
338 l_deliv_rec.transaction_currency_code := l_deliv_rec.currency_code;
339 END IF;
340
341 -- calling the public apis validate_delieverable
342 -- validate_deliverable
343 ams_deliverable_pub.validate_deliverable
344 (
345 p_api_version_number => l_api_version,
346 p_init_msg_list => FND_API.g_false,
347 p_validation_level => p_validation_level,
348 --p_validation_mode => JTF_PLSQL_API.g_create,
349 x_return_status => l_return_status,
350 x_msg_count => x_msg_count,
351 x_msg_data => x_msg_data,
352 p_deliv_rec => l_deliv_rec
353 );
354
355 IF l_return_status = FND_API.g_ret_sts_error THEN
356 RAISE FND_API.g_exc_error;
357 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
358 RAISE FND_API.g_exc_unexpected_error;
359 END IF;
360
361 -------------------------- insert --------------------------
362 IF (AMS_DEBUG_HIGH_ON) THEN
363 AMS_Utility_PVT.debug_message(l_full_name ||': insert');
364 END IF;
365
366 IF l_deliv_rec.deliverable_id IS NULL THEN
367 LOOP
368 OPEN c_deliv_seq;
369 FETCH c_deliv_seq INTO l_deliv_rec.deliverable_id;
370 CLOSE c_deliv_seq;
371
372 OPEN c_deliv_count(l_deliv_rec.deliverable_id);
373 FETCH c_deliv_count INTO l_deliv_count;
374 CLOSE c_deliv_count;
375
376 EXIT WHEN l_deliv_count = 0;
377 END LOOP;
378 END IF;
379
380
381 /* added musman since all the deliverables has to be created with status new */
382 IF l_deliv_rec.user_status_id IS NOT NULL THEN
383 OPEN c_status_code(l_deliv_rec.user_status_id);
384 FETCH c_status_code INTO l_status_code;
385 CLOSE c_status_code;
386 ELSE
387 l_status_code := 'NEW';
388 OPEN c_get_status_id(l_status_code);
389 FETCH c_get_status_id INTO l_deliv_Rec.user_status_id;
390 CLOSE c_get_status_id;
391 END IF;
392 /*
393 OPEN c_status_code(l_deliv_rec.user_status_id);
394 FETCH c_status_code INTO l_status_code;
395 CLOSE c_status_code;
396 */
397 l_deliv_rec.status_code := l_status_code;
398
399
400 IF (AMS_DEBUG_HIGH_ON) THEN
401 AMS_Utility_PVT.debug_message(l_full_name ||': The DBMS Org value**'||userenv('CLIENT_INFO')||'***');
402 END IF;
403
404 IF (AMS_DEBUG_HIGH_ON) THEN
405 AMS_Utility_PVT.debug_message(l_full_name ||': The DBMS changed value**'||SUBSTRB(userenv('CLIENT_INFO'),1,10)||'***');
406 END IF;
407
408
409
410
411
412 INSERT INTO ams_deliverables_all_b(
413 deliverable_id,
414 last_update_date,
415 last_updated_by,
416 creation_date,
417 created_by,
418 last_update_login,
419 object_version_number,
420 language_code,
421 version,
422 application_id,
423 user_status_id,
424 status_code,
425 status_date,
426 active_flag,
427 private_flag,
428 owner_user_id,
429 org_id,
430 fund_source_id,
431 fund_source_type,
432 category_type_id,
433 category_sub_type_id,
434 kit_flag,
435 can_fulfill_electronic_flag,
436 can_fulfill_physical_flag,
437 jtf_amv_item_id,
438 inventory_flag,
439 transaction_currency_code,
440 functional_currency_code,
441 budget_amount_tc,
442 budget_amount_fc,
443 actual_avail_from_date,
444 actual_avail_to_date,
445 forecasted_complete_date,
446 actual_complete_date,
447 replaced_by_deliverable_id,
448 inventory_item_id,
449 inventory_item_org_id,
450 pricelist_header_id,
451 pricelist_line_id,
452 non_inv_ctrl_code,
453 non_inv_quantity_on_hand,
454 non_inv_quantity_on_order,
455 non_inv_quantity_on_reserve,
456 chargeback_amount,
457 chargeback_amount_curr_code,
458 deliverable_code,
459 deliverable_pick_flag,
460 currency_code,
461 forecasted_cost,
462 actual_cost,
463 forecasted_responses,
464 actual_responses,
465 country,
466 attribute_category,
467 attribute1,
468 attribute2,
469 attribute3,
470 attribute4,
471 attribute5,
472 attribute6,
473 attribute7,
474 attribute8,
475 attribute9,
476 attribute10,
477 attribute11,
478 attribute12,
479 attribute13,
480 attribute14,
481 attribute15,
482 chargeback_uom ,
483 deliverable_calendar,
484 start_period_name,
485 end_period_name,
486 country_id,
487 custom_setup_id,
488 email_content_type
489 ) VALUES (
490 l_deliv_rec.deliverable_id,
491 SYSDATE,
492 FND_GLOBAL.user_id,
493 SYSDATE,
494 FND_GLOBAL.user_id,
495 FND_GLOBAL.conc_login_id,
496 1, -- object_version_number
497 l_deliv_rec.language_code,
498 l_deliv_rec.version,
499 l_deliv_rec.application_id,
500 l_deliv_rec.user_status_id,
501 l_deliv_rec.status_code,
502 l_deliv_rec.status_date,
503 NVL(l_deliv_rec.active_flag, 'Y'),
504 NVL(l_deliv_rec.private_flag, 'N'),
505 l_deliv_rec.owner_user_id,
506 TO_NUMBER(SUBSTRB(userenv('CLIENT_INFO'),1,10)), -- org_id
507 l_deliv_rec.fund_source_id,
508 l_deliv_rec.fund_source_type,
509 l_deliv_rec.category_type_id,
510 l_deliv_rec.category_sub_type_id,
511 NVL(l_deliv_rec.kit_flag, 'N'),
512 NVL(l_deliv_rec.can_fulfill_electronic_flag, 'N'),
513 NVL(l_deliv_rec.can_fulfill_physical_flag, 'N'),
514 l_deliv_rec.jtf_amv_item_id,
515 NVL(l_deliv_rec.inventory_flag, 'N'),
516 l_deliv_rec.transaction_currency_code,--l_deliv_rec.currency_code, /* since defaulted to currency_code if it is null */
517 l_deliv_rec.functional_currency_code,
518 l_deliv_rec.budget_amount_tc,
519 l_deliv_rec.budget_amount_fc,
520 l_deliv_rec.actual_avail_from_date,
521 l_deliv_rec.actual_avail_to_date,
522 l_deliv_rec.forecasted_complete_date,
523 l_deliv_rec.actual_complete_date,
524 l_deliv_rec.replaced_by_deliverable_id,
525 l_deliv_rec.inventory_item_id,
526 l_deliv_rec.inventory_item_org_id,
527 l_deliv_rec.pricelist_header_id,
528 l_deliv_rec.pricelist_line_id,
529 l_deliv_rec.non_inv_ctrl_code,
530 l_deliv_rec.non_inv_quantity_on_hand,
531 l_deliv_rec.non_inv_quantity_on_order,
532 l_deliv_rec.non_inv_quantity_on_reserve,
533 l_deliv_rec.chargeback_amount,
534 l_deliv_rec.chargeback_amount_curr_code,
535 l_deliv_rec.deliverable_code,
536 NVL(l_deliv_rec.deliverable_pick_flag, 'N'),
537 l_deliv_rec.currency_code,
538 l_deliv_rec.forecasted_cost,
539 l_deliv_rec.actual_cost,
540 l_deliv_rec.forecasted_responses,
541 l_deliv_rec.actual_responses,
542 l_deliv_rec.country,
543 l_deliv_rec.attribute_category,
544 l_deliv_rec.attribute1,
545 l_deliv_rec.attribute2,
546 l_deliv_rec.attribute3,
547 l_deliv_rec.attribute4,
548 l_deliv_rec.attribute5,
549 l_deliv_rec.attribute6,
550 l_deliv_rec.attribute7,
551 l_deliv_rec.attribute8,
552 l_deliv_rec.attribute9,
553 l_deliv_rec.attribute10,
554 l_deliv_rec.attribute11,
555 l_deliv_rec.attribute12,
556 l_deliv_rec.attribute13,
557 l_deliv_rec.attribute14,
558 l_deliv_rec.attribute15,
559 l_deliv_rec.chargeback_uom ,
560 l_deliv_rec.deliverable_calendar,
561 l_deliv_rec.start_period_name,
562 l_deliv_rec.end_period_name,
563 l_deliv_rec.country_id,
564 l_deliv_Rec.setup_id,
565 l_deliv_Rec.email_content_type
566 );
567
568 INSERT INTO ams_deliverables_all_tl(
569 deliverable_id,
570 language,
571 last_update_date,
572 last_updated_by,
573 creation_date,
574 created_by,
575 last_update_login,
576 source_lang,
577 deliverable_name,
578 description
579 )
580 SELECT
581 l_deliv_rec.deliverable_id,
582 l.language_code,
583 SYSDATE,
584 FND_GLOBAL.user_id,
585 SYSDATE,
586 FND_GLOBAL.user_id,
587 FND_GLOBAL.conc_login_id,
588 USERENV('LANG'),
589 l_deliv_rec.deliverable_name,
590 l_deliv_rec.description
591 FROM fnd_languages l
592 WHERE l.installed_flag in ('I', 'B')
593 AND NOT EXISTS(
594 SELECT NULL
595 FROM ams_deliverables_all_tl t
596 WHERE t.deliverable_id = l_deliv_rec.deliverable_id
597 AND t.language = l.language_code );
598
599 l_obj_type := 'DELV';
600 l_obj_id := l_deliv_rec.deliverable_id;
601
602
603 /* commentend by musman
604 --since ams_object_attributes table will not be used anymore.
605 -- insert into ams_object_attributes table
606 -- for R2 deliverable screens cue card implementation
607 -- add by khung@us 03/15/2000
608
609 OPEN c_get_custom_setup_id;
610 FETCH c_get_custom_setup_id INTO l_setup_id;
611 CLOSE c_get_custom_setup_id;
612
613 -- create object attributes
614 IF (AMS_DEBUG_HIGH_ON) THEN
615 AMS_Utility_PVT.debug_message('calling AMS_ObjectAttribute_PVT.create_object_attributes');
616 END IF;
617
618 AMS_ObjectAttribute_PVT.create_object_attributes(
619 l_api_version,
620 FND_API.g_false, -- p_init_msg_list - use same message queue as the calling API
621 FND_API.g_false, -- p_commit - commiting will cause problem with rollback in the calling API
622 p_validation_level,
623 l_return_status,
624 x_msg_count,
625 x_msg_data,
626 l_obj_type,
627 l_obj_id,
628 l_setup_id
629 );
630
631 IF l_return_status = FND_API.g_ret_sts_error THEN
632 RAISE FND_API.g_exc_error;
633 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
634 RAISE FND_API.g_exc_unexpected_error;
635 END IF;
636 */
637 l_access_rec.act_access_to_object_id := l_obj_id;
638 l_access_rec.arc_act_access_to_object := l_obj_type;
639 l_access_rec.user_or_role_id := l_deliv_rec.owner_user_id;
640 l_access_rec.arc_user_or_role_type := 'USER';
641 l_access_rec.owner_flag := 'Y';
642
643 /*
644 --- hp bug fix
645 IF (AMS_ACCESS_PVT.CHECK_ADMIN_ACCESS(l_deliv_rec.owner_user_id)) THEN
646 l_access_rec.admin_flag := 'Y';
647 ELSE
648 l_access_rec.admin_flag := 'N';
649 END IF;
650 */
651 l_access_rec.admin_flag := 'Y'; /* bug fix for Hp: */
652 l_access_rec.delete_flag := 'N';
653 AMS_Access_PVT.create_access (
654 p_api_version => 1.0,
655 p_init_msg_list => FND_API.G_FALSE,
656 p_commit => FND_API.G_FALSE,
657 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
658 x_return_status => x_return_status,
659 x_msg_count => x_msg_count,
660 x_msg_data => x_msg_data,
661 p_access_rec => l_access_rec,
662 x_access_id => l_access_rec.activity_access_id
663 );
664 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
665 RAISE FND_API.G_EXC_ERROR;
666 END IF;
667
668
669 /*added by musman this to create jtf_Amv_item_item if it is a electronic deliverable */
670 init_deliv_rec(x_deliv_rec_from_init);
671
672 x_deliv_rec_from_init.deliverable_id := l_deliv_rec.deliverable_id;
673 complete_deliv_rec(x_deliv_rec_from_init, x_deliv_rec);
674 IF (x_deliv_Rec.can_fulfill_electronic_flag = 'Y')
675 THEN
676 IF (AMS_DEBUG_HIGH_ON) THEN
677 AMS_Utility_PVT.debug_message('THE object version number is '||x_deliv_rec.object_version_number);
678 END IF;
679 x_deliv_Rec.object_version_number := 1;
680 update_deliverable
681 (p_api_version => 1.0,
682 p_commit => p_commit,
683 x_return_status => l_return_status,
684 x_msg_count => l_msg_count,
685 x_msg_data => l_msg_data,
686 p_deliv_rec => x_deliv_rec
687 );
688 END IF;
689
690 IF l_return_status <> FND_API.g_ret_sts_success THEN
691 IF (AMS_DEBUG_HIGH_ON) THEN
692
693 AMS_Utility_PVT.debug_message(l_full_name||': in the exception of call to update');
694 END IF;
695 x_msg_count := l_msg_count;
696 x_msg_data := l_msg_data;
697 RAISE FND_API.G_EXC_ERROR;
698 END IF;
699
700
701 -- attach seeded metrics
702 IF (AMS_DEBUG_HIGH_ON) THEN
703 AMS_Utility_PVT.debug_message('calling AMS_RefreshMetric_PVT.copy_seeded_metric');
704 END IF;
705
706 AMS_RefreshMetric_PVT.copy_seeded_metric(
707 p_api_version => 1.0,
708 x_return_status => l_return_status,
709 x_msg_count => x_msg_count,
710 x_msg_data => x_msg_data,
711 p_arc_act_metric_used_by =>'DELV',
712 p_act_metric_used_by_id => l_deliv_rec.deliverable_id,
713 p_act_metric_used_by_type => NULL
714 );
715
716 IF l_return_status = FND_API.g_ret_sts_error THEN
717 RAISE FND_API.g_exc_error;
718 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
719 RAISE FND_API.g_exc_unexpected_error;
720 END IF;
721
722
723 -- create task
724 IF (AMS_DEBUG_HIGH_ON) THEN
725 AMS_Utility_PVT.debug_message('calling AMS_TASK_PVT.Create_task');
726 END IF;
727
728 IF SYSDATE > l_deliv_rec.forecasted_complete_date THEN
729 l_task_planned_start_date := l_deliv_rec.forecasted_complete_date;
730 END IF;
731 l_trim_task_name := 'Prep. task for - '||substr(l_deliv_rec.deliverable_name,1,62);
732
733 l_source_object_name := substr(l_deliv_rec.deliverable_name,1,80);
734
735
736 AMS_TASK_PVT.Create_task
737 (
738 p_api_version => l_api_version,
739 p_init_msg_list => FND_API.g_false, -- use the same message queue as the calling API
740 p_commit => FND_API.g_false, -- committing in the called api will cause problems with rollback in the calling API
741 p_task_id => NULL,
742 p_task_name => l_trim_task_name, -- 'Prep. task for - '||l_deliv_rec.deliverable_name,
743 p_task_type_id => 15,
744 p_task_status_id => 14, -- in jtf_task_statuses_vl, 13 is Unassigned
745 p_task_priority_id => 3,
746 p_owner_id => l_deliv_rec.owner_user_id,
747 p_owner_type_code => 'RS_EMPLOYEE',
748 p_private_flag => 'N',
749 p_planned_start_date => l_task_planned_start_date,
750 p_planned_end_date => l_deliv_rec.forecasted_complete_date,
751 p_actual_start_date => NULL,
752 p_actual_end_date => l_deliv_rec.forecasted_complete_date,
753 p_source_object_type_code => 'AMS_DELV',
754 p_source_object_id => l_deliv_rec.deliverable_id,
755 p_source_object_name => l_source_object_name ,--l_deliv_rec.deliverable_name, -- because jtfObject seed data has changed. TO_CHAR(l_deliv_rec.deliverable_id),
756 x_return_status => l_return_status,
757 x_msg_count => x_msg_count,
758 x_msg_data => x_msg_data,
759 x_task_id => x_task_id
760 );
761
762 IF (AMS_DEBUG_HIGH_ON) THEN
763 AMS_UTILITY_PVT.debug_message('the return status from :'||l_return_status);
764 END IF;
765
766 IF l_return_status = FND_API.g_ret_sts_error THEN
767 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
768 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
769 END IF;
770
771 FND_MSG_PUB.count_and_get(
772 p_encoded => FND_API.g_false,
773 p_count => x_msg_count,
774 p_data => x_msg_data
775 );
776
777 RAISE FND_API.g_exc_error;
778
779 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
780 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
781 THEN
782 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
783 END IF;
784
785 FND_MSG_PUB.count_and_get(
786 p_encoded => FND_API.g_false,
787 p_count => x_msg_count,
788 p_data => x_msg_data
789 );
790
791 RAISE FND_API.g_exc_unexpected_error;
792 END IF;
793
794 -- create task assignment
795 IF (AMS_DEBUG_HIGH_ON) THEN
796 AMS_Utility_PVT.debug_message('calling AMS_TASK_PVT.Create_Task_Assignment');
797 END IF;
798
799 AMS_TASK_PVT.Create_Task_Assignment (
800 p_api_version => l_api_version,
801 p_init_msg_list => FND_API.g_false,
802 p_commit => FND_API.g_false,
803 p_task_id => x_task_id,
804 p_resource_type_code => 'RS_EMPLOYEE',
805 p_resource_id => l_deliv_rec.owner_user_id,
806 p_assignment_status_id => 1,
807 x_return_status => l_return_status,
808 x_msg_count => x_msg_count,
809 x_msg_data => x_msg_data,
810 x_task_assignment_id => x_task_assignment_id
811 );
812
813 IF l_return_status = FND_API.g_ret_sts_error THEN
814
815 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
816 THEN
817 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
818 END IF;
819
820 FND_MSG_PUB.count_and_get(
821 p_encoded => FND_API.g_false,
822 p_count => x_msg_count,
823 p_data => x_msg_data
824 );
825 RAISE FND_API.g_exc_error;
826 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
827
828 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
829 THEN
830 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
831 END IF;
832
833 FND_MSG_PUB.count_and_get(
834 p_encoded => FND_API.g_false,
835 p_count => x_msg_count,
836 p_data => x_msg_data
837 );
838 RAISE FND_API.g_exc_unexpected_error;
839 END IF;
840
841 -------------------- code added by abhola ---------------------
842 --------- ams_association pvt. create association ------------
843
844 if (l_deliv_rec.associate_flag = 'Y') then
845 if ( l_deliv_rec.deliverable_id IS NOT NULL ) then
846 l_association_rec.master_object_id := l_deliv_rec.master_object_id;
847 l_association_rec.master_object_type := l_deliv_rec.master_object_type;
848 l_association_rec.using_object_id := l_deliv_rec.deliverable_id;
849 l_association_rec.using_object_type := 'DELV';
850 l_association_rec.primary_flag := 'Y';
851 l_association_rec.usage_type :='USED_BY';
852
853 AMS_Associations_PVT.create_association
854 ( p_api_version => l_api_version,
855 p_init_msg_list => FND_API.g_false,
856 p_commit => FND_API.g_false,
857 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
858 x_return_status => l_return_status,
859 x_msg_count => x_msg_count,
860 x_msg_data => x_msg_data,
861 p_association_rec => l_association_rec,
862 x_object_association_id => l_object_association_id
863 );
864 IF l_return_status = FND_API.g_ret_sts_error THEN
865 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
866 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
867 END IF;
868 FND_MSG_PUB.count_and_get
869 (
870 p_encoded => FND_API.g_false,
871 p_count => x_msg_count,
872 p_data => x_msg_data
873 );
874 RAISE FND_API.g_exc_error;
875 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
876 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
877 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
878 END IF;
879 FND_MSG_PUB.count_and_get
880 (
881 p_encoded => FND_API.g_false,
882 p_count => x_msg_count,
883 p_data => x_msg_data
884 );
885 RAISE FND_API.g_exc_unexpected_error;
886 END IF;
887 end if;
888 end if;
889 ----------------- end code added by abhola --------------------
890 ------------------------- finish -------------------------------
891 x_deliv_id := l_deliv_rec.deliverable_id;
892
893 IF FND_API.to_boolean(p_commit) THEN
894 COMMIT;
895 END IF;
896
897 FND_MSG_PUB.count_and_get(
898 p_encoded => FND_API.g_false,
899 p_count => x_msg_count,
900 p_data => x_msg_data
901 );
902
903 IF (AMS_DEBUG_HIGH_ON) THEN
904 AMS_Utility_PVT.debug_message(l_full_name ||': end');
905 END IF;
906
907 EXCEPTION
908
909 WHEN FND_API.g_exc_error THEN
910 ROLLBACK TO create_deliverable;
911 x_return_status := FND_API.g_ret_sts_error;
912 FND_MSG_PUB.count_and_get(
913 p_encoded => FND_API.g_false,
914 p_count => x_msg_count,
915 p_data => x_msg_data
916 );
917
918 WHEN FND_API.g_exc_unexpected_error THEN
919 ROLLBACK TO create_deliverable;
920 x_return_status := FND_API.g_ret_sts_unexp_error ;
921 FND_MSG_PUB.count_and_get(
922 p_encoded => FND_API.g_false,
923 p_count => x_msg_count,
924 p_data => x_msg_data
925 );
926
927 WHEN OTHERS THEN
928 ROLLBACK TO create_deliverable;
929 x_return_status := FND_API.g_ret_sts_unexp_error ;
930
931 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
932 THEN
933 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
934 END IF;
935
936 FND_MSG_PUB.count_and_get(
937 p_encoded => FND_API.g_false,
938 p_count => x_msg_count,
939 p_data => x_msg_data
940 );
941
942 END create_deliverable;
943
944
945 --------------------------------------------------------------------
946 -- PROCEDURE
947 -- delete_deliverable (creative)
948 --
949 -- HISTORY
950 -- 10/09/99 khung Create.
951 --------------------------------------------------------------------
952
953 PROCEDURE delete_deliverable
954 (
955 p_api_version IN NUMBER,
956 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
957 p_commit IN VARCHAR2 := FND_API.g_false,
958
959 x_return_status OUT NOCOPY VARCHAR2,
960 x_msg_count OUT NOCOPY NUMBER,
961 x_msg_data OUT NOCOPY VARCHAR2,
962
963 p_deliv_id IN NUMBER,
964 p_object_version IN NUMBER
965 )
966 IS
967
968 l_api_version CONSTANT NUMBER := 1.0;
969 l_api_name CONSTANT VARCHAR2(30) := 'delete_deliverable';
970 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
971
972 BEGIN
973
974 --------------------- initialize -----------------------
975 SAVEPOINT delete_deliverable;
976
977 IF (AMS_DEBUG_HIGH_ON) THEN
978 AMS_Utility_PVT.debug_message(l_full_name||': start');
979 END IF;
980
981 IF FND_API.to_boolean(p_init_msg_list) THEN
982 FND_MSG_PUB.initialize;
983 END IF;
984
985 IF NOT FND_API.compatible_api_call(
986 l_api_version,
987 p_api_version,
988 l_api_name,
989 g_pkg_name
990 ) THEN
991 RAISE FND_API.g_exc_unexpected_error;
992 END IF;
993
994 x_return_status := FND_API.G_RET_STS_SUCCESS;
995
996 ------------------------ delete ------------------------
997 IF (AMS_DEBUG_HIGH_ON) THEN
998 AMS_Utility_PVT.debug_message(l_full_name ||': delete');
999 END IF;
1000
1001 DELETE FROM ams_deliverables_all_b
1002 WHERE deliverable_id = p_deliv_id
1003 AND object_version_number = p_object_version;
1004
1005 IF (SQL%NOTFOUND) THEN
1006 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1007 THEN
1008 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1009 FND_MSG_PUB.add;
1010 END IF;
1011 RAISE FND_API.g_exc_error;
1012 END IF;
1013
1014 DELETE FROM ams_deliverables_all_tl
1015 WHERE deliverable_id = p_deliv_id;
1016
1017 IF (SQL%NOTFOUND) THEN
1018 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1019 THEN
1020 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1021 FND_MSG_PUB.add;
1022 END IF;
1023 RAISE FND_API.g_exc_error;
1024 END IF;
1025
1026 -------------------- finish --------------------------
1027 IF FND_API.to_boolean(p_commit) THEN
1028 COMMIT;
1029 END IF;
1030
1031 FND_MSG_PUB.count_and_get(
1032 p_encoded => FND_API.g_false,
1033 p_count => x_msg_count,
1034 p_data => x_msg_data
1035 );
1036
1037 IF (AMS_DEBUG_HIGH_ON) THEN
1038 AMS_Utility_PVT.debug_message(l_full_name ||': end');
1039 END IF;
1040
1041 EXCEPTION
1042
1043 WHEN FND_API.g_exc_error THEN
1044 ROLLBACK TO delete_deliverable;
1045 x_return_status := FND_API.g_ret_sts_error;
1046 FND_MSG_PUB.count_and_get(
1047 p_encoded => FND_API.g_false,
1048 p_count => x_msg_count,
1049 p_data => x_msg_data
1050 );
1051
1052 WHEN FND_API.g_exc_unexpected_error THEN
1053 ROLLBACK TO delete_deliverable;
1054 x_return_status := FND_API.g_ret_sts_unexp_error ;
1055 FND_MSG_PUB.count_and_get(
1056 p_encoded => FND_API.g_false,
1057 p_count => x_msg_count,
1058 p_data => x_msg_data
1059 );
1060
1061 WHEN OTHERS THEN
1062 ROLLBACK TO delete_deliverable;
1063 x_return_status := FND_API.g_ret_sts_unexp_error ;
1064
1065 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1066 THEN
1067 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1068 END IF;
1069
1070 FND_MSG_PUB.count_and_get(
1071 p_encoded => FND_API.g_false,
1072 p_count => x_msg_count,
1073 p_data => x_msg_data
1074 );
1075
1076
1077 END delete_deliverable;
1078
1079 -------------------------------------------------------------------
1080 -- PROCEDURE
1081 -- lock_deliverable
1082 --
1083 -- HISTORY
1084 -- 10/09/99 khung Create.
1085 --------------------------------------------------------------------
1086
1087 PROCEDURE lock_deliverable
1088 (
1089 p_api_version IN NUMBER,
1090 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1091
1092 x_return_status OUT NOCOPY VARCHAR2,
1093 x_msg_count OUT NOCOPY NUMBER,
1094 x_msg_data OUT NOCOPY VARCHAR2,
1095
1096 p_deliv_id IN NUMBER,
1097 p_object_version IN NUMBER
1098 )
1099 IS
1100
1101 l_api_version CONSTANT NUMBER := 1.0;
1102 l_api_name CONSTANT VARCHAR2(30) := 'lock_deliverable';
1103 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1104
1105 l_deliv_id NUMBER;
1106
1107 CURSOR c_deliv_b IS
1108 SELECT deliverable_id
1109 FROM ams_deliverables_all_b
1110 WHERE deliverable_id = p_deliv_id
1111 AND object_version_number = p_object_version
1112 FOR UPDATE OF deliverable_id NOWAIT;
1113
1114 CURSOR c_deliv_tl IS
1115 SELECT deliverable_id
1116 FROM ams_deliverables_all_tl
1117 WHERE deliverable_id = p_deliv_id
1118 AND USERENV('LANG') IN (language, source_lang)
1119 FOR UPDATE OF deliverable_id NOWAIT;
1120
1121 BEGIN
1122
1123 -------------------- initialize ------------------------
1124 IF (AMS_DEBUG_HIGH_ON) THEN
1125
1126 AMS_Utility_PVT.debug_message(l_full_name||': start');
1127 END IF;
1128
1129 IF FND_API.to_boolean(p_init_msg_list) THEN
1130 FND_MSG_PUB.initialize;
1131 END IF;
1132
1133 IF NOT FND_API.compatible_api_call(
1134 l_api_version,
1135 p_api_version,
1136 l_api_name,
1137 g_pkg_name
1138 ) THEN
1139 RAISE FND_API.g_exc_unexpected_error;
1140 END IF;
1141
1142 x_return_status := FND_API.G_RET_STS_SUCCESS;
1143
1144 ------------------------ lock -------------------------
1145 IF (AMS_DEBUG_HIGH_ON) THEN
1146
1147 AMS_Utility_PVT.debug_message(l_full_name||': lock');
1148 END IF;
1149
1150 OPEN c_deliv_b;
1151 FETCH c_deliv_b INTO l_deliv_id;
1152 IF (c_deliv_b%NOTFOUND) THEN
1153 CLOSE c_deliv_b;
1154 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1155 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1156 FND_MSG_PUB.add;
1157 END IF;
1158 RAISE FND_API.g_exc_error;
1159 END IF;
1160 CLOSE c_deliv_b;
1161
1162 OPEN c_deliv_tl;
1163 CLOSE c_deliv_tl;
1164
1165 -------------------- finish --------------------------
1166 FND_MSG_PUB.count_and_get(
1167 p_encoded => FND_API.g_false,
1168 p_count => x_msg_count,
1169 p_data => x_msg_data
1170 );
1171
1172 EXCEPTION
1173
1174 WHEN AMS_Utility_PVT.resource_locked THEN
1175 x_return_status := FND_API.g_ret_sts_error;
1176 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1177 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
1178 FND_MSG_PUB.add;
1179 END IF;
1180
1181 FND_MSG_PUB.count_and_get(
1182 p_encoded => FND_API.g_false,
1183 p_count => x_msg_count,
1184 p_data => x_msg_data
1185 );
1186
1187 WHEN FND_API.g_exc_error THEN
1188 x_return_status := FND_API.g_ret_sts_error;
1189 FND_MSG_PUB.count_and_get(
1190 p_encoded => FND_API.g_false,
1191 p_count => x_msg_count,
1192 p_data => x_msg_data
1193 );
1194
1195 WHEN FND_API.g_exc_unexpected_error THEN
1196 x_return_status := FND_API.g_ret_sts_unexp_error ;
1197 FND_MSG_PUB.count_and_get(
1198 p_encoded => FND_API.g_false,
1199 p_count => x_msg_count,
1200 p_data => x_msg_data
1201 );
1202
1203 WHEN OTHERS THEN
1204 x_return_status := FND_API.g_ret_sts_unexp_error ;
1205 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1206 THEN
1207 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1208 END IF;
1209
1210 FND_MSG_PUB.count_and_get(
1211 p_encoded => FND_API.g_false,
1212 p_count => x_msg_count,
1213 p_data => x_msg_data
1214 );
1215
1216
1217 END lock_deliverable;
1218
1219
1220 ---------------------------------------------------------------------
1221 -- PROCEDURE
1222 -- update_deliverable (creative)
1223 --
1224 -- HISTORY
1225 -- 10/09/99 khung Create.
1226 -- 13-Sep-2000 choang Changed call for workflow approval from ams_wfcmpapr_pvt
1227 -- to ams_approval_pvt.
1228 ----------------------------------------------------------------------
1229
1230 PROCEDURE update_deliverable
1231 (
1232 p_api_version IN NUMBER,
1233 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1234 p_commit IN VARCHAR2 := FND_API.g_false,
1235 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1236
1237 x_return_status OUT NOCOPY VARCHAR2,
1238 x_msg_count OUT NOCOPY NUMBER,
1239 x_msg_data OUT NOCOPY VARCHAR2,
1240
1241 p_deliv_rec IN deliv_rec_type
1242 )
1243 IS
1244
1245 l_api_version CONSTANT NUMBER := 1.0;
1246 l_api_name CONSTANT VARCHAR2(30) := 'update_deliverable';
1247 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1248
1249 l_deliv_status_type CONSTANT VARCHAR2(30) := 'AMS_DELIV_STATUS';
1250 l_reject_status_code VARCHAR2(30) := 'DENIED_BA';
1251
1252 l_deliv_rec deliv_rec_type := p_deliv_rec;
1253 l_return_status VARCHAR2(1);
1254
1255 l_custom_setup_attr VARCHAR2(10);
1256 l_approval_type VARCHAR2(10);
1257
1258 l_approval_for VARCHAR2(10) := 'DELV';
1259 l_new_status_code VARCHAR2(30);
1260 l_reject_status_id NUMBER;
1261
1262
1263 l_pricelist_header_id NUMBER;
1264 l_modifier_list_rec qp_modifiers_pub.modifier_list_rec_type;
1265 l_price_hdr_name VARCHAR2(240);
1266 l_modifiers_tbl qp_modifiers_pub.modifiers_tbl_type;
1267
1268 -- Inventory
1269 l_ii_return_status VARCHAR2(1);
1270 inv_creation_error EXCEPTION;
1271
1272 l_item_rec INV_Item_GRP.Item_rec_type;
1273 x_item_rec INV_Item_GRP.Item_rec_type;
1274 x_error_tbl INV_Item_GRP.Error_tbl_type;
1275
1276 -- add the following profile option for bug 1350422
1277 -- added by khung@us 07/11/2000
1278 l_inv_profile VARCHAR2(1);
1279 l_qp_profile VARCHAR2(1);
1280
1281 -- JTF amv item
1282 jtf_inv_item_creation_error EXCEPTION;
1283 l_jtf_amv_item_rec JTF_AMV_ITEM_PUB.item_rec_type;
1284 x_jtf_amv_item_id NUMBER;
1285 l_jtf_delv_item_id NUMBER;
1286 l_jtf_att_count NUMBER;
1287 l_jtf_used_by VARCHAR2(30);
1288 l_jtf_used_by_id NUMBER;
1289 l_att_id NUMBER;
1290
1291 l_msg_count NUMBER;
1292 l_msg_data VARCHAR2(2000);
1293
1294 l_notes_from_requester VARCHAR2(2000);
1295 l_item_type VARCHAR2(30) := 'AMSAPRV';
1296 l_workflowprocess VARCHAR2(30) := 'AMS_APPROVAL';
1297
1298 CURSOR c_new_status_code(status_id IN NUMBER) IS
1299 SELECT system_status_code
1300 FROM ams_user_statuses_vl
1301 WHERE user_status_id = status_id;
1302
1303 CURSOR c_user_status_id (p_status_code IN VARCHAR2) IS
1304 SELECT user_status_id
1305 FROM ams_user_statuses_vl
1306 WHERE system_status_type = l_deliv_status_type
1307 AND system_status_code = p_status_code
1308 AND default_flag = 'Y';
1309
1310 CURSOR c_orig_stat_rec(p_deliverable_id IN NUMBER) IS
1311 SELECT user_status_id, status_code
1312 FROM ams_deliverables_vl
1313 WHERE deliverable_id = p_deliverable_id;
1314
1315 l_orig_stat_rec c_orig_stat_rec%ROWTYPE;
1316 /*
1317 -- SQLID : 11753349 Fix by musman
1318 -- commented out cursor which are not being used
1319 CURSOR c_inv_item_id(p_inv_item_num IN VARCHAR2) IS
1320 SELECT distinct(inventory_item_id)
1321 FROM mtl_system_items_b_kfv
1322 WHERE segment1 = p_inv_item_num;
1323
1324 CURSOR c_pricelist_header_id IS
1325 SELECT distinct(pricelist_header_id)
1326 FROM ams_deliverables_vl;
1327 */
1328 CURSOR c_jtf_item_id (p_deliv_id IN NUMBER) IS
1329 SELECT jtf_amv_item_id
1330 FROM ams_deliverables_vl
1331 WHERE deliverable_id = p_deliv_id;
1332
1333 CURSOR c_jtf_att_count (p_deliv_id IN NUMBER) IS
1334 SELECT count(1)
1335 FROM jtf_amv_attachments_v
1336 WHERE attachment_used_by_id = p_deliv_id
1337 AND attachment_used_by = 'AMS_DELV';
1338
1339 CURSOR c_jtf_file_rec (p_deliv_id IN NUMBER) IS
1340 SELECT file_id, file_name, attachment_type, version
1341 FROM jtf_amv_attachments_v
1342 WHERE attachment_used_by_id = p_deliv_id
1343 AND attachment_used_by = 'AMS_DELV'
1344 AND creation_date = (
1345 SELECT max(creation_date) FROM jtf_amv_attachments_v
1346 WHERE attachment_used_by_id = p_deliv_id
1347 AND attachment_used_by = 'AMS_DELV');
1348
1349 l_jtf_file_rec c_jtf_file_rec%ROWTYPE;
1350
1351
1352 CURSOR get_attachments_rec (p_deliv_id IN NUMBER, p_itm_id IN NUMBER) IS
1353 SELECT file_id, file_name, attachment_type, version
1354 FROM jtf_amv_attachments_v
1355 WHERE attachment_used_by_id = p_deliv_id
1356 AND attachment_used_by = 'AMS_DELV'
1357 AND file_id NOT IN
1358 ( select file_id
1359 from jtf_amv_attachments_v
1360 where attachment_used_by_id = p_itm_id
1361 and attachment_used_by = 'ITEM' );
1362
1363 l_attachments_rec get_attachments_rec%ROWTYPE;
1364
1365
1366 CURSOR c_get_ob_ver_num(delv_id IN NUMBER) IS
1367 SELECT object_version_number
1368 from ams_deliverables_all_b where deliverable_id = delv_id;
1369
1370 l_dummy NUMBER;
1371 l_pending_budget_stat VARCHAR2(50);
1372 l_pending_budget_stat_id NUMBER;
1373 l_new_budget_stat_id NUMBER;
1374
1375 l_deliverable_calendar VARCHAR2(15);
1376
1377 CURSOR get_owner_id(deliv_id IN NUMBER)
1378 IS
1379 SELECT owner_user_id
1380 FROM ams_deliverables_all_b
1381 WHERE deliverable_id = deliv_id;
1382
1383 l_owner_user_id NUMBER;
1384
1385 CURSOR check_budget(deliv_id IN NUMBER)
1386 IS
1387 SELECT DISTINCT 'Y'
1388 FROM ams_act_budgets
1389 WHERE arc_act_budget_used_by='DELV'
1390 AND act_budget_used_by_id = deliv_id;
1391
1392 l_budget_lines_exist VARCHAR2(1) := 'N';
1393
1394 CURSOR get_detl(deliv_id IN NUMBER)
1395 IS
1396 SELECT kit_flag, inventory_flag,non_inv_quantity_on_hand
1397 FROM ams_deliverables_all_b
1398 WHERE deliverable_id = deliv_id;
1399
1400 l_kit_flag VARCHAR2(1) ;
1401 l_inventory_flag VARCHAR2(1);
1402 l_quantity NUMBER;
1403
1404 --01/24/02 added for access check bug #2764852 mukumar start
1405
1406 l_user_id NUMBER;
1407 l_res_id NUMBER;
1408
1409 CURSOR get_res_id(l_user_id IN NUMBER) IS
1410 SELECT resource_id
1411 FROM ams_jtf_rs_emp_v
1412 WHERE user_id = l_user_id;
1413 --01/24/02 added for access check bug #2764852 mukumar end
1414
1415
1416 BEGIN
1417
1418 -------------------- initialize -------------------------
1419 SAVEPOINT update_deliverable;
1420
1421 IF (AMS_DEBUG_HIGH_ON) THEN
1422 AMS_Utility_PVT.debug_message(l_full_name||': start');
1423 END IF;
1424
1425 IF FND_API.to_boolean(p_init_msg_list) THEN
1426 FND_MSG_PUB.initialize;
1427 END IF;
1428
1429 IF NOT FND_API.compatible_api_call(
1430 l_api_version,
1431 p_api_version,
1432 l_api_name,
1433 g_pkg_name
1434 ) THEN
1435 RAISE FND_API.g_exc_unexpected_error;
1436 END IF;
1437
1438 x_return_status := FND_API.G_RET_STS_SUCCESS;
1439
1440 ----------------------- validate ----------------------
1441 --01/24/02 added for access check bug #2764852 mukumar start
1442
1443 l_user_id := FND_GLOBAL.User_Id;
1444
1445 IF (AMS_DEBUG_HIGH_ON) THEN
1446 AMS_Utility_PVT.debug_message(' CHECK ACCESS l_user_id is ' ||l_user_id );
1447 END IF;
1448 if l_user_id IS NOT NULL then
1449 open get_res_id(l_user_id);
1450 fetch get_res_id into l_res_id;
1451 close get_res_id;
1452 end if;
1453 if AMS_ACCESS_PVT.check_update_access(l_deliv_rec.deliverable_id, 'DELV', l_res_id, 'USER') = 'N' then
1454 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1455 FND_MESSAGE.set_name('AMS', 'AMS_EVO_NO_UPDATE_ACCESS');-- reusing the message
1456 FND_MSG_PUB.add;
1457 END IF;
1458 RAISE FND_API.g_exc_error;
1459 end if;
1460 --01/24/02 added for access check bug #2764852 mukumar end
1461
1462 IF (AMS_DEBUG_HIGH_ON) THEN
1463 AMS_Utility_PVT.debug_message(l_full_name ||': validate');
1464 AMS_Utility_PVT.debug_message('l_deliv_rec.transaction_currency_code:'||l_deliv_rec.transaction_currency_code);
1465 AMS_Utility_PVT.debug_message('l_deliv_rec.currency_code:'||l_deliv_rec.currency_code);
1466 END IF;
1467
1468
1469 IF l_deliv_rec.transaction_currency_code = FND_API.G_MISS_CHAR
1470 OR l_deliv_rec.transaction_currency_code IS NULL
1471 THEN
1472 l_deliv_rec.transaction_currency_code := l_deliv_rec.currency_code;
1473 END IF;
1474
1475 -- replace g_miss_char/num/date with current column values
1476 complete_deliv_rec(p_deliv_rec, l_deliv_rec);
1477
1478 -- checking the values of periods and deliverable_calendar
1479 IF (AMS_DEBUG_HIGH_ON) THEN
1480 AMS_Utility_PVT.debug_message(l_full_name ||':checking the period name');
1481 END IF;
1482
1483
1484 IF ( (l_deliv_rec.start_period_name IS NOT NULL)
1485 OR (l_deliv_rec.end_period_name IS NOT NULL)) THEN
1486
1487 check_periods(
1488 p_deliv_rec => l_deliv_rec
1489 ,x_deliverable_calendar => l_deliverable_calendar
1490 ,x_return_status => l_return_Status
1491 );
1492
1493 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1494 RAISE FND_API.g_exc_unexpected_error;
1495 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1496 RAISE FND_API.g_exc_error;
1497 ELSIF l_return_status = FND_API.g_ret_sts_success THEN
1498 l_deliv_Rec.deliverable_calendar := l_deliverable_calendar;
1499 END IF;
1500 ELSIF ((l_deliv_rec.start_period_name IS NULL)
1501 AND (l_deliv_rec.end_period_name IS NULL)) THEN
1502 l_deliv_Rec.deliverable_calendar := null;
1503
1504 END IF;
1505
1506
1507 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1508 -- calling the public apis validate_delieverable
1509 -- validate_deliverable
1510 ams_deliverable_pub.validate_deliverable
1511 (
1512 p_api_version_number => l_api_version,
1513 p_init_msg_list => FND_API.g_false,
1514 p_validation_level => p_validation_level,
1515 p_validation_mode => JTF_PLSQL_API.g_update,
1516 x_return_status => l_return_status,
1517 x_msg_count => x_msg_count,
1518 x_msg_data => x_msg_data,
1519 p_deliv_rec => l_deliv_rec
1520 );
1521
1522
1523 /*
1524 check_deliv_items(
1525 p_deliv_rec => l_deliv_rec,
1526 p_validation_mode => JTF_PLSQL_API.g_update,
1527 x_return_status => l_return_status
1528 );
1529
1530 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1531 RAISE FND_API.g_exc_unexpected_error;
1532 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1533 RAISE FND_API.g_exc_error;
1534 END IF;
1535 END IF;
1536
1537 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
1538
1539 IF (AMS_DEBUG_HIGH_ON) THEN
1540 AMS_Utility_PVT.debug_message('check_deliv_record');
1541 END IF;
1542
1543 check_deliv_record(
1544 p_deliv_rec => l_deliv_rec,
1545 p_complete_rec => l_deliv_rec,
1546 x_return_status => l_return_status
1547 );
1548 */
1549
1550 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1551 RAISE FND_API.g_exc_unexpected_error;
1552 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1553 RAISE FND_API.g_exc_error;
1554 END IF;
1555 END IF;
1556
1557 OPEN c_new_status_code(l_deliv_rec.user_status_id);
1558 FETCH c_new_status_code INTO l_new_status_code;
1559 CLOSE c_new_status_code;
1560
1561 l_deliv_rec.status_code := l_new_status_code;
1562
1563 OPEN c_orig_stat_rec(l_deliv_rec.deliverable_id);
1564 FETCH c_orig_stat_rec INTO l_orig_stat_rec;
1565 CLOSE c_orig_stat_rec;
1566
1567 -- for cancellation change musman 10/04/00
1568 IF l_deliv_rec.status_code IN ('CANCELLED', 'ARCHIVED') THEN
1569 Deliverable_cancellation(l_deliv_rec, l_return_status);
1570 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1571 RAISE FND_API.g_exc_unexpected_error;
1572 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1573 RAISE FND_API.g_exc_error;
1574 END IF;
1575 END IF;
1576
1577 -- Bug 1350422 modified by khung 07/11/2000
1578 -- get values of profile options to call inv/qp profile options.
1579 -- By default we will call the inv/qp api. We will not call the api
1580 -- only if user explicitly set this profile option to 'N'
1581
1582 l_inv_profile := FND_PROFILE.Value('AMS_INV_API_CALLOUT');--'Y';
1583 l_qp_profile := FND_PROFILE.Value('AMS_QP_PRICING_CALLOUT');--'Y';
1584
1585 -- changed the profile option defaults to 'N'
1586 IF l_inv_profile IS NULL THEN
1587 l_inv_profile := 'N';
1588 END IF;
1589
1590 IF l_qp_profile IS NULL THEN
1591 l_qp_profile := 'N';
1592 END IF;
1593
1594
1595 IF (l_deliv_rec.replaced_by_deliverable_id IS NOT NULL) THEN
1596 l_deliv_rec.status_code := 'SUPERCEDED';
1597 OPEN c_user_status_id (l_deliv_rec.status_code);
1598 FETCH c_user_status_id INTO l_deliv_rec.user_status_id;
1599 CLOSE c_user_status_id;
1600 END IF;
1601
1602 IF ((l_new_status_code = 'CANCELLED') OR
1603 (l_new_status_code = 'ARCHIVED') OR
1604 (l_new_status_Code = 'EXPIRED'))THEN
1605 l_deliv_rec.active_flag := 'N';
1606 END IF;
1607
1608 OPEN get_detl(l_deliv_rec.deliverable_id);
1609 FETCH get_detl INTO l_kit_flag,l_inventory_flag, l_quantity;
1610 CLOSE get_detl;
1611
1612 -- if user has wrongly entered data on quantity and selected inventory option
1613 IF (--l_inventory_flag = 'Y' AND
1614 l_deliv_rec.non_inv_quantity_on_hand > 0
1615 AND l_deliv_rec.inventory_flag = 'Y')
1616 THEN
1617 l_deliv_rec.non_inv_quantity_on_hand := l_quantity;
1618 END IF;
1619
1620 IF l_inv_profile = 'Y'
1621 AND l_deliv_rec.inventory_flag = 'Y'
1622 AND l_deliv_rec.inventory_item_id IS NULL
1623 AND (l_kit_Flag = 'N'
1624 OR (l_kit_flag ='Y' AND l_qp_profile ='Y'))
1625 THEN
1626 creat_inv_item (
1627 p_deliv_rec => l_deliv_rec,
1628 x_inv_id => l_deliv_rec.inventory_item_id,
1629 x_org_id => l_deliv_rec.inventory_item_org_id,
1630 x_return_status => x_return_status,
1631 x_msg_count => x_msg_count,
1632 x_msg_data => x_msg_data
1633 );
1634
1635 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1636 RAISE FND_API.g_exc_unexpected_error;
1637 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
1638 RAISE FND_API.g_exc_error;
1639 END IF;
1640
1641 -- this is needed if the user passes the inv_id,org_id from the public api
1642 -- validating whether the inv item has reqd attributes set.
1643 ELSIF l_inv_profile = 'Y'
1644 AND l_deliv_rec.inventory_flag = 'Y'
1645 AND l_inventory_flag ='N' --old inventory flag
1646 AND nvl(l_deliv_rec.inventory_item_id,-4) <> -4
1647 AND nvl(l_deliv_rec.inventory_item_org_id,-4) <> -4
1648 AND (l_kit_Flag = 'N'
1649 OR (l_kit_flag ='Y' AND l_qp_profile ='Y'))
1650 THEN
1651
1652 check_inv_item(
1653 p_deliv_rec => l_deliv_rec
1654 ,x_return_status => x_return_status);
1655
1656 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1657 RAISE FND_API.g_exc_unexpected_error;
1658 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
1659 RAISE FND_API.g_exc_error;
1660 END IF;
1661
1662 END IF;
1663 -- End Inventory creation
1664 -- Begin Price List API call-out
1665 -- modify by khung 07/11/2000
1666
1667 -- JTF Amv Item API call-out
1668
1669 IF (l_deliv_rec.can_fulfill_electronic_flag = 'Y') THEN
1670 l_jtf_att_count := 0;
1671
1672 OPEN c_jtf_att_count(l_deliv_rec.deliverable_id);
1673 FETCH c_jtf_att_count INTO l_jtf_att_count;
1674 CLOSE c_jtf_att_count;
1675
1676 OPEN c_jtf_item_id(l_deliv_rec.deliverable_id);
1677 FETCH c_jtf_item_id INTO l_jtf_delv_item_id;
1678 CLOSE c_jtf_item_id;
1679
1680 /***********************************************************************************
1681 -- commented by ABHOLA
1682 THIS line is commented as we want to create JTF_AMV item even if there are no attachments
1683 IF ((l_jtf_delv_item_id IS NULL) AND (l_jtf_att_count > 0) ) THEN
1684 **************************************************************************************/
1685 IF (l_jtf_delv_item_id IS NULL) THEN
1686 l_jtf_amv_item_rec.item_name :='DELV-'||substr(l_deliv_rec.deliverable_name,1,235);--l_deliv_rec.deliverable_name;
1687 l_jtf_amv_item_rec.application_id := 520;
1688 l_jtf_amv_item_rec.item_type := 'FILE_ITEM';
1689 l_jtf_amv_item_rec.content_type_id := 10;
1690 l_jtf_amv_item_rec.owner_id := FND_GLOBAL.user_id;
1691
1692 JTF_AMV_ITEM_PUB.Create_Item
1693 (
1694 p_api_version => 1.0,
1695 p_init_msg_list => FND_API.g_true,
1696 p_commit => FND_API.g_false,
1697 x_return_status => l_return_status,
1698 x_msg_count => l_msg_count,
1699 x_msg_data => l_msg_data,
1700 p_item_rec => l_jtf_amv_item_rec,
1701 x_item_id => x_jtf_amv_item_id
1702 );
1703 x_return_status := l_return_status;
1704
1705 IF l_return_status <> FND_API.g_ret_sts_success THEN
1706 RAISE jtf_inv_item_creation_error;
1707 ELSE
1708 l_deliv_rec.jtf_amv_item_id := x_jtf_amv_item_id;
1709 l_jtf_delv_item_id := x_jtf_amv_item_id;
1710 END IF;
1711 END IF;
1712
1713 -- abhola added ' AND (l_jtf_att_count > 0) ' in the IF clause
1714 -----------------------------------------------------------
1715 IF (AMS_DEBUG_HIGH_ON) THEN
1716
1717 ams_utility_pvt.debug_message (' delv item id '||l_jtf_delv_item_id||' attacth '||l_jtf_att_count);
1718 END IF;
1719
1720 IF ( (l_jtf_delv_item_id IS NOT NULL) AND (l_jtf_att_count > 0) ) THEN
1721 -- create jtf_amv_attachment for ITEM
1722 /*******************
1723 OPEN c_jtf_file_rec(l_deliv_rec.deliverable_id);
1724 FETCH c_jtf_file_rec INTO l_jtf_file_rec;
1725 CLOSE c_jtf_file_rec;
1726 *******************/
1727 OPEN get_attachments_rec(l_deliv_rec.deliverable_id,l_jtf_delv_item_id);
1728 LOOP
1729 FETCH get_attachments_rec INTO l_attachments_rec;
1730 EXIT WHEN get_attachments_rec%NOTFOUND;
1731
1732 l_jtf_used_by := 'ITEM';
1733 l_jtf_used_by_id := l_deliv_rec.jtf_amv_item_id;
1734
1735 create_jtf_attachment
1736 (
1737 p_used_by => l_jtf_used_by,
1738 p_used_by_id => l_jtf_used_by_id,
1739 p_file_id => l_attachments_rec.file_id, -- l_jtf_file_rec.file_id,
1740 p_file_name => l_attachments_rec.file_name, -- l_jtf_file_rec.file_name,
1741 p_att_type => l_jtf_file_rec.attachment_type, -- l_jtf_file_rec.attachment_type,
1742 p_file_ver => l_jtf_file_rec.version, -- l_jtf_file_rec.version,
1743 x_return_status => l_return_status,
1744 x_msg_count => l_msg_count,
1745 x_msg_data => l_msg_data,
1746 x_att_id => l_att_id
1747 );
1748 END LOOP ;
1749 CLOSE get_attachments_rec;
1750 END IF;
1751 END IF; -- end JTF Amv Item API call-out
1752 ------------------------------------------------------------------------
1753 -- This piece of code added by ABHOLA
1754 -- if status code is AVAILABLE and Budget Approval is REQ - BAPL attr present
1755 -- in custom setup then change status code to SUBMITTED_BA
1756 -- set these var to original values
1757 -- so that in case the belwo IF condiion does not meets
1758 -- the status should not be updated by wrong values
1759 --
1760 -- the status order rule has been changed i.e ,from NEW the status can go to
1761 -- BUDGET_APPR or CANCELLED and not directly to AVAILABLE. so doing the following changes
1762 -- by musman 08/08/01
1763 /*
1764 -- by musman 07/29/02
1765 -- since the call to approvals is done
1766 -- in the api AMS_DeliverableRules_PVT.update_delv_status
1767
1768 -- l_pending_budget_stat_id := l_deliv_rec.user_status_id;
1769 -- l_new_budget_stat_id := l_deliv_rec.user_status_id; -- to send in to approvalpvt
1770 -- l_pending_budget_stat := l_deliv_rec.status_code;
1771
1772 -- IF ((l_orig_stat_rec.status_code = 'NEW'
1773 -- OR l_orig_stat_rec.status_code ='DENIED_BA')
1774 -- AND (l_new_status_code = 'BUDGET_APPR')) THEN
1775 --
1776 -- l_reject_status_code := 'DENIED_BA';
1777 -- l_custom_setup_attr := 'BAPL';
1778 -- l_approval_type := 'BUDGET';
1779 -- l_pending_budget_stat := 'SUBMITTED_BA';
1780 -- l_workflowprocess := 'AMS_APPROVAL';
1781
1782 -- ELSIF ((l_orig_stat_rec.status_code = 'BUDGET_APPR'
1783 -- OR l_orig_stat_rec.status_code = 'DENIED_TA')
1784 -- AND (l_new_status_code = 'AVAILABLE')) THEN
1785 --
1786 -- l_reject_status_code := 'DENIED_TA';
1787 -- l_custom_setup_attr := 'CAPL';
1788 -- l_approval_type := 'CONCEPT';
1789 -- l_pending_budget_stat := 'SUBMITTED_TA';
1790 -- l_workflowprocess := 'AMS_CONCEPT_APPROVAL';
1791 -- END If;
1792
1793
1794 -- IF ( (l_orig_stat_rec.status_code = 'NEW'
1795 -- OR l_orig_stat_rec.status_code ='DENIED_BA')
1796 -- AND (l_new_status_code = 'BUDGET_APPR'))
1797 -- OR ( (l_orig_stat_rec.status_code = 'BUDGET_APPR'
1798 -- OR l_orig_stat_rec.status_code ='DENIED_TA')
1799 -- AND (l_new_status_code = 'AVAILABLE'))
1800 -- THEN
1801 --
1802 -- OPEN c_user_status_id (l_reject_status_code);
1803 FETCH c_user_status_id INTO l_reject_status_id;
1804 -- CLOSE c_user_status_id;
1805 --
1806 -- IF (AMS_DEBUG_HIGH_ON) THEN AMS_UTILITY_PVT.debug_message(' the approval reqd flag gives : '||Approval_required_flag( l_deliv_rec.setup_id, l_custom_setup_attr)); END IF;
1807 -- IF Approval_required_flag( l_deliv_rec.setup_id, l_custom_setup_attr) = FND_API.g_true
1808 -- THEN
1809 -- -- choang - 13-sep-2000
1810 -- -- Change of approval process.
1811 -- -- flip the status to pending budget approval
1812 -- IF (AMS_DEBUG_HIGH_ON) THEN ams_utility_pvt.debug_message (' Inside the if changing the status :'||l_dummy); END IF;
1813 -- -- l_pending_budget_stat := 'SUBMITTED_BA';
1814 --
1815 -- OPEN c_user_status_id (l_pending_budget_stat);
1816 -- FETCH c_user_status_id INTO l_pending_budget_stat_id;
1817 -- CLOSE c_user_status_id;
1818
1819 -- IF (AMS_DEBUG_HIGH_ON) THEN ams_utility_pvt.debug_message (' l_pending_budget_stat_id : '||l_pending_budget_stat_id); END IF;
1820 -- ELSIF Approval_required_flag( l_deliv_rec.setup_id, l_custom_setup_attr) = FND_API.g_false
1821 -- THEN
1822 -- l_pending_budget_stat_id :=l_deliv_rec.user_status_id;
1823 -- l_pending_budget_stat := l_deliv_rec.status_code;
1824 -- END IF; -- if approval needed / BAPL exists
1825
1826 -- END IF;
1827 -----------------------------------------
1828 -- End of code added by ABHOLA
1829 -----------------------------------------
1830
1831 */
1832
1833 OPEN get_owner_id(l_deliv_rec.deliverable_id);
1834 FETCH get_owner_id INTO l_owner_user_id;
1835 CLOSE get_owner_id;
1836 /*
1837 -- IF l_deliv_rec.status_code = 'AVAILABLE'
1838 -- AND l_deliv_rec.private_flag = 'Y'
1839 -- THEN
1840 -- l_deliv_rec.private_flag := 'N';
1841 -- END IF;
1842 */
1843
1844 IF (AMS_DEBUG_HIGH_ON) THEN
1845 ams_utility_pvt.debug_message (l_full_name || ' - status_code: ' || l_deliv_rec.status_code);
1846 END IF;
1847
1848 UPDATE ams_deliverables_all_b
1849 SET last_update_date = SYSDATE,
1850 last_updated_by = FND_GLOBAL.user_id,
1851 last_update_login = FND_GLOBAL.conc_login_id,
1852 object_version_number = l_deliv_rec.object_version_number + 1,
1853 language_code = l_deliv_rec.language_code,
1854 version = l_deliv_rec.version,
1855 application_id = l_deliv_rec.application_id,
1856 -- user_status_id = l_pending_budget_stat_id,
1857 -- status_code = l_pending_budget_stat,
1858 -- status_date = l_deliv_rec.status_date,
1859 active_flag = l_deliv_rec.active_flag,
1860 private_flag = l_deliv_rec.private_flag,
1861 owner_user_id = l_deliv_rec.owner_user_id,
1862 fund_source_id = l_deliv_rec.fund_source_id,
1863 fund_source_type = l_deliv_rec.fund_source_type,
1864 category_type_id = l_deliv_rec.category_type_id,
1865 category_sub_type_id = l_deliv_rec.category_sub_type_id,
1866 kit_flag = l_deliv_rec.kit_flag,
1867 can_fulfill_electronic_flag = l_deliv_rec.can_fulfill_electronic_flag,
1868 can_fulfill_physical_flag = l_deliv_rec.can_fulfill_physical_flag,
1869 jtf_amv_item_id = l_deliv_rec.jtf_amv_item_id,
1870 inventory_flag = l_deliv_rec.inventory_flag,
1871 transaction_currency_code = l_deliv_rec.transaction_currency_code, /* because defaulted to l_deliv_rec.currency_code if transaction currency is null*/
1872 functional_currency_code = l_deliv_rec.functional_currency_code,
1873 budget_amount_tc = l_deliv_rec.budget_amount_tc,
1874 budget_amount_fc = l_deliv_rec.budget_amount_fc,
1875 actual_avail_from_date = l_deliv_rec.actual_avail_from_date,
1876 actual_avail_to_date = l_deliv_rec.actual_avail_to_date,
1877 forecasted_complete_date = l_deliv_rec.forecasted_complete_date,
1878 actual_complete_date = l_deliv_rec.actual_complete_date,
1879 replaced_by_deliverable_id = l_deliv_rec.replaced_by_deliverable_id,
1880 inventory_item_id = l_deliv_rec.inventory_item_id,
1881 inventory_item_org_id = l_deliv_rec.inventory_item_org_id,
1882 pricelist_header_id = l_deliv_rec.pricelist_header_id,
1883 pricelist_line_id = l_deliv_rec.pricelist_line_id,
1884 non_inv_ctrl_code = l_deliv_rec.non_inv_ctrl_code,
1885 non_inv_quantity_on_hand = l_deliv_rec.non_inv_quantity_on_hand,
1886 non_inv_quantity_on_order = l_deliv_rec.non_inv_quantity_on_order,
1887 non_inv_quantity_on_reserve = l_deliv_rec.non_inv_quantity_on_reserve,
1888 chargeback_amount = l_deliv_rec.chargeback_amount,
1889 chargeback_amount_curr_code = l_deliv_rec.chargeback_amount_curr_code,
1890 deliverable_code = l_deliv_rec.deliverable_code,
1891 deliverable_pick_flag = l_deliv_rec.deliverable_pick_flag,
1892 currency_code = l_deliv_rec.currency_code,
1893 forecasted_cost = l_deliv_rec.forecasted_cost,
1894 actual_cost = l_deliv_rec.actual_cost,
1895 forecasted_responses = l_deliv_rec.forecasted_responses,
1896 actual_responses = l_deliv_rec.actual_responses,
1897 country = l_deliv_rec.country,
1898 attribute_category = l_deliv_rec.attribute_category,
1899 attribute1 = l_deliv_rec.attribute1,
1900 attribute2 = l_deliv_rec.attribute2,
1901 attribute3 = l_deliv_rec.attribute3,
1902 attribute4 = l_deliv_rec.attribute4,
1903 attribute5 = l_deliv_rec.attribute5,
1904 attribute6 = l_deliv_rec.attribute6,
1905 attribute7 = l_deliv_rec.attribute7,
1906 attribute8 = l_deliv_rec.attribute8,
1907 attribute9 = l_deliv_rec.attribute9,
1908 attribute10 = l_deliv_rec.attribute10,
1909 attribute11 = l_deliv_rec.attribute11,
1910 attribute12 = l_deliv_rec.attribute12,
1911 attribute13 = l_deliv_rec.attribute13,
1912 attribute14 = l_deliv_rec.attribute14,
1913 attribute15 = l_deliv_rec.attribute15,
1914 chargeback_uom = l_deliv_rec.chargeback_uom,
1915 deliverable_calendar = l_deliv_rec.deliverable_calendar,
1916 start_period_name = l_deliv_rec.start_period_name,
1917 end_period_name = l_deliv_rec.end_period_name,
1918 email_content_type = l_deliv_rec.email_content_type
1919 WHERE deliverable_id = l_deliv_rec.deliverable_id
1920 AND object_version_number = l_deliv_rec.object_version_number;
1921 IF (AMS_DEBUG_HIGH_ON) THEN
1922
1923 ams_utility_pvt.debug_message ('values id '||l_deliv_rec.deliverable_id||' ver '||l_deliv_rec.object_version_number||l_pending_budget_stat_id||l_pending_budget_stat);
1924 END IF;
1925
1926
1927 IF (SQL%NOTFOUND) THEN
1928 IF (AMS_DEBUG_HIGH_ON) THEN
1929
1930 ams_utility_pvt.debug_message (' SQL NOT FOUND ');
1931 END IF;
1932 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1933 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1934 FND_MSG_PUB.add;
1935 END IF;
1936 RAISE FND_API.g_exc_error;
1937 END IF;
1938 IF (AMS_DEBUG_HIGH_ON) THEN
1939
1940 ams_utility_pvt.debug_message (l_full_name ||' end of update 1 ');
1941 END IF;
1942 UPDATE ams_deliverables_all_tl
1943 SET deliverable_name = l_deliv_rec.deliverable_name,
1944 description = l_deliv_rec.description,
1945 last_update_date = SYSDATE,
1946 last_updated_by = FND_GLOBAL.user_id,
1947 last_update_login = FND_GLOBAL.conc_login_id,
1948 source_lang = USERENV('LANG')
1949 WHERE deliverable_id = l_deliv_rec.deliverable_id
1950 AND USERENV('LANG') IN (language, source_lang);
1951
1952 IF (SQL%NOTFOUND) THEN
1953 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1954 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1955 FND_MSG_PUB.add;
1956 END IF;
1957 RAISE FND_API.g_exc_error;
1958 END IF;
1959
1960 IF (l_Deliv_rec.owner_user_id <> FND_API.g_miss_NUM
1961 AND l_owner_user_id <> l_deliv_Rec.owner_user_id )
1962 THEN
1963 AMS_Access_PVT.update_object_owner
1964 ( p_api_version => 1.0
1965 ,p_init_msg_list => FND_API.G_FALSE
1966 ,p_commit => FND_API.G_FALSE
1967 ,p_validation_level => p_validation_level
1968 ,x_return_status => x_return_status
1969 ,x_msg_count => x_msg_count
1970 ,x_msg_data => x_msg_data
1971 ,p_object_type => 'DELV'
1972 ,p_object_id => l_deliv_Rec.deliverable_id
1973 ,p_resource_id => l_deliv_rec.owner_user_id
1974 ,p_old_resource_id => l_owner_user_id
1975 );
1976 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1977 RAISE FND_API.G_EXC_ERROR;
1978 END IF;
1979 END IF;
1980
1981 -------------------------------------
1982 -- This piece of code added by ABHOLA
1983 -------------------------------------
1984 /*
1985 -- by musman 07/29/02
1986 -- since the call to approvals is done
1987 -- in the api AMS_DeliverableRules_PVT.update_delv_status
1988 -- IF ((l_orig_stat_rec.status_code = 'NEW'
1989 -- OR l_orig_stat_rec.status_code = 'DENIED_BA')
1990 -- AND (l_new_status_code = 'BUDGET_APPR'))
1991 -- OR ((l_orig_stat_rec.status_code = 'BUDGET_APPR'
1992 -- OR l_orig_stat_rec.status_code = 'DENIED_TA')
1993 -- AND (l_new_status_code = 'AVAILABLE'))
1994 -- THEN
1995 --
1996 -- OPEN c_user_status_id (l_reject_status_code);
1997 -- FETCH c_user_status_id INTO l_reject_status_id;
1998 -- CLOSE c_user_status_id;
1999 --
2000 -- ****************************************
2001 -- -- by ABHOLA on Oct 31 2001
2002 -- -- commented out this code to check budget lines
2003 -- -- this code check will happen in WKFLOW api
2004 -- --
2005 -- -- musman checking whether budget lines exist.
2006 --
2007 -- OPEN check_budget(l_deliv_rec.deliverable_id);
2008 -- FETCH check_budget INTO l_budget_lines_exist;
2009 -- CLOSE check_budget;
2010 --
2011 --
2012 -- IF Approval_required_flag( l_deliv_rec.setup_id, 'BAPL') = FND_API.g_true
2013 -- AND l_budget_lines_exist = 'N' THEN
2014 --
2015 -- IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2016 -- FND_MESSAGE.set_name('AMS', 'AMS_EVE_NO_BGT_SRC');
2017 -- FND_MSG_PUB.add;
2018 -- END IF;
2019 -- RAISE FND_API.g_exc_error;
2020 -- END IF;
2021 -- *****************************************
2022 --
2023 -- IF (AMS_DEBUG_HIGH_ON) THEN AMS_UTILITY_PVT.debug_message('after update the approval reqd flag gives : '||Approval_required_flag( l_deliv_rec.setup_id, 'BAPL')||' and fnd_api.g_true :'|| FND_API.g_true); END IF;
2024 -- IF Approval_required_flag( l_deliv_rec.setup_id, l_custom_setup_attr) = FND_API.g_true
2025 -- THEN
2026 --
2027 -- choang - 13-sep-2000
2028 -- -- Change of approval process.
2029 --
2030 -- OPEN c_get_ob_ver_num(l_deliv_rec.deliverable_id);
2031 -- FETCH c_get_ob_ver_num INTO l_dummy;
2032 -- CLOSE c_get_ob_ver_num;
2033 -- IF (AMS_DEBUG_HIGH_ON) THEN ams_utility_pvt.debug_message (' OBJ VER NUM 2 '||l_dummy); END IF;
2034 --
2035 -- AMS_Approval_PVT.StartProcess (
2036 -- p_activity_type => l_approval_for,
2037 -- p_activity_id => l_deliv_rec.deliverable_id,
2038 -- p_approval_type => l_approval_type,
2039 -- p_object_version_number => l_dummy,
2040 -- p_orig_stat_id => l_orig_stat_rec.user_status_id,
2041 -- p_new_stat_id => l_new_budget_stat_id, --- l_pending_budget_stat_id,
2042 -- p_reject_stat_id => l_reject_status_id,
2043 -- p_requester_userid => AMS_Utility_PVT.get_resource_id (FND_GLOBAL.user_id),
2044 -- p_notes_from_requester => l_notes_from_requester,
2045 -- p_workflowprocess => l_workflowprocess,
2046 -- p_item_type => l_item_type
2047 -- );
2048 -- IF (AMS_DEBUG_HIGH_ON) THEN ams_utility_pvt.debug_message (' after the start process '); END IF;
2049 -- END IF; -- if approval needed / l_custom_setup_attr (BAPL/CAPL) exists
2050 -- END IF;
2051 */
2052 -----------------------------------------------------
2053 -- end of code added by ABHOLA
2054 -----------------------------------------------------
2055 AMS_DeliverableRules_PVT.update_delv_status
2056 (
2057 p_deliverable_id => l_deliv_rec.deliverable_id
2058 ,p_user_status_id => l_deliv_rec.user_status_id
2059 );
2060
2061 -- IF x_return_status <> FND_API.g_ret_sts_success
2062 -- THEN
2063 -- RAISE FND_API.g_exc_error;
2064 -- END IF;
2065
2066 IF (AMS_DEBUG_HIGH_ON) THEN
2067
2068
2069
2070 ams_utility_pvt.debug_message (l_full_name ||' end of update 2 ');
2071
2072 END IF;
2073 -------------------- finish --------------------------
2074 IF FND_API.to_boolean(p_commit) THEN
2075 COMMIT;
2076 END IF;
2077
2078 FND_MSG_PUB.count_and_get(
2079 p_encoded => FND_API.g_false,
2080 p_count => x_msg_count,
2081 p_data => x_msg_data
2082 );
2083
2084 IF (AMS_DEBUG_HIGH_ON) THEN
2085
2086
2087
2088 AMS_Utility_PVT.debug_message(l_full_name ||': end');
2089
2090 END IF;
2091
2092 EXCEPTION
2093
2094 WHEN FND_API.g_exc_error THEN
2095 ROLLBACK TO update_deliverable;
2096 x_return_status := FND_API.g_ret_sts_error;
2097 FND_MSG_PUB.count_and_get(
2098 p_encoded => FND_API.g_false,
2099 p_count => x_msg_count,
2100 p_data => x_msg_data
2101 );
2102
2103 WHEN FND_API.g_exc_unexpected_error THEN
2104 ROLLBACK TO update_deliverable;
2105 x_return_status := FND_API.g_ret_sts_unexp_error ;
2106 FND_MSG_PUB.count_and_get(
2107 p_encoded => FND_API.g_false,
2108 p_count => x_msg_count,
2109 p_data => x_msg_data
2110 );
2111
2112 WHEN inv_creation_error THEN
2113 /*-modify on 08/07/2000 khung
2114 --FND_MSG_PUB.count_and_get(
2115 p_encoded => FND_API.g_false,
2116 -- p_count => x_msg_count,
2117 -- p_data => x_msg_data
2118 -- );*/
2119 ROLLBACK TO update_deliverable;
2120 FOR i IN 1 .. x_error_tbl.count LOOP
2121 FND_MSG_PUB.count_and_get(
2122 p_encoded => FND_API.g_false,
2123 p_count => x_msg_count,
2124 p_data => x_error_tbl(i).message_text
2125 );
2126 IF (AMS_DEBUG_HIGH_ON) THEN
2127
2128 AMS_Utility_PVT.debug_message(l_full_name ||'the error text is '||x_error_tbl(i).message_text);
2129 END IF;
2130 END LOOP;
2131
2132 WHEN jtf_inv_item_creation_error THEN
2133 ROLLBACK TO update_deliverable;
2134 FND_MSG_PUB.count_and_get(
2135 p_encoded => FND_API.g_false,
2136 p_count => x_msg_count,
2137 p_data => x_msg_data
2138 );
2139 WHEN OTHERS THEN
2140 ROLLBACK TO update_deliverable;
2141 x_return_status := FND_API.g_ret_sts_unexp_error ;
2142 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2143 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2144 END IF;
2145 FND_MSG_PUB.count_and_get(
2146 p_encoded => FND_API.g_false,
2147 p_count => x_msg_count,
2148 p_data => x_msg_data
2149 );
2150 END update_deliverable;
2151
2152 ---------------------------------------------------------------------
2153 -- PROCEDURE
2154 -- validate_deliverable
2155 --
2156 -- HISTORY
2157 -- 10/09/99 khung Create.
2158 ----------------------------------------------------------------------
2159
2160 PROCEDURE validate_deliverable
2161 (
2162 p_api_version IN NUMBER,
2163 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2164 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2165 p_validation_mode IN VARCHAR2,
2166 x_return_status OUT NOCOPY VARCHAR2,
2167 x_msg_count OUT NOCOPY NUMBER,
2168 x_msg_data OUT NOCOPY VARCHAR2,
2169 p_deliv_rec IN deliv_rec_type
2170 )
2171 IS
2172
2173 l_api_version CONSTANT NUMBER := 1.0;
2174 l_api_name CONSTANT VARCHAR2(30) := 'validate_deliverable';
2175 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2176 l_return_status VARCHAR2(1);
2177
2178 BEGIN
2179
2180 ----------------------- initialize --------------------
2181 IF (AMS_DEBUG_HIGH_ON) THEN
2182
2183 AMS_Utility_PVT.debug_message(l_full_name||': start');
2184 END IF;
2185
2186 IF FND_API.to_boolean(p_init_msg_list) THEN
2187 FND_MSG_PUB.initialize;
2188 END IF;
2189
2190 IF NOT FND_API.compatible_api_call(
2191 l_api_version,
2192 p_api_version,
2193 l_api_name,
2194 g_pkg_name
2195 ) THEN
2196 RAISE FND_API.g_exc_unexpected_error;
2197 END IF;
2198
2199 x_return_status := FND_API.g_ret_sts_success;
2200
2201 ---------------------- validate ------------------------
2202 IF (AMS_DEBUG_HIGH_ON) THEN
2203
2204 AMS_Utility_PVT.debug_message(l_full_name||': check items');
2205 END IF;
2206
2207 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
2208 check_deliv_items(
2209 p_deliv_rec => p_deliv_rec,
2210 p_validation_mode => p_validation_mode, --JTF_PLSQL_API.g_create,
2211 x_return_status => l_return_status
2212 );
2213
2214 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2215 RAISE FND_API.g_exc_unexpected_error;
2216 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
2217 RAISE FND_API.g_exc_error;
2218 END IF;
2219 END IF;
2220
2221 IF (AMS_DEBUG_HIGH_ON) THEN
2222 AMS_Utility_PVT.debug_message(l_full_name||': check record');
2223 END IF;
2224
2225 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record
2226 AND p_validation_mode = JTF_PLSQL_API.g_create
2227 THEN
2228
2229 check_deliv_record(
2230 p_deliv_rec => p_deliv_rec,
2231 p_complete_rec => NULL,
2232 x_return_status => l_return_status
2233 );
2234 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2235 RAISE FND_API.g_exc_unexpected_error;
2236 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
2237 RAISE FND_API.g_exc_error;
2238 END IF;
2239
2240 ELSIF p_validation_level >= JTF_PLSQL_API.g_valid_level_record
2241 AND p_validation_mode = JTF_PLSQL_API.g_update
2242 THEN
2243
2244 check_deliv_record(
2245 p_deliv_rec => p_deliv_rec,
2246 p_complete_rec => p_deliv_rec,
2247 x_return_status => l_return_status
2248 );
2249 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2250 RAISE FND_API.g_exc_unexpected_error;
2251 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
2252 RAISE FND_API.g_exc_error;
2253 END IF;
2254
2255 END IF;
2256
2257 -------------------- finish --------------------------
2258 FND_MSG_PUB.count_and_get(
2259 p_encoded => FND_API.g_false,
2260 p_count => x_msg_count,
2261 p_data => x_msg_data
2262 );
2263
2264 IF (AMS_DEBUG_HIGH_ON) THEN
2265
2266
2267
2268 AMS_Utility_PVT.debug_message(l_full_name ||': end');
2269
2270 END IF;
2271
2272 EXCEPTION
2273
2274 WHEN FND_API.g_exc_error THEN
2275 x_return_status := FND_API.g_ret_sts_error;
2276 FND_MSG_PUB.count_and_get(
2277 p_encoded => FND_API.g_false,
2278 p_count => x_msg_count,
2279 p_data => x_msg_data
2280 );
2281
2282 WHEN FND_API.g_exc_unexpected_error THEN
2283 x_return_status := FND_API.g_ret_sts_unexp_error ;
2284 FND_MSG_PUB.count_and_get(
2285 p_encoded => FND_API.g_false,
2286 p_count => x_msg_count,
2287 p_data => x_msg_data
2288 );
2289
2290 WHEN OTHERS THEN
2291 x_return_status := FND_API.g_ret_sts_unexp_error;
2292 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
2293 THEN
2294 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2295 END IF;
2296
2297 FND_MSG_PUB.count_and_get(
2298 p_encoded => FND_API.g_false,
2299 p_count => x_msg_count,
2300 p_data => x_msg_data
2301 );
2302
2303
2304 END validate_deliverable;
2305
2306 ---------------------------------------------------------------------
2307 -- PROCEDURE
2308 -- check_deliv_req_items
2309 --
2310 -- HISTORY
2311 -- 10/11/99 khung Create.
2312 ---------------------------------------------------------------------
2313 PROCEDURE check_deliv_req_items(
2314 p_deliv_rec IN deliv_rec_type,
2315 x_return_status OUT NOCOPY VARCHAR2
2316 )
2317 IS
2318 BEGIN
2319
2320 x_return_status := FND_API.g_ret_sts_success;
2321
2322 -------------------- put required items here ---------------------
2323
2324 IF p_deliv_rec.language_code IS NULL THEN
2325 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2326 THEN
2327 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_NO_LANGUAGE_CODE');
2328 FND_MSG_PUB.add;
2329 END IF;
2330
2331 x_return_status := FND_API.g_ret_sts_error;
2332 RETURN;
2333 END IF;
2334
2335 IF p_deliv_rec.deliverable_name IS NULL THEN
2336 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2337 THEN
2338 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_NO_DELIVERABLE_NAME');
2339 FND_MSG_PUB.add;
2340 END IF;
2341
2342 x_return_status := FND_API.g_ret_sts_error;
2343 RETURN;
2344 END IF;
2345
2346 IF p_deliv_rec.actual_avail_from_date IS NULL THEN
2347 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2348 THEN
2349 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_NO_FROM_DATE');
2350 FND_MSG_PUB.add;
2351 END IF;
2352
2353 x_return_status := FND_API.g_ret_sts_error;
2354 RETURN;
2355 END IF;
2356
2357 IF p_deliv_rec.actual_avail_to_date IS NULL THEN
2358 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2359 THEN
2360 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_NO_TO_DATE');
2361 FND_MSG_PUB.add;
2362 END IF;
2363
2364 x_return_status := FND_API.g_ret_sts_error;
2365 RETURN;
2366 END IF;
2367
2368 IF p_deliv_rec.forecasted_complete_date IS NULL THEN
2369 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2370 THEN
2371 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_NO_FOREC_DATE');
2372 FND_MSG_PUB.add;
2373 END IF;
2374
2375 x_return_status := FND_API.g_ret_sts_error;
2376 RETURN;
2377 END IF;
2378
2379 IF p_deliv_rec.owner_user_id IS NULL THEN
2380 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2381 THEN
2382 FND_MESSAGE.set_name('AMS', 'AMS_EVT_REG_NO_OWNER_ID');
2383 FND_MSG_PUB.add;
2384 END IF;
2385
2386 x_return_status := FND_API.g_ret_sts_error;
2387 RETURN;
2388 END IF;
2389
2390 IF p_deliv_rec.setup_id IS NULL THEN
2391 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2392 THEN
2393 FND_MESSAGE.set_name('AMS', 'AMS_FUND_NO_CUSTOM_SETUP');
2394 FND_MSG_PUB.add;
2395 END IF;
2396
2397 x_return_status := FND_API.g_ret_sts_error;
2398 RETURN;
2399 END IF;
2400
2401 IF p_deliv_rec.country_id IS NULL THEN
2402 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2403 THEN
2404 FND_MESSAGE.set_name('AMS', 'AMS_DILG_BAD_CITY');
2405 FND_MSG_PUB.add;
2406 END IF;
2407
2408 x_return_status := FND_API.g_ret_sts_error;
2409 RETURN;
2410 END IF;
2411
2412 IF p_deliv_rec.category_Type_id IS NULL THEN
2413 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2414 THEN
2415 FND_MESSAGE.set_name('AMS', 'AMS_ACT_CAT_NO_CATEGORY_ID');
2416 FND_MSG_PUB.add;
2417 END IF;
2418
2419 x_return_status := FND_API.g_ret_sts_error;
2420 RETURN;
2421 END IF;
2422
2423 IF p_deliv_rec.category_sub_Type_id IS NULL THEN
2424 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2425 THEN
2426 FND_MESSAGE.set_name('AMS', 'AMS_SUB_CATEGORY_IS_MISSING');
2427 FND_MSG_PUB.add;
2428 END IF;
2429
2430 x_return_status := FND_API.g_ret_sts_error;
2431 RETURN;
2432 END IF;
2433
2434 IF p_deliv_rec.version IS NULL THEN
2435 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2436 THEN
2437 FND_MESSAGE.set_name('AMS', 'AMS_DELV_VERSION_MISSING');
2438 FND_MSG_PUB.add;
2439 END IF;
2440
2441 x_return_status := FND_API.g_ret_sts_error;
2442 RETURN;
2443 END IF;
2444
2445 IF p_deliv_rec.transaction_currency_code IS NULL THEN
2446 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2447 THEN
2448 FND_MESSAGE.set_name('AMS', 'AMS_CAMP_BUDGET_NO_CURRENCY');
2449 FND_MSG_PUB.add;
2450 END IF;
2451
2452 x_return_status := FND_API.g_ret_sts_error;
2453 RETURN;
2454 END IF;
2455
2456 END check_deliv_req_items;
2457
2458 ---------------------------------------------------------------------
2459 -- PROCEDURE
2460 -- check_deliv_uk_items
2461 --
2462 -- HISTORY
2463 -- 10/11/99 khung Create.
2464 ---------------------------------------------------------------------
2465
2466 PROCEDURE check_deliv_uk_items(
2467 p_deliv_rec IN deliv_rec_type,
2468 p_validation_mode IN VARCHAR2,
2469 x_return_status OUT NOCOPY VARCHAR2
2470 )
2471 IS
2472 l_where_clause VARCHAR2(2000);
2473 l_valid_flag VARCHAR2(1);
2474
2475 cursor c_check_uniq ( del_nm IN VARCHAR2, del_ver IN VARCHAR2 )
2476 IS
2477 SELECT 'N'
2478 FROM ams_deliverables_vl
2479 WHERE deliverable_name = del_nm
2480 AND version = del_ver;
2481
2482
2483 cursor c_check_uniq_u ( del_nm IN VARCHAR2, del_ver IN VARCHAR2 , del_id IN NUMBER )
2484 IS
2485 SELECT 'N'
2486 FROM ams_deliverables_vl
2487 WHERE deliverable_name = del_nm
2488 AND version = del_ver
2489 AND deliverable_id <> del_id;
2490
2491
2492 l_uniq_flag VARCHAR2(1);
2493 l_uniq_flag_u VARCHAR2(1);
2494
2495
2496 BEGIN
2497
2498 x_return_status := FND_API.g_ret_sts_success;
2499
2500 -- For create_deliverable, when deliverable_id is passed in, we need to
2501 -- check if this deliverable_id is unique.
2502 IF p_validation_mode = JTF_PLSQL_API.g_create
2503 AND p_deliv_rec.deliverable_id IS NOT NULL
2504 THEN
2505 IF AMS_Utility_PVT.check_uniqueness(
2506 'ams_deliverables_vl',
2507 'deliverable_id = ' || p_deliv_rec.deliverable_id
2508 ) = FND_API.g_false
2509 THEN
2510 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2511 THEN
2512 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_DUPLICATE_ID');
2513 FND_MSG_PUB.add;
2514 END IF;
2515 x_return_status := FND_API.g_ret_sts_error;
2516 RETURN;
2517 END IF;
2518 END IF;
2519
2520 IF (AMS_DEBUG_HIGH_ON) THEN
2521 AMS_Utility_PVT.debug_message(': start of check create '||p_deliv_rec.deliverable_name||' -'||p_deliv_rec.version);
2522 END IF;
2523 -- Check if deliverable_name/version is unique.
2524 IF p_validation_mode = JTF_PLSQL_API.g_create
2525 THEN
2526 l_uniq_flag := 'Y';
2527 OPEN c_check_uniq(p_deliv_rec.deliverable_name, p_deliv_rec.version);
2528 FETCH c_check_uniq INTO l_uniq_flag;
2529 IF (AMS_DEBUG_HIGH_ON) THEN
2530 AMS_Utility_PVT.debug_message(' Flag '||l_uniq_flag);
2531 END IF;
2532 CLOSE c_check_uniq;
2533
2534 IF (l_uniq_flag = 'N')
2535 THEN
2536 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2537 THEN
2538 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_DUP_NAME_VERSION');
2539 FND_MSG_PUB.add;
2540 END IF;
2541 x_return_status := FND_API.g_ret_sts_error;
2542 RETURN;
2543 END IF;
2544 END IF;
2545
2546 IF (AMS_DEBUG_HIGH_ON) THEN
2547 AMS_Utility_PVT.debug_message(': start of check update ');
2548 END IF;
2549
2550 -- check name and ver uniquiness in update mode
2551
2552 IF p_validation_mode = JTF_PLSQL_API.g_update
2553 THEN
2554 l_uniq_flag_u := 'Y';
2555 OPEN c_check_uniq_u(p_deliv_rec.deliverable_name,p_deliv_rec.version,p_deliv_rec.deliverable_id);
2556 FETCH c_check_uniq_u INTO l_uniq_flag_u;
2557 CLOSE c_check_uniq_u;
2558 IF (AMS_DEBUG_HIGH_ON) THEN
2559 AMS_Utility_PVT.debug_message('val of l_uniq_flag_u'||l_uniq_flag_u);
2560 END IF;
2561
2562 IF (l_uniq_flag_u = 'N')
2563 THEN
2564 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2565 THEN
2566 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_DUP_NAME_VERSION');
2567 FND_MSG_PUB.add;
2568 END IF;
2569 x_return_status := FND_API.g_ret_sts_error;
2570 RETURN;
2571 END IF;
2572 END IF;
2573
2574 --IF p_validation_mode = JTF_PLSQL_API.g_create THEN
2575 -- l_valid_flag := AMS_Utility_PVT.check_uniqueness(
2576 -- 'ams_deliverabless_vl',
2577 -- 'deliverable_name = ''' || p_deliv_rec.deliverable_name ||
2578 -- '''AND version = ' || p_deliv_rec.version
2579 --IF p_validation_mode = JTF_PLSQL_API.g_create THEN
2580 -- l_valid_flag := AMS_Utility_PVT.check_uniqueness(
2581 -- 'ams_deliverabless_vl',
2582 -- 'deliverable_name = ''' || p_deliv_rec.deliverable_name ||
2583 -- '''AND version = ' || p_deliv_rec.version
2584 -- );
2585 --ELSE
2586 -- l_valid_flag := AMS_Utility_PVT.check_uniqueness(
2587 -- 'ams_deliverables_vl',
2588 -- 'deliverable_name = ''' || p_deliv_rec.deliverable_name ||
2589 -- ''' AND deliverable_id <> ' || p_deliv_rec.deliverable_id
2590 -- );
2591 --END IF;
2592 --IF l_valid_flag = FND_API.g_false THEN
2593 -- IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2594 -- THEN
2595 -- FND_MESSAGE.set_name('AMS', 'AMS_DELIV_DUPLICATE_NAME');
2596 -- FND_MSG_PUB.add;
2597 -- END IF;
2598 -- x_return_status := FND_API.g_ret_sts_error;
2599 -- RETURN;
2600 --END IF;
2601
2602 -- check other unique items
2603
2604 END check_deliv_uk_items;
2605
2606 ---------------------------------------------------------------------
2607 -- PROCEDURE
2608 -- check_deliv_fk_items
2609 --
2610 -- HISTORY
2611 -- 10/11/99 khung Create.
2612 ---------------------------------------------------------------------
2613 PROCEDURE check_deliv_fk_items(
2614 p_deliv_rec IN deliv_rec_type,
2615 x_return_status OUT NOCOPY VARCHAR2
2616 )
2617 IS
2618 BEGIN
2619
2620 x_return_status := FND_API.g_ret_sts_success;
2621 -- check other fk items
2622
2623 --------------------- owner_user_id ------------------------
2624 IF p_deliv_rec.owner_user_id <> FND_API.g_miss_num
2625 THEN
2626 IF AMS_Utility_PVT.check_fk_exists(
2627 -- 'ams_jtf_rs_emp_v',
2628 'jtf_rs_resource_extns',
2629 'resource_id',
2630 p_deliv_rec.owner_user_id
2631 ) = FND_API.g_false
2632 THEN
2633 AMS_Utility_PVT.Error_Message('AMS_CAMP_BAD_OWNER_USER_ID');
2634 x_return_status := FND_API.g_ret_sts_error;
2635 RETURN;
2636 END IF;
2637 END IF;
2638
2639 --------------------- application_id ------------------------
2640 IF p_deliv_rec.application_id <> FND_API.g_miss_num
2641 THEN
2642 IF AMS_Utility_PVT.check_fk_exists(
2643 'fnd_application',
2644 'application_id',
2645 p_deliv_rec.application_id
2646 ) = FND_API.g_false
2647 THEN
2648 AMS_Utility_PVT.Error_Message('AMS_API_NO_APPLICATION_ID');
2649 x_return_status := FND_API.g_ret_sts_error;
2650 RETURN;
2651 END IF;
2652 END IF;
2653
2654 --------------------- category_type_id ------------------------
2655 IF p_deliv_rec.category_type_id <> FND_API.g_miss_num
2656 AND p_deliv_rec.category_type_id IS NOT NULL
2657 THEN
2658 IF AMS_Utility_PVT.check_fk_exists(
2659 'ams_categories_b',
2660 'category_id',
2661 p_deliv_rec.category_type_id
2662 ) = FND_API.g_false
2663 THEN
2664 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2665 THEN
2666 FND_MESSAGE.set_name('AMS', 'AMS_FUND_BAD_CATEGORY_ID');
2667 FND_MSG_PUB.add;
2668 END IF;
2669 x_return_status := FND_API.g_ret_sts_error;
2670 RETURN;
2671 END IF;
2672 END IF;
2673
2674 --------------------- category_sub_type_id ------------------------
2675 IF p_deliv_rec.category_sub_type_id <> FND_API.g_miss_num
2676 AND p_deliv_rec.category_sub_type_id IS NOT NULL
2677 THEN
2678 IF AMS_Utility_PVT.check_fk_exists(
2679 'ams_categories_b',
2680 'category_id',
2681 p_deliv_rec.category_sub_type_id
2682 ) = FND_API.g_false
2683 THEN
2684 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2685 THEN
2686 FND_MESSAGE.set_name('AMS', 'AMS_SUB_CATEGORY_IS_MISSING');
2687 FND_MSG_PUB.add;
2688 END IF;
2689 x_return_status := FND_API.g_ret_sts_error;
2690 RETURN;
2691 END IF;
2692 END IF;
2693
2694 --------------------- custom_setup_id ----------------------------
2695 IF p_deliv_rec.setup_id <> FND_API.g_miss_num
2696 AND p_deliv_rec.setup_id IS NOT NULL
2697 THEN
2698 IF AMS_Utility_PVT.check_fk_exists(
2699 'ams_custom_setups_b',
2700 'custom_setup_id',
2701 p_deliv_rec.setup_id
2702 ) = FND_API.g_false
2703 THEN
2704 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2705 THEN
2706 FND_MESSAGE.set_name('AMS', 'AMS_CAMP_BAD_CUSTOM_SETUP');
2707 FND_MSG_PUB.add;
2708 END IF;
2709 x_return_status := FND_API.g_ret_sts_error;
2710 RETURN;
2711 END IF;
2712
2713 END IF;
2714
2715 --------------------- country_id ----------------------------
2716 IF p_deliv_rec.country_id <> FND_API.g_miss_num
2717 AND p_deliv_rec.country_id IS NOT NULL
2718 THEN
2719 IF AMS_Utility_PVT.check_fk_exists(
2720 'jtf_loc_hierarchies_b',
2721 'location_hierarchy_id',
2722 p_deliv_rec.country_id,
2723 AMS_Utility_PVT.g_number,
2724 NULL
2725 ) = FND_API.g_false
2726 THEN
2727 AMS_Utility_PVT.Error_Message('AMS_CSCH_BAD_COUNTRY');
2728 x_return_status := FND_API.g_ret_sts_error;
2729 RETURN;
2730 END IF;
2731 END IF;
2732
2733 --------------------- Currency_code ----------------------------
2734 IF p_deliv_rec.currency_code <> FND_API.g_miss_char
2735 AND p_deliv_rec.currency_code IS NOT NULL
2736 THEN
2737 IF AMS_Utility_PVT.check_fk_exists(
2738 'fnd_currencies',
2739 'currency_code',
2740 p_deliv_rec.currency_code,
2741 AMS_Utility_PVT.g_varchar2,
2742 NULL
2743 ) = FND_API.g_false
2744 THEN
2745 AMS_Utility_PVT.Error_Message('AMS_TRIG_INVALID_CURR');
2746 x_return_status := FND_API.g_ret_sts_error;
2747 RETURN;
2748 END IF;
2749 END IF;
2750
2751 ---------------------Transaction_Currency_code ----------------------------
2752 IF p_deliv_rec.transaction_currency_code <> FND_API.g_miss_char
2753 AND p_deliv_rec.transaction_currency_code IS NOT NULL
2754 THEN
2755 IF AMS_Utility_PVT.check_fk_exists(
2756 'fnd_currencies',
2757 'currency_code',
2758 p_deliv_rec.transaction_currency_code,
2759 AMS_Utility_PVT.g_varchar2,
2760 NULL
2761 ) = FND_API.g_false
2762 THEN
2763 AMS_Utility_PVT.Error_Message('AMS_TRIG_INVALID_CURR');
2764 x_return_status := FND_API.g_ret_sts_error;
2765 RETURN;
2766 END IF;
2767 END IF;
2768
2769 --------------------- Language ----------------------------
2770 IF p_deliv_rec.language_code <> FND_API.g_miss_char
2771 AND p_deliv_rec.language_code IS NOT NULL
2772 THEN
2773 IF AMS_Utility_PVT.check_fk_exists(
2774 'fnd_languages',
2775 'language_code',
2776 p_deliv_rec.language_code,
2777 AMS_Utility_PVT.g_varchar2,
2778 NULL
2779 ) = FND_API.g_false
2780 THEN
2781 AMS_Utility_PVT.Error_Message('AMS_CAMP_BAD_LANG');
2782 x_return_status := FND_API.g_ret_sts_error;
2783 RETURN;
2784 END IF;
2785 END IF;
2786
2787 END check_deliv_fk_items;
2788
2789 ---------------------------------------------------------------------
2790 -- PROCEDURE
2791 -- check_deliv_lookup_items
2792 --
2793 -- HISTORY
2794 -- 10/11/99 khung Create.
2795 ---------------------------------------------------------------------
2796 PROCEDURE check_deliv_lookup_items(
2797 p_deliv_rec IN deliv_rec_type,
2798 x_return_status OUT NOCOPY VARCHAR2
2799 )
2800 IS
2801 BEGIN
2802
2803 x_return_status := FND_API.g_ret_sts_success;
2804
2805 ----------------------- status_code ------------------------
2806 IF p_deliv_rec.status_code <> FND_API.g_miss_char THEN
2807 IF AMS_Utility_PVT.check_lookup_exists(
2808 p_lookup_type => 'AMS_DELIV_STATUS',
2809 p_lookup_code => p_deliv_rec.status_code
2810 ) = FND_API.g_false
2811 THEN
2812 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2813 THEN
2814 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_BAD_STATUS_CODE');
2815 FND_MSG_PUB.add;
2816 END IF;
2817
2818 x_return_status := FND_API.g_ret_sts_error;
2819 RETURN;
2820 END IF;
2821 END IF;
2822
2823 -- check other lookup codes
2824
2825 END check_deliv_lookup_items;
2826
2827
2828 ---------------------------------------------------------------------
2829 -- PROCEDURE
2830 -- check_deliv_flag_items
2831 --
2832 -- HISTORY
2833 -- 10/11/99 khung Create.
2834 ---------------------------------------------------------------------
2835 PROCEDURE check_deliv_flag_items(
2836 p_deliv_rec IN deliv_rec_type,
2837 p_validation_mode IN VARCHAR2,
2838 x_return_status OUT NOCOPY VARCHAR2
2839 )
2840 IS
2841
2842 CURSOR get_flags(delivId IN NUMBER)
2843 IS
2844 SELECT can_fulfill_electronic_flag
2845 ,can_fulfill_physical_flag
2846 ,inventory_flag
2847 ,non_inv_quantity_on_hand
2848 ,kit_flag
2849 ,status_code
2850 FROM ams_deliverables_all_b
2851 WHERE deliverable_id = delivId;
2852
2853
2854 l_api_name VARCHAR2(30) := 'check_deliv_flag_items';
2855 l_old_electronic_flag VARCHAR2(1);
2856 l_old_inventory_flag VARCHAR2(1);
2857 l_old_physical_flag VARCHAR2(1);
2858 l_quantity_on_hand NUMBER;
2859 l_old_kit_flag VARCHAR2(1);
2860 l_status_code VARCHAR2(30);
2861
2862
2863 l_check_deliv_has_kit VARCHAR2(1) := 'N';
2864
2865 -- Check if this deliverable is a kit
2866 CURSOR is_deliv_kit(l_deliverable_id IN NUMBER )
2867 IS select count(*)
2868 from ams_deliv_kit_items
2869 where deliverable_kit_id = l_deliverable_id;
2870
2871 -- Check if this deliverable is part of a kit
2872 CURSOR is_deliv_kit_part(l_deliverable_id IN NUMBER )
2873 IS select count(*)
2874 from ams_deliv_kit_items
2875 where deliverable_kit_part_id = l_deliverable_id;
2876
2877 l_kits_check NUMBER;
2878 l_qp_profile varchar2(1) := FND_PROFILE.Value('AMS_QP_PRICING_CALLOUT');
2879
2880 BEGIN
2881
2882 IF l_qp_profile IS NULL THEN
2883 l_qp_profile := 'N';
2884 END IF;
2885
2886 x_return_status := FND_API.g_ret_sts_success;
2887
2888 -- validation added for the public apis
2889
2890 IF p_validation_mode = JTF_PLSQL_API.g_create
2891 THEN
2892 IF NVL(p_deliv_rec.inventory_flag,'N') = 'Y'
2893 THEN
2894 AMS_UTILITY_PVT.error_message('AMS_DELV_NO_INV_CRE');
2895 --- Inventory option can be enabled only after the deliverable is created.
2896 x_return_status := FND_API.g_ret_sts_error;
2897 RETURN;
2898 END IF;
2899 END IF;
2900
2901 IF NVL(p_deliv_rec.can_fulfill_electronic_flag,'N') = NVL(p_deliv_rec.can_fulfill_physical_flag,'N')
2902 THEN
2903 AMS_UTILITY_PVT.error_message('AMS_DELV_PHY_OR_ELEC');
2904 --Please select this deliverable as physical or electronic.
2905 x_return_status := FND_API.g_ret_sts_error;
2906 RETURN;
2907 END IF;
2908
2909 IF NVL(p_deliv_rec.can_fulfill_electronic_flag ,'N') ='Y'
2910 AND p_deliv_rec.non_inv_quantity_on_hand > 0
2911 THEN
2912 AMS_UTILITY_PVT.error_message('AMS_DELV_NO_QUAN_ELEC');
2913 --- Quantity cannot be tracked for electronic deliverable. Please change your options
2914 x_return_status := FND_API.g_ret_sts_error;
2915 RETURN;
2916 END IF;
2917 -- validation added for the public api -- end
2918 ----------------------- rollup_flag ------------------------
2919 IF p_deliv_rec.kit_flag <> FND_API.g_miss_char
2920 AND p_deliv_rec.kit_flag IS NOT NULL
2921 THEN
2922 IF AMS_Utility_PVT.is_Y_or_N(p_deliv_rec.kit_flag) = FND_API.g_false
2923 THEN
2924 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2925 THEN
2926 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_BAD_KIT_FLAG');
2927 FND_MSG_PUB.add;
2928 END IF;
2929
2930 x_return_status := FND_API.g_ret_sts_error;
2931 RETURN;
2932 END IF;
2933 END IF;
2934
2935 ------------ Electronic,Physical and Inventory Flag -----------------------
2936 IF (AMS_DEBUG_HIGH_ON) THEN
2937
2938 AMS_UTILITY_PVT.debug_message(l_api_name||' : going to check the FLAGS');
2939 END IF;
2940
2941 OPEN get_flags(p_deliv_rec.deliverable_id);
2942 FETCH get_flags INTO l_old_electronic_flag
2943 ,l_old_physical_flag
2944 ,l_old_inventory_flag
2945 ,l_quantity_on_hand
2946 ,l_old_kit_flag
2947 ,l_status_code;
2948 CLOSE get_flags;
2949
2950
2951
2952
2953 -- When a deliv status is available,flags which identifies the charateristic of deliv cannot be updated.
2954 -- 01/29/03 added extra conditon to check the status (CANCELLED,ARCHIVED,EXPIRED) disallow user to chnage the delivery method flag check bug #2764840 mukumar start
2955 IF ( (l_status_code = 'AVAILABLE' OR l_status_code = 'CANCELLED' OR l_status_code = 'ARCHIVED' OR l_status_code = 'EXPIRED')
2956 AND( (l_old_electronic_flag = 'Y' AND p_deliv_rec.can_fulfill_electronic_flag = 'N')
2957 OR (l_old_physical_flag = 'Y' AND p_deliv_rec.can_fulfill_physical_flag = 'N')
2958 OR (l_old_inventory_flag = 'Y' AND p_deliv_rec.inventory_flag = 'N')
2959 OR (l_old_inventory_flag = 'N' AND p_deliv_rec.inventory_flag = 'Y')
2960 OR (l_old_kit_flag = 'Y' AND p_deliv_rec.kit_flag = 'N')
2961 OR (l_old_kit_flag = 'N' AND p_deliv_rec.kit_flag = 'Y'))
2962 )
2963 THEN
2964 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2965 THEN
2966 FND_MESSAGE.set_name('AMS', 'AMS_DELV_NO_UPD_AVAL_STATUS');
2967 FND_MSG_PUB.add;
2968 END IF;
2969 x_return_status := FND_API.g_ret_sts_error;
2970 RETURN;
2971 END IF;
2972
2973 IF (l_old_electronic_flag='N'
2974 AND p_deliv_rec.can_fulfill_electronic_flag = 'Y'
2975 AND l_old_inventory_flag ='Y' )THEN
2976 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2977 THEN
2978 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_CANT_CHANGE_EFLAG');
2979 FND_MSG_PUB.add;
2980 END IF;
2981 x_return_status := FND_API.g_ret_sts_error;
2982 RETURN;
2983 END IF;
2984
2985
2986 -- if an inv item is attached ,cannot make electronic to physical
2987 IF (l_old_physical_flag='N'
2988 AND p_deliv_rec.can_fulfill_physical_flag = 'Y'
2989 AND l_old_inventory_flag ='Y' )THEN
2990 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2991 THEN
2992 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_CANT_CHANGE_PFLAG');
2993 FND_MSG_PUB.add;
2994 END IF;
2995 x_return_status := FND_API.g_ret_sts_error;
2996 RETURN;
2997 -- if an inv item is attached ,cannot change phy-inventoried to stock manually
2998 -- except if kit_flag is 'Y' and pricing profile is 'N'
2999 ELSIF (l_old_inventory_flag ='Y'
3000 AND p_deliv_rec.inventory_flag='N'
3001 AND p_deliv_rec.can_fulfill_physical_flag = 'Y'
3002 AND (p_deliv_rec.kit_flag ='N' OR (p_deliv_rec.kit_flag='Y' and l_qp_profile='Y')))
3003 THEN
3004 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3005 THEN
3006 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_CANT_CHANGE_PFLAG');
3007 FND_MSG_PUB.add;
3008 END IF;
3009 x_return_status := FND_API.g_ret_sts_error;
3010 RETURN;
3011 END IF;
3012
3013 IF (AMS_DEBUG_HIGH_ON) THEN
3014 AMS_UTILITY_PVT.debug_message(l_api_name||' :checking the electronic flag cross checking the quantity on hand of physical');
3015 END IF;
3016
3017 IF (l_old_electronic_flag='N'
3018 AND p_deliv_rec.can_fulfill_electronic_flag = 'Y'
3019 AND l_quantity_on_hand >0 )THEN
3020 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3021 THEN
3022 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_CANT_CHANGE_EFLAG');
3023 FND_MSG_PUB.add;
3024 END IF;
3025 x_return_status := FND_API.g_ret_sts_error;
3026 RETURN;
3027 END IF;
3028
3029 IF (AMS_DEBUG_HIGH_ON) THEN
3030 AMS_UTILITY_PVT.debug_message(l_api_name||':cross checking the quantity on hand of physical and inventory');
3031 END IF;
3032 IF (l_old_inventory_flag='N'
3033 AND p_deliv_rec.inventory_flag = 'Y'
3034 AND l_quantity_on_hand >0 )THEN
3035 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3036 THEN
3037 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_CANT_CHANGE_IFLAG');
3038 FND_MSG_PUB.add;
3039 END IF;
3040 x_return_status := FND_API.g_ret_sts_error;
3041 RETURN;
3042 END IF;
3043
3044 --- before updating the physical and electronic flag checking whether it has kit parts
3045
3046 IF (AMS_DEBUG_HIGH_ON) THEN
3047 AMS_UTILITY_PVT.debug_message(l_api_name||':cross checking whether it has kit parts');
3048 END IF;
3049
3050 OPEN is_deliv_kit(p_deliv_rec.deliverable_id);
3051 FETCH is_deliv_kit INTO l_kits_check;
3052 CLOSE is_deliv_kit;
3053
3054 IF (l_old_electronic_flag='Y'
3055 AND p_deliv_rec.can_fulfill_electronic_flag = 'N'
3056 AND l_kits_check > 0)
3057 THEN
3058 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3059 THEN
3060 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_CANT_CHANGE_PFLAG');
3061 FND_MSG_PUB.add;
3062 END IF;
3063 x_return_status := FND_API.g_ret_sts_error;
3064 RETURN;
3065 END IF;
3066
3067 IF (l_old_physical_flag='Y'
3068 AND p_deliv_rec.can_fulfill_electronic_flag = 'Y'
3069 AND l_kits_check > 0)
3070 THEN
3071 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3072 THEN
3073 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_CANT_CHANGE_EFLAG');
3074 FND_MSG_PUB.add;
3075 END IF;
3076 x_return_status := FND_API.g_ret_sts_error;
3077 RETURN;
3078 END IF;
3079
3080 IF ( p_deliv_rec.can_fulfill_physical_flag = 'Y'
3081 AND l_old_inventory_flag ='N'
3082 AND p_deliv_rec.inventory_flag = 'Y'
3083 AND l_kits_check > 0)
3084 THEN
3085 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3086 THEN
3087 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_CANT_CHANGE_IFLAG');
3088 FND_MSG_PUB.add;
3089 END IF;
3090 x_return_status := FND_API.g_ret_sts_error;
3091 RETURN;
3092 END IF;
3093
3094 -- cannot change the stock manually deliv to physically inventoried if it as phys- inv kit.
3095 IF ( p_deliv_rec.can_fulfill_physical_flag = 'Y'
3096 AND l_old_inventory_flag ='Y'
3097 AND p_deliv_rec.inventory_flag = 'N'
3098 AND l_kits_check > 0)
3099 THEN
3100 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3101 THEN
3102 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_CANT_CHANGE_PFLAG');
3103 FND_MSG_PUB.add;
3104 END IF;
3105 x_return_status := FND_API.g_ret_sts_error;
3106 RETURN;
3107 /*
3108 ELSIF ( p_deliv_rec.can_fulfill_physical_flag = 'Y'
3109 AND l_old_inventory_flag ='Y'
3110 AND p_deliv_rec.inventory_flag = 'N'
3111 AND l_kits_check > 0
3112 AND p_deliv_rec.non_inv_quantity_on_hand >0)
3113 THEN
3114 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3115 THEN
3116 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_CANT_CHANGE_PFLAG');
3117 FND_MSG_PUB.add;
3118 END IF;
3119 x_return_status := FND_API.g_ret_sts_error;
3120 RETURN;
3121 */
3122 END IF;
3123
3124 -- added by abhola
3125 -- Check whether the deliverable is a part of a kit or is itself a kit.
3126 --if DELIV is part of KIT the KIT FLAG cannot be updated to Y
3127 --IF DELIV is a KIT, the KIT FLAG cannot be updated to N
3128
3129 -- If the deliv is a kit, cannot upd kit flag to 'N'
3130 if ((l_kits_check > 0) AND (p_deliv_rec.kit_flag = 'N')) then
3131 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3132 THEN
3133 FND_MESSAGE.set_name('AMS', 'AMS_CANNOT_UPD_KIT_TO_N');
3134 FND_MSG_PUB.add;
3135 END IF;
3136 x_return_status := FND_API.g_ret_sts_error;
3137 RETURN;
3138 end if;
3139
3140 l_kits_check := 0;
3141 -- If the deliv is a part of a kit, cannot upd kit flag to 'Y'
3142 OPEN is_deliv_kit_part(p_deliv_rec.deliverable_id);
3143 FETCH is_deliv_kit_part INTO l_kits_check;
3144 CLOSE is_deliv_kit_part;
3145
3146 if ((l_kits_check > 0) AND (p_deliv_rec.kit_flag = 'Y')) then
3147 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3148 THEN
3149 FND_MESSAGE.set_name('AMS', 'AMS_CANNOT_UPD_KIT_TO_Y');
3150 FND_MSG_PUB.add;
3151 END IF;
3152 x_return_status := FND_API.g_ret_sts_error;
3153 RETURN;
3154 end if;
3155 -- end by abhola
3156
3157 --Kit options cannot be updated if the inventory flag is 'Y'
3158 IF ((l_old_kit_flag = 'N'
3159 AND p_deliv_rec.kit_flag = 'Y'
3160 AND l_old_inventory_flag = 'Y')
3161 OR (l_old_kit_flag = 'Y'
3162 AND p_deliv_rec.kit_flag ='N'
3163 AND l_old_inventory_flag ='Y'))
3164 THEN
3165 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3166 THEN
3167 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_CANT_UPD_KIT');
3168 FND_MSG_PUB.add;
3169 END IF;
3170 x_return_status := FND_API.g_ret_sts_error;
3171 RETURN;
3172 END IF;
3173
3174 -- check whether the deliv is a part of a kit before updating the electronic,physical,inventory flags
3175 IF (l_old_electronic_flag='Y'
3176 AND p_deliv_rec.can_fulfill_electronic_flag = 'N'
3177 AND l_kits_check > 0)
3178 THEN
3179 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3180 THEN
3181 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_CANT_CHANGE_PFLAG');
3182 FND_MSG_PUB.add;
3183 END IF;
3184 x_return_status := FND_API.g_ret_sts_error;
3185 RETURN;
3186 END IF;
3187
3188 IF (l_old_physical_flag='Y'
3189 AND p_deliv_rec.can_fulfill_electronic_flag = 'Y'
3190 AND l_kits_check > 0) --l_check_deliv_has_kit = 'Y')
3191 THEN
3192 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3193 THEN
3194 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_CANT_CHANGE_EFLAG');
3195 FND_MSG_PUB.add;
3196 END IF;
3197 x_return_status := FND_API.g_ret_sts_error;
3198 RETURN;
3199 END IF;
3200
3201 IF ( p_deliv_rec.can_fulfill_physical_flag = 'Y'
3202 AND l_old_inventory_flag ='N'
3203 AND p_deliv_rec.inventory_flag = 'Y'
3204 AND l_kits_check > 0)
3205 THEN
3206 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3207 THEN
3208 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_CANT_CHANGE_IFLAG');
3209 FND_MSG_PUB.add;
3210 END IF;
3211 x_return_status := FND_API.g_ret_sts_error;
3212 RETURN;
3213 END IF;
3214
3215 -- cannot change the stock manually deliv to physically inventoried if it is part of phys- inv kit.
3216 IF ( p_deliv_rec.can_fulfill_physical_flag = 'Y'
3217 AND l_old_inventory_flag ='Y'
3218 AND p_deliv_rec.inventory_flag = 'N'
3219 AND l_kits_check > 0)
3220 THEN
3221 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3222 THEN
3223 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_CANT_CHANGE_PFLAG');
3224 FND_MSG_PUB.add;
3225 END IF;
3226 x_return_status := FND_API.g_ret_sts_error;
3227 RETURN;
3228 END IF;
3229
3230
3231
3232 END check_deliv_flag_items;
3233
3234
3235 ---------------------------------------------------------------------
3236 -- PROCEDURE
3237 -- check_deliv_items
3238 --
3239 -- HISTORY
3240 -- 10/11/99 khung Create.
3241 ---------------------------------------------------------------------
3242 PROCEDURE check_deliv_items
3243 (
3244 p_deliv_rec IN deliv_rec_type,
3245 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
3246 x_return_status OUT NOCOPY VARCHAR2
3247 )
3248 IS
3249 l_dummy NUMBER;
3250
3251 CURSOR c_period (l_name IN VARCHAR2) IS
3252 SELECT 1
3253 FROM dual
3254 WHERE EXISTS (
3255 SELECT 1
3256 FROM gl_periods_v
3257 WHERE period_set_name = p_deliv_rec.deliverable_calendar
3258 AND period_name = l_name)
3259 ;
3260 BEGIN
3261
3262 /* 01/29/03 commented out to allow user to chnage th status check bug #2764840 mukumar start
3263
3264 check_inactive_deliv
3265 ( p_deliv_rec => p_deliv_rec,
3266 x_return_status => x_return_status
3267 );
3268
3269 IF x_return_status <> FND_API.g_ret_sts_success THEN
3270 RETURN;
3271 END IF;
3272 01/29/03 commented out to allow user to chnage th status check bug #2764840 mukumar end */
3273
3274
3275 check_deliv_req_items
3276 (
3277 p_deliv_rec => p_deliv_rec,
3278 x_return_status => x_return_status
3279 );
3280
3281 IF x_return_status <> FND_API.g_ret_sts_success THEN
3282 RETURN;
3283 END IF;
3284
3285 check_deliv_uk_items
3286 (
3287 p_deliv_rec => p_deliv_rec,
3288 p_validation_mode => p_validation_mode,
3289 x_return_status => x_return_status
3290 );
3291
3292 IF x_return_status <> FND_API.g_ret_sts_success THEN
3293 RETURN;
3294 END IF;
3295
3296 check_deliv_fk_items
3297 (
3298 p_deliv_rec => p_deliv_rec,
3299 x_return_status => x_return_status
3300 );
3301
3302 IF x_return_status <> FND_API.g_ret_sts_success THEN
3303 RETURN;
3304 END IF;
3305
3306 check_deliv_lookup_items
3307 (
3308 p_deliv_rec => p_deliv_rec,
3309 x_return_status => x_return_status
3310 );
3311
3312 IF x_return_status <> FND_API.g_ret_sts_success THEN
3313 RETURN;
3314 END IF;
3315
3316 check_deliv_flag_items
3317 (
3318 p_deliv_rec => p_deliv_rec,
3319 p_validation_mode => p_validation_mode,
3320 x_return_status => x_return_status
3321 );
3322
3323 IF x_return_status <> FND_API.g_ret_sts_success THEN
3324 RETURN;
3325 END IF;
3326
3327 check_owner_id
3328 (
3329 p_deliv_rec => p_deliv_Rec,
3330 x_return_status => x_return_status
3331 );
3332
3333 IF x_return_status <> FND_API.g_ret_sts_success THEN
3334 RETURN;
3335 END IF;
3336
3337 IF (AMS_DEBUG_HIGH_ON) THEN
3338 AMS_Utility_PVT.debug_message('Checking the budget lines :');
3339 END IF;
3340
3341 check_budget_lines
3342 (
3343 p_deliv_rec => p_deliv_rec,
3344 x_return_status => x_Return_status
3345 );
3346
3347 IF x_return_status <> FND_API.g_ret_sts_success THEN
3348 RETURN;
3349 END IF;
3350
3351 IF (AMS_DEBUG_HIGH_ON) THEN
3352 AMS_Utility_PVT.debug_message('Start Period Name:'|| p_deliv_rec.start_period_name );
3353 AMS_Utility_PVT.debug_message('End Period Name:'|| p_deliv_rec.end_period_name );
3354 AMS_Utility_PVT.debug_message('Deliverable Calendar:'|| p_deliv_rec.deliverable_calendar );
3355 END IF;
3356
3357 IF p_deliv_rec.start_period_name IS NOT NULL THEN
3358 OPEN c_period (p_deliv_rec.start_period_name);
3359 FETCH c_period INTO l_dummy;
3360 IF c_period%NOTFOUND THEN
3361 x_return_status := FND_API.g_ret_sts_error;
3362 AMS_Utility_PVT.error_message ('AMS_CAMP_BAD_START_PERIOD');
3363 END IF;
3364 CLOSE c_period;
3365 END IF;
3366
3367 IF p_deliv_rec.end_period_name iS NOT NULL THEN
3368 OPEN c_period (p_deliv_rec.end_period_name);
3369 FETCH c_period INTO l_dummy;
3370 IF c_period%NOTFOUND THEN
3371 x_return_status := FND_API.g_ret_sts_error;
3372 AMS_Utility_PVT.error_message ('AMS_CAMP_BAD_END_PERIOD');
3373 END IF;
3374 CLOSE c_period;
3375 END IF;
3376
3377 END check_deliv_items;
3378
3379
3380 ---------------------------------------------------------------------
3381 -- PROCEDURE
3382 -- check_deliv_record
3383 --
3384 -- HISTORY
3385 -- 10/11/99 khung Create.
3386 ---------------------------------------------------------------------
3387
3388 PROCEDURE check_deliv_record
3389 (
3390 p_deliv_rec IN deliv_rec_type,
3391 p_complete_rec IN deliv_rec_type := NULL,
3392 x_return_status OUT NOCOPY VARCHAR2
3393 )
3394 IS
3395 l_dummy NUMBER;
3396 l_start_date DATE;
3397 l_end_date DATE;
3398 l_fore_comp_date DATE;
3399
3400 CURSOR c_calendar (l_calendar_name IN VARCHAR2) IS
3401 SELECT 1
3402 FROM dual
3403 WHERE EXISTS (SELECT 1
3404 FROM gl_periods_v
3405 WHERE period_set_name = l_calendar_name);
3406
3407 CURSOR c_period (l_name IN VARCHAR2) IS
3408 SELECT start_date, end_date
3409 FROM gl_periods_v
3410 WHERE period_set_name = p_deliv_rec.deliverable_calendar
3411 AND period_name = l_name;
3412 l_start_rec c_period%ROWTYPE;
3413 l_end_rec c_period%ROWTYPE;
3414 BEGIN
3415
3416 x_return_status := FND_API.g_ret_sts_success;
3417
3418 IF p_deliv_rec.actual_avail_from_date <> FND_API.g_miss_date
3419 OR p_deliv_rec.actual_avail_to_date <> FND_API.g_miss_date
3420 THEN
3421 IF p_deliv_rec.actual_avail_from_date = FND_API.g_miss_date THEN
3422 l_start_date := p_complete_rec.actual_avail_from_date;
3423 ELSE
3424 l_start_date := p_deliv_rec.actual_avail_from_date;
3425 END IF;
3426
3427 IF p_deliv_rec.actual_avail_to_date = FND_API.g_miss_date THEN
3428 l_end_date := p_complete_rec.actual_avail_to_date;
3429 ELSE
3430 l_end_date := p_deliv_rec.actual_avail_to_date;
3431 END IF;
3432
3433 IF l_start_date > l_end_date THEN
3434 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3435 THEN
3436 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_DATE_AFTER_DATE');
3437 FND_MSG_PUB.add;
3438 END IF;
3439 x_return_status := FND_API.g_ret_sts_error;
3440 END IF;
3441 END IF;
3442
3443 IF p_deliv_rec.actual_avail_from_date <> FND_API.g_miss_date
3444 OR p_deliv_rec.forecasted_complete_date <> FND_API.g_miss_date
3445 THEN
3446 IF p_deliv_rec.actual_avail_from_date = FND_API.g_miss_date THEN
3447 l_start_date := p_complete_rec.actual_avail_from_date;
3448 ELSE
3449 l_start_date := p_deliv_rec.actual_avail_from_date;
3450 END IF;
3451
3452 IF p_deliv_rec.forecasted_complete_date = FND_API.g_miss_date THEN
3453 l_fore_comp_date := p_complete_rec.forecasted_complete_date;
3454 ELSE
3455 l_fore_comp_date := p_deliv_rec.forecasted_complete_date;
3456 END IF;
3457
3458 IF l_start_date < l_fore_comp_date THEN
3459 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3460 THEN
3461 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_FOREC_AFTER_START');
3462 FND_MSG_PUB.add;
3463 END IF;
3464 x_return_status := FND_API.g_ret_sts_error;
3465 END IF;
3466
3467 /* remove on 06/07/2000 by khung
3468 IF p_deliv_rec.deliverable_id IS NULL THEN -- only for creation
3469 IF l_fore_comp_date < SYSDATE THEN
3470 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3471 THEN
3472 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_FOREC_BEFORE_TODAY');
3473 FND_MSG_PUB.add;
3474 END IF;
3475 x_return_status := FND_API.g_ret_sts_error;
3476 END IF;
3477 END IF;
3478 */
3479 END IF;
3480
3481 -- do other record level checkings
3482
3483 -- start period and end period validation
3484 IF p_deliv_rec.start_period_name IS NOT NULL OR p_deliv_rec.end_period_name IS NOT NULL THEN
3485 -- validate calendr exists only if start or end period is chosen
3486 OPEN c_calendar (p_deliv_rec.deliverable_calendar);
3487 FETCH c_calendar INTO l_dummy;
3488 IF c_calendar%NOTFOUND THEN
3489 x_return_status := FND_API.g_ret_sts_error;
3490 AMS_Utility_PVT.error_message ('AMS_CAMP_NO_CAMPAIGN_CALENDAR');
3491 END IF;
3492 CLOSE c_calendar;
3493 END IF;
3494
3495 IF p_deliv_rec.start_period_name IS NOT NULL THEN
3496 OPEN c_period (p_deliv_rec.start_period_name);
3497 FETCH c_period INTO l_start_rec;
3498 CLOSE c_period;
3499 END IF;
3500
3501 IF p_deliv_rec.end_period_name IS NOT NULL THEN
3502 OPEN c_period (p_deliv_rec.end_period_name);
3503 FETCH c_period INTO l_end_rec;
3504 CLOSE c_period;
3505 END IF;
3506
3507 --
3508 -- The start period start date should be
3509 -- before the end period end date
3510 IF l_start_rec.start_date IS NOT NULL AND l_end_rec.end_date IS NOT NULL THEN
3511 IF l_start_rec.start_date > l_end_rec.end_date THEN
3512 x_return_status := FND_API.g_ret_sts_error;
3513 AMS_Utility_PVT.error_message ('AMS_CAMP_BAD_PERIODS');
3514 END IF;
3515 END IF;
3516
3517 --
3518 -- Available From date should be within
3519 -- the given start period date range.
3520 IF l_start_rec.start_date IS NOT NULL THEN
3521 IF p_complete_rec.actual_avail_from_date < l_start_rec.start_date OR p_complete_rec.actual_avail_from_date > l_start_rec.end_date THEN
3522 x_return_status := FND_API.g_ret_sts_error;
3523 AMS_Utility_PVT.error_message ('AMS_DELV_OUT_START_DATE');
3524 END IF;
3525 END IF;
3526
3527 --
3528 -- Available To date should be within the
3529 -- given end period date range.
3530 IF l_end_rec.start_date IS NOT NULL THEN
3531 IF p_complete_rec.actual_avail_to_date < l_end_rec.start_date OR p_complete_rec.actual_avail_to_date > l_end_rec.end_date THEN
3532 x_return_status := FND_API.g_ret_sts_error;
3533 AMS_Utility_PVT.error_message ('AMS_DELV_OUT_END_DATE');
3534 END IF;
3535 END IF;
3536
3537
3538 END check_deliv_record;
3539
3540
3541 ---------------------------------------------------------------------
3542 -- PROCEDURE
3543 -- init_deliv_rec
3544 --
3545 -- HISTORY
3546 -- 10/11/99 khung Create.
3547 ---------------------------------------------------------------------
3548
3549 PROCEDURE init_deliv_rec
3550 (
3551 x_deliv_rec OUT NOCOPY deliv_rec_type
3552 )
3553 IS
3554
3555 BEGIN
3556
3557 IF (AMS_DEBUG_HIGH_ON) THEN
3558 AMS_Utility_PVT.debug_message('IN THE INIT_DELIV REC');
3559 END IF;
3560 x_deliv_rec.deliverable_id := FND_API.g_miss_num;
3561 x_deliv_rec.last_update_date := FND_API.g_miss_date;
3562 x_deliv_rec.last_updated_by := FND_API.g_miss_num;
3563 x_deliv_rec.creation_date := FND_API.g_miss_date;
3564 x_deliv_rec.created_by := FND_API.g_miss_num;
3565 x_deliv_rec.last_update_login := FND_API.g_miss_num;
3566 x_deliv_rec.object_version_number := FND_API.g_miss_num;
3567 x_deliv_rec.language_code := FND_API.g_miss_char;
3568 x_deliv_rec.version := FND_API.g_miss_char;
3569 x_deliv_rec.application_id := FND_API.g_miss_num;
3570 x_deliv_rec.user_status_id := FND_API.g_miss_num;
3571 x_deliv_rec.status_code := FND_API.g_miss_char;
3572 x_deliv_rec.status_date := FND_API.g_miss_date;
3573 x_deliv_rec.active_flag := FND_API.g_miss_char;
3574 x_deliv_rec.private_flag := FND_API.g_miss_char;
3575 x_deliv_rec.owner_user_id := FND_API.g_miss_num;
3576 x_deliv_rec.fund_source_id := FND_API.g_miss_num;
3577 x_deliv_rec.fund_source_type := FND_API.g_miss_char;
3578 x_deliv_rec.category_type_id := FND_API.g_miss_num;
3579 x_deliv_rec.category_sub_type_id := FND_API.g_miss_num;
3580 x_deliv_rec.kit_flag := FND_API.g_miss_char;
3581 x_deliv_rec.can_fulfill_electronic_flag := FND_API.g_miss_char;
3582 x_deliv_rec.can_fulfill_physical_flag := FND_API.g_miss_char;
3583 x_deliv_rec.jtf_amv_item_id := FND_API.g_miss_num;
3584 x_deliv_rec.inventory_flag := FND_API.g_miss_char;
3585 x_deliv_rec.transaction_currency_code := FND_API.g_miss_char;
3586 x_deliv_rec.functional_currency_code := FND_API.g_miss_char;
3587 x_deliv_rec.budget_amount_tc := FND_API.g_miss_num;
3588 x_deliv_rec.budget_amount_fc := FND_API.g_miss_num;
3589 x_deliv_rec.actual_avail_from_date := FND_API.g_miss_date;
3590 x_deliv_rec.actual_avail_to_date := FND_API.g_miss_date;
3591 x_deliv_rec.forecasted_complete_date := FND_API.g_miss_date;
3592 x_deliv_rec.actual_complete_date := FND_API.g_miss_date;
3593 x_deliv_rec.replaced_by_deliverable_id := FND_API.g_miss_num;
3594 x_deliv_rec.inventory_item_id := FND_API.g_miss_num;
3595 x_deliv_rec.inventory_item_org_id := FND_API.g_miss_num;
3596 x_deliv_rec.pricelist_header_id := FND_API.g_miss_num;
3597 x_deliv_rec.pricelist_line_id := FND_API.g_miss_num;
3598 x_deliv_rec.non_inv_ctrl_code := FND_API.g_miss_char;
3599 x_deliv_rec.non_inv_quantity_on_hand := FND_API.g_miss_num;
3600 x_deliv_rec.non_inv_quantity_on_order := FND_API.g_miss_num;
3601 x_deliv_rec.non_inv_quantity_on_reserve := FND_API.g_miss_num;
3602 x_deliv_rec.chargeback_amount := FND_API.g_miss_num;
3603 x_deliv_rec.chargeback_amount_curr_code := FND_API.g_miss_char;
3604 x_deliv_rec.deliverable_code := FND_API.g_miss_char;
3605 x_deliv_rec.deliverable_pick_flag := FND_API.g_miss_char;
3606 x_deliv_rec.currency_code := FND_API.g_miss_char;
3607 x_deliv_rec.forecasted_cost := FND_API.g_miss_num;
3608 x_deliv_rec.actual_cost := FND_API.g_miss_num;
3609 x_deliv_rec.forecasted_responses := FND_API.g_miss_num;
3610 x_deliv_rec.actual_responses := FND_API.g_miss_num;
3611 x_deliv_rec.country_id := FND_API.g_miss_num;
3612 x_deliv_rec.setup_id := FND_API.g_miss_num;
3613 x_deliv_rec.attribute_category := FND_API.g_miss_char;
3614 x_deliv_rec.attribute1 := FND_API.g_miss_char;
3615 x_deliv_rec.attribute2 := FND_API.g_miss_char;
3616 x_deliv_rec.attribute3 := FND_API.g_miss_char;
3617 x_deliv_rec.attribute4 := FND_API.g_miss_char;
3618 x_deliv_rec.attribute5 := FND_API.g_miss_char;
3619 x_deliv_rec.attribute6 := FND_API.g_miss_char;
3620 x_deliv_rec.attribute7 := FND_API.g_miss_char;
3621 x_deliv_rec.attribute8 := FND_API.g_miss_char;
3622 x_deliv_rec.attribute9 := FND_API.g_miss_char;
3623 x_deliv_rec.attribute10 := FND_API.g_miss_char;
3624 x_deliv_rec.attribute11 := FND_API.g_miss_char;
3625 x_deliv_rec.attribute12 := FND_API.g_miss_char;
3626 x_deliv_rec.attribute13 := FND_API.g_miss_char;
3627 x_deliv_rec.attribute14 := FND_API.g_miss_char;
3628 x_deliv_rec.attribute15 := FND_API.g_miss_char;
3629 x_deliv_rec.chargeback_uom := FND_API.g_miss_char;
3630 x_deliv_rec.deliverable_name := FND_API.g_miss_char;
3631 x_deliv_rec.description := FND_API.g_miss_char;
3632 x_deliv_rec.deliverable_calendar := FND_API.g_miss_char;
3633 x_deliv_rec.start_period_name := FND_API.g_miss_char;
3634 x_deliv_rec.end_period_name := FND_API.g_miss_char;
3635
3636 END init_deliv_rec;
3637
3638 ---------------------------------------------------------------------
3639 -- PROCEDURE
3640 -- complete_deliv_rec
3641 --
3642 -- HISTORY
3643 -- 10/11/99 khung Create.
3644 ---------------------------------------------------------------------
3645 PROCEDURE complete_deliv_rec
3646 (
3647 p_deliv_rec IN deliv_rec_type,
3648 x_complete_rec OUT NOCOPY deliv_rec_type
3649 )
3650 IS
3651
3652 CURSOR c_deliv IS
3653 SELECT *
3654 FROM ams_deliverables_vl
3655 WHERE deliverable_id = p_deliv_rec.deliverable_id;
3656
3657 l_deliv_rec c_deliv%ROWTYPE;
3658
3659 BEGIN
3660
3661 IF (AMS_DEBUG_HIGH_ON) THEN
3662
3663
3664
3665 AMS_Utility_PVT.debug_message('complete_deliv_rec...');
3666
3667 END IF;
3668
3669 x_complete_rec := p_deliv_rec;
3670
3671 OPEN c_deliv;
3672 FETCH c_deliv INTO l_deliv_rec;
3673 IF c_deliv%NOTFOUND THEN
3674 CLOSE c_deliv;
3675 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3676 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
3677 FND_MSG_PUB.add;
3678 END IF;
3679 RAISE FND_API.g_exc_error;
3680 END IF;
3681 CLOSE c_deliv;
3682
3683 IF p_deliv_rec.language_code = FND_API.g_miss_char THEN
3684 x_complete_rec.language_code := l_deliv_rec.language_code;
3685 END IF;
3686
3687 IF p_deliv_rec.version = FND_API.g_miss_char THEN
3688 x_complete_rec.version := l_deliv_rec.version;
3689 END IF;
3690
3691 IF p_deliv_rec.application_id = FND_API.g_miss_num THEN
3692 x_complete_rec.application_id := l_deliv_rec.application_id;
3693 END IF;
3694
3695 IF p_deliv_rec.user_status_id = FND_API.g_miss_num THEN
3696 x_complete_rec.user_status_id := l_deliv_rec.user_status_id;
3697 END IF;
3698
3699 IF p_deliv_rec.status_code = FND_API.g_miss_char THEN
3700 x_complete_rec.status_code := l_deliv_rec.status_code;
3701 END IF;
3702
3703 IF p_deliv_rec.status_date = FND_API.g_miss_date THEN
3704 x_complete_rec.status_date := l_deliv_rec.status_date;
3705 END IF;
3706
3707 IF p_deliv_rec.active_flag = FND_API.g_miss_char THEN
3708 x_complete_rec.active_flag := l_deliv_rec.active_flag;
3709 END IF;
3710
3711 IF p_deliv_rec.private_flag = FND_API.g_miss_char THEN
3712 x_complete_rec.private_flag := l_deliv_rec.private_flag;
3713 END IF;
3714
3715 IF p_deliv_rec.owner_user_id = FND_API.g_miss_num THEN
3716 x_complete_rec.owner_user_id := l_deliv_rec.owner_user_id;
3717 END IF;
3718
3719 IF p_deliv_rec.fund_source_id = FND_API.g_miss_num THEN
3720 x_complete_rec.fund_source_id := l_deliv_rec.fund_source_id;
3721 END IF;
3722
3723 IF p_deliv_rec.fund_source_type = FND_API.g_miss_char THEN
3724 x_complete_rec.fund_source_type := l_deliv_rec.fund_source_type;
3725 END IF;
3726
3727 IF p_deliv_rec.category_type_id = FND_API.g_miss_num THEN
3728 x_complete_rec.category_type_id := l_deliv_rec.category_type_id;
3729 END IF;
3730
3731 IF p_deliv_rec.category_sub_type_id = FND_API.g_miss_num THEN
3732 x_complete_rec.category_sub_type_id := l_deliv_rec.category_sub_type_id;
3733 END IF;
3734
3735 IF p_deliv_rec.kit_flag = FND_API.g_miss_char THEN
3736 x_complete_rec.kit_flag := l_deliv_rec.kit_flag;
3737 END IF;
3738
3739 IF p_deliv_rec.can_fulfill_electronic_flag = FND_API.g_miss_char THEN
3740 x_complete_rec.can_fulfill_electronic_flag := l_deliv_rec.can_fulfill_electronic_flag;
3741 END IF;
3742
3743 IF p_deliv_rec.can_fulfill_physical_flag = FND_API.g_miss_char THEN
3744 x_complete_rec.can_fulfill_physical_flag := l_deliv_rec.can_fulfill_physical_flag;
3745 END IF;
3746
3747 IF p_deliv_rec.jtf_amv_item_id = FND_API.g_miss_num THEN
3748 x_complete_rec.jtf_amv_item_id := l_deliv_rec.jtf_amv_item_id;
3749 END IF;
3750
3751 IF p_deliv_rec.inventory_flag = FND_API.g_miss_char THEN
3752 x_complete_rec.inventory_flag := l_deliv_rec.inventory_flag;
3753 END IF;
3754
3755 IF p_deliv_rec.transaction_currency_code = FND_API.g_miss_char THEN
3756 x_complete_rec.transaction_currency_code := l_deliv_rec.transaction_currency_code;
3757 END IF;
3758
3759 IF p_deliv_rec.functional_currency_code = FND_API.g_miss_char THEN
3760 x_complete_rec.functional_currency_code := l_deliv_rec.functional_currency_code;
3761 END IF;
3762
3763 IF p_deliv_rec.budget_amount_tc = FND_API.g_miss_num THEN
3764 x_complete_rec.budget_amount_tc := l_deliv_rec.budget_amount_tc;
3765 END IF;
3766
3767 IF p_deliv_rec.budget_amount_fc = FND_API.g_miss_num THEN
3768 x_complete_rec.budget_amount_fc := l_deliv_rec.budget_amount_fc;
3769 END IF;
3770
3771 IF p_deliv_rec.actual_avail_from_date = FND_API.g_miss_date THEN
3772 x_complete_rec.actual_avail_from_date := l_deliv_rec.actual_avail_from_date;
3773 END IF;
3774
3775 IF p_deliv_rec.actual_avail_to_date = FND_API.g_miss_date THEN
3776 x_complete_rec.actual_avail_to_date := l_deliv_rec.actual_avail_to_date;
3777 END IF;
3778
3779 IF p_deliv_rec.forecasted_complete_date = FND_API.g_miss_date THEN
3780 x_complete_rec.forecasted_complete_date := l_deliv_rec.forecasted_complete_date;
3781 END IF;
3782
3783 IF p_deliv_rec.actual_complete_date = FND_API.g_miss_date THEN
3784 x_complete_rec.actual_complete_date := l_deliv_rec.actual_complete_date;
3785 END IF;
3786
3787 IF p_deliv_rec.replaced_by_deliverable_id = FND_API.g_miss_num THEN
3788 x_complete_rec.replaced_by_deliverable_id := l_deliv_rec.replaced_by_deliverable_id;
3789 END IF;
3790
3791 IF p_deliv_rec.inventory_item_id = FND_API.g_miss_num THEN
3792 x_complete_rec.inventory_item_id := l_deliv_rec.inventory_item_id;
3793 END IF;
3794
3795 IF p_deliv_rec.inventory_item_org_id = FND_API.g_miss_num THEN
3796 x_complete_rec.inventory_item_org_id := l_deliv_rec.inventory_item_org_id;
3797 END IF;
3798
3799 IF p_deliv_rec.pricelist_header_id = FND_API.g_miss_num THEN
3800 x_complete_rec.pricelist_header_id := l_deliv_rec.pricelist_header_id;
3801 END IF;
3802
3803 IF p_deliv_rec.pricelist_line_id = FND_API.g_miss_num THEN
3804 x_complete_rec.pricelist_line_id := l_deliv_rec.pricelist_line_id;
3805 END IF;
3806
3807 IF p_deliv_rec.non_inv_ctrl_code = FND_API.g_miss_char THEN
3808 x_complete_rec.non_inv_ctrl_code := l_deliv_rec.non_inv_ctrl_code;
3809 END IF;
3810
3811 IF p_deliv_rec.non_inv_quantity_on_hand = FND_API.g_miss_num THEN
3812 x_complete_rec.non_inv_quantity_on_hand := l_deliv_rec.non_inv_quantity_on_hand;
3813 END IF;
3814
3815 IF p_deliv_rec.non_inv_quantity_on_order = FND_API.g_miss_num THEN
3816 x_complete_rec.non_inv_quantity_on_order := l_deliv_rec.non_inv_quantity_on_order;
3817 END IF;
3818
3819 IF p_deliv_rec.non_inv_quantity_on_reserve = FND_API.g_miss_num THEN
3820 x_complete_rec.non_inv_quantity_on_reserve := l_deliv_rec.non_inv_quantity_on_reserve;
3821 END IF;
3822
3823 IF p_deliv_rec.chargeback_amount = FND_API.g_miss_num THEN
3824 x_complete_rec.chargeback_amount := l_deliv_rec.chargeback_amount;
3825 END IF;
3826
3827 IF p_deliv_rec.chargeback_amount_curr_code = FND_API.g_miss_char THEN
3828 x_complete_rec.chargeback_amount_curr_code := l_deliv_rec.chargeback_amount_curr_code;
3829 END IF;
3830
3831 IF p_deliv_rec.deliverable_code = FND_API.g_miss_char THEN
3832 x_complete_rec.deliverable_code := l_deliv_rec.deliverable_code;
3833 END IF;
3834
3835 IF p_deliv_rec.deliverable_pick_flag = FND_API.g_miss_char THEN
3836 x_complete_rec.deliverable_pick_flag := l_deliv_rec.deliverable_pick_flag;
3837 END IF;
3838
3839 IF p_deliv_rec.currency_code = FND_API.g_miss_char THEN
3840 x_complete_rec.currency_code := l_deliv_rec.currency_code;
3841 END IF;
3842
3843 IF p_deliv_rec.forecasted_cost = FND_API.g_miss_num THEN
3844 x_complete_rec.forecasted_cost := l_deliv_rec.forecasted_cost;
3845 END IF;
3846
3847 IF p_deliv_rec.actual_cost = FND_API.g_miss_num THEN
3848 x_complete_rec.actual_cost := l_deliv_rec.actual_cost;
3849 END IF;
3850
3851 IF p_deliv_rec.forecasted_responses = FND_API.g_miss_num THEN
3852 x_complete_rec.forecasted_responses := l_deliv_rec.forecasted_responses;
3853 END IF;
3854
3855 IF p_deliv_rec.actual_responses = FND_API.g_miss_num THEN
3856 x_complete_rec.actual_responses := l_deliv_rec.actual_responses;
3857 END IF;
3858
3859 IF p_deliv_rec.country = FND_API.g_miss_char THEN
3860 x_complete_rec.country := l_deliv_rec.country;
3861 END IF;
3862
3863 IF p_deliv_rec.attribute_category = FND_API.g_miss_char THEN
3864 x_complete_rec.attribute_category := l_deliv_rec.attribute_category;
3865 END IF;
3866
3867 IF p_deliv_rec.attribute1 = FND_API.g_miss_char THEN
3868 x_complete_rec.attribute1 := l_deliv_rec.attribute1;
3869 END IF;
3870
3871 IF p_deliv_rec.attribute2 = FND_API.g_miss_char THEN
3872 x_complete_rec.attribute2 := l_deliv_rec.attribute2;
3873 END IF;
3874
3875 IF p_deliv_rec.attribute3 = FND_API.g_miss_char THEN
3876 x_complete_rec.attribute3 := l_deliv_rec.attribute3;
3877 END IF;
3878
3879 IF p_deliv_rec.attribute4 = FND_API.g_miss_char THEN
3880 x_complete_rec.attribute4 := l_deliv_rec.attribute4;
3881 END IF;
3882
3883 IF p_deliv_rec.attribute5 = FND_API.g_miss_char THEN
3884 x_complete_rec.attribute5 := l_deliv_rec.attribute5;
3885 END IF;
3886
3887 IF p_deliv_rec.attribute6 = FND_API.g_miss_char THEN
3888 x_complete_rec.attribute6 := l_deliv_rec.attribute6;
3889 END IF;
3890
3891 IF p_deliv_rec.attribute7 = FND_API.g_miss_char THEN
3892 x_complete_rec.attribute7 := l_deliv_rec.attribute7;
3893 END IF;
3894
3895 IF p_deliv_rec.attribute8 = FND_API.g_miss_char THEN
3896 x_complete_rec.attribute8 := l_deliv_rec.attribute8;
3897 END IF;
3898
3899 IF p_deliv_rec.attribute9 = FND_API.g_miss_char THEN
3900 x_complete_rec.attribute9 := l_deliv_rec.attribute9;
3901 END IF;
3902
3903 IF p_deliv_rec.attribute10 = FND_API.g_miss_char THEN
3904 x_complete_rec.attribute10 := l_deliv_rec.attribute10;
3905 END IF;
3906
3907 IF p_deliv_rec.attribute11 = FND_API.g_miss_char THEN
3908 x_complete_rec.attribute11 := l_deliv_rec.attribute11;
3909 END IF;
3910
3911 IF p_deliv_rec.attribute12 = FND_API.g_miss_char THEN
3912 x_complete_rec.attribute12 := l_deliv_rec.attribute12;
3913 END IF;
3914
3915 IF p_deliv_rec.attribute13 = FND_API.g_miss_char THEN
3916 x_complete_rec.attribute13 := l_deliv_rec.attribute13;
3917 END IF;
3918
3919 IF p_deliv_rec.attribute14 = FND_API.g_miss_char THEN
3920 x_complete_rec.attribute14 := l_deliv_rec.attribute14;
3921 END IF;
3922
3923 IF p_deliv_rec.attribute15 = FND_API.g_miss_char THEN
3924 x_complete_rec.attribute15 := l_deliv_rec.attribute15;
3925 END IF;
3926
3927 IF p_deliv_rec.chargeback_uom = FND_API.g_miss_char THEN
3928 x_complete_rec.chargeback_uom := l_deliv_rec.chargeback_uom;
3929 END IF;
3930
3931 IF p_deliv_rec.deliverable_name = FND_API.g_miss_char THEN
3932 x_complete_rec.deliverable_name := l_deliv_rec.deliverable_name;
3933 END IF;
3934
3935 IF p_deliv_rec.description = FND_API.g_miss_char THEN
3936 x_complete_rec.description := l_deliv_rec.description;
3937 END IF;
3938
3939 IF p_deliv_rec.deliverable_calendar = FND_API.g_miss_char THEN
3940 x_complete_rec.deliverable_calendar := l_deliv_rec.deliverable_calendar;
3941 END IF;
3942
3943 IF p_deliv_rec.start_period_name = FND_API.g_miss_char THEN
3944 x_complete_rec.start_period_name := l_deliv_rec.start_period_name;
3945 END IF;
3946
3947 IF p_deliv_rec.end_period_name = FND_API.g_miss_char THEN
3948 x_complete_rec.end_period_name := l_deliv_rec.end_period_name;
3949 END IF;
3950
3951 IF p_deliv_rec.country_id = FND_API.g_miss_num THEN
3952 x_complete_rec.country_id := l_deliv_rec.country_id;
3953 END IF;
3954
3955 IF p_deliv_rec.Setup_id = FND_API.g_miss_num THEN
3956 x_complete_rec.Setup_id := l_deliv_rec.custom_setup_id;
3957 END IF;
3958
3959 END complete_deliv_rec;
3960
3961
3962 ---------------------------------------------------------------------
3963 -- PROCEDURE
3964 -- create_pricelist_header
3965 --
3966 -- HISTORY
3967 -- 02/16/2000 khung@us Create.
3968 ---------------------------------------------------------------------
3969
3970 PROCEDURE create_pricelist_header
3971 (
3972 p_api_version IN NUMBER,
3973 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
3974 p_return_values IN VARCHAR2 := FND_API.g_false,
3975 p_commit IN VARCHAR2 := FND_API.g_false,
3976 p_deliv_rec IN deliv_rec_type,
3977 x_return_status OUT NOCOPY VARCHAR2,
3978 x_msg_count OUT NOCOPY NUMBER,
3979 x_msg_data OUT NOCOPY VARCHAR2,
3980 x_pricelist_header_id OUT NOCOPY NUMBER
3981 )
3982
3983 IS
3984 l_api_version CONSTANT NUMBER := 1.0;
3985 l_api_name CONSTANT VARCHAR2(30) := 'create_pricelist_header';
3986 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
3987
3988 l_return_status VARCHAR2(1);
3989 l_msg_count NUMBER;
3990 l_msg_data VARCHAR2(2000);
3991
3992 p_price_list_rec qp_price_list_pub.price_list_rec_type;
3993 p_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
3994 p_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
3995 p_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
3996 p_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
3997 p_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
3998 p_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
3999 p_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
4000
4001 l_price_list_rec qp_price_list_pub.price_list_rec_type;
4002 l_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
4003 l_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
4004 l_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
4005 l_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
4006 l_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
4007 l_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
4008 l_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
4009
4010 BEGIN
4011
4012 --dbms_output.put_line('create List header called');
4013 x_return_status := FND_API.g_ret_sts_success;
4014
4015 p_price_list_rec.name := 'Deliverable Inventory HDR';
4016 p_price_list_rec.created_by := p_deliv_rec.owner_user_id;
4017 p_price_list_rec.creation_date := sysdate;
4018 p_price_list_rec.currency_code := FND_PROFILE.Value('AMS_DEFAULT_CURR_CODE');
4019 p_price_list_rec.list_type_code := 'PRL';
4020 p_price_list_rec.description := 'Deliverable Inventory HDR';
4021 p_price_list_rec.start_date_active := p_deliv_rec.actual_avail_from_date;
4022 --p_price_list_rec.end_date_active := p_deliv_rec.actual_avail_to_date;
4023 p_price_list_rec.operation :=QP_GLOBALS.G_OPR_CREATE;
4024
4025 IF (AMS_DEBUG_HIGH_ON) THEN
4026 AMS_Utility_PVT.debug_message(l_full_name ||': create pricelist header...');
4027 END IF;
4028
4029 QP_PRICE_LIST_PUB.Process_Price_List(
4030 p_api_version_number => 1.0,
4031 p_init_msg_list => FND_API.g_false,
4032 p_return_values => FND_API.G_TRUE,
4033 p_commit => FND_API.g_false,
4034 x_return_status => l_return_status,
4035 x_msg_count => l_msg_count,
4036 x_msg_data => l_msg_data,
4037 p_price_list_rec => p_price_list_rec,
4038 p_price_list_val_rec => p_price_list_val_rec,
4039 p_price_list_line_tbl => p_price_list_line_tbl ,
4040 p_price_list_line_val_tbl => p_price_list_line_val_tbl ,
4041 p_qualifiers_tbl => p_qualifiers_tbl,
4042 p_qualifiers_val_tbl => p_qualifiers_val_tbl,
4043 p_pricing_attr_tbl => p_pricing_attr_tbl,
4044 p_pricing_attr_val_tbl => p_pricing_attr_val_tbl,
4045 x_price_list_rec => l_price_list_rec,
4046 x_price_list_val_rec => l_price_list_val_rec,
4047 x_price_list_line_tbl => l_price_list_line_tbl ,
4048 x_price_list_line_val_tbl => l_price_list_line_val_tbl ,
4049 x_qualifiers_tbl => l_qualifiers_tbl,
4050 x_qualifiers_val_tbl => l_qualifiers_val_tbl,
4051 x_pricing_attr_tbl => l_pricing_attr_tbl,
4052 x_pricing_attr_val_tbl => l_pricing_attr_val_tbl
4053 );
4054
4055 x_pricelist_header_id := l_price_list_rec.list_header_id;
4056 x_return_status := l_return_status;
4057
4058 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4059 RAISE FND_API.g_exc_unexpected_error;
4060 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
4061 RAISE FND_API.g_exc_error;
4062 END IF;
4063
4064 IF p_commit = FND_API.g_true THEN
4065 COMMIT WORK;
4066 END IF;
4067 END create_pricelist_header;
4068
4069 ---------------------------------------------------------------------
4070 -- PROCEDURE
4071 -- create_pricelist_line
4072 --
4073 -- HISTORY
4074 -- 02/17/2000 khung@us Create.
4075 ---------------------------------------------------------------------
4076
4077 PROCEDURE create_pricelist_line
4078 (
4079 p_api_version IN NUMBER,
4080 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
4081 p_return_values IN VARCHAR2 := FND_API.g_false,
4082 p_commit IN VARCHAR2 := FND_API.g_false,
4083 p_price_hdr_id IN NUMBER,
4084 p_deliv_rec IN deliv_rec_type,
4085 x_return_status OUT NOCOPY VARCHAR2,
4086 x_msg_count OUT NOCOPY NUMBER,
4087 x_msg_data OUT NOCOPY VARCHAR2,
4088 x_pricelist_line_id OUT NOCOPY NUMBER
4089 )
4090 IS
4091 l_api_version CONSTANT NUMBER := 1.0;
4092 l_api_name CONSTANT VARCHAR2(30) := 'create_pricelist_line';
4093 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
4094
4095 l_return_status VARCHAR2(1);
4096 l_msg_count NUMBER;
4097 l_msg_data VARCHAR2(2000);
4098
4099 p_price_list_rec qp_price_list_pub.price_list_rec_type;
4100 p_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
4101 p_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
4102 p_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
4103 p_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
4104 p_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
4105 p_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
4106 p_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
4107
4108 l_price_list_rec qp_price_list_pub.price_list_rec_type;
4109 l_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
4110 l_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
4111 l_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
4112 l_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
4113 l_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
4114 l_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
4115 l_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
4116
4117 BEGIN
4118
4119 --dbms_output.put_line('create List line called');
4120 x_return_status := FND_API.g_ret_sts_success;
4121
4122 p_price_list_line_tbl(1).list_header_id := p_price_hdr_id;
4123 --dbms_output.put_line('p_price_hdr_id: '||p_price_hdr_id);
4124 p_price_list_line_tbl(1).list_line_type_code := 'PLL';
4125 p_price_list_line_tbl(1).base_uom_code := 'EA';
4126 p_price_list_line_tbl(1).created_by := p_deliv_rec.owner_user_id;
4127 p_price_list_line_tbl(1).inventory_item_id := p_deliv_rec.inventory_item_id;
4128 p_price_list_line_tbl(1).start_date_active := p_deliv_rec.actual_avail_from_date;
4129 p_price_list_line_tbl(1).end_date_active := p_deliv_rec.actual_avail_to_date;
4130 p_price_list_line_tbl(1).organization_id := p_deliv_rec.inventory_item_org_id;
4131 p_price_list_line_tbl(1).operation :=QP_GLOBALS.G_OPR_CREATE;
4132 p_price_list_line_tbl(1).automatic_flag := 'Y';
4133 -- if p_deliv_rec.PRICELIST_LIST_PRICE is NULL or p_deliv_rec.PRICELIST_LIST_PRICE = FND_API.g_miss_num then
4134 p_price_list_line_tbl(1).list_price := 0;
4135 --else
4136 --p_price_list_line_tbl(1).list_price := p_deliv_rec.PRICELIST_LIST_PRICE;
4137 --end if;
4138
4139 IF (AMS_DEBUG_HIGH_ON) THEN
4140 AMS_Utility_PVT.debug_message(l_full_name ||': create pricelist line...');
4141 END IF;
4142
4143 QP_PRICE_LIST_PUB.Process_Price_List(
4144 p_api_version_number => 1.0,
4145 p_init_msg_list => FND_API.g_false,
4146 p_return_values => FND_API.G_TRUE,
4147 p_commit => FND_API.g_false,
4148 x_return_status => l_return_status,
4149 x_msg_count => l_msg_count,
4150 x_msg_data => l_msg_data,
4151 p_price_list_rec => p_price_list_rec,
4152 p_price_list_val_rec => p_price_list_val_rec,
4153 p_price_list_line_tbl => p_price_list_line_tbl ,
4154 p_price_list_line_val_tbl => p_price_list_line_val_tbl ,
4155 p_qualifiers_tbl => p_qualifiers_tbl,
4156 p_qualifiers_val_tbl => p_qualifiers_val_tbl,
4157 p_pricing_attr_tbl => p_pricing_attr_tbl,
4158 p_pricing_attr_val_tbl => p_pricing_attr_val_tbl,
4159 x_price_list_rec => l_price_list_rec,
4160 x_price_list_val_rec => l_price_list_val_rec,
4161 x_price_list_line_tbl => l_price_list_line_tbl ,
4162 x_price_list_line_val_tbl => l_price_list_line_val_tbl ,
4163 x_qualifiers_tbl => l_qualifiers_tbl,
4164 x_qualifiers_val_tbl => l_qualifiers_val_tbl,
4165 x_pricing_attr_tbl => l_pricing_attr_tbl,
4166 x_pricing_attr_val_tbl => l_pricing_attr_val_tbl
4167 );
4168
4169 x_pricelist_line_id := l_PRICE_LIST_LINE_tbl(1).list_line_id;
4170 x_return_status := l_return_status;
4171
4172 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4173 RAISE FND_API.g_exc_unexpected_error;
4174 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
4175 RAISE FND_API.g_exc_error;
4176 END IF;
4177
4178 IF p_commit = FND_API.g_true THEN
4179 COMMIT WORK;
4180 END IF;
4181 END create_pricelist_line;
4182
4183 PROCEDURE create_jtf_attachment
4184 (
4185 p_used_by IN VARCHAR2,
4186 p_used_by_id IN NUMBER,
4187 p_file_id IN NUMBER,
4188 p_file_name IN VARCHAR2,
4189 p_att_type IN VARCHAR2,
4190 p_file_ver IN VARCHAR2,
4191 x_return_status OUT NOCOPY VARCHAR2,
4192 x_msg_count OUT NOCOPY NUMBER,
4193 x_msg_data OUT NOCOPY VARCHAR2,
4194 x_att_id OUT NOCOPY NUMBER
4195 ) IS
4196
4197 l_api_name CONSTANT VARCHAR2(30) := 'create_jtf_attachment';
4198
4199 l_api_version NUMBER := 1.0;
4200 l_init_msg_list VARCHAR2(1) := FND_API.g_false;
4201 l_commit VARCHAR2(1) := FND_API.g_false;
4202 l_validation_level NUMBER := FND_API.g_valid_level_full;
4203 l_return_status VARCHAR2(1);
4204 l_msg_count NUMBER;
4205 l_msg_data VARCHAR2(2000);
4206
4207 l_att_rec jtf_amv_attachment_pub.act_attachment_rec_type;
4208 l_att_id NUMBER;
4209
4210 BEGIN
4211
4212 x_return_status := FND_API.g_ret_sts_success;
4213
4214 l_att_rec.attachment_used_by := p_used_by;
4215 l_att_rec.attachment_used_by_id := p_used_by_id;
4216 l_att_rec.file_id := p_file_id;
4217 l_att_rec.file_name := p_file_name;
4218 l_att_rec.attachment_type := p_att_type;
4219 l_att_rec.version := p_file_ver;
4220 IF l_att_rec.attachment_used_by = 'AMS_DELV' THEN
4221 l_att_rec.application_id := 530;
4222 ELSE
4223 l_att_rec.application_id := 520;
4224 END IF;
4225 l_att_rec.owner_user_id := FND_GLOBAL.user_id;
4226 l_att_rec.can_fulfill_electronic_flag := 'Y';
4227
4228 jtf_amv_attachment_pub.create_act_attachment(
4229 p_api_version => l_api_version,
4230 p_init_msg_list => l_init_msg_list,
4231 p_commit => l_commit,
4232 p_validation_level => l_validation_level,
4233 x_return_status => l_return_status,
4234 x_msg_count => l_msg_count,
4235 x_msg_data => l_msg_data,
4236 p_act_attachment_rec => l_att_rec,
4237 x_act_attachment_id => l_att_id
4238 );
4239
4240 x_att_id := l_att_id;
4241 x_return_status := l_return_status;
4242 x_msg_count := l_msg_count;
4243 x_msg_data := l_msg_data;
4244
4245 END create_jtf_attachment;
4246
4247
4248 -----------------------------------------------------------------
4249 PROCEDURE Deliverable_Cancellation
4250 (p_deliverable_rec IN deliv_rec_type,
4251 x_return_status OUT NOCOPY VARCHAR2 )
4252 IS
4253 l_api_name CONSTANT VARCHAR2(30) := 'Deliverable_Cancellation';
4254 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name||'.'||l_api_name;
4255 l_using_object_type CONSTANT VARCHAR2(30) := 'DELV';
4256 l_item_type CONSTANT VARCHAR2(30) := 'AMSAPRV';
4257 l_workflowprocess CONSTANT VARCHAR2(30) := 'AMS_DELV_CANCELLATION';
4258 l_master_object_id NUMBER;
4259 l_master_object_type VARCHAR2(30);
4260 l_user_id NUMBER;
4261 l_resource_id NUMBER;
4262 l_object_name VARCHAR2(240);
4263 l_object_type_name VARCHAR2(240);
4264
4265 l_count NUMBER := 0 ;
4266
4267 CURSOR c_parent_deliv(p_deliverable_id IN NUMBER) IS
4268 SELECT DISTINCT master_object_id,
4269 master_object_type
4270 FROM ams_object_associations
4271 WHERE using_object_type = l_using_object_type
4272 AND using_object_id = p_deliverable_id;
4273
4274 l_parent_deliv_rec c_parent_deliv%ROWTYPE;
4275
4276 CURSOR c_camp(l_master_object_id IN NUMBER) IS
4277 SELECT owner_user_id ,campaign_name
4278 FROM ams_campaigns_vl
4279 WHERE campaign_id = l_master_object_id;
4280
4281
4282 CURSOR c_eveh(l_master_object_id IN NUMBER) IS
4283 SELECT owner_user_id, event_header_name
4284 FROM ams_event_headers_vl
4285 WHERE event_header_id = l_master_object_id;
4286
4287 CURSOR c_eveo(l_master_object_id IN NUMBER) IS
4288 SELECT owner_user_id, event_offer_name
4289 FROM ams_event_offers_vl
4290 WHERE event_offer_id = l_master_object_id;
4291
4292 BEGIN
4293 x_return_status := FND_API.g_ret_sts_success;
4294 OPEN c_parent_deliv(p_deliverable_rec.deliverable_id);
4295 LOOP
4296 FETCH c_parent_deliv INTO l_parent_deliv_rec;
4297 EXIT WHEN c_parent_deliv%NOTFOUND;
4298 l_count := l_count + 1;
4299 l_master_object_id := l_parent_deliv_rec.master_object_id;
4300 l_master_object_type := l_parent_deliv_rec.master_object_type;
4301 IF l_master_object_type ='CAMP' THEN
4302 OPEN c_camp(l_master_object_id);
4303 FETCH c_camp INTO l_user_id, l_object_name;
4304 CLOSE c_camp;
4305 ELSIF l_master_object_type ='EVEH' THEN
4306 OPEN c_eveh(l_master_object_id);
4307 FETCH c_eveh INTO l_user_id, l_object_name;
4308 CLOSE c_eveh;
4309 ELSIF l_master_object_type ='EVEO' THEN
4310 OPEN c_eveo(l_master_object_id);
4311 FETCH c_eveo INTO l_user_id, l_object_name;
4312 CLOSE c_eveo;
4313 END IF;
4314
4315 l_object_type_name := AMS_UTILITY_PVT.get_lookup_meaning('AMS_SYS_ARC_QUALIFIER', l_master_object_type);
4316 AMS_approval_pvt.delvStartProcess(
4317 p_deliverable_id => p_deliverable_rec.deliverable_id,
4318 p_deliverable_name => p_deliverable_rec.deliverable_name,
4319 p_object_version_number => p_deliverable_rec.object_version_number,
4320 p_usedby_object_id => l_parent_deliv_rec.master_object_id,
4321 p_usedby_object_name => l_object_name,
4322 p_usedby_object_type_name => l_object_type_name,
4323 p_requester_userid => AMS_UTILITY_PVT.get_resource_id(FND_GLOBAL.USER_ID),
4324 p_deliverable_userid => l_user_id,
4325 p_workflowprocess => l_workflowprocess,
4326 p_item_type => l_item_type
4327 );
4328 END LOOP;
4329 CLOSE c_parent_deliv;
4330
4331 /*
4332 --if the deliverable is not associated to any objects,when
4333 --the owner or team member changes the status to cancelled
4334 --the notification will be sent to the owner of deliverable.
4335 */
4336
4337 IF l_count = 0 THEN
4338
4339 l_user_id := p_deliverable_rec.owner_user_id;
4340 l_master_object_id := p_deliverable_rec.deliverable_id;
4341 l_object_name := p_deliverable_rec.deliverable_name;
4342
4343 l_object_type_name := AMS_UTILITY_PVT.get_lookup_meaning('AMS_SYS_ARC_QUALIFIER', 'DELV');
4344
4345 AMS_approval_pvt.delvStartProcess(
4346 p_deliverable_id => p_deliverable_rec.deliverable_id,
4347 p_deliverable_name => p_deliverable_rec.deliverable_name,
4348 p_object_version_number => p_deliverable_rec.object_version_number,
4349 p_usedby_object_id => l_master_object_id,
4350 p_usedby_object_name => l_object_name,
4351 p_usedby_object_type_name => l_object_type_name,
4352 p_requester_userid => AMS_UTILITY_PVT.get_resource_id(FND_GLOBAL.USER_ID),
4353 p_deliverable_userid => l_user_id,
4354 p_workflowprocess => l_workflowprocess,
4355 p_item_type => l_item_type
4356 );
4357 END IF;
4358 /*
4359 EXCEPTION
4360 WHEN others THEN
4361 IF c_parent_deliv%ISOPEN THEN
4362 CLOSE c_parent_deliv;
4363 END IF;
4364 x_return_status := FND_API.g_ret_sts_unexp_error ;
4365 IF (AMS_DEBUG_HIGH_ON) THEN
4366
4367 AMS_UTILITY_PVT.debug_message(l_full_name||' Unhandled Error');
4368 END IF;
4369 */
4370 END Deliverable_Cancellation;
4371
4372 -------------------------------------------------------------------
4373 -- PROCEDURE
4374 -- check_periods
4375 --
4376 -------------------------------------------------------------------
4377 PROCEDURE check_periods(
4378 p_deliv_Rec IN deliv_rec_type
4379 ,x_deliverable_calendar OUT NOCOPY VARCHAR2
4380 ,x_return_status OUT NOCOPY VARCHAR2)
4381 IS
4382
4383 CURSOR c_get_period_dets(deliv_id IN NUMBER)
4384 IS SELECT start_period_name
4385 ,end_period_name
4386 ,deliverable_calendar
4387 FROM ams_deliverables_all_b
4388 WHERE deliverable_id = deliv_id;
4389
4390 l_get_period_cur c_get_period_Dets%ROWTYPE;
4391 l_deliverable_calendar VARCHAR2(15) := FND_PROFILE.value('AMS_CAMPAIGN_DEFAULT_CALENDER');
4392
4393
4394 BEGIN
4395
4396 x_return_status := FND_API.g_ret_sts_success;
4397
4398 OPEN c_get_period_dets(p_deliv_rec.deliverable_id);
4399 FETCH c_get_period_dets INTO l_get_period_cur;
4400 CLOSE c_get_period_dets;
4401
4402 IF ( (NVL(l_get_period_cur.start_period_name,'%%') <> NVL(p_deliv_rec.start_period_name,'%%') )
4403 OR ( NVL(l_get_period_cur.end_period_name,'$$') <> NVL(p_deliv_rec.end_period_name,'$$') ))
4404 THEN
4405
4406 IF ( NVL(l_get_period_cur.deliverable_calendar,'**') <> NVL(l_deliverable_calendar, '**') ) THEN
4407 x_deliverable_calendar := FND_PROFILE.value('AMS_CAMPAIGN_DEFAULT_CALENDER');
4408 ELSE
4409 x_deliverable_calendar := l_get_period_cur.deliverable_calendar;
4410 END IF;
4411 ELSE
4412 x_deliverable_calendar := l_get_period_cur.deliverable_calendar;
4413 END IF;
4414
4415
4416 EXCEPTION
4417 WHEN OTHERS THEN
4418 x_return_status := FND_API.g_ret_sts_unexp_error ;
4419 END check_periods;
4420
4421 -------------------------------------------------------------------
4422 -- PROCEDURE
4423 -- check_owner_id
4424 --
4425 --
4426 -------------------------------------------------------------------
4427
4428 PROCEDURE check_owner_id
4429 (
4430 p_deliv_rec IN deliv_rec_type,
4431 x_return_status OUT NOCOPY VARCHAR2
4432 ) IS
4433
4434
4435 CURSOR c_owner_id(deliv_id IN NUMBER)
4436 IS
4437 SELECT owner_user_id
4438 FROM ams_deliverables_all_b
4439 WHERE deliverable_id = deliv_id;
4440 l_owner_user_id NUMBER;
4441 l_resource_id NUMBER := AMS_UTILITY_PVT.get_resource_id(FND_GLOBAL.User_id);
4442
4443 BEGIN
4444
4445 x_return_status := FND_API.g_ret_sts_success;
4446
4447
4448 IF p_deliv_rec.owner_user_id <> FND_API.g_miss_num THEN
4449
4450 OPEN c_owner_id(p_deliv_rec.deliverable_id);
4451 FETCH c_owner_id INTO l_owner_user_id;
4452 CLOSE c_owner_id;
4453
4454 IF ( (AMS_ACCESS_PVT.CHECK_ADMIN_ACCESS(p_deliv_rec.owner_user_id) = FALSE)
4455 AND (l_owner_user_id <> l_resource_id)
4456 AND (l_owner_user_id <> p_deliv_rec.owner_user_id))
4457 THEN
4458
4459 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
4460 THEN
4461 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_CANT_CHANGE_OWNER');
4462 FND_MSG_PUB.add;
4463 END IF;
4464
4465 x_return_status := FND_API.g_ret_sts_error;
4466 RETURN;
4467
4468 END IF;
4469 END IF;
4470
4471
4472 END check_owner_id;
4473
4474 -------------------------------------------------------------------
4475 -- PROCEDURE
4476 -- check_budget_lines
4477 --
4478 --
4479 -------------------------------------------------------------------
4480
4481 PROCEDURE check_budget_lines
4482 (
4483 p_deliv_rec IN deliv_rec_type,
4484 x_return_status OUT NOCOPY VARCHAR2
4485 ) IS
4486
4487
4488
4489
4490 CURSOR get_currency_code(deliv_id IN NUMBER)
4491 IS
4492 SELECT currency_code
4493 FROM ams_deliverables_all_b
4494 WHERE deliverable_id = deliv_id;
4495
4496 CURSOR check_budget(deliv_id IN NUMBER)
4497 IS
4498 SELECT 'Y'
4499 FROM ams_act_budgets
4500 WHERE arc_act_budget_used_by='DELV'
4501 AND act_budget_used_by_id = deliv_id;
4502
4503 l_currency_code VARCHAR2(15);
4504 l_budget_lines VARCHAR2(1) := 'N';
4505
4506 BEGIN
4507
4508 x_return_status := FND_API.g_ret_sts_success;
4509
4510 IF p_deliv_rec.currency_code <> FND_API.g_miss_char THEN
4511
4512 OPEN get_currency_code(p_deliv_rec.deliverable_id);
4513 FETCH get_currency_code INTO l_currency_code;
4514 CLOSE get_currency_code;
4515
4516 IF (l_currency_code <> p_deliv_rec.currency_code) THEN
4517 OPEN check_budget(p_deliv_rec.deliverable_id);
4518 FETCH check_budget INTO l_budget_lines;
4519 CLOSE check_budget;
4520
4521 IF l_budget_lines = 'Y' THEN
4522 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
4523 THEN
4524 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_CANT_CHANGE_CURRENCY');
4525 FND_MSG_PUB.add;
4526 END IF;
4527 x_return_status := FND_API.g_ret_sts_error;
4528 RETURN;
4529 END IF;
4530 END IF;
4531 END IF;
4532 IF (AMS_DEBUG_HIGH_ON) THEN
4533 AMS_UTILITY_PVT.debug_message('check budget lines is checked with no errors');
4534 END IF;
4535 END check_budget_lines;
4536
4537 -------------------------------------------------------------------------
4538 -- Function
4539 -- Approval_required_flag
4540 --
4541 --
4542 --------------------------------------------------------------------------
4543 FUNCTION Approval_Required_Flag
4544 ( p_custom_setup_id IN NUMBER ,
4545 p_approval_type IN VARCHAR2
4546 )
4547 RETURN VARCHAR2 IS
4548 CURSOR c_custom_attr IS
4549 SELECT attr_available_flag
4550 FROM ams_custom_setup_attr
4551 WHERE custom_setup_id = p_custom_setup_id
4552 AND object_attribute = p_approval_type ;
4553
4554 l_flag VARCHAR2(1) ;
4555 BEGIN
4556
4557 OPEN c_custom_attr;
4558 FETCH c_custom_attr INTO l_flag ;
4559 CLOSE c_custom_attr ;
4560
4561 IF l_flag = 'Y' THEN
4562 l_flag := FND_API.g_true;
4563 ELSIF l_flag = 'N' THEN
4564 l_flag := FND_API.g_false;
4565 END IF;
4566
4567 RETURN l_flag ;
4568
4569 END Approval_Required_Flag;
4570
4571 -------------------------------------------------------------------
4572 -- PROCEDURE
4573 -- check_budget_lines
4574 --
4575 -- 02/25/02 musman created
4576 -------------------------------------------------------------------
4577
4578
4579 PROCEDURE creat_inv_item
4580 (
4581 p_deliv_rec IN deliv_rec_type,
4582 x_inv_id OUT NOCOPY NUMBER,
4583 x_org_id OUT NOCOPY NUMBER,
4584 x_return_status OUT NOCOPY VARCHAR2,
4585 x_msg_count OUT NOCOPY NUMBER,
4586 x_msg_data OUT NOCOPY VARCHAR2
4587
4588 )
4589 IS
4590
4591 l_full_name VARCHAR2(40) := 'Private creat_inv_item';
4592
4593 -- Inventory
4594 inv_creation_error EXCEPTION;
4595
4596 l_item_rec AMS_ITEM_OWNER_PVT.ITEM_REC_TYPE; -- INV_Item_GRP.Item_rec_type;
4597 x_item_rec AMS_ITEM_OWNER_PVT.ITEM_REC_TYPE; --INV_Item_GRP.Item_rec_type;
4598 x_error_tbl AMS_ITEM_OWNER_PVT.Error_tbl_type; --INV_Item_GRP.Error_tbl_type;
4599 l_item_owner_rec AMS_ITEM_OWNER_PVT.ITEM_OWNER_Rec_Type;
4600
4601 l_qp_profile varchar2(1) := FND_PROFILE.Value('AMS_QP_PRICING_CALLOUT');
4602 l_org_Id NUMBER := FND_PROFILE.Value('AMS_ITEM_ORGANIZATION_ID');
4603
4604 x_item_return_status Varchar2(1) ;
4605 x_item_owner_id NUMBER;
4606
4607 BEGIN
4608
4609 x_return_status := FND_API.g_ret_sts_success;
4610 -- Begin Inventory creation
4611
4612 IF l_qp_profile IS NULL THEN
4613 l_qp_profile := 'N';
4614 END IF;
4615
4616
4617 IF (AMS_DEBUG_HIGH_ON) THEN
4618 AMS_Utility_PVT.debug_message(l_full_name ||' orgID:'||p_deliv_rec.inventory_item_org_id);
4619 AMS_Utility_PVT.debug_message(l_full_name ||' itemNumber:'||p_deliv_rec.item_number);
4620 AMS_Utility_PVT.debug_message(l_full_name ||' kitFlag:'||p_deliv_rec.kit_Flag);
4621 AMS_Utility_PVT.debug_message(l_full_name ||' Price Profile :'||l_qp_profile);
4622 END IF;
4623
4624 IF (p_deliv_rec.item_number IS NULL
4625 OR p_deliv_rec.item_number = FND_API.g_miss_char)
4626 THEN
4627 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
4628 THEN
4629 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_ENTER_PROPER_PARTNO');
4630 FND_MSG_PUB.add;
4631 END IF;
4632 x_return_status := FND_API.g_ret_sts_error;
4633 RETURN;
4634 END IF;
4635
4636 IF (l_org_Id IS NULL)
4637 THEN
4638 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
4639 THEN
4640 FND_MESSAGE.set_name('AMS', 'AMS_PROD_PROFILE_TIP');
4641 FND_MSG_PUB.add;
4642 END IF;
4643 x_return_status := FND_API.g_ret_sts_error;
4644 RETURN;
4645 END IF;
4646
4647
4648 l_item_owner_rec.is_master_item := 'Y';
4649 l_item_owner_rec.owner_id := p_deliv_rec.owner_user_id;
4650
4651 l_item_rec.item_number := p_deliv_rec.item_number;
4652 --l_item_rec.segment1 := 'DELIV'||p_deliv_rec.deliverable_id;
4653 l_item_rec.organization_id := FND_PROFILE.Value ('AMS_ITEM_ORGANIZATION_ID'); -- new org_id
4654
4655 l_item_rec.description := 'DELIV-'||p_deliv_rec.deliverable_name;
4656 l_item_rec.long_description := 'DELIV-'||p_deliv_rec.description;
4657 l_item_rec.collateral_flag := 'Y';
4658 l_item_rec.costing_enabled_flag := 'Y';
4659 l_item_rec.customer_order_flag := 'Y';
4660 l_item_rec.customer_order_enabled_flag := 'Y';
4661 l_item_rec.shippable_item_flag := 'Y';
4662
4663 IF (AMS_DEBUG_HIGH_ON)
4664 THEN
4665 AMS_Utility_PVT.debug_message(l_full_name ||' Cal To Inv API ');
4666 AMS_Utility_PVT.debug_message(l_full_name ||' Org Id '||l_item_rec.organization_id);
4667 AMS_Utility_PVT.debug_message(l_full_name ||' Item Number '|| l_item_rec.item_number);
4668 AMS_Utility_PVT.debug_message(l_full_name ||'Desc '||l_item_rec.description );
4669 END IF;
4670
4671 AMS_ITEM_OWNER_PVT.Create_item_owner(
4672 P_Api_Version_Number => 1.0,
4673 X_Return_Status => x_return_status,
4674 X_Msg_Count => x_msg_count,
4675 X_Msg_Data => x_msg_data,
4676 P_ITEM_OWNER_Rec => l_item_owner_rec,
4677 X_ITEM_OWNER_ID => x_item_owner_id , --- for create api
4678 P_ITEM_REC_In => l_item_rec,
4679 P_ITEM_REC_Out => x_item_rec,
4680 x_item_return_status => x_item_return_status,
4681 x_Error_tbl => x_error_tbl );
4682
4683 /*
4684 INV_Item_GRP.Create_Item
4685 ( p_commit => FND_API.g_false
4686 , p_validation_level => fnd_api.g_VALID_LEVEL_FULL
4687 , p_Item_rec => l_item_rec
4688 , x_Item_rec => x_item_rec
4689 , x_return_status => x_return_status
4690 , x_Error_tbl => x_error_tbl
4691 );
4692
4693 */
4694
4695 IF (AMS_DEBUG_HIGH_ON) THEN
4696 AMS_Utility_PVT.debug_message(l_full_name ||' Status of Inv API '||x_return_status);
4697 END IF;
4698
4699 IF ( x_return_status <> FND_API.g_ret_sts_success
4700 OR x_item_return_status <> FND_API.g_ret_sts_success )
4701 THEN
4702 RAISE inv_creation_error;
4703 ELSE
4704 x_inv_id := x_item_rec.inventory_item_id;
4705 x_org_id := x_item_rec.organization_id;
4706 END IF;
4707
4708 EXCEPTION
4709 WHEN inv_creation_error THEN
4710 IF x_item_return_status <> FND_API.g_ret_sts_success
4711 THEN
4712 x_msg_count := x_error_tbl.count;
4713 FOR i IN 1 .. x_error_tbl.count LOOP
4714 /*
4715 FND_MSG_PUB.count_and_get(
4716 p_encoded => FND_API.g_false,
4717 p_count => x_msg_count,
4718 p_data => x_error_tbl(i).message_text
4719 ); */
4720 IF x_error_tbl(i).message_name IS NOT NULL
4721 THEN
4722 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
4723 THEN
4724 FND_MESSAGE.set_name('INV', x_error_tbl(i).message_name);
4725 FND_MSG_PUB.add;
4726 END IF;
4727 END IF;
4728 IF (AMS_DEBUG_HIGH_ON) THEN
4729
4730 AMS_Utility_PVT.debug_message(l_full_name ||'the error text is '||x_error_tbl(i).message_text);
4731 END IF;
4732 END LOOP;
4733 END IF;
4734 x_return_status := FND_API.g_ret_sts_error;
4735 RETURN;
4736 END creat_inv_item;
4737
4738 -------------------------------------------------------------------
4739 -- PROCEDURE
4740 -- check_inactive_deliv
4741 --
4742 -- HISTORY
4743 -- 02/25/2002 musman@us Create
4744 -------------------------------------------------------------------
4745
4746 PROCEDURE check_inactive_deliv
4747 (
4748 p_deliv_rec IN deliv_rec_type,
4749 x_return_status OUT NOCOPY VARCHAR2
4750 )IS
4751
4752
4753
4754 CURSOR get_active_flag(deliv_id IN NUMBER)
4755 IS
4756 SELECT active_flag
4757 FROM ams_deliverables_all_b
4758 WHERE deliverable_id = deliv_id;
4759
4760 l_active_flag VARCHAR2(1) := 'Y';
4761
4762 BEGIN
4763
4764 x_return_status := FND_API.g_ret_sts_success;
4765
4766 OPEN get_active_flag(p_deliv_rec.deliverable_id);
4767 FETCH get_active_flag INTO l_active_flag;
4768 CLOSE get_active_flag;
4769
4770 IF l_active_flag = 'N'
4771 THEN
4772 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
4773 THEN
4774 FND_MESSAGE.set_name('AMS', 'AMS_DELIV_CANT_UPD_INACTIVE');
4775 FND_MSG_PUB.add;
4776 END IF;
4777 x_return_status := FND_API.g_ret_sts_error;
4778 RETURN;
4779 END IF;
4780
4781 IF (AMS_DEBUG_HIGH_ON) THEN
4782
4783
4784
4785 AMS_UTILITY_PVT.debug_message('check budget lines is checked with no errors');
4786
4787 END IF;
4788
4789 END check_inactive_deliv;
4790
4791 -------------------------------------------------------------------
4792 -- PROCEDURE
4793 -- check_inv_item
4794 --
4795 -- HISTORY
4796 -- 02/25/2002 musman@us Create
4797 -------------------------------------------------------------------
4798 PROCEDURE check_inv_item
4799 (
4800 p_deliv_rec IN deliv_rec_type,
4801 x_return_status OUT NOCOPY VARCHAR2
4802 )
4803 IS
4804
4805 CURSOR get_flag(inv_id IN NUMBER
4806 ,org_id IN NUMBER)
4807 IS
4808 SELECT collateral_flag
4809 ,costing_enabled_flag
4810 ,customer_order_flag
4811 ,customer_order_enabled_flag
4812 ,shippable_item_flag
4813 FROM mtl_system_items_b
4814 WHERE inventory_item_id = inv_id
4815 AND organization_id = org_id;
4816
4817 l_flag_rec get_flag%ROWTYPE;
4818
4819 l_active_flag VARCHAR2(1) := 'Y';
4820
4821 BEGIN
4822
4823 x_return_status := FND_API.g_ret_sts_success;
4824
4825 OPEN get_flag(p_deliv_rec.inventory_item_id,p_deliv_rec.inventory_item_org_id);
4826 FETCH get_flag INTO l_flag_rec;
4827 CLOSE get_flag;
4828
4829 IF (p_deliv_rec.can_fulfill_electronic_flag) = 'N'
4830 THEN /* this is reqd because for electronic delv picking up inv item only with collateral flag as 'Y' */
4831
4832 IF l_flag_rec.collateral_flag = 'N'
4833 OR l_flag_rec.costing_enabled_flag = 'N'
4834 OR l_flag_rec.customer_order_flag = 'N'
4835 OR l_flag_rec.customer_order_enabled_flag = 'N'
4836 OR l_flag_rec.shippable_item_flag = 'N'
4837 THEN
4838 AMS_UTILITY_PVT.error_message('AMS_DELV_API_BAD_INV');
4839 --Program Error: Inventory item passed to the API ,should hava the value set to "Y" for the following flags collateral_flag
4840 --,costing_enabled_flag,customer_order_flag,customer_order_enabled_flag,shippable_item_flag.
4841 x_return_status := FND_API.g_ret_sts_error;
4842 RETURN;
4843 END IF;
4844 ELSE
4845 IF l_flag_rec.collateral_flag = 'N'
4846 THEN
4847 AMS_UTILITY_PVT.error_message('AMS_DELV_API_BAD_INV_COLL');
4848 --Program Error: Inventory item passed to the API ,should have the value set to "Y" for collateral_flag
4849 x_return_status := FND_API.g_ret_sts_error;
4850 RETURN;
4851 END IF;
4852 END IF;
4853
4854
4855 IF (AMS_DEBUG_HIGH_ON) THEN
4856 AMS_UTILITY_PVT.debug_message('check inv item is checked with no errors');
4857 END IF;
4858
4859 END check_inv_item;
4860
4861
4862 END AMS_Deliverable_PVT;