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