1 PACKAGE BODY CS_UWQ_DISTRIBUTE_ITEMS AS
2 /* $Header: csvsrwdb.pls 120.7.12020000.3 2013/04/02 18:38:55 rgandhi ship $ */
3
4 /*****************************************
5 -- This procdeure is going to be called by UWQ's distribute_function()
6 -- to update owner of Service Request in CS when the WI is assigned
7 -- to an individual owner
8 -- Modification History:
9 --
10 -- Date Name Desc
11 -- -------- ---------- --------------------------------------
12 -- 05/25/2004 RMANABAT Fix for bug 3612904. Passed resp_id and
13 -- rep_appL_id to update_servicerequest() api
17 PROCEDURE Distribute_ServiceRequests
14 -- for security validation.
15 *******************************************/
16
18 (P_RESOURCE_ID IN NUMBER,
19 P_LANGUAGE IN VARCHAR2,
20 P_SOURCE_LANG IN VARCHAR2,
21 P_NUM_OF_ITEMS IN NUMBER,
22 P_DIST_BUS_RULES IN SYSTEM.DIST_BUS_RULES_NST,
23 P_WS_INPUT_DATA IN OUT NOCOPY SYSTEM.WR_ITEM_DATA_NST,
24 X_MSG_COUNT OUT NOCOPY NUMBER,
25 X_MSG_DATA OUT NOCOPY VARCHAR2,
26 X_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
27
28
29 l_distributed_from varchar2(100);
30 l_distributed_to varchar2(100);
31 l_grp_owner varchar2(100);
32 l_individual_owner varchar2(100);
33
34 l_notes CS_SERVICEREQUEST_PVT.notes_table;
35 l_contacts CS_SERVICEREQUEST_PVT.contacts_table;
36 out_interaction_id NUMBER;
37 out_wf_process_id NUMBER;
38 l_return_status VARCHAR2(1);
39 l_msg_count NUMBER;
40 l_msg_data VARCHAR2(2000);
41
42 l_incident_number VARCHAR2(64);
43 l_object_version_number NUMBER;
44 l_incident_owner_id NUMBER;
45 l_assignee_id NUMBER;
46 l_resource_type VARCHAR2(33);
47
48 CURSOR sel_servereq_csr(l_request_id IN NUMBER) IS
49 SELECT incident_number,
50 object_version_number,
51 incident_owner_id
52 FROM CS_INCIDENTS_B_SEC /* Changed for 16585596 */
53 WHERE incident_id = l_request_id;
54
55 CURSOR sel_resource_type_csr IS
56 SELECT 'RS_' || category
57 FROM jtf_rs_resource_extns
58 WHERE RESOURCE_ID = P_RESOURCE_ID;
59
60 CURSOR sel_wi_owner_csr(l_request_id IN NUMBER) IS
61 SELECT assignee_id
62 FROM ieu_uwqm_items
63 WHERE workitem_obj_code = 'SR'
64 AND workitem_pk_id = l_request_id;
65
66 l_msg_index_OUT NUMBER;
67
68
69 BEGIN
70
71
72 -- Establish savepoint
73 SAVEPOINT Distribute_SR;
74 --dbms_output.put_line('Starting Distribute SR ');
75
76 --insert into rm_tmp values(null,null,'start of SR distribute func',rm_tmp_seq.nextval);
77 --commit;
78
79 -- Loop thru the Business rules per Work Source
80 For i in P_DIST_BUS_RULES.first .. P_DIST_BUS_RULES.last
81 Loop
82
83 l_distributed_from := P_DIST_BUS_RULES(i).DISTRIBUTE_FROM;
84 l_distributed_to := P_DIST_BUS_RULES(i).DISTRIBUTE_TO;
85
86 -- For each Work Source,Get the Details of the Work Item to be distributed and the
87 -- Distribution Rules. Try to Distribute the Work Item.
88 IF (P_DIST_BUS_RULES(i).work_source = 'SR') THEN
89
90 --dbms_output.put_line('Distribute SR, P_DIST_BUS_RULES.work_source=SR ');
91
92 -- Loop thru Work Item Details
93 For j in P_WS_INPUT_DATA.first .. P_WS_INPUT_DATA.last
94 LOOP
95
96 IF (P_WS_INPUT_DATA(j).Work_source= 'SR' AND
97 l_distributed_from = 'GROUP_OWNED') THEN
98 -- Commented out since we don't have any special logic regarding
99 -- distribute_to
100 -- l_distributed_to = 'INDIVIDUAL_ASSIGNED') THEN
101
102 --dbms_output.put_line('Distribute SR, P_WS_INPUT_DATA.work_source=SR ');
103
104
105 -- Obtain Resource Type of the resource
106
107 OPEN sel_resource_type_csr;
108 FETCH sel_resource_type_csr INTO l_resource_type;
109 CLOSE sel_resource_type_csr;
110
111
112 OPEN sel_servereq_csr(P_WS_INPUT_DATA(j).WORKITEM_PK_ID);
113 FETCH sel_servereq_csr
114 INTO l_incident_number, l_object_version_number,l_incident_owner_id;
115
116 IF sel_servereq_csr%rowcount = 0 then /* Added if block for 16585596 */
117 ROLLBACK TO Distribute_SR;
118 P_WS_INPUT_DATA(j).DISTRIBUTED := 'FALSE';
119 CLOSE sel_servereq_csr;
120 continue;
121 END IF;
122
123 CLOSE sel_servereq_csr;
124
125 --dbms_output.put_line('Distribute SR, Calling Update SR_UWQ ');
126
127
128 CS_ServiceRequest_PVT.Update_Owner(
129 p_api_version => 2.0,
130 p_init_msg_list => fnd_api.g_false,
131 p_commit => fnd_api.g_false,
132 p_validation_level => fnd_api.g_valid_level_full,
133 x_return_status => l_return_status,
134 x_msg_count => l_msg_count,
135 x_msg_data => l_msg_data,
136 p_request_id => P_WS_INPUT_DATA(j).WORKITEM_PK_ID,
137 p_object_version_number => l_object_version_number,
138 --p_resp_id => NULL,
139 --p_resp_appl_id => P_WS_INPUT_DATA(j).APPLICATION_ID,
140 p_resp_id => fnd_global.resp_id,
141 p_resp_appl_id => fnd_global.resp_appl_id,
142 p_owner_id => P_RESOURCE_ID,
143 p_owner_group_id => P_WS_INPUT_DATA(j).OWNER_ID,
144 p_resource_type => l_resource_type,
145 p_last_updated_by => FND_GLOBAL.USER_ID,
146 p_last_update_login => NULL,
147 p_last_update_date => sysdate,
148 p_audit_comments => NULL,
149 p_called_by_workflow => fnd_api.g_false,
150 p_workflow_process_id => NULL,
151 p_comments => NULL,
152 p_public_comment_flag => fnd_api.g_false,
153 p_parent_interaction_id => NULL,
154 x_interaction_id => out_interaction_id);
155
156 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
157
158 -- An error return status from Update_WorkItem() API
159 -- is ignored by Update_Owner(), so we have to test that
160 -- the owner update was successful for the SR and the
161 -- Work Item.
162
163 OPEN sel_servereq_csr(P_WS_INPUT_DATA(j).WORKITEM_PK_ID);
167
164 FETCH sel_servereq_csr
165 INTO l_incident_number, l_object_version_number,l_incident_owner_id;
166 CLOSE sel_servereq_csr;
168 OPEN sel_wi_owner_csr(P_WS_INPUT_DATA(j).WORKITEM_PK_ID);
169 FETCH sel_wi_owner_csr INTO l_assignee_id;
170 CLOSE sel_wi_owner_csr;
171
172 IF (l_incident_owner_id = P_RESOURCE_ID AND
173 l_assignee_id = P_RESOURCE_ID) THEN
174
175 P_WS_INPUT_DATA(j).DISTRIBUTED := 'TRUE';
176 P_WS_INPUT_DATA(j).ASSIGNEE_ID := P_RESOURCE_ID;
177 P_WS_INPUT_DATA(j).ASSIGNEE_TYPE := 'RS_INDIVIDUAL';
178 P_WS_INPUT_DATA(j).ITEM_INCLUDED_BY_APP := 'FALSE';
179
180 COMMIT;
181
182 ELSE
183
184 ROLLBACK TO Distribute_SR;
185 P_WS_INPUT_DATA(j).DISTRIBUTED := 'FALSE';
186
187 END IF;
188
189 ELSE
190
191 -- Rollback to savepoint
192 ROLLBACK TO Distribute_SR;
193
194 P_WS_INPUT_DATA(j).DISTRIBUTED := 'FALSE';
195
196 END IF;
197
198 END IF;
199
200 END LOOP;
201
202 END IF;
203
204 END LOOP;
205
206 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
207
208 END Distribute_ServiceRequests;
209
210
211 PROCEDURE SYNC_SR_TASKS(
212 P_TASKS_DATA IN SYSTEM.WR_TASKS_DATA_NST,
213 P_DEF_WR_DATA IN SYSTEM.DEF_WR_DATA_NST,
214 X_MSG_COUNT OUT NOCOPY NUMBER,
215 X_MSG_DATA OUT NOCOPY VARCHAR2,
216 X_RETURN_STATUS OUT NOCOPY VARCHAR2) AS
217
218 l_work_item_id NUMBER;
219 l_msg_count NUMBER;
220 l_assignee_id NUMBER := NULL;
221 l_owner_id NUMBER := NULL;
222 l_orig_grpowner NUMBER := NULL;
223 l_task_status_id NUMBER := NULL;
224 l_group_member_id NUMBER;
225 l_msg_data VARCHAR2(2000);
226 l_return_status VARCHAR2(1);
227 l_assignee_type VARCHAR2(30) := NULL;
228 l_owner_type VARCHAR2(30) := NULL;
229 l_priority_code VARCHAR2(30) := 'LOW';
230 l_task_status VARCHAR2(30);
231 l_wi_flag VARCHAR2(1);
232 l_due_date DATE;
233
234 BEGIN
235
236 --dbms_output.put_line('In SYNC SR Tasks, BEGIN ');
237 l_return_status := FND_API.G_RET_STS_SUCCESS;
238 --dbms_output.put_line('In SYNC SR Tasks, BEGIN, X_RETURN_STATUS='|| X_RETURN_STATUS );
239
240 -- Get the Priority Code, WorkItem Status and Due Date
241 FOR i in P_DEF_WR_DATA.FIRST..P_DEF_WR_DATA.LAST
242 LOOP --LP1
243 l_due_date := p_def_wr_data(i).due_date;
244 l_task_status := p_def_wr_data(i).work_item_status;
245 l_priority_code := p_def_wr_data(i).priority_code;
246 l_orig_grpowner := p_def_wr_data(i).original_grp_owner;
247 END LOOP; --LP1
248
249 -- Loop thru Task Details
250 FOR j in P_TASKS_DATA.first.. P_TASKS_DATA.last
251 LOOP --LP2
252 -- Work Items should be synched up only during Create/Update/Delete task
253 -- Assignee Id and type are not considered here
254 --dbms_output.put_line('In SYNC SR Tasks, proc_type ='||p_tasks_data(j).proc_type);
255
256 IF (p_tasks_data(j).proc_type = 'CREATE_TASK') OR --IF1
257 (p_tasks_data(j).proc_type = 'UPDATE_TASK') OR
258 (p_tasks_data(j).proc_type = 'DELETE_TASK') THEN
259 --dbms_output.put_line('In SYNC SR Tasks, task_id ='||p_tasks_data(j).task_id);
260
261 IF (p_tasks_data(j).owner_type_code = 'RS_GROUP') THEN --IF2
262 l_owner_id := p_tasks_data(j).owner_id;
263 l_owner_type := p_tasks_data(j).owner_type_code;
264 ELSIF ( (p_tasks_data(j).owner_type_code <> 'RS_GROUP') AND --IF2
265 (p_tasks_data(j).owner_type_code <> 'RS_TEAM') ) THEN
266 l_assignee_id := p_tasks_data(j).owner_id;
267 l_assignee_type := p_tasks_data(j).owner_type_code;
268 -- If the Tasks Owner is an individual,
269 -- Select the previous Group Owner
270 -- Check if this Individual is a member of the Previous Group
271 -- Set the UWQ Owner, assignee based on the these validation
272 IF l_orig_grpowner IS NOT NULL THEN
273 l_owner_id := l_orig_grpowner;
274 l_owner_type := 'RS_GROUP';
275 END IF;
276
277 END IF; --IF2
278
279 -- Query for work item if it already exists for this Task
280 BEGIN
281 SELECT 'Y'
282 INTO l_wi_flag
283 FROM ieu_uwqm_items
284 WHERE workitem_obj_code = 'TASK'
285 AND workitem_pk_id = p_tasks_data(j).task_id;
286 EXCEPTION
287 WHEN NO_DATA_FOUND THEN
288 l_wi_flag := 'N';
289 END;
290
291 IF (l_wi_flag = 'N') THEN --IF3
292 --dbms_output.put_line('Creating Work Item for Task '||p_tasks_data(j).task_number);
293 IEU_WR_PUB.CREATE_WR_ITEM(
294 p_api_version => 1.0,
295 p_init_msg_list => FND_API.G_TRUE,
296 p_commit => FND_API.G_FALSE,
297 p_workitem_obj_code => 'TASK',
298 p_workitem_pk_id => p_tasks_data(j).task_id,
299 p_work_item_number => p_tasks_data(j).task_number,
300 p_title => p_tasks_data(j).task_name,
301 p_party_id => p_tasks_data(j).customer_id,
302 p_priority_code => l_priority_code,
303 p_due_date => l_due_date,
304 p_owner_id => l_owner_id,
305 p_owner_type => l_owner_type,
309 p_source_object_type_code => p_tasks_data(j).source_object_type_code,
306 p_assignee_id => l_assignee_id,
307 p_assignee_type => l_assignee_type,
308 p_source_object_id => p_tasks_data(j).source_object_id,
310 p_application_id => 170,
311 p_ieu_enum_type_uuid => 'TASKS', -- 'IEU_MY_TASKS_OWN',
312 p_work_item_status => l_task_status,
313 p_user_id => FND_GLOBAL.USER_ID,
314 p_login_id => FND_GLOBAL.LOGIN_ID,
315 x_work_item_id => l_work_item_id,
316 x_msg_count => l_msg_count,
317 x_msg_data => l_msg_data,
318 x_return_status => l_return_status);
319 ELSE --IF3
320 --dbms_output.put_line('Updating Work Item for Task '||p_tasks_data(j).task_number);
321 IEU_WR_PUB.UPDATE_WR_ITEM(
322 p_api_version => 1.0,
323 p_init_msg_list => FND_API.G_TRUE,
324 p_commit => FND_API.G_FALSE,
325 p_workitem_obj_code => 'TASK',
326 p_workitem_pk_id => p_tasks_data(j).task_id,
327 p_title => p_tasks_data(j).task_name,
328 p_party_id => p_tasks_data(j).customer_id,
329 p_priority_code => l_priority_code,
330 p_due_date => l_due_date,
331 p_owner_id => l_owner_id,
332 p_owner_type => l_owner_type,
333 p_assignee_id => l_assignee_id,
334 p_assignee_type => l_assignee_type,
335 p_source_object_id => p_tasks_data(j).source_object_id,
336 p_source_object_type_code => p_tasks_data(j).source_object_type_code,
337 p_application_id => 170,
338 p_work_item_status => l_task_status,
339 p_user_id => FND_GLOBAL.USER_ID,
340 p_login_id => FND_GLOBAL.LOGIN_ID,
341 x_msg_count => l_msg_count,
342 x_msg_data => l_msg_data,
343 x_return_status => l_return_status);
344 --dbms_output.put_line('Update l_return_status='||l_return_status);
345 END IF; --IF3
346 END IF; --IF1
347 END LOOP; --LP2
348
349 x_return_status := l_return_status;
350 --dbms_output.put_line('SYNC_SR_TASKS x_return_status='||x_return_status);
351
352 END SYNC_SR_TASKS;
353
354
355 PROCEDURE SYNC_SR_TASKS(
356 P_PROCESSING_SET_ID IN NUMBER DEFAULT NULL,
357 X_MSG_COUNT OUT NOCOPY NUMBER,
358 X_MSG_DATA OUT NOCOPY VARCHAR2,
359 X_RETURN_STATUS OUT NOCOPY VARCHAR2) AS
360
361
362 l_msg_count NUMBER;
363 l_msg_data VARCHAR2(4000);
364 l_return_status VARCHAR2(1);
365
366 BEGIN
367 --dbms_output.put_line('In SYNC SR Tasks, BEGIN ');
368
369 l_return_status := FND_API.G_RET_STS_SUCCESS;
370 --dbms_output.put_line('In SYNC SR Tasks, BEGIN, X_RETURN_STATUS='|| l_RETURN_STATUS );
371
372
373 BEGIN
374 UPDATE IEU_UWQM_ITEMS_GTT A
375 SET A.APPLICATION_ID = 170
376 WHERE A.PROCESSING_SET_ID = P_PROCESSING_SET_ID;
377 -- EXCEPTION
378 -- WHEN others THEN null;
379 END;
380
381
382 -- Set the Owner/Assignee in UWQ based on the Owner Type Code
383 -- Required Group Validation can be done here
384
385 -- If the Tasks Owner is an individual,
386 -- Select the previous Group Owner
387 -- Check if this Individual is a member of the Previous Group
388 -- Set the UWQ Owner, assignee based on the these validation
389
390 -- IF individual owner is NOT member of group (to be obtained from UWQ
391 -- owner info with owner type ='GROUP') , nullify group owner info in UWQ
392 -- and set assignee to the individual task owner .
393 -- Else if individual IS member of group, just set assignee to the
394 -- individual task owner
395
396 BEGIN
397 UPDATE IEU_UWQM_ITEMS_GTT A
398 SET A.ASSIGNEE_ID = A.OWNER_ID
399 , A.ASSIGNEE_TYPE_ACTUAL = A.OWNER_TYPE_ACTUAL
400 WHERE PROCESSING_SET_ID = P_PROCESSING_SET_ID
401 AND A.OWNER_TYPE_ACTUAL NOT IN ('RS_GROUP', 'RS_TEAM');
402
403 UPDATE IEU_UWQM_ITEMS_GTT A
404 SET A.OWNER_ID = ''
405 , A.OWNER_TYPE_ACTUAL = ''
406 WHERE PROCESSING_SET_ID = P_PROCESSING_SET_ID
407 AND A.OWNER_TYPE_ACTUAL NOT IN ('RS_GROUP', 'RS_TEAM')
408 AND NOT EXISTS
409 ( SELECT 1
410 FROM JTF_RS_GROUP_MEMBERS B
411 , IEU_UWQM_ITEMS C
412 WHERE B.RESOURCE_ID = A.OWNER_ID
413 AND B.GROUP_ID = C.OWNER_ID
414 AND A.WORKITEM_OBJ_CODE = 'TASK'
415 AND C.WORKITEM_PK_ID = A.WORKITEM_PK_ID
416 AND C.WORKITEM_OBJ_CODE = A.WORKITEM_OBJ_CODE
417 AND C.OWNER_TYPE = 'RS_GROUP'
418 AND C.OWNER_ID IS NOT NULL);
419
420 -- EXCEPTION
421 -- WHEN others THEN null;
422 END;
423
424 IEU_WR_PUB.SYNC_WR_ITEMS (
425 p_api_version => 1.0,
426 p_init_msg_list => FND_API.G_TRUE,
427 p_commit => FND_API.G_FALSE,
428 p_processing_set_id => P_PROCESSING_SET_ID,
432 x_msg_data => l_msg_data,
429 p_user_id => FND_GLOBAL.USER_ID,
430 p_login_id => FND_GLOBAL.LOGIN_ID,
431 x_msg_count => l_msg_count,
433 x_return_status => l_return_status);
434
435
436 x_return_status := l_return_status;
437 --dbms_output.put_line('In SYNC SR Tasks, end, X_RETURN_STATUS='|| X_RETURN_STATUS );
438
439
440 END SYNC_SR_TASKS;
441
442
443
444
445 PROCEDURE DISTRIBUTE_SRTASKS
446 (P_RESOURCE_ID IN NUMBER,
447 P_LANGUAGE IN VARCHAR2,
448 P_SOURCE_LANG IN VARCHAR2,
449 P_NUM_OF_ITEMS IN NUMBER,
450 P_DIST_BUS_RULES IN SYSTEM.DIST_BUS_RULES_NST,
451 P_WS_INPUT_DATA IN OUT NOCOPY SYSTEM.WR_ITEM_DATA_NST,
452 X_MSG_COUNT OUT NOCOPY NUMBER,
453 X_MSG_DATA OUT NOCOPY VARCHAR2,
454 X_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
455
456 l_distributed_from varchar2(100);
457 l_distributed_to varchar2(100);
458 l_dist_st_based_on_parent_flag varchar2(1);
459
460 l_grp_owner number;
461 l_group_id varchar2(1);
462 l_task_distributed_to number;
463
464 l_source_object_id number;
465 l_source_object_code varchar2(30);
466 l_distribution_status number;
467
468 l_msg_count number;
469 l_msg_data varchar2(2000);
470 l_return_status varchar2(1);
471 l_task_assignment_id number;
472 l_assignment_id number;
473 l_object_version_number number;
474 l_status_id NUMBER;
475 l_sr_task_exist VARCHAR2(1);/* Changed for 16585596 */
476
477 CURSOR sel_tasks_csr(p_task_id IN NUMBER) IS
478 SELECT task_id,
479 object_version_number,
480 task_priority_id,
481 source_object_id /* Changed for 16585596 */
482 FROM jtf_tasks_b
483 WHERE task_id = p_task_id;
484
485 sel_tasks_rec sel_tasks_csr%ROWTYPE;
486
487 CURSOR sel_jtf_category_csr(p_resource_id IN NUMBER) IS
488 SELECT 'RS_' || CATEGORY
489 FROM jtf_rs_resource_extns
490 WHERE RESOURCE_ID = p_resource_id;
491
492 l_category VARCHAR2(35);
493
494 BEGIN
495
496 --insert into rm_tmp values (null,null,'start of DISTRIBUTE_SRTASKS ',rm_tmp_seq.nextval);
497 --commit;
498
499 -- initialize to success first
500 x_return_status := 'I';
501
502 if p_dist_bus_rules.count <= 0 then
503 x_return_status := 'E';
504 end if;
505 if p_ws_input_data.count <= 0 then
506 x_return_status := 'E';
507 end if;
508
509 -- Loop thru the Business rules per Work Source
510
511 For i in P_DIST_BUS_RULES.first.. P_DIST_BUS_RULES.last
512 Loop
513
514 --insert into rm_tmp values (null,null,'DISTRIBUTE_SRTASKS: Looping P_DIST_BUS_RULES ',rm_tmp_seq.nextval);
515 --commit;
516 --insert into temp_f values('in the first loop');commit;
517
518 l_distributed_from := P_DIST_BUS_RULES(i).DISTRIBUTE_FROM;
519 l_distributed_to := P_DIST_BUS_RULES(i).DISTRIBUTE_TO;
520 l_dist_st_based_on_parent_flag := P_DIST_BUS_RULES(i).DIST_ST_BASED_ON_PARENT_FLAG;
521
522 -- For each Work Source, Get the Details of the Work Item to be distributed and the
523 -- Distribution Rules. Try to Distribute the Work Item.
524
525 if (P_DIST_BUS_RULES(i).work_source = 'SR_TASKS')
526 then
527
528 -- Loop thru Work Item Details
529 For j in p_WS_INPUT_DATA .first.. p_WS_INPUT_DATA.last
530 loop
531
532 --insert into rm_tmp values (null,null,'DISTRIBUTE_SRTASKS: Looping p_WS_INPUT_DATA ',rm_tmp_seq.nextval);
533 --commit;
534 IF (p_WS_INPUT_DATA(j).Work_source = 'SR_TASKS') THEN
535
536 if (l_distributed_from = 'GROUP_OWNED') THEN
537 l_grp_owner := p_WS_INPUT_DATA(j).OWNER_ID;
538 else
539 l_grp_owner := p_WS_INPUT_DATA(j).ASSIGNEE_ID;
540 end if;
541
542 l_task_distributed_to := p_resource_id;
543
544 begin
545 select 'X' into l_group_id
546 from jtf_rs_group_members
547 where resource_id = p_resource_id
548 and group_id = l_grp_owner
549 and nvl(delete_flag, 'N') = 'N'
550 and rownum < 2;
551 exception when no_data_found then
552 x_return_status := 'E';
553 end;
554
555 --insert into rm_tmp values (null,null,'DISTRIBUTE_SRTASKS: After jtf_rs_group_members,x_return_status='||x_return_status,rm_tmp_seq.nextval);
556 --commit;
557 if l_group_id = 'X' then
558
559 if l_dist_st_based_on_parent_flag = 'Y' then
560
561 -- Code changes required. This will be required only for 'Association' work
562 -- source like SR-TASK. The object code should be selected from
563 -- ieu_uwqm_work_sources_b for parent work source
564
565 -- if p_ws_input_data(j).source_object_type_code = 'SR' then
566 l_source_object_code := p_ws_input_data(j).source_object_type_code;
567 l_source_object_id := p_ws_input_data(j).source_object_id;
568
569 --insert into rm_tmp values (null,null,'DISTRIBUTE_SRTASKS: source_object_type_code=SR,l_source_object_id'||l_source_object_id||',l_source_object_code='||l_source_object_code,rm_tmp_seq.nextval);
570 --commit;
571 begin
572 select distribution_status_id, status_id
573 into l_distribution_status, l_status_id
574 from ieu_uwqm_items
575 where workitem_pk_id = l_source_object_id
579 end;
576 and workitem_obj_code = l_source_object_code;
577 exception when no_data_found then
578 l_distribution_status := 0;
580 -- end if;
581
582 end if;
583
584 -- distribution_status 3 is Distributed, Status id 3 is Close.
585 if ( ((l_distribution_status = 3 OR l_status_id = 3)
586 and l_dist_st_based_on_parent_flag = 'Y')
587 OR (l_dist_st_based_on_parent_flag is null) ) then
588
589 --insert into rm_tmp values (null,null,'DISTRIBUTE_SRTASKS: l_distribution_status=3',rm_tmp_seq.nextval);
590 --commit;
591
592 -- Update the SR Task Owner
593 -- The update to the SR Task work item will be handled by
594 -- rule function for the work source.
595
596 OPEN sel_tasks_csr(p_ws_input_data(j).workitem_pk_id);
597 FETCH sel_tasks_csr INTO sel_tasks_rec;
598
599 IF sel_tasks_csr%NOTFOUND THEN
600 x_return_status := 'E';
601 ELSE
602
603 -- obtain the owner_type_code by concatenating 'RS_' and the category
604 -- from jtf_rs_resource_extns
605 OPEN sel_jtf_category_csr(P_RESOURCE_ID);
606 FETCH sel_jtf_category_csr INTO l_category;
607 IF (sel_jtf_category_csr%NOTFOUND) THEN
608 l_category := 'RS_EMPLOYEE';
609 END IF;
610 CLOSE sel_jtf_category_csr;
611
612 BEGIN/* Changed for 16585596 */
613 SELECT 'X' into l_sr_task_exist
614 FROM cs_incidents_b_sec
615 where incident_id=sel_tasks_rec.source_object_id;
616
617 EXCEPTION
618 WHEN NO_DATA_FOUND THEN
619 P_WS_INPUT_DATA(j).DISTRIBUTED := 'FALSE';
620 continue;
621 END;
622
623
624 JTF_TASKS_PVT.UPDATE_TASK(
625 p_api_version => 1.0,
626 p_commit => FND_API.G_TRUE,
627 p_object_version_number => sel_tasks_rec.object_version_number,
628 p_task_id => sel_tasks_rec.task_id,
629 --p_owner_type_code => 'RS_INDIVIDUAL',
630 p_owner_type_code => l_category,
631 p_owner_id => P_RESOURCE_ID,
632 x_return_status => l_return_status,
633 x_msg_count => l_msg_count,
634 x_msg_data => l_msg_data);
635
636
637 x_return_status := l_return_status;
638 END IF;
639 CLOSE sel_tasks_csr;
640
641
642 end if; -- if (l_distribution_status = 3 and l_dist_st_based_on_parent_flag = 'Y')
643
644 end if; -- if l_group_id = 'X'
645
646 end if; -- IF (p_WS_INPUT_DATA(j).Work_source = 'SR_TASKS')
647
648 If x_return_status = 'S' THEN
649 P_WS_INPUT_DATA(j).DISTRIBUTED := 'TRUE';
650 P_WS_INPUT_DATA(j).ASSIGNEE_ID := p_resource_id;
651 P_WS_INPUT_DATA(j).ASSIGNEE_TYPE := 'RS_INDIVIDUAL';
652 P_WS_INPUT_DATA(j).ITEM_INCLUDED_BY_APP := 'FALSE';
653 Else
654 P_WS_INPUT_DATA(j).DISTRIBUTED := 'FALSE';
655 End if;
656
657 End loop; -- For j in p_WS_INPUT_DATA .first.. p_WS_INPUT_DATA.last
658
659 End if; -- if (P_DIST_BUS_RULES(i).work_source = 'SR_TASKS')
660
661 End loop; -- For i in P_DIST_BUS_RULES.first.. P_DIST_BUS_RULES.last
662
663
664 END DISTRIBUTE_SRTASKS;
665
666
667 END CS_UWQ_DISTRIBUTE_ITEMS;