select
fu.user_name,
fu.description,
fu.email_address,
fu.end_date user_end_date,
x.runs,
x.scheduled_runs,
x.runs-x.scheduled_runs ad_hoc_runs,
x.xl_connect_runs "XL Connect Runs",
x.reports_run,
xxen_util.client_time(x.last_run) last_run,
(select err.report_name from xxeis.eis_rs_reports err where x.last_report_id=err.report_id) last_report,
x.active_schedules,
r.eis_responsibilities "EIS Responsibilities",
xxen_util.yes(r.blitz_enabled) blitz_enabled
from
fnd_user fu,
(
select
y.user_id,
count(y.run_flag) runs,
count(y.scheduled_flag) scheduled_runs,
count(case when y.submission_source='XL Connect' then 1 end) xl_connect_runs,
count(distinct case when y.run_flag='Y' then y.report_id end) reports_run,
max(y.start_time) last_run,
max(case when y.run_flag='Y' then y.report_id end) keep (dense_rank last order by y.start_time nulls first) last_report_id,
count(case when y.run_flag is null then 1 end) active_schedules
from
(
select
erp.created_by user_id,
erp.report_id,
ers.responsibility_id,
ers.application_id,
'Y' run_flag,
case when erp.start_time-ers.logon_time>1 then 'Y' end scheduled_flag,
erp.submission_source,
erp.start_time
from
xxeis.eis_rs_processes erp,
xxeis.eis_rs_sessions ers
where
3=3 and
erp.submission_source in ('eXpress','XL Connect') and
erp.report_id<>-999 and
erp.session_id=ers.session_id(+)
union all
select
fcr.requested_by user_id,
to_number(fcr.argument1) report_id,
fcr.responsibility_id,
fcr.responsibility_application_id application_id,
null run_flag,
null scheduled_flag,
null submission_source,
null start_time
from
fnd_concurrent_requests fcr
where
(fcr.phase_code='P' or fcr.phase_code='R' and fcr.release_class_id is not null) and
(fcr.program_application_id,fcr.concurrent_program_id) in
(
select
fcp.application_id,
fcp.concurrent_program_id
from
fnd_application fa,
fnd_executables fe,
fnd_concurrent_programs fcp
where
fa.application_short_name='XXEIS' and
fa.application_id=fe.application_id and
upper(fe.execution_file_name)='XXEIS.EIS_RSC_PROCESS_REPORTS.SUBMIT_CONC_REPORT' and
fe.application_id=fcp.executable_application_id and
fe.executable_id=fcp.executable_id
)
) y,
xxeis.eis_rs_reports err,
xxeis.eis_rs_applications era,
xxeis.eis_rs_report_categories errc,
fnd_responsibility_vl frv
where
2=2 and
y.report_id=err.report_id(+) and
err.application_id=era.application_id(+) and
err.category_id=errc.category_id(+) and
y.responsibility_id=frv.responsibility_id(+) and
y.application_id=frv.application_id(+)
group by
y.user_id
) x,
(
select
furg.user_id,
count(fcmf_e.menu_id) eis_responsibilities,
max(case when fcmf_b.menu_id is not null then 'Y' end) blitz_enabled
from
(select distinct furg.user_id, furg.responsibility_id, furg.responsibility_application_id from fnd_user_resp_groups furg) furg,
fnd_responsibility fr,
(select distinct fcmf.menu_id from fnd_form_functions fff, fnd_compiled_menu_functions fcmf where fff.function_name like 'XXEIS_RSC%' and fff.function_id=fcmf.function_id and fcmf.grant_flag='Y') fcmf_e,
(select distinct fcmf.menu_id from fnd_form_functions fff, fnd_compiled_menu_functions fcmf where fff.function_name like 'XXEN_REPORTS%' and fff.function_id=fcmf.function_id and fcmf.grant_flag='Y') fcmf_b
where
furg.responsibility_id=fr.responsibility_id and
furg.responsibility_application_id=fr.application_id and
sysdate between fr.start_date and nvl(fr.end_date,sysdate) and
fr.menu_id=fcmf_e.menu_id(+) and
fr.menu_id=fcmf_b.menu_id(+) and
(fcmf_e.menu_id is not null or fcmf_b.menu_id is not null)
group by
furg.user_id
) r
where
1=1 and
fu.user_id=x.user_id(+) and
fu.user_id=r.user_id(+) and
(x.user_id is not null or r.eis_responsibilities>0)
order by
x.runs desc nulls last,
fu.user_name |
