[Home] [Help]
PACKAGE BODY: APPS.JTF_TASK_UTL
Source
1 PACKAGE BODY jtf_task_utl AS
2 /* $Header: jtfptklb.pls 120.13 2011/12/12 17:21:48 anangupt ship $ */
3
4 /******** Start of addition by SBARAT on 29/12/2005 for bug# 4866066 ********/
5 FUNCTION g_yes
6 RETURN VARCHAR2
7 IS
8 BEGIN
9 RETURN 'Y';
10 END;
11
12 FUNCTION g_no
13 RETURN VARCHAR2
14 IS
15 BEGIN
16 RETURN 'N';
17 END;
18 /******** End of addition by SBARAT on 29/12/2005 for bug# 4866066 ********/
19
20 FUNCTION get_escalation_owner (p_task_id IN NUMBER)
21 RETURN VARCHAR2
22 IS
23 l_owner VARCHAR2(120);
24 BEGIN
25 SELECT jtf_task_utl.get_owner (esc.owner_type_code, esc.owner_id)
26 INTO
27 l_owner
28 FROM jtf_tasks_b esc
29 WHERE task_id IN (SELECT task_id
30 FROM jtf_task_references_b
31 WHERE object_id = p_task_id
32 AND object_type_code = 'TASK'
33 AND reference_code = 'ESC')
34 AND task_type_id = 22;
35 RETURN l_owner;
36 EXCEPTION
37 WHEN OTHERS
38 THEN
39 RETURN NULL;
40 END;
41
42 FUNCTION get_escalation_level (p_task_id IN NUMBER)
43 RETURN VARCHAR2
44 IS
45 l_meaning VARCHAR2(30);
46 BEGIN
47 SELECT lookups.meaning
48 INTO l_meaning
49 FROM jtf_tasks_b, fnd_lookups lookups
50 WHERE task_id IN (SELECT task_id
51 FROM jtf_task_references_b
52 WHERE object_id = p_task_id
53 AND object_type_code = 'TASK'
54 AND reference_code = 'ESC')
55 AND task_type_id = 22
56 AND lookups.lookup_type = 'JTF_TASK_ESC_LEVEL'
57 AND lookups.lookup_code = jtf_tasks_b.escalation_level;
58 RETURN l_meaning;
59 EXCEPTION
60 WHEN OTHERS
61 THEN
62 RETURN NULL;
63 END;
64
65 PROCEDURE call_internal_hook (
66 p_package_name IN VARCHAR2,
67 p_api_name IN VARCHAR2,
68 p_processing_type IN VARCHAR2,
69 x_return_status OUT NOCOPY VARCHAR2
70 )
71 IS
72 CURSOR c1
73 IS
74 SELECT hd.hook_package, hd.hook_api, fa.application_id -- Fix bug 2512087
75 FROM fnd_application fa
76 , jtf_hooks_data hd
77 WHERE hd.package_name = p_package_name
78 AND hd.api_name = p_api_name
79 AND hd.execute_flag = 'Y'
80 AND hd.processing_type = p_processing_type
81 and fa.application_short_name = hd.product_code
82 ORDER BY execution_order;
83
84 v_cursorid INTEGER;
85 v_blockstr VARCHAR2(2000);
86 v_dummy INTEGER;
87 BEGIN
88 x_return_status := fnd_api.g_ret_sts_success;
89
90 FOR i IN c1
91 LOOP
92 v_cursorid := DBMS_SQL.open_cursor;
93 v_blockstr :=
94 ' begin ' || i.hook_package || '.' || i.hook_api || '(:1); end; ';
95 DBMS_SQL.parse (v_cursorid, v_blockstr, DBMS_SQL.v7);
96 DBMS_SQL.bind_variable (v_cursorid, ':1', x_return_status, 20);
97 v_dummy := DBMS_SQL.execute (v_cursorid);
98 DBMS_SQL.variable_value (v_cursorid, ':1', x_return_status);
99 DBMS_SQL.close_cursor (v_cursorid);
100
101 -- check for null first
102 -- record the package name in the error stack
103 IF x_return_status IS NULL
104 THEN
105 fnd_message.set_name ('JTF', 'JTF_TASK_MISSING_RETURN_STATUS');
106 /**** Fix Bug# 2042516 *****/
107 fnd_message.set_token (
108 'P_PROCEDURE',
109 i.hook_package || '.' || i.hook_api
110 );
111 /*-------------------------*/
112 fnd_msg_pub.add;
113 x_return_status := fnd_api.g_ret_sts_unexp_error;
114 RAISE fnd_api.g_exc_unexpected_error;
115 END IF;
116
117 -- return an error to indicate that the hook failed, to fix bug #2231728
118 -- record the package name in the error stack
119 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
120 THEN
121 fnd_message.set_name ('JTF', 'JTF_TASK_HOOK_RETURN_ERROR');
122 fnd_message.set_token (
123 'P_PROCEDURE',
124 i.hook_package || '.' || i.hook_api
125 );
126 -- Fix bug 2512087
127 -- Display application_id
128 fnd_message.set_token (
129 'APPL_ID',
130 i.application_id
131 );
132 fnd_msg_pub.add;
133 x_return_status := fnd_api.g_ret_sts_unexp_error;
134 RAISE fnd_api.g_exc_unexpected_error;
135 END IF;
136
137 END LOOP;
138 EXCEPTION
139 WHEN fnd_api.g_exc_unexpected_error
140 THEN
141 x_return_status := fnd_api.g_ret_sts_unexp_error;
142 WHEN OTHERS
143 THEN
144 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
145 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
146 fnd_msg_pub.add;
147 x_return_status := fnd_api.g_ret_sts_unexp_error;
148 END;
149
150 PROCEDURE validate_location_id (
151 p_location_id IN NUMBER,
152 p_address_id IN NUMBER,
153 p_task_id IN NUMBER,
154 x_return_status OUT NOCOPY VARCHAR2
155 )
156 IS
157 Cursor C_Task Is
158 Select DECODE (
159 p_address_id,
160 fnd_api.g_miss_num,
161 address_id,
162 p_address_id
163 ) address_id,
164 DECODE (
165 p_location_id,
166 fnd_api.g_miss_num,
167 location_id,
168 p_location_id
169 ) location_id
170 From JTF_TASKS_VL
171 Where Task_Id=p_task_id;
172 V_task C_Task%Rowtype;
173 BEGIN
174 x_return_status := fnd_api.g_ret_sts_success;
175 IF (p_location_id IS NOT NULL And p_address_id IS NOT NULL)
176 THEN
177 IF (p_task_id IS NOT NULL)
178 THEN
179 IF ((p_location_id <> fnd_api.g_miss_num AND p_address_id <> fnd_api.g_miss_num))
180 THEN
181 fnd_message.set_name ('JTF', 'JTF_TASK_LOCATION_VALIDATION');
182 fnd_msg_pub.add;
183 x_return_status := fnd_api.g_ret_sts_unexp_error;
184
185 ELSIF (p_address_id <> fnd_api.g_miss_num)
186 THEN
187 Open C_Task;
188 Fetch C_Task into V_task;
189 Close C_Task;
190 IF (V_Task.Location_Id IS NOT NULL AND V_Task.Address_Id IS NOT NULL)
191 THEN
192 fnd_message.set_name ('JTF', 'JTF_TASK_LOCATION_EXIST');
193 fnd_msg_pub.add;
194 x_return_status := fnd_api.g_ret_sts_unexp_error;
195 END IF;
196
197 ELSIF (p_location_id <> fnd_api.g_miss_num)
198 THEN
199 Open C_Task;
200 Fetch C_Task into V_task;
201 Close C_Task;
202 IF (V_Task.Location_Id IS NOT NULL AND V_Task.Address_Id IS NOT NULL)
203 THEN
204 fnd_message.set_name ('JTF', 'JTF_TASK_ADDRESS_EXIST');
205 fnd_msg_pub.add;
206 x_return_status := fnd_api.g_ret_sts_unexp_error;
207 END IF;
208 END IF;
209 ELSE
210 fnd_message.set_name ('JTF', 'JTF_TASK_LOCATION_VALIDATION');
211 fnd_msg_pub.add;
212 x_return_status := fnd_api.g_ret_sts_unexp_error;
213 END IF;
214 END IF;
215 END validate_location_id;
216
217 FUNCTION validate_shift_construct (p_shift_construct_id IN NUMBER)
218 RETURN BOOLEAN
219 IS
220 x CHAR;
221 BEGIN
222 SELECT 1
223 INTO x
224 FROM jtf_cal_shift_cons_v
225 WHERE shift_construct_id = p_shift_construct_id;
226 RETURN TRUE;
227 EXCEPTION
228 WHEN NO_DATA_FOUND
229 THEN
230 RETURN FALSE;
231 WHEN TOO_MANY_ROWS
232 THEN
233 RETURN TRUE;
234 WHEN OTHERS
235 THEN
236 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
237 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
238 fnd_msg_pub.add;
239 RETURN FALSE;
240 END;
241
242 PROCEDURE validate_distance (
243 p_distance_units IN NUMBER,
244 p_distance_tag IN VARCHAR2,
245 x_return_status OUT NOCOPY VARCHAR2
246 )
247 IS
248 BEGIN
249 -- to fix bug#2401431
250 x_return_status := fnd_api.g_ret_sts_success;
251 IF p_distance_units IS NOT NULL
252 THEN
253 IF p_distance_units < 0
254 THEN
255 fnd_message.set_name ('JTF', 'JTF_TASK_DISTANCE_UNITS');
256 fnd_message.set_token ('P_DISTANCE_TAG', p_distance_tag);
257 fnd_msg_pub.add;
258 x_return_status := fnd_api.g_ret_sts_unexp_error;
259 END IF;
260 END IF;
261 END;
262
263 FUNCTION validate_lookup (
264 p_lookup_type IN VARCHAR2,
265 p_lookup_code IN VARCHAR2,
266 p_lookup_type_name IN VARCHAR2
267 )
268 RETURN BOOLEAN
269 IS
270 l_return_status BOOLEAN := TRUE;
271 x NUMBER;
272 BEGIN
273 SELECT 1
274 INTO x
275 FROM fnd_lookups
276 WHERE lookup_type = p_lookup_type
277 AND lookup_code = p_lookup_code;
278 RETURN TRUE;
279 EXCEPTION
280 WHEN NO_DATA_FOUND
281 THEN
282 fnd_message.set_name ('JTF', 'JTF_TASK_MISSING_LOOKUP');
283 fnd_message.set_token ('P_LOOKUP_CODE', p_lookup_code);
284 fnd_message.set_token ('P_LOOKUP_TYPE', p_lookup_type);
285 fnd_msg_pub.add;
286 RETURN FALSE;
287 WHEN TOO_MANY_ROWS
288 THEN
289 RETURN TRUE;
290 WHEN OTHERS
291 THEN
292 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
293 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
294 fnd_msg_pub.add;
295 RETURN FALSE;
296 END;
297
298 PROCEDURE validate_contact_point (
299 p_contact_id IN NUMBER,
300 p_phone_id IN NUMBER,
301 x_return_status OUT NOCOPY VARCHAR2,
302 p_owner_table_name IN VARCHAR2
303 )
304 IS
305 x VARCHAR2(1);
306 l_contact_type_code jtf_task_contacts.contact_type_code%TYPE;
307
308 l_person_party_id hz_parties.party_id%TYPE;
309 l_rel_party_id hz_parties.party_id%TYPE;
310
311 cursor get_party_ids (p_contact_id Number) Is
312 select pc.party_id, pc.subject_party_id
313 from jtf_party_all_contacts_v pc,
314 jtf_task_contacts tc
315 where tc.task_contact_id = p_contact_id
316 and tc.contact_id IN (pc.party_id, pc.subject_party_id);
317
318 BEGIN
319
320 x_return_status := fnd_api.g_ret_sts_success;
321
322 --- for backward compatibility, the owner table name is defaulted to p_owner_table_name
323 IF p_owner_table_name = 'JTF_TASK_CONTACTS'
324 OR p_owner_table_name IS NULL
325 THEN
326 --- for backward compatibility, the contact_type_code is assumed to be CUST.
327 SELECT NVL (contact_type_code, 'CUST')
328 INTO l_contact_type_code
329 FROM jtf_task_contacts
330 WHERE task_contact_id = p_contact_id;
331 END IF;
332
333 IF p_owner_table_name = 'JTF_TASKS_B'
334 THEN
335 l_contact_type_code := 'CUST';
336 END IF;
337
338
339
340 BEGIN
341 IF l_contact_type_code = 'CUST'
342 THEN
343
344
345 IF p_owner_table_name = 'JTF_TASK_CONTACTS'
346 THEN
347
348 open get_party_ids(p_contact_id);
349 fetch get_party_ids into l_rel_party_id ,l_person_party_id;
350 if get_party_ids%NOTFOUND then
351 close get_party_ids;
352 raise no_data_found;
353 else
354 close get_party_ids;
355 end if;
356
357 SELECT 1
358 INTO x
359 FROM jtf_party_cont_points_v
360 WHERE party_id in (l_rel_party_id ,l_person_party_id)
361 AND contact_point_id = p_phone_id
362 AND status = 'A'
363 AND ROWNUM < 2;
364 ELSE
365
366 SELECT 1
367 INTO x
368 FROM jtf_party_cont_points_v
369 WHERE party_id = (SELECT customer_id
370 FROM jtf_tasks_b
371 WHERE task_id = p_contact_id)
372 AND contact_point_id = p_phone_id
373 AND status = 'A'
374 AND ROWNUM < 2;
375
376 END IF;
377 ELSE
378 SELECT 1
379 INTO x
380 FROM per_phones
381 WHERE parent_table = 'PER_ALL_PEOPLE_F'
382 AND parent_id = (SELECT contact_id
383 FROM jtf_task_contacts
384 WHERE task_contact_id = p_contact_id)
385 AND phone_id = p_phone_id;
386 END IF;
387
388
389 EXCEPTION
390 WHEN NO_DATA_FOUND
391 THEN
392 x_return_status := fnd_api.g_ret_sts_unexp_error;
393 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_PHONE_ID');
394 fnd_message.set_token ('P_PHONE_ID', p_phone_id);
395 fnd_msg_pub.add;
396 WHEN OTHERS
397 THEN
398 x_return_status := fnd_api.g_ret_sts_unexp_error;
399 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
400 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
401 fnd_msg_pub.add;
402 END;
403 END;
404
405 FUNCTION get_owner (p_object_type_code IN VARCHAR2, p_object_id IN NUMBER)
406 RETURN VARCHAR2
407 IS
408 CURSOR c_references
409 IS
410 SELECT select_id, select_name, from_table, where_clause
411 FROM jtf_objects_b
412 WHERE object_code = p_object_type_code;
413 --- Removed the reference from JTF_OBJECTS_VL to JT_OBJECTS_B.
414
415
416 l_id_column jtf_objects_b.select_id%TYPE;
417 l_name_column jtf_objects_b.select_name%TYPE;
418 l_from_clause jtf_objects_b.from_table%TYPE;
419 l_where_clause jtf_objects_b.where_clause%TYPE;
420 l_object_code jtf_tasks_b.source_object_type_code%TYPE
421 := p_object_type_code;
422 l_object_name VARCHAR2(2000);
423 l_object_id jtf_tasks_b.source_object_id%TYPE
424 := p_object_id;
425 is_null BOOLEAN := FALSE;
426 is_not_null BOOLEAN := FALSE;
427 sql_stmt VARCHAR2(2000);
428 BEGIN
429 OPEN c_references;
430 FETCH c_references INTO l_id_column,
431 l_name_column,
432 l_from_clause,
433 l_where_clause;
434
435 IF c_references%NOTFOUND
436 THEN
437 NULL;
438 END IF;
439
440 -- SELECT DECODE (l_where_clause, NULL, ' ', l_where_clause || ' AND ')
441 --INTO
442 -- l_where_clause
443 --FROM dual;
444
445 -- Fix for bug 2932012
446 IF (l_where_clause IS NULL)
447 THEN
448 l_where_clause := ' ';
449 ELSE
450 l_where_clause := l_where_clause || ' AND ';
451 END IF;
452
453 -- Added 'rownum = 1' bshih
454 sql_stmt := ' SELECT ' ||
455 l_name_column ||
456 ' from ' ||
457 l_from_clause ||
458 ' where ' ||
459 l_where_clause ||
460 l_id_column ||
461 ' = :object_id and rownum = 1';
462
463 EXECUTE IMMEDIATE sql_stmt INTO l_object_name
464 USING p_object_id;
465 RETURN l_object_name;
466 EXCEPTION
467 WHEN OTHERS
468 THEN
469 RETURN NULL;
470 END;
471
472 PROCEDURE validate_contact (
473 p_contact_id IN NUMBER,
474 p_task_id IN NUMBER,
475 p_contact_type_code IN VARCHAR2,
476 x_return_status OUT NOCOPY VARCHAR2
477 )
478 IS
479 l_customer_id NUMBER;
480 x_return BOOLEAN := TRUE;
481 x CHAR;
482 BEGIN
483 x_return_status := fnd_api.g_ret_sts_success;
484
485 BEGIN
486 IF p_contact_type_code = 'CUST'
487 THEN
488 BEGIN
489 SELECT 1
490 INTO x
491 FROM jtf_party_all_contacts_v contacts
492 WHERE contacts.object_party_id = (SELECT customer_id
493 FROM jtf_tasks_b
494 WHERE task_id = p_task_id)
495 AND p_contact_id in (contacts.subject_party_id, contacts.party_id)
496 AND ROWNUM < 2;
497 END;
498 ELSE
499 BEGIN
500 SELECT 1
501 INTO x
502 FROM per_people_f
503 WHERE person_id = p_contact_id
504 AND SYSDATE BETWEEN
505 effective_start_date AND effective_end_date;
506 END;
507 END IF;
508 EXCEPTION
509 WHEN NO_DATA_FOUND
510 THEN
511 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_CONTACT');
512 fnd_msg_pub.add;
513 x_return_status := fnd_api.g_ret_sts_unexp_error;
514 RAISE fnd_api.g_exc_unexpected_error;
515 WHEN TOO_MANY_ROWS
516 THEN
517 NULL;
518 END;
519 END;
520
521 PROCEDURE check_duplicate_contact (
522 p_contact_id IN NUMBER,
523 p_task_id IN NUMBER,
524 p_contact_type_code IN VARCHAR2,
525 p_task_contact_id IN NUMBER DEFAULT NULL,
526 x_return_status OUT NOCOPY VARCHAR2
527 ) IS
528 Cursor c_dup_contact IS
529 Select 'Y' From JTF_TASK_CONTACTS
530 Where task_id = p_task_id
531 And contact_id = p_contact_id
532 And contact_type_code = p_contact_type_code
533 And task_contact_id <> NVL(p_task_contact_id, -99999);
534
535 l_dup_contact VARCHAR2(10);
536
537 Begin
538
539 x_return_status := fnd_api.g_ret_sts_success;
540
541 Open c_dup_contact;
542 Fetch c_dup_contact Into l_dup_contact;
543 If c_dup_contact%FOUND
544 Then
545 fnd_message.set_name ('JTF', 'JTF_TASK_DUPLICATE_CONTACT');
546 fnd_msg_pub.add;
547 x_return_status := fnd_api.g_ret_sts_unexp_error;
548 End If;
549
550 Close c_dup_contact;
551
552 End;
553
554 ---------
555 --------- Validate Task Template Group
556 ---------
557 PROCEDURE validate_task_template_group (
558 p_task_template_group_id IN NUMBER,
559 p_task_template_group_name IN VARCHAR2,
560 x_return_status OUT NOCOPY VARCHAR2,
561 x_task_template_group_id IN OUT NOCOPY NUMBER -- Fixed from OUT to IN OUT
562 )
563 IS
564 CURSOR c_task_template_group_id
565 IS
566 SELECT task_template_group_id
567 FROM jtf_task_temp_groups_b
568 WHERE task_template_group_id = p_task_template_group_id
569 AND trunc(NVL (end_date_active, SYSDATE)) >= trunc(SYSDATE)
570 AND trunc(NVL (start_date_active, SYSDATE)) <= trunc(SYSDATE);
571
572 CURSOR c_task_template_group_name
573 IS
574 SELECT task_template_group_id
575 FROM jtf_task_temp_groups_vl
576 WHERE template_group_name = p_task_template_group_name
577 AND trunc(NVL (end_date_active, SYSDATE)) >= trunc(SYSDATE)
578 AND trunc(NVL (start_date_active, SYSDATE)) <= trunc(SYSDATE);
579 BEGIN
580 x_return_status := fnd_api.g_ret_sts_success;
581
582 IF p_task_template_group_id IS NOT NULL
583 THEN
584 OPEN c_task_template_group_id;
585 FETCH c_task_template_group_id INTO x_task_template_group_id;
586
587 IF c_task_template_group_id%NOTFOUND
588 THEN
589 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_TEMP_GRP_ID');
590 fnd_message.set_token (
591 'JTF_TASK_INVALID_TEMP_GROUP_ID',
592 p_task_template_group_id
593 );
594 fnd_msg_pub.add;
595 x_return_status := fnd_api.g_ret_sts_unexp_error;
596 RAISE fnd_api.g_exc_unexpected_error;
597 END IF;
598
599 CLOSE c_task_template_group_id;
600 ELSIF p_task_template_group_name IS NOT NULL
601 THEN
602 OPEN c_task_template_group_name;
603 FETCH c_task_template_group_name INTO x_task_template_group_id;
604
605 IF c_task_template_group_name%NOTFOUND
606 THEN
607 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_TEMP_GRP_NAME');
608 fnd_message.set_token (
609 'JTF_TASK_INVALID_TEMP_GROUP_NAME',
610 p_task_template_group_name
611 );
612 x_return_status := fnd_api.g_ret_sts_unexp_error;
613 RAISE fnd_api.g_exc_unexpected_error;
614 END IF;
615
616 CLOSE c_task_template_group_name;
617 END IF;
618 END;
619
620 ---------
621 --------- Validate Costs
622 ---------
623 PROCEDURE validate_costs (
624 x_return_status OUT NOCOPY VARCHAR2,
625 p_costs IN NUMBER,
626 p_currency_code IN VARCHAR2
627 )
628 IS
629 BEGIN
630 x_return_status := fnd_api.g_ret_sts_success;
631
632 IF ( p_costs IS NULL
633 AND p_currency_code IS NOT NULL)
634 THEN
635 x_return_status := fnd_api.g_ret_sts_unexp_error;
636 fnd_message.set_name ('JTF', 'JTF_TASK_MISSING_COST');
637 fnd_message.set_token ('P_currency_code', p_currency_code);
638 fnd_msg_pub.add;
639 ELSIF ( p_costs IS NOT NULL
640 AND p_currency_code IS NULL)
641 THEN
642 x_return_status := fnd_api.g_ret_sts_unexp_error;
643 fnd_message.set_name ('JTF', 'JTF_TASK_MISSING_CURRENCY_CODE');
644 fnd_message.set_token ('P_cOSTs', p_costs);
645 fnd_msg_pub.add;
646 ELSIF ( p_costs IS NOT NULL
647 AND p_currency_code IS NOT NULL)
648 THEN
649 IF NOT fnd_api.to_boolean (
650 jtf_task_utl.currency_code (p_currency_code)
651 )
652 THEN
653 x_return_status := fnd_api.g_ret_sts_unexp_error;
654 END IF;
655 ELSE
656 NULL;
657 END IF;
658 END;
659
660 ---------
661 --------- FND currencies
662 ---------
663 FUNCTION currency_code (p_currency_code IN VARCHAR2)
664 RETURN VARCHAR2
665 IS
666 CURSOR c_jtf_currencies
667 IS
668 SELECT 1
669 FROM fnd_currencies
670 WHERE currency_code = p_currency_code;
671
672 x CHAR;
673 l_result CHAR;
674 BEGIN
675 OPEN c_jtf_currencies;
676 FETCH c_jtf_currencies INTO x;
677
678 IF c_jtf_currencies%NOTFOUND
679 THEN
680 l_result := fnd_api.g_false;
681 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_CURRENCY_CODE');
682 fnd_message.set_token ('P_CURRENCY_CODE', p_currency_code);
683 fnd_msg_pub.add;
684 ELSE
685 l_result := fnd_api.g_true;
686 END IF;
687
688 CLOSE c_jtf_currencies;
689 RETURN l_result;
690 END;
691
692 ---------------------
693 ----- Task Object type code
694 ---------------------
695 ---------------------
696 PROCEDURE validate_object_type (
697 p_object_code IN VARCHAR2,
698 p_object_type_name IN VARCHAR2,
699 p_object_type_tag IN VARCHAR2,
700 p_object_usage IN VARCHAR2,
701 x_return_status OUT NOCOPY VARCHAR2,
702 x_object_code IN OUT NOCOPY VARCHAR2 -- Fixed from OUT to IN OUT
703 )
704 IS
705 CURSOR c_object_code
706 IS
707 SELECT object_code
708 FROM jtf_objects_b
709 WHERE object_code = p_object_code
710 AND trunc(NVL (end_date_active, SYSDATE)) >= trunc(SYSDATE)
711 AND trunc(NVL (start_date_active, SYSDATE)) <= trunc(SYSDATE)
712 AND (object_code IN
713 (SELECT object_code
714 FROM jtf_object_usages
715 WHERE object_user_code =
716 NVL (p_object_usage, object_user_code)));
717
718 CURSOR c_object_type_name
719 IS
720 SELECT object_code
721 FROM jtf_objects_vl
722 WHERE name = p_object_type_name
723 AND trunc(NVL (end_date_active, SYSDATE)) >= trunc(SYSDATE)
724 AND trunc(NVL (start_date_active, SYSDATE)) <= trunc(SYSDATE)
725 AND (object_code IN
726 (SELECT object_code
727 FROM jtf_object_usages
728 WHERE object_user_code =
729 NVL (p_object_usage, object_user_code)));
730
731 BEGIN
732 x_return_status := fnd_api.g_ret_sts_success;
733
734 IF p_object_code IS NOT NULL
735 THEN
736 OPEN c_object_code;
737 FETCH c_object_code INTO x_object_code;
738
739 IF c_object_code%NOTFOUND
740 THEN
741 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_OBJECT_CODE');
742 fnd_message.set_token ('P_OBJECT_CODE', p_object_type_tag);
743 fnd_msg_pub.add;
744 x_return_status := fnd_api.g_ret_sts_unexp_error;
745 RAISE fnd_api.g_exc_unexpected_error;
746 END IF;
747
748 CLOSE c_object_code;
749 ELSIF p_object_type_name IS NOT NULL
750 THEN
751 OPEN c_object_type_name;
752 FETCH c_object_type_name INTO x_object_code;
753
754 IF c_object_type_name%NOTFOUND
755 THEN
756 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_OBJECT_NAME');
757 fnd_message.set_token ('P_TAG_NAME', p_object_type_tag);
758 fnd_msg_pub.add;
759 x_return_status := fnd_api.g_ret_sts_unexp_error;
760 RAISE fnd_api.g_exc_unexpected_error;
761 END IF;
762
763 CLOSE c_object_type_name;
764 END IF;
765 END;
766
767 ------------
768 ------------ Validate Reference codes
769 ------------
770 PROCEDURE validate_reference_codes (
771 p_reference_code IN VARCHAR2,
772 x_return_status OUT NOCOPY VARCHAR2
773 )
774 IS
775 CURSOR c_reference_codes
776 IS
777 SELECT 1
778 FROM fnd_lookups
779 WHERE lookup_type = 'JTF_TASK_REFERENCE_CODES'
780 AND lookup_code = p_reference_code;
781
782 x CHAR;
783 BEGIN
784 x_return_status := fnd_api.g_ret_sts_success;
785
786 IF p_reference_code IS NOT NULL
787 THEN
788 OPEN c_reference_codes;
789 FETCH c_reference_codes INTO x;
790
791 IF c_reference_codes%NOTFOUND
792 THEN
793 fnd_message.set_name ('JTF', 'JTF_TASK_REFERENCE_CODE');
794 fnd_message.set_token ('P_REFERENCE_CODE', p_reference_code);
795 fnd_msg_pub.add;
796 RAISE fnd_api.g_exc_unexpected_error;
797 END IF;
798 END IF;
799
800 CLOSE c_reference_codes;
801 END;
802
803 ------------
804 ------------ Validate Assigned By
805 ------------
806 PROCEDURE validate_assigned_by (
807 p_assigned_by_id IN NUMBER,
808 p_assigned_by_name IN VARCHAR2,
809 x_return_status OUT NOCOPY VARCHAR2,
810 x_assigned_by_id IN OUT NOCOPY NUMBER -- Fixed from OUT to IN OUT
811 )
812 IS
813 CURSOR c_assigned_by_id
814 IS
815 SELECT user_id assigned_by_id
816 FROM fnd_user
817 WHERE user_id = p_assigned_by_id
818 AND NVL (end_date, SYSDATE) >= SYSDATE
819 AND NVL (start_date, SYSDATE) <= SYSDATE;
820
821 CURSOR c_assigned_by_name
822 IS
823 SELECT user_id assigned_by_id
824 FROM fnd_user
825 WHERE user_name = p_assigned_by_name
826 AND NVL (end_date, SYSDATE) >= SYSDATE
827 AND NVL (start_date, SYSDATE) <= SYSDATE;
828 BEGIN
829 x_return_status := fnd_api.g_ret_sts_success;
830
831 IF p_assigned_by_id IS NOT NULL
832 THEN
833 OPEN c_assigned_by_id;
834 FETCH c_assigned_by_id INTO x_assigned_by_id;
835
836 IF c_assigned_by_id%NOTFOUND
837 THEN
838 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_ASSIGNED_ID');
839 fnd_message.set_token ('P_ASSIGNED_BY_ID', p_assigned_by_id);
840 fnd_msg_pub.add;
841 x_return_status := fnd_api.g_ret_sts_unexp_error;
842 RAISE fnd_api.g_exc_unexpected_error;
843 END IF;
844
845 CLOSE c_assigned_by_id;
846 ELSIF p_assigned_by_name IS NOT NULL
847 THEN
848 OPEN c_assigned_by_name;
849 FETCH c_assigned_by_name INTO x_assigned_by_id;
850
851 IF c_assigned_by_name%NOTFOUND
852 THEN
853 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_ASSIGNED_NAME');
854 fnd_message.set_token ('P_ASSIGNED_BY_NAME', p_assigned_by_name);
855 fnd_msg_pub.add;
856 x_return_status := fnd_api.g_ret_sts_unexp_error;
857 RAISE fnd_api.g_exc_unexpected_error;
858 END IF;
859
860 CLOSE c_assigned_by_name;
861 END IF;
862 END;
863
864 ------------
865 ------------
866 ------------
867 FUNCTION validate_dependency_id (p_dependency_id IN NUMBER)
868 RETURN BOOLEAN
869 IS
870 CURSOR c_dependency
871 IS
872 SELECT 1
873 FROM jtf_task_depends
874 WHERE dependency_id = p_dependency_id;
875
876 x CHAR;
877 BEGIN
878 OPEN c_dependency;
879 FETCH c_dependency INTO x;
880
881 IF c_dependency%NOTFOUND
882 THEN
883 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_DEPENDS_ID');
884 fnd_message.set_token (
885 'JTF_TASK_INVALID_DEPENDS_ID',
886 p_dependency_id
887 );
888 fnd_msg_pub.add;
889 CLOSE c_dependency;
890 RETURN FALSE;
891 ELSE
892 CLOSE c_dependency;
893 RETURN TRUE;
894 END IF;
895 END;
896
897 FUNCTION to_boolean (x VARCHAR2)
898 RETURN BOOLEAN
899 IS
900 BEGIN
901 IF x = jtf_task_utl.g_yes
902 THEN
903 RETURN TRUE;
904 ELSIF x = jtf_task_utl.g_no
905 THEN
906 RETURN FALSE;
907 ELSE
908 RETURN NULL;
909 END IF;
910 END;
911
912 -- Fix For Bug 2932012
913 FUNCTION created_by
914 RETURN NUMBER
915 IS
916 BEGIN
917 RETURN fnd_global.user_id;
918 END;
919
920 -- Fix For Bug 2932012
921 FUNCTION updated_by
922 RETURN NUMBER
923 IS
924 BEGIN
925 RETURN fnd_global.user_id;
926 END;
927
928 -- Fix For Bug 2932012
929 FUNCTION login_id
930 RETURN NUMBER
931 IS
932 BEGIN
933 RETURN fnd_global.login_id;
934 END;
935
936 FUNCTION get_task_template_group (p_task_template_id IN NUMBER)
937 RETURN NUMBER
938 IS
939 CURSOR c_task_template_group
940 IS
941 SELECT task_template_group_id
942 FROM jtf_task_temp_groups_b
943 WHERE task_template_group_id =
944 (SELECT task_group_id
945 FROM jtf_task_templates_b
946 WHERE task_template_id = p_task_template_id);
947
948 l_task_template_group_id jtf_task_temp_groups_b.task_template_group_id%TYPE;
949 BEGIN
950 OPEN c_task_template_group;
951 FETCH c_task_template_group INTO l_task_template_group_id;
952
953 IF c_task_template_group%NOTFOUND
954 OR l_task_template_group_id IS NULL
955 THEN
956 IF c_task_template_group%NOTFOUND
957 THEN
958 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_TEMP_GRP_ID');
959 fnd_message.set_token (
960 'P_TASK_TEMPLATE_GROUP_ID',
961 l_task_template_group_id
962 );
963 fnd_msg_pub.add;
964 CLOSE c_task_template_group;
965 END IF;
966
967 RAISE fnd_api.g_exc_unexpected_error;
968 ELSE
969 CLOSE c_task_template_group;
970 RETURN l_task_template_group_id;
971 END IF;
972 END;
973
974 ------------
975 ------------
976 ------------
977 PROCEDURE validate_dates (
978 p_date_tag IN VARCHAR2,
979 p_start_date IN DATE,
980 p_end_date IN DATE,
981 x_return_status OUT NOCOPY VARCHAR2
982 )
983 IS
984 BEGIN
985 x_return_status := fnd_api.g_ret_sts_success;
986
987 IF (p_start_date IS NOT NULL)
988 AND (p_end_date IS NOT NULL)
989 THEN
990 IF p_start_date > p_end_date
991 THEN
992 x_return_status := fnd_api.g_ret_sts_unexp_error;
993 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_DATES');
994 fnd_message.set_token ('P_DATE_TAG', p_date_tag);
995 fnd_msg_pub.add;
996 END IF;
997 END IF;
998 END;
999
1000 ------------
1001 ------------
1002 ------------
1003 PROCEDURE validate_flag (
1004 p_api_name IN VARCHAR2,
1005 p_init_msg_list IN VARCHAR2,
1006 x_return_status OUT NOCOPY VARCHAR2,
1007 p_flag_name IN VARCHAR2,
1008 p_flag_value IN VARCHAR2
1009 )
1010 IS
1011 BEGIN
1012 x_return_status := fnd_api.g_ret_sts_success;
1013
1014 IF NOT ( p_flag_value IN (jtf_task_utl.g_yes, jtf_task_utl.g_no)
1015 OR p_flag_value IS NULL)
1016 THEN
1017 x_return_status := fnd_api.g_ret_sts_unexp_error;
1018 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_FLAG');
1019 fnd_message.set_token ('P_FLAG_NAME', p_flag_name);
1020 fnd_msg_pub.add;
1021 END IF;
1022 END;
1023
1024 PROCEDURE get_object_details (
1025 p_task_id IN NUMBER,
1026 p_template_flag IN VARCHAR2,
1027 x_return_status OUT NOCOPY VARCHAR2,
1028 x_source_object_code OUT NOCOPY VARCHAR2
1029 )
1030 IS
1031 CURSOR c_task_object_details
1032 IS
1033 SELECT source_object_type_code
1034 FROM jtf_tasks_b
1035 WHERE task_id = p_task_id;
1036
1037 CURSOR c_template_object_details
1038 IS
1039 SELECT source_object_type_code
1040 FROM jtf_task_temp_groups_b
1041 WHERE task_template_group_id = (SELECT task_group_id
1042 FROM jtf_task_templates_b
1043 WHERE task_template_id = p_task_id);
1044 BEGIN
1045 x_return_status := fnd_api.g_ret_sts_success;
1046
1047 IF p_template_flag = jtf_task_utl.g_yes
1048 THEN
1049 OPEN c_template_object_details;
1050 FETCH c_template_object_details INTO x_source_object_code;
1051
1052 IF c_template_object_details%NOTFOUND
1053 THEN
1054 x_return_status := fnd_api.g_ret_sts_unexp_error;
1055 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_TEMP_ID');
1056 fnd_message.set_token ('P_TASK_TEMPLATE_ID', p_task_id);
1057 fnd_msg_pub.add;
1058 END IF;
1059 ELSE
1060 OPEN c_task_object_details;
1061 FETCH c_task_object_details INTO x_source_object_code;
1062
1063 IF c_task_object_details%NOTFOUND
1064 THEN
1065 x_return_status := fnd_api.g_ret_sts_unexp_error;
1066 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_TASK_ID');
1067 fnd_message.set_token ('P_TASK_ID', p_task_id);
1068 fnd_msg_pub.add;
1069 END IF;
1070 END IF;
1071 END;
1072
1073 -----------
1074 ----------- Validate Party Site/Addres
1075 -----------
1076 PROCEDURE validate_party_site (
1077 p_party_site_id IN NUMBER,
1078 p_party_site_number IN VARCHAR2,
1079 x_return_status OUT NOCOPY VARCHAR2,
1080 x_party_site_id IN OUT NOCOPY NUMBER -- Fixed from OUT to IN OUT
1081 )
1082 IS
1083 CURSOR c_party_site_id
1084 IS
1085 SELECT party_site_id
1086 FROM hz_party_sites
1087 WHERE party_site_id = p_party_site_id;
1088
1089 CURSOR c_party_site_number
1090 IS
1091 SELECT party_site_id
1092 FROM hz_party_sites
1093 WHERE party_site_number = p_party_site_number;
1094 BEGIN
1095 x_return_status := fnd_api.g_ret_sts_success;
1096
1097 IF p_party_site_id IS NOT NULL
1098 THEN
1099 OPEN c_party_site_id;
1100 FETCH c_party_site_id INTO x_party_site_id;
1101
1102 IF c_party_site_id%NOTFOUND
1103 THEN
1104 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_SITE_ID');
1105 fnd_message.set_token ('P_PARTY_SITE_ID', p_party_site_id);
1106 fnd_msg_pub.add;
1107 x_return_status := fnd_api.g_ret_sts_unexp_error;
1108 RAISE fnd_api.g_exc_unexpected_error;
1109 END IF;
1110
1111 CLOSE c_party_site_id;
1112 ELSIF p_party_site_number IS NOT NULL
1113 THEN
1114 OPEN c_party_site_number;
1115 FETCH c_party_site_number INTO x_party_site_id;
1116
1117 IF c_party_site_number%NOTFOUND
1118 THEN
1119 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_SITE_NUMBER');
1120 fnd_message.set_token ('P_PARTY_SITE_NUMBER', p_party_site_number);
1121 fnd_msg_pub.add;
1122 x_return_status := fnd_api.g_ret_sts_unexp_error;
1123 RAISE fnd_api.g_exc_unexpected_error;
1124 END IF;
1125
1126 CLOSE c_party_site_number;
1127 END IF;
1128 END;
1129
1130 -----------
1131 ----------- Validate Party/Customer
1132 -----------
1133 PROCEDURE validate_party (
1134 p_party_id IN NUMBER,
1135 p_party_number IN VARCHAR2,
1136 x_return_status OUT NOCOPY VARCHAR2,
1137 x_party_id IN OUT NOCOPY NUMBER -- Fixed from OUT to IN OUT
1138 )
1139 IS
1140 CURSOR c_party_id
1141 IS
1142 SELECT party_id
1143 FROM hz_parties
1144 WHERE party_id = p_party_id;
1145
1146 CURSOR c_party_number
1147 IS
1148 SELECT party_id
1149 FROM hz_parties
1150 WHERE party_number = p_party_number;
1151 BEGIN
1152 x_return_status := fnd_api.g_ret_sts_success;
1153
1154 IF p_party_id IS NOT NULL
1155 THEN
1156 OPEN c_party_id;
1157 FETCH c_party_id INTO x_party_id;
1158
1159 IF c_party_id%NOTFOUND
1160 THEN
1161 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_PARTY_ID');
1162 fnd_message.set_token ('P_PARTY_ID', p_party_id);
1163 fnd_msg_pub.add;
1164 x_return_status := fnd_api.g_ret_sts_unexp_error;
1165 RAISE fnd_api.g_exc_unexpected_error;
1166 END IF;
1167
1168 CLOSE c_party_id;
1169 ELSIF p_party_number IS NOT NULL
1170 THEN
1171 OPEN c_party_number;
1172 FETCH c_party_number INTO x_party_id;
1173
1174 IF c_party_number%NOTFOUND
1175 THEN
1176 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_PARTY_NUMBER');
1177 fnd_message.set_token (
1178 'PARTY_NUMBER', -- Bug# 2120668
1179 p_party_number
1180 );
1181 fnd_msg_pub.add;
1182 x_return_status := fnd_api.g_ret_sts_unexp_error;
1183 RAISE fnd_api.g_exc_unexpected_error;
1184 END IF;
1185
1186 CLOSE c_party_number;
1187 END IF;
1188 END;
1189
1190 -----------
1191 ----------- Validate customer Account
1192 -----------
1193 PROCEDURE validate_cust_account (
1194 p_cust_account_id IN NUMBER,
1195 p_cust_account_number IN VARCHAR2,
1196 x_return_status OUT NOCOPY VARCHAR2,
1197 x_cust_account_id IN OUT NOCOPY NUMBER -- Fixed from OUT to IN OUT
1198 )
1199 IS
1200 CURSOR c_cust_account_id
1201 IS
1202 SELECT cust_account_id
1203 FROM hz_cust_accounts
1204 WHERE cust_account_id = p_cust_account_id;
1205
1206 CURSOR c_cust_account_number
1207 IS
1208 SELECT cust_account_id
1209 FROM hz_cust_accounts
1210 WHERE account_number = p_cust_account_number;
1211 BEGIN
1212 x_return_status := fnd_api.g_ret_sts_success;
1213
1214 IF p_cust_account_id IS NOT NULL
1215 THEN
1216 OPEN c_cust_account_id;
1217 FETCH c_cust_account_id INTO x_cust_account_id;
1218
1219 IF c_cust_account_id%NOTFOUND
1220 THEN
1221 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_CUST_ACCT_ID');
1222 fnd_message.set_token ('P_CUST_ACCOUNT_ID', p_cust_account_id);
1223 fnd_msg_pub.add;
1224 x_return_status := fnd_api.g_ret_sts_unexp_error;
1225 RAISE fnd_api.g_exc_unexpected_error;
1226 END IF;
1227
1228 CLOSE c_cust_account_id;
1229 ELSIF p_cust_account_number IS NOT NULL
1230 THEN
1231 OPEN c_cust_account_number;
1232 FETCH c_cust_account_number INTO x_cust_account_id;
1233
1234 IF c_cust_account_number%NOTFOUND
1235 THEN
1236 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_CUST_ACCT_NUM'); -- Bug# 2120231
1237 fnd_message.set_token ('P_CUST_ACCOUNT_NUMBER', p_cust_account_number);
1238 fnd_msg_pub.add;
1239 x_return_status := fnd_api.g_ret_sts_unexp_error;
1240 RAISE fnd_api.g_exc_unexpected_error;
1241 END IF;
1242
1243 CLOSE c_cust_account_number;
1244 END IF;
1245 END;
1246
1247 -----------
1248 ----------- Validate customer info.
1249 -----------
1250 PROCEDURE validate_customer_info (
1251 p_cust_account_number IN VARCHAR2,
1252 p_cust_account_id IN NUMBER,
1253 p_customer_number IN VARCHAR2,
1254 p_customer_id IN NUMBER,
1255 p_address_id IN NUMBER,
1256 p_address_number IN VARCHAR2,
1257 x_return_status OUT NOCOPY VARCHAR2,
1258 x_cust_account_id IN OUT NOCOPY NUMBER, -- Fixed from OUT to IN OUT
1259 x_customer_id IN OUT NOCOPY NUMBER, -- Fixed from OUT to IN OUT
1260 x_address_id IN OUT NOCOPY NUMBER -- Fixed from OUT to IN OUT
1261 )
1262 IS
1263 BEGIN
1264 jtf_task_utl.validate_cust_account (
1265 p_cust_account_id => p_cust_account_id,
1266 p_cust_account_number => p_cust_account_number,
1267 x_return_status => x_return_status,
1268 x_cust_account_id => x_cust_account_id
1269 );
1270
1271 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
1272 THEN
1273 x_return_status := fnd_api.g_ret_sts_unexp_error;
1274 RAISE fnd_api.g_exc_unexpected_error;
1275 END IF;
1276
1277 jtf_task_utl.validate_party (
1278 p_party_id => p_customer_id,
1279 p_party_number => p_customer_number,
1280 x_return_status => x_return_status,
1281 x_party_id => x_customer_id
1282 );
1283
1284 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
1285 THEN
1286 x_return_status := fnd_api.g_ret_sts_unexp_error;
1287 RAISE fnd_api.g_exc_unexpected_error;
1288 END IF;
1289
1290 jtf_task_utl.validate_party_site (
1291 p_party_site_id => p_address_id,
1292 p_party_site_number => p_address_number,
1293 x_return_status => x_return_status,
1294 x_party_site_id => x_address_id
1295 );
1296
1297 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
1298 THEN
1299 x_return_status := fnd_api.g_ret_sts_unexp_error;
1300 RAISE fnd_api.g_exc_unexpected_error;
1301 END IF;
1302 /* removing fix for bug #1628560
1303 ----
1304 ---- Cross-validate customer/address/account
1305 ----
1306 jtf_task_utl.validate_party_site_acct (
1307 p_party_id => p_customer_id,
1308 p_party_site_id => p_address_id,
1309 p_cust_account_id => p_cust_account_id,
1310 x_return_status => x_return_status
1311 );
1312
1313 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
1314 THEN
1315 x_return_status := fnd_api.g_ret_sts_unexp_error;
1316 RAISE fnd_api.g_exc_unexpected_error;
1317 END IF;
1318 */
1319
1320 END;
1321
1322 -----------
1323 ----------- Validate Source Object Details
1324 -----------
1325 PROCEDURE validate_source_object (
1326 p_object_code IN VARCHAR2,
1327 p_object_id IN NUMBER,
1328 p_tag IN VARCHAR2,
1329 p_object_name IN VARCHAR2,
1330 x_return_status OUT NOCOPY VARCHAR2
1331 )
1332 IS
1333 CURSOR c_references
1334 IS
1335 SELECT select_id, select_name, from_table, where_clause
1336 FROM jtf_objects_b
1337 WHERE object_code = p_object_code
1338 AND object_code IN (SELECT object_code
1339 FROM jtf_object_usages
1340 WHERE object_user_code = 'TASK');
1341
1342
1343 l_id_column jtf_objects_b.select_id%TYPE;
1344 l_name_column jtf_objects_b.select_name%TYPE;
1345 l_from_clause jtf_objects_b.from_table%TYPE;
1346 l_where_clause jtf_objects_b.where_clause%TYPE;
1347 l_object_code jtf_tasks_b.source_object_type_code%TYPE
1348 := p_object_code;
1349 --Fix bug 2430516, when EXECUTE IMMEDIATE sql_stmt below, the length of
1350 --l_object_name could exceed 80 chars
1351 --l_object_name jtf_tasks_b.source_object_name%TYPE
1352 l_object_name VARCHAR2(2000)
1353 := p_object_name;
1354 l_object_id jtf_tasks_b.source_object_id%TYPE
1355 := p_object_id;
1356 /************* Comment out (Bug 2118130) *****************************
1357 --is_null BOOLEAN := FALSE;
1358 --is_not_null BOOLEAN := FALSE;
1359 *********************************************************************/
1360 sql_stmt VARCHAR2(2000);
1361 x NUMBER;
1362 l_tag VARCHAR2(2000);
1363 BEGIN
1364 x_return_status := fnd_api.g_ret_sts_success;
1365
1366 /************* Comment out (Bug 2118130) *****************************
1367 --IF ( l_object_code IS NULL
1368 -- OR l_object_name IS NULL
1369 -- OR l_object_id IS NULL)
1370 --THEN
1371 -- is_null := TRUE;
1372 --END IF;
1373 --
1374 --IF ( l_object_code IS NOT NULL
1375 -- OR l_object_name IS NOT NULL
1376 -- OR l_object_id IS NOT NULL)
1377 --THEN
1378 -- is_not_null := TRUE;
1379 --END IF;
1380 --
1381 --IF is_null
1382 -- AND is_not_null
1383 --THEN
1384 *********************************************************************/
1385
1386 /**** Make the statement clear (Bug 2118130) ***********************/
1387 /** Fixing Bug 2602732 **/
1388 IF ( l_object_code IS NULL AND
1389 l_object_name IS NULL AND
1390 l_object_id IS NULL)
1391 THEN
1392 return;
1393 ELSIF NOT ( l_object_code IS NOT NULL AND
1394 l_object_id IS NOT NULL)
1395 THEN
1396 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_SOURCE_DTLS');
1397 fnd_msg_pub.add;
1398 x_return_status := fnd_api.g_ret_sts_unexp_error;
1399 RAISE fnd_api.g_exc_unexpected_error;
1400 END IF;
1401 /**** Make the statement clear (Bug 2118130) ***********************/
1402
1403 /************* Comment out (Bug 2118130) *****************************
1404 --IF is_null
1405 --THEN
1406 -- RETURN;
1407 --END IF;
1408 *********************************************************************/
1409
1410 OPEN c_references;
1411 FETCH c_references INTO l_id_column,
1412 l_name_column,
1413 l_from_clause,
1414 l_where_clause;
1415
1416 IF c_references%NOTFOUND
1417 THEN
1418 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_OBJECT_CODE');
1419 -- Bug 3418166 message token is null if p_tag is not passed in.
1420 -- Added message token to ensure that message token has accurate data.
1421 IF p_tag IS NOT NULL THEN
1422 l_tag := p_tag;
1423 ELSIF p_object_name IS NOT NULL THEN
1424 l_tag := p_object_name;
1425 ELSIF p_object_code IS NOT NULL THEN
1426 l_tag := p_object_code;
1427 ELSIF p_object_id IS NOT NULL THEN
1428 l_tag := p_object_id;
1429 END IF;
1430
1431 fnd_message.set_token ('P_OBJECT_CODE', l_tag);
1432 fnd_msg_pub.add;
1433 RAISE fnd_api.g_exc_unexpected_error;
1434 END IF;
1435
1436 /************* Comment out (Bug 2118130) *****************************
1437 --IF p_object_name IS NULL
1438 -- OR p_object_id IS NULL
1439 --THEN
1440 -- fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_SOURCE_DTLS');
1441 -- fnd_msg_pub.add;
1442 -- RAISE fnd_api.g_exc_unexpected_error;
1443 --END IF;
1444 *********************************************************************/
1445
1446 -- SELECT DECODE (
1447 -- l_where_clause,
1448 -- NULL, ' where ',
1449 -- ' where ' || l_where_clause || ' AND '
1450 -- )
1451 -- INTO
1452 -- l_where_clause
1453 -- FROM dual;
1454
1455 -- Fix for bug 2932012
1456 IF (l_where_clause IS NULL)
1457 THEN
1458 l_where_clause := ' where ';
1459 ELSE
1460 l_where_clause := ' where ' || l_where_clause || ' AND ';
1461 END IF;
1462
1463 -- Added 'rownum = 1' bshih
1464 sql_stmt := ' SELECT ' ||
1465 l_name_column ||
1466 ' , ' ||
1467 l_id_column ||
1468 ' from ' ||
1469 l_from_clause ||
1470 l_where_clause ||
1471 l_id_column ||
1472 ' = :object_id and rownum = 1';
1473
1474 EXECUTE IMMEDIATE sql_stmt INTO l_object_name, l_object_id
1475 USING p_object_id;
1476
1477 -- fix bug #2180381
1478 -- fix bug# 2602732
1479 -- removing the validation for p_object_name
1480 IF (l_object_id <> p_object_id)
1481 THEN
1482 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_SOURCE_DTLS');
1483 fnd_msg_pub.add;
1484 x_return_status := fnd_api.g_ret_sts_unexp_error;
1485 RAISE fnd_api.g_exc_unexpected_error;
1486 END IF;
1487 EXCEPTION
1488 WHEN fnd_api.g_exc_unexpected_error
1489 THEN
1490 x_return_status := fnd_api.g_ret_sts_unexp_error;
1491 WHEN NO_DATA_FOUND
1492 THEN
1493 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_SOURCE_DTLS');
1494 fnd_msg_pub.add;
1495 x_return_status := fnd_api.g_ret_sts_unexp_error;
1496 WHEN OTHERS
1497 THEN
1498 x_return_status := fnd_api.g_ret_sts_unexp_error;
1499 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
1500 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
1501 fnd_msg_pub.add;
1502 END;
1503
1504 -----------
1505 ----------- Validate Task
1506 -----------
1507 PROCEDURE validate_task (
1508 x_return_status OUT NOCOPY VARCHAR2,
1509 p_task_id IN NUMBER,
1510 p_task_number IN VARCHAR2,
1511 x_task_id IN OUT NOCOPY NUMBER -- Fixed from OUT to IN OUT
1512 )
1513 IS
1514 CURSOR c_task_id
1515 IS
1516 SELECT task_id
1517 FROM jtf_tasks_b
1518 WHERE task_id = p_task_id;
1519
1520 CURSOR c_task_number
1521 IS
1522 SELECT task_id
1523 FROM jtf_tasks_b
1524 WHERE task_number = p_task_number;
1525
1526 l_task_id jtf_tasks_b.task_id%TYPE;
1527 l_task_number jtf_tasks_b.task_number%TYPE;
1528 l_task_name jtf_tasks_vl.task_name%TYPE;
1529 done BOOLEAN := FALSE;
1530 BEGIN
1531 x_return_status := fnd_api.g_ret_sts_success;
1532
1533 --- Assume correct task id is supplied
1534 IF p_task_id IS NOT NULL
1535 THEN
1536 OPEN c_task_id;
1537 FETCH c_task_id INTO l_task_id;
1538
1539 IF c_task_id%NOTFOUND
1540 THEN
1541 x_return_status := fnd_api.g_ret_sts_unexp_error;
1542 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_TASK_ID');
1543 fnd_message.set_token ('P_TASK_ID', p_task_id);
1544 fnd_msg_pub.add;
1545 END IF;
1546 ELSIF --- if the task id is null, then ignore
1547 (p_task_number IS NOT NULL)
1548 THEN
1549 OPEN c_task_number;
1550 FETCH c_task_number INTO l_task_id;
1551
1552 IF c_task_number%NOTFOUND
1553 THEN
1554 x_return_status := fnd_api.g_ret_sts_unexp_error;
1555 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_TASK_NUMBER');
1556 fnd_message.set_token ('P_TASK_NUMBER', p_task_number);
1557 fnd_msg_pub.add;
1558 END IF;
1559 END IF;
1560
1561 x_task_id := l_task_id;
1562 END;
1563
1564 PROCEDURE validate_task_template (
1565 x_return_status OUT NOCOPY VARCHAR2,
1566 p_task_id IN NUMBER,
1567 p_task_number IN VARCHAR2,
1568 x_task_id IN OUT NOCOPY NUMBER -- Fixed from OUT to IN OUT
1569 )
1570 IS
1571 CURSOR c_task_id
1572 IS
1573 SELECT task_template_id task_id
1574 FROM jtf_task_templates_b
1575 WHERE task_template_id = p_task_id;
1576
1577 CURSOR c_task_number
1578 IS
1579 SELECT task_template_id task_id
1580 FROM jtf_task_templates_b
1581 WHERE task_number = p_task_number;
1582
1583 l_task_id jtf_tasks_b.task_id%TYPE;
1584 l_task_number jtf_tasks_b.task_number%TYPE;
1585 l_task_name jtf_tasks_vl.task_name%TYPE;
1586 done BOOLEAN := FALSE;
1587 BEGIN
1588 x_return_status := fnd_api.g_ret_sts_success;
1589
1590 --- Assume correct task id is supplied
1591 IF p_task_id IS NOT NULL
1592 THEN
1593 OPEN c_task_id;
1594 FETCH c_task_id INTO l_task_id;
1595
1596 IF c_task_id%NOTFOUND
1597 THEN
1598 x_return_status := fnd_api.g_ret_sts_unexp_error;
1599 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_TEMP_ID');
1600 fnd_message.set_token ('P_TASK_TEMPLATE_ID', p_task_id);
1601 fnd_msg_pub.add;
1602 END IF;
1603 ELSIF --- if the task id is null, then ignore
1604 (p_task_number IS NOT NULL)
1605 THEN
1606 OPEN c_task_number;
1607 FETCH c_task_number INTO l_task_id;
1608
1609 IF c_task_number%NOTFOUND
1610 THEN
1611 x_return_status := fnd_api.g_ret_sts_unexp_error;
1612 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_TEMP_NUMBER');
1613 fnd_message.set_token ('P_TASK_TEMPLATE_NUMBER', p_task_number);
1614 fnd_msg_pub.add;
1615 END IF;
1616 END IF;
1617
1618 x_task_id := l_task_id;
1619 END;
1620
1621 ------------
1622 ------------
1623 ------------
1624 FUNCTION validate_dependency_code (p_dependency_code IN VARCHAR2)
1625 RETURN BOOLEAN
1626 IS
1627 CURSOR c_dependency_type_code
1628 IS
1629 SELECT 1
1630 FROM fnd_lookup_values
1631 WHERE lookup_type = 'JTF_TASK_DEPENDENCY_TYPES'
1632 AND lookup_code = p_dependency_code;
1633
1634 x CHAR;
1635 l_result BOOLEAN;
1636 BEGIN
1637 OPEN c_dependency_type_code;
1638 FETCH c_dependency_type_code INTO x;
1639
1640 IF c_dependency_type_code%NOTFOUND
1641 THEN
1642 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_DEPENDS_CODE');
1643 fnd_message.set_token ('P_DEPENDENCY_CODE', p_dependency_code);
1644 fnd_msg_pub.add;
1645 l_result := FALSE;
1646 ELSE
1647 l_result := TRUE;
1648 END IF;
1649
1650 CLOSE c_dependency_type_code;
1651 RETURN l_result;
1652 END;
1653
1654 FUNCTION validate_time_uom (p_uom_code IN VARCHAR2)
1655 RETURN VARCHAR2
1656 IS
1657 CURSOR c_jtf_uom
1658 IS
1659 SELECT 1
1660 FROM mtl_units_of_measure
1661 WHERE uom_class = g_uom_time_class
1662 AND uom_code = p_uom_code;
1663
1664 x CHAR;
1665 l_result CHAR;
1666 BEGIN
1667 OPEN c_jtf_uom;
1668 FETCH c_jtf_uom INTO x;
1669
1670 IF c_jtf_uom%NOTFOUND
1671 THEN
1672 l_result := fnd_api.g_false;
1673 ELSE
1674 l_result := fnd_api.g_true;
1675 END IF;
1676
1677 CLOSE c_jtf_uom;
1678 RETURN l_result;
1679 END;
1680
1681 ---------
1682 --------- Validate effort
1683 ---------
1684 PROCEDURE validate_effort (
1685 p_api_name IN VARCHAR2,
1686 p_init_msg_list IN VARCHAR2,
1687 p_tag IN VARCHAR2,
1688 p_tag_uom IN VARCHAR2,
1689 x_return_status OUT NOCOPY VARCHAR2,
1690 p_effort IN NUMBER,
1691 p_effort_uom IN VARCHAR2
1692 )
1693 IS
1694 l_effort number;
1695 l_effort_uom varchar2(3);
1696 BEGIN
1697 x_return_status := fnd_api.g_ret_sts_success;
1698
1699 if (p_effort = fnd_api.g_miss_num)
1700 then
1701 l_effort := null;
1702 else
1703 l_effort := p_effort;
1704 end if;
1705
1706 if (p_effort_uom = fnd_api.g_miss_char)
1707 then
1708 l_effort_uom := null;
1709 else
1710 l_effort_uom := p_effort_uom;
1711 end if;
1712
1713 IF (l_effort IS NULL AND l_effort_uom IS NOT NULL)
1714 THEN
1715 x_return_status := fnd_api.g_ret_sts_unexp_error;
1716 fnd_message.set_name ('JTF', 'JTF_TASK_MISSING_QUANTITY');
1717 fnd_message.set_token ('P_TAG', p_tag);
1718 fnd_msg_pub.add;
1719 ELSIF (l_effort IS NOT NULL AND l_effort_uom IS NULL)
1720 THEN
1721 x_return_status := fnd_api.g_ret_sts_unexp_error;
1722 fnd_message.set_name ('JTF', 'JTF_TASK_MISSING_UOM');
1723 fnd_message.set_token ('P_TAG', p_tag);
1724 fnd_msg_pub.add;
1725 ELSIF (l_effort IS NOT NULL AND l_effort_uom IS NOT NULL)
1726 THEN
1727 IF l_effort < g_minimum_effort
1728 THEN
1729 x_return_status := fnd_api.g_ret_sts_error;
1730 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_QUANTITY');
1731 fnd_message.set_token ('P_TAG', p_tag);
1732 fnd_msg_pub.add;
1733 END IF;
1734
1735 IF NOT fnd_api.to_boolean (jtf_task_utl.validate_time_uom(l_effort_uom))
1736 THEN
1737 x_return_status := fnd_api.g_ret_sts_unexp_error;
1738 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_UOM');
1739 fnd_message.set_token ('P_UOM_CODE', l_effort_uom); -- Fix Bug 2118718
1740 fnd_msg_pub.add;
1741 END IF;
1742 ELSE
1743 NULL;
1744 END IF;
1745 END;
1746
1747 ---------------------
1748 ----- Task Type
1749 ---------------------
1750 ---------------------
1751 PROCEDURE validate_task_type (
1752 p_task_type_id IN NUMBER,
1753 p_task_type_name IN VARCHAR2,
1754 x_return_status OUT NOCOPY VARCHAR2,
1755 x_task_type_id IN OUT NOCOPY NUMBER -- Fixed from OUT to IN OUT
1756 )
1757 IS
1758 CURSOR c_task_type_id
1759 IS
1760 SELECT task_type_id
1761 FROM jtf_task_types_b
1762 WHERE task_type_id = p_task_type_id
1763 AND trunc(NVL (end_date_active, SYSDATE)) >= trunc(SYSDATE)
1764 AND trunc(NVL (start_date_active, SYSDATE)) <= trunc(SYSDATE);
1765
1766 CURSOR c_task_type_name
1767 IS
1768 SELECT task_type_id
1769 FROM jtf_task_types_vl
1770 WHERE name = p_task_type_name
1771 AND trunc(NVL (end_date_active, SYSDATE)) >= trunc(SYSDATE)
1772 AND trunc(NVL (start_date_active, SYSDATE)) <= trunc(SYSDATE);
1773 BEGIN
1774 x_return_status := fnd_api.g_ret_sts_success;
1775
1776 IF p_task_type_id IS NOT NULL
1777 THEN
1778 OPEN c_task_type_id;
1779 FETCH c_task_type_id INTO x_task_type_id;
1780
1781 IF c_task_type_id%NOTFOUND
1782 THEN
1783 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_TYPE_ID');
1784 fnd_message.set_token ('P_TASK_TYPE_ID', p_task_type_id);
1785 fnd_msg_pub.add;
1786 x_return_status := fnd_api.g_ret_sts_unexp_error;
1787 END IF;
1788
1789 CLOSE c_task_type_id;
1790 ELSIF p_task_type_name IS NOT NULL
1791 THEN
1792 OPEN c_task_type_name;
1793 FETCH c_task_type_name INTO x_task_type_id;
1794
1795 IF c_task_type_name%NOTFOUND
1796 THEN
1797 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_TYPE_NAME');
1798 fnd_message.set_token ('P_TASK_TYPE_NAME', p_task_type_name);
1799 fnd_msg_pub.add;
1800 RAISE fnd_api.g_exc_unexpected_error;
1801 END IF;
1802
1803 CLOSE c_task_type_name;
1804 END IF;
1805 END;
1806
1807 ---------------------
1808 ----- Task status
1809 ---------------------
1810 ---------------------
1811 PROCEDURE validate_task_status (
1812 p_task_status_id IN NUMBER,
1813 p_task_status_name IN VARCHAR2,
1814 p_validation_type IN VARCHAR2,
1815 x_return_status OUT NOCOPY VARCHAR2,
1816 x_task_status_id IN OUT NOCOPY NUMBER -- Fixed from OUT to IN OUT
1817 )
1818 IS
1819 CURSOR c_task_status_id
1820 IS
1821 SELECT task_status_id
1822 FROM jtf_task_statuses_b
1823 WHERE task_status_id = p_task_status_id
1824 AND trunc(NVL (end_date_active, SYSDATE)) >= trunc(SYSDATE)
1825 AND trunc(NVL (start_date_active, SYSDATE)) <= trunc(SYSDATE);
1826
1827 CURSOR c_task_status_name
1828 IS
1829 SELECT task_status_id
1830 FROM jtf_task_statuses_vl
1831 WHERE name = p_task_status_name
1832 AND trunc(NVL (end_date_active, SYSDATE)) >= trunc(SYSDATE)
1833 AND trunc(NVL (start_date_active, SYSDATE)) <= trunc(SYSDATE);
1834
1835 l_type varchar2(10);
1836
1837 BEGIN
1838 x_return_status := fnd_api.g_ret_sts_success;
1839
1840 IF p_task_status_id IS NOT NULL
1841 THEN
1842 OPEN c_task_status_id;
1843 FETCH c_task_status_id INTO x_task_status_id;
1844
1845 IF c_task_status_id%NOTFOUND
1846 THEN
1847 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_STATUS_ID');
1848 fnd_message.set_token ('P_TASK_STATUS_ID', p_task_status_id);
1849 fnd_msg_pub.add;
1850 x_return_status := fnd_api.g_ret_sts_unexp_error;
1851 RAISE fnd_api.g_exc_unexpected_error;
1852 END IF;
1853
1854 CLOSE c_task_status_id;
1855 ELSIF p_task_status_name IS NOT NULL
1856 THEN
1857 OPEN c_task_status_name;
1858 FETCH c_task_status_name INTO x_task_status_id;
1859
1860 IF c_task_status_name%NOTFOUND
1861 THEN
1862 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_STATUS_NAME');
1863 fnd_message.set_token ('P_TASK_STATUS_NAME', p_task_status_name);
1864 fnd_msg_pub.add;
1865 x_return_status := fnd_api.g_ret_sts_unexp_error;
1866 RAISE fnd_api.g_exc_unexpected_error;
1867 END IF;
1868
1869 CLOSE c_task_status_name;
1870 END IF;
1871
1872 ---
1873 --- call new procedure to validate the status, to fix bug #1878669
1874 ---
1875 jtf_task_utl.validate_status (
1876 p_status_id => x_task_status_id,
1877 p_type => p_validation_type,
1878 x_return_status => x_return_status);
1879
1880 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
1881 THEN
1882 x_return_status := fnd_api.g_ret_sts_unexp_error;
1883 RAISE fnd_api.g_exc_unexpected_error;
1884 END IF;
1885
1886 END;
1887
1888 ---------------------
1889 ----- Task priority
1890 ---------------------
1891 ---------------------
1892 PROCEDURE validate_task_priority (
1893 p_task_priority_id IN NUMBER,
1894 p_task_priority_name IN VARCHAR2,
1895 x_return_status OUT NOCOPY VARCHAR2,
1896 x_task_priority_id IN OUT NOCOPY NUMBER -- Fixed from OUT to IN OUT
1897 )
1898 IS
1899 CURSOR c_task_priority_id
1900 IS
1901 SELECT task_priority_id
1902 FROM jtf_task_priorities_b
1903 WHERE task_priority_id = p_task_priority_id
1904 AND trunc(NVL (end_date_active, SYSDATE)) >= trunc(SYSDATE)
1905 AND trunc(NVL (start_date_active, SYSDATE)) <= trunc(SYSDATE);
1906
1907 CURSOR c_task_priority_name
1908 IS
1909 SELECT task_priority_id
1910 FROM jtf_task_priorities_vl
1911 WHERE name = p_task_priority_name
1912 AND trunc(NVL (end_date_active, SYSDATE)) >= trunc(SYSDATE)
1913 AND trunc(NVL (start_date_active, SYSDATE)) <= trunc(SYSDATE);
1914 BEGIN
1915 x_return_status := fnd_api.g_ret_sts_success;
1916 -- Bug Fix 2865490
1917 IF (p_task_priority_id IS NOT NULL) AND (p_task_priority_id <> fnd_api.g_miss_num)
1918 THEN
1919 OPEN c_task_priority_id;
1920 FETCH c_task_priority_id INTO x_task_priority_id;
1921
1922 IF c_task_priority_id%NOTFOUND
1923 THEN
1924 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_PRIORITY_ID');
1925 fnd_message.set_token ('P_TASK_PRIORITY_ID', p_task_priority_id);
1926 fnd_msg_pub.add;
1927 x_return_status := fnd_api.g_ret_sts_unexp_error;
1928 RAISE fnd_api.g_exc_unexpected_error;
1929 END IF;
1930
1931 CLOSE c_task_priority_id;
1932 -- Bug Fix 2865490
1933 ELSE IF (p_task_priority_name IS NOT NULL) AND (p_task_priority_name <> fnd_api.g_miss_char)
1934 THEN
1935 OPEN c_task_priority_name;
1936 FETCH c_task_priority_name INTO x_task_priority_id;
1937
1938 IF c_task_priority_name%NOTFOUND
1939 THEN
1940 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_PRIORITY_NAME');
1941 fnd_message.set_token (
1942 'P_TASK_PRIORITY_NAME',
1943 p_task_priority_name
1944 );
1945 fnd_msg_pub.add;
1946 x_return_status := fnd_api.g_ret_sts_unexp_error;
1947 RAISE fnd_api.g_exc_unexpected_error;
1948 END IF;
1949
1950 CLOSE c_task_priority_name;
1951 ELSE
1952 --Enhn 2496234 task priority should be defaulted to Unprioritized
1953 x_task_priority_id := 8;
1954 END IF;
1955 END IF;
1956 END;
1957
1958 --------------
1959 -------------- Date Types
1960 --------------
1961 PROCEDURE validate_date_types (
1962 p_date_type_id IN NUMBER,
1963 p_date_type IN VARCHAR2,
1964 x_return_status OUT NOCOPY VARCHAR2,
1965 x_date_type_id IN OUT NOCOPY NUMBER -- Fixed from OUT to IN OUT
1966 )
1967 IS
1968 CURSOR c_date_type_id
1969 IS
1970 SELECT date_type_id
1971 FROM jtf_task_date_types_b
1972 WHERE date_type_id = p_date_type_id;
1973
1974 CURSOR c_date_type_name
1975 IS
1976 SELECT date_type_id
1977 FROM jtf_task_date_types_vl
1978 WHERE date_type = p_date_type;
1979 BEGIN
1980 x_return_status := fnd_api.g_ret_sts_success;
1981
1982 IF p_date_type_id IS NOT NULL
1983 THEN
1984 OPEN c_date_type_id;
1985 FETCH c_date_type_id INTO x_date_type_id;
1986
1987 IF c_date_type_id%NOTFOUND
1988 THEN
1989 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_DATE_TYPE_ID');
1990 fnd_message.set_token ('P_DATE_TYPE_ID', p_date_type_id);
1991 fnd_msg_pub.add;
1992 x_return_status := fnd_api.g_ret_sts_unexp_error;
1993 END IF;
1994
1995 CLOSE c_date_type_id;
1996 ELSIF p_date_type IS NOT NULL
1997 THEN
1998 OPEN c_date_type_name;
1999 FETCH c_date_type_name INTO x_date_type_id;
2000
2001 IF c_date_type_name%NOTFOUND
2002 THEN
2003 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_DATE_TYPE_NAME');
2004 fnd_message.set_token ('P_DATE_TYPE', p_date_type);
2005 fnd_msg_pub.add;
2006 x_return_status := fnd_api.g_ret_sts_unexp_error;
2007 END IF;
2008
2009 CLOSE c_date_type_name;
2010 END IF;
2011 END;
2012
2013 --------------
2014 --------------
2015 --------------
2016 -- Commented out this procedure by SBARAT on 19/01/2006 for bug# 4888496.
2017 -- Since validate_escalation has already been commented out and validate_territory
2018 -- is not being referred from any other place. Unnecessarily, the query of c_terr_name
2019 -- comes in SQL Repository's violation list for Full Table Scan
2020 --
2021 /*PROCEDURE validate_territory (
2022 p_terr_id IN NUMBER,
2023 p_terr_name IN VARCHAR2,
2024 x_return_status OUT NOCOPY VARCHAR2,
2025 x_terr_id OUT NOCOPY NUMBER
2026 )
2027 IS
2028 CURSOR c_terr_id
2029 IS
2030 SELECT terr_id
2031 FROM jtf_terr_all
2032 WHERE terr_id = p_terr_id
2033 AND trunc(NVL (end_date_active, SYSDATE)) >= trunc(SYSDATE)
2034 AND trunc(NVL (start_date_active, SYSDATE)) <= trunc(SYSDATE);
2035
2036 CURSOR c_terr_name
2037 IS
2038 SELECT terr_id
2039 FROM jtf_terr_all
2040 WHERE name = p_terr_name
2041 AND trunc(NVL (end_date_active, SYSDATE)) >= trunc(SYSDATE)
2042 AND trunc(NVL (start_date_active, SYSDATE)) <= trunc(SYSDATE);
2043 BEGIN
2044 x_return_status := fnd_api.g_ret_sts_success;
2045
2046 IF p_terr_id IS NOT NULL
2047 THEN
2048 OPEN c_terr_id;
2049 FETCH c_terr_id INTO x_terr_id;
2050
2051 IF c_terr_id%NOTFOUND
2052 THEN
2053 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_TERR_ID');
2054 fnd_message.set_token ('P_TERR_ID', p_terr_id);
2055 fnd_msg_pub.add;
2056 x_return_status := fnd_api.g_ret_sts_unexp_error;
2057 RAISE fnd_api.g_exc_unexpected_error;
2058 END IF;
2059
2060 CLOSE c_terr_id;
2061 ELSIF p_terr_name IS NOT NULL
2062 THEN
2063 OPEN c_terr_name;
2064 FETCH c_terr_name INTO x_terr_id;
2065
2066 IF c_terr_name%NOTFOUND
2067 THEN
2068 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_TERR_NAME');
2069 fnd_message.set_token ('P_TERR_NAME', p_terr_name);
2070 fnd_msg_pub.add;
2071 x_return_status := fnd_api.g_ret_sts_unexp_error;
2072 RAISE fnd_api.g_exc_unexpected_error;
2073 END IF;
2074
2075 CLOSE c_terr_name;
2076 END IF;
2077 EXCEPTION
2078 WHEN fnd_api.g_exc_unexpected_error
2079 THEN
2080 x_return_status := fnd_api.g_ret_sts_unexp_error;
2081 END;*/
2082
2083 -----------
2084 ----------- Validate Escalation
2085 -----------
2086 /* PROCEDURE validate_escalation (
2087 p_escalation_flag IN VARCHAR2,
2088 p_owner_id IN NUMBER,
2089 p_terr_id IN NUMBER,
2090 p_terr_name IN VARCHAR2,
2091 x_owner_id OUT NUMBER,
2092 x_terr_id OUT NUMBER,
2093 x_return_status OUT VARCHAR2
2094 )
2095 IS
2096 CURSOR c_owner
2097 IS
2098 SELECT person_id
2099 FROM per_all_people_f
2100 WHERE person_id = p_owner_id;
2101 BEGIN
2102 x_return_status := fnd_api.g_ret_sts_success;
2103 jtf_task_utl.validate_flag (
2104 p_api_name => NULL,
2105 p_init_msg_list => fnd_api.g_false,
2106 x_return_status => x_return_status,
2107 p_flag_name => 'Escalation Flag',
2108 p_flag_value => p_escalation_flag
2109 );
2110
2111 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
2112 THEN
2113 x_return_status := fnd_api.g_ret_sts_unexp_error;
2114 RAISE fnd_api.g_exc_unexpected_error;
2115 END IF;
2116
2117 IF ( jtf_task_utl.to_boolean (p_escalation_flag) = FALSE
2118 OR p_escalation_flag IS NULL)
2119 THEN
2120 IF (p_owner_id IS NOT NULL)
2121 OR (p_terr_id IS NOT NULL)
2122 OR (p_terr_name IS NOT NULL)
2123 THEN
2124 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_ESC_DTLS');
2125 fnd_msg_pub.add;
2126 x_return_status := fnd_api.g_ret_sts_unexp_error;
2127 RAISE fnd_api.g_exc_unexpected_error;
2128 ELSE
2129 RETURN;
2130 END IF;
2131 END IF;
2132
2133 IF (p_owner_id IS NULL)
2134 AND ( p_terr_id IS NULL
2135 OR p_terr_name IS NULL)
2136 THEN
2137 fnd_message.set_name ('JTF', 'OWNER_OR_TERRITORY');
2138 fnd_msg_pub.add;
2139 x_return_status := fnd_api.g_ret_sts_unexp_error;
2140 RAISE fnd_api.g_exc_unexpected_error;
2141 END IF;
2142
2143 IF p_owner_id IS NOT NULL
2144 THEN
2145 OPEN c_owner;
2146 FETCH c_owner INTO x_owner_id;
2147
2148 IF c_owner%NOTFOUND
2149 THEN
2150 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_OWNER_ID');
2151 fnd_message.set_token ('P_OWNER_ID', p_owner_id);
2152 fnd_msg_pub.add;
2153 x_return_status := fnd_api.g_ret_sts_unexp_error;
2154 RAISE fnd_api.g_exc_unexpected_error;
2155 END IF;
2156 END IF;
2157
2158 IF ( p_terr_id IS NOT NULL
2159 OR p_terr_name IS NOT NULL)
2160 THEN
2161 jtf_task_utl.validate_territory (
2162 p_terr_id => p_terr_id,
2163 p_terr_name => p_terr_name,
2164 x_return_status => x_return_status,
2165 x_terr_id => x_terr_id
2166 );
2167
2168 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
2169 THEN
2170 x_return_status := fnd_api.g_ret_sts_unexp_error;
2171 RAISE fnd_api.g_exc_unexpected_error;
2172 END IF;
2173 END IF;
2174
2175
2176 EXCEPTION
2177 WHEN fnd_api.g_exc_unexpected_error
2178 THEN
2179 x_return_status := fnd_api.g_ret_sts_unexp_error;
2180 END;
2181 */
2182 --------------
2183 -------------- Validate Task Object Type
2184 --------------
2185 /* PROCEDURE validate_task_object_type (
2186 p_object_type_name IN VARCHAR2,
2187 p_object_code IN NUMBER,
2188 x_return_status OUT VARCHAR2,
2189 x_object_code OUT NUMBER
2190 )
2191 IS
2192 CURSOR c_object_code
2193 IS
2194 SELECT object_code
2195 FROM jtf_objects_b
2196 WHERE object_code = p_object_code;
2197
2198 CURSOR c_object_type_name
2199 IS
2200 SELECT object_code
2201 FROM jtf_objects_vl
2202 WHERE name = p_object_type_name;
2203
2204 BEGIN
2205 x_return_status := fnd_api.g_ret_sts_success;
2206
2207 IF p_object_code IS NOT NULL
2208 THEN
2209 OPEN c_object_code;
2210 FETCH c_object_code INTO x_object_code;
2211
2212 IF c_object_code%NOTFOUND
2213 THEN
2214 fnd_message.set_name ('JTF', 'invalid_object_type');
2215 fnd_msg_pub.add;
2216 x_return_status := fnd_api.g_ret_sts_unexp_error;
2217 END IF;
2218
2219 CLOSE c_object_code;
2220 ELSIF p_object_type_name IS NOT NULL
2221 THEN
2222 OPEN c_object_type_name;
2223 FETCH c_object_type_name INTO x_object_code;
2224
2225 IF c_object_type_name%NOTFOUND
2226 THEN
2227 fnd_message.set_name ('JTF', 'invalid_object_type');
2228 fnd_msg_pub.add;
2229 x_return_status := fnd_api.g_ret_sts_unexp_error;
2230 END IF;
2231
2232 CLOSE c_object_type_name;
2233 END IF;
2234 END;
2235 */
2236 PROCEDURE validate_task_owner (
2237 p_owner_type_name IN VARCHAR2,
2238 p_owner_type_code IN VARCHAR2,
2239 p_owner_id IN NUMBER,
2240 x_return_status OUT NOCOPY VARCHAR2,
2241 x_owner_id IN OUT NOCOPY NUMBER, -- Fixed from OUT to IN OUT
2242 x_owner_type_code IN OUT NOCOPY VARCHAR2 -- Fixed from OUT to IN OUT
2243 )
2244 IS
2245 l_owner_type_code jtf_objects_b.object_code%TYPE
2246 := p_owner_type_code;
2247 l_owner_type_name jtf_objects_tl.name%TYPE
2248 := p_owner_type_name;
2249 l_id_column jtf_objects_b.select_id%TYPE;
2250 l_from_clause jtf_objects_b.from_table%TYPE;
2251 l_where_clause jtf_objects_b.where_clause%TYPE;
2252 sql_stmt VARCHAR2(4000);
2253 l_owner_id jtf_tasks_b.owner_id%TYPE := p_owner_id;
2254
2255 CURSOR c_owner_type
2256 IS
2257
2258 SELECT select_id, from_table, where_clause
2259 FROM jtf_objects_b
2260 WHERE object_code = l_owner_type_code;
2261 BEGIN
2262 x_return_status := fnd_api.g_ret_sts_success;
2263 jtf_task_utl.validate_object_type (
2264 p_object_code => p_owner_type_code,
2265 p_object_type_name => p_owner_type_name,
2266 p_object_type_tag => 'Owner',
2267 p_object_usage => 'RESOURCES',
2268 x_return_status => x_return_status,
2269 x_object_code => l_owner_type_code
2270 );
2271
2272 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
2273 THEN
2274 x_return_status := fnd_api.g_ret_sts_unexp_error;
2275 RAISE fnd_api.g_exc_unexpected_error;
2276 END IF;
2277
2278 OPEN c_owner_type;
2279 FETCH c_owner_type INTO l_id_column, l_from_clause, l_where_clause;
2280
2281 -- Bug 3128054 fixed by TSINGHAL removed dbms messages
2282 IF c_owner_type%NOTFOUND THEN
2283 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_OBJECT_CODE');
2284 fnd_message.set_token ('P_OBJECT_CODE', 'Owner');
2285 fnd_msg_pub.add;
2286 x_return_status := fnd_api.g_ret_sts_unexp_error;
2287 RAISE fnd_api.g_exc_unexpected_error;
2288 END IF;
2289
2290 -- SELECT DECODE (
2291 -- l_where_clause,
2292 -- NULL, l_where_clause,
2293 -- l_where_clause || ' AND '
2294 -- )
2295 --INTO
2296 -- l_where_clause
2297 --FROM dual;
2298
2299 -- Fix for bug 2932012
2300 IF (l_where_clause IS NULL)
2301 THEN
2302 NULL;
2303 ELSE
2304 l_where_clause := l_where_clause || ' AND ';
2305 END IF;
2306
2307 -- Added 'rownum = 1' bshih
2308 sql_stmt := 'SELECT ' ||
2309 l_id_column ||
2310 ' from ' ||
2311 l_from_clause ||
2312 ' where ' ||
2313 l_where_clause ||
2314 l_id_column ||
2315 ' = :owner_id and rownum = 1';
2316
2317 BEGIN
2318 EXECUTE IMMEDIATE sql_stmt INTO l_owner_id
2319 USING l_owner_id;
2320 EXCEPTION
2321 WHEN OTHERS
2322 THEN
2323 x_owner_id := NULL;
2324 x_owner_type_code := NULL;
2325 x_return_status := fnd_api.g_ret_sts_unexp_error;
2326 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_OWNER_ID');
2327 fnd_message.set_token ('P_OWNER_ID', p_owner_id);
2328 fnd_msg_pub.add;
2329 RAISE fnd_api.g_exc_unexpected_error;
2330 END;
2331
2332 x_owner_id := l_owner_id;
2333 x_owner_type_code := l_owner_type_code;
2334 EXCEPTION
2335 WHEN NO_DATA_FOUND
2336 THEN
2337 x_owner_id := NULL;
2338 x_owner_type_code := NULL;
2339 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_OWNER_ID');
2340 fnd_message.set_token ('P_OWNER_ID', p_owner_id);
2341 fnd_msg_pub.add;
2342 x_return_status := fnd_api.g_ret_sts_unexp_error;
2343 RAISE fnd_api.g_exc_unexpected_error;
2344 WHEN fnd_api.g_exc_unexpected_error
2345 THEN
2346 x_return_status := fnd_api.g_ret_sts_unexp_error;
2347 RAISE fnd_api.g_exc_unexpected_error;
2348 WHEN OTHERS
2349 THEN
2350 x_owner_id := NULL;
2351 x_owner_type_code := NULL;
2352 x_return_status := fnd_api.g_ret_sts_unexp_error;
2353 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
2354 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
2355 fnd_msg_pub.add;
2356 RAISE fnd_api.g_exc_unexpected_error;
2357 END;
2358
2359 -----------------------------------------------------------
2360 -----------------------------------------------------------
2361 ---------------- Time zones
2362 -----------------------------------------------------------
2363 PROCEDURE validate_timezones (
2364 p_timezone_id IN NUMBER,
2365 p_timezone_name IN VARCHAR2,
2366 x_return_status OUT NOCOPY VARCHAR2,
2367 x_timezone_id IN OUT NOCOPY NUMBER -- Fixed from OUT to IN OUT
2368 )
2369 IS
2370 CURSOR c_timezone_id
2371 IS
2372 SELECT timezone_id
2373 FROM hz_timezones
2374 WHERE timezone_id = p_timezone_id;
2375
2376 CURSOR c_timezone_name
2377 IS
2378 SELECT timezone_id
2379 FROM hz_timezones
2380 WHERE global_timezone_name = p_timezone_name;
2381 BEGIN
2382 x_return_status := fnd_api.g_ret_sts_success;
2383
2384 IF p_timezone_id IS NOT NULL
2385 THEN
2386 OPEN c_timezone_id;
2387 FETCH c_timezone_id INTO x_timezone_id;
2388
2389 IF c_timezone_id%NOTFOUND
2390 THEN
2391 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_TIMEZONE_ID');
2392 fnd_message.set_token ('P_TIMEZONE_ID', p_timezone_id);
2393 fnd_msg_pub.add;
2394 x_return_status := fnd_api.g_ret_sts_unexp_error;
2395 END IF;
2396
2397 CLOSE c_timezone_id;
2398 ELSIF p_timezone_name IS NOT NULL
2399 THEN
2400 OPEN c_timezone_name;
2401 FETCH c_timezone_name INTO x_timezone_id;
2402
2403 IF c_timezone_name%NOTFOUND
2404 THEN
2405 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_TIMEZONE_NAME');
2406 fnd_message.set_token ('P_TIMEZONE_NAME', p_timezone_name);
2407 fnd_msg_pub.add;
2408 x_return_status := fnd_api.g_ret_sts_unexp_error;
2409 END IF;
2410
2411 CLOSE c_timezone_name;
2412 END IF;
2413 EXCEPTION
2414 WHEN OTHERS
2415 THEN
2416 x_timezone_id := NULL;
2417 x_return_status := fnd_api.g_ret_sts_unexp_error;
2418 END;
2419
2420 -----------------------------------------------------------
2421 -----------------------------------------------------------
2422 PROCEDURE validate_parent_task_id (
2423 p_parent_task_id IN NUMBER,
2424 p_source_object_code IN VARCHAR2,
2425 p_source_object_id IN NUMBER,
2426 x_return_status OUT NOCOPY VARCHAR2
2427 )
2428 IS
2429 l_p_source_object_code jtf_tasks_b.source_object_type_code%TYPE;
2430 l_source_object_code jtf_tasks_b.source_object_type_code%TYPE
2431 := p_source_object_code;
2432 l_parent_task_id jtf_tasks_b.task_id%TYPE
2433 := p_parent_task_id;
2434 l_source_object_id jtf_tasks_b.source_object_id%TYPE
2435 := p_source_object_id;
2436 l_p_source_object_id jtf_tasks_b.source_object_id%TYPE;
2437 BEGIN
2438 x_return_status := fnd_api.g_ret_sts_success;
2439 SELECT source_object_type_code, source_object_id
2440 INTO l_p_source_object_code, l_p_source_object_id
2441 FROM jtf_tasks_b
2442 WHERE task_id = p_parent_task_id;
2443
2444 IF ( l_p_source_object_code IS NOT NULL
2445 AND l_source_object_code IS NULL)
2446 OR ( l_p_source_object_code IS NULL
2447 AND l_source_object_code IS NOT NULL)
2448 THEN
2449 fnd_message.set_name ('JTF', 'JTF_TASK_PARENT_TYPE_CODE');
2450 fnd_msg_pub.add;
2451 x_return_status := fnd_api.g_ret_sts_unexp_error;
2452 END IF;
2453
2454 IF ( l_p_source_object_code IS NOT NULL
2455 AND l_source_object_code IS NOT NULL)
2456 THEN
2457 IF l_p_source_object_code <> l_source_object_code
2458 THEN
2459 fnd_message.set_name ('JTF', 'JTF_TASK_PARENT_TYPE_CODE');
2460 fnd_msg_pub.add;
2461 x_return_status := fnd_api.g_ret_sts_unexp_error;
2462 END IF;
2463 END IF;
2464
2465 SELECT source_object_id
2466 INTO l_p_source_object_id
2467 FROM jtf_tasks_b
2468 WHERE task_id = p_parent_task_id;
2469
2470 IF ( l_p_source_object_id IS NOT NULL
2471 AND l_source_object_id IS NULL)
2472 OR ( l_p_source_object_id IS NULL
2473 AND l_source_object_id IS NOT NULL)
2474 THEN
2475 fnd_message.set_name ('JTF', 'JTF_TASK_PARENT');
2476 fnd_msg_pub.add;
2477 x_return_status := fnd_api.g_ret_sts_unexp_error;
2478 END IF;
2479
2480 IF ( l_p_source_object_id IS NOT NULL
2481 AND l_source_object_id IS NOT NULL)
2482 THEN
2483 IF l_p_source_object_id <> l_source_object_id
2484 THEN
2485 fnd_message.set_name ('JTF', 'JTF_TASK_PARENT');
2486 fnd_msg_pub.add;
2487 x_return_status := fnd_api.g_ret_sts_unexp_error;
2488 END IF;
2489 END IF;
2490 EXCEPTION
2491 WHEN fnd_api.g_exc_unexpected_error
2492 THEN
2493 x_return_status := fnd_api.g_ret_sts_unexp_error;
2494 WHEN OTHERS
2495 THEN
2496 x_return_status := fnd_api.g_ret_sts_unexp_error;
2497 END;
2498
2499 -----------------------------------------------------------
2500 -----------------------------------------------------------
2501 PROCEDURE validate_notification (
2502 p_notification_flag IN VARCHAR2,
2503 p_notification_period IN NUMBER,
2504 p_notification_period_uom IN VARCHAR2,
2505 x_return_status OUT NOCOPY VARCHAR2
2506 )
2507 IS
2508 l_notification_flag jtf_tasks_b.notification_flag%TYPE
2509 := p_notification_flag;
2510 l_period jtf_tasks_b.notification_period%TYPE
2511 := p_notification_period;
2512 l_period_uom jtf_tasks_b.notification_period_uom%TYPE
2513 := p_notification_period_uom;
2514 BEGIN
2515 x_return_status := fnd_api.g_ret_sts_success;
2516 jtf_task_utl.validate_flag (
2517 x_return_status => x_return_status,
2518 p_flag_name => 'Notification
2519 Flag',
2520 p_flag_value => p_notification_flag
2521 );
2522
2523 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
2524 THEN
2525 RAISE fnd_api.g_exc_unexpected_error;
2526 END IF;
2527
2528 ------- Commented out (Bug 2118464) -----------------
2529 --IF ( l_period IS NOT NULL
2530 -- AND l_period_uom IS NULL)
2531 -- OR ( l_period IS NOT NULL
2532 -- AND l_period_uom IS NULL)
2533 --THEN
2534 -- fnd_message.set_name ('JTF', 'INVALID_EFFORT');
2535 -- fnd_msg_pub.add;
2536 -- x_return_status := fnd_api.g_ret_sts_unexp_error;
2537 --END IF;
2538 -----------------------------------------------------
2539
2540 --------- Fixed bug 2137765 --------------------------
2541 --jtf_task_utl.validate_effort (
2542 -- x_return_status => x_return_status,
2543 -- p_effort => l_period,
2544 -- p_effort_uom => l_period_uom
2545 --);
2546 jtf_task_utl.validate_effort (
2547 p_tag => jtf_task_utl.get_translated_lookup (
2548 'JTF_TASK_TRANSLATED_MESSAGES',
2549 'NOTIFICATION_PERIOD'
2550 ),
2551 p_tag_uom => jtf_task_utl.get_translated_lookup (
2552 'JTF_TASK_TRANSLATED_MESSAGES',
2553 'NOTIFICATION_PERIOD_UOM'
2554 ),
2555 x_return_status => x_return_status,
2556 p_effort => l_period,
2557 p_effort_uom => l_period_uom
2558 );
2559 -----------------------------------------------------
2560 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
2561 THEN
2562 RAISE fnd_api.g_exc_unexpected_error;
2563 END IF;
2564 EXCEPTION
2565 WHEN fnd_api.g_exc_unexpected_error
2566 THEN
2567 x_return_status := fnd_api.g_ret_sts_unexp_error;
2568 WHEN OTHERS
2569 THEN
2570 x_return_status := fnd_api.g_ret_sts_unexp_error;
2571 END;
2572
2573 -----------------------------------------------------------
2574 -----------------------------------------------------------
2575 -----------------------------------------------------------
2576 -----------------------------------------------------------
2577 PROCEDURE validate_alarm (
2578 p_alarm_start IN NUMBER,
2579 p_alarm_start_uom IN VARCHAR2,
2580 p_alarm_on IN VARCHAR2,
2581 p_alarm_count IN NUMBER,
2582 p_alarm_interval IN NUMBER,
2583 p_alarm_interval_uom IN VARCHAR2,
2584 x_return_status OUT NOCOPY VARCHAR2
2585 )
2586 IS
2587 is_null BOOLEAN := FALSE;
2588 is_not_null BOOLEAN := FALSE;
2589 l_alarm_start jtf_tasks_b.alarm_start%TYPE
2590 := p_alarm_start;
2591 l_alarm_start_uom jtf_tasks_b.alarm_start_uom%TYPE
2592 := p_alarm_start_uom;
2593 l_alarm_on jtf_tasks_b.alarm_on%TYPE
2594 := p_alarm_on;
2595 l_alarm_count jtf_tasks_b.alarm_count%TYPE
2596 := p_alarm_count;
2597 l_alarm_interval jtf_tasks_b.alarm_interval%TYPE
2598 := p_alarm_interval;
2599 l_alarm_interval_uom jtf_tasks_b.alarm_interval_uom%TYPE
2600 := p_alarm_interval_uom;
2601 ----
2602 ---- This api validates the alarm parameters for the task.
2603 ----
2604 ----
2605
2606 BEGIN
2607 x_return_status := fnd_api.g_ret_sts_success;
2608 jtf_task_utl.validate_flag (
2609 x_return_status => x_return_status,
2610 p_flag_name => 'Alarm Flag',
2611 p_flag_value => l_alarm_on
2612 );
2613
2614 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
2615 THEN
2616 RAISE fnd_api.g_exc_unexpected_error;
2617 END IF;
2618
2619 IF jtf_task_utl.to_boolean (p_alarm_on)
2620 THEN
2621 IF (l_alarm_start IS NULL)
2622 OR (l_alarm_start_uom IS NULL)
2623 OR (l_alarm_count IS NULL)
2624 OR (l_alarm_interval IS NULL)
2625 OR (l_alarm_interval_uom IS NULL)
2626 THEN
2627 fnd_message.set_name ('JTF', 'INVALID_ALARM_PARAMETERS');
2628 fnd_msg_pub.add;
2629 x_return_status := fnd_api.g_ret_sts_unexp_error;
2630 RAISE fnd_api.g_exc_unexpected_error;
2631 END IF;
2632
2633 jtf_task_utl.validate_effort (
2634 x_return_status => x_return_status,
2635 p_effort => l_alarm_start,
2636 p_effort_uom => l_alarm_start_uom
2637 );
2638
2639 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
2640 THEN
2641 -- fnd_message.set_name ('JTF', 'Invalid_ALARM');
2642 -- fnd_msg_pub.add;
2643 x_return_status := fnd_api.g_ret_sts_unexp_error;
2644 RAISE fnd_api.g_exc_unexpected_error;
2645 END IF;
2646
2647 /*** Fixed Bug 2118582 ***********/
2648 --IF is_not_null
2649 --THEN
2650 jtf_task_utl.validate_effort (
2651 x_return_status => x_return_status,
2652 p_effort => l_alarm_interval,
2653 p_effort_uom => l_alarm_interval_uom
2654 );
2655
2656 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
2657 THEN
2658 -- fnd_message.set_name ('JTF', 'Invalid_ALARM');
2659 -- fnd_msg_pub.add;
2660 x_return_status := fnd_api.g_ret_sts_unexp_error;
2661 RAISE fnd_api.g_exc_unexpected_error;
2662 END IF;
2663 --END IF;
2664 ELSE
2665 IF (l_alarm_start IS NOT NULL)
2666 OR (l_alarm_start_uom IS NOT NULL)
2667 OR (l_alarm_count IS NOT NULL)
2668 OR (l_alarm_interval IS NOT NULL)
2669 OR (l_alarm_interval_uom IS NOT NULL)
2670 THEN
2671 fnd_message.set_name ('JTF', 'INVALID_ALARM_PARAMETERS');
2672 fnd_msg_pub.add;
2673 x_return_status := fnd_api.g_ret_sts_unexp_error;
2674 END IF;
2675 END IF;
2676 END;
2677
2678 -----------------------------------------------------------
2679 -----------------------------------------------------------
2680 FUNCTION g_miss_char
2681 RETURN VARCHAR2
2682 IS
2683 BEGIN
2684 RETURN fnd_api.g_miss_char;
2685 END;
2686
2687 FUNCTION g_miss_date
2688 RETURN DATE
2689 IS
2690 BEGIN
2691 RETURN fnd_api.g_miss_date;
2692 END;
2693
2694 FUNCTION g_miss_number
2695 RETURN NUMBER
2696 IS
2697 BEGIN
2698 RETURN fnd_api.g_miss_num;
2699 END;
2700
2701 FUNCTION get_translated_lookup (
2702 p_lookup_type IN VARCHAR2,
2703 p_lookup_code IN VARCHAR2
2704 )
2705 RETURN VARCHAR2
2706 IS
2707 l_description fnd_lookups.description%TYPE;
2708 BEGIN
2709 SELECT description
2710 INTO l_description
2711 FROM fnd_lookups
2712 WHERE lookup_type = p_lookup_type
2713 AND lookup_code = p_lookup_code;
2714 RETURN l_description;
2715 EXCEPTION
2716 WHEN NO_DATA_FOUND
2717 THEN
2718 RETURN NULL;
2719 END;
2720
2721 -----------------------------
2722 -----------------------------
2723 -----------------------------
2724 PROCEDURE privelege_all_tasks (
2725 p_profile_name IN VARCHAR2,
2726 x_privelege_all_tasks OUT NOCOPY VARCHAR2,
2727 x_return_status OUT NOCOPY VARCHAR2
2728 )
2729 IS
2730 privelege_all_tasks VARCHAR2(1);
2731 l_resource_id NUMBER;
2732 BEGIN
2733 x_return_status := fnd_api.g_ret_sts_success;
2734 fnd_profile.get (p_profile_name, privelege_all_tasks);
2735
2736 IF privelege_all_tasks = 'Y'
2737 OR privelege_all_tasks IS NULL
2738 THEN
2739 RETURN;
2740 END IF;
2741
2742 SELECT resource_id
2743 INTO l_resource_id
2744 FROM jtf_rs_resource_extns
2745 WHERE user_id = fnd_global.user_id;
2746 EXCEPTION
2747 WHEN NO_DATA_FOUND
2748 THEN
2749 x_return_status := fnd_api.g_ret_sts_unexp_error;
2750 fnd_message.set_name ('JTF', 'JTF_TASK_MISSING_USER_MAPPING');
2751 fnd_msg_pub.add;
2752 WHEN OTHERS
2753 THEN
2754 x_return_status := fnd_api.g_ret_sts_unexp_error;
2755 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
2756 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
2757 fnd_msg_pub.add;
2758 RAISE fnd_api.g_exc_unexpected_error;
2759 END;
2760
2761 -----------------------------
2762 -----------------------------
2763 -----------------------------
2764 PROCEDURE get_default_owner (
2765 x_owner_type_code OUT NOCOPY VARCHAR2,
2766 x_owner_id OUT NOCOPY NUMBER,
2767 x_owner_type_code_name OUT NOCOPY VARCHAR2,
2768 x_owner_name OUT NOCOPY VARCHAR2,
2769 x_return_status OUT NOCOPY VARCHAR2
2770 )
2771 AS
2772 profile_set BOOLEAN := TRUE;
2773 BEGIN
2774 x_return_status := fnd_api.g_ret_sts_success;
2775
2776 BEGIN
2777 x_owner_type_code :=
2778 fnd_profile.VALUE ('JTF_TASK_DEFAULT_OWNER_TYPE');
2779 x_owner_id := fnd_profile.VALUE ('JTF_TASK_DEFAULT_OWNER');
2780 EXCEPTION
2781 WHEN NO_DATA_FOUND
2782 THEN
2783 profile_set := FALSE;
2784 END;
2785
2786 IF ( x_owner_type_code IS NULL
2787 AND x_owner_id IS NOT NULL)
2788 OR ( x_owner_id IS NULL
2789 AND x_owner_type_code IS NOT NULL)
2790 THEN
2791 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_DEFAULT_OWNER');
2792 fnd_msg_pub.add;
2793 x_return_status := fnd_api.g_ret_sts_unexp_error;
2794 profile_set := FALSE;
2795 RETURN;
2796 END IF;
2797
2798 IF profile_set
2799 THEN
2800 BEGIN
2801 x_owner_name :=
2802 jtf_task_utl.get_owner (x_owner_type_code, x_owner_id);
2803 EXCEPTION
2804 WHEN NO_DATA_FOUND
2805 THEN
2806 profile_set := FALSE;
2807 END;
2808 END IF;
2809
2810 IF (x_owner_name IS NOT NULL)
2811 THEN
2812 SELECT name
2813 INTO x_owner_type_code_name
2814 FROM jtf_objects_vl
2815 WHERE object_code = x_owner_type_code;
2816 RETURN;
2817 ELSE
2818 BEGIN
2819 SELECT resource_id,
2820 DECODE (
2821 category,
2822 'EMPLOYEE', 'RS_EMPLOYEE',
2823 'PARTNER', 'RS_PARTNER',
2824 'PARTY', 'RS_PARTY',
2825 'OTHER', 'RS_OTHER',
2826 'SUPPLIER_CONTACT', 'RS_SUPPLIER_CONTACT'
2827 )
2828 INTO x_owner_id,
2829 x_owner_type_code
2830 FROM jtf_rs_resource_extns
2831 WHERE user_id = fnd_global.user_id;
2832 SELECT name
2833 INTO x_owner_type_code_name
2834 FROM jtf_objects_vl
2835 WHERE object_code = x_owner_type_code;
2836 x_owner_name :=
2837 jtf_task_utl.get_owner (x_owner_type_code, x_owner_id);
2838 EXCEPTION
2839 WHEN NO_DATA_FOUND
2840 THEN
2841 NULL;
2842 END;
2843 END IF;
2844 END;
2845
2846 -----------------------------
2847 --- USAGE
2848 --- 1. Used in the task main form.
2849 ---
2850 ---
2851 -----------------------------
2852 FUNCTION get_uom_time_class
2853 RETURN VARCHAR2
2854 IS
2855 BEGIN
2856 RETURN jtf_task_utl.g_uom_time_class;
2857 END;
2858
2859 -----------------------------
2860 ----------------------------
2861 --- USAGE
2862 --- 1. JTF_TASK_UWQ_V procedure to get if the task is closed or open.
2863 ---
2864 ---
2865 -----------------------------
2866 -----------------------------
2867 FUNCTION get_customer_name (p_customer_id IN NUMBER)
2868 RETURN VARCHAR2
2869 AS
2870 l_customer_name VARCHAR2(120);
2871 BEGIN
2872 IF p_customer_id IS NULL
2873 THEN
2874 RETURN NULL;
2875 ELSE
2876 SELECT party_name
2877 INTO l_customer_name
2878 FROM hz_parties
2879 WHERE party_id = p_customer_id;
2880 END IF;
2881
2882 RETURN l_customer_name;
2883 EXCEPTION
2884 WHEN NO_DATA_FOUND
2885 THEN
2886 RETURN NULL;
2887 WHEN OTHERS
2888 THEN
2889 RETURN NULL;
2890 END;
2891
2892 -----------------------------
2893 --------------------------
2894 --- USAGE
2895 --- 1. JTF_TASK_UWQ_V procedure to get if the task is closed or open.
2896 ---
2897 ---
2898 -----------------------------
2899 -----------------------------
2900 FUNCTION is_task_closed (p_task_status_id IN NUMBER)
2901 RETURN VARCHAR2
2902 IS
2903 l_closed_flag VARCHAR2(1);
2904 l_completed_flag VARCHAR2(1);
2905 l_cancelled_flag VARCHAR2(1);
2906 l_rejected_flag VARCHAR2(1);
2907 BEGIN
2908 --- Added to check for rejected flag on 6th October.
2909 SELECT closed_flag, completed_flag, cancelled_flag,
2910 rejected_flag
2911 INTO l_closed_flag, l_completed_flag, l_cancelled_flag,
2912 l_rejected_flag
2913 FROM jtf_task_statuses_vl
2914 WHERE task_status_id = p_task_status_id;
2915
2916 --- Added to check for rejected flag on 6th October.
2917 IF ( l_closed_flag IS NULL
2918 OR l_closed_flag = 'N')
2919 AND ( l_completed_flag IS NULL
2920 OR l_completed_flag = 'N')
2921 AND ( l_cancelled_flag IS NULL
2922 OR l_cancelled_flag = 'N')
2923 AND ( l_rejected_flag IS NULL
2924 OR l_rejected_flag = 'N')
2925 THEN
2926 RETURN 'N';
2927 ELSE
2928 RETURN 'Y';
2929 END IF;
2930 EXCEPTION
2931 WHEN OTHERS
2932 THEN
2933 RETURN 'Y';
2934 END;
2935
2936 -----------------------------
2937 -----------------------------
2938 -----------------------------
2939 PROCEDURE validate_missing_task_id (
2940 p_task_id IN NUMBER,
2941 x_return_status OUT NOCOPY VARCHAR2
2942 )
2943 IS
2944 BEGIN
2945 x_return_status := fnd_api.g_ret_sts_success;
2946
2947 IF p_task_id IS NULL
2948 THEN
2949 fnd_message.set_name ('JTF', 'JTF_TASK_MISSING_TASK');
2950 fnd_msg_pub.add;
2951 x_return_status := fnd_api.g_ret_sts_unexp_error;
2952 RAISE fnd_api.g_exc_unexpected_error;
2953 END IF;
2954 END;
2955
2956 -----------------------------
2957 -----------------------------
2958 -----------------------------
2959 PROCEDURE validate_missing_contact_id (
2960 p_task_contact_id IN NUMBER,
2961 x_return_status OUT NOCOPY VARCHAR2
2962 )
2963 IS
2964 BEGIN
2965 x_return_status := fnd_api.g_ret_sts_success;
2966
2967 IF p_task_contact_id IS NULL
2968 THEN
2969 fnd_message.set_name ('JTF', 'JTF_TASK_MISSING_CONTACT');
2970 fnd_msg_pub.add;
2971 x_return_status := fnd_api.g_ret_sts_unexp_error;
2972 RAISE fnd_api.g_exc_unexpected_error;
2973 END IF;
2974 END;
2975
2976 -----------------------------
2977 -----------------------------
2978 -----------------------------
2979 PROCEDURE validate_missing_phone_id (
2980 p_task_phone_id IN NUMBER,
2981 x_return_status OUT NOCOPY VARCHAR2
2982 )
2983 IS
2984 BEGIN
2985 x_return_status := fnd_api.g_ret_sts_success;
2986
2987 IF p_task_phone_id IS NULL
2988 THEN
2989 fnd_message.set_name ('JTF', 'JTF_TASK_MISSING_PHONE');
2990 fnd_msg_pub.add;
2991 x_return_status := fnd_api.g_ret_sts_unexp_error;
2992 RAISE fnd_api.g_exc_unexpected_error;
2993 END IF;
2994 END;
2995
2996 -----------------------------
2997 -----------------------------
2998 -----------------------------
2999 --- This is used to validate application id in task template groups.
3000 -----------------------------
3001 -----------------------------
3002 PROCEDURE validate_application_id (
3003 p_application_id IN NUMBER,
3004 x_return_status OUT NOCOPY VARCHAR2
3005 )
3006 IS
3007 l_application_id NUMBER;
3008 BEGIN
3009 x_return_status := fnd_api.g_ret_sts_success;
3010 SELECT application_id
3011 INTO l_application_id
3012 FROM fnd_application_vl
3013 WHERE application_id = p_application_id;
3014 EXCEPTION
3015 WHEN NO_DATA_FOUND
3016 THEN
3017 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_APPL_ID');
3018 fnd_message.set_token ('P_APPL_ID', p_application_id);
3019 fnd_msg_pub.add;
3020 x_return_status := fnd_api.g_ret_sts_unexp_error;
3021 WHEN TOO_MANY_ROWS
3022 THEN
3023 NULL;
3024 WHEN OTHERS
3025 THEN
3026 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
3027 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
3028 fnd_msg_pub.add;
3029 x_return_status := fnd_api.g_ret_sts_unexp_error;
3030 END;
3031
3032 -----------------------------
3033 -----------------------------
3034 -----------------------------
3035 --- This is used to get the user name for the JTF_TASKS_V
3036 -----------------------------
3037 -----------------------------
3038 FUNCTION get_user_name (p_user_id IN NUMBER)
3039 RETURN VARCHAR2
3040 IS
3041 l_user_name fnd_user.user_name%TYPE;
3042 BEGIN
3043 IF p_user_id IS NULL
3044 THEN
3045 RETURN NULL;
3046 ELSE
3047 SELECT user_name
3048 INTO l_user_name
3049 FROM fnd_user
3050 WHERE user_id = p_user_id;
3051 RETURN l_user_name;
3052 END IF;
3053 END;
3054
3055 -----
3056 -----
3057 -----
3058 FUNCTION get_parent_task_number (p_task_id IN NUMBER)
3059 RETURN VARCHAR2
3060 IS
3061 l_task_number VARCHAR2(30);
3062 BEGIN
3063 IF p_task_id IS NOT NULL
3064 THEN
3065 SELECT task_number
3066 INTO l_task_number
3067 FROM jtf_tasks_b task
3068 WHERE task_id = p_task_id ;
3069 END IF;
3070
3071 RETURN l_task_number;
3072 EXCEPTION
3073 WHEN NO_DATA_FOUND
3074 THEN
3075 RETURN l_task_number;
3076 END;
3077
3078 -----
3079 -----
3080 -----
3081 FUNCTION get_territory_name (p_terr_id IN NUMBER)
3082 RETURN VARCHAR2
3083 IS
3084 l_terr_name VARCHAR2(2000);
3085 BEGIN
3086 IF p_terr_id IS NOT NULL
3087 THEN
3088 SELECT name
3089 INTO l_terr_name
3090 FROM jtf_terr
3091 WHERE terr_id = p_terr_id;
3092 END IF;
3093
3094 RETURN l_terr_name;
3095 EXCEPTION
3096 WHEN NO_DATA_FOUND
3097 THEN
3098 RETURN l_terr_name;
3099 END;
3100
3101 -----
3102 -----
3103 -----
3104 PROCEDURE validate_phones_table (
3105 p_owner_table_name IN VARCHAR2,
3106 x_return_status OUT NOCOPY VARCHAR2
3107 )
3108 IS
3109 BEGIN
3110 x_return_status := fnd_api.g_ret_sts_success;
3111
3112 IF p_owner_table_name IS NOT NULL
3113 THEN
3114 IF p_owner_table_name NOT IN ('JTF_TASKS_B', 'JTF_TASK_CONTACTS')
3115 THEN
3116 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_PHONE_TABLE');
3117 fnd_msg_pub.add;
3118 x_return_status := fnd_api.g_ret_sts_unexp_error;
3119 END IF;
3120 END IF;
3121 END;
3122 -----
3123 -----
3124 PROCEDURE validate_category (
3125 p_category_id in number,
3126 x_return_status OUT NOCOPY varchar2)
3127
3128 IS
3129 l_category_id number;
3130 l_resource_id number;
3131 l_profile_name varchar2(30);
3132
3133
3134 BEGIN
3135 x_return_status := fnd_api.g_ret_sts_success;
3136 -- to verify user's category
3137 IF p_category_id is not null THEN
3138 SELECT resource_id
3139 INTO l_resource_id
3140 FROM jtf_rs_resource_extns
3141 WHERE user_id = fnd_global.user_id;
3142
3143 l_profile_name := TO_CHAR(l_resource_id)||g_perz_suffix;
3144
3145 SELECT perz_data_id into l_category_id
3146 FROM jtf_perz_data
3147 WHERE profile_id = (SELECT profile_id
3148 FROM jtf_perz_profile
3149 WHERE profile_name = l_profile_name)
3150 AND perz_data_id = p_category_id;
3151 END IF;
3152 EXCEPTION
3153 WHEN no_data_found THEN
3154 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_CATEGORY_ID');
3155 fnd_message.set_token ('P_CATEGORY_ID', p_category_id );
3156 fnd_msg_pub.add;
3157 x_return_status := fnd_api.g_ret_sts_unexp_error;
3158 END;
3159 -----
3160 -----
3161 /************************ Commented out and rewrote : Bug 2120569
3162 PROCEDURE check_security_privilege(
3163 p_task_id number,
3164 p_session varchar2,
3165 x_return_status OUT NOCOPY VARCHAR2
3166 )
3167 IS
3168
3169 l_rs_id number;
3170 l_rs_id_type varchar2(30);
3171 l_rs_type_code varchar2(30);
3172 l_resource_id number;
3173 l_resource_type_code varchar2(30);
3174 l_owner_id number;
3175 l_owner_type_code varchar2(30);
3176 l_privilege varchar2(30) default null;
3177 l_private_flag jtf_tasks_b.private_flag%TYPE default null;
3178
3179
3180 BEGIN
3181
3182 x_return_status := fnd_api.g_ret_sts_success;
3183 IF fnd_profile.value('JTF_TASK_CHECK_SECURITY') = jtf_task_utl.g_yes_char
3184 THEN
3185
3186 -- select the owner info from task table for this task id
3187 SELECT owner_id, owner_type_code, private_flag
3188 INTO l_owner_id, l_owner_type_code, l_private_flag
3189 FROM jtf_tasks_b
3190 WHERE task_id = p_task_id;
3191
3192
3193 -- user's id and type
3194 SELECT resource_id
3195 INTO l_rs_id
3196 FROM jtf_rs_resource_extns
3197 WHERE user_id = fnd_global.user_id;
3198
3199
3200 IF p_session = 'UPDATE'
3201 THEN
3202 BEGIN
3203 -- check when resource_type_code is a group
3204 SELECT group_id
3205 INTO l_resource_id
3206 FROM jtf_rs_group_members
3207 WHERE resource_id = l_rs_id
3208 AND group_id IN (SELECT resource_id
3209 FROM jtf_task_all_assignments
3210 WHERE task_id = p_task_id
3211 AND resource_type_code = 'RS_GROUP');
3212 EXCEPTION
3213 WHEN no_data_found THEN
3214 BEGIN
3215 SELECT team_id
3216 INTO l_resource_id
3217 FROM jtf_rs_team_members
3218 WHERE team_resource_id = l_rs_id
3219 AND team_id IN (SELECT resource_id
3220 FROM jtf_task_all_assignments
3221 WHERE task_id = p_task_id
3222 AND resource_type_code = 'RS_TEAM');
3223 EXCEPTION
3224 when no_data_found then
3225 -- compare user's id with the id list on assignment
3226 SELECT resource_id
3227 INTO l_resource_id
3228 FROM jtf_task_all_assignments
3229 WHERE task_id = p_task_id
3230 AND resource_id = l_rs_id;
3231
3232
3233 IF SQL%FOUND THEN
3234 x_return_status := fnd_api.g_ret_sts_success;
3235 ELSE
3236 -- check to see if the user has the privilege when private flag is set to 'N'
3237 IF l_private_flag = jtf_task_utl.g_no_char or l_private_flag is null
3238 THEN
3239 SELECT function_name into l_privilege
3240 FROM fnd_form_functions fff, fnd_menu_entries fme
3241 WHERE fff.function_id = fme.function_id
3242 AND fme.menu_id = (select fm.menu_id from fnd_menus fm, fnd_grants fg
3243 where fm.menu_id = fg.menu_id
3244 and fg.instance_pk1_value = l_owner_id
3245 and fg.instance_pk2_value = l_owner_type_code
3246 and fg.grantee_key = TO_CHAR(l_rs_id)
3247 and fg.object_id = (select object_id from fnd_objects
3248 where obj_name = 'JTF_TASK_RESOURCE'
3249 )
3250 )
3251 AND function_name = jtf_task_utl.g_tasks_full_privelege;
3252
3253 IF SQL%FOUND THEN
3254 x_return_status := fnd_api.g_ret_sts_success;
3255 ELSE
3256 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_USER_ID');
3257 fnd_message.set_token ('P_USER_ID', l_rs_id );
3258 fnd_msg_pub.add;
3259 x_return_status := fnd_api.g_ret_sts_unexp_error;
3260 END IF;
3261 END IF;
3262 END IF;
3263 END;
3264 END;
3265 END IF;
3266 End IF;
3267 EXCEPTION
3268
3269 WHEN OTHERS
3270 THEN
3271
3272 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_SECURITY_CHK');
3273 fnd_message.set_token ('P_USER_ID', l_rs_id );
3274 fnd_msg_pub.add;
3275 x_return_status := fnd_api.g_ret_sts_unexp_error;
3276
3277 END;
3278 ************************************************************************************/
3279 /******** Rewote : Bug 2120569 **********/
3280 PROCEDURE check_security_privilege(
3281 p_task_id NUMBER,
3282 p_session VARCHAR2,
3283 x_return_status OUT NOCOPY VARCHAR2)
3284 IS
3285 l_user_id NUMBER := fnd_global.user_id;
3286
3287 -- select the owner info from task table for this task id
3288 CURSOR c_task_owner IS
3289 SELECT owner_id, owner_type_code, private_flag
3290 FROM jtf_tasks_b
3291 WHERE task_id = p_task_id;
3292
3293 rec_task_owner c_task_owner%ROWTYPE;
3294
3295 -- user's id and type
3296 CURSOR c_resource IS
3297 SELECT resource_id
3298 FROM jtf_rs_resource_extns
3299 WHERE user_id = l_user_id;
3300
3301 rec_resource c_resource%ROWTYPE;
3302
3303 CURSOR c_group (pc_rs_id NUMBER) IS
3304 SELECT group_id
3305 FROM jtf_rs_group_members
3306 WHERE resource_id = pc_rs_id
3307 AND group_id IN (SELECT resource_id
3308 FROM jtf_task_all_assignments
3309 WHERE task_id = p_task_id
3310 AND resource_type_code = 'RS_GROUP');
3311
3312 rec_group c_group%ROWTYPE;
3313
3314 CURSOR c_team (pc_rs_id NUMBER) IS
3315 SELECT team_id
3316 FROM jtf_rs_team_members
3317 WHERE team_resource_id = pc_rs_id
3318 AND team_id IN (SELECT resource_id
3319 FROM jtf_task_all_assignments
3320 WHERE task_id = p_task_id
3321 AND resource_type_code = 'RS_TEAM');
3322
3323 rec_team c_team%ROWTYPE;
3324
3325 CURSOR c_assignee_or_owner (pc_rs_id NUMBER) IS
3326 SELECT resource_id
3327 FROM jtf_task_all_assignments
3328 WHERE task_id = p_task_id
3329 AND resource_id = pc_rs_id;
3330
3331 rec_assignee_or_owner c_assignee_or_owner%ROWTYPE;
3332
3333 CURSOR c_grant (pc_pk1_value VARCHAR2, pc_pk2_value VARCHAR2, pc_grantee_key VARCHAR2) IS
3334 SELECT function_name
3335 FROM fnd_form_functions fff
3336 , fnd_menu_entries fme
3337 WHERE fff.function_id = fme.function_id
3338 AND fme.menu_id = (SELECT fm.menu_id
3339 FROM fnd_menus fm
3340 , fnd_grants fg
3341 WHERE fm.menu_id = fg.menu_id
3342 AND fg.instance_pk1_value = pc_pk1_value
3343 AND fg.instance_pk2_value = pc_pk2_value
3344 AND fg.grantee_key = pc_grantee_key
3345 AND fg.object_id =
3346 (SELECT object_id
3347 FROM fnd_objects
3348 WHERE obj_name = 'JTF_TASK_RESOURCE'
3349 )
3350 )
3351 AND function_name = jtf_task_utl.g_tasks_full_privelege;
3352
3353 rec_grant c_grant%ROWTYPE;
3354
3355 BEGIN
3356 x_return_status := fnd_api.g_ret_sts_success;
3357
3358 IF fnd_profile.value('JTF_TASK_CHECK_SECURITY') = jtf_task_utl.g_yes_char
3359 THEN
3360 OPEN c_task_owner;
3361 FETCH c_task_owner INTO rec_task_owner;
3362 IF c_task_owner%NOTFOUND
3363 THEN
3364 fnd_message.set_name('JTF', 'JTF_TASK_INVALID_TASK_ID');
3365 fnd_message.set_token('P_TASK_ID', p_task_id);
3366 fnd_msg_pub.add;
3367 x_return_status := fnd_api.g_ret_sts_error;
3368 RAISE fnd_api.g_exc_error;
3369 END IF;
3370 CLOSE c_task_owner;
3371
3372 OPEN c_resource;
3373 FETCH c_resource INTO rec_resource;
3374 IF c_resource%NOTFOUND
3375 THEN
3376 fnd_message.set_name('JTF', 'JTF_TASK_MISSING_USER_MAPPING');
3377 fnd_message.set_token('P_USER_ID', l_user_id);
3378 fnd_msg_pub.add;
3379 x_return_status := fnd_api.g_ret_sts_error;
3380 RAISE fnd_api.g_exc_error;
3381 END IF;
3382 CLOSE c_resource;
3383
3384 IF p_session = 'UPDATE'
3385 THEN
3386 -- Check group
3387 OPEN c_group (rec_resource.resource_id);
3388 FETCH c_group INTO rec_group;
3389 IF c_group%NOTFOUND
3390 THEN
3391 CLOSE c_group;
3392 -- Check team
3393 OPEN c_team (rec_resource.resource_id);
3394 FETCH c_team INTO rec_team;
3395 IF c_team%NOTFOUND
3396 THEN
3397 CLOSE c_team;
3398 --Check Assignment
3399 OPEN c_assignee_or_owner (rec_resource.resource_id);
3400 FETCH c_assignee_or_owner INTO rec_assignee_or_owner;
3401 IF c_assignee_or_owner%NOTFOUND
3402 THEN
3403 -- check to see if the user has the privilege when private flag is set to 'N'
3404 IF rec_task_owner.private_flag = jtf_task_utl.g_no_char or
3405 rec_task_owner.private_flag is null
3406 THEN
3407 OPEN c_grant (rec_task_owner.owner_id,
3408 rec_task_owner.owner_type_code,
3409 rec_resource.resource_id);
3410 FETCH c_grant INTO rec_grant;
3411 IF c_grant%NOTFOUND
3412 THEN
3413 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_USER_ID');
3414 fnd_message.set_token ('P_USER_ID', rec_resource.resource_id);
3415 fnd_msg_pub.add;
3416 x_return_status := fnd_api.g_ret_sts_error;
3417 -- Don't raise any exception.
3418 -- This is not an exception, but insufficient privilege error.
3419 ELSE
3420 CLOSE c_grant;
3421 END IF; -- Check grant
3422 ELSE
3423 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_USER_ID');
3424 fnd_message.set_token ('P_USER_ID', rec_resource.resource_id);
3425 fnd_msg_pub.add;
3426 x_return_status := fnd_api.g_ret_sts_error;
3427 -- Don't raise any exception.
3428 -- This is not an exception, but insufficient privilege error.
3429 END IF;
3430 ELSE
3431 CLOSE c_assignee_or_owner;
3432 END IF; -- Check assignee_or_owner
3433 ELSE
3434 CLOSE c_team;
3435 END IF; -- Check team
3436 ELSE
3437 CLOSE c_group;
3438 END IF; -- Check group
3439 END IF; -- p_session = UPDATE
3440 End IF; -- fnd_profile.value('JTF_TASK_CHECK_SECURITY') = Yes
3441 EXCEPTION
3442 WHEN fnd_api.g_exc_error
3443 THEN
3444 IF c_task_owner%ISOPEN THEN
3445 CLOSE c_task_owner;
3446 END IF;
3447 IF c_resource%ISOPEN THEN
3448 CLOSE c_resource;
3449 END IF;
3450 IF c_group%ISOPEN THEN
3451 CLOSE c_group;
3452 END IF;
3453 IF c_team%ISOPEN THEN
3454 CLOSE c_team;
3455 END IF;
3456 IF c_assignee_or_owner%ISOPEN THEN
3457 CLOSE c_assignee_or_owner;
3458 END IF;
3459 IF c_grant%ISOPEN THEN
3460 CLOSE c_grant;
3461 END IF;
3462
3463 WHEN OTHERS
3464 THEN
3465 IF c_task_owner%ISOPEN THEN
3466 CLOSE c_task_owner;
3467 END IF;
3468 IF c_resource%ISOPEN THEN
3469 CLOSE c_resource;
3470 END IF;
3471 IF c_group%ISOPEN THEN
3472 CLOSE c_group;
3473 END IF;
3474 IF c_team%ISOPEN THEN
3475 CLOSE c_team;
3476 END IF;
3477 IF c_assignee_or_owner%ISOPEN THEN
3478 CLOSE c_assignee_or_owner;
3479 END IF;
3480 IF c_grant%ISOPEN THEN
3481 CLOSE c_grant;
3482 END IF;
3483 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_SECURITY_CHK');
3484 fnd_message.set_token ('P_TEXT', SQLCODE ||' '|| SQLERRM);
3485 fnd_msg_pub.add;
3486 x_return_status := fnd_api.g_ret_sts_unexp_error;
3487 RAISE;
3488 END;
3489
3490 -----
3491 -----
3492 FUNCTION g_no_char
3493 RETURN VARCHAR2
3494 IS
3495 BEGIN
3496 RETURN 'N';
3497 END;
3498 -----
3499 -----
3500 FUNCTION g_yes_char
3501 RETURN VARCHAR2
3502 IS
3503 BEGIN
3504 RETURN 'Y';
3505 END;
3506
3507 -----
3508 -----
3509 FUNCTION g_false_char
3510 RETURN VARCHAR2
3511 IS
3512 BEGIN
3513 RETURN 'F';
3514 END;
3515 -----
3516 -----
3517 FUNCTION g_true_char
3518 RETURN VARCHAR2
3519 IS
3520 BEGIN
3521 RETURN 'T';
3522 END;
3523
3524 PROCEDURE validate_party_site_acct (
3525 p_party_id number,
3526 p_party_site_id number,
3527 p_cust_account_id number,
3528 x_return_status OUT NOCOPY varchar2)
3529 IS
3530
3531 CURSOR c_party_site(b_site_id number,
3532 b_party_id number)
3533 IS
3534 SELECT 'found'
3535 FROM hz_party_sites
3536 WHERE party_site_id = b_site_id
3537 AND party_id = b_party_id;
3538
3539 CURSOR c_party_acct(b_acct_id number,
3540 b_party_id number)
3541 IS
3542 SELECT 'found'
3543 FROM hz_cust_accounts
3544 WHERE cust_account_id = b_acct_id
3545 AND party_id = b_party_id;
3546
3547 x varchar2(10);
3548
3549 BEGIN
3550 x_return_status := fnd_api.g_ret_sts_success;
3551
3552 IF p_party_id IS NULL
3553 AND (p_party_site_id IS NOT NULL
3554 OR p_cust_account_id IS NOT NULL) THEN
3555 -- cannot validate if there is no customer
3556 -- return a warning message
3557 fnd_message.set_name ('JTF', 'JTF_TASK_NO_PARTY');
3558 fnd_msg_pub.add;
3559 RETURN;
3560 END IF;
3561
3562 IF p_party_site_id IS NOT NULL
3563 THEN
3564 OPEN c_party_site(p_party_site_id,
3565 p_party_id);
3566 FETCH c_party_site into x;
3567
3568 IF c_party_site%NOTFOUND
3569 THEN
3570 CLOSE c_party_site;
3571 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_SITE_ID');
3572 fnd_message.set_token ('P_PARTY_SITE_ID', p_party_site_id);
3573 fnd_msg_pub.add;
3574 x_return_status := fnd_api.g_ret_sts_unexp_error;
3575 RAISE fnd_api.g_exc_unexpected_error;
3576 END IF;
3577
3578 CLOSE c_party_site;
3579 END IF;
3580
3581 IF p_cust_account_id IS NOT NULL
3582 THEN
3583 OPEN c_party_acct(p_cust_account_id,
3584 p_party_id);
3585 FETCH c_party_acct into x;
3586
3587 IF c_party_acct%NOTFOUND
3588 THEN
3589 CLOSE c_party_acct;
3590 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_CUST_ACCT_ID');
3591 fnd_message.set_token ('P_CUST_ACCOUNT_ID', p_cust_account_id);
3592 fnd_msg_pub.add;
3593 x_return_status := fnd_api.g_ret_sts_unexp_error;
3594 RAISE fnd_api.g_exc_unexpected_error;
3595 END IF;
3596
3597 CLOSE c_party_acct;
3598 END IF;
3599 END;
3600
3601
3602 FUNCTION GET_CATEGORY_NAME ( p_category_id in number )
3603 RETURN varchar2 IS
3604
3605 l_msg_data varchar2(240); -- Fix bug 2540772
3606
3607 BEGIN
3608 if p_category_id is null then
3609 fnd_msg_pub.initialize ;
3610 fnd_message.set_name('JTF','JTF_CAL_COMMON_CATEGORY');
3611 fnd_msg_pub.add ;
3612 l_msg_data := substrb(fnd_msg_pub.get( 1 , 'F' ),1,240); -- Fixed for bug 2540722
3613 else
3614 select perz_data_desc
3615 into l_msg_data
3616 from jtf_perz_data
3617 where perz_data_id = p_category_id ;
3618 end if;
3619
3620 return l_msg_data;
3621 EXCEPTION
3622 when no_data_found then
3623 return null ;
3624 END;
3625
3626
3627
3628 FUNCTION GET_CATEGORY_NAME_FOR_TASK ( p_task_id in number,
3629 p_resource_id in number,
3630 p_resource_type_code in varchar2 )
3631 RETURN varchar2 IS
3632 l_category_id number ;
3633 l_category_name varchar2(240); -- Fix bug 2540772
3634 BEGIN
3635 /* Make sure that only one row is returned */
3636 /* if the same resource is assigned to the task*/
3637 select category_id
3638 into l_category_id
3639 from jtf_task_all_assignments
3640 where task_id = p_task_id
3641 and resource_id = p_resource_id
3642 and resource_type_code = p_resource_type_code
3643 and rownum < 2 ;
3644
3645 -- For fix bug 2540722
3646 -- If l_category_id is null, return the translated message for "Unfiled"
3647 l_category_name := jtf_task_utl.get_category_name(l_category_id);
3648
3649 return l_category_name;
3650
3651 EXCEPTION
3652 when no_data_found then
3653 l_category_name := jtf_task_utl.get_category_name(null);
3654 return l_category_name;
3655 END;
3656
3657
3658 procedure delete_category( p_category_name in varchar2 )
3659 as
3660 begin
3661
3662 update jtf_cal_addresses
3663 set category = null
3664 where category in ( select perz_data_id from jtf_perz_data where perz_data_name = p_category_name ) ;
3665
3666
3667 update jtf_task_all_assignments
3668 set category_id = null
3669 where category_id in ( select perz_data_id from jtf_perz_data where perz_data_name = p_category_name ) ;
3670
3671
3672 delete from jtf_perz_data where perz_data_name = p_category_name ;
3673
3674 commit ;
3675 exception
3676 when others then
3677 raise ;
3678 end ;
3679
3680 PROCEDURE set_calendar_dates (
3681 p_show_on_calendar IN VARCHAR2,
3682 p_date_selected IN VARCHAR2,
3683 p_planned_start_date IN DATE,
3684 p_planned_end_date IN DATE,
3685 p_scheduled_start_date IN DATE,
3686 p_scheduled_end_date IN DATE,
3687 p_actual_start_date IN DATE,
3688 p_actual_end_date IN DATE,
3689 x_show_on_calendar IN OUT NOCOPY VARCHAR2,-- Fixed from OUT to IN OUT
3690 x_date_selected IN OUT NOCOPY VARCHAR2,-- Fixed from OUT to IN OUT
3691 x_calendar_start_date OUT NOCOPY DATE,
3692 x_calendar_end_date OUT NOCOPY DATE,
3693 x_return_status OUT NOCOPY VARCHAR2
3694 )
3695 is
3696 begin
3697 jtf_task_utl_ext.set_calendar_dates (
3698 p_show_on_calendar => p_show_on_calendar ,
3699 p_date_selected => p_date_selected ,
3700 p_planned_start_date => p_planned_start_date ,
3701 p_planned_end_date => p_planned_end_date ,
3702 p_scheduled_start_date => p_scheduled_start_date,
3703 p_scheduled_end_date => p_scheduled_end_date ,
3704 p_actual_start_date => p_actual_start_date ,
3705 p_actual_end_date => p_actual_end_date ,
3706 x_show_on_calendar => x_show_on_calendar ,
3707 x_date_selected => x_date_selected ,
3708 x_calendar_start_date => x_calendar_start_date ,
3709 x_calendar_end_date => x_calendar_end_date ,
3710 x_return_status => x_return_status ,
3711 p_task_status_id => NULL,
3712 p_creation_date => NULL
3713 );
3714 EXCEPTION
3715 WHEN OTHERS THEN
3716 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
3717 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
3718 fnd_msg_pub.add;
3719 x_return_status := fnd_api.g_ret_sts_unexp_error;
3720 END;
3721 -----
3722 -----
3723 PROCEDURE validate_status (
3724 p_status_id in number,
3725 p_type in varchar2,
3726 x_return_status OUT NOCOPY varchar2)
3727
3728 IS
3729 l_task_status_flag varchar2(1);
3730 l_assignment_status_flag varchar2(1);
3731 l_status_id number;
3732 l_usage varchar2(15);
3733
3734 BEGIN
3735 x_return_status := fnd_api.g_ret_sts_unexp_error;
3736
3737 SELECT task_status_id, task_status_flag, assignment_status_flag, usage
3738 INTO l_status_id, l_task_status_flag, l_assignment_status_flag, l_usage
3739 FROM jtf_task_statuses_b
3740 WHERE task_status_id = p_status_id;
3741
3742 IF SQL%FOUND THEN
3743 IF p_type = 'TASK' AND l_task_status_flag = jtf_task_utl.g_yes_char
3744 THEN
3745 x_return_status := fnd_api.g_ret_sts_success;
3746 ELSE
3747 IF p_type = 'ASSIGNMENT' AND l_assignment_status_flag = jtf_task_utl.g_yes_char
3748 THEN
3749 x_return_status := fnd_api.g_ret_sts_success;
3750 ELSE
3751 IF p_type = 'ESCALATION' AND l_usage = 'ESCALATION'
3752 THEN
3753 x_return_status := fnd_api.g_ret_sts_success;
3754 ELSE
3755 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_STATUS_NAME');
3756 fnd_message.set_token ('P_TASK_STATUS_NAME', jtf_task_utl.get_status_name(l_status_id));
3757 fnd_msg_pub.add;
3758 x_return_status := fnd_api.g_ret_sts_unexp_error;
3759 END IF;
3760 END IF;
3761 END IF;
3762 END IF;
3763
3764 EXCEPTION
3765 WHEN OTHERS
3766 THEN
3767 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_STATUS_NAME');
3768 fnd_message.set_token ('P_TASK_STATUS_NAME', jtf_task_utl.get_status_name(l_status_id));
3769 fnd_msg_pub.add;
3770 x_return_status := fnd_api.g_ret_sts_unexp_error;
3771 END;
3772 -------------
3773 -------------
3774 function getURL ( p_web_function_name in varchar2 )
3775 return varchar2
3776 as l_jsp_name varchar2(200);
3777 begin
3778 if p_web_function_name is not null then
3779 select web_html_call into l_jsp_name
3780 from fnd_form_functions
3781 where function_name = p_web_function_name ;
3782
3783 return l_jsp_name ;
3784 else
3785 return null ;
3786 end if ;
3787 end;
3788 -------------
3789 -------------
3790 function getURLparameter ( p_object_code in varchar2 )
3791 return varchar2
3792 as
3793 l_object_parameters varchar2(60);
3794 begin
3795 select OBJECT_PARAMETERS into l_object_parameters
3796 from jtf_objects_b
3797 where object_code = p_object_code ;
3798
3799 return l_object_parameters ;
3800 end ;
3801 -------------
3802 -------------
3803 -- fix bug #2224949
3804 FUNCTION check_truncation (p_object_name in varchar2)
3805 return varchar2
3806 is
3807
3808 x_object_name varchar2(60);
3809
3810 begin
3811
3812 if lengthb(p_object_name) > 60 then
3813 x_object_name := substrb(p_object_name, 1, 57) || '...';
3814 else
3815 x_object_name := p_object_name;
3816 end if;
3817
3818 return x_object_name;
3819
3820 end;
3821 -------------
3822 -------------
3823 -- Function added for Enhancement # 2102281
3824 FUNCTION check_duplicate_reference (p_task_id jtf_tasks_b.task_id%type,
3825 p_object_id hz_relationships.object_id%type,
3826 p_object_type_code jtf_task_references_b.object_type_code%type)
3827 return boolean
3828 is
3829
3830 x_count NUMBER := 0;
3831 x_return_value boolean := true;
3832
3833 begin
3834 select count(object_id)
3835 INTO x_count
3836 FROM JTF_TASK_REFERENCES_b
3837 WHERE task_id = p_task_id
3838 AND object_id = p_object_id
3839 AND object_type_code = p_object_type_code
3840 AND rownum = 1;
3841
3842 if x_count > 0 then
3843 x_return_value := false;
3844 else
3845 x_return_value := true;
3846 end if;
3847
3848 return x_return_value;
3849
3850 end check_duplicate_reference;
3851
3852 -------------
3853 -------------
3854 -- Function added for Enhancement # 2102281
3855 FUNCTION check_reference_delete (p_task_id jtf_tasks_b.task_id%type,
3856 p_object_id hz_relationships.object_id%type)
3857 return boolean
3858 is
3859
3860 l_delete_status boolean := true;
3861 l_dummy varchar2(100);
3862
3863 begin
3864
3865
3866 --Check from the Customer Details WRT Subject ID
3867
3868 if l_delete_status then
3869
3870 /* Check if the incoming object is shared by
3871 ** any of the subject of the Customer
3872 ** for the incoming task
3873 */
3874
3875 begin
3876
3877 select 'Exists' into l_dummy from hz_relationships
3878 where party_id in (select customer_id id
3879 from jtf_tasks_b
3880 where task_id = p_task_id)
3881 and subject_id = p_object_id;
3882
3883 l_delete_status := false;
3884
3885 Exception
3886 when too_many_rows then
3887 l_delete_status := false;
3888 when no_data_found then
3889 l_delete_status := true;
3890 end;
3891
3892 end if;
3893
3894
3895 --Check from the Assignment Details WRT Subject ID
3896
3897 if l_delete_status then
3898
3899 begin
3900
3901 /* Check if the incoming object is shared by
3902 ** any of the subject of the assignment(s)
3903 ** for the incoming task
3904 */
3905
3906 select 'Exists' into l_dummy from hz_relationships
3907 where party_id in (select resource_id id
3908 from jtf_task_assignments
3909 where task_id = p_task_id)
3910 and subject_id = p_object_id;
3911
3912
3913 l_delete_status := false;
3914
3915 Exception
3916 when too_many_rows then
3917 l_delete_status := false;
3918 when no_data_found then
3919 l_delete_status := true;
3920 end;
3921
3922
3923 end if;
3924
3925
3926 --Check from the Contact Details WRT Subject ID
3927
3928 if l_delete_status then
3929
3930 begin
3931 /* Check if the incoming object is shared by
3932 ** any of the subject of the contact(s)
3933 ** for the incoming task
3934 */
3935
3936 select 'Exists' into l_dummy from hz_relationships
3937 where party_id in (select contact_id id
3938 from jtf_task_contacts
3939 where task_id = p_task_id)
3940 and subject_id = p_object_id;
3941
3942
3943 l_delete_status := false;
3944
3945 Exception
3946 when too_many_rows then
3947 l_delete_status := false;
3948 when no_data_found then
3949 l_delete_status := true;
3950 end;
3951
3952
3953 end if;
3954
3955 --Check from the Source Details WRT Subject ID
3956
3957 if l_delete_status then
3958
3959 begin
3960
3961 /* Check if the incoming object is shared by
3962 ** the Source
3963 ** for the incoming task
3964 */
3965
3966 select 'Exists' into l_dummy from hz_relationships
3967 where party_id in (select source_object_id
3968 from jtf_tasks_b
3969 where task_id = p_task_id)
3970 and subject_id = p_object_id;
3971
3972 l_delete_status := false;
3973
3974 Exception
3975 when too_many_rows then
3976 l_delete_status := false;
3977 when no_data_found then
3978 l_delete_status := true;
3979 end;
3980
3981 end if;
3982
3983
3984 -- Check references shared by Contact(s)
3985
3986 if l_delete_status then
3987
3988 begin
3989
3990 /* Check if the incoming object is shared by
3991 ** the contact(s)
3992 ** for the incoming task
3993 */
3994
3995 select 'Party exists' into l_dummy from hz_parties
3996 where party_id in (select contact_id id
3997 from jtf_task_contacts
3998 where task_id = p_task_id
3999 and contact_id = p_object_id);
4000
4001
4002 l_delete_status := false;
4003
4004 Exception
4005 when too_many_rows then
4006 l_delete_status := false;
4007 when no_data_found then
4008 l_delete_status := true;
4009 end;
4010
4011
4012 end if;
4013
4014
4015 -- Check from the Source Details for other types - People
4016 -- or Organization
4017
4018 if l_delete_status then
4019
4020 begin
4021
4022 /* Check if the incoming object is shared by
4023 ** the Source
4024 ** for the incoming task
4025 */
4026
4027 select 'Party exists' into l_dummy from hz_parties
4028 where party_id in (select source_object_id
4029 from jtf_tasks_b
4030 where task_id = p_task_id
4031 and source_object_id = p_object_id);
4032
4033 l_delete_status := false;
4034
4035 Exception
4036 when too_many_rows then
4037 l_delete_status := false;
4038 when no_data_found then
4039 l_delete_status := true;
4040 end;
4041
4042 end if;
4043
4044 -- Check from the Customer Details for other types - People
4045 -- or Organization
4046
4047 if l_delete_status then
4048
4049 begin
4050
4051 select 'party exists' into l_dummy from hz_parties
4052 where party_id = (select customer_id
4053 from jtf_tasks_b
4054 where task_id = p_task_id
4055 and customer_id = p_object_id);
4056
4057
4058 l_delete_status := false;
4059
4060 Exception
4061 when too_many_rows then
4062 l_delete_status := false;
4063 when no_data_found then
4064 l_delete_status := true;
4065 end;
4066
4067 end if;
4068
4069
4070 -- Check from the Assignment Details for other types -
4071 -- People / Organization
4072
4073 if l_delete_status then
4074
4075 begin
4076
4077 select 'party exists' into l_dummy from hz_parties
4078 where party_id in (select resource_id id
4079 from jtf_task_assignments
4080 where task_id = p_task_id
4081 and resource_id = p_object_id);
4082
4083
4084 l_delete_status := false;
4085
4086 Exception
4087 when too_many_rows then
4088 l_delete_status := false;
4089 when no_data_found then
4090 l_delete_status := true;
4091 end;
4092
4093 end if;
4094
4095 return l_delete_status;
4096
4097 end check_reference_delete;
4098
4099 -------------
4100 -------------
4101
4102 PROCEDURE create_party_reference (
4103 p_reference_from in varchar2,
4104 p_task_id in number,
4105 p_party_type_code in varchar2,
4106 p_party_id in number,
4107 x_msg_count OUT NOCOPY number,
4108 x_msg_data OUT NOCOPY varchar2,
4109 x_return_status OUT NOCOPY varchar2)
4110
4111 ---
4112 --- This procedure creates a reference for each of the parties
4113 --- associated with a task
4114 ---
4115 --- p_reference_from can be 'TASK', 'ASSIGNMENT' or 'CONTACT'
4116 --- if p_reference_from is 'TASK' then only p_party_id is passed
4117 --- if p_reference_from is 'ASSIGNMENT' or 'CONTACT' then both
4118 --- p_party_id and p_party_type_code are passed
4119 ---
4120
4121 IS
4122
4123 l_reference_id jtf_task_references_b.task_reference_id%type;
4124 l_object_id hz_parties.party_id%type;
4125 l_subject_id hz_parties.party_id%type;
4126 l_party_name hz_parties.party_name%type;
4127 l_party_type hz_parties.party_type%type;
4128 l_task_type jtf_tasks_b.task_type_id%type;
4129 l_api_version CONSTANT NUMBER := 1.0;
4130
4131 cursor c_type (b_task_id jtf_tasks_b.task_id%type) is
4132 select task_type_id
4133 from jtf_tasks_b
4134 where task_id = b_task_id;
4135
4136 cursor c_party (b_party_id hz_parties.party_id%type) is
4137 select party_name,
4138 party_type
4139 from hz_parties
4140 where party_id = b_party_id;
4141
4142 cursor c_relation (b_relation_id hz_parties.party_id%type) is
4143 select object_id,
4144 subject_id
4145 from hz_relationships
4146 where party_id = b_relation_id
4147 and directional_flag = 'F' ;
4148
4149
4150 BEGIN
4151
4152 x_return_status := fnd_api.g_ret_sts_success;
4153
4154 --/*
4155 if p_party_id is null then
4156 return;
4157 end if;
4158 ---
4159 --- For assignments, check if it is for type PARTY_PERSON or PARTY_RELATIONSHIP,
4160 --- as these are the only types we are interested in
4161 ---
4162 if p_reference_from = 'ASSIGNMENT' and
4163 p_party_type_code not in ('PARTY_PERSON', 'PARTY_RELATIONSHIP') then
4164 return;
4165 end if;
4166 ---
4167 --- For contacts, check if it is for type CUST, as this is the only type we are
4168 --- interested in
4169 ---
4170 if p_reference_from = 'CONTACT' and
4171 p_party_type_code <> 'CUST' then
4172 return;
4173 end if;
4174 ---
4175 --- Check if the Task is an Escalation - we only create the references
4176 --- for Tasks that are not Escalation documents
4177 ---
4178 open c_type(p_task_id);
4179 fetch c_type into l_task_type;
4180 if c_type%NOTFOUND then
4181 close c_type;
4182 raise fnd_api.g_exc_unexpected_error;
4183 end if;
4184 close c_type;
4185 if l_task_type <> 22 then
4186 if p_reference_from in ('TASK', 'CONTACT', 'ASSIGNMENT') then
4187 open c_party(p_party_id);
4188 fetch c_party into l_party_name,
4189 l_party_type;
4190 if c_party%NOTFOUND then
4191 close c_party;
4192 raise fnd_api.g_exc_unexpected_error;
4193 end if;
4194 close c_party;
4195 ---
4196 --- Create a reference for the party
4197 ---
4198
4199 -- Added for Enhancement # 2102281
4200 g_show_error_for_dup_reference := False;
4201
4202 jtf_task_references_pvt.create_references (
4203 p_api_version => l_api_version,
4204 p_init_msg_list => fnd_api.g_false,
4205 p_commit => fnd_api.g_false,
4206 p_task_id => p_task_id,
4207 p_object_type_code => 'PARTY',
4208 p_object_name => l_party_name,
4209 p_object_id => p_party_id,
4210 x_return_status => x_return_status,
4211 x_msg_count => x_msg_count,
4212 x_msg_data => x_msg_data,
4213 x_task_reference_id => l_reference_id
4214 );
4215
4216 if (x_return_status = fnd_api.g_ret_sts_error) then
4217 raise fnd_api.g_exc_error;
4218 elsif (x_return_status = fnd_api.g_ret_sts_unexp_error) then
4219 raise fnd_api.g_exc_unexpected_error;
4220 end if;
4221
4222 if l_party_type = 'PARTY_RELATIONSHIP' then
4223 ---
4224 --- Create two additional references - one for the SUBJECT of the
4225 --- relationship and (not when p_reference_from = 'CONTACT') one for
4226 --- the OBJECT of the relationship
4227 ---
4228 open c_relation(p_party_id);
4229 fetch c_relation into l_object_id,
4230 l_subject_id;
4231 if c_relation%NOTFOUND then
4232 close c_relation;
4233 raise fnd_api.g_exc_unexpected_error;
4234 end if;
4235 close c_relation;
4236 ---
4237 --- Get the party details for the SUBJECT of the relationship
4238 ---
4239 open c_party(l_subject_id);
4240 fetch c_party into l_party_name,
4241 l_party_type;
4242 if c_party%NOTFOUND then
4243 close c_party;
4244 raise fnd_api.g_exc_unexpected_error;
4245 end if;
4246 close c_party;
4247
4248 -- Added for Enhancement # 2102281
4249 g_show_error_for_dup_reference := False;
4250
4251 jtf_task_references_pvt.create_references (
4252 p_api_version => l_api_version,
4253 p_init_msg_list => fnd_api.g_false,
4254 p_commit => fnd_api.g_false,
4255 p_task_id => p_task_id,
4256 p_object_type_code => 'PARTY',
4257 p_object_name => l_party_name,
4258 p_object_id => l_subject_id,
4259 x_return_status => x_return_status,
4260 x_msg_count => x_msg_count,
4261 x_msg_data => x_msg_data,
4262 x_task_reference_id => l_reference_id
4263 );
4264
4265 if (x_return_status = fnd_api.g_ret_sts_error) then
4266 raise fnd_api.g_exc_error;
4267 elsif (x_return_status = fnd_api.g_ret_sts_unexp_error) then
4268 raise fnd_api.g_exc_unexpected_error;
4269 end if;
4270 if p_reference_from in ('TASK', 'ASSIGNMENT') then
4271 ---
4272 --- Get the party details for the OBJECT of the relationship
4273 ---
4274 open c_party(l_object_id);
4275 fetch c_party into l_party_name,
4276 l_party_type;
4277 if c_party%NOTFOUND then
4278 close c_party;
4279 raise fnd_api.g_exc_unexpected_error;
4280 end if;
4281 close c_party;
4282
4283 -- Added for Enhancement # 2102281
4284 g_show_error_for_dup_reference := False;
4285
4286 jtf_task_references_pvt.create_references (
4287 p_api_version => l_api_version,
4288 p_init_msg_list => fnd_api.g_false,
4289 p_commit => fnd_api.g_false,
4290 p_task_id => p_task_id,
4291 p_object_type_code => 'PARTY',
4292 p_object_name => l_party_name,
4293 p_object_id => l_object_id,
4294 x_return_status => x_return_status,
4295 x_msg_count => x_msg_count,
4296 x_msg_data => x_msg_data,
4297 x_task_reference_id => l_reference_id
4298 );
4299
4300 if (x_return_status = fnd_api.g_ret_sts_error) then
4301 raise fnd_api.g_exc_error;
4302 elsif (x_return_status = fnd_api.g_ret_sts_unexp_error) then
4303 raise fnd_api.g_exc_unexpected_error;
4304 end if;
4305
4306 elsif l_party_type not in ('ORGANIZATION', 'PERSON') then
4307 raise fnd_api.g_exc_unexpected_error;
4308 end if;
4309 end if;
4310 else
4311 ---
4312 --- Invalid value for p_reference_from
4313 ---
4314 raise fnd_api.g_exc_unexpected_error;
4315 end if;
4316 end if;
4317
4318 --*/
4319 exception
4320 when others
4321 then
4322 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
4323 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
4324 fnd_msg_pub.add;
4325 x_return_status := fnd_api.g_ret_sts_unexp_error;
4326 end;
4327
4328 PROCEDURE delete_party_reference (
4329 p_reference_from in varchar2,
4330 p_task_id in number,
4331 p_party_type_code in varchar2,
4332 p_party_id in number,
4333 x_msg_count OUT NOCOPY number,
4334 x_msg_data OUT NOCOPY varchar2,
4335 x_return_status OUT NOCOPY varchar2)
4336 IS
4337
4338 l_reference_id jtf_task_references_b.task_reference_id%type;
4339 l_object_version jtf_task_references_b.object_version_number%type;
4340 l_object_id hz_parties.party_id%type;
4341 l_subject_id hz_parties.party_id%type;
4342 l_party_type hz_parties.party_type%type;
4343 l_task_type jtf_tasks_b.task_type_id%type;
4344 l_api_version CONSTANT NUMBER := 1.0;
4345
4346 cursor c_type (b_task_id jtf_tasks_b.task_id%type) is
4347 select task_type_id
4348 from jtf_tasks_b
4349 where task_id = b_task_id;
4350
4351 cursor c_party (b_party_id hz_parties.party_id%type) is
4352 select party_type
4353 from hz_parties
4354 where party_id = b_party_id;
4355
4356 cursor c_relation (b_relation_id hz_parties.party_id%type) is
4357 select object_id,
4358 subject_id
4359 from hz_relationships
4360 where party_id = b_relation_id
4361 and directional_flag = 'F' ;
4362
4363 cursor c_reference (b_task_id jtf_tasks_b.task_id%type,
4364 b_party_id hz_parties.party_id%type) is
4365 select task_reference_id,
4366 object_version_number
4367 from jtf_task_references_b
4368 where task_id = b_task_id
4369 and object_id = b_party_id;
4370
4371 BEGIN
4372
4373 x_return_status := fnd_api.g_ret_sts_success;
4374
4375 --/*
4376 if p_party_id is null then
4377 return;
4378 end if;
4379 ---
4380 --- For assignments, check if it is for type PARTY_PERSON or PARTY_RELATIONSHIP,
4381 --- as these are the only types we are interested in
4382 ---
4383 if p_reference_from = 'ASSIGNMENT' and
4384 p_party_type_code not in ('PARTY_PERSON', 'PARTY_RELATIONSHIP') then
4385 return;
4386 end if;
4387 ---
4388 --- For contacts, check if it is for type CUST, as this is the only type we are
4389 --- interested in
4390 ---
4391 if p_reference_from = 'CONTACT' and
4392 p_party_type_code <> 'CUST' then
4393 return;
4394 end if;
4395 ---
4396 --- Check if the Task is an Escalation - we only need to delete references
4397 --- for Tasks that are not Escalation documents
4398 ---
4399 open c_type(p_task_id);
4400 fetch c_type into l_task_type;
4401 if c_type%NOTFOUND then
4402 close c_type;
4403 raise fnd_api.g_exc_unexpected_error;
4404 end if;
4405 close c_type;
4406 if l_task_type <> 22 then
4407 if p_reference_from in ('TASK', 'CONTACT', 'ASSIGNMENT') then
4408 open c_party(p_party_id);
4409 fetch c_party into l_party_type;
4410 if c_party%NOTFOUND then
4411 close c_party;
4412 raise fnd_api.g_exc_unexpected_error;
4413 end if;
4414 close c_party;
4415 ---
4416 --- Find the reference to be deleted
4417 ---
4418 open c_reference(p_task_id, p_party_id);
4419 fetch c_reference into l_reference_id,
4420 l_object_version;
4421 if c_reference%NOTFOUND then
4422 ---
4423 --- Ignore it if the reference does not exist
4424 ---
4425 close c_reference;
4426 else
4427 close c_reference;
4428 ---
4429 --- Delete the reference for the party
4430 ---
4431 -- Added for Enhancement # 2102281
4432 g_show_error_for_dup_reference := False;
4433
4434 jtf_task_references_pvt.delete_references (
4435 p_api_version => l_api_version,
4436 p_init_msg_list => fnd_api.g_false,
4437 p_commit => fnd_api.g_false,
4438 p_object_version_number => l_object_version,
4439 p_task_reference_id => l_reference_id,
4440 x_return_status => x_return_status,
4441 x_msg_count => x_msg_count,
4442 x_msg_data => x_msg_data
4443 );
4444
4445 if (x_return_status = fnd_api.g_ret_sts_error) then
4446 raise fnd_api.g_exc_error;
4447 elsif (x_return_status = fnd_api.g_ret_sts_unexp_error) then
4448 raise fnd_api.g_exc_unexpected_error;
4449 end if;
4450 end if;
4451
4452 if l_party_type = 'PARTY_RELATIONSHIP' then
4453 ---
4454 --- Delete two additional references - one for the SUBJECT of the
4455 --- relationship and (not when p_reference_from = 'CONTACT') one for
4456 --- the OBJECT of the relationship
4457 ---
4458 open c_relation(p_party_id);
4459 fetch c_relation into l_object_id,
4460 l_subject_id;
4461 if c_relation%NOTFOUND then
4462 close c_relation;
4463 raise fnd_api.g_exc_unexpected_error;
4464 end if;
4465 close c_relation;
4466 ---
4467 --- Get the party details for the SUBJECT of the relationship
4468 ---
4469 open c_party(l_subject_id);
4470 fetch c_party into l_party_type;
4471 if c_party%NOTFOUND then
4472 close c_party;
4473 raise fnd_api.g_exc_unexpected_error;
4474 end if;
4475 close c_party;
4476 ---
4477 --- Find the reference to be deleted
4478 ---
4479 open c_reference(p_task_id, l_subject_id);
4480 fetch c_reference into l_reference_id,
4481 l_object_version;
4482 if c_reference%NOTFOUND then
4483 ---
4484 --- Ignore it if the reference does not exist
4485 ---
4486 close c_reference;
4487 else
4488 close c_reference;
4489 ---
4490 --- Delete the reference for the party
4491 ---
4492 -- Added for Enhancement # 2102281
4493 g_show_error_for_dup_reference := False;
4494
4495 jtf_task_references_pvt.delete_references (
4496 p_api_version => l_api_version,
4497 p_init_msg_list => fnd_api.g_false,
4498 p_commit => fnd_api.g_false,
4499 p_object_version_number => l_object_version,
4500 p_task_reference_id => l_reference_id,
4501 x_return_status => x_return_status,
4502 x_msg_count => x_msg_count,
4503 x_msg_data => x_msg_data
4504 );
4505
4506 if (x_return_status = fnd_api.g_ret_sts_error) then
4507 raise fnd_api.g_exc_error;
4508 elsif (x_return_status = fnd_api.g_ret_sts_unexp_error) then
4509 raise fnd_api.g_exc_unexpected_error;
4510 end if;
4511 end if;
4512
4513 if p_reference_from in ('TASK', 'ASSIGNMENT') then
4514 ---
4515 --- Get the party details for the OBJECT of the relationship
4516 ---
4517 open c_party(l_object_id);
4518 fetch c_party into l_party_type;
4519 if c_party%NOTFOUND then
4520 close c_party;
4521 raise fnd_api.g_exc_unexpected_error;
4522 end if;
4523 close c_party;
4524 ---
4525 --- Find the reference to be deleted
4526 ---
4527 open c_reference(p_task_id, l_object_id);
4528 fetch c_reference into l_reference_id,
4529 l_object_version;
4530 if c_reference%NOTFOUND then
4531 ---
4532 --- Ignore it if the reference does not exist
4533 ---
4534 close c_reference;
4535 else
4536 close c_reference;
4537 ---
4538 --- Delete the reference for the party
4539 ---
4540 -- Added for Enhancement # 2102281
4541 g_show_error_for_dup_reference := False;
4542
4543 jtf_task_references_pvt.delete_references (
4544 p_api_version => l_api_version,
4545 p_init_msg_list => fnd_api.g_false,
4546 p_commit => fnd_api.g_false,
4547 p_object_version_number => l_object_version,
4548 p_task_reference_id => l_reference_id,
4549 x_return_status => x_return_status,
4550 x_msg_count => x_msg_count,
4551 x_msg_data => x_msg_data
4552 );
4553
4554 if (x_return_status = fnd_api.g_ret_sts_error) then
4555 raise fnd_api.g_exc_error;
4556 elsif (x_return_status = fnd_api.g_ret_sts_unexp_error) then
4557 raise fnd_api.g_exc_unexpected_error;
4558 end if;
4559 end if;
4560
4561 elsif l_party_type not in ('ORGANIZATION', 'PERSON') then
4562 raise fnd_api.g_exc_unexpected_error;
4563 end if;
4564 end if;
4565 else
4566 ---
4567 --- Invalid value for p_reference_from
4568 ---
4569 raise fnd_api.g_exc_unexpected_error;
4570 end if;
4571 end if;
4572
4573 --*/
4574 exception
4575 when others
4576 then
4577 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
4578 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
4579 fnd_msg_pub.add;
4580 x_return_status := fnd_api.g_ret_sts_unexp_error;
4581 end;
4582
4583 --Bug 2467222 for assignee category update
4584 --- update_task_assignee procedure begin
4585
4586 PROCEDURE update_task_category (
4587 p_api_version IN NUMBER,
4588 p_object_version_number IN OUT NOCOPY NUMBER,
4589 p_task_assignment_id IN NUMBER,
4590 p_category_id IN NUMBER,
4591 x_return_status OUT NOCOPY VARCHAR2,
4592 x_msg_count OUT NOCOPY NUMBER,
4593 x_msg_data OUT NOCOPY VARCHAR2
4594 )
4595 IS
4596
4597 BEGIN
4598 jtf_task_assignments_pvt.update_task_assignment (
4599 p_api_version => p_api_version,
4600 p_object_version_number => p_object_version_number,
4601 p_init_msg_list => fnd_api.g_false,
4602 p_commit => fnd_api.g_false,
4603 p_task_assignment_id => p_task_assignment_id,
4604 x_msg_data => x_msg_data,
4605 x_msg_count => x_msg_count,
4606 x_return_status => x_return_status,
4607 P_CATEGORY_ID => p_category_id
4608 );
4609
4610 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
4611 THEN
4612 x_return_status := fnd_api.g_ret_sts_unexp_error;
4613 RAISE fnd_api.g_exc_unexpected_error;
4614 END IF;
4615
4616 END;
4617
4618 FUNCTION get_owner_detail (p_object_type_code IN VARCHAR2, p_object_id IN NUMBER)
4619 RETURN VARCHAR2
4620 IS
4621 CURSOR c_references
4622 IS
4623 SELECT select_id, select_details, from_table, where_clause
4624 FROM jtf_objects_b
4625 WHERE object_code = p_object_type_code;
4626 --- Removed the reference from JTF_OBJECTS_VL to JT_OBJECTS_B.
4627
4628
4629 l_id_column jtf_objects_b.select_id%TYPE;
4630 l_details_column jtf_objects_b.select_details%TYPE;
4631 l_from_clause jtf_objects_b.from_table%TYPE;
4632 l_where_clause jtf_objects_b.where_clause%TYPE;
4633 l_object_code jtf_tasks_b.source_object_type_code%TYPE
4634 := p_object_type_code;
4635 l_object_details VARCHAR2(2000);
4636 is_null BOOLEAN := FALSE;
4637 is_not_null BOOLEAN := FALSE;
4638 sql_stmt VARCHAR2(2000);
4639 BEGIN
4640 OPEN c_references;
4641 FETCH c_references INTO l_id_column,
4642 l_details_column,
4643 l_from_clause,
4644 l_where_clause;
4645
4646 IF c_references%NOTFOUND
4647 THEN
4648 NULL;
4649 END IF;
4650
4651 IF (l_where_clause IS NULL)
4652 THEN
4653 l_where_clause := ' ';
4654 ELSE
4655 l_where_clause := l_where_clause || ' AND ';
4656 END IF;
4657
4658 -- Added 'rownum = 1' bshih
4659 sql_stmt := ' SELECT ' ||
4660 l_details_column ||
4661 ' from ' ||
4662 l_from_clause ||
4663 ' where ' ||
4664 l_where_clause ||
4665 l_id_column ||
4666 ' = :object_id and rownum = 1';
4667
4668 EXECUTE IMMEDIATE sql_stmt INTO l_object_details
4669 USING p_object_id;
4670 RETURN l_object_details;
4671
4672 EXCEPTION
4673 WHEN OTHERS
4674 THEN
4675 RETURN NULL;
4676 END;
4677
4678 -- Function to convert status id into status name
4679 FUNCTION get_status_name (p_status_id IN NUMBER)
4680 RETURN VARCHAR2
4681 AS
4682 l_status_name VARCHAR2(30);
4683 BEGIN
4684 IF p_status_id IS NULL
4685 THEN
4686 RETURN NULL;
4687 ELSE
4688 SELECT name
4689 INTO l_status_name
4690 FROM jtf_task_statuses_vl
4691 WHERE task_status_id = p_status_id;
4692 END IF;
4693
4694 RETURN l_status_name;
4695 EXCEPTION
4696 WHEN NO_DATA_FOUND
4697 THEN
4698 RETURN NULL;
4699 WHEN OTHERS
4700 THEN
4701 RETURN NULL;
4702 END;
4703
4704 FUNCTION enable_audit (p_enable IN BOOLEAN)
4705 RETURN BOOLEAN
4706 AS PRAGMA autonomous_transaction;
4707 l_status BOOLEAN := FALSE;
4708 BEGIN
4709 IF( p_enable ) THEN
4710 l_status:=fnd_profile.save('JTF_TASK_ENABLE_AUDIT',g_yes,'SITE');
4711 ELSE
4712 l_status:=fnd_profile.save('JTF_TASK_ENABLE_AUDIT',g_no,'SITE');
4713 END IF;
4714 IF(l_status) THEN
4715 COMMIT;
4716 END IF;
4717 RETURN l_status;
4718 END;
4719
4720
4721 FUNCTION ServerToTaskTz(p_date IN DATE,p_TaskTz IN NUMBER) RETURN DATE IS
4722 l_server_tz NUMBER;
4723 l_client_tz NUMBER;
4724 l_dest_date DATE;
4725 l_temp_date DATE;
4726 l_tz_enabled VARCHAR2(5);
4727 BEGIN
4728 IF ( p_TaskTz is NULL)
4729 THEN
4730 RETURN p_date;
4731 END IF;
4732
4733 l_server_tz := fnd_profile.value('SERVER_TIMEZONE_ID');
4734 l_client_tz := fnd_profile.value('CLIENT_TIMEZONE_ID');
4735 l_tz_enabled := fnd_profile.value('ENABLE_TIMEZONE_CONVERSIONS');
4736
4737 jtf_cal_utility_pvt.adjustfortimezone( l_server_tz,
4738 p_TaskTz,
4739 p_date,
4740 l_temp_date);
4741
4742 l_dest_date := l_temp_date;
4743
4744 IF (l_tz_enabled = 'Y')
4745 THEN
4746 jtf_cal_utility_pvt.adjustfortimezone( l_client_tz,
4747 l_server_tz,
4748 l_temp_date,
4749 l_dest_date);
4750 END IF;
4751 RETURN l_dest_date;
4752 end;
4753
4754 FUNCTION TaskToServerTz(p_date IN DATE,p_TaskTz IN NUMBER) RETURN DATE IS
4755 l_server_tz NUMBER;
4756 l_client_tz NUMBER;
4757 l_dest_date DATE;
4758 l_temp_date DATE;
4759 l_tz_enabled VARCHAR2(5);
4760 BEGIN
4761 IF ( p_TaskTz is NULL)
4762 THEN
4763 RETURN p_date;
4764 END IF;
4765
4766 l_server_tz := fnd_profile.value('SERVER_TIMEZONE_ID');
4767 l_client_tz := fnd_profile.value('CLIENT_TIMEZONE_ID');
4768 l_tz_enabled := fnd_profile.value('ENABLE_TIMEZONE_CONVERSIONS');
4769
4770 l_temp_date:=p_date;
4771
4772 IF (l_tz_enabled = 'Y')
4773 THEN
4774 jtf_cal_utility_pvt.adjustfortimezone( l_server_tz,
4775 l_client_tz,
4776 p_date,
4777 l_temp_date);
4778 END IF;
4779
4780 jtf_cal_utility_pvt.adjustfortimezone( p_TaskTz,
4781 l_server_tz,
4782 l_temp_date,
4783 l_dest_date);
4784 RETURN l_dest_date;
4785 end;
4786 END;