[Home] [Help]
PACKAGE BODY: APPS.UMX_FGA_UTILS_PKG
Source
1 package body UMX_FGA_UTILS_PKG as
2 /* $Header: UMXFGAUTILB.pls 120.9 2010/09/09 10:05:52 spakanat noship $ */
3
4 role_Code varchar2(1000);
5 role_Cat varchar2(1000);
6 role_Appl varchar2(1000);
7 role_Set_Id varchar2(1000);
8 bulk_selection_value varchar2(1);
9
10 procedure endDate_grants(adminRole varchar2) is
11 begin
12 update fnd_grants set END_DATE = sysdate,
13 LAST_UPDATE_DATE = sysdate,
14 LAST_UPDATED_BY = 1000002,
15 LAST_UPDATE_LOGIN = 1000002
16 where grantee_key = adminRole and
17 object_id in (select object_id from fnd_objects where obj_name='UMX_ACCESS_ROLE');
18 end;
19
20 /*******************************************************************************************************************************/
21 /*******************************************************************************************************************************/
22
23 procedure convert_All_Grants_to_roleSets is
24 cursor adminRoles is select distinct grantee_key
25 from fnd_grants fg,
26 fnd_objects fo
27 --,fnd_menus fm
28 where --fg.menu_id = fm.menu_id and
29 fg.object_id = fo.object_id
30 --and fm.menu_name = 'UMX_OBJ_ADMIN_ROLE_PERMS'
31 and fo.obj_name = 'UMX_ACCESS_ROLE'
32 and fg.start_date <= sysdate
33 and nvl(fg.end_date, sysdate+1) > sysdate
34 and GRANTEE_TYPE= 'GROUP';
35 begin
36 for adminRole in adminRoles
37 loop
38 create_roleSet_from_grants(adminRole.grantee_key);
39 end loop;
40 end;
41
42 /*******************************************************************************************************************************/
43 /*******************************************************************************************************************************/
44
45 procedure copy_grants_to_roleSets(adminRole varchar2, roleSetId number) is
46 begin
47 insert into UMX_LSA_ROLE_SET_ROLES (role_set_id, role_name) select roleSetId, INSTANCE_PK1_VALUE
48 from fnd_grants fg,
49 fnd_objects fo,
50 fnd_menus fm,
51 wf_all_roles_vl wfr
52 where fg.menu_id = fm.menu_id
53 and fg.object_id = fo.object_id
54 and INSTANCE_PK1_VALUE = wfr.name (+)
55 and grantee_key = adminRole
56 and fm.menu_name = 'UMX_OBJ_ADMIN_ROLE_PERMS'
57 and fo.obj_name = 'UMX_ACCESS_ROLE'
58 and INSTANCE_TYPE = 'INSTANCE'
59 and fg.start_date <= sysdate
60 and nvl(fg.end_date, sysdate+1) > sysdate;
61 end;
62
63
64 /*******************************************************************************************************************************/
65 /*******************************************************************************************************************************/
66
67 /*
68 Case 1: Converting grants containing all roles
69 a. Create a new role set criteria if not already present with role set Id as '0'
70 b. Add the roleSet with Id '0' to the adminRole with all the privilages set
71 c. no entries will be added to the roleSet_roles table
72 Case 2: Converting grants containing a set of roles`
73 a. Create a new role set criteria
74 b. Add the new roleSet to the adminRole
75 c. Add the roles in the grants to the new role set
76 d. Add the roleSet with Id '0' to the adminRole with all the privilages set except assign and revoke
77 */
78 procedure create_roleSet_from_grants(adminRole varchar2) is
79 roleSetId UMX_LSA_ROLE_SET_ROLES.role_set_id%type;
80 cnt number(6);
81 errm varchar2(300) := '';
82 errcode varchar2(100) := '';
83 begin
84 select count(*) into cnt
85 from UMX_LSA_ROLE_SET_CRITERIA ulrsc,
86 UMX_LSA_ROLE ulr
87 where ulr.admin_role = adminRole
88 and ulrsc.role_set_id = ulr.role_Set_Id
89 and ulrsc.role_name = '*'
90 and ulrsc.role_appl = '*'
91 and ulrsc.role_cat = '*';
92 if(cnt<=0) then
93 begin
94 --For all roles
95 select count(*) into cnt
96 from fnd_grants fg,
97 fnd_objects fo
98 --fnd_menus fm,
99 --wf_all_roles_vl wfr
100 where
101 --fg.menu_id = fm.menu_id and
102 fg.object_id = fo.object_id
103 --and INSTANCE_PK1_VALUE = wfr.name (+)
104 and grantee_key = adminRole
105 -- and fm.menu_name = 'UMX_OBJ_ADMIN_ROLE_PERMS'
106 and fo.obj_name = 'UMX_ACCESS_ROLE'
107 and INSTANCE_TYPE = 'GLOBAL';
108 if(cnt > 0) then --All Roles. typically the cnt will be 1
109 roleSetId := create_roleSet_criteria(0,'All Roles','**','**','**');
110 assign_roleSet_to_adminRole(adminRole,0);
111 else -- for some roles
112 roleSetId := create_roleSet_criteria(null,'Unspecified Criteria','*','*','*');
113 assign_roleSet_to_adminRole(adminRole,roleSetId);
114 copy_grants_to_roleSets(adminRole,roleSetId);
115 --assign privs for all roles with no assign and revoke perms
116 roleSetId := create_roleSet_criteria(0,'All Roles','**','**','**');
117 assign_roleSet_to_adminRole(adminRole, 0,
118 canUpdate => 1,
119 canManageGrants => 1,
120 canAlterHierarchy => 1,
121 canAssign => 0,
122 canRevoke => 0,
123 securityWizard => 1,
124 PRIV7 => 1,
125 PRIV8 => 1,
126 PRIV9 => 1,
127 PRIV10 => 1,
128 PRIV11 => 1,
129 PRIV12 => 1,
130 PRIV13 => 1,
131 PRIV14 => 1);
132 end if;
133 create_global_privileges(adminRole,1);
134 endDate_grants(adminRole);
135 --commit;
136 exception
137 when others then
138 errcode := SQLCODE;
139 errm := SQLERRM;
140 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
141 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,'fnd.plsql.UMXFGAUTILB.create_roleSet_from_grants','Exception: '||errcode||' : '||errm);
142 end if;
143 rollback;
144 end;
145 end if;
146 exception
147 when others then
148 errcode := SQLCODE;
149 errm := SQLERRM;
150 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
151 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,'fnd.plsql.UMXFGAUTILB.create_roleSet_from_grants','Exception : '||errcode||' : '||errm);
152 end if;
153 end;
154
155 /*******************************************************************************************************************************/
156 /*******************************************************************************************************************************/
157
158 procedure create_global_privileges(adminRole varchar2, canCreate number,
159 GLOBALPRIV2 number default 1,
160 GLOBALPRIV3 number default 1,
161 GLOBALPRIV4 number default 1,
162 GLOBALPRIV5 number default 1,
163 GLOBALPRIV6 number default 1,
164 GLOBALPRIV7 number default 1,
165 GLOBALPRIV8 number default 1,
166 GLOBALPRIV9 number default 1) is
167 cnt number(2);
168 begin
169 select count(*) into cnt from UMX_LSA_ROLE_GLOBAL_PRIVS where admin_role = adminRole;
170 if(cnt=0) then
171 insert into UMX_LSA_ROLE_GLOBAL_PRIVS(admin_role,
172 can_Create,
173 GLOBAL_PRIV2 ,
174 GLOBAL_PRIV3 ,
175 GLOBAL_PRIV4 ,
176 GLOBAL_PRIV5 ,
177 GLOBAL_PRIV6 ,
178 GLOBAL_PRIV7 ,
179 GLOBAL_PRIV8 ,
180 GLOBAL_PRIV9 )
181 values(adminRole,
182 canCreate,
183 GLOBALPRIV2 ,
184 GLOBALPRIV3 ,
185 GLOBALPRIV4 ,
186 GLOBALPRIV5 ,
187 GLOBALPRIV6 ,
188 GLOBALPRIV7 ,
189 GLOBALPRIV8 ,
190 GLOBALPRIV9 );
191 else
192 update UMX_LSA_ROLE_GLOBAL_PRIVS set can_Create = canCreate,
193 GLOBAL_PRIV2 = GLOBALPRIV2,
194 GLOBAL_PRIV3 = GLOBALPRIV3,
195 GLOBAL_PRIV4 = GLOBALPRIV4,
196 GLOBAL_PRIV5 = GLOBALPRIV5,
197 GLOBAL_PRIV6 = GLOBALPRIV6,
198 GLOBAL_PRIV7 = GLOBALPRIV7,
199 GLOBAL_PRIV8 = GLOBALPRIV8,
200 GLOBAL_PRIV9 = GLOBALPRIV9
201 where admin_role = adminRole;
202 end if;
203 end;
204
205 /*******************************************************************************************************************************/
206 /*******************************************************************************************************************************/
207
208 procedure create_assign_roleSet(adminRole varchar2,
209 roleSetName varchar2 default null,
210 roleName varchar2 default '%',
211 roleAapl varchar2 default '',
212 roleCat varchar2 default '',
213 canUpdate number default 1,
214 canManageGrants number default 1,
215 canAlterHierarchy number default 1,
216 canAssign number default 1,
217 canRevoke number default 1,
218 securityWizard number default 1,
219 PRIVILEGE7 number default 1,
220 PRIVILEGE8 number default 1,
221 PRIVILEGE9 number default 1,
222 PRIVILEGE10 number default 1,
223 PRIVILEGE11 number default 1,
224 PRIVILEGE12 number default 1,
225 PRIVILEGE13 number default 1,
226 PRIVILEGE14 number default 1)
227 is
228 roleSetId UMX_LSA_ROLE_SET_ROLES.role_set_id%type;
229 begin
230 roleSetId := create_roleSet_criteria(null,roleSetName,roleName,roleAapl,roleCat);
231 assign_roleSet_to_adminRole(adminRole,roleSetId,
232 canUpdate,
233 canManageGrants,
234 canAlterHierarchy,
235 canAssign,
236 canRevoke,
237 securityWizard,
238 PRIVILEGE7,
239 PRIVILEGE8,
240 PRIVILEGE9,
241 PRIVILEGE10,
242 PRIVILEGE11,
243 PRIVILEGE12,
244 PRIVILEGE13,
245 PRIVILEGE14);
246 end;
247
248 /*******************************************************************************************************************************/
249 /*******************************************************************************************************************************/
250
251 function create_roleSet_criteria(roleSetId number default null,
252 roleSetName varchar2 default null,
253 roleName varchar2 default '%',
254 roleAapl varchar2 default null,
255 roleCat varchar2 default null)
256 return number is
257 roleSetIdNew UMX_LSA_ROLE_SET_ROLES.role_set_id%type;
258 roleSetIdOld UMX_LSA_ROLE_SET_ROLES.role_set_id%type;
259 begin
260 --dbms_output.put_line('In create_roleSet_criteria : '||to_char(roleSetId));
261 if(roleSetId is null) then
262 select UMX_LSA_ROLE_SET_CRITERIA_S.nextval into roleSetIdNew from dual;
263 --dbms_output.put_line('In if(roleSetId is null) : '||to_char(roleSetIdNew));
264 else
265 begin
266 select role_set_id into roleSetIdOld from UMX_LSA_ROLE_SET_CRITERIA where role_set_id = roleSetId;
267 update UMX_LSA_ROLE_SET_CRITERIA set role_set_name = roleSetName,
268 role_name = roleName,
269 role_appl = roleAapl,
270 role_cat = roleCat
271 where role_set_id = roleSetId;
272 --dbms_output.put_line('In if(roleSetId is null) else : '||to_char(roleSetId));
273 return roleSetId;
274 exception
275 when no_data_found then
276 roleSetIdNew := roleSetId;
277 --dbms_output.put_line('In no_data_found : '||to_char(roleSetIdNew));
278 end;
279 end if;
280 insert into UMX_LSA_ROLE_SET_CRITERIA (role_set_id,
281 role_set_name,
282 role_name,
283 role_appl,
284 role_cat)
285 values(roleSetIdNew,
286 roleSetName,
287 roleName,
288 roleAapl,
289 roleCat);
290 --dbms_output.put_line('returning : '||to_char(roleSetIdNew));
291 return roleSetIdNew;
292 end;
293
294 /*******************************************************************************************************************************/
295 /*******************************************************************************************************************************/
296
297 procedure assign_roleSet_to_adminRole(adminRole varchar2, roleSetId number,
298 canUpdate number default 1,
299 canManageGrants number default 1,
300 canAlterHierarchy number default 1,
301 canAssign number default 1,
302 canRevoke number default 1,
303 securityWizard number default 1,
304 PRIV7 number default 1,
305 PRIV8 number default 1,
306 PRIV9 number default 1,
307 PRIV10 number default 1,
308 PRIV11 number default 1,
309 PRIV12 number default 1,
310 PRIV13 number default 1,
311 PRIV14 number default 1)
312 is
313 cnt number(10);
314 begin
315 select count(*) into cnt
316 from UMX_LSA_ROLE
317 where admin_role = adminRole
318 and role_set_id = roleSetId;
319 if(cnt=0) then
320 insert into UMX_LSA_ROLE(admin_role, role_set_id,
321 can_update,
322 CAN_CREATE_GRANT,
323 can_alter_hierarchy,
324 can_assign,
325 can_revoke,
326 security_wizard,
327 PRIVILEGE7,
328 PRIVILEGE8,
329 PRIVILEGE9,
330 PRIVILEGE10,
331 PRIVILEGE11,
332 PRIVILEGE12,
333 PRIVILEGE13,
334 PRIVILEGE14)
335 values(adminRole, roleSetId,
336 canUpdate,
337 canManageGrants,
338 canAlterHierarchy,
339 canAssign,
340 canRevoke,
341 securityWizard,
342 PRIV7,
343 PRIV8,
344 PRIV9,
345 PRIV10,
346 PRIV11,
347 PRIV12,
348 PRIV13,
349 PRIV14);
350 else
351 update UMX_LSA_ROLE set can_update = canUpdate,
352 CAN_CREATE_GRANT = canManageGrants,
353 can_alter_hierarchy = canAlterHierarchy,
354 can_assign = canAssign,
355 can_revoke = canRevoke,
356 security_wizard = securityWizard,
357 PRIVILEGE7 = PRIV7,
358 PRIVILEGE8 = PRIV8,
359 PRIVILEGE9 = PRIV9,
360 PRIVILEGE10 = PRIV10,
361 PRIVILEGE11 = PRIV11,
362 PRIVILEGE12 = PRIV12,
363 PRIVILEGE13 = PRIV13,
364 PRIVILEGE14 = PRIV14
365 where admin_role = adminRole and role_Set_Id = roleSetId;
366 end if;
367 end;
368
369 /*******************************************************************************************************************************/
370 /*******************************************************************************************************************************/
371
372 procedure create_all_roles_roleset is
373 cnt number(2);
374 BEGIN
375 select count(*) into cnt
376 from UMX_LSA_ROLE_SET_CRITERIA where role_set_id = 0;
377 EXCEPTION
378 when NO_DATA_FOUND then
379 insert into UMX_LSA_ROLE_SET_CRITERIA(role_set_id,
380 role_set_name,
381 role_name,
382 role_appl,
383 role_cat)
384 VALUES(0,'All Roles','**','**','**');
385 END;
386
387 /*******************************************************************************************************************************/
388 /*******************************************************************************************************************************/
389
390 procedure bulk_add_all(p_role_set_id varchar2) is
391 begin
392 insert into umx_lsa_role_set_roles (role_set_id,
393 role_name,
394 CREATED_BY,
395 CREATION_DATE,
396 LAST_UPDATED_BY,
397 LAST_UPDATE_DATE,
398 LAST_UPDATE_LOGIN)
399
400 select p_role_set_id,
401 wlr.name,
402 fnd_global.user_id,
403 sysdate,
404 fnd_global.user_id,
405 sysdate,
406 fnd_global.login_id
407 from WF_LOCAL_ROLES wlr,
408 WF_LOCAL_ROLES_TL wlrt,
409 FND_LOOKUP_ASSIGNMENTS cat,
410 umx_lsa_role_set_criteria ulrsc
411 where cat.OBJ_NAME(+) = 'UMX_ACCESS_ROLE'
412 and nvl(cat.LOOKUP_CODE,'%') like ulrsc.role_cat
413 and wlr.name = cat.INSTANCE_PK1_VALUE(+)
414 and cat.INSTANCE_PK2_VALUE is null
415 and cat.INSTANCE_PK3_VALUE is null
416 and cat.INSTANCE_PK4_VALUE is null
417 and cat.INSTANCE_PK5_VALUE is null
418 and wlr.owner_tag like ulrsc.role_Appl
419 and ulrsc.role_set_id = p_role_set_id
420 and wlr.name like ulrsc.role_name
421 and wlr.partition_id in (2,13)
422 and wlr.orig_system in ('UMX','FND_RESP')
423 and wlr.orig_system_id like '%'
424 and wlr.orig_system = wlrt.orig_system (+)
425 and wlr.orig_system_id = wlrt.orig_system_id (+)
426 and wlr.name = wlrt.name (+)
427 and wlr.partition_id = wlrt.partition_id (+)
428 and wlrt.language (+) = userenv('LANG')
429 and not exists (select role_name from UMX_LSA_ROLE_SET_ROLES
430 where role_set_id = p_role_set_id
431 and role_name = wlr.name);
432 end;
433
434 /*******************************************************************************************************************************/
435 /*******************************************************************************************************************************/
436
437 procedure bulk_add_all_for_new(p_role_set_id varchar2, p_role_name varchar2, p_role_appl varchar2, p_role_cat varchar2) is
438 begin
439 insert into umx_lsa_role_set_roles (role_set_id,
440 role_name,
441 CREATED_BY,
442 CREATION_DATE,
443 LAST_UPDATED_BY,
444 LAST_UPDATE_DATE,
445 LAST_UPDATE_LOGIN)
446 select p_role_set_id,
447 wlr.name,
448 fnd_global.user_id,
449 sysdate,
450 fnd_global.user_id,
451 sysdate,
452 fnd_global.login_id
453 from WF_LOCAL_ROLES wlr,
454 FND_LOOKUP_ASSIGNMENTS cat
455 where wlr.owner_tag like p_role_appl
456 and wlr.name like p_role_name
457 and wlr.partition_id in (2,13)
458 and wlr.orig_system in ('UMX','FND_RESP')
459 and wlr.orig_system_id like '%'
460 and cat.OBJ_NAME(+) = 'UMX_ACCESS_ROLE'
461 and nvl(cat.LOOKUP_CODE,'%') like p_role_cat
462 and wlr.name = cat.INSTANCE_PK1_VALUE(+)
463 and cat.INSTANCE_PK2_VALUE is null
464 and cat.INSTANCE_PK3_VALUE is null
465 and cat.INSTANCE_PK4_VALUE is null
466 and cat.INSTANCE_PK5_VALUE is null;
467 end;
468
469 /*******************************************************************************************************************************/
470 /*******************************************************************************************************************************/
471
472 procedure bulk_remove_all(p_role_set_id varchar2) is
473 begin
474 delete from umx_lsa_role_set_roles where role_set_id = p_role_set_id;
475 end;
476 /*******************************************************************************************************************************/
477 /*******************************************************************************************************************************/
478
479 procedure delete_role_Set(p_role_set_id varchar2,p_admin_role varchar2) is
480 begin
481 delete from umx_lsa_role_set_roles
482 where role_set_id = p_role_set_id;
483
484 if(p_role_set_id <> 0) then
485 delete from umx_lsa_role_set_criteria
486 where role_set_id = p_role_set_id;
487 end if;
488
489 if(p_admin_role is not null) then
490 delete from umx_lsa_role
491 where role_set_id = p_role_set_id and admin_role = p_admin_role;
492 else
493 delete from umx_lsa_role
494 where role_set_id = p_role_set_id;
495 end if;
496 end;
497 /*******************************************************************************************************************************/
498 /*******************************************************************************************************************************/
499
500 procedure set(code in varchar2, value in varchar2) is
501 begin
502 if(code='ROLE_CODE') then
503 role_Code := value;
504 elsif(code='ROLE_CAT') then
505 role_Cat := value;
506 elsif(code='ROLE_APPL') then
507 role_Appl := value;
508 elsif(code='ROLE_SET_ID') then
509 role_Set_Id := value;
510 elsif(code='BULK_SELECTION_VALUE') then
511 bulk_selection_value := value;
512 end if;
513 end;
514 /*******************************************************************************************************************************/
515 /*******************************************************************************************************************************/
516
517 function get(code in varchar2) return varchar2 is
518 begin
519 if(code='ROLE_CODE') then
520 return role_Code;
521 elsif(code='ROLE_CAT') then
522 return role_Cat;
523 elsif(code='ROLE_APPL') then
524 return role_Appl;
525 elsif(code='ROLE_SET_ID') then
526 return role_Set_Id;
527 elsif(code='BULK_SELECTION_VALUE') then
528 return bulk_selection_value;
529 else
530 return null;
531 end if;
532 end;
533 /*******************************************************************************************************************************/
534 /*******************************************************************************************************************************/
535
536 procedure clearViewParams is
537 begin
538 role_Code := '';
539 role_Cat := '';
540 role_Appl := '';
541 role_Set_Id := '';
542 bulk_selection_value := '';
543 end;
544 /*******************************************************************************************************************************/
545 /*******************************************************************************************************************************/
546 end;