1 PACKAGE BODY OKE_K_SECURITY_PKG AS
2 /* $Header: OKEKSECB.pls 120.2 2005/11/07 18:26:52 ifilimon noship $ */
3
4 --
5 -- Private Global Cached Variables
6 --
7 G_A_Hdr_ID NUMBER := NULL;
8 G_A_User_ID NUMBER := NULL;
9 G_A_Emp_ID NUMBER := NULL;
10 G_Access_Level VARCHAR2(30) := NULL;
11 G_R_Hdr_ID NUMBER := NULL;
12 G_R_User_ID NUMBER := NULL;
13 G_R_Emp_ID NUMBER := NULL;
14 G_Role_ID NUMBER := NULL;
15 G_Assignment_Date DATE := NULL;
16
17 --
18 -- Private Global Cursors
19 --
20 CURSOR G_Emp_CSR ( C_User_ID NUMBER ) IS
21 SELECT employee_id
22 FROM fnd_user
23 WHERE user_id = C_User_ID;
24
25 CURSOR G_Owner_CSR ( C_K_Header_ID NUMBER
26 , C_User_ID NUMBER )
27 IS
28 SELECT decode(count(1),1,'EDIT','NONE')
29 FROM okc_k_headers_all_b
30 WHERE id = C_K_Header_ID
31 AND created_by = C_User_ID;
32
33 --
34 -- Name : Get_K_Access
35 -- Pre-reqs : FND_GLOBAL.INITIALIZE
36 -- Function : This function returns the access level of
37 -- the current user for the given contract
38 --
39 -- Parameters :
40 -- IN : P_K_HEADER_ID NUMBER
41 -- OUT : None
42 --
43 -- Returns : VARCHAR2
44 --
45 -- Note : The return value is cached for performance
46 -- reasons. If you need to have real-time
47 -- information, you should use the functions
48 -- Get_User_K_Access() or Get_Emp_K_Access() instead.
49 --
50
51 FUNCTION Get_K_Access
52 ( P_K_Header_ID IN NUMBER
53 ) RETURN VARCHAR2 IS
54
55 BEGIN
56 --
57 -- If input parameter is not given, there is no need to go any
58 -- further.
59 --
60 IF ( P_K_Header_ID IS NULL ) THEN
61 RETURN ( NULL );
62 END IF;
63
64 --
65 -- The input and output are all cached into global variables to
66 -- speed up repeated lookups. If the input parameters of the
67 -- current lookup matches the input parameters of the previous
68 -- lookup, the cached result will be used instead of hitting the DB
69 -- again.
70 --
71 IF ( G_Access_Level IS NULL
72 OR G_A_User_ID <> FND_GLOBAL.User_ID
73 OR G_A_Hdr_ID <> P_K_Header_ID ) THEN
74 --
75 -- No cache output or input parameters have changed; recache
76 --
77 G_Access_Level := Get_Emp_K_Access ( P_K_Header_ID
78 , OKE_UTILS.Curr_Emp_ID );
79 G_A_Hdr_ID := P_K_Header_ID;
80 G_A_User_ID := FND_GLOBAL.User_ID;
81
82 IF ( G_Access_Level = G_NO_ACCESS ) THEN
83 --
84 -- Current user is not defined in contract role, check to
85 -- see if he/she is the creator of the record
86 --
87 OPEN G_Owner_CSR ( G_A_Hdr_ID , G_A_User_ID );
88 FETCH G_Owner_CSR INTO G_Access_Level;
89 CLOSE G_Owner_CSR;
90
91 END IF;
92
93 END IF;
94
95 RETURN ( G_Access_Level );
96
97 EXCEPTION
98 WHEN OTHERS THEN
99 RETURN ( NULL );
100
101 END;
102
103
104 --
105 -- Name : Get_User_K_Access
106 -- Pre-reqs : None
107 -- Function : This function returns the access level of
108 -- the given user for the given contract
109 --
110 -- Parameters :
111 -- IN : K_HEADER_ID NUMBER
112 -- K_USER_ID NUMBER
113 -- OUT : None
114 --
115 -- Returns : VARCHAR2
116 --
117
118 FUNCTION Get_User_K_Access
119 ( P_K_Header_ID IN NUMBER
120 , P_User_ID IN NUMBER
121 ) RETURN VARCHAR2 IS
122
123 L_Emp_ID NUMBER;
124 L_Access_Level VARCHAR2(30);
125
126 BEGIN
127 --
128 -- If input parameter is not given, there is no need to go any
129 -- further.
130 --
131 IF ( P_K_Header_ID IS NULL OR NVL(P_User_ID , -1) = -1 ) THEN
132 RETURN ( NULL );
133 END IF;
134
135 L_Emp_ID := NULL;
136
137 --
138 -- Getting employee information for the given user from FND_USER.
139 -- Access information are kept by employees.
140 --
141 OPEN G_Emp_CSR ( P_User_ID );
142 FETCH G_Emp_CSR INTO L_Emp_ID;
143 CLOSE G_Emp_CSR;
144
145 --
146 -- If employee is not linked to the user, stop here.
147 --
148 IF ( L_Emp_ID IS NULL ) THEN
149 RETURN ( NULL );
150 END IF;
151
152 L_Access_Level := Get_Emp_K_Access ( P_K_Header_ID , L_Emp_ID );
153
154 IF ( L_Access_Level = G_NO_ACCESS ) THEN
155 --
156 -- Current user is not defined in contract role, check to
157 -- see if he/she is the creator of the record
158 --
159 OPEN G_Owner_CSR ( P_K_Header_ID , P_User_ID );
160 FETCH G_Owner_CSR INTO L_Access_Level;
161 CLOSE G_Owner_CSR;
162 END IF;
163
164 RETURN ( L_Access_Level );
165
166 EXCEPTION
167 WHEN OTHERS THEN
168 RETURN ( NULL );
169
170 END Get_User_K_Access;
171
172
173 --
174 -- Name : Get_Emp_K_Access
175 -- Pre-reqs : None
176 -- Function : This function returns the access level of
177 -- the given employee for the given contract
178 --
179 -- Parameters :
180 -- IN : P_K_HEADER_ID NUMBER
181 -- P_EMP_ID NUMBER
182 -- OUT : None
183 --
184 -- Returns : VARCHAR2
185 --
186
187 FUNCTION Get_Emp_K_Access
188 ( P_K_Header_ID IN NUMBER
189 , P_Emp_ID IN NUMBER
190 ) RETURN VARCHAR2 IS
191
192 CURSOR csr ( C_Role_ID NUMBER )
193 IS
194 SELECT default_access_level
195 FROM pa_project_role_types prt
196 WHERE prt.project_role_id = C_Role_ID;
197
198 L_Access_Level VARCHAR2(30);
199 L_Role_ID NUMBER;
200
201 BEGIN
202 --
203 -- If input parameter is not given, there is no need to go any
204 -- further.
205 --
206 IF ( P_K_Header_ID IS NULL OR P_Emp_ID IS NULL ) THEN
207 RETURN ( NULL );
208 END IF;
209
210 --
211 -- First get the contract role for the given employee and
212 -- contract.
213 --
214 L_Role_ID := Get_Emp_K_Role ( P_K_Header_ID , P_Emp_ID );
215
216 --
217 -- If No role is found, the employee is not allowed to access
218 -- the contract.
219 --
220 IF ( L_Role_ID IS NULL ) THEN
221 RETURN( G_NO_ACCESS );
222 END IF;
223
224 --
225 -- Now, get the default access level from the role definition.
226 --
227 OPEN csr ( L_Role_ID );
228 FETCH csr INTO L_Access_Level;
229
230 --
231 -- There is a weird situation; the employee is assigned a certain
232 -- role but the role does not exist. It probably means a role is
233 -- deleted by mistake.
234 --
235 IF ( csr%notfound ) THEN
236 CLOSE csr;
237 L_Access_Level := G_NO_ACCESS;
238 ELSE
239 CLOSE csr;
240 END IF;
241
242 RETURN ( L_Access_Level );
243
244 EXCEPTION
245 WHEN OTHERS THEN
246 RETURN ( NULL );
247
248 END Get_Emp_K_Access;
249
250
251 --
252 -- Name : Get_K_Role
253 -- Pre-reqs : FND_GLOBAL.INITIALIZE
254 -- Function : This function returns the role of the
255 -- current user for the given contract
256 --
257 -- Parameters :
258 -- IN : P_K_HEADER_ID NUMBER
259 -- OUT : None
260 --
261 -- Returns : VARCHAR2
262 --
263 -- Note : The return value is cached for performance
264 -- reasons. If you need to have real-time
265 -- information, you should use the functions
266 -- Get_User_K_Access() or Get_Emp_K_Access() instead.
267 --
268
269 FUNCTION Get_K_Role
270 ( P_K_Header_ID IN NUMBER
271 ) RETURN VARCHAR2 IS
272
273 BEGIN
274 --
275 -- If input parameter is not given, there is no need to go any
276 -- further.
277 --
278 IF ( P_K_Header_ID IS NULL ) THEN
279 RETURN ( NULL );
280 END IF;
281
282 --
283 -- The input and output are all cached into global variables to
284 -- speed up repeated lookups. If the input parameters of the
285 -- current lookup matches the input parameters of the previous
286 -- lookup, the cached result will be used instead of hitting the DB
287 -- again.
288 --
289 IF ( G_Role_ID IS NULL
290 OR G_R_User_ID <> FND_GLOBAL.User_ID
291 OR G_R_Hdr_ID <> P_K_Header_ID ) THEN
292 --
293 -- No cache output or input parameters have changed; recache
294 --
295 G_Role_ID := Get_Emp_K_Role ( P_K_Header_ID
296 , OKE_UTILS.Curr_Emp_ID );
297 G_R_Hdr_ID := P_K_Header_ID;
298 G_R_User_ID := FND_GLOBAL.User_ID;
299
300 END IF;
301
302 RETURN ( G_Role_ID );
303
304 EXCEPTION
305 WHEN OTHERS THEN
306 RETURN ( NULL );
307
308 END Get_K_Role;
309
310
311 --
312 -- Name : Get_User_K_Role
313 -- Pre-reqs : None
314 -- Function : This function returns the role of the
315 -- given user for the given contract
316 --
317 -- Parameters :
318 -- IN : K_HEADER_ID NUMBER
319 -- K_USER_ID NUMBER
320 -- OUT : None
321 --
322 -- Returns : VARCHAR2
323 --
324
325 FUNCTION Get_User_K_Role
326 ( P_K_Header_ID IN NUMBER
327 , P_User_ID IN NUMBER
328 ) RETURN VARCHAR2 IS
329
330 L_Emp_ID NUMBER;
331
332 BEGIN
333 --
334 -- If input parameter is not given, there is no need to go any
335 -- further.
336 --
337 IF ( P_K_Header_ID IS NULL OR NVL(P_User_ID , -1) = -1 ) THEN
338 RETURN ( NULL );
339 END IF;
340
341 L_Emp_ID := NULL;
342
343 --
344 -- Getting employee information for the given user from FND_USER.
345 -- Access information are kept by employees.
346 --
347 OPEN G_Emp_CSR ( P_User_ID );
348 FETCH G_Emp_CSR INTO L_Emp_ID;
349 CLOSE G_Emp_CSR;
350
351 --
352 -- If employee is not linked to the user, stop here.
353 --
354 IF ( L_Emp_ID IS NULL ) THEN
355 RETURN ( NULL );
356 END IF;
357
358 RETURN ( Get_Emp_K_Role ( P_K_Header_ID , L_Emp_ID ) );
359
360 EXCEPTION
361 WHEN OTHERS THEN
362 RETURN ( NULL );
363
364 END Get_User_K_Role;
365
366
367 --
368 -- Name : Get_Emp_K_Role
369 -- Pre-reqs : None
370 -- Function : This function returns the role of the
371 -- given employee for the given contract
372 --
373 -- Parameters :
374 -- IN : P_K_HEADER_ID NUMBER
375 -- P_EMP_ID NUMBER
376 -- OUT : None
377 --
378 -- Returns : VARCHAR2
379 --
380
381 FUNCTION Get_Emp_K_Role
382 ( P_K_Header_ID IN NUMBER
383 , P_Emp_ID IN NUMBER
384 ) RETURN VARCHAR2 IS
385
386 CURSOR csr ( C_K_Header_ID NUMBER
387 , C_Emp_ID NUMBER )
388 IS
389 /*
390 This version somehow results in FTS
391
392 SELECT project_role_id
393 FROM pa_project_parties pp
394 , oke_k_headers kh
395 WHERE kh.k_header_id = C_K_Header_ID
396 AND ( ( pp.object_type = 'OKE_K_HEADERS'
397 AND pp.object_id = kh.k_header_id
398 )
399 OR ( pp.object_type = 'OKE_PROGRAMS'
400 AND pp.object_id in (kh.program_id , 0)
401 )
402 )
403 AND pp.resource_type_id = 101
404 AND pp.resource_source_id = C_Emp_ID
405 AND sysdate
406 BETWEEN nvl( pp.start_date_active , sysdate - 1)
407 AND nvl( pp.end_date_active , sysdate + 1)
408 */
409 --
410 -- First part of the union retrieves contract level as well as
411 -- site level assignment
412 --
413 SELECT Project_Role_ID
414 , decode(pp.object_type, 'OKE_K_HEADERS', 1, 3) Sort_Order
415 FROM pa_project_parties pp
416 WHERE ( pp.object_type , pp.object_id ) IN
417 ( ( 'OKE_K_HEADERS' , C_K_Header_ID )
418 , ( 'OKE_PROGRAMS' , 0 )
419 )
420 AND pp.resource_type_id = 101
421 AND pp.resource_source_id = C_Emp_ID
422 AND trunc(sysdate)
423 BETWEEN nvl( trunc(pp.start_date_active) , trunc(sysdate) - 1)
424 AND nvl( trunc(pp.end_date_active) , trunc(sysdate) + 1)
425 UNION ALL
426 --
427 -- Second part of the union retrieves program level assignment.
428 -- This is separated from the first part because it requires a
429 -- join to OKE_K_HEADERS and for some reason the combined SELECT
430 -- results in a FTS of PA_PROJECT_PARTIES.
431 --
432 SELECT Project_Role_ID
433 , 2
434 FROM pa_project_parties pp
435 , oke_k_headers kh
436 WHERE kh.k_header_id = C_K_Header_ID
437 AND pp.object_type = 'OKE_PROGRAMS'
438 AND pp.object_id = kh.program_id
439 AND pp.resource_type_id = 101
440 AND pp.resource_source_id = C_Emp_ID
441 AND trunc(sysdate)
442 BETWEEN nvl( trunc(pp.start_date_active) , trunc(sysdate) - 1)
443 AND nvl( trunc(pp.end_date_active) , trunc(sysdate) + 1)
444 ORDER BY 2;
445
446 L_Role_ID NUMBER;
447 L_Dummy NUMBER;
448
449 BEGIN
450 --
451 -- If input parameter is not given, there is no need to go any
452 -- further.
453 --
454 IF ( P_K_Header_ID IS NULL OR P_Emp_ID IS NULL ) THEN
455 RETURN ( NULL );
456 END IF;
457
458 OPEN csr ( P_K_Header_ID , P_Emp_ID );
459 FETCH csr INTO L_Role_ID , L_Dummy;
460 CLOSE csr;
461
462 RETURN ( L_Role_ID );
463
464 EXCEPTION
465 WHEN OTHERS THEN
466 RETURN ( NULL );
467
468 END Get_Emp_K_Role;
469
470
471 PROCEDURE Set_Assignment_Date
472 ( P_Date IN DATE
473 ) IS
474
475 BEGIN
476
477 G_Assignment_Date := P_Date;
478
479 END Set_Assignment_Date;
480
481
482 FUNCTION Get_Assignment_Date
483 RETURN DATE IS
484 BEGIN
485
486 IF ( G_Assignment_Date IS NULL ) THEN
487 RETURN TRUNC(SYSDATE);
488 ELSE
489 RETURN G_Assignment_Date;
490 END IF;
491
492 END Get_Assignment_Date;
493
494 FUNCTION Function_Allowed
495 ( X_Role_ID IN NUMBER
496 , X_Function_Name IN VARCHAR2
497 ) RETURN VARCHAR2 IS
498
499 flag VARCHAR2(1) := 'F';
500
501 CURSOR c1 IS
502 SELECT 'T'
503 FROM fnd_form_functions ff
504 , fnd_menu_entries me
505 , pa_project_role_types prt
506 WHERE prt.project_role_id = X_Role_ID
507 AND me.menu_id = prt.menu_id
508 AND me.grant_flag = 'Y'
509 AND ff.function_id = me.function_id
510 AND ff.function_name = X_Function_Name;
511
512 BEGIN
513
514 OPEN c1;
515 FETCH c1 INTO flag;
516 CLOSE c1;
517
518 return flag;
519
520 END Function_Allowed;
521
522
523
524 END OKE_K_SECURITY_PKG;
[Home] [Help]