select
xxen_util.user_name(xrlu.user_id) user_name,
xrlu.last_run_date,
y.report_executions,
y.distinct_reports,
xrv.report_name most_popular,
y.count
from
xxen_report_license_users xrlu,
fnd_user fu,
(
select distinct
x.created_by,
x.report_executions,
x.distinct_reports,
max(x.report_id) keep (dense_rank last order by x.count) over (partition by x.created_by) most_popular,
max(x.count) keep (dense_rank last order by x.count) over (partition by x.created_by) count
from
(
select distinct
xrr.created_by,
xrr.report_id,
count(*) over (partition by xrr.created_by) report_executions,
count(distinct xrr.report_id) over (partition by xrr.created_by) distinct_reports,
count(*) over (partition by xrr.created_by, xrr.report_id) count
from
xxen_report_runs xrr
where
xrr.creation_date>sysdate-60
) x
) y,
xxen_reports_v xrv
where
xrlu.last_run_date>sysdate-60 and
xrlu.user_id=fu.user_id and
nvl(fu.end_date,sysdate)>=sysdate and
xrlu.user_id=y.created_by(+) and
y.most_popular=xrv.report_id(+)
order by
xrlu.last_run_date desc |
select
xxen_util.user_name(xrlu.user_id) user_name,
xrlu.last_run_date,
y.report_executions,
y.distinct_reports,
xrv.report_name most_popular,
y.count
from
xxen_report_license_users xrlu,
fnd_user fu,
(
select distinct
x.created_by,
x.report_executions,
x.distinct_reports,
max(x.report_id) keep (dense_rank last order by x.count) over (partition by x.created_by) most_popular,
max(x.count) keep (dense_rank last order by x.count) over (partition by x.created_by) count
from
(
select distinct
xrr.created_by,
xrr.report_id,
count(*) over (partition by xrr.created_by) report_executions,
count(distinct xrr.report_id) over (partition by xrr.created_by) distinct_reports,
count(*) over (partition by xrr.created_by, xrr.report_id) count
from
xxen_report_runs xrr
where
xrr.creation_date>sysdate-60
) x
) y,
xxen_reports_v xrv
where
xrlu.last_run_date>sysdate-60 and
xrlu.user_id=fu.user_id and
nvl(fu.end_date,sysdate)>=sysdate and
xrlu.user_id=y.created_by(+) and
y.most_popular=xrv.report_id(+)
order by
xrlu.last_run_date desc |
