[Home] [Help]
PACKAGE BODY: APPS.JTF_TASK_PHONES_PUB
Source
1 PACKAGE BODY jtf_task_phones_pub AS
2 /* $Header: jtfptkpb.pls 115.26 2002/12/04 21:04:09 cjang ship $ */
3 g_pkg_name VARCHAR2(30) := 'JTF_TASK_PHONES_PUB';
4
5 PROCEDURE do_unmark_primary_flag_create (
6 p_task_contact_id IN NUMBER,
7 p_phone_id IN NUMBER,
8 p_owner_table_name IN VARCHAR2
9 );
10
11 PROCEDURE do_unmark_primary_flag_update (
12 p_task_phone_id IN NUMBER,
13 p_phone_id IN NUMBER,
14 l_contact_id IN NUMBER
15 );
16
17 ---------------------------------------
18 -- For fixing a bug 2644132
19 FUNCTION is_this_first_phone(p_task_contact_id IN NUMBER
20 ,p_owner_table_name IN VARCHAR2)
21 RETURN BOOLEAN
22 IS
23 CURSOR c_task_phone (b_task_contact_id NUMBER, b_owner_table_name VARCHAR2) IS
24 SELECT '1'
25 FROM jtf_task_phones
26 WHERE task_contact_id = b_task_contact_id
27 AND owner_table_name = b_owner_table_name;
28
29 l_dummy VARCHAR2(1);
30 l_this_is_first BOOLEAN;
31 BEGIN
32 OPEN c_task_phone(p_task_contact_id, p_owner_table_name);
33 FETCH c_task_phone INTO l_dummy;
34
35 IF c_task_phone%NOTFOUND
36 THEN
37 l_this_is_first := TRUE;
38 ELSE
39 l_this_is_first := FALSE;
40 END IF;
41 CLOSE c_task_phone;
42
43 RETURN l_this_is_first;
44
45 END is_this_first_phone;
46 ---------------------------------------
47
48 PROCEDURE create_task_phones (
49 p_api_version IN NUMBER,
50 p_init_msg_list IN VARCHAR2,
51 p_commit IN VARCHAR2,
52 p_task_contact_id IN NUMBER,
53 p_phone_id IN NUMBER,
54 x_task_phone_id OUT NOCOPY NUMBER,
55 x_return_status OUT NOCOPY VARCHAR2,
56 x_msg_data OUT NOCOPY VARCHAR2,
57 x_msg_count OUT NOCOPY NUMBER,
58 p_attribute1 IN VARCHAR2,
59 p_attribute2 IN VARCHAR2,
60 p_attribute3 IN VARCHAR2,
61 p_attribute4 IN VARCHAR2,
62 p_attribute5 IN VARCHAR2,
63 p_attribute6 IN VARCHAR2,
64 p_attribute7 IN VARCHAR2,
65 p_attribute8 IN VARCHAR2,
66 p_attribute9 IN VARCHAR2,
67 p_attribute10 IN VARCHAR2,
68 p_attribute11 IN VARCHAR2,
69 p_attribute12 IN VARCHAR2,
70 p_attribute13 IN VARCHAR2,
71 p_attribute14 IN VARCHAR2,
72 p_attribute15 IN VARCHAR2,
73 p_attribute_category IN VARCHAR2,
74 p_owner_table_name 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_PHONES';
81 l_rowid ROWID;
82 l_task_id jtf_tasks_b.task_id%TYPE;
83 l_contact_id jtf_task_contacts.contact_id%TYPE;
84 l_task_phone_id jtf_task_phones.task_phone_id%TYPE;
85 x CHAR;
86
87 l_primary_flag VARCHAR2(1) := p_primary_flag; -- For fixing a bug 2644132
88
89 CURSOR c_jtf_task_phones (l_rowid IN ROWID)
90 IS
91 SELECT 1
92 FROM jtf_task_phones
93 WHERE ROWID = l_rowid;
94 BEGIN
95 SAVEPOINT create_task_phones_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_phones_table (
114 p_owner_table_name => p_owner_table_name,
115 x_return_status => x_return_status
116 );
117
118 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
119 THEN
120 x_return_status := fnd_api.g_ret_sts_unexp_error;
121 RAISE fnd_api.g_exc_unexpected_error;
122 END IF;
123
124 jtf_task_utl.validate_missing_phone_id (
125 p_task_phone_id => p_phone_id,
126 x_return_status => x_return_status
127 );
128
129 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
130 THEN
131 x_return_status := fnd_api.g_ret_sts_unexp_error;
132 RAISE fnd_api.g_exc_unexpected_error;
133 END IF;
134
135 jtf_task_utl.validate_missing_contact_id (
136 p_task_contact_id => p_task_contact_id,
137 x_return_status => x_return_status
138 );
139
140 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
141 THEN
142 x_return_status := fnd_api.g_ret_sts_unexp_error;
143 RAISE fnd_api.g_exc_unexpected_error;
144 END IF;
145
146 jtf_task_utl.validate_contact_point (
147 p_contact_id => p_task_contact_id,
148 p_phone_id => p_phone_id,
149 x_return_status => x_return_status,
150 p_owner_table_name => p_owner_table_name
151 );
152
153 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
154 THEN
155 x_return_status := fnd_api.g_ret_sts_unexp_error;
156 RAISE fnd_api.g_exc_unexpected_error;
157 END IF;
158
159 jtf_task_utl.validate_flag (
160 p_flag_name => jtf_task_utl.get_translated_lookup (
161 'JTF_TASK_TRANSLATED_MESSAGES',
162 'PRIMARY_FLAG'
163 ),
164 p_flag_value => p_primary_flag,
165 x_return_status => x_return_status
166 );
167
168 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
169 THEN
170 x_return_status := fnd_api.g_ret_sts_unexp_error;
171 RAISE fnd_api.g_exc_unexpected_error;
172 END IF;
173
174 SELECT jtf_task_phones_s.nextval
175 INTO l_task_phone_id
176 FROM dual;
177
178 --Unmark the previous contact points with primary flag = 'Y'.
179 IF p_primary_flag = jtf_task_utl.g_yes
180 THEN
181 do_unmark_primary_flag_create (
182 p_task_contact_id,
183 p_phone_id,
184 p_owner_table_name
185 );
186 END IF;
187
188 -------------------------------------------
189 -- For fixing a bug 2644132
190 IF is_this_first_phone(p_task_contact_id, p_owner_table_name)
191 THEN
192 l_primary_flag := jtf_task_utl.g_yes;
193 END IF;
194 -------------------------------------------
195
196 jtf_task_phones_pkg.insert_row (
197 x_rowid => l_rowid,
198 x_task_phone_id => l_task_phone_id,
199 x_task_contact_id => p_task_contact_id,
200 x_attribute1 => p_attribute1,
201 x_attribute2 => p_attribute2,
202 x_attribute3 => p_attribute3,
203 x_attribute4 => p_attribute4,
204 x_attribute5 => p_attribute5,
205 x_attribute6 => p_attribute6,
206 x_attribute7 => p_attribute7,
207 x_attribute8 => p_attribute8,
208 x_attribute9 => p_attribute9,
209 x_attribute10 => p_attribute10,
210 x_attribute11 => p_attribute11,
211 x_attribute12 => p_attribute12,
212 x_attribute13 => p_attribute13,
213 x_attribute14 => p_attribute14,
214 x_attribute15 => p_attribute15,
215 x_attribute_category => p_attribute_category,
216 x_phone_id => p_phone_id,
217 x_creation_date => SYSDATE,
218 x_created_by => jtf_task_utl.created_by,
219 x_last_update_date => SYSDATE,
220 x_last_updated_by => jtf_task_utl.updated_by,
221 x_last_update_login => jtf_task_utl.login_id,
222 x_owner_table_name => p_owner_table_name,
223 x_primary_flag => l_primary_flag -- For fixing a bug 2644132
224 );
225 OPEN c_jtf_task_phones (l_rowid);
226 FETCH c_jtf_task_phones INTO x;
227
228 IF c_jtf_task_phones%NOTFOUND
229 THEN
230 x_return_status := fnd_api.g_ret_sts_unexp_error;
231 fnd_message.set_name ('JTF', 'JTF_TASK_CREATING_PHONE');
232 fnd_msg_pub.add;
233 RAISE fnd_api.g_exc_unexpected_error;
234 ELSE
235 x_task_phone_id := l_task_phone_id;
236 END IF;
237
238 IF fnd_api.to_boolean (p_commit)
239 THEN
240 COMMIT WORK;
241 END IF;
242
243 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
244 EXCEPTION
245 WHEN fnd_api.g_exc_unexpected_error
246 THEN
247 ROLLBACK TO create_task_phones_pub;
248 x_return_status := fnd_api.g_ret_sts_unexp_error;
249 fnd_msg_pub.count_and_get (
250 p_count => x_msg_count,
251 p_data => x_msg_data
252 );
253 WHEN OTHERS
254 THEN
255 ROLLBACK TO create_task_phones_pub;
256 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
257 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
258 fnd_msg_pub.add;
259 x_return_status := fnd_api.g_ret_sts_unexp_error;
260 fnd_msg_pub.count_and_get (
261 p_count => x_msg_count,
262 p_data => x_msg_data
263 );
264 END;
265
266 PROCEDURE lock_task_phones (
267 p_api_version IN NUMBER,
268 p_init_msg_list IN VARCHAR2,
269 p_commit IN VARCHAR2,
270 p_task_phone_id IN NUMBER,
271 p_object_version_number IN NUMBER,
272 x_return_status OUT NOCOPY VARCHAR2,
273 x_msg_data OUT NOCOPY VARCHAR2,
274 x_msg_count OUT NOCOPY NUMBER
275 )
276 IS
277 l_api_version CONSTANT NUMBER := 1.0;
278 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_TASK_PHONES';
279 resource_locked EXCEPTION;
280 PRAGMA EXCEPTION_INIT (resource_locked, -54);
281 BEGIN
282 SAVEPOINT lock_task_phones_pub;
283 x_return_status := fnd_api.g_ret_sts_success;
284
285 IF NOT fnd_api.compatible_api_call (
286 l_api_version,
287 p_api_version,
288 l_api_name,
289 g_pkg_name
290 )
291 THEN
292 RAISE fnd_api.g_exc_unexpected_error;
293 END IF;
294
295 IF fnd_api.to_boolean (p_init_msg_list)
296 THEN
297 fnd_msg_pub.initialize;
298 END IF;
299
300 x_return_status := fnd_api.g_ret_sts_success;
301 jtf_task_phones_pkg.lock_row (
302 x_task_phone_id => p_task_phone_id,
303 x_object_version_number => p_object_version_number
304 );
305 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
306 EXCEPTION
307 WHEN resource_locked
308 THEN
309 ROLLBACK TO lock_task_phones_pub;
310 fnd_message.set_name ('JTF', 'JTF_TASK_RESOURCE_LOCKED');
311 fnd_message.set_token ('P_LOCKED_RESOURCE', 'Contacts');
312 fnd_msg_pub.add;
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 fnd_api.g_exc_unexpected_error
319 THEN
320 ROLLBACK TO lock_task_phones_pub;
321 x_return_status := fnd_api.g_ret_sts_unexp_error;
322 fnd_msg_pub.count_and_get (
323 p_count => x_msg_count,
324 p_data => x_msg_data
325 );
326 WHEN OTHERS
327 THEN
328 ROLLBACK TO lock_task_phones_pub;
329 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
330 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
331 fnd_msg_pub.add;
332 x_return_status := fnd_api.g_ret_sts_unexp_error;
333 fnd_msg_pub.count_and_get (
334 p_count => x_msg_count,
335 p_data => x_msg_data
336 );
337 END;
338
339 PROCEDURE update_task_phones (
340 p_api_version IN NUMBER,
341 p_init_msg_list IN VARCHAR2,
342 p_commit IN VARCHAR2,
343 p_object_version_number IN OUT NOCOPY NUMBER,
344 p_task_phone_id IN NUMBER,
345 p_phone_id IN NUMBER,
346 x_return_status OUT NOCOPY VARCHAR2,
347 x_msg_data OUT NOCOPY VARCHAR2,
348 x_msg_count OUT NOCOPY NUMBER,
349 p_attribute1 IN VARCHAR2,
350 p_attribute2 IN VARCHAR2,
351 p_attribute3 IN VARCHAR2,
352 p_attribute4 IN VARCHAR2,
353 p_attribute5 IN VARCHAR2,
354 p_attribute6 IN VARCHAR2,
355 p_attribute7 IN VARCHAR2,
356 p_attribute8 IN VARCHAR2,
357 p_attribute9 IN VARCHAR2,
358 p_attribute10 IN VARCHAR2,
359 p_attribute11 IN VARCHAR2,
360 p_attribute12 IN VARCHAR2,
361 p_attribute13 IN VARCHAR2,
362 p_attribute14 IN VARCHAR2,
363 p_attribute15 IN VARCHAR2,
364 p_attribute_category IN VARCHAR2,
365 p_primary_flag IN VARCHAR2
366 )
367 IS
368 l_api_version CONSTANT NUMBER := 1.0;
369 l_api_name CONSTANT VARCHAR2(30)
370 := 'UPDATE_TASK_PHONES';
371 l_rowid ROWID;
372 l_task_contact_id jtf_task_contacts.task_contact_id%TYPE;
373 l_contact_id jtf_task_contacts.contact_id%TYPE;
374 x CHAR;
375
376 CURSOR c_task_phones
377 IS
378 SELECT DECODE (
379 p_attribute1,
380 fnd_api.g_miss_char, attribute1,
381 p_attribute1
382 ) attribute1,
383 DECODE (
384 p_attribute2,
385 fnd_api.g_miss_char, attribute2,
386 p_attribute2
387 ) attribute2,
388 DECODE (
389 p_attribute3,
390 fnd_api.g_miss_char, attribute3,
391 p_attribute3
392 ) attribute3,
393 DECODE (
394 p_attribute4,
395 fnd_api.g_miss_char, attribute4,
396 p_attribute4
397 ) attribute4,
398 DECODE (
399 p_attribute5,
400 fnd_api.g_miss_char, attribute5,
401 p_attribute5
402 ) attribute5,
403 DECODE (
404 p_attribute6,
405 fnd_api.g_miss_char, attribute6,
406 p_attribute6
407 ) attribute6,
411 p_attribute7
408 DECODE (
409 p_attribute7,
410 fnd_api.g_miss_char, attribute7,
412 ) attribute7,
413 DECODE (
414 p_attribute8,
415 fnd_api.g_miss_char, attribute8,
416 p_attribute8
417 ) attribute8,
418 DECODE (
419 p_attribute9,
420 fnd_api.g_miss_char, attribute9,
421 p_attribute9
422 ) attribute9,
423 DECODE (
424 p_attribute10,
425 fnd_api.g_miss_char, attribute10,
426 p_attribute10
427 ) attribute10,
428 DECODE (
429 p_attribute11,
430 fnd_api.g_miss_char, attribute11,
431 p_attribute11
432 ) attribute11,
433 DECODE (
434 p_attribute12,
435 fnd_api.g_miss_char, attribute12,
436 p_attribute12
437 ) attribute12,
438 DECODE (
439 p_attribute13,
440 fnd_api.g_miss_char, attribute13,
441 p_attribute13
442 ) attribute13,
443 DECODE (
444 p_attribute14,
445 fnd_api.g_miss_char, attribute14,
446 p_attribute14
447 ) attribute14,
448 DECODE (
449 p_attribute15,
450 fnd_api.g_miss_char, attribute15,
451 p_attribute15
452 ) attribute15,
453 DECODE (
454 p_attribute_category,
455 fnd_api.g_miss_char, attribute_category,
456 p_attribute_category
457 ) attribute_category,
458 DECODE (
459 p_primary_flag,
460 fnd_api.g_miss_char, primary_flag,
461 p_primary_flag
462 ) primary_flag,
463 owner_table_name
464 FROM jtf_task_phones
465 WHERE task_phone_id = p_task_phone_id;
466
467 task_phones c_task_phones%ROWTYPE;
468 l_owner_table_name VARCHAR2(30);
469 BEGIN
470 SAVEPOINT update_task_phones_pub;
471 x_return_status := fnd_api.g_ret_sts_success;
472
473 IF NOT fnd_api.compatible_api_call (
474 l_api_version,
475 p_api_version,
476 l_api_name,
477 g_pkg_name
478 )
479 THEN
480 RAISE fnd_api.g_exc_unexpected_error;
481 END IF;
482
483 IF fnd_api.to_boolean (p_init_msg_list)
484 THEN
485 fnd_msg_pub.initialize;
486 END IF;
487
488 IF p_task_phone_id IS NULL
489 THEN
490 fnd_message.set_name ('JTF', 'JTF_TASK_MISSING_PHONE_ID');
491 fnd_msg_pub.add;
492 x_return_status := fnd_api.g_ret_sts_unexp_error;
493 RAISE fnd_api.g_exc_unexpected_error;
494 END IF;
495
496 BEGIN
497 --- Added the nvl clause for backward compatibility
498 SELECT task_contact_id,
499 NVL (owner_table_name, 'JTF_TASK_CONTACTS')
500 INTO l_task_contact_id,
501 l_owner_table_name
502 FROM jtf_task_phones
503 WHERE task_phone_id = p_task_phone_id;
504 EXCEPTION
505 WHEN NO_DATA_FOUND
506 THEN
507 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_PHONE');
508 fnd_msg_pub.add;
509 x_return_status := fnd_api.g_ret_sts_unexp_error;
510 RAISE fnd_api.g_exc_unexpected_error;
511 END;
512
513 IF l_owner_table_name = 'JTF_TASK_CONTACTS'
514 THEN
515 BEGIN
516 SELECT contact_id
517 INTO l_contact_id
518 FROM jtf_task_contacts
519 WHERE task_contact_id = l_task_contact_id;
520 EXCEPTION
521 WHEN NO_DATA_FOUND
522 THEN
523 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_CONTACT');
524 fnd_msg_pub.add;
525 x_return_status := fnd_api.g_ret_sts_unexp_error;
526 RAISE fnd_api.g_exc_unexpected_error;
527 END;
528 ELSE
529 BEGIN
530 SELECT customer_id
531 INTO l_contact_id
532 FROM jtf_tasks_b
533 WHERE task_id = l_task_contact_id;
534 EXCEPTION
535 WHEN NO_DATA_FOUND
536 THEN
537 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_PHONE');
538 fnd_msg_pub.add;
539 x_return_status := fnd_api.g_ret_sts_unexp_error;
540 RAISE fnd_api.g_exc_unexpected_error;
541 END;
542 END IF;
543
544 IF p_phone_id IS NULL
545 THEN
546 fnd_message.set_name ('JTF', 'JTF_TASK_MISSING_PHONE');
547 fnd_msg_pub.add;
551
548 x_return_status := fnd_api.g_ret_sts_unexp_error;
549 RAISE fnd_api.g_exc_unexpected_error;
550 END IF;
552 OPEN c_task_phones;
553 FETCH c_task_phones INTO task_phones;
554 jtf_task_utl.validate_contact_point (
555 p_contact_id => l_task_contact_id,
556 p_phone_id => p_phone_id,
557 x_return_status => x_return_status,
558 p_owner_table_name => task_phones.owner_table_name
559 );
560
561 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
562 THEN
563 x_return_status := fnd_api.g_ret_sts_unexp_error;
564 RAISE fnd_api.g_exc_unexpected_error;
565 END IF;
566
567 BEGIN
568 SELECT 1
569 INTO x
570 FROM jtf_task_phones
571 WHERE task_phone_id = p_task_phone_id;
572 EXCEPTION
573 WHEN NO_DATA_FOUND
574 THEN
575 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_PHONE');
576 fnd_message.set_token ('P_TASK_PHONE_ID', p_task_phone_id);
577 fnd_msg_pub.add;
578 RAISE fnd_api.g_exc_unexpected_error;
579 END;
580
581 jtf_task_phones_pub.lock_task_phones (
582 p_api_version => 1.0,
583 p_init_msg_list => fnd_api.g_false,
584 p_commit => fnd_api.g_false,
585 p_task_phone_id => p_task_phone_id,
586 p_object_version_number => p_object_version_number,
587 x_return_status => x_return_status,
588 x_msg_data => x_msg_data,
589 x_msg_count => x_msg_count
590 );
591
592 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
593 THEN
594 x_return_status := fnd_api.g_ret_sts_unexp_error;
595 RAISE fnd_api.g_exc_unexpected_error;
596 END IF;
597
598 jtf_task_utl.validate_flag (
599 p_flag_name => jtf_task_utl.get_translated_lookup (
600 'JTF_TASK_TRANSLATED_MESSAGES',
601 'PRIMARY_FLAG'
602 ),
603 p_flag_value => task_phones.primary_flag,
604 x_return_status => x_return_status
605 );
606
607 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
608 THEN
609 x_return_status := fnd_api.g_ret_sts_unexp_error;
610 RAISE fnd_api.g_exc_unexpected_error;
611 END IF;
612
613 --Unmark the previous contact points with primary flag = 'Y'.
614 IF p_primary_flag = jtf_task_utl.g_yes
615 THEN
616 do_unmark_primary_flag_update (
617 p_task_phone_id,
618 p_phone_id,
619 l_contact_id
620 );
621 END IF;
622
623 jtf_task_phones_pkg.update_row (
624 x_task_phone_id => p_task_phone_id,
625 x_task_contact_id => l_task_contact_id,
626 x_object_version_number => p_object_version_number + 1,
627 x_attribute1 => task_phones.attribute1,
628 x_attribute2 => task_phones.attribute2,
629 x_attribute3 => task_phones.attribute3,
630 x_attribute4 => task_phones.attribute4,
631 x_attribute5 => task_phones.attribute5,
632 x_attribute6 => task_phones.attribute6,
633 x_attribute7 => task_phones.attribute7,
634 x_attribute8 => task_phones.attribute8,
635 x_attribute9 => task_phones.attribute9,
636 x_attribute10 => task_phones.attribute10,
637 x_attribute11 => task_phones.attribute11,
638 x_attribute12 => task_phones.attribute12,
639 x_attribute13 => task_phones.attribute13,
640 x_attribute14 => task_phones.attribute14,
641 x_attribute15 => task_phones.attribute15,
642 x_attribute_category => task_phones.attribute_category,
643 x_phone_id => p_phone_id,
644 x_last_update_date => SYSDATE,
645 x_last_updated_by => jtf_task_utl.updated_by,
646 x_last_update_login => jtf_task_utl.login_id,
647 x_owner_table_name => task_phones.owner_table_name,
648 x_primary_flag => task_phones.primary_flag
649 );
650 -- Increase ovn when all the process is successfully completed
651 -- bug 2667735
652 p_object_version_number := p_object_version_number + 1;
653
654 IF fnd_api.to_boolean (p_commit)
655 THEN
656 COMMIT WORK;
657 END IF;
658
659 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
660 EXCEPTION
661 WHEN fnd_api.g_exc_unexpected_error
662 THEN
663 ROLLBACK TO update_task_phones_pub;
664 x_return_status := fnd_api.g_ret_sts_unexp_error;
665 fnd_msg_pub.count_and_get (
666 p_count => x_msg_count,
667 p_data => x_msg_data
668 );
669 WHEN OTHERS
670 THEN
671 ROLLBACK TO update_task_phones_pub;
672 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
673 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
674 fnd_msg_pub.add;
675 x_return_status := fnd_api.g_ret_sts_unexp_error;
676 fnd_msg_pub.count_and_get (
677 p_count => x_msg_count,
678 p_data => x_msg_data
679 );
680 END;
681
685 p_commit IN VARCHAR2,
682 PROCEDURE delete_task_phones (
683 p_api_version IN NUMBER,
684 p_init_msg_list IN VARCHAR2,
686 p_object_version_number IN NUMBER,
687 p_task_phone_id IN NUMBER,
688 x_return_status OUT NOCOPY VARCHAR2,
689 x_msg_data OUT NOCOPY VARCHAR2,
690 x_msg_count OUT NOCOPY NUMBER
691 )
692 IS
693 l_api_version CONSTANT NUMBER := 1.0;
694 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TASK_PHONES';
695 x CHAR;
696
697 CURSOR c_jtf_task_phones
698 IS
699 SELECT 1
700 FROM jtf_task_phones
701 WHERE task_phone_id = p_task_phone_id;
702 BEGIN
703 SAVEPOINT delete_task_phones_pub;
704 x_return_status := fnd_api.g_ret_sts_success;
705
706 IF NOT fnd_api.compatible_api_call (
707 l_api_version,
708 p_api_version,
709 l_api_name,
710 g_pkg_name
711 )
712 THEN
713 RAISE fnd_api.g_exc_unexpected_error;
714 END IF;
715
716 IF fnd_api.to_boolean (p_init_msg_list)
717 THEN
718 fnd_msg_pub.initialize;
719 END IF;
720
721 jtf_task_phones_pub.lock_task_phones (
722 p_api_version => 1.0,
723 p_init_msg_list => fnd_api.g_false,
724 p_commit => fnd_api.g_false,
725 p_task_phone_id => p_task_phone_id,
726 p_object_version_number => p_object_version_number,
727 x_return_status => x_return_status,
728 x_msg_data => x_msg_data,
729 x_msg_count => x_msg_count
730 );
731
732 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
733 THEN
734 x_return_status := fnd_api.g_ret_sts_unexp_error;
735 RAISE fnd_api.g_exc_unexpected_error;
736 END IF;
737
738 jtf_task_phones_pkg.delete_row (x_task_phone_id => p_task_phone_id);
739 OPEN c_jtf_task_phones;
740 FETCH c_jtf_task_phones INTO x;
741
742 IF c_jtf_task_phones%FOUND
743 THEN
744 x_return_status := fnd_api.g_ret_sts_unexp_error;
745 fnd_message.set_name ('JTF', 'JTF_TASK_DELETING_PHONES');
746 fnd_msg_pub.add;
747 RAISE fnd_api.g_exc_unexpected_error;
748 END IF;
749
750 IF fnd_api.to_boolean (p_commit)
751 THEN
752 COMMIT WORK;
753 END IF;
754
755 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
756 EXCEPTION
757 WHEN fnd_api.g_exc_unexpected_error
758 THEN
759 ROLLBACK TO delete_task_phones_pub;
760 x_return_status := fnd_api.g_ret_sts_unexp_error;
761 fnd_msg_pub.count_and_get (
762 p_count => x_msg_count,
763 p_data => x_msg_data
764 );
765 WHEN OTHERS
766 THEN
767 ROLLBACK TO delete_task_phones_pub;
768 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
769 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
770 fnd_msg_pub.add;
771 x_return_status := fnd_api.g_ret_sts_unexp_error;
772 fnd_msg_pub.count_and_get (
773 p_count => x_msg_count,
774 p_data => x_msg_data
775 );
776 END;
777
778 PROCEDURE do_unmark_primary_flag_create (
779 p_task_contact_id IN NUMBER,
780 p_phone_id IN NUMBER,
781 p_owner_table_name IN VARCHAR2
782 )
783 IS
784 BEGIN
785 -- select contact points of the same type and unmark the primary flag
786 IF p_owner_table_name = 'JTF_TASK_CONTACTS'
787 THEN
788 UPDATE jtf_task_phones
789 SET primary_flag = 'N'
790 WHERE task_contact_id = p_task_contact_id
791 AND phone_id IN
792 ( SELECT contact_point_id
793 FROM hz_contact_points
794 WHERE contact_point_type =
795 (SELECT contact_point_type
796 FROM hz_contact_points
797 WHERE contact_point_id = p_phone_id
798 AND owner_table_id =
799 (SELECT contact_id
800 FROM jtf_task_contacts
801 WHERE task_contact_id =
802 p_task_contact_id))
803 AND owner_table_id =
804 (SELECT contact_id
805 FROM jtf_task_contacts
806 WHERE task_contact_id = p_task_contact_id))
807 AND primary_flag = 'Y';
808 ELSE
809 -- select contact points of the same type and unmark the primary flag
810 UPDATE jtf_task_phones
811 SET primary_flag = 'N'
812 WHERE task_contact_id = p_task_contact_id
813 AND phone_id IN
814 ( SELECT contact_point_id
815 FROM hz_contact_points
816 WHERE contact_point_type =
817 (SELECT contact_point_type
818 FROM hz_contact_points
819 WHERE contact_point_id =
820 p_phone_id
821 AND owner_table_id =
822 (SELECT customer_id
823 FROM jtf_tasks_b
824 WHERE task_id =
825 p_task_contact_id))
826 AND owner_table_id =
827 (SELECT customer_id
828 FROM jtf_tasks_b
829 WHERE task_id =
830 p_task_contact_id))
831 AND primary_flag = 'Y';
832 END IF;
833 END;
834
835 PROCEDURE do_unmark_primary_flag_update (
836 p_task_phone_id IN NUMBER,
837 --p_task_contact_id IN NUMBER,
838 p_phone_id IN NUMBER,
839 --p_owner_table_name IN VARCHAR2,
840 l_contact_id IN NUMBER
841 )
842 IS
843 BEGIN
844 UPDATE jtf_task_phones
845 SET primary_flag = 'N'
846 WHERE task_contact_id = (SELECT task_contact_id
847 FROM jtf_task_phones
848 WHERE task_phone_id = p_task_phone_id)
849 AND phone_id IN
850 ( SELECT contact_point_id
851 FROM hz_contact_points
852 WHERE contact_point_type =
853 (SELECT contact_point_type
854 FROM hz_contact_points
855 WHERE contact_point_id = p_phone_id
856 AND owner_table_id = l_contact_id
857 )
858 AND owner_table_id = l_contact_id
859 )
860 AND primary_flag = 'Y';
861 END;
862 END;