ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 71D38CE714E46227E053F400140A79E4Y select err.report_name, x.eis_report_id "EIS Report ID", era.application_name application, errc.category_name category, err.view_name, x.source submission_source, xxen_util.user_name(x.created_by) user_name, frv.responsibility_name responsibility, ers.operating_unit, xxen_util.client_time(x.start_time) start_time, xxen_util.client_time(x.end_time) end_time, xxen_util.time(x.seconds) time, x.seconds, case x.status_code when 'C' then xxen_util.meaning('C','CP_PHASE_CODE',0) when 'E' then xxen_util.meaning('E','CP_STATUS_CODE',0) when 'T' then xxen_util.meaning('X','CP_STATUS_CODE',0) when 'P' then xxen_util.meaning('R','CP_PHASE_CODE',0) when 'U' then xxen_util.meaning('P','CP_PHASE_CODE',0) end status, x.rows_retrieved, xxen_util.yes(case when x.start_time-ers.logon_time>1 then 'Y' end) scheduled, ( select ltrim( max(case when erprd.select_flag='Y' and (erprd.email_address is not null or erprd.user_group_id is not null) then ', Email' end)|| max(case when erprd.ftp_site_id is not null then ', FTP' end)|| max(case when erprd.file_system is not null then ', File System' end), ', ') from xxeis.eis_rs_process_rpt_distribute erprd where x.process_id=erprd.process_id ) delivery, x.request_id, x.process_id "EIS Process ID", x.parameters from ( select case when erp.submission_source like 'Email Distribution~%' then to_number(substr(erp.submission_source,20)) else erp.report_id end eis_report_id, case when erp.submission_source like 'Email Distribution~%' then 'Email Distribution' else erp.submission_source end source, decode(erp.status,'R','C',erp.status) status_code, round((erp.end_time-erp.start_time)*86400) seconds, erp.* from xxeis.eis_rs_processes erp where (erp.submission_source in ('eXpress','XL Connect') and erp.report_id<>-999 or erp.submission_source like 'Email Distribution~%') ) x, xxeis.eis_rs_sessions ers, xxeis.eis_rs_reports err, xxeis.eis_rs_applications era, xxeis.eis_rs_report_categories errc, fnd_responsibility_vl frv where 1=1 and x.session_id=ers.session_id(+) and x.eis_report_id=err.report_id(+) and err.application_id=era.application_id(+) and err.category_id=errc.category_id(+) and ers.responsibility_id=frv.responsibility_id(+) and ers.application_id=frv.application_id(+) order by x.start_time desc, x.process_id desc Rewritten: one row per eXpress or XL Connect run, optionally per distribution job with the report id taken from the submission source; FSG runs and runs of report id -999 dropped; new columns EIS Report ID, Application, Category, End Time, Scheduled, Delivery, EIS Process ID and the Parameters text instead of parameter1..10; status from the standard request lookups; window on start time, default 30 days; parameters Application, Category, Report Name, User, Responsibility, Submission Source, Scheduled, Status replace View Name and Submitted by User. US EIS Execution History EIS report runs, one row per run from the EIS web interface (eXpress) or the Excel add-in (XL Connect). With Exclude Email Distribution set to No, the distribution jobs that deliver a run's output by email, FTP or file system are listed as separate rows as well. A run is flagged Scheduled when it started more than one day after the logon of its EIS session, as scheduled runs keep reusing the session in which the schedule was created. Delivery shows the channels set up for a run: Email for selected recipients or user groups, FTP and File System. Distribution job rows have no Rows Retrieved, Delivery or Parameters. EIS purges its run history but keeps the distribution jobs, which are then the only evidence of earlier usage. The EIS process table has no date index, so an execution without a User reads it completely. 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 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 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 x.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. 5 50 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 Responsibility of the EIS session of the run. For scheduled runs, the one the schedule was created in. 6 60 1=1 x.start_time>=sysdate-:submitted_within_days Number 30 F Soumis dans les jours qui suivent US Submitted within Days 7 70 1=1 x.source=:submission_source LOV custom Y select 'eXpress' value, null description from dual union all select 'XL Connect' value, null description from dual union all select 'Email Distribution' value, null description from dual US Submission Source 8 80 1=1 x.start_time-ers.logon_time>1 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 Scheduled 9 1=1 x.start_time-ers.logon_time<=1 N US Scheduled 10 90 1=1 x.status_code=:status LOV custom Y select 'C' id, xxen_util.meaning('C','CP_PHASE_CODE',0) value, null description from dual union all select 'E' id, xxen_util.meaning('E','CP_STATUS_CODE',0) value, null description from dual union all select 'T' id, xxen_util.meaning('X','CP_STATUS_CODE',0) value, null description from dual union all select 'P' id, xxen_util.meaning('R','CP_PHASE_CODE',0) value, null description from dual union all select 'U' id, xxen_util.meaning('P','CP_PHASE_CODE',0) value, null description from dual US Status 11 100 1=1 x.source<>'Email Distribution' 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 decode(:$flex$.submission_source,'Email Distribution',null,'Y') F Exclure la distribution d'e-mails US Exclude Email Distribution Excludes the distribution jobs, which EIS records as separate rows for the email, FTP or file system delivery of a run.