EIS Overview
Description
Categories: EIS
One-page summary of an EIS eXpress Reporting installation: the installed version, the number of standard and custom reports, and how actively the product is used and developed.
The usage periods count back from the last report run rather than from today, so the figures are also meaningful on a cloned environment.
Active Users ran at least one report. Report Developers created or chan ... more
The usage periods count back from the last report run rather than from today, so the figures are also meaningful on a cloned environment.
Active Users ran at least one report. Report Developers created or chan ... more
select x.metric, x.value, x.last_month "Last Month", x.last_6_months "Last 6 Months", x.last_12_months "Last 12 Months", x.total from ( select 1 seq, 'EIS Version' metric, max(ervc.version_number||nvl2(ervc.patch_number,' (patch '||ervc.patch_number||')',null)) keep (dense_rank last order by ervc.applied_date, ervc.version_number) value, to_number(null) last_month, to_number(null) last_6_months, to_number(null) last_12_months, to_number(null) total from xxeis.eis_rs_version_control ervc where ervc.module='Base engine' union all select 2 seq, 'EIS Version Released' metric, fnd_date.date_to_displaydate(max(ervc.release_date) keep (dense_rank last order by ervc.applied_date, ervc.version_number)) value, null, null, null, null from xxeis.eis_rs_version_control ervc where ervc.module='Base engine' union all select decode(rowgen.column_value,1,3,4) seq, decode(rowgen.column_value,1,'EIS First Installed','EIS Last Patch Applied') metric, fnd_date.date_to_displaydate(decode(rowgen.column_value,1,min(ervc.applied_date),max(ervc.applied_date))) value, null, null, null, null from xxeis.eis_rs_version_control ervc, table(xxen_util.rowgen(2)) rowgen group by rowgen.column_value union all select 5 seq, 'Last Report Run' metric, fnd_date.date_to_displaydate(max(erp.start_time)) value, null, null, null, null from xxeis.eis_rs_processes erp where erp.submission_source in ('eXpress','XL Connect') and erp.report_id<>-999 union all select decode(rowgen.column_value,1,6,7) seq, decode(rowgen.column_value,1,'Standard Reports','Custom Reports') metric, null value, null, null, null, count(case when decode(err.seeded_flag,'Y',1,2)=rowgen.column_value then 1 end) total from xxeis.eis_rs_reports err, table(xxen_util.rowgen(2)) rowgen where err.application_id<>85000 group by rowgen.column_value union all select 7+w.m seq, decode(w.m,1,'Active Users',2,'Report Developers',3,'Report Users without Development',4,'Report Runs',5,'Reports Run',6,'Custom Reports Run',7,'Custom Reports Created',8,'Custom Reports Changed') metric, null value, max(decode(w.w,1,w.value)) last_month, max(decode(w.w,2,w.value)) last_6_months, max(decode(w.w,3,w.value)) last_12_months, max(decode(w.w,4,w.value)) total from ( select v.w, m.column_value m, decode(m.column_value,1,v.active_users,2,v.developers,3,v.run_only_users,4,v.runs,5,v.reports_run,6,v.custom_reports_run,7,v.custom_reports_created,8,v.custom_reports_changed) value from ( select u.w, count(distinct case when u.type='R' then u.user_id end) active_users, count(distinct case when u.type<>'R' then u.user_id end) developers, count(distinct case when u.type='R' and u.developer is null then u.user_id end) run_only_users, sum(case when u.type='R' then u.runs end) runs, count(distinct case when u.type='R' then u.report_id end) reports_run, count(distinct case when u.type='R' and u.custom='Y' then u.report_id end) custom_reports_run, count(distinct case when u.type='C' then u.report_id end) custom_reports_created, count(distinct case when u.type<>'R' then u.report_id end) custom_reports_changed from ( select rowgen.column_value w, t.type, t.user_id, t.report_id, t.runs, (select 'Y' from xxeis.eis_rs_reports err where t.report_id=err.report_id and err.seeded_flag is null) custom, max(case when t.type<>'R' then 'Y' end) over (partition by rowgen.column_value, t.user_id) developer from ( select s.*, max(case when s.type='R' then s.event_date end) over () anchor_date from ( select 'R' type, erp.created_by user_id, erp.report_id, trunc(erp.start_time) event_date, count(*) runs from xxeis.eis_rs_processes erp where erp.submission_source in ('eXpress','XL Connect') and erp.report_id<>-999 group by erp.created_by, erp.report_id, trunc(erp.start_time) union all select distinct decode(d.type,'C','C','U') type, d.user_id, d.report_id, trunc(d.event_date) event_date, null runs from ( select 'C' type, err.created_by user_id, err.report_id, err.creation_date event_date from xxeis.eis_rs_reports err union all select 'U' type, err.last_updated_by, err.report_id, err.last_update_date from xxeis.eis_rs_reports err union all select 'U' type, errc.last_updated_by, errc.report_id, errc.last_update_date from xxeis.eis_rs_report_columns errc union all select 'U' type, errp.last_updated_by, errp.report_id, errp.last_update_date from xxeis.eis_rs_report_parameters errp union all select 'U' type, errch.last_updated_by, errch.report_id, errch.last_update_date from xxeis.eis_rs_report_cond_headers errch ) d where d.user_id<>-1 and d.report_id in (select err.report_id from xxeis.eis_rs_reports err where err.seeded_flag is null and err.application_id<>85000) ) s ) t, table(xxen_util.rowgen(4)) rowgen where rowgen.column_value=4 or t.event_date>add_months(t.anchor_date,-decode(rowgen.column_value,1,1,2,6,12)) ) u group by u.w ) v, table(xxen_util.rowgen(8)) m ) w group by w.m ) x order by x.seq |