[Home] [Help]
PACKAGE BODY: APPS.JTF_TASK_RECURRENCES_PVT
Source
1 PACKAGE BODY jtf_task_recurrences_pvt AS
2 /* $Header: jtfvtkub.pls 120.8.12010000.2 2008/10/23 09:54:37 anangupt ship $ */
3
4 PROCEDURE get_mth_day(
5 p_occurs_which VARCHAR2,
6 p_sunday VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
7 p_monday VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
8 p_tuesday VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
9 p_wednesday VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
10 p_thursday VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
11 p_friday VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
12 p_saturday VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
13 p_month VARCHAR2,
14 p_year NUMBER,
15 x_required_date OUT NOCOPY DATE
16 )
17 AS
18 TYPE get_days IS TABLE OF DATE
19 INDEX BY BINARY_INTEGER;
20 TYPE get_weekdays IS TABLE OF VARCHAR2(10)
21 INDEX BY BINARY_INTEGER;
22 daywk get_weekdays;
23 daysx get_days;
24 l_count INTEGER := 0;
25 start_date DATE;
26 i INTEGER := 1;
27 j INTEGER := 1;
28 output_date DATE;
29 BEGIN
30 start_date := TO_DATE ('01-' || p_month || '-' || p_year, 'dd-mm-rrrr','NLS_DATE_LANGUAGE=AMERICAN');
31
32 WHILE (i < 8)
33 AND (TO_CHAR (start_date, 'MON','NLS_DATE_LANGUAGE=AMERICAN') = p_month)
34 LOOP
35 IF p_sunday = 'Y'
36 THEN
37 IF RTRIM (LTRIM (TO_CHAR (start_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'SUNDAY'
38 THEN
39 daysx (j) := start_date;
40 daywk (j) := 'SUNDAY';
41 j := j + 1;
42 END IF;
43 END IF;
44
45 IF p_monday = 'Y'
46 THEN
47 IF RTRIM (LTRIM (TO_CHAR (start_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) ='MONDAY'
48 THEN
49 daysx (j) := start_date;
50 daywk (j) := 'MONDAY';
51 j := j + 1;
52 END IF;
53 END IF;
54
55 IF p_tuesday = 'Y'
56 THEN
57 IF RTRIM (LTRIM (TO_CHAR (start_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) ='TUESDAY'
58 THEN
59 daysx (j) := start_date;
60 daywk (j) := 'TUESDAY';
61 j := j + 1;
62 END IF;
63 END IF;
64
65 IF p_wednesday = 'Y'
66 THEN
67 IF RTRIM (LTRIM (TO_CHAR (start_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'WEDNESDAY'
68 THEN
69 daysx (j) := start_date;
70 daywk (j) := 'WEDNESDAY';
71 j := j + 1;
72 END IF;
73 END IF;
74
75 IF p_thursday = 'Y'
76 THEN
77 IF RTRIM (LTRIM (TO_CHAR (start_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'THURSDAY'
78 THEN
79 daysx (j) := start_date;
80 daywk (j) := 'THURSDAY';
81 j := j + 1;
82 END IF;
83 END IF;
84
85 IF p_friday = 'Y'
86 THEN
87 IF RTRIM (LTRIM (TO_CHAR (start_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'FRIDAY'
88 THEN
89 daysx (j) := start_date;
90 daywk (j) := 'FRIDAY';
91 j := j + 1;
92 END IF;
93 END IF;
94
95 IF p_saturday = 'Y'
96 THEN
97 IF RTRIM (LTRIM (TO_CHAR (start_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'SATURDAY'
98 THEN
99 daysx (j) := start_date;
100 daywk (j) := 'SATURDAY';
101 j := j + 1;
102 END IF;
103 END IF;
104
105 i := i + 1;
106 start_date := start_date + 1;
107 END LOOP;
108
109 j := 2;
110 IF daywk(1) = 'SUNDAY' THEN
111 WHILE (i < 32)
112 AND (TO_CHAR (start_date, 'MON','NLS_DATE_LANGUAGE=AMERICAN') = p_month)
113 LOOP
114 IF RTRIM (LTRIM (TO_CHAR (start_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'SUNDAY'
115 THEN
116 daysx (j) := start_date;
117 j := j + 1;
118 END IF;
119 i := i + 1;
120 start_date := start_date + 1;
121 END LOOP;
122 END IF;
123
124 IF daywk(1) = 'MONDAY' THEN
125 WHILE (i < 32)
126 AND (TO_CHAR (start_date, 'MON','NLS_DATE_LANGUAGE=AMERICAN') = p_month)
127 LOOP
128 IF RTRIM (LTRIM (TO_CHAR (start_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'MONDAY'
129 THEN
130 daysx (j) := start_date;
131 j := j + 1;
132 END IF;
133 i := i + 1;
134 start_date := start_date + 1;
135 END LOOP;
136 END IF;
137
138 IF daywk(1) = 'TUESDAY' THEN
139 WHILE (i < 32)
140 AND (TO_CHAR (start_date, 'MON','NLS_DATE_LANGUAGE=AMERICAN') = p_month)
141 LOOP
142 IF RTRIM (LTRIM (TO_CHAR (start_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'TUESDAY'
143 THEN
144 daysx (j) := start_date;
145 j := j + 1;
146 END IF;
147 i := i + 1;
148 start_date := start_date + 1;
149 END LOOP;
150 END IF;
151
152 IF daywk(1) = 'WEDNESDAY' THEN
153 WHILE (i < 32)
154 AND (TO_CHAR (start_date, 'MON','NLS_DATE_LANGUAGE=AMERICAN') = p_month)
155 LOOP
156 IF RTRIM (LTRIM (TO_CHAR (start_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'WEDNESDAY'
157 THEN
158 daysx (j) := start_date;
159 j := j + 1;
160 END IF;
161 i := i + 1;
162 start_date := start_date + 1;
163 END LOOP;
164 END IF;
165
166 IF daywk(1) = 'THURSDAY' THEN
167 WHILE (i < 32)
168 AND (TO_CHAR (start_date, 'MON','NLS_DATE_LANGUAGE=AMERICAN') = p_month)
169 LOOP
170 IF RTRIM (LTRIM (TO_CHAR (start_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'THURSDAY'
171 THEN
172 daysx (j) := start_date;
173 j := j + 1;
174 END IF;
175 i := i + 1;
176 start_date := start_date + 1;
177 END LOOP;
178 END IF;
179
180 IF daywk(1) = 'FRIDAY' THEN
181 WHILE (i < 32)
182 AND (TO_CHAR (start_date, 'MON','NLS_DATE_LANGUAGE=AMERICAN') = p_month)
183 LOOP
184 IF RTRIM (LTRIM (TO_CHAR (start_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'FRIDAY'
185 THEN
186 daysx (j) := start_date;
187 j := j + 1;
188 END IF;
189 i := i + 1;
190 start_date := start_date + 1;
191 END LOOP;
192 END IF;
193
194 IF daywk(1) = 'SATURDAY' THEN
195 WHILE (i < 32)
196 AND (TO_CHAR (start_date, 'MON','NLS_DATE_LANGUAGE=AMERICAN') = p_month)
197 LOOP
198 IF RTRIM (LTRIM (TO_CHAR (start_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'SATURDAY'
199 THEN
200 daysx (j) := start_date;
201 j := j + 1;
202 END IF;
203 i := i + 1;
204 start_date := start_date + 1;
205 END LOOP;
206 END IF;
207
208 IF p_occurs_which = 'FIRST'
209 THEN
210 output_date := daysx (1);
211 ELSIF p_occurs_which = 'SECOND'
212 THEN
213 output_date := daysx (2);
214 ELSIF p_occurs_which = 'THIRD'
215 THEN
216 output_date := daysx (3);
217 ELSIF p_occurs_which = 'FOUR'
218 THEN
219 output_date := daysx (4);
220 ELSE
221 output_date := daysx (j - 1);
222 --when user selects last to each month, check to see if the following selected date
223 --falls to next month, if it's true, go back to select all qulified date prior to the output_date
224 IF RTRIM (LTRIM (TO_CHAR (output_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'SUNDAY'
225 THEN
226 IF p_monday = 'Y' AND (TO_CHAR ((output_date + 1), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
227 THEN
228 output_date := output_date - 6;
229 ELSIF p_tuesday = 'Y' AND (TO_CHAR ((output_date + 2), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
230 THEN
231 output_date := output_date - 5;
232 ELSIF p_wednesday = 'Y' AND (TO_CHAR ((output_date + 3), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
233 THEN
234 output_date := output_date - 4;
235 ELSIF p_thursday = 'Y' AND (TO_CHAR ((output_date + 4), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
236 THEN
237 output_date := output_date - 3;
238 ELSIF p_friday = 'Y' AND (TO_CHAR ((output_date + 5), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
239 THEN
240 output_date := output_date - 2;
241 ELSIF p_saturday = 'Y' AND (TO_CHAR ((output_date + 6), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
242 THEN
243 output_date := output_date - 1;
244 END IF;
245 END IF;
246
247 IF RTRIM (LTRIM (TO_CHAR (output_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'SATURDAY'
248 THEN
249 IF p_sunday = 'Y' AND (TO_CHAR ((output_date + 1), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
250 THEN
251 output_date := output_date - 6;
252 ELSIF p_monday = 'Y' AND (TO_CHAR ((output_date + 2), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
253 THEN
254 output_date := output_date - 5;
255 ELSIF p_tuesday = 'Y' AND (TO_CHAR ((output_date + 3), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
256 THEN
257 output_date := output_date - 4;
258 ELSIF p_wednesday = 'Y' AND (TO_CHAR ((output_date + 4), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
259 THEN
260 output_date := output_date - 3;
261 ELSIF p_thursday = 'Y' AND (TO_CHAR ((output_date + 5), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
262 THEN
263 output_date := output_date - 2;
264 ELSIF p_friday = 'Y' AND (TO_CHAR ((output_date + 6), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
265 THEN
266 output_date := output_date - 1;
267 END IF;
268 END IF;
269
270 IF RTRIM (LTRIM (TO_CHAR (output_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'FRIDAY'
271 THEN
272 IF p_saturday = 'Y' AND (TO_CHAR ((output_date + 1), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
273 THEN
274 output_date := output_date - 6;
275 ELSIF p_sunday = 'Y' AND (TO_CHAR ((output_date + 2), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
276 THEN
277 output_date := output_date - 5;
278 ELSIF p_monday = 'Y' AND (TO_CHAR ((output_date + 3), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
279 THEN
280 output_date := output_date - 4;
281 ELSIF p_tuesday = 'Y' AND (TO_CHAR ((output_date + 4), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
282 THEN
283 output_date := output_date - 3;
284 ELSIF p_wednesday = 'Y' AND (TO_CHAR ((output_date + 5), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
285 THEN
286 output_date := output_date - 2;
287 ELSIF p_thursday = 'Y' AND (TO_CHAR ((output_date + 6), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
288 THEN
289 output_date := output_date - 1;
290 END IF;
291 END IF;
292
293 IF RTRIM (LTRIM (TO_CHAR (output_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'THURSDAY'
294 THEN
295 IF p_friday = 'Y' AND (TO_CHAR ((output_date + 1), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
296 THEN
297 output_date := output_date - 6;
298 ELSIF p_saturday = 'Y' AND (TO_CHAR ((output_date + 2), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
299 THEN
300 output_date := output_date - 5;
301 ELSIF p_sunday = 'Y' AND (TO_CHAR ((output_date + 3), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
302 THEN
303 output_date := output_date - 4;
304 ELSIF p_monday = 'Y' AND (TO_CHAR ((output_date + 4), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
305 THEN
306 output_date := output_date - 3;
307 ELSIF p_tuesday = 'Y' AND (TO_CHAR ((output_date + 5), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
308 THEN
309 output_date := output_date - 2;
310 ELSIF p_wednesday = 'Y' AND (TO_CHAR ((output_date + 6), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
311 THEN
312 output_date := output_date - 1;
313 END IF;
314 END IF;
315
316 IF RTRIM (LTRIM (TO_CHAR (output_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'WEDNESDAY'
317 THEN
318 IF p_thursday = 'Y' AND (TO_CHAR ((output_date + 1), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
319 THEN
320 output_date := output_date - 6;
321 ELSIF p_friday = 'Y' AND (TO_CHAR ((output_date + 2), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
322 THEN
323 output_date := output_date - 5;
324 ELSIF p_saturday = 'Y' AND (TO_CHAR ((output_date + 3), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
325 THEN
326 output_date := output_date - 4;
327 ELSIF p_sunday = 'Y' AND (TO_CHAR ((output_date + 4), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
328 THEN
329 output_date := output_date - 3;
330 ELSIF p_monday = 'Y' AND (TO_CHAR ((output_date + 5), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
331 THEN
332 output_date := output_date - 2;
333 ELSIF p_tuesday = 'Y' AND (TO_CHAR ((output_date + 6), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
334 THEN
335 output_date := output_date - 1;
336 END IF;
337 END IF;
338
339 IF RTRIM (LTRIM (TO_CHAR (output_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'TUESDAY'
340 THEN
341 IF p_wednesday = 'Y' AND (TO_CHAR ((output_date + 1), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
342 THEN
343 output_date := output_date - 6;
344 ELSIF p_thursday = 'Y' AND (TO_CHAR ((output_date + 2), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
345 THEN
346 output_date := output_date - 5;
347 ELSIF p_friday = 'Y' AND (TO_CHAR ((output_date + 3), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
348 THEN
349 output_date := output_date - 4;
350 ELSIF p_saturday = 'Y' AND (TO_CHAR ((output_date + 4), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
351 THEN
352 output_date := output_date - 3;
353 ELSIF p_sunday = 'Y' AND (TO_CHAR ((output_date + 5), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
354 THEN
355 output_date := output_date - 2;
356 ELSIF p_monday = 'Y' AND (TO_CHAR ((output_date + 6), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
357 THEN
358 output_date := output_date - 1;
359 END IF;
360 END IF;
361
362 IF RTRIM (LTRIM (TO_CHAR (output_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'MONDAY'
363 THEN
364 IF p_tuesday = 'Y' AND (TO_CHAR ((output_date + 1), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
365 THEN
366 output_date := output_date - 6;
367 ELSIF p_wednesday = 'Y' AND (TO_CHAR ((output_date + 2), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
368 THEN
369 output_date := output_date - 5;
370 ELSIF p_thursday = 'Y' AND (TO_CHAR ((output_date + 3), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
371 THEN
372 output_date := output_date - 4;
373 ELSIF p_friday = 'Y' AND (TO_CHAR ((output_date + 4), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
374 THEN
375 output_date := output_date - 3;
376 ELSIF p_saturday = 'Y' AND (TO_CHAR ((output_date + 5), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
377 THEN
378 output_date := output_date - 2;
379 ELSIF p_sunday = 'Y' AND (TO_CHAR ((output_date + 6), 'MON','NLS_DATE_LANGUAGE=AMERICAN') <> p_month)
380 THEN
381 output_date := output_date - 1;
382 END IF;
383 END IF;
384 END IF;
385
386 x_required_date := output_date;
387
388 END;
389
390 PROCEDURE get_week_day(
391 p_sunday VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
392 p_monday VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
393 p_tuesday VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
394 p_wednesday VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
395 p_thursday VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
396 p_friday VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
397 p_saturday VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
398 p_required_date DATE,
399 x_required_date IN OUT NOCOPY DATE
400 )
401 AS
402
403 start_date DATE;
404 i INTEGER := 1;
405 BEGIN
406 start_date := p_required_date;
407 WHILE (i < 8)
408 LOOP
409 IF p_sunday = 'Y'
410 THEN
411 IF RTRIM (LTRIM (TO_CHAR (start_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'SUNDAY'
412 THEN
413 x_required_date := start_date;
414 EXIT;
415 END IF;
416 END IF;
417
418 IF p_monday = 'Y'
419 THEN
420 IF RTRIM (LTRIM (TO_CHAR (start_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) ='MONDAY'
421 THEN
422 x_required_date := start_date;
423 EXIT;
424 END IF;
425 END IF;
426
427 IF p_tuesday = 'Y'
428 THEN
429 IF RTRIM (LTRIM (TO_CHAR (start_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) ='TUESDAY'
430 THEN
431 x_required_date := start_date;
432 EXIT;
433 END IF;
434 END IF;
435
436 IF p_wednesday = 'Y'
437 THEN
438 IF RTRIM (LTRIM (TO_CHAR (start_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'WEDNESDAY'
439 THEN
440 x_required_date := start_date;
441 EXIT;
442 END IF;
443 END IF;
444
445 IF p_thursday = 'Y'
446 THEN
447 IF RTRIM (LTRIM (TO_CHAR (start_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'THURSDAY'
448 THEN
449 x_required_date := start_date;
450 EXIT;
451 END IF;
452 END IF;
453
454 IF p_friday = 'Y'
455 THEN
456 IF RTRIM (LTRIM (TO_CHAR (start_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'FRIDAY'
457 THEN
458 x_required_date := start_date;
459 EXIT;
460 END IF;
461 END IF;
462
463 IF p_saturday = 'Y'
464 THEN
465 IF RTRIM (LTRIM (TO_CHAR (start_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'SATURDAY'
466 THEN
467 x_required_date := start_date;
468 EXIT;
469 END IF;
470 END IF;
471
472 i := i + 1;
473 start_date := start_date + 1;
474 END LOOP;
475
476 END;
477
478 PROCEDURE get_occurs_which (
479 p_occurs_which VARCHAR2,
480 p_day_of_week VARCHAR2,
481 p_month VARCHAR2,
482 p_year NUMBER,
483 x_required_date OUT NOCOPY DATE
484 )
485 AS
486 TYPE get_days IS TABLE OF DATE
487 INDEX BY BINARY_INTEGER;
488
489 daysx get_days;
490 start_date DATE;
491 i INTEGER := 1;
492 j INTEGER := 1;
493 output_date DATE;
494 BEGIN
495 start_date := TO_DATE ('01-' || p_month || '-' || p_year, 'dd-mm-rrrr','NLS_DATE_LANGUAGE=AMERICAN');
496
497 WHILE (i < 32)
498 AND (TO_CHAR (start_date, 'MON','NLS_DATE_LANGUAGE=AMERICAN') = p_month)
499 LOOP
500 IF p_day_of_week IN ('SUNDAY', 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY')
501 THEN
502 IF RTRIM (LTRIM (TO_CHAR (start_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = LTRIM (TRIM (p_day_of_week))
503 THEN
504 daysx (j) := start_date;
505 j := j + 1;
506 END IF;
507 END IF;
508
509 IF p_day_of_week IN ('WEEKEND')
510 THEN
511 IF RTRIM (LTRIM (TO_CHAR (start_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) IN ('SATURDAY', 'SUNDAY')
512 THEN
513 daysx (j) := start_date;
514 j := j + 1;
515 END IF;
516 END IF;
517
518 IF p_day_of_week IN ('WEEKDAY')
519 THEN
520 IF RTRIM (LTRIM (TO_CHAR (start_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) IN ('MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY')
521 THEN
522 daysx (j) := start_date;
523 j := j + 1;
524 END IF;
525 END IF;
526
527 IF p_day_of_week = 'DAY'
528 THEN
529 IF RTRIM (LTRIM (TO_CHAR (start_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) IN ('SUNDAY', 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY')
530 THEN
531 daysx (j) := start_date;
532 j := j + 1;
533 END IF;
534 END IF;
535
536 i := i + 1;
537 start_date := start_date + 1;
538 END LOOP;
539
540 IF p_occurs_which = 'FIRST'
541 THEN
542 output_date := daysx (1);
543 ELSIF p_occurs_which = 'SECOND'
544 THEN
545 output_date := daysx (2);
546 ELSIF p_occurs_which = 'THIRD'
547 THEN
548 output_date := daysx (3);
549 ELSIF p_occurs_which = 'FOUR'
550 THEN
551 output_date := daysx (4);
552 ELSE
553 output_date := daysx (j - 1);
554 END IF;
555
556 x_required_date := output_date;
557 END;
558
559 -------------------------------------------------------
560 -------------------------------------------------------
561 PROCEDURE occurs_date_of_month (
562 p_date_of_month NUMBER,
563 p_month VARCHAR2,
564 p_year NUMBER,
565 x_required_date OUT NOCOPY DATE
566 )
567 IS
568 BEGIN
569 -- Added NLS parameter to bug# 7491191
570 x_required_date := TO_DATE (TO_CHAR (p_date_of_month) || '-' || p_month || '-' || TO_CHAR (p_year), 'dd-mm-rrrr','NLS_DATE_LANGUAGE=AMERICAN');
571 END;
572
573
574
575 --This function corrects DST offset for p_to_date
576 --using p_from_date.
577
578 FUNCTION get_dst_corrected_date (
579 p_to_date IN DATE
580 , p_from_date IN DATE
581 ) RETURN DATE IS
582
583 l_server_tz_id NUMBER := TO_NUMBER(fnd_profile.value_specific('SERVER_TIMEZONE_ID'));
584 l_from_date DATE := p_from_date;
585 l_to_date DATE := p_to_date;
586 l_global_timezone_name VARCHAR2(50);
587 l_name VARCHAR2(80);
588 l_status VARCHAR2(1);
589 l_msg_count NUMBER;
590 l_msg_data VARCHAR2(2000);
591 l_GMT_deviation NUMBER;
592 l_server_offset NUMBER;
593 l_to_date_in_dst BOOLEAN := false;
594 l_from_date_in_dst BOOLEAN := false;
595
596 BEGIN
597 --Get server timezone's offset from GMT
598 SELECT gmt_deviation_hours
599 INTO l_server_offset
600 FROM hz_timezones
601 WHERE timezone_id = l_server_tz_id;
602
603 --Get the GMT deviation of p_to_date
604 hz_timezone_pub.get_timezone_gmt_deviation(
605 p_api_version => 1.0
606 , p_init_msg_list => 'F'
607 , p_timezone_id => l_server_tz_id
608 , p_date => l_to_date
609 , x_gmt_deviation => l_gmt_deviation
610 , x_global_timezone_name => l_global_timezone_name
611 , x_name => l_name
612 , x_return_status => l_status
613 , x_msg_count => l_msg_count
614 , x_msg_data => l_msg_data
615 );
616
617 IF ( l_status <> FND_API.G_RET_STS_SUCCESS )
618 THEN
619 RAISE fnd_api.G_EXC_ERROR;
620 END IF;
621
622 IF ( l_gmt_deviation <> l_server_offset)
623 THEN
624 --p_to_date is in DST
625 l_to_date_in_dst := true;
626 END IF;
627
628 --Get the GMT deviation of p_from_date
629 hz_timezone_pub.get_timezone_gmt_deviation(
630 p_api_version => 1.0
631 , p_init_msg_list => 'F'
632 , p_timezone_id => l_server_tz_id
633 , p_date => l_from_date
634 , x_gmt_deviation => l_gmt_deviation
635 , x_global_timezone_name => l_global_timezone_name
636 , x_name => l_name
637 , x_return_status => l_status
638 , x_msg_count => l_msg_count
639 , x_msg_data => l_msg_data
640 );
641
642 IF ( l_status <> FND_API.G_RET_STS_SUCCESS )
643 THEN
644 RAISE fnd_api.G_EXC_ERROR;
645 END IF;
646
647 IF ( l_gmt_deviation <> l_server_offset)
648 THEN
649 --p_from_date is in DST
650 l_from_date_in_dst := true;
651 END IF;
652
653 --correct p_to_date by one hour based
654 -- DST information retrieved above.
655 IF ( l_from_date_in_dst = true AND
656 l_to_date_in_dst = false
657 )
658 THEN
659 l_to_date := l_to_date - 1/24;
660 ELSIF
661 ( l_from_date_in_dst = false AND
662 l_to_date_in_dst = true
663 )
664 THEN
665 l_to_date :=l_to_date + 1/24;
666 END IF;
667
668 RETURN l_to_date;
669
670 END get_dst_corrected_date;
671
672
673 -------------------------------------------------------
674 -------------------------------------------------------
675 PROCEDURE recur_main (
676 p_occurs_which VARCHAR2 DEFAULT NULL,
677 p_day_of_week VARCHAR2 DEFAULT NULL,
678 p_date_of_month NUMBER DEFAULT NULL,
679 p_occurs_month NUMBER DEFAULT NULL,
680 p_occurs_uom VARCHAR2 DEFAULT NULL,
681 p_occurs_every NUMBER DEFAULT NULL,
682 p_occurs_number NUMBER DEFAULT 0,
683 p_start_date DATE DEFAULT NULL,
684 p_end_date DATE DEFAULT NULL,
685 x_output_dates_tbl OUT NOCOPY jtf_task_recurrences_pvt.output_dates_rec,
686 x_output_dates_counter OUT NOCOPY INTEGER,
687 p_sunday VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
688 p_monday VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
689 p_tuesday VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
690 p_wednesday VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
691 p_thursday VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
692 p_friday VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
693 p_saturday VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
694 p_task_id NUMBER DEFAULT NULL
695 )
696 IS
697 p_required_date DATE := p_start_date;
698 p_count NUMBER := 0;
699 output_dates_counter INTEGER := 1;
700 success BOOLEAN;
701 generated BOOLEAN := TRUE;
702 valid_date BOOLEAN := FALSE;
703 i INTEGER := 1;
704 j INTEGER := 1;
705 p_required_date_tbl jtf_task_recurrences_pvt.output_dates_rec;
706 l_day_of_week VARCHAR2(15);
707 l_month VARCHAR2(2);
708
709 /******* Start of addition by SBARAT on 12/04/2006 for bug# 5119803 ******/
710 l_tz_enabled_prof VARCHAR2(10) := fnd_profile.value('ENABLE_TIMEZONE_CONVERSIONS');
711 l_server_tz_id NUMBER := to_number(fnd_profile.value_specific('SERVER_TIMEZONE_ID'));
712 l_client_tz_id NUMBER := to_number(fnd_profile.value_specific('CLIENT_TIMEZONE_ID'));
713
714 Cursor c_task IS
715 Select calendar_start_date, timezone_id From jtf_tasks_b Where task_id=p_task_id;
716
717 l_cal_start_date DATE;
718 l_conv_cal_start_date DATE;
719 l_tz_id NUMBER;
720 l_tz_diff NUMBER;
721 l_required_date DATE;
722 /******* End of addition by SBARAT on 12/04/2006 for bug# 5119803 ******/
723
724 BEGIN
725
726 WHILE 2 > 1
727 LOOP
728 IF p_occurs_number < output_dates_counter
729 THEN
730 EXIT;
731 END IF;
732
733 generated := TRUE;
734
735 IF p_occurs_uom = 'DAY'
736 THEN
737 NULL;
738 END IF;
739
740 IF p_occurs_uom = 'WEK'
741 THEN
742 jtf_task_recurrences_pvt.get_week_day (
743 p_sunday => p_sunday,
744 p_monday => p_monday,
745 p_tuesday => p_tuesday,
746 p_wednesday => p_wednesday,
747 p_thursday => p_thursday,
748 p_friday => p_friday,
749 p_saturday => p_saturday,
750 p_required_date => p_required_date,
751 x_required_date => p_required_date
752 );
753 END IF;
754
755 IF p_occurs_uom = 'WK'
756 THEN
757 IF ltrim(rtrim(TO_CHAR (p_required_date, 'DAY', 'NLS_DATE_LANGUAGE=AMERICAN'))) = rtrim(ltrim(p_day_of_week)) -- Fix Bug 2398568
758 THEN
759 NULL;
760 ELSE
761 generated := FALSE;
762 END IF;
763 END IF;
764
765 IF p_occurs_uom = 'MTH'
766 THEN
767 IF p_date_of_month IS NULL
768 THEN
769 jtf_task_recurrences_pvt.get_occurs_which (
770 p_occurs_which,
771 p_day_of_week,
772 TO_CHAR (p_required_date, 'MON', 'NLS_DATE_LANGUAGE=AMERICAN'), -- Fix Bug 2398568
773 TO_CHAR (p_required_date, 'YYYY'),
774 p_required_date
775 );
776
777
778 ELSE
779 jtf_task_recurrences_pvt.occurs_date_of_month (
780 p_date_of_month,
781 TO_CHAR (p_required_date, 'MON', 'NLS_DATE_LANGUAGE=AMERICAN'), -- Fix Bug 2398568
782 TO_CHAR (p_required_date, 'YYYY'),
783 p_required_date
784 );
785
786 END IF;
787 END IF;
788
789 IF p_occurs_uom = 'MON'
790 THEN
791 IF p_date_of_month IS NULL
792 THEN
793
794 jtf_task_recurrences_pvt.get_mth_day (
795 p_occurs_which => p_occurs_which,
796 p_sunday => p_sunday,
797 p_monday => p_monday,
798 p_tuesday => p_tuesday,
799 p_wednesday => p_wednesday,
800 p_thursday => p_thursday,
801 p_friday => p_friday,
802 p_saturday => p_saturday,
803 p_month => TO_CHAR (p_required_date, 'MON', 'NLS_DATE_LANGUAGE=AMERICAN'), -- Fix Bug 2398568
804 p_year => TO_CHAR (p_required_date, 'YYYY'),
805 x_required_date => p_required_date
806 );
807
808
809 ELSE
810 -- when date of month is selected
811 jtf_task_recurrences_pvt.occurs_date_of_month (
812 p_date_of_month,
813 TO_CHAR (p_required_date, 'MON', 'NLS_DATE_LANGUAGE=AMERICAN'), -- Fix Bug 2398568
814 TO_CHAR (p_required_date, 'YYYY'),
815 p_required_date
816 );
817
818 END IF;
819 END IF;
820
821 IF p_occurs_uom = 'YR'
822 THEN
823
824 IF p_date_of_month IS NOT NULL
825 THEN
826 IF ( p_occurs_month < TO_CHAR (p_required_date, 'MM')
827 OR NOT (p_occurs_month = TO_CHAR (p_required_date, 'MM'))
828 AND p_date_of_month <= TO_CHAR (LAST_DAY (p_required_date), 'DD')
829 AND p_date_of_month >= TO_CHAR (p_required_date, 'DD'))
830 THEN
831 success := FALSE;
832 p_required_date := ADD_MONTHS (p_required_date, 1);
833
834 WHILE NOT success
835 LOOP
836 IF TO_NUMBER (TO_CHAR (LAST_DAY (p_required_date), 'DD')) >= p_date_of_month
837 AND p_occurs_month = TO_CHAR (p_required_date, 'MM')
838 THEN
839 p_required_date :=
840 TO_DATE (
841 TO_CHAR (p_date_of_month) ||
842 '-' ||
843 TO_CHAR (p_required_date, 'MM') ||
844 '-' ||
845 TO_CHAR (p_required_date, 'yyyy'),
846 'dd-mm-yyyy'
847 );
848 success := TRUE;
849 ELSE
850 p_required_date := ADD_MONTHS (p_required_date, 1);
851 END IF;
852 END LOOP;
853 ELSE
854 p_required_date :=
855 TO_DATE (TO_CHAR (p_date_of_month) || '-' || p_occurs_month || '-' || TO_CHAR (p_required_date, 'yyyy'), 'dd-mm-yyyy');
856 END IF;
857 ELSE
858 IF TO_NUMBER (TO_CHAR (p_required_date, 'MM')) = p_occurs_month
859 THEN
860 jtf_task_recurrences_pvt.get_occurs_which (
861 p_occurs_which,
862 p_day_of_week,
863 TO_CHAR (p_required_date, 'MON', 'NLS_DATE_LANGUAGE=AMERICAN'), -- Fix Bug 2398568
864 TO_CHAR (p_required_date, 'YYYY'),
865 p_required_date
866 );
867
868 generated := TRUE;
869 ELSE
870 generated := FALSE;
871 END IF;
872 END IF;
873 END IF;
874 IF p_occurs_uom = 'YER' THEN
875 IF p_date_of_month IS NOT NULL
876 THEN
877 IF ( p_occurs_month < TO_CHAR (p_required_date, 'MM')
878 OR NOT (p_occurs_month = TO_CHAR (p_required_date, 'MM'))
879 AND p_date_of_month <= TO_CHAR (LAST_DAY (p_required_date), 'DD')
880 AND p_date_of_month >= TO_CHAR (p_required_date, 'DD'))
881 THEN
882 success := FALSE;
883 p_required_date := ADD_MONTHS (p_required_date, 1);
884
885 WHILE NOT success
886 LOOP
887 IF TO_NUMBER (TO_CHAR (LAST_DAY (p_required_date), 'DD')) >= p_date_of_month
888 AND p_occurs_month = TO_CHAR (p_required_date, 'MM')
889 THEN
890 p_required_date :=
891 TO_DATE (
892 TO_CHAR (p_date_of_month) ||
893 '-' ||
894 TO_CHAR (p_required_date, 'MM') ||
895 '-' ||
896 TO_CHAR (p_required_date, 'yyyy'),
897 'dd-mm-yyyy'
898 );
899
900 success := TRUE;
901 ELSE
902 p_required_date := ADD_MONTHS (p_required_date, 1);
903 END IF;
904 END LOOP;
905 ELSE
906 -- Commented out by SBARAT on 12/04/2006 for bug# 5119803
907 --p_required_date :=
908 --TO_DATE (TO_CHAR (p_date_of_month) || '-' || p_occurs_month || '-' || TO_CHAR (p_required_date, 'yyyy'), 'dd-mm-yyyy');
909
910 /******* Start of addition by SBARAT on 12/04/2006 for bug# 5119803 ******/
911 ---------------------------------------------------------------
912 -- This new logic has been introduced to handle leap-year issue
913 ----------------------------------------------------------------
914 IF (NOT ((p_date_of_month = 28 OR p_date_of_month = 29) AND p_occurs_month = 2)
915 AND NOT (p_date_of_month = 01 AND p_occurs_month = 3))
916 THEN
917 p_required_date :=
918 TO_DATE (TO_CHAR (p_date_of_month) || '-' || p_occurs_month || '-' || TO_CHAR (p_required_date, 'yyyy'), 'dd-mm-yyyy');
919 ELSE
920 IF p_task_id IS NULL
921 THEN
922 IF (p_date_of_month = 29)
923 THEN
924 WHILE 2>1 LOOP
925 p_required_date:=LAST_DAY(p_required_date);
926
927 IF (to_number(to_char(p_required_date,'DD'))=29
928 AND to_number(to_char(p_required_date,'MM'))=2)
929 THEN
930 EXIT;
931 END IF;
932
933 p_required_date :=
934 ADD_MONTHS (to_date('01-02-'|| to_char(p_required_date, 'YYYY'), 'DD-MM-YYYY'), p_occurs_every*12);
935 END LOOP;
936 ELSE
937 p_required_date :=
938 TO_DATE (TO_CHAR (p_date_of_month) || '-' || p_occurs_month || '-' || TO_CHAR (p_required_date, 'yyyy'), 'dd-mm-yyyy');
939 END IF;
940 ELSE
941 Open c_task;
942 Fetch c_task Into l_cal_start_date, l_tz_id;
943 Close c_task;
944
945 IF ((l_tz_id IS NOT NULL AND l_tz_id <> l_server_tz_id) Or NVL(l_tz_enabled_prof,'N') <> 'Y' Or l_server_tz_id IS NULL)
946 THEN
947 IF (p_date_of_month = 29 AND p_occurs_month = 2)
948 THEN
949 WHILE 2>1 LOOP
950 p_required_date:=LAST_DAY(p_required_date);
951
952 IF (to_number(to_char(p_required_date,'DD'))=29
953 AND to_number(to_char(p_required_date,'MM'))=2)
954 THEN
955 EXIT;
956 END IF;
957
958 p_required_date := ADD_MONTHS (to_date('01-02-'|| to_char(p_required_date, 'YYYY'), 'DD-MM-YYYY'), p_occurs_every*12);
959 END LOOP;
960 ELSE
961 p_required_date :=
962 TO_DATE (TO_CHAR (p_date_of_month) || '-' || p_occurs_month || '-' || TO_CHAR (p_required_date, 'yyyy'), 'dd-mm-yyyy');
963 END IF;
964 ELSE
965 l_conv_cal_start_date:= HZ_TIMEZONE_PUB.CONVERT_DATETIME(l_server_tz_id,
966 l_client_tz_id,
967 l_cal_start_date
968 );
969
970 l_tz_diff:=(l_conv_cal_start_date - p_start_date);
971
972 IF ((p_date_of_month=28 AND to_number(TO_CHAR(l_conv_cal_start_date,'DD')) IN (27,28))
973 OR (p_date_of_month=1 AND to_number(TO_CHAR(l_conv_cal_start_date,'DD')) IN (1,2))
974 )
975 THEN
976 p_required_date :=
977 TO_DATE (TO_CHAR (p_date_of_month) || '-' || p_occurs_month || '-' || TO_CHAR (p_required_date, 'yyyy'), 'dd-mm-yyyy');
978 ELSIF (p_date_of_month IN (28,29) AND to_number(TO_CHAR(l_conv_cal_start_date,'DD'))=29)
979 THEN
980 WHILE 2>1 LOOP
981 p_required_date:=LAST_DAY(p_required_date);
982
983 IF (p_date_of_month=28 AND to_char(p_required_date,'DD')='29')
984 THEN
985 p_required_date:=p_required_date - 1;
986 END IF;
987
988 l_required_date:= p_required_date+l_tz_diff;
989
990 IF (to_number(to_char(l_required_date,'DD'))=29
991 AND to_number(to_char(l_required_date,'MM'))=2)
992 THEN
993 EXIT;
994 END IF;
995 p_required_date := ADD_MONTHS (to_date('01-02-'|| to_char(p_required_date, 'YYYY'), 'DD-MM-YYYY'), p_occurs_every*12);
996 END LOOP;
997
998 ELSIF (p_date_of_month IN (28,29) AND to_number(TO_CHAR(l_conv_cal_start_date,'DD'))=1)
999 THEN
1000 p_required_date :=
1001 LAST_DAY(TO_DATE (TO_CHAR (p_date_of_month-1) || '-' || p_occurs_month || '-' || TO_CHAR (p_required_date, 'yyyy'), 'dd-mm-yyyy'));
1002 ELSIF (p_date_of_month=29 AND to_number(TO_CHAR(l_conv_cal_start_date,'DD'))=28)
1003 THEN
1004 p_required_date := to_date('01-02-'|| to_char(p_required_date, 'YYYY'), 'DD-MM-YYYY') + 28;
1005
1006 ELSIF (p_date_of_month=1 AND to_number(TO_CHAR(l_conv_cal_start_date,'DD'))=29)
1007 THEN
1008 WHILE 2>1 LOOP
1009
1010 l_required_date:= p_required_date+l_tz_diff;
1011
1012 IF (to_number(to_char(l_required_date,'DD'))=29
1013 AND to_number(to_char(l_required_date,'MON'))=2)
1014 THEN
1015 EXIT;
1016 END IF;
1017
1018 p_required_date := ADD_MONTHS (to_date('01-03-'|| to_char(p_required_date, 'YYYY'), 'DD-MM-YYYY'), p_occurs_every*12);
1019 END LOOP;
1020 ELSIF (p_date_of_month=1 AND to_number(TO_CHAR(l_conv_cal_start_date,'DD'))=28)
1021 THEN
1022
1023 l_required_date:= p_required_date+l_tz_diff;
1024
1025 IF (to_number(to_char(l_required_date,'DD'))=28
1026 AND to_number(to_char(l_required_date,'MON'))=2)
1027 THEN
1028 NULL;
1029 ELSE
1030 p_required_date:=(p_required_date-1);
1031 END IF;
1032 END IF;
1033 END IF;
1034 END IF;
1035 END IF;
1036 /******* End of addition by SBARAT on 12/04/2006 for bug# 5119803 ******/
1037 END IF;
1038 ELSE
1039 IF TO_NUMBER (TO_CHAR (p_required_date, 'MM')) = p_occurs_month
1040 THEN
1041
1042 IF p_sunday = 'Y' THEN
1043 l_day_of_week := 'SUNDAY';
1044 ELSIF p_monday = 'Y' THEN
1045 l_day_of_week := 'MONDAY';
1046 ELSIF p_tuesday = 'Y' THEN
1047 l_day_of_week := 'TUESDAY';
1048 ELSIF p_wednesday = 'Y' THEN
1049 l_day_of_week := 'WEDNESDAY';
1050 ELSIF p_thursday = 'Y' THEN
1051 l_day_of_week := 'THURSDAY';
1052 ELSIF p_friday = 'Y' THEN
1053 l_day_of_week := 'FRIDAY';
1054 ELSIF p_saturday = 'Y' THEN
1055 l_day_of_week := 'SATURDAY';
1056 END IF;
1057 jtf_task_recurrences_pvt.get_occurs_which (
1058 p_occurs_which,
1059 l_day_of_week,
1060 TO_CHAR (p_required_date, 'MON', 'NLS_DATE_LANGUAGE=AMERICAN'), -- Fix Bug 2398568
1061 TO_CHAR (p_required_date, 'YYYY'),
1062 p_required_date
1063 );
1064
1065 generated := TRUE;
1066 ELSE
1067 generated := FALSE;
1068 END IF;
1069
1070 END IF;
1071
1072 END IF;
1073 IF p_required_date > p_end_date
1074 THEN
1075 EXIT;
1076 END IF;
1077 IF generated
1078 AND ( (trunc(p_required_date) <= trunc(p_end_date))
1079 OR (output_dates_counter <= p_occurs_number))
1080 THEN
1081 IF trunc(p_required_date) >= trunc(p_start_date)
1082 THEN
1083 x_output_dates_tbl (output_dates_counter) := p_required_date;
1084 output_dates_counter := output_dates_counter + 1;
1085 valid_date := TRUE;
1086 END IF;
1087 END IF;
1088
1089 IF p_occurs_uom IN ('MTH', 'YR')
1090 THEN
1091 IF valid_date
1092 THEN
1093 p_required_date := ADD_MONTHS (p_required_date, p_occurs_every);
1094 ELSE
1095 p_required_date := ADD_MONTHS (p_required_date, 1);
1096 END IF;
1097 END IF;
1098
1099 IF p_occurs_uom IN ('YER')
1100 THEN
1101 IF valid_date
1102 THEN
1103 --p_required_date := ADD_MONTHS (p_required_date, p_occurs_every*12);
1104 IF p_occurs_month < 10 THEN
1105 l_month := '0' || p_occurs_month;
1106 ELSE
1107 l_month := p_occurs_month; -- Fix bug 2720817
1108 END IF;
1109 p_required_date := ADD_MONTHS (to_date('01' || l_month
1110 || to_char(p_required_date, 'YYYY'), 'DD-MM-YYYY'), p_occurs_every*12);
1111
1112 ELSE
1113 p_required_date := ADD_MONTHS (p_required_date, 1);
1114 END IF;
1115 END IF;
1116
1117 IF p_occurs_uom IN ('DAY')
1118 THEN
1119 p_required_date := p_required_date + p_occurs_every;
1120 END IF;
1121
1122
1123
1124 IF p_occurs_uom = 'MON' AND p_date_of_month IS NULL
1125 THEN
1126 WHILE (i < 7)
1127 AND (TO_CHAR (p_required_date, 'MON','NLS_DATE_LANGUAGE=AMERICAN') = TO_CHAR (p_required_date + i, 'MON','NLS_DATE_LANGUAGE=AMERICAN'))
1128 LOOP
1129 --check the next 6 days starts from the required date to see if they are checked
1130 --if checked, add up to the output
1131 IF p_sunday = 'Y' AND (p_required_date + i > p_start_date) AND (output_dates_counter <= p_occurs_number OR p_occurs_number is null)
1132 THEN
1133 IF RTRIM (LTRIM (TO_CHAR (p_required_date + i, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'SUNDAY'
1134 THEN
1135 x_output_dates_tbl (output_dates_counter) := p_required_date + i;
1136 output_dates_counter := output_dates_counter + 1;
1137 END IF;
1138 END IF;
1139
1140 IF p_monday = 'Y' AND (p_required_date + i > p_start_date) AND (output_dates_counter <= p_occurs_number OR p_occurs_number is null)
1141 THEN
1142 IF RTRIM (LTRIM (TO_CHAR (p_required_date + i, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) ='MONDAY'
1143 THEN
1144 x_output_dates_tbl (output_dates_counter) := p_required_date + i;
1145 output_dates_counter := output_dates_counter + 1;
1146 END IF;
1147 END IF;
1148
1149 IF p_tuesday = 'Y' AND (p_required_date + i > p_start_date) AND (output_dates_counter <= p_occurs_number OR p_occurs_number is null)
1150 THEN
1151 IF RTRIM (LTRIM (TO_CHAR (p_required_date + i, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) ='TUESDAY'
1152 THEN
1153 x_output_dates_tbl (output_dates_counter) := p_required_date + i;
1154 output_dates_counter := output_dates_counter + 1;
1155 END IF;
1156 END IF;
1157
1158 IF p_wednesday = 'Y' AND (p_required_date + i > p_start_date) AND (output_dates_counter <= p_occurs_number OR p_occurs_number is null)
1159 THEN
1160 IF RTRIM (LTRIM (TO_CHAR (p_required_date + i, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'WEDNESDAY'
1161 THEN
1162 x_output_dates_tbl (output_dates_counter) := p_required_date + i;
1163 output_dates_counter := output_dates_counter + 1;
1164 END IF;
1165 END IF;
1166
1167 IF p_thursday = 'Y' AND (p_required_date + i > p_start_date) AND (output_dates_counter <= p_occurs_number OR p_occurs_number is null)
1168 THEN
1169 IF RTRIM (LTRIM (TO_CHAR (p_required_date + i, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'THURSDAY'
1170 THEN
1171 x_output_dates_tbl (output_dates_counter) := p_required_date + i;
1172 output_dates_counter := output_dates_counter + 1;
1173 END IF;
1174 END IF;
1175
1176 IF p_friday = 'Y' AND (p_required_date + i > p_start_date) AND (output_dates_counter <= p_occurs_number OR p_occurs_number is null)
1177 THEN
1178 IF RTRIM (LTRIM (TO_CHAR (p_required_date + i, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'FRIDAY'
1179 THEN
1180 x_output_dates_tbl (output_dates_counter) := p_required_date + i;
1181 output_dates_counter := output_dates_counter + 1;
1182
1183 END IF;
1184 END IF;
1185
1186 IF p_saturday = 'Y' AND (p_required_date + i > p_start_date) AND (output_dates_counter <= p_occurs_number OR p_occurs_number is null)
1187 THEN
1188 IF RTRIM (LTRIM (TO_CHAR (p_required_date + i, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'SATURDAY'
1189 THEN
1190 x_output_dates_tbl (output_dates_counter) := p_required_date + i;
1191 output_dates_counter := output_dates_counter + 1;
1192 END IF;
1193 END IF;
1194
1195 i := i + 1;
1196 END LOOP;
1197
1198 i := 1;
1199
1200 IF valid_date
1201 THEN
1202 --go to the next circle based on user's selection criteria
1203 --to check should required date is greater than end date, check if the end date meets the criteria.
1204 if trunc(p_required_date) = trunc(p_end_date) then
1205 exit;
1206 else
1207 p_required_date := ADD_MONTHS (x_output_dates_tbl (output_dates_counter-1), p_occurs_every);
1208 if p_required_date > p_end_date and
1209 to_char(p_required_date, 'MON', 'NLS_DATE_LANGUAGE=AMERICAN') = to_char(p_end_date, 'MON', 'NLS_DATE_LANGUAGE=AMERICAN') then -- Fix Bug 2398568
1210 p_required_date := p_end_date;
1211 end if;
1212 end if;
1213 ELSE
1214 --if not valid, add one month to start the loop again
1215 --to check if the required date less than end date, and counter is not incremented, use p_required_date
1216 if (output_dates_counter = 1) then
1217 p_required_date := ADD_MONTHS (p_required_date, 1);
1218 else
1219 p_required_date := ADD_MONTHS (x_output_dates_tbl (output_dates_counter-1), 1);
1220 end if;
1221 END IF;
1222
1223 --when date of month is selected
1224 ELSIF p_occurs_uom = 'MON' AND p_date_of_month IS NOT NULL
1225 THEN
1226 IF valid_date
1227 THEN
1228 --go to the next circle based on user's selection criteria
1229 p_required_date := ADD_MONTHS (p_required_date, p_occurs_every);
1230
1231 -- Commented out by SBARAT on 12/04/2006 for bug# 5144171
1232 /*WHILE 2 > 1 AND j < 1000 LOOP
1233 IF TO_CHAR (p_required_date, 'DD') <> p_date_of_month THEN
1234 p_required_date := ADD_MONTHS (p_required_date, p_occurs_every);
1235 j := j + 1;
1236 ELSE
1237 exit;
1238 END IF;
1239 END LOOP;*/
1240
1241 /******** Start of addition by SBARAT on 12/04/2006 for bug# 5144171 ********/
1242 WHILE 2 > 1 AND j < 1000 LOOP
1243 IF to_number(TO_CHAR (p_required_date, 'DD')) < p_date_of_month THEN
1244 p_required_date := ADD_MONTHS (p_required_date, p_occurs_every);
1245 j := j + 1;
1246 ELSE
1247 exit;
1248 END IF;
1249 END LOOP;
1250
1251 IF to_number(TO_CHAR (p_required_date, 'DD')) > p_date_of_month
1252 THEN
1253 p_required_date:=p_required_date-(to_number(TO_CHAR (p_required_date, 'DD')) - p_date_of_month);
1254 END IF;
1255 /******** End of addition by SBARAT on 12/04/2006 for bug# 5144171 ********/
1256
1257 ELSE
1258 --if not valid, add one month to start the loop again
1259 p_required_date := ADD_MONTHS (p_required_date, 1);
1260 END IF;
1261 END IF;
1262
1263 IF p_occurs_uom IN ('WK')
1264 THEN
1265 if generated then
1266 p_required_date := p_required_date + p_occurs_every * 7;
1267 else
1268 p_required_date := p_required_date + 1;
1269 end if ;
1270 END IF;
1271
1272 IF p_occurs_uom IN ('WEK')
1273 THEN
1274 IF RTRIM (LTRIM (TO_CHAR (p_required_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'SUNDAY'
1275 THEN
1276 IF p_monday = 'Y' AND (output_dates_counter <= p_occurs_number OR p_occurs_number is null) THEN
1277 x_output_dates_tbl (output_dates_counter) := p_required_date + 1;
1278 output_dates_counter := output_dates_counter + 1;
1279 END IF;
1280
1281 IF p_tuesday = 'Y' AND (output_dates_counter <= p_occurs_number OR p_occurs_number is null) THEN
1282 x_output_dates_tbl (output_dates_counter) := p_required_date + 2;
1283 output_dates_counter := output_dates_counter + 1;
1284 END IF;
1285
1286 IF p_wednesday = 'Y' AND (output_dates_counter <= p_occurs_number OR p_occurs_number is null) THEN
1287 x_output_dates_tbl (output_dates_counter) := p_required_date + 3;
1288 output_dates_counter := output_dates_counter + 1;
1289 END IF;
1290
1291 IF p_thursday = 'Y' AND (output_dates_counter <= p_occurs_number OR p_occurs_number is null) THEN
1292 x_output_dates_tbl (output_dates_counter) := p_required_date + 4;
1293 output_dates_counter := output_dates_counter + 1;
1294 END IF;
1295
1296 IF p_friday = 'Y' AND (output_dates_counter <= p_occurs_number OR p_occurs_number is null) THEN
1297 x_output_dates_tbl (output_dates_counter) := p_required_date + 5;
1298 output_dates_counter := output_dates_counter + 1;
1299 END IF;
1300
1301 IF p_saturday = 'Y' AND (output_dates_counter <= p_occurs_number OR p_occurs_number is null) THEN
1302 x_output_dates_tbl (output_dates_counter) := p_required_date + 6;
1303 output_dates_counter := output_dates_counter + 1;
1304 END IF;
1305 p_required_date := p_required_date + p_occurs_every * 7;
1306 END IF;
1307
1308 IF RTRIM (LTRIM (TO_CHAR (p_required_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'MONDAY'
1309 THEN
1310
1311 IF p_tuesday = 'Y' AND (output_dates_counter <= p_occurs_number OR p_occurs_number is null) THEN
1312 x_output_dates_tbl (output_dates_counter) := p_required_date + 1;
1313 output_dates_counter := output_dates_counter + 1;
1314 END IF;
1315
1316 IF p_wednesday = 'Y' AND (output_dates_counter <= p_occurs_number OR p_occurs_number is null) THEN
1317 x_output_dates_tbl (output_dates_counter) := p_required_date + 2;
1318 output_dates_counter := output_dates_counter + 1;
1319 END IF;
1320
1321 IF p_thursday = 'Y' AND (output_dates_counter <= p_occurs_number OR p_occurs_number is null) THEN
1322 x_output_dates_tbl (output_dates_counter) := p_required_date + 3;
1323 output_dates_counter := output_dates_counter + 1;
1324 END IF;
1325
1326 IF p_friday = 'Y' AND (output_dates_counter <= p_occurs_number OR p_occurs_number is null) THEN
1327 x_output_dates_tbl (output_dates_counter) := p_required_date + 4;
1328 output_dates_counter := output_dates_counter + 1;
1329 END IF;
1330
1331 IF p_saturday = 'Y' AND (output_dates_counter <= p_occurs_number OR p_occurs_number is null) THEN
1332 x_output_dates_tbl (output_dates_counter) := p_required_date + 5;
1333 output_dates_counter := output_dates_counter + 1;
1334 END IF;
1335
1336 IF p_sunday = 'Y' THEN
1337 p_required_date := p_required_date - 1;
1338 END IF;
1339
1340 p_required_date := p_required_date + p_occurs_every * 7;
1341 END IF;
1342
1343 IF RTRIM (LTRIM (TO_CHAR (p_required_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'TUESDAY'
1344 THEN
1345
1346 IF p_wednesday = 'Y' AND (output_dates_counter <= p_occurs_number OR p_occurs_number is null) THEN
1347 x_output_dates_tbl (output_dates_counter) := p_required_date + 1;
1348 output_dates_counter := output_dates_counter + 1;
1349 END IF;
1350
1351 IF p_thursday = 'Y' AND (output_dates_counter <= p_occurs_number OR p_occurs_number is null) THEN
1352 x_output_dates_tbl (output_dates_counter) := p_required_date + 2;
1353 output_dates_counter := output_dates_counter + 1;
1354 END IF;
1355
1356 IF p_friday = 'Y' AND (output_dates_counter <= p_occurs_number OR p_occurs_number is null) THEN
1357 x_output_dates_tbl (output_dates_counter) := p_required_date + 3;
1358 output_dates_counter := output_dates_counter + 1;
1359 END IF;
1360
1361 IF p_saturday = 'Y' AND (output_dates_counter <= p_occurs_number OR p_occurs_number is null) THEN
1362 x_output_dates_tbl (output_dates_counter) := p_required_date + 4;
1363 output_dates_counter := output_dates_counter + 1;
1364 END IF;
1365
1366 IF p_monday = 'Y' AND p_sunday <> 'Y' THEN
1367 p_required_date := p_required_date - 1;
1368 ELSIF p_sunday = 'Y' THEN
1369 p_required_date := p_required_date - 2;
1370 END IF;
1371 p_required_date := p_required_date + p_occurs_every * 7;
1372
1373 END IF;
1374
1375 IF RTRIM (LTRIM (TO_CHAR (p_required_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'WEDNESDAY'
1376 THEN
1377
1378 IF p_thursday = 'Y' AND (output_dates_counter <= p_occurs_number OR p_occurs_number is null) THEN
1379 x_output_dates_tbl (output_dates_counter) := p_required_date + 1;
1380 output_dates_counter := output_dates_counter + 1;
1381 END IF;
1382
1383 IF p_friday = 'Y' AND (output_dates_counter <= p_occurs_number OR p_occurs_number is null) THEN
1384 x_output_dates_tbl (output_dates_counter) := p_required_date + 2;
1385 output_dates_counter := output_dates_counter + 1;
1386 END IF;
1387
1388 IF p_saturday = 'Y' AND (output_dates_counter <= p_occurs_number OR p_occurs_number is null) THEN
1389 x_output_dates_tbl (output_dates_counter) := p_required_date + 3;
1390 output_dates_counter := output_dates_counter + 1;
1391 END IF;
1392
1393 IF p_tuesday = 'Y' AND p_monday <> 'Y' AND p_sunday <> 'Y' THEN
1394 p_required_date := p_required_date - 1;
1395 ELSIF p_monday = 'Y' AND p_sunday <> 'Y' THEN
1396 p_required_date := p_required_date - 2;
1397 ELSIF p_sunday = 'Y' THEN
1398 p_required_date := p_required_date - 3;
1399 END IF;
1400
1401 p_required_date := p_required_date + p_occurs_every * 7;
1402 END IF;
1403
1404 IF RTRIM (LTRIM (TO_CHAR (p_required_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'THURSDAY'
1405 THEN
1406
1407 IF p_friday = 'Y' AND (output_dates_counter <= p_occurs_number OR p_occurs_number is null) THEN
1408 x_output_dates_tbl (output_dates_counter) := p_required_date + 1;
1409 output_dates_counter := output_dates_counter + 1;
1410 END IF;
1411
1412 IF p_saturday = 'Y' AND (output_dates_counter <= p_occurs_number OR p_occurs_number is null) THEN
1413 x_output_dates_tbl (output_dates_counter) := p_required_date + 2;
1414 output_dates_counter := output_dates_counter + 1;
1415 END IF;
1416
1417 IF p_wednesday = 'Y' AND p_tuesday <> 'Y' AND p_monday <> 'Y' AND p_sunday <> 'Y' THEN
1418 p_required_date := p_required_date - 1;
1419 ELSIF p_tuesday = 'Y' AND p_monday <> 'Y' AND p_sunday <> 'Y' THEN
1420 p_required_date := p_required_date - 2;
1421 ELSIF p_monday = 'Y' AND p_sunday <> 'Y' THEN
1422 p_required_date := p_required_date - 3;
1423 ELSIF p_sunday = 'Y' THEN
1424 p_required_date := p_required_date - 4;
1425 END IF;
1426
1427 p_required_date := p_required_date + p_occurs_every * 7;
1428 END IF;
1429
1430 IF RTRIM (LTRIM (TO_CHAR (p_required_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'FRIDAY'
1431 THEN
1432
1433 IF p_saturday = 'Y' AND (output_dates_counter <= p_occurs_number OR p_occurs_number is null) THEN
1434 x_output_dates_tbl (output_dates_counter) := p_required_date + 1;
1435 output_dates_counter := output_dates_counter + 1;
1436 END IF;
1437
1438 IF p_thursday = 'Y' AND p_wednesday <> 'Y' AND p_tuesday <> 'Y' AND p_monday <> 'Y' AND p_sunday <> 'Y' THEN
1439 p_required_date := p_required_date - 1;
1440 ELSIF p_wednesday = 'Y' AND p_tuesday <> 'Y' AND p_monday <> 'Y' AND p_sunday <> 'Y' THEN
1441 p_required_date := p_required_date - 2;
1442 ELSIF p_tuesday = 'Y' AND p_monday <> 'Y' AND p_sunday <> 'Y' THEN
1443 p_required_date := p_required_date - 3;
1444 ELSIF p_monday = 'Y' AND p_sunday <> 'Y' THEN
1445 p_required_date := p_required_date - 4;
1446 ELSIF p_sunday = 'Y' THEN
1447 p_required_date := p_required_date - 5;
1448 END IF;
1449
1450 p_required_date := p_required_date + p_occurs_every * 7;
1451 END IF;
1452
1453 IF RTRIM (LTRIM (TO_CHAR (p_required_date, 'DAY','NLS_DATE_LANGUAGE=AMERICAN'))) = 'SATURDAY'
1454 THEN
1455
1456 IF p_friday = 'Y' AND p_thursday <> 'Y' AND p_wednesday <> 'Y' AND p_tuesday <> 'Y' AND p_monday <> 'Y' AND p_sunday <> 'Y' THEN
1457 p_required_date := p_required_date - 1;
1458 ELSIF p_thursday = 'Y' AND p_wednesday <> 'Y' AND p_tuesday <> 'Y' AND p_monday <> 'Y' AND p_sunday <> 'Y' THEN
1459 p_required_date := p_required_date - 2;
1460 ELSIF p_wednesday = 'Y' AND p_tuesday <> 'Y' AND p_monday <> 'Y' AND p_sunday <> 'Y' THEN
1461 p_required_date := p_required_date - 3;
1462 ELSIF p_tuesday = 'Y' AND p_monday <> 'Y' AND p_sunday <> 'Y' THEN
1463 p_required_date := p_required_date - 4;
1464 ELSIF p_monday = 'Y' AND p_sunday <> 'Y' THEN
1465 p_required_date := p_required_date - 5;
1466 ELSIF p_sunday = 'Y' THEN
1467 p_required_date := p_required_date - 6;
1468 END IF;
1469
1470 p_required_date := p_required_date + p_occurs_every * 7;
1471 END IF;
1472 END IF;
1473 END LOOP;
1474 x_output_dates_counter := output_dates_counter;
1475 EXCEPTION
1476 WHEN OTHERS
1477 THEN
1478 null;
1479 END; -- Procedure
1480
1481 -------------------------------------------------------
1482 -------------------------------------------------------
1483 PROCEDURE generate_dates (
1484 p_occurs_which NUMBER DEFAULT NULL,
1485 p_day_of_week NUMBER DEFAULT NULL,
1486 p_date_of_month NUMBER DEFAULT NULL,
1487 p_occurs_month NUMBER DEFAULT NULL,
1488 p_occurs_uom VARCHAR2 DEFAULT NULL,
1489 p_occurs_every NUMBER DEFAULT NULL,
1490 p_occurs_number NUMBER DEFAULT 0,
1491 p_start_date DATE DEFAULT NULL,
1492 p_end_date DATE DEFAULT SYSDATE,
1493 x_output_dates_tbl OUT NOCOPY jtf_task_recurrences_pvt.output_dates_rec,
1494 x_output_dates_counter OUT NOCOPY INTEGER,
1495 p_sunday VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
1496 p_monday VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
1497 p_tuesday VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
1498 p_wednesday VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
1499 p_thursday VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
1500 p_friday VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
1501 p_saturday VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
1502 p_task_id NUMBER DEFAULT NULL
1503 )
1504 IS
1505 l_occurs_which VARCHAR2(20);
1506 l_day_of_week VARCHAR2(20);
1507 l_date_of_month NUMBER := p_date_of_month;
1508 l_occurs_month NUMBER := p_occurs_month;
1509 l_occurs_uom VARCHAR2(20);
1510 l_occurs_every NUMBER := p_occurs_every;
1511 l_occurs_number NUMBER := p_occurs_number;
1512 l_start_date DATE := p_start_date;
1513 l_end_date DATE := p_end_date;
1514 l_sunday VARCHAR2(1) := p_sunday;
1515 l_monday VARCHAR2(1) := p_monday;
1516 l_tuesday VARCHAR2(1) := p_tuesday;
1517 l_wednesday VARCHAR2(1) := p_wednesday;
1518 l_thursday VARCHAR2(1) := p_thursday;
1519 l_friday VARCHAR2(1) := p_friday;
1520 l_saturday VARCHAR2(1) := p_saturday;
1521 BEGIN
1522 SELECT DECODE (p_occurs_which, 1, 'FIRST', 2, 'SECOND', 3, 'THIRD', 4, 'FOUR', 99, 'LAST')
1523 INTO l_occurs_which
1524 FROM dual;
1525 SELECT DECODE (
1526 p_day_of_week,
1527 1, 'SUNDAY',
1528 2, 'MONDAY',
1529 3, 'TUESDAY',
1530 4, 'WEDNESDAY',
1531 5, 'THURSDAY',
1532 6, 'FRIDAY',
1533 7, 'SATURDAY',
1534 0, 'DAY',
1535 8, 'WEEKDAY',
1536 9, 'WEEKEND'
1537 )
1538 INTO l_day_of_week
1539 FROM dual;
1540 jtf_task_recurrences_pvt.recur_main (
1541 p_occurs_which => l_occurs_which,
1542 p_date_of_month => p_date_of_month,
1543 p_day_of_week => l_day_of_week,
1544 p_occurs_month => p_occurs_month,
1545 p_occurs_uom => p_occurs_uom,
1546 p_occurs_every => p_occurs_every,
1547 p_occurs_number => p_occurs_number,
1548 p_start_date => p_start_date,
1549 p_end_date => p_end_date,
1550 x_output_dates_tbl => x_output_dates_tbl,
1551 x_output_dates_counter => x_output_dates_counter,
1552 p_sunday => l_sunday,
1553 p_monday => l_monday,
1554 p_tuesday => l_tuesday,
1555 p_wednesday => l_wednesday,
1556 p_thursday => l_thursday,
1557 p_friday => l_friday,
1558 p_saturday => l_saturday,
1559 p_task_id => p_task_id
1560 );
1561 END;
1562
1563 FUNCTION get_ovn (p_task_id IN NUMBER)
1564 RETURN NUMBER
1565 IS
1566 CURSOR c_tasks_ovn (b_task_id NUMBER)
1567 IS
1568 SELECT object_version_number
1569 FROM jtf_tasks_b
1570 WHERE task_id = b_task_id;
1571
1572 l_object_version_number NUMBER;
1573 BEGIN
1574 OPEN c_tasks_ovn (p_task_id);
1575 FETCH c_tasks_ovn INTO l_object_version_number;
1576
1577 IF c_tasks_ovn%NOTFOUND
1578 THEN
1579 CLOSE c_tasks_ovn;
1580 raise_application_error(-20100,'Task OVN not found at GET_OVN');
1581 END IF;
1582
1583 CLOSE c_tasks_ovn;
1584 RETURN l_object_version_number;
1585 END get_ovn;
1586
1587 FUNCTION original_date_meets_criteria (p_output_dates_tbl IN jtf_task_recurrences_pvt.output_dates_rec,
1588 p_start_date_active IN DATE)
1589 RETURN BOOLEAN
1590 IS
1591 BEGIN
1592 FOR i IN 1..p_output_dates_tbl.last LOOP
1593 IF TRUNC(p_output_dates_tbl(i)) = TRUNC(p_start_date_active) THEN
1594 RETURN TRUE;
1595 END IF;
1596 END LOOP;
1597 RETURN FALSE;
1598 END;
1599
1600 FUNCTION week_days_are_null (p_sunday IN VARCHAR2, p_monday IN VARCHAR2, p_tuesday IN VARCHAR2,
1601 p_wednesday IN VARCHAR2, p_thursday IN VARCHAR2, p_friday IN VARCHAR2,
1602 p_saturday IN VARCHAR2)
1603 RETURN BOOLEAN
1604 IS
1605 BEGIN
1606 IF ((p_sunday = 'N' and p_monday = 'N' and p_tuesday = 'N' and
1607 p_wednesday = 'N' and p_thursday = 'N'and p_friday = 'N' and
1608 p_saturday = 'N') OR
1609 (p_sunday is null and p_monday is null and p_tuesday is null and
1610 p_wednesday is null and p_thursday is null and p_friday is null
1611 and p_saturday is null) OR
1612 (p_sunday = jtf_task_utl.g_no_char and
1613 p_monday = jtf_task_utl.g_no_char and
1614 p_tuesday = jtf_task_utl.g_no_char and
1615 p_wednesday = jtf_task_utl.g_no_char and
1616 p_thursday = jtf_task_utl.g_no_char and
1617 p_friday = jtf_task_utl.g_no_char and
1618 p_saturday = jtf_task_utl.g_no_char)) THEN
1619 RETURN TRUE;
1620 ELSE
1621 RETURN FALSE;
1622 END IF;
1623 END;
1624
1625 PROCEDURE set_last_update_date(p_recurrence_rule_id IN NUMBER)
1626 IS
1627 -- Fix bug 2376554
1628 --CURSOR c_last_update_date IS
1629 --select max(last_update_date)
1630 -- from jtf_tasks_b t
1631 --where t.recurrence_rule_id = p_recurrence_rule_id
1632 -- and deleted_flag <> 'Y';
1633 l_date DATE := SYSDATE;
1634 BEGIN
1635 -- set all last_update_dates equal, needed for JSync project
1636 --OPEN c_last_update_date;
1637 --FETCH c_last_update_date INTO l_date;
1638 --IF c_last_update_date%NOTFOUND THEN
1639 -- CLOSE c_last_update_date;
1640 -- fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
1641 -- fnd_msg_pub.add;
1642 -- RAISE fnd_api.g_exc_unexpected_error;
1643 --ELSE
1644 -- CLOSE c_last_update_date;
1645 -- UPDATE jtf_tasks_b
1646 -- SET last_update_date = NVL(l_date, SYSDATE) -- Fix bug 2376554
1647 -- WHERE recurrence_rule_id = p_recurrence_rule_id;
1648 --END IF;
1649
1650 UPDATE jtf_task_recur_rules
1651 SET last_update_date = l_date
1652 , creation_date = l_date
1653 WHERE recurrence_rule_id = p_recurrence_rule_id;
1654 END;
1655
1656 PROCEDURE get_repeat_start_date(p_recurrence_rule_id IN NUMBER,
1657 x_repeat_start_date OUT NOCOPY DATE)
1658 IS
1659 CURSOR c_start_date IS
1660 select min(planned_start_date)
1661 from jtf_tasks_b t
1662 where t.recurrence_rule_id = p_recurrence_rule_id
1663 and deleted_flag <> 'Y';
1664
1665 l_start_date DATE;
1666 BEGIN
1667 OPEN c_start_date;
1668 FETCH c_start_date INTO l_start_date;
1669 IF c_start_date%NOTFOUND THEN
1670 CLOSE c_start_date;
1671 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
1672 fnd_msg_pub.add;
1673 RAISE fnd_api.g_exc_unexpected_error;
1674 ELSE
1675 CLOSE c_start_date;
1676 x_repeat_start_date := l_start_date;
1677 END IF;
1678 END;
1679
1680 PROCEDURE get_repeat_end_date(p_recurrence_rule_id IN NUMBER,
1681 x_repeat_end_date OUT NOCOPY DATE)
1682 IS
1683 CURSOR c_end_date IS
1684 select max(planned_end_date)
1685 from jtf_tasks_b t
1686 where t.recurrence_rule_id = p_recurrence_rule_id
1687 and deleted_flag <> 'Y';
1688
1689 l_end_date DATE;
1690 BEGIN
1691 OPEN c_end_date;
1692 FETCH c_end_date INTO l_end_date;
1693 IF c_end_date%NOTFOUND THEN
1694 CLOSE c_end_date;
1695 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
1696 fnd_msg_pub.add;
1697 RAISE fnd_api.g_exc_unexpected_error;
1698 ELSE
1699 CLOSE c_end_date;
1700 x_repeat_end_date := l_end_date;
1701 END IF;
1702 END;
1703
1704
1705 -------------------------------------------------------
1706 -------------------------------------------------------
1707 -------------------------------------------------------
1708 PROCEDURE validate_task_recurrence (
1709 p_occurs_which IN INTEGER DEFAULT NULL,
1710 p_day_of_week IN INTEGER DEFAULT NULL,
1711 p_date_of_month IN INTEGER DEFAULT NULL,
1712 p_occurs_month IN INTEGER DEFAULT NULL,
1713 p_occurs_uom IN VARCHAR2 DEFAULT NULL,
1714 p_occurs_every IN INTEGER DEFAULT NULL,
1715 p_occurs_number IN INTEGER DEFAULT NULL,
1716 p_start_date_active IN DATE DEFAULT NULL,
1717 p_end_date_active IN DATE DEFAULT NULL,
1718 x_msg_count OUT NOCOPY NUMBER,
1719 x_msg_data OUT NOCOPY VARCHAR2,
1720 x_return_status OUT NOCOPY VARCHAR2,
1721 p_sunday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
1722 p_monday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
1723 p_tuesday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
1724 p_wednesday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
1725 p_thursday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
1726 p_friday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
1727 p_saturday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char
1728 )
1729 IS
1730 BEGIN
1731 x_return_status := fnd_api.g_ret_sts_success;
1732
1733 --- the uom should be right.
1734 IF p_occurs_uom IS NULL
1735 THEN
1736 fnd_message.set_name ('JTF', 'JTF_TASK_MISSING_RECUR_UOM');
1737 fnd_msg_pub.add;
1738 RAISE fnd_api.g_exc_unexpected_error;
1739 END IF;
1740
1741 IF p_occurs_uom NOT IN ('DAY', 'WK', 'WEK', 'MTH', 'MON', 'YR', 'YER')
1742 THEN
1743 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_UOM');
1744 fnd_msg_pub.add;
1745 RAISE fnd_api.g_exc_unexpected_error;
1746 END IF;
1747 IF p_start_date_active IS NULL
1748 THEN
1749 fnd_message.set_name ('JTF', 'JTF_TASK_MISSING_START_DATE');
1750 fnd_msg_pub.add;
1751 RAISE fnd_api.g_exc_unexpected_error;
1752 END IF;
1753
1754 --- at least p_occurs_number or end date should be specified
1755 IF ( p_occurs_number <= 0 OR p_occurs_number IS NULL )
1756 AND p_end_date_active IS NULL
1757 THEN
1758 fnd_message.set_name ('JTF', 'JTF_TASK_RECUR_END_DATE_MSG');
1759 fnd_msg_pub.add;
1760 RAISE fnd_api.g_exc_unexpected_error;
1761 END IF;
1762 IF p_occurs_every IS NULL
1763 THEN
1764 fnd_message.set_name ('JTF', 'JTF_TASK_MISSING_OCCURS_EVERY');
1765 fnd_msg_pub.add;
1766 RAISE fnd_api.g_exc_unexpected_error;
1767 END IF;
1768 IF p_occurs_every < 0
1769 THEN
1770 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_OCCURS_EVERY');
1771 fnd_msg_pub.add;
1772 RAISE fnd_api.g_exc_unexpected_error;
1773 END IF;
1774
1775 --- occurs_every should be 1 if uom is year else it should be atleast 1
1776 IF p_occurs_uom IN ('YR')
1777 AND p_occurs_every <> 1
1778 THEN
1779 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
1780 fnd_msg_pub.add;
1781 RAISE fnd_api.g_exc_unexpected_error;
1782 ELSE
1783 IF p_occurs_every < 1
1784 THEN
1785 fnd_message.set_name ('JTF', 'JTF_TASK_OCCURS_EVERY_<_THAN_1');
1786 fnd_msg_pub.add;
1787 RAISE fnd_api.g_exc_unexpected_error;
1788 END IF;
1789 END IF;
1790 IF p_occurs_uom = 'DAY'
1791 THEN
1792 IF (p_occurs_which IS NOT NULL)
1793 OR (p_day_of_week IS NOT NULL)
1794 OR (p_date_of_month IS NOT NULL)
1795 OR (p_occurs_month IS NOT NULL)
1796 THEN
1797 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
1798 fnd_msg_pub.add;
1799 RAISE fnd_api.g_exc_unexpected_error;
1800 END IF;
1801 END IF;
1802
1803 IF p_occurs_uom = 'WK'
1804 THEN
1805 IF (p_occurs_which IS NOT NULL)
1806 OR (p_date_of_month IS NOT NULL)
1807 OR (p_occurs_month IS NOT NULL)
1808 THEN
1809
1810 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
1811 fnd_msg_pub.add;
1812 RAISE fnd_api.g_exc_unexpected_error;
1813 END IF;
1814
1815 IF p_day_of_week IS NULL
1816 THEN
1817 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
1818 fnd_msg_pub.add;
1819 RAISE fnd_api.g_exc_unexpected_error;
1820 ELSE
1821 IF (p_day_of_week < 1)
1822 OR (p_day_of_week > 7)
1823 THEN
1824 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
1825 fnd_msg_pub.add;
1826 RAISE fnd_api.g_exc_unexpected_error;
1827 END IF;
1828 END IF;
1829
1830
1831 END IF;
1832
1833 IF p_occurs_uom = 'MTH'
1834 THEN
1835
1836
1837 --- start from the day of the week
1838 IF p_day_of_week IS NOT NULL
1839 THEN
1840
1841 IF ( p_day_of_week < 0
1842 OR p_day_of_week > 9)
1843 THEN
1844
1845 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
1846 fnd_msg_pub.add;
1847 RAISE fnd_api.g_exc_unexpected_error;
1848 END IF;
1849 END IF;
1850
1851 IF p_occurs_which IS NULL
1852 AND p_date_of_month IS NULL
1853 THEN
1854
1855 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
1856 fnd_msg_pub.add;
1857 RAISE fnd_api.g_exc_unexpected_error;
1858 END IF;
1859
1860 IF (p_date_of_month IS NOT NULL)
1861 THEN
1862
1863 IF ( p_date_of_month < 1
1864 OR p_date_of_month > 31)
1865 THEN
1866
1867 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
1868 fnd_msg_pub.add;
1869 RAISE fnd_api.g_exc_unexpected_error;
1870 END IF;
1871
1872 IF p_occurs_month IS NOT NULL
1873 THEN
1874
1875 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
1876 fnd_msg_pub.add;
1877 RAISE fnd_api.g_exc_unexpected_error;
1878 END IF;
1879
1880 IF p_occurs_which IS NOT NULL
1881 THEN
1882
1883 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
1884 fnd_msg_pub.add;
1885 RAISE fnd_api.g_exc_unexpected_error;
1886 END IF;
1887 END IF;
1888 END IF;
1889
1890 IF p_occurs_uom = 'WEK'
1891 THEN
1892 IF (p_occurs_which IS NOT NULL)
1893 OR (p_date_of_month IS NOT NULL)
1894 OR (p_occurs_month IS NOT NULL)
1895 THEN
1896
1897 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
1898 fnd_msg_pub.add;
1899 RAISE fnd_api.g_exc_unexpected_error;
1900 END IF;
1901
1902 IF week_days_are_null(p_sunday, p_monday, p_tuesday, p_wednesday, p_thursday, p_friday, p_saturday)
1903 THEN
1904 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
1905 fnd_msg_pub.add;
1906 RAISE fnd_api.g_exc_unexpected_error;
1907 END IF;
1908 END IF;
1909
1910 IF p_occurs_uom = 'MON'
1911 THEN
1912
1913 IF p_sunday = 'N' and p_monday = 'N' and p_tuesday = 'N' and p_wednesday = 'N' and p_thursday = 'N'
1914 and p_friday = 'N' and p_saturday = 'N'
1915 THEN
1916 --- here the date of the month should be specified
1917 IF p_date_of_month IS NULL
1918 THEN
1919
1920 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
1921 fnd_msg_pub.add;
1922 RAISE fnd_api.g_exc_unexpected_error;
1923 END IF;
1924
1925 IF ( p_date_of_month < 1
1926 OR p_date_of_month > 31)
1927 THEN
1928
1929 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
1930 fnd_msg_pub.add;
1931 RAISE fnd_api.g_exc_unexpected_error;
1932 END IF;
1933
1934 IF p_occurs_month IS NOT NULL
1935 THEN
1936
1937 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
1938 fnd_msg_pub.add;
1939 RAISE fnd_api.g_exc_unexpected_error;
1940 END IF;
1941
1942 IF p_occurs_which IS NOT NULL
1943 THEN
1944
1945 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
1946 fnd_msg_pub.add;
1947 RAISE fnd_api.g_exc_unexpected_error;
1948 END IF;
1949 ELSE
1950 /* IF p_date_of_month IS NOT NULL
1951 THEN
1952
1953 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
1954 fnd_msg_pub.add;
1955 RAISE fnd_api.g_exc_unexpected_error;
1956 END IF;
1957
1958 IF p_occurs_month IS NOT NULL
1959 THEN
1960
1961 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
1962 fnd_msg_pub.add;
1963 RAISE fnd_api.g_exc_unexpected_error;
1964 END IF;
1965 */
1966 IF (p_occurs_which NOT IN (1, 2, 3, 4, 99))
1967 THEN
1968
1969 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
1970 fnd_msg_pub.add;
1971 RAISE fnd_api.g_exc_unexpected_error;
1972 END IF;
1973 END IF;
1974 END IF;
1975
1976 ------------------------------------------------------------------------------------------------------
1977 ------------------------------------------------------------------------------------------------------
1978 ---- end of checking month UOM
1979 ------------------------------------------------------------------------------------------------------
1980 ------------------------------------------------------------------------------------------------------
1981
1982 IF p_occurs_uom = 'YR'
1983 THEN
1984 IF p_occurs_month IS NULL
1985 THEN
1986
1987 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
1988 fnd_msg_pub.add;
1989 RAISE fnd_api.g_exc_unexpected_error;
1990 END IF;
1991 IF p_occurs_month < 1
1992 OR p_occurs_month > 12
1993 THEN
1994
1995 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
1996 fnd_msg_pub.add;
1997 RAISE fnd_api.g_exc_unexpected_error;
1998 END IF;
1999 IF p_date_of_month IS NULL
2000 AND ( p_occurs_which IS NULL
2001 OR p_day_of_week IS NULL)
2002 THEN
2003
2004 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
2005 fnd_msg_pub.add;
2006 RAISE fnd_api.g_exc_unexpected_error;
2007 END IF;
2008 IF p_date_of_month IS NOT NULL
2009 AND ( p_occurs_which IS NOT NULL
2010 OR p_day_of_week IS NOT NULL)
2011 THEN
2012 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
2013 fnd_msg_pub.add;
2014 RAISE fnd_api.g_exc_unexpected_error;
2015 END IF;
2016 IF p_date_of_month IS NULL
2017 THEN
2018 IF (p_occurs_which IS NULL)
2019 THEN
2020 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
2021 fnd_msg_pub.add;
2022 RAISE fnd_api.g_exc_unexpected_error;
2023 END IF;
2024 IF (p_occurs_which NOT IN (1, 2, 3, 4, 99))
2025 THEN
2026 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
2027 fnd_msg_pub.add;
2028 RAISE fnd_api.g_exc_unexpected_error;
2029 END IF;
2030 IF p_day_of_week IS NULL
2031 THEN
2032 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
2033 fnd_msg_pub.add;
2034 RAISE fnd_api.g_exc_unexpected_error;
2035 END IF;
2036 IF ( p_day_of_week < 0
2037 OR p_day_of_week > 9)
2038 THEN
2039 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
2040 fnd_msg_pub.add;
2041 RAISE fnd_api.g_exc_unexpected_error;
2042 END IF;
2043 IF p_date_of_month IS NOT NULL
2044 THEN
2045 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
2046 fnd_msg_pub.add;
2047 RAISE fnd_api.g_exc_unexpected_error;
2048 END IF;
2049 ELSE
2050 IF ( p_date_of_month < 1
2051 OR p_date_of_month > 31)
2052 THEN
2053 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
2054 fnd_msg_pub.add;
2055 RAISE fnd_api.g_exc_unexpected_error;
2056 END IF;
2057
2058 IF ( p_occurs_month = 2
2059 AND p_date_of_month > 29)
2060 OR ( p_occurs_month IN (1, 3, 5, 7, 8, 10, 12)
2061 AND p_date_of_month > 31)
2062 OR ( p_occurs_month IN (4, 6, 9, 11)
2063 AND p_date_of_month > 30)
2064 THEN
2065
2066 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
2067 fnd_msg_pub.add;
2068 RAISE fnd_api.g_exc_unexpected_error;
2069 END IF;
2070
2071 IF (p_occurs_which IS NOT NULL)
2072 OR (p_day_of_week IS NOT NULL)
2073 THEN
2074
2075 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
2076 fnd_msg_pub.add;
2077 RAISE fnd_api.g_exc_unexpected_error;
2078 END IF;
2079 END IF;
2080 END IF;
2081 IF p_occurs_uom = 'YER'
2082 THEN
2083 IF p_occurs_month IS NULL
2084 THEN
2085
2086 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
2087 fnd_msg_pub.add;
2088 RAISE fnd_api.g_exc_unexpected_error;
2089 END IF;
2090 IF p_occurs_month < 1
2091 OR p_occurs_month > 12
2092 THEN
2093
2094 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
2095 fnd_msg_pub.add;
2096 RAISE fnd_api.g_exc_unexpected_error;
2097 END IF;
2098 IF p_date_of_month IS NULL
2099 AND (p_occurs_which IS NULL
2100 OR week_days_are_null(p_sunday, p_monday, p_tuesday, p_wednesday, p_thursday, p_friday, p_saturday))
2101 THEN
2102
2103 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
2104 fnd_msg_pub.add;
2105 RAISE fnd_api.g_exc_unexpected_error;
2106 END IF;
2107 IF p_date_of_month IS NOT NULL
2108 AND ( p_occurs_which IS NOT NULL
2109 OR p_day_of_week IS NOT NULL)
2110 THEN
2111 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
2112 fnd_msg_pub.add;
2113 RAISE fnd_api.g_exc_unexpected_error;
2114 END IF;
2115 IF p_date_of_month IS NULL
2116 THEN
2117 IF (p_occurs_which IS NULL)
2118 THEN
2119 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
2120 fnd_msg_pub.add;
2121 RAISE fnd_api.g_exc_unexpected_error;
2122 END IF;
2123 IF (p_occurs_which NOT IN (1, 2, 3, 4, 99))
2124 THEN
2125 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
2126 fnd_msg_pub.add;
2127 RAISE fnd_api.g_exc_unexpected_error;
2128 END IF;
2129 IF week_days_are_null(p_sunday, p_monday, p_tuesday, p_wednesday, p_thursday, p_friday, p_saturday)
2130 THEN
2131 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
2132 fnd_msg_pub.add;
2133 RAISE fnd_api.g_exc_unexpected_error;
2134 END IF;
2135 IF p_date_of_month IS NOT NULL
2136 THEN
2137 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
2138 fnd_msg_pub.add;
2139 RAISE fnd_api.g_exc_unexpected_error;
2140 END IF;
2141 ELSE
2142 IF ( p_date_of_month < 1
2143 OR p_date_of_month > 31)
2144 THEN
2145 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
2146 fnd_msg_pub.add;
2147 RAISE fnd_api.g_exc_unexpected_error;
2148 END IF;
2149
2150 IF (p_occurs_month = 2
2151 AND p_date_of_month > 29)
2152 OR ( p_occurs_month IN (1, 3, 5, 7, 8, 10, 12)
2153 AND p_date_of_month > 31)
2154 OR ( p_occurs_month IN (4, 6, 9, 11)
2155 AND p_date_of_month > 30)
2156 THEN
2157
2158 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
2159 fnd_msg_pub.add;
2160 RAISE fnd_api.g_exc_unexpected_error;
2161 END IF;
2162
2163 IF (p_occurs_which IS NOT NULL)
2164 OR (p_day_of_week IS NOT NULL)
2165 THEN
2166
2167 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_RECUR_RULE');
2168 fnd_msg_pub.add;
2169 RAISE fnd_api.g_exc_unexpected_error;
2170 END IF;
2171 END IF;
2172 END IF;
2173
2174 EXCEPTION
2175 WHEN fnd_api.g_exc_unexpected_error
2176 THEN
2177 x_return_status := fnd_api.g_ret_sts_unexp_error;
2178 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2179 WHEN OTHERS
2180 THEN
2181 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
2182 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
2183 x_return_status := fnd_api.g_ret_sts_unexp_error;
2184 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2185 END;
2186
2187 -----------------------------------------------------------------------------------------------------------
2188 -----------------------------------------------------------------------------------------------------------
2189 -----------------------------------------------------------------------------------------------------------
2190 -----------------------------------------------------------------------------------------------------------
2191 PROCEDURE create_task_recurrence (
2192 p_api_version IN NUMBER,
2193 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
2194 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
2195 p_task_id IN NUMBER,
2196 p_occurs_which IN INTEGER DEFAULT NULL,
2197 p_day_of_week IN INTEGER DEFAULT NULL,
2198 p_date_of_month IN INTEGER DEFAULT NULL,
2199 p_occurs_month IN INTEGER DEFAULT NULL,
2200 p_occurs_uom IN VARCHAR2 DEFAULT NULL,
2201 p_occurs_every IN INTEGER DEFAULT NULL,
2202 p_occurs_number IN INTEGER DEFAULT NULL,
2203 p_start_date_active IN DATE DEFAULT NULL,
2204 p_end_date_active IN DATE DEFAULT NULL,
2205 p_template_flag IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
2206 x_return_status OUT NOCOPY VARCHAR2,
2207 x_msg_count OUT NOCOPY NUMBER,
2208 x_msg_data OUT NOCOPY VARCHAR2,
2209 x_recurrence_rule_id OUT NOCOPY NUMBER,
2210 x_task_rec OUT NOCOPY jtf_task_recurrences_pub.task_details_rec,
2211 x_output_dates_counter OUT NOCOPY INTEGER,
2212 p_attribute1 IN VARCHAR2 DEFAULT null ,
2213 p_attribute2 IN VARCHAR2 DEFAULT null ,
2214 p_attribute3 IN VARCHAR2 DEFAULT null ,
2215 p_attribute4 IN VARCHAR2 DEFAULT null ,
2216 p_attribute5 IN VARCHAR2 DEFAULT null ,
2217 p_attribute6 IN VARCHAR2 DEFAULT null ,
2218 p_attribute7 IN VARCHAR2 DEFAULT null ,
2219 p_attribute8 IN VARCHAR2 DEFAULT null ,
2220 p_attribute9 IN VARCHAR2 DEFAULT null ,
2221 p_attribute10 IN VARCHAR2 DEFAULT null ,
2222 p_attribute11 IN VARCHAR2 DEFAULT null ,
2223 p_attribute12 IN VARCHAR2 DEFAULT null ,
2224 p_attribute13 IN VARCHAR2 DEFAULT null ,
2225 p_attribute14 IN VARCHAR2 DEFAULT null ,
2226 p_attribute15 IN VARCHAR2 DEFAULT null ,
2227 p_attribute_category IN VARCHAR2 DEFAULT null ,
2228 p_sunday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
2229 p_monday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
2230 p_tuesday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
2231 p_wednesday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
2232 p_thursday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
2233 p_friday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
2234 p_saturday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char
2235 )
2236 IS
2237 l_output_dates_tbl jtf_task_recurrences_pvt.output_dates_rec;
2238 l_recur_task_id NUMBER;
2239 l_api_version CONSTANT NUMBER := 1.0;
2240 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_TASK_RECURRENCE';
2241 l_task_id jtf_tasks_b.task_id%TYPE := p_task_id;
2242 l_recur_id jtf_task_recur_rules.recurrence_rule_id%TYPE;
2243 l_rowid ROWID;
2244 x CHAR;
2245 l_date_selected VARCHAR(1);
2246 l_planned_start_date date ;
2247 l_planned_end_date date ;
2248 l_scheduled_start_date date ;
2249 l_scheduled_end_date date ;
2250 l_actual_start_date date ;
2251 l_actual_end_date date ;
2252 l_ovn NUMBER;
2253 l_repeat_start_date date;
2254 l_repeat_end_date date;
2255 l_last binary_integer; -- Fix bug 2376554
2256 l_calendar_start_date DATE; -- Fix bug 2376554
2257 l_calendar_end_date DATE; -- Fix bug 2376554
2258 l_valid BOOLEAN := FALSE; -- Fix bug 2376554
2259 l_current DATE;
2260
2261 CURSOR c_jtf_task_recur (
2262 l_rowid IN ROWID
2263 )
2264 IS
2265 SELECT 1
2266 FROM jtf_task_recur_rules
2267 WHERE ROWID = l_rowid;
2268
2269 i NUMBER;
2270
2271 CURSOR c_task_details
2272 IS
2273 SELECT planned_start_date
2274 , planned_end_date
2275 , scheduled_start_date
2276 , scheduled_end_date
2277 , actual_start_date
2278 , actual_end_date
2279 , task_status_id
2280 , creation_date
2281 FROM jtf_tasks_b
2282 WHERE task_id = p_task_id;
2283
2284 /* Start of addition by lokumar for bug#6067036 */
2285 cursor c_task_planned_effort (p_task_id number)
2286 is
2287 select planned_effort,
2288 planned_effort_uom
2289 from jtf_tasks_b
2290 where task_id=p_task_id;
2291
2292 v_task_planned_effort c_task_planned_effort%rowtype;
2293
2294 cursor c_assign_actual_dtls (p_task_id number)
2295 is
2296 select task_assignment_id,
2297 actual_start_date,
2298 actual_end_date,
2299 actual_travel_duration,
2300 actual_travel_duration_uom,
2301 actual_effort,
2302 actual_effort_uom
2303 from jtf_task_all_assignments
2304 where task_id = p_task_id;
2305
2306 l_booking_start_date Date;
2307 l_booking_end_date Date;
2308
2309 /* End of additon lokumar for bug#6067036 */
2310
2311
2312 l_task_details c_task_details%rowtype;
2313
2314 BEGIN
2315 IF jtf_task_recurrences_pub.creating_recurrences
2316 THEN
2317 RETURN;
2318 END IF;
2319
2320 jtf_task_recurrences_pub.creating_recurrences := TRUE;
2321
2322 SAVEPOINT create_task_recur_pvt;
2323 x_return_status := fnd_api.g_ret_sts_success;
2324
2325 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
2326 THEN
2327 RAISE fnd_api.g_exc_unexpected_error;
2328 END IF;
2329
2330 IF fnd_api.to_boolean (p_init_msg_list)
2331 THEN
2332 fnd_msg_pub.initialize;
2333 END IF;
2334
2335 --- check if the given task id has already the recur
2336 --- checking if the task already has a recurrence
2337 IF jtf_task_utl.to_boolean (p_template_flag)
2338 THEN
2339
2340
2341 SELECT recurrence_rule_id
2342 INTO l_recur_id
2343 FROM jtf_task_templates_b
2344 WHERE task_template_id = l_task_id;
2345
2346 IF l_recur_id IS NOT NULL
2347 THEN
2348
2349 x_return_status := fnd_api.g_ret_sts_error;
2350 fnd_message.set_name ('JTF', 'JTF_TASK_RECURS_TEMP_ALREADY');
2351 fnd_msg_pub.add;
2352 RAISE fnd_api.g_exc_unexpected_error;
2353 END IF;
2354 ELSE
2355 SELECT recurrence_rule_id
2356 INTO l_recur_id
2357 FROM jtf_tasks_b
2358 WHERE task_id = l_task_id;
2359 IF l_recur_id IS NOT NULL
2360 THEN
2361 x_return_status := fnd_api.g_ret_sts_error;
2362 fnd_message.set_name ('JTF', 'JTF_TASK_RECURS_TASK_ALREADY');
2363 fnd_msg_pub.add;
2364 RAISE fnd_api.g_exc_unexpected_error;
2365 END IF;
2366 END IF;
2367
2368 jtf_task_recurrences_pvt.validate_task_recurrence (
2369 p_occurs_which => p_occurs_which,
2370 p_day_of_week => p_day_of_week,
2371 p_date_of_month => p_date_of_month,
2372 p_occurs_month => p_occurs_month,
2373 p_occurs_uom => p_occurs_uom,
2374 p_occurs_every => p_occurs_every,
2375 p_occurs_number => p_occurs_number,
2376 p_start_date_active => p_start_date_active,
2377 p_end_date_active => p_end_date_active,
2378 x_msg_count => x_msg_count,
2379 x_msg_data => x_msg_data,
2380 x_return_status => x_return_status,
2381 p_sunday => p_sunday,
2382 p_monday => p_monday,
2383 p_tuesday => p_tuesday,
2384 p_wednesday => p_wednesday,
2385 p_thursday => p_thursday,
2386 p_friday => p_friday,
2387 p_saturday => p_saturday
2388 );
2389
2390 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
2391 THEN
2392
2393 x_return_status := fnd_api.g_ret_sts_unexp_error;
2394 RAISE fnd_api.g_exc_unexpected_error;
2395 END IF;
2396 --- Call the procedure to generate the dates
2397 IF not jtf_task_utl.to_boolean (p_template_flag)
2398 THEN
2399 jtf_task_recurrences_pvt.generate_dates (
2400 p_occurs_which => p_occurs_which,
2401 p_day_of_week => p_day_of_week,
2402 p_date_of_month => p_date_of_month,
2403 p_occurs_month => p_occurs_month,
2404 p_occurs_uom => p_occurs_uom,
2405 p_occurs_every => p_occurs_every,
2406 p_occurs_number => p_occurs_number,
2407 p_start_date => p_start_date_active,
2408 p_end_date => p_end_date_active,
2409 x_output_dates_tbl => l_output_dates_tbl,
2410 x_output_dates_counter => x_output_dates_counter,
2411 p_sunday => p_sunday,
2412 p_monday => p_monday,
2413 p_tuesday => p_tuesday,
2414 p_wednesday => p_wednesday,
2415 p_thursday => p_thursday,
2416 p_friday => p_friday,
2417 p_saturday => p_saturday,
2418 p_task_id => p_task_id
2419 );
2420
2421 END IF ;
2422 IF x_output_dates_counter > 1
2423 THEN
2424 x_output_dates_counter := x_output_dates_counter - 1;
2425 END IF;
2426
2427 i := 1;
2428
2429 --- To fix bug#2170817
2430 BEGIN
2431 SELECT date_selected
2432 INTO l_date_selected
2433 FROM jtf_tasks_b
2434 WHERE task_id = p_task_id;
2435 EXCEPTION WHEN OTHERS THEN
2436 l_date_selected := null;
2437 END;
2438
2439 SELECT jtf_task_recur_rules_s.nextval
2440 INTO l_recur_id
2441 FROM dual;
2442
2443 jtf_task_recur_rules_pkg.insert_row (
2444 x_rowid => l_rowid,
2445 x_recurrence_rule_id => l_recur_id,
2446 x_occurs_which => p_occurs_which,
2447 x_day_of_week => p_day_of_week,
2448 x_date_of_month => p_date_of_month,
2449 x_occurs_month => p_occurs_month,
2450 x_occurs_uom => p_occurs_uom,
2451 x_occurs_every => p_occurs_every,
2452 x_occurs_number => p_occurs_number,
2453 x_start_date_active => p_start_date_active,
2454 x_end_date_active => p_end_date_active,
2455 x_attribute1 => p_attribute1 ,
2456 x_attribute2 => p_attribute2 ,
2457 x_attribute3 => p_attribute3 ,
2458 x_attribute4 => p_attribute4 ,
2459 x_attribute5 => p_attribute5 ,
2460 x_attribute6 => p_attribute6 ,
2461 x_attribute7 => p_attribute7 ,
2462 x_attribute8 => p_attribute8 ,
2463 x_attribute9 => p_attribute9 ,
2464 x_attribute10 => p_attribute10 ,
2465 x_attribute11 => p_attribute11 ,
2466 x_attribute12 => p_attribute12 ,
2467 x_attribute13 => p_attribute13 ,
2468 x_attribute14 => p_attribute14 ,
2469 x_attribute15 => p_attribute15,
2470 x_attribute_category => p_attribute_category ,
2471 x_creation_date => SYSDATE,
2472 x_created_by => jtf_task_utl.created_by,
2473 x_last_update_date => SYSDATE,
2474 x_last_updated_by => jtf_task_utl.updated_by,
2475 x_last_update_login => fnd_global.login_id,
2476 x_sunday => p_sunday,
2477 x_monday => p_monday,
2478 x_tuesday => p_tuesday,
2479 x_wednesday => p_wednesday,
2480 x_thursday => p_thursday,
2481 x_friday => p_friday,
2482 x_saturday => p_saturday,
2483 x_date_selected => l_date_selected
2484 );
2485 OPEN c_jtf_task_recur (l_rowid);
2486 FETCH c_jtf_task_recur INTO x;
2487
2488 IF c_jtf_task_recur%NOTFOUND
2489 THEN
2490
2491 x_return_status := fnd_api.g_ret_sts_unexp_error;
2492 fnd_message.set_name ('JTF', 'ERROR_INSERTING_RECURRENCE');
2493 fnd_msg_pub.add;
2494 RAISE fnd_api.g_exc_unexpected_error;
2495 ELSE
2496 x_recurrence_rule_id := l_recur_id;
2497
2498 IF jtf_task_utl.to_boolean(p_template_flag)
2499 THEN
2500 UPDATE jtf_task_templates_b
2501 SET recurrence_rule_id = l_recur_id
2502 WHERE task_template_id = l_task_id;
2503 ELSE
2504 UPDATE jtf_tasks_b
2505 SET recurrence_rule_id = l_recur_id
2506 WHERE task_id = l_task_id;
2507
2508 END IF;
2509 IF SQL%NOTFOUND
2510 THEN
2511 x_return_status := fnd_api.g_ret_sts_unexp_error;
2512 fnd_message.set_name ('JTF', 'ERROR_UPDATING_TASK');
2513 fnd_msg_pub.add;
2514 RAISE fnd_api.g_exc_unexpected_error;
2515 END IF;
2516 END IF;
2517
2518
2519 OPEN c_task_details;
2520 FETCH c_task_details into l_task_details;
2521 IF c_task_details%NOTFOUND
2522 THEN
2523 CLOSE c_task_details;
2524 fnd_message.set_name('JTF','JTF_TASK_INVALID_TASK_ID');
2525 fnd_message.set_token('P_TASK_ID', p_task_id);
2526 fnd_msg_pub.add;
2527 RAISE fnd_api.g_exc_unexpected_error;
2528 END IF;
2529
2530 --- if it a template do nothing
2531 --- else call copy tasks to create the tasks.
2532 ---
2533
2534 IF NOT jtf_task_utl.to_boolean (p_template_flag)
2535 THEN
2536 i := 1;
2537 WHILE i <= x_output_dates_counter
2538 LOOP
2539 l_valid := FALSE; -- Fix bug 2376554
2540
2541
2542 IF l_date_selected = 'P' OR l_date_selected IS NULL
2543 THEN
2544
2545 IF ( l_task_details.planned_end_date IS NULL AND
2546 l_task_details.planned_start_date IS NOT NULL
2547 )
2548 THEN
2549 l_planned_start_date :=
2550 get_dst_corrected_date( TO_DATE(TO_CHAR(l_output_dates_tbl (i),'dd-mm-rrrr')||TO_CHAR (l_task_details.planned_start_date, 'hh24:mi:ss'), 'DD-MM-RRRRHH24:MI:SS')
2551 , l_task_details.planned_start_date
2552 );
2553
2554 l_planned_end_date := NULL ;
2555
2556 ELSIF ( l_task_details.planned_end_date IS NOT NULL AND
2557 l_task_details.planned_start_date IS NULL
2558 )
2559 THEN
2560 l_planned_end_date :=
2561 get_dst_corrected_date( TO_DATE(TO_CHAR(l_output_dates_tbl (i),'dd-mm-rrrr') || TO_CHAR (l_task_details.planned_end_date, 'hh24:mi:ss'), 'DD-MM-RRRRHH24:MI:SS')
2562 , l_task_details.planned_end_date
2563 );
2564 l_planned_start_date := null ;
2565
2566
2567 ELSIF ( l_task_details.planned_end_date IS NULL AND
2568 l_task_details.planned_start_date IS NULL
2569 )
2570 THEN
2571 l_planned_start_date := l_output_dates_tbl(i);
2572 l_planned_end_date := NULL ;
2573
2574 ELSIF ( l_task_details.planned_end_date IS NOT NULL AND
2575 l_task_details.planned_start_date IS NOT NULL
2576 )
2577 THEN
2578 l_planned_start_date :=
2579 get_dst_corrected_date(TO_DATE(TO_CHAR(l_output_dates_tbl (i),'dd-mm-rrrr') || TO_CHAR (l_task_details.planned_start_date, 'hh24:mi:ss'), 'DD-MM-RRRRHH24:MI:SS')
2580 , l_task_details.planned_start_date
2581 );
2582 l_planned_end_date := l_planned_start_date + (l_task_details.planned_end_date - l_task_details.planned_start_date);
2583 END IF;
2584
2585 ELSIF l_date_selected = 'S'
2586 THEN
2587 IF ( l_task_details.scheduled_end_date IS NULL AND
2588 l_task_details.scheduled_start_date IS NOT NULL
2589 )
2590 THEN
2591 l_scheduled_start_date :=
2592 get_dst_corrected_date(TO_DATE (TO_CHAR(l_output_dates_tbl (i),'dd-mm-rrrr')||TO_CHAR (l_task_details.scheduled_start_date, 'hh24:mi:ss'), 'dd-mm-rrrrhh24:mi:ss')
2593 ,l_task_details.scheduled_start_date
2594 );
2595 l_scheduled_end_date := Null ;
2596
2597 ELSIF ( l_task_details.scheduled_end_date IS NOT NULL AND
2598 l_task_details.scheduled_start_date IS NULL
2599 )
2600 THEN
2601 l_scheduled_end_date :=
2602 get_dst_corrected_date(TO_DATE (to_char(l_output_dates_tbl (i),'dd-mm-rrrr') || TO_CHAR (l_task_details.scheduled_end_date, 'hh24:mi:ss'), 'dd-mm-rrrrhh24:mi:ss')
2603 ,l_task_details.scheduled_end_date
2604 );
2605 l_scheduled_start_date := null ;
2606
2607 ELSIF ( l_task_details.scheduled_end_date IS NULL AND
2608 l_task_details.scheduled_start_date IS NULL
2609 )
2610 THEN
2611 l_scheduled_start_date := l_output_dates_tbl(i);
2612 l_scheduled_end_date := null ;
2613 ELSIF ( l_task_details.scheduled_end_date IS NOT NULL AND
2614 l_task_details.scheduled_start_date IS NOT NULL
2615 )
2616 THEN
2617 l_scheduled_start_date :=
2618 get_dst_corrected_date(TO_DATE (to_char(l_output_dates_tbl (i),'dd-mm-rrrr') || TO_CHAR (l_task_details.scheduled_start_date, 'hh24:mi:ss'), 'dd-mm-rrrrhh24:mi:ss')
2619 ,l_task_details.scheduled_start_date
2620 );
2621 l_scheduled_end_date := l_scheduled_start_date + (l_task_details.scheduled_end_date - l_task_details.scheduled_start_date);
2622 END IF;
2623
2624 ELSIF l_date_selected = 'A'
2625 THEN
2626 IF ( l_task_details.actual_end_date IS NULL AND
2627 l_task_details.actual_start_date IS NOT NULL
2628 )
2629 THEN
2630 l_actual_start_date :=
2631 get_dst_corrected_date(TO_DATE (to_char(l_output_dates_tbl (i),'dd-mm-rrrr')||TO_CHAR (l_task_details.actual_start_date, 'hh24:mi:ss'), 'dd-mm-rrrrhh24:mi:ss')
2632 ,l_task_details.actual_start_date
2633 );
2634 l_actual_end_date := Null ;
2635
2636 ELSIF ( l_task_details.actual_end_date IS NOT NULL AND
2637 l_task_details.actual_start_date IS NULL
2638 )
2639 THEN
2640 l_actual_end_date :=
2641 get_dst_corrected_date(TO_DATE (to_char(l_output_dates_tbl (i),'dd-mm-rrrr') || TO_CHAR (l_task_details.actual_end_date, 'hh24:mi:ss'), 'dd-mm-rrrrhh24:mi:ss')
2642 ,l_task_details.actual_end_date
2643 );
2644 l_actual_start_date := null ;
2645
2646 ELSIF ( l_task_details.actual_end_date IS NULL AND
2647 l_task_details.actual_start_date IS NULL
2648 )
2649 THEN
2650 l_actual_start_date := l_output_dates_tbl(i);
2651 l_actual_end_date := null ;
2652 ELSIF ( l_task_details.actual_end_date IS NOT NULL AND
2653 l_task_details.actual_start_date IS NOT NULL
2654 )
2655 THEN
2656 l_actual_start_date :=
2657 get_dst_corrected_date(TO_DATE (to_char(l_output_dates_tbl (i),'dd-mm-rrrr') || TO_CHAR (l_task_details.actual_start_date, 'hh24:mi:ss'), 'dd-mm-rrrrhh24:mi:ss')
2658 ,l_task_details.actual_start_date
2659 );
2660 l_actual_end_date := l_actual_start_date + (l_task_details.actual_end_date - l_task_details.actual_start_date);
2661 END IF;
2662
2663
2664 END IF;
2665
2666 IF l_date_selected = 'P' OR l_date_selected IS NULL
2667 THEN
2668
2669
2670 --change or add dates for scheduled date fields with the same pattern
2671 IF ( l_task_details.scheduled_end_date IS NULL
2672 AND l_task_details.scheduled_start_date IS NOT NULL)
2673 THEN
2674
2675 l_scheduled_start_date :=
2676 l_task_details.scheduled_start_date +(l_planned_start_date - l_task_details.planned_start_date);
2677
2678 l_scheduled_end_date := Null ;
2679 elsif ( l_task_details.scheduled_end_date IS NOT NULL
2680 AND l_task_details.scheduled_start_date IS NULL)
2681 THEN
2682
2683 l_scheduled_end_date :=
2684 l_task_details.scheduled_end_date +(l_planned_start_date - l_task_details.planned_start_date);
2685
2686 l_scheduled_start_date := Null ;
2687
2688 elsif
2689 ( l_task_details.scheduled_end_date IS NULL
2690 AND l_task_details.scheduled_start_date IS NULL)
2691 THEN
2692
2693 l_scheduled_start_date := null;
2694 l_scheduled_end_date := null ;
2695 elsif
2696 ( l_task_details.scheduled_end_date IS NOT NULL
2697 AND l_task_details.scheduled_start_date IS NOT NULL)
2698 THEN
2699
2700 l_scheduled_start_date :=
2701 l_task_details.scheduled_start_date + (l_planned_start_date - l_task_details.planned_start_date);
2702
2703 l_scheduled_end_date :=
2704 l_task_details.scheduled_end_date + (l_planned_start_date - l_task_details.planned_start_date);
2705 end if;-- for scheduled
2706
2707 --change or add dates for actual date fields with the same pattern
2708 IF ( l_task_details.actual_end_date IS NULL
2709 AND l_task_details.actual_start_date IS NOT NULL)
2710 THEN
2711
2712 l_actual_start_date :=
2713 l_task_details.actual_start_date + (l_planned_start_date - l_task_details.planned_start_date);
2714
2715 l_actual_end_date := Null ;
2716
2717 elsif ( l_task_details.actual_end_date IS NOT NULL
2718 AND l_task_details.actual_start_date IS NULL)
2719 THEN
2720
2721 l_actual_end_date :=
2722 l_task_details.actual_end_date + (l_planned_start_date - l_task_details.planned_start_date);
2723
2724 l_actual_start_date := Null ;
2725
2726 elsif
2727 ( l_task_details.actual_end_date IS NULL
2728 AND l_task_details.actual_start_date IS NULL)
2729 THEN
2730
2731 l_actual_start_date := null;
2732 l_actual_end_date := null ;
2733 elsif
2734 ( l_task_details.actual_end_date IS NOT NULL
2735 AND l_task_details.actual_start_date IS NOT NULL)
2736 THEN
2737
2738 l_actual_start_date :=
2739 l_task_details.actual_start_date + (l_planned_start_date - l_task_details.planned_start_date);
2740
2741 l_actual_end_date :=
2742 l_task_details.actual_end_date + (l_planned_start_date - l_task_details.planned_start_date);
2743 end if;-- for actual
2744
2745 ELSIF l_date_selected = 'S'
2746 THEN
2747
2748 --change or add dates for planned date fields with the same pattern
2749 IF ( l_task_details.planned_end_date IS NULL
2750 AND l_task_details.planned_start_date IS NOT NULL)
2751 THEN
2752
2753 l_planned_start_date :=
2754 l_task_details.planned_start_date + (l_scheduled_start_date - l_task_details.scheduled_start_date);
2755
2756 l_planned_end_date := Null ;
2757
2758 elsif ( l_task_details.planned_end_date IS NOT NULL
2759 AND l_task_details.planned_start_date IS NULL)
2760 THEN
2761
2762 l_planned_end_date :=
2763 l_task_details.planned_end_date + (l_scheduled_start_date - l_task_details.scheduled_start_date);
2764
2765 l_planned_start_date := Null ;
2766
2767 elsif
2768 ( l_task_details.planned_end_date IS NULL
2769 AND l_task_details.planned_start_date IS NULL)
2770 THEN
2771
2772 l_planned_start_date := null;
2773 l_planned_end_date := null ;
2774 elsif
2775 ( l_task_details.planned_end_date IS NOT NULL
2776 AND l_task_details.planned_start_date IS NOT NULL)
2777 THEN
2778
2779 l_planned_start_date :=
2780 l_task_details.planned_start_date + (l_scheduled_start_date - l_task_details.scheduled_start_date);
2781
2782 l_planned_end_date :=
2783 l_task_details.planned_end_date + (l_scheduled_start_date - l_task_details.scheduled_start_date);
2784 end if;-- for planned
2785
2786 --change or add dates for actual date fields with the same pattern
2787 IF ( l_task_details.actual_end_date IS NULL
2788 AND l_task_details.actual_start_date IS NOT NULL)
2789 THEN
2790
2791 l_actual_start_date :=
2792 l_task_details.actual_start_date + (l_scheduled_start_date - l_task_details.scheduled_start_date);
2793
2794 l_actual_end_date := Null ;
2795
2796 elsif ( l_task_details.actual_end_date IS NOT NULL
2797 AND l_task_details.actual_start_date IS NULL)
2798 THEN
2799
2800 l_actual_end_date :=
2801 l_task_details.actual_end_date + (l_scheduled_start_date - l_task_details.scheduled_start_date);
2802
2803 l_actual_start_date := Null ;
2804
2805 elsif
2806 ( l_task_details.actual_end_date IS NULL
2807 AND l_task_details.actual_start_date IS NULL)
2808 THEN
2809
2810 l_actual_start_date := null;
2811 l_actual_end_date := null ;
2812 elsif
2813 ( l_task_details.actual_end_date IS NOT NULL
2814 AND l_task_details.actual_start_date IS NOT NULL)
2815 THEN
2816
2817 l_actual_start_date :=
2818 l_task_details.actual_start_date + (l_scheduled_start_date - l_task_details.scheduled_start_date);
2819
2820 l_actual_end_date :=
2821 l_task_details.actual_end_date + (l_scheduled_start_date - l_task_details.scheduled_start_date);
2822 end if;-- for actual
2823
2824 ELSIF l_date_selected = 'A'
2825 THEN
2826
2827 --change or add dates for planned date fields with the same pattern
2828 IF ( l_task_details.planned_end_date IS NULL
2829 AND l_task_details.planned_start_date IS NOT NULL)
2830 THEN
2831
2832 l_planned_start_date :=
2833 l_task_details.planned_start_date + (l_actual_start_date - l_task_details.actual_start_date);
2834
2835 l_planned_end_date := Null ;
2836
2837 elsif ( l_task_details.planned_end_date IS NOT NULL
2838 AND l_task_details.planned_start_date IS NULL)
2839 THEN
2840
2841 l_planned_end_date :=
2842 l_task_details.planned_end_date + (l_actual_start_date - l_task_details.actual_start_date);
2843
2844 l_planned_start_date := Null ;
2845
2846 elsif
2847 ( l_task_details.planned_end_date IS NULL
2848 AND l_task_details.planned_start_date IS NULL)
2849 THEN
2850
2851 l_planned_start_date := null;
2852 l_planned_end_date := null ;
2853 elsif
2854 ( l_task_details.planned_end_date IS NOT NULL
2855 AND l_task_details.planned_start_date IS NOT NULL)
2856 THEN
2857
2858 l_planned_start_date :=
2859 l_task_details.planned_start_date + (l_actual_start_date - l_task_details.actual_start_date);
2860
2861 l_planned_end_date :=
2862 l_task_details.planned_end_date + (l_actual_start_date - l_task_details.actual_start_date);
2863 end if;-- for planned
2864
2865 --change or add dates for scheduled date fields with the same pattern
2866 IF ( l_task_details.scheduled_end_date IS NULL
2867 AND l_task_details.scheduled_start_date IS NOT NULL)
2868 THEN
2869
2870 l_scheduled_start_date :=
2871 l_task_details.scheduled_start_date + (l_actual_start_date - l_task_details.actual_start_date);
2872
2873 l_scheduled_end_date := Null ;
2874
2875 elsif ( l_task_details.scheduled_end_date IS NOT NULL
2876 AND l_task_details.scheduled_start_date IS NULL)
2877 THEN
2878
2879 l_scheduled_end_date :=
2880 l_task_details.scheduled_end_date + (l_actual_start_date - l_task_details.actual_start_date);
2881
2882 l_scheduled_start_date := Null ;
2883
2884 elsif
2885 ( l_task_details.scheduled_end_date IS NULL
2886 AND l_task_details.scheduled_start_date IS NULL)
2887 THEN
2888
2889 l_scheduled_start_date := null;
2890 l_scheduled_end_date := null ;
2891 elsif
2892 ( l_task_details.scheduled_end_date IS NOT NULL
2893 AND l_task_details.scheduled_start_date IS NOT NULL)
2894 THEN
2895
2896 l_scheduled_start_date :=
2897 l_task_details.scheduled_start_date + (l_actual_start_date - l_task_details.actual_start_date);
2898
2899 l_scheduled_end_date :=
2900 l_task_details.scheduled_end_date + (l_actual_start_date - l_task_details.actual_start_date);
2901 end if;-- for scheduled
2902 ELSIF l_date_selected = 'D'
2903 THEN
2904 l_planned_start_date := NULL;
2905 l_planned_end_date := NULL;
2906 l_scheduled_start_date := NULL;
2907 l_scheduled_end_date := NULL;
2908 l_actual_start_date := NULL;
2909 l_actual_end_date := NULL;
2910 IF l_task_details.planned_start_date IS NOT NULL
2911 THEN
2912 l_planned_start_date := get_dst_corrected_date( TO_DATE(TO_CHAR(l_output_dates_tbl (i),'dd-mm-rrrr')||TO_CHAR (l_task_details.planned_start_date, 'hh24:mi:ss'), 'DD-MM-RRRRHH24:MI:SS')
2913 , l_task_details.planned_start_date
2914 );
2915 END IF;
2916
2917 IF l_task_details.planned_end_date IS NOT NULL
2918 THEN
2919 l_planned_end_date := get_dst_corrected_date( TO_DATE(TO_CHAR(l_output_dates_tbl (i),'dd-mm-rrrr')||TO_CHAR (l_task_details.planned_end_date, 'hh24:mi:ss'), 'DD-MM-RRRRHH24:MI:SS')
2920 , l_task_details.planned_end_date
2921 );
2922 END IF;
2923
2924 IF l_task_details.scheduled_start_date IS NOT NULL
2925 THEN
2926 l_scheduled_start_date := get_dst_corrected_date( TO_DATE(TO_CHAR(l_output_dates_tbl (i),'dd-mm-rrrr')||TO_CHAR (l_task_details.scheduled_start_date, 'hh24:mi:ss'), 'DD-MM-RRRRHH24:MI:SS')
2927 , l_task_details.scheduled_start_date
2928 );
2929 END IF;
2930
2931 IF l_task_details.scheduled_end_date IS NOT NULL
2932 THEN
2933 l_scheduled_end_date := get_dst_corrected_date( TO_DATE(TO_CHAR(l_output_dates_tbl (i),'dd-mm-rrrr')||TO_CHAR (l_task_details.scheduled_end_date, 'hh24:mi:ss'), 'DD-MM-RRRRHH24:MI:SS')
2934 , l_task_details.scheduled_end_date
2935 );
2936 END IF;
2937
2938 IF l_task_details.actual_start_date IS NOT NULL
2939 THEN
2940 l_actual_start_date := get_dst_corrected_date( TO_DATE(TO_CHAR(l_output_dates_tbl (i),'dd-mm-rrrr')||TO_CHAR (l_task_details.actual_start_date, 'hh24:mi:ss'), 'DD-MM-RRRRHH24:MI:SS')
2941 , l_task_details.actual_start_date
2942 );
2943 END IF;
2944
2945 IF l_task_details.actual_end_date IS NOT NULL
2946 THEN
2947 l_actual_end_date := get_dst_corrected_date( TO_DATE(TO_CHAR(l_output_dates_tbl (i),'dd-mm-rrrr')||TO_CHAR (l_task_details.actual_end_date, 'hh24:mi:ss'), 'DD-MM-RRRRHH24:MI:SS')
2948 , l_task_details.actual_end_date
2949 );
2950 END IF;
2951 END IF;
2952
2953
2954
2955
2956
2957
2958
2959
2960 IF l_date_selected IS NULL -- Added by lokumar for bug#6067036
2961 THEN
2962 l_calendar_start_date := NULL;
2963 l_calendar_end_date := NULL;
2964 l_valid := TRUE;
2965 ELSIF l_date_selected = 'P' --OR l_date_selected IS NULL -- Commented out by lokumar for bug#6067036
2966 THEN
2967 if ((p_end_date_active is null or trunc(l_planned_start_date) <= trunc(p_end_date_active))
2968 --and trunc(p_start_date_active) <> trunc(l_planned_start_date) then
2969 and trunc(l_task_details.planned_start_date) <> trunc(l_planned_start_date))
2970 OR l_date_selected IS NULL -- Fix bug 2376554
2971 then
2972 l_calendar_start_date := l_planned_start_date; -- Fix bug 2376554
2973 l_calendar_end_date := l_planned_end_date; -- Fix bug 2376554
2974 l_valid := TRUE; -- Fix bug 2376554
2975 end if;
2976 ELSIF l_date_selected = 'S'
2977 THEN
2978 if (p_end_date_active is null or trunc(l_scheduled_start_date) <= trunc(p_end_date_active))
2979 --and trunc(p_start_date_active) <> trunc(l_scheduled_start_date) then
2980 and trunc(l_task_details.scheduled_start_date) <> trunc(l_scheduled_start_date) then
2981 l_calendar_start_date := l_scheduled_start_date; -- Fix bug 2376554
2982 l_calendar_end_date := l_scheduled_end_date; -- Fix bug 2376554
2983 l_valid := TRUE; -- Fix bug 2376554
2984 end if;
2985 ELSIF l_date_selected = 'A'
2986 THEN
2987 if (p_end_date_active is null or trunc(l_actual_start_date) <= trunc(p_end_date_active))
2988 --and trunc(p_start_date_active) <> trunc(l_actual_start_date) then
2989 and trunc(l_task_details.actual_start_date) <> trunc(l_actual_start_date) then
2990 l_calendar_start_date := l_actual_start_date; -- Fix bug 2376554
2991 l_calendar_end_date := l_actual_end_date; -- Fix bug 2376554
2992 l_valid := TRUE; -- Fix bug 2376554
2993 end if;
2994 ELSIF l_date_selected = 'D'
2995 THEN
2996 jtf_task_utl_ext.set_start_n_due_date (
2997 p_task_status_id => l_task_details.task_status_id
2998 , p_planned_start_date => l_planned_start_date
2999 , p_planned_end_date => l_planned_end_date
3000 , p_scheduled_start_date => l_scheduled_start_date
3001 , p_scheduled_end_date => l_scheduled_end_date
3002 , p_actual_start_date => l_actual_start_date
3003 , p_actual_end_date => l_actual_end_date
3004 , p_creation_date => l_task_details.creation_date
3005 , x_calendar_start_date => l_calendar_start_date
3006 , x_calendar_end_date => l_calendar_end_date
3007 , x_return_status => x_return_status);
3008
3009 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
3010 THEN
3011 RAISE fnd_api.g_exc_unexpected_error;
3012 END IF;
3013 l_valid := TRUE;
3014 END IF;
3015
3016 -------------------------
3017 -- Fix bug 2376554
3018 -------------------------
3019 IF l_valid
3020 THEN
3021 l_current := SYSDATE;
3022
3023 IF i <> 1
3024 THEN
3025 jtf_tasks_pub.copy_task (
3026 p_api_version => 1.0,
3027 p_init_msg_list => fnd_api.g_false,
3028 p_commit => fnd_api.g_true,
3029 p_source_task_id => l_task_id,
3030 p_copy_task_assignments => fnd_api.g_true,
3031 p_copy_task_rsc_reqs => fnd_api.g_true,
3032 p_copy_task_depends => fnd_api.g_true,
3033 p_create_recurrences => fnd_api.g_false,
3034 p_copy_task_references => fnd_api.g_true,
3035 p_copy_task_dates => fnd_api.g_true,
3036 p_copy_task_contacts => fnd_api.g_true,
3037 p_copy_task_contact_points => fnd_api.g_true,
3038 x_return_status => x_return_status,
3039 x_msg_count => x_msg_count,
3040 x_msg_data => x_msg_data,
3041 x_task_id => l_recur_task_id
3042 );
3043
3044 UPDATE jtf_tasks_b
3045 SET recurrence_rule_id = l_recur_id,
3046 planned_start_date = l_planned_start_date ,
3047 planned_end_date = l_planned_end_date,
3048 scheduled_start_date = l_scheduled_start_date ,
3049 scheduled_end_date = l_scheduled_end_date,
3050 actual_start_date = l_actual_start_date ,
3051 actual_end_date = l_actual_end_date,
3052 calendar_start_date = l_calendar_start_date ,
3053 calendar_end_date = l_calendar_end_date,
3054 creation_date = l_current,
3055 last_update_date = l_current,
3056 date_selected = l_date_selected -- Added by lokumar for bug#6067036
3057 WHERE task_id = l_recur_task_id ;
3058 ELSE
3059 UPDATE jtf_tasks_b
3060 SET recurrence_rule_id = l_recur_id,
3061 planned_start_date = l_planned_start_date ,
3062 planned_end_date = l_planned_end_date,
3063 scheduled_start_date = l_scheduled_start_date ,
3064 scheduled_end_date = l_scheduled_end_date,
3065 actual_start_date = l_actual_start_date ,
3066 actual_end_date = l_actual_end_date,
3067 calendar_start_date = l_calendar_start_date ,
3068 calendar_end_date = l_calendar_end_date,
3069 creation_date = l_current,
3070 last_update_date = l_current,
3071 date_selected = l_date_selected -- Added by lokumar for bug#6067036
3072 WHERE task_id = l_task_id ;
3073 END IF;
3074
3075 /* Start of addition by lokumar for bug#6067036 */
3076
3077 OPEN c_task_planned_effort(NVL(l_recur_task_id,l_task_id));
3078 FETCH c_task_planned_effort INTO v_task_planned_effort;
3079 CLOSE c_task_planned_effort;
3080
3081 FOR i IN c_assign_actual_dtls(NVL(l_recur_task_id,l_task_id))
3082 LOOP
3083 jtf_task_assignments_pvt.populate_booking_dates
3084 (
3085 p_calendar_start_date => l_calendar_start_date,
3086 p_calendar_end_date => l_calendar_end_date,
3087 p_actual_start_date => i.actual_start_date,
3088 p_actual_end_date => i.actual_end_date,
3089 p_actual_travel_duration => i.actual_travel_duration,
3090 p_actual_travel_duration_uom => i.actual_travel_duration_uom,
3091 p_planned_effort => v_task_planned_effort.planned_effort,
3092 p_planned_effort_uom => v_task_planned_effort.planned_effort_uom,
3093 p_actual_effort => i.actual_effort,
3094 p_actual_effort_uom => i.actual_effort_uom,
3095 x_booking_start_date => l_booking_start_date,
3096 x_booking_end_date => l_booking_end_date
3097 );
3098
3099 UPDATE jtf_task_all_assignments
3100 SET booking_start_date = l_booking_start_date,
3101 booking_end_date = l_booking_end_date
3102 WHERE task_assignment_id = i.task_assignment_id;
3103 END LOOP;
3104
3105 /* End of addition by lokumar for bug#6067036 */
3106
3107
3108 END IF;
3109 ---------------------------------------------------
3110
3111 jtf_task_recurrences_pub.creating_recurrences := FALSE;
3112 i := i + 1;
3113 END LOOP;
3114 END IF;
3115 close c_task_details;
3116 jtf_task_recurrences_pub.creating_recurrences := FALSE;
3117 IF not jtf_task_utl.to_boolean (p_template_flag) AND -- Fix bug 2395216
3118 NOT original_date_meets_criteria(p_output_dates_tbl => l_output_dates_tbl ,
3119 p_start_date_active => p_start_date_active) and
3120 p_occurs_uom <> 'DAY'
3121 THEN
3122 -- Fix bug 2376554
3123 --l_ovn := get_ovn (p_task_id => p_task_id);
3124 --jtf_tasks_pvt.delete_task (
3125 -- p_api_version => 1.0,
3126 -- p_init_msg_list => fnd_api.g_true,
3127 -- p_commit => fnd_api.g_false,
3128 -- p_task_id => p_task_id,
3129 -- p_object_version_number => l_ovn,
3130 -- x_return_status => x_return_status,
3131 -- x_msg_count => x_msg_count,
3132 -- x_msg_data => x_msg_data
3133 --);
3134 get_repeat_start_date(
3135 p_recurrence_rule_id => l_recur_id
3136 ,x_repeat_start_date => l_repeat_start_date
3137 );
3138
3139 UPDATE jtf_task_recur_rules
3140 SET start_date_active = TRUNC(NVL(l_repeat_start_date, l_output_dates_tbl(1))) -- Fix bug 2376554, bug 2385202
3141 WHERE recurrence_rule_id = l_recur_id;
3142 END IF;
3143
3144 IF not jtf_task_utl.to_boolean (p_template_flag) AND -- Fix bug 2395216
3145 p_end_date_active IS NULL
3146 THEN
3147 l_last := l_output_dates_tbl.LAST; -- Fix bug 2376554
3148
3149 -- Commented out by SBARAT on 28/07/2005 for bug# 4365923
3150 /*jtf_task_recurrences_pvt.get_repeat_end_date(
3151 p_recurrence_rule_id => l_recur_id
3152 ,x_repeat_end_date => l_repeat_end_date
3153 );*/
3154
3155 -- Modified by SBARAT on 28/07/2005 for bug# 4365923
3156 UPDATE jtf_task_recur_rules
3157 SET end_date_active = trunc(l_output_dates_tbl(l_last)) --trunc(NVL(l_repeat_end_date,l_output_dates_tbl(l_last))) -- Fix bug 2376554, bug 2385202
3158 WHERE recurrence_rule_id = l_recur_id;
3159 END IF;
3160
3161 set_last_update_date(l_recur_id);
3162
3163 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3164
3165 EXCEPTION
3166 WHEN fnd_api.g_exc_unexpected_error
3167 THEN
3168 jtf_task_recurrences_pub.creating_recurrences := FALSE;
3169
3170
3171 ROLLBACK TO create_task_recur_pvt;
3172 x_return_status := fnd_api.g_ret_sts_unexp_error;
3173 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3174 WHEN OTHERS
3175 THEN
3176 jtf_task_recurrences_pub.creating_recurrences := FALSE;
3177 ROLLBACK TO create_task_recur_pvt;
3178 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
3179 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
3180 fnd_msg_pub.add;
3181 x_return_status := fnd_api.g_ret_sts_unexp_error;
3182 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3183 END;
3184
3185 -- To fix bug 2721278
3186 FUNCTION is_repeat_start_date_changed (p_recurrence_rule_id IN NUMBER
3187 ,p_target_repeat_start_date IN DATE)
3188 RETURN BOOLEAN
3189 IS
3190 CURSOR c_recur IS
3191 SELECT 1
3192 FROM jtf_task_recur_rules
3193 WHERE recurrence_rule_id = p_recurrence_rule_id
3194 AND TRUNC(start_date_active) = TRUNC(p_target_repeat_start_date);
3195
3196 l_dummy NUMBER;
3197 l_changed BOOLEAN := TRUE;
3198 BEGIN
3199 OPEN c_recur;
3200 FETCH c_recur INTO l_dummy;
3201 IF c_recur%FOUND
3202 THEN
3203 l_changed := FALSE;
3204 END IF;
3205 CLOSE c_recur;
3206
3207 RETURN l_changed;
3208 END is_repeat_start_date_changed;
3209
3210 PROCEDURE update_task_recurrence (
3211 p_api_version IN NUMBER,
3212 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
3213 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
3214 p_task_id IN NUMBER,
3215 p_recurrence_rule_id IN NUMBER,
3216 p_occurs_which IN INTEGER DEFAULT NULL,
3217 p_day_of_week IN INTEGER DEFAULT NULL,
3218 p_date_of_month IN INTEGER DEFAULT NULL,
3219 p_occurs_month IN INTEGER DEFAULT NULL,
3220 p_occurs_uom IN VARCHAR2 DEFAULT NULL,
3221 p_occurs_every IN INTEGER DEFAULT NULL,
3222 p_occurs_number IN INTEGER DEFAULT NULL,
3223 p_start_date_active IN DATE DEFAULT NULL,
3224 p_end_date_active IN DATE DEFAULT NULL,
3225 p_template_flag IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
3226 p_attribute1 IN VARCHAR2 DEFAULT NULL ,
3227 p_attribute2 IN VARCHAR2 DEFAULT NULL ,
3228 p_attribute3 IN VARCHAR2 DEFAULT NULL ,
3229 p_attribute4 IN VARCHAR2 DEFAULT NULL ,
3230 p_attribute5 IN VARCHAR2 DEFAULT NULL ,
3231 p_attribute6 IN VARCHAR2 DEFAULT NULL ,
3232 p_attribute7 IN VARCHAR2 DEFAULT NULL ,
3233 p_attribute8 IN VARCHAR2 DEFAULT NULL ,
3234 p_attribute9 IN VARCHAR2 DEFAULT NULL ,
3235 p_attribute10 IN VARCHAR2 DEFAULT NULL ,
3236 p_attribute11 IN VARCHAR2 DEFAULT NULL ,
3237 p_attribute12 IN VARCHAR2 DEFAULT NULL ,
3238 p_attribute13 IN VARCHAR2 DEFAULT NULL ,
3239 p_attribute14 IN VARCHAR2 DEFAULT NULL ,
3240 p_attribute15 IN VARCHAR2 DEFAULT NULL ,
3241 p_attribute_category IN VARCHAR2 DEFAULT NULL ,
3242 p_sunday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
3243 p_monday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
3244 p_tuesday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
3245 p_wednesday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
3246 p_thursday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
3247 p_friday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
3248 p_saturday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
3249 x_new_recurrence_rule_id OUT NOCOPY NUMBER,
3250 x_return_status OUT NOCOPY VARCHAR2,
3251 x_msg_count OUT NOCOPY NUMBER,
3252 x_msg_data OUT NOCOPY VARCHAR2
3253 )
3254 IS
3255 CURSOR c_task (b_task_id NUMBER) IS
3256 SELECT MAX(t.calendar_start_date) calendar_start_date
3257 , COUNT(t.task_id) occurs_number
3258 FROM jtf_tasks_b t
3259 , jtf_tasks_b curr_task
3260 WHERE curr_task.task_id = b_task_id
3261 AND t.recurrence_rule_id = curr_task.recurrence_rule_id
3262 AND t.calendar_start_date < curr_task.calendar_start_date;
3263
3264 rec_task c_task%ROWTYPE;
3265
3266 CURSOR c_new_start_date (b_task_id NUMBER) IS
3267 SELECT TRUNC(calendar_start_date) calendar_start_date
3268 FROM jtf_tasks_b
3269 WHERE task_id = b_task_id;
3270
3271 rec_new_start_date c_new_start_date%ROWTYPE;
3272
3273 l_new_task_id NUMBER;
3274 l_first_task BOOLEAN := FALSE;
3275 l_delete_future_recurrences VARCHAR2(1);
3276 l_task_details_rec jtf_task_recurrences_pub.task_details_rec;
3277 l_output_dates_counter INTEGER;
3278 l_object_version_number NUMBER;
3279 i NUMBER;
3280 -- To fix bug 2721278
3281 l_repeat_start_date DATE;
3282 BEGIN
3283 SAVEPOINT update_task_recurrence_pvt;
3284
3285 x_return_status := fnd_api.g_ret_sts_success;
3286
3287 IF fnd_api.to_boolean (p_init_msg_list)
3288 THEN
3289 fnd_msg_pub.initialize;
3290 END IF;
3291
3292 OPEN c_new_start_date (p_task_id);
3293 FETCH c_new_start_date INTO rec_new_start_date;
3294
3295 IF c_new_start_date%NOTFOUND
3296 THEN
3297 CLOSE c_new_start_date;
3298 fnd_message.set_name('JTF','JTF_TASK_INVALID_TASK_ID');
3299 fnd_message.set_token('P_TASK_ID', p_task_id);
3300 fnd_msg_pub.add;
3301 x_return_status := fnd_api.g_ret_sts_unexp_error;
3302 RAISE fnd_api.g_exc_unexpected_error;
3303 END IF;
3304 CLOSE c_new_start_date;
3305
3306 -- To fix bug 2721278
3307 -- Check if the repeat start date is changed or not.
3308 -- If it's changed, use the new repeating start date
3309 -- Otherwise, use the current selected task's calendar start date
3310 IF is_repeat_start_date_changed (p_recurrence_rule_id => p_recurrence_rule_id
3311 ,p_target_repeat_start_date => p_start_date_active)
3312 THEN
3313 l_repeat_start_date := p_start_date_active;
3314 ELSE
3315 l_repeat_start_date := rec_new_start_date.calendar_start_date;
3316 END IF;
3317 -----------------------------------------------------------------------------
3318
3319 ---------------------------------------------
3320 -- Copy p_task_id to a new one
3321 -- Store the new task_id into l_new_task_id
3322 ---------------------------------------------
3323 jtf_tasks_pub.copy_task (
3324 p_api_version => 1.0,
3325 p_init_msg_list => fnd_api.g_true,
3326 p_commit => fnd_api.g_false,
3327 p_source_task_id => p_task_id,
3328 p_copy_task_assignments => fnd_api.g_true,
3329 p_copy_task_rsc_reqs => fnd_api.g_true,
3330 p_copy_task_depends => fnd_api.g_true,
3331 p_create_recurrences => fnd_api.g_false,
3332 p_copy_task_references => fnd_api.g_true,
3333 p_copy_task_dates => fnd_api.g_true,
3334 p_copy_task_contacts => fnd_api.g_true,
3335 p_copy_task_contact_points => fnd_api.g_true,
3336 x_return_status => x_return_status,
3337 x_msg_count => x_msg_count,
3338 x_msg_data => x_msg_data,
3339 x_task_id => l_new_task_id
3340 );
3341 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
3342 THEN
3343 x_return_status := fnd_api.g_ret_sts_unexp_error;
3344 RAISE fnd_api.g_exc_unexpected_error;
3345 END IF;
3346
3347 ---------------------------------------------
3348 -- Change the status of all assignees to 18
3349 -- for the copied
3350 ---------------------------------------------
3351 UPDATE jtf_task_all_assignments
3352 SET assignment_status_id = 18
3353 , last_update_date = SYSDATE
3354 , last_updated_by = fnd_global.user_id
3355 WHERE task_id = l_new_task_id
3356 AND assignee_role = 'ASSIGNEE';
3357
3358 --------------------------------------------------
3359 -- Check if p_task_id equals to the first task id
3360 --------------------------------------------------
3361 l_first_task := jta_sync_task_utl.is_this_first_task(p_task_id => p_task_id);
3362
3363 IF l_first_task
3364 THEN
3365 l_delete_future_recurrences := 'A'; -- Delete all the occurrences
3366 ELSE
3367 l_delete_future_recurrences := fnd_api.g_true; -- Delete the future occrrences
3368 END IF;
3369
3370 --------------------------------------------------
3371 -- Delete all the appointments
3372 -- with given option l_delete_future_recurrences
3373 --------------------------------------------------
3374 l_object_version_number := jta_sync_task_common.get_ovn(p_task_id => p_task_id);
3375
3376 jtf_tasks_pvt.delete_task (
3377 p_api_version => 1.0,
3378 p_init_msg_list => fnd_api.g_false,
3379 p_commit => fnd_api.g_false,
3380 p_object_version_number => l_object_version_number,
3381 p_task_id => p_task_id,
3382 p_delete_future_recurrences => l_delete_future_recurrences,
3383 x_return_status => x_return_status,
3384 x_msg_count => x_msg_count,
3385 x_msg_data => x_msg_data
3386 );
3387 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
3388 THEN
3389 x_return_status := fnd_api.g_ret_sts_unexp_error;
3390 RAISE fnd_api.g_exc_unexpected_error;
3391 END IF;
3392
3393 ---------------------------------------------------------------------
3394 -- Update end_date_active and occurs_number for the current
3395 -- recurrence rule with the calendar_start_date of the task
3396 -- and the number of tasks right before the current selected task
3397 ---------------------------------------------------------------------
3398 IF NOT l_first_task
3399 THEN
3400 OPEN c_task (p_task_id);
3401 FETCH c_task INTO rec_task;
3402 IF c_task%NOTFOUND
3403 THEN
3404 CLOSE c_task;
3405 fnd_message.set_name('JTF','JTF_TASK_INVALID_TASK_ID');
3406 fnd_message.set_token('P_TASK_ID', p_task_id);
3407 fnd_msg_pub.add;
3408 x_return_status := fnd_api.g_ret_sts_unexp_error;
3409 RAISE fnd_api.g_exc_unexpected_error;
3410 END IF;
3411 CLOSE c_task;
3412
3413 UPDATE jtf_task_recur_rules
3414 SET end_date_active = TRUNC(rec_task.calendar_start_date)
3415 , occurs_number = rec_task.occurs_number
3416 WHERE recurrence_rule_id = p_recurrence_rule_id;
3417 END IF;
3418
3419 --------------------------------------------------
3420 -- Create a new recurrence with l_new_task_id
3421 --------------------------------------------------
3422 create_task_recurrence (
3423 p_api_version => 1.0,
3424 p_init_msg_list => fnd_api.g_false,
3425 p_commit => fnd_api.g_false,
3426 p_task_id => l_new_task_id,
3427 p_occurs_which => p_occurs_which,
3428 p_day_of_week => p_day_of_week,
3429 p_date_of_month => p_date_of_month,
3430 p_occurs_month => p_occurs_month,
3431 p_occurs_uom => p_occurs_uom,
3432 p_occurs_every => p_occurs_every,
3433 p_occurs_number => p_occurs_number,
3434 p_start_date_active => l_repeat_start_date, -- To fix bug 2721278
3435 p_end_date_active => p_end_date_active,
3436 p_template_flag => p_template_flag,
3437 x_return_status => x_return_status,
3438 x_msg_count => x_msg_count,
3439 x_msg_data => x_msg_data,
3440 x_recurrence_rule_id => x_new_recurrence_rule_id,
3441 x_task_rec => l_task_details_rec,
3442 x_output_dates_counter => l_output_dates_counter,
3443 p_attribute1 => p_attribute1,
3444 p_attribute2 => p_attribute2,
3445 p_attribute3 => p_attribute3,
3446 p_attribute4 => p_attribute4,
3447 p_attribute5 => p_attribute5,
3448 p_attribute6 => p_attribute6,
3449 p_attribute7 => p_attribute7,
3450 p_attribute8 => p_attribute8,
3451 p_attribute9 => p_attribute9,
3452 p_attribute10 => p_attribute10,
3453 p_attribute11 => p_attribute11,
3454 p_attribute12 => p_attribute12,
3455 p_attribute13 => p_attribute13,
3456 p_attribute14 => p_attribute14,
3457 p_attribute15 => p_attribute15,
3458 p_attribute_category => p_attribute_category,
3459 p_sunday => p_sunday,
3460 p_monday => p_monday,
3461 p_tuesday => p_tuesday,
3462 p_wednesday => p_wednesday,
3463 p_thursday => p_thursday,
3464 p_friday => p_friday,
3465 p_saturday => p_saturday
3466 );
3467 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
3468 THEN
3469 x_return_status := fnd_api.g_ret_sts_unexp_error;
3470 RAISE fnd_api.g_exc_unexpected_error;
3471 END IF;
3472
3473 ------------------------------------------------------------
3474 -- Update sync mapping table if this task is the first one
3475 ------------------------------------------------------------
3476 IF l_first_task
3477 THEN
3478 UPDATE jta_sync_task_mapping
3479 SET task_id = l_new_task_id
3480 WHERE task_id = p_task_id;
3481 END IF;
3482
3483 IF fnd_api.to_boolean (p_commit)
3484 THEN
3485 COMMIT WORK;
3486 END IF;
3487
3488 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3489 EXCEPTION
3490 WHEN fnd_api.g_exc_unexpected_error
3491 THEN
3492 ROLLBACK TO update_task_recurrence_pvt;
3493 x_return_status := fnd_api.g_ret_sts_unexp_error;
3494 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3495 WHEN OTHERS
3496 THEN
3497 ROLLBACK TO update_task_recurrence_pvt;
3498 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
3499 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
3500 fnd_msg_pub.add;
3501 x_return_status := fnd_api.g_ret_sts_unexp_error;
3502 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3503 END update_task_recurrence;
3504
3505 END; --CREATE OR REPLACE PACKAGE