select
rrs.stat_id,
xxen_util.user_name(rrs.user_id) user_name,
frv.responsibility_name responsibility,
decode(rrs.report_id,-1,'output generation ('||rrs.output_type||')',rr.report_name) report_name,
decode(rr.report_type,'USER','Adhoc','SEEDED','Seeded Adhoc','DASH_BOARD','BI Publisher','VAR','PL/SQL Custom','FLEX','Dashboard','SEEDED_VAR','Seeded PL/SQL Custom',rr.report_type) report_type,
y.view_name,
y.view_alias,
rrs.params_text,
xxen_util.client_time(rrs.start_time) start_time,
xxen_util.client_time(rrs.end_time_) end_time,
xxen_util.time(rrs.seconds) time,
rrs.seconds,
rrs.row_count,
rrs.row_count/xxen_util.zero_to_null(rrs.seconds) rows_second,
rrs.run_type,
rrs.output_type,
rr.report_type,
rrs.error_stack,
decode(rrs.report_id,-1,null,rrs.report_id) report_id,
y.view_id,
y.view_count,
decode(rrs.template_id,-1,null,rrs.template_id) template_id,
rrs.user_id
from
(select (nvl(nvl2(rrs.row_count,rrs.end_time,null),sysdate)-rrs.start_time)*86400 seconds, nvl2(rrs.row_count,rrs.end_time,null) end_time_, rrs.* from rx_report_stats rrs) rrs,
rx_reports rr,
fnd_responsibility_vl frv,
(
select distinct
x.report_id,
listagg(rv.view_name,', ') within group (order by x.view_id) over (partition by x.report_id) view_name,
listagg(rv.view_alias,', ') within group (order by x.view_id) over (partition by x.report_id) view_alias,
listagg(x.view_id,',') within group (order by x.view_id) over (partition by x.report_id) view_id,
count(x.view_id) over (partition by x.report_id) view_count
from
(
select distinct
rrc.report_id,
rvc.view_id
from
rx_report_columns rrc,
rx_view_columns rvc
where
rrc.view_column_id=rvc.view_column_id
) x,
rx_views rv
where
x.view_id=rv.view_id
) y
where
1=1 and
rrs.report_id=rr.report_id(+) and
rrs.responsibility_id=frv.responsibility_id(+) and
rrs.application_id=frv.application_id(+) and
rrs.report_id=y.report_id(+)
order by
rrs.start_time desc,
rrs.stat_id desc |
