1 package body Fnd_User_Resp_Groups_api as
2 /* $Header: AFSCURGB.pls 120.16.12020000.4 2013/03/25 19:50:40 fskinner ship $ */
3
4 C_PKG_NAME CONSTANT VARCHAR2(30) := 'FND_USER_RESP_GROUPS_API';
5 C_LOG_HEAD CONSTANT VARCHAR2(240)
6 := 'fnd.plsql.FND_USER_RESP_GROUPS_API.';
7
8 -- This is a one level cache used in check_secgrp_enabled.
9 G_ENABLED_RESPID NUMBER := null;
10 G_ENABLED_APPID NUMBER := null;
11 G_ENABLED_RETVAL varchar2(1) := null;
12
13
14 --
15 -- Generic_Error (Internal)
16 --
17 -- Set error message and raise exception for unexpected sql errors.
18 --
19 procedure Generic_Error(
20 routine in varchar2,
21 errcode in number,
22 errmsg in varchar2)
23 is
24 begin
25 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
26 fnd_message.set_token('ROUTINE', routine);
27 fnd_message.set_token('ERRNO', errcode);
28 fnd_message.set_token('REASON', errmsg);
29 app_exception.raise_exception;
30 end Generic_Error;
31
32 --
33 -- Returns 'Y' if security groups are enabled for this app, 'N' otherwise.
34 --
35 function check_secgrp_enabled(respid in number, appid in number)
36 return varchar2
37 is
38 prof_value varchar2(240);
39 begin
40 /* Check one level cache first */
41 if ( (G_ENABLED_RESPID = respid)
42 and (G_ENABLED_APPID = appid)) then
43 return G_ENABLED_RETVAL;
44 end if;
45
46 prof_value := nvl(fnd_profile.value_specific(
47 'ENABLE_SECURITY_GROUPS',
48 NULL,
49 respid,
50 appid),
51 'N');
52
53 if (prof_value = 'N') then
54 G_ENABLED_RETVAL := 'N';
55 else
56 G_ENABLED_RETVAL := 'Y';
57 end if;
58
59 G_ENABLED_RESPID := respid;
60 G_ENABLED_APPID := appid;
61
62 return G_ENABLED_RETVAL;
63 end check_secgrp_enabled;
64
65
66 --
67 -- Role_Name_from_Resp_name -
68 --
69 -- Returns role name in the format FND_RESP|SECGRPKEY|APPSNAME|RESPKEY
70 -- from the names passed in
71 --
72 function Role_Name_from_Resp_name(
73 x_respkey in varchar2,
74 x_applsname in varchar2,
75 x_secgrpkey in varchar2) return varchar2 is
76 rolename varchar2(1000);
77 begin
78
79 rolename := 'FND_RESP'||'|'||
80 x_applsname||'|'||
81 x_respkey||'|'||
82 x_secgrpkey;
83
84 /* Colons are a special character that is currently not allowed in */
85 /* workflow so we work around this by replacing it with the string %col.*/
86 /* See bug 3591913. If that is fixed we might be able to remove this. */
87 rolename := replace(rolename, ':', '%col');
88
89 if(LENGTHB(rolename) > 320) then
90 /* This should never happen, but if it does, show what went wrong.*/
91 rolename := substrb('UNEXPECTED_ERROR:KEYTOOBIG:Role_Name_from_Resp:'||
92 rolename, 1, 320);
93
94 end if;
95
96 return rolename;
97 end Role_Name_from_Resp_name;
98
99 --
100 -- Role_Name_from_Resp (INTERNAL ONLY)-
101 --
102 -- Returns role name in the format FND_RESP|APPSNAME|RESPKEY|SECGRPKEY
103 -- from the security group and resp passed in.
104 --
105 function Role_Name_from_Resp(
106 x_resp_id in number,
107 x_resp_appl_id in number,
108 x_secgrp_id in number)
109 return varchar2 is
110 rolename varchar2(1000);
111 secgrpkey varchar2(30);
112 appsname varchar2(50);
113 respkey varchar2(30);
114 begin
115
116 select security_group_key
117 into secgrpkey
118 from fnd_security_groups
119 where security_group_id = x_secgrp_id;
120
121 select application_short_name
122 into appsname
123 from fnd_application
124 where application_id = x_resp_appl_id;
125
126 select responsibility_key
127 into respkey
128 from fnd_responsibility
129 where application_id = x_resp_appl_id
130 and responsibility_id = x_resp_id;
131
132 return Role_Name_from_Resp_name(respkey, appsname, secgrpkey);
133 end Role_Name_from_Resp;
134
135
136 /* This is a version of role_name_from_resp which won't return errors, */
137 /* to be used when calling from somewhere that errors can't be trapped */
138 /* like inline inside a SQL select statement */
139 function Role_Name_from_Resp_No_Exc(
140 x_resp_id in number,
141 x_resp_appl_id in number,
142 x_secgrp_id in number)
143 return varchar2 is
144 begin
145
146 return fnd_user_resp_groups_api.Role_Name_from_Resp(
147 x_resp_id,
148 x_resp_appl_id,
149 x_secgrp_id);
150
151 exception
152 when no_data_found then
153 return 'BAD_FK:'||x_resp_id||':'|| x_resp_appl_id ||':'||x_secgrp_id;
154 when others then
155 return 'ERROR:'||x_resp_id||':'|| x_resp_appl_id ||':'||x_secgrp_id;
156 end Role_Name_from_Resp_No_Exc;
157
158 -- Upgrade_Resp_Role
159 -- Converts role names from FND_RESPX:Y format to FND_RESP|A|B|C format
160 -- if necessary. returns upgraded role name or original.
161 function upgrade_resp_role(respid in number,
162 appid in number) return varchar2 is
163 new_role_name varchar2(255);
164 begin
165
166 begin
167 new_role_name := fnd_user_resp_groups_api.Role_Name_from_Resp(
168 x_resp_id => respid,
169 x_resp_appl_id => appid,
170 x_secgrp_id => 0);
171
172 exception
173 when no_data_found then /* If invalid foreign keys, bail. */
174 return 'INVALID_FK_APPID_'||appid||'_RESPID_'||respid;
175 end;
176
177 return new_role_name;
178 end upgrade_resp_role;
179
180 --
181 -- Assignment_Check (INTERNAL routine only)
182 --
183 -- Check whether a particular assignment of a user to a role exists,
184 -- regardless of start/end date. This is different from
185 -- wf_directory.IsPerformer which only operates for current sysdate.
186 -- In: username- user name
187 -- In: rolename- role name
188 --
189 function Assignment_Check(username in varchar2,
190 rolename in varchar2,
191 direct_flag in varchar2)
192 return boolean is
193 result boolean;
194 dummy number;
195 begin
196
197 if(direct_flag = 'E') then
198 begin
199 select null
200 into dummy
201 from wf_all_user_roles
202 where user_name = username
203 and role_name = rolename
204 and rownum = 1;
205 result := TRUE;
206 exception
207 when no_data_found then
208 result := FALSE;
209 when others then
210 Generic_Error('FND_USER_RESP_GROUPS_API.ASSIGNMENT_CHECK(E)',
211 sqlcode, sqlerrm);
212 end;
213 elsif (direct_flag = 'D') then
214 begin
215 select null
216 into dummy
217 from wf_all_user_roles
218 where user_name = username
219 and role_name = rolename
220 and assignment_type in ('D', 'B')
221 and rownum = 1;
222 result := TRUE;
223 exception
224 when no_data_found then
225 result := FALSE;
226 when others then
227 Generic_Error('FND_USER_RESP_GROUPS_API.ASSIGNMENT_CHECK(D)',
228 sqlcode, sqlerrm);
229 end;
230 elsif (direct_flag = 'I') then
231 begin
232 select null
233 into dummy
234 from wf_all_user_roles
235 where user_name = username
236 and role_name = rolename
237 and assignment_type in ('I', 'B')
238 and rownum = 1;
239 result := TRUE;
240 exception
241 when no_data_found then
242 result := FALSE;
243 when others then
244 Generic_Error('FND_USER_RESP_GROUPS_API.ASSIGNMENT_CHECK(I)',
245 sqlcode, sqlerrm);
246 end;
247 end if;
248 return result;
249 end Assignment_Check;
250
251 --
252 -- Assignment_Exists
253 -- Check if user/resp/group assignment exists. This API does not check
254 -- start or end dates on the user, repsonsibility, or responsibility
255 -- assignment.
256 -- IN
257 -- user_id - User to get assignment
258 -- responsibility_id - Responsibility to be assigned
259 -- responsibility_application_id - Resp Application to be assigned
260 -- security_group_id - Security Group to be assigned (default to current)
261 -- direct_flag- 'D', 'I', or 'E' (default) determines whether this checks
262 -- indirect assignments from wf_role_hierarchy or just
263 -- direct assignments.
264 -- 'D'= Direct only. Dates can be updated.
265 -- 'I'= Indirect only. Dates cannot be updated on these assignments.
266 -- 'E'= Either Direct or Indirect. (this is the default)
267 -- RETURNS
268 -- TRUE if assignment is found
269 --
270 function Assignment_Exists(
271 user_id in number,
272 responsibility_id in number,
273 responsibility_application_id in number,
274 security_group_id in number default null,
275 direct_flag in varchar2 default null /* null means 'E': Direct or Indirect*/
276 )
277 return boolean
278 is
279 dummy number;
280 sgid number;
281 rolename varchar2(320);
282 username varchar2(100);
283 l_direct_flag varchar2(1);
284 begin
285 if(direct_flag is NULL) then
286 l_direct_flag := 'E'; /* Default to 'E' meaning Either direct or indirect*/
287 else
288 l_direct_flag := direct_flag;
289 end if;
290
291 if (security_group_id is null) then
292 sgid := fnd_global.security_group_id;
293 else
294 sgid := security_group_id;
295 end if;
296
297 begin
298 select user_name
299 into username
300 from fnd_user
301 where user_id = assignment_exists.user_id;
302
303 rolename := role_name_from_resp(responsibility_id,
304 responsibility_application_id,
305 sgid);
306
307 exception /* This exception handler is new to fix bug 3573846. */
308 when no_data_found then
309 /* If some data passed is invalid, there can't be an assignment.*/
310 /* This preserves backward compatibility. */
311 return FALSE;
312 end;
313
314 return Assignment_Check(
315 username ,
316 rolename,
317 l_direct_flag);
318
319 exception
320 when others then
321 Generic_Error('FND_USER_RESP_GROUPS_API.ASSIGNMENT_EXISTS',
322 sqlcode, sqlerrm);
323 end Assignment_Exists;
324
325
326 --
327 -- Validates the security context to determine if the given user has access
328 -- to the given responsibility.
329 -- IN
330 -- p_user_id - the user id
331 -- p_resp_appl_id - the application id of the responsibility
332 -- p_responsibility_id - the responsibility id
333 -- p_security_group_id - the security group id
334 -- OUT
335 -- x_status:
336 -- 'N' if the security context is not valid
337 -- 'Y' if the security context is valid
338 --
339 procedure validate_security_context(
340 p_user_id in number,
341 p_resp_appl_id in number,
342 p_responsibility_id in number,
343 p_security_group_id in number,
344 x_status out nocopy varchar2)
345 is
346 begin
347 /* # Someday this routine could be reimplemented against the database */
348 /* objects underlying the fnd_user_resp_groups view, but not needed now.*/
349 x_status := 'N';
350
351 select 'Y'
352 into x_status
353 from dual
354 where exists
355 (select null
356 from fnd_user u,
357 fnd_user_resp_groups urg,
358 fnd_responsibility r
359 where u.user_id = p_user_id
360 and sysdate between u.start_date and nvl(u.end_date, sysdate)
361 and urg.user_id = u.user_id
362 and urg.responsibility_application_id = p_resp_appl_id
363 and urg.responsibility_id = p_responsibility_id
364 and urg.security_group_id in (-1, p_security_group_id)
365 /*NOT NEEDED: and sysdate between urg.start_date and nvl(urg.end_date,sysdate)*/
366 and r.application_id = urg.responsibility_application_id
367 and r.responsibility_id = urg.responsibility_id
368 and sysdate between r.start_date and nvl(r.end_date, sysdate));
369 exception
370 when no_data_found then
371 x_status := 'N';
372
373 end validate_security_context;
374
375
376 --
377 -- Lock_Assignment
378 -- Lock the row for an assignment (used by a UI)
379 -- IN
380 -- user_id - User
381 -- responsibility_id - Responsibility
382 -- responsibility_application_id - Resp Application
383 -- security_group_id - Security Group
384 -- start_date - Start date of assignment
385 -- end_date - End date of assignment
386 --
387 procedure Lock_Assignment(
388 x_user_id in number,
389 x_responsibility_id in number,
390 x_resp_application_id in number,
391 x_security_group_id in number,
392 x_start_date in date,
393 x_end_date in date,
394 x_description in varchar2)
395 is
396 cursor c (user varchar2, role varchar2) is
397 select start_date,
398 end_date
399 from wf_all_user_role_assignments --BUG5467610
400 where user_name = user
401 and role_name = role
402 and rownum = 1
403 for update of start_date nowait;
404 rolename varchar2(1000);
405 username varchar2(100);
406 recinfo c%rowtype;
407 begin
408
409 select user_name
410 into username
411 from fnd_user
412 where user_id = x_user_id;
413
414 rolename := role_name_from_resp(x_responsibility_id,
415 x_resp_application_id,
416 x_security_group_id);
417
418 open c(username, rolename);
419 fetch c into recinfo;
420 if (c%notfound) then
421 close c;
422 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
423 app_exception.raise_exception;
424 end if;
425 close c;
426 if ( ((recinfo.start_date = x_start_date)
427 OR ((recinfo.start_date is null) AND (x_start_date is null)))
428 AND ((recinfo.end_date = x_end_date)
429 OR ((recinfo.end_date is null) AND (x_end_date is null)))
430 ) then
431 null;
432 else
433 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
434 app_exception.raise_exception;
435 end if;
436
437 end Lock_Assignment;
438
439
440 --
441 -- Insert_Assignment
442 -- Insert a new user/resp/group assignment
443 -- IN
444 -- user_id - User to get assignment
445 -- responsibility_id - Responsibility to be assigned
446 -- responsibility_application_id - Resp Application to be assigned
447 -- security_group_id - Security Group to be assigned
448 -- start_date - Start date of assignment
449 -- end_date - End date of assignment
450 -- description - Optional comment
451 -- EXCEPTION
452 -- If user/resp/group assignment already exists
453 --
454 procedure Insert_Assignment(
455 user_id in number,
456 responsibility_id in number,
457 responsibility_application_id in number,
458 security_group_id in number,
459 start_date in date,
460 end_date in date,
461 description in varchar2)
462 is
463 sgid number;
464 rolename varchar2(4000);
465 secgrpkey varchar2(30);
466 appsname varchar2(50);
467 respkey varchar2(30);
468 username varchar2(100);
469 l_user_orig_system varchar2(30);
470 l_user_orig_system_id number;
471 result boolean;
472 old_rolename varchar2(4000);
473 l_parameters wf_parameter_list_t := wf_parameter_list_t();
474 begin
475 if (security_group_id is null) then
476 sgid := fnd_global.security_group_id;
477 else
478 sgid := security_group_id;
479 end if;
480
481 rolename := role_name_from_resp(responsibility_id,
482 responsibility_application_id,
483 sgid);
484
485 --
486 -- Generate old role name for backwards compatibility.
487 --
488
489 old_rolename := 'FND_RESP'||responsibility_application_id||
490 ':'||responsibility_id;
491
492 select user_name
493 into username
494 from fnd_user
495 where user_id = Insert_assignment.user_id;
496
497
498 select application_short_name
499 into appsname
500 from fnd_application
501 where application_id = responsibility_application_id;
502
503
504 /* Check whether there already is a direct row; if so, */
505 /* we can't insert a duplicate. */
506 result := assignment_check(username, rolename, 'D');
507 if (result = TRUE) then
508 fnd_message.set_name('FND', 'FND_CANT_INSERT_USER_ROLE');
509 fnd_message.set_token('USERNAME', username);
510 fnd_message.set_token('ROLENAME', rolename);
511 fnd_message.set_token('ROUTINE',
512 'FND_USER_RESP_GROUPS_API.Insert_Assignment');
513 app_exception.raise_exception;
514 end if;
515
516
517 /* We can't just assume that the orig system is FND_USR. It could */
518 /* be PER because the row in wf_users/wf_roles is one or the other */
519 wf_directory.GetRoleOrigSysInfo(username,
520 l_user_orig_system,
521 l_user_orig_system_id);
522
523 /* In case there is no WF user, sync this user up so there is one.*/
524 /* Should never happen but be safe in case sync wasn't perfect in past */
525 /* # Should we remove this code and just trust the bulk sync? */
526 if( (l_user_orig_system is NULL)
527 and (l_user_orig_system_id is NULL)) then
528 fnd_user_pkg.user_synch(username);
529 wf_directory.GetRoleOrigSysInfo(username,
530 l_user_orig_system,
531 l_user_orig_system_id);
532 end if;
533
534 wf_local_synch.PropagateUserRole
535 (p_user_name=>username,
536 p_role_name=>rolename,
537 p_user_orig_system=>l_user_orig_system,
538 p_user_orig_system_id=>l_user_orig_system_id,
539 p_role_orig_system=>'FND_RESP',
540 p_role_orig_system_id=>responsibility_id,
541 p_start_date=>start_date,
542 p_expiration_date=>end_date,
543 p_overwrite=>TRUE,
544 p_raiseErrors=>TRUE,
545 p_parent_orig_system => 'FND_RESP',
546 p_parent_orig_system_id => responsibility_id,
547 p_ownerTag => appsname,
548 p_createdBy => fnd_global.user_id,/*Bug3626390*/
549 p_lastUpdatedBy => fnd_global.user_id,
550 p_lastUpdateLogin => 0,
551 p_creationDate => sysdate, /*Bug3626390 sysdate*/
552 p_lastUpdatedate=> sysdate,
553 p_assignmentReason=>description);
554
555 --
556 -- Need to propagate the old roles for backwards compatibility.
557 --
558
559 wf_local_synch.PropagateUserRole
560 (p_user_name=>username,
561 p_role_name=>old_rolename,
562 p_user_orig_system=>l_user_orig_system,
563 p_user_orig_system_id=>l_user_orig_system_id,
564 p_role_orig_system=>'FND_RESP'||responsibility_application_id,
565 p_role_orig_system_id=>responsibility_id,
566 p_start_date=>start_date,
567 p_expiration_date=>end_date,
568 p_overwrite=>TRUE,
569 p_raiseErrors=>TRUE,
570 p_parent_orig_system => 'FND_RESP'||responsibility_application_id,
571 p_parent_orig_system_id => responsibility_id,
572 p_ownerTag => appsname,
573 p_createdBy => fnd_global.user_id,/*Bug3626390*/
574 p_lastUpdatedBy => fnd_global.user_id,
575 p_lastUpdateLogin => 0,
576 p_creationDate => sysdate, /*Bug3626390 sysdate*/
577 p_lastUpdatedate=> sysdate,
578 p_assignmentReason=>description);
579
580 wf_event.raise('oracle.apps.fnd.security.user.assignment.change',
581 Insert_Assignment.user_id||':'||
582 Insert_Assignment.responsibility_id,
583 null, null);
584 --we have to raise a differnt event as for USER_INFO_CACHE
585 --the key should be just the user_id
586 wf_event.addparametertolist(p_name => 'FND_USER_ID',
587 p_value => Insert_Assignment.user_id,
588 p_parameterlist => l_parameters);
589
590 wf_event.addparametertolist(p_name => 'FND_RESPONSIBILITY_ID',
591 p_value => Insert_Assignment.responsibility_id,
592 p_parameterlist => l_parameters);
593
594 wf_event.addparametertolist(p_name => 'FND_APPS_SHORT_NAME',
595 p_value => appsname,
596 p_parameterlist => l_parameters);
597
598 wf_event.addparametertolist(p_name => 'FND_RESPONSIBILITY_APPS_ID',
599 p_value => Insert_Assignment.responsibility_application_id,
600 p_parameterlist => l_parameters);
601
602 wf_event.raise(p_event_name => 'oracle.apps.fnd.user.role.insert',
603 p_event_key => to_char(Insert_Assignment.user_id)||':'||to_char(Insert_Assignment.responsibility_id)||':'||appsname||':'||to_char(Insert_Assignment.responsibility_application_id),
604 p_event_data => NULL,
605 p_parameters => l_parameters,
606 p_send_date => Sysdate);
607
608 exception
609 when others then
610 Generic_Error('FND_USER_RESP_GROUPS_API.INSERT_ASSIGNMENT',
611 sqlcode, sqlerrm);
612 end Insert_Assignment;
613
614 --
615 -- Update_Assignment
616 -- Update an existing user/resp/group assignment
617 -- IN
618 -- KEY VALUES: These columns identify row to update
619 -- user_id - User to get assignment
620 -- responsibility_id - Responsibility to be assigned
621 -- responsibility_application_id - Resp Application to be assigned
622 -- security_group_id - Security Group to be assigned
623 -- UPDATE VALUES: These columns identify values to update
624 -- start_date - Start date of assignment
625 -- end_date - End date of assignment
626 -- description - Optional comment
627 -- FLAGS
628 -- update_who_columns- pass 'Y' or 'N' ('Y' is default if not passed)
629 -- 'N' = leave old who vals. 'Y'= update who cols to current user/date
630 -- EXCEPTION
631 -- If user/resp/group assignment does not exist
632 --
633 procedure Update_Assignment(
634 user_id in number,
635 responsibility_id in number,
636 responsibility_application_id in number,
637 security_group_id in number,
638 start_date in date,
639 end_date in date,
640 description in varchar2,
641 update_who_columns in varchar2 default null
642 /* 'N' = leave old who vals. 'Y' (default) = update who to current*/)
643 is
644 sgid number;
645 rolename varchar2(4000);
646 secgrpkey varchar2(30);
647 appsname varchar2(50);
648 respkey varchar2(30);
649 username varchar2(100);
650 l_user_orig_system varchar2(30);
651 l_user_orig_system_id number;
652 result boolean;
653 old_rolename varchar2(4000);
654 l_parameters wf_parameter_list_t := wf_parameter_list_t();
655 l_last_update_date date;
656 l_last_updated_by number;
657 l_last_update_login number;
658 l_creation_date date;
659 l_created_by number;
660 l_update_who boolean; --Bug5467610
661 begin
662
663 if (security_group_id is null) then
664 sgid := fnd_global.security_group_id;
665 else
666 sgid := security_group_id;
667 end if;
668
669 rolename := role_name_from_resp(responsibility_id,
670 responsibility_application_id,
671 sgid);
672
673 --
674 -- Generate old role name for backwards compatibility.
675 --
676
677 old_rolename := 'FND_RESP'||responsibility_application_id||
678 ':'||responsibility_id;
679
680 select user_name
681 into username
682 from fnd_user
683 where user_id = Update_assignment.user_id;
684
685
686 select application_short_name
687 into appsname
688 from fnd_application
689 where application_id = responsibility_application_id;
690
691
692 /* Check whether there already is a direct row to update; if not, */
693 /* the caller probably queried an indirect row and is trying to */
694 /* update it which is not allowed. */
695 result := assignment_check(username, rolename,'D');
696 if (result = FALSE) then
697 fnd_message.set_name('FND', 'FND_CANT_UPDATE_USER_ROLE');
698 fnd_message.set_token('USERNAME', username);
699 fnd_message.set_token('ROLENAME', rolename);
700 fnd_message.set_token('ROUTINE',
701 'FND_USER_RESP_GROUPS_API.Update_Assignment');
702 app_exception.raise_exception;
703 end if;
704
705 /* We can't just assume that the orig system is FND_USR. It could */
706 /* be PER because the row in wf_users/wf_roles is one or the other */
707 wf_directory.GetRoleOrigSysInfo(username,
708 l_user_orig_system,
709 l_user_orig_system_id);
710
711 /* In case there is no WF user, sync this user up so there is one.*/
712 /* Should never happen but be safe in case sync wasn't perfect in past */
713 /* # Should we remove this code and just trust bulk sync?*/
714 if( (l_user_orig_system is NULL)
715 and (l_user_orig_system_id is NULL)) then
716 fnd_user_pkg.user_synch(username);
717 wf_directory.GetRoleOrigSysInfo(username,
718 l_user_orig_system,
719 l_user_orig_system_id);
720 end if;
721
722 /* Get the old who values. Note that there is no exception handler around*/
723 /* this because the assignment_check() above should have already verified */
724 /* that we have the row. */
725 /* NOTE: Workflow has added support for '*NOCHANGE*' for the who */
726 /* parameters for RUP4. This SQL will be removed and this option */
727 /* will be used */
728 -- Bug5121512 - Replaced SQL to eliminate the case where it is returning
729 -- a 1422.
730 -- Bug5467610 Removing the following SQL as it is no longer needed and adding
731 -- the p_updatewho parameter to the wf_local_synch.PropagateUserRole call.
732
733 -- select created_by, creation_date, last_updated_by,
734 -- last_update_date, last_update_login
735 -- into l_created_by, l_creation_date, l_last_updated_by,
736 -- l_last_update_date, l_last_update_login
737 -- from wf_all_user_roles
738 -- where user_name = Update_Assignment.username
739 -- and role_orig_system_id = Update_Assignment.responsibility_id
740 -- and role_name = Update_Assignment.rolename
741 -- and role_orig_system = 'FND_RESP';
742
743 /* If we passed the flag saying to update the who columns */
744 /* then we set the last_update who columns to current user/date. */
745 if (update_who_columns = 'Y') then
746 l_last_updated_by := fnd_global.user_id;
747 l_last_update_login := 0;
748 l_last_update_date := sysdate;
749 l_update_who := TRUE; -- Bug5467610 update who columns.
750 -- Else part has been added for bug 14709898 as null in l_update_who
751 -- is defaulted to TRUE by the WF team.
752 else
753 l_update_who := FALSE;
754 end if;
755
756
757 -- Bug4747169 - Removed passing in the parameters p_created_by and
758 -- p_creation_date as this is the update processing.
759
760 wf_local_synch.PropagateUserRole
761 (p_user_name=>username,
762 p_role_name=>rolename,
763 p_user_orig_system=>l_user_orig_system,
764 p_user_orig_system_id=>l_user_orig_system_id,
765 p_role_orig_system=>'FND_RESP',
766 p_role_orig_system_id=>responsibility_id,
767 p_start_date=>start_date,
768 p_expiration_date=>end_date,
769 p_overwrite=>TRUE,
770 p_raiseErrors=>TRUE,
771 p_parent_orig_system => 'FND_RESP',
772 p_parent_orig_system_id => responsibility_id,
773 p_ownerTag => appsname,
774 p_createdBy => l_created_by,
775 p_creationDate => l_creation_date, /*Bug3626390 sysdate*/
776 p_lastUpdatedate=> l_last_update_date,
777 p_lastUpdatedBy => l_last_updated_by,
778 p_lastUpdateLogin => l_last_update_login,
779 p_assignmentReason=>description,
780 p_updatewho => l_update_who); -- Bug5467610
781
782 --
783 -- Need to propagate the old role name
784 --
785
786 wf_local_synch.PropagateUserRole
787 (p_user_name=>username,
788 p_role_name=>old_rolename,
789 p_user_orig_system=>l_user_orig_system,
790 p_user_orig_system_id=>l_user_orig_system_id,
791 p_role_orig_system=>'FND_RESP'||responsibility_application_id,
792 p_role_orig_system_id=>responsibility_id,
793 p_start_date=>start_date,
794 p_expiration_date=>end_date,
795 p_overwrite=>TRUE,
796 p_raiseErrors=>TRUE,
797 p_parent_orig_system => 'FND_RESP'||responsibility_application_id,
798 p_parent_orig_system_id => responsibility_id,
799 p_ownerTag => appsname,
800 p_createdBy => l_created_by,
801 p_creationDate => l_creation_date, /*Bug3626390 sysdate*/
802 p_lastUpdatedate=> l_last_update_date,
803 p_lastUpdatedBy => l_last_updated_by,
804 p_lastUpdateLogin => l_last_update_login,
805 p_assignmentReason=>description,
806 p_updatewho => l_update_who); -- Bug5467610
807
808
809 wf_event.raise('oracle.apps.fnd.security.user.assignment.change',
810 Update_Assignment.user_id||':'||
811 Update_Assignment.responsibility_id,
812 null, null);
813 --Raise the invalidation event attached to the USER_INFO_CACHE
814 --(ideally this should be done by the wf API once the propagation
815 --has been sucessful) / or if they are not doing it then we should attach
816 --our business event to the CACHE as we do not really know what
817 --subscriptions are attcahed to the wf event.
818 --o.k putting our event (but we need to change the b3664848.ldt
819 --and the event-subscription file to attach this event to the
820 --bes control group.
821 wf_event.addparametertolist(p_name => 'FND_USER_ID',
822 p_value => Update_Assignment.user_id,
823 p_parameterlist => l_parameters);
824
825 wf_event.addparametertolist(p_name => 'FND_RESPONSIBILITY_ID',
826 p_value => Update_Assignment.responsibility_id,
827 p_parameterlist => l_parameters);
828
829 wf_event.addparametertolist(p_name => 'FND_APPS_SHORT_NAME',
830 p_value => appsname,
831 p_parameterlist => l_parameters);
832
833 wf_event.addparametertolist(p_name => 'FND_RESPONSIBILITY_APPS_ID',
834 p_value => Update_Assignment.responsibility_application_id,
835 p_parameterlist => l_parameters);
836
837 wf_event.raise(p_event_name => 'oracle.apps.fnd.user.role.update',
838 p_event_key => to_char(Update_Assignment.user_id)||':'||to_char(Update_Assignment.responsibility_id)||':'||appsname||':'||to_char(Update_Assignment.responsibility_application_id),
839 p_event_data => NULL,
840 p_parameters => l_parameters,
841 p_send_date => Sysdate);
842
843 exception
844 when others then
845 Generic_Error('FND_USER_RESP_GROUPS_API.UPDATE_ASSIGNMENT',
846 sqlcode, sqlerrm);
847
848 end Update_Assignment;
849
850 procedure LOAD_ROW (
851 X_USER_NAME in VARCHAR2,
852 X_RESP_KEY in VARCHAR2,
853 X_APP_SHORT_NAME in VARCHAR2,
854 X_SECURITY_GROUP in VARCHAR2,
855 X_OWNER in VARCHAR2,
856 X_START_DATE in VARCHAR2,
857 X_END_DATE in VARCHAR2,
858 X_DESCRIPTION in VARCHAR2,
859 X_LAST_UPDATE_DATE in DATE default sysdate) is
860 u_id number;
861 app_id number;
862 resp_id number;
863 sgroup_id number;
864 l_end_date varchar2(4000);
865 l_owner number;
866 rolename varchar2(4000);
867 l_user_orig_system varchar2(30);
868 l_user_orig_system_id number;
869 old_rolename varchar2(4000);
870
871 begin
872 select user_id into u_id
873 from fnd_user
874 where user_name = X_USER_NAME;
875
876 select application_id into app_id
877 from fnd_application
878 where application_short_name = X_APP_SHORT_NAME;
879
880 select responsibility_id into resp_id
881 from fnd_responsibility
882 where responsibility_key = X_RESP_KEY
883 and application_id = app_id;
884
885 select security_group_id into sgroup_id
886 from fnd_security_groups
887 where security_group_key = X_SECURITY_GROUP;
888
889 select decode(X_END_DATE,
890 fnd_load_util.null_value, null,
891 null, X_END_DATE,
892 X_END_DATE)
893 into l_end_date
894 from dual;
895
896 -- bug3649874 Modified to use fnd_load_util to get the owner_id
897
898 l_owner := fnd_load_util.owner_id(X_OWNER);
899
900 fnd_user_resp_groups_api.UPLOAD_ASSIGNMENT(
901 USER_ID => u_id,
902 RESPONSIBILITY_ID => resp_id,
903 RESPONSIBILITY_APPLICATION_ID => app_id,
904 SECURITY_GROUP_ID => sgroup_id,
905 START_DATE => to_date(X_START_DATE, 'YYYY/MM/DD'),
906 END_DATE => to_date(l_end_date, 'YYYY/MM/DD'),
907 DESCRIPTION => X_DESCRIPTION);
908
909 --------------------------------------------------------------------------
910 -- The upload_assignment routine uses fnd_global.user_id and
911 -- fnd_global.login_id which is not what we want for loader updates.
912 -- Also upload_assignment only updates created_by if the row was just
913 -- created.
914 -- Added call to PropagateUserRole to correctly set the who columns.
915 --------------------------------------------------------------------------
916 rolename := Role_Name_from_Resp_name(
917 X_RESP_KEY,
918 X_APP_SHORT_NAME,
919 X_SECURITY_GROUP);
920 --
921 -- Generate old role name for backwards compatibility.
922 --
923
924 old_rolename := 'FND_RESP'||app_id||
925 ':'||resp_id;
926
927 -- Bug3649874 propagate the who columns
928
929 wf_directory.GetRoleOrigSysInfo(x_user_name,
930 l_user_orig_system,
931 l_user_orig_system_id);
932
933 wf_local_synch.PropagateUserRole
934 (p_user_name=>x_user_name,
935 p_role_name=>rolename,
936 p_user_orig_system=>l_user_orig_system,
937 p_user_orig_system_id=>l_user_orig_system_id,
938 p_role_orig_system=>'FND_RESP',
939 p_role_orig_system_id=>resp_id,
940 p_start_date=> to_date(X_START_DATE, 'YYYY/MM/DD'),
941 p_expiration_date=>to_date(l_end_date, 'YYYY/MM/DD'),
942 p_overwrite=>TRUE,
943 p_raiseErrors=>TRUE,
944 p_parent_orig_system => 'FND_RESP',
945 p_parent_orig_system_id => resp_id,
946 p_ownerTag => X_APP_SHORT_NAME,
947 p_createdBy => l_owner,
948 p_creationDate => sysdate, /*Bug3626390 sysdate*/
949 p_lastUpdatedate=> x_last_update_date,
950 p_lastUpdatedBy => l_owner,
951 p_lastUpdateLogin => 0,
952 p_assignmentReason=>X_DESCRIPTION);
953
954
955 --
956 -- Need to propagate the old role name
957 --
958
959 wf_local_synch.PropagateUserRole
960 (p_user_name=>x_user_name,
961 p_role_name=>old_rolename,
962 p_user_orig_system=>l_user_orig_system,
963 p_user_orig_system_id=>l_user_orig_system_id,
964 p_role_orig_system=>'FND_RESP'||app_id,
965 p_role_orig_system_id=>resp_id,
966 p_start_date=> to_date(X_START_DATE, 'YYYY/MM/DD'),
967 p_expiration_date=>to_date(l_end_date, 'YYYY/MM/DD'),
968 p_overwrite=>TRUE,
969 p_raiseErrors=>TRUE,
970 p_parent_orig_system => 'FND_RESP'||app_id,
971 p_parent_orig_system_id => resp_id,
972 p_ownerTag => X_APP_SHORT_NAME,
973 p_createdBy => l_owner,
974 p_lastUpdatedBy => l_owner,
975 p_lastUpdateLogin => 0,
976 p_creationDate => sysdate, /*Bug3626390 sysdate*/
977 p_lastUpdatedate=> x_last_update_date,
978 p_assignmentReason=>X_DESCRIPTION);
979
980 end LOAD_ROW;
981
982 --
983 -- Upload_Assignment
984 -- Update user/resp/group assignment if it exists,
985 -- otherwise insert new assignment.
986 -- IN
987 -- user_id - User to get assignment
988 -- responsibility_id - Responsibility to be assigned
989 -- responsibility_application_id - Resp Application to be assigned
990 -- security_group_id - Security Group to be assigned
991 -- start_date - Start date of assignment
992 -- end_date - End date of assignment
993 -- description - Optional comment
994 -- update_who_columns- pass 'Y' or 'N' ('Y' is default if not passed)
995 -- 'N' = leave old who vals. 'Y'= update who cols to current user/date
996
997 --
998 procedure Upload_Assignment(
999 user_id in number,
1000 responsibility_id in number,
1001 responsibility_application_id in number,
1002 security_group_id in number,
1003 start_date in date,
1004 end_date in date,
1005 description in varchar2,
1006 update_who_columns in varchar2 default null
1007 /* 'N' = leave old who vals. 'Y' (default) = update who to current*/)
1008 is
1009 sgid number;
1010 begin
1011
1012 if (security_group_id is null) then
1013 sgid := fnd_global.security_group_id;
1014 else
1015 sgid := security_group_id;
1016 end if;
1017
1018 if (Fnd_User_Resp_Groups_Api.Assignment_Exists(
1019 Upload_Assignment.user_id,
1020 Upload_Assignment.responsibility_id,
1021 Upload_Assignment.responsibility_application_id,
1022 Upload_Assignment.sgid,
1023 'D'))
1024 then
1025 Fnd_User_Resp_Groups_Api.Update_Assignment(
1026 Upload_Assignment.user_id,
1027 Upload_Assignment.responsibility_id,
1028 Upload_Assignment.responsibility_application_id,
1029 Upload_Assignment.sgid,
1030 Upload_Assignment.start_date,
1031 Upload_Assignment.end_date,
1032 Upload_Assignment.description,
1033 update_who_columns);
1034 else
1035 Fnd_User_Resp_Groups_Api.Insert_Assignment(
1036 Upload_Assignment.user_id,
1037 Upload_Assignment.responsibility_id,
1038 Upload_Assignment.responsibility_application_id,
1039 Upload_Assignment.sgid,
1040 Upload_Assignment.start_date,
1041 Upload_Assignment.end_date,
1042 Upload_Assignment.description);
1043 end if;
1044 exception
1045 when others then
1046 Generic_Error('FND_USER_RESP_GROUPS_API.UPLOAD_ASSIGNMENT',
1047 sqlcode, sqlerrm);
1048 end Upload_Assignment;
1049
1050 --
1051 -- Sync_roles_one_resp_secgrp
1052 -- Sync the role for a particular resp and security group.
1053 --
1054 procedure sync_roles_one_resp_secgrp(
1055 respid in number,
1056 appid in number,
1057 respkey in varchar2,
1058 secgrpid in number,
1059 secgrpkey in varchar2,
1060 startdate in date,
1061 enddate in date)
1062 is
1063 l_respkey varchar2(30);
1064 l_secgrpkey varchar2(30);
1065 applsname varchar2(50);
1066 role_name varchar2(320);
1067 role_display_name varchar2(1000);
1068 secgrp_name varchar2(80);
1069 resp_name varchar2(100);
1070 descr varchar2(240);
1071 wf_parameters wf_parameter_list_t;
1072 old_rolename varchar2(320);
1073 -- Bug4507634 - Parameters needed to determine WF STATUS.
1074 my_exp date;
1075 my_start date;
1076 my_creationdate date;
1077 my_lastupdatedate date;
1078 my_createdby number;
1079 my_lastupdatedby number;
1080 my_lastupdatelogin number;
1081
1082 begin
1083
1084 --
1085 -- Generate old role name for backwards compatibility.
1086 --
1087
1088 old_rolename := 'FND_RESP'||appid||
1089 ':'||respid;
1090
1091 /* If caller didn't have respkey to pass, get it from respid/appid */
1092 if (respkey is null) then
1093 begin
1094 select responsibility_key
1095 into l_respkey
1096 from fnd_responsibility
1097 where responsibility_id = respid
1098 and application_id = appid;
1099 exception
1100 when no_data_found then
1101 return; /* Bad foreign key; we can't build a role. Return. */
1102 end;
1103 else
1104 l_respkey := respkey;
1105 end if;
1106
1107 /* If caller didn't have secgrpkey to pass, get it from secgrpid */
1108 if (secgrpkey is null) then
1109 begin
1110 select security_group_key
1111 into l_secgrpkey
1112 from fnd_security_groups
1113 where security_group_id = secgrpid;
1114 exception
1115 when no_data_found then
1116 return; /* Bad foreign key; we can't build a role. Return. */
1117 end;
1118 else
1119 l_secgrpkey := secgrpkey;
1120 end if;
1121
1122 /* Get the application short name for role name */
1123 begin
1124 select application_short_name
1125 into applsname
1126 from fnd_application
1127 where application_id = appid;
1128
1129 role_name := fnd_user_resp_groups_api.role_name_from_resp_name(
1130 respkey, applsname, secgrpkey);
1131
1132 exception
1133 when no_data_found then
1134 /* invalid foreign key to a nonexistant app. Skip. */
1135 role_name := null;
1136 applsname := null;
1137 return;
1138 end;
1139
1140 /* Get the responsibility name of base language for role display name */
1141 begin
1142 select responsibility_name, description
1143 into resp_name, descr
1144 from fnd_responsibility_tl
1145 where responsibility_id = respid
1146 and application_id = appid
1147 and language = (select language_code
1148 from fnd_languages
1149 where installed_flag = 'B');
1150 exception
1151 when no_data_found then
1152 /* This shouldn't normally happen, it's just for bad TL tables*/
1153 resp_name := applsname ||':'||respkey;
1154 descr := NULL;
1155 end;
1156
1157 /* Get the security group name of base language for role display name */
1158 /* Don't need the name for STANDARD, so skip the select */
1159 if (secgrpid <> 0) then
1160 begin
1161 select security_group_name
1162 into secgrp_name
1163 from fnd_security_groups_tl
1164 where security_group_id = secgrpid
1165 and language = (select language_code
1166 from fnd_languages
1167 where installed_flag = 'B');
1168 exception
1169 when no_data_found then
1170 /* This shouldn't normally happen, it's just for bad TL tables*/
1171 secgrp_name := secgrpkey;
1172 end;
1173 end if;
1174
1175 -- Bug4507634 Need to attain values for start/end date in order to
1176 -- correctly update the status in WF.
1177
1178 -- Bug4864465 Added getting the WHO column data also.
1179
1180 begin
1181 select start_date, end_date,
1182 created_by, creation_date,
1183 last_updated_by, last_update_date, last_update_login
1184 into my_start, my_exp, my_createdby, my_creationdate,
1185 my_lastupdatedby, my_lastupdatedate, my_lastupdatelogin
1186 from fnd_responsibility
1187 where responsibility_id = respid
1188 and application_id = appid;
1189 exception
1190 when no_data_found then
1191 return; /* Bad foreign key; we can't build a role. Return. */
1192 end;
1193
1194 -- Insert or update role in workflow.
1195 -- Need to do this even if role already exists, to update
1196 -- attribute values.
1197 wf_parameters := NULL;
1198 wf_event.AddParameterToList('USER_NAME',
1199 role_name , wf_parameters);
1200 if(secgrpkey = 'STANDARD') then
1201 role_display_name := resp_name;
1202 else
1203 role_display_name := resp_name||':'||secgrp_name;
1204 end if;
1205
1206 wf_event.AddParameterToList('DISPLAYNAME',
1207 role_display_name, wf_parameters);
1208 wf_event.AddParameterToList('DESCRIPTION',
1209 descr, wf_parameters);
1210 wf_event.AddParameterToList('OWNER_TAG',
1211 applsname, wf_parameters);
1212 wf_event.AddParameterToList('RAISEERRORS',
1213 'TRUE', wf_parameters);
1214
1215 -- Bug4507634 added WFSYNCH_OVERWRITE and ORCLISENABLED parameters.
1216
1217 wf_event.AddParameterToList('WFSYNCH_OVERWRITE',
1218 'TRUE', wf_parameters);
1219 if ((my_exp is null) OR
1220 (trunc(sysdate) between my_start and my_exp)) then
1221 wf_event.AddParameterToList('ORCLISENABLED', 'ACTIVE', wf_parameters);
1222 else
1223 wf_event.AddParameterToList('ORCLISENABLED', 'INACTIVE', wf_parameters);
1224 end if;
1225
1226 -- Bug4864465 Adding the WHO column values to be propagated.
1227
1228 wf_event.AddParameterToList('LAST_UPDATED_BY',my_lastupdatedby,wf_parameters);
1229
1230 -- Bug5729583 - Updated date values to use WF_CORE.canonical_date_mask.
1231
1232 wf_event.AddParameterToList('LAST_UPDATE_DATE',
1233 to_char(my_lastupdatedate,WF_CORE.canonical_date_mask),wf_parameters);
1234 wf_event.AddParameterToList('CREATED_BY',my_createdby,wf_parameters);
1235
1236 -- Bug5729583 - Updated date values to use WF_CORE.canonical_date_mask.
1237
1238 wf_event.AddParameterToList('CREATION_DATE',to_char(my_creationdate,WF_CORE.canonical_date_mask),wf_parameters);
1239 wf_event.AddParameterToList('LAST_UPDATE_LOGIN',
1240 my_lastupdatelogin,wf_parameters);
1241
1242 wf_local_synch.propagate_role(p_orig_system=>'FND_RESP',
1243 p_orig_system_id=>respid,
1244 p_attributes=> wf_parameters,
1245 p_start_date=>startdate,
1246 p_expiration_date=>enddate);
1247
1248 -- Insert or update role in workflow for old_rolename.
1249 wf_parameters := NULL;
1250 wf_event.AddParameterToList('USER_NAME',
1251 old_rolename , wf_parameters);
1252
1253 role_display_name := resp_name||':Any security group';
1254
1255 wf_event.AddParameterToList('DISPLAYNAME',
1256 role_display_name, wf_parameters);
1257 wf_event.AddParameterToList('DESCRIPTION',
1258 descr, wf_parameters);
1259 wf_event.AddParameterToList('OWNER_TAG',
1260 applsname, wf_parameters);
1261 wf_event.AddParameterToList('RAISEERRORS',
1262 'TRUE', wf_parameters);
1263
1264 -- Bug4507634 added WFSYNCH_OVERWRITE parameter.
1265
1266 wf_event.AddParameterToList('WFSYNCH_OVERWRITE',
1267 'TRUE', wf_parameters);
1268
1269 -- Bug4699363 added parameter for ORCLISENABLED.
1270
1271 if ((my_exp is null) OR
1272 (trunc(sysdate) between my_start and my_exp)) then
1273 wf_event.AddParameterToList('ORCLISENABLED', 'ACTIVE', wf_parameters);
1274 else
1275 wf_event.AddParameterToList('ORCLISENABLED', 'INACTIVE', wf_parameters);
1276 end if;
1277
1278 -- Bug4864465 Adding the WHO column values to be propagated.
1279
1280 wf_event.AddParameterToList('LAST_UPDATED_BY',my_lastupdatedby,wf_parameters);
1281
1282 -- Bug5729583 - Updated date values to use WF_CORE.canonical_date_mask.
1283
1284 wf_event.AddParameterToList('LAST_UPDATE_DATE',
1285 to_char(my_lastupdatedate,WF_CORE.canonical_date_mask), wf_parameters);
1286 wf_event.AddParameterToList('CREATED_BY',my_createdby, wf_parameters);
1287 wf_event.AddParameterToList('CREATION_DATE',to_char(my_creationdate,WF_CORE.canonical_date_mask),wf_parameters);
1288 wf_event.AddParameterToList('LAST_UPDATE_LOGIN',
1289 my_lastupdatelogin, wf_parameters);
1290
1291 wf_local_synch.propagate_role(p_orig_system=>'FND_RESP'||appid,
1292 p_orig_system_id=>respid,
1293 p_attributes=> wf_parameters,
1294 p_start_date=>startdate,
1295 p_expiration_date=>enddate);
1296
1297 end sync_roles_one_resp_secgrp;
1298
1299 --
1300 -- sync_roles_all_secgrps
1301 -- For a given resp, sync roles for all security groups
1302 -- NOTE: This is intended to be called whenever a responsibility
1303 -- is inserted or updated.
1304 --
1305
1306 procedure sync_roles_all_secgrps(
1307 respid in number,
1308 appid in number,
1309 respkey in varchar2,
1310 startdate in date,
1311 enddate in date)
1312 is
1313 cursor get_secgrp is
1314 select security_group_id,
1315 security_group_key
1316 from fnd_security_groups;
1317
1318 begin
1319
1320 if (check_secgrp_enabled(respid, appid) = 'N') then
1321 -- Security Groups not enabled for this resp,
1322 -- only create a role for the STANDARD sec grp.
1323 sync_roles_one_resp_secgrp( respid=> respid,
1324 appid=> appid,
1325 respkey=> respkey,
1326 secgrpid=> 0,
1327 secgrpkey=> 'STANDARD',
1328 startdate=> startdate,
1329 enddate=> enddate);
1330 else
1331 -- Security Groups are enabled, create one role for
1332 -- every resp/secgrp pair.
1333 for secrec in get_secgrp loop
1334 sync_roles_one_resp_secgrp(
1335 respid => respid,
1336 appid => appid,
1337 respkey => respkey,
1338 secgrpid => secrec.security_group_id,
1339 secgrpkey => secrec.security_group_key,
1340 startdate => startdate,
1341 enddate => enddate);
1342 end loop;
1343 end if;
1344
1345 end sync_roles_all_secgrps;
1346
1347 --
1348 -- sync_roles_all_resps
1349 -- For a given security group, sync roles for all responsibilities
1350 -- NOTE: This is intended to be called whenever a security group
1351 -- is inserted or updated.
1352 -- ### Security groups can be deleted, should also sync that.
1353 --
1354 procedure sync_roles_all_resps(secgrpid in varchar2,
1355 secgrpkey in varchar2) is
1356 cursor get_resp is
1357 select application_id,
1358 responsibility_id,
1359 responsibility_key,
1360 start_date,
1361 end_date
1362 from fnd_responsibility;
1363 begin
1364 for resprec in get_resp loop
1365 -- If secgrp is STANDARD, then create resp/secgrp role for all resps.
1366 -- Otherwise, only create roles for resps with security groups enabled.
1367 if ((secgrpid = 0) or
1368 (check_secgrp_enabled(resprec.responsibility_id,
1369 resprec.application_id) = 'Y'))
1370 then
1371 sync_roles_one_resp_secgrp(
1372 resprec.responsibility_id,
1373 resprec.application_id,
1374 resprec.responsibility_key,
1375 secgrpid,
1376 secgrpkey,
1377 resprec.start_date,
1378 resprec.end_date);
1379 end if;
1380 end loop;
1381
1382 end sync_roles_all_resps;
1383
1384 --
1385 -- sync_roles_all_resp_secgrps
1386 -- Create roles for all resp/security group pairs.
1387 --
1388 -- Bug4349774 - Added sync_all_flag to default to previous behavior
1389 -- where if TRUE is passed then updates and inserts are
1390 -- processed otherwise only inserts are done.
1391
1392 procedure sync_roles_all_resp_secgrps (sync_all_flag in boolean default FALSE)
1393 is
1394 cursor get_resp is
1395 select application_id,
1396 responsibility_id,
1397 responsibility_key,
1398 start_date,
1399 end_date
1400 from fnd_responsibility;
1401 begin
1402
1403 if (sync_all_flag = TRUE) then
1404 for resprec in get_resp loop
1405 sync_roles_all_secgrps(respid=> resprec.responsibility_id,
1406 appid=> resprec.application_id,
1407 respkey=> resprec.responsibility_key,
1408 startdate=> resprec.start_date,
1409 enddate=> resprec.end_date);
1410 commit;
1411 end loop;
1412 else
1413 -- Bug4322412 changed call to internal procedure so that if the role
1414 -- exists we do not waste time updating again.
1415
1416 for resprec in get_resp loop
1417 sync_roles_all_secgrps_int(respid=> resprec.responsibility_id,
1418 appid=> resprec.application_id,
1419 respkey=> resprec.responsibility_key,
1420 startdate=> resprec.start_date,
1421 enddate=> resprec.end_date);
1422 commit;
1423 end loop;
1424 end if;
1425
1426 end sync_roles_all_resp_secgrps;
1427
1428
1429 --
1430 -- Moves old data from fnd_user_resp_groups table to new workflow tables.
1431 -- This routine is exposed so that it can be called from a one time
1432 -- upgrade script, and it should never need to be run after that.
1433 -- Running it unnecessarily might invalidate work that
1434 -- admins have done to split assignments out into roles
1435 --
1436 -- Before calling this, make sure you have called
1437 -- sync_roles_all_resp_secgrps() in order to get the roles in place that
1438 -- this routine will depend on.
1439 --
1440 -- OBSOLETE: This functionality is now in the bulk sync of FND_RESP
1441 -- which is called from the affurgol.sql script.
1442 -- This code is just here in case the bulk sync fails as a last ditch
1443 -- effort this code could be called.
1444 procedure one_time_furg_to_wf_upgrade is /* THIS ROUTINE IS OBSOLETE */
1445 begin
1446 null; /* ### Stubbed ### */
1447 /*
1448 l_api_name CONSTANT VARCHAR2(30) := 'one_time_furg_to_wf_upgrade';
1449 cursor get_old_row is
1450 select fu.user_name,
1451 secgrp.security_group_key,
1452 app.application_short_name,
1453 resp.responsibility_key,
1454 resp.start_date resp_start_date,
1455 resp.end_date resp_end_date,
1456 furgo.user_id,
1457 furgo.responsibility_id,
1458 furgo.responsibility_application_id,
1459 furgo.start_date,
1460 furgo.end_date,
1461 furgo.security_group_id,
1462 furgo.created_by,
1463 furgo.creation_date,
1464 furgo.last_updated_by,
1465 furgo.last_update_date,
1466 furgo.last_update_login
1467 from fnd_user_resp_groups_old furgo,
1468 fnd_user fu,
1469 fnd_application app,
1470 fnd_responsibility resp,
1471 fnd_security_groups secgrp
1472 where furgo.user_id = fu.user_id
1473 and furgo.responsibility_id = resp.responsibility_id
1474 and furgo.responsibility_application_id
1475 = resp.application_id
1476 and furgo.responsibility_application_id
1477 = app.application_id
1478 and furgo.security_group_id = secgrp.security_group_id;
1479 resp_name varchar2(100);
1480 descr varchar2(240);
1481 secgrp_name varchar2(80);
1482 dummy varchar2(255);
1483 wf_parameters wf_parameter_list_t;
1484 l_user_orig_system varchar2(30);
1485 l_user_orig_system_id number;
1486 rolename varchar2(1000);
1487 resp_key varchar2(100);
1488 begin
1489 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1490 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1491 c_log_head || l_api_name || '.begin',
1492 c_pkg_name || '.' ||l_api_name);
1493 end if;
1494
1495 for rowrec in get_old_row loop
1496
1497 resp_key := rowrec.responsibility_key;
1498
1499 rolename := role_name_from_resp_name( resp_key,
1500 rowrec.application_short_name,
1501 rowrec.security_group_key);
1502
1503 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1504 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1505 c_log_head || l_api_name || '.processing',
1506 'Processing role:'|| rolename ||' for user_id:'||
1507 rowrec.user_id ||' user_name:'||rowrec.user_name);
1508 end if;
1509 begin
1510 select name
1511 into dummy
1512 from wf_local_roles partition (FND_RESP)
1513 where name = rolename
1514 and rownum = 1;
1515 exception -- This shouldnt be necessary since the roles should already
1516 when no_data_found then -- have been created, but be safe.
1517 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1518 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1519 c_log_head || l_api_name || '.need_to_create_role',
1520 'Creating role:'|| rolename);
1521 end if;
1522 fnd_user_resp_groups_api.sync_roles_one_resp_secgrp(
1523 rowrec.responsibility_id,
1524 rowrec.responsibility_application_id,
1525 rowrec.responsibility_key,
1526 rowrec.security_group_id,
1527 rowrec.security_group_key,
1528 rowrec.resp_start_date,
1529 rowrec.resp_end_date);
1530 end;
1531
1532 begin
1533 select role_name
1534 into dummy
1535 from wf_all_user_roles waur
1536 where waur.role_name = rolename
1537 and waur.user_name = rowrec.user_name
1538 and ( (waur.start_date = rowrec.start_date)
1539 OR((waur.start_date is NULL) AND (rowrec.start_date is NULL)))
1540 and ( (waur.expiration_date = rowrec.end_date)
1541 OR((waur.expiration_date is NULL) AND(rowrec.end_date is NULL)));
1542 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1543 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1544 c_log_head || l_api_name || '.ur_exists',
1545 'USER_ROLE FOUND. Not inserting.');
1546 end if;
1547 exception
1548 when no_data_found then
1549 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
1550 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1551 c_log_head || l_api_name || '.ur_notfound',
1552 'USER_ROLE Not FOUND. Need to insert');
1553 end if;
1554 -- We cant just assume that the orig system is FND_USR. It could
1555 -- be PER because the row in wf_users/wf_roles is one or the other
1556 wf_directory.GetRoleOrigSysInfo(rowrec.user_name,
1557 l_user_orig_system,
1558 l_user_orig_system_id);
1559 -- In case there is no WF user, sync this user up so there is one.
1560 -- Should never happen but be safe in case sync wasnt perfect in past
1561 if( (l_user_orig_system is NULL)
1562 and (l_user_orig_system_id is NULL)) then
1563 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
1564 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1565 c_log_head || l_api_name || '.orig_notfound',
1566 'Orig system and id not found. Trying to sync user:'||
1567 rowrec.user_name);
1568 end if;
1569 fnd_user_pkg.user_synch(rowrec.user_name);
1570 wf_directory.GetRoleOrigSysInfo(rowrec.user_name,
1571 l_user_orig_system,
1572 l_user_orig_system_id);
1573 end if;
1574 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1575 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1576 c_log_head || l_api_name || '.got_orig',
1577 'Looked up orig: l_user_orig_system:'||
1578 l_user_orig_system ||' l_user_orig_system_id:'||
1579 l_user_orig_system_id);
1580 end if;
1581 end;
1582
1583 -- Sync the User/Role (but not if there is no wf_user)
1584 if( (l_user_orig_system is not NULL)
1585 or (l_user_orig_system_id is not NULL)) then
1586 begin
1587 wf_local_synch.PropagateUserRole
1588 (p_user_name=>rowrec.user_name,
1589 p_role_name=>rolename,
1590 p_user_orig_system=>l_user_orig_system,
1591 p_user_orig_system_id=>l_user_orig_system_id,
1592 p_role_orig_system=>'FND_RESP',
1593 p_role_orig_system_id=>rowrec.responsibility_id,
1594 p_start_date=>rowrec.start_date,
1595 p_expiration_date=>rowrec.end_date,
1596 p_overwrite=>TRUE,
1597 p_raiseErrors=>TRUE,
1598 p_parent_orig_system => 'FND_RESP',
1599 p_parent_orig_system_id =>rowrec.responsibility_id,
1600 p_ownerTag => rowrec.application_short_name,
1601 p_createdBy => fnd_global.user_id,
1602 p_lastUpdatedBy => fnd_global.user_id,
1603 p_lastUpdateLogin => 0,
1604 p_creationDate => sysdate,
1605 p_lastUpdatedate=> sysdate);
1606
1607 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1608 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1609 c_log_head || l_api_name || '.called_prop',
1610 'Successfully called wf_local_synch.PropagateUserRole');
1611 end if;
1612 exception when others then
1613 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
1614 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1615 c_log_head || l_api_name || '.propagate_fail',
1616 'PropogateUserRole call failed with exception for user:'||
1617 rowrec.user_name || ' role:'||rolename ||
1618 ' sql code:'||sqlcode ||
1619 ' sql errm:'||sqlerrm);
1620 end if;
1621 end;
1622 else
1623 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
1624 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1625 c_log_head || l_api_name || '.orig_notfound',
1626 'Did not propogate role because Orig System not found for user:'||
1627 rowrec.user_name);
1628 end if;
1629 end if;
1630
1631 commit;
1632
1633 end loop;
1634
1635 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1636 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1637 c_log_head || l_api_name || '.end',
1638 c_pkg_name || '.' ||l_api_name );
1639 end if;
1640 */
1641 end one_time_furg_to_wf_upgrade;
1642
1643 -- sync_roles_all_secgrps_int
1644 --
1645 -- Bug4322412
1646 -- For a given resp, sync roles for all security groups if the role
1647 -- does not already exist.
1648 --
1649 -- NOTE:This routine does not update existing roles. To update existing roles
1650 -- the routine sync_roles_all_secgrps should be used.
1651 --
1652 procedure sync_roles_all_secgrps_int(
1653 respid in number,
1654 appid in number,
1655 respkey in varchar2,
1656 startdate in date,
1657 enddate in date)
1658 is
1659 cursor get_secgrp is
1660 select security_group_id,
1661 security_group_key
1662 from fnd_security_groups;
1663
1664 rolename varchar2(320);
1665 dummy number;
1666
1667 begin
1668
1669 if (check_secgrp_enabled(respid, appid) = 'N') then
1670 -- Security Groups not enabled for this resp,
1671 -- only create a role for the STANDARD sec grp.
1672 sync_roles_one_resp_secgrp( respid=> respid,
1673 appid=> appid,
1674 respkey=> respkey,
1675 secgrpid=> 0,
1676 secgrpkey=> 'STANDARD',
1677 startdate=> startdate,
1678 enddate=> enddate);
1679 else
1680 -- Security Groups are enabled, create one role for
1681 -- every resp/secgrp pair.
1682
1683 for secrec in get_secgrp loop
1684
1685 begin
1686 rolename := role_name_from_resp(respid, appid,
1687 secrec.security_group_id);
1688 select null
1689 into dummy
1690 from wf_local_roles
1691 where name = rolename
1692 and partition_id = 2
1693 and rownum = 1;
1694
1695 exception
1696 when no_data_found then
1697 sync_roles_one_resp_secgrp(
1698 respid => respid,
1699 appid => appid,
1700 respkey => respkey,
1701 secgrpid => secrec.security_group_id,
1702 secgrpkey => secrec.security_group_key,
1703 startdate => startdate,
1704 enddate => enddate);
1705 end;
1706 end loop;
1707 end if;
1708
1709 end sync_roles_all_secgrps_int;
1710
1711
1712 end Fnd_User_Resp_Groups_Api;
[Home] [Help]