[Home] [Help]
PACKAGE BODY: APPS.IEU_TASKS_ENUMS_PVT
Source
1 PACKAGE BODY IEU_TASKS_ENUMS_PVT AS
2 /* $Header: IEUENTNB.pls 120.1.12010000.3 2008/10/15 19:17:45 spamujul ship $ */
3
4 -- Sub-Program Units
5
6
7 PROCEDURE ENUMERATE_TASK_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_def_where VARCHAR2(20000);
19 l_sql_stmt VARCHAR2(2000);
20 l_lookup_type VARCHAR2(2000);
21 l_view_application_id VARCHAR2(2000);
22 l_lookup_code VARCHAR2(2000);
23
24 l_tk_list IEU_PUB.EnumeratorDataRecordList;
25 l_tk_bind_list IEU_PUB.BindVariableRecordList;
26 l_ind_own_tk_bind_list IEU_PUB.BindVariableRecordList;
27 l_ind_asg_tk_bind_list IEU_PUB.BindVariableRecordList;
28 l_grp_own_tk_bind_list IEU_PUB.BindVariableRecordList;
29 l_grp_asg_tk_bind_list IEU_PUB.BindVariableRecordList;
30 l_team_own_tk_bind_list IEU_PUB.BindVariableRecordList;
31 l_team_asg_tk_bind_list IEU_PUB.BindVariableRecordList;
32
33
34 -- 02/07/01 Type_id of 22 is 'Escalations' and Tasks team asked to eliminate these.
35
36
37 -- Owned By Me
38 CURSOR c_ind_own_task_nodes_1 IS
39 SELECT TYPES.TASK_TYPE_ID TASK_TYPE_ID,TYPES.NAME TASK_TYPE
40 FROM JTF_TASK_TYPES_TL TYPES
41 WHERE EXISTS (
42 SELECT 1
43 FROM JTF_TASKS_B TASKS
44 WHERE OPEN_FLAG = 'Y'
45 AND TASKS.OWNER_ID = p_resource_id
46 AND TASKS.OWNER_TYPE_CODE NOT IN ( 'RS_GROUP','RS_TEAM' )
47 AND TASKS.entity = 'TASK'
48 AND NVL(TASKS.DELETED_FLAG,'N') = 'N'
49 AND TASKS.TASK_TYPE_ID =TYPES.TASK_TYPE_ID
50 )
51 and types.language = userenv('lang')
52 ORDER BY 2;
53
54 -- Owned by Group
55 -- Begin fix for 7412700 by spamujul
56 -- Commented the Following code to improved the performance
57 -- CURSOR c_grp_own_task_nodes_1 IS
58 -- SELECT /*+ first_rows */ TYPES.TASK_TYPE_ID TASK_TYPE_ID,TYPES.NAME TASK_TYPE
59 -- FROM JTF_TASK_TYPES_TL TYPES
60 -- WHERE types.task_type_id in
61 -- (SELECT /*+ use_nl(tasks) */ task_type_id FROM JTF_TASKS_B TASKS
62 -- WHERE OPEN_FLAG = 'Y'
63 -- and TASKS.OWNER_TYPE_CODE= 'RS_GROUP'
64 -- AND exists (SELECT /*+ no_unnest index(m jtf_rs_group_members_n1) */ null
65 -- FROM JTF_RS_GROUP_MEMBERS m
66 -- WHERE RESOURCE_ID = p_resource_id
67 -- and GROUP_ID=TASKS.OWNER_ID
68 -- AND NVL(DELETE_FLAG,'N') <> 'Y' )
69 -- AND TASKS.entity = 'TASK'
70 -- AND NVL(TASKS.DELETED_FLAG,'N') = 'N'
71 -- )
72 -- and types.language = userenv('lang')
73 -- ORDER BY 2;
74 -- End fix for 7412700 by spamujul
75 CURSOR c_grp_own_task_nodes_1 IS
76 SELECT /*+ first_rows */
77 TYPES.TASK_TYPE_ID TASK_TYPE_ID,
78 TYPES.NAME TASK_TYPE
79 FROM JTF_TASK_TYPES_TL TYPES
80 WHERE EXISTS
81 ( SELECT 1
82 FROM JTF_RS_GROUP_MEMBERS M,
83 JTF_TASKS_B TASKS
84 WHERE M.RESOURCE_ID = p_resource_id
85 AND TYPES.TASK_TYPE_ID=TASKS.TASK_TYPE_ID
86 AND NVL(M.DELETE_FLAG,'N') <> 'Y'
87 AND TASKS.OWNER_ID =M.GROUP_ID
88 AND TASKS.OPEN_FLAG = 'Y'
89 AND TASKS.OWNER_TYPE_CODE= 'RS_GROUP'
90 AND TASKS.ENTITY = 'TASK'
91 AND NVL(TASKS.DELETED_FLAG,'N') = 'N')
92 AND TYPES.LANGUAGE = USERENV('lang')
93 ORDER BY 2;
94
95 -- Owned by Team added
96 -- Begin fix for 7412700 by spamujul
97 -- Commented the Following code to improved the performance
98 -- CURSOR c_team_own_task_nodes_1 IS
99 -- SELECT /*+ first_rows */ TYPES.TASK_TYPE_ID TASK_TYPE_ID,TYPES.NAME TASK_TYPE
100 -- FROM JTF_TASK_TYPES_TL TYPES
101 -- WHERE types.task_type_id in
102 -- (SELECT /*+ use_nl(tasks) */ task_type_id FROM JTF_TASKS_B TASKS
103 -- WHERE OPEN_FLAG = 'Y'
104 -- and TASKS.OWNER_TYPE_CODE= 'RS_TEAM'
105 -- AND exists (SELECT /*+ no_unnest index(m jtf_rs_group_members_n1) */ null
106 -- FROM JTF_RS_TEAM_MEMBERS m
107 -- WHERE TEAM_RESOURCE_ID = p_resource_id
108 -- and TEAM_ID=TASKS.OWNER_ID
109 -- AND NVL(DELETE_FLAG,'N') <> 'Y' )
110 -- AND TASKS.entity = 'TASK'
111 -- AND NVL(TASKS.DELETED_FLAG,'N') = 'N'
112 -- )
113 -- and types.language = userenv('lang')
114 -- ORDER BY 2;
115 CURSOR c_team_own_task_nodes_1 IS
116 SELECT /*+ first_rows */
117 TYPES.TASK_TYPE_ID TASK_TYPE_ID,
118 TYPES.NAME TASK_TYPE
119 FROM JTF_TASK_TYPES_TL TYPES
120 WHERE EXISTS
121 ( SELECT 1
122 FROM JTF_RS_TEAM_MEMBERS M,
123 JTF_TASKS_B TASKS
124 WHERE M.TEAM_RESOURCE_ID = p_resource_id
125 and TYPES.TASK_TYPE_ID=TASKS.TASK_TYPE_ID
126 AND NVL(M.DELETE_FLAG,'N') <> 'Y'
127 AND TASKS.OWNER_ID =M.TEAM_ID
128 AND TASKS.OPEN_FLAG = 'Y'
129 AND TASKS.OWNER_TYPE_CODE= 'RS_TEAM'
130 AND TASKS.ENTITY = 'TASK'
131 AND NVL(TASKS.DELETED_FLAG,'N') = 'N')
132 AND TYPES.LANGUAGE = USERENV('lang')
133 ORDER BY 2;
134 -- End fix for 7412700 by spamujul
135 -- Assigned to Me
136
137
138 CURSOR c_assign_ind_task_nodes_1 IS
139 SELECT TYPES.TASK_TYPE_ID TASK_TYPE_ID,TYPES.NAME TASK_TYPE
140 FROM JTF_TASK_TYPES_TL TYPES
141 WHERE TYPES.TASK_TYPE_ID IN
142 (SELECT /*+ use_nl(tasks asg) */ TASKS.TASK_TYPE_ID
143 FROM JTF_TASK_ASSIGNMENTS ASG,JTF_TASKS_B TASKS
144 WHERE exists
145 (SELECT null
146 FROM JTF_OBJECT_USAGES
147 WHERE OBJECT_USER_CODE = 'RESOURCES'
148 and object_code = ASG.RESOURCE_TYPE_CODE
149 AND OBJECT_CODE NOT IN ( 'RS_GROUP','RS_TEAM' ))
150 AND TASKS.TASK_ID = ASG.TASK_ID
151 AND TASKS.OPEN_FLAG = 'Y'
152 AND TASKS.entity = 'TASK'
153 and ASG.RESOURCE_ID = p_resource_id
154 AND NVL(TASKS.DELETED_FLAG,'N') = 'N' )
155 and types.language = userenv('lang')
156 ORDER BY 2;
157
158
159 -- Assigned to Group
160
161 CURSOR c_assign_grp_task_nodes_1 IS
162 SELECT TYPES.TASK_TYPE_ID TASK_TYPE_ID,TYPES.NAME TASK_TYPE
163 FROM
164 JTF_TASK_TYPES_TL TYPES
165 WHERE TYPES.TASK_TYPE_ID IN (SELECT /*+ use_nl(tasks asg) */ TASKS.TASK_TYPE_ID
166 FROM JTF_TASK_ASSIGNMENTS ASG,JTF_TASKS_B TASKS
167 WHERE exists
168 (SELECT null
169 FROM JTF_RS_GROUP_MEMBERS
170 WHERE RESOURCE_ID = p_resource_id
171 and group_id=asg.resource_id
172 AND NVL(DELETE_FLAG,'N') <> 'Y' )
173 AND TASKS.TASK_ID = ASG.TASK_ID
174 AND TASKS.OPEN_FLAG = 'Y'
175 AND TASKS.entity = 'TASK'
176 and asg.resource_type_code='RS_GROUP'
177 AND NVL(TASKS.DELETED_FLAG,'N') = 'N' )
178 and types.language = userenv('lang')
179 ORDER BY 2;
180
181 -- Assigned to TEAM added on 7/25/03 by dolee
182
183 CURSOR c_assign_team_task_nodes_1 IS
184 SELECT TYPES.TASK_TYPE_ID TASK_TYPE_ID,TYPES.NAME TASK_TYPE
185 FROM
186 JTF_TASK_TYPES_TL TYPES
187 WHERE TYPES.TASK_TYPE_ID IN (SELECT /*+ use_nl(tasks asg) */ TASKS.TASK_TYPE_ID
188 FROM JTF_TASK_ASSIGNMENTS ASG,JTF_TASKS_B TASKS
189 WHERE exists
190 (SELECT null
191 FROM JTF_RS_TEAM_MEMBERS
192 WHERE TEAM_RESOURCE_ID = p_resource_id
193 and team_id=asg.resource_id
194 AND NVL(DELETE_FLAG,'N') <> 'Y' )
195 AND TASKS.TASK_ID = ASG.TASK_ID
196 AND TASKS.OPEN_FLAG = 'Y'
197 AND TASKS.entity = 'TASK'
198 and asg.resource_type_code='RS_TEAM'
199 AND NVL(TASKS.DELETED_FLAG,'N') = 'N' )
200 and types.language = userenv('lang')
201 ORDER BY 2;
202
203
204 CURSOR c_task_nodes_2 IS
205 SELECT
206 task_type_id, name task_type
207 FROM
208 jtf_task_types_vl
209 WHERE
210 trunc(sysdate) between trunc(nvl(start_date_active, sysdate))
211 and trunc(nvl(end_date_active, sysdate))
212 ORDER BY 2;
213
214 BEGIN
215 IF (FND_PROFILE.VALUE('IEU_QEN_NEW_TASKS') = 'N' ) THEN
216 RETURN;
217 END IF;
218
219 l_node_counter := 0;
220
221 SAVEPOINT start_enumeration;
222
223 l_sql_stmt := 'Select meaning from fnd_lookup_values_vl where lookup_type = :1 and view_application_id = :2 and lookup_code = :3';
224
225 l_lookup_type := 'IEU_NODE_LABELS';
226 l_view_application_id := 696;
227 l_lookup_code := 'IEU_NEW_TASKS_LBL';
228
229 execute immediate l_sql_stmt into l_node_label using l_lookup_type,l_view_application_id,l_lookup_code;
230
231 /* Select meaning into l_node_label
232 from fnd_lookup_values_vl
233 where lookup_type = 'IEU_NODE_LABELS'
234 and view_application_id = 696
235 and lookup_code = 'IEU_NEW_TASKS_LBL';
236 */
237
238 l_tk_list(l_node_counter).NODE_LABEL := l_node_label;
239 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_NODE_V';
240 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_NODE_DS';
241 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
242 l_tk_list(l_node_counter).WHERE_CLAUSE := '';
243 l_tk_list(l_node_counter).NODE_TYPE := 91;
244 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
245 l_tk_list(l_node_counter).NODE_DEPTH := 1;
246 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'Y';
247 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASKS_NODE_V';
248 l_tk_list(l_node_counter).REFRESH_VIEW_SUM_COL := 'COUNT';
249 l_tk_list(l_node_counter).WHERE_CLAUSE := 'resource_id = :resource_id and
250 resource_id+0 = :resource_id';
251
252 l_node_counter := l_node_counter + 1;
253
254 --Now build the subnodes
255
256 -- Owned by Me
257
258 l_ind_own_tk_bind_list(1).bind_var_name := ':resource_id';
259 l_ind_own_tk_bind_list(1).bind_var_value := p_resource_id;
260 l_ind_own_tk_bind_list(1).bind_var_data_type := 'NUMBER';
261
262
263 l_lookup_type := 'IEU_NODE_LABELS';
264 l_view_application_id := 696;
265 l_lookup_code := 'IEU_MY_OWN_LBL';
266
267 execute immediate l_sql_stmt into l_node_label using l_lookup_type,l_view_application_id,l_lookup_code;
268
269 /* Select meaning into l_node_label
270 from fnd_lookup_values_vl
271 where lookup_type = 'IEU_NODE_LABELS'
272 and view_application_id = 696
273 and lookup_code = 'IEU_MY_OWN_LBL'; */
274
275
276 l_tk_list(l_node_counter).NODE_LABEL := l_node_label;
277 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_IO_V';
278 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_IO_DS';
279 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
280 l_tk_list(l_node_counter).WHERE_CLAUSE := 'resource_id = :resource_id';
281 l_tk_list(l_node_counter).NODE_TYPE := 0;
282 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
283 l_tk_list(l_node_counter).NODE_DEPTH := 2;
284 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_ind_own_tk_bind_list);
285 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
286 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_IO_REF_V';
287
288 l_node_counter := l_node_counter + 1;
289
290 IF (FND_PROFILE.VALUE('IEU_TASK_TYPES') = 'Y') THEN
291 IF (FND_PROFILE.VALUE('IEU_CLI_UI_SHOW_ALL_NODES') = 'N') THEN
292
293 FOR cur_rec IN c_ind_own_task_nodes_1 LOOP
294
295 l_ind_own_tk_bind_list(1).bind_var_name := ':resource_id';
296 l_ind_own_tk_bind_list(1).bind_var_value := p_resource_id;
297 l_ind_own_tk_bind_list(1).bind_var_data_type := 'NUMBER';
298 l_ind_own_tk_bind_list(2).bind_var_name := ':task_type_id';
299 l_ind_own_tk_bind_list(2).bind_var_value := cur_rec.task_type_id;
300 l_ind_own_tk_bind_list(2).bind_var_data_type := 'NUMBER';
301
302 l_tk_list(l_node_counter).NODE_LABEL := cur_rec.task_type;
303 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_IO_V';
304 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_IO_DS';
305 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
306 l_tk_list(l_node_counter).WHERE_CLAUSE := 'resource_id = :resource_id and
307 TASK_TYPE_ID = :task_type_id';
308 l_tk_list(l_node_counter).NODE_TYPE := 0;
309 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
310 l_tk_list(l_node_counter).NODE_DEPTH := 3;
311 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_ind_own_tk_bind_list);
312 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
313 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_IO_REF_V';
314
315 l_node_counter := l_node_counter + 1;
316
317 END LOOP;
318
319 ELSE
320
321 FOR cur_rec IN c_task_nodes_2 LOOP
322
323 -- insert the bind variable names and values into l_bind_list
324
325 l_ind_own_tk_bind_list(1).bind_var_name := ':resource_id';
326 l_ind_own_tk_bind_list(1).bind_var_value := p_resource_id;
327 l_ind_own_tk_bind_list(1).bind_var_data_type := 'NUMBER';
328 l_ind_own_tk_bind_list(2).bind_var_name := ':task_type_id';
329 l_ind_own_tk_bind_list(2).bind_var_value := cur_rec.task_type_id;
330 l_ind_own_tk_bind_list(2).bind_var_data_type := 'NUMBER';
331
332 l_tk_list(l_node_counter).NODE_LABEL := cur_rec.task_type;
333 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_IO_V';
334 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_IO_DS';
335 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
336 l_tk_list(l_node_counter).WHERE_CLAUSE := 'resource_id = :resource_id and
337 TASK_TYPE_ID = :task_type_id';
338 l_tk_list(l_node_counter).NODE_TYPE := 0;
339 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
340 l_tk_list(l_node_counter).NODE_DEPTH := 3;
341 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_ind_own_tk_bind_list);
342 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
343 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_IO_REF_V';
344
345 l_node_counter := l_node_counter + 1;
346
347 END LOOP;
348
349 END IF;
350 END IF; --Task Types
351
352 -- Assigned to Me
353
354
355 l_lookup_type := 'IEU_NODE_LABELS';
356 l_view_application_id := 696;
357 l_lookup_code := 'IEU_MY_ASSIGN_LBL';
358
359 execute immediate l_sql_stmt into l_node_label using l_lookup_type,l_view_application_id,l_lookup_code;
360
361 /* Select meaning into l_node_label
362 from fnd_lookup_values_vl
363 where lookup_type = 'IEU_NODE_LABELS'
364 and view_application_id = 696
365 and lookup_code = 'IEU_MY_ASSIGN_LBL'; */
366
367 l_ind_asg_tk_bind_list(1).bind_var_name := ':resource_id';
368 l_ind_asg_tk_bind_list(1).bind_var_value := p_resource_id;
369 l_ind_asg_tk_bind_list(1).bind_var_data_type := 'NUMBER';
370
371 l_tk_list(l_node_counter).NODE_LABEL := l_node_label;
372 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_IA_V';
373 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_IA_DS';
374 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
375 l_tk_list(l_node_counter).WHERE_CLAUSE := 'resource_id = :resource_id';
376 l_tk_list(l_node_counter).NODE_TYPE := 0;
377 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
378 l_tk_list(l_node_counter).NODE_DEPTH := 2;
379 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_ind_asg_tk_bind_list);
380 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
381 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_IA_REF_V';
382
383 l_node_counter := l_node_counter + 1;
384
385 IF (FND_PROFILE.VALUE('IEU_TASK_TYPES') = 'Y') THEN
386 IF (FND_PROFILE.VALUE('IEU_CLI_UI_SHOW_ALL_NODES') = 'N') THEN
387
388 FOR cur_rec IN c_assign_ind_task_nodes_1 LOOP
389
390 l_ind_asg_tk_bind_list(1).bind_var_name := ':resource_id';
391 l_ind_asg_tk_bind_list(1).bind_var_value := p_resource_id;
392 l_ind_asg_tk_bind_list(1).bind_var_data_type := 'NUMBER';
393 l_ind_asg_tk_bind_list(2).bind_var_name := ':task_type_id';
394 l_ind_asg_tk_bind_list(2).bind_var_value := cur_rec.task_type_id;
395 l_ind_asg_tk_bind_list(2).bind_var_data_type := 'NUMBER';
396
397 l_tk_list(l_node_counter).NODE_LABEL := cur_rec.task_type;
398 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_IA_V';
399 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_IA_DS';
400 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
401 l_tk_list(l_node_counter).WHERE_CLAUSE := 'resource_id = :resource_id and
402 TASK_TYPE_ID = :task_type_id';
403 l_tk_list(l_node_counter).NODE_TYPE := 0;
404 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
405 l_tk_list(l_node_counter).NODE_DEPTH := 3;
406 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_ind_asg_tk_bind_list);
407 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
408 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_IA_REF_V';
409
410 l_node_counter := l_node_counter + 1;
411
412 END LOOP;
413
414 ELSE
415
416 FOR cur_rec IN c_task_nodes_2 LOOP
417
418 -- insert the bind variable names and values into l_bind_list
419
420 l_ind_asg_tk_bind_list(1).bind_var_name := ':resource_id';
421 l_ind_asg_tk_bind_list(1).bind_var_value := p_resource_id;
422 l_ind_asg_tk_bind_list(1).bind_var_data_type := 'NUMBER';
423 l_ind_asg_tk_bind_list(2).bind_var_name := ':task_type_id';
424 l_ind_asg_tk_bind_list(2).bind_var_value := cur_rec.task_type_id;
425 l_ind_asg_tk_bind_list(2).bind_var_data_type := 'NUMBER';
426
427 l_tk_list(l_node_counter).NODE_LABEL := cur_rec.task_type;
428 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_IA_V';
429 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_IA_DS';
430 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
431 l_tk_list(l_node_counter).WHERE_CLAUSE := 'resource_id = :resource_id and
432 TASK_TYPE_ID = :task_type_id';
433 l_tk_list(l_node_counter).NODE_TYPE := 0;
434 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
435 l_tk_list(l_node_counter).NODE_DEPTH := 3;
436 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_ind_asg_tk_bind_list);
437 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
438 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_IA_REF_V';
439
440 l_node_counter := l_node_counter + 1;
441
442 END LOOP;
443
444 END IF;
445 END IF; -- Task Types
446
447 -- Group Owned
448 -- This node will be displayed if the Profile Option valus is 'A' - Show Groups and Teams
449 -- or 'S' - Show Groups.
450
451 IF ( (nvl(FND_PROFILE.VALUE('IEU_ENT_TASK_RES_TYPES'), 'H') = 'A') OR
452 (nvl(FND_PROFILE.VALUE('IEU_ENT_TASK_RES_TYPES'), 'H') = 'S') )
453 then
454
455
456 l_lookup_type := 'IEU_NODE_LABELS';
457 l_view_application_id := 696;
458 l_lookup_code := 'IEU_GRP_OWN_LBL';
459
460 execute immediate l_sql_stmt into l_node_label using l_lookup_type,l_view_application_id,l_lookup_code;
461
462 /* Select meaning into l_node_label
463 from fnd_lookup_values_vl
464 where lookup_type = 'IEU_NODE_LABELS'
465 and view_application_id = 696
466 and lookup_code = 'IEU_GRP_OWN_LBL'; */
467
468 l_grp_own_tk_bind_list(1).bind_var_name := ':resource_id';
469 l_grp_own_tk_bind_list(1).bind_var_value := p_resource_id;
470 l_grp_own_tk_bind_list(1).bind_var_data_type := 'NUMBER';
471
472 l_grp_own_tk_bind_list(2).bind_var_name := ':delete_flag';
473 l_grp_own_tk_bind_list(2).bind_var_value := 'N';
474 l_grp_own_tk_bind_list(2).bind_var_data_type := 'CHAR';
475
476 l_tk_list(l_node_counter).NODE_LABEL := l_node_label;
477 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_GO_V';
478 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_GO_DS';
479 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
480 -- Begin fix by spamujul for 7024226
481 -- Commented the Following code in include index in the where clause
482 /* l_tk_list(l_node_counter).WHERE_CLAUSE := ' exists ( select m.resource_id
483 from jtf_rs_group_members m
484 where m.group_id = owner_id
485 and nvl(delete_flag,'||''''||'N'||''''||') = '||':delete_flag'||
486 ' and m.resource_id = :resource_id)'; */
487 l_tk_list(l_node_counter).WHERE_CLAUSE := ' exists ( select /*+ index(m JTF_RS_GROUP_MEMBERS_N1) */
488 m.resource_id
489 from jtf_rs_group_members m
490 where m.group_id = owner_id
491 and nvl(delete_flag,'||''''||'N'||''''||') = '||':delete_flag'||
492 ' and m.resource_id = :resource_id) ';
493 -- End fix by spamujul for 7024226
494 l_tk_list(l_node_counter).NODE_TYPE := 0;
495 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
496 l_tk_list(l_node_counter).NODE_DEPTH := 2;
497 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_grp_own_tk_bind_list);
498 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
499 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_GO_REF_V v';
500
501 l_node_counter := l_node_counter + 1;
502
503 IF (FND_PROFILE.VALUE('IEU_TASK_TYPES') = 'Y') THEN
504 IF (FND_PROFILE.VALUE('IEU_CLI_UI_SHOW_ALL_NODES') = 'N') THEN
505
506 FOR cur_rec IN c_grp_own_task_nodes_1 LOOP
507
508 l_grp_own_tk_bind_list(1).bind_var_name := ':resource_id';
509 l_grp_own_tk_bind_list(1).bind_var_value := p_resource_id;
510 l_grp_own_tk_bind_list(1).bind_var_data_type := 'NUMBER';
511 l_grp_own_tk_bind_list(2).bind_var_name := ':task_type_id';
512 l_grp_own_tk_bind_list(2).bind_var_value := cur_rec.task_type_id;
513 l_grp_own_tk_bind_list(2).bind_var_data_type := 'NUMBER';
514 l_grp_own_tk_bind_list(3).bind_var_name := ':delete_flag';
515 l_grp_own_tk_bind_list(3).bind_var_value := 'N';
516 l_grp_own_tk_bind_list(3).bind_var_data_type := 'CHAR';
517
518 l_tk_list(l_node_counter).NODE_LABEL := cur_rec.task_type;
519 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_GO_V';
520 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_GO_DS';
521 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
522 l_tk_list(l_node_counter).WHERE_CLAUSE := ' exists ( select m.resource_id
523 from jtf_rs_group_members m
524 where m.group_id = owner_id
525 and nvl(delete_flag,'||''''||'N'||''''||') = '||':delete_flag'||
526 ' and m.resource_id = :resource_id) and
527 TASK_TYPE_ID = :task_type_id';
528 l_tk_list(l_node_counter).NODE_TYPE := 0;
529 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
530 l_tk_list(l_node_counter).NODE_DEPTH := 3;
531 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_grp_own_tk_bind_list);
532 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
533 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_GO_REF_V v';
534
535
536 l_node_counter := l_node_counter + 1;
537
538 END LOOP;
539
540 ELSE
541
542 FOR cur_rec IN c_task_nodes_2 LOOP
543
544 -- insert the bind variable names and values into l_bind_list
545
546 l_grp_own_tk_bind_list(1).bind_var_name := ':resource_id';
547 l_grp_own_tk_bind_list(1).bind_var_value := p_resource_id;
548 l_grp_own_tk_bind_list(1).bind_var_data_type := 'NUMBER';
549 l_grp_own_tk_bind_list(2).bind_var_name := ':task_type_id';
550 l_grp_own_tk_bind_list(2).bind_var_value := cur_rec.task_type_id;
551 l_grp_own_tk_bind_list(2).bind_var_data_type := 'NUMBER';
552 l_grp_own_tk_bind_list(3).bind_var_name := ':delete_flag';
553 l_grp_own_tk_bind_list(3).bind_var_value := 'N';
554 l_grp_own_tk_bind_list(3).bind_var_data_type := 'CHAR';
555
556 l_tk_list(l_node_counter).NODE_LABEL := cur_rec.task_type;
557 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_GO_V';
558 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_GO_DS';
559 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
560 l_tk_list(l_node_counter).WHERE_CLAUSE := ' exists ( select m.resource_id
561 from jtf_rs_group_members m
562 where m.group_id = owner_id
563 and nvl(delete_flag,'||''''||'N'||''''||') = '||':delete_flag'||
564 ' and m.resource_id = :resource_id) and
565 TASK_TYPE_ID = :task_type_id';
566 l_tk_list(l_node_counter).NODE_TYPE := 0;
567 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
568 l_tk_list(l_node_counter).NODE_DEPTH := 3;
569 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_grp_own_tk_bind_list);
570 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
571 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_GO_REF_V v';
572
573 l_node_counter := l_node_counter + 1;
574
575 END LOOP;
576 END IF;
577 END IF; -- Task Types
578
579 -- Group Assigned
580
581 l_lookup_type := 'IEU_NODE_LABELS';
582 l_view_application_id := 696;
583 l_lookup_code := 'IEU_GRP_ASSIGN_LBL';
584
585 execute immediate l_sql_stmt into l_node_label using l_lookup_type,l_view_application_id,l_lookup_code;
586
587 /* Select meaning into l_node_label
588 from fnd_lookup_values_vl
589 where lookup_type = 'IEU_NODE_LABELS'
590 and view_application_id = 696
591 and lookup_code = 'IEU_GRP_ASSIGN_LBL'; */
592
593 l_grp_asg_tk_bind_list(1).bind_var_name := ':resource_id';
594 l_grp_asg_tk_bind_list(1).bind_var_value := p_resource_id;
595 l_grp_asg_tk_bind_list(1).bind_var_data_type := 'NUMBER';
596 l_grp_asg_tk_bind_list(2).bind_var_name := ':delete_flag';
597 l_grp_asg_tk_bind_list(2).bind_var_value := 'N';
598 l_grp_asg_tk_bind_list(2).bind_var_data_type := 'CHAR';
599
600 l_tk_list(l_node_counter).NODE_LABEL := l_node_label;
601 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_GA_V';
602 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_GA_DS';
603 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
604 l_tk_list(l_node_counter).WHERE_CLAUSE := ' exists ( select m.resource_id
605 from jtf_rs_group_members m
606 where m.group_id = owner_id
607 and nvl(delete_flag,'||''''||'N'||''''||') = '||':delete_flag'||
608 ' and m.resource_id = :resource_id) ';
609 l_tk_list(l_node_counter).NODE_TYPE := 0;
610 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
611 l_tk_list(l_node_counter).NODE_DEPTH := 2;
612 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_grp_asg_tk_bind_list);
613 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
614 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_GA_REF_V v';
615 l_node_counter := l_node_counter + 1;
616
617 IF (FND_PROFILE.VALUE('IEU_TASK_TYPES') = 'Y') THEN
618 IF (FND_PROFILE.VALUE('IEU_CLI_UI_SHOW_ALL_NODES') = 'N') THEN
619
620 FOR cur_rec IN c_assign_grp_task_nodes_1 LOOP
621
622 l_grp_asg_tk_bind_list(1).bind_var_name := ':resource_id';
623 l_grp_asg_tk_bind_list(1).bind_var_value := p_resource_id;
624 l_grp_asg_tk_bind_list(1).bind_var_data_type := 'NUMBER';
625 l_grp_asg_tk_bind_list(2).bind_var_name := ':task_type_id';
626 l_grp_asg_tk_bind_list(2).bind_var_value := cur_rec.task_type_id;
627 l_grp_asg_tk_bind_list(2).bind_var_data_type := 'NUMBER';
628 l_grp_asg_tk_bind_list(3).bind_var_name := ':delete_flag';
629 l_grp_asg_tk_bind_list(3).bind_var_value := 'N';
630 l_grp_asg_tk_bind_list(3).bind_var_data_type := 'CHAR';
631
632 l_tk_list(l_node_counter).NODE_LABEL := cur_rec.task_type;
633 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_GA_V';
634 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_GA_DS';
635 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
636 l_tk_list(l_node_counter).WHERE_CLAUSE := ' exists ( select m.resource_id
637 from jtf_rs_group_members m
638 where m.group_id = owner_id
639 and nvl(delete_flag,'||''''||'N'||''''||') = '||':delete_flag'||
640 ' and m.resource_id = :resource_id) and
641 TASK_TYPE_ID = :task_type_id';
642 l_tk_list(l_node_counter).NODE_TYPE := 0;
643 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
644 l_tk_list(l_node_counter).NODE_DEPTH := 3;
645 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_grp_asg_tk_bind_list);
646 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
647 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_GA_REF_V v';
648
649 l_node_counter := l_node_counter + 1;
650
651 END LOOP;
652
653 ELSE
654
655 FOR cur_rec IN c_task_nodes_2 LOOP
656
657 -- insert the bind variable names and values into l_bind_list
658
659 l_grp_asg_tk_bind_list(1).bind_var_name := ':resource_id';
660 l_grp_asg_tk_bind_list(1).bind_var_value := p_resource_id;
661 l_grp_asg_tk_bind_list(1).bind_var_data_type := 'NUMBER';
662 l_grp_asg_tk_bind_list(2).bind_var_name := ':task_type_id';
663 l_grp_asg_tk_bind_list(2).bind_var_value := cur_rec.task_type_id;
664 l_grp_asg_tk_bind_list(2).bind_var_data_type := 'NUMBER';
665 l_grp_asg_tk_bind_list(3).bind_var_name := ':delete_flag';
666 l_grp_asg_tk_bind_list(3).bind_var_value := 'N';
667 l_grp_asg_tk_bind_list(3).bind_var_data_type := 'CHAR';
668
669 l_tk_list(l_node_counter).NODE_LABEL := cur_rec.task_type;
670 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_GA_V';
671 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_GA_DS';
672 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
673 l_tk_list(l_node_counter).WHERE_CLAUSE := ' exists ( select m.resource_id
674 from jtf_rs_group_members m
675 where m.group_id = owner_id
676 and nvl(delete_flag,'||''''||'N'||''''||') = '||':delete_flag'||
677 ' and m.resource_id = :resource_id) and
678 TASK_TYPE_ID = :task_type_id';
679 l_tk_list(l_node_counter).NODE_TYPE := 0;
680 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
681 l_tk_list(l_node_counter).NODE_DEPTH := 3;
682 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_grp_asg_tk_bind_list);
683 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
684 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_GA_REF_V v';
685
686 l_node_counter := l_node_counter + 1;
687
688 END LOOP;
689
690 END IF;
691 END IF;
692 end if ; -- show group
693
694
695 -- Team Owned
696 -- This node will be displayed if the Profile Option valus is 'A' - Show Groups and Teams
697 -- or 'ST' - Show Groups.
698
699 IF ( (nvl(FND_PROFILE.VALUE('IEU_ENT_TASK_RES_TYPES'), 'H') = 'A') OR
700 (nvl(FND_PROFILE.VALUE('IEU_ENT_TASK_RES_TYPES'), 'H') = 'ST') )
701 then
702
703 l_lookup_type := 'IEU_NODE_LABELS';
704 l_view_application_id := 696;
705 l_lookup_code := 'IEU_TEAM_OWN_LBL';
706
707 execute immediate l_sql_stmt into l_node_label using l_lookup_type,l_view_application_id,l_lookup_code;
708
709 /* Select meaning into l_node_label
710 from fnd_lookup_values_vl
711 where lookup_type = 'IEU_NODE_LABELS'
712 and view_application_id = 696
713 and lookup_code = 'IEU_TEAM_OWN_LBL'; */
714
715 l_team_own_tk_bind_list(1).bind_var_name := ':resource_id';
716 l_team_own_tk_bind_list(1).bind_var_value := p_resource_id;
717 l_team_own_tk_bind_list(1).bind_var_data_type := 'NUMBER';
718 l_team_own_tk_bind_list(2).bind_var_name := ':delete_flag';
719 l_team_own_tk_bind_list(2).bind_var_value := 'N';
720 l_team_own_tk_bind_list(2).bind_var_data_type := 'CHAR';
721
722 l_tk_list(l_node_counter).NODE_LABEL := l_node_label;
723 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_TO_V';
724 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_TO_DS';
725 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
726 l_tk_list(l_node_counter).WHERE_CLAUSE := ' exists ( select m.team_resource_id
727 from jtf_rs_team_members m
728 where m.team_id = owner_id
729 and nvl(delete_flag,'||''''||'N'||''''||') = '||':delete_flag'||
730 ' and m.team_resource_id = :resource_id)';
731 l_tk_list(l_node_counter).NODE_TYPE := 0;
732 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
733 l_tk_list(l_node_counter).NODE_DEPTH := 2;
734 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_team_own_tk_bind_list);
735 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
736 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_TO_REF_V';
737
738 l_node_counter := l_node_counter + 1;
739
740 IF (FND_PROFILE.VALUE('IEU_TASK_TYPES') = 'Y') THEN
741 IF (FND_PROFILE.VALUE('IEU_CLI_UI_SHOW_ALL_NODES') = 'N') THEN
742
743 FOR cur_rec IN c_team_own_task_nodes_1 LOOP
744
745 l_team_own_tk_bind_list(1).bind_var_name := ':resource_id';
746 l_team_own_tk_bind_list(1).bind_var_value := p_resource_id;
747 l_team_own_tk_bind_list(1).bind_var_data_type := 'NUMBER';
748 l_team_own_tk_bind_list(2).bind_var_name := ':task_type_id';
749 l_team_own_tk_bind_list(2).bind_var_value := cur_rec.task_type_id;
750 l_team_own_tk_bind_list(2).bind_var_data_type := 'NUMBER';
751 l_team_own_tk_bind_list(3).bind_var_name := ':delete_flag';
752 l_team_own_tk_bind_list(3).bind_var_value := 'N';
753 l_team_own_tk_bind_list(3).bind_var_data_type := 'CHAR';
754
755 l_tk_list(l_node_counter).NODE_LABEL := cur_rec.task_type;
756 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_TO_V';
757 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_TO_DS';
758 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
759 l_tk_list(l_node_counter).WHERE_CLAUSE := ' exists ( select m.team_resource_id
760 from jtf_rs_team_members m
761 where m.team_id = owner_id
762 and nvl(delete_flag,'||''''||'N'||''''||') = '||':delete_flag'||
763 ' and m.team_resource_id = :resource_id) and
764 TASK_TYPE_ID = :task_type_id';
765 l_tk_list(l_node_counter).NODE_TYPE := 0;
766 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
767 l_tk_list(l_node_counter).NODE_DEPTH := 3;
768 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_team_own_tk_bind_list);
769 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
770 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_TO_REF_V';
771
772
773 l_node_counter := l_node_counter + 1;
774
775 END LOOP;
776
777 ELSE
778
779 FOR cur_rec IN c_task_nodes_2 LOOP
780
781 -- insert the bind variable names and values into l_bind_list
782
783 l_team_own_tk_bind_list(1).bind_var_name := ':resource_id';
784 l_team_own_tk_bind_list(1).bind_var_value := p_resource_id;
785 l_team_own_tk_bind_list(1).bind_var_data_type := 'NUMBER';
786 l_team_own_tk_bind_list(2).bind_var_name := ':task_type_id';
787 l_team_own_tk_bind_list(2).bind_var_value := cur_rec.task_type_id;
788 l_team_own_tk_bind_list(2).bind_var_data_type := 'NUMBER';
789 l_team_own_tk_bind_list(3).bind_var_name := ':delete_flag';
790 l_team_own_tk_bind_list(3).bind_var_value := 'N';
791 l_team_own_tk_bind_list(3).bind_var_data_type := 'CHAR';
792
793 l_tk_list(l_node_counter).NODE_LABEL := cur_rec.task_type;
794 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_TO_V';
795 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_TO_DS';
796 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
797 l_tk_list(l_node_counter).WHERE_CLAUSE := ' exists ( select m.team_resource_id
798 from jtf_rs_team_members m
799 where m.team_id = owner_id
800 and nvl(delete_flag,'||''''||'N'||''''||') = '||':delete_flag'||
801 ' and m.team_resource_id = :resource_id) and
802 TASK_TYPE_ID = :task_type_id';
803 l_tk_list(l_node_counter).NODE_TYPE := 0;
804 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
805 l_tk_list(l_node_counter).NODE_DEPTH := 3;
806 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_team_own_tk_bind_list);
807 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
808 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_TO_REF_V';
809
810 l_node_counter := l_node_counter + 1;
811
812 END LOOP;
813
814 END IF;
815 END IF; -- Task Types
816
817 -- Team Assigned
818
819 l_lookup_type := 'IEU_NODE_LABELS';
820 l_view_application_id := 696;
821 l_lookup_code := 'IEU_TEAM_ASSIGN_LBL';
822
823 execute immediate l_sql_stmt into l_node_label using l_lookup_type,l_view_application_id,l_lookup_code;
824
825
826 /* Select meaning into l_node_label
827 from fnd_lookup_values_vl
828 where lookup_type = 'IEU_NODE_LABELS'
829 and view_application_id = 696
830 and lookup_code = 'IEU_TEAM_ASSIGN_LBL'; */
831
832 l_team_asg_tk_bind_list(1).bind_var_name := ':resource_id';
833 l_team_asg_tk_bind_list(1).bind_var_value := p_resource_id;
834 l_team_asg_tk_bind_list(1).bind_var_data_type := 'NUMBER';
835 l_team_asg_tk_bind_list(2).bind_var_name := ':delete_flag';
836 l_team_asg_tk_bind_list(2).bind_var_value := 'N';
837 l_team_asg_tk_bind_list(2).bind_var_data_type := 'CHAR';
838
839 l_tk_list(l_node_counter).NODE_LABEL := l_node_label;
840 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_TA_V';
841 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_TA_DS';
842 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
843 l_tk_list(l_node_counter).WHERE_CLAUSE := ' exists ( select m.team_resource_id
844 from jtf_rs_team_members m
845 where m.team_id = owner_id
846 and nvl(delete_flag,'||''''||'N'||''''||') = '||':delete_flag'||
847 ' and m.team_resource_id = :resource_id)';
848 l_tk_list(l_node_counter).NODE_TYPE := 0;
849 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
850 l_tk_list(l_node_counter).NODE_DEPTH := 2;
851 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_team_asg_tk_bind_list);
852 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
853 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_TA_REF_V';
854 l_node_counter := l_node_counter + 1;
855
856 IF (FND_PROFILE.VALUE('IEU_TASK_TYPES') = 'Y') THEN
857 IF (FND_PROFILE.VALUE('IEU_CLI_UI_SHOW_ALL_NODES') = 'N') THEN
858
859 FOR cur_rec IN c_assign_team_task_nodes_1 LOOP
860
861 l_team_asg_tk_bind_list(1).bind_var_name := ':resource_id';
862 l_team_asg_tk_bind_list(1).bind_var_value := p_resource_id;
863 l_team_asg_tk_bind_list(1).bind_var_data_type := 'NUMBER';
864 l_team_asg_tk_bind_list(2).bind_var_name := ':task_type_id';
865 l_team_asg_tk_bind_list(2).bind_var_value := cur_rec.task_type_id;
866 l_team_asg_tk_bind_list(2).bind_var_data_type := 'NUMBER';
867 l_team_asg_tk_bind_list(3).bind_var_name := ':delete_flag';
868 l_team_asg_tk_bind_list(3).bind_var_value := 'N';
869 l_team_asg_tk_bind_list(3).bind_var_data_type := 'CHAR';
870
871 l_tk_list(l_node_counter).NODE_LABEL := cur_rec.task_type;
872 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_TA_V';
873 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_TA_DS';
874 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
875 l_tk_list(l_node_counter).WHERE_CLAUSE := ' exists ( select m.team_resource_id
876 from jtf_rs_team_members m
877 where m.team_id = owner_id
878 and nvl(delete_flag,'||''''||'N'||''''||') = '||':delete_flag'||
879 ' and m.team_resource_id = :resource_id) and
880 TASK_TYPE_ID = :task_type_id';
881 l_tk_list(l_node_counter).NODE_TYPE := 0;
882 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
883 l_tk_list(l_node_counter).NODE_DEPTH := 3;
884 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_team_asg_tk_bind_list);
885 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
886 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_TA_REF_V';
887
888 l_node_counter := l_node_counter + 1;
889
890 END LOOP;
891
892 ELSE
893
894 FOR cur_rec IN c_task_nodes_2 LOOP
895
896 -- insert the bind variable names and values into l_bind_list
897
898 l_team_asg_tk_bind_list(1).bind_var_name := ':resource_id';
899 l_team_asg_tk_bind_list(1).bind_var_value := p_resource_id;
900 l_team_asg_tk_bind_list(1).bind_var_data_type := 'NUMBER';
901 l_team_asg_tk_bind_list(2).bind_var_name := ':task_type_id';
902 l_team_asg_tk_bind_list(2).bind_var_value := cur_rec.task_type_id;
903 l_team_asg_tk_bind_list(2).bind_var_data_type := 'NUMBER';
904 l_team_asg_tk_bind_list(3).bind_var_name := ':delete_flag';
905 l_team_asg_tk_bind_list(3).bind_var_value := 'N';
906 l_team_asg_tk_bind_list(3).bind_var_data_type := 'CHAR';
907
908 l_tk_list(l_node_counter).NODE_LABEL := cur_rec.task_type;
909 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_TA_V';
910 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_TA_DS';
911 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
912 l_tk_list(l_node_counter).WHERE_CLAUSE := ' exists ( select m.team_resource_id
913 from jtf_rs_team_members m
914 where m.team_id = owner_id
915 and nvl(delete_flag,'||''''||'N'||''''||') = '|| ':delete_flag'||
916 ' and m.team_resource_id = :resource_id) and
917 TASK_TYPE_ID = :task_type_id';
918 l_tk_list(l_node_counter).NODE_TYPE := 0;
919 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
920 l_tk_list(l_node_counter).NODE_DEPTH := 3;
921 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_team_asg_tk_bind_list);
922 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
923 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_TA_REF_V';
924
925 l_node_counter := l_node_counter + 1;
926
927 END LOOP;
928
929 END IF;
930 END IF;
931 end if ; -- show team
932
933
934 IEU_PUB.ADD_UWQ_NODE_DATA
935 (P_RESOURCE_ID,
936 P_SEL_ENUM_ID,
937 l_tk_list
938 );
939
940 EXCEPTION
941 WHEN OTHERS THEN
942 ROLLBACK TO start_enumeration;
943 RAISE;
944
945 END ENUMERATE_TASK_NODES;
946
947 END IEU_TASKS_ENUMS_PVT;