1 package body WFA_SEC as
2 /* $Header: wfsecicb.pls 120.4.12010000.7 2009/02/12 02:09:01 alepe ship $ */
3
4 -- Private global to hold access cookie
5 wf_session varchar2(320) := '';
6
7 -- The default behavior is to use ICX profile options instead
8 -- But Exchange has a requirement of using fnd_preferences
9 wf_use_fnd_preferences boolean := null;
10
11 -- ICX Session Id cache
12 g_session_id number := -1;
13
14 --
15 -- Use_Fnd_Preferences (PRIVATE)
16 -- Find out whether we should use FND Preference or not
17 -- Return
18 -- True if Token Resource WF_PREFERENCE is set to FND
19 -- False otherwise or if the above token is not set
20 --
21 function Use_Fnd_Preferences
22 return boolean
23 is
24 begin
25 if (wf_use_fnd_preferences is null) then
26 if (wf_core.translate('WF_PREFERENCE') = 'FND') then
27 wf_use_fnd_preferences := true;
28 else
29 wf_use_fnd_preferences := false;
30 end if;
31 end if;
32 return wf_use_fnd_preferences;
33 end Use_Fnd_Preferences;
34
35 --
36 -- CreateSession
37 --
38 procedure CreateSession(
39 c_user_name in varchar2,
40 c_user_password in varchar2)
41 is
42 sid number;
43 user varchar2(320);
44 pwd varchar2(255);
45 res varchar2(255);
46 begin
47 user := c_user_name;
48 pwd := c_user_password;
49 sid := 0;
50
51 -- Validate the user with icx
52 begin
53 res := ICX_SEC.ValidatePassword(user, pwd, sid);
54 exception
55 when others then
56 wf_core.token('USER', c_user_name);
57 wf_core.token('SQLCODE', SQLCODE);
58 wf_core.token('SQLERRM', SQLERRM);
59 wf_core.raise('WFSEC_CREATE_SESSION');
60 end;
61
62 if (res <> '0') then
63 wf_core.token('USER', c_user_name);
64 wf_core.raise('WFSEC_USER_PASSWORD');
65 end if;
66
67 -- Set the private access global
68 wf_session := c_user_name;
69
70 exception
71 when others then
72 wf_core.context('Wfa_Sec', 'CreateSession', c_user_name);
73 raise;
74 end CreateSession;
75
76 --
77 -- GetSession
78 --
79 procedure GetSession(user_name out NOCOPY varchar2)
80 is
81 l_user_name varchar2(320); -- used as out parameters cannot be read!!
82 res boolean;
83 begin
84 -- First check if local acccess global has been set
85 if (wfa_sec.wf_session is not null) then
86 l_user_name := wfa_sec.wf_session;
87 else
88 -- Otherwise check the ic cookie for a session
89 begin
90
91 if (wfa_sec.validate_only = TRUE) then
92
93 /* GK:
94 ** Do not update the icx_sessions table. If you get a long
95 ** running worklist or any other workflow api, you'll get a
96 ** lock on the sessions table that will lead to db enqueue contention
97 ** across the db.
98 */
99 res := ICX_SEC.ValidateSession( c_validate_only => 'Y',
100 c_update => FALSE);
101
102 else
103
104 res := ICX_SEC.ValidateSession(c_update => FALSE);
105
106 end if;
107
108 exception
109 when others then
110 wf_core.token('SQLCODE', SQLCODE);
111 wf_core.token('SQLERRM', SQLERRM);
112 wf_core.raise('WFSEC_GET_SESSION');
113 end;
114
115 if (res = FALSE ) then
116 wf_core.raise('WFSEC_NO_SESSION');
117 end if;
118
119 l_user_name := ICX_SEC.GetID(99);
120 end if;
121
122 user_name := l_user_name;
123 exception
124 when others then
125 wf_core.context('Wfa_Sec', 'GetSession');
126 raise;
127 end GetSession;
128
129 --
130 -- Header
131 -- Print an html page header
132 -- IN
133 -- background_only - Only set background with no other header
134 -- disp_find - When defined, Find button is displayed, and the value
135 -- is the URL the Find button is pointting to.
136 --
137 procedure Header(background_only in boolean,
138 disp_find in varchar2,
139 page_title in varchar2,
140 inc_lov_applet in boolean,
141 pseudo_login in boolean)
142 is
143 begin
144 if (background_only) then
145
146 htp.p('<BODY BGCOLOR="#CCCCCC">');
147
148 else
149 if (disp_find is not null) then
150
151 htp.p ('<BODY bgcolor="#CCCCCC">');
152
153 if not (pseudo_login) then
154 icx_plug_utilities.Toolbar(p_text=>page_title, p_disp_help=>'Y',
155 p_disp_find=>Header.disp_find);
156 else
157 icx_plug_utilities.Toolbar(p_text=>page_title, p_disp_mainmenu=>'N', p_disp_menu=>'N');
158 end if;
159
160 htp.p('<BR>');
161
162 else
163
164 htp.p ('<BODY bgcolor="#CCCCCC">');
165
166 if not (pseudo_login) then
167 icx_plug_utilities.Toolbar(p_text=>page_title, p_disp_help=>'Y');
168 else
169 icx_plug_utilities.Toolbar(p_text=>page_title, p_disp_mainmenu=>'N', p_disp_menu=>'N');
170 end if;
171
172 htp.p('<BR>');
173
174 end if;
175 end if;
176 exception
177 when others then
178 wf_core.context('Wfa_Sec', 'Header');
179 raise;
180 end Header;
181
182 --
183 -- Footer
184 -- Print an html page footer
185 --
186 procedure Footer
187 is
188 begin
189 icx_admin_sig.footer;
190 exception
191 when others then
192 wf_core.context('Wfa_Sec', 'Footer');
193 raise;
194 end Footer;
195
196 --
197 -- DetailURL
198 -- Produce URL for notification detail and response page.
199 -- IN
200 -- nid - notification id
201 -- RETURNS
202 -- URL of detail and response page for notification.
203 --
204 function DetailURL(nid in number) return varchar2
205 is
206 begin
207 return('wfa_html.detail?nid='||to_char(nid));
208 exception
209 when others then
210 Wf_Core.Context('Wfa_Sec', 'DetailURL', to_char(nid));
211 raise;
212 end DetailURL;
213
214 --
215 -- PseudoSession - create ICX psuedo session for the client
216 -- Creates a temp ICX session for the current user coming into ICX
217 -- from an email notification with a link to the applications.
218 -- Session information is typically stored on the web client as an
219 -- http cookie. This only applies to ICX so only wfsecicb will
220 -- have an actual implementation for this function. The others
221 -- do nothing.
222 --
223 -- Added setting of user preference here, so that a French user
224 -- when viewing a detached notification will still view this in
225 -- French instead of English.
226 procedure PseudoSession(IncludeHeader in BOOLEAN,
227 user_name in varchar2)
228 is
229 l_session_id NUMBER := 0;
230 l_result VARCHAR2(5) := '0';
231 c_territory VARCHAR2(80);
232 c_language VARCHAR2(80);
233 c_date_format VARCHAR2(40);
234 l_user_id NUMBER := to_number(null);
235 role_info_tbl wf_directory.wf_local_roles_tbl_type;
236 begin
237
238 l_result := ICX_SEC.PseudoSession (l_session_id, IncludeHeader);
239
240 if (user_name is not null) then
241 Wf_Directory.GetRoleInfo2(user_name,role_info_tbl);
242
243 -- do not brother to find out the user id if we use fnd_preferences
244 if (Use_FND_Preferences) then
245 l_user_id := to_number(null);
246 else
247 begin
248 -- user_name should be unique, but use rownum just in case
249 select USER_ID into l_user_id
250 from FND_USER
251 where USER_NAME = PseudoSession.user_name
252 and rownum < 2;
253 exception
254 when NO_DATA_FOUND then
255 l_user_id := to_number(null);
256 end;
257 end if;
258
259 -- Get the language preference
260 c_language := ''''||role_info_tbl(1).language||'''';
261
262 -- Get the terriory preference
263 c_territory := ''''||role_info_tbl(1).territory||'''';
264
265 if (l_user_id is not null) then
266 -- get the date format preference
267 c_date_format := ''''||NVL(fnd_profile.value_specific(
268 'ICX_DATE_FORMAT_MASK',l_user_id, null, null),'DD-MON-RRRR')||'''';
269 else
270 c_date_format := ''''||NVL(wf_pref.get_pref(user_name,'DATEFORMAT'),
271 'DD-MON-RRRR')||'''';
272 end if;
273
274 dbms_session.set_nls('NLS_LANGUAGE' , c_language);
275 dbms_session.set_nls('NLS_TERRITORY' , c_territory);
276 dbms_session.set_nls('NLS_DATE_FORMAT', c_date_format);
277 end if;
278 exception
279 when others then
280 Wf_Core.Context('Wfa_Sec', 'PseudoSession');
281 raise;
282 end PseudoSession;
283
284
285 --
286 -- Create_Help_Syntax
287 -- Create the javascript necessary to launch the help function
288 -- Since this is only required for the apps install case
289 -- I have covered this function with a wfa_sec function.
290 -- The other wfsec cases are just a stub.
291 --
292 procedure Create_Help_Syntax (
293 p_target in varchar2,
294 p_language_code in varchar2) IS
295
296 begin
297 htp.p('<SCRIPT>');
298
299 icx_admin_sig.help_win_script(p_target, p_language_code, 'FND');
300
301 htp.p('</SCRIPT>');
302
303 htp.p('<NOSCRIPT>' || WF_CORE.Translate('WFA_NOSCRIPT') || '</NOSCRIPT>');
304
305 exception
306 when others then
307 Wf_Core.Context('Wfa_Sec', 'Create_Help_Syntax');
308 raise;
309 end Create_Help_Syntax;
310
311 --
312 -- get_role_info
313 -- Gets role info for the user sources that we know about rather
314 -- than using the ugly expensive wf_roles view
315 --
316
317 procedure get_role_info(
318 role in varchar2,
319 name out NOCOPY varchar2,
320 display_name out NOCOPY varchar2,
321 description out NOCOPY varchar2,
322 email_address out NOCOPY varchar2,
323 notification_preference out NOCOPY varchar2,
324 language out NOCOPY varchar2,
325 territory out NOCOPY varchar2,
326 orig_system out NOCOPY varchar2,
327 orig_system_id out NOCOPY number
328 ) IS
329 l_status varchar2(8);
330 l_fax varchar2(100);
331 l_exp_date date;
332 begin
333 wfa_sec.get_role_info2(role , name ,display_name, description ,email_address,notification_preference,language , territory,orig_system,orig_system_id ,l_fax , l_status,l_exp_date);
334 exception
335 when others then
336 Wf_Core.Context('Wfa_Sec', 'Get_Role_Info', role);
337 raise;
338 end get_role_info;
339 --
340 -- get_role_info2
341 -- Gets role info2 for the user sources that we know about rather
342 -- than using the ugly expensive wf_roles view
343 --
344
345 procedure get_role_info2(
346 role in varchar2,
347 name out NOCOPY varchar2,
348 display_name out NOCOPY varchar2,
349 description out NOCOPY varchar2,
350 email_address out NOCOPY varchar2,
351 notification_preference out NOCOPY varchar2,
352 language out NOCOPY varchar2,
353 territory out NOCOPY varchar2,
354 orig_system out NOCOPY varchar2,
355 orig_system_id out NOCOPY number,
356 FAX out NOCOPY VARCHAR2,
357 STATUS out NOCOPY VARCHAR2,
358 EXPIRATION_DATE out NOCOPY DATE,
359 p_CompositeName in BOOLEAN
360 ) IS
361 prefix VARCHAR2(80);
362 roleid VARCHAR2(320);
363 nlsLang NUMBER;
364 nlsTerr NUMBER;
365 l_langstatus PLS_INTEGER;
366 l_terrstatus PLS_INTEGER;
367 l_composite BOOLEAN;
368 fndUserID NUMBER;
369
370 begin
371 if (p_CompositeName) then
372 l_composite := TRUE;
373 else
374 l_composite := FALSE;
375 end if;
376
377 if (l_composite) then
378 prefix := SUBSTRB(role, 1, INSTRB(role, ':') - 1);
379 roleid := SUBSTRB(role, INSTRB(role, ':') + 1);
380
381 if (prefix = 'POS') then
382 select NAME,
383 DISPLAY_NAME,
384 DESCRIPTION,
385 EMAIL_ADDRESS,
386 NOTIFICATION_PREFERENCE,
387 LANGUAGE,
388 TERRITORY,
389 ORIG_SYSTEM,
390 ORIG_SYSTEM_ID,
391 FAX,
392 STATUS,
393 EXPIRATION_DATE
394 into name,
395 display_name,
396 description,
397 email_address,
398 notification_preference,
399 language,
400 territory,
401 orig_system,
402 orig_system_id,
403 FAX,
404 STATUS,
405 EXPIRATION_DATE
406 from WF_LOCAL_ROLES PARTITION (POS)
407 where ORIG_SYSTEM = prefix
408 and ORIG_SYSTEM_ID = to_number(roleid)
409 and nvl(EXPIRATION_DATE, sysdate+1) > sysdate;
410
411 elsif (prefix = 'ENG_LIST') then
412 SELECT NAME,
413 DISPLAY_NAME,
414 DESCRIPTION,
415 EMAIL_ADDRESS,
416 NOTIFICATION_PREFERENCE,
417 LANGUAGE,
418 TERRITORY,
419 ORIG_SYSTEM,
420 ORIG_SYSTEM_ID,
421 FAX,
422 STATUS,
423 EXPIRATION_DATE
424 into name,
425 display_name,
426 description,
427 email_address,
428 notification_preference,
429 language,
430 territory,
431 orig_system,
432 orig_system_id,
433 FAX,
434 STATUS,
435 EXPIRATION_DATE
436 from WF_LOCAL_ROLES PARTITION (ENG_LIST)
437 where ORIG_SYSTEM = prefix
438 and ORIG_SYSTEM_ID = to_number(roleid)
439 and nvl(EXPIRATION_DATE, sysdate+1) > sysdate;
440
441 elsif ((SUBSTRB(prefix, 1, 8) = 'FND_RESP') and
442 ((length(prefix) = 8) or --In case we just get 'FND_RESP'
443 (substr(prefix, 9, 9) between '0' and '9'))) then
444 SELECT NAME,
445 DISPLAY_NAME,
446 DESCRIPTION,
447 EMAIL_ADDRESS,
448 NOTIFICATION_PREFERENCE,
449 LANGUAGE,
450 TERRITORY,
451 ORIG_SYSTEM,
452 ORIG_SYSTEM_ID,
453 FAX,
454 STATUS,
455 EXPIRATION_DATE
456 into name,
457 display_name,
458 description,
459 email_address,
460 notification_preference,
461 language,
462 territory,
463 orig_system,
464 orig_system_id,
465 FAX,
466 STATUS,
467 EXPIRATION_DATE
468 from WF_LOCAL_ROLES PARTITION (FND_RESP)
469 where ORIG_SYSTEM = prefix
470 and ORIG_SYSTEM_ID = to_number(roleid)
471 and nvl(EXPIRATION_DATE, sysdate+1) > sysdate;
472 elsif (prefix = 'AMV_CHN') then
473 select NAME,
474 DISPLAY_NAME,
475 DESCRIPTION,
476 EMAIL_ADDRESS,
477 NOTIFICATION_PREFERENCE,
478 LANGUAGE,
482 FAX,
479 TERRITORY,
480 ORIG_SYSTEM,
481 ORIG_SYSTEM_ID,
483 STATUS,
484 EXPIRATION_DATE
485 into name,
486 display_name,
487 description,
488 email_address,
489 notification_preference,
490 language,
491 territory,
492 orig_system,
493 orig_system_id,
494 FAX,
495 STATUS,
496 EXPIRATION_DATE
497 from WF_LOCAL_ROLES PARTITION (AMV_CHN)
498 where ORIG_SYSTEM = prefix
499 and ORIG_SYSTEM_ID = to_number(roleid)
500 and nvl(EXPIRATION_DATE, sysdate+1) > sysdate;
501
502 elsif (prefix = 'HZ_PARTY') then
503 select NAME,
504 DISPLAY_NAME,
505 DESCRIPTION,
506 EMAIL_ADDRESS,
507 NOTIFICATION_PREFERENCE,
508 LANGUAGE,
509 TERRITORY,
510 ORIG_SYSTEM,
511 ORIG_SYSTEM_ID,
512 FAX,
513 STATUS,
514 EXPIRATION_DATE
515 into name,
516 display_name,
517 description,
518 email_address,
519 notification_preference,
520 language,
521 territory,
522 orig_system,
523 orig_system_id,
524 FAX,
525 STATUS,
526 EXPIRATION_DATE
527 from WF_LOCAL_ROLES PARTITION (HZ_PARTY)
528 where ORIG_SYSTEM = prefix
529 and ORIG_SYSTEM_ID = to_number(roleid)
530 and nvl(EXPIRATION_DATE, sysdate+1) > sysdate;
531 else
532 l_composite := FALSE;
533 end if;
534 end if;
535
536 if NOT (l_composite) then
537 --Bug 2728955
538 --Changed the elseif to else this is for
539 --composite names ( eg : ABC:123). This will not fall
540 --in any of the above conditions but has prefix non-null also
541 --Tuned the query to use the partition_id for the prefix null
542 --ORIG_SYSTEM .
543 select NAME,
544 DISPLAY_NAME,
545 DESCRIPTION,
546 EMAIL_ADDRESS,
547 NOTIFICATION_PREFERENCE,
548 LANGUAGE,
549 TERRITORY,
550 ORIG_SYSTEM,
551 ORIG_SYSTEM_ID,
552 FAX,
553 STATUS,
554 EXPIRATION_DATE
555 into name,
556 display_name,
557 description,
558 email_address,
559 notification_preference,
560 language,
561 territory,
562 orig_system,
563 orig_system_id,
564 FAX,
565 STATUS,
566 EXPIRATION_DATE
567 from WF_LOCAL_ROLES
568 where NAME = role
569 and partition_id in (1, 0, 5, 10, 13)
570 and nvl(EXPIRATION_DATE, sysdate+1) > sysdate
571 and rownum < 2; /* Bug 2728955 */
572 end if;
573
574 --<rwunderl:2750876>
575 if (orig_system in ('FND_USR', 'PER')) then
576 if (Use_FND_Preferences) then
577 notification_preference := nvl(WF_PREF.get_pref(name,'MAILTYPE'),
578 'MAILHTML');
579 language := WF_PREF.get_pref(name, 'LANGUAGE');
580 territory := WF_PREF.get_pref(name, 'TERRITORY');
581
582 else
583 if (orig_system = 'PER') then
584 SELECT USER_ID
585 INTO fndUserID
586 FROM FND_USER
587 WHERE USER_NAME = name;
588
589 else
590 fndUserID := orig_system_ID;
591
592 end if;
593
594 notification_preference := nvl(WF_PREF.get_pref(name,'MAILTYPE'),
595 'MAILHTML');
596
597 -- <7578908> "-1" instead of NULL for ctx parameters other than USER_ID, so that
598 -- NOT to use current login ctx when calling fnd_profile.value_specific.
599 -- Also, in case of null profile value (user and site), getting the session
600 -- values
601 language := nvl(fnd_profile.value_specific('ICX_LANGUAGE',fndUserID /*user_id*/
602 , -1 /*resp_id*/, -1 /*app_id*/, -1 /*org_id*/, -1 /*server_id*/)
603 , wf_core.nls_language);
604 territory := nvl(fnd_profile.value_specific('ICX_TERRITORY',fndUserID, -1, -1, -1, -1)
605 , wf_core.nls_territory);
606
607 end if;
608 end if;
609
610 -- <7578908> this not needed now per above change
611 --Need to make sure the nls preferences were not null.
612 -- if ((language is NULL) or (territory is NULL)) then
613 -- WF_CACHE.GetNLSParameter('BASELANGUAGE', l_langstatus, nlsLang);
614 -- --Bug 3188230
615 -- --Get the base territory aswell
616 -- WF_CACHE.GetNLSParameter('BASETERRITORY', l_terrstatus, nlsTerr);
617 --
618 -- if ((l_langstatus <> WF_CACHE.task_SUCCESS) OR
622 -- WF_CACHE.NLSParameters(nlsTerr).PARAMETER := 'BASETERRITORY';
619 -- (l_terrstatus <> WF_CACHE.task_SUCCESS)) then
620 -- --Where there is language there is territory, so we will go after both.
621 -- WF_CACHE.NLSParameters(nlsLang).PARAMETER := 'BASELANGUAGE';
623 --
624 -- SELECT NLS_LANGUAGE, NLS_TERRITORY
625 -- INTO WF_CACHE.NLSParameters(nlsLang).VALUE,
626 -- WF_CACHE.NLSParameters(nlsTerr).VALUE
627 -- FROM FND_LANGUAGES
628 -- WHERE INSTALLED_FLAG = 'B';
629 --
630 -- end if;
631 --
632 -- language := WF_CACHE.NLSParameters(nlsLang).VALUE;
633 -- territory := WF_CACHE.NLSParameters(nlsTerr).VALUE;
634 --
635 -- end if;
636
637 exception
638 when no_data_found then
639 name := '';
640 display_name := '';
641 description := '';
642 notification_preference := '';
643 language := '';
644 territory := '';
645 email_address := '';
646 orig_system := '';
647 orig_system_id := to_number(null);
648 fax := '';
649 status := '';
650 EXPIRATION_DATE := to_date(null);
651
652 when others then
653 Wf_Core.Context('Wfa_Sec', 'Get_Role_Info2', role);
654 raise;
655
656 end get_role_info2;
657
658 /* get_role_info3
659 *
660 * Same as get_role_info2(), but handles rest of parameters for full NLS support
661 * (bug 7578908)
662 */
663 procedure get_role_info3( p_CompositeName in BOOLEAN,
664 p_role in varchar2,
665 p_name out NOCOPY varchar2,
666 p_display_name out NOCOPY varchar2,
667 p_description out NOCOPY varchar2,
668 p_email_address out NOCOPY varchar2,
669 p_notification_preference out NOCOPY varchar2,
670 p_orig_system out NOCOPY varchar2,
671 p_orig_system_id out NOCOPY number,
672 p_FAX out NOCOPY VARCHAR2,
673 p_STATUS out NOCOPY VARCHAR2,
674 p_EXPIRATION_DATE out NOCOPY DATE ,
675 p_nlsLanguage out NOCOPY varchar2,
676 p_nlsTerritory out NOCOPY varchar2
677 , p_nlsDateFormat out NOCOPY varchar2
678 , p_nlsDateLanguage out NOCOPY varchar2
679 , p_nlsCalendar out NOCOPY varchar2
680 , p_nlsNumericCharacters out NOCOPY varchar2
681 , p_nlsSort out NOCOPY varchar2
682 , p_nlsCurrency out NOCOPY varchar2
683 )
684 is
685 l_fndUserID NUMBER;
686
687 begin
688 p_nlsCalendar := null; -- <7720908> nls_calendar is never used to set session
689
690 get_role_info2(role => p_role, name => p_name, display_name => p_display_name,
691 description => p_description, email_address => p_email_address,
692 notification_preference => p_notification_preference,
693 language => p_nlsLanguage,
694 territory => p_nlsTerritory,
695 orig_system =>p_orig_system,
696 orig_system_id => p_orig_system_id,
697 FAX => p_fax,
698 STATUS => p_status,
699 EXPIRATION_DATE => p_expiration_date,
700 p_CompositeName => p_compositeName
701 );
702
703 if (p_orig_system in ('PER', 'FND_USR') ) then
704 if (p_orig_system ='PER') then
705 SELECT USER_ID
706 INTO l_fndUserID
707 FROM FND_USER
708 WHERE USER_NAME = p_role;
709 else
710 l_fndUserID := p_orig_system_id;
711 end if;
712
713 p_nlsCurrency := nvl(fnd_profile.value_specific('ICX_PREFERRED_CURRENCY', l_fndUserID /*user_id*/
714 , -1 /*resp_id*/, -1 /*app_id*/, -1 /*org_id*/, -1 /*server_id*/)
715 , wf_core.nls_currency);
716 p_nlsNumericCharacters := nvl(fnd_profile.value_specific('ICX_NUMERIC_CHARACTERS', l_fndUserID, -1, -1, -1, -1),
717 wf_core.nls_numeric_characters);
718
719 -- <8220816> returns default value if profile nls_calendar is null
720 if (fnd_release.major_version = 12 and fnd_release.minor_version >= 1 and fnd_release.point_version>=1)
721 or (fnd_release.major_version > 12) then
722 p_nlsCalendar := nvl(fnd_profile.value_specific('FND_FORMS_USER_CALENDAR', l_fndUserID, -1, -1, -1, -1)
723 , wf_core.nls_calendar);
724 end if;
725
726 p_nlsDateFormat := nvl(fnd_profile.value_specific('ICX_DATE_FORMAT_MASK', l_fndUserID, -1, -1, -1, -1),
727 wf_core.nls_date_format);
728
729 p_nlsDateLanguage := nvl(
730 nvl(fnd_profile.value_specific('ICX_DATE_LANGUAGE', l_fndUserID, -1, -1, -1, -1) , p_nlsLanguage),
731 wf_core.nls_date_language);
732
733 p_nlsSort := nvl(fnd_profile.value_specific('ICX_NLS_SORT', l_fndUserID, -1, -1, -1, -1),
734 wf_core.nls_sort);
738 p_nlsCurrency := wf_core.nls_currency;
735
736 else -- not an EBS user role, therefore, return PHASE 1 default values
737
739 p_nlsNumericCharacters := wf_core.nls_numeric_characters;
740 p_nlsCalendar := wf_core.nls_calendar;
741 p_nlsDateFormat := wf_core.nls_date_format;
742 p_nlsSort := wf_core.nls_sort;
743
744 -- for Date language we simply use the role's preference language
745 p_nlsDateLanguage := p_nlsLanguage;
746
747 end if;
748
749 exception
750 when NO_DATA_FOUND then
751 p_nlsCurrency := '';
752 p_nlsNumericCharacters := '';
753 p_nlsCalendar := '';
754 p_nlsDateFormat := '';
755 p_nlsDateLanguage := '';
756 p_nlsSort := '';
757 when others then
758 Wf_Core.Context('Wfa_Sec', 'Get_Role_Info3', p_role);
759 raise;
760 end get_role_info3;
761
762 --
763 -- ResetCookie
764 --
765 -- IN: Name of the cookie to be reset to -1.
766 --
767
768 procedure ResetCookie(cookieName in varchar2)
769 is
770 BEGIN
771
772 owa_cookie.send(name=>cookieName, value=>'-1', expires=>'',
773 path=>'/');
774
775 end ResetCookie;
776
777 --
778 -- GET_PROFILE_VALUE (PRIVATE)
779 --
780 function Get_Profile_Value(name varchar2,
781 user_name varchar2)
782 return varchar2
783 is
784 l_orig_system varchar2(30);
785 l_orig_system_id number;
786 l_user_id number;
787 l_application_id number;
788
789 result varchar2(32000);
790 begin
791 Wf_Directory.GetRoleOrigSysInfo(user_name, l_orig_system, l_orig_system_id);
792
793 if (instr(l_orig_system, 'FND_USR') > 0) then
794 result := fnd_profile.value_specific(name=>Get_Profile_Value.name,
795 user_id=>l_orig_system_id);
796 elsif ((SUBSTRB(l_orig_system, 1, 8) = 'FND_RESP') and
797 (length(l_orig_system) > 8) and --Make sure we don't just get
798 --'FND_RESP'
799 (substr(l_orig_system, 9, 9) between '0' and '9')) then
800 l_application_id := substr(l_orig_system,
801 instr(l_orig_system,'FND_RESP')+8);
802 result := fnd_profile.value_specific(name=>Get_Profile_Value.name,
803 responsibility_id=>l_orig_system_id,
804 application_id=>l_application_id);
805 elsif (instr(l_orig_system, 'PER') > 0) then
806 begin
807 --Bug 2358728A
808 --Obtain the user_id based on the unique user_name
809 SELECT USER_ID
810 INTO l_user_id
811 FROM FND_USER
812 WHERE user_name = Get_Profile_Value.user_name;
813 exception
814 when NO_DATA_FOUND then
815 l_user_id := to_number(null);
816 end;
817 if (l_user_id is not null) then
818 result := fnd_profile.value_specific(name=>Get_Profile_Value.name,
819 user_id=>l_user_id);
820 else
821 result := null;
822 end if;
823 else
824 result := null;
825 end if;
826
827 return result;
828
829 exception
830 when OTHERS then
831 Wf_Core.Context('Wfa_Sec', 'Get_Profile_Value', name, user_name);
832 raise;
833 end Get_Profile_Value;
834
835 -- Local_Chr
836 -- Return specified character in current codeset
837 -- IN
838 -- ascii_chr - chr number in US7ASCII
839 function Local_Chr(
840 ascii_chr in number)
841 return varchar2
842 is
843 begin
844 if (ascii_chr = 10) then
845
846 if (WF_CORE.LOCAL_CS_NL is null) then
847 WF_CORE.LOCAL_CS_NL := Fnd_Global.Local_Chr(ascii_chr);
848 end if;
849
850 return WF_CORE.LOCAL_CS_NL;
851
852 elsif (ascii_chr = 9) then
853
854 if (WF_CORE.LOCAL_CS_TB is null) then
855 WF_CORE.LOCAL_CS_TB := Fnd_Global.Local_Chr(ascii_chr);
856 end if;
857
858 return WF_CORE.LOCAL_CS_TB;
859
860 elsif (ascii_chr = 13) then
861
862 if (WF_CORE.LOCAL_CS_CR is null) then
863 WF_CORE.LOCAL_CS_CR := Fnd_Global.Local_Chr(ascii_chr);
864 end if;
865
866 return WF_CORE.LOCAL_CS_CR;
867
868 else
869
870 return(Fnd_Global.Local_Chr(ascii_chr));
871
872 end if;
873
874 end Local_Chr;
875
876 --
877 -- DirectLogin - Return proper function name for DirectLogin --Bug: 1566390
878 -- Also needed to port bug 1838410
879 --
880 function DirectLogin (nid in NUMBER) return VARCHAR2
881 IS
882 x_mode varchar2(30);
883 BEGIN
884 fnd_profile.get('WF_ICX_MODE',x_mode);
885
886 return ('OracleApps.DF?i_direct=' || WFA_SEC.DetailURL(nid) || '&i_mode=' ||
887 nvl(x_mode,'2'));
888
889 exception
890 when others then
891 Wf_Core.Context('Wfa_Sec', 'DirectLogin', to_char(nid));
892 raise;
893 end DirectLogin;
894
895
896 --
897 -- GetFWKUserName
898 -- Return current Framework user name
899 --
900 function GetFWKUserName
901 return varchar2
902 is
903 begin
904 return FND_GLOBAL.USER_NAME;
905 exception
909 end GetFWKUserName;
906 when others then
907 Wf_Core.Context('Wfa_Sec', 'GetFWKUserName');
908 raise;
910
911 --
912 -- Logout
913 -- This is a dummy procedure, wfa_html.logout should be used
914 -- unless single signon feature is activated
915 --
916 procedure Logout
917 is
918 begin
919 return;
920 end Logout;
921
922
923 --
924 -- DS_Count_Local_Role (PRIVATE)
925 -- Returns count of a role in local directory service table
926 -- IN
927 -- role_name - role to be counted
928 -- RETURN
929 -- count of provided role in local directory service table
930 --
931 function DS_Count_Local_Role(role_name in varchar2)
932 return number
933 is
934 cnt number;
935 begin
936 select count(1) into cnt
937 from WF_LOCAL_ROLES PARTITION (WF_LOCAL_ROLES)
938 where NAME = role_name
939 and ORIG_SYSTEM in ('WF_LOCAL_ROLES', 'WF_LOCAL_USERS')
940 and ORIG_SYSTEM_ID = 0;
941
942 return(cnt);
943
944 exception
945 when others then
946 WF_CORE.Context('WFA_SEC', 'DS_Count_Local_Role', role_name);
947 raise;
948 end DS_Count_Local_Role;
949
950 --
951 -- DS_Update_Local_Role (PRIVATE)
952 -- Update old name user/role in local directory service tables with new name
953 -- IN
954 -- OldName - original name to be replaced
955 -- NewName - new name to replace
956 --
957 procedure DS_Update_Local_Role(
958 OldName in varchar2,
959 NewName in varchar2
960 )
961 is
962 begin
963 update WF_LOCAL_ROLES PARTITION (WF_LOCAL_ROLES)
964 set NAME = NewName
965 where NAME = OldName
966 and ORIG_SYSTEM in ('WF_LOCAL_USERS', 'WF_LOCAL_ROLES')
967 and ORIG_SYSTEM_ID = 0;
968
969 -- Update local user roles
970 update WF_LOCAL_USER_ROLES PARTITION (WF_LOCAL_ROLES)
971 set USER_NAME = NewName
972 where USER_NAME = OldName
973 and USER_ORIG_SYSTEM = 'WF_LOCAL_USERS'
974 and USER_ORIG_SYSTEM_ID = 0;
975
976 update WF_LOCAL_USER_ROLES PARTITION (WF_LOCAL_ROLES)
977 set ROLE_NAME = NewName
978 where ROLE_NAME = OldName
979 and ROLE_ORIG_SYSTEM = 'WF_LOCAL_USERS'
980 and ROLE_ORIG_SYSTEM_ID = 0;
981
982 update WF_LOCAL_USER_ROLES PARTITION (WF_LOCAL_ROLES)
983 set ROLE_NAME = NewName
984 where ROLE_NAME = OldName
985 and ROLE_ORIG_SYSTEM = 'WF_LOCAL_ROLES'
986 and ROLE_ORIG_SYSTEM_ID = 0;
987
988
989
990 exception
991 when others then
992 WF_CORE.Context('WFA_SEC', 'DS_Update_Local_Role', OldName, NewName);
993 raise;
994 end DS_Update_Local_Role;
995
996 function GetUser
997 return varchar2
998 is
999 username varchar2(320);
1000 begin
1001 username := wfa_sec.GetFWKUserName;
1002 return username;
1003 exception
1004 when others then
1005 --Incase of exception just return null
1006 return '';
1007 end;
1008
1009
1010 --
1011 -- user_id
1012 -- Return current user id, in apps, wrapper to FND_GLOBAL.user_id
1013 -- In standalone, returns -1.
1014 function user_id return number is
1015
1016 begin
1017 return FND_GLOBAL.user_id;
1018 end;
1019
1020 --
1021 -- login_id
1022 -- Return current login id, in apps, wrapper to FND_GLOBAL.login_id
1023 -- In standalone, returns -1.
1024 function login_id return number is
1025 begin
1026 return FND_GLOBAL.login_id;
1027 end;
1028
1029 --
1030 -- security_group_id
1031 -- Return current security_group_id, in apps, wrapper to
1032 -- FND_GLOBAL.security_group_id In standalone, returns -1.
1033 function security_group_id return number is
1034 begin
1035 return FND_GLOBAL.security_group_id;
1036 end;
1037
1038 --
1039 -- CheckSession
1040 -- Check the cached ICX session id against the current session id to determine
1041 -- if the session has been changed. This function caches the current session id
1042 -- after the check.
1043 -- RETURN
1044 -- boolean - True if session matches, else False
1045 function CheckSession return boolean
1046 is
1047 begin
1048 if (wfa_sec.g_session_id = fnd_session_management.g_session_id) then
1049 -- Session has not changed from the previous one or the WF Code executes in the
1050 -- background where both are -1.
1051 return true;
1052 else
1053 -- Cache current session id since it has changed
1054 wfa_sec.g_session_id := fnd_session_management.g_session_id;
1055 return false;
1056 end if;
1057 end CheckSession;
1058
1059 -- See spec for description
1060 function Random return varchar2
1061 is
1062 begin
1063 -- Fnd_crypto.RandomNumber return a number of 16 bytes which has maximum
1064 -- 39 digits. This is well within the limit of 80 that this random
1065 -- function is returning.
1066 return(to_char(fnd_crypto.RandomNumber));
1067 end Random;
1068
1069 end WFA_SEC;
[Home] [Help]