[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.4.12020000.2 2013/05/06 06:02:13 spamujul ship $ */
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);
208 l_work_item_id_1 number(15);
205 l_due_date varchar2(30);
206 l_workitem_obj_code varchar2(30);
207
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;
533 ----move swapped to second
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
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 := 0;
1034 v varchar2(1000);
1035 l_ws_code1 IEU_UWQM_WORK_SOURCES_B.WS_CODE%TYPE;
1036 l_sec_incident NUMBER;
1037 l_del_temp_item_flag VARCHAR2(10);
1038
1039 BEGIN
1040 v := p_bindvar_to_list.count;
1041 l_del_items_flag := 'Y';
1042 l_dist_items_flag := 'Y';
1043 l_distribute_to := 'INDIVIDUAL_ASSIGNED';
1044 l_distribute_from := 'GROUP_OWNED';
1045 l_not_valid_flag := 'N';
1046 l_audit_log_val := FND_PROFILE.VALUE('IEU_WR_DIST_AUDIT_LOG');
1047
1048 x_return_status := fnd_api.g_ret_sts_success;
1049
1050 IF NOT fnd_api.compatible_api_call (
1051 l_api_version,
1052 p_api_version,
1053 l_api_name,
1054 g_pkg_name
1055 )
1056 THEN
1057 RAISE fnd_api.g_exc_unexpected_error;
1058 END IF;
1059
1060 -- Initialize Message list
1061
1062 FND_MSG_PUB.INITIALIZE;
1063
1064 LOOP
1065
1066 exit when ((l_dist_item_ctr >= 2) or (l_num_of_items_distributed > 0));
1067
1068 l_dist_item_ctr := l_dist_item_ctr + 1;
1069
1070 -- Audit Trail
1071 l_action_key := 'DELIVERY';
1072 if (l_audit_log_val = 'DETAILED')
1073 then
1074 l_ieu_comment_code1 := 'NUM_OF_ATTEMPTS '||l_dist_item_ctr;
1075 end if;
1076
1077 --- *** Get the Distributed Work Item with sorted by pty and due_date *** ---
1078
1079 -- IEU_UWQ_GET_NEXT_WORK_PVT.GET_WS_WHERE_CLAUSE('DIST_TO',l_where_clause);
1080
1081 -- Build the complete select stmt
1082 /* Bug 10164373, 10634614 added alisas items to the below query*/
1083 l_sql_stmt := 'SELECT /*+ first_rows */
1084 WORK_ITEM_ID,
1085 WORKITEM_OBJ_CODE,
1086 WORKITEM_PK_ID,
1087 STATUS_ID,
1088 PRIORITY_ID,
1089 PRIORITY_LEVEL,
1090 null, -- Selecting null for pty code
1091 DUE_DATE,
1092 TITLE,
1093 PARTY_ID,
1094 OWNER_ID,
1095 OWNER_TYPE,
1096 ASSIGNEE_ID,
1097 ASSIGNEE_TYPE,
1098 SOURCE_OBJECT_ID,
1099 SOURCE_OBJECT_TYPE_CODE,
1100 APPLICATION_ID,
1101 IEU_ENUM_TYPE_UUID,
1102 WORK_ITEM_NUMBER,
1103 RESCHEDULE_TIME,
1104 WS_ID
1105 FROM IEU_UWQM_ITEMS items '||
1106 ' WHERE ( '|| p_dist_to_extra_where_clause || ' ) '||
1107 ' AND DISTRIBUTION_STATUS_ID = :l_del_status' ||
1108 ' AND STATUS_ID = :l_open_status_id ' ||
1109 ' and reschedule_time <= sysdate ' ||
1110 ' order by priority_level, due_date ';
1111 cursor_id := dbms_sql.open_cursor;
1112 DBMS_SQL.PARSE(cursor_id, l_sql_stmt, dbms_sql.native);
1113 DBMS_SQL.BIND_VARIABLE(cursor_id,':l_del_status', l_del_status);
1114 DBMS_SQL.BIND_VARIABLE(cursor_id,':l_open_status_id', l_open_status_id);
1115 DBMS_SQL.BIND_VARIABLE(cursor_id,':resource_id', p_resource_id);
1116
1117
1118
1119 --insert into temp values (' to proc ',p_dist_to_extra_where_clause);
1120 for i in 1..p_bindvar_to_list.count loop
1121 --insert into temp values (' to proc bind vars',p_bindvar_to_list(i).bind_name||' '||p_bindvar_to_list(i).value);
1122 DBMS_SQL.BIND_VARIABLE(cursor_id,p_bindvar_to_list(i).bind_name, p_bindvar_to_list(i).value);
1123 end loop;
1124 DBMS_SQL.DEFINE_COLUMN(cursor_id,1,l_del_nw_item.WORK_ITEM_ID);
1125 DBMS_SQL.DEFINE_COLUMN(cursor_id,2,l_del_nw_item.WORKITEM_OBJ_CODE,30);
1126 DBMS_SQL.DEFINE_COLUMN(cursor_id,3,l_del_nw_item.WORKITEM_PK_ID);
1127 DBMS_SQL.DEFINE_COLUMN(cursor_id,4,l_del_nw_item.STATUS_ID);
1128 DBMS_SQL.DEFINE_COLUMN(cursor_id,5,l_del_nw_item.PRIORITY_ID);
1129 DBMS_SQL.DEFINE_COLUMN(cursor_id,6,l_del_nw_item.PRIORITY_LEVEL);
1130 DBMS_SQL.DEFINE_COLUMN(cursor_id,7,l_del_nw_item.PRIORITY_CODE,30);
1131 DBMS_SQL.DEFINE_COLUMN(cursor_id,8,l_del_nw_item.DUE_DATE);
1132 DBMS_SQL.DEFINE_COLUMN(cursor_id,9,l_del_nw_item.TITLE,1990);
1133 DBMS_SQL.DEFINE_COLUMN(cursor_id,10,l_del_nw_item.PARTY_ID);
1134 DBMS_SQL.DEFINE_COLUMN(cursor_id,11,l_del_nw_item.OWNER_ID);
1135 DBMS_SQL.DEFINE_COLUMN(cursor_id,12,l_del_nw_item.OWNER_TYPE,25);
1136 DBMS_SQL.DEFINE_COLUMN(cursor_id,13,l_del_nw_item.ASSIGNEE_ID);
1137 DBMS_SQL.DEFINE_COLUMN(cursor_id,14,l_del_nw_item.ASSIGNEE_TYPE,25);
1138 DBMS_SQL.DEFINE_COLUMN(cursor_id,15,l_del_nw_item.SOURCE_OBJECT_ID);
1139 DBMS_SQL.DEFINE_COLUMN(cursor_id,16,l_del_nw_item.SOURCE_OBJECT_TYPE_CODE,30);
1140 DBMS_SQL.DEFINE_COLUMN(cursor_id,17,l_del_nw_item.APPLICATION_ID);
1141 DBMS_SQL.DEFINE_COLUMN(cursor_id,18,l_del_nw_item.IEU_ENUM_TYPE_UUID,38);
1142 DBMS_SQL.DEFINE_COLUMN(cursor_id,19,l_del_nw_item.WORK_ITEM_NUMBER,64);
1143 DBMS_SQL.DEFINE_COLUMN(cursor_id,20,l_del_nw_item.RESCHEDULE_TIME);
1144 DBMS_SQL.DEFINE_COLUMN(cursor_id,21,l_del_nw_item.WS_ID);
1145 dummy := DBMS_SQL.EXECUTE(cursor_id);
1146 /******** 12.2.2 HTML UWQ Changes Start ***********************/
1147 LOOP
1148 temp := DBMS_SQL.FETCH_ROWS(cursor_id);
1149 l_del_nw_item := NULL;
1150 IF temp = 0 THEN
1151 exit;
1152 ELSIF temp <> 0 THEN
1153 DBMS_SQL.COLUMN_VALUE(cursor_id,1,l_del_nw_item.WORK_ITEM_ID);
1154 DBMS_SQL.COLUMN_VALUE(cursor_id,2,l_del_nw_item.WORKITEM_OBJ_CODE);
1155 DBMS_SQL.COLUMN_VALUE(cursor_id,3,l_del_nw_item.WORKITEM_PK_ID);
1156 DBMS_SQL.COLUMN_VALUE(cursor_id,4,l_del_nw_item.STATUS_ID);
1157 DBMS_SQL.COLUMN_VALUE(cursor_id,5,l_del_nw_item.PRIORITY_ID);
1158 DBMS_SQL.COLUMN_VALUE(cursor_id,6,l_del_nw_item.PRIORITY_LEVEL);
1159 DBMS_SQL.COLUMN_VALUE(cursor_id,7,l_del_nw_item.PRIORITY_CODE);
1160 DBMS_SQL.COLUMN_VALUE(cursor_id,8,l_del_nw_item.DUE_DATE);
1161 DBMS_SQL.COLUMN_VALUE(cursor_id,9,l_del_nw_item.TITLE);
1162 DBMS_SQL.COLUMN_VALUE(cursor_id,10,l_del_nw_item.PARTY_ID);
1163 DBMS_SQL.COLUMN_VALUE(cursor_id,11,l_del_nw_item.OWNER_ID);
1164 DBMS_SQL.COLUMN_VALUE(cursor_id,12,l_del_nw_item.OWNER_TYPE);
1165 DBMS_SQL.COLUMN_VALUE(cursor_id,13,l_del_nw_item.ASSIGNEE_ID);
1166 DBMS_SQL.COLUMN_VALUE(cursor_id,14,l_del_nw_item.ASSIGNEE_TYPE);
1167 DBMS_SQL.COLUMN_VALUE(cursor_id,15,l_del_nw_item.SOURCE_OBJECT_ID);
1168 DBMS_SQL.COLUMN_VALUE(cursor_id,16,l_del_nw_item.SOURCE_OBJECT_TYPE_CODE);
1169 DBMS_SQL.COLUMN_VALUE(cursor_id,17,l_del_nw_item.APPLICATION_ID);
1170 DBMS_SQL.COLUMN_VALUE(cursor_id,18,l_del_nw_item.IEU_ENUM_TYPE_UUID);
1171 DBMS_SQL.COLUMN_VALUE(cursor_id,19,l_del_nw_item.WORK_ITEM_NUMBER);
1172 DBMS_SQL.COLUMN_VALUE(cursor_id,20,l_del_nw_item.RESCHEDULE_TIME);
1173 DBMS_SQL.COLUMN_VALUE(cursor_id,21,l_del_nw_item.WS_ID);
1174 BEGIN
1175 select ws_code
1176 into l_ws_code1
1177 from ieu_uwqm_work_sources_b
1178 where ws_id = l_del_nw_item.ws_id;
1179 EXCEPTION
1180 WHEN OTHERS THEN
1181 l_ws_code1 := '';
1182 END;
1183 IF l_ws_code1 ='SR' THEN
1184 BEGIN
1185 SELECT INCIDENT_ID
1186 INTO l_sec_incident
1187 FROM CS_INCIDENTS_B_SEC
1188 WHERE incident_id = l_del_nw_item.WORKITEM_PK_ID;
1189 IF l_sec_incident IS NOT NULL THEN
1190 l_del_items_flag := 'Y';
1191 END IF;
1192 EXIT;
1193 EXCEPTION
1194 WHEN OTHERS THEN
1195 l_del_items_flag := 'N';
1196 END;
1197 ELSIF l_ws_code1 ='SR_TASKS' THEN
1198 BEGIN
1199 SELECT INCIDENT_ID
1200 INTO l_sec_incident
1201 FROM CS_INCIDENTS_B_SEC
1202 WHERE incident_id = l_del_nw_item.SOURCE_OBJECT_ID;
1203 IF l_sec_incident IS NOT NULL THEN
1204 l_del_items_flag := 'Y';
1205 END IF;
1206 EXIT;
1207 EXCEPTION
1208 WHEN OTHERS THEN
1209 l_del_items_flag := 'N';
1210 END;
1211 ELSE
1212 l_del_items_flag := 'Y';
1213 EXIT;
1214 END IF;
1215 END IF;
1216 END LOOP;
1217 DBMS_SQL.CLOSE_CURSOR(cursor_id);
1218 /******** 12.2.2 HTML UWQ Changes End ***********************/
1219
1220
1221 -- FETCH l_del_wr_cur into l_del_nw_item;
1222
1223 -- Check if there are any Distributed Items for this resource
1224
1225 /* if (l_del_wr_cur%NOTFOUND)
1226 then
1227 -- NO Distributed Work item
1228 l_del_items_flag := 'N';
1229 end if;
1230 */
1231 -- CLOSE l_del_wr_cur;
1232
1233 --- *** Get the Distributable Work Item with sorted by pty and due_date *** ---
1234
1235 -- IEU_UWQ_GET_NEXT_WORK_PVT.GET_WS_WHERE_CLAUSE('DIST_FROM',l_where_clause);
1236
1237 -- Get the Distributed Work Item with sorted by pty and due_date
1238
1239 -- Build the complete select stmt
1240 /* Bug 10164373, 10634614 added alisas items to the below query*/
1241 l_sql_stmt := 'SELECT /*+ first_rows */
1242 WORK_ITEM_ID,
1243 WORKITEM_OBJ_CODE,
1244 WORKITEM_PK_ID,
1245 STATUS_ID,
1246 PRIORITY_ID,
1247 PRIORITY_LEVEL,
1248 null, -- Selecting null for pty code
1249 DUE_DATE,
1250 TITLE,
1251 PARTY_ID,
1252 OWNER_ID,
1253 OWNER_TYPE,
1254 ASSIGNEE_ID,
1255 ASSIGNEE_TYPE,
1256 SOURCE_OBJECT_ID,
1257 SOURCE_OBJECT_TYPE_CODE,
1258 APPLICATION_ID,
1259 IEU_ENUM_TYPE_UUID,
1260 WORK_ITEM_NUMBER,
1261 RESCHEDULE_TIME,
1262 WS_ID
1263 FROM IEU_UWQM_ITEMS items '||
1264 ' WHERE ( '|| p_dist_from_extra_where_clause || ' ) '||
1265 ' AND DISTRIBUTION_STATUS_ID = :l_dist_status' ||
1266 ' AND STATUS_ID = :l_open_status_id ' ||
1267 ' and reschedule_time <= sysdate ' ||
1268 ' order by priority_level, due_date ' ||
1269 ' for update skip locked ';
1270
1271 -- insert into p_temp(msg) values ('dist from sql- '||l_sql_stmt|| ' res id: '||p_resource_id ||' dist st: '||l_dist_status
1272 -- || ' open st: '||l_open_status_id); commit;
1273
1274 l_ctr := 0;
1275 l_dist_wr_cur_cnt := 1;
1276
1277 -- Select the top 5 Work Items for Distribution
1278
1279 -- OPEN l_dist_wr_cur FOR l_sql_stmt
1280 -- USING IN l_dist_status, IN l_open_status_id;
1281
1282 cursor_id := dbms_sql.open_cursor;
1283 DBMS_SQL.PARSE(cursor_id, l_sql_stmt, dbms_sql.native);
1284
1285 DBMS_SQL.BIND_VARIABLE(cursor_id,':l_dist_status', l_dist_status);
1286 DBMS_SQL.BIND_VARIABLE(cursor_id,':l_open_status_id', l_open_status_id);
1287
1288
1289 for i in 1..p_bindvar_from_list.count loop
1290 DBMS_SQL.BIND_VARIABLE(cursor_id,p_bindvar_from_list(i).bind_name, p_bindvar_from_list(i).value);
1291 end loop;
1292
1293 DBMS_SQL.DEFINE_COLUMN(cursor_id,1,l_dist_nw_item.WORK_ITEM_ID);
1294 DBMS_SQL.DEFINE_COLUMN(cursor_id,2,l_dist_nw_item.WORKITEM_OBJ_CODE,30);
1295 DBMS_SQL.DEFINE_COLUMN(cursor_id,3,l_dist_nw_item.WORKITEM_PK_ID);
1296 DBMS_SQL.DEFINE_COLUMN(cursor_id,4,l_dist_nw_item.STATUS_ID);
1297 DBMS_SQL.DEFINE_COLUMN(cursor_id,5,l_dist_nw_item.PRIORITY_ID);
1298 DBMS_SQL.DEFINE_COLUMN(cursor_id,6,l_dist_nw_item.PRIORITY_LEVEL);
1299 DBMS_SQL.DEFINE_COLUMN(cursor_id,7,l_dist_nw_item.PRIORITY_CODE,30);
1300 DBMS_SQL.DEFINE_COLUMN(cursor_id,8,l_dist_nw_item.DUE_DATE);
1301 DBMS_SQL.DEFINE_COLUMN(cursor_id,9,l_dist_nw_item.TITLE,1990);
1302 DBMS_SQL.DEFINE_COLUMN(cursor_id,10,l_dist_nw_item.PARTY_ID);
1303 DBMS_SQL.DEFINE_COLUMN(cursor_id,11,l_dist_nw_item.OWNER_ID);
1304 DBMS_SQL.DEFINE_COLUMN(cursor_id,12,l_dist_nw_item.OWNER_TYPE,25);
1305 DBMS_SQL.DEFINE_COLUMN(cursor_id,13,l_dist_nw_item.ASSIGNEE_ID);
1306 DBMS_SQL.DEFINE_COLUMN(cursor_id,14,l_dist_nw_item.ASSIGNEE_TYPE,25);
1307 DBMS_SQL.DEFINE_COLUMN(cursor_id,15,l_dist_nw_item.SOURCE_OBJECT_ID);
1308 DBMS_SQL.DEFINE_COLUMN(cursor_id,16,l_dist_nw_item.SOURCE_OBJECT_TYPE_CODE,30);
1309 DBMS_SQL.DEFINE_COLUMN(cursor_id,17,l_dist_nw_item.APPLICATION_ID);
1310 DBMS_SQL.DEFINE_COLUMN(cursor_id,18,l_dist_nw_item.IEU_ENUM_TYPE_UUID,38);
1311 DBMS_SQL.DEFINE_COLUMN(cursor_id,19,l_dist_nw_item.WORK_ITEM_NUMBER,64);
1312 DBMS_SQL.DEFINE_COLUMN(cursor_id,20,l_dist_nw_item.RESCHEDULE_TIME);
1313 DBMS_SQL.DEFINE_COLUMN(cursor_id,21,l_dist_nw_item.WS_ID);
1314 dummy := DBMS_SQL.EXECUTE(cursor_id);
1315
1316 LOOP
1317
1318
1319 --dbms_output.put_line(' row cnt '||DBMS_SQL.FETCH_ROWS(cursor_id));
1320
1321 temp := DBMS_SQL.FETCH_ROWS(cursor_id);
1322
1323 if temp = 0 or (l_dist_wr_cur_cnt > 5) then
1324 exit;
1325 elsif temp <> 0 then
1326 DBMS_SQL.COLUMN_VALUE(cursor_id,1,l_dist_nw_item.WORK_ITEM_ID);
1327 DBMS_SQL.COLUMN_VALUE(cursor_id,2,l_dist_nw_item.WORKITEM_OBJ_CODE);
1328 DBMS_SQL.COLUMN_VALUE(cursor_id,3,l_dist_nw_item.WORKITEM_PK_ID);
1329 DBMS_SQL.COLUMN_VALUE(cursor_id,4,l_dist_nw_item.STATUS_ID);
1330 DBMS_SQL.COLUMN_VALUE(cursor_id,5,l_dist_nw_item.PRIORITY_ID);
1331 DBMS_SQL.COLUMN_VALUE(cursor_id,6,l_dist_nw_item.PRIORITY_LEVEL);
1332 DBMS_SQL.COLUMN_VALUE(cursor_id,7,l_dist_nw_item.PRIORITY_CODE);
1333 DBMS_SQL.COLUMN_VALUE(cursor_id,8,l_dist_nw_item.DUE_DATE);
1334 DBMS_SQL.COLUMN_VALUE(cursor_id,9,l_dist_nw_item.TITLE);
1335 DBMS_SQL.COLUMN_VALUE(cursor_id,10,l_dist_nw_item.PARTY_ID);
1336 DBMS_SQL.COLUMN_VALUE(cursor_id,11,l_dist_nw_item.OWNER_ID);
1337 DBMS_SQL.COLUMN_VALUE(cursor_id,12,l_dist_nw_item.OWNER_TYPE);
1338 DBMS_SQL.COLUMN_VALUE(cursor_id,13,l_dist_nw_item.ASSIGNEE_ID);
1339 DBMS_SQL.COLUMN_VALUE(cursor_id,14,l_dist_nw_item.ASSIGNEE_TYPE);
1340 DBMS_SQL.COLUMN_VALUE(cursor_id,15,l_dist_nw_item.SOURCE_OBJECT_ID);
1341 DBMS_SQL.COLUMN_VALUE(cursor_id,16,l_dist_nw_item.SOURCE_OBJECT_TYPE_CODE);
1342 DBMS_SQL.COLUMN_VALUE(cursor_id,17,l_dist_nw_item.APPLICATION_ID);
1343 DBMS_SQL.COLUMN_VALUE(cursor_id,18,l_dist_nw_item.IEU_ENUM_TYPE_UUID);
1344 DBMS_SQL.COLUMN_VALUE(cursor_id,19,l_dist_nw_item.WORK_ITEM_NUMBER);
1345 DBMS_SQL.COLUMN_VALUE(cursor_id,20,l_dist_nw_item.RESCHEDULE_TIME);
1346 DBMS_SQL.COLUMN_VALUE(cursor_id,21,l_dist_nw_item.WS_ID);
1347
1348 end if;
1349 -- LOOP
1350
1351 -- FETCH l_dist_wr_cur into l_dist_nw_item;
1352
1353 -- insert into p_temp(msg) values ('Dist item: '||l_dist_nw_item.workitem_pk_id);
1354
1355 -- exit when ( (l_dist_wr_cur%NOTFOUND) OR (l_dist_wr_cur_cnt > 5) ) ;
1356
1357 l_dist_wr_cur_cnt := l_dist_wr_cur_cnt + 1;
1358
1359
1360 update ieu_uwqm_items
1361 set distribution_status_id = 2
1362 where work_item_id = l_dist_nw_item.WORK_ITEM_ID;
1363
1364
1365 -- Add items to the Table of rec
1366 BEGIN
1367 select priority_code
1368 into l_priority_code
1369 from ieu_uwqm_priorities_b
1370 where priority_id = l_dist_nw_item.PRIORITY_ID;
1371 EXCEPTION
1372 WHEN OTHERS THEN
1373 null;
1374 END;
1375
1376 l_nw_items_list(l_ctr).WORK_ITEM_ID := l_dist_nw_item.WORK_ITEM_ID;
1377 l_nw_items_list(l_ctr).WORKITEM_OBJ_CODE := l_dist_nw_item.WORKITEM_OBJ_CODE;
1378 l_nw_items_list(l_ctr).WORKITEM_PK_ID := l_dist_nw_item.WORKITEM_PK_ID;
1379 l_nw_items_list(l_ctr).STATUS_ID := l_dist_nw_item.STATUS_ID;
1380 l_nw_items_list(l_ctr).PRIORITY_CODE := l_priority_code;
1381 l_nw_items_list(l_ctr).DUE_DATE := l_dist_nw_item.DUE_DATE;
1382 l_nw_items_list(l_ctr).TITLE := l_dist_nw_item.TITLE;
1383 l_nw_items_list(l_ctr).PARTY_ID := l_dist_nw_item.PARTY_ID;
1384 l_nw_items_list(l_ctr).OWNER_ID := l_dist_nw_item.OWNER_ID;
1385 l_nw_items_list(l_ctr).OWNER_TYPE := l_dist_nw_item.OWNER_TYPE;
1386 l_nw_items_list(l_ctr).ASSIGNEE_ID := l_dist_nw_item.ASSIGNEE_ID;
1387 l_nw_items_list(l_ctr).ASSIGNEE_TYPE := l_dist_nw_item.ASSIGNEE_TYPE;
1388 l_nw_items_list(l_ctr).SOURCE_OBJECT_ID := l_dist_nw_item.SOURCE_OBJECT_ID;
1389 l_nw_items_list(l_ctr).SOURCE_OBJECT_TYPE_CODE := l_dist_nw_item.SOURCE_OBJECT_TYPE_CODE;
1390 l_nw_items_list(l_ctr).APPLICATION_ID := l_dist_nw_item.APPLICATION_ID;
1391 l_nw_items_list(l_ctr).IEU_ENUM_TYPE_UUID := l_dist_nw_item.IEU_ENUM_TYPE_UUID;
1392 l_nw_items_list(l_ctr).WORK_ITEM_NUMBER := l_dist_nw_item.WORK_ITEM_NUMBER;
1393 l_nw_items_list(l_ctr).RESCHEDULE_TIME := l_dist_nw_item.RESCHEDULE_TIME;
1394 l_nw_items_list(l_ctr).WS_ID := l_dist_nw_item.WS_ID;
1395
1396 l_ctr := l_ctr + 1;
1397
1398 END LOOP;
1399 -- CLOSE l_dist_wr_cur;
1400
1401
1402 DBMS_SQL.CLOSE_CURSOR(cursor_id);
1403 COMMIT;
1404
1405 -- Check if there are any Distributed Items for this resource
1406 if (l_nw_items_list.COUNT = 0)
1407 then
1408 -- no Distributable Work items
1409 l_dist_items_flag := 'N';
1410 end if;
1411
1412
1413 --insert into p_temp(msg) values ('l_dist_items_flag: '||l_dist_items_flag ||' l_del_items_flag: '||l_del_items_flag ); commit;
1414
1415
1416 --- *** Check if Work Item is Distributed OR Distributable Sorted by Pty_level and Due Date *** ---
1417
1418 -- Sort the Work Items (Distributed, Distributable) base on pty and due date
1419 -- Set the l_delivery_only_flag to 'Y' if Distribution is not required, 'N'if Distributionb may be required
1420 -- '-1' if No Distributable or Distributed Items are present
1421 if (nvl(l_dist_items_flag, 'Y') = 'Y') AND (nvl(l_del_items_flag, 'Y') = 'Y')
1422 then
1423 l_delivery_only_flag := 'N';
1424 elsif (nvl(l_dist_items_flag,'Y') = 'N') AND (nvl(l_del_items_flag,'Y') = 'Y')
1425 then
1426 l_delivery_only_flag := 'Y';
1427 elsif (nvl(l_dist_items_flag,'Y') = 'Y') AND (nvl(l_del_items_flag,'Y') = 'N')
1428 then
1429 l_delivery_only_flag := 'N';
1430 elsif (nvl(l_dist_items_flag,'Y') = 'N') AND (nvl(l_del_items_flag,'Y') = 'N')
1431 then
1432 l_delivery_only_flag := '-1';
1433 raise fnd_api.g_exc_error;
1434 end if; /* Check to see if Distributed or Distributable items are present */
1435 -- Audit Logging
1436 if (l_audit_log_val = 'DETAILED')
1437 then
1438 if ( l_delivery_only_flag = 'Y' )
1439 then
1440 l_ieu_comment_code2 := 'DELIVERY_ONLY';
1441 end if;
1442 end if;/* Audit Log Val is detailed */
1443
1444 -- insert into p_temp(msg) values(' l_delivery_only_flag : '||l_delivery_only_flag );
1445
1446
1447 --- *** Process Distribution/Delivery *** --
1448 if (l_delivery_only_flag = 'Y')
1449 then
1450
1451 -- Workitem is disrtibuted for this resource
1452 -- Copy the Work item data from l_del_nw_item to table of rec - x_uwqm_workitem_data
1453
1454 --dbms_output.put_line('Delivery Only');
1455
1456
1457
1458 IEU_UWQ_GET_NEXT_WORK_PVT.SET_DIST_AND_DEL_ITEM_DATA_REC(p_var_in_type_code => 'REC',
1459 p_dist_workitem_data => null,
1460 p_dist_del_workitem_data => l_del_nw_item,
1461 x_ctr => l_ctr,
1462 x_workitem_action_data => x_uwqm_workitem_data);
1463
1464 l_num_of_items_distributed := 1;
1465
1466 if x_uwqm_workitem_data.count > 0
1467 then
1468 for j in x_uwqm_workitem_data.first .. x_uwqm_workitem_data.last
1469 loop
1470 if (x_uwqm_workitem_data(j).param_name = 'WORKITEM_PK_ID')
1471 then
1472 l_workitem_pk_id := x_uwqm_workitem_data(j).param_value;
1473 end if;
1474 if (x_uwqm_workitem_data(j).param_name = 'WORKITEM_OBJ_CODE')
1475 then
1476 l_workitem_obj_code := x_uwqm_workitem_data(j).param_value;
1477 end if;
1478 if (x_uwqm_workitem_data(j).param_name = 'PRIORITY_ID')
1479 then
1480 l_priority_id := x_uwqm_workitem_data(j).param_value;
1481 end if;
1482 if (x_uwqm_workitem_data(j).param_name = 'DUE_DATE')
1483 then
1484 l_due_date := x_uwqm_workitem_data(j).param_value;
1485 end if;
1486 if (x_uwqm_workitem_data(j).param_name = 'OWNER_ID')
1487 then
1488 l_owner_id := x_uwqm_workitem_data(j).param_value;
1489 end if;
1490 if (x_uwqm_workitem_data(j).param_name = 'OWNER_TYPE')
1491 then
1492 l_owner_type := x_uwqm_workitem_data(j).param_value;
1493 end if;
1494 if (x_uwqm_workitem_data(j).param_name = 'ASSIGNEE_ID')
1495 then
1496 l_assignee_id := x_uwqm_workitem_data(j).param_value;
1497 end if;
1498 if (x_uwqm_workitem_data(j).param_name = 'ASSIGNEE_TYPE')
1499 then
1500 l_assignee_type := x_uwqm_workitem_data(j).param_value;
1501 end if;
1502 if (x_uwqm_workitem_data(j).param_name = 'SOURCE_OBJECT_ID')
1503 then
1504 l_source_object_id := x_uwqm_workitem_data(j).param_value;
1505 end if;
1506 if (x_uwqm_workitem_data(j).param_name = 'SOURCE_OBJECT_TYPE_CODE')
1507 then
1508 l_source_object_type_code := x_uwqm_workitem_data(j).param_value;
1509 end if;
1510 if (x_uwqm_workitem_data(j).param_name = 'STATUS_ID')
1511 then
1512 l_status_id := x_uwqm_workitem_data(j).param_value;
1513 end if;
1514
1515
1516 end loop;
1517 end if;
1518
1519 if ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') )
1520 then
1521 l_event_key := 'DELIVER';
1522 else
1523 l_event_key := null;
1524 end if;
1525 l_module := 'IEU_GET_NEXT_WORK_PVT.DISTRIBUTE_AND_DELIVER';
1526 l_application_id := 696;
1527 l_ws_code := null;
1528 l_ret_sts := 'S';
1529
1530 BEGIN
1531 select ws_code
1532 into l_ws_code
1533 from ieu_uwqm_work_sources_b
1534 where ws_id = l_del_nw_item.ws_id;
1535 EXCEPTION
1536 WHEN OTHERS THEN
1537 l_ws_code := '';
1538 END;
1539
1540 if ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') OR (l_audit_log_val = 'MINIMAL') )
1541 then
1542
1543 BEGIN
1544
1545 select reschedule_time, distribution_status_id
1546 into l_reschedule_time, l_distribution_status_id
1547 from ieu_uwqm_items
1548 where workitem_pk_id = l_workitem_pk_id
1549 and workitem_obj_code = l_workitem_obj_code;
1550
1551 EXCEPTION
1552 when others then
1553 null;
1554 END;
1555
1556 IEU_UWQM_AUDIT_LOG_PKG.INSERT_ROW
1557 (
1558 P_ACTION_KEY => l_action_key,
1559 P_EVENT_KEY => l_event_key,
1560 P_MODULE => l_module,
1561 P_WS_CODE => l_ws_code,
1562 P_APPLICATION_ID => l_application_id,
1563 P_WORKITEM_PK_ID => l_workitem_pk_id,
1564 P_WORKITEM_OBJ_CODE => l_workitem_obj_code,
1565 P_WORK_ITEM_STATUS_PREV => l_status_id,
1566 P_WORK_ITEM_STATUS_CURR => l_status_id,
1567 P_OWNER_ID_PREV => l_owner_id,
1568 P_OWNER_ID_CURR => l_owner_id,
1569 P_OWNER_TYPE_PREV => l_owner_type,
1570 P_OWNER_TYPE_CURR => l_owner_type,
1571 P_ASSIGNEE_ID_PREV => l_assignee_id,
1572 P_ASSIGNEE_ID_CURR => l_assignee_id,
1573 P_ASSIGNEE_TYPE_PREV => l_assignee_type,
1574 P_ASSIGNEE_TYPE_CURR => l_assignee_type,
1575 P_SOURCE_OBJECT_ID_PREV => l_source_object_id,
1576 P_SOURCE_OBJECT_ID_CURR => l_source_object_id,
1577 P_SOURCE_OBJECT_TYPE_CODE_PREV => l_source_object_type_code,
1578 P_SOURCE_OBJECT_TYPE_CODE_CURR => l_source_object_type_code,
1579 P_PARENT_WORKITEM_STATUS_PREV => null,
1580 P_PARENT_WORKITEM_STATUS_CURR => null,
1581 P_PARENT_DIST_STATUS_PREV => null,
1585 P_PRIORITY_PREV => l_priority_id,
1582 P_PARENT_DIST_STATUS_CURR => null,
1583 P_WORKITEM_DIST_STATUS_PREV => l_distribution_status_id,
1584 P_WORKITEM_DIST_STATUS_CURR => l_distribution_status_id,
1586 P_PRIORITY_CURR => l_priority_id,
1587 P_DUE_DATE_PREV => l_due_date,
1588 P_DUE_DATE_CURR => l_due_date,
1589 P_RESCHEDULE_TIME_PREV => l_reschedule_time,
1590 P_RESCHEDULE_TIME_CURR => l_reschedule_time,
1591 P_IEU_COMMENT_CODE1 => l_ieu_comment_code1,
1592 P_IEU_COMMENT_CODE2 => l_ieu_comment_code2,
1593 P_IEU_COMMENT_CODE3 => l_ieu_comment_code3,
1594 P_IEU_COMMENT_CODE4 => l_ieu_comment_code4,
1595 P_IEU_COMMENT_CODE5 => l_ieu_comment_code5,
1596 P_WORKITEM_COMMENT_CODE1 => l_workitem_comment_code1,
1597 P_WORKITEM_COMMENT_CODE2 => l_workitem_comment_code2,
1598 P_WORKITEM_COMMENT_CODE3 => l_workitem_comment_code3,
1599 P_WORKITEM_COMMENT_CODE4 => l_workitem_comment_code4,
1600 P_WORKITEM_COMMENT_CODE5 => l_workitem_comment_code5,
1601 P_STATUS => 'S',
1602 P_ERROR_CODE => l_msg_data,
1603 X_AUDIT_LOG_ID => l_audit_log_id,
1604 X_MSG_DATA => l_msg_data,
1605 X_RETURN_STATUS => l_ret_sts); commit;
1606
1607 end if;
1608
1609 elsif (l_delivery_only_flag = 'N')
1610 then
1611
1612 -- Loop thru all Distributable Items
1613
1614 for z in l_nw_items_list.first .. l_nw_items_list.last
1615 loop
1616
1617 -- Select the Work Items with highest pty, due date
1618 -- l_dist_flag = 'Y' if Distribution is required.
1619
1620 if (nvl(l_del_items_flag,'Y') = 'N')
1621 then
1622
1623 l_dist_flag := 'Y';
1624
1625 else
1626
1627 --insert into p_temp(msg) values('Attempting Dist for ID: '||l_nw_items_list(z).workitem_pk_id); commit;
1628 BEGIN
1629 select priority_level
1630 into l_priority_level
1631 from ieu_uwqm_priorities_b
1632 where priority_code = l_nw_items_list(z).priority_code;
1633 EXCEPTION
1634 WHEN OTHERS THEN
1635 null;
1636 END;
1637
1638 --insert into p_temp(msg) values('dist pty lvl: '||l_priority_level);
1639 --insert into p_temp(msg) values(' due date: '||l_nw_items_list(z).due_date);
1640 --insert into p_temp(msg) values('del pty lvl: '||l_del_nw_item.priority_level||' due date: '||l_del_nw_item.due_date );
1641
1642 if (l_priority_level < l_del_nw_item.priority_level)
1643 then
1644 l_dist_flag := 'Y';
1645 elsif (l_priority_level > l_del_nw_item.priority_level)
1646 then
1647 l_dist_flag := 'N';
1648 elsif (l_priority_level = l_del_nw_item.priority_level)
1649 then
1650 if (l_nw_items_list(z).due_date is null) and (l_del_nw_item.due_date is null)
1651 then
1652 l_dist_flag := 'N';
1653 elsif (l_nw_items_list(z).due_date is null) and (l_del_nw_item.due_date is not null)
1654 then
1655 l_dist_flag := 'N';
1656 elsif (l_nw_items_list(z).due_date is not null) and (l_del_nw_item.due_date is null)
1657 then
1658 l_dist_flag := 'Y';
1659 elsif (l_nw_items_list(z).due_date < l_del_nw_item.due_date)
1660 then
1661 l_dist_flag := 'Y';
1662 elsif (l_nw_items_list(z).due_date > l_del_nw_item.due_date)
1663 then
1664 l_dist_flag := 'N';
1665 elsif (l_nw_items_list(z).due_date = l_del_nw_item.due_date)
1666 then
1667 l_dist_flag := 'N';
1668 end if; /*due date */
1669 end if;/* pty_level */
1670
1671 end if; /* (nvl(l_del_items_flag,'Y') = 'N') */
1672
1673 if (l_dist_flag <> 'Y')
1674 then
1675
1676 -- Workitem is disrtibuted for this resource
1677 -- Copy the Work item data from l_del_nw_item to table of rec - x_uwqm_workitem_data
1678
1679 --dbms_output.put_line('Delivery Only');
1680
1681
1682
1683 IEU_UWQ_GET_NEXT_WORK_PVT.SET_DIST_AND_DEL_ITEM_DATA_REC(p_var_in_type_code => 'REC',
1684 p_dist_workitem_data => null,
1685 p_dist_del_workitem_data => l_del_nw_item,
1686 x_ctr => l_ctr,
1687 x_workitem_action_data => x_uwqm_workitem_data);
1688
1689 if x_uwqm_workitem_data.count > 0
1690 then
1691 for j in x_uwqm_workitem_data.first .. x_uwqm_workitem_data.last
1692 loop
1693 if (x_uwqm_workitem_data(j).param_name = 'WORKITEM_PK_ID')
1694 then
1695 l_workitem_pk_id := x_uwqm_workitem_data(j).param_value;
1696 end if;
1697 if (x_uwqm_workitem_data(j).param_name = 'WORKITEM_OBJ_CODE')
1698 then
1699 l_workitem_obj_code := x_uwqm_workitem_data(j).param_value;
1700 end if;
1701 if (x_uwqm_workitem_data(j).param_name = 'PRIORITY_ID')
1702 then
1703 l_priority_id := x_uwqm_workitem_data(j).param_value;
1704 end if;
1705 if (x_uwqm_workitem_data(j).param_name = 'DUE_DATE')
1706 then
1707 l_due_date := x_uwqm_workitem_data(j).param_value;
1708 end if;
1709 if (x_uwqm_workitem_data(j).param_name = 'OWNER_ID')
1710 then
1711 l_owner_id := x_uwqm_workitem_data(j).param_value;
1712 end if;
1713 if (x_uwqm_workitem_data(j).param_name = 'OWNER_TYPE')
1714 then
1715 l_owner_type := x_uwqm_workitem_data(j).param_value;
1716 end if;
1717 if (x_uwqm_workitem_data(j).param_name = 'ASSIGNEE_ID')
1718 then
1719 l_assignee_id := x_uwqm_workitem_data(j).param_value;
1720 end if;
1721 if (x_uwqm_workitem_data(j).param_name = 'ASSIGNEE_TYPE')
1722 then
1723 l_assignee_type := x_uwqm_workitem_data(j).param_value;
1724 end if;
1725 if (x_uwqm_workitem_data(j).param_name = 'SOURCE_OBJECT_ID')
1726 then
1727 l_source_object_id := x_uwqm_workitem_data(j).param_value;
1728 end if;
1729 if (x_uwqm_workitem_data(j).param_name = 'SOURCE_OBJECT_TYPE_CODE')
1730 then
1731 l_source_object_type_code := x_uwqm_workitem_data(j).param_value;
1732 end if;
1733 if (x_uwqm_workitem_data(j).param_name = 'STATUS_ID')
1734 then
1735 l_status_id := x_uwqm_workitem_data(j).param_value;
1736 end if;
1737 end loop;
1738 end if;
1739
1740 l_num_of_items_distributed := 1;
1741 if ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') )
1742 then
1743 l_event_key := 'DELIVER';
1744 else
1745 l_event_key := null;
1746 end if;
1747 l_module := 'IEU_GET_NEXT_WORK_PVT.DISTRIBUTE_AND_DELIVER';
1748 l_application_id := 696;
1749 l_ws_code := null;
1750 l_ret_sts := 'S';
1751
1752 BEGIN
1753 select ws_code
1754 into l_ws_code
1755 from ieu_uwqm_work_sources_b
1756 where ws_id = l_del_nw_item.ws_id;
1757 EXCEPTION
1758 WHEN OTHERS THEN
1759 l_ws_code := '';
1760 END;
1761
1762 --insert into p_temp(msg) values('audit log val: '||l_audit_log_val||' ret sts: '||l_ret_sts ||' ws code: '||l_ws_code);
1763 if ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') OR (l_audit_log_val = 'MINIMAL') )
1764 then
1765
1766 BEGIN
1767
1768 select reschedule_time, distribution_status_id
1769 into l_reschedule_time, l_distribution_status_id
1770 from ieu_uwqm_items
1771 where workitem_pk_id = l_workitem_pk_id
1772 and workitem_obj_code = l_workitem_obj_code;
1773
1774 EXCEPTION
1775 when others then
1776 null;
1777 END;
1778
1779 IEU_UWQM_AUDIT_LOG_PKG.INSERT_ROW
1780 (
1781 P_ACTION_KEY => l_action_key,
1782 P_EVENT_KEY => l_event_key,
1783 P_MODULE => l_module,
1784 P_WS_CODE => l_ws_code,
1785 P_APPLICATION_ID => l_application_id,
1786 P_WORKITEM_PK_ID => l_workitem_pk_id,
1787 P_WORKITEM_OBJ_CODE => l_workitem_obj_code,
1788 P_WORK_ITEM_STATUS_PREV => l_status_id,
1789 P_WORK_ITEM_STATUS_CURR => l_status_id,
1790 P_OWNER_ID_PREV => l_owner_id,
1791 P_OWNER_ID_CURR => l_owner_id,
1792 P_OWNER_TYPE_PREV => l_owner_type,
1793 P_OWNER_TYPE_CURR => l_owner_type,
1794 P_ASSIGNEE_ID_PREV => l_assignee_id,
1795 P_ASSIGNEE_ID_CURR => l_assignee_id,
1796 P_ASSIGNEE_TYPE_PREV => l_assignee_type,
1797 P_ASSIGNEE_TYPE_CURR => l_assignee_type,
1798 P_SOURCE_OBJECT_ID_PREV => l_source_object_id,
1799 P_SOURCE_OBJECT_ID_CURR => l_source_object_id,
1800 P_SOURCE_OBJECT_TYPE_CODE_PREV => l_source_object_type_code,
1801 P_SOURCE_OBJECT_TYPE_CODE_CURR => l_source_object_type_code,
1802 P_PARENT_WORKITEM_STATUS_PREV => null,
1803 P_PARENT_WORKITEM_STATUS_CURR => null,
1804 P_PARENT_DIST_STATUS_PREV => null,
1805 P_PARENT_DIST_STATUS_CURR => null,
1806 P_WORKITEM_DIST_STATUS_PREV => l_distribution_status_id,
1807 P_WORKITEM_DIST_STATUS_CURR => l_distribution_status_id,
1808 P_PRIORITY_PREV => l_priority_id,
1809 P_PRIORITY_CURR => l_priority_id,
1810 P_DUE_DATE_PREV => l_due_date,
1811 P_DUE_DATE_CURR => l_due_date,
1812 P_RESCHEDULE_TIME_PREV => l_reschedule_time,
1813 P_RESCHEDULE_TIME_CURR => l_reschedule_time,
1814 P_IEU_COMMENT_CODE1 => l_ieu_comment_code1,
1815 P_IEU_COMMENT_CODE2 => l_ieu_comment_code2,
1816 P_IEU_COMMENT_CODE3 => l_ieu_comment_code3,
1817 P_IEU_COMMENT_CODE4 => l_ieu_comment_code4,
1818 P_IEU_COMMENT_CODE5 => l_ieu_comment_code5,
1819 P_WORKITEM_COMMENT_CODE1 => l_workitem_comment_code1,
1820 P_WORKITEM_COMMENT_CODE2 => l_workitem_comment_code2,
1821 P_WORKITEM_COMMENT_CODE3 => l_workitem_comment_code3,
1822 P_WORKITEM_COMMENT_CODE4 => l_workitem_comment_code4,
1823 P_WORKITEM_COMMENT_CODE5 => l_workitem_comment_code5,
1824 P_STATUS => 'S',
1825 P_ERROR_CODE => l_msg_data,
1826 X_AUDIT_LOG_ID => l_audit_log_id,
1827 X_MSG_DATA => l_msg_data,
1828 X_RETURN_STATUS => l_ret_sts); commit;
1829
1830
1831 end if;
1832 exit;
1833
1834 elsif (l_dist_flag = 'Y')
1835 then
1836
1837
1838 /************ THIS IS NOT REQUIRED NOW AS WE HAVE TWO DIFFERENT EVENTS FOR DIST AND DELIVER *********
1839 -- Audit Logging
1840 if (l_audit_log_val = 'DETAILED')
1841 then
1842 l_ieu_comment_code2 := 'DISTRIBUTE_AND_DELIVER';
1843 end if;
1844 ******************************************************************************************************/
1845
1846 l_ws_code := '';
1847 --dbms_output.put_line('Distributing for ws..'||l_nw_items_list(z).WS_ID);
1848 l_num_of_items_distributed := 0;
1849
1850 -- Initialize Collection
1851 l_dist_bus_rules := SYSTEM.DIST_BUS_RULES_NST();
1852 l_dist_items := SYSTEM.WR_ITEM_DATA_NST();
1853 --l_dist_workitem_data := SYSTEM.WR_ITEM_DATA_NST();
1854
1855 BEGIN
1856
1857 SELECT WS_B.DISTRIBUTION_FUNCTION ,
1858 WS_A.DIST_ST_BASED_ON_PARENT_FLAG, WS_B.WS_CODE
1859 INTO l_distribution_function,
1860 l_dist_st_based_on_parent_flag, l_ws_code
1861 FROM IEU_UWQM_WORK_SOURCES_B WS_B, IEU_UWQM_WS_ASSCT_PROPS WS_A
1862 WHERE ws_b.ws_id = l_nw_items_list(z).WS_ID
1863 AND ws_b.not_valid_flag = l_not_valid_flag
1864 AND ws_b.ws_id = ws_a.ws_id(+);
1865
1866 EXCEPTION
1867 WHEN OTHERS THEN
1868 null;
1869 END;
1870
1871 if (l_audit_log_val = 'DETAILED')
1872 then
1873 l_ieu_comment_code4 := 'DISTRIBUTION_FUNC '||l_distribution_function;
1874 end if;
1875
1876 l_dist_bus_rules.extend;
1877 l_dist_bus_rules(l_dist_bus_rules.last) := SYSTEM.DIST_BUS_RULES_OBJ ( l_ws_code,
1878 l_distribute_from,
1879 l_distribute_to,
1880 l_DIST_ST_BASED_ON_PARENT_FLAG);
1881 if (l_distribute_from = 'GROUP_OWNED') and
1882 (l_distribute_to = 'INDIVIDUAL_OWNED')
1883 then
1884 l_ieu_comment_code3 := 'GO_IO';
1885 elsif (l_distribute_from = 'GROUP_OWNED') and
1886 (l_distribute_to = 'INDIVIDUAL_ASSIGNED')
1887 then
1888 l_ieu_comment_code3 := 'GO_IA';
1889 elsif (l_distribute_from = 'GROUP_ASSIGNED') and
1890 (l_distribute_to = 'INDIVIDUAL_OWNED')
1891 then
1892 l_ieu_comment_code3 := 'GA_IO';
1893 elsif (l_distribute_from = 'GROUP_ASSIGNED') and
1894 (l_distribute_to = 'INDIVIDUAL_ASSIGNED')
1895 then
1896 l_ieu_comment_code3 := 'GA_IA';
1897 end if;
1898
1899 if (l_nw_items_list(z).STATUS_ID = 0)
1900 then
1901 l_work_item_status := 'OPEN';
1902 elsif (l_nw_items_list(z).STATUS_ID = 3)
1903 then
1904 l_work_item_status := 'CLOSE';
1905 elsif (l_nw_items_list(z).STATUS_ID = 4)
1906 then
1907 l_work_item_status := 'DELETE';
1908 elsif (l_nw_items_list(z).STATUS_ID = 5)
1909 then
1910 l_work_item_status := 'SLEEP';
1911 end if;
1912
1913 --dbms_output.put_line('ws id matches: '||l_nw_items_list(i).ws_id|| ' ID: '||l_nw_items_list(i).WORKITEM_PK_ID);
1914 l_dist_items.extend;
1915 l_dist_items(l_dist_items.last) := SYSTEM.WR_ITEM_DATA_OBJ(l_nw_items_list(z).WORK_ITEM_ID,
1916 l_nw_items_list(z).WORKITEM_OBJ_CODE,
1917 l_nw_items_list(z).WORKITEM_PK_ID,
1918 l_work_item_status,
1919 l_nw_items_list(z).PRIORITY_ID,
1920 l_nw_items_list(z).PRIORITY_LEVEL,
1921 l_nw_items_list(z).PRIORITY_CODE,
1922 l_nw_items_list(z).DUE_DATE,
1923 l_nw_items_list(z).TITLE,
1924 l_nw_items_list(z).PARTY_ID,
1925 l_nw_items_list(z).OWNER_ID,
1926 l_nw_items_list(z).OWNER_TYPE,
1927 l_nw_items_list(z).ASSIGNEE_ID,
1928 l_nw_items_list(z).ASSIGNEE_TYPE,
1929 l_nw_items_list(z).SOURCE_OBJECT_ID,
1930 l_nw_items_list(z).SOURCE_OBJECT_TYPE_CODE,
1931 l_nw_items_list(z).APPLICATION_ID,
1932 l_nw_items_list(z).IEU_ENUM_TYPE_UUID,
1933 l_nw_items_list(z).WORK_ITEM_NUMBER,
1934 l_nw_items_list(z).RESCHEDULE_TIME,
1935 l_ws_code, --l_nw_items_list(i).WS_ID,
1936 null,
1937 null);
1938
1939
1940 --dbms_output.put_line('dist items cnt'||l_dist_items.count);
1941
1942 -- Call the Distribution Function
1943 if (l_dist_items.count > 0)
1944 then
1945 --dbms_output.put_line('calling dist func: '||L_DISTRIBUTION_FUNCTION);
1946
1947 if ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') OR (l_audit_log_val = 'MINIMAL') )
1948 then
1949
1950 for k in l_dist_items.first .. l_dist_items.last
1951 loop
1952
1953 IEU_UWQM_AUDIT_LOG_PKG.INSERT_ROW
1954 (
1955 P_ACTION_KEY => l_action_key,
1956 P_EVENT_KEY => l_event_key,
1957 P_MODULE => l_module,
1958 P_WS_CODE => l_ws_code,
1959 P_APPLICATION_ID => l_dist_items(k).application_id,
1960 P_WORKITEM_PK_ID => l_dist_items(k).workitem_pk_id,
1961 P_WORKITEM_OBJ_CODE => l_dist_items(k).workitem_obj_code,
1962 P_WORK_ITEM_STATUS_PREV => l_status_id,
1963 P_WORK_ITEM_STATUS_CURR => l_status_id,
1964 P_OWNER_ID_PREV => l_dist_items(k).owner_id,
1965 P_OWNER_ID_CURR => l_dist_items(k).owner_id,
1966 P_OWNER_TYPE_PREV => l_dist_items(k).owner_type,
1967 P_OWNER_TYPE_CURR => l_dist_items(k).owner_type,
1968 P_ASSIGNEE_ID_PREV => null,
1969 P_ASSIGNEE_ID_CURR => l_dist_items(k).assignee_id,
1970 P_ASSIGNEE_TYPE_PREV => null,
1971 P_ASSIGNEE_TYPE_CURR => l_dist_items(k).assignee_type,
1972 P_SOURCE_OBJECT_ID_PREV => l_dist_items(k).source_object_id,
1973 P_SOURCE_OBJECT_ID_CURR => l_dist_items(k).source_object_id,
1974 P_SOURCE_OBJECT_TYPE_CODE_PREV => l_dist_items(k).source_object_type_code,
1975 P_SOURCE_OBJECT_TYPE_CODE_CURR => l_dist_items(k).source_object_type_code,
1976 P_PARENT_WORKITEM_STATUS_PREV => null,
1977 P_PARENT_WORKITEM_STATUS_CURR => null,
1978 P_PARENT_DIST_STATUS_PREV => null,
1979 P_PARENT_DIST_STATUS_CURR => null,
1980 P_WORKITEM_DIST_STATUS_PREV => l_distribution_status_id,
1981 P_WORKITEM_DIST_STATUS_CURR => l_distribution_status_id,
1982 P_PRIORITY_PREV => l_dist_items(k).priority_id,
1983 P_PRIORITY_CURR => l_dist_items(k).priority_id,
1984 P_DUE_DATE_PREV => l_dist_items(k).due_date,
1985 P_DUE_DATE_CURR => l_dist_items(k).due_date,
1986 P_RESCHEDULE_TIME_PREV => l_reschedule_time,
1987 P_RESCHEDULE_TIME_CURR => l_reschedule_time,
1988 P_IEU_COMMENT_CODE1 => l_ieu_comment_code1,
1989 P_IEU_COMMENT_CODE2 => l_ieu_comment_code2,
1990 P_IEU_COMMENT_CODE3 => l_ieu_comment_code3,
1991 P_IEU_COMMENT_CODE4 => l_ieu_comment_code4,
1992 P_IEU_COMMENT_CODE5 => l_ieu_comment_code5,
1993 P_WORKITEM_COMMENT_CODE1 => l_workitem_comment_code1,
1994 P_WORKITEM_COMMENT_CODE2 => l_workitem_comment_code2,
1995 P_WORKITEM_COMMENT_CODE3 => l_workitem_comment_code3,
1996 P_WORKITEM_COMMENT_CODE4 => l_workitem_comment_code4,
1997 P_WORKITEM_COMMENT_CODE5 => l_workitem_comment_code5,
1998 P_STATUS => 'E',
1999 P_ERROR_CODE => x_msg_data,
2000 X_AUDIT_LOG_ID => l_audit_log_id_list(k),
2001 X_MSG_DATA => x_msg_data,
2002 X_RETURN_STATUS => l_ret_sts
2003 );
2004
2005 end loop;
2006 end if;
2007
2008 BEGIN
2009 EXECUTE IMMEDIATE
2010 'BEGIN '|| L_DISTRIBUTION_FUNCTION||'(:1,:2,:3,:4,:5,:6,:7,:8,:9); END;'
2011 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,
2012 OUT L_MSG_COUNT, OUT L_MSG_DATA, OUT L_RETURN_STATUS;
2013 EXCEPTION
2014 when others then
2015 -- Set the status back from 'Distributing' to 'Distributable'
2016 for k in l_dist_items.first .. l_dist_items.last
2017 loop
2018 l_workitem_pk_id := l_dist_items(k).workitem_pk_id;
2019 l_workitem_obj_code := l_dist_items(k).workitem_obj_code;
2020 l_owner_id := l_dist_items(k).owner_id;
2021 l_owner_type := l_dist_items(k).owner_type;
2022 l_assignee_id := l_dist_items(k).assignee_id;
2023 l_assignee_type := l_dist_items(k).assignee_type;
2024 l_priority_id := l_dist_items(k).priority_id;
2025 l_due_date := l_dist_items(k).due_date;
2026 l_source_object_id := l_dist_items(k).source_object_id;
2027 l_source_object_type_code := l_dist_items(k).source_object_type_code;
2028
2029 if (l_dist_items(k).work_item_status = 'OPEN')
2030 then
2031 l_status_id := 0;
2032 elsif (l_dist_items(k).work_item_status = 'CLOSE')
2033 then
2034 l_status_id := 3;
2035 elsif (l_dist_items(k).work_item_status = 'DELETE')
2036 then
2037 l_status_id := 4;
2038 elsif (l_dist_items(k).work_item_status = 'SLEEP')
2039 then
2040 l_status_id := 5;
2041 end if;
2042
2043 update ieu_uwqm_items
2044 set distribution_status_id = 1
2045 where work_item_id = l_dist_items(k).work_item_id;
2046 commit;
2047
2048 if ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') )
2049 then
2050 l_event_key := 'DISTRIBUTE';
2051 else
2052 l_event_key := null;
2053 end if;
2054 l_module := 'IEU_GET_NEXT_WORK_PVT.DISTRIBUTE_AND_DELIVER';
2055 l_application_id := 696;
2056 l_ret_sts := 'E';
2057
2058 FND_MSG_PUB.INITIALIZE;
2059 FND_MESSAGE.SET_NAME('IEU', 'IEU_SQL_ERROR');
2060 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME','IEU_GET_NEXT_WORK_PVT.DISTRIBUTE_AND_DELIVER');
2061 FND_MESSAGE.SET_TOKEN('SQL_ERROR_MSG',l_token_str);
2062 fnd_msg_pub.ADD;
2063
2064 fnd_msg_pub.Count_and_Get
2065 (
2066 p_count => x_msg_count,
2067 p_data => x_msg_data
2068 );
2069
2070
2071 if ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') OR (l_audit_log_val = 'MINIMAL') )
2072 then
2073 BEGIN
2074
2075 select reschedule_time, distribution_status_id, priority_id
2076 into l_reschedule_time, l_distribution_status_id, l_priority_id
2077 from ieu_uwqm_items
2078 where workitem_pk_id = l_workitem_pk_id
2079 and workitem_obj_code = l_workitem_obj_code;
2080
2081 EXCEPTION
2082 when others then
2083 null;
2084 END;
2085
2086
2087 IEU_UWQM_AUDIT_LOG_PKG.UPDATE_ROW
2088 (
2089 P_AUDIT_LOG_ID => l_audit_log_id_list(k),
2090 P_ACTION_KEY => l_action_key,
2091 P_EVENT_KEY => l_event_key,
2092 P_MODULE => l_module,
2093 P_WS_CODE => l_ws_code,
2094 P_APPLICATION_ID => l_application_id,
2095 P_WORKITEM_PK_ID => l_workitem_pk_id,
2096 P_WORKITEM_OBJ_CODE => l_workitem_obj_code,
2097 P_WORK_ITEM_STATUS_PREV => l_status_id,
2098 P_WORK_ITEM_STATUS_CURR => l_status_id,
2099 P_OWNER_ID_PREV => l_owner_id,
2100 P_OWNER_ID_CURR => l_owner_id,
2101 P_OWNER_TYPE_PREV => l_owner_type,
2102 P_OWNER_TYPE_CURR => l_owner_type,
2103 P_ASSIGNEE_ID_PREV => l_assignee_id,
2104 P_ASSIGNEE_ID_CURR => l_assignee_id,
2105 P_ASSIGNEE_TYPE_PREV => l_assignee_type,
2106 P_ASSIGNEE_TYPE_CURR => l_assignee_type,
2107 P_SOURCE_OBJECT_ID_PREV => l_source_object_id,
2108 P_SOURCE_OBJECT_ID_CURR => l_source_object_id,
2109 P_SOURCE_OBJECT_TYPE_CODE_PREV => l_source_object_type_code,
2110 P_SOURCE_OBJECT_TYPE_CODE_CURR => l_source_object_type_code,
2111 P_PARENT_WORKITEM_STATUS_PREV => null,
2112 P_PARENT_WORKITEM_STATUS_CURR => null,
2113 P_PARENT_DIST_STATUS_PREV => null,
2114 P_PARENT_DIST_STATUS_CURR => null,
2115 P_WORKITEM_DIST_STATUS_PREV => l_distribution_status_id,
2116 P_WORKITEM_DIST_STATUS_CURR => l_distribution_status_id,
2117 P_PRIORITY_PREV => l_priority_id,
2118 P_PRIORITY_CURR => l_priority_id,
2119 P_DUE_DATE_PREV => l_due_date,
2120 P_DUE_DATE_CURR => l_due_date,
2121 P_RESCHEDULE_TIME_PREV => l_reschedule_time,
2122 P_RESCHEDULE_TIME_CURR => l_reschedule_time,
2123 P_IEU_COMMENT_CODE1 => l_ieu_comment_code1,
2124 P_IEU_COMMENT_CODE2 => l_ieu_comment_code2,
2125 P_IEU_COMMENT_CODE3 => l_ieu_comment_code3,
2126 P_IEU_COMMENT_CODE4 => l_ieu_comment_code4,
2127 P_IEU_COMMENT_CODE5 => l_ieu_comment_code5,
2128 P_WORKITEM_COMMENT_CODE1 => l_workitem_comment_code1,
2129 P_WORKITEM_COMMENT_CODE2 => l_workitem_comment_code2,
2130 P_WORKITEM_COMMENT_CODE3 => l_workitem_comment_code3,
2131 P_WORKITEM_COMMENT_CODE4 => l_workitem_comment_code4,
2132 P_WORKITEM_COMMENT_CODE5 => l_workitem_comment_code5,
2133 P_STATUS => 'E',
2134 P_ERROR_CODE => x_msg_data); commit;
2135
2136 end if;
2137
2138 end loop;
2139 END;
2140
2141 -- Check the # of items distributed
2142 -- l_dist_items will contain only 1 item. This loop was required just to retrieve the values
2143 -- instead or hardcoding 0/1
2144
2145 for j in l_dist_items.FIRST..l_dist_items.LAST
2146 loop
2147
2148 l_workitem_pk_id := l_dist_items(j).workitem_pk_id;
2149 l_workitem_obj_code := l_dist_items(j).workitem_obj_code;
2150 l_owner_id := l_dist_items(j).owner_id;
2151 l_owner_type := l_dist_items(j).owner_type;
2152 l_assignee_id := l_dist_items(j).assignee_id;
2153 l_assignee_type := l_dist_items(j).assignee_type;
2154 l_priority_id := l_dist_items(j).priority_id;
2155 l_due_date := l_dist_items(j).due_date;
2156 l_source_object_id := l_dist_items(j).source_object_id;
2157 l_source_object_type_code := l_dist_items(j).source_object_type_code;
2158 if (l_dist_items(j).work_item_status = 'OPEN')
2159 then
2160 l_status_id := 0;
2161 elsif (l_dist_items(j).work_item_status = 'CLOSE')
2162 then
2163 l_status_id := 3;
2164 elsif (l_dist_items(j).work_item_status = 'DELETE')
2165 then
2166 l_status_id := 4;
2167 elsif (l_dist_items(j).work_item_status = 'SLEEP')
2168 then
2169 l_status_id := 5;
2170 end if;
2171
2172
2173 if ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') )
2174 then
2175 l_event_key := 'DISTRIBUTE';
2176 else
2177 l_event_key := null;
2178 end if;
2179 l_module := 'IEU_GET_NEXT_WORK_PVT.DISTRIBUTE_AND_DELIVER';
2180 l_application_id := 696;
2181
2182 if (l_dist_items(j).DISTRIBUTED = 'TRUE')
2183 then
2184 l_audit_log_sts := 'S';
2185 else
2186 l_audit_log_sts := 'E';
2187 end if;
2188
2189 if ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') OR (l_audit_log_val = 'MINIMAL') )
2190 then
2191
2192 BEGIN
2193
2194 select reschedule_time, distribution_status_id, priority_id
2195 into l_reschedule_time, l_distribution_status_id, l_priority_id
2196 from ieu_uwqm_items
2197 where workitem_pk_id = l_workitem_pk_id
2198 and workitem_obj_code = l_workitem_obj_code;
2199
2200 EXCEPTION
2201 when others then
2202 null;
2203 END;
2204
2205
2206 IEU_UWQM_AUDIT_LOG_PKG.UPDATE_ROW
2207 (
2208 P_AUDIT_LOG_ID => l_audit_log_id_list(j),
2209 P_ACTION_KEY => l_action_key,
2210 P_EVENT_KEY => l_event_key,
2211 P_MODULE => l_module,
2212 P_WS_CODE => l_ws_code,
2213 P_APPLICATION_ID => l_application_id,
2214 P_WORKITEM_PK_ID => l_workitem_pk_id,
2215 P_WORKITEM_OBJ_CODE => l_workitem_obj_code,
2216 P_WORK_ITEM_STATUS_PREV => l_status_id,
2217 P_WORK_ITEM_STATUS_CURR => l_status_id,
2218 P_OWNER_ID_PREV => l_owner_id,
2219 P_OWNER_ID_CURR => l_owner_id,
2220 P_OWNER_TYPE_PREV => l_owner_type,
2221 P_OWNER_TYPE_CURR => l_owner_type,
2222 P_ASSIGNEE_ID_PREV => l_assignee_id,
2223 P_ASSIGNEE_ID_CURR => l_assignee_id,
2224 P_ASSIGNEE_TYPE_PREV => l_assignee_type,
2225 P_ASSIGNEE_TYPE_CURR => l_assignee_type,
2226 P_SOURCE_OBJECT_ID_PREV => l_source_object_id,
2227 P_SOURCE_OBJECT_ID_CURR => l_source_object_id,
2228 P_SOURCE_OBJECT_TYPE_CODE_PREV => l_source_object_type_code,
2229 P_SOURCE_OBJECT_TYPE_CODE_CURR => l_source_object_type_code,
2230 P_PARENT_WORKITEM_STATUS_PREV => null,
2231 P_PARENT_WORKITEM_STATUS_CURR => null,
2232 P_PARENT_DIST_STATUS_PREV => null,
2233 P_PARENT_DIST_STATUS_CURR => null,
2234 P_WORKITEM_DIST_STATUS_PREV => 1,
2235 P_WORKITEM_DIST_STATUS_CURR => l_distribution_status_id,
2236 P_PRIORITY_PREV => l_priority_id,
2237 P_PRIORITY_CURR => l_priority_id,
2238 P_DUE_DATE_PREV => l_due_date,
2239 P_DUE_DATE_CURR => l_due_date,
2240 P_RESCHEDULE_TIME_PREV => l_reschedule_time,
2241 P_RESCHEDULE_TIME_CURR => l_reschedule_time,
2242 P_IEU_COMMENT_CODE1 => l_ieu_comment_code1,
2243 P_IEU_COMMENT_CODE2 => l_ieu_comment_code2,
2244 P_IEU_COMMENT_CODE3 => l_ieu_comment_code3,
2245 P_IEU_COMMENT_CODE4 => l_ieu_comment_code4,
2246 P_IEU_COMMENT_CODE5 => l_ieu_comment_code5,
2247 P_WORKITEM_COMMENT_CODE1 => l_workitem_comment_code1,
2248 P_WORKITEM_COMMENT_CODE2 => l_workitem_comment_code2,
2249 P_WORKITEM_COMMENT_CODE3 => l_workitem_comment_code3,
2250 P_WORKITEM_COMMENT_CODE4 => l_workitem_comment_code4,
2251 P_WORKITEM_COMMENT_CODE5 => l_workitem_comment_code5,
2252 P_STATUS => l_audit_log_sts,
2253 P_ERROR_CODE => l_msg_data);commit;
2254
2255 end if;
2256
2257 if (l_dist_items(j).DISTRIBUTED = 'TRUE')
2258 then
2259 IF (l_dist_items(j).WORK_ITEM_STATUS is not null)
2260 THEN
2261 IF (l_dist_items(j).WORK_ITEM_STATUS = 'OPEN')
2262 THEN
2263 l_work_item_status_id := 0;
2264 ELSIF (l_dist_items(j).WORK_ITEM_STATUS = 'CLOSE')
2265 THEN
2266 l_work_item_status_id := 3;
2267 ELSIF (l_dist_items(j).WORK_ITEM_STATUS = 'DELETE')
2268 THEN
2269 l_work_item_status_id := 4;
2270 ELSIF (l_dist_items(j).WORK_ITEM_STATUS = 'SLEEP')
2271 THEN
2272 l_work_item_status_id := 5;
2273 END IF;
2274 END IF;
2275
2276 --dbms_output.put_line('dist status set to TRUE work item pkid: '||l_dist_items(j).WORKITEM_PK_ID);
2277
2278 l_num_of_items_distributed := l_num_of_items_distributed + 1;
2279 -- Update the same object
2280 l_dist_items(l_dist_items.LAST) := SYSTEM.WR_ITEM_DATA_OBJ(l_dist_items(j).WORK_ITEM_ID,
2281 l_dist_items(j).WORKITEM_OBJ_CODE,
2282 l_dist_items(j).WORKITEM_PK_ID,
2283 l_work_item_status_id,
2284 l_dist_items(j).PRIORITY_ID,
2285 l_dist_items(j).PRIORITY_LEVEL,
2286 l_dist_items(j).PRIORITY_CODE,
2287 l_dist_items(j).DUE_DATE,
2288 l_dist_items(j).TITLE,
2289 l_dist_items(j).PARTY_ID,
2290 l_dist_items(j).OWNER_ID,
2291 l_dist_items(j).OWNER_TYPE,
2292 l_dist_items(j).ASSIGNEE_ID,
2293 l_dist_items(j).ASSIGNEE_TYPE,
2294 l_dist_items(j).SOURCE_OBJECT_ID,
2295 l_dist_items(j).SOURCE_OBJECT_TYPE_CODE,
2296 l_dist_items(j).APPLICATION_ID,
2297 l_dist_items(j).IEU_ENUM_TYPE_UUID,
2298 l_dist_items(j).WORK_ITEM_NUMBER,
2299 l_dist_items(j).RESCHEDULE_TIME,
2300 l_dist_items(j).WORK_SOURCE,
2301 l_dist_items(j).DISTRIBUTED,
2302 l_dist_items(j).ITEM_INCLUDED_BY_APP);
2303
2304 -- If a work item was distributed, copy the Work Item data from table of obj - l_dist_workitem_data
2305 -- to table of Rec - x_uwqm_workitem_data
2306
2307 IEU_UWQ_GET_NEXT_WORK_PVT.SET_DIST_AND_DEL_ITEM_DATA_REC(p_var_in_type_code => 'OBJ',
2308 -- p_dist_workitem_data => l_dist_workitem_data,
2309 p_dist_workitem_data => l_dist_items,
2310 p_dist_del_workitem_data => null,
2311 x_ctr => l_ctr,
2312 x_workitem_action_data => x_uwqm_workitem_data);
2313
2314 /********************************* Added New Event Deliver *****************************/
2315
2316
2317 if ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') )
2318 then
2319 l_event_key := 'DELIVER';
2320 else
2321 l_event_key := null;
2322 end if;
2323 l_module := 'IEU_GET_NEXT_WORK_PVT.DISTRIBUTE_AND_DELIVER';
2324 l_application_id := 696;
2325
2326 l_audit_log_sts := 'S';
2327 l_ieu_comment_code1 := null;
2328 l_ieu_comment_code2 := null;
2329 l_ieu_comment_code3 := null;
2330 l_ieu_comment_code4 := null;
2331 l_ieu_comment_code5 := null;
2332
2333 if ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') OR (l_audit_log_val = 'MINIMAL') )
2334 then
2335
2336 BEGIN
2337
2338 select reschedule_time, distribution_status_id, priority_id
2339 into l_reschedule_time, l_distribution_status_id, l_priority_id
2340 from ieu_uwqm_items
2341 where workitem_pk_id = l_dist_items(j).workitem_pk_id
2342 and workitem_obj_code = l_dist_items(j).workitem_obj_code;
2343
2344 EXCEPTION
2345 when others then
2346 null;
2347 END;
2348
2349 IEU_UWQM_AUDIT_LOG_PKG.INSERT_ROW
2350 (
2351 P_ACTION_KEY => l_action_key,
2352 P_EVENT_KEY => l_event_key,
2353 P_MODULE => l_module,
2354 P_WS_CODE => l_ws_code,
2355 P_APPLICATION_ID => l_application_id,
2356 P_WORKITEM_PK_ID =>l_dist_items(j).workitem_pk_id,
2357 P_WORKITEM_OBJ_CODE =>l_dist_items(j).workitem_obj_code,
2358 P_WORK_ITEM_STATUS_PREV =>l_work_item_status_id,
2359 P_WORK_ITEM_STATUS_CURR =>l_work_item_status_id,
2360 P_OWNER_ID_PREV =>l_dist_items(j).owner_id,
2361 P_OWNER_ID_CURR =>l_dist_items(j).owner_id,
2362 P_OWNER_TYPE_PREV =>l_dist_items(j).owner_type,
2363 P_OWNER_TYPE_CURR =>l_dist_items(j).owner_type,
2364 P_ASSIGNEE_ID_PREV =>l_dist_items(j).assignee_id,
2365 P_ASSIGNEE_ID_CURR =>l_dist_items(j).assignee_id,
2366 P_ASSIGNEE_TYPE_PREV =>l_dist_items(j).assignee_type,
2367 P_ASSIGNEE_TYPE_CURR =>l_dist_items(j).assignee_type,
2368 P_SOURCE_OBJECT_ID_PREV =>l_dist_items(j).source_object_id,
2369 P_SOURCE_OBJECT_ID_CURR =>l_dist_items(j).source_object_id,
2370 P_SOURCE_OBJECT_TYPE_CODE_PREV =>l_dist_items(j).source_object_type_code,
2371 P_SOURCE_OBJECT_TYPE_CODE_CURR =>l_dist_items(j).source_object_type_code,
2372 P_PARENT_WORKITEM_STATUS_PREV => null,
2373 P_PARENT_WORKITEM_STATUS_CURR => null,
2374 P_PARENT_DIST_STATUS_PREV => null,
2375 P_PARENT_DIST_STATUS_CURR => null,
2376 P_WORKITEM_DIST_STATUS_PREV => l_distribution_status_id,
2377 P_WORKITEM_DIST_STATUS_CURR => l_distribution_status_id,
2378 P_PRIORITY_PREV => l_priority_id,
2379 P_PRIORITY_CURR => l_priority_id,
2380 P_DUE_DATE_PREV =>l_dist_items(j).due_date,
2381 P_DUE_DATE_CURR =>l_dist_items(j).due_date,
2382 P_RESCHEDULE_TIME_PREV => l_reschedule_time,
2383 P_RESCHEDULE_TIME_CURR => l_reschedule_time,
2384 P_IEU_COMMENT_CODE1 => l_ieu_comment_code1,
2385 P_IEU_COMMENT_CODE2 => l_ieu_comment_code2,
2386 P_IEU_COMMENT_CODE3 => l_ieu_comment_code3,
2387 P_IEU_COMMENT_CODE4 => l_ieu_comment_code4,
2388 P_IEU_COMMENT_CODE5 => l_ieu_comment_code5,
2389 P_WORKITEM_COMMENT_CODE1 => l_workitem_comment_code1,
2390 P_WORKITEM_COMMENT_CODE2 => l_workitem_comment_code2,
2391 P_WORKITEM_COMMENT_CODE3 => l_workitem_comment_code3,
2392 P_WORKITEM_COMMENT_CODE4 => l_workitem_comment_code4,
2393 P_WORKITEM_COMMENT_CODE5 => l_workitem_comment_code5,
2394 P_STATUS => l_audit_log_sts,
2395 P_ERROR_CODE => l_msg_data,
2396 X_AUDIT_LOG_ID => l_audit_log_id,
2397 X_MSG_DATA => l_msg_data,
2398 X_RETURN_STATUS => l_ret_sts);commit;
2399
2400 end if;
2401 /***************************************************************************************/
2402 exit;
2403 else
2404 if ((l_dist_items(j).DISTRIBUTED = 'FALSE') and
2405 (l_del_items_flag = 'Y') and
2406 (z = l_nw_items_list.last)) then
2407
2408 if (l_audit_log_val = 'DETAILED')
2409 then
2410 l_ieu_comment_code3 := 'DIST_FAILURE_DELIVERY_ONLY';
2411 end if;/* Audit Log Val is detailed */
2412
2413
2414 IEU_UWQ_GET_NEXT_WORK_PVT.SET_DIST_AND_DEL_ITEM_DATA_REC(p_var_in_type_code => 'REC',
2415 p_dist_workitem_data => null,
2416 p_dist_del_workitem_data => l_del_nw_item,
2420 l_num_of_items_distributed := 1;
2417 x_ctr => l_ctr,
2418 x_workitem_action_data => x_uwqm_workitem_data);
2419
2421 end if;
2422 end if;/* l_dist_items(j).DISTRIBUTED */
2423 end loop;/* l_nw_items.FIRST to LAST */
2424
2425 end if; /* l_dist_items.count > 1 */
2426
2427 end if; /* l_dist_flag */
2428
2429 --dbms_output.put_line('Num of Items Dist: '||l_num_of_items_distributed);
2430
2431 if (l_num_of_items_distributed > 0)
2432 then
2433 --dbms_output.put_line('exiting..');
2434 exit;
2435 end if;
2436
2437
2438 end loop; /*l_nw_items_list.first to last */
2439
2440 end if; /* l_delivery_only_flag */
2441
2442 -- Set the status back to 'Distributable' for the Work Items Selected for Distribution except the Distributed Work Item
2443 -- This check is required here for the following reasons
2444 -- 1. Any Item out of the 5 we are selecting for Distribution can be Distributed. If for eg. the 2nd item is Distributed
2445 -- then the Dist Status for all others should be reset here
2446 -- 2. If No Distribution was done, then the Dist Status should be reset here.
2447
2448 if (x_uwqm_workitem_data.count >= 1)
2449 then
2450 for p in x_uwqm_workitem_data.first .. x_uwqm_workitem_data.last
2451 loop
2452 if (x_uwqm_workitem_data(p).param_name = 'WORK_ITEM_ID')
2453 then
2454 l_dist_work_item_id := x_uwqm_workitem_data(p).param_value;
2455 end if;
2456 end loop;
2457 end if;
2458
2459 if (nvl(l_dist_items_flag,'Y') = 'Y')
2460 then
2461 --dbms_output.put_line('dist flag = Y.. cnt: '||l_nw_items_list.count);
2462 for y in l_nw_items_list.first..l_nw_items_list.last
2463 loop
2464 -- The work_item_id should not be Distributed Work item Id
2465 --dbms_output.put_line('Work item id: '||l_nw_items_list(y).workitem_pk_id);
2466 --dbms_output.put_line('Distributed Work Item Id: '||l_dist_work_item_id );
2467 if (l_nw_items_list(y).work_item_id <> nvl(l_dist_work_item_id,-1))
2468 then
2469 update ieu_uwqm_items
2470 set distribution_status_id = 1
2471 where work_item_id = l_nw_items_list(y).work_item_id;
2472 commit;
2473 end if;
2474 end loop;
2475 end if;
2476
2477 end loop;
2478 --dbms_output.put_line('# of items distributed '||l_num_of_items_distributed );
2479
2480 /*****************
2481 if (x_uwqm_workitem_data.count > 0)
2482 then
2483 for p in x_uwqm_workitem_data.first .. x_uwqm_workitem_data.last
2484 loop
2485 dbms_output.put_line('workitem id: '||x_uwqm_workitem_data(p).work_item_id||' obj code: '||x_uwqm_workitem_data(p).WORKITEM_OBJ_CODE||
2486 ' obj func: '||x_uwqm_workitem_data(p).IEU_OBJECT_FUNCTION ||
2487 ' params: '||x_uwqm_workitem_data(p).IEU_OBJECT_PARAMETERS);
2488 end loop;
2489 end if;
2490 ******************/
2491
2492
2493 if (x_uwqm_workitem_data.count < 1)
2494 then
2495
2496 raise fnd_api.g_exc_error;
2497 end if;
2498 --commit;
2499 EXCEPTION
2500
2501 WHEN fnd_api.g_exc_error THEN
2502 x_return_status := fnd_api.g_ret_sts_error;
2503
2504 fnd_msg_pub.Count_and_Get
2505 (
2506 p_count => x_msg_count,
2507 p_data => x_msg_data
2508 );
2509
2510 WHEN fnd_api.g_exc_unexpected_error THEN
2511
2512 x_return_status := fnd_api.g_ret_sts_unexp_error;
2513
2514 fnd_msg_pub.Count_and_Get
2515 (
2516 p_count => x_msg_count,
2517 p_data => x_msg_data
2518 );
2519
2520 WHEN OTHERS THEN
2521
2522 x_return_status := fnd_api.g_ret_sts_unexp_error;
2523
2524 IF FND_MSG_PUB.Check_msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2525 THEN
2526
2527 fnd_msg_pub.Count_and_Get
2528 (
2529 p_count => x_msg_count,
2530 p_data => x_msg_data
2531 );
2532
2533 END IF;
2534 END DISTRIBUTE_AND_DELIVER_WR_ITEM;
2535
2536 PROCEDURE DISTRIBUTE_WR_ITEMS
2537 ( p_api_version IN NUMBER,
2538 p_resource_id IN NUMBER,
2539 p_language IN VARCHAR2,
2540 p_source_lang IN VARCHAR2,
2541 p_num_of_dist_items IN NUMBER, -- Number of Items Requested to be Distributed
2542 p_extra_where_clause IN VARCHAR2,
2543 p_bindvar_list IN IEU_UWQ_BINDVAR_LIST,
2544 x_uwqm_workitem_data OUT NOCOPY IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WR_ITEM_ACT_DATA_LIST,
2545 x_num_of_items_distributed OUT NOCOPY NUMBER, -- Number of Items finally Distributed
2546 x_msg_count OUT NOCOPY NUMBER,
2547 x_msg_data OUT NOCOPY VARCHAR2,
2548 x_return_status OUT NOCOPY VARCHAR2) IS
2549
2550 -- Used to Validate API version and name
2551 l_api_version CONSTANT NUMBER := 1.0;
2552 l_api_name CONSTANT VARCHAR2(30) := 'DISTRIBUTE_WR_ITEMS';
2553
2554 l_num_of_items_distributed NUMBER := 0; -- Number of Items Distributed
2555 l_dist_workitem_data SYSTEM.WR_ITEM_DATA_NST;
2556 l_dist_items_ctr NUMBER := 1;
2557 l_ctr NUMBER := 0;
2558 l_num_of_dist_items NUMBER := 0; -- Number of Items Requested to be Distributed
2559
2560 l_object_function VARCHAR2(40);
2561 l_object_parameters VARCHAR2(500);
2562 l_enter_from_task VARCHAR2(10);
2563 l_ws_id NUMBER;
2564
2568 x_return_status := fnd_api.g_ret_sts_success;
2565
2566 BEGIN
2567
2569
2570
2571
2572 IF NOT fnd_api.compatible_api_call (
2573 l_api_version,
2574 p_api_version,
2575 l_api_name,
2576 g_pkg_name
2577 )
2578 THEN
2579 RAISE fnd_api.g_exc_unexpected_error;
2580 END IF;
2581
2582 -- Initialize Message list
2583
2584 FND_MSG_PUB.INITIALIZE;
2585
2586 x_num_of_items_distributed := 0;
2587
2588 loop
2589
2590 -- exit when one of the following conditions is satisfied
2591 -- 1. Requested Num of Items are distributed (p_num_of_dist_items - Request num of items to be distributed)
2592 -- 2. No more items in Distributable status (flag 'l_num_of_items_distributed ' will be set to -1)
2593 -- 3. Attempt distribution only 2 times. This is done for performance reasons.
2594
2595 exit when ( (l_num_of_items_distributed >= p_num_of_dist_items) OR
2596 (l_num_of_items_distributed = -1) OR
2597 ( l_dist_items_ctr > 2) OR
2598 (l_num_of_items_distributed > 0) ) ;
2599
2600 l_num_of_dist_items := p_num_of_dist_items - x_num_of_items_distributed;
2601
2602 -- dbms_output.put_line('calling get_next_wr_item..requesting '||l_num_of_dist_items ||' items');
2603
2604 l_dist_deliver_num_of_attempts := l_dist_items_ctr;
2605
2606 IEU_UWQ_GET_NEXT_WORK_PVT.GET_DIST_WR_ITEMS
2607 ( p_api_version => p_api_version,
2608 p_resource_id => p_resource_id,
2609 p_language => p_language,
2610 p_source_lang => p_source_lang,
2611 p_num_of_dist_items => l_num_of_dist_items,
2612 p_extra_where_clause => p_extra_where_clause,
2613 p_bindvar_list => p_bindvar_list,
2614 x_uwqm_workitem_data => l_dist_workitem_data,
2615 x_num_of_items_distributed => l_num_of_items_distributed,
2616 x_msg_count => x_msg_count,
2617 x_msg_data => x_msg_data,
2618 x_return_status => x_return_status);
2619
2620 l_dist_items_ctr := l_dist_items_ctr + 1;
2621
2622 -- If items were distributed, then copy values from table of objects to table of records.
2623 -- Also, set the appropriate values for Object Function, Object params etc.
2624
2625 if (l_num_of_items_distributed <> -1)
2626 then
2627
2628 -- The actual num of items distributed will be the sum of items distributed in each attempt
2629 -- x_num_of_items_distributed - Final num of items distributed
2630 -- l_num_of_items_distributed - Items distributed this time
2631
2632 x_num_of_items_distributed := x_num_of_items_distributed + l_num_of_items_distributed;
2633
2634 IEU_UWQ_GET_NEXT_WORK_PVT.SET_WR_ITEM_DATA_REC(p_var_in_type_code => 'OBJ',
2635 p_dist_workitem_data => l_dist_workitem_data,
2636 p_dist_del_workitem_data => null,
2637 x_ctr => l_ctr,
2638 x_uwqm_workitem_data => x_uwqm_workitem_data);
2639
2640 end if; /* l_num_of_items_distributed <> -1 */
2641
2642 end loop;
2643
2644 --dbms_output.put_line('# of items distributed '||x_num_f_items_distributed ||' cnt: '||x_uwqm_workitem_data.count);
2645
2646
2647 EXCEPTION
2648
2649 WHEN fnd_api.g_exc_error THEN
2650
2651 x_return_status := fnd_api.g_ret_sts_error;
2652
2653 fnd_msg_pub.Count_and_Get
2654 (
2655 p_count => x_msg_count,
2656 p_data => x_msg_data
2657 );
2658
2659 WHEN fnd_api.g_exc_unexpected_error THEN
2660
2661 x_return_status := fnd_api.g_ret_sts_unexp_error;
2662
2663 fnd_msg_pub.Count_and_Get
2664 (
2665 p_count => x_msg_count,
2666 p_data => x_msg_data
2667 );
2668
2669 WHEN OTHERS THEN
2670
2671 x_return_status := fnd_api.g_ret_sts_unexp_error;
2672
2673 IF FND_MSG_PUB.Check_msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2674 THEN
2675
2676 fnd_msg_pub.Count_and_Get
2677 (
2678 p_count => x_msg_count,
2679 p_data => x_msg_data
2680 );
2681
2682 END IF;
2683 END DISTRIBUTE_WR_ITEMS;
2684
2685
2686 /**
2687 ** Used in Proc - Distribute_wr_items, distribute_and_deliver_wr_item
2688 **/
2689
2690 /************* Open issues ***********************
2691 *** 1. Handling multiple bind variables - Restrictions on usage of Ref Cursors/Open-for
2692 *** ex: sql_stmt := 'select .... where owner_id = :resource_id or assignee_id = :resource_id';
2693 *** open cur for sql_stmt using In l_res_id
2694 *** 2. Performance enh#
2695 *** - indexes, loops, proc calls
2696 *** 3. Setting Distributing status back to distributable after 2 attempts
2697 **************************************************/
2698
2699 PROCEDURE GET_DIST_WR_ITEMS
2700 ( p_api_version IN NUMBER,
2701 p_resource_id IN NUMBER,
2702 p_language IN VARCHAR2,
2703 p_source_lang IN VARCHAR2,
2704 p_num_of_dist_items IN NUMBER,
2705 p_extra_where_clause IN VARCHAR2,
2706 p_bindvar_list IN IEU_UWQ_BINDVAR_LIST,
2707 x_uwqm_workitem_data OUT NOCOPY SYSTEM.WR_ITEM_DATA_NST,
2708 x_num_of_items_distributed OUT NOCOPY NUMBER,
2712
2709 x_msg_count OUT NOCOPY NUMBER,
2710 x_msg_data OUT NOCOPY VARCHAR2,
2711 x_return_status OUT NOCOPY VARCHAR2) IS
2713 -- Used to Validate API version and name
2714 l_api_version CONSTANT NUMBER := 1.0;
2715 l_api_name CONSTANT VARCHAR2(30) := 'GET_DIST_WR_ITEMS';
2716
2717
2718 l_sql_stmt VARCHAR2(4000);
2719 l_dist_status NUMBER := 1;
2720 l_open_status_id NUMBER := 0;
2721 l_resource_id NUMBER := 100001713;
2722 l_next_wr_items IEU_UWQ_GET_NEXT_WORK_PVT.l_get_work;
2723
2724
2725 -- Table of records for all OUT variables
2726 l_work_item_num NUMBER;
2727
2728 l_wr_cur IEU_UWQ_GET_NEXT_WORK_PVT.l_get_work;
2729 l_nw_item IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WR_ITEM_DATA_REC := null;
2730 l_nw_items_list IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WR_ITEM_DATA;
2731
2732 l_nw_items_list2 IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WR_ITEM_DATA;
2733 l_nw_ctr number := 1;
2734 z number := 0;
2735
2736 l_num_of_dist_items_incr number := 0;
2737
2738 /*
2739 l_dist_items IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WR_DIST_ITEM_DATA;
2740 l_dist_bus_rules IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WR_DIST_RULES;
2741 */
2742
2743 l_dist_items SYSTEM.WR_ITEM_DATA_NST;
2744 l_dist_bus_rules SYSTEM.DIST_BUS_RULES_NST;
2745
2746 L_MSG_COUNT NUMBER;
2747 L_MSG_DATA VARCHAR2(4000);
2748 L_RETURN_STATUS VARCHAR2(10);
2749 l_ctr NUMBER := 1;
2750 l_curr_ws_id NUMBER;
2751 l_priority_code IEU_UWQM_PRIORITIES_B.PRIORITY_CODE%TYPE;
2752 l_ws_code VARCHAR2(500);
2753 l_work_item_status VARCHAR2(500);
2754 l_work_item_status_id NUMBER;
2755 l_wr_cur_cnt NUMBER;
2756 cursor_id PLS_INTEGER;
2757 dummy PLS_INTEGER;
2758 temp number;
2759
2760
2761 l_not_valid_flag VARCHAR2(1);
2762 cursor c_ws is
2763 select WS_B.WS_ID, 'INDIVIDUAL_ASSIGNED' DISTRIBUTE_TO, 'GROUP_OWNED' DISTRIBUTE_FROM , WS_B.DISTRIBUTION_FUNCTION ,
2764 WS_A.DIST_ST_BASED_ON_PARENT_FLAG, WS_B.WS_CODE
2765 from IEU_UWQM_WORK_SOURCES_B WS_B, IEU_UWQM_WS_ASSCT_PROPS WS_A
2766 where ws_b.not_valid_flag = l_not_valid_flag
2767 and ws_b.ws_id = ws_a.ws_id(+);
2768
2769 -- Audit Trail
2770 l_action_key VARCHAR2(500);
2771 l_event_key VARCHAR2(500);
2772 l_module VARCHAR2(1000);
2773 l_application_id NUMBER;
2774 --l_ws_code VARCHAR2(500);
2775 l_ret_sts VARCHAR2(10);
2776 l_audit_log_val VARCHAR2(100);
2777 l_ieu_comment_code1 VARCHAR2(2000);
2778 l_ieu_comment_code2 VARCHAR2(2000);
2779 l_ieu_comment_code3 VARCHAR2(2000);
2780 l_ieu_comment_code4 VARCHAR2(2000);
2781 l_ieu_comment_code5 VARCHAR2(2000);
2782 l_workitem_comment_code1 VARCHAR2(2000);
2783 l_workitem_comment_code2 VARCHAR2(2000);
2784 l_workitem_comment_code3 VARCHAR2(2000);
2785 l_workitem_comment_code4 VARCHAR2(2000);
2786 l_workitem_comment_code5 VARCHAR2(2000);
2787
2788 l_workitem_pk_id NUMBER;
2789 l_workitem_obj_code VARCHAR2(50);
2790 l_audit_log_sts VARCHAR2(50);
2791 l_owner_id NUMBER;
2792 l_owner_type VARCHAR2(500);
2793 l_assignee_id NUMBER;
2794 l_assignee_type VARCHAR2(500);
2795 l_priority_id NUMBER;
2796 l_due_date DATE;
2797 l_source_object_id NUMBER;
2798 l_source_object_type_code VARCHAR2(500);
2799 l_status_id NUMBER;
2800 l_distribution_status_id NUMBER;
2801 l_reschedule_time DATE;
2802 l_token_str VARCHAR2(4000);
2803 -- l_audit_log_id NUMBER;
2804 TYPE AUDIT_LOG_ID_TBL is TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2805 l_audit_log_id_list AUDIT_LOG_ID_TBL;
2806 v varchar2(1000);
2807 BEGIN
2808 l_not_valid_flag := 'N';
2809 x_return_status := fnd_api.g_ret_sts_success;
2810
2811 l_audit_log_val := FND_PROFILE.VALUE('IEU_WR_DIST_AUDIT_LOG');
2812
2813 IF NOT fnd_api.compatible_api_call (
2814 l_api_version,
2815 p_api_version,
2816 l_api_name,
2817 g_pkg_name
2818 )
2819 THEN
2820 RAISE fnd_api.g_exc_unexpected_error;
2821 END IF;
2822
2823 -- Initialize Message list
2824
2825 FND_MSG_PUB.INITIALIZE;
2826
2827 x_uwqm_workitem_data := SYSTEM.WR_ITEM_DATA_NST();
2828
2829 -- Get the Where Clause
2830 -- IEU_UWQ_GET_NEXT_WORK_PVT.GET_WS_WHERE_CLAUSE('DIST_FROM',l_where_clause);
2831
2832 -- Build the complete select stmt
2833 l_sql_stmt := 'SELECT /*+ first_rows */
2834 WORK_ITEM_ID,
2835 WORKITEM_OBJ_CODE,
2836 WORKITEM_PK_ID,
2837 STATUS_ID,
2838 PRIORITY_ID,
2839 PRIORITY_LEVEL,
2840 null, -- Selecting null for pty code
2841 DUE_DATE,
2842 TITLE,
2843 PARTY_ID,
2844 OWNER_ID,
2845 OWNER_TYPE,
2846 ASSIGNEE_ID,
2847 ASSIGNEE_TYPE,
2848 SOURCE_OBJECT_ID,
2849 SOURCE_OBJECT_TYPE_CODE,
2850 APPLICATION_ID,
2851 IEU_ENUM_TYPE_UUID,
2852 WORK_ITEM_NUMBER,
2853 RESCHEDULE_TIME,
2854 WS_ID
2855 FROM IEU_UWQM_ITEMS '||
2856 ' WHERE ' || ' ( ' ||p_extra_where_clause || ' ) '||
2857 ' AND DISTRIBUTION_STATUS_ID = :l_dist_status' ||
2858 ' AND STATUS_ID = :l_status_id ' ||
2859 ' and reschedule_time <= sysdate ' ||
2860 -- l_where_clause ||' ) '||
2861 -- ' ) AND rownum <= '|| p_num_of_dist_items||
2862 -- ' ) AND rownum <= :p_num_of_dist_items '||
2863 ' order by priority_level, due_date '||
2864 ' for update skip locked ';
2865
2866 -- 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;
2867
2871 -- USING IN l_dist_status, IN l_open_status_id;
2868 -- Select the items based on Business rules
2869
2870 -- OPEN l_wr_cur FOR l_sql_stmt
2872
2873 cursor_id := dbms_sql.open_cursor;
2874 DBMS_SQL.PARSE(cursor_id, l_sql_stmt, dbms_sql.native);
2875 DBMS_SQL.BIND_VARIABLE(cursor_id,':l_dist_status', l_dist_status);
2876 DBMS_SQL.BIND_VARIABLE(cursor_id,':l_status_id', l_open_status_id);
2877
2878 for i in 1..p_bindvar_list.count loop
2879 DBMS_SQL.BIND_VARIABLE(cursor_id,p_bindvar_list(i).bind_name, p_bindvar_list(i).value);
2880
2881 end loop;
2882
2883
2884 -- USING IN l_dist_status, IN l_open_status_id, IN p_resource_id, IN p_num_of_dist_items;
2885 -- USING IN l_dist_status, IN l_open_status_id, IN p_resource_id;
2886
2887 l_wr_cur_cnt := 1;
2888
2889 DBMS_SQL.DEFINE_COLUMN(cursor_id,1,l_nw_item.WORK_ITEM_ID);
2890 DBMS_SQL.DEFINE_COLUMN(cursor_id,2,l_nw_item.WORKITEM_OBJ_CODE,30);
2891 DBMS_SQL.DEFINE_COLUMN(cursor_id,3,l_nw_item.WORKITEM_PK_ID);
2892 DBMS_SQL.DEFINE_COLUMN(cursor_id,4,l_nw_item.STATUS_ID);
2893 DBMS_SQL.DEFINE_COLUMN(cursor_id,5,l_nw_item.PRIORITY_ID);
2894 DBMS_SQL.DEFINE_COLUMN(cursor_id,6,l_nw_item.PRIORITY_LEVEL);
2895 DBMS_SQL.DEFINE_COLUMN(cursor_id,7,l_nw_item.PRIORITY_CODE,30);
2896 DBMS_SQL.DEFINE_COLUMN(cursor_id,8,l_nw_item.DUE_DATE);
2897 DBMS_SQL.DEFINE_COLUMN(cursor_id,9,l_nw_item.TITLE,1990);
2898 DBMS_SQL.DEFINE_COLUMN(cursor_id,10,l_nw_item.PARTY_ID);
2899 DBMS_SQL.DEFINE_COLUMN(cursor_id,11,l_nw_item.OWNER_ID);
2900 DBMS_SQL.DEFINE_COLUMN(cursor_id,12,l_nw_item.OWNER_TYPE,25);
2901 DBMS_SQL.DEFINE_COLUMN(cursor_id,13,l_nw_item.ASSIGNEE_ID);
2902 DBMS_SQL.DEFINE_COLUMN(cursor_id,14,l_nw_item.ASSIGNEE_TYPE,25);
2903 DBMS_SQL.DEFINE_COLUMN(cursor_id,15,l_nw_item.SOURCE_OBJECT_ID);
2904 DBMS_SQL.DEFINE_COLUMN(cursor_id,16,l_nw_item.SOURCE_OBJECT_TYPE_CODE,30);
2905 DBMS_SQL.DEFINE_COLUMN(cursor_id,17,l_nw_item.APPLICATION_ID);
2906 DBMS_SQL.DEFINE_COLUMN(cursor_id,18,l_nw_item.IEU_ENUM_TYPE_UUID,38);
2907 DBMS_SQL.DEFINE_COLUMN(cursor_id,19,l_nw_item.WORK_ITEM_NUMBER,64);
2908 DBMS_SQL.DEFINE_COLUMN(cursor_id,20,l_nw_item.RESCHEDULE_TIME);
2909 DBMS_SQL.DEFINE_COLUMN(cursor_id,21,l_nw_item.WS_ID);
2910
2911
2912
2913 -- FETCH l_wr_cur into l_nw_item;
2914
2915 dummy := DBMS_SQL.EXECUTE(cursor_id);
2916
2917 LOOP
2918 temp := DBMS_SQL.FETCH_ROWS(cursor_id);
2919
2920 if p_num_of_dist_items <= 2 then
2921 l_num_of_dist_items_incr := p_num_of_dist_items * 4;
2922 elsif p_num_of_dist_items > 2 and p_num_of_dist_items <= 4 then
2923 l_num_of_dist_items_incr := p_num_of_dist_items * 3;
2924 elsif p_num_of_dist_items > 4 and p_num_of_dist_items <=6 then
2925 l_num_of_dist_items_incr := P_num_of_dist_items * 2;
2926 elsif p_num_of_dist_items > 6 then
2927 l_num_of_dist_items_incr := p_num_of_dist_items;
2928 end if;
2929 if temp = 0 or (l_wr_cur_cnt > l_num_of_dist_items_incr) then
2930 exit;
2931 elsif temp <> 0 then
2932
2933 DBMS_SQL.COLUMN_VALUE(cursor_id,1,l_nw_item.WORK_ITEM_ID);
2934 DBMS_SQL.COLUMN_VALUE(cursor_id,2,l_nw_item.WORKITEM_OBJ_CODE);
2935 DBMS_SQL.COLUMN_VALUE(cursor_id,3,l_nw_item.WORKITEM_PK_ID);
2936 DBMS_SQL.COLUMN_VALUE(cursor_id,4,l_nw_item.STATUS_ID);
2937 DBMS_SQL.COLUMN_VALUE(cursor_id,5,l_nw_item.PRIORITY_ID);
2938 DBMS_SQL.COLUMN_VALUE(cursor_id,6,l_nw_item.PRIORITY_LEVEL);
2939 DBMS_SQL.COLUMN_VALUE(cursor_id,7,l_nw_item.PRIORITY_CODE);
2940 DBMS_SQL.COLUMN_VALUE(cursor_id,8,l_nw_item.DUE_DATE);
2941 DBMS_SQL.COLUMN_VALUE(cursor_id,9,l_nw_item.TITLE);
2942 DBMS_SQL.COLUMN_VALUE(cursor_id,10,l_nw_item.PARTY_ID);
2943 DBMS_SQL.COLUMN_VALUE(cursor_id,11,l_nw_item.OWNER_ID);
2944 DBMS_SQL.COLUMN_VALUE(cursor_id,12,l_nw_item.OWNER_TYPE);
2945 DBMS_SQL.COLUMN_VALUE(cursor_id,13,l_nw_item.ASSIGNEE_ID);
2946 DBMS_SQL.COLUMN_VALUE(cursor_id,14,l_nw_item.ASSIGNEE_TYPE);
2947 DBMS_SQL.COLUMN_VALUE(cursor_id,15,l_nw_item.SOURCE_OBJECT_ID);
2948 DBMS_SQL.COLUMN_VALUE(cursor_id,16,l_nw_item.SOURCE_OBJECT_TYPE_CODE);
2949 DBMS_SQL.COLUMN_VALUE(cursor_id,17,l_nw_item.APPLICATION_ID);
2950 DBMS_SQL.COLUMN_VALUE(cursor_id,18,l_nw_item.IEU_ENUM_TYPE_UUID);
2951 DBMS_SQL.COLUMN_VALUE(cursor_id,19,l_nw_item.WORK_ITEM_NUMBER);
2952 DBMS_SQL.COLUMN_VALUE(cursor_id,20,l_nw_item.RESCHEDULE_TIME);
2953 DBMS_SQL.COLUMN_VALUE(cursor_id,21,l_nw_item.WS_ID);
2954 end if;
2955
2956
2957
2958 -- exit when ( (l_wr_cur%NOTFOUND) OR (l_wr_cur_cnt > l_num_of_dist_items_incr) ) ;
2959
2960 l_wr_cur_cnt := l_wr_cur_cnt + 1;
2961
2962 -- update work item status to distributing
2963 update ieu_uwqm_items
2964 set distribution_status_id = 2
2965 where work_item_id = l_nw_item.WORK_ITEM_ID;
2966
2967
2968 -- Add items to the Table of rec
2969 select priority_code
2970 into l_priority_code
2971 from ieu_uwqm_priorities_b
2972 where priority_id = l_nw_item.PRIORITY_ID;
2973
2974 l_nw_items_list(l_ctr).WORK_ITEM_ID := l_nw_item.WORK_ITEM_ID;
2975 l_nw_items_list(l_ctr).WORKITEM_OBJ_CODE := l_nw_item.WORKITEM_OBJ_CODE;
2976 l_nw_items_list(l_ctr).WORKITEM_PK_ID := l_nw_item.WORKITEM_PK_ID;
2977 l_nw_items_list(l_ctr).STATUS_ID := l_nw_item.STATUS_ID;
2978 l_nw_items_list(l_ctr).PRIORITY_CODE := l_priority_code;
2982 l_nw_items_list(l_ctr).OWNER_ID := l_nw_item.OWNER_ID;
2979 l_nw_items_list(l_ctr).DUE_DATE := l_nw_item.DUE_DATE;
2980 l_nw_items_list(l_ctr).TITLE := l_nw_item.TITLE;
2981 l_nw_items_list(l_ctr).PARTY_ID := l_nw_item.PARTY_ID;
2983 l_nw_items_list(l_ctr).OWNER_TYPE := l_nw_item.OWNER_TYPE;
2984 l_nw_items_list(l_ctr).ASSIGNEE_ID := l_nw_item.ASSIGNEE_ID;
2985 l_nw_items_list(l_ctr).ASSIGNEE_TYPE := l_nw_item.ASSIGNEE_TYPE;
2986 l_nw_items_list(l_ctr).SOURCE_OBJECT_ID := l_nw_item.SOURCE_OBJECT_ID;
2987 l_nw_items_list(l_ctr).SOURCE_OBJECT_TYPE_CODE := l_nw_item.SOURCE_OBJECT_TYPE_CODE;
2988 l_nw_items_list(l_ctr).APPLICATION_ID := l_nw_item.APPLICATION_ID;
2989 l_nw_items_list(l_ctr).IEU_ENUM_TYPE_UUID := l_nw_item.IEU_ENUM_TYPE_UUID;
2990 l_nw_items_list(l_ctr).WORK_ITEM_NUMBER := l_nw_item.WORK_ITEM_NUMBER;
2991 l_nw_items_list(l_ctr).RESCHEDULE_TIME := l_nw_item.RESCHEDULE_TIME;
2992 l_nw_items_list(l_ctr).WS_ID := l_nw_item.WS_ID;
2993
2994 l_ctr := l_ctr + 1;
2995
2996 END LOOP;
2997 DBMS_SQL.CLOSE_CURSOR(cursor_id);
2998
2999 -- CLOSE l_wr_cur;
3000 commit;
3001
3002 -- dbms_output.put_line('item cnt: '||l_nw_items_list.COUNT);
3003
3004 -- Check if there any any Distributable Items for this resource
3005 -- x_num_of_items_distributed will be set to -1 if there are no Distributable Items
3006
3007 if (l_nw_items_list.COUNT < 1)
3008 then
3009 x_num_of_items_distributed := -1;
3010 else
3011 x_num_of_items_distributed := 0;
3012 end if;
3013
3014 --dbms_output.put_line('dist flag: '||x_num_of_items_distributed);
3015
3016 -- If there are distributable items for this resource then
3017 -- 1. get the distribution rules for each work source
3018 -- 2. Select the Distributable Work Item details
3019 -- 3. call the appropriate distribution function based on the Work Source
3020
3021 if (x_num_of_items_distributed <> -1)
3022 then
3023
3024 while (l_nw_ctr <= l_nw_items_list.count)
3025 loop
3026
3027 z := z +1;
3028
3029 -- insert into p_temp values(p_num_of_dist_items||' '||x_num_of_items_distributed||' '||z||' '||l_nw_ctr, 10001);commit;
3030
3031 if (z <= (p_num_of_dist_items - x_num_of_items_distributed)) then
3032
3033 l_nw_items_list2(z).WORK_ITEM_ID := l_nw_items_list(l_nw_ctr).WORK_ITEM_ID;
3034 l_nw_items_list2(z).WORKITEM_OBJ_CODE := l_nw_items_list(l_nw_ctr).WORKITEM_OBJ_CODE;
3035 l_nw_items_list2(z).WORKITEM_PK_ID := l_nw_items_list(l_nw_ctr).WORKITEM_PK_ID;
3036 l_nw_items_list2(z).STATUS_ID := l_nw_items_list(l_nw_ctr).STATUS_ID;
3037 l_nw_items_list2(z).PRIORITY_CODE := l_nw_items_list(l_nw_ctr).priority_code;
3038 l_nw_items_list2(z).DUE_DATE := l_nw_items_list(l_nw_ctr).DUE_DATE;
3039 l_nw_items_list2(z).TITLE := l_nw_items_list(l_nw_ctr).TITLE;
3040 l_nw_items_list2(z).PARTY_ID := l_nw_items_list(l_nw_ctr).PARTY_ID;
3041 l_nw_items_list2(z).OWNER_ID := l_nw_items_list(l_nw_ctr).OWNER_ID;
3042 l_nw_items_list2(z).OWNER_TYPE := l_nw_items_list(l_nw_ctr).OWNER_TYPE;
3043 l_nw_items_list2(z).ASSIGNEE_ID := l_nw_items_list(l_nw_ctr).ASSIGNEE_ID;
3044 l_nw_items_list2(z).ASSIGNEE_TYPE := l_nw_items_list(l_nw_ctr).ASSIGNEE_TYPE;
3045 l_nw_items_list2(z).SOURCE_OBJECT_ID := l_nw_items_list(l_nw_ctr).SOURCE_OBJECT_ID;
3046 l_nw_items_list2(z).SOURCE_OBJECT_TYPE_CODE := l_nw_items_list(l_nw_ctr).SOURCE_OBJECT_TYPE_CODE;
3047 l_nw_items_list2(z).APPLICATION_ID := l_nw_items_list(l_nw_ctr).APPLICATION_ID;
3048 l_nw_items_list2(z).IEU_ENUM_TYPE_UUID := l_nw_items_list(l_nw_ctr).IEU_ENUM_TYPE_UUID;
3049 l_nw_items_list2(z).WORK_ITEM_NUMBER := l_nw_items_list(l_nw_ctr).WORK_ITEM_NUMBER;
3050 l_nw_items_list2(z).RESCHEDULE_TIME := l_nw_items_list(l_nw_ctr).RESCHEDULE_TIME;
3051 l_nw_items_list2(z).WS_ID := l_nw_items_list(l_nw_ctr).WS_ID;
3052
3053 end if;
3054
3055 if x_num_of_items_distributed = p_num_of_dist_items then
3056 exit;
3057 else
3058 l_nw_ctr := l_nw_ctr + z;
3059 z := 0;
3060 end if;
3061
3062
3063 -- dbms_output.put_line('getting ws id');
3064 -- loop thru all seeded Work sources
3065 for cur_rec in c_ws
3066 loop
3067
3068 l_curr_ws_id := cur_rec.ws_id;
3069 l_ws_code := cur_rec.ws_code;
3070
3071 /*
3072 begin
3073 select ws_code
3074 into l_ws_name
3075 from ieu_uwqm_work_sources_b
3076 where ws_id = l_curr_ws_id;
3077 exception
3078 when others then
3079 l_ws_name := '';
3080 end;
3081 */
3082 --dbms_output.put_line('curr ws id: '||l_curr_ws_id);
3083
3084 -- Get the Business rules to be passed to the Distribution Function
3085 l_dist_bus_rules := SYSTEM.DIST_BUS_RULES_NST();
3086
3087 l_dist_bus_rules.extend;
3088 l_dist_bus_rules(l_dist_bus_rules.last) := SYSTEM.DIST_BUS_RULES_OBJ ( l_ws_code,
3089 cur_rec.distribute_from,
3090 cur_rec.distribute_to,
3091 cur_rec.DIST_ST_BASED_ON_PARENT_FLAG);
3092
3093
3094 if (l_audit_log_val = 'DETAILED')
3095 then
3096
3100 l_ieu_comment_code3 := 'GO_IO';
3097 if (cur_rec.distribute_from = 'GROUP_OWNED') and
3098 (cur_rec.distribute_to = 'INDIVIDUAL_OWNED')
3099 then
3101 elsif (cur_rec.distribute_from = 'GROUP_OWNED') and
3102 (cur_rec.distribute_to = 'INDIVIDUAL_ASSIGNED')
3103 then
3104 l_ieu_comment_code3 := 'GO_IA';
3105 elsif (cur_rec.distribute_from = 'GROUP_ASSIGNED') and
3106 (cur_rec.distribute_to = 'INDIVIDUAL_OWNED')
3107 then
3108 l_ieu_comment_code3 := 'GA_IO';
3109 elsif (cur_rec.distribute_from = 'GROUP_ASSIGNED') and
3110 (cur_rec.distribute_to = 'INDIVIDUAL_ASSIGNED')
3111 then
3112 l_ieu_comment_code3 := 'GA_IA';
3113 end if;
3114 end if;
3115 --dbms_output.put_line('loop 5');
3116
3117 --dbms_output.put_line('bus rules: '||l_dist_bus_rules.count);
3118
3119 -- Initialize this table for new WS
3120 l_dist_items := SYSTEM.WR_ITEM_DATA_NST();
3121
3122 for i in l_nw_items_list2.first .. l_nw_items_list2.last
3123 loop
3124
3125
3126 -- group the Distributable Work Items based on Work Source
3127 if (l_nw_items_list2(i).ws_id = l_curr_ws_id)
3128 then
3129
3130 if (l_nw_items_list2(i).STATUS_ID = 0)
3131 then
3132 l_work_item_status := 'OPEN';
3133 elsif (l_nw_items_list2(i).STATUS_ID = 3)
3134 then
3135 l_work_item_status := 'CLOSE';
3136 elsif (l_nw_items_list2(i).STATUS_ID = 4)
3137 then
3138 l_work_item_status := 'DELETE';
3139 elsif (l_nw_items_list2(i).STATUS_ID = 5)
3140 then
3141 l_work_item_status := 'SLEEP';
3142 end if;
3143
3144 --dbms_output.put_line('ws id matches: '||l_nw_items_list(i).ws_id|| ' ID: '||l_nw_items_list(i).WORKITEM_PK_ID);
3145 l_dist_items.extend;
3146 l_dist_items(l_dist_items.last) := SYSTEM.WR_ITEM_DATA_OBJ(l_nw_items_list2(i).WORK_ITEM_ID,
3147 l_nw_items_list2(i).WORKITEM_OBJ_CODE,
3148 l_nw_items_list2(i).WORKITEM_PK_ID,
3149 l_work_item_status,
3150 l_nw_items_list2(i).PRIORITY_ID,
3151 l_nw_items_list2(i).PRIORITY_LEVEL,
3152 l_nw_items_list2(i).PRIORITY_CODE,
3153 l_nw_items_list2(i).DUE_DATE,
3154 l_nw_items_list2(i).TITLE,
3155 l_nw_items_list2(i).PARTY_ID,
3156 l_nw_items_list2(i).OWNER_ID,
3157 l_nw_items_list2(i).OWNER_TYPE,
3158 l_nw_items_list2(i).ASSIGNEE_ID,
3159 l_nw_items_list2(i).ASSIGNEE_TYPE,
3160 l_nw_items_list2(i).SOURCE_OBJECT_ID,
3161 l_nw_items_list2(i).SOURCE_OBJECT_TYPE_CODE,
3162 l_nw_items_list2(i).APPLICATION_ID,
3163 l_nw_items_list2(i).IEU_ENUM_TYPE_UUID,
3164 l_nw_items_list2(i).WORK_ITEM_NUMBER,
3165 l_nw_items_list2(i).RESCHEDULE_TIME,
3166 l_ws_code, --l_nw_items_list(i).WS_ID,
3167 null,
3168 null);
3169 end if;
3170
3171 end loop; /* l_nw_items_list2 */
3172
3173
3174 --dbms_output.put_line('dist items cnt'||l_dist_items.count);
3175
3176 -- Call the Distribution Function
3177
3178
3179 if (l_dist_items.count > 0)
3180 then
3181 -- insert into p_temp values('calling dist func', 1001);commit;
3182 --dbms_output.put_line('calling dist func');
3183
3184 if ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') OR (l_audit_log_val = 'MINIMAL') )
3185 then
3186
3187 for k in l_dist_items.first .. l_dist_items.last
3188 loop
3189
3190 IEU_UWQM_AUDIT_LOG_PKG.INSERT_ROW
3191 (
3192 P_ACTION_KEY => l_action_key,
3193 P_EVENT_KEY => l_event_key,
3194 P_MODULE => l_module,
3195 P_WS_CODE => l_ws_code,
3196 P_APPLICATION_ID => l_application_id,
3197 P_WORKITEM_PK_ID => l_dist_items(k).workitem_pk_id,
3198 P_WORKITEM_OBJ_CODE => l_dist_items(k).workitem_obj_code,
3199 P_WORK_ITEM_STATUS_PREV => l_status_id,
3200 P_WORK_ITEM_STATUS_CURR => l_status_id,
3201 P_OWNER_ID_PREV => l_dist_items(k).owner_id,
3202 P_OWNER_ID_CURR => l_dist_items(k).owner_id,
3203 P_OWNER_TYPE_PREV => l_dist_items(k).owner_type,
3204 P_OWNER_TYPE_CURR => l_dist_items(k).owner_type,
3205 P_ASSIGNEE_ID_PREV => null,
3206 P_ASSIGNEE_ID_CURR => l_dist_items(k).assignee_id,
3207 P_ASSIGNEE_TYPE_PREV => null,
3208 P_ASSIGNEE_TYPE_CURR => l_dist_items(k).assignee_type,
3209 P_SOURCE_OBJECT_ID_PREV => l_dist_items(k).source_object_id,
3210 P_SOURCE_OBJECT_ID_CURR => l_dist_items(k).source_object_id,
3211 P_SOURCE_OBJECT_TYPE_CODE_PREV => l_dist_items(k).source_object_type_code,
3212 P_SOURCE_OBJECT_TYPE_CODE_CURR => l_dist_items(k).source_object_type_code,
3213 P_PARENT_WORKITEM_STATUS_PREV => null,
3214 P_PARENT_WORKITEM_STATUS_CURR => null,
3215 P_PARENT_DIST_STATUS_PREV => null,
3216 P_PARENT_DIST_STATUS_CURR => null,
3217 P_WORKITEM_DIST_STATUS_PREV => l_distribution_status_id,
3218 P_WORKITEM_DIST_STATUS_CURR => l_distribution_status_id,
3219 P_PRIORITY_PREV => l_dist_items(k).priority_id,
3220 P_PRIORITY_CURR => l_dist_items(k).priority_id,
3221 P_DUE_DATE_PREV => l_dist_items(k).due_date,
3222 P_DUE_DATE_CURR => l_dist_items(k).due_date,
3226 P_IEU_COMMENT_CODE2 => l_ieu_comment_code2,
3223 P_RESCHEDULE_TIME_PREV => l_reschedule_time,
3224 P_RESCHEDULE_TIME_CURR => l_reschedule_time,
3225 P_IEU_COMMENT_CODE1 => l_ieu_comment_code1,
3227 P_IEU_COMMENT_CODE3 => l_ieu_comment_code3,
3228 P_IEU_COMMENT_CODE4 => l_ieu_comment_code4,
3229 P_IEU_COMMENT_CODE5 => l_ieu_comment_code5,
3230 P_WORKITEM_COMMENT_CODE1 => l_workitem_comment_code1,
3231 P_WORKITEM_COMMENT_CODE2 => l_workitem_comment_code2,
3232 P_WORKITEM_COMMENT_CODE3 => l_workitem_comment_code3,
3233 P_WORKITEM_COMMENT_CODE4 => l_workitem_comment_code4,
3234 P_WORKITEM_COMMENT_CODE5 => l_workitem_comment_code5,
3235 P_STATUS => 'S',
3236 P_ERROR_CODE => x_msg_data,
3237 X_AUDIT_LOG_ID => l_audit_log_id_list(k),
3238 X_MSG_DATA => x_msg_data,
3239 X_RETURN_STATUS => l_ret_sts
3240 );
3241
3242 end loop;
3243 end if;
3244
3245 -- Set the Resource_id and type in IEU_WR_PUB
3246 --IEU_WR_PUB.l_dist_resource_id := p_resource_id;
3247 --IEU_WR_PUB.l_dist_resource_type := 'RS_INDIVIDUAL';
3248
3249 BEGIN
3250 EXECUTE IMMEDIATE
3251 'BEGIN '|| cur_rec.DISTRIBUTION_FUNCTION||'(:1,:2,:3,:4,:5,:6,:7,:8,:9); END;'
3252 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,
3253 OUT L_MSG_COUNT, OUT L_MSG_DATA, OUT L_RETURN_STATUS;
3254 EXCEPTION
3255 when others then
3256
3257 -- insert into p_temp(msg) values('exception');
3258 -- Set the status back from 'Distributing' to 'Distributable'
3259 for k in l_dist_items.first .. l_dist_items.last
3260 loop
3261 l_workitem_pk_id := l_dist_items(k).workitem_pk_id;
3262 l_workitem_obj_code := l_dist_items(k).workitem_obj_code;
3263 l_owner_id := l_dist_items(k).owner_id;
3264 l_owner_type := l_dist_items(k).owner_type;
3265 l_assignee_id := l_dist_items(k).assignee_id;
3266 l_assignee_type := l_dist_items(k).assignee_type;
3267 l_priority_id := l_dist_items(k).priority_id;
3268 l_due_date := l_dist_items(k).due_date;
3269 l_source_object_id := l_dist_items(k).source_object_id;
3270 l_source_object_type_code := l_dist_items(k).source_object_type_code;
3271 if (l_dist_items(k).work_item_status = 'OPEN')
3272 then
3273 l_status_id := 0;
3274 elsif (l_dist_items(k).work_item_status = 'CLOSE')
3275 then
3276 l_status_id := 3;
3277 elsif (l_dist_items(k).work_item_status = 'DELETE')
3278 then
3279 l_status_id := 4;
3280 elsif (l_dist_items(k).work_item_status = 'SLEEP')
3281 then
3282 l_status_id := 5;
3283 end if;
3284
3285 -- insert into p_temp values('dist func failed '||l_return_status||' '||l_msg_data, l_dist_items(k).work_item_id);commit;
3286 update ieu_uwqm_items
3287 set distribution_status_id = 1
3288 where work_item_id = l_dist_items(k).work_item_id;
3289 commit;
3290
3291 -- Set the Resource_id and type in IEU_WR_PUB
3292
3293 l_action_key := 'DISTRIBUTION';
3294 if ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED'))
3295 then
3296 l_event_key := 'DISTRIBUTE';
3297 else
3298 l_event_key := null;
3299 end if;
3300 l_module := 'IEU_GET_NEXT_WORK_PVT.DISTRIBUTE_WR_ITEMS';
3301 l_application_id := 696;
3302 l_ret_sts := 'E';
3303 l_token_str := SQLCODE||': '||SQLERRM;
3304 --l_token_str := SQLERRM;
3305 --insert into p_temp('errcode: '||SQLCODE);
3306 --insert inot p_temp('errm: '||SQLERRM);
3307
3308 FND_MSG_PUB.INITIALIZE;
3309 FND_MESSAGE.SET_NAME('IEU', 'IEU_SQL_ERROR');
3310 FND_MESSAGE.SET_TOKEN('PACKAGE_NAME','IEU_GET_NEXT_WORK_PVT.DISTRIBUTE_AND_DELIVER');
3311 FND_MESSAGE.SET_TOKEN('SQL_ERROR_MSG',l_token_str);
3312 fnd_msg_pub.ADD;
3313
3314 fnd_msg_pub.Count_and_Get
3315 (
3316 p_count => x_msg_count,
3317 p_data => x_msg_data
3318 );
3319
3320
3321 if (l_audit_log_val = 'DETAILED')
3322 then
3323 l_ieu_comment_code1 := 'NUM_OF_ATTEMPTS '||l_dist_deliver_num_of_attempts;
3324 l_ieu_comment_code2 := 'DISTRIBUTION_FUNC '||cur_rec.DISTRIBUTION_FUNCTION;
3325 end if;
3326
3327
3328 if ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') OR (l_audit_log_val = 'MINIMAL') )
3329 then
3330
3331 BEGIN
3332
3333 select reschedule_time, distribution_status_id, priority_id
3334 into l_reschedule_time, l_distribution_status_id, l_priority_id
3335 from ieu_uwqm_items
3336 where workitem_pk_id = l_workitem_pk_id
3337 and workitem_obj_code = l_workitem_obj_code;
3338
3339 EXCEPTION
3340 when others then
3341 null;
3342 END;
3343
3344 l_distribution_status_id := 1;
3345 l_msg_data:= x_msg_data;
3346
3347 IEU_UWQM_AUDIT_LOG_PKG.UPDATE_ROW
3348 (
3349 P_AUDIT_LOG_ID => l_audit_log_id_list(k),
3350 P_ACTION_KEY => l_action_key,
3351 P_EVENT_KEY => l_event_key,
3352 P_MODULE => l_module,
3353 P_WS_CODE => l_ws_code,
3354 P_APPLICATION_ID => l_application_id,
3355 P_WORKITEM_PK_ID => l_workitem_pk_id,
3356 P_WORKITEM_OBJ_CODE => l_workitem_obj_code,
3357 P_WORK_ITEM_STATUS_PREV => l_status_id,
3361 P_OWNER_TYPE_PREV => l_owner_type,
3358 P_WORK_ITEM_STATUS_CURR => l_status_id,
3359 P_OWNER_ID_PREV => l_owner_id,
3360 P_OWNER_ID_CURR => l_owner_id,
3362 P_OWNER_TYPE_CURR => l_owner_type,
3363 P_ASSIGNEE_ID_PREV => null,
3364 P_ASSIGNEE_ID_CURR => l_assignee_id,
3365 P_ASSIGNEE_TYPE_PREV => null,
3366 P_ASSIGNEE_TYPE_CURR => l_assignee_type,
3367 P_SOURCE_OBJECT_ID_PREV => l_source_object_id,
3368 P_SOURCE_OBJECT_ID_CURR => l_source_object_id,
3369 P_SOURCE_OBJECT_TYPE_CODE_PREV => l_source_object_type_code,
3370 P_SOURCE_OBJECT_TYPE_CODE_CURR => l_source_object_type_code,
3371 P_PARENT_WORKITEM_STATUS_PREV => null,
3372 P_PARENT_WORKITEM_STATUS_CURR => null,
3373 P_PARENT_DIST_STATUS_PREV => null,
3374 P_PARENT_DIST_STATUS_CURR => null,
3375 P_WORKITEM_DIST_STATUS_PREV => l_distribution_status_id,
3376 P_WORKITEM_DIST_STATUS_CURR => l_distribution_status_id,
3377 P_PRIORITY_PREV => l_priority_id,
3378 P_PRIORITY_CURR => l_priority_id,
3379 P_DUE_DATE_PREV => l_due_date,
3380 P_DUE_DATE_CURR => l_due_date,
3381 P_RESCHEDULE_TIME_PREV => l_reschedule_time,
3382 P_RESCHEDULE_TIME_CURR => l_reschedule_time,
3383 P_IEU_COMMENT_CODE1 => l_ieu_comment_code1,
3384 P_IEU_COMMENT_CODE2 => l_ieu_comment_code2,
3385 P_IEU_COMMENT_CODE3 => l_ieu_comment_code3,
3386 P_IEU_COMMENT_CODE4 => l_ieu_comment_code4,
3387 P_IEU_COMMENT_CODE5 => l_ieu_comment_code5,
3388 P_WORKITEM_COMMENT_CODE1 => l_workitem_comment_code1,
3389 P_WORKITEM_COMMENT_CODE2 => l_workitem_comment_code2,
3390 P_WORKITEM_COMMENT_CODE3 => l_workitem_comment_code3,
3391 P_WORKITEM_COMMENT_CODE4 => l_workitem_comment_code4,
3392 P_WORKITEM_COMMENT_CODE5 => l_workitem_comment_code5,
3393 P_STATUS => 'E',
3394 P_ERROR_CODE => l_msg_data);
3395
3396 -- insert into p_temp(msg) values('l_msg_data3: '||x_msg_data);
3397
3398 end if;
3399 end loop;
3400 END;
3401
3402 -- Check the # of items distributed
3403
3404 for j in l_dist_items.first .. l_dist_items.last
3405 loop
3406
3407 l_workitem_pk_id := l_dist_items(j).workitem_pk_id;
3408 l_workitem_obj_code := l_dist_items(j).workitem_obj_code;
3409 l_owner_id := l_dist_items(j).owner_id;
3410 l_owner_type := l_dist_items(j).owner_type;
3411 l_assignee_id := l_dist_items(j).assignee_id;
3412 l_assignee_type := l_dist_items(j).assignee_type;
3413 l_priority_id := l_dist_items(j).priority_id;
3414 l_due_date := l_dist_items(j).due_date;
3415 l_source_object_id := l_dist_items(j).source_object_id;
3416 l_source_object_type_code := l_dist_items(j).source_object_type_code;
3417 if (l_dist_items(j).work_item_status = 'OPEN')
3418 then
3419 l_status_id := 0;
3420 elsif (l_dist_items(j).work_item_status = 'CLOSE')
3421 then
3422 l_status_id := 3;
3423 elsif (l_dist_items(j).work_item_status = 'DELETE')
3424 then
3425 l_status_id := 4;
3426 elsif (l_dist_items(j).work_item_status = 'SLEEP')
3427 then
3428 l_status_id := 5;
3429 end if;
3430
3431
3432 l_action_key := 'DISTRIBUTION';
3433 if ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED'))
3434 then
3435 l_event_key := 'DISTRIBUTE';
3436 else
3437 l_event_key := null;
3438 end if;
3439 l_module := 'IEU_GET_NEXT_WORK_PVT.DISTRIBUTE_WR_ITEMS';
3440 l_application_id := 696;
3441
3442 if (l_audit_log_val = 'DETAILED')
3443 then
3444 l_ieu_comment_code1 := 'NUM_OF_ATTEMPTS '||l_dist_deliver_num_of_attempts;
3445 l_ieu_comment_code2 := 'DISTRIBUTION_FUNC '||cur_rec.DISTRIBUTION_FUNCTION;
3446 end if;
3447
3448 if (l_dist_items(j).DISTRIBUTED = 'TRUE')
3449 then
3450 l_audit_log_sts := 'S';
3451 l_distribution_status_id := 3;
3452 else
3453 l_audit_log_sts := 'E';
3454 l_distribution_status_id := 1;
3455 end if;
3456
3457 if ( (l_audit_log_val = 'FULL') OR (l_audit_log_val = 'DETAILED') OR (l_audit_log_val = 'MINIMAL') )
3458 then
3459
3460 BEGIN
3461
3462 select reschedule_time, priority_id
3463 into l_reschedule_time, l_priority_id
3464 from ieu_uwqm_items
3465 where workitem_pk_id = l_workitem_pk_id
3466 and workitem_obj_code = l_workitem_obj_code;
3467
3468 EXCEPTION
3469 when others then
3470 null;
3471 END;
3472
3473 IEU_UWQM_AUDIT_LOG_PKG.UPDATE_ROW
3474 (
3475 P_AUDIT_LOG_ID => l_audit_log_id_list(j),
3476 P_ACTION_KEY => l_action_key,
3477 P_EVENT_KEY => l_event_key,
3478 P_MODULE => l_module,
3479 P_WS_CODE => l_ws_code,
3480 P_APPLICATION_ID => l_application_id,
3481 P_WORKITEM_PK_ID => l_workitem_pk_id,
3482 P_WORKITEM_OBJ_CODE => l_workitem_obj_code,
3483 P_WORK_ITEM_STATUS_PREV => l_status_id,
3484 P_WORK_ITEM_STATUS_CURR => l_status_id,
3485 P_OWNER_ID_PREV => l_owner_id,
3486 P_OWNER_ID_CURR => l_owner_id,
3487 P_OWNER_TYPE_PREV => l_owner_type,
3488 P_OWNER_TYPE_CURR => l_owner_type,
3489 P_ASSIGNEE_ID_PREV => null,
3490 P_ASSIGNEE_ID_CURR => l_assignee_id,
3491 P_ASSIGNEE_TYPE_PREV => null,
3492 P_ASSIGNEE_TYPE_CURR => l_assignee_type,
3493 P_SOURCE_OBJECT_ID_PREV => l_source_object_id,
3494 P_SOURCE_OBJECT_ID_CURR => l_source_object_id,
3498 P_PARENT_WORKITEM_STATUS_CURR => null,
3495 P_SOURCE_OBJECT_TYPE_CODE_PREV => l_source_object_type_code,
3496 P_SOURCE_OBJECT_TYPE_CODE_CURR => l_source_object_type_code,
3497 P_PARENT_WORKITEM_STATUS_PREV => null,
3499 P_PARENT_DIST_STATUS_PREV => null,
3500 P_PARENT_DIST_STATUS_CURR => null,
3501 P_WORKITEM_DIST_STATUS_PREV => 1,
3502 P_WORKITEM_DIST_STATUS_CURR => l_distribution_status_id,
3503 P_PRIORITY_PREV => l_priority_id,
3504 P_PRIORITY_CURR => l_priority_id,
3505 P_DUE_DATE_PREV => l_due_date,
3506 P_DUE_DATE_CURR => l_due_date,
3507 P_RESCHEDULE_TIME_PREV => l_reschedule_time,
3508 P_RESCHEDULE_TIME_CURR => l_reschedule_time,
3509 P_IEU_COMMENT_CODE1 => l_ieu_comment_code1,
3510 P_IEU_COMMENT_CODE2 => l_ieu_comment_code2,
3511 P_IEU_COMMENT_CODE3 => l_ieu_comment_code3,
3512 P_IEU_COMMENT_CODE4 => l_ieu_comment_code4,
3513 P_IEU_COMMENT_CODE5 => l_ieu_comment_code5,
3514 P_WORKITEM_COMMENT_CODE1 => l_workitem_comment_code1,
3515 P_WORKITEM_COMMENT_CODE2 => l_workitem_comment_code2,
3516 P_WORKITEM_COMMENT_CODE3 => l_workitem_comment_code3,
3517 P_WORKITEM_COMMENT_CODE4 => l_workitem_comment_code4,
3518 P_WORKITEM_COMMENT_CODE5 => l_workitem_comment_code5,
3519 P_STATUS => l_audit_log_sts,
3520 P_ERROR_CODE => l_msg_data
3521 );
3522
3523 end if;
3524
3525
3526 if (l_dist_items(j).DISTRIBUTED = 'TRUE')
3527 then
3528 IF (l_dist_items(j).WORK_ITEM_STATUS is not null)
3529 THEN
3530 IF (l_dist_items(j).WORK_ITEM_STATUS = 'OPEN')
3531 THEN
3532 l_work_item_status_id := 0;
3533 ELSIF (l_dist_items(j).WORK_ITEM_STATUS = 'CLOSE')
3534 THEN
3535 l_work_item_status_id := 3;
3536 ELSIF (l_dist_items(j).WORK_ITEM_STATUS = 'DELETE')
3537 THEN
3538 l_work_item_status_id := 4;
3539 ELSIF (l_dist_items(j).WORK_ITEM_STATUS = 'SLEEP')
3540 THEN
3541 l_work_item_status_id := 5;
3542 END IF;
3543 END IF;
3544
3545 --dbms_output.put_line('dist status set to TRUE work item pkid: '||l_dist_items(j).WORKITEM_PK_ID);
3546
3547 x_num_of_items_distributed := x_num_of_items_distributed + 1;
3548 x_uwqm_workitem_data.extend;
3549 x_uwqm_workitem_data(x_uwqm_workitem_data.last) := SYSTEM.WR_ITEM_DATA_OBJ(l_dist_items(j).WORK_ITEM_ID,
3550 l_dist_items(j).WORKITEM_OBJ_CODE,
3551 l_dist_items(j).WORKITEM_PK_ID,
3552 l_work_item_status_id,
3553 l_dist_items(j).PRIORITY_ID,
3554 l_dist_items(j).PRIORITY_LEVEL,
3555 l_dist_items(j).PRIORITY_CODE,
3556 l_dist_items(j).DUE_DATE,
3557 l_dist_items(j).TITLE,
3558 l_dist_items(j).PARTY_ID,
3559 l_dist_items(j).OWNER_ID,
3560 l_dist_items(j).OWNER_TYPE,
3561 l_dist_items(j).ASSIGNEE_ID,
3562 l_dist_items(j).ASSIGNEE_TYPE,
3563 l_dist_items(j).SOURCE_OBJECT_ID,
3564 l_dist_items(j).SOURCE_OBJECT_TYPE_CODE,
3565 l_dist_items(j).APPLICATION_ID,
3566 l_dist_items(j).IEU_ENUM_TYPE_UUID,
3567 l_dist_items(j).WORK_ITEM_NUMBER,
3568 l_dist_items(j).RESCHEDULE_TIME,
3569 l_dist_items(j).WORK_SOURCE,
3570 l_dist_items(j).DISTRIBUTED,
3571 l_dist_items(j).ITEM_INCLUDED_BY_APP);
3572 elsif (l_dist_items(j).DISTRIBUTED = 'FALSE')
3573 then
3574 -- set the distribution_status_id back to 'Distributable'
3575 --dbms_output.put_line('dist status set to FALSE work item pkid: '||l_dist_items(j).WORKITEM_PK_ID);
3576 update ieu_uwqm_items
3577 set distribution_status_id = 1
3578 where work_item_id = l_dist_items(j).work_item_id;
3579 commit;
3580
3581 end if;
3582 end loop;
3583 --dbms_output.put_line('Num of Items Dist: '||x_num_of_items_distributed||' l_dist_item_obj cnt: '||x_uwqm_workitem_data.count);
3584
3585 end if; /* l_dist_items.count > 1 */
3586
3587 end loop; /* cur_res in c_ws */
3588
3589 end loop; /* l_nw_items_list */
3590
3591 end if; /* x_num_of_items_distributed <> -1 */
3592 if l_nw_items_list.count > 0 then
3593 for y in l_nw_items_list.first..l_nw_items_list.last
3594 loop
3595 update ieu_uwqm_items
3596 set distribution_status_id = 1
3597 where work_item_id = l_nw_items_list(y).work_item_id
3598 and distribution_status_id = 2;
3599 commit;
3600 end loop;
3601 end if;
3602 -- commit;
3603 -- dbms_output.put_line('cnt: '||l_nw_item_list.count);
3604 EXCEPTION
3605
3606 WHEN fnd_api.g_exc_error THEN
3607
3608 x_return_status := fnd_api.g_ret_sts_error;
3609
3610 fnd_msg_pub.Count_and_Get
3611 (
3612 p_count => x_msg_count,
3613 p_data => x_msg_data
3614 );
3615
3619
3616 WHEN fnd_api.g_exc_unexpected_error THEN
3617
3618 x_return_status := fnd_api.g_ret_sts_unexp_error;
3620 fnd_msg_pub.Count_and_Get
3621 (
3622 p_count => x_msg_count,
3623 p_data => x_msg_data
3624 );
3625
3626 WHEN OTHERS THEN
3627
3628 x_return_status := fnd_api.g_ret_sts_unexp_error;
3629
3630 IF FND_MSG_PUB.Check_msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3631 THEN
3632
3633 fnd_msg_pub.Count_and_Get
3634 (
3635 p_count => x_msg_count,
3636 p_data => x_msg_data
3637 );
3638
3639 END IF;
3640
3641 END GET_DIST_WR_ITEMS;
3642
3643 /**
3644 ** Called by PROCEDURE - DISTRIBUTE_AND_DELIVER_WR_ITEM, DISTRIBUTE_WORK_ITEMS
3645 ** The in var can be either a rec of type IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WR_ITEM_DATA_REC OR
3646 ** table of objects SYSTEM.WR_ITEM_DATA_NST
3647 ** The In var - p_var_in_type_code indicates if its a record - 'REC' or an object - 'OBJ'
3648 ** 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
3649 ** to table of records of type - IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WR_ITEM_ACT_DATA_LIST
3650 **/
3651
3652 PROCEDURE SET_WR_ITEM_DATA_REC( p_var_in_type_code IN VARCHAR2,
3653 p_dist_workitem_data IN SYSTEM.WR_ITEM_DATA_NST,
3654 p_dist_del_workitem_data IN IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WR_ITEM_DATA_REC,
3655 x_ctr IN OUT NOCOPY NUMBER,
3656 x_uwqm_workitem_data IN OUT NOCOPY IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WR_ITEM_ACT_DATA_LIST) IS
3657
3658 l_object_function VARCHAR2(40);
3659 l_object_parameters VARCHAR2(500);
3660 l_enter_from_task VARCHAR2(10);
3661 l_ws_id NUMBER;
3662 l_not_valid_flag VARCHAR2(1);
3663 BEGIN
3664
3665 if (p_var_in_type_code = 'OBJ')
3666 then
3667
3668 -- If a work item was distributed, copy the Work Item data from table of obj - l_dist_workitem_data
3669 -- to table of Rec - x_uwqm_workitem_data
3670
3671 for n in 1 .. p_dist_workitem_data .count
3672 loop
3673
3674 -- Changes reqd for object function and params
3675 -- Get the Object func and params based from JTF_OBJECTS
3676 IF (p_dist_workitem_data(n).SOURCE_OBJECT_TYPE_CODE is not null)
3677 THEN
3678
3679
3680 BEGIN
3681 SELECT enter_from_task, object_function, object_parameters
3682 INTO l_enter_from_task, l_object_function, l_object_parameters
3683 FROM JTF_OBJECTS_B
3684 WHERE OBJECT_CODE = p_dist_workitem_data(n).SOURCE_OBJECT_TYPE_CODE;
3685 EXCEPTION
3686 when no_data_found then
3687 null;
3688 END;
3689
3690 x_uwqm_workitem_data(x_ctr).IEU_OBJECT_FUNCTION := l_object_function;
3691 x_uwqm_workitem_data(x_ctr).IEU_OBJECT_PARAMETERS := l_object_parameters;
3692 x_uwqm_workitem_data(x_ctr).IEU_PARAM_PK_VALUE := p_dist_workitem_data(n).SOURCE_OBJECT_ID;
3693 x_uwqm_workitem_data(x_ctr).IEU_PARAM_PK_COL := 'SOURCE_OBJECT_ID';
3694
3695 ELSIF (p_dist_workitem_data(n).SOURCE_OBJECT_TYPE_CODE is null)
3696 THEN
3697
3698 BEGIN
3699 SELECT enter_from_task, object_function, object_parameters
3700 INTO l_enter_from_task, l_object_function, l_object_parameters
3701 FROM JTF_OBJECTS_B
3702 WHERE OBJECT_CODE = p_dist_workitem_data(n).WORKITEM_OBJ_CODE;
3703 EXCEPTION
3704 when no_data_found then
3705 null;
3706 END;
3707
3708 x_uwqm_workitem_data(x_ctr).IEU_OBJECT_FUNCTION := l_object_function;
3709 x_uwqm_workitem_data(x_ctr).IEU_OBJECT_PARAMETERS := l_object_parameters;
3710 x_uwqm_workitem_data(x_ctr).IEU_PARAM_PK_VALUE := p_dist_workitem_data(n).WORKITEM_PK_ID;
3711 x_uwqm_workitem_data(x_ctr).IEU_PARAM_PK_COL := 'WORKITEM_PK_ID';
3712
3713
3714 END IF; /* SOURCE_OBJECT_TYPE_CODE is not null */
3715
3716 BEGIN
3717 l_not_valid_flag := 'N';
3718 SELECT ws_id
3719 INTO l_ws_id
3720 FROM ieu_uwqm_work_sources_b
3721 WHERE ws_code = p_dist_workitem_data(n).WORK_SOURCE
3722 -- AND nvl(not_valid_flag,'N') = 'N';
3723 AND nvl(not_valid_flag,'N') = l_not_valid_flag;
3724
3725 EXCEPTION
3726 WHEN OTHERS THEN
3727 l_ws_id := null;
3728 END;
3729
3730 x_uwqm_workitem_data(x_ctr).IEU_MEDIA_TYPE_UUID := '';
3731 x_uwqm_workitem_data(x_ctr).WORK_ITEM_ID := p_dist_workitem_data(n).WORK_ITEM_ID;
3732 x_uwqm_workitem_data(x_ctr).WORKITEM_OBJ_CODE := p_dist_workitem_data(n).WORKITEM_OBJ_CODE;
3733 x_uwqm_workitem_data(x_ctr).WORKITEM_PK_ID := p_dist_workitem_data(n).WORKITEM_PK_ID;
3734 x_uwqm_workitem_data(x_ctr).STATUS_ID := p_dist_workitem_data(n).WORK_ITEM_STATUS;
3735 x_uwqm_workitem_data(x_ctr).PRIORITY_ID := p_dist_workitem_data(n).PRIORITY_ID;
3736 x_uwqm_workitem_data(x_ctr).PRIORITY_LEVEL := p_dist_workitem_data(n).PRIORITY_LEVEL;
3737 x_uwqm_workitem_data(x_ctr).DUE_DATE := p_dist_workitem_data(n).DUE_DATE;
3738 x_uwqm_workitem_data(x_ctr).TITLE := p_dist_workitem_data(n).TITLE;
3742 x_uwqm_workitem_data(x_ctr).ASSIGNEE_ID := p_dist_workitem_data(n).ASSIGNEE_ID;
3739 x_uwqm_workitem_data(x_ctr).PARTY_ID := p_dist_workitem_data(n).PARTY_ID;
3740 x_uwqm_workitem_data(x_ctr).OWNER_ID := p_dist_workitem_data(n).OWNER_ID;
3741 x_uwqm_workitem_data(x_ctr).OWNER_TYPE := p_dist_workitem_data(n).OWNER_TYPE;
3743 x_uwqm_workitem_data(x_ctr).ASSIGNEE_TYPE := p_dist_workitem_data(n).ASSIGNEE_TYPE;
3744 x_uwqm_workitem_data(x_ctr).SOURCE_OBJECT_ID := p_dist_workitem_data(n).SOURCE_OBJECT_ID;
3745 x_uwqm_workitem_data(x_ctr).SOURCE_OBJECT_TYPE_CODE := p_dist_workitem_data(n).SOURCE_OBJECT_TYPE_CODE;
3746 x_uwqm_workitem_data(x_ctr).APPLICATION_ID := p_dist_workitem_data(n).APPLICATION_ID;
3747 x_uwqm_workitem_data(x_ctr).IEU_ENUM_TYPE_UUID := p_dist_workitem_data(n).IEU_ENUM_TYPE_UUID;
3748 x_uwqm_workitem_data(x_ctr).WORK_ITEM_NUMBER := p_dist_workitem_data(n).WORK_ITEM_NUMBER;
3749 x_uwqm_workitem_data(x_ctr).RESCHEDULE_TIME := p_dist_workitem_data(n).RESCHEDULE_TIME;
3750 x_uwqm_workitem_data(x_ctr).IEU_GET_NEXTWORK_FLAG := 'Y';
3751 x_uwqm_workitem_data(x_ctr).IEU_ACTION_OBJECT_CODE := p_dist_workitem_data(n).SOURCE_OBJECT_TYPE_CODE;
3752 x_uwqm_workitem_data(x_ctr).WS_ID := l_ws_id;
3753 x_ctr := x_ctr + 1;
3754
3755 end loop;/* p_dist_workitem_data */
3756
3757 elsif (p_var_in_type_code = 'REC')
3758 then
3759
3760 -- If a work item was distributed, copy the Work Item data from table of obj - l_dist_workitem_data
3761 -- to table of Rec - x_uwqm_workitem_data
3762
3763 -- Changes reqd for object function and params
3764 -- Get the Object func and params based from JTF_OBJECTS
3765
3766 IF (p_dist_del_workitem_data.SOURCE_OBJECT_TYPE_CODE is not null)
3767 THEN
3768
3769 BEGIN
3770 SELECT enter_from_task, object_function, object_parameters
3771 INTO l_enter_from_task, l_object_function, l_object_parameters
3772 FROM JTF_OBJECTS_B
3773 WHERE OBJECT_CODE = p_dist_del_workitem_data.SOURCE_OBJECT_TYPE_CODE;
3774 EXCEPTION
3775 when no_data_found then
3776 null;
3777 END;
3778
3779 x_uwqm_workitem_data(x_ctr).IEU_OBJECT_FUNCTION := l_object_function;
3780 x_uwqm_workitem_data(x_ctr).IEU_OBJECT_PARAMETERS := l_object_parameters;
3781 x_uwqm_workitem_data(x_ctr).IEU_PARAM_PK_VALUE := p_dist_del_workitem_data.SOURCE_OBJECT_ID;
3782 x_uwqm_workitem_data(x_ctr).IEU_PARAM_PK_COL := 'SOURCE_OBJECT_ID';
3783
3784
3785 ELSIF (p_dist_del_workitem_data.SOURCE_OBJECT_TYPE_CODE is null)
3786 THEN
3787
3788 BEGIN
3789 SELECT enter_from_task, object_function, object_parameters
3790 INTO l_enter_from_task, l_object_function, l_object_parameters
3791 FROM JTF_OBJECTS_B
3792 WHERE OBJECT_CODE = p_dist_del_workitem_data.WORKITEM_OBJ_CODE;
3793 EXCEPTION
3794 when no_data_found then
3795 null;
3796 END;
3797
3798 x_uwqm_workitem_data(x_ctr).IEU_OBJECT_FUNCTION := l_object_function;
3799 x_uwqm_workitem_data(x_ctr).IEU_OBJECT_PARAMETERS := l_object_parameters;
3800 x_uwqm_workitem_data(x_ctr).IEU_PARAM_PK_VALUE := p_dist_del_workitem_data.WORKITEM_PK_ID;
3801 x_uwqm_workitem_data(x_ctr).IEU_PARAM_PK_COL := 'WORKITEM_PK_ID';
3802
3803
3804 END IF; /* SOURCE_OBJECT_TYPE_CODE is not null */
3805
3806 x_uwqm_workitem_data(x_ctr).IEU_MEDIA_TYPE_UUID := '';
3807 x_uwqm_workitem_data(x_ctr).WORK_ITEM_ID := p_dist_del_workitem_data.WORK_ITEM_ID;
3808 x_uwqm_workitem_data(x_ctr).WORKITEM_OBJ_CODE := p_dist_del_workitem_data.WORKITEM_OBJ_CODE;
3809 x_uwqm_workitem_data(x_ctr).WORKITEM_PK_ID := p_dist_del_workitem_data.WORKITEM_PK_ID;
3810 x_uwqm_workitem_data(x_ctr).STATUS_ID := p_dist_del_workitem_data.STATUS_ID;
3811 x_uwqm_workitem_data(x_ctr).PRIORITY_ID := p_dist_del_workitem_data.PRIORITY_ID;
3812 x_uwqm_workitem_data(x_ctr).PRIORITY_LEVEL := p_dist_del_workitem_data.PRIORITY_LEVEL;
3813 x_uwqm_workitem_data(x_ctr).DUE_DATE := p_dist_del_workitem_data.DUE_DATE;
3814 x_uwqm_workitem_data(x_ctr).TITLE := p_dist_del_workitem_data.TITLE;
3815 x_uwqm_workitem_data(x_ctr).PARTY_ID := p_dist_del_workitem_data.PARTY_ID;
3816 x_uwqm_workitem_data(x_ctr).OWNER_ID := p_dist_del_workitem_data.OWNER_ID;
3817 x_uwqm_workitem_data(x_ctr).OWNER_TYPE := p_dist_del_workitem_data.OWNER_TYPE;
3818 x_uwqm_workitem_data(x_ctr).ASSIGNEE_ID := p_dist_del_workitem_data.ASSIGNEE_ID;
3819 x_uwqm_workitem_data(x_ctr).ASSIGNEE_TYPE := p_dist_del_workitem_data.ASSIGNEE_TYPE;
3820 x_uwqm_workitem_data(x_ctr).SOURCE_OBJECT_ID := p_dist_del_workitem_data.SOURCE_OBJECT_ID;
3821 x_uwqm_workitem_data(x_ctr).SOURCE_OBJECT_TYPE_CODE := p_dist_del_workitem_data.SOURCE_OBJECT_TYPE_CODE;
3822 x_uwqm_workitem_data(x_ctr).APPLICATION_ID := p_dist_del_workitem_data.APPLICATION_ID;
3823 x_uwqm_workitem_data(x_ctr).IEU_ENUM_TYPE_UUID := p_dist_del_workitem_data.IEU_ENUM_TYPE_UUID;
3824 x_uwqm_workitem_data(x_ctr).WORK_ITEM_NUMBER := p_dist_del_workitem_data.WORK_ITEM_NUMBER;
3825 x_uwqm_workitem_data(x_ctr).RESCHEDULE_TIME := p_dist_del_workitem_data.RESCHEDULE_TIME;
3826 x_uwqm_workitem_data(x_ctr).IEU_GET_NEXTWORK_FLAG := 'Y';
3827 x_uwqm_workitem_data(x_ctr).IEU_ACTION_OBJECT_CODE := p_dist_del_workitem_data.SOURCE_OBJECT_TYPE_CODE;
3831 end if; /* p_var_in_type_code */
3828 x_uwqm_workitem_data(x_ctr).WS_ID := p_dist_del_workitem_data.WS_ID;
3829 x_ctr := x_ctr + 1;
3830
3832
3833 END SET_WR_ITEM_DATA_REC;
3834
3835
3836 /**
3837 ** Distribute Only returns the table of Records in a different format compared to Distribute and Deliver.
3838 ** This was required as Distribute Only can return multiple records. Distribute and Deliver requires the Return Record
3839 ** to be of type IEU_FRM_PVT.T_IEU_MEDIA_DATA for processing on the FORM.
3840 ** Called by PROCEDURE - DISTRIBUTE_AND_DELIVER_WR_ITEM
3841 ** The in var can be either a rec of type IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WR_ITEM_DATA_REC OR
3842 ** table of objects SYSTEM.WR_ITEM_DATA_NST
3843 ** The In var - p_var_in_type_code indicates if its a record - 'REC' or an object - 'OBJ'
3844 ** 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
3845 ** to table of records of type - IEU_FRM_PVT.T_IEU_MEDIA_DATA
3846 **/
3847
3848 PROCEDURE SET_DIST_AND_DEL_ITEM_DATA_REC( p_var_in_type_code IN VARCHAR2,
3849 p_dist_workitem_data IN SYSTEM.WR_ITEM_DATA_NST,
3850 p_dist_del_workitem_data IN IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WR_ITEM_DATA_REC,
3851 x_ctr IN OUT NOCOPY NUMBER,
3852 x_workitem_action_data IN OUT NOCOPY IEU_FRM_PVT.T_IEU_MEDIA_DATA) IS
3853
3854
3855 --l_ctr NUMBER := 0;
3856 l_enter_from_task VARCHAR2(1);
3857 l_object_function VARCHAR2(30);
3858 l_object_parameters VARCHAR2(2000);
3859 l_work_type VARCHAR2(80);
3860
3861 BEGIN
3862
3863 if (p_var_in_type_code = 'OBJ')
3864 then
3865
3866 -- If a work item was distributed, copy the Work Item data from table of obj - l_dist_workitem_data
3867 -- to table of Rec - x_uwqm_workitem_data
3868
3869 -- Changes reqd for object function and params
3870 -- Get the Object func and params based from JTF_OBJECTS
3871
3872 for n in 1 .. p_dist_workitem_data.count
3873 loop
3874
3875 IF ( p_dist_workitem_data(n).SOURCE_OBJECT_TYPE_CODE is not 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).SOURCE_OBJECT_TYPE_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).source_object_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 := 'SOURCE_OBJECT_ID';
3905 x_workitem_action_data(x_ctr).param_type := '';
3906 x_ctr := x_ctr + 1;
3907
3908
3909 ELSIF (p_dist_workitem_data(n).SOURCE_OBJECT_TYPE_CODE is null)
3910 THEN
3911
3912 BEGIN
3913 SELECT enter_from_task, object_function, object_parameters
3914 INTO l_enter_from_task, l_object_function, l_object_parameters
3915 FROM JTF_OBJECTS_B
3916 WHERE OBJECT_CODE = p_dist_workitem_data(n).WORKITEM_OBJ_CODE;
3917 EXCEPTION
3918 when no_data_found then
3919 null;
3920 END;
3921
3922 x_workitem_action_data(x_ctr).param_name := 'IEU_OBJECT_FUNCTION';
3923 x_workitem_action_data(x_ctr).param_value := l_object_function;
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 := 'IEU_OBJECT_PARAMETERS';
3928 x_workitem_action_data(x_ctr).param_value := l_object_parameters;
3929 x_workitem_action_data(x_ctr).param_type := 'CHAR';
3930 x_ctr := x_ctr + 1;
3931
3932 x_workitem_action_data(x_ctr).param_name := 'IEU_PARAM_PK_VALUE';
3933 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).workitem_pk_id;
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 := 'IEU_PARAM_PK_COL';
3938 x_workitem_action_data(x_ctr).param_value := 'WORKITEM_PK_ID';
3939 x_workitem_action_data(x_ctr).param_type := '';
3940 x_ctr := x_ctr + 1;
3941
3942
3943 END IF; /* SOURCE_OBJECT_TYPE_CODE is not null */
3944
3945
3946 x_workitem_action_data(x_ctr).param_name := 'IEU_MEDIA_TYPE_UUID';
3947 x_workitem_action_data(x_ctr).param_value := '';
3948 x_workitem_action_data(x_ctr).param_type := '';
3949 x_ctr := x_ctr + 1;
3953 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
3950
3951 x_workitem_action_data(x_ctr).param_name := 'WORK_ITEM_ID';
3952 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).WORK_ITEM_ID;
3954 x_ctr := x_ctr + 1;
3955
3956 x_workitem_action_data(x_ctr).param_name := 'WORKITEM_OBJ_CODE';
3957 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).WORKITEM_OBJ_CODE;
3958 x_workitem_action_data(x_ctr).param_type := 'CHAR';
3959 x_ctr := x_ctr + 1;
3960
3961 x_workitem_action_data(x_ctr).param_name := 'WORKITEM_PK_ID';
3962 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).WORKITEM_PK_ID;
3963 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
3964 x_ctr := x_ctr + 1;
3965
3966 x_workitem_action_data(x_ctr).param_name := 'STATUS_ID';
3967 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).WORK_ITEM_STATUS;
3968 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
3969 x_ctr := x_ctr + 1;
3970
3971 x_workitem_action_data(x_ctr).param_name := 'PRIORITY_ID';
3972 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).PRIORITY_ID;
3973 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
3974 x_ctr := x_ctr + 1;
3975
3976 x_workitem_action_data(x_ctr).param_name := 'PRIORITY_LEVEL';
3977 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).PRIORITY_LEVEL;
3978 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
3979 x_ctr := x_ctr + 1;
3980
3981 x_workitem_action_data(x_ctr).param_name := 'DUE_DATE';
3982 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).DUE_DATE;
3983 x_workitem_action_data(x_ctr).param_type := 'DATE';
3984 x_ctr := x_ctr + 1;
3985
3986 x_workitem_action_data(x_ctr).param_name := 'TITLE';
3987 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).TITLE;
3988 x_workitem_action_data(x_ctr).param_type := 'CHAR';
3989 x_ctr := x_ctr + 1;
3990
3991 x_workitem_action_data(x_ctr).param_name := 'PARTY_ID';
3992 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).PARTY_ID;
3993 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
3994 x_ctr := x_ctr + 1;
3995
3996 x_workitem_action_data(x_ctr).param_name := 'OWNER_TYPE';
3997 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).OWNER_TYPE;
3998 x_workitem_action_data(x_ctr).param_type := 'CHAR';
3999 x_ctr := x_ctr + 1;
4000
4001 x_workitem_action_data(x_ctr).param_name := 'OWNER_ID';
4002 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).OWNER_ID;
4003 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
4004 x_ctr := x_ctr + 1;
4005
4006 x_workitem_action_data(x_ctr).param_name := 'ASSIGNEE_TYPE';
4007 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).ASSIGNEE_TYPE;
4008 x_workitem_action_data(x_ctr).param_type := 'CHAR';
4009 x_ctr := x_ctr + 1;
4010
4011 x_workitem_action_data(x_ctr).param_name := 'ASSIGNEE_ID';
4012 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).ASSIGNEE_ID;
4013 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
4014 x_ctr := x_ctr + 1;
4015 /*
4016 x_workitem_action_data(x_ctr).param_name := 'OWNER_TYPE_ACTUAL';
4017 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).OWNER_TYPE_ACTUAL;
4018 x_workitem_action_data(x_ctr).param_type := 'CHAR';
4019 x_ctr := x_ctr + 1;
4020
4021 x_workitem_action_data(x_ctr).param_name := 'ASSIGNEE_TYPE_ACTUAL';
4022 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).ASSIGNEE_TYPE_ACTUAL;
4023 x_workitem_action_data(x_ctr).param_type := 'CHAR';
4024 x_ctr := x_ctr + 1;
4025 */
4026 x_workitem_action_data(x_ctr).param_name := 'SOURCE_OBJECT_ID';
4027 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).SOURCE_OBJECT_ID;
4028 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
4029 x_ctr := x_ctr + 1;
4030
4031 x_workitem_action_data(x_ctr).param_name := 'SOURCE_OBJECT_TYPE_CODE';
4032 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).SOURCE_OBJECT_TYPE_CODE;
4033 x_workitem_action_data(x_ctr).param_type := 'CHAR';
4034 x_ctr := x_ctr + 1;
4035
4036 x_workitem_action_data(x_ctr).param_name := 'APPLICATION_ID';
4037 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).APPLICATION_ID;
4038 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
4039 x_ctr := x_ctr + 1;
4040
4041 x_workitem_action_data(x_ctr).param_name := 'IEU_ACTION_OBJECT_CODE';
4042 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).SOURCE_OBJECT_TYPE_CODE;
4043 x_workitem_action_data(x_ctr).param_type := 'CHAR';
4044 x_ctr := x_ctr + 1;
4045
4046 x_workitem_action_data(x_ctr).param_name := 'IEU_GET_NEXTWORK_FLAG';
4047 x_workitem_action_data(x_ctr).param_value := 'Y';
4048 x_workitem_action_data(x_ctr).param_type := 'CHAR';
4049 x_ctr := x_ctr + 1;
4050
4051 x_workitem_action_data(x_ctr).param_name := 'RESCHEDULE_TIME';
4052 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).RESCHEDULE_TIME;
4053 x_workitem_action_data(x_ctr).param_type := 'DATE';
4054 x_ctr := x_ctr + 1;
4055
4056 x_workitem_action_data(x_ctr).param_name := 'IEU_ENUM_TYPE_UUID';
4057 x_workitem_action_data(x_ctr).param_value := p_dist_workitem_data(n).IEU_ENUM_TYPE_UUID;
4058 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
4059 x_ctr := x_ctr + 1;
4060
4064 INTO L_WORK_TYPE
4061 BEGIN
4062
4063 SELECT LKUPS.MEANING
4065 FROM FND_LOOKUP_VALUES_VL LKUPS, IEU_UWQ_SEL_ENUMERATORS ENUM
4066 WHERE ENUM.ENUM_TYPE_UUID = p_dist_workitem_data(n).IEU_ENUM_TYPE_UUID
4067 AND LKUPS.LOOKUP_TYPE(+) = ENUM.WORK_Q_LABEL_LU_TYPE
4068 AND LKUPS.VIEW_APPLICATION_ID(+) = ENUM.APPLICATION_ID
4069 AND LKUPS.LOOKUP_CODE(+) = WORK_Q_LABEL_LU_CODE;
4070
4071 EXCEPTION
4072 WHEN NO_DATA_FOUND THEN
4073 NULL;
4074 END;
4075
4076 x_workitem_action_data(x_ctr).param_name := 'WORK_TYPE';
4077 x_workitem_action_data(x_ctr).param_value := L_WORK_TYPE;
4078 x_workitem_action_data(x_ctr).param_type := 'VARCHAR2';
4079 x_ctr := x_ctr + 1;
4080
4081 end loop;/* p_dist_workitem_data */
4082
4083 elsif (p_var_in_type_code = 'REC')
4084 then
4085
4086 -- If a work item was distributed, copy the Work Item data from table of obj - l_dist_workitem_data
4087 -- to table of Rec - x_uwqm_workitem_data
4088
4089 -- Changes reqd for object function and params
4090 -- Get the Object func and params based from JTF_OBJECTS
4091
4092 IF (p_dist_del_workitem_data.SOURCE_OBJECT_TYPE_CODE is not null)
4093 THEN
4094
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.SOURCE_OBJECT_TYPE_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.source_object_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 := 'SOURCE_OBJECT_ID';
4123 x_workitem_action_data(x_ctr).param_type := '';
4124 x_ctr := x_ctr + 1;
4125
4126
4127 ELSIF (p_dist_del_workitem_data.SOURCE_OBJECT_TYPE_CODE is null)
4128 THEN
4129
4130 BEGIN
4131 SELECT enter_from_task, object_function, object_parameters
4132 INTO l_enter_from_task, l_object_function, l_object_parameters
4133 FROM JTF_OBJECTS_B
4134 WHERE OBJECT_CODE = p_dist_del_workitem_data.WORKITEM_OBJ_CODE;
4135 EXCEPTION
4136 when no_data_found then
4137 null;
4138 END;
4139
4140 x_workitem_action_data(x_ctr).param_name := 'IEU_OBJECT_FUNCTION';
4141 x_workitem_action_data(x_ctr).param_value := l_object_function;
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 := 'IEU_OBJECT_PARAMETERS';
4146 x_workitem_action_data(x_ctr).param_value := l_object_parameters;
4147 x_workitem_action_data(x_ctr).param_type := 'CHAR';
4148 x_ctr := x_ctr + 1;
4149
4150 x_workitem_action_data(x_ctr).param_name := 'IEU_PARAM_PK_VALUE';
4151 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.workitem_pk_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 := 'IEU_PARAM_PK_COL';
4156 x_workitem_action_data(x_ctr).param_value := 'WORKITEM_PK_ID';
4157 x_workitem_action_data(x_ctr).param_type := '';
4158 x_ctr := x_ctr + 1;
4159
4160
4161 END IF; /* SOURCE_OBJECT_TYPE_CODE is not null */
4162
4163
4164 x_workitem_action_data(x_ctr).param_name := 'IEU_MEDIA_TYPE_UUID';
4165 x_workitem_action_data(x_ctr).param_value := '';
4166 x_workitem_action_data(x_ctr).param_type := '';
4167 x_ctr := x_ctr + 1;
4168
4169 x_workitem_action_data(x_ctr).param_name := 'WORK_ITEM_ID';
4170 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.WORK_ITEM_ID;
4171 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
4172 x_ctr := x_ctr + 1;
4173
4174 x_workitem_action_data(x_ctr).param_name := 'WORKITEM_OBJ_CODE';
4175 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.WORKITEM_OBJ_CODE;
4176 x_workitem_action_data(x_ctr).param_type := 'CHAR';
4177 x_ctr := x_ctr + 1;
4178
4179 x_workitem_action_data(x_ctr).param_name := 'WORKITEM_PK_ID';
4180 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.WORKITEM_PK_ID;
4181 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
4182 x_ctr := x_ctr + 1;
4183
4184 x_workitem_action_data(x_ctr).param_name := 'STATUS_ID';
4188
4185 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.STATUS_ID;
4186 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
4187 x_ctr := x_ctr + 1;
4189 x_workitem_action_data(x_ctr).param_name := 'PRIORITY_ID';
4190 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.PRIORITY_ID;
4191 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
4192 x_ctr := x_ctr + 1;
4193
4194 x_workitem_action_data(x_ctr).param_name := 'PRIORITY_LEVEL';
4195 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.PRIORITY_LEVEL;
4196 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
4197 x_ctr := x_ctr + 1;
4198
4199 x_workitem_action_data(x_ctr).param_name := 'DUE_DATE';
4200 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.DUE_DATE;
4201 x_workitem_action_data(x_ctr).param_type := 'DATE';
4202 x_ctr := x_ctr + 1;
4203
4204 x_workitem_action_data(x_ctr).param_name := 'TITLE';
4205 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.TITLE;
4206 x_workitem_action_data(x_ctr).param_type := 'CHAR';
4207 x_ctr := x_ctr + 1;
4208
4209 x_workitem_action_data(x_ctr).param_name := 'PARTY_ID';
4210 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.PARTY_ID;
4211 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
4212 x_ctr := x_ctr + 1;
4213
4214 x_workitem_action_data(x_ctr).param_name := 'OWNER_TYPE';
4215 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.OWNER_TYPE;
4216 x_workitem_action_data(x_ctr).param_type := 'CHAR';
4217 x_ctr := x_ctr + 1;
4218
4219 x_workitem_action_data(x_ctr).param_name := 'OWNER_ID';
4220 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.OWNER_ID;
4221 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
4222 x_ctr := x_ctr + 1;
4223
4224 x_workitem_action_data(x_ctr).param_name := 'ASSIGNEE_TYPE';
4225 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.ASSIGNEE_TYPE;
4226 x_workitem_action_data(x_ctr).param_type := 'CHAR';
4227 x_ctr := x_ctr + 1;
4228
4229 x_workitem_action_data(x_ctr).param_name := 'ASSIGNEE_ID';
4230 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.ASSIGNEE_ID;
4231 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
4232 x_ctr := x_ctr + 1;
4233 /*
4234 x_workitem_action_data(x_ctr).param_name := 'OWNER_TYPE_ACTUAL';
4235 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.OWNER_TYPE_ACTUAL;
4236 x_workitem_action_data(x_ctr).param_type := 'CHAR';
4237 x_ctr := x_ctr + 1;
4238
4239 x_workitem_action_data(x_ctr).param_name := 'ASSIGNEE_TYPE_ACTUAL';
4240 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.ASSIGNEE_TYPE_ACTUAL;
4241 x_workitem_action_data(x_ctr).param_type := 'CHAR';
4242 x_ctr := x_ctr + 1;
4243 */
4244 x_workitem_action_data(x_ctr).param_name := 'SOURCE_OBJECT_ID';
4245 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.SOURCE_OBJECT_ID;
4246 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
4247 x_ctr := x_ctr + 1;
4248
4249 x_workitem_action_data(x_ctr).param_name := 'SOURCE_OBJECT_TYPE_CODE';
4250 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.SOURCE_OBJECT_TYPE_CODE;
4251 x_workitem_action_data(x_ctr).param_type := 'CHAR';
4252 x_ctr := x_ctr + 1;
4253
4254 x_workitem_action_data(x_ctr).param_name := 'APPLICATION_ID';
4255 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.APPLICATION_ID;
4256 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
4257 x_ctr := x_ctr + 1;
4258
4259 x_workitem_action_data(x_ctr).param_name := 'IEU_ACTION_OBJECT_CODE';
4260 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.SOURCE_OBJECT_TYPE_CODE;
4261 x_workitem_action_data(x_ctr).param_type := 'CHAR';
4262 x_ctr := x_ctr + 1;
4263
4264 x_workitem_action_data(x_ctr).param_name := 'IEU_GET_NEXTWORK_FLAG';
4265 x_workitem_action_data(x_ctr).param_value := 'Y';
4266 x_workitem_action_data(x_ctr).param_type := 'CHAR';
4267 x_ctr := x_ctr + 1;
4268
4269 x_workitem_action_data(x_ctr).param_name := 'RESCHEDULE_TIME';
4270 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.RESCHEDULE_TIME;
4271 x_workitem_action_data(x_ctr).param_type := 'DATE';
4272 x_ctr := x_ctr + 1;
4273
4274 x_workitem_action_data(x_ctr).param_name := 'IEU_ENUM_TYPE_UUID';
4275 x_workitem_action_data(x_ctr).param_value := p_dist_del_workitem_data.IEU_ENUM_TYPE_UUID;
4276 x_workitem_action_data(x_ctr).param_type := 'NUMBER';
4277 x_ctr := x_ctr + 1;
4278
4279 BEGIN
4280
4281 SELECT LKUPS.MEANING
4282 INTO L_WORK_TYPE
4283 FROM FND_LOOKUP_VALUES_VL LKUPS, IEU_UWQ_SEL_ENUMERATORS ENUM
4284 WHERE ENUM.ENUM_TYPE_UUID = p_dist_del_workitem_data.IEU_ENUM_TYPE_UUID
4285 AND LKUPS.LOOKUP_TYPE(+) = ENUM.WORK_Q_LABEL_LU_TYPE
4286 AND LKUPS.VIEW_APPLICATION_ID(+) = ENUM.APPLICATION_ID
4287 AND LKUPS.LOOKUP_CODE(+) = WORK_Q_LABEL_LU_CODE;
4288
4289 EXCEPTION
4290 WHEN NO_DATA_FOUND THEN
4291 NULL;
4292 END;
4293
4294 x_workitem_action_data(x_ctr).param_name := 'WORK_TYPE';
4295 x_workitem_action_data(x_ctr).param_value := L_WORK_TYPE;
4296 x_workitem_action_data(x_ctr).param_type := 'VARCHAR2';
4297 x_ctr := x_ctr + 1;
4298
4299 end if; /* p_var_in_type_code */
4303 /**
4300
4301 END SET_DIST_AND_DEL_ITEM_DATA_REC;
4302
4304 ** Called by PROCEDURE - GET_NEXT_WORK_FOR_APPS
4305 ** Sets the where clause based on business rules like ws_id, distribute_to and distribute_from
4306 ** This extra where clause will be appened to actual where clause to fetch the set of distributable items
4307 **/
4308 PROCEDURE GET_WS_WHERE_CLAUSE
4309 (p_type IN VARCHAR2,
4310 p_ws_det_list IN IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WS_DETAILS_LIST,
4311 p_resource_id IN NUMBER,
4312 x_dist_from_where OUT NOCOPY VARCHAR2,
4313 x_dist_to_where OUT NOCOPY VARCHAR2,
4314 x_bindvar_from_list OUT NOCOPY IEU_UWQ_BINDVAR_LIST,
4315 x_bindvar_to_list OUT NOCOPY IEU_UWQ_BINDVAR_LIST) IS
4316
4317 /*
4318 cursor C1 is
4319 select WS_B.WS_ID, WS_B.DISTRIBUTE_TO, WS_B.DISTRIBUTE_FROM , WS_B.DISTRIBUTION_FUNCTION
4320 from IEU_UWQM_WORK_SOURCES_B WS_B
4321 where ws_b.not_valid_flag = 'N';
4322 */
4323
4324 l_dist_from IEU_UWQM_WORK_SOURCES_B.DISTRIBUTE_FROM%TYPE;
4325 l_dist_to IEU_UWQM_WORK_SOURCES_B.DISTRIBUTE_TO%TYPE;
4326 l_ws_id IEU_UWQM_WORK_SOURCES_B.WS_ID%TYPE;
4327
4328 -- Variables for Distribute_from
4329
4330 l_df_own_where_clause varchar2(4000);
4331 l_df_asg_where_clause varchar2(4000);
4332
4333 l_df_own_ws_clause varchar2(4000);
4334 l_df_own_ws_clause1 varchar2(4000);
4335 l_df_asg_ws_clause varchar2(4000);
4336 l_df_asg_ws_clause1 varchar2(4000);
4337
4338 l_df_final_where varchar2(4000);
4339
4340 l_df_grp_own_ctr number := 0;
4341 l_df_grp_asg_ctr number := 0;
4342
4343 -- Variables for Distribute_to
4344
4345 l_dt_own_where_clause varchar2(4000);
4346 l_dt_asg_where_clause varchar2(4000);
4347
4348 l_dt_own_ws_clause varchar2(4000);
4349 l_dt_own_ws_clause1 varchar2(4000);
4350 l_dt_asg_ws_clause varchar2(4000);
4351 l_dt_asg_ws_clause1 varchar2(4000);
4352
4353 l_dt_final_where varchar2(4000);
4354
4355 l_dt_grp_own_ctr number := 0;
4356 l_dt_grp_asg_ctr number := 0;
4357
4358 z number := 1;
4359 p_grp_id_list IEU_UWQ_GET_NEXT_WORK_PVT.IEU_GRP_ID_LIST;
4360 l_df_grp_id_clause varchar2(4000);
4361 l_df_grp_id_ctr number := 0;
4362
4363 l_delete_flag_yes varchar2(1);
4364
4365 cursor c_grp_id(p_resource_id in number) is
4366 select group_id from jtf_rs_group_members
4367 where resource_id = p_resource_id
4368 and nvl(delete_flag, 'N') <> l_delete_flag_yes;
4369
4370 l_not_valid_flag VARCHAR2(1);
4371
4372 l_bindvar_fm_ctr number;
4373 l_bindvar_to_ctr number;
4374 t number;
4375
4376 l_fm_group_owned_flag varchar2(1) := 'F';
4377 l_fm_group_assigned_flag varchar2(1) := 'F';
4378 l_to_ind_owned_flag varchar2(1) := 'F';
4379 l_to_ind_assigned_flag varchar2(1) := 'F';
4380
4381 x_filter_condition VARCHAR2(2000);/* Bug 10164373, 10634614 */
4382
4383 BEGIN
4384
4385
4386 l_dist_from := 'GROUP_OWNED';
4387 l_dist_to := 'INDIVIDUAL_ASSIGNED';
4388 l_delete_flag_yes := 'Y';
4389 l_bindvar_fm_ctr := 0;
4390 l_bindvar_to_ctr := 0;
4391
4392 /* Added fir bug 10164373, 10634614 */
4393
4394 x_filter_condition := NULL;
4395 If jtf_usr_hks.Ok_To_Execute('IEU_USER_HOOK_PUB',
4396 'ADDITIONAL_FILTER_WORKITEM',
4397 'B', 'C') THEN
4398 BEGIN
4399 IEU_USER_HOOK_PUB.ADDITIONAL_FILTER_WORKITEM
4400 ( p_resource_id => p_resource_id,
4401 x_filter_condition => x_filter_condition);
4402 EXCEPTION
4403 WHEN OTHERS THEN
4404 x_filter_condition := NULL;
4405 END;
4406 END IF;
4407
4408 /* performance issues with the query and try three different approach and using the one that is giving better performance
4409 1. owner_id in (select group_id from jtf_rs_group_members
4410 where resource_id = :resource_id
4411 and nvl(delete_flag,'N') <> 'Y');
4412 2. exists (select 1 from jtf_rs_group_members
4413 where resource_id = :resource_id
4414 and nvl(delete_flag,'N') <> 'Y');
4415 3. owner_id in (group_id1, group_id2, group_id3); - Explicitly passing the string.
4416
4417 Using # 3 approach so, the following loop is getting the group_ids for that resource_id and building the
4418 string: if only one group_id then string would be 'owner_id = group_id1' if no group_id then owner_id = ''
4419 if more than one group_ids then 'owner_id in (group_id1, group_id2...group_idx)'
4420
4421 Note: Right now, this approch is only applied for GROUP_OWNED because GROUP_ASSIGNED is not being used. In the future when
4422 GROUP_ASSIGNED is used then should apply the same logic to build the string.
4423 */
4424
4425 for grp_id in c_grp_id(p_resource_id)
4426 loop
4427 p_grp_id_list(z).group_id := grp_id.group_id;
4428 z := z + 1;
4429 end loop;
4430
4431 if p_grp_id_list.count = 0 then
4432 l_bindvar_fm_ctr := l_bindvar_fm_ctr + 1;
4433 l_df_grp_id_clause := 'owner_id in ('||':owner_id'||l_bindvar_fm_ctr||')';
4434 x_bindvar_from_list(l_bindvar_fm_ctr).bind_name := ':owner_id'||l_bindvar_fm_ctr;
4435 x_bindvar_from_list(l_bindvar_fm_ctr).value :='';
4436
4437 elsif p_grp_id_list.count > 0 then
4438 for x in p_grp_id_list.first..p_grp_id_list.last
4439 loop
4440
4441 if ((p_grp_id_list.count = 1) and (l_df_grp_id_ctr = 0)) then
4442 l_bindvar_fm_ctr := l_bindvar_fm_ctr + 1;
4443 x_bindvar_from_list(l_bindvar_fm_ctr).bind_name := ':owner_id'||l_bindvar_fm_ctr;
4444 x_bindvar_from_list(l_bindvar_fm_ctr).value := p_grp_id_list(x).group_id;
4448 elsif p_grp_id_list.count > 1 then
4445
4446 l_df_grp_id_clause := 'owner_id = '||':owner_id'||l_bindvar_fm_ctr;
4447
4449 if l_df_grp_id_ctr = 0 then
4450 l_bindvar_fm_ctr := l_bindvar_fm_ctr + 1;
4451 x_bindvar_from_list(l_bindvar_fm_ctr).bind_name := ':owner_id'||l_bindvar_fm_ctr;
4452 x_bindvar_from_list(l_bindvar_fm_ctr).value := p_grp_id_list(x).group_id;
4453 l_df_grp_id_clause := 'owner_id in ('||':owner_id'||l_bindvar_fm_ctr;
4454 l_df_grp_id_ctr := l_df_grp_id_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 := ':owner_id'||l_bindvar_fm_ctr;
4458 x_bindvar_from_list(l_bindvar_fm_ctr).value := p_grp_id_list(x).group_id;
4459 l_df_grp_id_clause := l_df_grp_id_clause||', '||':owner_id'||l_bindvar_fm_ctr;
4460 l_df_grp_id_ctr := l_df_grp_id_ctr + 1;
4461 end if;
4462 end if;
4463 if l_df_grp_id_ctr = p_grp_id_list.count then
4464 l_df_grp_id_clause := l_df_grp_id_clause||')';
4465 end if;
4466 end loop;
4467 end if;
4468 -- insert into p_temp values('final grp where clause '||l_df_grp_id_clause, 101);commit;
4469
4470 for i in p_ws_det_list.first .. p_ws_det_list.last
4471 loop
4472
4473 -- This will not throw any exception here, as the ws_code will be validated in the public api before calling
4474 -- this procedure.
4475
4476 BEGIN
4477 l_not_valid_flag := 'N';
4478 select WS_B.WS_ID
4479 into l_ws_id
4480 from IEU_UWQM_WORK_SOURCES_B WS_B
4481 where ws_code = p_ws_det_list(i).ws_code
4482 -- and ws_b.not_valid_flag = 'N';
4483 and ws_b.not_valid_flag = l_not_valid_flag;
4484 EXCEPTION
4485 when others then
4486 null;
4487 END;
4488
4489 -- Group Owned
4490 if (l_dist_from= 'GROUP_OWNED')
4491 then
4492
4493 -- Build the Work Source Where clause
4494 -- If this is the 1st WS, then where clause should be ws_id = :1
4495 -- else use ws_id in (:1,:2,..)
4496
4497 if (l_df_grp_own_ctr = 0)
4498 then
4499 l_bindvar_fm_ctr := l_bindvar_fm_ctr + 1;
4500 x_bindvar_from_list(l_bindvar_fm_ctr).bind_name := ':ws_id'||l_bindvar_fm_ctr;
4501 x_bindvar_from_list(l_bindvar_fm_ctr).value := l_ws_id;
4502
4503 l_df_own_ws_clause1 := ' ws_id = '||':ws_id'||l_bindvar_fm_ctr;
4504 l_df_own_ws_clause := ' ws_id in ('||':ws_id'||l_bindvar_fm_ctr;
4505 l_df_grp_own_ctr := l_df_grp_own_ctr + 1;
4506 else
4507 l_bindvar_fm_ctr := l_bindvar_fm_ctr + 1;
4508 x_bindvar_from_list(l_bindvar_fm_ctr).bind_name := ':ws_id'||l_bindvar_fm_ctr;
4509 x_bindvar_from_list(l_bindvar_fm_ctr).value := l_ws_id;
4510 l_df_own_ws_clause := l_df_own_ws_clause || ', '||':ws_id'||l_bindvar_fm_ctr;
4511 l_df_grp_own_ctr := l_df_grp_own_ctr + 1;
4512 end if;
4513
4514 if l_fm_group_owned_flag = 'F' then
4515 l_bindvar_fm_ctr := l_bindvar_fm_ctr + 1;
4516 x_bindvar_from_list(l_bindvar_fm_ctr).bind_name := ':owner_type'||l_bindvar_fm_ctr;
4517 x_bindvar_from_list(l_bindvar_fm_ctr).value := 'RS_GROUP';
4518 -- Build the complete Grp Own Where clause
4519 l_df_own_where_clause := ' owner_type = '||':owner_type'||l_bindvar_fm_ctr||
4520 ' and '||l_df_grp_id_clause;
4521 l_fm_group_owned_flag := 'T';
4522 end if;
4523
4524 -- insert into p_temp values(' Dist from group owned '||l_df_own_ws_clause1||' '||l_df_own_ws_clause||' '
4525 -- ||l_df_own_where_clause, l_df_grp_own_ctr);commit;
4526
4527 end if;
4528
4529 -- Group Assigned
4530 if (l_dist_from= 'GROUP_ASSIGNED')
4531 then
4532
4533 -- Build the Work Source Where clause
4534 -- If this is the 1st WS, then where clause should be ws_id = :1
4535 -- else use ws_id in (:1,:2,..)
4536 if (l_df_grp_asg_ctr = 0)
4537 then
4538 l_bindvar_fm_ctr := l_bindvar_fm_ctr + 1;
4539 x_bindvar_from_list(l_bindvar_fm_ctr).bind_name := ':ws_id'||l_bindvar_fm_ctr;
4540 x_bindvar_from_list(l_bindvar_fm_ctr).value := l_ws_id;
4541 l_df_asg_ws_clause1 := ' ws_id = '||'ws_id'||l_bindvar_fm_ctr;
4542 l_df_asg_ws_clause := ' ws_id in ('||'ws_id'||l_bindvar_fm_ctr;
4543 l_df_grp_asg_ctr := l_df_grp_asg_ctr + 1;
4544 else
4545 l_bindvar_fm_ctr := l_bindvar_fm_ctr + 1;
4546 x_bindvar_from_list(l_bindvar_fm_ctr).bind_name := ':ws_id'||l_bindvar_fm_ctr;
4547 x_bindvar_from_list(l_bindvar_fm_ctr).value := l_ws_id;
4548 l_df_asg_ws_clause := l_df_asg_ws_clause || ', '||'ws_id'||l_bindvar_fm_ctr;
4549 l_df_grp_asg_ctr := l_df_grp_asg_ctr + 1;
4550 end if;
4551
4552 if l_fm_group_assigned_flag = 'F' then
4553 l_bindvar_fm_ctr := l_bindvar_fm_ctr + 1;
4554
4555 x_bindvar_from_list(l_bindvar_fm_ctr).bind_name := ':assignee_type'||l_bindvar_fm_ctr;
4556 x_bindvar_from_list(l_bindvar_fm_ctr).value := 'RS_GROUP';
4557
4558 l_bindvar_fm_ctr := l_bindvar_fm_ctr + 1;
4559
4560 x_bindvar_from_list(l_bindvar_fm_ctr).bind_name := ':delete_flag'||l_bindvar_fm_ctr;
4561 x_bindvar_from_list(l_bindvar_fm_ctr).value := 'N';
4562
4563
4567 x_bindvar_from_list(l_bindvar_fm_ctr).value := 'Y';
4564 l_bindvar_fm_ctr := l_bindvar_fm_ctr + 1;
4565
4566 x_bindvar_from_list(l_bindvar_fm_ctr).bind_name := ':delete_flag'||l_bindvar_fm_ctr;
4568
4569
4570
4571 -- Build the complete Grp Asg Where clause
4572 l_df_asg_where_clause := ' assignee_type = '||':assignee_type'||(l_bindvar_fm_ctr-2)||
4573 ' and assignee_id in
4574 (select group_id from jtf_rs_group_members
4575 where resource_id = :resource_id'||
4576 ' and nvl(delete_flag,'||':delete_flag'||(l_bindvar_fm_ctr-1)||') <> '||':delete_flag'||l_bindvar_fm_ctr||')';
4577
4578 l_fm_group_assigned_flag := 'T';
4579 end if;
4580
4581 end if;
4582
4583
4584
4585 -- Distribute_To
4586
4587 if (p_type = 'DELIVER')
4588 then
4589
4590 -- Group Owned
4591 if (l_dist_to = 'INDIVIDUAL_OWNED')
4592 then
4593
4594 -- Build the Work Source Where clause
4595 -- If this is the 1st WS, then where clause should be ws_id = :1
4596 -- else use ws_id in (:1,:2,..)
4597 if (l_dt_grp_own_ctr = 0)
4598 then
4599 l_bindvar_to_ctr := l_bindvar_to_ctr + 1;
4600
4601 x_bindvar_to_list(l_bindvar_to_ctr).bind_name := ':ws_id'||l_bindvar_to_ctr;
4602 x_bindvar_to_list(l_bindvar_to_ctr).value := l_ws_id;
4603 l_dt_own_ws_clause1 := ' ws_id = '||':ws_id'||l_bindvar_to_ctr;
4604 l_dt_own_ws_clause := ' ws_id in ('||':ws_id'||l_bindvar_to_ctr;
4605 l_dt_grp_own_ctr := l_dt_grp_own_ctr + 1;
4606 else
4607 l_bindvar_to_ctr := l_bindvar_to_ctr + 1;
4608
4609 x_bindvar_to_list(l_bindvar_to_ctr).bind_name := ':ws_id'||l_bindvar_to_ctr;
4610 x_bindvar_to_list(l_bindvar_to_ctr).value := l_ws_id;
4611 l_dt_own_ws_clause := l_dt_own_ws_clause || ', '||':ws_id'||l_bindvar_to_ctr;
4612 l_dt_grp_own_ctr := l_dt_grp_own_ctr + 1;
4613 end if;
4614
4615 if l_to_ind_owned_flag = 'F' then
4616 l_bindvar_to_ctr := l_bindvar_to_ctr + 1;
4617
4618 x_bindvar_to_list(l_bindvar_to_ctr).bind_name := ':owner_type'||l_bindvar_to_ctr;
4619 x_bindvar_to_list(l_bindvar_to_ctr).value := 'RS_INDIVIDUAL';
4620
4621 -- Build the complete Grp Own Where clause
4622 l_dt_own_where_clause := ' owner_type = '||':owner_type'||l_bindvar_to_ctr||
4623 ' and owner_id = :resource_id';
4624 l_to_ind_owned_flag := 'T' ;
4625 end if;
4626
4627
4628 end if;
4629
4630
4631 -- Group Assigned
4632 if (l_dist_to = 'INDIVIDUAL_ASSIGNED')
4633 then
4634
4635 -- Build the Work Source Where clause
4636 -- If this is the 1st WS, then where clause should be ws_id = :1
4637 -- else use ws_id in (:1,:2,..)
4638
4639 if (l_dt_grp_asg_ctr = 0)
4640 then
4641 l_bindvar_to_ctr := l_bindvar_to_ctr + 1;
4642
4643 x_bindvar_to_list(l_bindvar_to_ctr).bind_name := ':ws_id'||l_bindvar_to_ctr;
4644 x_bindvar_to_list(l_bindvar_to_ctr).value := l_ws_id;
4645 l_dt_asg_ws_clause1 := ' ws_id = '||':ws_id'||l_bindvar_to_ctr;
4646 l_dt_asg_ws_clause := ' ws_id in ('||':ws_id'||l_bindvar_to_ctr;
4647 l_dt_grp_asg_ctr := l_dt_grp_asg_ctr + 1;
4648 else
4649 l_bindvar_to_ctr := l_bindvar_to_ctr + 1;
4650
4651 x_bindvar_to_list(l_bindvar_to_ctr).bind_name := ':ws_id'||l_bindvar_to_ctr;
4652 x_bindvar_to_list(l_bindvar_to_ctr).value := l_ws_id;
4653 l_dt_asg_ws_clause := l_dt_asg_ws_clause || ', '||':ws_id'||l_bindvar_to_ctr;
4654 l_dt_grp_asg_ctr := l_dt_grp_asg_ctr + 1;
4655 end if;
4656 if l_to_ind_assigned_flag = 'F' then
4657 l_bindvar_to_ctr := l_bindvar_to_ctr + 1;
4658
4659 x_bindvar_to_list(l_bindvar_to_ctr).bind_name := ':assignee_type'||l_bindvar_to_ctr;
4660 x_bindvar_to_list(l_bindvar_to_ctr).value := 'RS_INDIVIDUAL';
4661
4662 -- Build the complete Grp Asg Where clause
4663 l_dt_asg_where_clause := ' assignee_type = '||':assignee_type'||l_bindvar_to_ctr||
4664 ' and assignee_id = :resource_id';
4665
4666 -- insert into p_temp values('dist to individual assigned '||l_df_asg_ws_clause1||' '||l_df_asg_ws_clause||' '
4667 -- ||l_df_asg_where_clause, l_df_grp_asg_ctr);commit;
4668 l_to_ind_assigned_flag := 'T';
4669 end if;
4670
4671
4672 end if;
4673
4674 end if; /* p_type = Deliver */
4675
4676 end loop; /* p_ws_det_list.first . p_ws_det_list.last */
4677
4678
4679 ---------------- **************** Built The where Clause for Distribute_from **************** ----------------------
4680
4681 -- Add closing paranthesis to Work Source Where Clause
4682 -- ws_id in (1,2,3)
4683 if (l_df_grp_own_ctr > 1)
4684 then
4685 if (l_df_own_ws_clause is not null)
4686 then
4687 l_df_own_ws_clause := l_df_own_ws_clause || ')';
4688 end if;
4689 end if;
4690
4691 if (l_df_grp_asg_ctr > 1)
4692 then
4693 if (l_df_asg_ws_clause is not null)
4694 then
4695 l_df_asg_ws_clause := l_df_asg_ws_clause || ')';
4696 end if;
4697 end if;
4698
4699 l_df_final_where := null;
4700
4701 -- set the final where_clause
4705 then
4702 -- This includes both Grp Own and Grp Asg where clause
4703
4704 if (l_df_grp_own_ctr = 1)
4706 if ((l_df_own_ws_clause1 is not null) and
4707 (l_df_own_where_clause is not null))
4708 then
4709 -- l_final_where := '( '||l_own_ws_clause1 || l_own_where_clause || ')';
4710 l_df_final_where := '( '||l_df_own_where_clause || ' and ' || l_df_own_ws_clause1 || ')';
4711 end if;
4712 elsif (l_df_grp_own_ctr > 1)
4713 then
4714 if ((l_df_own_ws_clause is not null) and
4715 (l_df_own_where_clause is not null))
4716 then
4717 -- l_final_where := '( '||l_own_ws_clause || l_own_where_clause || ')';
4718 l_df_final_where := '( '|| l_df_own_where_clause || ' and ' || l_df_own_ws_clause || ')';
4719 end if;
4720 end if;
4721
4722
4723 if (l_df_grp_asg_ctr = 1)
4724 then
4725 if ((l_df_asg_ws_clause1 is not null) and
4726 (l_df_asg_where_clause is not null))
4727 then
4728 if (l_df_final_where is null)
4729 then
4730 -- l_final_where := '( '||l_asg_ws_clause1 || l_asg_where_clause || ')';
4731 l_df_final_where := '( '||l_df_asg_where_clause || ' and '||l_df_asg_ws_clause1 || ')';
4732 elsif (l_df_final_where is not null)
4733 then
4734 -- l_final_where := l_final_where||' OR '|| '( '|| l_asg_ws_clause1 || l_asg_where_clause|| ')';
4735 l_df_final_where := l_df_final_where||' OR '|| '( '|| l_df_asg_where_clause||' and '||l_df_asg_ws_clause1|| ')';
4736 end if;
4737 end if;
4738 elsif (l_df_grp_asg_ctr > 1)
4739 then
4740 if ((l_df_asg_ws_clause is not null) and
4741 (l_df_asg_where_clause is not null))
4742 then
4743 if (l_df_final_where is null)
4744 then
4745 -- l_final_where := '( '||l_asg_ws_clause || l_asg_where_clause|| ')';
4746 l_df_final_where := '( '||l_df_asg_where_clause|| ' and '||l_df_asg_ws_clause || ')';
4747 elsif (l_df_final_where is not null)
4748 then
4749 -- l_final_where := l_final_where||' OR '|| '( '|| l_asg_ws_clause || l_asg_where_clause|| ')';
4750 l_df_final_where := l_df_final_where||' OR '|| '( '|| l_df_asg_where_clause||' and '||l_df_asg_ws_clause || ')';
4751 end if;
4752 end if;
4753 end if;
4754
4755 x_dist_from_where := l_df_final_where;
4756
4757 /* bug 10164373, 10634614 */
4758 IF (x_filter_condition is not null) THEN
4759 x_dist_from_where := '(' || x_dist_from_where || ' AND '
4760 || x_filter_condition || ')';
4761 END IF;
4762
4763 -- insert into p_temp values('final from where '||x_dist_from_where, 1);commit;
4764 --dbms_output.put_line('dist from: '||x_dist_from_where);
4765
4766 ---------------- **************** Built The where Clause for Distribute_to **************** ----------------------
4767
4768
4769 if (p_type = 'DELIVER')
4770 then
4771
4772
4773 -- Add closing paranthesis to Work Source Where Clause
4774 -- ws_id in (1,2,3)
4775 if (l_dt_grp_own_ctr > 1)
4776 then
4777 if (l_dt_own_ws_clause is not null)
4778 then
4779 l_dt_own_ws_clause := l_dt_own_ws_clause || ')';
4780 end if;
4781 end if;
4782
4783
4784 if (l_dt_grp_asg_ctr > 1)
4785 then
4786 if (l_dt_asg_ws_clause is not null)
4787 then
4788 l_dt_asg_ws_clause := l_dt_asg_ws_clause || ')';
4789 end if;
4790 end if;
4791
4792 l_dt_final_where := null;
4793
4794 -- set the final where_clause
4795 -- This includes both Grp Own and Grp Asg where clause
4796
4797 if (l_dt_grp_own_ctr = 1)
4798 then
4799 if ((l_dt_own_ws_clause1 is not null) and
4800 (l_dt_own_where_clause is not null))
4801 then
4802 -- l_final_where := '( '||l_own_ws_clause1 || l_own_where_clause || ')';
4803 l_dt_final_where := '( '||l_dt_own_where_clause ||' and '||l_dt_own_ws_clause1 ||')';
4804 end if;
4805 elsif (l_dt_grp_own_ctr > 1)
4806 then
4807 if ((l_dt_own_ws_clause is not null) and
4808 (l_dt_own_where_clause is not null))
4809 then
4810 -- l_final_where := '( '||l_own_ws_clause || l_own_where_clause || ')';
4811 l_dt_final_where := '( '||l_dt_own_where_clause ||' and '||l_dt_own_ws_clause || ')';
4812 end if;
4813 end if;
4814
4815 if (l_dt_grp_asg_ctr = 1)
4816 then
4817 if ((l_dt_asg_ws_clause1 is not null) and
4818 (l_dt_asg_where_clause is not null))
4819 then
4820 if (l_dt_final_where is null)
4821 then
4822 -- l_final_where := '( '||l_asg_ws_clause1 || l_asg_where_clause || ')';
4823 l_dt_final_where := '( '||l_dt_asg_where_clause ||' and '||l_dt_asg_ws_clause1 || ')';
4824 elsif (l_dt_final_where is not null)
4825 then
4826 -- l_final_where := l_final_where||' OR '|| '( '|| l_asg_ws_clause1 || l_asg_where_clause|| ')';
4827 l_dt_final_where := l_dt_final_where||' OR '|| '( '|| l_dt_asg_where_clause||' and '||l_dt_asg_ws_clause1 || ')';
4828 end if;
4829 end if;
4830 elsif (l_dt_grp_asg_ctr > 1)
4831 then
4832 if ((l_dt_asg_ws_clause is not null) and
4833 (l_dt_asg_where_clause is not null))
4834 then
4835 if (l_dt_final_where is null)
4836 then
4837 -- l_final_where := '( '||l_asg_ws_clause || l_asg_where_clause|| ')';
4838 l_dt_final_where := '( '||l_dt_asg_where_clause||' and '||l_dt_asg_ws_clause || ')';
4839 elsif (l_dt_final_where is not null)
4840 then
4844 end if;
4841 -- l_final_where := l_final_where||' OR '|| '( '|| l_asg_ws_clause || l_asg_where_clause|| ')';
4842 l_dt_final_where := l_dt_final_where||' OR '|| '( '|| l_dt_asg_where_clause||' and '||l_dt_asg_ws_clause || ')';
4843 end if;
4845 end if;
4846
4847
4848 x_dist_to_where := l_dt_final_where;
4849
4850 /* bug 10164373,10634614 */
4851 IF (x_filter_condition is not null) THEN
4852 x_dist_to_where := '(' || x_dist_to_where || ' AND '
4853 || x_filter_condition || ')';
4854 END IF;
4855
4856 --dbms_output.put_line('dist from: '||x_dist_to_where);
4857 --insert into p_temp values('final to where '||x_dist_to_where, 2);commit;
4858
4859
4860
4861 end if; /* p_type = Deliver */
4862
4863 END GET_WS_WHERE_CLAUSE;
4864
4865 PROCEDURE GET_WS_WHERE_CLAUSE
4866 (p_ws_det_list IN IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WS_DETAILS_LIST,
4867 p_resource_id IN NUMBER,
4868 x_dist_from_where OUT NOCOPY VARCHAR2,
4869 x_dist_to_where OUT NOCOPY VARCHAR2
4870 ) IS
4871 l_list IEU_UWQ_BINDVAR_LIST;
4872 BEGIN
4873 GET_WS_WHERE_CLAUSE ('DISTRIBUTE', p_ws_det_list, p_resource_id, x_dist_from_where, x_dist_to_where,l_list,l_list);
4874 END GET_WS_WHERE_CLAUSE;
4875 PROCEDURE CLEANUP_DISTRIBUTING_STATUS
4876 (
4877 P_resource_id IN NUMBER,
4878 X_MSG_DATA OUT NOCOPY VARCHAR2,
4879 X_RETURN_STATUS OUT NOCOPY VARCHAR2
4880 )
4881 IS
4882
4883 p_grp_id_list IEU_UWQ_GET_NEXT_WORK_PVT.IEU_GRP_ID_LIST;
4884 l_df_grp_id_clause varchar2(1000);
4885 l_df_grp_id_ctr number := 0;
4886 z number := 1;
4887 l_sql_stmt varchar2(4000);
4888
4889 l_distribution_status_id number;
4890 l_status_id number;
4891 l_last_update_date date;
4892
4893 l_delete_flag_no varchar2(1);
4894
4895 cursor c_grp_id(p_resource_id in number) is
4896 select group_id from jtf_rs_group_members
4897 where resource_id = p_resource_id
4898 and nvl(delete_flag, 'N') = l_delete_flag_no;
4899
4900 BEGIN
4901 l_delete_flag_no :='N';
4902 l_distribution_status_id := 2;
4903 l_status_id := 0;
4904 l_last_update_date := sysdate - 10/1440;
4905
4906 if ( p_resource_id is not null)
4907 then
4908 x_return_status := FND_API.G_RET_STS_SUCCESS;
4909
4910 for grp_id in c_grp_id(p_resource_id)
4911 loop
4912 p_grp_id_list(z).group_id := grp_id.group_id;
4913 z := z + 1;
4914 end loop;
4915
4916 if p_grp_id_list.count = 0 then
4917 l_df_grp_id_clause := 'owner_id in ('||''''||''||''''||')';
4918 elsif p_grp_id_list.count > 0 then
4919 for x in p_grp_id_list.first..p_grp_id_list.last
4920 loop
4921
4922 if ((p_grp_id_list.count = 1) and (l_df_grp_id_ctr = 0)) then
4923 l_df_grp_id_clause := 'owner_id = '||p_grp_id_list(x).group_id;
4924 elsif p_grp_id_list.count > 1 then
4925 if l_df_grp_id_ctr = 0 then
4926 l_df_grp_id_clause := 'owner_id in ('||p_grp_id_list(x).group_id;
4927 l_df_grp_id_ctr := l_df_grp_id_ctr + 1;
4928 else
4929 l_df_grp_id_clause := l_df_grp_id_clause||', '||p_grp_id_list(x).group_id;
4930 l_df_grp_id_ctr := l_df_grp_id_ctr + 1;
4931 end if;
4932 end if;
4933 if l_df_grp_id_ctr = p_grp_id_list.count then
4934 l_df_grp_id_clause := l_df_grp_id_clause||')';
4935 end if;
4936 end loop;
4937 end if;
4938 l_df_grp_id_clause := '( owner_type = '||''''||'RS_GROUP'||''''||' and '||l_df_grp_id_clause||')';
4939
4940
4941 l_sql_stmt := 'UPDATE IEU_UWQM_ITEMS
4942 SET DISTRIBUTION_STATUS_ID = 1
4943 WHERE '|| l_df_grp_id_clause ||
4944 'AND DISTRIBUTION_STATUS_ID = '||':l_distribution_status_id '||
4945 'AND STATUS_ID = '||':l_status_id'
4946 ||' and to_date(last_update_date'||','||''''||'DD-MON-YYYY HH24:MI:SS'||''''||') < '
4947 ||' to_date('||''''||l_last_update_date||''''||','||''''||'DD-MON-YYYY HH24:MI:SS'||''''||')' ;
4948
4949 BEGIN
4950 execute immediate l_sql_stmt
4951 using in l_distribution_status_id, in l_status_id;
4952 EXCEPTION WHEN OTHERS THEN
4953 X_MSG_DATA := SQLCODE||' '||SQLERRM;
4954 END;
4955 commit;
4956 end if;
4957
4958 EXCEPTION
4959 WHEN OTHERS THEN
4960 x_return_status := FND_API.G_RET_STS_ERROR;
4961 x_msg_data := SQLCODE||' '||sqlerrm;
4962
4963 END CLEANUP_DISTRIBUTING_STATUS;
4964
4965
4966 END IEU_UWQ_GET_NEXT_WORK_PVT;
4967