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