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