[Home] [Help]
PACKAGE BODY: APPS.JTF_TASK_SYNC_UPGRADE_PKG
Source
1 PACKAGE BODY JTF_TASK_SYNC_UPGRADE_PKG AS
2 /* $Header: jtftkugb.pls 120.2 2006/02/14 06:37:46 sbarat ship $ */
3 /*======================================================================+
4 | DESCRIPTION |
5 | This package is used to migrate the existing task data |
6 | so that they can work with synchronization function. |
7 | |
8 | NOTES |
9 | |
10 | Date Developer Change |
11 | ------ --------------- -------------------------------------- |
12 | 26-Aug-2002 cjang Created. |
13 | 18-Oct-2004 mmarovic Added is_repeating_appt to improve |
14 | upgrade performance for customers that do not use appts. |
15 | 14-Feb-2006 sbarat Added hint 'parallel' for perf |
16 | bug# 4965969. Reviewed by Avanish |
17 | Srivatsav of Perf Team. |
18 +=======================================================================*/
19
20 -- Global variable that will be set to TRUE if there is any repeating
21 -- appointment to be upgraded - it will be set by is_repeating_appt
22 g_repeating_appt BOOLEAN := null;
23
24 -- Checks is there any repeating appointment to migrate
25 FUNCTION is_repeating_appt RETURN BOOLEAN
26 IS
27 cursor c_appt is
28 select 'x'
29 from jtf_tasks_b
30 where source_object_type_code = 'APPOINTMENT'
31 and rownum = 1;
32 l_is_appt CHAR(1);
33
34 BEGIN
35 if g_repeating_appt is null
36 then
37 open c_appt;
38 fetch c_appt into l_is_appt;
39 close c_appt;
40 if l_is_appt = 'x'
41 then
42 g_repeating_appt := true;
43 else
44 g_repeating_appt := false;
45 end if;
46 end if;
47
48 return g_repeating_appt;
49
50 END is_repeating_appt;
51
52 PROCEDURE update_invalid_repeating_appts
53 IS
54 -----------------------------------------------------------
55 -- For selecting duplicate repeating appointments
56 -- among unchanged records
57 -----------------------------------------------------------
58 CURSOR c_duplicates (b_recurrence_rule_id NUMBER
59 ,b_recur_creation_date DATE) IS
60 SELECT t.task_id
61 FROM jtf_tasks_b t
62 , (SELECT recurrence_rule_id
63 , calendar_start_date
64 , count(task_id) cnt
65 , min(task_id) min_task_id
66 FROM jtf_tasks_b
67 WHERE recurrence_rule_id = b_recurrence_rule_id
68 AND (last_update_date <= b_recur_creation_date OR
69 last_update_date = creation_date)
70 AND source_object_type_code = 'APPOINTMENT'
71 HAVING count(task_id) > 1
72 GROUP BY recurrence_rule_id, calendar_start_date
73 ) dup
74 WHERE t.recurrence_rule_id = dup.recurrence_rule_id
75 AND t.calendar_start_date = dup.calendar_start_date
76 AND t.task_id <> dup.min_task_id;
77
78 -----------------------------------------------------------
79 -- For selecting all repeating appointments not changed
80 -- among unchanged records
81 -----------------------------------------------------------
82 CURSOR c_repeat_appt (b_recurrence_rule_id NUMBER
83 ,b_recur_creation_date DATE) IS
84 SELECT task_id
85 , calendar_start_date
86 , created_by
87 FROM jtf_tasks_b
88 WHERE recurrence_rule_id = b_recurrence_rule_id
89 AND (last_update_date <= b_recur_creation_date OR
90 last_update_date = creation_date)
91 AND source_object_type_code = 'APPOINTMENT'
92 ORDER BY calendar_start_date, task_id;
93
94 ------------------------------------------------------------------
95 -- For selecting recurrence rules for all repeating appointments
96 ------------------------------------------------------------------
97 CURSOR c_recur IS
98 SELECT recurrence_rule_id
99 , occurs_which
100 , day_of_week
101 , date_of_month
102 , occurs_number
103 , occurs_month
104 , occurs_uom
105 , occurs_every
106 , start_date_active
107 , end_date_active
108 , sunday
109 , monday
110 , tuesday
111 , wednesday
112 , thursday
113 , friday
114 , saturday
115 , created_by
116 , creation_date
117 FROM jtf_task_recur_rules r
118 WHERE EXISTS (SELECT 1
119 FROM jtf_tasks_b jtb
120 WHERE jtb.recurrence_rule_id = r.recurrence_rule_id
121 AND jtb.source_object_type_code = 'APPOINTMENT'
122 AND (jtb.object_changed_date = to_date('01/02/1970','MM/DD/YYYY') OR
123 jtb.object_changed_date IS NULL)
124 );
125
126 l_output_dates_tbl jtf_task_recurrences_pvt.output_dates_rec;
127 l_output_dates_counter INTEGER;
128 l_invalid BOOLEAN := TRUE;
129 l_all_changed BOOLEAN := FALSE;
130 l_task_exclusion_id NUMBER;
131 l_time VARCHAR2(8) := '00:00:00';
132 l_exclusion_date DATE;
133
134 l_num NUMBER := 0;
135 l_commit_records NUMBER := 0;
136 l_commit_checkpoint NUMBER := 1000;
137 BEGIN
138 IF NOT is_repeating_appt() THEN
139 RETURN;
140 END IF;
141
142 FOR rec_recur IN c_recur
143 LOOP
144 -----------------------------------------------------------
145 -- Duplicate Dates:
146 -- Nullify recurrence_rule_id
147 -----------------------------------------------------------
148 FOR rec_duplicates IN c_duplicates(rec_recur.recurrence_rule_id
149 ,rec_recur.creation_date)
150 LOOP
151 l_num := l_num + 1;
152
153 -- Nullify recurrence_rule_id
154 UPDATE jtf_tasks_b
155 SET recurrence_rule_id = NULL
156 , last_updated_by = fnd_global.user_id
157 WHERE task_id = rec_duplicates.task_id;
158
159 COMMIT;
160
161 END LOOP;
162
163 -----------------------------------------------------------
164 -- Invalid and Extra Dates:
165 -- Nullify recurrence_rule_id
166 -- Insert into exclusion table
167 -----------------------------------------------------------
168 jtf_task_recurrences_pvt.generate_dates (
169 p_occurs_which => rec_recur.occurs_which,
170 p_day_of_week => rec_recur.day_of_week,
171 p_date_of_month => rec_recur.date_of_month,
172 p_occurs_month => rec_recur.occurs_month,
173 p_occurs_uom => rec_recur.occurs_uom,
174 p_occurs_every => rec_recur.occurs_every,
175 p_occurs_number => NULL,
176 p_start_date => rec_recur.start_date_active,
177 p_end_date => rec_recur.end_date_active,
178 x_output_dates_tbl => l_output_dates_tbl,
179 x_output_dates_counter => l_output_dates_counter,
180 p_sunday => rec_recur.sunday,
181 p_monday => rec_recur.monday,
182 p_tuesday => rec_recur.tuesday,
183 p_wednesday => rec_recur.wednesday,
184 p_thursday => rec_recur.thursday,
185 p_friday => rec_recur.friday,
186 p_saturday => rec_recur.saturday
187 );
188
189 IF l_output_dates_tbl.COUNT > 0
190 THEN
191 FOR rec_repeat_appt IN c_repeat_appt (rec_recur.recurrence_rule_id
192 ,rec_recur.creation_date)
193 LOOP
194 l_invalid := TRUE;
195
196 IF l_output_dates_tbl.COUNT > 0
197 THEN
198 FOR i IN l_output_dates_tbl.FIRST..l_output_dates_tbl.LAST
199 LOOP
200 IF l_output_dates_tbl.EXISTS(i)
201 THEN
202 IF TRUNC(l_output_dates_tbl(i)) = TRUNC(rec_repeat_appt.calendar_start_date)
203 THEN
204 l_output_dates_tbl.DELETE(i);
205 l_invalid := FALSE;
206 l_time := to_char(rec_repeat_appt.calendar_start_date,'HH24:MI:SS');
207 EXIT;
208 END IF;
209 END IF;
210 END LOOP;
211 END IF;
212
213 -----------------------------------------------
214 -- If not found the matched date
215 -----------------------------------------------
216 IF l_invalid
217 THEN
218 -- Nullify recurrence_rule_id
219 UPDATE jtf_tasks_b
220 SET recurrence_rule_id = NULL
221 , last_updated_by = fnd_global.user_id
222 WHERE task_id = rec_repeat_appt.task_id;
223
224 l_num := l_num + 1;
225
226 IF (l_num - l_commit_records) = l_commit_checkpoint
227 THEN
228 COMMIT;
229 l_commit_records := l_commit_records + l_commit_checkpoint;
230 END IF;
231 END IF;
232
233 END LOOP; -- c_repeat_appt
234
235 COMMIT;
236
237 END IF; -- l_output_dates_tbl.COUNT > 0
238
239 -----------------------------------------------------------
240 -- Missing Dates:
241 -- Insert into exclusion table
242 -----------------------------------------------------------
243 IF l_output_dates_tbl.COUNT > 0
244 THEN
245 FOR i IN l_output_dates_tbl.FIRST..l_output_dates_tbl.LAST
246 LOOP
247 IF l_output_dates_tbl.EXISTS(i)
248 THEN
249 SELECT jta_task_exclusions_s.NEXTVAL
250 INTO l_task_exclusion_id
251 FROM DUAL;
252
253 IF TRUNC(l_output_dates_tbl(i)) = l_output_dates_tbl(i) AND
254 l_time <> '00:00:00'
255 THEN
256 l_exclusion_date := TO_DATE(TO_CHAR(l_output_dates_tbl(i),'YYYY/MM/DD')||' '||l_time
257 ,'YYYY/MM/DD HH24:MI:SS');
258 ELSE
259 l_exclusion_date := l_output_dates_tbl(i);
260 END IF;
261
262 jta_task_exclusions_pkg.insert_row (
263 p_task_exclusion_id => l_task_exclusion_id,
264 p_task_id => 0-i,
265 p_recurrence_rule_id => rec_recur.recurrence_rule_id,
266 p_exclusion_date => l_exclusion_date,
267 p_created_by => rec_recur.created_by
268 );
269
270 l_num := l_num + 1;
271
272 COMMIT;
273 END IF; -- l_output_dates_tbl.EXISTS(i)
274 END LOOP; -- FOR i
275 END IF; -- l_output_dates_tbl.COUNT > 0
276
277 END LOOP; -- c_recur
278
279 COMMIT WORK;
280 EXCEPTION
281 WHEN OTHERS THEN
282 ROLLBACK;
283 raise_application_error(-20001, 'Unexpected error at jtftkugb.pls (update_invalid_repeating_appts) : '||SQLERRM(SQLCODE));
284 END update_invalid_repeating_appts;
285
286 PROCEDURE initialize_object_changed_date
287 IS
288 -- Added hint by SBARAT on 14/02/2006 for perf bug# 4965969
289 CURSOR c_tasks IS
290 SELECT /*+ parallel(b) */ b.rowid row_id
291 FROM jtf_tasks_b b
292 WHERE b.object_changed_date IS NULL;
293
294 l_num NUMBER := 0;
295 l_commit_records NUMBER := 0;
296 l_commit_checkpoint NUMBER := 1000;
297 BEGIN
298 IF NOT is_repeating_appt() THEN
299 RETURN;
300 END IF;
301
302 ----------------------------------------------------------------------------
303 -- Update object_changed_date with 02-Jan-1970
304 ----------------------------------------------------------------------------
305 FOR rec_tasks IN c_tasks
306 LOOP
307 UPDATE jtf_tasks_b
308 SET object_changed_date = TO_DATE('01/02/1970', 'MM/DD/YYYY')
309 , last_updated_by = fnd_global.user_id
310 WHERE rowid = rec_tasks.row_id;
311
312 l_num := l_num + 1;
313
314 IF (l_num - l_commit_records) = l_commit_checkpoint
315 THEN
316 COMMIT;
317 l_commit_records := l_commit_records + l_commit_checkpoint;
318 END IF;
319
320 END LOOP;
321
322 COMMIT WORK;
323 EXCEPTION
324 WHEN OTHERS THEN
325 ROLLBACK;
326 raise_application_error(-20001, 'Unexpected error at jtftkugb.pls (initialize_object_changed_date) : '||SQLCODE||' '||SQLERRM);
327 END initialize_object_changed_date;
328
329 PROCEDURE exclude_modified_repeat_appts
330 IS
331 -- Added hint by SBARAT on 14/02/2006 for perf bug# 4965969
332 CURSOR c_updated_appts IS
333 SELECT /*+ parallel(t) */ t.task_id
334 , t.recurrence_rule_id
335 , t.calendar_start_date
336 , t.created_by
337 , t.deleted_flag
338 , recur.creation_date recur_creation_date
339 FROM jtf_tasks_b t
340 , jtf_task_recur_rules recur
341 WHERE ((t.creation_date <> t.last_update_date AND
342 t.last_update_date > recur.creation_date
343 ) OR
344 deleted_flag = 'Y')
345 AND t.recurrence_rule_id = recur.recurrence_rule_id
346 AND t.source_object_type_code = 'APPOINTMENT'
347 AND (t.object_changed_date = to_date('01/02/1970','MM/DD/YYYY') OR
348 t.object_changed_date IS NULL)
349 ORDER BY t.calendar_start_date, t.task_id;
350
351 CURSOR c_duplicate_dates (b_recurrence_rule_id NUMBER
352 --,b_recur_creation_date DATE
353 ,b_calendar_start_date DATE
354 ,b_task_id NUMBER) IS
355 SELECT task_id
356 FROM jtf_tasks_b
357 WHERE recurrence_rule_id = b_recurrence_rule_id
358 AND calendar_start_date = b_calendar_start_date
359 AND task_id <> b_task_id;
360
361 CURSOR c_recur (b_recurrence_rule_id NUMBER) IS
362 SELECT recurrence_rule_id
363 , occurs_which
364 , day_of_week
365 , date_of_month
366 , occurs_number
367 , occurs_month
368 , occurs_uom
369 , occurs_every
370 , start_date_active
371 , end_date_active
372 , sunday
373 , monday
374 , tuesday
375 , wednesday
376 , thursday
377 , friday
378 , saturday
379 , created_by
380 FROM jtf_task_recur_rules
381 WHERE recurrence_rule_id = b_recurrence_rule_id;
382
383 rec_duplicate_dates c_duplicate_dates%ROWTYPE;
384 rec_recur c_recur%ROWTYPE;
385
386 notfound_recur_rule EXCEPTION;
390 l_output_dates_counter INTEGER;
387 l_recurrence_rule_id NUMBER;
388
389 l_output_dates_tbl jtf_task_recurrences_pvt.output_dates_rec;
391
392 l_updated BOOLEAN;
393 l_duplicate_found BOOLEAN;
394 l_start_date_or_extra_modified BOOLEAN;
395
396 l_task_exclusion_id NUMBER;
397
398 l_num NUMBER := 0;
399 l_commit_records NUMBER := 0;
400 l_commit_checkpoint NUMBER := 1000;
401 BEGIN
402 IF NOT is_repeating_appt() THEN
403 RETURN;
404 END IF;
405
406 ----------------------------------------------------------------------------
407 -- For all the modified occurrences,
408 -- - Nullify recurrence_rule_id
409 -- - Insert into exclusion table
410 -- For all the deleted occurrences
411 -- - Insert into exclusion table
412 ----------------------------------------------------------------------------
413 FOR rec_updated_appts IN c_updated_appts
414 LOOP
415 l_updated := FALSE;
416 l_duplicate_found := FALSE;
417 l_start_date_or_extra_modified := TRUE;
418
419 -------------------------------------------------------------
420 -- Check duplicate dates among unchanged records.
421 -- This is the duplication made by an end user.
422 -------------------------------------------------------------
423 OPEN c_duplicate_dates(rec_updated_appts.recurrence_rule_id
424 --,rec_updated_appts.recur_creation_date
425 ,rec_updated_appts.calendar_start_date
426 ,rec_updated_appts.task_id);
427 FETCH c_duplicate_dates INTO rec_duplicate_dates;
428 IF c_duplicate_dates%FOUND
429 THEN
430 l_duplicate_found := TRUE;
431 END IF;
432 CLOSE c_duplicate_dates;
433
434 -------------------------------------------------------------
435 -- Get recurrence rule
436 -------------------------------------------------------------
437 OPEN c_recur (rec_updated_appts.recurrence_rule_id);
438 FETCH c_recur INTO rec_recur;
439 IF c_recur%NOTFOUND
440 THEN
441 l_recurrence_rule_id := rec_updated_appts.recurrence_rule_id;
442 CLOSE c_recur;
443 raise notfound_recur_rule;
444 END IF;
445 CLOSE c_recur;
446
447 -------------------------------------------------------------
448 -- Generate repeating dates
449 -------------------------------------------------------------
450 jtf_task_recurrences_pvt.generate_dates (
451 p_occurs_which => rec_recur.occurs_which,
452 p_day_of_week => rec_recur.day_of_week,
453 p_date_of_month => rec_recur.date_of_month,
454 p_occurs_month => rec_recur.occurs_month,
455 p_occurs_uom => rec_recur.occurs_uom,
456 p_occurs_every => rec_recur.occurs_every,
457 p_occurs_number => NULL,
458 p_start_date => rec_recur.start_date_active,
459 p_end_date => rec_recur.end_date_active,
460 x_output_dates_tbl => l_output_dates_tbl,
461 x_output_dates_counter => l_output_dates_counter,
462 p_sunday => rec_recur.sunday,
463 p_monday => rec_recur.monday,
464 p_tuesday => rec_recur.tuesday,
465 p_wednesday => rec_recur.wednesday,
466 p_thursday => rec_recur.thursday,
467 p_friday => rec_recur.friday,
468 p_saturday => rec_recur.saturday
469 );
470
471 IF l_output_dates_tbl.COUNT > 0
472 THEN
473 FOR i IN l_output_dates_tbl.FIRST..l_output_dates_tbl.LAST
474 LOOP
475 IF TRUNC(rec_updated_appts.calendar_start_date) = TRUNC(l_output_dates_tbl(i))
476 THEN
477 l_start_date_or_extra_modified := FALSE;
478 EXIT;
479 END IF;
480 END LOOP;
481 END IF; -- l_output_dates_tbl.COUNT > 0
482
483 -----------------------------------------------------------------------
484 -- If rec_updated_appts.calendar_start_date has a unchanged duplicate one,
485 -- we don't make an exclusion for this.
486 -- We assume this duplication was caused because the start date has
487 -- been updated by an end user.
488 -- If this duplication has been made by a bug in recurrence API,
489 -- the records would have been nullified by the script "jtftkugb.sql"
490 -- The script "jtftkugb.sql" will encounter the missing dates
491 -- for the records whose the start date has been changed.
492 -- And the script will make exclusions for those missing dates.
493 -----------------------------------------------------------------------
494
495 -- The following "if" means
496 -- that the appointment start date has been changed and has not been duplicated
497 IF NOT (l_duplicate_found OR l_start_date_or_extra_modified)
498 THEN
499 SELECT jta_task_exclusions_s.NEXTVAL
500 INTO l_task_exclusion_id
504 p_task_exclusion_id => l_task_exclusion_id,
501 FROM DUAL;
502
503 jta_task_exclusions_pkg.insert_row (
505 p_task_id => rec_updated_appts.task_id,
506 p_recurrence_rule_id => rec_updated_appts.recurrence_rule_id,
507 p_exclusion_date => rec_updated_appts.calendar_start_date,
508 p_created_by => rec_updated_appts.created_by
509 );
510
511 l_updated := TRUE;
512 END IF;
513
514 -- If this has not been deleted, nullify recurrence rule
515 IF NVL(rec_updated_appts.deleted_flag,'N') = 'N'
516 THEN
517 UPDATE jtf_tasks_b
518 SET recurrence_rule_id = NULL
519 , last_updated_by = fnd_global.user_id
520 WHERE task_id = rec_updated_appts.task_id;
521
522 l_updated := TRUE;
523 END IF;
524
525 -- If this record is altered, check the commit check point
526 IF l_updated
527 THEN
528 l_num := l_num + 1;
529
530 IF (l_num - l_commit_records) = l_commit_checkpoint
531 THEN
532 COMMIT;
533 l_commit_records := l_commit_records + l_commit_checkpoint;
534 END IF;
535 END IF;
536
537 END LOOP;
538
539 COMMIT WORK;
540 EXCEPTION
541 WHEN notfound_recur_rule THEN
542 ROLLBACK;
543 raise_application_error(-20001, 'Unexpected error at jtftkugb.pls (exclude_modified_repeat_appts) : recurrence_rule_id '||l_recurrence_rule_id||'is not found in the table JTF_TASK_RECUR_RULES.');
544
545 WHEN OTHERS THEN
546 ROLLBACK;
547 raise_application_error(-20001, 'Unexpected error at jtftkugb.pls (exclude_modified_repeat_appts) : '||SQLCODE||' '||SQLERRM);
548 END exclude_modified_repeat_appts;
549
550 PROCEDURE correct_recurrence_rule
551 IS
552 CURSOR c_recur IS
553 SELECT *
554 FROM jtf_task_recur_rules r
555 WHERE (end_date_active IS NULL OR occurs_number IS NULL)
556 AND EXISTS (SELECT 1
557 FROM jtf_tasks_b t
558 WHERE t.source_object_type_code = 'APPOINTMENT'
559 AND t.recurrence_rule_id = r.recurrence_rule_id);
560
561 l_output_dates_tbl jtf_task_recurrences_pvt.output_dates_rec;
562 l_output_dates_counter INTEGER;
563 l_max_date DATE;
564 l_occurs_number NUMBER;
565
566 l_num NUMBER := 0;
567 l_commit_records NUMBER := 0;
568 l_commit_checkpoint NUMBER := 1000;
569 BEGIN
570 IF NOT is_repeating_appt() THEN
571 RETURN;
572 END IF;
573
574 ----------------------------------------------------------------------------
575 -- Update end_date_active in jtf_task_recur_rules with the last one of dates
576 -- which is generated by jtf_task_recurrences_pvt.generate_dates().
577 ----------------------------------------------------------------------------
578 FOR rec_recur IN c_recur
579 LOOP
580 jtf_task_recurrences_pvt.generate_dates (
581 p_occurs_which => rec_recur.occurs_which,
582 p_day_of_week => rec_recur.day_of_week,
583 p_date_of_month => rec_recur.date_of_month,
584 p_occurs_month => rec_recur.occurs_month,
585 p_occurs_uom => rec_recur.occurs_uom,
586 p_occurs_every => rec_recur.occurs_every,
587 p_occurs_number => rec_recur.occurs_number,
588 p_start_date => rec_recur.start_date_active,
589 p_end_date => rec_recur.end_date_active,
590 x_output_dates_tbl => l_output_dates_tbl,
591 x_output_dates_counter => l_output_dates_counter,
592 p_sunday => rec_recur.sunday,
593 p_monday => rec_recur.monday,
594 p_tuesday => rec_recur.tuesday,
595 p_wednesday => rec_recur.wednesday,
596 p_thursday => rec_recur.thursday,
597 p_friday => rec_recur.friday,
598 p_saturday => rec_recur.saturday
599 );
600
601 l_occurs_number := l_output_dates_tbl.COUNT;
602
603 IF l_output_dates_tbl.COUNT > 0
604 THEN
605 -------------------------------------------------------
606 -- Find the repeating end date from l_output_dates_tbl
607 -------------------------------------------------------
608 l_max_date := l_output_dates_tbl(l_output_dates_tbl.FIRST);
609 FOR i IN l_output_dates_tbl.FIRST..l_output_dates_tbl.LAST
610 LOOP
611 IF l_max_date < l_output_dates_tbl(i)
612 THEN
613 l_max_date := l_output_dates_tbl(i);
614 END IF;
615 END LOOP;
616
617 UPDATE jtf_task_recur_rules
618 SET occurs_number = l_occurs_number
619 , end_date_active = l_max_date
620 , last_updated_by = fnd_global.user_id
621 WHERE recurrence_rule_id = rec_recur.recurrence_rule_id;
622
623 l_num := l_num + 1;
624
628 l_commit_records := l_commit_records + l_commit_checkpoint;
625 IF (l_num - l_commit_records) = l_commit_checkpoint
626 THEN
627 COMMIT;
629 END IF;
630 END IF;
631 END LOOP;
632
633 COMMIT WORK;
634 EXCEPTION
635 WHEN OTHERS THEN
636 ROLLBACK;
637 raise_application_error(-20001, 'Unexpected error at jtftkugb.pls (correct_recurrence_rule) : '||SQLCODE||' '||SQLERRM);
638 END correct_recurrence_rule;
639
640 PROCEDURE reset_assignment_status
641 IS
642 -- Added hint by SBARAT on 14/02/2006 for perf bug# 4965969
643 CURSOR c_appt_with_mixed_status IS
644 SELECT DISTINCT
645 t.task_id
646 , a.task_assignment_id
647 FROM jtf_task_all_assignments a
648 , jtf_tasks_b t
649 , (SELECT /*+ parallel(jtb) */ jtb.recurrence_rule_id
650 , jtaa.resource_id
651 , SUM(decode(jtaa.assignment_status_id, 3, 1, 0)) num_of_accept
652 , SUM(decode(jtaa.assignment_status_id, 4, 1, 0)) num_of_reject
653 , SUM(decode(jtaa.assignment_status_id, 18, 1, 0)) num_of_invitee
654 FROM jtf_task_all_assignments jtaa
655 , jtf_tasks_b jtb
656 WHERE jtb.recurrence_rule_id IS NOT NULL
657 AND jtb.source_object_type_code = 'APPOINTMENT'
658 AND jtaa.task_id = jtb.task_id
659 AND jtaa.assignee_role = 'ASSIGNEE'
660 AND jtaa.assignment_status_id IN (18, 3, 4)
661 HAVING NOT
662 ((SUM(decode(jtaa.assignment_status_id, 3, 1, 0)) > 0 AND SUM(decode(jtaa.assignment_status_id, 4, 1, 0)) = 0 AND SUM(decode(jtaa.assignment_status_id, 18, 1, 0)) = 0) OR
666 ORDER BY jtb.recurrence_rule_id, jtaa.resource_id
663 (SUM(decode(jtaa.assignment_status_id, 3, 1, 0)) = 0 AND SUM(decode(jtaa.assignment_status_id, 4, 1, 0)) > 0 AND SUM(decode(jtaa.assignment_status_id, 18, 1, 0)) = 0) OR
664 (SUM(decode(jtaa.assignment_status_id, 3, 1, 0)) = 0 AND SUM(decode(jtaa.assignment_status_id, 4, 1, 0)) = 0 AND SUM(decode(jtaa.assignment_status_id, 18, 1, 0)) > 0))
665 GROUP BY jtb.recurrence_rule_id, jtaa.resource_id
667 ) m
668 WHERE t.recurrence_rule_id = m.recurrence_rule_id
669 AND a.task_id = t.task_id
670 AND a.resource_id = m.resource_id;
671
672 l_num NUMBER := 0;
673 l_commit_records NUMBER := 0;
674 l_commit_checkpoint NUMBER := 1000;
675 BEGIN
676 IF NOT is_repeating_appt() THEN
677 RETURN;
678 END IF;
679
680 ----------------------------------------------------------------------------
681 -- Find all appts having accept, reject and invitee together
682 ----------------------------------------------------------------------------
683 l_num := 0;
684 FOR rec_appt_with_mixed_status IN c_appt_with_mixed_status
685 LOOP
686 l_num := l_num + 1;
687
688 UPDATE jtf_task_all_assignments
689 SET assignment_status_id = 18
690 , last_updated_by = fnd_global.user_id
691 WHERE task_assignment_id = rec_appt_with_mixed_status.task_assignment_id;
692
693 IF (l_num - l_commit_records) = l_commit_checkpoint
694 THEN
695 COMMIT;
696 l_commit_records := l_commit_records + l_commit_checkpoint;
697 END IF;
698
699 END LOOP;
700
701 COMMIT WORK;
702 EXCEPTION
703 WHEN OTHERS THEN
704 ROLLBACK;
705 raise_application_error(-20001, 'Unexpected error at jtftkugb.pls (reset_assignment_status) : '||SQLCODE||' '||SQLERRM);
706 END reset_assignment_status;
707
708 PROCEDURE nullify_wrong_assignments
709 IS
710 CURSOR c_recur IS
711 SELECT *
712 FROM jtf_task_recur_rules r
713 WHERE EXISTS (SELECT 1
714 FROM jtf_tasks_b t
715 WHERE t.source_object_type_code = 'APPOINTMENT'
716 AND t.recurrence_rule_id = r.recurrence_rule_id);
717
718 CURSOR c_assignees (b_recurrence_rule_id NUMBER
719 ,b_valid_count NUMBER) IS
720 SELECT a.resource_id
721 , a.resource_type_code
722 , count(a.task_assignment_id)
723 FROM jtf_task_all_assignments a
724 , jtf_tasks_b t
725 WHERE t.recurrence_rule_id = b_recurrence_rule_id
726 AND t.source_object_type_code = 'APPOINTMENT'
727 AND (t.object_changed_date = to_date('01/02/1970','MM/DD/YYYY') OR
728 t.object_changed_date IS NULL)
729 AND a.task_id = t.task_id
730 AND a.assignee_role = 'ASSIGNEE'
731 HAVING count(a.task_assignment_id) < b_valid_count
732 GROUP BY a.resource_id, a.resource_type_code
733 ORDER BY a.resource_id, a.resource_type_code;
734
735 rec_assignees c_assignees%ROWTYPE;
736
737 l_output_dates_tbl jtf_task_recurrences_pvt.output_dates_rec;
738 l_output_dates_counter INTEGER;
739 l_max_date DATE;
740
741 l_num NUMBER := 0;
742 l_commit_records NUMBER := 0;
743 l_commit_checkpoint NUMBER := 1000;
744 BEGIN
745 IF NOT is_repeating_appt() THEN
746 RETURN;
747 END IF;
748
749 ----------------------------------------------------------------------------
750 -- Find all appts having accept, reject and invitee together
751 ----------------------------------------------------------------------------
752 FOR rec_recur IN c_recur
753 LOOP
754 jtf_task_recurrences_pvt.generate_dates (
755 p_occurs_which => rec_recur.occurs_which,
756 p_day_of_week => rec_recur.day_of_week,
757 p_date_of_month => rec_recur.date_of_month,
758 p_occurs_month => rec_recur.occurs_month,
759 p_occurs_uom => rec_recur.occurs_uom,
760 p_occurs_every => rec_recur.occurs_every,
761 p_occurs_number => NULL,
762 p_start_date => rec_recur.start_date_active,
763 p_end_date => rec_recur.end_date_active,
764 x_output_dates_tbl => l_output_dates_tbl,
765 x_output_dates_counter => l_output_dates_counter,
766 p_sunday => rec_recur.sunday,
767 p_monday => rec_recur.monday,
768 p_tuesday => rec_recur.tuesday,
769 p_wednesday => rec_recur.wednesday,
770 p_thursday => rec_recur.thursday,
771 p_friday => rec_recur.friday,
772 p_saturday => rec_recur.saturday
773 );
774
775 OPEN c_assignees (rec_recur.recurrence_rule_id, l_output_dates_tbl.COUNT);
776 FETCH c_assignees INTO rec_assignees;
777
778 IF c_assignees%FOUND
779 THEN
780 l_num := l_num + 1;
781
782 UPDATE jtf_tasks_b
783 SET recurrence_rule_id = NULL
784 , last_updated_by = fnd_global.user_id
785 WHERE recurrence_rule_id = rec_recur.recurrence_rule_id
786 AND NVL(deleted_flag,'N') <> 'Y';
787
788 IF (l_num - l_commit_records) = l_commit_checkpoint
789 THEN
790 COMMIT;
791 l_commit_records := l_commit_records + l_commit_checkpoint;
792 END IF;
793 END IF;
794
795 CLOSE c_assignees;
796
800 EXCEPTION
797 END LOOP;
798
799 COMMIT WORK;
801 WHEN OTHERS THEN
802 ROLLBACK;
803 raise_application_error(-20001, 'Unexpected error at jtftkugb.pls (nullify_wrong_assignments) : '||SQLCODE||' '||SQLERRM);
804 END nullify_wrong_assignments;
805
806 END JTF_TASK_SYNC_UPGRADE_PKG;