[Home] [Help]
PACKAGE BODY: APPS.ICX_SEC
Source
1 package body icx_sec as
2 /* $Header: ICXSESEB.pls 120.6.12020000.3 2013/04/05 17:05:29 vijnagar 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 --the below line was commented for performance reasons.
3322 --bcos getsessioncookiedomain also hits DB for finding icx_parameters.session_cookie_domain.
3323 --uncommenting again.
3324 icx_sec.g_session_cookie_domain := icx_sec.getsessioncookiedomain;
3325
3326 --mputman modified to change default domain setting 1755300
3327 -- will remove first segment of CGI env for server
3328 -- then will test to see how many remaining segments
3329 -- if there are more than 3 remaining segment, it will substring to only 3
3330
3331
3332 --if FND_WEB_CONFIG.PROTOCOL = 'https:'
3333 --then
3334 -- l_secure := 'secure';
3335 --else
3336 -- l_secure := '';
3337 --end if;
3338
3339 -- bug 1688982
3340 c_browser := owa_util.get_cgi_env('HTTP_USER_AGENT');
3341
3342 if FND_WEB_CONFIG.PROTOCOL = 'https:'
3343 then
3344 --if (instr(c_browser, 'Mac_PowerPC') = 36 )
3345 -- Bug 2618058
3346 if (instr(c_browser, 'Mac_PowerPC') > 0)
3347 then
3348 l_secure := '';
3349 else
3350 if FND_WEB_CONFIG.PROTOCOL = 'https:'
3351 then
3352 l_secure := 'secure';
3353 else
3354 l_secure := '';
3355 end if;
3356 end if;
3357 end if;
3358 -- end of bug 1688982
3359
3360 if (icx_sec.g_session_cookie_domain = 'NODOMAIN')
3361 then
3362 owa_cookie.send(name => icx_sec.g_session_cookie_name,
3363 value => l_encrypted_session_id,
3364 expires => '',
3365 path => '/',
3366 secure => l_secure);
3367 else
3368 owa_cookie.send(name => icx_sec.g_session_cookie_name,
3369 value => l_encrypted_session_id,
3370 expires => '',
3371 path => '/',
3372 domain => icx_sec.g_session_cookie_domain,
3373 secure => l_secure);
3374 end if;
3375
3376 exception
3377 when others then
3378 -- htp.p(SQLERRM);
3379 htp.p(dbms_utility.format_error_stack);
3380 end;
3381
3382 function getsessioncookie(p_ticket in varchar2) return number is
3383
3384 l_cookie_session owa_cookie.cookie;
3385 l_session_id number;
3386
3387 begin
3388
3389 IF icx_sec.g_query_set = -1 THEN
3390 select HOME_URL,
3391 WEBMASTER_EMAIL,
3392 QUERY_SET,
3393 MAX_ROWS,
3394 SESSION_COOKIE_DOMAIN, --mputman added 1574527
3395 SESSION_COOKIE_NAME, --mputman added 1574527
3396 WINDOW_COOKIE_NAME
3397
3398 into icx_sec.g_home_url,
3399 icx_sec.g_webmaster_email,
3400 icx_sec.g_query_set,
3401 icx_sec.g_max_rows,
3402 icx_sec.g_session_cookie_domain, --mputman added 1574527
3403 icx_sec.g_session_cookie_name, --mputman added 1574527
3404 icx_sec.g_window_cookie_name
3405 from ICX_PARAMETERS;
3406 END IF; --mputman added 1574527
3407
3408 -- Bug 1491332: Moved the below 2 lines to the else section below
3409 -- icx_sec.g_session_cookie_name := icx_sec.getsessioncookiename;
3410 -- icx_sec.g_session_cookie_domain := icx_sec.getsessioncookiedomain;
3411
3412 if p_ticket is not null
3413 then
3414 l_session_id := to_number(icx_call.decrypt3(p_ticket));
3415 else
3416
3417 -- mputman added 1574527
3418 -- icx_sec.g_session_cookie_name := icx_sec.getsessioncookiename;
3419
3420 if (icx_sec.g_session_cookie_name is null) then
3421 icx_sec.g_session_cookie_name := FND_WEB_CONFIG.DATABASE_ID;
3422 end if;
3423
3424 -- mputman added 1574527, don't need domain
3425 -- icx_sec.g_session_cookie_domain := icx_sec.getsessioncookiedomain;
3426
3427 l_cookie_session := owa_cookie.get(icx_sec.g_session_cookie_name);
3428
3429 if (l_cookie_session.num_vals > 0) and (l_cookie_session.vals(l_cookie_session.num_vals) <> '-1')
3430 then
3431 l_session_id := to_number(icx_call.decrypt3(l_cookie_session.vals(l_cookie_session.num_vals)));
3432 else
3433 l_session_id := -1;
3434 end if;
3435 end if;
3436
3437 return l_session_id;
3438
3439 exception
3440 when others then
3441 -- htp.p(SQLERRM);
3442 htp.p(dbms_utility.format_error_stack);
3443 return -1;
3444 end;
3445
3446 -- ***********************************************
3447 -- function getsessioncookiename
3448 -- ***********************************************
3449
3450 function getsessioncookiename return varchar2 is
3451
3452 l_session_cookie_name varchar2(81);
3453
3454 begin
3455
3456 IF icx_sec.g_session_cookie_name IS NULL THEN
3457
3458 select SESSION_COOKIE_NAME
3459 into l_session_cookie_name
3460 from ICX_PARAMETERS;
3461 ELSE
3462 l_session_cookie_name:=icx_sec.g_session_cookie_name;
3463 END IF; -- added mputman 1574527
3464
3465 if (l_session_cookie_name is null) then
3466 l_session_cookie_name := FND_WEB_CONFIG.DATABASE_ID;
3467 end if;
3468
3469 return l_session_cookie_name;
3470
3471 exception
3472 when others then
3473 htp.p(SQLERRM);
3474 return -1;
3475 end;
3476
3477 -- ***********************************************
3478 -- function getsessioncookiedomain
3479 -- ***********************************************
3480
3481 function getsessioncookiedomain return varchar2 is
3482
3483 l_session_cookie_domain varchar2(30);
3484 l_server_name varchar2(240);
3485 l_domain_count number;
3486 /*
3487 Modified logic for default domain naming to drop the first segment
3488 of the server CGI value then substr if needed to limit domain name
3489 size to no more than 3 segments.
3490 1755300
3491 */
3492 --Modified above logic remove the above restriction.
3493 --now session cookie domain can have any number of elements.
3494
3495 begin
3496 l_session_cookie_domain := trim(fnd_profile.value('ICX_SESSION_COOKIE_DOMAIN'));
3497 IF l_session_cookie_domain IS NULL THEN
3498
3499 select SESSION_COOKIE_DOMAIN
3500 into l_session_cookie_domain
3501 from ICX_PARAMETERS;
3502 END IF;
3503
3504 if (l_session_cookie_domain is null OR upper(l_session_cookie_domain) = 'DOMAIN') then
3505 l_server_name := owa_util.get_cgi_env('SERVER_NAME'); -- should APPS_WEB_AGENT PROFILE BE USED?
3506 l_domain_count := instr(l_server_name,'.',-1,2);
3507 if l_domain_count > 0
3508 then
3509 l_domain_count := instr(l_server_name,'.',1,1);
3510 --Bug 15922926 - don't limit the elements in cookie domain to 3.
3511 l_server_name := substr(l_server_name,l_domain_count,length(l_server_name));
3512 l_session_cookie_domain := l_server_name;
3513 else
3514 l_session_cookie_domain := '';
3515 end if;
3516 elsif l_session_cookie_domain = 'NULL'
3517 then
3518 l_session_cookie_domain := '';
3519 elsif upper(l_session_cookie_domain) = 'HOST'
3520 then
3521 l_session_cookie_domain := '';
3522 elsif (
3523 (l_session_cookie_domain <> 'NODOMAIN') AND -- mputman 22-FEB-02
3524 (l_session_cookie_domain <>'')) -- mputman 22-FEB-02
3525 then
3526 --user has provided some value for the profile option icx_session_cookie_domain.
3527 --append a '.' if not present.
3528 if(substr(l_session_cookie_domain,1,1) <> '.')
3529 then
3530 l_session_cookie_domain := '.'|| l_session_cookie_domain; -- bug 1612338
3531 end if;
3532
3533 --don't allow something like .com OR .org
3534 l_domain_count := instr(l_session_cookie_domain, '.',-1,2);
3535 if l_domain_count > 0
3536 then
3537 --check the security.
3538 l_server_name := owa_util.get_cgi_env('SERVER_NAME');
3539 if(instr(l_server_name, l_session_cookie_domain) = 0)
3540 then
3541 l_session_cookie_domain := '';
3542 end if;
3543 else
3544 l_session_cookie_domain := '';
3545 end if;
3546 end if;
3547 return l_session_cookie_domain;
3548
3549 exception
3550 when others then
3551 -- htp.p(SQLERRM);
3552 htp.p(dbms_utility.format_error_stack);
3553 return -1;
3554
3555
3556 end;
3557
3558 -- ***********************************************
3559 -- function createRFURL (AOL/J)
3560 -- ***********************************************
3561 function createRFURL( p_function_name varchar2,
3562 p_function_id number,
3563 p_application_id number,
3564 p_responsibility_id number,
3565 p_security_group_id number,
3566 p_session_id number,
3567 p_parameters varchar2)
3568 return varchar2 is
3569
3570 PRAGMA AUTONOMOUS_TRANSACTION;
3571
3572 l_RFURL varchar2(2000) := null;
3573 -- l_session_id number;
3574 l_function_id number;
3575
3576 begin
3577
3578 -- l_session_id := nvl(p_session_id, icx_sec.getID(icx_sec.pv_session_id));
3579
3580 if p_function_id is null
3581 then
3582 select FUNCTION_ID
3583 into l_function_id
3584 from FND_FORM_FUNCTIONS
3585 where FUNCTION_NAME = p_function_name;
3586 else
3587 l_function_id := p_function_id;
3588 end if;
3589
3590 /*
3591 l_RFURL := FND_WEB_CONFIG.PLSQL_AGENT||'OracleSSWA.Execute?E='||
3592 wfa_html.conv_special_url_chars(
3593 icx_call.encrypt(p_application_id||'*'||
3594 p_responsibility_id||'*'||
3595 p_security_group_id||'*'||
3596 l_function_id||'**] '));
3597
3598
3599 --mputman convert to execute effort
3600 -- l_RFURL := FND_WEB_CONFIG.PLSQL_AGENT||'OracleApps.RF?F='||
3601 -- icx_call.encrypt2(p_application_id||'*'||
3602 -- p_responsibility_id||'*'||
3603 -- p_security_group_id||'*'||
3604 -- l_function_id||'**] ',
3605 -- l_session_id);
3606
3607 if p_parameters is not null
3608 then
3609 --mputman convert to execute effort
3610 --l_RFURL := l_RFURL||'&'||'P='||icx_call.encrypt2(p_parameters,l_session_id);
3611 l_RFURL := l_RFURL||'&'||'P='||icx_call.encrypt(p_parameters);
3612 end if;
3613 */
3614
3615 -- 2758891 nlbarlow
3616 l_RFURL := icx_portlet.createExecLink(p_application_id => p_application_id,
3617 p_responsibility_id => p_responsibility_id,
3618 p_security_group_id => p_security_group_id,
3619 p_function_id => l_function_id,
3620 p_parameters => p_parameters,
3621 p_url_only => 'Y');
3622
3623
3624 commit; -- bug 1324906
3625
3626 return l_RFURL;
3627
3628 end createRFURL;
3629
3630 -- ***********************************************
3631 -- function createRFLink
3632 -- ***********************************************
3633 function createRFLink( p_text varchar2,
3634 p_application_id number,
3635 p_responsibility_id number,
3636 p_security_group_id number,
3637 p_function_id number,
3638 p_target varchar2,
3639 p_session_id number)
3640 return varchar2 is
3641
3642 PRAGMA AUTONOMOUS_TRANSACTION;
3643
3644 l_RFLink varchar2(2000) := null;
3645 l_session_id number;
3646
3647 begin
3648
3649 -- 2758891 nlbarlow
3650
3651 l_RFLink := icx_portlet.createExecLink(p_application_id => p_application_id,
3652 p_responsibility_id => p_responsibility_id,
3653 p_security_group_id => p_security_group_id,
3654 p_function_id => p_function_id,
3655 p_parameters => '',
3656 p_target => p_target,
3657 p_link_name => p_text,
3658 p_url_only => 'N');
3659
3660 /* --mputman convert to execute effort
3661 --l_session_id := icx_sec.getID(n_param => icx_sec.pv_session_id,
3662 -- p_session_id => p_session_id);
3663
3664 l_RFLink := '<A HREF=OracleSSWA.Execute?E='||wfa_html.conv_special_url_chars(
3665 icx_call.encrypt(p_application_id||'*'||
3666 p_responsibility_id||'*'||
3667 p_security_group_id||'*'||
3668 p_function_id||'**]'))||
3669 ' TARGET='''||p_target||'''>'||p_text||'</A>';
3670
3671 --mputman convert to execute effort
3672 -- l_RFLink := '<A HREF=OracleApps.RF?F='||
3673 -- icx_call.encrypt2(p_application_id||'*'||
3674 -- p_responsibility_id||'*'||
3675 -- p_security_group_id||'*'||
3676 -- p_function_id||'**]',
3677 -- l_session_id)||
3678 -- ' TARGET='''||p_target||'''>'||p_text||'</A>';
3679 */
3680 commit; -- bug 1324906
3681
3682 return l_RFlink;
3683
3684 end createRFLink;
3685
3686 -- ***********************************************
3687 -- procedure updateSessionContext (AOL/J)
3688 -- ***********************************************
3689 procedure updateSessionContext( p_function_name varchar2,
3690 p_function_id number,
3691 p_application_id number,
3692 p_responsibility_id number,
3693 p_security_group_id number,
3694 p_session_id number,
3695 p_transaction_id number)
3696 is
3697
3698 l_session_id number;
3699 l_function_id number;
3700 l_function_type varchar2(30);
3701 l_multi_org_flag varchar2(30);
3702 l_org_id number;
3703 l_profile_defined boolean;
3704
3705 begin
3706
3707 IF p_session_id IS NOT NULL THEN
3708 l_session_id := p_session_id;
3709 ELSE
3710 l_session_id := icx_sec.getID(icx_sec.pv_session_id);
3711 END IF; --2482554
3712 --l_session_id := nvl(p_session_id, icx_sec.getID(icx_sec.pv_session_id));
3713
3714 if p_function_id is null and p_function_name is not null
3715 then
3716 select FUNCTION_ID, TYPE
3717 into l_function_id, l_function_type
3718 from FND_FORM_FUNCTIONS
3719 where FUNCTION_NAME = p_function_name;
3720 elsif p_function_name is null and p_function_id is not null
3721 then
3722 select FUNCTION_ID, TYPE
3723 into l_function_id, l_function_type
3724 from FND_FORM_FUNCTIONS
3725 where FUNCTION_ID = p_function_id;
3726 else
3727 l_function_id := '';
3728 l_function_type := '';
3729 end if;
3730
3731 select multi_org_flag
3732 into l_multi_org_flag
3733 from fnd_product_groups
3734 where rownum < 2;
3735
3736 if l_multi_org_flag = 'Y'
3737 then
3738 fnd_profile.get_specific(
3739 name_z => 'ORG_ID',
3740 responsibility_id_z => p_responsibility_id,
3741 application_id_z => p_application_id,
3742 val_z => l_org_id,
3743 defined_z => l_profile_defined);
3744 end if;
3745
3746 update ICX_SESSIONS
3747 set RESPONSIBILITY_APPLICATION_ID = p_application_id,
3748 RESPONSIBILITY_ID = p_responsibility_id,
3749 SECURITY_GROUP_ID = p_security_group_id,
3750 ORG_ID = l_org_id,
3751 FUNCTION_ID = l_function_id,
3752 FUNCTION_TYPE = l_function_type
3753 where SESSION_ID = l_session_id;
3754
3755 if p_transaction_id is not null
3756 then
3757
3758 update ICX_TRANSACTIONS
3759 set RESPONSIBILITY_APPLICATION_ID = p_application_id,
3760 RESPONSIBILITY_ID = p_responsibility_id,
3761 SECURITY_GROUP_ID = p_security_group_id,
3762 FUNCTION_ID = l_function_id,
3763 FUNCTION_TYPE = l_function_type
3764 where SESSION_ID = l_session_id
3765 and TRANSACTION_ID = p_transaction_id;
3766 -- 3201309 nlbarlow reordered where
3767 end if;
3768
3769 commit;
3770
3771 end updateSessionContext;
3772
3773 -- ***********************************************
3774 -- function jumpIntoFlow
3775 -- ***********************************************
3776 function jumpIntoFlow( c_person_id in number,
3777 c_application_id in number,
3778 c_flow_code in varchar2,
3779 c_sequence in number,
3780 c_key1 in varchar2,
3781 c_key2 in varchar2,
3782 c_key3 in varchar2,
3783 c_key4 in varchar2,
3784 c_key5 in varchar2,
3785 c_key6 in varchar2,
3786 c_key7 in varchar2,
3787 c_key8 in varchar2,
3788 c_key9 in varchar2,
3789 c_key10 in varchar2)
3790 return varchar2 is
3791
3792 c_url varchar2(2000);
3793 n_session_id number default 911;
3794 vHost_name varchar2(80);
3795 vAgent_name varchar2(80);
3796 c_param varchar2(1000);
3797
3798 begin
3799
3800 c_url := 'POREQWF.OPENREQ?a1=' || icx_call.encrypt(c_key1);
3801
3802 -- c_param := 'POREQWF.OPENREQ?pFlowCode=' || c_flow_code
3803 -- || '&' || 'pKey=' || c_key1;
3804 -- c_url := c_url || c_param;
3805
3806 return c_url;
3807 end;
3808
3809 -- ***********************************************
3810 -- function jumpIntoFunction
3811 -- ***********************************************
3812 function jumpIntoFunction(p_application_id in number,
3813 p_function_code in varchar2,
3814 p_parameter1 in varchar2,
3815 p_parameter2 in varchar2,
3816 p_parameter3 in varchar2,
3817 p_parameter4 in varchar2,
3818 p_parameter5 in varchar2,
3819 p_parameter6 in varchar2,
3820 p_parameter7 in varchar2,
3821 p_parameter8 in varchar2,
3822 p_parameter9 in varchar2,
3823 p_parameter10 in varchar2,
3824 p_parameter11 in varchar2)
3825 return varchar2 is
3826
3827 l_url varchar2(2000);
3828 l_web_host_name varchar2(80);
3829 l_web_agent_name varchar2(80);
3830 l_web_html_call varchar2(80);
3831 l_web_encrypt_parameters varchar2(1);
3832
3833 begin
3834
3835 select web_host_name,web_agent_name,web_html_call,web_encrypt_parameters
3836 into l_web_host_name,l_web_agent_name,l_web_html_call,l_web_encrypt_parameters
3837 from fnd_form_functions
3838 where FUNCTION_NAME = p_function_code
3839 and WEB_SECURED = 'Y';
3840
3841 l_url := '';
3842
3843 if l_web_host_name is not null
3844 then
3845 l_url := FND_WEB_CONFIG.PROTOCOL||'//'||l_web_host_name||'/';
3846 end if;
3847
3848 if l_web_agent_name is not null
3849 then
3850 l_url := l_url||l_web_agent_name||'/';
3851 end if;
3852
3853 if l_url is null
3854 then
3855 l_url := FND_WEB_CONFIG.TRAIL_SLASH(fnd_profile.value('APPS_WEB_AGENT'));
3856 end if;
3857
3858 l_url := l_url||l_web_html_call;
3859
3860 if p_parameter1 is not null
3861 then
3862 if l_web_encrypt_parameters = 'Y'
3863 then
3864 l_url := l_url||'?p1='||icx_call.encrypt(p_parameter1);
3865 else
3866 l_url := l_url||'?p1='||p_parameter1;
3867 end if;
3868 end if;
3869
3870 if p_parameter2 is not null
3871 then
3872 if l_web_encrypt_parameters = 'Y'
3873 then
3874 l_url := l_url||'&'||'p2='||icx_call.encrypt(p_parameter2);
3875 else
3876 l_url := l_url||'&'||'p2='||p_parameter2;
3877 end if;
3878 end if;
3879
3880 if p_parameter3 is not null
3881 then
3882 if l_web_encrypt_parameters = 'Y'
3883 then
3884 l_url := l_url||'&'||'p3='||icx_call.encrypt(p_parameter3);
3885 else
3886 l_url := l_url||'&'||'p3='||p_parameter3;
3887 end if;
3888 end if;
3889
3890 if p_parameter4 is not null
3891 then
3892 if l_web_encrypt_parameters = 'Y'
3893 then
3894 l_url := l_url||'&'||'p4='||icx_call.encrypt(p_parameter4);
3895 else
3896 l_url := l_url||'&'||'p4='||p_parameter4;
3897 end if;
3898 end if;
3899
3900 if p_parameter5 is not null
3901 then
3902 if l_web_encrypt_parameters = 'Y'
3903 then
3904 l_url := l_url||'&'||'p5='||icx_call.encrypt(p_parameter5);
3905 else
3906 l_url := l_url||'&'||'p5='||p_parameter5;
3907 end if;
3908 end if;
3909
3910 if p_parameter6 is not null
3911 then
3912 if l_web_encrypt_parameters = 'Y'
3913 then
3914 l_url := l_url||'&'||'p6='||icx_call.encrypt(p_parameter6);
3915 else
3916 l_url := l_url||'&'||'p6='||p_parameter6;
3917 end if;
3918 end if;
3919
3920 if p_parameter7 is not null
3921 then
3922 if l_web_encrypt_parameters = 'Y'
3923 then
3924 l_url := l_url||'&'||'p7='||icx_call.encrypt(p_parameter7);
3925 else
3926 l_url := l_url||'&'||'p7='||p_parameter7;
3927 end if;
3928 end if;
3929
3930 if p_parameter8 is not null
3931 then
3932 if l_web_encrypt_parameters = 'Y'
3933 then
3934 l_url := l_url||'&'||'p8='||icx_call.encrypt(p_parameter8);
3935 else
3936 l_url := l_url||'&'||'p8='||p_parameter8;
3937 end if;
3938 end if;
3939
3940 if p_parameter9 is not null
3941 then
3942 if l_web_encrypt_parameters = 'Y'
3943 then
3944 l_url := l_url||'&'||'p9='||icx_call.encrypt(p_parameter9);
3945 else
3946 l_url := l_url||'&'||'p9='||p_parameter9;
3947 end if;
3948 end if;
3949
3950 if p_parameter10 is not null
3951 then
3952 if l_web_encrypt_parameters = 'Y'
3953 then
3954 l_url := l_url||'&'||'p10='||icx_call.encrypt(p_parameter10);
3955 else
3956 l_url := l_url||'&'||'p10='||p_parameter10;
3957 end if;
3958 end if;
3959
3960 if p_parameter11 is not null
3961 then
3962 if l_web_encrypt_parameters = 'Y'
3963 then
3964 l_url := l_url||'&'||'p11='||icx_call.encrypt(p_parameter11);
3965 else
3966 l_url := l_url||'&'||'p11='||p_parameter11;
3967 end if;
3968 end if;
3969
3970 return l_url;
3971
3972 exception
3973 when others then
3974 -- return SQLERRM;
3975 htp.p(dbms_utility.format_error_stack);
3976
3977 end;
3978
3979 -- added for 1574527 mputman
3980 function getNLS_PARAMETER(p_param in VARCHAR2)
3981 return varchar2 is
3982 requested_val VARCHAR2(255);
3983 BEGIN
3984
3985 select upper(value)
3986 into requested_val
3987 from v$nls_parameters
3988 where parameter = p_param;
3989
3990 RETURN requested_val;
3991
3992
3993 END;
3994
3995 --added by mputman for use by AOLJ/CRM
3996 PROCEDURE set_session_nls (p_session_id IN NUMBER,
3997 p_language IN VARCHAR2,
3998 p_date_format_mask IN VARCHAR2,
3999 p_language_code IN VARCHAR2,
4000 p_date_language IN VARCHAR2,
4001 p_numeric_characters IN VARCHAR2,
4002 p_sort IN VARCHAR2,
4003 p_territory IN VARCHAR2)
4004 IS
4005
4006
4007 BEGIN
4008
4009 UPDATE icx_sessions
4010 SET
4011
4012 NLS_LANGUAGE=p_language,
4013 DATE_FORMAT_MASK=p_date_format_mask,
4014 LANGUAGE_CODE=p_language_code,
4015 NLS_DATE_LANGUAGE=p_date_language,
4016 NLS_NUMERIC_CHARACTERS=p_numeric_characters,
4017 NLS_SORT=p_sort,
4018 NLS_TERRITORY=p_territory
4019 WHERE session_id=p_session_id;
4020 COMMIT;
4021
4022 EXCEPTION
4023 WHEN OTHERS THEN
4024 -- htp.p(SQLERRM);
4025 htp.p(dbms_utility.format_error_stack);
4026
4027 END;
4028
4029
4030
4031 FUNCTION recreate_session(i_1 IN VARCHAR2,
4032 i_2 IN VARCHAR2,
4033 p_enc_session IN VARCHAR2,
4034 p_mode IN VARCHAR2)
4035 RETURN VARCHAR2
4036 IS
4037
4038 u fnd_user%rowtype;
4039 c_server_name varchar2(240);
4040 c_server_port varchar2(80);
4041 l_server varchar2(240);
4042 c_script_name varchar2(80);
4043 l_host_instance varchar2(240);
4044 l_url varchar2(2000);
4045 l_result varchar2(30);
4046 l_app varchar2(30);
4047 l_msg_code varchar2(30);
4048 l_valid2 varchar2(240);
4049 v_user_id number;
4050 v_user_name varchar2(80);
4051 v_password varchar2(80);
4052 v_encrypted_psswd varchar2(1000);
4053 v_encrypted_upper_psswd varchar2(1000);
4054 c_error_msg varchar2(2000);
4055 c_login_msg varchar2(2000);
4056 e_signin_invalid exception;
4057 e_account_expired exception;
4058 e_invalid_password exception;
4059 e_java_password exception;
4060 l_enc_fnd_pwd varchar2(100);
4061 l_enc_user_pwd varchar2(100);
4062 l_expired varchar2(30);
4063 return_to_url varchar2(2000);
4064 l_agent varchar2(240);
4065 c_validate_only VARCHAR2(10);
4066 l_session_id NUMBER;
4067 l_new_xsid varchar2(32);
4068 begin
4069 if (i_1 is NULL or i_2 is NULL)
4070 then
4071 raise e_signin_invalid;
4072 end if;
4073
4074 l_result := fnd_web_sec.validate_login(upper(i_1), i_2);
4075 c_validate_only:='N';
4076
4077 if l_result = 'Y'
4078 then
4079
4080 begin
4081 select 'Y'
4082 into l_expired
4083 from FND_USER
4084 where USER_NAME = UPPER(i_1)
4085 and (PASSWORD_DATE is NULL or
4086 (PASSWORD_LIFESPAN_ACCESSES is not NULL and
4087 nvl(PASSWORD_ACCESSES_LEFT, 0) < 1) or
4088 (PASSWORD_LIFESPAN_DAYS is not NULL and
4089 SYSDATE >= PASSWORD_DATE + PASSWORD_LIFESPAN_DAYS));
4090 exception
4091 when no_data_found then
4092 l_expired := 'N';
4093 end;
4094
4095 if (l_expired = 'Y') then
4096 return_to_url:='';
4097 OracleApps.displayNewPassword(i_1, return_to_url, p_mode);
4098 return -1;
4099
4100 else
4101
4102 select *
4103 into u
4104 from fnd_user
4105 where user_name = UPPER(i_1);
4106
4107 if u.end_date is null or u.end_date > sysdate
4108 then
4109 --return the session_id after sendsession cookie
4110 l_session_id:=icx_call.decrypt3(p_enc_session);
4111
4112 -- Session Hijacking. re-create XSID whenever session is re-created.
4113 l_new_xsid := fnd_session_management.NewXSID;
4114 BEGIN
4115 UPDATE icx_sessions
4116 SET
4117 disabled_flag='N',
4118 last_connect=SYSDATE,
4119 counter=0,
4120 first_connect=SYSDATE,
4121 xsid=l_new_xsid
4122 WHERE
4123 session_id=l_session_id;
4124 exception
4125 when OTHERS then
4126 RETURN -1;
4127 END;
4128
4129 owa_util.mime_header('text/html', FALSE);
4130 sendsessioncookie(l_session_id);
4131 owa_util.http_header_close;
4132 --htp.p('testing????');
4133 RETURN 1;
4134
4135 else
4136 raise e_account_expired;
4137 end if; -- u.end_date is null or u.end_date > sysdate
4138 end if; -- l_expired
4139 end if; -- l_valid = '0';
4140 RAISE e_invalid_password;
4141 exception
4142 when e_java_password
4143 then
4144
4145 if c_validate_only = 'N'
4146 then
4147 fnd_message.set_name('ICX','ICX_ACCT_EXPIRED');
4148 c_error_msg := fnd_message.get;
4149 fnd_message.set_name('ICX','ICX_CONTACT_WEBMASTER');
4150 c_login_msg := fnd_message.get;
4151
4152 OracleApps.displayLogin(c_error_msg||' '||c_login_msg,'IC','Y');
4153 end if;
4154
4155 insert into icx_failures
4156 (user_name,password,failure_code,failure_date,
4157 created_by, creation_date, last_updated_by,
4158 last_update_date, last_update_login)
4159 values
4160 (i_1,-1,
4161 'ICX_ACCT_EXPIRED',sysdate,
4162 nvl(u.user_id,-1), sysdate, nvl(u.user_id,-1),
4163 sysdate, u.user_id);
4164
4165 return '-1';
4166
4167 when e_signin_invalid OR e_invalid_password
4168 then
4169 if c_validate_only = 'N'
4170 then
4171 fnd_message.set_name('ICX','ICX_SIGNIN_INVALID');
4172 c_error_msg := fnd_message.get;
4173 fnd_message.set_name('ICX','ICX_SIGNIN_AGAIN');
4174 c_login_msg := fnd_message.get;
4175
4176 OracleApps.displayLogin(c_error_msg||' '||c_login_msg,'IC','Y');
4177 end if;
4178
4179 v_encrypted_psswd := icx_call.encrypt(i_2);
4180
4181 insert into icx_failures
4182 (user_name,password,failure_code,failure_date,
4183 created_by, creation_date, last_updated_by,
4184 last_update_date, last_update_login)
4185 values
4186 (i_1,v_encrypted_psswd,'ICX_SIGNIN_INVALID',sysdate,
4187 '-1', sysdate, '-1', sysdate, '-1');
4188 return '-1';
4189
4190 when others
4191 then
4192 if c_validate_only = 'N'
4193 then
4194 fnd_message.set_name('ICX','ICX_SIGNIN_INVALID');
4195 c_error_msg := fnd_message.get;
4196 fnd_message.set_name('ICX','ICX_SIGNIN_AGAIN');
4197 c_login_msg := fnd_message.get;
4198
4199 OracleApps.displayLogin(c_error_msg||' '||c_login_msg,'IC','Y');
4200 end if;
4201 /*
4202 when others then
4203 htp.p(SQLERRM);
4204 */
4205 return '-1';
4206 END;
4207
4208
4209 -- ***********************************************
4210 -- function recreateURL
4211 -- ***********************************************
4212
4213 function recreateURL(p_session_id IN NUMBER,
4214 p_user_name in varchar2)
4215 return VARCHAR2 is
4216 l_url VARCHAR2(600);
4217 l_url2 VARCHAR2(600);
4218 l_url3 VARCHAR2(600);
4219
4220 l_mode VARCHAR2(20);
4221 l_errm VARCHAR2(2000);
4222
4223 BEGIN
4224
4225 -- commented out all portal redirect code from here and let it be handled in displayLogin
4226
4227 --get mode_code to see if a portal session
4228 -- SELECT mode_code
4229 -- INTO l_mode
4230 -- FROM icx_sessions
4231 -- WHERE session_id=p_session_id;
4232 --get home_url so we know where to send after portal logout
4233 -- SELECT home_url
4234 -- INTO l_url3
4235 -- FROM icx_parameters;
4236
4237 -- l_url3:= wfa_html.conv_special_url_chars(l_url3);
4238
4239 --if portal
4240 -- IF l_mode='115X' THEN
4241
4242 -- fnd_profile.get(name => 'APPS_PORTAL',
4243 -- val => l_url);
4244
4245 -- l_url2 := wfa_html.conv_special_url_chars(l_url);
4246 -- l_url := replace(l_url,'home','wwsec_app_priv.logout ?p_done_url='||(nvl(l_url3,l_url2)));
4247
4248 --else PHP
4249 -- ELSE
4250 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||'**]');
4251
4252 -- END IF;
4253 return (l_url);
4254
4255
4256 EXCEPTION
4257 WHEN OTHERS THEN
4258 l_errm:=SQLERRM;
4259 RETURN ('ERROR - '||l_errm);
4260 end;
4261
4262 --newSessionRaiseEvent will raise the WF Business Event oracle.apps.icx.security.session.created
4263 --mputman 1513025
4264 procedure newSessionRaiseEvent (p_user_id in varchar2,
4265 p_session_id in varchar2) is
4266
4267 l_parameterList WF_PARAMETER_LIST_T;
4268
4269 begin
4270
4271 --Initialize the parameter list.
4272
4273 l_parameterList := WF_PARAMETER_LIST_T(null);
4274
4275 --Populate the first subscript with param1, then extend the varray.
4276
4277 l_parameterList(1) := wf_parameter_t('p_user_id', p_user_id);
4278
4279 l_parameterList.EXTEND;
4280
4281 --Populate the second, but do not extend (will get an ORA-30625 if you do.)
4282
4283 l_parameterList(2) := wf_parameter_t('p_session_id', p_session_id);
4284
4285 --Raise the event
4286 --htp.p('####NSRE-5####');--debug mputman
4287
4288 WF_EVENT.Raise(p_event_name=>'oracle.apps.icx.security.session.created',
4289 p_event_key=>to_char(sysdate, 'HH:MI:SS'),
4290 p_parameters=>l_parameterList);
4291 --htp.p('####NSRE-6####');--debug mputman
4292
4293 end;
4294
4295 --doNewSessionEvent is a function that can be called via an event subscription to
4296 --disable all other sessions for the user_id except the session_id
4297 --(user_id and session_id are retrieved from the p_evtMsg type).
4298 --mputman 1513025
4299 function doNewSessionEvent (p_guid in raw,
4300 p_evtMsg in out NOCOPY wf_event_t) return varchar2 is
4301 l_user_id VARCHAR2(80);
4302 l_user_name VARCHAR2(240);
4303 l_session_id VARCHAR2(80);
4304 l_except_ids VARCHAR2(4000);
4305
4306 begin
4307
4308 --Access p_user_id
4309 l_user_id := p_evtMsg.GetValueForParameter('p_user_id');
4310 --Access p_session_id
4311 l_session_id := p_evtMsg.GetValueForParameter('p_session_id');
4312
4313 BEGIN
4314 SELECT user_name
4315 INTO l_user_name
4316 FROM fnd_user
4317 WHERE user_id=l_user_id;
4318 EXCEPTION
4319 WHEN OTHERS THEN
4320 WF_CORE.CONTEXT('icx_sec', 'doNewSessionEvent',p_evtMsg.getEventName( ), p_guid);
4321 WF_EVENT.setErrorInfo(p_evtMsg, 'ERROR');
4322 return 'ERROR';
4323 END;
4324
4325 BEGIN
4326
4327 SELECT substrb(parameters,(instrb(parameters,'=',1)+1))
4328 INTO l_except_ids
4329 FROM wf_event_subscriptions
4330 WHERE guid=p_guid;
4331
4332
4333
4334 EXCEPTION
4335 WHEN no_data_found THEN
4336 WF_CORE.CONTEXT('icx_sec', 'doNewSessionEvent',p_evtMsg.getEventName( ), p_guid);
4337 WF_EVENT.setErrorInfo(p_evtMsg, 'ERROR');
4338 return 'ERROR';
4339
4340 END;
4341
4342 IF (instrb((nvl(l_except_ids,' ')),l_user_name) = 0) THEN
4343
4344
4345 BEGIN
4346 UPDATE icx_sessions
4347 SET disabled_flag='Y'
4348 WHERE user_id = l_user_id
4349 AND session_id <> l_session_id
4350 AND mode_code = '115P';
4351 COMMIT;
4352
4353 EXCEPTION
4354 WHEN OTHERS THEN
4355 WF_CORE.CONTEXT('icx_sec', 'doNewSessionEvent',p_evtMsg.getEventName( ), p_guid);
4356 WF_EVENT.setErrorInfo(p_evtMsg, 'ERROR');
4357
4358 return 'ERROR';
4359 END;
4360 NULL;
4361 END IF;
4362
4363
4364 return 'SUCCESS';
4365
4366 end;
4367
4368 /*
4369 -- ***********************************************
4370 -- function newLoginId
4371 -- ***********************************************
4372
4373 function newLoginId
4374 return number is
4375
4376 l_login_id number;
4377
4378 begin
4379
4380 select fnd_logins_s.nextval
4381 into l_login_id
4382 from sys.dual;
4383
4384
4385 return(l_login_id);
4386 end;
4387
4388 */
4389
4390
4391 --disableSession is to be used with high availability to
4392 --disable all sessions that are older than the threshold value (mins)
4393 -- added for 2124463
4394 PROCEDURE disableSessions (threshold IN NUMBER)
4395 IS
4396
4397 BEGIN
4398
4399 UPDATE icx_sessions
4400 SET disabled_flag='Y'
4401 WHERE
4402 (((SYSDATE-first_connect)*24*60)> threshold);
4403
4404 COMMIT;
4405 END;
4406
4407 FUNCTION anonFunctionTest(p_func_id IN VARCHAR2,
4408 p_user_id IN NUMBER)
4409
4410 RETURN BOOLEAN IS
4411
4412 --b_allowed BOOLEAN DEFAULT FALSE;
4413 n_hits NUMBER DEFAULT 0;
4414 l_anon_name VARCHAR2(400);
4415 l_anon_user_id NUMBER;
4416 x VARCHAR2(400);
4417
4418
4419 BEGIN
4420 IF p_user_id IS NULL THEN
4421
4422 -- fnd_profile.get(name => 'GUEST_USER_PWD',
4423 -- val => l_anon_name);
4424 -- Using new api to retrieve GUEST credentials.
4425 l_anon_name := fnd_web_sec.get_guest_username_pwd;
4426 l_anon_name := SUBSTR(l_anon_name, 1, INSTR(l_anon_name, '/') -1);
4427 BEGIN
4428 SELECT user_id
4429 INTO l_anon_user_id
4430 FROM fnd_user
4431 WHERE user_name = l_anon_name;
4432 EXCEPTION
4433 WHEN no_data_found THEN
4434 l_anon_user_id := -999;
4435 END;
4436 ELSE
4437 l_anon_user_id := p_user_id;
4438
4439 END IF;
4440 select count(*)
4441 INTO n_hits
4442 from FND_FORM_FUNCTIONS a,
4443 fnd_menu_entries_vl b,
4444 fnd_responsibility_vl c,
4445 fnd_user_resp_groups d,
4446 fnd_security_groups_vl e
4447 where d.user_id = l_anon_user_id
4448 AND a.function_id = p_func_id
4449 and b.function_id = a.function_id
4450 and d.responsibility_application_id = c.application_id
4451 and b.MENU_ID = c.MENU_ID
4452 and c.responsibility_id = d.responsibility_id
4453 and type in ('WWW','WWK','JSP','SERVLET', 'INTEROPJSP')
4454 and d.start_date <= sysdate
4455 and (d.end_date is null or d.end_date > sysdate)
4456 and d.SECURITY_GROUP_ID = e.SECURITY_GROUP_ID
4457 and prompt is not null
4458 and nvl(a.function_id,-1) not IN
4459 (select ACTION_ID
4460 from FND_RESP_FUNCTIONS
4461 where RESPONSIBILITY_ID = c.responsibility_id
4462 and APPLICATION_ID = d.responsibility_application_id)
4463 and nvl(SUB_MENU_ID,-1) not IN -- submenu exclusions 2029055
4464 (select ACTION_ID
4465 from FND_RESP_FUNCTIONS
4466 where RESPONSIBILITY_ID = c.responsibility_id
4467 and APPLICATION_ID = d.responsibility_application_id);
4468 IF (n_hits >0) THEN
4469 RETURN TRUE;
4470 ELSE
4471 RETURN FALSE;
4472 END IF;
4473
4474 EXCEPTION
4475 WHEN OTHERS THEN
4476 x := SQLERRM;
4477
4478 RETURN FALSE;
4479 END; -- anonFucntionTest
4480
4481 PROCEDURE setUserNLS (p_user_id IN NUMBER,
4482 l_language OUT NOCOPY varchar2,
4483 l_language_code OUT NOCOPY varchar2,
4484 l_date_format OUT NOCOPY varchar2,
4485 l_date_language OUT NOCOPY varchar2,
4486 l_numeric_characters OUT NOCOPY varchar2,
4487 l_nls_sort OUT NOCOPY varchar2,
4488 l_nls_territory OUT NOCOPY varchar2,
4489 l_limit_time OUT NOCOPY NUMBER,
4490 l_limit_connects OUT NOCOPY NUMBER,
4491 l_org_id OUT NOCOPY varchar2)
4492
4493 IS
4494
4495
4496 l_timeout number;
4497
4498
4499 begin
4500
4501 setUserNLS(p_user_id,
4502 l_language,
4503 l_language_code,
4504 l_date_format,
4505 l_date_language,
4506 l_numeric_characters,
4507 l_nls_sort,
4508 l_nls_territory,
4509 l_limit_time,
4510 l_limit_connects,
4511 l_org_id,
4512 l_timeout);
4513
4514
4515 END;--setUserNLS
4516
4517
4518
4519 PROCEDURE setUserNLS (p_user_id IN NUMBER,
4520 l_language OUT NOCOPY varchar2,
4521 l_language_code OUT NOCOPY varchar2,
4522 l_date_format OUT NOCOPY varchar2,
4523 l_date_language OUT NOCOPY varchar2,
4524 l_numeric_characters OUT NOCOPY varchar2,
4525 l_nls_sort OUT NOCOPY varchar2,
4526 l_nls_territory OUT NOCOPY varchar2,
4527 l_limit_time OUT NOCOPY NUMBER,
4528 l_limit_connects OUT NOCOPY NUMBER,
4529 l_org_id OUT NOCOPY varchar2,
4530 l_timeout OUT NOCOPY NUMBER)
4531
4532 IS
4533 -- l_language varchar2(80);
4534 -- l_language_code varchar2(30);
4535 -- l_date_format varchar2(150);
4536 -- l_date_language varchar2(30);
4537 -- l_numeric_characters varchar2(30);
4538 -- l_nls_sort varchar2(30);
4539 -- l_nls_territory varchar2(30);
4540 -- l_limit_time number;
4541 -- l_limit_connects number;
4542 -- l_multi_org_flag varchar2(1);
4543 -- l_org_id varchar2(50);
4544 l_multi_org_flag varchar2(1);
4545 l_profile_defined boolean;
4546 db_lang varchar2(512);
4547 lang varchar2(255);
4548 l_nls_characterset varchar2(50);
4549
4550 c_language varchar2(30);
4551 l_login_id NUMBER;
4552 l_expired VARCHAR2(5);
4553
4554 cursor c1 (lang in varchar2) is
4555 select UTF8_DATE_LANGUAGE
4556 from FND_LANGUAGES
4557 where NLS_LANGUAGE = lang;
4558
4559 cursor c2 (lang in varchar2) is
4560 select LOCAL_DATE_LANGUAGE
4561 from FND_LANGUAGES
4562 where NLS_LANGUAGE = lang;
4563
4564 begin
4565
4566 -- The following Profiles should be set
4567
4568 fnd_profile.get_specific(name_z => 'ICX_LANGUAGE',
4569 user_id_z => p_user_id,
4570 val_z => l_language,
4571 defined_z => l_profile_defined);
4572
4573 if l_language is null then
4574
4575 /*
4576 select upper(value)
4577 into l_language
4578 from v$nls_parameters
4579 where parameter = 'NLS_LANGUAGE';
4580 */ -- removed select 1574527 mputman
4581
4582 l_language:=getNLS_PARAMETER('NLS_LANGUAGE'); -- replaces above select mputman 1574527
4583
4584 end if;
4585
4586 select language_code
4587 into l_language_code
4588 from fnd_languages
4589 where nls_language = l_language;
4590
4591 fnd_profile.get_specific(name_z => 'ICX_NLS_SORT',
4592 user_id_z => p_user_id,
4593 val_z => l_nls_sort,
4594 defined_z => l_profile_defined);
4595
4596 if l_nls_sort is null then
4597 /*
4598 select upper(value)
4599 into l_nls_sort
4600 from v$nls_parameters
4601 where parameter = 'NLS_SORT';
4602 */
4603 l_nls_sort:=getNLS_PARAMETER('NLS_SORT'); -- replaces above select mputman 1574527
4604
4605
4606 end if;
4607
4608 fnd_profile.get_specific(name_z => 'ICX_DATE_FORMAT_MASK',
4609 user_id_z => p_user_id,
4610 val_z => l_date_format,
4611 defined_z => l_profile_defined);
4612
4613 if l_date_format is null then
4614 /*
4615 select upper(value)
4616 into l_date_format
4617 from v$nls_parameters
4618 where parameter = 'NLS_DATE_FORMAT';
4619 */
4620 l_date_format:=getNLS_PARAMETER('NLS_DATE_FORMAT'); -- replaces above select mputman 1574527
4621
4622 end if;
4623
4624 l_date_format := replace(upper(l_date_format), 'YYYY', 'RRRR');
4625 l_date_format := replace(l_date_format, 'YY', 'RRRR');
4626 if (instr(l_date_format, 'RR') > 0) then
4627 if (instr(l_date_format, 'RRRR') = 0) then
4628 l_date_format := replace(l_date_format, 'RR', 'RRRR');
4629 end if;
4630 end if;
4631
4632 /* set the NLS date language. Get it from the FND_LANGUAGES table,
4633 choosing which column based on whether the codeset is UTF8
4634 or AL32UTF8. But the profile ICX_DATE_LANGUAGE overrides
4635 all that if it is set.
4636 */
4637
4638 fnd_profile.get_specific(name_z => 'ICX_DATE_LANGUAGE',
4639 user_id_z => p_user_id,
4640 val_z => l_date_language,
4641 defined_z => l_profile_defined);
4642
4643 if l_date_language is null then
4644 l_nls_characterset := getNLS_PARAMETER('NLS_CHARACTERSET');
4645 if (l_nls_characterset in ('UTF8', 'AL32UTF8')) then
4646 open c1(l_language);
4647 fetch c1 into l_date_language;
4648 close c1;
4649 else
4650 open c2(l_language);
4651 fetch c2 into l_date_language;
4652 close c2;
4653 end if;
4654
4655 end if;
4656
4657 fnd_profile.get_specific(name_z => 'ICX_NUMERIC_CHARACTERS',
4658 user_id_z => p_user_id,
4659 val_z => l_numeric_characters,
4660 defined_z => l_profile_defined);
4661
4662 if l_numeric_characters is null then
4663 /*
4664 select upper(value)
4665 into l_numeric_characters
4666 from v$nls_parameters
4667 where parameter = 'NLS_NUMERIC_CHARACTERS';
4668 */
4669 l_numeric_characters:=getNLS_PARAMETER('NLS_NUMERIC_CHARACTERS'); -- replaces above select mputman 1574527
4670
4671 end if;
4672
4673 fnd_profile.get_specific(name_z => 'ICX_TERRITORY',
4674 user_id_z => p_user_id,
4675 val_z => l_nls_territory,
4676 defined_z => l_profile_defined);
4677
4678 if l_nls_territory is null then
4679 /*
4680 select upper(value)
4681 into l_nls_territory
4682 from v$nls_parameters
4683 where parameter = 'NLS_TERRITORY';
4684 */
4685 l_nls_territory:=getNLS_PARAMETER('NLS_TERRITORY'); -- replaces above select mputman 1574527
4686
4687
4688 end if;
4689
4690 fnd_profile.get_specific(name_z => 'ICX_LIMIT_TIME',
4691 user_id_z => p_user_id,
4692 val_z => l_limit_time,
4693 defined_z => l_profile_defined);
4694
4695 if l_limit_time is null then
4696 l_limit_time := 4;
4697 end if;
4698
4699 fnd_profile.get_specific(name_z => 'ICX_LIMIT_CONNECT',
4700 user_id_z => p_user_id,
4701 val_z => l_limit_connects,
4702 defined_z => l_profile_defined);
4703
4704 if l_limit_connects is null
4705 then
4706 l_limit_connects := 1000;
4707 end if;
4708
4709 fnd_profile.get_specific(name_z => 'ICX_SESSION_TIMEOUT',
4710 user_id_z => p_user_id,
4711 val_z => l_timeout,
4712 defined_z => l_profile_defined);
4713
4714
4715 select multi_org_flag
4716 into l_multi_org_flag
4717 from fnd_product_groups
4718 where rownum < 2;
4719
4720 if l_multi_org_flag = 'Y' then
4721 fnd_profile.get_specific(name_z => 'ORG_ID',
4722 val_z => l_org_id,
4723 defined_z => l_profile_defined);
4724 end if;
4725
4726 END;--setUserNLS
4727
4728
4729
4730 end icx_sec;