[Home] [Help]
PACKAGE: APPS.CAC_SYNC_TASK_CURSORS
Source
1 PACKAGE CAC_SYNC_TASK_CURSORS AUTHID CURRENT_USER AS
2 /* $Header: cacvstzs.pls 120.16.12010000.1 2008/07/24 18:03:39 appldev ship $ */
3 /*======================================================================+
4 | Copyright (c) 1995 Oracle Corporation Redwood Shores, California, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 | FILENAME |
8 | cacvstzs.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 | 02-FEB-2005 rhshriva Modified the cursors c_new_repeating_task |
20 | and c_modify_repeating_task |
21 *=======================================================================*/
22
23 -- G_SYNC_DAYS_BEFORE CONSTANT NUMBER := 7;
24
25 G_SYNC_DAYS_BEFORE CONSTANT NUMBER := TO_NUMBER (nvl(fnd_profile.VALUE ('CAC_SYNC_DAYS_BEFORE'),0));
26 --G_CAC_SYNC_TASK_NO_DATE VARCHAR2(10) := fnd_profile.VALUE ('CAC_SYNC_TASK_NO_DATE');
27 G_CAC_SYNC_TASK_NO_DATE VARCHAR2(50) := 'FALSE';
28
29 CURSOR c_new_repeating_task (
30 b_syncanchor DATE,
31 b_resource_id NUMBER,
32 b_principal_id NUMBER,
33 b_resource_type VARCHAR2,
34 b_source_object_type VARCHAR2
35 )
36 IS
37 SELECT DISTINCT tl.task_name,
38 tl.description,
39 t.date_selected,
40 ( trunc(rc.start_date_active)+ (t.planned_start_date-trunc(t.planned_start_date))) planned_start_date,
41 t.planned_end_date,
42 t.scheduled_start_date,
43 t.scheduled_end_date,
44 t.actual_start_date,
45 t.actual_end_date,
46 t.calendar_start_date,
47 t.calendar_end_date,
48 t.task_status_id,
49 tb.importance_level importance_level,
50 NVL (t.alarm_on, 'N') alarm_on,
51 t.alarm_start,
52 UPPER (t.alarm_start_uom) alarm_start_uom,
53 NVL (t.private_flag, 'N') private_flag,
54 t.deleted_flag,
55 NVL (t.timezone_id, cac_sync_task_common.g_client_timezone_id) timezone_id,
56 t.task_id,
57 t.owner_type_code,
58 t.source_object_type_code,
59 rc.recurrence_rule_id,
60 rc.occurs_uom,
61 rc.occurs_every,
62 rc.occurs_number,
63 greatest(rc.start_date_active, t.planned_start_date) start_date_active,
64 rc.end_date_active,
65 rc.sunday,
66 rc.monday,
67 rc.tuesday,
68 rc.wednesday,
69 rc.thursday,
70 rc.friday,
71 rc.saturday,
72 rc.date_of_month,
73 rc.occurs_which,
74 greatest(t.object_changed_date, ta.last_update_date) new_timestamp,
75 CAC_VIEW_UTIL_PUB.get_locations(t.task_id) locations,
76 ta.free_busy_type free_busy_type,
77 t.entity
78 FROM jtf_task_recur_rules rc,
79 jtf_task_statuses_b ts,
80 jtf_task_priorities_b tb,
81 jtf_tasks_tl tl,
82 jtf_task_all_assignments ta,
83 jtf_tasks_b t,
84 (SELECT jtb.recurrence_rule_id
85 , MIN (jtb.task_id) task_id
86 FROM jtf_tasks_b jtb
87 , jtf_task_all_assignments jtaa
88 WHERE jtaa.resource_id = b_resource_id
89 AND jtaa.resource_type_code = b_resource_type
90 AND jtb.task_id = jtaa.task_id
91 AND jtb.entity='APPOINTMENT'
92 -- AND jtb.source_object_type_code = b_source_object_type , using entity instead of source_object_type_code
93 AND b_source_object_type = 'APPOINTMENT'
94 AND jtb.recurrence_rule_id IS NOT NULL
95 HAVING NOT EXISTS (SELECT 1
96 FROM jta_sync_task_mapping tm
97 WHERE tm.task_id = MIN(jtb.task_id)
98 AND tm.resource_id = cac_sync_task.g_login_resource_id
99 AND tm.principal_id = b_principal_id)
100 GROUP BY jtb.recurrence_rule_id) newtask
101 WHERE t.task_id = newtask.task_id
102 AND ( (b_resource_type = 'RS_GROUP' AND
103 t.owner_type_code = b_resource_type AND
104 t.owner_id = b_resource_id
105 )
106 OR
107 (b_resource_type = 'RS_EMPLOYEE' AND
108 t.owner_type_code = b_resource_type
109 )
110 )
111 AND (t.object_changed_date > b_syncanchor OR
112 ta.last_update_date > b_syncanchor)
113 AND exists
114 --rhshriva.. The following lines will check for start date to be in range for any of the repeating tasks
115 (select 1 from jtf_tasks_b b where
116 b.recurrence_rule_id = ( select a.recurrence_rule_id from jtf_tasks_b a
117 where a.task_id= t.task_id)
118 and b.calendar_start_date > (sysdate-G_SYNC_DAYS_BEFORE))
119
120 -- AND t.calendar_start_date > (sysdate - G_SYNC_DAYS_BEFORE )
121 --AND ( ta.last_update_date > (sysdate - G_SYNC_DAYS_BEFORE )
122 -- OR t.object_changed_date > (sysdate - G_SYNC_DAYS_BEFORE ) and rownum=1)
123 AND ta.task_id = t.task_id
124 AND ta.resource_id = b_resource_id
125 AND ta.resource_type_code = b_resource_type
126 AND ta.assignment_status_id IN (3 -- Accepted
127 ,18 -- Invited
128 )
129 AND tl.task_id = t.task_id
130 AND ts.task_status_id = t.task_status_id
131 AND tl.language = USERENV ('LANG')
132 AND t.entity='APPOINTMENT'
133 AND rc.recurrence_rule_id = t.recurrence_rule_id
134 AND tb.task_priority_id (+) = t.task_priority_id
135 AND NVL (t.deleted_flag, 'N') = 'N';
136
137 CURSOR c_modify_repeating_task (
138 b_syncanchor DATE,
139 b_resource_id NUMBER,
140 b_principal_id NUMBER,
141 b_resource_type VARCHAR2,
142 b_source_object_type VARCHAR2
143 )
144 IS
145 SELECT DISTINCT tl.task_name,
146 tl.description,
147 t.date_selected,
148 ( trunc(rc.start_date_active)+ (t.planned_start_date-trunc(t.planned_start_date))) planned_start_date,
149 t.planned_end_date,
150 t.scheduled_start_date,
151 t.scheduled_end_date,
152 t.actual_start_date,
153 t.actual_end_date,
154 t.calendar_start_date,
155 t.calendar_end_date,
156 t.task_status_id,
157 tb.importance_level l_importance_level,
158 NVL (t.alarm_on, 'N') alarm_on,
159 t.alarm_start,
160 UPPER (t.alarm_start_uom) alarm_start_uom,
161 NVL (t.private_flag, 'N') private_flag,
162 t.deleted_flag,
163 NVL (t.timezone_id, cac_sync_task_common.g_client_timezone_id) timezone_id,
164 tm.task_sync_id,
165 t.task_id,
166 t.owner_type_code,
167 t.source_object_type_code,
168 ta.assignment_status_id,
169 rc.recurrence_rule_id,
170 rc.occurs_uom,
171 rc.occurs_every,
172 rc.occurs_number,
173 greatest(rc.start_date_active, t.planned_start_date) start_date_active,
174 rc.end_date_active,
175 rc.sunday,
176 rc.monday,
177 rc.tuesday,
178 rc.wednesday,
179 rc.thursday,
180 rc.friday,
181 rc.saturday,
182 rc.date_of_month,
183 rc.occurs_which,
184 greatest(t.object_changed_date, ta.last_update_date) new_timestamp,
185 CAC_VIEW_UTIL_PUB.get_locations(t.task_id) locations,
186 ta.free_busy_type free_busy_type,
187 t.entity
188 FROM jtf_task_recur_rules rc,
189 jta_sync_task_mapping tm,
190 jtf_task_all_assignments ta,
191 jtf_task_statuses_b ts,
192 jtf_task_priorities_b tb,
193 jtf_tasks_tl tl,
194 jtf_tasks_b t
195 WHERE tm.resource_id = cac_sync_task.g_login_resource_id
196 AND tm.principal_id = b_principal_id
197 AND t.task_id = tm.task_id
198 AND t.task_id = ta.task_id
199 AND tl.task_id = t.task_id
200 AND ( (b_resource_type = 'RS_GROUP' AND
201 t.owner_type_code = b_resource_type AND
202 t.owner_id = b_resource_id
203 )
204 OR
205 (b_resource_type = 'RS_EMPLOYEE' AND
206 t.owner_type_code = b_resource_type
207 )
208 )
209 AND ta.resource_id = b_resource_id
210 AND ta.assignment_status_id IN (3, -- Accepted
211 18 -- Invited
212 )
213 AND ts.task_status_id = t.task_status_id
214 AND t.recurrence_rule_id IS NOT NULL
215 AND rc.recurrence_rule_id = t.recurrence_rule_id
216 AND tb.task_priority_id (+) = t.task_priority_id
217 -- AND t.task_type_id <> 22
218 -- AND task_type_id <> 22
219 AND tl.language = USERENV ('LANG')
220 AND t.entity='APPOINTMENT'
221 -- AND t.source_object_type_code = b_source_object_type using entity instead of source_object_type_code
222 AND b_source_object_type = 'APPOINTMENT'
223 AND exists
224 --rhshriva.. The following lines will check for start date to be in range for any of the repeating tasks
225 (select 1 from jtf_tasks_b b where
226 b.recurrence_rule_id = t.recurrence_rule_id
227 --commented for bug 5352055
228 /*( select a.recurrence_rule_id from jtf_tasks_b a
229 where a.task_id= t.task_id)*/
230 and b.calendar_start_date > (sysdate-G_SYNC_DAYS_BEFORE)
231 and rownum=1 )
232
233 -- AND t.calendar_start_date > (sysdate - G_SYNC_DAYS_BEFORE )
234 AND ( rc.last_update_date > b_syncanchor
235 OR (SELECT MAX(last_update_date) FROM jtf_task_all_assignments
236 WHERE task_id = t.task_id) > b_syncanchor
237 OR t.object_changed_date > b_syncanchor
238 OR (SELECT MAX(m.object_changed_date) FROM jtf_tasks_b m WHERE m.task_id IN
239 (SELECT jte.task_id FROM jta_task_exclusions jte
240 WHERE jte.recurrence_rule_id=t.recurrence_rule_id)) > b_syncanchor)
241 AND ts.task_status_id = t.task_status_id
242 AND NVL (t.deleted_flag, 'N') = 'N';
243
244
245 CURSOR c_exclusions (
246 b_syncanchor DATE,
247 b_recurrence_rule_id NUMBER,
248 b_resource_id NUMBER,
249 b_resource_type VARCHAR2
250 )
251 IS
252 SELECT jte.exclusion_date,
253 jte.task_id,
254 tl.task_name,
255 tl.description,
256 tb.date_selected,
257 tb.planned_start_date,
258 tb.planned_end_date,
259 tb.scheduled_start_date,
260 tb.scheduled_end_date,
261 tb.actual_start_date,
262 tb.actual_end_date,
263 tb.calendar_start_date,
264 tb.calendar_end_date,
265 tb.task_status_id,
266 tp.importance_level importance_level,
267 NVL (tb.alarm_on, 'N') alarm_on,
268 tb.alarm_start,
269 UPPER (tb.alarm_start_uom) alarm_start_uom,
270 NVL (tb.private_flag, 'N') private_flag,
271 tb.deleted_flag,
272 NVL (tb.timezone_id, cac_sync_task_common.g_client_timezone_id) timezone_id,
273 tb.owner_type_code,
274 tb.owner_id,
275 tb.source_object_type_code,
276 jte.recurrence_rule_id,
277 greatest(tb.last_update_date ) new_timestamp,
278 CAC_VIEW_UTIL_PUB.get_locations(tl.task_id) locations,
279 ta.free_busy_type,
280 tb.entity,
281 case
282 when ((nvl(tb.deleted_flag,'N')='Y') and tb.recurrence_rule_id is null) then cac_sync_task_common.g_delete
283 when ((nvl(tb.deleted_flag,'N')='N') and tb.recurrence_rule_id is null) then cac_sync_task_common.g_modify
284 when ( tb.recurrence_rule_id is not null) then cac_sync_task_common.g_delete
285
286
287
288
289 end as event
290 FROM
291 jtf_task_priorities_b tp,
292 jtf_tasks_tl tl,
293 jtf_tasks_b tb,
294 jta_task_exclusions jte,
295 jtf_task_all_assignments ta
296 WHERE jte.recurrence_rule_id = b_recurrence_rule_id
297 and tb.task_id=jte.task_id
298 and tl.task_id=tb.task_id
299 and tl.language=userenv('LANG')
300 AND ta.task_id = tb.task_id
301 AND ta.resource_id = b_resource_id
302 AND ta.resource_type_code = b_resource_type
303 AND ta.assignment_status_id IN (3 -- Accepted
304 ,18 -- Invited
305 )
306 and tb.task_priority_id(+)=tp.task_priority_id
307 and tb.entity='APPOINTMENT'
308 and tb.source_object_type_code='APPOINTMENT';
309 -- AND ((tb.object_changed_date > b_syncanchor ) or (tb.last_update_date > b_syncanchor));
310
311
312 CURSOR c_delete_task (b_syncanchor DATE,
313 b_resource_id NUMBER,
314 b_principal_id NUMBER,
315 b_resource_type VARCHAR2,
316 b_source_object_type VARCHAR2)
317 IS
318 SELECT tm.task_sync_id
319 FROM jtf_tasks_b t
320 , jta_sync_task_mapping tm
321 WHERE tm.resource_id = cac_sync_task.g_login_resource_id
322 AND tm.principal_id = b_principal_id
323 AND t.task_id = tm.task_id
324 AND ( (b_resource_type = 'RS_GROUP' AND
325 t.owner_type_code = b_resource_type AND
326 t.owner_id = b_resource_id
327 )
328 OR
329 (b_resource_type = 'RS_EMPLOYEE' AND
330 t.owner_type_code = b_resource_type
331 )
332 )
333 AND NVL (t.deleted_flag, 'N') = 'Y'
334 AND decode(t.source_object_type_code,'APPOINTMENT','APPOINTMENT','TASK') = b_source_object_type
335 AND task_type_id <> 22
336 AND t.object_changed_date > b_syncanchor;
337
338 -- This cursor is working for only appointment
339 -- If the invitee rejects the invitation, then it sends a delete signal
340 CURSOR c_delete_assignee_reject (b_syncanchor DATE,
341 b_resource_id NUMBER,
342 b_principal_id NUMBER,
343 b_resource_type VARCHAR2,
344 b_source_object_type VARCHAR2)
345 IS
346 SELECT tm.task_sync_id
347 FROM jtf_tasks_b t
348 , jta_sync_task_mapping tm
349 , jtf_task_all_assignments ta
350 WHERE tm.resource_id = cac_sync_task.g_login_resource_id
351 AND tm.principal_id = b_principal_id
352 AND t.task_id = tm.task_id
353 AND t.owner_type_code = b_resource_type
354 AND b_resource_type = 'RS_EMPLOYEE'
355 AND NVL (t.deleted_flag, 'N') = 'N'
356 AND t.task_type_id <> 22
357 AND decode(t.source_object_type_code,'APPOINTMENT','APPOINTMENT','TASK') = b_source_object_type
358 AND decode(t.entity, 'APPOINTMENT','APPOINTMENT','TASK', 'TASK','BOOKING') = b_source_object_type
359 AND b_source_object_type = 'APPOINTMENT' -- Fix bug 2442496
360 AND ta.task_id = t.task_id
361 AND ta.resource_id = b_resource_id
362 AND ta.resource_type_code = b_resource_type
363 AND nvl(ta.assignee_role,'ASSIGNEE') = 'ASSIGNEE' -- Fix bug 2442496
364 AND ta.assignment_status_id = 4 -- Reject Status
365 AND ta.last_update_date > b_syncanchor;
366
367 CURSOR c_delete_rejected_tasks (b_syncanchor DATE,
368 b_resource_id NUMBER,
369 b_resource_type VARCHAR2,
370 b_principal_id NUMBER,
371 b_source_object_type VARCHAR2)
372 IS
373 SELECT tm.task_sync_id
374 FROM jtf_tasks_b t
375 , jta_sync_task_mapping tm
376 WHERE tm.resource_id = cac_sync_task.g_login_resource_id
377 AND tm.principal_id = b_principal_id
378 AND t.task_id = tm.task_id
379 AND ( (b_resource_type = 'RS_GROUP' AND
380 t.owner_type_code = b_resource_type AND
381 t.owner_id = b_resource_id
382 )
383 OR
384 (b_resource_type = 'RS_EMPLOYEE' AND
385 t.owner_type_code = b_resource_type
386 )
387 )
388 AND NVL (t.deleted_flag, 'N') = 'N'
389 AND t.task_type_id <> 22
390 AND decode(t.source_object_type_code,'APPOINTMENT','APPOINTMENT','TASK') = b_source_object_type
391 AND decode(t.entity, 'APPOINTMENT','APPOINTMENT','TASK', 'TASK','BOOKING') = b_source_object_type
392 AND
393 (
394 -- Closed Status
395 NVL(t.open_flag,'Y') = 'N' -- Enh# 2666995
396
397 -- endless task
398 OR
399 ( t.calendar_end_date IS NULL AND
400 t.calendar_start_date IS NOT NULL AND
401 t.source_object_type_code <> 'APPOINTMENT')
402
403 -- Appointment with the spanned Date
404 OR ( t.source_object_type_code = 'APPOINTMENT' AND
405 TRUNC (t.calendar_start_date) <> TRUNC (t.calendar_end_date)
406 )
407 )
408 --AND t.object_changed_date > b_syncanchor ;
409 AND t.object_changed_date > b_syncanchor ;
410
411
412 CURSOR c_delete_assignment (b_syncanchor DATE,
413 b_resource_id NUMBER,
414 b_resource_type VARCHAR2,
415 b_principal_id NUMBER,
416 b_source_object_type VARCHAR2)
417 IS
418 SELECT tm.task_sync_id
419 FROM jtf_tasks_b t
420 , jta_sync_task_mapping tm
421 WHERE tm.resource_id = cac_sync_task.g_login_resource_id
422 AND tm.principal_id = b_principal_id
423 AND t.task_id = tm.task_id
424 AND t.owner_type_code = b_resource_type
425 AND b_resource_type = 'RS_EMPLOYEE'
426 AND NVL (t.deleted_flag, 'N') = 'N'
427 AND decode(t.source_object_type_code,'APPOINTMENT','APPOINTMENT','TASK') = b_source_object_type
428 AND decode(t.entity, 'APPOINTMENT','APPOINTMENT','TASK', 'TASK','BOOKING') = b_source_object_type
429 --this indicates that the resource is no longer on the task, however the task is not deleted
430 AND NOT EXISTS (SELECT 1
431 FROM jtf_task_all_assignments asgn
432 WHERE asgn.task_id = t.task_id
433 AND asgn.resource_id = b_resource_id/*and asgn.last_update_date >= b_syncanchor */
434 );
435
436 CURSOR c_new_non_repeat_task (b_syncanchor DATE,
437 b_resource_id NUMBER,
438 b_principal_id NUMBER,
439 b_resource_type VARCHAR2,
440 b_source_object_type VARCHAR2)
441 IS
442 SELECT distinct tl.task_name,
443 tl.description,
444 t.date_selected,
445 t.planned_start_date,
446 t.planned_end_date,
447 t.scheduled_start_date,
448 t.scheduled_end_date,
449 t.actual_start_date,
450 t.actual_end_date,
451 t.calendar_start_date,
452 t.calendar_end_date,
453 t.task_status_id,
454 tb.importance_level,
455 NVL (t.alarm_on, 'N') alarm_on,
456 t.alarm_start,
457 UPPER (t.alarm_start_uom) alarm_start_uom,
458 NVL (t.private_flag, 'N') private_flag,
459 t.deleted_flag,
460 NVL (t.timezone_id, cac_sync_task_common.g_client_timezone_id) timezone_id,
461 t.task_id,
462 t.owner_type_code,
463 t.source_object_type_code,
464 t.recurrence_rule_id,
465 ta.assignment_status_id,
466 greatest(t.object_changed_date, ta.last_update_date) new_timestamp,
467 CAC_VIEW_UTIL_PUB.get_locations(t.task_id) locations,
468 ta.free_busy_type free_busy_type,
469 t.entity
470 FROM jtf_task_all_assignments ta,
471 jtf_task_priorities_b tb,
472 jtf_tasks_tl tl,
473 jtf_tasks_b t
474 WHERE ta.resource_id = b_resource_id
475 AND ta.resource_type_code = b_resource_type
476 /* AND ta.assignment_status_id IN (3, -- Accepted
477 18 -- Invited
478 )*/
479 --commented out the assignment status code. Please look at bug 4404244
480 AND ta.assignment_status_id <> 4 --rejected task should not be synced to the client._bug 4698139
481 AND t.task_id = ta.task_id
482 AND ( (b_resource_type = 'RS_GROUP' AND
483 t.owner_type_code = b_resource_type AND
484 t.owner_id = b_resource_id
485 )
486 OR
487 (b_resource_type = 'RS_EMPLOYEE' AND
488 t.owner_type_code = b_resource_type
489 )
490 )
491 AND task_type_id <> 22
492 AND NOT EXISTS (SELECT 1
493 FROM jta_sync_task_mapping tm
494 WHERE tm.task_id = t.task_id
495 AND tm.resource_id = cac_sync_task.g_login_resource_id
496 AND tm.principal_id = b_principal_id)
497 AND t.recurrence_rule_id IS NULL
498 AND decode(t.source_object_type_code,'APPOINTMENT','APPOINTMENT','TASK') = b_source_object_type
499 AND decode(t.entity, 'APPOINTMENT','APPOINTMENT','TASK', 'TASK','BOOKING') = b_source_object_type
500 AND tl.task_id = t.task_id
501 AND tl.language = USERENV ('LANG')
502
503 AND tb.task_priority_id (+) = t.task_priority_id
504 AND NVL(t.open_flag,'Y') = 'Y' -- Enh# 2666995
505 AND ( ta.last_update_date > b_syncanchor
506 OR t.object_changed_date > b_syncanchor)
507 AND nvl(t.calendar_end_date,sysdate+1) > (sysdate - G_SYNC_DAYS_BEFORE )
508 AND NVL (t.deleted_flag, 'N') = 'N'
509 and NOT EXISTS (select 1 from jta_task_exclusions where task_id=t.task_id);
510
511 -- (t.calendar_end_date > (sysdate - G_SYNC_DAYS_BEFORE ))
512 -- OR
513 -- ( (G_CAC_SYNC_TASK_NO_DATE = 'TRUE') AND (t.calendar_end_date IS NULL) );
514
515
516
517 CURSOR c_modify_non_repeat_task (
518 b_syncanchor DATE,
519 b_resource_id NUMBER,
520 b_principal_id NUMBER,
521 b_resource_type VARCHAR2,
522 b_source_object_type VARCHAR2
523 )
524 IS
525 SELECT distinct tl.task_name,
526 tl.description,
527 t.date_selected,
528 t.planned_start_date,
529 t.planned_end_date,
530 t.scheduled_start_date,
531 t.scheduled_end_date,
532 t.actual_start_date,
533 t.actual_end_date,
534 t.calendar_start_date,
535 t.calendar_end_date,
536 t.task_status_id,
537 tb.importance_level,
538 NVL (t.alarm_on, 'N') alarm_on,
539 t.alarm_start,
540 UPPER (t.alarm_start_uom) alarm_start_uom,
541 NVL (t.private_flag, 'N') private_flag,
542 t.deleted_flag,
543 NVL (t.timezone_id, cac_sync_task_common.g_client_timezone_id) timezone_id,
544 tm.task_sync_id,
545 t.task_id,
546 t.owner_type_code,
547 t.source_object_type_code,
548 t.recurrence_rule_id,
549 ta.assignment_status_id,
550 greatest(t.object_changed_date, ta.last_update_date) new_timestamp,
551 CAC_VIEW_UTIL_PUB.get_locations(t.task_id) locations,
552 ta.free_busy_type free_busy_type,
553 t.entity
554 FROM jta_sync_task_mapping tm,
555 jtf_task_all_assignments ta,
556 jtf_task_priorities_b tb,
557 jtf_tasks_tl tl,
558 jtf_tasks_b t
559 WHERE tm.resource_id = cac_sync_task.g_login_resource_id
560 AND tm.principal_id = b_principal_id
561 AND t.task_id = tm.task_id
562 AND ( (b_resource_type = 'RS_GROUP' AND
563 t.owner_type_code = b_resource_type AND
564 t.owner_id = b_resource_id
565 )
566 OR
567 (b_resource_type = 'RS_EMPLOYEE' AND
568 t.owner_type_code = b_resource_type
569 )
570 )
571 AND ta.task_id = t.task_id
572 AND ta.assignment_status_id <> 4 --rejected task should not be synced to the client. Please refer to bug 4698139
573 -- AND ta.assignment_status_id IN (3, 18) -- Accepted, Invited, --commented out the assignment status code. Please look at bug 4404244
574 AND ta.resource_id = b_resource_id
575 AND t.recurrence_rule_id IS NULL
576 AND task_type_id <> 22
577 AND ( t.object_changed_date > b_syncanchor
578 OR (SELECT MAX(last_update_date) FROM jtf_task_all_assignments
579 WHERE task_id = t.task_id) > b_syncanchor)
580 --AND ( ta.last_update_date > (sysdate - G_SYNC_DAYS_BEFORE )
581 -- OR t.object_changed_date > (sysdate - G_SYNC_DAYS_BEFORE ))
582 AND nvl(t.calendar_end_date,sysdate+1) > (sysdate - G_SYNC_DAYS_BEFORE )
583 AND tl.task_id = t.task_id
584 AND ta.resource_type_code = b_resource_type
585 AND ta.resource_id = b_resource_id
586 AND NVL(t.open_flag,'Y') = 'Y' -- Enh# 2666995
587 AND decode(t.source_object_type_code,'APPOINTMENT','APPOINTMENT','TASK') = b_source_object_type
588 AND decode(t.entity, 'APPOINTMENT','APPOINTMENT','TASK', 'TASK','BOOKING') = b_source_object_type
589 AND tl.language = USERENV ('LANG')
590 AND tb.task_priority_id (+) = t.task_priority_id
591 AND NVL (t.deleted_flag, 'N') = 'N'
592 and NOT EXISTS (select 1 from jta_task_exclusions where task_id=t.task_id);
593
594
595 CURSOR c_delete_unsubscribed (
596 b_resource_id NUMBER,
597 b_resource_type VARCHAR2,
598 b_principal_id NUMBER,
599 b_source_object_type VARCHAR2
600 )
601 IS
602 SELECT m.task_sync_id
603 FROM jtf_tasks_b b, jta_sync_task_mapping m
604 WHERE b.task_id = m.task_id
605 AND m.principal_id = b_principal_id
606 AND m.resource_id = cac_sync_task.g_login_resource_id
607 AND NVL (b.deleted_flag, 'N') = 'N'
608 AND b.owner_type_code ='RS_GROUP'
609 AND b.source_object_type_code = cac_sync_task_common.G_APPOINTMENT
610 AND b_resource_id = m.resource_id
611 AND b_source_object_type = cac_sync_task_common.G_APPOINTMENT
612 AND b_resource_type = 'RS_EMPLOYEE'
613 AND NOT EXISTS
614 (SELECT 1
615 FROM fnd_grants g
616 WHERE g.instance_pk1_value = to_char(b.owner_id) -- fix bug bug 2613008
617 AND g.grantee_key = to_char(cac_sync_task.g_login_resource_id)
618 );
619
620
621 END; -- Package Specification JTA_SYNC_TASK_CURSORS