1 package body Wf_Directory as
2 /* $Header: wfdirb.pls 120.41 2010/07/16 04:18:47 dafang ship $ */
3
4 --
5 -- Private variables and APIs
6 --
7 hasBulkSyncView boolean;
8 g_origSystem varchar2(30);
9 g_partitionID number;
10 g_localPartitionID number;
11 g_partitionName varchar2(30);
12 g_localPartitionName varchar2(30);
13 g_system_status varchar2(30);
14
15 -- logging variable
16 g_plsqlName varchar2(30) := 'wf.plsql.WF_DIRECTORY.';
17
18 -- System_Status (PRIVATE)
19 -- Returns the current System Status
20 function System_Status
21 return varchar2
22 is
23 begin
24 if wf_directory.g_system_status is null then
25 wf_directory.g_system_status:= wf_core.translate('WF_SYSTEM_STATUS');
26 end if;
27 return wf_directory.g_system_status;
28 end;
29 --
30 -- MinDate (PRIVATE)
31 -- Return the earliest of the two dates
32 -- IN
33 -- date1
34 -- date2
35 -- OUT
36 -- RETURN
37 -- date
38 --
39 function MinDate(date1 in date,
40 date2 in date)
41 return date
42 is
43 begin
44 if (date2 is not null) then
45 --
46 -- when date2 is non-null, so we need to check
47 -- both if date1 is null and which is ealier.
48 --
49 if (date1 is null or date2 < date1) then
50 return(date2);
51 end if;
52 end if;
53
54 -- Note that date1 could be null
55 return date1;
56 end MinDate;
57
58 --
59 -- IsBulkSync (Private)
60 -- Return true if bulk sync view exists
61 --
62 -- RETURN
63 -- boolean
64 --
65 function IsBulkSync
66 return boolean
67 is
68 cnt number;
69 begin
70 if (hasBulkSyncView is null) then
71
72 select count(1) into cnt
73 from USER_VIEWS
74 where VIEW_NAME = 'WF_FND_USR_ROLES';
75
76 if (cnt = 0) then
77 hasBulkSyncView := false;
78 else
79 hasBulkSyncView := true;
80 end if;
81 end if;
82
83 return hasBulkSyncView;
84 end IsBulkSync;
85
86
87 --
88 -- String_To_UserTable (PRIVATE)
89 -- Converts a comma/space delimited string of users into a UserTable
90 -- IN
91 -- P_UserList VARCHAR2
92 -- OUT
93 -- RETURN
94 -- P_UserTable WF_DIRECTORY.UserTable
95 --
96 procedure String_To_UserTable (p_UserList in VARCHAR2,
97 p_UserTable out NOCOPY WF_DIRECTORY.UserTable)
98 is
99
100 c1 pls_integer;
101 u1 pls_integer := 0;
102 l_userList varchar2(32000);
103
104 begin
105 if (p_UserList is not NULL) then
106 --
107 -- Substring and insert users into UserTable
108 --
109 l_userList := ltrim(p_UserList);
110 <<UserLoop>>
111 loop
112 c1 := instr(l_userList, ',');
113 if (c1 = 0) then
114 c1 := instr(l_userList, ' ');
115 if (c1 = 0) then
116 p_UserTable(u1) := l_userList;
117 exit;
118 else
119 p_UserTable(u1) := substr(l_userList, 1, c1-1);
120 end if;
121 else
122 p_UserTable(u1) := substr(l_userList, 1, c1-1);
123 end if;
124 u1 := u1 + 1;
125 l_userList := ltrim(substr(l_userList, c1+1));
126 end loop UserLoop;
127 end if;
128 end String_To_UserTable;
129
130
131 --
132 -- CompositeName (PRIVATE)
133 -- Extracts the origSystem/origSystemID from a composite name
134 -- IN
135 -- p_CompositeName VARCHAR2
136 -- OUT
137 -- p_CompositeName VARCHAR2
138 -- p_origSystem VARCHAR2
139 -- p_origSystemID NUMBER
140 -- RETURN
141 -- boolean
142 -- TRUE - if name is composite
143 -- FALSE - if name is not composite
144 --
145 function CompositeName (p_CompositeName IN VARCHAR2,
146 p_OrigSystem OUT NOCOPY VARCHAR2,
147 p_OrigSystemID OUT NOCOPY NUMBER) return boolean is
148
149 invalidNumConv EXCEPTION;
150 pragma exception_init(invalidNumConv, -6502);
151 colon NUMBER;
152
153 begin
154 colon := instr(p_CompositeName,':');
155 if (colon <> 0) then
156 p_origSystemID := to_number(substrb(p_CompositeName, colon+1));
157 p_origSystem := substrb(p_CompositeName, 1, colon-1);
158 return TRUE;
159 else
160 return FALSE;
161 end if;
162 exception
163 when invalidNumConv then
164 return FALSE;
165
166 when others then
167 wf_core.context('Wf_Directory','CompositeName',p_CompositeName);
168 raise;
169 end;
170
171 --
172 -- End Private API section
173 --
174
175 --
176 -- GETROLEUSERS
177 -- list of users who perform role
178 -- IN
179 -- role
180 -- OUT
181 -- table of users that perform the role
182 --
183 procedure GetRoleUsers(
184 role in varchar2,
185 users out NOCOPY Wf_Directory.UserTable)
186
187 is
188 l_origSystem VARCHAR2(30);
189 l_origSystemID NUMBER;
190 l_partID NUMBER;
191 l_partName VARCHAR2(30);
192
193 cursor c(c_rolename varchar2) is
194 select UR.USER_NAME
195 from WF_USER_ROLES UR
196 where UR.ROLE_NAME = c_rolename
197 and UR.PARTITION_ID not in (9,8,7,6,4);
198
199 cursor corig(c_rolename varchar2, c_origSys varchar2,
200 c_origSysID number, c_partID number) is
201 select UR.USER_NAME
202 from WF_USER_ROLES UR
203 where UR.ROLE_ORIG_SYSTEM = c_origSys
204 and UR.ROLE_ORIG_SYSTEM_ID = c_origSysID
205 and UR.ROLE_NAME = c_rolename
206 and UR.PARTITION_ID = c_partID;
207
208 begin
209 if (compositeName(role, l_origSystem, l_origSystemID)) then
210 AssignPartition(l_origSystem, l_partID, l_partName);
211 open corig(role, l_origSystem, l_origSystemID, l_partID);
212 fetch corig bulk collect into users;
213 close corig;
214 else
215 open c(role);
216 fetch c bulk collect into users;
217 close c;
218 end if;
219 exception
220 when others then
221 if c%ISOPEN then
222 close c;
223 elsif corig%ISOPEN then
224 close corig;
225 end if;
226 wf_core.context('Wf_Directory','GetRoleUsers',Role);
227 raise;
228 end GetRoleUsers;
229
230 --
231 -- GETUSERRELATION
232 -- list of users associated with a user
233 -- IN
234 -- base user
235 -- relationship
236 -- OUT
237 -- table of related users
238 -- NOTES
239 -- currently unimplemented!
240 -- different relations may be supported by different directory services,
241 -- so the implementation of this procedure is expected to vary.
242 -- Example relationships are 'MANAGER', 'REPORT', 'HR_REP'
243 --
244 procedure GetUserRelation(
245 base_user in varchar2,
246 relation in varchar2,
247 users out NOCOPY Wf_Directory.UserTable)
248 is
249 begin
250 null;
251 exception
252 when others then
253 wf_core.context('Wf_Directory','GetUserRelation',base_user,relation);
254 raise;
255 end GetUserRelation;
256
257 --
258 -- GETUSERROLES
259 -- list of roles performed by user
260 -- IN
261 -- user
262 -- OUT
263 -- table of roles performed by the user
264 --
265 procedure GetUserRoles(
266 user in varchar2,
267 roles out NOCOPY Wf_Directory.RoleTable)
268 is
269 l_origSystem VARCHAR2(30);
270 l_origSystemID NUMBER;
271
272 cursor c(c_username varchar2) is
273 select UR.ROLE_NAME
274 from WF_USER_ROLES UR
275 where UR.USER_NAME = c_username
276 and UR.USER_ORIG_SYSTEM not in ('HZ_PARTY','CUST_CONT');
277
278 cursor corig(c_username varchar2,
279 c_origSystem varchar2,
280 c_origSystemID number) is
281 select UR.ROLE_NAME
282 from WF_USER_ROLES UR
283 where UR.USER_ORIG_SYSTEM = c_origSystem
284 and UR.USER_ORIG_SYSTEM_ID = c_origSystemID
285 and UR.USER_NAME = c_username;
286
287 begin
288 if (CompositeName(user, l_origSystem, l_origSystemID)) then
289 open corig(user, l_origSystem, l_origSystemID);
290 fetch corig bulk collect into roles;
291 close corig;
292 else
293 open c(user);
294 fetch c bulk collect into roles;
295 close c;
296 end if;
297 exception
298 when others then
299 if (c%ISOPEN) then
300 close c;
301 elsif (corig%ISOPEN) then
302 close corig;
303 end if;
304
305 wf_core.context('Wf_Directory','GetUserRoles',User);
306 raise;
307 end GetUserRoles;
308
309 --
310 -- GETROLEINFO
311 -- information about a role
312 -- IN
313 -- role
314 -- OUT
315 -- display_name
316 -- email_address
317 -- notification_preference
318 -- language
319 -- territory
320 --
321 procedure GetRoleInfo(
322 role in varchar2,
323 display_name out NOCOPY varchar2,
324 email_address out NOCOPY varchar2,
325 notification_preference out NOCOPY varchar2,
326 language out NOCOPY varchar2,
327 territory out NOCOPY varchar2)
328 is
329 role_info_tbl wf_directory.wf_local_roles_tbl_type;
330 begin
331 Wf_Directory.GetRoleInfo2(role, role_info_tbl);
332
333 display_name := role_info_tbl(1).display_name;
334 email_address := role_info_tbl(1).email_address;
335 notification_preference := role_info_tbl(1).notification_preference;
336 language := role_info_tbl(1).language;
337 territory := role_info_tbl(1).territory;
338
339 exception
340 when others then
341 wf_core.context('Wf_Directory','GetRoleInfo',Role);
342 raise;
343 end GetRoleInfo;
344
345 --
346 -- GETROLEINFO2
347 -- information about a role
348 -- IN
349 -- role
350 -- OUT
351 -- role_info_tbl
352 --
353 procedure GetRoleInfo2(
354 role in varchar2,
355 role_info_tbl out NOCOPY wf_directory.wf_local_roles_tbl_type)
356 is
357 l_origSystem VARCHAR2(30);
358 l_origSystemID NUMBER;
359 l_isComposite BOOLEAN;
360 l_api varchar2(250) := g_plsqlName ||'GetRoleInfo2';
361 l_logPROC boolean := false;
362
363 begin
364 l_logPROC := WF_LOG_PKG.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level;
365 if( l_logPROC ) then
366 wf_log_pkg.String(WF_LOG_PKG.LEVEL_PROCEDURE, l_api,'BEGIN');
367 end if;
368
369 --Check for composite name.
370 l_isComposite := CompositeName(role, l_origSystem, l_origSystemID);
371
372 /*
373 ** First try to get the role information from the new
374 ** wfa_sec.get_role_info function. This function looks at each component
375 ** of the wf_roles view and attempts to get the information from there.
376 ** If it does not find the role then use the old method of looking at the
377 ** view.
378 */
379
380 --Get all info including expiration_date , fax and
381 --status for the role. Call get_role_info2
382 wfa_sec.get_role_info3(l_isComposite,
383 role,
384 role_info_tbl(1).name,
385 role_info_tbl(1).display_name,
386 role_info_tbl(1).description,
387 role_info_tbl(1).email_address,
388 role_info_tbl(1).notification_preference,
389 role_info_tbl(1).orig_system,
390 role_info_tbl(1).orig_system_id,
391 role_info_tbl(1).fax,
392 role_info_tbl(1).status,
393 role_info_tbl(1).expiration_date,
394 role_info_tbl(1).language,
395 role_info_tbl(1).territory,
396 role_info_tbl(1).nls_date_format, -- <7578908> new NLS parameters
397 role_info_tbl(1).nls_date_language,
398 role_info_tbl(1).nls_calendar,
399 role_info_tbl(1).nls_numeric_characters,
400 role_info_tbl(1).nls_sort,
401 role_info_tbl(1).nls_currency -- </7578908>
402 );
403
404 if (role_info_tbl.COUNT = 0 or role_info_tbl(1).display_name is NULL) then
405 if( l_logPROC ) then
406 wf_log_pkg.String(WF_LOG_PKG.LEVEL_PROCEDURE, l_api,
407 'no records found by wfa_sec.get_role_info3(), querying *active* roles in other partitions then.');
408 end if;
409
410 if NOT (l_isComposite) then
411 if( l_logPROC ) then
412 wf_log_pkg.String(WF_LOG_PKG.LEVEL_PROCEDURE, l_api,'case of no composite name');
413 end if;
414
415 -- try to select from all the ORIG_SYSTEMs that have no colon.
416 -- we query FNDRESPXXX also since we cannot list all of them here.
417 -- it is safer to use 'not in', in case of custom additions.
418 select R.NAME,
419 substrb(R.DISPLAY_NAME,1,360),
420 substrb(R.DESCRIPTION,1,1000),
421 R.NOTIFICATION_PREFERENCE,
422 R.LANGUAGE,
423 R.TERRITORY,
424 wf_core.nls_date_format, -- <7578908> new NLS parameters
425 R.LANGUAGE, -- default nls_date_language
426 wf_core.nls_calendar ,
427 wf_core.nls_numeric_characters,
428 wf_core.nls_sort,
429 wf_core.nls_currency, -- </7578908>
430 substrb(R.EMAIL_ADDRESS,1,320),
431 R.FAX,
432 R.STATUS,
433 R.EXPIRATION_DATE,
434 R.ORIG_SYSTEM,
435 R.ORIG_SYSTEM_ID,
436 R.PARENT_ORIG_SYSTEM,
437 R.PARENT_ORIG_SYSTEM_ID,
438 R.OWNER_TAG,
439 R.LAST_UPDATE_DATE,
440 R.LAST_UPDATED_BY,
441 R.CREATION_DATE,
442 R.CREATED_BY,
443 R.LAST_UPDATE_LOGIN
444 into role_info_tbl(1)
445 from WF_LOCAL_ROLES R
446 where R.NAME = GetRoleInfo2.role
447 and R.PARTITION_ID not in (9,8,7,6,4)
448 and nvl(R.EXPIRATION_DATE, sysdate+1) > sysdate
449 and rownum = 1;
450
451 else
452 if( l_logPROC ) then
453 wf_log_pkg.String(WF_LOG_PKG.LEVEL_PROCEDURE, l_api,'case of composite name, querying with orig_system');
454 end if;
455
456 select R.NAME,
457 substrb(R.DISPLAY_NAME,1,360),
458 substrb(R.DESCRIPTION,1,1000),
459 R.NOTIFICATION_PREFERENCE,
460 R.LANGUAGE,
461 R.TERRITORY,
462 wf_core.nls_date_format, -- <7578908> new NLS parameters
463 R.LANGUAGE, -- default nls_date_language
464 wf_core.nls_calendar ,
465 wf_core.nls_numeric_characters,
466 wf_core.nls_sort,
467 wf_core.nls_currency, -- </7578908>
468 substrb(R.EMAIL_ADDRESS,1,320),
469 R.FAX,
470 R.STATUS,
471 R.EXPIRATION_DATE,
472 R.ORIG_SYSTEM,
473 R.ORIG_SYSTEM_ID,
474 R.PARENT_ORIG_SYSTEM,
475 R.PARENT_ORIG_SYSTEM_ID,
476 R.OWNER_TAG,
477 R.LAST_UPDATE_DATE,
478 R.LAST_UPDATED_BY,
479 R.CREATION_DATE,
480 R.CREATED_BY,
481 R.LAST_UPDATE_LOGIN
482 into role_info_tbl(1)
483 from WF_LOCAL_ROLES R
484 where R.ORIG_SYSTEM = l_origSystem
485 and R.ORIG_SYSTEM_ID = l_origSystemID
486 and R.NAME = GetRoleInfo2.role
487 and nvl(R.EXPIRATION_DATE, sysdate+1) > sysdate
488 and rownum = 1;
489 end if;
490
491 else
492 role_info_tbl(1).name := role;
493 end if;
494
495 if( l_logPROC ) then
496 wf_log_pkg.String(WF_LOG_PKG.LEVEL_PROCEDURE, l_api,'END');
497 end if;
498 exception
499 when no_data_found then
500 if( WF_LOG_PKG.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
501 wf_log_pkg.String(WF_LOG_PKG.LEVEL_EXCEPTION, l_api,'role not found, querying ALL roles');
502 end if;
503 --If the role is not found in the local tables, we will check the view to make
504 --sure we continue to support standalone which has not denormalized wfds.
505 begin
506 if NOT (l_isComposite) then
507 if( l_logPROC ) then
508 wf_log_pkg.String(WF_LOG_PKG.LEVEL_PROCEDURE, l_api,'case of not composite role name');
509 end if;
510 select R.NAME,
511 substrb(R.DISPLAY_NAME,1,360),
512 substrb(R.DESCRIPTION,1,1000),
513 R.NOTIFICATION_PREFERENCE,
514 R.LANGUAGE,
515 R.TERRITORY,
516 wf_core.nls_date_format, -- <7578908> new NLS parameters
517 R.LANGUAGE, -- default nls_date_language
518 wf_core.nls_calendar ,
519 wf_core.nls_numeric_characters,
520 wf_core.nls_sort,
521 wf_core.nls_currency, -- </7578908>
522 substrb(R.EMAIL_ADDRESS,1,320),
523 R.FAX,
524 R.STATUS,
525 R.EXPIRATION_DATE,
526 R.ORIG_SYSTEM,
527 R.ORIG_SYSTEM_ID,
528 NULL,
529 to_number(NULL),
530 NULL,
531 to_date(NULL),
532 to_number(NULL),
533 to_date(NULL),
534 to_number(NULL),
535 to_number(NULL)
536 into role_info_tbl(1)
537 from WF_ROLES R
538 where R.NAME = GetRoleInfo2.role
539 and R.PARTITION_ID not in (9,8,7,6,4)
540 and rownum = 1;
541 else
542 if( l_logPROC ) then
543 wf_log_pkg.String(WF_LOG_PKG.LEVEL_PROCEDURE, l_api,'case of composite role name');
544 end if;
545
546 select R.NAME,
547 substrb(R.DISPLAY_NAME,1,360),
548 substrb(R.DESCRIPTION,1,1000),
549 R.NOTIFICATION_PREFERENCE,
550 R.LANGUAGE,
551 R.TERRITORY,
552 wf_core.nls_date_format, -- <7578908> new NLS parameters
553 R.LANGUAGE, -- default nls_date_language
554 wf_core.nls_calendar ,
555 wf_core.nls_numeric_characters,
556 wf_core.nls_sort,
557 wf_core.nls_currency, -- </7578908>
558 substrb(R.EMAIL_ADDRESS,1,320),
559 R.FAX,
560 R.STATUS,
561 R.EXPIRATION_DATE,
562 R.ORIG_SYSTEM,
563 R.ORIG_SYSTEM_ID,
564 NULL,
565 to_number(NULL),
566 NULL,
567 to_date(NULL),
568 to_number(NULL),
569 to_date(NULL),
570 to_number(NULL),
571 to_number(NULL)
572 into role_info_tbl(1)
573 from WF_ROLES R
574 where R.ORIG_SYSTEM = l_origSystem
575 and R.ORIG_SYSTEM_ID = l_origSystemID
576 and R.NAME = GetRoleInfo2.role
577 and rownum = 1;
578 end if;
579
580 if( l_logPROC ) then
581 wf_log_pkg.String(WF_LOG_PKG.LEVEL_PROCEDURE, l_api,'END');
582 end if;
583 exception
584 when NO_DATA_FOUND then
585 if( WF_LOG_PKG.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level) then
586 wf_log_pkg.String(WF_LOG_PKG.LEVEL_EXCEPTION, l_api,'role not found again, setting role_info_tbl fields to NULL');
587 end if;
588 role_info_tbl(1).name := '';
589 role_info_tbl(1).display_name := '';
590 role_info_tbl(1).description := '';
591 role_info_tbl(1).notification_preference := '';
592 role_info_tbl(1).language := '';
593 role_info_tbl(1).territory := '';
594 role_info_tbl(1).email_address := '';
595 role_info_tbl(1).fax := '';
596 role_info_tbl(1).status := '';
597 role_info_tbl(1).expiration_date := to_date(null);
598 role_info_tbl(1).orig_system := '';
599 role_info_tbl(1).orig_system_id := to_number(null);
600 role_info_tbl(1).parent_orig_system := '';
601 role_info_tbl(1).parent_orig_system_id := to_number(null);
602 role_info_tbl(1).owner_tag := null;
603 role_info_tbl(1).last_update_date := to_date(null);
604 role_info_tbl(1).last_updated_by := to_number(null);
605 role_info_tbl(1).creation_date := to_date(null);
606 role_info_tbl(1).last_update_login := to_number(null);
607 role_info_tbl(1).NLS_DATE_FORMAT :='';
608 role_info_tbl(1).NLS_DATE_LANGUAGE :='';
609 role_info_tbl(1).NLS_CALENDAR :='';
610 role_info_tbl(1).NLS_NUMERIC_CHARACTERS :='';
611 role_info_tbl(1).NLS_SORT :='';
612 role_info_tbl(1).NLS_CURRENCY :='';
613
614 if( l_logPROC ) then
615 wf_log_pkg.String(WF_LOG_PKG.LEVEL_PROCEDURE, l_api,'END');
616 end if;
617 end;
618
619 when others then
620 wf_core.context('Wf_Directory','GetRoleInfo2',Role);
621 raise;
622 end GetRoleInfo2;
623
624 --
625 -- GETROLEINFOMAIL
626 -- All information about a role for mailer
627 -- IN
628 -- role
629 -- OUT
630 -- display_name
631 -- email_address
632 -- notification_preference
633 -- language
634 -- territory
635 -- orig_system
636 -- orig_system_id
637 -- installed_flag - Y when a language is installed in WF_LANGUAGES,
638 -- N otherwise.
639 --
640 procedure GetRoleInfoMail(
641 role in varchar2,
642 display_name out NOCOPY varchar2,
643 email_address out NOCOPY varchar2,
644 notification_preference out NOCOPY varchar2,
645 language out NOCOPY varchar2,
646 territory out NOCOPY varchar2,
647 orig_system out NOCOPY varchar2,
648 orig_system_id out NOCOPY number,
649 installed_flag out NOCOPY varchar2)
650 is
651 role_info_tbl wf_directory.wf_local_roles_tbl_type;
652 begin
653
654 Wf_Directory.GetRoleInfo2(role, role_info_tbl);
655
656 display_name := role_info_tbl(1).display_name;
657 email_address := role_info_tbl(1).email_address;
658 notification_preference := role_info_tbl(1).notification_preference;
659 language := role_info_tbl(1).language;
660 territory := role_info_tbl(1).territory;
661 orig_system := role_info_tbl(1).orig_system;
662 orig_system_id := role_info_tbl(1).orig_system_id;
663
664 begin
665 select nvl(INSTALLED_FLAG, 'N')
666 into GetRoleInfoMail.installed_flag
667 from WF_LANGUAGES
668 where NLS_LANGUAGE = GetRoleInfoMail.language;
669 exception
670 when NO_DATA_FOUND then
671 installed_flag := 'N';
672 end;
673
674 exception
675 when others then
676 wf_core.context('Wf_Directory','GetRoleInfoMail',Role);
677 raise;
678 end GetRoleInfoMail;
679
680 procedure GetRoleInfoMail2( p_role in varchar2,
681 p_display_name out NOCOPY varchar2,
682 p_email_address out NOCOPY varchar2,
683 p_notification_preference out NOCOPY varchar2,
684 p_orig_system out NOCOPY varchar2,
685 p_orig_system_id out NOCOPY number,
686 p_installed_flag out NOCOPY varchar2
687 , p_nlsLanguage out NOCOPY varchar2,
688 p_nlsTerritory out NOCOPY varchar2
689 , p_nlsDateFormat out NOCOPY varchar2
690 , p_nlsDateLanguage out NOCOPY varchar2
691 , p_nlsCalendar out NOCOPY varchar2
692 , p_nlsNumericCharacters out NOCOPY varchar2
693 , p_nlsSort out NOCOPY varchar2
694 , p_nlsCurrency out NOCOPY varchar2)
695 is
696 l_role_info_tbl wf_directory.wf_local_roles_tbl_type;
697 l_api varchar2(250) := g_plsqlName ||'GetRoleInfoMail2';
698
699 begin
700 if( WF_LOG_PKG.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level ) then
701 wf_log_pkg.String(WF_LOG_PKG.LEVEL_PROCEDURE, l_api,'BEGIN');
702 end if;
703
704 Wf_Directory.GetRoleInfo2(p_role, l_role_info_tbl);
705
706 p_display_name := l_role_info_tbl(1).display_name;
707 p_email_address := l_role_info_tbl(1).email_address;
708 p_notification_preference := l_role_info_tbl(1).notification_preference;
709 p_orig_system := l_role_info_tbl(1).orig_system;
710 p_orig_system_id := l_role_info_tbl(1).orig_system_id;
711
712 p_nlsLanguage := l_role_info_tbl(1).language;
713 p_nlsTerritory := l_role_info_tbl(1).territory;
714 p_nlsDateFormat := l_role_info_tbl(1).NLS_DATE_FORMAT;
715 p_nlsDateLanguage := l_role_info_tbl(1).NLS_DATE_LANGUAGE;
716 p_nlsCalendar := l_role_info_tbl(1).NLS_CALENDAR;
717 p_nlsNumericCharacters := l_role_info_tbl(1).NLS_NUMERIC_CHARACTERS;
718 p_nlsSort := l_role_info_tbl(1).NLS_SORT;
719 p_nlsCurrency := l_role_info_tbl(1).NLS_CURRENCY;
720
721 begin
722 select nvl(INSTALLED_FLAG, 'N')
723 into GetRoleInfoMail2.p_installed_flag
724 from WF_LANGUAGES
725 where NLS_LANGUAGE = GetRoleInfoMail2.p_nlsLanguage;
726 exception
727 when NO_DATA_FOUND then
728 p_installed_flag := 'N';
729 end;
730
731 if( WF_LOG_PKG.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level ) then
732 wf_log_pkg.String(WF_LOG_PKG.LEVEL_PROCEDURE, l_api,'END');
733 end if;
734 exception
735 when others then
736 wf_core.context('Wf_Directory','GetRoleInfoMail2', p_role);
737 raise;
738 end GetRoleInfoMail2;
739
740 --
741 -- GETROLENTFPREF
742 -- Obtains the notification preference for a given role
743 -- IN
744 -- role
745 -- OUT
746 -- notification_preference
747 --
748 function GetRoleNtfPref(
749 role in varchar2) return varchar2
750 is
751 role_info_tbl wf_directory.wf_local_roles_tbl_type;
752 notification_preference varchar2(8);
753 begin
754
755 Wf_Directory.GetRoleInfo2(role, role_info_tbl);
756 notification_preference := role_info_tbl(1).notification_preference;
757
758 return notification_preference;
759 exception
760 when others then
761 wf_core.context('Wf_Directory','GetRoleNotePref',Role);
762 raise;
763 end GetRoleNtfPref;
764
765 --
766 -- GETROLEORIGSYSINFO
767 -- orig system information about a role
768 -- IN
769 -- role
770 -- OUT
771 -- orig_system
772 -- orig_system_id
773 --
774 procedure GetRoleOrigSysInfo(
775 role in varchar2,
776 orig_system out NOCOPY varchar2,
777 orig_system_id out NOCOPY number
778 )
779 is
780 role_info_tbl wf_directory.wf_local_roles_tbl_type;
781 begin
782 Wf_Directory.GetRoleInfo2(role, role_info_tbl);
783
784 orig_system := role_info_tbl(1).orig_system;
785 orig_system_id := role_info_tbl(1).orig_system_id;
786
787 exception
788 when others then
789 wf_core.context('Wf_Directory','GetRoleOrigSysInfo',Role);
790 raise;
791 end GetRoleOrigSysInfo;
792
793 --
794 -- GETROLEPARTITIONINFO
795 -- partition information about a role
796 -- IN
797 -- role
798 -- OUT
799 -- partition_id
800 -- orig_system
801 -- display_name
802 --
803 procedure GetRolePartitionInfo(
804 role in varchar2,
805 partition_id out nocopy number,
806 orig_system out nocopy varchar2,
807 display_name out nocopy varchar2
808 )
809 is
810 l_orig_system varchar2(30);
811 l_orig_system_id number;
812 l_is_composite boolean;
813 begin
814 l_is_composite := CompositeName(role, l_orig_system, l_orig_system_id);
815
816 if not l_is_composite then
817 select partition_id, orig_system
818 into GetRolePartitionInfo.partition_id, GetRolePartitionInfo.orig_system
819 from wf_local_roles
820 where name = GetRolePartitionInfo.role
821 and nvl(expiration_date, sysdate+1) > sysdate
822 and rownum = 1;
823 else
824 select partition_id, orig_system
825 into GetRolePartitionInfo.partition_id, GetRolePartitionInfo.orig_system
826 from wf_local_roles
827 where name = GetRolePartitionInfo.role
828 and orig_system =l_orig_system
829 and orig_system_id = l_orig_system_id
830 and nvl(expiration_date, sysdate+1) > sysdate
831 and rownum = 1;
832 end if;
833
834 select orig_system, display_name
835 into GetRolePartitionInfo.orig_system,
836 GetRolePartitionInfo.display_name
837 from wf_directory_partitions_vl
838 where GetRolePartitionInfo.partition_id <> 1
839 and partition_id = GetRolePartitionInfo.partition_id
840 or GetRolePartitionInfo.partition_id = 1
841 and orig_system = GetRolePartitionInfo.orig_system;
842
843 exception
844 when no_data_found then
845 begin
846 if not l_is_composite then
847 select partition_id, orig_system
848 into GetRolePartitionInfo.partition_id, GetRolePartitionInfo.orig_system
849 from wf_roles
850 where name = GetRolePartitionInfo.role
851 and rownum = 1;
852 else
853 select partition_id, orig_system
854 into GetRolePartitionInfo.partition_id, GetRolePartitionInfo.orig_system
855 from wf_roles
856 where name = GetRolePartitionInfo.role
857 and orig_system =l_orig_system
858 and orig_system_id = l_orig_system_id
859 and rownum = 1;
860 end if;
861
862 select orig_system, display_name
863 into GetRolePartitionInfo.orig_system,
864 GetRolePartitionInfo.display_name
865 from wf_directory_partitions_vl
866 where GetRolePartitionInfo.partition_id <> 1
867 and partition_id = GetRolePartitionInfo.partition_id
868 or GetRolePartitionInfo.partition_id = 1
869 and orig_system = GetRolePartitionInfo.orig_system;
870
871 exception
872 when no_data_found then
873 partition_id := -1;
874 orig_system := null;
875 display_name := null;
876 when others then
877 wf_core.context('Wf_Directory','GetRolePartitionInfo',role);
878 raise;
879 end;
880 when others then
881 wf_core.context('Wf_Directory','GetRolePartitionInfo',role);
882 raise;
883 end GetRolePartitionInfo;
884
885 --
886 -- ISPERFORMER
887 -- test if user performs role
888 --
889 function IsPerformer(
890 user in varchar2,
891 role in varchar2) return boolean
892 is
893 userComposite boolean;
894 roleComposite boolean;
895 l_uorigSys varchar2(30);
896 l_uorigSysID number;
897 l_rorigSys varchar(30);
898 l_rorigSysID number;
899 l_partID number;
900 l_partName varchar2(30);
901 dummy pls_integer;
902
903 begin
904 userComposite := CompositeName(user, l_uorigSys, l_uorigSysID);
905 roleComposite := CompositeName(role, l_rorigSys, l_rorigSysID);
906
907 if NOT (roleComposite) then
908 if NOT (userComposite) then
909 select 1
910 into dummy
911 from SYS.DUAL
912 where exists
913 (select null
914 from WF_USER_ROLES UR
915 where UR.USER_NAME = IsPerformer.user
916 and UR.USER_ORIG_SYSTEM not in ('HZ_PARTY')
917 and UR.ROLE_NAME = IsPerformer.role
918 and UR.PARTITION_ID not in (9,8,7,6,4)
919 );
920 else
921 select 1
922 into dummy
923 from SYS.DUAL
924 where exists
925 (select null
926 from WF_USER_ROLES UR
927 where UR.USER_ORIG_SYSTEM = l_uOrigSys
928 and UR.USER_ORIG_SYSTEM_ID = l_uOrigSysID
929 and UR.USER_NAME = IsPerformer.user
930 and UR.ROLE_NAME = IsPerformer.role
931 and UR.PARTITION_ID not in (9,8,7,6,4)
932 );
933 end if;
934 else
935 AssignPartition (l_rorigSys,l_partID,l_partName);
936 if NOT (userComposite) then
937 select 1
938 into dummy
939 from SYS.DUAL
940 where exists
941 (select null
942 from WF_USER_ROLES UR
943 where UR.USER_NAME = IsPerformer.user
944 and UR.USER_ORIG_SYSTEM not in ('HZ_PARTY')
945 and UR.ROLE_NAME = IsPerformer.role
946 and UR.ROLE_ORIG_SYSTEM = l_rorigSys
947 and UR.ROLE_ORIG_SYSTEM_ID = l_rorigSysID
948 and UR.PARTITION_ID = l_partID);
949 else
950 select 1
951 into dummy
952 from SYS.DUAL
953 where exists
954 (select null
955 from WF_USER_ROLES UR
956 where UR.USER_ORIG_SYSTEM = l_uOrigSys
957 and UR.USER_ORIG_SYSTEM_ID = l_rOrigSysID
958 and UR.USER_NAME = IsPerformer.user
959 and UR.ROLE_ORIG_SYSTEM = l_rOrigSys
960 and UR.ROLE_ORIG_SYSTEM_ID = l_rOrigSysID
961 and UR.ROLE_NAME = IsPerformer.role
962 and UR.PARTITION_ID = l_partID);
963 end if;
964 end if;
965 return TRUE;
966 exception
967 when no_data_found then
968 return FALSE;
969 when others then
970 wf_core.context('Wf_Directory','IsPerformer',User,Role);
971 raise;
972 end IsPerformer;
973
974 --
975 -- CURRENTUSER
976 -- user name for current db session
977 -- NOTES
978 -- unimplemented! This needs more thought.
979 --
980 function CurrentUser return varchar2
981 is
982 begin
983 return NULL;
984 exception
985 when others then
986 wf_core.context('Wf_Directory','CurrentUser');
987 raise;
988 end CurrentUser;
989
990 --
991 -- USERACTIVE
992 -- determine if a user is currently active
993 -- IN
994 -- username
995 -- RETURN:
996 -- True - If user is Active
997 -- False - If User is NOT Active
998 --
999 function UserActive(
1000 username in varchar2)
1001 return boolean
1002 is
1003 colon pls_integer;
1004 dummy pls_integer;
1005 begin
1006 colon := instr(username, ':');
1007 if (colon = 0) then
1008 select 1
1009 into dummy
1010 from SYS.DUAL
1011 where exists
1012 (select null
1013 from wf_users
1014 where name = username
1015 and PARTITION_ID <> 9
1016 and status = 'ACTIVE');
1017 else
1018 select 1
1019 into dummy
1020 from SYS.DUAL
1021 where exists
1022 (select null
1023 from wf_users
1024 where orig_system = substr(username, 1, colon-1)
1025 and orig_system_id = substr(username, colon+1)
1026 and name = username
1027 and status = 'ACTIVE');
1028 end if;
1029
1030 return TRUE;
1031 exception
1032 when no_data_found then
1033 return FALSE;
1034 when others then
1035 wf_core.context('Wf_Directory','UserActive',Username);
1036 raise;
1037 end UserActive;
1038
1039 --
1040 -- RoleActive
1041 -- determine if a user is currently active
1042 -- IN
1043 -- rolename
1044 -- RETURN:
1045 -- True - If user is Active
1046 -- False - If User is NOT Active
1047 --
1048 function RoleActive(
1049 p_rolename in varchar2)
1050 return boolean
1051 is
1052 colon pls_integer;
1053 dummy pls_integer;
1054 begin
1055 if WF_DIRECTORY.UserActive(p_rolename) then
1056 return TRUE;
1057 end if;
1058 colon := instr(p_rolename, ':');
1059 if (colon = 0) then
1060 select 1
1061 into dummy
1062 from SYS.DUAL
1063 where exists
1064 (select null
1065 from WF_ROLES
1066 where name = p_rolename
1067 and status = 'ACTIVE');
1068 else
1069 select 1
1070 into dummy
1071 from SYS.DUAL
1072 where exists
1073 (select null
1074 from WF_ROLES
1075 where orig_system = substr(p_rolename, 1, colon-1)
1076 and orig_system_id = substr(p_rolename, colon+1)
1077 and name = p_rolename
1078 and status = 'ACTIVE');
1079 end if;
1080 return TRUE;
1081 exception
1082 when no_data_found then
1083 return FALSE;
1084 when others then
1085 wf_core.context('Wf_Directory','RoleActive',p_rolename);
1086 raise;
1087 end RoleActive;
1088
1089 --
1090 -- GETUSERNAME
1091 -- returns the Workflow username given the originating system info
1092 -- IN
1093 -- orig_system - Code identifying the original table
1094 -- orig_system_id - Id of the row in original table
1095 -- OUT
1096 -- user_name - Workflow user_name
1097 -- display_name - Users display name
1098 --
1099 procedure GetUserName(p_orig_system in varchar2,
1100 p_orig_system_id in varchar2,
1101 p_name out NOCOPY varchar2,
1102 p_display_name out NOCOPY varchar2)
1103 is
1104 cursor c_user is
1105 select name,
1106 substrb(display_name,1,360)
1107 p_display_name
1108 from wf_users
1109 where orig_system = p_orig_system
1110 and orig_system_id = p_orig_system_id
1111 order by status, start_date;
1112
1113 begin
1114 open c_user;
1115 fetch c_user into p_name, p_display_name;
1116 close c_user;
1117 exception
1118 when others then
1119 wf_core.context('Wf_Directory','GetUserName', p_orig_system,
1120 p_orig_system_id);
1121 raise;
1122 end GetuserName;
1123
1124 --
1125 -- GETROLENAME
1126 -- returns the Workflow rolename given the originating system info
1127 -- IN
1128 -- orig_system - Code identifying the original table
1129 -- orig_system_id - Id of the row in original table
1130 -- OUT
1131 -- name - Workflow role name
1132 -- display_name - role display name
1133 --
1134 procedure GetRoleName(p_orig_system in varchar2,
1135 p_orig_system_id in varchar2,
1136 p_name out NOCOPY varchar2,
1137 p_display_name out NOCOPY varchar2)
1138 is
1139 cursor c_role is
1140 select name,
1141 substrb(display_name,1,360)
1142 p_display_name
1143 from wf_roles
1144 where orig_system = p_orig_system
1145 and orig_system_id = p_orig_system_id
1146 order by status, start_date;
1147 begin
1148 open c_role;
1149 fetch c_role into p_name,p_display_name;
1150 close c_role;
1151 exception
1152 when others then
1153 wf_core.context('Wf_Directory','GetRoleName',p_orig_system,p_orig_system);
1154 raise;
1155 end GetRoleName;
1156
1157 --
1158 -- GetRoleDisplayName
1159 -- Return display name of role
1160 -- IN
1161 -- p_role_name - internal name of role
1162 -- RETURNS
1163 -- role display name
1164 --
1165 -- NOTE
1166 -- Cannot implement using GetRoleInfo/GetRoleInfo2, because of the
1167 -- pragma WNPS.
1168 --
1169 function GetRoleDisplayName (
1170 p_role_name in varchar2)
1171 return varchar2
1172 is
1173 colon pls_integer;
1174
1175 cursor c_role (l_name in varchar2) is
1176 select substrb(display_name,1,360)
1177 from wf_roles
1178 where name = l_name
1179 and PARTITION_ID not in (9,8,7,6,4);
1180
1181 cursor corig_role (l_name in varchar2, l_origSys in varchar2,
1182 l_origSysID in number) is
1183 select substrb(display_name,1,360)
1184 from wf_roles
1185 where orig_system = l_origSys
1186 and orig_system_id = l_origSysID
1187 and name = l_name;
1188
1189 l_display_name wf_roles.display_name%TYPE;
1190 invalidNumConv EXCEPTION;
1191 pragma exception_init(invalidNumConv, -6502);
1192 begin
1193 begin
1194 colon := instr(p_role_name, ':');
1195 if (colon = 0) then
1196 open c_role(p_role_name);
1197 fetch c_role into l_display_name;
1198 close c_role;
1199 else
1200 open corig_role(p_role_name, substr(p_role_name, 1, colon-1),
1201 to_number(substr(p_role_name, colon+1)));
1202 fetch corig_role into l_display_name;
1203 close corig_role;
1204 end if;
1205 exception
1206 when invalidNumConv then
1207 --p_role_name is not a true composite so we failed to open corig_role
1208 --we will fall back to c_role.
1209 open c_role(p_role_name);
1210 fetch c_role into l_display_name;
1211 close c_role;
1212 end;
1213 return l_display_name;
1214 end GetRoleDisplayName;
1215
1216 --
1217 -- GetRoleDisplayName2
1218 -- Return display name of active/inactive role
1219 -- IN
1220 -- p_role_name - internal name of role
1221 -- RETURNS
1222 -- role display name
1223 --
1224 -- NOTE
1225 -- Cannot implement using GetRoleInfo/GetRoleInfo2, because of the
1226 -- pragma WNPS.
1227 --
1228 function GetRoleDisplayName2 (
1229 p_role_name in varchar2)
1230 return varchar2
1231 is
1232 colon pls_integer;
1233
1234 cursor c_role (l_name in varchar2) is
1235 select substrb(nvl(wrt.display_name,wr.display_name),1,360)
1236 from wf_local_roles wr, wf_local_roles_tl wrt
1237 where wr.name = l_name
1238 and wr.orig_system = wrt.orig_system (+)
1239 and wr.orig_system_id = wrt.orig_system_id (+)
1240 and wr.name = wrt.name (+)
1241 and wr.partition_id = wrt.partition_id (+)
1242 and wrt.language (+) = userenv('LANG')
1243 and wr.partition_id not in (9,8,7,6,4,3);
1244
1245 cursor corig_role (l_name in varchar2, l_origSys in varchar2,
1246 l_origSysID in number) is
1247 select substrb(nvl(wrt.display_name,wr.display_name),1,360)
1248 from wf_local_roles wr, wf_local_roles_tl wrt
1249 where wr.orig_system = l_origSys
1250 and wr.orig_system_id = l_origSysID
1251 and wr.name = l_name
1252 and wr.orig_system = wrt.orig_system (+)
1253 and wr.orig_system_id = wrt.orig_system_id (+)
1254 and wr.name = wrt.name (+)
1255 and wr.partition_id = wrt.partition_id (+)
1256 and wrt.language (+) = userenv('LANG')
1257 AND wr.partition_id <> 3;
1258
1259 l_display_name varchar2(360);
1260 invalidNumConv EXCEPTION;
1261 pragma exception_init(invalidNumConv, -6502);
1262 begin
1263 begin
1264 colon := instr(p_role_name, ':');
1265 if (colon = 0) then
1266 open c_role(p_role_name);
1267 fetch c_role into l_display_name;
1268 close c_role;
1269 else
1270 open corig_role(p_role_name, substr(p_role_name, 1, colon-1),
1271 to_number(substr(p_role_name, colon+1)));
1272 fetch corig_role into l_display_name;
1273 close corig_role;
1274 end if;
1275 exception
1276 when invalidNumConv then
1277 --p_role_name is not a true composite so we failed to open corig_role
1278 --we will fall back to c_role.
1279 open c_role(p_role_name);
1280 fetch c_role into l_display_name;
1281 close c_role;
1282 end;
1283 return l_display_name;
1284 end GetRoleDisplayName2;
1285
1286 --
1287 -- SetAdHocUserStatus
1288 -- Update status for user
1289 -- IN
1290 -- user_name -
1291 -- status - status could be 'ACTIVE' or 'INACTIVE'
1292 -- OUT
1293 --
1294 procedure SetAdHocUserStatus(user_name in varchar2,
1295 status in varchar2)
1296 is
1297 begin
1298 --
1299 -- Update Status
1300 --
1301 SetUserAttr(user_name=>SetAdHocUserStatus.user_name,
1302 orig_system=>'WF_LOCAL_USERS',
1303 orig_system_id=>0,
1304 display_name=>NULL,
1305 notification_preference=>NULL,
1306 language=>NULL,
1307 territory=>NULL,
1308 email_address=>NULL,
1309 fax=>NULL,
1310 expiration_date=>NULL,
1311 status=>SetAdHocUserStatus.status);
1312
1313 exception
1314 when others then
1315 wf_core.context('Wf_Directory', 'SetAdHocUserStatus', user_name, status);
1316 raise;
1317 end SetAdHocUserStatus;
1318
1319 --
1320 -- SetAdHocRoleStatus
1321 -- Update status for role
1322 -- IN
1323 -- role_name -
1324 -- status - status could be 'ACTIVE' or 'INACTIVE'
1325 -- OUT
1326 --
1327 procedure SetAdHocRoleStatus(role_name in varchar2,
1328 status in varchar2)
1329 is
1330 begin
1331 --
1332 -- Update Status
1333 --
1334 SetRoleAttr(role_name=>SetAdHocRoleStatus.role_name,
1335 orig_system=>'WF_LOCAL_ROLES',
1336 orig_system_id=>0,
1337 display_name=>NULL,
1338 notification_preference=>NULL,
1339 language=>NULL,
1340 territory=>NULL,
1341 email_address=>NULL,
1342 fax=>NULL,
1343 expiration_date=>NULL,
1344 status=>SetAdHocRoleStatus.status);
1345
1346 exception
1347 when others then
1348 wf_core.context('Wf_Directory', 'SetAdHocRoleStatus', role_name, status);
1349 raise;
1350 end SetAdHocRoleStatus;
1351
1352
1353 --
1354 -- CreateUser (PRIVATE)
1355 -- Create a User
1356 -- IN
1357 -- name - User Name
1358 -- display_name - User display name
1359 -- description -
1360 -- notification_preference -
1361 -- language -
1362 -- territory -
1363 -- email_address -
1364 -- fax -
1365 -- status -
1366 -- expiration_date - NULL expiration date means no expiration
1367 -- orig_system
1368 -- orig_system_id
1369 -- parent_orig_system
1370 -- parent_orig_system_id
1371 -- owner_tag -
1372 -- last_update_date -
1373 -- last_updated_by -
1374 -- creation_date -
1375 -- created_by -
1376 -- last_update_login
1377 -- OUT
1378 --
1379
1380 procedure CreateUser( name in varchar2,
1381 display_name in varchar2,
1382 orig_system in varchar2,
1383 orig_system_id in number,
1384 language in varchar2,
1385 territory in varchar2,
1386 description in varchar2,
1387 notification_preference in varchar2,
1388 email_address in varchar2,
1389 fax in varchar2,
1390 status in varchar2,
1391 expiration_date in date,
1392 start_date in date,
1393 parent_orig_system in varchar2,
1394 parent_orig_system_id in number,
1395 owner_tag in varchar2,
1396 last_update_date in date,
1397 last_updated_by in number,
1398 creation_date in date,
1399 created_by in number,
1400 last_update_login in number)
1401 is
1402 nlang varchar2(30);
1403 nterr varchar2(30);
1404 l_partitionID number;
1405 l_partitionName varchar2(30);
1406 l_origSys VARCHAR2(30);
1407
1408 l_count number;
1409 l_creatby number;
1410 l_creatdt date;
1411 l_lastupdby number;
1412 l_lastupddt date;
1413 l_lastupdlog number;
1414
1415 l_ntfPref varchar2(8);
1416
1417 begin
1418
1419 -- [Name Validation]
1420 -- If concat name is passed, check to make sure it is valid.
1421 --
1422 if ( instr(name, ':') > 0 ) then
1423 if ( (orig_system||':'||orig_system_id ) <> name) then
1424 WF_CORE.Token('NAME', name);
1425 WF_CORE.Token('ORIG_SYSTEM', orig_system);
1426 WF_CORE.Token('ORIG_SYS_ID', orig_system_id);
1427 WF_CORE.Raise('WF_INVAL_CONCAT_NAME');
1428
1429 end if;
1430
1431 end if;
1432
1433 --
1434 -- Make sure no '#' or '/' exist in name.
1435 --
1436 /* Bug 2779747
1437 if ( (instr(name, '/') > 0) or (instr(name, '#') > 0) ) then
1438 WF_CORE.Token('ROLENAME', name);
1439 WF_CORE.Raise('WF_INVALID_ROLE');
1440 */
1441
1442 --
1443 -- Make sure the length of the name is <= 320
1444 --
1445 if ( lengthb(name) > 320 ) then
1446 WF_CORE.Token('NAME', name);
1447 WF_CORE.Token('LENGTH', 320);
1448 WF_CORE.Raise('WF_ROLENAME_TOO_LONG');
1449
1450 end if;
1451
1452 --
1453 -- [Status Validation]
1454 --
1455 if ( CreateUser.status not in ('ACTIVE', 'TMPLEAVE', 'EXTLEAVE',
1456 'INACTIVE') ) then
1457
1458 WF_CORE.Token('STATUS', CreateUser.status);
1459 WF_CORE.Raise('WF_INVALID_ROLE_STATUS');
1460
1461 end if;
1462
1463 --
1464 -- [Notification_Preference Validation]
1465 --
1466 -- Bug 2779747
1467 if (CreateUser.notification_preference is NULL) then
1468 if (CreateUser.email_address is NULL) then
1469 l_ntfPref := 'QUERY';
1470 else
1471 l_ntfPref := 'MAILHTML';
1472 end if;
1473 elsif (CreateUser.notification_preference not in ('MAILHTML','MAILHTM2',
1474 'MAILATTH', 'SUMMARY', 'SUMHTML', 'QUERY', 'MAILTEXT','DISABLED')) then
1475 WF_CORE.Token('NTF_PREF', CreateUser.notification_preference);
1476 WF_CORE.Raise('WF_INVALID_NTF_PREF');
1477 else
1478 l_ntfPref := CreateUser.notification_preference;
1479 end if;
1480
1481 --
1482 -- Resolve Territory and Language
1483 --
1484 if (language is null or territory is null) then
1485 begin
1486 select nls_territory, nls_language into nterr, nlang
1487 from WF_LANGUAGES
1488 where code = userenv('LANG');
1489 exception
1490 when NO_DATA_FOUND then
1491 wf_core.raise('WF_NO_LANG_TERR');
1492 end;
1493
1494 end if;
1495
1496 l_origSys := UPPER(CreateUser.orig_system);
1497 --
1498 -- Set the partition for the orig_system
1499 --
1500 AssignPartition(l_origSys, l_partitionID, l_partitionName);
1501
1502 --<rwunderl:4115907> Check to make sure the same name does not exist under
1503 --a different orig_system_id within this orig_system. We may want to
1504 --change the indexes later to control this but given the data-model change
1505 --on the stage tables, we are just performing the check.
1506 if (instr(CreateUser.name, ':') < 1) then
1507 select count(*)
1508 into l_count
1509 from WF_LOCAL_ROLES
1510 where NAME = CreateUser.name
1511 and PARTITION_ID = l_partitionID
1512 and ORIG_SYSTEM = l_origSys
1513 and ORIG_SYSTEM_ID <> CreateUser.orig_system_id;
1514 end if;
1515
1516 if (l_count > 0) then
1517 WF_CORE.Token('NAME', CreateUser.name);
1518 WF_CORE.Token('ORIG_SYSTEM', l_origSys);
1519 WF_CORE.Raise('WFDS_DUPLICATE_NAME');
1520 end if;
1521 --
1522 -- Evaluating the WHO columns in case they are not passed
1523
1524 l_creatby := nvl(Createuser.created_by,WFA_SEC.USER_ID);
1525 l_creatdt := nvl(CreateUser.creation_date, SYSDATE);
1526 l_lastupdby := nvl(CreateUser.last_updated_by, WFA_SEC.USER_ID);
1527 l_lastupddt := nvl(CreateUser.last_update_date, SYSDATE);
1528 l_lastupdlog:= nvl(CreateUser.last_update_login, WFA_SEC.LOGIN_ID);
1529
1530
1531 -- Insert WF_LOCAL_ROLES with USER_FLAG = 'Y'
1532 --
1533 insert into WF_LOCAL_ROLES
1534 (name,
1535 display_name,
1536 description,
1537 notification_preference,
1538 language,
1539 territory,
1540 email_address,
1541 fax,
1542 status,
1543 expiration_date,
1544 orig_system,
1545 orig_system_id,
1546 start_date,
1547 user_flag,
1548 partition_id,
1549 parent_orig_system,
1550 parent_orig_system_id,
1551 owner_tag,
1552 last_update_date,
1553 last_updated_by,
1554 creation_date,
1555 created_by,
1556 last_update_login)
1557 values
1558 ( nvl(CreateUser.name, l_origSys || ':' ||
1559 CreateUser.orig_system_id),
1560 nvl(CreateUser.display_name, (nvl(CreateUser.name,
1561 l_origSys || ':' ||
1562 CreateUser.orig_system_id))),
1563 CreateUser.description,
1564 l_ntfPref,
1565 nvl(CreateUser.language, nlang),
1566 nvl(CreateUser.territory, nterr),
1567 CreateUser.email_address,
1568 CreateUser.fax,
1569 CreateUser.status,
1570 CreateUser.expiration_date,
1571 l_origSys,
1572 CreateUser.orig_system_id,
1573 CreateUser.start_date,
1574 'Y',
1575 l_partitionID,
1576 nvl(CreateUser.parent_orig_system, CreateUser.orig_system),
1577 nvl(CreateUser.parent_orig_system_id, CreateUser.orig_system_id),
1578 CreateUser.owner_tag,
1579 l_lastupddt,
1580 l_lastupdby,
1581 l_creatdt,
1582 l_creatby,
1583 l_lastupdlog );
1584
1585 --If MLS language support is enabled for this orig_system
1586 --then sync the data to _TL table aswell.
1587 if ((WF_DIRECTORY.IsMLSEnabled(l_origSys) = TRUE) AND
1588 (userenv('LANG') <> 'US')) then
1589 insert into WF_LOCAL_ROLES_TL
1590 ( NAME,
1591 ORIG_SYSTEM,
1592 ORIG_SYSTEM_ID,
1593 PARTITION_ID,
1594 LANGUAGE,
1595 DISPLAY_NAME,
1596 DESCRIPTION,
1597 OWNER_TAG,
1598 LAST_UPDATE_DATE,
1599 LAST_UPDATED_BY,
1600 CREATION_DATE,
1601 CREATED_BY,
1602 LAST_UPDATE_LOGIN)
1603 values
1604 ( nvl(CreateUser.name, l_origSys || ':' || CreateUser.orig_system_id),
1605 l_origSys,
1606 CreateUser.orig_system_id,
1607 l_partitionID,
1608 userenv('LANG'),
1609 nvl(CreateUser.display_name, (nvl(CreateUser.name,
1610 l_origSys || ':' ||
1611 CreateUser.orig_system_id))),
1612 CreateUser.description,
1613 CreateUser.owner_tag,
1614 l_lastupddt,
1615 l_lastupdby,
1616 l_creatdt,
1617 l_creatby,
1618 l_lastupdlog);
1619 end if;
1620
1621
1622
1623 --All Users belong to their own user/role relationship.
1624 begin
1625 CreateUserRole(user_name=>CreateUser.name,
1626 role_name=>CreateUser.name,
1627 user_orig_system=>l_origSys,
1628 user_orig_system_id=>CreateUser.orig_system_id,
1629 role_orig_system=>l_origSys,
1630 role_orig_system_id=>CreateUser.orig_system_id,
1631 start_date=>CreateUser.start_date,
1632 end_date=>CreateUser.expiration_date,
1633 validateUserRole=>FALSE,
1634 parent_orig_system=>CreateUser.parent_orig_system,
1635 parent_orig_system_id=>CreateUser.parent_orig_system_id,
1636 owner_tag=>CreateUser.Owner_Tag,
1637 last_update_date=>CreateUser.last_update_date,
1638 last_updated_by=>CreateUser.last_updated_by,
1639 creation_date=>CreateUser.creation_date,
1640 created_by=>CreateUser.created_by,
1641 last_update_login=>CreateUser.last_update_login);
1642
1643 exception
1644 when OTHERS then
1645 if (WF_CORE.error_name = 'WF_DUP_USER_ROLE') then
1646 SetUserRoleAttr(user_name=>CreateUser.name,
1647 role_name=>CreateUser.name,
1648 user_orig_system=>l_origSys,
1649 user_orig_system_id=>CreateUser.orig_system_id,
1650 role_orig_system=>l_origSys,
1651 role_orig_system_id=>CreateUser.orig_system_id,
1652 start_date=>CreateUser.start_date,
1653 end_date=>CreateUser.expiration_date,
1654 overWrite=>TRUE,
1655 parent_orig_system=>CreateUser.parent_orig_system,
1656 parent_orig_system_id=>
1657 CreateUser.parent_orig_system_id,
1658 owner_tag=>CreateUser.owner_tag,
1659 last_update_date=>CreateUser.last_update_date,
1660 last_updated_by=>CreateUser.last_updated_by,
1661 last_update_login=>CreateUser.last_update_login,
1662 created_by=>CreateUser.created_by,
1663 creation_date=>CreateUser.creation_date);
1664
1665 else
1666 raise;
1667
1668 end if;
1669 end;
1670 exception
1671 when DUP_VAL_ON_INDEX then
1672 WF_CORE.Token('DISPNAME', CreateUser.display_name);
1673 WF_CORE.Token('USERNAME', nvl(CreateUser.name,
1674 l_origSys || ':' ||
1675 CreateUser.orig_system_id));
1676 WF_CORE.Raise('WF_DUP_USER');
1677
1678 when others then
1679 wf_core.context('Wf_Directory', 'CreateUser', CreateUser.Name,
1680 l_origSys, CreateUser.orig_system_id );
1681 raise;
1682 end CreateUser;
1683
1684
1685 --
1686 -- CreateAdHocUser
1687 -- Create an ad hoc user given a user name, display name, etc.
1688 -- IN
1689 -- name - User name
1690 -- display_name - User display name
1691 -- description -
1692 -- notification_preference -
1693 -- language -
1694 -- territory -
1695 -- email_address -
1696 -- fax -
1697 -- status -
1698 -- expiration_date - NULL expiration date means no expiration
1699 -- OUT
1700 --
1701 procedure CreateAdHocUser(name in out NOCOPY varchar2,
1702 display_name in out NOCOPY varchar2,
1703 language in varchar2,
1704 territory in varchar2,
1705 description in varchar2,
1706 notification_preference in varchar2,
1707 email_address in varchar2,
1708 fax in varchar2,
1709 status in varchar2,
1710 expiration_date in date,
1711 parent_orig_system in varchar2,
1712 parent_orig_system_id in number)
1713 is
1714 role_id pls_integer;
1715 d1 pls_integer;
1716
1717 begin
1718 --
1719 -- Check if user name and display name exists in wf_users
1720 --
1721 if (name is not null and display_name is not null) then
1722
1723 /* GK: The display name does not have to be unique
1724
1725 select count(1) into d1
1726 from wf_users u
1727 where u.name = CreateAdHocUser.name
1728 or u.display_name = CreateAdHocUser.display_name;
1729 if (d1 > 0) then
1730 wf_core.token('USERNAME', CreateAdHocUser.name);
1731 wf_core.token('DISPNAME', CreateAdHocUser.display_name);
1732 wf_core.raise('WF_DUP_USER');
1733 end if;
1734
1735 */
1736
1737 NULL;
1738 elsif (name is null ) then
1739 begin
1740 select to_char(WF_ADHOC_ROLE_S.NEXTVAL)
1741 into role_id
1742 from SYS.DUAL;
1743 exception
1744 when others then
1745 raise;
1746 end;
1747
1748 CreateAdHocUser.name := '~WF_ADHOC-' || role_id;
1749 if display_name is null then
1750 CreateAdHocUser.display_name := CreateAdHocUser.name;
1751 end if;
1752 end if;
1753
1754 CreateUser(name=>CreateAdHocUser.name,
1755 display_name=>CreateAdHocUser.display_name,
1756 orig_system=>'WF_LOCAL_USERS',
1757 orig_system_id=>0,
1758 language=>CreateAdHocUser.language,
1759 territory=>CreateAdHocUser.territory,
1760 description=>CreateAdHocUser.description,
1761 notification_preference=>CreateAdHocUser.notification_preference,
1762 email_address=>CreateAdHocUser.email_address,
1763 fax=>CreateAdHocUser.fax,
1764 status=>CreateAdHocUser.status,
1765 expiration_date=>CreateAdHocUser.expiration_date,
1766 parent_orig_system=>CreateAdhocUser.parent_orig_system,
1767 parent_orig_system_id=>CreateAdhocUser.parent_orig_system_id);
1768
1769
1770 exception
1771 when others then
1772 wf_core.context('Wf_Directory', 'CreateAdHocUser');
1773 raise;
1774 end CreateAdHocUser;
1775
1776
1777 --
1778 -- CreateRole (PRIVATE)
1779 -- Create a role given a specific name
1780 -- IN
1781 -- role_name -
1782 -- role_display_name -
1783 -- role_description -
1784 -- notification_preference -
1785 -- language -
1786 -- territory -
1787 -- email_address -
1788 -- fax -
1789 -- status -
1790 -- start_date - defaults to sysdate
1791 -- expiration_date - Null means no expiration date
1792 -- OUT
1793 --
1794 procedure CreateRole( role_name in varchar2,
1795 role_display_name in varchar2,
1796 orig_system in varchar2,
1797 orig_system_id in number,
1798 language in varchar2,
1799 territory in varchar2,
1800 role_description in varchar2,
1801 notification_preference in varchar2,
1802 email_address in varchar2,
1803 fax in varchar2,
1804 status in varchar2,
1805 expiration_date in date,
1806 start_date in date,
1807 parent_orig_system in varchar2,
1808 parent_orig_system_id in number,
1809 owner_tag in varchar2,
1810 last_update_date in date,
1811 last_updated_by in number,
1812 creation_date in date,
1813 created_by in number,
1814 last_update_login in number )
1815
1816 is
1817 nlang varchar2(30);
1818 nterr varchar2(30);
1819 l_partitionID NUMBER;
1820 l_partitionName VARCHAR2(30);
1821 l_origSys VARCHAR2(30);
1822
1823 TYPE numTAB is table of number index by binary_integer;
1824 l_origSysIDTAB numTAB;
1825
1826 l_creatby number;
1827 l_creatdt date;
1828 l_lastupdby number;
1829 l_lastupddt date;
1830 l_lastupdlog number;
1831
1832 l_ntfPref varchar2(8);
1833 begin
1834
1835 -- These validations are also performed in CreateUser. Should the
1836 -- validations become resource intensive such as accessing DB, we
1837 -- might want to have a private variable to indicate if this is
1838 -- being called by CreateUser.
1839
1840 -- [Name Validation]
1841 -- If concat role_name is passed, check to make sure it is valid.
1842 --
1843 if ( instr(role_name, ':') > 0 ) then
1844 if ( (orig_system||':'||orig_system_id ) <> role_name) then
1845 WF_CORE.Token('NAME', role_name);
1846 WF_CORE.Token('ORIG_SYSTEM', orig_system);
1847 WF_CORE.Token('ORIG_SYS_ID', orig_system_id);
1848 WF_CORE.Raise('WF_INVAL_CONCAT_NAME');
1849
1850 end if;
1851
1852 end if;
1853
1854 --
1855 -- Make sure no '#' or '/' exist in role_name.
1856 --
1857 /* Bug 2779747
1858 if ( (instr(role_name, '/') > 0) or (instr(role_name, '#') > 0) ) then
1859 WF_CORE.Token('ROLENAME', role_name);
1860 WF_CORE.Raise('WF_INVALID_ROLE');
1861 */
1862
1863 --
1864 -- Make sure the length of the role_name is <= 320
1865 --
1866 if ( lengthb(role_name) > 320 ) then
1867 WF_CORE.Token('NAME', role_name);
1868 WF_CORE.Token('LENGTH', 320);
1869 WF_CORE.Raise('WF_ROLENAME_TOO_LONG');
1870
1871 end if;
1872
1873 --
1874 -- [Status Validation]
1875 --
1876 if ( CreateRole.status not in ('ACTIVE', 'TMPLEAVE', 'EXTLEAVE',
1877 'INACTIVE') ) then
1878
1879 WF_CORE.Token('STATUS', CreateRole.status);
1880 WF_CORE.Raise('WF_INVALID_ROLE_STATUS');
1881
1882 end if;
1883
1884 --
1885 -- [Notification_Preference Validation]
1886 --
1887 -- Bug 2779747
1888 if (CreateRole.notification_preference is NULL) then
1889 if (CreateRole.email_address is NULL) then
1890 l_ntfPref := 'QUERY';
1891 else
1892 l_ntfPref := 'MAILHTML';
1893 end if;
1894 elsif (CreateRole.notification_preference not in ('MAILHTML','MAILHTM2',
1895 'MAILATTH', 'SUMMARY', 'SUMHTML', 'QUERY', 'MAILTEXT','DISABLED')) then
1896 WF_CORE.Token('NTF_PREF', CreateRole.notification_preference);
1897 WF_CORE.Raise('WF_INVALID_NTF_PREF');
1898 else
1899 l_ntfPref := CreateRole.notification_preference;
1900 end if;
1901
1902 --
1903 -- Resolve Territory and Language
1904 --
1905 if (language is null or territory is null) then
1906 begin
1907 select nls_territory, nls_language into nterr, nlang
1908 from WF_LANGUAGES
1909 where code = userenv('LANG');
1910
1911 exception
1912 when NO_DATA_FOUND then
1913 wf_core.raise('WF_NO_LANG_TERR');
1914 end;
1915
1916 else
1917 nlang := CreateRole.language;
1918 nterr := CreateRole.territory;
1919
1920 end if;
1921
1922 l_origSys := UPPER(CreateRole.orig_system);
1923
1924 --
1925 -- Check the partition.
1926 --
1927 AssignPartition(l_origSys,l_PartitionID, l_PartitionName);
1928
1929 --<rwunderl:4115907> Check to make sure the same name does not exist under
1930 --a different orig_system_id within this orig_system. We may want to
1931 --change the indexes later to control this but given the data-model change
1932 --on the stage tables, we are just performing the check.
1933
1934 --We are also using bulk operation so we do not have to catch a
1935 --NO_DATA_FOUND exception which we expect this condition often.
1936 if (instr(CreateRole.role_name, ':') < 1) then
1937
1938 select ORIG_SYSTEM_ID
1939 bulk collect into l_origSysIDTAB
1940 from WF_LOCAL_ROLES
1941 where NAME = CreateRole.role_name
1942 and PARTITION_ID = l_partitionID
1943 and ORIG_SYSTEM = CreateRole.orig_system
1944 and ORIG_SYSTEM_ID <> CreateRole.orig_system_id
1945 and rownum < 2;
1946
1947 end if;
1948
1949 -- Evaluating the WHO columns in case they are not passed
1950
1951 l_creatby := nvl(CreateRole.created_by,WFA_SEC.USER_ID);
1952 l_creatdt := nvl(CreateRole.creation_date, SYSDATE);
1953 l_lastupdby := nvl(CreateRole.last_updated_by, WFA_SEC.USER_ID);
1954 l_lastupddt := nvl(CreateRole.last_update_date, SYSDATE);
1955 l_lastupdlog:= nvl(CreateRole.last_update_login, WFA_SEC.LOGIN_ID);
1956
1957 if (l_origSysIDTAB.COUNT > 0) then
1958 if (l_partitionID <> 2) then
1959 WF_CORE.Token('NAME', CreateRole.role_name);
1960 WF_CORE.Token('ORIG_SYSTEM', l_origSys);
1961 WF_CORE.Raise('WFDS_DUPLICATE_NAME');
1962 else
1963 --This is an FND_RESP which could be coming in from the loader using
1964 --afrole.lct, so we will go ahead and call the SetUserAttr() api
1965 --for the other orig_system_id.
1966 SetRoleAttr(role_name=>CreateRole.role_name,
1967 orig_system=>l_origSys,
1968 orig_system_id=>l_origSysIDTAB(1),
1969 display_name=>CreateRole.role_display_name,
1970 description=>CreateRole.role_description,
1971 notification_preference=>l_ntfPref,
1972 language=>CreateRole.language,
1973 territory=>CreateRole.territory,
1974 email_address=>CreateRole.email_address,
1975 fax=>CreateRole.fax,
1976 start_date=>CreateRole.start_date,
1977 expiration_date=>CreateRole.Expiration_date,
1978 status=>CreateRole.status,
1979 parent_orig_system=>CreateRole.parent_orig_system,
1980 parent_orig_system_id=>CreateRole.parent_orig_system_id,
1981 owner_tag=>CreateRole.owner_tag);
1982 end if;
1983 else
1984 --
1985 -- Insert WF_LOCAL_ROLES with USER_FLAG = 'N'
1986 --
1987
1988 insert into WF_LOCAL_ROLES
1989 (name,
1990 display_name,
1991 description,
1992 notification_preference,
1993 language,
1994 territory,
1995 email_address,
1996 fax,
1997 status,
1998 expiration_date,
1999 orig_system,
2000 orig_system_id,
2001 start_date,
2002 user_flag,
2003 partition_id,
2004 parent_orig_system,
2005 parent_orig_system_id,
2006 owner_tag,
2007 last_update_date,
2008 last_updated_by,
2009 creation_date,
2010 created_by,
2011 last_update_login)
2012 values
2013 (nvl(CreateRole.role_name, l_origSys || ':' ||
2014 CreateRole.orig_system_id),
2015 nvl(CreateRole.role_display_name, (nvl(CreateRole.role_name,
2016 l_origSys || ':' ||
2017 CreateRole.orig_system_id))),
2018 CreateRole.role_description,
2019 l_ntfPref,
2020 nvl(CreateRole.language, nlang),
2021 nvl(CreateRole.territory, nterr),
2022 CreateRole.email_address,
2023 CreateRole.fax,
2024 CreateRole.status,
2025 CreateRole.expiration_date,
2026 l_origSys,
2027 CreateRole.orig_system_id,
2028 CreateRole.start_date,
2029 'N',
2030 l_PartitionID,
2031 CreateRole.parent_orig_system,
2032 CreateRole.parent_orig_system_id,
2033 CreateRole.owner_tag,
2034 l_lastupddt,
2035 l_lastupdby,
2036 l_creatdt,
2037 l_creatby,
2038 l_lastupdlog );
2039
2040 if ((WF_DIRECTORY.IsMLSEnabled(l_origSys) = TRUE ) AND
2041 (userenv('LANG') <> 'US')) then
2042 --If the orig_system is MLS enabled then sync the dat into
2043 --the _TL table aswell.
2044 insert into WF_LOCAL_ROLES_TL
2045 ( NAME,
2046 ORIG_SYSTEM,
2047 ORIG_SYSTEM_ID,
2048 PARTITION_ID,
2049 LANGUAGE,
2050 DISPLAY_NAME,
2051 DESCRIPTION,
2052 OWNER_TAG,
2053 LAST_UPDATE_DATE,
2054 LAST_UPDATED_BY,
2055 CREATION_DATE,
2056 CREATED_BY,
2057 LAST_UPDATE_LOGIN)
2058 values
2059 (nvl(CreateRole.role_name, l_origSys || ':' ||
2060 CreateRole.orig_system_id),
2061 l_origSys,
2062 CreateRole.orig_system_id,
2063 l_partitionID,
2064 userenv('LANG'),
2065 nvl(CreateRole.role_display_name, (nvl(CreateRole.role_name,
2066 l_origSys || ':' ||
2067 CreateRole.orig_system_id))),
2068 CreateRole.role_description,
2069 CreateRole.owner_tag,
2070 l_lastupddt,
2071 l_lastupdby,
2072 l_creatdt,
2073 l_creatby,
2074 l_lastupdlog );
2075 end if;
2076 end if;
2077 exception
2078 when DUP_VAL_ON_INDEX then
2079 WF_CORE.Token('DISPNAME', CreateRole.role_display_name);
2080 WF_CORE.Token('ROLENAME', nvl(CreateRole.role_name,
2081 l_origSys || ':' ||
2082 CreateRole.orig_system_id));
2083 WF_CORE.Raise('WF_DUP_ROLE');
2084
2085 when others then
2086 wf_core.context('Wf_Directory', 'CreateRole', CreateRole.role_Name,
2087 l_origSys, CreateRole.orig_system_id);
2088 raise;
2089 end CreateRole;
2090
2091
2092 --
2093 -- CreateAdHocRole
2094 -- Create an ad hoc role given a specific name
2095 -- IN
2096 -- role_name -
2097 -- role_display_name -
2098 -- role_description -
2099 -- notification_preference -
2100 -- role_users - Comma or space delimited list
2101 -- language -
2102 -- territory -
2103 -- email_address -
2104 -- fax -
2105 -- status -
2106 -- expiration_date - Null means no expiration date
2107 -- OUT
2108 --
2109 procedure CreateAdHocRole(role_name in out NOCOPY varchar2,
2110 role_display_name in out NOCOPY varchar2,
2111 language in varchar2,
2112 territory in varchar2,
2113 role_description in varchar2,
2114 notification_preference in varchar2,
2115 role_users in varchar2,
2116 email_address in varchar2,
2117 fax in varchar2,
2118 status in varchar2,
2119 expiration_date in date,
2120 parent_orig_system in varchar2,
2121 parent_orig_system_id in number,
2122 owner_tag in varchar2)
2123 is
2124 l_users WF_DIRECTORY.UserTable;
2125
2126 begin
2127 --Convert the string to a proper user table.
2128 if (role_users is NOT NULL) then
2129 WF_DIRECTORY.string_to_userTable(role_users, l_users);
2130 end if;
2131
2132 --Pass the call over to the superceding procedure CreateAdHocRole2
2133 WF_DIRECTORY.CreateAdHocRole2(role_name=>CreateAdhocRole.role_name,
2134 role_display_name=>CreateAdhocRole.role_display_name,
2135 language=>CreateAdhocRole.language,
2136 territory=>CreateAdhocRole.Territory,
2137 role_description=>CreateAdhocRole.role_description,
2138 notification_preference=>CreateAdhocRole.notification_preference,
2139 role_users=>l_users,
2140 email_address=>CreateAdhocRole.email_address,
2141 fax=>CreateAdhocRole.fax,
2142 status=>CreateAdhocRole.status,
2143 expiration_date=>CreateAdhocRole.expiration_date,
2144 parent_orig_system=>CreateAdhocRole.parent_orig_system,
2145 parent_orig_system_id=>CreateAdhocRole.parent_orig_system_id,
2146 owner_tag=>CreateAdhocRole.owner_tag);
2147 exception
2148 when others then
2149 wf_core.context('Wf_Directory', 'CreateAdHocRole');
2150 raise;
2151 end CreateAdHocRole;
2152
2153 --
2154 -- CreateAdHocRole2
2155 -- Create an ad hoc role given a specific name
2156 -- IN
2157 -- role_name -
2158 -- role_display_name -
2159 -- role_description -
2160 -- notification_preference -
2161 -- role_users - WF_DIRECTORY.UserTable
2162 -- language -
2163 -- territory -
2164 -- email_address -
2165 -- fax -
2166 -- status -
2167 -- expiration_date - Null means no expiration date
2168 -- OUT
2169 --
2170 procedure CreateAdHocRole2(role_name in out NOCOPY varchar2,
2171 role_display_name in out NOCOPY varchar2,
2172 language in varchar2,
2173 territory in varchar2,
2174 role_description in varchar2,
2175 notification_preference in varchar2,
2176 role_users in WF_DIRECTORY.UserTable,
2177 email_address in varchar2,
2178 fax in varchar2,
2179 status in varchar2,
2180 expiration_date in date,
2181 parent_orig_system in varchar2,
2182 parent_orig_system_id in number,
2183 owner_tag in varchar2)
2184 is
2185 role_id pls_integer;
2186 name varchar2(320);
2187 d1 pls_integer;
2188
2189 begin
2190 --
2191 -- Check if role name exists in wf_roles
2192 --
2193 if (role_name is not null and role_display_name is not null) then
2194 /* GK: The display name does not have to be unique
2195
2196 select count(1) into d1
2197 from wf_roles
2198 where name = CreateAdHocRole.role_name
2199 or display_name = CreateAdHocRole.role_display_name;
2200 if (d1 > 0) then
2201 wf_core.token('ROLENAME', CreateAdHocRole.role_name);
2202 wf_core.token('DISPNAME', CreateAdHocRole.role_display_name);
2203 wf_core.raise('WF_DUP_ROLE');
2204 end if;
2205
2206 */
2207
2208 NULL;
2209
2210 elsif role_name is null then
2211 --
2212 -- Create role name if not exist
2213 --
2214 begin
2215 select to_char(WF_ADHOC_ROLE_S.NEXTVAL)
2216 into role_id
2217 from SYS.DUAL;
2218 exception
2219 when others then
2220 raise;
2221 end;
2222
2223 role_name := '~WF_ADHOC-' || role_id;
2224 if role_display_name is null then
2225 role_display_name := role_name;
2226 end if;
2227 end if;
2228
2229 CreateRole( role_name=>CreateAdHocRole2.role_name,
2230 role_display_name=>CreateAdHocRole2.role_display_name,
2231 orig_system=>'WF_LOCAL_ROLES',
2232 orig_system_id=>0,
2233 language=>CreateAdHocRole2.language,
2234 territory=>CreateAdHocRole2.territory,
2235 role_description=>CreateAdHocRole2.role_description,
2236 notification_preference=>CreateAdHocRole2.notification_preference,
2237 email_address=>CreateAdHocRole2.email_address,
2238 fax=>CreateAdHocRole2.fax,
2239 status=>CreateAdHocRole2.status,
2240 expiration_date=>CreateAdHocRole2.expiration_date,
2241 parent_orig_system=>CreateAdHocRole2.parent_orig_system,
2242 parent_orig_system_id=>CreateAdHocRole2.parent_orig_system_id,
2243 owner_tag=>CreateAdHocRole2.owner_tag );
2244
2245
2246 --
2247 -- Add Role Users
2248 --
2249 if (role_users.COUNT > 0) then
2250 AddUsersToAdHocRole2(CreateAdHocRole2.role_name,
2251 CreateAdHocRole2.role_users);
2252 end if;
2253
2254
2255 exception
2256 when others then
2257 wf_core.context('Wf_Directory', 'CreateAdHocRole2');
2258 raise;
2259 end CreateAdHocRole2;
2260
2261 --
2262 -- CreateUserRole (PRIVATE)
2263 -- Create a user to role relationship.
2264 -- IN
2265 -- user_name -
2266 -- role_name -
2267 -- start_date -
2268 -- expiration_date -
2269 -- user_orig_system -
2270 -- user_orig_system_id -
2271 -- role_orig_system -
2272 -- role_orig_system_id -
2273 -- validateUserRole -
2274 -- start_date -
2275 -- end_date -
2276 -- created_by -
2277 -- creation_date -
2278 -- last_updated_by -
2279 -- last_update_date -
2280 -- last_update_login -
2281 -- assignment_type -
2282 -- assignment_type -
2283 -- parent_orig_system -,
2284 -- parent_orig_system_id -
2285 -- owner_tag -
2286 -- assignment_reason -
2287 --
2288 procedure CreateUserRole ( user_name in varchar2,
2289 role_name in varchar2,
2290 user_orig_system in varchar2,
2291 user_orig_system_id in number,
2292 role_orig_system in varchar2,
2293 role_orig_system_id in number,
2294 validateUserRole in boolean,
2295 start_date in date,
2296 end_date in date,
2297 created_by in number,
2298 creation_date in date,
2299 last_updated_by in number,
2300 last_update_date in date,
2301 last_update_login in number,
2302 assignment_type in varchar2,
2303 parent_orig_system in varchar2,
2304 parent_orig_system_id in number,
2305 owner_tag in varchar2,
2306 assignment_reason in varchar2,
2307 eventParams in wf_parameter_list_t )
2308
2309 is
2310 l_count PLS_INTEGER;
2311 l_upartID number;
2312 l_rpartID number;
2313 l_partitionID number;
2314 l_partitionName varchar2(30);
2315 l_uorigSys VARCHAR2(30);
2316 l_uorigSysID NUMBER;
2317 l_rorigSys VARCHAR2(30);
2318 l_rorigSysID NUMBER;
2319 l_validateUserRole BOOLEAN;
2320 l_params WF_PARAMETER_LIST_T;
2321 l_affectedRow rowid;
2322 l_userStartDate date;
2323 l_userExpDate date;
2324 l_roleStartDate date;
2325 l_roleExpDate date;
2326 l_effStartDate date;
2327 l_effEndDate date;
2328
2329 l_creatdt date;
2330 l_lastupddt date;
2331 l_lastupdby number;
2332 l_creatby number;
2333 l_lastupdlog number;
2334 event wf_event_t;
2335 result varchar2(10);
2336 sub_id raw(20);
2337 begin
2338 if ((user_orig_system is NULL) or (user_orig_system_id is NULL) or
2339 (role_orig_system is NULL) or (role_orig_system_id is NULL)) then
2340 --We need to validate by USER_NAME and ROLE_NAME to retreive the origSys
2341 --info.
2342 --Checking the user.
2343 begin
2344 SELECT ORIG_SYSTEM, ORIG_SYSTEM_ID,
2345 START_DATE, EXPIRATION_DATE,PARTITION_ID
2346 INTO l_uorigSys, l_uorigSysID,
2347 l_userStartDate, l_userExpDate, l_upartID
2348 FROM WF_LOCAL_ROLES
2349 WHERE NAME = CreateUserRole.USER_NAME
2350 AND ROWNUM < 2;
2351 exception
2352 when NO_DATA_FOUND then
2353 WF_CORE.Token('NAME', CreateUserRole.user_name);
2354 WF_CORE.Token('ORIG_SYSTEM', 'NULL');
2355 WF_CORE.Token('ORIG_SYSTEM_ID', 'NULL');
2356 WF_CORE.Raise('WF_NO_USER');
2357 end;
2358
2359 --Checking the Role.
2360 begin
2361 SELECT ORIG_SYSTEM, ORIG_SYSTEM_ID,
2362 START_DATE, EXPIRATION_DATE, PARTITION_ID
2363 INTO l_rorigSys, l_rorigSysID,
2364 l_roleStartDate, l_roleExpDate,l_rpartID
2365 FROM WF_LOCAL_ROLES
2366 WHERE NAME = CreateUserRole.ROLE_NAME
2367 AND ROWNUM < 2;
2368 exception
2369 when NO_DATA_FOUND then
2370 WF_CORE.Token('NAME', CreateUserRole.role_name);
2371 WF_CORE.Token('ORIG_SYSTEM', 'NULL');
2372 WF_CORE.Token('ORIG_SYSTEM_ID', 'NULL');
2373 WF_CORE.Raise('WF_NO_ROLE');
2374 end;
2375 else
2376 l_validateUserRole := validateUserRole;
2377 l_uorigSys := UPPER(CreateUserRole.user_orig_system);
2378 l_uorigSysID := CreateUserRole.user_orig_system_id;
2379 l_rorigSys := UPPER(CreateUserRole.role_orig_system);
2380 l_rorigSysID := CreateUserRole.role_orig_system_id;
2381
2382 end if;
2383
2384 --
2385 -- Confirm that the User and Role actually exist. We also need to make
2386 -- sure the user and role start/end dates are recorded.
2387
2388 --
2389 -- Removed the requirement for the user_name to be an actual user per Kevin
2390 -- and Mark for JTF team.
2391 if (l_validateUserRole) then
2392 begin
2393 if (l_upartID is null) then
2394 AssignPartition(l_uorigSys,l_upartID, l_partitionName);
2395 end if;
2396 SELECT start_date, expiration_date
2397 into l_userStartDate, l_userExpDate
2398 FROM WF_LOCAL_ROLES
2399 WHERE NAME = CreateUserRole.user_name
2400 AND ORIG_SYSTEM = l_uorigSys
2401 AND ORIG_SYSTEM_ID = l_uorigSysID
2402 AND PARTITION_ID = l_upartID;
2403 exception
2404 when NO_DATA_FOUND then
2405 WF_CORE.Token('NAME', CreateUserRole.user_name);
2406 WF_CORE.Token('ORIG_SYSTEM', l_uorigSys);
2407 WF_CORE.Token('ORIG_SYSTEM_ID', l_uorigSysID);
2408 WF_CORE.Raise('WF_NO_USER');
2409 end;
2410
2411 begin
2412 if (l_rpartID is null) then
2413 AssignPartition(l_rorigSys,l_rpartID, l_partitionName);
2414 end if;
2415
2416 SELECT start_date, expiration_date
2417 INTO l_roleStartDate, l_roleExpDate
2418 FROM WF_LOCAL_ROLES
2419 WHERE NAME = CreateUserRole.role_name
2420 AND ORIG_SYSTEM = l_rorigSys
2421 AND ORIG_SYSTEM_ID = l_rorigSysID
2422 AND PARTITION_ID = l_rpartID;
2423 exception
2424 when NO_DATA_FOUND then
2425 WF_CORE.Token('NAME', CreateUserRole.role_name);
2426 WF_CORE.Token('ORIG_SYSTEM', l_rorigSys);
2427 WF_CORE.Token('ORIG_SYSTEM_ID', l_rorigSysID);
2428 WF_CORE.Raise('WF_NO_ROLE');
2429 end;
2430 elsif (CreateUserRole.user_name = CreateUserRole.role_name) then
2431 --If this is a self reference we can set the user and role date values to
2432 --the same as the start/end of the user/role relationship as they are the
2433 --same.
2434 l_userStartDate := CreateUserRole.start_date;
2435 l_userExpDate := CreateUserRole.end_date;
2436 l_roleStartDate := CreateUserRole.start_date;
2437 l_roleExpDate := CreateUserRole.end_date;
2438 end if;
2439
2440 --
2441 -- Set the partition for the orig_system of the role_name
2442 --
2443 if (l_rpartID is null) then
2444 AssignPartition(l_rorigSys,
2445 l_rpartID, l_partitionName);
2446 end if;
2447
2448 -- Determine the effective dates for the user/role
2449 WF_ROLE_HIERARCHY.Calculate_Effective_Dates
2450 ( CreateUserRole.start_date,
2451 CreateUserRole.end_date,
2452 l_userStartDate,
2453 l_userExpDate,
2454 l_roleStartDate,
2455 l_roleExpDate,
2456 null,
2457 null,
2458 l_effStartDate,
2459 l_effEndDate);
2460
2461 -- Evaluate the WHO columns in case they are not passed
2462
2463 l_creatby := nvl(CreateUserRole.created_by,WFA_SEC.USER_ID);
2464 l_creatdt := nvl(CreateUserRole.creation_date, SYSDATE);
2465 l_lastupdby := nvl(CreateUserRole.last_updated_by, WFA_SEC.USER_ID);
2466 l_lastupddt := nvl(CreateUserRole.last_update_date, SYSDATE);
2467 l_lastupdlog:= nvl(CreateUserRole.last_update_login, WFA_SEC.LOGIN_ID);
2468
2469 -- Insert
2470 begin
2471 insert into WF_LOCAL_USER_ROLES
2472 ( USER_NAME,
2473 ROLE_NAME,
2474 USER_ORIG_SYSTEM,
2475 USER_ORIG_SYSTEM_ID,
2476 ROLE_ORIG_SYSTEM,
2477 ROLE_ORIG_SYSTEM_ID,
2478 START_DATE,
2479 EXPIRATION_DATE,
2480 USER_START_DATE,
2481 USER_END_DATE,
2482 ROLE_START_DATE,
2483 ROLE_END_DATE,
2484 EFFECTIVE_START_DATE,
2485 EFFECTIVE_END_DATE,
2486 PARTITION_ID,
2487 PARENT_ORIG_SYSTEM,
2488 PARENT_ORIG_SYSTEM_ID,
2489 ASSIGNMENT_TYPE,
2490 OWNER_TAG,
2491 LAST_UPDATE_DATE,
2492 LAST_UPDATED_BY,
2493 CREATION_DATE,
2494 CREATED_BY,
2495 LAST_UPDATE_LOGIN,
2496 ASSIGNMENT_REASON
2497 )
2498 values
2499 (
2500 CreateUserRole.user_name,
2501 CreateUserRole.role_name,
2502 l_uorigSys,
2503 l_uorigSysID,
2504 l_rorigSys,
2505 l_rorigSysID,
2506 trunc(CreateUserRole.start_date),
2507 trunc(CreateUserRole.end_date),
2508 l_userStartDate,
2509 l_userExpDate,
2510 l_roleStartDate,
2511 l_roleExpDate,
2512 l_effStartDate,
2513 l_effEndDate,
2514 l_rpartID,
2515 nvl(CreateUserRole.parent_orig_system,
2516 CreateUserRole.role_orig_system),
2517 nvl(CreateUserRole.parent_orig_system_id,
2518 CreateUserRole.role_orig_system_id),
2519 CreateUserRole.assignment_type,
2520 CreateUserRole.owner_tag,
2521 l_lastupddt,
2522 l_lastupdby,
2523 l_creatdt,
2524 l_creatby,
2525 l_lastupdlog,
2526 CreateUserRole.assignment_reason
2527 ) returning rowid into l_affectedRow;
2528
2529 --We were able to insert the record, so we will raise the created event
2530 --Build parameter list.
2531 WF_EVENT.AddParameterToList('ROWID', ROWIDTOCHAR(l_affectedRow), l_params);
2532 WF_EVENT.AddParameterToList('USER_NAME', CreateUserRole.user_name, l_params);
2533 WF_EVENT.AddParameterToList('ROLE_NAME', CreateUserRole.role_name, l_params);
2534 WF_EVENT.AddParameterToList('USER_ORIG_SYSTEM', l_uorigSys, l_params);
2535 WF_EVENT.AddParameterToList('USER_ORIG_SYSTEM_ID', l_uorigSysID, l_params);
2536 WF_EVENT.AddParameterToList('ROLE_ORIG_SYSTEM', l_rorigSys, l_params);
2537 WF_EVENT.AddParameterToList('ROLE_ORIG_SYSTEM_ID', l_rorigSysID, l_params);
2538 WF_EVENT.AddParameterToList('START_DATE',
2539 to_char(trunc(CreateUserRole.start_date),
2540 WF_CORE.Canonical_Date_Mask),
2541 l_params);
2542 WF_EVENT.AddParameterToList('END_DATE',
2543 to_char(trunc(CreateUserRole.end_date),
2544 WF_CORE.Canonical_Date_Mask),
2545 l_params);
2546
2547 WF_EVENT.AddParameterToList('CREATED_BY',
2548 to_char(CreateUserRole.created_by,
2549 WF_CORE.canonical_number_mask), l_params);
2550
2551 WF_EVENT.AddParameterToList('CREATION_DATE',
2552 to_char(CreateUserRole.creation_date,
2553 WF_CORE.canonical_date_mask), l_params);
2554
2555 WF_EVENT.AddParameterToList('LAST_UPDATED_BY',
2556 to_char(CreateUserRole.last_updated_by,
2557 WF_CORE.canonical_number_mask), l_params);
2558 WF_EVENT.AddParameterToList('LAST_UPDATE_DATE',
2559 to_char(CreateUserRole.last_update_date,
2560 WF_CORE.canonical_date_mask), l_params);
2561 WF_EVENT.AddParameterToList('LAST_UPDATE_LOGIN',
2562 to_char(CreateUserRole.last_update_login,
2563 WF_CORE.canonical_number_mask), l_params);
2564 WF_EVENT.AddParameterToList('ASSIGNMENT_TYPE',
2565 CreateUserRole.assignment_type, l_params);
2566 WF_EVENT.AddParameterToList('PARENT_ORIG_SYSTEM',
2567 CreateUserRole.parent_orig_system, l_params);
2568 WF_EVENT.AddParameterToList('PARENT_ORIG_SYSTEM_ID',
2569 to_char(CreateUserRole.parent_orig_system_id,
2570 WF_CORE.canonical_number_mask), l_params);
2571 WF_EVENT.AddParameterToList('PARTITION_ID', to_char(l_partitionID,
2572 WF_CORE.canonical_number_mask),l_params);
2573 WF_EVENT.AddParameterToList('ASSIGNMENT_REASON',
2574 CreateUserRole.assignment_reason, l_params);
2575 if (eventParams is not null and eventParams.count>0) then
2576 for i in eventParams.first..eventParams.last loop
2577 WF_EVENT.AddParameterToList(upper(eventParams(i).getName()),
2578 eventParams(i).getValue(),l_params);
2579 end loop;
2580 end if;
2581 --determine if BES is enabled
2582 if (wf_directory.System_Status()='DISABLED') then
2583 --Create the event that is to be raised
2584
2585 wf_event_t.initialize(event);
2586 event.Send_Date := sysdate;
2587 event.Event_Name := 'oracle.apps.fnd.wf.ds.userRole.created';
2588 event.Event_Key := l_uOrigSys||':'||
2589 to_char(l_uOrigSysId)||'|'||
2590 l_rOrigSys||':'||
2591 to_char(l_rOrigSysId)||'|'||
2592 to_char(SYSDATE, 'J:SSSSS');
2593 event.Parameter_List := l_params;
2594 sub_id:= hextoraw('1');
2595 result:= WF_ROLE_HIERARCHY.Cascade_RF(sub_id,event);
2596 if (result='SUCCESS') then
2597 result:= WF_ROLE_HIERARCHY.Aggregate_User_Roles_RF(sub_id,event);
2598 end if;
2599 else
2600 WF_EVENT.Raise(p_event_name=>'oracle.apps.fnd.wf.ds.userRole.created',
2601 p_event_key=> l_uOrigSys||':'||
2602 to_char(l_uOrigSysId)||'|'||
2603 l_rOrigSys||':'||
2604 to_char(l_rOrigSysId)||'|'||
2605 to_char(SYSDATE, 'J:SSSSS'),
2606 p_parameters=>l_params);
2607 end if;
2608
2609 exception
2610 when DUP_VAL_ON_INDEX then
2611 WF_CORE.Token('UNAME', CreateUserRole.user_name);
2612 WF_CORE.Token('RNAME', CreateUserRole.role_name);
2613 WF_CORE.Raise('WF_DUP_USER_ROLE');
2614
2615 when OTHERS then
2616 raise;
2617
2618 end;
2619
2620
2621
2622 exception
2623 when others then
2624 wf_core.context('Wf_Directory', 'CreateUserRole',
2625 user_name, role_name, l_uorigSys,
2626 to_char(nvl(user_orig_system_id, l_uorigSysID)),
2627 l_rorigSys, to_char(nvl(role_orig_system_id, l_rorigSysID)));
2628
2629 raise;
2630
2631 end CreateUserRole;
2632
2633
2634 --
2635 -- SetUserRoleAttr (PRIVATE)
2636 -- Update a user to role relationship.
2637 -- IN
2638 -- user_name -
2639 -- role_name -
2640 -- start_date -
2641 -- expiration_date -
2642 -- user_orig_system -
2643 -- user_orig_system_id -
2644 -- role_orig_system -
2645 -- role_orig_system_id -
2646 -- OverWrite -
2647 -- last_updated_by -
2648 -- last_update_date -
2649 -- last_update_login -
2650 -- assignment_type -
2651 -- parent_orig_system -
2652 -- parent_orig_system_id
2653 -- owner_tag
2654 -- last_update_date -
2655 -- last_updated_by -
2656 -- creation_date -
2657 -- created_by -
2658 -- last_update_login -
2659 -- assignment_reason -
2660 -- updateWho
2661 procedure SetUserRoleAttr ( user_name in varchar2,
2662 role_name in varchar2,
2663 start_date in date,
2664 end_date in date,
2665 user_orig_system in varchar2,
2666 user_orig_system_id in number,
2667 role_orig_system in varchar2,
2668 role_orig_system_id in number,
2669 OverWrite in boolean,
2670 last_updated_by in number,
2671 last_update_date in date,
2672 last_update_login in number,
2673 created_by in number,
2674 creation_date in date,
2675 assignment_type in varchar2,
2676 parent_orig_system in varchar2,
2677 parent_orig_system_id in number,
2678 owner_tag in varchar2,
2679 assignment_reason in varchar2,
2680 updateWho in BOOLEAN,
2681 eventParams in wf_parameter_list_t) is
2682
2683 l_uorigSys VARCHAR2(30) := UPPER(user_orig_system);
2684 l_uorigSysID NUMBER := UPPER(user_orig_system_id);
2685 l_rorigSys VARCHAR2(30) := UPPER(role_orig_system);
2686 l_rorigSysID NUMBER := UPPER(role_orig_system_id);
2687 l_porigSys VARCHAR2(30) := UPPER(nvl(parent_orig_system,
2688 role_orig_system));
2689 l_porigSysID NUMBER := UPPER(nvl(parent_orig_system_id,
2690 role_orig_system_id));
2691 l_params WF_PARAMETER_LIST_T;
2692 l_affectedRow rowid;
2693
2694 l_lastupdby NUMBER;
2695 l_lastupddt DATE;
2696 l_lastupdlog NUMBER;
2697 l_oldstartdate date;
2698 l_oldenddate date;
2699 l_UpdateWho BOOLEAN := nvl(updateWho,TRUE);
2700 l_partitionID number;
2701 l_partitionName varchar2(30);
2702 event wf_event_t;
2703 result varchar2(10);
2704 sub_id raw(20);
2705 begin
2706 --<rwunderl:2823630> Lookup origSys info if not provided.
2707 if ((SetUserRoleAttr.user_orig_system is NULL) or
2708 (SetUserRoleAttr.user_orig_system_id is NULL)) then
2709 --Checking the user.
2710 begin
2711 SELECT ORIG_SYSTEM, ORIG_SYSTEM_ID
2712 INTO l_uorigSys, l_uorigSysID
2713 FROM WF_LOCAL_ROLES
2714 WHERE NAME = SetUserRoleAttr.user_name
2715 and rownum < 2;
2716
2717 exception
2718 when NO_DATA_FOUND then
2719 WF_CORE.Token('NAME', SetUserRoleAttr.user_name);
2720 WF_CORE.Token('ORIG_SYSTEM', 'NULL');
2721 WF_CORE.Token('ORIG_SYSTEM_ID', 'NULL');
2722 WF_CORE.Raise('WF_NO_USER');
2723 end;
2724 end if;
2725
2726
2727 if ((SetUserRoleAttr.role_orig_system is NULL) or
2728 (SetUserRoleAttr.role_orig_system_id is NULL)) then
2729 --Checking the role.
2730 begin
2731 SELECT ORIG_SYSTEM, ORIG_SYSTEM_ID, PARTITION_ID
2732 INTO l_rorigSys, l_rorigSysID,l_partitionID
2733 FROM WF_LOCAL_ROLES
2734 WHERE NAME = SetUserRoleAttr.role_name
2735 AND rownum < 2;
2736
2737 if (l_porigSys is NULL or l_porigSysID is NULL) then
2738 l_porigSys := l_rorigSys;
2739 l_porigSysID := l_rorigSysID;
2740 end if;
2741
2742 exception
2743 when NO_DATA_FOUND then
2744 WF_CORE.Token('NAME', SetUserRoleAttr.role_name);
2745 WF_CORE.Token('ORIG_SYSTEM', 'NULL');
2746 WF_CORE.Token('ORIG_SYSTEM_ID', 'NULL');
2747 WF_CORE.Raise('WF_NO_ROLE');
2748 end;
2749 end if;
2750 if (l_partitionID is null) then
2751 AssignPartition(l_rorigSys,l_partitionID,l_partitionName);
2752 end if;
2753 --We need to capture the current start/end date in case they are
2754 --changed.
2755 begin
2756 -- Bug 8680963 / 9323176
2757 select START_DATE, END_DATE
2758 into l_oldStartDate, l_oldEndDate
2759 from (select START_DATE, END_DATE
2760 from WF_USER_ROLE_ASSIGNMENTS
2761 where USER_NAME = SetUserRoleAttr.user_name
2762 and ROLE_NAME = SetUserRoleAttr.role_name
2763 and USER_ORIG_SYSTEM = l_uorigSys
2764 and USER_ORIG_SYSTEM_ID = l_uorigSysID
2765 and ROLE_ORIG_SYSTEM = l_rorigSys
2766 and ROLE_ORIG_SYSTEM_ID = l_rorigSysID
2767 and PARTITION_ID = l_partitionID
2768 order by relationship_id)
2769 where rownum = 1;
2770 -- By using rownum=1 and ordering by relationship_id we guarantee the
2771 -- direct assignment will go first and then the older indirect assignments
2772
2773 exception
2774 when NO_DATA_FOUND then
2775 WF_CORE.Raise('WF_INVAL_USER_ROLE');
2776 end;
2777
2778 -- Evaluating the WHO columns in case they are not passed
2779 l_lastupdby := nvl(setuserroleattr.last_updated_by, WFA_SEC.USER_ID);
2780 l_lastupddt := nvl(setuserroleattr.last_update_date, SYSDATE);
2781 l_lastupdlog:= nvl(setuserroleattr.last_update_login, WFA_SEC.LOGIN_ID);
2782
2783 if (OverWrite and l_updateWho) then
2784 update WF_LOCAL_USER_ROLES
2785 set EXPIRATION_DATE = SetUserRoleAttr.end_date,
2786 START_DATE = SetUserRoleAttr.start_date,
2787 LAST_UPDATED_BY = l_lastupdby,
2788 LAST_UPDATE_DATE = l_lastupddt,
2789 LAST_UPDATE_LOGIN = l_lastupdlog,
2790 -- <7298384> never update CREATION_DATE, CREATED_BY on update dml
2791 -- CREATION_DATE = nvl(SetUserRoleAttr.creation_date,
2792 -- CREATION_DATE),
2793 -- CREATED_BY = nvl(SetUserRoleAttr.created_by, CREATED_BY), -- </7298384>
2794 PARENT_ORIG_SYSTEM = l_porigSys,
2795 PARENT_ORIG_SYSTEM_ID = l_porigSysID,
2796 ASSIGNMENT_REASON = SetUserRoleAttr.assignment_reason
2797 where USER_NAME = SetUserRoleAttr.user_name
2798 and ROLE_NAME = SetUserRoleAttr.role_name
2799 and USER_ORIG_SYSTEM = l_uorigSys
2800 and USER_ORIG_SYSTEM_ID = l_uorigSysID
2801 and ROLE_ORIG_SYSTEM = l_rorigSys
2802 and ROLE_ORIG_SYSTEM_ID = l_rorigSysID
2803 and PARTITION_ID = l_partitionID
2804 returning rowid into l_affectedRow;
2805
2806 elsif (OverWrite) then --donot Update WHO Columns
2807 update WF_LOCAL_USER_ROLES
2808 set EXPIRATION_DATE = SetUserRoleAttr.end_date,
2809 START_DATE = SetUserRoleAttr.start_date,
2810 PARENT_ORIG_SYSTEM = l_porigSys,
2811 PARENT_ORIG_SYSTEM_ID = l_porigSysID,
2812 ASSIGNMENT_REASON = SetUserRoleAttr.assignment_reason
2813 where USER_NAME = SetUserRoleAttr.user_name
2814 and ROLE_NAME = SetUserRoleAttr.role_name
2815 and USER_ORIG_SYSTEM = l_uorigSys
2816 and USER_ORIG_SYSTEM_ID = l_uorigSysID
2817 and ROLE_ORIG_SYSTEM = l_rorigSys
2818 and ROLE_ORIG_SYSTEM_ID = l_rorigSysID
2819 and PARTITION_ID = l_partitionID
2820 returning rowid into l_affectedRow;
2821
2822 elsif (l_updateWho) then -- Update WHO columns
2823 update WF_LOCAL_USER_ROLES
2824 set EXPIRATION_DATE = nvl(SetUserRoleAttr.end_date, EXPIRATION_DATE),
2825 START_DATE = nvl(SetUserRoleAttr.start_date, START_DATE),
2826 PARENT_ORIG_SYSTEM = nvl(SetUserRoleAttr.parent_orig_system,
2827 l_porigSys),
2828 PARENT_ORIG_SYSTEM_ID = nvl(
2829 SetUserRoleAttr.parent_orig_system_id,
2830 l_porigSysID),
2831 LAST_UPDATED_BY = l_lastupdby,
2832 LAST_UPDATE_DATE = l_lastupddt,
2833 LAST_UPDATE_LOGIN = l_lastupdlog,
2834 -- <7298384> never update CREATION_DATE, CREATED_BY on update dml
2835 -- CREATED_BY = nvl(SetUserRoleAttr.created_by,
2836 -- created_by),
2837 -- CREATION_DATE = nvl(SetUserRoleAttr.creation_date,
2838 -- creation_date), -- </7298384>
2839 ASSIGNMENT_REASON = nvl(SetUserRoleAttr.assignment_reason,
2840 ASSIGNMENT_REASON)
2841 where USER_NAME = SetUserRoleAttr.user_name
2842 and ROLE_NAME = SetUserRoleAttr.role_name
2843 and USER_ORIG_SYSTEM = l_uorigSys
2844 and USER_ORIG_SYSTEM_ID = l_uorigSysID
2845 and ROLE_ORIG_SYSTEM = l_rorigSys
2846 and ROLE_ORIG_SYSTEM_ID = l_rorigSysID
2847 and PARTITION_ID = l_partitionID
2848 returning rowid into l_affectedRow;
2849
2850
2851 else --Donot Update Who columns
2852 update WF_LOCAL_USER_ROLES
2853 set EXPIRATION_DATE = nvl(SetUserRoleAttr.end_date, EXPIRATION_DATE),
2854 START_DATE = nvl(SetUserRoleAttr.start_date, START_DATE),
2855 PARENT_ORIG_SYSTEM = nvl(SetUserRoleAttr.parent_orig_system,
2856 l_porigSys),
2857 PARENT_ORIG_SYSTEM_ID = nvl(
2858 SetUserRoleAttr.parent_orig_system_id,
2859 l_porigSysID),
2860 ASSIGNMENT_REASON = nvl(SetUserRoleAttr.assignment_reason,
2861 ASSIGNMENT_REASON)
2862 where USER_NAME = SetUserRoleAttr.user_name
2863 and ROLE_NAME = SetUserRoleAttr.role_name
2864 and USER_ORIG_SYSTEM = l_uorigSys
2865 and USER_ORIG_SYSTEM_ID = l_uorigSysID
2866 and ROLE_ORIG_SYSTEM = l_rorigSys
2867 and ROLE_ORIG_SYSTEM_ID = l_rorigSysID
2868 and PARTITION_ID = l_partitionID
2869 returning rowid into l_affectedRow;
2870
2871 end if;
2872
2873 if (sql%ROWCOUNT = 0) then
2874 WF_CORE.Raise('WF_INVAL_USER_ROLE');
2875
2876 end if;
2877 --We were able to update an existing record, so we will raise the
2878 --updated event
2879
2880 --Build parameter list.
2881 WF_EVENT.AddParameterToList('ROWID', ROWIDTOCHAR(l_affectedRow), l_params);
2882 WF_EVENT.AddParameterToList('USER_NAME', SetUserRoleAttr.user_name,
2883 l_params);
2884 WF_EVENT.AddParameterToList('ROLE_NAME', SetUserRoleAttr.role_name,
2885 l_params);
2886 WF_EVENT.AddParameterToList('USER_ORIG_SYSTEM', l_uorigSys, l_params);
2887 WF_EVENT.AddParameterToList('USER_ORIG_SYSTEM_ID', to_char(l_uorigSysID,
2888 WF_CORE.canonical_number_mask), l_params);
2889 WF_EVENT.AddParameterToList('ROLE_ORIG_SYSTEM', l_rorigSys, l_params);
2890 WF_EVENT.AddParameterToList('ROLE_ORIG_SYSTEM_ID', to_char(l_rorigSysID,
2891 WF_CORE.canonical_number_mask), l_params);
2892 WF_EVENT.AddParameterToList('PARENT_ORIG_SYSTEM', l_porigSys, l_params);
2893 WF_EVENT.AddParameterToList('PARENT_ORIG_SYSTEM_ID', to_char(l_porigSysID,
2894 WF_CORE.canonical_number_mask), l_params);
2895 WF_EVENT.AddParameterToList('START_DATE',
2896 to_char(trunc(SetUserRoleAttr.start_date),
2897 WF_CORE.canonical_date_mask),
2898 l_params);
2899 WF_EVENT.AddParameterToList('END_DATE',
2900 to_char(trunc(SetUserRoleAttr.end_date),
2901 WF_CORE.canonical_date_mask),
2902 l_params);
2903
2904 if ((((l_oldStartDate is NOT NULL) and
2905 (SetUserRoleAttr.start_date is NOT NULL)) and
2906 (trunc(l_oldStartDate) <> trunc(SetUserRoleAttr.start_date))) or
2907 ((l_oldStartDate is NULL and SetUserRoleAttr.start_date is NOT NULL) or
2908 (SetUserRoleAttr.start_date is NULL and l_oldStartDate is NOT NULL))) then
2909
2910 WF_EVENT.AddParameterToList('OLD_START_DATE',
2911 to_char(trunc(l_oldStartDate),
2912 WF_CORE.Canonical_Date_Mask),
2913 l_params);
2914 else
2915 WF_EVENT.AddParameterToList('OLD_START_DATE', '*UNDEFINED*',
2916 l_params);
2917 end if;
2918
2919 if ((((l_oldEndDate is NOT NULL) and
2920 (SetUserRoleAttr.end_date is NOT NULL)) and
2921 (trunc(l_oldEndDate) <> trunc(SetUserRoleAttr.end_date))) or
2922 ((l_oldEndDate is NULL and SetUserRoleAttr.end_date is NOT NULL)
2923 or (SetUserRoleAttr.end_date is NULL
2924 and l_oldEndDate is NOT NULL))) then
2925
2926 WF_EVENT.AddParameterToList('OLD_END_DATE',
2927 to_char(trunc(l_oldEndDate),
2928 WF_CORE.Canonical_Date_Mask),
2929 l_params);
2930 else
2931 WF_EVENT.AddParameterToList('OLD_END_DATE', '*UNDEFINED*',
2932 l_params);
2933 end if;
2934
2935
2936 WF_EVENT.AddParameterToList('LAST_UPDATED_BY',
2937 to_char(SetUserRoleAttr.last_updated_by,
2938 WF_CORE.canonical_number_mask), l_params);
2939
2940 WF_EVENT.AddParameterToList('LAST_UPDATE_DATE',
2941 to_char(SetUserRoleAttr.last_update_date,
2942 WF_CORE.canonical_date_mask), l_params);
2943 WF_EVENT.AddParameterToList('LAST_UPDATE_LOGIN',
2944 to_char(SetUserRoleAttr.last_update_login,
2945 WF_CORE.canonical_number_mask), l_params);
2946
2947 WF_EVENT.AddParameterToList('CREATED_BY',
2948 to_char(SetUserRoleAttr.created_by,
2949 WF_CORE.canonical_number_mask), l_params);
2950
2951 WF_EVENT.AddParameterToList('CREATION_DATE',
2952 to_char(SetUserRoleAttr.creation_date,
2953 WF_CORE.canonical_date_mask), l_params);
2954
2955 WF_EVENT.AddParameterToList('ASSIGNMENT_TYPE',
2956 SetUserRoleAttr.assignment_type, l_params);
2957
2958 WF_EVENT.AddParameterToList('ASSIGNMENT_REASON',
2959 SetUserRoleAttr.assignment_reason, l_params);
2960
2961 if (OverWrite) then
2962 WF_EVENT.AddParameterToList('WFSYNCH_OVERWRITE', 'TRUE',l_params );
2963 end if;
2964
2965 if (l_updateWho) then
2966 WF_EVENT.AddParameterToList('UPDATE_WHO','TRUE',l_params);
2967 end if;
2968
2969 if (eventParams is not null and eventParams.count>0) then
2970 for i in eventParams.first..eventParams.last loop
2971 WF_EVENT.AddParameterToList(upper(eventParams(i).getName()),
2972 eventParams(i).getValue(),l_params);
2973 end loop;
2974 end if;
2975
2976 --determine if BES is enabled
2977 if (wf_directory.System_Status()='DISABLED') then
2978 --Create the event that is to be raised
2979
2980 wf_event_t.initialize(event);
2981 event.Send_Date := sysdate;
2982 event.Event_Name := 'oracle.apps.fnd.wf.ds.userRole.updated';
2983 event.Event_Key := l_uOrigSys||':'||
2984 to_char(l_uOrigSysId)||'|'||
2985 l_rOrigSys||':'||
2986 to_char(l_rOrigSysId)||'|'||
2987 to_char(SYSDATE, 'J:SSSSS');
2988
2989 event.Parameter_List := l_params;
2990 sub_id :=hextoraw('1');
2991 result:= WF_ROLE_HIERARCHY.Cascade_RF(sub_id,event);
2992 if (result='SUCCESS') then
2993 result:= WF_ROLE_HIERARCHY.Aggregate_User_Roles_RF(sub_id,event);
2994 end if;
2995 else
2996 WF_EVENT.Raise(p_event_name=>'oracle.apps.fnd.wf.ds.userRole.updated',
2997 p_event_key=> l_uOrigSys||':'||
2998 to_char(l_uOrigSysId)||'|'||
2999 l_rOrigSys||':'||
3000 to_char(l_rOrigSysId)||'|'||
3001 to_char(SYSDATE, 'J:SSSSS'),
3002 p_parameters=>l_params);
3003 end if;
3004
3005
3006 end;
3007
3008
3009 --
3010 -- RemoveUserRole (PRIVATE)
3011 -- Remove a user from a role.
3012 -- IN
3013 -- user_name -
3014 -- role_name -
3015 -- user_orig_system -
3016 -- user_orig_system_id -
3017 -- role_orig_system -
3018 -- role_orig_system_id -
3019 --
3020 procedure RemoveUserRole(user_name in varchar2,
3021 role_name in varchar2,
3022 user_orig_system in varchar2,
3023 user_orig_system_id in number,
3024 role_orig_system in varchar2,
3025 role_orig_system_id in number)
3026 is
3027
3028 l_lastupddt date;
3029 l_lastupdlog number;
3030 l_lastupdby number;
3031 l_expdate date;
3032 l_partitionID number;
3033 l_partitionName varchar2(30);
3034
3035 begin
3036 -- set the expiration date
3037 l_expdate := SYSDATE;
3038
3039 --set the who columns
3040
3041 l_lastupddt := SYSDATE;
3042 l_lastupdby := WFA_SEC.USER_ID;
3043 l_lastupdlog := WFA_SEC.LOGIN_ID;
3044
3045 AssignPartition( RemoveUserRole.role_orig_system,l_partitionId,l_partitionName);
3046
3047 if (user_orig_system is null or user_orig_system_id is null) then
3048 -- Expire user
3049 update WF_LOCAL_USER_ROLES
3050 set EXPIRATION_DATE = l_expdate,
3051 EFFECTIVE_END_DATE = l_expdate,
3052 LAST_UPDATED_BY = l_lastupdby,
3053 LAST_UPDATE_LOGIN = l_lastupdlog,
3054 LAST_UPDATE_DATE = l_lastupddt
3055 where USER_NAME = RemoveUserRole.user_name
3056 and ROLE_NAME = RemoveUserRole.role_name
3057 and ROLE_ORIG_SYSTEM = RemoveUserRole.role_orig_system
3058 and ROLE_ORIG_SYSTEM_ID = RemoveUserRole.role_orig_system_id
3059 and PARTITION_ID = l_partitionID;
3060
3061 update WF_USER_ROLE_ASSIGNMENTS
3062 set END_DATE = l_expdate,
3063 EFFECTIVE_END_DATE = l_expdate,
3064 LAST_UPDATED_BY = l_lastupdby,
3065 LAST_UPDATE_LOGIN = l_lastupdlog,
3066 LAST_UPDATE_DATE = l_lastupddt
3067 where USER_NAME = RemoveUserRole.user_name
3068 and ROLE_NAME = RemoveUserRole.role_name
3069 and ROLE_ORIG_SYSTEM = RemoveUserRole.role_orig_system
3070 and ROLE_ORIG_SYSTEM_ID = RemoveUserRole.role_orig_system_id;
3071
3072 else
3073
3074 -- Expire user with orig system and orig system id
3075 update WF_LOCAL_USER_ROLES
3076 set EXPIRATION_DATE = l_expdate,
3077 EFFECTIVE_END_DATE = l_expdate,
3078 LAST_UPDATED_BY = l_lastupdby,
3079 LAST_UPDATE_LOGIN = l_lastupdlog,
3080 LAST_UPDATE_DATE = l_lastupddt
3081 where USER_NAME = RemoveUserRole.user_name
3082 and ROLE_NAME = RemoveUserRole.role_name
3083 and USER_ORIG_SYSTEM = RemoveUserRole.user_orig_system
3084 and USER_ORIG_SYSTEM_ID = RemoveUserRole.user_orig_system_id
3085 and ROLE_ORIG_SYSTEM = RemoveUserRole.role_orig_system
3086 and ROLE_ORIG_SYSTEM_ID = RemoveUserRole.role_orig_system_id
3087 and PARTITION_ID = l_partitionID;
3088
3089 update WF_USER_ROLE_ASSIGNMENTS
3090 set END_DATE = l_expdate,
3091 EFFECTIVE_END_DATE = l_expdate,
3092 LAST_UPDATED_BY = l_lastupdby,
3093 LAST_UPDATE_LOGIN = l_lastupdlog,
3094 LAST_UPDATE_DATE = l_lastupddt
3095 where USER_NAME = RemoveUserRole.user_name
3096 and ROLE_NAME = RemoveUserRole.role_name
3097 and USER_ORIG_SYSTEM = RemoveUserRole.user_orig_system
3098 and USER_ORIG_SYSTEM_ID = RemoveUserRole.user_orig_system_id
3099 and ROLE_ORIG_SYSTEM = RemoveUserRole.role_orig_system
3100 and ROLE_ORIG_SYSTEM_ID = RemoveUserRole.role_orig_system_id;
3101
3102 end if;
3103
3104 -- DL: did not trap the WF_INVALID_USER error here.
3105 -- It should be fine if someone want to remove a user again from the
3106 -- same role. Plus it should be a user/role not exist, not an invalid
3107 -- user.
3108
3109 exception
3110 when others then
3111 wf_core.context('Wf_Directory', 'RemoveUserRole',
3112 user_name, role_name, user_orig_system, to_char(user_orig_system_id),
3113 role_orig_system, to_char(role_orig_system_id));
3114
3115 raise;
3116 end RemoveUserRole;
3117
3118 --
3119 -- AddUsersToAdHocRole (Deprecated)
3120 -- Use AddUsersToAdHocRole2
3121 -- IN
3122 -- role_name - AdHoc role name
3123 -- role_users - Space or comma delimited list of apps-based users
3124 -- or adhoc users
3125 -- OUT
3126 --
3127 procedure AddUsersToAdHocRole(role_name in varchar2,
3128 role_users in varchar2)
3129 is
3130 l_users WF_DIRECTORY.UserTable;
3131
3132 begin
3133
3134 if (role_users is NOT NULL) then
3135 String_To_UserTable (p_UserList=>AddUsersToAdHocRole.role_users,
3136 p_UserTable=>l_users);
3137
3138 AddUsersToAdHocRole2(role_name=>AddUsersToAdHocRole.role_name,
3139 role_users=>l_users);
3140 end if;
3141
3142 exception
3143 when others then
3144 wf_core.context('Wf_Directory', 'AddUsersToAdHocRole',
3145 role_name, '"'||role_users||'"');
3146 raise;
3147 end AddUsersToAdHocRole;
3148
3149 --
3150 -- AddUsersToAdHocRole2
3151 -- Add users to an existing ad hoc role
3152 -- IN
3153 -- role_name - AdHoc role name
3154 -- role_users - Space or comma delimited list of apps-based users
3155 -- or adhoc users
3156 -- OUT
3157 --
3158 procedure AddUsersToAdHocRole2(role_name in varchar2,
3159 role_users in WF_DIRECTORY.UserTable) is
3160
3161 d1 pls_integer;
3162 colon pls_integer;
3163 userIND number;
3164 l_orig_system varchar2(30) := NULL;
3165 l_orig_system_id number := NULL;
3166
3167 begin
3168 -- Validate Role
3169 if (wfa_sec.DS_Count_Local_Role(AddUsersToAdHocRole2.role_name) <= 0) then
3170 wf_core.token('ROLENAME', AddUsersToAdHocRole2.role_name);
3171 wf_core.raise('WF_INVALID_ROLE');
3172 end if;
3173
3174 if (role_users.COUNT > 0) then
3175 for userIND in role_users.FIRST..role_users.LAST loop
3176 if (role_users(userIND) is NOT NULL) then
3177 -- Validation
3178 -- 1379875: (Performance) added support for orig_system, orig_system_id
3179 -- composite name.
3180 -- Changed Validation and duplicate checking to limit selects against
3181 -- wf_users.
3182 -- Used a sub-block to use exception handling instead of single count
3183 -- into.
3184
3185
3186 begin
3187 colon := instr(role_users(userIND), ':');
3188 if (colon = 0) then
3189 --Bug 2465881
3190 --To eliminate error: Exact fetch returning more than
3191 --requested number of rows.
3192 SELECT ORIG_SYSTEM, ORIG_SYSTEM_ID
3193 INTO l_orig_system, l_orig_system_id
3194 FROM WF_USERS
3195 WHERE NAME = role_users(userIND)
3196 AND partition_id <> 9
3197 AND rownum < 2;
3198 else
3199 -- Bug 2465881
3200 SELECT ORIG_SYSTEM, ORIG_SYSTEM_ID
3201 INTO l_orig_system, l_orig_system_id
3202 FROM WF_USERS
3203 WHERE ORIG_SYSTEM = substr(role_users(userIND), 1, colon-1)
3204 AND ORIG_SYSTEM_ID = substr(role_users(userIND), colon+1)
3205 AND rownum < 2;
3206 end if;
3207 exception
3208 when NO_DATA_FOUND then
3209 wf_core.token('USERNAME', role_users(userIND));
3210 wf_core.raise('WF_INVALID_USER');
3211
3212 when others then
3213 wf_core.context('Wf_Directory', 'AddUsersToAdHocRole2', role_name);
3214 raise;
3215 end;
3216
3217 -- Check Duplicate
3218 -- for local table, check user name and role name are sufficient
3219 -- there will not be index on orig system and orig system id
3220 -- orig systems and orig system ids are identical among the users
3221 -- from WF_LOCAL_ROLES.
3222 --
3223
3224 -- <rwunderl:5218259>
3225 -- Commenting out this check and catching a dup user/role exception
3226 -- select count(1) into d1
3227 -- from WF_LOCAL_USER_ROLES
3228 -- where USER_NAME = role_users(userIND)
3229 -- and ROLE_NAME = AddUsersToAdHocRole2.role_name
3230 -- and ROLE_ORIG_SYSTEM = 'WF_LOCAL_ROLES'
3231 -- and ROLE_ORIG_SYSTEM_ID = 0;
3232
3233 -- if (d1 > 0) then
3234 -- wf_core.token('USERNAME', role_users(userIND));
3235 -- wf_core.token('DISPNAME', '');
3236 -- wf_core.raise('WF_DUP_USER');
3237 -- end if;
3238
3239 CreateUserRole(user_name=>role_users(userIND),
3240 role_name=>AddUsersToAdHocRole2.role_name,
3241 user_orig_system=>l_orig_system,
3242 user_orig_system_id=>l_orig_system_id,
3243 role_orig_system=>'WF_LOCAL_ROLES',
3244 role_orig_system_id=>0,
3245 start_date=>sysdate,
3246 end_date=>to_date(NULL),
3247 validateUserRole=>FALSE,
3248 created_by=>WFA_SEC.user_id,
3249 creation_date=>sysdate,
3250 last_updated_by=>WFA_SEC.user_id,
3251 last_update_date=>sysdate,
3252 last_update_login=>WFA_SEC.login_id);
3253
3254 end if;
3255 end loop;
3256 end if;
3257
3258 exception
3259 when others then
3260 wf_core.context('Wf_Directory', 'AddUsersToAdHocRole2',
3261 role_name);
3262 raise;
3263 end AddUsersToAdHocRole2;
3264
3265
3266 --
3267 -- SetUserAttr (PRIVATE)
3268 -- Update additional attributes for users
3269 -- IN
3270 -- user_name - user name
3271 -- orig_system -
3272 -- orig_system_id -
3273 -- display_name -
3274 -- notification_preference -
3275 -- language -
3276 -- territory -
3277 -- email_address -
3278 -- fax -
3279 -- expiration_date - New expiration date
3280 -- status - status could be 'ACTIVE' or 'INACTIVE'
3281 -- start_date -
3282 -- OverWrite -
3283 -- parent_orig_system -
3284 -- parent_orig_system_id -
3285 -- owner_tag -
3286 -- last_updated_by -
3287 -- last_update_date -
3288 -- last_update_login -
3289 -- OUT
3290 --
3291 procedure SetUserAttr(user_name in varchar2,
3292 orig_system in varchar2,
3293 orig_system_id in number,
3294 display_name in varchar2,
3295 description in varchar2,
3296 notification_preference in varchar2,
3297 language in varchar2,
3298 territory in varchar2,
3299 email_address in varchar2,
3300 fax in varchar2,
3301 expiration_date in date,
3302 status in varchar2,
3303 start_date in date,
3304 OverWrite in boolean,
3305 Parent_Orig_System in varchar2,
3306 Parent_orig_system_id in number,
3307 owner_tag in varchar2,
3308 last_updated_by in number,
3309 last_update_date in date,
3310 last_update_login in number,
3311 created_by in number,
3312 creation_date in date,
3313 eventParams in wf_parameter_list_t)
3314 is
3315
3316 l_expiration DATE;
3317 l_params WF_PARAMETER_LIST_T;
3318 l_oldStartDate DATE;
3319 l_oldEndDate DATE;
3320 l_lastupddt DATE;
3321 l_creatdt DATE;
3322 l_creatby NUMBER;
3323 l_lastupdby NUMBER;
3324 l_lastupdlog NUMBER;
3325 l_partitionID NUMBER;
3326 l_partitionName VARCHAR2(30);
3327
3328 begin
3329 --We first need to capture the current start/end date in case they are
3330 --changed.
3331 AssignPartition(SetUserAttr.orig_system, l_partitionID, l_partitionName);
3332
3333 begin
3334 SELECT START_DATE, EXPIRATION_DATE
3335 INTO l_oldStartDate, l_oldEndDate
3336 FROM WF_LOCAL_ROLES
3337 WHERE NAME = SetUserAttr.user_name
3338 AND ORIG_SYSTEM = SetUserAttr.orig_system
3339 AND ORIG_SYSTEM_ID = SetUserAttr.orig_system_id
3340 AND PARTITION_ID = l_partitionID;
3341 exception
3342 when NO_DATA_FOUND then
3343 wf_core.token('USERNAME', user_name);
3344 wf_core.raise('WF_INVALID_USER');
3345 end;
3346
3347 --
3348 -- Evaluate the WHO columns, and set default values if they are not passed
3349
3350 l_creatdt := nvl(SetUserAttr.creation_date, SYSDATE);
3351 l_creatby := nvl(SetUserAttr.created_by, WFA_SEC.USER_ID);
3352 l_lastupddt := nvl(SetUserAttr.last_update_date, SYSDATE);
3353 l_lastupdby := nvl(SetUserAttr.last_updated_by, WFA_SEC.USER_ID);
3354 l_lastupdlog := nvl(SetUserAttr.last_update_login, WFA_SEC.LOGIN_ID);
3355
3356 -- Update WF_LOCAL_ROLES where user_flag = 'Y'
3357 --
3358 if (OverWrite) then
3359
3360 if ((userenv('LANG') = 'US') OR
3361 (isMLSEnabled(SetUserAttr.orig_system) = FALSE)) then
3362 update WF_LOCAL_ROLES
3363 set NOTIFICATION_PREFERENCE = nvl(SetUserAttr.notification_preference,
3364 NOTIFICATION_PREFERENCE),
3365 LANGUAGE = nvl(SetUserAttr.language, LANGUAGE),
3366 TERRITORY = nvl(SetUserAttr.territory, TERRITORY),
3367 EMAIL_ADDRESS = SetUserAttr.email_address,
3368 FAX = SetUserAttr.fax,
3369 DISPLAY_NAME = nvl(SetUserAttr.display_name,
3370 DISPLAY_NAME),
3371 DESCRIPTION = SetUserAttr.description,
3372 EXPIRATION_DATE = SetUserAttr.expiration_date,
3373 STATUS = nvl(SetUserAttr.status, STATUS),
3374 START_DATE = SetUserAttr.start_date,
3375 PARENT_ORIG_SYSTEM = SetUserAttr.parent_orig_system,
3376 PARENT_ORIG_SYSTEM_ID = SetUserAttr.parent_orig_system_id,
3377 OWNER_TAG = SetUserAttr.owner_tag,
3378 -- <7298384> always keep CREATED_BY and CREATION_DATE in update
3379 -- CREATED_BY = nvl(SetUserAttr.created_by, created_by),
3380 -- CREATION_DATE = nvl(SetUserAttr.creation_date,
3381 -- creation_date), -- </7298384>
3382 LAST_UPDATED_BY = l_lastupdby,
3383 LAST_UPDATE_DATE = l_lastupddt,
3384 LAST_UPDATE_LOGIN = l_lastupdlog
3385 where NAME = user_name
3386 and ORIG_SYSTEM = SetUserAttr.orig_system
3387 and ORIG_SYSTEM_ID = SetUserAttr.orig_system_id
3388 and PARTITION_ID = l_partitionID
3389 and USER_FLAG = 'Y';
3390
3391 else
3392
3393 update WF_LOCAL_ROLES
3394 set NOTIFICATION_PREFERENCE = nvl(SetUserAttr.notification_preference,
3395 NOTIFICATION_PREFERENCE),
3396 LANGUAGE = nvl(SetUserAttr.language, LANGUAGE),
3397 TERRITORY = nvl(SetUserAttr.territory, TERRITORY),
3398 EMAIL_ADDRESS = SetUserAttr.email_address,
3399 FAX = SetUserAttr.fax,
3400 EXPIRATION_DATE = SetUserAttr.expiration_date,
3401 STATUS = nvl(SetUserAttr.status, STATUS),
3402 START_DATE = SetUserAttr.start_date,
3403 PARENT_ORIG_SYSTEM = SetUserAttr.parent_orig_system,
3404 PARENT_ORIG_SYSTEM_ID = SetUserAttr.parent_orig_system_id,
3405 -- <7298384> always keep CREATED_BY and CREATION_DATE in update
3406 -- CREATED_BY = nvl(SetUserAttr.created_by, created_by),
3407 -- CREATION_DATE = nvl(SetUserAttr.creation_date,
3408 -- creation_date), -- </7298384>
3409 LAST_UPDATED_BY = l_lastupdby,
3410 LAST_UPDATE_DATE = l_lastupddt,
3411 LAST_UPDATE_LOGIN = l_lastupdlog
3412 where NAME = user_name
3413 and ORIG_SYSTEM = SetUserAttr.orig_system
3414 and ORIG_SYSTEM_ID = SetUserAttr.orig_system_id
3415 and PARTITION_ID = l_partitionID
3416 and USER_FLAG = 'Y';
3417
3418
3419 --Update the _TL table
3420 update WF_LOCAL_ROLES_TL
3421 set DISPLAY_NAME = nvl(SetUserAttr.display_name, DISPLAY_NAME),
3422 DESCRIPTION = SetUserAttr.description,
3423 -- <7298384> always keep CREATED_BY and CREATION_DATE in update
3424 -- CREATED_BY = nvl(SetUserAttr.created_by, created_by),
3425 -- CREATION_DATE = nvl(SetUserAttr.creation_date, creation_date), -- </7298384>
3426 LAST_UPDATED_BY = l_lastupdby,
3427 LAST_UPDATE_DATE = l_lastupddt,
3428 LAST_UPDATE_LOGIN = l_lastupdlog
3429 where NAME = SetUserAttr.user_name
3430 and ORIG_SYSTEM = SetUserAttr.orig_system
3431 and ORIG_SYSTEM_ID = SetUserAttr.orig_system_id
3432 and LANGUAGE = userenv('LANG')
3433 and PARTITION_ID = l_partitionID;
3434
3435 if (sql%rowcount = 0) then
3436 --No record exist for this lang in _TL table
3437 --so insert a record
3438 insert into WF_LOCAL_ROLES_TL (NAME,
3439 DISPLAY_NAME,
3440 DESCRIPTION,
3441 ORIG_SYSTEM,
3442 ORIG_SYSTEM_ID,
3443 PARTITION_ID,
3444 LANGUAGE,
3445 OWNER_TAG,
3446 CREATED_BY,
3447 CREATION_DATE,
3448 LAST_UPDATED_BY,
3449 LAST_UPDATE_DATE,
3450 LAST_UPDATE_LOGIN )
3451 (select wu.name,
3452 nvl(SetUserAttr.display_name, wu.DISPLAY_NAME) ,
3453 SetUserAttr.description,
3454 wu.ORIG_SYSTEM,
3455 wu.ORIG_SYSTEM_ID ,
3456 wu.PARTITION_ID,
3457 userenv('LANG'),
3458 nvl(SetUserAttr.owner_tag, wu.OWNER_TAG),
3459 l_creatby,
3460 l_creatdt,
3461 l_lastupdby,
3462 l_lastupddt,
3463 l_lastupdlog
3464 from WF_LOCAL_ROLES wu
3465 where wu.NAME = SetUserAttr.user_name
3466 and wu.ORIG_SYSTEM = SetUserAttr.orig_system
3467 and wu.ORIG_SYSTEM_ID = SetUserAttr.orig_system_id
3468 and wu.PARTITION_ID = l_partitionID
3469 and wu.USER_FLAG = 'Y');
3470
3471 --Now update the table for relevant data
3472
3473 end if;
3474 end if;
3475 else
3476 if ((userenv('LANG') = 'US') OR
3477 (isMLSEnabled(SetUserAttr.orig_system) = FALSE)) then
3478 update WF_LOCAL_ROLES
3479 set NOTIFICATION_PREFERENCE = nvl(SetUserAttr.notification_preference,
3480 NOTIFICATION_PREFERENCE),
3481 LANGUAGE = nvl(SetUserAttr.language, LANGUAGE),
3482 TERRITORY = nvl(SetUserAttr.territory, TERRITORY),
3483 EMAIL_ADDRESS = nvl(SetUserAttr.email_address, EMAIL_ADDRESS),
3484 FAX = nvl(SetUserAttr.fax, FAX),
3485 DISPLAY_NAME = nvl(SetUserAttr.display_name, DISPLAY_NAME),
3486 DESCRIPTION = nvl(SetUserAttr.description, DESCRIPTION),
3487 EXPIRATION_DATE = nvl(SetUserAttr.expiration_date, EXPIRATION_DATE),
3488 STATUS = nvl(SetUserAttr.status, STATUS),
3489 START_DATE = nvl(SetUserAttr.start_date, START_DATE),
3490 PARENT_ORIG_SYSTEM = nvl(SetUserAttr.parent_orig_system,
3491 PARENT_ORIG_SYSTEM),
3492 PARENT_ORIG_SYSTEM_ID = nvl(SetUserAttr.parent_orig_system_id,
3493 PARENT_ORIG_SYSTEM_ID),
3494 OWNER_TAG = nvl(SetUserAttr.owner_tag, OWNER_TAG)
3495 -- <7298384> no overwrite mode, so keeping previous standard WHO column values
3496 -- CREATED_BY = nvl(SetUserAttr.created_by, CREATED_BY),
3497 -- CREATION_DATE = nvl(SetUserAttr.creation_date, CREATION_DATE),
3498 -- LAST_UPDATED_BY = l_lastupdby,
3499 -- LAST_UPDATE_DATE = l_lastupddt,
3500 -- LAST_UPDATE_LOGIN = l_lastupdlog
3501 -- </7298384>
3502 where NAME = user_name
3503 and ORIG_SYSTEM = SetUserAttr.orig_system
3504 and ORIG_SYSTEM_ID = SetUserAttr.orig_system_id
3505 and PARTITION_ID = l_partitionID
3506 and USER_FLAG = 'Y';
3507 else
3508 update WF_LOCAL_ROLES
3509 set NOTIFICATION_PREFERENCE = nvl(SetUserAttr.notification_preference,
3510 NOTIFICATION_PREFERENCE),
3511 LANGUAGE = nvl(SetUserAttr.language, LANGUAGE),
3512 TERRITORY = nvl(SetUserAttr.territory, TERRITORY),
3513 EMAIL_ADDRESS = nvl(SetUserAttr.email_address, EMAIL_ADDRESS),
3514 FAX = nvl(SetUserAttr.fax, FAX),
3515 EXPIRATION_DATE = nvl(SetUserAttr.expiration_date, EXPIRATION_DATE),
3516 STATUS = nvl(SetUserAttr.status, STATUS),
3517 START_DATE = nvl(SetUserAttr.start_date, START_DATE),
3518 PARENT_ORIG_SYSTEM = nvl(SetUserAttr.parent_orig_system,
3519 PARENT_ORIG_SYSTEM),
3520 PARENT_ORIG_SYSTEM_ID = nvl(SetUserAttr.parent_orig_system_id,
3521 PARENT_ORIG_SYSTEM_ID),
3522 OWNER_TAG = nvl(SetUserAttr.owner_tag, OWNER_TAG)
3523 -- <7298384> no overwrite mode, so keeping previous values
3524 -- CREATED_BY = nvl(SetUserAttr.created_by, CREATED_BY),
3525 -- CREATION_DATE = nvl(SetUserAttr.creation_date, CREATION_DATE),
3526 -- LAST_UPDATED_BY = l_lastupdby,
3527 -- LAST_UPDATE_DATE = l_lastupddt,
3528 -- LAST_UPDATE_LOGIN = l_lastupdlog -- </7298384>
3529 where NAME = user_name
3530 and ORIG_SYSTEM = SetUserAttr.orig_system
3531 and ORIG_SYSTEM_ID = SetUserAttr.orig_system_id
3532 and PARTITION_ID = l_partitionID
3533 and USER_FLAG = 'Y';
3534
3535 --then lets update the _TL table
3536 update WF_LOCAL_ROLES_TL
3537 set DISPLAY_NAME = nvl(SetUserAttr.display_name, DISPLAY_NAME),
3538 DESCRIPTION = SetUserAttr.description
3539 -- <7298384> no overwrite mode, so keeping previous values
3540 -- CREATED_BY = nvl(SetUserAttr.created_by, CREATED_BY),
3541 -- CREATION_DATE = nvl(SetUserAttr.creation_date, CREATION_DATE),
3542 -- LAST_UPDATED_BY = l_lastupdby,
3543 -- LAST_UPDATE_DATE = l_lastupddt,
3544 -- LAST_UPDATE_LOGIN = l_lastupdlog -- </7298384>
3545 where NAME = SetUserAttr.user_name
3546 and ORIG_SYSTEM = SetUserAttr.orig_system
3547 and ORIG_SYSTEM_ID = SetUserAttr.orig_system_id
3548 and LANGUAGE = userenv('LANG')
3549 and PARTITION_ID = l_partitionID;
3550
3551 if (sql%rowcount = 0) then
3552 --No record exist for this lang in _TL table
3553 --so insert a record
3554 --The issue is that if the passed values or param
3555 --are null we would be inserting 'US' language
3556 --display name and description
3557
3558 insert into WF_LOCAL_ROLES_TL (NAME,
3559 DISPLAY_NAME,
3560 DESCRIPTION,
3561 ORIG_SYSTEM,
3562 ORIG_SYSTEM_ID,
3563 PARTITION_ID,
3564 LANGUAGE,
3565 OWNER_TAG,
3566 CREATED_BY,
3567 CREATION_DATE,
3568 LAST_UPDATED_BY,
3569 LAST_UPDATE_DATE,
3570 LAST_UPDATE_LOGIN )
3571 (select name ,
3572 nvl(SetUserAttr.display_name,DISPLAY_NAME) ,
3573 SetUserAttr.description,
3574 ORIG_SYSTEM,
3575 ORIG_SYSTEM_ID,
3576 PARTITION_ID,
3577 userenv('LANG'),
3578 nvl(SetUserAttr.owner_tag, OWNER_TAG),
3579 l_creatby,
3580 l_creatdt,
3581 l_lastupdby,
3582 l_lastupddt,
3583 l_lastupdlog
3584 from WF_LOCAL_ROLES
3585 where NAME = SetUserAttr.user_name
3586 and ORIG_SYSTEM = SetUserAttr.orig_system
3587 and ORIG_SYSTEM_ID = SetUserAttr.orig_system_id
3588 and PARTITION_ID = l_partitionID
3589 and USER_FLAG = 'Y');
3590
3591 --Now update the table for relevant data
3592
3593 end if;
3594
3595 end if;
3596 end if;
3597 if (sql%rowcount = 0) then
3598 wf_core.token('USERNAME', user_name);
3599 wf_core.raise('WF_INVALID_USER');
3600 end if;
3601
3602 --We were able to update the record, so we will raise the updated event
3603 --Build parameter list.
3604 WF_EVENT.AddParameterToList('USER_NAME', SetUserAttr.user_name, l_params);
3605 WF_EVENT.AddParameterToList('ORIG_SYSTEM', SetUserAttr.orig_system,
3606 l_params);
3607 WF_EVENT.AddParameterToList('ORIG_SYSTEM_ID',
3608 to_char(SetUserAttr.orig_system_id), l_params);
3609 WF_EVENT.AddParameterToList('DISPLAY_NAME', SetUserAttr.display_name,
3610 l_params);
3611 WF_EVENT.AddParameterToList('DESCRIPTION', SetUserAttr.description,
3612 l_params);
3613 WF_EVENT.AddParameterToList('NOTIFICATION_PREFERENCE',
3614 SetUserAttr.notification_preference, l_params);
3615 WF_EVENT.AddParameterToList('LANGUAGE', SetUserAttr.language, l_params);
3616 WF_EVENT.AddParameterToList('TERRITORY', SetUserAttr.territory, l_params);
3617 WF_EVENT.AddParameterToList('EMAIL_ADDRESS', SetUserAttr.email_address,
3618 l_params);
3619 WF_EVENT.AddParameterToList('FAX', SetUserAttr.fax, l_params);
3620 WF_EVENT.AddParameterToList('EXPIRATION_DATE',
3621 to_char(trunc(SetUserAttr.expiration_date),
3622 WF_CORE.Canonical_Date_Mask),
3623 l_params);
3624 WF_EVENT.AddParameterToList('STATUS', SetUserAttr.status, l_params);
3625 WF_EVENT.AddParameterToList('START_DATE',
3626 to_char(trunc(SetUserAttr.start_date),
3627 WF_CORE.Canonical_Date_Mask),
3628 l_params);
3629 if ((((l_oldStartDate is NOT NULL) and
3630 (SetUserAttr.start_date is NOT NULL)) and
3631 (trunc(l_oldStartDate) <> trunc(SetUserAttr.start_date))) or
3632 ((l_oldStartDate is NULL and SetUserAttr.start_date is NOT NULL) or
3633 (SetUserAttr.start_date is NULL and l_oldStartDate is NOT NULL))) then
3634
3635 WF_EVENT.AddParameterToList('OLD_START_DATE',
3636 to_char(trunc(l_oldStartDate),
3637 WF_CORE.Canonical_Date_Mask),
3638 l_params);
3639 else
3640 WF_EVENT.AddParameterToList('OLD_START_DATE', '*UNDEFINED*',
3641 l_params);
3642 end if;
3643
3644 if ((((l_oldEndDate is NOT NULL) and
3645 (SetUserAttr.expiration_date is NOT NULL)) and
3646 (trunc(l_oldEndDate) <> trunc(SetUserAttr.expiration_date))) or
3647 ((l_oldEndDate is NULL and SetUserAttr.expiration_date is NOT NULL)
3648 or (SetUserAttr.expiration_date is NULL
3649 and l_oldEndDate is NOT NULL))) then
3650
3651 WF_EVENT.AddParameterToList('OLD_END_DATE',
3652 to_char(trunc(l_oldEndDate),
3653 WF_CORE.Canonical_Date_Mask),
3654 l_params);
3655 else
3656 WF_EVENT.AddParameterToList('OLD_END_DATE', '*UNDEFINED*',
3657 l_params);
3658 end if;
3659
3660 WF_EVENT.AddParameterToList('PARENT_ORIG_SYSTEM',
3661 SetUserAttr.parent_orig_system, l_params);
3662 WF_EVENT.AddParameterToList('PARENT_ORIG_SYSTEM_ID',
3663 to_char(SetUserAttr.parent_orig_system_id ,
3664 WF_CORE.canonical_number_mask), l_params);
3665 WF_EVENT.AddParameterToList('OWNER_TAG', SetUserAttr.owner_tag, l_params);
3666 WF_EVENT.AddParameterToList('LAST_UPDATED_BY',
3667 to_char(SetUserAttr.last_updated_by,
3668 WF_CORE.canonical_number_mask), l_params);
3669 WF_EVENT.AddParameterToList('LAST_UPDATE_DATE',
3670 to_char(SetUserAttr.last_update_date,
3671 WF_CORE.canonical_date_mask), l_params);
3672 WF_EVENT.AddParameterToList('LAST_UPDATE_LOGIN',
3673 to_char(SetUserAttr.last_update_login ,
3674 WF_CORE.canonical_number_mask), l_params);
3675 WF_EVENT.AddParameterToList('CREATED_BY', to_char(SetUserAttr.created_by ,
3676 WF_CORE.canonical_number_mask), l_params);
3677 WF_EVENT.AddParameterToList('CREATION_DATE',
3678 to_char(SetUserAttr.creation_date,
3679 WF_CORE.canonical_date_mask), l_params);
3680 if (eventParams is not null and eventParams.count>0) then
3681 for i in eventParams.first..eventParams.last loop
3682 WF_EVENT.AddParameterToList(upper(eventParams(i).getName()),
3683 eventParams(i).getValue(),l_params);
3684 end loop;
3685 end if;
3686
3687 WF_EVENT.Raise(p_event_name=>'oracle.apps.fnd.wf.ds.user.updated',
3688 p_event_key=>user_name, p_parameters=>l_params);
3689
3690 exception
3691 when others then
3692 wf_core.context('Wf_Directory', 'SetUserAttr', user_name, display_name);
3693 raise;
3694 end SetUserAttr;
3695
3696 --
3697 -- SetRoleAttr (PRIVATE)
3698 -- Update additional attributes for roles
3699 -- IN
3700 -- role_name - role name
3701 -- orig_system -
3702 -- orig_system_id -
3703 -- display_name -
3704 -- notification_preference -
3705 -- language -
3706 -- territory -
3707 -- email_address -
3708 -- fax -
3709 -- expiration_date - New expiration date
3710 -- status - status could be 'ACTIVE' or 'INACTIVE'
3711 -- OUT
3712 --
3713 procedure SetRoleAttr(role_name in varchar2,
3714 orig_system in varchar2,
3715 orig_system_id in number,
3716 display_name in varchar2,
3717 description in varchar2,
3718 notification_preference in varchar2,
3719 language in varchar2,
3720 territory in varchar2,
3721 email_address in varchar2,
3722 fax in varchar2,
3723 expiration_date in date,
3724 status in varchar2,
3725 start_date in date,
3726 OverWrite in boolean,
3727 Parent_Orig_System in varchar2,
3728 Parent_Orig_System_ID in number,
3729 owner_tag in varchar2,
3730 last_updated_by in number,
3731 last_update_date in date,
3732 last_update_login in number,
3733 created_by in number,
3734 creation_date in date,
3735 eventParams in wf_parameter_list_t)
3736 is
3737
3738 l_expiration DATE;
3739 l_params WF_PARAMETER_LIST_T;
3740 l_oldStartDate DATE;
3741 l_oldEndDate DATE;
3742
3743 l_creatdt date;
3744 l_lastupddt date;
3745 l_creatby number;
3746 l_lastupdby number;
3747 l_lastupdlog number;
3748 l_partitionID number;
3749 l_partitionName varchar2(30);
3750
3751 begin
3752 --We first need to capture the current start/end date in case they are
3753 --changed.
3754 AssignPartition(SetRoleAttr.orig_system, l_partitionID, l_partitionName);
3755
3756 begin
3757 SELECT START_DATE, EXPIRATION_DATE
3758 INTO l_oldStartDate, l_oldEndDate
3759 FROM WF_LOCAL_ROLES
3760 WHERE NAME = SetRoleAttr.role_name
3761 AND ORIG_SYSTEM = SetRoleAttr.orig_system
3762 AND ORIG_SYSTEM_ID = SetRoleAttr.orig_system_id
3763 AND PARTITION_ID = l_partitionID;
3764 exception
3765 when NO_DATA_FOUND then
3766 WF_CORE.Token('ROLENAME', role_name);
3767 WF_CORE.Raise('WF_INVALID_ROLE');
3768 end;
3769 --
3770
3771
3772 -- Evaluating the WHO columns in case they are not passed
3773 l_creatdt := nvl(SetRoleAttr.creation_date, SYSDATE);
3774 l_creatby := nvl(SetRoleAttr.created_by, WFA_SEC.USER_ID);
3775 l_lastupdby := nvl(SetRoleAttr.last_updated_by, WFA_SEC.USER_ID);
3776 l_lastupddt := nvl(SetRoleAttr.last_update_date, SYSDATE);
3777 l_lastupdlog:= nvl(SetRoleAttr.last_update_login, WFA_SEC.LOGIN_ID);
3778
3779
3780 -- Update WF_LOCAL_ROLES
3781 --
3782 if (OverWrite) then
3783 --Update the description field and display name field
3784 --in the base table only if the session language is 'US'
3785 --Else update theses values for the _TL table and keep the
3786 --base table values same
3787 if ((userenv('LANG') = 'US') OR
3788 (isMLSEnabled(SetRoleAttr.orig_system) = FALSE)) then
3789 update WF_LOCAL_ROLES
3790 set NOTIFICATION_PREFERENCE = nvl(SetRoleAttr.notification_preference,
3791 NOTIFICATION_PREFERENCE),
3792 LANGUAGE = nvl(SetRoleAttr.language, LANGUAGE),
3793 TERRITORY = nvl(SetRoleAttr.territory, TERRITORY),
3794 EMAIL_ADDRESS = SetRoleAttr.email_address,
3795 FAX = SetRoleAttr.fax,
3796 DISPLAY_NAME = nvl(SetRoleAttr.display_name,
3797 DISPLAY_NAME),
3798 DESCRIPTION = SetRoleAttr.description,
3799 EXPIRATION_DATE = SetRoleAttr.expiration_date,
3800 STATUS = nvl(SetRoleAttr.status, STATUS),
3801 START_DATE = SetRoleAttr.start_date,
3802 PARENT_ORIG_SYSTEM = SetRoleAttr.parent_orig_system,
3803 PARENT_ORIG_SYSTEM_ID = SetRoleAttr.parent_orig_system_id,
3804 OWNER_TAG = nvl(SetRoleAttr.owner_tag,
3805 OWNER_TAG),
3806 LAST_UPDATED_BY = l_lastupdby,
3807 LAST_UPDATE_DATE = l_lastupddt,
3808 LAST_UPDATE_LOGIN = l_lastupdlog
3809 -- <7298384> always keep CREATED_BY and CREATION_DATE in update
3810 -- CREATED_BY = nvl(SetRoleAttr.created_by,
3811 -- created_by),
3812 -- CREATION_DATE = nvl(SetRoleAttr.creation_date,
3813 -- creation_date) -- </7298384>
3814 where NAME = role_name
3815 and ORIG_SYSTEM = SetRoleAttr.orig_system
3816 and ORIG_SYSTEM_ID = SetRoleAttr.orig_system_id
3817 and PARTITION_ID = l_partitionID;
3818
3819 --Bug 3490260
3820 --lets keep the code here rather than end for better understanding
3821 --If the role information was not updated, we need to raise an
3822 --invalid role error so the caller can call the CreateRole api.
3823 if (sql%rowcount = 0) then
3824 WF_CORE.Token('ROLENAME', role_name);
3825 WF_CORE.Raise('WF_INVALID_ROLE');
3826 end if;
3827
3828 else
3829 update WF_LOCAL_ROLES
3830 set NOTIFICATION_PREFERENCE = nvl(SetRoleAttr.notification_preference,
3831 NOTIFICATION_PREFERENCE),
3832 LANGUAGE = nvl(SetRoleAttr.language, LANGUAGE),
3833 TERRITORY = nvl(SetRoleAttr.territory, TERRITORY),
3834 EMAIL_ADDRESS = SetRoleAttr.email_address,
3835 FAX = SetRoleAttr.fax,
3836 EXPIRATION_DATE = SetRoleAttr.expiration_date,
3837 STATUS = nvl(SetRoleAttr.status, STATUS),
3838 START_DATE = SetRoleAttr.start_date,
3839 PARENT_ORIG_SYSTEM = SetRoleAttr.parent_orig_system,
3840 PARENT_ORIG_SYSTEM_ID = SetRoleAttr.parent_orig_system_id,
3841 OWNER_TAG = nvl(SetRoleAttr.owner_tag,
3842 OWNER_TAG),
3843 LAST_UPDATED_BY = l_lastupdby,
3844 LAST_UPDATE_DATE = l_lastupddt,
3845 LAST_UPDATE_LOGIN = l_lastupdlog
3846 -- <7298384> always keep CREATED_BY and CREATION_DATE in update
3847 -- CREATED_BY = nvl(SetRoleAttr.created_by,
3848 -- created_by),
3849 -- CREATION_DATE = nvl(SetRoleAttr.creation_date,
3850 -- creation_date) -- </7298384>
3851
3852 where NAME = role_name
3853 and ORIG_SYSTEM = SetRoleAttr.orig_system
3854 and ORIG_SYSTEM_ID = SetRoleAttr.orig_system_id
3855 and PARTITION_ID = l_partitionID;
3856
3857 --If the base role information was not updated, we need to raise an
3858 --invalid role error so the caller can call the CreateRole api.
3859 if (sql%rowcount = 0) then
3860 WF_CORE.Token('ROLENAME', role_name);
3861 WF_CORE.Raise('WF_INVALID_ROLE');
3862 end if;
3863
3864 --Update the _TL table for the display_name and
3865 --description
3866 update WF_LOCAL_ROLES_TL
3867 set DISPLAY_NAME = nvl(SetRoleAttr.display_name,
3868 DISPLAY_NAME),
3869 DESCRIPTION = SetRoleAttr.description,
3870 OWNER_TAG = nvl(SetRoleAttr.owner_tag,
3871 OWNER_TAG),
3872 LAST_UPDATED_BY = l_lastupdby,
3873 LAST_UPDATE_DATE = l_lastupddt,
3874 LAST_UPDATE_LOGIN = l_lastupdlog
3875 -- <7298384> always keep CREATED_BY and CREATION_DATE in update
3876 -- CREATED_BY = nvl(SetRoleAttr.created_by,
3877 -- created_by),
3878 -- CREATION_DATE = nvl(SetRoleAttr.creation_date,
3879 -- creation_date) -- </7298384>
3880
3881 where NAME = role_name
3882 and ORIG_SYSTEM = SetRoleAttr.orig_system
3883 and ORIG_SYSTEM_ID = SetRoleAttr.orig_system_id
3884 and PARTITION_ID = l_partitionID;
3885
3886 if (sql%rowcount = 0) then
3887 --No record exist for this lang in _TL table
3888 --so insert a record
3889 --The issue is that if the passed values or param
3890 --are null we would be inserting 'US' language
3891 --display name as its non-nullable was max we can do is to
3892 --add a 'NON_TRANSLATED' string or something to recognize.
3893
3894 insert into WF_LOCAL_ROLES_TL (NAME,
3895 DISPLAY_NAME,
3896 DESCRIPTION,
3897 ORIG_SYSTEM,
3898 ORIG_SYSTEM_ID,
3899 PARTITION_ID,
3900 LANGUAGE,
3901 OWNER_TAG,
3902 CREATED_BY,
3903 CREATION_DATE,
3904 LAST_UPDATED_BY,
3905 LAST_UPDATE_DATE,
3906 LAST_UPDATE_LOGIN )
3907 (select name,
3908 nvl(SetRoleAttr.display_name, DISPLAY_NAME) ,
3909 SetRoleAttr.description,
3910 ORIG_SYSTEM,
3911 ORIG_SYSTEM_ID,
3912 PARTITION_ID,
3913 userenv('LANG'),
3914 nvl(SetRoleAttr.owner_tag, OWNER_TAG),
3915 l_creatby,
3916 l_creatdt,
3917 l_lastupdby,
3918 l_lastupddt,
3919 l_lastupdlog
3920 from WF_LOCAL_ROLES
3921 where NAME = role_name
3922 and ORIG_SYSTEM = SetRoleAttr.orig_system
3923 and ORIG_SYSTEM_ID = SetRoleAttr.orig_system_id
3924 and PARTITION_ID = l_partitionID);
3925
3926
3927 --Now update the table for relevant data
3928
3929 end if;
3930
3931 end if;
3932 else --(NOT overWrite)
3933 if ((userenv('LANG') = 'US') OR
3934 (wf_directory.isMLSEnabled(SetRoleAttr.orig_system) = FALSE)) then
3935 update WF_LOCAL_ROLES
3936 set NOTIFICATION_PREFERENCE = nvl(SetRoleAttr.notification_preference,
3937 NOTIFICATION_PREFERENCE),
3938 LANGUAGE = nvl(SetRoleAttr.language, LANGUAGE),
3939 TERRITORY = nvl(SetRoleAttr.territory, TERRITORY),
3940 EMAIL_ADDRESS = nvl(SetRoleAttr.email_address, EMAIL_ADDRESS),
3941 FAX = nvl(SetRoleAttr.fax, FAX),
3942 DISPLAY_NAME = nvl(SetRoleAttr.display_name, DISPLAY_NAME),
3943 DESCRIPTION = nvl(SetRoleAttr.description, DESCRIPTION),
3944 EXPIRATION_DATE = nvl(SetRoleAttr.expiration_date, EXPIRATION_DATE),
3945 STATUS = nvl(SetRoleAttr.status, STATUS),
3946 START_DATE = nvl(SetRoleAttr.start_date, START_DATE),
3947 PARENT_ORIG_SYSTEM = nvl(SetRoleAttr.parent_orig_system,
3948 PARENT_ORIG_SYSTEM),
3949 PARENT_ORIG_SYSTEM_ID = nvl(SetRoleAttr.parent_orig_system_id,
3950 PARENT_ORIG_SYSTEM_ID),
3951 OWNER_TAG = nvl(SetRoleAttr.owner_tag,
3952 OWNER_TAG)
3953 -- <7298384> no overwrite mode, so keeping previous values
3954 -- LAST_UPDATED_BY = l_lastupdby,
3955 -- LAST_UPDATE_DATE = l_lastupddt,
3956 -- LAST_UPDATE_LOGIN = l_lastupdlog,
3957 -- CREATED_BY = nvl(SetRoleAttr.created_by,
3958 -- created_by),
3959 -- CREATION_DATE = nvl(SetRoleAttr.creation_date,
3960 -- creation_date) -- </7298384>
3961
3962 where NAME = role_name
3963 and ORIG_SYSTEM = SetRoleAttr.orig_system
3964 and ORIG_SYSTEM_ID = SetRoleAttr.orig_system_id
3965 and PARTITION_ID = l_partitionID;
3966
3967 --Bug 3490260
3968 --lets keep the code here rather than end for better understanding
3969 --If the role information was not updated, we need to raise an
3970 --invalid role error so the caller can call the CreateRole api.
3971 if (sql%rowcount = 0) then
3972 WF_CORE.Token('ROLENAME', role_name);
3973 WF_CORE.Raise('WF_INVALID_ROLE');
3974 end if;
3975
3976 else
3977 update WF_LOCAL_ROLES
3978 set NOTIFICATION_PREFERENCE = nvl(SetRoleAttr.notification_preference,
3979 NOTIFICATION_PREFERENCE),
3980 LANGUAGE = nvl(SetRoleAttr.language, LANGUAGE),
3981 TERRITORY = nvl(SetRoleAttr.territory, TERRITORY),
3982 EMAIL_ADDRESS = nvl(SetRoleAttr.email_address, EMAIL_ADDRESS),
3983 FAX = nvl(SetRoleAttr.fax, FAX),
3984 EXPIRATION_DATE = nvl(SetRoleAttr.expiration_date, EXPIRATION_DATE),
3985 STATUS = nvl(SetRoleAttr.status, STATUS),
3986 START_DATE = nvl(SetRoleAttr.start_date, START_DATE),
3987 PARENT_ORIG_SYSTEM = nvl(SetRoleAttr.parent_orig_system,
3988 PARENT_ORIG_SYSTEM),
3989 PARENT_ORIG_SYSTEM_ID = nvl(SetRoleAttr.parent_orig_system_id,
3990 PARENT_ORIG_SYSTEM_ID),
3991 OWNER_TAG = nvl(SetRoleAttr.owner_tag,
3992 OWNER_TAG)
3993 -- <7298384> no overwrite mode, so keeping previous values
3994 -- LAST_UPDATED_BY = l_lastupdby,
3995 -- LAST_UPDATE_DATE = l_lastupddt,
3996 -- LAST_UPDATE_LOGIN = l_lastupdlog,
3997 -- CREATED_BY = nvl(SetRoleAttr.created_by,
3998 -- created_by),
3999 -- CREATION_DATE = nvl(SetRoleAttr.creation_date,
4000 -- creation_date) -- </7298384>
4001
4002 where NAME = role_name
4003 and ORIG_SYSTEM = SetRoleAttr.orig_system
4004 and ORIG_SYSTEM_ID = SetRoleAttr.orig_system_id
4005 and PARTITION_ID = l_partitionID;
4006
4007 --If the base role information was not updated, we need to raise an
4008 --invalid role error so the caller can call the CreateRole api.
4009 if (sql%rowcount = 0) then
4010 WF_CORE.Token('ROLENAME', role_name);
4011 WF_CORE.Raise('WF_INVALID_ROLE');
4012 end if;
4013
4014 --Update the _TL table for the display_name and
4015 --description
4016 update WF_LOCAL_ROLES_TL
4017 set DISPLAY_NAME = nvl(SetRoleAttr.display_name,
4018 DISPLAY_NAME),
4019 DESCRIPTION = nvl(SetRoleAttr.description,DESCRIPTION),
4020 OWNER_TAG = nvl(SetRoleAttr.owner_tag,
4021 OWNER_TAG)
4022 -- <7298384> no overwrite mode, so keeping previous values
4023 -- LAST_UPDATED_BY = l_lastupdby,
4024 -- LAST_UPDATE_DATE = l_lastupddt,
4025 -- LAST_UPDATE_LOGIN = l_lastupdlog,
4026 -- CREATED_BY = nvl(SetRoleAttr.created_by,
4027 -- created_by),
4028 -- CREATION_DATE = nvl(SetRoleAttr.creation_date,
4029 -- creation_date) -- </7298384>
4030
4031 where NAME = role_name
4032 and ORIG_SYSTEM = SetRoleAttr.orig_system
4033 and ORIG_SYSTEM_ID = SetRoleAttr.orig_system_id
4034 and PARTITION_ID = l_partitionID;
4035
4036 if (sql%rowcount = 0) then
4037 --No record exist for this lang in _TL table
4038 --so insert a record
4039 --The issue is that if the passed values or param
4040 --are null we would be inserting 'US' language
4041 --display name as its non-nullable
4042
4043 insert into WF_LOCAL_ROLES_TL (NAME,
4044 DISPLAY_NAME,
4045 DESCRIPTION,
4046 ORIG_SYSTEM,
4047 ORIG_SYSTEM_ID,
4048 PARTITION_ID,
4049 LANGUAGE,
4050 OWNER_TAG,
4051 CREATED_BY,
4052 CREATION_DATE,
4053 LAST_UPDATED_BY,
4054 LAST_UPDATE_DATE,
4055 LAST_UPDATE_LOGIN )
4056 (select name,
4057 nvl(SetRoleAttr.display_name, DISPLAY_NAME),
4058 SetRoleAttr.description,
4059 ORIG_SYSTEM,
4060 ORIG_SYSTEM_ID ,
4061 PARTITION_ID,
4062 userenv('LANG'),
4063 nvl(SetRoleAttr.owner_tag, OWNER_TAG),
4064 l_creatby,
4065 l_creatdt,
4066 l_lastupdby,
4067 l_lastupddt,
4068 l_lastupdlog
4069 from WF_LOCAL_ROLES
4070 where NAME = SetRoleAttr.role_name
4071 and ORIG_SYSTEM = SetRoleAttr.orig_system
4072 and ORIG_SYSTEM_ID = SetRoleAttr.orig_system_id
4073 and PARTITION_ID = l_partitionID);
4074
4075 end if;
4076
4077
4078 end if;
4079 end if;
4080
4081 --We were able to update the record, so we will raise the updated event
4082 --Build parameter list.
4083 WF_EVENT.AddParameterToList('ROLE_NAME', SetRoleAttr.role_name, l_params);
4084 WF_EVENT.AddParameterToList('ORIG_SYSTEM', SetRoleAttr.orig_system,
4085 l_params);
4086 WF_EVENT.AddParameterToList('ORIG_SYSTEM_ID',
4087 to_char(SetRoleAttr.orig_system_id), l_params);
4088 WF_EVENT.AddParameterToList('DISPLAY_NAME', SetRoleAttr.display_name,
4089 l_params);
4090 WF_EVENT.AddParameterToList('DESCRIPTION', SetRoleAttr.description,
4091 l_params);
4092 WF_EVENT.AddParameterToList('NOTIFICATION_PREFERENCE',
4093 SetRoleAttr.notification_preference, l_params);
4094 WF_EVENT.AddParameterToList('LANGUAGE', SetRoleAttr.language, l_params);
4095 WF_EVENT.AddParameterToList('TERRITORY', SetRoleAttr.territory, l_params);
4096 WF_EVENT.AddParameterToList('EMAIL_ADDRESS', SetRoleAttr.email_address,
4097 l_params);
4098 WF_EVENT.AddParameterToList('FAX', SetRoleAttr.fax, l_params);
4099 WF_EVENT.AddParameterToList('EXPIRATION_DATE',
4100 to_char(trunc(SetRoleAttr.expiration_date),
4101 WF_CORE.Canonical_Date_Mask),
4102 l_params);
4103 WF_EVENT.AddParameterToList('STATUS', SetRoleAttr.status, l_params);
4104 WF_EVENT.AddParameterToList('START_DATE',
4105 to_char(trunc(SetRoleAttr.start_date),
4106 WF_CORE.Canonical_Date_Mask),
4107 l_params);
4108 if ((((l_oldStartDate is NOT NULL) and
4109 (SetRoleAttr.start_date is NOT NULL)) and
4110 (trunc(l_oldStartDate) <> trunc(SetRoleAttr.start_date))) or
4111 ((l_oldStartDate is NULL and SetRoleAttr.start_date is NOT NULL) or
4112 (SetRoleAttr.start_date is NULL and l_oldStartDate is NOT NULL))) then
4113
4114 WF_EVENT.AddParameterToList('OLD_START_DATE',
4115 to_char(trunc(l_oldStartDate),
4116 WF_CORE.Canonical_Date_Mask),
4117 l_params);
4118 else
4119 WF_EVENT.AddParameterToList('OLD_START_DATE', '*UNDEFINED*',
4120 l_params);
4121 end if;
4122
4123 if ((((l_oldEndDate is NOT NULL) and
4124 (SetRoleAttr.expiration_date is NOT NULL)) and
4125 (trunc(l_oldEndDate) <> trunc(SetRoleAttr.expiration_date))) or
4126 ((l_oldEndDate is NULL and SetRoleAttr.expiration_date is NOT NULL)
4127 or (SetRoleAttr.expiration_date is NULL
4128 and l_oldEndDate is NOT NULL))) then
4129
4130 WF_EVENT.AddParameterToList('OLD_END_DATE',
4131 to_char(trunc(l_oldEndDate),
4132 WF_CORE.Canonical_Date_Mask),
4133 l_params);
4134 else
4135 WF_EVENT.AddParameterToList('OLD_END_DATE', '*UNDEFINED*',
4136 l_params);
4137 end if;
4138
4139 WF_EVENT.AddParameterToList('PARENT_ORIG_SYSTEM',
4140 SetRoleAttr.parent_orig_system, l_params);
4141 WF_EVENT.AddParameterToList('PARENT_ORIG_SYSTEM_ID',
4142 to_char(SetRoleAttr.parent_orig_system_id ,
4143 WF_CORE.canonical_number_mask),l_params);
4144
4145 WF_EVENT.AddParameterToList('OWNER_TAG', SetRoleAttr.owner_tag, l_params);
4146 WF_EVENT.AddParameterToList('LAST_UPDATED_BY',
4147 to_char(SetRoleAttr.last_updated_by ,
4148 WF_CORE.canonical_number_mask), l_params);
4149 WF_EVENT.AddParameterToList('LAST_UPDATE_DATE',
4150 to_char(SetRoleAttr.last_update_date,
4151 WF_CORE.canonical_date_mask), l_params);
4152 WF_EVENT.AddParameterToList('LAST_UPDATE_LOGIN',
4153 to_char(SetRoleAttr.last_update_login ,
4154 WF_CORE.canonical_number_mask), l_params);
4155 if (eventParams is not null and eventParams.count>0) then
4156 for i in eventParams.first..eventParams.last loop
4157 WF_EVENT.AddParameterToList(upper(eventParams(i).getName()),
4158 eventParams(i).getValue(),l_params);
4159 end loop;
4160 end if;
4161 WF_EVENT.Raise(p_event_name=>'oracle.apps.fnd.wf.ds.role.updated',
4162 p_event_key=>role_name, p_parameters=>l_params);
4163
4164 exception
4165 when others then
4166 wf_core.context('Wf_Directory', 'SetRoleAttr', SetRoleAttr.role_name,
4167 SetRoleAttr.display_name);
4168 raise;
4169 end SetRoleAttr;
4170
4171 --
4172 -- SetAdHocUserExpiration
4173 -- Update expiration date for ad hoc users
4174 -- IN
4175 -- user_name - Ad hoc user name
4176 -- expiration_date - New expiration date
4177 -- OUT
4178 --
4179 procedure SetAdHocUserExpiration(user_name in varchar2,
4180 expiration_date in date)
4181 is
4182 begin
4183 --
4184 -- Update Expiration Date
4185 --
4186 SetUserAttr(user_name=>SetAdHocUserExpiration.user_name,
4187 orig_system=>'WF_LOCAL_USERS',
4188 orig_system_id=>0,
4189 display_name=>NULL,
4190 notification_preference=>NULL,
4191 language=>NULL,
4192 territory=>NULL,
4193 email_address=>NULL,
4194 fax=>NULL,
4195 expiration_date=>SetAdHocUserExpiration.expiration_date,
4196 status=>NULL);
4197
4198 exception
4199 when others then
4200 wf_core.context('Wf_Directory', 'SetAdHocUserExpiration', user_name, expiration_date);
4201 raise;
4202 end SetAdHocUserExpiration;
4203
4204 --
4205 -- SetAdHocRoleExpiration
4206 -- Update expiration date for ad hoc roles, user roles
4207 -- IN
4208 -- role_name - Ad hoc role name
4209 -- expiration_date - New expiration date
4210 -- OUT
4211 --
4212 procedure SetAdHocRoleExpiration(role_name in varchar2,
4213 expiration_date in date)
4214 is
4215 begin
4216 --
4217 -- Update Expiration Date
4218 --
4219 SetRoleAttr(role_name=>SetAdHocRoleExpiration.role_name,
4220 orig_system=>'WF_LOCAL_ROLES',
4221 orig_system_id=>0,
4222 display_name=>NULL,
4223 notification_preference=>NULL,
4224 language=>NULL,
4225 territory=>NULL,
4226 email_address=>NULL,
4227 fax=>NULL,
4228 expiration_date=>SetAdHocRoleExpiration.expiration_date,
4229 status=>NULL);
4230
4231 exception
4232 when others then
4233 wf_core.context('Wf_Directory', 'SetAdHocRoleExpiration', role_name,
4234 expiration_date);
4235 raise;
4236 end SetAdHocRoleExpiration;
4237
4238 --
4239 -- SetAdHocUserAttr
4240 -- Update additional attributes for ad hoc users
4241 -- IN
4242 -- user_name - Ad hoc user name
4243 -- display_name -
4244 -- notification_preference -
4245 -- language -
4246 -- territory -
4247 -- email_address -
4248 -- fax -
4249 -- OUT
4250 --
4251 procedure SetAdHocUserAttr(user_name in varchar2,
4252 display_name in varchar2,
4253 notification_preference in varchar2,
4254 language in varchar2,
4255 territory in varchar2,
4256 email_address in varchar2,
4257 fax in varchar2,
4258 parent_orig_system in varchar2,
4259 parent_orig_system_id in number,
4260 owner_tag in varchar2)
4261 is
4262 begin
4263 --
4264 -- Update the user.
4265 --
4266 SetUserAttr(user_name=>SetAdHocUserAttr.user_name,
4267 orig_system=>'WF_LOCAL_USERS',
4268 orig_system_id=>0,
4269 display_name=>SetAdHocUserAttr.display_name,
4270 notification_preference=>SetAdHocUserAttr.notification_preference,
4271 language=>SetAdHocUserAttr.language,
4272 territory=>SetAdHocUserAttr.territory,
4273 email_address=>SetAdHocUserAttr.email_address,
4274 fax=>SetAdHocUserAttr.fax,
4275 expiration_date=>NULL,
4276 status=>NULL,
4277 parent_orig_system=>SetAdHocUserAttr.parent_orig_system,
4278 parent_orig_system_id=>SetAdHocUserAttr.parent_orig_system_id,
4279 owner_tag=>SetAdhocUserAttr.owner_tag);
4280
4281 exception
4282 when others then
4283 wf_core.context('Wf_Directory', 'SetAdHocUserAttr', user_name,
4284 display_name);
4285 raise;
4286 end SetAdHocUserAttr;
4287
4288 --
4289 -- SetAdHocRoleAttr
4290 -- Update additional attributes for ad hoc roles, user roles
4291 -- IN
4292 -- role_name - Ad hoc role name
4293 -- display_name -
4294 -- notification_preference -
4295 -- language -
4296 -- territory -
4297 -- email_address -
4298 -- fax -
4299 -- OUT
4300 --
4301 procedure SetAdHocRoleAttr(role_name in varchar2,
4302 display_name in varchar2,
4303 notification_preference in varchar2,
4304 language in varchar2,
4305 territory in varchar2,
4306 email_address in varchar2,
4307 fax in varchar2,
4308 parent_orig_system in varchar2,
4309 parent_orig_system_id in number,
4310 owner_tag in varchar2)
4311 is
4312 begin
4313 --
4314 -- Update the role
4315 --
4316 SetRoleAttr(role_name=>SetAdHocRoleAttr.role_name,
4317 orig_system=>'WF_LOCAL_ROLES',
4318 orig_system_id=>0,
4319 display_name=>SetAdHocRoleAttr.display_name,
4320 notification_preference=>SetAdHocRoleAttr.notification_preference,
4321 language=>SetAdHocRoleAttr.language,
4322 territory=>SetAdHocRoleAttr.territory,
4323 email_address=>SetAdHocRoleAttr.email_address,
4324 fax=>fax,
4325 expiration_date=>NULL,
4326 status=>NULL,
4327 parent_orig_system=>SetAdHocRoleAttr.parent_orig_system,
4328 parent_orig_system_id=>SetAdHocRoleAttr.parent_orig_system_id,
4329 owner_tag=>SetAdHocRoleAttr.owner_tag);
4330
4331 exception
4332 when others then
4333 wf_core.context('Wf_Directory', 'SetAdHocRoleAttr', role_name,
4334 display_name);
4335 raise;
4336 end SetAdHocRoleAttr;
4337
4338 --
4339 -- RemoveUsersFromAdHocRole
4340 -- Remove users from an existing ad hoc role
4341 -- IN
4342 -- role_name -
4343 -- role_users -
4344 -- OUT
4345 --
4346 procedure RemoveUsersFromAdHocRole(role_name in varchar2,
4347 role_users in varchar2)
4348 is
4349 user varchar2(320);
4350 rest varchar2(2000);
4351 c1 pls_integer;
4352 begin
4353 if (role_users is null) then
4354 -- Delete all users
4355 begin
4356 delete from WF_LOCAL_USER_ROLES UR
4357 where UR.ROLE_NAME = RemoveUsersFromAdHocRole.role_name
4358 and UR.ROLE_ORIG_SYSTEM = 'WF_LOCAL_ROLES'
4359 and UR.ROLE_ORIG_SYSTEM_ID = 0
4360 and UR.PARTITION_ID = 0;
4361
4362 --delete from WF_USER_ROLE_ASSIGNMENTS as well
4363 delete from WF_USER_ROLE_ASSIGNMENTS URA
4364 where URA.ROLE_NAME = RemoveUsersFromAdHocRole.role_name
4365 and URA.ROLE_ORIG_SYSTEM = 'WF_LOCAL_ROLES'
4366 and URA.ROLE_ORIG_SYSTEM_ID = 0
4367 and URA.PARTITION_ID = 0;
4368 end;
4369 else
4370 --
4371 -- Delete Users
4372 --
4373 rest := ltrim(role_users);
4374 loop
4375 c1 := instr(rest, ',');
4376 if (c1 = 0) then
4377 c1 := instr(rest, ' ');
4378 if (c1 = 0) then
4379 user := rest;
4380 else
4381 user := substr(rest, 1, c1-1);
4382 end if;
4383 else
4384 user := substr(rest, 1, c1-1);
4385 end if;
4386
4387 -- Delete
4388 delete from WF_LOCAL_USER_ROLES UR
4389 where UR.USER_NAME = user
4390 and UR.ROLE_NAME = RemoveUsersFromAdHocRole.role_name
4391 and UR.ROLE_ORIG_SYSTEM = 'WF_LOCAL_ROLES'
4392 and UR.ROLE_ORIG_SYSTEM_ID = 0
4393 and UR.PARTITION_ID = 0;
4394 if (sql%rowcount = 0) then
4395 wf_core.token('USERNAME', user);
4396 wf_core.raise('WF_INVALID_USER');
4397 end if;
4398
4399 -- Delete from wf_user_role_Assignments as well
4400 delete from WF_USER_ROLE_ASSIGNMENTS URA
4401 where URA.USER_NAME = user
4402 and URA.ROLE_NAME = RemoveUsersFromAdHocRole.role_name
4403 and URA.ROLE_ORIG_SYSTEM = 'WF_LOCAL_ROLES'
4404 and URA.ROLE_ORIG_SYSTEM_ID = 0
4405 and URA.PARTITION_ID = 0;
4406 exit when (c1 = 0);
4407
4408 rest := ltrim(substr(rest, c1+1));
4409 end loop;
4410 end if;
4411
4412 exception
4413 when others then
4414 wf_core.context('Wf_Directory', 'RemoveUsersFromAdHocRole',
4415 role_name, '"'||role_users||'"');
4416 raise;
4417 end RemoveUsersFromAdHocRole;
4418
4419
4420 --
4421 -- ChangeLocalUserName
4422 -- Change a User's Name in the WF_LOCAL_ROLES table.
4423 -- IN
4424 -- OldName
4425 -- NewName
4426 -- Propagate - call WF_MAINTENANCE.PropagateChangedName
4427 -- OUT
4428 --
4429
4430 function ChangeLocalUserName (OldName in varchar2,
4431 NewName in varchar2,
4432 Propagate in boolean)
4433 return boolean
4434
4435 is
4436 NumRows pls_integer;
4437 l_oldname varchar2(320);
4438 l_newname varchar2(320);
4439
4440 begin
4441 l_newname := substrb(NewName,1,320);
4442 l_oldname := substrb(OldName,1,320);
4443
4444 NumRows := wfa_sec.DS_Count_Local_Role(l_oldname);
4445
4446 if (NumRows = 1) then
4447 wfa_sec.DS_Update_Local_Role(l_oldname,l_newname);
4448 commit;
4449
4450 if (Propagate) then
4451 WF_MAINTENANCE.PropagateChangedName(l_oldname, l_newname);
4452 end if;
4453
4454 return TRUE;
4455
4456 else
4457
4458 return FALSE;
4459
4460 end if;
4461
4462 exception
4463 when others then
4464 WF_CORE.Context('WF_DIRECTORY', 'ChangeLocalUserName', OldName, NewName);
4465 raise;
4466
4467 end ChangeLocalUserName;
4468
4469 --
4470 -- ReassignUserRoles
4471 -- Reassigns user/roles when the user information changes.
4472 -- IN
4473 -- p_user_name
4474 -- p_old_user_origSystem
4475 -- p_old_user_origSystemID
4476 -- p_new_user_origSystem
4477 -- p_new_user_origSystemID
4478 -- p_last_update_date
4479 -- p_last_updated_by
4480 -- p_last_update_login
4481 --
4482 -- OUT
4483 --
4484 procedure ReassignUserRoles (p_user_name in VARCHAR2,
4485 p_old_user_origSystem in VARCHAR2,
4486 p_old_user_origSystemID in VARCHAR2,
4487 p_new_user_origSystem in VARCHAR2,
4488 p_new_user_origSystemID in VARCHAR2,
4489 p_last_update_date in DATE,
4490 p_last_updated_by in NUMBER,
4491 p_last_update_login in NUMBER
4492 -- <6817561>
4493 , p_overWriteUserRoles in boolean
4494 -- </6817561>
4495 ) is
4496
4497 l_overWriteUserRoles varchar2(2) := 'N';
4498 l_api varchar2(250) := g_plsqlName ||'ReassignUserRoles';
4499
4500 BEGIN
4501 if(wf_log_pkg.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
4502 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE, l_api,'BEGIN');
4503 end if;
4504
4505 -- <6817561>
4506 if (p_overWriteUserRoles) then
4507 l_overWriteUserRoles := 'Y';
4508 end if; -- </6817561>
4509 --First Update the user self-reference.
4510 begin
4511 Update WF_USER_ROLE_ASSIGNMENTS
4512 Set USER_ORIG_SYSTEM = p_new_user_origSystem,
4513 USER_ORIG_SYSTEM_ID = p_new_user_origSystemID,
4514 ROLE_ORIG_SYSTEM = p_new_user_origSystem,
4515 ROLE_ORIG_SYSTEM_ID = p_new_user_origSystemID,
4516 -- <6817561>
4517 LAST_UPDATE_DATE = decode(l_overWriteUserRoles,'Y', nvl(p_last_update_date, LAST_UPDATE_DATE), LAST_UPDATE_DATE),
4518 LAST_UPDATED_BY = decode(l_overWriteUserRoles,'Y', nvl(p_last_updated_by, LAST_UPDATED_BY), LAST_UPDATED_BY),
4519 LAST_UPDATE_LOGIN = decode(l_overWriteUserRoles,'Y', nvl(p_last_update_login, LAST_UPDATE_LOGIN), LAST_UPDATE_LOGIN)
4520 -- </6817561>
4521 Where USER_ORIG_SYSTEM = p_old_user_origSystem
4522 And USER_ORIG_SYSTEM_ID = p_old_user_origSystemID
4523 And ROLE_ORIG_SYSTEM = p_old_user_origSystem
4524 And ROLE_ORIG_SYSTEM_ID = p_old_user_origSystemID
4525 And USER_NAME = p_user_name;
4526 exception
4527 when DUP_VAL_ON_INDEX then
4528 --This is an old reference that can be deleted
4529 Delete from WF_USER_ROLE_ASSIGNMENTS
4530 Where USER_ORIG_SYSTEM = p_old_user_origSystem
4531 And USER_ORIG_SYSTEM_ID = p_old_user_origSystemID
4532 And ROLE_ORIG_SYSTEM = p_old_user_origSystem
4533 And ROLE_ORIG_SYSTEM_ID = p_old_user_origSystemID
4534 And USER_NAME = p_user_name;
4535 end;
4536 begin
4537 Update WF_LOCAL_USER_ROLES
4538 Set USER_ORIG_SYSTEM = p_new_user_origSystem,
4539 USER_ORIG_SYSTEM_ID = p_new_user_origSystemID,
4540 ROLE_ORIG_SYSTEM = p_new_user_origSystem,
4541 ROLE_ORIG_SYSTEM_ID = p_new_user_origSystemID,
4542 -- <6817561>
4543 LAST_UPDATE_DATE = decode(l_overWriteUserRoles,'Y', nvl(p_last_update_date, LAST_UPDATE_DATE), LAST_UPDATE_DATE),
4544 LAST_UPDATED_BY = decode(l_overWriteUserRoles,'Y', nvl(p_last_updated_by, LAST_UPDATED_BY), LAST_UPDATED_BY),
4545 LAST_UPDATE_LOGIN = decode(l_overWriteUserRoles,'Y', nvl(p_last_update_login, LAST_UPDATE_LOGIN), LAST_UPDATE_LOGIN)
4546 -- </6817561>
4547 Where USER_ORIG_SYSTEM = p_old_user_origSystem
4548 And USER_ORIG_SYSTEM_ID = p_old_user_origSystemID
4549 And ROLE_ORIG_SYSTEM = p_old_user_origSystem
4550 And ROLE_ORIG_SYSTEM_ID = p_old_user_origSystemID
4551 And USER_NAME = p_user_name;
4552 exception
4553 when DUP_VAL_ON_INDEX then
4554 --This is an old reference that can be deleted
4555 Delete from WF_LOCAL_USER_ROLES
4556 Where USER_ORIG_SYSTEM = p_old_user_origSystem
4557 And USER_ORIG_SYSTEM_ID = p_old_user_origSystemID
4558 And ROLE_ORIG_SYSTEM = p_old_user_origSystem
4559 And ROLE_ORIG_SYSTEM_ID = p_old_user_origSystemID
4560 And USER_NAME = p_user_name;
4561 end;
4562
4563 --Now update all other role references (self-reference is already updated so
4564 --it will not be effected by these updates)
4565 Update WF_LOCAL_USER_ROLES
4566 Set USER_ORIG_SYSTEM = p_new_user_origSystem,
4567 USER_ORIG_SYSTEM_ID = p_new_user_origSystemID,
4568 -- <6817561>
4569 LAST_UPDATE_DATE = decode(l_overWriteUserRoles,'Y', nvl(p_last_update_date, LAST_UPDATE_DATE), LAST_UPDATE_DATE),
4570 LAST_UPDATED_BY = decode(l_overWriteUserRoles,'Y', nvl(p_last_updated_by, LAST_UPDATED_BY), LAST_UPDATED_BY),
4571 LAST_UPDATE_LOGIN = decode(l_overWriteUserRoles,'Y', nvl(p_last_update_login, LAST_UPDATE_LOGIN), LAST_UPDATE_LOGIN)
4572 -- </6817561>
4573 Where USER_ORIG_SYSTEM = p_old_user_origSystem
4574 And USER_ORIG_SYSTEM_ID = p_old_user_origSystemID
4575 And USER_NAME = p_user_name;
4576
4577 Update WF_USER_ROLE_ASSIGNMENTS
4578 Set USER_ORIG_SYSTEM = p_new_user_origSystem,
4579 USER_ORIG_SYSTEM_ID = p_new_user_origSystemID,
4580 -- <6817561>
4581 LAST_UPDATE_DATE = decode(l_overWriteUserRoles,'Y', nvl(p_last_update_date, LAST_UPDATE_DATE), LAST_UPDATE_DATE),
4582 LAST_UPDATED_BY = decode(l_overWriteUserRoles,'Y', nvl(p_last_updated_by, LAST_UPDATED_BY), LAST_UPDATED_BY),
4583 LAST_UPDATE_LOGIN = decode(l_overWriteUserRoles,'Y', nvl(p_last_update_login, LAST_UPDATE_LOGIN), LAST_UPDATE_LOGIN)
4584 -- </6817561>
4585 Where USER_ORIG_SYSTEM = p_old_user_origSystem
4586 And USER_ORIG_SYSTEM_ID = p_old_user_origSystemID
4587 And USER_NAME = p_user_name;
4588
4589 if(wf_log_pkg.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
4590 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE, l_api,'END');
4591 end if;
4592 END ReassignUserRoles;
4593
4594
4595 --
4596 -- AssignPartition (PRIVATE)
4597 --
4598 -- IN
4599 -- p_orig_system (VARCHAR2)
4600 --
4601 -- RETURNS
4602 -- Partition ID (NUMBER)
4603 --
4604 -- COMMENTS
4605 -- This api will check to see the partition for the p_orig_system exists.
4606 -- if it does not exist, it will be added to p_table_name. In either case
4607 -- the Partition_ID will be returned for the calling api to properly populate
4608 -- that column on insert/update.
4609 --
4610 procedure AssignPartition (p_orig_system in varchar2,
4611 p_partitionID out NOCOPY number,
4612 p_partitionName out NOCOPY varchar2) is
4613
4614 begin
4615 begin
4616 --Check for existing partition.
4617 if ((g_origSystem <> UPPER(p_orig_system))) or (g_origSystem is NULL) then
4618
4619 --Orig_systems such as FND_RESP have the application id concatenated
4620 --which makes the responsibilities for various applications fall under
4621 --different orig_systems. However all responsibilities need to go into
4622 --the same partition, so we handle that here. Any other systems that
4623 --we need to bulksynch who add to the orig system will need to be here
4624 --as well.
4625 if ((substr(UPPER(p_orig_system), 1, 8) = 'FND_RESP') and
4626 ((length(p_orig_system) = 8) or --In case we just get 'FND_RESP'
4627 (substr(p_orig_system, 9, 9) between '0' and '9'))) then
4628 g_origSystem := 'FND_RESP';
4629
4630 else
4631 g_origSystem := UPPER(p_orig_system);
4632
4633 end if;
4634 /* We will place PER in FND_USR */
4635
4636 SELECT Partition_ID, orig_system
4637 INTO g_partitionID, g_partitionName
4638 FROM WF_DIRECTORY_PARTITIONS
4639 WHERE ORIG_SYSTEM = DECODE(g_origSystem, 'PER', 'FND_USR',
4640 g_origSystem)
4641 AND PARTITION_ID IS NOT NULL;
4642
4643 end if;
4644
4645 exception
4646 when NO_DATA_FOUND then
4647 --If the partition does not exist, we will put this into the
4648 --WF_LOCAL partition
4649 if (g_localPartitionID is NULL) then
4650 begin
4651 SELECT Partition_ID, orig_system
4652 INTO g_localPartitionID, g_localPartitionName
4653 FROM WF_DIRECTORY_PARTITIONS
4654 WHERE ORIG_SYSTEM = 'WF_LOCAL_ROLES';
4655
4656 exception
4657 when NO_DATA_FOUND then
4658 g_localPartitionID := 0;
4659 g_localPartitionName := 'WF_LOCAL_ROLES';
4660
4661 end;
4662 end if;
4663
4664 g_partitionID := g_localPartitionID;
4665 g_partitionName := g_localPartitionName;
4666
4667 end;
4668 p_partitionID := g_partitionID;
4669 p_partitionName := g_partitionName;
4670
4671 exception
4672 when OTHERS then
4673 WF_CORE.Context('WF_DIRECTORY', 'AssignPartition', p_orig_system);
4674 raise;
4675 end;
4676
4677 -- Bug 3090738
4678 -- GetInfoFromMail
4679 --
4680 -- IN
4681 -- email address
4682 -- OUT
4683 -- User attributes as in WF_ROLES view
4684 --
4685 -- This API queries wf_roles view for information of the user when
4686 -- the e-mail address is given.
4687 procedure GetInfoFromMail(mailid in varchar2,
4688 role out NOCOPY varchar2,
4689 display_name out NOCOPY varchar2,
4690 description out NOCOPY varchar2,
4691 notification_preference out NOCOPY varchar2,
4692 language out NOCOPY varchar2,
4693 territory out NOCOPY varchar2,
4694 fax out NOCOPY varchar2,
4695 expiration_date out NOCOPY date,
4696 status out NOCOPY varchar2,
4697 orig_system out NOCOPY varchar2,
4698 orig_system_id out NOCOPY number)
4699 is
4700 l_email varchar2(2000);
4701 l_start pls_integer;
4702 l_end pls_integer;
4703 begin
4704 -- strip off the unwanted info from email. Emails from the mailer
4705 -- could be of the form "Vijay Shanmugam"[email protected]>
4706 l_start := instr(mailid, '<', 1, 1);
4707 if (l_start > 0) then
4708 l_end := instr(mailid, '>', l_start);
4709 l_email := substr(mailid, l_start+1, l_end-l_start-1);
4710 else
4711 l_email := mailid;
4712 end if;
4713
4714 -- lets find any active user with this e-mail id if not we will
4715 -- check for inactive user
4716 begin
4717 select WR.NAME,
4718 WR.DISPLAY,
4719 WR.DESCRIPTION,
4720 WR.NOTIFICATION_PREFERENCE,
4721 WR.LANGUAGE,
4722 WR.TERRITORY,
4723 WR.FAX,
4724 WR.STATUS,
4725 WR.EXPIRATION_DATE,
4726 WR.ORIG_SYSTEM,
4727 WR.ORIG_SYSTEM_ID
4728 into ROLE,
4729 DISPLAY_NAME,
4730 DESCRIPTION,
4731 NOTIFICATION_PREFERENCE,
4732 LANGUAGE,
4733 TERRITORY,
4734 FAX,
4735 STATUS,
4736 EXPIRATION_DATE,
4737 ORIG_SYSTEM,
4738 ORIG_SYSTEM_ID
4739 from (select R.NAME,
4740 substrb(R.DISPLAY_NAME,1,360) DISPLAY,
4741 substrb(R.DESCRIPTION,1,1000) DESCRIPTION,
4742 R.NOTIFICATION_PREFERENCE,
4743 R.LANGUAGE,
4744 R.TERRITORY,
4745 R.FAX,
4746 R.STATUS,
4747 R.EXPIRATION_DATE,
4748 R.ORIG_SYSTEM,
4749 R.ORIG_SYSTEM_ID,
4750 decode (R.STATUS, 'ACTIVE', 1, 2) ACTIVE_ORDER,
4751 decode (R.ORIG_SYSTEM, 'PER', 1, 'FND_USR', 2, 3) ORIG_SYS_ORDER
4752 from WF_ROLES R
4753 where UPPER(R.EMAIL_ADDRESS) = UPPER(l_email)
4754 order by ACTIVE_ORDER asc, ORIG_SYS_ORDER asc, START_DATE asc) WR
4755 where ROWNUM < 2;
4756 exception
4757 when others then
4758 ROLE := '';
4759 DISPLAY_NAME := '';
4760 DESCRIPTION := '';
4761 NOTIFICATION_PREFERENCE := '';
4762 LANGUAGE := '';
4763 TERRITORY := '';
4764 FAX := '';
4765 STATUS := '';
4766 EXPIRATION_DATE := to_date(null);
4767 ORIG_SYSTEM := '';
4768 ORIG_SYSTEM_ID := to_number(null);
4769 end;
4770 end GetInfoFromMail;
4771
4772 /* (PRIVATE) - to be used by WF only
4773 *
4774 * Fetches role information when the e-mail address is given.
4775 * Added other parameters for full NLS support -phase 1-, bug 7578908
4776 *
4777 * In phase 1, we only use constant default values for NLS parameters
4778 * not currently stored in wf_local_roles table.
4779 */
4780 procedure GetInfoFromMail2( p_emailid in varchar2
4781 , p_role out NOCOPY varchar2,
4782 p_display_name out NOCOPY varchar2,
4783 p_description out NOCOPY varchar2,
4784 p_notification_preference out NOCOPY varchar2,
4785 p_orig_system out NOCOPY varchar2,
4786 p_orig_system_id out NOCOPY number,
4787 p_fax out NOCOPY number,
4788 p_expiration_date out nocopy date,
4789 p_status out NOCOPY varchar2
4790 , p_nlsLanguage out NOCOPY varchar2,
4791 p_nlsTerritory out NOCOPY varchar2
4792 , p_nlsDateFormat out NOCOPY varchar2
4793 , p_nlsDateLanguage out NOCOPY varchar2
4794 , p_nlsCalendar out NOCOPY varchar2
4795 , p_nlsNumericCharacters out NOCOPY varchar2
4796 , p_nlsSort out NOCOPY varchar2
4797 , p_nlsCurrency out NOCOPY varchar2)
4798 is
4799 l_isComposite boolean;
4800 l_name varchar2(320);
4801 l_email_address varchar2(320);
4802 l_origSystem varchar2(30);
4803 l_origSystemID number;
4804 l_api varchar2(250) := g_plsqlName ||'GetInfoFromMail2';
4805
4806 begin
4807 if(wf_log_pkg.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
4808 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE, l_api,'BEGIN');
4809 end if;
4810
4811 GetInfoFromMail(mailid => p_emailid,
4812 role => l_name,
4813 display_name => p_display_name,
4814 description => p_description,
4815 notification_preference => p_notification_preference,
4816 language => p_nlsLanguage,
4817 territory => p_nlsTerritory,
4818 fax => p_fax,
4819 expiration_date => p_expiration_date,
4820 status => p_status,
4821 orig_system => p_orig_system,
4822 orig_system_id => p_orig_system_id);
4823
4824 if (l_name is not null) then
4825 -- got a unique entry, get NLS params from EBS profiles
4826
4827 l_isComposite := CompositeName(p_role, l_origSystem, l_origSystemID);
4828
4829 p_role:= l_name;
4830 wfa_sec.get_role_info3(l_isComposite,
4831 l_name,
4832 p_role,
4833 p_display_name,
4834 p_description,
4835 l_email_address,
4836 p_notification_preference,
4837 p_orig_system,
4838 p_orig_system_id,
4839 p_fax,
4840 p_STATUS,
4841 p_EXPIRATION_DATE,
4842 p_nlsLanguage,
4843 p_nlsTerritory
4844 , p_nlsDateFormat
4845 , p_nlsDateLanguage
4846 , p_nlsCalendar
4847 , p_nlsNumericCharacters
4848 , p_nlsSort
4849 , p_nlsCurrency);
4850
4851 else
4852 p_nlsDateFormat := '';
4853 p_nlsDateLanguage := '';
4854 p_nlsCalendar := '';
4855 p_nlsNumericCharacters := '';
4856 p_nlsSort := '';
4857 p_nlsCurrency := '';
4858 end if;
4859
4860 if(wf_log_pkg.LEVEL_PROCEDURE >= fnd_log.g_current_runtime_level) then
4861 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE, l_api,'END');
4862 end if;
4863 end GetInfoFromMail2;
4864
4865 function IsMLSEnabled(p_orig_system in varchar2)
4866 return boolean
4867 is
4868 mls_enabled number;
4869 l_origSys varchar2(30);
4870 begin
4871 l_origSys := UPPER(p_orig_system);
4872 if ((substr(l_origSys, 1, 8) = 'FND_RESP') and
4873 ((length(l_origSys) = 8) or --In case we just get 'FND_RESP'
4874 (substr(l_origSys, 9, 9) between '0' and '9'))) then
4875 l_origSys := 'FND_RESP';
4876
4877 end if;
4878
4879 --We can use the global variable set in wf_local.syncroles
4880 --but for standalone so as not to introduce dependency on WF_LOCAL
4881 --package we query from wf_directory_partitions directly.
4882 select count(1)
4883 into mls_enabled
4884 from wf_directory_partitions
4885 where orig_system = l_origSys
4886 and ROLE_TL_VIEW is not NULL ;
4887 if (mls_enabled = 1) then
4888 return TRUE;
4889 end if;
4890 --else case return false
4891 return FALSE;
4892 end IsMLSEnabled;
4893
4894 -- Change_Name_References_RF (PRIVATE)
4895 --
4896 -- IN
4897 -- p_sub_guid (RAW)
4898 -- p_event (WF_EVENT_T)
4899 --
4900 -- RETURNS
4901 -- varchar2
4902 --
4903 -- COMMENTS
4904 -- This api is a rule function to be called by BES. It is primarily used for
4905 -- a user name change to update all the fk references. The subscription is
4906 -- set as deferred to offline the updates to return control back to the user
4907 -- more quickly.
4908 --
4909 function Change_Name_References_RF( p_sub_guid in RAW,
4910 p_event in out NOCOPY WF_EVENT_T )
4911 return VARCHAR2 is
4912 l_newName VARCHAR2(360);
4913 l_oldName VARCHAR2(360);
4914
4915 begin
4916 l_newName := p_event.getValueForParameter('USER_NAME');
4917 l_oldName := p_event.getValueForParameter('OLD_USER_NAME');
4918
4919 /* --Update the user/roles
4920 UPDATE WF_LOCAL_USER_ROLES
4921 SET USER_NAME = l_newName
4922 WHERE USER_NAME = l_oldName;
4923
4924 --Update the user/role assignments
4925 UPDATE WF_USER_ROLE_ASSIGNMENTS
4926 SET USER_NAME = l_newName
4927 WHERE USER_NAME = l_oldName;*/ --these updates are now made inline
4928
4929 --Call WF_MAINTENANCE to update all the other fk references.
4930 WF_MAINTENANCE.PropagateChangedName(OLDNAME=>l_oldName, NEWNAME=>l_newName);
4931 return 'SUCCESS';
4932 exception
4933 when OTHERS then
4934 return 'ERROR';
4935
4936 end Change_Name_References_RF;
4937
4938 --
4939 -- DeleteRole
4940 -- IN
4941 -- p_name (VARCHAR2)
4942 -- p_OrigSystem (VARCHAR2)
4943 -- p_OrigSystemID (NUMBER)
4944 --
4945 --
4946 -- COMMENTS
4947 -- This API is to be used to remove a specified end-dated role or user with
4948 -- its references from the WFDS Tables.
4949 --
4950 procedure DeleteRole ( p_name in varchar2,
4951 p_origSystem in varchar2,
4952 p_origSystemID in number)
4953 is
4954
4955 TYPE numTab is table of number index by binary_integer;
4956
4957 l_count pls_integer;
4958 l_flag char(1);
4959 l_partitionID number;
4960 l_partitionName varchar2(30);
4961 l_relIDTAB numTab;
4962 begin
4963 AssignPartition (p_origSystem,l_partitionID,l_partitionName);
4964 begin
4965 --check whether the role name is truly end dated
4966
4967 select 1 into l_count
4968 from SYS.DUAL
4969 where exists (select null from wf_roles
4970 where name=p_name
4971 );
4972
4973 --if we have reached here, it implies that the user or role
4974 -- is not truly end dated.
4975 -- raise error
4976
4977 if(wf_log_pkg.level_exception >= fnd_log.g_current_runtime_level) then
4978 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_EXCEPTION,
4979 'WF_DIRECTORY.deleteRole',
4980 'Role is still active. Make sure it is end dated ');
4981 end if;
4982 WF_CORE.Context('WF_DIRECTORY', 'deleteRole', p_name);
4983 WF_CORE.Token('ROLE',p_name);
4984 WF_CORE.Raise('WFDS_ROLE_ACTIVE');
4985 exception
4986 when no_data_found then
4987
4988 delete from wf_local_roles
4989 where name=p_name
4990 and orig_system=p_origSystem
4991 and orig_system_id=p_origSystemId
4992 and partition_id =l_partitionID
4993 returning user_flag into l_flag;
4994
4995 -- once the role has been successfully removed, remove the assignments as well
4996 if l_flag ='Y' then
4997 --a user has been removed so call deleteUserRoles to remove all user/role
4998 -- associations for the user.
4999 DeleteUserRole(p_username=>p_name,
5000 p_userorigSystem=>p_origSystem,
5001 p_userorigSystemID=>p_origSystemID);
5002 else
5003 -- a role has been removed so call deleteUserRoles to remove all user/role
5004 -- associations for the role.
5005 -- also call wf_role_hierarchy.removeRelaionship to delete all hierarchical
5006 -- relationships in which the role participates.
5007 begin
5008 select relationship_id bulk collect into l_relIDTab
5009 from wf_role_hierarchies where sub_name=p_name
5010 or super_name = p_name;
5011 if (l_relIDTAB.count>0) then
5012 for i in l_relIDTAB.first..l_RelIDTAB.last loop
5013 WF_ROLE_HIERARCHY.RemoveRelationship(l_relIDTab(i),TRUE);
5014 end loop;
5015 end if;
5016 exception
5017 when no_data_found then
5018 null;
5019 end;
5020
5021 DeleteUserRole(p_rolename=>p_name,
5022 p_roleorigSystem=>p_origSystem,
5023 p_roleorigSystemID=>p_origSystemID);
5024 end if;
5025 end;
5026
5027
5028 exception
5029 when others then
5030 WF_CORE.Context('WF_DIRECTORY', 'deleteRole', p_name);
5031 raise;
5032 end;
5033
5034
5035 --
5036 -- DeleteUserRole
5037 --
5038 -- IN
5039 -- p_username (VARCHAR2)
5040 -- p_rolename (VARCHAR2)
5041 -- p_userOrigSystem (VARCHAR2)
5042 -- p_userOrigSystemID (NUMBER)
5043 -- p_roleOrigSystem (VARCHAR2)
5044 -- p_roleOrigSystemID (NUMBER)
5045 --
5046 -- COMMENTS
5047 -- This API is to be used to remove a specified end-dated user/role
5048 -- assignment along with its references from the WFDS Tables.
5049 --
5050 procedure DeleteUserRole ( p_username in varchar2,
5051 p_rolename in varchar2,
5052 p_userOrigSystem in varchar2,
5053 p_userOrigSystemID in number,
5054 p_roleOrigSystem in varchar2,
5055 p_roleOrigSystemID in number)
5056 is
5057
5058 l_count pls_integer;
5059 l_partitionID number;
5060 l_partitionName varchar2(30);
5061 begin
5062
5063 --check whether both the inbound parameters are null
5064 -- or the orig sys information is not provided
5065
5066 if (((p_username is null) and (p_rolename is null))
5067 or(p_username is not null and (p_userOrigSystem is null
5068 or p_userOrigSystemID is null))
5069 or(p_rolename is not null and (p_roleOrigSystem is null
5070 or p_roleOrigSystemID is null))) then
5071
5072 --raise error
5073 WF_CORE.Context('WF_DIRECTORY', 'DeleteUserRole', p_username,p_rolename);
5074 WF_CORE.Raise('WFSQL_ARGS');
5075
5076 elsif p_username is null then -- role has been passed
5077 begin
5078
5079 AssignPartition(p_roleorigSystem,l_partitionID, l_partitionName);
5080
5081 -- check whether the role is end-dated
5082
5083 select 1 into l_count
5084 from SYS.DUAL
5085 where exists (select null from wf_local_roles
5086 where name=p_rolename)
5087 and ( exists (select null from wf_user_roles
5088 where role_name=p_rolename
5089 )
5090 or exists (select null from wf_user_role_assignments_v
5091 where role_name=p_rolename
5092 )
5093 or exists (select null from wf_role_hierarchies
5094 where (super_name=p_rolename
5095 or sub_name=p_rolename)
5096 and enabled_flag='Y'
5097 ));
5098
5099 --if we have reached here, it implies that the role
5100 -- is not truly end dated.
5101 -- raise error
5102
5103 if(wf_log_pkg.level_exception >=
5104 fnd_log.g_current_runtime_level) then
5105 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_EXCEPTION,
5106 'WF_DIRECTORY.DeleteUserRole',
5107 'User/Role assignment is still active. Make sure it is end dated ' ||
5108 'and removed from any hierarchies');
5109 end if;
5110 WF_CORE.Context('WF_DIRECTORY', 'DeleteUserRole', p_rolename);
5111 WF_CORE.Token('ROLE',p_rolename);
5112 WF_CORE.Raise('WFDS_USER_ROLE_ACTIVE');
5113 exception
5114 when no_data_found then
5115 -- assignment is truly end dated
5116
5117 delete from wf_user_role_assignments
5118 where role_name=p_rolename;
5119
5120 delete from wf_local_user_roles
5121 where role_name=p_rolename
5122 and role_orig_system=p_roleorigSystem
5123 and role_orig_system_id=p_roleorigSystemID
5124 and partition_id = l_partitionID;
5125
5126
5127 end;
5128 elsif p_rolename is null then --user has been passed
5129 begin
5130 --check whether user is truly end dated
5131 select 1 into l_count
5132 from SYS.DUAL
5133 where exists (select null from wf_local_roles
5134 where name=p_username)
5135 and ( exists (select null from wf_user_roles
5136 where user_name=p_username
5137 )
5138 or exists (select null from wf_user_role_assignments_v
5139 where user_name=p_username
5140 ));
5141
5142 --if we have reached here, it implies that the user
5143 -- is not truly end dated.
5144 -- raise error
5145
5146 if(wf_log_pkg.level_exception >=
5147 fnd_log.g_current_runtime_level) then
5148 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_EXCEPTION,
5149 'WF_DIRECTORY.DeleteUserRole',
5150 'User/Role assignment is still active. Make sure it is end dated ' ||
5151 'and removed from any hierarchies');
5152 end if;
5153 WF_CORE.Context('WF_DIRECTORY', 'DeleteUserRole', p_username);
5154 WF_CORE.Token('ROLE',p_username);
5155 WF_CORE.Raise('WFDS_USER_ROLE_ACTIVE');
5156 exception
5157 when no_data_found then
5158 -- assignment is truly end dated
5159
5160 delete from wf_user_role_assignments
5161 where user_name=p_username;
5162
5163 delete from wf_local_user_roles
5164 where user_name=p_username
5165 and user_orig_system=p_userorigSystem
5166 and user_orig_system_id=p_userorigSystemID;
5167
5168 end;
5169 else --both role and user have been passed
5170 begin
5171 AssignPartition(p_roleorigSystem,l_partitionID, l_partitionName);
5172
5173 --check whether user/role is truly end dated
5174
5175 select 1 into l_count
5176 from SYS.DUAL
5177 where exists (select null from wf_local_roles
5178 where name=p_rolename or name=p_username)
5179 and ( exists (select null from wf_user_roles
5180 where role_name=p_rolename
5181 and user_name=p_username
5182 )
5183 or exists (select null from wf_user_role_assignments_v
5184 where role_name=p_rolename
5185 and user_name=p_username
5186 )
5187 or exists (select null from wf_role_hierarchies
5188 where (super_name=p_rolename
5189 or sub_name=p_rolename)
5190 and enabled_flag='Y'
5191 ));
5192
5193 --if we have reached here, it implies that the user/role
5194 -- is not truly end dated.
5195 -- raise error
5196
5197 if(wf_log_pkg.level_exception >=
5198 fnd_log.g_current_runtime_level) then
5199 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_EXCEPTION,
5200 'WF_DIRECTORY.DeleteUserRole',
5201 'User/Role assignment is still active. Make sure it is end dated ' ||
5202 'and removed from any hierarchies');
5203 end if;
5204 WF_CORE.Context('WF_DIRECTORY', 'DeleteUserRole', p_username,p_rolename);
5205 WF_CORE.Token('ROLE',p_rolename);
5206 WF_CORE.Token('USER',p_username);
5207 WF_CORE.Raise('WFDS_ASSIGNMENT_ACTIVE');
5208 exception
5209 when no_data_found then
5210 -- assignment is truly end dated
5211
5212 delete from wf_user_role_assignments
5213 where role_name=p_rolename
5214 and user_name=p_username;
5215
5216 delete from wf_local_user_roles
5217 where role_name=p_rolename
5218 and user_name=p_username
5219 and role_orig_system=p_roleorigSystem
5220 and role_orig_system_id=p_roleorigSystemID
5221 and user_orig_system=p_userOrigSystem
5222 and user_orig_system_id=p_userOrigSystemID
5223 and partition_id = l_partitionID;
5224 end;
5225 end if;
5226 exception
5227 when others then
5228 WF_CORE.Context('WF_DIRECTORY', 'DeleteUserRole', p_username,p_rolename);
5229 raise;
5230 end;
5231
5232
5233 end Wf_Directory;
[Home] [Help]