[Home] [Help]
PACKAGE BODY: APPS.JTF_TASK_CONTACTS_PUB
Source
1 PACKAGE BODY jtf_task_contacts_pub AS
2 /* $Header: jtfptkcb.pls 120.3 2006/07/13 11:30:06 sbarat ship $ */
3 PROCEDURE do_unmark_primary_flag_create(
4 p_task_id IN NUMBER
5 ) ;
6
7 PROCEDURE do_unmark_primary_flag_update(
8 p_task_contact_id IN NUMBER
9 ) ;
10
11 PROCEDURE do_delete_cascade(
12 p_task_contact_id IN NUMBER
13 ) ;
14
15 ---------------------------------------
16 -- For fixing a bug 2644132
17 FUNCTION is_this_first_contact(p_task_id IN NUMBER)
18 RETURN BOOLEAN
19 IS
20 CURSOR c_task_contact (b_task_id NUMBER) IS
21 SELECT '1'
22 FROM jtf_task_contacts
23 WHERE task_id = b_task_id;
24
25 l_dummy VARCHAR2(1);
26 l_this_is_first BOOLEAN;
27 BEGIN
28 OPEN c_task_contact(p_task_id);
29 FETCH c_task_contact INTO l_dummy;
30
31 IF c_task_contact%NOTFOUND
32 THEN
33 l_this_is_first := TRUE;
34 ELSE
35 l_this_is_first := FALSE;
36 END IF;
37
38 CLOSE c_task_contact;
39
40 RETURN l_this_is_first;
41
42 END is_this_first_contact;
43 ---------------------------------------
44
45 PROCEDURE create_task_contacts (
46 p_api_version IN NUMBER,
47 p_init_msg_list IN VARCHAR2,
48 p_commit IN VARCHAR2,
49 p_task_id IN NUMBER,
50 p_task_number IN VARCHAR2,
51 p_contact_id IN NUMBER,
52 p_contact_type_code IN VARCHAR2,
53 p_escalation_notify_flag IN VARCHAR2,
54 p_escalation_requester_flag IN VARCHAR2,
55 x_task_contact_id OUT NOCOPY NUMBER,
56 x_return_status OUT NOCOPY VARCHAR2,
57 x_msg_data OUT NOCOPY VARCHAR2,
58 x_msg_count OUT NOCOPY NUMBER,
59 p_attribute1 IN VARCHAR2,
60 p_attribute2 IN VARCHAR2,
61 p_attribute3 IN VARCHAR2,
62 p_attribute4 IN VARCHAR2,
63 p_attribute5 IN VARCHAR2,
64 p_attribute6 IN VARCHAR2,
65 p_attribute7 IN VARCHAR2,
66 p_attribute8 IN VARCHAR2,
67 p_attribute9 IN VARCHAR2,
68 p_attribute10 IN VARCHAR2,
69 p_attribute11 IN VARCHAR2,
70 p_attribute12 IN VARCHAR2,
71 p_attribute13 IN VARCHAR2,
72 p_attribute14 IN VARCHAR2,
73 p_attribute15 IN VARCHAR2,
74 p_attribute_category IN VARCHAR2,
75 p_primary_flag IN VARCHAR2
76 )
77 IS
78 l_api_version CONSTANT NUMBER := 1.0;
79 l_api_name CONSTANT VARCHAR2(30)
80 := 'CREATE_TASK_CONTACTS';
81 l_rowid ROWID;
82 l_task_id jtf_tasks_b.task_id%TYPE;
83 l_task_contact_id jtf_task_contacts.task_contact_id%TYPE;
84 x CHAR;
85 l_person_id NUMBER;
86
87 l_primary_flag VARCHAR2(1) := p_primary_flag; -- For fixing a bug 2644132
88
89 CURSOR c_jtf_task_contacts (l_rowid IN ROWID)
90 IS
91 SELECT 1
92 FROM jtf_task_contacts
93 WHERE ROWID = l_rowid;
94 BEGIN
95 SAVEPOINT create_task_contacts_pub;
96 x_return_status := fnd_api.g_ret_sts_success;
97
98 IF NOT fnd_api.compatible_api_call (
99 l_api_version,
100 p_api_version,
101 l_api_name,
102 g_pkg_name
103 )
104 THEN
105 RAISE fnd_api.g_exc_unexpected_error;
106 END IF;
107
108 IF fnd_api.to_boolean (p_init_msg_list)
109 THEN
110 fnd_msg_pub.initialize;
111 END IF;
112
113 jtf_task_utl.validate_task (
114 x_return_status => x_return_status,
115 p_task_id => p_task_id,
116 p_task_number => p_task_number,
117 x_task_id => l_task_id
118 );
119
120 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
121 THEN
122 x_return_status := fnd_api.g_ret_sts_unexp_error;
123 RAISE fnd_api.g_exc_unexpected_error;
124 END IF;
125
126
127
128 jtf_task_utl.validate_missing_task_id (
129 p_task_id => p_task_id,
130 x_return_status => x_return_status
131 );
132
133 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
134 THEN
135 x_return_status := fnd_api.g_ret_sts_unexp_error;
136 RAISE fnd_api.g_exc_unexpected_error;
137 END IF;
138
139
140
141 jtf_task_utl.validate_missing_contact_id (
142 p_task_contact_id => p_contact_id,
143 x_return_status => x_return_status
144 );
145
146 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
147 THEN
148 x_return_status := fnd_api.g_ret_sts_unexp_error;
149 RAISE fnd_api.g_exc_unexpected_error;
150 END IF;
151
152
153
154 IF NOT jtf_task_utl.validate_lookup (
155 'JTF_TASK_CONTACT_TYPE',
156 NVL (p_contact_type_code, 'CUST'),
157 'Escalation Contact Point ( JTF_EC_CONTACT_TYPE)'
158 )
159 THEN
160 x_return_status := fnd_api.g_ret_sts_unexp_error;
161 RAISE fnd_api.g_exc_unexpected_error;
162 END IF;
163
164 jtf_task_utl.validate_contact (
165 p_contact_id => p_contact_id,
166 p_task_id => p_task_id,
167 p_contact_type_code => NVL (p_contact_type_code, 'CUST'),
168 x_return_status => x_return_status
169 );
170
171 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
172 THEN
173
174 x_return_status := fnd_api.g_ret_sts_unexp_error;
175 RAISE fnd_api.g_exc_unexpected_error;
176 END IF;
177
178 jtf_task_utl.validate_flag (
179 p_flag_name => jtf_task_utl.get_translated_lookup (
180 'JTF_TASK_TRANSLATED_MESSAGES',
181 'NOTIFICATION_FLAG'
182 ),
183 p_flag_value => p_escalation_notify_flag,
184 x_return_status => x_return_status
185 );
186
187 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
188 THEN
189 x_return_status := fnd_api.g_ret_sts_unexp_error;
190 RAISE fnd_api.g_exc_unexpected_error;
191 END IF;
192
193 jtf_task_utl.validate_flag (
194 p_flag_name => jtf_task_utl.get_translated_lookup (
195 'JTF_TASK_TRANSLATED_MESSAGES',
196 'ESCALATION_REQUESTOR_FLAG'
197 ),
198 p_flag_value => p_escalation_requester_flag,
199 x_return_status => x_return_status
200 );
201
202 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
203 THEN
204 x_return_status := fnd_api.g_ret_sts_unexp_error;
205 RAISE fnd_api.g_exc_unexpected_error;
206 END IF;
207
208 -- Added this call for bug# 5140139
209 jtf_task_utl.check_duplicate_contact (
210 p_contact_id => p_contact_id,
211 p_task_id => p_task_id,
212 p_contact_type_code => NVL (p_contact_type_code, 'CUST'),
213 p_task_contact_id => NULL,
214 x_return_status => x_return_status
215 );
216
217 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
218 THEN
219 x_return_status := fnd_api.g_ret_sts_unexp_error;
220 RAISE fnd_api.g_exc_unexpected_error;
221 END IF;
222
223 SELECT jtf_task_contacts_s.nextval
224 INTO l_task_contact_id
225 FROM dual;
226
227 --Unmark the previous task id with primary flag = 'Y'.
228 IF p_primary_flag = jtf_task_utl.g_yes THEN
229 do_unmark_primary_flag_create( l_task_id );
230 END IF;
231
232 -------------------------------------------
233 -- For fixing a bug 2644132
234 IF is_this_first_contact(p_task_id)
235 THEN
236 l_primary_flag := jtf_task_utl.g_yes;
237 END IF;
238 -------------------------------------------
239
240 jtf_task_contacts_pkg.insert_row (
241 x_rowid => l_rowid,
242 x_task_contact_id => l_task_contact_id,
243 x_attribute4 => p_attribute4,
244 x_attribute5 => p_attribute5,
245 x_attribute6 => p_attribute6,
246 x_attribute7 => p_attribute7,
247 x_attribute8 => p_attribute8,
248 x_attribute9 => p_attribute9,
249 x_attribute10 => p_attribute10,
250 x_attribute11 => p_attribute11,
251 x_attribute12 => p_attribute12,
252 x_attribute13 => p_attribute13,
253 x_attribute14 => p_attribute14,
254 x_attribute15 => p_attribute15,
255 x_attribute_category => p_attribute_category,
256 x_contact_id => p_contact_id,
257 x_attribute1 => p_attribute1,
258 x_attribute2 => p_attribute2,
259 x_attribute3 => p_attribute3,
260 x_task_id => p_task_id,
261 x_creation_date => SYSDATE,
262 x_created_by => jtf_task_utl.created_by,
263 x_last_update_date => SYSDATE,
264 x_last_updated_by => jtf_task_utl.updated_by,
265 x_last_update_login => jtf_task_utl.login_id,
266 x_contact_type_code => p_contact_type_code,
267 x_escalation_notify_flag => p_escalation_notify_flag,
268 x_escalation_requester_flag => p_escalation_requester_flag,
269 x_primary_flag => l_primary_flag -- For fixing a bug 2644132
270 );
271 OPEN c_jtf_task_contacts (l_rowid);
272 FETCH c_jtf_task_contacts INTO x;
273
274 IF c_jtf_task_contacts%NOTFOUND
275 THEN
276 x_return_status := fnd_api.g_ret_sts_unexp_error;
277 fnd_message.set_name ('JTF', 'JTF_TASK_CREATING_CONTACTS');
278 fnd_msg_pub.add;
279 RAISE fnd_api.g_exc_unexpected_error;
280 ELSE
281 x_task_contact_id := l_task_contact_id;
282 END IF;
283
284 -- ------------------------------------------------------------------------
285 -- Create reference to contact, fix for enh #1845501
286 -- ------------------------------------------------------------------------
287
288 jtf_task_utl.create_party_reference (
289 p_reference_from => 'CONTACT',
290 p_task_id => p_task_id,
291 p_party_type_code => p_contact_type_code,
292 p_party_id => p_contact_id,
293 x_msg_count => x_msg_count,
294 x_msg_data => x_msg_data,
295 x_return_status => x_return_status);
296
297 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
298 THEN
299 x_return_status := fnd_api.g_ret_sts_unexp_error;
300 RAISE fnd_api.g_exc_unexpected_error;
301 END IF;
302
303 IF fnd_api.to_boolean (p_commit)
304 THEN
305 COMMIT WORK;
306 END IF;
307
308 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
309 EXCEPTION
310 WHEN fnd_api.g_exc_unexpected_error
311 THEN
312 ROLLBACK TO create_task_contacts_pub;
313 x_return_status := fnd_api.g_ret_sts_unexp_error;
314 fnd_msg_pub.count_and_get (
315 p_count => x_msg_count,
316 p_data => x_msg_data
317 );
318 WHEN OTHERS
319 THEN
320 ROLLBACK TO create_task_contacts_pub;
321 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
322 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
323 x_return_status := fnd_api.g_ret_sts_unexp_error;
324 fnd_msg_pub.count_and_get (
325 p_count => x_msg_count,
326 p_data => x_msg_data
327 );
328 END;
329
330 PROCEDURE lock_task_contacts (
331 p_api_version IN NUMBER,
332 p_init_msg_list IN VARCHAR2,
333 p_commit IN VARCHAR2,
334 p_task_contact_id IN NUMBER,
335 p_object_version_number IN NUMBER,
336 x_return_status OUT NOCOPY VARCHAR2,
337 x_msg_data OUT NOCOPY VARCHAR2,
338 x_msg_count OUT NOCOPY NUMBER
339 )
340 IS
341 l_api_version CONSTANT NUMBER := 1.0;
342 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_TASK_CONTACTS';
343 resource_locked EXCEPTION;
344 PRAGMA EXCEPTION_INIT (resource_locked, -54);
345 BEGIN
346 SAVEPOINT lock_task_contacts_pub;
347 x_return_status := fnd_api.g_ret_sts_success;
348
349
350
351 IF NOT fnd_api.compatible_api_call (
352 l_api_version,
353 p_api_version,
354 l_api_name,
355 g_pkg_name
356 )
357 THEN
358 RAISE fnd_api.g_exc_unexpected_error;
359 END IF;
360
361 IF fnd_api.to_boolean (p_init_msg_list)
362 THEN
363 fnd_msg_pub.initialize;
364 END IF;
365
366 x_return_status := fnd_api.g_ret_sts_success;
367 jtf_task_contacts_pkg.lock_row (
368 x_task_contact_id => p_task_contact_id,
369 x_object_version_number => p_object_version_number);
370 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
371
372 EXCEPTION
373 WHEN resource_locked
374 THEN
375
376 ROLLBACK TO lock_task_contacts_pub;
377 fnd_message.set_name ('JTF', 'JTF_TASK_RESOURCE_LOCKED');
378 fnd_message.set_token ('P_LOCKED_RESOURCE', 'Contacts');
379 fnd_msg_pub.add;
380 x_return_status := fnd_api.g_ret_sts_unexp_error;
381 fnd_msg_pub.count_and_get (
382 p_count => x_msg_count,
383 p_data => x_msg_data
384 );
385 WHEN fnd_api.g_exc_unexpected_error
386 THEN
387
388 ROLLBACK TO lock_task_contacts_pub;
389 x_return_status := fnd_api.g_ret_sts_unexp_error;
390 fnd_msg_pub.count_and_get (
391 p_count => x_msg_count,
392 p_data => x_msg_data
393 );
394 WHEN OTHERS
395 THEN
396 ROLLBACK TO lock_task_contacts_pub;
397 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
398 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
399 fnd_msg_pub.add;
400 x_return_status := fnd_api.g_ret_sts_unexp_error;
401 fnd_msg_pub.count_and_get (
402 p_count => x_msg_count,
403 p_data => x_msg_data
404 );
405 END;
406
407 PROCEDURE update_task_contacts (
408 p_api_version IN NUMBER,
409 p_init_msg_list IN VARCHAR2,
410 p_commit IN VARCHAR2,
411 p_object_version_number IN OUT NOCOPY NUMBER,
412 p_task_contact_id IN NUMBER,
413 p_contact_id IN NUMBER,
414 p_contact_type_code IN VARCHAR2,
415 p_escalation_notify_flag IN VARCHAR2,
416 p_escalation_requester_flag IN VARCHAR2,
417 x_return_status OUT NOCOPY VARCHAR2,
418 x_msg_data OUT NOCOPY VARCHAR2,
419 x_msg_count OUT NOCOPY NUMBER,
420 p_attribute1 IN VARCHAR2,
421 p_attribute2 IN VARCHAR2,
422 p_attribute3 IN VARCHAR2,
423 p_attribute4 IN VARCHAR2,
424 p_attribute5 IN VARCHAR2,
425 p_attribute6 IN VARCHAR2,
426 p_attribute7 IN VARCHAR2,
427 p_attribute8 IN VARCHAR2,
428 p_attribute9 IN VARCHAR2,
429 p_attribute10 IN VARCHAR2,
430 p_attribute11 IN VARCHAR2,
431 p_attribute12 IN VARCHAR2,
432 p_attribute13 IN VARCHAR2,
433 p_attribute14 IN VARCHAR2,
434 p_attribute15 IN VARCHAR2,
435 p_attribute_category IN VARCHAR2,
436 p_primary_flag IN VARCHAR2
437 )
438 IS
439 l_api_version CONSTANT NUMBER := 1.0;
440 l_api_name CONSTANT VARCHAR2(30)
441 := 'UPDATE_TASK_CONTACTS';
442 l_task_contact_id jtf_task_contacts.task_contact_id%TYPE;
443 l_contact_id jtf_task_contacts.contact_id%TYPE;
444 l_task_id jtf_tasks_b.task_id%TYPE;
445 CURSOR c_task_contacts
446 IS
447 SELECT task_contact_id,
448 task_id,
449 DECODE (
450 p_contact_id,
451 fnd_api.g_miss_num, contact_id,
452 p_contact_id
453 ) contact_id,
454 DECODE (
455 p_contact_type_code,
456 fnd_api.g_miss_char, contact_type_code,
457 p_contact_type_code
458 ) contact_type_code,
459 DECODE (
460 p_escalation_notify_flag,
461 fnd_api.g_miss_char, escalation_notify_flag,
462 p_escalation_notify_flag
463 ) escalation_notify_flag,
464 DECODE (
465 p_escalation_requester_flag,
466 fnd_api.g_miss_char, escalation_requester_flag,
467 p_escalation_requester_flag
468 ) escalation_requester_flag,
469 DECODE (
470 p_attribute1,
471 fnd_api.g_miss_char, attribute1,
472 p_attribute1
473 ) attribute1,
474 DECODE (
475 p_attribute2,
476 fnd_api.g_miss_char, attribute2,
477 p_attribute2
478 ) attribute2,
479 DECODE (
480 p_attribute3,
481 fnd_api.g_miss_char, attribute3,
482 p_attribute3
483 ) attribute3,
484 DECODE (
485 p_attribute4,
486 fnd_api.g_miss_char, attribute4,
487 p_attribute4
488 ) attribute4,
489 DECODE (
490 p_attribute5,
491 fnd_api.g_miss_char, attribute5,
492 p_attribute5
493 ) attribute5,
494 DECODE (
495 p_attribute6,
496 fnd_api.g_miss_char, attribute6,
497 p_attribute6
498 ) attribute6,
499 DECODE (
500 p_attribute7,
501 fnd_api.g_miss_char, attribute7,
502 p_attribute7
503 ) attribute7,
504 DECODE (
505 p_attribute8,
506 fnd_api.g_miss_char, attribute8,
507 p_attribute8
508 ) attribute8,
509 DECODE (
510 p_attribute9,
511 fnd_api.g_miss_char, attribute9,
512 p_attribute9
513 ) attribute9,
514 DECODE (
515 p_attribute10,
516 fnd_api.g_miss_char, attribute10,
517 p_attribute10
518 ) attribute10,
519 DECODE (
520 p_attribute11,
521 fnd_api.g_miss_char, attribute11,
522 p_attribute11
523 ) attribute11,
524 DECODE (
525 p_attribute12,
526 fnd_api.g_miss_char, attribute12,
527 p_attribute12
528 ) attribute12,
529 DECODE (
530 p_attribute13,
531 fnd_api.g_miss_char, attribute13,
532 p_attribute13
533 ) attribute13,
534 DECODE (
535 p_attribute14,
536 fnd_api.g_miss_char, attribute14,
537 p_attribute14
538 ) attribute14,
539 DECODE (
540 p_attribute15,
541 fnd_api.g_miss_char, attribute15,
542 p_attribute15
543 ) attribute15,
544 DECODE (
545 p_attribute_category,
546 fnd_api.g_miss_char, attribute_category,
547 p_attribute_category
548 ) attribute_category,
549 DECODE (
550 p_primary_flag,
551 fnd_api.g_miss_char, primary_flag,
552 p_primary_flag
553 ) primary_flag
554 FROM jtf_task_contacts
555 WHERE task_contact_id = p_task_contact_id;
556
557 task_contacts c_task_contacts%ROWTYPE;
558 x NUMBER;
559
560 CURSOR con_con_orig (b_task_contact_id IN NUMBER)
561 IS
562 SELECT contact_id,
563 contact_type_code
564 FROM jtf_task_contacts
565 WHERE task_contact_id = b_task_contact_id;
566
567 l_orig_con_id jtf_task_contacts.contact_id%type;
568 l_orig_type_code jtf_task_contacts.contact_type_code%type;
569 BEGIN
570 SAVEPOINT update_task_contacts_pub;
571 x_return_status := fnd_api.g_ret_sts_success;
572
573 IF NOT fnd_api.compatible_api_call (
574 l_api_version,
575 p_api_version,
576 l_api_name,
577 g_pkg_name
578 )
579 THEN
580 RAISE fnd_api.g_exc_unexpected_error;
581 END IF;
582
583
584 IF fnd_api.to_boolean (p_init_msg_list)
585 THEN
586 fnd_msg_pub.initialize;
587 END IF;
588
589 x_return_status := fnd_api.g_ret_sts_success;
590
591
592
593
594
595 jtf_task_utl.validate_missing_contact_id (
596 p_task_contact_id => p_contact_id,
597 x_return_status => x_return_status
598 );
599
600 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
601 THEN
602
603 x_return_status := fnd_api.g_ret_sts_unexp_error;
604 RAISE fnd_api.g_exc_unexpected_error;
605 END IF;
606
607
608
609
610 OPEN c_task_contacts;
611 FETCH c_task_contacts INTO task_contacts;
612
613 IF c_task_contacts%NOTFOUND
614 THEN
615 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_CONTACTS');
616 fnd_msg_pub.add;
617 x_return_status := fnd_api.g_ret_sts_unexp_error;
618 RAISE fnd_api.g_exc_unexpected_error;
619 END IF;
620
621
622
623 IF NOT jtf_task_utl.validate_lookup(
624 'JTF_TASK_CONTACT_TYPE',
625 NVL (task_contacts.contact_type_code, 'CUST'),
626 'Escalation Contact Point( JTF_EC_CONTACT_TYPE)'
627 )
628 THEN
629 x_return_status := fnd_api.g_ret_sts_unexp_error;
630 RAISE fnd_api.g_exc_unexpected_error;
631 END IF;
632
633
634
635 BEGIN
636 SELECT task_id
637 INTO l_task_id
638 FROM jtf_task_contacts
639 WHERE task_contact_id = p_task_contact_id;
640 EXCEPTION
641 WHEN NO_DATA_FOUND
642 THEN
643 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_CONTACTS');
644 fnd_msg_pub.add;
645 x_return_status := fnd_api.g_ret_sts_unexp_error;
646 RAISE fnd_api.g_exc_unexpected_error;
647 END;
648
649
650 jtf_task_utl.validate_contact (
651 p_contact_id => task_contacts.contact_id,
652 p_task_id => task_contacts.task_id,
653 p_contact_type_code => task_contacts.contact_type_code,
654 x_return_status => x_return_status
655 );
656
657 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
658 THEN
659 x_return_status := fnd_api.g_ret_sts_unexp_error;
660 RAISE fnd_api.g_exc_unexpected_error;
661 END IF;
662
663
664
665 jtf_task_utl.validate_flag (
666 p_flag_name => jtf_task_utl.get_translated_lookup (
667 'JTF_TASK_TRANSLATED_MESSAGES',
668 'NOTIFICATION_FLAG'
669 ),
670 p_flag_value => task_contacts.escalation_notify_flag,
671 x_return_status => x_return_status
672 );
673
674 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
675 THEN
676 x_return_status := fnd_api.g_ret_sts_unexp_error;
677 RAISE fnd_api.g_exc_unexpected_error;
678 END IF;
679
680
681
682 jtf_task_utl.validate_flag (
683 p_flag_name => jtf_task_utl.get_translated_lookup (
684 'JTF_TASK_TRANSLATED_MESSAGES',
685 'ESCALATION_REQUESTOR_FLAG'
686 ),
687 p_flag_value => task_contacts.escalation_requester_flag,
688 x_return_status => x_return_status
689 );
690
691 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
692 THEN
693 x_return_status := fnd_api.g_ret_sts_unexp_error;
694 RAISE fnd_api.g_exc_unexpected_error;
695 END IF;
696
697 -- Added this call for bug# 5140139
698 jtf_task_utl.check_duplicate_contact(
699 p_contact_id => task_contacts.contact_id,
700 p_task_id => task_contacts.task_id,
701 p_contact_type_code => NVL (task_contacts.contact_type_code, 'CUST'),
702 p_task_contact_id => p_task_contact_id,
703 x_return_status => x_return_status
704 );
705
706 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
707 THEN
708 x_return_status := fnd_api.g_ret_sts_unexp_error;
709 RAISE fnd_api.g_exc_unexpected_error;
710 END IF;
711
712 jtf_task_contacts_pub.lock_task_contacts (
713 p_api_version => 1.0,
714 p_init_msg_list => fnd_api.g_false,
715 p_commit => fnd_api.g_false,
716 p_task_contact_id => p_task_contact_id,
717 p_object_version_number => p_object_version_number,
718 x_return_status => x_return_status,
719 x_msg_data => x_msg_data,
720 x_msg_count => x_msg_count
721 );
722
723 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
724 THEN
725 x_return_status := fnd_api.g_ret_sts_unexp_error;
726 RAISE fnd_api.g_exc_unexpected_error;
727 END IF;
728
729
730 --unmark the previous task id with primary flag = 'Y'.
731 IF p_primary_flag = jtf_task_utl.g_yes THEN
732 do_unmark_primary_flag_update( P_task_contact_id );
733
734 END IF;
735
736 -- ------------------------------------------------------------------------
737 -- Get the original contact_id and contact_type_code so we can update the
738 -- reference details if necessary
739 -- ------------------------------------------------------------------------
740
741 OPEN con_con_orig (p_task_contact_id);
742 FETCH con_con_orig INTO l_orig_con_id,
743 l_orig_type_code;
744
745 IF con_con_orig%NOTFOUND
746 THEN
747 RAISE fnd_api.g_exc_unexpected_error;
748 END IF;
749
750
751 jtf_task_contacts_pkg.update_row (
752 x_task_contact_id => p_task_contact_id,
753 x_object_version_number => p_object_version_number + 1,
754 x_attribute1 => task_contacts.attribute1,
755 x_attribute2 => task_contacts.attribute2,
756 x_attribute3 => task_contacts.attribute3,
757 x_attribute4 => task_contacts.attribute4,
758 x_attribute5 => task_contacts.attribute5,
759 x_attribute6 => task_contacts.attribute6,
760 x_attribute7 => task_contacts.attribute7,
761 x_attribute8 => task_contacts.attribute8,
762 x_attribute9 => task_contacts.attribute9,
763 x_attribute10 => task_contacts.attribute10,
764 x_attribute11 => task_contacts.attribute11,
765 x_attribute12 => task_contacts.attribute12,
766 x_attribute13 => task_contacts.attribute13,
767 x_attribute14 => task_contacts.attribute14,
768 x_attribute15 => task_contacts.attribute15,
769 x_attribute_category => task_contacts.attribute_category,
770 x_task_id => task_contacts.task_id,
771 x_contact_id => task_contacts.contact_id,
772 x_last_update_date => SYSDATE,
773 x_last_updated_by => jtf_task_utl.updated_by,
774 x_last_update_login => jtf_task_utl.login_id,
775 x_contact_type_code => task_contacts.contact_type_code,
776 x_escalation_notify_flag => task_contacts.escalation_notify_flag,
777 x_escalation_requester_flag => task_contacts.escalation_requester_flag,
778 x_primary_flag => task_contacts.primary_flag
779 );
780
781 l_contact_id := task_contacts.contact_id;
782 -- ------------------------------------------------------------------------
783 -- Update reference to contact if changed, fix enh #1845501
784 -- ------------------------------------------------------------------------
785 if (nvl(l_contact_id, 0) <> fnd_api.g_miss_num and
786 nvl(l_contact_id, 0) <> nvl(l_orig_con_id, 0)) then
787 -- delete the old one
788 jtf_task_utl.delete_party_reference(
789 p_reference_from => 'CONTACT',
790 p_task_id => l_task_id,
791 p_party_type_code => l_orig_type_code,
792 p_party_id => l_orig_con_id,
793 x_msg_count => x_msg_count,
794 x_msg_data => x_msg_data,
795 x_return_status => x_return_status);
796
797 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
798 THEN
799 x_return_status := fnd_api.g_ret_sts_unexp_error;
800 RAISE fnd_api.g_exc_unexpected_error;
801 END IF;
802 -- create a new one
803 jtf_task_utl.create_party_reference(
804 p_reference_from => 'CONTACT',
805 p_task_id => l_task_id,
806 p_party_type_code => task_contacts.contact_type_code,
807 p_party_id => l_contact_id,
808 x_msg_count => x_msg_count,
809 x_msg_data => x_msg_data,
810 x_return_status => x_return_status);
811
812 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
813 THEN
814 x_return_status := fnd_api.g_ret_sts_unexp_error;
815 RAISE fnd_api.g_exc_unexpected_error;
816 END IF;
817 end if;
818
819 IF con_con_orig%ISOPEN
820 THEN
821 CLOSE con_con_orig;
822 END IF;
823
824 IF fnd_api.to_boolean (p_commit)
825 THEN
826 COMMIT WORK;
827 END IF;
828
829 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
830 p_object_version_number := p_object_version_number + 1;
831 EXCEPTION
832 WHEN fnd_api.g_exc_unexpected_error
833 THEN
834 ROLLBACK TO update_task_contacts_pub;
835 x_return_status := fnd_api.g_ret_sts_unexp_error;
836 fnd_msg_pub.count_and_get (
837 p_count => x_msg_count,
838 p_data => x_msg_data
839 );
840 WHEN OTHERS
841 THEN
842 ROLLBACK TO update_task_contacts_pub;
843 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
844 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
845 x_return_status := fnd_api.g_ret_sts_unexp_error;
846 fnd_msg_pub.count_and_get (
847 p_count => x_msg_count,
848 p_data => x_msg_data
849 );
850 END;
851
852 PROCEDURE delete_task_contacts (
853 p_api_version IN NUMBER,
854 p_init_msg_list IN VARCHAR2,
855 p_commit IN VARCHAR2,
856 p_object_version_number IN NUMBER,
857 p_task_contact_id IN NUMBER,
858 x_return_status OUT NOCOPY VARCHAR2,
859 x_msg_data OUT NOCOPY VARCHAR2,
860 x_msg_count OUT NOCOPY NUMBER,
861 p_delete_cascade IN VARCHAR2
862 )
863 IS
864 l_api_version CONSTANT NUMBER := 1.0;
865 l_api_name CONSTANT VARCHAR2(30)
866 := 'DELETE_TASK_CONTACTS';
867 l_task_id jtf_tasks_b.task_id%TYPE;
868 l_contact_id jtf_task_contacts.contact_id%TYPE;
869 l_contact_type_code jtf_task_contacts.contact_type_code%TYPE;
870 CURSOR con_con_orig (b_task_contact_id IN NUMBER)
871 IS
872 SELECT contact_id,
873 contact_type_code
874 FROM jtf_task_contacts
875 WHERE task_contact_id = b_task_contact_id;
876 BEGIN
877 x_return_status := fnd_api.g_ret_sts_success;
878 SAVEPOINT delete_task_contacts_pvt;
879 x_return_status := fnd_api.g_ret_sts_success;
880
881 IF NOT fnd_api.compatible_api_call (
882 l_api_version,
883 p_api_version,
884 l_api_name,
885 g_pkg_name
886 )
887 THEN
888 RAISE fnd_api.g_exc_unexpected_error;
889 END IF;
890
891 IF fnd_api.to_boolean (p_init_msg_list)
892 THEN
893 fnd_msg_pub.initialize;
894 END IF;
895
896 x_return_status := fnd_api.g_ret_sts_success;
897
898 jtf_task_utl.validate_missing_contact_id (
899 p_task_contact_id => p_task_contact_id,
900 x_return_status => x_return_status
901 );
902
903 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
904 THEN
905 x_return_status := fnd_api.g_ret_sts_unexp_error;
906 RAISE fnd_api.g_exc_unexpected_error;
907 END IF;
908
909 BEGIN
910 SELECT task_id
911 INTO l_task_id
912 FROM jtf_task_contacts
913 WHERE task_contact_id = p_task_contact_id;
914 EXCEPTION
915 WHEN NO_DATA_FOUND
916 THEN
917 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_CONTACTS');
918 fnd_msg_pub.add;
919 x_return_status := fnd_api.g_ret_sts_unexp_error;
920 RAISE fnd_api.g_exc_unexpected_error;
921 END;
922
923
924 jtf_task_contacts_pub.lock_task_contacts (
925 p_api_version => 1.0,
926 p_init_msg_list => fnd_api.g_false,
927 p_commit => fnd_api.g_false,
928 p_task_contact_id => p_task_contact_id,
929 p_object_version_number => p_object_version_number,
930 x_return_status => x_return_status,
931 x_msg_data => x_msg_data,
932 x_msg_count => x_msg_count
933 );
934
935 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
936 THEN
937 x_return_status := fnd_api.g_ret_sts_unexp_error;
938 RAISE fnd_api.g_exc_unexpected_error;
939 END IF;
940
941 --Delete the associated contact points in JTF_TASK_PHONES
942 IF p_delete_cascade = jtf_task_utl.g_yes THEN
943 do_delete_cascade (
944 p_task_contact_id => p_task_contact_id
945 ) ;
946 END IF;
947
948 -- ------------------------------------------------------------------------
949 -- Get the original contact_id so we can delete the reference details
950 -- ------------------------------------------------------------------------
951
952 OPEN con_con_orig (p_task_contact_id);
953 FETCH con_con_orig INTO l_contact_id,
954 l_contact_type_code;
955
956 IF con_con_orig%NOTFOUND
957 THEN
958 RAISE fnd_api.g_exc_unexpected_error;
959 END IF;
960
961 jtf_task_contacts_pkg.delete_row (
962 x_task_contact_id => p_task_contact_id
963 );
964
965 -- ------------------------------------------------------------------------
966 -- Delete reference to contact, fix enh #1845501
967 -- ------------------------------------------------------------------------
968 jtf_task_utl.delete_party_reference(
969 p_reference_from => 'CONTACT',
970 p_task_id => l_task_id,
971 p_party_type_code => l_contact_type_code,
972 p_party_id => l_contact_id,
973 x_msg_count => x_msg_count,
974 x_msg_data => x_msg_data,
975 x_return_status => x_return_status);
976
977 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
978 THEN
979 x_return_status := fnd_api.g_ret_sts_unexp_error;
980 RAISE fnd_api.g_exc_unexpected_error;
981 END IF;
982
983 IF con_con_orig%ISOPEN
984 THEN
985 CLOSE con_con_orig;
986 END IF;
987
988 IF fnd_api.to_boolean (p_commit)
989 THEN
990 COMMIT WORK;
991 END IF;
992
993 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
994 EXCEPTION
995 WHEN fnd_api.g_exc_unexpected_error
996 THEN
997 ROLLBACK TO delete_task_contacts_pvt;
998 x_return_status := fnd_api.g_ret_sts_unexp_error;
999 fnd_msg_pub.count_and_get (
1000 p_count => x_msg_count,
1001 p_data => x_msg_data
1002 );
1003 WHEN OTHERS
1004 THEN
1005 ROLLBACK TO delete_task_contacts_pvt;
1006 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
1007 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
1008 x_return_status := fnd_api.g_ret_sts_unexp_error;
1009 fnd_msg_pub.count_and_get (
1010 p_count => x_msg_count,
1011 p_data => x_msg_data
1012 );
1013 END;
1014
1015
1016
1017 PROCEDURE do_unmark_primary_flag_create(
1018 p_task_id IN NUMBER
1019 ) IS
1020
1021 BEGIN
1022 -- unmark previous primary flag
1023 UPDATE jtf_task_contacts
1024 SET primary_flag = 'N'
1025 WHERE task_id = p_task_id
1026 AND primary_flag = 'Y';
1027
1028 END;
1029
1030 PROCEDURE do_unmark_primary_flag_update(
1031 p_task_contact_id IN NUMBER
1032
1033 ) IS
1034
1035 BEGIN
1036 -- unmark previous primary flag
1037 UPDATE jtf_task_contacts
1038 SET primary_flag = 'N'
1039 WHERE task_id = (SELECT task_id FROM jtf_task_contacts
1040 WHERE task_contact_id = p_task_contact_id)
1041 AND primary_flag = 'Y';
1042
1043 END;
1044
1045 PROCEDURE do_delete_cascade(
1046 p_task_contact_id IN NUMBER
1047 ) IS
1048
1049 l_task_phone_id jtf_task_phones.task_phone_id%TYPE;
1050
1051 CURSOR c_phone_id(p_task_contact_id NUMBER)
1052 IS
1053 SELECT task_phone_id
1054 FROM jtf_task_phones
1055 WHERE task_contact_id = p_task_contact_id
1056 AND owner_table_name = 'JTF_TASK_CONTACTS';
1057
1058 BEGIN
1059 OPEN c_phone_id(p_task_contact_id);
1060 LOOP
1061 FETCH c_phone_id INTO l_task_phone_id;
1062 EXIT WHEN c_phone_id%NOTFOUND;
1063
1064 DELETE FROM jtf_task_phones
1065 WHERE task_phone_id = l_task_phone_id;
1066 END LOOP;
1067 CLOSE c_phone_id;
1068 END;
1069
1070 END;