EIS Overview

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

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