1 package body FND_SIGNON as
2 /* $Header: AFSCSGNB.pls 120.8.12010000.3 2008/11/22 21:37:10 dggriffi ship $ */
3
4
5 --
6 -- GENERIC_ERROR (Internal)
7 --
8 -- Set error message and raise exception for unexpected sql errors.
9 --
10 procedure GENERIC_ERROR(routine in varchar2,
11 errcode in number,
12 errmsg in varchar2) is
13 begin
14 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
15 fnd_message.set_token('ROUTINE', routine);
16 fnd_message.set_token('ERRNO', errcode);
17 fnd_message.set_token('REASON', errmsg);
18 app_exception.raise_exception;
19 end;
20
21 --
22 -- AUDIT_FORM_END (Internal) - Signon audit mark form endtimes
23 --
24 -- Stamp end_time of current record in soa forms table.
25 --
26 procedure AUDIT_FORM_END(login_id in number) is
27 pragma AUTONOMOUS_TRANSACTION;
28 begin
29 if login_id is null then
30 return;
31 end if;
32
33 -- Stamp end time on any current form for this login in FLRF
34 UPDATE FND_LOGIN_RESP_FORMS FLRF
35 SET END_TIME = SYSDATE
36 WHERE FLRF.LOGIN_ID = audit_form_end.login_id
37 AND FLRF.END_TIME is NULL;
38
39 COMMIT;
40 exception
41 when no_data_found then
42 null;
43 end AUDIT_FORM_END;
44
45 --
46 -- AUDIT_RESPONSIBILITY_END (Internal) - Signon audit mark resp endtimes
47 --
48 -- Stamp end_time of current record in soa resps table.
49 --
50 procedure AUDIT_RESPONSIBILITY_END(login_id in number) is
51 pragma AUTONOMOUS_TRANSACTION;
52 begin
53 if login_id is null then
54 return;
55 end if;
56
57 -- Stamp end time on any current resp for this login in FLR
58 UPDATE FND_LOGIN_RESPONSIBILITIES FLR
59 SET END_TIME = SYSDATE
60 WHERE FLR.LOGIN_ID = audit_responsibility_end.login_id
61 AND FLR.END_TIME is NULL;
62
63 -- End any open forms
64 AUDIT_FORM_END(login_id);
65 COMMIT;
66 exception
67 when no_data_found then
68 null;
69 end AUDIT_RESPONSIBILITY_END;
70
71 --
72 -- AUDIT_USER_END (Internal) - Signon audit mark user endtimes
73 --
74 -- Set end_time or current record in soa logins table.
75 --
76 procedure AUDIT_USER_END(login_id in number) is
77 pragma AUTONOMOUS_TRANSACTION;
78 begin
79
80 if login_id is null then
81 return;
82 end if;
83
84 -- Stamp end time on current login in FL
85
86 UPDATE FND_LOGINS FL
87 SET END_TIME = SYSDATE
88 WHERE FL.LOGIN_ID = audit_user_end.login_id;
89
90 -- End any open resps
91 AUDIT_RESPONSIBILITY_END(login_id);
92 COMMIT;
93 exception
94 when no_data_found then
95 null;
96 end AUDIT_USER_END;
97
98 --
99 -- AUDIT_FORM - Signon audit form begin | end
100 --
101 -- If END_FORM
102 -- Stamp end time on current form record.
103 -- If BEGIN_FORM
104 -- Insert new soa record for form level auditing.
105 --
106 procedure AUDIT_FORM(login_id in number,
107 login_resp_id in number,
108 form_application in varchar2,
109 form_name in varchar2,
110 audit_level in varchar2 DEFAULT 'D',
111 begin_flag in number DEFAULT 0) is
112 pragma AUTONOMOUS_TRANSACTION;
113 begin
114 if (begin_flag = 0) then
115 --
116 -- END_FORM call
117 -- Stamp end time on current form record.
118 --
119 begin
120 -- JWSMITH bug 1879642 - added FLRF.END_TIME is null
121 UPDATE FND_LOGIN_RESP_FORMS FLRF
122 SET END_TIME = SYSDATE
123 WHERE FLRF.LOGIN_ID = audit_form.login_id
124 AND FLRF.LOGIN_RESP_ID = audit_form.login_resp_id
125 AND FLRF.END_TIME IS NULL
126 AND (FLRF.FORM_ID, FLRF.FORM_APPL_ID) =
127 (SELECT F.FORM_ID, F.APPLICATION_ID
128 FROM FND_FORM F, FND_APPLICATION A
129 WHERE F.FORM_NAME = audit_form.form_name
130 AND F.APPLICATION_ID = A.APPLICATION_ID
131 AND A.APPLICATION_SHORT_NAME = audit_form.form_application);
132 exception
133 when no_data_found then
134 null;
135 end;
136 else
137 --
138 -- BEGIN_FORM call
139 -- If form level auditing insert new form record.
140 --
141 if (audit_level = 'D') then
142 INSERT INTO FND_LOGIN_RESP_FORMS
143 (LOGIN_ID, LOGIN_RESP_ID, FORM_APPL_ID, FORM_ID, START_TIME,
144 AUDSID)
145 SELECT audit_form.login_id, audit_form.login_resp_id,
146 A.APPLICATION_ID, F.FORM_ID, SYSDATE,
147 userenv('SESSIONID')
148 FROM FND_FORM F, FND_APPLICATION A
149 WHERE F.FORM_NAME = audit_form.form_name
150 AND F.APPLICATION_ID = A.APPLICATION_ID
151 AND A.APPLICATION_SHORT_NAME = audit_form.form_application;
152 end if;
153 end if;
154 COMMIT;
155 exception
156 when others then
157 rollback;
158 generic_error('FND_SIGNON.AUDIT_FORM', SQLCODE, SQLERRM);
159 end AUDIT_FORM;
160
161 --
162 -- AUDIT_RESPONSIBILITY - Signon audit responsibility
163 --
164 -- Insert new soa record for responsibility,
165 -- update pid in soa logins table.
166 --
167 procedure AUDIT_RESPONSIBILITY(audit_level in varchar2,
168 login_id in number,
169 login_resp_id in out nocopy number,
170 resp_appl_id in number,
171 resp_id in number,
172 terminal_id in varchar2,
173 spid in varchar2) is
174 pragma AUTONOMOUS_TRANSACTION;
175 l_pid number;
176 l_serial number;
177 l_spid varchar2(30);
178 begin
179 -- Endstamp any previous resp record in this login.
180 -- This must be done regardless of level in case level was changed
181 -- during login.
182 AUDIT_RESPONSIBILITY_END(login_id);
183
184 --
185 -- Change pid in logins table to reflect pid of current process.
186 -- This must be done when resp changes because a reconnect may
187 -- have caused pid to change.
188 --
189 SELECT P.PID, P.SERIAL#, P.SPID
190 INTO l_pid, l_serial, l_spid
191 FROM V$PROCESS P, V$SESSION S
192 WHERE S.AUDSID = USERENV('SESSIONID')
193 AND S.PADDR = P.ADDR;
194
195 UPDATE FND_LOGINS FL
196 SET PID = l_pid,
197 SERIAL# = l_serial,
198 PROCESS_SPID = l_spid
199 WHERE FL.LOGIN_ID = audit_responsibility.login_id;
200
201 -- If resp/form level auditing insert record for new resp
202 if (audit_level in ('C', 'D')) then
203 --
204 -- Bug 3457883: The unique index FND_LOGIN_RESPONSIBILITIES_U1 is being violated when a
205 -- responsibility is relaunched within an existing ICX session. This was brought about
206 -- by the changes in bug 3043856 wherein the login_id obtained by ICX is not used throughout
207 -- the session. A new sequence, FND_LOGIN_RESPONSIBILITIES_S, is created to generate unique
208 -- values for login_resp_id and will be used when inserting records into
209 -- FND_LOGIN_RESPONSIBILITIES. The login_resp_id generated will be passed back to the API
210 -- calling fnd_signon.audit_responsibility().
211 --
212 -- If login_resp_id is null, then generate one.
213 if (login_resp_id is null) then
214 select FND_LOGIN_RESPONSIBILITIES_S.nextval into login_resp_id from dual;
215 end if;
216
217 INSERT INTO FND_LOGIN_RESPONSIBILITIES
218 (LOGIN_ID, LOGIN_RESP_ID, RESP_APPL_ID, RESPONSIBILITY_ID, START_TIME,
219 AUDSID)
220 VALUES (audit_responsibility.login_id,
221 audit_responsibility.login_resp_id,
222 audit_responsibility.resp_appl_id,
223 audit_responsibility.resp_id, SYSDATE,
224 userenv('SESSIONID'));
225
226 -- If auditing at form level add a new record for the signon
227 -- form under the new responsibility.
228 if (audit_level = 'D') then
229 AUDIT_FORM(login_id, login_resp_id, 'FND', 'FNDSCSGN',
230 audit_level, 1);
231 end if;
232 end if;
233 COMMIT;
234 exception
235 when others then
236 rollback;
237 generic_error('FND_SIGNON.AUDIT_RESPONSIBILITY', SQLCODE, SQLERRM);
238 end AUDIT_RESPONSIBILITY;
239
240 --
241 -- AUDIT_USER - Begin user level signon auditing
242 --
243 -- Insert new soa record for login,
244 -- create new login_id for this signon.
245 --
246 procedure AUDIT_USER(login_id in out nocopy number,
247 audit_level in varchar2,
248 user_id in number,
249 terminal_id in varchar2,
250 login_name in varchar2,
251 spid in varchar2,
252 session_number in number) is
253 pragma AUTONOMOUS_TRANSACTION;
254 local_pid number;
255 local_spid varchar2(30);
256 local_serial# number;
257 local_process_spid VARCHAR2(30);
258 begin
259 -- Endstamp any previous login or resp record.
260 -- This must be done regardless of level in case level was changed
261 -- during login.
262 AUDIT_USER_END(login_id);
263 AUDIT_RESPONSIBILITY_END(login_id);
264
265 -- Create a new login id
266 SELECT FND_LOGINS_S.NEXTVAL INTO audit_user.login_id FROM SYS.DUAL;
267
268 -- If auditing turned on insert record in FL for new login
269 -- bug 7160418, skip auditing for guest user
270 if (audit_level <> 'A') AND (user_id <> '6') then
271 -- Get current oracle and system process ids
272 begin
273 SELECT P.PID, S.PROCESS, P.SERIAL#, P.SPID
274 INTO local_pid, local_spid, local_serial#, local_process_spid
275 FROM V$PROCESS P, V$SESSION S
276 WHERE S.AUDSID = USERENV('SESSIONID')
277 AND S.PADDR = P.ADDR;
278 exception
279 when no_data_found then
280 local_pid := null;
281 local_spid := null;
282 end;
283
284 -- Insert record
285 INSERT INTO FND_LOGINS
286 (LOGIN_ID, USER_ID, START_TIME, TERMINAL_ID,
287 LOGIN_NAME, PID, SPID, SESSION_NUMBER, SERIAL#,
288 PROCESS_SPID, LOGIN_TYPE)
289 VALUES(audit_user.login_id, audit_user.user_id, SYSDATE,
290 audit_user.terminal_id, audit_user.login_name,
291 local_pid, local_spid, audit_user.session_number,
292 local_serial#, local_process_spid, 'FORM');
293 end if;
294 COMMIT;
295 exception
296 when others then
297 rollback;
298 generic_error('FND_SIGNON.AUDIT_USER', SQLCODE, SQLERRM);
299 end AUDIT_USER;
300
301 --
302 -- AUDIT_END - End signon audit
303 --
304 -- End stamp last user and resp record when exiting.
305 --
306 procedure AUDIT_END(login_id in number) is
307 pragma AUTONOMOUS_TRANSACTION;
308 begin
309 -- Endstamp any previous signon audit records
310 AUDIT_USER_END(login_id);
311 COMMIT;
312 exception
313 when others then
314 rollback;
315 generic_error('FND_SIGNON.AUDIT_END', SQLCODE, SQLERRM);
316 end AUDIT_END;
317
318 --
319 -- NEW_SESSION - Misc signon things
320 --
321 -- Get new session number, check password expiration, etc
322 --
323 procedure NEW_SESSION(UID in number,
324 SID out nocopy number,
325 EXPIRED out nocopy varchar2) is
326 LSID number;
327 begin
328 --
329 -- Fetch and lock session number
330 --
331 -- Bug 7160418 skip guest user as we do not audit guest
332 if (UID <> 6) then
333 select SESSION_NUMBER
334 into LSID
335 from FND_USER
336 where USER_ID = UID
337 for update of SESSION_NUMBER, LAST_LOGON_DATE;
338 LSID := LSID + 1;
339 SID := LSID;
340 --
341 -- Update session number, set logon date
342 --
343 update FND_USER
344 set LAST_LOGON_DATE = SYSDATE,
345 SESSION_NUMBER = LSID
346 where USER_ID = UID;
347
348 --
349 -- Test for Expired password
350 --
351 begin
352 select 'Y'
353 into EXPIRED
354 from FND_USER
355 where USER_ID = UID
356 and ENCRYPTED_USER_PASSWORD <> 'EXTERNAL' -- Bug #2288977 --
357 and (PASSWORD_DATE is NULL or
358 (PASSWORD_LIFESPAN_ACCESSES is not NULL and
359 nvl(PASSWORD_ACCESSES_LEFT, 0) < 1) or
360 (PASSWORD_LIFESPAN_DAYS is not NULL and
361 SYSDATE >= PASSWORD_DATE + PASSWORD_LIFESPAN_DAYS));
362 exception
363 when no_data_found then
364 EXPIRED := 'N';
365 end;
366 --
367 -- Decrement password accesses left
368 --
369 begin
370 update FND_USER
371 set PASSWORD_ACCESSES_LEFT = PASSWORD_ACCESSES_LEFT - 1
372 where USER_ID = UID
373 and PASSWORD_ACCESSES_LEFT > 0;
374 exception
375 when no_data_found then
376 null;
377 end;
378 else
379 -- Bug 7160418 skip guest user as we do not audit guest
380 -- We will fall into this else for GUEST user uid=6.
381 SID := -1;
382 EXPIRED := 'N';
383 end if;
384
385 commit;
386 exception
387 when others then
388 generic_error('FND_SIGNON.NEW_SESSION', SQLCODE, SQLERRM);
389 end NEW_SESSION;
390
391 --
392 -- Bug 3375261. new_icx_session(user_id,login_id,expired)
393 -- is called by Java APIs
394 -- SessionManager.validateLogin and WebAppsContext.createSession,
395 -- this causes the functions in new_icx_session to be executed
396 -- twice in a local login flow. The fix is to split the functionality
397 -- of new_icx_session into two new APIs:
398 -- (1) is_pwd_expired: performs password expiration related operations,
399 -- to be used when authenticating a user/pwd pair
400 -- (2) new_icx_session(UID,l_login_id): performs auditing and
401 -- session number related operation, to be used when a session
402 -- is created.
403 /* tests whether a password has expired or not, updates
404 * expiration related bookkeeping data in fnd_user table if necessary.
405 * update last_logon_date in fnd_user
406 */
407 procedure is_pwd_expired(UID in number,
408 EXPIRED out nocopy varchar2) is
409 pragma AUTONOMOUS_TRANSACTION;
410 begin
411 --
412 -- Test for Expired password
413 --
414 begin
415 select 'Y'
416 into EXPIRED
417 from FND_USER
418 where USER_ID = UID
419 and ENCRYPTED_USER_PASSWORD <> 'EXTERNAL' -- Bug #2288977 --
420 and (PASSWORD_DATE is NULL or
421 (PASSWORD_LIFESPAN_ACCESSES is not NULL and
422 nvl(PASSWORD_ACCESSES_LEFT, 0) < 1) or
423 (PASSWORD_LIFESPAN_DAYS is not NULL and
424 SYSDATE >= PASSWORD_DATE + PASSWORD_LIFESPAN_DAYS));
425 exception
426 when no_data_found then
427 EXPIRED := 'N';
428 end;
429 --
430 -- Decrement password accesses left
431 --
432 begin
433 update FND_USER
434 set PASSWORD_ACCESSES_LEFT = PASSWORD_ACCESSES_LEFT - 1
435 where USER_ID = UID
436 and PASSWORD_ACCESSES_LEFT > 0;
437 exception
438 when no_data_found then
439 null;
440 end;
441
442 update FND_USER
443 set LAST_LOGON_DATE = SYSDATE
444 where USER_ID = UID;
445
446 commit;
447 exception
448 when others then
449 rollback;
450 generic_error('FND_SIGNON.is_pwd_expired', SQLCODE, SQLERRM);
451 end is_pwd_expired;
452
453 /*
454 * updates session_number in fnd_user table.
455 * generate auditing record
456 */
457 procedure new_icx_session(UID IN NUMBER,
458 login_id OUT nocopy NUMBER) IS
459 begin
460 new_proxy_icx_session(UID, null, login_id);
461 end new_icx_session;
462
463 /*
464 * updates session_number in fnd_user table.
465 * generate auditing record
466 * Same as new_icx_session except a single change for handling SIGNONAUDIT:LEVEL
467 * differently for Proxy Sessions.
468 */
469 procedure new_proxy_icx_session(UID IN NUMBER,
470 proxy_user IN NUMBER,
471 login_id OUT nocopy NUMBER) IS
472 LSID NUMBER;
473 l_login_id NUMBER;
474 l_audit_level VARCHAR2(1);
475 l_session_id NUMBER;
476 l_proxy_user_id NUMBER;
477 begin
478 --
479 -- Fetch and lock session number
480 --
481 -- Bug 7160418 skip guest user as we do not audit guest
482 if (UID <> 6) then
483 select SESSION_NUMBER
484 into LSID
485 from FND_USER
486 where USER_ID = UID
487 for update of SESSION_NUMBER, LAST_LOGON_DATE;
488 LSID := LSID + 1;
489 --
490 -- Update session number, set logon date
491 --
492 update FND_USER
493 set LAST_LOGON_DATE = SYSDATE,
494 SESSION_NUMBER = LSID
495 where USER_ID = UID;
496 else
497 -- Bug 7160418 , fall into this else for GUEST user, uid=6
498 LSID := -1;
499 end if;
500
501 SELECT userenv('SESSIONID')
502 INTO l_session_id
503 FROM dual;
504
505 /*
506 * Special handling of 'SIGNONAUDIT:LEVEL' for proxy sessions
507 */
508 /* Proxy info is already passed to this api
509 * so no need to call Fnd_Session_Management.isProxySession
510 */
511 l_proxy_user_id := proxy_user;
512
513 if ((l_proxy_user_id is not NULL) AND
514 (l_proxy_user_id <> -1)) then
515 l_audit_level := 'D';
516 else
517 if (FND_GLOBAL.USER_ID = -1) or (FND_GLOBAL.USER_ID = 6) then
518 l_audit_level:=
519 fnd_profile.value_specific('SIGNONAUDIT:LEVEL', UID);
520 else
521 l_audit_level:= fnd_profile.value('SIGNONAUDIT:LEVEL');
522 end if;
523 end if;
524
525
526 audit_user(l_login_id, l_audit_level, UID,
527 NULL, NULL, NULL, LSID);
528
529 -- Bug 7160418 skip guest user as we do not audit guest
530 if (UID <> 6) then
531 INSERT INTO
532 fnd_appl_sessions(login_type, login_id, audsid, start_time)
533 VALUES ('AOLJ', l_login_id, l_session_id, Sysdate);
534 end if;
535
536 login_id := l_login_id;
537 exception
538 when others then
539 login_id := 0;
540 generic_error('FND_SIGNON.NEW_PROXY_ICX_SESSION', SQLCODE, SQLERRM);
541 end new_proxy_icx_session;
542
543 --
544 -- Update_Desktop_Object (PRIVATE)
545 -- Update a function value on the desktop.
546 --
547 procedure Update_Desktop_Object(
548 func_name in varchar2,
549 func_sequence in number,
550 user_id in number,
551 resp_id in number,
552 appl_id in number,
553 login_id in number)
554 is
555 begin
556 if (func_name is null) then
557 -- Delete if value nulled out
558 delete from FND_USER_DESKTOP_OBJECTS
559 where USER_ID = update_desktop_object.user_id
560 and APPLICATION_ID = update_desktop_object.appl_id
561 and RESPONSIBILITY_ID = update_desktop_object.resp_id
562 and TYPE = 'FUNCTION'
563 and SEQUENCE = update_desktop_object.func_sequence;
564 else
565 -- Try for update
566 update FND_USER_DESKTOP_OBJECTS set
567 FUNCTION_NAME = update_desktop_object.func_name,
568 LAST_UPDATE_DATE = sysdate,
569 LAST_UPDATED_BY = update_desktop_object.user_id,
570 LAST_UPDATE_LOGIN = update_desktop_object.login_id
571 where USER_ID = update_desktop_object.user_id
572 and APPLICATION_ID = update_desktop_object.appl_id
573 and RESPONSIBILITY_ID = update_desktop_object.resp_id
574 and TYPE = 'FUNCTION'
575 and SEQUENCE = update_desktop_object.func_sequence;
576
577 if (sql%rowcount = 0) then
578 -- Insert new row if not found
579 insert into FND_USER_DESKTOP_OBJECTS (
580 DESKTOP_OBJECT_ID,
581 USER_ID,
582 APPLICATION_ID,
583 RESPONSIBILITY_ID,
584 OBJECT_NAME,
585 FUNCTION_NAME,
586 OBJECT_LABEL,
587 PARAMETER_STRING,
588 SEQUENCE,
589 LAST_UPDATE_DATE,
590 LAST_UPDATED_BY,
591 CREATION_DATE,
592 CREATED_BY,
593 LAST_UPDATE_LOGIN,
594 TYPE)
595 select
596 FND_DESKTOP_OBJECT_ID_S.NEXTVAL,
597 update_desktop_object.user_id,
598 update_desktop_object.appl_id,
599 update_desktop_object.resp_id,
600 'FUNCTION',
601 update_desktop_object.func_name,
602 'FUNCTION',
603 '',
604 update_desktop_object.func_sequence,
605 sysdate,
606 update_desktop_object.user_id,
607 sysdate,
608 update_desktop_object.user_id,
609 update_desktop_object.login_id,
610 'FUNCTION'
611 from sys.dual;
612 end if;
613 end if;
614 exception
615 when others then
616 generic_error('FND_SIGNON.UPDATE_DESKTOP_OBJECT', SQLCODE, SQLERRM);
617 end;
618
619 --
620 -- UPDATE_NAVIGATOR
621 --
622 -- Update navigator info for current user/resp.
623 --
624 procedure UPDATE_NAVIGATOR(
625 USER_ID in number,
626 RESP_ID in number,
627 APPL_ID in number,
628 LOGIN_ID in number,
629 FUNCTION1 in varchar2,
630 FUNCTION2 in varchar2,
631 FUNCTION3 in varchar2,
632 FUNCTION4 in varchar2,
633 FUNCTION5 in varchar2,
634 FUNCTION6 in varchar2,
635 FUNCTION7 in varchar2,
636 FUNCTION8 in varchar2,
637 FUNCTION9 in varchar2,
638 FUNCTION10 in varchar2,
639 WINDOW_WIDTH in number,
640 WINDOW_HEIGHT in number,
641 WINDOW_XPOS in number,
642 WINDOW_YPOS in number,
643 NEW_WINDOW_FLAG in varchar2) is
644
645 begin
646 -- Save Hotlist functions to desktop objects
647 Fnd_Signon.Update_Desktop_Object(function1, 1,
648 user_id, resp_id, appl_id, login_id);
649 Fnd_Signon.Update_Desktop_Object(function2, 2,
650 user_id, resp_id, appl_id, login_id);
651 Fnd_Signon.Update_Desktop_Object(function3, 3,
652 user_id, resp_id, appl_id, login_id);
653 Fnd_Signon.Update_Desktop_Object(function4, 4,
654 user_id, resp_id, appl_id, login_id);
655 Fnd_Signon.Update_Desktop_Object(function5, 5,
656 user_id, resp_id, appl_id, login_id);
657 Fnd_Signon.Update_Desktop_Object(function6, 6,
658 user_id, resp_id, appl_id, login_id);
659 Fnd_Signon.Update_Desktop_Object(function7, 7,
660 user_id, resp_id, appl_id, login_id);
661 Fnd_Signon.Update_Desktop_Object(function8, 8,
662 user_id, resp_id, appl_id, login_id);
663 Fnd_Signon.Update_Desktop_Object(function9, 9,
664 user_id, resp_id, appl_id, login_id);
665 Fnd_Signon.Update_Desktop_Object(function10, 10,
666 user_id, resp_id, appl_id, login_id);
667
668 -- Save window position to preferences
669 Fnd_Preference.Put(Fnd_Global.User_Name, 'FNDSCSGN',
670 'WINDOW_WIDTH',
671 fnd_number.number_to_canonical(window_width));
672 Fnd_Preference.Put(Fnd_Global.User_Name, 'FNDSCSGN',
673 'WINDOW_HEIGHT',
674 fnd_number.number_to_canonical(window_height));
675 Fnd_Preference.Put(Fnd_Global.User_Name, 'FNDSCSGN',
676 'WINDOW_XPOS',
677 fnd_number.number_to_canonical(window_xpos));
678 Fnd_Preference.Put(Fnd_Global.User_Name, 'FNDSCSGN',
679 'WINDOW_YPOS',
680 fnd_number.number_to_canonical(window_ypos));
681 Fnd_Preference.Put(Fnd_Global.User_Name, 'FNDSCSGN',
682 'NEW_WINDOW_FLAG', new_window_flag);
683 commit;
684 exception
685 when others then
686 generic_error('FND_SIGNON.UPDATE_NAVIGATOR', SQLCODE, SQLERRM);
687 end UPDATE_NAVIGATOR;
688
689 --
690 -- GET_NAVIGATOR_PREFERENCES
691 -- Get Navigator window sizing preferences.
692 --
693 procedure GET_NAVIGATOR_PREFERENCES(
694 WINDOW_WIDTH out nocopy number,
695 WINDOW_HEIGHT out nocopy number,
696 WINDOW_XPOS out nocopy number,
697 WINDOW_YPOS out nocopy number,
698 NEW_WINDOW_FLAG out nocopy varchar2)
699 is
700 begin
701 window_width := fnd_number.canonical_to_number(
702 Fnd_Preference.Get(Fnd_Global.User_Name,
703 'FNDSCSGN', 'WINDOW_WIDTH'));
704 window_height := fnd_number.canonical_to_number(
705 Fnd_Preference.Get(Fnd_Global.User_Name,
706 'FNDSCSGN', 'WINDOW_HEIGHT'));
707 window_xpos := fnd_number.canonical_to_number(
708 Fnd_Preference.Get(Fnd_Global.User_Name,
709 'FNDSCSGN', 'WINDOW_XPOS'));
710 window_ypos := fnd_number.canonical_to_number(
711 Fnd_Preference.Get(Fnd_Global.User_Name,
712 'FNDSCSGN', 'WINDOW_YPOS'));
713 new_window_flag := substrb(Fnd_Preference.Get(
714 Fnd_Global.User_Name,
715 'FNDSCSGN', 'NEW_WINDOW_FLAG'), 1, 1);
716 exception
717 when others then
718 generic_error('FND_SIGNON.GET_NAVIGATOR_PREFERENCES', SQLCODE, SQLERRM);
719 end GET_NAVIGATOR_PREFERENCES;
720
721 --
722 -- SET_SESSION
723 -- Store session date whenever new session is created.
724 -- To be called in pre-form of any form opened in a new session.
725 -- This is to maintain session dates for AOL forms running under
726 -- HR responsibilities.
727 --
728 procedure SET_SESSION(session_date in varchar2) is
729 l_ses_date date;
730 hmask varchar2(11) := 'DD-MON-YYYY';
731 begin
732 l_ses_date := nvl(to_date(session_date, hmask),
733 trunc(sysdate));
734 insert into FND_SESSIONS (
735 SESSION_ID,
736 EFFECTIVE_DATE)
737 select
738 userenv('SESSIONID'),
739 l_ses_date
740 from sys.dual
741 where not exists
742 (select null
743 from FND_SESSIONS
744 where SESSION_ID = userenv('SESSIONID'));
745
746 commit;
747 exception
748 when others then
749 generic_error('FND_SIGNON.SET_SESSION', SQLCODE, SQLERRM);
750 end SET_SESSION;
751
752 -- Misc signon things for an aol/j session.
753 -- For internal use only.
754
755
756 -- PRIVATE_NEW_SESSION - wrapper call to new_session() to isolate autonomous
757 -- transaction to just the new_session. This is required for bug 1950030 and
758 -- comes from the original requirement for bug 1870328, where the call to
759 -- new_session() - which does a commit - caused the calling Java save points
760 -- to be lost and all transactions before this call to get committed.
761 --
762 -- 1870328 tried adding the AUTONOMOUS_TRANSACTION pragma to new_aolj_session()
763 -- and assumed that the commit in new_session() was sufficient for the pragma,
764 -- but the additional insert clause required a commit as well... Rather than
765 -- dealing with the INSERT and EXCEPTION handling under the pragma and to keep
766 -- the commit structure as close to original coding i decided to just isolate
767 -- new_session().
768 --
769 -- For internal use only.
770
771 procedure PRIVATE_NEW_SESSION(pUID in number,
772 pSID in out nocopy number,
773 pEXPIRED in out nocopy varchar2) is
774 pragma AUTONOMOUS_TRANSACTION;
775 begin
776 new_session(pUID,pSID,pEXPIRED);
777 end PRIVATE_NEW_SESSION;
778
779
780 /*
781 * NEW_AOLJ_SESSION
782 * Wrapper to new_icx_session as it was deemed redundant, i.e. it performed
783 * the same functionality as new_icx_session without the auditing. All sessions
784 * created should be audited.
785 *
786 * IN
787 * user_id - User's ID
788 * OUT
789 * p_loginID - Login ID of audit record (if successful)
790 * p_expired - Expiration flag to check whether user's password has expired.
791 * RAISES
792 * Never raises exceptions, places a message on the
793 * message stack if an error is encountered.
794 */
795 PROCEDURE new_aolj_session(user_id IN NUMBER,
796 login_id OUT nocopy NUMBER,
797 expired OUT nocopy VARCHAR2)
798 IS
799 BEGIN
800
801 new_icx_session(user_id, login_id, expired);
802
803 EXCEPTION
804 WHEN OTHERS THEN
805 login_id := 0;
806 expired := 'N';
807 generic_error('FND_SIGNON.NEW_AOLJ_SESSION', SQLCODE, SQLERRM);
808 end NEW_AOLJ_SESSION;
809
810
811 --AUDIT_WEB_RESPONSIBILITY created to audit visits to Responsibilities within ICX.
812 --Not as detailed as other resp audits.. but that is due to lack of reliable details within ICX.
813 procedure AUDIT_WEB_RESPONSIBILITY(login_id in number,
814 login_resp_id in number,
815 resp_appl_id in number,
816 resp_id in number)
817 IS
818 pragma AUTONOMOUS_TRANSACTION;
819 audit_level VARCHAR2(1);
820 rows_exist NUMBER := 0;
821 l_audit_level VARCHAR2(1);
822 l_proxy_user_id NUMBER;
823 l_session_id NUMBER;
824
825 BEGIN
826
827 select userenv('SESSIONID') into l_session_id from dual;
828 l_proxy_user_id := fnd_session_management.isProxySession(null);
829
830
831 if (l_proxy_user_id is not NULL) then
832 l_audit_level := 'D';
833 else
834 l_audit_level:=fnd_profile.value('SIGNONAUDIT:LEVEL');
835 end if;
836
837 -- If resp/form level auditing insert record for new resp
838 if (l_audit_level in ('C', 'D')) then
839
840 --see if there is already a row
841 SELECT count(*) INTO rows_exist
842 FROM fnd_login_responsibilities
843 WHERE login_id=audit_web_responsibility.login_id
844 AND login_resp_id=audit_web_responsibility.login_resp_id;
845
846
847 IF rows_exist=0 THEN
848
849 INSERT INTO FND_LOGIN_RESPONSIBILITIES
850 (LOGIN_ID, LOGIN_RESP_ID, RESP_APPL_ID, RESPONSIBILITY_ID, START_TIME,
851 AUDSID)
852 VALUES (audit_web_responsibility.login_id,
853 audit_web_responsibility.login_resp_id,
854 audit_web_responsibility.resp_appl_id,
855 audit_web_responsibility.resp_id, SYSDATE,
856 userenv('SESSIONID'));
857
858 END IF; -- rows_exist
859
860 end if; -- audit_level in ..
861 COMMIT;
862 exception
863 when others then
864 rollback;
865
866 generic_error('FND_SIGNON.AUDIT_WEB_RESPONSIBILITY', SQLCODE, SQLERRM);
867 end AUDIT_WEB_RESPONSIBILITY;
868
869 /*
870 * NEW_ICX_SESSION
871 * Creates a session and updates auditing tables for each session created.
872 *
873 * IN
874 * user_id - User's ID
875 * OUT
876 * p_loginID - Login ID of audit record (if successful)
877 * p_expired - Expiration flag to check whether user's password has expired.
878 * RAISES
879 * Never raises exceptions, places a message on the
880 * message stack if an error is encountered.
881 */
882
883 PROCEDURE new_icx_session(user_id IN NUMBER,
884 login_id OUT nocopy NUMBER,
885 expired OUT nocopy VARCHAR2)
886 IS
887 l_session_number NUMBER;
888 l_login_id NUMBER;
889 l_expired VARCHAR2(1);
890 l_audit_level VARCHAR2(1);
891 l_session_id NUMBER;
892 l_proxy_user_id NUMBER;
893 BEGIN
894
895 PRIVATE_NEW_SESSION(user_id, l_session_number, l_expired);
896 --
897 -- Bug 3238722: The login_id generated by FND_LOGINS_S is not being recorded in
898 -- FND_LOGINS when a user-level value for the profile option 'Sign-On Audit:Level' is set
899 -- but a site-level value is not. This happens when the audit level value returned by
900 -- fnd_profile.value returns the site-level when the context is not set. The code needs to
901 -- check if the context is set by an FND_GLOBAL.USER_ID call. If the return value is -1,
902 -- then the context is not set. If the context is not set, the code should call
903 -- fnd_profile.value_specific and pass in the user_id to return an accurate value for
904 -- the audit level. Once an accurate value for the profile is returned, the login_id will
905 -- be properly recorded in FND_LOGINS.
906 --
907
908 SELECT userenv('SESSIONID')
909 INTO l_session_id
910 FROM dual;
911
912 begin
913 select session_id into l_session_id
914 from ICX_SESSIONS
915 where session_id= l_session_id;
916
917 l_proxy_user_id :=
918 fnd_session_management.isProxySession(l_session_id);
919 exception
920 when no_data_found then
921 l_proxy_user_id := NULL;
922 end;
923
924 -- Proxy sessions are always audited at FORM level irrespective
925 -- of SIGNONAUDIT:LEVEL profile option
926 if (l_proxy_user_id is not NULL) then
927 l_audit_level := 'D';
928 else
929 if (FND_GLOBAL.USER_ID = -1) OR (FND_GLOBAL.USER_ID = 6) then
930 l_audit_level:= fnd_profile.value_specific('SIGNONAUDIT:LEVEL', user_id);
931 else
932 l_audit_level:= fnd_profile.value('SIGNONAUDIT:LEVEL');
933 end if;
934 end if;
935
936
937 audit_user(l_login_id, l_audit_level, user_id, NULL, NULL, NULL, l_session_number);
938
939 -- Bug 7160418 skip guest user as we do not audit guest
940 if (UID <> 6) then
941 INSERT INTO fnd_appl_sessions(login_type, login_id, audsid, start_time)
942 VALUES ('AOLJ', l_login_id, l_session_id, Sysdate);
943 end if;
944
945 login_id := l_login_id;
946 expired := l_expired;
947
948 EXCEPTION
949 WHEN OTHERS THEN
950 login_id := 0;
951 expired := 'N';
952 -- Changed FND_SIGNON.NEW_AOLJ_SESSION to FND_SIGNON.NEW_ICX_SESSION
953 -- for consistency.
954 generic_error('FND_SIGNON.NEW_ICX_SESSION', SQLCODE, SQLERRM);
955 end NEW_ICX_SESSION;
956
957 /* BUG:5052314: API to retrieve number of unsuccessful logins */
958 /* previous to current login */
959 FUNCTION get_invalid_logins(p_userID number) return NUMBER
960 IS
961
962 number_of_unsuccessful_logins NUMBER:= 0;
963 l_user_id NUMBER;
964
965
966 BEGIN
967
968 -- check user id exist.
969 select user_id
970 into l_user_id
971 from FND_USER
972 where user_id = p_userID;
973
974 -- Bug 7169414 - rewrite - changed query to be the same used in the Forms login
975
976 select count(ul.USER_ID)
977 into number_of_unsuccessful_logins
978 from fnd_unsuccessful_logins ul, fnd_user u
979 where u.user_id = l_user_id
980 and ul.user_id = u.user_id
981 and ul.attempt_time > nvl(u.last_logon_date, u.last_update_date);
982
983 return number_of_unsuccessful_logins;
984
985 EXCEPTION when NO_DATA_FOUND then
986
987 -- raise no data found error.
988 fnd_message.set_name('FND','SQL_PLSQL_ERROR');
989 fnd_message.set_token('ROUTINE','FND_AOLJ_UTIL.get_invalid_logins()');
990 fnd_message.set_token('ERRNO',1403 );
991 fnd_message.set_token('REASON','Invalid User ID provided');
992 app_exception.raise_exception;
993
994 when OTHERS then
995
996 --raise generic error.
997 fnd_message.set_name('FND','SQL_PLSQL_ERROR');
998 fnd_message.set_token('ROUTINE','FND_AOLJ_UTIL.get_invalid_logins()');
999 fnd_message.set_token('ERRNO',SUBSTR(sqlerrm,5,5));
1000 fnd_message.set_token('REASON', sqlerrm);
1001 app_exception.raise_exception;
1002
1003
1004 END get_invalid_logins;
1005
1006 end FND_SIGNON;
[Home] [Help]