[Home] [Help]
PACKAGE BODY: APPS.JTF_TASK_REPEAT_ASSIGNMENT_PVT
Source
1 PACKAGE BODY jtf_task_repeat_assignment_pvt AS
2 /* $Header: jtfvtkcb.pls 120.5 2010/06/08 09:34:11 anangupt ship $ */
3 /*======================================================================+
4 | Copyright (c) 1995 Oracle Corporation Redwood Shores, California, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 | FILENAME |
8 | jtftkcb.pls |
9 | |
10 | DESCRIPTION |
11 | This package is used to process the repsone of assignee's response |
12 | in repeating appointment. |
13 | The assignee can accept or reject either a specific appointment or |
14 | all the appointments among repeating appointments. |
15 | |
16 | Action assignment_status_id |
17 | ========== ====================== |
18 | REJECT ALL 4 |
19 | ACCEPT ALL 3 |
20 | |
21 | The possible value for add_option: |
22 | T: Add a new invitee to all the future appointments |
23 | A: Add a new invitee to all appointments |
24 | F: Add a new invitee to the current selected appointment only |
25 | N: Skip the new functionality |
26 | |
27 | The possible value for delete_option: |
28 | T: Delete a new invitee from all the future appointments |
29 | A: Delete a new invitee from all appointments |
30 | F: Delete a new invitee from the current selected appointment |
31 | N: Skip the new functionality |
32 | |
33 | NOTES |
34 | |
35 | Date Developer Change |
36 |------ --------------- ---------------------------------------|
37 | 28-Mar-2002 cjang Created |
38 | 29-Mar-2002 cjang Added response_invitation_rec |
39 | add_assignee_rec |
40 | delete_assignee_rec |
41 | add_assignee_rec |
42 | add_assignee_rec |
43 | add_assignee |
44 | delete_assignee |
45 | Modified response_invitation |
46 | 02-Apr-2002 cjang Modified |
47 | 03-Apr-2002 cjang Fixed so as to update last_update_date |
48 | 09-Apr-2002 cjang Update object_changed_date with SYSDATE|
49 | in jtf_tasks_b |
50 | 10-Apr-2002 cjang A user is NOT allowed to accept one of |
51 | occurrences. |
52 | He/She can either accept all or reject all.|
53 | The "update_all" and "calendar_start_date" |
54 | in response_invitation_rec is removed. |
55 | 28-Apr-2002 cjang Modified the package name to refer the |
56 | followings: |
57 | - is_this_first_task |
58 | - get_new_first_taskid |
59 | - exist_syncid |
60 | from jtf_task_utl to jta_sync_task_utl |
61 *=======================================================================*/
62
63 PROCEDURE response_invitation(
64 p_api_version IN NUMBER,
65 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
66 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
67 p_object_version_number IN OUT NOCOPY NUMBER,
68 p_response_invitation_rec IN response_invitation_rec,
69 x_return_status OUT NOCOPY VARCHAR2,
70 x_msg_count OUT NOCOPY NUMBER,
71 x_msg_data OUT NOCOPY VARCHAR2
72 )
73 IS
74 CURSOR c_assignments (b_recurrence_rule_id NUMBER
75 , b_task_assignment_id NUMBER)
76 IS
77 SELECT jtb.task_id
78 , jtaa.task_assignment_id
79 , jtaa.object_version_number
80 FROM jtf_task_all_assignments jtaa
81 , jtf_tasks_b jtb
82 , jtf_task_all_assignments rs
83 WHERE jtb.recurrence_rule_id = b_recurrence_rule_id
84 AND rs.task_assignment_id = b_task_assignment_id
85 AND jtaa.task_id = jtb.task_id
86 AND jtaa.resource_id = rs.resource_id;
87
88 l_object_version_number NUMBER := p_object_version_number;
89 BEGIN
90 SAVEPOINT response_invitation_pvt;
91
92 x_return_status := fnd_api.g_ret_sts_success;
93
94 IF fnd_api.to_boolean (p_init_msg_list)
95 THEN
96 fnd_msg_pub.initialize;
97 END IF;
98
99 ----------------------------------------------------
100 FOR rec_assignments IN c_assignments(b_recurrence_rule_id => p_response_invitation_rec.recurrence_rule_id
101 , b_task_assignment_id => p_response_invitation_rec.task_assignment_id)
102 LOOP
103 l_object_version_number := rec_assignments.object_version_number;
104
105 jtf_task_assignments_pvt.g_response_flag := jtf_task_utl.g_yes_char;
106
107 jtf_task_assignments_pvt.update_task_assignment (
108 p_api_version => p_api_version,
109 p_object_version_number => l_object_version_number,
110 p_init_msg_list => fnd_api.g_true,
111 p_commit => fnd_api.g_false,
112 p_task_assignment_id => rec_assignments.task_assignment_id,
113 p_assignment_status_id => p_response_invitation_rec.assignment_status_id,
114 x_return_status => x_return_status,
115 x_msg_count => x_msg_count,
116 x_msg_data => x_msg_data,
117 p_enable_workflow => 'N',
118 p_abort_workflow => 'N'
119 );
120
121 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
122 THEN
123 x_return_status := fnd_api.g_ret_sts_unexp_error;
124 RAISE fnd_api.g_exc_unexpected_error;
125 END IF;
126
127 IF p_response_invitation_rec.task_id = rec_assignments.task_id
128 THEN
129 p_object_version_number := l_object_version_number;
130 END IF;
131 END LOOP;
132 ----------------------------------------------------
133
134 IF fnd_api.to_boolean (p_commit)
135 THEN
136 COMMIT WORK;
137 END IF;
138
139 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
140 EXCEPTION
141 WHEN fnd_api.g_exc_unexpected_error
142 THEN
143 ROLLBACK TO response_invitation_pvt;
144 x_return_status := fnd_api.g_ret_sts_unexp_error;
145 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
146 WHEN OTHERS
147 THEN
148 ROLLBACK TO response_invitation_pvt;
149 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
150 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
151 fnd_msg_pub.add;
152 x_return_status := fnd_api.g_ret_sts_unexp_error;
153 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
154 END response_invitation;
155
156 PROCEDURE add_assignee(
157 p_api_version IN NUMBER,
158 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
159 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
160 p_add_assignee_rec IN add_assignee_rec,
161 x_return_status OUT NOCOPY VARCHAR2,
162 x_msg_count OUT NOCOPY NUMBER,
163 x_msg_data OUT NOCOPY VARCHAR2,
164 x_task_assignment_id OUT NOCOPY NUMBER
165 )
166 IS
167 CURSOR c_tasks (b_recurrence_rule_id NUMBER
168 ,b_calendar_start_date DATE
169 ,b_add_option VARCHAR2)
170 IS
171 SELECT task_id
172 , calendar_start_date, calendar_end_date
173 FROM jtf_tasks_b
174 WHERE recurrence_rule_id = b_recurrence_rule_id
175 AND ((b_add_option = JTF_TASK_REPEAT_APPT_PVT.G_ALL) OR
176 (b_add_option = JTF_TASK_REPEAT_APPT_PVT.G_FUTURE AND calendar_start_date >= b_calendar_start_date) OR
177 (b_add_option = JTF_TASK_REPEAT_APPT_PVT.G_ONE AND calendar_start_date = b_calendar_start_date));
178
179 l_task_exclusion_id NUMBER;
180
181 CURSOR c_recur (b_recurrence_rule_id NUMBER) IS
182 SELECT *
183 FROM jtf_task_recur_rules
184 WHERE recurrence_rule_id = b_recurrence_rule_id;
185
186 rec_recur c_recur%ROWTYPE;
187
188 l_rowid ROWID;
189 l_new_recurrence_rule_id NUMBER := NULL;
190 l_new_minimum_task_id NUMBER := NULL;
191 l_first BOOLEAN := FALSE;
192 l_exist_new_first_task BOOLEAN := FALSE;
193 l_sync_id NUMBER;
194
195 l_add_option VARCHAR2(1) := p_add_assignee_rec.add_option;
196 l_availability VARCHAR2(1) := 'F';
197 l_resource_type_code VARCHAR2(30);
198 l_resource VARCHAR2(200);
199 l_name VARCHAR2(200);
200 l_date VARCHAR2(200);
201 BEGIN
202 SAVEPOINT add_assignee_pvt;
203
204 x_return_status := fnd_api.g_ret_sts_success;
205
206 IF fnd_api.to_boolean (p_init_msg_list)
207 THEN
208 fnd_msg_pub.initialize;
209 END IF;
210
211 ----------------------------------------------------------
212 -- Check whether the current task_id is the first task_id
213 -- which has been synced
214 ----------------------------------------------------------
215 l_first := jta_sync_task_utl.is_this_first_task(p_task_id => p_add_assignee_rec.task_id);
216
217 -----------------------------------
218 -- Get new minimum task id
219 -----------------------------------
220 l_new_minimum_task_id := jta_sync_task_utl.get_new_first_taskid(
221 p_calendar_start_date => p_add_assignee_rec.calendar_start_date,
222 p_recurrence_rule_id => p_add_assignee_rec.recurrence_rule_id
223 );
224 IF l_new_minimum_task_id > 0
225 THEN
226 l_exist_new_first_task := TRUE;
227 END IF;
228
229 -----------------------------------
230 -- Check if this is the last one
231 -----------------------------------
232 IF (l_first AND NOT l_exist_new_first_task) OR
233 (l_first AND l_add_option = JTF_TASK_REPEAT_APPT_PVT.G_FUTURE)
234 THEN
235 -- This repeating rule has only one appointment currently OR
236 -- A user selected the first task one and
237 -- chose the option "Add this new invitee into all the future appointments"
238 l_add_option := JTF_TASK_REPEAT_APPT_PVT.G_ALL;
239 END IF;
240
241 IF l_add_option = JTF_TASK_REPEAT_APPT_PVT.G_FUTURE
242 THEN
243 -----------------------------------------------------------------
244 -- Create a new repeating rule (use recurrence table handler)
245 -----------------------------------------------------------------
246 OPEN c_recur (p_add_assignee_rec.recurrence_rule_id);
247 FETCH c_recur INTO rec_recur;
248 IF c_recur%NOTFOUND
249 THEN
250 CLOSE c_recur;
251 fnd_message.set_name ('JTF', 'JTF_TK_INVALID_RECUR_RULE');
252 fnd_message.set_token ('P_TASK_RECURRENCE_RULE_ID', p_add_assignee_rec.recurrence_rule_id);
253 fnd_msg_pub.add;
254
255 x_return_status := fnd_api.g_ret_sts_unexp_error;
256 RAISE fnd_api.g_exc_unexpected_error;
257 END IF;
258 CLOSE c_recur;
259
260 SELECT jtf_task_recur_rules_s.NEXTVAL
261 INTO l_new_recurrence_rule_id
262 FROM dual;
263
264 jtf_task_recur_rules_pkg.insert_row (
265 x_rowid => l_rowid,
266 x_recurrence_rule_id => l_new_recurrence_rule_id,
267 x_occurs_which => rec_recur.occurs_which,
268 x_day_of_week => rec_recur.day_of_week,
269 x_date_of_month => rec_recur.date_of_month,
270 x_occurs_month => rec_recur.occurs_month,
271 x_occurs_uom => rec_recur.occurs_uom,
272 x_occurs_every => rec_recur.occurs_every,
273 x_occurs_number => rec_recur.occurs_number,
274 x_start_date_active => trunc(p_add_assignee_rec.calendar_start_date), -- New start date
275 x_end_date_active => rec_recur.end_date_active,
276 x_attribute1 => rec_recur.attribute1 ,
277 x_attribute2 => rec_recur.attribute2 ,
278 x_attribute3 => rec_recur.attribute3 ,
279 x_attribute4 => rec_recur.attribute4 ,
280 x_attribute5 => rec_recur.attribute5 ,
281 x_attribute6 => rec_recur.attribute6 ,
282 x_attribute7 => rec_recur.attribute7 ,
283 x_attribute8 => rec_recur.attribute8 ,
284 x_attribute9 => rec_recur.attribute9 ,
285 x_attribute10 => rec_recur.attribute10 ,
286 x_attribute11 => rec_recur.attribute11 ,
287 x_attribute12 => rec_recur.attribute12 ,
288 x_attribute13 => rec_recur.attribute13 ,
289 x_attribute14 => rec_recur.attribute14 ,
290 x_attribute15 => rec_recur.attribute15,
291 x_attribute_category => rec_recur.attribute_category ,
292 x_creation_date => SYSDATE,
293 x_created_by => jtf_task_utl.created_by,
294 x_last_update_date => SYSDATE,
295 x_last_updated_by => jtf_task_utl.updated_by,
296 x_last_update_login => fnd_global.login_id,
297 x_sunday => rec_recur.sunday,
298 x_monday => rec_recur.monday,
299 x_tuesday => rec_recur.tuesday,
300 x_wednesday => rec_recur.wednesday,
301 x_thursday => rec_recur.thursday,
302 x_friday => rec_recur.friday,
303 x_saturday => rec_recur.saturday,
304 x_date_selected => rec_recur.date_selected
305 );
306 END IF;
307
308 FOR rec_tasks IN c_tasks (b_recurrence_rule_id => p_add_assignee_rec.recurrence_rule_id
309 ,b_calendar_start_date=> p_add_assignee_rec.calendar_start_date
310 ,b_add_option => l_add_option)
311 LOOP
312
313 IF l_add_option = JTF_TASK_REPEAT_APPT_PVT.G_ONE AND
314 l_first AND
315 l_exist_new_first_task
316 THEN
317 ---------------------------------------------------
318 -- Update mapping table with new minimum task id
319 -- if this is the first one and not the last one
320 ---------------------------------------------------
321 IF jta_sync_task_utl.exist_syncid(
322 p_task_id => rec_tasks.task_id,
323 x_sync_id => l_sync_id)
324 THEN
325 jta_sync_task_map_pkg.update_row (
326 p_task_sync_id => l_sync_id,
327 p_task_id => l_new_minimum_task_id,
328 p_resource_id => p_add_assignee_rec.resource_id
329 );
330 END IF;
331 END IF;
332
333 IF l_add_option = JTF_TASK_REPEAT_APPT_PVT.G_FUTURE OR
334 l_add_option = JTF_TASK_REPEAT_APPT_PVT.G_ONE
335 THEN
336 --------------------------------------------
337 -- Insert this appt into exclusion table
338 --------------------------------------------
339 SELECT jta_task_exclusions_s.NEXTVAL
340 INTO l_task_exclusion_id
341 FROM DUAL;
342
343 jta_task_exclusions_pkg.insert_row (
344 p_task_exclusion_id => l_task_exclusion_id,
345 p_task_id => rec_tasks.task_id,
346 p_recurrence_rule_id => p_add_assignee_rec.recurrence_rule_id,
347 p_exclusion_date => rec_tasks.calendar_start_date
348 );
349
350 --------------------------------------------------------
351 -- l_new_recurrence_rule_id has the following value
352 -- 1) NULL if option = JTF_TASK_REPEAT_APPT_PVT.G_ONE
353 -- 2) new recurrence rule id if option = JTF_TASK_REPEAT_APPT_PVT.G_FUTURE
354 --------------------------------------------------------
355 UPDATE jtf_tasks_b
356 SET recurrence_rule_id = l_new_recurrence_rule_id
357 , object_changed_date = SYSDATE
358 WHERE task_id = rec_tasks.task_id;
359 END IF;
360
361 ----------------------
362 -- Add a new invitee
363 ----------------------
364 if ( l_availability = 'F') THEN
365 if ( p_add_assignee_rec.resource_type_code ='PN_LOCATION') THEN
366 cac_avlblty_pub.IS_AVAILABLE(
367 p_api_version => 1.0,
368 p_init_msg_list => 'F',
369 p_Object_Type => p_add_assignee_rec.resource_type_code,
370 p_Object_ID => p_add_assignee_rec.resource_id,
371 p_Start_Date_Time => rec_tasks.calendar_start_date,
372 p_End_Date_Time => rec_tasks.calendar_end_date,
373 p_Schedule_Category => null ,
374 p_Busy_Tentative => p_add_assignee_rec.free_busy_type,
375 p_task_assignment_id => null,
376 x_Available => l_availability,
377 x_return_status => x_return_status,
378 x_msg_count => x_msg_count,
379 x_msg_data => x_msg_data);
380 else
381 l_availability :='T';
382 end if;
383 end if;
384
385 IF( l_availability ='T') THEN
386 jtf_task_assignments_pvt.create_task_assignment (
387 p_api_version => 1.0,
388 p_init_msg_list => fnd_api.g_false,
389 p_commit => fnd_api.g_false,
390 p_task_id => rec_tasks.task_id,
391 p_resource_type_code => p_add_assignee_rec.resource_type_code,
392 p_resource_id => p_add_assignee_rec.resource_id,
393 p_free_busy_type => p_add_assignee_rec.free_busy_type,
394 p_assignment_status_id => p_add_assignee_rec.assignment_status_id,
395 p_add_option => NULL,
396 p_enable_workflow => 'N',
397 p_abort_workflow => 'N',
398 x_return_status => x_return_status,
399 x_msg_count => x_msg_count,
400 x_msg_data => x_msg_data,
401 x_task_assignment_id => x_task_assignment_id
402 );
403
404 l_availability := 'F';
405
406 IF x_return_status <> fnd_api.g_ret_sts_success THEN
407 x_return_status := fnd_api.g_ret_sts_unexp_error;
408 RAISE fnd_api.g_exc_unexpected_error;
409 END IF;
410 else
411 l_name := null;
412 l_name := JTF_TASK_UTL.get_owner(p_add_assignee_rec.resource_type_code, p_add_assignee_rec.resource_id);
413 l_date := rec_tasks.calendar_start_date;
414 IF(fnd_profile.Value('ENABLE_TIMEZONE_CONVERSIONS') = 'Y') THEN
415 l_date := To_Char(CAC_AVLBLTY_PVT.ADJUST_FOR_TIMEZONE(fnd_profile.value('SERVER_TIMEZONE_ID'),
416 fnd_profile.value('CLIENT_TIMEZONE_ID'),
417 rec_tasks.calendar_start_date),
418 fnd_profile.Value('ICX_DATE_FORMAT_MASK')||' HH:MI:SS AM');
419 END IF;
420
421
422 fnd_message.set_name ('JTF', 'JTF_APPT_RES_NOT_AVAILABLE');
423 fnd_message.set_token ('RES', l_name );
424 fnd_message.set_token ('DATE', l_date);
425
426 fnd_msg_pub.add;
427 x_return_status := fnd_api.g_ret_sts_unexp_error;
428
429 RAISE fnd_api.g_exc_unexpected_error;
430
431
432
433 END IF;
434
435 END LOOP;
436 ----------------------------------------------------
437
438 IF fnd_api.to_boolean (p_commit)
439 THEN
440 COMMIT WORK;
441 END IF;
442
443 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
444 EXCEPTION
445 WHEN fnd_api.g_exc_unexpected_error
446 THEN
447 ROLLBACK TO add_assignee_pvt;
448 x_return_status := fnd_api.g_ret_sts_unexp_error;
449 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
450 WHEN OTHERS
451 THEN
452 ROLLBACK TO add_assignee_pvt;
453 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
454 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
455 fnd_msg_pub.add;
456 x_return_status := fnd_api.g_ret_sts_unexp_error;
457 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
458 END add_assignee;
459
460 PROCEDURE delete_assignee(
461 p_api_version IN NUMBER,
462 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
463 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
464 p_delete_assignee_rec IN delete_assignee_rec,
465 x_return_status OUT NOCOPY VARCHAR2,
466 x_msg_count OUT NOCOPY NUMBER,
467 x_msg_data OUT NOCOPY VARCHAR2
468 )
469 IS
470 CURSOR c_assignments (b_recurrence_rule_id NUMBER
471 ,b_calendar_start_date DATE
472 ,b_resource_id NUMBER
473 ,b_delete_option VARCHAR2)
474 IS
475 SELECT jtaa.task_assignment_id
476 , jtaa.object_version_number
477 , jtaa.task_id
478 , jtb.calendar_start_date
479 FROM jtf_task_all_assignments jtaa
480 , jtf_tasks_b jtb
481 WHERE jtb.recurrence_rule_id = b_recurrence_rule_id
482 AND ((b_delete_option = JTF_TASK_REPEAT_APPT_PVT.G_ALL) OR
483 (b_delete_option = JTF_TASK_REPEAT_APPT_PVT.G_ONE AND jtb.calendar_start_date = b_calendar_start_date) OR
484 (b_delete_option = JTF_TASK_REPEAT_APPT_PVT.G_FUTURE AND jtb.calendar_start_date >= b_calendar_start_date))
485 AND jtaa.task_id = jtb.task_id
486 AND jtaa.resource_id = b_resource_id;
487
488 l_task_exclusion_id NUMBER;
489
490 CURSOR c_recur (b_recurrence_rule_id NUMBER) IS
491 SELECT *
492 FROM jtf_task_recur_rules
493 WHERE recurrence_rule_id = b_recurrence_rule_id;
494
495 rec_recur c_recur%ROWTYPE;
496
497 l_rowid ROWID;
498 l_new_recurrence_rule_id NUMBER := NULL;
499 l_new_minimum_task_id NUMBER := NULL;
500 l_first BOOLEAN := FALSE;
501 l_exist_new_first_task BOOLEAN := FALSE;
502 l_sync_id NUMBER;
503
504 l_delete_option VARCHAR2(1) := p_delete_assignee_rec.delete_option;
505 BEGIN
506 SAVEPOINT delete_assignee_pvt;
507
508 x_return_status := fnd_api.g_ret_sts_success;
509
510 IF fnd_api.to_boolean (p_init_msg_list)
511 THEN
512 fnd_msg_pub.initialize;
513 END IF;
514
515 ----------------------------------------------------
516 ----------------------------------------------------------
517 -- Check whether the current task_id is the first task_id
518 -- which has been synced
519 ----------------------------------------------------------
520 l_first := jta_sync_task_utl.is_this_first_task(
521 p_task_id => p_delete_assignee_rec.task_id
522 );
523
524 -----------------------------------
525 -- Get new minimum task id
526 -----------------------------------
527 l_new_minimum_task_id := jta_sync_task_utl.get_new_first_taskid(
528 p_calendar_start_date => p_delete_assignee_rec.calendar_start_date,
529 p_recurrence_rule_id => p_delete_assignee_rec.recurrence_rule_id
530 );
531 IF l_new_minimum_task_id > 0
532 THEN
533 l_exist_new_first_task := TRUE;
534 END IF;
535
536 -----------------------------------
537 -- Check if this is the last one
538 -----------------------------------
539 IF (l_first AND NOT l_exist_new_first_task) OR
540 (l_first AND l_delete_option = JTF_TASK_REPEAT_APPT_PVT.G_FUTURE)
541 THEN
542 -- This repeating rule has only one appointment currently OR
543 -- A user selected the first task one and
544 -- chose the option "Delete this invitee from all the future appointments"
545 l_delete_option := JTF_TASK_REPEAT_APPT_PVT.G_ALL;
546 END IF;
547
548 IF l_delete_option = JTF_TASK_REPEAT_APPT_PVT.G_FUTURE
549 THEN
550 -----------------------------------------------------------------
551 -- Create a new repeating rule (use recurrence table handler)
552 -----------------------------------------------------------------
553 OPEN c_recur (p_delete_assignee_rec.recurrence_rule_id);
554 FETCH c_recur INTO rec_recur;
555 IF c_recur%NOTFOUND
556 THEN
557 CLOSE c_recur;
558 fnd_message.set_name ('JTF', 'JTF_TK_INVALID_RECUR_RULE');
559 fnd_message.set_token ('P_TASK_RECURRENCE_RULE_ID', p_delete_assignee_rec.recurrence_rule_id);
560 fnd_msg_pub.add;
561
562 x_return_status := fnd_api.g_ret_sts_unexp_error;
563 RAISE fnd_api.g_exc_unexpected_error;
564 END IF;
565 CLOSE c_recur;
566
567 SELECT jtf_task_recur_rules_s.NEXTVAL
568 INTO l_new_recurrence_rule_id
569 FROM dual;
570
571 jtf_task_recur_rules_pkg.insert_row (
572 x_rowid => l_rowid,
573 x_recurrence_rule_id => l_new_recurrence_rule_id,
574 x_occurs_which => rec_recur.occurs_which,
575 x_day_of_week => rec_recur.day_of_week,
576 x_date_of_month => rec_recur.date_of_month,
577 x_occurs_month => rec_recur.occurs_month,
578 x_occurs_uom => rec_recur.occurs_uom,
579 x_occurs_every => rec_recur.occurs_every,
580 x_occurs_number => rec_recur.occurs_number,
581 x_start_date_active => trunc(p_delete_assignee_rec.calendar_start_date), -- New start date
582 x_end_date_active => rec_recur.end_date_active,
583 x_attribute1 => rec_recur.attribute1 ,
584 x_attribute2 => rec_recur.attribute2 ,
585 x_attribute3 => rec_recur.attribute3 ,
586 x_attribute4 => rec_recur.attribute4 ,
587 x_attribute5 => rec_recur.attribute5 ,
588 x_attribute6 => rec_recur.attribute6 ,
589 x_attribute7 => rec_recur.attribute7 ,
590 x_attribute8 => rec_recur.attribute8 ,
591 x_attribute9 => rec_recur.attribute9 ,
592 x_attribute10 => rec_recur.attribute10 ,
593 x_attribute11 => rec_recur.attribute11 ,
594 x_attribute12 => rec_recur.attribute12 ,
595 x_attribute13 => rec_recur.attribute13 ,
596 x_attribute14 => rec_recur.attribute14 ,
597 x_attribute15 => rec_recur.attribute15,
598 x_attribute_category => rec_recur.attribute_category ,
599 x_creation_date => SYSDATE,
600 x_created_by => jtf_task_utl.created_by,
601 x_last_update_date => SYSDATE,
602 x_last_updated_by => jtf_task_utl.updated_by,
603 x_last_update_login => fnd_global.login_id,
604 x_sunday => rec_recur.sunday,
605 x_monday => rec_recur.monday,
606 x_tuesday => rec_recur.tuesday,
607 x_wednesday => rec_recur.wednesday,
608 x_thursday => rec_recur.thursday,
609 x_friday => rec_recur.friday,
610 x_saturday => rec_recur.saturday,
611 x_date_selected => rec_recur.date_selected
612 );
613 END IF;
614
615 FOR rec_assignments IN c_assignments (b_recurrence_rule_id => p_delete_assignee_rec.recurrence_rule_id
616 ,b_calendar_start_date => p_delete_assignee_rec.calendar_start_date
617 ,b_resource_id => p_delete_assignee_rec.resource_id
618 ,b_delete_option => l_delete_option)
619 LOOP
620 IF l_delete_option = JTF_TASK_REPEAT_APPT_PVT.G_ONE AND
621 l_first AND
622 l_exist_new_first_task
623 THEN
624 ---------------------------------------------------
625 -- Update mapping table with new minimum task id
626 -- if this is the first one and not the last one
627 ---------------------------------------------------
628 IF jta_sync_task_utl.exist_syncid(
629 p_task_id => rec_assignments.task_id,
630 x_sync_id => l_sync_id)
631 THEN
632 jta_sync_task_map_pkg.update_row (
633 p_task_sync_id => l_sync_id,
634 p_task_id => l_new_minimum_task_id,
635 p_resource_id => p_delete_assignee_rec.resource_id
636 );
637 END IF;
638 END IF;
639
640 IF l_delete_option = JTF_TASK_REPEAT_APPT_PVT.G_FUTURE OR
641 l_delete_option = JTF_TASK_REPEAT_APPT_PVT.G_ONE
642 THEN
643 --------------------------------------------
644 -- Insert this appt into exclusion table
645 --------------------------------------------
646 SELECT jta_task_exclusions_s.NEXTVAL
647 INTO l_task_exclusion_id
648 FROM DUAL;
649
650 jta_task_exclusions_pkg.insert_row (
651 p_task_exclusion_id => l_task_exclusion_id,
652 p_task_id => rec_assignments.task_id,
653 p_recurrence_rule_id => p_delete_assignee_rec.recurrence_rule_id,
654 p_exclusion_date => rec_assignments.calendar_start_date
655 );
656
657 --------------------------------------------------------
658 -- l_new_recurrence_rule_id has the following value
659 -- 1) NULL if option = JTF_TASK_REPEAT_APPT_PVT.G_ONE
660 -- 2) new recurrence rule id if option = JTF_TASK_REPEAT_APPT_PVT.G_FUTURE
661 --------------------------------------------------------
662 UPDATE jtf_tasks_b
663 SET recurrence_rule_id = l_new_recurrence_rule_id
664 , object_changed_date = SYSDATE
665 WHERE task_id = rec_assignments.task_id;
666 END IF;
667
668 ----------------------
669 -- Delete this invitee
670 ----------------------
671 jtf_task_assignments_pvt.delete_task_assignment (
672 p_api_version => 1.0,
673 p_init_msg_list => fnd_api.g_false,
674 p_commit => fnd_api.g_false,
675 p_task_assignment_id => rec_assignments.task_assignment_id,
676 p_object_version_number => rec_assignments.object_version_number,
677 p_delete_option => NULL,
678 p_enable_workflow => 'N',
679 p_abort_workflow => 'N',
680 x_return_status => x_return_status,
681 x_msg_count => x_msg_count,
682 x_msg_data => x_msg_data
683 );
684 IF x_return_status <> fnd_api.g_ret_sts_success THEN
685 x_return_status := fnd_api.g_ret_sts_unexp_error;
686 RAISE fnd_api.g_exc_unexpected_error;
687 END IF;
688 END LOOP;
689 ----------------------------------------------------
690
691 IF fnd_api.to_boolean (p_commit)
692 THEN
693 COMMIT WORK;
694 END IF;
695
696 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
697 EXCEPTION
698 WHEN fnd_api.g_exc_unexpected_error
699 THEN
700 ROLLBACK TO delete_assignee_pvt;
701 x_return_status := fnd_api.g_ret_sts_unexp_error;
702 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
703 WHEN OTHERS
704 THEN
705 ROLLBACK TO delete_assignee_pvt;
706 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
707 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
708 fnd_msg_pub.add;
709 x_return_status := fnd_api.g_ret_sts_unexp_error;
710 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
711 END delete_assignee;
712
713 END jtf_task_repeat_assignment_pvt;