[Home] [Help]
PACKAGE BODY: APPS.JTF_TASK_RECURRENCES_PVT
Source
1 PACKAGE BODY jtf_task_recurrences_pvt AS
2 /* $Header: jtfvtkub.pls 120.15 2010/06/08 09:36:08 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 ,source_object_type_code
2282 FROM jtf_tasks_b
2283 WHERE task_id = p_task_id;
2284
2285 /* Start of addition by lokumar for bug#6067036 */
2286 cursor c_task_planned_effort (p_task_id number)
2287 is
2288 select planned_effort,
2289 planned_effort_uom
2290 from jtf_tasks_b
2291 where task_id=p_task_id;
2292
2293 v_task_planned_effort c_task_planned_effort%rowtype;
2294
2295 cursor c_assign_actual_dtls (p_task_id number)
2296 is
2297 select task_assignment_id,
2298 actual_start_date,
2299 actual_end_date,
2300 actual_travel_duration,
2301 actual_travel_duration_uom,
2302 actual_effort,
2303 actual_effort_uom,
2304 resource_type_code,
2305 resource_id
2306 from jtf_task_all_assignments
2307 where task_id = p_task_id;
2308
2309 l_booking_start_date Date;
2310 l_booking_end_date Date;
2311
2312 /* End of additon lokumar for bug#6067036 */
2313
2314
2315 l_task_details c_task_details%rowtype;
2316 l_name VARCHAR2(200);
2317 l_date VARCHAR2(200);
2318 l_availability VARCHAR2(1) := 'F';
2319
2320 BEGIN
2321 IF jtf_task_recurrences_pub.creating_recurrences
2322 THEN
2323 RETURN;
2324 END IF;
2325
2326 jtf_task_recurrences_pub.creating_recurrences := TRUE;
2327
2328 SAVEPOINT create_task_recur_pvt;
2329 x_return_status := fnd_api.g_ret_sts_success;
2330
2331 IF NOT fnd_api.compatible_api_call (l_api_version, p_api_version, l_api_name, g_pkg_name)
2332 THEN
2333 RAISE fnd_api.g_exc_unexpected_error;
2334 END IF;
2335
2336 IF fnd_api.to_boolean (p_init_msg_list)
2337 THEN
2338 fnd_msg_pub.initialize;
2339 END IF;
2340
2341 --- check if the given task id has already the recur
2342 --- checking if the task already has a recurrence
2343 IF jtf_task_utl.to_boolean (p_template_flag)
2344 THEN
2345
2346
2347 SELECT recurrence_rule_id
2348 INTO l_recur_id
2349 FROM jtf_task_templates_b
2350 WHERE task_template_id = l_task_id;
2351
2352 IF l_recur_id IS NOT NULL
2353 THEN
2354
2355 x_return_status := fnd_api.g_ret_sts_error;
2356 fnd_message.set_name ('JTF', 'JTF_TASK_RECURS_TEMP_ALREADY');
2357 fnd_msg_pub.add;
2358 RAISE fnd_api.g_exc_unexpected_error;
2359 END IF;
2360 ELSE
2361 SELECT recurrence_rule_id
2362 INTO l_recur_id
2363 FROM jtf_tasks_b
2364 WHERE task_id = l_task_id;
2365 IF l_recur_id IS NOT NULL
2366 THEN
2367 x_return_status := fnd_api.g_ret_sts_error;
2368 fnd_message.set_name ('JTF', 'JTF_TASK_RECURS_TASK_ALREADY');
2369 fnd_msg_pub.add;
2370 RAISE fnd_api.g_exc_unexpected_error;
2371 END IF;
2372 END IF;
2373
2374 jtf_task_recurrences_pvt.validate_task_recurrence (
2375 p_occurs_which => p_occurs_which,
2376 p_day_of_week => p_day_of_week,
2377 p_date_of_month => p_date_of_month,
2378 p_occurs_month => p_occurs_month,
2379 p_occurs_uom => p_occurs_uom,
2380 p_occurs_every => p_occurs_every,
2381 p_occurs_number => p_occurs_number,
2382 p_start_date_active => p_start_date_active,
2383 p_end_date_active => p_end_date_active,
2384 x_msg_count => x_msg_count,
2385 x_msg_data => x_msg_data,
2386 x_return_status => x_return_status,
2387 p_sunday => p_sunday,
2388 p_monday => p_monday,
2389 p_tuesday => p_tuesday,
2390 p_wednesday => p_wednesday,
2391 p_thursday => p_thursday,
2392 p_friday => p_friday,
2393 p_saturday => p_saturday
2394 );
2395
2396 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
2397 THEN
2398
2399 x_return_status := fnd_api.g_ret_sts_unexp_error;
2400 RAISE fnd_api.g_exc_unexpected_error;
2401 END IF;
2402 --- Call the procedure to generate the dates
2403 IF not jtf_task_utl.to_boolean (p_template_flag)
2404 THEN
2405 jtf_task_recurrences_pvt.generate_dates (
2406 p_occurs_which => p_occurs_which,
2407 p_day_of_week => p_day_of_week,
2408 p_date_of_month => p_date_of_month,
2409 p_occurs_month => p_occurs_month,
2410 p_occurs_uom => p_occurs_uom,
2411 p_occurs_every => p_occurs_every,
2412 p_occurs_number => p_occurs_number,
2413 p_start_date => p_start_date_active,
2414 p_end_date => p_end_date_active,
2415 x_output_dates_tbl => l_output_dates_tbl,
2416 x_output_dates_counter => x_output_dates_counter,
2417 p_sunday => p_sunday,
2418 p_monday => p_monday,
2419 p_tuesday => p_tuesday,
2420 p_wednesday => p_wednesday,
2421 p_thursday => p_thursday,
2422 p_friday => p_friday,
2423 p_saturday => p_saturday,
2424 p_task_id => p_task_id
2425 );
2426
2427 END IF ;
2428 IF x_output_dates_counter > 1
2429 THEN
2430 x_output_dates_counter := x_output_dates_counter - 1;
2431 END IF;
2432
2433 i := 1;
2434
2435 --- To fix bug#2170817
2436 BEGIN
2437 SELECT date_selected
2438 INTO l_date_selected
2439 FROM jtf_tasks_b
2440 WHERE task_id = p_task_id;
2441 EXCEPTION WHEN OTHERS THEN
2442 l_date_selected := null;
2443 END;
2444
2445 SELECT jtf_task_recur_rules_s.nextval
2446 INTO l_recur_id
2447 FROM dual;
2448
2449 jtf_task_recur_rules_pkg.insert_row (
2450 x_rowid => l_rowid,
2451 x_recurrence_rule_id => l_recur_id,
2452 x_occurs_which => p_occurs_which,
2453 x_day_of_week => p_day_of_week,
2454 x_date_of_month => p_date_of_month,
2455 x_occurs_month => p_occurs_month,
2456 x_occurs_uom => p_occurs_uom,
2457 x_occurs_every => p_occurs_every,
2458 x_occurs_number => p_occurs_number,
2459 x_start_date_active => p_start_date_active,
2460 x_end_date_active => p_end_date_active,
2461 x_attribute1 => p_attribute1 ,
2462 x_attribute2 => p_attribute2 ,
2463 x_attribute3 => p_attribute3 ,
2464 x_attribute4 => p_attribute4 ,
2465 x_attribute5 => p_attribute5 ,
2466 x_attribute6 => p_attribute6 ,
2467 x_attribute7 => p_attribute7 ,
2468 x_attribute8 => p_attribute8 ,
2469 x_attribute9 => p_attribute9 ,
2470 x_attribute10 => p_attribute10 ,
2471 x_attribute11 => p_attribute11 ,
2472 x_attribute12 => p_attribute12 ,
2473 x_attribute13 => p_attribute13 ,
2474 x_attribute14 => p_attribute14 ,
2475 x_attribute15 => p_attribute15,
2476 x_attribute_category => p_attribute_category ,
2477 x_creation_date => SYSDATE,
2478 x_created_by => jtf_task_utl.created_by,
2479 x_last_update_date => SYSDATE,
2480 x_last_updated_by => jtf_task_utl.updated_by,
2481 x_last_update_login => fnd_global.login_id,
2482 x_sunday => p_sunday,
2483 x_monday => p_monday,
2484 x_tuesday => p_tuesday,
2485 x_wednesday => p_wednesday,
2486 x_thursday => p_thursday,
2487 x_friday => p_friday,
2488 x_saturday => p_saturday,
2489 x_date_selected => l_date_selected
2490 );
2491 OPEN c_jtf_task_recur (l_rowid);
2492 FETCH c_jtf_task_recur INTO x;
2493
2494 IF c_jtf_task_recur%NOTFOUND
2495 THEN
2496
2497 x_return_status := fnd_api.g_ret_sts_unexp_error;
2498 fnd_message.set_name ('JTF', 'ERROR_INSERTING_RECURRENCE');
2499 fnd_msg_pub.add;
2500 RAISE fnd_api.g_exc_unexpected_error;
2501 ELSE
2502 x_recurrence_rule_id := l_recur_id;
2503
2504 IF jtf_task_utl.to_boolean(p_template_flag)
2505 THEN
2506 UPDATE jtf_task_templates_b
2507 SET recurrence_rule_id = l_recur_id
2508 WHERE task_template_id = l_task_id;
2509 ELSE
2510 UPDATE jtf_tasks_b
2511 SET recurrence_rule_id = l_recur_id
2512 WHERE task_id = l_task_id;
2513
2514 END IF;
2515 IF SQL%NOTFOUND
2516 THEN
2517 x_return_status := fnd_api.g_ret_sts_unexp_error;
2518 fnd_message.set_name ('JTF', 'ERROR_UPDATING_TASK');
2519 fnd_msg_pub.add;
2520 RAISE fnd_api.g_exc_unexpected_error;
2521 END IF;
2522 END IF;
2523
2524
2525 OPEN c_task_details;
2526 FETCH c_task_details into l_task_details;
2527 IF c_task_details%NOTFOUND
2528 THEN
2529 CLOSE c_task_details;
2530 fnd_message.set_name('JTF','JTF_TASK_INVALID_TASK_ID');
2531 fnd_message.set_token('P_TASK_ID', p_task_id);
2532 fnd_msg_pub.add;
2533 RAISE fnd_api.g_exc_unexpected_error;
2534 END IF;
2535
2536 --- if it a template do nothing
2537 --- else call copy tasks to create the tasks.
2538 ---
2539
2540 IF NOT jtf_task_utl.to_boolean (p_template_flag)
2541 THEN
2542 i := 1;
2543 WHILE i <= x_output_dates_counter
2544 LOOP
2545 l_valid := FALSE; -- Fix bug 2376554
2546
2547
2548 IF l_date_selected = 'P' OR l_date_selected IS NULL
2549 THEN
2550
2551 IF ( l_task_details.planned_end_date IS NULL AND
2552 l_task_details.planned_start_date IS NOT NULL
2553 )
2554 THEN
2555 l_planned_start_date :=
2556 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')
2557 , l_task_details.planned_start_date
2558 );
2559
2560 l_planned_end_date := NULL ;
2561
2562 ELSIF ( l_task_details.planned_end_date IS NOT NULL AND
2563 l_task_details.planned_start_date IS NULL
2564 )
2565 THEN
2566 l_planned_end_date :=
2567 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')
2568 , l_task_details.planned_end_date
2569 );
2570 l_planned_start_date := null ;
2571
2572
2573 ELSIF ( l_task_details.planned_end_date IS NULL AND
2574 l_task_details.planned_start_date IS NULL
2575 )
2576 THEN
2577 l_planned_start_date := l_output_dates_tbl(i);
2578 l_planned_end_date := NULL ;
2579
2580 ELSIF ( l_task_details.planned_end_date IS NOT NULL AND
2581 l_task_details.planned_start_date IS NOT NULL
2582 )
2583 THEN
2584 l_planned_start_date :=
2585 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')
2586 , l_task_details.planned_start_date
2587 );
2588 l_planned_end_date := l_planned_start_date + (l_task_details.planned_end_date - l_task_details.planned_start_date);
2589 END IF;
2590
2591 ELSIF l_date_selected = 'S'
2592 THEN
2593 IF ( l_task_details.scheduled_end_date IS NULL AND
2594 l_task_details.scheduled_start_date IS NOT NULL
2595 )
2596 THEN
2597 l_scheduled_start_date :=
2598 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')
2599 ,l_task_details.scheduled_start_date
2600 );
2601 l_scheduled_end_date := Null ;
2602
2603 ELSIF ( l_task_details.scheduled_end_date IS NOT NULL AND
2604 l_task_details.scheduled_start_date IS NULL
2605 )
2606 THEN
2607 l_scheduled_end_date :=
2608 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')
2609 ,l_task_details.scheduled_end_date
2610 );
2611 l_scheduled_start_date := null ;
2612
2613 ELSIF ( l_task_details.scheduled_end_date IS NULL AND
2614 l_task_details.scheduled_start_date IS NULL
2615 )
2616 THEN
2617 l_scheduled_start_date := l_output_dates_tbl(i);
2618 l_scheduled_end_date := null ;
2619 ELSIF ( l_task_details.scheduled_end_date IS NOT NULL AND
2620 l_task_details.scheduled_start_date IS NOT NULL
2621 )
2622 THEN
2623 l_scheduled_start_date :=
2624 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')
2625 ,l_task_details.scheduled_start_date
2626 );
2627 l_scheduled_end_date := l_scheduled_start_date + (l_task_details.scheduled_end_date - l_task_details.scheduled_start_date);
2628 END IF;
2629
2630 ELSIF l_date_selected = 'A'
2631 THEN
2632 IF ( l_task_details.actual_end_date IS NULL AND
2633 l_task_details.actual_start_date IS NOT NULL
2634 )
2635 THEN
2636 l_actual_start_date :=
2637 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')
2638 ,l_task_details.actual_start_date
2639 );
2640 l_actual_end_date := Null ;
2641
2642 ELSIF ( l_task_details.actual_end_date IS NOT NULL AND
2643 l_task_details.actual_start_date IS NULL
2644 )
2645 THEN
2646 l_actual_end_date :=
2647 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')
2648 ,l_task_details.actual_end_date
2649 );
2650 l_actual_start_date := null ;
2651
2652 ELSIF ( l_task_details.actual_end_date IS NULL AND
2653 l_task_details.actual_start_date IS NULL
2654 )
2655 THEN
2656 l_actual_start_date := l_output_dates_tbl(i);
2657 l_actual_end_date := null ;
2658 ELSIF ( l_task_details.actual_end_date IS NOT NULL AND
2659 l_task_details.actual_start_date IS NOT NULL
2660 )
2661 THEN
2662 l_actual_start_date :=
2663 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')
2664 ,l_task_details.actual_start_date
2665 );
2666 l_actual_end_date := l_actual_start_date + (l_task_details.actual_end_date - l_task_details.actual_start_date);
2667 END IF;
2668
2669
2670 END IF;
2671
2672 IF l_date_selected = 'P' OR l_date_selected IS NULL
2673 THEN
2674
2675
2676 --change or add dates for scheduled date fields with the same pattern
2677 IF ( l_task_details.scheduled_end_date IS NULL
2678 AND l_task_details.scheduled_start_date IS NOT NULL)
2679 THEN
2680
2681 l_scheduled_start_date :=
2682 l_task_details.scheduled_start_date +(l_planned_start_date - l_task_details.planned_start_date);
2683
2684 l_scheduled_end_date := Null ;
2685 elsif ( l_task_details.scheduled_end_date IS NOT NULL
2686 AND l_task_details.scheduled_start_date IS NULL)
2687 THEN
2688
2689 l_scheduled_end_date :=
2690 l_task_details.scheduled_end_date +(l_planned_start_date - l_task_details.planned_start_date);
2691
2692 l_scheduled_start_date := Null ;
2693
2694 elsif
2695 ( l_task_details.scheduled_end_date IS NULL
2696 AND l_task_details.scheduled_start_date IS NULL)
2697 THEN
2698
2699 l_scheduled_start_date := null;
2700 l_scheduled_end_date := null ;
2701 elsif
2702 ( l_task_details.scheduled_end_date IS NOT NULL
2703 AND l_task_details.scheduled_start_date IS NOT NULL)
2704 THEN
2705
2706 l_scheduled_start_date :=
2707 l_task_details.scheduled_start_date + (l_planned_start_date - l_task_details.planned_start_date);
2708
2709 l_scheduled_end_date :=
2710 l_task_details.scheduled_end_date + (l_planned_start_date - l_task_details.planned_start_date);
2711 end if;-- for scheduled
2712
2713 --change or add dates for actual date fields with the same pattern
2714 IF ( l_task_details.actual_end_date IS NULL
2715 AND l_task_details.actual_start_date IS NOT NULL)
2716 THEN
2717
2718 l_actual_start_date :=
2719 l_task_details.actual_start_date + (l_planned_start_date - l_task_details.planned_start_date);
2720
2721 l_actual_end_date := Null ;
2722
2723 elsif ( l_task_details.actual_end_date IS NOT NULL
2724 AND l_task_details.actual_start_date IS NULL)
2725 THEN
2726
2727 l_actual_end_date :=
2728 l_task_details.actual_end_date + (l_planned_start_date - l_task_details.planned_start_date);
2729
2730 l_actual_start_date := Null ;
2731
2732 elsif
2733 ( l_task_details.actual_end_date IS NULL
2734 AND l_task_details.actual_start_date IS NULL)
2735 THEN
2736
2737 l_actual_start_date := null;
2738 l_actual_end_date := null ;
2739 elsif
2740 ( l_task_details.actual_end_date IS NOT NULL
2741 AND l_task_details.actual_start_date IS NOT NULL)
2742 THEN
2743
2744 l_actual_start_date :=
2745 l_task_details.actual_start_date + (l_planned_start_date - l_task_details.planned_start_date);
2746
2747 l_actual_end_date :=
2748 l_task_details.actual_end_date + (l_planned_start_date - l_task_details.planned_start_date);
2749 end if;-- for actual
2750
2751 ELSIF l_date_selected = 'S'
2752 THEN
2753
2754 --change or add dates for planned date fields with the same pattern
2755 IF ( l_task_details.planned_end_date IS NULL
2756 AND l_task_details.planned_start_date IS NOT NULL)
2757 THEN
2758
2759 l_planned_start_date :=
2760 l_task_details.planned_start_date + (l_scheduled_start_date - l_task_details.scheduled_start_date);
2761
2762 l_planned_end_date := Null ;
2763
2764 elsif ( l_task_details.planned_end_date IS NOT NULL
2765 AND l_task_details.planned_start_date IS NULL)
2766 THEN
2767
2768 l_planned_end_date :=
2769 l_task_details.planned_end_date + (l_scheduled_start_date - l_task_details.scheduled_start_date);
2770
2771 l_planned_start_date := Null ;
2772
2773 elsif
2774 ( l_task_details.planned_end_date IS NULL
2775 AND l_task_details.planned_start_date IS NULL)
2776 THEN
2777
2778 l_planned_start_date := null;
2779 l_planned_end_date := null ;
2780 elsif
2781 ( l_task_details.planned_end_date IS NOT NULL
2782 AND l_task_details.planned_start_date IS NOT NULL)
2783 THEN
2784
2785 l_planned_start_date :=
2786 l_task_details.planned_start_date + (l_scheduled_start_date - l_task_details.scheduled_start_date);
2787
2788 l_planned_end_date :=
2789 l_task_details.planned_end_date + (l_scheduled_start_date - l_task_details.scheduled_start_date);
2790 end if;-- for planned
2791
2792 --change or add dates for actual date fields with the same pattern
2793 IF ( l_task_details.actual_end_date IS NULL
2794 AND l_task_details.actual_start_date IS NOT NULL)
2795 THEN
2796
2797 l_actual_start_date :=
2798 l_task_details.actual_start_date + (l_scheduled_start_date - l_task_details.scheduled_start_date);
2799
2800 l_actual_end_date := Null ;
2801
2802 elsif ( l_task_details.actual_end_date IS NOT NULL
2803 AND l_task_details.actual_start_date IS NULL)
2804 THEN
2805
2806 l_actual_end_date :=
2807 l_task_details.actual_end_date + (l_scheduled_start_date - l_task_details.scheduled_start_date);
2808
2809 l_actual_start_date := Null ;
2810
2811 elsif
2812 ( l_task_details.actual_end_date IS NULL
2813 AND l_task_details.actual_start_date IS NULL)
2814 THEN
2815
2816 l_actual_start_date := null;
2817 l_actual_end_date := null ;
2818 elsif
2819 ( l_task_details.actual_end_date IS NOT NULL
2820 AND l_task_details.actual_start_date IS NOT NULL)
2821 THEN
2822
2823 l_actual_start_date :=
2824 l_task_details.actual_start_date + (l_scheduled_start_date - l_task_details.scheduled_start_date);
2825
2826 l_actual_end_date :=
2827 l_task_details.actual_end_date + (l_scheduled_start_date - l_task_details.scheduled_start_date);
2828 end if;-- for actual
2829
2830 ELSIF l_date_selected = 'A'
2831 THEN
2832
2833 --change or add dates for planned date fields with the same pattern
2834 IF ( l_task_details.planned_end_date IS NULL
2835 AND l_task_details.planned_start_date IS NOT NULL)
2836 THEN
2837
2838 l_planned_start_date :=
2839 l_task_details.planned_start_date + (l_actual_start_date - l_task_details.actual_start_date);
2840
2841 l_planned_end_date := Null ;
2842
2843 elsif ( l_task_details.planned_end_date IS NOT NULL
2844 AND l_task_details.planned_start_date IS NULL)
2845 THEN
2846
2847 l_planned_end_date :=
2848 l_task_details.planned_end_date + (l_actual_start_date - l_task_details.actual_start_date);
2849
2850 l_planned_start_date := Null ;
2851
2852 elsif
2853 ( l_task_details.planned_end_date IS NULL
2854 AND l_task_details.planned_start_date IS NULL)
2855 THEN
2856
2857 l_planned_start_date := null;
2858 l_planned_end_date := null ;
2859 elsif
2860 ( l_task_details.planned_end_date IS NOT NULL
2861 AND l_task_details.planned_start_date IS NOT NULL)
2862 THEN
2863
2864 l_planned_start_date :=
2865 l_task_details.planned_start_date + (l_actual_start_date - l_task_details.actual_start_date);
2866
2867 l_planned_end_date :=
2868 l_task_details.planned_end_date + (l_actual_start_date - l_task_details.actual_start_date);
2869 end if;-- for planned
2870
2871 --change or add dates for scheduled date fields with the same pattern
2872 IF ( l_task_details.scheduled_end_date IS NULL
2873 AND l_task_details.scheduled_start_date IS NOT NULL)
2874 THEN
2875
2876 l_scheduled_start_date :=
2877 l_task_details.scheduled_start_date + (l_actual_start_date - l_task_details.actual_start_date);
2878
2879 l_scheduled_end_date := Null ;
2880
2881 elsif ( l_task_details.scheduled_end_date IS NOT NULL
2882 AND l_task_details.scheduled_start_date IS NULL)
2883 THEN
2884
2885 l_scheduled_end_date :=
2886 l_task_details.scheduled_end_date + (l_actual_start_date - l_task_details.actual_start_date);
2887
2888 l_scheduled_start_date := Null ;
2889
2890 elsif
2891 ( l_task_details.scheduled_end_date IS NULL
2892 AND l_task_details.scheduled_start_date IS NULL)
2893 THEN
2894
2895 l_scheduled_start_date := null;
2896 l_scheduled_end_date := null ;
2897 elsif
2898 ( l_task_details.scheduled_end_date IS NOT NULL
2899 AND l_task_details.scheduled_start_date IS NOT NULL)
2900 THEN
2901
2902 l_scheduled_start_date :=
2903 l_task_details.scheduled_start_date + (l_actual_start_date - l_task_details.actual_start_date);
2904
2905 l_scheduled_end_date :=
2906 l_task_details.scheduled_end_date + (l_actual_start_date - l_task_details.actual_start_date);
2907 end if;-- for scheduled
2908 ELSIF l_date_selected = 'D'
2909 THEN
2910 l_planned_start_date := NULL;
2911 l_planned_end_date := NULL;
2912 l_scheduled_start_date := NULL;
2913 l_scheduled_end_date := NULL;
2914 l_actual_start_date := NULL;
2915 l_actual_end_date := NULL;
2916 IF l_task_details.planned_start_date IS NOT NULL
2917 THEN
2918 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')
2919 , l_task_details.planned_start_date
2920 );
2921 END IF;
2922
2923 IF l_task_details.planned_end_date IS NOT NULL
2924 THEN
2925 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')
2926 , l_task_details.planned_end_date
2927 );
2928 END IF;
2929
2930 IF l_task_details.scheduled_start_date IS NOT NULL
2931 THEN
2932 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')
2933 , l_task_details.scheduled_start_date
2934 );
2935 END IF;
2936
2937 IF l_task_details.scheduled_end_date IS NOT NULL
2938 THEN
2939 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')
2940 , l_task_details.scheduled_end_date
2941 );
2942 END IF;
2943
2944 IF l_task_details.actual_start_date IS NOT NULL
2945 THEN
2946 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')
2947 , l_task_details.actual_start_date
2948 );
2949 END IF;
2950
2951 IF l_task_details.actual_end_date IS NOT NULL
2952 THEN
2953 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')
2954 , l_task_details.actual_end_date
2955 );
2956 END IF;
2957 END IF;
2958
2959
2960
2961
2962
2963
2964
2965
2966 IF l_date_selected IS NULL -- Added by lokumar for bug#6067036
2967 THEN
2968 l_calendar_start_date := NULL;
2969 l_calendar_end_date := NULL;
2970 l_valid := TRUE;
2971 ELSIF l_date_selected = 'P' --OR l_date_selected IS NULL -- Commented out by lokumar for bug#6067036
2972 THEN
2973 if ((p_end_date_active is null or trunc(l_planned_start_date) <= trunc(p_end_date_active))
2974 --and trunc(p_start_date_active) <> trunc(l_planned_start_date) then
2975 and trunc(l_task_details.planned_start_date) <> trunc(l_planned_start_date))
2976 OR l_date_selected IS NULL -- Fix bug 2376554
2977 then
2978 l_calendar_start_date := l_planned_start_date; -- Fix bug 2376554
2979 l_calendar_end_date := l_planned_end_date; -- Fix bug 2376554
2980 l_valid := TRUE; -- Fix bug 2376554
2981 end if;
2982 ELSIF l_date_selected = 'S'
2983 THEN
2984 if (p_end_date_active is null or trunc(l_scheduled_start_date) <= trunc(p_end_date_active))
2985 --and trunc(p_start_date_active) <> trunc(l_scheduled_start_date) then
2986 and trunc(l_task_details.scheduled_start_date) <> trunc(l_scheduled_start_date) then
2987 l_calendar_start_date := l_scheduled_start_date; -- Fix bug 2376554
2988 l_calendar_end_date := l_scheduled_end_date; -- Fix bug 2376554
2989 l_valid := TRUE; -- Fix bug 2376554
2990 end if;
2991 ELSIF l_date_selected = 'A'
2992 THEN
2993 if (p_end_date_active is null or trunc(l_actual_start_date) <= trunc(p_end_date_active))
2994 --and trunc(p_start_date_active) <> trunc(l_actual_start_date) then
2995 and trunc(l_task_details.actual_start_date) <> trunc(l_actual_start_date) then
2996 l_calendar_start_date := l_actual_start_date; -- Fix bug 2376554
2997 l_calendar_end_date := l_actual_end_date; -- Fix bug 2376554
2998 l_valid := TRUE; -- Fix bug 2376554
2999 end if;
3000 ELSIF l_date_selected = 'D'
3001 THEN
3002 jtf_task_utl_ext.set_start_n_due_date (
3003 p_task_status_id => l_task_details.task_status_id
3004 , p_planned_start_date => l_planned_start_date
3005 , p_planned_end_date => l_planned_end_date
3006 , p_scheduled_start_date => l_scheduled_start_date
3007 , p_scheduled_end_date => l_scheduled_end_date
3008 , p_actual_start_date => l_actual_start_date
3009 , p_actual_end_date => l_actual_end_date
3010 , p_creation_date => l_task_details.creation_date
3011 , x_calendar_start_date => l_calendar_start_date
3012 , x_calendar_end_date => l_calendar_end_date
3013 , x_return_status => x_return_status);
3014
3015 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
3016 THEN
3017 RAISE fnd_api.g_exc_unexpected_error;
3018 END IF;
3019 l_valid := TRUE;
3020 END IF;
3021
3022 -------------------------
3023 -- Fix bug 2376554
3024 -------------------------
3025 IF l_valid
3026 THEN
3027 l_current := SYSDATE;
3028
3029 IF i <> 1
3030 THEN
3031 jtf_tasks_pub.copy_task (
3032 p_api_version => 1.0,
3033 p_init_msg_list => fnd_api.g_false,
3034 p_commit => p_commit,
3035 p_source_task_id => l_task_id,
3036 p_copy_task_assignments => fnd_api.g_true,
3037 p_copy_task_rsc_reqs => fnd_api.g_true,
3038 p_copy_task_depends => fnd_api.g_true,
3039 p_create_recurrences => fnd_api.g_false,
3040 p_copy_task_references => fnd_api.g_true,
3041 p_copy_task_dates => fnd_api.g_true,
3042 p_copy_task_contacts => fnd_api.g_true,
3043 p_copy_task_contact_points => fnd_api.g_true,
3044 x_return_status => x_return_status,
3045 x_msg_count => x_msg_count,
3046 x_msg_data => x_msg_data,
3047 x_task_id => l_recur_task_id
3048 );
3049
3050 UPDATE jtf_tasks_b
3051 SET recurrence_rule_id = l_recur_id,
3052 planned_start_date = l_planned_start_date ,
3053 planned_end_date = l_planned_end_date,
3054 scheduled_start_date = l_scheduled_start_date ,
3055 scheduled_end_date = l_scheduled_end_date,
3056 actual_start_date = l_actual_start_date ,
3057 actual_end_date = l_actual_end_date,
3058 calendar_start_date = l_calendar_start_date ,
3059 calendar_end_date = l_calendar_end_date,
3060 creation_date = l_current,
3061 last_update_date = l_current,
3062 date_selected = l_date_selected -- Added by lokumar for bug#6067036
3063 WHERE task_id = l_recur_task_id ;
3064 ELSE
3065 UPDATE jtf_tasks_b
3066 SET recurrence_rule_id = l_recur_id,
3067 planned_start_date = l_planned_start_date ,
3068 planned_end_date = l_planned_end_date,
3069 scheduled_start_date = l_scheduled_start_date ,
3070 scheduled_end_date = l_scheduled_end_date,
3071 actual_start_date = l_actual_start_date ,
3072 actual_end_date = l_actual_end_date,
3073 calendar_start_date = l_calendar_start_date ,
3074 calendar_end_date = l_calendar_end_date,
3075 creation_date = l_current,
3076 last_update_date = l_current,
3077 date_selected = l_date_selected -- Added by lokumar for bug#6067036
3078 WHERE task_id = l_task_id ;
3079 END IF;
3080
3081 /* Start of addition by lokumar for bug#6067036 */
3082
3083 OPEN c_task_planned_effort(NVL(l_recur_task_id,l_task_id));
3084 FETCH c_task_planned_effort INTO v_task_planned_effort;
3085 CLOSE c_task_planned_effort;
3086
3087 FOR i IN c_assign_actual_dtls(NVL(l_recur_task_id,l_task_id))
3088 LOOP
3089 jtf_task_assignments_pvt.populate_booking_dates
3090 (
3091 p_calendar_start_date => l_calendar_start_date,
3092 p_calendar_end_date => l_calendar_end_date,
3093 p_actual_start_date => i.actual_start_date,
3094 p_actual_end_date => i.actual_end_date,
3095 p_actual_travel_duration => i.actual_travel_duration,
3096 p_actual_travel_duration_uom => i.actual_travel_duration_uom,
3097 p_planned_effort => v_task_planned_effort.planned_effort,
3098 p_planned_effort_uom => v_task_planned_effort.planned_effort_uom,
3099 p_actual_effort => i.actual_effort,
3100 p_actual_effort_uom => i.actual_effort_uom,
3101 x_booking_start_date => l_booking_start_date,
3102 x_booking_end_date => l_booking_end_date
3103 );
3104
3105 UPDATE jtf_task_all_assignments
3106 SET booking_start_date = l_booking_start_date,
3107 booking_end_date = l_booking_end_date
3108 WHERE task_assignment_id = i.task_assignment_id;
3109
3110
3111 /* Added check to halt creation of double booking appointments while updating appointments*/
3112 if l_task_details.source_object_type_code = 'APPOINTMENT' and i.resource_type_code = 'PN_LOCATION' then
3113
3114
3115 cac_avlblty_pub.IS_AVAILABLE(
3116 p_api_version => 1.0,
3117 p_init_msg_list => 'F',
3118 p_Object_Type => i.resource_type_code,
3119 p_Object_ID => i.resource_id,
3120 p_Start_Date_Time => l_booking_start_date,
3121 p_End_Date_Time => l_booking_end_date,
3122 p_Schedule_Category => null ,
3123 p_Busy_Tentative => null,
3124 p_task_assignment_id => i.task_assignment_id,
3125 x_Available => l_availability,
3126 x_return_status => x_return_status,
3127 x_msg_count => x_msg_count,
3128 x_msg_data => x_msg_data);
3129
3130
3131
3132 if l_availability = 'F' then
3133
3134 l_name := null;
3135 l_name := JTF_TASK_UTL.get_owner(i.resource_type_code, i.resource_id);
3136 l_date := l_booking_start_date;
3137 IF(fnd_profile.Value('ENABLE_TIMEZONE_CONVERSIONS') = 'Y') THEN
3138 l_date := To_Char(CAC_AVLBLTY_PVT.ADJUST_FOR_TIMEZONE(fnd_profile.value('SERVER_TIMEZONE_ID'),
3139 fnd_profile.value('CLIENT_TIMEZONE_ID'),
3140 l_booking_start_date),
3141 fnd_profile.Value('ICX_DATE_FORMAT_MASK')||' HH:MI:SS AM');
3142 END IF;
3143
3144
3145 fnd_message.set_name ('JTF', 'JTF_APPT_RES_NOT_AVAILABLE');
3146 fnd_message.set_token ('RES', l_name );
3147 fnd_message.set_token ('DATE', l_date);
3148
3149 fnd_msg_pub.add;
3150 x_return_status := fnd_api.g_ret_sts_unexp_error;
3151
3152 RAISE fnd_api.g_exc_unexpected_error;
3153
3154 end if;
3155 end if;
3156
3157 END LOOP;
3158
3159 /* End of addition by lokumar for bug#6067036 */
3160
3161
3162 END IF;
3163 ---------------------------------------------------
3164
3165 jtf_task_recurrences_pub.creating_recurrences := FALSE;
3166 i := i + 1;
3167 END LOOP;
3168 END IF;
3169 close c_task_details;
3170 jtf_task_recurrences_pub.creating_recurrences := FALSE;
3171 IF not jtf_task_utl.to_boolean (p_template_flag) AND -- Fix bug 2395216
3172 NOT original_date_meets_criteria(p_output_dates_tbl => l_output_dates_tbl ,
3173 p_start_date_active => p_start_date_active) and
3174 p_occurs_uom <> 'DAY'
3175 THEN
3176 -- Fix bug 2376554
3177 --l_ovn := get_ovn (p_task_id => p_task_id);
3178 --jtf_tasks_pvt.delete_task (
3179 -- p_api_version => 1.0,
3180 -- p_init_msg_list => fnd_api.g_true,
3181 -- p_commit => fnd_api.g_false,
3182 -- p_task_id => p_task_id,
3183 -- p_object_version_number => l_ovn,
3184 -- x_return_status => x_return_status,
3185 -- x_msg_count => x_msg_count,
3186 -- x_msg_data => x_msg_data
3187 --);
3188 get_repeat_start_date(
3189 p_recurrence_rule_id => l_recur_id
3190 ,x_repeat_start_date => l_repeat_start_date
3191 );
3192
3193 UPDATE jtf_task_recur_rules
3194 SET start_date_active = TRUNC(NVL(l_repeat_start_date, l_output_dates_tbl(1))) -- Fix bug 2376554, bug 2385202
3195 WHERE recurrence_rule_id = l_recur_id;
3196 END IF;
3197
3198 IF not jtf_task_utl.to_boolean (p_template_flag) AND -- Fix bug 2395216
3199 p_end_date_active IS NULL
3200 THEN
3201 l_last := l_output_dates_tbl.LAST; -- Fix bug 2376554
3202
3203 -- Commented out by SBARAT on 28/07/2005 for bug# 4365923
3204 /*jtf_task_recurrences_pvt.get_repeat_end_date(
3205 p_recurrence_rule_id => l_recur_id
3206 ,x_repeat_end_date => l_repeat_end_date
3207 );*/
3208
3209 -- Modified by SBARAT on 28/07/2005 for bug# 4365923
3210 UPDATE jtf_task_recur_rules
3211 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
3212 WHERE recurrence_rule_id = l_recur_id;
3213 END IF;
3214
3215 set_last_update_date(l_recur_id);
3216
3217 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3218
3219 EXCEPTION
3220 WHEN fnd_api.g_exc_unexpected_error
3221 THEN
3222 jtf_task_recurrences_pub.creating_recurrences := FALSE;
3223
3224
3225 ROLLBACK TO create_task_recur_pvt;
3226 x_return_status := fnd_api.g_ret_sts_unexp_error;
3227 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3228 WHEN OTHERS
3229 THEN
3230 jtf_task_recurrences_pub.creating_recurrences := FALSE;
3231 ROLLBACK TO create_task_recur_pvt;
3232 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
3233 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
3234 fnd_msg_pub.add;
3235 x_return_status := fnd_api.g_ret_sts_unexp_error;
3236 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3237 END;
3238
3239 -- To fix bug 2721278
3240 FUNCTION is_repeat_start_date_changed (p_recurrence_rule_id IN NUMBER
3241 ,p_target_repeat_start_date IN DATE)
3242 RETURN BOOLEAN
3243 IS
3244 CURSOR c_recur IS
3245 SELECT 1
3246 FROM jtf_task_recur_rules
3247 WHERE recurrence_rule_id = p_recurrence_rule_id
3248 AND TRUNC(start_date_active) = TRUNC(p_target_repeat_start_date);
3249
3250 l_dummy NUMBER;
3251 l_changed BOOLEAN := TRUE;
3252 BEGIN
3253 OPEN c_recur;
3254 FETCH c_recur INTO l_dummy;
3255 IF c_recur%FOUND
3256 THEN
3257 l_changed := FALSE;
3258 END IF;
3259 CLOSE c_recur;
3260
3261 RETURN l_changed;
3262 END is_repeat_start_date_changed;
3263
3264 PROCEDURE update_task_recurrence (
3265 p_api_version IN NUMBER,
3266 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
3267 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
3268 p_task_id IN NUMBER,
3269 p_recurrence_rule_id IN NUMBER,
3270 p_occurs_which IN INTEGER DEFAULT NULL,
3271 p_day_of_week IN INTEGER DEFAULT NULL,
3272 p_date_of_month IN INTEGER DEFAULT NULL,
3273 p_occurs_month IN INTEGER DEFAULT NULL,
3274 p_occurs_uom IN VARCHAR2 DEFAULT NULL,
3275 p_occurs_every IN INTEGER DEFAULT NULL,
3276 p_occurs_number IN INTEGER DEFAULT NULL,
3277 p_start_date_active IN DATE DEFAULT NULL,
3278 p_end_date_active IN DATE DEFAULT NULL,
3279 p_template_flag IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
3280 p_attribute1 IN VARCHAR2 DEFAULT NULL ,
3281 p_attribute2 IN VARCHAR2 DEFAULT NULL ,
3282 p_attribute3 IN VARCHAR2 DEFAULT NULL ,
3283 p_attribute4 IN VARCHAR2 DEFAULT NULL ,
3284 p_attribute5 IN VARCHAR2 DEFAULT NULL ,
3285 p_attribute6 IN VARCHAR2 DEFAULT NULL ,
3286 p_attribute7 IN VARCHAR2 DEFAULT NULL ,
3287 p_attribute8 IN VARCHAR2 DEFAULT NULL ,
3288 p_attribute9 IN VARCHAR2 DEFAULT NULL ,
3289 p_attribute10 IN VARCHAR2 DEFAULT NULL ,
3290 p_attribute11 IN VARCHAR2 DEFAULT NULL ,
3291 p_attribute12 IN VARCHAR2 DEFAULT NULL ,
3292 p_attribute13 IN VARCHAR2 DEFAULT NULL ,
3293 p_attribute14 IN VARCHAR2 DEFAULT NULL ,
3294 p_attribute15 IN VARCHAR2 DEFAULT NULL ,
3295 p_attribute_category IN VARCHAR2 DEFAULT NULL ,
3296 p_sunday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
3297 p_monday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
3298 p_tuesday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
3299 p_wednesday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
3300 p_thursday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
3301 p_friday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
3302 p_saturday IN VARCHAR2 DEFAULT jtf_task_utl.g_no_char,
3303 x_new_recurrence_rule_id OUT NOCOPY NUMBER,
3304 x_return_status OUT NOCOPY VARCHAR2,
3305 x_msg_count OUT NOCOPY NUMBER,
3306 x_msg_data OUT NOCOPY VARCHAR2
3307 )
3308 IS
3309 CURSOR c_task (b_task_id NUMBER) IS
3310 SELECT MAX(t.calendar_start_date) calendar_start_date
3311 , COUNT(t.task_id) occurs_number
3312 FROM jtf_tasks_b t
3313 , jtf_tasks_b curr_task
3314 WHERE curr_task.task_id = b_task_id
3315 AND t.recurrence_rule_id = curr_task.recurrence_rule_id
3316 AND t.calendar_start_date < curr_task.calendar_start_date;
3317
3318 rec_task c_task%ROWTYPE;
3319
3320 CURSOR c_new_start_date (b_task_id NUMBER) IS
3321 SELECT TRUNC(calendar_start_date) calendar_start_date
3322 FROM jtf_tasks_b
3323 WHERE task_id = b_task_id;
3324
3325 rec_new_start_date c_new_start_date%ROWTYPE;
3326
3327 l_new_task_id NUMBER;
3328 l_first_task BOOLEAN := FALSE;
3329 l_delete_future_recurrences VARCHAR2(1);
3330 l_task_details_rec jtf_task_recurrences_pub.task_details_rec;
3331 l_output_dates_counter INTEGER;
3332 l_object_version_number NUMBER;
3333 i NUMBER;
3334 -- To fix bug 2721278
3335 l_repeat_start_date DATE;
3336 BEGIN
3337 SAVEPOINT update_task_recurrence_pvt;
3338
3339 x_return_status := fnd_api.g_ret_sts_success;
3340
3341 IF fnd_api.to_boolean (p_init_msg_list)
3342 THEN
3343 fnd_msg_pub.initialize;
3344 END IF;
3345
3346 OPEN c_new_start_date (p_task_id);
3347 FETCH c_new_start_date INTO rec_new_start_date;
3348
3349 IF c_new_start_date%NOTFOUND
3350 THEN
3351 CLOSE c_new_start_date;
3352 fnd_message.set_name('JTF','JTF_TASK_INVALID_TASK_ID');
3353 fnd_message.set_token('P_TASK_ID', p_task_id);
3354 fnd_msg_pub.add;
3355 x_return_status := fnd_api.g_ret_sts_unexp_error;
3356 RAISE fnd_api.g_exc_unexpected_error;
3357 END IF;
3358 CLOSE c_new_start_date;
3359
3360 -- To fix bug 2721278
3361 -- Check if the repeat start date is changed or not.
3362 -- If it's changed, use the new repeating start date
3363 -- Otherwise, use the current selected task's calendar start date
3364 IF is_repeat_start_date_changed (p_recurrence_rule_id => p_recurrence_rule_id
3365 ,p_target_repeat_start_date => p_start_date_active)
3366 THEN
3367 l_repeat_start_date := p_start_date_active;
3368 ELSE
3369 l_repeat_start_date := rec_new_start_date.calendar_start_date;
3370 END IF;
3371 -----------------------------------------------------------------------------
3372
3373 ---------------------------------------------
3374 -- Copy p_task_id to a new one
3375 -- Store the new task_id into l_new_task_id
3376 ---------------------------------------------
3377 jtf_tasks_pub.copy_task (
3378 p_api_version => 1.0,
3379 p_init_msg_list => fnd_api.g_true,
3380 p_commit => fnd_api.g_false,
3381 p_source_task_id => p_task_id,
3382 p_copy_task_assignments => fnd_api.g_true,
3383 p_copy_task_rsc_reqs => fnd_api.g_true,
3384 p_copy_task_depends => fnd_api.g_true,
3385 p_create_recurrences => fnd_api.g_false,
3386 p_copy_task_references => fnd_api.g_true,
3387 p_copy_task_dates => fnd_api.g_true,
3388 p_copy_task_contacts => fnd_api.g_true,
3389 p_copy_task_contact_points => fnd_api.g_true,
3390 x_return_status => x_return_status,
3391 x_msg_count => x_msg_count,
3392 x_msg_data => x_msg_data,
3393 x_task_id => l_new_task_id
3394 );
3395 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
3396 THEN
3397 x_return_status := fnd_api.g_ret_sts_unexp_error;
3398 RAISE fnd_api.g_exc_unexpected_error;
3399 END IF;
3400
3401 ---------------------------------------------
3402 -- Change the status of all assignees to 18
3403 -- for the copied
3404 ---------------------------------------------
3405 UPDATE jtf_task_all_assignments
3406 SET assignment_status_id = 18
3407 , last_update_date = SYSDATE
3408 , last_updated_by = fnd_global.user_id
3409 WHERE task_id = l_new_task_id
3410 AND assignee_role = 'ASSIGNEE';
3411
3412 --------------------------------------------------
3413 -- Check if p_task_id equals to the first task id
3414 --------------------------------------------------
3415 l_first_task := jta_sync_task_utl.is_this_first_task(p_task_id => p_task_id);
3416
3417 IF l_first_task
3418 THEN
3419 l_delete_future_recurrences := 'A'; -- Delete all the occurrences
3420 ELSE
3421 l_delete_future_recurrences := fnd_api.g_true; -- Delete the future occrrences
3422 END IF;
3423
3424 --------------------------------------------------
3425 -- Delete all the appointments
3426 -- with given option l_delete_future_recurrences
3427 --------------------------------------------------
3428 l_object_version_number := jta_sync_task_common.get_ovn(p_task_id => p_task_id);
3429
3430 jtf_tasks_pvt.delete_task (
3431 p_api_version => 1.0,
3432 p_init_msg_list => fnd_api.g_false,
3433 p_commit => fnd_api.g_false,
3434 p_object_version_number => l_object_version_number,
3435 p_task_id => p_task_id,
3436 p_delete_future_recurrences => l_delete_future_recurrences,
3437 x_return_status => x_return_status,
3438 x_msg_count => x_msg_count,
3439 x_msg_data => x_msg_data
3440 );
3441 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
3442 THEN
3443 x_return_status := fnd_api.g_ret_sts_unexp_error;
3444 RAISE fnd_api.g_exc_unexpected_error;
3445 END IF;
3446
3447 ---------------------------------------------------------------------
3448 -- Update end_date_active and occurs_number for the current
3449 -- recurrence rule with the calendar_start_date of the task
3450 -- and the number of tasks right before the current selected task
3451 ---------------------------------------------------------------------
3452 IF NOT l_first_task
3453 THEN
3454 OPEN c_task (p_task_id);
3455 FETCH c_task INTO rec_task;
3456 IF c_task%NOTFOUND
3457 THEN
3458 CLOSE c_task;
3459 fnd_message.set_name('JTF','JTF_TASK_INVALID_TASK_ID');
3460 fnd_message.set_token('P_TASK_ID', p_task_id);
3461 fnd_msg_pub.add;
3462 x_return_status := fnd_api.g_ret_sts_unexp_error;
3463 RAISE fnd_api.g_exc_unexpected_error;
3464 END IF;
3465 CLOSE c_task;
3466
3467 UPDATE jtf_task_recur_rules
3468 SET end_date_active = TRUNC(rec_task.calendar_start_date)
3469 , occurs_number = rec_task.occurs_number
3470 WHERE recurrence_rule_id = p_recurrence_rule_id;
3471 END IF;
3472
3473 --------------------------------------------------
3474 -- Create a new recurrence with l_new_task_id
3475 --------------------------------------------------
3476 create_task_recurrence (
3477 p_api_version => 1.0,
3478 p_init_msg_list => fnd_api.g_false,
3479 p_commit => fnd_api.g_false,
3480 p_task_id => l_new_task_id,
3481 p_occurs_which => p_occurs_which,
3482 p_day_of_week => p_day_of_week,
3483 p_date_of_month => p_date_of_month,
3484 p_occurs_month => p_occurs_month,
3485 p_occurs_uom => p_occurs_uom,
3486 p_occurs_every => p_occurs_every,
3487 p_occurs_number => p_occurs_number,
3488 p_start_date_active => l_repeat_start_date, -- To fix bug 2721278
3489 p_end_date_active => p_end_date_active,
3490 p_template_flag => p_template_flag,
3491 x_return_status => x_return_status,
3492 x_msg_count => x_msg_count,
3493 x_msg_data => x_msg_data,
3494 x_recurrence_rule_id => x_new_recurrence_rule_id,
3495 x_task_rec => l_task_details_rec,
3496 x_output_dates_counter => l_output_dates_counter,
3497 p_attribute1 => p_attribute1,
3498 p_attribute2 => p_attribute2,
3499 p_attribute3 => p_attribute3,
3500 p_attribute4 => p_attribute4,
3501 p_attribute5 => p_attribute5,
3502 p_attribute6 => p_attribute6,
3503 p_attribute7 => p_attribute7,
3504 p_attribute8 => p_attribute8,
3505 p_attribute9 => p_attribute9,
3506 p_attribute10 => p_attribute10,
3507 p_attribute11 => p_attribute11,
3508 p_attribute12 => p_attribute12,
3509 p_attribute13 => p_attribute13,
3510 p_attribute14 => p_attribute14,
3511 p_attribute15 => p_attribute15,
3512 p_attribute_category => p_attribute_category,
3513 p_sunday => p_sunday,
3514 p_monday => p_monday,
3515 p_tuesday => p_tuesday,
3516 p_wednesday => p_wednesday,
3517 p_thursday => p_thursday,
3518 p_friday => p_friday,
3519 p_saturday => p_saturday
3520 );
3521 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
3522 THEN
3523 x_return_status := fnd_api.g_ret_sts_unexp_error;
3524 RAISE fnd_api.g_exc_unexpected_error;
3525 END IF;
3526
3527 ------------------------------------------------------------
3528 -- Update sync mapping table if this task is the first one
3529 ------------------------------------------------------------
3530 IF l_first_task
3531 THEN
3532 UPDATE jta_sync_task_mapping
3533 SET task_id = l_new_task_id
3534 WHERE task_id = p_task_id;
3535 END IF;
3536
3537 IF fnd_api.to_boolean (p_commit)
3538 THEN
3539 COMMIT WORK;
3540 END IF;
3541
3542 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3543 EXCEPTION
3544 WHEN fnd_api.g_exc_unexpected_error
3545 THEN
3546 ROLLBACK TO update_task_recurrence_pvt;
3547 x_return_status := fnd_api.g_ret_sts_unexp_error;
3548 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3549 WHEN OTHERS
3550 THEN
3551 ROLLBACK TO update_task_recurrence_pvt;
3552 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
3553 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
3554 fnd_msg_pub.add;
3555 x_return_status := fnd_api.g_ret_sts_unexp_error;
3556 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3557 END update_task_recurrence;
3558
3559 END; --CREATE OR REPLACE PACKAGE