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