select
err.report_name,
x.eis_report_id "EIS Report ID",
era.application_name application,
errc.category_name category,
err.view_name,
x.source submission_source,
xxen_util.user_name(x.created_by) user_name,
frv.responsibility_name responsibility,
ers.operating_unit,
xxen_util.client_time(x.start_time) start_time,
xxen_util.client_time(x.end_time) end_time,
xxen_util.time(x.seconds) time,
x.seconds,
case x.status_code
when 'C' then xxen_util.meaning('C','CP_PHASE_CODE',0)
when 'E' then xxen_util.meaning('E','CP_STATUS_CODE',0)
when 'T' then xxen_util.meaning('X','CP_STATUS_CODE',0)
when 'P' then xxen_util.meaning('R','CP_PHASE_CODE',0)
when 'U' then xxen_util.meaning('P','CP_PHASE_CODE',0)
end status,
x.rows_retrieved,
xxen_util.yes(case when x.start_time-ers.logon_time>1 then 'Y' end) scheduled,
(
select
ltrim(
max(case when erprd.select_flag='Y' and (erprd.email_address is not null or erprd.user_group_id is not null) then ', Email' end)||
max(case when erprd.ftp_site_id is not null then ', FTP' end)||
max(case when erprd.file_system is not null then ', File System' end),
', ')
from
xxeis.eis_rs_process_rpt_distribute erprd
where
x.process_id=erprd.process_id
) delivery,
x.request_id,
x.process_id "EIS Process ID",
x.parameters
from
(
select
case when erp.submission_source like 'Email Distribution~%' then to_number(substr(erp.submission_source,20)) else erp.report_id end eis_report_id,
case when erp.submission_source like 'Email Distribution~%' then 'Email Distribution' else erp.submission_source end source,
decode(erp.status,'R','C',erp.status) status_code,
round((erp.end_time-erp.start_time)*86400) seconds,
erp.*
from
xxeis.eis_rs_processes erp
where
(erp.submission_source in ('eXpress','XL Connect') and erp.report_id<>-999 or erp.submission_source like 'Email Distribution~%')
) x,
xxeis.eis_rs_sessions ers,
xxeis.eis_rs_reports err,
xxeis.eis_rs_applications era,
xxeis.eis_rs_report_categories errc,
fnd_responsibility_vl frv
where
1=1 and
x.session_id=ers.session_id(+) and
x.eis_report_id=err.report_id(+) and
err.application_id=era.application_id(+) and
err.category_id=errc.category_id(+) and
ers.responsibility_id=frv.responsibility_id(+) and
ers.application_id=frv.application_id(+)
order by
x.start_time desc,
x.process_id desc |
