[Home] [Help]
PACKAGE BODY: APPS.IEU_UWQ_MY_TASKS_ENUMS_PVT
Source
1 PACKAGE BODY IEU_UWQ_MY_TASKS_ENUMS_PVT AS
2 /* $Header: IEUEMTOB.pls 120.0 2005/06/02 15:48:31 appldev noship $ */
3
4 -- Sub-Program Units
5
6
7 PROCEDURE ENUMERATE_MY_TASKS_OWN_NODES
8 (P_RESOURCE_ID IN NUMBER
9 ,P_LANGUAGE IN VARCHAR2
10 ,P_SOURCE_LANG IN VARCHAR2
11 ,P_SEL_ENUM_ID IN NUMBER
12 )
13 AS
14
15 l_node_counter NUMBER;
16 l_node_pid NUMBER;
17 l_node_label VARCHAR2(100);
18 l_open_flag VARCHAR2(1);
19 l_owner_type_code1 VARCHAR2(10);
20 l_owner_type_code2 VARCHAR2(10);
21 l_entity VARCHAR2(10);
22 l_deleted_flag VARCHAR2(1);
23 l_lookup_type VARCHAR2(50);
24 l_application_id NUMBER(4);
25 l_lookup_code VARCHAR2(50);
26
27 l_tk_list IEU_PUB.EnumeratorDataRecordList;
28
29 CURSOR c_task_nodes_1 IS
30
31 /* New Query for Task types from Performance Team - 03/04/03 */
32
33 SELECT TYPES.TASK_TYPE_ID TASK_TYPE_ID,TYPES.NAME TASK_TYPE
34 FROM JTF_TASK_TYPES_TL TYPES
35 WHERE TYPES.LANGUAGE = USERENV('LANG')
36 AND EXISTS (
37 SELECT 1
38 FROM JTF_TASKS_B TASKS
39 WHERE tasks.open_flag = l_open_flag
40 AND TASKS.OWNER_ID = P_RESOURCE_ID
41 AND TASKS.OWNER_TYPE_CODE NOT IN ( l_owner_type_code1, l_owner_type_code2 )
42 AND TASKS.ENTITY = l_entity
43 AND NVL(TASKS.DELETED_FLAG,'N') = l_deleted_flag
44 AND TASKS.TASK_TYPE_ID =TYPES.TASK_TYPE_ID )
45 ORDER BY 2;
46
47
48 CURSOR c_task_nodes_2 IS
49 SELECT
50 task_type_id, name task_type
51 FROM
52 jtf_task_types_vl
53 WHERE
54 trunc(sysdate) between trunc(nvl(start_date_active, sysdate))
55 and trunc(nvl(end_date_active, sysdate))
56 ORDER BY 2;
57
58 BEGIN
59
60 l_open_flag := 'Y';
61 l_owner_type_code1 := 'RS_GROUP';
62 l_owner_type_code2 := 'RS_TEAM';
63 l_entity := 'TASK';
64 l_deleted_flag := 'N';
65 l_lookup_type := 'IEU_NODE_LABELS';
66 l_application_id := 696;
67 l_lookup_code := 'IEU_TASKS_MYOWN_LBL';
68 l_node_counter := 0;
69
70 SAVEPOINT start_enumeration;
71 -- dbms_output.put_line('in my tasks enum proc);
72
73 /* label, view, and where for main node taken from enum table anyway */
74
75 Select meaning into l_node_label
76 from fnd_lookup_values_vl
77 where lookup_type = l_lookup_type
78 and view_application_id = l_application_id
79 and lookup_code = l_lookup_code;
80
81 l_tk_list(l_node_counter).NODE_LABEL := l_node_label;
82 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_MYOWN_V';
83 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_MYOWN_DS';
84 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
85 l_tk_list(l_node_counter).WHERE_CLAUSE := '';
86 l_tk_list(l_node_counter).NODE_TYPE := 0;
87 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
88 l_tk_list(l_node_counter).NODE_DEPTH := 1;
89 l_node_counter := l_node_counter + 1;
90
91 --Now build the subnodes
92 --Dbms_output.put_line('Node Label : '||l_node_label);
93
94 IF (FND_PROFILE.VALUE('IEU_TASK_TYPES') = 'Y') THEN
95 IF (FND_PROFILE.VALUE('IEU_CLI_UI_SHOW_ALL_NODES') = 'N') THEN
96
97 FOR cur_rec IN c_task_nodes_1 LOOP
98
99 l_tk_list(l_node_counter).NODE_LABEL := cur_rec.task_type;
100 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_MYOWN_V';
101 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_MYOWN_DS';
102 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
103 l_tk_list(l_node_counter).WHERE_CLAUSE := ' TASK_TYPE_ID = ' || cur_rec.task_type_id;
104 l_tk_list(l_node_counter).NODE_TYPE := 0;
105 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
106 l_tk_list(l_node_counter).NODE_DEPTH := 2;
107 l_node_counter := l_node_counter + 1;
108
109 -- Dbms_output.put_line('where clause : '||l_tk_list(l_node_counter).WHERE_CLAUSE);
110 -- insert into uwq_foo_test values ('where clause : '||l_tk_list(l_node_counter).WHERE_CLAUSE );
111
112
113 END LOOP;
114 ELSE
115
116 FOR cur_rec IN c_task_nodes_2 LOOP
117
118 l_tk_list(l_node_counter).NODE_LABEL := cur_rec.task_type;
119 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_MYOWN_V';
120 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_MYOWN_DS';
121 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
122 l_tk_list(l_node_counter).WHERE_CLAUSE := ' TASK_TYPE_ID = ' || cur_rec.task_type_id;
123 l_tk_list(l_node_counter).NODE_TYPE := 0;
124 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
125 l_tk_list(l_node_counter).NODE_DEPTH := 2;
126 l_node_counter := l_node_counter + 1;
127
128 -- Dbms_output.put_line('where clause : '||l_tk_list(l_node_counter).WHERE_CLAUSE);
129 -- insert into uwq_foo_test values ('where clause : '||l_tk_list(l_node_counter).WHERE_CLAUSE );
130
131 END LOOP;
132
133 END IF;
134 END IF; -- Task Types
135
136 -- Now add everything
137 IEU_PUB.ADD_UWQ_NODE_DATA
138 (P_RESOURCE_ID,
139 P_SEL_ENUM_ID,
140 l_tk_list
141 );
142
143 EXCEPTION
144 WHEN OTHERS THEN
145 --dbms_output.put_line('Exception in enumerate my tasks nodes'||sqlerrm);
146 ROLLBACK TO start_enumeration;
147 RAISE;
148
149 END ENUMERATE_MY_TASKS_OWN_NODES;
150
151
152 /* Used to refresh task nodes for Forms tree view. */
153 PROCEDURE REFRESH_MY_TASKS_OWN_NODES( P_RESOURCE_ID IN NUMBER, P_NODE_ID IN NUMBER, P_COUNT OUT NOCOPY NUMBER)
154 AS
155 l_count NUMBER:=0;
156 l_where_clause VARCHAR(1000);
157
158 l_sql_own_count varchar2(30000);
159
160 BEGIN
161 l_where_clause := '';
162 select where_clause
163 into l_where_clause
164 from ieu_uwq_sel_rt_nodes
165 where resource_id=P_RESOURCE_ID
166 and node_id=P_NODE_ID;
167
168 if (l_where_clause is NOT NULL) then
169 l_where_clause := ' and '|| l_where_clause;
170 end if;
171
172 BEGIN
173 -- Get count
174
175 l_sql_own_count :=
176 'begin select /*+ index(tasks jtf_tasks_b_n2) */ count(*) into :l_count from jtf_tasks_b tasks '||
177 'where '||
178 '( tasks.owner_id = :resource_id
179 and tasks.owner_type_code not in ('||''''||'RS_GROUP'||''''||','||''''||'RS_TEAM'||''''||') ) '||
180 'and exists
181 ( select task_status_id
182 from jtf_task_statuses_vl tsv
183 where tasks.open_flag = '||''''||'Y'||''''||
184 ' and tsv.task_status_id = tasks.task_status_id )
185 and tasks.entity = ' ||''''|| 'TASK' ||''''|| '
186 and nvl(tasks.deleted_flag,'||''''||'N'||''''||') = '||''''||'N'||''''||l_where_clause ||' ; end; ';
187
188 execute immediate l_sql_own_count
189 USING OUT l_count, IN p_resource_id;
190
191 EXCEPTION
192 WHEN OTHERS THEN
193
194 -- dbms_output.put_line(SQLCODE);
195 -- dbms_output.put_line(SQLERRM);
196 l_count := 0;
197 END;
198
199 IF (l_count IS NULL)
200 THEN
201 l_count := 0;
202 END IF;
203
204 P_COUNT:=l_count;
205
206 END REFRESH_MY_TASKS_OWN_NODES;
207
208 -- PL/SQL Block
209 END IEU_UWQ_MY_TASKS_ENUMS_PVT;