[Home] [Help]
PACKAGE BODY: APPS.IEU_WORKLIST_ENUMS_PVT
Source
1 PACKAGE BODY IEU_WORKLIST_ENUMS_PVT AS
2 /* $Header: IEUENWLB.pls 120.0 2005/06/02 15:52:22 appldev noship $ */
3
4 PROCEDURE ENUMERATE_WORKLIST_NODES
5 (P_RESOURCE_ID IN NUMBER
6 ,P_LANGUAGE IN VARCHAR2
7 ,P_SOURCE_LANG IN VARCHAR2
8 ,P_SEL_ENUM_ID IN NUMBER
9 )
10 AS
11
12 l_node_counter NUMBER;
13 l_node_pid NUMBER;
14 l_node_label VARCHAR2(100);
15 l_def_where VARCHAR2(20000);
16
17 l_wl_list IEU_PUB.EnumeratorDataRecordList;
18 l_bind_list IEU_PUB.BindVariableRecordList;
19 l_ind_own_bind_list IEU_PUB.BindVariableRecordList;
20 l_ind_asg_bind_list IEU_PUB.BindVariableRecordList;
21 l_grp_own_bind_list IEU_PUB.BindVariableRecordList;
22 l_grp_asg_bind_list IEU_PUB.BindVariableRecordList;
23
24 BEGIN
25
26 IF (FND_PROFILE.VALUE('IEU_QEN_WORKLIST') = 'N' ) THEN
27 RETURN;
28 END IF;
29
30 l_node_counter := 0;
31
32 SAVEPOINT start_enumeration;
33
34 Select meaning into l_node_label
35 from fnd_lookup_values_vl
36 where lookup_type = 'IEU_NODE_LABELS'
37 and view_application_id = 696
38 and lookup_code = 'IEU_WORKLIST_LBL';
39
40 l_bind_list(1).bind_var_name := ':owner_id';
41 l_bind_list(1).bind_var_value := p_resource_id;
42 l_bind_list(1).bind_var_data_type :='NUMBER';
43 l_bind_list(2).bind_var_name := ':assignee_id';
44 l_bind_list(2).bind_var_value := p_resource_id;
45 l_bind_list(2).bind_var_data_type := 'NUMBER';
46
47 l_wl_list(l_node_counter).NODE_LABEL := l_node_label;
48 l_wl_list(l_node_counter).VIEW_NAME := 'IEU_UWQM_WL_NODE_V';
49 l_wl_list(l_node_counter).DATA_SOURCE := 'IEU_UWQM_WL_NODE_DS';
50 l_wl_list(l_node_counter).MEDIA_TYPE_ID := '';
51 l_wl_list(l_node_counter).WHERE_CLAUSE := 'resource_id = :owner_id or resource_id = :assignee_id';
52 l_wl_list(l_node_counter).NODE_TYPE := 1;
53 l_wl_list(l_node_counter).HIDE_IF_EMPTY := '';
54 l_wl_list(l_node_counter).NODE_DEPTH := 1;
55 l_wl_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_bind_list);
56 l_wl_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
57
58 /*
59
60 l_node_counter := l_node_counter + 1;
61
62 --Now build the subnodes
63
64 -- Owned by Me
65
66 Select meaning into l_node_label
67 from fnd_lookup_values_vl
68 where lookup_type = 'IEU_NODE_LABELS'
69 and view_application_id = 696
70 and lookup_code = 'IEU_MY_OWN_LBL';
71
72 l_ind_own_bind_list(1).bind_var_name := ':owner_id';
73 l_ind_own_bind_list(1).bind_var_value := p_resource_id;
74 l_ind_own_bind_list(1).bind_var_data_type :='NUMBER';
75
76 l_wl_list(l_node_counter).NODE_LABEL := l_node_label;
77 l_wl_list(l_node_counter).VIEW_NAME := 'IEU_UWQM_WORKLIST_V';
78 l_wl_list(l_node_counter).DATA_SOURCE := 'IEU_UWQM_WORKLIST_DS';
79 l_wl_list(l_node_counter).MEDIA_TYPE_ID := '';
80 l_wl_list(l_node_counter).WHERE_CLAUSE := ' owner_type = '||''''||'RS_INDIVIDUAL'||''''||
81 ' and owner_id = :owner_id and (status_id <> 3 or status_id <> 4) ';
82 l_wl_list(l_node_counter).NODE_TYPE := 2;
83 l_wl_list(l_node_counter).HIDE_IF_EMPTY := '';
84 l_wl_list(l_node_counter).NODE_DEPTH := 2;
85 l_wl_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_ind_own_bind_list);
86 l_wl_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
87 l_node_counter := l_node_counter + 1;
88
89
90 -- Assigned to Me
91
92 Select meaning into l_node_label
93 from fnd_lookup_values_vl
94 where lookup_type = 'IEU_NODE_LABELS'
95 and view_application_id = 696
96 and lookup_code = 'IEU_MY_ASSIGN_LBL';
97
98 l_ind_asg_bind_list(1).bind_var_name := ':assignee_id';
99 l_ind_asg_bind_list(1).bind_var_value := p_resource_id;
100 l_ind_asg_bind_list(1).bind_var_data_type := 'NUMBER';
101
102 l_wl_list(l_node_counter).NODE_LABEL := l_node_label;
103 l_wl_list(l_node_counter).VIEW_NAME := 'IEU_UWQM_WORKLIST_V';
104 l_wl_list(l_node_counter).DATA_SOURCE := 'IEU_UWQM_WORKLIST_DS';
105 l_wl_list(l_node_counter).MEDIA_TYPE_ID := '';
106 l_wl_list(l_node_counter).WHERE_CLAUSE := 'assignee_type = '||''''||'RS_INDIVIDUAL'||''''||
107 'and assignee_id = :assignee_id and (status_id <> 3 or status_id <> 4)';
108 l_wl_list(l_node_counter).NODE_TYPE := 3;
109 l_wl_list(l_node_counter).HIDE_IF_EMPTY := '';
110 l_wl_list(l_node_counter).NODE_DEPTH := 2;
111 l_wl_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_ind_asg_bind_list);
112 l_wl_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
113 l_node_counter := l_node_counter + 1;
114
115 -- Owned by My Groups
116
117 Select meaning into l_node_label
118 from fnd_lookup_values_vl
119 where lookup_type = 'IEU_NODE_LABELS'
120 and view_application_id = 696
121 and lookup_code = 'IEU_GRP_OWN_LBL';
122
123 l_grp_own_bind_list(1).bind_var_name := ':owner_id';
124 l_grp_own_bind_list(1).bind_var_value := p_resource_id;
125 l_grp_own_bind_list(1).bind_var_data_type :='NUMBER';
126
127
128 l_wl_list(l_node_counter).NODE_LABEL := l_node_label;
129 l_wl_list(l_node_counter).VIEW_NAME := 'IEU_UWQM_WORKLIST_V';
130 l_wl_list(l_node_counter).DATA_SOURCE := 'IEU_UWQM_WORKLIST_DS';
131 l_wl_list(l_node_counter).MEDIA_TYPE_ID := '';
132 l_wl_list(l_node_counter).WHERE_CLAUSE := 'owner_type = '||''''||'RS_GROUP'||''''||' and owner_id in
133 ( select group_id from jtf_rs_group_members where resource_id= :owner_id ) and (status_id <> 3 or status_id <> 4) ';
134 l_wl_list(l_node_counter).NODE_TYPE := 4;
135 l_wl_list(l_node_counter).HIDE_IF_EMPTY := '';
136 l_wl_list(l_node_counter).NODE_DEPTH := 2;
137 l_wl_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_grp_own_bind_list);
138 l_wl_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
139 l_node_counter := l_node_counter + 1;
140
141 -- Assigned To My Groups
142
143 Select meaning into l_node_label
144 from fnd_lookup_values_vl
145 where lookup_type = 'IEU_NODE_LABELS'
146 and view_application_id = 696
147 and lookup_code = 'IEU_GRP_ASSIGN_LBL';
148
149 l_grp_asg_bind_list(1).bind_var_name := ':assignee_id';
150 l_grp_asg_bind_list(1).bind_var_value := p_resource_id;
151 l_grp_asg_bind_list(1).bind_var_data_type := 'NUMBER';
152
153 l_wl_list(l_node_counter).NODE_LABEL := l_node_label;
154 l_wl_list(l_node_counter).VIEW_NAME := 'IEU_UWQM_WORKLIST_V';
155 l_wl_list(l_node_counter).DATA_SOURCE := 'IEU_UWQM_WORKLIST_DS';
156 l_wl_list(l_node_counter).MEDIA_TYPE_ID := '';
157 l_wl_list(l_node_counter).WHERE_CLAUSE := 'assignee_type = '||''''||'RS_GROUP'||''''||' and assignee_id in
158 ( select group_id from jtf_rs_group_members where resource_id= :assignee_id ) and (status_id <> 3 or status_id <> 4) ';
159 l_wl_list(l_node_counter).NODE_TYPE := 5;
160 l_wl_list(l_node_counter).HIDE_IF_EMPTY := '';
161 l_wl_list(l_node_counter).NODE_DEPTH := 2;
162 l_wl_list(l_node_counter).BIND_VARS := ieu_pub.set_bind_var_data(l_grp_asg_bind_list);
163 l_wl_list(l_node_counter).RES_CAT_ENUM_FLAG := 'N';
164 l_node_counter := l_node_counter + 1;
165
166 */
167
168 IEU_PUB.ADD_UWQ_NODE_DATA
169 (P_RESOURCE_ID,
170 P_SEL_ENUM_ID,
171 l_wl_list
172 );
173
174
175 END ENUMERATE_WORKLIST_NODES;
176
177 PROCEDURE REFRESH_WORKLIST_NODES( P_RESOURCE_ID IN NUMBER, P_NODE_ID IN NUMBER, X_COUNT OUT NOCOPY NUMBER)
178 AS
179
180 l_curr_count NUMBER;
181 -- l_ind_asg_count NUMBER;
182 -- l_grp_own_count NUMBER;
183 -- l_grp_asg_count NUMBER;
184 l_node_type NUMBER(10);
185 l_owner_type_ind varchar2(25);
186 l_owner_type_grp varchar2(25);
187 l_count number(20) := 0;
188
189 BEGIN
190
191 l_owner_type_ind := 'RS_INDIVIDUAL';
192 l_owner_type_grp := 'RS_GROUP';
193 x_count := 0;
194
195 select node_type
196 into l_node_type
197 from ieu_uwq_sel_rt_nodes
198 where resource_id=P_RESOURCE_ID
199 and node_id=P_NODE_ID;
200
201 l_count := IEU_UWQ_GET_NEXT_WORK_PVT.GET_WORKLIST_QUEUE_COUNT(p_resource_id, 0, l_node_type);
202 x_count := x_count + l_count;
203 l_count := 0;
204
205 l_count := IEU_UWQ_GET_NEXT_WORK_PVT.GET_WORKLIST_QUEUE_COUNT(p_resource_id, 1, l_node_type);
206 x_count := x_count + l_count;
207 l_count := 0;
208
209 l_count := IEU_UWQ_GET_NEXT_WORK_PVT.GET_WORKLIST_QUEUE_COUNT(p_resource_id, 2, l_node_type);
210 x_count := x_count + l_count;
211
212 /*
213
214 if l_node_type = 1 then
215 begin
216 select count(*)
217 into x_count
218 from ieu_uwqm_items where
219 (((owner_id in (select group_id from jtf_rs_group_members where
220 resource_id=p_resource_id ) and owner_type=l_owner_type_grp)
221 OR (owner_id=p_resource_id AND owner_type = l_owner_type_ind))
222 OR
223 ((assignee_id in (select group_id from jtf_rs_group_members where
224 resource_id=p_resource_id ) and assignee_type=l_owner_type_grp)
225 OR (assignee_id=p_resource_id AND assignee_type = l_owner_type_ind)))
226 and status_id not in (3,4);
227 exception
228 when no_data_found then null;
229 end;
230 end if;
231
232
233 if ( (l_node_type = 1) or (l_node_type = 2) )
234 then
235 REFRESH_IND_OWN_WL_NODES(P_RESOURCE_ID, L_CURR_COUNT);
236 x_count := x_count + l_curr_count;
237 end if ;
238
239 if ( (l_node_type = 1) or (l_node_type = 3) )
240 then
241 REFRESH_IND_ASG_WL_NODES(P_RESOURCE_ID, L_CURR_COUNT);
242 x_count := x_count + l_curr_count;
243 end if;
244
245 if ( (l_node_type = 1) or (l_node_type = 4) )
246 then
247 REFRESH_GRP_OWN_WL_NODES(P_RESOURCE_ID, L_CURR_COUNT);
248 x_count := x_count + l_curr_count;
249 end if;
250
251 if ( (l_node_type = 1) or (l_node_type = 5) )
252 then
253 REFRESH_GRP_ASG_WL_NODES(P_RESOURCE_ID, L_CURR_COUNT);
254 x_count := x_count + l_curr_count;
255 end if;
256 */
257
258 END REFRESH_WORKLIST_NODES;
259
260
261 PROCEDURE REFRESH_IND_OWN_WL_NODES(P_RESOURCE_ID IN NUMBER, X_IND_OWN_COUNT OUT NOCOPY NUMBER)
262 AS
263
264 BEGIN
265
266 select count(*)
267 into x_ind_own_count
268 -- from IEU_UWQ_WORKLIST_V
269 from ieu_uwqm_items
270 where owner_type = 'RS_INDIVIDUAL'
271 and owner_id = p_resource_id
272 and (status_id <> 3 or status_id <> 4);
273
274 END REFRESH_IND_OWN_WL_NODES;
275
276
277 PROCEDURE REFRESH_GRP_OWN_WL_NODES(P_RESOURCE_ID IN NUMBER, X_GRP_OWN_COUNT OUT NOCOPY NUMBER)
278 AS
279
280 BEGIN
281
282 select count(*)
283 into x_grp_own_count
284 from ieu_uwqm_items
285 where owner_type ='RS_GROUP'
286 and owner_id in
287 (
288 select group_id from jtf_rs_group_members
289 where resource_id= p_resource_id
290 )
291 and (status_id <> 3 or status_id <> 4);
292
293 END REFRESH_GRP_OWN_WL_NODES;
294
295 PROCEDURE REFRESH_IND_ASG_WL_NODES(P_RESOURCE_ID IN NUMBER, X_IND_ASG_COUNT OUT NOCOPY NUMBER)
296 AS
297
298 BEGIN
299
300 select count(*)
301 into x_ind_asg_count
302 from ieu_uwqm_items
303 where assignee_type ='RS_INDIVIDUAL'
304 and assignee_id = p_resource_id
305 and (status_id <> 3 or status_id <> 4);
306
307 END REFRESH_IND_ASG_WL_NODES;
308
309 PROCEDURE REFRESH_GRP_ASG_WL_NODES(P_RESOURCE_ID IN NUMBER, X_GRP_ASG_COUNT OUT NOCOPY NUMBER)
310 AS
311
312 BEGIN
313
314 select count(*)
315 into x_grp_asg_count
316 from ieu_uwqm_items
317 where
318 assignee_type = 'RS_GROUP'
319 and assignee_id in
320 (
321 select group_id from jtf_rs_group_members
322 where resource_id= p_resource_id
323 )
324 and (status_id <> 3 or status_id <> 4);
325
326 END REFRESH_GRP_ASG_WL_NODES;
327
328
329 END IEU_WORKLIST_ENUMS_PVT;