[Home] [Help]
PACKAGE BODY: APPS.FND_SESSION_MANAGEMENT
Source
1 package body FND_SESSION_MANAGEMENT as
2 /* $Header: AFICXSMB.pls 120.32.12020000.2 2012/12/14 20:29:26 ctilley ship $ */
3
4 -- ***********************************************
5 -- function NewSessionId
6 -- ***********************************************
7
8 function NewSessionId return number is
9
10 l_session_id number;
11 x_session_id varchar2(1) := 'N';
12
13 begin
14
15 l_session_id := fnd_crypto.SmallRandomNumber;
16
17 loop
18
19 select 'Y' into x_session_id from icx_sessions
20 where session_id = l_session_id;
21
22 if x_session_id = 'Y'
23 then
24 l_session_id := fnd_crypto.SmallRandomNumber;
25 else
26 return(l_session_id);
27 end if;
28
29 end loop;
30
31 exception
32 when no_data_found
33 then
34 return(l_session_id);
35 end NewSessionId;
36
37 function NewXSID return varchar2 is
38
39 l_XSID varchar2(32);
40 x_XSID varchar2(1) := 'N';
41
42 begin
43
44 -- l_XSID := fnd_crypto.encode(fnd_crypto.RandomBytes(18),fnd_crypto.ENCODE_URL);
45 -- Bug#4192742. XSID which is stored as cookie value can't contain illegal
46 -- characters. Hence using fnd_crypto.RandomString to generate alpha numeric
47 -- string as XSID.
48 l_XSID := fnd_crypto.RandomString(len=>26,
49 msk=> FND_CRYPTO_CONSTANTS.ALPHANUMERIC_MASK);
50
51 loop
52
53 select 'Y' into x_XSID from icx_sessions
54 where XSID = l_XSID;
55
56 if x_XSID = 'Y'
57 then
58 -- l_XSID := fnd_crypto.encode(fnd_crypto.RandomBytes(18),fnd_crypto.ENCODE_URL);
59 -- Bug#4192742. XSID which is stored as cookie value can't contain illegal
60 -- characters. Hence using fnd_crypto.RandomString to generate
61 -- alpha numeric string as XSID.
62 l_XSID := fnd_crypto.RandomString(len=>26,
63 msk=> FND_CRYPTO_CONSTANTS.ALPHANUMERIC_MASK);
64 else
65 -- return(l_XSID||':S');
66 return(l_XSID);
67 end if;
68
69 end loop;
70
71 exception
72 when no_data_found
73 then
74 -- return(l_XSID||':S');
75 return(l_XSID);
76 end NewXSID;
77
78 function NewTransactionId return number is
79
80 l_transaction_id number;
81 x_transaction_id varchar2(1) := 'N';
82
83 begin
84
85 l_transaction_id := fnd_crypto.SmallRandomNumber;
86
87 loop
88
89 select 'Y' into x_transaction_id from icx_transactions
90 where transaction_id = l_transaction_id;
91
92 if x_transaction_id = 'Y'
93 then
94 l_transaction_id := fnd_crypto.SmallRandomNumber;
95 else
96 return(l_transaction_id);
97 end if;
98
99 end loop;
100
101 exception
102 when no_data_found
103 then
104 return(l_transaction_id);
105 end NewTransactionId;
106
107
108 function NewTransactionId(p_session_id in number)
109 return number is
110
111 l_transaction_id number;
112 x_transaction_id varchar2(1) := 'N';
113
114 begin
115
116 l_transaction_id := fnd_crypto.SmallRandomNumber;
117
118 loop
119
120 select 'Y' into x_transaction_id from icx_transactions
121 where transaction_id = l_transaction_id
122 and SESSION_ID = p_session_id
123 and DISABLED_FLAG <> 'Y';
124
125 if x_transaction_id = 'Y'
126 then
127 l_transaction_id := fnd_crypto.SmallRandomNumber;
128 else
129 return(l_transaction_id);
130 end if;
131
132 end loop;
133
134 exception
135 when no_data_found
136 then
137 return(l_transaction_id);
138 end NewTransactionId;
139
140 function NewXTID return varchar2 is
141
142 l_XTID varchar2(32);
143 x_XTID varchar2(1);
144
145 begin
146
147 l_XTID := fnd_crypto.encode(fnd_crypto.RandomBytes(18),fnd_crypto.ENCODE_URL);
148
149 loop
150
151 select 'Y' into x_XTID from icx_transactions
152 where XTID = l_XTID;
153
154 if x_XTID = 'Y'
155 then
156 l_XTID := fnd_crypto.encode(fnd_crypto.RandomBytes(18),fnd_crypto.ENCODE_URL);
157 else
158 return(l_XTID||':T');
159 end if;
160
161 end loop;
162
163 exception
164 when no_data_found
165 then
166 return(l_XTID||':T');
167 end NewXTID;
168
169
170 --newSessionRaiseEvent will raise the WF Business Event oracle.apps.icx.security.session.created
171 --mputman 1513025
172 procedure newSessionRaiseEvent(p_user_id in varchar2,
173 p_session_id in varchar2) is
174
175 l_parameterList WF_PARAMETER_LIST_T;
176
177 begin
178
179 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
180 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE
181 , 'fnd.plsql.FND_SESSION_MANAGEMENT.newSessionRaiseEvent','BEGIN');
182 end if;
183 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
184 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
185 , 'fnd.plsql.FND_SESSION_MANAGEMENT.newSessionRaiseEvent',
186 'userid='||NVL(p_user_id,'**NULL**')
187 ||' sessionId='||NVL(p_session_id,'**NULL*')
188 );
189 end if;
190 --Initialize the parameter list.
191
192 l_parameterList := WF_PARAMETER_LIST_T(null);
193
194 --Populate the first subscript with param1, then extend the varray.
195
196 l_parameterList(1) := wf_parameter_t('p_user_id', p_user_id);
197
198 l_parameterList.EXTEND;
199
200 --Populate the second, but do not extend (will get an ORA-30625 if you do.)
201
202 l_parameterList(2) := wf_parameter_t('p_session_id', p_session_id);
203
204 --Raise the event
205
206 begin
207 WF_EVENT.Raise(p_event_name=>'oracle.apps.icx.security.session.created',
208 p_event_key=>to_char(sysdate, 'HH:MI:SS'),
209 p_parameters=>l_parameterList);
210 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
211 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE
212 , 'fnd.plsql.FND_SESSION_MANAGEMENT.newSessionRaiseEvent','END');
213 end if;
214 exception
215 when others then
216 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
217 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED
218 , 'fnd.plsql.FND_SESSION_MANAGEMENT.newSessionRaiseEvent','END with errors '||sqlerrm);
219 end if;
220 null; -- allows login to continue if WF process not installed.
221 end;
222
223 end newSessionRaiseEvent;
224
225 --doNewSessionEvent is a function that can be called via an event subscription to
226 --disable all other sessions for the user_id except the session_id
227 --(user_id and session_id are retrieved from the p_evtMsg type).
228 --mputman 1513025
229 function doNewSessionEvent(p_guid in raw,
230 p_evtMsg in out NOCOPY wf_event_t)
231 return varchar2 is
232
233 l_user_id VARCHAR2(80);
234 l_user_name VARCHAR2(240);
235 l_session_id VARCHAR2(80);
236 l_except_ids VARCHAR2(4000);
237 -- bug:7715927
238 l_login_id NUMBER;
239 l_audit_level VARCHAR2(1);
240
241 cursor c_end_date_fndlogins is
242 SELECT login_id
243 from ICX_SESSIONS
244 where user_id = l_user_id
245 and session_id <> l_session_id
246 and disabled_flag = 'N'
247 and mode_code = '115P'
248 and user_id <> 6;
249 -- Added the last 2 lines in above cursor for CTILLY for bug#8964712
250
251
252 begin
253
254 --Access p_user_id
255 l_user_id := p_evtMsg.GetValueForParameter('p_user_id');
256 --Access p_session_id
257 l_session_id := p_evtMsg.GetValueForParameter('p_session_id');
258
259 BEGIN
260 SELECT user_name
261 INTO l_user_name
262 FROM fnd_user
263 WHERE user_id=l_user_id;
264 EXCEPTION
265 WHEN OTHERS THEN
266 WF_CORE.CONTEXT('fnd_session_management', 'doNewSessionEvent',p_evtMsg.getEventName( ), p_guid);
267 WF_EVENT.setErrorInfo(p_evtMsg, 'ERROR');
268 return 'ERROR';
269 END;
270
271 BEGIN
272 SELECT substrb(parameters,(instrb(parameters,'=',1)+1))
273 INTO l_except_ids
274 FROM wf_event_subscriptions
275 WHERE guid=p_guid;
276
277 EXCEPTION
278 WHEN no_data_found THEN
279 WF_CORE.CONTEXT('fnd_session_management', 'doNewSessionEvent',p_evtMsg.getEventName( ), p_guid);
280 WF_EVENT.setErrorInfo(p_evtMsg, 'ERROR');
281 return 'ERROR';
282 END;
283
284 IF (instrb((nvl(l_except_ids,' ')),l_user_name) = 0)
285 THEN
286 BEGIN
287
288 -- bug:7715927
289 l_audit_level:=fnd_profile.value('SIGNONAUDIT:LEVEL');
290
291 IF (l_audit_level is not null) THEN
292 FOR end_date_rec in c_end_date_fndlogins LOOP
293 fnd_signon.audit_end(l_login_id); -- end date FND_LOGINS
294 END LOOP;
295 END IF;
296
297
298 UPDATE icx_sessions
299 SET disabled_flag='Y'
300 WHERE user_id = l_user_id
301 AND session_id <> l_session_id
302 AND mode_code = '115P';
303
304 COMMIT;
305
306 EXCEPTION
307 WHEN OTHERS THEN
308 WF_CORE.CONTEXT('fnd_session_management', 'doNewSessionEvent',p_evtMsg.getEventName( ), p_guid);
309 WF_EVENT.setErrorInfo(p_evtMsg, 'ERROR');
310 return 'ERROR';
311 END;
312 NULL;
313 END IF;
314
315 return 'SUCCESS';
316
317 end;
318
319
320 /*
321 * Fetches the values of the FND_FIXED_KEY_ENABLED and FND_FIXED_SEC_KEY
322 * profiles to use as the mac and encryption key for the session. If not
323 * specified, just returns nulls. Raises an exception
324 * if the values are set but improperly defined.
325 */
326 procedure get_fixed_sec_keys(p_user_id in number,
327 p_mac_key out nocopy raw,
328 p_enc_key out nocopy raw) is
329 e_invalid_fixed_key exception;
330 lf_key varchar2(64);
331 lm_key varchar2(40);
332 l_fixed_key varchar2(10);
333 l_profile_defined boolean;
334 begin
335 fnd_profile.get_specific(name_z => 'FND_FIXED_KEY_ENABLED',
336 user_id_z => p_user_id,
337 val_z => l_fixed_key,
338 defined_z => l_profile_defined);
339 if(l_fixed_key = 'Y') then
340 fnd_profile.get_specific(name_z => 'FND_FIXED_SEC_KEY',
341 user_id_z => p_user_id,
342 val_z => lf_key,
343 defined_z => l_profile_defined);
344
345 if(length(lf_key) <> 64) then
346 raise e_invalid_fixed_key;
347 end if;
348 p_enc_key := hextoraw(lf_key);
349 lm_key := substr(lf_key, 0, 40);
350 p_mac_key := hextoraw(lm_key);
351 else
352 p_enc_key := null;
353 p_mac_key := null;
354 end if;
355 exception
356 when others then
357 app_exception.raise_exception(exception_text=>
358 'Invalid Key defined in the profile FND_FIXED_SEC_KEY.' ||
359 ' The key should be a Hexadecimal string of length 64');
360 app_exception.raise_exception;
361 end get_fixed_sec_keys;
362
363
364 function createSessionPrivate(p_user_id in number,
365 p_session_id in number,
366 p_pseudo_flag in varchar2,
367 c_mode_code in varchar2,
368 p_server_id in varchar2,
369 p_home_url in varchar2,
370 p_language_code in varchar2,
371 p_proxy_user in number)
372 return varchar2 is
373
374 PRAGMA AUTONOMOUS_TRANSACTION; --(gjimenez -> bug#4163368)
375
376 l_language varchar2(80);
377 l_language_code varchar2(30);
378 l_date_format varchar2(150);
379 l_date_language varchar2(30);
380 l_numeric_characters varchar2(30);
381 l_nls_sort varchar2(30);
382 l_nls_territory varchar2(30);
383 l_limit_time number;
384 l_limit_connects number;
385 l_org_id varchar2(50);
386 l_timeout number;
387
388 l_login_id NUMBER;
389 l_node_id number;
390 l_XSID varchar2(32);
391 l_guest varchar2(30);
392 l_guest_username varchar2(240);
393 l_guest_user_id number;
394 l_profile_defined boolean;
395 l_dist varchar2(30);
396 l_enc_key raw(32);
397 l_mac_key raw(20);
398 e_invalid_fixed_key exception;
399 lf_key varchar2(64);
400 lm_key varchar2(40);
401 l_fixed_key varchar2(10);
402 l_module varchar2(100) := 'fnd.plsql.FND_SESSION_MANAGEMENT.createSessionPrivate';
403 begin
404 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
405 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , l_module, 'BEGIN');
406 end if;
407
408 begin
409 select node_id into l_node_id from fnd_nodes
410 where server_id = p_server_id;
411 exception
412 when no_data_found THEN
413 l_node_id := 9999;
414 end;
415
416 -- BUG 5354477 amgonzal
417 -- Finding the corresponding ICX_SESSION_TIMEOUT for the new session to be created.
418 --
419
420 -- There are not responsibility_id and app_resp_id defined.
421 l_profile_defined := false;
422 fnd_profile.get_specific (name_z => 'ICX_SESSION_TIMEOUT',
423 user_id_z => p_user_id,
424 val_z => l_timeout,
425 defined_z => l_profile_defined);
426 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
427 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT , l_module||'.timeout',
428 'timeout : ' || to_char(l_timeout) || ' User Id : ' || to_char (p_user_id));
429 end if;
430 -- end BUG 5354477
431
432 setUserNLS(p_user_id,
433 p_language_code,
434 l_language,
435 l_language_code,
436 l_date_format,
437 l_date_language,
438 l_numeric_characters,
439 l_nls_sort,
440 l_nls_territory,
441 l_limit_time,
442 l_limit_connects,
443 l_org_id,
444 l_timeout);
445
446 -- bug 3375261, switched to new version of new_icx_session to
447 -- not perform password related operations when creating a session
448 -- fnd_signon.new_icx_session(p_user_id,
449 -- l_login_id);
450 -- Call new api new_proxy_icx_session(new version of new_icx_session) which
451 -- has an extra param to indicate whether it's creation of proxy session
452 -- (or) normal session
453 fnd_signon.new_proxy_icx_session(UID => p_user_id,
454 proxy_user => p_proxy_user,
455 login_id => l_login_id);
456
457
458 l_XSID := NewXSID;
459
460 -- Is user GUEST
461 -- fnd_profile.get_specific
462 -- (name_z => 'GUEST_USER_PWD',
463 -- val_z => l_guest_username ,
464 -- defined_z => l_profile_defined);
465 -- Using new api to retrieve GUEST credentials.
466 l_guest_username := fnd_web_sec.get_guest_username_pwd;
467
468 l_guest_username := UPPER(SUBSTR(l_guest_username,1,INSTR(l_guest_username,'/') -1));
469 BEGIN
470 SELECT user_id
471 INTO l_guest_user_id
472 FROM fnd_user
473 WHERE user_name = l_guest_username;
474 EXCEPTION
475 WHEN no_data_found THEN
476 l_guest_user_id := -999;
477 END;
478
479 if l_guest_user_id = p_user_id
480 then
481 l_guest := 'Y';
482 else
483 l_guest := 'N';
484 end if;
485
486 fnd_profile.get_specific
487 (name_z => 'DISTRIBUTED_ENVIRONMENT',
488 val_z => l_dist,
489 defined_z => l_profile_defined);
490
491 -- fetch values for the encryption keys
492 get_fixed_sec_keys(p_user_id, l_mac_key, l_enc_key);
493 if ( l_mac_key is null or l_enc_key is null ) then
494 l_enc_key := fnd_crypto.RandomBytes(32);
495 l_mac_key := fnd_crypto.RandomBytes(20);
496 end if;
497
498 insert into icx_sessions (
499 session_id,
500 user_id,
501 org_id,
502 security_group_id,
503 mode_code,
504 home_url,
505 nls_language,
506 language_code,
507 pseudo_flag,
508 limit_time,
509 limit_connects,
510 counter,
511 first_connect,
512 last_connect,
513 created_by,
514 creation_date,
515 last_updated_by,
516 last_update_date,
517 last_update_login,
518 date_format_mask,
519 nls_numeric_characters,
520 nls_date_language,
521 nls_sort,
522 nls_territory,
523 disabled_flag,
524 node_id,
525 login_id,
526 MAC_KEY,
527 ENC_KEY,
528 XSID,
529 TIME_OUT,
530 GUEST,
531 DISTRIBUTED,
532 proxy_user_id)
533 values (
534 p_session_id,
535 p_user_id,
536 l_org_id,
537 fnd_session_management.g_security_group_id,
538 c_mode_code,
539 p_home_url,
540 l_language,
541 l_language_code,
542 p_pseudo_flag,
543 l_limit_time,
544 l_limit_connects,
545 0,
546 sysdate,
547 sysdate,
548 p_user_id,
549 sysdate,
550 p_user_id,
551 sysdate,
552 p_user_id,
553 l_date_format,
554 l_numeric_characters,
555 l_date_language,
556 l_nls_sort,
557 l_nls_territory,
558 'N',
559 l_node_id,
560 l_login_id, -- mputman added login_id per 2020952
561 l_mac_key,
562 l_enc_key,
563 l_XSID,
564 l_timeout,
565 l_guest,
566 l_dist,
567 p_proxy_user);
568
569 commit;
570
571 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
572 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , l_module, 'END');
573 end if;
574 return '0';
575 EXCEPTION WHEN OTHERS THEN
576 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
577 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED , l_module, 'Exception:'||sqlcode||' '||sqlerrm);
578 end if;
579 RAISE;
580 -- exception
581 -- when others then
582 -- return -1;
583 end;
584
585 -- p_language_code added for enh. 4082741.
586 -- if a non-null language code is passed in and is one
587 -- of the installed languages, the language code
588 -- and nls language settings for the session to be created
589 -- will overwrite what's specified in the nls profiles.
590 -- The other nls settings will still get their values from
591 -- the profiles.
592 function createSession(p_user_id in number,
593 c_mode_code in varchar2,
594 c_sec_grp_id in NUMBER,
595 p_server_id in varchar2,
596 p_home_url in varchar2,
597 p_language_code in varchar2,
598 p_proxy_user in number)
599 return number is
600
601 l_session_id number;
602 l_message varchar2(80);
603 l_module varchar2(200):= 'fnd.plsql.FND_SESSION_MANAGEMENT.createSession';
604
605 begin
606
607 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
608 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , l_module, 'BEGIN');
609 end if;
610 fnd_session_management.g_security_group_id := c_sec_grp_id;
611
612
613 l_session_id := NewSessionId;
614 l_message := createSessionPrivate( p_user_id => p_user_id,
615 p_server_id => p_server_id,
616 p_session_id => l_session_id,
617 p_pseudo_flag => 'N',
618 c_mode_code => nvl(c_mode_code,'115P'),
619 p_home_url => p_home_url,
620 p_language_code => p_language_code,
621 p_proxy_user => p_proxy_user);
622 if l_message = '0'
623 then
624 newSessionRaiseEvent(p_user_id,l_session_id);
625 newSSOSession(p_user_id,l_session_id);
626
627 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
628 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , l_module, 'END->'||l_session_id);
629 end if;
630 return l_session_id;
631 else
632 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
633 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , l_module, 'END->-1(l_message=0)');
634 end if;
635 return -1;
636 end if;
637
638 EXCEPTION WHEN OTHERS THEN
639 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
640 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED , l_module, 'Exception:'||sqlcode||' '||sqlerrm);
641 end if;
642 RAISE;
643 -- exception
644 -- when others then
645 -- return -1;
646 end;
647
648 function convertGuestSession(p_user_id in number,
649 p_server_id in varchar2,
650 p_session_id in varchar2,
651 p_language_code in varchar2,
652 c_sec_grp_id in number,
653 p_home_url in varchar2,
654 p_mode_code in varchar2)
655 return varchar2 is
656 pragma AUTONOMOUS_TRANSACTION;
657 l_mode_code varchar2(30);
658 l_language varchar2(80);
659 l_language_code varchar2(30);
660 l_date_format varchar2(150);
661 l_date_language varchar2(30);
662 l_numeric_characters varchar2(30);
663 l_nls_sort varchar2(30);
664 l_nls_territory varchar2(30);
665 l_limit_time number;
666 l_limit_connects number;
667 l_org_id varchar2(50);
668 l_timeout number;
669 l_session_id number;
670
671 l_login_id NUMBER;
672 l_node_id number;
673 l_XSID varchar2(32);
674 l_guest varchar2(30);
675 l_guest_username varchar2(240);
676 l_guest_user_id number;
677 l_profile_defined boolean;
678 l_dist varchar2(30);
679 l_user_id number;
680 l_enc_key raw(32);
681 l_mac_key raw(20);
682
683 l_resp_id number;
684 l_resp_app_id number;
685 l_curr_timeout number;
686 l_profile_timeout number;
687
688 l_audit_level varchar2(1) := null;
689 l_from_login_id NUMBER;
690 l_module varchar2(200):= 'fnd.plsql.FND_SESSION_MANAGEMENT.convertGuestSession';
691
692 begin
693 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
694 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , l_module, 'BEGIN');
695 end if;
696 -- check if user exists
697 begin
698 select user_id into l_user_id from fnd_user
699 where user_id = p_user_id and
700 (start_date <= sysdate) and
701 (end_date is null or end_date>sysdate);
702 exception
703 when no_data_found then
704 rollback;
705 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
706 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , l_module, 'END-> user not found');
707 end if;
708 return 'N';
709 end;
710 -- check if it is guest session
711 begin
712 select session_id,guest, mode_code, time_out, responsibility_application_id, responsibility_id, login_id
713 into l_session_id,l_guest, l_mode_code, l_curr_timeout, l_resp_app_id, l_resp_id, l_from_login_id
714 from icx_sessions
715 where xsid = p_session_id;
716 exception
717 when no_data_found then
718 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
719 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , l_module, 'END-> session not found');
720 end if;
721 rollback;
722 return 'N';
723 end;
724 if (l_guest <> 'Y') then
725 rollback;
726 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
727 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , l_module, 'END-> no guest session ');
728 end if;
729 return 'N';
730 end if;
731
732 -- check if switched-to user is GUEST
733 -- fnd_profile.get_specific
734 -- (name_z => 'GUEST_USER_PWD',
735 -- val_z => l_guest_username ,
736 -- defined_z => l_profile_defined);
737 -- Using new api to retrieve GUEST credentials.
738 l_guest_username := fnd_web_sec.get_guest_username_pwd;
739
740 l_guest_username := UPPER(SUBSTR(l_guest_username,1,INSTR(l_guest_username,'/') -1));
741 BEGIN
742 SELECT user_id
743 INTO l_guest_user_id
744 FROM fnd_user
745 WHERE user_name = l_guest_username;
746 EXCEPTION
747 WHEN no_data_found THEN
748 l_guest_user_id := -999;
749 END;
750
751 if l_guest_user_id = p_user_id
752 then
753 rollback;
754 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
755 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , l_module, 'END-> guest target user ');
756 end if;
757 return 'N';
758 end if;
759
760
761 fnd_session_management.g_security_group_id := c_sec_grp_id;
762
763 begin
764 select node_id into l_node_id from fnd_nodes
765 where server_id = p_server_id;
766 exception
767 when no_data_found THEN
768 l_node_id := 9999;
769 end;
770
771 -- Bug 5354477 amgonzal
772 -- Finding the ICX_SESSION_TIMEOUT for the user session being converted
773 --
774 l_profile_defined := false;
775 fnd_profile.get_specific (name_z => 'ICX_SESSION_TIMEOUT',
776 user_id_z => p_user_id,
777 responsibility_id_z => l_resp_id,
778 application_id_z => l_resp_app_id,
779 val_z => l_profile_timeout,
780 defined_z => l_profile_defined);
781 if l_profile_defined then
782 l_timeout := l_profile_timeout;
783 else
784 l_timeout := l_curr_timeout;
785 end if;
786 l_profile_defined := false;
787
788
789 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
790 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
791 , 'fnd.plsql.FND_SESSION_MANAGEMENT.convertGuestSession.timeout'
792 , 'timeout : ' || to_char(l_timeout) || ' User Id : ' || to_char (p_user_id)
793 || ' Resp ID: ' || to_char(l_resp_id)
794 || ' Resp app ID : ' || to_char(l_resp_app_id));
795 end if;
796 -- end BUG 5354477
797
798
799 setUserNLS(p_user_id,
800 p_language_code,
801 l_language,
802 l_language_code,
803 l_date_format,
804 l_date_language,
805 l_numeric_characters,
806 l_nls_sort,
807 l_nls_territory,
808 l_limit_time,
809 l_limit_connects,
810 l_org_id,
811 l_timeout);
812
813 -- Bug 6010245 Guest Login is not being end dated in FND_LOGINS.
814 -- AMGONZAL.
815
816 l_audit_level:=fnd_profile.value('SIGNONAUDIT:LEVEL');
817 if (l_audit_level is not null) and ( l_from_login_id is not null) then
818 fnd_signon.audit_end(l_from_login_id); -- end guest audit session and resps.
819 end if;
820
821 fnd_signon.new_icx_session(p_user_id,
822 l_login_id);
823
824 get_fixed_sec_keys(p_user_id, l_mac_key, l_enc_key);
825
826 -- Session Hijacking fix.
827 l_XSID := NewXSID;
828 update icx_sessions set (
829 user_id,
830 mode_code,
831 org_id,
832 security_group_id,
833 function_id,
834 home_url,
835 nls_language,
836 language_code,
837 limit_time,
838 limit_connects,
839 counter,
840 first_connect,
841 last_connect,
842 created_by,
843 creation_date,
844 last_updated_by,
845 last_update_date,
846 last_update_login,
847 date_format_mask,
848 nls_numeric_characters,
849 nls_date_language,
850 nls_sort,
851 nls_territory,
852 disabled_flag,
853 node_id,
854 login_id,
855 mac_key,
856 enc_key,
857 TIME_OUT,
858 GUEST,
859 xsid)
860 = ( select
861 p_user_id,
862 nvl(p_mode_code,l_mode_code),
863 l_org_id,
864 fnd_session_management.g_security_group_id,
865 NULL,
866 p_home_url,
867 l_language,
868 l_language_code,
869 l_limit_time,
870 l_limit_connects,
871 0,
872 sysdate,
873 sysdate,
874 p_user_id,
875 sysdate,
876 p_user_id,
877 sysdate,
878 p_user_id,
879 l_date_format,
880 l_numeric_characters,
881 l_date_language,
882 l_nls_sort,
883 l_nls_territory,
884 'N',
885 l_node_id,
886 l_login_id,
887 NVL(l_mac_key, mac_key),
888 NVL(l_enc_key, enc_key),
889 l_timeout,
890 'N',
891 l_XSID from dual) -- Updating XSID when GUEST session is upgraded to user session
892 where xsid = p_session_id;
893
894 --Bug 7174340 newSessionRaiseEvent(p_user_id,p_session_id);
895 newSessionRaiseEvent(p_user_id,l_session_id);
896 newSSOSession(p_user_id,l_session_id);
897
898 commit;
899 return 'Y';
900 end;
901
902 function createTransaction(p_session_id in number,
903 p_resp_appl_id in number,
904 p_responsibility_id in number,
905 p_security_group_id in number,
906 p_menu_id in number,
907 p_function_id in number,
908 p_function_type in varchar2,
909 p_page_id in number)
910 return number is
911
912 l_transaction_id number;
913 l_XTID varchar2(32);
914
915 begin
916
917 l_transaction_id := NewTransactionId(p_session_id);
918 l_XTID := NewXTID;
919
920 insert into icx_transactions (
921 TRANSACTION_ID,
922 SESSION_ID,
923 RESPONSIBILITY_APPLICATION_ID,
924 RESPONSIBILITY_ID,
925 SECURITY_GROUP_ID,
926 MENU_ID,
927 FUNCTION_ID,
928 FUNCTION_TYPE,
929 PAGE_ID,
930 LAST_CONNECT,
931 DISABLED_FLAG,
932 CREATED_BY,
933 CREATION_DATE,
934 LAST_UPDATED_BY,
935 LAST_UPDATE_DATE,
936 XTID)
937 values (
938 l_transaction_id,
939 p_session_id,
940 p_resp_appl_id,
941 p_responsibility_id,
942 p_security_group_id,
943 p_menu_id,
944 p_function_id,
945 p_function_type,
946 p_page_id,
947 sysdate,
948 'N',
949 fnd_session_management.g_user_id,
950 sysdate,
951 fnd_session_management.g_user_id,
952 sysdate,
953 l_XTID);
954
955 return l_transaction_id;
956
957 exception
958 when others then
959 return -1;
960 end createTransaction;
961
962
963 procedure removeTransaction(p_transaction_id in number) is
964
965 begin
966
967 update ICX_TRANSACTIONS
968 set DISABLED_FLAG = 'Y'
969 where TRANSACTION_ID = p_transaction_id;
970
971 end removeTransaction;
972
973 procedure setSessionPrivate(p_user_id in number,
974 p_responsibility_id in number,
975 p_resp_appl_id in number,
976 p_security_group_id in number,
977 p_date_format in varchar2,
978 p_language in varchar2,
979 p_date_language in varchar2,
980 p_numeric_characters in varchar2,
981 p_nls_sort in varchar2,
982 p_nls_territory in varchar2,
983 p_node_id in number) is
984
985 x_session NUMBER;
986 c_node_id number;
987
988 begin
989
990 if p_node_id is null
991 then
992 select node_id into c_node_id from icx_sessions
993 where session_id = g_session_id;
994 else
995 c_node_id := p_node_id;
996 end if;
997
998 fnd_global.bless_next_init('FND_PERMIT_0001');
999 fnd_global.INITIALIZE(session_id => x_session,
1000 user_id => p_user_id,
1001 resp_id => p_responsibility_id,
1002 resp_appl_id => p_resp_appl_id,
1003 security_group_id => p_security_group_id,
1004 site_id => -1,
1005 login_id => fnd_session_management.g_login_id,
1006 conc_login_id => -1,
1007 prog_appl_id => fnd_session_management.g_prog_appl_id,
1008 conc_program_id => -1,
1009 conc_request_id => -1,
1010 server_id => c_node_id,
1011 conc_priority_request => -1);
1012 --g_prog_appl_id defaults to -1... if -999 fnd_global will verify user_id - resp_id relationship
1013
1014 /* 3152313, remove NLS caching in icx layer
1015 if p_language is not null
1016 and nvl(g_language_c,'XXXXX') <> p_language
1017 then
1018 c_nls_language := p_language;
1019 g_language_c:=p_language;
1020 end if;
1021
1022 if p_date_language is not null
1023 and nvl(g_date_language_c,'XXXXX') <> p_date_language
1024 then
1025 c_date_language := p_date_language;
1026 g_date_language_c:= p_date_language;
1027 end if;
1028
1029 if p_nls_sort is not null
1030 and nvl(g_nls_sort_c,'XXXXX') <> p_nls_sort
1031 then
1032 c_nls_sort := p_nls_sort;
1033 g_nls_sort_c:= p_nls_sort;
1034 end if;
1035
1036 if p_nls_territory is not null
1037 and nvl(g_nls_territory_c,'XXXXX') <> p_nls_territory
1038 then
1039 c_nls_territory := p_nls_territory;
1040 g_nls_territory_c := p_nls_territory;
1041 end if;
1042
1043 if p_date_format is not null
1044 and nvl(g_date_format_c,'XXXXX') <> p_date_format
1045 then
1046 c_date_format := p_date_format;
1047 g_date_format_c := p_date_format;
1048 end if;
1049
1050 if p_numeric_characters IS NOT NULL
1051 and nvl(g_numeric_characters_c,'XXXXX') <> p_numeric_characters
1052 then
1053 c_numeric_characters := p_numeric_characters;
1054 g_numeric_characters_c := p_numeric_characters;
1055 end if;
1056 */
1057
1058 FND_GLOBAL.set_nls_context(
1059 p_nls_language => p_language,
1060 p_nls_date_format => p_date_format,
1061 p_nls_date_language => p_date_language,
1062 p_nls_numeric_characters => p_numeric_characters,
1063 p_nls_sort => p_nls_sort,
1064 p_nls_territory => p_nls_territory);
1065
1066 end setSessionPrivate;
1067
1068
1069 procedure initializeSSWAGlobals(p_session_id in number,
1070 p_transaction_id in number,
1071 p_resp_appl_id in number,
1072 p_responsibility_id in number,
1073 p_security_group_id in number,
1074 p_function_id in number) is
1075
1076 l_multi_org_flag varchar2(30);
1077 l_profile_defined boolean;
1078 l_prefix varchar2(30);
1079
1080
1081 begin
1082
1083 select SESSION_ID,
1084 MODE_CODE,
1085 NLS_LANGUAGE,
1086 LANGUAGE_CODE,
1087 DATE_FORMAT_MASK,
1088 NLS_NUMERIC_CHARACTERS,
1089 NLS_DATE_LANGUAGE,
1090 NLS_SORT,
1091 NLS_TERRITORY,
1092 USER_ID,
1093 nvl(p_resp_appl_id,RESPONSIBILITY_APPLICATION_ID),
1094 nvl(p_security_group_id,SECURITY_GROUP_ID),
1095 nvl(p_responsibility_id,RESPONSIBILITY_ID),
1096 nvl(p_function_id,FUNCTION_ID),
1097 FUNCTION_TYPE,
1098 MENU_ID,
1099 PAGE_ID,
1100 MODE_CODE,
1101 LOGIN_ID,
1102 NODE_ID,
1103 MAC_KEY,
1104 ENC_KEY,
1105 nvl(PROXY_USER_ID, -1)
1106 into fnd_session_management.g_session_id,
1107 fnd_session_management.g_session_mode,
1108 fnd_session_management.g_language,
1109 fnd_session_management.g_language_code,
1110 fnd_session_management.g_date_format,
1111 fnd_session_management.g_numeric_characters,
1112 fnd_session_management.g_date_language,
1113 fnd_session_management.g_nls_sort,
1114 fnd_session_management.g_nls_territory,
1115 fnd_session_management.g_user_id,
1116 fnd_session_management.g_resp_appl_id,
1117 fnd_session_management.g_security_group_id,
1118 fnd_session_management.g_responsibility_id,
1119 fnd_session_management.g_function_id,
1120 fnd_session_management.g_function_type,
1121 fnd_session_management.g_menu_id,
1122 fnd_session_management.g_page_id,
1123 fnd_session_management.g_mode_code,
1124 fnd_session_management.g_login_id,
1125 fnd_session_management.g_node_id,
1126 fnd_session_management.g_mac_key,
1127 fnd_session_management.g_enc_key,
1128 fnd_session_management.g_proxy_user_id
1129 from ICX_SESSIONS
1130 where SESSION_ID = p_session_id;
1131
1132 if fnd_session_management.g_language_code is null
1133 then
1134 select language_code
1135 into fnd_session_management.g_language_code
1136 from fnd_languages
1137 where nls_language = fnd_session_management.g_language;
1138 end if;
1139
1140 if p_transaction_id is not null
1141 then
1142
1143 select TRANSACTION_ID,
1144 nvl(p_resp_appl_id,RESPONSIBILITY_APPLICATION_ID),
1145 nvl(p_responsibility_id,RESPONSIBILITY_ID),
1146 nvl(p_security_group_id,SECURITY_GROUP_ID),
1147 MENU_ID,
1148 nvl(p_function_id,FUNCTION_ID),
1149 FUNCTION_TYPE,
1150 PAGE_ID
1151 into fnd_session_management.g_transaction_id,
1152 fnd_session_management.g_resp_appl_id,
1153 fnd_session_management.g_responsibility_id,
1154 fnd_session_management.g_security_group_id,
1155 fnd_session_management.g_menu_id,
1156 fnd_session_management.g_function_id,
1157 fnd_session_management.g_function_type,
1158 fnd_session_management.g_page_id
1159 from ICX_TRANSACTIONS
1160 where TRANSACTION_ID = p_transaction_id
1161 and SESSION_ID = p_session_id
1162 and DISABLED_FLAG <> 'Y';
1163
1164 end if;
1165
1166 --Bug 3495818
1167 /*
1168 select multi_org_flag
1169 into l_multi_org_flag
1170 from fnd_product_groups
1171 where rownum < 2;
1172 */
1173 l_multi_org_flag := MO_UTILS.Get_Multi_Org_Flag;
1174
1175 if l_multi_org_flag = 'Y'
1176 then
1177 fnd_profile.get_specific
1178 (name_z => 'ORG_ID',
1179 responsibility_id_z => fnd_session_management.g_responsibility_id,
1180 application_id_z => fnd_session_management.g_resp_appl_id,
1181 val_z => fnd_session_management.g_org_id,
1182 defined_z => l_profile_defined);
1183 end if;
1184
1185 fnd_profile.get(name => 'ICX_PREFIX',
1186 val => l_prefix);
1187
1188 if (l_prefix IS NOT NULL)
1189 then
1190 fnd_session_management.g_OA_HTML := fnd_web_config.trail_slash(l_prefix)||'OA_HTML';
1191 fnd_session_management.g_OA_MEDIA := fnd_web_config.trail_slash(l_prefix)||'OA_MEDIA';
1192 else
1193 fnd_session_management.g_OA_HTML := 'OA_HTML';
1194 fnd_session_management.g_OA_MEDIA := 'OA_MEDIA';
1195 end if;
1196
1197 icx_sec.g_session_id := fnd_session_management.g_session_id;
1198 icx_sec.g_language := fnd_session_management.g_language;
1199 icx_sec.g_language_code := fnd_session_management.g_language_code;
1200 icx_sec.g_date_format := fnd_session_management.g_date_format;
1201 icx_sec.g_numeric_characters := fnd_session_management.g_numeric_characters;
1202 icx_sec.g_date_language := fnd_session_management.g_date_language;
1203 icx_sec.g_nls_sort := fnd_session_management.g_nls_sort;
1204 icx_sec.g_nls_territory := fnd_session_management.g_nls_territory;
1205 icx_sec.g_user_id := fnd_session_management.g_user_id;
1206 icx_sec.g_resp_appl_id := fnd_session_management.g_resp_appl_id;
1207 icx_sec.g_security_group_id := fnd_session_management.g_security_group_id;
1208 icx_sec.g_responsibility_id := fnd_session_management.g_responsibility_id;
1209 icx_sec.g_function_id := fnd_session_management.g_function_id;
1210 icx_sec.g_function_type := fnd_session_management.g_function_type;
1211 icx_sec.g_menu_id := fnd_session_management.g_menu_id;
1212 icx_sec.g_page_id := fnd_session_management.g_page_id;
1213 icx_sec.g_mode_code := fnd_session_management.g_mode_code;
1214 icx_sec.g_login_id := fnd_session_management.g_login_id;
1215 icx_sec.g_org_id := fnd_session_management.g_org_id;
1216 icx_sec.g_OA_HTML := fnd_session_management.g_OA_HTML;
1217 icx_sec.g_OA_MEDIA := fnd_session_management.g_OA_MEDIA;
1218
1219 -- Bug 3665024
1220 icx_sec.g_transaction_id := fnd_session_management.g_transaction_id;
1221
1222 end initializeSSWAGlobals;
1223
1224
1225 function validateSessionPrivate( c_XSID in varchar2,
1226 c_function_code in varchar2,
1227 c_commit in boolean,
1228 c_update in boolean,
1229 c_responsibility_id in number,
1230 c_function_id in number,
1231 c_resp_appl_id in number,
1232 c_security_group_id in number,
1233 c_validate_mode_on in varchar2,
1234 c_XTID in varchar2,
1235 session_id out NOCOPY number,
1236 transaction_id out NOCOPY number,
1237 user_id out NOCOPY number,
1238 responsibility_id out NOCOPY number,
1239 resp_appl_id out NOCOPY number,
1240 security_group_id out NOCOPY number,
1241 language_code out NOCOPY varchar2,
1242 nls_language out NOCOPY varchar2,
1243 date_format_mask out NOCOPY varchar2,
1244 nls_date_language out NOCOPY varchar2,
1245 nls_numeric_characters out NOCOPY varchar2,
1246 nls_sort out NOCOPY varchar2,
1247 nls_territory out NOCOPY varchar2)
1248 return varchar2 is
1249
1250 l_result varchar2(30);
1251 l_session_id number;
1252 l_transaction_id number;
1253
1254 p_session_id number;
1255
1256 begin
1257
1258 -- Allow easier performance tuning
1259 /* Request to remove aalomari 16-NOV-1999
1260 DBMS_APPLICATION_INFO.SET_MODULE(
1261 module_name => fnd_session_management.g_function_id,
1262 action_name => 'Self Service');
1263 */
1264
1265
1266 BEGIN
1267
1268 l_session_id := fnd_session_utilities.XSID_to_SessionID(c_XSID);
1269
1270 exception
1271 when others
1272 then
1273 return ('INVALID');
1274 end;
1275
1276
1277 if c_XTID is not null
1278 then
1279 l_transaction_id := fnd_session_utilities.XTID_to_TransactionID(c_XTID);
1280 end if;
1281
1282 if c_validate_mode_on = 'Y'
1283 then
1284 l_result := fnd_session_management.check_session
1285 (p_session_id => l_session_id,
1286 p_resp_id => c_responsibility_id,
1287 p_app_resp_id => c_resp_appl_id,
1288 p_tickle => 'N');
1289 else
1290 l_result := 'VALID';
1291 end if;
1292
1293 if l_result = 'VALID' or l_result = 'EXPIRED'
1294 then
1295
1296 fnd_session_management.initializeSSWAGlobals
1297 (p_session_id => l_session_id,
1298 p_transaction_id => l_transaction_id,
1299 p_resp_appl_id => c_resp_appl_id,
1300 p_responsibility_id => c_responsibility_id,
1301 p_security_group_id => c_security_group_id,
1302 p_function_id => c_function_id);
1303
1304 fnd_session_management.setSessionPrivate
1305 (fnd_session_management.g_user_id,
1306 fnd_session_management.g_responsibility_id,
1307 fnd_session_management.g_resp_appl_id,
1308 fnd_session_management.g_security_group_id,
1309 fnd_session_management.g_date_format,
1310 fnd_session_management.g_language,
1311 fnd_session_management.g_date_language,
1312 fnd_session_management.g_numeric_characters,
1313 fnd_session_management.g_nls_sort,
1314 fnd_session_management.g_nls_territory,
1315 fnd_session_management.g_node_id);
1316
1317 session_id := fnd_session_management.g_session_id;
1318 transaction_id := fnd_session_management.g_transaction_id;
1319 user_id := fnd_session_management.g_user_id;
1320 responsibility_id := fnd_session_management.g_responsibility_id;
1321 resp_appl_id := fnd_session_management.g_resp_appl_id;
1322 security_group_id := fnd_session_management.g_security_group_id;
1323 language_code := fnd_session_management.g_language_code;
1324 nls_language := fnd_session_management.g_language;
1325 date_format_mask := fnd_session_management.g_date_format;
1326 nls_date_language := fnd_session_management.g_date_language;
1327 nls_numeric_characters := fnd_session_management.g_numeric_characters;
1328 nls_sort := fnd_session_management.g_nls_sort;
1329 nls_territory := fnd_session_management.g_nls_territory;
1330
1331
1332 p_session_id := fnd_session_management.g_session_id;
1333
1334 if l_result = 'VALID'
1335 then
1336 if (c_update) or (c_commit)
1337 then
1338
1339 validateSession_pragma(p_session_id);
1340
1341 end if;
1342
1343
1344 /* Bug 3634632 removed commit - call validateSession_pragma now.
1345
1346 update icx_sessions
1347 set last_connect = sysdate,
1348 counter = counter + 1
1349 where session_id = fnd_session_management.g_session_id;
1350
1351 if c_commit
1352 then
1353 commit;
1354 end if;
1355 end if;
1356 */
1357
1358
1359 if c_function_code is not null
1360 then
1361 if (not FND_FUNCTION.TEST(c_function_code))
1362 then
1363 l_result := 'INVALID';
1364 end if;
1365 -- bug 3422198
1366 elsif (fnd_session_management.g_function_id is not null) and
1367 (fnd_session_management.g_function_id <> -1)
1368 then
1369 if (not FND_FUNCTION.TEST_ID(fnd_session_management.g_function_id))
1370 then
1371 l_result := 'INVALID';
1372 end if;
1373 end if;
1374 end if; -- 'VALID'
1375
1376 else -- l_result not valid
1377 session_id := -1;
1378 transaction_id := -1;
1379 user_id := '';
1380 responsibility_id := '';
1381 resp_appl_id := '';
1382 security_group_id := '';
1383 language_code := '';
1384 nls_language := '';
1385 date_format_mask := '';
1386 nls_date_language := '';
1387 nls_numeric_characters := '';
1388 nls_sort := '';
1389 nls_territory := '';
1390 end if; -- l_result = 'VALID'
1391
1392 return l_result;
1393
1394 end validateSessionPrivate;
1395
1396
1397 PROCEDURE Session_tickle_PVT(p_session_id IN NUMBER)
1398 is
1399 PRAGMA AUTONOMOUS_TRANSACTION; -- mputman added for 2233089
1400
1401 begin
1402
1403 update icx_sessions
1404 set last_connect = sysdate
1405 where session_id = p_session_id;
1406 commit;
1407
1408 end Session_tickle_PVT;
1409
1410
1411 PROCEDURE Session_tickle2_PVT(p_session_id IN NUMBER)
1412 is
1413
1414 begin
1415
1416 update icx_sessions
1417 set last_connect = sysdate
1418 where session_id = p_session_id;
1419 commit;
1420
1421 end Session_tickle2_PVT;
1422
1423
1424 PROCEDURE validateSession_pragma(p_session_id IN NUMBER)
1425 is
1426 PRAGMA AUTONOMOUS_TRANSACTION;
1427 begin
1428
1429 update icx_sessions
1430 set last_connect = sysdate,
1431 counter = counter + 1
1432 where session_id = p_session_id;
1433 commit;
1434
1435
1436 end validateSession_pragma;
1437
1438
1439 --
1440 -- procedure added for bug#3951647
1441 --
1442 procedure Session_update_timeout_pvt(p_session_id number, l_timeout number) is
1443 pragma autonomous_transaction;
1444
1445 begin
1446
1447 update icx_sessions set time_out = l_timeout where session_id = p_session_id;
1448 commit;
1449
1450 end Session_update_timeout_pvt;
1451
1452
1453
1454 FUNCTION CHECK_SESSION(p_session_id IN NUMBER,
1455 p_resp_id IN NUMBER,
1456 p_app_resp_id IN NUMBER,
1457 p_tickle IN VARCHAR2)
1458 RETURN VARCHAR2 is
1459
1460 e_exceed_limit exception;
1461 e_session_invalid exception;
1462 n_limit_connects number;
1463 n_limit_time number;
1464 n_counter number;
1465 c_disabled_flag varchar2(1);
1466 c_text varchar2(80);
1467 c_display_error varchar2(240);
1468 c_error_msg varchar2(2000);
1469 c_login_msg varchar2(2000);
1470 n_error_num number;
1471 l_string varchar2(100);
1472 d_first_connect_time date;
1473 l_profile_defined boolean;
1474 l_session_mode varchar2(30);
1475 l_last_connect DATE;--mputman added 1755317
1476 l_session_timeout NUMBER;--mputman added 1755317
1477 l_dist varchar2(30);
1478 l_user_id NUMBER;
1479 l_app_resp_id NUMBER;
1480 l_resp_id NUMBER;
1481 l_guest varchar2(30);
1482 l_timeout number; --gjimenez added 3951647
1483
1484 begin
1485
1486 begin
1487
1488 select LIMIT_CONNECTS, LIMIT_TIME,
1489 FIRST_CONNECT, COUNTER,
1490 nvl(DISABLED_FLAG,'N'),
1491 LAST_CONNECT, user_id,
1492 nvl(p_resp_id,RESPONSIBILITY_ID),
1493 nvl(p_app_resp_id,RESPONSIBILITY_APPLICATION_ID),
1494 TIME_OUT, GUEST, DISTRIBUTED
1495 into n_limit_connects, n_limit_time,
1496 d_first_connect_time,n_counter,
1497 c_disabled_flag,
1498 l_last_connect, l_user_id,
1499 l_resp_id, l_app_resp_id,
1500 l_session_timeout, l_guest, l_dist
1501 from ICX_SESSIONS
1502 where SESSION_ID = p_session_id;
1503
1504 exception
1505 when no_data_found
1506 then
1507 return ('INVALID');
1508 end;
1509
1510 if (c_disabled_flag = 'Y') then
1511 raise e_session_invalid;
1512 end if;
1513
1514 if l_guest = 'N'
1515 then
1516 if ((n_counter + 1) > n_limit_connects)
1517 or (( d_first_connect_time + n_limit_time/24 < sysdate))
1518 then
1519 raise e_exceed_limit;
1520 end if;
1521
1522 IF (l_session_timeout ) IS NOT NULL AND (l_session_timeout > 0) THEN
1523 IF (((SYSDATE-l_last_connect)*24*60)> l_session_timeout ) THEN
1524 RAISE e_exceed_limit;
1525 END IF;
1526 END IF;
1527 end if;
1528
1529 if p_tickle = 'Y' then
1530 -- nlbarlow 2847057
1531 if l_dist = 'Y'
1532 then
1533 Session_tickle2_PVT(p_session_id);
1534 else
1535 Session_tickle_PVT(p_session_id);--moved to after idle check.
1536 end if;
1537 end if;
1538
1539 -- Bug 5354477 amgonzal
1540 -- Finding first new possible ICX_SESSION_TIMEOUT value
1541 /*
1542 -- added changes for bug#3951647
1543
1544 fnd_profile.get(name => 'ICX_SESSION_TIMEOUT',
1545 val => l_timeout);
1546 Session_update_timeout_pvt(p_session_id, l_timeout);
1547
1548 -- end changes for bug #3951647
1549 */
1550
1551 -- Bug 6032403
1552 -- Most of the times fnd_session_management.check_session is called with no
1553 -- values for p_resp_id and p_app_resp_id
1554 -- Then, if passed p_resp_id and p_app_resp_id the ICX_SESSION_TIMEOUT
1555 -- value returned will the one defined for the USER or for the SITE
1556 -- Calling fnd_profile.get_specific with the resp_id and app_resp_id
1557 -- taken from ICX_SESSIONS given the session_id.
1558 -- AMGONZAL
1559 l_profile_defined := false;
1560 fnd_profile.get_specific(
1561 name_z => 'ICX_SESSION_TIMEOUT',
1562 user_id_z => l_user_id,
1563 responsibility_id_z => l_resp_id,
1564 application_id_z => l_app_resp_id,
1565 val_z => l_timeout,
1566 defined_z => l_profile_defined);
1567 if ( l_user_id = 6) then -- Guest user has special rules for timeout.
1568 l_timeout := l_session_timeout;
1569 end if;
1570 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1571 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
1572 , 'fnd.plsql.FND_SESSION_MANAGEMENT.check_session.timeout'
1573 , 'timeout : ' || to_char(l_timeout) || ' User Id : ' || to_char (l_user_id)
1574 || ' Resp ID: ' || to_char(l_resp_id)
1575 || ' Resp app ID : ' || to_char(l_app_resp_id));
1576 end if;
1577 Session_update_timeout_pvt(p_session_id, l_timeout);
1578
1579 return ('VALID');
1580
1581 exception
1582 when e_session_invalid
1583 then
1584 return ('INVALID');
1585 when e_exceed_limit
1586 then
1587 return ('EXPIRED');
1588
1589 -- when others
1590 -- then
1591 -- return ('ERROR');
1592 end CHECK_SESSION;
1593
1594
1595 function getID(n_param in number,
1596 p_session_id in number)
1597 return varchar2 is
1598
1599 n_id varchar2(80) default NULL;
1600 n_user_name varchar2(80);
1601
1602 begin
1603
1604 if n_param = PV_LANGUAGE_CODE --** LANGUAGE CODE (21) **
1605 then
1606 n_id := fnd_session_management.g_language_code; -- add to Java login.
1607
1608 elsif n_param = PV_RESPONSIBILITY_ID --** RESPONSIBILITY ID (25) **
1609 then
1610 n_id := fnd_session_management.g_responsibility_id;
1611
1612 elsif n_param = PV_FUNCTION_ID --** FUNCTION ID (31) **
1613 then
1614 n_id := fnd_session_management.g_function_id;
1615
1616 elsif n_param = PV_FUNCTION_TYPE --** FUNCTION TYPE (32) **
1617 then
1618 n_id := fnd_session_management.g_function_type;
1619
1620 elsif n_param = PV_USER_NAME --** USERNAME (99) **
1621 then
1622 select b.USER_NAME
1623 into n_id
1624 from icx_sessions a,
1625 fnd_user b
1626 where b.user_id = a.user_id
1627 and a.session_id = p_session_id;
1628
1629 elsif n_param = PV_USER_ID --** WEB USER ID (10) **
1630 then
1631 n_id := fnd_session_management.g_user_id;
1632
1633 elsif n_param = PV_DATE_FORMAT --** DATE FORMAT MASK (22) **
1634 then
1635 n_id := fnd_session_management.g_date_format;
1636
1637 elsif n_param = PV_SESSION_ID -- ** SESSION_ID (23) **
1638 then
1639 n_id := p_session_id;
1640
1641 elsif n_param = PV_ORG_ID -- ** ORG_ID (29) **
1642 then
1643 n_id := fnd_session_management.g_org_id;
1644
1645 elsif n_param = PV_SESSION_MODE --** PV_SESSION_MODE (30) **
1646 then
1647 n_id := fnd_session_management.g_session_mode;
1648
1649 end if;
1650
1651 return(n_id);
1652
1653 exception
1654 when others then
1655 return '-1';
1656 end;
1657
1658
1659 procedure putSessionAttributeValue(p_name in varchar2,
1660 p_value in varchar2,
1661 p_session_id in number) is
1662 pragma AUTONOMOUS_TRANSACTION;
1663 l_name varchar2(80);
1664 l_len number;
1665
1666 begin
1667
1668 -- substr added for bug3282584 - MSkees
1669 -- we truncate from the right as requested by OA FWK - GKellner
1670 l_len := LENGTH( p_name );
1671 if ( l_len > 30 ) then
1672 -- substr() hass a base 1 index so use 29 to get new start
1673 l_name := substr( upper(p_name), (l_len - 29), l_len);
1674 else
1675 -- bug 3296747 forgot the else ...
1676 l_name := upper(p_name);
1677 end if;
1678
1679 delete ICX_SESSION_ATTRIBUTES
1680 where SESSION_ID = p_session_id
1681 and NAME = l_name;
1682
1683 insert into ICX_SESSION_ATTRIBUTES
1684 (SESSION_ID,NAME,VALUE)
1685 values
1686 (p_session_id,l_name,p_value);
1687 commit;
1688
1689 end putSessionAttributeValue;
1690
1691 function getSessionAttributeValue(p_name in varchar2,
1692 p_session_id in number)
1693 return varchar2 is
1694 l_name varchar2(80);
1695 l_value varchar2(4000);
1696 l_len number;
1697
1698 begin
1699
1700 -- substr added for bug3282584 - MSkees
1701 -- we truncate from the right as requested by OA FWK - GKellner
1702 l_len := LENGTH( p_name );
1703 if ( l_len > 30 ) then
1704 -- substr() hass a base 1 index so use 29 to get new start
1705 l_name := substr( upper(p_name), (l_len - 29), l_len);
1706 else
1707 -- bug 3296747 forgot the else ...
1708 l_name := upper(p_name);
1709 end if;
1710
1711 select VALUE
1712 into l_value
1713 from ICX_SESSION_ATTRIBUTES
1714 where SESSION_ID = p_session_id
1715 and NAME = l_name;
1716
1717 return l_value;
1718
1719 exception
1720 when others then
1721 return NULL;
1722 end getSessionAttributeValue;
1723
1724 procedure clearSessionAttributeValue(p_name in varchar2,
1725 p_session_id in number) is
1726
1727 PRAGMA AUTONOMOUS_TRANSACTION; --(gjimenez -> bug#4671867)
1728
1729 l_name varchar2(80);
1730 l_len number;
1731
1732 begin
1733
1734 -- substr added for bug3282584 - MSkees
1735 -- we truncate from the right as requested by OA FWK - GKellner
1736 l_len := LENGTH( p_name );
1737 if ( l_len > 30 ) then
1738 -- substr() hass a base 1 index so use 29 to get new start
1739 l_name := substr( upper(p_name), (l_len - 29), l_len);
1740 else
1741 -- bug 3296747 forgot the else ...
1742 l_name := upper(p_name);
1743 end if;
1744
1745 delete ICX_SESSION_ATTRIBUTES
1746 where SESSION_ID = p_session_id
1747 and NAME = l_name;
1748
1749 -- Fix for bug#5326396 -- Added a commit and exception handling to the code.
1750 commit;
1751
1752 exception
1753 when others then
1754 rollback;
1755
1756
1757 end clearSessionAttributeValue;
1758
1759
1760 function getsessioncookiename return varchar2 is
1761
1762 l_session_cookie_name varchar2(81);
1763
1764 begin
1765
1766 IF fnd_session_management.g_session_cookie_name IS NULL THEN
1767
1768 select SESSION_COOKIE_NAME
1769 into l_session_cookie_name
1770 from ICX_PARAMETERS;
1771 ELSE
1772 l_session_cookie_name:=fnd_session_management.g_session_cookie_name;
1773 END IF; -- added mputman 1574527
1774
1775 if (l_session_cookie_name is null) then
1776 l_session_cookie_name := FND_WEB_CONFIG.DATABASE_ID;
1777 end if;
1778
1779 return l_session_cookie_name;
1780
1781 exception
1782 when others then
1783 return -1;
1784 end getsessioncookiename;
1785
1786
1787 procedure updateSessionContext( p_function_name varchar2,
1788 p_function_id number,
1789 p_application_id number,
1790 p_responsibility_id number,
1791 p_security_group_id number,
1792 p_session_id number,
1793 p_transaction_id number)
1794 is
1795 PRAGMA AUTONOMOUS_TRANSACTION; --bug#5030523
1796
1797 l_function_id number;
1798 l_function_type varchar2(30);
1799 l_multi_org_flag varchar2(30);
1800 l_org_id number;
1801 l_profile_defined boolean;
1802
1803 l_user_id number;
1804 l_new_timeout number;
1805 l_prev_timeout number;
1806 l_timeout number;
1807
1808 begin
1809
1810 if p_function_id is null and p_function_name is not null
1811 then
1812 select FUNCTION_ID, TYPE
1813 into l_function_id, l_function_type
1814 from FND_FORM_FUNCTIONS
1815 where FUNCTION_NAME = p_function_name;
1816 elsif p_function_name is null and p_function_id is not null
1817 then
1818 select FUNCTION_ID, TYPE
1819 into l_function_id, l_function_type
1820 from FND_FORM_FUNCTIONS
1821 where FUNCTION_ID = p_function_id;
1822 else
1823 l_function_id := '';
1824 l_function_type := '';
1825 end if;
1826
1827 --Bug 3495818
1828 /*
1829 select multi_org_flag
1830 into l_multi_org_flag
1831 from fnd_product_groups
1832 where rownum < 2;
1833 */
1834 l_multi_org_flag := MO_UTILS.Get_Multi_Org_Flag;
1835
1836 if l_multi_org_flag = 'Y'
1837 then
1838 fnd_profile.get_specific(
1839 name_z => 'ORG_ID',
1840 responsibility_id_z => p_responsibility_id,
1841 application_id_z => p_application_id,
1842 val_z => l_org_id,
1843 defined_z => l_profile_defined);
1844 end if;
1845
1846 --
1847 -- Bug 5354477 amgonzal
1848 -- Finding the possible new value for ICX_SESSION_TIMEOUT profile option
1849 --
1850 --
1851 Begin
1852 Select user_id, time_out
1853 into l_user_id, l_prev_timeout
1854 from icx_sessions
1855 where session_id = p_session_id;
1856
1857
1858 fnd_profile.get_specific(
1859 name_z => 'ICX_SESSION_TIMEOUT',
1860 user_id_z => l_user_id,
1861 responsibility_id_z => p_responsibility_id,
1862 application_id_z => p_application_id,
1863 val_z => l_new_timeout,
1864 defined_z => l_profile_defined);
1865
1866 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1867 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
1868 , 'fnd.plsql.FND_SESSION_MANAGEMENT.updateSessionContext.timeout'
1869 , 'Previous timeout : ' || to_char(l_prev_timeout)
1870 || 'New Timeout ' || to_char (l_new_timeout)
1871 || ' Resp : ' || to_char(p_responsibility_id)
1872 || ' Apps id: ' || to_char (p_application_id));
1873 end if;
1874
1875
1876
1877 if l_user_id <> 6 then
1878 l_timeout := l_new_timeout;
1879 else
1880 l_timeout := l_prev_timeout;
1881 end if;
1882 End;
1883
1884 update ICX_SESSIONS
1885 set RESPONSIBILITY_APPLICATION_ID = p_application_id,
1886 RESPONSIBILITY_ID = p_responsibility_id,
1887 SECURITY_GROUP_ID = p_security_group_id,
1888 ORG_ID = l_org_id,
1889 FUNCTION_ID = l_function_id,
1890 FUNCTION_TYPE = l_function_type,
1891 time_out = l_timeout
1892 where SESSION_ID = p_session_id;
1893
1894 -- Bug 6032403 : In case next SQL stmt gaves error a
1895 -- rollback will undo icx_sessions update
1896 commit;
1897
1898 if p_transaction_id is not null
1899 then
1900
1901 update ICX_TRANSACTIONS
1902 set RESPONSIBILITY_APPLICATION_ID = p_application_id,
1903 RESPONSIBILITY_ID = p_responsibility_id,
1904 SECURITY_GROUP_ID = p_security_group_id,
1905 FUNCTION_ID = l_function_id,
1906 FUNCTION_TYPE = l_function_type
1907 where TRANSACTION_ID = p_transaction_id
1908 and SESSION_ID = p_session_id;
1909
1910 end if;
1911
1912 commit;
1913
1914 exception
1915
1916 when others then
1917 rollback;
1918
1919 end updateSessionContext;
1920
1921
1922 function getNLS_PARAMETER(p_param in VARCHAR2)
1923 return varchar2 is
1924
1925 requested_val VARCHAR2(255);
1926
1927 BEGIN
1928
1929 select upper(value)
1930 into requested_val
1931 from v$nls_parameters
1932 where parameter = p_param;
1933
1934 RETURN requested_val;
1935
1936 END getNLS_PARAMETER;
1937
1938
1939 PROCEDURE set_session_nls (p_session_id IN NUMBER,
1940 p_language IN VARCHAR2,
1941 p_date_format_mask IN VARCHAR2,
1942 p_language_code IN VARCHAR2,
1943 p_date_language IN VARCHAR2,
1944 p_numeric_characters IN VARCHAR2,
1945 p_sort IN VARCHAR2,
1946 p_territory IN VARCHAR2) IS
1947
1948 BEGIN
1949
1950 UPDATE icx_sessions
1951 SET
1952 NLS_LANGUAGE=p_language,
1953 DATE_FORMAT_MASK=p_date_format_mask,
1954 LANGUAGE_CODE=p_language_code,
1955 NLS_DATE_LANGUAGE=p_date_language,
1956 NLS_NUMERIC_CHARACTERS=p_numeric_characters,
1957 NLS_SORT=p_sort,
1958 NLS_TERRITORY=p_territory
1959 WHERE session_id = p_session_id;
1960
1961 COMMIT;
1962
1963 END set_session_nls;
1964
1965 procedure reset_session(p_session_id in number) is
1966 l_XSID varchar2(32);
1967 begin
1968
1969 -- Session Hijacking fix.
1970 l_XSID := NewXSID;
1971
1972 UPDATE icx_sessions
1973 SET disabled_flag='N',
1974 last_connect=SYSDATE,
1975 counter=0,
1976 first_connect=SYSDATE,
1977 xsid=l_XSID -- Update XSID whenever session is re-established(Session Hijacking)
1978 WHERE session_id = p_session_id;
1979
1980 end;
1981
1982 /*
1983 function newLoginId
1984 return number is
1985
1986 l_login_id number;
1987
1988 begin
1989
1990 select fnd_logins_s.nextval
1991 into l_login_id
1992 from sys.dual;
1993
1994
1995 return(l_login_id);
1996 end;
1997 */
1998
1999
2000
2001 --disableSession is to be used with high availability to
2002 --disable all sessions that are older than the threshold value (mins)
2003 -- added for 2124463
2004 PROCEDURE disableSessions (threshold IN NUMBER)
2005 IS
2006
2007 BEGIN
2008
2009 UPDATE icx_sessions
2010 SET disabled_flag='Y'
2011 WHERE
2012 (((SYSDATE-first_connect)*24*60)> threshold);
2013
2014 COMMIT;
2015 END;
2016
2017 function disableUserSession(c_session_id in number,
2018 c_user_id in number) return BOOLEAN
2019 is
2020
2021 --added for 4230606
2022 l_login_id number;
2023 l_audit_level VARCHAR2(1);
2024 --end modification
2025
2026 begin
2027
2028 --added for 4230606
2029 select login_id into l_login_id
2030 from ICX_SESSIONS
2031 where SESSION_ID = c_session_id;
2032
2033 l_audit_level:=fnd_profile.value('SIGNONAUDIT:LEVEL');
2034 if (l_audit_level is not null) and ( l_login_id is not null)
2035 then
2036 fnd_signon.audit_end(l_login_id); -- end audit session and resps.
2037 end if;
2038 --end changes for 4230606
2039
2040 if c_user_id is null then
2041 update icx_sessions
2042 set disabled_flag = 'Y'
2043 where session_id = c_session_id;
2044 elsif c_user_id is not null then
2045 update icx_sessions
2046 set disabled_flag = 'Y'
2047 where session_id = c_session_id
2048 and user_id = c_user_id;
2049 end if;
2050 COMMIT;
2051
2052 return true;
2053 exception
2054 when others then
2055 return false;
2056 end;
2057
2058
2059 PROCEDURE setUserNLS (p_user_id IN NUMBER,
2060 p_language_code IN varchar2,
2061 l_language OUT NOCOPY varchar2,
2062 l_language_code OUT NOCOPY varchar2,
2063 l_date_format OUT NOCOPY varchar2,
2064 l_date_language OUT NOCOPY varchar2,
2065 l_numeric_characters OUT NOCOPY varchar2,
2066 l_nls_sort OUT NOCOPY varchar2,
2067 l_nls_territory OUT NOCOPY varchar2,
2068 l_limit_time OUT NOCOPY NUMBER,
2069 l_limit_connects OUT NOCOPY NUMBER,
2070 l_org_id OUT NOCOPY varchar2,
2071 l_timeout OUT NOCOPY NUMBER)
2072
2073 IS
2074 l_multi_org_flag varchar2(1);
2075 l_profile_defined boolean;
2076 db_lang varchar2(512);
2077 lang varchar2(255);
2078
2079 l_login_id NUMBER;
2080 l_expired VARCHAR2(5);
2081
2082 l_user_id NUMBER;
2083
2084 begin
2085
2086 if (fnd_session_management.g_proxy_user_id = -1) then
2087 /* For normal session get the NLS settings for the passed in user */
2088 l_user_id := p_user_id;
2089 else
2090 /* For Proxy session carry over the NLS settings from the original user's
2091 session */
2092 l_user_id := fnd_session_management.g_proxy_user_id;
2093 end if;
2094
2095 l_language := null;
2096 if p_language_code is not null
2097 then
2098 begin
2099 select language_code, nls_language
2100 into l_language_code, l_language
2101 from fnd_languages
2102 where installed_flag in ('I', 'B') and
2103 language_code = p_language_code;
2104 exception
2105 when no_data_found
2106 then
2107 l_language := null;
2108 end;
2109 end if;
2110 if l_language is null then
2111 fnd_profile.get_specific(name_z => 'ICX_LANGUAGE',
2112 user_id_z => l_user_id,
2113 val_z => l_language,
2114 defined_z => l_profile_defined);
2115
2116 if l_language is null
2117 then
2118 l_language:=getNLS_PARAMETER('NLS_LANGUAGE');
2119 end if;
2120
2121 select language_code
2122 into l_language_code
2123 from fnd_languages
2124 where nls_language = l_language;
2125 end if;
2126
2127 -- The following Profiles should be set
2128
2129 fnd_profile.get_specific(name_z => 'ICX_NLS_SORT',
2130 user_id_z => l_user_id,
2131 val_z => l_nls_sort,
2132 defined_z => l_profile_defined);
2133
2134 if l_nls_sort is null
2135 then
2136 l_nls_sort:=getNLS_PARAMETER('NLS_SORT');
2137 end if;
2138
2139 fnd_profile.get_specific(name_z => 'ICX_DATE_FORMAT_MASK',
2140 user_id_z => l_user_id,
2141 val_z => l_date_format,
2142 defined_z => l_profile_defined);
2143
2144 if l_date_format is null
2145 then
2146 l_date_format:=getNLS_PARAMETER('NLS_DATE_FORMAT');
2147 end if;
2148
2149 l_date_format := replace(upper(l_date_format), 'YYYY', 'RRRR');
2150 l_date_format := replace(l_date_format, 'YY', 'RRRR');
2151 if (instr(l_date_format, 'RR') > 0) then
2152 if (instr(l_date_format, 'RRRR') = 0) then
2153 l_date_format := replace(l_date_format, 'RR', 'RRRR');
2154 end if;
2155 end if;
2156
2157 -- Bug 5032374: Using unified function in ATG to get the NLS_DATE_LANGUAGE
2158 -- FND_GLOBAL.nls_date_language
2159 -- Changing :
2160 -- l_date_language := getDateLanguage(l_language);
2161 -- By:
2162 l_date_language := FND_GLOBAL.nls_date_language;
2163
2164 if l_date_language is null
2165 then
2166 l_date_language:=getNLS_PARAMETER('NLS_DATE_LANGUAGE');
2167 end if;
2168
2169 fnd_profile.get_specific(name_z => 'ICX_NUMERIC_CHARACTERS',
2170 user_id_z => l_user_id,
2171 val_z => l_numeric_characters,
2172 defined_z => l_profile_defined);
2173
2174 if l_numeric_characters is null
2175 then
2176 l_numeric_characters:=getNLS_PARAMETER('NLS_NUMERIC_CHARACTERS');
2177 end if;
2178
2179 fnd_profile.get_specific(name_z => 'ICX_TERRITORY',
2180 user_id_z => l_user_id,
2181 val_z => l_nls_territory,
2182 defined_z => l_profile_defined);
2183
2184 if l_nls_territory is null
2185 then
2186 l_nls_territory:=getNLS_PARAMETER('NLS_TERRITORY');
2187 end if;
2188
2189 fnd_profile.get_specific(name_z => 'ICX_LIMIT_TIME',
2190 user_id_z => l_user_id,
2191 val_z => l_limit_time,
2192 defined_z => l_profile_defined);
2193
2194 if l_limit_time is null
2195 then
2196 l_limit_time := 4;
2197 end if;
2198
2199 fnd_profile.get_specific(name_z => 'ICX_LIMIT_CONNECT',
2200 user_id_z => l_user_id,
2201 val_z => l_limit_connects,
2202 defined_z => l_profile_defined);
2203
2204 if l_limit_connects is null
2205 then
2206 l_limit_connects := 1000;
2207 end if;
2208 -- Bug 5354477 : Now ICX_SESSION_TIMEOUT is populated on
2209 -- convertGuestSession
2210 -- updateSessionContext
2211 -- check_session
2212 --
2213 /*
2214 fnd_profile.get_specific(name_z => 'ICX_SESSION_TIMEOUT',
2215 user_id_z => p_user_id,
2216 val_z => l_timeout,
2217 defined_z => l_profile_defined);
2218 fnd_profile.get(name => 'ICX_SESSION_TIMEOUT',
2219 val => l_timeout);
2220 */
2221
2222 /*
2223 select multi_org_flag
2224 into l_multi_org_flag
2225 from fnd_product_groups
2226 where rownum < 2;
2227 */
2228 l_multi_org_flag := MO_UTILS.Get_Multi_Org_Flag;
2229
2230 if l_multi_org_flag = 'Y' then
2231 fnd_profile.get_specific(name_z => 'ORG_ID',
2232 val_z => l_org_id,
2233 defined_z => l_profile_defined);
2234 end if;
2235
2236 END;--setUserNLS
2237
2238
2239 function GET_CACHING_KEY(p_reference_path VARCHAR2) return varchar2
2240 is
2241 cachingKey varchar2(55);
2242 begin
2243
2244 select caching_key into cachingKey
2245 from icx_portlet_customizations
2246 where reference_path = p_reference_path;
2247
2248 return cachingKey;
2249
2250 EXCEPTION
2251 WHEN OTHERS THEN
2252 return null;
2253
2254 end GET_CACHING_KEY;
2255
2256 function isProxySession(p_session_id in number)
2257 return number is
2258 user_id number;
2259 begin
2260 if (p_session_id is null)
2261 then
2262 if (fnd_session_management.g_proxy_user_id = -1) then
2263 return NULL;
2264 else
2265 return fnd_session_management.g_proxy_user_id;
2266 end if;
2267 end if;
2268 select proxy_user_id into user_id from icx_sessions where
2269 session_id = p_session_id;
2270 return user_id;
2271 exception
2272 when no_data_found then
2273 app_exception.raise_exception(exception_text=>
2274 'Invalid Session Id ');
2275 app_exception.raise_exception;
2276 when others then
2277 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
2278 fnd_message.set_token('ROUTINE', 'FND_SESSION_MANAGEMENT.isProxySession');
2279 fnd_message.set_token('ERRNO', SQLCODE);
2280 fnd_message.set_token('REASON', SQLERRM);
2281 app_exception.raise_exception;
2282 end isProxySession;
2283
2284 -- *** INTERNAL API to be used by AOL only ***
2285 -- The newSSOSession API is to be used to invalidate/timeout SSO sessions
2286 -- when limiting SSO users to one session in an EBS instance
2287 -- It is similar to the doNewSession API which limits the local ICX
2288 -- sessions.
2289
2290 PROCEDURE newSSOSession (p_user_id IN NUMBER, p_session_id IN NUMBER)
2291 IS
2292 l_module_source varchar2(256) := 'fnd_session_management.newSSOSession';
2293 l_user_id number;
2294 l_session_timeout number :=0;
2295 l_limit_sessions VARCHAR2(1) := 'N';
2296 l_profile_defined boolean;
2297 l_user_guid raw(256);
2298
2299 BEGIN
2300
2301 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2302 then
2303 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Begin');
2304 end if;
2305
2306 if (p_user_id is null) then
2307 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2308 then
2309 fnd_log.string(fnd_log.LEVEL_STATEMENT,l_module_source,'Userid not passed - get from session: '||to_char(p_session_id));
2310 end if;
2311
2312 select user_id, time_out
2313 into l_user_id, l_session_timeout
2314 from icx_sessions
2315 where session_id = p_session_id;
2316
2317 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2318 then
2319 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Userid from session is: '||to_char(l_user_id));
2320 end if;
2321 else
2322 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2323 then
2324 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'User id passed: '||to_char(p_user_id));
2325 end if;
2326
2327 l_user_id := p_user_id;
2328 end if;
2329
2330 if (l_user_id is not null) then
2331 select user_guid into l_user_guid from fnd_user where user_id = l_user_id;
2332
2333 if (l_user_guid is not null) then
2334 -- Could have an SSO session - if so disable if profile is set to limit
2335 fnd_profile.get_specific(name_z => 'APPS_SSO_LIMIT_SESSIONS',
2336 user_id_z => l_user_id,
2337 val_z => l_limit_sessions,
2338 defined_z => l_profile_defined);
2339
2340 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2341 then
2342 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source,'APPS_SSO_LIMIT_SESSIONS profile is '||l_limit_sessions);
2343 end if;
2344
2345 -- Handle disabling of local session on reauth of SSO user. If event is enabled local sessions should be limited
2346 newSessionRaiseEvent(l_user_id,p_session_id);
2347
2348 if (l_limit_sessions = 'Y') then
2349 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2350 then
2351 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Limiting SSO user session. Disable all but session id '||to_char(p_session_id));
2352 end if;
2353
2354 UPDATE icx_sessions
2355 SET last_connect=sysdate-2 -- May need to adjust this value
2356 WHERE mode_code = '115J'
2357 AND session_id <> p_session_id
2358 AND user_id = l_user_id
2359 AND disabled_flag = 'N';
2360
2361 COMMIT;
2362 else
2363 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2364 then
2365 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Not limiting SSO user session - do nothing');
2366 end if;
2367 end if;
2368
2369 else
2370 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2371 then
2372 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'Not an SSO user - do nothing');
2373 end if;
2374 end if;
2375
2376 else
2377 -- Should never get here...
2378 if (fnd_log.LEVEL_STATEMENT >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2379 then
2380 fnd_log.string(fnd_log.LEVEL_STATEMENT, l_module_source, 'No user id found');
2381 end if;
2382
2383 end if;
2384
2385 if (fnd_log.LEVEL_PROCEDURE >= fnd_log.G_CURRENT_RUNTIME_LEVEL)
2386 then
2387 fnd_log.string(fnd_log.LEVEL_PROCEDURE, l_module_source, 'END');
2388 end if;
2389
2390 EXCEPTION WHEN OTHERS THEN
2391 if( fnd_log.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2392 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED , l_module_source, 'Exception:'||sqlcode||' '||sqlerrm);
2393 end if;
2394 raise;
2395 END;
2396
2397
2398
2399 end FND_SESSION_MANAGEMENT;