[Home] [Help]
PACKAGE: APPS.JTA_SYNC_TASK_CURSORS
Source
1 PACKAGE JTA_SYNC_TASK_CURSORS AS
2 /* $Header: jtavstzs.pls 120.3 2005/09/08 06:02:40 deeprao ship $ */
3 /*======================================================================+
4 | Copyright (c) 1995 Oracle Corporation Redwood Shores, California, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 | FILENAME |
8 | jtavstzs.pls |
9 | |
10 | DESCRIPTION |
11 | This package has all the cursors |
12 | |
13 | NOTES |
14 | |
15 | |
16 |Date Developer Change |
17 |----------- --------- ------------------------------------------- |
18 |04-02-02 SSALLAKA Created |
19 |04-19-2002 SSALLAKA Updated with Object_changted_date instead |
20 | last_update_date in jtf_tasks_b table |
21 |04-22-2002 CJANG Added t.owner_type_code = 'RS_EMPLOYEE' |
22 | in the cursor c_delete_assignment |
23 |04-23-2002 CJANG When a user change non-repeat to repeat |
24 | after sync, the cursor c_new_repeating_task|
25 | was selecting the repeating as a new. |
26 | This bug is fixed by moving |
27 | jtaa.last_update_date > b_syncanchor from|
28 | inline view to outside where clause |
29 | This may affect performance. |
30 |04-24-2002 CJANG Modified the cursor c_modify_non_repeat_task
31 | and c_modify_non_repeat_task |
32 | and c_delete_tasks |
33 | and c_delete_rejected_tasks |
34 | to pick up all the non-task manager source|
35 |04-25-2002 CJANG Modified the cursor |
36 | 1) c_new_repeating_task: |
37 | Added ta.assignment_status_id IN (3,18)|
38 | Select the greatest start_date_active |
39 | 2) c_modify_repeating_task: |
40 | Added ta.assignment_status_id IN (3,18)|
41 | Select the greatest start_date_active |
42 | 3) c_delete_assignee_reject: Newly added |
43 | 4) c_delete_rejected_tasks: |
44 | |
45 | OR ( nvl(sts.completed_flag,'N') = 'Y' AND |
46 | nvl(sts.cancelled_flag,'N') = 'Y' AND |
47 | nvl(sts.rejected_flag,'N') = 'Y' AND |
48 | nvl(sts.closed_flag,'N') = 'Y' |
49 | ====> |
50 | OR ( nvl(sts.completed_flag,'N') = 'Y' OR |
51 | nvl(sts.cancelled_flag,'N') = 'Y' OR |
52 | nvl(sts.rejected_flag,'N') = 'Y' OR |
53 | nvl(sts.closed_flag,'N') = 'Y' |
54 | |
55 |04-26-2002 CJANG 1)Added two more conditions to the cursor |
56 | c_non_repeat_task |
57 | |
58 | AND ta.assignment_status_id IN (3, 18) |
59 | AND ta.resource_id = b_resource_id |
60 | not to pick up any rejected appts/task |
61 | 2)Modified the following statement in all |
62 | cursors |
63 | tm.resource_id = b_resource_id |
64 | ==>tm.resource_id = jta_sync_task.g_login_resource_id|
65 | 3)Modified all cursor for handling group id |
66 | |
67 |04-29-2002 CJANG Added a condition in c_new_non_repeat_task |
68 | "AND ta.assignment_status_id IN (3,18)" |
69 | |
70 |05-02-2002 CJANG Modified c_new_repeating_task to pick up |
71 | all the future appts modified. |
72 | Modified c_delete_rejected_tasks, |
73 | c_delete_assignment |
74 | to support diverse source_object_type |
75 | such as Opportunity, Lead etc |
76 | |
77 |07-02-2002 CJANG Fix Bug: 2442496 |
78 | Modified c_delete_assignee_reject to pick up|
79 | only the rejected appointment. |
80 | A task is not deleted from device even if |
81 | the task has been rejected by the current |
82 | logged-in assignee. |
83 |07-03-2002 CJANG After Code Review for Fix Bug: 2442496 |
84 | Modified c_delete_assignee_reject |
85 | ta.assignee_role = 'ASSIGNEE' |
86 | ==> nvl(ta.assignee_role,'ASSIGNEE') |
87 | = 'ASSIGNEE' |
88 | |
89 |18-JUL-2005 TSINGHAL Commented code 'assignment_status_id IN(3,18)'|
90 | to allow all assignment_status_id as per |
91 | bug 4397779 update |
92 | |
93 |08-SEP-2005 DEEPRAO Modified c_delete_unsubscribed |
94 | |
95 *=======================================================================*/
96 CURSOR c_new_repeating_task (
97 b_syncanchor DATE,
98 b_resource_id NUMBER,
99 b_resource_type VARCHAR2,
100 b_source_object_type VARCHAR2
101 )
102 IS
103 SELECT DISTINCT tl.task_name,
104 tl.description,
105 t.date_selected,
106 t.planned_start_date,
107 t.planned_end_date,
108 t.scheduled_start_date,
109 t.scheduled_end_date,
110 t.actual_start_date,
111 t.actual_end_date,
112 t.calendar_start_date,
113 t.calendar_end_date,
114 t.task_status_id,
115 tb.importance_level importance_level,
116 NVL (t.alarm_on, 'N') alarm_on,
117 t.alarm_start,
118 UPPER (t.alarm_start_uom) alarm_start_uom,
119 NVL (t.private_flag, 'N') private_flag,
120 t.deleted_flag,
121 NVL (t.timezone_id, jta_sync_task_common.g_client_timezone_id) timezone_id,
122 t.task_id,
123 t.owner_type_code,
124 t.source_object_type_code,
125 rc.recurrence_rule_id,
126 rc.occurs_uom,
127 rc.occurs_every,
128 rc.occurs_number,
129 greatest(rc.start_date_active, t.planned_start_date) start_date_active,
130 rc.end_date_active,
131 rc.sunday,
132 rc.monday,
133 rc.tuesday,
134 rc.wednesday,
135 rc.thursday,
136 rc.friday,
137 rc.saturday,
138 rc.date_of_month,
139 rc.occurs_which,
140 greatest(t.object_changed_date, ta.last_update_date) new_timestamp
141 FROM jtf_task_recur_rules rc,
142 jtf_task_statuses_b ts,
143 jtf_task_priorities_b tb,
144 jtf_tasks_tl tl,
145 jtf_task_all_assignments ta,
146 jtf_tasks_b t,
147 (SELECT jtb.recurrence_rule_id
148 , MIN (jtb.task_id) task_id
149 FROM jtf_tasks_b jtb
150 , jtf_task_all_assignments jtaa
151 WHERE jtaa.resource_id = b_resource_id
152 AND jtaa.resource_type_code = b_resource_type
153 AND jtb.task_id = jtaa.task_id
154 AND jtb.source_object_type_code = b_source_object_type
155 AND b_source_object_type = 'APPOINTMENT'
156 AND jtb.recurrence_rule_id IS NOT NULL
157 HAVING NOT EXISTS (SELECT 1
158 FROM jta_sync_task_mapping tm
159 WHERE tm.task_id = MIN(jtb.task_id)
160 AND tm.resource_id = jta_sync_task.g_login_resource_id)
161 GROUP BY jtb.recurrence_rule_id) newtask
162 WHERE t.task_id = newtask.task_id
163 AND ( (b_resource_type = 'RS_GROUP' AND
164 t.owner_type_code = b_resource_type AND
165 t.owner_id = b_resource_id
166 )
167 OR
168 (b_resource_type = 'RS_EMPLOYEE' AND
169 t.owner_type_code = b_resource_type
170 )
171 )
172 AND (t.object_changed_date > b_syncanchor OR
173 ta.last_update_date > b_syncanchor)
174 AND ta.task_id = t.task_id
175 AND ta.resource_id = b_resource_id
176 AND ta.resource_type_code = b_resource_type
177 -- Modifed by TSINGHAL for bug 4397779
178 /*
179 AND ta.assignment_status_id IN (3 -- Accepted
180 ,18 -- Invited
181 )*/
182 AND tl.task_id = t.task_id
183 AND ts.task_status_id = t.task_status_id
184 AND tl.language = USERENV ('LANG')
185 AND task_type_id <> 22
186 AND rc.recurrence_rule_id = t.recurrence_rule_id
187 AND tb.task_priority_id (+) = t.task_priority_id;
188
189 CURSOR c_modify_repeating_task (
190 b_syncanchor DATE,
191 b_resource_id NUMBER,
192 b_resource_type VARCHAR2,
193 b_source_object_type VARCHAR2
194 )
195 IS
196 SELECT DISTINCT tl.task_name,
197 tl.description,
198 t.date_selected,
199 t.planned_start_date,
200 t.planned_end_date,
201 t.scheduled_start_date,
202 t.scheduled_end_date,
203 t.actual_start_date,
204 t.actual_end_date,
205 t.calendar_start_date,
206 t.calendar_end_date,
207 t.task_status_id,
208 tb.importance_level l_importance_level,
209 NVL (t.alarm_on, 'N') alarm_on,
210 t.alarm_start,
211 UPPER (t.alarm_start_uom) alarm_start_uom,
212 NVL (t.private_flag, 'N') private_flag,
213 t.deleted_flag,
214 NVL (t.timezone_id, jta_sync_task_common.g_client_timezone_id) timezone_id,
215 tm.task_sync_id,
216 t.task_id,
217 t.owner_type_code,
218 t.source_object_type_code,
219 ta.assignment_status_id,
220 rc.recurrence_rule_id,
221 rc.occurs_uom,
222 rc.occurs_every,
223 rc.occurs_number,
224 greatest(rc.start_date_active, t.planned_start_date) start_date_active,
225 rc.end_date_active,
226 rc.sunday,
227 rc.monday,
228 rc.tuesday,
229 rc.wednesday,
230 rc.thursday,
231 rc.friday,
232 rc.saturday,
233 rc.date_of_month,
234 rc.occurs_which,
235 greatest(t.object_changed_date, ta.last_update_date) new_timestamp
236 FROM jtf_task_recur_rules rc,
237 jta_sync_task_mapping tm,
238 jtf_task_all_assignments ta,
239 jtf_task_statuses_b ts,
240 jtf_task_priorities_b tb,
241 jtf_tasks_tl tl,
242 jtf_tasks_b t
243 WHERE tm.resource_id = jta_sync_task.g_login_resource_id
244 AND t.task_id = tm.task_id
245 AND t.task_id = ta.task_id
246 AND tl.task_id = t.task_id
247 AND ( (b_resource_type = 'RS_GROUP' AND
248 t.owner_type_code = b_resource_type AND
249 t.owner_id = b_resource_id
250 )
251 OR
252 (b_resource_type = 'RS_EMPLOYEE' AND
253 t.owner_type_code = b_resource_type
254 )
255 )
256 AND ta.resource_id = b_resource_id
257 -- Modified by TSINGHAL for bug 4397779
258 /* AND ta.assignment_status_id IN (3, -- Accepted
259 18 -- Invited
260 )*/
261 AND ts.task_status_id = t.task_status_id
262 AND t.recurrence_rule_id IS NOT NULL
263 AND rc.recurrence_rule_id = t.recurrence_rule_id
264 AND tb.task_priority_id (+) = t.task_priority_id
265 AND task_type_id <> 22
266 AND tl.language = USERENV ('LANG')
267 AND t.source_object_type_code = b_source_object_type
268 AND b_source_object_type = 'APPOINTMENT'
269 AND ( rc.last_update_date > b_syncanchor
270 OR ta.last_update_date > b_syncanchor
271 OR t.object_changed_date > b_syncanchor)
272 AND ts.task_status_id = t.task_status_id ;
273
274 CURSOR c_delete_task (b_syncanchor DATE,
275 b_resource_id NUMBER,
276 b_resource_type VARCHAR2,
277 b_source_object_type VARCHAR2)
278 IS
279 SELECT tm.task_sync_id
280 FROM jtf_tasks_b t
281 , jta_sync_task_mapping tm
282 WHERE tm.resource_id = jta_sync_task.g_login_resource_id
283 AND t.task_id = tm.task_id
284 AND ( (b_resource_type = 'RS_GROUP' AND
285 t.owner_type_code = b_resource_type AND
286 t.owner_id = b_resource_id
287 )
288 OR
289 (b_resource_type = 'RS_EMPLOYEE' AND
290 t.owner_type_code = b_resource_type
291 )
292 )
293 AND NVL (t.deleted_flag, 'N') = 'Y'
294 AND decode(t.source_object_type_code,'APPOINTMENT','APPOINTMENT','TASK') = b_source_object_type
295 AND task_type_id <> 22
296 AND t.object_changed_date > b_syncanchor;
297
298 -- This cursor is working for only appointment
299 -- If the invitee rejects the invitation, then it sends a delete signal
300 CURSOR c_delete_assignee_reject (b_syncanchor DATE,
301 b_resource_id NUMBER,
302 b_resource_type VARCHAR2,
303 b_source_object_type VARCHAR2)
304 IS
305 SELECT tm.task_sync_id
306 FROM jtf_tasks_b t
307 , jta_sync_task_mapping tm
308 , jtf_task_all_assignments ta
309 WHERE tm.resource_id = jta_sync_task.g_login_resource_id
310 AND t.task_id = tm.task_id
311 AND t.owner_type_code = b_resource_type
312 AND b_resource_type = 'RS_EMPLOYEE'
313 AND NVL (t.deleted_flag, 'N') = 'N'
314 AND t.task_type_id <> 22
315 AND decode(t.source_object_type_code,'APPOINTMENT','APPOINTMENT','TASK') = b_source_object_type
316 AND b_source_object_type = 'APPOINTMENT' -- Fix bug 2442496
317 AND ta.task_id = t.task_id
318 AND ta.resource_id = b_resource_id
319 AND ta.resource_type_code = b_resource_type
320 AND nvl(ta.assignee_role,'ASSIGNEE') = 'ASSIGNEE' -- Fix bug 2442496
321 AND ta.assignment_status_id = 4 -- Reject Status
322 AND ta.last_update_date > b_syncanchor;
323
324 CURSOR c_delete_rejected_tasks (b_syncanchor DATE,
325 b_resource_id NUMBER,
326 b_resource_type VARCHAR2,
327 b_source_object_type VARCHAR2)
328 IS
329 SELECT tm.task_sync_id
330 FROM jtf_tasks_b t
331 , jta_sync_task_mapping tm
332 WHERE tm.resource_id = jta_sync_task.g_login_resource_id
333 AND t.task_id = tm.task_id
334 AND ( (b_resource_type = 'RS_GROUP' AND
335 t.owner_type_code = b_resource_type AND
336 t.owner_id = b_resource_id
337 )
338 OR
339 (b_resource_type = 'RS_EMPLOYEE' AND
340 t.owner_type_code = b_resource_type
341 )
342 )
343 AND NVL (t.deleted_flag, 'N') = 'N'
344 AND t.task_type_id <> 22
345 AND decode(t.source_object_type_code,'APPOINTMENT','APPOINTMENT','TASK') = b_source_object_type
346 AND
347 (
348 -- Closed Status
349 NVL(t.open_flag,'Y') = 'N' -- Enh# 2666995
350
351 -- endless task
352 OR
353 ( t.calendar_end_date IS NULL AND
354 t.calendar_start_date IS NOT NULL AND
355 t.source_object_type_code <> 'APPOINTMENT')
356
357 -- Appointment with the spanned Date
358 OR ( t.source_object_type_code = 'APPOINTMENT' AND
359 TRUNC (t.calendar_start_date) <> TRUNC (t.calendar_end_date)
360 )
361 )
362 AND t.object_changed_date > b_syncanchor;
363
364 CURSOR c_delete_assignment (b_syncanchor DATE,
365 b_resource_id NUMBER,
366 b_resource_type VARCHAR2,
367 b_source_object_type VARCHAR2)
368 IS
369 SELECT tm.task_sync_id
370 FROM jtf_tasks_b t
371 , jta_sync_task_mapping tm
372 WHERE tm.resource_id = jta_sync_task.g_login_resource_id
373 AND t.task_id = tm.task_id
374 AND t.owner_type_code = b_resource_type
375 AND b_resource_type = 'RS_EMPLOYEE'
376 AND NVL (t.deleted_flag, 'N') = 'N'
377 AND decode(t.source_object_type_code,'APPOINTMENT','APPOINTMENT','TASK') = b_source_object_type
378 --this indicates that the resource is no longer on the task, however the task is not deleted
379 AND NOT EXISTS (SELECT 1
380 FROM jtf_task_all_assignments asgn
381 WHERE asgn.task_id = t.task_id
382 AND asgn.resource_id = b_resource_id/*and asgn.last_update_date >= b_syncanchor */
383 );
384
385 CURSOR c_new_non_repeat_task (b_syncanchor DATE,
386 b_resource_id NUMBER,
387 b_resource_type VARCHAR2,
388 b_source_object_type VARCHAR2)
389 IS
390 SELECT distinct tl.task_name,
391 tl.description,
392 t.date_selected,
393 t.planned_start_date,
394 t.planned_end_date,
395 t.scheduled_start_date,
396 t.scheduled_end_date,
397 t.actual_start_date,
398 t.actual_end_date,
399 t.calendar_start_date,
400 t.calendar_end_date,
401 t.task_status_id,
402 tb.importance_level,
403 NVL (t.alarm_on, 'N') alarm_on,
404 t.alarm_start,
405 UPPER (t.alarm_start_uom) alarm_start_uom,
406 NVL (t.private_flag, 'N') private_flag,
407 t.deleted_flag,
408 NVL (t.timezone_id, jta_sync_task_common.g_client_timezone_id) timezone_id,
409 t.task_id,
410 t.owner_type_code,
411 t.source_object_type_code,
412 t.recurrence_rule_id,
413 ta.assignment_status_id,
414 greatest(t.object_changed_date, ta.last_update_date) new_timestamp
415 FROM jtf_task_all_assignments ta,
416 jtf_task_priorities_b tb,
417 jtf_tasks_tl tl,
418 jtf_tasks_b t
419 WHERE ta.resource_id = b_resource_id
420 AND ta.resource_type_code = b_resource_type
421 -- Commented by TSINGHAL to fix bug 4397779
422 /* AND ta.assignment_status_id IN (3, -- Accepted
423 18 -- Invited
424 )*/
425 AND t.task_id = ta.task_id
426 AND ( (b_resource_type = 'RS_GROUP' AND
427 t.owner_type_code = b_resource_type AND
428 t.owner_id = b_resource_id
429 )
430 OR
431 (b_resource_type = 'RS_EMPLOYEE' AND
432 t.owner_type_code = b_resource_type
433 )
434 )
435 AND task_type_id <> 22
436 AND NOT EXISTS (SELECT 1
437 FROM jta_sync_task_mapping tm
438 WHERE tm.task_id = t.task_id
439 AND tm.resource_id = jta_sync_task.g_login_resource_id)
440 AND t.recurrence_rule_id IS NULL
441 AND decode(t.source_object_type_code,'APPOINTMENT','APPOINTMENT','TASK') = b_source_object_type
442 AND tl.task_id = t.task_id
443 AND tl.language = USERENV ('LANG')
444 AND tb.task_priority_id (+) = t.task_priority_id
445 AND NVL(t.open_flag,'Y') = 'Y' -- Enh# 2666995
446 AND ( ta.last_update_date > b_syncanchor
447 OR t.object_changed_date > b_syncanchor);
448
449 CURSOR c_modify_non_repeat_task (
450 b_syncanchor DATE,
451 b_resource_id NUMBER,
452 b_resource_type VARCHAR2,
453 b_source_object_type VARCHAR2
454 )
455 IS
456 SELECT distinct tl.task_name,
457 tl.description,
458 t.date_selected,
459 t.planned_start_date,
460 t.planned_end_date,
461 t.scheduled_start_date,
462 t.scheduled_end_date,
463 t.actual_start_date,
464 t.actual_end_date,
465 t.calendar_start_date,
466 t.calendar_end_date,
467 t.task_status_id,
468 tb.importance_level,
469 NVL (t.alarm_on, 'N') alarm_on,
470 t.alarm_start,
471 UPPER (t.alarm_start_uom) alarm_start_uom,
472 NVL (t.private_flag, 'N') private_flag,
473 t.deleted_flag,
474 NVL (t.timezone_id, jta_sync_task_common.g_client_timezone_id) timezone_id,
475 tm.task_sync_id,
476 t.task_id,
477 t.owner_type_code,
478 t.source_object_type_code,
479 t.recurrence_rule_id,
480 ta.assignment_status_id,
481 greatest(t.object_changed_date, ta.last_update_date) new_timestamp
482 FROM jta_sync_task_mapping tm,
483 jtf_task_all_assignments ta,
484 jtf_task_priorities_b tb,
485 jtf_tasks_tl tl,
486 jtf_tasks_b t
487 WHERE tm.resource_id = jta_sync_task.g_login_resource_id
488 AND t.task_id = tm.task_id
489 AND ( (b_resource_type = 'RS_GROUP' AND
490 t.owner_type_code = b_resource_type AND
491 t.owner_id = b_resource_id
492 )
493 OR
494 (b_resource_type = 'RS_EMPLOYEE' AND
495 t.owner_type_code = b_resource_type
496 )
497 )
498 AND ta.task_id = t.task_id
499 -- Modified by TSINGHAL for bug 4397779
500 /* AND ta.assignment_status_id IN (3, 18) -- Accepted, Invited */
501 AND ta.resource_id = b_resource_id
502 AND t.recurrence_rule_id IS NULL
503 AND task_type_id <> 22
504 AND ( t.object_changed_date > b_syncanchor
505 OR ta.last_update_date > b_syncanchor)
506 AND tl.task_id = t.task_id
507 AND ta.resource_type_code = b_resource_type
508 AND ta.resource_id = b_resource_id
509 AND NVL(t.open_flag,'Y') = 'Y' -- Enh# 2666995
510 AND decode(t.source_object_type_code,'APPOINTMENT','APPOINTMENT','TASK') = b_source_object_type
511 AND tl.language = USERENV ('LANG')
512 AND tb.task_priority_id (+) = t.task_priority_id;
513
514
515 CURSOR c_delete_unsubscribed (
516 b_resource_id NUMBER,
517 b_resource_type VARCHAR2,
518 b_source_object_type VARCHAR2
519 )
520 IS
521 SELECT m.task_sync_id
522 FROM jtf_tasks_b b, jta_sync_task_mapping m
523 WHERE b.task_id = m.task_id
524 AND m.resource_id = jta_sync_task.g_login_resource_id
525 AND NVL (b.deleted_flag, 'N') = 'N'
526 AND b.owner_type_code ='RS_GROUP'
527 AND b.source_object_type_code = jta_sync_task_common.G_APPOINTMENT
528 AND b_resource_id = m.resource_id
529 AND b_source_object_type = jta_sync_task_common.G_APPOINTMENT
530 AND b_resource_type = 'RS_EMPLOYEE'
531 AND NOT EXISTS
532 (SELECT 1
533 FROM fnd_grants g
534 WHERE g.instance_pk1_value = to_char(b.owner_id) -- fix bug bug 2613008
535 AND g.grantee_key = to_char(jta_sync_task.g_login_resource_id) -- fix bug#4592625
536 );
537
538
539 END; -- Package Specification JTA_SYNC_TASK_CURSORS