ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 714B5276832F5972E053F400140ADC67Y select /*+ no_index(erp eis_rs_processes_idx3 eis_rs_processes_idx4) */ err.report_name, err.report_id "EIS Report ID", era.application_name application, errc.category_name category, to_char(erp.start_time,'yyyy-mm') month, count(*) runs, count(case when erp.start_time-ers.logon_time>1 then 1 end) scheduled_runs, count(*)-count(case when erp.start_time-ers.logon_time>1 then 1 end) ad_hoc_runs, count(case when erp.submission_source='XL Connect' then 1 end) "XL Connect Runs", count(distinct erp.created_by) users, count(case when erp.status in ('E','T') then 1 end) errors, round(avg((erp.end_time-erp.start_time)*86400)) average_seconds, round(avg(erp.rows_retrieved)) average_rows from xxeis.eis_rs_processes erp, xxeis.eis_rs_sessions ers, xxeis.eis_rs_reports err, xxeis.eis_rs_applications era, xxeis.eis_rs_report_categories errc where 1=1 and erp.submission_source in ('eXpress','XL Connect') and erp.session_id=ers.session_id and erp.report_id=err.report_id and err.application_id=era.application_id and err.category_id=errc.category_id group by err.report_name, err.report_id, era.application_name, errc.category_name, to_char(erp.start_time,'yyyy-mm') order by err.report_name, month Rewritten: one row per EIS report and month of the run start with runs, scheduled and ad hoc runs, XL Connect runs, users, errors, average seconds and rows; email distribution jobs no longer counted as runs; parameters Application, Category, Report Name, Seeded, Executed within Days, User, Responsibility; Pivot by Month template. US EIS Execution Summary EIS eXpress usage by report and month: one row per EIS report and calendar month of the run start, counting runs from eXpress and XL Connect. Email distribution jobs are deliveries of a run and are not counted. A run counts as scheduled when it started more than a day after the logon of its EIS session, because scheduled runs keep the session of the user who created the schedule. Errors are runs with status Error or Terminated. Executed within Days counts back from now, so the first month is partial. EIS purges its run history; EIS Execution History shows the earliest retained run. EIS 1=1 1 10 1=1 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 F Soumis dans les jours qui suivent US Application 2 20 1=1 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 F Exclure la distribution d'e-mails US Category 3 30 1=1 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 4 40 1=1 err.seeded_flag='Y' 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 Seeded 5 1=1 err.seeded_flag is null N US Seeded 6 50 1=1 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. 7 60 1=1 erp.created_by=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 User who submitted the run. For scheduled runs, the user who created the schedule. 8 70 1=1 (ers.responsibility_id,ers.application_id) in (select frv.responsibility_id,frv.application_id from fnd_responsibility_vl frv where 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.