QAR : Blitz Report User Access

select
xxen_util.user_name(fpov.level_value) user_name,
xxen_util.display_profile_option_value(fpo.application_id,fpo.profile_option_id,fpov.profile_option_value) blitz_report_access,
xxen_util.client_time(y.first_run_date) first_run_date,
xxen_util.client_time(xrlu.last_run_date) last_run_date,
y.run_count,
y.distinct_reports,
xrv.report_name most_popular,
y.most_popular_count,
xxen_util.user_name(fpov.created_by) created_by,
xxen_util.client_time(fpov.creation_date) creation_date,
xxen_util.user_name(fpov.last_updated_by) last_updated_by,
xxen_util.client_time(fpov.last_update_date) last_update_date
from
fnd_profile_options_vl fpo,
fnd_profile_option_values fpov,
fnd_user fu,
xxen_report_license_users xrlu,
(
select distinct
x.created_by,
x.first_run_date,
x.run_count,
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) most_popular_count
from
(
select distinct
xrr.created_by,
xrr.first_run_date,
xrr.report_id,
count(*) over (partition by xrr.created_by) run_count,
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
(select min(xrr.creation_date) over (partition by xrr.created_by) first_run_date,  xrr.* from xxen_report_runs xrr) xrr
where
nvl(xrr.type,'x')<>'S'
) x
) y,
xxen_reports_v xrv
where
1=1 and
fpo.user_profile_option_name='Blitz Report Access' and
fpo.profile_option_id=fpov.profile_option_id and
fpov.level_id=10004 and
fpov.level_value=fu.user_id and
nvl(fu.end_date,sysdate)>=sysdate and
fu.user_id=xrlu.user_id(+) and
xrlu.user_id=y.created_by(+) and
y.most_popular=xrv.report_id(+)
order by
fpo.user_profile_option_name,
fpov.level_id,
xxen_util.user_name(fpov.level_value)

select xxen_util.user_name(fpov.level_value) user_name, xxen_util.display_profile_option_value(fpo.application_id,fpo.profile_option_id,fpov.profile_option_value) blitz_report_access, xxen_util.client_time(y.first_run_date) first_run_date, xxen_util.client_time(xrlu.last_run_date) last_run_date, y.run_count, y.distinct_reports, xrv.report_name most_popular, y.most_popular_count, xxen_util.user_name(fpov.created_by) created_by, xxen_util.client_time(fpov.creation_date) creation_date, xxen_util.user_name(fpov.last_updated_by) last_updated_by, xxen_util.client_time(fpov.last_update_date) last_update_date from fnd_profile_options_vl fpo, fnd_profile_option_values fpov, fnd_user fu, xxen_report_license_users xrlu, ( select distinct x.created_by, x.first_run_date, x.run_count, 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) most_popular_count from ( select distinct xrr.created_by, xrr.first_run_date, xrr.report_id, count(*) over (partition by xrr.created_by) run_count, 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 (select min(xrr.creation_date) over (partition by xrr.created_by) first_run_date, xrr.* from xxen_report_runs xrr) xrr where nvl(xrr.type,'x')<>'S' ) x ) y, xxen_reports_v xrv where 1=1 and fpo.user_profile_option_name='Blitz Report Access' and fpo.profile_option_id=fpov.profile_option_id and fpov.level_id=10004 and fpov.level_value=fu.user_id and nvl(fu.end_date,sysdate)>=sysdate and fu.user_id=xrlu.user_id(+) and xrlu.user_id=y.created_by(+) and y.most_popular=xrv.report_id(+) order by fpo.user_profile_option_name, fpov.level_id, xxen_util.user_name(fpov.level_value)