[Home] [Help]
PACKAGE BODY: APPS.IEU_DIAG_AUDIT_TRACK_PVT
Source
1 PACKAGE BODY IEU_DIAG_AUDIT_TRACK_PVT AS
2 /* $Header: IEUATRB.pls 120.2 2006/01/14 09:02:40 msista noship $ */
3 PROCEDURE getUnDis ( x_return_status OUT NOCOPY VARCHAR2,
4 x_msg_count OUT NOCOPY NUMBER,
5 x_msg_data OUT NOCOPY VARCHAR2,
6 p_from_date IN DATE, -- format : 10-JAN-04
7 p_to_date IN DATE,
8 x_results OUT NOCOPY IEU_DIAG_DISTRIBUTING_NST
9 )AS
10 l_msg_count NUMBER(2);
11
12 l_msg_data VARCHAR2(2000);
13 l_ws_name varchar2(100) ;
14 prev_ws_name varchar2(100);
15 i integer;
16 j integer;
17 l_count integer;
18 l_from_date date ;
19 l_to_date DATE ;
20 l_date date;
21 owner_name varchar2(2000);
22 assignee_name varchar2(2000);
23 priority varchar2(2000);
24 title varchar2(2000);
25 l_results IEU_DIAG_NOTMEMBER_NST;
26 l_tmp number;
27 l_temp_count number;
28 work_item_number number;
29 l_ws_code varchar2(2000);
30 cursor cur_items IS
31 Select a.workitem_pk_id,
32 a.title,
33 DECODE(a.STATUS_ID,'0', 'Open', '3', 'Close', '4', 'Delete', '5', 'Sleep') status,
34 a.priority_id,
35 a.due_date,
36 a.reschedule_time,
37 a.OWNER_ID,
38 a.owner_type,
39 a.ASSIGNEE_ID,
40 a.assignee_type,
41 a.ws_id
42 from ieu_uwqm_items a
43 Where a.DISTRIBUTION_STATUS_ID = 0
44 And nvl(a.owner_type, 'NULL') <> 'RS_GROUP'
45 AND nvl(a.assignee_type, 'NULL') <> 'RS_INDIVIDUAL'
46 and a.creation_date BETWEEN p_from_date AND p_to_date
47 ORDER BY a.title;
48
49 BEGIN
50 owner_name :='';
51 assignee_name :='';
52 priority :='';
53 l_temp_count :=0;
54 work_item_number :=0;
55 l_ws_name := 'ws_name';
56 prev_ws_name := 'ws_name';
57 l_ws_code:='';
58 i := 0;
59 l_count :=0;
60 j :=0;
61 l_tmp :=0;
62 title :='';
63 --dbms_output.put_line('begin');
64 fnd_msg_pub.delete_msg();
65 x_return_status := fnd_api.g_ret_sts_success;
66 x_msg_data := 'begin--> from '||p_from_date||' to '|| p_to_date;
67 FND_MSG_PUB.initialize;
68 x_results := IEU_DIAG_DISTRIBUTING_NST();
69 FOR cur_rec IN cur_items
70 LOOP
71 --dbms_output.put_line('in the loop of cur_rec');
72 i := i+1;
73
74 x_results.EXTEND(1);
75 -- dbms_output.put_line('extended');
76 owner_name :='';
77 assignee_name :='';
78 priority :='';
79 title := '';
80 l_ws_code :='';
81 l_ws_name :='';
82
83 if cur_rec.owner_id is not null then
84 if cur_rec.owner_type = 'RS_GROUP' then
85 begin
86 select group_name into owner_name
87 from jtf_rs_groups_tl
88 where group_id = cur_rec.owner_id and language = FND_GLOBAL.CURRENT_LANGUAGE;
89 exception
90 when no_data_found then null;
91 end;
92 else
93 begin
94 select resource_name into owner_name
95 from JTF_RS_RESOURCE_EXTNS_vl
96 where resource_id = cur_rec.owner_id;
97 exception
98 when no_data_found then null;
99 end;
100 end if;
101 end if;
102
103 if cur_rec.assignee_id is not null then
104 if cur_rec.assignee_type = 'RS_INDIVIDUAL' then
105 begin
106 select resource_name into assignee_name
107 from JTF_RS_RESOURCE_EXTNS_vl
108 where resource_id = cur_rec.assignee_id;
109 exception
110 when no_data_found then null;
111 end;
112 else
113 begin
114 select group_name into assignee_name
115 from jtf_rs_groups_tl
116 where group_id = cur_rec.assignee_id and language = FND_GLOBAL.CURRENT_LANGUAGE;
117 exception
118 when no_data_found then null;
119 end;
120 end if;
121 end if;
122
123
124 if cur_rec.priority_id is not null then
125 begin
126 select name into priority
127 from ieu_uwqm_priorities_tl
128 where priority_id = cur_rec.priority_id
129 and language = FND_GLOBAL.CURRENT_LANGUAGE;
130 exception
131 when no_data_found then null;
132 end;
133 end if;
134
135 if cur_rec.ws_id is not null then
136 begin
137 select ws_name into l_ws_name
138 from ieu_uwqm_work_sources_tl
139 where ws_id = cur_rec.ws_id
140 and language = FND_GLOBAL.CURRENT_LANGUAGE;
141 exception
142 when no_data_found then null;
143 end;
144 end if;
145
146 --dbms_output.put_line('extened');
147 x_results(x_results.last) :=IEU_DIAG_DISTRIBUTING_OBJ(cur_rec.workitem_pk_id,
148 cur_rec.title,
149 cur_rec.status,
150 priority,
151 cur_rec.due_date,
152 cur_rec.reschedule_time,
153 cur_rec.owner_id,
154 owner_name,
155 cur_rec.assignee_id,
156 assignee_name,
157 l_ws_name
158 );
159
160 end LOOP;
161 EXCEPTION
162
163 WHEN FND_API.G_EXC_ERROR THEN
164 x_return_status := FND_API.G_RET_STS_ERROR;
165 x_msg_data := sqlerrm;
166 x_msg_count := fnd_msg_pub.COUNT_MSG();
167 FOR i in 1..x_msg_count LOOP
168 l_msg_data := '';
169 l_msg_count := 0;
170 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
171 x_msg_data := x_msg_data || ',' || l_msg_data;
172 END LOOP;
173 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
174 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
175 x_msg_data := sqlerrm;
176 x_msg_count := fnd_msg_pub.COUNT_MSG();
177 FOR i in 1..x_msg_count LOOP
178 l_msg_data := '';
179 l_msg_count := 0;
180 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
181 x_msg_data := x_msg_data || ',' || l_msg_data;
182 END LOOP;
183
184
185 WHEN OTHERS THEN
186 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
187 x_msg_data := sqlerrm;
188 x_msg_count := fnd_msg_pub.COUNT_MSG();
189 FOR i in 1..x_msg_count LOOP
190 l_msg_data := '';
191 l_msg_count := 0;
192 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
193 x_msg_data := x_msg_data || ',' || l_msg_data;
194 END LOOP;
195
196 end getUnDis;
197
198 PROCEDURE getDisSpe ( x_return_status OUT NOCOPY VARCHAR2,
199 x_msg_count OUT NOCOPY NUMBER,
200 x_msg_data OUT NOCOPY VARCHAR2,
201 p_user_name IN varchar2,
202 p_from_date IN DATE, -- format : 10-JAN-04
203 p_to_date IN DATE,
204 x_results OUT NOCOPY IEU_DIAG_DISTRIBUTING_NST
205 )AS
206 l_msg_count NUMBER(2);
207
208 l_msg_data VARCHAR2(2000);
209 prev_ws_name varchar2(100);
210 i integer;
211 j integer;
212 l_count integer;
213 l_from_date date ;
214 l_to_date DATE ;
215 l_date date;
216 owner_name varchar2(2000);
217 assignee_name varchar2(2000);
218 priority varchar2(2000);
219
220 l_results IEU_DIAG_NOTMEMBER_NST;
221 l_tmp number;
222 l_temp_count number;
223 work_item_number number;
224 l_ws_code varchar2(2000);
225 l_ws_name varchar2(2000);
226 l_user_id FND_USER.USER_ID%TYPE;
227 l_sql VARCHAR2(4000);
228
229 cursor cur_items IS
230 Select a.workitem_pk_id,
231 a.title,
232 DECODE(a.STATUS_ID,'0', 'Open', '3', 'Close', '4', 'Delete', '5', 'Sleep') status,
233 a.priority_id,
234 a.due_date,
235 a.reschedule_time,
236 a.OWNER_ID,
237 a.owner_type,
238 a.ASSIGNEE_ID,
239 a.assignee_type,
240 a.ws_id
241 from ieu_uwqm_items a
242 Where a.DISTRIBUTION_STATUS_ID = 3
243 And a.assignee_type = 'RS_INDIVIDUAL'
244 And a.assignee_id IN ( select resource_id from JTF_RS_RESOURCE_EXTNS where lower(user_name) = lower(p_user_name))
245 and a.creation_date BETWEEN p_from_date AND p_to_date
246 ORDER BY a.title;
247
248
249 BEGIN
250 owner_name :='';
251 assignee_name :='';
252 priority :='';
253 l_temp_count :=0;
254 work_item_number :=0;
255 l_ws_name := 'ws_name';
256 prev_ws_name := 'ws_name';
257 i := 0;
258 l_count :=0;
259 j :=0;
260 l_tmp :=0;
261 --dbms_output.put_line('begin');
262 fnd_msg_pub.delete_msg();
263 x_return_status := fnd_api.g_ret_sts_success;
264 --x_msg_data := 'begin--> from '||p_from_date||' to '|| p_to_date;
265 x_msg_data := '';
266 FND_MSG_PUB.initialize;
267 x_results := IEU_DIAG_DISTRIBUTING_NST();
268
269 begin
270
271 -- msista 1/14/06 - the following sql can be removed because the queried
272 -- user_id is not used, but the query is used for
273 -- validating the user_name as a part of the diagnostic
274 -- test, so leaving it as is.
275 l_sql := ' select user_id from fnd_user where upper(user_name) like upper( :p_user_name)';
276 EXECUTE IMMEDIATE l_sql into l_user_id USING p_user_name;
277
278 EXCEPTION
279 WHEN NO_DATA_FOUND THEN
280 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_USER_INVALID');
281 FND_MSG_PUB.Add;
282 x_return_status := FND_API.G_RET_STS_ERROR;
283 x_msg_count := fnd_msg_pub.COUNT_MSG();
284
285 FOR i in 1..x_msg_count LOOP
286 l_msg_data := '';
287 l_msg_count := 0;
288 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
289 x_msg_data := x_msg_data || ',' || l_msg_data;
290 END LOOP;
291
292 end;
293
294 if (x_return_status = 'S') then
295 FOR cur_rec IN cur_items
296 LOOP
297 --dbms_output.put_line('in the loop of cur_rec');
298 i := i+1;
299
300 x_results.EXTEND(1);
301 -- dbms_output.put_line('extended');
302 owner_name :='';
303 assignee_name :='';
304 priority :='';
305 l_ws_code:='';
306 l_ws_name:='';
307
308 if cur_rec.owner_id is not null then
309 if cur_rec.owner_type = 'RS_GROUP' then
310 begin
311 select group_name into owner_name
312 from jtf_rs_groups_tl
313 where group_id = cur_rec.owner_id and language = FND_GLOBAL.CURRENT_LANGUAGE;
314 exception
315 when no_data_found then null;
316 end;
317 else
318 begin
319 select resource_name into owner_name
320 from JTF_RS_RESOURCE_EXTNS_vl
321 where resource_id = cur_rec.owner_id;
322 exception
323 when no_data_found then null;
324 end;
325 end if;
326 end if;
327
328 if cur_rec.assignee_id is not null then
329 if cur_rec.assignee_type = 'RS_INDIVIDUAL' then
330 begin
331 select resource_name into assignee_name
332 from JTF_RS_RESOURCE_EXTNS_vl
333 where resource_id = cur_rec.assignee_id;
334 exception
335 when no_data_found then null;
336 end;
337 else
338 begin
339 select group_name into assignee_name
340 from jtf_rs_groups_tl
341 where group_id = cur_rec.assignee_id and language = FND_GLOBAL.CURRENT_LANGUAGE;
342 exception
343 when no_data_found then null;
344 end;
345 end if;
346 end if;
347
348
349
350 if cur_rec.priority_id is not null then
351 begin
352 select name into priority
353 from ieu_uwqm_priorities_tl
354 where priority_id = cur_rec.priority_id
355 and language = FND_GLOBAL.CURRENT_LANGUAGE;
356 exception
357 when no_data_found then null;
358 end;
359 end if;
360
361 if cur_rec.ws_id is not null then
362 begin
363 select ws_name into l_ws_name
364 from ieu_uwqm_work_sources_tl
365 where ws_id = cur_rec.ws_id
366 and language = FND_GLOBAL.CURRENT_LANGUAGE;
367 exception
368 when no_data_found then null;
369 end;
370 end if;
371
372
373
374 --dbms_output.put_line('extened');
375 x_results(x_results.last) :=IEU_DIAG_DISTRIBUTING_OBJ(cur_rec.workitem_pk_id,
376 cur_rec.title,
377 cur_rec.status,
378 priority,
379 cur_rec.due_date,
380 cur_rec.reschedule_time,
381 cur_rec.owner_id,
382 owner_name,
383 cur_rec.assignee_id,
384 assignee_name,
385 l_ws_name
386 );
387 end LOOP;
388 end if;
389 EXCEPTION
390
391 WHEN FND_API.G_EXC_ERROR THEN
392 x_return_status := FND_API.G_RET_STS_ERROR;
393 x_msg_data := sqlerrm;
394 x_msg_count := fnd_msg_pub.COUNT_MSG();
395 FOR i in 1..x_msg_count LOOP
396 l_msg_data := '';
397 l_msg_count := 0;
398 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
399 x_msg_data := x_msg_data || ',' || l_msg_data;
400 END LOOP;
401 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
402 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
403 x_msg_data := sqlerrm;
404 x_msg_count := fnd_msg_pub.COUNT_MSG();
405 FOR i in 1..x_msg_count LOOP
406 l_msg_data := '';
407 l_msg_count := 0;
408 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
409 x_msg_data := x_msg_data || ',' || l_msg_data;
410 END LOOP;
411
412 WHEN OTHERS THEN
413 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
414 x_msg_data := sqlerrm;
415 x_msg_count := fnd_msg_pub.COUNT_MSG();
416 FOR i in 1..x_msg_count LOOP
417 l_msg_data := '';
418 l_msg_count := 0;
419 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
420 x_msg_data := x_msg_data || ',' || l_msg_data;
421 END LOOP;
422
423 end getDisSpe;
424 PROCEDURE getReDis( x_return_status OUT NOCOPY VARCHAR2,
425 x_msg_count OUT NOCOPY NUMBER,
426 x_msg_data OUT NOCOPY VARCHAR2,
427 p_group_name IN Varchar2,
428 p_from_date IN DATE, -- format : 10-JAN-04
429 p_to_date IN DATE,
430 x_results OUT NOCOPY IEU_DIAG_REQUEUED_NST
431 )AS
432 l_msg_count NUMBER(2);
433
434 l_msg_data VARCHAR2(2000);
435 l_ws_name varchar2(100) ;
436 prev_ws_name varchar2(100);
437 i integer;
438 j integer;
439 l_count integer;
440 l_from_date date ;
441 l_to_date DATE ;
442 l_date date;
443 owner_name_prev varchar2(2000);
444 assignee_name_prev varchar2(2000);
445 owner_name_curr varchar2(2000);
446 assignee_name_curr varchar2(2000);
447 l_results IEU_DIAG_NOTMEMBER_NST;
448 l_tmp number;
449 l_temp_count number;
450 work_item_number number;
451 title varchar2(2000);
452 cursor cur_items IS
453 Select a.workitem_pk_id, a.workitem_obj_code,
454 DECODE(a.workitem_STATUS_ID_curr,'0', 'Open', '3', 'Close', '4', 'Delete', '5', 'Sleep') status,
455 a.owner_id_prev,a.owner_id_curr,a.owner_type_prev,a.owner_type_curr,
456 a.assignee_id_prev,a.assignee_id_curr,a.assignee_type_prev,a.assignee_type_curr,
457 ws_code
458 from ieu_uwqm_audit_log a
459 Where a.creation_date between p_from_date and p_to_date
460 And (a.assignee_type_prev ='RS_INDIVIDUAL' )
461 and (a.assignee_type_curr ='RS_INDIVIDUAL')
462 and (a.assignee_id_prev <> a.assignee_id_curr)
463 AND a.owner_id_curr in ( select group_id from jtf_rs_groups_vl where lower(group_name) = lower(p_group_name))
464 ORDER BY a.creation_date;
465
466
467
468
469 BEGIN
470 owner_name_prev :='';
471 assignee_name_prev :='';
472 l_temp_count :=0;
473 work_item_number :=0;
474 l_ws_name := 'ws_name';
475 prev_ws_name := 'ws_name';
476 title := '';
477 i := 0;
478 l_count :=0;
479 j :=0;
480 l_tmp :=0;
481 --dbms_output.put_line('begin');
482 fnd_msg_pub.delete_msg();
483 x_return_status := fnd_api.g_ret_sts_success;
484 x_msg_data := 'begin--> from '||p_from_date||' to '|| p_to_date;
485 FND_MSG_PUB.initialize;
486 x_results := IEU_DIAG_REQUEUED_NST();
487
488 FOR cur_rec IN cur_items
489 LOOP
490 --dbms_output.put_line('in the loop of cur_rec');
491 i := i+1;
492
493 x_results.EXTEND(1);
494 -- dbms_output.put_line('extended');
495 owner_name_prev :='';
496 assignee_name_prev :='';
497 owner_name_curr :='';
498 assignee_name_curr :='';
499 title :='';
500 l_ws_name := '';
501
502
503 begin
504 select title into title
505 from ieu_uwqm_items where workitem_pk_id = cur_rec.workitem_pk_id
506 and workitem_obj_code=cur_rec.workitem_obj_code;
507 exception
508 when no_data_found then null;
509 end;
510 if cur_rec.owner_id_prev is not null then
511 if cur_rec.owner_type_prev = 'RS_GROUP' then
512 begin
513 select group_name into owner_name_prev
514 from jtf_rs_groups_tl
515 where group_id = cur_rec.owner_id_prev and language = FND_GLOBAL.CURRENT_LANGUAGE;
516 exception
517 when no_data_found then null;
518 end;
519 else
520 begin
521 select resource_name into owner_name_prev
522 from JTF_RS_RESOURCE_EXTNS_vl
523 where resource_id = cur_rec.owner_id_prev;
524 exception
525 when no_data_found then null;
526 end;
527 end if;
528 end if;
529
530 if cur_rec.assignee_id_prev is not null then
531 if cur_rec.assignee_type_prev = 'RS_INDIVIDUAL' then
532 begin
533 select resource_name into assignee_name_prev
534 from JTF_RS_RESOURCE_EXTNS_vl
535 where resource_id = cur_rec.assignee_id_prev;
536 exception
537 when no_data_found then null;
538 end;
539 else
540 begin
541 select group_name into assignee_name_prev
542 from jtf_rs_groups_tl
543 where group_id = cur_rec.assignee_id_prev and language = FND_GLOBAL.CURRENT_LANGUAGE;
544 exception
545 when no_data_found then null;
546 end;
547 end if;
548 end if;
549 if cur_rec.owner_id_curr is not null then
550 if cur_rec.owner_type_curr = 'RS_GROUP' then
551 begin
552 select group_name into owner_name_curr
553 from jtf_rs_groups_tl
554 where group_id = cur_rec.owner_id_curr and language = FND_GLOBAL.CURRENT_LANGUAGE;
555 exception
556 when no_data_found then null;
557 end;
558 else
559 begin
560 select resource_name into owner_name_curr
561 from JTF_RS_RESOURCE_EXTNS_vl
562 where resource_id = cur_rec.owner_id_curr;
563 exception
564 when no_data_found then null;
565 end;
566 end if;
567 end if;
568
569 if cur_rec.assignee_id_curr is not null then
570 if cur_rec.assignee_type_curr = 'RS_INDIVIDUAL' then
571 begin
572 select resource_name into assignee_name_curr
573 from JTF_RS_RESOURCE_EXTNS_vl
574 where resource_id = cur_rec.assignee_id_curr;
575 exception
576 when no_data_found then null;
577 end;
578 else
579 begin
580 select group_name into assignee_name_curr
581 from jtf_rs_groups_tl
582 where group_id = cur_rec.assignee_id_curr and language = FND_GLOBAL.CURRENT_LANGUAGE;
583 exception
584 when no_data_found then null;
585 end;
586 end if;
587 end if;
588
589 if cur_rec.ws_code is not null then
590 begin
591 select ws_name into l_ws_name
592 from ieu_uwqm_work_sources_tl tl, ieu_uwqm_work_sources_b b
593 where b.ws_id = tl.ws_id
594 and tl.language = FND_GLOBAL.CURRENT_LANGUAGE
595 and b.ws_code=cur_rec.ws_code;
596 exception
597 when no_data_found then null;
598 end;
599 end if;
600
601
602 --dbms_output.put_line('extened');
603 x_results(x_results.last) :=IEU_DIAG_REQUEUED_OBJ(cur_rec.workitem_pk_id,
604 title,
605 cur_rec.status,
606 cur_rec.owner_id_prev,
607 owner_name_prev,
608 cur_rec.owner_id_curr,
609 owner_name_curr,
610 cur_rec.assignee_id_prev,
611 assignee_name_prev,
612 cur_rec.assignee_id_curr,
613 assignee_name_curr,
614 l_ws_name
615 );
616 end LOOP;
617 EXCEPTION
618
619 WHEN FND_API.G_EXC_ERROR THEN
620 x_return_status := FND_API.G_RET_STS_ERROR;
621 x_msg_data := sqlerrm;
622 x_msg_count := fnd_msg_pub.COUNT_MSG();
623 FOR i in 1..x_msg_count LOOP
624 l_msg_data := '';
625 l_msg_count := 0;
626 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
627 x_msg_data := x_msg_data || ',' || l_msg_data;
628 END LOOP;
629 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
630 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
631 x_msg_data := sqlerrm;
632 x_msg_count := fnd_msg_pub.COUNT_MSG();
633 FOR i in 1..x_msg_count LOOP
634 l_msg_data := '';
635 l_msg_count := 0;
636 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
637 x_msg_data := x_msg_data || ',' || l_msg_data;
638 END LOOP;
639
640
641 WHEN OTHERS THEN
642 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
643 x_msg_data := sqlerrm;
644 x_msg_count := fnd_msg_pub.COUNT_MSG();
645 FOR i in 1..x_msg_count LOOP
646 l_msg_data := '';
647 l_msg_count := 0;
648 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
649 x_msg_data := x_msg_data || ',' || l_msg_data;
650 END LOOP;
651
652 end getReDis;
653 PROCEDURE getRequeued( x_return_status OUT NOCOPY VARCHAR2,
654 x_msg_count OUT NOCOPY NUMBER,
655 x_msg_data OUT NOCOPY VARCHAR2,
656 p_from_date IN DATE, -- format : 10-JAN-04
657 p_to_date IN DATE,
658 x_results OUT NOCOPY IEU_DIAG_REQUEUED_NST
659 )AS
660 l_msg_count NUMBER(2);
661
662 l_msg_data VARCHAR2(2000);
663 l_ws_name varchar2(100) ;
664 prev_ws_name varchar2(100);
665 i integer;
666 j integer;
667 l_count integer;
668 l_from_date date ;
669 l_to_date DATE ;
670 l_date date;
671 owner_name_prev varchar2(2000);
672 assignee_name_prev varchar2(2000);
673 owner_name_curr varchar2(2000);
674 assignee_name_curr varchar2(2000);
675 l_results IEU_DIAG_NOTMEMBER_NST;
676 l_tmp number;
677 l_temp_count number;
678 work_item_number number;
679 title varchar2(2000);
680 cursor cur_items IS
681 Select a.workitem_pk_id, a.workitem_obj_code,
682 DECODE(a.workitem_STATUS_ID_curr,'0', 'Open', '3', 'Close', '4', 'Delete', '5', 'Sleep') status,
683 a.owner_id_prev,a.owner_id_curr,a.owner_type_prev,a.owner_type_curr,
684 a.assignee_id_prev,a.assignee_id_curr,a.assignee_type_prev,a.assignee_type_curr,
685 ws_code
686 from ieu_uwqm_audit_log a
687 Where a.creation_date between p_from_date and p_to_date
688 And ( (a.owner_type_prev <> 'RS_GROUP')
689 and (a.owner_type_curr = 'RS_GROUP')
690 and (a.assignee_type_curr is null)
691 )
692 OR
693 ( (a.owner_type_prev = 'RS_GROUP')
694 and (a.owner_type_curr = 'RS_GROUP')
695 and (a.assignee_type_curr is null)
696 and (a.owner_id_prev <> a.owner_id_curr)
697 )
698 OR
699 ( (a.assignee_type_prev is not null)
700 and (a.assignee_type_curr is null)
701 and (a.owner_type_curr = 'RS_GROUP')
702 )
703 ORDER BY a.creation_date;
704
705
706
707
708 BEGIN
709 owner_name_prev :='';
710 assignee_name_prev :='';
711 l_temp_count :=0;
712 work_item_number :=0;
713 l_ws_name := 'ws_name';
714 prev_ws_name := 'ws_name';
715 title := '';
716 i := 0;
717 l_count :=0;
718 j :=0;
719 l_tmp :=0;
720 --dbms_output.put_line('begin');
721 fnd_msg_pub.delete_msg();
722 x_return_status := fnd_api.g_ret_sts_success;
723 x_msg_data := 'begin--> from '||p_from_date||' to '|| p_to_date;
724 FND_MSG_PUB.initialize;
725 x_results := IEU_DIAG_REQUEUED_NST();
726
727 FOR cur_rec IN cur_items
728 LOOP
729 --dbms_output.put_line('in the loop of cur_rec');
730 i := i+1;
731
732 x_results.EXTEND(1);
733 -- dbms_output.put_line('extended');
734 owner_name_prev :='';
735 assignee_name_prev :='';
736 owner_name_curr :='';
737 assignee_name_curr :='';
738 title :='';
739 l_ws_name := '';
740
741
742 begin
743 select title into title
744 from ieu_uwqm_items where workitem_pk_id = cur_rec.workitem_pk_id
745 and workitem_obj_code=cur_rec.workitem_obj_code;
746 exception
747 when no_data_found then null;
748 end;
749 if cur_rec.owner_id_prev is not null then
750 if cur_rec.owner_type_prev = 'RS_GROUP' then
751 begin
752 select group_name into owner_name_prev
753 from jtf_rs_groups_tl
754 where group_id = cur_rec.owner_id_prev and language = FND_GLOBAL.CURRENT_LANGUAGE;
755 exception
756 when no_data_found then null;
757 end;
758 else
759 begin
760 select resource_name into owner_name_prev
761 from JTF_RS_RESOURCE_EXTNS_vl
762 where resource_id = cur_rec.owner_id_prev;
763 exception
764 when no_data_found then null;
765 end;
766 end if;
767 end if;
768
769 if cur_rec.assignee_id_prev is not null then
770 if cur_rec.assignee_type_prev = 'RS_INDIVIDUAL' then
771 begin
772 select resource_name into assignee_name_prev
773 from JTF_RS_RESOURCE_EXTNS_vl
774 where resource_id = cur_rec.assignee_id_prev;
775 exception
776 when no_data_found then null;
777 end;
778 else
779 begin
780 select group_name into assignee_name_prev
781 from jtf_rs_groups_tl
782 where group_id = cur_rec.assignee_id_prev and language = FND_GLOBAL.CURRENT_LANGUAGE;
783 exception
784 when no_data_found then null;
785 end;
786 end if;
787 end if;
788 if cur_rec.owner_id_curr is not null then
789 if cur_rec.owner_type_curr = 'RS_GROUP' then
790 begin
791 select group_name into owner_name_curr
792 from jtf_rs_groups_tl
793 where group_id = cur_rec.owner_id_curr and language = FND_GLOBAL.CURRENT_LANGUAGE;
794 exception
795 when no_data_found then null;
796 end;
797 else
798 begin
799 select resource_name into owner_name_curr
800 from JTF_RS_RESOURCE_EXTNS_vl
801 where resource_id = cur_rec.owner_id_curr;
802 exception
803 when no_data_found then null;
804 end;
805 end if;
806 end if;
807
808 if cur_rec.assignee_id_curr is not null then
809 if cur_rec.assignee_type_curr = 'RS_INDIVIDUAL' then
810 begin
811 select resource_name into assignee_name_curr
812 from JTF_RS_RESOURCE_EXTNS_vl
813 where resource_id = cur_rec.assignee_id_curr;
814 exception
815 when no_data_found then null;
816 end;
817 else
818 begin
819 select group_name into assignee_name_curr
820 from jtf_rs_groups_tl
821 where group_id = cur_rec.assignee_id_curr and language = FND_GLOBAL.CURRENT_LANGUAGE;
822 exception
823 when no_data_found then null;
824 end;
825 end if;
826 end if;
827
828 if cur_rec.ws_code is not null then
829 begin
830 select ws_name into l_ws_name
831 from ieu_uwqm_work_sources_tl tl, ieu_uwqm_work_sources_b b
832 where b.ws_id = tl.ws_id
833 and tl.language = FND_GLOBAL.CURRENT_LANGUAGE
834 and b.ws_code=cur_rec.ws_code;
835 exception
836 when no_data_found then null;
837 end;
838 end if;
839
840 --dbms_output.put_line('extened');
841 x_results(x_results.last) :=IEU_DIAG_REQUEUED_OBJ(cur_rec.workitem_pk_id,
842 title,
843 cur_rec.status,
844 cur_rec.owner_id_prev,
845 owner_name_prev,
846 cur_rec.owner_id_curr,
847 owner_name_curr,
848 cur_rec.assignee_id_prev,
849 assignee_name_prev,
850 cur_rec.assignee_id_curr,
851 assignee_name_curr,
852 l_ws_name
853 );
854 end LOOP;
855 EXCEPTION
856
857 WHEN FND_API.G_EXC_ERROR THEN
858 x_return_status := FND_API.G_RET_STS_ERROR;
859 x_msg_data := sqlerrm;
860 x_msg_count := fnd_msg_pub.COUNT_MSG();
861 FOR i in 1..x_msg_count LOOP
862 l_msg_data := '';
863 l_msg_count := 0;
864 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
865 x_msg_data := x_msg_data || ',' || l_msg_data;
866 END LOOP;
867 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
868 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
869 x_msg_data := sqlerrm;
870 x_msg_count := fnd_msg_pub.COUNT_MSG();
871 FOR i in 1..x_msg_count LOOP
872 l_msg_data := '';
873 l_msg_count := 0;
874 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
875 x_msg_data := x_msg_data || ',' || l_msg_data;
876 END LOOP;
877
878
879 WHEN OTHERS THEN
880 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
881 x_msg_data := sqlerrm;
882 x_msg_count := fnd_msg_pub.COUNT_MSG();
883 FOR i in 1..x_msg_count LOOP
884 l_msg_data := '';
885 l_msg_count := 0;
886 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
887 x_msg_data := x_msg_data || ',' || l_msg_data;
888 END LOOP;
889
890 end getRequeued;
891
892 PROCEDURE getDistributing ( x_return_status OUT NOCOPY VARCHAR2,
893 x_msg_count OUT NOCOPY NUMBER,
894 x_msg_data OUT NOCOPY VARCHAR2,
895 p_group_name IN varchar2,
896 p_from_date IN DATE, -- format : 10-JAN-04
897 p_to_date IN DATE,
898 x_results OUT NOCOPY IEU_DIAG_DISTRIBUTING_NST)
899 AS
900 l_msg_count NUMBER(2);
901
902 l_msg_data VARCHAR2(2000);
903 prev_ws_name varchar2(100);
904 i integer;
905 j integer;
906 l_count integer;
907 l_from_date date ;
908 l_to_date DATE ;
909 l_date date;
910 owner_name varchar2(2000);
911 assignee_name varchar2(2000);
912 priority varchar2(2000);
913
914 l_results IEU_DIAG_NOTMEMBER_NST;
915 l_tmp number;
916 l_temp_count number;
917 work_item_number number;
918 l_ws_name varchar2(2000);
919 l_ws_code varchar2(2000);
920 cursor cur_items IS
921 Select a.workitem_pk_id,
922 a.title,
923 DECODE(a.STATUS_ID,'0', 'Open', '3', 'Close', '4', 'Delete', '5', 'Sleep') status,
924 a.priority_id,
925 a.due_date,
926 a.reschedule_time,
927 a.OWNER_ID,
928 a.owner_type,
929 a.ASSIGNEE_ID,
930 a.assignee_type,
931 a.ws_id
932 from ieu_uwqm_items a
933 Where a.DISTRIBUTION_STATUS_ID = 2
934 And a.owner_type = 'RS_GROUP'
935 And a.owner_id IN (select group_id FROM jtf_rs_groups_vl WHERE lower(GROUP_name) = lower(p_group_name))
936 and a.creation_date BETWEEN p_from_date AND p_to_date
937 ORDER BY a.title;
938
939
940 BEGIN
941 owner_name :='';
942 assignee_name :='';
943 priority :='';
944 l_temp_count :=0;
945 work_item_number :=0;
946 l_ws_name := 'ws_name'; prev_ws_name := 'ws_name';
947 i := 0;
948 l_count :=0;
949 j :=0;
950 l_tmp :=0;
951 --dbms_output.put_line('begin');
952 fnd_msg_pub.delete_msg();
953 x_return_status := fnd_api.g_ret_sts_success;
954 x_msg_data := 'begin--> from '||p_from_date||' to '|| p_to_date;
955 FND_MSG_PUB.initialize;
956 x_results := IEU_DIAG_DISTRIBUTING_NST();
957 FOR cur_rec IN cur_items
958 LOOP
959 --dbms_output.put_line('in the loop of cur_rec');
960 i := i+1;
961
962 x_results.EXTEND(1);
963 -- dbms_output.put_line('extended');
964 owner_name :='';
965 assignee_name :='';
966 priority :='';
967 l_ws_code :='';
968 l_ws_name :='';
969
970 if cur_rec.owner_id is not null then
971 if cur_rec.owner_type = 'RS_GROUP' then
972 begin
973 select group_name into owner_name
974 from jtf_rs_groups_tl
975 where group_id = cur_rec.owner_id and language = FND_GLOBAL.CURRENT_LANGUAGE;
976 exception
977 when no_data_found then null;
978 end;
979 else
980 begin
981 select resource_name into owner_name
982 from JTF_RS_RESOURCE_EXTNS_vl
983 where resource_id = cur_rec.owner_id;
984 exception
985 when no_data_found then null;
986 end;
987 end if;
988 end if;
989
990 if cur_rec.assignee_id is not null then
991 if cur_rec.assignee_type = 'RS_INDIVIDUAL' then
992 begin
993 select resource_name into assignee_name
994 from JTF_RS_RESOURCE_EXTNS_vl
995 where resource_id = cur_rec.assignee_id;
996 exception
997 when no_data_found then null;
998 end;
999 else
1000 begin
1001 select group_name into assignee_name
1002 from jtf_rs_groups_tl
1003 where group_id = cur_rec.assignee_id and language = FND_GLOBAL.CURRENT_LANGUAGE;
1004 exception
1005 when no_data_found then null;
1006 end;
1007 end if;
1008 end if;
1009
1010
1011
1012 if cur_rec.priority_id is not null then
1013 begin
1014 select name into priority
1015 from ieu_uwqm_priorities_tl
1016 where priority_id = cur_rec.priority_id
1017 and language = FND_GLOBAL.CURRENT_LANGUAGE;
1018 exception
1019 when no_data_found then null;
1020 end;
1021 end if;
1022
1023
1024 if cur_rec.ws_id is not null then
1025 begin
1026 select ws_name into l_ws_name
1027 from ieu_uwqm_work_sources_tl
1028 where ws_id = cur_rec.ws_id
1029 and language = FND_GLOBAL.CURRENT_LANGUAGE;
1030 exception
1031 when no_data_found then null;
1032 end;
1033 end if;
1034
1035 --dbms_output.put_line('extened');
1036 x_results(x_results.last) :=IEU_DIAG_DISTRIBUTING_OBJ(cur_rec.workitem_pk_id,
1037 cur_rec.title,
1038 cur_rec.status,
1039 priority,
1040 cur_rec.due_date,
1041 cur_rec.reschedule_time,
1042 cur_rec.owner_id,
1043 owner_name,
1044 cur_rec.assignee_id,
1045 assignee_name,
1046 l_ws_name
1047 );
1048 end LOOP;
1049 EXCEPTION
1050
1051 WHEN FND_API.G_EXC_ERROR THEN
1052 x_return_status := FND_API.G_RET_STS_ERROR;
1053 x_msg_data := sqlerrm;
1054 x_msg_count := fnd_msg_pub.COUNT_MSG();
1055 FOR i in 1..x_msg_count LOOP
1056 l_msg_data := '';
1057 l_msg_count := 0;
1058 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1059 x_msg_data := x_msg_data || ',' || l_msg_data;
1060 END LOOP;
1061 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1062 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1063 x_msg_data := sqlerrm;
1064 x_msg_count := fnd_msg_pub.COUNT_MSG();
1065 FOR i in 1..x_msg_count LOOP
1066 l_msg_data := '';
1067 l_msg_count := 0;
1068 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1069 x_msg_data := x_msg_data || ',' || l_msg_data;
1070 END LOOP;
1071
1072
1073 WHEN OTHERS THEN
1074 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1075 x_msg_data := sqlerrm;
1076 x_msg_count := fnd_msg_pub.COUNT_MSG();
1077 FOR i in 1..x_msg_count LOOP
1078 l_msg_data := '';
1079 l_msg_count := 0;
1080 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1081 x_msg_data := x_msg_data || ',' || l_msg_data;
1082 END LOOP;
1083
1084 end getDistributing;
1085
1086 PROCEDURE getNotMember ( x_return_status OUT NOCOPY VARCHAR2,
1087 x_msg_count OUT NOCOPY NUMBER,
1088 x_msg_data OUT NOCOPY VARCHAR2,
1089 p_from_date IN DATE, -- format : 10-JAN-04
1090 p_to_date IN DATE,
1091 x_groups OUT NOCOPY IEU_DIAG_GROUP_NST,
1092 x_results OUT NOCOPY IEU_DIAG_NOTMEMBER_NST
1093 )AS
1094 l_msg_count NUMBER(2);
1095
1096 l_msg_data VARCHAR2(2000);
1097 l_ws_name varchar2(100) ;
1098 l_ws_code varchar2(100) ;
1099 prev_ws_name varchar2(100);
1100 i integer;
1101 j integer;
1102 l_count integer;
1103 l_from_date date ;
1104 l_to_date DATE ;
1105 l_date date;
1106
1107 l_results IEU_DIAG_NOTMEMBER_NST;
1108 l_tmp number;
1109 l_temp_count number;
1110 work_item_number number;
1111 cursor cur_items IS
1112 select distinct a.workitem_pk_id, a.MODULE ,
1113 DECODE(a.WORKITEM_STATUS_ID_CURR, '0', 'Open', '3', 'Close', '4', 'Delete', '5', 'Sleep') WORKITEM_STATUS_ID_CURR,
1114 DECODE(a.WORKITEM_DIST_STATUS_ID_CURR, '0', 'On Hold', '1', 'Distributable',
1115 '2', 'Distributing', '3', 'Distributed') WORKITEM_DIST_STATUS_ID_CURR ,
1116 a.workitem_obj_code,a.OWNER_ID_CURR, rs1.group_name owner_name,
1117 a.ASSIGNEE_ID_CURR, rs2.resource_name assignee_name, a.ws_code, a.work_item_number, b.ws_id, tl.ws_name ws_name
1118 FROM ieu_uwqm_audit_log a, ieu_uwqm_work_sources_b b, ieu_uwqm_work_sources_tl tl,
1119 jtf_rs_groups_vl rs1, JTF_RS_RESOURCE_EXTNS_vl rs2
1120 WHERE a.owner_type_curr = 'RS_GROUP'
1121 and a.assignee_type_curr = 'RS_INDIVIDUAL'
1122 AND a.owner_id_curr = rs1.group_id(+)
1123 AND a.assignee_id_curr = rs2.resource_id(+)
1124 and a.workitem_obj_code = b.object_code
1125 and b.ws_id = tl.ws_id
1126 and tl.language = FND_GLOBAL.CURRENT_LANGUAGE
1127 AND not exists
1128 (select 1 from jtf_rs_group_members
1129 where group_id = a.owner_id_curr
1130 and resource_id = a.assignee_id_curr
1131 and nvl(delete_flag, 'N') = 'N')
1132 and a.creation_date BETWEEN p_from_date AND p_to_date
1133 ORDER BY a.workitem_obj_code;
1134
1135
1136 BEGIN
1137 l_from_date := add_months(sysdate, -1 * 2 );
1138 l_to_date := add_months(sysdate, 1* 2 );
1139 l_temp_count :=0;
1140 work_item_number :=0;
1141 l_ws_name := 'ws_name';
1142 prev_ws_name := 'ws_name';
1143 i := 0;
1144 l_count :=0;
1145 j :=0;
1146 l_tmp :=0;
1147 --dbms_output.put_line('begin');
1148 fnd_msg_pub.delete_msg();
1149 x_return_status := fnd_api.g_ret_sts_success;
1150 x_msg_data := 'begin--> from '||p_from_date||' to '|| p_to_date;
1151 FND_MSG_PUB.initialize;
1152 x_results := IEU_DIAG_NOTMEMBER_NST();
1153 x_groups := IEU_DIAG_GROUP_NST();
1154 SELECT count(*) INTO l_tmp FROM ( select distinct a.workitem_pk_id, a.MODULE ,
1155 DECODE(a.WORKITEM_STATUS_ID_CURR,'0', 'Not Distributable', '1', 'Distributable',
1156 '2', 'Distributing', '3', 'Distributed') WORKITEM_STATUS_ID_CURR,
1157 DECODE(a.WORKITEM_DIST_STATUS_ID_CURR, '0', 'Not Distributable', '1', 'Distributable',
1158 '2', 'Distributing', '3', 'Distributed') WORKITEM_DIST_STATUS_ID_CURR ,
1159 a.workitem_obj_code,a.OWNER_ID_CURR, rs1.group_name owner_name,
1160 a.ASSIGNEE_ID_CURR, rs2.resource_name assignee_name, b.ws_id, tl.ws_name ws_name,
1161 a.work_item_number
1162 FROM ieu_uwqm_audit_log a, ieu_uwqm_work_sources_b b, ieu_uwqm_work_sources_tl tl,
1163 jtf_rs_groups_tl rs1, JTF_RS_RESOURCE_EXTNS_vl rs2
1164 WHERE a.owner_type_curr = 'RS_GROUP'
1165 and a.assignee_type_curr = 'RS_INDIVIDUAL'
1166 and a.workitem_obj_code = b.object_code
1167 and b.ws_id = tl.ws_id
1168 AND a.owner_id_curr = rs1.group_id(+)
1169 AND a.assignee_id_curr = rs2.resource_id(+)
1170 and rs1.language=FND_GLOBAL.CURRENT_LANGUAGE
1171 AND not exists
1172 (select 1 from jtf_rs_group_members
1173 where group_id = a.owner_id_curr
1174 and resource_id = a.assignee_id_curr
1175 and nvl(delete_flag, 'N') = 'N')
1176 and a.creation_date BETWEEN p_from_date AND p_to_date
1177 ORDER BY a.workitem_obj_code);
1178 x_msg_data := x_msg_data || 'get count is '||l_tmp;
1179 FOR cur_rec IN cur_items
1180 LOOP
1181 x_msg_data := x_msg_data || ' in the loop of cur_rec.';
1182 --dbms_output.put_line('in the loop of cur_rec');
1183 i := i+1;
1184 l_ws_name :='';
1185
1186 x_results.EXTEND(1);
1187 -- dbms_output.put_line('extended');
1188 x_msg_data := x_msg_data || 'extened';
1189 --dbms_output.put_line('extened');
1190 /* select ws_name into l_ws_name
1191 from ieu_uwqm_work_sources_tl tl, ieu_uwqm_work_sources_b b
1192 where b.ws_id = tl.ws_id
1193 and tl.language = FND_GLOBAL.CURRENT_LANGUAGE
1194 and b.ws_code=cur_rec.ws_code;
1195 */
1196 x_results(x_results.last) :=IEU_DIAG_NOTMEMBER_OBJ(cur_rec.workitem_pk_id,
1197 cur_rec.workitem_obj_code,
1198 cur_rec.WORKITEM_STATUS_ID_CURR ,
1199 cur_rec.owner_name,
1200 cur_rec.assignee_name,
1201 cur_rec.WORKITEM_DIST_STATUS_ID_CURR,
1202 cur_rec.MODULE,
1203 cur_rec.ws_name,
1204 cur_rec.work_item_number
1205 );
1206 -- dbms_output.put_line('id-->'||i||'....)-'||cur_rec.enum_id);
1207 --dbms_output.put_line('ws name-->'||cur_rec.workitem_obj_code);
1208 x_msg_data := x_msg_data || ' primary key is '||cur_rec.workitem_obj_code;
1209 -- dbms_output.put_line('ws name-->'||cur_rec.ws_name);
1210 -- x_msg_data := x_msg_data || ' primary key is '||cur_rec.ws_name;
1211 prev_ws_name := l_ws_name;
1212 l_ws_name := cur_rec.ws_name;
1213 IF (l_ws_name <> prev_ws_name and l_count > 0) THEN
1214 -- start new work source
1215 --dbms_output.put_line('l_ws_name is '|| l_ws_name||', cur_rec.ws_name is '||cur_rec.ws_name);
1216 j := j+1;
1217 x_groups.extend(1);
1218 x_groups(x_groups.last) := IEU_DIAG_GROUP_OBJ(l_count, l_ws_name);
1219 -- x_msg_data := x_msg_data || ' work source name is '||cur_rec.ws_name;
1220 l_count := 0;
1221 END IF ;
1222 l_count := l_count+1;
1223 end LOOP;
1224 -- for last record
1225 IF (l_count > 0) then
1226 x_groups.extend(1);
1227 x_groups(x_groups.last) := IEU_DIAG_GROUP_OBJ(l_count, l_ws_name);
1228 x_msg_data := x_msg_data || ' , outside the loop of cur_rec. ';
1229 END if;
1230 --dbms_output.put_line('x_groups count is '||x_groups.count);
1231
1232
1233
1234 EXCEPTION
1235
1236 WHEN FND_API.G_EXC_ERROR THEN
1237
1238 --dbms_output.PUT_LINE('Error : '||sqlerrm);
1239
1240
1241 x_return_status := FND_API.G_RET_STS_ERROR;
1242 x_msg_data := sqlerrm;
1243
1244 x_msg_count := fnd_msg_pub.COUNT_MSG();
1245
1246 FOR i in 1..x_msg_count LOOP
1247 l_msg_data := '';
1248 l_msg_count := 0;
1249 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1250 x_msg_data := x_msg_data || ',' || l_msg_data;
1251 END LOOP;
1252
1253
1254
1255
1256 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1257
1258 --dbms_output.PUT_LINE('Error : '||sqlerrm);
1259
1260
1261 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1262 x_msg_data := sqlerrm;
1263
1264 x_msg_count := fnd_msg_pub.COUNT_MSG();
1265
1266 FOR i in 1..x_msg_count LOOP
1267 l_msg_data := '';
1268 l_msg_count := 0;
1269 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1270 x_msg_data := x_msg_data || ',' || l_msg_data;
1271 END LOOP;
1272
1273
1274 WHEN OTHERS THEN
1275 --Rollback to IEU_UWQ_MEDIA_TYPES_PVT;
1276 --dbms_output.PUT_LINE('Error : '||sqlerrm);
1277
1278 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1279
1280 x_msg_data := sqlerrm;
1281
1282 x_msg_count := fnd_msg_pub.COUNT_MSG();
1283
1284 FOR i in 1..x_msg_count LOOP
1285 l_msg_data := '';
1286 l_msg_count := 0;
1287 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1288 x_msg_data := x_msg_data || ',' || l_msg_data;
1289 END LOOP;
1290
1291 end getNotMember;
1292
1293 PROCEDURE getLifeCycle(x_return_status OUT NOCOPY VARCHAR2,
1294 x_msg_count OUT NOCOPY NUMBER,
1295 x_msg_data OUT NOCOPY VARCHAR2,
1296 p_object_code IN VARCHAR2,
1297 p_item_number IN varchar2,
1298 x_results OUT NOCOPY IEU_DIAG_WORKLIFE_NST
1299 )
1300 AS
1301
1302 ws_type vARCHAR2(2000);
1303 l_msg_count NUMBER(2);
1304 l_language VARCHAR2(2000);
1305 l_msg_data VARCHAR2(2000);
1306 i integer := 0;
1307 j integer := 1;
1308 my_message varchar2(4000);
1309 l_del_msg varchar2(4000);
1310 x_app_name varchar2(25);
1311 x_msg_name varchar2(1000);
1312
1313 current_m varchar2(4000) ;
1314 current_meaning varchar2(4000) ;
1315 meaning1 varchar2(4000);
1316 meaning2 varchar2(4000);
1317 meaning3 varchar2(4000);
1318 meaning4 varchar2(4000);
1319 meaning5 varchar2(4000);
1320 meaning6 varchar2(4000);
1321 meaning7 varchar2(4000);
1322 meaning8 varchar2(4000);
1323 meaning9 varchar2(4000);
1324 meaning10 varchar2(4000);
1325 return_status varchar2(4000) ;
1326 owner_name varchar2(200) ;
1327 assignee_name varchar2(200) ;
1328 action varchar2(200) ;
1329 event varchar2(200) ;
1330 l_temp_count NUMBER ;
1331 priority varchar2(200) ;
1332 title varchar2(4000) ;
1333 code varchar2(200) ;
1334 position number ;
1335 cursor c_items is
1336 select a.action_key,a.EVENT_KEY, a.MODULE ,
1337 DECODE(a.WORKITEM_STATUS_ID_CURR, '0', 'Open', '3', 'Close', '4', 'Delete', '5', 'Sleep') WORKITEM_STATUS_ID_CURR,
1338 a.owner_id_curr, a.OWNER_TYPE_CURR,
1339 a.assignee_id_curr,a.ASSIGNEE_TYPE_CURR,
1340 DECODE(a.PARENT_WORKITEM_STATUS_ID_CURR, '0', 'Open', '3', 'Close',
1341 '4', 'Delete', '5', 'Sleep') PARENT_WORKITEM_STATUS_ID_CURR,
1342 DECODE(a.PARENT_DIST_STATUS_ID_CURR ,'0', 'On Hold', '1', 'Distributable',
1343 '2', 'Distributing', '3', 'Distributed') PARENT_DIST_STATUS_ID_CURR,
1344 DECODE(a.WORKITEM_DIST_STATUS_ID_CURR, '0', 'On Hold', '1', 'Distributable',
1345 '2', 'Distributing', '3', 'Distributed') WORKITEM_DIST_STATUS_ID_CURR,
1346 priority_id_curr ,a.DUE_DATE_CURR ,a.RESCHEDULE_TIME_CURR,
1347 a.IEU_COMMENT_CODE1 m1,
1348 a.IEU_COMMENT_CODE2 m2,
1349 a.IEU_COMMENT_CODE3 m3,
1350 a.IEU_COMMENT_CODE4 m4,
1351 a.IEU_COMMENT_CODE5 m5,
1352 a.WORKITEM_COMMENT_CODE1 m6,
1353 a.WORKITEM_COMMENT_CODE2 m7,
1354 a.WORKITEM_COMMENT_CODE3 m8,
1355 a.WORKITEM_COMMENT_CODE4 m9,
1356 a.WORKITEM_COMMENT_CODE5 m10, a.LAST_UPDATE_DATE, a.workitem_pk_id,
1357 a.return_status, a.error_code, a.ws_code, a.source_object_id_curr, a.source_object_type_code_curr
1358 FROM ieu_uwqm_audit_log a
1359 where (a.work_item_number = p_item_number
1360 AND a.workitem_obj_code = p_object_code)
1361 or (a.SOURCE_OBJECT_ID_CURR = p_item_number
1362 and a.SOURCE_OBJECT_TYPE_CODE_CURR = p_object_code)
1363 order by a.audit_log_id,a.creation_date;
1364
1365 BEGIN
1366
1367 fnd_msg_pub.delete_msg();
1368 x_return_status := fnd_api.g_ret_sts_success;
1369 FND_MSG_PUB.initialize;
1370 x_results := IEU_DIAG_WORKLIFE_NST();
1371 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
1372
1373 current_m :='';
1374 current_meaning :='';
1375 meaning1 :='';
1376 meaning2 :='';
1377 meaning3 :='';
1378 meaning4 :='';
1379 meaning5 :='';
1380 meaning6 :='';
1381 meaning7 :='';
1382 meaning8 :='';
1383 meaning9 :='';
1384 meaning10:='';
1385 return_status :='';
1386 owner_name :='';
1387 assignee_name :='';
1388 action :='';
1389 event :='';
1390 l_temp_count :=0;
1391 priority :='';
1392 title :='';
1393 code :='';
1394 position :=0;
1395
1396 FOR cur_rec IN c_items
1397 LOOP
1398 --dbms_output.put_line('in the loop of '|| i);
1399 i := i+1;
1400 x_results.EXTEND(1);
1401
1402 -- dbms_output.put_line('extended');
1403 owner_name :=''; assignee_name :=''; action := ''; event := '';
1404 meaning1 := ''; meaning2 := ''; meaning3 := ''; meaning4 := ''; meaning5 :='';
1405 meaning6 := ''; meaning7 := ''; meaning8 := ''; meaning9 := ''; meaning10 :='';
1406 return_status := '';
1407 if cur_rec.owner_id_curr is not null then
1408 if cur_rec.owner_type_curr = 'RS_GROUP' then
1409 begin
1410 select group_name into owner_name
1411 from jtf_rs_groups_tl
1412 where group_id = cur_rec.owner_id_curr and language = l_language;
1413 exception
1414 when no_data_found then null;
1415 end;
1416 else
1417 begin
1418 select resource_name into owner_name
1419 from JTF_RS_RESOURCE_EXTNS_vl
1420 where resource_id = cur_rec.owner_id_curr;
1421 exception
1422 when no_data_found then null;
1423 end;
1424 end if;
1425 end if;
1426
1427 if cur_rec.assignee_id_curr is not null then
1428 if cur_rec.assignee_type_curr = 'RS_INDIVIDUAL' then
1429 begin
1430 select resource_name into assignee_name
1431 from JTF_RS_RESOURCE_EXTNS_vl
1432 where resource_id = cur_rec.assignee_id_curr;
1433 exception
1434 when no_data_found then null;
1435 end;
1436 else
1437 begin
1438 select group_name into assignee_name
1439 from jtf_rs_groups_tl
1440 where group_id = cur_rec.assignee_id_curr and language = l_language;
1441 exception
1442 when no_data_found then null;
1443 end;
1444 end if;
1445 end if;
1446
1447 if cur_rec.action_key is not null then
1448 begin
1449 select meaning into action
1450 from ieu_lookups where lookup_type = 'IEU_WR_AUDIT_LOG_RULES' and lookup_code = cur_rec.action_key;
1451 exception
1452 when no_data_found then action := cur_rec.action_key;
1453 end;
1454 if (cur_rec.SOURCE_OBJECT_ID_CURR = p_item_number
1455 and cur_rec.SOURCE_OBJECT_TYPE_CODE_CURR = p_object_code) then
1456 begin
1457 select ws_type into ws_type
1458 from ieu_uwqm_work_sources_b where ws_code = cur_rec.ws_code;
1459 exception
1460 when no_data_found then null;
1461 end;
1462 if ws_type = 'ASSOCIATION' then
1463 action := action || '<br>(' || cur_rec.ws_code || ')';
1464 end if;
1465 end if;
1466 end if;
1467
1468 if cur_rec.event_key is not null then
1469 begin
1470 select meaning into event
1471 from ieu_lookups where lookup_type = 'IEU_WR_AUDIT_LOG_RULES' and lookup_code = cur_rec.event_key;
1472 exception
1473 when no_data_found then event := cur_rec.event_key;
1474 end;
1475 end if;
1476
1477
1478 begin
1479 select title into title
1480 from ieu_uwqm_items where workitem_pk_id = cur_rec.workitem_pk_id
1481 and workitem_obj_code=p_object_code;
1482 exception
1483 when no_data_found then null;
1484 end;
1485
1486 if cur_rec.priority_id_curr is not null then
1487 begin
1488 select name into priority
1489 from ieu_uwqm_priorities_tl
1490 where priority_id = cur_rec.priority_id_curr
1491 and language = l_language;
1492 exception
1493 when no_data_found then null;
1494 end;
1495 end if;
1496
1497 --dbms_output.put_line('m1 is '|| cur_rec.m1);
1498
1499 for j in 1..10 loop
1500 if j=1 then current_m := cur_rec.m1; end if;
1501 if j=2 then current_m := cur_rec.m2; end if;
1502 if j=3 then current_m := cur_rec.m3; end if;
1503 if j=4 then current_m := cur_rec.m4; end if;
1504 if j=5 then current_m := cur_rec.m5; end if;
1505 if j=6 then current_m := cur_rec.m6; end if;
1506 if j=7 then current_m := cur_rec.m7; end if;
1507 if j=8 then current_m := cur_rec.m8; end if;
1508 if j=9 then current_m := cur_rec.m9; end if;
1509 if j=10 then current_m := cur_rec.m10; end if;
1510 if current_m is not null then
1511 position := instr(current_m, ' ', 1,1);
1512 if position > 0 then
1513 code := substr(current_m, 1, position-1);
1514 else
1515 code := current_m;
1516 end if ;
1517 current_meaning :='';
1518 begin
1519 select meaning into current_meaning
1520 from ieu_lookups where lookup_type = 'IEU_WR_AUDIT_LOG_RULES' and lookup_code = code;
1521 exception
1522 when no_data_found then current_meaning :='';
1523 end;
1524 if position > 0 then
1525 current_meaning := current_meaning ||' : '|| substr(current_m, position+1, length(current_m));
1526 end if ;
1527 if j=1 then meaning1 := current_meaning; end if;
1528 if j=2 then meaning2 := current_meaning; end if;
1529 if j=3 then meaning3 := current_meaning; end if;
1530 if j=4 then meaning4 := current_meaning; end if;
1531 if j=5 then meaning5 := current_meaning; end if;
1532 if j=6 then meaning6 := current_meaning; end if;
1533 if j=7 then meaning7 := current_meaning; end if;
1534 if j=8 then meaning8 := current_meaning; end if;
1535 if j=9 then meaning9 := current_meaning; end if;
1536 if j=10 then meaning10 := current_meaning; end if;
1537 end if;
1538 end loop;
1539
1540
1541 if cur_rec.return_status = 'E' then
1542 fnd_msg_pub.reset;
1543 fnd_msg_pub.initialize;
1544 fnd_message.parse_encoded(cur_rec.error_code, x_app_name, x_msg_name);
1545 fnd_message.set_encoded(cur_rec.error_code);
1546 fnd_msg_pub.add;
1547 fnd_msg_pub.Count_and_Get
1548 (
1549 p_encoded => 'F',
1550 p_count => l_msg_count,
1551 p_data => cur_rec.error_code
1552 );
1553 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_LAU_FAIL');
1554 return_status := FND_MESSAGE.GET();
1555 FOR l_index IN 1..l_msg_count LOOP
1556 my_message := FND_MSG_PUB.Get(p_msg_index => l_index,p_encoded => 'F');
1557 if my_message is not null then
1558 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_LAU_NEXT_LINE');
1559 return_status := return_status || FND_MESSAGE.get()|| my_message ;
1560 end if;
1561 --dbms_output.put_line(l_index || ' = ' || my_message);
1562 --insert into p_temp(msg) values(l_index || ' = ' || my_message); commit;
1563 END LOOP;
1564 fnd_msg_pub.set_search_name(x_app_name, x_msg_name);
1565 l_del_msg := fnd_msg_pub.delete_msg;
1566 end if ;
1567
1568
1569 x_results(x_results.last) := IEU_DIAG_WORKLIFE_OBJ(
1570 event,
1571 cur_rec.LAST_UPDATE_DATE,
1572 cur_rec.MODULE,
1573 cur_rec.WORKITEM_STATUS_ID_CURR ,
1574 owner_name,
1575 cur_rec.OWNER_ID_CURR,
1576 ASSIGNEE_name,
1577 cur_rec.ASSIGNEE_ID_CURR,
1578 cur_rec.PARENT_WORKITEM_STATUS_ID_CURR,
1579 cur_rec.PARENT_DIST_STATUS_ID_CURR,
1580 cur_rec.WORKITEM_DIST_STATUS_ID_CURR,
1581 priority,
1582 cur_rec.DUE_DATE_CURR,
1583 cur_rec.RESCHEDULE_TIME_CURR,
1584 action,
1585 title,
1586 cur_rec.workitem_pk_id,
1587 meaning1,
1588 meaning2,
1589 meaning3,
1590 meaning4,
1591 meaning5,
1592 meaning6,
1593 meaning7,
1594 meaning8,
1595 meaning9,
1596 meaning10,
1597 return_status
1598 );
1599 -- dbms_output.put_line('id-->'||i||'....)-'||cur_rec.enum_id);
1600 --dbms_output.put_line('name-->'||cur_rec.node_name);
1601
1602 end LOOP;
1603 EXCEPTION
1604
1605 WHEN FND_API.G_EXC_ERROR THEN
1606
1607 --dbms_output.PUT_LINE('Error : '||sqlerrm);
1608
1609
1610 x_return_status := FND_API.G_RET_STS_ERROR;
1611 x_msg_data := sqlerrm;
1612
1613 x_msg_count := fnd_msg_pub.COUNT_MSG();
1614
1615 FOR i in 1..x_msg_count LOOP
1616 l_msg_data := '';
1617 l_msg_count := 0;
1618 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1619 x_msg_data := x_msg_data || ',' || l_msg_data;
1620 END LOOP;
1621
1622
1623
1624
1625 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1626
1627 --dbms_output.PUT_LINE('Error : '||sqlerrm);
1628
1629
1630 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1631 x_msg_data := sqlerrm;
1632
1633 x_msg_count := fnd_msg_pub.COUNT_MSG();
1634
1635 FOR i in 1..x_msg_count LOOP
1636 l_msg_data := '';
1637 l_msg_count := 0;
1638 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1639 x_msg_data := x_msg_data || ',' || l_msg_data;
1640 END LOOP;
1641
1642
1643 WHEN OTHERS THEN
1644 --Rollback to IEU_UWQ_MEDIA_TYPES_PVT;
1645 --dbms_output.PUT_LINE('Error : '||sqlerrm);
1646
1647 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1648
1649 x_msg_data := sqlerrm;
1650
1651 x_msg_count := fnd_msg_pub.COUNT_MSG();
1652
1653 FOR i in 1..x_msg_count LOOP
1654 l_msg_data := '';
1655 l_msg_count := 0;
1656 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1657 x_msg_data := x_msg_data || ',' || l_msg_data;
1658 END LOOP;
1659
1660
1661 end getLifeCycle;
1662
1663
1664 END IEU_DIAG_AUDIT_TRACK_PVT;