[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE FND_LOGIN_RESP_FORMS FLRF
SET END_TIME = SYSDATE
WHERE FLRF.LOGIN_ID = audit_form_end.login_id
AND FLRF.END_TIME is NULL;
UPDATE FND_LOGIN_RESPONSIBILITIES FLR
SET END_TIME = SYSDATE
WHERE FLR.LOGIN_ID = audit_responsibility_end.login_id
AND FLR.END_TIME is NULL;
UPDATE FND_LOGINS FL
SET END_TIME = SYSDATE
WHERE FL.LOGIN_ID = audit_user_end.login_id;
UPDATE FND_LOGIN_RESP_FORMS FLRF
SET END_TIME = SYSDATE
WHERE FLRF.LOGIN_ID = audit_form.login_id
AND FLRF.LOGIN_RESP_ID = audit_form.login_resp_id
AND FLRF.END_TIME IS NULL
AND (FLRF.FORM_ID, FLRF.FORM_APPL_ID) =
(SELECT F.FORM_ID, F.APPLICATION_ID
FROM FND_FORM F, FND_APPLICATION A
WHERE F.FORM_NAME = audit_form.form_name
AND F.APPLICATION_ID = A.APPLICATION_ID
AND A.APPLICATION_SHORT_NAME = audit_form.form_application);
INSERT INTO FND_LOGIN_RESP_FORMS
(LOGIN_ID, LOGIN_RESP_ID, FORM_APPL_ID, FORM_ID, START_TIME,
AUDSID)
SELECT audit_form.login_id, audit_form.login_resp_id,
A.APPLICATION_ID, F.FORM_ID, SYSDATE,
userenv('SESSIONID')
FROM FND_FORM F, FND_APPLICATION A
WHERE F.FORM_NAME = audit_form.form_name
AND F.APPLICATION_ID = A.APPLICATION_ID
AND A.APPLICATION_SHORT_NAME = audit_form.form_application;
SELECT P.PID, P.SERIAL#, P.SPID
INTO l_pid, l_serial, l_spid
FROM V$PROCESS P, V$SESSION S
WHERE S.AUDSID = USERENV('SESSIONID')
AND S.PADDR = P.ADDR;
UPDATE FND_LOGINS FL
SET PID = l_pid,
SERIAL# = l_serial,
PROCESS_SPID = l_spid
WHERE FL.LOGIN_ID = audit_responsibility.login_id;
select FND_LOGIN_RESPONSIBILITIES_S.nextval into login_resp_id from dual;
INSERT INTO FND_LOGIN_RESPONSIBILITIES
(LOGIN_ID, LOGIN_RESP_ID, RESP_APPL_ID, RESPONSIBILITY_ID, START_TIME,
AUDSID)
VALUES (audit_responsibility.login_id,
audit_responsibility.login_resp_id,
audit_responsibility.resp_appl_id,
audit_responsibility.resp_id, SYSDATE,
userenv('SESSIONID'));
SELECT FND_LOGINS_S.NEXTVAL INTO audit_user.login_id FROM SYS.DUAL;
SELECT P.PID, S.PROCESS, P.SERIAL#, P.SPID
INTO local_pid, local_spid, local_serial#, local_process_spid
FROM V$PROCESS P, V$SESSION S
WHERE S.AUDSID = USERENV('SESSIONID')
AND S.PADDR = P.ADDR;
-- Insert record
INSERT INTO FND_LOGINS
(LOGIN_ID, USER_ID, START_TIME, TERMINAL_ID,
LOGIN_NAME, PID, SPID, SESSION_NUMBER, SERIAL#,
PROCESS_SPID, LOGIN_TYPE)
VALUES(audit_user.login_id, audit_user.user_id, SYSDATE,
audit_user.terminal_id, audit_user.login_name,
local_pid, local_spid, audit_user.session_number,
local_serial#, local_process_spid, 'FORM');
select SESSION_NUMBER
into LSID
from FND_USER
where USER_ID = UID
for update of SESSION_NUMBER, LAST_LOGON_DATE;
update FND_USER
set LAST_LOGON_DATE = SYSDATE,
SESSION_NUMBER = LSID
where USER_ID = UID;
select 'Y'
into EXPIRED
from FND_USER
where USER_ID = UID
and ENCRYPTED_USER_PASSWORD <> 'EXTERNAL' -- Bug #2288977 --
and (PASSWORD_DATE is NULL or
(PASSWORD_LIFESPAN_ACCESSES is not NULL and
nvl(PASSWORD_ACCESSES_LEFT, 0) < 1) or
(PASSWORD_LIFESPAN_DAYS is not NULL and
SYSDATE >= PASSWORD_DATE + PASSWORD_LIFESPAN_DAYS));
update FND_USER
set PASSWORD_ACCESSES_LEFT = PASSWORD_ACCESSES_LEFT - 1
where USER_ID = UID
and PASSWORD_ACCESSES_LEFT > 0;
/* tests whether a password has expired or not, updates
* expiration related bookkeeping data in fnd_user table if necessary.
* update last_logon_date in fnd_user
*/
procedure is_pwd_expired(UID in number,
EXPIRED out nocopy varchar2) is
pragma AUTONOMOUS_TRANSACTION;
select 'Y'
into EXPIRED
from FND_USER
where USER_ID = UID
and ENCRYPTED_USER_PASSWORD <> 'EXTERNAL' -- Bug #2288977 --
and (PASSWORD_DATE is NULL or
(PASSWORD_LIFESPAN_ACCESSES is not NULL and
nvl(PASSWORD_ACCESSES_LEFT, 0) < 1) or
(PASSWORD_LIFESPAN_DAYS is not NULL and
SYSDATE >= PASSWORD_DATE + PASSWORD_LIFESPAN_DAYS));
update FND_USER
set PASSWORD_ACCESSES_LEFT = PASSWORD_ACCESSES_LEFT - 1
where USER_ID = UID
and PASSWORD_ACCESSES_LEFT > 0;
update FND_USER
set LAST_LOGON_DATE = SYSDATE
where USER_ID = UID;
* updates session_number in fnd_user table.
* generate auditing record
*/
procedure new_icx_session(UID IN NUMBER,
login_id OUT nocopy NUMBER) IS
begin
new_proxy_icx_session(UID, null, login_id);
* updates session_number in fnd_user table.
* generate auditing record
* Same as new_icx_session except a single change for handling SIGNONAUDIT:LEVEL
* differently for Proxy Sessions.
*/
procedure new_proxy_icx_session(UID IN NUMBER,
proxy_user IN NUMBER,
login_id OUT nocopy NUMBER) IS
LSID NUMBER;
select SESSION_NUMBER
into LSID
from FND_USER
where USER_ID = UID
for update of SESSION_NUMBER, LAST_LOGON_DATE;
update FND_USER
set LAST_LOGON_DATE = SYSDATE,
SESSION_NUMBER = LSID
where USER_ID = UID;
SELECT userenv('SESSIONID')
INTO l_session_id
FROM dual;
INSERT INTO
fnd_appl_sessions(login_type, login_id, audsid, start_time)
VALUES ('AOLJ', l_login_id, l_session_id, Sysdate);
procedure Update_Desktop_Object(
func_name in varchar2,
func_sequence in number,
user_id in number,
resp_id in number,
appl_id in number,
login_id in number)
is
begin
if (func_name is null) then
-- Delete if value nulled out
delete from FND_USER_DESKTOP_OBJECTS
where USER_ID = update_desktop_object.user_id
and APPLICATION_ID = update_desktop_object.appl_id
and RESPONSIBILITY_ID = update_desktop_object.resp_id
and TYPE = 'FUNCTION'
and SEQUENCE = update_desktop_object.func_sequence;
update FND_USER_DESKTOP_OBJECTS set
FUNCTION_NAME = update_desktop_object.func_name,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = update_desktop_object.user_id,
LAST_UPDATE_LOGIN = update_desktop_object.login_id
where USER_ID = update_desktop_object.user_id
and APPLICATION_ID = update_desktop_object.appl_id
and RESPONSIBILITY_ID = update_desktop_object.resp_id
and TYPE = 'FUNCTION'
and SEQUENCE = update_desktop_object.func_sequence;
insert into FND_USER_DESKTOP_OBJECTS (
DESKTOP_OBJECT_ID,
USER_ID,
APPLICATION_ID,
RESPONSIBILITY_ID,
OBJECT_NAME,
FUNCTION_NAME,
OBJECT_LABEL,
PARAMETER_STRING,
SEQUENCE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TYPE)
select
FND_DESKTOP_OBJECT_ID_S.NEXTVAL,
update_desktop_object.user_id,
update_desktop_object.appl_id,
update_desktop_object.resp_id,
'FUNCTION',
update_desktop_object.func_name,
'FUNCTION',
'',
update_desktop_object.func_sequence,
sysdate,
update_desktop_object.user_id,
sysdate,
update_desktop_object.user_id,
update_desktop_object.login_id,
'FUNCTION'
from sys.dual;
generic_error('FND_SIGNON.UPDATE_DESKTOP_OBJECT', SQLCODE, SQLERRM);
procedure UPDATE_NAVIGATOR(
USER_ID in number,
RESP_ID in number,
APPL_ID in number,
LOGIN_ID in number,
FUNCTION1 in varchar2,
FUNCTION2 in varchar2,
FUNCTION3 in varchar2,
FUNCTION4 in varchar2,
FUNCTION5 in varchar2,
FUNCTION6 in varchar2,
FUNCTION7 in varchar2,
FUNCTION8 in varchar2,
FUNCTION9 in varchar2,
FUNCTION10 in varchar2,
WINDOW_WIDTH in number,
WINDOW_HEIGHT in number,
WINDOW_XPOS in number,
WINDOW_YPOS in number,
NEW_WINDOW_FLAG in varchar2) is
begin
-- Save Hotlist functions to desktop objects
Fnd_Signon.Update_Desktop_Object(function1, 1,
user_id, resp_id, appl_id, login_id);
Fnd_Signon.Update_Desktop_Object(function2, 2,
user_id, resp_id, appl_id, login_id);
Fnd_Signon.Update_Desktop_Object(function3, 3,
user_id, resp_id, appl_id, login_id);
Fnd_Signon.Update_Desktop_Object(function4, 4,
user_id, resp_id, appl_id, login_id);
Fnd_Signon.Update_Desktop_Object(function5, 5,
user_id, resp_id, appl_id, login_id);
Fnd_Signon.Update_Desktop_Object(function6, 6,
user_id, resp_id, appl_id, login_id);
Fnd_Signon.Update_Desktop_Object(function7, 7,
user_id, resp_id, appl_id, login_id);
Fnd_Signon.Update_Desktop_Object(function8, 8,
user_id, resp_id, appl_id, login_id);
Fnd_Signon.Update_Desktop_Object(function9, 9,
user_id, resp_id, appl_id, login_id);
Fnd_Signon.Update_Desktop_Object(function10, 10,
user_id, resp_id, appl_id, login_id);
generic_error('FND_SIGNON.UPDATE_NAVIGATOR', SQLCODE, SQLERRM);
end UPDATE_NAVIGATOR;
insert into FND_SESSIONS (
SESSION_ID,
EFFECTIVE_DATE)
select
userenv('SESSIONID'),
l_ses_date
from sys.dual
where not exists
(select null
from FND_SESSIONS
where SESSION_ID = userenv('SESSIONID'));
select userenv('SESSIONID') into l_session_id from dual;
SELECT count(*) INTO rows_exist
FROM fnd_login_responsibilities
WHERE login_id=audit_web_responsibility.login_id
AND login_resp_id=audit_web_responsibility.login_resp_id;
INSERT INTO FND_LOGIN_RESPONSIBILITIES
(LOGIN_ID, LOGIN_RESP_ID, RESP_APPL_ID, RESPONSIBILITY_ID, START_TIME,
AUDSID)
VALUES (audit_web_responsibility.login_id,
audit_web_responsibility.login_resp_id,
audit_web_responsibility.resp_appl_id,
audit_web_responsibility.resp_id, SYSDATE,
userenv('SESSIONID'));
* Creates a session and updates auditing tables for each session created.
*
* IN
* user_id - User's ID
* OUT
* p_loginID - Login ID of audit record (if successful)
* p_expired - Expiration flag to check whether user's password has expired.
* RAISES
* Never raises exceptions, places a message on the
* message stack if an error is encountered.
*/
PROCEDURE new_icx_session(user_id IN NUMBER,
login_id OUT nocopy NUMBER,
expired OUT nocopy VARCHAR2)
IS
l_session_number NUMBER;
SELECT userenv('SESSIONID')
INTO l_session_id
FROM dual;
select session_id into l_session_id
from ICX_SESSIONS
where session_id= l_session_id;
INSERT INTO fnd_appl_sessions(login_type, login_id, audsid, start_time)
VALUES ('AOLJ', l_login_id, l_session_id, Sysdate);
select user_id
into l_user_id
from FND_USER
where user_id = p_userID;
select count(ul.USER_ID)
into number_of_unsuccessful_logins
from fnd_unsuccessful_logins ul, fnd_user u
where u.user_id = l_user_id
and ul.user_id = u.user_id
and ul.attempt_time > nvl(u.last_logon_date, u.last_update_date);