[Home] [Help]
PACKAGE BODY: APPS.IEU_UWQ_GET_NEXT_WORK_PVT
Source
1 PACKAGE BODY IEU_UWQ_GET_NEXT_WORK_PVT AS
2 /* $Header: IEUVGNWB.pls 120.3 2006/03/08 22:40:21 msathyan noship $ */
3
4 -- SORT NOT DONE BY WORKITEM OBJECT CODE SO IT WAS REMOVED FROM THE ORDER BY CLAUSE (DEC-06-2001) - ckurian
5
6 resource_busy_nowait EXCEPTION;
7 PRAGMA EXCEPTION_INIT(resource_busy_nowait, -54);
8
9 l_dist_deliver_num_of_attempts NUMBER;
10
11 PROCEDURE GET_NEXT_WORKITEM
12 ( p_api_version IN NUMBER,
13 p_resource_id IN NUMBER,
14 p_user_id IN NUMBER,
15 x_uwqm_workitem_data OUT NOCOPY IEU_FRM_PVT.T_IEU_MEDIA_DATA,
16 x_msg_count OUT NOCOPY NUMBER,
17 x_msg_data OUT NOCOPY VARCHAR2,
18 x_return_status OUT NOCOPY VARCHAR2)
19 IS
20
21 BEGIN
22
23 null;
24
25 END GET_NEXT_WORKITEM;
26
27 PROCEDURE GET_WORKITEM_ACTION_FUNC_DATA
28 ( p_workitem_data IN IEU_UWQ_GET_NEXT_WORK_PVT.ieu_uwqm_item_data_rec,
29 x_workitem_action_data OUT NOCOPY IEU_FRM_PVT.T_IEU_MEDIA_DATA )
30 AS
31
32
33 BEGIN
34
35 null;
36
37 END GET_WORKITEM_ACTION_FUNC_DATA;
38
39
40 PROCEDURE GET_NEXT_WORK_ITEM_CONT
41 (p_release_api_version IN NUMBER,
42 p_next_work_api_version IN NUMBER,
43 p_workitem_obj_code IN VARCHAR2,
44 p_workitem_pk_id IN NUMBER,
45 p_work_item_id IN NUMBER,
46 p_user_id IN NUMBER,
47 p_resource_id IN NUMBER,
48 p_worklist_cont_mode IN VARCHAR2,
49 x_uwqm_workitem_data OUT NOCOPY IEU_FRM_PVT.T_IEU_MEDIA_DATA,
50 x_release_return_status OUT NOCOPY VARCHAR2,
51 x_release_msg_count OUT NOCOPY NUMBER,
52 x_release_msg_data OUT NOCOPY VARCHAR2,
53 x_nw_return_status OUT NOCOPY VARCHAR2,
54 x_nw_msg_count OUT NOCOPY NUMBER,
55 x_nw_msg_data OUT NOCOPY VARCHAR2)
56 IS
57
58 l_status_id NUMBER;
59 l_source_object_id NUMBER;
60 l_source_obj_type_code VARCHAR2(30);
61 L_SOURCEOBJ_WORKITEM_ID NUMBER;
62
63 BEGIN
64 null;
65 /*
66 IF ( (p_work_item_id is not null) OR
67 ( (p_workitem_pk_id is not null) AND (p_workitem_obj_code is not null) )
68 )
69 THEN
70
71 BEGIN
72
73 IF (p_work_item_id is not null)
74 THEN
75
76 SELECT status_id, source_object_id, source_object_type_code
77 INTO l_status_id, l_source_object_id, l_source_obj_type_code
78 FROM ieu_uwqm_items
79 WHERE work_item_id = p_work_item_id;
80
81 ELSE
82
83 SELECT status_id, source_object_id, source_object_type_code
84 INTO l_status_id, l_source_object_id, l_source_obj_type_code
85 FROM ieu_uwqm_items
86 WHERE workitem_pk_id = p_workitem_pk_id
87 AND workitem_obj_code = p_workitem_obj_code;
88
89 END IF;
90
91 EXCEPTION
92 WHEN NO_DATA_FOUND THEN
93 NULL;
94 END;
95
96 IF (l_status_id = 1)
97 THEN
98
99 -- Release Work Item
100
101 IEU_UWQM_PUB.RELEASE_UWQM_ITEM
102 ( p_api_version => p_release_api_version,
103 p_init_msg_list => 'T',
104 p_commit => 'T',
105 p_workitem_obj_code => p_workitem_obj_code,
106 p_workitem_pk_id => p_workitem_pk_id,
107 p_work_item_id => p_work_item_id,
108 p_user_id => p_user_id,
109 p_login_id => null,
110 x_msg_count => x_release_msg_count,
111 x_msg_data => x_release_msg_data,
112 x_return_status => x_release_return_status);
113
114
115 -- Release source_doc_id
116
117 /* SELECT WORK_ITEM_ID
118 INTO L_SOURCEOBJ_WORKITEM_ID
119 FROM IEU_UWQM_ITEMS
120 WHERE (WORKITEM_PK_ID, WORKITEM_OBJ_CODE) IN
121 (SELECT SOURCE_OBJECT_ID, SOURCE_OBJECT_TYPE_CODE
122 FROM IEU_UWQM_ITEMS
123 WHERE ( (WORK_ITEM_ID = P_WORK_ITEM_ID) OR
124 ( (WORKITEM_PK_ID = P_WORKITEM_PK_ID) AND (WORKITEM_OBJ_CODE = P_WORKITEM_OBJ_CODE) )
125 )
126 );
127 */
128 /*
129 SELECT WORK_ITEM_ID
130 INTO L_SOURCEOBJ_WORKITEM_ID
131 FROM IEU_UWQM_ITEMS
132 WHERE WORKITEM_PK_ID = l_source_object_id
133 AND WORKITEM_OBJ_CODE = l_source_obj_type_code;
134
135 IF (L_SOURCEOBJ_WORKITEM_ID is not null)
136 THEN
137
138 IEU_UWQM_PUB.RELEASE_UWQM_ITEM
139 ( p_api_version => p_release_api_version,
140 p_init_msg_list => 'T',
141 p_commit => 'T',
142 p_workitem_obj_code => null,
143 p_workitem_pk_id => null,
144 p_work_item_id => L_SOURCEOBJ_WORKITEM_ID,
145 p_user_id => p_user_id,
146 p_login_id => null,
147 x_msg_count => x_release_msg_count,
148 x_msg_data => x_release_msg_data,
149 x_return_status => x_release_return_status);
150
151 END IF;
152 */
153 /*
154 END IF;
155
156 END IF;
157
158 IF (p_worklist_cont_mode = 'TRUE')
159 THEN
160
161 IEU_UWQ_GET_NEXT_WORK_PVT.GET_NEXT_WORKITEM
162 ( p_api_version => p_next_work_api_version,
163 p_resource_id => p_resource_id,
164 p_user_id => p_user_id,
165 x_uwqm_workitem_data => x_uwqm_workitem_data,
166 x_msg_count => x_nw_msg_count,
167 x_msg_data => x_nw_msg_data,
168 x_return_status => x_nw_return_status);
169
170 END IF;
171 */
172 END GET_NEXT_WORK_ITEM_CONT;
173
174 PROCEDURE GET_WORKLIST_QUEUE
175 ( p_api_version IN NUMBER,
176 p_resource_id IN NUMBER,
177 p_user_id IN NUMBER,
178 p_no_of_recs IN NUMBER,
179 x_uwqm_workitem_data OUT NOCOPY IEU_UWQ_GET_NEXT_WORK_PVT.ieu_uwqm_item_data,
180 x_msg_count OUT NOCOPY NUMBER,
181 x_msg_data OUT NOCOPY VARCHAR2,
182 x_return_status OUT NOCOPY VARCHAR2)
183 IS
184
185 -- Used to Validate API version and name
186 l_api_version CONSTANT NUMBER := 1.0;
187 l_api_name CONSTANT VARCHAR2(30) := 'GET_WORKLIST_QUEUE';
188
189 -- Used for Time Analysis
190 t1 NUMBER; -- start time
191 t2 NUMBER; -- end time
192 l_time_spent NUMBER; -- time elapsed
193
194 -- Used to get Workitems based on Individual/Group Ownership/Asssignment
195
196 l_ind_own_work_item_id number(15);
197 l_ind_asg_work_item_id number(15);
198 l_grp_own_work_item_id number(15);
199 l_grp_asg_work_item_id number(15);
200
201 -- Used for sorting
202
203 l_work_item_id number(15);
204 l_priority_level number(1);
205 l_due_date varchar2(30);
206 l_workitem_obj_code varchar2(30);
207
208 l_work_item_id_1 number(15);
209 l_priority_level_1 number(1);
210 l_due_date_1 varchar2(30);
211 l_workitem_obj_code_1 varchar2(30);
212
213 -- Used for removing duplicate work item ids
214
215 l_work_item_id_last number(15);
216
217 -- Used to get the group count
218
219 l_grp_count NUMBER;
220 l_grp_id NUMBER;
221
222 res_code varchar2(30); -- Result Code
223
224 -- status_flag
225 l_open_status_id NUMBER := 0; -- Not In Use
226 l_lock_status_id NUMBER := 1; -- Status 'L' - Locked by UWQ
227
228 -- cursor to get Owned items
229 l_next_ind_own_work IEU_UWQ_GET_NEXT_WORK_PVT.l_get_work;
230 l_next_grp_own_work_1 IEU_UWQ_GET_NEXT_WORK_PVT.l_get_work;
231 l_next_grp_own_work_2 IEU_UWQ_GET_NEXT_WORK_PVT.l_get_work;
232
233 -- cursor to get Assigned items
234 l_next_ind_asg_work IEU_UWQ_GET_NEXT_WORK_PVT.l_get_work;
235 l_next_grp_asg_work_1 IEU_UWQ_GET_NEXT_WORK_PVT.l_get_work;
236 l_next_grp_asg_work_2 IEU_UWQ_GET_NEXT_WORK_PVT.l_get_work;
237
238
239 l_ind_own_no_data_found number := 0;
240 l_grp1_own_no_data_found number := 0;
241 l_grp2_own_no_data_found number := 0;
242
243 l_ind_asg_no_data_found number := 0;
244 l_grp1_asg_no_data_found number := 0;
245 l_grp2_asg_no_data_found number := 0;
246
247 l_ctr PLS_INTEGER := 0;
248 l_loop_ctr PLS_INTEGER := 0;
249
250 l_nw_item_list IEU_UWQ_GET_NEXT_WORK_PVT.IEU_UWQ_NEXTWORK_ITEM_LIST;
251 l_uwqm_item_data IEU_UWQ_GET_NEXT_WORK_PVT.ieu_uwqm_item_data; -- := null;
252
253 --- Values assigned for different work items ---
254
255 l_owner_type_ind varchar2(25);
256 l_owner_type_grp varchar2(25);
257
258 begin
259
260 l_owner_type_ind := 'RS_INDIVIDUAL';
261 l_owner_type_grp := 'RS_GROUP';
262 x_return_status := fnd_api.g_ret_sts_success;
263
264 IF NOT fnd_api.compatible_api_call (
265 l_api_version,
266 p_api_version,
267 l_api_name,
268 g_pkg_name
269 )
270 THEN
271 RAISE fnd_api.g_exc_unexpected_error;
272 END IF;
273
274 -- Initialize Message list
275
276 FND_MSG_PUB.INITIALIZE;
277
278 t1 := DBMS_UTILITY.GET_TIME;
279
280 ------ If no of records to be selected is not passed in the parameter
281 ------ then select all records
282
283 begin
284 -------------- Individual Owned Work Items ----------1
285 declare
286 cursor c1 is
287 select work_item_id, priority_level, to_char(due_date,'dd-mon-yyyy hh24:mi:ss') due_date,
288 workitem_obj_code
289 from ieu_uwqm_items
290 where owner_type = l_owner_type_ind
291 and owner_id = p_resource_id
292 and status_id in (0,1,2)
293 and reschedule_time <= sysdate
294 order by priority_level, due_date;
295 begin
296 for c1_rec in c1 loop
297 -- Update Work Item Rec
298 l_nw_item_list(l_ctr).work_item_id := c1_rec.work_item_id;
299 l_nw_item_list(l_ctr).priority_level := c1_rec.priority_level;
300 l_nw_item_list(l_ctr).due_date := c1_rec.due_date;
301 l_nw_item_list(l_ctr).workitem_obj_code := c1_rec.workitem_obj_code;
302 l_ctr := l_ctr + 1;
303 if nvl(p_no_of_recs, 0) <= 0 then
304 exit when c1%notfound;
305 elsif nvl(p_no_of_recs, 0) > 0 and c1%found then
306 if nvl(p_no_of_recs, 0) = c1%rowcount then
307 exit;
308 end if;
309 else
310 exit;
311 end if;
312 end loop;
313 end;
314
315 -------------- Group Owned Work Items ----------2
316 declare
317 cursor c1 is
318 select work_item_id, priority_level, to_char(due_date,'dd-mon-yyyy hh24:mi:ss') due_date,
319 workitem_obj_code
320 from ieu_uwqm_items
321 where owner_type = l_owner_type_grp
322 and owner_id in (select group_id from jtf_rs_group_members where resource_id = p_resource_id)
323 and status_id in (0,1,2)
324 and reschedule_time <= sysdate
325 order by priority_level, due_date;
326
327 begin
328 for c1_rec in c1 loop
329 -- Update Work Item Rec
330 l_nw_item_list(l_ctr).work_item_id := c1_rec.work_item_id;
331 l_nw_item_list(l_ctr).priority_level := c1_rec.priority_level;
332 l_nw_item_list(l_ctr).due_date := c1_rec.due_date;
333 l_nw_item_list(l_ctr).workitem_obj_code := c1_rec.workitem_obj_code;
334 l_ctr := l_ctr + 1;
335 if nvl(p_no_of_recs, 0) <= 0 then
336 exit when c1%notfound;
337 elsif nvl(p_no_of_recs, 0) > 0 and c1%found then
338 if nvl(p_no_of_recs, 0) = c1%rowcount then
339 exit;
340 end if;
341 else
342 exit;
343 end if;
344 end loop;
345 end;
346
347 -------------- Individual Assigned Work Items ----------3
348
349 declare
350 cursor c1 is
351 select work_item_id, priority_level, to_char(due_date,'dd-mon-yyyy hh24:mi:ss') due_date,
352 workitem_obj_code
353 from ieu_uwqm_items
354 where assignee_type = l_owner_type_ind
355 and assignee_id = p_resource_id
356 and status_id in (0,1,2)
357 and reschedule_time <= sysdate
358 order by priority_level, due_date;
359
360 begin
361 for c1_rec in c1 loop
362 -- Update Work Item Rec
363 l_nw_item_list(l_ctr).work_item_id := c1_rec.work_item_id;
364 l_nw_item_list(l_ctr).priority_level := c1_rec.priority_level;
365 l_nw_item_list(l_ctr).due_date := c1_rec.due_date;
366 l_nw_item_list(l_ctr).workitem_obj_code := c1_rec.workitem_obj_code;
367 l_ctr := l_ctr + 1;
368 if nvl(p_no_of_recs, 0) <= 0 then
369 exit when c1%notfound;
370 elsif nvl(p_no_of_recs, 0) > 0 and c1%found then
371 if nvl(p_no_of_recs, 0) = c1%rowcount then
372 exit;
373 end if;
374 else
375 exit;
376 end if;
377 end loop;
378 end;
379
380 -------------- Group Assigned Work Items ----------4
381
382 declare
383 cursor c1 is
384 select work_item_id, priority_level, to_char(due_date,'dd-mon-yyyy hh24:mi:ss') due_date,
385 workitem_obj_code
386 from ieu_uwqm_items
387 where assignee_type = l_owner_type_grp
388 and assignee_id in (select group_id from jtf_rs_group_members where resource_id =p_resource_id)
389 and status_id in (0,1,2)
390 and reschedule_time <= sysdate
391 order by priority_level, due_date;
392
393 begin
394 for c1_rec in c1 loop
395 -- Update Work Item Rec
396 l_nw_item_list(l_ctr).work_item_id := c1_rec.work_item_id;
397 l_nw_item_list(l_ctr).priority_level := c1_rec.priority_level;
398 l_nw_item_list(l_ctr).due_date := c1_rec.due_date;
399 l_nw_item_list(l_ctr).workitem_obj_code := c1_rec.workitem_obj_code;
400 l_ctr := l_ctr + 1;
401 if nvl(p_no_of_recs, 0) <= 0 then
402 exit when c1%notfound;
403 elsif nvl(p_no_of_recs, 0) > 0 and c1%found then
404 if nvl(p_no_of_recs, 0) = c1%rowcount then
405 exit;
406 end if;
407 else
408 exit;
409 end if;
410 end loop;
411 end;
412 exception
413 when no_data_found then
414 l_ind_own_no_data_found := 1;
415 end;
416
417 -- (If there is no work owned or assigned to an INDIVIDUAL
418 -- or a GROUP then raise exception and return the message)
419
420 l_ctr := l_nw_item_list.count;
421
422 if (l_ctr = 0)
423 then
424 null;
425 /*
426 x_return_status := fnd_api.g_ret_sts_error;
427
428 FND_MESSAGE.SET_NAME('IEU', 'IEU_UWQ_GET_NEXT_WORK_FAILED');
429
430 fnd_msg_pub.ADD;
431 fnd_msg_pub.Count_and_Get
432 (
433 p_count => x_msg_count,
434 p_data => x_msg_data
435 );
436
437 RAISE fnd_api.g_exc_error;
438 */
439 else
440
441 -- Order the work items by priority level, due date and Object_code
442 -- Return the best work item
443
444 -------------------- Sort by Priority Level -------------
445
446 for i in l_nw_item_list.first..(l_nw_item_list.last-1)
447 loop
448 l_loop_ctr := i;
449
450 l_work_item_id := l_nw_item_list(i).work_item_id;
451 l_priority_level := l_nw_item_list(i).priority_level;
452 l_due_date := l_nw_item_list(i).due_date;
453 l_workitem_obj_code := l_nw_item_list(i).workitem_obj_code;
454
455 if ( l_priority_level > l_nw_item_list(i+1).priority_level) then
456
457 ----move second to first
458 l_nw_item_list(i).work_item_id := l_nw_item_list(i+1).work_item_id;
459 l_nw_item_list(i).priority_level := l_nw_item_list(i+1).priority_level;
460 l_nw_item_list(i).due_date := l_nw_item_list(i+1).due_date;
461 l_nw_item_list(i).workitem_obj_code := l_nw_item_list(i+1).workitem_obj_code;
462
463 ----move swapped to second
464 l_nw_item_list(i+1).work_item_id := l_work_item_id;
465 l_nw_item_list(i+1).priority_level := l_priority_level;
466 l_nw_item_list(i+1).due_date := l_due_date;
467 l_nw_item_list(i+1).workitem_obj_code := l_workitem_obj_code;
468
469 ----------------------------------------------------------------------------
470 for k in reverse l_nw_item_list.first..l_loop_ctr
471 loop
472
473 l_work_item_id_1 := l_nw_item_list(k).work_item_id;
474 l_priority_level_1 := l_nw_item_list(k).priority_level;
475 l_due_date_1 := l_nw_item_list(k).due_date;
476 l_workitem_obj_code_1 := l_nw_item_list(k).workitem_obj_code;
477
478 if ( l_priority_level_1 > l_nw_item_list(k+1).priority_level) then
479
480 ----move second to first
481 l_nw_item_list(k).work_item_id := l_nw_item_list(k+1).work_item_id;
482 l_nw_item_list(k).priority_level := l_nw_item_list(k+1).priority_level;
483 l_nw_item_list(k).due_date := l_nw_item_list(k+1).due_date;
484 l_nw_item_list(k).workitem_obj_code := l_nw_item_list(k+1).workitem_obj_code;
485
486 ----move swapped to second
487 l_nw_item_list(k+1).work_item_id := l_work_item_id_1;
488 l_nw_item_list(k+1).priority_level := l_priority_level_1;
489 l_nw_item_list(k+1).due_date := l_due_date_1;
490 l_nw_item_list(k+1).workitem_obj_code := l_workitem_obj_code_1;
491
492 elsif ( l_priority_level_1 < l_nw_item_list(k+1).priority_level) then
493 l_work_item_id_1 := l_nw_item_list(k+1).work_item_id;
494 l_priority_level_1 := l_nw_item_list(k+1).priority_level;
495 l_due_date_1 := l_nw_item_list(k+1).due_date;
496 l_workitem_obj_code_1 := l_nw_item_list(k+1).workitem_obj_code;
497 end if;
498
499 end loop; -- for k loop
500
501 elsif ( l_priority_level < l_nw_item_list(i+1).priority_level) then
502
503 l_work_item_id := l_nw_item_list(i+1).work_item_id;
504 l_priority_level := l_nw_item_list(i+1).priority_level;
505 l_due_date := l_nw_item_list(i+1).due_date;
506 l_workitem_obj_code := l_nw_item_list(i+1).workitem_obj_code;
507
508 end if;
509
510 end loop;
511
512 -------------------- Sort by Due date -------------
513
514 for i in l_nw_item_list.first..(l_nw_item_list.last-1)
515 loop
516 l_loop_ctr := i;
517
518 l_work_item_id := l_nw_item_list(i).work_item_id;
519 l_priority_level := l_nw_item_list(i).priority_level;
520 l_due_date := l_nw_item_list(i).due_date;
521 l_workitem_obj_code := l_nw_item_list(i).workitem_obj_code;
522
523 if ( l_priority_level = l_nw_item_list(i+1).priority_level) then
524
525 If l_due_date is null and l_nw_item_list(i+1).due_date is not null then
526
527 ----move second to first
528 l_nw_item_list(i).work_item_id := l_nw_item_list(i+1).work_item_id;
529 l_nw_item_list(i).priority_level := l_nw_item_list(i+1).priority_level;
530 l_nw_item_list(i).due_date := l_nw_item_list(i+1).due_date;
531 l_nw_item_list(i).workitem_obj_code := l_nw_item_list(i+1).workitem_obj_code;
532
533 ----move swapped to second
534 l_nw_item_list(i+1).work_item_id := l_work_item_id;
535 l_nw_item_list(i+1).priority_level := l_priority_level;
536 l_nw_item_list(i+1).due_date := l_due_date;
537 l_nw_item_list(i+1).workitem_obj_code := l_workitem_obj_code;
538
539 elsif ( FND_DATE.STRING_TO_DATE(l_due_date, 'dd-mon-yyyy hh24:mi:ss') >
540 FND_DATE.STRING_TO_DATE(l_nw_item_list(i+1).due_date, 'dd-mon-yyyy hh24:mi:ss') ) then
541
542 ----move second to first
543 l_nw_item_list(i).work_item_id := l_nw_item_list(i+1).work_item_id;
544 l_nw_item_list(i).priority_level := l_nw_item_list(i+1).priority_level;
545 l_nw_item_list(i).due_date := l_nw_item_list(i+1).due_date;
546 l_nw_item_list(i).workitem_obj_code := l_nw_item_list(i+1).workitem_obj_code;
547
548 ----move swapped to second
549 l_nw_item_list(i+1).work_item_id := l_work_item_id;
550 l_nw_item_list(i+1).priority_level := l_priority_level;
551 l_nw_item_list(i+1).due_date := l_due_date;
552 l_nw_item_list(i+1).workitem_obj_code := l_workitem_obj_code;
553
554 ----------------------------------------------------------------------------
555 for k in reverse l_nw_item_list.first..l_loop_ctr
556 loop
557
558 l_work_item_id_1 := l_nw_item_list(k).work_item_id;
559 l_priority_level_1 := l_nw_item_list(k).priority_level;
560 l_due_date_1 := l_nw_item_list(k).due_date;
561 l_workitem_obj_code_1 := l_nw_item_list(k).workitem_obj_code;
562
563 if ( l_priority_level_1 = l_nw_item_list(k+1).priority_level) then
564
565 if ( FND_DATE.STRING_TO_DATE(l_due_date_1, 'dd-mon-yyyy hh24:mi:ss') >
566 FND_DATE.STRING_TO_DATE(l_nw_item_list(k+1).due_date, 'dd-mon-yyyy hh24:mi:ss')) then
567
568 ----move second to first
569 l_nw_item_list(k).work_item_id := l_nw_item_list(k+1).work_item_id;
570 l_nw_item_list(k).priority_level := l_nw_item_list(k+1).priority_level;
571 l_nw_item_list(k).due_date := l_nw_item_list(k+1).due_date;
572 l_nw_item_list(k).workitem_obj_code := l_nw_item_list(k+1).workitem_obj_code;
573
574 ----move swapped to second
575 l_nw_item_list(k+1).work_item_id := l_work_item_id_1;
576 l_nw_item_list(k+1).priority_level := l_priority_level_1;
577 l_nw_item_list(k+1).due_date := l_due_date_1;
578 l_nw_item_list(k+1).workitem_obj_code := l_workitem_obj_code_1;
579
580 elsif ( FND_DATE.STRING_TO_DATE(l_due_date_1, 'dd-mon-yyyy hh24:mi:ss') <
581 FND_DATE.STRING_TO_DATE(l_nw_item_list(k+1).due_date, 'dd-mon-yyyy hh24:mi:ss')) then
582 l_work_item_id_1 := l_nw_item_list(k+1).work_item_id;
583 l_priority_level_1 := l_nw_item_list(k+1).priority_level;
584 l_due_date_1 := l_nw_item_list(k+1).due_date;
585 l_workitem_obj_code_1 := l_nw_item_list(k+1).workitem_obj_code;
586 end if;
587
588 end if;
589
590 end loop; -- for k loop
591
592 elsif ( FND_DATE.STRING_TO_DATE(l_due_date, 'dd-mon-yyyy hh24:mi:ss') <
593 FND_DATE.STRING_TO_DATE(l_nw_item_list(i+1).due_date, 'dd-mon-yyyy hh24:mi:ss')) then
594
595 l_work_item_id := l_nw_item_list(i+1).work_item_id;
596 l_priority_level := l_nw_item_list(i+1).priority_level;
597 l_due_date := l_nw_item_list(i+1).due_date;
598 l_workitem_obj_code := l_nw_item_list(i+1).workitem_obj_code;
599
600 end if;
601
602 end if;
603
604 end loop;
605
606 /*
607 -- REMOVED THE SORTING BY WORKITEM OBJECT CODE (DEC-06-2001) - ckurian
608
609 ---------------- Sort by Workitem Object Code -------------
610
611 for i in l_nw_item_list.first..(l_nw_item_list.last-1)
612 loop
613 l_loop_ctr := i;
614
615 l_work_item_id := l_nw_item_list(i).work_item_id;
616 l_priority_level := l_nw_item_list(i).priority_level;
617 l_due_date := l_nw_item_list(i).due_date;
618 l_workitem_obj_code := l_nw_item_list(i).workitem_obj_code;
619
620 if (( l_priority_level = l_nw_item_list(i+1).priority_level) and
621 ( l_due_date is null and l_nw_item_list(i+1).due_date is null)) OR
622 (( l_priority_level = l_nw_item_list(i+1).priority_level) and
623 ( l_due_date = l_nw_item_list(i+1).due_date)) then
624
625 If ( l_workitem_obj_code > l_nw_item_list(i+1).workitem_obj_code) then
626
627 ----move second to first
628 l_nw_item_list(i).work_item_id := l_nw_item_list(i+1).work_item_id;
629 l_nw_item_list(i).priority_level := l_nw_item_list(i+1).priority_level;
630 l_nw_item_list(i).due_date := l_nw_item_list(i+1).due_date;
631 l_nw_item_list(i).workitem_obj_code := l_nw_item_list(i+1).workitem_obj_code;
632
633 ----move swapped to second
634 l_nw_item_list(i+1).work_item_id := l_work_item_id;
635 l_nw_item_list(i+1).priority_level := l_priority_level;
636 l_nw_item_list(i+1).due_date := l_due_date;
637 l_nw_item_list(i+1).workitem_obj_code := l_workitem_obj_code;
638
639 ----------------------------------------------------------------------------
640 for k in reverse l_nw_item_list.first..l_loop_ctr
641 loop
642
643 l_work_item_id_1 := l_nw_item_list(k).work_item_id;
644 l_priority_level_1 := l_nw_item_list(k).priority_level;
645 l_due_date_1 := l_nw_item_list(k).due_date;
646 l_workitem_obj_code_1 := l_nw_item_list(k).workitem_obj_code;
647
648 If (( l_priority_level_1 = l_nw_item_list(k+1).priority_level) and
649 ( l_due_date_1 is null and l_nw_item_list(k+1).due_date is null)) OR
650 (( l_priority_level_1 = l_nw_item_list(k+1).priority_level) and
651 ( l_due_date_1 = l_nw_item_list(k+1).due_date)) then
652
653 If ( l_workitem_obj_code_1 > l_nw_item_list(k+1).workitem_obj_code) then
654
655 ----move second to first
656 l_nw_item_list(k).work_item_id := l_nw_item_list(k+1).work_item_id;
657 l_nw_item_list(k).priority_level := l_nw_item_list(k+1).priority_level;
658 l_nw_item_list(k).due_date := l_nw_item_list(k+1).due_date;
659 l_nw_item_list(k).workitem_obj_code := l_nw_item_list(k+1).workitem_obj_code;
660
661 ----move swapped to second
662 l_nw_item_list(k+1).work_item_id := l_work_item_id_1;
663 l_nw_item_list(k+1).priority_level := l_priority_level_1;
664 l_nw_item_list(k+1).due_date := l_due_date_1;
665 l_nw_item_list(k+1).workitem_obj_code := l_workitem_obj_code_1;
666
667 elsif ( l_workitem_obj_code_1 < l_nw_item_list(k+1).workitem_obj_code) then
668 l_work_item_id_1 := l_nw_item_list(k+1).work_item_id;
669 l_priority_level_1 := l_nw_item_list(k+1).priority_level;
670 l_due_date_1 := l_nw_item_list(k+1).due_date;
671 l_workitem_obj_code_1 := l_nw_item_list(k+1).workitem_obj_code;
672 end if;
673 end if;
674 end loop; -- for k loop
675
676 elsif ( l_workitem_obj_code < l_nw_item_list(i+1).workitem_obj_code) then
677 l_work_item_id := l_nw_item_list(i+1).work_item_id;
678 l_priority_level := l_nw_item_list(i+1).priority_level;
679 l_due_date := l_nw_item_list(i+1).due_date;
680 l_workitem_obj_code := l_nw_item_list(i+1).workitem_obj_code;
681 end if;
682
683 end if;
684
685 end loop;
686
687 */
688 ------------- To eliminate duplicate Work Item Ids ----------------
689
690 for i in l_nw_item_list.first..l_nw_item_list.last loop
691 l_work_item_id_last := l_nw_item_list(i).work_item_id;
692
693 for k in reverse l_nw_item_list.first..i loop
694 if l_work_item_id_last = l_nw_item_list(k).work_item_id and
695 i <> k then
696 l_nw_item_list(k).work_item_id := null;
697 end if;
698 end loop;
699 end loop;
700
701
702 l_ctr := l_nw_item_list.first;
703
704 for i in l_nw_item_list.first..l_nw_item_list.last loop
705 if nvl(l_nw_item_list(i).work_item_id,0) > 0 then
706 x_uwqm_workitem_data(l_ctr).work_item_id := l_nw_item_list(i).work_item_id;
707 x_uwqm_workitem_data(l_ctr).priority_level := l_nw_item_list(i).priority_level;
708 x_uwqm_workitem_data(l_ctr).due_date := FND_DATE.STRING_TO_DATE(l_nw_item_list(i).due_date, 'dd-mon-yyyy hh24:mi:ss');
709 x_uwqm_workitem_data(l_ctr).workitem_obj_code := l_nw_item_list(i).workitem_obj_code;
710 l_ctr := l_ctr + 1;
711 end if;
712 end loop;
713
714 l_nw_item_list.delete;
715
716 for i in x_uwqm_workitem_data.first..x_uwqm_workitem_data.last loop
717 l_nw_item_list(i).work_item_id := x_uwqm_workitem_data(i).work_item_id;
718 l_nw_item_list(i).priority_level := x_uwqm_workitem_data(i).priority_level;
719 l_nw_item_list(i).due_date := x_uwqm_workitem_data(i).due_date;
720 l_nw_item_list(i).workitem_obj_code := x_uwqm_workitem_data(i).workitem_obj_code;
721 end loop;
722
723 x_uwqm_workitem_data.delete;
724
725
726 BEGIN
727
728 for i in l_nw_item_list.first..l_nw_item_list.last
729 loop
730
731 SELECT UWQM.WORK_ITEM_ID,
732 UWQM.WORKITEM_OBJ_CODE,
733 UWQM.WORKITEM_PK_ID,
734 UWQM.STATUS_ID,
735 UWQM.PRIORITY_ID,
736 UWQM.PRIORITY_LEVEL,
737 PR.NAME PRIORITY,
738 UWQM.DUE_DATE,
739 UWQM.TITLE,
740 UWQM.PARTY_ID,
741 UWQM.OWNER_ID,
742 UWQM.OWNER_TYPE,
743 UWQM.ASSIGNEE_ID,
744 UWQM.ASSIGNEE_TYPE,
745 UWQM.SOURCE_OBJECT_ID,
746 UWQM.SOURCE_OBJECT_TYPE_CODE,
747 UWQM.OWNER_TYPE_ACTUAL,
748 UWQM.ASSIGNEE_TYPE_ACTUAL,
749 UWQM.APPLICATION_ID,
750 ENUM.ENUM_TYPE_UUID IEU_ENUM_TYPE_UUID,
751 UWQM.STATUS_UPDATE_USER_ID,
752 UWQM.WORK_ITEM_NUMBER,
753 UWQM.RESCHEDULE_TIME,
754 LKUPS.MEANING WORK_TYPE,
755 DECODE(STATUS_ID, 0, '', 1, LKUPS1.MEANING, 2, LKUPS1.MEANING) STATUS_CODE
756 INTO X_UWQM_WORKITEM_DATA(i)
757 FROM IEU_UWQM_ITEMS UWQM,
758 IEU_UWQ_SEL_ENUMERATORS ENUM,
759 IEU_UWQM_PRIORITIES_TL PR,
760 FND_LOOKUP_VALUES_VL LKUPS,
761 FND_LOOKUP_VALUES_VL LKUPS1
762 WHERE UWQM.WORK_ITEM_ID = l_nw_item_list(i).work_item_id
763 AND ENUM.ENUM_TYPE_UUID = UWQM.IEU_ENUM_TYPE_UUID
764 AND PR.PRIORITY_ID = UWQM.PRIORITY_ID
765 AND LKUPS.LOOKUP_TYPE(+) = ENUM.WORK_Q_LABEL_LU_TYPE
766 AND LKUPS.VIEW_APPLICATION_ID(+) = ENUM.APPLICATION_ID
767 AND LKUPS.LOOKUP_CODE(+) = ENUM.WORK_Q_LABEL_LU_CODE
768 AND LKUPS1.LOOKUP_TYPE = 'IEU_NODE_LABELS'
769 AND LKUPS1.VIEW_APPLICATION_ID = ENUM.APPLICATION_ID
770 AND LKUPS1.LOOKUP_CODE = 'IN_USE' ;
771 end loop;
772
773 EXCEPTION
774 when no_data_found
775 then
776 raise fnd_api.g_exc_unexpected_error;
777 END;
778
779 t2 := DBMS_UTILITY.GET_TIME;
780 l_time_spent := t2 - t1;
781
782 -- insert into IEU_UNIQUE_TEST_RESULTS (result_code,user_id, time_spent, task_id)
783 -- values ('CK', p_resource_id, l_time_spent, l_work_item_id);
784
785 commit;
786
787 end if;
788
789 EXCEPTION
790
791 WHEN fnd_api.g_exc_error THEN
792
793 x_return_status := fnd_api.g_ret_sts_error;
794
795 fnd_msg_pub.Count_and_Get
796 (
797 p_count => x_msg_count,
798 p_data => x_msg_data
799 );
800
801 WHEN fnd_api.g_exc_unexpected_error THEN
802
803 x_return_status := fnd_api.g_ret_sts_unexp_error;
804
805 fnd_msg_pub.Count_and_Get
806 (
807 p_count => x_msg_count,
808 p_data => x_msg_data
809 );
810
811 WHEN OTHERS THEN
812
813 IF FND_MSG_PUB.Check_msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
814 THEN
815
816 fnd_msg_pub.Count_and_Get
817 (
818 p_count => x_msg_count,
819 p_data => x_msg_data
820 );
821 end if;
822
823 END GET_WORKLIST_QUEUE;
824
825
826
827 FUNCTION GET_WORKLIST_QUEUE_COUNT
828 ( p_resource_id IN NUMBER,
829 p_status_id IN NUMBER,
830 p_node_type IN NUMBER)
831 RETURN NUMBER
832 IS x_tot_count NUMBER(20);
833
834 -- Used for Time Analysis
835 t1 NUMBER; -- start time
836 t2 NUMBER; -- end time
837 l_time_spent NUMBER; -- time elapsed
838
839 -- Used to get the group count
840
841 l_count NUMBER := 0;
842 l_ctr PLS_INTEGER := 0;
843
844 --- Values assigned for different work items ---
845
846 l_owner_type_ind varchar2(25);
847 l_owner_type_grp varchar2(25);
848 l_work_item_id number(15);
849
850 l_nw_item_list IEU_UWQ_GET_NEXT_WORK_PVT.IEU_UWQ_NEXTWORK_ITEM_LIST;
851
852 BEGIN
853
854 l_owner_type_ind := 'RS_INDIVIDUAL';
855 l_owner_type_grp := 'RS_GROUP';
856 x_tot_count := 0;
857
858 -------------- Individual Owned Work Items ----------1
859 declare
860 cursor c1 is
861 select work_item_id
862 from ieu_uwqm_items
863 where owner_type = l_owner_type_ind
864 and owner_id = p_resource_id
865 and status_id = p_status_id
866 and (p_node_type = 1 or p_node_type = 2)
867 and reschedule_time <= sysdate
868 union
869 select work_item_id
870 from ieu_uwqm_items
871 where owner_type = l_owner_type_grp
872 and owner_id in (select group_id from jtf_rs_group_members where resource_id = p_resource_id)
873 and status_id = p_status_id
874 and (p_node_type = 1 or p_node_type = 3)
875 and reschedule_time <= sysdate
876 union
877 select work_item_id
878 from ieu_uwqm_items
879 where assignee_type = l_owner_type_ind
880 and assignee_id = p_resource_id
881 and status_id = p_status_id
882 and (p_node_type = 1 or p_node_type = 4)
883 and reschedule_time <= sysdate
884 union
885 select work_item_id
886 from ieu_uwqm_items
887 where assignee_type = l_owner_type_grp
888 and assignee_id in (select group_id from jtf_rs_group_members where resource_id =p_resource_id)
889 and status_id = p_status_id
890 and (p_node_type = 1 or p_node_type = 5)
891 and reschedule_time <= sysdate;
892 begin
893 for c1_rec in c1 loop
894 -- Update Work Item Rec
895 l_nw_item_list(l_ctr).work_item_id := c1_rec.work_item_id;
896 l_ctr := l_ctr + 1;
897 exit when c1%notfound;
898 end loop;
899 end;
900
901 x_tot_count := l_nw_item_list.count;
902
903 /*
904 ------------- To eliminate duplicate Work Item Ids ----------------
905 If x_tot_count > 0 then
906
907 for i in l_nw_item_list.first..l_nw_item_list.last loop
908 l_work_item_id := l_nw_item_list(i).work_item_id;
909
910 for k in reverse l_nw_item_list.first..i loop
911 if l_work_item_id = l_nw_item_list(k).work_item_id and
912 i <> k then
913 x_tot_count := x_tot_count - 1;
914 end if;
915 end loop;
916 end loop;
917
918 end if;
919 */
920
921 RETURN (x_tot_count);
922
923 END GET_WORKLIST_QUEUE_COUNT;
924
925 PROCEDURE DISTRIBUTE_AND_DELIVER_WR_ITEM
926 ( p_api_version IN NUMBER,
927 p_resource_id IN NUMBER,
928 p_language IN VARCHAR2,
929 p_source_lang IN VARCHAR2,
930 p_dist_from_extra_where_clause IN VARCHAR2,
931 p_dist_to_extra_where_clause IN VARCHAR2,
932 p_bindvar_from_list IN IEU_UWQ_BINDVAR_LIST,
933 p_bindvar_to_list IN IEU_UWQ_BINDVAR_LIST,
934 x_uwqm_workitem_data OUT NOCOPY IEU_FRM_PVT.T_IEU_MEDIA_DATA,
935 x_msg_count OUT NOCOPY NUMBER,
936 x_msg_data OUT NOCOPY VARCHAR2,
937 x_return_status OUT NOCOPY VARCHAR2) IS
938
939 -- Used to Validate API version and name
940 l_api_version CONSTANT NUMBER := 1.0;
941 l_api_name CONSTANT VARCHAR2(30) := 'DISTRIBUTE_AND_DELIVER_WR_ITEM';
942
943
944 l_num_of_items_distributed NUMBER := 0;
945
946 l_sql_stmt VARCHAR2(4000);
947 l_del_status NUMBER := 3;
948 l_dist_status NUMBER := 1;
949 l_open_status_id NUMBER := 0;
950 l_resource_id NUMBER := 100001713;
951 l_next_wr_items IEU_UWQ_GET_NEXT_WORK_PVT.l_get_work;
952
953
954 -- Table of records for all OUT variables
955 l_del_wr_cur IEU_UWQ_GET_NEXT_WORK_PVT.l_get_work;
956 l_dist_wr_cur IEU_UWQ_GET_NEXT_WORK_PVT.l_get_work;
957 l_del_nw_item IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WR_ITEM_DATA_REC := null;
958 l_dist_nw_item IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WR_ITEM_DATA_REC := null;
959 l_nw_items_list IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WR_ITEM_DATA;
960 l_dist_flag VARCHAR2(10);
961 l_del_items_flag VARCHAR2(10);
962 l_dist_items_flag VARCHAR2(10);
963 l_delivery_only_flag VARCHAR2(10);
964
965 l_object_function VARCHAR2(40);
966 l_object_parameters VARCHAR2(500);
967 l_enter_from_task VARCHAR2(10);
968 l_ws_id NUMBER;
969 l_ctr NUMBER := 0;
970
971 -- used for Distribution
972 l_distribute_to IEU_UWQM_WORK_SOURCES_B.DISTRIBUTE_TO%TYPE;
973 l_distribute_from IEU_UWQM_WORK_SOURCES_B.DISTRIBUTE_FROM%TYPE;
974 l_distribution_function IEU_UWQM_WORK_SOURCES_B.DISTRIBUTION_FUNCTION%TYPE;
975 l_dist_st_based_on_parent_flag IEU_UWQM_WS_ASSCT_PROPS.DIST_ST_BASED_ON_PARENT_FLAG%TYPE;
976 l_ws_code IEU_UWQM_WORK_SOURCES_B.WS_CODE%TYPE;
977 l_dist_bus_rules SYSTEM.DIST_BUS_RULES_NST;
978 l_work_item_status VARCHAR2(50);
979 l_work_item_status_id NUMBER;
980 l_dist_items SYSTEM.WR_ITEM_DATA_NST;
981 l_priority_code IEU_UWQM_PRIORITIES_B.PRIORITY_CODE%TYPE;
982 l_NUM_OF_DIST_ITEMS NUMBER := 1;
983 l_dist_wr_cur_cnt NUMBER;
984 l_dist_work_item_id NUMBER;
985 l_priority_level NUMBER;
986
987 L_MSG_COUNT NUMBER;
988 L_MSG_DATA VARCHAR2(4000);
989 L_RETURN_STATUS VARCHAR2(10);
990
991 l_dist_item_ctr number := 0;
992
993 -- Audit Trail
994 l_action_key VARCHAR2(500);
995 l_event_key VARCHAR2(500);
996 l_module VARCHAR2(1000);
997 l_application_id NUMBER;
998 --l_ws_code VARCHAR2(500);
999 l_ret_sts VARCHAR2(10);
1000 l_audit_log_val VARCHAR2(100);
1001 l_ieu_comment_code1 VARCHAR2(2000);
1002 l_ieu_comment_code2 VARCHAR2(2000);
1003 l_ieu_comment_code3 VARCHAR2(2000);
1004 l_ieu_comment_code4 VARCHAR2(2000);
1005 l_ieu_comment_code5 VARCHAR2(2000);
1006 l_workitem_comment_code1 VARCHAR2(2000);
1007 l_workitem_comment_code2 VARCHAR2(2000);
1008 l_workitem_comment_code3 VARCHAR2(2000);
1009 l_workitem_comment_code4 VARCHAR2(2000);
1010 l_workitem_comment_code5 VARCHAR2(2000);
1011
1012 l_workitem_pk_id NUMBER;
1013 l_workitem_obj_code VARCHAR2(50);
1014 l_audit_log_sts VARCHAR2(50);
1015 l_owner_id NUMBER;
1016 l_owner_type VARCHAR2(500);
1017 l_assignee_id NUMBER;
1018 l_assignee_type VARCHAR2(500);
1019 l_priority_id NUMBER;
1020 l_due_date DATE;
1021 l_source_object_id NUMBER;
1022 l_source_object_type_code VARCHAR2(500);
1023 l_status_id NUMBER;
1024 l_distribution_status_id NUMBER;
1025 l_reschedule_time DATE;
1026 l_token_str VARCHAR2(4000);
1027 TYPE AUDIT_LOG_ID_TBL is TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1028 l_audit_log_id_list AUDIT_LOG_ID_TBL;
1029 l_audit_log_id NUMBER;
1030 l_not_valid_flag VARCHAR2(1);
1031 cursor_id PLS_INTEGER;
1032 dummy PLS_INTEGER;
1033 temp number;
1034 v varchar2(1000);
1035 BEGIN
1036 v := p_bindvar_to_list.count;
1037 l_del_items_flag := 'Y';
1038 l_dist_items_flag := 'Y';
1039 l_distribute_to := 'INDIVIDUAL_ASSIGNED';
1040 l_distribute_from := 'GROUP_OWNED';
1041 l_not_valid_flag := 'N';
1042 l_audit_log_val := FND_PROFILE.VALUE('IEU_WR_DIST_AUDIT_LOG');
1043
1044 x_return_status := fnd_api.g_ret_sts_success;
1045
1046 IF NOT fnd_api.compatible_api_call (
1047 l_api_version,
1048 p_api_version,
1049 l_api_name,
1050 g_pkg_name
1051 )
1052 THEN
1053 RAISE fnd_api.g_exc_unexpected_error;
1054 END IF;
1055
1056 -- Initialize Message list
1057
1058 FND_MSG_PUB.INITIALIZE;
1059
1060 LOOP
1061
1062 exit when ((l_dist_item_ctr >= 2) or (l_num_of_items_distributed > 0));
1063
1064 l_dist_item_ctr := l_dist_item_ctr + 1;
1065
1066 -- Audit Trail
1067 l_action_key := 'DELIVERY';
1068 if (l_audit_log_val = 'DETAILED')
1069 then
1070 l_ieu_comment_code1 := 'NUM_OF_ATTEMPTS '||l_dist_item_ctr;
1071 end if;
1072
1073 --- *** Get the Distributed Work Item with sorted by pty and due_date *** ---
1074
1075 -- IEU_UWQ_GET_NEXT_WORK_PVT.GET_WS_WHERE_CLAUSE('DIST_TO',l_where_clause);
1076
1077 -- Build the complete select stmt
1078 l_sql_stmt := 'SELECT /*+ first_rows */
1079 WORK_ITEM_ID,
1080 WORKITEM_OBJ_CODE,
1081 WORKITEM_PK_ID,
1082 STATUS_ID,
1083 PRIORITY_ID,
1084 PRIORITY_LEVEL,
1085 null, -- Selecting null for pty code
1086 DUE_DATE,
1087 TITLE,
1088 PARTY_ID,
1089 OWNER_ID,
1090 OWNER_TYPE,
1091 ASSIGNEE_ID,
1092 ASSIGNEE_TYPE,
1093 SOURCE_OBJECT_ID,
1094 SOURCE_OBJECT_TYPE_CODE,
1095 APPLICATION_ID,
1096 IEU_ENUM_TYPE_UUID,
1097 WORK_ITEM_NUMBER,
1098 RESCHEDULE_TIME,
1099 WS_ID
1100 FROM IEU_UWQM_ITEMS '||
1101 ' WHERE ( '|| p_dist_to_extra_where_clause || ' ) '||
1102 ' AND DISTRIBUTION_STATUS_ID = :l_del_status' ||
1103 ' AND STATUS_ID = :l_open_status_id ' ||
1104 ' and reschedule_time <= sysdate ' ||
1105 ' order by priority_level, due_date ';
1106
1107 -- insert into p_temp values ('dist to sql- '||l_sql_stmt, l_instr_to_num); commit;
1108
1109 -- OPEN l_del_wr_cur FOR l_sql_stmt
1110 -- USING IN p_resource_id, IN l_del_status, IN l_open_status_id;
1111
1112
1113
1114 cursor_id := dbms_sql.open_cursor;
1115
1116 DBMS_SQL.PARSE(cursor_id, l_sql_stmt, dbms_sql.native);
1117
1118 DBMS_SQL.BIND_VARIABLE(cursor_id,':l_del_status', l_del_status);
1119 DBMS_SQL.BIND_VARIABLE(cursor_id,':l_open_status_id', l_open_status_id);
1120 DBMS_SQL.BIND_VARIABLE(cursor_id,':resource_id', p_resource_id);
1121
1122
1123
1124 --insert into temp values (' to proc ',p_dist_to_extra_where_clause);
1125
1126 for i in 1..p_bindvar_to_list.count loop
1127 --insert into temp values (' to proc bind vars',p_bindvar_to_list(i).bind_name||' '||p_bindvar_to_list(i).value);
1128 DBMS_SQL.BIND_VARIABLE(cursor_id,p_bindvar_to_list(i).bind_name, p_bindvar_to_list(i).value);
1129
1130 end loop;
1131 DBMS_SQL.DEFINE_COLUMN(cursor_id,1,l_del_nw_item.WORK_ITEM_ID);
1132 DBMS_SQL.DEFINE_COLUMN(cursor_id,2,l_del_nw_item.WORKITEM_OBJ_CODE,30);
1133 DBMS_SQL.DEFINE_COLUMN(cursor_id,3,l_del_nw_item.WORKITEM_PK_ID);
1134 DBMS_SQL.DEFINE_COLUMN(cursor_id,4,l_del_nw_item.STATUS_ID);
1135 DBMS_SQL.DEFINE_COLUMN(cursor_id,5,l_del_nw_item.PRIORITY_ID);
1136 DBMS_SQL.DEFINE_COLUMN(cursor_id,6,l_del_nw_item.PRIORITY_LEVEL);
1137 DBMS_SQL.DEFINE_COLUMN(cursor_id,7,l_del_nw_item.PRIORITY_CODE,30);
1138 DBMS_SQL.DEFINE_COLUMN(cursor_id,8,l_del_nw_item.DUE_DATE);
1139 DBMS_SQL.DEFINE_COLUMN(cursor_id,9,l_del_nw_item.TITLE,1990);
1140 DBMS_SQL.DEFINE_COLUMN(cursor_id,10,l_del_nw_item.PARTY_ID);
1141 DBMS_SQL.DEFINE_COLUMN(cursor_id,11,l_del_nw_item.OWNER_ID);
1142 DBMS_SQL.DEFINE_COLUMN(cursor_id,12,l_del_nw_item.OWNER_TYPE,25);
1143 DBMS_SQL.DEFINE_COLUMN(cursor_id,13,l_del_nw_item.ASSIGNEE_ID);
1144 DBMS_SQL.DEFINE_COLUMN(cursor_id,14,l_del_nw_item.ASSIGNEE_TYPE,25);
1145 DBMS_SQL.DEFINE_COLUMN(cursor_id,15,l_del_nw_item.SOURCE_OBJECT_ID);
1146 DBMS_SQL.DEFINE_COLUMN(cursor_id,16,l_del_nw_item.SOURCE_OBJECT_TYPE_CODE,30);
1147 DBMS_SQL.DEFINE_COLUMN(cursor_id,17,l_del_nw_item.APPLICATION_ID);
1148 DBMS_SQL.DEFINE_COLUMN(cursor_id,18,l_del_nw_item.IEU_ENUM_TYPE_UUID,38);
1149 DBMS_SQL.DEFINE_COLUMN(cursor_id,19,l_del_nw_item.WORK_ITEM_NUMBER,64);
1150 DBMS_SQL.DEFINE_COLUMN(cursor_id,20,l_del_nw_item.RESCHEDULE_TIME);
1151 DBMS_SQL.DEFINE_COLUMN(cursor_id,21,l_del_nw_item.WS_ID);
1152 dummy := DBMS_SQL.EXECUTE(cursor_id);
1153 temp := DBMS_SQL.FETCH_ROWS(cursor_id);
1154 if temp <> 0 then
1155 DBMS_SQL.COLUMN_VALUE(cursor_id,1,l_del_nw_item.WORK_ITEM_ID);
1156 DBMS_SQL.COLUMN_VALUE(cursor_id,2,l_del_nw_item.WORKITEM_OBJ_CODE);
1157 DBMS_SQL.COLUMN_VALUE(cursor_id,3,l_del_nw_item.WORKITEM_PK_ID);
1158 DBMS_SQL.COLUMN_VALUE(cursor_id,4,l_del_nw_item.STATUS_ID);
1159 DBMS_SQL.COLUMN_VALUE(cursor_id,5,l_del_nw_item.PRIORITY_ID);
1160 DBMS_SQL.COLUMN_VALUE(cursor_id,6,l_del_nw_item.PRIORITY_LEVEL);
1161 DBMS_SQL.COLUMN_VALUE(cursor_id,7,l_del_nw_item.PRIORITY_CODE);
1162 DBMS_SQL.COLUMN_VALUE(cursor_id,8,l_del_nw_item.DUE_DATE);
1163 DBMS_SQL.COLUMN_VALUE(cursor_id,9,l_del_nw_item.TITLE);
1164 DBMS_SQL.COLUMN_VALUE(cursor_id,10,l_del_nw_item.PARTY_ID);
1165 DBMS_SQL.COLUMN_VALUE(cursor_id,11,l_del_nw_item.OWNER_ID);
1166 DBMS_SQL.COLUMN_VALUE(cursor_id,12,l_del_nw_item.OWNER_TYPE);
1167 DBMS_SQL.COLUMN_VALUE(cursor_id,13,l_del_nw_item.ASSIGNEE_ID);
1168 DBMS_SQL.COLUMN_VALUE(cursor_id,14,l_del_nw_item.ASSIGNEE_TYPE);
1169 DBMS_SQL.COLUMN_VALUE(cursor_id,15,l_del_nw_item.SOURCE_OBJECT_ID);
1170 DBMS_SQL.COLUMN_VALUE(cursor_id,16,l_del_nw_item.SOURCE_OBJECT_TYPE_CODE);
1171 DBMS_SQL.COLUMN_VALUE(cursor_id,17,l_del_nw_item.APPLICATION_ID);
1172 DBMS_SQL.COLUMN_VALUE(cursor_id,18,l_del_nw_item.IEU_ENUM_TYPE_UUID);
1173 DBMS_SQL.COLUMN_VALUE(cursor_id,19,l_del_nw_item.WORK_ITEM_NUMBER);
1174 DBMS_SQL.COLUMN_VALUE(cursor_id,20,l_del_nw_item.RESCHEDULE_TIME);
1175 DBMS_SQL.COLUMN_VALUE(cursor_id,21,l_del_nw_item.WS_ID);
1176 else
1177 -- NO Distributed Work item
1178 l_del_items_flag := 'N';
1179 end if;
1180 DBMS_SQL.CLOSE_CURSOR(cursor_id);
1181
1182
1183
1184
1185 -- FETCH l_del_wr_cur into l_del_nw_item;
1186
1187 -- Check if there are any Distributed Items for this resource
1188
1189 /* if (l_del_wr_cur%NOTFOUND)
1190 then
1191 -- NO Distributed Work item
1192 l_del_items_flag := 'N';
1193 end if;
1194 */
1195 -- CLOSE l_del_wr_cur;
1196
1197 --- *** Get the Distributable Work Item with sorted by pty and due_date *** ---
1198
1199 -- IEU_UWQ_GET_NEXT_WORK_PVT.GET_WS_WHERE_CLAUSE('DIST_FROM',l_where_clause);
1200
1201 -- Get the Distributed Work Item with sorted by pty and due_date
1202
1203 -- Build the complete select stmt
1204 l_sql_stmt := 'SELECT /*+ first_rows */
1205 WORK_ITEM_ID,
1206 WORKITEM_OBJ_CODE,
1207 WORKITEM_PK_ID,
1208 STATUS_ID,
1209 PRIORITY_ID,
1210 PRIORITY_LEVEL,
1211 null, -- Selecting null for pty code
1212 DUE_DATE,
1213 TITLE,
1214 PARTY_ID,
1215 OWNER_ID,
1216 OWNER_TYPE,
1217 ASSIGNEE_ID,
1218 ASSIGNEE_TYPE,
1219 SOURCE_OBJECT_ID,
1220 SOURCE_OBJECT_TYPE_CODE,
1221 APPLICATION_ID,
1222 IEU_ENUM_TYPE_UUID,
1223 WORK_ITEM_NUMBER,
1224 RESCHEDULE_TIME,
1225 WS_ID
1226 FROM IEU_UWQM_ITEMS '||
1227 ' WHERE ( '|| p_dist_from_extra_where_clause || ' ) '||
1228 ' AND DISTRIBUTION_STATUS_ID = :l_dist_status' ||
1229 ' AND STATUS_ID = :l_open_status_id ' ||
1230 ' and reschedule_time <= sysdate ' ||
1231 ' order by priority_level, due_date ' ||
1232 ' for update skip locked ';
1233
1234 -- insert into p_temp(msg) values ('dist from sql- '||l_sql_stmt|| ' res id: '||p_resource_id ||' dist st: '||l_dist_status
1235 -- || ' open st: '||l_open_status_id); commit;
1236
1237 l_ctr := 0;
1238 l_dist_wr_cur_cnt := 1;
1239
1240 -- Select the top 5 Work Items for Distribution
1241
1242 -- OPEN l_dist_wr_cur FOR l_sql_stmt
1243 -- USING IN l_dist_status, IN l_open_status_id;
1244
1245 cursor_id := dbms_sql.open_cursor;
1246 DBMS_SQL.PARSE(cursor_id, l_sql_stmt, dbms_sql.native);
1247
1248 DBMS_SQL.BIND_VARIABLE(cursor_id,':l_dist_status', l_dist_status);
1249 DBMS_SQL.BIND_VARIABLE(cursor_id,':l_open_status_id', l_open_status_id);
1250
1251
1252 for i in 1..p_bindvar_from_list.count loop
1253 DBMS_SQL.BIND_VARIABLE(cursor_id,p_bindvar_from_list(i).bind_name, p_bindvar_from_list(i).value);
1254 end loop;
1255
1256 DBMS_SQL.DEFINE_COLUMN(cursor_id,1,l_dist_nw_item.WORK_ITEM_ID);
1257 DBMS_SQL.DEFINE_COLUMN(cursor_id,2,l_dist_nw_item.WORKITEM_OBJ_CODE,30);
1258 DBMS_SQL.DEFINE_COLUMN(cursor_id,3,l_dist_nw_item.WORKITEM_PK_ID);
1259 DBMS_SQL.DEFINE_COLUMN(cursor_id,4,l_dist_nw_item.STATUS_ID);
1260 DBMS_SQL.DEFINE_COLUMN(cursor_id,5,l_dist_nw_item.PRIORITY_ID);
1261 DBMS_SQL.DEFINE_COLUMN(cursor_id,6,l_dist_nw_item.PRIORITY_LEVEL);
1262 DBMS_SQL.DEFINE_COLUMN(cursor_id,7,l_dist_nw_item.PRIORITY_CODE,30);
1263 DBMS_SQL.DEFINE_COLUMN(cursor_id,8,l_dist_nw_item.DUE_DATE);
1264 DBMS_SQL.DEFINE_COLUMN(cursor_id,9,l_dist_nw_item.TITLE,1990);
1265 DBMS_SQL.DEFINE_COLUMN(cursor_id,10,l_dist_nw_item.PARTY_ID);
1266 DBMS_SQL.DEFINE_COLUMN(cursor_id,11,l_dist_nw_item.OWNER_ID);
1267 DBMS_SQL.DEFINE_COLUMN(cursor_id,12,l_dist_nw_item.OWNER_TYPE,25);
1268 DBMS_SQL.DEFINE_COLUMN(cursor_id,13,l_dist_nw_item.ASSIGNEE_ID);
1269 DBMS_SQL.DEFINE_COLUMN(cursor_id,14,l_dist_nw_item.ASSIGNEE_TYPE,25);
1270 DBMS_SQL.DEFINE_COLUMN(cursor_id,15,l_dist_nw_item.SOURCE_OBJECT_ID);
1271 DBMS_SQL.DEFINE_COLUMN(cursor_id,16,l_dist_nw_item.SOURCE_OBJECT_TYPE_CODE,30);
1272 DBMS_SQL.DEFINE_COLUMN(cursor_id,17,l_dist_nw_item.APPLICATION_ID);
1273 DBMS_SQL.DEFINE_COLUMN(cursor_id,18,l_dist_nw_item.IEU_ENUM_TYPE_UUID,38);
1274 DBMS_SQL.DEFINE_COLUMN(cursor_id,19,l_dist_nw_item.WORK_ITEM_NUMBER,64);
1275 DBMS_SQL.DEFINE_COLUMN(cursor_id,20,l_dist_nw_item.RESCHEDULE_TIME);
1276 DBMS_SQL.DEFINE_COLUMN(cursor_id,21,l_dist_nw_item.WS_ID);
1277 dummy := DBMS_SQL.EXECUTE(cursor_id);
1278
1279 LOOP
1280
1281
1282 --dbms_output.put_line(' row cnt '||DBMS_SQL.FETCH_ROWS(cursor_id));
1283
1284 temp := DBMS_SQL.FETCH_ROWS(cursor_id);
1285
1286 if temp = 0 or (l_dist_wr_cur_cnt > 5) then
1287 exit;
1288 elsif temp <> 0 then
1289 DBMS_SQL.COLUMN_VALUE(cursor_id,1,l_dist_nw_item.WORK_ITEM_ID);
1290 DBMS_SQL.COLUMN_VALUE(cursor_id,2,l_dist_nw_item.WORKITEM_OBJ_CODE);
1291 DBMS_SQL.COLUMN_VALUE(cursor_id,3,l_dist_nw_item.WORKITEM_PK_ID);
1292 DBMS_SQL.COLUMN_VALUE(cursor_id,4,l_dist_nw_item.STATUS_ID);
1293 DBMS_SQL.COLUMN_VALUE(cursor_id,5,l_dist_nw_item.PRIORITY_ID);
1294 DBMS_SQL.COLUMN_VALUE(cursor_id,6,l_dist_nw_item.PRIORITY_LEVEL);
1295 DBMS_SQL.COLUMN_VALUE(cursor_id,7,l_dist_nw_item.PRIORITY_CODE);
1296 DBMS_SQL.COLUMN_VALUE(cursor_id,8,l_dist_nw_item.DUE_DATE);
1297 DBMS_SQL.COLUMN_VALUE(cursor_id,9,l_dist_nw_item.TITLE);
1298 DBMS_SQL.COLUMN_VALUE(cursor_id,10,l_dist_nw_item.PARTY_ID);
1299 DBMS_SQL.COLUMN_VALUE(cursor_id,11,l_dist_nw_item.OWNER_ID);
1300 DBMS_SQL.COLUMN_VALUE(cursor_id,12,l_dist_nw_item.OWNER_TYPE);
1301 DBMS_SQL.COLUMN_VALUE(cursor_id,13,l_dist_nw_item.ASSIGNEE_ID);
1302 DBMS_SQL.COLUMN_VALUE(cursor_id,14,l_dist_nw_item.ASSIGNEE_TYPE);
1303 DBMS_SQL.COLUMN_VALUE(cursor_id,15,l_dist_nw_item.SOURCE_OBJECT_ID);
1304 DBMS_SQL.COLUMN_VALUE(cursor_id,16,l_dist_nw_item.SOURCE_OBJECT_TYPE_CODE);
1305 DBMS_SQL.COLUMN_VALUE(cursor_id,17,l_dist_nw_item.APPLICATION_ID);
1306 DBMS_SQL.COLUMN_VALUE(cursor_id,18,l_dist_nw_item.IEU_ENUM_TYPE_UUID);
1307 DBMS_SQL.COLUMN_VALUE(cursor_id,19,l_dist_nw_item.WORK_ITEM_NUMBER);
1308 DBMS_SQL.COLUMN_VALUE(cursor_id,20,l_dist_nw_item.RESCHEDULE_TIME);
1309 DBMS_SQL.COLUMN_VALUE(cursor_id,21,l_dist_nw_item.WS_ID);
1310
1311 end if;
1312 -- LOOP
1313
1314 -- FETCH l_dist_wr_cur into l_dist_nw_item;
1315
1316 -- insert into p_temp(msg) values ('Dist item: '||l_dist_nw_item.workitem_pk_id);
1317
1318 -- exit when ( (l_dist_wr_cur%NOTFOUND) OR (l_dist_wr_cur_cnt > 5) ) ;
1319
1320 l_dist_wr_cur_cnt := l_dist_wr_cur_cnt + 1;
1321
1322
1323 update ieu_uwqm_items
1324 set distribution_status_id = 2
1325 where work_item_id = l_dist_nw_item.WORK_ITEM_ID;
1326
1327
1328 -- Add items to the Table of rec
1329 BEGIN
1330 select priority_code
1331 into l_priority_code
1332 from ieu_uwqm_priorities_b
1333 where priority_id = l_dist_nw_item.PRIORITY_ID;
1334 EXCEPTION
1335 WHEN OTHERS THEN
1336 null;
1337 END;
1338
1339 l_nw_items_list(l_ctr).WORK_ITEM_ID := l_dist_nw_item.WORK_ITEM_ID;
1340 l_nw_items_list(l_ctr).WORKITEM_OBJ_CODE := l_dist_nw_item.WORKITEM_OBJ_CODE;
1341 l_nw_items_list(l_ctr).WORKITEM_PK_ID := l_dist_nw_item.WORKITEM_PK_ID;
1342 l_nw_items_list(l_ctr).STATUS_ID := l_dist_nw_item.STATUS_ID;
1343 l_nw_items_list(l_ctr).PRIORITY_CODE := l_priority_code;
1344 l_nw_items_list(l_ctr).DUE_DATE := l_dist_nw_item.DUE_DATE;
1345 l_nw_items_list(l_ctr).TITLE := l_dist_nw_item.TITLE;
1346 l_nw_items_list(l_ctr).PARTY_ID := l_dist_nw_item.PARTY_ID;
1347 l_nw_items_list(l_ctr).OWNER_ID := l_dist_nw_item.OWNER_ID;
1348 l_nw_items_list(l_ctr).OWNER_TYPE := l_dist_nw_item.OWNER_TYPE;
1349 l_nw_items_list(l_ctr).ASSIGNEE_ID := l_dist_nw_item.ASSIGNEE_ID;
1350 l_nw_items_list(l_ctr).ASSIGNEE_TYPE := l_dist_nw_item.ASSIGNEE_TYPE;
1351 l_nw_items_list(l_ctr).SOURCE_OBJECT_ID := l_dist_nw_item.SOURCE_OBJECT_ID;
1352 l_nw_items_list(l_ctr).SOURCE_OBJECT_TYPE_CODE := l_dist_nw_item.SOURCE_OBJECT_TYPE_CODE;
1353 l_nw_items_list(l_ctr).APPLICATION_ID := l_dist_nw_item.APPLICATION_ID;
1354 l_nw_items_list(l_ctr).IEU_ENUM_TYPE_UUID := l_dist_nw_item.IEU_ENUM_TYPE_UUID;
1355 l_nw_items_list(l_ctr).WORK_ITEM_NUMBER := l_dist_nw_item.WORK_ITEM_NUMBER;
1356 l_nw_items_list(l_ctr).RESCHEDULE_TIME := l_dist_nw_item.RESCHEDULE_TIME;
1357 l_nw_items_list(l_ctr).WS_ID := l_dist_nw_item.WS_ID;
1358
1359 l_ctr := l_ctr + 1;
1360
1361 END LOOP;
1362 -- CLOSE l_dist_wr_cur;
1363
1364
1365 DBMS_SQL.CLOSE_CURSOR(cursor_id);
1366 COMMIT;
1367
1368 -- Check if there are any Distributed Items for this resource
1369 if (l_nw_items_list.COUNT = 0)
1370 then
1371 -- no Distributable Work items
1372 l_dist_items_flag := 'N';
1373 end if;
1374
1375
1376 --insert into p_temp(msg) values ('l_dist_items_flag: '||l_dist_items_flag ||' l_del_items_flag: '||l_del_items_flag ); commit;
1377
1378
1379 --- *** Check if Work Item is Distributed OR Distributable Sorted by Pty_level and Due Date *** ---
1380
1381 -- Sort the Work Items (Distributed, Distributable) base on pty and due date
1382 -- Set the l_delivery_only_flag to 'Y' if Distribution is not required, 'N'if Distributionb may be required
1383 -- '-1' if No Distributable or Distributed Items are present
1384
1385 if (nvl(l_dist_items_flag, 'Y') = 'Y') AND (nvl(l_del_items_flag, 'Y') = 'Y')
1386 then
1387 l_delivery_only_flag := 'N';
1388 elsif (nvl(l_dist_items_flag,'Y') = 'N') AND (nvl(l_del_items_flag,'Y') = 'Y')
1389 then
1390 l_delivery_only_flag := 'Y';
1391 elsif (nvl(l_dist_items_flag,'Y') = 'Y') AND (nvl(l_del_items_flag,'Y') = 'N')
1392 then
1393 l_delivery_only_flag := 'N';
1394 elsif (nvl(l_dist_items_flag,'Y') = 'N') AND (nvl(l_del_items_flag,'Y') = 'N')
1395 then
1396 l_delivery_only_flag := '-1';
1397 raise fnd_api.g_exc_error;
1398 end if; /* Check to see if Distributed or Distributable items are present */
1399
1400 -- Audit Logging
1401 if (l_audit_log_val = 'DETAILED')
1402 then
1403 if ( l_delivery_only_flag = 'Y' )
1404 then
1405 l_ieu_comment_code2 := 'DELIVERY_ONLY';
1406 end if;
1407 end if;/* Audit Log Val is detailed */
1408
1409 -- insert into p_temp(msg) values(' l_delivery_only_flag : '||l_delivery_only_flag );
1410
1411
1412 --- *** Process Distribution/Delivery *** --
1413
1414 if (l_delivery_only_flag = 'Y')
1415 then
1416
1417 -- Workitem is disrtibuted for this resource
1418 -- Copy the Work item data from l_del_nw_item to table of rec - x_uwqm_workitem_data
1419
1420 --dbms_output.put_line('Delivery Only');
1421
1422
1423
1424 IEU_UWQ_GET_NEXT_WORK_PVT.SET_DIST_AND_DEL_ITEM_DATA_REC(p_var_in_type_code => 'REC',
1425 p_dist_workitem_data => null,
1426 p_dist_del_workitem_data => l_del_nw_item,
1427 x_ctr => l_ctr,
1428 x_workitem_action_data => x_uwqm_workitem_data);
1429
1430 l_num_of_items_distributed := 1;
1431
1432 if x_uwqm_workitem_data.count > 0
1433 then
1434 for j in x_uwqm_workitem_data.first .. x_uwqm_workitem_data.last
1435 loop
1436 if (x_uwqm_workitem_data(j).param_name = 'WORKITEM_PK_ID')
1437 then
1438 l_workitem_pk_id := x_uwqm_workitem_data(j).param_value;
1439 end if;
1440 if (x_uwqm_workitem_data(j).param_name = 'WORKITEM_OBJ_CODE')
1441 then
1442 l_workitem_obj_code := x_uwqm_workitem_data(j).param_value;
1443 end if;
1444 if (x_uwqm_workitem_data(j).param_name = 'PRIORITY_ID')
1445 then
1446 l_priority_id := x_uwqm_workitem_data(j).param_value;
1447 end if;
1448 if (x_uwqm_workitem_data(j).param_name = 'DUE_DATE')
1449 then
1450 l_due_date := x_uwqm_workitem_data(j).param_value;
1451 end if;
1452 if (x_uwqm_workitem_data(j).param_name = 'OWNER_ID')
1453 then
1454 l_owner_id := x_uwqm_workitem_data(j).param_value;
1455 end if;
1456 if (x_uwqm_workitem_data(j).param_name = 'OWNER_TYPE')
1457 then
1458 l_owner_type := x_uwqm_workitem_data(j).param_value;
1459 end if;
1460 if (x_uwqm_workitem_data(j).param_name = 'ASSIGNEE_ID')
1461 then
1462 l_assignee_id := x_uwqm_workitem_data(j).param_value;
1463 end if;
1464 if (x_uwqm_workitem_data(j).param_name = 'ASSIGNEE_TYPE')
1465 then
1466 l_assignee_type := x_uwqm_workitem_data(j).param_value;
1467 end if;
1468 if (x_uwqm_workitem_data(j).param_name = 'SOURCE_OBJECT_ID')
1469 then
1470 l_source_object_id := x_uwqm_workitem_data(j).param_value;
1471 end if;
1472 if (x_uwqm_workitem_data(j).param_name = 'SOURCE_OBJECT_TYPE_CODE')
1473 then
1474 l_source_object_type_code := x_uwqm_workitem_data(j).param_value;
1475 end if;
1476 if (x_uwqm_workitem_data(j).param_name = 'STATUS_ID')
1477 then
1478 l_status_id := x_uwqm_workitem_data(j).param_value;
1479 end if;
1480
1481
1482 end loop;
1483 end if;
1484
1485 if ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') )
1486 then
1487 l_event_key := 'DELIVER';
1488 else
1489 l_event_key := null;
1490 end if;
1491 l_module := 'IEU_GET_NEXT_WORK_PVT.DISTRIBUTE_AND_DELIVER';
1492 l_application_id := 696;
1493 l_ws_code := null;
1494 l_ret_sts := 'S';
1495
1496 BEGIN
1497 select ws_code
1498 into l_ws_code
1499 from ieu_uwqm_work_sources_b
1500 where ws_id = l_del_nw_item.ws_id;
1501 EXCEPTION
1502 WHEN OTHERS THEN
1503 l_ws_code := '';
1504 END;
1505
1506 if ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') OR (l_audit_log_val = 'MINIMAL') )
1507 then
1508
1509 BEGIN
1510
1511 select reschedule_time, distribution_status_id
1512 into l_reschedule_time, l_distribution_status_id
1513 from ieu_uwqm_items
1514 where workitem_pk_id = l_workitem_pk_id
1515 and workitem_obj_code = l_workitem_obj_code;
1516
1517 EXCEPTION
1518 when others then
1519 null;
1520 END;
1521
1522 IEU_UWQM_AUDIT_LOG_PKG.INSERT_ROW
1523 (
1524 P_ACTION_KEY => l_action_key,
1525 P_EVENT_KEY => l_event_key,
1526 P_MODULE => l_module,
1527 P_WS_CODE => l_ws_code,
1528 P_APPLICATION_ID => l_application_id,
1529 P_WORKITEM_PK_ID => l_workitem_pk_id,
1530 P_WORKITEM_OBJ_CODE => l_workitem_obj_code,
1531 P_WORK_ITEM_STATUS_PREV => l_status_id,
1532 P_WORK_ITEM_STATUS_CURR => l_status_id,
1533 P_OWNER_ID_PREV => l_owner_id,
1534 P_OWNER_ID_CURR => l_owner_id,
1535 P_OWNER_TYPE_PREV => l_owner_type,
1536 P_OWNER_TYPE_CURR => l_owner_type,
1537 P_ASSIGNEE_ID_PREV => l_assignee_id,
1538 P_ASSIGNEE_ID_CURR => l_assignee_id,
1539 P_ASSIGNEE_TYPE_PREV => l_assignee_type,
1540 P_ASSIGNEE_TYPE_CURR => l_assignee_type,
1541 P_SOURCE_OBJECT_ID_PREV => l_source_object_id,
1542 P_SOURCE_OBJECT_ID_CURR => l_source_object_id,
1543 P_SOURCE_OBJECT_TYPE_CODE_PREV => l_source_object_type_code,
1544 P_SOURCE_OBJECT_TYPE_CODE_CURR => l_source_object_type_code,
1545 P_PARENT_WORKITEM_STATUS_PREV => null,
1546 P_PARENT_WORKITEM_STATUS_CURR => null,
1547 P_PARENT_DIST_STATUS_PREV => null,
1548 P_PARENT_DIST_STATUS_CURR => null,
1549 P_WORKITEM_DIST_STATUS_PREV => l_distribution_status_id,
1550 P_WORKITEM_DIST_STATUS_CURR => l_distribution_status_id,
1551 P_PRIORITY_PREV => l_priority_id,
1552 P_PRIORITY_CURR => l_priority_id,
1553 P_DUE_DATE_PREV => l_due_date,
1554 P_DUE_DATE_CURR => l_due_date,
1555 P_RESCHEDULE_TIME_PREV => l_reschedule_time,
1556 P_RESCHEDULE_TIME_CURR => l_reschedule_time,
1557 P_IEU_COMMENT_CODE1 => l_ieu_comment_code1,
1558 P_IEU_COMMENT_CODE2 => l_ieu_comment_code2,
1559 P_IEU_COMMENT_CODE3 => l_ieu_comment_code3,
1560 P_IEU_COMMENT_CODE4 => l_ieu_comment_code4,
1561 P_IEU_COMMENT_CODE5 => l_ieu_comment_code5,
1562 P_WORKITEM_COMMENT_CODE1 => l_workitem_comment_code1,
1563 P_WORKITEM_COMMENT_CODE2 => l_workitem_comment_code2,
1564 P_WORKITEM_COMMENT_CODE3 => l_workitem_comment_code3,
1565 P_WORKITEM_COMMENT_CODE4 => l_workitem_comment_code4,
1566 P_WORKITEM_COMMENT_CODE5 => l_workitem_comment_code5,
1567 P_STATUS => 'S',
1568 P_ERROR_CODE => l_msg_data,
1569 X_AUDIT_LOG_ID => l_audit_log_id,
1570 X_MSG_DATA => l_msg_data,
1571 X_RETURN_STATUS => l_ret_sts); commit;
1572
1573 end if;
1574
1575 elsif (l_delivery_only_flag = 'N')
1576 then
1577
1578 -- Loop thru all Distributable Items
1579
1580 for z in l_nw_items_list.first .. l_nw_items_list.last
1581 loop
1582
1583 -- Select the Work Items with highest pty, due date
1584 -- l_dist_flag = 'Y' if Distribution is required.
1585
1586 if (nvl(l_del_items_flag,'Y') = 'N')
1587 then
1588
1589 l_dist_flag := 'Y';
1590
1591 else
1592
1593 --insert into p_temp(msg) values('Attempting Dist for ID: '||l_nw_items_list(z).workitem_pk_id); commit;
1594 BEGIN
1595 select priority_level
1596 into l_priority_level
1597 from ieu_uwqm_priorities_b
1598 where priority_code = l_nw_items_list(z).priority_code;
1599 EXCEPTION
1600 WHEN OTHERS THEN
1601 null;
1602 END;
1603
1604 --insert into p_temp(msg) values('dist pty lvl: '||l_priority_level);
1605 --insert into p_temp(msg) values(' due date: '||l_nw_items_list(z).due_date);
1606 --insert into p_temp(msg) values('del pty lvl: '||l_del_nw_item.priority_level||' due date: '||l_del_nw_item.due_date );
1607
1608 if (l_priority_level < l_del_nw_item.priority_level)
1609 then
1610 l_dist_flag := 'Y';
1611 elsif (l_priority_level > l_del_nw_item.priority_level)
1612 then
1613 l_dist_flag := 'N';
1614 elsif (l_priority_level = l_del_nw_item.priority_level)
1615 then
1616 if (l_nw_items_list(z).due_date is null) and (l_del_nw_item.due_date is null)
1617 then
1618 l_dist_flag := 'N';
1619 elsif (l_nw_items_list(z).due_date is null) and (l_del_nw_item.due_date is not null)
1620 then
1621 l_dist_flag := 'N';
1622 elsif (l_nw_items_list(z).due_date is not null) and (l_del_nw_item.due_date is null)
1623 then
1624 l_dist_flag := 'Y';
1625 elsif (l_nw_items_list(z).due_date < l_del_nw_item.due_date)
1626 then
1627 l_dist_flag := 'Y';
1628 elsif (l_nw_items_list(z).due_date > l_del_nw_item.due_date)
1629 then
1630 l_dist_flag := 'N';
1631 elsif (l_nw_items_list(z).due_date = l_del_nw_item.due_date)
1632 then
1633 l_dist_flag := 'N';
1634 end if; /*due date */
1635 end if;/* pty_level */
1636
1637 end if; /* (nvl(l_del_items_flag,'Y') = 'N') */
1638
1639 if (l_dist_flag <> 'Y')
1640 then
1641
1642 -- Workitem is disrtibuted for this resource
1643 -- Copy the Work item data from l_del_nw_item to table of rec - x_uwqm_workitem_data
1644
1645 --dbms_output.put_line('Delivery Only');
1646
1647
1648
1649 IEU_UWQ_GET_NEXT_WORK_PVT.SET_DIST_AND_DEL_ITEM_DATA_REC(p_var_in_type_code => 'REC',
1650 p_dist_workitem_data => null,
1651 p_dist_del_workitem_data => l_del_nw_item,
1652 x_ctr => l_ctr,
1653 x_workitem_action_data => x_uwqm_workitem_data);
1654
1655 if x_uwqm_workitem_data.count > 0
1656 then
1657 for j in x_uwqm_workitem_data.first .. x_uwqm_workitem_data.last
1658 loop
1659 if (x_uwqm_workitem_data(j).param_name = 'WORKITEM_PK_ID')
1660 then
1661 l_workitem_pk_id := x_uwqm_workitem_data(j).param_value;
1662 end if;
1663 if (x_uwqm_workitem_data(j).param_name = 'WORKITEM_OBJ_CODE')
1664 then
1665 l_workitem_obj_code := x_uwqm_workitem_data(j).param_value;
1666 end if;
1667 if (x_uwqm_workitem_data(j).param_name = 'PRIORITY_ID')
1668 then
1669 l_priority_id := x_uwqm_workitem_data(j).param_value;
1670 end if;
1671 if (x_uwqm_workitem_data(j).param_name = 'DUE_DATE')
1672 then
1673 l_due_date := x_uwqm_workitem_data(j).param_value;
1674 end if;
1675 if (x_uwqm_workitem_data(j).param_name = 'OWNER_ID')
1676 then
1677 l_owner_id := x_uwqm_workitem_data(j).param_value;
1678 end if;
1679 if (x_uwqm_workitem_data(j).param_name = 'OWNER_TYPE')
1680 then
1681 l_owner_type := x_uwqm_workitem_data(j).param_value;
1682 end if;
1683 if (x_uwqm_workitem_data(j).param_name = 'ASSIGNEE_ID')
1684 then
1685 l_assignee_id := x_uwqm_workitem_data(j).param_value;
1686 end if;
1687 if (x_uwqm_workitem_data(j).param_name = 'ASSIGNEE_TYPE')
1688 then
1689 l_assignee_type := x_uwqm_workitem_data(j).param_value;
1690 end if;
1691 if (x_uwqm_workitem_data(j).param_name = 'SOURCE_OBJECT_ID')
1692 then
1693 l_source_object_id := x_uwqm_workitem_data(j).param_value;
1694 end if;
1695 if (x_uwqm_workitem_data(j).param_name = 'SOURCE_OBJECT_TYPE_CODE')
1696 then
1697 l_source_object_type_code := x_uwqm_workitem_data(j).param_value;
1698 end if;
1699 if (x_uwqm_workitem_data(j).param_name = 'STATUS_ID')
1700 then
1701 l_status_id := x_uwqm_workitem_data(j).param_value;
1702 end if;
1703 end loop;
1704 end if;
1705
1706 l_num_of_items_distributed := 1;
1707 if ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') )
1708 then
1709 l_event_key := 'DELIVER';
1710 else
1711 l_event_key := null;
1712 end if;
1713 l_module := 'IEU_GET_NEXT_WORK_PVT.DISTRIBUTE_AND_DELIVER';
1714 l_application_id := 696;
1715 l_ws_code := null;
1716 l_ret_sts := 'S';
1717
1718 BEGIN
1719 select ws_code
1720 into l_ws_code
1721 from ieu_uwqm_work_sources_b
1722 where ws_id = l_del_nw_item.ws_id;
1723 EXCEPTION
1724 WHEN OTHERS THEN
1725 l_ws_code := '';
1726 END;
1727
1728 --insert into p_temp(msg) values('audit log val: '||l_audit_log_val||' ret sts: '||l_ret_sts ||' ws code: '||l_ws_code);
1729 if ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') OR (l_audit_log_val = 'MINIMAL') )
1730 then
1731
1732 BEGIN
1733
1734 select reschedule_time, distribution_status_id
1735 into l_reschedule_time, l_distribution_status_id
1736 from ieu_uwqm_items
1737 where workitem_pk_id = l_workitem_pk_id
1738 and workitem_obj_code = l_workitem_obj_code;
1739
1740 EXCEPTION
1741 when others then
1742 null;
1743 END;
1744
1745 IEU_UWQM_AUDIT_LOG_PKG.INSERT_ROW
1746 (
1747 P_ACTION_KEY => l_action_key,
1748 P_EVENT_KEY => l_event_key,
1749 P_MODULE => l_module,
1750 P_WS_CODE => l_ws_code,
1751 P_APPLICATION_ID => l_application_id,
1752 P_WORKITEM_PK_ID => l_workitem_pk_id,
1753 P_WORKITEM_OBJ_CODE => l_workitem_obj_code,
1754 P_WORK_ITEM_STATUS_PREV => l_status_id,
1755 P_WORK_ITEM_STATUS_CURR => l_status_id,
1756 P_OWNER_ID_PREV => l_owner_id,
1757 P_OWNER_ID_CURR => l_owner_id,
1758 P_OWNER_TYPE_PREV => l_owner_type,
1759 P_OWNER_TYPE_CURR => l_owner_type,
1760 P_ASSIGNEE_ID_PREV => l_assignee_id,
1761 P_ASSIGNEE_ID_CURR => l_assignee_id,
1762 P_ASSIGNEE_TYPE_PREV => l_assignee_type,
1763 P_ASSIGNEE_TYPE_CURR => l_assignee_type,
1764 P_SOURCE_OBJECT_ID_PREV => l_source_object_id,
1765 P_SOURCE_OBJECT_ID_CURR => l_source_object_id,
1766 P_SOURCE_OBJECT_TYPE_CODE_PREV => l_source_object_type_code,
1767 P_SOURCE_OBJECT_TYPE_CODE_CURR => l_source_object_type_code,
1768 P_PARENT_WORKITEM_STATUS_PREV => null,
1769 P_PARENT_WORKITEM_STATUS_CURR => null,
1770 P_PARENT_DIST_STATUS_PREV => null,
1771 P_PARENT_DIST_STATUS_CURR => null,
1772 P_WORKITEM_DIST_STATUS_PREV => l_distribution_status_id,
1773 P_WORKITEM_DIST_STATUS_CURR => l_distribution_status_id,
1774 P_PRIORITY_PREV => l_priority_id,
1775 P_PRIORITY_CURR => l_priority_id,
1776 P_DUE_DATE_PREV => l_due_date,
1777 P_DUE_DATE_CURR => l_due_date,
1778 P_RESCHEDULE_TIME_PREV => l_reschedule_time,
1779 P_RESCHEDULE_TIME_CURR => l_reschedule_time,
1780 P_IEU_COMMENT_CODE1 => l_ieu_comment_code1,
1781 P_IEU_COMMENT_CODE2 => l_ieu_comment_code2,
1782 P_IEU_COMMENT_CODE3 => l_ieu_comment_code3,
1783 P_IEU_COMMENT_CODE4 => l_ieu_comment_code4,
1784 P_IEU_COMMENT_CODE5 => l_ieu_comment_code5,
1785 P_WORKITEM_COMMENT_CODE1 => l_workitem_comment_code1,
1786 P_WORKITEM_COMMENT_CODE2 => l_workitem_comment_code2,
1787 P_WORKITEM_COMMENT_CODE3 => l_workitem_comment_code3,
1788 P_WORKITEM_COMMENT_CODE4 => l_workitem_comment_code4,
1789 P_WORKITEM_COMMENT_CODE5 => l_workitem_comment_code5,
1790 P_STATUS => 'S',
1791 P_ERROR_CODE => l_msg_data,
1792 X_AUDIT_LOG_ID => l_audit_log_id,
1793 X_MSG_DATA => l_msg_data,
1794 X_RETURN_STATUS => l_ret_sts); commit;
1795
1796
1797 end if;
1798 exit;
1799
1800 elsif (l_dist_flag = 'Y')
1801 then
1802
1803
1804 /************ THIS IS NOT REQUIRED NOW AS WE HAVE TWO DIFFERENT EVENTS FOR DIST AND DELIVER *********
1805 -- Audit Logging
1806 if (l_audit_log_val = 'DETAILED')
1807 then
1808 l_ieu_comment_code2 := 'DISTRIBUTE_AND_DELIVER';
1809 end if;
1810 ******************************************************************************************************/
1811
1812 l_ws_code := '';
1813 --dbms_output.put_line('Distributing for ws..'||l_nw_items_list(z).WS_ID);
1814 l_num_of_items_distributed := 0;
1815
1816 -- Initialize Collection
1817 l_dist_bus_rules := SYSTEM.DIST_BUS_RULES_NST();
1818 l_dist_items := SYSTEM.WR_ITEM_DATA_NST();
1819 --l_dist_workitem_data := SYSTEM.WR_ITEM_DATA_NST();
1820
1821 BEGIN
1822
1823 SELECT WS_B.DISTRIBUTION_FUNCTION ,
1824 WS_A.DIST_ST_BASED_ON_PARENT_FLAG, WS_B.WS_CODE
1825 INTO l_distribution_function,
1826 l_dist_st_based_on_parent_flag, l_ws_code
1827 FROM IEU_UWQM_WORK_SOURCES_B WS_B, IEU_UWQM_WS_ASSCT_PROPS WS_A
1828 WHERE ws_b.ws_id = l_nw_items_list(z).WS_ID
1829 AND ws_b.not_valid_flag = l_not_valid_flag
1830 AND ws_b.ws_id = ws_a.ws_id(+);
1831
1832 EXCEPTION
1833 WHEN OTHERS THEN
1834 null;
1835 END;
1836
1837 if (l_audit_log_val = 'DETAILED')
1838 then
1839 l_ieu_comment_code4 := 'DISTRIBUTION_FUNC '||l_distribution_function;
1840 end if;
1841
1842 l_dist_bus_rules.extend;
1843 l_dist_bus_rules(l_dist_bus_rules.last) := SYSTEM.DIST_BUS_RULES_OBJ ( l_ws_code,
1844 l_distribute_from,
1845 l_distribute_to,
1846 l_DIST_ST_BASED_ON_PARENT_FLAG);
1847 if (l_distribute_from = 'GROUP_OWNED') and
1848 (l_distribute_to = 'INDIVIDUAL_OWNED')
1849 then
1850 l_ieu_comment_code3 := 'GO_IO';
1851 elsif (l_distribute_from = 'GROUP_OWNED') and
1852 (l_distribute_to = 'INDIVIDUAL_ASSIGNED')
1853 then
1854 l_ieu_comment_code3 := 'GO_IA';
1855 elsif (l_distribute_from = 'GROUP_ASSIGNED') and
1856 (l_distribute_to = 'INDIVIDUAL_OWNED')
1857 then
1858 l_ieu_comment_code3 := 'GA_IO';
1859 elsif (l_distribute_from = 'GROUP_ASSIGNED') and
1860 (l_distribute_to = 'INDIVIDUAL_ASSIGNED')
1861 then
1862 l_ieu_comment_code3 := 'GA_IA';
1863 end if;
1864
1865 if (l_nw_items_list(z).STATUS_ID = 0)
1866 then
1867 l_work_item_status := 'OPEN';
1868 elsif (l_nw_items_list(z).STATUS_ID = 3)
1869 then
1870 l_work_item_status := 'CLOSE';
1871 elsif (l_nw_items_list(z).STATUS_ID = 4)
1872 then
1873 l_work_item_status := 'DELETE';
1874 elsif (l_nw_items_list(z).STATUS_ID = 5)
1875 then
1876 l_work_item_status := 'SLEEP';
1877 end if;
1878
1879 --dbms_output.put_line('ws id matches: '||l_nw_items_list(i).ws_id|| ' ID: '||l_nw_items_list(i).WORKITEM_PK_ID);
1880 l_dist_items.extend;
1881 l_dist_items(l_dist_items.last) := SYSTEM.WR_ITEM_DATA_OBJ(l_nw_items_list(z).WORK_ITEM_ID,
1882 l_nw_items_list(z).WORKITEM_OBJ_CODE,
1883 l_nw_items_list(z).WORKITEM_PK_ID,
1884 l_work_item_status,
1885 l_nw_items_list(z).PRIORITY_ID,
1886 l_nw_items_list(z).PRIORITY_LEVEL,
1887 l_nw_items_list(z).PRIORITY_CODE,
1888 l_nw_items_list(z).DUE_DATE,
1889 l_nw_items_list(z).TITLE,
1890 l_nw_items_list(z).PARTY_ID,
1891 l_nw_items_list(z).OWNER_ID,
1892 l_nw_items_list(z).OWNER_TYPE,
1893 l_nw_items_list(z).ASSIGNEE_ID,
1894 l_nw_items_list(z).ASSIGNEE_TYPE,
1895 l_nw_items_list(z).SOURCE_OBJECT_ID,
1896 l_nw_items_list(z).SOURCE_OBJECT_TYPE_CODE,
1897 l_nw_items_list(z).APPLICATION_ID,
1898 l_nw_items_list(z).IEU_ENUM_TYPE_UUID,
1899 l_nw_items_list(z).WORK_ITEM_NUMBER,
1900 l_nw_items_list(z).RESCHEDULE_TIME,
1901 l_ws_code, --l_nw_items_list(i).WS_ID,
1902 null,
1903 null);
1904
1905
1906 --dbms_output.put_line('dist items cnt'||l_dist_items.count);
1907
1908 -- Call the Distribution Function
1909 if (l_dist_items.count > 0)
1910 then
1911 --dbms_output.put_line('calling dist func: '||L_DISTRIBUTION_FUNCTION);
1912
1913 if ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') OR (l_audit_log_val = 'MINIMAL') )
1914 then
1915
1916 for k in l_dist_items.first .. l_dist_items.last
1917 loop
1918
1919 IEU_UWQM_AUDIT_LOG_PKG.INSERT_ROW
1920 (
1921 P_ACTION_KEY => l_action_key,
1922 P_EVENT_KEY => l_event_key,
1923 P_MODULE => l_module,
1924 P_WS_CODE => l_ws_code,
1925 P_APPLICATION_ID => l_dist_items(k).application_id,
1926 P_WORKITEM_PK_ID => l_dist_items(k).workitem_pk_id,
1927 P_WORKITEM_OBJ_CODE => l_dist_items(k).workitem_obj_code,
1928 P_WORK_ITEM_STATUS_PREV => l_status_id,
1929 P_WORK_ITEM_STATUS_CURR => l_status_id,
1930 P_OWNER_ID_PREV => l_dist_items(k).owner_id,
1931 P_OWNER_ID_CURR => l_dist_items(k).owner_id,
1932 P_OWNER_TYPE_PREV => l_dist_items(k).owner_type,
1933 P_OWNER_TYPE_CURR => l_dist_items(k).owner_type,
1934 P_ASSIGNEE_ID_PREV => null,
1935 P_ASSIGNEE_ID_CURR => l_dist_items(k).assignee_id,
1936 P_ASSIGNEE_TYPE_PREV => null,
1937 P_ASSIGNEE_TYPE_CURR => l_dist_items(k).assignee_type,
1938 P_SOURCE_OBJECT_ID_PREV => l_dist_items(k).source_object_id,
1939 P_SOURCE_OBJECT_ID_CURR => l_dist_items(k).source_object_id,
1940 P_SOURCE_OBJECT_TYPE_CODE_PREV => l_dist_items(k).source_object_type_code,
1941 P_SOURCE_OBJECT_TYPE_CODE_CURR => l_dist_items(k).source_object_type_code,
1942 P_PARENT_WORKITEM_STATUS_PREV => null,
1943 P_PARENT_WORKITEM_STATUS_CURR => null,
1944 P_PARENT_DIST_STATUS_PREV => null,
1945 P_PARENT_DIST_STATUS_CURR => null,
1946 P_WORKITEM_DIST_STATUS_PREV => l_distribution_status_id,
1947 P_WORKITEM_DIST_STATUS_CURR => l_distribution_status_id,
1948 P_PRIORITY_PREV => l_dist_items(k).priority_id,
1949 P_PRIORITY_CURR => l_dist_items(k).priority_id,
1950 P_DUE_DATE_PREV => l_dist_items(k).due_date,
1951 P_DUE_DATE_CURR => l_dist_items(k).due_date,
1952 P_RESCHEDULE_TIME_PREV => l_reschedule_time,
1953 P_RESCHEDULE_TIME_CURR => l_reschedule_time,
1954 P_IEU_COMMENT_CODE1 => l_ieu_comment_code1,
1955 P_IEU_COMMENT_CODE2 => l_ieu_comment_code2,
1956 P_IEU_COMMENT_CODE3 => l_ieu_comment_code3,
1957 P_IEU_COMMENT_CODE4 => l_ieu_comment_code4,
1958 P_IEU_COMMENT_CODE5 => l_ieu_comment_code5,
1959 P_WORKITEM_COMMENT_CODE1 => l_workitem_comment_code1,
1960 P_WORKITEM_COMMENT_CODE2 => l_workitem_comment_code2,
1961 P_WORKITEM_COMMENT_CODE3 => l_workitem_comment_code3,
1962 P_WORKITEM_COMMENT_CODE4 => l_workitem_comment_code4,
1963 P_WORKITEM_COMMENT_CODE5 => l_workitem_comment_code5,
1964 P_STATUS => 'E',
1965 P_ERROR_CODE => x_msg_data,
1966 X_AUDIT_LOG_ID => l_audit_log_id_list(k),
1967 X_MSG_DATA => x_msg_data,
1968 X_RETURN_STATUS => l_ret_sts
1969 );
1970
1971 end loop;
1972 end if;
1973
1974 BEGIN
1975 EXECUTE IMMEDIATE
1976 'BEGIN '|| L_DISTRIBUTION_FUNCTION||'(:1,:2,:3,:4,:5,:6,:7,:8,:9); END;'
1977 USING IN P_RESOURCE_ID, IN P_LANGUAGE, IN P_SOURCE_LANG, IN L_NUM_OF_DIST_ITEMS, IN L_DIST_BUS_RULES, IN OUT L_DIST_ITEMS,
1978 OUT L_MSG_COUNT, OUT L_MSG_DATA, OUT L_RETURN_STATUS;
1979 EXCEPTION
1980 when others then
1981 -- Set the status back from 'Distributing' to 'Distributable'
1982 for k in l_dist_items.first .. l_dist_items.last
1983 loop
1984 l_workitem_pk_id := l_dist_items(k).workitem_pk_id;
1985 l_workitem_obj_code := l_dist_items(k).workitem_obj_code;
1986 l_owner_id := l_dist_items(k).owner_id;
1987 l_owner_type := l_dist_items(k).owner_type;
1988 l_assignee_id := l_dist_items(k).assignee_id;
1989 l_assignee_type := l_dist_items(k).assignee_type;
1990 l_priority_id := l_dist_items(k).priority_id;
1991 l_due_date := l_dist_items(k).due_date;
1992 l_source_object_id := l_dist_items(k).source_object_id;
1993 l_source_object_type_code := l_dist_items(k).source_object_type_code;
1994
1995 if (l_dist_items(k).work_item_status = 'OPEN')
1996 then
1997 l_status_id := 0;
1998 elsif (l_dist_items(k).work_item_status = 'CLOSE')
1999 then
2000 l_status_id := 3;
2001 elsif (l_dist_items(k).work_item_status = 'DELETE')
2002 then
2003 l_status_id := 4;
2004 elsif (l_dist_items(k).work_item_status = 'SLEEP')
2005 then
2006 l_status_id := 5;
2007 end if;
2008
2009 update ieu_uwqm_items
2010 set distribution_status_id = 1
2011 where work_item_id = l_dist_items(k).work_item_id;
2012 commit;
2013
2014 if ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') )
2015 then
2016 l_event_key := 'DISTRIBUTE';
2017 else
2018 l_event_key := null;
2019 end if;
2020 l_module := 'IEU_GET_NEXT_WORK_PVT.DISTRIBUTE_AND_DELIVER';
2021 l_application_id := 696;
2022 l_ret_sts := 'E';
2023
2024 FND_MSG_PUB.INITIALIZE;
2025 FND_MESSAGE.SET_NAME('IEU', 'IEU_SQL_ERROR');
2026 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME','IEU_GET_NEXT_WORK_PVT.DISTRIBUTE_AND_DELIVER');
2027 FND_MESSAGE.SET_TOKEN('SQL_ERROR_MSG',l_token_str);
2028 fnd_msg_pub.ADD;
2029
2030 fnd_msg_pub.Count_and_Get
2031 (
2032 p_count => x_msg_count,
2033 p_data => x_msg_data
2034 );
2035
2036
2037 if ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') OR (l_audit_log_val = 'MINIMAL') )
2038 then
2039 BEGIN
2040
2041 select reschedule_time, distribution_status_id, priority_id
2042 into l_reschedule_time, l_distribution_status_id, l_priority_id
2043 from ieu_uwqm_items
2044 where workitem_pk_id = l_workitem_pk_id
2045 and workitem_obj_code = l_workitem_obj_code;
2046
2047 EXCEPTION
2048 when others then
2049 null;
2050 END;
2051
2052
2053 IEU_UWQM_AUDIT_LOG_PKG.UPDATE_ROW
2054 (
2055 P_AUDIT_LOG_ID => l_audit_log_id_list(k),
2056 P_ACTION_KEY => l_action_key,
2057 P_EVENT_KEY => l_event_key,
2058 P_MODULE => l_module,
2059 P_WS_CODE => l_ws_code,
2060 P_APPLICATION_ID => l_application_id,
2061 P_WORKITEM_PK_ID => l_workitem_pk_id,
2062 P_WORKITEM_OBJ_CODE => l_workitem_obj_code,
2063 P_WORK_ITEM_STATUS_PREV => l_status_id,
2064 P_WORK_ITEM_STATUS_CURR => l_status_id,
2065 P_OWNER_ID_PREV => l_owner_id,
2066 P_OWNER_ID_CURR => l_owner_id,
2067 P_OWNER_TYPE_PREV => l_owner_type,
2068 P_OWNER_TYPE_CURR => l_owner_type,
2069 P_ASSIGNEE_ID_PREV => l_assignee_id,
2070 P_ASSIGNEE_ID_CURR => l_assignee_id,
2071 P_ASSIGNEE_TYPE_PREV => l_assignee_type,
2072 P_ASSIGNEE_TYPE_CURR => l_assignee_type,
2073 P_SOURCE_OBJECT_ID_PREV => l_source_object_id,
2074 P_SOURCE_OBJECT_ID_CURR => l_source_object_id,
2075 P_SOURCE_OBJECT_TYPE_CODE_PREV => l_source_object_type_code,
2076 P_SOURCE_OBJECT_TYPE_CODE_CURR => l_source_object_type_code,
2077 P_PARENT_WORKITEM_STATUS_PREV => null,
2078 P_PARENT_WORKITEM_STATUS_CURR => null,
2079 P_PARENT_DIST_STATUS_PREV => null,
2080 P_PARENT_DIST_STATUS_CURR => null,
2081 P_WORKITEM_DIST_STATUS_PREV => l_distribution_status_id,
2082 P_WORKITEM_DIST_STATUS_CURR => l_distribution_status_id,
2083 P_PRIORITY_PREV => l_priority_id,
2084 P_PRIORITY_CURR => l_priority_id,
2085 P_DUE_DATE_PREV => l_due_date,
2086 P_DUE_DATE_CURR => l_due_date,
2087 P_RESCHEDULE_TIME_PREV => l_reschedule_time,
2088 P_RESCHEDULE_TIME_CURR => l_reschedule_time,
2089 P_IEU_COMMENT_CODE1 => l_ieu_comment_code1,
2090 P_IEU_COMMENT_CODE2 => l_ieu_comment_code2,
2091 P_IEU_COMMENT_CODE3 => l_ieu_comment_code3,
2092 P_IEU_COMMENT_CODE4 => l_ieu_comment_code4,
2093 P_IEU_COMMENT_CODE5 => l_ieu_comment_code5,
2094 P_WORKITEM_COMMENT_CODE1 => l_workitem_comment_code1,
2095 P_WORKITEM_COMMENT_CODE2 => l_workitem_comment_code2,
2096 P_WORKITEM_COMMENT_CODE3 => l_workitem_comment_code3,
2097 P_WORKITEM_COMMENT_CODE4 => l_workitem_comment_code4,
2098 P_WORKITEM_COMMENT_CODE5 => l_workitem_comment_code5,
2099 P_STATUS => 'E',
2100 P_ERROR_CODE => x_msg_data); commit;
2101
2102 end if;
2103
2104 end loop;
2105 END;
2106
2107 -- Check the # of items distributed
2108 -- l_dist_items will contain only 1 item. This loop was required just to retrieve the values
2109 -- instead or hardcoding 0/1
2110
2111 for j in l_dist_items.FIRST..l_dist_items.LAST
2112 loop
2113
2114 l_workitem_pk_id := l_dist_items(j).workitem_pk_id;
2115 l_workitem_obj_code := l_dist_items(j).workitem_obj_code;
2116 l_owner_id := l_dist_items(j).owner_id;
2117 l_owner_type := l_dist_items(j).owner_type;
2118 l_assignee_id := l_dist_items(j).assignee_id;
2119 l_assignee_type := l_dist_items(j).assignee_type;
2120 l_priority_id := l_dist_items(j).priority_id;
2121 l_due_date := l_dist_items(j).due_date;
2122 l_source_object_id := l_dist_items(j).source_object_id;
2123 l_source_object_type_code := l_dist_items(j).source_object_type_code;
2124 if (l_dist_items(j).work_item_status = 'OPEN')
2125 then
2126 l_status_id := 0;
2127 elsif (l_dist_items(j).work_item_status = 'CLOSE')
2128 then
2129 l_status_id := 3;
2130 elsif (l_dist_items(j).work_item_status = 'DELETE')
2131 then
2132 l_status_id := 4;
2133 elsif (l_dist_items(j).work_item_status = 'SLEEP')
2134 then
2135 l_status_id := 5;
2136 end if;
2137
2138
2139 if ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') )
2140 then
2141 l_event_key := 'DISTRIBUTE';
2142 else
2143 l_event_key := null;
2144 end if;
2145 l_module := 'IEU_GET_NEXT_WORK_PVT.DISTRIBUTE_AND_DELIVER';
2146 l_application_id := 696;
2147
2148 if (l_dist_items(j).DISTRIBUTED = 'TRUE')
2149 then
2150 l_audit_log_sts := 'S';
2151 else
2152 l_audit_log_sts := 'E';
2153 end if;
2154
2155 if ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') OR (l_audit_log_val = 'MINIMAL') )
2156 then
2157
2158 BEGIN
2159
2160 select reschedule_time, distribution_status_id, priority_id
2161 into l_reschedule_time, l_distribution_status_id, l_priority_id
2162 from ieu_uwqm_items
2163 where workitem_pk_id = l_workitem_pk_id
2164 and workitem_obj_code = l_workitem_obj_code;
2165
2166 EXCEPTION
2167 when others then
2168 null;
2169 END;
2170
2171
2172 IEU_UWQM_AUDIT_LOG_PKG.UPDATE_ROW
2173 (
2174 P_AUDIT_LOG_ID => l_audit_log_id_list(j),
2175 P_ACTION_KEY => l_action_key,
2176 P_EVENT_KEY => l_event_key,
2177 P_MODULE => l_module,
2178 P_WS_CODE => l_ws_code,
2179 P_APPLICATION_ID => l_application_id,
2180 P_WORKITEM_PK_ID => l_workitem_pk_id,
2181 P_WORKITEM_OBJ_CODE => l_workitem_obj_code,
2182 P_WORK_ITEM_STATUS_PREV => l_status_id,
2183 P_WORK_ITEM_STATUS_CURR => l_status_id,
2184 P_OWNER_ID_PREV => l_owner_id,
2185 P_OWNER_ID_CURR => l_owner_id,
2186 P_OWNER_TYPE_PREV => l_owner_type,
2187 P_OWNER_TYPE_CURR => l_owner_type,
2188 P_ASSIGNEE_ID_PREV => l_assignee_id,
2189 P_ASSIGNEE_ID_CURR => l_assignee_id,
2190 P_ASSIGNEE_TYPE_PREV => l_assignee_type,
2191 P_ASSIGNEE_TYPE_CURR => l_assignee_type,
2192 P_SOURCE_OBJECT_ID_PREV => l_source_object_id,
2193 P_SOURCE_OBJECT_ID_CURR => l_source_object_id,
2194 P_SOURCE_OBJECT_TYPE_CODE_PREV => l_source_object_type_code,
2195 P_SOURCE_OBJECT_TYPE_CODE_CURR => l_source_object_type_code,
2196 P_PARENT_WORKITEM_STATUS_PREV => null,
2197 P_PARENT_WORKITEM_STATUS_CURR => null,
2198 P_PARENT_DIST_STATUS_PREV => null,
2199 P_PARENT_DIST_STATUS_CURR => null,
2200 P_WORKITEM_DIST_STATUS_PREV => 1,
2201 P_WORKITEM_DIST_STATUS_CURR => l_distribution_status_id,
2202 P_PRIORITY_PREV => l_priority_id,
2203 P_PRIORITY_CURR => l_priority_id,
2204 P_DUE_DATE_PREV => l_due_date,
2205 P_DUE_DATE_CURR => l_due_date,
2206 P_RESCHEDULE_TIME_PREV => l_reschedule_time,
2207 P_RESCHEDULE_TIME_CURR => l_reschedule_time,
2208 P_IEU_COMMENT_CODE1 => l_ieu_comment_code1,
2209 P_IEU_COMMENT_CODE2 => l_ieu_comment_code2,
2210 P_IEU_COMMENT_CODE3 => l_ieu_comment_code3,
2211 P_IEU_COMMENT_CODE4 => l_ieu_comment_code4,
2212 P_IEU_COMMENT_CODE5 => l_ieu_comment_code5,
2213 P_WORKITEM_COMMENT_CODE1 => l_workitem_comment_code1,
2214 P_WORKITEM_COMMENT_CODE2 => l_workitem_comment_code2,
2215 P_WORKITEM_COMMENT_CODE3 => l_workitem_comment_code3,
2216 P_WORKITEM_COMMENT_CODE4 => l_workitem_comment_code4,
2217 P_WORKITEM_COMMENT_CODE5 => l_workitem_comment_code5,
2218 P_STATUS => l_audit_log_sts,
2219 P_ERROR_CODE => l_msg_data);commit;
2220
2221 end if;
2222
2223 if (l_dist_items(j).DISTRIBUTED = 'TRUE')
2224 then
2225 IF (l_dist_items(j).WORK_ITEM_STATUS is not null)
2226 THEN
2227 IF (l_dist_items(j).WORK_ITEM_STATUS = 'OPEN')
2228 THEN
2229 l_work_item_status_id := 0;
2230 ELSIF (l_dist_items(j).WORK_ITEM_STATUS = 'CLOSE')
2231 THEN
2232 l_work_item_status_id := 3;
2233 ELSIF (l_dist_items(j).WORK_ITEM_STATUS = 'DELETE')
2234 THEN
2235 l_work_item_status_id := 4;
2236 ELSIF (l_dist_items(j).WORK_ITEM_STATUS = 'SLEEP')
2237 THEN
2238 l_work_item_status_id := 5;
2239 END IF;
2240 END IF;
2241
2242 --dbms_output.put_line('dist status set to TRUE work item pkid: '||l_dist_items(j).WORKITEM_PK_ID);
2243
2244 l_num_of_items_distributed := l_num_of_items_distributed + 1;
2245 -- Update the same object
2246 l_dist_items(l_dist_items.LAST) := SYSTEM.WR_ITEM_DATA_OBJ(l_dist_items(j).WORK_ITEM_ID,
2247 l_dist_items(j).WORKITEM_OBJ_CODE,
2248 l_dist_items(j).WORKITEM_PK_ID,
2249 l_work_item_status_id,
2250 l_dist_items(j).PRIORITY_ID,
2251 l_dist_items(j).PRIORITY_LEVEL,
2252 l_dist_items(j).PRIORITY_CODE,
2253 l_dist_items(j).DUE_DATE,
2254 l_dist_items(j).TITLE,
2255 l_dist_items(j).PARTY_ID,
2256 l_dist_items(j).OWNER_ID,
2257 l_dist_items(j).OWNER_TYPE,
2258 l_dist_items(j).ASSIGNEE_ID,
2259 l_dist_items(j).ASSIGNEE_TYPE,
2260 l_dist_items(j).SOURCE_OBJECT_ID,
2261 l_dist_items(j).SOURCE_OBJECT_TYPE_CODE,
2262 l_dist_items(j).APPLICATION_ID,
2263 l_dist_items(j).IEU_ENUM_TYPE_UUID,
2264 l_dist_items(j).WORK_ITEM_NUMBER,
2265 l_dist_items(j).RESCHEDULE_TIME,
2266 l_dist_items(j).WORK_SOURCE,
2267 l_dist_items(j).DISTRIBUTED,
2268 l_dist_items(j).ITEM_INCLUDED_BY_APP);
2269
2270 -- If a work item was distributed, copy the Work Item data from table of obj - l_dist_workitem_data
2271 -- to table of Rec - x_uwqm_workitem_data
2272
2273 IEU_UWQ_GET_NEXT_WORK_PVT.SET_DIST_AND_DEL_ITEM_DATA_REC(p_var_in_type_code => 'OBJ',
2274 -- p_dist_workitem_data => l_dist_workitem_data,
2275 p_dist_workitem_data => l_dist_items,
2276 p_dist_del_workitem_data => null,
2277 x_ctr => l_ctr,
2278 x_workitem_action_data => x_uwqm_workitem_data);
2279
2280 /********************************* Added New Event Deliver *****************************/
2281
2282
2283 if ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') )
2284 then
2285 l_event_key := 'DELIVER';
2286 else
2287 l_event_key := null;
2288 end if;
2289 l_module := 'IEU_GET_NEXT_WORK_PVT.DISTRIBUTE_AND_DELIVER';
2290 l_application_id := 696;
2291
2292 l_audit_log_sts := 'S';
2293 l_ieu_comment_code1 := null;
2294 l_ieu_comment_code2 := null;
2295 l_ieu_comment_code3 := null;
2296 l_ieu_comment_code4 := null;
2297 l_ieu_comment_code5 := null;
2298
2299 if ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') OR (l_audit_log_val = 'MINIMAL') )
2300 then
2301
2302 BEGIN
2303
2304 select reschedule_time, distribution_status_id, priority_id
2305 into l_reschedule_time, l_distribution_status_id, l_priority_id
2306 from ieu_uwqm_items
2307 where workitem_pk_id = l_dist_items(j).workitem_pk_id
2308 and workitem_obj_code = l_dist_items(j).workitem_obj_code;
2309
2310 EXCEPTION
2311 when others then
2312 null;
2313 END;
2314
2315 IEU_UWQM_AUDIT_LOG_PKG.INSERT_ROW
2316 (
2317 P_ACTION_KEY => l_action_key,
2318 P_EVENT_KEY => l_event_key,
2319 P_MODULE => l_module,
2320 P_WS_CODE => l_ws_code,
2321 P_APPLICATION_ID => l_application_id,
2322 P_WORKITEM_PK_ID =>l_dist_items(j).workitem_pk_id,
2323 P_WORKITEM_OBJ_CODE =>l_dist_items(j).workitem_obj_code,
2324 P_WORK_ITEM_STATUS_PREV =>l_work_item_status_id,
2325 P_WORK_ITEM_STATUS_CURR =>l_work_item_status_id,
2326 P_OWNER_ID_PREV =>l_dist_items(j).owner_id,
2327 P_OWNER_ID_CURR =>l_dist_items(j).owner_id,
2328 P_OWNER_TYPE_PREV =>l_dist_items(j).owner_type,
2329 P_OWNER_TYPE_CURR =>l_dist_items(j).owner_type,
2330 P_ASSIGNEE_ID_PREV =>l_dist_items(j).assignee_id,
2331 P_ASSIGNEE_ID_CURR =>l_dist_items(j).assignee_id,
2332 P_ASSIGNEE_TYPE_PREV =>l_dist_items(j).assignee_type,
2333 P_ASSIGNEE_TYPE_CURR =>l_dist_items(j).assignee_type,
2334 P_SOURCE_OBJECT_ID_PREV =>l_dist_items(j).source_object_id,
2335 P_SOURCE_OBJECT_ID_CURR =>l_dist_items(j).source_object_id,
2336 P_SOURCE_OBJECT_TYPE_CODE_PREV =>l_dist_items(j).source_object_type_code,
2337 P_SOURCE_OBJECT_TYPE_CODE_CURR =>l_dist_items(j).source_object_type_code,
2338 P_PARENT_WORKITEM_STATUS_PREV => null,
2339 P_PARENT_WORKITEM_STATUS_CURR => null,
2340 P_PARENT_DIST_STATUS_PREV => null,
2341 P_PARENT_DIST_STATUS_CURR => null,
2342 P_WORKITEM_DIST_STATUS_PREV => l_distribution_status_id,
2343 P_WORKITEM_DIST_STATUS_CURR => l_distribution_status_id,
2344 P_PRIORITY_PREV => l_priority_id,
2345 P_PRIORITY_CURR => l_priority_id,
2346 P_DUE_DATE_PREV =>l_dist_items(j).due_date,
2347 P_DUE_DATE_CURR =>l_dist_items(j).due_date,
2348 P_RESCHEDULE_TIME_PREV => l_reschedule_time,
2349 P_RESCHEDULE_TIME_CURR => l_reschedule_time,
2350 P_IEU_COMMENT_CODE1 => l_ieu_comment_code1,
2351 P_IEU_COMMENT_CODE2 => l_ieu_comment_code2,
2352 P_IEU_COMMENT_CODE3 => l_ieu_comment_code3,
2353 P_IEU_COMMENT_CODE4 => l_ieu_comment_code4,
2354 P_IEU_COMMENT_CODE5 => l_ieu_comment_code5,
2355 P_WORKITEM_COMMENT_CODE1 => l_workitem_comment_code1,
2356 P_WORKITEM_COMMENT_CODE2 => l_workitem_comment_code2,
2357 P_WORKITEM_COMMENT_CODE3 => l_workitem_comment_code3,
2358 P_WORKITEM_COMMENT_CODE4 => l_workitem_comment_code4,
2359 P_WORKITEM_COMMENT_CODE5 => l_workitem_comment_code5,
2360 P_STATUS => l_audit_log_sts,
2361 P_ERROR_CODE => l_msg_data,
2362 X_AUDIT_LOG_ID => l_audit_log_id,
2363 X_MSG_DATA => l_msg_data,
2364 X_RETURN_STATUS => l_ret_sts);commit;
2365
2366 end if;
2367 /***************************************************************************************/
2368 exit;
2369 else
2370 if ((l_dist_items(j).DISTRIBUTED = 'FALSE') and
2371 (l_del_items_flag = 'Y') and
2372 (z = l_nw_items_list.last)) then
2373
2374 if (l_audit_log_val = 'DETAILED')
2375 then
2376 l_ieu_comment_code3 := 'DIST_FAILURE_DELIVERY_ONLY';
2377 end if;/* Audit Log Val is detailed */
2378
2379
2380 IEU_UWQ_GET_NEXT_WORK_PVT.SET_DIST_AND_DEL_ITEM_DATA_REC(p_var_in_type_code => 'REC',
2381 p_dist_workitem_data => null,
2382 p_dist_del_workitem_data => l_del_nw_item,
2383 x_ctr => l_ctr,
2384 x_workitem_action_data => x_uwqm_workitem_data);
2385
2386 l_num_of_items_distributed := 1;
2387 end if;
2388 end if;/* l_dist_items(j).DISTRIBUTED */
2389 end loop;/* l_nw_items.FIRST to LAST */
2390
2391 end if; /* l_dist_items.count > 1 */
2392
2393 end if; /* l_dist_flag */
2394
2395 --dbms_output.put_line('Num of Items Dist: '||l_num_of_items_distributed);
2396
2397 if (l_num_of_items_distributed > 0)
2398 then
2399 --dbms_output.put_line('exiting..');
2400 exit;
2401 end if;
2402
2403
2404 end loop; /*l_nw_items_list.first to last */
2405
2406 end if; /* l_delivery_only_flag */
2407
2408 -- Set the status back to 'Distributable' for the Work Items Selected for Distribution except the Distributed Work Item
2409 -- This check is required here for the following reasons
2410 -- 1. Any Item out of the 5 we are selecting for Distribution can be Distributed. If for eg. the 2nd item is Distributed
2411 -- then the Dist Status for all others should be reset here
2412 -- 2. If No Distribution was done, then the Dist Status should be reset here.
2413
2414 if (x_uwqm_workitem_data.count >= 1)
2415 then
2416 for p in x_uwqm_workitem_data.first .. x_uwqm_workitem_data.last
2417 loop
2418 if (x_uwqm_workitem_data(p).param_name = 'WORK_ITEM_ID')
2419 then
2420 l_dist_work_item_id := x_uwqm_workitem_data(p).param_value;
2421 end if;
2422 end loop;
2423 end if;
2424
2425 if (nvl(l_dist_items_flag,'Y') = 'Y')
2426 then
2427 --dbms_output.put_line('dist flag = Y.. cnt: '||l_nw_items_list.count);
2428 for y in l_nw_items_list.first..l_nw_items_list.last
2429 loop
2430 -- The work_item_id should not be Distributed Work item Id
2431 --dbms_output.put_line('Work item id: '||l_nw_items_list(y).workitem_pk_id);
2432 --dbms_output.put_line('Distributed Work Item Id: '||l_dist_work_item_id );
2433 if (l_nw_items_list(y).work_item_id <> nvl(l_dist_work_item_id,-1))
2434 then
2435 update ieu_uwqm_items
2436 set distribution_status_id = 1
2437 where work_item_id = l_nw_items_list(y).work_item_id;
2438 commit;
2439 end if;
2440 end loop;
2441 end if;
2442
2443 end loop;
2444 --dbms_output.put_line('# of items distributed '||l_num_of_items_distributed );
2445
2446 /*****************
2447 if (x_uwqm_workitem_data.count > 0)
2448 then
2449 for p in x_uwqm_workitem_data.first .. x_uwqm_workitem_data.last
2450 loop
2451 dbms_output.put_line('workitem id: '||x_uwqm_workitem_data(p).work_item_id||' obj code: '||x_uwqm_workitem_data(p).WORKITEM_OBJ_CODE||
2452 ' obj func: '||x_uwqm_workitem_data(p).IEU_OBJECT_FUNCTION ||
2453 ' params: '||x_uwqm_workitem_data(p).IEU_OBJECT_PARAMETERS);
2454 end loop;
2455 end if;
2456 ******************/
2457
2458
2459 if (x_uwqm_workitem_data.count < 1)
2460 then
2461
2462 raise fnd_api.g_exc_error;
2463 end if;
2464 --commit;
2465 EXCEPTION
2466
2467 WHEN fnd_api.g_exc_error THEN
2468 x_return_status := fnd_api.g_ret_sts_error;
2469
2470 fnd_msg_pub.Count_and_Get
2471 (
2472 p_count => x_msg_count,
2473 p_data => x_msg_data
2474 );
2475
2476 WHEN fnd_api.g_exc_unexpected_error THEN
2477
2478 x_return_status := fnd_api.g_ret_sts_unexp_error;
2479
2480 fnd_msg_pub.Count_and_Get
2481 (
2482 p_count => x_msg_count,
2483 p_data => x_msg_data
2484 );
2485
2486 WHEN OTHERS THEN
2487
2488 x_return_status := fnd_api.g_ret_sts_unexp_error;
2489
2490 IF FND_MSG_PUB.Check_msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2491 THEN
2492
2493 fnd_msg_pub.Count_and_Get
2494 (
2495 p_count => x_msg_count,
2496 p_data => x_msg_data
2497 );
2498
2499 END IF;
2500 END DISTRIBUTE_AND_DELIVER_WR_ITEM;
2501
2502 PROCEDURE DISTRIBUTE_WR_ITEMS
2503 ( p_api_version IN NUMBER,
2504 p_resource_id IN NUMBER,
2505 p_language IN VARCHAR2,
2506 p_source_lang IN VARCHAR2,
2507 p_num_of_dist_items IN NUMBER, -- Number of Items Requested to be Distributed
2508 p_extra_where_clause IN VARCHAR2,
2509 p_bindvar_list IN IEU_UWQ_BINDVAR_LIST,
2510 x_uwqm_workitem_data OUT NOCOPY IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WR_ITEM_ACT_DATA_LIST,
2511 x_num_of_items_distributed OUT NOCOPY NUMBER, -- Number of Items finally Distributed
2512 x_msg_count OUT NOCOPY NUMBER,
2513 x_msg_data OUT NOCOPY VARCHAR2,
2514 x_return_status OUT NOCOPY VARCHAR2) IS
2515
2516 -- Used to Validate API version and name
2517 l_api_version CONSTANT NUMBER := 1.0;
2518 l_api_name CONSTANT VARCHAR2(30) := 'DISTRIBUTE_WR_ITEMS';
2519
2520 l_num_of_items_distributed NUMBER := 0; -- Number of Items Distributed
2521 l_dist_workitem_data SYSTEM.WR_ITEM_DATA_NST;
2522 l_dist_items_ctr NUMBER := 1;
2523 l_ctr NUMBER := 0;
2524 l_num_of_dist_items NUMBER := 0; -- Number of Items Requested to be Distributed
2525
2526 l_object_function VARCHAR2(40);
2527 l_object_parameters VARCHAR2(500);
2528 l_enter_from_task VARCHAR2(10);
2529 l_ws_id NUMBER;
2530
2531
2532 BEGIN
2533
2534 x_return_status := fnd_api.g_ret_sts_success;
2535
2536
2537
2538 IF NOT fnd_api.compatible_api_call (
2539 l_api_version,
2540 p_api_version,
2541 l_api_name,
2542 g_pkg_name
2543 )
2544 THEN
2545 RAISE fnd_api.g_exc_unexpected_error;
2546 END IF;
2547
2548 -- Initialize Message list
2549
2550 FND_MSG_PUB.INITIALIZE;
2551
2552 x_num_of_items_distributed := 0;
2553
2554 loop
2555
2556 -- exit when one of the following conditions is satisfied
2557 -- 1. Requested Num of Items are distributed (p_num_of_dist_items - Request num of items to be distributed)
2558 -- 2. No more items in Distributable status (flag 'l_num_of_items_distributed ' will be set to -1)
2559 -- 3. Attempt distribution only 2 times. This is done for performance reasons.
2560
2561 exit when ( (l_num_of_items_distributed >= p_num_of_dist_items) OR
2562 (l_num_of_items_distributed = -1) OR
2563 ( l_dist_items_ctr > 2) OR
2564 (l_num_of_items_distributed > 0) ) ;
2565
2566 l_num_of_dist_items := p_num_of_dist_items - x_num_of_items_distributed;
2567
2568 -- dbms_output.put_line('calling get_next_wr_item..requesting '||l_num_of_dist_items ||' items');
2569
2570 l_dist_deliver_num_of_attempts := l_dist_items_ctr;
2571
2572 IEU_UWQ_GET_NEXT_WORK_PVT.GET_DIST_WR_ITEMS
2573 ( p_api_version => p_api_version,
2574 p_resource_id => p_resource_id,
2575 p_language => p_language,
2576 p_source_lang => p_source_lang,
2577 p_num_of_dist_items => l_num_of_dist_items,
2578 p_extra_where_clause => p_extra_where_clause,
2579 p_bindvar_list => p_bindvar_list,
2580 x_uwqm_workitem_data => l_dist_workitem_data,
2581 x_num_of_items_distributed => l_num_of_items_distributed,
2582 x_msg_count => x_msg_count,
2583 x_msg_data => x_msg_data,
2584 x_return_status => x_return_status);
2585
2586 l_dist_items_ctr := l_dist_items_ctr + 1;
2587
2588 -- If items were distributed, then copy values from table of objects to table of records.
2589 -- Also, set the appropriate values for Object Function, Object params etc.
2590
2591 if (l_num_of_items_distributed <> -1)
2592 then
2593
2594 -- The actual num of items distributed will be the sum of items distributed in each attempt
2595 -- x_num_of_items_distributed - Final num of items distributed
2596 -- l_num_of_items_distributed - Items distributed this time
2597
2598 x_num_of_items_distributed := x_num_of_items_distributed + l_num_of_items_distributed;
2599
2600 IEU_UWQ_GET_NEXT_WORK_PVT.SET_WR_ITEM_DATA_REC(p_var_in_type_code => 'OBJ',
2601 p_dist_workitem_data => l_dist_workitem_data,
2602 p_dist_del_workitem_data => null,
2603 x_ctr => l_ctr,
2604 x_uwqm_workitem_data => x_uwqm_workitem_data);
2605
2606 end if; /* l_num_of_items_distributed <> -1 */
2607
2608 end loop;
2609
2610 --dbms_output.put_line('# of items distributed '||x_num_f_items_distributed ||' cnt: '||x_uwqm_workitem_data.count);
2611
2612
2613 EXCEPTION
2614
2615 WHEN fnd_api.g_exc_error THEN
2616
2617 x_return_status := fnd_api.g_ret_sts_error;
2618
2619 fnd_msg_pub.Count_and_Get
2620 (
2621 p_count => x_msg_count,
2622 p_data => x_msg_data
2623 );
2624
2625 WHEN fnd_api.g_exc_unexpected_error THEN
2626
2627 x_return_status := fnd_api.g_ret_sts_unexp_error;
2628
2629 fnd_msg_pub.Count_and_Get
2630 (
2631 p_count => x_msg_count,
2632 p_data => x_msg_data
2633 );
2634
2635 WHEN OTHERS THEN
2636
2637 x_return_status := fnd_api.g_ret_sts_unexp_error;
2638
2639 IF FND_MSG_PUB.Check_msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2640 THEN
2641
2642 fnd_msg_pub.Count_and_Get
2643 (
2644 p_count => x_msg_count,
2645 p_data => x_msg_data
2646 );
2647
2648 END IF;
2649 END DISTRIBUTE_WR_ITEMS;
2650
2651
2652 /**
2653 ** Used in Proc - Distribute_wr_items, distribute_and_deliver_wr_item
2654 **/
2655
2656 /************* Open issues ***********************
2657 *** 1. Handling multiple bind variables - Restrictions on usage of Ref Cursors/Open-for
2658 *** ex: sql_stmt := 'select .... where owner_id = :resource_id or assignee_id = :resource_id';
2659 *** open cur for sql_stmt using In l_res_id
2660 *** 2. Performance enh#
2661 *** - indexes, loops, proc calls
2662 *** 3. Setting Distributing status back to distributable after 2 attempts
2663 **************************************************/
2664
2665 PROCEDURE GET_DIST_WR_ITEMS
2666 ( p_api_version IN NUMBER,
2667 p_resource_id IN NUMBER,
2668 p_language IN VARCHAR2,
2669 p_source_lang IN VARCHAR2,
2670 p_num_of_dist_items IN NUMBER,
2671 p_extra_where_clause IN VARCHAR2,
2672 p_bindvar_list IN IEU_UWQ_BINDVAR_LIST,
2673 x_uwqm_workitem_data OUT NOCOPY SYSTEM.WR_ITEM_DATA_NST,
2674 x_num_of_items_distributed OUT NOCOPY NUMBER,
2675 x_msg_count OUT NOCOPY NUMBER,
2676 x_msg_data OUT NOCOPY VARCHAR2,
2677 x_return_status OUT NOCOPY VARCHAR2) IS
2678
2679 -- Used to Validate API version and name
2680 l_api_version CONSTANT NUMBER := 1.0;
2681 l_api_name CONSTANT VARCHAR2(30) := 'GET_DIST_WR_ITEMS';
2682
2683
2684 l_sql_stmt VARCHAR2(4000);
2685 l_dist_status NUMBER := 1;
2686 l_open_status_id NUMBER := 0;
2687 l_resource_id NUMBER := 100001713;
2688 l_next_wr_items IEU_UWQ_GET_NEXT_WORK_PVT.l_get_work;
2689
2690
2691 -- Table of records for all OUT variables
2692 l_work_item_num NUMBER;
2693
2694 l_wr_cur IEU_UWQ_GET_NEXT_WORK_PVT.l_get_work;
2695 l_nw_item IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WR_ITEM_DATA_REC := null;
2696 l_nw_items_list IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WR_ITEM_DATA;
2697
2698 l_nw_items_list2 IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WR_ITEM_DATA;
2699 l_nw_ctr number := 1;
2700 z number := 0;
2701
2702 l_num_of_dist_items_incr number := 0;
2703
2704 /*
2705 l_dist_items IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WR_DIST_ITEM_DATA;
2706 l_dist_bus_rules IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WR_DIST_RULES;
2707 */
2708
2709 l_dist_items SYSTEM.WR_ITEM_DATA_NST;
2710 l_dist_bus_rules SYSTEM.DIST_BUS_RULES_NST;
2711
2712 L_MSG_COUNT NUMBER;
2713 L_MSG_DATA VARCHAR2(4000);
2714 L_RETURN_STATUS VARCHAR2(10);
2715 l_ctr NUMBER := 1;
2716 l_curr_ws_id NUMBER;
2717 l_priority_code IEU_UWQM_PRIORITIES_B.PRIORITY_CODE%TYPE;
2718 l_ws_code VARCHAR2(500);
2719 l_work_item_status VARCHAR2(500);
2720 l_work_item_status_id NUMBER;
2721 l_wr_cur_cnt NUMBER;
2722 cursor_id PLS_INTEGER;
2723 dummy PLS_INTEGER;
2724 temp number;
2725
2726
2727 l_not_valid_flag VARCHAR2(1);
2728 cursor c_ws is
2729 select WS_B.WS_ID, 'INDIVIDUAL_ASSIGNED' DISTRIBUTE_TO, 'GROUP_OWNED' DISTRIBUTE_FROM , WS_B.DISTRIBUTION_FUNCTION ,
2730 WS_A.DIST_ST_BASED_ON_PARENT_FLAG, WS_B.WS_CODE
2731 from IEU_UWQM_WORK_SOURCES_B WS_B, IEU_UWQM_WS_ASSCT_PROPS WS_A
2732 where ws_b.not_valid_flag = l_not_valid_flag
2733 and ws_b.ws_id = ws_a.ws_id(+);
2734
2735 -- Audit Trail
2736 l_action_key VARCHAR2(500);
2737 l_event_key VARCHAR2(500);
2738 l_module VARCHAR2(1000);
2739 l_application_id NUMBER;
2740 --l_ws_code VARCHAR2(500);
2741 l_ret_sts VARCHAR2(10);
2742 l_audit_log_val VARCHAR2(100);
2743 l_ieu_comment_code1 VARCHAR2(2000);
2744 l_ieu_comment_code2 VARCHAR2(2000);
2745 l_ieu_comment_code3 VARCHAR2(2000);
2746 l_ieu_comment_code4 VARCHAR2(2000);
2747 l_ieu_comment_code5 VARCHAR2(2000);
2748 l_workitem_comment_code1 VARCHAR2(2000);
2749 l_workitem_comment_code2 VARCHAR2(2000);
2750 l_workitem_comment_code3 VARCHAR2(2000);
2751 l_workitem_comment_code4 VARCHAR2(2000);
2752 l_workitem_comment_code5 VARCHAR2(2000);
2753
2754 l_workitem_pk_id NUMBER;
2755 l_workitem_obj_code VARCHAR2(50);
2756 l_audit_log_sts VARCHAR2(50);
2757 l_owner_id NUMBER;
2758 l_owner_type VARCHAR2(500);
2759 l_assignee_id NUMBER;
2760 l_assignee_type VARCHAR2(500);
2761 l_priority_id NUMBER;
2762 l_due_date DATE;
2763 l_source_object_id NUMBER;
2764 l_source_object_type_code VARCHAR2(500);
2765 l_status_id NUMBER;
2766 l_distribution_status_id NUMBER;
2767 l_reschedule_time DATE;
2768 l_token_str VARCHAR2(4000);
2769 -- l_audit_log_id NUMBER;
2770 TYPE AUDIT_LOG_ID_TBL is TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2771 l_audit_log_id_list AUDIT_LOG_ID_TBL;
2772 v varchar2(1000);
2773 BEGIN
2774 l_not_valid_flag := 'N';
2775 x_return_status := fnd_api.g_ret_sts_success;
2776
2777 l_audit_log_val := FND_PROFILE.VALUE('IEU_WR_DIST_AUDIT_LOG');
2778
2779 IF NOT fnd_api.compatible_api_call (
2780 l_api_version,
2781 p_api_version,
2782 l_api_name,
2783 g_pkg_name
2784 )
2785 THEN
2786 RAISE fnd_api.g_exc_unexpected_error;
2787 END IF;
2788
2789 -- Initialize Message list
2790
2791 FND_MSG_PUB.INITIALIZE;
2792
2793 x_uwqm_workitem_data := SYSTEM.WR_ITEM_DATA_NST();
2794
2795 -- Get the Where Clause
2796 -- IEU_UWQ_GET_NEXT_WORK_PVT.GET_WS_WHERE_CLAUSE('DIST_FROM',l_where_clause);
2797
2798 -- Build the complete select stmt
2799 l_sql_stmt := 'SELECT /*+ first_rows */
2800 WORK_ITEM_ID,
2801 WORKITEM_OBJ_CODE,
2802 WORKITEM_PK_ID,
2803 STATUS_ID,
2804 PRIORITY_ID,
2805 PRIORITY_LEVEL,
2806 null, -- Selecting null for pty code
2807 DUE_DATE,
2808 TITLE,
2809 PARTY_ID,
2810 OWNER_ID,
2811 OWNER_TYPE,
2812 ASSIGNEE_ID,
2813 ASSIGNEE_TYPE,
2814 SOURCE_OBJECT_ID,
2815 SOURCE_OBJECT_TYPE_CODE,
2816 APPLICATION_ID,
2817 IEU_ENUM_TYPE_UUID,
2818 WORK_ITEM_NUMBER,
2819 RESCHEDULE_TIME,
2820 WS_ID
2821 FROM IEU_UWQM_ITEMS '||
2822 ' WHERE ' || ' ( ' ||p_extra_where_clause || ' ) '||
2823 ' AND DISTRIBUTION_STATUS_ID = :l_dist_status' ||
2824 ' AND STATUS_ID = :l_status_id ' ||
2825 ' and reschedule_time <= sysdate ' ||
2826 -- l_where_clause ||' ) '||
2827 -- ' ) AND rownum <= '|| p_num_of_dist_items||
2828 -- ' ) AND rownum <= :p_num_of_dist_items '||
2829 ' order by priority_level, due_date '||
2830 ' for update skip locked ';
2831
2832 -- insert into p_temp(msg) values ('sql- '||l_sql_stmt||' res id : '||p_resource_id||' dist stat: '||l_dist_status||' open stat '||l_open_status_id); commit;
2833
2834 -- Select the items based on Business rules
2835
2836 -- OPEN l_wr_cur FOR l_sql_stmt
2837 -- USING IN l_dist_status, IN l_open_status_id;
2838
2839 cursor_id := dbms_sql.open_cursor;
2840 DBMS_SQL.PARSE(cursor_id, l_sql_stmt, dbms_sql.native);
2841 DBMS_SQL.BIND_VARIABLE(cursor_id,':l_dist_status', l_dist_status);
2842 DBMS_SQL.BIND_VARIABLE(cursor_id,':l_status_id', l_open_status_id);
2843
2844 for i in 1..p_bindvar_list.count loop
2845 DBMS_SQL.BIND_VARIABLE(cursor_id,p_bindvar_list(i).bind_name, p_bindvar_list(i).value);
2846
2847 end loop;
2848
2849
2850 -- USING IN l_dist_status, IN l_open_status_id, IN p_resource_id, IN p_num_of_dist_items;
2851 -- USING IN l_dist_status, IN l_open_status_id, IN p_resource_id;
2852
2853 l_wr_cur_cnt := 1;
2854
2855 DBMS_SQL.DEFINE_COLUMN(cursor_id,1,l_nw_item.WORK_ITEM_ID);
2856 DBMS_SQL.DEFINE_COLUMN(cursor_id,2,l_nw_item.WORKITEM_OBJ_CODE,30);
2857 DBMS_SQL.DEFINE_COLUMN(cursor_id,3,l_nw_item.WORKITEM_PK_ID);
2858 DBMS_SQL.DEFINE_COLUMN(cursor_id,4,l_nw_item.STATUS_ID);
2859 DBMS_SQL.DEFINE_COLUMN(cursor_id,5,l_nw_item.PRIORITY_ID);
2860 DBMS_SQL.DEFINE_COLUMN(cursor_id,6,l_nw_item.PRIORITY_LEVEL);
2861 DBMS_SQL.DEFINE_COLUMN(cursor_id,7,l_nw_item.PRIORITY_CODE,30);
2862 DBMS_SQL.DEFINE_COLUMN(cursor_id,8,l_nw_item.DUE_DATE);
2863 DBMS_SQL.DEFINE_COLUMN(cursor_id,9,l_nw_item.TITLE,1990);
2864 DBMS_SQL.DEFINE_COLUMN(cursor_id,10,l_nw_item.PARTY_ID);
2865 DBMS_SQL.DEFINE_COLUMN(cursor_id,11,l_nw_item.OWNER_ID);
2866 DBMS_SQL.DEFINE_COLUMN(cursor_id,12,l_nw_item.OWNER_TYPE,25);
2867 DBMS_SQL.DEFINE_COLUMN(cursor_id,13,l_nw_item.ASSIGNEE_ID);
2868 DBMS_SQL.DEFINE_COLUMN(cursor_id,14,l_nw_item.ASSIGNEE_TYPE,25);
2869 DBMS_SQL.DEFINE_COLUMN(cursor_id,15,l_nw_item.SOURCE_OBJECT_ID);
2870 DBMS_SQL.DEFINE_COLUMN(cursor_id,16,l_nw_item.SOURCE_OBJECT_TYPE_CODE,30);
2871 DBMS_SQL.DEFINE_COLUMN(cursor_id,17,l_nw_item.APPLICATION_ID);
2872 DBMS_SQL.DEFINE_COLUMN(cursor_id,18,l_nw_item.IEU_ENUM_TYPE_UUID,38);
2873 DBMS_SQL.DEFINE_COLUMN(cursor_id,19,l_nw_item.WORK_ITEM_NUMBER,64);
2874 DBMS_SQL.DEFINE_COLUMN(cursor_id,20,l_nw_item.RESCHEDULE_TIME);
2875 DBMS_SQL.DEFINE_COLUMN(cursor_id,21,l_nw_item.WS_ID);
2876
2877
2878
2879 -- FETCH l_wr_cur into l_nw_item;
2880
2881 dummy := DBMS_SQL.EXECUTE(cursor_id);
2882
2883 LOOP
2884 temp := DBMS_SQL.FETCH_ROWS(cursor_id);
2885
2886 if p_num_of_dist_items <= 2 then
2887 l_num_of_dist_items_incr := p_num_of_dist_items * 4;
2888 elsif p_num_of_dist_items > 2 and p_num_of_dist_items <= 4 then
2889 l_num_of_dist_items_incr := p_num_of_dist_items * 3;
2890 elsif p_num_of_dist_items > 4 and p_num_of_dist_items <=6 then
2891 l_num_of_dist_items_incr := P_num_of_dist_items * 2;
2892 elsif p_num_of_dist_items > 6 then
2893 l_num_of_dist_items_incr := p_num_of_dist_items;
2894 end if;
2895 if temp = 0 or (l_wr_cur_cnt > l_num_of_dist_items_incr) then
2896 exit;
2897 elsif temp <> 0 then
2898
2899 DBMS_SQL.COLUMN_VALUE(cursor_id,1,l_nw_item.WORK_ITEM_ID);
2900 DBMS_SQL.COLUMN_VALUE(cursor_id,2,l_nw_item.WORKITEM_OBJ_CODE);
2901 DBMS_SQL.COLUMN_VALUE(cursor_id,3,l_nw_item.WORKITEM_PK_ID);
2902 DBMS_SQL.COLUMN_VALUE(cursor_id,4,l_nw_item.STATUS_ID);
2903 DBMS_SQL.COLUMN_VALUE(cursor_id,5,l_nw_item.PRIORITY_ID);
2904 DBMS_SQL.COLUMN_VALUE(cursor_id,6,l_nw_item.PRIORITY_LEVEL);
2905 DBMS_SQL.COLUMN_VALUE(cursor_id,7,l_nw_item.PRIORITY_CODE);
2906 DBMS_SQL.COLUMN_VALUE(cursor_id,8,l_nw_item.DUE_DATE);
2907 DBMS_SQL.COLUMN_VALUE(cursor_id,9,l_nw_item.TITLE);
2908 DBMS_SQL.COLUMN_VALUE(cursor_id,10,l_nw_item.PARTY_ID);
2909 DBMS_SQL.COLUMN_VALUE(cursor_id,11,l_nw_item.OWNER_ID);
2910 DBMS_SQL.COLUMN_VALUE(cursor_id,12,l_nw_item.OWNER_TYPE);
2911 DBMS_SQL.COLUMN_VALUE(cursor_id,13,l_nw_item.ASSIGNEE_ID);
2912 DBMS_SQL.COLUMN_VALUE(cursor_id,14,l_nw_item.ASSIGNEE_TYPE);
2913 DBMS_SQL.COLUMN_VALUE(cursor_id,15,l_nw_item.SOURCE_OBJECT_ID);
2914 DBMS_SQL.COLUMN_VALUE(cursor_id,16,l_nw_item.SOURCE_OBJECT_TYPE_CODE);
2915 DBMS_SQL.COLUMN_VALUE(cursor_id,17,l_nw_item.APPLICATION_ID);
2916 DBMS_SQL.COLUMN_VALUE(cursor_id,18,l_nw_item.IEU_ENUM_TYPE_UUID);
2917 DBMS_SQL.COLUMN_VALUE(cursor_id,19,l_nw_item.WORK_ITEM_NUMBER);
2918 DBMS_SQL.COLUMN_VALUE(cursor_id,20,l_nw_item.RESCHEDULE_TIME);
2919 DBMS_SQL.COLUMN_VALUE(cursor_id,21,l_nw_item.WS_ID);
2920 end if;
2921
2922
2923
2924 -- exit when ( (l_wr_cur%NOTFOUND) OR (l_wr_cur_cnt > l_num_of_dist_items_incr) ) ;
2925
2926 l_wr_cur_cnt := l_wr_cur_cnt + 1;
2927
2928 -- update work item status to distributing
2929 update ieu_uwqm_items
2930 set distribution_status_id = 2
2931 where work_item_id = l_nw_item.WORK_ITEM_ID;
2932
2933
2934 -- Add items to the Table of rec
2935 select priority_code
2936 into l_priority_code
2937 from ieu_uwqm_priorities_b
2938 where priority_id = l_nw_item.PRIORITY_ID;
2939
2940 l_nw_items_list(l_ctr).WORK_ITEM_ID := l_nw_item.WORK_ITEM_ID;
2941 l_nw_items_list(l_ctr).WORKITEM_OBJ_CODE := l_nw_item.WORKITEM_OBJ_CODE;
2942 l_nw_items_list(l_ctr).WORKITEM_PK_ID := l_nw_item.WORKITEM_PK_ID;
2943 l_nw_items_list(l_ctr).STATUS_ID := l_nw_item.STATUS_ID;
2944 l_nw_items_list(l_ctr).PRIORITY_CODE := l_priority_code;
2945 l_nw_items_list(l_ctr).DUE_DATE := l_nw_item.DUE_DATE;
2946 l_nw_items_list(l_ctr).TITLE := l_nw_item.TITLE;
2947 l_nw_items_list(l_ctr).PARTY_ID := l_nw_item.PARTY_ID;
2948 l_nw_items_list(l_ctr).OWNER_ID := l_nw_item.OWNER_ID;
2949 l_nw_items_list(l_ctr).OWNER_TYPE := l_nw_item.OWNER_TYPE;
2950 l_nw_items_list(l_ctr).ASSIGNEE_ID := l_nw_item.ASSIGNEE_ID;
2951 l_nw_items_list(l_ctr).ASSIGNEE_TYPE := l_nw_item.ASSIGNEE_TYPE;
2952 l_nw_items_list(l_ctr).SOURCE_OBJECT_ID := l_nw_item.SOURCE_OBJECT_ID;
2953 l_nw_items_list(l_ctr).SOURCE_OBJECT_TYPE_CODE := l_nw_item.SOURCE_OBJECT_TYPE_CODE;
2954 l_nw_items_list(l_ctr).APPLICATION_ID := l_nw_item.APPLICATION_ID;
2955 l_nw_items_list(l_ctr).IEU_ENUM_TYPE_UUID := l_nw_item.IEU_ENUM_TYPE_UUID;
2956 l_nw_items_list(l_ctr).WORK_ITEM_NUMBER := l_nw_item.WORK_ITEM_NUMBER;
2957 l_nw_items_list(l_ctr).RESCHEDULE_TIME := l_nw_item.RESCHEDULE_TIME;
2958 l_nw_items_list(l_ctr).WS_ID := l_nw_item.WS_ID;
2959
2960 l_ctr := l_ctr + 1;
2961
2962 END LOOP;
2963 DBMS_SQL.CLOSE_CURSOR(cursor_id);
2964
2965 -- CLOSE l_wr_cur;
2966 commit;
2967
2968 -- dbms_output.put_line('item cnt: '||l_nw_items_list.COUNT);
2969
2970 -- Check if there any any Distributable Items for this resource
2971 -- x_num_of_items_distributed will be set to -1 if there are no Distributable Items
2972
2973 if (l_nw_items_list.COUNT < 1)
2974 then
2975 x_num_of_items_distributed := -1;
2976 else
2977 x_num_of_items_distributed := 0;
2978 end if;
2979
2980 --dbms_output.put_line('dist flag: '||x_num_of_items_distributed);
2981
2982 -- If there are distributable items for this resource then
2983 -- 1. get the distribution rules for each work source
2984 -- 2. Select the Distributable Work Item details
2985 -- 3. call the appropriate distribution function based on the Work Source
2986
2987 if (x_num_of_items_distributed <> -1)
2988 then
2989
2990 while (l_nw_ctr <= l_nw_items_list.count)
2991 loop
2992
2993 z := z +1;
2994
2995 -- insert into p_temp values(p_num_of_dist_items||' '||x_num_of_items_distributed||' '||z||' '||l_nw_ctr, 10001);commit;
2996
2997 if (z <= (p_num_of_dist_items - x_num_of_items_distributed)) then
2998
2999 l_nw_items_list2(z).WORK_ITEM_ID := l_nw_items_list(l_nw_ctr).WORK_ITEM_ID;
3000 l_nw_items_list2(z).WORKITEM_OBJ_CODE := l_nw_items_list(l_nw_ctr).WORKITEM_OBJ_CODE;
3001 l_nw_items_list2(z).WORKITEM_PK_ID := l_nw_items_list(l_nw_ctr).WORKITEM_PK_ID;
3002 l_nw_items_list2(z).STATUS_ID := l_nw_items_list(l_nw_ctr).STATUS_ID;
3003 l_nw_items_list2(z).PRIORITY_CODE := l_nw_items_list(l_nw_ctr).priority_code;
3004 l_nw_items_list2(z).DUE_DATE := l_nw_items_list(l_nw_ctr).DUE_DATE;
3005 l_nw_items_list2(z).TITLE := l_nw_items_list(l_nw_ctr).TITLE;
3006 l_nw_items_list2(z).PARTY_ID := l_nw_items_list(l_nw_ctr).PARTY_ID;
3007 l_nw_items_list2(z).OWNER_ID := l_nw_items_list(l_nw_ctr).OWNER_ID;
3008 l_nw_items_list2(z).OWNER_TYPE := l_nw_items_list(l_nw_ctr).OWNER_TYPE;
3009 l_nw_items_list2(z).ASSIGNEE_ID := l_nw_items_list(l_nw_ctr).ASSIGNEE_ID;
3010 l_nw_items_list2(z).ASSIGNEE_TYPE := l_nw_items_list(l_nw_ctr).ASSIGNEE_TYPE;
3011 l_nw_items_list2(z).SOURCE_OBJECT_ID := l_nw_items_list(l_nw_ctr).SOURCE_OBJECT_ID;
3012 l_nw_items_list2(z).SOURCE_OBJECT_TYPE_CODE := l_nw_items_list(l_nw_ctr).SOURCE_OBJECT_TYPE_CODE;
3013 l_nw_items_list2(z).APPLICATION_ID := l_nw_items_list(l_nw_ctr).APPLICATION_ID;
3014 l_nw_items_list2(z).IEU_ENUM_TYPE_UUID := l_nw_items_list(l_nw_ctr).IEU_ENUM_TYPE_UUID;
3015 l_nw_items_list2(z).WORK_ITEM_NUMBER := l_nw_items_list(l_nw_ctr).WORK_ITEM_NUMBER;
3016 l_nw_items_list2(z).RESCHEDULE_TIME := l_nw_items_list(l_nw_ctr).RESCHEDULE_TIME;
3017 l_nw_items_list2(z).WS_ID := l_nw_items_list(l_nw_ctr).WS_ID;
3018
3019 end if;
3020
3021 if x_num_of_items_distributed = p_num_of_dist_items then
3022 exit;
3023 else
3024 l_nw_ctr := l_nw_ctr + z;
3025 z := 0;
3026 end if;
3027
3028
3029 -- dbms_output.put_line('getting ws id');
3030 -- loop thru all seeded Work sources
3031 for cur_rec in c_ws
3032 loop
3033
3034 l_curr_ws_id := cur_rec.ws_id;
3035 l_ws_code := cur_rec.ws_code;
3036
3037 /*
3038 begin
3039 select ws_code
3040 into l_ws_name
3041 from ieu_uwqm_work_sources_b
3042 where ws_id = l_curr_ws_id;
3043 exception
3044 when others then
3045 l_ws_name := '';
3046 end;
3047 */
3048 --dbms_output.put_line('curr ws id: '||l_curr_ws_id);
3049
3050 -- Get the Business rules to be passed to the Distribution Function
3051 l_dist_bus_rules := SYSTEM.DIST_BUS_RULES_NST();
3052
3053 l_dist_bus_rules.extend;
3054 l_dist_bus_rules(l_dist_bus_rules.last) := SYSTEM.DIST_BUS_RULES_OBJ ( l_ws_code,
3055 cur_rec.distribute_from,
3056 cur_rec.distribute_to,
3057 cur_rec.DIST_ST_BASED_ON_PARENT_FLAG);
3058
3059
3060 if (l_audit_log_val = 'DETAILED')
3061 then
3062
3063 if (cur_rec.distribute_from = 'GROUP_OWNED') and
3064 (cur_rec.distribute_to = 'INDIVIDUAL_OWNED')
3065 then
3066 l_ieu_comment_code3 := 'GO_IO';
3067 elsif (cur_rec.distribute_from = 'GROUP_OWNED') and
3068 (cur_rec.distribute_to = 'INDIVIDUAL_ASSIGNED')
3069 then
3070 l_ieu_comment_code3 := 'GO_IA';
3071 elsif (cur_rec.distribute_from = 'GROUP_ASSIGNED') and
3072 (cur_rec.distribute_to = 'INDIVIDUAL_OWNED')
3073 then
3074 l_ieu_comment_code3 := 'GA_IO';
3075 elsif (cur_rec.distribute_from = 'GROUP_ASSIGNED') and
3076 (cur_rec.distribute_to = 'INDIVIDUAL_ASSIGNED')
3077 then
3078 l_ieu_comment_code3 := 'GA_IA';
3079 end if;
3080 end if;
3081 --dbms_output.put_line('loop 5');
3082
3083 --dbms_output.put_line('bus rules: '||l_dist_bus_rules.count);
3084
3085 -- Initialize this table for new WS
3086 l_dist_items := SYSTEM.WR_ITEM_DATA_NST();
3087
3088 for i in l_nw_items_list2.first .. l_nw_items_list2.last
3089 loop
3090
3091
3092 -- group the Distributable Work Items based on Work Source
3093 if (l_nw_items_list2(i).ws_id = l_curr_ws_id)
3094 then
3095
3096 if (l_nw_items_list2(i).STATUS_ID = 0)
3097 then
3098 l_work_item_status := 'OPEN';
3099 elsif (l_nw_items_list2(i).STATUS_ID = 3)
3100 then
3101 l_work_item_status := 'CLOSE';
3102 elsif (l_nw_items_list2(i).STATUS_ID = 4)
3103 then
3104 l_work_item_status := 'DELETE';
3105 elsif (l_nw_items_list2(i).STATUS_ID = 5)
3106 then
3107 l_work_item_status := 'SLEEP';
3108 end if;
3109
3110 --dbms_output.put_line('ws id matches: '||l_nw_items_list(i).ws_id|| ' ID: '||l_nw_items_list(i).WORKITEM_PK_ID);
3111 l_dist_items.extend;
3112 l_dist_items(l_dist_items.last) := SYSTEM.WR_ITEM_DATA_OBJ(l_nw_items_list2(i).WORK_ITEM_ID,
3113 l_nw_items_list2(i).WORKITEM_OBJ_CODE,
3114 l_nw_items_list2(i).WORKITEM_PK_ID,
3115 l_work_item_status,
3116 l_nw_items_list2(i).PRIORITY_ID,
3117 l_nw_items_list2(i).PRIORITY_LEVEL,
3118 l_nw_items_list2(i).PRIORITY_CODE,
3119 l_nw_items_list2(i).DUE_DATE,
3120 l_nw_items_list2(i).TITLE,
3121 l_nw_items_list2(i).PARTY_ID,
3122 l_nw_items_list2(i).OWNER_ID,
3123 l_nw_items_list2(i).OWNER_TYPE,
3124 l_nw_items_list2(i).ASSIGNEE_ID,
3125 l_nw_items_list2(i).ASSIGNEE_TYPE,
3126 l_nw_items_list2(i).SOURCE_OBJECT_ID,
3127 l_nw_items_list2(i).SOURCE_OBJECT_TYPE_CODE,
3128 l_nw_items_list2(i).APPLICATION_ID,
3129 l_nw_items_list2(i).IEU_ENUM_TYPE_UUID,
3130 l_nw_items_list2(i).WORK_ITEM_NUMBER,
3131 l_nw_items_list2(i).RESCHEDULE_TIME,
3132 l_ws_code, --l_nw_items_list(i).WS_ID,
3133 null,
3134 null);
3135 end if;
3136
3137 end loop; /* l_nw_items_list2 */
3138
3139
3140 --dbms_output.put_line('dist items cnt'||l_dist_items.count);
3141
3142 -- Call the Distribution Function
3143
3144
3145 if (l_dist_items.count > 0)
3146 then
3147 -- insert into p_temp values('calling dist func', 1001);commit;
3148 --dbms_output.put_line('calling dist func');
3149
3150 if ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') OR (l_audit_log_val = 'MINIMAL') )
3151 then
3152
3153 for k in l_dist_items.first .. l_dist_items.last
3154 loop
3155
3156 IEU_UWQM_AUDIT_LOG_PKG.INSERT_ROW
3157 (
3158 P_ACTION_KEY => l_action_key,
3159 P_EVENT_KEY => l_event_key,
3160 P_MODULE => l_module,
3161 P_WS_CODE => l_ws_code,
3162 P_APPLICATION_ID => l_application_id,
3163 P_WORKITEM_PK_ID => l_dist_items(k).workitem_pk_id,
3164 P_WORKITEM_OBJ_CODE => l_dist_items(k).workitem_obj_code,
3165 P_WORK_ITEM_STATUS_PREV => l_status_id,
3166 P_WORK_ITEM_STATUS_CURR => l_status_id,
3167 P_OWNER_ID_PREV => l_dist_items(k).owner_id,
3168 P_OWNER_ID_CURR => l_dist_items(k).owner_id,
3169 P_OWNER_TYPE_PREV => l_dist_items(k).owner_type,
3170 P_OWNER_TYPE_CURR => l_dist_items(k).owner_type,
3171 P_ASSIGNEE_ID_PREV => null,
3172 P_ASSIGNEE_ID_CURR => l_dist_items(k).assignee_id,
3173 P_ASSIGNEE_TYPE_PREV => null,
3174 P_ASSIGNEE_TYPE_CURR => l_dist_items(k).assignee_type,
3175 P_SOURCE_OBJECT_ID_PREV => l_dist_items(k).source_object_id,
3176 P_SOURCE_OBJECT_ID_CURR => l_dist_items(k).source_object_id,
3177 P_SOURCE_OBJECT_TYPE_CODE_PREV => l_dist_items(k).source_object_type_code,
3178 P_SOURCE_OBJECT_TYPE_CODE_CURR => l_dist_items(k).source_object_type_code,
3179 P_PARENT_WORKITEM_STATUS_PREV => null,
3180 P_PARENT_WORKITEM_STATUS_CURR => null,
3181 P_PARENT_DIST_STATUS_PREV => null,
3182 P_PARENT_DIST_STATUS_CURR => null,
3183 P_WORKITEM_DIST_STATUS_PREV => l_distribution_status_id,
3184 P_WORKITEM_DIST_STATUS_CURR => l_distribution_status_id,
3185 P_PRIORITY_PREV => l_dist_items(k).priority_id,
3186 P_PRIORITY_CURR => l_dist_items(k).priority_id,
3187 P_DUE_DATE_PREV => l_dist_items(k).due_date,
3188 P_DUE_DATE_CURR => l_dist_items(k).due_date,
3189 P_RESCHEDULE_TIME_PREV => l_reschedule_time,
3190 P_RESCHEDULE_TIME_CURR => l_reschedule_time,
3191 P_IEU_COMMENT_CODE1 => l_ieu_comment_code1,
3192 P_IEU_COMMENT_CODE2 => l_ieu_comment_code2,
3193 P_IEU_COMMENT_CODE3 => l_ieu_comment_code3,
3194 P_IEU_COMMENT_CODE4 => l_ieu_comment_code4,
3195 P_IEU_COMMENT_CODE5 => l_ieu_comment_code5,
3196 P_WORKITEM_COMMENT_CODE1 => l_workitem_comment_code1,
3197 P_WORKITEM_COMMENT_CODE2 => l_workitem_comment_code2,
3198 P_WORKITEM_COMMENT_CODE3 => l_workitem_comment_code3,
3199 P_WORKITEM_COMMENT_CODE4 => l_workitem_comment_code4,
3200 P_WORKITEM_COMMENT_CODE5 => l_workitem_comment_code5,
3201 P_STATUS => 'S',
3202 P_ERROR_CODE => x_msg_data,
3203 X_AUDIT_LOG_ID => l_audit_log_id_list(k),
3204 X_MSG_DATA => x_msg_data,
3205 X_RETURN_STATUS => l_ret_sts
3206 );
3207
3208 end loop;
3209 end if;
3210
3211 -- Set the Resource_id and type in IEU_WR_PUB
3212 --IEU_WR_PUB.l_dist_resource_id := p_resource_id;
3213 --IEU_WR_PUB.l_dist_resource_type := 'RS_INDIVIDUAL';
3214
3215 BEGIN
3216 EXECUTE IMMEDIATE
3217 'BEGIN '|| cur_rec.DISTRIBUTION_FUNCTION||'(:1,:2,:3,:4,:5,:6,:7,:8,:9); END;'
3218 USING IN P_RESOURCE_ID, IN P_LANGUAGE, IN P_SOURCE_LANG, IN P_NUM_OF_DIST_ITEMS, IN L_DIST_BUS_RULES, IN OUT L_DIST_ITEMS,
3219 OUT L_MSG_COUNT, OUT L_MSG_DATA, OUT L_RETURN_STATUS;
3220 EXCEPTION
3221 when others then
3222
3223 -- insert into p_temp(msg) values('exception');
3224 -- Set the status back from 'Distributing' to 'Distributable'
3225 for k in l_dist_items.first .. l_dist_items.last
3226 loop
3227 l_workitem_pk_id := l_dist_items(k).workitem_pk_id;
3228 l_workitem_obj_code := l_dist_items(k).workitem_obj_code;
3229 l_owner_id := l_dist_items(k).owner_id;
3230 l_owner_type := l_dist_items(k).owner_type;
3231 l_assignee_id := l_dist_items(k).assignee_id;
3232 l_assignee_type := l_dist_items(k).assignee_type;
3233 l_priority_id := l_dist_items(k).priority_id;
3234 l_due_date := l_dist_items(k).due_date;
3235 l_source_object_id := l_dist_items(k).source_object_id;
3236 l_source_object_type_code := l_dist_items(k).source_object_type_code;
3237 if (l_dist_items(k).work_item_status = 'OPEN')
3238 then
3239 l_status_id := 0;
3240 elsif (l_dist_items(k).work_item_status = 'CLOSE')
3241 then
3242 l_status_id := 3;
3243 elsif (l_dist_items(k).work_item_status = 'DELETE')
3244 then
3245 l_status_id := 4;
3246 elsif (l_dist_items(k).work_item_status = 'SLEEP')
3247 then
3248 l_status_id := 5;
3249 end if;
3250
3251 -- insert into p_temp values('dist func failed '||l_return_status||' '||l_msg_data, l_dist_items(k).work_item_id);commit;
3252 update ieu_uwqm_items
3253 set distribution_status_id = 1
3254 where work_item_id = l_dist_items(k).work_item_id;
3255 commit;
3256
3257 -- Set the Resource_id and type in IEU_WR_PUB
3258
3259 l_action_key := 'DISTRIBUTION';
3260 if ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED'))
3261 then
3262 l_event_key := 'DISTRIBUTE';
3263 else
3264 l_event_key := null;
3265 end if;
3266 l_module := 'IEU_GET_NEXT_WORK_PVT.DISTRIBUTE_WR_ITEMS';
3267 l_application_id := 696;
3268 l_ret_sts := 'E';
3269 l_token_str := SQLCODE||': '||SQLERRM;
3270 --l_token_str := SQLERRM;
3271 --insert into p_temp('errcode: '||SQLCODE);
3272 --insert inot p_temp('errm: '||SQLERRM);
3273
3274 FND_MSG_PUB.INITIALIZE;
3275 FND_MESSAGE.SET_NAME('IEU', 'IEU_SQL_ERROR');
3276 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME','IEU_GET_NEXT_WORK_PVT.DISTRIBUTE_AND_DELIVER');
3277 FND_MESSAGE.SET_TOKEN('SQL_ERROR_MSG',l_token_str);
3278 fnd_msg_pub.ADD;
3279
3280 fnd_msg_pub.Count_and_Get
3281 (
3282 p_count => x_msg_count,
3283 p_data => x_msg_data
3284 );
3285
3286
3287 if (l_audit_log_val = 'DETAILED')
3288 then
3289 l_ieu_comment_code1 := 'NUM_OF_ATTEMPTS '||l_dist_deliver_num_of_attempts;
3290 l_ieu_comment_code2 := 'DISTRIBUTION_FUNC '||cur_rec.DISTRIBUTION_FUNCTION;
3291 end if;
3292
3293
3294 if ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') OR (l_audit_log_val = 'MINIMAL') )
3295 then
3296
3297 BEGIN
3298
3299 select reschedule_time, distribution_status_id, priority_id
3300 into l_reschedule_time, l_distribution_status_id, l_priority_id
3301 from ieu_uwqm_items
3302 where workitem_pk_id = l_workitem_pk_id
3303 and workitem_obj_code = l_workitem_obj_code;
3304
3305 EXCEPTION
3306 when others then
3307 null;
3308 END;
3309
3310 l_distribution_status_id := 1;
3311 l_msg_data:= x_msg_data;
3312
3313 IEU_UWQM_AUDIT_LOG_PKG.UPDATE_ROW
3314 (
3315 P_AUDIT_LOG_ID => l_audit_log_id_list(k),
3316 P_ACTION_KEY => l_action_key,
3317 P_EVENT_KEY => l_event_key,
3318 P_MODULE => l_module,
3319 P_WS_CODE => l_ws_code,
3320 P_APPLICATION_ID => l_application_id,
3321 P_WORKITEM_PK_ID => l_workitem_pk_id,
3322 P_WORKITEM_OBJ_CODE => l_workitem_obj_code,
3323 P_WORK_ITEM_STATUS_PREV => l_status_id,
3324 P_WORK_ITEM_STATUS_CURR => l_status_id,
3325 P_OWNER_ID_PREV => l_owner_id,
3326 P_OWNER_ID_CURR => l_owner_id,
3327 P_OWNER_TYPE_PREV => l_owner_type,
3328 P_OWNER_TYPE_CURR => l_owner_type,
3329 P_ASSIGNEE_ID_PREV => null,
3330 P_ASSIGNEE_ID_CURR => l_assignee_id,
3331 P_ASSIGNEE_TYPE_PREV => null,
3332 P_ASSIGNEE_TYPE_CURR => l_assignee_type,
3333 P_SOURCE_OBJECT_ID_PREV => l_source_object_id,
3334 P_SOURCE_OBJECT_ID_CURR => l_source_object_id,
3335 P_SOURCE_OBJECT_TYPE_CODE_PREV => l_source_object_type_code,
3336 P_SOURCE_OBJECT_TYPE_CODE_CURR => l_source_object_type_code,
3337 P_PARENT_WORKITEM_STATUS_PREV => null,
3338 P_PARENT_WORKITEM_STATUS_CURR => null,
3339 P_PARENT_DIST_STATUS_PREV => null,
3340 P_PARENT_DIST_STATUS_CURR => null,
3341 P_WORKITEM_DIST_STATUS_PREV => l_distribution_status_id,
3342 P_WORKITEM_DIST_STATUS_CURR => l_distribution_status_id,
3343 P_PRIORITY_PREV => l_priority_id,
3344 P_PRIORITY_CURR => l_priority_id,
3345 P_DUE_DATE_PREV => l_due_date,
3346 P_DUE_DATE_CURR => l_due_date,
3347 P_RESCHEDULE_TIME_PREV => l_reschedule_time,
3348 P_RESCHEDULE_TIME_CURR => l_reschedule_time,
3349 P_IEU_COMMENT_CODE1 => l_ieu_comment_code1,
3350 P_IEU_COMMENT_CODE2 => l_ieu_comment_code2,
3351 P_IEU_COMMENT_CODE3 => l_ieu_comment_code3,
3352 P_IEU_COMMENT_CODE4 => l_ieu_comment_code4,
3353 P_IEU_COMMENT_CODE5 => l_ieu_comment_code5,
3354 P_WORKITEM_COMMENT_CODE1 => l_workitem_comment_code1,
3355 P_WORKITEM_COMMENT_CODE2 => l_workitem_comment_code2,
3356 P_WORKITEM_COMMENT_CODE3 => l_workitem_comment_code3,
3357 P_WORKITEM_COMMENT_CODE4 => l_workitem_comment_code4,
3358 P_WORKITEM_COMMENT_CODE5 => l_workitem_comment_code5,
3359 P_STATUS => 'E',
3360 P_ERROR_CODE => l_msg_data);
3361
3362 -- insert into p_temp(msg) values('l_msg_data3: '||x_msg_data);
3363
3364 end if;
3365 end loop;
3366 END;
3367
3368 -- Check the # of items distributed
3369
3370 for j in l_dist_items.first .. l_dist_items.last
3371 loop
3372
3373 l_workitem_pk_id := l_dist_items(j).workitem_pk_id;
3374 l_workitem_obj_code := l_dist_items(j).workitem_obj_code;
3375 l_owner_id := l_dist_items(j).owner_id;
3376 l_owner_type := l_dist_items(j).owner_type;
3377 l_assignee_id := l_dist_items(j).assignee_id;
3378 l_assignee_type := l_dist_items(j).assignee_type;
3379 l_priority_id := l_dist_items(j).priority_id;
3380 l_due_date := l_dist_items(j).due_date;
3381 l_source_object_id := l_dist_items(j).source_object_id;
3382 l_source_object_type_code := l_dist_items(j).source_object_type_code;
3383 if (l_dist_items(j).work_item_status = 'OPEN')
3384 then
3385 l_status_id := 0;
3386 elsif (l_dist_items(j).work_item_status = 'CLOSE')
3387 then
3388 l_status_id := 3;
3389 elsif (l_dist_items(j).work_item_status = 'DELETE')
3390 then
3391 l_status_id := 4;
3392 elsif (l_dist_items(j).work_item_status = 'SLEEP')
3393 then
3394 l_status_id := 5;
3395 end if;
3396
3397
3398 l_action_key := 'DISTRIBUTION';
3399 if ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED'))
3400 then
3401 l_event_key := 'DISTRIBUTE';
3402 else
3403 l_event_key := null;
3404 end if;
3405 l_module := 'IEU_GET_NEXT_WORK_PVT.DISTRIBUTE_WR_ITEMS';
3406 l_application_id := 696;
3407
3408 if (l_audit_log_val = 'DETAILED')
3409 then
3410 l_ieu_comment_code1 := 'NUM_OF_ATTEMPTS '||l_dist_deliver_num_of_attempts;
3411 l_ieu_comment_code2 := 'DISTRIBUTION_FUNC '||cur_rec.DISTRIBUTION_FUNCTION;
3412 end if;
3413
3414 if (l_dist_items(j).DISTRIBUTED = 'TRUE')
3415 then
3416 l_audit_log_sts := 'S';
3417 l_distribution_status_id := 3;
3418 else
3419 l_audit_log_sts := 'E';
3420 l_distribution_status_id := 1;
3421 end if;
3422
3423 if ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') OR (l_audit_log_val = 'MINIMAL') )
3424 then
3425
3426 BEGIN
3427
3428 select reschedule_time, priority_id
3429 into l_reschedule_time, l_priority_id
3430 from ieu_uwqm_items
3431 where workitem_pk_id = l_workitem_pk_id
3432 and workitem_obj_code = l_workitem_obj_code;
3433
3434 EXCEPTION
3435 when others then
3436 null;
3437 END;
3438
3439 IEU_UWQM_AUDIT_LOG_PKG.UPDATE_ROW
3440 (
3441 P_AUDIT_LOG_ID => l_audit_log_id_list(j),
3442 P_ACTION_KEY => l_action_key,
3443 P_EVENT_KEY => l_event_key,
3444 P_MODULE => l_module,
3445 P_WS_CODE => l_ws_code,
3446 P_APPLICATION_ID => l_application_id,
3447 P_WORKITEM_PK_ID => l_workitem_pk_id,
3448 P_WORKITEM_OBJ_CODE => l_workitem_obj_code,
3449 P_WORK_ITEM_STATUS_PREV => l_status_id,
3450 P_WORK_ITEM_STATUS_CURR => l_status_id,
3451 P_OWNER_ID_PREV => l_owner_id,
3452 P_OWNER_ID_CURR => l_owner_id,
3453 P_OWNER_TYPE_PREV => l_owner_type,
3454 P_OWNER_TYPE_CURR => l_owner_type,
3455 P_ASSIGNEE_ID_PREV => null,
3456 P_ASSIGNEE_ID_CURR => l_assignee_id,
3457 P_ASSIGNEE_TYPE_PREV => null,
3458 P_ASSIGNEE_TYPE_CURR => l_assignee_type,
3459 P_SOURCE_OBJECT_ID_PREV => l_source_object_id,
3460 P_SOURCE_OBJECT_ID_CURR => l_source_object_id,
3461 P_SOURCE_OBJECT_TYPE_CODE_PREV => l_source_object_type_code,
3462 P_SOURCE_OBJECT_TYPE_CODE_CURR => l_source_object_type_code,
3463 P_PARENT_WORKITEM_STATUS_PREV => null,
3464 P_PARENT_WORKITEM_STATUS_CURR => null,
3465 P_PARENT_DIST_STATUS_PREV => null,
3466 P_PARENT_DIST_STATUS_CURR => null,
3467 P_WORKITEM_DIST_STATUS_PREV => 1,
3468 P_WORKITEM_DIST_STATUS_CURR => l_distribution_status_id,
3469 P_PRIORITY_PREV => l_priority_id,
3470 P_PRIORITY_CURR => l_priority_id,
3471 P_DUE_DATE_PREV => l_due_date,
3472 P_DUE_DATE_CURR => l_due_date,
3473 P_RESCHEDULE_TIME_PREV => l_reschedule_time,
3474 P_RESCHEDULE_TIME_CURR => l_reschedule_time,
3475 P_IEU_COMMENT_CODE1 => l_ieu_comment_code1,
3476 P_IEU_COMMENT_CODE2 => l_ieu_comment_code2,
3477 P_IEU_COMMENT_CODE3 => l_ieu_comment_code3,
3478 P_IEU_COMMENT_CODE4 => l_ieu_comment_code4,
3479 P_IEU_COMMENT_CODE5 => l_ieu_comment_code5,
3480 P_WORKITEM_COMMENT_CODE1 => l_workitem_comment_code1,
3481 P_WORKITEM_COMMENT_CODE2 => l_workitem_comment_code2,
3482 P_WORKITEM_COMMENT_CODE3 => l_workitem_comment_code3,
3483 P_WORKITEM_COMMENT_CODE4 => l_workitem_comment_code4,
3484 P_WORKITEM_COMMENT_CODE5 => l_workitem_comment_code5,
3485 P_STATUS => l_audit_log_sts,
3486 P_ERROR_CODE => l_msg_data
3487 );
3488
3489 end if;
3490
3491
3492 if (l_dist_items(j).DISTRIBUTED = 'TRUE')
3493 then
3494 IF (l_dist_items(j).WORK_ITEM_STATUS is not null)
3495 THEN
3496 IF (l_dist_items(j).WORK_ITEM_STATUS = 'OPEN')
3497 THEN
3498 l_work_item_status_id := 0;
3499 ELSIF (l_dist_items(j).WORK_ITEM_STATUS = 'CLOSE')
3500 THEN
3501 l_work_item_status_id := 3;
3502 ELSIF (l_dist_items(j).WORK_ITEM_STATUS = 'DELETE')
3503 THEN
3504 l_work_item_status_id := 4;
3505 ELSIF (l_dist_items(j).WORK_ITEM_STATUS = 'SLEEP')
3506 THEN
3507 l_work_item_status_id := 5;
3508 END IF;
3509 END IF;
3510
3511 --dbms_output.put_line('dist status set to TRUE work item pkid: '||l_dist_items(j).WORKITEM_PK_ID);
3512
3513 x_num_of_items_distributed := x_num_of_items_distributed + 1;
3514 x_uwqm_workitem_data.extend;
3515 x_uwqm_workitem_data(x_uwqm_workitem_data.last) := SYSTEM.WR_ITEM_DATA_OBJ(l_dist_items(j).WORK_ITEM_ID,
3516 l_dist_items(j).WORKITEM_OBJ_CODE,
3517 l_dist_items(j).WORKITEM_PK_ID,
3518 l_work_item_status_id,
3519 l_dist_items(j).PRIORITY_ID,
3520 l_dist_items(j).PRIORITY_LEVEL,
3521 l_dist_items(j).PRIORITY_CODE,
3522 l_dist_items(j).DUE_DATE,
3523 l_dist_items(j).TITLE,
3524 l_dist_items(j).PARTY_ID,
3525 l_dist_items(j).OWNER_ID,
3526 l_dist_items(j).OWNER_TYPE,
3527 l_dist_items(j).ASSIGNEE_ID,
3528 l_dist_items(j).ASSIGNEE_TYPE,
3529 l_dist_items(j).SOURCE_OBJECT_ID,
3530 l_dist_items(j).SOURCE_OBJECT_TYPE_CODE,
3531 l_dist_items(j).APPLICATION_ID,
3532 l_dist_items(j).IEU_ENUM_TYPE_UUID,
3533 l_dist_items(j).WORK_ITEM_NUMBER,
3534 l_dist_items(j).RESCHEDULE_TIME,
3535 l_dist_items(j).WORK_SOURCE,
3536 l_dist_items(j).DISTRIBUTED,
3537 l_dist_items(j).ITEM_INCLUDED_BY_APP);
3538 elsif (l_dist_items(j).DISTRIBUTED = 'FALSE')
3539 then
3540 -- set the distribution_status_id back to 'Distributable'
3541 --dbms_output.put_line('dist status set to FALSE work item pkid: '||l_dist_items(j).WORKITEM_PK_ID);
3542 update ieu_uwqm_items
3543 set distribution_status_id = 1
3544 where work_item_id = l_dist_items(j).work_item_id;
3545 commit;
3546
3547 end if;
3548 end loop;
3549 --dbms_output.put_line('Num of Items Dist: '||x_num_of_items_distributed||' l_dist_item_obj cnt: '||x_uwqm_workitem_data.count);
3550
3551 end if; /* l_dist_items.count > 1 */
3552
3553 end loop; /* cur_res in c_ws */
3554
3555 end loop; /* l_nw_items_list */
3556
3557 end if; /* x_num_of_items_distributed <> -1 */
3558 if l_nw_items_list.count > 0 then
3559 for y in l_nw_items_list.first..l_nw_items_list.last
3560 loop
3561 update ieu_uwqm_items
3562 set distribution_status_id = 1
3563 where work_item_id = l_nw_items_list(y).work_item_id
3564 and distribution_status_id = 2;
3565 commit;
3566 end loop;
3567 end if;
3568 -- commit;
3569 -- dbms_output.put_line('cnt: '||l_nw_item_list.count);
3570 EXCEPTION
3571
3572 WHEN fnd_api.g_exc_error THEN
3573
3574 x_return_status := fnd_api.g_ret_sts_error;
3575
3576 fnd_msg_pub.Count_and_Get
3577 (
3578 p_count => x_msg_count,
3579 p_data => x_msg_data
3580 );
3581
3582 WHEN fnd_api.g_exc_unexpected_error THEN
3583
3584 x_return_status := fnd_api.g_ret_sts_unexp_error;
3585
3586 fnd_msg_pub.Count_and_Get
3587 (
3588 p_count => x_msg_count,
3589 p_data => x_msg_data
3590 );
3591
3592 WHEN OTHERS THEN
3593
3594 x_return_status := fnd_api.g_ret_sts_unexp_error;
3595
3596 IF FND_MSG_PUB.Check_msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3597 THEN
3598
3599 fnd_msg_pub.Count_and_Get
3600 (
3601 p_count => x_msg_count,
3602 p_data => x_msg_data
3603 );
3604
3605 END IF;
3606
3607 END GET_DIST_WR_ITEMS;
3608
3609 /**
3610 ** Called by PROCEDURE - DISTRIBUTE_AND_DELIVER_WR_ITEM, DISTRIBUTE_WORK_ITEMS
3611 ** The in var can be either a rec of type IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WR_ITEM_DATA_REC OR
3612 ** table of objects SYSTEM.WR_ITEM_DATA_NST
3613 ** The In var - p_var_in_type_code indicates if its a record - 'REC' or an object - 'OBJ'
3614 ** Copies the Work Item data from table of objects - SYSTEM.WR_ITEM_DATA_NST or rec - IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WR_ITEM_DATA_REC
3615 ** to table of records of type - IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WR_ITEM_ACT_DATA_LIST
3616 **/
3617
3618 PROCEDURE SET_WR_ITEM_DATA_REC( p_var_in_type_code IN VARCHAR2,
3619 p_dist_workitem_data IN SYSTEM.WR_ITEM_DATA_NST,
3620 p_dist_del_workitem_data IN IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WR_ITEM_DATA_REC,
3621 x_ctr IN OUT NOCOPY NUMBER,
3622 x_uwqm_workitem_data IN OUT NOCOPY IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WR_ITEM_ACT_DATA_LIST) IS
3623
3624 l_object_function VARCHAR2(40);
3625 l_object_parameters VARCHAR2(500);
3626 l_enter_from_task VARCHAR2(10);
3627 l_ws_id NUMBER;
3628 l_not_valid_flag VARCHAR2(1);
3629 BEGIN
3630
3631 if (p_var_in_type_code = 'OBJ')
3632 then
3633
3634 -- If a work item was distributed, copy the Work Item data from table of obj - l_dist_workitem_data
3635 -- to table of Rec - x_uwqm_workitem_data
3636
3637 for n in 1 .. p_dist_workitem_data .count
3638 loop
3639
3640 -- Changes reqd for object function and params
3641 -- Get the Object func and params based from JTF_OBJECTS
3642 IF (p_dist_workitem_data(n).SOURCE_OBJECT_TYPE_CODE is not null)
3643 THEN
3644
3645
3646 BEGIN
3647 SELECT enter_from_task, object_function, object_parameters
3648 INTO l_enter_from_task, l_object_function, l_object_parameters
3649 FROM JTF_OBJECTS_B
3650 WHERE OBJECT_CODE = p_dist_workitem_data(n).SOURCE_OBJECT_TYPE_CODE;
3651 EXCEPTION
3652 when no_data_found then
3653 null;
3654 END;
3655
3656 x_uwqm_workitem_data(x_ctr).IEU_OBJECT_FUNCTION := l_object_function;
3657 x_uwqm_workitem_data(x_ctr).IEU_OBJECT_PARAMETERS := l_object_parameters;
3658 x_uwqm_workitem_data(x_ctr).IEU_PARAM_PK_VALUE := p_dist_workitem_data(n).SOURCE_OBJECT_ID;
3659 x_uwqm_workitem_data(x_ctr).IEU_PARAM_PK_COL := 'SOURCE_OBJECT_ID';
3660
3661 ELSIF (p_dist_workitem_data(n).SOURCE_OBJECT_TYPE_CODE is null)
3662 THEN
3663
3664 BEGIN
3665 SELECT enter_from_task, object_function, object_parameters
3666 INTO l_enter_from_task, l_object_function, l_object_parameters
3667 FROM JTF_OBJECTS_B
3668 WHERE OBJECT_CODE = p_dist_workitem_data(n).WORKITEM_OBJ_CODE;
3669 EXCEPTION
3670 when no_data_found then
3671 null;
3672 END;
3673
3674 x_uwqm_workitem_data(x_ctr).IEU_OBJECT_FUNCTION := l_object_function;
3675 x_uwqm_workitem_data(x_ctr).IEU_OBJECT_PARAMETERS := l_object_parameters;
3676 x_uwqm_workitem_data(x_ctr).IEU_PARAM_PK_VALUE := p_dist_workitem_data(n).WORKITEM_PK_ID;
3677 x_uwqm_workitem_data(x_ctr).IEU_PARAM_PK_COL := 'WORKITEM_PK_ID';
3678
3679
3680 END IF; /* SOURCE_OBJECT_TYPE_CODE is not null */
3681
3682 BEGIN
3683 l_not_valid_flag := 'N';
3684 SELECT ws_id
3685 INTO l_ws_id
3686 FROM ieu_uwqm_work_sources_b
3687 WHERE ws_code = p_dist_workitem_data(n).WORK_SOURCE
3688 -- AND nvl(not_valid_flag,'N') = 'N';
3689 AND nvl(not_valid_flag,'N') = l_not_valid_flag;
3690
3691 EXCEPTION
3692 WHEN OTHERS THEN
3693 l_ws_id := null;
3694 END;
3695
3696 x_uwqm_workitem_data(x_ctr).IEU_MEDIA_TYPE_UUID := '';
3697 x_uwqm_workitem_data(x_ctr).WORK_ITEM_ID := p_dist_workitem_data(n).WORK_ITEM_ID;
3698 x_uwqm_workitem_data(x_ctr).WORKITEM_OBJ_CODE := p_dist_workitem_data(n).WORKITEM_OBJ_CODE;
3699 x_uwqm_workitem_data(x_ctr).WORKITEM_PK_ID := p_dist_workitem_data(n).WORKITEM_PK_ID;
3700 x_uwqm_workitem_data(x_ctr).STATUS_ID := p_dist_workitem_data(n).WORK_ITEM_STATUS;
3701 x_uwqm_workitem_data(x_ctr).PRIORITY_ID := p_dist_workitem_data(n).PRIORITY_ID;
3702 x_uwqm_workitem_data(x_ctr).PRIORITY_LEVEL := p_dist_workitem_data(n).PRIORITY_LEVEL;
3703 x_uwqm_workitem_data(x_ctr).DUE_DATE := p_dist_workitem_data(n).DUE_DATE;
3704 x_uwqm_workitem_data(x_ctr).TITLE := p_dist_workitem_data(n).TITLE;
3705 x_uwqm_workitem_data(x_ctr).PARTY_ID := p_dist_workitem_data(n).PARTY_ID;
3706 x_uwqm_workitem_data(x_ctr).OWNER_ID := p_dist_workitem_data(n).OWNER_ID;
3707 x_uwqm_workitem_data(x_ctr).OWNER_TYPE := p_dist_workitem_data(n).OWNER_TYPE;
3708 x_uwqm_workitem_data(x_ctr).ASSIGNEE_ID := p_dist_workitem_data(n).ASSIGNEE_ID;
3709 x_uwqm_workitem_data(x_ctr).ASSIGNEE_TYPE := p_dist_workitem_data(n).ASSIGNEE_TYPE;
3710 x_uwqm_workitem_data(x_ctr).SOURCE_OBJECT_ID := p_dist_workitem_data(n).SOURCE_OBJECT_ID;
3711 x_uwqm_workitem_data(x_ctr).SOURCE_OBJECT_TYPE_CODE := p_dist_workitem_data(n).SOURCE_OBJECT_TYPE_CODE;
3712 x_uwqm_workitem_data(x_ctr).APPLICATION_ID := p_dist_workitem_data(n).APPLICATION_ID;
3713 x_uwqm_workitem_data(x_ctr).IEU_ENUM_TYPE_UUID := p_dist_workitem_data(n).IEU_ENUM_TYPE_UUID;
3714 x_uwqm_workitem_data(x_ctr).WORK_ITEM_NUMBER := p_dist_workitem_data(n).WORK_ITEM_NUMBER;
3715 x_uwqm_workitem_data(x_ctr).RESCHEDULE_TIME := p_dist_workitem_data(n).RESCHEDULE_TIME;
3716 x_uwqm_workitem_data(x_ctr).IEU_GET_NEXTWORK_FLAG := 'Y';
3717 x_uwqm_workitem_data(x_ctr).IEU_ACTION_OBJECT_CODE := p_dist_workitem_data(n).SOURCE_OBJECT_TYPE_CODE;
3718 x_uwqm_workitem_data(x_ctr).WS_ID := l_ws_id;
3719 x_ctr := x_ctr + 1;
3720
3721 end loop;/* p_dist_workitem_data */
3722
3723 elsif (p_var_in_type_code = 'REC')
3724 then
3725
3726 -- If a work item was distributed, copy the Work Item data from table of obj - l_dist_workitem_data
3727 -- to table of Rec - x_uwqm_workitem_data
3728
3729 -- Changes reqd for object function and params
3730 -- Get the Object func and params based from JTF_OBJECTS
3731
3732 IF (p_dist_del_workitem_data.SOURCE_OBJECT_TYPE_CODE is not null)
3733 THEN
3734
3735 BEGIN
3736 SELECT enter_from_task, object_function, object_parameters
3737 INTO l_enter_from_task, l_object_function, l_object_parameters
3738 FROM JTF_OBJECTS_B
3739 WHERE OBJECT_CODE = p_dist_del_workitem_data.SOURCE_OBJECT_TYPE_CODE;
3740 EXCEPTION
3741 when no_data_found then
3742 null;
3743 END;
3744
3745 x_uwqm_workitem_data(x_ctr).IEU_OBJECT_FUNCTION := l_object_function;
3746 x_uwqm_workitem_data(x_ctr).IEU_OBJECT_PARAMETERS := l_object_parameters;
3747 x_uwqm_workitem_data(x_ctr).IEU_PARAM_PK_VALUE := p_dist_del_workitem_data.SOURCE_OBJECT_ID;
3748 x_uwqm_workitem_data(x_ctr).IEU_PARAM_PK_COL := 'SOURCE_OBJECT_ID';
3749
3750
3751 ELSIF (p_dist_del_workitem_data.SOURCE_OBJECT_TYPE_CODE is null)
3752 THEN
3753
3754 BEGIN
3755 SELECT enter_from_task, object_function, object_parameters
3756 INTO l_enter_from_task, l_object_function, l_object_parameters
3757 FROM JTF_OBJECTS_B
3758 WHERE OBJECT_CODE = p_dist_del_workitem_data.WORKITEM_OBJ_CODE;
3759 EXCEPTION
3760 when no_data_found then
3761 null;
3762 END;
3763
3764 x_uwqm_workitem_data(x_ctr).IEU_OBJECT_FUNCTION := l_object_function;
3765 x_uwqm_workitem_data(x_ctr).IEU_OBJECT_PARAMETERS := l_object_parameters;
3766 x_uwqm_workitem_data(x_ctr).IEU_PARAM_PK_VALUE := p_dist_del_workitem_data.WORKITEM_PK_ID;
3767 x_uwqm_workitem_data(x_ctr).IEU_PARAM_PK_COL := 'WORKITEM_PK_ID';
3768
3769
3770 END IF; /* SOURCE_OBJECT_TYPE_CODE is not null */
3771
3772 x_uwqm_workitem_data(x_ctr).IEU_MEDIA_TYPE_UUID := '';
3773 x_uwqm_workitem_data(x_ctr).WORK_ITEM_ID := p_dist_del_workitem_data.WORK_ITEM_ID;
3774 x_uwqm_workitem_data(x_ctr).WORKITEM_OBJ_CODE := p_dist_del_workitem_data.WORKITEM_OBJ_CODE;
3775 x_uwqm_workitem_data(x_ctr).WORKITEM_PK_ID := p_dist_del_workitem_data.WORKITEM_PK_ID;
3776 x_uwqm_workitem_data(x_ctr).STATUS_ID := p_dist_del_workitem_data.STATUS_ID;
3777 x_uwqm_workitem_data(x_ctr).PRIORITY_ID := p_dist_del_workitem_data.PRIORITY_ID;
3778 x_uwqm_workitem_data(x_ctr).PRIORITY_LEVEL := p_dist_del_workitem_data.PRIORITY_LEVEL;
3779 x_uwqm_workitem_data(x_ctr).DUE_DATE := p_dist_del_workitem_data.DUE_DATE;
3780 x_uwqm_workitem_data(x_ctr).TITLE := p_dist_del_workitem_data.TITLE;
3781 x_uwqm_workitem_data(x_ctr).PARTY_ID := p_dist_del_workitem_data.PARTY_ID;
3782 x_uwqm_workitem_data(x_ctr).OWNER_ID := p_dist_del_workitem_data.OWNER_ID;
3783 x_uwqm_workitem_data(x_ctr).OWNER_TYPE := p_dist_del_workitem_data.OWNER_TYPE;
3784 x_uwqm_workitem_data(x_ctr).ASSIGNEE_ID := p_dist_del_workitem_data.ASSIGNEE_ID;
3785 x_uwqm_workitem_data(x_ctr).ASSIGNEE_TYPE := p_dist_del_workitem_data.ASSIGNEE_TYPE;
3786 x_uwqm_workitem_data(x_ctr).SOURCE_OBJECT_ID := p_dist_del_workitem_data.SOURCE_OBJECT_ID;
3787 x_uwqm_workitem_data(x_ctr).SOURCE_OBJECT_TYPE_CODE := p_dist_del_workitem_data.SOURCE_OBJECT_TYPE_CODE;
3788 x_uwqm_workitem_data(x_ctr).APPLICATION_ID := p_dist_del_workitem_data.APPLICATION_ID;
3789 x_uwqm_workitem_data(x_ctr).IEU_ENUM_TYPE_UUID := p_dist_del_workitem_data.IEU_ENUM_TYPE_UUID;
3790 x_uwqm_workitem_data(x_ctr).WORK_ITEM_NUMBER := p_dist_del_workitem_data.WORK_ITEM_NUMBER;
3791 x_uwqm_workitem_data(x_ctr).RESCHEDULE_TIME := p_dist_del_workitem_data.RESCHEDULE_TIME;
3792 x_uwqm_workitem_data(x_ctr).IEU_GET_NEXTWORK_FLAG := 'Y';
3793 x_uwqm_workitem_data(x_ctr).IEU_ACTION_OBJECT_CODE := p_dist_del_workitem_data.SOURCE_OBJECT_TYPE_CODE;
3794 x_uwqm_workitem_data(x_ctr).WS_ID := p_dist_del_workitem_data.WS_ID;
3795 x_ctr := x_ctr + 1;
3796
3797 end if; /* p_var_in_type_code */
3798
3799 END SET_WR_ITEM_DATA_REC;
3800
3801
3802 /**
3803 ** Distribute Only returns the table of Records in a different format compared to Distribute and Deliver.
3804 ** This was required as Distribute Only can return multiple records. Distribute and Deliver requires the Return Record
3805 ** to be of type IEU_FRM_PVT.T_IEU_MEDIA_DATA for processing on the FORM.
3806 ** Called by PROCEDURE - DISTRIBUTE_AND_DELIVER_WR_ITEM
3807 ** The in var can be either a rec of type IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WR_ITEM_DATA_REC OR
3808 ** table of objects SYSTEM.WR_ITEM_DATA_NST
3809 ** The In var - p_var_in_type_code indicates if its a record - 'REC' or an object - 'OBJ'
3810 ** Copies the Work Item data from table of objects - SYSTEM.WR_ITEM_DATA_NST or rec - IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WR_ITEM_DATA_REC
3811 ** to table of records of type - IEU_FRM_PVT.T_IEU_MEDIA_DATA
3812 **/
3813
3814 PROCEDURE SET_DIST_AND_DEL_ITEM_DATA_REC( p_var_in_type_code IN VARCHAR2,
3815 p_dist_workitem_data IN SYSTEM.WR_ITEM_DATA_NST,
3816 p_dist_del_workitem_data IN IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WR_ITEM_DATA_REC,
3817 x_ctr IN OUT NOCOPY NUMBER,
3818 x_workitem_action_data IN OUT NOCOPY IEU_FRM_PVT.T_IEU_MEDIA_DATA) IS
3819
3820
3821 --l_ctr NUMBER := 0;
3822 l_enter_from_task VARCHAR2(1);
3823 l_object_function VARCHAR2(30);
3824 l_object_parameters VARCHAR2(2000);
3825 l_work_type VARCHAR2(80);
3826
3827 BEGIN
3828
3829 if (p_var_in_type_code = 'OBJ')
3830 then
3831
3832 -- If a work item was distributed, copy the Work Item data from table of obj - l_dist_workitem_data
3833 -- to table of Rec - x_uwqm_workitem_data
3834
3835 -- Changes reqd for object function and params
3836 -- Get the Object func and params based from JTF_OBJECTS
3837
3838 for n in 1 .. p_dist_workitem_data.count
3839 loop
3840
3841 IF ( p_dist_workitem_data(n).SOURCE_OBJECT_TYPE_CODE is not null)
3842 THEN
3843
3844 BEGIN
3845 SELECT enter_from_task, object_function, object_parameters
3846 INTO l_enter_from_task, l_object_function, l_object_parameters
3847 FROM JTF_OBJECTS_B
3848 WHERE OBJECT_CODE = p_dist_workitem_data(n).SOURCE_OBJECT_TYPE_CODE;
3849 EXCEPTION
3850 when no_data_found then
3851 null;
3852 END;
3853
3854 x_workitem_action_data(x_ctr).param_name := 'IEU_OBJECT_FUNCTION';
3855 x_workitem_action_data(x_ctr).param_value := l_object_function;
3856 x_workitem_action_data(x_ctr).param_type := 'CHAR';
3857 x_ctr := x_ctr + 1;
3858
3859 x_workitem_action_data(x_ctr).param_name := 'IEU_OBJECT_PARAMETERS';
3860 x_workitem_action_data(x_ctr).param_value := l_object_parameters;
3861 x_workitem_action_data(x_ctr).param_type := 'CHAR';
3862 x_ctr := x_ctr + 1;
3863
3864 x_workitem_action_data(x_ctr).param_name := 'IEU_PARAM_PK_VALUE';
3865 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).source_object_id;
3866 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
3867 x_ctr := x_ctr + 1;
3868
3869 x_workitem_action_data(x_ctr).param_name := 'IEU_PARAM_PK_COL';
3870 x_workitem_action_data(x_ctr).param_value := 'SOURCE_OBJECT_ID';
3871 x_workitem_action_data(x_ctr).param_type := '';
3872 x_ctr := x_ctr + 1;
3873
3874
3875 ELSIF (p_dist_workitem_data(n).SOURCE_OBJECT_TYPE_CODE is null)
3876 THEN
3877
3878 BEGIN
3879 SELECT enter_from_task, object_function, object_parameters
3880 INTO l_enter_from_task, l_object_function, l_object_parameters
3881 FROM JTF_OBJECTS_B
3882 WHERE OBJECT_CODE = p_dist_workitem_data(n).WORKITEM_OBJ_CODE;
3883 EXCEPTION
3884 when no_data_found then
3885 null;
3886 END;
3887
3888 x_workitem_action_data(x_ctr).param_name := 'IEU_OBJECT_FUNCTION';
3889 x_workitem_action_data(x_ctr).param_value := l_object_function;
3890 x_workitem_action_data(x_ctr).param_type := 'CHAR';
3891 x_ctr := x_ctr + 1;
3892
3893 x_workitem_action_data(x_ctr).param_name := 'IEU_OBJECT_PARAMETERS';
3894 x_workitem_action_data(x_ctr).param_value := l_object_parameters;
3895 x_workitem_action_data(x_ctr).param_type := 'CHAR';
3896 x_ctr := x_ctr + 1;
3897
3898 x_workitem_action_data(x_ctr).param_name := 'IEU_PARAM_PK_VALUE';
3899 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).workitem_pk_id;
3900 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
3901 x_ctr := x_ctr + 1;
3902
3903 x_workitem_action_data(x_ctr).param_name := 'IEU_PARAM_PK_COL';
3904 x_workitem_action_data(x_ctr).param_value := 'WORKITEM_PK_ID';
3905 x_workitem_action_data(x_ctr).param_type := '';
3906 x_ctr := x_ctr + 1;
3907
3908
3909 END IF; /* SOURCE_OBJECT_TYPE_CODE is not null */
3910
3911
3912 x_workitem_action_data(x_ctr).param_name := 'IEU_MEDIA_TYPE_UUID';
3913 x_workitem_action_data(x_ctr).param_value := '';
3914 x_workitem_action_data(x_ctr).param_type := '';
3915 x_ctr := x_ctr + 1;
3916
3917 x_workitem_action_data(x_ctr).param_name := 'WORK_ITEM_ID';
3918 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).WORK_ITEM_ID;
3919 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
3920 x_ctr := x_ctr + 1;
3921
3922 x_workitem_action_data(x_ctr).param_name := 'WORKITEM_OBJ_CODE';
3923 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).WORKITEM_OBJ_CODE;
3924 x_workitem_action_data(x_ctr).param_type := 'CHAR';
3925 x_ctr := x_ctr + 1;
3926
3927 x_workitem_action_data(x_ctr).param_name := 'WORKITEM_PK_ID';
3928 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).WORKITEM_PK_ID;
3929 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
3930 x_ctr := x_ctr + 1;
3931
3932 x_workitem_action_data(x_ctr).param_name := 'STATUS_ID';
3933 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).WORK_ITEM_STATUS;
3934 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
3935 x_ctr := x_ctr + 1;
3936
3937 x_workitem_action_data(x_ctr).param_name := 'PRIORITY_ID';
3938 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).PRIORITY_ID;
3939 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
3940 x_ctr := x_ctr + 1;
3941
3942 x_workitem_action_data(x_ctr).param_name := 'PRIORITY_LEVEL';
3943 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).PRIORITY_LEVEL;
3944 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
3945 x_ctr := x_ctr + 1;
3946
3947 x_workitem_action_data(x_ctr).param_name := 'DUE_DATE';
3948 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).DUE_DATE;
3949 x_workitem_action_data(x_ctr).param_type := 'DATE';
3950 x_ctr := x_ctr + 1;
3951
3952 x_workitem_action_data(x_ctr).param_name := 'TITLE';
3953 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).TITLE;
3954 x_workitem_action_data(x_ctr).param_type := 'CHAR';
3955 x_ctr := x_ctr + 1;
3956
3957 x_workitem_action_data(x_ctr).param_name := 'PARTY_ID';
3958 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).PARTY_ID;
3959 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
3960 x_ctr := x_ctr + 1;
3961
3962 x_workitem_action_data(x_ctr).param_name := 'OWNER_TYPE';
3963 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).OWNER_TYPE;
3964 x_workitem_action_data(x_ctr).param_type := 'CHAR';
3965 x_ctr := x_ctr + 1;
3966
3967 x_workitem_action_data(x_ctr).param_name := 'OWNER_ID';
3968 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).OWNER_ID;
3969 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
3970 x_ctr := x_ctr + 1;
3971
3972 x_workitem_action_data(x_ctr).param_name := 'ASSIGNEE_TYPE';
3973 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).ASSIGNEE_TYPE;
3974 x_workitem_action_data(x_ctr).param_type := 'CHAR';
3975 x_ctr := x_ctr + 1;
3976
3977 x_workitem_action_data(x_ctr).param_name := 'ASSIGNEE_ID';
3978 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).ASSIGNEE_ID;
3979 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
3980 x_ctr := x_ctr + 1;
3981 /*
3982 x_workitem_action_data(x_ctr).param_name := 'OWNER_TYPE_ACTUAL';
3983 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).OWNER_TYPE_ACTUAL;
3984 x_workitem_action_data(x_ctr).param_type := 'CHAR';
3985 x_ctr := x_ctr + 1;
3986
3987 x_workitem_action_data(x_ctr).param_name := 'ASSIGNEE_TYPE_ACTUAL';
3988 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).ASSIGNEE_TYPE_ACTUAL;
3989 x_workitem_action_data(x_ctr).param_type := 'CHAR';
3990 x_ctr := x_ctr + 1;
3991 */
3992 x_workitem_action_data(x_ctr).param_name := 'SOURCE_OBJECT_ID';
3993 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).SOURCE_OBJECT_ID;
3994 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
3995 x_ctr := x_ctr + 1;
3996
3997 x_workitem_action_data(x_ctr).param_name := 'SOURCE_OBJECT_TYPE_CODE';
3998 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).SOURCE_OBJECT_TYPE_CODE;
3999 x_workitem_action_data(x_ctr).param_type := 'CHAR';
4000 x_ctr := x_ctr + 1;
4001
4002 x_workitem_action_data(x_ctr).param_name := 'APPLICATION_ID';
4003 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).APPLICATION_ID;
4004 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
4005 x_ctr := x_ctr + 1;
4006
4007 x_workitem_action_data(x_ctr).param_name := 'IEU_ACTION_OBJECT_CODE';
4008 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).SOURCE_OBJECT_TYPE_CODE;
4009 x_workitem_action_data(x_ctr).param_type := 'CHAR';
4010 x_ctr := x_ctr + 1;
4011
4012 x_workitem_action_data(x_ctr).param_name := 'IEU_GET_NEXTWORK_FLAG';
4013 x_workitem_action_data(x_ctr).param_value := 'Y';
4014 x_workitem_action_data(x_ctr).param_type := 'CHAR';
4015 x_ctr := x_ctr + 1;
4016
4017 x_workitem_action_data(x_ctr).param_name := 'RESCHEDULE_TIME';
4018 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).RESCHEDULE_TIME;
4019 x_workitem_action_data(x_ctr).param_type := 'DATE';
4020 x_ctr := x_ctr + 1;
4021
4022 x_workitem_action_data(x_ctr).param_name := 'IEU_ENUM_TYPE_UUID';
4023 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).IEU_ENUM_TYPE_UUID;
4024 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
4025 x_ctr := x_ctr + 1;
4026
4027 BEGIN
4028
4029 SELECT LKUPS.MEANING
4030 INTO L_WORK_TYPE
4031 FROM FND_LOOKUP_VALUES_VL LKUPS, IEU_UWQ_SEL_ENUMERATORS ENUM
4032 WHERE ENUM.ENUM_TYPE_UUID = p_dist_workitem_data(n).IEU_ENUM_TYPE_UUID
4033 AND LKUPS.LOOKUP_TYPE(+) = ENUM.WORK_Q_LABEL_LU_TYPE
4034 AND LKUPS.VIEW_APPLICATION_ID(+) = ENUM.APPLICATION_ID
4035 AND LKUPS.LOOKUP_CODE(+) = WORK_Q_LABEL_LU_CODE;
4036
4037 EXCEPTION
4038 WHEN NO_DATA_FOUND THEN
4039 NULL;
4040 END;
4041
4042 x_workitem_action_data(x_ctr).param_name := 'WORK_TYPE';
4043 x_workitem_action_data(x_ctr).param_value := L_WORK_TYPE;
4044 x_workitem_action_data(x_ctr).param_type := 'VARCHAR2';
4045 x_ctr := x_ctr + 1;
4046
4047 end loop;/* p_dist_workitem_data */
4048
4049 elsif (p_var_in_type_code = 'REC')
4050 then
4051
4052 -- If a work item was distributed, copy the Work Item data from table of obj - l_dist_workitem_data
4053 -- to table of Rec - x_uwqm_workitem_data
4054
4055 -- Changes reqd for object function and params
4056 -- Get the Object func and params based from JTF_OBJECTS
4057
4058 IF (p_dist_del_workitem_data.SOURCE_OBJECT_TYPE_CODE is not null)
4059 THEN
4060
4061
4062 BEGIN
4063 SELECT enter_from_task, object_function, object_parameters
4064 INTO l_enter_from_task, l_object_function, l_object_parameters
4065 FROM JTF_OBJECTS_B
4066 WHERE OBJECT_CODE = p_dist_del_workitem_data.SOURCE_OBJECT_TYPE_CODE;
4067 EXCEPTION
4068 when no_data_found then
4069 null;
4070 END;
4071
4072 x_workitem_action_data(x_ctr).param_name := 'IEU_OBJECT_FUNCTION';
4073 x_workitem_action_data(x_ctr).param_value := l_object_function;
4074 x_workitem_action_data(x_ctr).param_type := 'CHAR';
4075 x_ctr := x_ctr + 1;
4076
4077 x_workitem_action_data(x_ctr).param_name := 'IEU_OBJECT_PARAMETERS';
4078 x_workitem_action_data(x_ctr).param_value := l_object_parameters;
4079 x_workitem_action_data(x_ctr).param_type := 'CHAR';
4080 x_ctr := x_ctr + 1;
4081
4082 x_workitem_action_data(x_ctr).param_name := 'IEU_PARAM_PK_VALUE';
4083 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.source_object_id;
4084 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
4085 x_ctr := x_ctr + 1;
4086
4087 x_workitem_action_data(x_ctr).param_name := 'IEU_PARAM_PK_COL';
4088 x_workitem_action_data(x_ctr).param_value := 'SOURCE_OBJECT_ID';
4089 x_workitem_action_data(x_ctr).param_type := '';
4090 x_ctr := x_ctr + 1;
4091
4092
4093 ELSIF (p_dist_del_workitem_data.SOURCE_OBJECT_TYPE_CODE is null)
4094 THEN
4095
4096 BEGIN
4097 SELECT enter_from_task, object_function, object_parameters
4098 INTO l_enter_from_task, l_object_function, l_object_parameters
4099 FROM JTF_OBJECTS_B
4100 WHERE OBJECT_CODE = p_dist_del_workitem_data.WORKITEM_OBJ_CODE;
4101 EXCEPTION
4102 when no_data_found then
4103 null;
4104 END;
4105
4106 x_workitem_action_data(x_ctr).param_name := 'IEU_OBJECT_FUNCTION';
4107 x_workitem_action_data(x_ctr).param_value := l_object_function;
4108 x_workitem_action_data(x_ctr).param_type := 'CHAR';
4109 x_ctr := x_ctr + 1;
4110
4111 x_workitem_action_data(x_ctr).param_name := 'IEU_OBJECT_PARAMETERS';
4112 x_workitem_action_data(x_ctr).param_value := l_object_parameters;
4113 x_workitem_action_data(x_ctr).param_type := 'CHAR';
4114 x_ctr := x_ctr + 1;
4115
4116 x_workitem_action_data(x_ctr).param_name := 'IEU_PARAM_PK_VALUE';
4117 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.workitem_pk_id;
4118 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
4119 x_ctr := x_ctr + 1;
4120
4121 x_workitem_action_data(x_ctr).param_name := 'IEU_PARAM_PK_COL';
4122 x_workitem_action_data(x_ctr).param_value := 'WORKITEM_PK_ID';
4123 x_workitem_action_data(x_ctr).param_type := '';
4124 x_ctr := x_ctr + 1;
4125
4126
4127 END IF; /* SOURCE_OBJECT_TYPE_CODE is not null */
4128
4129
4130 x_workitem_action_data(x_ctr).param_name := 'IEU_MEDIA_TYPE_UUID';
4131 x_workitem_action_data(x_ctr).param_value := '';
4132 x_workitem_action_data(x_ctr).param_type := '';
4133 x_ctr := x_ctr + 1;
4134
4135 x_workitem_action_data(x_ctr).param_name := 'WORK_ITEM_ID';
4136 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.WORK_ITEM_ID;
4137 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
4138 x_ctr := x_ctr + 1;
4139
4140 x_workitem_action_data(x_ctr).param_name := 'WORKITEM_OBJ_CODE';
4141 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.WORKITEM_OBJ_CODE;
4142 x_workitem_action_data(x_ctr).param_type := 'CHAR';
4143 x_ctr := x_ctr + 1;
4144
4145 x_workitem_action_data(x_ctr).param_name := 'WORKITEM_PK_ID';
4146 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.WORKITEM_PK_ID;
4147 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
4148 x_ctr := x_ctr + 1;
4149
4150 x_workitem_action_data(x_ctr).param_name := 'STATUS_ID';
4151 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.STATUS_ID;
4152 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
4153 x_ctr := x_ctr + 1;
4154
4155 x_workitem_action_data(x_ctr).param_name := 'PRIORITY_ID';
4156 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.PRIORITY_ID;
4157 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
4158 x_ctr := x_ctr + 1;
4159
4160 x_workitem_action_data(x_ctr).param_name := 'PRIORITY_LEVEL';
4161 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.PRIORITY_LEVEL;
4162 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
4163 x_ctr := x_ctr + 1;
4164
4165 x_workitem_action_data(x_ctr).param_name := 'DUE_DATE';
4166 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.DUE_DATE;
4167 x_workitem_action_data(x_ctr).param_type := 'DATE';
4168 x_ctr := x_ctr + 1;
4169
4170 x_workitem_action_data(x_ctr).param_name := 'TITLE';
4171 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.TITLE;
4172 x_workitem_action_data(x_ctr).param_type := 'CHAR';
4173 x_ctr := x_ctr + 1;
4174
4175 x_workitem_action_data(x_ctr).param_name := 'PARTY_ID';
4176 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.PARTY_ID;
4177 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
4178 x_ctr := x_ctr + 1;
4179
4180 x_workitem_action_data(x_ctr).param_name := 'OWNER_TYPE';
4181 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.OWNER_TYPE;
4182 x_workitem_action_data(x_ctr).param_type := 'CHAR';
4183 x_ctr := x_ctr + 1;
4184
4185 x_workitem_action_data(x_ctr).param_name := 'OWNER_ID';
4186 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.OWNER_ID;
4187 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
4188 x_ctr := x_ctr + 1;
4189
4190 x_workitem_action_data(x_ctr).param_name := 'ASSIGNEE_TYPE';
4191 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.ASSIGNEE_TYPE;
4192 x_workitem_action_data(x_ctr).param_type := 'CHAR';
4193 x_ctr := x_ctr + 1;
4194
4195 x_workitem_action_data(x_ctr).param_name := 'ASSIGNEE_ID';
4196 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.ASSIGNEE_ID;
4197 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
4198 x_ctr := x_ctr + 1;
4199 /*
4200 x_workitem_action_data(x_ctr).param_name := 'OWNER_TYPE_ACTUAL';
4201 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.OWNER_TYPE_ACTUAL;
4202 x_workitem_action_data(x_ctr).param_type := 'CHAR';
4203 x_ctr := x_ctr + 1;
4204
4205 x_workitem_action_data(x_ctr).param_name := 'ASSIGNEE_TYPE_ACTUAL';
4206 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.ASSIGNEE_TYPE_ACTUAL;
4207 x_workitem_action_data(x_ctr).param_type := 'CHAR';
4208 x_ctr := x_ctr + 1;
4209 */
4210 x_workitem_action_data(x_ctr).param_name := 'SOURCE_OBJECT_ID';
4211 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.SOURCE_OBJECT_ID;
4212 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
4213 x_ctr := x_ctr + 1;
4214
4215 x_workitem_action_data(x_ctr).param_name := 'SOURCE_OBJECT_TYPE_CODE';
4216 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.SOURCE_OBJECT_TYPE_CODE;
4217 x_workitem_action_data(x_ctr).param_type := 'CHAR';
4218 x_ctr := x_ctr + 1;
4219
4220 x_workitem_action_data(x_ctr).param_name := 'APPLICATION_ID';
4221 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.APPLICATION_ID;
4222 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
4223 x_ctr := x_ctr + 1;
4224
4225 x_workitem_action_data(x_ctr).param_name := 'IEU_ACTION_OBJECT_CODE';
4226 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.SOURCE_OBJECT_TYPE_CODE;
4227 x_workitem_action_data(x_ctr).param_type := 'CHAR';
4228 x_ctr := x_ctr + 1;
4229
4230 x_workitem_action_data(x_ctr).param_name := 'IEU_GET_NEXTWORK_FLAG';
4231 x_workitem_action_data(x_ctr).param_value := 'Y';
4232 x_workitem_action_data(x_ctr).param_type := 'CHAR';
4233 x_ctr := x_ctr + 1;
4234
4235 x_workitem_action_data(x_ctr).param_name := 'RESCHEDULE_TIME';
4236 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.RESCHEDULE_TIME;
4237 x_workitem_action_data(x_ctr).param_type := 'DATE';
4238 x_ctr := x_ctr + 1;
4239
4240 x_workitem_action_data(x_ctr).param_name := 'IEU_ENUM_TYPE_UUID';
4241 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.IEU_ENUM_TYPE_UUID;
4242 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
4243 x_ctr := x_ctr + 1;
4244
4245 BEGIN
4246
4247 SELECT LKUPS.MEANING
4248 INTO L_WORK_TYPE
4249 FROM FND_LOOKUP_VALUES_VL LKUPS, IEU_UWQ_SEL_ENUMERATORS ENUM
4250 WHERE ENUM.ENUM_TYPE_UUID = p_dist_del_workitem_data.IEU_ENUM_TYPE_UUID
4251 AND LKUPS.LOOKUP_TYPE(+) = ENUM.WORK_Q_LABEL_LU_TYPE
4252 AND LKUPS.VIEW_APPLICATION_ID(+) = ENUM.APPLICATION_ID
4253 AND LKUPS.LOOKUP_CODE(+) = WORK_Q_LABEL_LU_CODE;
4254
4255 EXCEPTION
4256 WHEN NO_DATA_FOUND THEN
4257 NULL;
4258 END;
4259
4260 x_workitem_action_data(x_ctr).param_name := 'WORK_TYPE';
4261 x_workitem_action_data(x_ctr).param_value := L_WORK_TYPE;
4262 x_workitem_action_data(x_ctr).param_type := 'VARCHAR2';
4263 x_ctr := x_ctr + 1;
4264
4265 end if; /* p_var_in_type_code */
4266
4267 END SET_DIST_AND_DEL_ITEM_DATA_REC;
4268
4269 /**
4270 ** Called by PROCEDURE - GET_NEXT_WORK_FOR_APPS
4271 ** Sets the where clause based on business rules like ws_id, distribute_to and distribute_from
4272 ** This extra where clause will be appened to actual where clause to fetch the set of distributable items
4273 **/
4274 PROCEDURE GET_WS_WHERE_CLAUSE
4275 (p_type IN VARCHAR2,
4276 p_ws_det_list IN IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WS_DETAILS_LIST,
4277 p_resource_id IN NUMBER,
4278 x_dist_from_where OUT NOCOPY VARCHAR2,
4279 x_dist_to_where OUT NOCOPY VARCHAR2,
4280 x_bindvar_from_list OUT NOCOPY IEU_UWQ_BINDVAR_LIST,
4281 x_bindvar_to_list OUT NOCOPY IEU_UWQ_BINDVAR_LIST) IS
4282
4283 /*
4284 cursor C1 is
4285 select WS_B.WS_ID, WS_B.DISTRIBUTE_TO, WS_B.DISTRIBUTE_FROM , WS_B.DISTRIBUTION_FUNCTION
4286 from IEU_UWQM_WORK_SOURCES_B WS_B
4287 where ws_b.not_valid_flag = 'N';
4288 */
4289
4290 l_dist_from IEU_UWQM_WORK_SOURCES_B.DISTRIBUTE_FROM%TYPE;
4291 l_dist_to IEU_UWQM_WORK_SOURCES_B.DISTRIBUTE_TO%TYPE;
4292 l_ws_id IEU_UWQM_WORK_SOURCES_B.WS_ID%TYPE;
4293
4294 -- Variables for Distribute_from
4295
4296 l_df_own_where_clause varchar2(4000);
4297 l_df_asg_where_clause varchar2(4000);
4298
4299 l_df_own_ws_clause varchar2(4000);
4300 l_df_own_ws_clause1 varchar2(4000);
4301 l_df_asg_ws_clause varchar2(4000);
4302 l_df_asg_ws_clause1 varchar2(4000);
4303
4304 l_df_final_where varchar2(4000);
4305
4306 l_df_grp_own_ctr number := 0;
4307 l_df_grp_asg_ctr number := 0;
4308
4309 -- Variables for Distribute_to
4310
4311 l_dt_own_where_clause varchar2(4000);
4312 l_dt_asg_where_clause varchar2(4000);
4313
4314 l_dt_own_ws_clause varchar2(4000);
4315 l_dt_own_ws_clause1 varchar2(4000);
4316 l_dt_asg_ws_clause varchar2(4000);
4317 l_dt_asg_ws_clause1 varchar2(4000);
4318
4319 l_dt_final_where varchar2(4000);
4320
4321 l_dt_grp_own_ctr number := 0;
4322 l_dt_grp_asg_ctr number := 0;
4323
4324 z number := 1;
4325 p_grp_id_list IEU_UWQ_GET_NEXT_WORK_PVT.IEU_GRP_ID_LIST;
4326 l_df_grp_id_clause varchar2(4000);
4327 l_df_grp_id_ctr number := 0;
4328
4329 l_delete_flag_yes varchar2(1);
4330
4331 cursor c_grp_id(p_resource_id in number) is
4332 select group_id from jtf_rs_group_members
4333 where resource_id = p_resource_id
4334 and nvl(delete_flag, 'N') <> l_delete_flag_yes;
4335
4336 l_not_valid_flag VARCHAR2(1);
4337
4338 l_bindvar_fm_ctr number;
4339 l_bindvar_to_ctr number;
4340 t number;
4341
4342 l_fm_group_owned_flag varchar2(1) := 'F';
4343 l_fm_group_assigned_flag varchar2(1) := 'F';
4344 l_to_ind_owned_flag varchar2(1) := 'F';
4345 l_to_ind_assigned_flag varchar2(1) := 'F';
4346
4347
4348 BEGIN
4349
4350
4351 l_dist_from := 'GROUP_OWNED';
4352 l_dist_to := 'INDIVIDUAL_ASSIGNED';
4353 l_delete_flag_yes := 'Y';
4354 l_bindvar_fm_ctr := 0;
4355 l_bindvar_to_ctr := 0;
4356
4357 /* performance issues with the query and try three different approach and using the one that is giving better performance
4358 1. owner_id in (select group_id from jtf_rs_group_members
4359 where resource_id = :resource_id
4360 and nvl(delete_flag,'N') <> 'Y');
4361 2. exists (select 1 from jtf_rs_group_members
4362 where resource_id = :resource_id
4363 and nvl(delete_flag,'N') <> 'Y');
4364 3. owner_id in (group_id1, group_id2, group_id3); - Explicitly passing the string.
4365
4366 Using # 3 approach so, the following loop is getting the group_ids for that resource_id and building the
4367 string: if only one group_id then string would be 'owner_id = group_id1' if no group_id then owner_id = ''
4368 if more than one group_ids then 'owner_id in (group_id1, group_id2...group_idx)'
4369
4370 Note: Right now, this approch is only applied for GROUP_OWNED because GROUP_ASSIGNED is not being used. In the future when
4371 GROUP_ASSIGNED is used then should apply the same logic to build the string.
4372 */
4373
4374 for grp_id in c_grp_id(p_resource_id)
4375 loop
4376 p_grp_id_list(z).group_id := grp_id.group_id;
4377 z := z + 1;
4378 end loop;
4379
4380 if p_grp_id_list.count = 0 then
4381 l_bindvar_fm_ctr := l_bindvar_fm_ctr + 1;
4382 l_df_grp_id_clause := 'owner_id in ('||':owner_id'||l_bindvar_fm_ctr||')';
4383 x_bindvar_from_list(l_bindvar_fm_ctr).bind_name := ':owner_id'||l_bindvar_fm_ctr;
4384 x_bindvar_from_list(l_bindvar_fm_ctr).value :='';
4385
4386 elsif p_grp_id_list.count > 0 then
4387 for x in p_grp_id_list.first..p_grp_id_list.last
4388 loop
4389
4390 if ((p_grp_id_list.count = 1) and (l_df_grp_id_ctr = 0)) then
4391 l_bindvar_fm_ctr := l_bindvar_fm_ctr + 1;
4392 x_bindvar_from_list(l_bindvar_fm_ctr).bind_name := ':owner_id'||l_bindvar_fm_ctr;
4393 x_bindvar_from_list(l_bindvar_fm_ctr).value := p_grp_id_list(x).group_id;
4394
4395 l_df_grp_id_clause := 'owner_id = '||':owner_id'||l_bindvar_fm_ctr;
4396
4397 elsif p_grp_id_list.count > 1 then
4398 if l_df_grp_id_ctr = 0 then
4399 l_bindvar_fm_ctr := l_bindvar_fm_ctr + 1;
4400 x_bindvar_from_list(l_bindvar_fm_ctr).bind_name := ':owner_id'||l_bindvar_fm_ctr;
4401 x_bindvar_from_list(l_bindvar_fm_ctr).value := p_grp_id_list(x).group_id;
4402 l_df_grp_id_clause := 'owner_id in ('||':owner_id'||l_bindvar_fm_ctr;
4403 l_df_grp_id_ctr := l_df_grp_id_ctr + 1;
4404 else
4405 l_bindvar_fm_ctr := l_bindvar_fm_ctr + 1;
4406 x_bindvar_from_list(l_bindvar_fm_ctr).bind_name := ':owner_id'||l_bindvar_fm_ctr;
4407 x_bindvar_from_list(l_bindvar_fm_ctr).value := p_grp_id_list(x).group_id;
4408 l_df_grp_id_clause := l_df_grp_id_clause||', '||':owner_id'||l_bindvar_fm_ctr;
4409 l_df_grp_id_ctr := l_df_grp_id_ctr + 1;
4410 end if;
4411 end if;
4412 if l_df_grp_id_ctr = p_grp_id_list.count then
4413 l_df_grp_id_clause := l_df_grp_id_clause||')';
4414 end if;
4415 end loop;
4416 end if;
4417 -- insert into p_temp values('final grp where clause '||l_df_grp_id_clause, 101);commit;
4418
4419 for i in p_ws_det_list.first .. p_ws_det_list.last
4420 loop
4421
4422 -- This will not throw any exception here, as the ws_code will be validated in the public api before calling
4423 -- this procedure.
4424
4425 BEGIN
4426 l_not_valid_flag := 'N';
4427 select WS_B.WS_ID
4428 into l_ws_id
4429 from IEU_UWQM_WORK_SOURCES_B WS_B
4430 where ws_code = p_ws_det_list(i).ws_code
4431 -- and ws_b.not_valid_flag = 'N';
4432 and ws_b.not_valid_flag = l_not_valid_flag;
4433 EXCEPTION
4434 when others then
4435 null;
4436 END;
4437
4438 -- Group Owned
4439 if (l_dist_from= 'GROUP_OWNED')
4440 then
4441
4442 -- Build the Work Source Where clause
4443 -- If this is the 1st WS, then where clause should be ws_id = :1
4444 -- else use ws_id in (:1,:2,..)
4445
4446 if (l_df_grp_own_ctr = 0)
4447 then
4448 l_bindvar_fm_ctr := l_bindvar_fm_ctr + 1;
4449 x_bindvar_from_list(l_bindvar_fm_ctr).bind_name := ':ws_id'||l_bindvar_fm_ctr;
4450 x_bindvar_from_list(l_bindvar_fm_ctr).value := l_ws_id;
4451
4452 l_df_own_ws_clause1 := ' ws_id = '||':ws_id'||l_bindvar_fm_ctr;
4453 l_df_own_ws_clause := ' ws_id in ('||':ws_id'||l_bindvar_fm_ctr;
4454 l_df_grp_own_ctr := l_df_grp_own_ctr + 1;
4455 else
4456 l_bindvar_fm_ctr := l_bindvar_fm_ctr + 1;
4457 x_bindvar_from_list(l_bindvar_fm_ctr).bind_name := ':ws_id'||l_bindvar_fm_ctr;
4458 x_bindvar_from_list(l_bindvar_fm_ctr).value := l_ws_id;
4459 l_df_own_ws_clause := l_df_own_ws_clause || ', '||':ws_id'||l_bindvar_fm_ctr;
4460 l_df_grp_own_ctr := l_df_grp_own_ctr + 1;
4461 end if;
4462
4463 if l_fm_group_owned_flag = 'F' then
4464 l_bindvar_fm_ctr := l_bindvar_fm_ctr + 1;
4465 x_bindvar_from_list(l_bindvar_fm_ctr).bind_name := ':owner_type'||l_bindvar_fm_ctr;
4466 x_bindvar_from_list(l_bindvar_fm_ctr).value := 'RS_GROUP';
4467 -- Build the complete Grp Own Where clause
4468 l_df_own_where_clause := ' owner_type = '||':owner_type'||l_bindvar_fm_ctr||
4469 ' and '||l_df_grp_id_clause;
4470 l_fm_group_owned_flag := 'T';
4471 end if;
4472
4473 -- insert into p_temp values(' Dist from group owned '||l_df_own_ws_clause1||' '||l_df_own_ws_clause||' '
4474 -- ||l_df_own_where_clause, l_df_grp_own_ctr);commit;
4475
4476 end if;
4477
4478 -- Group Assigned
4479 if (l_dist_from= 'GROUP_ASSIGNED')
4480 then
4481
4482 -- Build the Work Source Where clause
4483 -- If this is the 1st WS, then where clause should be ws_id = :1
4484 -- else use ws_id in (:1,:2,..)
4485 if (l_df_grp_asg_ctr = 0)
4486 then
4487 l_bindvar_fm_ctr := l_bindvar_fm_ctr + 1;
4488 x_bindvar_from_list(l_bindvar_fm_ctr).bind_name := ':ws_id'||l_bindvar_fm_ctr;
4489 x_bindvar_from_list(l_bindvar_fm_ctr).value := l_ws_id;
4490 l_df_asg_ws_clause1 := ' ws_id = '||'ws_id'||l_bindvar_fm_ctr;
4491 l_df_asg_ws_clause := ' ws_id in ('||'ws_id'||l_bindvar_fm_ctr;
4492 l_df_grp_asg_ctr := l_df_grp_asg_ctr + 1;
4493 else
4494 l_bindvar_fm_ctr := l_bindvar_fm_ctr + 1;
4495 x_bindvar_from_list(l_bindvar_fm_ctr).bind_name := ':ws_id'||l_bindvar_fm_ctr;
4496 x_bindvar_from_list(l_bindvar_fm_ctr).value := l_ws_id;
4497 l_df_asg_ws_clause := l_df_asg_ws_clause || ', '||'ws_id'||l_bindvar_fm_ctr;
4498 l_df_grp_asg_ctr := l_df_grp_asg_ctr + 1;
4499 end if;
4500
4501 if l_fm_group_assigned_flag = 'F' then
4502 l_bindvar_fm_ctr := l_bindvar_fm_ctr + 1;
4503
4504 x_bindvar_from_list(l_bindvar_fm_ctr).bind_name := ':assignee_type'||l_bindvar_fm_ctr;
4505 x_bindvar_from_list(l_bindvar_fm_ctr).value := 'RS_GROUP';
4506
4507 l_bindvar_fm_ctr := l_bindvar_fm_ctr + 1;
4508
4509 x_bindvar_from_list(l_bindvar_fm_ctr).bind_name := ':delete_flag'||l_bindvar_fm_ctr;
4510 x_bindvar_from_list(l_bindvar_fm_ctr).value := 'N';
4511
4512
4513 l_bindvar_fm_ctr := l_bindvar_fm_ctr + 1;
4514
4515 x_bindvar_from_list(l_bindvar_fm_ctr).bind_name := ':delete_flag'||l_bindvar_fm_ctr;
4516 x_bindvar_from_list(l_bindvar_fm_ctr).value := 'Y';
4517
4518
4519
4520 -- Build the complete Grp Asg Where clause
4521 l_df_asg_where_clause := ' assignee_type = '||':assignee_type'||(l_bindvar_fm_ctr-2)||
4522 ' and assignee_id in
4523 (select group_id from jtf_rs_group_members
4524 where resource_id = :resource_id'||
4525 ' and nvl(delete_flag,'||':delete_flag'||(l_bindvar_fm_ctr-1)||') <> '||':delete_flag'||l_bindvar_fm_ctr||')';
4526
4527 l_fm_group_assigned_flag := 'T';
4528 end if;
4529
4530 end if;
4531
4532
4533
4534 -- Distribute_To
4535
4536 if (p_type = 'DELIVER')
4537 then
4538
4539 -- Group Owned
4540 if (l_dist_to = 'INDIVIDUAL_OWNED')
4541 then
4542
4543 -- Build the Work Source Where clause
4544 -- If this is the 1st WS, then where clause should be ws_id = :1
4545 -- else use ws_id in (:1,:2,..)
4546 if (l_dt_grp_own_ctr = 0)
4547 then
4548 l_bindvar_to_ctr := l_bindvar_to_ctr + 1;
4549
4550 x_bindvar_to_list(l_bindvar_to_ctr).bind_name := ':ws_id'||l_bindvar_to_ctr;
4551 x_bindvar_to_list(l_bindvar_to_ctr).value := l_ws_id;
4552 l_dt_own_ws_clause1 := ' ws_id = '||':ws_id'||l_bindvar_to_ctr;
4553 l_dt_own_ws_clause := ' ws_id in ('||':ws_id'||l_bindvar_to_ctr;
4554 l_dt_grp_own_ctr := l_dt_grp_own_ctr + 1;
4555 else
4556 l_bindvar_to_ctr := l_bindvar_to_ctr + 1;
4557
4558 x_bindvar_to_list(l_bindvar_to_ctr).bind_name := ':ws_id'||l_bindvar_to_ctr;
4559 x_bindvar_to_list(l_bindvar_to_ctr).value := l_ws_id;
4560 l_dt_own_ws_clause := l_dt_own_ws_clause || ', '||':ws_id'||l_bindvar_to_ctr;
4561 l_dt_grp_own_ctr := l_dt_grp_own_ctr + 1;
4562 end if;
4563
4564 if l_to_ind_owned_flag = 'F' then
4565 l_bindvar_to_ctr := l_bindvar_to_ctr + 1;
4566
4567 x_bindvar_to_list(l_bindvar_to_ctr).bind_name := ':owner_type'||l_bindvar_to_ctr;
4568 x_bindvar_to_list(l_bindvar_to_ctr).value := 'RS_INDIVIDUAL';
4569
4570 -- Build the complete Grp Own Where clause
4571 l_dt_own_where_clause := ' owner_type = '||':owner_type'||l_bindvar_to_ctr||
4572 ' and owner_id = :resource_id';
4573 l_to_ind_owned_flag := 'T' ;
4574 end if;
4575
4576
4577 end if;
4578
4579
4580 -- Group Assigned
4581 if (l_dist_to = 'INDIVIDUAL_ASSIGNED')
4582 then
4583
4584 -- Build the Work Source Where clause
4585 -- If this is the 1st WS, then where clause should be ws_id = :1
4586 -- else use ws_id in (:1,:2,..)
4587
4588 if (l_dt_grp_asg_ctr = 0)
4589 then
4590 l_bindvar_to_ctr := l_bindvar_to_ctr + 1;
4591
4592 x_bindvar_to_list(l_bindvar_to_ctr).bind_name := ':ws_id'||l_bindvar_to_ctr;
4593 x_bindvar_to_list(l_bindvar_to_ctr).value := l_ws_id;
4594 l_dt_asg_ws_clause1 := ' ws_id = '||':ws_id'||l_bindvar_to_ctr;
4595 l_dt_asg_ws_clause := ' ws_id in ('||':ws_id'||l_bindvar_to_ctr;
4596 l_dt_grp_asg_ctr := l_dt_grp_asg_ctr + 1;
4597 else
4598 l_bindvar_to_ctr := l_bindvar_to_ctr + 1;
4599
4600 x_bindvar_to_list(l_bindvar_to_ctr).bind_name := ':ws_id'||l_bindvar_to_ctr;
4601 x_bindvar_to_list(l_bindvar_to_ctr).value := l_ws_id;
4602 l_dt_asg_ws_clause := l_dt_asg_ws_clause || ', '||':ws_id'||l_bindvar_to_ctr;
4603 l_dt_grp_asg_ctr := l_dt_grp_asg_ctr + 1;
4604 end if;
4605 if l_to_ind_assigned_flag = 'F' then
4606 l_bindvar_to_ctr := l_bindvar_to_ctr + 1;
4607
4608 x_bindvar_to_list(l_bindvar_to_ctr).bind_name := ':assignee_type'||l_bindvar_to_ctr;
4609 x_bindvar_to_list(l_bindvar_to_ctr).value := 'RS_INDIVIDUAL';
4610
4611 -- Build the complete Grp Asg Where clause
4612 l_dt_asg_where_clause := ' assignee_type = '||':assignee_type'||l_bindvar_to_ctr||
4613 ' and assignee_id = :resource_id';
4614
4615 -- insert into p_temp values('dist to individual assigned '||l_df_asg_ws_clause1||' '||l_df_asg_ws_clause||' '
4616 -- ||l_df_asg_where_clause, l_df_grp_asg_ctr);commit;
4617 l_to_ind_assigned_flag := 'T';
4618 end if;
4619
4620
4621 end if;
4622
4623 end if; /* p_type = Deliver */
4624
4625 end loop; /* p_ws_det_list.first . p_ws_det_list.last */
4626
4627
4628 ---------------- **************** Built The where Clause for Distribute_from **************** ----------------------
4629
4630 -- Add closing paranthesis to Work Source Where Clause
4631 -- ws_id in (1,2,3)
4632 if (l_df_grp_own_ctr > 1)
4633 then
4634 if (l_df_own_ws_clause is not null)
4635 then
4636 l_df_own_ws_clause := l_df_own_ws_clause || ')';
4637 end if;
4638 end if;
4639
4640 if (l_df_grp_asg_ctr > 1)
4641 then
4642 if (l_df_asg_ws_clause is not null)
4643 then
4644 l_df_asg_ws_clause := l_df_asg_ws_clause || ')';
4645 end if;
4646 end if;
4647
4648 l_df_final_where := null;
4649
4650 -- set the final where_clause
4651 -- This includes both Grp Own and Grp Asg where clause
4652
4653 if (l_df_grp_own_ctr = 1)
4654 then
4655 if ((l_df_own_ws_clause1 is not null) and
4656 (l_df_own_where_clause is not null))
4657 then
4658 -- l_final_where := '( '||l_own_ws_clause1 || l_own_where_clause || ')';
4659 l_df_final_where := '( '||l_df_own_where_clause || ' and ' || l_df_own_ws_clause1 || ')';
4660 end if;
4661 elsif (l_df_grp_own_ctr > 1)
4662 then
4663 if ((l_df_own_ws_clause is not null) and
4664 (l_df_own_where_clause is not null))
4665 then
4666 -- l_final_where := '( '||l_own_ws_clause || l_own_where_clause || ')';
4667 l_df_final_where := '( '|| l_df_own_where_clause || ' and ' || l_df_own_ws_clause || ')';
4668 end if;
4669 end if;
4670
4671
4672 if (l_df_grp_asg_ctr = 1)
4673 then
4674 if ((l_df_asg_ws_clause1 is not null) and
4675 (l_df_asg_where_clause is not null))
4676 then
4677 if (l_df_final_where is null)
4678 then
4679 -- l_final_where := '( '||l_asg_ws_clause1 || l_asg_where_clause || ')';
4680 l_df_final_where := '( '||l_df_asg_where_clause || ' and '||l_df_asg_ws_clause1 || ')';
4681 elsif (l_df_final_where is not null)
4682 then
4683 -- l_final_where := l_final_where||' OR '|| '( '|| l_asg_ws_clause1 || l_asg_where_clause|| ')';
4684 l_df_final_where := l_df_final_where||' OR '|| '( '|| l_df_asg_where_clause||' and '||l_df_asg_ws_clause1|| ')';
4685 end if;
4686 end if;
4687 elsif (l_df_grp_asg_ctr > 1)
4688 then
4689 if ((l_df_asg_ws_clause is not null) and
4690 (l_df_asg_where_clause is not null))
4691 then
4692 if (l_df_final_where is null)
4693 then
4694 -- l_final_where := '( '||l_asg_ws_clause || l_asg_where_clause|| ')';
4695 l_df_final_where := '( '||l_df_asg_where_clause|| ' and '||l_df_asg_ws_clause || ')';
4696 elsif (l_df_final_where is not null)
4697 then
4698 -- l_final_where := l_final_where||' OR '|| '( '|| l_asg_ws_clause || l_asg_where_clause|| ')';
4699 l_df_final_where := l_df_final_where||' OR '|| '( '|| l_df_asg_where_clause||' and '||l_df_asg_ws_clause || ')';
4700 end if;
4701 end if;
4702 end if;
4703
4704 x_dist_from_where := l_df_final_where;
4705
4706 -- insert into p_temp values('final from where '||x_dist_from_where, 1);commit;
4707 --dbms_output.put_line('dist from: '||x_dist_from_where);
4708
4709 ---------------- **************** Built The where Clause for Distribute_to **************** ----------------------
4710
4711
4712 if (p_type = 'DELIVER')
4713 then
4714
4715
4716 -- Add closing paranthesis to Work Source Where Clause
4717 -- ws_id in (1,2,3)
4718 if (l_dt_grp_own_ctr > 1)
4719 then
4720 if (l_dt_own_ws_clause is not null)
4721 then
4722 l_dt_own_ws_clause := l_dt_own_ws_clause || ')';
4723 end if;
4724 end if;
4725
4726
4727 if (l_dt_grp_asg_ctr > 1)
4728 then
4729 if (l_dt_asg_ws_clause is not null)
4730 then
4731 l_dt_asg_ws_clause := l_dt_asg_ws_clause || ')';
4732 end if;
4733 end if;
4734
4735 l_dt_final_where := null;
4736
4737 -- set the final where_clause
4738 -- This includes both Grp Own and Grp Asg where clause
4739
4740 if (l_dt_grp_own_ctr = 1)
4741 then
4742 if ((l_dt_own_ws_clause1 is not null) and
4743 (l_dt_own_where_clause is not null))
4744 then
4745 -- l_final_where := '( '||l_own_ws_clause1 || l_own_where_clause || ')';
4746 l_dt_final_where := '( '||l_dt_own_where_clause ||' and '||l_dt_own_ws_clause1 ||')';
4747 end if;
4748 elsif (l_dt_grp_own_ctr > 1)
4749 then
4750 if ((l_dt_own_ws_clause is not null) and
4751 (l_dt_own_where_clause is not null))
4752 then
4753 -- l_final_where := '( '||l_own_ws_clause || l_own_where_clause || ')';
4754 l_dt_final_where := '( '||l_dt_own_where_clause ||' and '||l_dt_own_ws_clause || ')';
4755 end if;
4756 end if;
4757
4758 if (l_dt_grp_asg_ctr = 1)
4759 then
4760 if ((l_dt_asg_ws_clause1 is not null) and
4761 (l_dt_asg_where_clause is not null))
4762 then
4763 if (l_dt_final_where is null)
4764 then
4765 -- l_final_where := '( '||l_asg_ws_clause1 || l_asg_where_clause || ')';
4766 l_dt_final_where := '( '||l_dt_asg_where_clause ||' and '||l_dt_asg_ws_clause1 || ')';
4767 elsif (l_dt_final_where is not null)
4768 then
4769 -- l_final_where := l_final_where||' OR '|| '( '|| l_asg_ws_clause1 || l_asg_where_clause|| ')';
4770 l_dt_final_where := l_dt_final_where||' OR '|| '( '|| l_dt_asg_where_clause||' and '||l_dt_asg_ws_clause1 || ')';
4771 end if;
4772 end if;
4773 elsif (l_dt_grp_asg_ctr > 1)
4774 then
4775 if ((l_dt_asg_ws_clause is not null) and
4776 (l_dt_asg_where_clause is not null))
4777 then
4778 if (l_dt_final_where is null)
4779 then
4780 -- l_final_where := '( '||l_asg_ws_clause || l_asg_where_clause|| ')';
4781 l_dt_final_where := '( '||l_dt_asg_where_clause||' and '||l_dt_asg_ws_clause || ')';
4782 elsif (l_dt_final_where is not null)
4783 then
4784 -- l_final_where := l_final_where||' OR '|| '( '|| l_asg_ws_clause || l_asg_where_clause|| ')';
4785 l_dt_final_where := l_dt_final_where||' OR '|| '( '|| l_dt_asg_where_clause||' and '||l_dt_asg_ws_clause || ')';
4786 end if;
4787 end if;
4788 end if;
4789
4790
4791 x_dist_to_where := l_dt_final_where;
4792 --dbms_output.put_line('dist from: '||x_dist_to_where);
4793 --insert into p_temp values('final to where '||x_dist_to_where, 2);commit;
4794
4795
4796
4797 end if; /* p_type = Deliver */
4798
4799 END GET_WS_WHERE_CLAUSE;
4800
4801 PROCEDURE GET_WS_WHERE_CLAUSE
4802 (p_ws_det_list IN IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WS_DETAILS_LIST,
4803 p_resource_id IN NUMBER,
4804 x_dist_from_where OUT NOCOPY VARCHAR2,
4805 x_dist_to_where OUT NOCOPY VARCHAR2
4806 ) IS
4807 l_list IEU_UWQ_BINDVAR_LIST;
4808 BEGIN
4809 GET_WS_WHERE_CLAUSE ('DISTRIBUTE', p_ws_det_list, p_resource_id, x_dist_from_where, x_dist_to_where,l_list,l_list);
4810 END GET_WS_WHERE_CLAUSE;
4811 PROCEDURE CLEANUP_DISTRIBUTING_STATUS
4812 (
4813 P_resource_id IN NUMBER,
4814 X_MSG_DATA OUT NOCOPY VARCHAR2,
4815 X_RETURN_STATUS OUT NOCOPY VARCHAR2
4816 )
4817 IS
4818
4819 p_grp_id_list IEU_UWQ_GET_NEXT_WORK_PVT.IEU_GRP_ID_LIST;
4820 l_df_grp_id_clause varchar2(1000);
4821 l_df_grp_id_ctr number := 0;
4822 z number := 1;
4823 l_sql_stmt varchar2(4000);
4824
4825 l_distribution_status_id number;
4826 l_status_id number;
4827 l_last_update_date date;
4828
4829 l_delete_flag_no varchar2(1);
4830
4831 cursor c_grp_id(p_resource_id in number) is
4832 select group_id from jtf_rs_group_members
4833 where resource_id = p_resource_id
4834 and nvl(delete_flag, 'N') = l_delete_flag_no;
4835
4836 BEGIN
4837 l_delete_flag_no :='N';
4838 l_distribution_status_id := 2;
4839 l_status_id := 0;
4840 l_last_update_date := sysdate - 10/1440;
4841
4842 if ( p_resource_id is not null)
4843 then
4844 x_return_status := FND_API.G_RET_STS_SUCCESS;
4845
4846 for grp_id in c_grp_id(p_resource_id)
4847 loop
4848 p_grp_id_list(z).group_id := grp_id.group_id;
4849 z := z + 1;
4850 end loop;
4851
4852 if p_grp_id_list.count = 0 then
4853 l_df_grp_id_clause := 'owner_id in ('||''''||''||''''||')';
4854 elsif p_grp_id_list.count > 0 then
4855 for x in p_grp_id_list.first..p_grp_id_list.last
4856 loop
4857
4858 if ((p_grp_id_list.count = 1) and (l_df_grp_id_ctr = 0)) then
4859 l_df_grp_id_clause := 'owner_id = '||p_grp_id_list(x).group_id;
4860 elsif p_grp_id_list.count > 1 then
4861 if l_df_grp_id_ctr = 0 then
4862 l_df_grp_id_clause := 'owner_id in ('||p_grp_id_list(x).group_id;
4863 l_df_grp_id_ctr := l_df_grp_id_ctr + 1;
4864 else
4865 l_df_grp_id_clause := l_df_grp_id_clause||', '||p_grp_id_list(x).group_id;
4866 l_df_grp_id_ctr := l_df_grp_id_ctr + 1;
4867 end if;
4868 end if;
4869 if l_df_grp_id_ctr = p_grp_id_list.count then
4870 l_df_grp_id_clause := l_df_grp_id_clause||')';
4871 end if;
4872 end loop;
4873 end if;
4874 l_df_grp_id_clause := '( owner_type = '||''''||'RS_GROUP'||''''||' and '||l_df_grp_id_clause||')';
4875
4876
4877 l_sql_stmt := 'UPDATE IEU_UWQM_ITEMS
4878 SET DISTRIBUTION_STATUS_ID = 1
4879 WHERE '|| l_df_grp_id_clause ||
4880 'AND DISTRIBUTION_STATUS_ID = '||':l_distribution_status_id '||
4881 'AND STATUS_ID = '||':l_status_id'
4882 ||' and to_date(last_update_date'||','||''''||'DD-MON-YYYY HH24:MI:SS'||''''||') < '
4883 ||' to_date('||''''||l_last_update_date||''''||','||''''||'DD-MON-YYYY HH24:MI:SS'||''''||')' ;
4884
4885 BEGIN
4886 execute immediate l_sql_stmt
4887 using in l_distribution_status_id, in l_status_id;
4888 EXCEPTION WHEN OTHERS THEN
4889 X_MSG_DATA := SQLCODE||' '||SQLERRM;
4890 END;
4891 commit;
4892 end if;
4893
4894 EXCEPTION
4895 WHEN OTHERS THEN
4896 x_return_status := FND_API.G_RET_STS_ERROR;
4897 x_msg_data := SQLCODE||' '||sqlerrm;
4898
4899 END CLEANUP_DISTRIBUTING_STATUS;
4900
4901
4902 END IEU_UWQ_GET_NEXT_WORK_PVT;
4903