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