Reports2017-11-18T12:27:27+00:00

FND User Login History

Description
Categories: Application, Enginatics
Audit history of application user logins. EBS user logon audit is controlled by profile 'Sign-On:Audit Level'. The most detailed audit level setting is 'FORM'. Unfortunately, this audit tracks access to individual forms only, but not to different JSPs (HTML / OAF / ADF Pages). As a workaround, the report SQL also joins to icx_sessions, which contains a record for each login (in fa... Audit history of application user logins.

EBS user logon audit is controlled by profile 'Sign-On:Audit Level'.
The most detailed audit level setting is 'FORM'.
Unfortunately, this audit tracks access to individual forms only, but not to different JSPs (HTML / OAF / ADF Pages).
As a workaround, the report SQL also joins to icx_sessions, which contains a record for each login (in fact, it also stores a record for each access to the login page before login. These records are marked with guest='Y').
The function retrieved from icx_session however, just shows the latest OAF function accessed by the user, not all individual JSP functions accessed within that session.
  more

select
xxen_util.client_time(x.start_time) start_time,
xxen_util.client_time(x.end_time) end_time,
x.user_name,
frv.responsibility_name responsibility,
x.form,
x.icx_function,
x.login_id,
x.audsid,
gs.inst_id,
gs.sid,
gs.serial#,
x.server_address,
x.webhost,
x.organization
from
(
select
nvl(flrf.start_time,nvl(flr.start_time,fl.start_time)) start_time,
nvl(flrf.end_time,nvl(flr.end_time,fl.end_time)) end_time,
xxen_util.user_name(fl.user_id) user_name,
ffv.user_form_name form,
fffv.user_function_name icx_function,
fl.login_id,
nvl(flr.resp_appl_id,ixs.responsibility_application_id) resp_appl_id,
nvl(flr.responsibility_id,ixs.responsibility_id) responsibility_id,
coalesce(flrf.audsid,flr.audsid,fas.audsid) audsid,
fn.server_address,
fn.webhost,
(
select distinct
listagg(haou.name,', ') within group (order by haou.name) over (partition by paaf.person_id) organization
from
per_all_assignments_f paaf,
hr_all_organization_units haou
where
fu.employee_id=paaf.person_id and
sysdate between nvl(paaf.effective_start_date,sysdate) and nvl(paaf.effective_end_date,sysdate) and
paaf.organization_id=haou.organization_id
) organization
from
fnd_logins fl,
fnd_login_responsibilities flr,
fnd_login_resp_forms flrf,
fnd_form_vl ffv,
fnd_appl_sessions fas,
icx_sessions ixs,
fnd_nodes fn,
fnd_form_functions_vl fffv,
fnd_user fu
where
1=1 and
fl.login_type='FORM' and
fl.login_id=flr.login_id(+) and
flr.login_id=flrf.login_id(+) and
flr.login_resp_id=flrf.login_resp_id(+) and
flrf.form_appl_id=ffv.application_id(+) and
flrf.form_id=ffv.form_id(+) and
fl.login_id=fas.login_id(+) and
fl.login_id=ixs.login_id(+) and
ixs.node_id=fn.node_id(+) and
ixs.function_id=fffv.function_id(+) and
fl.user_id=fu.user_id
) x,
fnd_responsibility_vl frv,
gv$session gs
where
x.resp_appl_id=frv.application_id(+) and
x.responsibility_id=frv.responsibility_id(+) and
x.audsid=gs.audsid(+)
order by
x.login_id desc,
x.start_time desc

Parameter Name SQL text Validation
User Name
fl.user_id in (select fu.user_id from fnd_user fu where fu.user_name=:user_name)
LOV
Responsibility Name
frv.responsibility_name=:responsibility
LOV
Interface Mode
nvl(flrf.start_time,nvl(flr.start_time,fl.start_time))>=
(
select
fcr0.actual_start_date
from
fnd_concurrent_requests fcr,
fnd_concurrent_requests fcr0
where
fcr.request_id=fnd_global.conc_request_id and
fcr.parent_request_id=fcr0.request_id
) and
fl.user_id<>(select fu.user_id from fnd_user fu where fu.user_name='SWITZERLAND')
LOV
Form Name
ffv.form_name=:form
LOV
Date To
nvl(flrf.start_time,nvl(flr.start_time,fl.start_time))<=:date_to
DateTime
Date From
nvl(flrf.start_time,nvl(flr.start_time,fl.start_time))>=:date_from
DateTime
Logged in within Days
nvl(flrf.start_time,nvl(flr.start_time,fl.start_time))>=sysdate-:days
Number

By continuing to use the site, you agree to the use of cookies. Accept