[Home] [Help]
PACKAGE BODY: APPS.IEU_TASKS_ENUMS_PVT
Source
4 -- Sub-Program Units
1 PACKAGE BODY IEU_TASKS_ENUMS_PVT AS
2 /* $Header: IEUENTNB.pls 120.4.12020000.4 2013/05/21 18:00:12 spamujul ship $ */
3
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' )
113 -- and types.language = userenv('lang')
110 -- AND TASKS.entity = 'TASK'
111 -- AND NVL(TASKS.DELETED_FLAG,'N') = 'N'
112 -- )
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 ,JTF_TASK_STATUSES_B STB --Added the following code by rgandhi for Bug9920514
145 WHERE exists
146 (SELECT null
147 FROM JTF_OBJECT_USAGES
148 WHERE OBJECT_USER_CODE = 'RESOURCES'
149 and object_code = ASG.RESOURCE_TYPE_CODE
150 AND OBJECT_CODE NOT IN ( 'RS_GROUP','RS_TEAM' ))
151 AND TASKS.TASK_ID = ASG.TASK_ID
152 AND STB.TASK_STATUS_ID = ASG.ASSIGNMENT_STATUS_ID --Added the following code by rgandhi for Bug9920514
153 AND NVL(STB.CANCELLED_FLAG, 'N') = 'N' --Added the following code by rgandhi for Bug9920514
154 AND TASKS.OPEN_FLAG = 'Y'
155 AND TASKS.entity = 'TASK'
156 and ASG.RESOURCE_ID = p_resource_id
160
157 AND NVL(TASKS.DELETED_FLAG,'N') = 'N' )
158 and types.language = userenv('lang')
159 ORDER BY 2;
161
162 -- Assigned to Group
163
164 CURSOR c_assign_grp_task_nodes_1 IS
165 SELECT TYPES.TASK_TYPE_ID TASK_TYPE_ID,TYPES.NAME TASK_TYPE
166 FROM
167 JTF_TASK_TYPES_TL TYPES
168 ,JTF_TASK_STATUSES_B STB --Added the following code by rgandhi for Bug9920514
169 WHERE TYPES.TASK_TYPE_ID IN (SELECT /*+ use_nl(tasks asg) */ TASKS.TASK_TYPE_ID
170 FROM JTF_TASK_ASSIGNMENTS ASG,JTF_TASKS_B TASKS
171 WHERE exists
172 (SELECT null
173 FROM JTF_RS_GROUP_MEMBERS
174 WHERE RESOURCE_ID = p_resource_id
175 and group_id=asg.resource_id
176 AND NVL(DELETE_FLAG,'N') <> 'Y' )
177 AND TASKS.TASK_ID = ASG.TASK_ID
178 AND STB.TASK_STATUS_ID = ASG.ASSIGNMENT_STATUS_ID --Added the following code by rgandhi for Bug9920514
179 AND NVL(STB.CANCELLED_FLAG, 'N') = 'N' --Added the following code by rgandhi for Bug9920514
180 AND TASKS.OPEN_FLAG = 'Y'
181 AND TASKS.entity = 'TASK'
182 and asg.resource_type_code='RS_GROUP'
183 AND NVL(TASKS.DELETED_FLAG,'N') = 'N' )
184 and types.language = userenv('lang')
185 ORDER BY 2;
186
187 -- Assigned to TEAM added on 7/25/03 by dolee
188
189 CURSOR c_assign_team_task_nodes_1 IS
190 SELECT TYPES.TASK_TYPE_ID TASK_TYPE_ID,TYPES.NAME TASK_TYPE
191 FROM
192 JTF_TASK_TYPES_TL TYPES
193 ,JTF_TASK_STATUSES_B STB --Added the following code by rgandhi for Bug9920514
194 WHERE TYPES.TASK_TYPE_ID IN (SELECT /*+ use_nl(tasks asg) */ TASKS.TASK_TYPE_ID
195 FROM JTF_TASK_ASSIGNMENTS ASG,JTF_TASKS_B TASKS
196 WHERE exists
197 (SELECT null
198 FROM JTF_RS_TEAM_MEMBERS
199 WHERE TEAM_RESOURCE_ID = p_resource_id
200 and team_id=asg.resource_id
201 AND NVL(DELETE_FLAG,'N') <> 'Y' )
202 AND TASKS.TASK_ID = ASG.TASK_ID
203 AND STB.TASK_STATUS_ID = ASG.ASSIGNMENT_STATUS_ID --Added the following code by rgandhi for Bug9920514
204 AND NVL(STB.CANCELLED_FLAG, 'N') = 'N' --Added the following code by rgandhi for Bug9920514
205 AND TASKS.OPEN_FLAG = 'Y'
206 AND TASKS.entity = 'TASK'
207 and asg.resource_type_code='RS_TEAM'
208 AND NVL(TASKS.DELETED_FLAG,'N') = 'N' )
209 and types.language = userenv('lang')
210 ORDER BY 2;
211
212
213 CURSOR c_task_nodes_2 IS
214 SELECT
215 task_type_id, name task_type
216 FROM
217 jtf_task_types_vl
218 WHERE
219 trunc(sysdate) between trunc(nvl(start_date_active, sysdate))
220 and trunc(nvl(end_date_active, sysdate))
221 ORDER BY 2;
222
223 BEGIN
224 IF (FND_PROFILE.VALUE('IEU_QEN_NEW_TASKS') = 'N' ) THEN
225 RETURN;
226 END IF;
227
228 l_node_counter := 0;
229
230 SAVEPOINT start_enumeration;
231
232 l_sql_stmt := 'Select meaning from fnd_lookup_values_vl where lookup_type = :1 and view_application_id = :2 and lookup_code = :3';
233
234 l_lookup_type := 'IEU_NODE_LABELS';
235 l_view_application_id := 696;
236 l_lookup_code := 'IEU_NEW_TASKS_LBL';
237
238 execute immediate l_sql_stmt into l_node_label using l_lookup_type,l_view_application_id,l_lookup_code;
239
240 /* Select meaning into l_node_label
241 from fnd_lookup_values_vl
242 where lookup_type = 'IEU_NODE_LABELS'
243 and view_application_id = 696
244 and lookup_code = 'IEU_NEW_TASKS_LBL';
245 */
246
247 l_tk_list(l_node_counter).NODE_LABEL := l_node_label;
248 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_NODE_V';
249 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_NODE_DS';
250 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
251 l_tk_list(l_node_counter).WHERE_CLAUSE := '';
252 l_tk_list(l_node_counter).NODE_TYPE := 91;
253 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
254 l_tk_list(l_node_counter).NODE_DEPTH := 1;
255 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'Y';
256 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASKS_NODE_V';
257 l_tk_list(l_node_counter).REFRESH_VIEW_SUM_COL := 'COUNT';
258 l_tk_list(l_node_counter).WHERE_CLAUSE := 'resource_id = :resource_id and
259 resource_id+0 = :resource_id';
260 l_tk_bind_list(l_node_counter).bind_var_name := ':resource_id';
261 l_tk_bind_list(l_node_counter).bind_var_value := p_resource_id;
262 l_tk_bind_list(l_node_counter).bind_var_data_type := 'NUMBER';
263 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_tk_bind_list);
264
265 l_node_counter := l_node_counter + 1;
266
267 --Now build the subnodes
268
269 -- Owned by Me
270
271 l_ind_own_tk_bind_list(1).bind_var_name := ':resource_id';
272 l_ind_own_tk_bind_list(1).bind_var_value := p_resource_id;
273 l_ind_own_tk_bind_list(1).bind_var_data_type := 'NUMBER';
274
275
276 l_lookup_type := 'IEU_NODE_LABELS';
277 l_view_application_id := 696;
278 l_lookup_code := 'IEU_MY_OWN_LBL';
279
283 from fnd_lookup_values_vl
280 execute immediate l_sql_stmt into l_node_label using l_lookup_type,l_view_application_id,l_lookup_code;
281
282 /* Select meaning into l_node_label
284 where lookup_type = 'IEU_NODE_LABELS'
285 and view_application_id = 696
286 and lookup_code = 'IEU_MY_OWN_LBL'; */
287
288
289 l_tk_list(l_node_counter).NODE_LABEL := l_node_label;
290 /* Bug 16059990 changed to Sec view*/
291 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_IO_SEC_V';
292 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_IO_DS';
293 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
294 l_tk_list(l_node_counter).WHERE_CLAUSE := 'resource_id = :resource_id';
295 l_tk_list(l_node_counter).NODE_TYPE := 0;
296 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
297 l_tk_list(l_node_counter).NODE_DEPTH := 2;
298 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_ind_own_tk_bind_list);
299 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
300 /* Bug 16059990 changed to Sec view*/
301 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_IO_REF_SEC_V';
302
303 l_node_counter := l_node_counter + 1;
304
305 IF (FND_PROFILE.VALUE('IEU_TASK_TYPES') = 'Y') THEN
306 IF (FND_PROFILE.VALUE('IEU_CLI_UI_SHOW_ALL_NODES') = 'N') THEN
307
311 l_ind_own_tk_bind_list(1).bind_var_value := p_resource_id;
308 FOR cur_rec IN c_ind_own_task_nodes_1 LOOP
309
310 l_ind_own_tk_bind_list(1).bind_var_name := ':resource_id';
312 l_ind_own_tk_bind_list(1).bind_var_data_type := 'NUMBER';
313 l_ind_own_tk_bind_list(2).bind_var_name := ':task_type_id';
314 l_ind_own_tk_bind_list(2).bind_var_value := cur_rec.task_type_id;
315 l_ind_own_tk_bind_list(2).bind_var_data_type := 'NUMBER';
316
317 l_tk_list(l_node_counter).NODE_LABEL := cur_rec.task_type;
318 /* Bug 16059990 changed to Sec view*/
319 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_IO_SEC_V';
320 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_IO_DS';
321 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
322 l_tk_list(l_node_counter).WHERE_CLAUSE := 'resource_id = :resource_id and
323 TASK_TYPE_ID = :task_type_id';
324 l_tk_list(l_node_counter).NODE_TYPE := 0;
325 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
326 l_tk_list(l_node_counter).NODE_DEPTH := 3;
327 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_ind_own_tk_bind_list);
328 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
329 /* Bug 16059990 changed to Sec view*/
330 l_tk_list(l_node_counter).REFRESH_VIEW_NAME :=
331 'IEU_UWQ_TASK_IO_REF_SEC_V';
332
333 l_node_counter := l_node_counter + 1;
334
335 END LOOP;
336
337 ELSE
338
339 FOR cur_rec IN c_task_nodes_2 LOOP
340
341 -- insert the bind variable names and values into l_bind_list
342
343 l_ind_own_tk_bind_list(1).bind_var_name := ':resource_id';
344 l_ind_own_tk_bind_list(1).bind_var_value := p_resource_id;
345 l_ind_own_tk_bind_list(1).bind_var_data_type := 'NUMBER';
346 l_ind_own_tk_bind_list(2).bind_var_name := ':task_type_id';
347 l_ind_own_tk_bind_list(2).bind_var_value := cur_rec.task_type_id;
348 l_ind_own_tk_bind_list(2).bind_var_data_type := 'NUMBER';
349
350 l_tk_list(l_node_counter).NODE_LABEL := cur_rec.task_type;
351 /* Bug 16059990 changed to Sec view*/
352 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_IO_SEC_V';
353 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_IO_DS';
354 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
355 l_tk_list(l_node_counter).WHERE_CLAUSE := 'resource_id = :resource_id and
356 TASK_TYPE_ID = :task_type_id';
357 l_tk_list(l_node_counter).NODE_TYPE := 0;
358 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
359 l_tk_list(l_node_counter).NODE_DEPTH := 3;
360 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_ind_own_tk_bind_list);
361 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
362 /* Bug 16059990 changed to Sec view*/
363 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_IO_REF_SEC_V';
364
365 l_node_counter := l_node_counter + 1;
366
367 END LOOP;
368
369 END IF;
370 END IF; --Task Types
371
372 -- Assigned to Me
373
374
375 l_lookup_type := 'IEU_NODE_LABELS';
376 l_view_application_id := 696;
377 l_lookup_code := 'IEU_MY_ASSIGN_LBL';
378
382 from fnd_lookup_values_vl
379 execute immediate l_sql_stmt into l_node_label using l_lookup_type,l_view_application_id,l_lookup_code;
380
381 /* Select meaning into l_node_label
383 where lookup_type = 'IEU_NODE_LABELS'
384 and view_application_id = 696
385 and lookup_code = 'IEU_MY_ASSIGN_LBL'; */
386
387 l_ind_asg_tk_bind_list(1).bind_var_name := ':resource_id';
388 l_ind_asg_tk_bind_list(1).bind_var_value := p_resource_id;
389 l_ind_asg_tk_bind_list(1).bind_var_data_type := 'NUMBER';
390
391 l_tk_list(l_node_counter).NODE_LABEL := l_node_label;
392 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_IA_SEC_V';
393 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_IA_DS';
394 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
395 l_tk_list(l_node_counter).WHERE_CLAUSE := 'resource_id = :resource_id';
396 l_tk_list(l_node_counter).NODE_TYPE := 0;
397 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
398 l_tk_list(l_node_counter).NODE_DEPTH := 2;
399 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_ind_asg_tk_bind_list);
400 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
401 /* Bug 16059990 changed to Sec view*/
402 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_IA_REF_SEC_V';
403
404 l_node_counter := l_node_counter + 1;
405
406 IF (FND_PROFILE.VALUE('IEU_TASK_TYPES') = 'Y') THEN
407 IF (FND_PROFILE.VALUE('IEU_CLI_UI_SHOW_ALL_NODES') = 'N') THEN
408
409 FOR cur_rec IN c_assign_ind_task_nodes_1 LOOP
410
411 l_ind_asg_tk_bind_list(1).bind_var_name := ':resource_id';
412 l_ind_asg_tk_bind_list(1).bind_var_value := p_resource_id;
413 l_ind_asg_tk_bind_list(1).bind_var_data_type := 'NUMBER';
414 l_ind_asg_tk_bind_list(2).bind_var_name := ':task_type_id';
415 l_ind_asg_tk_bind_list(2).bind_var_value := cur_rec.task_type_id;
416 l_ind_asg_tk_bind_list(2).bind_var_data_type := 'NUMBER';
417
418 l_tk_list(l_node_counter).NODE_LABEL := cur_rec.task_type;
419 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_IA_SEC_V';
420 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_IA_DS';
421 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
422 l_tk_list(l_node_counter).WHERE_CLAUSE := 'resource_id = :resource_id and
423 TASK_TYPE_ID = :task_type_id';
424 l_tk_list(l_node_counter).NODE_TYPE := 0;
425 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
426 l_tk_list(l_node_counter).NODE_DEPTH := 3;
427 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_ind_asg_tk_bind_list);
428 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
429 /* Bug 16059990 changed to Sec view*/
430 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_IA_REF_SEC_V';
431
432 l_node_counter := l_node_counter + 1;
433
434 END LOOP;
435
436 ELSE
437
438 FOR cur_rec IN c_task_nodes_2 LOOP
439
440 -- insert the bind variable names and values into l_bind_list
441
442 l_ind_asg_tk_bind_list(1).bind_var_name := ':resource_id';
443 l_ind_asg_tk_bind_list(1).bind_var_value := p_resource_id;
444 l_ind_asg_tk_bind_list(1).bind_var_data_type := 'NUMBER';
445 l_ind_asg_tk_bind_list(2).bind_var_name := ':task_type_id';
446 l_ind_asg_tk_bind_list(2).bind_var_value := cur_rec.task_type_id;
450 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_IA_SEC_V';
447 l_ind_asg_tk_bind_list(2).bind_var_data_type := 'NUMBER';
448
449 l_tk_list(l_node_counter).NODE_LABEL := cur_rec.task_type;
451 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_IA_DS';
452 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
453 l_tk_list(l_node_counter).WHERE_CLAUSE := 'resource_id = :resource_id and
454 TASK_TYPE_ID = :task_type_id';
455 l_tk_list(l_node_counter).NODE_TYPE := 0;
456 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
457 l_tk_list(l_node_counter).NODE_DEPTH := 3;
458 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_ind_asg_tk_bind_list);
459 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
460 /* Bug 16059990 changed to Sec view*/
461 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_IA_REF_SEC_V';
462
463 l_node_counter := l_node_counter + 1;
464
465 END LOOP;
466
467 END IF;
468 END IF; -- Task Types
469
473
470 -- Group Owned
471 -- This node will be displayed if the Profile Option valus is 'A' - Show Groups and Teams
472 -- or 'S' - Show Groups.
474 IF ( (nvl(FND_PROFILE.VALUE('IEU_ENT_TASK_RES_TYPES'), 'H') = 'A') OR
475 (nvl(FND_PROFILE.VALUE('IEU_ENT_TASK_RES_TYPES'), 'H') = 'S') )
476 then
477
478
479 l_lookup_type := 'IEU_NODE_LABELS';
480 l_view_application_id := 696;
481 l_lookup_code := 'IEU_GRP_OWN_LBL';
482
483 execute immediate l_sql_stmt into l_node_label using l_lookup_type,l_view_application_id,l_lookup_code;
484
485 /* Select meaning into l_node_label
486 from fnd_lookup_values_vl
487 where lookup_type = 'IEU_NODE_LABELS'
488 and view_application_id = 696
489 and lookup_code = 'IEU_GRP_OWN_LBL'; */
490
491 l_grp_own_tk_bind_list(1).bind_var_name := ':resource_id';
492 l_grp_own_tk_bind_list(1).bind_var_value := p_resource_id;
493 l_grp_own_tk_bind_list(1).bind_var_data_type := 'NUMBER';
494
495 l_grp_own_tk_bind_list(2).bind_var_name := ':delete_flag';
496 l_grp_own_tk_bind_list(2).bind_var_value := 'N';
497 l_grp_own_tk_bind_list(2).bind_var_data_type := 'CHAR';
498
499 l_tk_list(l_node_counter).NODE_LABEL := l_node_label;
500 /* Bug 16059990 changed to Sec view*/
501 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_GO_SEC_V';
502 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_GO_DS';
503 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
504 -- Begin fix by spamujul for 7024226
505 -- Commented the Following code in include index in the where clause
506 /* l_tk_list(l_node_counter).WHERE_CLAUSE := ' exists ( select m.resource_id
507 from jtf_rs_group_members m
508 where m.group_id = owner_id
509 and nvl(delete_flag,'||''''||'N'||''''||') = '||':delete_flag'||
510 ' and m.resource_id = :resource_id)'; */
511 l_tk_list(l_node_counter).WHERE_CLAUSE := ' exists ( select /*+ index(m JTF_RS_GROUP_MEMBERS_N1) */
512 m.resource_id
513 from jtf_rs_group_members m
514 where m.group_id = owner_id
515 and nvl(delete_flag,'||''''||'N'||''''||') = '||':delete_flag'||
516 ' and m.resource_id = :resource_id) ';
517 -- End fix by spamujul for 7024226
518 l_tk_list(l_node_counter).NODE_TYPE := 0;
519 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
520 l_tk_list(l_node_counter).NODE_DEPTH := 2;
521 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_grp_own_tk_bind_list);
522 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
523 /* Bug 16059990 changed to Sec view*/
524 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_GO_REF_SEC_V v';
525
526 l_node_counter := l_node_counter + 1;
527
528 IF (FND_PROFILE.VALUE('IEU_TASK_TYPES') = 'Y') THEN
529 IF (FND_PROFILE.VALUE('IEU_CLI_UI_SHOW_ALL_NODES') = 'N') THEN
530
531 FOR cur_rec IN c_grp_own_task_nodes_1 LOOP
532
533 l_grp_own_tk_bind_list(1).bind_var_name := ':resource_id';
534 l_grp_own_tk_bind_list(1).bind_var_value := p_resource_id;
535 l_grp_own_tk_bind_list(1).bind_var_data_type := 'NUMBER';
536 l_grp_own_tk_bind_list(2).bind_var_name := ':task_type_id';
537 l_grp_own_tk_bind_list(2).bind_var_value := cur_rec.task_type_id;
538 l_grp_own_tk_bind_list(2).bind_var_data_type := 'NUMBER';
539 l_grp_own_tk_bind_list(3).bind_var_name := ':delete_flag';
540 l_grp_own_tk_bind_list(3).bind_var_value := 'N';
541 l_grp_own_tk_bind_list(3).bind_var_data_type := 'CHAR';
542
543 l_tk_list(l_node_counter).NODE_LABEL := cur_rec.task_type;
544 /* Bug 16059990 changed to Sec view*/
545 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_GO_SEC_V';
546 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_GO_DS';
547 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
548 l_tk_list(l_node_counter).WHERE_CLAUSE := ' exists ( select m.resource_id
549 from jtf_rs_group_members m
550 where m.group_id = owner_id
551 and nvl(delete_flag,'||''''||'N'||''''||') = '||':delete_flag'||
552 ' and m.resource_id = :resource_id) and
553 TASK_TYPE_ID = :task_type_id';
554 l_tk_list(l_node_counter).NODE_TYPE := 0;
555 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
556 l_tk_list(l_node_counter).NODE_DEPTH := 3;
557 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_grp_own_tk_bind_list);
558 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
559 /* Bug 16059990 changed to Sec view*/
560 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_GO_REF_SEC_V v';
561
562
563 l_node_counter := l_node_counter + 1;
564
565 END LOOP;
566
567 ELSE
568
569 FOR cur_rec IN c_task_nodes_2 LOOP
570
571 -- insert the bind variable names and values into l_bind_list
572
573 l_grp_own_tk_bind_list(1).bind_var_name := ':resource_id';
574 l_grp_own_tk_bind_list(1).bind_var_value := p_resource_id;
575 l_grp_own_tk_bind_list(1).bind_var_data_type := 'NUMBER';
576 l_grp_own_tk_bind_list(2).bind_var_name := ':task_type_id';
577 l_grp_own_tk_bind_list(2).bind_var_value := cur_rec.task_type_id;
581 l_grp_own_tk_bind_list(3).bind_var_data_type := 'CHAR';
578 l_grp_own_tk_bind_list(2).bind_var_data_type := 'NUMBER';
579 l_grp_own_tk_bind_list(3).bind_var_name := ':delete_flag';
580 l_grp_own_tk_bind_list(3).bind_var_value := 'N';
582
583 l_tk_list(l_node_counter).NODE_LABEL := cur_rec.task_type;
584 /* Bug 16059990 changed to Sec view*/
585 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_GO_SEC_V';
586 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_GO_DS';
587 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
588 l_tk_list(l_node_counter).WHERE_CLAUSE := ' exists ( select m.resource_id
589 from jtf_rs_group_members m
590 where m.group_id = owner_id
591 and nvl(delete_flag,'||''''||'N'||''''||') = '||':delete_flag'||
592 ' and m.resource_id = :resource_id) and
593 TASK_TYPE_ID = :task_type_id';
594 l_tk_list(l_node_counter).NODE_TYPE := 0;
595 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
596 l_tk_list(l_node_counter).NODE_DEPTH := 3;
597 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_grp_own_tk_bind_list);
598 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
599 /* Bug 16059990 changed to Sec view*/
600 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_GO_REF_SEC_V v';
601
602 l_node_counter := l_node_counter + 1;
603
604 END LOOP;
605 END IF;
606 END IF; -- Task Types
607
608 -- Group Assigned
609
610 l_lookup_type := 'IEU_NODE_LABELS';
611 l_view_application_id := 696;
612 l_lookup_code := 'IEU_GRP_ASSIGN_LBL';
613
614 execute immediate l_sql_stmt into l_node_label using l_lookup_type,l_view_application_id,l_lookup_code;
615
616 /* Select meaning into l_node_label
617 from fnd_lookup_values_vl
618 where lookup_type = 'IEU_NODE_LABELS'
619 and view_application_id = 696
620 and lookup_code = 'IEU_GRP_ASSIGN_LBL'; */
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 := ':delete_flag';
626 l_grp_asg_tk_bind_list(2).bind_var_value := 'N';
627 l_grp_asg_tk_bind_list(2).bind_var_data_type := 'CHAR';
628
629 l_tk_list(l_node_counter).NODE_LABEL := l_node_label;
630 /* Bug 16059990 changed to Sec view*/
631 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_GA_SEC_V';
632 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_GA_DS';
633 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
634 l_tk_list(l_node_counter).WHERE_CLAUSE := ' exists ( select m.resource_id
635 from jtf_rs_group_members m
636 where m.group_id = owner_id
637 and nvl(delete_flag,'||''''||'N'||''''||') = '||':delete_flag'||
638 ' and m.resource_id = :resource_id) ';
639 l_tk_list(l_node_counter).NODE_TYPE := 0;
640 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
641 l_tk_list(l_node_counter).NODE_DEPTH := 2;
642 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_grp_asg_tk_bind_list);
643 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
644 /* Bug 16059990 changed to Sec view*/
645 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_GA_REF_SEC_V v';
646 l_node_counter := l_node_counter + 1;
647
648 IF (FND_PROFILE.VALUE('IEU_TASK_TYPES') = 'Y') THEN
649 IF (FND_PROFILE.VALUE('IEU_CLI_UI_SHOW_ALL_NODES') = 'N') THEN
650
651 FOR cur_rec IN c_assign_grp_task_nodes_1 LOOP
652
653 l_grp_asg_tk_bind_list(1).bind_var_name := ':resource_id';
654 l_grp_asg_tk_bind_list(1).bind_var_value := p_resource_id;
655 l_grp_asg_tk_bind_list(1).bind_var_data_type := 'NUMBER';
656 l_grp_asg_tk_bind_list(2).bind_var_name := ':task_type_id';
657 l_grp_asg_tk_bind_list(2).bind_var_value := cur_rec.task_type_id;
658 l_grp_asg_tk_bind_list(2).bind_var_data_type := 'NUMBER';
659 l_grp_asg_tk_bind_list(3).bind_var_name := ':delete_flag';
660 l_grp_asg_tk_bind_list(3).bind_var_value := 'N';
661 l_grp_asg_tk_bind_list(3).bind_var_data_type := 'CHAR';
662
663 l_tk_list(l_node_counter).NODE_LABEL := cur_rec.task_type;
664 /* Bug 16059990 changed to Sec view*/
665 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_GA_SEC_V';
666 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_GA_DS';
667 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
668 l_tk_list(l_node_counter).WHERE_CLAUSE := ' exists ( select m.resource_id
669 from jtf_rs_group_members m
670 where m.group_id = owner_id
671 and nvl(delete_flag,'||''''||'N'||''''||') = '||':delete_flag'||
672 ' and m.resource_id = :resource_id) and
673 TASK_TYPE_ID = :task_type_id';
674 l_tk_list(l_node_counter).NODE_TYPE := 0;
675 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
676 l_tk_list(l_node_counter).NODE_DEPTH := 3;
677 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_grp_asg_tk_bind_list);
678 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
679 /* Bug 16059990 changed to Sec view*/
680 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_GA_REF_SEC_V v';
681
682 l_node_counter := l_node_counter + 1;
683
684 END LOOP;
685
686 ELSE
687
688 FOR cur_rec IN c_task_nodes_2 LOOP
689
690 -- insert the bind variable names and values into l_bind_list
691
692 l_grp_asg_tk_bind_list(1).bind_var_name := ':resource_id';
693 l_grp_asg_tk_bind_list(1).bind_var_value := p_resource_id;
694 l_grp_asg_tk_bind_list(1).bind_var_data_type := 'NUMBER';
695 l_grp_asg_tk_bind_list(2).bind_var_name := ':task_type_id';
696 l_grp_asg_tk_bind_list(2).bind_var_value := cur_rec.task_type_id;
697 l_grp_asg_tk_bind_list(2).bind_var_data_type := 'NUMBER';
698 l_grp_asg_tk_bind_list(3).bind_var_name := ':delete_flag';
699 l_grp_asg_tk_bind_list(3).bind_var_value := 'N';
700 l_grp_asg_tk_bind_list(3).bind_var_data_type := 'CHAR';
701
702 l_tk_list(l_node_counter).NODE_LABEL := cur_rec.task_type;
703 /* Bug 16059990 changed to Sec view*/
704 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_GA_SEC_V';
705 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_GA_DS';
706 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
707 l_tk_list(l_node_counter).WHERE_CLAUSE := ' exists ( select m.resource_id
708 from jtf_rs_group_members m
709 where m.group_id = owner_id
710 and nvl(delete_flag,'||''''||'N'||''''||') = '||':delete_flag'||
711 ' and m.resource_id = :resource_id) and
712 TASK_TYPE_ID = :task_type_id';
713 l_tk_list(l_node_counter).NODE_TYPE := 0;
714 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
715 l_tk_list(l_node_counter).NODE_DEPTH := 3;
716 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_grp_asg_tk_bind_list);
717 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
718 /* Bug 16059990 changed to Sec view*/
719 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_GA_REF_SEC_V v';
720
721 l_node_counter := l_node_counter + 1;
722
723 END LOOP;
724
725 END IF;
726 END IF;
727 end if ; -- show group
728
729
730 -- Team Owned
731 -- This node will be displayed if the Profile Option valus is 'A' - Show Groups and Teams
732 -- or 'ST' - Show Groups.
733
734 IF ( (nvl(FND_PROFILE.VALUE('IEU_ENT_TASK_RES_TYPES'), 'H') = 'A') OR
735 (nvl(FND_PROFILE.VALUE('IEU_ENT_TASK_RES_TYPES'), 'H') = 'ST') )
736 then
737
738 l_lookup_type := 'IEU_NODE_LABELS';
739 l_view_application_id := 696;
740 l_lookup_code := 'IEU_TEAM_OWN_LBL';
741
742 execute immediate l_sql_stmt into l_node_label using l_lookup_type,l_view_application_id,l_lookup_code;
743
744 /* Select meaning into l_node_label
745 from fnd_lookup_values_vl
746 where lookup_type = 'IEU_NODE_LABELS'
747 and view_application_id = 696
748 and lookup_code = 'IEU_TEAM_OWN_LBL'; */
749
750 l_team_own_tk_bind_list(1).bind_var_name := ':resource_id';
751 l_team_own_tk_bind_list(1).bind_var_value := p_resource_id;
752 l_team_own_tk_bind_list(1).bind_var_data_type := 'NUMBER';
753 l_team_own_tk_bind_list(2).bind_var_name := ':delete_flag';
754 l_team_own_tk_bind_list(2).bind_var_value := 'N';
755 l_team_own_tk_bind_list(2).bind_var_data_type := 'CHAR';
756
757 l_tk_list(l_node_counter).NODE_LABEL := l_node_label;
758 /* Bug 16059990 changed to Sec view*/
759 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_TO_SEC_V';
760 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_TO_DS';
761 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
762 l_tk_list(l_node_counter).WHERE_CLAUSE := ' exists ( select m.team_resource_id
763 from jtf_rs_team_members m
764 where m.team_id = owner_id
765 and nvl(delete_flag,'||''''||'N'||''''||') = '||':delete_flag'||
769 l_tk_list(l_node_counter).NODE_DEPTH := 2;
766 ' and m.team_resource_id = :resource_id)';
767 l_tk_list(l_node_counter).NODE_TYPE := 0;
768 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
770 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_team_own_tk_bind_list);
771 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
772 /* Bug 16059990 changed to Sec view*/
773 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_TO_REF_SEC_V';
774
775 l_node_counter := l_node_counter + 1;
776
777 IF (FND_PROFILE.VALUE('IEU_TASK_TYPES') = 'Y') THEN
778 IF (FND_PROFILE.VALUE('IEU_CLI_UI_SHOW_ALL_NODES') = 'N') THEN
779
780 FOR cur_rec IN c_team_own_task_nodes_1 LOOP
781
782 l_team_own_tk_bind_list(1).bind_var_name := ':resource_id';
783 l_team_own_tk_bind_list(1).bind_var_value := p_resource_id;
784 l_team_own_tk_bind_list(1).bind_var_data_type := 'NUMBER';
785 l_team_own_tk_bind_list(2).bind_var_name := ':task_type_id';
786 l_team_own_tk_bind_list(2).bind_var_value := cur_rec.task_type_id;
787 l_team_own_tk_bind_list(2).bind_var_data_type := 'NUMBER';
788 l_team_own_tk_bind_list(3).bind_var_name := ':delete_flag';
789 l_team_own_tk_bind_list(3).bind_var_value := 'N';
790 l_team_own_tk_bind_list(3).bind_var_data_type := 'CHAR';
791
792 l_tk_list(l_node_counter).NODE_LABEL := cur_rec.task_type;
793 /* Bug 16059990 changed to Sec view*/
794 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_TO_SEC_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 /* Bug 16059990 changed to Sec view*/
809 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_TO_REF_SEC_V';
810
811
812 l_node_counter := l_node_counter + 1;
813
814 END LOOP;
815
816 ELSE
817
818 FOR cur_rec IN c_task_nodes_2 LOOP
819
820 -- insert the bind variable names and values into l_bind_list
821
822 l_team_own_tk_bind_list(1).bind_var_name := ':resource_id';
823 l_team_own_tk_bind_list(1).bind_var_value := p_resource_id;
824 l_team_own_tk_bind_list(1).bind_var_data_type := 'NUMBER';
825 l_team_own_tk_bind_list(2).bind_var_name := ':task_type_id';
826 l_team_own_tk_bind_list(2).bind_var_value := cur_rec.task_type_id;
827 l_team_own_tk_bind_list(2).bind_var_data_type := 'NUMBER';
828 l_team_own_tk_bind_list(3).bind_var_name := ':delete_flag';
829 l_team_own_tk_bind_list(3).bind_var_value := 'N';
830 l_team_own_tk_bind_list(3).bind_var_data_type := 'CHAR';
831
832 l_tk_list(l_node_counter).NODE_LABEL := cur_rec.task_type;
833 /* Bug 16059990 changed to Sec view*/
837 l_tk_list(l_node_counter).WHERE_CLAUSE := ' exists ( select m.team_resource_id
834 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_TO_SEC_V';
835 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_TO_DS';
836 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
838 from jtf_rs_team_members m
839 where m.team_id = owner_id
840 and nvl(delete_flag,'||''''||'N'||''''||') = '||':delete_flag'||
841 ' and m.team_resource_id = :resource_id) and
842 TASK_TYPE_ID = :task_type_id';
843 l_tk_list(l_node_counter).NODE_TYPE := 0;
844 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
845 l_tk_list(l_node_counter).NODE_DEPTH := 3;
846 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_team_own_tk_bind_list);
847 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
848 /* Bug 16059990 changed to Sec view*/
849 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_TO_REF_SEC_V';
850
851 l_node_counter := l_node_counter + 1;
852
853 END LOOP;
854
855 END IF;
856 END IF; -- Task Types
857
858 -- Team Assigned
859
860 l_lookup_type := 'IEU_NODE_LABELS';
861 l_view_application_id := 696;
862 l_lookup_code := 'IEU_TEAM_ASSIGN_LBL';
863
864 execute immediate l_sql_stmt into l_node_label using l_lookup_type,l_view_application_id,l_lookup_code;
865
866
867 /* Select meaning into l_node_label
868 from fnd_lookup_values_vl
869 where lookup_type = 'IEU_NODE_LABELS'
870 and view_application_id = 696
871 and lookup_code = 'IEU_TEAM_ASSIGN_LBL'; */
872
873 l_team_asg_tk_bind_list(1).bind_var_name := ':resource_id';
874 l_team_asg_tk_bind_list(1).bind_var_value := p_resource_id;
875 l_team_asg_tk_bind_list(1).bind_var_data_type := 'NUMBER';
876 l_team_asg_tk_bind_list(2).bind_var_name := ':delete_flag';
877 l_team_asg_tk_bind_list(2).bind_var_value := 'N';
878 l_team_asg_tk_bind_list(2).bind_var_data_type := 'CHAR';
879
880 l_tk_list(l_node_counter).NODE_LABEL := l_node_label;
881 /* Bug 16059990 changed to Sec view*/
882 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_TA_SEC_V';
883 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_TA_DS';
884 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
885 l_tk_list(l_node_counter).WHERE_CLAUSE := ' exists ( select m.team_resource_id
886 from jtf_rs_team_members m
887 where m.team_id = owner_id
888 and nvl(delete_flag,'||''''||'N'||''''||') = '||':delete_flag'||
889 ' and m.team_resource_id = :resource_id)';
890 l_tk_list(l_node_counter).NODE_TYPE := 0;
891 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
892 l_tk_list(l_node_counter).NODE_DEPTH := 2;
893 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_team_asg_tk_bind_list);
894 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
895 /* Bug 16059990 changed to Sec view*/
896 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_TA_REF_SEC_V';
897 l_node_counter := l_node_counter + 1;
898
899 IF (FND_PROFILE.VALUE('IEU_TASK_TYPES') = 'Y') THEN
900 IF (FND_PROFILE.VALUE('IEU_CLI_UI_SHOW_ALL_NODES') = 'N') THEN
901
902 FOR cur_rec IN c_assign_team_task_nodes_1 LOOP
903
904 l_team_asg_tk_bind_list(1).bind_var_name := ':resource_id';
905 l_team_asg_tk_bind_list(1).bind_var_value := p_resource_id;
906 l_team_asg_tk_bind_list(1).bind_var_data_type := 'NUMBER';
907 l_team_asg_tk_bind_list(2).bind_var_name := ':task_type_id';
908 l_team_asg_tk_bind_list(2).bind_var_value := cur_rec.task_type_id;
909 l_team_asg_tk_bind_list(2).bind_var_data_type := 'NUMBER';
910 l_team_asg_tk_bind_list(3).bind_var_name := ':delete_flag';
911 l_team_asg_tk_bind_list(3).bind_var_value := 'N';
912 l_team_asg_tk_bind_list(3).bind_var_data_type := 'CHAR';
913
914 l_tk_list(l_node_counter).NODE_LABEL := cur_rec.task_type;
915 /* Bug 16059990 changed to Sec view*/
916 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_TA_SEC_V';
917 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_TA_DS';
918 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
919 l_tk_list(l_node_counter).WHERE_CLAUSE := ' exists ( select m.team_resource_id
920 from jtf_rs_team_members m
921 where m.team_id = owner_id
922 and nvl(delete_flag,'||''''||'N'||''''||') = '||':delete_flag'||
923 ' and m.team_resource_id = :resource_id) and
924 TASK_TYPE_ID = :task_type_id';
925 l_tk_list(l_node_counter).NODE_TYPE := 0;
926 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
927 l_tk_list(l_node_counter).NODE_DEPTH := 3;
928 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_team_asg_tk_bind_list);
929 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
930 /* Bug 16059990 changed to Sec view*/
931 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_TA_REF_SEC_V';
932
933 l_node_counter := l_node_counter + 1;
934
935 END LOOP;
936
937 ELSE
938
939 FOR cur_rec IN c_task_nodes_2 LOOP
940
941 -- insert the bind variable names and values into l_bind_list
942
943 l_team_asg_tk_bind_list(1).bind_var_name := ':resource_id';
944 l_team_asg_tk_bind_list(1).bind_var_value := p_resource_id;
945 l_team_asg_tk_bind_list(1).bind_var_data_type := 'NUMBER';
946 l_team_asg_tk_bind_list(2).bind_var_name := ':task_type_id';
947 l_team_asg_tk_bind_list(2).bind_var_value := cur_rec.task_type_id;
948 l_team_asg_tk_bind_list(2).bind_var_data_type := 'NUMBER';
949 l_team_asg_tk_bind_list(3).bind_var_name := ':delete_flag';
950 l_team_asg_tk_bind_list(3).bind_var_value := 'N';
951 l_team_asg_tk_bind_list(3).bind_var_data_type := 'CHAR';
952
953 l_tk_list(l_node_counter).NODE_LABEL := cur_rec.task_type;
954 /* Bug 16059990 changed to Sec view*/
955 l_tk_list(l_node_counter).VIEW_NAME := 'IEU_UWQ_TASKS_TA_SEC_V';
956 l_tk_list(l_node_counter).DATA_SOURCE := 'IEU_UWQ_TASKS_TA_DS';
957 l_tk_list(l_node_counter).MEDIA_TYPE_ID := '';
958 l_tk_list(l_node_counter).WHERE_CLAUSE := ' exists ( select m.team_resource_id
959 from jtf_rs_team_members m
960 where m.team_id = owner_id
961 and nvl(delete_flag,'||''''||'N'||''''||') = '|| ':delete_flag'||
962 ' and m.team_resource_id = :resource_id) and
963 TASK_TYPE_ID = :task_type_id';
964 l_tk_list(l_node_counter).NODE_TYPE := 0;
965 l_tk_list(l_node_counter).HIDE_IF_EMPTY := '';
966 l_tk_list(l_node_counter).NODE_DEPTH := 3;
967 l_tk_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_team_asg_tk_bind_list);
968 l_tk_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
969 /* Bug 16059990 changed to Sec view*/
970 l_tk_list(l_node_counter).REFRESH_VIEW_NAME := 'IEU_UWQ_TASK_TA_REF_SEC_V';
971
972 l_node_counter := l_node_counter + 1;
973
974 END LOOP;
975
976 END IF;
977 END IF;
978 end if ; -- show team
979
980
981 IEU_PUB.ADD_UWQ_NODE_DATA
982 (P_RESOURCE_ID,
983 P_SEL_ENUM_ID,
984 l_tk_list
985 );
986
987 EXCEPTION
988 WHEN OTHERS THEN
989 ROLLBACK TO start_enumeration;
990 RAISE;
991
992 END ENUMERATE_TASK_NODES;
993
994 END IEU_TASKS_ENUMS_PVT;