FND User Login History with Geolocation

Description
Categories: Draft
Columns: Ip Address, Login Country, Login City, First Connect, Start Time, End Time, User Name, Responsibility, Form, Icx Function ...
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 fac ... 
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
ela.ip_address,
ela.login_country,
ela.login_city,
x.first_connect,
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,
ixs.first_connect,
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(haouv.name,', ') within group (order by haouv.name) over (partition by paaf.person_id) organization
from
per_all_assignments_f paaf,
hr_all_organization_units_vl haouv
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=haouv.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,
ebs_login_audit ela
where
x.resp_appl_id=frv.application_id(+) and
x.responsibility_id=frv.responsibility_id(+) and
x.audsid=gs.audsid(+) and
x.first_connect=ela.login_time(+)
order by
x.login_id desc,
x.start_time desc
Parameter Name SQL text Validation
User Name
fl.user_id=xxen_util.user_id(:user_name)
LOV
Exclude User Name
fl.user_id<>xxen_util.user_id(:exclude_user_name)
LOV
Responsibility Name
frv.responsibility_name=:responsibility
LOV
Restrict to incremental data
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
Incremental Alert 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
)
LOV
Form Name
ffv.form_name=:form
LOV
Logged in within Days
nvl(flrf.start_time,nvl(flr.start_time,fl.start_time))>=sysdate-:days
Number
Date From
nvl(flrf.start_time,nvl(flr.start_time,fl.start_time))>=:date_from
DateTime
Date To
nvl(flrf.start_time,nvl(flr.start_time,fl.start_time))<=:date_to
DateTime