1 PACKAGE BODY IEU_UWQM_TASK_WL_MIG AS
2 /* $Header: IEUVTKPB.pls 120.2 2006/05/02 12:21:54 pkumble noship $ */
3
4 FUNCTION GET_TASKS_BY_PRIORITY
5 RETURN SYSTEM.IEU_UWQM_TASK_PRIORITY_NST AS
6
7 l_priority_list l_pty_list;
8
9 cursor l_tasks_cur is
10 select b.importance_level, count(a.task_id) cnt
11 from jtf_Tasks_b a, jtf_task_priorities_vl b
12 where a.task_priority_id = b.task_priority_id
13 and nvl(a.deleted_flag , 'N') = 'N'
14 and a.entity = 'TASK'
15 and nvl(a.open_flag, 'N') = 'Y'
16 and source_object_type_code <> 'SR'
17 group by importance_level
18 order by importance_level;
19
20 i number := 0;
21
22 l_null_pty_count Number;
23
24 l_return_list SYSTEM.IEU_UWQM_TASK_PRIORITY_NST;
25
26 begin
27
28 for ctr in l_tasks_cur loop
29 if ctr.importance_level <= 4
30 then
31 i := i + 1;
32 l_priority_list(i).importance_level := ctr.importance_level;
33 l_priority_list(i).task_count := ctr.cnt;
34 else
35 l_priority_list(i).importance_level := 4;
36 l_priority_list(i).task_count := l_priority_list(i).task_count + ctr.cnt;
37 end if;
38 end loop;
39
40 -- All Tasks with Null Priority Id will be included under importance level 4
41
42 select count(a.task_id)
43 into l_null_pty_count
44 from jtf_Tasks_b a
45 where a.task_priority_id is null
46 and nvl(a.deleted_flag, 'N') = 'N'
47 and a.entity = 'TASK'
48 and nvl(a.open_flag, 'N') = 'Y';
49
50 for ctr in l_priority_list.FIRST .. l_priority_list.last loop
51 if l_priority_list(ctr).importance_level = 4
52 then
53 l_priority_list(ctr).task_count := l_priority_list(ctr).task_count + l_null_pty_count;
54 end if;
55 end loop;
56
57 l_return_list := SYSTEM.IEU_UWQM_TASK_PRIORITY_NST();
58 for k in 1..l_priority_list.count loop
59 l_return_list.extend;
60 l_return_list(l_return_list.last) := SYSTEM.IEU_UWQM_TASK_PRIORITY_OBJ(l_priority_list(k).importance_level, l_priority_list(k).task_count);
61 end loop;
62
63 return l_return_list;
64
65 END GET_TASKS_BY_PRIORITY;
66
67 END IEU_UWQM_TASK_WL_MIG;
[Home] [Help]