[Home] [Help]
PACKAGE BODY: APPS.JTA_SYNC_TASK_COMMON
Source
1 PACKAGE BODY jta_sync_task_common AS
2 /* $Header: jtavstcb.pls 120.2 2005/12/28 22:19:48 deeprao ship $ */
3 /*======================================================================+
4 | Copyright (c) 1995 Oracle Corporation Redwood Shores, California, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 | FILENAME |
8 | jtavstcb.pls |
9 | |
10 | DESCRIPTION |
11 | This package is a common for sync task |
12 | |
13 | NOTES |
14 | |
15 | |
16 | Date Developer Change |
17 | ------ --------------- --------------------------------------- |
18 | 19-Feb-2002 cjang Changed to binary structure for if-test |
19 | in get_event_type |
20 | 20-Feb-2002 cjang The followings are not synced |
21 | 1) Change from Task to Appt |
22 | 2) Change from Appt to Task |
23 | Removed the parameter x_event_type, |
24 | x_object form get_event_type |
25 | We do not sync the tasks on public |
26 | calendar |
27 | Changed get_group_calendar() |
28 | 21-Feb-2002 cjang Refactoring |
29 | 22-Feb-2002 cjang Refactoring |
30 | 25-Feb-2002 cjang Bug Fix on set_alarm_date() |
31 | Removed include_record() |
32 | Removed ABS() on get_alarm_mins() |
33 | Modified get_client_priority() |
34 | 27-Feb-2002 cjang Added can_update_task, get_max_enddate |
35 | Modified add_task |
36 | 28-Feb-2002 cjang Integrate with invitee function |
37 | 01-Mar-2002 cjang Refactoring and Bug Fix |
38 | 06-Mar-2002 cjang Added get_all_nonrepeat_tasks |
39 | get_all_repeat_tasks |
40 | create_new_data |
41 | update_existing_data |
42 | add_nonrepeat_task |
43 | add_repeat_task |
44 | Modified parameters on add_task |
45 | 08-Mar-2002 arpatel Added all attributes for update of |
46 | repeating appointments |
47 | 11-Mar-2002 cjang Added the followings |
48 | - delete_exclusion_task |
49 | - delete_task_data |
50 | - reject_task_data |
51 | - changed_repeat_rule |
52 | - update_repeating_rule |
53 | 11-Mar-2002 sanjeev - changed methods for exclusions |
54 | - changed update_existing_data() |
55 | 13-Mar-2002 cjang Added insert_or_update_mapping |
56 | Changed OUT to IN OUT on the parameter |
57 | x_task_rec in delete_exclusion_task |
58 | 14-Mar-2002 arpatel Added privateFlag to create_new_data |
59 | and update_existing_data |
60 | 14-Mar-2002 ssallaka Added get_exclusion _data |
61 | 15-Mar-2002 ssallaka Added count_exclusion |
62 | 25-Mar-2002 sanjeev Added procedure transformStatus() |
63 | function getChangedStatusId() and |
64 | checkUserStatusRule() |
65 | 04-Apr-2002 cjang Modified convert_recur_date_to_client |
66 | TO_DATE should only use numbers |
67 | 08-Apr-2002 arpatel Merged convert_dates2 with convert_dates |
68 | 09-APr-2002 ssallaka Choping the subject of task if it is |
69 | more than 80 chars and fixed the typo |
70 | of tuesday in task_rec |
71 | 19-Apr-2002 cjang Removed p_get_data from add_task() |
72 | 24-Apr-2002 cjang Process exclusion first, and update |
73 | 25-Apr-2002 cjang Modified update_existing_data() to fix |
74 | the length(80) issue of task name |
75 | Modified get_all_deleted_tasks to execute |
76 | the new cursor c_delete_assignee_reject |
77 | to pick up the appts rejected by assignee|
78 | 26-Apr-2002 cjang Modified all the calls to check_span_days |
79 | to pass source_object_type_code |
80 | rather than p_request_type |
81 | All delete cursor has now p_resource_type |
82 | 30-Apr-2002 cjang Added a new parameter p_resource_type |
83 | in get_assignment_id |
84 | Fixed the undefined message name |
85 | 01-May-2002 cjang Fixed task_name length issue |
86 | Added convert_carriage_return |
87 | Modified get_subject |
88 | 17-May-2002 cjang Fix for the bug 2380399 |
89 | Modified update_existing_data() |
90 | 21-May-2002 cjang Modified get_exclusion_data() |
91 | Added "DISTINCT" in the cursor c_exclusion|
92 | 23-May-2002 cjang Modified add_task() to fix bug 2389092 |
93 | it defaults x_task_rec.dateselected with |
94 | 'S' when it's NULL. |
95 | Added validate_syncid() to fix bug 2382927 |
96 | 29-May-2002 cjang Modified validate_syncid() |
97 | to fix Bug# 2395004 |
98 | |
99 | 02-Jul-2002 cjang (Fix Bug: 2442686) Ver: 115.75 |
100 | If this is TASK, assignee can update any |
101 | fields, but if it's APPOINTMENT, then |
102 | the invitee can update only the status when|
103 | he/she accept the appointment. |
104 | Modified get_update_type() |
105 | to check source object type code when the|
106 | login user is an assignee for the task |
107 | |
108 | (Fix Bug: 2443049) Ver: 115.76 |
109 | When a new task is synced from Outlook, |
110 | display on calendar should be checked. |
111 | When a updated task is synced from Outlook,|
112 | display on calendar should not be nullified|
113 | 04-Oct-2002 cjang To fix bug 2540722, |
114 | in ADD_TASK() |
115 | 1) Change local varible definition |
116 | from l_category_name VARCHAR2(40); |
117 | to l_category_name VARCHAR2(240);|
118 | |
119 | 2) Change from |
120 | l_category_name := jtf_task_utl.get_category_name_for_task( |
121 | p_task_id => p_task_id, |
122 | p_resource_id => p_resource_id, |
123 | p_resource_type_code => p_resource_type|
124 | ); |
125 | to |
126 | l_category_name := substrb(jtf_task_utl.get_category_name_for_task(|
127 | p_task_id => p_task_id, |
128 | p_resource_id => p_resource_id, |
129 | p_resource_type_code => p_resource_type |
130 | ), 1, 240); |
131 | |
132 | To Fix bug 2608703, Removed the followings |
133 | IF (l_category_name = 'Unfiled') |
134 | THEN |
135 | l_category_name := NULL; |
136 | END IF; |
137 | 04-Oct-2002 cjang Fixed bug 2469488, 2469487, 2469479 |
138 | 1) Modified get_all_nonrepeat_tasks |
139 | 2) Modified get_all_deleted_tasks |
140 | 3) Modified get_all_repeat_tasks |
141 | 4) Added already_selected() |
142 | Fixed bug 2482833 |
143 | 1) Added get_sync_info() |
144 | 2) Modified get_update_type() |
145 | Fixed GSCC Warning |
146 | File.Pkg.22 |
147 | 1190-1, 1511, 2527, 3144 - |
148 | No default parameter values in package body|
149 | 09-Oct-2002 cjang Fixed bug 2467021 |
150 | Modified convert_dates(),create_new_data(),update_existing_data():
151 | If it is APPOINTMENT, pass Y for show_on_calendar and pass P for date_selected.
152 | This is coded by the package jta_cal_appointment_pvt.
153 | If it is TASK for creation, pass show_on_calendar and date_selected as NULL
154 | if it is TASK for update, pass show_on_calendar and date_selected as g_miss_char
155 | 22-Oct-2002 cjang Fixed bug 2635512, Removed debug_pkg.add |
156 | 01-Nov-2002 cjang Fixed bug 2540722 |
157 | Call jtf_task_security_pvt.get_category_id()|
158 | and jtf_task_utl.get_category_name() |
159 | instead of calling jtf_task_utl.get_category_name_for_task()
160 | 07-Nov-2002 cjang Removed the code fix for the bug 2469488, |
161 | 2469487, 2469479 |
162 | But keep the function already_selected() |
163 | so this version 115.82 is basically |
164 | same as 115.81 |
165 | Here just removed the comment |
166 | "Fixed bug 2469488, 2469487, 2469479" |
167 *=======================================================================*/
168
169 PROCEDURE check_span_days (
170 p_source_object_type_code IN VARCHAR2,
171 p_calendar_start_date IN DATE,
172 p_calendar_end_date IN DATE,
173 x_status OUT NOCOPY BOOLEAN
174 )
175 IS
176 BEGIN
177 -------------------------------------------
178 -- Returns TRUE:
179 -- 1) if an appointment spans over a day
180 -- 2) if a task is endless
181 -------------------------------------------
182 x_status := FALSE;
183
184 IF (p_source_object_type_code = G_TASK AND
185 p_calendar_end_date IS NULL AND
186 p_calendar_start_date IS NOT NULL
187 )
188 OR (p_source_object_type_code = G_APPOINTMENT AND
189 TRUNC (p_calendar_start_date) <> TRUNC (p_calendar_end_date)
190 )
191 THEN
192 x_status := TRUE;
193 END IF;
194
195 END check_span_days;
196
197 FUNCTION convert_carriage_return(
198 p_subject IN VARCHAR2
199 ,p_type IN VARCHAR2)
200 RETURN VARCHAR2
201 IS
202 l_from VARCHAR2(10);
203 l_to VARCHAR2(10);
204 BEGIN
205 IF p_type = 'ORACLE'
206 THEN
207 l_from := G_CARRIAGE_RETURN_XML;
208 l_to := G_CARRIAGE_RETURN_ORACLE;
209 ELSE
210 l_from := G_CARRIAGE_RETURN_ORACLE;
211 l_to := G_CARRIAGE_RETURN_XML;
212 END IF;
213
214 RETURN REPLACE(p_subject, l_from ,l_to);
215 END convert_carriage_return;
216
217 FUNCTION get_subject(p_subject IN VARCHAR2
218 ,p_type IN VARCHAR2)
219 RETURN VARCHAR2
220 IS
221 l_from VARCHAR2(10);
222 l_to VARCHAR2(10);
223 BEGIN
224 RETURN SUBSTR(convert_carriage_return(p_subject,p_type), 1, 80);
225 END get_subject;
226
227 PROCEDURE convert_recur_date_to_gmt (
228 p_timezone_id IN NUMBER,
229 p_base_start_date IN DATE,
230 p_base_end_date IN DATE,
231 p_start_date IN DATE,
232 p_end_date IN DATE,
233 p_item_display_type IN NUMBER,
234 p_occurs_which IN NUMBER,
235 p_uom IN VARCHAR2,
236 x_date_of_month OUT NOCOPY NUMBER,
237 x_start_date IN OUT NOCOPY DATE,
238 x_end_date IN OUT NOCOPY DATE
239 )
240 IS
241 l_start_date VARCHAR2(11); -- DD-MON-YYYY
242 l_start_time VARCHAR2(8); -- HH24:MI:SS
243 l_end_date VARCHAR2(11); -- DD-MON-YYYY
244 l_end_time VARCHAR2(8); -- HH24:MI:SS
245 BEGIN
246 l_start_date := TO_CHAR (p_start_date, 'DD-MON-YYYY');
247 l_start_time := TO_CHAR (p_base_start_date, 'HH24:MI:SS');
248 l_end_date := TO_CHAR (p_end_date, 'DD-MON-YYYY');
249 l_end_time := TO_CHAR (p_base_end_date, 'HH24:MI:SS');
250
251 IF p_item_display_type <> 3 THEN
252 x_start_date :=
253 convert_task_to_gmt (
254 TO_DATE (
255 l_start_date || ' ' || l_start_time,
256 'DD-MON-YYYY HH24:MI:SS'
257 ),
258 p_timezone_id
259 );
260 x_end_date :=
261 convert_task_to_gmt (
262 TO_DATE (
263 l_end_date || ' ' || l_end_time,
264 'DD-MON-YYYY HH24:MI:SS'
265 ),
266 p_timezone_id
267 );
268 ELSE
269 x_start_date := TO_DATE (
270 l_start_date || ' ' || l_start_time,
271 'DD-MON-YYYY HH24:MI:SS');
272 x_end_date :=TO_DATE (
273 l_end_date || ' ' || l_end_time,
274 'DD-MON-YYYY HH24:MI:SS'
275 );
276 END IF;
277 x_start_date := TRUNC (x_start_date);
278 x_end_date := TRUNC (x_end_date);
279
280 IF p_occurs_which IS NULL
281 AND (p_uom = 'MON' OR p_uom ='YER') THEN
282 x_date_of_month := TO_CHAR (x_start_date, 'DD');
283 END IF;
284 END convert_recur_date_to_gmt;
285
286 PROCEDURE process_exclusions (
287 p_exclusion_tbl IN jta_sync_task.exclusion_tbl,
288 p_rec_rule_id IN NUMBER,
289 p_repeating_task_id IN NUMBER,
290 p_task_rec IN OUT NOCOPY jta_sync_task.task_rec
291 )
292 IS
293 i NUMBER := 0;
294 l_exclude_task_id NUMBER ;
295 BEGIN
296 FOR i IN p_exclusion_tbl.FIRST .. p_exclusion_tbl.LAST
297 LOOP
298 l_exclude_task_id := get_excluding_taskid (
299 p_sync_id => p_task_rec.syncid,
300 p_recurrence_rule_id => p_rec_rule_id,
301 p_exclusion_rec => p_exclusion_tbl (i)
302 );
303
304 IF l_exclude_task_id > 0
305 THEN
306 delete_exclusion_task (
307 p_repeating_task_id => l_exclude_task_id,
308 x_task_rec => p_task_rec
309 );
310 END IF; -- l_task_id
311 END LOOP;
312 END process_exclusions;
313
314 FUNCTION get_default_task_type
315 RETURN NUMBER
316 IS
317 BEGIN
318 RETURN NVL (
319 fnd_profile.VALUE ('JTF_TASK_DEFAULT_TASK_TYPE'),
320 g_task_type_general
321 );
322 END;
323
324 FUNCTION is_this_new_task (p_sync_id IN NUMBER)
325 RETURN BOOLEAN
326 IS
327 CURSOR c_synctask
328 IS
329 SELECT task_id
330 FROM jta_sync_task_mapping
331 WHERE task_sync_id = p_sync_id;
332
333 l_task_id NUMBER;
334 BEGIN
335 IF p_sync_id IS NULL OR
336 p_sync_id < 1
337 THEN
338 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
339 fnd_msg_pub.add;
340
341 fnd_message.set_name('JTF', 'JTA_SYNC_INVALID_SYNCID');
342 fnd_message.set_token('PROC_NAME','JTA_SYNC_TASK_COMMON.IS_THIS_NEW_TASK');
343 fnd_msg_pub.add;
344
345 raise_application_error (-20100,jta_sync_common.get_messages);
346 END IF;
347
348 OPEN c_synctask;
349 FETCH c_synctask INTO l_task_id;
350
351 IF c_synctask%NOTFOUND
352 THEN
353 CLOSE c_synctask;
354 RETURN TRUE;
355 ELSE
356 CLOSE c_synctask;
357 RETURN FALSE;
358 END IF;
359 END;
360
361 -- count num of exclusions from jta_task_exclusion
362 FUNCTION count_exclusions (p_recurrence_rule_id IN NUMBER)
363 RETURN NUMBER
364 IS
365 l_count NUMBER;
366 BEGIN
367 SELECT COUNT (recurrence_rule_id)
368 INTO l_count
369 FROM jta_task_exclusions
370 WHERE recurrence_rule_id = p_recurrence_rule_id;
371 RETURN l_count;
372 END count_exclusions;
373
374 FUNCTION count_excluded_tasks (p_recurrence_rule_id IN NUMBER)
375 RETURN NUMBER
376 IS
377 l_count NUMBER;
378 BEGIN
379 SELECT COUNT (recurrence_rule_id)
380 INTO l_count
381 FROM jtf_tasks_b
382 WHERE recurrence_rule_id = p_recurrence_rule_id;
383 RETURN l_count;
384 END count_excluded_tasks;
385
386 FUNCTION check_for_exclusion (
387 p_sync_id IN NUMBER,
388 p_exclusion_tbl IN jta_sync_task.exclusion_tbl,
389 p_calendar_start_date IN DATE,
390 p_client_time_zone_id IN NUMBER
391 )
392 RETURN BOOLEAN
393 IS
394 is_exclusion BOOLEAN;
395 l_task_date DATE;
396 BEGIN
397 IF (p_exclusion_tbl.COUNT = 0)
398 OR (p_exclusion_tbl IS NULL)
399 THEN
400 RETURN FALSE;
401 ELSE
402 is_exclusion := FALSE;
403
404 FOR i IN p_exclusion_tbl.FIRST .. p_exclusion_tbl.LAST
405 LOOP
406 l_task_date := p_calendar_start_date;
407
408 IF (p_sync_id = p_exclusion_tbl (i).syncid)
409 AND (TRUNC (l_task_date) =
410 TRUNC (p_exclusion_tbl (i).exclusion_date))
411 THEN
412 is_exclusion := TRUE;
413 EXIT;
414 END IF;
415 END LOOP; --end of the loop
416
417 RETURN is_exclusion;
418 END IF;
419 END;
420
421 FUNCTION get_excluding_taskid (
422 p_sync_id IN NUMBER,
423 p_recurrence_rule_id IN NUMBER,
424 p_exclusion_rec IN jta_sync_task.exclusion_rec
425 )
426 RETURN NUMBER
427 IS
428 CURSOR c_recur_tasks (b_recurrence_rule_id NUMBER,
429 b_exclusion_start_date DATE)
430 IS
431 SELECT task_id
432 FROM jtf_tasks_b
433 WHERE recurrence_rule_id = b_recurrence_rule_id
434 AND TRUNC (calendar_start_date) = TRUNC (b_exclusion_start_date);
435
436 l_task_id NUMBER;
437 BEGIN
438 OPEN c_recur_tasks (
439 b_recurrence_rule_id => p_recurrence_rule_id,
440 b_exclusion_start_date => p_exclusion_rec.exclusion_date
441 );
442 FETCH c_recur_tasks INTO l_task_id;
443
444 IF c_recur_tasks%NOTFOUND
445 THEN
446 l_task_id := -9;
447 END IF;
448
449 CLOSE c_recur_tasks;
450
451 IF p_sync_id <> p_exclusion_rec.syncid
452 THEN
453 l_task_id := -9;
454 END IF;
455
456 RETURN l_task_id;
457 END;
458
459 FUNCTION set_alarm_date (
460 p_task_id IN NUMBER,
461 p_request_type IN VARCHAR2,
462 p_scheduled_start_date IN DATE,
463 p_planned_start_date IN DATE,
464 p_actual_start_date IN DATE,
465 p_alarm_flag IN VARCHAR2,
466 p_alarm_start IN NUMBER
467 )
468 RETURN DATE
469 IS
470 l_date_selected VARCHAR2(1);
471 l_date DATE;
472 l_alarm_date DATE;
473 l_alarm_days NUMBER;
474
475 CURSOR c_dateselect
476 IS
477 SELECT jt.date_selected
478 FROM jtf_tasks_b jt
479 WHERE jt.task_id = p_task_id;
480 --check for alarm flag
481
482 BEGIN
483 IF p_alarm_flag = 'Y'
484 THEN
485 OPEN c_dateselect;
486 FETCH c_dateselect INTO l_date_selected;
487
488 IF c_dateselect%NOTFOUND
489 OR l_date_selected = 'P'
490 OR p_request_type = G_REQ_APPOINTMENT
491 THEN
492 l_date := p_planned_start_date;
493 ELSIF l_date_selected = 'S'
494 THEN
495 l_date := p_scheduled_start_date;
496 ELSIF l_date_selected = 'A'
497 THEN
498 l_date := p_actual_start_date;
499 END IF;
500
501 CLOSE c_dateselect;
502 l_alarm_days := p_alarm_start / 1440;
503 l_alarm_date := l_date - l_alarm_days;
504 END IF;
505
506 RETURN l_alarm_date;
507 END;
508
509 FUNCTION get_task_id (p_sync_id IN NUMBER)
510 RETURN NUMBER
511 IS
512 CURSOR c_task_sync
513 IS
514 SELECT task_id
515 FROM jta_sync_task_mapping
516 WHERE task_sync_id = p_sync_id;
517
518 l_task_id NUMBER;
519 BEGIN
520 IF p_sync_id IS NULL
521 OR p_sync_id < 1
522 THEN
523 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
524 fnd_msg_pub.add;
525
526 fnd_message.set_name('JTF', 'JTA_SYNC_INVALID_SYNCID');
527 fnd_message.set_token('PROC_NAME','JTA_SYNC_TASK_COMMON.GET_TASK_ID');
528 fnd_msg_pub.add;
529
530 raise_application_error (-20100,jta_sync_common.get_messages);
531
532 END IF;
533
534 OPEN c_task_sync;
535 FETCH c_task_sync INTO l_task_id;
536
537 IF c_task_sync%NOTFOUND
538 THEN
539 CLOSE c_task_sync;
540
541 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
542 fnd_msg_pub.add;
543
544 fnd_message.set_name('JTF', 'JTA_SYNC_NOTFOUND_TASKID');
545 fnd_message.set_token('PROC_NAME','JTA_SYNC_TASK_COMMON.GET_TASK_ID');
546 fnd_msg_pub.add;
547
548 raise_application_error (-20100,jta_sync_common.get_messages);
549 ELSIF l_task_id IS NULL
550 THEN
551 CLOSE c_task_sync;
552
553 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
554 fnd_msg_pub.add;
555
556 fnd_message.set_name('JTF', 'JTA_SYNC_NULL_TASKID');
557 fnd_message.set_token('PROC_NAME','JTA_SYNC_TASK_COMMON.GET_TASK_ID');
558 fnd_msg_pub.add;
559
560 raise_application_error (-20100,jta_sync_common.get_messages);
561 END IF;
562
563 CLOSE c_task_sync;
564 RETURN l_task_id;
565 END;
566
567 FUNCTION get_task_id (p_task_assignment_id IN NUMBER)
568 RETURN NUMBER
569 IS
570 CURSOR c_task
571 IS
572 SELECT task_id
573 FROM jtf_task_all_assignments
574 WHERE task_assignment_id = p_task_assignment_id;
575
576 l_task_id NUMBER;
577 BEGIN
578 OPEN c_task;
579 FETCH c_task INTO l_task_id;
580
581 IF c_task%NOTFOUND
582 THEN
583 CLOSE c_task;
584
585 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
586 fnd_msg_pub.add;
587
588 fnd_message.set_name('JTF', 'JTA_SYNC_NOTFOUND_TASKID');
589 fnd_message.set_token('PROC_NAME','JTA_SYNC_TASK_COMMON.GET_TASK_ID');
590 fnd_msg_pub.add;
591
592 raise_application_error (-20100,jta_sync_common.get_messages);
593 END IF;
594
595 CLOSE c_task;
596 RETURN l_task_id;
597 END;
598
599 FUNCTION get_task_timezone_id (p_task_id IN NUMBER)
600 RETURN NUMBER
601 IS
602 CURSOR c_task_timezone
603 IS
604 SELECT timezone_id
605 FROM jtf_tasks_b
606 WHERE task_id = p_task_id;
607
608 l_task_timezone_id NUMBER;
609 BEGIN
610 OPEN c_task_timezone;
611 FETCH c_task_timezone INTO l_task_timezone_id;
612
613 IF c_task_timezone%NOTFOUND
614 THEN
615 CLOSE c_task_timezone;
616
617 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
618 fnd_msg_pub.add;
619
620 fnd_message.set_name('JTF', 'JTA_SYNC_TIMEZONEID_NOTFOUND');
621 fnd_message.set_token('PROC_NAME','JTA_SYNC_TASK_COMMON.GET_TASK_TIMEZONE_ID');
622 fnd_msg_pub.add;
623
624 raise_application_error (-20100,jta_sync_common.get_messages);
625
626 END IF;
627
628 CLOSE c_task_timezone;
629
630 IF l_task_timezone_id IS NULL
631 THEN
632 l_task_timezone_id :=
633 NVL (fnd_profile.VALUE ('CLIENT_TIMEZONE_ID'), 0);
634 END IF;
635
636 RETURN l_task_timezone_id;
637 END;
638
639 FUNCTION get_ovn (p_task_id IN NUMBER)
640 RETURN NUMBER
641 IS
642 CURSOR c_tasks_ovn (b_task_id NUMBER)
643 IS
644 SELECT object_version_number
645 FROM jtf_tasks_b
646 WHERE task_id = b_task_id;
647
648 l_object_version_number NUMBER;
649 BEGIN
650 OPEN c_tasks_ovn (p_task_id);
651 FETCH c_tasks_ovn into l_object_version_number;
652
653 IF c_tasks_ovn%NOTFOUND
654 THEN
655 CLOSE c_tasks_ovn;
656
657 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
658 fnd_msg_pub.add;
659
660 fnd_message.set_name('JTF', 'JTA_SYNC_TASK_OVN_NOTFOUND');
661 fnd_message.set_token('PROC_NAME','JTA_SYNC_TASK_COMMON.GET_OVN');
662 fnd_msg_pub.add;
663
664 raise_application_error (-20100,jta_sync_common.get_messages);
665 END IF;
666
667 CLOSE c_tasks_ovn;
668 RETURN l_object_version_number;
669 END get_ovn;
670
671 FUNCTION get_ovn (p_task_assignment_id IN NUMBER)
672 RETURN NUMBER
673 IS
674 CURSOR c_assignment_ovn (b_task_assignment_id NUMBER)
675 IS
676 SELECT object_version_number
677 FROM jtf_task_all_assignments
678 WHERE task_assignment_id = b_task_assignment_id;
679
680 l_object_version_number NUMBER;
681 BEGIN
682 OPEN c_assignment_ovn (p_task_assignment_id);
683 FETCH c_assignment_ovn into l_object_version_number;
684
685 IF c_assignment_ovn%NOTFOUND
686 THEN
687 CLOSE c_assignment_ovn;
688
689 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
690 fnd_msg_pub.add;
691
692 fnd_message.set_name('JTF', 'JTA_SYNC_ASSIGNMT_OVN_NOTFOUND');
693 fnd_message.set_token('PROC_NAME','JTA_SYNC_TASK_COMMON.GET_OVN');
694 fnd_msg_pub.add;
695
696 raise_application_error (-20100,jta_sync_common.get_messages);
697 END IF;
698
699 CLOSE c_assignment_ovn;
700 RETURN l_object_version_number;
701 END get_ovn;
702
703 PROCEDURE get_resource_details (
704 x_resource_id OUT NOCOPY NUMBER,
705 x_resource_type OUT NOCOPY VARCHAR2
706 )
707 IS
708 CURSOR c_resource
709 IS
710 SELECT resource_id, 'RS_' || category
711 FROM jtf_rs_resource_extns
712 WHERE user_id = fnd_global.user_id;
713 BEGIN
714 OPEN c_resource;
715 FETCH c_resource INTO x_resource_id, x_resource_type;
716
717 IF c_resource%NOTFOUND
718 THEN
719 CLOSE c_resource;
720
721 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
722 fnd_msg_pub.add;
723
724 fnd_message.set_name('JTF', 'JTA_SYNC_RESOURCE_NOTFOUND');
725 fnd_message.set_token('PROC_NAME','JTA_SYNC_TASK_COMMON.GET_RESOURCE_DETAILS');
726 fnd_msg_pub.add;
727
728 raise_application_error (-20100,jta_sync_common.get_messages);
729 END IF;
730
731 CLOSE c_resource;
732
733 END get_resource_details;
734
735 PROCEDURE do_mapping(p_task_id IN NUMBER,
736 p_operation IN VARCHAR2,
737 x_task_sync_id IN OUT NOCOPY NUMBER
738 )
739 IS
740 BEGIN
741 IF (p_operation = g_new)
742 THEN
743 IF x_task_sync_id IS NULL
744 THEN
745 SELECT jta_sync_task_mapping_s.nextval
746 INTO x_task_sync_id
747 FROM dual;
748 END IF;
749
750 jta_sync_task_map_pkg.insert_row (
751 p_task_sync_id => x_task_sync_id,
752 p_task_id => p_task_id,
753 p_resource_id => jta_sync_task.g_login_resource_id
754 );
755 ELSIF p_operation = g_modify
756 THEN
757 jta_sync_task_map_pkg.update_row (
758 p_task_sync_id => x_task_sync_id,
759 p_task_id => p_task_id,
760 p_resource_id => jta_sync_task.g_login_resource_id
761 );
762 /* ELSIF p_operation = G_DELETE
763 THEN
764 jta_sync_task_map_pkg.delete_row (
765 p_task_sync_id => x_task_sync_id
766 );*/
767
768 END IF;
769 END do_mapping;
770
771 /*PROCEDURE get_event_type (
772 p_deleted_flag IN VARCHAR2,
773 p_task_sync_id IN NUMBER,
774 p_source_object_type_code IN VARCHAR2,
775 p_calendar_start_date IN DATE,
776 p_calendar_end_date IN DATE,
777 p_assignment_status_id IN NUMBER,
778 x_operation OUT NOCOPY VARCHAR2
779 )
780 IS
781 l_deleted_flag VARCHAR2(1) := NVL (p_deleted_flag, 'N');
782 l_task_sync_id NUMBER := p_task_sync_id;
783 l_calendar_start_date DATE := p_calendar_start_date;
784 l_calendar_end_date DATE := p_calendar_end_date;
785 BEGIN
786 -- For task, we sync a task with the spanned day
787 -- For Appt, we don't a task with the spanned day
788 IF l_calendar_start_date IS NOT NULL AND
789 (l_calendar_end_date IS NULL OR
790 (p_source_object_type_code = G_APPOINTMENT AND
791 trunc(l_calendar_start_date) <> trunc(l_calendar_end_date))
792 )
793 THEN
794 IF l_task_sync_id IS NOT NULL
795 THEN
796 x_operation := G_DELETE;
797 END IF;
798 RETURN;
799 END IF;
800
801 IF l_task_sync_id IS NOT NULL
802 THEN
803 IF l_deleted_flag = 'Y' OR
804 p_assignment_status_id = 4 -- Rejected
805 THEN
806 x_operation := G_DELETE;
807 ELSE -- l_deleted_flag = 'N'
808 x_operation := G_MODIFY;
809 END IF;
810 ELSE -- l_task_sync_id IS NULL
811 IF l_deleted_flag = 'N' AND
812 nvl(p_assignment_status_id,-1) <> 4 -- Not Rejected
813 THEN
814 x_operation := G_NEW;
815 END IF;
816 END IF;
817 END get_event_type;
818 */
819 FUNCTION get_group_team_tasks (p_resource_id IN NUMBER)
820 RETURN resource_list_tbl
821 IS
822 CURSOR c_group_id (b_resource_id IN VARCHAR2)
823 IS
824 SELECT group_id resource_id
825 FROM jtf_rs_group_members
826 WHERE resource_id = b_resource_id
827 AND delete_flag <> 'Y';
828
829 CURSOR c_team_id (b_resource_id IN VARCHAR2)
830 IS
831 SELECT team_id resource_id
832 FROM jtf_rs_team_members
833 WHERE team_resource_id = b_resource_id
834 AND delete_flag <> 'Y';
835
836 l_group_resource_tbl resource_list_tbl;
837 i BINARY_INTEGER := 0;
838 BEGIN
839 FOR r_resources IN c_group_id (b_resource_id => p_resource_id)
840 LOOP
841 i := i + 1;
842 l_group_resource_tbl (i).resource_id := r_resources.resource_id;
843 l_group_resource_tbl (i).resource_type := 'RS_GROUP';
844 END LOOP;
845
846 FOR r_resources IN c_team_id (b_resource_id => p_resource_id)
847 LOOP
848 i := i + 1;
849 l_group_resource_tbl (i).resource_id := r_resources.resource_id;
850 l_group_resource_tbl (i).resource_type := 'RS_TEAM';
851 END LOOP;
852
853 RETURN l_group_resource_tbl;
854 END get_group_team_tasks;
855
856 FUNCTION get_group_calendar (p_resource_id IN NUMBER)
857 RETURN resource_list_tbl
858 IS
859 ------------------------------------------------------------------------------
860 -- This does not pick up the public calendar, pick up only group calendar
861 ------------------------------------------------------------------------------
862 CURSOR c_group_calendar (b_resource_id IN VARCHAR2)
863 IS
864 SELECT DISTINCT fgs.instance_pk1_value resource_id,
865 fgs.instance_pk2_value resource_type
866 FROM fnd_grants fgs,
867 fnd_menus fmu,
868 fnd_objects fos,
869 jtf_rs_group_usages jru,
870 jtf_rs_groups_tl jrt
871 WHERE fgs.object_id = fos.object_id -- grants joint to object
872 AND fgs.menu_id = fmu.menu_id -- grants joint to menus
873 AND fos.obj_name = 'JTF_TASK_RESOURCE'
874 AND fgs.grantee_key = b_resource_id
875 AND fgs.grantee_type = 'USER'
876 AND fgs.start_date < SYSDATE
877 AND ( fgs.end_date >= SYSDATE
878 OR fgs.end_date IS NULL)
879 AND fgs.instance_pk2_value = 'RS_GROUP'
880 AND jrt.group_id = TO_NUMBER (fgs.instance_pk1_value)
881 AND jrt.language = USERENV ('LANG')
882 AND jru.group_id = jrt.group_id
883 AND jru.usage = 'GROUP_CALENDAR';
884
885 l_group_resource_tbl resource_list_tbl;
886 i BINARY_INTEGER := 0;
887 BEGIN
888 FOR r_resources IN c_group_calendar (b_resource_id => p_resource_id)
889 LOOP
890 i := i + 1;
891 l_group_resource_tbl (i).resource_id := r_resources.resource_id;
892 l_group_resource_tbl (i).resource_type := r_resources.resource_type;
893 END LOOP; --r_resources
894
895 RETURN l_group_resource_tbl;
896 END get_group_calendar;
897
898 PROCEDURE get_group_resource (
899 p_request_type IN VARCHAR2,
900 p_resource_id IN NUMBER,
901 p_resource_type IN VARCHAR2,
902 x_resources OUT NOCOPY resource_list_tbl
903 )
904 IS
905 res_index BINARY_INTEGER;
906 BEGIN
907 IF p_request_type = G_REQ_APPOINTMENT
908 THEN
909 x_resources := get_group_calendar (p_resource_id => p_resource_id);
910 ELSIF p_request_type = G_REQ_TASK
911 THEN
912 x_resources := get_group_team_tasks (p_resource_id => p_resource_id);
913 END IF;
914
915 res_index := NVL (x_resources.LAST, 0) + 1;
916 x_resources (res_index).resource_id := p_resource_id;
917 x_resources (res_index).resource_type := p_resource_type;
918 END get_group_resource;
919
920 PROCEDURE get_alarm_mins (
921 p_task_rec IN jta_sync_task.task_rec,
922 x_alarm_mins OUT NOCOPY NUMBER
923 )
924 IS
925 l_alarm_days NUMBER;
926 BEGIN
927 IF (p_task_rec.objectcode = G_APPOINTMENT)
928 THEN
929 IF (p_task_rec.alarmflag = 'Y')
930 THEN
931 l_alarm_days :=
932 p_task_rec.plannedstartdate - p_task_rec.alarmdate;
933 x_alarm_mins := ROUND (l_alarm_days * 1440, 0);
934 ELSE
935 x_alarm_mins := NULL;
936 END IF;
937 ELSE
938 x_alarm_mins := NULL;
939 END IF;
940 END get_alarm_mins;
941
942 FUNCTION convert_gmt_to_client (p_date IN DATE)
943 RETURN DATE
944 IS
945 l_date DATE;
946 BEGIN
947 jtf_cal_utility_pvt.adjustfortimezone (
948 g_gmt_timezone_id,
949 NVL (g_client_timezone_id, g_server_timezone_id),
950 p_date,
951 l_date
952 );
953 RETURN l_date;
954 END;
955
956 FUNCTION convert_task_to_gmt (p_date IN DATE, p_timezone_id IN NUMBER)
957 RETURN DATE
958 IS
959 l_date DATE;
960 BEGIN
961 jtf_cal_utility_pvt.adjustfortimezone (
962 p_timezone_id,
963 g_gmt_timezone_id,
964 p_date,
965 l_date
966 );
967 RETURN l_date;
968 END convert_task_to_gmt;
969
970 FUNCTION convert_server_to_gmt (p_date IN DATE)
971 RETURN DATE
972 IS
973 l_date DATE;
974 BEGIN
975 jtf_cal_utility_pvt.adjustfortimezone (
976 g_server_timezone_id,
977 g_gmt_timezone_id,
978 p_date,
979 l_date
980 );
981 RETURN l_date;
982 END convert_server_to_gmt;
983
984 FUNCTION convert_gmt_to_task (p_date IN DATE, p_task_id IN NUMBER)
985 RETURN DATE
986 IS
987 l_date DATE;
988 l_task_timezone_id NUMBER;
989 BEGIN
990 l_task_timezone_id := get_task_timezone_id (p_task_id);
991
992 IF l_task_timezone_id <> g_gmt_timezone_id
993 THEN
994 jtf_cal_utility_pvt.adjustfortimezone (
995 g_gmt_timezone_id,
996 l_task_timezone_id,
997 p_date,
998 l_date
999 );
1000 ELSE
1001 l_date := p_date;
1002 END IF;
1003
1004 RETURN l_date;
1005 END convert_gmt_to_task;
1006
1007 FUNCTION convert_gmt_to_server (p_date IN DATE)
1008 RETURN DATE
1009 IS
1010 l_date DATE;
1011 BEGIN
1012 jtf_cal_utility_pvt.adjustfortimezone (
1013 g_gmt_timezone_id,
1014 g_server_timezone_id,
1015 p_date,
1016 l_date
1017 );
1018 RETURN l_date;
1019 END convert_gmt_to_server;
1020
1021 PROCEDURE convert_dates (
1022 p_task_rec IN jta_sync_task.task_rec,
1023 p_operation IN VARCHAR2, --CREATE OR UPDATE
1024 x_planned_start OUT NOCOPY DATE,
1025 x_planned_end OUT NOCOPY DATE,
1026 x_scheduled_start OUT NOCOPY DATE,
1027 x_scheduled_end OUT NOCOPY DATE,
1028 x_actual_start OUT NOCOPY DATE,
1029 x_actual_end OUT NOCOPY DATE,
1030 x_date_selected OUT NOCOPY VARCHAR2,
1031 x_show_on_calendar OUT NOCOPY VARCHAR2
1032 )
1033 IS
1034 l_task_id NUMBER;
1035 BEGIN
1036 -- If it's All Day APMT, do not convert the dates
1037 IF (p_task_rec.plannedstartdate = p_task_rec.plannedenddate AND
1038 TRUNC(p_task_rec.plannedstartdate) = p_task_rec.plannedstartdate) AND
1039 p_task_rec.objectcode = G_APPOINTMENT
1040 THEN
1041 x_planned_start := p_task_rec.plannedstartdate;
1042 x_planned_end := p_task_rec.plannedenddate;
1043
1044 -- This is not all day appointment
1045 ELSE
1046 IF p_operation = 'CREATE'
1047 THEN
1048 IF p_task_rec.objectcode = G_APPOINTMENT
1049 THEN
1050 x_planned_start := convert_gmt_to_client(p_task_rec.plannedstartdate);
1051 x_planned_end := convert_gmt_to_client(p_task_rec.plannedenddate);
1052 ELSE
1053 -- for create task don't do timezone conversion, it's untimed
1054 x_planned_start := p_task_rec.plannedstartdate;
1055 x_planned_end := p_task_rec.plannedenddate;
1056 x_scheduled_start := p_task_rec.scheduledstartdate;
1057 x_scheduled_end := p_task_rec.scheduledenddate;
1058 x_actual_start := p_task_rec.actualstartdate;
1059 x_actual_end := p_task_rec.actualenddate;
1060 END IF;
1061 ELSIF p_operation = 'UPDATE'
1062 THEN
1063 l_task_id := get_task_id (p_sync_id => p_task_rec.syncid);
1064 x_planned_start := convert_gmt_to_task (p_task_rec.plannedstartdate, l_task_id);
1065 x_planned_end := convert_gmt_to_task (p_task_rec.plannedenddate, l_task_id);
1066 x_scheduled_start := convert_gmt_to_task (p_task_rec.scheduledstartdate, l_task_id);
1067 x_scheduled_end := convert_gmt_to_task (p_task_rec.scheduledenddate, l_task_id);
1068 x_actual_start := convert_gmt_to_task (p_task_rec.actualstartdate, l_task_id);
1069 x_actual_end := convert_gmt_to_task (p_task_rec.actualenddate, l_task_id);
1070 END IF; -- end-if operation
1071 END IF; -- end if-all day appt
1072
1073 END convert_dates;
1074
1075 PROCEDURE adjust_timezone (
1076 p_timezone_id IN NUMBER,
1077 p_syncanchor IN DATE,
1078 p_planned_start_date IN DATE,
1079 p_planned_end_date IN DATE,
1080 p_scheduled_start_date IN DATE,
1081 p_scheduled_end_date IN DATE,
1082 p_actual_start_date IN DATE,
1083 p_actual_end_date IN DATE,
1084 p_item_display_type IN NUMBER,
1085 x_task_rec IN OUT NOCOPY jta_sync_task.task_rec
1086 )
1087 IS
1088 BEGIN
1089
1090 -------------------------------------------------------------
1091 -- Decide new syncAnchor and Convert server to GMT timezone
1092 x_task_rec.syncanchor := convert_server_to_gmt (p_syncanchor);
1093
1094 IF p_item_display_type = 3 AND x_task_rec.objectcode = G_APPOINTMENT THEN
1095 x_task_rec.plannedstartdate := p_planned_start_date;
1096 x_task_rec.plannedenddate := p_planned_end_date;
1097 ELSE
1098 x_task_rec.plannedstartdate := convert_task_to_gmt (p_planned_start_date, p_timezone_id);
1099 x_task_rec.plannedenddate := convert_task_to_gmt (p_planned_end_date, p_timezone_id);
1100 x_task_rec.scheduledstartdate := convert_task_to_gmt (p_scheduled_start_date, p_timezone_id);
1101 x_task_rec.scheduledenddate := convert_task_to_gmt (p_scheduled_end_date, p_timezone_id);
1102 x_task_rec.actualstartdate := convert_task_to_gmt (p_actual_start_date, p_timezone_id);
1103 x_task_rec.actualenddate := convert_task_to_gmt (p_actual_end_date, p_timezone_id);
1104
1105 END IF;
1106
1107 END adjust_timezone;
1108
1109 FUNCTION get_max_enddate (p_recurrence_rule_id IN NUMBER)
1110 RETURN DATE
1111 IS
1112 CURSOR c_recur_tasks
1113 IS
1114 SELECT MAX (calendar_end_date)
1115 FROM jtf_tasks_b
1116 WHERE recurrence_rule_id = p_recurrence_rule_id;
1117
1118 l_date DATE;
1119 BEGIN
1120 OPEN c_recur_tasks;
1121 FETCH c_recur_tasks into l_date;
1122
1123 IF c_recur_tasks%NOTFOUND
1124 THEN
1125 CLOSE c_recur_tasks;
1126
1127 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
1128 fnd_msg_pub.add;
1129
1130 fnd_message.set_name('JTF', 'JTA_SYNC_INVALID_RECUR_RULE_ID');
1131 fnd_message.set_token('PROC_NAME','JTA_SYNC_TASK_COMMON.GET_MAX_ENDDATE');
1132 fnd_msg_pub.add;
1133
1134 raise_application_error (-20100,jta_sync_common.get_messages);
1135 END IF;
1136
1137 CLOSE c_recur_tasks;
1138
1139 IF l_date IS NULL
1140 THEN
1141 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
1142 fnd_msg_pub.add;
1143
1144 fnd_message.set_name('JTF', 'JTA_SYNC_NULL_CALENDAR_ENDDATE');
1145 fnd_message.set_token('PROC_NAME','JTA_SYNC_TASK_COMMON.GET_MAX_ENDDATE');
1146 fnd_msg_pub.add;
1147
1148 raise_application_error (-20100,jta_sync_common.get_messages);
1149 END IF;
1150
1151 RETURN l_date;
1152 END get_max_enddate;
1153
1154 procedure get_exclusion_data (p_recurrence_rule_id IN NUMBER, p_exclusion_data in out NOCOPY jta_sync_task.exclusion_tbl,
1155
1156 p_task_sync_id in number )
1157 --- RETURN jta_sync_task.exclusion_tbl
1158 IS
1159 CURSOR c_exclusion
1160 IS
1161 SELECT DISTINCT exclusion_date ex_date
1162 FROM jta_task_exclusions
1163 WHERE recurrence_rule_id = p_recurrence_rule_id;
1164
1165 l_date DATE;
1166 --- l_exclusion_data jta_sync_task.exclusion_tbl;
1167 l_exclusion c_exclusion%ROWTYPE;
1168 i BINARY_INTEGER := nvl(p_exclusion_data.last,0) ;
1169 BEGIN
1170 FOR l_exclusion IN c_exclusion
1171 LOOP
1172 i := i + 1;
1173 p_exclusion_data (i).exclusion_date := l_exclusion.ex_date;
1174 p_exclusion_data (i).syncid := p_task_sync_id;
1175 END LOOP;
1176
1177 --- RETURN l_exclusion_data;
1178 END get_exclusion_data;
1179
1180 FUNCTION already_selected(p_task_id IN NUMBER
1181 ,p_sync_id IN NUMBER
1182 ,p_task_tbl IN jta_sync_task.task_tbl)
1183 RETURN BOOLEAN
1184 IS
1185 l_selected BOOLEAN := FALSE;
1186 BEGIN
1187 IF p_task_tbl.COUNT > 0
1188 THEN
1189 FOR i IN p_task_tbl.FIRST..p_task_tbl.LAST
1190 LOOP
1191 IF p_task_id IS NOT NULL
1192 THEN
1193 IF p_task_tbl(i).task_id = p_task_id
1194 THEN
1195 l_selected := TRUE;
1196 EXIT;
1197 END IF;
1198 ELSIF p_sync_id IS NOT NULL
1199 THEN
1200 IF p_task_tbl(i).syncid = p_sync_id
1201 THEN
1202 l_selected := TRUE;
1203 EXIT;
1204 END IF;
1205 ELSE
1206 EXIT;
1207 END IF;
1208 END LOOP;
1209 END IF;
1210
1211 RETURN l_selected;
1212
1213 END already_selected;
1214
1215 PROCEDURE add_task (
1216 p_request_type IN VARCHAR2,
1217 p_resource_id IN NUMBER,
1218 p_resource_type IN VARCHAR2,
1219 p_recordindex IN NUMBER,
1220 p_operation IN VARCHAR2,
1221 p_task_sync_id IN NUMBER,
1222 p_task_id IN NUMBER,
1223 p_task_name IN VARCHAR2,
1224 p_owner_type_code IN VARCHAR2,
1225 p_description IN VARCHAR2,
1226 p_task_status_id IN NUMBER,
1227 p_task_priority_id IN NUMBER,
1228 p_private_flag IN VARCHAR2,
1229 p_date_selected IN VARCHAR2,
1230 p_timezone_id IN NUMBER,
1231 p_syncanchor IN DATE,
1232 p_planned_start_date IN DATE,
1233 p_planned_end_date IN DATE,
1234 p_scheduled_start_date IN DATE,
1235 p_scheduled_end_date IN DATE,
1236 p_actual_start_date IN DATE,
1237 p_actual_end_date IN DATE,
1238 p_calendar_start_date IN DATE,
1239 p_calendar_end_date IN DATE,
1240 p_alarm_on IN VARCHAR2,
1241 p_alarm_start IN NUMBER,
1242 p_recurrence_rule_id IN NUMBER,
1243 p_occurs_uom IN VARCHAR2,
1244 p_occurs_every IN NUMBER,
1245 p_occurs_number IN NUMBER,
1246 p_start_date_active IN DATE,
1247 p_end_date_active IN DATE,
1248 p_sunday IN VARCHAR2,
1249 p_monday IN VARCHAR2,
1250 p_tuesday IN VARCHAR2,
1251 p_wednesday IN VARCHAR2,
1252 p_thursday IN VARCHAR2,
1253 p_friday IN VARCHAR2,
1254 p_saturday IN VARCHAR2,
1255 p_date_of_month IN VARCHAR2,
1256 p_occurs_which IN VARCHAR2,
1257 --p_get_data IN BOOLEAN,
1258 x_task_rec IN OUT NOCOPY jta_sync_task.task_rec
1259 )
1260 IS
1261 l_category_name VARCHAR2(240); -- Fix bug 2540722
1262 l_status BOOLEAN;
1263 l_operation VARCHAR2(20);
1264 l_task_status_id number ;
1265 l_item_display_type NUMBER;
1266 l_category_id NUMBER;
1267 BEGIN
1268 l_operation := p_operation;
1269 x_task_rec.syncid := p_task_sync_id;
1270
1271 x_task_rec.resultid := 0;
1272 x_task_rec.objectcode := RTRIM (p_request_type, 'S');
1273
1274
1275 -- item display type equals 3 for all items shown on top of daily view
1276 --l_item_display_type := jtf_cal_utility_pvt.getItemType
1277 -- ( p_SourceCode => 'TASK'
1278 -- , p_PeriodStartDate => null
1279 -- , p_PeriodEndDate => null
1280 -- , p_StartDate => p_calendar_start_date
1281 -- , p_EndDate => p_calendar_end_date
1282 -- , p_CalSpanDaysProfile => fnd_profile.value('JTF_CAL_SPAN_DAYS')
1283 -- );
1284 l_item_display_type := 1;
1285
1286 adjust_timezone (
1287 p_timezone_id => p_timezone_id,
1288 p_syncanchor => p_syncanchor,
1289 p_planned_start_date => p_planned_start_date,
1290 p_planned_end_date => p_planned_end_date,
1291 p_scheduled_start_date => p_scheduled_start_date,
1292 p_scheduled_end_date => p_scheduled_end_date,
1293 p_actual_start_date => p_actual_start_date,
1294 p_actual_end_date => p_actual_end_date,
1295 p_item_display_type => l_item_display_type,
1296 x_task_rec => x_task_rec
1297 );
1298
1299 do_mapping (
1300 p_task_id,
1301 p_operation,
1302 x_task_rec.syncid
1303 );
1304
1305 -- change status
1306 l_task_status_id := p_task_status_id;
1307
1308 IF (x_task_rec.objectcode <> G_APPOINTMENT)
1309 THEN
1310 transformstatus (
1311 p_task_status_id => l_task_status_id,
1312 p_task_sync_id => x_task_rec.syncId,
1313 x_operation => l_operation
1314 ) ;
1315 END IF;
1316
1317 x_task_rec.recordindex := p_recordindex;
1318 x_task_rec.eventtype := p_operation;
1319 x_task_rec.subject := convert_carriage_return(p_task_name,'XML');
1320 x_task_rec.task_id := p_task_id;
1321
1322 IF p_operation <> G_DELETE
1323 THEN
1324 make_prefix (
1325 p_assignment_status_id => get_assignment_status_id (p_task_id, p_resource_id),
1326 p_source_object_type_code => x_task_rec.objectcode,
1327 p_resource_type => p_owner_type_code,
1328 p_resource_id => jta_sync_task.g_login_resource_id,
1329 p_group_id => p_resource_id,
1330 x_subject => x_task_rec.subject
1331 );
1332 END IF;
1333
1334 x_task_rec.description := p_description;
1335 x_task_rec.statusid := l_task_status_id;
1336 x_task_rec.priorityid := get_client_priority(p_task_priority_id);
1337 x_task_rec.alarmflag := p_alarm_on;
1338 x_task_rec.privateflag := p_private_flag;
1339 x_task_rec.dateselected := NVL(p_date_selected,'S'); -- fix bug 2389092
1340
1341 x_task_rec.resultsystemmessage := NULL;
1342 x_task_rec.resultusermessage := NULL;
1343
1344 -- For fix bug 2540722
1345 l_category_id := jtf_task_security_pvt.get_category_id(
1346 p_task_id => p_task_id,
1347 p_resource_id => p_resource_id,
1348 p_resource_type_code => p_resource_type
1349 );
1350 IF l_category_id IS NOT NULL
1351 THEN
1352 l_category_name := substr(jtf_task_utl.get_category_name(l_category_id), 1, 240);
1353 END IF;
1354 x_task_rec.category := l_category_name;
1355
1356 x_task_rec.alarmdate := set_alarm_date (
1357 p_task_id => p_task_id,
1358 p_request_type => p_request_type,
1359 p_scheduled_start_date => x_task_rec.scheduledstartdate,
1360 p_planned_start_date => x_task_rec.plannedstartdate,
1361 p_actual_start_date => x_task_rec.actualstartdate,
1362 p_alarm_flag => p_alarm_on,
1363 p_alarm_start => p_alarm_start
1364 );
1365
1366 ----------------------------------------------------------
1367 -- Repeating data
1368 ----------------------------------------------------------
1369 IF p_recurrence_rule_id IS NOT NULL
1370 THEN
1371 x_task_rec.unit_of_measure := p_occurs_uom;
1372 x_task_rec.occurs_every := p_occurs_every;
1373 --x_task_rec.occurs_number := p_occurs_number;
1374 x_task_rec.start_date := p_start_date_active;
1375 x_task_rec.end_date := NVL (p_end_date_active,
1376 get_max_enddate (p_recurrence_rule_id)
1377 );
1378 x_task_rec.sunday := p_sunday;
1379 x_task_rec.monday := p_monday;
1380 x_task_rec.tuesday := p_tuesday;
1381 x_task_rec.wednesday := p_wednesday;
1382 x_task_rec.thursday := p_thursday;
1383 x_task_rec.friday := p_friday;
1384 x_task_rec.saturday := p_saturday;
1385 x_task_rec.date_of_month := p_date_of_month;
1386 x_task_rec.occurs_which := p_occurs_which;
1387
1388 convert_recur_date_to_gmt (
1389 p_timezone_id => p_timezone_id,
1390 p_base_start_date => p_planned_start_date,
1391 p_base_end_date => p_planned_end_date,
1392 p_start_date => x_task_rec.start_date,
1393 p_end_date => x_task_rec.end_date,
1394 p_item_display_type => l_item_display_type,
1395 p_occurs_which => p_occurs_which,
1396 p_uom => p_occurs_uom,
1397 x_date_of_month => x_task_rec.date_of_month,
1398 x_start_date => x_task_rec.start_date,
1399 x_end_date => x_task_rec.end_date
1400 );
1401 END IF;
1402
1403 END add_task;
1404
1405
1406 FUNCTION get_client_priority (p_importance_level IN NUMBER)
1407 RETURN NUMBER
1408 IS
1409 l_priority_id NUMBER;
1410 BEGIN
1411 IF p_importance_level <= 2 -- Critical(1), High(1)
1412 THEN
1413 l_priority_id := 2;
1414 ELSIF p_importance_level = 3 -- Medium, Standard
1415 THEN
1416 l_priority_id := 3;
1417 ELSIF p_importance_level >= 4 -- Low, Optional(5)
1418 THEN
1419 l_priority_id := 4;
1420 ELSE
1421 l_priority_id := NULL;
1422 END IF;
1423
1424 RETURN l_priority_id;
1425 END get_client_priority;
1426
1427 PROCEDURE make_prefix (
1428 p_assignment_status_id IN NUMBER,
1429 p_source_object_type_code IN VARCHAR2,
1430 p_resource_type IN VARCHAR2,
1431 p_resource_id IN NUMBER,
1432 p_group_id IN NUMBER,
1433 x_subject IN OUT NOCOPY VARCHAR2
1434 )
1435 IS
1436 l_prefix VARCHAR2(100);
1437 BEGIN
1438
1439 -- This is appending the prefix 'INVITEE: '
1440 IF p_source_object_type_code = G_APPOINTMENT AND
1441 p_resource_type <> 'RS_GROUP' AND
1442 p_assignment_status_id = 18
1443 THEN
1444 x_subject := g_prefix_invitee || x_subject;
1445
1446 -- This is appending the prefix of the group
1447 ELSIF p_source_object_type_code = G_APPOINTMENT AND
1448 p_resource_type = 'RS_GROUP'
1449 THEN
1450 l_prefix := jtf_cal_utility_pvt.GetGroupPrefix(p_ResourceID => p_resource_id
1451 ,p_ResourceType => p_resource_type
1452 ,p_GroupID => p_group_id);
1453 IF l_prefix IS NOT NULL
1454 THEN
1455 x_subject := l_prefix || x_subject;
1456 END IF;
1457 END IF;
1458
1459 END make_prefix;
1460
1461 -- check if the user is assigne then set status to rejected
1462 -- and set delete flag to false this rec can not be deleted
1463 -- else set delete flag to true this rec can be delted
1464 PROCEDURE check_delete_data (
1465 p_task_id IN NUMBER,
1466 p_resource_id IN NUMBER,
1467 p_objectcode IN VARCHAR2,
1468 x_status_id OUT NOCOPY NUMBER,
1469 x_delete_flag OUT NOCOPY VARCHAR2
1470 )
1471 IS
1472 l_assignee_role VARCHAR2(30);
1473 l_assignment_status_id NUMBER;
1474 BEGIN
1475 IF (p_objectcode = G_APPOINTMENT)
1476 THEN
1477 get_assignment_info (
1478 p_task_id => p_task_id,
1479 p_resource_id => p_resource_id,
1480 x_assignee_role => l_assignee_role,
1481 x_assignment_status_id => l_assignment_status_id
1482 );
1483
1484 IF (l_assignee_role = 'ASSIGNEE')
1485 THEN
1486 x_status_id := 4; --rejected
1487 x_delete_flag := 'U'; -- UPDATE
1488 ELSIF l_assignee_role = 'GROUP'
1489 THEN
1490 x_delete_flag := 'X'; -- DO NOTHING
1491 ELSIF (l_assignee_role = 'OWNER')
1492 THEN
1493 x_delete_flag := 'D'; -- DELETE
1494 END IF;
1495 ELSE -- p_objectcode = G_TASK
1496 x_delete_flag := 'D';
1497 END IF; -- p_objectcode = G_APPOINTMENT
1498 END check_delete_data;
1499
1500 FUNCTION get_assignment_id (p_task_id IN NUMBER
1501 , p_resource_id IN NUMBER
1502 , p_resource_type IN VARCHAR2
1503 )
1504 RETURN NUMBER
1505 IS
1506 CURSOR c_assignment
1507 IS
1508 SELECT task_assignment_id
1509 FROM jtf_task_all_assignments
1510 WHERE task_id = p_task_id
1511 AND resource_id = p_resource_id
1512 AND resource_type_code = p_resource_type;
1513
1514 l_task_assignment_id NUMBER;
1515 BEGIN
1516 OPEN c_assignment;
1517 FETCH c_assignment into l_task_assignment_id;
1518
1519 IF c_assignment%NOTFOUND
1520 THEN
1521 CLOSE c_assignment;
1522
1523 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
1524 fnd_msg_pub.add;
1525
1526 fnd_message.set_name('JTF', 'JTA_SYNC_ASSIGNMENT_NOTFOUND');
1527 fnd_message.set_token('PROC_NAME','JTA_SYNC_TASK_COMMON.GET_ASSIGNMENT_ID');
1528 fnd_msg_pub.add;
1529
1530 raise_application_error (-20100,jta_sync_common.get_messages);
1531 END IF;
1532
1533 CLOSE c_assignment;
1534 RETURN l_task_assignment_id;
1535 END get_assignment_id;
1536
1537 FUNCTION get_assignment_status_id (
1538 p_task_id IN NUMBER,
1539 p_resource_id IN NUMBER
1540 )
1541 RETURN NUMBER
1542 IS
1543 CURSOR c_assignment
1544 IS
1545 SELECT assignment_status_id
1546 FROM jtf_task_all_assignments
1547 WHERE task_id = p_task_id
1548 AND resource_id = p_resource_id;
1549
1550 l_assignment_status_id NUMBER;
1551 BEGIN
1552 OPEN c_assignment;
1553 FETCH c_assignment into l_assignment_status_id;
1554
1555 IF c_assignment%NOTFOUND
1556 THEN
1557 CLOSE c_assignment;
1558
1559 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
1560 fnd_msg_pub.add;
1561
1562 fnd_message.set_name('JTF', 'JTA_SYNC_ASSIGN_STSID_NOTFOUND');
1563 fnd_message.set_token('PROC_NAME','JTA_SYNC_TASK_COMMON.GET_ASSIGNMENT_STATUS_ID');
1564 fnd_msg_pub.add;
1565
1566 raise_application_error (-20100,jta_sync_common.get_messages);
1567 END IF;
1568
1569 CLOSE c_assignment;
1570 RETURN l_assignment_status_id;
1571 END get_assignment_status_id;
1572
1573 PROCEDURE get_owner_info (
1574 p_task_id IN NUMBER,
1575 x_task_name OUT NOCOPY VARCHAR2,
1576 x_owner_id OUT NOCOPY NUMBER,
1577 x_owner_type_code OUT NOCOPY VARCHAR2
1578 )
1579 IS
1580 CURSOR c_task (b_task_id NUMBER)
1581 IS
1582 SELECT task_name, owner_id, owner_type_code
1583 FROM jtf_tasks_vl
1584 WHERE task_id = b_task_id;
1585
1586 rec_task c_task%ROWTYPE;
1587 BEGIN
1588 OPEN c_task (p_task_id);
1589 FETCH c_task INTO rec_task;
1590
1591 IF c_task%NOTFOUND
1592 THEN
1593 CLOSE c_task;
1594
1595 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
1596 fnd_msg_pub.add;
1597
1598 fnd_message.set_name('JTF', 'JTA_SYNC_NOTFOUND_TASKID');
1599 fnd_message.set_token('PROC_NAME','JTA_SYNC_TASK_COMMON.GET_OWNER_INFO');
1600 fnd_msg_pub.add;
1601
1602 raise_application_error (-20100,jta_sync_common.get_messages);
1603 END IF;
1604
1605 CLOSE c_task;
1606 x_task_name := rec_task.task_name;
1607 x_owner_id := rec_task.owner_id;
1608 x_owner_type_code := rec_task.owner_type_code;
1609 END get_owner_info;
1610
1611 PROCEDURE get_assignment_info (
1612 p_task_id IN NUMBER,
1613 p_resource_id IN NUMBER,
1614 x_assignee_role OUT NOCOPY VARCHAR2,
1615 x_assignment_status_id OUT NOCOPY NUMBER
1616 )
1617 IS
1618 CURSOR c_assignment (b_task_id NUMBER, b_resource_id NUMBER)
1619 IS
1620 SELECT a.assignee_role, a.assignment_status_id, r.resource_id
1621 FROM jtf_rs_resource_extns r, jtf_task_all_assignments a
1622 WHERE a.task_id = b_task_id
1623 AND a.resource_id = b_resource_id
1624 AND r.user_id = a.created_by;
1625
1626 l_assignee_role VARCHAR2(30);
1627 l_assignment_status_id NUMBER;
1628 l_task_name VARCHAR2(80);
1629 l_owner_id NUMBER;
1630 l_owner_type_code VARCHAR2(30);
1631 l_creator_resource_id NUMBER;
1632 BEGIN
1633 get_owner_info (
1634 p_task_id => p_task_id,
1635 x_task_name => l_task_name,
1636 x_owner_id => l_owner_id,
1637 x_owner_type_code => l_owner_type_code
1638 );
1639
1640 IF l_owner_type_code = 'RS_GROUP'
1641 THEN
1642 OPEN c_assignment (
1643 b_task_id => p_task_id,
1644 b_resource_id => l_owner_id
1645 );
1646 ELSE
1647 OPEN c_assignment (
1648 b_task_id => p_task_id,
1649 b_resource_id => p_resource_id
1650 );
1651 END IF;
1652
1653 FETCH c_assignment into l_assignee_role, l_assignment_status_id, l_creator_resource_id;
1654
1655 IF c_assignment%NOTFOUND
1656 THEN
1657 CLOSE c_assignment;
1658
1659 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
1660 fnd_msg_pub.add;
1661
1662 fnd_message.set_name('JTF', 'JTA_SYNC_ASSIGNMENT_NOTFOUND');
1663 fnd_message.set_token('PROC_NAME','JTA_SYNC_TASK_COMMON.GET_ASSIGNMENT_INFO');
1664 fnd_msg_pub.add;
1665
1666 raise_application_error (-20100,jta_sync_common.get_messages);
1667 END IF;
1668
1669 CLOSE c_assignment;
1670
1671 IF l_owner_type_code = 'RS_GROUP'
1672 -- AND l_creator_resource_id <> p_resource_id
1673
1674 THEN
1675 x_assignee_role := 'GROUP';
1676 x_assignment_status_id := NULL;
1677 ELSE
1678 x_assignee_role := l_assignee_role;
1679 x_assignment_status_id := l_assignment_status_id;
1680 END IF;
1681 END get_assignment_info;
1682
1683 FUNCTION get_access (p_group_id IN VARCHAR2, p_resource_id IN NUMBER)
1684 RETURN VARCHAR2
1685 IS
1686 -- 1) JTF_CAL_FULL_ACCESS
1687 -- 2) JTF_CAL_ADMIN_ACCESS
1688 -- 3) JTF_CAL_READ_ACCESS
1689 CURSOR c_access (b_group_id VARCHAR2, b_resource_id VARCHAR2)
1690 IS
1691 SELECT DISTINCT fmu.menu_name
1692 FROM fnd_menus fmu, fnd_objects fos, fnd_grants fgs
1693 WHERE fmu.menu_id = fgs.menu_id -- grants joint to menus
1694 AND fos.obj_name = 'JTF_TASK_RESOURCE'
1695 AND fos.object_id = fgs.object_id -- grants joint to object
1696 AND fgs.grantee_key = b_resource_id
1697 AND fgs.grantee_type = 'USER'
1698 AND fgs.start_date < SYSDATE
1699 AND ( fgs.end_date >= SYSDATE
1700 OR fgs.end_date IS NULL)
1701 AND fgs.instance_pk2_value = 'RS_GROUP'
1702 AND fgs.instance_pk1_value = b_group_id;
1703
1704 l_menu_name fnd_menus.menu_name%TYPE;
1705 BEGIN
1706 OPEN c_access (b_group_id => p_group_id, b_resource_id => p_resource_id);
1707 FETCH c_access into l_menu_name;
1708
1709 IF c_access%NOTFOUND
1710 THEN
1711 CLOSE c_access;
1712
1713 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
1714 fnd_msg_pub.add;
1715
1716 fnd_message.set_name('JTF', 'JTA_SYNC_ACCESS_PRIV_NOTFOUND');
1717 fnd_message.set_token('PROC_NAME','JTA_SYNC_TASK_COMMON.GET_ACCESS');
1718 fnd_msg_pub.add;
1719
1720 raise_application_error (-20100,jta_sync_common.get_messages);
1721 END IF;
1722
1723 CLOSE c_access;
1724 RETURN l_menu_name;
1725 END get_access;
1726
1727 FUNCTION get_source_object_type (p_task_id IN NUMBER)
1728 RETURN VARCHAR2
1729 IS
1730 CURSOR c_source (b_task_id NUMBER)
1731 IS
1732 SELECT source_object_type_code
1733 FROM jtf_tasks_b
1734 WHERE task_id = b_task_id;
1735
1736 l_source_object_type_code VARCHAR2(60);
1737 BEGIN
1738 OPEN c_source (b_task_id => p_task_id);
1739 FETCH c_source into l_source_object_type_code;
1740
1741 IF c_source%NOTFOUND
1742 THEN
1743 CLOSE c_source;
1744
1745 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
1746 fnd_msg_pub.add;
1747
1748 fnd_message.set_name('JTF', 'JTA_SYNC_NOTFOUND_TASKID');
1749 fnd_message.set_token('PROC_NAME','JTA_SYNC_TASK_COMMON.GET_SOURCE_OBJECT_TYPE');
1750 fnd_msg_pub.add;
1751
1752 raise_application_error (-20100,jta_sync_common.get_messages);
1753 END IF;
1754
1755 CLOSE c_source;
1756 RETURN l_source_object_type_code;
1757 END get_source_object_type;
1758
1759 -- Added for fix bug 2482833
1760 PROCEDURE get_sync_info (p_task_id IN NUMBER,
1761 p_resource_id IN NUMBER,
1762 x_assignee_role OUT NOCOPY VARCHAR2,
1763 x_resource_type OUT NOCOPY VARCHAR2,
1764 x_group_calendar_flag OUT NOCOPY VARCHAR2,
1765 x_assignment_status_id OUT NOCOPY NUMBER,
1766 x_source_object_type_code OUT NOCOPY VARCHAR2)
1767 IS
1768 CURSOR c_resource IS
1769 SELECT asg.assignee_role
1770 , rs.resource_type_code
1771 , rs.group_calendar_flag
1772 , asg.assignment_status_id
1773 , tsk.source_object_type_code
1774 FROM (SELECT p_resource_id resource_id
1775 , 'RS_EMPLOYEE' resource_type_code
1776 , 'N' group_calendar_flag
1777 FROM dual
1778 UNION ALL
1779 SELECT tm.team_id resource_id
1780 , 'RS_TEAM' resource_type_code
1781 , 'N' group_calendar_flag
1782 FROM jtf_rs_team_members tm
1783 WHERE tm.team_resource_id = p_resource_id
1784 UNION ALL
1785 SELECT gm.group_id resource_id
1786 , 'RS_GROUP' resource_type_code
1787 , 'N' group_calendar_flag
1788 FROM jtf_rs_group_members gm
1789 WHERE gm.resource_id = p_resource_id
1790 UNION ALL
1791 SELECT g.group_id resource_id
1792 , 'RS_GROUP' resource_type_code
1793 , 'Y' group_calendar_flag
1794 FROM fnd_grants fg
1795 , jtf_rs_groups_b g
1796 WHERE fg.grantee_key = to_char(p_resource_id)
1797 AND fg.grantee_type = 'USER'
1798 AND fg.instance_pk2_value = 'RS_GROUP'
1799 AND fg.instance_pk1_value = to_char(g.group_id)
1800 ) rs
1801 , jtf_task_all_assignments asg
1802 , jtf_tasks_b tsk
1803 WHERE asg.resource_type_code = rs.resource_type_code
1804 AND asg.resource_id = rs.resource_id
1805 AND asg.task_id = tsk.task_id
1806 AND tsk.task_id = p_task_id
1807 ORDER BY rs.group_calendar_flag desc
1808 ,decode(rs.resource_type_code,
1809 'RS_EMPLOYEE', 1,
1810 'RS_GROUP', 2,
1811 'RS_TEAM', 3);
1812
1813 BEGIN
1814 OPEN c_resource;
1815 FETCH c_resource
1816 INTO x_assignee_role
1817 , x_resource_type
1818 , x_group_calendar_flag
1819 , x_assignment_status_id
1820 , x_source_object_type_code;
1821 IF c_resource%NOTFOUND
1822 THEN
1823 CLOSE c_resource;
1824
1825 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
1826 fnd_msg_pub.add;
1827
1828 fnd_message.set_name('JTF', 'JTA_SYNC_ASSIGNMENT_NOTFOUND');
1829 fnd_message.set_token('PROC_NAME','JTA_SYNC_TASK_COMMON.GET_SYNC_TYPE');
1830 fnd_msg_pub.add;
1831
1832 raise_application_error (-20100,jta_sync_common.get_messages);
1833 END IF;
1834 CLOSE c_resource;
1835
1836 END get_sync_info;
1837
1838 FUNCTION get_update_type (p_task_id IN NUMBER,
1839 p_resource_id IN NUMBER,
1840 p_subject IN VARCHAR2)
1841 RETURN VARCHAR2
1842 IS
1843 l_synced_resource_type VARCHAR2(30);
1844 l_group_calendar_flag VARCHAR2(1);
1845 l_task_name VARCHAR2(80);
1846 l_assignee_role VARCHAR2(30);
1847 l_assignment_status_id NUMBER;
1848 l_source_object_type_code VARCHAR2(60); -- Added for fix bug 2442686
1849 l_update_type VARCHAR2(15) := G_UPDATE_ALL; -- Added for fix bug 2442686
1850 BEGIN
1851 -- Added for fix bug 2482833
1852 get_sync_info (p_task_id => p_task_id,
1853 p_resource_id => p_resource_id,
1854 x_assignee_role => l_assignee_role,
1855 x_resource_type => l_synced_resource_type,
1856 x_group_calendar_flag => l_group_calendar_flag,
1857 x_assignment_status_id => l_assignment_status_id,
1858 x_source_object_type_code => l_source_object_type_code
1859 );
1860
1861 IF rtrim(l_synced_resource_type) = 'RS_GROUP' AND
1862 l_group_calendar_flag = 'Y'
1863 THEN
1864 l_update_type := g_do_nothing; -- Added for fix bug 2442686
1865 ELSE
1866 IF l_assignee_role = 'ASSIGNEE'
1867 THEN
1868 -- Fix bug 2442686:
1869 -- If this is TASK, assignee can update any fields,
1870 -- but if it's APPOINTMENT, then the invitee can update only the status
1871 -- when he/she accept the appointment.
1872 --l_source_object_type_code := get_source_object_type(p_task_id); -- Added for fix bug 2442686
1873 IF l_source_object_type_code = G_APPOINTMENT -- Added for fix bug 2442686
1874 THEN
1875 IF l_assignment_status_id = 18 AND -- Status = Invited
1876 SUBSTR(p_subject, 1, LENGTH(g_prefix_invitee)) <> g_prefix_invitee
1877 THEN
1878 l_update_type := g_update_status;
1879 ELSE -- Status <> Invited
1880 l_update_type := g_do_nothing;
1881 END IF;
1882 END IF;
1883 END IF;
1884 END IF;
1885
1886 RETURN l_update_type; -- Added for fix bug 2442686
1887
1888 END get_update_type;
1889
1890 FUNCTION get_recurrence_rule_id (p_task_id IN NUMBER)
1891 RETURN NUMBER
1892 IS
1893 CURSOR c_recur
1894 IS
1895 SELECT t.recurrence_rule_id
1896 FROM jtf_tasks_b t
1897 WHERE t.task_id = p_task_id;
1898
1899 l_recurrence_rule_id NUMBER;
1900 BEGIN
1901 OPEN c_recur;
1902 FETCH c_recur into l_recurrence_rule_id;
1903
1904 IF c_recur%NOTFOUND
1905 THEN
1906 l_recurrence_rule_id := NULL;
1907 END IF;
1908
1909 CLOSE c_recur;
1910 RETURN l_recurrence_rule_id;
1911 END get_recurrence_rule_id;
1912
1913 PROCEDURE convert_recur_date_to_client (
1914 p_base_start_time IN DATE,
1915 p_base_end_time IN DATE,
1916 p_start_date IN DATE,
1917 p_end_date IN DATE,
1918 p_occurs_which IN NUMBER,
1919 p_uom IN VARCHAR2,
1920 x_date_of_month OUT NOCOPY NUMBER,
1921 x_start_date IN OUT NOCOPY DATE,
1922 x_end_date IN OUT NOCOPY DATE
1923 )
1924 IS
1925 l_start_date VARCHAR2(10); -- DD-MM-YYYY
1926 l_start_time VARCHAR2(8); -- HH24:MI:SS
1927 l_end_date VARCHAR2(10); -- DD-MM-YYYY
1928 l_end_time VARCHAR2(8); -- HH24:MI:SS
1929 BEGIN
1930 l_start_date := TO_CHAR (p_start_date, 'DD-MM-YYYY');
1931 l_start_time := TO_CHAR (p_base_start_time, 'HH24:MI:SS');
1932 l_end_date := TO_CHAR (p_end_date, 'DD-MM-YYYY');
1933 l_end_time := TO_CHAR (p_base_end_time, 'HH24:MI:SS');
1934
1935 if l_start_time <> l_end_time then
1936 x_start_date :=
1937 TRUNC (
1938 convert_gmt_to_client (
1939 TO_DATE (
1940 l_start_date || ' ' || l_start_time,
1941 'DD-MM-YYYY HH24:MI:SS'
1942 )
1943 )
1944 );
1945 IF l_end_date IS NOT NULL THEN
1946 x_end_date :=
1947 TRUNC (
1948 convert_gmt_to_client (
1949 TO_DATE (
1950 l_end_date || ' ' || l_end_time,
1951 'DD-MM-YYYY HH24:MI:SS'
1952 )
1953 )
1954 );
1955 END IF;
1956 else
1957 x_start_date := TO_DATE(l_start_date,'DD-MM-YYYY');
1958 x_end_date := TO_DATE(l_end_date,'DD-MM-YYYY');
1959 end if ;
1960
1961 IF p_occurs_which IS NULL
1962 AND (p_uom = 'MON' OR p_uom ='YER')
1963 THEN
1964 x_date_of_month := TO_CHAR (x_start_date, 'DD');
1965 END IF;
1966 END convert_recur_date_to_client;
1967
1968 PROCEDURE get_all_nonrepeat_tasks (
1969 p_request_type IN VARCHAR2,
1970 p_syncanchor IN DATE,
1971 p_recordindex IN NUMBER,
1972 p_resource_id IN NUMBER,
1973 p_resource_type IN VARCHAR2,
1974 p_source_object_type IN VARCHAR2,
1975 p_get_data IN BOOLEAN,
1976 x_totalnew IN OUT NOCOPY NUMBER,
1977 x_totalmodified IN OUT NOCOPY NUMBER,
1978 -- x_totaldeleted IN OUT NOCOPY NUMBER,
1979 x_data IN OUT NOCOPY jta_sync_task.task_tbl
1980 --p_new_syncanchor IN DATE
1981 )
1982 IS
1983 x_task_rec jta_sync_task.task_rec;
1984 i INTEGER := p_recordindex;
1985 l_invalid BOOLEAN;
1986 BEGIN
1987 FOR rec_modify_nonrepeat IN jta_sync_task_cursors.c_modify_non_repeat_task (
1988 p_syncanchor,
1989 p_resource_id,
1990 p_resource_type,
1991 p_source_object_type
1992 )
1993 LOOP
1994 --check span days and skip add_task
1995 check_span_days (
1996 p_source_object_type_code => rec_modify_nonrepeat.source_object_type_code,
1997 p_calendar_start_date => rec_modify_nonrepeat.calendar_start_date,
1998 p_calendar_end_date => rec_modify_nonrepeat.calendar_end_date,
1999 x_status => l_invalid
2000 );
2001
2002 IF NOT (l_invalid OR already_selected(p_task_id => rec_modify_nonrepeat.task_id, p_task_tbl => x_data))
2003 THEN
2004 IF p_get_data
2005 THEN
2006 add_task (
2007 p_request_type => p_request_type,
2008 p_resource_id => p_resource_id,
2009 p_resource_type => p_resource_type,
2010 p_recordindex => i+1,
2011 p_operation => g_modify,
2012 p_task_sync_id => rec_modify_nonrepeat.task_sync_id,
2013 p_task_id => rec_modify_nonrepeat.task_id,
2014 p_task_name => rec_modify_nonrepeat.task_name,
2015 p_owner_type_code => rec_modify_nonrepeat.owner_type_code,
2016 p_description => rec_modify_nonrepeat.description,
2017 p_task_status_id => rec_modify_nonrepeat.task_status_id,
2018 p_task_priority_id => rec_modify_nonrepeat.importance_level ,
2019 p_private_flag => rec_modify_nonrepeat.private_flag,
2020 p_date_selected => rec_modify_nonrepeat.date_selected,
2021 p_timezone_id => rec_modify_nonrepeat.timezone_id,
2022 p_syncanchor => rec_modify_nonrepeat.new_timestamp,
2023 p_planned_start_date => rec_modify_nonrepeat.planned_start_date,
2024 p_planned_end_date => rec_modify_nonrepeat.planned_end_date,
2025 p_scheduled_start_date => rec_modify_nonrepeat.scheduled_start_date,
2026 p_scheduled_end_date => rec_modify_nonrepeat.scheduled_end_date,
2027 p_actual_start_date => rec_modify_nonrepeat.actual_start_date,
2028 p_actual_end_date => rec_modify_nonrepeat.actual_end_date,
2029 p_calendar_start_date => rec_modify_nonrepeat.calendar_start_date,
2030 p_calendar_end_date => rec_modify_nonrepeat.calendar_end_date,
2031 p_alarm_on => rec_modify_nonrepeat.alarm_on,
2032 p_alarm_start => rec_modify_nonrepeat.alarm_start,
2033 p_recurrence_rule_id => rec_modify_nonrepeat.recurrence_rule_id,
2034 p_occurs_uom => NULL,
2035 p_occurs_every => NULL,
2036 p_occurs_number => NULL,
2037 p_start_date_active => NULL,
2038 p_end_date_active => NULL,
2039 p_sunday => NULL,
2040 p_monday => NULL,
2041 p_tuesday => NULL,
2042 p_wednesday => NULL,
2043 p_thursday => NULL,
2044 p_friday => NULL,
2045 p_saturday => NULL,
2046 p_date_of_month => NULL,
2047 p_occurs_which => NULL,
2048 x_task_rec => x_task_rec
2049 );
2050 i := i + 1;
2051 x_data (i) := x_task_rec;
2052
2053 ELSE -- For get_count, store the task_id selected so as to avoid the duplicate
2054 i := i + 1;
2055 x_data (i).task_id := rec_modify_nonrepeat.task_id;
2056 END IF; -- p_get_data
2057 x_totalmodified := x_totalmodified + 1;
2058 END IF; -- l_invalid
2059
2060 END LOOP;
2061
2062 FOR rec_new_nonrepeat IN jta_sync_task_cursors.c_new_non_repeat_task (
2063 p_syncanchor,
2064 p_resource_id,
2065 p_resource_type,
2066 p_source_object_type
2067 )
2068 LOOP
2069 --check span days and skip add_task
2070 check_span_days (
2071 p_source_object_type_code => rec_new_nonrepeat.source_object_type_code,
2072 p_calendar_start_date => rec_new_nonrepeat.calendar_start_date,
2073 p_calendar_end_date => rec_new_nonrepeat.calendar_end_date,
2074 x_status => l_invalid
2075 );
2076
2077 IF NOT (l_invalid OR already_selected(p_task_id => rec_new_nonrepeat.task_id, p_task_tbl => x_data))
2078 THEN
2079 IF p_get_data
2080 THEN
2081 add_task (
2082 p_request_type => p_request_type,
2083 p_resource_id => p_resource_id,
2084 p_resource_type => p_resource_type,
2085 p_recordindex => i + 1,
2086 p_operation => g_new,
2087 p_task_sync_id => NULL,
2088 p_task_id => rec_new_nonrepeat.task_id,
2089 p_task_name => rec_new_nonrepeat.task_name,
2090 p_owner_type_code => rec_new_nonrepeat.owner_type_code,
2091 p_description => rec_new_nonrepeat.description,
2092 p_task_status_id => rec_new_nonrepeat.task_status_id,
2093 p_task_priority_id => rec_new_nonrepeat.importance_level ,
2094 p_private_flag => rec_new_nonrepeat.private_flag,
2095 p_date_selected => rec_new_nonrepeat.date_selected,
2096 p_timezone_id => rec_new_nonrepeat.timezone_id,
2097 p_syncanchor => rec_new_nonrepeat.new_timestamp,
2098 p_planned_start_date => rec_new_nonrepeat.planned_start_date,
2099 p_planned_end_date => rec_new_nonrepeat.planned_end_date,
2100 p_scheduled_start_date => rec_new_nonrepeat.scheduled_start_date,
2101 p_scheduled_end_date => rec_new_nonrepeat.scheduled_end_date,
2102 p_actual_start_date => rec_new_nonrepeat.actual_start_date,
2103 p_actual_end_date => rec_new_nonrepeat.actual_end_date,
2104 p_calendar_start_date => rec_new_nonrepeat.calendar_start_date,
2105 p_calendar_end_date => rec_new_nonrepeat.calendar_end_date,
2106 p_alarm_on => rec_new_nonrepeat.alarm_on,
2107 p_alarm_start => rec_new_nonrepeat.alarm_start,
2108 p_recurrence_rule_id => rec_new_nonrepeat.recurrence_rule_id,
2109 p_occurs_uom => NULL,
2110 p_occurs_every => NULL,
2111 p_occurs_number => NULL,
2112 p_start_date_active => NULL,
2113 p_end_date_active => NULL,
2114 p_sunday => NULL,
2115 p_monday => NULL,
2116 p_tuesday => NULL,
2117 p_wednesday => NULL,
2118 p_thursday => NULL,
2119 p_friday => NULL,
2120 p_saturday => NULL,
2121 p_date_of_month => NULL,
2122 p_occurs_which => NULL,
2123 --p_get_data => p_get_data,
2124 x_task_rec => x_task_rec
2125 );
2126
2127 i := i + 1;
2128 x_data (i) := x_task_rec;
2129 ELSE -- For get_count, store the task_id selected so as to avoid the duplicate
2130 i := i + 1;
2131 x_data (i).task_id := rec_new_nonrepeat.task_id;
2132 END IF; --p_get_data
2133
2134 x_totalnew := x_totalnew + 1;
2135 END IF; -- l_invalid
2136 END LOOP;
2137
2138 END get_all_nonrepeat_tasks;
2139
2140 PROCEDURE get_all_deleted_tasks (
2141 p_request_type IN VARCHAR2,
2142 p_syncanchor IN DATE,
2143 p_recordindex IN NUMBER,
2144 p_resource_id IN NUMBER,
2145 p_resource_type IN VARCHAR2,
2146 p_source_object_type IN VARCHAR2,
2147 p_get_data IN BOOLEAN,
2148 x_totaldeleted IN OUT NOCOPY NUMBER,
2149 x_data IN OUT NOCOPY jta_sync_task.task_tbl
2150 )
2151 IS
2152 i INTEGER := nvl(x_data.last,0);
2153 BEGIN
2154 FOR rec_delete IN jta_sync_task_cursors.c_delete_task (
2155 p_syncanchor,
2156 p_resource_id,
2157 p_resource_type,
2158 p_source_object_type
2159 )
2160 LOOP
2161 IF NOT already_selected(p_sync_id => rec_delete.task_sync_id, p_task_tbl => x_data)
2162 THEN
2163 IF p_get_data
2164 THEN
2165 i := i + 1;
2166 x_data(i).syncid := rec_delete.task_sync_id;
2167 x_data(i).recordindex:= i;
2168 x_data(i).eventtype := g_delete;
2169 x_data(i).resultid := 0;
2170
2171 jta_sync_task_map_pkg.delete_row (
2172 p_task_sync_id => rec_delete.task_sync_id
2173 );
2174
2175 x_data(i).syncanchor := convert_server_to_gmt (SYSDATE);
2176 ELSE -- For get_count, store the sync_id selected so as to avoid the duplicate
2177 i := i + 1;
2178 x_data (i).syncid := rec_delete.task_sync_id;
2179 END IF;
2180
2181 x_totaldeleted := x_totaldeleted + 1;
2182 END IF;
2183 END LOOP;
2184
2185 FOR rec_delete IN jta_sync_task_cursors.c_delete_assignee_reject (
2186 p_syncanchor,
2187 p_resource_id,
2188 p_resource_type,
2189 p_source_object_type
2190 )
2191 LOOP
2192 IF NOT already_selected(p_sync_id => rec_delete.task_sync_id, p_task_tbl => x_data)
2193 THEN
2194 IF p_get_data
2195 THEN
2196 i := i + 1;
2197 x_data(i).syncid := rec_delete.task_sync_id;
2198 x_data(i).recordindex:= i;
2199 x_data(i).eventtype := g_delete;
2200 x_data(i).resultid := 0;
2201
2202 jta_sync_task_map_pkg.delete_row (
2203 p_task_sync_id => rec_delete.task_sync_id
2204 );
2205
2206 x_data(i).syncanchor := convert_server_to_gmt (SYSDATE);
2207 ELSE -- For get_count, store the sync_id selected so as to avoid the duplicate
2208 i := i + 1;
2209 x_data (i).syncid := rec_delete.task_sync_id;
2210 END IF;
2211
2212 x_totaldeleted := x_totaldeleted + 1;
2213 END IF;
2214 END LOOP;
2215
2216 FOR rec_delete IN jta_sync_task_cursors.c_delete_assignment (
2217 p_syncanchor,
2218 p_resource_id,
2219 p_resource_type,
2220 p_source_object_type
2221 )
2222 LOOP
2223 IF NOT already_selected(p_sync_id => rec_delete.task_sync_id, p_task_tbl => x_data)
2224 THEN
2225 IF p_get_data
2226 THEN
2227 i := i + 1;
2228 x_data(i).eventtype := g_delete;
2229 x_data(i).syncid := rec_delete.task_sync_id;
2230 x_data(i).recordindex:= i;
2231 x_data(i).resultid := 0;
2232
2233 jta_sync_task_map_pkg.delete_row (
2234 p_task_sync_id => rec_delete.task_sync_id
2235 );
2236
2237 x_data(i).syncanchor := convert_server_to_gmt (SYSDATE);
2238 ELSE -- For get_count, store the sync_id selected so as to avoid the duplicate
2239 i := i + 1;
2240 x_data (i).syncid := rec_delete.task_sync_id;
2241 END IF;
2242
2243 x_totaldeleted := x_totaldeleted + 1;
2244 END IF;
2245 END LOOP;
2246
2247 FOR rec_delete IN jta_sync_task_cursors.c_delete_rejected_tasks (
2248 p_syncanchor,
2249 p_resource_id,
2250 p_resource_type,
2251 p_source_object_type
2252 )
2253 LOOP
2254 IF NOT already_selected(p_sync_id => rec_delete.task_sync_id, p_task_tbl => x_data)
2255 THEN
2256 IF p_get_data
2257 THEN
2258 i := i + 1;
2259 x_data (i).syncid := rec_delete.task_sync_id;
2260 x_data(i).recordindex := i;
2261 x_data (i).eventtype := g_delete;
2262 x_data (i).resultid := 0;
2263
2264 jta_sync_task_map_pkg.delete_row (
2265 p_task_sync_id => rec_delete.task_sync_id
2266 );
2267
2268 x_data (i).syncanchor := convert_server_to_gmt (SYSDATE);
2269 ELSE -- For get_count, store the sync_id selected so as to avoid the duplicate
2270 i := i + 1;
2271 x_data (i).syncid := rec_delete.task_sync_id;
2272 END IF;
2273
2274 x_totaldeleted := x_totaldeleted + 1;
2275 END IF;
2276 END LOOP;
2277
2278 FOR rec_delete IN jta_sync_task_cursors.c_delete_unsubscribed(
2279 p_resource_id,
2280 p_resource_type,
2281 p_source_object_type
2282 )
2283 LOOP
2284 IF NOT already_selected(p_sync_id => rec_delete.task_sync_id, p_task_tbl => x_data)
2285 THEN
2286 IF p_get_data
2287 THEN
2288 i := i + 1;
2289 x_data (i).syncid := rec_delete.task_sync_id;
2290 x_data(i).recordindex := i;
2291 x_data (i).eventtype := g_delete;
2292 x_data (i).resultid := 0;
2293
2294 jta_sync_task_map_pkg.delete_row (
2295 p_task_sync_id => rec_delete.task_sync_id
2296 );
2297
2298 x_data (i).syncanchor := convert_server_to_gmt (SYSDATE);
2299
2300 ELSE -- For get_count, store the sync_id selected so as to avoid the duplicate
2301 i := i + 1;
2302 x_data (i).syncid := rec_delete.task_sync_id;
2303 END IF;
2304
2305 x_totaldeleted := x_totaldeleted + 1;
2306 END IF;
2307 END LOOP;
2308 END get_all_deleted_tasks;
2309
2310 ------------------------------------------------
2311 PROCEDURE get_all_repeat_tasks (
2312 p_request_type IN VARCHAR2,
2313 p_syncanchor IN DATE,
2314 p_recordindex IN NUMBER,
2315 p_resource_id IN NUMBER,
2316 p_resource_type IN VARCHAR2,
2317 p_source_object_type IN VARCHAR2,
2318 p_get_data IN BOOLEAN,
2319 x_totalnew IN OUT NOCOPY NUMBER,
2320 x_totalmodified IN OUT NOCOPY NUMBER,
2321 -- x_totaldeleted IN OUT NOCOPY NUMBER,
2322 x_data IN OUT NOCOPY jta_sync_task.task_tbl,
2323 x_exclusion_data IN OUT NOCOPY jta_sync_task.exclusion_tbl
2324 --p_new_syncanchor IN DATE
2325 )
2326 IS
2327 i INTEGER := nvl(x_data.last,0);
2328 x_task_rec jta_sync_task.task_rec;
2329 l_invalid BOOLEAN;
2330 BEGIN
2331
2332 FOR rec_modify_repeat IN jta_sync_task_cursors.c_modify_repeating_task (
2333 p_syncanchor,
2334 p_resource_id,
2335 p_resource_type,
2336 p_source_object_type
2337 )
2338 LOOP
2339 --check span days and skip add_task
2340 check_span_days (
2341 p_source_object_type_code => rec_modify_repeat.source_object_type_code,
2342 p_calendar_start_date => rec_modify_repeat.calendar_start_date,
2343 p_calendar_end_date => rec_modify_repeat.calendar_end_date,
2344 x_status => l_invalid
2345 );
2346
2347 IF NOT (l_invalid OR already_selected(p_task_id => rec_modify_repeat.task_id, p_task_tbl => x_data))
2348 THEN
2349
2350 IF p_get_data
2351 THEN
2352 add_task (
2353 p_request_type => p_request_type,
2354 p_resource_id => p_resource_id,
2355 p_resource_type => p_resource_type,
2356 p_recordindex => i + 1,
2357 p_operation => g_modify,
2358 p_task_sync_id => rec_modify_repeat.task_sync_id,
2359 p_task_id => rec_modify_repeat.task_id,
2360 p_task_name => rec_modify_repeat.task_name,
2361 p_owner_type_code => rec_modify_repeat.owner_type_code,
2362 p_description => rec_modify_repeat.description,
2363 p_task_status_id => rec_modify_repeat.task_status_id,
2364 p_task_priority_id => null ,
2365 p_private_flag => rec_modify_repeat.private_flag,
2366 p_date_selected => rec_modify_repeat.date_selected,
2367 p_timezone_id => rec_modify_repeat.timezone_id,
2368 p_syncanchor => rec_modify_repeat.new_timestamp,
2369 p_planned_start_date => rec_modify_repeat.planned_start_date,
2370 p_planned_end_date => rec_modify_repeat.planned_end_date,
2371 p_scheduled_start_date => rec_modify_repeat.scheduled_start_date,
2372 p_scheduled_end_date => rec_modify_repeat.scheduled_end_date,
2373 p_actual_start_date => rec_modify_repeat.actual_start_date,
2374 p_actual_end_date => rec_modify_repeat.actual_end_date,
2375 p_calendar_start_date => rec_modify_repeat.calendar_start_date,
2376 p_calendar_end_date => rec_modify_repeat.calendar_end_date,
2377 p_alarm_on => rec_modify_repeat.alarm_on,
2378 p_alarm_start => rec_modify_repeat.alarm_start,
2379 p_recurrence_rule_id => rec_modify_repeat.recurrence_rule_id,
2380 p_occurs_uom => rec_modify_repeat.occurs_uom,
2381 p_occurs_every => rec_modify_repeat.occurs_every,
2382 p_occurs_number => rec_modify_repeat.occurs_number,
2383 p_start_date_active => rec_modify_repeat.start_date_active,
2384 p_end_date_active => rec_modify_repeat.end_date_active,
2385 p_sunday => rec_modify_repeat.sunday,
2386 p_monday => rec_modify_repeat.monday,
2387 p_tuesday => rec_modify_repeat.tuesday,
2388 p_wednesday => rec_modify_repeat.wednesday,
2389 p_thursday => rec_modify_repeat.thursday,
2390 p_friday => rec_modify_repeat.friday,
2391 p_saturday => rec_modify_repeat.saturday,
2392 p_date_of_month => rec_modify_repeat.date_of_month,
2393 p_occurs_which => rec_modify_repeat.occurs_which,
2394 x_task_rec => x_task_rec
2395 --p_get_data => p_get_data
2396 );
2397 i := i + 1;
2398 x_data (i) := x_task_rec;
2399
2400 get_exclusion_data (
2401 p_recurrence_rule_id => rec_modify_repeat.recurrence_rule_id,
2402 p_exclusion_data => x_exclusion_data,
2403 p_task_sync_id => x_task_rec.syncid
2404 );
2405 ELSE -- For get_count, store the sync_id selected so as to avoid the duplicate
2406 i := i + 1;
2407 x_data (i).task_id := rec_modify_repeat.task_id;
2408 END IF; -- p_get_data
2409
2410 x_totalmodified := x_totalmodified + 1;
2411 END IF; -- l_invalid
2412
2413 END LOOP;
2414
2415 FOR rec_new_repeat IN jta_sync_task_cursors.c_new_repeating_task (
2416 p_syncanchor,
2417 p_resource_id,
2418 p_resource_type,
2419 p_source_object_type
2420 )
2421 LOOP
2422 --check span days and skip add_task
2423 check_span_days (
2424 p_source_object_type_code => rec_new_repeat.source_object_type_code,
2425 p_calendar_start_date => rec_new_repeat.calendar_start_date,
2426 p_calendar_end_date => rec_new_repeat.calendar_end_date,
2427 x_status => l_invalid
2428 );
2429
2430 IF NOT (l_invalid OR already_selected(p_task_id => rec_new_repeat.task_id, p_task_tbl => x_data))
2431 THEN
2432 IF p_get_data
2433 THEN
2434 add_task (
2435 p_request_type => p_request_type,
2436 p_resource_id => p_resource_id,
2437 p_resource_type => p_resource_type,
2438 p_recordindex => i + 1,
2439 p_operation => g_new,
2440 p_task_sync_id => null ,
2441 p_task_id => rec_new_repeat.task_id,
2442 p_task_name => rec_new_repeat.task_name,
2443 p_owner_type_code => rec_new_repeat.owner_type_code,
2444 p_description => rec_new_repeat.description,
2445 p_task_status_id => rec_new_repeat.task_status_id,
2446 p_task_priority_id => rec_new_repeat.importance_level,
2447 p_private_flag => rec_new_repeat.private_flag,
2448 p_date_selected => rec_new_repeat.date_selected,
2449 p_timezone_id => rec_new_repeat.timezone_id,
2450 p_syncanchor => rec_new_repeat.new_timestamp,
2451 p_planned_start_date => rec_new_repeat.planned_start_date,
2452 p_planned_end_date => rec_new_repeat.planned_end_date,
2453 p_scheduled_start_date => rec_new_repeat.scheduled_start_date,
2454 p_scheduled_end_date => rec_new_repeat.scheduled_end_date,
2455 p_actual_start_date => rec_new_repeat.actual_start_date,
2456 p_actual_end_date => rec_new_repeat.actual_end_date,
2457 p_calendar_start_date => rec_new_repeat.calendar_start_date,
2458 p_calendar_end_date => rec_new_repeat.calendar_end_date,
2459 p_alarm_on => rec_new_repeat.alarm_on,
2460 p_alarm_start => rec_new_repeat.alarm_start,
2461 p_recurrence_rule_id => rec_new_repeat.recurrence_rule_id,
2462 p_occurs_uom => rec_new_repeat.occurs_uom,
2463 p_occurs_every => rec_new_repeat.occurs_every,
2464 p_occurs_number => rec_new_repeat.occurs_number,
2465 p_start_date_active => rec_new_repeat.start_date_active,
2466 p_end_date_active => rec_new_repeat.end_date_active,
2467 p_sunday => rec_new_repeat.sunday,
2468 p_monday => rec_new_repeat.monday,
2469 p_tuesday => rec_new_repeat.tuesday,
2470 p_wednesday => rec_new_repeat.wednesday,
2471 p_thursday => rec_new_repeat.thursday,
2472 p_friday => rec_new_repeat.friday,
2473 p_saturday => rec_new_repeat.saturday,
2474 p_date_of_month => rec_new_repeat.date_of_month,
2475 p_occurs_which => rec_new_repeat.occurs_which,
2476 --p_get_data => p_get_data,
2477 x_task_rec => x_task_rec
2478 );
2479
2480 i := i + 1;
2481 x_data (i) := x_task_rec;
2482
2483 get_exclusion_data (
2484 p_recurrence_rule_id => rec_new_repeat.recurrence_rule_id,
2485 p_exclusion_data => x_exclusion_data,
2486 p_task_sync_id => x_task_rec.syncid
2487 );
2488 ELSE -- For get_count, store the sync_id selected so as to avoid the duplicate
2489 i := i + 1;
2490 x_data (i).task_id := rec_new_repeat.task_id;
2491 END IF; -- p_get_data
2492
2493 x_totalnew := x_totalnew + 1;
2494
2495 END IF; -- l_invalid
2496 END LOOP;
2497
2498 END get_all_repeat_tasks;
2499
2500 PROCEDURE create_new_data (
2501 p_task_rec IN OUT NOCOPY jta_sync_task.task_rec,
2502 p_mapping_type IN VARCHAR2,
2503 p_exclusion_tbl IN jta_sync_task.exclusion_tbl,
2504 p_resource_id IN NUMBER,
2505 p_resource_type IN VARCHAR2
2506 )
2507 IS
2508 l_task_id NUMBER;
2509 l_return_status VARCHAR2(1);
2510 l_msg_count NUMBER;
2511 l_msg_data VARCHAR2(2000);
2512 l_task_assignment_id NUMBER;
2513 l_show_on_calendar VARCHAR2(100);
2514 l_date_selected VARCHAR2(100);
2515 l_alarm_mins NUMBER;
2516 l_scheduled_start DATE;
2517 l_scheduled_end DATE;
2518 l_planned_end DATE;
2519 l_planned_start DATE;
2520 l_actual_end DATE;
2521 l_actual_start DATE;
2522 l_recurrence_rule_id NUMBER;
2523 l_rec_rule_id NUMBER;
2524 task_id NUMBER;
2525 l_task_rec jtf_task_recurrences_pub.task_details_rec;
2526 l_reccurences_generated INTEGER;
2527 l_update_type VARCHAR2(15);
2528 l_repeat_start_date DATE;
2529 l_repeat_end_date DATE;
2530 l_status_id NUMBER;
2531 l_category_id NUMBER;
2532 l_subject VARCHAR2(80);
2533 l_occurs_month NUMBER;
2534 l_occurs_number NUMBER;
2535 BEGIN
2536 fnd_msg_pub.initialize;
2537
2538 get_alarm_mins (p_task_rec, x_alarm_mins => l_alarm_mins);
2539
2540 --------------------------------------------
2541 -- Convert GMT to Client timezone
2542 -- for plan / schedule / actual dates
2543 --------------------------------------------
2544 convert_dates (
2545 p_task_rec => p_task_rec,
2546 p_operation => 'CREATE',
2547 x_planned_start => l_planned_start,
2548 x_planned_end => l_planned_end,
2549 x_scheduled_start => l_scheduled_start,
2550 x_scheduled_end => l_scheduled_end,
2551 x_actual_start => l_actual_start,
2552 x_actual_end => l_actual_end,
2553 x_date_selected => l_date_selected,
2554 x_show_on_calendar => l_show_on_calendar
2555 );
2556
2557 l_category_id := jta_sync_task_category.get_category_id (
2558 p_category_name => p_task_rec.category,
2559 p_profile_id => jta_sync_task_category.get_profile_id (p_resource_id)
2560 );
2561 /*l_status_id :=
2562 getchangedstatusid (
2563 p_task_status_id => p_task_rec.statusid,
2564 p_source_object_type_code => p_task_rec.objectcode
2565 );
2566 */
2567 l_subject := get_subject( p_subject => p_task_rec.subject
2568 , p_type => 'ORACLE');
2569
2570 IF p_task_rec.objectcode = G_APPOINTMENT THEN
2571 jta_cal_appointment_pvt.create_appointment (
2572 p_task_name => l_subject,
2573 p_task_type_id => get_default_task_type,
2574 p_description => p_task_rec.description,
2575 p_task_priority_id => p_task_rec.priorityid,
2576 p_owner_type_code => p_resource_type,
2577 p_owner_id => p_resource_id,
2578 p_planned_start_date => l_planned_start,
2579 p_planned_end_date => l_planned_end,
2580 p_timezone_id => g_client_timezone_id,
2581 p_private_flag => p_task_rec.privateflag,
2582 p_alarm_start => l_alarm_mins,
2583 p_alarm_on => p_task_rec.alarmflag,
2584 p_category_id => l_category_id,
2585 x_return_status => l_return_status,
2586 x_task_id => l_task_id
2587 );
2588 ELSE
2589 jtf_tasks_pvt.create_task (
2590 p_api_version => 1.0,
2591 p_init_msg_list => fnd_api.g_true,
2592 p_commit => fnd_api.g_false,
2593 p_source_object_type_code => p_task_rec.objectcode,
2594 p_task_name => l_subject,
2595 p_task_type_id => get_default_task_type,
2596 p_description => p_task_rec.description,
2597 p_task_status_id => p_task_rec.statusId,
2598 p_task_priority_id => p_task_rec.priorityid,
2599 p_owner_type_code => p_resource_type,
2600 p_owner_id => p_resource_id,
2601 p_planned_start_date => l_planned_start,
2602 p_planned_end_date => l_planned_end,
2603 p_scheduled_start_date => l_scheduled_start,
2604 p_scheduled_end_date => l_scheduled_end,
2605 p_actual_start_date => l_actual_start,
2606 p_actual_end_date => l_actual_end,
2607 p_show_on_calendar => NULL, -- Fix Bug 2467021: For creation, pass NULL
2608 p_timezone_id => g_client_timezone_id,
2609 p_date_selected => NULL, -- Fix Bug 2467021: For creation, pass NULL
2610 p_alarm_start => l_alarm_mins,
2611 p_alarm_start_uom => 'MIN',
2612 p_alarm_interval_uom => 'MIN',
2613 p_alarm_on => p_task_rec.alarmflag,
2614 p_private_flag => p_task_rec.privateflag,
2615 p_category_id => l_category_id,
2616 x_return_status => l_return_status,
2617 x_msg_count => l_msg_count,
2618 x_msg_data => l_msg_data,
2619 x_task_id => l_task_id
2620 );
2621 END IF;
2622
2623 IF jta_sync_common.is_success (l_return_status)
2624 THEN
2625 --------------------------------------------
2626 -- Check whether it has a repeating information
2627 -- If it has, then create a recurrence
2628 --------------------------------------------
2629 IF ( p_task_rec.objectcode = G_APPOINTMENT
2630 AND p_task_rec.unit_of_measure <> fnd_api.g_miss_char
2631 AND p_task_rec.unit_of_measure IS NOT NULL)
2632 -- include open end dates also
2633 -- AND p_task_rec.end_date IS NOT NULL)
2634 THEN
2635 -- Convert repeating start and end date
2636 -- to client timezone
2637 convert_recur_date_to_client (
2638 p_base_start_time => p_task_rec.plannedstartdate,
2639 p_base_end_time => p_task_rec.plannedenddate,
2640 p_start_date => p_task_rec.start_date,
2641 p_end_date => p_task_rec.end_date,
2642 p_occurs_which => p_task_rec.occurs_which,
2643 p_uom => p_task_rec.unit_of_measure,
2644 x_date_of_month => p_task_rec.date_of_month,
2645 x_start_date => l_repeat_start_date,
2646 x_end_date => l_repeat_end_date
2647 );
2648 IF p_task_rec.unit_of_measure = 'YER' THEN
2649 l_occurs_month := to_number(to_char(l_repeat_start_date, 'MM'));
2650 END IF;
2651
2652 -- include open end dates also
2653 IF (p_task_rec.end_date IS NULL)
2654 THEN
2655 l_occurs_number := G_USER_DEFAULT_REPEAT_COUNT;
2656 END IF;
2657 jtf_task_recurrences_pvt.create_task_recurrence (
2658 p_api_version => 1,
2659 p_commit => fnd_api.g_false,
2660 p_task_id => l_task_id,
2661 p_occurs_which => p_task_rec.occurs_which,
2662 p_template_flag => 'N',
2663 p_date_of_month => p_task_rec.date_of_month,
2664 p_occurs_uom => p_task_rec.unit_of_measure,
2665 p_occurs_every => p_task_rec.occurs_every,
2666 p_occurs_number => l_occurs_number,
2667 p_occurs_month => l_occurs_month,
2668 p_start_date_active => l_repeat_start_date,
2669 p_end_date_active => l_repeat_end_date,
2670 p_sunday => p_task_rec.sunday,
2671 p_monday => p_task_rec.monday,
2672 p_tuesday => p_task_rec.tuesday,
2673 p_wednesday => p_task_rec.wednesday,
2674 p_thursday => p_task_rec.thursday,
2675 p_friday => p_task_rec.friday,
2676 p_saturday => p_task_rec.saturday,
2677 x_recurrence_rule_id => l_recurrence_rule_id,
2678 x_task_rec => l_task_rec,
2679 x_output_dates_counter => l_reccurences_generated,
2680 x_return_status => l_return_status,
2681 x_msg_count => l_msg_count,
2682 x_msg_data => l_msg_data
2683 );
2684
2685
2686 IF jta_sync_common.is_success (l_return_status)
2687 THEN
2688 -------------------------------------------------------
2689 -- Recurrences are successfully created.
2690 -------------------------------------------------------
2691 IF p_exclusion_tbl.COUNT > 0
2692 THEN
2693 process_exclusions (
2694 p_exclusion_tbl => p_exclusion_tbl,
2695 p_rec_rule_id => l_recurrence_rule_id,
2696 p_repeating_task_id => l_task_id,
2697 p_task_rec => p_task_rec
2698 );
2699 ELSE
2700 -------------------------------------------------------
2701 -- There are no exclusion tasks.
2702 -------------------------------------------------------
2703 jta_sync_common.put_messages_to_result (
2704 p_task_rec,
2705 p_status => g_sync_success,
2706 p_user_message => 'JTA_SYNC_SUCCESS'
2707 );
2708 END IF;
2709 ELSE
2710 -------------------------------------------------------
2711 -- Failed to create a task recurrence
2712 -------------------------------------------------------
2713 jta_sync_common.put_messages_to_result (
2714 p_task_rec,
2715 p_status => 2,
2716 p_user_message => 'JTA_RECURRENCE_CREATION_FAIL'
2717 );
2718 END IF;
2719
2720 ELSE
2721 --------------------------------------------------------------------
2722 -- This is a Single Task and succeeded to create a single task
2723 --------------------------------------------------------------------
2724 jta_sync_common.put_messages_to_result (
2725 p_task_rec,
2726 p_status => g_sync_success,
2727 p_user_message => 'JTA_SYNC_SUCCESS'
2728 );
2729 END IF; -- end-check if this is repeating Task
2730
2731 do_mapping (
2732 p_task_id => l_task_id,
2733 p_operation => g_new,
2734 x_task_sync_id => p_task_rec.syncid
2735 );
2736
2737 p_task_rec.syncanchor := convert_server_to_gmt (SYSDATE);
2738
2739 ELSE-- failed
2740 ---------------------------------------------
2741 -- Failed to create a task
2742 ---------------------------------------------
2743
2744 jta_sync_common.put_messages_to_result (
2745 p_task_rec,
2746 p_status => 2,
2747 p_user_message => 'JTA_SYNC_TASK_CREATION_FAILED'
2748 );
2749 END IF; -- end-check if task creation is successed or not
2750
2751 /*insert_or_update_mapping (
2752 p_task_sync_id => p_task_rec.syncid,
2753 p_task_id => l_task_id,
2754 p_resource_id => p_resource_id,
2755 p_mapping_type => p_mapping_type
2756 );
2757 */
2758
2759 END create_new_data;
2760
2761 PROCEDURE update_existing_data (
2762 p_task_rec IN OUT NOCOPY jta_sync_task.task_rec,
2763 p_exclusion_tbl IN jta_sync_task.exclusion_tbl,
2764 p_resource_id IN NUMBER,
2765 p_resource_type IN VARCHAR2
2766 )
2767 IS
2768 l_ovn NUMBER;
2769 l_task_id NUMBER;
2770 l_exclude_task_id NUMBER;
2771 l_return_status VARCHAR2(1);
2772 l_msg_count NUMBER;
2773 l_msg_data VARCHAR2(2000);
2774 l_task_assignment_id NUMBER;
2775 l_show_on_calendar VARCHAR2(100);
2776 l_date_selected VARCHAR2(100);
2777 l_alarm_mins NUMBER;
2778 l_rec_rule_id NUMBER;
2779 task_id NUMBER;
2780 l_update_type VARCHAR2(15);
2781 l_planned_start_date DATE;
2782 l_planned_end_date DATE;
2783 l_scheduled_start_date DATE;
2784 l_scheduled_end_date DATE;
2785 l_actual_start_date DATE;
2786 l_actual_end_date DATE;
2787 l_sync_id NUMBER;
2788 l_category_id NUMBER;
2789 l_recurr VARCHAR2(5);
2790 l_update_all VARCHAR2(5);
2791 l_new_recurrence_rule_id NUMBER;
2792 l_occurs_month NUMBER;
2793 l_occurs_number NUMBER;
2794
2795 CURSOR c_recur_tasks (b_recurrence_rule_id NUMBER)
2796 IS
2797 SELECT task_id,
2798 planned_start_date,
2799 planned_end_date,
2800 scheduled_start_date,
2801 scheduled_end_date,
2802 actual_start_date,
2803 actual_end_date,
2804 calendar_start_date,
2805 timezone_id
2806 FROM jtf_tasks_b
2807 WHERE recurrence_rule_id = b_recurrence_rule_id;
2808
2809 l_changed_rule boolean ;
2810 l_status_id number;
2811 l_task_name jtf_tasks_tl.task_name%TYPE;
2812 BEGIN
2813 fnd_msg_pub.initialize;
2814
2815 get_alarm_mins (
2816 p_task_rec,
2817 x_alarm_mins => l_alarm_mins
2818 );
2819
2820 ---------------------------------------
2821 -- Convert GMT to client timezone
2822 -- for plan / schedule / actual dates
2823 ---------------------------------------
2824 convert_dates (
2825 p_task_rec => p_task_rec,
2826 p_operation => 'UPDATE',
2827 x_planned_start => l_planned_start_date,
2828 x_planned_end => l_planned_end_date,
2829 x_scheduled_start => l_scheduled_start_date,
2830 x_scheduled_end => l_scheduled_end_date,
2831 x_actual_start => l_actual_start_date,
2832 x_actual_end => l_actual_end_date,
2833 x_date_selected => l_date_selected,
2834 x_show_on_calendar => l_show_on_calendar
2835 );
2836
2837 l_task_name := get_subject(p_subject => p_task_rec.subject,
2838 p_type => 'ORACLE');
2839 l_task_id := get_task_id (p_sync_id => p_task_rec.syncid);
2840 l_ovn := get_ovn (p_task_id => l_task_id);
2841 l_rec_rule_id := get_recurrence_rule_id (p_task_id => l_task_id);
2842 l_sync_id := p_task_rec.syncid;
2843
2844 l_status_id := getchangedstatusid (
2845 p_task_status_id => p_task_rec.statusid,
2846 p_source_object_type_code => p_task_rec.objectcode
2847 );
2848
2849 l_category_id := jta_sync_task_category.get_category_id (
2850 p_category_name => p_task_rec.category,
2851 p_profile_id => jta_sync_task_category.get_profile_id(p_resource_id)
2852 );
2853
2854 l_update_type := get_update_type (
2855 p_task_id => l_task_id,
2856 p_resource_id => p_resource_id,
2857 p_subject => p_task_rec.subject
2858 );
2859 -- if it is repeating and exclusion and owner privilage
2860 --process exclusions
2861 IF NVL(p_task_rec.resultId,0) < 2 AND
2862 l_rec_rule_id IS NOT NULL AND
2863 p_task_rec.unit_of_measure IS NOT NULL AND
2864 p_task_rec.unit_of_measure <> fnd_api.g_miss_char
2865 THEN
2866 IF l_update_type = g_update_all
2867 THEN
2868 IF p_exclusion_tbl.COUNT > 0
2869 THEN
2870 process_exclusions (
2871 p_exclusion_tbl => p_exclusion_tbl,
2872 p_rec_rule_id => l_rec_rule_id,
2873 p_repeating_task_id => l_task_id,
2874 p_task_rec => p_task_rec
2875 );
2876 ELSE -- p_exclusion_tbl.COUNT = 0 and check change rule
2877 l_changed_rule := jta_sync_task_common.changed_repeat_rule(p_task_rec => p_task_rec);
2878
2879 IF l_changed_rule AND
2880 l_update_type = jta_sync_task_common.g_update_all
2881 THEN -- Changed Repeating Rule
2882 IF p_task_rec.unit_of_measure = 'YER' THEN
2883 l_occurs_month := to_number(to_char(p_task_rec.start_date, 'MM'));
2884 END IF;
2885
2886 -- include open end dates also
2887 IF (p_task_rec.end_date IS NULL) THEN
2888 l_occurs_number := G_USER_DEFAULT_REPEAT_COUNT;
2889 END IF;
2890
2891 jtf_task_recurrences_pvt.update_task_recurrence (
2892 p_api_version => 1.0,
2893 p_task_id => l_task_id,
2894 p_recurrence_rule_id => l_rec_rule_id,
2895 p_occurs_which => p_task_rec.occurs_which,
2896 p_date_of_month => p_task_rec.date_of_month,
2897 p_occurs_month => l_occurs_month,
2898 p_occurs_uom => p_task_rec.unit_of_measure,
2899 p_occurs_every => p_task_rec.occurs_every,
2900 p_occurs_number => l_occurs_number,
2901 p_start_date_active => p_task_rec.start_date,
2902 p_end_date_active => p_task_rec.end_date,
2903 p_sunday => p_task_rec.sunday,
2904 p_monday => p_task_rec.monday,
2905 p_tuesday => p_task_rec.tuesday,
2906 p_wednesday => p_task_rec.wednesday,
2907 p_thursday => p_task_rec.thursday,
2908 p_friday => p_task_rec.friday,
2909 p_saturday => p_task_rec.saturday,
2910 x_new_recurrence_rule_id => l_new_recurrence_rule_id,
2911 x_return_status => l_return_status,
2912 x_msg_count => l_msg_count,
2913 x_msg_data => l_msg_data
2914 );
2915
2916 IF NOT jta_sync_common.is_success (l_return_status)
2917 THEN-- Failed to update a task
2918 jta_sync_common.put_messages_to_result (
2919 p_task_rec,
2920 p_status => 2,
2921 p_user_message => 'JTA_SYNC_UPDATE_RECUR_FAIL'
2922 );
2923 END IF; -- is_success
2924 END IF; -- change rule
2925 END IF; -- p_exclusion_tbl.COUNT > 0
2926 END IF; -- l_update_type = g_update_all
2927 END IF; -- success and recurring appt process
2928
2929 --------------------------------------------------
2930 -- Update Repeating Tasks
2931 -- 1. You can delete the excluded tasks, or
2932 -- 2. You can update all occurrences
2933 --------------------------------------------------
2934 --- update_task with new parameters
2935 IF l_update_type = g_update_all
2936 THEN
2937 -----------------------------------------------------------
2938 -- Fix for the bug 2380399
2939 -- : If the current sync has a change of any fields
2940 -- along with the change of repeating rule,
2941 -- The update_task_recurrence_rule API creates new repeating
2942 -- tasks and updates the mapping record with the new first
2943 -- task_id. Hence the new task_id must be picked from
2944 -- mapping table again. And the new object_version_number
2945 -- of the the new task_id must be selected for update of the
2946 -- other fields
2947 -----------------------------------------------------------
2948 l_task_id := get_task_id (p_sync_id => p_task_rec.syncid);
2949 l_ovn := get_ovn (p_task_id => l_task_id);
2950
2951 IF p_task_rec.objectcode = G_APPOINTMENT
2952 THEN
2953 jta_cal_appointment_pvt.update_appointment (
2954 p_object_version_number => l_ovn ,
2955 p_task_id => l_task_id,
2956 p_task_name => NVL (l_task_name, ' '),
2957 p_description => p_task_rec.description,
2958 p_task_priority_id => p_task_rec.priorityid,
2959 p_planned_start_date => l_planned_start_date,
2960 p_planned_end_date => l_planned_end_date,
2961 p_timezone_id => get_task_timezone_id (l_task_id),
2962 p_private_flag => p_task_rec.privateflag,
2963 p_alarm_start => l_alarm_mins,
2964 p_alarm_on => p_task_rec.alarmflag,
2965 p_category_id => l_category_id,
2966 p_change_mode => 'A',
2967 x_return_status => l_return_status
2968 );
2969 ELSE
2970 jtf_tasks_pvt.update_task (
2971 p_api_version => 1.0,
2972 p_init_msg_list => fnd_api.g_true,
2973 p_commit => fnd_api.g_false,
2974 p_task_id => l_task_id,
2975 p_object_version_number => l_ovn,
2976 p_task_name => NVL (l_task_name, ' '),
2977 p_description => p_task_rec.description,
2978 p_task_status_id => p_task_rec.statusid,
2979 p_task_priority_id => p_task_rec.priorityid,
2980 p_planned_start_date => l_planned_start_date,
2981 p_planned_end_date => l_planned_end_date,
2982 p_scheduled_start_date => l_scheduled_start_date,
2983 p_scheduled_end_date => l_scheduled_end_date,
2984 p_actual_start_date => l_actual_start_date,
2985 p_actual_end_date => l_actual_end_date,
2986 p_show_on_calendar => fnd_api.g_miss_char, -- Fix Bug 2467021: For update, pass g_miss_char
2987 p_date_selected => fnd_api.g_miss_char, -- Fix Bug 2467021: For update, pass g_miss_char
2988 p_alarm_start => l_alarm_mins,
2989 p_alarm_start_uom => 'MIN',
2990 p_timezone_id => get_task_timezone_id (l_task_id),
2991 p_private_flag => p_task_rec.privateflag,
2992 p_category_id => l_category_id,
2993 p_change_mode => 'A',
2994 p_enable_workflow => 'N',
2995 p_abort_workflow => 'N',
2996 x_return_status => l_return_status,
2997 x_msg_count => l_msg_count,
2998 x_msg_data => l_msg_data
2999 );
3000 END IF;
3001
3002 IF NOT jta_sync_common.is_success (l_return_status)
3003 THEN-- Failed to update a task
3004
3005 jta_sync_common.put_messages_to_result (
3006 p_task_rec,
3007 p_status => 2,
3008 p_user_message => 'JTA_SYNC_UPDATE_TASK_FAIL'
3009 ); -- l_return_status
3010 END IF;
3011
3012 ELSIF l_update_type = g_update_status
3013 THEN
3014 l_task_assignment_id := get_assignment_id (
3015 p_task_id => l_task_id,
3016 p_resource_id => p_resource_id,
3017 p_resource_type => p_resource_type
3018 );
3019
3020 l_ovn := get_ovn (p_task_assignment_id => l_task_assignment_id);
3021
3022 jtf_task_assignments_pvt.update_task_assignment (
3023 p_api_version => 1.0,
3024 p_object_version_number => l_ovn,
3025 p_init_msg_list => fnd_api.g_true,
3026 p_commit => fnd_api.g_false,
3027 p_task_assignment_id => l_task_assignment_id,
3028 p_assignment_status_id => 3, -- ACCEPT
3029 --p_update_all => l_update_all,
3030 --p_enable_workflow => 'N',
3031 --p_abort_workflow => 'N',
3032 x_return_status => l_return_status,
3033 x_msg_count => l_msg_count,
3034 x_msg_data => l_msg_data
3035 );
3036
3037 IF NOT jta_sync_common.is_success (l_return_status)
3038 THEN
3039 jta_sync_common.put_messages_to_result (
3040 p_task_rec,
3041 p_status => 2,
3042 p_user_message => 'JTA_SYNC_UPDATE_STS_FAIL'
3043 );
3044 END IF;
3045
3046 END IF; -- l_update_type
3047
3048 -- Check the current status and update if it's succeeded
3049 IF nvl(p_task_rec.resultId,0) < 2
3050 THEN
3051 jta_sync_common.put_messages_to_result (
3052 p_task_rec,
3053 p_status => g_sync_success,
3054 p_user_message => 'JTA_SYNC_SUCCESS'
3055 );
3056 --CHANGE TO GMT
3057 p_task_rec.syncanchor := convert_server_to_gmt (SYSDATE);
3058 END IF;
3059
3060 END update_existing_data;
3061
3062 PROCEDURE delete_exclusion_task (
3063 p_repeating_task_id IN NUMBER,
3064 x_task_rec IN OUT NOCOPY jta_sync_task.task_rec
3065 )
3066 IS
3067 l_ovn NUMBER;
3068 l_return_status VARCHAR2(1);
3069 l_msg_data VARCHAR2(2000);
3070 l_msg_count NUMBER;
3071 BEGIN
3072
3073 l_return_status := fnd_api.g_ret_sts_success;
3074
3075 l_ovn := get_ovn (p_task_id => p_repeating_task_id);
3076
3077 IF x_task_rec.objectcode = G_APPOINTMENT
3078 THEN
3079 jta_cal_appointment_pvt.delete_appointment (
3080 p_object_version_number => l_ovn,
3081 p_task_id => p_repeating_task_id,
3082 p_delete_future_recurrences => fnd_api.g_false,
3083 x_return_status => l_return_status
3084 );
3085 ELSE
3086 jtf_tasks_pvt.delete_task (
3087 p_api_version => 1.0,
3088 p_init_msg_list => fnd_api.g_true,
3089 p_commit => fnd_api.g_false,
3090 p_task_id => p_repeating_task_id,
3091 p_object_version_number => l_ovn,
3092 x_return_status => l_return_status,
3093 p_delete_future_recurrences => fnd_api.g_false ,
3094 x_msg_count => l_msg_count,
3095 x_msg_data => l_msg_data
3096 );
3097 END IF;
3098
3099 IF jta_sync_common.is_success (l_return_status)
3100 THEN
3101 x_task_rec.syncanchor := convert_server_to_gmt (SYSDATE);
3102
3103 jta_sync_common.put_messages_to_result (
3104 x_task_rec,
3105 p_status => g_sync_success,
3106 p_user_message => 'JTA_SYNC_SUCCESS'
3107 );
3108 ELSE
3109 jta_sync_common.put_messages_to_result (
3110 x_task_rec,
3111 p_status => 2,
3112 p_user_message => 'JTA_SYNC_DELETE_EXCLUSION_FAIL'
3113 );
3114 END IF;
3115 END delete_exclusion_task;
3116
3117 PROCEDURE delete_task_data (
3118 p_task_rec IN OUT NOCOPY jta_sync_task.task_rec,
3119 p_delete_map_flag IN BOOLEAN
3120 )
3121 IS
3122 l_task_id NUMBER;
3123 l_ovn NUMBER;
3124 l_return_status VARCHAR2(1);
3125 l_msg_data VARCHAR2(2000);
3126 l_msg_count NUMBER;
3127 BEGIN
3128 l_return_status := fnd_api.g_ret_sts_success;
3129
3130 l_task_id := get_task_id (p_sync_id => p_task_rec.syncid);
3131 l_ovn := get_ovn (p_task_id => l_task_id);
3132
3133 IF p_task_rec.objectcode = G_APPOINTMENT THEN
3134 jta_cal_appointment_pvt.delete_appointment (
3135 p_object_version_number => l_ovn,
3136 p_task_id => l_task_id,
3137 p_delete_future_recurrences => 'A',
3138 x_return_status => l_return_status
3139 );
3140 ELSE
3141 jtf_tasks_pvt.delete_task (
3142 p_api_version => 1.0,
3143 p_init_msg_list => fnd_api.g_true,
3144 p_commit => fnd_api.g_false,
3145 p_task_id => l_task_id,
3146 p_object_version_number => l_ovn,
3147 p_delete_future_recurrences => 'A',
3148 x_return_status => l_return_status,
3149 x_msg_count => l_msg_count,
3150 x_msg_data => l_msg_data
3151 );
3152 END IF;
3153
3154 IF jta_sync_common.is_success (l_return_status)
3155 THEN
3156 p_task_rec.syncanchor := convert_server_to_gmt (SYSDATE + 1 / (24 * 60 * 60));
3157
3158 IF p_delete_map_flag
3159 THEN
3160 jta_sync_task_map_pkg.delete_row (
3161 p_task_sync_id => p_task_rec.syncid
3162 );
3163 END IF;
3164
3165 jta_sync_common.put_messages_to_result (
3166 p_task_rec,
3167 p_status => g_sync_success,
3168 p_user_message => 'JTA_SYNC_SUCCESS'
3169 );
3170 ELSE
3171 jta_sync_common.put_messages_to_result (
3172 p_task_rec,
3173 p_status => 2,
3174 p_user_message => 'JTA_SYNC_DELETE_TASK_FAILED'
3175 );
3176 END IF;
3177 END delete_task_data;
3178
3179 PROCEDURE reject_task_data (p_task_rec IN OUT NOCOPY jta_sync_task.task_rec)
3180 IS
3181 l_task_id NUMBER;
3182 l_rec_rule_id NUMBER;
3183 l_task_assignment_id NUMBER;
3184 l_ovn NUMBER;
3185 l_resource_id NUMBER;
3186 l_resource_type VARCHAR2(30);
3187 l_deleted BOOLEAN := FALSE;
3188 l_return_status VARCHAR2(1);
3189 l_msg_data VARCHAR2(2000);
3190 l_msg_count NUMBER;
3191
3192 --CURSOR c_tasks (b_recurrence_rule_id NUMBER, b_task_id NUMBER)
3193 --IS
3194 -- SELECT task_id, source_object_type_code
3195 -- FROM jtf_tasks_b
3196 -- WHERE ( b_recurrence_rule_id IS NOT NULL
3197 -- AND recurrence_rule_id = b_recurrence_rule_id)
3198 -- OR ( b_recurrence_rule_id IS NULL
3199 -- AND task_id = b_task_id);
3200
3201 l_update_all varchar2(1) ;
3202
3203 BEGIN
3204 get_resource_details (l_resource_id, l_resource_type);
3205
3206 l_task_id := get_task_id (p_sync_id => p_task_rec.syncid);
3207 l_rec_rule_id := get_recurrence_rule_id (p_task_id => l_task_id);
3208
3209 if l_rec_rule_id is not null then
3210 l_update_all := 'Y' ;
3211 else
3212 l_update_all := null ;
3213 end if ;
3214
3215 l_task_assignment_id := get_assignment_id (
3216 p_task_id => l_task_id,
3217 p_resource_id => l_resource_id,
3218 p_resource_type => l_resource_type
3219 );
3220
3221 l_ovn := get_ovn (p_task_assignment_id => l_task_assignment_id);
3222
3223 jtf_task_assignments_pvt.update_task_assignment (
3224 p_api_version => 1.0,
3225 p_object_version_number => l_ovn,
3226 p_init_msg_list => fnd_api.g_true,
3227 p_commit => fnd_api.g_false,
3228 p_task_assignment_id => l_task_assignment_id,
3229 p_assignment_status_id => 4, -- reject
3230 x_return_status => l_return_status,
3231 x_msg_count => l_msg_count,
3232 x_msg_data => l_msg_data
3233 --p_enable_workflow => 'N',
3234 --p_abort_workflow => 'N'
3235 );
3236
3237 IF jta_sync_common.is_success (l_return_status)
3238 THEN
3239 p_task_rec.syncanchor := convert_server_to_gmt(SYSDATE);
3240
3241 jta_sync_common.put_messages_to_result (
3242 p_task_rec,
3243 p_status => g_sync_success,
3244 p_user_message => 'JTA_SYNC_SUCCESS'
3245 );
3246
3247 jta_sync_task_map_pkg.delete_row(p_task_sync_id => p_task_rec.syncid);
3248 ELSE
3249 jta_sync_common.put_messages_to_result (
3250 p_task_rec,
3251 p_status => 2,
3252 p_user_message => 'JTA_SYNC_UPDATE_STS_FAIL'
3253 );
3254 END IF;
3255 END reject_task_data;
3256
3257 FUNCTION changed_repeat_rule (p_task_rec IN jta_sync_task.task_rec)
3258 RETURN BOOLEAN
3259 IS
3260 CURSOR c_task_recur (b_task_id NUMBER)
3261 IS
3262 SELECT jtrr.*
3263 FROM jtf_task_recur_rules jtrr, jtf_tasks_b jtb
3264 WHERE jtb.task_id = b_task_id
3265 AND jtb.recurrence_rule_id IS NOT NULL
3266 AND jtrr.recurrence_rule_id = jtb.recurrence_rule_id;
3267
3268 l_task_id NUMBER;
3269 l_rec_task_recur c_task_recur%ROWTYPE;
3270 l_start_date DATE;
3271 l_end_date DATE;
3272 l_current DATE := SYSDATE;
3273 l_date_of_month NUMBER;
3274 BEGIN
3275 l_task_id := get_task_id (p_sync_id => p_task_rec.syncid);
3276 OPEN c_task_recur (l_task_id);
3277 FETCH c_task_recur into l_rec_task_recur;
3278
3279 IF c_task_recur%NOTFOUND
3280 THEN
3281 CLOSE c_task_recur;
3282 RETURN FALSE;
3283 END IF;
3284
3285 CLOSE c_task_recur;
3286 convert_recur_date_to_client (
3287 p_base_start_time => p_task_rec.plannedstartdate,
3288 p_base_end_time => p_task_rec.plannedenddate,
3289 p_start_date => p_task_rec.start_date,
3290 p_end_date => p_task_rec.end_date,
3291 p_occurs_which => p_task_rec.occurs_which,
3292 p_uom => p_task_rec.unit_of_measure,
3293 x_date_of_month => l_date_of_month,
3294 x_start_date => l_start_date,
3295 x_end_date => l_end_date
3296 );
3297
3298
3299
3300 IF NVL (p_task_rec.occurs_which, 0) =
3301 NVL (l_rec_task_recur.occurs_which, 0)
3302 AND NVL (p_task_rec.date_of_month, 0) =
3303 NVL (l_rec_task_recur.date_of_month, 0)
3304 AND p_task_rec.unit_of_measure = l_rec_task_recur.occurs_uom
3305 AND NVL (p_task_rec.occurs_every, 0) =
3306 NVL (l_rec_task_recur.occurs_every, 0)
3307 /*AND NVL (p_task_rec.occurs_number, 0) =
3308 NVL (l_rec_task_recur.occurs_number, 0)*/
3309 AND l_start_date = l_rec_task_recur.start_date_active
3310 AND NVL (l_end_date, TRUNC (l_current)) =
3311 NVL (l_rec_task_recur.end_date_active, TRUNC (l_current))
3312 AND NVL (p_task_rec.sunday, '?') = NVL (l_rec_task_recur.sunday, '?')
3313 AND NVL (p_task_rec.monday, '?') = NVL (l_rec_task_recur.monday, '?')
3314 AND NVL (p_task_rec.tuesday, '?') =
3315 NVL (l_rec_task_recur.tuesday, '?')
3316 AND NVL (p_task_rec.wednesday, '?') =
3317 NVL (l_rec_task_recur.wednesday, '?')
3318 AND NVL (p_task_rec.thursday, '?') =
3319 NVL (l_rec_task_recur.thursday, '?')
3320 AND NVL (p_task_rec.friday, '?') = NVL (l_rec_task_recur.friday, '?')
3321 AND NVL (p_task_rec.saturday, '?') =
3322 NVL (l_rec_task_recur.saturday, '?')
3323 THEN
3324 RETURN FALSE;
3325 ELSE
3326 RETURN TRUE;
3327 END IF;
3328 END changed_repeat_rule;
3329
3330 PROCEDURE transformstatus (
3331 p_task_status_id IN OUT NOCOPY NUMBER,
3332 p_task_sync_id IN NUMBER,
3333 x_operation IN OUT NOCOPY VARCHAR2
3334 )
3335 IS
3336 l_rejected_flag CHAR;
3337 l_cancelled_flag CHAR;
3338 l_completed_flag CHAR;
3339 l_closed_flag CHAR;
3340 l_assigned_flag CHAR;
3341 l_working_flag CHAR;
3342 l_schedulable_flag CHAR;
3343 l_accepted_flag CHAR;
3344 l_on_hold_flag CHAR;
3345 l_approved_flag CHAR;
3346
3347 CURSOR c_task_status
3348 IS
3349 SELECT closed_flag, completed_flag, cancelled_flag, rejected_flag,
3350 assigned_flag, working_flag, schedulable_flag, accepted_flag,
3351 on_hold_flag, approved_flag
3352
3353 FROM jtf_task_statuses_b
3354 WHERE task_status_id = p_task_status_id;
3355 BEGIN
3356 IF (p_task_status_id = 8)
3357 OR (p_task_status_id = 4)
3358 OR (p_task_status_id = 7)
3359 OR (p_task_status_id = 12)
3360 OR (p_task_status_id = 15)
3361 OR (p_task_status_id = 16)
3362 OR (p_task_status_id = 6)
3363 THEN
3364 IF (p_task_status_id = 8)
3365 OR (p_task_status_id = 4)
3366 OR (p_task_status_id = 7)
3367 THEN
3368 x_operation := jta_sync_task_common.g_delete;
3369 END IF;
3370
3371 IF (p_task_status_id = 12)
3372 OR (p_task_status_id = 15)
3373 OR (p_task_status_id = 16)
3374 OR (p_task_status_id = 6)
3375 THEN
3376 IF p_task_sync_id IS NOT NULL
3377 THEN
3378 OPEN c_task_status;
3379 FETCH c_task_status into l_closed_flag, l_rejected_flag, l_cancelled_flag, l_completed_flag,
3380 l_assigned_flag, l_working_flag, l_schedulable_flag,
3381 l_accepted_flag, l_on_hold_flag, l_approved_flag;
3382
3383 IF (NVL (l_closed_flag, 'N') = 'Y')
3384 OR (NVL (l_rejected_flag, 'N') = 'Y')
3385 OR (NVL (l_completed_flag, 'N') = 'Y')
3386 OR (NVL (l_cancelled_flag, 'N') = 'Y')
3387 THEN
3388 x_operation := jta_sync_task_common.g_delete;
3389 END IF;
3390 CLOSE c_task_status;
3391 END IF;
3392
3393 END IF;
3394
3395 ELSE
3396 OPEN c_task_status;
3397 FETCH c_task_status into l_closed_flag, l_rejected_flag, l_cancelled_flag, l_completed_flag,
3398 l_assigned_flag, l_working_flag, l_schedulable_flag,
3399 l_accepted_flag, l_on_hold_flag, l_approved_flag;
3400
3401 IF (NVL (l_closed_flag, 'N') = 'Y')
3402 OR (NVL (l_rejected_flag, 'N') = 'Y')
3403 OR (NVL (l_completed_flag, 'N') = 'Y')
3404 OR (NVL (l_cancelled_flag, 'N') = 'Y')
3405 THEN
3406 x_operation := jta_sync_task_common.g_delete;
3407 ELSIF (NVL (l_assigned_flag, 'N') = 'Y')
3408 THEN p_task_status_id := 12;
3409 ELSIF (NVL (l_working_flag, 'N') = 'Y')
3410 THEN p_task_status_id := 15;
3411 ELSIF (NVL (l_schedulable_flag, 'N') = 'Y')
3412 THEN p_task_status_id := 12;
3413 ELSIF (NVL (l_accepted_flag, 'N') = 'Y')
3414 THEN p_task_status_id := 15;
3415 ELSIF (NVL (l_on_hold_flag, 'N') = 'Y')
3416 THEN p_task_status_id := 16;
3417 ELSIF (NVL (l_approved_flag, 'N') = 'Y')
3418 THEN p_task_status_id := 15;
3419 END IF;
3420 x_operation := jta_sync_task_common.g_modify;
3421
3422 CLOSE c_task_status;
3423
3424 END IF;
3425 END transformstatus;
3426
3427 FUNCTION getchangedstatusid (
3428 p_task_status_id IN NUMBER,
3429 p_source_object_type_code IN VARCHAR2
3430 )
3431 RETURN NUMBER
3432 IS
3433 BEGIN
3434 IF (p_source_object_type_code = G_APPOINTMENT)
3435 THEN
3436 RETURN p_task_status_id;
3437 ELSE
3438 IF (checkuserstatusrule ())
3439 THEN
3440 RETURN fnd_api.g_miss_num;
3441 ELSE
3442 RETURN p_task_status_id;
3443 END IF;
3444 END IF;
3445 END getchangedstatusid;
3446
3447
3448 FUNCTION checkUserStatusRule
3449 RETURN BOOLEAN
3450 IS
3451 l_num NUMBER;
3452 BEGIN
3453 IF G_USER_STATUS_RULE IS NULL
3454 THEN
3455 SELECT 1 INTO l_num
3456 FROM
3457 fnd_user
3458 ,fnd_user_resp_groups
3459 ,jtf_state_rules_b
3460 , jtf_state_responsibilities
3461 WHERE fnd_user.user_id = fnd_global.user_id
3462 AND fnd_user.user_id = fnd_user_resp_groups.user_id
3463 AND fnd_user_resp_groups.responsibility_id = jtf_state_responsibilities.responsibility_id
3464 AND jtf_state_responsibilities.rule_id = jtf_state_rules_b.rule_id;
3465 G_USER_STATUS_RULE := TRUE;
3466 RETURN TRUE;
3467 ELSE
3468 RETURN G_USER_STATUS_RULE;
3469 END IF;
3470
3471 EXCEPTION
3472 WHEN no_data_found
3473 THEN
3474 G_USER_STATUS_RULE := FALSE;
3475 RETURN FALSE;
3476 WHEN too_many_rows
3477 THEN
3478 G_USER_STATUS_RULE := TRUE;
3479 RETURN TRUE;
3480 END checkUserStatusRule;
3481
3482 -- Added to fix bug 2382927
3483 FUNCTION validate_syncid(p_syncid IN NUMBER)
3484 RETURN BOOLEAN
3485 IS
3486 CURSOR c_mapping (b_syncid NUMBER) IS
3487 SELECT 1
3488 FROM jta_sync_task_mapping
3489 WHERE task_sync_id = b_syncid;
3490
3491 l_dummy NUMBER;
3492 l_valid BOOLEAN := TRUE;
3493 BEGIN
3494
3495 ---------------------------------
3496 -- Fix Bug# 2395004
3497 IF NVL(p_syncid,-1) < 1
3498 THEN
3499 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
3500 fnd_msg_pub.add;
3501
3502 fnd_message.set_name('JTF', 'JTA_SYNC_INVALID_SYNCID');
3503 fnd_message.set_token('PROC_NAME','JTA_SYNC_TASK_COMMON.GET_TASK_ID');
3504 fnd_msg_pub.add;
3505
3506 raise_application_error (-20100,jta_sync_common.get_messages);
3507 END IF;
3508 ---------------------------------
3509
3510 OPEN c_mapping (p_syncid);
3511 FETCH c_mapping INTO l_dummy;
3512 IF c_mapping%NOTFOUND
3513 THEN
3514 l_valid := FALSE;
3515 END IF;
3516 CLOSE c_mapping;
3517
3518 RETURN l_valid;
3519 END validate_syncid;
3520
3521 END jta_sync_task_common;