[Home] [Help]
PACKAGE BODY: APPS.ORACLESSWA
Source
1 package body OracleSSWA as
2 /* $Header: ICXSSWAB.pls 120.6 2012/04/12 19:48:36 ctilley ship $ */
3
4 procedure bookmarkthis (icxtoken in varchar2,
5 p in varchar2) is
6
7 l_session_id number;
8 l_text varchar2(2000);
9 l_parameters icx_on_utilities.v80_table;
10 l_resp_appl_id number;
11 l_responsibility_id number;
12 l_security_group_id number;
13 l_function_id number;
14 l_url varchar2(4000);
15
16 /*
17 l_function_type varchar2(30);
18 l_menu_id number;
19 l_validate boolean;
20 l_error_message varchar2(2000);
21 err_mesg varchar2(240);
22 err_num number;
23 l_user_id number;
24 l_org_id number;
25 l_multi_org_flag varchar2(30);
26 l_profile_defined boolean;
27 e_session_invalid exception;
28 */
29
30 begin
31
32 l_session_id := icx_portlet.validateSessionPart1;
33
34 l_text := icx_call.decrypt4(icxtoken, l_session_id);
35
36 icx_on_utilities.unpack_parameters(l_text,l_parameters);
37
38 l_resp_appl_id := nvl(l_parameters(1),178);
39 l_responsibility_id := l_parameters(2);
40 l_security_group_id := l_parameters(3);
41 l_function_id := l_parameters(4);
42
43 if P is null
44 then
45 l_text := null;
46 else
47 l_text := icx_call.decrypt4(P,l_session_id);
48 end if;
49
50 -- 2802333 nlbarlow
51 l_url := icx_portlet.createExecLink(p_application_id => l_resp_appl_id,
52 p_responsibility_id => l_responsibility_id,
53 p_security_group_id => l_security_group_id,
54 p_function_id => l_function_id,
55 p_parameters => P,
56 p_url_only => 'Y');
57
58 owa_util.mime_header('text/html', FALSE);
59
60 owa_util.redirect_url(l_url);
61
62 owa_util.http_header_close;
63
64 /*
65 select TYPE
66 into l_function_type
67 from FND_FORM_FUNCTIONS
68 where FUNCTION_ID = l_function_id;
69
70 l_menu_id := l_parameters(5);
71
72 select multi_org_flag
73 into l_multi_org_flag
74 from fnd_product_groups
75 where rownum < 2;
76
77 if l_multi_org_flag = 'Y'
78 then
79 fnd_profile.get_specific(
80 name_z => 'ORG_ID',
81 responsibility_id_z => l_responsibility_id,
82 application_id_z => l_resp_appl_id,
83 val_z => l_org_id,
84 defined_z => l_profile_defined);
85 end if;
86
87 update ICX_SESSIONS
88 set RESPONSIBILITY_APPLICATION_ID = l_resp_appl_id,
89 RESPONSIBILITY_ID = l_responsibility_id,
90 SECURITY_GROUP_ID = l_security_group_id,
91 ORG_ID = l_org_id,
92 FUNCTION_ID = l_function_id,
93 FUNCTION_TYPE = l_function_type,
94 MENU_ID = l_menu_id
95 where SESSION_ID = l_session_id;
96
97 commit;
98
99 l_validate := icx_sec.validateSessionPrivate
100 (c_session_id => l_session_id,
101 c_resp_appl_id => l_resp_appl_id,
102 c_security_group_id => l_security_group_id,
103 c_responsibility_id => l_responsibility_id,
104 c_function_id => l_function_id,
105 c_update => FALSE);
106
107 icx_sec.g_validateSession_flag := false;
108
109 OracleApps.runFunction
110 (c_function_id => l_function_id,
111 n_session_id => l_session_id,
112 c_parameters => l_text,
113 p_resp_appl_id => l_resp_appl_id,
114 p_responsibility_id => l_responsibility_id,
115 p_security_group_id => l_security_group_id,
116 p_menu_id => l_menu_id,
117 p_function_type => l_function_type);
118
119 icx_sec.g_validateSession_flag := true;
120 */
121
122 exception
123 when others then
124 icx_sec.g_validateSession_flag := true;
125 htp.p('User does not have access to this function');
126 htp.nl;
127 --NEED TO DO!::
128 --if this is portal/sso, send to oraclesswa.convertsession
129 --if this is php, send to oracleapps.convertsession with encrypted param string
130 -- htp.p(SQLERRM);
131 htp.p(dbms_utility.format_error_stack);
132
133 end;
134
135 -- OA Framework version of bookmarkthis which can do some really
136 -- neat stuff for the current responsibility portlet - blow away
137 -- every cached version for the current user!
138
139 procedure FwkBookmarkThis (icxtoken in varchar2,
140 p in varchar2)
141 is
142
143 l_session_id number;
144 l_text varchar2(2000);
145 l_parameters icx_on_utilities.v80_table;
146 l_resp_appl_id number;
147 l_responsibility_id number;
148 l_security_group_id number;
149 l_user_id number;
150
151 begin
152 -- Get the responsibility information for the bookmark being
153 -- launched
154 l_session_id := icx_portlet.validateSessionPart1;
155 l_text := icx_call.decrypt4(icxtoken, l_session_id);
156 icx_on_utilities.unpack_parameters(l_text,l_parameters);
157 l_resp_appl_id := nvl(l_parameters(1),178);
158 l_responsibility_id := l_parameters(2);
159 l_security_group_id := l_parameters(3);
160
161 -- Get the current user's user_id
162 select user_id
163 into l_user_id
164 from icx_sessions
165 where session_id = l_session_id;
166
167 -- Update the caching key for every 'Current responsibility
168 -- pages' portlet belonging to the current user
169 update icx_portlet_customizations
170 set responsibility_id = l_responsibility_id,
171 application_id = l_resp_appl_id,
172 security_group_id = l_security_group_id,
173 caching_key = caching_key + 1
174 where plug_id in (select function_id
175 from fnd_form_functions
176 where function_name = 'FND_NAVIGATE_SCTX_PORTLET')
177 and user_id = l_user_id;
178
179 -- Do whatever the BookmarkThis
180 OracleSSWA.BookmarkThis(icxtoken, p);
181 end;
182
183
184 procedure switchpage (pagename in varchar2) is
185
186 l_url varchar2(2000);
187 l_end number;
188
189 begin
190
191 fnd_profile.get(name => 'APPS_PORTAL',
192 val => l_url);
193
194 if l_url IS NULL Then
195 htp.p ('Please contact System Administrator. ');
196 htp.p ('Profile - APPS_PORTAL is null') ;
197 end If ;
198
199 l_end := instrb(l_url,'/',-1,1);
200 l_url := substrb(l_url,1,l_end);
201 l_url := l_url||'url/page/'||pagename;
202
203 owa_util.redirect_url(l_url);
204
205 exception
206 when others then
207 -- htp.p(SQLERRM);
208 htp.p(dbms_utility.format_error_stack);
209 end;
210
211 function listener_token return varchar2 is
212
213 l_listener_token VARCHAR2(240);
214 l_profile_defined boolean;
215 l_server VARCHAR2(240);
216
217 begin
218
219 fnd_profile.get_specific(
220 name_z => 'APPS_SSO_LISTENER_TOKEN',
221 val_z => l_listener_token,
222 defined_z => l_profile_defined);
223
224 if l_listener_token is null
225
226 then
227
228 -- bug 2712473
229 fnd_profile.get_specific(
230 name_z => 'APPS_DATABASE_ID',
231 val_z => l_listener_token,
232 defined_z => l_profile_defined);
233 if l_listener_token is null
234 then
235 l_listener_token := FND_WEB_CONFIG.DATABASE_ID;
236 end if;
237
238 end if;
239
240 return l_listener_token;
241
242 end;
243
244 procedure sign_on (urlc in varchar2) is
245
246 l_listener_token VARCHAR2(240);
247 l_sso_user_name VARCHAR2(1000);
248 l_ip_address VARCHAR2(1000);
249 l_sso_time_remaining VARCHAR2(1000);
250 l_site_time_stamp VARCHAR2(1000);
251 l_url_requested VARCHAR2(32000);
252 l_unused_param VARCHAR2(1000);
253 u fnd_user%rowtype;
254 l_user_id number;
255 l_session_id number;
256 l_return VARCHAR2(240);
257 l_session_status VARCHAR2(10);
258 c_user_id NUMBER;
259 l_anon_id NUMBER;
260 l_anon_name VARCHAR2(350);
261 l_return VARCHAR2(240);
262 l_procedure_call varchar2(32000);
263 l_call integer;
264 l_dummy integer;
265 l_apps_sso VARCHAR2(30);
266 l_profile_defined BOOLEAN;
267 l_mode VARCHAR2(10);
268 l_language varchar2(80);
269 l_language_code varchar2(30);
270 l_date_format varchar2(150);
271 l_date_language varchar2(30);
272 l_numeric_characters varchar2(30);
273 l_nls_sort varchar2(30);
274 l_nls_territory varchar2(30);
275 l_limit_time number;
276 l_limit_connects number;
277 l_org_id varchar2(50);
278 portalUrl VARCHAR2(4000);
279 portalUrlBase VARCHAR2(4000);
280 l_exception VARCHAR2(30);
281 l_timeout NUMBER;
282
283 l_new_xsid varchar2(32);
284
285 begin
286
287 icx_sec.ServerLevel;
288
289 l_listener_token := OracleSSWA.listener_token;
290 fnd_profile.get_specific(
291 name_z => 'APPS_SSO',
292 user_id_z => l_user_id,
293 val_z => l_apps_sso,
294 defined_z => l_profile_defined);
295
296 -- Wait SSO
297
298 IF (nvl(l_apps_sso,'SSWA')='SSO_SDK') OR
299 (nvl(l_apps_sso,'SSWA')='SSWA_SSO') THEN
300
301 l_call := dbms_sql.open_cursor;
302
303 l_procedure_call := 'wwsec_sso_enabler.parse_url_cookie'||
304 '(p_lsnr_token => :l_listener_token'||
305 ',p_enc_url_cookie => :urlc'||
306 ',p_sso_username => :l_sso_user_name'||
307 ',p_ipaddr => :l_ip_address'||
308 ',p_sso_timeremaining => :l_sso_time_remaining'||
309 ',p_site_timestamp => :l_site_time_stamp'||
310 ',p_url_requested => :l_url_requested)';
311
312 icx_sec.g_window_cookie_name := 'Y';
313
314 dbms_sql.parse(l_call,'begin '||l_procedure_call||'; exception when wwsec_sso_enabler.COOKIE_EXPIRED_EXCEPTION then icx_sec.g_window_cookie_name := ''X''; raise; when others then raise; end;' ,dbms_sql.native);
315
316 l_sso_user_name := '1234567890123456789012345678901234567890';
317 l_ip_address := '123.456.678.901';
318 l_sso_time_remaining := 1234567890;
319 l_site_time_stamp := sysdate;
320 l_url_requested := '';
321
322 for i in 1..100 loop -- set l_url_requested to 2000 characters
323 l_url_requested := l_url_requested||'12345678901234567890';
324 end loop;
325
326 dbms_sql.bind_variable(l_call,'l_listener_token',l_listener_token);
327 dbms_sql.bind_variable(l_call,'urlc',urlc);
328 dbms_sql.bind_variable(l_call,'l_sso_user_name',l_sso_user_name);
329 dbms_sql.bind_variable(l_call,'l_ip_address',l_ip_address);
330 dbms_sql.bind_variable(l_call,'l_sso_time_remaining',l_sso_time_remaining);
331 dbms_sql.bind_variable(l_call,'l_site_time_stamp',l_site_time_stamp);
332 dbms_sql.bind_variable(l_call,'l_url_requested',l_url_requested);
333
334 l_dummy := dbms_sql.execute(l_call);
335
336 dbms_sql.variable_value(l_call,'l_sso_user_name',l_sso_user_name);
337 dbms_sql.variable_value(l_call,'l_url_requested',l_url_requested);
338
339 dbms_sql.close_cursor(l_call);
340
341 END IF;--apps_sso profile option
342
343 portalUrl := fnd_profile.value('APPS_PORTAL');
344 portalUrlBase := substr(portalUrl, 0, length(portalUrl) - 4);
345
346 if (l_url_requested = 'APPSHOMEPAGE') then
347 if(fnd_profile.value('APPS_SSO') = 'SSO_SDK') then
348 l_url_requested :=
349 portalUrlBase||'wwsec_app_priv.login?p_requested_url='||wfa_html.conv_special_url_chars(portalUrl)||'&p_cancel_url='||wfa_html.conv_special_url_chars(portalUrl);
350 else
351 l_url_requested := FND_WEB_CONFIG.PLSQL_AGENT||'OracleMyPage.Home';
352 end if;
353 end if;
354
355 l_sso_user_name := upper(l_sso_user_name);
356
357 -- Set application cookie
358 -- Bug 3801219
359 -- Bug 4151179 : remove those extra parentesis
360 select user_id
361 into l_user_id
362 from fnd_user
363 where user_name = l_sso_user_name
364 and (END_DATE is NULL or END_DATE > sysdate);
365
366
367 --begin code for preservation of session_id when switching from anonymous user to authenticated user.
368 l_session_id := icx_sec.getsessioncookie; -- get the cookie if there is one.
369 l_session_status := icx_sec.check_session(l_session_id); -- check to see if cookie is for valid session
370 BEGIN
371 SELECT user_id
372 INTO c_user_id
373 FROM icx_sessions
374 WHERE session_id=l_session_id; -- use cookie value to get user_id, defaults to -999 if bogus cookie/user_id.
375 EXCEPTION
376 WHEN no_data_found THEN
377 c_user_id := -999;
378 END;
379 -- fnd_profile.get(name => 'GUEST_USER_PWD',
380 -- val => l_anon_name);
381 -- Using new api to retrieve GUEST credentials.
382 l_anon_name := fnd_web_sec.get_guest_username_pwd;
383
384 IF l_anon_name IS NOT NULL THEN
385 l_anon_name := SUBSTR(l_anon_name, 1, INSTR(l_anon_name, '/') -1); -- profile is stored as user/passwd
386 BEGIN
387 SELECT user_id
388 INTO l_anon_id
389 FROM fnd_user
390 WHERE user_name=l_anon_name;
391 EXCEPTION
392 WHEN OTHERS THEN
393 l_anon_id := -999;
394 END;
395 ELSE
396 l_anon_id := -999;
397 --END;
398 END IF;
399
400 --test to see if we are switching from an anonymous session to authenticated session
401 -- if it is a valid session, and the users are difference, and the original user is the anonymous user then
402 IF l_session_status <> 'INVALID' and l_user_id <> l_anon_id and c_user_id = l_user_id THEN
403 -- Reuse expired session
404 -- Session Hijacking. Reset xsid whenever session is reset
405 l_new_xsid := fnd_session_management.NewXSID;
406 UPDATE icx_sessions
407 SET last_connect = sysdate,
408 first_connect = SYSDATE,
409 counter = 1,
410 xsid = l_new_xsid
411 WHERE session_id = l_session_id;
412 owa_util.mime_header('text/html', FALSE);
413 -- Send the new XSID
414 icx_sec.sendsessioncookie(l_session_id);
415 owa_util.redirect_url(l_url_requested);
416 owa_util.http_header_close;
417 ELSIF ((l_session_status<>'INVALID') AND (c_user_id = l_anon_id) AND (l_anon_id <> -999) AND (c_user_id <> l_user_id)) THEN
418
419 icx_sec.setUserNLS
420 (l_user_id,
421 l_language,
422 l_language_code,
423 l_date_format,
424 l_date_language,
425 l_numeric_characters,
426 l_nls_sort,
427 l_nls_territory,
428 l_limit_time,
429 l_limit_connects,
430 l_org_id,
431 l_timeout);
432
433 --preserve the existing session durring switch from anon user.
434 -- Session Hijacking. Reset xsid whenever session is upgraded from GUEST to user
435 l_new_xsid := fnd_session_management.NewXSID;
436 UPDATE icx_sessions
437 SET user_id = l_user_id,
438 nls_language = l_language,
439 language_code = l_language_code,
440 date_format_mask = l_date_format,
441 nls_date_language = l_date_language,
442 nls_numeric_characters = l_numeric_characters,
443 nls_sort = l_nls_sort,
444 nls_territory = l_nls_territory,
445 limit_time = l_limit_time,
446 limit_connects = l_limit_connects,
447 org_id = l_org_id,
448 last_connect = sysdate,
449 first_connect = SYSDATE,
450 counter = 1,
451 xsid = l_new_xsid
452 WHERE session_id = l_session_id;
453 owa_util.mime_header('text/html', FALSE);
454 -- Send the new XSID
455 icx_sec.sendsessioncookie(l_session_id);
456 owa_util.redirect_url(l_url_requested);
457 owa_util.http_header_close;
458 --end code for preservation of session_id when switching from anonymous user to authenticated user
459 ELSE
460
461 --this is not an anonymous session conversion.. create a new session and send a new cookie!
462 --Need to check apps_sso profile and determine if we are Portal or PHP and set mode accordingly
463 IF (nvl(l_apps_sso,'SSWA')='SSWA')
464 THEN
465 l_mode := '115P';
466 ELSIF l_apps_sso='SSWA_SSO'
467 THEN
468 l_mode := '115J';
469 ELSE
470 l_mode := '115X';
471 END IF;
472 l_session_id := icx_sec.createSession
473 (p_user_id => l_user_id,
474 c_mode_code => l_mode);
475 owa_util.mime_header('text/html', FALSE);
476 owa_cookie.send(name=>'DEBUG_COOKIE', value=>'-1', expires=>'', path=>'/');
477 owa_cookie.send(name=>'WF_WORKLIST_MODE', value=>'-1', expires=>'', path=>'/');-- mputman added for 1903545
478 icx_sec.sendsessioncookie(l_session_id);
479 owa_util.redirect_url(l_url_requested);
480 owa_util.http_header_close;
481
482 END IF;
483
484 exception
485 when others then
486 IF ((nvl(l_apps_sso,'SSWA')='SSO_SDK') OR
487 (nvl(l_apps_sso,'SSWA')='SSWA_SSO')) AND
488 (icx_sec.g_window_cookie_name = 'X')
489 THEN
490 l_url_requested := fnd_sso_manager.getLoginUrl;
491 owa_util.mime_header('text/html', FALSE);
492 owa_util.redirect_url(l_url_requested);
493 owa_util.http_header_close;
494 ELSE
495 raise;
496 END IF;
497 end sign_on;
498
499 procedure navigate is
500
501 l_function_id number;
502 l_session_id number;
503
504 begin
505
506 select FUNCTION_ID
507 into l_function_id
508 from FND_FORM_FUNCTIONS
509 where FUNCTION_NAME = 'FND_NAVIGATE_PAGE';
510
511 if icx_sec.validateSession
512 then
513 l_session_id := icx_sec.getID(icx_sec.pv_session_id);
514
515 OracleApps.runFunction(c_function_id => l_function_id,
516 n_session_id => l_session_id);
517 end if;
518
519 end;
520
521 PROCEDURE convertSession
522 IS
523 --this should only be called from VSP when validate_only='N'
524
525
526 l_gen_redirect_url varchar2(32000);
527 l_urlrequested varchar2(32000);
528 l_urlcancel varchar2(32000);
529 l_procedure_call varchar2(32000);
530 l_call integer;
531 l_dummy integer;
532 l_defined boolean;
533 l_listener_token VARCHAR2(240);
534 l_apps_sso VARCHAR2(30);
535
536
537 begin
538 --lower(owa_util.get_cgi_env('REQUEST_PROTOCOL'))||'://'||
539 l_urlrequested :=
540 FND_WEB_CONFIG.PROTOCOL||'//'||
541 owa_util.get_cgi_env('SERVER_NAME')||':'||
542 owa_util.get_cgi_env('SERVER_PORT')||
543 owa_util.get_cgi_env('SCRIPT_NAME')||
544 owa_util.get_cgi_env('PATH_INFO')||'?'||
545 owa_util.get_cgi_env('QUERY_STRING');
546
547 fnd_profile.get(name => 'APPS_SSO',
548 val => l_apps_sso);
549 -- Wait SSO
550 IF ((nvl(l_apps_sso,'SSWA')='SSO_SDK') OR (nvl(l_apps_sso,'SSWA')='SSWA_SSO')) THEN
551
552 l_listener_token := OracleSSWA.listener_token;
553
554 l_call := dbms_sql.open_cursor;
555 -- l_gen_redirect_url:= wwsec_sso_enabler.generate_redirect(p_lsnr_token => l_listener_token,
556 -- p_url_requested => l_urlrequested,
557 -- p_url_cancel => l_urlcancel);
558 l_procedure_call := ':l_gen_redirect_url := wwsec_sso_enabler.generate_redirect'||
559 '(p_lsnr_token => :l_listener_token'||
560 ',p_url_requested => :l_urlrequested'||
561 ',p_url_cancel => :l_urlcancel)';
562
563 dbms_sql.parse(l_call,'declare l_gen_redirect_url varchar2(32000); begin '||l_procedure_call||'; end;',dbms_sql.native);
564
565 l_gen_redirect_url := '';
566 for i in 1..100 loop -- set l_gen_redirect_url to 2000 characters
567 l_gen_redirect_url := l_gen_redirect_url||'12345678901234567890';
568 end loop;
569
570 dbms_sql.bind_variable(l_call,'l_gen_redirect_url',l_gen_redirect_url);
571 dbms_sql.bind_variable(l_call,'l_listener_token',l_listener_token);
572 dbms_sql.bind_variable(l_call,'l_urlrequested',l_urlrequested);
573 dbms_sql.bind_variable(l_call,'l_urlcancel',l_urlcancel);
574
575 l_dummy := dbms_sql.execute(l_call);
576
577 dbms_sql.variable_value(l_call,'l_gen_redirect_url',l_gen_redirect_url);
578
579 dbms_sql.close_cursor(l_call);
580
581 END IF;--apps_sso profile
582 owa_util.redirect_url(l_gen_redirect_url);
583
584 exception
585 when others then
586 -- htp.p(SQLERRM);
587 htp.p(dbms_utility.format_error_stack);
588
589 end;
590
591 procedure execute (F IN VARCHAR2,
592 E in VARCHAR2,
593 P IN VARCHAR2,
594 L IN VARCHAR2) is
595
596 --f = function_name --mutually exclusive
597 --e = encrypted parameter string --mutually exclusive
598 --p = parameters (encrypted)
599 --l = lanaguage code
600
601 l_session_id number;
602 l_text varchar2(2000);
603 l_parameters icx_on_utilities.v80_table;
604 l_resp_appl_id number;
605 l_responsibility_id number;
606 l_security_group_id number;
607 l_function_id number;
608 l_function_type varchar2(30);
609 l_menu_id number;
610 l_validate boolean;
611 l_error_message varchar2(2000);
612 err_mesg varchar2(240);
613 err_num number;
614 l_user_id number;
615 l_user_name varchar2(100);
616 l_org_id number;
617 l_multi_org_flag varchar2(30);
618 l_profile_defined boolean;
619 e_session_invalid exception;
620 e_invalid_function exception;
621 e_refresh_4_cookie EXCEPTION;
622 l_guest_profile_value varchar2(80);
623 l_guest_name varchar2(80);
624 l_guest_pwd varchar2(80);
625 c_anchor varchar2(2000);
626 l_url varchar2(2000);
627 l_apps_agent varchar2(2000);
628 new_encrypted_string varchar2(2000);
629 l_apps_sso VARCHAR2(100);
630 l_params VARCHAR2(2000);
631 C_LOGIN_MSG VARCHAR2(400);
632 C_ERROR_MSG VARCHAR2(400);
633 l_exeurl VARCHAR2(2000);
634 l_mode VARCHAR2(10);
635 l_language varchar2(30);
636 l_lang_code varchar2(30);
637 f_type VARCHAR2(30);
638 l_servlet_agent VARCHAR2(800);
639 l_dbc VARCHAR2(70);
640 nls_base_lang varchar2(30);
641 l_apps_web_agent VARCHAR2(2000);
642 l_recreate_code varchar2(240);
643
644 begin
645
646 icx_sec.ServerLevel;
647
648 --add parameter for sgid and init it here.
649
650 IF e IS NOT NULL THEN
651 l_text := icx_call.decrypt(E);
652 icx_on_utilities.unpack_parameters(l_text,l_parameters);
653 l_resp_appl_id := nvl(l_parameters(1),178);
654 l_responsibility_id := l_parameters(2);
655 l_security_group_id := l_parameters(3);
656 l_function_id := l_parameters(4);
657 --verify that this is all we need to call to get the selects to work in hosted env.
658 fnd_global.SET_SECURITY_GROUP_ID_CONTEXT(l_security_group_id);
659 ELSIF f IS NOT NULL THEN
660 --We dont have a secgrpid set here!!!!!
661
662 --get function_id.
663 BEGIN
664 SELECT function_id
665 INTO l_function_id
666 FROM fnd_form_functions
667 WHERE function_name = F;
668 l_resp_appl_id := NULL;
669 l_responsibility_id :=NULL;
670 l_security_group_id := NULL;
671
672 EXCEPTION
673 WHEN OTHERS THEN
674 RAISE e_invalid_function;
675 END;
676 ELSIF ((E IS NULL) AND (F IS NULL)) THEN
677 raise e_invalid_function;
678
679 END IF;--e is null
680 -- function identified
681 fnd_profile.get_specific(
682 name_z => 'APPS_SSO',
683 user_id_z => l_user_id,
684 val_z => l_apps_sso,
685 defined_z => l_profile_defined);
686
687
688
689 --Bug 2545562/2667712
690 select nls_language into nls_base_lang from fnd_languages_vl
691 where installed_flag = 'B';
692
693 BEGIN
694
695 if L is not null
696 then
697 select nls_language into l_language from fnd_languages_vl
698 where LANGUAGE_CODE = L and installed_flag in ('B', 'I');
699 end if;
700 exception
701 when NO_DATA_FOUND
702 then
703 l_language := nls_base_lang;
704
705 END;
706
707 FND_GLOBAL.set_nls_context(
708 p_nls_language => l_language);
709 -- p_nls_territory =>'AMERICA');
710 -- l_language := L;
711
712 l_session_id := icx_sec.getsessioncookie;
713
714 --IF (icx_sec.validatesession(c_validate_only=>'Y')) THEN --there is a session cookie
715
716 IF (l_session_id >0 AND l_session_id IS NOT NULL) THEN --there is a session cookie
717
718 select multi_org_flag
719 into l_multi_org_flag
720 from fnd_product_groups
721 where rownum < 2;
722
723 if l_multi_org_flag = 'Y'
724 then
725 fnd_profile.get_specific(
726 name_z => 'ORG_ID',
727 responsibility_id_z => l_responsibility_id,
728 application_id_z => l_resp_appl_id,
729 val_z => l_org_id,
730 defined_z => l_profile_defined);
731 end if;
732
733
734 -- 2646577, nvl(l_language,NLS_LANGUAGE)
735
736 update ICX_SESSIONS
737 set RESPONSIBILITY_APPLICATION_ID = l_resp_appl_id,
738 RESPONSIBILITY_ID = l_responsibility_id,
739 SECURITY_GROUP_ID = l_security_group_id,
740 ORG_ID = l_org_id,
741 FUNCTION_ID = l_function_id,
742 FUNCTION_TYPE = l_function_type,
743 MENU_ID = l_menu_id,
744 NLS_LANGUAGE = nvl(l_language,NLS_LANGUAGE),
745 LANGUAGE_CODE = nvl(L,LANGUAGE_CODE)
746 where SESSION_ID = l_session_id;
747 --where SESSION_ID = icx_sec.getsessioncookie;
748
749 commit;
750
751 ELSE
752 --get anonymous user info
753 -- fnd_profile.get(name => 'GUEST_USER_PWD',
754 -- val => l_guest_profile_value);
755 -- Using new api to retrieve GUEST credentials.
756 l_guest_profile_value := fnd_web_sec.get_guest_username_pwd;
757 l_guest_name := SUBSTR(l_guest_profile_value, 1, INSTR(l_guest_profile_value, '/') -1);
758 --l_guest_pwd := SUBSTR(l_guest_profile_value, INSTR(l_guest_profile_value, '/') + 1);
759 l_guest_name := upper(l_guest_name);
760 SELECT user_id
761 into l_user_id
762 from fnd_user
763 where user_name = l_guest_name;
764
765 --create anonymous session
766
767 --Need to check apps_sso profile and determine if we are Portal or PHP and set mode accordingly
768 IF (nvl(l_apps_sso,'SSWA')='SSWA')
769 THEN
770 l_mode := '115P';
771 ELSIF l_apps_sso='SSWA_SSO'
772 THEN
773 l_mode := '115J';
774 ELSE
775 l_mode := '115X';
776 END IF;
777 l_session_id:=icx_sec.CREATESession(l_user_id, l_mode);
778 /*
779 fnd_profile.get_specific(
780 name_z => 'APPS_WEB_AGENT',
781 responsibility_id_z => l_responsibility_id,
782 application_id_z => l_resp_appl_id,
783 val_z => l_apps_web_agent,
784 defined_z => l_profile_defined);
785 */
786 fnd_profile.get(name => 'APPS_WEB_AGENT',
787 val => l_apps_web_agent);
788
789 l_url := FND_WEB_CONFIG.TRAIL_SLASH(l_apps_web_agent);
790
791 --c_anchor := 'OracleSSWA.Execute?E='||wfa_html.conv_special_url_chars(icx_call.encrypt(l_resp_appl_id||'*'||l_responsibility_id||'*'||l_security_group_id||'*'||l_function_id||'*'||'**]'));
792
793 --bug 267712 added L parm to anchor
794 c_anchor := 'OracleSSWA.Execute?F='||F||'&E='||E||'&P='||P||'&L='||L;
795
796 owa_util.mime_header('text/html', FALSE);
797 owa_cookie.send(name=>'WF_WORKLIST_MODE', value=>'-1', expires=>'', path=>'/');-- mputman added for 1903545
798 icx_sec.sendSessionCookie(l_session_id);
799 owa_util.http_header_close;
800
801 RAISE e_refresh_4_cookie;
802
803 END IF;--cookie
804
805 --need to do validate_only so if fails, we can still run if public
806 --set a global to -999 to fnd_global will verify user-resp relationship
807 icx_sec.g_prog_appl_id := -999;
808 IF icx_sec.VALIDATESession(c_validate_only => 'Y') THEN
809 icx_sec.g_prog_appl_id := -1;
810
811 -- IF (fnd_function.test_id(l_function_id))
812 -- THEN
813 --either current user or anonymous user has access to this function.. run it.
814 IF E IS NOT NULL and icx_sec.g_login_id is not null THEN
815 fnd_signon.audit_web_responsibility(icx_sec.g_login_id,
816 l_responsibility_id,
817 l_resp_appl_id,
818 l_responsibility_id); -- mputman added for 1941776
819 END IF;
820
821 SELECT TYPE
822 INTO f_type
823 FROM fnd_form_functions
824 WHERE function_id = l_function_id;
825
826 IF f_type = 'FORM' THEN
827
828 /* 3220523 Replace hard coded RF.jsp
829 fnd_profile.get_specific(
830 name_z => 'APPS_SERVLET_AGENT',
831 val_z => l_servlet_agent,
832 defined_z => l_profile_defined);
833 l_servlet_agent:=FND_WEB_CONFIG.TRAIL_SLASH(replace(upper(l_servlet_agent),'OA_SERVLETS','OA_HTML'));
834
835 fnd_profile.get(name => 'APPS_DATABASE_ID',
836 val => l_dbc);
837
838 if l_dbc is null
839 then
840 l_dbc := FND_WEB_CONFIG.DATABASE_ID;
841 end if;
842
843 l_servlet_agent:=l_servlet_agent||'jsp/fnd/RF.jsp?dbc='||l_dbc||
844 '&function_id='|| l_function_id ||
845 '&resp_id=' || l_responsibility_id ||
846 '&resp_appl_id=' || l_resp_appl_id ||
847 '&security_group_id=' || nvl(l_security_group_id,'0');
848 */
849
850 l_servlet_agent:= FND_RUN_FUNCTION.GET_RUN_FUNCTION_URL
851 (P_FUNCTION_ID => l_function_id,
852 P_RESP_APPL_ID => l_resp_appl_id,
853 P_RESP_ID => l_responsibility_id,
854 P_SECURITY_GROUP_ID => nvl(l_security_group_id,'0'));
855
856 owa_util.redirect_url(l_servlet_agent);
857
858 ELSE
859 IF p IS NOT NULL THEN
860 l_params := icx_call.decrypt(P);
861 --p_resp_appl_id,p_responsibility_id,p_security_group_id,
862 --p_menu_id,c_function_id,p_function_type,p_page_id
863 OracleApps.runfunction(p_resp_appl_id => l_resp_appl_id,
864 p_responsibility_id => l_responsibility_id,
865 p_security_group_id => nvl(l_security_group_id,'0'),
866 p_function_type => f_type,
867 c_function_id => l_function_id,
868 n_session_id => icx_Sec.g_session_id,
869 c_parameters => l_params);
870 ELSE
871 OracleApps.runfunction(p_resp_appl_id => l_resp_appl_id,
872 p_responsibility_id => l_responsibility_id,
873 p_security_group_id => nvl(l_security_group_id,'0'),
874 p_function_type => f_type,
875 c_function_id => l_function_id,
876 n_session_id => icx_Sec.g_session_id);
877 END IF;
878 END IF;
879
880 icx_sec.g_prog_appl_id := -1; --set global back to -1
881
882 ELSIF icx_sec.check_session(p_session_id => l_session_id) = 'EXPIRED'
883 THEN
884 icx_sec.g_prog_appl_id := -1;
885
886 select USER_NAME
887 into l_user_name
888 from FND_USER fu,
889 ICX_SESSIONS i
890 where i.SESSION_ID = l_session_id
891 and i.USER_ID = fu.USER_ID;
892
893 l_recreate_code := icx_call.encrypt(l_session_id||'*'||l_user_name||'**]');
894
895 fnd_profile.get_specific(
896 name_z => 'APPS_WEB_AGENT',
897 responsibility_id_z => l_responsibility_id,
898 application_id_z => l_resp_appl_id,
899 val_z => l_apps_web_agent,
900 defined_z => l_profile_defined);
901 l_url := FND_WEB_CONFIG.TRAIL_SLASH(l_apps_web_agent);
902 l_url := l_url||'OracleSSWA.Execute?E='||icx_call.encrypt(l_resp_appl_id||'*'||l_responsibility_id||'*'||l_security_group_id||'*'||l_function_id||'*'||'**]')||'&'||'P='||P||'&'||'L='||L;
903
904 OracleApps.displayLogin(i_direct => l_url,
905 recreate => l_recreate_code);
906
907 --function available to current or anonymous with a valid session
908 ELSIF (fnd_function.test_id(l_function_id)) -- OR (l_function_id=2594)
909 THEN -- VS Failed.. if function is public, create session and go!
910 icx_sec.g_prog_appl_id := -1;
911 --get anonymous user info
912 -- fnd_profile.get(name => 'GUEST_USER_PWD',
913 -- val => l_guest_profile_value);
914 -- Using new api to retrieve GUEST credentials.
915 l_guest_profile_value := fnd_web_sec.get_guest_username_pwd;
916 l_guest_name := SUBSTR(l_guest_profile_value, 1, INSTR(l_guest_profile_value, '/') -1);
917 --l_guest_pwd := SUBSTR(l_guest_profile_value, INSTR(l_guest_profile_value, '/') + 1);
918 l_guest_name := upper(l_guest_name);
919 SELECT user_id
920 into l_user_id
921 from fnd_user
922 where user_name = l_guest_name;
923
924 --create anonymous session
925
926 --Need to check apps_sso profile and determine if we are Portal or PHP and set mode accordingly
927 IF (nvl(l_apps_sso,'SSWA')='SSWA')
928 THEN
929 l_mode := '115P';
930 ELSIF l_apps_sso='SSWA_SSO'
931 THEN
932 l_mode := '115J';
933 ELSE
934 l_mode := '115X';
935 END IF;
936 l_session_id:=icx_sec.CREATESession(l_user_id, l_mode);
937 fnd_profile.get_specific(
938 name_z => 'APPS_WEB_AGENT',
939 responsibility_id_z => l_responsibility_id,
940 application_id_z => l_resp_appl_id,
941 val_z => l_apps_web_agent,
942 defined_z => l_profile_defined);
943 l_url := FND_WEB_CONFIG.TRAIL_SLASH(l_apps_web_agent);
944 c_anchor := 'OracleSSWA.Execute?E='||wfa_html.conv_special_url_chars(icx_call.encrypt(l_resp_appl_id||'*'||l_responsibility_id||'*'||l_security_group_id||'*'||l_function_id||'*'||'**]'));
945
946 owa_util.mime_header('text/html', FALSE);
947 owa_cookie.send(name=>'WF_WORKLIST_MODE', value=>'-1', expires=>'', path=>'/');-- mputman added for 1903545
948 icx_sec.sendSessionCookie(l_session_id);
949 owa_util.http_header_close;
950
951 select multi_org_flag
952 into l_multi_org_flag
953 from fnd_product_groups
954 where rownum < 2;
955 if l_multi_org_flag = 'Y' THEN
956 fnd_profile.get_specific(name_z => 'ORG_ID',
957 responsibility_id_z => l_responsibility_id,
958 application_id_z => l_resp_appl_id,
959 val_z => l_org_id,
960 defined_z => l_profile_defined);
961 end if;
962 update ICX_SESSIONS
963 set RESPONSIBILITY_APPLICATION_ID = l_resp_appl_id,
964 RESPONSIBILITY_ID = l_responsibility_id,
965 SECURITY_GROUP_ID = l_security_group_id,
966 ORG_ID = l_org_id,
967 FUNCTION_ID = l_function_id,
968 FUNCTION_TYPE = l_function_type
969 where SESSION_ID = l_session_id;
970 commit;
971 htp.p('<META HTTP-EQUIV=Refresh CONTENT="1; URL='||l_url||c_anchor||'">');
972
973 ELSE
974 icx_sec.g_prog_appl_id := -1;
975 -- session not valid... or current user cannot access function
976 -- function is not public... need to login.
977 IF ((nvl(l_apps_sso,'SSWA')='SSO_SDK') OR
978 (nvl(l_apps_sso,'SSWA')='SSWA_SSO'))THEN
979 OracleSSWA.convertSession;
980 ELSE
981
982 --need a session for convertsession to work
983 --get anonymous user info
984 -- fnd_profile.get(name => 'GUEST_USER_PWD',
985 -- val => l_guest_profile_value);
986 -- Using new api to retrieve GUEST credentials.
987 l_guest_profile_value := fnd_web_sec.get_guest_username_pwd;
988 l_guest_name := SUBSTR(l_guest_profile_value, 1, INSTR(l_guest_profile_value, '/') -1);
989 --l_guest_pwd := SUBSTR(l_guest_profile_value, INSTR(l_guest_profile_value, '/') + 1);
990 l_guest_name := upper(l_guest_name);
991 SELECT user_id
992 into l_user_id
993 from fnd_user
994 where user_name = l_guest_name;
995 ----
996 l_session_id:=icx_sec.getsessioncookie;
997 IF ((l_user_id <> icx_sec.g_user_id) OR (icx_sec.check_session(icx_sec.getsessioncookie) = 'INVALID')) THEN
998 --create anonymous session
999
1000 --Need to check apps_sso profile and determine if we are Portal or PHP and set mode accordingly
1001 IF (nvl(l_apps_sso,'SSWA')='SSWA')
1002 THEN
1003 l_mode := '115P';
1004 ELSIF l_apps_sso='SSWA_SSO'
1005 THEN
1006 l_mode := '115J';
1007 ELSE
1008 l_mode := '115X';
1009 END IF;
1010 l_session_id:=icx_sec.CREATESession(l_user_id, l_mode);
1011 -- l_url := FND_WEB_CONFIG.TRAIL_SLASH(fnd_profile.value('APPS_WEB_AGENT'));
1012 -- c_anchor := 'OracleSSWA.Execute?E='||wfa_html.conv_special_url_chars(icx_call.encrypt(l_resp_appl_id||'*'||l_responsibility_id||'*'||l_security_group_id||'*'||l_function_id||'*'||'**]'));
1013
1014 owa_util.mime_header('text/html', FALSE);
1015 owa_cookie.send(name=>'WF_WORKLIST_MODE', value=>'-1', expires=>'', path=>'/');-- mputman added for 1903545
1016 icx_sec.sendSessionCookie(l_session_id);
1017 owa_util.http_header_close;
1018 --- end create new session
1019
1020 END IF;
1021
1022 select multi_org_flag
1023 into l_multi_org_flag
1024 from fnd_product_groups
1025 where rownum < 2;
1026 if l_multi_org_flag = 'Y' THEN
1027 fnd_profile.get_specific(name_z => 'ORG_ID',
1028 responsibility_id_z => l_responsibility_id,
1029 application_id_z => l_resp_appl_id,
1030 val_z => l_org_id,
1031 defined_z => l_profile_defined);
1032 end if;
1033 update ICX_SESSIONS
1034 set RESPONSIBILITY_APPLICATION_ID = l_resp_appl_id,
1035 RESPONSIBILITY_ID = l_responsibility_id,
1036 SECURITY_GROUP_ID = l_security_group_id,
1037 ORG_ID = l_org_id,
1038 FUNCTION_ID = l_function_id,
1039 FUNCTION_TYPE = l_function_type
1040 where SESSION_ID = l_session_id;
1041 commit;
1042
1043
1044 new_encrypted_string := icx_call.encrypt(l_session_id||'*'||
1045 l_resp_appl_id||'*'||
1046 l_responsibility_id||'*'||
1047 l_security_group_id||'*'||
1048 l_function_id||'*'||
1049 p||'**]');
1050 OracleApps.convertSession(new_encrypted_String);
1051
1052 END IF;
1053 END IF;
1054
1055 EXCEPTION
1056 WHEN e_refresh_4_cookie THEN
1057 htp.p('<META HTTP-EQUIV=Refresh CONTENT="1; URL='||l_url||c_anchor||'">');
1058
1059 WHEN e_invalid_function THEN
1060
1061 fnd_message.set_name('ICX','ICX_INVALID_FUNCTION');
1062 c_error_msg := fnd_message.get;
1063 fnd_message.set_name('ICX','ICX_SIGNIN_AGAIN');
1064 c_login_msg := fnd_message.get;
1065 OracleApps.displayLogin(c_error_msg||' '||c_login_msg,'IC','Y');
1066
1067 WHEN OTHERS THEN
1068 icx_sec.g_prog_appl_id := -1;
1069
1070 -- fnd_message.set_name('ICX','ICX_SESSION_FAILED');
1071 -- c_error_msg := fnd_message.get;
1072 -- fnd_message.set_name('ICX','ICX_SIGNIN_AGAIN');
1073 -- c_login_msg := fnd_message.get;
1074
1075 fnd_message.set_name('ICX','ICX_CONTACT_WEBMASTER');
1076 c_error_msg := fnd_message.get;
1077 c_login_msg := dbms_utility.format_error_stack;
1078
1079 OracleApps.displayLogin(c_error_msg||' '||c_login_msg,'IC','Y');
1080 END;
1081
1082 FUNCTION SSORedirect (p_req_url IN VARCHAR2,
1083 p_cancel_url IN VARCHAR2)
1084 RETURN VARCHAR2
1085 IS
1086
1087 l_gen_redirect_url varchar2(2024);
1088 l_urlrequested varchar2(2024);
1089 l_urlcancel varchar2(2024);
1090 l_listener_token varchar2(240);
1091 l_procedure_call varchar2(32000);
1092 l_call integer;
1093 l_dummy integer;
1094 l_defined boolean;
1095
1096 BEGIN
1097
1098 IF p_req_url IS NULL THEN
1099 fnd_profile.get_specific(name_z => 'APPS_PORTAL',
1100 val_z => l_urlrequested,
1101 defined_z => l_defined );
1102 ELSE
1103 l_urlrequested :=p_req_url;
1104 END IF;
1105 IF p_cancel_url IS NULL THEN
1106 fnd_profile.get_specific(name_z => 'APPS_PORTAL',
1107 val_z => l_urlcancel,
1108 defined_z => l_defined );
1109 ELSE
1110 l_urlcancel:=p_cancel_url;
1111 END IF;
1112
1113 -- Wait SSO
1114
1115 l_listener_token := OracleSSWA.listener_token;
1116 -- l_gen_redirect_url := wwsec_sso_enabler.generate_redirect(p_lsnr_token => l_listener_token,
1117 -- p_url_requested => l_urlrequested,
1118 -- p_url_cancel => l_urlcancel);
1119
1120
1121 l_call := dbms_sql.open_cursor;
1122
1123 l_procedure_call := ':l_gen_redirect_url := wwsec_sso_enabler.generate_redirect'||
1124 '(p_lsnr_token => :l_listener_token'||
1125 ',p_url_requested => :l_urlrequested'||
1126 ',p_url_cancel => :l_urlcancel)';
1127
1128 dbms_sql.parse(l_call,'declare l_gen_redirect_url varchar2(32000); begin '||l_procedure_call||'; end;',dbms_sql.native);
1129
1130 l_gen_redirect_url := '';
1131 for i in 1..100 loop -- set l_gen_redirect_url to 2000 characters
1132 l_gen_redirect_url := l_gen_redirect_url||'12345678901234567890';
1133 end loop;
1134
1135 dbms_sql.bind_variable(l_call,'l_gen_redirect_url',l_gen_redirect_url);
1136 dbms_sql.bind_variable(l_call,'l_listener_token',l_listener_token);
1137 dbms_sql.bind_variable(l_call,'l_urlrequested',l_urlrequested);
1138 dbms_sql.bind_variable(l_call,'l_urlcancel',l_urlcancel);
1139 l_dummy := dbms_sql.execute(l_call);
1140 dbms_sql.variable_value(l_call,'l_gen_redirect_url',l_gen_redirect_url);
1141
1142 dbms_sql.close_cursor(l_call);
1143
1144 RETURN l_gen_redirect_url;
1145 END;
1146
1147
1148
1149 PROCEDURE logout
1150 IS
1151 --using static image from fwk UI media.
1152 -- hextoraw to store a value in a blob wont work in 8i
1153 -- cant use a raw bacause wpg_docload cant accept a raw.
1154
1155 -- l_img RAW(1000);
1156 BEGIN
1157
1158 -- l_img:= hextoraw('4749463839610D000D00B30F'||
1159 -- '000000008000000080008080'||
1160 -- '00000080800080008080C0C0'||
1161 -- 'C0808080FF000000FF00FFFF'||
1162 -- '000000FFFF00FF00FFFFFFFF'||
1163 -- 'FF21F9040100000F002C0000'||
1164 -- '00000D000D0040041FF0C949'||
1165 -- '2B618CD4C92AD65B2872DF73'||
1166 -- '4965088EEC8865CF4BAE64AA'||
1167 -- 'D2ED1601003B');
1168 owa_util.mime_header('image/gif', FALSE);
1169 -- Reset cookie
1170 owa_cookie.send
1171 (
1172 name => icx_sec.getsessioncookiename,
1173 value => '-1',
1174 path => '/',
1175 domain => icx_sec.getsessioncookiedomain
1176 );
1177 --htp.p('Content-Length: ' || length(l_img));
1178 htp.p('Expires: Thu, 29 Oct 1970 17:04:19 GMT');
1179 htp.p('Pragma: no-cache');
1180 htp.p('Cache-Control: no-cache');
1181 owa_util.redirect_url('/OA_MEDIA/completeind_status.gif');
1182 owa_util.http_header_close;
1183 --wpg_docload.download_file(l_img);
1184 EXCEPTION
1185 WHEN OTHERS THEN
1186 -- htp.p(sqlerrm);
1187 htp.p(dbms_utility.format_error_stack);
1188 END logout;
1189
1190 end OracleSSWA;