[Home] [Help]
PACKAGE BODY: APPS.ICX_SEC
Source
1 package body icx_sec as
2 /* $Header: ICXSESEB.pls 120.2 2007/12/28 09:54:56 stadepal ship $ */
3
4 -- ***********************************************
5 -- function NewSessionId
6 -- ***********************************************
7
8 function NewSessionId(dummy in number)
9 return number is
10
11 l_session_id number;
12 l_new_session_id number;
13 x_session_id varchar2(1);
14
15 begin
16
17 l_session_id := abs(dbms_random.random);
18
19 loop
20
21 select 'Y' into x_session_id from icx_sessions
22 where session_id = l_session_id;
23
24 if x_session_id = 'Y'
25 then
26 l_session_id := abs(dbms_random.random);
27 end if;
28 end loop;
29
30 EXCEPTION
31 WHEN no_data_found THEN
32 l_session_id := l_session_id;
33
34 l_new_session_id := l_session_id;
35
36 -- Moved terminare above return
37
38 dbms_random.terminate;
39
40 return(l_new_session_id);
41
42
43 end;
44
45 -- ***********************************************
46 -- function NewSessionId
47 -- ***********************************************
48
49 --function NewSessionId(dummy in number)
50 -- return number is
51
52 --l_session_id number;
53 --l_new_session_id number;
54
55 --begin
56
57 --select icx_sessions_s.nextval
58 -- into l_session_id
59 -- from sys.dual;
60
61 -- l_random_num := Random1('X');
62 -- l_session_id := l_session_id||l_random_num;
63 -- l_new_session_id := l_session_id;
64
65 --return(l_new_session_id);
66 --end;
67
68 -- ***********************************************
69 -- function validatePassword
70 -- ***********************************************
71
72 function validatePassword(c_user_name in varchar2,
73 c_user_password in varchar2,
74 n_session_id out NOCOPY number,
75 c_validate_only in varchar2,
76 c_mode_code in varchar2,
77 c_url in varchar2)
78 return varchar2 is
79
80 u fnd_user%rowtype;
81 c_server_name varchar2(240);
82 c_server_port varchar2(80);
83 l_server varchar2(240);
84 c_script_name varchar2(80);
85 l_host_instance varchar2(240);
86 l_url varchar2(2000);
87 l_result varchar2(30);
88 l_app varchar2(30);
89 l_msg_code varchar2(30);
90 l_valid2 varchar2(240);
91 v_user_id number;
92 v_user_name varchar2(80);
93 v_password varchar2(80);
94 v_encrypted_psswd varchar2(1000);
95 v_encrypted_upper_psswd varchar2(1000);
96 c_error_msg varchar2(2000);
97 c_login_msg varchar2(2000);
98 e_signin_invalid exception;
99 e_account_expired exception;
100 e_invalid_password exception;
101 e_java_password exception;
102 l_enc_fnd_pwd varchar2(100);
103 l_enc_user_pwd varchar2(100);
104 l_expired varchar2(30);
105 return_to_url varchar2(2000);
106 l_agent varchar2(240);
107 t_user_id NUMBER; -- added for bug 1916792
108 t_language VARCHAR2(240); -- added for bug 1916792
109 c_nls_language VARCHAR2(240); -- added for bug 1916792
110 l_profile_defined boolean; -- added for bug 1916792
111 b_hosted BOOLEAN DEFAULT FALSE;
112 l_hosted_profile VARCHAR2(50);
113 l_remote_addr varchar2(80);
114 c_error_msg1 varchar2(240);
115 p_loginfrom varchar2(30);
116
117
118 BEGIN
119 --htp.p('VP');--mputman debug
120 -- start additions for 1916792
121 -- icx_sec.g_security_group_id:=c_sec_grp_id; --mputman hosted update
122 --SECURITY_GROUP_KEY in the FND_SECURITY_GROUPS
123 fnd_profile.get(name => 'ENABLE_SECURITY_GROUPS',
124 val => l_hosted_profile);
125
126 IF (upper(l_hosted_profile)='HOSTED') THEN
127 b_hosted:=TRUE;
128 fnd_global.apps_initialize(user_id => -1,
129 resp_id => -1,
130 resp_appl_id => -1,
131 security_group_id => icx_sec.g_security_group_id);--mputman hosted update
132
133 END IF;
134
135
136 -- start additions for 1916792
137 BEGIN
138 SELECT user_id
139 INTO t_user_id
140 FROM fnd_user
141 WHERE user_name=upper(c_user_name);
142 EXCEPTION
143 WHEN no_data_found THEN
144 t_user_id := NULL;
145 END;
146
147 IF t_user_id IS NOT NULL THEN
148 fnd_profile.get_specific(name_z => 'ICX_LANGUAGE',
149 user_id_z => t_user_id,
150 val_z => t_language,
151 defined_z => l_profile_defined);
152 ELSE
153 t_language := fnd_profile.value('ICX_LANGUAGE');
154
155 --start bug 3100151
156
157 l_remote_addr := owa_util.get_cgi_env('REMOTE_ADDR');
158
159 insert into icx_failures
160 (user_name,password,failure_code,failure_date,
161 created_by, creation_date, last_updated_by,
162 last_update_date, last_update_login)
163 values
164 (l_remote_addr,-1,
165 'ICX_ACCT_EXPIRED',sysdate,
166 nvl(u.user_id,-1), sysdate, nvl(u.user_id,-1),
167 sysdate, u.user_id);
168 commit;
169
170 --end bug 3100151
171
172 END IF;
173 if t_language is not null
174 and nvl(g_language_c,'XXXXX') <> t_language
175 then
176 FND_GLOBAL.set_nls_context(p_nls_language => t_language);--mputman changed for performance and consist.
177 --c_nls_language := ''''||t_language||'''';
178 --dbms_session.set_nls('NLS_LANGUAGE' , c_nls_language);
179 g_language_c := t_language;
180 end if;
181
182 -- end additions for 1916792
183
184 if (c_user_name is NULL or c_user_password is NULL)
185 then
186 raise e_signin_invalid;
187 end if;
188
189 --bug 3238722
190 p_loginfrom := 'ICX';
191 l_result := fnd_web_sec.validate_login(upper(c_user_name), c_user_password,
192 g_p_loginID, g_p_expired, p_loginfrom);
193
194 -- Begin Bug 1961641
195 if l_result = 'N'
196 then
197 select *
198 into u
199 from fnd_user
200 where user_name = UPPER(c_user_name);
201
202 if u.user_id = 6
203 then
204 c_error_msg := fnd_message.get;
205 fnd_message.set_name('ICX','ICX_SIGNIN_INVALID');
206 c_login_msg := fnd_message.get;
207 OracleApps.displayLogin(c_error_msg||' '||c_login_msg,'IC','Y');
208 end if;
209
210
211 if u.end_date <= sysdate
212 then
213 raise e_java_password;
214 end if;
215 end if;
216 -- End Bug 1961641
217
218 if l_result = 'Y'
219 then
220 IF b_hosted THEN --mputman hosted update
221
222 fnd_global.apps_initialize(user_id => t_user_id,
223 resp_id => -1,
224 resp_appl_id => -1,
225 security_group_id => icx_sec.g_security_group_id);--mputman added 2092330
226 ELSE
227 fnd_global.apps_initialize(user_id => t_user_id,
228 resp_id => -1,
229 resp_appl_id => -1,
230 security_group_id => -1);--mputman added 2092330
231
232 END IF;
233
234 -- if c_url is null then we don't know where to return the user
235 -- in the situation where the user updates expired password.
236 -- assign a default url.
237 if (c_url is null) then
238 --l_agent := icx_plug_utilities.getPLSQLagent; -- mputman removed 1574527
239 --return_to_url := l_agent || 'OracleMyPage.Home?validate_flag=Y';
240 return_to_url := 'OracleMyPage.Home?validate_flag=Y'; -- removed agent to work in stateful envs mputman 1574527
241 else
242 return_to_url := c_url;
243 end if;
244
245 begin
246 select 'Y'
247 into l_expired
248 from FND_USER
249 where USER_NAME = UPPER(c_user_name)
250 and (PASSWORD_DATE is NULL or
251 (PASSWORD_LIFESPAN_ACCESSES is not NULL and
252 nvl(PASSWORD_ACCESSES_LEFT, 0) < 1) or
253 (PASSWORD_LIFESPAN_DAYS is not NULL and
254 SYSDATE >= PASSWORD_DATE + PASSWORD_LIFESPAN_DAYS));
255 exception
256 when no_data_found then
257 l_expired := 'N';
258 end;
259
260 if (l_expired = 'Y') then
261 OracleApps.displayNewPassword(c_user_name, return_to_url, c_mode_code);
262 return -1;
263
264 else
265
266 select *
267 into u
268 from fnd_user
269 where user_name = UPPER(c_user_name);
270
271 if u.end_date is null or u.end_date > sysdate
272 then
273
274 return NewSession( user_info => u,
275 c_user_name => c_user_name,
276 c_password => c_user_password,
277 n_session_id => n_session_id,
278 c_validate_only => c_validate_only,
279 c_mode_code => c_mode_code);
280
281 else
282 raise e_account_expired;
283 end if; -- u.end_date is null or u.end_date > sysdate
284 end if; -- l_expired
285
286 else
287 -- l_msg_code := fnd_message.get; 2697634
288 fnd_message.parse_encoded(fnd_message.get_encoded,l_app,l_msg_code);
289 if l_msg_code = 'SECURITY_APPL_LOGIN_FAILED'
290 then
291
292 begin
293 select *
294 into u
295 from fnd_user
296 where user_name = UPPER(c_user_name)
297 and WEB_PASSWORD is not null;
298 exception
299 when others then
300 raise e_java_password;
301 end;
302
303 v_encrypted_upper_psswd := to_char(icx_call.crchash( UPPER(c_user_name), UPPER(c_user_password)));
304
305 v_encrypted_psswd := to_char(icx_call.crchash( UPPER(c_user_name),c_user_password));
306
307 if u.WEB_PASSWORD = v_encrypted_upper_psswd or u.WEB_PASSWORD = v_encrypted_psswd
308 then
309 OracleApps.displayNewPassword(i_1 => c_user_name);
310 return '-1';
311 else
312 raise e_java_password;
313 end if;
314 else
315
316 --bug 2505470 - change exception below
317 -- raise e_java_password;
318 raise e_signin_invalid;
319 end if;
320 end if; -- l_valid = '0';
321
322 exception
323 when e_java_password
324 then
325
326 if c_validate_only = 'N'
327 then
328 fnd_message.set_name('ICX','ICX_ACCT_EXPIRED');
329 c_error_msg := fnd_message.get;
330 fnd_message.set_name('ICX','ICX_CONTACT_WEBMASTER');
331 c_login_msg := fnd_message.get;
332
333 OracleApps.displayLogin(c_error_msg||' '||c_login_msg,'IC','Y');
334 end if;
335
336 insert into icx_failures
337 (user_name,password,failure_code,failure_date,
338 created_by, creation_date, last_updated_by,
339 last_update_date, last_update_login)
340 values
341 (c_user_name,-1,
342 'ICX_ACCT_EXPIRED',sysdate,
343 nvl(u.user_id,-1), sysdate, nvl(u.user_id,-1),
344 sysdate, u.user_id);
345
346 return '-1';
347
348 when e_signin_invalid OR e_invalid_password
349 then
350 if c_validate_only = 'N'
351 then
352
353 fnd_message.set_name('ICX','ICX_SIGNIN_INVALID');
354 c_error_msg := fnd_message.get;
355 fnd_message.set_name('ICX','ICX_SIGNIN_AGAIN');
356 c_login_msg := fnd_message.get;
357
358
359 OracleApps.displayLogin(c_error_msg||' '||c_login_msg,'IC','Y');
360
361 end if;
362
363 v_encrypted_psswd := icx_call.encrypt(c_user_password);
364
365 insert into icx_failures
366 (user_name,password,failure_code,failure_date,
367 created_by, creation_date, last_updated_by,
368 last_update_date, last_update_login)
369 values
370 (c_user_name,v_encrypted_psswd,'ICX_SIGNIN_INVALID',sysdate,
371 '-1', sysdate, '-1', sysdate, '-1');
372 return '-1';
373
374 when others
375 then
376 if c_validate_only = 'N'
377 then
378
379 --Start Bug 3161306
380 select fnd_message.get into c_error_msg1 from dual;
381 if c_error_msg1 like 'Oracle error%'
382 then
383 htp.p(c_error_msg1);
384 htp.nl;
385 htp.line;
386 end if;
387 --End Bug 3161306
388
389 fnd_message.set_name('ICX','ICX_SIGNIN_INVALID');
390 c_error_msg := fnd_message.get;
391 fnd_message.set_name('ICX','ICX_SIGNIN_AGAIN');
392 c_login_msg := fnd_message.get;
393
394 OracleApps.displayLogin(c_error_msg||' '||c_login_msg,'IC','Y');
395 end if;
396
397 /*
398 when others then
399 htp.p(SQLERRM);
400 */
401 return '-1';
402 end;
403
404 -- ***********************************************
405 -- Function createSlaveSession
406 -- ***********************************************
407
408 procedure SS(S in varchar2) is
409
410 l_parameters icx_on_utilities.v80_table;
411 l_user_id number;
412 l_responsibility_id number;
413 l_function_id number;
414 l_user fnd_user%rowtype;
415 l_session_id number;
416 c_server_name varchar2(240);
417 c_domain varchar2(240);
418 l_date_format_mask varchar2(240);
419 l_error_msg varchar2(2000);
420 l_login_msg varchar2(2000);
421 l_ip_address varchar2(50);
422 l_url varchar2(2000);
423
424 begin
425
426 icx_on_utilities.unpack_parameters(icx_call.decrypt2(S),l_parameters);
427
428 l_user_id := l_parameters(1);
429 l_responsibility_id := l_parameters(2);
430 l_function_id := l_parameters(3);
431
432 l_ip_address := owa_util.get_cgi_env('REMOTE_ADDR');
433
434 l_session_id := 1234;
435
436 owa_util.mime_header('text/html', FALSE);
437
438 sendsessioncookie(l_session_id);
439
440 owa_util.http_header_close;
441
442 l_url := 'OracleApps.RF?F='||icx_call.encrypt2(l_responsibility_id||'*'||l_function_id||'**]');
443
444 htp.htmlOpen;
445 -- htp.p('<body onload="open('''||l_url||''', ''_top'')">');
446
447 htp.p('Run Function would called here');
448
449 -- htp.p('</body>');
450 htp.htmlClose;
451
452 exception
453 when others then
454 fnd_message.set_name('ICX','ICX_DATA_INCORRECT');
455 l_error_msg := fnd_message.get;
456 fnd_message.set_name('ICX','ICX_CONTACT_WEBMASTER');
457 l_login_msg := fnd_message.get;
458
459 OracleApps.displayLogin(l_error_msg||' '||l_login_msg,'IC','Y');
460
461 insert into icx_failures
462 (user_name,password,failure_code,failure_date,
463 created_by, creation_date, last_updated_by,
464 last_update_date, last_update_login)
465 values
466 ('-1','-1','ICX_NAVIGATOR',sysdate,
467 '-1', sysdate, '-1', sysdate, '-1');
468 end;
469
470 -- ***********************************************
471 -- Procedure createSessionCookie
472 -- ***********************************************
473
474 procedure createSessionCookie(p_session_id in number) is
475
476 l_ip_address varchar2(80);
477 l_server_name varchar2(240);
478 l_domain varchar2(240);
479 l_session_id varchar2(150);
480
481 begin
482 sendsessioncookie(p_session_id);
483 end;
484
485
486 -- ***********************************************
487 -- Function createSessionPrivate
488 -- ***********************************************
489 function createSessionPrivate(p_user_id in number,
490 p_session_id in number,
491 p_pseudo_flag in varchar2,
492 c_mode_code in varchar2,
493 p_server_id in varchar2 default NULL)
494 return varchar2 is
495
496 l_language varchar2(80);
497 l_language_code varchar2(30);
498 l_date_format varchar2(150);
499 l_date_language varchar2(30);
500 l_numeric_characters varchar2(30);
501 l_nls_sort varchar2(30);
502 l_nls_territory varchar2(30);
503 l_limit_time number;
504 l_limit_connects number;
505 l_multi_org_flag varchar2(1);
506 l_org_id varchar2(50);
507 l_profile_defined boolean;
508 db_lang varchar2(512);
509 lang varchar2(255);
510
511 c_language varchar2(30);
512 l_login_id NUMBER;
513 l_count_resp_f NUMBER;
514 l_count_resp_o NUMBER;
515 l_server_host varchar2(256);
516 l_node_id number;
517 l_server_id varchar2(80);
518 l_expired VARCHAR2(5);
519
520 l_XSID varchar2(32);
521
522 l_timeout number;
523 l_guest varchar2(30);
524 l_dist varchar2(30);
525 l_guest_username varchar2(240);
526 l_guest_user_id number;
527
528
529 cursor c1 (lang in varchar2) is
530 select UTF8_DATE_LANGUAGE
531 from FND_LANGUAGES
532 where NLS_LANGUAGE = lang;
533
534 cursor c2 (lang in varchar2) is
535 select LOCAL_DATE_LANGUAGE
536 from FND_LANGUAGES
537 where NLS_LANGUAGE = lang;
538
539 begin
540
541
542 begin
543
544 l_server_id := p_server_id;
545
546 if l_server_id is null
547 then
548 l_server_host := owa_util.get_cgi_env('SERVER_NAME');
549
550 -- Bug 3361985
551 -- where lower(node_name) = l_server_host;
552
553 select node_id into l_node_id from fnd_nodes
554 where lower(webhost) = l_server_host;
555
556 else if l_server_host is NULL
557 then
558
559 select node_id into l_node_id from fnd_nodes
560 where lower(node_name) = l_server_host;
561
562 else
563
564 select node_id into l_node_id from fnd_nodes
565 where server_id = l_server_id;
566 end if;
567 end if;
568
569 exception
570 when no_data_found THEN
571 l_node_id := 9999;
572 end;
573
574
575 setUserNLS(p_user_id,
576 l_language,
577 l_language_code,
578 l_date_format,
579 l_date_language,
580 l_numeric_characters,
581 l_nls_sort,
582 l_nls_territory,
583 l_limit_time,
584 l_limit_connects,
585 l_org_id,
586 l_timeout);
587
588
589
590 --audit the login session
591 -- bug 2538912
592 -- If the user has only one forms resp do not audit the user
593 -- the forms session will do the auditing.
594
595 -- Bug 3238722 fnd_web_sec will now return the login_id
596 /*
597
598 select count(*) into l_count_resp_f
599 from FND_SECURITY_GROUPS_VL fsg,
600 fnd_responsibility_vl a,
601 FND_USER_RESP_GROUPS b,
602 FND_APPLICATION fa
603 where b.user_id = p_user_id
604 and b.start_date <= sysdate
605 and (b.end_date is null or b.end_date > sysdate)
606 and b.RESPONSIBILITY_id = a.responsibility_id
607 and b.RESPONSIBILITY_application_id = a.application_id
608 and a.application_id = fa.application_id
609 and a.version in ('4')
610 and a.start_date <= sysdate
611 and (a.end_date is null or a.end_date > sysdate)
612 and b.SECURITY_GROUP_ID = fsg.SECURITY_GROUP_ID;
613
614 select count(*) into l_count_resp_o
615 from FND_SECURITY_GROUPS_VL fsg,
616 fnd_responsibility_vl a,
617 FND_USER_RESP_GROUPS b,
618 FND_APPLICATION fa
619 where b.user_id = p_user_id
620 and b.start_date <= sysdate
621 and (b.end_date is null or b.end_date > sysdate)
622 and b.RESPONSIBILITY_id = a.responsibility_id
623 and b.RESPONSIBILITY_application_id = a.application_id
624 and a.application_id = fa.application_id
625 and a.version in ('W')
626 and a.start_date <= sysdate
627 and (a.end_date is null or a.end_date > sysdate)
628 and b.SECURITY_GROUP_ID = fsg.SECURITY_GROUP_ID;
629
630 if ((l_count_resp_f = 1 and l_count_resp_o = 0) = false) then
631
632 fnd_signon.new_icx_session(p_user_id,
633 l_login_id,
634 l_expired);
635
636 -- Bug 2833286 (so login_id will not be null for only one forms resp)
637 else
638 l_login_id := newLoginId;
639 end if;
640
641 */
642 -- Bug 3368816
643 begin
644
645 if g_p_loginID is null
646 then
647 fnd_signon.new_icx_session(p_user_id,
648 l_login_id,
649 l_expired);
650
651 g_p_loginID := l_login_id;
652 end if;
653
654 end;
655
656 -- l_XSID := FND_SESSION_MANAGEMENT.NewXSID;
657 l_XSID := icx_call.encrypt3(p_session_id);
658
659 -- Is user GUEST
660 -- fnd_profile.get_specific
661 -- (name_z => 'GUEST_USER_PWD',
662 -- val_z => l_guest_username ,
663 -- defined_z => l_profile_defined);
664 -- Using new api to retrieve GUEST credentials.
665 l_guest_username := fnd_web_sec.get_guest_username_pwd;
666
667 l_guest_username := SUBSTR(l_guest_username,1,INSTR(l_guest_username,'/') -1);
668
669 BEGIN
670 SELECT user_id
671 INTO l_guest_user_id
672 FROM fnd_user
673 WHERE user_name = l_guest_username;
674 EXCEPTION
675 WHEN no_data_found THEN
676 l_guest_username := -999;
677 END;
678
679 if l_guest_user_id = p_user_id
680 then
681 l_guest := 'Y';
682 else
683 l_guest := 'N';
684 end if;
685
686 fnd_profile.get_specific
687 (name_z => 'DISTRIBUTED_ENVIRONMENT',
688 val_z => l_dist,
689 defined_z => l_profile_defined);
690
691
692
693 insert into icx_sessions (
694 session_id,
695 user_id,
696 org_id,
697 security_group_id,
698 mode_code,
699 nls_language,
700 language_code,
701 pseudo_flag,
702 limit_time,
703 limit_connects,
704 counter,
705 first_connect,
706 last_connect,
707 created_by,
708 creation_date,
709 last_updated_by,
710 last_update_date,
711 last_update_login,
712 date_format_mask,
713 nls_numeric_characters,
714 nls_date_language,
715 nls_sort,
716 nls_territory,
717 disabled_flag,
718 node_id,
719 login_id,
720 MAC_KEY,
721 ENC_KEY,
722 XSID,
723 TIME_OUT,
724 GUEST,
725 DISTRIBUTED)
726 values (
727 p_session_id,
728 p_user_id,
729 l_org_id,
730 icx_sec.g_security_group_id,
731 c_mode_code,
732 l_language,
733 l_language_code,
734 p_pseudo_flag,
735 l_limit_time,
736 l_limit_connects,
737 0,
738 sysdate,
739 sysdate,
740 p_user_id,
741 sysdate,
742 p_user_id,
743 sysdate,
744 p_user_id,
745 l_date_format,
746 l_numeric_characters,
747 l_date_language,
748 l_nls_sort,
749 l_nls_territory,
750 'N',
751 l_node_id,
752 g_p_loginID,
753 fnd_crypto.RandomBytes(20),
754 fnd_crypto.RandomBytes(32),
755 l_XSID,
756 l_timeout,
757 l_guest,
758 l_dist);
759
760
761 commit;
762 -- l_login_id); --mputman added login_id per 2020952
763
764
765 return '0';
766 exception
767 when dup_val_on_index then --bug 1388903
768 return -1;
769
770 when others then
771 -- htp.p(SQLERRM);
772 htp.p(dbms_utility.format_error_stack);
773 return -1;
774 end;
775
776
777 -- ***********************************************
778 -- Function createSession
779 -- ***********************************************
780 -- added p_server_id bug 2884059
781 function createSession(p_user_id in number,
782 c_mode_code in varchar2,
783 c_sec_grp_id in NUMBER,
784 p_server_id in varchar2 DEFAULT NULL)
785 return number is
786
787 l_session_id number;
788 l_message varchar2(80);
789 l_new_session_id number;
790 l_server_id varchar2(64);
791
792
793 begin
794
795 begin
796 select server_id into l_server_id from fnd_nodes
797 where server_id = p_server_id;
798 EXCEPTION
799 WHEN no_data_found THEN
800 l_server_id := '-1';
801 end;
802 icx_sec.g_security_group_id := c_sec_grp_id; --mputman hosted update
803
804 l_session_id := NewSessionId(l_new_session_id);
805 l_message := createSessionPrivate( p_user_id => p_user_id,
806 p_server_id => l_server_id,
807 p_session_id => l_session_id,
808 p_pseudo_flag => 'N',
809 c_mode_code => nvl(c_mode_code,'115P')) ;
810 if l_message = '0' then
811
812 newSessionRaiseEvent(p_user_id,l_session_id);
813 return l_session_id;
814 else
815 return -1;
816 end if;
817
818 exception
819 when others then
820 -- htp.p(SQLERRM);
821 htp.p(dbms_utility.format_error_stack);
822 return -1;
823 end;
824
825 -- ***********************************************
826 -- Function createTransaction
827 -- ***********************************************
828
829 function createTransaction(p_session_id in number,
830 p_resp_appl_id in number,
831 p_responsibility_id in number,
832 p_security_group_id in number,
833 p_menu_id in number,
834 p_function_id in number,
835 p_function_type in varchar2,
836 p_page_id in number)
837 return number is
838
839 l_transaction_id number;
840
841 begin
842 -- select icx_transactions_s.nextval
843 -- into l_transaction_id
844 -- from sys.dual;
845 -- icx_transactions_s.nextval moved directly into insert statment for performance bug# 2494109 --mputman
846
847 insert into icx_transactions (
848 TRANSACTION_ID,
849 SESSION_ID,
850 RESPONSIBILITY_APPLICATION_ID,
851 RESPONSIBILITY_ID,
852 SECURITY_GROUP_ID,
853 MENU_ID,
854 FUNCTION_ID,
855 FUNCTION_TYPE,
856 PAGE_ID,
857 LAST_CONNECT,
858 DISABLED_FLAG,
859 CREATED_BY,
860 CREATION_DATE,
861 LAST_UPDATED_BY,
862 LAST_UPDATE_DATE)
863 values (
864 icx_transactions_s.nextval,
865 p_session_id,
866 p_resp_appl_id,
867 p_responsibility_id,
868 p_security_group_id,
869 p_menu_id,
870 p_function_id,
871 p_function_type,
872 p_page_id,
873 sysdate,
874 'N',
875 icx_sec.g_user_id,
876 sysdate,
877 icx_sec.g_user_id,
878 sysdate)
879 returning transaction_id into l_transaction_id;
880
881 return l_transaction_id;
882
883 exception
884 when others then
885 -- htp.p(SQLERRM);
886 htp.p(dbms_utility.format_error_stack);
887 return -1;
888 end;
889 -- ***********************************************
890 -- Procedure SeverLevel
891 -- ***********************************************
892 procedure ServerLevel (p_server_id in varchar2 default NULL) is
893
894 l_node_id number;
895 l_server_id varchar2(240);
896 l_server_host varchar2(256);
897 l_result boolean;
898 x_prof varchar2(240);
899
900 l_user_id number;
901 l_agent varchar2(240);
902 l_profile_defined varchar2(240);
903
904 begin
905 begin
906
907 -- l_result := icx_sec.validateSession;
908
909 l_server_id := p_server_id;
910
911 if l_server_id is null
912 then
913 l_server_host := owa_util.get_cgi_env('SERVER_NAME');
914
915 -- Bug 3361985
916 -- where lower(node_name) = (l_server_host);
917
918 select node_id into l_node_id from fnd_nodes
919 where lower(webhost) = l_server_host;
920
921 else if l_server_host is NULL
922 then
923
924 select node_id into l_node_id from fnd_nodes
925 where lower(node_name) = (l_server_host);
926
927
928 else
929
930 select node_id into l_node_id from fnd_nodes
931 where server_id = l_server_id;
932 end if;
933 end if;
934
935 exception
936 when no_data_found THEN
937 l_node_id := 9999;
938 end;
939 FND_GLOBAL.APPS_INITIALIZE(user_id => icx_sec.g_user_id,
940 resp_id => icx_sec.g_responsibility_id,
941 resp_appl_id => icx_sec.g_resp_appl_id,
942 server_id => l_node_id);
943
944
945 end;
946
947 -- ***********************************************
948 -- Procedure removeTransaction
949 -- ***********************************************
950
951 procedure removeTransaction(p_transaction_id in number) is
952
953 begin
954
955 update ICX_TRANSACTIONS
956 set DISABLED_FLAG = 'Y'
957 where TRANSACTION_ID = p_transaction_id;
958
959 exception
960 when others then
961 -- htp.p(SQLERRM);
962 htp.p(dbms_utility.format_error_stack);
963 end;
964
965 -- ***********************************************
966 -- Function NewSession
967 -- ***********************************************
968
969 function NewSession( user_info in fnd_user%rowtype,
970 c_user_name in varchar2,
971 c_password in varchar2,
972 n_session_id out NOCOPY number,
973 c_validate_only in varchar2,
974 c_mode_code in varchar2)
975 return varchar2 is
976
977 l_session_id number;
978 l_message varchar2(80);
979 l_new_session_id number;
980 l_server_id varchar2(64);
981 v_cookie owa_cookie.cookie;
982
983 begin
984
985 l_session_id := NewSessionId(l_new_session_id);
986
987 n_session_id := l_session_id;
988
989 --start bug 3154705
990 -- Only expire the cookie if it already exists
991
992 v_cookie := owa_cookie.get('WF_WORKLIST_MODE');
993 owa_util.mime_header('text/html', FALSE);
994
995 IF (v_cookie.num_vals > 0) THEN
996
997 owa_cookie.send(name=>'WF_WORKLIST_MODE', value=>'-1', expires=>'', path=>'/');-- mputman added for 1903545
998 end if;
999
1000 --end bug 3154705
1001
1002 sendsessioncookie(l_session_id); -- mputman reordered, ICX cookie must be last for FWK
1003
1004 owa_util.http_header_close;
1005
1006 l_message := createSessionPrivate( p_user_id => user_info.user_id,
1007 p_server_id => l_server_id,
1008 p_session_id => l_session_id,
1009 p_pseudo_flag => 'N',
1010 c_mode_code => c_mode_code) ;
1011
1012 if l_message = '0' then
1013 --htp.p('####NSRE####');--debug mputman
1014 newSessionRaiseEvent(user_info.user_id,l_session_id);--mputman 1513025
1015 -- htp.p(' ####post NSRE####');--debug mputman
1016 return l_session_id;
1017 else
1018 return -1;
1019 end if;
1020
1021 exception
1022 when others then
1023 -- htp.p(SQLERRM);
1024 htp.p(dbms_utility.format_error_stack);
1025 return -1;
1026 end;
1027
1028 -- ***********************************************
1029 -- function PseudoSession
1030 -- ***********************************************
1031
1032 function PseudoSession (n_session_id out NOCOPY number,
1033 IncludeHeader in boolean) return varchar2
1034 is
1035
1036 l_session_id number;
1037 c_error_msg varchar2(2000);
1038 c_login_msg varchar2(2000);
1039 c_date_format varchar2(50);
1040 l_language varchar2(80);
1041 l_language_code varchar2(30);
1042 l_new_session_id number;
1043 l_server_id varchar2(64);
1044
1045 begin
1046
1047 l_session_id := NewSessionId(l_new_session_id);
1048
1049 n_session_id := l_session_id;
1050
1051 if (IncludeHeader) then
1052 owa_util.mime_header('text/html', FALSE);
1053 end if;
1054
1055 sendsessioncookie(l_session_id);
1056
1057 if (IncludeHeader) then
1058 owa_util.http_header_close;
1059 end if;
1060
1061 return createSessionPrivate(p_user_id => -1,
1062 p_server_id => l_server_id,
1063 p_session_id => l_session_id,
1064 p_pseudo_flag => 'Y',
1065 c_mode_code => 'SLAVE') ;
1066
1067 exception
1068 when others then
1069 fnd_message.set_name('ICX','ICX_DATA_INCORRECT');
1070 c_error_msg := fnd_message.get;
1071 fnd_message.set_name('ICX','ICX_CONTACT_WEBMASTER');
1072 c_login_msg := fnd_message.get;
1073
1074 OracleApps.displayLogin(c_error_msg||' '||c_login_msg,'IC','Y');
1075
1076 insert into icx_failures
1077 (user_name,password,failure_code,failure_date,
1078 created_by, creation_date, last_updated_by,
1079 last_update_date, last_update_login)
1080 values
1081 ('-1','-1','ICX_DATA_INCORRECT',sysdate,
1082 '-1', sysdate, '-1', sysdate, '-1');
1083 return '-1';
1084 end;
1085
1086
1087 -- ***********************************************
1088 -- function PseudoSession
1089 -- ***********************************************
1090
1091 function validatePlugSession(p_plug_id in number,
1092 p_session_id in number,
1093 p_update_context in varchar2)
1094 return BOOLEAN is
1095
1096 l_session_id number;
1097 l_resp_appl_id number;
1098 l_security_group_id number;
1099 l_responsibility_id number;
1100 l_menu_id number;
1101 l_entry_sequence number;
1102 l_function_id number;
1103 l_org_id number;
1104 l_multi_org_flag varchar2(1);
1105 l_profile_defined boolean;
1106 l_validate boolean;
1107 l_wf_cookie owa_cookie.cookie;
1108
1109 begin
1110
1111 if p_session_id is null
1112 then
1113 l_session_id := getsessioncookie;
1114 else
1115 l_session_id := p_session_id;
1116 IF icx_sec.g_query_set = -1 THEN
1117 select HOME_URL,
1118 WEBMASTER_EMAIL,
1119 QUERY_SET,
1120 MAX_ROWS,
1121 SESSION_COOKIE_DOMAIN, --mputman added 1574527
1122 SESSION_COOKIE_NAME, --mputman added 1574527
1123 WINDOW_COOKIE_NAME
1124
1125 into icx_sec.g_home_url,
1126 icx_sec.g_webmaster_email,
1127 icx_sec.g_query_set,
1128 icx_sec.g_max_rows,
1129 icx_sec.g_session_cookie_domain, --mputman added 1574527
1130 icx_sec.g_session_cookie_name, --mputman added 1574527
1131 icx_sec.g_window_cookie_name
1132 from ICX_PARAMETERS;
1133 END IF; --mputman added 1574527
1134
1135 if (icx_sec.g_session_cookie_name is null) then
1136 icx_sec.g_session_cookie_name := FND_WEB_CONFIG.DATABASE_ID;
1137 end if;
1138
1139 end if;
1140
1141 select RESPONSIBILITY_ID, MENU_ID, ENTRY_SEQUENCE
1142 into l_responsibility_id, l_menu_id, l_entry_sequence
1143 from ICX_PAGE_PLUGS
1144 where PLUG_ID = p_plug_id;
1145
1146 if l_responsibility_id = -1
1147 then
1148 select FUNCTION_ID
1149 into l_function_id
1150 from FND_FORM_FUNCTIONS
1151 where FUNCTION_ID = l_entry_sequence
1152 and FUNCTION_NAME = 'ICX_NAVIGATE_PLUG';
1153
1154 l_responsibility_id := '';
1155 l_function_id := '';
1156 else
1157 select ipe.RESPONSIBILITY_APPLICATION_ID,
1158 ipe.SECURITY_GROUP_ID,
1159 ipe.RESPONSIBILITY_ID,
1160 fff.FUNCTION_ID
1161 into l_resp_appl_id,
1162 l_security_group_id,
1163 l_responsibility_id,
1164 l_function_id
1165 from FND_FORM_FUNCTIONS fff,
1166 FND_MENU_ENTRIES fme,
1167 ICX_PAGE_PLUGS ipe
1168 where ipe.PLUG_ID = p_plug_id
1169 and fme.MENU_ID = ipe.MENU_ID
1170 and fme.ENTRY_SEQUENCE = ipe.ENTRY_SEQUENCE
1171 and fme.function_id = fff.function_id;
1172 end if;
1173
1174 l_validate := validateSessionPrivate(c_session_id => l_session_id,
1175 c_resp_appl_id => l_resp_appl_id,
1176 c_security_group_id => l_security_group_id,
1177 c_responsibility_id => l_responsibility_id,
1178 c_function_id => l_function_id,
1179 c_update => FALSE);
1180
1181 if l_validate and p_update_context = 'Y'
1182 then
1183 l_org_id := '';
1184
1185 select multi_org_flag
1186 into l_multi_org_flag
1187 from fnd_product_groups
1188 where rownum < 2;
1189
1190 if l_multi_org_flag = 'Y'
1191 then
1192 fnd_profile.get_specific(
1193 name_z => 'ORG_ID',
1194 responsibility_id_z => l_responsibility_id,
1195 application_id_z => l_resp_appl_id,
1196 val_z => l_org_id,
1197 defined_z => l_profile_defined);
1198 end if;
1199
1200 update ICX_SESSIONS
1201 set RESPONSIBILITY_APPLICATION_ID = l_resp_appl_id,
1202 RESPONSIBILITY_ID = l_responsibility_id,
1203 SECURITY_GROUP_ID = l_security_group_id,
1204 ORG_ID = l_org_id,
1205 FUNCTION_ID = l_function_id,
1206 LAST_CONNECT = sysdate,
1207 COUNTER = COUNTER +1
1208 where SESSION_ID = l_session_id;
1209
1210 commit;
1211 end if; -- p_update_context = 'Y'
1212
1213 return l_validate;
1214 end;
1215
1216
1217 -- ***********************************************
1218 -- function setSessionPublic
1219 -- ***********************************************
1220
1221
1222 function setSessionPublic(p_ticket in varchar2) return BOOLEAN is
1223
1224 l_success boolean := FALSE;
1225
1226 begin
1227
1228 icx_sec.g_session_id := to_number(icx_call.decrypt3(p_ticket));
1229
1230 setSessionPrivate(p_session_id => icx_sec.g_session_id,
1231 p_success => l_success);
1232
1233 if (l_success)
1234 then
1235 return TRUE;
1236 else
1237 return FALSE;
1238 end if;
1239
1240 exception
1241 when others then
1242 -- htp.p(SQLERRM);
1243 htp.p(dbms_utility.format_error_stack);
1244 return FALSE;
1245 end;
1246
1247 -- ***********************************************
1248 -- procedure setSessionPrivate
1249 -- ***********************************************
1250
1251 procedure setSessionPrivate( p_session_id in number,
1252 p_success out NOCOPY boolean )
1253 is
1254
1255 begin
1256
1257 select NLS_LANGUAGE,
1258 LANGUAGE_CODE, DATE_FORMAT_MASK,
1259 NLS_NUMERIC_CHARACTERS, NLS_DATE_LANGUAGE,
1260 NLS_SORT, NLS_TERRITORY,
1261 USER_ID,
1262 RESPONSIBILITY_APPLICATION_ID,
1263 SECURITY_GROUP_ID,
1264 RESPONSIBILITY_ID
1265 into
1266 icx_sec.g_language,
1267 icx_sec.g_language_code, icx_sec.g_date_format,
1268 icx_sec.g_numeric_characters, icx_sec.g_date_language,
1269 icx_sec.g_nls_sort, icx_sec.g_nls_territory,
1270 icx_sec.g_user_id,
1271 icx_sec.g_resp_appl_id,
1272 icx_sec.g_security_group_id,
1273 icx_sec.g_responsibility_id
1274 from ICX_SESSIONS
1275 where SESSION_ID = p_session_id;
1276
1277 setSessionPrivate(icx_sec.g_user_id,
1278 icx_sec.g_responsibility_id,
1279 icx_sec.g_resp_appl_id,
1280 icx_sec.g_security_group_id,
1281 icx_sec.g_date_format,
1282 icx_sec.g_language,
1283 icx_sec.g_date_language,
1284 icx_sec.g_numeric_characters,
1285 icx_sec.g_nls_sort,
1286 icx_sec.g_nls_territory);
1287 exception
1288 when others
1289 then
1290 -- htp.p(SQLERRM);
1291 htp.p(dbms_utility.format_error_stack);
1292 p_success := FALSE;
1293 end;
1294
1295
1296 procedure setSessionPrivate( p_user_id in number,
1297 p_responsibility_id in number,
1298 p_resp_appl_id in number,
1299 p_security_group_id in number,
1300 p_date_format in varchar2,
1301 p_language in varchar2,
1302 p_date_language in varchar2,
1303 p_numeric_characters in varchar2,
1304 p_nls_sort in varchar2,
1305 p_nls_territory in varchar2) is
1306
1307 c_date_format varchar2(240);
1308 c_nls_language varchar2(240);
1309 c_date_language varchar2(240);
1310 c_numeric_characters varchar2(240);
1311 c_nls_sort varchar2(240);
1312 c_nls_territory varchar2(240);
1313 x_session NUMBER;
1314 c_node_id number;
1315
1316 /*
1317 cursor nls is
1318 select parameter, value
1319 from v$nls_parameters
1320 where parameter in ('NLS_LANGUAGE','NLS_DATE_LANGUAGE','NLS_SORT',
1321 'NLS_TERRITORY','NLS_DATE_FORMAT',
1322 'NLS_NUMERIC_CHARACTERS')
1323 order by parameter;
1324 */
1325
1326 begin
1327
1328 select node_id into c_node_id from icx_sessions
1329 where user_id = p_user_id and session_id = g_session_id;
1330
1331 -- changed call to fnd_global.initialize to pass login_id 2020952
1332 -- Bug 2864081
1333 -- changed call to fnd_global.initialize to pass server_id
1334 fnd_global.INITIALIZE(session_id => x_session,
1335 user_id => p_user_id,
1336 resp_id => p_responsibility_id,
1337 resp_appl_id => p_resp_appl_id,
1338 security_group_id => p_security_group_id,
1339 site_id => -1,
1340 login_id => icx_sec.g_login_id,
1341 conc_login_id => -1,
1342 prog_appl_id => icx_sec.g_prog_appl_id,
1343 conc_program_id => -1,
1344 conc_request_id => -1,
1345 server_id => c_node_id,
1346 conc_priority_request => -1);
1347 --g_prog_appl_id defaults to -1... if -999 fnd_global will verify user_id - resp_id relationship
1348
1349
1350
1351 /*
1352 fnd_global.apps_initialize(user_id => p_user_id,
1353 resp_id => p_responsibility_id,
1354 resp_appl_id => p_resp_appl_id,
1355 security_group_id => p_security_group_id);
1356 */
1357
1358 -- *******************************************
1359 -- Here, we need to alter the DATABASE session
1360 -- We want the database to return data in the
1361 -- appropriate language for the user
1362 -- *******************************************
1363 /* removed by mputman for 1574527
1364 c_date_format := ''''||p_date_format||'''';
1365 c_date_language := ''''||p_date_language||'''';
1366 c_numeric_characters := ''''||p_numeric_characters||'''';
1367 c_nls_sort := ''''||p_nls_sort||'''';
1368 c_nls_territory := ''''||p_nls_territory||'''';
1369 */
1370 -- for n in nls loop -- mputman removed 1574527
1371
1372 if p_language is not null
1373 and nvl(g_language_c,'XXXXX') <> p_language
1374 then
1375 c_nls_language := p_language;
1376 --c_nls_language := ''''||p_language||'''';
1377 --dbms_session.set_nls('NLS_LANGUAGE' , c_nls_language);
1378 g_language_c:=p_language;
1379
1380 end if;
1381
1382 if p_date_language is not null
1383 and nvl(g_date_language_c,'XXXXX') <> p_date_language
1384 then
1385 c_date_language := p_date_language;
1386 --c_date_language := ''''||p_date_language||'''';
1387 --dbms_session.set_nls('NLS_DATE_LANGUAGE', c_date_language);
1388 g_date_language_c:= p_date_language;
1389
1390 end if;
1391
1392 if p_nls_sort is not null
1393 and nvl(g_nls_sort_c,'XXXXX') <> p_nls_sort
1394 then
1395 c_nls_sort := p_nls_sort;
1396 --c_nls_sort := ''''||p_nls_sort||'''';
1397 --dbms_session.set_nls('NLS_SORT', c_nls_sort);
1398 g_nls_sort_c:= p_nls_sort;
1399
1400
1401 end if;
1402
1403 if p_nls_territory is not null
1404 and nvl(g_nls_territory_c,'XXXXX') <> p_nls_territory
1405 then
1406 c_nls_territory := p_nls_territory;
1407 --c_nls_territory := ''''||p_nls_territory||'''';
1408 --dbms_session.set_nls('NLS_TERRITORY' , c_nls_territory);
1409 g_nls_territory_c := p_nls_territory;
1410
1411
1412 end if;
1413
1414
1415 if p_date_format is not null
1416 and nvl(g_date_format_c,'XXXXX') <> p_date_format
1417 then
1418 c_date_format := p_date_format;
1419 --c_date_format := ''''||p_date_format||'''';
1420 --dbms_session.set_nls('NLS_DATE_FORMAT', c_date_format);
1421 g_date_format_c := p_date_format;
1422
1423
1424 end if;
1425
1426 if p_numeric_characters IS NOT NULL
1427 and nvl(g_numeric_characters_c,'XXXXX') <> p_numeric_characters
1428 then
1429 --c_numeric_characters := p_numeric_characters;
1430 c_numeric_characters :='.,';
1431 --dbms_session.set_nls('NLS_NUMERIC_CHARACTERS', '''.,''');
1432 g_numeric_characters_c := p_numeric_characters;
1433
1434 end if;
1435
1436 FND_GLOBAL.set_nls_context(
1437 p_nls_language => c_nls_language,
1438 p_nls_date_format => c_date_format,
1439 p_nls_date_language => c_date_language,
1440 p_nls_numeric_characters => c_numeric_characters,
1441 p_nls_sort => c_nls_sort,
1442 p_nls_territory =>c_nls_territory); -- mputman changed to use FND API for performance and consist.
1443
1444
1445 /* commented out because javascript cannot handle multiradix
1446 if n.parameter = 'NLS_NUMERIC_CHARACTERS'
1447 and p_numeric_characters is not null
1448 and n.value <> p_numeric_characters
1449 then
1450 dbms_session.set_nls('NLS_NUMERIC_CHARACTERS', c_numeric_characters);
1451 end if;
1452 */
1453
1454 -- end loop; -- mputman removed for 1574527
1455
1456
1457 exception
1458 when others
1459 then
1460 -- htp.p(SQLERRM);
1461 htp.p(dbms_utility.format_error_stack);
1462
1463 end setSessionPrivate;
1464
1465 -- **************************************************
1466 -- function validateSessionPrivate
1467 -- **************************************************
1468 function validateSessionPrivate( c_session_id in number,
1469 c_function_code in varchar2,
1470 c_validate_only in varchar2,
1471 c_commit in boolean,
1472 c_update in boolean,
1473 c_responsibility_id in number,
1474 c_function_id in number,
1475 c_resp_appl_id in number,
1476 c_security_group_id in number,
1477 c_validate_mode_on in varchar2,
1478 c_transaction_id in number)
1479 return BOOLEAN
1480 is
1481 c_user_name
1482 varchar2(30);
1483 c_user_password varchar2(30);
1484 c_func_name varchar2(30);
1485 e_exceed_limit exception;
1486 e_no_function_id exception;
1487 e_session_invalid exception;
1488 n_limit_connects number;
1489 n_limit_time number;
1490 n_counter number;
1491 c_disabled_flag varchar2(1);
1492 c_pseudo_session varchar2(1);
1493 c_text varchar2(80);
1494 c_display_error varchar2(240);
1495 c_error_msg varchar2(2000);
1496 c_login_msg varchar2(2000);
1497 n_error_num number;
1498 l_string varchar2(100);
1499 d_first_connect_time date;
1500 c_org_id number;
1501 l_multi_org_flag varchar2(30);
1502 l_profile_defined boolean;
1503 l_session_mode varchar2(30);
1504 c_date_format varchar2(240);
1505 c_nls_language varchar2(240);
1506 l_prefix varchar2(30);
1507 l_OA_HTML varchar2(80);
1508 l_OA_MEDIA varchar2(80);
1509 l_style_sheet varchar2(80);
1510 l_last_connect DATE;--mputman added 1755317
1511 l_session_timeout NUMBER;--mputman added 1755317
1512 l_recreate_code VARCHAR2(600);--mputman added timeout
1513 l_url VARCHAR2(600); --mputman added for timeout
1514 new_flag VARCHAR2(1);--mputman added for timeout
1515 attempt_limit NUMBER;--mputman added for timeout
1516 l_url2 VARCHAR2(600); --mputman added for timeout
1517 l_portal_url VARCHAR2(600); --mputman added for timeout 2
1518 l_home_url VARCHAR2(600);
1519 numeric_disabled_flag NUMBER;
1520 l_server_name VARCHAR2(200); --MPUTMAN added for 2214199
1521 l_domain_count NUMBER; --MPUTMAN added for 2214199
1522 l_browser VARCHAR2(400); --MPUTMAN added for 2214199
1523 l_browser_is_IE BOOLEAN; --MPUTMAN added for 2214199
1524 l_user_id NUMBER;
1525 l_anon_name VARCHAR2(400);
1526
1527
1528 begin
1529 if c_session_id is null -- don't use nvl jsp doesn't like it
1530 then
1531 icx_sec.g_session_id := getsessioncookie;
1532 else
1533 icx_sec.g_session_id := c_session_id;
1534 end if;
1535
1536 icx_sec.g_transaction_id := c_transaction_id;
1537 -- added last_connect into the select for 1755317 mputman
1538 select NLS_LANGUAGE, LANGUAGE_CODE, DATE_FORMAT_MASK,
1539 NLS_NUMERIC_CHARACTERS, NLS_DATE_LANGUAGE,
1540 NLS_SORT, NLS_TERRITORY,
1541 LIMIT_CONNECTS, LIMIT_TIME,
1542 FIRST_CONNECT, COUNTER,
1543 nvl(DISABLED_FLAG,'N'), nvl(PSEUDO_FLAG,'N'),
1544 USER_ID,
1545 nvl(c_resp_appl_id,RESPONSIBILITY_APPLICATION_ID),
1546 nvl(c_security_group_id,SECURITY_GROUP_ID),
1547 nvl(c_responsibility_id,RESPONSIBILITY_ID),
1548 nvl(c_function_id,FUNCTION_ID),
1549 FUNCTION_TYPE,
1550 MENU_ID,
1551 PAGE_ID,
1552 ORG_ID, MODE_CODE, LAST_CONNECT,
1553 login_id --mputman added 2020952
1554 into icx_sec.g_language, icx_sec.g_language_code, icx_sec.g_date_format,
1555 icx_sec.g_numeric_characters, icx_sec.g_date_language,
1556 icx_sec.g_nls_sort,icx_sec.g_nls_territory,
1557 n_limit_connects, n_limit_time,
1558 d_first_connect_time,n_counter,
1559 c_disabled_flag, c_pseudo_session,
1560 icx_sec.g_user_id,
1561 icx_sec.g_resp_appl_id,
1562 icx_sec.g_security_group_id,
1563 icx_sec.g_responsibility_id,
1564 icx_sec.g_function_id,
1565 icx_sec.g_function_type,
1566 icx_sec.g_menu_id,
1567 icx_sec.g_page_id,
1568 c_org_id, icx_sec.g_mode_code,
1569 l_last_connect,
1570 icx_sec.g_login_id --mputman added 2020952
1571
1572 from ICX_SESSIONS
1573 where SESSION_ID = icx_sec.g_session_id;
1574
1575 if c_transaction_id is not null
1576 then
1577
1578 select TRANSACTION_ID,
1579 nvl(c_resp_appl_id,RESPONSIBILITY_APPLICATION_ID),
1580 nvl(c_responsibility_id,RESPONSIBILITY_ID),
1581 nvl(c_security_group_id,SECURITY_GROUP_ID),
1582 MENU_ID,
1583 nvl(c_function_id,FUNCTION_ID),
1584 FUNCTION_TYPE,
1585 PAGE_ID
1586 into icx_sec.g_transaction_id,
1587 icx_sec.g_resp_appl_id,
1588 icx_sec.g_responsibility_id,
1589 icx_sec.g_security_group_id,
1590 icx_sec.g_menu_id,
1591 icx_sec.g_function_id,
1592 icx_sec.g_function_type,
1593 icx_sec.g_page_id
1594 from ICX_TRANSACTIONS
1595 where TRANSACTION_ID = c_transaction_id
1596 and SESSION_ID = icx_sec.g_session_id
1597 and DISABLED_FLAG <> 'Y'; --reordered select for bug #2389169 mputman
1598
1599 end if;
1600
1601 if icx_sec.g_language_code is null
1602 then
1603 select language_code
1604 into icx_sec.g_language_code
1605 from fnd_languages
1606 where nls_language = icx_sec.g_language;
1607 end if;
1608
1609 -- **************************************************
1610 -- This section handles the multi-org implemenation
1611 -- **************************************************
1612
1613 -- htp.p('DEBUG session_id='||icx_sec.g_session_id||' user_id='||icx_sec.g_user_id||' responsibility_id='||icx_sec.g_responsibility_id||' resp_appl_id='||icx_sec.g_resp_appl_id||' security_group_id='||icx_sec.g_security_group_id);
1614
1615 -- htp.p('DEBUG function_id='||icx_sec.g_function_id);
1616
1617 -- Allow easier performance tuning
1618 /* Request to remove aalomari 16-NOV-1999
1619 DBMS_APPLICATION_INFO.SET_MODULE(
1620 module_name => icx_sec.g_function_id,
1621 action_name => 'Self Service');
1622 */
1623
1624 -- *******************************************
1625 -- Here, we need to alter the DATABASE session
1626 -- We want the database to return data in the
1627 -- appropriate language for the user
1628 -- *******************************************
1629
1630 setSessionPrivate(icx_sec.g_user_id,
1631 icx_sec.g_responsibility_id,
1632 icx_sec.g_resp_appl_id,
1633 icx_sec.g_security_group_id,
1634 icx_sec.g_date_format,
1635 icx_sec.g_language,
1636 icx_sec.g_date_language,
1637 icx_sec.g_numeric_characters,
1638 icx_sec.g_nls_sort,
1639 icx_sec.g_nls_territory);
1640
1641 /* nlbarlow 1574527
1642 fnd_profile.get(name => 'ICX_OA_HTML',
1643 val => l_OA_HTML);
1644
1645 if l_OA_HTML is not null
1646 then
1647 icx_sec.g_OA_HTML := l_OA_HTML;
1648 end if;
1649
1650 fnd_profile.get(name => 'ICX_OA_MEDIA',
1651 val => l_OA_MEDIA);
1652
1653 if l_OA_MEDIA is not null
1654 then
1655 icx_sec.g_OA_MEDIA := l_OA_MEDIA;
1656 end if;
1657
1658 fnd_profile.get(name => 'ICX_STYLE_SHEET',
1659 val => l_style_sheet);
1660
1661 if l_style_sheet is not null
1662 then
1663 icx_sec.g_style_sheet := l_style_sheet;
1664 end if;
1665 */
1666
1667 fnd_profile.get(name => 'ICX_PREFIX',
1668 val => l_prefix);
1669
1670 -- GK: Bug 1622218
1671 -- There is an extra slash before OA_HTML when l_prefix is null
1672 -- which causes jsps to fail.
1673 -- ie: http://ap814sun.us.oracle.com:7732//OA_HTML/....
1674 if (l_prefix IS NOT NULL) then
1675
1676 icx_sec.g_OA_HTML := l_prefix||'/OA_HTML';
1677 icx_sec.g_OA_MEDIA := l_prefix||'/OA_MEDIA';
1678
1679 else
1680
1681 icx_sec.g_OA_HTML := 'OA_HTML';
1682 icx_sec.g_OA_MEDIA := 'OA_MEDIA';
1683
1684 end if;
1685
1686
1687 if icx_sec.g_mode_code in ( '115J', '115P', '115X', 'SLAVE')
1688 then
1689 icx_cabo.g_base_href := FND_WEB_CONFIG.WEB_SERVER;
1690 icx_cabo.g_plsql_agent := icx_plug_utilities.getPLSQLagent;
1691 else
1692 icx_cabo.g_base_href := '';
1693 icx_cabo.g_plsql_agent := '';
1694 end if;
1695
1696 if icx_sec.g_menu_id is null then
1697 icx_cabo.g_display_menu_icon := FALSE;
1698 else
1699 icx_cabo.g_display_menu_icon := TRUE;
1700 end if;
1701
1702 if c_org_id is not null
1703 then
1704 icx_sec.g_org_id := c_org_id;
1705 fnd_client_info.set_org_context(c_org_id);
1706 else
1707 select multi_org_flag
1708 into l_multi_org_flag
1709 from fnd_product_groups
1710 where rownum < 2;
1711
1712 if l_multi_org_flag = 'Y'
1713 then
1714 /* 3219471 nlbarlow replaced get_specific
1715 fnd_profile.get_specific(
1716 name_z => 'ORG_ID',
1717 responsibility_id_z => icx_sec.g_responsibility_id,
1718 application_id_z => icx_sec.g_resp_appl_id,
1719 val_z => icx_sec.g_org_id,
1720 defined_z => l_profile_defined);
1721 */
1722 fnd_profile.get(name => 'ORG_ID',
1723 val => icx_sec.g_org_id);
1724 end if;
1725 end if;
1726
1727 if icx_sec.g_mode_code in ( 'WEBAPPS', '115J', '115P', '115X')
1728 then
1729
1730 if (c_disabled_flag = 'Y') then
1731 raise e_session_invalid;
1732 end if;
1733
1734 if c_validate_mode_on = 'Y'
1735 then
1736
1737 if (n_counter + 1) > n_limit_connects
1738 then
1739 raise e_exceed_limit;
1740 end if;
1741
1742 -- begin additions for 1755317 mputman
1743
1744 /* 3219471 nlbarlow replaced get_specific
1745 fnd_profile.get_specific(name_z => 'ICX_SESSION_TIMEOUT',
1746 application_id_z => g_resp_appl_id,
1747 responsibility_id_z => icx_sec.g_responsibility_id, user_id_z => icx_sec.g_user_id,
1748 val_z => l_session_timeout ,
1749 defined_z => l_profile_defined);
1750 */
1751 fnd_profile.get(name => 'ICX_SESSION_TIMEOUT',
1752 val => l_session_timeout);
1753
1754 IF (l_session_timeout ) IS NOT NULL AND (l_session_timeout > 0) THEN
1755 IF (((SYSDATE-l_last_connect)*24*60)> l_session_timeout ) THEN
1756 RAISE e_exceed_limit;
1757 END IF; --end additions for 1755317 mputman
1758 END IF;
1759 IF ( d_first_connect_time + n_limit_time/24 < sysdate) THEN
1760 raise e_exceed_limit;
1761 END IF;
1762
1763 end if; -- c_validate_mode_on = 'Y'
1764
1765 end if; -- icx_sec.g_mode_code = 'WEBAPPS'
1766
1767 if (c_pseudo_session = 'N')
1768 then
1769 if c_function_code is not null
1770 then
1771 if (not FND_FUNCTION.TEST(c_function_code))
1772 then
1773
1774 --IF (NOT anonFunctionTest(c_function_id)) THEN
1775
1776 raise e_no_function_id;
1777 --END IF;
1778 end if;
1779 elsif icx_sec.g_function_id is not null
1780 then
1781 if (not FND_FUNCTION.TEST_ID(icx_sec.g_function_id))
1782 then
1783
1784 --IF (NOT anonFunctionTest(icx_sec.g_function_id)) THEN
1785 --SKAUSHIK
1786 --NULL;
1787 raise e_no_function_id;
1788 -- END IF;
1789 end if;
1790 end if;
1791
1792 end if;
1793
1794 if c_update
1795 then
1796 update icx_sessions
1797 set last_connect = sysdate,
1798 counter = counter + 1
1799 where session_id = icx_sec.g_session_id;
1800
1801 if c_commit
1802 then
1803 commit;
1804 end if;
1805 end if;
1806
1807 return TRUE;
1808
1809 exception
1810 when e_session_invalid
1811 then
1812 if c_validate_only = 'N'
1813 then
1814 -- fnd_message.set_name('ICX','ICX_SESSION_FAILED');
1815 -- c_error_msg := fnd_message.get;
1816 -- fnd_message.set_name('ICX','ICX_SIGNIN_AGAIN');
1817 -- c_login_msg := fnd_message.get;
1818
1819 fnd_message.set_name('ICX','ICX_CONTACT_WEBMASTER');
1820 c_login_msg := fnd_message.get;
1821 c_error_msg := dbms_utility.format_error_stack;
1822
1823 if g_session_id is not null
1824 then
1825 update icx_sessions
1826 set disabled_flag = 'Y'
1827 where session_id = g_session_id;
1828 COMMIT; -- mputman added 1574527
1829 end if;
1830
1831 OracleApps.displayLogin(c_login_msg||' '||c_error_msg,'IC','Y');
1832 return FALSE;
1833 else
1834 fnd_message.set_name('ICX','ICX_SESSION_FAILED');
1835 c_error_msg := fnd_message.get;
1836 g_validation_error := substr(c_error_msg,1,240);
1837 return FALSE;
1838 end if;
1839
1840 when e_exceed_limit
1841 then
1842 if c_validate_only = 'N'
1843 then
1844 fnd_message.set_name('FND','FND_SESSION_ICX_EXPIRED');
1845 c_error_msg := fnd_message.get;
1846 fnd_message.set_name('ICX','ICX_SIGNIN_AGAIN');
1847 c_login_msg := fnd_message.get;
1848 IF g_user_id IS NOT NULL THEN
1849 SELECT user_name
1850 INTO c_user_name
1851 FROM fnd_user
1852 WHERE user_id=g_user_id;
1853
1854 END IF;
1855
1856 --removed portal support and moved it to oracleapps.displaylogin mputman 2053850
1857 IF (c_user_name IS NOT NULL) AND (g_session_id IS NOT NULL) THEN
1858 l_recreate_code:=icx_call.encrypt(g_session_id||'*'||c_user_name||'**]');
1859
1860 l_url := FND_WEB_CONFIG.TRAIL_SLASH(fnd_profile.value('APPS_WEB_AGENT'));
1861 l_url := l_url||'OracleApps.displayLogin?recreate='||l_recreate_code;
1862 -- this fix isnt adequately tested. Will implement in later patch after testing
1863 --begin fix for 2214199
1864 /*
1865 l_browser := owa_util.get_cgi_env('HTTP_USER_AGENT');
1866 IF (instrb(l_browser,'MSIE')>0) THEN
1867 l_browser_is_IE := TRUE;
1868 ELSE
1869 l_browser_is_IE := FALSE;
1870 END IF;
1871 IF l_browser_is_IE THEN
1872 l_server_name := owa_util.get_cgi_env('SERVER_NAME');
1873 l_domain_count := instr(l_server_name,'.',-1,2);
1874 if l_domain_count > 0
1875 then
1876 l_domain_count := instr(l_server_name,'.',1,1);
1877 l_server_name := substr(l_server_name,l_domain_count,length(l_server_name));
1878 l_domain_count := instr(l_server_name,'.',-1,3);
1879 IF l_domain_count > 0 THEN
1880 l_server_name := substr(l_server_name,l_domain_count,length(l_server_name));
1881 END IF;--SECOND domain count
1882 end if;--FIRST domain count
1883 --l_server_name now holds the domain value.
1884 END IF;--l_browser_is_IE
1885 IF ((instr(l_server_name,'.',1,1))=1) THEN
1886 l_server_name:=substr(l_server_name,2);
1887 END IF;
1888
1889 --end fix for 2214199
1890 */-- mputman
1891
1892 owa_util.mime_header('text/html', TRUE); -- added to prevent login loop 2065270 mputman
1893 htp.p('<meta http-equiv="Expires" content="-1">');-- added to prevent login loop 2065270 mputman
1894 htp.htmlOpen;
1895 --part of 2214199 .. not ready to be released.
1896 /*
1897 IF l_browser_is_IE THEN
1898 htp.p('<script>
1899 document.domain="'||l_server_name||'"
1900 </script>');
1901 END IF;
1902 */-- mputman
1903 htp.p('<script>
1904 var login_window = new Object();
1905 login_window.open = false;
1906 function icx_login_window(mode, url, name){
1907 if (mode == "WWK") {
1908 attributes = "status=yes,resizable=yes,scrollbars=yes,menubar=no,toolbar=no";
1909 login_window.win = window.open(url, "login_window", attributes);
1910 if (login_window.win != null){
1911 if (login_window.win.opener == null)
1912 login_window.win.opener = self;
1913 login_window.win.focus();
1914 }
1915 }
1916 else {
1917 top.location = url;
1918 };
1919 };');
1920
1921
1922 fnd_profile.get_specific(name_z => 'APPS_SSO',
1923 user_id_z => icx_sec.g_user_id,
1924 val_z => l_portal_url,
1925 defined_z => l_profile_defined);
1926 IF (nvl(l_portal_url,'SSWA') = 'PORTAL') OR
1927 (nvl(l_portal_url,'SSWA') = 'SSO_SDK') THEN
1928 htp.p('icx_login_window("WWW","'||l_url||'","_Login_");
1929 </script>');
1930
1931 ELSE --profile option is null
1932
1933 --Bug 3816417 changed below WWK to WWW
1934
1935 htp.p('icx_login_window("WWW","'||l_url||'","_Login_");
1936 </script>');
1937 END IF;
1938
1939 htp.htmlClose;
1940 ELSE
1941 OracleApps.displayLogin(c_error_msg||' '||c_login_msg,'IC','Y');
1942 END IF; --recreate
1943
1944 return FALSE;
1945 else
1946 fnd_message.set_name('ICX','ICX_SESSION_FAILED');
1947 c_error_msg := fnd_message.get;
1948 g_validation_error := substr(c_error_msg,1,240);
1949 return FALSE;
1950 end if;
1951
1952 when e_no_function_id
1953 then
1954 if c_validate_only = 'N' then
1955 fnd_profile.get(name => 'APPS_PORTAL',
1956 val =>l_portal_url);
1957 IF l_portal_url IS NOT NULL THEN
1958 --portal instance.. direct through SSO
1959 -- fnd_profile.get(name => 'GUEST_USER_PWD',
1960 -- val => l_anon_name);
1961 -- Using new api to retrieve GUEST credentials.
1962 l_anon_name := fnd_web_sec.get_guest_username_pwd;
1963 l_anon_name := SUBSTR(l_anon_name, 1, INSTR(l_anon_name, '/') -1);
1964 BEGIN
1965 SELECT user_id
1966 INTO l_user_id
1967 FROM fnd_user
1968 WHERE user_name = l_anon_name;
1969 EXCEPTION
1970 WHEN no_data_found THEN
1971 l_user_id := -999;
1972 END;
1973
1974 IF (l_user_id = icx_sec.g_user_id) THEN
1975 --this session needs conversion to authenticated
1976 OracleSSWA.convertSession;
1977
1978 RETURN FALSE;
1979 END IF; -- an anonymous user
1980 ELSE -- not portal
1981 -- fnd_profile.get(name => 'GUEST_USER_PWD',
1982 -- val => l_anon_name);
1983 -- Using new api to retrieve GUEST credentials.
1984 l_anon_name := fnd_web_sec.get_guest_username_pwd;
1985
1986 l_anon_name := SUBSTR(l_anon_name, 1, INSTR(l_anon_name, '/') -1);
1987
1988 BEGIN
1989 SELECT user_id
1990 INTO l_user_id
1991 FROM fnd_user
1992 WHERE user_name = l_anon_name;
1993 EXCEPTION
1994 WHEN no_data_found THEN
1995 l_user_id := -999;
1996 END;
1997
1998
1999 IF (l_user_id = icx_sec.g_user_id) THEN
2000 --this session needs conversion to authenticated
2001 OracleApps.convertSession(icx_call.encrypt(icx_sec.g_session_id||'*'||
2002 icx_sec.g_resp_appl_id||'*'||
2003 icx_sec.g_responsibility_id||'*'||
2004 icx_sec.g_security_group_id||'*'||
2005 icx_sec.g_function_id||'**]'));
2006
2007 RETURN FALSE;
2008 END IF; -- an anonymous user
2009
2010 END IF; -- portal profile defined
2011 --let normal failure occur.
2012 n_error_num := SQLCODE;
2013 c_error_msg := SQLERRM;
2014 select substr(c_error_msg,12,512) into c_display_error from dual;
2015 icx_util.add_error(c_display_error);
2016 icx_admin_sig.error_screen(c_display_error);
2017
2018 -- fnd_message.set_name('ICX','ICX_DATA_INCORRECT');
2019 -- fnd_message.set_name('ICX','ICX_INVALID_FUNCTION');
2020 -- c_error_msg := fnd_message.get;
2021 -- fnd_message.set_name('ICX','ICX_CONTACT_WEBMASTER');
2022 -- c_login_msg := fnd_message.get;
2023
2024 if g_session_id is not null
2025 then
2026 update icx_sessions
2027 set disabled_flag = 'Y'
2028 where session_id = g_session_id;
2029 COMMIT; -- mputman added 1574527
2030 end if;
2031 OracleApps.displayLogin(c_error_msg||' '||c_login_msg,'IC','Y');
2032
2033 return FALSE;
2034 else
2035 fnd_message.set_name('ICX','ICX_SESSION_FAILED');
2036 c_error_msg := fnd_message.get;
2037 g_validation_error := substr(c_error_msg,1,240);
2038 return FALSE;
2039 end if;
2040
2041 when others
2042 then
2043 if c_validate_only = 'N'
2044 then
2045 fnd_message.set_name('ICX','ICX_SESSION_FAILED');
2046 c_error_msg := fnd_message.get;
2047 fnd_message.set_name('ICX','ICX_SIGNIN_AGAIN');
2048 c_login_msg := fnd_message.get;
2049
2050 if g_session_id is not null
2051 then
2052 update icx_sessions
2053 set disabled_flag = 'Y'
2054 where session_id = g_session_id;
2055 COMMIT; -- mputman added 1574527
2056 end if;
2057 OracleApps.displayLogin(c_error_msg||' '||c_login_msg,'IC','Y');
2058 return FALSE;
2059 else
2060 g_validation_error := substr(SQLERRM,1,240);
2061 return FALSE;
2062 end if;
2063 end;
2064
2065 function validateSessionPrivate( c_encrypted_session_id in varchar2,
2066 c_function_code in varchar2,
2067 c_validate_only in varchar2,
2068 c_commit in boolean,
2069 c_update in boolean,
2070 c_responsibility_id in number,
2071 c_function_id in number,
2072 c_resp_appl_id in number,
2073 c_security_group_id in number,
2074 c_validate_mode_on in varchar2,
2075 c_encrypted_transaction_id in varchar2,
2076 session_id out NOCOPY number,
2077 transaction_id out NOCOPY number,
2078 user_id out NOCOPY number,
2079 responsibility_id out NOCOPY number,
2080 resp_appl_id out NOCOPY number,
2081 security_group_id out NOCOPY number,
2082 language_code out NOCOPY varchar2,
2083 nls_language out NOCOPY varchar2,
2084 date_format_mask out NOCOPY varchar2,
2085 nls_date_language out NOCOPY varchar2,
2086 nls_numeric_characters out NOCOPY varchar2,
2087 nls_sort out NOCOPY varchar2,
2088 nls_territory out NOCOPY varchar2)
2089 return BOOLEAN is
2090
2091 l_result boolean;
2092 l_session_id number;
2093 l_transaction_id number;
2094
2095 begin
2096
2097 BEGIN --2301884
2098 l_session_id := icx_call.decrypt3(c_encrypted_session_id);
2099 EXCEPTION
2100 WHEN OTHERS THEN
2101 RETURN FALSE;
2102 END; --2301884
2103
2104 if c_encrypted_transaction_id is not null
2105 then
2106 l_transaction_id := icx_call.decrypt3(c_encrypted_transaction_id);
2107 else
2108 l_transaction_id := '';
2109 end if;
2110
2111 l_result := validateSessionPrivate(
2112 c_session_id => l_session_id,
2113 c_function_code => c_function_code,
2114 c_validate_only => c_validate_only,
2115 c_commit => c_commit,
2116 c_update => c_update,
2117 c_responsibility_id => c_responsibility_id,
2118 c_function_id => c_function_id,
2119 c_resp_appl_id => c_resp_appl_id,
2120 c_security_group_id => c_security_group_id,
2121 c_validate_mode_on => c_validate_mode_on,
2122 c_transaction_id => l_transaction_id);
2123
2124 session_id := l_session_id;
2125 transaction_id := l_transaction_id;
2126
2127 if l_result
2128 then
2129 user_id := icx_sec.g_user_id;
2130 responsibility_id := icx_sec.g_responsibility_id;
2131 resp_appl_id := icx_sec.g_resp_appl_id;
2132 security_group_id := icx_sec.g_security_group_id;
2133 language_code := icx_sec.g_language_code;
2134 nls_language := icx_sec.g_language;
2135 date_format_mask := icx_sec.g_date_format;
2136 nls_date_language := icx_sec.g_date_language;
2137 nls_numeric_characters := icx_sec.g_numeric_characters;
2138 nls_sort := icx_sec.g_nls_sort;
2139 nls_territory := icx_sec.g_nls_territory;
2140 else
2141 user_id := '';
2142 responsibility_id := '';
2143 resp_appl_id := '';
2144 security_group_id := '';
2145 language_code := '';
2146 nls_language := '';
2147 date_format_mask := '';
2148 nls_date_language := '';
2149 nls_numeric_characters := '';
2150 nls_sort := '';
2151 nls_territory := '';
2152 end if;
2153
2154 return l_result;
2155
2156 end;
2157
2158 PROCEDURE Session_tickle_PVT(p_session_id IN NUMBER)
2159 is
2160 PRAGMA AUTONOMOUS_TRANSACTION; -- mputman added for 2233089
2161
2162 begin
2163
2164 update icx_sessions
2165 set last_connect = sysdate
2166 where session_id = p_session_id;
2167 commit;
2168
2169 end;--Check Session PVT
2170
2171 PROCEDURE Session_tickle2_PVT(p_session_id IN NUMBER)
2172 is
2173
2174 begin
2175
2176 update icx_sessions
2177 set last_connect = sysdate
2178 where session_id = p_session_id;
2179 commit;
2180
2181 end;--Check Session2 PVT
2182
2183 FUNCTION CHECK_SESSION(p_session_id IN NUMBER,
2184 p_resp_id IN NUMBER,
2185 p_app_resp_id IN NUMBER) RETURN VARCHAR2
2186 is
2187
2188 e_exceed_limit exception;
2189 e_session_invalid exception;
2190 n_limit_connects number;
2191 n_limit_time number;
2192 n_counter number;
2193 c_disabled_flag varchar2(1);
2194 c_text varchar2(80);
2195 c_display_error varchar2(240);
2196 c_error_msg varchar2(2000);
2197 c_login_msg varchar2(2000);
2198 n_error_num number;
2199 l_string varchar2(100);
2200 d_first_connect_time date;
2201 l_profile_defined boolean;
2202 l_session_mode varchar2(30);
2203 l_last_connect DATE;--mputman added 1755317
2204 l_session_timeout NUMBER;--mputman added 1755317
2205 l_dist varchar2(30);
2206 l_user_id NUMBER;
2207 l_app_resp_id NUMBER;
2208 l_resp_id NUMBER;
2209
2210 begin
2211
2212
2213 -- added last_connect into the select for 1755317 mputman
2214
2215 select LIMIT_CONNECTS, LIMIT_TIME,
2216 FIRST_CONNECT, COUNTER,
2217 nvl(DISABLED_FLAG,'N'),
2218 LAST_CONNECT, user_id,
2219 nvl(p_resp_id,RESPONSIBILITY_ID),
2220 nvl(p_app_resp_id,RESPONSIBILITY_APPLICATION_ID)
2221 into n_limit_connects, n_limit_time,
2222 d_first_connect_time,n_counter,
2223 c_disabled_flag,
2224 l_last_connect, l_user_id,
2225 l_resp_id, l_app_resp_id
2226 from ICX_SESSIONS
2227 where SESSION_ID = p_session_id;
2228
2229 if ((n_counter + 1) > n_limit_connects) or
2230 (( d_first_connect_time + n_limit_time/24 < sysdate))
2231 then
2232 raise e_exceed_limit;
2233 end if;
2234
2235 if (c_disabled_flag = 'Y') then
2236 raise e_session_invalid;
2237 end if;
2238 fnd_profile.get_specific
2239 (name_z => 'ICX_SESSION_TIMEOUT',
2240 application_id_z => l_app_resp_id,
2241 user_id_z => l_user_id,
2242 responsibility_id_z => l_resp_id,
2243 val_z => l_session_timeout ,
2244 defined_z => l_profile_defined);
2245 IF (l_session_timeout ) IS NOT NULL AND (l_session_timeout > 0) THEN
2246
2247 IF (((SYSDATE-l_last_connect)*24*60)> l_session_timeout ) THEN
2248 RAISE e_exceed_limit;
2249 ELSE
2250 -- nlbarlow 2847057
2251 fnd_profile.get_specific
2252 (name_z => 'DISTRIBUTED_ENVIRONMENT',
2253 application_id_z => l_app_resp_id,
2254 user_id_z => l_user_id,
2255 responsibility_id_z => l_resp_id,
2256 val_z => l_dist,
2257 defined_z => l_profile_defined);
2258 if l_dist = 'Y'
2259 then
2260 Session_tickle2_PVT(p_session_id);
2261 else
2262 Session_tickle_PVT(p_session_id);--moved to after idle check.
2263 end if;
2264 END IF;
2265 END IF;
2266
2267 return ('VALID');
2268
2269 exception
2270 when e_session_invalid
2271 then
2272 -- fnd_message.set_name('ICX','ICX_SESSION_FAILED');
2273 return ('INVALID');
2274 when e_exceed_limit
2275 then
2276 -- fnd_message.set_name('ICX','ICX_LIMIT_EXCEEDED');
2277 return ('EXPIRED');
2278 when others
2279 then
2280 -- fnd_message.set_name('ICX','ICX_SESSION_FAILED');
2281 return ('INVALID');
2282 end;--Check Session PVT
2283
2284 -- ***********************************************
2285 -- function validateSession
2286 -- ***********************************************
2287
2288 function validateSession( c_function_code in varchar2,
2289 c_validate_only in varchar2,
2290 c_commit in boolean,
2291 c_update in boolean,
2292 c_validate_mode_on in varchar2)
2293 return BOOLEAN is
2294
2295 v_cookie_session owa_cookie.cookie;
2296 e_session_invalid exception;
2297 c_text varchar2(80);
2298 n_session_id number;
2299 c_ip_address varchar2(50);
2300 c_error_msg varchar2(2000);
2301 c_login_msg varchar2(2000);
2302
2303 begin
2304 icx_util.error_page_setup; --clean out artifact errors -- mputman 1574527
2305 -- nlbarlow 1574527
2306 if icx_sec.g_validateSession_flag
2307 then
2308
2309 --*** Get the cookie for this session.
2310 n_session_id := getsessioncookie;
2311
2312 --* if ICX cookie exists, get session id *--
2313 if (n_session_id <= 0)
2314 then
2315
2316 --* if WF cookie exists, create pseudo session *--
2317 v_cookie_session := owa_cookie.get('WF_SESSION');
2318 if (v_cookie_session.num_vals <= 0)
2319 then
2320 raise e_session_invalid;
2321 else
2322 if wf_notification.accessCheck(v_cookie_session.vals(v_cookie_session.num_vals)) is not null
2323 then
2324 c_text := PseudoSession(n_session_id);
2325 else
2326 n_session_id := -1;
2327 end if;
2328 end if;
2329 end if;
2330
2331 return (validateSessionPrivate(
2332 c_session_id => n_session_id,
2333 c_function_code => c_function_code,
2334 c_validate_only => c_validate_only,
2335 c_commit => c_commit,
2336 c_update => c_update,
2337 c_validate_mode_on => c_validate_mode_on));
2338
2339 else
2340 return true;
2341 end if;
2342
2343 exception
2344 when e_session_invalid
2345 then
2346 if c_validate_only = 'N'
2347 then
2348 fnd_message.set_name('ICX','ICX_SESSION_FAILED');
2349 c_error_msg := fnd_message.get;
2350 fnd_message.set_name('ICX','ICX_SIGNIN_AGAIN');
2351 c_login_msg := fnd_message.get;
2352
2353 if n_session_id is not null
2354 then
2355 update icx_sessions
2356 set disabled_flag = 'Y'
2357 where session_id = n_session_id;
2358 COMMIT; -- mputman added 1574527
2359 end if;
2360 OracleApps.displayLogin(c_error_msg||' '||c_login_msg,'IC','Y');
2361 return FALSE;
2362 else
2363 return FALSE;
2364 end if;
2365
2366 when others
2367 then
2368 if c_validate_only = 'N'
2369 then
2370 fnd_message.set_name('ICX','ICX_SESSION_FAILED');
2371 c_error_msg := fnd_message.get;
2372 fnd_message.set_name('ICX','ICX_SIGNIN_AGAIN');
2373 c_login_msg := fnd_message.get;
2374
2375 if n_session_id is not null
2376 then
2377 update icx_sessions
2378 set disabled_flag = 'Y'
2379 where session_id = n_session_id;
2380 COMMIT; -- mputman added 1574527
2381 end if;
2382 --Bug 3957805
2383 fnd_message.set_name('ICX','ICX_CONTACT_WEBMASTER');
2384 c_login_msg := fnd_message.get;
2385 c_error_msg := dbms_utility.format_error_stack;
2386
2387 OracleApps.displayLogin(c_login_msg||' '||c_error_msg,'IC','Y');
2388 return FALSE;
2389 else
2390 return FALSE;
2391 end if;
2392 end;
2393
2394 function disableUserSession(c_session_id in number,
2395 c_user_id in number) return BOOLEAN
2396 is
2397
2398 l_session_id number;
2399 l_user_id number;
2400
2401 begin
2402
2403 if c_session_id is null then
2404 l_session_id := getsessioncookie;
2405 else
2406 l_session_id := c_session_id;
2407 end if;
2408
2409 if c_user_id is null then
2410 update icx_sessions
2411 set disabled_flag = 'Y'
2412 where session_id = l_session_id;
2413 COMMIT; -- mputman added 1574527
2414 elsif c_user_id is not null then
2415 update icx_sessions
2416 set disabled_flag = 'Y'
2417 where session_id = l_session_id
2418 and user_id = c_user_id;
2419 COMMIT; -- mputman added 1574527
2420 end if;
2421
2422 return true;
2423 exception
2424 when others then
2425 -- htp.p(SQLERRM);
2426 htp.p(dbms_utility.format_error_stack);
2427 return false;
2428 end;
2429
2430 -- ***********************************************
2431 -- procedure RemoveCookie
2432 -- ***********************************************
2433
2434 procedure RemoveCookie is
2435
2436 n_session_id number;
2437
2438 begin
2439
2440 -- when we have corrected how WebServer.RemoveCookie works,
2441 -- we can remove the check for -1 here
2442
2443 n_session_id := getsessioncookie;
2444
2445 if (n_session_id > 0)
2446 then
2447 if n_session_id is not null
2448 then
2449 update icx_sessions
2450 set disabled_flag = 'Y'
2451 where session_id = n_session_id;
2452 COMMIT; -- mputman added 1574527
2453 end if;
2454
2455 --*********************************************--
2456 -- Disable the cookie
2457 -- Cookie is to be set within html header
2458 --*********************************************--
2459
2460 -- The correct way to remove the cookie is to
2461 -- set it to expire immediately.
2462 -- However, because of a WebServer bug,
2463 -- We are resetting it to -1 for now.
2464
2465 owa_util.mime_header('text/html', FALSE);
2466
2467 sendsessioncookie(-1);
2468
2469 owa_util.http_header_close;
2470 end if;
2471
2472 end;
2473
2474 -- ***********************************************
2475 -- procedure writeAudit
2476 -- ***********************************************
2477
2478 procedure writeAudit is
2479 c_audit_id number(15);
2480 c_server_name varchar2(80);
2481 c_server_port varchar2(80);
2482 c_script_name varchar2(80);
2483 c_path_info varchar2(80);
2484 c_message varchar2(80);
2485 n_session_id number;
2486 begin
2487 NULL;
2488 /*
2489 select icx_audit_s.nextval
2490 into c_audit_id
2491 from sys.dual;
2492
2493 n_session_id := getsessioncookie;
2494 c_message := icx_sec.validateSession(n_session_id,c_web_user_id,c_language);
2495
2496 */
2497 /*
2498 for i in 1..14 loop
2499
2500 if owa.cgi_var_name(i) = 'SERVER_NAME'
2501 then c_server_name := owa.cgi_var_val(i);
2502 end if;
2503
2504 if owa.cgi_var_name(i) = 'SERVER_PORT'
2505 then c_server_port := owa.cgi_var_val(i);
2506 end if;
2507
2508 if owa.cgi_var_name(i) = 'SCRIPT_NAME'
2509 then c_script_name := owa.cgi_var_val(i);
2510 End if;
2511
2512 if owa.cgi_var_name(i) = 'PATH_INFO'
2513 then c_path_info := owa.cgi_var_val(i);
2514 end if;
2515
2516 end loop;
2517
2518 Insert into icx_audit
2519 (audit_id,session_id,
2520 SERVER_NAME,SERVER_PORT,SCRIPT_NAME,
2521 PATH_INFO,connect_date,
2522 created_by, creation_date, last_updated_by, last_update_date)
2523 values
2524 (c_audit_id,c_session_id,c_server_name,c_server_port,c_script_name,
2525 c_path_info, sysdate,
2526 1, sysdate, 1, sysdate);
2527
2528 Make sure fill out standard WHO columns.
2529
2530
2531 */
2532 end;
2533
2534 -- ***********************************************
2535 -- procedure getSecureAttributeValues
2536 -- ***********************************************
2537
2538 procedure getSecureAttributeValues(p_return_status out NOCOPY varchar2,
2539 p_attri_code in varchar2,
2540 p_char_tbl out NOCOPY g_char_tbl_type,
2541 p_session_id in number)
2542 is
2543
2544 n_session_id number;
2545 c_error_msg varchar2(2000);
2546 c_login_msg varchar2(2000);
2547 e_exceeded_limit exception;
2548 l_count number default 0;
2549 l_user_id number;
2550
2551
2552 cursor c_attri is
2553 select varchar2_value
2554 from ak_web_user_sec_attr_values
2555 where attribute_code = upper(p_attri_code)
2556 and web_user_id = l_user_id;
2557
2558 begin
2559
2560 p_return_status := 0;
2561
2562 -- **********************************************
2563 --
2564 -- Get the cookie for this session.
2565 -- and find out other information from the db
2566 --
2567 -- **********************************************
2568
2569 if p_session_id = -1
2570 then
2571 n_session_id := getsessioncookie;
2572
2573 if (n_session_id <= 0)
2574 then
2575 raise e_exceeded_limit;
2576 end if;
2577 else
2578 n_session_id := p_session_id;
2579 end if;
2580
2581 select a.user_id
2582 into l_user_id
2583 from icx_sessions a
2584 where session_id = n_session_id;
2585
2586 for cur_att in c_attri
2587 loop
2588 l_count := l_count + 1;
2589 p_char_tbl(l_count) := cur_att.varchar2_value;
2590 end loop;
2591
2592 exception
2593 when e_exceeded_limit
2594 then
2595 fnd_message.set_name('FND','FND_SESSION_ICX_EXPIRED');
2596 c_error_msg := fnd_message.get;
2597 fnd_message.set_name('ICX','ICX_SIGNIN_AGAIN');
2598 c_login_msg := fnd_message.get;
2599 p_return_status := '-1';
2600
2601 OracleApps.displayLogin(c_error_msg||' '||c_login_msg,'IC','Y');
2602
2603 when others then
2604 fnd_message.set_name('ICX','ICX_DATA_INCORRECT');
2605 c_error_msg := fnd_message.get;
2606 fnd_message.set_name('ICX','ICX_SIGNIN_AGAIN');
2607 c_login_msg := fnd_message.get;
2608 p_return_status := '-1';
2609
2610 OracleApps.displayLogin(c_error_msg||' '||c_login_msg,'IC','Y');
2611 end;
2612
2613
2614 -- ***********************************************
2615 -- procedure getSecureAttributeValues
2616 -- ***********************************************
2617
2618 procedure getSecureAttributeValues(p_return_status out NOCOPY varchar2,
2619 p_attri_code in varchar2,
2620 p_date_tbl out NOCOPY g_date_tbl_type,
2621 p_session_id in number)
2622 is
2623
2624 n_session_id number;
2625 v_date_format varchar2(100);
2626 c_error_msg varchar2(2000);
2627 c_login_msg varchar2(2000);
2628 e_exceeded_limit exception;
2629 l_count number default 0;
2630 l_user_id number;
2631
2632
2633 cursor c_attri is
2634 select date_value
2635 from ak_web_user_sec_attr_values
2636 where attribute_code = upper(p_attri_code)
2637 and web_user_id = l_user_id;
2638
2639 begin
2640
2641 p_return_status := 0;
2642
2643 -- **********************************************
2644 --
2645 -- Get the cookie for this session.
2646 -- and find out other information from the db
2647 --
2648 -- **********************************************
2649
2650 if p_session_id = -1
2651 then
2652
2653 n_session_id := getsessioncookie;
2654
2655 if (n_session_id <= 0)
2656 then
2657 raise e_exceeded_limit;
2658 end if;
2659
2660 else
2661 n_session_id := p_session_id;
2662 end if;
2663
2664 select a.user_id
2665 into l_user_id
2666 from icx_sessions a
2667 where session_id = n_session_id;
2668
2669 for cur_att in c_attri
2670 loop
2671 l_count := l_count + 1;
2672 select date_format_mask
2673 into v_date_format
2674 from icx_sessions
2675 where session_id = n_session_id;
2676
2677 p_date_tbl(l_count) := to_char(cur_att.date_value, v_date_format);
2678
2679 end loop;
2680
2681 exception
2682 when e_exceeded_limit
2683 then
2684 fnd_message.set_name('FND','FND_SESSION_ICX_EXPIRED');
2685 c_error_msg := fnd_message.get;
2686 fnd_message.set_name('ICX','ICX_SIGNIN_AGAIN');
2687 c_login_msg := fnd_message.get;
2688 p_return_status := '-1';
2689
2690 OracleApps.displayLogin(c_error_msg||' '||c_login_msg,'IC','Y');
2691
2692 when others then
2693 fnd_message.set_name('ICX','ICX_DATA_INCORRECT');
2694 c_error_msg := fnd_message.get;
2695 fnd_message.set_name('ICX','ICX_SIGNIN_AGAIN');
2696 c_login_msg := fnd_message.get;
2697 p_return_status := '-1';
2698
2699 OracleApps.displayLogin(c_error_msg||' '||c_login_msg,'IC','Y');
2700 end;
2701
2702
2703 -- ***********************************************
2704 -- procedure getSecurAttribValues
2705 -- ***********************************************
2706
2707 procedure getSecureAttributeValues(p_return_status out NOCOPY varchar2,
2708 p_attri_code in varchar2,
2709 p_num_tbl out NOCOPY g_num_tbl_type,
2710 p_session_id in number)
2711 is
2712
2713 c_ip_address varchar2(50);
2714 n_session_id number;
2715 c_error_msg varchar2(2000);
2716 c_login_msg varchar2(2000);
2717 e_exceeded_limit exception;
2718 l_count number default 0;
2719 l_user_id number;
2720
2721
2722 cursor c_attri is
2723 select number_value
2724 from ak_web_user_sec_attr_values
2725 where attribute_code = upper(p_attri_code)
2726 and web_user_id = l_user_id;
2727
2728 begin
2729
2730 p_return_status := 0;
2731
2732 -- **********************************************
2733 --
2734 -- Get the cookie for this session.
2735 -- and find out other information from the db
2736 --
2737 -- **********************************************
2738
2739 if p_session_id = -1
2740 then
2741
2742 n_session_id := getsessioncookie;
2743
2744 if (n_session_id <= 0)
2745 then
2746 raise e_exceeded_limit;
2747 end if;
2748
2749 else
2750 n_session_id := p_session_id;
2751 end if;
2752
2753 select a.user_id
2754 into l_user_id
2755 from icx_sessions a
2756 where session_id = n_session_id;
2757
2758 for cur_att in c_attri
2759 loop
2760 l_count := l_count + 1;
2761 p_num_tbl(l_count) := to_char(cur_att.number_value);
2762
2763 end loop;
2764
2765 exception
2766 when e_exceeded_limit
2767 then
2768 fnd_message.set_name('FND','FND_SESSION_ICX_EXPIRED');
2769 c_error_msg := fnd_message.get;
2770 fnd_message.set_name('ICX','ICX_SIGNIN_AGAIN');
2771 c_login_msg := fnd_message.get;
2772 p_return_status := '-1';
2773
2774 OracleApps.displayLogin(c_error_msg||' '||c_login_msg,'IC','Y');
2775
2776 when others then
2777 fnd_message.set_name('ICX','ICX_DATA_INCORRECT');
2778 c_error_msg := fnd_message.get;
2779 fnd_message.set_name('ICX','ICX_SIGNIN_AGAIN');
2780 c_login_msg := fnd_message.get;
2781 p_return_status := '-1';
2782
2783 OracleApps.displayLogin(c_error_msg||' '||c_login_msg,'IC','Y');
2784 end;
2785
2786
2787 -- ***********************************************
2788 -- procedure getOrgInfo
2789 -- ***********************************************
2790 procedure getOrgInfo(n_param in number,
2791 n_session_id in number,
2792 n_id out NOCOPY varchar2)
2793 is
2794
2795 n_user_id number;
2796 n_customer_contact_id number;
2797 n_vendor_contact_id number;
2798 n_internal_contact_id number;
2799 c_error_msg varchar2(2000);
2800 c_login_msg varchar2(2000);
2801
2802 begin
2803
2804 select a.user_id
2805 into n_user_id
2806 from icx_sessions a
2807 where a.session_id = n_session_id;
2808
2809 if n_user_id <> 1 and n_user_id <> -1 -- ** 1 is sysadmin **
2810 then
2811
2812 select CUSTOMER_ID, SUPPLIER_ID, EMPLOYEE_ID
2813 into n_customer_contact_id, n_vendor_contact_id, n_internal_contact_id
2814 from fnd_user
2815 where user_id = n_user_id;
2816
2817 if n_param = PV_CUST_CONTACT_ID --** CUSTOMER_CONTACT_ID (7)
2818 then
2819 n_id := n_customer_contact_id;
2820
2821 elsif n_param = PV_VEND_CONTACT_ID --** VENDOR_CONTACT_ID (8)
2822 then
2823 n_id := n_vendor_contact_id;
2824
2825 elsif n_param = PV_INT_CONTACT_ID --** INTERNAL_CONTACT_ID (9)
2826 then
2827 n_id := n_internal_contact_id;
2828
2829 end if;
2830
2831 end if;
2832
2833 exception
2834 when others then
2835 fnd_message.set_name('ICX','ICX_DATA_INCORRECT');
2836 c_error_msg := fnd_message.get;
2837 fnd_message.set_name('ICX','ICX_SIGNIN_AGAIN');
2838 c_login_msg := fnd_message.get;
2839
2840 OracleApps.displayLogin(c_error_msg||' '||c_login_msg,'IC','Y');
2841 end;
2842
2843 -- ***********************************************
2844 -- procedure set_org_context
2845 -- ***********************************************
2846 procedure set_org_context(
2847 n_session_id in number,
2848 n_org_id in number)
2849 is
2850
2851 n_user_id number;
2852 n_customer_contact_id number;
2853 n_vendor_contact_id number;
2854 n_internal_contact_id number;
2855 c_error_msg varchar2(2000);
2856 c_login_msg varchar2(2000);
2857
2858 begin
2859
2860 if n_session_id is not null and n_org_id is not null
2861 then
2862 update icx_sessions
2863 set org_id = n_org_id
2864 where session_id = n_session_id;
2865
2866 fnd_client_info.set_org_context(to_char(n_org_id));
2867 end if;
2868
2869 exception
2870 when others then
2871 fnd_message.set_name('ICX','ICX_DATA_INCORRECT');
2872 c_error_msg := fnd_message.get;
2873 fnd_message.set_name('ICX','ICX_SIGNIN_AGAIN');
2874 c_login_msg := fnd_message.get;
2875
2876 OracleApps.displayLogin(c_error_msg||' '||c_login_msg,'IC','Y');
2877 end;
2878
2879 -- ***********************************************
2880 -- function getID
2881 --
2882 -- This function get the session information
2883 -- from the cookie.
2884 -- If cookie is NOT exist, the function
2885 -- returns default language_code and date_format_mask
2886 -- everything else will return NULLs.
2887 -- ***********************************************
2888
2889 function getID(n_param in number,
2890 c_logo in varchar2,
2891 p_session_id in number)
2892 return varchar2 is
2893
2894 n_user_id number;
2895 n_id varchar2(80) default NULL;
2896 n_customer_contact_id number;
2897 n_vendor_contact_id number;
2898 n_internal_contact_id number;
2899 n_responsibility_id number;
2900 n_org_id number;
2901 n_user_name varchar2(80);
2902 n_session_id number;
2903 l_session_id number; -- this is a dummy variable
2904 v_cookie_session owa_cookie.cookie;
2905 v_language_code varchar2(10);
2906 v_date_format varchar2(100);
2907 l_session_mode varchar2(30);
2908 l_profile_defined boolean;
2909 c_error_msg varchar2(2000);
2910 c_login_msg varchar2(2000);
2911 e_exceeded_limit exception;
2912
2913 begin
2914
2915 -- If session id supplied do not look at cookie
2916 if p_session_id is not null
2917 then
2918 n_session_id := p_session_id;
2919 else
2920 n_session_id := getsessioncookie;
2921 end if;
2922
2923 /* nlbarlow 1574527
2924 if (n_session_id > 0)
2925 then
2926 begin -- bug 643163, check session exists
2927 select session_id
2928 into l_session_id
2929 from icx_sessions
2930 where session_id = n_session_id;
2931 exception
2932 when others then
2933 n_session_id := -1;
2934 end;
2935 else
2936 n_session_id := -1;
2937 end if;
2938 */
2939
2940 if (n_session_id > 0)
2941 then
2942 -- *** Find out the IP address of the client ***
2943
2944 if n_param = PV_LANGUAGE_CODE --** LANGUAGE CODE (21) **
2945 then
2946 n_id := icx_sec.g_language_code; -- add to Java login.
2947
2948 elsif n_param = PV_CUST_CONTACT_ID or
2949 n_param = PV_VEND_CONTACT_ID or
2950 n_param = PV_INT_CONTACT_ID
2951 then
2952 getOrgInfo(n_param, n_session_id, n_id);
2953
2954
2955 elsif n_param = PV_RESPONSIBILITY_ID --** RESPONSIBILITY ID (25) **
2956 then
2957 n_id := icx_sec.g_responsibility_id;
2958
2959 elsif n_param = PV_FUNCTION_ID --** FUNCTION ID (31) **
2960 then
2961 n_id := icx_sec.g_function_id;
2962
2963 elsif n_param = PV_FUNCTION_TYPE --** FUNCTION TYPE (32) **
2964 then
2965 n_id := icx_sec.g_function_type;
2966
2967 elsif n_param = PV_USER_NAME --** USERNAME (99) **
2968 then
2969 select b.USER_NAME
2970 into n_id
2971 from icx_sessions a,
2972 fnd_user b
2973 where b.user_id = a.user_id
2974 and a.session_id = n_session_id;
2975
2976 elsif n_param = PV_USER_ID --** WEB USER ID (10) **
2977 then
2978 n_id := icx_sec.g_user_id;
2979
2980 elsif n_param = PV_DATE_FORMAT --** DATE FORMAT MASK (22) **
2981 then
2982 n_id := icx_sec.g_date_format;
2983
2984 elsif n_param = PV_SESSION_ID -- ** SESSION_ID (23) **
2985 then
2986 n_id := n_session_id;
2987
2988 elsif n_param = PV_ORG_ID -- ** ORG_ID (29) **
2989 then
2990 n_id := icx_sec.g_org_id;
2991
2992 elsif n_param = PV_USER_REQ_TEMPLATE -- ** REQ DEFAULT TEMPLATE (25)
2993 then
2994
2995 fnd_profile.get_specific(
2996 name_z => 'ICX_REQ_DEFAULT_TEMPLATE',
2997 application_id_z => icx_sec.g_resp_appl_id,
2998 user_id_z => icx_sec.g_user_id,
2999 responsibility_id_z => icx_sec.g_responsibility_id,
3000 val_z => n_id,
3001 defined_z => l_profile_defined);
3002
3003
3004 elsif n_param = PV_USER_REQ_OVERRIDE_REQUESTOR -- ** PV_USER_REQ_OVERRIDE_REQUESTOR (26)
3005 then
3006
3007 fnd_profile.get_specific(
3008 name_z => 'ICX_REQ_OVERRIDE_REQUESTOR_CODE',
3009 application_id_z => icx_sec.g_resp_appl_id,
3010 user_id_z => icx_sec.g_user_id,
3011 responsibility_id_z => icx_sec.g_responsibility_id,
3012 val_z => n_id,
3013 defined_z => l_profile_defined);
3014
3015 elsif n_param = PV_USER_REQ_OVERRIDE_LOC_FLAG -- ** PV_USER_REQ_OVERRIDE_LOC_FLAG (27)
3016 then
3017
3018 fnd_profile.get_specific(
3019 name_z => 'ICX_REQ_OVERRIDE_LOCATION_FLAG',
3020 application_id_z => icx_sec.g_resp_appl_id,
3021 user_id_z => icx_sec.g_user_id,
3022 responsibility_id_z => icx_sec.g_responsibility_id,
3023 val_z => n_id,
3024 defined_z => l_profile_defined);
3025
3026 elsif n_param = PV_USER_REQ_DAYS_NEEDED_BY -- ** PV_USER_REQ_DAYS_NEEDED_BY (28)
3027 then
3028
3029 fnd_profile.get_specific(
3030 name_z => 'ICX_DAYS_NEEDED_BY',
3031 application_id_z => icx_sec.g_resp_appl_id,
3032 user_id_z => icx_sec.g_user_id,
3033 responsibility_id_z => icx_sec.g_responsibility_id,
3034 val_z => n_id,
3035 defined_z => l_profile_defined);
3036
3037
3038 elsif n_param = PV_SESSION_MODE --** PV_SESSION_MODE (30) **
3039 then
3040 select mode_code
3041 into n_id
3042 from icx_sessions
3043 where session_id = n_session_id;
3044
3045 elsif n_param = 0
3046 then
3047 return(n_id); --** return NULL **
3048 end if;
3049 else
3050 -- *********************************
3051 -- if cookie does not exist.
3052 -- returns default values for only two codes.
3053 -- returns NULLs for everything else.
3054 -- *********************************
3055
3056 if n_param = PV_LANGUAGE_CODE --** LANGUAGE CODE (21) **
3057 then
3058 select LANGUAGE_CODE
3059 into n_id
3060 from FND_LANGUAGES
3061 where INSTALLED_FLAG = 'B';
3062 elsif n_param = PV_DATE_FORMAT --** DATE FORMAT MASK (22) **
3063 then
3064 /*
3065 select value
3066 into n_id
3067 from v$nls_parameters
3068 where parameter = 'NLS_DATE_FORMAT';
3069 */
3070
3071 n_id:=getNLS_PARAMETER('NLS_DATE_FORMAT'); -- replaces above select mputman 1574527
3072
3073
3074
3075 else
3076 n_id := NULL;
3077 -- *************************************************
3078 -- * if WF cookie exists, returns '-1' - HR requested this implementation
3079 -- *************************************************
3080 v_cookie_session := owa_cookie.get('WF_SESSION');
3081 if (v_cookie_session.num_vals <= 0)
3082 then
3083 n_id := NULL;
3084 else
3085 if wf_notification.accessCheck(v_cookie_session.vals(v_cookie_session.num_vals)) is not null
3086 then
3087 n_id := -1;
3088 end if;
3089 end if;
3090 end if;
3091
3092 end if;
3093
3094 return(n_id);
3095 exception
3096 when e_exceeded_limit
3097 then
3098 fnd_message.set_name('FND','FND_SESSION_ICX_EXPIRED');
3099 c_error_msg := fnd_message.get;
3100 fnd_message.set_name('ICX','ICX_SIGNIN_AGAIN');
3101 c_login_msg := fnd_message.get;
3102
3103 OracleApps.displayLogin(c_error_msg||' '||c_login_msg,'IC','Y');
3104 return '-1';
3105
3106 when others then
3107 if n_param = PV_DATE_FORMAT
3108 then
3109 return getNLS_PARAMETER('NLS_DATE_FORMAT');
3110 else
3111
3112 fnd_message.set_name('ICX','ICX_DATA_INCORRECT');
3113 c_error_msg := fnd_message.get;
3114 fnd_message.set_name('ICX','ICX_SIGNIN_AGAIN');
3115 c_login_msg := fnd_message.get;
3116
3117 OracleApps.displayLogin(c_error_msg||' '||c_login_msg,'IC','Y');
3118
3119 return '-1';
3120 end if;
3121 end;
3122
3123
3124 procedure getResponsibilityList(c_user_id in number,
3125 c_application_id in number,
3126 c_responsibility_list out NOCOPY g_responsibility_list) is
3127
3128 l_index number;
3129
3130 cursor responsibility is
3131 select a.responsibility_name
3132 from
3133 --FND_SECURITY_GROUPS_VL fsg, -- mputman per 2018060
3134 fnd_responsibility_vl a,
3135 FND_USER_RESP_GROUPS b
3136 where b.user_id = c_user_id
3137 and b.start_date <= sysdate
3138 and (b.end_date is null or b.end_date > sysdate)
3139 and b.RESPONSIBILITY_id = a.responsibility_id
3140 and b.RESPONSIBILITY_application_id = a.application_id
3141 and a.application_id = NVL(c_application_id, a.application_id)
3142 and a.version in ('W','4')
3143 and a.start_date <= sysdate
3144 and (a.end_date is null or a.end_date > sysdate)
3145 --and b.SECURITY_GROUP_ID = fsg.SECURITY_GROUP_ID -- mputman per 2018060
3146 order by responsibility_name;
3147
3148 begin
3149
3150 l_index := 1;
3151 for r in responsibility loop
3152 c_responsibility_list(l_index) := r.responsibility_name;
3153 l_index := l_index + 1;
3154 end loop;
3155
3156 end;
3157
3158
3159
3160 procedure putSessionAttributeValue(p_name in varchar2,
3161 p_value in varchar2,
3162 p_session_id in number) is
3163 l_session_id number;
3164 l_name varchar2(80);
3165 l_len number;
3166
3167 begin
3168
3169 -- 2833640 l_session_id := nvl(p_session_id,getsessioncookie);
3170 if p_session_id is null
3171 then
3172 l_session_id := getsessioncookie;
3173 else
3174 l_session_id := p_session_id;
3175 end if;
3176
3177 -- substr added for bug3282584 - MSkees
3178 -- we truncate from the right as requested by OA FWK - GKellner
3179 l_len := LENGTH( p_name );
3180 if ( l_len > 30 ) then
3181 -- substr() hass a base 1 index so use 29 to get new start
3182 l_name := substr( upper(p_name), (l_len - 29), l_len);
3183 else
3184 -- bug 3296747 forgot the else ...
3185 l_name := upper(p_name);
3186 end if;
3187
3188 delete ICX_SESSION_ATTRIBUTES
3189 where SESSION_ID = l_session_id
3190 and NAME = l_name;
3191
3192 insert into ICX_SESSION_ATTRIBUTES
3193 (SESSION_ID,NAME,VALUE)
3194 values
3195 (l_session_id,l_name,p_value);
3196
3197 end;
3198
3199 function getSessionAttributeValue(p_name in varchar2,
3200 p_session_id in number)
3201 return varchar2 is
3202 l_session_id number;
3203 l_name varchar2(80);
3204 l_value varchar2(4000);
3205 l_len number;
3206
3207 begin
3208
3209 -- 2833640 l_session_id := nvl(p_session_id,getsessioncookie);
3210 if p_session_id is null
3211 then
3212 l_session_id := getsessioncookie;
3213 else
3214 l_session_id := p_session_id;
3215 end if;
3216
3217 -- substr added for bug3282584 - MSkees
3218 -- we truncate from the right as requested by OA FWK - GKellner
3219 l_len := LENGTH( p_name );
3220 if ( l_len > 30 ) then
3221 -- substr() hass a base 1 index so use 29 to get new start
3222 l_name := substr( upper(p_name), (l_len - 29), l_len);
3223 else
3224 -- bug 3296747 forgot the else ...
3225 l_name := upper(p_name);
3226 end if;
3227
3228 select VALUE
3229 into l_value
3230 from ICX_SESSION_ATTRIBUTES
3231 where SESSION_ID = l_session_id
3232 and NAME = l_name;
3233
3234 return l_value;
3235
3236 exception
3237 when others then
3238 return NULL;
3239 end;
3240
3241 procedure clearSessionAttributeValue(p_name in varchar2,
3242 p_session_id in number) is
3243 l_session_id number;
3244 l_name varchar2(80);
3245 l_len number;
3246
3247 begin
3248
3249 -- 2833640 l_session_id := nvl(p_session_id,getsessioncookie);
3250 if p_session_id is null
3251 then
3252 l_session_id := getsessioncookie;
3253 else
3254 l_session_id := p_session_id;
3255 end if;
3256
3257 -- substr added for bug3282584 - MSkees
3258 -- we truncate from the right as requested by OA FWK - GKellner
3259 l_len := LENGTH( p_name );
3260 if ( l_len > 30 ) then
3261 -- substr() hass a base 1 index so use 29 to get new start
3262 l_name := substr( upper(p_name), (l_len - 29), l_len);
3263 else
3264 -- bug 3296747 forgot the else ...
3265 l_name := upper(p_name);
3266 end if;
3267
3268 delete ICX_SESSION_ATTRIBUTES
3269 where SESSION_ID = l_session_id
3270 and NAME = l_name;
3271
3272 end;
3273
3274 procedure sendsessioncookie (p_session_id in number) is
3275
3276 l_encrypted_session_id varchar2(240);
3277 l_server_name varchar2(240);
3278 l_domain_count number;
3279 l_domain varchar2(240);
3280 l_secure varchar2(30);
3281 c_browser varchar2(240);
3282
3283 begin
3284
3285 if p_session_id > 0
3286 then
3287 l_encrypted_session_id := icx_call.encrypt3(p_session_id);
3288 else
3289 l_encrypted_session_id := '-1';
3290 end if;
3291
3292 IF icx_sec.g_query_set = -1 THEN
3293
3294 select HOME_URL,
3295 WEBMASTER_EMAIL,
3296 QUERY_SET,
3297 MAX_ROWS,
3298 SESSION_COOKIE_DOMAIN, --mputman added 1574527
3299 SESSION_COOKIE_NAME, --mputman added 1574527
3300 WINDOW_COOKIE_NAME
3301
3302 into icx_sec.g_home_url,
3303 icx_sec.g_webmaster_email,
3304 icx_sec.g_query_set,
3305 icx_sec.g_max_rows,
3306 icx_sec.g_session_cookie_domain, --mputman added 1574527
3307 icx_sec.g_session_cookie_name, --mputman added 1574527
3308 icx_sec.g_window_cookie_name
3309 from ICX_PARAMETERS;
3310 END IF; --mputman added 1574527
3311
3312 --mputman added 1574527
3313 -- icx_sec.g_session_cookie_name := icx_sec.getsessioncookiename;
3314
3315 if (icx_sec.g_session_cookie_name is null) then
3316 icx_sec.g_session_cookie_name := FND_WEB_CONFIG.DATABASE_ID;
3317
3318 end if;
3319
3320 -- mputman added 1574527
3321 -- icx_sec.g_session_cookie_domain := icx_sec.getsessioncookiedomain;
3322
3323 --mputman modified to change default domain setting 1755300
3324 -- will remove first segment of CGI env for server
3325 -- then will test to see how many remaining segments
3326 -- if there are more than 3 remaining segment, it will substring to only 3
3327 if (icx_sec.g_session_cookie_domain is null) then
3328 l_server_name := owa_util.get_cgi_env('SERVER_NAME');
3329 l_domain_count := instr(l_server_name,'.',-1,2);
3330 if l_domain_count > 0
3331 then
3332 l_domain_count := instr(l_server_name,'.',1,1);
3333 l_server_name := substr(l_server_name,l_domain_count,length(l_server_name));
3334 --icx_sec.g_session_cookie_domain := substr(l_server_name,l_domain_count,length(l_server_name));
3335 l_domain_count := instr(l_server_name,'.',-1,3);
3336 IF l_domain_count > 0 THEN
3337 l_server_name := substr(l_server_name,l_domain_count,length(l_server_name));
3338 END IF;
3339 icx_sec.g_session_cookie_domain := l_server_name;
3340 else
3341 icx_sec.g_session_cookie_domain := '';
3342 end if;
3343 elsif icx_sec.g_session_cookie_domain = 'NULL'
3344 then
3345 icx_sec.g_session_cookie_domain := '';
3346 elsif ((substr(icx_sec.g_session_cookie_domain,1,1) <> '.') AND -- bug 1612338
3347 (icx_sec.g_session_cookie_domain <> 'NODOMAIN') AND -- mputman 22-FEB-02
3348 (icx_sec.g_session_cookie_domain <>'')) -- mputman 22-FEB-02
3349 then
3350 icx_sec.g_session_cookie_domain := '.'|| icx_sec.g_session_cookie_domain; -- bug 1612338
3351 end if;
3352
3353 --if FND_WEB_CONFIG.PROTOCOL = 'https:'
3354 --then
3355 -- l_secure := 'secure';
3356 --else
3357 -- l_secure := '';
3358 --end if;
3359
3360 -- bug 1688982
3361 c_browser := owa_util.get_cgi_env('HTTP_USER_AGENT');
3362
3363 if FND_WEB_CONFIG.PROTOCOL = 'https:'
3364 then
3365 --if (instr(c_browser, 'Mac_PowerPC') = 36 )
3366 -- Bug 2618058
3367 if (instr(c_browser, 'Mac_PowerPC') > 0)
3368 then
3369 l_secure := '';
3370 else
3371 if FND_WEB_CONFIG.PROTOCOL = 'https:'
3372 then
3373 l_secure := 'secure';
3374 else
3375 l_secure := '';
3376 end if;
3377 end if;
3378 end if;
3379 -- end of bug 1688982
3380
3381 if (icx_sec.g_session_cookie_domain = 'NODOMAIN')
3382 then
3383 owa_cookie.send(name => icx_sec.g_session_cookie_name,
3384 value => l_encrypted_session_id,
3385 expires => '',
3386 path => '/',
3387 secure => l_secure);
3388 else
3389 owa_cookie.send(name => icx_sec.g_session_cookie_name,
3390 value => l_encrypted_session_id,
3391 expires => '',
3392 path => '/',
3393 domain => icx_sec.g_session_cookie_domain,
3394 secure => l_secure);
3395 end if;
3396
3397 exception
3398 when others then
3399 -- htp.p(SQLERRM);
3400 htp.p(dbms_utility.format_error_stack);
3401 end;
3402
3403 function getsessioncookie(p_ticket in varchar2) return number is
3404
3405 l_cookie_session owa_cookie.cookie;
3406 l_session_id number;
3407
3408 begin
3409
3410 IF icx_sec.g_query_set = -1 THEN
3411 select HOME_URL,
3412 WEBMASTER_EMAIL,
3413 QUERY_SET,
3414 MAX_ROWS,
3415 SESSION_COOKIE_DOMAIN, --mputman added 1574527
3416 SESSION_COOKIE_NAME, --mputman added 1574527
3417 WINDOW_COOKIE_NAME
3418
3419 into icx_sec.g_home_url,
3420 icx_sec.g_webmaster_email,
3421 icx_sec.g_query_set,
3422 icx_sec.g_max_rows,
3423 icx_sec.g_session_cookie_domain, --mputman added 1574527
3424 icx_sec.g_session_cookie_name, --mputman added 1574527
3425 icx_sec.g_window_cookie_name
3426 from ICX_PARAMETERS;
3427 END IF; --mputman added 1574527
3428
3429 -- Bug 1491332: Moved the below 2 lines to the else section below
3430 -- icx_sec.g_session_cookie_name := icx_sec.getsessioncookiename;
3431 -- icx_sec.g_session_cookie_domain := icx_sec.getsessioncookiedomain;
3432
3433 if p_ticket is not null
3434 then
3435 l_session_id := to_number(icx_call.decrypt3(p_ticket));
3436 else
3437
3438 -- mputman added 1574527
3439 -- icx_sec.g_session_cookie_name := icx_sec.getsessioncookiename;
3440
3441 if (icx_sec.g_session_cookie_name is null) then
3442 icx_sec.g_session_cookie_name := FND_WEB_CONFIG.DATABASE_ID;
3443 end if;
3444
3445 -- mputman added 1574527, don't need domain
3446 -- icx_sec.g_session_cookie_domain := icx_sec.getsessioncookiedomain;
3447
3448 l_cookie_session := owa_cookie.get(icx_sec.g_session_cookie_name);
3449
3450 if (l_cookie_session.num_vals > 0) and (l_cookie_session.vals(l_cookie_session.num_vals) <> '-1')
3451 then
3452 l_session_id := to_number(icx_call.decrypt3(l_cookie_session.vals(l_cookie_session.num_vals)));
3453 else
3454 l_session_id := -1;
3455 end if;
3456 end if;
3457
3458 return l_session_id;
3459
3460 exception
3461 when others then
3462 -- htp.p(SQLERRM);
3463 htp.p(dbms_utility.format_error_stack);
3464 return -1;
3465 end;
3466
3467 -- ***********************************************
3468 -- function getsessioncookiename
3469 -- ***********************************************
3470
3471 function getsessioncookiename return varchar2 is
3472
3473 l_session_cookie_name varchar2(81);
3474
3475 begin
3476
3477 IF icx_sec.g_session_cookie_name IS NULL THEN
3478
3479 select SESSION_COOKIE_NAME
3480 into l_session_cookie_name
3481 from ICX_PARAMETERS;
3482 ELSE
3483 l_session_cookie_name:=icx_sec.g_session_cookie_name;
3484 END IF; -- added mputman 1574527
3485
3486 if (l_session_cookie_name is null) then
3487 l_session_cookie_name := FND_WEB_CONFIG.DATABASE_ID;
3488 end if;
3489
3490 return l_session_cookie_name;
3491
3492 exception
3493 when others then
3494 htp.p(SQLERRM);
3495 return -1;
3496 end;
3497
3498 -- ***********************************************
3499 -- function getsessioncookiedomain
3500 -- ***********************************************
3501
3502 function getsessioncookiedomain return varchar2 is
3503
3504 l_session_cookie_domain varchar2(30);
3505 l_server_name varchar2(240);
3506 l_domain_count number;
3507 /*
3508 Modified logic for default domain naming to drop the first segment
3509 of the server CGI value then substr if needed to limit domain name
3510 size to no more than 3 segments.
3511 1755300
3512 */
3513
3514 begin
3515
3516 IF icx_sec.g_session_cookie_domain IS NULL THEN
3517
3518 select SESSION_COOKIE_DOMAIN
3519 into l_session_cookie_domain
3520 from ICX_PARAMETERS;
3521 ELSE
3522 l_session_cookie_domain:=icx_sec.g_session_cookie_domain;
3523 END IF; --added mputman 1574527
3524
3525 if (l_session_cookie_domain is null) then
3526 l_server_name := owa_util.get_cgi_env('SERVER_NAME');
3527 l_domain_count := instr(l_server_name,'.',-1,2);
3528 if l_domain_count > 0
3529 then
3530 l_domain_count := instr(l_server_name,'.',1,1);
3531 l_server_name := substr(l_server_name,l_domain_count,length(l_server_name));
3532 l_domain_count := instr(l_server_name,'.',-1,3);
3533 IF l_domain_count > 0 THEN
3534 l_server_name := substr(l_server_name,l_domain_count,length(l_server_name));
3535 END IF;
3536 l_session_cookie_domain := l_server_name;
3537 else
3538 l_session_cookie_domain := '';
3539 end if;
3540 elsif l_session_cookie_domain = 'NULL'
3541 then
3542 l_session_cookie_domain := '';
3543 elsif ((substr(l_session_cookie_domain,1,1) <> '.') AND -- bug 1612338
3544 (l_session_cookie_domain <> 'NODOMAIN') AND -- mputman 22-FEB-02
3545 (l_session_cookie_domain <>'')) -- mputman 22-FEB-02
3546 then
3547 l_session_cookie_domain := '.'|| l_session_cookie_domain; -- bug 1612338
3548 end if;
3549 return l_session_cookie_domain;
3550
3551 exception
3552 when others then
3553 -- htp.p(SQLERRM);
3554 htp.p(dbms_utility.format_error_stack);
3555 return -1;
3556
3557
3558 end;
3559
3560 -- ***********************************************
3561 -- function createRFURL (AOL/J)
3562 -- ***********************************************
3563 function createRFURL( p_function_name varchar2,
3564 p_function_id number,
3565 p_application_id number,
3566 p_responsibility_id number,
3567 p_security_group_id number,
3568 p_session_id number,
3569 p_parameters varchar2)
3570 return varchar2 is
3571
3572 PRAGMA AUTONOMOUS_TRANSACTION;
3573
3574 l_RFURL varchar2(2000) := null;
3575 -- l_session_id number;
3576 l_function_id number;
3577
3578 begin
3579
3580 -- l_session_id := nvl(p_session_id, icx_sec.getID(icx_sec.pv_session_id));
3581
3582 if p_function_id is null
3583 then
3584 select FUNCTION_ID
3585 into l_function_id
3586 from FND_FORM_FUNCTIONS
3587 where FUNCTION_NAME = p_function_name;
3588 else
3589 l_function_id := p_function_id;
3590 end if;
3591
3592 /*
3593 l_RFURL := FND_WEB_CONFIG.PLSQL_AGENT||'OracleSSWA.Execute?E='||
3594 wfa_html.conv_special_url_chars(
3595 icx_call.encrypt(p_application_id||'*'||
3596 p_responsibility_id||'*'||
3597 p_security_group_id||'*'||
3598 l_function_id||'**] '));
3599
3600
3601 --mputman convert to execute effort
3602 -- l_RFURL := FND_WEB_CONFIG.PLSQL_AGENT||'OracleApps.RF?F='||
3603 -- icx_call.encrypt2(p_application_id||'*'||
3604 -- p_responsibility_id||'*'||
3605 -- p_security_group_id||'*'||
3606 -- l_function_id||'**] ',
3607 -- l_session_id);
3608
3609 if p_parameters is not null
3610 then
3611 --mputman convert to execute effort
3612 --l_RFURL := l_RFURL||'&'||'P='||icx_call.encrypt2(p_parameters,l_session_id);
3613 l_RFURL := l_RFURL||'&'||'P='||icx_call.encrypt(p_parameters);
3614 end if;
3615 */
3616
3617 -- 2758891 nlbarlow
3618 l_RFURL := icx_portlet.createExecLink(p_application_id => p_application_id,
3619 p_responsibility_id => p_responsibility_id,
3620 p_security_group_id => p_security_group_id,
3621 p_function_id => l_function_id,
3622 p_parameters => p_parameters,
3623 p_url_only => 'Y');
3624
3625
3626 commit; -- bug 1324906
3627
3628 return l_RFURL;
3629
3630 end createRFURL;
3631
3632 -- ***********************************************
3633 -- function createRFLink
3634 -- ***********************************************
3635 function createRFLink( p_text varchar2,
3636 p_application_id number,
3637 p_responsibility_id number,
3638 p_security_group_id number,
3639 p_function_id number,
3640 p_target varchar2,
3641 p_session_id number)
3642 return varchar2 is
3643
3644 PRAGMA AUTONOMOUS_TRANSACTION;
3645
3646 l_RFLink varchar2(2000) := null;
3647 l_session_id number;
3648
3649 begin
3650
3651 -- 2758891 nlbarlow
3652
3653 l_RFLink := icx_portlet.createExecLink(p_application_id => p_application_id,
3654 p_responsibility_id => p_responsibility_id,
3655 p_security_group_id => p_security_group_id,
3656 p_function_id => p_function_id,
3657 p_parameters => '',
3658 p_target => p_target,
3659 p_link_name => p_text,
3660 p_url_only => 'N');
3661
3662 /* --mputman convert to execute effort
3663 --l_session_id := icx_sec.getID(n_param => icx_sec.pv_session_id,
3664 -- p_session_id => p_session_id);
3665
3666 l_RFLink := '<A HREF=OracleSSWA.Execute?E='||wfa_html.conv_special_url_chars(
3667 icx_call.encrypt(p_application_id||'*'||
3668 p_responsibility_id||'*'||
3669 p_security_group_id||'*'||
3670 p_function_id||'**]'))||
3671 ' TARGET='''||p_target||'''>'||p_text||'</A>';
3672
3673 --mputman convert to execute effort
3674 -- l_RFLink := '<A HREF=OracleApps.RF?F='||
3675 -- icx_call.encrypt2(p_application_id||'*'||
3676 -- p_responsibility_id||'*'||
3677 -- p_security_group_id||'*'||
3678 -- p_function_id||'**]',
3679 -- l_session_id)||
3680 -- ' TARGET='''||p_target||'''>'||p_text||'</A>';
3681 */
3682 commit; -- bug 1324906
3683
3684 return l_RFlink;
3685
3686 end createRFLink;
3687
3688 -- ***********************************************
3689 -- procedure updateSessionContext (AOL/J)
3690 -- ***********************************************
3691 procedure updateSessionContext( p_function_name varchar2,
3692 p_function_id number,
3693 p_application_id number,
3694 p_responsibility_id number,
3695 p_security_group_id number,
3696 p_session_id number,
3697 p_transaction_id number)
3698 is
3699
3700 l_session_id number;
3701 l_function_id number;
3702 l_function_type varchar2(30);
3703 l_multi_org_flag varchar2(30);
3704 l_org_id number;
3705 l_profile_defined boolean;
3706
3707 begin
3708
3709 IF p_session_id IS NOT NULL THEN
3710 l_session_id := p_session_id;
3711 ELSE
3712 l_session_id := icx_sec.getID(icx_sec.pv_session_id);
3713 END IF; --2482554
3714 --l_session_id := nvl(p_session_id, icx_sec.getID(icx_sec.pv_session_id));
3715
3716 if p_function_id is null and p_function_name is not null
3717 then
3718 select FUNCTION_ID, TYPE
3719 into l_function_id, l_function_type
3720 from FND_FORM_FUNCTIONS
3721 where FUNCTION_NAME = p_function_name;
3722 elsif p_function_name is null and p_function_id is not null
3723 then
3724 select FUNCTION_ID, TYPE
3725 into l_function_id, l_function_type
3726 from FND_FORM_FUNCTIONS
3727 where FUNCTION_ID = p_function_id;
3728 else
3729 l_function_id := '';
3730 l_function_type := '';
3731 end if;
3732
3733 select multi_org_flag
3734 into l_multi_org_flag
3735 from fnd_product_groups
3736 where rownum < 2;
3737
3738 if l_multi_org_flag = 'Y'
3739 then
3740 fnd_profile.get_specific(
3741 name_z => 'ORG_ID',
3742 responsibility_id_z => p_responsibility_id,
3743 application_id_z => p_application_id,
3744 val_z => l_org_id,
3745 defined_z => l_profile_defined);
3746 end if;
3747
3748 update ICX_SESSIONS
3749 set RESPONSIBILITY_APPLICATION_ID = p_application_id,
3750 RESPONSIBILITY_ID = p_responsibility_id,
3751 SECURITY_GROUP_ID = p_security_group_id,
3752 ORG_ID = l_org_id,
3753 FUNCTION_ID = l_function_id,
3754 FUNCTION_TYPE = l_function_type
3755 where SESSION_ID = l_session_id;
3756
3757 if p_transaction_id is not null
3758 then
3759
3760 update ICX_TRANSACTIONS
3761 set RESPONSIBILITY_APPLICATION_ID = p_application_id,
3762 RESPONSIBILITY_ID = p_responsibility_id,
3763 SECURITY_GROUP_ID = p_security_group_id,
3764 FUNCTION_ID = l_function_id,
3765 FUNCTION_TYPE = l_function_type
3766 where SESSION_ID = l_session_id
3767 and TRANSACTION_ID = p_transaction_id;
3768 -- 3201309 nlbarlow reordered where
3769 end if;
3770
3771 commit;
3772
3773 end updateSessionContext;
3774
3775 -- ***********************************************
3776 -- function jumpIntoFlow
3777 -- ***********************************************
3778 function jumpIntoFlow( c_person_id in number,
3779 c_application_id in number,
3780 c_flow_code in varchar2,
3781 c_sequence in number,
3782 c_key1 in varchar2,
3783 c_key2 in varchar2,
3784 c_key3 in varchar2,
3785 c_key4 in varchar2,
3786 c_key5 in varchar2,
3787 c_key6 in varchar2,
3788 c_key7 in varchar2,
3789 c_key8 in varchar2,
3790 c_key9 in varchar2,
3791 c_key10 in varchar2)
3792 return varchar2 is
3793
3794 c_url varchar2(2000);
3795 n_session_id number default 911;
3796 vHost_name varchar2(80);
3797 vAgent_name varchar2(80);
3798 c_param varchar2(1000);
3799
3800 begin
3801
3802 c_url := 'POREQWF.OPENREQ?a1=' || icx_call.encrypt(c_key1);
3803
3804 -- c_param := 'POREQWF.OPENREQ?pFlowCode=' || c_flow_code
3805 -- || '&' || 'pKey=' || c_key1;
3806 -- c_url := c_url || c_param;
3807
3808 return c_url;
3809 end;
3810
3811 -- ***********************************************
3812 -- function jumpIntoFunction
3813 -- ***********************************************
3814 function jumpIntoFunction(p_application_id in number,
3815 p_function_code in varchar2,
3816 p_parameter1 in varchar2,
3817 p_parameter2 in varchar2,
3818 p_parameter3 in varchar2,
3819 p_parameter4 in varchar2,
3820 p_parameter5 in varchar2,
3821 p_parameter6 in varchar2,
3822 p_parameter7 in varchar2,
3823 p_parameter8 in varchar2,
3824 p_parameter9 in varchar2,
3825 p_parameter10 in varchar2,
3826 p_parameter11 in varchar2)
3827 return varchar2 is
3828
3829 l_url varchar2(2000);
3830 l_web_host_name varchar2(80);
3831 l_web_agent_name varchar2(80);
3832 l_web_html_call varchar2(80);
3833 l_web_encrypt_parameters varchar2(1);
3834
3835 begin
3836
3837 select web_host_name,web_agent_name,web_html_call,web_encrypt_parameters
3838 into l_web_host_name,l_web_agent_name,l_web_html_call,l_web_encrypt_parameters
3839 from fnd_form_functions
3840 where FUNCTION_NAME = p_function_code
3841 and WEB_SECURED = 'Y';
3842
3843 l_url := '';
3844
3845 if l_web_host_name is not null
3846 then
3847 l_url := FND_WEB_CONFIG.PROTOCOL||'//'||l_web_host_name||'/';
3848 end if;
3849
3850 if l_web_agent_name is not null
3851 then
3852 l_url := l_url||l_web_agent_name||'/';
3853 end if;
3854
3855 if l_url is null
3856 then
3857 l_url := FND_WEB_CONFIG.TRAIL_SLASH(fnd_profile.value('APPS_WEB_AGENT'));
3858 end if;
3859
3860 l_url := l_url||l_web_html_call;
3861
3862 if p_parameter1 is not null
3863 then
3864 if l_web_encrypt_parameters = 'Y'
3865 then
3866 l_url := l_url||'?p1='||icx_call.encrypt(p_parameter1);
3867 else
3868 l_url := l_url||'?p1='||p_parameter1;
3869 end if;
3870 end if;
3871
3872 if p_parameter2 is not null
3873 then
3874 if l_web_encrypt_parameters = 'Y'
3875 then
3876 l_url := l_url||'&'||'p2='||icx_call.encrypt(p_parameter2);
3877 else
3878 l_url := l_url||'&'||'p2='||p_parameter2;
3879 end if;
3880 end if;
3881
3882 if p_parameter3 is not null
3883 then
3884 if l_web_encrypt_parameters = 'Y'
3885 then
3886 l_url := l_url||'&'||'p3='||icx_call.encrypt(p_parameter3);
3887 else
3888 l_url := l_url||'&'||'p3='||p_parameter3;
3889 end if;
3890 end if;
3891
3892 if p_parameter4 is not null
3893 then
3894 if l_web_encrypt_parameters = 'Y'
3895 then
3896 l_url := l_url||'&'||'p4='||icx_call.encrypt(p_parameter4);
3897 else
3898 l_url := l_url||'&'||'p4='||p_parameter4;
3899 end if;
3900 end if;
3901
3902 if p_parameter5 is not null
3903 then
3904 if l_web_encrypt_parameters = 'Y'
3905 then
3906 l_url := l_url||'&'||'p5='||icx_call.encrypt(p_parameter5);
3907 else
3908 l_url := l_url||'&'||'p5='||p_parameter5;
3909 end if;
3910 end if;
3911
3912 if p_parameter6 is not null
3913 then
3914 if l_web_encrypt_parameters = 'Y'
3915 then
3916 l_url := l_url||'&'||'p6='||icx_call.encrypt(p_parameter6);
3917 else
3918 l_url := l_url||'&'||'p6='||p_parameter6;
3919 end if;
3920 end if;
3921
3922 if p_parameter7 is not null
3923 then
3924 if l_web_encrypt_parameters = 'Y'
3925 then
3926 l_url := l_url||'&'||'p7='||icx_call.encrypt(p_parameter7);
3927 else
3928 l_url := l_url||'&'||'p7='||p_parameter7;
3929 end if;
3930 end if;
3931
3932 if p_parameter8 is not null
3933 then
3934 if l_web_encrypt_parameters = 'Y'
3935 then
3936 l_url := l_url||'&'||'p8='||icx_call.encrypt(p_parameter8);
3937 else
3938 l_url := l_url||'&'||'p8='||p_parameter8;
3939 end if;
3940 end if;
3941
3942 if p_parameter9 is not null
3943 then
3944 if l_web_encrypt_parameters = 'Y'
3945 then
3946 l_url := l_url||'&'||'p9='||icx_call.encrypt(p_parameter9);
3947 else
3948 l_url := l_url||'&'||'p9='||p_parameter9;
3949 end if;
3950 end if;
3951
3952 if p_parameter10 is not null
3953 then
3954 if l_web_encrypt_parameters = 'Y'
3955 then
3956 l_url := l_url||'&'||'p10='||icx_call.encrypt(p_parameter10);
3957 else
3958 l_url := l_url||'&'||'p10='||p_parameter10;
3959 end if;
3960 end if;
3961
3962 if p_parameter11 is not null
3963 then
3964 if l_web_encrypt_parameters = 'Y'
3965 then
3966 l_url := l_url||'&'||'p11='||icx_call.encrypt(p_parameter11);
3967 else
3968 l_url := l_url||'&'||'p11='||p_parameter11;
3969 end if;
3970 end if;
3971
3972 return l_url;
3973
3974 exception
3975 when others then
3976 -- return SQLERRM;
3977 htp.p(dbms_utility.format_error_stack);
3978
3979 end;
3980
3981 -- added for 1574527 mputman
3982 function getNLS_PARAMETER(p_param in VARCHAR2)
3983 return varchar2 is
3984 requested_val VARCHAR2(255);
3985 BEGIN
3986
3987 select upper(value)
3988 into requested_val
3989 from v$nls_parameters
3990 where parameter = p_param;
3991
3992 RETURN requested_val;
3993
3994
3995 END;
3996
3997 --added by mputman for use by AOLJ/CRM
3998 PROCEDURE set_session_nls (p_session_id IN NUMBER,
3999 p_language IN VARCHAR2,
4000 p_date_format_mask IN VARCHAR2,
4001 p_language_code IN VARCHAR2,
4002 p_date_language IN VARCHAR2,
4003 p_numeric_characters IN VARCHAR2,
4004 p_sort IN VARCHAR2,
4005 p_territory IN VARCHAR2)
4006 IS
4007
4008
4009 BEGIN
4010
4011 UPDATE icx_sessions
4012 SET
4013
4014 NLS_LANGUAGE=p_language,
4015 DATE_FORMAT_MASK=p_date_format_mask,
4016 LANGUAGE_CODE=p_language_code,
4017 NLS_DATE_LANGUAGE=p_date_language,
4018 NLS_NUMERIC_CHARACTERS=p_numeric_characters,
4019 NLS_SORT=p_sort,
4020 NLS_TERRITORY=p_territory
4021 WHERE session_id=p_session_id;
4022 COMMIT;
4023
4024 EXCEPTION
4025 WHEN OTHERS THEN
4026 -- htp.p(SQLERRM);
4027 htp.p(dbms_utility.format_error_stack);
4028
4029 END;
4030
4031
4032
4033 FUNCTION recreate_session(i_1 IN VARCHAR2,
4034 i_2 IN VARCHAR2,
4035 p_enc_session IN VARCHAR2,
4036 p_mode IN VARCHAR2)
4037 RETURN VARCHAR2
4038 IS
4039
4040 u fnd_user%rowtype;
4041 c_server_name varchar2(240);
4042 c_server_port varchar2(80);
4043 l_server varchar2(240);
4044 c_script_name varchar2(80);
4045 l_host_instance varchar2(240);
4046 l_url varchar2(2000);
4047 l_result varchar2(30);
4048 l_app varchar2(30);
4049 l_msg_code varchar2(30);
4050 l_valid2 varchar2(240);
4051 v_user_id number;
4052 v_user_name varchar2(80);
4053 v_password varchar2(80);
4054 v_encrypted_psswd varchar2(1000);
4055 v_encrypted_upper_psswd varchar2(1000);
4056 c_error_msg varchar2(2000);
4057 c_login_msg varchar2(2000);
4058 e_signin_invalid exception;
4059 e_account_expired exception;
4060 e_invalid_password exception;
4061 e_java_password exception;
4062 l_enc_fnd_pwd varchar2(100);
4063 l_enc_user_pwd varchar2(100);
4064 l_expired varchar2(30);
4065 return_to_url varchar2(2000);
4066 l_agent varchar2(240);
4067 c_validate_only VARCHAR2(10);
4068 l_session_id NUMBER;
4069 begin
4070 if (i_1 is NULL or i_2 is NULL)
4071 then
4072 raise e_signin_invalid;
4073 end if;
4074
4075 l_result := fnd_web_sec.validate_login(upper(i_1), i_2);
4076 c_validate_only:='N';
4077
4078 if l_result = 'Y'
4079 then
4080
4081 begin
4082 select 'Y'
4083 into l_expired
4084 from FND_USER
4085 where USER_NAME = UPPER(i_1)
4086 and (PASSWORD_DATE is NULL or
4087 (PASSWORD_LIFESPAN_ACCESSES is not NULL and
4088 nvl(PASSWORD_ACCESSES_LEFT, 0) < 1) or
4089 (PASSWORD_LIFESPAN_DAYS is not NULL and
4090 SYSDATE >= PASSWORD_DATE + PASSWORD_LIFESPAN_DAYS));
4091 exception
4092 when no_data_found then
4093 l_expired := 'N';
4094 end;
4095
4096 if (l_expired = 'Y') then
4097 return_to_url:='';
4098 OracleApps.displayNewPassword(i_1, return_to_url, p_mode);
4099 return -1;
4100
4101 else
4102
4103 select *
4104 into u
4105 from fnd_user
4106 where user_name = UPPER(i_1);
4107
4108 if u.end_date is null or u.end_date > sysdate
4109 then
4110 --return the session_id after sendsession cookie
4111 l_session_id:=icx_call.decrypt3(p_enc_session);
4112 BEGIN
4113 UPDATE icx_sessions
4114 SET
4115 disabled_flag='N',
4116 last_connect=SYSDATE,
4117 counter=0,
4118 first_connect=SYSDATE
4119 WHERE
4120 session_id=l_session_id;
4121 exception
4122 when OTHERS then
4123 RETURN -1;
4124 END;
4125
4126 owa_util.mime_header('text/html', FALSE);
4127 sendsessioncookie(l_session_id);
4128 owa_util.http_header_close;
4129 --htp.p('testing????');
4130 RETURN 1;
4131
4132 else
4133 raise e_account_expired;
4134 end if; -- u.end_date is null or u.end_date > sysdate
4135 end if; -- l_expired
4136 end if; -- l_valid = '0';
4137 RAISE e_invalid_password;
4138 exception
4139 when e_java_password
4140 then
4141
4142 if c_validate_only = 'N'
4143 then
4144 fnd_message.set_name('ICX','ICX_ACCT_EXPIRED');
4145 c_error_msg := fnd_message.get;
4146 fnd_message.set_name('ICX','ICX_CONTACT_WEBMASTER');
4147 c_login_msg := fnd_message.get;
4148
4149 OracleApps.displayLogin(c_error_msg||' '||c_login_msg,'IC','Y');
4150 end if;
4151
4152 insert into icx_failures
4153 (user_name,password,failure_code,failure_date,
4154 created_by, creation_date, last_updated_by,
4155 last_update_date, last_update_login)
4156 values
4157 (i_1,-1,
4158 'ICX_ACCT_EXPIRED',sysdate,
4159 nvl(u.user_id,-1), sysdate, nvl(u.user_id,-1),
4160 sysdate, u.user_id);
4161
4162 return '-1';
4163
4164 when e_signin_invalid OR e_invalid_password
4165 then
4166 if c_validate_only = 'N'
4167 then
4168 fnd_message.set_name('ICX','ICX_SIGNIN_INVALID');
4169 c_error_msg := fnd_message.get;
4170 fnd_message.set_name('ICX','ICX_SIGNIN_AGAIN');
4171 c_login_msg := fnd_message.get;
4172
4173 OracleApps.displayLogin(c_error_msg||' '||c_login_msg,'IC','Y');
4174 end if;
4175
4176 v_encrypted_psswd := icx_call.encrypt(i_2);
4177
4178 insert into icx_failures
4179 (user_name,password,failure_code,failure_date,
4180 created_by, creation_date, last_updated_by,
4181 last_update_date, last_update_login)
4182 values
4183 (i_1,v_encrypted_psswd,'ICX_SIGNIN_INVALID',sysdate,
4184 '-1', sysdate, '-1', sysdate, '-1');
4185 return '-1';
4186
4187 when others
4188 then
4189 if c_validate_only = 'N'
4190 then
4191 fnd_message.set_name('ICX','ICX_SIGNIN_INVALID');
4192 c_error_msg := fnd_message.get;
4193 fnd_message.set_name('ICX','ICX_SIGNIN_AGAIN');
4194 c_login_msg := fnd_message.get;
4195
4196 OracleApps.displayLogin(c_error_msg||' '||c_login_msg,'IC','Y');
4197 end if;
4198 /*
4199 when others then
4200 htp.p(SQLERRM);
4201 */
4202 return '-1';
4203 END;
4204
4205
4206 -- ***********************************************
4207 -- function recreateURL
4208 -- ***********************************************
4209
4210 function recreateURL(p_session_id IN NUMBER,
4211 p_user_name in varchar2)
4212 return VARCHAR2 is
4213 l_url VARCHAR2(600);
4214 l_url2 VARCHAR2(600);
4215 l_url3 VARCHAR2(600);
4216
4217 l_mode VARCHAR2(20);
4218 l_errm VARCHAR2(2000);
4219
4220 BEGIN
4221
4222 -- commented out all portal redirect code from here and let it be handled in displayLogin
4223
4224 --get mode_code to see if a portal session
4225 -- SELECT mode_code
4226 -- INTO l_mode
4227 -- FROM icx_sessions
4228 -- WHERE session_id=p_session_id;
4229 --get home_url so we know where to send after portal logout
4230 -- SELECT home_url
4231 -- INTO l_url3
4232 -- FROM icx_parameters;
4233
4234 -- l_url3:= wfa_html.conv_special_url_chars(l_url3);
4235
4236 --if portal
4237 -- IF l_mode='115X' THEN
4238
4239 -- fnd_profile.get(name => 'APPS_PORTAL',
4240 -- val => l_url);
4241
4242 -- l_url2 := wfa_html.conv_special_url_chars(l_url);
4243 -- l_url := replace(l_url,'home','wwsec_app_priv.logout ?p_done_url='||(nvl(l_url3,l_url2)));
4244
4245 --else PHP
4246 -- ELSE
4247 l_url:=FND_WEB_CONFIG.TRAIL_SLASH(fnd_profile.value('APPS_WEB_AGENT'))||'OracleApps.displayLogin?recreate='||icx_call.encrypt(p_session_id||'*'||p_user_name||'**]');
4248
4249 -- END IF;
4250 return (l_url);
4251
4252
4253 EXCEPTION
4254 WHEN OTHERS THEN
4255 l_errm:=SQLERRM;
4256 RETURN ('ERROR - '||l_errm);
4257 end;
4258
4259 --newSessionRaiseEvent will raise the WF Business Event oracle.apps.icx.security.session.created
4260 --mputman 1513025
4261 procedure newSessionRaiseEvent (p_user_id in varchar2,
4262 p_session_id in varchar2) is
4263
4264 l_parameterList WF_PARAMETER_LIST_T;
4265
4266 begin
4267
4268 --Initialize the parameter list.
4269
4270 l_parameterList := WF_PARAMETER_LIST_T(null);
4271
4272 --Populate the first subscript with param1, then extend the varray.
4273
4274 l_parameterList(1) := wf_parameter_t('p_user_id', p_user_id);
4275
4276 l_parameterList.EXTEND;
4277
4278 --Populate the second, but do not extend (will get an ORA-30625 if you do.)
4279
4280 l_parameterList(2) := wf_parameter_t('p_session_id', p_session_id);
4281
4282 --Raise the event
4283 --htp.p('####NSRE-5####');--debug mputman
4284
4285 WF_EVENT.Raise(p_event_name=>'oracle.apps.icx.security.session.created',
4286 p_event_key=>to_char(sysdate, 'HH:MI:SS'),
4287 p_parameters=>l_parameterList);
4288 --htp.p('####NSRE-6####');--debug mputman
4289
4290 end;
4291
4292 --doNewSessionEvent is a function that can be called via an event subscription to
4293 --disable all other sessions for the user_id except the session_id
4294 --(user_id and session_id are retrieved from the p_evtMsg type).
4295 --mputman 1513025
4296 function doNewSessionEvent (p_guid in raw,
4297 p_evtMsg in out NOCOPY wf_event_t) return varchar2 is
4298 l_user_id VARCHAR2(80);
4299 l_user_name VARCHAR2(240);
4300 l_session_id VARCHAR2(80);
4301 l_except_ids VARCHAR2(4000);
4302
4303 begin
4304
4305 --Access p_user_id
4306 l_user_id := p_evtMsg.GetValueForParameter('p_user_id');
4307 --Access p_session_id
4308 l_session_id := p_evtMsg.GetValueForParameter('p_session_id');
4309
4310 BEGIN
4311 SELECT user_name
4312 INTO l_user_name
4313 FROM fnd_user
4314 WHERE user_id=l_user_id;
4315 EXCEPTION
4316 WHEN OTHERS THEN
4317 WF_CORE.CONTEXT('icx_sec', 'doNewSessionEvent',p_evtMsg.getEventName( ), p_guid);
4318 WF_EVENT.setErrorInfo(p_evtMsg, 'ERROR');
4319 return 'ERROR';
4320 END;
4321
4322 BEGIN
4323
4324 SELECT substrb(parameters,(instrb(parameters,'=',1)+1))
4325 INTO l_except_ids
4326 FROM wf_event_subscriptions
4327 WHERE guid=p_guid;
4328
4329
4330
4331 EXCEPTION
4332 WHEN no_data_found THEN
4333 WF_CORE.CONTEXT('icx_sec', 'doNewSessionEvent',p_evtMsg.getEventName( ), p_guid);
4334 WF_EVENT.setErrorInfo(p_evtMsg, 'ERROR');
4335 return 'ERROR';
4336
4337 END;
4338
4339 IF (instrb((nvl(l_except_ids,' ')),l_user_name) = 0) THEN
4340
4341
4342 BEGIN
4343 UPDATE icx_sessions
4344 SET disabled_flag='Y'
4345 WHERE user_id = l_user_id
4346 AND session_id <> l_session_id;
4347 COMMIT;
4348
4349 EXCEPTION
4350 WHEN OTHERS THEN
4351 WF_CORE.CONTEXT('icx_sec', 'doNewSessionEvent',p_evtMsg.getEventName( ), p_guid);
4352 WF_EVENT.setErrorInfo(p_evtMsg, 'ERROR');
4353
4354 return 'ERROR';
4355 END;
4356 NULL;
4357 END IF;
4358
4359
4360 return 'SUCCESS';
4361
4362 end;
4363
4364 /*
4365 -- ***********************************************
4366 -- function newLoginId
4367 -- ***********************************************
4368
4369 function newLoginId
4370 return number is
4371
4372 l_login_id number;
4373
4374 begin
4375
4376 select fnd_logins_s.nextval
4377 into l_login_id
4378 from sys.dual;
4379
4380
4381 return(l_login_id);
4382 end;
4383
4384 */
4385
4386
4387 --disableSession is to be used with high availability to
4388 --disable all sessions that are older than the threshold value (mins)
4389 -- added for 2124463
4390 PROCEDURE disableSessions (threshold IN NUMBER)
4391 IS
4392
4393 BEGIN
4394
4395 UPDATE icx_sessions
4396 SET disabled_flag='Y'
4397 WHERE
4398 (((SYSDATE-first_connect)*24*60)> threshold);
4399
4400 COMMIT;
4401 END;
4402
4403 FUNCTION anonFunctionTest(p_func_id IN VARCHAR2,
4404 p_user_id IN NUMBER)
4405
4406 RETURN BOOLEAN IS
4407
4408 --b_allowed BOOLEAN DEFAULT FALSE;
4409 n_hits NUMBER DEFAULT 0;
4410 l_anon_name VARCHAR2(400);
4411 l_anon_user_id NUMBER;
4412 x VARCHAR2(400);
4413
4414
4415 BEGIN
4416 IF p_user_id IS NULL THEN
4417
4418 -- fnd_profile.get(name => 'GUEST_USER_PWD',
4419 -- val => l_anon_name);
4420 -- Using new api to retrieve GUEST credentials.
4421 l_anon_name := fnd_web_sec.get_guest_username_pwd;
4422 l_anon_name := SUBSTR(l_anon_name, 1, INSTR(l_anon_name, '/') -1);
4423 BEGIN
4424 SELECT user_id
4425 INTO l_anon_user_id
4426 FROM fnd_user
4427 WHERE user_name = l_anon_name;
4428 EXCEPTION
4429 WHEN no_data_found THEN
4430 l_anon_user_id := -999;
4431 END;
4432 ELSE
4433 l_anon_user_id := p_user_id;
4434
4435 END IF;
4436 select count(*)
4437 INTO n_hits
4438 from FND_FORM_FUNCTIONS a,
4439 fnd_menu_entries_vl b,
4440 fnd_responsibility_vl c,
4441 fnd_user_resp_groups d,
4442 fnd_security_groups_vl e
4443 where d.user_id = l_anon_user_id
4444 AND a.function_id = p_func_id
4445 and b.function_id = a.function_id
4446 and d.responsibility_application_id = c.application_id
4447 and b.MENU_ID = c.MENU_ID
4448 and c.responsibility_id = d.responsibility_id
4449 and type in ('WWW','WWK','JSP','SERVLET', 'INTEROPJSP')
4450 and d.start_date <= sysdate
4451 and (d.end_date is null or d.end_date > sysdate)
4452 and d.SECURITY_GROUP_ID = e.SECURITY_GROUP_ID
4453 and prompt is not null
4454 and nvl(a.function_id,-1) not IN
4455 (select ACTION_ID
4456 from FND_RESP_FUNCTIONS
4457 where RESPONSIBILITY_ID = c.responsibility_id
4458 and APPLICATION_ID = d.responsibility_application_id)
4459 and nvl(SUB_MENU_ID,-1) not IN -- submenu exclusions 2029055
4460 (select ACTION_ID
4461 from FND_RESP_FUNCTIONS
4462 where RESPONSIBILITY_ID = c.responsibility_id
4463 and APPLICATION_ID = d.responsibility_application_id);
4464 IF (n_hits >0) THEN
4465 RETURN TRUE;
4466 ELSE
4467 RETURN FALSE;
4468 END IF;
4469
4470 EXCEPTION
4471 WHEN OTHERS THEN
4472 x := SQLERRM;
4473
4474 RETURN FALSE;
4475 END; -- anonFucntionTest
4476
4477 PROCEDURE setUserNLS (p_user_id IN NUMBER,
4478 l_language OUT NOCOPY varchar2,
4479 l_language_code OUT NOCOPY varchar2,
4480 l_date_format OUT NOCOPY varchar2,
4481 l_date_language OUT NOCOPY varchar2,
4482 l_numeric_characters OUT NOCOPY varchar2,
4483 l_nls_sort OUT NOCOPY varchar2,
4484 l_nls_territory OUT NOCOPY varchar2,
4485 l_limit_time OUT NOCOPY NUMBER,
4486 l_limit_connects OUT NOCOPY NUMBER,
4487 l_org_id OUT NOCOPY varchar2)
4488
4489 IS
4490
4491
4492 l_timeout number;
4493
4494
4495 begin
4496
4497 setUserNLS(p_user_id,
4498 l_language,
4499 l_language_code,
4500 l_date_format,
4501 l_date_language,
4502 l_numeric_characters,
4503 l_nls_sort,
4504 l_nls_territory,
4505 l_limit_time,
4506 l_limit_connects,
4507 l_org_id,
4508 l_timeout);
4509
4510
4511 END;--setUserNLS
4512
4513
4514
4515 PROCEDURE setUserNLS (p_user_id IN NUMBER,
4516 l_language OUT NOCOPY varchar2,
4517 l_language_code OUT NOCOPY varchar2,
4518 l_date_format OUT NOCOPY varchar2,
4519 l_date_language OUT NOCOPY varchar2,
4520 l_numeric_characters OUT NOCOPY varchar2,
4521 l_nls_sort OUT NOCOPY varchar2,
4522 l_nls_territory OUT NOCOPY varchar2,
4523 l_limit_time OUT NOCOPY NUMBER,
4524 l_limit_connects OUT NOCOPY NUMBER,
4525 l_org_id OUT NOCOPY varchar2,
4526 l_timeout OUT NOCOPY NUMBER)
4527
4528 IS
4529 -- l_language varchar2(80);
4530 -- l_language_code varchar2(30);
4531 -- l_date_format varchar2(150);
4532 -- l_date_language varchar2(30);
4533 -- l_numeric_characters varchar2(30);
4534 -- l_nls_sort varchar2(30);
4535 -- l_nls_territory varchar2(30);
4536 -- l_limit_time number;
4537 -- l_limit_connects number;
4538 -- l_multi_org_flag varchar2(1);
4539 -- l_org_id varchar2(50);
4540 l_multi_org_flag varchar2(1);
4541 l_profile_defined boolean;
4542 db_lang varchar2(512);
4543 lang varchar2(255);
4544 l_nls_characterset varchar2(50);
4545
4546 c_language varchar2(30);
4547 l_login_id NUMBER;
4548 l_expired VARCHAR2(5);
4549
4550 cursor c1 (lang in varchar2) is
4551 select UTF8_DATE_LANGUAGE
4552 from FND_LANGUAGES
4553 where NLS_LANGUAGE = lang;
4554
4555 cursor c2 (lang in varchar2) is
4556 select LOCAL_DATE_LANGUAGE
4557 from FND_LANGUAGES
4558 where NLS_LANGUAGE = lang;
4559
4560 begin
4561
4562 -- The following Profiles should be set
4563
4564 fnd_profile.get_specific(name_z => 'ICX_LANGUAGE',
4565 user_id_z => p_user_id,
4566 val_z => l_language,
4567 defined_z => l_profile_defined);
4568
4569 if l_language is null then
4570
4571 /*
4572 select upper(value)
4573 into l_language
4574 from v$nls_parameters
4575 where parameter = 'NLS_LANGUAGE';
4576 */ -- removed select 1574527 mputman
4577
4578 l_language:=getNLS_PARAMETER('NLS_LANGUAGE'); -- replaces above select mputman 1574527
4579
4580 end if;
4581
4582 select language_code
4583 into l_language_code
4584 from fnd_languages
4585 where nls_language = l_language;
4586
4587 fnd_profile.get_specific(name_z => 'ICX_NLS_SORT',
4588 user_id_z => p_user_id,
4589 val_z => l_nls_sort,
4590 defined_z => l_profile_defined);
4591
4592 if l_nls_sort is null then
4593 /*
4594 select upper(value)
4595 into l_nls_sort
4596 from v$nls_parameters
4597 where parameter = 'NLS_SORT';
4598 */
4599 l_nls_sort:=getNLS_PARAMETER('NLS_SORT'); -- replaces above select mputman 1574527
4600
4601
4602 end if;
4603
4604 fnd_profile.get_specific(name_z => 'ICX_DATE_FORMAT_MASK',
4605 user_id_z => p_user_id,
4606 val_z => l_date_format,
4607 defined_z => l_profile_defined);
4608
4609 if l_date_format is null then
4610 /*
4611 select upper(value)
4612 into l_date_format
4613 from v$nls_parameters
4614 where parameter = 'NLS_DATE_FORMAT';
4615 */
4616 l_date_format:=getNLS_PARAMETER('NLS_DATE_FORMAT'); -- replaces above select mputman 1574527
4617
4618 end if;
4619
4620 l_date_format := replace(upper(l_date_format), 'YYYY', 'RRRR');
4621 l_date_format := replace(l_date_format, 'YY', 'RRRR');
4622 if (instr(l_date_format, 'RR') > 0) then
4623 if (instr(l_date_format, 'RRRR') = 0) then
4624 l_date_format := replace(l_date_format, 'RR', 'RRRR');
4625 end if;
4626 end if;
4627
4628 /* set the NLS date language. Get it from the FND_LANGUAGES table,
4629 choosing which column based on whether the codeset is UTF8
4630 or AL32UTF8. But the profile ICX_DATE_LANGUAGE overrides
4631 all that if it is set.
4632 */
4633
4634 fnd_profile.get_specific(name_z => 'ICX_DATE_LANGUAGE',
4635 user_id_z => p_user_id,
4636 val_z => l_date_language,
4637 defined_z => l_profile_defined);
4638
4639 if l_date_language is null then
4640 l_nls_characterset := getNLS_PARAMETER('NLS_CHARACTERSET');
4641 if (l_nls_characterset in ('UTF8', 'AL32UTF8')) then
4642 open c1(l_language);
4643 fetch c1 into l_date_language;
4644 close c1;
4645 else
4646 open c2(l_language);
4647 fetch c2 into l_date_language;
4648 close c2;
4649 end if;
4650
4651 end if;
4652
4653 fnd_profile.get_specific(name_z => 'ICX_NUMERIC_CHARACTERS',
4654 user_id_z => p_user_id,
4655 val_z => l_numeric_characters,
4656 defined_z => l_profile_defined);
4657
4658 if l_numeric_characters is null then
4659 /*
4660 select upper(value)
4661 into l_numeric_characters
4662 from v$nls_parameters
4663 where parameter = 'NLS_NUMERIC_CHARACTERS';
4664 */
4665 l_numeric_characters:=getNLS_PARAMETER('NLS_NUMERIC_CHARACTERS'); -- replaces above select mputman 1574527
4666
4667 end if;
4668
4669 fnd_profile.get_specific(name_z => 'ICX_TERRITORY',
4670 user_id_z => p_user_id,
4671 val_z => l_nls_territory,
4672 defined_z => l_profile_defined);
4673
4674 if l_nls_territory is null then
4675 /*
4676 select upper(value)
4677 into l_nls_territory
4678 from v$nls_parameters
4679 where parameter = 'NLS_TERRITORY';
4680 */
4681 l_nls_territory:=getNLS_PARAMETER('NLS_TERRITORY'); -- replaces above select mputman 1574527
4682
4683
4684 end if;
4685
4686 fnd_profile.get_specific(name_z => 'ICX_LIMIT_TIME',
4687 user_id_z => p_user_id,
4688 val_z => l_limit_time,
4689 defined_z => l_profile_defined);
4690
4691 if l_limit_time is null then
4692 l_limit_time := 4;
4693 end if;
4694
4695 fnd_profile.get_specific(name_z => 'ICX_LIMIT_CONNECT',
4696 user_id_z => p_user_id,
4697 val_z => l_limit_connects,
4698 defined_z => l_profile_defined);
4699
4700 if l_limit_connects is null
4701 then
4702 l_limit_connects := 1000;
4703 end if;
4704
4705 fnd_profile.get_specific(name_z => 'ICX_SESSION_TIMEOUT',
4706 user_id_z => p_user_id,
4707 val_z => l_timeout,
4708 defined_z => l_profile_defined);
4709
4710
4711 select multi_org_flag
4712 into l_multi_org_flag
4713 from fnd_product_groups
4714 where rownum < 2;
4715
4716 if l_multi_org_flag = 'Y' then
4717 fnd_profile.get_specific(name_z => 'ORG_ID',
4718 val_z => l_org_id,
4719 defined_z => l_profile_defined);
4720 end if;
4721
4722 END;--setUserNLS
4723
4724
4725
4726 end icx_sec;