1 package body FND_SEC_BULK as
2 /* $Header: AFSCBLKB.pls 120.3 2005/11/02 12:13:05 rsheh noship $ */
3
4
5 /*
6 ** cloneuser_attr - setup a new user with the attributes, responsibilities,
7 ** and profile option settings of an existing user.
8 ** Note: this procedure should live in fnd_user_pkg
9 */
10 PROCEDURE cloneuser_attr(p_template_user in varchar2,
11 p_new_user in varchar2,
12 description in varchar2,
13 email in varchar2)
14 is
15 new_userid number;
16 cursor template_user is
17 select end_date,
18 description,
19 password_lifespan_accesses,
20 password_lifespan_days,
21 employee_id,
22 email_address,
23 fax,
24 customer_id,
25 supplier_id
26 from fnd_user
27 where user_name = upper(p_template_user);
28
29 begin
30 begin
31 select user_id into new_userid
32 from fnd_user
33 where user_name = upper(p_new_user);
34 exception
35 when no_data_found then
36 null;
37 end;
38
39 for tu in template_user loop
40 fnd_user_pkg.UpdateUser(
41 x_user_name => upper(p_new_user),
42 x_owner => 'CUST',
43 x_end_date => tu.end_date,
44 x_description => nvl(description, tu.description),
45 x_password_date => sysdate,
46 x_password_accesses_left => tu.password_lifespan_accesses,
47 x_password_lifespan_accesses => tu.password_lifespan_accesses,
48 x_password_lifespan_days => tu.password_lifespan_days,
49 x_employee_id => tu.employee_id,
50 x_email_address => nvl(email, tu.email_address),
51 x_fax => tu.fax,
52 x_customer_id => tu.customer_id,
53 x_supplier_id => tu.supplier_id);
54 end loop;
55
56 end;
57
58 /*
59 ** cloneuser_resp - setup a new user with the attributes, responsibilities,
60 ** and profile option settings of an existing user.
61 ** Note: this procedure should live in fnd_user_pkg
62 */
63 PROCEDURE cloneuser_resp(p_template_user in varchar2,
64 p_new_user in varchar2)
65 is
66 new_userid number;
67
68 cursor resp_groups is
69 select a.application_short_name app,
70 r.responsibility_key resp,
71 s.security_group_key sg,
72 decode(u.last_updated_by, 1, 'SEED', 'CUSTOM') owner,
73 to_char(ur.start_date, 'YYYY/MM/DD') start_date,
74 to_char(ur.end_date, 'YYYY/MM/DD') end_date,
75 ur.description
76 from fnd_user u,
77 fnd_user_resp_groups ur,
78 fnd_application_vl a,
79 fnd_responsibility_vl r,
80 fnd_security_groups_vl s
81 where u.user_name = upper(p_template_user)
82 and ur.user_id = u.user_id
83 and ur.responsibility_id = r.responsibility_id
84 and ur.responsibility_application_id = r.application_id
85 and ur.responsibility_application_id = a.application_id
86 and ur.security_group_id = s.security_group_id;
87
88 begin
89 begin
90 select user_id into new_userid
91 from fnd_user
92 where user_name = upper(p_new_user);
93 exception
94 when no_data_found then
95 null;
96 end;
97
98 for rgx in resp_groups loop
99 fnd_user_resp_groups_api.load_row(
100 x_user_name => upper(p_new_user),
101 x_resp_key => rgx.resp,
102 x_app_short_name => rgx.app,
103 x_security_group => rgx.sg,
104 x_owner => rgx.owner,
105 x_start_date => rgx.start_date,
106 x_end_date => rgx.end_date,
107 x_description => rgx.description);
108 end loop;
109
110 end;
111 /*
112 ** cloneuser_prof - setup a new user with the attributes, responsibilities,
113 ** and profile option settings of an existing user.
114 ** Note: this procedure should live in fnd_user_pkg
115 */
116 PROCEDURE cloneuser_prof(p_template_user in varchar2,
117 p_new_user in varchar2)
118 is
119 new_userid number;
120 ret boolean;
121 cursor profile_values is
122 select pov.application_id appid,
123 p.profile_option_name proname,
124 pov.profile_option_value val
125 from fnd_profile_option_values pov,
126 fnd_profile_options p,
127 fnd_user u
128 where pov.level_id = 10004
129 and pov.level_value = u.user_id
130 and pov.profile_option_id = p.profile_option_id
131 and pov.application_id = p.application_id
132 and u.user_name = upper(p_template_user);
133 begin
134 begin
135 select user_id into new_userid
136 from fnd_user
137 where user_name = upper(p_new_user);
138 exception
139 when no_data_found then
140 null;
141 end;
142
143 for pvx in profile_values loop
144 ret := fnd_profile.save(pvx.proname, pvx.val, 'USER', new_userid, '');
145 end loop;
146 end;
147
148 --------------------------------------------------------------------------
149 -- AddRespUserGroup
150 -- Assign a responsibility key to a group of users.
151 -- The group of users is defined in x_user_group_clause.
152 -- You can optionally supply description, start_date and end_date.
153 -- The return value is the number of users processed.
154 --
155 -- Usage Example
156 -- declare
157 -- user_clause varchar2(2000);
158 -- cnt number;
159 -- begin
160 -- user_clause := fnd_sec_bulk.UserAssignResp('FND',
161 -- 'SYSTEM_ADMINISTRATOR',
162 -- 'STANDARD');
163 -- cnt:= fnd_sec_bulk.AddRespUserGroup(user_clause,
164 -- 'FND',
165 -- 'SYSTEM_ADMINISTRATOR_GUI',
166 -- '',
167 -- 'New SYSADMIN responsibility');
168 --
169 -- OR cnt:= fnd_sec_bulk.AddRespUserGroup(user_clause,
170 -- 'FND',
171 -- 'SYSTEM_ADMINISTRATOR_GUI',
172 -- end;
173 --
174 -- Input Arguments
175 -- x_user_group_clause: A sql statement returns all user's user_id
176 -- For example, "select user_id from fnd_user where..."
177 -- x_resp_application: Responsibility application short name
178 -- x_responsibility: Responsibility key
179 -- x_security_group: Security Group. Default is null.
180 -- If x_user_group_clause is provided from calling
181 -- UserAssignResp(), then DO NOT pass in
182 -- x_security_group.
183 -- If x_user_group_clause is provided by yourself,
184 -- then you SHOULD input the x_security_group.
185 -- x_description: Description
186 -- x_start_date: Start date
187 -- x_end_date: End date
188
189 function AddRespUserGroup(
190 x_user_group_clause in varchar2,
191 x_resp_application in varchar2,
192 x_responsibility in varchar2,
193 x_security_group in varchar2 default '',
194 x_description in varchar2 default '',
195 x_start_date in date default sysdate,
196 x_end_date in date default null) return number is
197
198 uid number := -1;
199 respid number := -1;
200 appid number := -1;
201 secid number := -1;
202 cnt number := 0;
203 TYPE cur_typ IS REF CURSOR;
204 c cur_typ;
205
206 begin
207
208 -- Do nothing if no user defined
209 if (x_user_group_clause is null) then
210 return(0);
211 end if;
212
213 -- Add a single responsibility to a group of users
214
215 begin
216 select application_id into appid
217 from fnd_application
218 where application_short_name = x_resp_application;
219 exception
220 when no_data_found then
221 fnd_message.set_name('FND', 'FND-INVALID APPLICATION');
222 fnd_message.set_token('APPL', x_resp_application);
223 app_exception.raise_exception;
224 end;
225
226 begin
227 select responsibility_id into respid
228 from fnd_responsibility
229 where application_id = appid
230 and responsibility_key = x_responsibility;
231 exception
232 when no_data_found then
233 fnd_message.set_name('FND', 'FND-INVALID RESPONSIBILITY');
234 fnd_message.set_token('RESP', x_responsibility);
235 app_exception.raise_exception;
236 end;
237
238 -- Security group should copied from the original --
239
240 if (x_security_group is not null) then
241 begin
242 select security_group_id into secid
243 from fnd_security_groups
244 where security_group_key = x_security_group;
245 exception
246 when no_data_found then
247 fnd_message.set_name('FND', 'FND-INVALID SECURITY');
248 fnd_message.set_token('SEC', x_security_group);
249 app_exception.raise_exception;
250 end;
251 end if;
252
253 cnt := 0;
254 OPEN c FOR x_user_group_clause;
255 LOOP
256 if (x_security_group is not null) then
257 FETCH c INTO uid;
258 else
259 FETCH c INTO uid, secid;
260 end if;
261
262 EXIT WHEN c%NOTFOUND;
263
264 -- process each row (user)
265 fnd_user_resp_groups_api.UPLOAD_ASSIGNMENT(
266 USER_ID => uid,
267 RESPONSIBILITY_ID => respid,
268 RESPONSIBILITY_APPLICATION_ID => appid,
269 SECURITY_GROUP_ID => secid,
270 START_DATE => x_start_date,
271 END_DATE => x_end_date,
272 DESCRIPTION => x_description);
273
274 cnt := cnt + 1;
275 END LOOP;
276 CLOSE c;
277
278 return(cnt);
279 end AddRespUserGroup;
280
281 --------------------------------------------------------------------------
282 -- AddRespIdUserGroup
283 -- Assign a responsibility which identified by ID to a group of users.
284 -- The group of users is defined in x_user_group_clause.
285 -- You can optionally supply security, description, start_date and end_date.
286 -- The return value is the number of users processed.
287 --
288 -- Usage Example
289 -- declare
290 -- user_clause varchar2(2000);
291 -- begin
292 -- user_clause := fnd_sec_bulk.UserAssignRespId(0, 101, 'STANDARD');
293 -- cnt := fnd_sec_bulk.AddRespIdUserGroup(user_clause, 0, 20420);
294 -- end;
295 --
296 -- Input Arguments
297 -- x_user_group_clause: A sql statement returns all user's user_id
298 -- For example, "select user_id from fnd_user where..."
299 -- x_resp_application_id:Responsibility application id
300 -- x_responsibility_id: Responsibility id
301 -- x_security_group: Security Group. Default is null.
302 -- If x_user_group_clause is provided from calling
303 -- UserAssignRespId(), then DO NOT pass in
304 -- x_security_group.
305 -- If x_user_group_clause is provided by yourself,
306 -- then you SHOULD input the x_security_group.
307 -- x_description: Description
308 -- x_start_date: Start date
309 -- x_end_date: End date
310
311 function AddRespIdUserGroup(
312 x_user_group_clause in varchar2,
313 x_resp_application_id in number,
314 x_responsibility_id in number,
315 x_security_group in varchar2 default '',
316 x_description in varchar2 default '',
317 x_start_date in date default sysdate,
318 x_end_date in date default null) return number is
319
320 uid number := -1;
321 respid number := -1;
322 appid number := -1;
323 secid number := -1;
324 cnt number := 0;
325 TYPE cur_typ IS REF CURSOR;
326 c cur_typ;
327
328 begin
329
330 -- Do nothing if no user defined
331 if (x_user_group_clause is null) then
332 return(0);
333 end if;
334
335 -- Add a single responsibility to a group of users
336
337 begin
338 select application_id into appid
339 from fnd_application
340 where application_id = x_resp_application_id;
341 exception
342 when no_data_found then
343 fnd_message.set_name('FND', 'FND-INVALID APPLICATION');
344 fnd_message.set_token('APPL', to_char(x_resp_application_id));
345 app_exception.raise_exception;
346 end;
347
348 begin
349 select responsibility_id into respid
350 from fnd_responsibility
351 where application_id = appid
352 and responsibility_id = x_responsibility_id;
353 exception
354 when no_data_found then
355 fnd_message.set_name('FND', 'FND-INVALID RESPONSIBILITY');
356 fnd_message.set_token('RESP', to_char(x_responsibility_id));
357 app_exception.raise_exception;
358 end;
359
360 if (x_security_group is not null) then
361 begin
362 select security_group_id into secid
363 from fnd_security_groups
364 where security_group_key = x_security_group;
365 exception
366 when no_data_found then
367 fnd_message.set_name('FND', 'FND-INVALID SECURITY');
368 fnd_message.set_token('SEC', x_security_group);
369 app_exception.raise_exception;
370 end;
371 end if;
372
373 cnt := 0;
374 OPEN c FOR x_user_group_clause;
375 LOOP
376 if (x_security_group is not null) then
377 FETCH c INTO uid;
378 else
379 FETCH c INTO uid, secid;
380 end if;
381
382 EXIT WHEN c%NOTFOUND;
383
384 -- process each row (user)
385 fnd_user_resp_groups_api.UPLOAD_ASSIGNMENT(
386 USER_ID => uid,
387 RESPONSIBILITY_ID => respid,
388 RESPONSIBILITY_APPLICATION_ID => appid,
389 SECURITY_GROUP_ID => secid,
390 START_DATE => x_start_date,
391 END_DATE => x_end_date,
392 DESCRIPTION => x_description);
393
394 cnt := cnt + 1;
395 END LOOP;
396 CLOSE c;
397 return(cnt);
398 end AddRespIdUserGroup;
399
400 --------------------------------------------------------------------------
401 -- UserAssignResp
402 -- Find all the users that have this given responsibility key and
403 -- security group pair.
404 -- Construct a full qualified sql statement to return all the user_ids.
405 -- So that this kind of sql statement can be used when calling
406 -- AddRespUserGroup().
407 -- Usage Example
408 -- declare
409 -- user_clause varchar2(2000);
410 -- cnt number;
411 -- begin
412 -- user_clause := fnd_sec_bulk.UserAssignResp('FND',
413 -- 'SYSTEM_ADMINISTRATOR',
414 -- 'STANDARD');
415 -- cnt := fnd_sec_bulk.AddRespUserGroup(user_clause,
416 -- 'FND',
417 -- 'SYSTEM_ADMINISTRATOR_GUI');
418 -- end;
419 -- Input Arguments
420 -- x_resp_application: Responsibility application short name
421 -- x_responsibility: Responsibility key
422 -- x_security_group: Responsibility security group name
423
424 function UserAssignResp(
425 x_resp_application in varchar2,
426 x_responsibility in varchar2,
427 x_security_group in varchar2 default 'STANDARD')
428 return varchar2 is
429
430 user_group_clause varchar2(2000) := '';
431 respid number := -1;
432 appid number := -1;
433 secid number := -1;
434 begin
435 -- return a sql statement clause which defines a group of users
436
437 begin
438
439 select application_id into appid
440 from fnd_application
441 where application_short_name = x_resp_application;
442
443 select security_group_id into secid
444 from fnd_security_groups
445 where security_group_key = x_security_group;
446
447 select responsibility_id into respid
448 from fnd_responsibility
449 where application_id = appid
450 and responsibility_key = x_responsibility;
451
452 user_group_clause :=
453 'select r.user_id, r.security_group_id ' ||
454 'from fnd_user_resp_groups r '||
455 'where r.responsibility_id = '||respid||
456 ' and r.security_group_id = '||secid||
457 ' and r.responsibility_application_id = '||appid;
458
459 exception
460 when no_data_found then
461 user_group_clause := null;
462 end;
463
464 return(user_group_clause);
465
466 end UserAssignResp;
467
468
469 --------------------------------------------------------------------------
470 -- UserAssignRespId
471 -- Find all the users that have this given responsibility identified by ID
472 -- and security group pair.
473 -- Construct a full qualified sql statement to return all the user_ids.
474 -- So that this kind of sql statement can be used when calling
475 -- AddRespUserGroup().
476 -- Usage Example
477 -- declare
478 -- user_clause varchar2(2000);
479 -- cnt number;
480 -- begin
481 -- user_clause := fnd_sec_bulk.UserAssignRespId(0,101, 'STANDARD');
482 --
483 -- cnt := fnd_sec_bulk.AddRespIdUserGroup(user_clause, 0, 20420);
484 -- end;
485 -- Input Arguments
486 -- x_resp_application_id:Responsibility application id
487 -- x_responsibility_id: Responsibility id
488 -- x_security_group: Responsibility security group name
489
490 function UserAssignRespId(
491 x_resp_application_id in number,
492 x_responsibility_id in number,
493 x_security_group in varchar2 default 'STANDARD')
494 return varchar2 is
495
496 user_group_clause varchar2(2000) := '';
497 respid number := -1;
498 appid number := -1;
499 secid number := -1;
500 begin
501 -- return a sql statement clause which defines a group of users
502
503 begin
504
505 select application_id into appid
506 from fnd_application
507 where application_id = x_resp_application_id;
508
509 select security_group_id into secid
510 from fnd_security_groups
511 where security_group_key = x_security_group;
512
513 -- There was this upgrade script afpnls01.sql which deleted all the
514 -- 107 responsibility from fnd_responsibility table. So, we have to
515 -- skip this validation for that sake.
516 -- Although the 107 responsibility got deleted from the table but
517 -- all the assignments are still in fnd_user_resp_group table.
518 /*
519 select responsibility_id into respid
520 from fnd_responsibility
521 where application_id = appid
522 and responsibility_id = x_responsibility_id;
523 */
524 respid := x_responsibility_id;
525
526
527
528 user_group_clause :=
529 'select r.user_id, r.security_group_id '||
530 'from fnd_user_resp_groups r '||
531 'where r.responsibility_id = '||respid||
532 ' and r.security_group_id = '||secid||
533 ' and r.responsibility_application_id = '||appid;
534
535 exception
536 when no_data_found then
537 user_group_clause := null;
538 end;
539
540 return(user_group_clause);
541
542 end UserAssignRespId;
543
544
545 --------------------------------------------------------------------------
546 -- UpdateUserGroup
547 -- Update some of user attributes for a group of users.
548 -- Non-specified attribute is treated as taking the current
549 -- default value.
550 -- The group of users is defined in x_user_group_clause which is a full
551 -- qualified "select" sql statement.
552 --
553 -- Input Arguments
554 -- x_user_group_clause: A sql statement returns all user's user_id
555 -- For example, "select user_id from fnd_user where..."
556 -- x_start_date: Start date
557 -- x_end_date: End date
558 -- x_description: User Description
559 -- x_password_lifespan_access: To control password expiration
560 -- x_password_lifespan_days: To control password expiration
561 -- x_email_address: User email address
562 -- x_fax: User fax number
563
564 procedure UpdateUserGroup(
565 x_user_group_clause in varchar2,
566 x_start_date in date default null,
567 x_end_date in date default null,
568 x_description in varchar2 default null,
569 x_password_lifespan_accesses in number default null,
570 x_password_lifespan_days in number default null,
571 x_email_address in varchar2 default null,
572 x_fax in varchar2 default null) is
573
574 uid number;
575 TYPE cur_typ IS REF CURSOR;
576 c cur_typ;
577
578 begin
579
580 if (x_user_group_clause is null) then
581 return;
582 end if;
583
584 -- Update a group of user with the input user attriutes
585
586 open c for x_user_group_clause;
587 loop
588 fetch c into uid;
589 exit when c%NOTFOUND;
590
591 update fnd_user
592 set last_update_date = sysdate,
593 start_date = nvl(x_start_date, start_date),
594 end_date = nvl(x_end_date, end_date),
595 description = nvl(x_description, description),
596 password_lifespan_accesses = nvl(x_password_lifespan_accesses,
597 password_lifespan_accesses),
598 password_lifespan_days = nvl(x_password_lifespan_days,
599 password_lifespan_days),
600 email_address = nvl(x_email_address, email_address),
601 fax = nvl(x_fax, fax)
602 where user_id = uid;
603
604 -- Added for Function Security Cache Invalidation Project.
605 fnd_function_security_cache.update_user(uid);
606
607 end loop;
608 close c;
609
610 end UpdateUserGroup;
611
612
613
614 --------------------------------------------------------------------------
615 -- UpdateUserGroupTemplate
616 -- Update a group of users to have the same privileges as the given
617 -- template user. Supported privileges are user attributes, assigned
618 -- responsibilities and profiles.
619 -- The group of users is defined in x_user_group_clause.
620 -- You can optionally choose to clone all user attributes, all
621 -- responsibilities or all profile options.
622 --
623 -- Usage Example
624 -- declare
625 -- user_clause varchar2(2000);
626 -- begin
627 -- user_clause := fnd_sec_bulk.UserAssignResp('FND',
628 -- 'ASSISTANT_SYSTEM_ADMINISTRATOR',
629 -- 'STANDARD');
630 -- /* This will give all users SYSADMIN's's profile but not */
631 -- /* responsibilities and other attributes. */
632 -- fnd_sec_bulk.UpdateUserGroupTemplate(user_clause,
633 -- 'SYSADMIN',
634 -- FALSE, FALSE, TRUE);
635 -- end;
636 --
637 -- Input Arguments
638 -- x_user_group_clause: A select sql statement returns all user's user_id
639 -- For example, "select user_id from fnd_user where..."
640 -- x_template_user: The user name that you are going to clone from
641 -- x_attribute_flag: Whether to clone user attributes
642 -- x_responsibility_flag:Whether to clone responsibility
643 -- x_profile_flag: Whether to clone profile
644
645 procedure UpdateUserGroupTemplate(
646 x_user_group_clause in varchar2,
647 x_template_user in varchar2,
648 x_attribute_flag in boolean default TRUE,
649 x_responsibility_flag in boolean default TRUE,
650 x_profile_flag in boolean default TRUE) is
651
652 uname varchar2(100);
653 uid number;
654 TYPE cur_typ IS REF CURSOR;
655 c cur_typ;
656 begin
657 -- Update a group of users by copying the user attributes, profile and
658 -- responsibilities from a template user.
659 -- x_attribute_flag, x_responsibility_flag and x_profile_flag is
660 -- to control whether to copy those value.
661 -- For example if you are going to assign bunch of users with the same
662 -- privilege as user "ACCOUNT_MANAGER".
663
664
665 if (x_user_group_clause is null) then
666 return;
667 end if;
668
669 open c for x_user_group_clause;
670 loop
671 fetch c into uid;
672 exit when c%NOTFOUND;
673
674 begin
675 select user_name into uname
676 from fnd_user where user_id = uid;
677 exception
678 when no_data_found then
679 -- just skip this user
680 null;
681 end;
682
683 if (x_attribute_flag) then
684 cloneuser_attr(x_template_user, uname, '', '');
685 end if;
686
687 if (x_responsibility_flag) then
688 cloneuser_resp(x_template_user, uname);
689 end if;
690
691 if (x_profile_flag) then
692 cloneuser_prof(x_template_user, uname);
693 end if;
694
695 end loop;
696 close c;
697
698 end UpdateUserGroupTemplate;
699
700
701 --------------------------------------------------------------------------
702 -- DisableUserGroup
703 -- Disable a group of user by setting their end_date to sysdate.
704 -- The group of users is defined in x_user_group_clause.
705 --
706 -- Usage Example
707 --
708 -- Input Arguments
709 -- x_user_group_clause: A sql statement returns all user's user_id
710
711 /*
712 procedure DisableUserGroup(
713 x_user_group_clause in varchar2) is
714
715 sql_string varchar2(2000);
716 begin
717 -- set end_date to disable group of users.
718
719 sql_string := 'update fnd_user set end_date = sysdate where userid in ('||
720 x_user_group_clause||')';
721 execute immediate sql_string;
722
723 end DisableUserGroup;
724 */
725
726 end FND_SEC_BULK;
[Home] [Help]