[Home] [Help]
PACKAGE BODY: APPS.IEU_TASKS_USERHOOKS
Source
1 PACKAGE body IEU_TASKS_USERHOOKS AS
2 /* $Header: IEUVTUHB.pls 120.12 2006/04/28 05:19:56 nveerara ship $ */
3
4
5 l_task_source_obj_type_code VARCHAR2(500);
6 l_del_task_id NUMBER;
7
8 l_object_code VARCHAR2(5);
9 l_not_valid_flag varchar2(5);
10 l_workitem_obj_code VARCHAR2(30);
11 l_owner_type_actual VARCHAR2(30);
12
13 PROCEDURE create_task_uwqm_pre ( x_return_status OUT NOCOPY VARCHAR2 ) As
14
15 l_work_item_id NUMBER;
16 l_msg_count NUMBER;
17 l_msg_data VARCHAR2(2000);
18 l_return_status varchar2(5);
19 l_assignee_id number := null;
20 l_assignee_type varchar2(30) := null;
21 l_due_date date;
22 l_priority_code varchar2(30);
23 l_importance_level number;
24 l_task_status varchar2(30);
25 l_task_status_id number := null;
26 l_ws_id1 NUMBER;
27 l_ws_id2 NUMBER := null;
28 l_association_ws_id NUMBER;
29 l_dist_from IEU_UWQM_WORK_SOURCES_B.DISTRIBUTE_FROM%TYPE;
30 l_dist_to IEU_UWQM_WORK_SOURCES_B.DISTRIBUTE_TO%TYPE;
31 l_tasks_rules_func VARCHAR2(500);
32
33 l_tasks_data_list SYSTEM.WR_TASKS_DATA_NST;
34 l_def_data_list SYSTEM.DEF_WR_DATA_NST;
35 l_orig_grp_owner NUMBER;
36
37 l_association_ws_code varchar2(32);
38 l_activation_status varchar2(5);
39
40 l_audit_trail_rec SYSTEM.WR_AUDIT_TRAIL_NST;
41 l_workitem_comment_code1 varchar2(100) := null;
42 l_workitem_comment_code2 varchar2(100) := null;
43 l_workitem_comment_code3 varchar2(100) := null;
44 begin
45
46 l_priority_code := 'LOW';
47 l_dist_from := 'GROUP_OWNED';
48 l_dist_to := 'INDIVIDUAL_ASSIGNED';
49 -- reset del task pkg lvl variable
50 l_del_task_id := null;
51
52 l_object_code := 'TASK';
53 l_not_valid_flag := 'N';
54
55 -- l_workitem_comment_code1 := 'GO_IA';
56 /***** Bookings End Date will be used as Due Date. This is available in Assignee hooks ***************
57 if jtf_tasks_pub.p_task_user_hooks.date_selected = 'P' then
58 l_due_date := jtf_tasks_pub.p_task_user_hooks.planned_end_date;
59 l_workitem_comment_code2 := 'PLAN_DUE_DT';
60 elsif jtf_tasks_pub.p_task_user_hooks.date_selected = 'A' then
61 l_due_date := jtf_tasks_pub.p_task_user_hooks.actual_end_date;
62 l_workitem_comment_code2 := 'ACTUAL_DUE_DT';
63 elsif jtf_tasks_pub.p_task_user_hooks.date_selected = 'S' then
64 l_due_date := jtf_tasks_pub.p_task_user_hooks.scheduled_end_date;
65 l_workitem_comment_code2 := 'SCHD_DUE_DT';
66 elsif jtf_tasks_pub.p_task_user_hooks.date_selected is null then
67 -- Niraj Bug 4609285 Commented following 2 lines. Making default as Scheduled Date in case of Null value
68 -- l_due_date := null;
69 -- l_workitem_comment_code2 := 'NULL_DUE_DT';
70 l_due_date := jtf_tasks_pub.p_task_user_hooks.scheduled_end_date;
71 l_workitem_comment_code2 := 'SCHD_DUE_DT';
72 end if;
73 *****************************************************************************/
74 begin
75 select importance_level
76 into l_importance_level
77 from jtf_task_priorities_vl
78 where task_priority_id = jtf_tasks_pub.p_task_user_hooks.task_priority_id;
79 exception when others then null;
80 end;
81
82 if l_importance_level = 1 then
83 l_workitem_comment_code3 := 'IMP_LEVEL_C';
84 elsif l_importance_level = 2 then
85 l_workitem_comment_code3 := 'IMP_LEVEL_H';
86 elsif l_importance_level = 3 then
87 l_workitem_comment_code3 := 'IMP_LEVEL_M';
88 elsif l_importance_level = 4 then
89 l_workitem_comment_code3 := 'IMP_LEVEL_L';
90 elsif l_importance_level >=5 then
91 l_workitem_comment_code3 := 'IMP_LEVEL_O';
92 end if;
93
94 if l_importance_level < 5 then
95
96 begin
97 select priority_code
98 into l_priority_code
99 from ieu_uwqm_priorities_b
100 where priority_level = l_importance_level;
101 exception when others then null;
102 end;
103
104 elsif l_importance_level >= 5 then
105
106 begin
107 select priority_code
108 into l_priority_code
109 from ieu_uwqm_priorities_b
110 where priority_level = 4;
111 exception when others then null;
112 end;
113
114 end if;
115
116 begin
117 select 'CLOSE' into l_task_status
118 from jtf_task_statuses_vl
119 where (nvl(closed_flag, 'N') = 'Y'
120 or nvl(completed_flag, 'N') = 'Y'
121 or nvl(cancelled_flag, 'N') = 'Y'
122 or nvl(rejected_flag, 'N') = 'Y')
123 and task_status_id = jtf_tasks_pub.p_task_user_hooks.task_status_id;
124 EXCEPTION WHEN others THEN
125 begin
126 select 'SLEEP' into l_task_status
127 from jtf_task_statuses_vl
128 where nvl(on_hold_flag, 'N') = 'Y'
129 and task_status_id = jtf_tasks_pub.p_task_user_hooks.task_status_id;
130 EXCEPTION WHEN others THEN
131 l_task_status := 'OPEN';
132 end;
133 end;
134
135 if (jtf_tasks_pub.p_task_user_hooks.source_object_type_code is not null)
136 then
137
138 BEGIN
139
140 Select ws_id
141 into l_ws_id1
142 from ieu_uwqm_work_sources_b
143 --where object_code = 'TASK'
144 --and nvl(not_valid_flag,'N') = 'N';
145 where object_code = l_object_code
146 and nvl(not_valid_flag,'N') = l_not_valid_flag;
147 EXCEPTION
148 WHEN OTHERS THEN l_ws_id1 := null;
149 END;
150
151 BEGIN
152
153 Select ws_id
154 into l_ws_id2
155 from ieu_uwqm_work_sources_b
156 where object_code = jtf_tasks_pub.p_task_user_hooks.source_object_type_code
157 -- and nvl(not_valid_flag,'N') = 'N';
158 and nvl(not_valid_flag,'N') = l_not_valid_flag;
159 EXCEPTION
160 WHEN OTHERS THEN
161
162 l_ws_id2 := null;
163 END;
164
165 if (l_ws_id2 is not null)
166 then
167
168 -- Check if Any Work Source Association exists for this combination of Object Code/Source Obj Code
169 BEGIN
170
171 SELECT a.ws_id, b.ws_code
172 INTO l_association_ws_id, l_association_ws_code
173 FROM ieu_uwqm_ws_assct_props a, ieu_uwqm_work_sources_b b
174 WHERE child_ws_id = l_ws_id1
175 AND parent_ws_id = l_ws_id2
176 AND a.ws_id = b.ws_id
177 --AND nvl(b.not_valid_flag,'N') = 'N';
178 AND nvl(b.not_valid_flag,'N') = l_not_valid_flag;
179
180 EXCEPTION
181 WHEN NO_DATA_FOUND THEN
182 l_association_ws_id := null;
183 END;
184
185 else
186 l_association_ws_id := null;
187
188 end if;
189
190 if l_association_ws_id is not null then
191 IEU_WR_PUB.CHECK_WS_ACTIVATION_STATUS(
192 p_api_version => 1,
193 p_init_msg_list => 'T',
194 p_commit => 'F',
195 p_ws_code => l_association_ws_code,
196 x_ws_activation_status => l_activation_status,
197 x_msg_count => l_msg_count,
198 x_msg_data => l_msg_data,
199 x_return_status => l_return_status);
200
201 else
202 IEU_WR_PUB.CHECK_WS_ACTIVATION_STATUS(
203 p_api_version => 1,
204 p_init_msg_list => 'T',
205 p_commit => 'F',
206 p_ws_code => 'TASK',
207 x_ws_activation_status => l_activation_status,
208 x_msg_count => l_msg_count,
209 x_msg_data => l_msg_data,
210 x_return_status => l_return_status);
211 end if;
212
213
214 -- Get the Tasks Rules Function
215
216 if (l_association_ws_id is not null)
217 then
218
219 BEGIN
220
221 SELECT ws_b.tasks_rules_function
222 INTO l_tasks_rules_func
223 FROM ieu_uwqm_ws_assct_props ws_b
224 WHERE ws_b.ws_id = l_association_ws_id;
225
226 EXCEPTION
227 WHEN OTHERS THEN
228 l_tasks_rules_func := null;
229 END;
230
231 end if;
232
233 end if; /* source_object_type_code is not null */
234
235 if l_activation_status = 'Y' then
236
237 if (l_tasks_rules_func is not null)
238 then
239
240 l_tasks_data_list := SYSTEM.WR_TASKS_DATA_NST();
241
242 l_tasks_data_list.extend;
243
244 l_tasks_data_list(l_tasks_data_list.last) := SYSTEM.WR_TASKS_DATA_OBJ (
245 'CREATE_TASK',
246 jtf_tasks_pub.p_task_user_hooks.task_id,
247 null,
248 jtf_tasks_pub.p_task_user_hooks.task_number,
249 jtf_tasks_pub.p_task_user_hooks.task_name,
250 jtf_tasks_pub.p_task_user_hooks.task_type_id,
251 jtf_tasks_pub.p_task_user_hooks.task_status_id,
252 jtf_tasks_pub.p_task_user_hooks.task_priority_id,
253 jtf_tasks_pub.p_task_user_hooks.owner_id,
254 jtf_tasks_pub.p_task_user_hooks.owner_type_code,
255 jtf_tasks_pub.p_task_user_hooks.source_object_id,
256 jtf_tasks_pub.p_task_user_hooks.source_object_type_code,
257 jtf_tasks_pub.p_task_user_hooks.customer_id,
258 jtf_tasks_pub.p_task_user_hooks.date_selected,
259 jtf_tasks_pub.p_task_user_hooks.planned_start_date,
260 jtf_tasks_pub.p_task_user_hooks.planned_end_date,
261 jtf_tasks_pub.p_task_user_hooks.scheduled_start_date,
262 jtf_tasks_pub.p_task_user_hooks.scheduled_end_date,
263 jtf_tasks_pub.p_task_user_hooks.actual_start_date,
264 jtf_tasks_pub.p_task_user_hooks.actual_end_date,
265 null,
266 null,
267 null);
268
269 l_def_data_list := SYSTEM.DEF_WR_DATA_NST();
270
271 l_def_data_list.extend;
272
273 -- Get the Group Owner
274
275 BEGIN
276 l_workitem_obj_code := 'TASK';
277 l_owner_type_actual := 'RS_GROUP';
278 Select owner_id
279 into l_orig_grp_owner
280 from ieu_uwqm_items
281 where WORKITEM_PK_ID = jtf_tasks_pub.p_task_user_hooks.task_id
282 -- and workitem_obj_code = 'TASK'
283 and workitem_obj_code = l_workitem_obj_code
284 -- and owner_type_actual = 'RS_GROUP';
285 and owner_type_actual = l_owner_type_actual;
286
287 EXCEPTION
288 when others then
289 l_orig_grp_owner := null;
290 END;
291
292 l_def_data_list(l_def_data_list.last) := SYSTEM.DEF_WR_DATA_OBJ(
293 l_task_status,
294 l_priority_code,
295 l_due_date,
296 'TASKS',
297 l_orig_grp_owner
298 );
299
300 execute immediate
301 'BEGIN '||l_tasks_rules_func ||
302 ' ( :1, :2, :3, :4 , :5); END ; '
303 USING
304 IN l_tasks_data_list, IN l_def_data_list , OUT l_msg_count, OUT l_msg_data, OUT l_return_status;
305
306 x_return_status := l_return_status;
307
308 else
309 -- Create work item only when the task is in Open status else return success
310 If (l_task_status <> 'CLOSE')
311 then
312
313 BEGIN
314
315 l_audit_trail_rec := SYSTEM.WR_AUDIT_TRAIL_NST();
316
317 l_audit_trail_rec.extend;
318
319 l_audit_trail_rec(l_audit_trail_rec.LAST):= SYSTEM.WR_AUDIT_TRAIL_OBJ
320 ('WORKITEM_CREATION',
321 'CREATE_WR_ITEM',
322 690,
323 'IEU_TASKS_USERHOOKS.CREATE_TASK_UWQM_PRE',
324 l_workitem_comment_code1,
325 l_workitem_comment_code2,
326 l_workitem_comment_code3,
327 null,
328 null);
329
330 if jtf_tasks_pub.p_task_user_hooks.entity = 'TASK' then
331
332 IEU_WR_PUB.CREATE_WR_ITEM(
333 p_api_version => 1.0,
334 p_init_msg_list => FND_API.G_TRUE,
335 p_commit => FND_API.G_FALSE,
336 p_workitem_obj_code => 'TASK',
337 p_workitem_pk_id => jtf_tasks_pub.p_task_user_hooks.task_id,
338 p_work_item_number => to_number(jtf_tasks_pub.p_task_user_hooks.task_number),
339 p_title => jtf_tasks_pub.p_task_user_hooks.task_name,
340 p_party_id => jtf_tasks_pub.p_task_user_hooks.customer_id,
341 p_priority_code => l_priority_code,
342 p_due_date => l_due_date,
343 p_owner_id => jtf_tasks_pub.p_task_user_hooks.owner_id,
344 p_owner_type => jtf_tasks_pub.p_task_user_hooks.owner_type_code,
345 p_assignee_id => l_assignee_id,
346 p_assignee_type => l_assignee_type,
347 p_source_object_id => jtf_tasks_pub.p_task_user_hooks.source_object_id,
348 p_source_object_type_code => jtf_tasks_pub.p_task_user_hooks.source_object_type_code,
349 p_application_id => 690,
350 p_ieu_enum_type_uuid => 'TASKS',
351 p_work_item_status => l_task_status,
352 p_user_id => FND_GLOBAL.USER_ID,
353 p_login_id => FND_GLOBAL.LOGIN_ID,
354 p_audit_trail_rec => l_audit_trail_rec,
355 x_work_item_id => L_WORK_ITEM_ID,
359
356 x_msg_count => l_msg_count,
357 x_msg_data => L_MSG_DATA,
358 x_return_status => L_RETURN_STATUS);
360 x_return_status := l_return_status;
361
362 else
363 x_return_status := fnd_api.g_ret_sts_success;
364
365 end if;
366
367 EXCEPTION
368 WHEN OTHERS THEN
369 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
370
371 END;
372
373 else -- task is not in Open status, so return success
374 x_return_status := FND_API.G_RET_STS_SUCCESS;
375 end if;
376
377 end if; /* Tasks Rules Func */
378
379 elsif l_activation_status = 'N' then
380 x_return_status := FND_API.G_RET_STS_SUCCESS;
381 end if;
382 end create_task_uwqm_pre;
383
384
385 PROCEDURE update_task_uwqm_pre ( x_return_status OUT NOCOPY VARCHAR2 ) As
386
387 l_work_item_id NUMBER; L_MSG_COUNT NUMBER;
388 l_msg_data VARCHAR2(2000);
389 l_return_status varchar2(5);
390 l_assignee_id number := null;
391 l_assignee_type varchar2(30) := null;
392 l_task_status varchar2(20);
393 l_due_date date;
394 l_priority_code varchar2(30);
395 l_importance_level number;
396 l_task_status_id number;
397 l_cur_task_status varchar2(30);
398 l_count number;
399 l_task_type_id number;
400 l_ws_id1 NUMBER;
401 l_ws_id2 NUMBER := null;
402 l_association_ws_id NUMBER;
403 l_dist_from IEU_UWQM_WORK_SOURCES_B.DISTRIBUTE_FROM%TYPE;
404 l_dist_to IEU_UWQM_WORK_SOURCES_B.DISTRIBUTE_TO%TYPE;
405 l_tasks_rules_func VARCHAR2(500);
406 l_orig_grp_owner NUMBER;
407
408 l_tasks_data_list SYSTEM.WR_TASKS_DATA_NST;
409 l_def_data_list SYSTEM.DEF_WR_DATA_NST;
410
411 l_association_ws_code varchar2(32);
412 l_activation_status varchar2(5);
413
414 l_audit_trail_rec SYSTEM.WR_AUDIT_TRAIL_NST;
415 l_workitem_comment_code1 varchar2(100) := null;
416 l_workitem_comment_code2 varchar2(100) := null;
417 l_workitem_comment_code3 varchar2(100) := null;
418 l_workitem_comment_code4 varchar2(100) := null;
419 l_event_key varchar2(2000);
420
421 l_wr_due_date date;
422 l_wr_priority_id number := null;
423 l_priority_id number;
424
425 l_status_id NUMBER;
426 l_wr_status_id ieu_uwqm_items.status_id%TYPE;
427 l_wr_party_id ieu_uwqm_items.party_id%TYPE;
428 l_wr_owner_id ieu_uwqm_items.owner_id%TYPE;
429 l_wr_title ieu_uwqm_items.title%TYPE;
430 l_wr_owner_type_actual ieu_uwqm_items.owner_type_actual%TYPE;
431 l_wr_source_object_id ieu_uwqm_items.source_object_id%TYPE;
432 l_wr_source_object_type_code ieu_uwqm_items.source_object_type_code%TYPE;
433 l_update_task_reqd_flag VARCHAR2(1);
434
435
436 begin
437
438
439 l_update_task_reqd_flag := 'n';
440 l_priority_code := 'LOW';
441 l_dist_from := 'GROUP_OWNED';
442 l_dist_to := 'INDIVIDUAL_ASSIGNED';
443
444 /*** This procedure will sync up any updates on the Task Work item
445 ** First the Activation Status is checked
446 ** If the Work Source is activated, then check if any Tasks Rules Function is registered
447 ** If the task Rules function is present, then execute it
448 ** If there is not Tasks Rules Function registered, update the work repository item
449 ** In certain cases like Work items in 'Closed' status, the work item may not be present in the work repository
450 ** If the work item does not exist then create them
451 ***/
452
453 -- reset del task pkg lvl variable
454 l_del_task_id := null;
455
456
457 l_object_code := 'TASK';
458 l_not_valid_flag := 'N';
459
460 -- Set this variable as we require this for Task Asg processing
461 l_task_source_obj_type_code := jtf_tasks_pub.p_task_user_hooks.source_object_type_code;
462 if jtf_tasks_pub.p_task_user_hooks.date_selected = 'P' then
463 l_due_date := jtf_tasks_pub.p_task_user_hooks.planned_end_date;
464 l_workitem_comment_code2 := 'PLAN_DUE_DT';
465 elsif jtf_tasks_pub.p_task_user_hooks.date_selected = 'A' then
469 l_due_date := jtf_tasks_pub.p_task_user_hooks.scheduled_end_date;
466 l_due_date := jtf_tasks_pub.p_task_user_hooks.actual_end_date;
467 l_workitem_comment_code2 := 'ACTUAL_DUE_DT';
468 elsif jtf_tasks_pub.p_task_user_hooks.date_selected = 'S' then
470 l_workitem_comment_code2 := 'SCHD_DUE_DT';
471 elsif jtf_tasks_pub.p_task_user_hooks.date_selected is null then
472 -- Niraj Bug 4609285 Commented following 2 lines. Making default as Scheduled Date in case of Null value
473 -- l_due_date := null;
474 -- l_workitem_comment_code2 := 'NULL_DUE_DT';
475 l_due_date := jtf_tasks_pub.p_task_user_hooks.scheduled_end_date;
476 l_workitem_comment_code2 := 'SCHD_DUE_DT';
477 end if;
478
479 begin
480 select importance_level
481 into l_importance_level
482 from jtf_task_priorities_vl
483 where task_priority_id = jtf_tasks_pub.p_task_user_hooks.task_priority_id;
484 exception when others then null;
485 end;
486
487 if l_importance_level < 5 then
488
489 begin
490 select priority_code, priority_id
491 into l_priority_code, l_priority_id
492 from ieu_uwqm_priorities_b
493 where priority_level = l_importance_level;
494 exception when others then null;
495 end;
496
497 elsif l_importance_level >= 5 then
498
499 begin
500 select priority_code, priority_id
501 into l_priority_code, l_priority_id
502 from ieu_uwqm_priorities_b
503 where priority_level = 4;
504 exception when others then null;
505 end;
506
507 end if;
508
509 begin
510 select task_status_id into l_task_status_id from jtf_tasks_b
511 where task_id = jtf_tasks_pub.p_task_user_hooks.task_id;
512 end;
513
514 begin
515 select 'CLOSE' into l_task_status
516 from jtf_task_statuses_vl
517 where (nvl(closed_flag, 'N') = 'Y'
518 or nvl(completed_flag, 'N') = 'Y'
519 or nvl(cancelled_flag, 'N') = 'Y'
520 or nvl(rejected_flag, 'N') = 'Y')
521 and task_status_id = jtf_tasks_pub.p_task_user_hooks.task_status_id;
522 l_status_id := 3;
523 EXCEPTION WHEN others THEN
524 begin
525 select 'SLEEP' into l_task_status
526 from jtf_task_statuses_vl
527 where nvl(on_hold_flag, 'N') = 'Y'
528 and task_status_id = jtf_tasks_pub.p_task_user_hooks.task_status_id;
529 l_status_id := 5;
530 EXCEPTION WHEN others THEN
531 l_task_status := 'OPEN';
532 l_status_id := 0;
533 end;
534 end;
535
536 -- l_workitem_comment_code1 := 'GO_IA';
537
538 if (jtf_tasks_pub.p_task_user_hooks.source_object_type_code is not null)
539 then
540
541 BEGIN
542
543 Select ws_id
544 into l_ws_id1
545 from ieu_uwqm_work_sources_b
546 --where object_code = 'TASK'
547 --and nvl(not_valid_flag,'N') = 'N';
548 where object_code = l_object_code
549 and nvl(not_valid_flag,'N') = l_not_valid_flag;
550 EXCEPTION
551 WHEN OTHERS THEN l_ws_id1 := null;
552 END;
553
554 BEGIN
555
556 Select ws_id
557 into l_ws_id2
558 from ieu_uwqm_work_sources_b
559 where object_code = jtf_tasks_pub.p_task_user_hooks.source_object_type_code
560 --and nvl(not_valid_flag,'N') = 'N';
561 and nvl(not_valid_flag,'N') = l_not_valid_flag;
562 EXCEPTION
563 WHEN OTHERS THEN
564
565 l_ws_id2 := null;
566 END;
567
568 if (l_ws_id2 is not null)
569 then
570
571 -- Check if Any Work Source Association exists for this combination of Object Code/Source Obj Code
572 BEGIN
573
574 SELECT a.ws_id, b.ws_code
575 INTO l_association_ws_id, l_association_ws_code
579 AND a.ws_id = b.ws_id
576 FROM ieu_uwqm_ws_assct_props a, ieu_uwqm_work_sources_b b
577 WHERE child_ws_id = l_ws_id1
578 AND parent_ws_id = l_ws_id2
580 --AND nvl(b.not_valid_flag,'N') = 'N';
581 AND nvl(b.not_valid_flag,'N') = l_not_valid_flag;
582 EXCEPTION
583 WHEN NO_DATA_FOUND THEN
584 l_association_ws_id := null;
585 END;
586
587 else
588 l_association_ws_id := null;
589
590 end if;
591
592 if l_association_ws_id is not null then
593 IEU_WR_PUB.CHECK_WS_ACTIVATION_STATUS(
594 p_api_version => 1,
595 p_init_msg_list => 'T',
596 p_commit => 'F',
597 p_ws_code => l_association_ws_code,
598 x_ws_activation_status => l_activation_status,
599 x_msg_count => l_msg_count,
600 x_msg_data => l_msg_data,
601 x_return_status => l_return_status);
602
603 else
604 IEU_WR_PUB.CHECK_WS_ACTIVATION_STATUS(
605 p_api_version => 1,
606 p_init_msg_list => 'T',
607 p_commit => 'F',
608 p_ws_code => 'TASK',
609 x_ws_activation_status => l_activation_status,
610 x_msg_count => l_msg_count,
611 x_msg_data => l_msg_data,
612 x_return_status => l_return_status);
613 end if;
614
615 -- Get the Tasks Rules Function
616
617 if (l_association_ws_id is not null)
618 then
619
620 BEGIN
621
622 SELECT ws_b.tasks_rules_function
623 INTO l_tasks_rules_func
624 FROM ieu_uwqm_ws_assct_props ws_b
625 WHERE ws_b.ws_id = l_association_ws_id;
626
627 EXCEPTION
628 WHEN OTHERS THEN
629 l_tasks_rules_func := null;
630 END;
631
632 end if;
633
634 end if; /* source_object_type_code is not null */
635
636 if l_activation_status = 'Y' then
637
638
639 if (l_tasks_rules_func is not null)
640 then
641
642 l_tasks_data_list := SYSTEM.WR_TASKS_DATA_NST();
643
644 l_tasks_data_list.extend;
645
646 l_tasks_data_list(l_tasks_data_list.last) := SYSTEM.WR_TASKS_DATA_OBJ (
647 'UPDATE_TASK',
648 jtf_tasks_pub.p_task_user_hooks.task_id,
649 null,
650 jtf_tasks_pub.p_task_user_hooks.task_number,
651 jtf_tasks_pub.p_task_user_hooks.task_name,
652 jtf_tasks_pub.p_task_user_hooks.task_type_id,
653 jtf_tasks_pub.p_task_user_hooks.task_status_id,
654 jtf_tasks_pub.p_task_user_hooks.task_priority_id,
655 jtf_tasks_pub.p_task_user_hooks.owner_id,
656 jtf_tasks_pub.p_task_user_hooks.owner_type_code,
657 jtf_tasks_pub.p_task_user_hooks.source_object_id,
658 jtf_tasks_pub.p_task_user_hooks.source_object_type_code,
659 jtf_tasks_pub.p_task_user_hooks.customer_id,
660 jtf_tasks_pub.p_task_user_hooks.date_selected,
661 jtf_tasks_pub.p_task_user_hooks.planned_start_date,
662 jtf_tasks_pub.p_task_user_hooks.planned_end_date,
663 jtf_tasks_pub.p_task_user_hooks.scheduled_start_date,
664 jtf_tasks_pub.p_task_user_hooks.scheduled_end_date,
665 jtf_tasks_pub.p_task_user_hooks.actual_start_date,
666 jtf_tasks_pub.p_task_user_hooks.actual_end_date,
667 null,
668 null,
669 null);
670
671
672 l_def_data_list := SYSTEM.DEF_WR_DATA_NST();
673
674 l_def_data_list.extend;
675
676 -- Get the Group Owner
677
678 BEGIN
679 l_workitem_obj_code := 'TASK';
680 l_owner_type_actual := 'RS_GROUP';
681 Select owner_id
682 into l_orig_grp_owner
683 from ieu_uwqm_items
684 where WORKITEM_PK_ID = jtf_tasks_pub.p_task_user_hooks.task_id
685 -- and workitem_obj_code = 'TASK'
686 -- and owner_type_actual = 'RS_GROUP';
687 and workitem_obj_code = l_workitem_obj_code
688 and owner_type_actual = l_owner_type_actual;
689
690 EXCEPTION
691 when others then
692 l_orig_grp_owner := null;
693 END;
694
695 l_def_data_list(l_def_data_list.last) := SYSTEM.DEF_WR_DATA_OBJ(
696 l_task_status,
697 l_priority_code,
698 l_due_date,
699 'TASKS',
700 l_orig_grp_owner
701 );
702
706 USING
703 execute immediate
704 'BEGIN '||l_tasks_rules_func ||
705 ' ( :1, :2, :3, :4 , :5); END ; '
707 IN l_tasks_data_list, IN l_def_data_list , OUT l_msg_count, OUT l_msg_data, OUT l_return_status;
708
709 x_return_status := l_return_status;
710 else
711
712 BEGIN
713
714 /***** Bookings End Date will be used as Due Date. This is available in Assignee hooks ***************
715 if jtf_tasks_pub.p_task_user_hooks.date_selected = 'P' then
716 l_due_date := jtf_tasks_pub.p_task_user_hooks.planned_end_date;
717 l_workitem_comment_code2 := 'PLAN_DUE_DT';
718 elsif jtf_tasks_pub.p_task_user_hooks.date_selected = 'A' then
719 l_due_date := jtf_tasks_pub.p_task_user_hooks.actual_end_date;
720 l_workitem_comment_code2 := 'ACTUAL_DUE_DT';
721 elsif jtf_tasks_pub.p_task_user_hooks.date_selected = 'S' then
722 l_due_date := jtf_tasks_pub.p_task_user_hooks.scheduled_end_date;
723 l_workitem_comment_code2 := 'SCHD_DUE_DT';
724 elsif jtf_tasks_pub.p_task_user_hooks.date_selected is null then
725 -- Niraj Bug 4609285 Commented following 2 lines. Making default as Scheduled Date in case of Null value
726 -- l_due_date := null;
727 -- l_workitem_comment_code2 := 'NULL_DUE_DT';
728 l_due_date := jtf_tasks_pub.p_task_user_hooks.scheduled_end_date;
729 l_workitem_comment_code2 := 'SCHD_DUE_DT';
730 end if;
731 *****************************/
732
733 BEGIN
734 l_workitem_obj_code := 'TASK';
735
736 select assignee_id,
737 assignee_type,
738 due_date,
739 priority_id,
740 party_id,
741 owner_id,
742 title,
743 status_id,
744 owner_type_actual,
745 source_object_id,
746 source_object_type_code
747 into l_assignee_id,
748 l_assignee_type,
749 l_wr_due_date,
750 l_wr_priority_id,
751 l_wr_party_id,
752 l_wr_owner_id,
753 l_wr_title,
754 l_wr_status_id,
755 l_wr_owner_type_actual,
756 l_wr_source_object_id,
757 l_wr_source_object_type_code
758 from ieu_uwqm_items
759 where workitem_pk_id = jtf_tasks_pub.p_task_user_hooks.task_id
760 -- and workitem_obj_code = 'TASK';
761 and workitem_obj_code = l_workitem_obj_code;
762
763 l_count := 1;
764
765 EXCEPTION
766 when no_data_found then -- When no work item exists in Work Repository table, say by deleting it manually
767 l_count := 0; -- Used for Creating a new Work item
768 when others then null;
769 END;
770
771 -- Niraj: Added for bug 4220060
772 IF ((NVL(l_wr_party_id, -1) <> NVL(jtf_tasks_pub.p_task_user_hooks.customer_id, -1)) OR
773 (NVL(l_wr_owner_id, -1) <> NVL(jtf_tasks_pub.p_task_user_hooks.owner_id, -1)) OR
774 (NVL(l_wr_title, '$%&*@') <> NVL(jtf_tasks_pub.p_task_user_hooks.task_name, '$%&*@')) OR
775 (NVL(l_wr_status_id, -1) <> NVL(l_status_id, -1)) OR
776 (NVL(l_wr_priority_id, -1) <> NVL(l_priority_id, -1)) OR
777 (NVL(l_wr_owner_type_actual, '$%&*@') <> NVL(jtf_tasks_pub.p_task_user_hooks.owner_type_code, '$%&*@')) OR
781 l_update_task_reqd_flag := 'y';
778 (NVL(l_wr_source_object_id, -1) <> NVL(jtf_tasks_pub.p_task_user_hooks.source_object_id, -1)) OR
779 (NVL(l_wr_source_object_type_code, '$%&*@') <> NVL(jtf_tasks_pub.p_task_user_hooks.source_object_type_code, '$%&*@')) OR
780 (NVL(l_wr_due_date, to_date('30-12-1000', 'DD-MM-RRRR')) <> NVL(l_due_date, to_date('30-12-1000', 'DD-MM-RRRR')))) THEN
782 ELSE
783 l_update_task_reqd_flag := 'n';
784 END IF;
785
786 -- Start IF-1
787 if (l_update_task_reqd_flag = 'y') THEN
788 -- Start IF-2
789 if (jtf_tasks_pub.p_task_user_hooks.entity = 'TASK')
790 then
791 -- Start IF-3
792 if ((l_task_status = 'CLOSE') and (l_count = 0)) THEN
793 x_return_status := fnd_api.g_ret_sts_success;
794 else
795 if trunc(nvl(l_due_date, FND_API.G_MISS_DATE)) <> trunc(nvl(l_wr_due_date, FND_API.G_MISS_DATE))
796 then
797 l_workitem_comment_code2 := l_workitem_comment_code2;
798 else
799 l_workitem_comment_code2 := null;
800 end if;
801
802 if l_priority_id <> l_wr_priority_id then
803 if l_importance_level = 1 then
804 l_workitem_comment_code3 := 'IMP_LEVEL_C';
805 elsif l_importance_level = 2 then
806 l_workitem_comment_code3 := 'IMP_LEVEL_H';
807 elsif l_importance_level = 3 then
808 l_workitem_comment_code3 := 'IMP_LEVEL_M';
809 elsif l_importance_level = 4 then
810 l_workitem_comment_code3 := 'IMP_LEVEL_L';
811 elsif l_importance_level >=5 then
812 l_workitem_comment_code3 := 'IMP_LEVEL_O';
813 end if;
814 else
815 l_workitem_comment_code3 := null;
816 end if;
817
818 if (l_dist_from = 'GROUP_OWNED') and
819 (l_dist_to = 'INDIVIDUAL_ASSIGNED')
820 then
821 if jtf_tasks_pub.p_task_user_hooks.owner_type_code = 'RS_GROUP' then
822 begin
823 select c.resource_id, c.resource_type_code
824 into l_assignee_id, l_assignee_type
825 from jtf_task_assignments c
826 where c.task_id = jtf_tasks_pub.p_task_user_hooks.task_id
827 and c.assignee_role = 'ASSIGNEE'
828 and c.resource_type_code not in ('RS_TEAM', 'RS_GROUP')
829 and c.resource_id in ( select resource_id
830 from jtf_rs_group_members
831 where group_id = jtf_tasks_pub.p_task_user_hooks.owner_id
832 and nvl(delete_flag,'N') <> 'Y')
833 and c.last_update_date = (select max(a.last_update_date)
834 from jtf_task_assignments a,jtf_task_statuses_vl b
835 where a.task_id = jtf_tasks_pub.p_task_user_hooks.task_id
836 and a.assignee_role = 'ASSIGNEE'
837 and a.assignment_status_id = b.task_status_id
838 and a.resource_type_code not in ('RS_TEAM', 'RS_GROUP')
839 and a.resource_id in ( select resource_id
840 from jtf_rs_group_members
841 where group_id = jtf_tasks_pub.p_task_user_hooks.owner_id
842 and nvl(delete_flag,'N') <> 'Y')
843 and (nvl(b.closed_flag, 'N') = 'N'
844 and nvl(b.completed_flag, 'N') = 'N'
845 and nvl(b.cancelled_flag, 'N') = 'N'
846 and nvl(b.rejected_flag, 'N') = 'N'
847 and b.task_status_id = c.assignment_status_id))
848 and rownum < 2;
849 exception when others then
850 l_assignee_id := null;
851 l_assignee_type := null;
852 end;
853 else
854 l_assignee_id := null;
855 l_assignee_type := null;
856 end if;
857 end if;
858
859 if (l_count = 0) then
860 l_event_key := 'CREATE_WR_ITEM';
861 else
862 l_event_key := 'UPDATE_WR_ITEM';
863 end if;
864
865 l_audit_trail_rec := SYSTEM.WR_AUDIT_TRAIL_NST();
866
867 l_audit_trail_rec.extend;
868
869 l_audit_trail_rec(l_audit_trail_rec.LAST):= SYSTEM.WR_AUDIT_TRAIL_OBJ
870 ('WORKITEM_UPDATE',
871 l_event_key,
872 690,
873 'IEU_TASKS_USERHOOKS.UPDATE_TASK_UWQM_PRE',
874 l_workitem_comment_code1,
875 l_workitem_comment_code2,
876 l_workitem_comment_code3,
877 l_workitem_comment_code4,
878 null);
879
880 -- Start IF-4
881 if ((l_task_status <> 'CLOSE') and (l_count = 0)) then
882 IEU_WR_PUB.CREATE_WR_ITEM(
883 p_api_version => 1.0,
884 p_init_msg_list => FND_API.G_TRUE,
885 p_commit => FND_API.G_FALSE,
886 p_workitem_obj_code => 'TASK',
887 p_workitem_pk_id => jtf_tasks_pub.p_task_user_hooks.task_id,
888 p_work_item_number => to_number(jtf_tasks_pub.p_task_user_hooks.task_number),
889 p_title => jtf_tasks_pub.p_task_user_hooks.task_name,
890 p_party_id => jtf_tasks_pub.p_task_user_hooks.customer_id,
891 p_priority_code => l_priority_code,
892 p_due_date => l_due_date,
893 p_owner_id => jtf_tasks_pub.p_task_user_hooks.owner_id,
894 p_owner_type => jtf_tasks_pub.p_task_user_hooks.owner_type_code,
895 p_assignee_id => l_assignee_id,
896 p_assignee_type => l_assignee_type,
897 p_source_object_id => jtf_tasks_pub.p_task_user_hooks.source_object_id,
898 p_source_object_type_code => jtf_tasks_pub.p_task_user_hooks.source_object_type_code,
899 p_application_id => 690,
903 p_login_id => FND_GLOBAL.LOGIN_ID,
900 p_ieu_enum_type_uuid => 'TASKS',
901 p_work_item_status => l_task_status,
902 p_user_id => FND_GLOBAL.USER_ID,
904 p_audit_trail_rec => l_audit_trail_rec,
905 x_work_item_id => L_WORK_ITEM_ID,
906 x_msg_count => l_msg_count,
907 x_msg_data => L_MSG_DATA,
908 x_return_status => L_RETURN_STATUS);
909
910 x_return_status := l_return_status;
911 else
912 IEU_WR_PUB.UPDATE_WR_ITEM(
913 p_api_version => 1.0,
914 p_init_msg_list => FND_API.G_TRUE,
915 p_commit => FND_API.G_FALSE,
916 p_workitem_obj_code => 'TASK',
917 p_workitem_pk_id => jtf_tasks_pub.p_task_user_hooks.task_id,
918 p_title => jtf_tasks_pub.p_task_user_hooks.task_name,
919 p_party_id => jtf_tasks_pub.p_task_user_hooks.customer_id,
920 p_priority_code => l_priority_code,
921 p_due_date => l_due_date,
922 p_owner_id => jtf_tasks_pub.p_task_user_hooks.owner_id,
923 p_owner_type => jtf_tasks_pub.p_task_user_hooks.owner_type_code,
924 p_assignee_id => l_assignee_id,
925 p_assignee_type => l_assignee_type,
926 p_source_object_id => jtf_tasks_pub.p_task_user_hooks.source_object_id,
927 p_source_object_type_code => jtf_tasks_pub.p_task_user_hooks.source_object_type_code,
928 p_application_id => 690,
929 p_work_item_status => l_task_status,
930 p_user_id => FND_GLOBAL.USER_ID,
931 p_login_id => FND_GLOBAL.LOGIN_ID,
932 p_audit_trail_rec => l_audit_trail_rec,
933 x_msg_count => L_MSG_COUNT,
934 x_msg_data => L_MSG_DATA,
935 x_return_status => L_RETURN_STATUS);
936
937 x_return_status := l_return_status;
938 end if;
939 -- End IF-4
940
941 if (x_return_status <> fnd_api.g_ret_sts_success) then
942 -- x_return_status := fnd_api.g_ret_sts_unexp_error;
943 raise FND_API.G_EXC_UNEXPECTED_ERROR;
944 end if;
945 End if;
946 -- End IF-3
947 else
948 x_return_status := fnd_api.g_ret_sts_success;
949 end if;
950 -- End IF-2 (Task Check)
951 else -- ie if flag=n
952 x_return_status := fnd_api.g_ret_sts_success;
953 end if;
954 -- End IF-1
955
956 EXCEPTION
957 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
958 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
959
960 WHEN OTHERS THEN
961 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
962 END;
963
964 end if; /* Tasks Rules Func */
965
966 elsif l_activation_status = 'N' then
967 x_return_status := FND_API.G_RET_STS_SUCCESS;
968 end if;
969 end update_task_uwqm_pre;
970
971
972 PROCEDURE delete_task_uwqm_pre ( x_return_status OUT NOCOPY VARCHAR2 ) As
973
974 l_msg_count NUMBER;
975 l_msg_data VARCHAR2(2000);
976 l_return_status varchar2(5);
977 l_task_type_id number;
978
979 l_entity varchar2(30);
980
981 l_ws_id1 NUMBER;
982 l_ws_id2 NUMBER := null;
983 l_association_ws_id NUMBER;
984 l_dist_from IEU_UWQM_WORK_SOURCES_B.DISTRIBUTE_FROM%TYPE;
985 l_dist_to IEU_UWQM_WORK_SOURCES_B.DISTRIBUTE_TO%TYPE;
986 l_tasks_rules_func VARCHAR2(500);
987 l_orig_grp_owner NUMBER;
988
989 l_tasks_data_list SYSTEM.WR_TASKS_DATA_NST;
990 l_def_data_list SYSTEM.DEF_WR_DATA_NST;
991
992 l_association_ws_code varchar2(32);
993 l_activation_status varchar2(5);
994
995 l_audit_trail_rec SYSTEM.WR_AUDIT_TRAIL_NST;
996 l_workitem_comment_code1 varchar2(100) := null;
997 l_wi_exists VARCHAR2(10);
998 begin
999
1000
1001 l_dist_from := 'GROUP_OWNED';
1002 l_dist_to := 'INDIVIDUAL_ASSIGNED';
1003 l_object_code := 'TASK';
1007
1004 l_not_valid_flag := 'N';
1005
1006 -- l_workitem_comment_code1 := 'GO_IA';
1008 -- if (l_task_source_obj_type_code is null)
1009 -- then
1010 begin
1011 select source_object_type_code
1012 into l_task_source_obj_type_code
1013 from jtf_tasks_b
1014 where task_id = jtf_tasks_pub.p_task_user_hooks.task_id;
1015
1016 exception when others then
1017 l_task_source_obj_type_code := null;
1018 end;
1019 -- end if;
1020
1021 if (l_task_source_obj_type_code is not null)
1022 then
1023
1024 BEGIN
1025
1026 Select ws_id
1027 into l_ws_id1
1028 from ieu_uwqm_work_sources_b
1029 -- where object_code = 'TASK'
1030 --and nvl(not_valid_flag,'N') = 'N';
1031 where object_code = l_object_code
1032 and nvl(not_valid_flag,'N') = l_not_valid_flag;
1033 EXCEPTION
1034 WHEN OTHERS THEN l_ws_id1 := null;
1035 END;
1036
1037 BEGIN
1038
1039 Select ws_id
1040 into l_ws_id2
1041 from ieu_uwqm_work_sources_b
1042 where object_code = l_task_source_obj_type_code
1043 --and nvl(not_valid_flag,'N') = 'N';
1044 and nvl(not_valid_flag,'N') = l_not_valid_flag;
1045 EXCEPTION
1046 WHEN OTHERS THEN
1047
1048 l_ws_id2 := null;
1049 END;
1050
1051 if (l_ws_id2 is not null)
1052 then
1053
1054 -- Check if Any Work Source Association exists for this combination of Object Code/Source Obj Code
1055 BEGIN
1056
1057 SELECT a.ws_id, b.ws_code
1058 INTO l_association_ws_id, l_association_ws_code
1059 FROM ieu_uwqm_ws_assct_props a, ieu_uwqm_work_sources_b b
1060 WHERE child_ws_id = l_ws_id1
1061 AND parent_ws_id = l_ws_id2
1062 AND a.ws_id = b.ws_id
1063 --AND nvl(b.not_valid_flag,'N') = 'N';
1064 AND nvl(b.not_valid_flag,'N') = l_not_valid_flag;
1065 EXCEPTION
1066 WHEN NO_DATA_FOUND THEN
1067 l_association_ws_id := null;
1068 END;
1069
1070 else
1071 l_association_ws_id := null;
1072
1073 end if;
1074
1075 if l_association_ws_id is not null then
1076 IEU_WR_PUB.CHECK_WS_ACTIVATION_STATUS(
1077 p_api_version => 1,
1078 p_init_msg_list => 'T',
1079 p_commit => 'F',
1083 x_msg_data => l_msg_data,
1080 p_ws_code => l_association_ws_code,
1081 x_ws_activation_status => l_activation_status,
1082 x_msg_count => l_msg_count,
1084 x_return_status => l_return_status);
1085
1086 else
1087 IEU_WR_PUB.CHECK_WS_ACTIVATION_STATUS(
1088 p_api_version => 1,
1089 p_init_msg_list => 'T',
1090 p_commit => 'F',
1091 p_ws_code => 'TASK',
1092 x_ws_activation_status => l_activation_status,
1093 x_msg_count => l_msg_count,
1094 x_msg_data => l_msg_data,
1095 x_return_status => l_return_status);
1096 end if;
1097
1098 -- Get the Tasks Rules Function
1099
1100 if (l_association_ws_id is not null)
1101 then
1102
1103 BEGIN
1104
1105 SELECT ws_b.tasks_rules_function
1106 INTO l_tasks_rules_func
1107 FROM ieu_uwqm_ws_assct_props ws_b
1108 WHERE ws_b.ws_id = l_association_ws_id;
1109
1110 EXCEPTION
1111 WHEN OTHERS THEN
1112 l_tasks_rules_func := null;
1113 END;
1114
1115 end if;
1116
1117 end if; /* source_object_type_code is not null */
1118
1119 --insert into p_temp values ('act sts: '||l_activation_status||' rules func: '||l_tasks_rules_func);
1120
1121 if l_activation_status = 'Y' then
1122
1123 if (l_tasks_rules_func is not null)
1124 then
1125
1126 l_tasks_data_list := SYSTEM.WR_TASKS_DATA_NST();
1127
1128 l_tasks_data_list.extend;
1129
1130 l_tasks_data_list(l_tasks_data_list.last) := SYSTEM.WR_TASKS_DATA_OBJ (
1131 'DELETE_TASK',
1132 jtf_tasks_pub.p_task_user_hooks.task_id,
1133 null,
1134 null,
1135 FND_API.G_MISS_CHAR,
1136 FND_API.G_MISS_NUM,
1137 FND_API.G_MISS_NUM,
1138 FND_API.G_MISS_NUM,
1139 FND_API.G_MISS_NUM,
1140 FND_API.G_MISS_CHAR,
1141 FND_API.G_MISS_NUM,
1142 l_task_source_obj_type_code,
1143 FND_API.G_MISS_NUM,
1144 NULL,
1145 FND_API.G_MISS_DATE,
1146 FND_API.G_MISS_DATE,
1147 FND_API.G_MISS_DATE,
1148 FND_API.G_MISS_DATE,
1149 FND_API.G_MISS_DATE,
1150 FND_API.G_MISS_DATE,
1151 null,
1152 null,
1153 null);
1154
1155 l_def_data_list := SYSTEM.DEF_WR_DATA_NST();
1156
1157 l_def_data_list.extend;
1158
1159 -- Get the Group Owner
1160
1161 BEGIN
1162 l_workitem_obj_code := 'TASK';
1163 l_owner_type_actual := 'RS_GROUP';
1164 Select owner_id
1165 into l_orig_grp_owner
1166 from ieu_uwqm_items
1167 where WORKITEM_PK_ID = jtf_tasks_pub.p_task_user_hooks.task_id
1168 -- and workitem_obj_code = 'TASK'
1169 -- and owner_type_actual = 'RS_GROUP';
1170 and workitem_obj_code = l_workitem_obj_code
1171 and owner_type_actual = l_owner_type_actual;
1172 EXCEPTION
1173 when others then
1174 l_orig_grp_owner := null;
1175 END;
1179 FND_API.G_MISS_CHAR,
1176
1177 l_def_data_list(l_def_data_list.last) := SYSTEM.DEF_WR_DATA_OBJ(
1178 'DELETE',
1180 FND_API.G_MISS_DATE,
1181 'TASKS',
1182 l_orig_grp_owner
1183 );
1184
1185 execute immediate
1186 'BEGIN '||l_tasks_rules_func ||
1187 ' ( :1, :2, :3, :4 , :5); END ; '
1188 USING
1189 IN l_tasks_data_list, IN l_def_data_list , OUT l_msg_count, OUT l_msg_data, OUT l_return_status;
1190
1191 x_return_status := l_return_status;
1192 else
1193
1194 BEGIN
1195
1196 -- Check if the Work Item exists in UWQ Metaphor Table
1197
1198 begin
1199 select 'Y'
1200 into l_wi_exists
1201 from ieu_uwqm_items
1202 where workitem_pk_id = jtf_tasks_pub.p_task_user_hooks.task_id
1203 and workitem_obj_code = 'TASK';
1204 exception
1205 when others then
1206 l_wi_exists := 'N';
1207 end;
1208
1209 --insert into p_temp(msg) values ('WI exists in table: '||l_wi_exists);
1210
1211 -- Closed Work Items are not currently migrated. No updates will be done to UWQ Metaphor table
1212 -- if the Task is in closed/deleted status and the Work Item is not present in UWQ table.
1213
1214 if (l_wi_exists = 'Y')
1215 then
1216
1217 begin
1218 select task_type_id, entity into l_task_type_id, l_entity
1219 from jtf_tasks_b
1220 where task_id = jtf_tasks_pub.p_task_user_hooks.task_id;
1221 exception when others then l_task_type_id := null;
1222 end;
1223
1224 l_audit_trail_rec := SYSTEM.WR_AUDIT_TRAIL_NST();
1225
1226 l_audit_trail_rec.extend;
1227
1228 l_audit_trail_rec(l_audit_trail_rec.LAST):= SYSTEM.WR_AUDIT_TRAIL_OBJ
1229 ('WORKITEM_UPDATE',
1230 'DELETE_WR_ITEM',
1231 690,
1232 'IEU_TASKS_USERHOOKS.DELETE_TASK_UWQM_PRE',
1233 l_workitem_comment_code1,
1234 null,
1235 null,
1236 null,
1237 null);
1238
1239
1240 if l_entity = 'TASK' then
1241 IEU_WR_PUB.UPDATE_WR_ITEM(
1242 p_api_version => 1.0,
1243 p_init_msg_list => FND_API.G_TRUE,
1244 p_commit => FND_API.G_FALSE,
1245 p_workitem_obj_code => 'TASK',
1246 p_workitem_pk_id => jtf_tasks_pub.p_task_user_hooks.task_id,
1247 p_title => FND_API.G_MISS_CHAR,
1248 p_party_id => FND_API.G_MISS_NUM,
1249 p_priority_code => FND_API.G_MISS_CHAR,
1250 p_due_date => FND_API.G_MISS_DATE,
1251 p_owner_id => FND_API.G_MISS_NUM,
1252 p_owner_type => FND_API.G_MISS_CHAR,
1253 p_assignee_id => FND_API.G_MISS_NUM,
1254 p_assignee_type => FND_API.G_MISS_CHAR,
1255 p_source_object_id => FND_API.G_MISS_NUM,
1256 p_source_object_type_code => FND_API.G_MISS_CHAR,
1257 p_application_id => 690,
1258 p_work_item_status => 'DELETE',
1259 p_user_id => FND_GLOBAL.USER_ID,
1260 p_login_id => FND_GLOBAL.LOGIN_ID,
1261 p_audit_trail_rec => l_audit_trail_rec,
1262 x_msg_count => L_MSG_COUNT,
1263 x_msg_data => L_MSG_DATA,
1264 x_return_status => L_RETURN_STATUS);
1265
1266 x_return_status := l_return_status;
1267 else
1268 x_return_status := fnd_api.g_ret_sts_success;
1269 end if;
1270 else /* Work Item does not exist in Work Repository */
1271
1272 --insert into p_temp values('ret success');
1273 x_return_status := fnd_api.g_ret_sts_success;
1274 end if;
1275
1276 if (x_return_status = fnd_api.g_ret_sts_success)
1277 then
1281
1278 l_del_task_id := jtf_tasks_pub.p_task_user_hooks.task_id;
1279 --insert into p_temp values ('del tsk id: '||l_del_task_id);
1280 end if;
1282 EXCEPTION WHEN OTHERS THEN
1283 --insert into p_temp values('excep');
1284 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1285
1286 END;
1287
1288 end if; /* Tasks Rules Func */
1289 elsif l_activation_status = 'N' then
1290 x_return_status := FND_API.G_RET_STS_SUCCESS;
1291 end if;
1292 end delete_task_uwqm_pre;
1293
1294 PROCEDURE create_task_assign_uwqm_pre ( x_return_status OUT NOCOPY VARCHAR2 ) As
1295
1296 l_msg_count NUMBER;
1297 l_msg_data VARCHAR2(2000);
1298 l_return_status varchar2(5);
1299 l_owner_id number;
1300 l_owner_type varchar2(25);
1301 l_source_object_id number;
1302 l_source_object_type_code varchar2(30);
1303 l_count number := 0;
1304 l_importance_level number;
1305 l_assignee_id number;
1306 l_assignee_type varchar2(30);
1307 l_status varchar2(20);
1308 l_task_type_id number;
1309
1310 l_ws_id1 NUMBER;
1311 l_ws_id2 NUMBER := null;
1312 l_association_ws_id NUMBER;
1313 l_dist_from IEU_UWQM_WORK_SOURCES_B.DISTRIBUTE_FROM%TYPE;
1314 l_dist_to IEU_UWQM_WORK_SOURCES_B.DISTRIBUTE_TO%TYPE;
1315 l_task_asg_count number := 0;
1316 l_group_id varchar2(5);
1317
1318 l_tasks_rules_func VARCHAR2(500);
1319 l_orig_grp_owner NUMBER;
1320
1321 l_entity varchar2(30);
1322
1323 l_tasks_data_list SYSTEM.WR_TASKS_DATA_NST;
1324 l_def_data_list SYSTEM.DEF_WR_DATA_NST;
1325
1326 l_association_ws_code varchar2(32);
1327 l_activation_status varchar2(5);
1328
1329 l_audit_trail_rec SYSTEM.WR_AUDIT_TRAIL_NST;
1330 l_workitem_comment_code1 varchar2(100) := null;
1331 l_workitem_comment_code2 varchar2(100) := null;
1332 l_wi_exists VARCHAR2(10);
1333 l_tsk_sts_id NUMBER;
1334 l_task_status VARCHAR2(500);
1335 l_del_flag VARCHAR2(10);
1336
1337 l_ins_task_id number;
1338 l_ins_task_number varchar2(30);
1339 l_ins_customer_id number;
1340 l_ins_owner_id number;
1341 l_ins_owner_type_code varchar2(30);
1342 l_ins_source_object_id number;
1343 l_ins_source_object_type_code varchar2(30);
1344 l_ins_task_name varchar2(80);
1345 l_ins_assignee_id number;
1346 l_ins_assignee_type varchar2(25);
1347 l_ins_task_priority_id number;
1348 l_ins_date_selected varchar2(1);
1349 l_ins_due_date date;
1350 l_ins_planned_end_date date;
1351 l_ins_actual_ins_end_date date;
1352 l_ins_scheduled_end_date date;
1353 l_ins_planned_start_date date;
1354 l_ins_actual_ins_start_date date;
1355 l_ins_scheduled_start_date date;
1356 l_ins_importance_level number;
1357 l_ins_priority_code varchar2(30);
1358 l_ins_task_status varchar2(10);
1359 l_ins_task_status_id number;
1360 l_ins_task_type_id number;
1361 l_ins_work_item_id NUMBER;
1362 l_wr_assignee_id ieu_uwqm_items.assignee_id%TYPE; -- Niraj, Bug 4220060, Added
1363 l_update_wr_item_call varchar2(5); -- Niraj, Bug 4220060, Added
1364
1365 begin
1366
1367 l_ins_priority_code := 'LOW';
1368 l_dist_from := 'GROUP_OWNED';
1369 l_dist_to := 'INDIVIDUAL_ASSIGNED';
1370 /*** Create Task Assignee
1371 ** First the Activation Status is checked
1372 ** If the Work Source is activated, then check if any Tasks Rules Function is registered
1373 ** If the task Rules function is present, then execute it
1374 ** If the Task Rules function is not registered then the assignee will be created based on the following rules for Standard Tasks
1375 ** 1. Create and assignee in UWQ if Owner is a Group and the assignee should be a member of the group
1376 ** 2. If the Owner is not a group, then Assignee will not be created in UWQ
1377 ** 3. If the Assignee is not a member of the Group, then UWQ Assignee will be the most recent Group member if it exists.
1378 ** If there are no group members present, then the assignee will be null
1379 ***/
1380
1381 -- reset del task pkg lvl variable
1382 l_del_task_id := null;
1383
1384
1385 l_object_code := 'TASK';
1386 l_not_valid_flag := 'N';
1387
1388 -- l_workitem_comment_code1 := 'GO_IA';
1389
1390
1391 -- if (l_task_source_obj_type_code is null)
1392 -- then
1393 begin
1394
1395 select source_object_type_code
1396 into l_task_source_obj_type_code
1397 from jtf_tasks_b
1398 where task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id;
1399
1400 exception when others then
1401 l_task_source_obj_type_code := null;
1402 end;
1403 -- end if;
1404
1405
1406 if (l_task_source_obj_type_code is not null)
1407 then
1408
1409 BEGIN
1410
1411 Select ws_id
1412 into l_ws_id1
1413 from ieu_uwqm_work_sources_b
1414 -- where object_code = 'TASK'
1418 EXCEPTION
1415 -- and nvl(not_valid_flag,'N') = 'N';
1416 where object_code = l_object_code
1417 and nvl(not_valid_flag,'N') = l_not_valid_flag;
1419 WHEN OTHERS THEN l_ws_id1 := null;
1420 END;
1421
1422 BEGIN
1423
1424 Select ws_id
1425 into l_ws_id2
1426 from ieu_uwqm_work_sources_b
1427 where object_code = l_task_source_obj_type_code
1428 --and nvl(not_valid_flag,'N') = 'N';
1429 and nvl(not_valid_flag,'N') = l_not_valid_flag;
1430 EXCEPTION
1431 WHEN OTHERS THEN
1432
1433 l_ws_id2 := null;
1434 END;
1435
1436 if (l_ws_id2 is not null)
1437 then
1438
1439 -- Check if Any Work Source Association exists for this combination of Object Code/Source Obj Code
1440 BEGIN
1441
1442 SELECT a.ws_id, b.ws_code
1443 INTO l_association_ws_id, l_association_ws_code
1444 FROM ieu_uwqm_ws_assct_props a, ieu_uwqm_work_sources_b b
1445 WHERE child_ws_id = l_ws_id1
1446 AND parent_ws_id = l_ws_id2
1447 AND a.ws_id = b.ws_id
1448 -- AND nvl(b.not_valid_flag,'N') = 'N';
1449 AND nvl(b.not_valid_flag,'N') = l_not_valid_flag;
1450
1451 EXCEPTION
1452 WHEN NO_DATA_FOUND THEN
1453 l_association_ws_id := null;
1454 END;
1455
1456 else
1457 l_association_ws_id := null;
1458
1459 end if;
1460
1461 if l_association_ws_id is not null then
1462 IEU_WR_PUB.CHECK_WS_ACTIVATION_STATUS(
1463 p_api_version => 1,
1464 p_init_msg_list => 'T',
1465 p_commit => 'F',
1466 p_ws_code => l_association_ws_code,
1467 x_ws_activation_status => l_activation_status,
1468 x_msg_count => l_msg_count,
1469 x_msg_data => l_msg_data,
1470 x_return_status => l_return_status);
1471
1472 else
1473 IEU_WR_PUB.CHECK_WS_ACTIVATION_STATUS(
1474 p_api_version => 1,
1475 p_init_msg_list => 'T',
1476 p_commit => 'F',
1477 p_ws_code => 'TASK',
1478 x_ws_activation_status => l_activation_status,
1479 x_msg_count => l_msg_count,
1480 x_msg_data => l_msg_data,
1481 x_return_status => l_return_status);
1482 end if;
1483
1484 -- Get the Tasks Rules Function
1485
1486 if (l_association_ws_id is not null)
1487 then
1488
1489 BEGIN
1490
1491 SELECT ws_b.tasks_rules_function
1492 INTO l_tasks_rules_func
1493 FROM ieu_uwqm_ws_assct_props ws_b
1494 WHERE ws_b.ws_id = l_association_ws_id;
1495
1496 EXCEPTION
1497 WHEN OTHERS THEN
1498 l_tasks_rules_func := null;
1499 END;
1500
1501 end if;
1502
1503 end if; /* source_object_type_code is not null */
1504
1505 if l_activation_status = 'Y' then
1506
1507 /* insert into p_temp(msg) values ('assignee_role: '||jtf_task_assignments_pub.p_task_assignments_user_hooks.assignee_role||
1508 ' booking end date: '|| jtf_task_assignments_pub.p_task_assignments_user_hooks.booking_end_date); */
1509 if (jtf_task_assignments_pub.p_task_assignments_user_hooks.assignee_role = 'OWNER')
1510 then
1511 l_ins_due_date := jtf_task_assignments_pub.p_task_assignments_user_hooks.booking_end_date;
1512 else
1513 -- Get the Bookings End Date from JTF_TASK_ASSIGNMENTS where assignee_role= owner
1514 BEGIN
1515
1516 SELECT booking_end_date
1517 INTO l_ins_due_date
1518 FROM JTF_TASK_ALL_ASSIGNMENTS
1519 WHERE task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
1520 AND assignee_role = 'OWNER';
1521
1522 EXCEPTION
1523 WHEN OTHERS THEN
1524 null;
1525 END;
1526 end if;
1527 --insert into p_temp(msg) values ('due date: '||l_ins_due_date);
1528
1529 if (l_tasks_rules_func is not null)
1530 then
1531
1532 l_tasks_data_list := SYSTEM.WR_TASKS_DATA_NST();
1533
1534 l_tasks_data_list.extend;
1535
1536 l_tasks_data_list(l_tasks_data_list.last) := SYSTEM.WR_TASKS_DATA_OBJ (
1537 'CREATE_TASK_ASG',
1538 jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id,
1539 null,
1540 FND_API.G_MISS_CHAR,
1541 FND_API.G_MISS_CHAR,
1542 FND_API.G_MISS_NUM,
1543 FND_API.G_MISS_NUM,
1544 FND_API.G_MISS_NUM,
1545 FND_API.G_MISS_NUM,
1549 FND_API.G_MISS_NUM,
1546 FND_API.G_MISS_CHAR,
1547 FND_API.G_MISS_NUM,
1548 l_task_source_obj_type_code,
1550 NULL,
1551 FND_API.G_MISS_DATE,
1552 FND_API.G_MISS_DATE,
1553 FND_API.G_MISS_DATE,
1554 FND_API.G_MISS_DATE,
1555 FND_API.G_MISS_DATE,
1556 FND_API.G_MISS_DATE,
1557 jtf_task_assignments_pub.p_task_assignments_user_hooks.resource_type_code,
1558 jtf_task_assignments_pub.p_task_assignments_user_hooks.resource_id,
1559 jtf_task_assignments_pub.p_task_assignments_user_hooks.assignment_status_id);
1560
1561 l_def_data_list := SYSTEM.DEF_WR_DATA_NST();
1562
1563 l_def_data_list.extend;
1564
1565 -- Get the Group Owner
1566
1567 BEGIN
1568 l_workitem_obj_code := 'TASK';
1569 l_owner_type_actual := 'RS_GROUP';
1570 Select owner_id
1571 into l_orig_grp_owner
1572 from ieu_uwqm_items
1573 where WORKITEM_PK_ID = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
1574 -- and workitem_obj_code = 'TASK'
1575 -- and owner_type_actual = 'RS_GROUP';
1576 and workitem_obj_code = l_workitem_obj_code
1577 and owner_type_actual = l_owner_type_actual;
1578
1579 EXCEPTION
1580 when others then
1581 l_orig_grp_owner := null;
1582 END;
1583
1584 l_def_data_list(l_def_data_list.last) := SYSTEM.DEF_WR_DATA_OBJ(
1585 FND_API.G_MISS_CHAR,
1586 FND_API.G_MISS_CHAR,
1587 l_ins_due_date,
1588 'TASKS',
1589 l_orig_grp_owner
1590 );
1591
1592 execute immediate
1593 'BEGIN '||l_tasks_rules_func ||
1594 ' ( :1, :2, :3, :4 , :5); END ; '
1595 USING
1596 IN l_tasks_data_list, IN l_def_data_list , OUT l_msg_count, OUT l_msg_data, OUT l_return_status;
1597
1598 x_return_status := l_return_status;
1599 else
1600
1601 BEGIN
1602
1603 -- Get the Task Status Id
1604 begin
1605 select task_status_id, deleted_flag
1606 into l_tsk_sts_id, l_del_flag
1607 from jtf_tasks_b
1608 where task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id;
1609 exception
1610 when others then null;
1611 end;
1612
1613 if (jtf_tasks_pub.p_task_user_hooks.task_status_id is not null)
1614 then
1615 l_tsk_sts_id := jtf_tasks_pub.p_task_user_hooks.task_status_id;
1616 end if;
1617
1618 -- Get the Task Status based on task Status Id
1619 begin
1620 select 'CLOSE' into l_task_status
1621 from jtf_task_statuses_vl
1622 where (nvl(closed_flag, 'N') = 'Y'
1623 or nvl(completed_flag, 'N') = 'Y'
1624 or nvl(cancelled_flag, 'N') = 'Y'
1625 or nvl(rejected_flag, 'N') = 'Y')
1626 and task_status_id = l_tsk_sts_id;
1627 EXCEPTION WHEN others THEN null;
1628 end;
1629
1630 -- Check if the Work Item exists in UWQ Metaphor Table
1631 begin
1632 select 'Y'
1633 into l_wi_exists
1634 from ieu_uwqm_items
1635 where workitem_pk_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
1636 and workitem_obj_code = 'TASK';
1637 exception
1638 when others then
1639 l_wi_exists := 'N';
1640 end;
1641
1642 -- insert into p_temp(msg) values ('Task Status Id from Tasks table: '||l_tsk_sts_id||' l_wi_exists: '||l_wi_exists||' l_del_flag: '||l_del_flag);
1643
1644 -- Closed Work Items are not currently migrated. No updates will be done to UWQ Metaphor table
1645 -- if the Task is in closed/deleted status and the Work Item is not present in UWQ table return Success.
1646
1647 if ( ( (l_task_status = 'CLOSE') or (l_del_flag = 'Y') )
1648 and (l_wi_exists = 'N'))
1649 then
1650 -- insert into p_temp(msg) values ('close/del and rec does not exists.. ret success');
1651 x_return_status := fnd_api.g_ret_sts_success;
1652 else
1653 -- insert into p_temp(msg) values (' else condn');
1654 if (l_wi_exists = 'N')
1655 then
1656 -- insert into p_temp(msg) values (' selecting data from tasks table');
1657
1658 begin
1659
1660 select tb.task_id, tb.task_number, tb.customer_id, tb.owner_id, tb.owner_type_code,
1661 tb.source_object_id, tb.source_object_type_code,
1662 tb.planned_start_date, tb.planned_end_date, tb.actual_start_date, tb.actual_end_date,
1663 tb.scheduled_start_date, tb.scheduled_end_date,tb.task_type_id,
1664 tb.task_status_id, tt.task_name, tp.importance_level, ip.priority_code
1665 into l_ins_task_id, l_ins_task_number, l_ins_customer_id, l_ins_owner_id, l_ins_owner_type_code,
1666 l_ins_source_object_id, l_ins_source_object_type_code, l_ins_planned_start_date, l_ins_planned_end_date,
1667 l_ins_actual_ins_start_date, l_ins_actual_ins_end_date, l_ins_scheduled_start_date, l_ins_scheduled_end_date,
1671 and tb.entity = 'TASK'
1668 l_ins_task_type_id, l_ins_task_status_id, l_ins_task_name, l_ins_importance_level, l_ins_priority_code
1669 from jtf_tasks_b tb, jtf_tasks_tl tt, jtf_task_priorities_vl tp, ieu_uwqm_priorities_b ip
1670 where tb.task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
1672 and tb.task_id = tt.task_id
1673 and tt.language = userenv('LANG')
1674 and tp.task_priority_id = nvl(tb.task_priority_id, 4)
1675 and least(tp.importance_level, 4) = ip.priority_level;
1676 exception
1677 when others then
1678 null;
1679 -- insert into p_temp(msg) values('raising err ');
1680 -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1681 --raise;
1682 end;
1683
1684
1685 l_owner_id := l_ins_owner_id;
1686 l_owner_type := l_ins_owner_type_code;
1687
1688 begin
1689 select 'CLOSE' into l_ins_task_status
1690 from jtf_task_statuses_vl
1691 where (nvl(closed_flag, 'N') = 'Y'
1692 or nvl(completed_flag, 'N') = 'Y'
1693 or nvl(cancelled_flag, 'N') = 'Y'
1694 or nvl(rejected_flag, 'N') = 'Y')
1695 and task_status_id = l_ins_task_status_id;
1696 EXCEPTION WHEN others THEN
1697 begin
1698 select 'SLEEP' into l_ins_task_status
1699 from jtf_task_statuses_vl
1700 where nvl(on_hold_flag, 'N') = 'Y'
1701 and task_status_id = l_ins_task_status_id;
1702 EXCEPTION WHEN others THEN
1703 l_ins_task_status := 'OPEN';
1704 end;
1705 end;
1706
1707
1708 /****
1709 insert into p_temp(msg) values('1'||l_ins_task_id||' '|| l_ins_task_number||' '|| l_ins_customer_id||' '|| l_ins_owner_id||' '|| l_ins_owner_type_code);
1710 insert into p_temp(msg) values('2'||l_ins_source_object_id||' '|| l_ins_source_object_type_code||' '|| l_ins_due_date||' '|| l_ins_planned_start_date);
1711 insert into p_temp(msg) values('3'||l_ins_planned_end_date||' '|| l_ins_actual_ins_start_date||' '|| l_ins_actual_ins_end_date||' '|| l_ins_scheduled_start_date||' '|| l_ins_scheduled_end_date);
1712 insert into p_temp(msg) values('4'||l_ins_task_type_id||' '|| l_ins_task_status_id||' '|| l_ins_task_name||' '|| l_ins_importance_level||' '|| l_ins_priority_code);
1713 *****/
1714
1715
1716 else
1717 begin
1718 l_workitem_obj_code := 'TASK';
1719 select owner_id, owner_type, source_object_id, source_object_type_code, assignee_id -- Niraj, 4220060, Added assignee_id
1720 into l_owner_id, l_owner_type, l_source_object_id, l_source_object_type_code, l_wr_assignee_id -- Niraj, 4220060, Added
1721 from ieu_uwqm_items
1722 where workitem_pk_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
1723 -- and workitem_obj_code = 'TASK';
1724 and workitem_obj_code = l_workitem_obj_code;
1725 EXCEPTION WHEN others THEN
1726 null;
1727 --x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1728 -- l_msg_data := 'Work item does not exist in the WR ';
1729 --raise;
1730
1731 end ;
1732
1733 end if;
1734
1735
1736 -- Get the Task Assignment Status Id
1737 begin
1738 select 'OPEN' INTO l_status
1739 from jtf_task_statuses_vl b
1740 where nvl(b.closed_flag, 'N') = 'N'
1741 and nvl(b.completed_flag, 'N') = 'N'
1742 and nvl(b.cancelled_flag, 'N') = 'N'
1743 and nvl(b.rejected_flag, 'N') = 'N'
1744 and b.task_status_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.assignment_status_id;
1745 exception when others then l_status := 'CLOSED';
1746 end;
1747
1748
1749 if (jtf_task_assignments_pub.p_task_assignments_user_hooks.resource_type_code = 'RS_TEAM')
1750 or (l_status = 'CLOSED')
1751 then
1752 l_assignee_id := null;
1753 l_assignee_type := null;
1754 -- insert into p_temp(msg) values('selecting asg id from jtf tsk asg table');
1755 begin
1756 select c.resource_id, c.resource_type_code
1757 into l_assignee_id, l_assignee_type
1758 from jtf_task_assignments c
1759 where c.task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
1760 and c.last_update_date = (select max(a.last_update_date)
1761 from jtf_task_assignments a,jtf_task_statuses_vl b
1762 where a.task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
1763 and a.assignee_role = 'ASSIGNEE'
1764 and a.resource_type_code <> 'RS_TEAM'
1765 and a.assignment_status_id = b.task_status_id
1766 and (nvl(b.closed_flag, 'N') = 'N'
1767 and nvl(b.completed_flag, 'N') = 'N'
1768 and nvl(b.cancelled_flag, 'N') = 'N'
1769 and nvl(b.rejected_flag, 'N') = 'N'
1770 and b.task_status_id = c.assignment_status_iD))
1771 and assignee_role = 'ASSIGNEE'
1772 and c.resource_type_code <> 'RS_TEAM'
1773 and c.task_assignment_id <> jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id
1774 and rownum < 2;
1775 exception when others then
1776 l_assignee_id := null;
1777 l_assignee_type := null;
1778 end;
1779
1780 else
1781 --insert into p_temp(msg) values('setting asg id to hooks data');
1782
1786
1783 l_assignee_id := jtf_task_assignments_pub.p_task_assignments_user_hooks.resource_id;
1784 l_assignee_type := jtf_task_assignments_pub.p_task_assignments_user_hooks.resource_type_code;
1785 end if;
1787 begin
1788 select task_type_id, entity into l_task_type_id, l_entity
1789 from jtf_tasks_b
1790 where task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id;
1791 exception when others then l_task_type_id := null;
1792 end;
1793
1794
1795 if (l_dist_from = 'GROUP_OWNED') and
1796 (l_dist_to = 'INDIVIDUAL_ASSIGNED')
1797 then
1798 if l_owner_type = 'RS_GROUP'
1799 then
1800 begin
1801 select count(0) into l_task_asg_count
1802 from jtf_task_assignments
1803 where task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
1804 and assignee_role = 'ASSIGNEE';
1805 exception when others then l_task_asg_count := 0;
1806 end;
1807
1808 --insert into p_temp(msg) values('asg type: '||l_assignee_type||' asg id: '||l_assignee_id|| ' own id: '||l_owner_id);
1809
1810 -- Check if the assignee is grp member
1811
1812 if l_assignee_type not in ('RS_TEAM', 'RS_GROUP') then
1813 begin
1814 select 'Y' into l_group_id
1815 from jtf_rs_group_members
1816 where resource_id = l_assignee_id
1817 and group_id = l_owner_id
1818 and nvl(delete_flag,'N') <> 'Y'
1819 and rownum < 2;
1820 exception when others then l_group_id := 'N';
1821 end;
1822 end if;
1823
1824
1825 if l_task_asg_count = 0
1826 then
1827 ----- create a new assignee -----------
1828
1829 -- If the new assignee is not a member of the grp then set the assignee to null in UWQ WR
1830 if nvl(l_group_id, 'N') = 'N' then
1831 l_assignee_id := null;
1832 l_assignee_type := null;
1833 end if;
1834 elsif l_task_asg_count >= 1
1835 then
1836
1837 --------- Add another assignee----------
1838
1839 -- If the new assignee created is not a member of the group,
1840 -- then get the assignee from UWQ WR if the record exists
1841 -- else get the most recent grp member assignee from JTF_TASK_ASSIGNMENTS.
1842
1843 if nvl(l_group_id, 'N') = 'N'
1844 then
1845
1846 if (l_wi_exists = 'Y')
1847 then
1848 begin
1849 l_workitem_obj_code := 'TASK';
1850 select assignee_id, assignee_type_actual
1851 into l_assignee_id, l_assignee_type
1852 from ieu_uwqm_items
1853 where workitem_pk_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
1854 -- and workitem_obj_code = 'TASK';
1855 and workitem_obj_code = l_workitem_obj_code;
1856 exception when others then
1857 l_assignee_id := null;
1858 l_assignee_type := null;
1859 end;
1860 else
1861 begin
1862 select c.resource_id, c.resource_type_code
1863 into l_assignee_id, l_assignee_type
1864 from jtf_task_assignments c
1865 where c.task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
1866 and c.assignee_role = 'ASSIGNEE'
1867 and c.resource_type_code not in ('RS_TEAM', 'RS_GROUP')
1868 and c.resource_id in ( select resource_id
1872 --and c.task_assignment_id <> jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id
1869 from jtf_rs_group_members
1870 where group_id = l_owner_id
1871 and nvl(delete_flag,'N') <> 'Y')
1873 and c.last_update_date = (select max(a.last_update_date)
1874 from jtf_task_assignments a,jtf_task_statuses_vl b
1875 where a.task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
1876 and a.assignee_role = 'ASSIGNEE'
1877 and a.task_assignment_id <> jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id
1878 and a.assignment_status_id = b.task_status_id
1879 and a.resource_type_code not in ('RS_TEAM', 'RS_GROUP')
1880 and a.resource_id in ( select resource_id
1881 from jtf_rs_group_members
1882 where group_id = l_owner_id
1883 and nvl(delete_flag,'N') <> 'Y')
1884 and (nvl(b.closed_flag, 'N') = 'N'
1885 and nvl(b.completed_flag, 'N') = 'N'
1886 and nvl(b.cancelled_flag, 'N') = 'N'
1887 and nvl(b.rejected_flag, 'N') = 'N'
1888 and b.task_status_id = c.assignment_status_id)
1889 )
1890
1891 and rownum < 2;
1892 exception when others then
1893 --l_sql_err := SQLERRM;
1894 --l_sql_code := SQLCODE;
1895 -- insert into p_temp(msg) values ('excep: '||l_SQL_ERR|| l_SQL_CODE);
1896 l_assignee_id := null;
1897 l_assignee_type := null;
1898 end;
1899 end if; /* l_wi_exists */
1900 end if; /*l_group_id */
1901 end if; /* l_task_asg_count */
1902 else
1903 l_assignee_id := null;
1904 l_assignee_type := null;
1905 end if;
1906 end if;
1907
1908 -- Niraj, Bug 4220060
1909 -- Set the flag to Y if the assignee_role = OWNER
1910 IF ( (jtf_task_assignments_pub.p_task_assignments_user_hooks.assignee_role = 'OWNER') OR
1911 ( (jtf_task_assignments_pub.p_task_assignments_user_hooks.assignee_role = 'ASSIGNEE') AND
1912 (NVL(l_wr_assignee_id, -1) <> NVL(l_assignee_id, -1))) )
1913 THEN
1914 l_update_wr_item_call := 'Y';
1915 ELSE
1916 l_update_wr_item_call := 'N';
1917 END IF;
1918
1919 -- insert into p_temp(msg) values ('update flag: '||l_update_wr_item_call);
1920
1921 -- Start 'l_update_wr_item_call' check
1922 IF (l_update_wr_item_call = 'Y') Then -- Niraj, 4220060
1923 l_workitem_comment_code2 := 'GRP_MAX_ASSGN';
1924
1925 l_audit_trail_rec := SYSTEM.WR_AUDIT_TRAIL_NST();
1926
1927 l_audit_trail_rec.extend;
1928
1929 l_audit_trail_rec(l_audit_trail_rec.LAST):= SYSTEM.WR_AUDIT_TRAIL_OBJ
1930 ('WORKITEM_UPDATE',
1931 'UPDATE_WR_ITEM',
1932 690,
1933 'IEU_TASKS_USERHOOKS.CREATE_TASK_ASSIGN_UWQM_PRE',
1934 l_workitem_comment_code1,
1935 l_workitem_comment_code2,
1936 null,
1937 null,
1938 null);
1939 /* insert into p_temp(msg) values ('asg role: '||jtf_task_assignments_pub.p_task_assignments_user_hooks.assignee_role ||
1940 ' entity: '||l_entity|| ' group id: '||l_group_id); */
1941
1942 -- insert into p_temp(msg) values ('passign due date as: '||l_ins_due_date);
1943 if (jtf_task_assignments_pub.p_task_assignments_user_hooks.assignee_role = 'ASSIGNEE')
1944 and (l_entity = 'TASK')
1945 --and (nvl(l_group_id, 'N') = 'Y')
1946 then
1947 if (l_wi_exists = 'N')
1948 then
1949 -- insert into p_temp(msg) values (' calling insert ');
1950
1951 IEU_WR_PUB.CREATE_WR_ITEM(
1952 p_api_version => 1.0,
1953 p_init_msg_list => FND_API.G_TRUE,
1954 p_commit => FND_API.G_true,
1955 p_workitem_obj_code => 'TASK',
1956 p_workitem_pk_id => l_ins_task_id,
1957 p_work_item_number => l_ins_task_number,
1958 p_title => l_ins_task_name,
1959 p_party_id => l_ins_customer_id,
1960 p_priority_code => l_ins_priority_code,
1961 p_due_date => l_ins_due_date,
1962 p_owner_id => l_ins_owner_id,
1963 p_owner_type => l_ins_owner_type_code,
1964 p_assignee_id => l_assignee_id,
1965 p_assignee_type => l_assignee_type,
1966 p_source_object_id => l_ins_source_object_id,
1967 p_source_object_type_code => l_ins_source_object_type_code,
1968 p_application_id => 690,
1969 p_ieu_enum_type_uuid => 'TASKS',
1970 p_work_item_status => l_ins_task_status,
1971 p_user_id => FND_GLOBAL.USER_ID,
1972 p_login_id => FND_GLOBAL.LOGIN_ID,
1973 x_work_item_id => l_ins_WORK_ITEM_ID,
1974 x_msg_count => l_msg_count,
1975 x_msg_data => l_MSG_DATA,
1976 x_return_status => l_RETURN_STATUS);
1977
1978 else
1979
1980 --insert into p_temp(msg) values (' calling update ');
1981 IEU_WR_PUB.UPDATE_WR_ITEM(
1982 p_api_version => 1.0,
1983 p_init_msg_list => FND_API.G_TRUE,
1984 p_commit => FND_API.G_FALSE,
1985 p_workitem_obj_code => 'TASK',
1989 p_priority_code => FND_API.G_MISS_CHAR,
1986 p_workitem_pk_id => jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id,
1987 p_title => FND_API.G_MISS_CHAR,
1988 p_party_id => FND_API.G_MISS_NUM,
1990 p_due_date => l_ins_due_date,
1991 p_owner_id => FND_API.G_MISS_NUM,
1992 p_owner_type => FND_API.G_MISS_CHAR,
1993 p_assignee_id => l_assignee_id,
1994 p_assignee_type => l_assignee_type,
1995 p_source_object_id => FND_API.G_MISS_NUM,
1996 p_source_object_type_code => FND_API.G_MISS_CHAR,
1997 p_application_id => 690,
1998 p_user_id => FND_GLOBAL.USER_ID,
1999 p_login_id => FND_GLOBAL.LOGIN_ID,
2000 p_work_item_status => FND_API.G_MISS_CHAR,
2001 p_audit_trail_rec => l_audit_trail_rec,
2002 x_msg_count => L_MSG_COUNT,
2003 x_msg_data => L_MSG_DATA,
2004 x_return_status => L_RETURN_STATUS);
2005
2006 end if;
2007 x_return_status := l_return_status;
2008
2009
2010 elsif (jtf_task_assignments_pub.p_task_assignments_user_hooks.assignee_role = 'OWNER')
2011 then
2012 -- Create or Update Task User Hook should have been invoked
2013 -- before invoking Create/Update Task Assignment if the assignee role is owner.
2014 -- So update the rec only if its present in IEU_UWQM_ITEMS
2015 if (l_wi_exists <> 'N')
2016 then
2017
2018 --insert into p_temp(msg) values (' calling update ');
2019 IEU_WR_PUB.UPDATE_WR_ITEM(
2020 p_api_version => 1.0,
2021 p_init_msg_list => FND_API.G_TRUE,
2022 p_commit => FND_API.G_FALSE,
2023 p_workitem_obj_code => 'TASK',
2024 p_workitem_pk_id => jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id,
2025 p_title => FND_API.G_MISS_CHAR,
2026 p_party_id => FND_API.G_MISS_NUM,
2027 p_priority_code => FND_API.G_MISS_CHAR,
2028 p_due_date => l_ins_due_date,
2029 p_owner_id => FND_API.G_MISS_NUM,
2030 p_owner_type => FND_API.G_MISS_CHAR,
2031 p_assignee_id => l_assignee_id,
2032 p_assignee_type => l_assignee_type,
2033 p_source_object_id => FND_API.G_MISS_NUM,
2034 p_source_object_type_code => FND_API.G_MISS_CHAR,
2035 p_application_id => 690,
2036 p_user_id => FND_GLOBAL.USER_ID,
2037 p_login_id => FND_GLOBAL.LOGIN_ID,
2038 p_work_item_status => FND_API.G_MISS_CHAR,
2039 p_audit_trail_rec => l_audit_trail_rec,
2040 x_msg_count => L_MSG_COUNT,
2041 x_msg_data => L_MSG_DATA,
2042 x_return_status => L_RETURN_STATUS);
2043
2044 end if;
2045 x_return_status := l_return_status;
2046
2047 elsif (l_entity <> 'TASK')
2048 --or (nvl(l_group_id, 'N') = 'N')
2049 then
2050 -- insert into p_temp(msg) values (' wr proc was not called..returning success ');
2051 x_return_status := fnd_api.g_ret_sts_success;
2052 end if;
2053 ELSE -- Niraj, 4220060
2054 x_return_status := fnd_api.g_ret_sts_success; -- Niraj, 4220060
2055 END IF; -- Niraj, 4220060
2056 -- End 'l_update_wr_item_call' check
2057
2058 end if; /* Work Item exists in UWQ Work Repository */
2059
2060 EXCEPTION WHEN OTHERS THEN
2061 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2062
2063 END;
2064
2065 end if; /* Tasks Rules Func */
2066 elsif l_activation_status = 'N' then
2067 x_return_status := FND_API.G_RET_STS_SUCCESS;
2068 end if;
2069 end create_task_assign_uwqm_pre;
2070
2071
2072 PROCEDURE update_task_assign_uwqm_pre ( x_return_status OUT NOCOPY VARCHAR2 ) As
2073
2074 l_msg_count NUMBER;
2075 l_msg_data VARCHAR2(2000);
2076 l_return_status varchar2(5);
2077 l_owner_id number;
2078 l_owner_type varchar2(25);
2079 l_source_object_id number;
2080 l_source_object_type_code varchar2(30);
2081 l_count number := 0;
2082 l_importance_level number;
2083 l_assignee_id number;
2084 l_assignee_type varchar2(30);
2085 l_status varchar2(20);
2086 l_task_type_id number;
2087
2088 l_ws_id1 NUMBER;
2089 l_ws_id2 NUMBER := null;
2090 l_association_ws_id NUMBER;
2091 l_dist_from IEU_UWQM_WORK_SOURCES_B.DISTRIBUTE_FROM%TYPE;
2092 l_dist_to IEU_UWQM_WORK_SOURCES_B.DISTRIBUTE_TO%TYPE;
2093 l_uwq_assignee_id number;
2094 l_uwq_assignee_type varchar2(30);
2095 l_group_id varchar2(5);
2096 l_other_asg_update_flag varchar2(5);
2097 l_old_assignee_id number;
2098 l_old_assignee_type varchar2(30);
2099 l_entity varchar2(30);
2100
2101 l_tasks_rules_func VARCHAR2(500);
2102 l_orig_grp_owner NUMBER;
2103
2104 l_tasks_data_list SYSTEM.WR_TASKS_DATA_NST;
2105 l_def_data_list SYSTEM.DEF_WR_DATA_NST;
2106
2107 l_association_ws_code varchar2(32);
2108 l_activation_status varchar2(5);
2109
2110 l_audit_trail_rec SYSTEM.WR_AUDIT_TRAIL_NST;
2111 l_workitem_comment_code1 varchar2(100) := null;
2112 l_workitem_comment_code2 varchar2(100) := null;
2113 l_update_wr_item_call varchar2(5);
2117 l_del_flag VARCHAR2(10);
2114 l_wi_exists VARCHAR2(10);
2115 l_tsk_sts_id NUMBER;
2116 l_task_status VARCHAR2(500);
2118 l_sql_err VARCHAR2(50);
2119 l_sql_code VARCHAR2(100);
2120
2121 l_ins_task_id number;
2122 l_ins_task_number varchar2(30);
2123 l_ins_customer_id number;
2124 l_ins_owner_id number;
2125 l_ins_owner_type_code varchar2(30);
2126 l_ins_source_object_id number;
2127 l_ins_source_object_type_code varchar2(30);
2128 l_ins_task_name varchar2(80);
2129 l_ins_assignee_id number;
2130 l_ins_assignee_type varchar2(25);
2131 l_ins_task_priority_id number;
2132 l_ins_date_selected varchar2(1);
2133 l_ins_due_date date;
2134 l_ins_planned_end_date date;
2135 l_ins_actual_ins_end_date date;
2136 l_ins_scheduled_end_date date;
2137 l_ins_planned_start_date date;
2138 l_ins_actual_ins_start_date date;
2139 l_ins_scheduled_start_date date;
2140 l_ins_importance_level number;
2141 l_ins_priority_code varchar2(30);
2142 l_ins_task_status varchar2(10);
2143 l_ins_task_status_id number;
2144 l_ins_task_type_id number;
2145 l_ins_work_item_id NUMBER;
2146 l_wr_assignee_id ieu_uwqm_items.assignee_id%TYPE; -- Niraj, Bug 4220060, Added
2147
2148 begin
2149 -- insert into p_temp(msg) values('proc update asg task');
2150
2151
2152 l_ins_priority_code := 'LOW';
2153 --l_update_wr_item_call := 'Y'; -- Niraj, Bug 4220060, Commented
2154 l_dist_from := 'GROUP_OWNED';
2155 l_dist_to := 'INDIVIDUAL_ASSIGNED';
2156 /*** Update Task Assignee
2157 ** First the Activation Status is checked
2158 ** If the Work Source is activated, then check if any Tasks Rules Function is registered
2159 ** If the task Rules function is present, then execute it
2160 ** If the Task Rules function is not registered then the assignee will be created based on the following rules for Standard Tasks
2161 ** 1. Update assignee in UWQ if Owner is a Group and the assignee should be a member of the group
2162 ** 2. If the Owner is not a group, then Assignee will not be created in UWQ
2163 ** 3. If the Assignee is not a member of the Group, then UWQ Assignee will be the most recent Group member if it exists.
2164 ** If there are no group members present, then the assignee will be null
2165 ***/
2166
2167 -- reset del task pkg lvl variable
2168 l_del_task_id := null;
2169
2170 l_object_code := 'TASK';
2171 l_not_valid_flag := 'N';
2172
2173 -- l_workitem_comment_code1 := 'GO_IA';
2174
2175 -- if (l_task_source_obj_type_code is null)
2176 -- then
2177 begin
2178
2179 select source_object_type_code
2180 into l_task_source_obj_type_code
2181 from jtf_tasks_b
2182 where task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id;
2183
2184 exception when others then
2185 l_task_source_obj_type_code := null;
2186 end;
2187 -- end if;
2188
2189 if (l_task_source_obj_type_code is not null)
2190 then
2191
2192 BEGIN
2193
2194 Select ws_id
2195 into l_ws_id1
2196 from ieu_uwqm_work_sources_b
2197 --where object_code = 'TASK'
2198 --and nvl(not_valid_flag,'N') = 'N';
2199 where object_code = l_object_code
2200 and nvl(not_valid_flag,'N') = l_not_valid_flag;
2201 EXCEPTION
2202 WHEN OTHERS THEN l_ws_id1 := null;
2203 END;
2204
2205 BEGIN
2206
2207 Select ws_id
2208 into l_ws_id2
2209 from ieu_uwqm_work_sources_b
2210 where object_code = l_task_source_obj_type_code
2211 --and nvl(not_valid_flag,'N') = 'N';
2212 and nvl(not_valid_flag,'N') = l_not_valid_flag;
2213 EXCEPTION
2214 WHEN OTHERS THEN
2215
2216 l_ws_id2 := null;
2217 END;
2218
2219 if (l_ws_id2 is not null)
2220 then
2221
2222 -- Check if Any Work Source Association exists for this combination of Object Code/Source Obj Code
2223 BEGIN
2224
2225 SELECT a.ws_id, b.ws_code
2226 INTO l_association_ws_id, l_association_ws_code
2227 FROM ieu_uwqm_ws_assct_props a, ieu_uwqm_work_sources_b b
2228 WHERE child_ws_id = l_ws_id1
2229 AND parent_ws_id = l_ws_id2
2230 AND a.ws_id = b.ws_id
2231 --AND nvl(b.not_valid_flag,'N') = 'N';
2232 AND nvl(b.not_valid_flag,'N') = l_not_valid_flag;
2233
2234 EXCEPTION
2235 WHEN NO_DATA_FOUND THEN
2236 l_association_ws_id := null;
2237 END;
2238
2239 else
2240 l_association_ws_id := null;
2241
2242 end if;
2243
2244 if l_association_ws_id is not null then
2245 IEU_WR_PUB.CHECK_WS_ACTIVATION_STATUS(
2246 p_api_version => 1,
2247 p_init_msg_list => 'T',
2251 x_msg_count => l_msg_count,
2248 p_commit => 'F',
2249 p_ws_code => l_association_ws_code,
2250 x_ws_activation_status => l_activation_status,
2252 x_msg_data => l_msg_data,
2253 x_return_status => l_return_status);
2254
2255 else
2256 IEU_WR_PUB.CHECK_WS_ACTIVATION_STATUS(
2257 p_api_version => 1,
2258 p_init_msg_list => 'T',
2259 p_commit => 'F',
2260 p_ws_code => 'TASK',
2261 x_ws_activation_status => l_activation_status,
2262 x_msg_count => l_msg_count,
2263 x_msg_data => l_msg_data,
2264 x_return_status => l_return_status);
2265 end if;
2266
2267 -- Get the Tasks Rules Function
2268
2269 if (l_association_ws_id is not null)
2270 then
2271
2272 BEGIN
2273
2274 SELECT ws_b.tasks_rules_function
2275 INTO l_tasks_rules_func
2276 FROM ieu_uwqm_ws_assct_props ws_b
2277 WHERE ws_b.ws_id = l_association_ws_id;
2278
2279 EXCEPTION
2280 WHEN OTHERS THEN
2281 l_tasks_rules_func := null;
2282 END;
2283
2284 end if;
2285
2286 end if;
2287
2288 if l_activation_status = 'Y' then
2289
2290 /* insert into p_temp(msg) values ('assignee_role: '||jtf_task_assignments_pub.p_task_assignments_user_hooks.assignee_role||
2291 ' booking end date: '|| jtf_task_assignments_pub.p_task_assignments_user_hooks.booking_end_date); */
2292
2293
2294 if (jtf_task_assignments_pub.p_task_assignments_user_hooks.assignee_role = 'OWNER')
2295 then
2296 l_ins_due_date := jtf_task_assignments_pub.p_task_assignments_user_hooks.booking_end_date;
2297 else
2298 -- Get the Bookings End Date from JTF_TASK_ASSIGNMENTS where assignee_role= owner
2299 BEGIN
2300
2301 SELECT booking_end_date
2302 INTO l_ins_due_date
2303 FROM JTF_TASK_ALL_ASSIGNMENTS
2304 WHERE task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
2305 AND assignee_role = 'OWNER';
2306
2307 EXCEPTION
2308 WHEN OTHERS THEN
2309 null;
2310 END;
2311 end if;
2312
2313
2314 if (l_tasks_rules_func is not null)
2315 then
2316
2317 l_tasks_data_list := SYSTEM.WR_TASKS_DATA_NST();
2318
2319 l_tasks_data_list.extend;
2320
2321 l_tasks_data_list(l_tasks_data_list.last) := SYSTEM.WR_TASKS_DATA_OBJ (
2322 'UPDATE_TASK_ASG',
2323 jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id,
2324 null,
2325 FND_API.G_MISS_CHAR,
2326 FND_API.G_MISS_CHAR,
2327 FND_API.G_MISS_NUM,
2328 FND_API.G_MISS_NUM,
2329 FND_API.G_MISS_NUM,
2330 FND_API.G_MISS_NUM,
2331 FND_API.G_MISS_CHAR,
2332 FND_API.G_MISS_NUM,
2333 l_task_source_obj_type_code,
2334 FND_API.G_MISS_NUM,
2335 NULL,
2336 FND_API.G_MISS_DATE,
2337 FND_API.G_MISS_DATE,
2338 FND_API.G_MISS_DATE,
2339 FND_API.G_MISS_DATE,
2340 FND_API.G_MISS_DATE,
2341 FND_API.G_MISS_DATE,
2342 jtf_task_assignments_pub.p_task_assignments_user_hooks.resource_type_code,
2343 jtf_task_assignments_pub.p_task_assignments_user_hooks.resource_id,
2344 jtf_task_assignments_pub.p_task_assignments_user_hooks.assignment_status_id);
2345
2346 l_def_data_list := SYSTEM.DEF_WR_DATA_NST();
2347
2348 l_def_data_list.extend;
2349
2350 -- Get the Group Owner
2351
2352 BEGIN
2353 l_workitem_obj_code := 'TASK';
2354 l_owner_type_actual := 'RS_GROUP';
2355 Select owner_id
2356 into l_orig_grp_owner
2357 from ieu_uwqm_items
2358 where WORKITEM_PK_ID = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
2359 -- and workitem_obj_code = 'TASK'
2360 -- and owner_type_actual = 'RS_GROUP';
2361 and workitem_obj_code = l_workitem_obj_code
2362 and owner_type_actual = l_owner_type_actual;
2363
2364 EXCEPTION
2365 when others then
2366 l_orig_grp_owner := null;
2367 END;
2368
2369 l_def_data_list(l_def_data_list.last) := SYSTEM.DEF_WR_DATA_OBJ(
2370 FND_API.G_MISS_CHAR,
2371 FND_API.G_MISS_CHAR,
2372 l_ins_due_date,
2373 'TASKS',
2374 l_orig_grp_owner
2375 );
2376
2377 execute immediate
2378 'BEGIN '||l_tasks_rules_func ||
2379 ' ( :1, :2, :3, :4 , :5); END ; '
2380 USING
2384 else
2381 IN l_tasks_data_list, IN l_def_data_list , OUT l_msg_count, OUT l_msg_data, OUT l_return_status;
2382
2383 x_return_status := l_return_status;
2385
2386 BEGIN
2387
2388
2389 -- if (jtf_task_assignments_pub.p_task_assignments_user_hooks.assignee_role = 'ASSIGNEE')
2390 -- then
2391
2392
2393 -- Get the Task Status Id
2394 begin
2395 select task_status_id, deleted_flag
2396 into l_tsk_sts_id, l_del_flag
2397 from jtf_tasks_b
2398 where task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id;
2399 exception
2400 when others then null;
2401 end;
2402
2403 if (jtf_tasks_pub.p_task_user_hooks.task_status_id is not null)
2404 then
2405 l_tsk_sts_id := jtf_tasks_pub.p_task_user_hooks.task_status_id;
2406 end if;
2407
2408 -- Get the Task Status based on task Status Id
2409 begin
2410 select 'CLOSE' into l_task_status
2411 from jtf_task_statuses_vl
2412 where (nvl(closed_flag, 'N') = 'Y'
2413 or nvl(completed_flag, 'N') = 'Y'
2414 or nvl(cancelled_flag, 'N') = 'Y'
2415 or nvl(rejected_flag, 'N') = 'Y')
2416 and task_status_id = l_tsk_sts_id;
2417 EXCEPTION WHEN others THEN null;
2418 end;
2419
2420 -- Check if the Work Item exists in UWQ Metaphor Table
2421 begin
2422 select 'Y'
2423 into l_wi_exists
2424 from ieu_uwqm_items
2425 where workitem_pk_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
2426 and workitem_obj_code = 'TASK';
2427 exception
2428 when others then
2429 l_wi_exists := 'N';
2430 end;
2431
2432 -- insert into p_temp(msg) values ('Task Status Id from Tasks table: '||l_tsk_sts_id||' l_wi_exists: '||l_wi_exists||' l_del_flag: '||l_del_flag);
2433
2434 -- Closed Work Items are not currently migrated. No updates will be done to UWQ Metaphor table
2435 -- if the Task is in closed/deleted status and the Work Item is not present in UWQ table.
2436
2437 if ( ( (l_task_status = 'CLOSE') or (l_del_flag = 'Y') )
2438 and (l_wi_exists = 'N'))
2439 then
2440 -- insert into p_temp(msg) values ('close/del and rec does not exists.. ret success');
2441 x_return_status := fnd_api.g_ret_sts_success;
2442 else
2443 -- If the work item status is not closed or deleted and if the work item does not exist in Work Repository
2444 -- then create the work item
2445 if (l_wi_exists = 'N')
2446 then
2447 --insert into p_temp values (' selecting data from tasks table');
2448
2449 begin
2450
2451 select tb.task_id, tb.task_number, tb.customer_id, tb.owner_id, tb.owner_type_code,
2452 tb.source_object_id, tb.source_object_type_code,
2453 tb.planned_start_date, tb.planned_end_date, tb.actual_start_date, tb.actual_end_date,
2454 tb.scheduled_start_date, tb.scheduled_end_date,tb.task_type_id,
2455 tb.task_status_id, tt.task_name, tp.importance_level, ip.priority_code
2456 into l_ins_task_id, l_ins_task_number, l_ins_customer_id, l_ins_owner_id, l_ins_owner_type_code,
2457 l_ins_source_object_id, l_ins_source_object_type_code,
2458 l_ins_planned_start_date,
2459 l_ins_planned_end_date, l_ins_actual_ins_start_date, l_ins_actual_ins_end_date, l_ins_scheduled_start_date, l_ins_scheduled_end_date,
2460 l_ins_task_type_id, l_ins_task_status_id, l_ins_task_name, l_ins_importance_level, l_ins_priority_code
2461 from jtf_tasks_b tb, jtf_tasks_tl tt, jtf_task_priorities_vl tp, ieu_uwqm_priorities_b ip
2462 where tb.task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
2463 and tb.entity = 'TASK'
2464 and tb.task_id = tt.task_id
2465 and tt.language = userenv('LANG')
2466 and tp.task_priority_id = nvl(tb.task_priority_id, 4)
2467 and least(tp.importance_level, 4) = ip.priority_level;
2468 exception
2469 when others then
2470 null;
2471 -- insert into p_temp values('raising err ');
2472 -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2473 --raise;
2474 end;
2475
2476 l_owner_id := l_ins_owner_id;
2477 l_owner_type := l_ins_owner_type_code;
2478
2479 begin
2480 select 'CLOSE' into l_ins_task_status
2481 from jtf_task_statuses_vl
2482 where (nvl(closed_flag, 'N') = 'Y'
2483 or nvl(completed_flag, 'N') = 'Y'
2484 or nvl(cancelled_flag, 'N') = 'Y'
2485 or nvl(rejected_flag, 'N') = 'Y')
2486 and task_status_id = l_ins_task_status_id;
2487 EXCEPTION WHEN others THEN
2488 begin
2489 select 'SLEEP' into l_ins_task_status
2490 from jtf_task_statuses_vl
2491 where nvl(on_hold_flag, 'N') = 'Y'
2492 and task_status_id = l_ins_task_status_id;
2493 EXCEPTION WHEN others THEN
2494 l_ins_task_status := 'OPEN';
2495 end;
2496 end;
2497
2498
2499 /******
2500 insert into p_temp values('1'||l_ins_task_id||' '|| l_ins_task_number||' '|| l_ins_customer_id||' '|| l_ins_owner_id||' '|| l_ins_owner_type_code);
2501 insert into p_temp values('2'||l_ins_source_object_id||' '|| l_ins_source_object_type_code||' '|| l_ins_due_date||' '|| l_ins_planned_start_date);
2502 insert into p_temp values('3'||l_ins_planned_end_date||' '|| l_ins_actual_ins_start_date||' '|| l_ins_actual_ins_end_date||' '|| l_ins_scheduled_start_date||' '|| l_ins_scheduled_end_date);
2503 insert into p_temp values('4'||l_ins_task_type_id||' '|| l_ins_task_status_id||' '|| l_ins_task_name||' '|| l_ins_importance_level||' '|| l_ins_priority_code);
2504 ******/
2505
2506
2507 else
2508 begin
2509 l_workitem_obj_code := 'TASK';
2510 select owner_id, owner_type, source_object_id, source_object_type_code, assignee_id -- Niraj, 4220060, Added assignee_id
2511 into l_owner_id, l_owner_type, l_source_object_id, l_source_object_type_code, l_wr_assignee_id -- Niraj, 4220060, Added
2512 from ieu_uwqm_items
2513 where workitem_pk_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
2514 -- and workitem_obj_code = 'TASK';
2515 and workitem_obj_code = l_workitem_obj_code;
2516 EXCEPTION WHEN others THEN null;
2517 end ;
2518
2519 end if;
2520
2521 begin
2522 select 'OPEN' INTO l_status
2523 from jtf_task_statuses_vl b
2524 where nvl(b.closed_flag, 'N') = 'N'
2525 and nvl(b.completed_flag, 'N') = 'N'
2526 and nvl(b.cancelled_flag, 'N') = 'N'
2527 and nvl(b.rejected_flag, 'N') = 'N'
2528 and b.task_status_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.assignment_status_id;
2529 exception when others then l_status := 'CLOSED';
2530 end;
2531
2532
2533 if (jtf_task_assignments_pub.p_task_assignments_user_hooks.resource_type_code = 'RS_TEAM')
2534 or (l_status = 'CLOSED')
2535 then
2536 l_assignee_id := null;
2537 l_assignee_type := null;
2538
2539 begin
2540 select c.resource_id, c.resource_type_code
2541 into l_assignee_id, l_assignee_type
2545 from jtf_task_assignments a,jtf_task_statuses_vl b
2542 from jtf_task_assignments c
2543 where c.task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
2544 and c.last_update_date = (select max(a.last_update_date)
2546 where a.task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
2547 and a.assignee_role = 'ASSIGNEE'
2548 and a.resource_type_code <> 'RS_TEAM'
2549 and a.assignment_status_id = b.task_status_id
2550 and (nvl(b.closed_flag, 'N') = 'N'
2551 and nvl(b.completed_flag, 'N') = 'N'
2552 and nvl(b.cancelled_flag, 'N') = 'N'
2553 and nvl(b.rejected_flag, 'N') = 'N'
2554 and b.task_status_id = c.assignment_status_iD))
2555 and assignee_role = 'ASSIGNEE'
2556 and c.resource_type_code <> 'RS_TEAM'
2557 and c.task_assignment_id <> jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id
2558 and rownum < 2;
2559 exception when others then
2560 l_assignee_id := null;
2561 l_assignee_type := null;
2562 end;
2563
2564 else
2565 l_assignee_id := jtf_task_assignments_pub.p_task_assignments_user_hooks.resource_id;
2566 l_assignee_type := jtf_task_assignments_pub.p_task_assignments_user_hooks.resource_type_code;
2567 end if;
2568
2569 begin
2570 select task_type_id, entity into l_task_type_id, l_entity
2571 from jtf_tasks_b
2572 where task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id;
2573 exception when others then l_task_type_id := null;
2574 end;
2575
2576 if (l_dist_from = 'GROUP_OWNED') and
2577 (l_dist_to = 'INDIVIDUAL_ASSIGNED')
2578 then
2579 if l_owner_type = 'RS_GROUP'
2580 then
2581
2582 if l_assignee_type not in ('RS_TEAM', 'RS_GROUP') then
2583 begin
2584 select 'Y' into l_group_id
2585 from jtf_rs_group_members
2586 where resource_id = l_assignee_id
2587 and group_id = l_owner_id
2588 and nvl(delete_flag,'N') <> 'Y'
2589 and rownum < 2;
2590 exception when others then l_group_id := 'N';
2591 end;
2592 end if;
2593 -- insert into p_temp values(' grp member: '||l_group_id);
2594
2595 if (nvl(l_group_id, 'N') ='N')
2596 then
2597 begin
2598 select c.resource_id, c.resource_type_code
2599 into l_assignee_id, l_assignee_type
2600 from jtf_task_assignments c
2601 where c.task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
2602 and c.assignee_role = 'ASSIGNEE'
2603 and c.resource_type_code not in ('RS_TEAM', 'RS_GROUP')
2604 and c.resource_id in ( select resource_id
2605 from jtf_rs_group_members
2606 where group_id = l_owner_id
2607 and nvl(delete_flag,'N') <> 'Y')
2608 --and c.task_assignment_id <> jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id
2609 and c.last_update_date = (select max(a.last_update_date)
2610 from jtf_task_assignments a,jtf_task_statuses_vl b
2611 where a.task_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id
2612 and a.assignee_role = 'ASSIGNEE'
2613 and a.task_assignment_id <> jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id
2614 and a.assignment_status_id = b.task_status_id
2615 and a.resource_type_code not in ('RS_TEAM', 'RS_GROUP')
2616 and a.resource_id in ( select resource_id
2617 from jtf_rs_group_members
2618 where group_id = l_owner_id
2619 and nvl(delete_flag,'N') <> 'Y')
2620 and (nvl(b.closed_flag, 'N') = 'N'
2621 and nvl(b.completed_flag, 'N') = 'N'
2622 and nvl(b.cancelled_flag, 'N') = 'N'
2623 and nvl(b.rejected_flag, 'N') = 'N'
2624 and b.task_status_id = c.assignment_status_id)
2625 )
2626
2627 and rownum < 2;
2628 exception when others then
2629 l_sql_err := SQLERRM;
2630 l_sql_code := SQLCODE;
2631 -- insert into p_temp values ('excep: '||l_SQL_ERR|| l_SQL_CODE);
2632 l_assignee_id := null;
2633 l_assignee_type := null;
2634 end;
2635
2636 /** insert into p_temp values('task id: '||jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id||
2637 ' task asg id: '||jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id||
2638 ' owner id: '||l_owner_id ||
2639 ' asg id: '||l_assignee_id||' asg type: '|| l_assignee_type); **/
2640
2641 end if; /* l_group_id */
2642
2643 else
2644
2645 l_assignee_id := null;
2646 l_assignee_type := null;
2647
2648 end if; /* owner_type = RS_GROUP */
2649
2650 end if; /* if dist_From */
2651
2652 if l_assignee_id is not null then
2653 l_workitem_comment_code2 := 'GRP_MAX_ASSGN';
2654 end if;
2655
2656 -- Niraj, Bug 4220060
2657 IF ( ( (jtf_task_assignments_pub.p_task_assignments_user_hooks.assignee_role = 'ASSIGNEE') AND
2658 (NVL(l_wr_assignee_id, -1) <> NVL(l_assignee_id, -1) )
2659 ) OR
2660 (jtf_task_assignments_pub.p_task_assignments_user_hooks.assignee_role = 'OWNER') )
2661 THEN
2662 l_update_wr_item_call := 'Y';
2663 ELSE
2664 l_update_wr_item_call := 'N';
2665 END IF;
2666
2667 -- Start 'l_update_wr_item_call' check
2668 IF (l_update_wr_item_call = 'Y') Then -- Niraj, 4220060
2672
2669 l_audit_trail_rec := SYSTEM.WR_AUDIT_TRAIL_NST();
2670
2671 l_audit_trail_rec.extend;
2673 l_audit_trail_rec(l_audit_trail_rec.LAST):= SYSTEM.WR_AUDIT_TRAIL_OBJ
2674 ('WORKITEM_UPDATE',
2675 'UPDATE_WR_ITEM',
2676 690,
2677 'IEU_TASKS_USERHOOKS.UPDATE_TASK_ASSIGN_UWQM_PRE',
2678 l_workitem_comment_code1,
2679 l_workitem_comment_code2,
2680 null,
2681 null,
2682 null);
2683
2684 -- insert into p_temp(msg) values ('passign due date as: '||l_ins_due_date);
2685
2686 if (jtf_task_assignments_pub.p_task_assignments_user_hooks.assignee_role = 'ASSIGNEE')
2687 and (l_entity = 'TASK')
2688 -- and (nvl(l_other_asg_update_flag, 'N') = 'N')
2689 -- and (nvl(l_update_wr_item_call, 'Y') = 'Y')
2690 then
2691
2692 if (l_wi_exists = 'N')
2693 then
2694 -- insert into p_temp values (' calling insert ');
2695
2696 IEU_WR_PUB.CREATE_WR_ITEM(
2697 p_api_version => 1.0,
2698 p_init_msg_list => FND_API.G_TRUE,
2699 p_commit => FND_API.G_true,
2700 p_workitem_obj_code => 'TASK',
2701 p_workitem_pk_id => l_ins_task_id,
2702 p_work_item_number => l_ins_task_number,
2703 p_title => l_ins_task_name,
2704 p_party_id => l_ins_customer_id,
2705 p_priority_code => l_ins_priority_code,
2706 p_due_date => l_ins_due_date,
2707 p_owner_id => l_ins_owner_id,
2708 p_owner_type => l_ins_owner_type_code,
2709 p_assignee_id => l_assignee_id,
2710 p_assignee_type => l_assignee_type,
2711 p_source_object_id => l_ins_source_object_id,
2712 p_source_object_type_code => l_ins_source_object_type_code,
2713 p_application_id => 690,
2714 p_ieu_enum_type_uuid => 'TASKS',
2715 p_work_item_status => l_ins_task_status,
2716 p_user_id => FND_GLOBAL.USER_ID,
2717 p_login_id => FND_GLOBAL.LOGIN_ID,
2718 x_work_item_id => l_ins_WORK_ITEM_ID,
2719 x_msg_count => l_msg_count,
2720 x_msg_data => l_MSG_DATA,
2721 x_return_status => l_RETURN_STATUS);
2722
2723 else
2724
2725 -- insert into p_temp values(' calling update wr item' );
2726 IEU_WR_PUB.UPDATE_WR_ITEM(
2727 p_api_version => 1.0,
2728 p_init_msg_list => FND_API.G_TRUE,
2729 p_commit => FND_API.G_FALSE,
2730 p_workitem_obj_code => 'TASK',
2731 p_workitem_pk_id => jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id,
2732 p_title => FND_API.G_MISS_CHAR,
2733 p_party_id => FND_API.G_MISS_NUM,
2734 p_priority_code => FND_API.G_MISS_CHAR,
2735 p_due_date => l_ins_due_date,
2736 p_owner_id => FND_API.G_MISS_NUM,
2737 p_owner_type => FND_API.G_MISS_CHAR,
2738 p_assignee_id => l_assignee_id,
2739 p_assignee_type => l_assignee_type,
2740 p_source_object_id => FND_API.G_MISS_NUM,
2741 p_source_object_type_code => FND_API.G_MISS_CHAR,
2742 p_application_id => 690,
2743 p_user_id => FND_GLOBAL.USER_ID,
2744 p_login_id => FND_GLOBAL.LOGIN_ID,
2745 p_work_item_status => FND_API.G_MISS_CHAR,
2746 p_audit_trail_rec => l_audit_trail_rec,
2747 x_msg_count => L_MSG_COUNT,
2748 x_msg_data => L_MSG_DATA,
2749 x_return_status => L_RETURN_STATUS);
2750
2751 end if;
2752
2753 x_return_status := l_return_status;
2754 -- insert into p_temp values(' called update wr items.. ret sts: '||x_return_status);
2755 elsif (jtf_task_assignments_pub.p_task_assignments_user_hooks.assignee_role = 'OWNER')
2756 then
2757 -- Create or Update Task User Hook should have been invoked
2758 -- before invoking Create/Update Task Assignment if the assignee role is owner.
2759 -- So update the rec only if its present in IEU_UWQM_ITEMS
2760 if (l_wi_exists <> 'N')
2761 then
2762
2763 -- insert into p_temp(msg) values(' calling update wr item' );
2764 IEU_WR_PUB.UPDATE_WR_ITEM(
2765 p_api_version => 1.0,
2766 p_init_msg_list => FND_API.G_TRUE,
2767 p_commit => FND_API.G_FALSE,
2768 p_workitem_obj_code => 'TASK',
2769 p_workitem_pk_id => jtf_task_assignments_pub.p_task_assignments_user_hooks.task_id,
2770 p_title => FND_API.G_MISS_CHAR,
2771 p_party_id => FND_API.G_MISS_NUM,
2772 p_priority_code => FND_API.G_MISS_CHAR,
2773 p_due_date => l_ins_due_date,
2774 p_owner_id => FND_API.G_MISS_NUM,
2775 p_owner_type => FND_API.G_MISS_CHAR,
2776 p_assignee_id => l_assignee_id,
2777 p_assignee_type => l_assignee_type,
2778 p_source_object_id => FND_API.G_MISS_NUM,
2779 p_source_object_type_code => FND_API.G_MISS_CHAR,
2780 p_application_id => 690,
2781 p_user_id => FND_GLOBAL.USER_ID,
2782 p_login_id => FND_GLOBAL.LOGIN_ID,
2783 p_work_item_status => FND_API.G_MISS_CHAR,
2784 p_audit_trail_rec => l_audit_trail_rec,
2785 x_msg_count => L_MSG_COUNT,
2786 x_msg_data => L_MSG_DATA,
2787 x_return_status => L_RETURN_STATUS);
2788
2789 x_return_status := l_return_status;
2790
2791 else
2792
2793 x_return_status := fnd_api.g_ret_sts_success;
2794
2795 end if;
2796
2797 elsif (l_entity <> 'TASK')
2798 then
2799 x_return_status := fnd_api.g_ret_sts_success;
2800 end if;
2801 ELSE -- Niraj, 4220060
2802 x_return_status := fnd_api.g_ret_sts_success; -- Niraj, 4220060
2803 END IF; -- Niraj, 4220060
2807
2804 -- End 'l_update_wr_item_call' check
2805
2806 end if; /* Task Work Item exists in UWQ Metaphor */
2808 -- else /* assignee_role <> assingee */
2809 -- x_return_status := fnd_api.g_ret_sts_success;
2810 -- end if;
2811
2812 EXCEPTION WHEN OTHERS THEN
2813 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2814
2815 END;
2816
2817 end if; /* Tasks Rules Func */
2818 elsif l_activation_status = 'N' then
2819 x_return_status := fnd_api.g_ret_sts_success;
2820 end if;
2821
2822 end update_task_assign_uwqm_pre;
2823
2824 PROCEDURE delete_task_assign_uwqm_pre (x_return_status OUT NOCOPY VARCHAR2 ) As
2825
2826 l_msg_count number;
2827 l_msg_data VARCHAR2(2000);
2828 l_return_status varchar2(5);
2829 l_assignee_id number;
2830 l_assignee_type varchar2(30);
2831 l_owner_id number;
2832 l_owner_type varchar2(25);
2833 l_source_object_id number;
2834 l_source_object_type_code varchar2(30);
2835 l_task_id number;
2836 l_task_type_id number;
2837
2838 l_ws_id1 NUMBER;
2839 l_ws_id2 NUMBER := null;
2840 l_association_ws_id NUMBER;
2841 l_dist_from IEU_UWQM_WORK_SOURCES_B.DISTRIBUTE_FROM%TYPE;
2842 l_dist_to IEU_UWQM_WORK_SOURCES_B.DISTRIBUTE_TO%TYPE;
2843 l_uwq_assignee_id number;
2844 l_uwq_assignee_type varchar2(30);
2845 l_other_asg_deleted_flag varchar2(5);
2846 l_entity varchar2(30);
2847 l_orig_grp_owner NUMBER;
2848
2849 l_tasks_rules_func VARCHAR2(500);
2850
2851 l_tasks_data_list SYSTEM.WR_TASKS_DATA_NST;
2852 l_def_data_list SYSTEM.DEF_WR_DATA_NST;
2853
2854 l_association_ws_code varchar2(32);
2855 l_activation_status varchar2(5);
2856
2857 l_audit_trail_rec SYSTEM.WR_AUDIT_TRAIL_NST;
2858 l_workitem_comment_code1 varchar2(100) := null;
2859 l_workitem_comment_code2 varchar2(100) := null;
2860 l_wi_exists VARCHAR2(10);
2861 l_tsk_sts_id NUMBER;
2862 l_task_status VARCHAR2(500);
2863 l_del_flag VARCHAR2(10);
2864
2865 l_ins_task_id number;
2866 l_ins_task_number varchar2(30);
2867 l_ins_customer_id number;
2868 l_ins_owner_id number;
2869 l_ins_owner_type_code varchar2(30);
2870 l_ins_source_object_id number;
2871 l_ins_source_object_type_code varchar2(30);
2872 l_ins_task_name varchar2(80);
2873 l_ins_assignee_id number;
2874 l_ins_assignee_type varchar2(25);
2875 l_ins_task_priority_id number;
2876 l_ins_date_selected varchar2(1);
2877 l_ins_due_date date;
2878 l_ins_planned_end_date date;
2879 l_ins_actual_ins_end_date date;
2880 l_ins_scheduled_end_date date;
2881 l_ins_planned_start_date date;
2882 l_ins_actual_ins_start_date date;
2883 l_ins_scheduled_start_date date;
2884 l_ins_importance_level number;
2885 l_ins_priority_code varchar2(30);
2886 l_ins_task_status varchar2(10);
2887 l_ins_task_status_id number;
2888 l_ins_task_type_id number;
2889 l_ins_work_item_id NUMBER;
2890 l_task_asg_count NUMBER;
2891
2892 begin
2893
2894
2895 l_ins_priority_code := 'LOW';
2896 l_dist_from := 'GROUP_OWNED';
2897 l_dist_to := 'INDIVIDUAL_ASSIGNED';
2898 l_object_code := 'TASK';
2899 l_not_valid_flag := 'N';
2900
2901 -- l_workitem_comment_code1 := 'GO_IA';
2902
2903 begin
2904 select task_id
2905 into l_task_id
2906 from jtf_task_all_assignments
2907 where task_assignment_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id;
2908 exception when others then null;
2909 end;
2910
2911
2912 -- if (l_task_source_obj_type_code is null)
2913 -- then
2914 begin
2915
2916 select source_object_type_code
2917 into l_task_source_obj_type_code
2918 from jtf_tasks_b
2919 where task_id = l_task_id;
2920 exception when others then
2921 l_task_source_obj_type_code := null;
2922 end;
2923 -- end if;
2924
2925 if (l_task_source_obj_type_code is not null)
2926 then
2927
2928 BEGIN
2929
2930 Select ws_id
2931 into l_ws_id1
2932 from ieu_uwqm_work_sources_b
2933 --where object_code = 'TASK'
2934 --and nvl(not_valid_flag,'N') = 'N';
2935 where object_code = l_object_code
2936 and nvl(not_valid_flag,'N') = l_not_valid_flag;
2937 EXCEPTION
2938 WHEN OTHERS THEN l_ws_id1 := null;
2939 END;
2940
2941 BEGIN
2942
2943 Select ws_id
2944 into l_ws_id2
2945 from ieu_uwqm_work_sources_b
2946 where object_code = l_task_source_obj_type_code
2947 --and nvl(not_valid_flag,'N') = 'N';
2948 and nvl(not_valid_flag,'N') = l_not_valid_flag;
2949 EXCEPTION
2950 WHEN OTHERS THEN
2951
2952 l_ws_id2 := null;
2953 END;
2954
2955 if (l_ws_id2 is not null)
2956 then
2957
2958 -- Check if Any Work Source Association exists for this combination of Object Code/Source Obj Code
2959 BEGIN
2960
2961 SELECT a.ws_id, b.ws_code
2965 AND parent_ws_id = l_ws_id2
2962 INTO l_association_ws_id, l_association_ws_code
2963 FROM ieu_uwqm_ws_assct_props a, ieu_uwqm_work_sources_b b
2964 WHERE child_ws_id = l_ws_id1
2966 AND a.ws_id = b.ws_id
2967 --AND nvl(b.not_valid_flag,'N') = 'N';
2968 AND nvl(b.not_valid_flag,'N') = l_not_valid_flag;
2969
2970 EXCEPTION
2971 WHEN NO_DATA_FOUND THEN
2972 l_association_ws_id := null;
2973 END;
2974
2975 else
2976 l_association_ws_id := null;
2977
2978 end if;
2979
2980 if l_association_ws_id is not null then
2981 IEU_WR_PUB.CHECK_WS_ACTIVATION_STATUS(
2982 p_api_version => 1,
2983 p_init_msg_list => 'T',
2984 p_commit => 'F',
2985 p_ws_code => l_association_ws_code,
2986 x_ws_activation_status => l_activation_status,
2987 x_msg_count => l_msg_count,
2988 x_msg_data => l_msg_data,
2989 x_return_status => l_return_status);
2990
2991 else
2992 IEU_WR_PUB.CHECK_WS_ACTIVATION_STATUS(
2993 p_api_version => 1,
2994 p_init_msg_list => 'T',
2995 p_commit => 'F',
2996 p_ws_code => 'TASK',
2997 x_ws_activation_status => l_activation_status,
2998 x_msg_count => l_msg_count,
2999 x_msg_data => l_msg_data,
3000 x_return_status => l_return_status);
3001 end if;
3002
3003 -- Get the Tasks Rules Function
3004
3005 if (l_association_ws_id is not null)
3006 then
3007
3008 BEGIN
3009
3010 SELECT ws_b.tasks_rules_function
3011 INTO l_tasks_rules_func
3012 FROM ieu_uwqm_ws_assct_props ws_b
3013 WHERE ws_b.ws_id = l_association_ws_id;
3014
3015 EXCEPTION
3016 WHEN OTHERS THEN
3017 l_tasks_rules_func := null;
3018 END;
3019
3020 end if;
3021
3022 end if;
3023
3024 if l_activation_status = 'Y' then
3025
3026 /* insert into p_temp(msg) values ('assignee_role: '||jtf_task_assignments_pub.p_task_assignments_user_hooks.assignee_role||
3027 ' booking end date: '|| jtf_task_assignments_pub.p_task_assignments_user_hooks.booking_end_date); */
3028
3029
3030 -- Get the Bookings End Date from JTF_TASK_ASSIGNMENTS where assignee_role= owner
3031 BEGIN
3032
3033 SELECT booking_end_date
3034 INTO l_ins_due_date
3035 FROM JTF_TASK_ALL_ASSIGNMENTS
3036 WHERE task_id = (select task_id from jtf_task_all_assignments
3037 where task_assignment_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id)
3038 AND assignee_role = 'OWNER';
3039
3040 EXCEPTION
3041 WHEN OTHERS THEN
3042 null;
3043 END;
3044
3045 --insert into p_temp(msg) values (' selected due date: '||l_ins_due_date);
3046 if (l_tasks_rules_func is not null)
3047 then
3048
3049 l_tasks_data_list := SYSTEM.WR_TASKS_DATA_NST();
3050
3051 l_tasks_data_list.extend;
3052
3053 l_tasks_data_list(l_tasks_data_list.last) := SYSTEM.WR_TASKS_DATA_OBJ (
3054 'DELETE_TASK_ASG',
3055 l_task_id,
3056 null,
3057 FND_API.G_MISS_CHAR,
3058 FND_API.G_MISS_CHAR,
3059 FND_API.G_MISS_NUM,
3060 FND_API.G_MISS_NUM,
3061 FND_API.G_MISS_NUM,
3062 FND_API.G_MISS_NUM,
3063 FND_API.G_MISS_CHAR,
3064 FND_API.G_MISS_NUM,
3065 l_task_source_obj_type_code,
3066 FND_API.G_MISS_NUM,
3067 NULL,
3068 FND_API.G_MISS_DATE,
3069 FND_API.G_MISS_DATE,
3070 FND_API.G_MISS_DATE,
3071 FND_API.G_MISS_DATE,
3072 FND_API.G_MISS_DATE,
3073 FND_API.G_MISS_DATE,
3074 null,
3075 null,
3076 null);
3077
3078 l_def_data_list := SYSTEM.DEF_WR_DATA_NST();
3079
3080 l_def_data_list.extend;
3081
3082 -- Get the Group Owner
3083
3084 BEGIN
3085 l_workitem_obj_code := 'TASK';
3086 l_owner_type_actual := 'RS_GROUP';
3087 Select owner_id
3088 into l_orig_grp_owner
3089 from ieu_uwqm_items
3090 where WORKITEM_PK_ID = l_task_id
3091 -- and workitem_obj_code = 'TASK'
3092 -- and owner_type_actual = 'RS_GROUP';
3093 and workitem_obj_code = l_workitem_obj_code
3094 and owner_type_actual = l_owner_type_actual;
3095
3096 EXCEPTION
3097 when others then
3098 l_orig_grp_owner := null;
3099 END;
3100
3101 l_def_data_list(l_def_data_list.last) := SYSTEM.DEF_WR_DATA_OBJ(
3102 FND_API.G_MISS_CHAR,
3103 FND_API.G_MISS_CHAR,
3104 l_ins_due_date,
3105 'TASKS',
3106 l_orig_grp_owner
3107 );
3108
3112 USING
3109 execute immediate
3110 'BEGIN '||l_tasks_rules_func ||
3111 ' ( :1, :2, :3, :4 , :5); END ; '
3113 IN l_tasks_data_list, IN l_def_data_list , OUT l_msg_count, OUT l_msg_data, OUT l_return_status;
3114
3115 x_return_status := l_return_status;
3116 else
3117
3118 BEGIN
3119
3120
3121 -- Get the Task Status Id
3122 begin
3123 select task_status_id, deleted_flag
3124 into l_tsk_sts_id, l_del_flag
3125 from jtf_tasks_b
3126 where task_id = l_task_id;
3127 exception
3128 when others then null;
3129 end;
3130
3131 if (jtf_tasks_pub.p_task_user_hooks.task_status_id is not null)
3132 then
3133 l_tsk_sts_id := jtf_tasks_pub.p_task_user_hooks.task_status_id;
3134 end if;
3135
3136 -- Get the Task Status based on task Status Id
3137 begin
3138 select 'CLOSE' into l_task_status
3139 from jtf_task_statuses_vl
3140 where (nvl(closed_flag, 'N') = 'Y'
3141 or nvl(completed_flag, 'N') = 'Y'
3142 or nvl(cancelled_flag, 'N') = 'Y'
3143 or nvl(rejected_flag, 'N') = 'Y')
3144 and task_status_id = l_tsk_sts_id;
3145 EXCEPTION WHEN others THEN null;
3146 end;
3147
3148 -- Check if the Work Item exists in UWQ Metaphor Table
3149 begin
3150 select 'Y'
3151 into l_wi_exists
3152 from ieu_uwqm_items
3153 where workitem_pk_id =l_task_id
3154 and workitem_obj_code = 'TASK';
3155 exception
3156 when others then
3157 l_wi_exists := 'N';
3158 end;
3159
3160 -- insert into p_temp(msg) values ('del tsk asg..Task Status Id from Tasks table: '||l_tsk_sts_id||' l_wi_exists: '||l_wi_exists||' l_del_flag: '||l_del_flag);
3161
3162 -- Closed Work Items are not currently migrated. No updates will be done to UWQ Metaphor table
3163 -- if the Task is in closed/deleted status and the Work Item is not present in UWQ table.
3164
3165 if (l_wi_exists = 'N')
3166 then
3167
3168 if ( (l_task_status = 'CLOSE') or (l_del_flag = 'Y') )
3169 then
3170 --insert into p_temp(msg) values ('close/del and rec does not exists.. ret success');
3171 x_return_status := fnd_api.g_ret_sts_success;
3172
3173 elsif (nvl(l_del_task_id, '-9') = l_task_id)
3174 then
3175 -- This work item is being deleted
3176
3177 begin
3178 select count(0) into l_task_asg_count
3179 from jtf_task_assignments
3180 where task_id = l_task_id
3181 and assignee_role = 'ASSIGNEE';
3182 exception
3183 when others then l_task_asg_count := 0;
3184 end;
3185 --insert into p_temp values ('del based on l_del_task_id.. task asg cnt: '||l_task_asg_count);
3186
3187 -- Set the del_task_id to null if this is the last assignee being deleted.
3188 if (l_task_asg_count = 1)
3189 then
3190 l_del_task_id := null;
3191 end if;
3192 x_return_status := fnd_api.g_ret_sts_success;
3193
3194 else
3195 -- Create Work repository Item
3196
3197 --insert into p_temp values (' selecting data from tasks table');
3198
3199 begin
3200
3201 select tb.task_id, tb.task_number, tb.customer_id, tb.owner_id, tb.owner_type_code,
3202 tb.source_object_id, tb.source_object_type_code,
3203 -- decode(tb.date_selected, 'P', tb.planned_end_date,
3204 -- 'A', tb.actual_end_date, 'S', tb.scheduled_end_date, null, tb.scheduled_end_date) due_date,
3205 tb.planned_start_date, tb.planned_end_date, tb.actual_start_date, tb.actual_end_date,
3206 tb.scheduled_start_date, tb.scheduled_end_date,tb.task_type_id,
3207 tb.task_status_id, tt.task_name, tp.importance_level, ip.priority_code, tb.entity
3208 into l_ins_task_id, l_ins_task_number, l_ins_customer_id, l_ins_owner_id, l_ins_owner_type_code,
3209 l_ins_source_object_id, l_ins_source_object_type_code,
3210 l_ins_planned_start_date, l_ins_planned_end_date, l_ins_actual_ins_start_date, l_ins_actual_ins_end_date, l_ins_scheduled_start_date, l_ins_scheduled_end_date,
3211 l_ins_task_type_id, l_ins_task_status_id, l_ins_task_name, l_ins_importance_level, l_ins_priority_code, l_entity
3212 from jtf_tasks_b tb, jtf_tasks_tl tt, jtf_task_priorities_vl tp, ieu_uwqm_priorities_b ip
3213 where tb.task_id = l_task_id
3214 and tb.entity = 'TASK'
3215 and tb.task_id = tt.task_id
3216 and tt.language = userenv('LANG')
3217 and tp.task_priority_id = nvl(tb.task_priority_id, 4)
3218 and least(tp.importance_level, 4) = ip.priority_level;
3219 exception
3220 when others then
3221 null;
3222 -- insert into p_temp values('raising err ');
3223 -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3224 --raise;
3225 end;
3226
3227 l_owner_id := l_ins_owner_id;
3228 l_owner_type := l_ins_owner_type_code;
3229
3230 begin
3231 select 'CLOSE' into l_ins_task_status
3232 from jtf_task_statuses_vl
3233 where (nvl(closed_flag, 'N') = 'Y'
3234 or nvl(completed_flag, 'N') = 'Y'
3235 or nvl(cancelled_flag, 'N') = 'Y'
3236 or nvl(rejected_flag, 'N') = 'Y')
3237 and task_status_id = l_ins_task_status_id;
3238 EXCEPTION WHEN others THEN
3239 begin
3240 select 'SLEEP' into l_ins_task_status
3241 from jtf_task_statuses_vl
3242 where nvl(on_hold_flag, 'N') = 'Y'
3243 and task_status_id = l_ins_task_status_id;
3244 EXCEPTION WHEN others THEN
3245 l_ins_task_status := 'OPEN';
3246 end;
3247 end;
3248
3252 insert into p_temp values('3'||l_ins_planned_end_date||' '|| l_ins_actual_ins_start_date||' '|| l_ins_actual_ins_end_date||' '|| l_ins_scheduled_start_date||' '|| l_ins_scheduled_end_date);
3249 /**********
3250 insert into p_temp values('1'||l_ins_task_id||' '|| l_ins_task_number||' '|| l_ins_customer_id||' '|| l_ins_owner_id||' '|| l_ins_owner_type_code);
3251 insert into p_temp values('2'||l_ins_source_object_id||' '|| l_ins_source_object_type_code||' '|| l_ins_due_date||' '|| l_ins_planned_start_date);
3253 insert into p_temp values('4'||l_ins_task_type_id||' '|| l_ins_task_status_id||' '|| l_ins_task_name||' '|| l_ins_importance_level||' '|| l_ins_priority_code);
3254 ***********/
3255
3256 -- Get the most recent grp member assignee
3257
3258 begin
3259 select c.resource_id, c.resource_type_code
3260 into l_assignee_id, l_assignee_type
3261 from jtf_task_assignments c
3262 where c.task_id = l_task_id
3263 and c.assignee_role = 'ASSIGNEE'
3264 and c.resource_type_code not in ('RS_TEAM', 'RS_GROUP')
3265 and c.resource_id in ( select resource_id
3266 from jtf_rs_group_members
3267 where group_id = l_owner_id
3268 and nvl(delete_flag,'N') <> 'Y')
3269 --and c.task_assignment_id <> jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id
3270 and c.last_update_date = (select max(a.last_update_date)
3271 from jtf_task_assignments a,jtf_task_statuses_vl b
3272 where a.task_id = l_task_id
3273 and a.assignee_role = 'ASSIGNEE'
3274 and a.task_assignment_id <> jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id
3275 and a.assignment_status_id = b.task_status_id
3276 and a.resource_type_code not in ('RS_TEAM', 'RS_GROUP')
3277 and a.resource_id in ( select resource_id
3278 from jtf_rs_group_members
3279 where group_id = l_owner_id
3280 and nvl(delete_flag,'N') <> 'Y')
3281 and (nvl(b.closed_flag, 'N') = 'N'
3282 and nvl(b.completed_flag, 'N') = 'N'
3283 and nvl(b.cancelled_flag, 'N') = 'N'
3284 and nvl(b.rejected_flag, 'N') = 'N'
3285 and b.task_status_id = c.assignment_status_id)
3286 )
3287
3288 and rownum < 2;
3289 exception when others then
3290 --l_sql_err := SQLERRM;
3291 --l_sql_code := SQLCODE;
3292 -- insert into p_temp values ('excep: '||l_SQL_ERR|| l_SQL_CODE);
3293 l_assignee_id := null;
3294 l_assignee_type := null;
3295 end;
3296
3297 l_audit_trail_rec := SYSTEM.WR_AUDIT_TRAIL_NST();
3298
3299 l_audit_trail_rec.extend;
3300
3301 l_audit_trail_rec(l_audit_trail_rec.LAST):= SYSTEM.WR_AUDIT_TRAIL_OBJ
3302 ('WORKITEM_UPDATE',
3303 'UPDATE_WR_ITEM',
3304 690,
3305 'IEU_TASKS_USERHOOKS.DELETE_TASK_ASSIGN_UWQM_PRE',
3306 l_workitem_comment_code1,
3307 l_workitem_comment_code2,
3308 null,
3309 null,
3310 null);
3311
3312 -- insert into p_temp(msg) values ('passign due date as: '||l_ins_due_date);
3313
3314 --insert into p_temp values ('entity: '||l_entity);
3315 if (l_entity = 'TASK')
3316 then
3317 --insert into p_temp values ('create wr item');
3318 IEU_WR_PUB.CREATE_WR_ITEM(
3319 p_api_version => 1.0,
3320 p_init_msg_list => FND_API.G_TRUE,
3321 p_commit => FND_API.G_true,
3322 p_workitem_obj_code => 'TASK',
3323 p_workitem_pk_id => l_ins_task_id,
3324 p_work_item_number => l_ins_task_number,
3325 p_title => l_ins_task_name,
3326 p_party_id => l_ins_customer_id,
3327 p_priority_code => l_ins_priority_code,
3328 p_due_date => l_ins_due_date,
3329 p_owner_id => l_ins_owner_id,
3330 p_owner_type => l_ins_owner_type_code,
3331 p_assignee_id => l_assignee_id,
3332 p_assignee_type => l_assignee_type,
3333 p_source_object_id => l_ins_source_object_id,
3334 p_source_object_type_code => l_ins_source_object_type_code,
3335 p_application_id => 690,
3336 p_ieu_enum_type_uuid => 'TASKS',
3337 p_work_item_status => l_ins_task_status,
3338 p_user_id => FND_GLOBAL.USER_ID,
3339 p_login_id => FND_GLOBAL.LOGIN_ID,
3340 p_audit_trail_rec => l_audit_trail_rec,
3341 x_work_item_id => l_ins_WORK_ITEM_ID,
3342 x_msg_count => l_msg_count,
3343 x_msg_data => l_MSG_DATA,
3344 x_return_status => l_RETURN_STATUS);
3345
3346 x_return_status := l_return_status;
3347 else
3348 x_return_status := l_return_status;
3349 end if;
3350
3351 end if; /* create wr item */
3352
3353 else
3354
3355
3356 begin
3357 select resource_id, resource_type_code
3358 into l_assignee_id, l_assignee_type
3359 from jtf_task_assignments c
3360 where c.task_id = l_task_id
3361 and task_assignment_id = jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id;
3362 EXCEPTION WHEN others THEN
3363 l_assignee_id := null;
3364 l_assignee_type := null;
3365 end ;
3366
3367 begin
3368 select task_type_id, entity into l_task_type_id, l_entity
3369 from jtf_tasks_b
3370 where task_id = l_task_id;
3371 exception when others then l_task_type_id := null;
3372 end;
3373
3374
3375 begin
3376 l_workitem_obj_code := 'TASK';
3377 select owner_id, owner_type, source_object_id, source_object_type_code
3378 into l_owner_id, l_owner_type, l_source_object_id, l_source_object_type_code
3379 from ieu_uwqm_items
3383 EXCEPTION WHEN others THEN null;
3380 where workitem_pk_id = l_task_id
3381 -- and workitem_obj_code = 'TASK';
3382 and workitem_obj_code = l_workitem_obj_code;
3384 end ;
3385
3386 if (l_dist_from = 'GROUP_OWNED') and
3387 (l_dist_to = 'INDIVIDUAL_ASSIGNED')
3388 then
3389 if l_owner_type = 'RS_GROUP' then
3390 begin
3391 l_workitem_obj_code := 'TASK';
3392 select assignee_id, assignee_type_actual
3393 into l_uwq_assignee_id, l_uwq_assignee_type
3394 from ieu_uwqm_items
3395 where workitem_pk_id = l_task_id
3396 -- and workitem_obj_code = 'TASK';
3397 and workitem_obj_code = l_workitem_obj_code;
3398 exception when others then
3399 l_assignee_id := null;
3400 l_assignee_type := null;
3401 end;
3402 if ((nvl(l_uwq_assignee_id, '-1') = l_assignee_id) and (nvl(l_uwq_assignee_type,'X') = l_assignee_type))
3403 then
3404 l_other_asg_deleted_flag := 'N';
3405 begin
3406 select c.resource_id, c.resource_type_code
3407 into l_assignee_id, l_assignee_type
3408 from jtf_task_assignments c
3409 where c.task_id = l_task_id
3410 and c.assignee_role = 'ASSIGNEE'
3411 and c.resource_type_code not in ('RS_TEAM', 'RS_GROUP')
3412 and c.resource_id in ( select resource_id
3413 from jtf_rs_group_members
3414 where group_id = l_owner_id
3415 and nvl(delete_flag,'N') <> 'Y')
3416 and c.last_update_date = (select max(a.last_update_date)
3417 from jtf_task_assignments a,jtf_task_statuses_vl b
3418 where a.task_id = l_task_id
3419 and a.assignee_role = 'ASSIGNEE'
3420 and a.assignment_status_id = b.task_status_id
3421 and a.resource_type_code not in ('RS_TEAM', 'RS_GROUP')
3422 and a.resource_id in ( select resource_id
3423 from jtf_rs_group_members
3424 where group_id = l_owner_id
3425 and nvl(delete_flag,'N') <> 'Y')
3426 and (nvl(b.closed_flag, 'N') = 'N'
3427 and nvl(b.completed_flag, 'N') = 'N'
3428 and nvl(b.cancelled_flag, 'N') = 'N'
3429 and nvl(b.rejected_flag, 'N') = 'N'
3430 and b.task_status_id = c.assignment_status_id)
3431 and task_assignment_id <> jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id)
3432 and rownum < 2;
3433 exception when others then
3434 l_assignee_id := null;
3435 l_assignee_type := null;
3436 end;
3437 else
3438 l_other_asg_deleted_flag := 'Y';
3439 end if;
3440
3441 else
3442 l_assignee_id := null;
3443 l_assignee_type := null;
3444 end if;
3445 end if;
3446
3447 if l_assignee_id is not null then
3448 l_workitem_comment_code2 := 'GRP_MAX_ASSGN';
3449 end if;
3450
3451 l_audit_trail_rec := SYSTEM.WR_AUDIT_TRAIL_NST();
3452
3453 l_audit_trail_rec.extend;
3454
3455 l_audit_trail_rec(l_audit_trail_rec.LAST):= SYSTEM.WR_AUDIT_TRAIL_OBJ
3456 ('WORKITEM_UPDATE',
3457 'UPDATE_WR_ITEM',
3458 690,
3459 'IEU_TASKS_USERHOOKS.DELETE_TASK_ASSIGN_UWQM_PRE',
3460 l_workitem_comment_code1,
3461 l_workitem_comment_code2,
3462 null,
3463 null,
3464 null);
3465
3466 -- insert into p_temp(msg) values ('passign due date as: '||l_ins_due_date|| ' other asg delete flag: '||l_other_asg_deleted_flag);
3467
3468 if (l_entity = 'TASK') and (nvl(l_other_asg_deleted_flag, 'N') = 'N')then
3469 IEU_WR_PUB.UPDATE_WR_ITEM(
3470 p_api_version => 1.0,
3471 p_init_msg_list => FND_API.G_TRUE,
3472 p_commit => FND_API.G_FALSE,
3473 p_workitem_obj_code => 'TASK',
3474 p_workitem_pk_id => l_task_id,
3475 p_title => FND_API.G_MISS_CHAR,
3476 p_party_id => FND_API.G_MISS_NUM,
3477 p_priority_code => FND_API.G_MISS_CHAR,
3478 p_due_date => l_ins_due_date,
3479 p_owner_id => FND_API.G_MISS_NUM,
3480 p_owner_type => FND_API.G_MISS_CHAR,
3481 p_assignee_id => l_assignee_id,
3482 p_assignee_type => l_assignee_type,
3483 p_source_object_id => FND_API.G_MISS_NUM,
3484 p_source_object_type_code => FND_API.G_MISS_CHAR,
3485 p_application_id => 690,
3486 p_user_id => FND_GLOBAL.USER_ID,
3487 p_login_id => FND_GLOBAL.LOGIN_ID,
3488 p_work_item_status => FND_API.G_MISS_CHAR,
3489 p_audit_trail_rec => l_audit_trail_rec,
3490 x_msg_data => L_MSG_DATA,
3491 x_msg_count => l_msg_count,
3492 x_return_status => L_RETURN_STATUS);
3493
3494 x_return_status := l_return_status;
3495 elsif (l_entity <> 'TASK') or
3496 (nvl(l_other_asg_deleted_flag, 'N') = 'Y') then
3497 x_return_status := fnd_api.g_ret_sts_success;
3498 end if;
3499
3500 end if; /* l_wi_exists */
3501
3502
3503 EXCEPTION WHEN OTHERS THEN
3504 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3505
3506 END;
3507
3508 end if; /* Task Rules Func */
3509 elsif l_activation_status = 'N' then
3510 x_return_status := fnd_api.g_ret_sts_success;
3511 end if;
3512 end delete_task_assign_uwqm_pre;
3513
3514 END IEU_TASKS_USERHOOKS;