[Home] [Help]
PACKAGE BODY: APPS.OKE_K_ACCESS_RULES_PKG2
Source
1 package body OKE_K_ACCESS_RULES_PKG2 as
2 /* $Header: OKEKAR2B.pls 115.10 2003/12/03 19:20:15 alaw ship $ */
3
4 --
5 -- Name : Compile_Rules
6 -- Pre-reqs : FND_GLOBAL.INITIALIZE
7 -- Function : This function compiles access rules for the
8 -- given contract role.
9 --
10 -- Parameters :
11 -- IN : X_ROLE_ID NUMBER
12 -- OUT : None
13 --
14 -- Returns : BOOLEAN
15 --
16
17 FUNCTION Compile_Rules
18 ( X_Role_ID IN VARCHAR2
19 ) RETURN BOOLEAN IS
20
21 L_user_id number;
22 L_login_id number;
23 L_def_access_level varchar2(30);
24 L_stage number := 0;
25
26 BEGIN
27
28 L_user_id := FND_GLOBAL.user_id;
29 L_login_id := FND_GLOBAL.conc_login_id;
30
31 SAVEPOINT Before_Compilation;
32 --
33 -- Step 0
34 -- Delete previous compiled information and get
35 -- default access level from role
36 --
37 DELETE FROM oke_compiled_access_rules
38 WHERE role_id = X_Role_ID;
39
40 SELECT default_access_level
41 INTO L_def_access_level
42 FROM pa_project_role_types
43 WHERE project_role_id = X_Role_ID;
44
45 --
46 -- Step 1
47 -- Access Rules by attributes
48 --
49 L_stage := 1;
50
51 INSERT INTO oke_compiled_access_rules
52 ( role_id
53 , secured_object_name
54 , attribute_code
55 , attribute_group_code
56 , creation_date
57 , created_by
58 , last_update_date
59 , last_updated_by
60 , last_update_login
61 , access_level
62 , access_rule_id
63 , form_item_flag)
64 SELECT kar.role_id
65 , kar.secured_object_name
66 , oap.attribute_code
67 , oa.attribute_group_code
68 , sysdate
69 , L_user_id
70 , sysdate
71 , L_user_id
72 , L_login_id
73 , DECODE( oa.securable_flag ,
74 'Y' , kar.access_level ,
75 'E' , DECODE( kar.access_level ,
76 'NONE' , 'VIEW' ,
77 kar.access_level
78 ) ,
79 'EDIT'
80 )
81 , kar.access_rule_id
82 , oap.form_item_flag
83 FROM oke_k_access_rules kar
84 , oke_object_attributes_b oa
85 , oke_object_attributes_b oap
86 WHERE kar.role_id = X_Role_ID
87 AND kar.attribute_code is not null
88 AND oa.database_object_name = kar.secured_object_name
92 , oap.attribute_code )
89 AND oa.attribute_code = kar.attribute_code
90 AND oap.database_object_name = oa.database_object_name
91 AND oa.attribute_code = nvl( oap.parent_attribute_code
93 ;
94
95 --
96 -- Step 2
97 -- Access Rules by attribute groups
98 --
99 L_stage := 2;
100
101 INSERT INTO oke_compiled_access_rules
102 ( role_id
103 , secured_object_name
104 , attribute_code
105 , attribute_group_code
106 , creation_date
107 , created_by
108 , last_update_date
109 , last_updated_by
110 , last_update_login
111 , access_level
112 , access_rule_id
113 , form_item_flag)
114 SELECT kar.role_id
115 , kar.secured_object_name
116 , oap.attribute_code
117 , oa.attribute_group_code
118 , sysdate
119 , L_user_id
120 , sysdate
121 , L_user_id
122 , L_login_id
123 , DECODE( oa.securable_flag ,
124 'Y' , kar.access_level ,
125 DECODE( kar.access_level ,
126 'NONE' , 'VIEW' ,
127 kar.access_level
128 )
129 )
130 , kar.access_rule_id
131 , oap.form_item_flag
132 FROM oke_k_access_rules kar
133 , oke_object_attributes_b oa
134 , oke_object_attributes_b oap
135 WHERE kar.role_id = X_Role_ID
136 AND kar.attribute_code is null
137 AND oa.database_object_name = kar.secured_object_name
138 AND oa.attribute_group_code = kar.attribute_group_code
139 AND oap.database_object_name = oa.database_object_name
140 AND oa.attribute_code = nvl( oap.parent_attribute_code
141 , oap.attribute_code )
142 AND NOT EXISTS (
143 SELECT null
144 FROM oke_compiled_access_rules
145 WHERE role_id = kar.role_id
146 AND secured_object_name = kar.secured_object_name
147 AND attribute_code = oap.attribute_code
148 )
149 ;
150
151 --
152 -- Step 3
153 -- Access Rules by object
154 --
155 L_stage := 3;
156
157 INSERT INTO oke_compiled_access_rules
158 ( role_id
159 , secured_object_name
160 , attribute_code
161 , creation_date
162 , created_by
163 , last_update_date
164 , last_updated_by
165 , last_update_login
166 , access_level
167 , access_rule_id
168 , form_item_flag)
169 SELECT X_role_id
170 , oa.database_object_name
171 , oap.attribute_code
172 , sysdate
173 , L_user_id
174 , sysdate
175 , L_user_id
176 , L_login_id
177 , DECODE( oa.securable_flag ,
178 'Y' , kar.access_level ,
179 DECODE( kar.access_level ,
180 'NONE' , 'VIEW' ,
181 kar.access_level
182 )
183 )
184 , kar.access_rule_id
185 , oap.form_item_flag
186 FROM oke_k_access_rules kar
187 , oke_object_attributes_b oa
188 , oke_object_attributes_b oap
189 WHERE kar.role_id = X_Role_ID
190 AND kar.secured_object_name = oa.database_object_name
191 AND kar.attribute_group_code IS NULL
192 AND kar.attribute_code IS NULL
193 AND oap.database_object_name = oa.database_object_name
194 AND oa.attribute_code = nvl( oap.parent_attribute_code
195 , oap.attribute_code )
196 AND NOT EXISTS (
197 SELECT null
198 FROM oke_compiled_access_rules
199 WHERE role_id = kar.role_id
200 AND secured_object_name = oap.database_object_name
201 AND attribute_code = oap.attribute_code
202 );
203
204 --
205 -- Step 4
206 -- Default Access Levels
207 --
208 L_stage := 4;
209
210 INSERT INTO oke_compiled_access_rules
211 ( role_id
212 , secured_object_name
213 , attribute_code
214 , creation_date
215 , created_by
216 , last_update_date
217 , last_updated_by
218 , last_update_login
219 , access_level
220 , form_item_flag)
221 SELECT X_Role_ID
222 , oap.database_object_name
223 , oap.attribute_code
224 , sysdate
225 , L_user_id
226 , sysdate
227 , L_user_id
228 , L_login_id
229 , L_def_access_level
230 , oap.form_item_flag
231 FROM oke_object_attributes_b oa
232 , oke_object_attributes_b oap
233 WHERE oa.database_object_name in ( 'OKE_K_HEADERS'
234 , 'OKE_K_LINES'
235 , 'OKE_K_DELIVERABLES' )
236 AND oap.database_object_name = oa.database_object_name
237 AND oa.attribute_code = nvl( oap.parent_attribute_code
238 , oap.attribute_code )
239 AND NOT EXISTS (
240 SELECT null
241 FROM oke_k_access_rules
242 WHERE role_id = X_Role_ID
243 AND secured_object_name = oap.database_object_name
244 AND attribute_group_code IS NULL
245 AND attribute_code IS NULL
246 )
247 AND NOT EXISTS (
248 SELECT null
249 FROM oke_compiled_access_rules
250 WHERE role_id = X_Role_ID
251 AND secured_object_name = oap.database_object_name
255 --
252 AND attribute_code = oap.attribute_code
253 );
254
256 -- Step 5
257 -- User Attribute Access by attribute groups
258 --
259 L_stage := 5;
260
261 INSERT INTO oke_compiled_access_rules
262 ( role_id
263 , secured_object_name
264 , attribute_group_type
265 , attribute_group_code
266 , creation_date
267 , created_by
268 , last_update_date
269 , last_updated_by
270 , last_update_login
271 , access_level
272 , access_rule_id)
273 SELECT kar.role_id
274 , kar.secured_object_name
275 , kar.attribute_group_type
276 , kar.attribute_group_code
277 , sysdate
278 , L_user_id
279 , sysdate
280 , L_user_id
281 , L_login_id
282 , kar.access_level
283 , kar.access_rule_id
284 FROM oke_k_access_rules kar
285 WHERE kar.role_id = X_Role_ID
286 AND kar.attribute_group_type = 'USER';
287
288 --
289 -- Step 6
290 -- User Attribute Access by object
291 --
292 L_stage := 6;
293
294 INSERT INTO oke_compiled_access_rules
295 ( role_id
296 , secured_object_name
297 , attribute_group_type
298 , attribute_group_code
299 , creation_date
300 , created_by
301 , last_update_date
302 , last_updated_by
303 , last_update_login
304 , access_level
305 , access_rule_id)
306 SELECT X_role_id
307 , kar.secured_object_name
308 , ag.attribute_group_type
309 , ag.attribute_group_code
310 , sysdate
311 , L_user_id
312 , sysdate
313 , L_user_id
314 , L_login_id
315 , kar.access_level
316 , kar.access_rule_id
317 FROM oke_k_access_rules kar
318 , oke_attribute_groups_v ag
319 WHERE kar.role_id = X_Role_ID
320 AND kar.attribute_group_code IS NULL
321 AND kar.attribute_code IS NULL
322 ANd ag.attribute_group_type = 'USER'
323 AND NOT EXISTS (
324 SELECT null
325 FROM oke_compiled_access_rules
326 WHERE role_id = kar.role_id
327 AND secured_object_name = kar.secured_object_name
328 AND attribute_group_code = ag.attribute_group_code
329 AND attribute_group_type = ag.attribute_group_type
330 )
331 ;
332
333 --
334 -- Step 7
335 -- Default User Attribute Access
336 --
337 L_stage := 7;
338
339 INSERT INTO oke_compiled_access_rules
340 ( role_id
341 , secured_object_name
342 , attribute_group_type
343 , attribute_group_code
344 , creation_date
345 , created_by
346 , last_update_date
347 , last_updated_by
348 , last_update_login
349 , access_level)
350 SELECT X_Role_ID
351 , 'OKE_K_HEADERS'
352 , ag.attribute_group_type
353 , ag.attribute_group_code
354 , sysdate
355 , L_user_id
356 , sysdate
357 , L_user_id
358 , L_login_id
359 , L_def_access_level
360 FROM oke_attribute_groups_v ag
361 WHERE ag.attribute_group_type = 'USER'
362 AND NOT EXISTS (
363 SELECT null
364 FROM oke_compiled_access_rules
365 WHERE role_id = X_Role_ID
366 AND secured_object_name = 'OKE_K_HEADERS'
367 AND attribute_group_type = 'USER'
368 AND attribute_group_code = ag.attribute_group_code )
369 UNION ALL
370 SELECT X_Role_ID
371 , 'OKE_K_LINES'
372 , ag.attribute_group_type
373 , ag.attribute_group_code
374 , sysdate
375 , L_user_id
376 , sysdate
377 , L_user_id
378 , L_login_id
379 , L_def_access_level
380 FROM oke_attribute_groups_v ag
381 WHERE ag.attribute_group_type = 'USER'
382 AND NOT EXISTS (
383 SELECT null
384 FROM oke_compiled_access_rules
385 WHERE role_id = X_Role_ID
386 AND secured_object_name = 'OKE_K_LINES'
387 AND attribute_group_type = 'USER'
388 AND attribute_group_code = ag.attribute_group_code )
389 ;
390
391 --
392 -- Step 8
393 -- Delete Compiled Role Functions
394 --
395 L_stage := 8;
396
397 DELETE FROM oke_role_functions
398 WHERE role_id = X_Role_ID;
399
400 --
401 -- Step 9
402 -- Populate Role Functions
403 --
404 L_stage := 9;
405
406 INSERT INTO oke_role_functions
407 ( role_id
408 , function_id
409 , creation_date
410 , created_by
411 , last_update_date
412 , last_updated_by
413 , last_update_login)
414 SELECT DISTINCT X_Role_ID
415 , f.function_id
416 , sysdate
417 , L_user_id
418 , sysdate
419 , L_user_id
420 , L_login_id
421 FROM fnd_form_functions f
422 , ( SELECT function_id
423 FROM fnd_menu_entries
424 START WITH menu_id = (
425 SELECT menu_id FROM pa_project_role_types
426 WHERE project_role_id = X_Role_ID )
427 CONNECT BY menu_id = PRIOR sub_menu_id ) me
428 WHERE me.function_id = f.function_id;
429
430 RETURN( TRUE );
431
432 EXCEPTION
433 WHEN OTHERS THEN
434 ROLLBACK TO SAVEPOINT Before_Compilation;
435 FND_MESSAGE.SET_NAME('OKE', 'OKE_SEC_COMPILE_RULE_FAILED');
439
436 FND_MESSAGE.SET_TOKEN('STAGE', L_stage);
437 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
438 RETURN( FALSE );
440 END Compile_Rules;
441
442
443 --
444 -- Name : Copy_Rules
445 -- Pre-reqs : FND_GLOBAL.INITIALIZE
446 -- Function : This function copies access rules from the
447 -- source role to the target role.
448 --
449 -- Parameters :
450 -- IN : X_ROLE_ID NUMBER
451 -- OUT : None
452 --
453 -- Returns : BOOLEAN
454 --
455
456 FUNCTION Copy_Rules
457 ( X_Source_Role_ID IN VARCHAR2
458 , X_Target_Role_ID IN VARCHAR2
459 , X_Copy_Option IN VARCHAR2
460 ) RETURN BOOLEAN IS
461
462 L_user_id number;
463 L_login_id number;
464 L_stage number := 0;
465
466 BEGIN
467
468 IF ( X_Copy_Option = 'REPLICATE' ) THEN
469
470 L_stage := 1;
471
472 DELETE FROM oke_k_access_rules
473 WHERE role_id = X_Target_Role_ID;
474
475 END IF;
476
477 L_user_id := FND_GLOBAL.user_id;
478 L_login_id := FND_GLOBAL.conc_login_id;
479
480 IF ( X_Copy_Option = 'MERGE' ) THEN
481
482 L_stage := 2;
483
484 UPDATE oke_k_access_rules kar
485 SET last_update_date = sysdate
486 , last_updated_by = L_user_id
487 , access_level = (
488 SELECT access_level
489 FROM oke_k_access_rules
490 WHERE role_id = X_Source_Role_ID
491 AND secured_object_name = kar.secured_object_name
492 AND nvl( attribute_group_code , '*NULL Attribute Group*' ) =
493 nvl( kar.attribute_group_code , '*NULL Attribute Group*' )
494 AND nvl( attribute_code , '*NULL Attribute*' ) =
495 nvl( kar.attribute_code , '*NULL Attribute*' )
496 )
497 WHERE role_id = X_Target_Role_ID
498 AND EXISTS (
499 SELECT NULL
500 FROM oke_k_access_rules
501 WHERE role_id = X_Source_Role_ID
502 AND secured_object_name = kar.secured_object_name
503 AND nvl( attribute_group_code , '*NULL Attribute Group*' ) =
504 nvl( kar.attribute_group_code , '*NULL Attribute Group*' )
505 AND nvl( attribute_code , '*NULL Attribute*' ) =
506 nvl( kar.attribute_code , '*NULL Attribute*' )
507 );
508
509 END IF;
510
511 L_stage := 3;
512
513 INSERT INTO oke_k_access_rules
514 ( access_rule_id
515 , creation_date
516 , created_by
517 , last_update_date
518 , last_updated_by
519 , last_update_login
520 , role_id
521 , secured_object_name
522 , attribute_group_code
523 , attribute_code
524 , access_level )
525 SELECT oke_k_access_rules_s.nextval
526 , sysdate
527 , L_user_id
528 , sysdate
529 , L_user_id
530 , L_login_id
531 , X_Target_Role_ID
532 , kar.secured_object_name
533 , kar.attribute_group_code
534 , kar.attribute_code
535 , kar.access_level
536 FROM oke_k_access_rules kar
537 WHERE role_id = X_Source_Role_ID
538 AND NOT EXISTS (
539 SELECT NULL
540 FROM oke_k_access_rules
541 WHERE role_id = X_Target_Role_ID
542 AND secured_object_name = kar.secured_object_name
543 AND nvl( attribute_group_code , '*NULL Attribute Group*' ) =
544 nvl( kar.attribute_group_code , '*NULL Attribute Group*' )
545 AND nvl( attribute_code , '*NULL Attribute*' ) =
546 nvl( kar.attribute_code , '*NULL Attribute*' )
547 );
548
549 RETURN( TRUE );
550
551 EXCEPTION
552 WHEN OTHERS THEN
553 FND_MESSAGE.SET_NAME('OKE', 'OKE_SEC_COPY_RULE_FAILED');
554 FND_MESSAGE.SET_TOKEN('STAGE', L_stage);
555 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
556 RETURN( FALSE );
557
558 END Copy_Rules;
559
560
561 --
562 -- Name : Compile
563 -- Pre-reqs : Invoke from Concurrent Manager
564 -- Function : This PL/SQL concurrent program compiles
565 -- access rules for all contract roles or
566 -- a specific role.
567 --
568 -- Parameters :
569 -- IN : X_ROLE_ID NUMBER
570 -- OUT : ERRBUF VARCHAR2
571 -- RETCODE NUMBER
572 --
573 -- Returns : None
574 --
575
576 PROCEDURE Compile
577 ( ERRBUF OUT NOCOPY VARCHAR2
578 , RETCODE OUT NOCOPY NUMBER
579 , X_Role_ID IN NUMBER
580 ) IS
581
582 L_Error_Buf VARCHAR2(4000);
583 RequestID NUMBER;
584 Compile_Error EXCEPTION;
585
586 CURSOR c IS
587 SELECT PRT.Project_Role_ID Role_ID
588 , PRT.Meaning Role_Name
589 FROM PA_Project_Role_Types PRT
590 , PA_Role_Controls RC
591 WHERE RC.Project_Role_ID = PRT.Project_Role_ID
592 AND RC.Role_Control_Code = 'ALLOW_AS_CONTRACT_MEMBER'
593 AND PRT.Freeze_Rules_Flag = 'Y'
594 AND PRT.Project_Role_ID = nvl(X_Role_ID , PRT.Project_Role_ID)
595 ORDER BY Role_Name;
596
597 BEGIN
598
599 RETCODE := 0;
600
601 FOR crec IN c LOOP
602
603 FND_MESSAGE.SET_NAME('OKE' , 'OKE_SEC_COMPILING_RULES');
604 FND_MESSAGE.SET_TOKEN('ROLE' , crec.Role_Name);
605 FND_FILE.PUT_LINE(FND_FILE.LOG , FND_MESSAGE.GET);
606
610
607 IF NOT ( Compile_Rules( crec.Role_ID ) ) THEN
608 RAISE Compile_Error;
609 END IF;
611 END LOOP;
612
613 RequestID := FND_REQUEST.Submit_Request
614 ( APPLICATION => 'OKE'
615 , PROGRAM => 'OKEGNKSV'
616 );
617
618 EXCEPTION
619 WHEN Compile_Error THEN
620 L_Error_Buf := FND_MESSAGE.GET;
621 FND_FILE.PUT_LINE(FND_FILE.LOG , L_Error_Buf);
622 ERRBUF := L_Error_Buf;
623 RETCODE := 2;
624
625 WHEN OTHERS THEN
626 ERRBUF := L_Error_Buf;
627 RETCODE := 2;
628
629 END Compile;
630
631 end OKE_K_ACCESS_RULES_PKG2;