ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 8E2FF36EDEA779D2E0530100007F1FF2 FND Form Name Y select ffv.form_name value, fav.application_name||': '||ffv.user_form_name||nvl2(ffv.description,': '||ffv.description,null) description from fnd_application_vl fav, fnd_form_vl ffv where fav.application_id=ffv.application_id order by ffv.form_name US 8E2FF36EDE9E79D2E0530100007F1FF2 FND Responsibility Name Y select frv.responsibility_name value, frv.description from fnd_responsibility_vl frv order by frv.responsibility_name US 8E2FF36EDE8479D2E0530100007F1FF2 FND User Name Y Y select fu.user_name value, trim(coalesce( trim(papf.first_name||' '||papf.last_name), fu.description, fu.email_address, papf.email_address )||fu.inactive) description from (select case when sysdate between fu.start_date and nvl(fu.end_date,sysdate) then null else ' (inactive)' end inactive, fu.* from fnd_user fu) fu, (select papf.* from per_all_people_f papf where sysdate between papf.effective_start_date and papf.effective_end_date) papf, ( select distinct furg.user_id, count(*) over (partition by furg.user_id) resp_count, max(fr.responsibility_key) over (partition by furg.user_id) max_responsibility_key from fnd_responsibility fr, fnd_user_resp_groups_direct furg where fr.responsibility_id=furg.responsibility_id and fr.application_id=furg.responsibility_application_id ) furg where fu.employee_id=papf.person_id(+) and fu.user_id=furg.user_id(+) and not (furg.resp_count=1 and furg.max_responsibility_key='IRC_EXT_CANDIDATE') order by fu.inactive desc, fu.user_name US FND user names, excluding iRecruitment candidates. 8E2FF36EDEA679D2E0530100007F1FF2 Yes Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US D09518C0CDBFFCF6E05362FB0905124E select x.clientip, xxen_geolocation(x.clientip,'country') country, xxen_geolocation(x.clientip,'city') 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 fl.clientip, 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 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 Y C752170F01AA63A6E05362FB09053C8A US XXEN: FND User Login History with Geolocation 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. Draft 1=1 1 2 1=1 frv.responsibility_name=:responsibility LOV FND Responsibility Name 8E2FF36EDE9E79D2E0530100007F1FF2 Y select frv.responsibility_name value, frv.description from fnd_responsibility_vl frv order by frv.responsibility_name US Responsibility Name 2 4 1=1 ffv.form_name=:form LOV FND Form Name 8E2FF36EDEA779D2E0530100007F1FF2 Y select ffv.form_name value, fav.application_name||': '||ffv.user_form_name||nvl2(ffv.description,': '||ffv.description,null) description from fnd_application_vl fav, fnd_form_vl ffv where fav.application_id=ffv.application_id order by ffv.form_name US Form Name 3 10 1=1 fl.user_id=xxen_util.user_id(:user_name) LOV FND User Name 8E2FF36EDE8479D2E0530100007F1FF2 Y Y select fu.user_name value, trim(coalesce( trim(papf.first_name||' '||papf.last_name), fu.description, fu.email_address, papf.email_address )||fu.inactive) description from (select case when sysdate between fu.start_date and nvl(fu.end_date,sysdate) then null else ' (inactive)' end inactive, fu.* from fnd_user fu) fu, (select papf.* from per_all_people_f papf where sysdate between papf.effective_start_date and papf.effective_end_date) papf, ( select distinct furg.user_id, count(*) over (partition by furg.user_id) resp_count, max(fr.responsibility_key) over (partition by furg.user_id) max_responsibility_key from fnd_responsibility fr, fnd_user_resp_groups_direct furg where fr.responsibility_id=furg.responsibility_id and fr.application_id=furg.responsibility_application_id ) furg where fu.employee_id=papf.person_id(+) and fu.user_id=furg.user_id(+) and not (furg.resp_count=1 and furg.max_responsibility_key='IRC_EXT_CANDIDATE') order by fu.inactive desc, fu.user_name US User Name 4 15 1=1 nvl(flrf.start_time,nvl(flr.start_time,fl.start_time))>=sysdate-:days Number 30 US Logged in within Days 5 20 1=1 nvl(flrf.start_time,nvl(flr.start_time,fl.start_time))>=:date_from DateTime US Date From 6 30 1=1 nvl(flrf.start_time,nvl(flr.start_time,fl.start_time))<=:date_to DateTime US Date To 7 100 1=1 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 Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Restrict to incremental data When set to 'Yes' and the report is scheduled periodically, records are restricted to creation dates after the previous (parent request_id) request run, in order to retrieve and send incremental data only 8 110 1=1 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 Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Incremental Alert Mode Only show requests since the last scheduled report run 9 120 1=1 fl.user_id<>xxen_util.user_id(:exclude_user_name) LOV FND User Name 8E2FF36EDE8479D2E0530100007F1FF2 Y Y select fu.user_name value, trim(coalesce( trim(papf.first_name||' '||papf.last_name), fu.description, fu.email_address, papf.email_address )||fu.inactive) description from (select case when sysdate between fu.start_date and nvl(fu.end_date,sysdate) then null else ' (inactive)' end inactive, fu.* from fnd_user fu) fu, (select papf.* from per_all_people_f papf where sysdate between papf.effective_start_date and papf.effective_end_date) papf, ( select distinct furg.user_id, count(*) over (partition by furg.user_id) resp_count, max(fr.responsibility_key) over (partition by furg.user_id) max_responsibility_key from fnd_responsibility fr, fnd_user_resp_groups_direct furg where fr.responsibility_id=furg.responsibility_id and fr.application_id=furg.responsibility_application_id ) furg where fu.employee_id=papf.person_id(+) and fu.user_id=furg.user_id(+) and not (furg.resp_count=1 and furg.max_responsibility_key='IRC_EXT_CANDIDATE') order by fu.inactive desc, fu.user_name US Exclude User Name