[Home] [Help]
PACKAGE BODY: APPS.CAC_SYNC_TASK_COMMON
Source
1 PACKAGE BODY CAC_SYNC_TASK_COMMON AS
2 /* $Header: cacvstcb.pls 120.63.12010000.1 2008/07/24 18:03:24 appldev 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 | 04-Nov-2004 sachoudh Created. |
19 | 01-FEB-2005 rhshriva Changed update_existing_data |
20 | 03-FEB-2004 rhshriva Changed create_new_data and do_mapping |
21 | 26-SEP-2005 deeprao Changed delete_task_data |
22 | and delete_bookings, added delete_tasks |
23 *=======================================================================*/
24
25 g_fb_type_changed boolean := false;
26
27 PROCEDURE check_span_days (
28 p_source_object_type_code IN VARCHAR2,
29 p_calendar_start_date IN DATE,
30 p_calendar_end_date IN DATE,
31 p_task_id IN NUMBER,
32 p_entity IN VARCHAR2,
33 x_status OUT NOCOPY BOOLEAN
34 )
35 IS
36
37 --cursor to check if the task is recurring once every more than one year
38 cursor getTaskRecur(b_task_id IN NUMBER) is
39
40 SELECT 1
41 FROM jtf_task_recur_rules jtrr, jtf_tasks_b jtb
42 WHERE ((jtrr.occurs_uom ='YER' AND
43 jtrr.occurs_every > 1)
44 OR
45 (jtrr.occurs_uom ='MON'
46 AND (DECODE(sunday,'Y',1,0) + DECODE(monday,'Y',1,0) + DECODE(tuesday,'Y',1,0)
47 + DECODE(wednesday,'Y',1,0) + DECODE(thursday,'Y',1,0) + DECODE(friday,'Y',1,0)
48 + DECODE(saturday,'Y',1,0)) > 1)) AND
49 jtb.recurrence_rule_id=jtrr.recurrence_rule_id
50 AND jtb.task_id=b_task_id;
51
52 l_temp NUMBER;
53
54
55
56 BEGIN
57 -------------------------------------------
58 -- Returns TRUE:
59 -- 1) if an appointment spans over a year
60 -- 2) if a task is endless and parameter G_CAC_SYNC_TASK_NO_DATE is set to no
61 -------------------------------------------
62 x_status := FALSE;
63
64 open getTaskRecur(p_task_id);
65 fetch getTaskRecur into l_temp;
66
67
68 IF (p_entity=G_TASK --source_object_type_code = G_TASK
69 AND p_calendar_end_date IS NULL AND
70 G_CAC_SYNC_TASK_NO_DATE ='N'
71 )
72 OR
73 ( p_entity = G_APPOINTMENT AND getTaskRecur%FOUND )
74
75 THEN
76 x_status := TRUE;
77 END IF;
78
79 if (getTaskRecur%ISOPEN) then
80 close getTaskRecur;
81 END IF;
82
83 END check_span_days;
84
85 FUNCTION convert_carriage_return(
86 p_subject IN VARCHAR2
87 ,p_type IN VARCHAR2)
88 RETURN VARCHAR2
89 IS
90 l_from VARCHAR2(10);
91 l_to VARCHAR2(10);
92 BEGIN
93 IF p_type = 'ORACLE'
94 THEN
95 l_from := G_CARRIAGE_RETURN_XML;
96 l_to := G_CARRIAGE_RETURN_ORACLE;
97 ELSE
98 l_from := G_CARRIAGE_RETURN_ORACLE;
99 l_to := G_CARRIAGE_RETURN_XML;
100 END IF;
101
102 RETURN REPLACE(p_subject, l_from ,l_to);
103 END convert_carriage_return;
104
105 FUNCTION get_subject(p_subject IN VARCHAR2
106 ,p_type IN VARCHAR2)
107 RETURN VARCHAR2
108 IS
109 l_from VARCHAR2(10);
110 l_to VARCHAR2(10);
111 BEGIN
112 RETURN SUBSTR(convert_carriage_return(p_subject,p_type), 1, 80);
113 END get_subject;
114
115 PROCEDURE convert_recur_date_to_gmt (
116 p_timezone_id IN NUMBER,
117 p_base_start_date IN DATE,
118 p_base_end_date IN DATE,
119 p_start_date IN DATE,
120 p_end_date IN DATE,
121 p_item_display_type IN NUMBER,
122 p_occurs_which IN NUMBER,
123 p_uom IN VARCHAR2,
124 x_date_of_month OUT NOCOPY NUMBER,
125 x_start_date IN OUT NOCOPY DATE,
126 x_end_date IN OUT NOCOPY DATE
127 )
128 IS
129 l_start_date VARCHAR2(11); -- DD-MON-YYYY
130 l_start_time VARCHAR2(8); -- HH24:MI:SS
131 l_end_date VARCHAR2(11); -- DD-MON-YYYY
132 l_end_time VARCHAR2(8); -- HH24:MI:SS
133 BEGIN
134 l_start_date := TO_CHAR (p_start_date, 'DD-MON-YYYY');
135 l_start_time := TO_CHAR (p_base_start_date, 'HH24:MI:SS');
136 l_end_date := TO_CHAR (p_end_date, 'DD-MON-YYYY');
137 l_end_time := TO_CHAR (p_base_end_date, 'HH24:MI:SS');
138
139 IF p_item_display_type <> 3 THEN
140 x_start_date :=
141 convert_task_to_gmt (
142 TO_DATE (
143 l_start_date || ' ' || l_start_time,
144 'DD-MON-YYYY HH24:MI:SS'
145 ),
146 p_timezone_id
147 );
148
149 /* x_end_date :=
150 convert_task_to_gmt (
151 TO_DATE (
152 l_end_date || ' ' || l_end_time,
153 'DD-MON-YYYY HH24:MI:SS'
154 ),
155 p_timezone_id
156 );*/
157 x_end_date := convert_task_to_gmt ( p_end_date, p_timezone_id );
158 ELSE
159 x_start_date := TO_DATE (
160 l_start_date || ' ' || l_start_time,
161 'DD-MON-YYYY HH24:MI:SS');
162 x_end_date :=TO_DATE (
163 l_end_date || ' ' || l_end_time,
164 'DD-MON-YYYY HH24:MI:SS'
165 );
166 END IF;
167 /* x_start_date := TRUNC (x_start_date);
168 x_end_date := TRUNC (x_end_date);*/--no trucation of dates hsould be done
169 --dates should have time component on it.please refer to bug 4261252.
170
171
172
173 IF p_occurs_which IS NULL
174 AND (p_uom = 'MON' OR p_uom ='YER') THEN
175 x_date_of_month := TO_CHAR (x_start_date, 'DD');
176 END IF;
177 END convert_recur_date_to_gmt;
178
179 PROCEDURE process_exclusions (
180 p_exclusion_tbl IN OUT NOCOPY cac_sync_task.exclusion_tbl,
181 p_rec_rule_id IN NUMBER,
182 p_repeating_task_id IN NUMBER,
183 p_task_rec IN OUT NOCOPY cac_sync_task.task_rec
184 )
185 IS
186 i NUMBER := 0;
187 l_exclude_task_id NUMBER ;
188 l_temp NUMBER;
189
190 CURSOR exclusion_exists(b_exclude_task_id IN NUMBER)
191 IS
192 SELECT task_id FROM jta_task_exclusions WHERE
193 task_id=b_exclude_task_id;
194 l_exclusion_exists exclusion_exists%ROWTYPE;
195 l_update_exclusion BOOLEAN:=FALSE;
196
197 BEGIN
198 FOR i IN p_exclusion_tbl.FIRST .. p_exclusion_tbl.LAST
199 LOOP
200 l_exclude_task_id := get_excluding_taskid (
201 p_sync_id => p_task_rec.syncid,
202 p_recurrence_rule_id => p_rec_rule_id,
203 p_exclusion_rec => p_exclusion_tbl (i)
204 );
205
206 IF l_exclude_task_id > 0
207 THEN
208
209
210 OPEN exclusion_exists(l_exclude_task_id);
211
212 FETCH exclusion_exists INTO l_exclusion_exists;
213
214 IF (exclusion_exists%FOUND) THEN
215 l_update_exclusion:=TRUE;
216 ELSE
217 l_update_exclusion:=FALSE;
218 END IF;
219 IF (exclusion_exists%isopen) THEN
220 CLOSE exclusion_exists;
221 END IF;
222
223
224 if ((p_exclusion_tbl(i).eventType <> g_delete) ) then
225 --creating exclusion
226
227
228 if (l_update_exclusion) then
229
230 delete from jta_task_exclusions
231 where task_id=l_exclude_task_id
232 and recurrence_rule_id=p_rec_rule_id;
233
234 end if;-- if (l_update_exclusion) then
235
236
237
238 create_updation_record
239 (p_exclusion =>p_exclusion_tbl(i),
240 p_task_rec =>p_task_rec ,
241 p_exclude_task_id =>l_exclude_task_id,
242 p_rec_rule_id=>p_rec_rule_id);
243 --deleting instance
244
245 else
246 delete_exclusion_task (
247 p_repeating_task_id => l_exclude_task_id,
248 x_task_rec => p_task_rec
249 );
250
251 end if;
252
253 END IF; -- l_task_id
254 END LOOP;
255 END process_exclusions;
256
257 procedure create_updation_record
258 (p_exclusion IN OUT NOCOPY cac_sync_task.exclusion_rec,
259 p_task_rec IN cac_sync_task.task_rec ,
260 p_exclude_task_id IN NUMBER,
261 p_rec_rule_id IN NUMBER )
262
263 is
264 CURSOR getCollabDetails(b_task_id NUMBER) IS
265 SELECT COLLAB_ID, MEETING_MODE,MEETING_ID,MEETING_URL,JOIN_URL ,
266 PLAYBACK_URL ,DOWNLOAD_URL ,CHAT_URL ,IS_STANDALONE_LOCATION,DIAL_IN
267 FROM CAC_VIEW_COLLAB_DETAILS_VL
268 WHERE task_id=b_task_id;
269
270 l_collab_details getCollabDetails%ROWTYPE;
271
272 l_return_status VARCHAR2(1);
273 l_msg_count NUMBER;
274 l_msg_data VARCHAR2(2000);
275 p_updation_record jtf_task_repeat_appt_pvt.updated_field_rec;
276 l_ovn NUMBER;
277 l_location CAC_VIEW_COLLAB_DETAILS_TL.LOCATION%TYPE;
278 l_alarm_days NUMBER;
279 l_alarm_mins NUMBER;
280
281 Begin
282 --no timexone conversion is done. Need to do timezone conversion.
283 p_updation_record.task_id :=p_exclude_task_id;
284 -- p_updation_record.task_name :=p_exclusion.subject;
285 p_updation_record.description:=p_exclusion.description;
286 p_updation_record.task_status_id :=p_exclusion.statusId;
287 p_updation_record.task_priority_id :=p_exclusion.priorityId;
288 -- p_updation_record.owner_type_code :=p_task_rec.resourcetype;
289 -- p_updation_record.owner_id :=p_task_rec.resourceid;
290 get_owner_info(p_task_id =>p_exclude_task_id,
291 x_task_name =>p_updation_record.task_name,
292 x_owner_id =>p_updation_record.owner_id,
293 x_owner_type_code =>p_updation_record.owner_type_code );
294
295 p_updation_record.task_name :=p_exclusion.subject;
296
297 IF (p_exclusion.objectcode <> G_TASK)
298 THEN
299 IF (p_exclusion.alarmflag = 'Y')
300 THEN
301 l_alarm_days :=
302 p_exclusion.plannedstartdate - p_exclusion.alarmdate;
303 l_alarm_mins := ROUND (l_alarm_days * 1440, 0);
304 ELSE
305 l_alarm_mins := NULL;
306 END IF;
307 ELSE
308 l_alarm_mins := NULL;
309 END IF;
310
311 if ( p_exclude_task_id is not null) then
312
313 p_updation_record.planned_start_date :=convert_gmt_to_task (p_exclusion.plannedstartdate, p_exclude_task_id);
314 p_updation_record.planned_end_date :=convert_gmt_to_task (p_exclusion.plannedenddate, p_exclude_task_id);
315 p_updation_record.scheduled_start_date:=convert_gmt_to_task (p_exclusion.scheduledstartdate, p_exclude_task_id);
316 p_updation_record.scheduled_end_date :=convert_gmt_to_task (p_exclusion.scheduledenddate, p_exclude_task_id);
317 p_updation_record.actual_end_date :=convert_gmt_to_task (p_exclusion.actualenddate, p_exclude_task_id); -- DATE DEFAULT fnd_api.g_miss_date,
318 p_updation_record.actual_start_date :=convert_gmt_to_task (p_exclusion.actualstartdate, p_exclude_task_id); -- DATE
319 p_updation_record.old_calendar_start_date:=convert_gmt_to_task (p_exclusion.exclusion_date,p_exclude_task_id); --.plannedstartdate;
320 p_updation_record.new_calendar_start_date:=convert_gmt_to_task (p_exclusion.plannedstartdate,p_exclude_task_id);
321 p_updation_record.new_calendar_end_date:=convert_gmt_to_task (p_exclusion.plannedenddate,p_exclude_task_id);
322
323
324 else
325
326
327 p_updation_record.planned_start_date :=convert_gmt_to_server (p_exclusion.plannedstartdate);
328 p_updation_record.planned_end_date :=convert_gmt_to_server (p_exclusion.plannedenddate);
329 p_updation_record.scheduled_start_date:=convert_gmt_to_server (p_exclusion.scheduledstartdate);
330 p_updation_record.scheduled_end_date :=convert_gmt_to_server (p_exclusion.scheduledenddate);
331 p_updation_record.actual_end_date :=convert_gmt_to_server (p_exclusion.actualenddate); -- DATE DEFAULT fnd_api.g_miss_date,
332 p_updation_record.actual_start_date :=convert_gmt_to_server (p_exclusion.actualstartdate); -- DATE
333 p_updation_record.old_calendar_start_date:=convert_gmt_to_server (p_exclusion.exclusion_date); --.plannedstartdate;
334 p_updation_record.new_calendar_start_date:=convert_gmt_to_server (p_exclusion.plannedstartdate);
335 p_updation_record.new_calendar_end_date:=convert_gmt_to_server (p_exclusion.plannedenddate);
336
337 end if;
338
339 p_updation_record.timezone_id :=get_task_timezone_id(p_task_id=>p_exclude_task_id);
340
341 p_updation_record.private_flag :=p_exclusion.privateflag ; -- jtf_tasks_b.private_flag%TYPE DEFAULT fnd_api.g_miss_char,
342 p_updation_record.alarm_on:=p_exclusion.alarmflag; -- NUMBER DEFAULT fnd_api.g_miss_num,
343 p_updation_record.change_mode:=jtf_task_repeat_appt_pvt.G_ONE;
344 p_updation_record.recurrence_rule_id:=p_rec_rule_id;
345 p_updation_record.free_busy_type :=p_exclusion.free_busy_type;
346 p_updation_record.alarm_start :=l_alarm_mins;
347
348 l_ovn:=get_ovn(p_task_id=>p_exclude_task_id);
349
350 jtf_task_repeat_appt_pvt.update_repeat_appointment(
351 p_api_version =>1.0,
352 p_init_msg_list =>fnd_api.g_false,
353 p_commit =>fnd_api.g_false,
354 p_object_version_number =>l_ovn,
355 p_updated_field_rec =>p_updation_record ,
356 x_return_status =>l_return_status,
357 x_msg_count =>l_msg_count,
358 x_msg_data =>l_msg_data
359 ) ;
360
361
362 IF NOT cac_sync_common.is_success (l_return_status)
363 THEN-- Failed to update a task
364
365 cac_sync_common.put_message_to_excl_record (
366 p_exclusion_rec=>p_exclusion,
367 p_status => 2,
368 p_user_message => 'JTA_SYNC_UPDATE_TASK_FAIL'
369 );
370 else
371 --add collabsuite details
372 -- Start Fix for bug #4687069
373 -- Location was not getting updated if added a location to an occurence
374 -- which made it an exclusion.
375 -- Added updated of collab details for exclusions also.
376
377 OPEN getCollabDetails(p_updation_record.task_id);
378
379 FETCH getCollabDetails INTO l_collab_details;
380
381 -- Update the rows only if there are some information in the CAC_VIEW_COLLAB_DETAILS table
382 --otherwise close the cursor.
383 IF (getCollabDetails%FOUND) THEN
384
385 l_location := SUBSTRB(p_exclusion.locations,1,100);
386
387 cac_view_collab_details_pkg.update_row
388 (x_collab_id=> l_collab_details.collab_id ,
389 x_task_id=> p_updation_record.task_id,
390 x_meeting_mode=>l_collab_details.meeting_mode,
391 x_meeting_id=>l_collab_details.meeting_id,
392 x_meeting_url=>l_collab_details.meeting_url,
393 x_join_url=>l_collab_details.join_url,
394 x_playback_url=>l_collab_details.playback_url,
395 x_download_url=>l_collab_details.download_url,
396 x_chat_url=>l_collab_details.chat_url,
397 x_is_standalone_location=>l_collab_details.is_standalone_location,
398 x_location=>l_location,
399 x_dial_in=>p_exclusion.dial_in,
400 x_last_update_date=>SYSDATE,
401 x_last_updated_by=>jtf_task_utl.updated_by,
402 x_last_update_login=>jtf_task_utl.login_id);
403
404 END IF;
405
406
407 if (getCollabDetails%ISOPEN) then
408 CLOSE getCollabDetails;
409 end if;
410
411 -- End Fix for bug #4687069
412
413
414
415
416 END IF;
417 end create_updation_record;
418
419 FUNCTION get_default_task_type
420 RETURN NUMBER
421 IS
422 BEGIN
423 RETURN NVL (
424 fnd_profile.VALUE ('JTF_TASK_DEFAULT_TASK_TYPE'),
425 g_task_type_general
426 );
427 END;
428
429 FUNCTION is_this_new_task (p_sync_id IN NUMBER)
430 RETURN BOOLEAN
431 IS
432 CURSOR c_synctask
433 IS
434 SELECT task_id
435 FROM jta_sync_task_mapping
436 WHERE task_sync_id = p_sync_id;
437
438 l_task_id NUMBER;
439 BEGIN
440 IF p_sync_id IS NULL OR
441 p_sync_id < 1
442 THEN
443 --fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
444 -- fnd_msg_pub.add;
445
446 -- fnd_message.set_name('JTF', 'JTA_SYNC_INVALID_SYNCID');
447 -- fnd_message.set_token('PROC_NAME','cac_sync_task_COMMON.IS_THIS_NEW_TASK');
448 -- fnd_msg_pub.add;
449
450 -- raise_application_error (-20100,cac_sync_common.get_messages);
451 RETURN TRUE;
452 END IF;
453
454 OPEN c_synctask;
455 FETCH c_synctask INTO l_task_id;
456
457 IF c_synctask%NOTFOUND
458 THEN
459 CLOSE c_synctask;
460 RETURN TRUE;
461 ELSE
462 CLOSE c_synctask;
463 RETURN FALSE;
464 END IF;
465 END;
466
467 -- count num of exclusions from jta_task_exclusion
468 FUNCTION count_exclusions (p_recurrence_rule_id IN NUMBER)
469 RETURN NUMBER
470 IS
471 l_count NUMBER;
472 BEGIN
473 SELECT COUNT (recurrence_rule_id)
474 INTO l_count
475 FROM jta_task_exclusions
476 WHERE recurrence_rule_id = p_recurrence_rule_id;
477 RETURN l_count;
478 END count_exclusions;
479
480 FUNCTION count_excluded_tasks (p_recurrence_rule_id IN NUMBER)
481 RETURN NUMBER
482 IS
483 l_count NUMBER;
484 BEGIN
485 SELECT COUNT (recurrence_rule_id)
486 INTO l_count
487 FROM jtf_tasks_b
488 WHERE recurrence_rule_id = p_recurrence_rule_id;
489 RETURN l_count;
490 END count_excluded_tasks;
491
492 FUNCTION check_for_exclusion (
493 p_sync_id IN NUMBER,
494 p_exclusion_tbl IN OUT NOCOPY cac_sync_task.exclusion_tbl,
495 p_calendar_start_date IN DATE,
496 p_client_time_zone_id IN NUMBER
497 )
498 RETURN BOOLEAN
499 IS
500 is_exclusion BOOLEAN;
501 l_task_date DATE;
502 BEGIN
503 IF (p_exclusion_tbl.COUNT = 0)
504 OR (p_exclusion_tbl IS NULL)
505 THEN
506 RETURN FALSE;
507 ELSE
508 is_exclusion := FALSE;
509
510 FOR i IN p_exclusion_tbl.FIRST .. p_exclusion_tbl.LAST
511 LOOP
512 l_task_date := p_calendar_start_date;
513
514 IF (p_sync_id = p_exclusion_tbl (i).syncid)
515 AND (TRUNC (l_task_date) =
516 TRUNC (p_exclusion_tbl (i).exclusion_date))
517 THEN
518 is_exclusion := TRUE;
519 EXIT;
520 END IF;
521 END LOOP; --end of the loop
522
523 RETURN is_exclusion;
524 END IF;
525 END;
526
527 FUNCTION get_excluding_taskid (
528 p_sync_id IN NUMBER,
529 p_recurrence_rule_id IN NUMBER,
530 p_exclusion_rec IN OUT NOCOPY cac_sync_task.exclusion_rec
531 )
532 RETURN NUMBER
533 IS
534 CURSOR c_recur_tasks (b_recurrence_rule_id NUMBER,
535 b_exclusion_start_date DATE)
536 IS
537 SELECT task_id
538 FROM jtf_tasks_b
539 WHERE recurrence_rule_id = b_recurrence_rule_id
540 AND TRUNC (calendar_start_date) = TRUNC (b_exclusion_start_date);
541 l_sync_task_id NUMBER;
542 l_task_id NUMBER;
543 BEGIN
544
545
546 l_sync_task_id:=get_task_id(p_sync_id=>p_sync_id);
547
548
549 OPEN c_recur_tasks (
550 b_recurrence_rule_id => p_recurrence_rule_id,
551 b_exclusion_start_date => convert_gmt_to_task(p_exclusion_rec.exclusion_date,l_sync_task_id)
552 );
553 FETCH c_recur_tasks INTO l_task_id;
554
555 IF c_recur_tasks%NOTFOUND
556 THEN
557 l_task_id := -9;
558 END IF;
559
560 CLOSE c_recur_tasks;
561
562 RETURN l_task_id;
563 END;
564
565 FUNCTION set_alarm_date (
566 p_task_id IN NUMBER,
567 p_request_type IN VARCHAR2,
568 p_scheduled_start_date IN DATE,
569 p_planned_start_date IN DATE,
570 p_actual_start_date IN DATE,
571 p_alarm_flag IN VARCHAR2,
572 p_alarm_start IN NUMBER
573 )
574 RETURN DATE
575 IS
576 l_date_selected VARCHAR2(1);
577 l_date DATE;
578 l_alarm_date DATE;
579 l_alarm_days NUMBER;
580
581 CURSOR c_dateselect
582 IS
583 SELECT jt.date_selected
584 FROM jtf_tasks_b jt
585 WHERE jt.task_id = p_task_id;
586 --check for alarm flag
587
588 BEGIN
589 IF p_alarm_flag = 'Y'
590 THEN
591 OPEN c_dateselect;
592 FETCH c_dateselect INTO l_date_selected;
593
594 IF c_dateselect%NOTFOUND
595 OR l_date_selected = 'P'
596 OR p_request_type = G_REQ_APPOINTMENT
597 THEN
598 l_date := p_planned_start_date;
599 ELSIF l_date_selected = 'S'
600 THEN
601 l_date := p_scheduled_start_date;
602 ELSIF l_date_selected = 'A'
603 THEN
604 l_date := p_actual_start_date;
605 END IF;
606
607 CLOSE c_dateselect;
608 l_alarm_days := p_alarm_start / 1440;
609 l_alarm_date := l_date - l_alarm_days;
610 END IF;
611
612 RETURN l_alarm_date;
613 END;
614
615 FUNCTION get_task_id (p_sync_id IN NUMBER)
616 RETURN NUMBER
617 IS
618 CURSOR c_task_sync
619 IS
620 SELECT task_id
621 FROM jta_sync_task_mapping
622 WHERE task_sync_id = p_sync_id;
623
624 l_task_id NUMBER;
625 BEGIN
626 IF p_sync_id IS NULL
627 OR p_sync_id < 1
628 THEN
629 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
630 fnd_msg_pub.add;
631
632 fnd_message.set_name('JTF', 'JTA_SYNC_INVALID_SYNCID');
633 fnd_message.set_token('PROC_NAME','cac_sync_task_COMMON.GET_TASK_ID');
634 fnd_msg_pub.add;
635
636 raise_application_error (-20100,cac_sync_common.get_messages);
637
638 END IF;
639
640 OPEN c_task_sync;
641 FETCH c_task_sync INTO l_task_id;
642
643 IF c_task_sync%NOTFOUND
644 THEN
645 CLOSE c_task_sync;
646
647 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
648 fnd_msg_pub.add;
649
650 fnd_message.set_name('JTF', 'JTA_SYNC_NOTFOUND_TASKID');
651 fnd_message.set_token('PROC_NAME','cac_sync_task_COMMON.GET_TASK_ID');
652 fnd_msg_pub.add;
653
654 raise_application_error (-20100,cac_sync_common.get_messages);
655 ELSIF l_task_id IS NULL
656 THEN
657 CLOSE c_task_sync;
658
659 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
660 fnd_msg_pub.add;
661
662 fnd_message.set_name('JTF', 'JTA_SYNC_NULL_TASKID');
663 fnd_message.set_token('PROC_NAME','cac_sync_task_COMMON.GET_TASK_ID');
664 fnd_msg_pub.add;
665
666 raise_application_error (-20100,cac_sync_common.get_messages);
667 END IF;
668
669 CLOSE c_task_sync;
670 RETURN l_task_id;
671 END;
672
673 FUNCTION get_task_id (p_task_assignment_id IN NUMBER)
674 RETURN NUMBER
675 IS
676 CURSOR c_task
677 IS
678 SELECT task_id
679 FROM jtf_task_all_assignments
680 WHERE task_assignment_id = p_task_assignment_id;
681
682 l_task_id NUMBER;
683 BEGIN
684 OPEN c_task;
685 FETCH c_task INTO l_task_id;
686
687 IF c_task%NOTFOUND
688 THEN
689 CLOSE c_task;
690
691 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
692 fnd_msg_pub.add;
693
694 fnd_message.set_name('JTF', 'JTA_SYNC_NOTFOUND_TASKID');
695 fnd_message.set_token('PROC_NAME','cac_sync_task_COMMON.GET_TASK_ID');
696 fnd_msg_pub.add;
697
698 raise_application_error (-20100,cac_sync_common.get_messages);
699 END IF;
700
701 CLOSE c_task;
702 RETURN l_task_id;
703 END;
704
705 FUNCTION get_task_timezone_id (p_task_id IN NUMBER)
706 RETURN NUMBER
707 IS
708 CURSOR c_task_timezone
709 IS
710 SELECT timezone_id
711 FROM jtf_tasks_b
712 WHERE task_id = p_task_id;
713
714 l_task_timezone_id NUMBER;
715 BEGIN
716 OPEN c_task_timezone;
717 FETCH c_task_timezone INTO l_task_timezone_id;
718
719 IF c_task_timezone%NOTFOUND
720 THEN
721 CLOSE c_task_timezone;
722
723 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
724 fnd_msg_pub.add;
725
726 fnd_message.set_name('JTF', 'JTA_SYNC_TIMEZONEID_NOTFOUND');
727 fnd_message.set_token('PROC_NAME','cac_sync_task_COMMON.GET_TASK_TIMEZONE_ID');
728 fnd_msg_pub.add;
729
730 raise_application_error (-20100,cac_sync_common.get_messages);
731
732 END IF;
733
734 CLOSE c_task_timezone;
735
736 IF l_task_timezone_id IS NULL
737 THEN
738 l_task_timezone_id :=
739 NVL (fnd_profile.VALUE ('CLIENT_TIMEZONE_ID'), 0);
740 END IF;
741
742 RETURN l_task_timezone_id;
743 END;
744
745 FUNCTION get_ovn (p_task_id IN NUMBER)
746 RETURN NUMBER
747 IS
748 CURSOR c_tasks_ovn (b_task_id NUMBER)
749 IS
750 SELECT object_version_number
751 FROM jtf_tasks_b
752 WHERE task_id = b_task_id;
753
754 l_object_version_number NUMBER;
755 BEGIN
756 OPEN c_tasks_ovn (p_task_id);
757 FETCH c_tasks_ovn into l_object_version_number;
758
759 IF c_tasks_ovn%NOTFOUND
760 THEN
761 CLOSE c_tasks_ovn;
762
763 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
764 fnd_msg_pub.add;
765
766 fnd_message.set_name('JTF', 'cac_sync_task_OVN_NOTFOUND');
767 fnd_message.set_token('PROC_NAME','cac_sync_task_COMMON.GET_OVN');
768 fnd_msg_pub.add;
769
770 raise_application_error (-20100,cac_sync_common.get_messages);
771 END IF;
772
773 CLOSE c_tasks_ovn;
774 RETURN l_object_version_number;
775 END get_ovn;
776
777 FUNCTION get_ovn (p_task_assignment_id IN NUMBER)
778 RETURN NUMBER
779 IS
780 CURSOR c_assignment_ovn (b_task_assignment_id NUMBER)
781 IS
782 SELECT object_version_number
783 FROM jtf_task_all_assignments
784 WHERE task_assignment_id = b_task_assignment_id;
785
786 l_object_version_number NUMBER;
787 BEGIN
788 OPEN c_assignment_ovn (p_task_assignment_id);
789 FETCH c_assignment_ovn into l_object_version_number;
790
791 IF c_assignment_ovn%NOTFOUND
792 THEN
793 CLOSE c_assignment_ovn;
794
795 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
796 fnd_msg_pub.add;
797
798 fnd_message.set_name('JTF', 'JTA_SYNC_ASSIGNMT_OVN_NOTFOUND');
799 fnd_message.set_token('PROC_NAME','cac_sync_task_COMMON.GET_OVN');
800 fnd_msg_pub.add;
801
802 raise_application_error (-20100,cac_sync_common.get_messages);
803 END IF;
804
805 CLOSE c_assignment_ovn;
806 RETURN l_object_version_number;
807 END get_ovn;
808
809 PROCEDURE get_resource_details (
810 x_resource_id OUT NOCOPY NUMBER,
811 x_resource_type OUT NOCOPY VARCHAR2
812 )
813 IS
814 CURSOR c_resource
815 IS
816 SELECT resource_id, 'RS_' || category
817 FROM jtf_rs_resource_extns
818 WHERE user_id = fnd_global.user_id;
819 BEGIN
820 OPEN c_resource;
821 FETCH c_resource INTO x_resource_id, x_resource_type;
822
823 IF c_resource%NOTFOUND
824 THEN
825 CLOSE c_resource;
826
827 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
828 fnd_msg_pub.add;
829
830 fnd_message.set_name('JTF', 'JTA_SYNC_RESOURCE_NOTFOUND');
831 fnd_message.set_token('PROC_NAME','cac_sync_task_COMMON.GET_RESOURCE_DETAILS');
832 fnd_msg_pub.add;
833
834 raise_application_error (-20100,cac_sync_common.get_messages);
835 END IF;
836
837 CLOSE c_resource;
838
839 END get_resource_details;
840
841
842
843 PROCEDURE do_mapping(p_task_id IN NUMBER,
844 p_principal_id IN NUMBER,
845 p_operation IN VARCHAR2,
846 x_task_sync_id IN OUT NOCOPY NUMBER
847 )
848 IS
849 BEGIN
850 IF (p_operation = g_new)
851 THEN
852 IF (x_task_sync_id IS NULL) OR (x_task_sync_id <= 0)
853 THEN
854 SELECT jta_sync_task_mapping_s.nextval
855 INTO x_task_sync_id
856 FROM dual;
857 END IF;
858
859 cac_sync_task_map_pkg.insert_row (
860 p_task_sync_id => x_task_sync_id,
861 p_task_id => p_task_id,
862 p_resource_id => cac_sync_task.g_login_resource_id,
863 p_principal_id => p_principal_id
864 );
865 ELSIF p_operation = g_modify
866 THEN
867 cac_sync_task_map_pkg.update_row (
868 p_task_sync_id => x_task_sync_id,
869 p_task_id => p_task_id,
870 p_resource_id => cac_sync_task.g_login_resource_id,
871 p_principal_id => p_principal_id
872 );
873 /* ELSIF p_operation = G_DELETE
874 THEN
875 cac_sync_task_map_pkg.delete_row (
876 p_task_sync_id => x_task_sync_id
877 );*/
878
879 END IF;
880 END do_mapping;
881
882 /*PROCEDURE get_event_type (
883 p_deleted_flag IN VARCHAR2,
884 p_task_sync_id IN NUMBER,
885 p_source_object_type_code IN VARCHAR2,
886 p_calendar_start_date IN DATE,
887 p_calendar_end_date IN DATE,
888 p_assignment_status_id IN NUMBER,
889 x_operation OUT NOCOPY VARCHAR2
890 )
891 IS
892 l_deleted_flag VARCHAR2(1) := NVL (p_deleted_flag, 'N');
893 l_task_sync_id NUMBER := p_task_sync_id;
894 l_calendar_start_date DATE := p_calendar_start_date;
895 l_calendar_end_date DATE := p_calendar_end_date;
896 BEGIN
897 -- For task, we sync a task with the spanned day
898 -- For Appt, we don't a task with the spanned day
899 IF l_calendar_start_date IS NOT NULL AND
900 (l_calendar_end_date IS NULL OR
901 (p_source_object_type_code = G_APPOINTMENT AND
902 trunc(l_calendar_start_date) <> trunc(l_calendar_end_date))
903 )
904 THEN
905 IF l_task_sync_id IS NOT NULL
906 THEN
907 x_operation := G_DELETE;
908 END IF;
909 RETURN;
910 END IF;
911
912 IF l_task_sync_id IS NOT NULL
913 THEN
914 IF l_deleted_flag = 'Y' OR
915 p_assignment_status_id = 4 -- Rejected
916 THEN
917 x_operation := G_DELETE;
918 ELSE -- l_deleted_flag = 'N'
919 x_operation := G_MODIFY;
920 END IF;
921 ELSE -- l_task_sync_id IS NULL
922 IF l_deleted_flag = 'N' AND
923 nvl(p_assignment_status_id,-1) <> 4 -- Not Rejected
924 THEN
925 x_operation := G_NEW;
926 END IF;
927 END IF;
928 END get_event_type;
929 */
930 FUNCTION get_group_team_tasks (p_resource_id IN NUMBER)
931 RETURN resource_list_tbl
932 IS
933 CURSOR c_group_id (b_resource_id IN VARCHAR2)
934 IS
935 SELECT group_id resource_id
936 FROM jtf_rs_group_members
937 WHERE resource_id = b_resource_id
938 AND delete_flag <> 'Y';
939
940 CURSOR c_team_id (b_resource_id IN VARCHAR2)
941 IS
942 SELECT team_id resource_id
943 FROM jtf_rs_team_members
944 WHERE team_resource_id = b_resource_id
945 AND delete_flag <> 'Y';
946
947 l_group_resource_tbl resource_list_tbl;
948 i BINARY_INTEGER := 0;
949 BEGIN
950 FOR r_resources IN c_group_id (b_resource_id => p_resource_id)
951 LOOP
952 i := i + 1;
953 l_group_resource_tbl (i).resource_id := r_resources.resource_id;
954 l_group_resource_tbl (i).resource_type := 'RS_GROUP';
955 END LOOP;
956
957 FOR r_resources IN c_team_id (b_resource_id => p_resource_id)
958 LOOP
959 i := i + 1;
960 l_group_resource_tbl (i).resource_id := r_resources.resource_id;
961 l_group_resource_tbl (i).resource_type := 'RS_TEAM';
962 END LOOP;
963
964 RETURN l_group_resource_tbl;
965 END get_group_team_tasks;
966
967 FUNCTION get_group_calendar (p_resource_id IN NUMBER)
968 RETURN resource_list_tbl
969 IS
970 ------------------------------------------------------------------------------
971 -- This does not pick up the public calendar, pick up only group calendar
972 ------------------------------------------------------------------------------
973 CURSOR c_group_calendar (b_resource_id IN VARCHAR2)
974 IS
975 SELECT DISTINCT fgs.instance_pk1_value resource_id,
976 fgs.instance_pk2_value resource_type
977 FROM fnd_grants fgs,
978 fnd_menus fmu,
979 fnd_objects fos,
980 jtf_rs_group_usages jru,
981 jtf_rs_groups_tl jrt
982 WHERE fgs.object_id = fos.object_id -- grants joint to object
983 AND fgs.menu_id = fmu.menu_id -- grants joint to menus
984 AND fos.obj_name = 'JTF_TASK_RESOURCE'
985 AND fgs.grantee_key = b_resource_id
986 AND fgs.grantee_type = 'USER'
987 AND fgs.start_date < SYSDATE
988 AND ( fgs.end_date >= SYSDATE
989 OR fgs.end_date IS NULL)
990 AND fgs.instance_pk2_value = 'RS_GROUP'
991 AND jrt.group_id = TO_NUMBER (fgs.instance_pk1_value)
992 AND jrt.language = USERENV ('LANG')
993 AND jru.group_id = jrt.group_id
994 AND jru.usage = 'GROUP_CALENDAR';
995
996 l_group_resource_tbl resource_list_tbl;
997 i BINARY_INTEGER := 0;
998 BEGIN
999 FOR r_resources IN c_group_calendar (b_resource_id => p_resource_id)
1000 LOOP
1001 i := i + 1;
1002 l_group_resource_tbl (i).resource_id := r_resources.resource_id;
1003 l_group_resource_tbl (i).resource_type := r_resources.resource_type;
1004 END LOOP; --r_resources
1005
1006 RETURN l_group_resource_tbl;
1007 END get_group_calendar;
1008
1009 PROCEDURE get_group_resource (
1010 p_request_type IN VARCHAR2,
1011 p_resource_id IN NUMBER,
1012 p_resource_type IN VARCHAR2,
1013 x_resources OUT NOCOPY resource_list_tbl
1014 )
1015 IS
1016 res_index BINARY_INTEGER;
1017 BEGIN
1018 IF p_request_type = G_REQ_APPOINTMENT
1019 THEN
1020 x_resources := get_group_calendar (p_resource_id => p_resource_id);
1021 ELSIF p_request_type = G_REQ_TASK
1022 THEN
1023 x_resources := get_group_team_tasks (p_resource_id => p_resource_id);
1024 END IF;
1025
1026 res_index := NVL (x_resources.LAST, 0) + 1;
1027 x_resources (res_index).resource_id := p_resource_id;
1028 x_resources (res_index).resource_type := p_resource_type;
1029 END get_group_resource;
1030
1031 PROCEDURE get_alarm_mins (
1032 p_task_rec IN cac_sync_task.task_rec,
1033 x_alarm_mins OUT NOCOPY NUMBER
1034 )
1035 IS
1036 l_alarm_days NUMBER;
1037 BEGIN
1038 IF (p_task_rec.objectcode <> G_TASK)
1039 THEN
1040 IF (p_task_rec.alarmflag = 'Y')
1041 THEN
1042 l_alarm_days :=
1043 p_task_rec.plannedstartdate - p_task_rec.alarmdate;
1044 x_alarm_mins := ROUND (l_alarm_days * 1440, 0);
1045 ELSE
1046 x_alarm_mins := NULL;
1047 END IF;
1048 ELSE
1049 x_alarm_mins := NULL;
1050 END IF;
1051 END get_alarm_mins;
1052
1053 FUNCTION convert_gmt_to_client (p_date IN DATE)
1054 RETURN DATE
1055 IS
1056 l_date DATE;
1057 BEGIN
1058 jtf_cal_utility_pvt.adjustfortimezone (
1059 g_gmt_timezone_id,
1060 NVL (g_client_timezone_id, g_server_timezone_id),
1061 p_date,
1062 l_date
1063 );
1064 RETURN l_date;
1065 END;
1066
1067 FUNCTION convert_task_to_gmt (p_date IN DATE, p_timezone_id IN NUMBER)
1068 RETURN DATE
1069 IS
1070 l_date DATE;
1071 BEGIN
1072 jtf_cal_utility_pvt.adjustfortimezone (
1073 p_timezone_id,
1074 g_gmt_timezone_id,
1075 p_date,
1076 l_date
1077 );
1078 RETURN l_date;
1079 END convert_task_to_gmt;
1080
1081 FUNCTION convert_server_to_gmt (p_date IN DATE)
1082 RETURN DATE
1083 IS
1084 l_date DATE;
1085 BEGIN
1086 jtf_cal_utility_pvt.adjustfortimezone (
1087 g_server_timezone_id,
1088 g_gmt_timezone_id,
1089 p_date,
1090 l_date
1091 );
1092 RETURN l_date;
1093 END convert_server_to_gmt;
1094
1095 FUNCTION convert_gmt_to_task (p_date IN DATE, p_task_id IN NUMBER)
1096 RETURN DATE
1097 IS
1098 l_date DATE;
1099 l_task_timezone_id NUMBER;
1100 BEGIN
1101 l_task_timezone_id := get_task_timezone_id (p_task_id);
1102
1103 IF l_task_timezone_id <> g_gmt_timezone_id
1104 THEN
1105 jtf_cal_utility_pvt.adjustfortimezone (
1106 g_gmt_timezone_id,
1107 l_task_timezone_id,
1108 p_date,
1109 l_date
1110 );
1111 ELSE
1112 l_date := p_date;
1113 END IF;
1114
1115 RETURN l_date;
1116 END convert_gmt_to_task;
1117
1118 FUNCTION convert_gmt_to_server (p_date IN DATE)
1119 RETURN DATE
1120 IS
1121 l_date DATE;
1122 BEGIN
1123 jtf_cal_utility_pvt.adjustfortimezone (
1124 g_gmt_timezone_id,
1125 g_server_timezone_id,
1126 p_date,
1127 l_date
1128 );
1129 RETURN l_date;
1130 END convert_gmt_to_server;
1131
1132 PROCEDURE convert_dates (
1133 p_task_rec IN cac_sync_task.task_rec,
1134 p_operation IN VARCHAR2, --CREATE OR UPDATE
1135 x_planned_start OUT NOCOPY DATE,
1136 x_planned_end OUT NOCOPY DATE,
1137 x_scheduled_start OUT NOCOPY DATE,
1138 x_scheduled_end OUT NOCOPY DATE,
1139 x_actual_start OUT NOCOPY DATE,
1140 x_actual_end OUT NOCOPY DATE,
1141 x_date_selected OUT NOCOPY VARCHAR2,
1142 x_show_on_calendar OUT NOCOPY VARCHAR2
1143 )
1144 IS
1145 l_task_id NUMBER;
1146 BEGIN
1147 -- If it's All Day APMT, do not convert the dates
1148 IF (p_task_rec.plannedstartdate = p_task_rec.plannedenddate AND
1149 TRUNC(p_task_rec.plannedstartdate) = p_task_rec.plannedstartdate) AND
1150 p_task_rec.objectcode = G_APPOINTMENT
1151 THEN
1152 x_planned_start := p_task_rec.plannedstartdate;
1153 x_planned_end := p_task_rec.plannedenddate;
1154
1155 -- This is not all day appointment
1156 ELSE
1157 IF (p_task_rec.objectcode <> G_TASK) --for booking and appointments
1158 THEN
1159 x_planned_start := convert_gmt_to_server(p_task_rec.plannedstartdate);
1160 x_planned_end := convert_gmt_to_server(p_task_rec.plannedenddate);
1161 x_scheduled_start:= convert_gmt_to_server(p_task_rec.scheduledstartdate);
1162 x_scheduled_end := convert_gmt_to_server(p_task_rec.scheduledenddate);
1163 x_actual_start := convert_gmt_to_server(p_task_rec.actualstartdate);
1164 x_actual_end := convert_gmt_to_server(p_task_rec.actualenddate);
1165 ELSE-- for tasks
1166 -- for create task don't do timezone conversion, it's untimed
1167 x_planned_start := p_task_rec.plannedstartdate;
1168 x_planned_end := p_task_rec.plannedenddate;
1169 x_scheduled_start := p_task_rec.scheduledstartdate;
1170 x_scheduled_end := p_task_rec.scheduledenddate;
1171 x_actual_start := p_task_rec.actualstartdate;
1172 x_actual_end := p_task_rec.actualenddate;
1173 END IF;
1174
1175 END IF; -- end if-all day appt
1176
1177 END convert_dates;
1178
1179 PROCEDURE adjust_timezone (
1180 p_timezone_id IN NUMBER,
1181 p_syncanchor IN DATE,
1182 p_planned_start_date IN DATE,
1183 p_planned_end_date IN DATE,
1184 p_scheduled_start_date IN DATE,
1185 p_scheduled_end_date IN DATE,
1186 p_actual_start_date IN DATE,
1187 p_actual_end_date IN DATE,
1188 p_item_display_type IN NUMBER,
1189 x_task_rec IN OUT NOCOPY cac_sync_task.task_rec
1190 )
1191 IS
1192 BEGIN
1193
1194 -------------------------------------------------------------
1195 -- Decide new syncAnchor and Convert server to GMT timezone
1196 x_task_rec.syncanchor := convert_server_to_gmt (p_syncanchor);
1197
1198 IF p_item_display_type = 3 AND x_task_rec.objectcode = G_APPOINTMENT THEN
1199 x_task_rec.plannedstartdate := p_planned_start_date;
1200 x_task_rec.plannedenddate := p_planned_end_date;
1201 ELSIF (x_task_rec.objectcode = G_TASK) then
1202 --for task we should not do any timezone conversion.
1203 x_task_rec.plannedstartdate := p_planned_start_date;
1204 x_task_rec.plannedenddate := p_planned_end_date;
1205 x_task_rec.scheduledstartdate := p_scheduled_start_date;
1206 x_task_rec.scheduledenddate := p_scheduled_end_date;
1207 x_task_rec.actualstartdate := p_actual_start_date;
1208 x_task_rec.actualenddate := p_actual_end_date;
1209
1210 ELSE
1211 x_task_rec.plannedstartdate := convert_task_to_gmt (p_planned_start_date, p_timezone_id);
1212 x_task_rec.plannedenddate := convert_task_to_gmt (p_planned_end_date, p_timezone_id);
1213 x_task_rec.scheduledstartdate := convert_task_to_gmt (p_scheduled_start_date, p_timezone_id);
1214 x_task_rec.scheduledenddate := convert_task_to_gmt (p_scheduled_end_date, p_timezone_id);
1215 x_task_rec.actualstartdate := convert_task_to_gmt (p_actual_start_date, p_timezone_id);
1216 x_task_rec.actualenddate := convert_task_to_gmt (p_actual_end_date, p_timezone_id);
1217
1218 END IF;
1219
1220 END adjust_timezone;
1221
1222 FUNCTION get_max_enddate (p_recurrence_rule_id IN NUMBER)
1223 RETURN DATE
1224 IS
1225
1226
1227 CURSOR c_recur_tasks
1228 IS
1229 SELECT MAX (tasks.calendar_start_date)
1230 FROM ( select b.calendar_start_date from jtf_tasks_b b
1231 where b.recurrence_rule_id=p_recurrence_rule_id
1232 union
1233 select b.calendar_start_date from jtf_tasks_b b,
1234 jta_task_exclusions jte where
1235 jte.recurrence_rule_id=p_recurrence_rule_id
1236 and jte.task_id=b.task_id) tasks;
1237
1238 l_date DATE;
1239
1240
1241 BEGIN
1242 OPEN c_recur_tasks;
1243 FETCH c_recur_tasks into l_date;
1244
1245 IF c_recur_tasks%NOTFOUND
1246 THEN
1247 CLOSE c_recur_tasks;
1248
1249 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
1250 fnd_msg_pub.add;
1251
1252 fnd_message.set_name('JTF', 'JTA_SYNC_INVALID_RECUR_RULE_ID');
1253 fnd_message.set_token('PROC_NAME','cac_sync_task_COMMON.GET_MAX_ENDDATE');
1254 fnd_msg_pub.add;
1255
1256 raise_application_error (-20100,cac_sync_common.get_messages);
1257 END IF;
1258
1259 CLOSE c_recur_tasks;
1260
1261 IF l_date IS NULL
1262 THEN
1263 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
1264 fnd_msg_pub.add;
1265
1266 fnd_message.set_name('JTF', 'JTA_SYNC_NULL_CALENDAR_ENDDATE');
1267 fnd_message.set_token('PROC_NAME','cac_sync_task_COMMON.GET_MAX_ENDDATE');
1268 fnd_msg_pub.add;
1269
1270 raise_application_error (-20100,cac_sync_common.get_messages);
1271 END IF;
1272
1273 RETURN l_date;
1274 END get_max_enddate;
1275
1276 FUNCTION get_priorityId (p_task_id IN NUMBER)
1277
1278 RETURN NUMBER
1279 IS
1280 CURSOR get_priorityId
1281 IS
1282 SELECT task_priority_id
1283 FROM jtf_tasks_b
1284 WHERE task_id = p_task_id;
1285
1286 l_priorityId Number;
1287 BEGIN
1288 l_priorityId:=null;
1289 OPEN get_priorityId;
1290 FETCH get_priorityId into l_priorityId;
1291
1292 IF get_priorityId%NOTFOUND
1293 THEN
1294 CLOSE get_priorityId;
1295 END IF;
1296
1297 if (get_priorityId%ISOPEN) then
1298 CLOSE get_priorityId;
1299 end if;
1300
1301
1302 RETURN l_priorityId;
1303 END get_priorityId;
1304
1305 procedure get_exclusion_data (
1306 p_recurrence_rule_id IN NUMBER,
1307 p_syncanchor IN DATE,
1308 p_task_sync_id IN number,
1309 p_timezone_id IN NUMBER,
1310 p_principal_id IN Number,
1311 p_resource_id IN Number,
1312 p_resource_type IN VARCHAR2,
1313 p_exclusion_data IN OUT NOCOPY cac_sync_task.exclusion_tbl
1314 )
1315 --- RETURN cac_sync_task.exclusion_tbl
1316 IS
1317
1318 l_date DATE;
1319 l_alarm_date DATE;
1320 l_exclusion cac_sync_task_cursors.c_exclusions%ROWTYPE;
1321 i BINARY_INTEGER := nvl(p_exclusion_data.last,0) ;
1322 BEGIN
1323
1324 FOR l_exclusion in cac_sync_task_cursors.c_exclusions(
1325 p_syncanchor,
1326 p_recurrence_rule_id,
1327 p_resource_id,
1328 p_resource_type)
1329 LOOP
1330 i := i + 1;
1331 --converting the dates to the timezone of the task id.Previous, code was assuming that every data in the schema is in server timezone.
1332 p_exclusion_data (i).exclusion_date := convert_task_to_gmt(l_exclusion.exclusion_date,p_timezone_id);--convert_server_to_gmt(l_exclusion.ex_date);
1333 p_exclusion_data (i).syncid := p_task_sync_id;
1334 p_exclusion_data (i).recordIndex:=i;
1335 p_exclusion_data (i).task_id:=l_exclusion.task_id;
1336 p_exclusion_data (i).syncAnchor:=l_exclusion.new_timestamp;
1337 p_exclusion_data (i).timeZoneId:=l_exclusion.timezone_id;
1338 p_exclusion_data (i).eventType:=l_exclusion.event; --new
1339 p_exclusion_data (i).objectCode:=l_exclusion.source_object_type_code;
1340 p_exclusion_data (i).subject:=l_exclusion.task_name;
1341 p_exclusion_data (i).description:=l_exclusion.description;
1342 p_exclusion_data (i).dateSelected:=l_exclusion.date_selected;
1343
1344 --code starts for bug # 5213476
1345 IF ((l_exclusion.planned_end_date - l_exclusion.planned_start_date)*24*60 = 1439) THEN
1346 l_exclusion.planned_end_date:=l_exclusion.planned_start_date;
1347 END IF;
1348 --code ends for bug # 5213476
1349
1350 p_exclusion_data (i).plannedStartDate:=convert_task_to_gmt(l_exclusion.planned_start_date,p_timezone_id);
1351 p_exclusion_data (i).plannedEndDate:=convert_task_to_gmt(l_exclusion.planned_end_date,p_timezone_id);
1352 p_exclusion_data (i).scheduledStartDate:=convert_task_to_gmt(l_exclusion.scheduled_start_date,p_timezone_id);
1353 p_exclusion_data (i).scheduledEndDate:=convert_task_to_gmt(l_exclusion.scheduled_end_date,p_timezone_id);
1354 p_exclusion_data (i).statusId:=l_exclusion.task_status_id;
1355 p_exclusion_data (i).priorityId:=l_exclusion.importance_level;
1356 p_exclusion_data (i).alarmFlag:=l_exclusion.alarm_on;
1357 --code starts for bug # 5213476
1358 l_alarm_date:= set_alarm_date (
1359 p_task_id => l_exclusion.task_id,
1360 p_request_type => G_REQ_APPOINTMENT,
1361 p_scheduled_start_date => l_exclusion.scheduled_start_date,
1362 p_planned_start_date => l_exclusion.planned_start_date,
1363 p_actual_start_date => l_exclusion.actual_start_date,
1364 p_alarm_flag => l_exclusion.alarm_on,
1365 p_alarm_start => l_exclusion.alarm_start );
1366 p_exclusion_data (i).alarmDate:=convert_task_to_gmt(l_alarm_date,p_timezone_id);
1367 --code ends for bug # 5213476
1368
1369 p_exclusion_data (i).privateFlag:=l_exclusion.private_flag;
1370 p_exclusion_data (i).category:= jtf_task_security_pvt.get_category_id(
1371 p_task_id => l_exclusion.task_id,
1372 p_resource_id => p_resource_id,
1373 p_resource_type_code => p_resource_type);
1374
1375 p_exclusion_data (i).resourceId:=p_resource_id;
1376 p_exclusion_data (i).resourceType:=p_resource_type;
1377 p_exclusion_data (i).task_assignment_id:=0;--l_exclusion.task_id;
1378
1379
1380 p_exclusion_data (i).unit_of_measure:=null;--l_exclusion.occurs_uom;
1381 p_exclusion_data (i).occurs_every:=null;--l_exclusion.occurs_every;
1382 p_exclusion_data (i).start_date:=null;--l_exclusion.start_date_active;
1383 p_exclusion_data (i).end_date:=null;--l_exclusion.end_date_active;
1384 p_exclusion_data (i).sunday:=null;--l_exclusion.sunday;
1385 p_exclusion_data (i).monday:=null;--l_exclusion.monday;
1386 p_exclusion_data (i).tuesday:=null;--l_exclusion.tuesday;
1387 p_exclusion_data (i).wednesday:=null;--l_exclusion.wednesday;
1388 p_exclusion_data (i).thursday:=null;--l_exclusion.thursday;
1389 p_exclusion_data (i).friday:=null;--l_exclusion.friday;
1390 p_exclusion_data (i).saturday:=null;--l_exclusion.saturday;
1391 p_exclusion_data (i).date_of_month :=null;--_exclusion.date_of_month;
1392 p_exclusion_data (i).occurs_which:=null;--l_exclusion.occurs_which;
1393
1394 p_exclusion_data (i).locations:=l_exclusion.locations;
1395 p_exclusion_data (i).principal_id:=p_principal_id;
1396 p_exclusion_data (i).free_busy_type:=l_exclusion.free_busy_type;
1397 p_exclusion_data (i).dial_in:=get_dial_in_value(l_exclusion.task_id);
1398
1399 END LOOP;
1400
1401 --- RETURN l_exclusion_data;
1402 END get_exclusion_data;
1403
1404
1405
1406 FUNCTION already_selected(p_task_id IN NUMBER
1407 ,p_sync_id IN NUMBER
1408 ,p_task_tbl IN cac_sync_task.task_tbl)
1409 RETURN BOOLEAN
1410 IS
1411 l_selected BOOLEAN := FALSE;
1412 BEGIN
1413 IF p_task_tbl.COUNT > 0
1414 THEN
1415 FOR i IN p_task_tbl.FIRST..p_task_tbl.LAST
1416 LOOP
1417 IF p_task_id IS NOT NULL
1418 THEN
1419 IF p_task_tbl(i).task_id = p_task_id
1420 THEN
1421 l_selected := TRUE;
1422 EXIT;
1423 END IF;
1424 ELSIF p_sync_id IS NOT NULL
1425 THEN
1426 IF p_task_tbl(i).syncid = p_sync_id
1427 THEN
1428 l_selected := TRUE;
1429 EXIT;
1430 END IF;
1431 ELSE
1432 EXIT;
1433 END IF;
1434 END LOOP;
1435 END IF;
1436
1437 RETURN l_selected;
1438
1439 END already_selected;
1440
1441 PROCEDURE add_task (
1442 p_request_type IN VARCHAR2,
1443 p_resource_id IN NUMBER,
1444 p_principal_id IN NUMBER,
1445 p_resource_type IN VARCHAR2,
1446 p_recordindex IN NUMBER,
1447 p_operation IN VARCHAR2,
1448 p_task_sync_id IN NUMBER,
1449 p_task_id IN NUMBER,
1450 p_task_name IN VARCHAR2,
1451 p_owner_type_code IN VARCHAR2,
1452 p_description IN VARCHAR2,
1453 p_task_status_id IN NUMBER,
1454 p_task_priority_id IN NUMBER,
1455 p_private_flag IN VARCHAR2,
1456 p_date_selected IN VARCHAR2,
1457 p_timezone_id IN NUMBER,
1458 p_syncanchor IN DATE,
1459 p_planned_start_date IN DATE,
1460 p_planned_end_date IN DATE,
1461 p_scheduled_start_date IN DATE,
1462 p_scheduled_end_date IN DATE,
1463 p_actual_start_date IN DATE,
1464 p_actual_end_date IN DATE,
1465 p_calendar_start_date IN DATE,
1466 p_calendar_end_date IN DATE,
1467 p_alarm_on IN VARCHAR2,
1468 p_alarm_start IN NUMBER,
1469 p_recurrence_rule_id IN NUMBER,
1470 p_occurs_uom IN VARCHAR2,
1471 p_occurs_every IN NUMBER,
1472 p_occurs_number IN NUMBER,
1473 p_start_date_active IN DATE,
1474 p_end_date_active IN DATE,
1475 p_sunday IN VARCHAR2,
1476 p_monday IN VARCHAR2,
1477 p_tuesday IN VARCHAR2,
1478 p_wednesday IN VARCHAR2,
1479 p_thursday IN VARCHAR2,
1480 p_friday IN VARCHAR2,
1481 p_saturday IN VARCHAR2,
1482 p_date_of_month IN VARCHAR2,
1483 p_occurs_which IN VARCHAR2,
1484 --p_get_data IN BOOLEAN,
1485 p_locations IN VARCHAR2,
1486 p_free_busy_type IN VARCHAR2,
1487 p_dial_in IN VARCHAR2,
1488 x_task_rec IN OUT NOCOPY cac_sync_task.task_rec
1489 )
1490 IS
1491 l_category_name VARCHAR2(240); -- Fix bug 2540722
1492 l_status BOOLEAN;
1493 l_operation VARCHAR2(20);
1494 l_task_status_id number ;
1495 l_item_display_type NUMBER;
1496 l_category_id NUMBER;
1497 l_repeat_start_day VARCHAR2(15);
1498 l_planned_end_date DATE;
1499 p_occurs_month NUMBER;
1500 l_occurs_month NUMBER;
1501 l_actual_end_date DATE;
1502 l_scheduled_end_date DATE;
1503 BEGIN
1504 l_operation := p_operation;
1505 x_task_rec.syncid := p_task_sync_id;
1506
1507 x_task_rec.resultid := 0;
1508 x_task_rec.objectcode := RTRIM (p_request_type, 'S');
1509 x_task_rec.free_busy_type:=p_free_busy_type;
1510 x_task_rec.dial_in :=p_dial_in;
1511
1512 -- item display type equals 3 for all items shown on top of daily view
1513 --l_item_display_type := jtf_cal_utility_pvt.getItemType
1514 -- ( p_SourceCode => 'TASK'
1515 -- , p_PeriodStartDate => null
1516 -- , p_PeriodEndDate => null
1517 -- , p_StartDate => p_calendar_start_date
1518 -- , p_EndDate => p_calendar_end_date
1519 -- , p_CalSpanDaysProfile => fnd_profile.value('JTF_CAL_SPAN_DAYS')
1520 -- );
1521 l_item_display_type := 1;
1522
1523 --checking if the appointment spans from 00:00:00 to 23:59:00
1524 --if yes change the end date to be equal to start_date. This will take care
1525 --of appoinment created from JTT and OA pages where
1526 --all day appointments are created from 00:00:00 to 23:59:00
1527 --for all-day appointment created from outlook, the start date is
1528 --equal to end date.
1529
1530 l_planned_end_date := p_planned_end_date;
1531 if (x_task_rec.objectcode = G_APPOINTMENT) then
1532 IF ((p_planned_end_date - p_planned_start_date)*24*60 = 1439) then
1533 l_planned_end_date := p_planned_start_date;
1534 end if;
1535 end if;
1536
1537 l_scheduled_end_date := p_scheduled_end_date;
1538 if (x_task_rec.objectcode = G_APPOINTMENT) then
1539 IF ((p_scheduled_end_date - p_scheduled_start_date)*24*60 = 1439) then
1540 l_scheduled_end_date := p_scheduled_start_date;
1541 end if;
1542 end if;
1543
1544 l_actual_end_date := p_actual_end_date;
1545 if (x_task_rec.objectcode = G_APPOINTMENT) then
1546 IF ((p_actual_end_date - p_actual_start_date)*24*60 = 1439) then
1547 l_actual_end_date := p_actual_start_date;
1548 end if;
1549 end if;
1550 adjust_timezone (
1551 p_timezone_id => p_timezone_id,
1552 p_syncanchor => p_syncanchor,
1553 p_planned_start_date => p_planned_start_date,
1554 p_planned_end_date => l_planned_end_date,
1555 p_scheduled_start_date => p_scheduled_start_date,
1556 p_scheduled_end_date => l_scheduled_end_date,
1557 p_actual_start_date => p_actual_start_date,
1558 p_actual_end_date => l_actual_end_date,
1559 p_item_display_type => l_item_display_type,
1560 x_task_rec => x_task_rec
1561 );
1562
1563 do_mapping (
1564 p_task_id,
1565 p_principal_id,
1566 p_operation,
1567 x_task_rec.syncid
1568 );
1569
1570 -- change status
1571 l_task_status_id := p_task_status_id;
1572
1573 IF (x_task_rec.objectcode <> G_APPOINTMENT)
1574 THEN
1575 transformstatus (
1576 p_task_status_id => l_task_status_id,
1577 p_task_sync_id => x_task_rec.syncId,
1578 x_operation => l_operation
1579 ) ;
1580 END IF;
1581
1582 x_task_rec.recordindex := p_recordindex;
1583 x_task_rec.eventtype := p_operation;
1584 x_task_rec.subject := convert_carriage_return(p_task_name,'XML');
1585 x_task_rec.task_id := p_task_id;
1586 x_task_rec.locations := p_locations;
1587
1588 IF p_operation <> G_DELETE
1589 THEN
1590 make_prefix (
1591 p_assignment_status_id => get_assignment_status_id (p_task_id, p_resource_id),
1592 p_source_object_type_code => x_task_rec.objectcode,
1593 p_resource_type => p_owner_type_code,
1594 p_resource_id => cac_sync_task.g_login_resource_id,
1595 p_group_id => p_resource_id,
1596 x_subject => x_task_rec.subject
1597 );
1598 END IF;
1599
1600 x_task_rec.description := p_description;
1601 x_task_rec.statusid := l_task_status_id;
1602 x_task_rec.priorityid := get_client_priority(p_task_priority_id);
1603 x_task_rec.alarmflag := p_alarm_on;
1604 x_task_rec.privateflag := p_private_flag;
1605 x_task_rec.dateselected := NVL(p_date_selected,'S'); -- fix bug 2389092
1606
1607 x_task_rec.resultsystemmessage := NULL;
1608 x_task_rec.resultusermessage := NULL;
1609
1610 -- For fix bug 2540722
1611 l_category_id := jtf_task_security_pvt.get_category_id(
1612 p_task_id => p_task_id,
1613 p_resource_id => p_resource_id,
1614 p_resource_type_code => p_resource_type
1615 );
1616 IF l_category_id IS NOT NULL
1617 THEN
1618 l_category_name := substr(jtf_task_utl.get_category_name(l_category_id), 1, 240);
1619 END IF;
1620 x_task_rec.category := l_category_name;
1621
1622 x_task_rec.alarmdate := set_alarm_date (
1623 p_task_id => p_task_id,
1624 p_request_type => p_request_type,
1625 p_scheduled_start_date => x_task_rec.scheduledstartdate,
1626 p_planned_start_date => x_task_rec.plannedstartdate,
1627 p_actual_start_date => x_task_rec.actualstartdate,
1628 p_alarm_flag => p_alarm_on,
1629 p_alarm_start => p_alarm_start
1630 );
1631
1632 ----------------------------------------------------------
1633 -- Repeating data
1634 ----------------------------------------------------------
1635 IF p_recurrence_rule_id IS NOT NULL
1636 THEN
1637 x_task_rec.unit_of_measure := p_occurs_uom;
1638 x_task_rec.occurs_every := p_occurs_every;
1639 --x_task_rec.occurs_number := p_occurs_number;
1640 -- x_task_rec.start_date := p_start_date_active;
1641 -- x_task_rec.end_date := get_max_enddate (p_recurrence_rule_id) ;
1642
1643 -- commneted out NVL (p_end_date_active,get_max_enddate (p_recurrence_rule_id) );
1644 --recurences created from the server does not contain the time component of the end date.
1645 --so pick up max calendar_start_date for the recurrences
1646 --refer bug 4261252.
1647 -- x_task_rec.sunday := p_sunday;
1648 -- x_task_rec.monday := p_monday;
1649 -- x_task_rec.tuesday := p_tuesday;
1650 -- x_task_rec.wednesday := p_wednesday;
1651 -- x_task_rec.thursday := p_thursday;
1652 -- x_task_rec.friday := p_friday;
1653 -- x_task_rec.saturday := p_saturday;
1654 -- x_task_rec.date_of_month := p_date_of_month;
1655 -- x_task_rec.occurs_which := p_occurs_which;
1656
1657 /*
1658 convert_recur_date_to_gmt (
1659 p_timezone_id => p_timezone_id,
1660 p_base_start_date => p_planned_start_date,
1661 p_base_end_date => p_planned_end_date,
1662 p_start_date => x_task_rec.start_date,
1663 p_end_date => x_task_rec.end_date,
1664 p_item_display_type => l_item_display_type,
1665 p_occurs_which => p_occurs_which,
1666 p_uom => p_occurs_uom,
1667 x_date_of_month => x_task_rec.date_of_month,
1668 x_start_date => x_task_rec.start_date,
1669 x_end_date => x_task_rec.end_date
1670 );
1671 */
1672
1673
1674
1675 IF p_occurs_uom = 'YER' THEN
1676 p_occurs_month := to_number(to_char(p_start_date_active, 'MM'));
1677 else
1678 p_occurs_month:=null;
1679 END IF;
1680
1681 CAC_VIEW_UTIL_PVT.ADJUST_RECUR_RULE_FOR_TIMEZONE(
1682 p_source_tz_id => p_timezone_id, --task timezone id,
1683 p_dest_tz_id => G_GMT_TIMEZONE_ID,
1684 p_base_start_datetime => p_planned_start_date,
1685 p_base_end_datetime => l_planned_end_date,
1686 p_start_date_active => p_start_date_active,
1687 p_end_date_active => get_max_enddate (p_recurrence_rule_id),
1688 p_occurs_which => p_occurs_which,
1689 p_date_of_month => p_date_of_month,
1690 p_occurs_month => p_occurs_month,
1691 p_sunday => p_sunday,
1692 p_monday => p_monday,
1693 p_tuesday => p_tuesday,
1694 p_wednesday => p_wednesday,
1695 p_thursday => p_thursday,
1696 p_friday => p_friday,
1697 p_saturday => p_saturday,
1698 x_start_date_active => x_task_rec.start_date,
1699 x_end_date_active => x_task_rec.end_date,
1700 x_occurs_which => x_task_rec.occurs_which,
1701 x_date_of_month => x_task_rec.date_of_month,
1702 x_occurs_month => l_occurs_month,
1703 x_sunday => x_task_rec.sunday,
1704 x_monday => x_task_rec.monday,
1705 x_tuesday => x_task_rec.tuesday,
1706 x_wednesday => x_task_rec.wednesday,
1707 x_thursday => x_task_rec.thursday,
1708 x_friday => x_task_rec.friday,
1709 x_saturday => x_task_rec.saturday);
1710
1711 --for appointment that repeats once every month or every year, set the day to 'N', refer to bug 4251849
1712 if (x_task_rec.unit_of_measure='MON' or x_task_rec.unit_of_measure='MTH' or
1713 x_task_rec.unit_of_measure='YER' or x_task_rec.unit_of_measure='YR') then
1714 x_task_rec.sunday:='N';
1715 x_task_rec.monday:='N';
1716 x_task_rec.tuesday:='N';
1717 x_task_rec.wednesday:='N';
1718 x_task_rec.thursday:='N';
1719 x_task_rec.friday:='N';
1720 x_task_rec.saturday:='N';
1721 end if;
1722
1723
1724
1725
1726 END IF;
1727
1728 END add_task;
1729
1730
1731 FUNCTION get_client_priority (p_importance_level IN NUMBER)
1732 RETURN NUMBER
1733 IS
1734 l_priority_id NUMBER;
1735 BEGIN
1736 IF p_importance_level <= 2 -- Critical(1), High(1)
1737 THEN
1738 l_priority_id := 2;
1739 ELSIF p_importance_level = 3 -- Medium, Standard
1740 THEN
1741 l_priority_id := 3;
1742 ELSIF p_importance_level >= 4 -- Low, Optional(5)
1743 THEN
1744 l_priority_id := 4;
1745 ELSE
1746 l_priority_id := NULL;
1747 END IF;
1748
1749 RETURN l_priority_id;
1750 END get_client_priority;
1751
1752 PROCEDURE make_prefix (
1753 p_assignment_status_id IN NUMBER,
1754 p_source_object_type_code IN VARCHAR2,
1755 p_resource_type IN VARCHAR2,
1756 p_resource_id IN NUMBER,
1757 p_group_id IN NUMBER,
1758 x_subject IN OUT NOCOPY VARCHAR2
1759 )
1760 IS
1761 l_prefix VARCHAR2(100);
1762 BEGIN
1763
1764 -- This is appending the prefix 'INVITEE: '
1765 IF p_source_object_type_code = G_APPOINTMENT AND
1766 p_resource_type <> 'RS_GROUP' AND
1767 p_assignment_status_id = 18
1768 THEN
1769 x_subject := g_prefix_invitee || x_subject;
1770
1771 -- This is appending the prefix of the group
1772 ELSIF p_source_object_type_code = G_APPOINTMENT AND
1773 p_resource_type = 'RS_GROUP'
1774 THEN
1775 l_prefix := jtf_cal_utility_pvt.GetGroupPrefix(p_ResourceID => p_resource_id
1776 ,p_ResourceType => p_resource_type
1777 ,p_GroupID => p_group_id);
1778 IF l_prefix IS NOT NULL
1779 THEN
1780 x_subject := l_prefix || x_subject;
1781 END IF;
1782 END IF;
1783
1784 END make_prefix;
1785
1786 -- check if the user is assigne then set status to rejected
1787 -- and set delete flag to false this rec can not be deleted
1788 -- else set delete flag to true this rec can be delted
1789 PROCEDURE check_delete_data (
1790 p_task_id IN NUMBER,
1791 p_resource_id IN NUMBER,
1792 p_objectcode IN VARCHAR2,
1793 x_status_id OUT NOCOPY NUMBER,
1794 x_delete_flag OUT NOCOPY VARCHAR2
1795 )
1796 IS
1797 l_assignee_role VARCHAR2(30);
1798 l_assignment_status_id NUMBER;
1799 BEGIN
1800 IF (p_objectcode = G_APPOINTMENT)
1801 THEN
1802 get_assignment_info (
1803 p_task_id => p_task_id,
1804 p_resource_id => p_resource_id,
1805 x_assignee_role => l_assignee_role,
1806 x_assignment_status_id => l_assignment_status_id
1807 );
1808
1809 IF (l_assignee_role = 'ASSIGNEE')
1810 THEN
1811 x_status_id := 4; --rejected
1812 x_delete_flag := 'U'; -- UPDATE
1813 ELSIF l_assignee_role = 'GROUP'
1814 THEN
1815 x_delete_flag := 'X'; -- DO NOTHING
1816 ELSIF (l_assignee_role = 'OWNER')
1817 THEN
1818 x_delete_flag := 'D'; -- DELETE
1819 END IF;
1820 ELSE -- p_objectcode = G_TASK
1821 x_delete_flag := 'D';
1822 END IF; -- p_objectcode = G_APPOINTMENT
1823 END check_delete_data;
1824
1825 FUNCTION get_assignment_id (p_task_id IN NUMBER
1826 , p_resource_id IN NUMBER
1827 , p_resource_type IN VARCHAR2
1828 )
1829 RETURN NUMBER
1830 IS
1831 CURSOR c_assignment
1832 IS
1833 SELECT task_assignment_id
1834 FROM jtf_task_all_assignments
1835 WHERE task_id = p_task_id
1836 AND resource_id = p_resource_id
1837 AND resource_type_code = p_resource_type;
1838
1839 l_task_assignment_id NUMBER;
1840 BEGIN
1841 OPEN c_assignment;
1842 FETCH c_assignment into l_task_assignment_id;
1843
1844 IF c_assignment%NOTFOUND
1845 THEN
1846 CLOSE c_assignment;
1847
1848 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
1849 fnd_msg_pub.add;
1850
1851 fnd_message.set_name('JTF', 'JTA_SYNC_ASSIGNMENT_NOTFOUND');
1852 fnd_message.set_token('PROC_NAME','cac_sync_task_COMMON.GET_ASSIGNMENT_ID');
1853 fnd_msg_pub.add;
1854
1855 raise_application_error (-20100,cac_sync_common.get_messages);
1856 END IF;
1857
1858 CLOSE c_assignment;
1859 RETURN l_task_assignment_id;
1860 END get_assignment_id;
1861
1862 FUNCTION get_assignment_status_id (
1863 p_task_id IN NUMBER,
1864 p_resource_id IN NUMBER
1865 )
1866 RETURN NUMBER
1867 IS
1868 CURSOR c_assignment
1869 IS
1870 SELECT assignment_status_id
1871 FROM jtf_task_all_assignments
1872 WHERE task_id = p_task_id
1873 AND resource_id = p_resource_id;
1874
1875 l_assignment_status_id NUMBER;
1876 BEGIN
1877 OPEN c_assignment;
1878 FETCH c_assignment into l_assignment_status_id;
1879
1880 IF c_assignment%NOTFOUND
1881 THEN
1882 CLOSE c_assignment;
1883
1884 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
1885 fnd_msg_pub.add;
1886
1887 fnd_message.set_name('JTF', 'JTA_SYNC_ASSIGN_STSID_NOTFOUND');
1888 fnd_message.set_token('PROC_NAME','cac_sync_task_COMMON.GET_ASSIGNMENT_STATUS_ID');
1889 fnd_msg_pub.add;
1890
1891 raise_application_error (-20100,cac_sync_common.get_messages);
1892 END IF;
1893
1894 CLOSE c_assignment;
1895 RETURN l_assignment_status_id;
1896 END get_assignment_status_id;
1897
1898 PROCEDURE get_owner_info (
1899 p_task_id IN NUMBER,
1900 x_task_name OUT NOCOPY VARCHAR2,
1901 x_owner_id OUT NOCOPY NUMBER,
1902 x_owner_type_code OUT NOCOPY VARCHAR2
1903 )
1904 IS
1905 CURSOR c_task (b_task_id NUMBER)
1906 IS
1907 SELECT task_name, owner_id, owner_type_code
1908 FROM jtf_tasks_vl
1909 WHERE task_id = b_task_id;
1910
1911 rec_task c_task%ROWTYPE;
1912 BEGIN
1913 OPEN c_task (p_task_id);
1914 FETCH c_task INTO rec_task;
1915
1916 IF c_task%NOTFOUND
1917 THEN
1918 CLOSE c_task;
1919
1920 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
1921 fnd_msg_pub.add;
1922
1923 fnd_message.set_name('JTF', 'JTA_SYNC_NOTFOUND_TASKID');
1924 fnd_message.set_token('PROC_NAME','cac_sync_task_COMMON.GET_OWNER_INFO');
1925 fnd_msg_pub.add;
1926
1927 raise_application_error (-20100,cac_sync_common.get_messages);
1928 END IF;
1929
1930 CLOSE c_task;
1931 x_task_name := rec_task.task_name;
1932 x_owner_id := rec_task.owner_id;
1933 x_owner_type_code := rec_task.owner_type_code;
1934 END get_owner_info;
1935
1936 PROCEDURE get_assignment_info (
1937 p_task_id IN NUMBER,
1938 p_resource_id IN NUMBER,
1939 x_assignee_role OUT NOCOPY VARCHAR2,
1940 x_assignment_status_id OUT NOCOPY NUMBER
1941 )
1942 IS
1943 CURSOR c_assignment (b_task_id NUMBER, b_resource_id NUMBER)
1944 IS
1945 SELECT a.assignee_role, a.assignment_status_id, r.resource_id
1946 FROM jtf_rs_resource_extns r, jtf_task_all_assignments a
1947 WHERE a.task_id = b_task_id
1948 AND a.resource_id = b_resource_id
1949 AND r.user_id = a.created_by;
1950
1951 l_assignee_role VARCHAR2(30);
1952 l_assignment_status_id NUMBER;
1953 l_task_name VARCHAR2(80);
1954 l_owner_id NUMBER;
1955 l_owner_type_code VARCHAR2(30);
1956 l_creator_resource_id NUMBER;
1957 BEGIN
1958 get_owner_info (
1959 p_task_id => p_task_id,
1960 x_task_name => l_task_name,
1961 x_owner_id => l_owner_id,
1962 x_owner_type_code => l_owner_type_code
1963 );
1964
1965 IF l_owner_type_code = 'RS_GROUP'
1966 THEN
1967 OPEN c_assignment (
1968 b_task_id => p_task_id,
1969 b_resource_id => l_owner_id
1970 );
1971 ELSE
1972 OPEN c_assignment (
1973 b_task_id => p_task_id,
1974 b_resource_id => p_resource_id
1975 );
1976 END IF;
1977
1978 FETCH c_assignment into l_assignee_role, l_assignment_status_id, l_creator_resource_id;
1979
1980 IF c_assignment%NOTFOUND
1981 THEN
1982 CLOSE c_assignment;
1983
1984 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
1985 fnd_msg_pub.add;
1986
1987 fnd_message.set_name('JTF', 'JTA_SYNC_ASSIGNMENT_NOTFOUND');
1988 fnd_message.set_token('PROC_NAME','cac_sync_task_COMMON.GET_ASSIGNMENT_INFO');
1989 fnd_msg_pub.add;
1990
1991 raise_application_error (-20100,cac_sync_common.get_messages);
1992 END IF;
1993
1994 CLOSE c_assignment;
1995
1996 IF l_owner_type_code = 'RS_GROUP'
1997 -- AND l_creator_resource_id <> p_resource_id
1998
1999 THEN
2000 x_assignee_role := 'GROUP';
2001 x_assignment_status_id := NULL;
2002 ELSE
2003 x_assignee_role := l_assignee_role;
2004 x_assignment_status_id := l_assignment_status_id;
2005 END IF;
2006 END get_assignment_info;
2007
2008 FUNCTION get_access (p_group_id IN VARCHAR2, p_resource_id IN NUMBER)
2009 RETURN VARCHAR2
2010 IS
2011 -- 1) JTF_CAL_FULL_ACCESS
2012 -- 2) JTF_CAL_ADMIN_ACCESS
2013 -- 3) JTF_CAL_READ_ACCESS
2014 CURSOR c_access (b_group_id VARCHAR2, b_resource_id VARCHAR2)
2015 IS
2016 SELECT DISTINCT fmu.menu_name
2017 FROM fnd_menus fmu, fnd_objects fos, fnd_grants fgs
2018 WHERE fmu.menu_id = fgs.menu_id -- grants joint to menus
2019 AND fos.obj_name = 'JTF_TASK_RESOURCE'
2020 AND fos.object_id = fgs.object_id -- grants joint to object
2021 AND fgs.grantee_key = b_resource_id
2022 AND fgs.grantee_type = 'USER'
2023 AND fgs.start_date < SYSDATE
2024 AND ( fgs.end_date >= SYSDATE
2025 OR fgs.end_date IS NULL)
2026 AND fgs.instance_pk2_value = 'RS_GROUP'
2027 AND fgs.instance_pk1_value = b_group_id;
2028
2029 l_menu_name fnd_menus.menu_name%TYPE;
2030 BEGIN
2031 OPEN c_access (b_group_id => p_group_id, b_resource_id => p_resource_id);
2032 FETCH c_access into l_menu_name;
2033
2034 IF c_access%NOTFOUND
2035 THEN
2036 CLOSE c_access;
2037
2038 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
2039 fnd_msg_pub.add;
2040
2041 fnd_message.set_name('JTF', 'JTA_SYNC_ACCESS_PRIV_NOTFOUND');
2042 fnd_message.set_token('PROC_NAME','cac_sync_task_COMMON.GET_ACCESS');
2043 fnd_msg_pub.add;
2044
2045 raise_application_error (-20100,cac_sync_common.get_messages);
2046 END IF;
2047
2048 CLOSE c_access;
2049 RETURN l_menu_name;
2050 END get_access;
2051
2052 FUNCTION get_source_object_type (p_task_id IN NUMBER)
2053 RETURN VARCHAR2
2054 IS
2055 CURSOR c_source (b_task_id NUMBER)
2056 IS
2057 SELECT source_object_type_code
2058 FROM jtf_tasks_b
2059 WHERE task_id = b_task_id;
2060
2061 l_source_object_type_code VARCHAR2(60);
2062 BEGIN
2063 OPEN c_source (b_task_id => p_task_id);
2064 FETCH c_source into l_source_object_type_code;
2065
2066 IF c_source%NOTFOUND
2067 THEN
2068 CLOSE c_source;
2069
2070 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
2071 fnd_msg_pub.add;
2072
2073 fnd_message.set_name('JTF', 'JTA_SYNC_NOTFOUND_TASKID');
2074 fnd_message.set_token('PROC_NAME','cac_sync_task_COMMON.GET_SOURCE_OBJECT_TYPE');
2075 fnd_msg_pub.add;
2076
2077 raise_application_error (-20100,cac_sync_common.get_messages);
2078 END IF;
2079
2080 CLOSE c_source;
2081 RETURN l_source_object_type_code;
2082 END get_source_object_type;
2083
2084 -- Added for fix bug 2482833
2085 PROCEDURE get_sync_info (p_task_id IN NUMBER,
2086 p_resource_id IN NUMBER,
2087 x_assignee_role OUT NOCOPY VARCHAR2,
2088 x_resource_type OUT NOCOPY VARCHAR2,
2089 x_group_calendar_flag OUT NOCOPY VARCHAR2,
2090 x_assignment_status_id OUT NOCOPY NUMBER,
2091 x_source_object_type_code OUT NOCOPY VARCHAR2)
2092 IS
2093 CURSOR c_resource IS
2094 SELECT asg.assignee_role
2095 , rs.resource_type_code
2096 , rs.group_calendar_flag
2097 , asg.assignment_status_id
2098 , tsk.source_object_type_code
2099 FROM (SELECT p_resource_id resource_id
2100 , 'RS_EMPLOYEE' resource_type_code
2101 , 'N' group_calendar_flag
2102 FROM dual
2103 UNION ALL
2104 SELECT tm.team_id resource_id
2105 , 'RS_TEAM' resource_type_code
2106 , 'N' group_calendar_flag
2107 FROM jtf_rs_team_members tm
2108 WHERE tm.team_resource_id = p_resource_id
2109 UNION ALL
2110 SELECT gm.group_id resource_id
2111 , 'RS_GROUP' resource_type_code
2112 , 'N' group_calendar_flag
2113 FROM jtf_rs_group_members gm
2114 WHERE gm.resource_id = p_resource_id
2115 UNION ALL
2116 SELECT g.group_id resource_id
2117 , 'RS_GROUP' resource_type_code
2118 , 'Y' group_calendar_flag
2119 FROM fnd_grants fg
2120 , jtf_rs_groups_b g
2121 WHERE fg.grantee_key = to_char(p_resource_id)
2122 AND fg.grantee_type = 'USER'
2123 AND fg.instance_pk2_value = 'RS_GROUP'
2124 AND fg.instance_pk1_value = to_char(g.group_id)
2125 ) rs
2126 , jtf_task_all_assignments asg
2127 , jtf_tasks_b tsk
2128 WHERE asg.resource_type_code = rs.resource_type_code
2129 AND asg.resource_id = rs.resource_id
2130 AND asg.task_id = tsk.task_id
2131 AND tsk.task_id = p_task_id
2132 ORDER BY rs.group_calendar_flag desc
2133 ,decode(rs.resource_type_code,
2134 'RS_EMPLOYEE', 1,
2135 'RS_GROUP', 2,
2136 'RS_TEAM', 3);
2137
2138 BEGIN
2139 OPEN c_resource;
2140 FETCH c_resource
2141 INTO x_assignee_role
2142 , x_resource_type
2143 , x_group_calendar_flag
2144 , x_assignment_status_id
2145 , x_source_object_type_code;
2146 IF c_resource%NOTFOUND
2147 THEN
2148 CLOSE c_resource;
2149
2150 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
2151 fnd_msg_pub.add;
2152
2153 fnd_message.set_name('JTF', 'JTA_SYNC_ASSIGNMENT_NOTFOUND');
2154 fnd_message.set_token('PROC_NAME','cac_sync_task_COMMON.GET_SYNC_TYPE');
2155 fnd_msg_pub.add;
2156
2157 raise_application_error (-20100,cac_sync_common.get_messages);
2158 END IF;
2159 CLOSE c_resource;
2160
2161 END get_sync_info;
2162
2163 FUNCTION get_update_type (p_task_id IN NUMBER,
2164 p_resource_id IN NUMBER,
2165 p_subject IN VARCHAR2)
2166 RETURN VARCHAR2
2167 IS
2168 l_synced_resource_type VARCHAR2(30);
2169 l_group_calendar_flag VARCHAR2(1);
2170 l_task_name VARCHAR2(80);
2171 l_assignee_role VARCHAR2(30);
2172 l_assignment_status_id NUMBER;
2173 l_source_object_type_code VARCHAR2(60); -- Added for fix bug 2442686
2174 l_update_type VARCHAR2(15) := G_UPDATE_ALL; -- Added for fix bug 2442686
2175
2176
2177
2178
2179
2180 BEGIN
2181 -- Added for fix bug 2482833
2182 get_sync_info (p_task_id => p_task_id,
2183 p_resource_id => p_resource_id,
2184 x_assignee_role => l_assignee_role,
2185 x_resource_type => l_synced_resource_type,
2186 x_group_calendar_flag => l_group_calendar_flag,
2187 x_assignment_status_id => l_assignment_status_id,
2188 x_source_object_type_code => l_source_object_type_code
2189 );
2190
2191 IF rtrim(l_synced_resource_type) = 'RS_GROUP' AND
2192 l_group_calendar_flag = 'Y'
2193 THEN
2194 l_update_type := g_do_nothing; -- Added for fix bug 2442686
2195 ELSE
2196 IF l_assignee_role = 'ASSIGNEE'
2197 THEN
2198
2199
2200
2201 -- Fix bug 2442686:
2202 -- If this is TASK, assignee can update any fields,
2203 -- but if it's APPOINTMENT, then the invitee can update only the status
2204 -- when he/she accept the appointment.
2205 --l_source_object_type_code := get_source_object_type(p_task_id); -- Added for fix bug 2442686
2206 IF l_source_object_type_code = G_APPOINTMENT -- Added for fix bug 2442686
2207 THEN
2208 IF l_assignment_status_id = 18 AND -- Status = Invited
2209 SUBSTR(p_subject, 1, LENGTH(g_prefix_invitee)) <> g_prefix_invitee
2210 THEN
2211 l_update_type := g_update_status;
2212 ELSE -- Status <> Invited
2213 l_update_type := g_do_nothing;
2214 END IF;
2215 END IF;
2216 END IF;
2217 END IF;
2218
2219 RETURN l_update_type; -- Added for fix bug 2442686
2220
2221 END get_update_type;
2222
2223 FUNCTION compare_task_rec(
2224 p_task_rec IN OUT NOCOPY cac_sync_task.task_rec
2225 )
2226 return boolean
2227 is
2228
2229 CURSOR get_task_info( b_role VARCHAR2, b_task_id NUMBER)
2230
2231 IS
2232 SELECT
2233 t.timezone_id,
2234 tl.description,
2235 tl.task_name,
2236 t.planned_start_date,
2237 t.planned_end_date,
2238 t.scheduled_start_date,
2239 t.scheduled_end_date,
2240 t.actual_start_date,
2241 t.actual_end_date,
2242 t.calendar_end_date,
2243 NVL (t.private_flag, 'N') private_flag,
2244 rc.occurs_uom,
2245 rc.occurs_every,
2246 greatest(rc.start_date_active, t.planned_start_date) start_date_active,
2247 rc.end_date_active,
2248 rc.sunday,
2249 rc.monday,
2250 rc.tuesday,
2251 rc.wednesday,
2252 rc.thursday,
2253 rc.friday,
2254 rc.saturday,
2255 rc.date_of_month,
2256 rc.occurs_which,
2257 rc.recurrence_rule_id,
2258 CAC_VIEW_UTIL_PUB.get_locations(t.task_id) locations,
2259 ta.free_busy_type free_busy_type,
2260 t.alarm_start alarm_start,
2261 t.alarm_on alarm_on
2262 FROM jtf_task_recur_rules rc,
2263 jtf_task_statuses_b ts,
2264 jtf_task_priorities_b tb,
2265 jtf_tasks_tl tl,
2266 jtf_task_all_assignments ta,
2267 jtf_tasks_b t
2268 WHERE
2269 ta.task_id = t.task_id
2270 and ta.assignee_role= b_role
2271 AND tl.task_id = t.task_id
2272 AND ts.task_status_id = t.task_status_id
2273 AND tl.language = USERENV ('LANG')
2274 AND rc.recurrence_rule_id (+)= t.recurrence_rule_id
2275 AND tb.task_priority_id (+) = t.task_priority_id
2276 and t.task_id=b_task_id
2277 and nvl(t.deleted_flag,'N')='N';
2278
2279 task_info get_task_info%rowtype;
2280 l_task_id jtf_tasks_b.task_id%type;
2281 l_start_date date;
2282 l_end_date DATE;
2283 l_occurs_which NUMBER;
2284 l_date_of_month NUMBER;
2285 l_occurs_month number;
2286 l_sunday VARCHAR2(1);
2287 l_monday VARCHAR2(1);
2288 l_tuesday VARCHAR2(1);
2289 l_wednesday VARCHAR2(1);
2290 l_thursday VARCHAR2(1);
2291 l_friday VARCHAR2(1);
2292 l_saturday VARCHAR2(1);
2293 p_occurs_month NUMBER;
2294
2295 l_alarm_start NUMBER := 0;
2296 BEGIN
2297
2298 l_task_id:=get_task_id (p_sync_id => p_task_rec.syncid);
2299 open get_task_info('ASSIGNEE',l_task_id);
2300 fetch get_task_info into task_info;
2301
2302 if ( get_task_info%FOUND) then
2303
2304 if (get_task_info%ISOPEN) then
2305 close get_task_info;
2306 end if;
2307
2308 IF (task_info.task_name<>p_task_rec.subject) THEN RETURN FALSE; END IF; --code changed for bug # 5396599
2309
2310 if (NVL(task_info.description, 'A')<>NVL(p_task_rec.description, 'A')) then return false; end if; --code changed for bug # 5264362
2311 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2312 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'cac_sync_task_common.compare_task_rec', ' description didnt match ');
2313
2314 end if;
2315 if (task_info.private_flag<>p_task_rec.privateflag) then return false;end if;
2316 -- if (task_info.occurs_which<>p_task_rec.occurs_which) then return false;end if;
2317 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2318 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'cac_sync_task_common.compare_task_rec', ' private flag didnt match ');
2319
2320 end if;
2321 if (task_info.locations<>p_task_rec.locations) then return false;end if;
2322 if (NVL(task_info.locations,'AaBb')<>NVL(p_task_rec.locations,'AaBb')) then return false;end if;
2323
2324 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2325 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'cac_sync_task_common.compare_task_rec', ' location didnt match ');
2326
2327 end if;
2328
2329 g_fb_type_changed := false;
2330 if (task_info.free_busy_type<>p_task_rec.free_busy_type)
2331 then
2332 g_fb_type_changed := true;
2333 end if;
2334
2335 get_alarm_mins(p_task_rec => p_task_rec ,x_alarm_mins => l_alarm_start);
2336
2337 if ((NVL(task_info.alarm_on,'N')<>NVL(p_task_rec.alarmFlag,'N')) or
2338 (NVL(task_info.alarm_start,0) <> NVL(l_alarm_start,0)))
2339 then
2340 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2341 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'cac_sync_task_common.compare_task_rec', 'alarm didnt match ');
2342 end if;
2343 return false;
2344 end if;
2345
2346 IF task_info.occurs_uom = 'YER' THEN
2347 p_occurs_month := to_number(to_char(task_info.start_date_active, 'MM'));
2348 else
2349 p_occurs_month:=null;
2350 END IF;
2351
2352 if (task_info.recurrence_rule_id is not null) then
2353 CAC_VIEW_UTIL_PVT.ADJUST_RECUR_RULE_FOR_TIMEZONE(
2354 p_source_tz_id => task_info.timezone_id, --task timezone id,
2355 p_dest_tz_id => G_GMT_TIMEZONE_ID,
2356 p_base_start_datetime => task_info.planned_start_date,
2357 p_base_end_datetime => task_info.planned_end_date,
2358 p_start_date_active => task_info.start_date_active,
2359 p_end_date_active => get_max_enddate (task_info.recurrence_rule_id),
2360 p_occurs_which => task_info.occurs_which,
2361 p_date_of_month => task_info.date_of_month,
2362 p_occurs_month => p_occurs_month,
2363 p_sunday => task_info.sunday,
2364 p_monday => task_info.monday,
2365 p_tuesday => task_info.tuesday,
2366 p_wednesday => task_info.wednesday,
2367 p_thursday => task_info.thursday,
2368 p_friday => task_info.friday,
2369 p_saturday => task_info.saturday,
2370 x_start_date_active => l_start_date,
2371 x_end_date_active => l_end_date,
2372 x_occurs_which => l_occurs_which,
2373 x_date_of_month => l_date_of_month,
2374 x_occurs_month => l_occurs_month,
2375 x_sunday => l_sunday,
2376 x_monday => l_monday,
2377 x_tuesday => l_tuesday,
2378 x_wednesday => l_wednesday,
2379 x_thursday => l_thursday,
2380 x_friday => l_friday,
2381 x_saturday => l_saturday);
2382
2383 if (TO_CHAR(l_start_date, 'DD-MON-YYYY')<>TO_CHAR(p_task_rec.start_date, 'DD-MON-YYYY')) then return false;end if;
2384 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2385 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'cac_sync_task_common.compare_task_rec', ' start date active didnt match ');
2386
2387 end if;
2388 if (TO_CHAR(l_end_date, 'DD-MON-YYYY')<>TO_CHAR(p_task_rec.end_date, 'DD-MON-YYYY')) then return false;end if;
2389 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2390 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'cac_sync_task_common.compare_task_rec', ' end date active didnt match ');
2391
2392 end if;
2393 /*if (l_sunday<>p_task_rec.sunday) then return false;end if;
2394 if (l_monday<>p_task_rec.monday) then return false;end if;
2395 if (l_tuesday<>p_task_rec.tuesday) then return false;end if;
2396 if (l_wednesday<>p_task_rec.wednesday) then return false;end if;
2397 if (l_thursday<>p_task_rec.thursday) then return false;end if;
2398 if (l_friday<>p_task_rec.friday) then return false;end if;
2399 if (l_saturday<>p_task_rec.saturday) then return false;end if;
2400 if (l_date_of_month<>p_task_rec.date_of_month) then return false;end if;
2401 */
2402 else
2403 if (convert_task_to_gmt(task_info.planned_start_date,task_info.timezone_id)<>p_task_rec.plannedstartdate) then return false;end if;
2404 if (convert_task_to_gmt(task_info.planned_end_date,task_info.timezone_id)<>p_task_rec.plannedenddate) then return false;end if;
2405
2406 end if; --for if (task_info.recurrence_rule_id is not null) then
2407 end if;-- for if ( get_task_info%FOUND)
2408
2409
2410 if (get_task_info%ISOPEN) then
2411 close get_task_info;
2412 end if;
2413
2414 return true;
2415
2416 end compare_task_rec;
2417
2418
2419
2420 FUNCTION get_recurrence_rule_id (p_task_id IN NUMBER)
2421 RETURN NUMBER
2422 IS
2423 CURSOR c_recur
2424 IS
2425 SELECT t.recurrence_rule_id
2426 FROM jtf_tasks_b t
2427 WHERE t.task_id = p_task_id;
2428
2429 l_recurrence_rule_id NUMBER;
2430 BEGIN
2431 OPEN c_recur;
2432 FETCH c_recur into l_recurrence_rule_id;
2433
2434 IF c_recur%NOTFOUND
2435 THEN
2436 l_recurrence_rule_id := NULL;
2437 END IF;
2438
2439 CLOSE c_recur;
2440 RETURN l_recurrence_rule_id;
2441 END get_recurrence_rule_id;
2442
2443 PROCEDURE convert_recur_date_to_client (
2444 p_base_start_time IN DATE,
2445 p_base_end_time IN DATE,
2446 p_start_date IN DATE,
2447 p_end_date IN DATE,
2448 p_occurs_which IN NUMBER,
2449 p_uom IN VARCHAR2,
2450 x_date_of_month OUT NOCOPY NUMBER,
2451 x_start_date IN OUT NOCOPY DATE,
2452 x_end_date IN OUT NOCOPY DATE
2453 )
2454 IS
2455 l_start_date VARCHAR2(10); -- DD-MM-YYYY
2456 l_start_time VARCHAR2(8); -- HH24:MI:SS
2457 l_end_date VARCHAR2(10); -- DD-MM-YYYY
2458 l_end_time VARCHAR2(8); -- HH24:MI:SS
2459 BEGIN
2460 l_start_date := TO_CHAR (p_start_date, 'DD-MM-YYYY');
2461 l_start_time := TO_CHAR (p_base_start_time, 'HH24:MI:SS');
2462 l_end_date := TO_CHAR (p_end_date, 'DD-MM-YYYY');
2463 l_end_time := TO_CHAR (p_base_end_time, 'HH24:MI:SS');
2464
2465 if l_start_time <> l_end_time then
2466 x_start_date :=
2467 TRUNC (
2468 convert_gmt_to_client (
2469 TO_DATE (
2470 l_start_date || ' ' || l_start_time,
2471 'DD-MM-YYYY HH24:MI:SS'
2472 )
2473 )
2474 );
2475 IF l_end_date IS NOT NULL THEN
2476 x_end_date :=
2477 TRUNC (
2478 convert_gmt_to_client (
2479 TO_DATE (
2480 l_end_date || ' ' || l_end_time,
2481 'DD-MM-YYYY HH24:MI:SS'
2482 )
2483 )
2484 );
2485 END IF;
2486 else
2487 x_start_date := TO_DATE(l_start_date,'DD-MM-YYYY');
2488 x_end_date := TO_DATE(l_end_date,'DD-MM-YYYY');
2489 end if ;
2490
2491 IF p_occurs_which IS NULL
2492 AND (p_uom = 'MON' OR p_uom ='YER')
2493 THEN
2494 x_date_of_month := TO_CHAR (x_start_date, 'DD');
2495 END IF;
2496 END convert_recur_date_to_client;
2497
2498 PROCEDURE convert_recur_date_to_server (
2499 p_base_start_time IN DATE,
2500 p_base_end_time IN DATE,
2501 p_start_date IN DATE,
2502 p_end_date IN DATE,
2503 p_occurs_which IN NUMBER,
2504 p_uom IN VARCHAR2,
2505 x_date_of_month OUT NOCOPY NUMBER,
2506 x_start_date IN OUT NOCOPY DATE,
2507 x_end_date IN OUT NOCOPY DATE
2508 )
2509 IS
2510 l_start_date VARCHAR2(10); -- DD-MM-YYYY
2511 l_start_time VARCHAR2(8); -- HH24:MI:SS
2512 l_end_date VARCHAR2(10); -- DD-MM-YYYY
2513 l_end_time VARCHAR2(8); -- HH24:MI:SS
2514 BEGIN
2515 l_start_date := TO_CHAR (p_start_date, 'DD-MM-YYYY');
2516 l_start_time := TO_CHAR (p_base_start_time, 'HH24:MI:SS');
2517 l_end_date := TO_CHAR (p_end_date, 'DD-MM-YYYY');
2518 l_end_time := TO_CHAR (p_base_end_time, 'HH24:MI:SS');
2519
2520 if l_start_time <> l_end_time then
2521 /* x_start_date :=
2522 TRUNC (
2523 convert_gmt_to_server (
2524 TO_DATE (
2525 l_start_date || ' ' || l_start_time,
2526 'DD-MM-YYYY HH24:MI:SS'
2527 )
2528 )
2529 );*/ --commenting out as we want to save start date and time and not just date
2530
2531 x_start_date :=
2532 convert_gmt_to_server (
2533 TO_DATE (
2534 l_start_date || ' ' || l_start_time,
2535 'DD-MM-YYYY HH24:MI:SS'
2536 )
2537 );
2538
2539 IF l_end_date IS NOT NULL THEN
2540 /* x_end_date :=
2541 TRUNC (
2542 convert_gmt_to_server (
2543 TO_DATE (
2544 l_end_date || ' ' || l_end_time,
2545 'DD-MM-YYYY HH24:MI:SS')));
2546 x_end_date :=convert_gmt_to_server (TO_DATE (l_end_date || ' ' || l_end_time,'DD-MM-YYYY HH24:MI:SS'));
2547 */
2548 x_end_date:=convert_gmt_to_server(p_end_date);
2549 END IF;
2550 else
2551 x_start_date := TO_DATE(l_start_date,'DD-MM-YYYY');
2552 x_end_date := TO_DATE(l_end_date,'DD-MM-YYYY');
2553 end if ;
2554
2555 IF p_occurs_which IS NULL
2556 AND (p_uom = 'MON' OR p_uom ='YER')
2557 THEN
2558 x_date_of_month := TO_CHAR (x_start_date, 'DD');
2559 END IF;
2560 END convert_recur_date_to_server;
2561
2562
2563
2564 PROCEDURE get_all_nonrepeat_tasks (
2565 p_request_type IN VARCHAR2,
2566 p_syncanchor IN DATE,
2567 p_recordindex IN NUMBER,
2568 p_resource_id IN NUMBER,
2569 p_principal_id IN NUMBER,
2570 p_resource_type IN VARCHAR2,
2571 p_source_object_type IN VARCHAR2,
2572 p_get_data IN BOOLEAN,
2573 x_totalnew IN OUT NOCOPY NUMBER,
2574 x_totalmodified IN OUT NOCOPY NUMBER,
2575 -- x_totaldeleted IN OUT NOCOPY NUMBER,
2576 x_data IN OUT NOCOPY cac_sync_task.task_tbl
2577 --p_new_syncanchor IN DATE
2578 )
2579 IS
2580 x_task_rec cac_sync_task.task_rec;
2581 i INTEGER := p_recordindex;
2582 l_invalid BOOLEAN;
2583 l_end_date DATE;
2584 BEGIN
2585 FOR rec_modify_nonrepeat IN cac_sync_task_cursors.c_modify_non_repeat_task (
2586 p_syncanchor,
2587 p_resource_id,
2588 p_principal_id,
2589 p_resource_type,
2590 p_source_object_type
2591 )
2592 LOOP
2593
2594 if (rec_modify_nonrepeat.calendar_end_date is not null) then
2595 l_end_date :=rec_modify_nonrepeat.calendar_end_date;
2596 elsif (rec_modify_nonrepeat.planned_end_date is not null) then
2597 l_end_date :=rec_modify_nonrepeat.planned_end_date;
2598 elsif (rec_modify_nonrepeat.scheduled_end_date is not null) then
2599 l_end_date :=rec_modify_nonrepeat.scheduled_end_date;
2600 elsif (rec_modify_nonrepeat.actual_end_date is not null) then
2601 l_end_date :=rec_modify_nonrepeat.actual_end_date;
2602 end if;
2603
2604 --check span days and skip add_task
2605 check_span_days (
2606 p_source_object_type_code => rec_modify_nonrepeat.source_object_type_code,
2607 p_calendar_start_date => rec_modify_nonrepeat.calendar_start_date,
2608 p_calendar_end_date => l_end_date,
2609 p_task_id => rec_modify_nonrepeat.task_id,
2610 p_entity => rec_modify_nonrepeat.entity,
2611 x_status => l_invalid
2612 );
2613
2614 IF NOT (l_invalid OR already_selected(p_task_id => rec_modify_nonrepeat.task_id, p_task_tbl => x_data))
2615 THEN
2616 IF p_get_data
2617 THEN
2618 add_task (
2619 p_request_type => p_request_type,
2620 p_resource_id => p_resource_id,
2621 p_principal_id => p_principal_id,
2622 p_resource_type => p_resource_type,
2623 p_recordindex => i+1,
2624 p_operation => g_modify,
2625 p_task_sync_id => rec_modify_nonrepeat.task_sync_id,
2626 p_task_id => rec_modify_nonrepeat.task_id,
2627 p_task_name => rec_modify_nonrepeat.task_name,
2628 p_owner_type_code => rec_modify_nonrepeat.owner_type_code,
2629 p_description => rec_modify_nonrepeat.description,
2630 p_task_status_id => rec_modify_nonrepeat.task_status_id,
2631 p_task_priority_id => rec_modify_nonrepeat.importance_level ,
2632 p_private_flag => rec_modify_nonrepeat.private_flag,
2633 p_date_selected => rec_modify_nonrepeat.date_selected,
2634 p_timezone_id => rec_modify_nonrepeat.timezone_id,
2635 p_syncanchor => rec_modify_nonrepeat.new_timestamp,
2636 p_planned_start_date => rec_modify_nonrepeat.planned_start_date,
2637 p_planned_end_date => rec_modify_nonrepeat.planned_end_date,
2638 p_scheduled_start_date => rec_modify_nonrepeat.scheduled_start_date,
2639 p_scheduled_end_date => rec_modify_nonrepeat.scheduled_end_date,
2640 p_actual_start_date => rec_modify_nonrepeat.actual_start_date,
2641 p_actual_end_date => rec_modify_nonrepeat.actual_end_date,
2642 p_calendar_start_date => rec_modify_nonrepeat.calendar_start_date,
2643 p_calendar_end_date => rec_modify_nonrepeat.calendar_end_date,
2644 p_alarm_on => rec_modify_nonrepeat.alarm_on,
2645 p_alarm_start => rec_modify_nonrepeat.alarm_start,
2646 p_recurrence_rule_id => rec_modify_nonrepeat.recurrence_rule_id,
2647 p_occurs_uom => NULL,
2648 p_occurs_every => NULL,
2649 p_occurs_number => NULL,
2650 p_start_date_active => NULL,
2651 p_end_date_active => NULL,
2652 p_sunday => NULL,
2653 p_monday => NULL,
2654 p_tuesday => NULL,
2655 p_wednesday => NULL,
2656 p_thursday => NULL,
2657 p_friday => NULL,
2658 p_saturday => NULL,
2659 p_date_of_month => NULL,
2660 p_occurs_which => NULL,
2661 p_locations => rec_modify_nonrepeat.locations,
2662 p_free_busy_type => rec_modify_nonrepeat.free_busy_type,
2663 p_dial_in => get_dial_in_value(rec_modify_nonrepeat.task_id),
2664 x_task_rec => x_task_rec
2665 );
2666 i := i + 1;
2667 x_data (i) := x_task_rec;
2668
2669 ELSE -- For get_count, store the task_id selected so as to avoid the duplicate
2670 i := i + 1;
2671 x_data (i).task_id := rec_modify_nonrepeat.task_id;
2672 END IF; -- p_get_data
2673 x_totalmodified := x_totalmodified + 1;
2674 END IF; -- l_invalid
2675
2676 END LOOP;
2677
2678 FOR rec_new_nonrepeat IN cac_sync_task_cursors.c_new_non_repeat_task (
2679 p_syncanchor,
2680 p_resource_id,
2681 p_principal_id,
2682 p_resource_type,
2683 p_source_object_type
2684 )
2685 LOOP
2686 if (rec_new_nonrepeat.calendar_end_date is not null) then
2687 l_end_date :=rec_new_nonrepeat.calendar_end_date;
2688 elsif (rec_new_nonrepeat.planned_end_date is not null) then
2689 l_end_date :=rec_new_nonrepeat.planned_end_date;
2690 elsif (rec_new_nonrepeat.scheduled_end_date is not null) then
2691 l_end_date :=rec_new_nonrepeat.scheduled_end_date;
2692 elsif (rec_new_nonrepeat.actual_end_date is not null) then
2693 l_end_date :=rec_new_nonrepeat.actual_end_date;
2694 end if;
2695 --check span days and skip add_task
2696 check_span_days (
2697 p_source_object_type_code => rec_new_nonrepeat.source_object_type_code,
2698 p_calendar_start_date => rec_new_nonrepeat.calendar_start_date,
2699 p_calendar_end_date => l_end_date,
2700 p_task_id => rec_new_nonrepeat.task_id,
2701 p_entity => rec_new_nonrepeat.entity,
2702 x_status => l_invalid
2703 );
2704
2705 IF NOT (l_invalid OR already_selected(p_task_id => rec_new_nonrepeat.task_id, p_task_tbl => x_data))
2706 THEN
2707 IF p_get_data
2708 THEN
2709 add_task (
2710 p_request_type => p_request_type,
2711 p_resource_id => p_resource_id,
2712 p_principal_id => p_principal_id,
2713 p_resource_type => p_resource_type,
2714 p_recordindex => i + 1,
2715 p_operation => g_new,
2716 p_task_sync_id => NULL,
2717 p_task_id => rec_new_nonrepeat.task_id,
2718 p_task_name => rec_new_nonrepeat.task_name,
2719 p_owner_type_code => rec_new_nonrepeat.owner_type_code,
2720 p_description => rec_new_nonrepeat.description,
2721 p_task_status_id => rec_new_nonrepeat.task_status_id,
2722 p_task_priority_id => rec_new_nonrepeat.importance_level ,
2723 p_private_flag => rec_new_nonrepeat.private_flag,
2724 p_date_selected => rec_new_nonrepeat.date_selected,
2725 p_timezone_id => rec_new_nonrepeat.timezone_id,
2726 p_syncanchor => rec_new_nonrepeat.new_timestamp,
2727 p_planned_start_date => rec_new_nonrepeat.planned_start_date,
2728 p_planned_end_date => rec_new_nonrepeat.planned_end_date,
2729 p_scheduled_start_date => rec_new_nonrepeat.scheduled_start_date,
2730 p_scheduled_end_date => rec_new_nonrepeat.scheduled_end_date,
2731 p_actual_start_date => rec_new_nonrepeat.actual_start_date,
2732 p_actual_end_date => rec_new_nonrepeat.actual_end_date,
2733 p_calendar_start_date => rec_new_nonrepeat.calendar_start_date,
2734 p_calendar_end_date => rec_new_nonrepeat.calendar_end_date,
2735 p_alarm_on => rec_new_nonrepeat.alarm_on,
2736 p_alarm_start => rec_new_nonrepeat.alarm_start,
2737 p_recurrence_rule_id => rec_new_nonrepeat.recurrence_rule_id,
2738 p_occurs_uom => NULL,
2739 p_occurs_every => NULL,
2740 p_occurs_number => NULL,
2741 p_start_date_active => NULL,
2742 p_end_date_active => NULL,
2743 p_sunday => NULL,
2744 p_monday => NULL,
2745 p_tuesday => NULL,
2746 p_wednesday => NULL,
2747 p_thursday => NULL,
2748 p_friday => NULL,
2749 p_saturday => NULL,
2750 p_date_of_month => NULL,
2751 p_occurs_which => NULL,
2752 --p_get_data => p_get_data,
2753 p_locations => rec_new_nonrepeat.locations,
2754 p_free_busy_type=>rec_new_nonrepeat.free_busy_type,
2755 p_dial_in=>get_dial_in_value(rec_new_nonrepeat.task_id),
2756 x_task_rec => x_task_rec
2757 );
2758
2759 i := i + 1;
2760 x_data (i) := x_task_rec;
2761 ELSE -- For get_count, store the task_id selected so as to avoid the duplicate
2762 i := i + 1;
2763 x_data (i).task_id := rec_new_nonrepeat.task_id;
2764 END IF; --p_get_data
2765
2766 x_totalnew := x_totalnew + 1;
2767 END IF; -- l_invalid
2768 END LOOP;
2769
2770 END get_all_nonrepeat_tasks;
2771
2772 PROCEDURE get_all_deleted_tasks (
2773 p_request_type IN VARCHAR2,
2774 p_syncanchor IN DATE,
2775 p_recordindex IN NUMBER,
2776 p_resource_id IN NUMBER,
2777 p_principal_id IN NUMBER,
2778 p_resource_type IN VARCHAR2,
2779 p_source_object_type IN VARCHAR2,
2780 p_get_data IN BOOLEAN,
2781 x_totaldeleted IN OUT NOCOPY NUMBER,
2782 x_data IN OUT NOCOPY cac_sync_task.task_tbl
2783 )
2784 IS
2785 i INTEGER := nvl(x_data.last,0);
2786 BEGIN
2787 FOR rec_delete IN cac_sync_task_cursors.c_delete_task (
2788 p_syncanchor,
2789 p_resource_id,
2790 p_principal_id,
2791 p_resource_type,
2792 p_source_object_type
2793 )
2794 LOOP
2795 IF NOT already_selected(p_sync_id => rec_delete.task_sync_id, p_task_tbl => x_data)
2796 THEN
2797 IF p_get_data
2798 THEN
2799 i := i + 1;
2800 x_data(i).syncid := rec_delete.task_sync_id;
2801 x_data(i).recordindex:= i;
2802 x_data(i).eventtype := g_delete;
2803 x_data(i).resultid := 0;
2804
2805 cac_sync_task_map_pkg.delete_row (
2806 p_task_sync_id => rec_delete.task_sync_id
2807 );
2808
2809 x_data(i).syncanchor := convert_server_to_gmt (SYSDATE);
2810 ELSE -- For get_count, store the sync_id selected so as to avoid the duplicate
2811 i := i + 1;
2812 x_data (i).syncid := rec_delete.task_sync_id;
2813 END IF;
2814
2815 x_totaldeleted := x_totaldeleted + 1;
2816 END IF;
2817 END LOOP;
2818
2819 FOR rec_delete IN cac_sync_task_cursors.c_delete_assignee_reject (
2820 p_syncanchor,
2821 p_resource_id,
2822 p_principal_id,
2823 p_resource_type,
2824 p_source_object_type
2825 )
2826 LOOP
2827 IF NOT already_selected(p_sync_id => rec_delete.task_sync_id, p_task_tbl => x_data)
2828 THEN
2829 IF p_get_data
2830 THEN
2831 i := i + 1;
2832 x_data(i).syncid := rec_delete.task_sync_id;
2833 x_data(i).recordindex:= i;
2834 x_data(i).eventtype := g_delete;
2835 x_data(i).resultid := 0;
2836
2837 /* Commented this for bug#5191856 bcos for deleted appointments, records are not deleted
2838 from jtf_task_all_assignments table and the user has the option of Accepting the declined
2839 Appointment. */
2840 /*cac_sync_task_map_pkg.delete_row (
2841 p_task_sync_id => rec_delete.task_sync_id
2842 );*/
2843
2844 x_data(i).syncanchor := convert_server_to_gmt (SYSDATE);
2845 ELSE -- For get_count, store the sync_id selected so as to avoid the duplicate
2846 i := i + 1;
2847 x_data (i).syncid := rec_delete.task_sync_id;
2848 END IF;
2849
2850 x_totaldeleted := x_totaldeleted + 1;
2851 END IF;
2852 END LOOP;
2853
2854 FOR rec_delete IN cac_sync_task_cursors.c_delete_assignment (
2855 p_syncanchor,
2856 p_resource_id,
2857 p_resource_type,
2858 p_principal_id,
2859 p_source_object_type
2860 )
2861 LOOP
2862 IF NOT already_selected(p_sync_id => rec_delete.task_sync_id, p_task_tbl => x_data)
2863 THEN
2864 IF p_get_data
2865 THEN
2866 i := i + 1;
2867 x_data(i).eventtype := g_delete;
2868 x_data(i).syncid := rec_delete.task_sync_id;
2869 x_data(i).recordindex:= i;
2870 x_data(i).resultid := 0;
2871
2872 cac_sync_task_map_pkg.delete_row (
2873 p_task_sync_id => rec_delete.task_sync_id
2874 );
2875
2876 x_data(i).syncanchor := convert_server_to_gmt (SYSDATE);
2877 ELSE -- For get_count, store the sync_id selected so as to avoid the duplicate
2878 i := i + 1;
2879 x_data (i).syncid := rec_delete.task_sync_id;
2880 END IF;
2881
2882 x_totaldeleted := x_totaldeleted + 1;
2883 END IF;
2884 END LOOP;
2885
2886 FOR rec_delete IN cac_sync_task_cursors.c_delete_rejected_tasks (
2887 p_syncanchor,
2888 p_resource_id,
2889 p_resource_type,
2890 p_principal_id,
2891 p_source_object_type
2892 )
2893 LOOP
2894 IF NOT already_selected(p_sync_id => rec_delete.task_sync_id, p_task_tbl => x_data)
2895 THEN
2896 IF p_get_data
2897 THEN
2898 i := i + 1;
2899 x_data (i).syncid := rec_delete.task_sync_id;
2900 x_data(i).recordindex := i;
2901 x_data (i).eventtype := g_delete;
2902 x_data (i).resultid := 0;
2903
2904 cac_sync_task_map_pkg.delete_row (
2905 p_task_sync_id => rec_delete.task_sync_id
2906 );
2907
2908 x_data (i).syncanchor := convert_server_to_gmt (SYSDATE);
2909 ELSE -- For get_count, store the sync_id selected so as to avoid the duplicate
2910 i := i + 1;
2911 x_data (i).syncid := rec_delete.task_sync_id;
2912 END IF;
2913
2914 x_totaldeleted := x_totaldeleted + 1;
2915 END IF;
2916 END LOOP;
2917
2918 FOR rec_delete IN cac_sync_task_cursors.c_delete_unsubscribed(
2919 p_resource_id,
2920 p_resource_type,
2921 p_principal_id,
2922 p_source_object_type
2923 )
2924 LOOP
2925 IF NOT already_selected(p_sync_id => rec_delete.task_sync_id, p_task_tbl => x_data)
2926 THEN
2927 IF p_get_data
2928 THEN
2929 i := i + 1;
2930 x_data (i).syncid := rec_delete.task_sync_id;
2931 x_data(i).recordindex := i;
2932 x_data (i).eventtype := g_delete;
2933 x_data (i).resultid := 0;
2934
2935 cac_sync_task_map_pkg.delete_row (
2936 p_task_sync_id => rec_delete.task_sync_id
2937 );
2938
2939 x_data (i).syncanchor := convert_server_to_gmt (SYSDATE);
2940
2941 ELSE -- For get_count, store the sync_id selected so as to avoid the duplicate
2942 i := i + 1;
2943 x_data (i).syncid := rec_delete.task_sync_id;
2944 END IF;
2945
2946 x_totaldeleted := x_totaldeleted + 1;
2947 END IF;
2948 END LOOP;
2949 END get_all_deleted_tasks;
2950
2951 ------------------------------------------------
2952 PROCEDURE get_all_repeat_tasks (
2953 p_request_type IN VARCHAR2,
2954 p_syncanchor IN DATE,
2955 p_recordindex IN NUMBER,
2956 p_resource_id IN NUMBER,
2957 p_principal_id IN NUMBER,
2958 p_resource_type IN VARCHAR2,
2959 p_source_object_type IN VARCHAR2,
2960 p_get_data IN BOOLEAN,
2961 x_totalnew IN OUT NOCOPY NUMBER,
2962 x_totalmodified IN OUT NOCOPY NUMBER,
2963 -- x_totaldeleted IN OUT NOCOPY NUMBER,
2964 x_data IN OUT NOCOPY cac_sync_task.task_tbl,
2965 x_exclusion_data IN OUT NOCOPY cac_sync_task.exclusion_tbl
2966 --p_new_syncanchor IN DATE
2967 )
2968 IS
2969 i INTEGER := nvl(x_data.last,0);
2970 x_task_rec cac_sync_task.task_rec;
2971 l_invalid BOOLEAN;
2972
2973
2974 BEGIN
2975
2976
2977
2978 FOR rec_modify_repeat IN cac_sync_task_cursors.c_modify_repeating_task (
2979 p_syncanchor,
2980 p_resource_id,
2981 p_principal_id,
2982 p_resource_type,
2983 p_source_object_type
2984 )
2985 LOOP
2986 --check span days and skip add_task
2987
2988 check_span_days (
2989 p_source_object_type_code => rec_modify_repeat.source_object_type_code,
2990 p_calendar_start_date => rec_modify_repeat.calendar_start_date,
2991 p_calendar_end_date => rec_modify_repeat.calendar_end_date,
2992 p_task_id => rec_modify_repeat.task_id,
2993 p_entity => rec_modify_repeat.entity,
2994 x_status => l_invalid
2995 );
2996
2997 IF (l_invalid AND rec_modify_repeat.entity = G_APPOINTMENT)
2998 THEN
2999 IF p_get_data
3000 THEN
3001 i := i + 1;
3002 x_data(i).syncid := rec_modify_repeat.task_sync_id;
3003 x_data(i).recordindex:= i;
3004 x_data(i).eventtype := g_delete;
3005 x_data(i).resultid := 0;
3006
3007 cac_sync_task_map_pkg.delete_row (
3008 p_task_sync_id => rec_modify_repeat.task_sync_id
3009 );
3010
3011 x_data(i).syncanchor := convert_server_to_gmt (SYSDATE);
3012 ELSE -- For get_count, store the sync_id selected so as to avoid the duplicate
3013 i := i + 1;
3014 x_data (i).syncid := rec_modify_repeat.task_sync_id;
3015 END IF;
3016
3017 x_totalmodified := x_totalmodified + 1;
3018
3019 END IF;
3020
3021
3022 IF NOT (l_invalid OR already_selected(p_task_id => rec_modify_repeat.task_id, p_task_tbl => x_data))
3023 THEN
3024
3025
3026 IF p_get_data
3027 THEN
3028
3029 add_task (
3030 p_request_type => p_request_type,
3031 p_resource_id => p_resource_id,
3032 p_principal_id => p_principal_id,
3033 p_resource_type => p_resource_type,
3034 p_recordindex => i + 1,
3035 p_operation => g_modify,
3036 p_task_sync_id => rec_modify_repeat.task_sync_id,
3037 p_task_id => rec_modify_repeat.task_id,
3038 p_task_name => rec_modify_repeat.task_name,
3039 p_owner_type_code => rec_modify_repeat.owner_type_code,
3040 p_description => rec_modify_repeat.description,
3041 p_task_status_id => rec_modify_repeat.task_status_id,
3042 p_task_priority_id => null ,
3043 p_private_flag => rec_modify_repeat.private_flag,
3044 p_date_selected => rec_modify_repeat.date_selected,
3045 p_timezone_id => rec_modify_repeat.timezone_id,
3046 p_syncanchor => rec_modify_repeat.new_timestamp,
3047 p_planned_start_date => rec_modify_repeat.planned_start_date,
3048 p_planned_end_date => rec_modify_repeat.planned_end_date,
3049 p_scheduled_start_date => rec_modify_repeat.scheduled_start_date,
3050 p_scheduled_end_date => rec_modify_repeat.scheduled_end_date,
3051 p_actual_start_date => rec_modify_repeat.actual_start_date,
3052 p_actual_end_date => rec_modify_repeat.actual_end_date,
3053 p_calendar_start_date => rec_modify_repeat.calendar_start_date,
3054 p_calendar_end_date => rec_modify_repeat.calendar_end_date,
3055 p_alarm_on => rec_modify_repeat.alarm_on,
3056 p_alarm_start => rec_modify_repeat.alarm_start,
3057 p_recurrence_rule_id => rec_modify_repeat.recurrence_rule_id,
3058 p_occurs_uom => rec_modify_repeat.occurs_uom,
3059 p_occurs_every => rec_modify_repeat.occurs_every,
3060 p_occurs_number => rec_modify_repeat.occurs_number,
3061 p_start_date_active => rec_modify_repeat.start_date_active,
3062 p_end_date_active => rec_modify_repeat.end_date_active,
3063 p_sunday => rec_modify_repeat.sunday,
3064 p_monday => rec_modify_repeat.monday,
3065 p_tuesday => rec_modify_repeat.tuesday,
3066 p_wednesday => rec_modify_repeat.wednesday,
3067 p_thursday => rec_modify_repeat.thursday,
3068 p_friday => rec_modify_repeat.friday,
3069 p_saturday => rec_modify_repeat.saturday,
3070 p_date_of_month => rec_modify_repeat.date_of_month,
3071 p_occurs_which => rec_modify_repeat.occurs_which,
3072 p_locations => rec_modify_repeat.locations,
3073 p_free_busy_type=>rec_modify_repeat.free_busy_type,
3074 p_dial_in =>get_dial_in_value(rec_modify_repeat.task_id),
3075 x_task_rec => x_task_rec
3076
3077 --p_get_data => p_get_data
3078 );
3079 i := i + 1;
3080 x_data (i) := x_task_rec;
3081
3082
3083 get_exclusion_data (
3084 p_recurrence_rule_id =>rec_modify_repeat.recurrence_rule_id,
3085 p_syncanchor =>p_syncanchor,
3086 p_task_sync_id =>x_task_rec.syncid,
3087 p_timezone_id =>rec_modify_repeat.timezone_id,
3088 p_exclusion_data =>x_exclusion_data ,
3089 p_resource_id=>p_resource_id,
3090 p_resource_type=>p_resource_type,
3091 p_principal_id=>p_principal_id);
3092
3093 ELSE -- For get_count, store the sync_id selected so as to avoid the duplicate
3094 i := i + 1;
3095 x_data (i).task_id := rec_modify_repeat.task_id;
3096
3097 END IF; -- p_get_data
3098
3099 x_totalmodified := x_totalmodified + 1;
3100 END IF; -- l_invalid
3101
3102 END LOOP;
3103
3104 FOR rec_new_repeat IN cac_sync_task_cursors.c_new_repeating_task (
3105 p_syncanchor,
3106 p_resource_id,
3107 p_principal_id,
3108 p_resource_type,
3109 p_source_object_type
3110 )
3111 LOOP
3112 --check span days and skip add_task
3113
3114 check_span_days (
3115 p_source_object_type_code => rec_new_repeat.source_object_type_code,
3116 p_calendar_start_date => rec_new_repeat.calendar_start_date,
3117 p_calendar_end_date => rec_new_repeat.calendar_end_date,
3118 p_task_id => rec_new_repeat.task_id,
3119 p_entity => rec_new_repeat.entity,
3120 x_status => l_invalid
3121 );
3122
3123 IF NOT (l_invalid OR already_selected(p_task_id => rec_new_repeat.task_id, p_task_tbl => x_data))
3124 THEN
3125
3126 IF p_get_data
3127 THEN
3128
3129 add_task (
3130 p_request_type => p_request_type,
3131 p_resource_id => p_resource_id,
3132 p_principal_id => p_principal_id,
3133 p_resource_type => p_resource_type,
3134 p_recordindex => i + 1,
3135 p_operation => g_new,
3136 p_task_sync_id => null ,
3137 p_task_id => rec_new_repeat.task_id,
3138 p_task_name => rec_new_repeat.task_name,
3139 p_owner_type_code => rec_new_repeat.owner_type_code,
3140 p_description => rec_new_repeat.description,
3141 p_task_status_id => rec_new_repeat.task_status_id,
3142 p_task_priority_id => rec_new_repeat.importance_level,
3143 p_private_flag => rec_new_repeat.private_flag,
3144 p_date_selected => rec_new_repeat.date_selected,
3145 p_timezone_id => rec_new_repeat.timezone_id,
3146 p_syncanchor => rec_new_repeat.new_timestamp,
3147 p_planned_start_date => rec_new_repeat.planned_start_date,
3148 p_planned_end_date => rec_new_repeat.planned_end_date,
3149 p_scheduled_start_date => rec_new_repeat.scheduled_start_date,
3150 p_scheduled_end_date => rec_new_repeat.scheduled_end_date,
3151 p_actual_start_date => rec_new_repeat.actual_start_date,
3152 p_actual_end_date => rec_new_repeat.actual_end_date,
3153 p_calendar_start_date => rec_new_repeat.calendar_start_date,
3154 p_calendar_end_date => rec_new_repeat.calendar_end_date,
3155 p_alarm_on => rec_new_repeat.alarm_on,
3156 p_alarm_start => rec_new_repeat.alarm_start,
3157 p_recurrence_rule_id => rec_new_repeat.recurrence_rule_id,
3158 p_occurs_uom => rec_new_repeat.occurs_uom,
3159 p_occurs_every => rec_new_repeat.occurs_every,
3160 p_occurs_number => rec_new_repeat.occurs_number,
3161 p_start_date_active => rec_new_repeat.start_date_active,
3162 p_end_date_active => rec_new_repeat.end_date_active,
3163 p_sunday => rec_new_repeat.sunday,
3164 p_monday => rec_new_repeat.monday,
3165 p_tuesday => rec_new_repeat.tuesday,
3166 p_wednesday => rec_new_repeat.wednesday,
3167 p_thursday => rec_new_repeat.thursday,
3168 p_friday => rec_new_repeat.friday,
3169 p_saturday => rec_new_repeat.saturday,
3170 p_date_of_month => rec_new_repeat.date_of_month,
3171 p_occurs_which => rec_new_repeat.occurs_which,
3172 --p_get_data => p_get_data,
3173 p_locations => rec_new_repeat.locations,
3174 p_free_busy_type=>rec_new_repeat.free_busy_type,
3175 p_dial_in => get_dial_in_value(rec_new_repeat.task_id),
3176 x_task_rec => x_task_rec
3177 );
3178
3179 i := i + 1;
3180 x_data (i) := x_task_rec;
3181
3182 get_exclusion_data (
3183 p_recurrence_rule_id =>rec_new_repeat.recurrence_rule_id,
3184 p_syncanchor =>p_syncanchor,
3185 p_task_sync_id =>x_task_rec.syncid,
3186 p_timezone_id =>rec_new_repeat.timezone_id,
3187 p_exclusion_data =>x_exclusion_data ,
3188 p_resource_id=>p_resource_id,
3189 p_resource_type=>p_resource_type,
3190 p_principal_id=>p_principal_id);
3191
3192 ELSE -- For get_count, store the sync_id selected so as to avoid the duplicate
3193 i := i + 1;
3194 x_data (i).task_id := rec_new_repeat.task_id;
3195
3196 END IF; -- p_get_data
3197
3198 x_totalnew := x_totalnew + 1;
3199
3200
3201 END IF; -- l_invalid
3202 END LOOP;
3203
3204 END get_all_repeat_tasks;
3205
3206
3207 FUNCTION get_collab_id
3208 RETURN NUMBER
3209 IS
3210 l_key NUMBER;
3211 BEGIN
3212 SELECT cac_view_collab_details_s.nextval INTO l_key FROM DUAL;
3213 RETURN l_key;
3214 END get_collab_id;
3215
3216
3217 PROCEDURE create_new_data (
3218 p_task_rec IN OUT NOCOPY cac_sync_task.task_rec,
3219 p_mapping_type IN VARCHAR2,
3220 p_exclusion_tbl IN OUT NOCOPY cac_sync_task.exclusion_tbl,
3221 p_resource_id IN NUMBER,
3222 p_resource_type IN VARCHAR2
3223 )
3224 IS
3225 l_task_id NUMBER;
3226 l_return_status VARCHAR2(1);
3227 l_msg_count NUMBER;
3228 l_msg_data VARCHAR2(2000);
3229 l_task_assignment_id NUMBER;
3230 l_show_on_calendar VARCHAR2(100);
3231 l_date_selected VARCHAR2(100);
3232 l_alarm_mins NUMBER;
3233 l_scheduled_start DATE;
3234 l_scheduled_end DATE;
3235 l_planned_end DATE;
3236 l_planned_start DATE;
3237 l_actual_end DATE;
3238 l_actual_start DATE;
3239 l_recurrence_rule_id NUMBER;
3240 l_rec_rule_id NUMBER;
3241 task_id NUMBER;
3242 l_task_rec jtf_task_recurrences_pub.task_details_rec;
3243 l_reccurences_generated INTEGER;
3244 l_update_type VARCHAR2(15);
3245 l_repeat_start_date DATE;
3246 l_repeat_end_date DATE;
3247 l_status_id NUMBER;
3248 l_category_id NUMBER;
3249 l_subject VARCHAR2(80);
3250 l_occurs_month NUMBER;
3251 p_occurs_month NUMBER;
3252 l_occurs_number NUMBER;
3253 l_rowid ROWID;
3254 l_booking_rec cac_bookings_pub.booking_type;
3255 l_object_version_number NUMBER;
3256 l_temps NUMBER;
3257 l_repeat_start_day VARCHAR2(15);
3258 l_G_TASK_TIMEZONE_ID NUMBER;
3259
3260 l_sunday VARCHAR2(1);
3261 l_monday VARCHAR2(1);
3262 l_tuesday VARCHAR2(1);
3263 l_wednesday VARCHAR2(1);
3264 l_thursday VARCHAR2(1);
3265 l_friday VARCHAR2(1);
3266 l_saturday VARCHAR2(1);
3267 l_date_of_month NUMBER;
3268 l_occurs_which NUMBER;
3269 l_mapped Boolean:=false;
3270 --cursor to check that no duplicate booking is created on the server by the client
3271 cursor doesBookingExists(b_task_name VARCHAR2,b_cal_start_date DATE,
3272 b_cal_end_date DATE,b_owner_type_code VARCHAR2,
3273 b_owner_id NUMBER)
3274 is
3275 select b.object_version_number,b.task_id from jtf_tasks_b b,jtf_tasks_tl t
3276 where b.entity in ('BOOKING','APPOINTMENT')
3277 and b.source_object_type_code='EXTERNAL APPOINTMENT'
3278 and t.task_id=b.task_id
3279 and t.language=userenv('LANG')
3280 and nvl(b.deleted_flag,'N')='N'
3281 and t.task_name=b_task_name
3282 and b.calendar_start_date =b_cal_start_date
3283 and b.calendar_end_date=b_cal_end_date
3284 and b.owner_type_code=b_owner_type_code
3285 and b.owner_id=b_owner_id;
3286 l_source_object_type_code jtf_tasks_b.source_object_type_code%type;
3287
3288
3289 cursor syncIDExists(b_principal_id NUMBER,b_task_id NUMBER,b_resource_id NUMBER)
3290 is
3291 select 1 from jta_sync_task_mapping where
3292 principal_id=b_principal_id
3293 and task_id=b_task_id
3294 and resource_id=b_resource_id;
3295
3296 CURSOR getCollabDetails(b_task_id NUMBER) IS
3297 SELECT COLLAB_ID, MEETING_MODE,MEETING_ID,MEETING_URL,JOIN_URL ,
3298 PLAYBACK_URL ,DOWNLOAD_URL ,CHAT_URL ,IS_STANDALONE_LOCATION,DIAL_IN
3299 FROM CAC_VIEW_COLLAB_DETAILS_VL
3300 WHERE task_id=b_task_id;
3301 l_collab_details getCollabDetails%ROWTYPE;
3302
3303 Dates VARCHAR2(4000);
3304 l_location CAC_VIEW_COLLAB_DETAILS_TL.LOCATION%type:=substrb(p_task_rec.locations,1,100);
3305
3306
3307 BEGIN
3308 fnd_msg_pub.initialize;
3309
3310 get_alarm_mins (p_task_rec, x_alarm_mins => l_alarm_mins);
3311
3312
3313
3314 --------------------------------------------
3315 -- Convert GMT to Server timezone
3316 -- for plan / schedule / actual dates
3317 --------------------------------------------
3318 convert_dates (
3319 p_task_rec => p_task_rec,
3320 p_operation => 'CREATE',
3321 x_planned_start => l_planned_start,
3322 x_planned_end => l_planned_end,
3323 x_scheduled_start => l_scheduled_start,
3324 x_scheduled_end => l_scheduled_end,
3325 x_actual_start => l_actual_start,
3326 x_actual_end => l_actual_end,
3327 x_date_selected => l_date_selected,
3328 x_show_on_calendar => l_show_on_calendar
3329 );
3330
3331
3332
3333 l_category_id := cac_sync_task_category.get_category_id (
3334 p_category_name => p_task_rec.category,
3335 p_profile_id => cac_sync_task_category.get_profile_id (p_resource_id)
3336 );
3337
3338
3339 l_subject := get_subject( p_subject => p_task_rec.subject
3340 , p_type => 'ORACLE');
3341 l_source_object_type_code:= find_source_object_type_code(p_task_rec.objectcode);
3342
3343 IF (l_source_object_type_code=G_APPOINTMENT) then --p_task_rec.objectcode = G_APPOINTMENT THEN
3344
3345 --check if the appoitment is all day in the client. If yes then convert the dates
3346 if (l_planned_start=l_planned_end) then
3347
3348 l_planned_end:=l_planned_start +1 -1/(60*24) ;
3349
3350 end if;
3351
3352
3353 jta_cal_appointment_pvt.create_appointment (
3354 p_task_name => l_subject,
3355 p_task_type_id => get_default_task_type,
3356 p_description => p_task_rec.description,
3357 p_task_priority_id => p_task_rec.priorityid,
3358 p_owner_type_code => p_resource_type,
3359 p_owner_id => p_resource_id,
3360 p_planned_start_date => l_planned_start,
3361 p_planned_end_date => l_planned_end,
3362 p_timezone_id => G_SERVER_TIMEZONE_ID, --changed from g_client_timezone_id as all the value must be stored at server timezone
3363 p_private_flag => p_task_rec.privateFlag,
3364 p_alarm_start => l_alarm_mins,
3365 p_alarm_on => p_task_rec.alarmflag,
3366 p_category_id => l_category_id,
3367 p_free_busy_type => p_task_rec.free_busy_type,
3368 x_return_status => l_return_status,
3369 x_task_id => l_task_id
3370 );
3371
3372 cac_view_collab_details_pkg.insert_row (
3373 x_rowid => l_rowid,
3374 x_collab_id => get_collab_id,
3375 x_task_id => l_task_id,
3376 x_meeting_mode => 'LIVE',
3377 x_meeting_id => null,
3378 x_meeting_url => null,
3379 x_join_url => null,
3380 x_playback_url => null,
3381 x_download_url => null,
3382 x_chat_url => null,
3383 x_is_standalone_location => 'Y',
3384 x_location => l_location,-- previous it was p_task_rec.locations,
3385 x_dial_in => p_task_rec.dial_in,
3386 x_creation_date => SYSDATE,
3387 x_created_by => jtf_task_utl.created_by,
3388 x_last_update_date => SYSDATE,
3389 x_last_updated_by => jtf_task_utl.updated_by,
3390 x_last_update_login => jtf_task_utl.login_id
3391 );
3392
3393 ELSIF (l_source_object_type_code = G_TASK) THEN
3394
3395 jtf_tasks_pvt.create_task (
3396 p_api_version => 1.0,
3397 p_init_msg_list => fnd_api.g_true,
3398 p_commit => fnd_api.g_false,
3399 p_source_object_type_code => p_task_rec.objectcode,
3400 p_task_name => l_subject,
3401 p_task_type_id => get_default_task_type,
3402 p_description => p_task_rec.description,
3403 p_task_status_id => p_task_rec.statusId,
3404 p_task_priority_id => p_task_rec.priorityid,
3405 p_owner_type_code => p_resource_type,
3406 p_owner_id => p_resource_id,
3407 p_planned_start_date => l_planned_start,
3408 p_planned_end_date => l_planned_end,
3409 p_scheduled_start_date => l_scheduled_start,
3410 p_scheduled_end_date => l_scheduled_end,
3411 p_actual_start_date => l_actual_start,
3412 p_actual_end_date => l_actual_end,
3413 p_show_on_calendar => NULL, -- Fix Bug 2467021: For creation, pass NULL
3414 p_timezone_id => G_SERVER_TIMEZONE_ID,--changed from g_client_timezone_id, as everything should be inserted in server timezone
3415 p_date_selected => NULL, -- Fix Bug 2467021: For creation, pass NULL
3416 p_alarm_start => l_alarm_mins,
3417 p_alarm_start_uom => 'MIN',
3418 p_alarm_interval_uom => 'MIN',
3419 p_alarm_on => p_task_rec.alarmflag,
3420 p_private_flag => p_task_rec.privateFlag,
3421 p_category_id => l_category_id,
3422 x_return_status => l_return_status,
3423 x_msg_count => l_msg_count,
3424 x_msg_data => l_msg_data,
3425 x_task_id => l_task_id
3426 );
3427 ELSE
3428 ----check booking for all day appointment. Added the code for all day booking
3429 if (l_planned_start=l_planned_end) then
3430
3431 l_planned_end:=l_planned_start +1 -1/(60*24) ;
3432
3433 end if;
3434
3435
3436 --Check in cac schema if the booking is already present
3437
3438 open doesBookingExists(l_subject,l_booking_rec.start_date,l_booking_rec.end_date,p_resource_type,p_resource_id);
3439
3440 fetch doesBookingExists into l_object_version_number,l_task_id;
3441
3442 if (doesBookingExists%FOUND) THEN
3443
3444 CLOSE doesBookingExists;
3445
3446
3447 cac_view_appt_pvt.update_external_appointment (
3448 p_object_version_number =>l_object_version_number,
3449 p_task_id =>l_task_id,
3450 p_task_name => l_subject,
3451 p_task_type_id => get_default_task_type,
3452 p_description => p_task_rec.description,
3453 p_task_priority_id => p_task_rec.priorityid,
3454 p_planned_start_date => l_planned_start,
3455 p_planned_end_date => l_planned_end,
3456 p_timezone_id => G_SERVER_TIMEZONE_ID, --changed from g_client_timezone_id as all the value must be stored at server timezone
3457 p_private_flag => p_task_rec.privateFlag,
3458 p_alarm_start => l_alarm_mins,
3459 p_alarm_on => p_task_rec.alarmflag,
3460 p_category_id => l_category_id,
3461 p_free_busy_type => p_task_rec.free_busy_type,
3462 p_change_mode => jtf_task_repeat_appt_pvt.g_all,
3463 x_return_status => l_return_status
3464 );
3465
3466 OPEN getCollabDetails(l_task_id);
3467
3468 FETCH getCollabDetails INTO l_collab_details;
3469
3470 -- Update the rows only if there are some information in the CAC_VIEW_COLLAB_DETAILS table
3471 --otherwise close the cursor.
3472 IF (getCollabDetails%FOUND) THEN
3473
3474 l_location := SUBSTRB(p_task_rec.locations,1,100);
3475
3476 cac_view_collab_details_pkg.update_row
3477 (x_collab_id=> l_collab_details.collab_id ,
3478 x_task_id=> l_task_id,
3479 x_meeting_mode=>l_collab_details.meeting_mode,
3480 x_meeting_id=>l_collab_details.meeting_id,
3481 x_meeting_url=>l_collab_details.meeting_url,
3482 x_join_url=>l_collab_details.join_url,
3483 x_playback_url=>l_collab_details.playback_url,
3484 x_download_url=>l_collab_details.download_url,
3485 x_chat_url=>l_collab_details.chat_url,
3486 x_is_standalone_location=>l_collab_details.is_standalone_location,
3487 x_location=>l_location,
3488 x_dial_in=>p_task_rec.dial_in,
3489 x_last_update_date=>SYSDATE,
3490 x_last_updated_by=>jtf_task_utl.updated_by,
3491 x_last_update_login=>jtf_task_utl.login_id);
3492
3493 END IF;
3494
3495
3496 IF (getCollabDetails%ISOPEN) THEN
3497 CLOSE getCollabDetails;
3498 END IF;
3499
3500
3501 --checking if the update status is false and if yes, write to message stack.
3502
3503 if (cac_sync_common.is_success (l_return_status)=false) then
3504
3505 cac_sync_common.put_messages_to_result (
3506 p_task_rec,
3507 p_status => 2,
3508 p_user_message => 'JTA_SYNC_UPDATE_TASK_FAIL'
3509 );
3510 END IF; -- of (cac_sync_common.is_success (l_return_status))
3511
3512
3513 ELSE -- for (doesBookingExists%FOUND)
3514
3515 cac_view_appt_pvt.create_external_appointment (
3516 p_task_name => l_subject,
3517 p_task_type_id => get_default_task_type,
3518 p_description => p_task_rec.description,
3519 p_task_priority_id => p_task_rec.priorityid,
3520 p_owner_type_code => p_resource_type,
3521 p_owner_id => p_resource_id,
3522 p_planned_start_date => l_planned_start,
3523 p_planned_end_date => l_planned_end,
3524 p_timezone_id => G_SERVER_TIMEZONE_ID, --changed from g_client_timezone_id as all the value must be stored at server timezone
3525 p_private_flag => p_task_rec.privateFlag,
3526 p_alarm_start => l_alarm_mins,
3527 p_alarm_on => p_task_rec.alarmflag,
3528 p_category_id => l_category_id,
3529 p_free_busy_type => p_task_rec.free_busy_type,
3530 p_source_object_type_code => l_source_object_type_code,
3531 x_return_status => l_return_status,
3532 x_task_id => l_task_id
3533 );
3534
3535 cac_view_collab_details_pkg.insert_row (
3536 x_rowid => l_rowid,
3537 x_collab_id => get_collab_id,
3538 x_task_id => l_task_id,
3539 x_meeting_mode => 'LIVE',
3540 x_meeting_id => NULL,
3541 x_meeting_url => NULL,
3542 x_join_url => NULL,
3543 x_playback_url => NULL,
3544 x_download_url => NULL,
3545 x_chat_url => NULL,
3546 x_is_standalone_location => 'Y',
3547 x_location => l_location,-- previous it was p_task_rec.locations,
3548 x_dial_in => p_task_rec.dial_in,
3549 x_creation_date => SYSDATE,
3550 x_created_by => jtf_task_utl.created_by,
3551 x_last_update_date => SYSDATE,
3552 x_last_updated_by => jtf_task_utl.updated_by,
3553 x_last_update_login => jtf_task_utl.login_id
3554 );
3555
3556
3557 END IF; -- for (doesBookingExists%FOUND)
3558
3559 IF (doesBookingExists%ISOPEN) THEN
3560 CLOSE doesBookingExists;
3561 END IF;
3562
3563 END IF; -- for p_task_rec.objectcode = G_APPOINTMENT
3564
3565 IF cac_sync_common.is_success (l_return_status)
3566 THEN
3567 --------------------------------------------
3568 -- Check whether it has a repeating information
3569 -- If it has, then create a recurrence
3570 --------------------------------------------
3571 IF ( l_source_object_type_code <> G_TASK -- = G_APPOINTMENT
3572 AND p_task_rec.unit_of_measure <> fnd_api.g_miss_char
3573 AND p_task_rec.unit_of_measure IS NOT NULL)
3574 -- include open end dates also
3575 -- AND p_task_rec.end_date IS NOT NULL)
3576 THEN
3577 -- Convert repeating start and end date
3578 -- to client timezone
3579
3580 l_G_TASK_TIMEZONE_ID:=get_task_timezone_id(p_task_id=>l_task_id);
3581
3582
3583
3584 IF p_task_rec.unit_of_measure = 'YER' THEN
3585 p_occurs_month := to_number(to_char(p_task_rec.start_date, 'MM'));
3586 else
3587 p_occurs_month:=null;
3588 END IF;
3589
3590
3591
3592 CAC_VIEW_UTIL_PVT.ADJUST_RECUR_RULE_FOR_TIMEZONE(
3593 p_source_tz_id => G_GMT_TIMEZONE_ID,
3594 p_dest_tz_id=> l_G_TASK_TIMEZONE_ID,
3595 p_base_start_datetime=>p_task_rec.plannedstartdate,
3596 p_base_end_datetime =>p_task_rec.plannedenddate,
3597 p_start_date_active =>p_task_rec.start_date,
3598 p_end_date_active =>p_task_rec.end_date,
3599 p_occurs_which =>p_task_rec.occurs_which,
3600 p_date_of_month =>p_task_rec.date_of_month,
3601 p_occurs_month =>p_occurs_month,
3602 p_sunday =>p_task_rec.sunday,
3603 p_monday =>p_task_rec.monday,
3604 p_tuesday =>p_task_rec.tuesday,
3605 p_wednesday =>p_task_rec.wednesday,
3606 p_thursday =>p_task_rec.thursday,
3607 p_friday =>p_task_rec.friday,
3608 p_saturday =>p_task_rec.saturday,
3609 x_start_date_active =>l_repeat_start_date,
3610 x_end_date_active =>l_repeat_end_date ,
3611 x_occurs_which =>l_occurs_which,
3612 x_date_of_month =>l_date_of_month,
3613 x_occurs_month =>l_occurs_month,
3614 x_sunday =>l_sunday,
3615 x_monday =>l_monday,
3616 x_tuesday =>l_tuesday,
3617 x_wednesday =>l_wednesday,
3618 x_thursday =>l_thursday,
3619 x_friday =>l_friday,
3620 x_saturday =>l_saturday);
3621
3622
3623 IF (l_repeat_end_date IS NULL)
3624 THEN
3625 l_occurs_number := G_USER_DEFAULT_REPEAT_COUNT;
3626 END IF;
3627
3628
3629 jtf_task_recurrences_pvt.create_task_recurrence (
3630 p_api_version => 1,
3631 p_commit => fnd_api.g_false,
3632 p_task_id => l_task_id,
3633 p_occurs_which => l_occurs_which,
3634 p_template_flag => 'N',
3635 p_date_of_month => l_date_of_month,
3636 p_occurs_uom => p_task_rec.unit_of_measure,
3637 p_occurs_every => p_task_rec.occurs_every,
3638 p_occurs_number => l_occurs_number,
3639 p_occurs_month => l_occurs_month,
3640 p_start_date_active => l_repeat_start_date,
3641 p_end_date_active => l_repeat_end_date,
3642 p_sunday => l_sunday,
3643 p_monday => l_monday,
3644 p_tuesday => l_tuesday,
3645 p_wednesday => l_wednesday,
3646 p_thursday => l_thursday,
3647 p_friday => l_friday,
3648 p_saturday =>l_saturday,
3649 x_recurrence_rule_id => l_recurrence_rule_id,
3650 x_task_rec => l_task_rec,
3651 x_output_dates_counter => l_reccurences_generated,
3652 x_return_status => l_return_status,
3653 x_msg_count => l_msg_count,
3654 x_msg_data => l_msg_data
3655 );
3656
3657
3658
3659 IF cac_sync_common.is_success (l_return_status)
3660 THEN
3661 -------------------------------------------------------
3662 -- Recurrences are successfully created.
3663 -------------------------------------------------------
3664
3665 do_mapping (
3666 p_task_id => l_task_id,
3667 p_operation => g_new,
3668 x_task_sync_id => p_task_rec.syncid,
3669 p_principal_id => p_task_rec.principal_id
3670 );
3671
3672 l_mapped:=true;
3673 IF p_exclusion_tbl.COUNT > 0
3674 THEN
3675 process_exclusions (
3676 p_exclusion_tbl => p_exclusion_tbl,
3677 p_rec_rule_id => l_recurrence_rule_id,
3678 p_repeating_task_id => l_task_id,
3679 p_task_rec => p_task_rec
3680 );
3681 ELSE
3682 -------------------------------------------------------
3683 -- There are no exclusion tasks.
3684 -------------------------------------------------------
3685 cac_sync_common.put_messages_to_result (
3686 p_task_rec,
3687 p_status => g_sync_success,
3688 p_user_message => 'JTA_SYNC_SUCCESS'
3689 );
3690 END IF;
3691 ELSE
3692 -------------------------------------------------------
3693 -- Failed to create a task recurrence
3694 -------------------------------------------------------
3695 cac_sync_common.put_messages_to_result (
3696 p_task_rec,
3697 p_status => 2,
3698 p_user_message => 'JTA_RECURRENCE_CREATION_FAIL'
3699 );
3700 END IF;
3701
3702 ELSE
3703 --------------------------------------------------------------------
3704 -- This is a Single Task and succeeded to create a single task
3705 --------------------------------------------------------------------
3706 cac_sync_common.put_messages_to_result (
3707 p_task_rec,
3708 p_status => g_sync_success,
3709 p_user_message => 'JTA_SYNC_SUCCESS'
3710 );
3711 END IF; -- end-check if this is repeating Task
3712
3713
3714 if (not (l_mapped)) then
3715 --check if the mapping is created or not. if it not created then only create new mapping.
3716
3717 do_mapping (
3718 p_task_id => l_task_id,
3719 p_operation => g_new,
3720 x_task_sync_id => p_task_rec.syncid,
3721 p_principal_id => p_task_rec.principal_id
3722 );
3723 end if;
3724
3725 p_task_rec.syncanchor := convert_server_to_gmt (SYSDATE);
3726
3727 ELSE-- failed
3728 ---------------------------------------------
3729 -- Failed to create a task
3730 ---------------------------------------------
3731
3732 cac_sync_common.put_messages_to_result (
3733 p_task_rec,
3734 p_status => 2,
3735 p_user_message => 'cac_sync_task_CREATION_FAILED'
3736 );
3737 END IF; -- end-check if task creation is successed or not
3738
3739 /*insert_or_update_mapping (
3740 p_task_sync_id => p_task_rec.syncid,
3741 p_task_id => l_task_id,
3742 p_resource_id => p_resource_id,
3743 p_mapping_type => p_mapping_type
3744 );
3745 */
3746
3747 END create_new_data;
3748
3749
3750
3751 FUNCTION find_source_object_type_code(objectcode IN VARCHAR2)
3752
3753 return VARCHAR2
3754
3755 is
3756
3757 begin
3758
3759 if (objectcode='APPOINTMENT') then
3760 return 'APPOINTMENT';
3761 elsif (objectcode='TASK') then
3762 return 'TASK' ; else
3763 return 'EXTERNAL APPOINTMENT';
3764 end if;
3765
3766 end find_source_object_type_code;
3767
3768
3769
3770 PROCEDURE overwrite_task_record (
3771 p_task_rec IN OUT NOCOPY cac_sync_task.task_rec,
3772 p_resource_id IN NUMBER,
3773 p_resource_type IN VARCHAR2)
3774
3775 is
3776 CURSOR get_task_info( b_role VARCHAR2, b_task_id NUMBER)
3777
3778 IS
3779 SELECT tl.task_name,t.task_id,
3780 tl.description,
3781 t.date_selected,
3782 t.planned_start_date,
3783 t.planned_end_date,
3784 t.scheduled_start_date,
3785 t.scheduled_end_date,
3786 t.actual_start_date,
3787 t.actual_end_date,
3788 t.calendar_start_date,
3789 t.calendar_end_date,
3790 t.task_status_id,
3791 tb.importance_level importance_level,
3792 NVL (t.alarm_on, 'N') alarm_on,
3793 t.alarm_start,
3794 UPPER (t.alarm_start_uom) alarm_start_uom,
3795 NVL (t.private_flag, 'N') private_flag,
3796 t.timezone_id timezone_id,
3797 t.owner_type_code,
3798 t.source_object_type_code,
3799 rc.recurrence_rule_id,
3800 rc.occurs_uom,
3801 rc.occurs_every,
3802 rc.occurs_number,
3803 greatest(rc.start_date_active, t.planned_start_date) start_date_active,
3804 rc.end_date_active,
3805 rc.sunday,
3806 rc.monday,
3807 rc.tuesday,
3808 rc.wednesday,
3809 rc.thursday,
3810 rc.friday,
3811 rc.saturday,
3812 rc.date_of_month,
3813 rc.occurs_which,
3814 greatest(t.object_changed_date, ta.last_update_date) new_timestamp,
3815 CAC_VIEW_UTIL_PUB.get_locations(t.task_id) locations,
3816 ta.free_busy_type free_busy_type
3817 FROM jtf_task_recur_rules rc,
3818 jtf_task_statuses_b ts,
3819 jtf_task_priorities_b tb,
3820 jtf_tasks_tl tl,
3821 jtf_task_all_assignments ta,
3822 jtf_tasks_b t
3823 WHERE
3824
3825 ta.task_id = t.task_id
3826 and ta.assignee_role= b_role
3827 AND tl.task_id = t.task_id
3828 AND ts.task_status_id = t.task_status_id
3829 AND tl.language = USERENV ('LANG')
3830 AND rc.recurrence_rule_id (+)= t.recurrence_rule_id
3831 AND tb.task_priority_id (+) = t.task_priority_id
3832 and t.task_id=b_task_id
3833 and nvl(t.deleted_flag,'N')='N';
3834
3835 task_info get_task_info%rowtype;
3836 l_alarm_mins NUMBER;
3837 l_alarmdate DATE;
3838 l_task_id NUMBER;
3839 p_occurs_month NUMBER;
3840 l_occurs_month NUMBER;
3841
3842 BEGIN
3843
3844 l_task_id := get_task_id (p_sync_id => p_task_rec.syncid);
3845 open get_task_info('ASSIGNEE',l_task_id);
3846 fetch get_task_info into task_info;
3847
3848 /* if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3849 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_sync_task_common.create_new_data', 'point 1 p_task_rec.syncid '||p_task_rec.syncid);
3850 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_sync_task_common.create_new_data', 'point 1 l_task_id '|| l_task_id);
3851 end if;*/
3852
3853 if ( get_task_info%FOUND) then
3854
3855 if (get_task_info%ISOPEN) then
3856 close get_task_info;
3857 end if;
3858
3859
3860
3861
3862 IF task_info.occurs_uom = 'YER' THEN
3863 p_occurs_month := to_number(to_char(task_info.start_date_active, 'MM'));
3864 else
3865 p_occurs_month:=null;
3866 END IF;
3867
3868 IF (task_info.recurrence_rule_id is not null) THEN
3869
3870 CAC_VIEW_UTIL_PVT.ADJUST_RECUR_RULE_FOR_TIMEZONE(
3871 p_source_tz_id => task_info.timezone_id, --task timezone id,
3872 p_dest_tz_id => G_GMT_TIMEZONE_ID,
3873 p_base_start_datetime => task_info.planned_start_date,
3874 p_base_end_datetime => task_info.planned_end_date,
3875 p_start_date_active => task_info.start_date_active,
3876 p_end_date_active => get_max_enddate (task_info.recurrence_rule_id),
3877 p_occurs_which => task_info.occurs_which,
3878 p_date_of_month => task_info.date_of_month,
3879 p_occurs_month => p_occurs_month,
3880 p_sunday => task_info.sunday,
3881 p_monday => task_info.monday,
3882 p_tuesday => task_info.tuesday,
3883 p_wednesday => task_info.wednesday,
3884 p_thursday => task_info.thursday,
3885 p_friday => task_info.friday,
3886 p_saturday => task_info.saturday,
3887 x_start_date_active => p_task_rec.start_date,
3888 x_end_date_active => p_task_rec.end_date,
3889 x_occurs_which => p_task_rec.occurs_which,
3890 x_date_of_month => p_task_rec.date_of_month,
3891 x_occurs_month => l_occurs_month,
3892 x_sunday => p_task_rec.sunday,
3893 x_monday => p_task_rec.monday,
3894 x_tuesday => p_task_rec.tuesday,
3895 x_wednesday => p_task_rec.wednesday,
3896 x_thursday => p_task_rec.thursday,
3897 x_friday => p_task_rec.friday,
3898 x_saturday => p_task_rec.saturday);
3899 END IF;
3900
3901 --for appointment that repeats once every month or every year, set the day to 'N', refer to bug 4251849
3902 if (p_task_rec.unit_of_measure='MON' or p_task_rec.unit_of_measure='MTH' or
3903 p_task_rec.unit_of_measure='YER' or p_task_rec.unit_of_measure='YR') then
3904 p_task_rec.sunday:='N';
3905 p_task_rec.monday:='N';
3906 p_task_rec.tuesday:='N';
3907 p_task_rec.wednesday:='N';
3908 p_task_rec.thursday:='N';
3909 p_task_rec.friday:='N';
3910 p_task_rec.saturday:='N';
3911 end if;
3912
3913
3914
3915 get_alarm_mins (p_task_rec, x_alarm_mins => l_alarm_mins);
3916
3917 p_task_rec.timeZoneId := task_info.timezone_id;
3918 p_task_rec.description := task_info.description;
3919 p_task_rec.statusId :=task_info.task_status_id;
3920 p_task_rec.priorityId :=task_info.importance_level;
3921 p_task_rec.alarmFlag :=task_info.alarm_on;
3922 p_task_rec.privateFlag :=task_info.private_flag;
3923
3924 -- fields added for recurring tasks
3925 p_task_rec.unit_of_measure :=task_info.occurs_uom;
3926 p_task_rec.occurs_every :=task_info.occurs_every;
3927
3928 p_task_rec.locations :=task_info.locations;
3929 p_task_rec.free_busy_type :=task_info.free_busy_type;
3930
3931
3932 --checking if the appointment spans from 00:00:00 to 23:59:00
3933 --if yes change the end date to be equal to start_date. This will take care
3934 --of appoinment created from JTT and OA pages where
3935 --all day appointments are created from 00:00:00 to 23:59:00
3936 --for all-day appointment created from outlook, the start date is
3937 --equal to end date.
3938
3939 if (p_task_rec.objectcode = G_APPOINTMENT) then
3940 IF ((task_info.planned_end_date - task_info.planned_start_date)*24*60 = 1439) then
3941 task_info.planned_end_date := task_info.planned_start_date;
3942 end if;
3943 end if;
3944
3945
3946 adjust_timezone (
3947 p_timezone_id => task_info.timezone_id,
3948 p_syncanchor => task_info.new_timestamp,
3949 p_planned_start_date => task_info.planned_start_date,
3950 p_planned_end_date => task_info.planned_end_date,
3951 p_scheduled_start_date => task_info.scheduled_start_date,
3952 p_scheduled_end_date => task_info.scheduled_end_date,
3953 p_actual_start_date => task_info.actual_start_date,
3954 p_actual_end_date => task_info.actual_end_date,
3955 p_item_display_type => 1,
3956 x_task_rec => p_task_rec);
3957
3958 p_task_rec.alarmdate := set_alarm_date (
3959 p_task_id => l_task_id,
3960 p_request_type => 'APPOINTMENTS',
3961 p_scheduled_start_date => p_task_rec.scheduledstartdate,
3962 p_planned_start_date => p_task_rec.plannedstartdate,
3963 p_actual_start_date => p_task_rec.actualstartdate,
3964 p_alarm_flag => task_info.alarm_on,
3965 p_alarm_start => task_info.alarm_start
3966 );
3967 -- p_task_rec.alarmdate:=convert_task_to_gmt (p_date=>l_alarmdate,p_timezone_id=>task_info.timezone_id );
3968
3969
3970 make_prefix (
3971 p_assignment_status_id => get_assignment_status_id (l_task_id, p_resource_id),
3972 p_source_object_type_code => p_task_rec.objectcode,
3973 p_resource_type => task_info.owner_type_code,
3974 p_resource_id => cac_sync_task.g_login_resource_id,
3975 p_group_id => p_resource_id,
3976 x_subject => task_info.task_name
3977 );
3978
3979 p_task_rec.subject := task_info.task_name;
3980
3981 /* if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3982 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_sync_task_common.create_new_data', 'point 1 p_task_rec.subject '||p_task_rec.subject);
3983 end if;*/
3984 end if;
3985
3986 if (get_task_info%ISOPEN) then
3987 close get_task_info;
3988 end if;
3989
3990 EXCEPTION
3991 WHEN OTHERS then
3992 if (get_task_info%ISOPEN) then
3993 close get_task_info;
3994 end if;
3995
3996
3997
3998
3999
4000 END overwrite_task_record;
4001
4002
4003
4004
4005 PROCEDURE update_existing_data (
4006 p_task_rec IN OUT NOCOPY cac_sync_task.task_rec,
4007 p_exclusion_tbl IN OUT NOCOPY cac_sync_task.exclusion_tbl,
4008 p_resource_id IN NUMBER,
4009 p_resource_type IN VARCHAR2
4010 )
4011 IS
4012 l_ovn NUMBER;
4013 l_task_id NUMBER;
4014 l_exclude_task_id NUMBER;
4015 l_return_status VARCHAR2(1);
4016 l_msg_count NUMBER;
4017 l_msg_data VARCHAR2(2000);
4018 l_task_assignment_id NUMBER;
4019 l_show_on_calendar VARCHAR2(100);
4020 l_date_selected VARCHAR2(100);
4021 l_alarm_mins NUMBER;
4022 l_rec_rule_id NUMBER;
4023 task_id NUMBER;
4024 l_update_type VARCHAR2(15);
4025 l_planned_start_date DATE;
4026 l_planned_end_date DATE;
4027 l_scheduled_start_date DATE;
4028 l_scheduled_end_date DATE;
4029 l_actual_start_date DATE;
4030 l_actual_end_date DATE;
4031 l_sync_id NUMBER;
4032 l_category_id NUMBER;
4033 l_recurr VARCHAR2(5);
4034 l_update_all VARCHAR2(5);
4035 l_new_recurrence_rule_id NUMBER;
4036 l_occurs_month NUMBER;
4037 p_occurs_month NUMBER;
4038 l_occurs_number NUMBER;
4039 l_booking_rec cac_bookings_pub.booking_type;
4040 l_rowid ROWID;
4041 l_G_TASK_TIMEZONE_ID NUMBER;
4042 l_sunday VARCHAR2(1);
4043 l_monday VARCHAR2(1);
4044 l_tuesday VARCHAR2(1);
4045 l_wednesday VARCHAR2(1);
4046 l_thursday VARCHAR2(1);
4047 l_friday VARCHAR2(1);
4048 l_saturday VARCHAR2(1);
4049 l_date_of_month NUMBER;
4050 l_occurs_which NUMBER;
4051 l_repeat_start_date DATE;
4052 l_repeat_end_date DATE;
4053
4054
4055
4056 CURSOR c_recur_tasks (b_recurrence_rule_id NUMBER)
4057 IS
4058 SELECT task_id,
4059 planned_start_date,
4060 planned_end_date,
4061 scheduled_start_date,
4062 scheduled_end_date,
4063 actual_start_date,
4064 actual_end_date,
4065 calendar_start_date,
4066 timezone_id
4067 FROM jtf_tasks_b
4068 WHERE recurrence_rule_id = b_recurrence_rule_id;
4069
4070 l_changed_rule boolean ;
4071 l_status_id number;
4072 l_task_name jtf_tasks_tl.task_name%TYPE;
4073
4074
4075 cursor getTaskForRecurRule(b_task_id number) is
4076 select CAC.COLLAB_ID, CAC.MEETING_MODE,CAC.MEETING_ID,CAC.MEETING_URL,
4077 CAC.JOIN_URL ,CAC.PLAYBACK_URL ,CAC.DOWNLOAD_URL ,CAC.CHAT_URL ,
4078 CAC.IS_STANDALONE_LOCATION,CAC.DIAL_IN, jtb1.task_id
4079 from cac_view_collab_details_vl cac, jtf_tasks_b jtb1,jtf_tasks_b jtb2
4080 where cac.task_id=jtb1.task_id
4081 and jtb1.recurrence_rule_id=jtb2.recurrence_rule_id
4082 and jtb2.task_id=b_task_id;
4083
4084 p_getTaskForRecurRule getTaskForRecurRule%rowtype;
4085
4086
4087 cursor getCollabDetails(b_task_id NUMBER) is
4088 select COLLAB_ID, MEETING_MODE,MEETING_ID,MEETING_URL,JOIN_URL ,
4089 PLAYBACK_URL ,DOWNLOAD_URL ,CHAT_URL ,IS_STANDALONE_LOCATION,DIAL_IN
4090 from CAC_VIEW_COLLAB_DETAILS_VL
4091 where task_id=b_task_id;
4092 l_collab_details getCollabDetails%rowtype;
4093 l_priorityId jtf_tasks_b.task_priority_id%type;
4094
4095 repeat_to_nonrepeat BOOLEAN;
4096 nonrepeat_to_repeat BOOLEAN;
4097 l_location CAC_VIEW_COLLAB_DETAILS_TL.LOCATION%type:=substrb(p_task_rec.locations,1,100);
4098 l_free_busy_type VARCHAR2(25) := FND_API.G_MISS_CHAR;
4099
4100 BEGIN
4101 fnd_msg_pub.initialize;
4102
4103
4104
4105 get_alarm_mins (
4106 p_task_rec,
4107 x_alarm_mins => l_alarm_mins
4108 );
4109
4110 ---------------------------------------
4111 -- Convert GMT to client timezone
4112 -- for plan / schedule / actual dates
4113 ---------------------------------------
4114 convert_dates (
4115 p_task_rec => p_task_rec,
4116 p_operation => 'UPDATE',
4117 x_planned_start => l_planned_start_date,
4118 x_planned_end => l_planned_end_date,
4119 x_scheduled_start => l_scheduled_start_date,
4120 x_scheduled_end => l_scheduled_end_date,
4121 x_actual_start => l_actual_start_date,
4122 x_actual_end => l_actual_end_date,
4123 x_date_selected => l_date_selected,
4124 x_show_on_calendar => l_show_on_calendar
4125 );
4126
4127 l_task_name := get_subject(p_subject => p_task_rec.subject,
4128 p_type => 'ORACLE');
4129 l_task_id := get_task_id (p_sync_id => p_task_rec.syncid);
4130 l_ovn := get_ovn (p_task_id => l_task_id);
4131 l_rec_rule_id := get_recurrence_rule_id (p_task_id => l_task_id);
4132 l_sync_id := p_task_rec.syncid;
4133 l_priorityId := get_priorityId (l_task_id);
4134 /* l_status_id := getchangedstatusid (
4135 p_task_status_id => p_task_rec.statusid,
4136 p_source_object_type_code => p_task_rec.objectcode
4137 );
4138 */ ---commented out this code as it not used.
4139 /* l_category_id := cac_sync_task_category.get_category_id (
4140 p_category_name => p_task_rec.category,
4141 p_profile_id => cac_sync_task_category.get_profile_id(p_resource_id)
4142 );*/ ---commented out this code as it not used.
4143
4144 l_update_type := get_update_type (
4145 p_task_id => l_task_id,
4146 p_resource_id => p_resource_id,
4147 p_subject => p_task_rec.subject
4148 );
4149
4150
4151 --checking if the user is converting repeating to non-repeating appointment
4152 --checking if te user is converting non-repeating to repeating appointment
4153 repeat_to_nonrepeat:=false;
4154 nonrepeat_to_repeat:=false;
4155
4156
4157
4158 if ((l_rec_rule_id is not null) and (p_task_rec.unit_of_measure IS NULL) ) then
4159
4160 repeat_to_nonrepeat:=true;
4161
4162 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4163
4164 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'cac_sync_task_common.create_new_data', ' Converting repeating into non-repeating ' );
4165
4166 end if;
4167
4168 elsif ((l_rec_rule_id is null) and (p_task_rec.unit_of_measure is not null) ) then
4169
4170 nonrepeat_to_repeat:=true;
4171
4172 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4173
4174 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'cac_sync_task_common.create_new_data', ' Converting non-repeating into repeating ' );
4175
4176 end if;
4177
4178 end if;
4179
4180
4181 -- if it is repeating and exclusion and owner privilage
4182 --process exclusions
4183 IF NVL(p_task_rec.resultId,0) < 2 AND
4184 l_rec_rule_id IS NOT NULL AND
4185 p_task_rec.unit_of_measure IS NOT NULL AND
4186 p_task_rec.unit_of_measure <> fnd_api.g_miss_char
4187 THEN
4188 IF l_update_type = g_update_all
4189 THEN
4190 IF p_exclusion_tbl.COUNT > 0
4191 THEN
4192 process_exclusions (
4193 p_exclusion_tbl => p_exclusion_tbl,
4194 p_rec_rule_id => l_rec_rule_id,
4195 p_repeating_task_id => l_task_id,
4196 p_task_rec => p_task_rec
4197 );
4198 ELSE -- p_exclusion_tbl.COUNT = 0 and check change rule
4199 l_changed_rule := cac_sync_task_common.changed_repeat_rule(p_task_rec => p_task_rec);
4200
4201 IF l_changed_rule AND
4202 l_update_type = cac_sync_task_common.g_update_all
4203 THEN -- Changed Repeating Rule
4204
4205
4206 -- include open end dates also
4207
4208 l_G_TASK_TIMEZONE_ID:=get_task_timezone_id(l_task_id);
4209
4210 IF p_task_rec.unit_of_measure = 'YER' THEN
4211 p_occurs_month := to_number(to_char(p_task_rec.start_date, 'MM'));
4212 else
4213 p_occurs_month:=null;
4214 END IF;
4215
4216 CAC_VIEW_UTIL_PVT.ADJUST_RECUR_RULE_FOR_TIMEZONE(
4217 p_source_tz_id => G_GMT_TIMEZONE_ID,
4218 p_dest_tz_id=> l_G_TASK_TIMEZONE_ID,
4219 p_base_start_datetime=>p_task_rec.plannedstartdate,
4220 p_base_end_datetime =>p_task_rec.plannedenddate,
4221 p_start_date_active =>p_task_rec.start_date,
4222 p_end_date_active =>p_task_rec.end_date,
4223 p_occurs_which =>p_task_rec.occurs_which,
4224 p_date_of_month =>p_task_rec.date_of_month,
4225 p_occurs_month =>p_occurs_month,
4226 p_sunday =>p_task_rec.sunday,
4227 p_monday =>p_task_rec.monday,
4228 p_tuesday =>p_task_rec.tuesday,
4229 p_wednesday =>p_task_rec.wednesday,
4230 p_thursday =>p_task_rec.thursday,
4231 p_friday =>p_task_rec.friday,
4232 p_saturday =>p_task_rec.saturday,
4233 x_start_date_active =>l_repeat_start_date,
4234 x_end_date_active =>l_repeat_end_date ,
4235 x_occurs_which =>l_occurs_which,
4236 x_date_of_month =>l_date_of_month,
4237 x_occurs_month =>l_occurs_month,
4238 x_sunday =>l_sunday,
4239 x_monday =>l_monday,
4240 x_tuesday =>l_tuesday,
4241 x_wednesday =>l_wednesday,
4242 x_thursday =>l_thursday,
4243 x_friday =>l_friday,
4244 x_saturday =>l_saturday);
4245
4246
4247
4248
4249 IF (l_repeat_end_date IS NULL) THEN
4250 l_occurs_number := G_USER_DEFAULT_REPEAT_COUNT;
4251 END IF;
4252
4253 jtf_task_recurrences_pvt.update_task_recurrence (
4254 p_api_version => 1.0,
4255 p_task_id => l_task_id,
4256 p_recurrence_rule_id => l_rec_rule_id,
4257 p_occurs_which => l_occurs_which,
4258 p_date_of_month => l_date_of_month,
4259 p_occurs_month => l_occurs_month,
4260 p_occurs_uom => p_task_rec.unit_of_measure,
4261 p_occurs_every => p_task_rec.occurs_every,
4262 p_occurs_number => l_occurs_number,
4263 p_start_date_active => l_repeat_start_date,
4264 p_end_date_active => l_repeat_end_date,
4265 p_sunday => l_sunday,
4266 p_monday => l_monday,
4267 p_tuesday => l_tuesday,
4268 p_wednesday => l_wednesday,
4269 p_thursday => l_thursday,
4270 p_friday => l_friday,
4271 p_saturday => l_saturday,
4272 x_new_recurrence_rule_id => l_new_recurrence_rule_id,
4273 x_return_status => l_return_status,
4274 x_msg_count => l_msg_count,
4275 x_msg_data => l_msg_data
4276 );
4277
4278
4279
4280 IF NOT cac_sync_common.is_success (l_return_status)
4281 THEN-- Failed to update a task
4282
4283 l_task_id := get_task_id (p_sync_id => p_task_rec.syncid);
4284 l_ovn := get_ovn (p_task_id => l_task_id);
4285 l_rec_rule_id := get_recurrence_rule_id (p_task_id => l_task_id);
4286
4287
4288 cac_sync_common.put_messages_to_result (
4289 p_task_rec,
4290 p_status => 2,
4291 p_user_message => 'JTA_SYNC_UPDATE_RECUR_FAIL'
4292 );
4293
4294 ELSE
4295
4296 --get all the collab details for the given recurrence rule
4297
4298 open getTaskForRecurRule(l_task_id);
4299
4300 LOOP
4301
4302 fetch getTaskForRecurRule into p_getTaskForRecurRule;
4303
4304 exit when getTaskForRecurRule%NOTFOUND;
4305
4306 --update collab details
4307
4308 cac_view_collab_details_pkg.update_row
4309 (x_collab_id=> p_getTaskForRecurRule.collab_id ,
4310 x_task_id=> p_getTaskForRecurRule.task_id,
4311 x_meeting_mode=>p_getTaskForRecurRule.meeting_mode,
4312 x_meeting_id=>p_getTaskForRecurRule.meeting_id,
4313 x_meeting_url=>p_getTaskForRecurRule.meeting_url,
4314 x_join_url=>p_getTaskForRecurRule.join_url,
4315 x_playback_url=>p_getTaskForRecurRule.playback_url,
4316 x_download_url=>p_getTaskForRecurRule.download_url,
4317 x_chat_url=>p_getTaskForRecurRule.chat_url,
4318 x_is_standalone_location=>p_getTaskForRecurRule.is_standalone_location,
4319 x_location=>l_location,-- p_task_rec.locations,
4320 x_dial_in=>p_task_rec.dial_in,
4321 x_last_update_date=>sysdate,
4322 x_last_updated_by=>jtf_task_utl.updated_by,
4323 x_last_update_login=>jtf_task_utl.login_id);
4324
4325 END LOOP;
4326
4327 IF (getTaskForRecurRule%ISOPEN) then
4328 close getTaskForRecurRule;
4329 END IF;
4330
4331 END IF; -- is_success
4332 END IF; -- change rule
4333 END IF; -- p_exclusion_tbl.COUNT > 0
4334 END IF; -- l_update_type = g_update_all
4335 END IF; -- success and recurring appt process
4336
4337 --------------------------------------------------
4338 -- Update Repeating Tasks
4339 -- 1. You can delete the excluded tasks, or
4340 -- 2. You can update all occurrences
4341 --------------------------------------------------
4342 --- update_task with new parameters
4343 IF l_update_type = g_update_all
4344 THEN
4345 -----------------------------------------------------------
4346 -- Fix for the bug 2380399
4347 -- : If the current sync has a change of any fields
4348 -- along with the change of repeating rule,
4349 -- The update_task_recurrence_rule API creates new repeating
4350 -- tasks and updates the mapping record with the new first
4351 -- task_id. Hence the new task_id must be picked from
4352 -- mapping table again. And the new object_version_number
4353 -- of the the new task_id must be selected for update of the
4354 -- other fields
4355 -----------------------------------------------------------
4356 l_task_id := get_task_id (p_sync_id => p_task_rec.syncid);
4357 l_ovn := get_ovn (p_task_id => l_task_id);
4358
4359 IF p_task_rec.objectcode = G_APPOINTMENT
4360 THEN
4361
4362 if ((repeat_to_nonrepeat=true) or (nonrepeat_to_repeat=true) ) then
4363
4364
4365 delete_task_data ( p_task_rec =>p_task_rec, p_delete_map_flag =>true);
4366
4367 create_new_data( p_task_rec =>p_task_rec,
4368 p_mapping_type =>null,
4369 p_exclusion_tbl =>p_exclusion_tbl,
4370 p_resource_id =>p_resource_id,
4371 p_resource_type =>p_resource_type );
4372
4373
4374 else -- of if (repeat_to_nonrepeat=true) then
4375
4376
4377 if (l_planned_start_date=l_planned_end_date) then
4378
4379 l_planned_end_date:=l_planned_start_date +1 -1/(60*24) ;
4380
4381 end if;
4382 jta_cal_appointment_pvt.update_appointment (
4383 p_object_version_number => l_ovn ,
4384 p_task_id => l_task_id,
4385 p_task_name => NVL (l_task_name, ' '),
4386 p_description => p_task_rec.description,
4387 p_task_priority_id => l_priorityId,
4388 p_planned_start_date => l_planned_start_date,
4389 p_planned_end_date => l_planned_end_date,
4390 p_timezone_id => get_task_timezone_id (l_task_id),
4391 p_private_flag => p_task_rec.privateflag,
4392 p_alarm_start => l_alarm_mins,
4393 p_alarm_on => p_task_rec.alarmflag,
4394 --p_category_id => l_category_id,
4395 p_free_busy_type => p_task_rec.free_busy_type,
4396 p_change_mode => jtf_task_repeat_appt_pvt.g_all,
4397 x_return_status => l_return_status
4398 );
4399
4400
4401
4402 if (l_rec_rule_id is null) then
4403
4404 --getting the Details from table CAC_VIEW_COLLAB_DETAILS for a given non -repeating task
4405 open getCollabDetails(l_task_id);
4406 fetch getCollabDetails into l_collab_details;
4407
4408 -- Update the rows only if there are some information in the CAC_VIEW_COLLAB_DETAILS table
4409 --otherwise close the cursor.
4410 If (getCollabDetails%FOUND) then
4411
4412 cac_view_collab_details_pkg.update_row
4413 (x_collab_id=> l_collab_details.collab_id ,
4414 x_task_id=> l_task_id,
4415 x_meeting_mode=>l_collab_details.meeting_mode,
4416 x_meeting_id=>l_collab_details.meeting_id,
4417 x_meeting_url=>l_collab_details.meeting_url,
4418 x_join_url=>l_collab_details.join_url,
4419 x_playback_url=>l_collab_details.playback_url,
4420 x_download_url=>l_collab_details.download_url,
4421 x_chat_url=>l_collab_details.chat_url,
4422 x_is_standalone_location=>l_collab_details.is_standalone_location,
4423 x_location=>l_location,-- was p_task_rec.locations,
4424 x_dial_in=>p_task_rec.dial_in,
4425 x_last_update_date=>sysdate,
4426 x_last_updated_by=>jtf_task_utl.updated_by,
4427 x_last_update_login=>jtf_task_utl.login_id);
4428
4429
4430 else
4431
4432 cac_view_collab_details_pkg.insert_row (
4433 x_rowid => l_rowid,
4434 x_collab_id => get_collab_id,--cac_view_collab_details_s.nextval,
4435 x_task_id => l_task_id,
4436 x_meeting_mode => 'LIVE',
4437 x_meeting_id => null,
4438 x_meeting_url => null,
4439 x_join_url => null,
4440 x_playback_url => null,
4441 x_download_url => null,
4442 x_chat_url => null,
4443 x_is_standalone_location => 'Y',
4444 x_location => l_location,--was p_task_rec.locations,
4445 x_dial_in => p_task_rec.dial_in,
4446 x_creation_date => SYSDATE,
4447 x_created_by => jtf_task_utl.created_by,
4448 x_last_update_date => SYSDATE,
4449 x_last_updated_by => jtf_task_utl.updated_by,
4450 x_last_update_login => jtf_task_utl.login_id);
4451
4452
4453 end if;
4454
4455 CLOSE getCollabDetails;
4456
4457 else
4458 --updating all the recurrence of the repeating appointment
4459
4460 open getTaskForRecurRule(l_task_id);
4461 LOOP
4462 fetch getTaskForRecurRule into p_getTaskForRecurRule;
4463 exit when getTaskForRecurRule%NOTFOUND;
4464 --update collab details
4465 cac_view_collab_details_pkg.update_row
4466 (x_collab_id=> p_getTaskForRecurRule.collab_id ,
4467 x_task_id=> p_getTaskForRecurRule.task_id,
4468 x_meeting_mode=>p_getTaskForRecurRule.meeting_mode,
4469 x_meeting_id=>p_getTaskForRecurRule.meeting_id,
4470 x_meeting_url=>p_getTaskForRecurRule.meeting_url,
4471 x_join_url=>p_getTaskForRecurRule.join_url,
4472 x_playback_url=>p_getTaskForRecurRule.playback_url,
4473 x_download_url=>p_getTaskForRecurRule.download_url,
4474 x_chat_url=>p_getTaskForRecurRule.chat_url,
4475 x_is_standalone_location=>p_getTaskForRecurRule.is_standalone_location,
4476 x_location=>l_location,--was p_task_rec.locations,
4477 x_dial_in=>p_task_rec.dial_in,
4478 x_last_update_date=>sysdate,
4479 x_last_updated_by=>jtf_task_utl.updated_by,
4480 x_last_update_login=>jtf_task_utl.login_id);
4481 END LOOP;
4482 close getTaskForRecurRule;
4483
4484 end if;-- for if (l_rec_rule_id is null)
4485
4486
4487 end if; --if (repeat_to_nonrepeat=true) then
4488
4489
4490
4491 ELSIF (p_task_rec.objectcode = 'TASK')
4492 THEN
4493
4494 jtf_tasks_pvt.update_task (
4495 p_api_version => 1.0,
4496 p_init_msg_list => fnd_api.g_true,
4497 p_commit => fnd_api.g_false,
4498 p_task_id => l_task_id,
4499 p_object_version_number => l_ovn,
4500 p_task_name => NVL (l_task_name, ' '),
4501 p_description => p_task_rec.description,
4502 p_task_status_id => p_task_rec.statusid,
4503 p_task_priority_id => p_task_rec.priorityid,
4504 p_planned_start_date => l_planned_start_date,
4505 p_planned_end_date => l_planned_end_date,
4506 p_scheduled_start_date => l_scheduled_start_date,
4507 p_scheduled_end_date => l_scheduled_end_date,
4508 -- p_actual_start_date => l_actual_start_date,
4509 -- p_actual_end_date => l_actual_end_date,
4510 p_show_on_calendar => fnd_api.g_miss_char, -- Fix Bug 2467021: For update, pass g_miss_char
4511 p_date_selected => fnd_api.g_miss_char, -- Fix Bug 2467021: For update, pass g_miss_char
4512 p_alarm_start => l_alarm_mins,
4513 p_alarm_start_uom => 'MIN',
4514 p_timezone_id => get_task_timezone_id (l_task_id),
4515 p_private_flag => p_task_rec.privateflag,
4516 --p_category_id => l_category_id,
4517 p_change_mode => 'A',
4518 p_enable_workflow => 'N',
4519 p_abort_workflow => 'N',
4520 x_return_status => l_return_status,
4521 x_msg_count => l_msg_count,
4522 x_msg_data => l_msg_data
4523 );
4524
4525 ELSIF (p_task_rec.objectcode = 'BOOKING')
4526 THEN
4527
4528 if ((repeat_to_nonrepeat=true) or (nonrepeat_to_repeat=true) ) then
4529
4530
4531 delete_task_data ( p_task_rec =>p_task_rec, p_delete_map_flag =>true);
4532
4533 create_new_data( p_task_rec =>p_task_rec,
4534 p_mapping_type =>null,
4535 p_exclusion_tbl =>p_exclusion_tbl,
4536 p_resource_id =>p_resource_id,
4537 p_resource_type =>p_resource_type );
4538
4539
4540 else -- of if (repeat_to_nonrepeat=true) then
4541
4542
4543 if (l_planned_start_date=l_planned_end_date) then
4544
4545 l_planned_end_date:=l_planned_start_date +1 -1/(60*24) ;
4546
4547 end if;
4548 cac_view_appt_pvt.update_external_appointment (
4549 p_object_version_number =>l_ovn,
4550 p_task_id =>l_task_id,
4551 p_task_name => NVL (l_task_name, ' '),
4552 p_task_type_id => get_default_task_type,
4553 p_description => p_task_rec.description,
4554 p_task_priority_id => p_task_rec.priorityid,
4555 p_planned_start_date => l_planned_start_date,
4556 p_planned_end_date => l_planned_end_date,
4557 p_timezone_id => G_SERVER_TIMEZONE_ID, --changed from g_client_timezone_id as all the value must be stored at server timezone
4558 p_private_flag => p_task_rec.privateFlag,
4559 p_alarm_start => l_alarm_mins,
4560 p_alarm_on => p_task_rec.alarmflag,
4561 --p_category_id => l_category_id,
4562 p_free_busy_type => p_task_rec.free_busy_type,
4563 p_change_mode => jtf_task_repeat_appt_pvt.g_all,
4564 x_return_status => l_return_status
4565 );
4566
4567 OPEN getCollabDetails(l_task_id);
4568
4569 FETCH getCollabDetails INTO l_collab_details;
4570
4571 -- Update the rows only if there are some information in the CAC_VIEW_COLLAB_DETAILS table
4572 --otherwise close the cursor.
4573 IF (getCollabDetails%FOUND) THEN
4574
4575 l_location := SUBSTRB(p_task_rec.locations,1,100);
4576
4577 cac_view_collab_details_pkg.update_row
4578 (x_collab_id=> l_collab_details.collab_id ,
4579 x_task_id=> l_task_id,
4580 x_meeting_mode=>l_collab_details.meeting_mode,
4581 x_meeting_id=>l_collab_details.meeting_id,
4582 x_meeting_url=>l_collab_details.meeting_url,
4583 x_join_url=>l_collab_details.join_url,
4584 x_playback_url=>l_collab_details.playback_url,
4585 x_download_url=>l_collab_details.download_url,
4586 x_chat_url=>l_collab_details.chat_url,
4587 x_is_standalone_location=>l_collab_details.is_standalone_location,
4588 x_location=>l_location,
4589 x_dial_in=>p_task_rec.dial_in,
4590 x_last_update_date=>SYSDATE,
4591 x_last_updated_by=>jtf_task_utl.updated_by,
4592 x_last_update_login=>jtf_task_utl.login_id);
4593
4594 END IF;
4595
4596
4597 IF (getCollabDetails%ISOPEN) THEN
4598 CLOSE getCollabDetails;
4599 END IF;
4600
4601 do_mapping (
4602 p_task_id => l_task_id,
4603 p_operation => g_modify,
4604 x_task_sync_id => p_task_rec.syncid,
4605 p_principal_id => p_task_rec.principal_id
4606 );
4607
4608 end if;-- for if ((repeat_to_nonrepeat=true) or (nonrepeat_to_repeat=true) ) then
4609
4610
4611 END IF;
4612
4613 if ((repeat_to_nonrepeat=false) and (nonrepeat_to_repeat=false) ) then
4614
4615 IF NOT cac_sync_common.is_success (l_return_status)
4616 THEN-- Failed to update a task
4617
4618 cac_sync_common.put_messages_to_result (
4619 p_task_rec,
4620 p_status => 2,
4621 p_user_message => 'JTA_SYNC_UPDATE_TASK_FAIL'
4622 ); -- l_return_status
4623 END IF;
4624 end if;
4625
4626 ELSIF ((l_update_type = g_update_status)) --and (compare_task_rec(p_task_rec)=true))
4627 THEN
4628 if not (compare_task_rec(p_task_rec)) then
4629
4630 cac_sync_common.put_messages_to_result (
4631 p_task_rec,
4632 p_status => 2,
4633 p_user_message => 'CAC_SYNC_APPT_PERMISSION_DENY',
4634 p_token_name=>'P_APPOINTMENT_SUBJECT',
4635 p_token_value=>p_task_rec.subject
4636 ); -- l_return_status
4637 else
4638
4639 l_task_assignment_id := get_assignment_id (
4640 p_task_id => l_task_id,
4641 p_resource_id => p_resource_id,
4642 p_resource_type => p_resource_type
4643 );
4644 l_ovn := get_ovn (p_task_assignment_id => l_task_assignment_id);
4645
4646 if g_fb_type_changed
4647 then
4648 l_free_busy_type := p_task_rec.free_busy_type;
4649 end if;
4650
4651 jtf_task_assignments_pvt.update_task_assignment (
4652 p_api_version => 1.0,
4653 p_object_version_number => l_ovn,
4654 p_init_msg_list => fnd_api.g_true,
4655 p_commit => fnd_api.g_false,
4656 p_task_assignment_id => l_task_assignment_id,
4657 p_assignment_status_id => 3, -- ACCEPT
4658 p_free_busy_type => l_free_busy_type,
4659 p_enable_workflow => fnd_profile.value('JTF_TASK_ENABLE_WORKFLOW'),
4660 p_abort_workflow => fnd_profile.value('JTF_TASK_ABORT_PREV_WF'),
4661 x_return_status => l_return_status,
4662 x_msg_count => l_msg_count,
4663 x_msg_data => l_msg_data
4664 );
4665
4666 IF NOT cac_sync_common.is_success (l_return_status)
4667 THEN
4668 cac_sync_common.put_messages_to_result (
4669 p_task_rec,
4670 p_status => 2,
4671 p_user_message => 'JTA_SYNC_UPDATE_STS_FAIL'
4672 );
4673 END IF;
4674 end if;
4675
4676 overwrite_task_record(
4677 p_task_rec=>p_task_rec,
4678 p_resource_id =>p_resource_id,
4679 p_resource_type =>p_resource_type);
4680 --check when user cant update the appointment as he is the invitee not the owner
4681 ELSE--IF --(l_update_type=g_do_nothing) then
4682 /*
4683
4684 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4685 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_sync_task_common.create_new_data', ' When l_update_type=g_do_nothing for task '|| p_task_rec.subject);
4686 end if;*/
4687 if not (compare_task_rec(p_task_rec)) then -- code added for bug 5264362
4688
4689 cac_sync_common.put_messages_to_result (
4690 p_task_rec,
4691 p_status => 2,
4692 p_user_message => 'CAC_SYNC_APPT_PERMISSION_DENY',
4693 p_token_name=>'P_APPOINTMENT_SUBJECT',
4694 p_token_value=>p_task_rec.subject
4695 ); -- l_return_status
4696
4697 --update the record. overwrite saved data
4698 overwrite_task_record(
4699 p_task_rec=>p_task_rec,
4700 p_resource_id =>p_resource_id,
4701 p_resource_type =>p_resource_type);
4702 elsif g_fb_type_changed
4703 then
4704 l_task_assignment_id := get_assignment_id (
4705 p_task_id => l_task_id,
4706 p_resource_id => p_resource_id,
4707 p_resource_type => p_resource_type
4708 );
4709
4710 l_ovn := get_ovn (p_task_assignment_id => l_task_assignment_id);
4711
4712 jtf_task_assignments_pvt.update_task_assignment (
4713 p_api_version => 1.0,
4714 p_object_version_number => l_ovn,
4715 p_init_msg_list => fnd_api.g_true,
4716 p_commit => fnd_api.g_false,
4717 p_task_assignment_id => l_task_assignment_id,
4718 p_free_busy_type => p_task_rec.free_busy_type,
4719 p_enable_workflow => fnd_profile.value('JTF_TASK_ENABLE_WORKFLOW'),
4720 p_abort_workflow => fnd_profile.value('JTF_TASK_ABORT_PREV_WF'),
4721 --p_update_all => l_update_all,
4722 --p_enable_workflow => 'N',
4723 --p_abort_workflow => 'N',
4724 x_return_status => l_return_status,
4725 x_msg_count => l_msg_count,
4726 x_msg_data => l_msg_data
4727 );
4728
4729 end if; -- code added for bug 5264362
4730
4731 END IF; -- l_update_type
4732
4733 -- Check the current status and update if it's succeeded
4734 IF nvl(p_task_rec.resultId,0) < 2
4735 THEN
4736 cac_sync_common.put_messages_to_result (
4737 p_task_rec,
4738 p_status => g_sync_success,
4739 p_user_message => 'JTA_SYNC_SUCCESS'
4740 );
4741 --CHANGE TO GMT
4742 p_task_rec.syncanchor := convert_server_to_gmt (SYSDATE);
4743 END IF;
4744
4745 END update_existing_data;
4746
4747
4748
4749
4750 PROCEDURE delete_exclusion_task (
4751 p_repeating_task_id IN NUMBER,
4752 x_task_rec IN OUT NOCOPY cac_sync_task.task_rec
4753 )
4754 IS
4755 l_ovn NUMBER;
4756 l_return_status VARCHAR2(1);
4757 l_msg_data VARCHAR2(2000);
4758 l_msg_count NUMBER;
4759 BEGIN
4760
4761 l_return_status := fnd_api.g_ret_sts_success;
4762
4763 l_ovn := get_ovn (p_task_id => p_repeating_task_id);
4764
4765 IF x_task_rec.objectcode = G_APPOINTMENT
4766 THEN
4767 jta_cal_appointment_pvt.delete_appointment (
4768 p_object_version_number => l_ovn,
4769 p_task_id => p_repeating_task_id,
4770 p_delete_future_recurrences => fnd_api.g_false,
4771 x_return_status => l_return_status
4772 );
4773 ELSE
4774 jtf_tasks_pvt.delete_task (
4775 p_api_version => 1.0,
4776 p_init_msg_list => fnd_api.g_true,
4777 p_commit => fnd_api.g_false,
4778 p_task_id => p_repeating_task_id,
4779 p_object_version_number => l_ovn,
4780 x_return_status => l_return_status,
4781 p_delete_future_recurrences => fnd_api.g_false ,
4782 x_msg_count => l_msg_count,
4783 x_msg_data => l_msg_data
4784 );
4785 END IF;
4786
4787 IF cac_sync_common.is_success (l_return_status)
4788 THEN
4789 x_task_rec.syncanchor := convert_server_to_gmt (SYSDATE);
4790
4791 cac_sync_common.put_messages_to_result (
4792 x_task_rec,
4793 p_status => g_sync_success,
4794 p_user_message => 'JTA_SYNC_SUCCESS'
4795 );
4796 ELSE
4797 cac_sync_common.put_messages_to_result (
4798 x_task_rec,
4799 p_status => 2,
4800 p_user_message => 'JTA_SYNC_DELETE_EXCLUSION_FAIL'
4801 );
4802 END IF;
4803 END delete_exclusion_task;
4804
4805 PROCEDURE delete_task_data (
4806 p_task_rec IN OUT NOCOPY cac_sync_task.task_rec,
4807 p_delete_map_flag IN BOOLEAN
4808 )
4809 IS
4810
4811 l_task_id NUMBER;
4812 l_return_status VARCHAR2(1);
4813 l_msg_data VARCHAR2(2000);
4814 l_msg_count NUMBER;
4815 BEGIN
4816 l_return_status := fnd_api.g_ret_sts_success;
4817
4818 l_task_id := get_task_id (p_sync_id => p_task_rec.syncid);
4819
4820 delete_tasks(p_task_id => l_task_id,
4821 x_return_status => l_return_status);
4822
4823
4824 If cac_sync_common.is_success (l_return_status)
4825 THEN
4826 p_task_rec.syncanchor := convert_server_to_gmt (SYSDATE + 1 / (24 * 60 * 60));
4827
4828 IF p_delete_map_flag
4829 THEN
4830
4831 cac_sync_task_map_pkg.delete_row (
4832 p_task_sync_id => p_task_rec.syncid
4833 );
4834 END IF;
4835
4836 cac_sync_common.put_messages_to_result (
4837 p_task_rec,
4838 p_status => g_sync_success,
4839 p_user_message => 'JTA_SYNC_SUCCESS'
4840 );
4841 ELSE
4842 cac_sync_common.put_messages_to_result (
4843 p_task_rec,
4844 p_status => 2,
4845 p_user_message => 'JTA_SYNC_DELETE_TASK_FAILED'
4846 );
4847 END IF;
4848
4849 END delete_task_data;
4850
4851 PROCEDURE reject_task_data (p_task_rec IN OUT NOCOPY cac_sync_task.task_rec)
4852 IS
4853 l_task_id NUMBER;
4854 l_rec_rule_id NUMBER;
4855 l_task_assignment_id NUMBER;
4856 l_ovn NUMBER;
4857 l_resource_id NUMBER;
4858 l_resource_type VARCHAR2(30);
4859 l_deleted BOOLEAN := FALSE;
4860 l_return_status VARCHAR2(1);
4861 l_msg_data VARCHAR2(2000);
4862 l_msg_count NUMBER;
4863
4864 --CURSOR c_tasks (b_recurrence_rule_id NUMBER, b_task_id NUMBER)
4865 --IS
4866 -- SELECT task_id, source_object_type_code
4867 -- FROM jtf_tasks_b
4868 -- WHERE ( b_recurrence_rule_id IS NOT NULL
4869 -- AND recurrence_rule_id = b_recurrence_rule_id)
4870 -- OR ( b_recurrence_rule_id IS NULL
4871 -- AND task_id = b_task_id);
4872
4873 l_update_all varchar2(1) ;
4874
4875 BEGIN
4876 get_resource_details (l_resource_id, l_resource_type);
4877
4878 l_task_id := get_task_id (p_sync_id => p_task_rec.syncid);
4879 l_rec_rule_id := get_recurrence_rule_id (p_task_id => l_task_id);
4880
4881 if l_rec_rule_id is not null then
4882 l_update_all := 'Y' ;
4883 else
4884 l_update_all := null ;
4885 end if ;
4886
4887 l_task_assignment_id := get_assignment_id (
4888 p_task_id => l_task_id,
4889 p_resource_id => l_resource_id,
4890 p_resource_type => l_resource_type
4891 );
4892
4893 l_ovn := get_ovn (p_task_assignment_id => l_task_assignment_id);
4894
4895 jtf_task_assignments_pvt.update_task_assignment (
4896 p_api_version => 1.0,
4897 p_object_version_number => l_ovn,
4898 p_init_msg_list => fnd_api.g_true,
4899 p_commit => fnd_api.g_false,
4900 p_task_assignment_id => l_task_assignment_id,
4901 p_assignment_status_id => 4, -- reject
4902 x_return_status => l_return_status,
4903 x_msg_count => l_msg_count,
4904 x_msg_data => l_msg_data
4905 --p_enable_workflow => 'N',
4906 --p_abort_workflow => 'N'
4907 );
4908
4909 IF cac_sync_common.is_success (l_return_status)
4910 THEN
4911 p_task_rec.syncanchor := convert_server_to_gmt(SYSDATE);
4912
4913 cac_sync_common.put_messages_to_result (
4914 p_task_rec,
4915 p_status => g_sync_success,
4916 p_user_message => 'JTA_SYNC_SUCCESS'
4917 );
4918
4919 cac_sync_task_map_pkg.delete_row(p_task_sync_id => p_task_rec.syncid);
4920 ELSE
4921 cac_sync_common.put_messages_to_result (
4922 p_task_rec,
4923 p_status => 2,
4924 p_user_message => 'JTA_SYNC_UPDATE_STS_FAIL'
4925 );
4926 END IF;
4927 END reject_task_data;
4928
4929 FUNCTION changed_repeat_rule (p_task_rec IN cac_sync_task.task_rec)
4930 RETURN BOOLEAN
4931 IS
4932 CURSOR c_task_recur (b_task_id NUMBER)
4933 IS
4934 SELECT jtrr.*
4935 FROM jtf_task_recur_rules jtrr, jtf_tasks_b jtb
4936 WHERE jtb.task_id = b_task_id
4937 AND jtb.recurrence_rule_id IS NOT NULL
4938 AND jtrr.recurrence_rule_id = jtb.recurrence_rule_id;
4939
4940 l_task_id NUMBER;
4941 l_rec_task_recur c_task_recur%ROWTYPE;
4942 l_start_date DATE;
4943 l_end_date DATE;
4944 l_current DATE := SYSDATE;
4945 l_date_of_month NUMBER;
4946 BEGIN
4947 l_task_id := get_task_id (p_sync_id => p_task_rec.syncid);
4948 OPEN c_task_recur (l_task_id);
4949 FETCH c_task_recur into l_rec_task_recur;
4950
4951 IF c_task_recur%NOTFOUND
4952 THEN
4953 CLOSE c_task_recur;
4954 RETURN FALSE;
4955 END IF;
4956
4957 CLOSE c_task_recur;
4958 convert_recur_date_to_server (
4959 p_base_start_time => p_task_rec.plannedstartdate,
4960 p_base_end_time => p_task_rec.plannedenddate,
4961 p_start_date => p_task_rec.start_date,
4962 p_end_date => p_task_rec.end_date,
4963 p_occurs_which => p_task_rec.occurs_which,
4964 p_uom => p_task_rec.unit_of_measure,
4965 x_date_of_month => l_date_of_month,
4966 x_start_date => l_start_date,
4967 x_end_date => l_end_date
4968 );
4969
4970 IF NVL (p_task_rec.occurs_which, 0) =
4971 NVL (l_rec_task_recur.occurs_which, 0)
4972 AND NVL (p_task_rec.date_of_month, 0) =
4973 NVL (l_rec_task_recur.date_of_month, 0)
4974 AND p_task_rec.unit_of_measure = l_rec_task_recur.occurs_uom
4975 AND NVL (p_task_rec.occurs_every, 0) =
4976 NVL (l_rec_task_recur.occurs_every, 0)
4977 /*AND NVL (p_task_rec.occurs_number, 0) =
4978 NVL (l_rec_task_recur.occurs_number, 0)*/
4979 AND l_start_date = l_rec_task_recur.start_date_active
4980 AND NVL (l_end_date, TRUNC (l_current)) =
4981 NVL (l_rec_task_recur.end_date_active, TRUNC (l_current))
4982 AND NVL (p_task_rec.sunday, '?') = NVL (l_rec_task_recur.sunday, '?')
4983 AND NVL (p_task_rec.monday, '?') = NVL (l_rec_task_recur.monday, '?')
4984 AND NVL (p_task_rec.tuesday, '?') =
4985 NVL (l_rec_task_recur.tuesday, '?')
4986 AND NVL (p_task_rec.wednesday, '?') =
4987 NVL (l_rec_task_recur.wednesday, '?')
4988 AND NVL (p_task_rec.thursday, '?') =
4989 NVL (l_rec_task_recur.thursday, '?')
4990 AND NVL (p_task_rec.friday, '?') = NVL (l_rec_task_recur.friday, '?')
4991 AND NVL (p_task_rec.saturday, '?') =
4992 NVL (l_rec_task_recur.saturday, '?')
4993 THEN
4994 RETURN FALSE;
4995 ELSE
4996 RETURN TRUE;
4997 END IF;
4998 END changed_repeat_rule;
4999
5000 PROCEDURE transformstatus (
5001 p_task_status_id IN OUT NOCOPY NUMBER,
5002 p_task_sync_id IN NUMBER,
5003 x_operation IN OUT NOCOPY VARCHAR2
5004 )
5005 IS
5006 l_rejected_flag CHAR;
5007 l_cancelled_flag CHAR;
5008 l_completed_flag CHAR;
5009 l_closed_flag CHAR;
5010 l_assigned_flag CHAR;
5011 l_working_flag CHAR;
5012 l_schedulable_flag CHAR;
5013 l_accepted_flag CHAR;
5014 l_on_hold_flag CHAR;
5015 l_approved_flag CHAR;
5016
5017 CURSOR c_task_status
5018 IS
5019 SELECT closed_flag, completed_flag, cancelled_flag, rejected_flag,
5020 assigned_flag, working_flag, schedulable_flag, accepted_flag,
5021 on_hold_flag, approved_flag
5022
5023 FROM jtf_task_statuses_b
5024 WHERE task_status_id = p_task_status_id;
5025 BEGIN
5026 IF (p_task_status_id = 8)
5027 OR (p_task_status_id = 4)
5028 OR (p_task_status_id = 7)
5029 OR (p_task_status_id = 12)
5030 OR (p_task_status_id = 15)
5031 OR (p_task_status_id = 16)
5032 OR (p_task_status_id = 6)
5033 THEN
5034 IF (p_task_status_id = 8)
5035 OR (p_task_status_id = 4)
5036 OR (p_task_status_id = 7)
5037 THEN
5038 x_operation := cac_sync_task_common.g_delete;
5039 END IF;
5040
5041 IF (p_task_status_id = 12)
5042 OR (p_task_status_id = 15)
5043 OR (p_task_status_id = 16)
5044 OR (p_task_status_id = 6)
5045 THEN
5046 IF p_task_sync_id IS NOT NULL
5047 THEN
5048 OPEN c_task_status;
5049 FETCH c_task_status into l_closed_flag, l_rejected_flag, l_cancelled_flag, l_completed_flag,
5050 l_assigned_flag, l_working_flag, l_schedulable_flag,
5051 l_accepted_flag, l_on_hold_flag, l_approved_flag;
5052
5053 IF (NVL (l_closed_flag, 'N') = 'Y')
5054 OR (NVL (l_rejected_flag, 'N') = 'Y')
5055 OR (NVL (l_completed_flag, 'N') = 'Y')
5056 OR (NVL (l_cancelled_flag, 'N') = 'Y')
5057 THEN
5058 x_operation := cac_sync_task_common.g_delete;
5059 END IF;
5060 CLOSE c_task_status;
5061 END IF;
5062
5063 END IF;
5064
5065 ELSE
5066 OPEN c_task_status;
5067 FETCH c_task_status into l_closed_flag, l_rejected_flag, l_cancelled_flag, l_completed_flag,
5068 l_assigned_flag, l_working_flag, l_schedulable_flag,
5069 l_accepted_flag, l_on_hold_flag, l_approved_flag;
5070
5071 IF (NVL (l_closed_flag, 'N') = 'Y')
5072 OR (NVL (l_rejected_flag, 'N') = 'Y')
5073 OR (NVL (l_completed_flag, 'N') = 'Y')
5074 OR (NVL (l_cancelled_flag, 'N') = 'Y')
5075 THEN
5076 x_operation := cac_sync_task_common.g_delete;
5077 ELSIF (NVL (l_assigned_flag, 'N') = 'Y')
5078 THEN p_task_status_id := 12;
5079 ELSIF (NVL (l_working_flag, 'N') = 'Y')
5080 THEN p_task_status_id := 15;
5081 ELSIF (NVL (l_schedulable_flag, 'N') = 'Y')
5082 THEN p_task_status_id := 12;
5083 ELSIF (NVL (l_accepted_flag, 'N') = 'Y')
5084 THEN p_task_status_id := 15;
5085 ELSIF (NVL (l_on_hold_flag, 'N') = 'Y')
5086 THEN p_task_status_id := 16;
5087 ELSIF (NVL (l_approved_flag, 'N') = 'Y')
5088 THEN p_task_status_id := 15;
5089 END IF;
5090 x_operation := cac_sync_task_common.g_modify;
5091
5092 CLOSE c_task_status;
5093
5094 END IF;
5095 END transformstatus;
5096 /*
5097 FUNCTION getchangedstatusid (
5098 p_task_status_id IN NUMBER,
5099 p_source_object_type_code IN VARCHAR2
5100 )
5101 RETURN NUMBER
5102 IS
5103 BEGIN
5104 IF (p_source_object_type_code = G_APPOINTMENT)
5105 THEN
5106 RETURN p_task_status_id;
5107 ELSE
5108 IF (checkuserstatusrule ())
5109 THEN
5110 RETURN fnd_api.g_miss_num;
5111 ELSE
5112 RETURN p_task_status_id;
5113 END IF;
5114 END IF;
5115 END getchangedstatusid;
5116
5117
5118 FUNCTION checkUserStatusRule
5119 RETURN BOOLEAN
5120 IS
5121 l_num NUMBER;
5122 BEGIN
5123 IF G_USER_STATUS_RULE IS NULL
5124 THEN
5125 SELECT 1 INTO l_num
5126 FROM
5127 fnd_user
5128 ,fnd_user_resp_groups
5129 ,fnd_responsibility
5130 ,jtf_state_rules_b
5131 , jtf_state_responsibilities
5132 WHERE fnd_user.user_id = fnd_global.user_id
5133 AND fnd_user.user_id = fnd_user_resp_groups.user_id
5134 AND fnd_user_resp_groups.responsibility_id = jtf_state_responsibilities.responsibility_id
5135 AND jtf_state_responsibilities.rule_id = jtf_state_rules_b.rule_id;
5136 G_USER_STATUS_RULE := TRUE;
5137 RETURN TRUE;
5138 ELSE
5139 RETURN G_USER_STATUS_RULE;
5140 END IF;
5141
5142 EXCEPTION
5143 WHEN no_data_found
5144 THEN
5145 G_USER_STATUS_RULE := FALSE;
5146 RETURN FALSE;
5147 WHEN too_many_rows
5148 THEN
5149 G_USER_STATUS_RULE := TRUE;
5150 RETURN TRUE;
5151 END checkUserStatusRule;
5152 */--commented out checkUserStatusRule as it is not used in the code
5153 -- Added to fix bug 2382927
5154 FUNCTION validate_syncid(p_syncid IN NUMBER)
5155 RETURN BOOLEAN
5156 IS
5157 CURSOR c_mapping (b_syncid NUMBER) IS
5158 SELECT 1
5159 FROM jta_sync_task_mapping
5160 WHERE task_sync_id = b_syncid;
5161
5162 l_dummy NUMBER;
5163 l_valid BOOLEAN := TRUE;
5164 BEGIN
5165
5166 ---------------------------------
5167 -- Fix Bug# 2395004
5168 IF NVL(p_syncid,-1) < 1
5169 THEN
5170 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
5171 fnd_msg_pub.add;
5172
5173 fnd_message.set_name('JTF', 'JTA_SYNC_INVALID_SYNCID');
5174 fnd_message.set_token('PROC_NAME','cac_sync_task_COMMON.GET_TASK_ID');
5175 fnd_msg_pub.add;
5176
5177 raise_application_error (-20100,cac_sync_common.get_messages);
5178 END IF;
5179 ---------------------------------
5180
5181 OPEN c_mapping (p_syncid);
5182 FETCH c_mapping INTO l_dummy;
5183 IF c_mapping%NOTFOUND
5184 THEN
5185 l_valid := FALSE;
5186 END IF;
5187 CLOSE c_mapping;
5188
5189 RETURN l_valid;
5190 END validate_syncid;
5191
5192
5193
5194 FUNCTION get_dial_in_value( p_task_id IN NUMBER)
5195
5196 RETURN VARCHAR2
5197 IS
5198 cursor getDialInValue(b_task_id number)
5199 is
5200 select cactl.dial_in
5201 from
5202 cac_view_collab_details_tl cactl,
5203 cac_view_collab_details cac
5204 where cac.collab_id=cactl.collab_id
5205 and cactl.LANGUAGE = userenv('LANG')
5206 and cac.task_id=b_task_id;
5207
5208 l_dial_in VARCHAR2(100);
5209
5210 BEGIN
5211
5212 open getDialInValue(p_task_id);
5213
5214 fetch getDialInValue into l_dial_in;
5215
5216 IF (getDialInValue%NOTFOUND) THEN
5217
5218 CLOSE getDialInValue;
5219 return null;
5220
5221 END IF;
5222
5223 IF (getDialInValue%ISOPEN) THEN
5224 CLOSE getDialInValue;
5225 END IF;
5226
5227 return l_dial_in;
5228
5229 END get_dial_in_value;
5230
5231
5232 procedure delete_bookings (
5233 p_principal_id IN NUMBER
5234
5235 )
5236 IS
5237
5238 CURSOR getUserId(b_principal_id IN NUMBER)
5239 IS
5240 SELECT user_id
5241 FROM cac_sync_principals
5242 WHERE principal_id = b_principal_id;
5243
5244 CURSOR getBookings(b_principal_id IN NUMBER, b_user_id IN NUMBER)
5245 IS
5246 SELECT jtb.task_id,jtb.object_version_number,jstm.task_sync_id, jtb.source_object_type_code objectcode
5247 FROM jta_sync_task_mapping jstm, jtf_tasks_b jtb
5248 WHERE jstm.principal_id IN
5249 (SELECT principal_id
5250 FROM cac_sync_principals
5251 WHERE device_id = (SELECT device_id FROM cac_sync_principals
5252 WHERE principal_id = b_principal_id)
5253 AND user_id = b_user_id)
5254 AND jstm.task_id=jtb.task_id
5255 AND jtb.entity IN ('BOOKING', 'APPOINTMENT')
5256 AND jtb.source_object_type_code='EXTERNAL APPOINTMENT';
5257
5258 p_getBookings getBookings%rowtype;
5259 l_getUserId getUserId%ROWTYPE;
5260 l_return_status VARCHAR2(1);
5261 l_msg_count NUMBER;
5262 l_msg_data VARCHAR2(2000);
5263 l_user_id NUMBER;
5264
5265
5266 BEGIN
5267 l_return_status := fnd_api.g_ret_sts_success;
5268
5269 OPEN getUserId(p_principal_id);
5270
5271 FETCH getUserId INTO l_getUserId ;
5272 IF (getUserId%FOUND) THEN
5273
5274 l_user_id := l_getUserId.user_id;
5275
5276 IF (getUserId%ISOPEN) THEN
5277 CLOSE getUserId;
5278 END IF;
5279
5280 END IF;
5281
5282 OPEN getBookings(p_principal_id, l_user_id);
5283
5284 LOOP --start of the loop
5285
5286 fetch getBookings into p_getBookings ;
5287
5288 /* if (getBookings%NOTFOUND) then
5289 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5290 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_sync_task_common.delete_booking', 'no booking is found for the principal id ' || p_principal_id);
5291 end if;
5292 else
5293
5294 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5295 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_sync_task_common.delete_booking', 'booking is found for the principal id ' || p_principal_id);
5296 end if;
5297
5298 end if;*/
5299
5300
5301
5302 exit when getBookings%NOTFOUND;
5303
5304 delete_tasks(p_task_id => p_getBookings.task_id,
5305 x_return_status => l_return_status);
5306
5307
5308 IF cac_sync_common.is_success (l_return_status)
5309 THEN
5310 cac_sync_task_map_pkg.delete_row (
5311 p_task_sync_id => p_getBookings.task_sync_id);
5312
5313 /* if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5314 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_sync_task_common.delete_booking', 'success from cac_bookings_pub.delete_booking');
5315 end if;
5316
5317 else --failure from delete_booking API
5318
5319 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5320 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_sync_task_common.delete_booking', 'failure from cac_bookings_pub.delete_booking');
5321 end if;
5322 */
5323
5324 END IF;
5325
5326
5327 END LOOP; --end of the loop
5328
5329 IF (getBookings%ISOPEN) THEN
5330 close getBookings;
5331 END IF;
5332
5333 END delete_bookings;
5334
5335
5336
5337 function is_recur_rule_same (
5338 p_task_rec IN OUT NOCOPY cac_sync_task.task_rec
5339
5340 ) return boolean
5341 IS
5342
5343 cursor get_recur_rule(b_task_id IN NUMBER)
5344 is
5345 select OCCURS_WHICH,DAY_OF_WEEK,DATE_OF_MONTH,
5346 OCCURS_MONTH,OCCURS_UOM,OCCURS_EVERY,
5347 OCCURS_NUMBER,START_DATE_ACTIVE,END_DATE_ACTIVE,
5348 SUNDAY,MONDAY,TUESDAY,WEDNESDAY,THURSDAY,FRIDAY,
5349 SATURDAY
5350 from jtf_task_recur_rules jtrr, jtf_tasks_b b
5351 where b.recurrence_rule_id=jtrr.recurrence_rule_id
5352 and b.task_id=b_task_id;
5353
5354 l_get_recur_rule get_recur_rule%rowtype;
5355 l_task_id NUMBER;
5356
5357 l_resource_id NUMBER;
5358 l_resource_type VARCHAR2(30);
5359 l_update_type VARCHAR2(15);
5360
5361 BEGIN
5362
5363 l_task_id:=get_task_id (p_sync_id=>p_task_rec.syncid );
5364 cac_sync_task_common.get_resource_details (l_resource_id, l_resource_type);
5365
5366 l_update_type := get_update_type (
5367 p_task_id => l_task_id,
5368 p_resource_id => l_resource_id,
5369 p_subject => p_task_rec.subject
5370 );
5371
5372
5373 if (l_update_type = g_update_all) then
5374 open get_recur_rule(l_task_id);
5375
5376 fetch get_recur_rule into l_get_recur_rule ;
5377
5378 if (get_recur_rule%FOUND) then
5379
5380
5381 IF (get_recur_rule%ISOPEN) THEN
5382 close get_recur_rule;
5383 END IF;
5384
5385
5386 if (nvl(p_task_rec.unit_of_measure,null)<> nvl(l_get_recur_rule.OCCURS_UOM,null)) then
5387 return false;
5388 end if;
5389 /*
5390 if (nvl(p_task_rec.occurs_every,null)<> nvl(l_get_recur_rule.OCCURS_EVERY,null)) then
5391 return false;
5392 end if;
5393 */
5394 if (nvl(p_task_rec.start_date,null)<> nvl(l_get_recur_rule.start_date_active,null)) then
5395 return false;
5396 end if;
5397
5398 if (nvl(p_task_rec.end_date,null)<> nvl(l_get_recur_rule.end_date_active,null)) then
5399 return false;
5400 end if;
5401 /*
5402 if (nvl(p_task_rec.date_of_month,null)<> nvl(l_get_recur_rule.date_of_month,null)) then
5403 return false;
5404 end if;
5405
5406 if (nvl(p_task_rec.occurs_which,null)<> nvl(l_get_recur_rule.OCCURS_WHICH,null)) then
5407 return false;
5408 end if;
5409
5410 if (nvl(p_task_rec.sunday,null)<> nvl(l_get_recur_rule.sunday,null)) then
5411 return false;
5412 end if;
5413
5414 if (nvl(p_task_rec.monday,null)<> nvl(l_get_recur_rule.monday,null)) then
5415 return false;
5416 end if;
5417
5418 if (nvl(p_task_rec.tuesday,null)<> nvl(l_get_recur_rule.tuesday,null)) then
5419 return false;
5420 end if;
5421
5422 if (nvl(p_task_rec.wednesday,null)<> nvl(l_get_recur_rule.wednesday,null)) then
5423 return false;
5424 end if;
5425
5426 if (nvl(p_task_rec.thursday,null)<> nvl(l_get_recur_rule.thursday,null)) then
5427 return false;
5428 end if;
5429
5430 if (nvl(p_task_rec.friday,null)<> nvl(l_get_recur_rule.friday,null)) then
5431 return false;
5432 end if;
5433
5434 if (nvl(p_task_rec.saturday,null)<> nvl(l_get_recur_rule.saturday,null)) then
5435 return false;
5436 end if;*/
5437
5438 ELSE
5439
5440 IF (get_recur_rule%ISOPEN) THEN
5441 close get_recur_rule;
5442 END IF;
5443 return true;
5444
5445 END IF;
5446
5447 end if; -- if (l_update_type = g_update_all)
5448
5449 IF (get_recur_rule%ISOPEN) THEN
5450 close get_recur_rule;
5451 END IF;
5452 return true;
5453 END is_recur_rule_same;
5454
5455
5456 PROCEDURE delete_tasks (
5457 p_task_id IN OUT NOCOPY NUMBER,
5458 x_return_status IN OUT NOCOPY VARCHAR2
5459 )
5460 IS
5461
5462 cursor get_tasks_ids (b_recurrence_rule_id IN NUMBER)
5463 is
5464 select jte.task_id from
5465 jta_task_exclusions jte
5466 where jte.recurrence_rule_id=b_recurrence_rule_id;
5467
5468 l_tsk_ids get_tasks_ids%rowtype;
5469 l_recurrence_rule_id jtf_tasks_b.recurrence_rule_id%type;
5470 l_ovn NUMBER;
5471 l_msg_data VARCHAR2(2000);
5472 l_msg_count NUMBER;
5473
5474 BEGIN
5475 l_ovn := get_ovn (p_task_id => p_task_id);
5476
5477 l_recurrence_rule_id :=get_recurrence_rule_id(p_task_id);
5478
5479 jtf_tasks_pvt.delete_task (
5480 p_api_version => 1.0,
5481 p_init_msg_list => fnd_api.g_false,
5482 p_commit => fnd_api.g_false,
5483 p_task_id => p_task_id,
5484 p_object_version_number => l_ovn,
5485 p_delete_future_recurrences => 'A',
5486 x_return_status => x_return_status,
5487 x_msg_count => l_msg_count,
5488 x_msg_data => l_msg_data
5489 );
5490
5491 IF cac_sync_common.is_success (x_return_status) then
5492 ---deleting all exclusions....
5493 if (l_recurrence_rule_id is not null) then
5494
5495 open get_tasks_ids(l_recurrence_rule_id);
5496
5497 LOOP
5498
5499 fetch get_tasks_ids into l_tsk_ids;
5500 exit when get_tasks_ids%NOTFOUND;
5501 l_ovn := get_ovn (p_task_id =>l_tsk_ids.task_id);
5502
5503 jtf_tasks_pvt.delete_task (
5504 p_api_version => 1.0,
5505 p_init_msg_list => fnd_api.g_false,
5506 p_commit => fnd_api.g_false,
5507 p_task_id => l_tsk_ids.task_id,
5508 p_object_version_number => l_ovn,
5509 p_delete_future_recurrences => jtf_task_repeat_appt_pvt.G_ONE,
5510 x_return_status => x_return_status,
5511 x_msg_count => l_msg_count,
5512 x_msg_data => l_msg_data
5513 );
5514
5515 END LOOP;
5516
5517 if (get_tasks_ids%ISOPEN) then
5518 close get_tasks_ids;
5519 end if;
5520
5521 end if;
5522
5523 end if;-- for IF cac_sync_common.is_success (x_return_status)
5524 --end deleting all exclusions.....
5525
5526 END delete_tasks;
5527
5528 /* Introduced this procedure for bug#5191856
5529 This will be called before updating an appointment to see if the appointment still exists
5530 If the appointment has been deleted/declined, it will return false and update will not be called.
5531 Instead Sync will throw error saying that appointment has been deleted in server. */
5532 PROCEDURE is_appointment_existing(p_task_sync_id IN NUMBER, x_result OUT NOCOPY VARCHAR2)
5533 IS
5534
5535 CURSOR check_appt(b_resource_id NUMBER, b_resource_type_code VARCHAR2)
5536 IS
5537 SELECT a.assignment_status_id
5538 from jtf_tasks_b b, jtf_task_all_assignments a, jta_sync_Task_mapping s
5539 where b.task_id = a.task_id
5540 and s.task_id = b.task_id
5541 and s.task_sync_id = p_task_sync_id
5542 and a.resource_id = b_resource_id
5543 and a.resource_type_code = b_resource_type_code;
5544
5545 l_resource_id NUMBER;
5546 l_resource_type_code VARCHAR2(50);
5547 l_assignment_status_id NUMBER;
5548 BEGIN
5549
5550 get_resource_details (l_resource_id, l_resource_type_code);
5551
5552 OPEN check_appt(l_resource_id, l_resource_type_code);
5553 FETCH check_appt INTO l_assignment_status_id;
5554 IF check_appt%NOTFOUND
5555 THEN
5556 l_assignment_status_id := -1;
5557 END IF;
5558
5559 IF check_appt%ISOPEN
5560 THEN
5561 CLOSE check_appt;
5562 END IF;
5563
5564 IF (l_assignment_status_id = -1 OR l_assignment_status_id = 4)
5565 THEN
5566 x_result := 'N';
5567 ELSE
5568 x_result := 'Y';
5569 END IF;
5570
5571 END is_appointment_existing;
5572
5573
5574 END CAC_SYNC_TASK_COMMON ;