ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 5C69E6B8A6FA05C5E0630100007F1397Y select fu.user_name, fu.description, fu.email_address, fu.end_date user_end_date, x.runs, x.scheduled_runs, x.runs-x.scheduled_runs ad_hoc_runs, x.xl_connect_runs "XL Connect Runs", x.reports_run, xxen_util.client_time(x.last_run) last_run, (select err.report_name from xxeis.eis_rs_reports err where x.last_report_id=err.report_id) last_report, x.active_schedules, r.eis_responsibilities "EIS Responsibilities", xxen_util.yes(r.blitz_enabled) blitz_enabled from fnd_user fu, ( select y.user_id, count(y.run_flag) runs, count(y.scheduled_flag) scheduled_runs, count(case when y.submission_source='XL Connect' then 1 end) xl_connect_runs, count(distinct case when y.run_flag='Y' then y.report_id end) reports_run, max(y.start_time) last_run, max(case when y.run_flag='Y' then y.report_id end) keep (dense_rank last order by y.start_time nulls first) last_report_id, count(case when y.run_flag is null then 1 end) active_schedules from ( select erp.created_by user_id, erp.report_id, ers.responsibility_id, ers.application_id, 'Y' run_flag, case when erp.start_time-ers.logon_time>1 then 'Y' end scheduled_flag, erp.submission_source, erp.start_time from xxeis.eis_rs_processes erp, xxeis.eis_rs_sessions ers where 3=3 and erp.submission_source in ('eXpress','XL Connect') and erp.report_id<>-999 and erp.session_id=ers.session_id(+) union all select fcr.requested_by user_id, to_number(fcr.argument1) report_id, fcr.responsibility_id, fcr.responsibility_application_id application_id, null run_flag, null scheduled_flag, null submission_source, null start_time from fnd_concurrent_requests fcr where (fcr.phase_code='P' or fcr.phase_code='R' and fcr.release_class_id is not null) and (fcr.program_application_id,fcr.concurrent_program_id) in ( select fcp.application_id, fcp.concurrent_program_id from fnd_application fa, fnd_executables fe, fnd_concurrent_programs fcp where fa.application_short_name='XXEIS' and fa.application_id=fe.application_id and upper(fe.execution_file_name)='XXEIS.EIS_RSC_PROCESS_REPORTS.SUBMIT_CONC_REPORT' and fe.application_id=fcp.executable_application_id and fe.executable_id=fcp.executable_id ) ) y, xxeis.eis_rs_reports err, xxeis.eis_rs_applications era, xxeis.eis_rs_report_categories errc, fnd_responsibility_vl frv where 2=2 and y.report_id=err.report_id(+) and err.application_id=era.application_id(+) and err.category_id=errc.category_id(+) and y.responsibility_id=frv.responsibility_id(+) and y.application_id=frv.application_id(+) group by y.user_id ) x, ( select furg.user_id, count(fcmf_e.menu_id) eis_responsibilities, max(case when fcmf_b.menu_id is not null then 'Y' end) blitz_enabled from (select distinct furg.user_id, furg.responsibility_id, furg.responsibility_application_id from fnd_user_resp_groups furg) furg, fnd_responsibility fr, (select distinct fcmf.menu_id from fnd_form_functions fff, fnd_compiled_menu_functions fcmf where fff.function_name like 'XXEIS_RSC%' and fff.function_id=fcmf.function_id and fcmf.grant_flag='Y') fcmf_e, (select distinct fcmf.menu_id from fnd_form_functions fff, fnd_compiled_menu_functions fcmf where fff.function_name like 'XXEN_REPORTS%' and fff.function_id=fcmf.function_id and fcmf.grant_flag='Y') fcmf_b where furg.responsibility_id=fr.responsibility_id and furg.responsibility_application_id=fr.application_id and sysdate between fr.start_date and nvl(fr.end_date,sysdate) and fr.menu_id=fcmf_e.menu_id(+) and fr.menu_id=fcmf_b.menu_id(+) and (fcmf_e.menu_id is not null or fcmf_b.menu_id is not null) group by furg.user_id ) r where 1=1 and fu.user_id=x.user_id(+) and fu.user_id=r.user_id(+) and (x.user_id is not null or r.eis_responsibilities>0) order by x.runs desc nulls last, fu.user_name New report: EIS users with runs, scheduled and ad hoc runs, reports run, last run and report, active schedules, the number of responsibilities with EIS functions and whether the user has a Blitz Report enabled responsibility. US EIS Users EIS users: one row per user who ran an EIS report within Executed within Days, owns a pending EIS schedule or holds an active responsibility whose menu grants EIS functions. Runs count eXpress and XL Connect executions. A run counts as scheduled when it starts more than a day after the logon of its EIS session, as scheduled runs keep the session in which the schedule was created. Active Schedules counts the requests EIS Schedules lists for the user: pending requests of the EIS report submission programs and the running request of a recurring schedule. EIS Responsibilities counts the user's active responsibilities whose menu grants EIS functions, including standard responsibilities with the EIS menu added. Blitz Enabled shows that the user holds an active responsibility whose menu grants the Blitz Report function. Application, Category, Report Name and Responsibility restrict the runs and schedules counted and list only the users who have any. EIS purges its run history, so a user without runs may still have used EIS before the earliest retained run. The EIS process table has no date index, so an execution without a User reads it completely. EIS 1=1 2=2 3=3 1 10 2=2 era.application_name=:application LOV custom Y select era.application_name value, era.description from xxeis.eis_rs_applications era order by era.application_name US Application 2 1=1 x.user_id is not null US Application 3 20 2=2 errc.category_name=:category LOV custom Y select distinct errc.category_name value, null description from xxeis.eis_rs_report_categories errc, xxeis.eis_rs_applications era where errc.application_id=era.application_id and (:$flex$.application is null or xxen_util.contains(:$flex$.application,era.application_name)='Y') order by errc.category_name US Category 4 1=1 x.user_id is not null US Category 5 30 2=2 err.report_name=:report_name LOV custom Y select err.report_name value, err.description from xxeis.eis_rs_reports err order by err.report_name US Report Name 6 1=1 x.user_id is not null US Report Name 7 40 1=1 fu.user_name=: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 User who submitted the run. For scheduled runs, the user who created the schedule. 8 2=2 y.user_id=xxen_util.user_id(:user_name) US User 9 50 2=2 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 Responsibility of the EIS session of the run. For scheduled runs, the one the schedule was created in. 10 1=1 x.user_id is not null US Responsibility 11 60 3=3 erp.start_time>=sysdate-:executed_within_days Number 365 US Executed within Days Usage window in days before today, by run start. Blank counts the whole retained history. 12 70 1=1 x.runs>0 LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y US Executed Yes lists the users with a run within the usage window, No the others. 13 1=1 nvl(x.runs,0)=0 N US Executed