1 package body Fnd_User_Resp_Groups_api as
2 /* $Header: AFSCURGB.pls 120.14.12000000.3 2007/02/12 16:36:58 jvalenti 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 direct_flag = 'D'
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 direct_flag = 'I'
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 end if;
751
752
753 -- Bug4747169 - Removed passing in the parameters p_created_by and
754 -- p_creation_date as this is the update processing.
755
756 wf_local_synch.PropagateUserRole
757 (p_user_name=>username,
758 p_role_name=>rolename,
759 p_user_orig_system=>l_user_orig_system,
760 p_user_orig_system_id=>l_user_orig_system_id,
761 p_role_orig_system=>'FND_RESP',
762 p_role_orig_system_id=>responsibility_id,
763 p_start_date=>start_date,
764 p_expiration_date=>end_date,
765 p_overwrite=>TRUE,
766 p_raiseErrors=>TRUE,
767 p_parent_orig_system => 'FND_RESP',
768 p_parent_orig_system_id => responsibility_id,
769 p_ownerTag => appsname,
770 p_createdBy => l_created_by,
771 p_creationDate => l_creation_date, /*Bug3626390 sysdate*/
772 p_lastUpdatedate=> l_last_update_date,
773 p_lastUpdatedBy => l_last_updated_by,
774 p_lastUpdateLogin => l_last_update_login,
775 p_assignmentReason=>description,
776 p_updatewho => l_update_who); -- Bug5467610
777
778 --
779 -- Need to propagate the old role name
780 --
781
782 wf_local_synch.PropagateUserRole
783 (p_user_name=>username,
784 p_role_name=>old_rolename,
785 p_user_orig_system=>l_user_orig_system,
786 p_user_orig_system_id=>l_user_orig_system_id,
787 p_role_orig_system=>'FND_RESP'||responsibility_application_id,
788 p_role_orig_system_id=>responsibility_id,
789 p_start_date=>start_date,
790 p_expiration_date=>end_date,
791 p_overwrite=>TRUE,
792 p_raiseErrors=>TRUE,
793 p_parent_orig_system => 'FND_RESP'||responsibility_application_id,
794 p_parent_orig_system_id => responsibility_id,
795 p_ownerTag => appsname,
796 p_createdBy => l_created_by,
797 p_creationDate => l_creation_date, /*Bug3626390 sysdate*/
798 p_lastUpdatedate=> l_last_update_date,
799 p_lastUpdatedBy => l_last_updated_by,
800 p_lastUpdateLogin => l_last_update_login,
801 p_assignmentReason=>description,
802 p_updatewho => l_update_who); -- Bug5467610
803
804
805 wf_event.raise('oracle.apps.fnd.security.user.assignment.change',
806 Update_Assignment.user_id||':'||
807 Update_Assignment.responsibility_id,
808 null, null);
809 --Raise the invalidation event attached to the USER_INFO_CACHE
810 --(ideally this should be done by the wf API once the propagation
811 --has been sucessful) / or if they are not doing it then we should attach
812 --our business event to the CACHE as we do not really know what
813 --subscriptions are attcahed to the wf event.
814 --o.k putting our event (but we need to change the b3664848.ldt
815 --and the event-subscription file to attach this event to the
816 --bes control group.
817 wf_event.addparametertolist(p_name => 'FND_USER_ID',
818 p_value => Update_Assignment.user_id,
819 p_parameterlist => l_parameters);
820
821 wf_event.addparametertolist(p_name => 'FND_RESPONSIBILITY_ID',
822 p_value => Update_Assignment.responsibility_id,
823 p_parameterlist => l_parameters);
824
825 wf_event.addparametertolist(p_name => 'FND_APPS_SHORT_NAME',
826 p_value => appsname,
827 p_parameterlist => l_parameters);
828
829 wf_event.addparametertolist(p_name => 'FND_RESPONSIBILITY_APPS_ID',
830 p_value => Update_Assignment.responsibility_application_id,
831 p_parameterlist => l_parameters);
832
833 wf_event.raise(p_event_name => 'oracle.apps.fnd.user.role.update',
834 p_event_key => to_char(Update_Assignment.user_id)||':'||to_char(Update_Assignment.responsibility_id)||':'||appsname||':'||to_char(Update_Assignment.responsibility_application_id),
835 p_event_data => NULL,
836 p_parameters => l_parameters,
837 p_send_date => Sysdate);
838
839 exception
840 when others then
841 Generic_Error('FND_USER_RESP_GROUPS_API.UPDATE_ASSIGNMENT',
842 sqlcode, sqlerrm);
843
844 end Update_Assignment;
845
846 procedure LOAD_ROW (
847 X_USER_NAME in VARCHAR2,
848 X_RESP_KEY in VARCHAR2,
849 X_APP_SHORT_NAME in VARCHAR2,
850 X_SECURITY_GROUP in VARCHAR2,
851 X_OWNER in VARCHAR2,
852 X_START_DATE in VARCHAR2,
853 X_END_DATE in VARCHAR2,
854 X_DESCRIPTION in VARCHAR2,
855 X_LAST_UPDATE_DATE in DATE default sysdate) is
856 u_id number;
857 app_id number;
858 resp_id number;
859 sgroup_id number;
860 l_end_date varchar2(4000);
861 l_owner number;
862 rolename varchar2(4000);
863 l_user_orig_system varchar2(30);
864 l_user_orig_system_id number;
865 old_rolename varchar2(4000);
866
867 begin
868 select user_id into u_id
869 from fnd_user
870 where user_name = X_USER_NAME;
871
872 select application_id into app_id
873 from fnd_application
874 where application_short_name = X_APP_SHORT_NAME;
875
876 select responsibility_id into resp_id
877 from fnd_responsibility
878 where responsibility_key = X_RESP_KEY
879 and application_id = app_id;
880
881 select security_group_id into sgroup_id
882 from fnd_security_groups
883 where security_group_key = X_SECURITY_GROUP;
884
885 select decode(X_END_DATE,
886 fnd_load_util.null_value, null,
887 null, X_END_DATE,
888 X_END_DATE)
889 into l_end_date
890 from dual;
891
892 -- bug3649874 Modified to use fnd_load_util to get the owner_id
893
894 l_owner := fnd_load_util.owner_id(X_OWNER);
895
896 fnd_user_resp_groups_api.UPLOAD_ASSIGNMENT(
897 USER_ID => u_id,
898 RESPONSIBILITY_ID => resp_id,
899 RESPONSIBILITY_APPLICATION_ID => app_id,
900 SECURITY_GROUP_ID => sgroup_id,
901 START_DATE => to_date(X_START_DATE, 'YYYY/MM/DD'),
902 END_DATE => to_date(l_end_date, 'YYYY/MM/DD'),
903 DESCRIPTION => X_DESCRIPTION);
904
905 --------------------------------------------------------------------------
906 -- The upload_assignment routine uses fnd_global.user_id and
907 -- fnd_global.login_id which is not what we want for loader updates.
908 -- Also upload_assignment only updates created_by if the row was just
909 -- created.
910 -- Added call to PropagateUserRole to correctly set the who columns.
911 --------------------------------------------------------------------------
912 rolename := Role_Name_from_Resp_name(
913 X_RESP_KEY,
914 X_APP_SHORT_NAME,
915 X_SECURITY_GROUP);
916 --
917 -- Generate old role name for backwards compatibility.
918 --
919
920 old_rolename := 'FND_RESP'||app_id||
921 ':'||resp_id;
922
923 -- Bug3649874 propagate the who columns
924
925 wf_directory.GetRoleOrigSysInfo(x_user_name,
926 l_user_orig_system,
927 l_user_orig_system_id);
928
929 wf_local_synch.PropagateUserRole
930 (p_user_name=>x_user_name,
931 p_role_name=>rolename,
932 p_user_orig_system=>l_user_orig_system,
933 p_user_orig_system_id=>l_user_orig_system_id,
934 p_role_orig_system=>'FND_RESP',
935 p_role_orig_system_id=>resp_id,
936 p_start_date=> to_date(X_START_DATE, 'YYYY/MM/DD'),
937 p_expiration_date=>to_date(l_end_date, 'YYYY/MM/DD'),
938 p_overwrite=>TRUE,
939 p_raiseErrors=>TRUE,
940 p_parent_orig_system => 'FND_RESP',
941 p_parent_orig_system_id => resp_id,
942 p_ownerTag => X_APP_SHORT_NAME,
943 p_createdBy => l_owner,
944 p_creationDate => sysdate, /*Bug3626390 sysdate*/
945 p_lastUpdatedate=> x_last_update_date,
946 p_lastUpdatedBy => l_owner,
947 p_lastUpdateLogin => 0,
948 p_assignmentReason=>X_DESCRIPTION);
949
950
951 --
952 -- Need to propagate the old role name
953 --
954
955 wf_local_synch.PropagateUserRole
956 (p_user_name=>x_user_name,
957 p_role_name=>old_rolename,
958 p_user_orig_system=>l_user_orig_system,
959 p_user_orig_system_id=>l_user_orig_system_id,
960 p_role_orig_system=>'FND_RESP'||app_id,
961 p_role_orig_system_id=>resp_id,
962 p_start_date=> to_date(X_START_DATE, 'YYYY/MM/DD'),
963 p_expiration_date=>to_date(l_end_date, 'YYYY/MM/DD'),
964 p_overwrite=>TRUE,
965 p_raiseErrors=>TRUE,
966 p_parent_orig_system => 'FND_RESP'||app_id,
967 p_parent_orig_system_id => resp_id,
968 p_ownerTag => X_APP_SHORT_NAME,
969 p_createdBy => l_owner,
970 p_lastUpdatedBy => l_owner,
971 p_lastUpdateLogin => 0,
972 p_creationDate => sysdate, /*Bug3626390 sysdate*/
973 p_lastUpdatedate=> x_last_update_date,
974 p_assignmentReason=>X_DESCRIPTION);
975
976 end LOAD_ROW;
977
978 --
979 -- Upload_Assignment
980 -- Update user/resp/group assignment if it exists,
981 -- otherwise insert new assignment.
982 -- IN
983 -- user_id - User to get assignment
984 -- responsibility_id - Responsibility to be assigned
985 -- responsibility_application_id - Resp Application to be assigned
986 -- security_group_id - Security Group to be assigned
987 -- start_date - Start date of assignment
988 -- end_date - End date of assignment
989 -- description - Optional comment
990 -- update_who_columns- pass 'Y' or 'N' ('Y' is default if not passed)
991 -- 'N' = leave old who vals. 'Y'= update who cols to current user/date
992
993 --
994 procedure Upload_Assignment(
995 user_id in number,
996 responsibility_id in number,
997 responsibility_application_id in number,
998 security_group_id in number,
999 start_date in date,
1000 end_date in date,
1001 description in varchar2,
1002 update_who_columns in varchar2 default null
1003 /* 'N' = leave old who vals. 'Y' (default) = update who to current*/)
1004 is
1005 sgid number;
1006 begin
1007
1008 if (security_group_id is null) then
1009 sgid := fnd_global.security_group_id;
1010 else
1011 sgid := security_group_id;
1012 end if;
1013
1014 if (Fnd_User_Resp_Groups_Api.Assignment_Exists(
1015 Upload_Assignment.user_id,
1016 Upload_Assignment.responsibility_id,
1017 Upload_Assignment.responsibility_application_id,
1018 Upload_Assignment.sgid,
1019 'D'))
1020 then
1021 Fnd_User_Resp_Groups_Api.Update_Assignment(
1022 Upload_Assignment.user_id,
1023 Upload_Assignment.responsibility_id,
1024 Upload_Assignment.responsibility_application_id,
1025 Upload_Assignment.sgid,
1026 Upload_Assignment.start_date,
1027 Upload_Assignment.end_date,
1028 Upload_Assignment.description,
1029 update_who_columns);
1030 else
1031 Fnd_User_Resp_Groups_Api.Insert_Assignment(
1032 Upload_Assignment.user_id,
1033 Upload_Assignment.responsibility_id,
1034 Upload_Assignment.responsibility_application_id,
1035 Upload_Assignment.sgid,
1036 Upload_Assignment.start_date,
1037 Upload_Assignment.end_date,
1038 Upload_Assignment.description);
1039 end if;
1040 exception
1041 when others then
1042 Generic_Error('FND_USER_RESP_GROUPS_API.UPLOAD_ASSIGNMENT',
1043 sqlcode, sqlerrm);
1044 end Upload_Assignment;
1045
1046 --
1047 -- Sync_roles_one_resp_secgrp
1048 -- Sync the role for a particular resp and security group.
1049 --
1050 procedure sync_roles_one_resp_secgrp(
1051 respid in number,
1052 appid in number,
1053 respkey in varchar2,
1054 secgrpid in number,
1055 secgrpkey in varchar2,
1056 startdate in date,
1057 enddate in date)
1058 is
1059 l_respkey varchar2(30);
1060 l_secgrpkey varchar2(30);
1061 applsname varchar2(50);
1062 role_name varchar2(320);
1063 role_display_name varchar2(1000);
1064 secgrp_name varchar2(80);
1065 resp_name varchar2(100);
1066 descr varchar2(240);
1067 wf_parameters wf_parameter_list_t;
1068 old_rolename varchar2(320);
1069 -- Bug4507634 - Parameters needed to determine WF STATUS.
1070 my_exp date;
1071 my_start date;
1072 my_creationdate date;
1073 my_lastupdatedate date;
1074 my_createdby number;
1075 my_lastupdatedby number;
1076 my_lastupdatelogin number;
1077
1078 begin
1079
1080 --
1081 -- Generate old role name for backwards compatibility.
1082 --
1083
1084 old_rolename := 'FND_RESP'||appid||
1085 ':'||respid;
1086
1087 /* If caller didn't have respkey to pass, get it from respid/appid */
1088 if (respkey is null) then
1089 begin
1090 select responsibility_key
1091 into l_respkey
1092 from fnd_responsibility
1093 where responsibility_id = respid
1094 and application_id = appid;
1095 exception
1096 when no_data_found then
1097 return; /* Bad foreign key; we can't build a role. Return. */
1098 end;
1099 else
1100 l_respkey := respkey;
1101 end if;
1102
1103 /* If caller didn't have secgrpkey to pass, get it from secgrpid */
1104 if (secgrpkey is null) then
1105 begin
1106 select security_group_key
1107 into l_secgrpkey
1108 from fnd_security_groups
1109 where security_group_id = secgrpid;
1110 exception
1111 when no_data_found then
1112 return; /* Bad foreign key; we can't build a role. Return. */
1113 end;
1114 else
1115 l_secgrpkey := secgrpkey;
1116 end if;
1117
1118 /* Get the application short name for role name */
1119 begin
1120 select application_short_name
1121 into applsname
1122 from fnd_application
1123 where application_id = appid;
1124
1125 role_name := fnd_user_resp_groups_api.role_name_from_resp_name(
1126 respkey, applsname, secgrpkey);
1127
1128 exception
1129 when no_data_found then
1130 /* invalid foreign key to a nonexistant app. Skip. */
1131 role_name := null;
1132 applsname := null;
1133 return;
1134 end;
1135
1136 /* Get the responsibility name of base language for role display name */
1137 begin
1138 select responsibility_name, description
1139 into resp_name, descr
1140 from fnd_responsibility_tl
1141 where responsibility_id = respid
1142 and application_id = appid
1143 and language = (select language_code
1144 from fnd_languages
1145 where installed_flag = 'B');
1146 exception
1147 when no_data_found then
1148 /* This shouldn't normally happen, it's just for bad TL tables*/
1149 resp_name := applsname ||':'||respkey;
1150 descr := NULL;
1151 end;
1152
1153 /* Get the security group name of base language for role display name */
1154 /* Don't need the name for STANDARD, so skip the select */
1155 if (secgrpid <> 0) then
1156 begin
1157 select security_group_name
1158 into secgrp_name
1159 from fnd_security_groups_tl
1160 where security_group_id = secgrpid
1161 and language = (select language_code
1162 from fnd_languages
1163 where installed_flag = 'B');
1164 exception
1165 when no_data_found then
1166 /* This shouldn't normally happen, it's just for bad TL tables*/
1167 secgrp_name := secgrpkey;
1168 end;
1169 end if;
1170
1171 -- Bug4507634 Need to attain values for start/end date in order to
1172 -- correctly update the status in WF.
1173
1174 -- Bug4864465 Added getting the WHO column data also.
1175
1176 begin
1177 select start_date, end_date,
1178 created_by, creation_date,
1179 last_updated_by, last_update_date, last_update_login
1180 into my_start, my_exp, my_createdby, my_creationdate,
1181 my_lastupdatedby, my_lastupdatedate, my_lastupdatelogin
1182 from fnd_responsibility
1183 where responsibility_id = respid
1184 and application_id = appid;
1185 exception
1186 when no_data_found then
1187 return; /* Bad foreign key; we can't build a role. Return. */
1188 end;
1189
1190 -- Insert or update role in workflow.
1191 -- Need to do this even if role already exists, to update
1192 -- attribute values.
1193 wf_parameters := NULL;
1194 wf_event.AddParameterToList('USER_NAME',
1195 role_name , wf_parameters);
1196 if(secgrpkey = 'STANDARD') then
1197 role_display_name := resp_name;
1198 else
1199 role_display_name := resp_name||':'||secgrp_name;
1200 end if;
1201
1202 wf_event.AddParameterToList('DISPLAYNAME',
1203 role_display_name, wf_parameters);
1204 wf_event.AddParameterToList('DESCRIPTION',
1205 descr, wf_parameters);
1206 wf_event.AddParameterToList('OWNER_TAG',
1207 applsname, wf_parameters);
1208 wf_event.AddParameterToList('RAISEERRORS',
1209 'TRUE', wf_parameters);
1210
1211 -- Bug4507634 added WFSYNCH_OVERWRITE and ORCLISENABLED parameters.
1212
1213 wf_event.AddParameterToList('WFSYNCH_OVERWRITE',
1214 'TRUE', wf_parameters);
1215 if ((my_exp is null) OR
1216 (trunc(sysdate) between my_start and my_exp)) then
1217 wf_event.AddParameterToList('ORCLISENABLED', 'ACTIVE', wf_parameters);
1218 else
1219 wf_event.AddParameterToList('ORCLISENABLED', 'INACTIVE', wf_parameters);
1220 end if;
1221
1222 -- Bug4864465 Adding the WHO column values to be propagated.
1223
1224 wf_event.AddParameterToList('LAST_UPDATED_BY',my_lastupdatedby,wf_parameters);
1225
1226 -- Bug5729583 - Updated date values to use WF_CORE.canonical_date_mask.
1227
1228 wf_event.AddParameterToList('LAST_UPDATE_DATE',
1229 to_char(my_lastupdatedate,WF_CORE.canonical_date_mask),wf_parameters);
1230 wf_event.AddParameterToList('CREATED_BY',my_createdby,wf_parameters);
1231
1232 -- Bug5729583 - Updated date values to use WF_CORE.canonical_date_mask.
1233
1234 wf_event.AddParameterToList('CREATION_DATE',to_char(my_creationdate,WF_CORE.canonical_date_mask),wf_parameters);
1235 wf_event.AddParameterToList('LAST_UPDATE_LOGIN',
1236 my_lastupdatelogin,wf_parameters);
1237
1238 wf_local_synch.propagate_role(p_orig_system=>'FND_RESP',
1239 p_orig_system_id=>respid,
1240 p_attributes=> wf_parameters,
1241 p_start_date=>startdate,
1242 p_expiration_date=>enddate);
1243
1244 -- Insert or update role in workflow for old_rolename.
1245 wf_parameters := NULL;
1246 wf_event.AddParameterToList('USER_NAME',
1247 old_rolename , wf_parameters);
1248
1249 role_display_name := resp_name||':Any security group';
1250
1251 wf_event.AddParameterToList('DISPLAYNAME',
1252 role_display_name, wf_parameters);
1253 wf_event.AddParameterToList('DESCRIPTION',
1254 descr, wf_parameters);
1255 wf_event.AddParameterToList('OWNER_TAG',
1256 applsname, wf_parameters);
1257 wf_event.AddParameterToList('RAISEERRORS',
1258 'TRUE', wf_parameters);
1259
1260 -- Bug4507634 added WFSYNCH_OVERWRITE parameter.
1261
1262 wf_event.AddParameterToList('WFSYNCH_OVERWRITE',
1263 'TRUE', wf_parameters);
1264
1265 -- Bug4699363 added parameter for ORCLISENABLED.
1266
1267 if ((my_exp is null) OR
1268 (trunc(sysdate) between my_start and my_exp)) then
1269 wf_event.AddParameterToList('ORCLISENABLED', 'ACTIVE', wf_parameters);
1270 else
1271 wf_event.AddParameterToList('ORCLISENABLED', 'INACTIVE', wf_parameters);
1272 end if;
1273
1274 -- Bug4864465 Adding the WHO column values to be propagated.
1275
1276 wf_event.AddParameterToList('LAST_UPDATED_BY',my_lastupdatedby,wf_parameters);
1277
1278 -- Bug5729583 - Updated date values to use WF_CORE.canonical_date_mask.
1279
1280 wf_event.AddParameterToList('LAST_UPDATE_DATE',
1281 to_char(my_lastupdatedate,WF_CORE.canonical_date_mask), wf_parameters);
1282 wf_event.AddParameterToList('CREATED_BY',my_createdby, wf_parameters);
1283 wf_event.AddParameterToList('CREATION_DATE',to_char(my_creationdate,WF_CORE.canonical_date_mask),wf_parameters);
1284 wf_event.AddParameterToList('LAST_UPDATE_LOGIN',
1285 my_lastupdatelogin, wf_parameters);
1286
1287 wf_local_synch.propagate_role(p_orig_system=>'FND_RESP'||appid,
1288 p_orig_system_id=>respid,
1289 p_attributes=> wf_parameters,
1290 p_start_date=>startdate,
1291 p_expiration_date=>enddate);
1292
1293 end sync_roles_one_resp_secgrp;
1294
1295 --
1296 -- sync_roles_all_secgrps
1297 -- For a given resp, sync roles for all security groups
1298 -- NOTE: This is intended to be called whenever a responsibility
1299 -- is inserted or updated.
1300 --
1301
1302 procedure sync_roles_all_secgrps(
1303 respid in number,
1304 appid in number,
1305 respkey in varchar2,
1306 startdate in date,
1307 enddate in date)
1308 is
1309 cursor get_secgrp is
1310 select security_group_id,
1311 security_group_key
1312 from fnd_security_groups;
1313
1314 begin
1315
1316 if (check_secgrp_enabled(respid, appid) = 'N') then
1317 -- Security Groups not enabled for this resp,
1318 -- only create a role for the STANDARD sec grp.
1319 sync_roles_one_resp_secgrp( respid=> respid,
1320 appid=> appid,
1321 respkey=> respkey,
1322 secgrpid=> 0,
1323 secgrpkey=> 'STANDARD',
1324 startdate=> startdate,
1325 enddate=> enddate);
1326 else
1327 -- Security Groups are enabled, create one role for
1328 -- every resp/secgrp pair.
1329 for secrec in get_secgrp loop
1330 sync_roles_one_resp_secgrp(
1331 respid => respid,
1332 appid => appid,
1333 respkey => respkey,
1334 secgrpid => secrec.security_group_id,
1335 secgrpkey => secrec.security_group_key,
1336 startdate => startdate,
1337 enddate => enddate);
1338 end loop;
1339 end if;
1340
1341 end sync_roles_all_secgrps;
1342
1343 --
1344 -- sync_roles_all_resps
1345 -- For a given security group, sync roles for all responsibilities
1346 -- NOTE: This is intended to be called whenever a security group
1347 -- is inserted or updated.
1348 -- ### Security groups can be deleted, should also sync that.
1349 --
1350 procedure sync_roles_all_resps(secgrpid in varchar2,
1351 secgrpkey in varchar2) is
1352 cursor get_resp is
1353 select application_id,
1354 responsibility_id,
1355 responsibility_key,
1356 start_date,
1357 end_date
1358 from fnd_responsibility;
1359 begin
1360 for resprec in get_resp loop
1361 -- If secgrp is STANDARD, then create resp/secgrp role for all resps.
1362 -- Otherwise, only create roles for resps with security groups enabled.
1363 if ((secgrpid = 0) or
1364 (check_secgrp_enabled(resprec.responsibility_id,
1365 resprec.application_id) = 'Y'))
1366 then
1367 sync_roles_one_resp_secgrp(
1368 resprec.responsibility_id,
1369 resprec.application_id,
1370 resprec.responsibility_key,
1371 secgrpid,
1372 secgrpkey,
1373 resprec.start_date,
1374 resprec.end_date);
1375 end if;
1376 end loop;
1377
1378 end sync_roles_all_resps;
1379
1380 --
1381 -- sync_roles_all_resp_secgrps
1382 -- Create roles for all resp/security group pairs.
1383 --
1384 -- Bug4349774 - Added sync_all_flag to default to previous behavior
1385 -- where if TRUE is passed then updates and inserts are
1386 -- processed otherwise only inserts are done.
1387
1388 procedure sync_roles_all_resp_secgrps (sync_all_flag in boolean default FALSE)
1389 is
1390 cursor get_resp is
1391 select application_id,
1392 responsibility_id,
1393 responsibility_key,
1394 start_date,
1395 end_date
1396 from fnd_responsibility;
1397 begin
1398
1399 if (sync_all_flag = TRUE) then
1400 for resprec in get_resp loop
1401 sync_roles_all_secgrps(respid=> resprec.responsibility_id,
1402 appid=> resprec.application_id,
1403 respkey=> resprec.responsibility_key,
1404 startdate=> resprec.start_date,
1405 enddate=> resprec.end_date);
1406 commit;
1407 end loop;
1408 else
1409 -- Bug4322412 changed call to internal procedure so that if the role
1410 -- exists we do not waste time updating again.
1411
1412 for resprec in get_resp loop
1413 sync_roles_all_secgrps_int(respid=> resprec.responsibility_id,
1414 appid=> resprec.application_id,
1415 respkey=> resprec.responsibility_key,
1416 startdate=> resprec.start_date,
1417 enddate=> resprec.end_date);
1418 commit;
1419 end loop;
1420 end if;
1421
1422 end sync_roles_all_resp_secgrps;
1423
1424
1425 --
1426 -- Moves old data from fnd_user_resp_groups table to new workflow tables.
1427 -- This routine is exposed so that it can be called from a one time
1428 -- upgrade script, and it should never need to be run after that.
1429 -- Running it unnecessarily might invalidate work that
1430 -- admins have done to split assignments out into roles
1431 --
1432 -- Before calling this, make sure you have called
1433 -- sync_roles_all_resp_secgrps() in order to get the roles in place that
1434 -- this routine will depend on.
1435 --
1436 -- OBSOLETE: This functionality is now in the bulk sync of FND_RESP
1437 -- which is called from the affurgol.sql script.
1438 -- This code is just here in case the bulk sync fails as a last ditch
1439 -- effort this code could be called.
1440 procedure one_time_furg_to_wf_upgrade is /* THIS ROUTINE IS OBSOLETE */
1441 begin
1442 null; /* ### Stubbed ### */
1443 /*
1444 l_api_name CONSTANT VARCHAR2(30) := 'one_time_furg_to_wf_upgrade';
1445 cursor get_old_row is
1446 select fu.user_name,
1447 secgrp.security_group_key,
1448 app.application_short_name,
1449 resp.responsibility_key,
1450 resp.start_date resp_start_date,
1451 resp.end_date resp_end_date,
1452 furgo.user_id,
1453 furgo.responsibility_id,
1454 furgo.responsibility_application_id,
1455 furgo.start_date,
1456 furgo.end_date,
1457 furgo.security_group_id,
1458 furgo.created_by,
1459 furgo.creation_date,
1460 furgo.last_updated_by,
1461 furgo.last_update_date,
1462 furgo.last_update_login
1463 from fnd_user_resp_groups_old furgo,
1464 fnd_user fu,
1465 fnd_application app,
1466 fnd_responsibility resp,
1467 fnd_security_groups secgrp
1468 where furgo.user_id = fu.user_id
1469 and furgo.responsibility_id = resp.responsibility_id
1470 and furgo.responsibility_application_id
1471 = resp.application_id
1472 and furgo.responsibility_application_id
1473 = app.application_id
1474 and furgo.security_group_id = secgrp.security_group_id;
1475 resp_name varchar2(100);
1476 descr varchar2(240);
1477 secgrp_name varchar2(80);
1478 dummy varchar2(255);
1479 wf_parameters wf_parameter_list_t;
1480 l_user_orig_system varchar2(30);
1481 l_user_orig_system_id number;
1482 rolename varchar2(1000);
1483 resp_key varchar2(100);
1484 begin
1485 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1486 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1487 c_log_head || l_api_name || '.begin',
1488 c_pkg_name || '.' ||l_api_name);
1489 end if;
1490
1491 for rowrec in get_old_row loop
1492
1493 resp_key := rowrec.responsibility_key;
1494
1495 rolename := role_name_from_resp_name( resp_key,
1496 rowrec.application_short_name,
1497 rowrec.security_group_key);
1498
1499 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1500 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1501 c_log_head || l_api_name || '.processing',
1502 'Processing role:'|| rolename ||' for user_id:'||
1503 rowrec.user_id ||' user_name:'||rowrec.user_name);
1504 end if;
1505 begin
1506 select name
1507 into dummy
1508 from wf_local_roles partition (FND_RESP)
1509 where name = rolename
1510 and rownum = 1;
1511 exception -- This shouldnt be necessary since the roles should already
1512 when no_data_found then -- have been created, but be safe.
1513 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1514 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1515 c_log_head || l_api_name || '.need_to_create_role',
1516 'Creating role:'|| rolename);
1517 end if;
1518 fnd_user_resp_groups_api.sync_roles_one_resp_secgrp(
1519 rowrec.responsibility_id,
1520 rowrec.responsibility_application_id,
1521 rowrec.responsibility_key,
1522 rowrec.security_group_id,
1523 rowrec.security_group_key,
1524 rowrec.resp_start_date,
1525 rowrec.resp_end_date);
1526 end;
1527
1528 begin
1529 select role_name
1530 into dummy
1531 from wf_all_user_roles waur
1532 where waur.role_name = rolename
1533 and waur.user_name = rowrec.user_name
1534 and ( (waur.start_date = rowrec.start_date)
1535 OR((waur.start_date is NULL) AND (rowrec.start_date is NULL)))
1536 and ( (waur.expiration_date = rowrec.end_date)
1537 OR((waur.expiration_date is NULL) AND(rowrec.end_date is NULL)));
1538 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1539 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1540 c_log_head || l_api_name || '.ur_exists',
1541 'USER_ROLE FOUND. Not inserting.');
1542 end if;
1543 exception
1544 when no_data_found then
1545 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
1546 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1547 c_log_head || l_api_name || '.ur_notfound',
1548 'USER_ROLE Not FOUND. Need to insert');
1549 end if;
1550 -- We cant just assume that the orig system is FND_USR. It could
1551 -- be PER because the row in wf_users/wf_roles is one or the other
1552 wf_directory.GetRoleOrigSysInfo(rowrec.user_name,
1553 l_user_orig_system,
1554 l_user_orig_system_id);
1555 -- In case there is no WF user, sync this user up so there is one.
1556 -- Should never happen but be safe in case sync wasnt perfect in past
1557 if( (l_user_orig_system is NULL)
1558 and (l_user_orig_system_id is NULL)) then
1559 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
1560 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1561 c_log_head || l_api_name || '.orig_notfound',
1562 'Orig system and id not found. Trying to sync user:'||
1563 rowrec.user_name);
1564 end if;
1565 fnd_user_pkg.user_synch(rowrec.user_name);
1566 wf_directory.GetRoleOrigSysInfo(rowrec.user_name,
1567 l_user_orig_system,
1568 l_user_orig_system_id);
1569 end if;
1570 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1571 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1572 c_log_head || l_api_name || '.got_orig',
1573 'Looked up orig: l_user_orig_system:'||
1574 l_user_orig_system ||' l_user_orig_system_id:'||
1575 l_user_orig_system_id);
1576 end if;
1577 end;
1578
1579 -- Sync the User/Role (but not if there is no wf_user)
1580 if( (l_user_orig_system is not NULL)
1581 or (l_user_orig_system_id is not NULL)) then
1582 begin
1583 wf_local_synch.PropagateUserRole
1584 (p_user_name=>rowrec.user_name,
1585 p_role_name=>rolename,
1586 p_user_orig_system=>l_user_orig_system,
1587 p_user_orig_system_id=>l_user_orig_system_id,
1588 p_role_orig_system=>'FND_RESP',
1589 p_role_orig_system_id=>rowrec.responsibility_id,
1590 p_start_date=>rowrec.start_date,
1591 p_expiration_date=>rowrec.end_date,
1592 p_overwrite=>TRUE,
1593 p_raiseErrors=>TRUE,
1594 p_parent_orig_system => 'FND_RESP',
1595 p_parent_orig_system_id =>rowrec.responsibility_id,
1596 p_ownerTag => rowrec.application_short_name,
1597 p_createdBy => fnd_global.user_id,
1598 p_lastUpdatedBy => fnd_global.user_id,
1599 p_lastUpdateLogin => 0,
1600 p_creationDate => sysdate,
1601 p_lastUpdatedate=> sysdate);
1602
1603 if (fnd_log.LEVEL_STATEMENT >= fnd_log.g_current_runtime_level) then
1604 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
1605 c_log_head || l_api_name || '.called_prop',
1606 'Successfully called wf_local_synch.PropagateUserRole');
1607 end if;
1608 exception when others then
1609 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
1610 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1611 c_log_head || l_api_name || '.propagate_fail',
1612 'PropogateUserRole call failed with exception for user:'||
1613 rowrec.user_name || ' role:'||rolename ||
1614 ' sql code:'||sqlcode ||
1615 ' sql errm:'||sqlerrm);
1616 end if;
1617 end;
1618 else
1619 if (fnd_log.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
1620 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,
1621 c_log_head || l_api_name || '.orig_notfound',
1622 'Did not propogate role because Orig System not found for user:'||
1623 rowrec.user_name);
1624 end if;
1625 end if;
1626
1627 commit;
1628
1629 end loop;
1630
1631 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
1632 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1633 c_log_head || l_api_name || '.end',
1634 c_pkg_name || '.' ||l_api_name );
1635 end if;
1636 */
1637 end one_time_furg_to_wf_upgrade;
1638
1639 -- sync_roles_all_secgrps_int
1640 --
1641 -- Bug4322412
1642 -- For a given resp, sync roles for all security groups if the role
1643 -- does not already exist.
1644 --
1645 -- NOTE:This routine does not update existing roles. To update existing roles
1646 -- the routine sync_roles_all_secgrps should be used.
1647 --
1648 procedure sync_roles_all_secgrps_int(
1649 respid in number,
1650 appid in number,
1651 respkey in varchar2,
1652 startdate in date,
1653 enddate in date)
1654 is
1655 cursor get_secgrp is
1656 select security_group_id,
1657 security_group_key
1658 from fnd_security_groups;
1659
1660 rolename varchar2(320);
1661 dummy number;
1662
1663 begin
1664
1665 if (check_secgrp_enabled(respid, appid) = 'N') then
1666 -- Security Groups not enabled for this resp,
1667 -- only create a role for the STANDARD sec grp.
1668 sync_roles_one_resp_secgrp( respid=> respid,
1669 appid=> appid,
1670 respkey=> respkey,
1671 secgrpid=> 0,
1672 secgrpkey=> 'STANDARD',
1673 startdate=> startdate,
1674 enddate=> enddate);
1675 else
1676 -- Security Groups are enabled, create one role for
1677 -- every resp/secgrp pair.
1678
1679 for secrec in get_secgrp loop
1680
1681 begin
1682 rolename := role_name_from_resp(respid, appid,
1683 secrec.security_group_id);
1684 select null
1685 into dummy
1686 from wf_local_roles
1687 where name = rolename
1688 and partition_id = 2
1689 and rownum = 1;
1690
1691 exception
1692 when no_data_found then
1693 sync_roles_one_resp_secgrp(
1694 respid => respid,
1695 appid => appid,
1696 respkey => respkey,
1697 secgrpid => secrec.security_group_id,
1698 secgrpkey => secrec.security_group_key,
1699 startdate => startdate,
1700 enddate => enddate);
1701 end;
1702 end loop;
1703 end if;
1704
1705 end sync_roles_all_secgrps_int;
1706
1707
1708 end Fnd_User_Resp_Groups_Api;
[Home] [Help]