1 PACKAGE BODY jtf_task_security_pvt AS
2 /* $Header: jtftkttb.pls 120.2 2005/09/02 00:03:28 akaran ship $ */
3
4
5
6 ----
7 -- Creted on July 22, 2002 by mmarovic
8 -- This is a wrapper around FND function created to support Java API.
9 -- Please do not use it before ask Milan or Girish.
10 ----
11 PROCEDURE get_privileges
12 (
13 p_api_version IN NUMBER,
14 p_object_name IN VARCHAR2,
15 p_instance_pk1_value IN VARCHAR2 DEFAULT NULL, -- NULL= only chk global gnts
16 p_instance_pk2_value IN VARCHAR2 DEFAULT NULL,
17 p_instance_pk3_value IN VARCHAR2 DEFAULT NULL,
18 p_instance_pk4_value IN VARCHAR2 DEFAULT NULL,
19 p_instance_pk5_value IN VARCHAR2 DEFAULT NULL,
20 p_user_name IN VARCHAR2 DEFAULT NULL,
21 x_return_status OUT NOCOPY VARCHAR2,
22 x_privileges OUT NOCOPY FND_TABLE_OF_VARCHAR2_30
23 ) IS
24 l_privilege_tbl fnd_data_security.FND_PRIVILEGE_NAME_TABLE_TYPE;
25 l_index BINARY_INTEGER;
26 l_return_status VARCHAR2(1);
27 begin
28 fnd_data_security.get_functions (
29 p_api_version => 1.0,
30 p_object_name => p_object_name,
31 p_instance_pk1_value => p_instance_pk1_value,
32 p_instance_pk2_value => p_instance_pk2_value,
33 p_instance_pk3_value => p_instance_pk3_value,
34 p_instance_pk4_value => p_instance_pk4_value,
35 p_instance_pk5_value => p_instance_pk5_value,
36 p_user_name => p_user_name,
37 x_return_status => x_return_status,
38 x_privilege_tbl => l_privilege_tbl
39 );
40
41 x_privileges := FND_TABLE_OF_VARCHAR2_30();
42 if x_return_status = 'T' then
43 FOR l_index IN l_privilege_tbl.FIRST..l_privilege_tbl.LAST LOOP
44 x_privileges.EXTEND;
45 x_privileges(x_privileges.COUNT):= l_privilege_tbl(l_index);
46 END LOOP;
47 end if;
48
49 exception
50 when others then
51 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
52 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
53 fnd_msg_pub.add;
54 x_return_status := 'U' ;
55 end get_privileges;
56
57 function get_object_name ( p_object_code in varchar2 )
58 return varchar2
59 is
60 l_name varchar2(30) ;
61 begin
62 select name into l_name from jtf_objects_tl
63 where object_code = p_object_code
64 and language = userenv('lang');
65 return l_name ;
66 end ;
67
68
69
70
71 FUNCTION check_privelege_for_task (
72 p_task_id NUMBER,
73 p_resource_id NUMBER,
74 p_resource_type IN VARCHAR2
75 )
76 RETURN VARCHAR2
77 IS
78 x CHAR;
79 l_privlege_name fnd_form_functions.function_name%type;
80 err varchar2(2000);
81 l_current_privelege fnd_form_functions.function_name%type;
82
83 BEGIN
84
85 BEGIN
86 SELECT 1
87 INTO x
88 FROM jtf_task_all_assignments
89 WHERE task_id = p_task_id
90 AND resource_id = p_resource_id
91 AND resource_type_code = p_resource_type
92 AND ROWNUM < 2;
93
94 l_current_privelege := 'FULL';
95 EXCEPTION
96 WHEN NO_DATA_FOUND
97 THEN
98 BEGIN
99 SELECT 1
100 INTO x
101 FROM jtf_task_all_assignments, jtf_rs_group_members
102 WHERE task_id = p_task_id
103 AND jtf_task_all_assignments.resource_id = group_id
104 AND resource_type_code = 'RS_GROUP'
105 AND jtf_rs_group_members.resource_id = p_resource_id
106 AND ROWNUM < 2;
107
108 l_current_privelege := 'FULL';
109 EXCEPTION
110 WHEN NO_DATA_FOUND THEN
111 BEGIN
112 SELECT 1
113 INTO x
114 FROM jtf_task_all_assignments, jtf_rs_team_members
115 WHERE task_id = p_task_id
116 AND jtf_task_all_assignments.resource_id = team_id
117 AND resource_type_code = 'RS_TEAM'
118 AND jtf_rs_team_members.team_resource_id = p_resource_id
119 AND ROWNUM < 2;
120
121 l_current_privelege := 'FULL';
122 EXCEPTION
123 WHEN NO_DATA_FOUND THEN
124 begin
125 SELECT function_name
126 into l_privlege_name
127 FROM fnd_form_functions fff,
128 fnd_menu_entries fme,
129 fnd_menus fm,
130 fnd_grants fg,
131 fnd_objects,
132 jtf_tasks_b
133 WHERE fff.function_id = fme.function_id
134 AND fme.menu_id = fm.menu_id
135 and fm.menu_id = fg.menu_id
136 and fg.instance_pk1_value = jtf_tasks_b.owner_id
137 and fg.instance_pk2_value = jtf_tasks_b.owner_type_code
138 and fg.grantee_key = TO_CHAR(p_resource_id)
139 and fg.object_id = fnd_objects.object_id
140 and task_id = p_task_id
141 and obj_name = 'JTF_TASK_RESOURCE' ;
142
143
144
145 if l_privlege_name = jtf_task_utl.g_tasks_read_privelege then
146 l_current_privelege := 'READ';
147 end if ;
148
149 if l_privlege_name = jtf_task_utl.g_tasks_full_privelege then
150 l_current_privelege := 'FULL';
151 end if ;
152
153 exception
154 when no_data_found then
155 l_current_privelege := 'DENIED';
156 end ;
157 END;
158 END;
159 END;
160
161 if l_current_privelege in ('FULL' )
162 then
163 BEGIN
164 SELECT NVL (enter_from_task, 'N')
165 INTO x
166 FROM jtf_tasks_b, jtf_objects_b
167 WHERE task_id = p_task_id
168 AND source_object_type_code = object_code
169 AND ROWNUM < 2;
170
171 IF (x = 'N')
172 THEN
173 l_current_privelege := 'READ';
174 END IF;
175 EXCEPTION
176 WHEN NO_DATA_FOUND THEN
177 NULL;
178 END;
179 end if ;
180
181 return l_current_privelege;
182
183 EXCEPTION
184 when others then
185 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
186 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
187 fnd_msg_pub.add;
188 return 'UNKNOWN_ERROR' ;
189 END;
190
191
192
193
194 FUNCTION get_default_query (profilename IN VARCHAR2,p_parameter_name in varchar2)
195 RETURN NUMBER IS
196 l_query_id number := '-99';
197 BEGIN
198 BEGIN
199 SELECT query_id
200 INTO l_query_id
201 FROM jtf_perz_query_param
202 WHERE parameter_name = p_parameter_name
203 and parameter_value = 'Y'
204 and rownum < 2
205 AND QUERY_ID IN
206 ( select query_id from jtf_perz_query where profile_id in
207 ( select profile_id from jtf_perz_profile where profile_name = profilename ));
208
209 RETURN l_query_id;
210 EXCEPTION
211 WHEN NO_DATA_FOUND
212 THEN
213 BEGIN
214 SELECT query_id
215 INTO l_query_id
216 FROM jtf_perz_query
217 WHERE profile_id IN ( SELECT profile_id
218 FROM jtf_perz_profile
219 WHERE profile_name = '-99999:JTF_TASK');
220 RETURN l_query_id;
221 END;
222 END;
223 END;
224
225
226
227 FUNCTION GET_CATEGORY_ID ( p_task_id in number ,
228 p_resource_id in number ,
229 p_resource_type_code in varchar2
230 )
231 RETURN number IS
232
233 l_category_id number ;
234
235
236 BEGIN
237 select category_id into l_category_id
238 from jtf_task_all_assignments
239 where task_id = p_task_id
240 and resource_id = p_resource_id
241 and resource_type_code = p_resource_type_code
242 and rownum < 2;
243
244
245 return l_category_id ;
246
247 EXCEPTION
248 WHEN no_data_found THEN
249 return 0;
250 END;
251
252
253 FUNCTION check_private_task_privelege(
254 p_task_id IN NUMBER,
255 p_resource_id IN NUMBER,
256 p_resource_type_code IN VARCHAR2
257 )
258
259
260 RETURN varchar2 is
261
262 x char ;
263 begin
264 /* for optimization, the UI should check if the task is private .*/
265
266 select 1 into x
267 from jtf_task_all_assignments
268 where task_id = p_task_id
269 and p_resource_id = resource_id
270 and p_resource_type_code = resource_type_code ;
271
272 return 'Y' ;
273
274 exception
275 when no_data_found then
276 /* Bug 2186841 Private Tasks belonging to a group can't be queried upon */
277 BEGIN
278 SELECT 1
279 INTO x
280 FROM jtf_task_all_assignments, jtf_rs_group_members
281 WHERE task_id = p_task_id
282 AND jtf_task_all_assignments.resource_id = group_id
283 AND resource_type_code = 'RS_GROUP'
284 AND jtf_rs_group_members.resource_id = p_resource_id
285 AND ROWNUM < 2;
286 return 'Y' ;
287
288 EXCEPTION
289 WHEN NO_DATA_FOUND THEN
290 BEGIN
291 SELECT 1
292 INTO x
293 FROM jtf_task_all_assignments, jtf_rs_team_members
294 WHERE task_id = p_task_id
295 AND jtf_task_all_assignments.resource_id = team_id
296 AND resource_type_code = 'RS_TEAM'
297 AND jtf_rs_team_members.team_resource_id = p_resource_id
298 AND ROWNUM < 2;
299 return 'Y' ;
300
301 EXCEPTION
302 WHEN NO_DATA_FOUND THEN
303 return 'N' ;
304 END ;
305 END ;
306 END ;
307
308
309 procedure delete_category ( p_category_name in varchar2 )
310 is
311 l_perz_data_id number;
312
313 begin
314
315 select perz_data_id into l_perz_data_id
316 from jtf_perz_data
317 where perz_data_name = p_category_name ;
318
319 update jtf_task_all_assignments
320 set category_id = null
321 where category_id = l_perz_data_id ;
322
323 update jtf_cal_addresses
324 set category = null
325 where category = l_perz_data_id ;
326
327 delete from jtf_perz_data
328 where perz_data_id = l_perz_data_id ;
329
330 commit;
331
332 end ;
333
334
335 Function priveleges_from_other_resource
336 ( logged_in_resource in number ,
337 priveleges_from_resource_id in number ,
338 priveleges_from_resource_type in varchar2
339 )
340 RETURN varchar2 IS
341
342 l_privlege_name varchar2(30) ;
343 BEGIN
344
345 /*
346 If the logged in resource is same as the resource whose privleges are checked,
347 then return full access.
348 */
349 if priveleges_from_resource_type not in ( 'RS_GROUP', 'RS_TEAM' ) and
350 logged_in_resource = priveleges_from_resource_id
351 then
352 return 'JTF_TASK_FULL_ACCESS' ;
353 end if ;
354
355
356
357
358
359 if priveleges_from_resource_type = 'RS_GROUP' then
360 begin
361 select 1
362 into l_privlege_name
363 from jtf_rs_group_members
364 where group_id = priveleges_from_resource_id
365 and resource_id = logged_in_resource
366 and rownum < 2;
367
368 return 'JTF_TASK_FULL_ACCESS' ;
369 exception
370 when no_data_found then
371 null ;
372 --return 'DENIED';*/
373 end ;
374 end if ;
375
376 if priveleges_from_resource_type = 'RS_TEAM' then
377 begin
378 select 1
379 into l_privlege_name
380 from jtf_rs_team_members
381 where TEAM_id = priveleges_from_resource_id
382 and TEAM_resource_id = logged_in_resource
383 and rownum < 2;
384
385 return 'JTF_TASK_FULL_ACCESS' ;
386 exception
387 when no_data_found then
388 null ;
389 -- return 'DENIED';*/
390 end ;
391 end if ;
392
393 begin
394 SELECT function_name
395 into l_privlege_name
396 FROM fnd_form_functions fff,
397 fnd_menu_entries fme,
398 fnd_menus fm,
399 fnd_grants fg,
400 fnd_objects
401 WHERE fff.function_id = fme.function_id
402 AND fme.menu_id = fm.menu_id
403 and fm.menu_id = fg.menu_id
404 and fg.instance_pk1_value = priveleges_from_resource_id
405 and fg.instance_pk2_value = priveleges_from_resource_type
406 and fg.grantee_key = TO_CHAR(logged_in_resource)
407 and fg.object_id = fnd_objects.object_id
408 and obj_name = 'JTF_TASK_RESOURCE' ;
409
410 return l_privlege_name ;
411 exception
412 when no_data_found then
413 return 'DENIED';
414 end ;
415
416 END;
417
418 END;
[Home] [Help]