select
erp.request_id,
xxen_util.user_name(erp.created_by) user_name,
frv.responsibility_name,
nvl(err.report_name,rr.name) report_name,
err.view_name,
coalesce(
(select
flv2.meaning||' - '||flv1.meaning status
from
fnd_lookup_values flv1,
fnd_lookup_values flv2
where
decode(fcr.phase_code,
'P',decode(fcr.hold_flag,'Y','H',case when fcr.requested_start_date>sysdate then 'P' else fcr.status_code end),
'R',decode(fcr.hold_flag,'Y','S',decode(fcr.status_code,'Q','B','I','B',fcr.status_code)),
fcr.status_code)=flv1.lookup_code and
case when flv1.lookup_code in ('H','S','U','M') then 'I' else fcr.phase_code end=flv2.lookup_code and
flv1.lookup_type(+)='CP_STATUS_CODE' and
flv2.lookup_type(+)='CP_PHASE_CODE' and
flv1.language(+)=userenv('lang') and
flv2.language(+)=userenv('lang') and
flv1.view_application_id(+)=0 and
flv2.view_application_id(+)=0 and
flv1.security_group_id(+)=0 and
flv2.security_group_id(+)=0),
nvl2(erp.end_time,'Completed','Active')
) status,
xxen_util.client_time(erp.start_time) start_date,
xxen_util.time((nvl(erp.end_time,sysdate)-erp.start_time)*86400) time,
(nvl(erp.end_time,sysdate)-erp.start_time)*86400 seconds,
erp.submission_source,
ers.operating_unit,
erp.rows_retrieved,
erp.parameter1,
erp.parameter2,
erp.parameter3,
erp.parameter4,
erp.parameter5,
erp.parameter6,
erp.parameter7,
erp.parameter8,
erp.parameter9,
erp.parameter10
from
xxeis.eis_rs_processes erp,
xxeis.eis_rs_sessions ers,
xxeis.eis_rs_reports err,
xxeis.rg_reports rr,
fnd_responsibility_vl frv,
fnd_concurrent_requests fcr
where
1=1 and
erp.session_id=ers.session_id(+) and
case when erp.submission_source like 'Email Distribution~%' then to_number(substr(erp.submission_source,20)) when erp.submission_source in ('eXpress','XL Connect') then erp.report_id end=err.report_id(+) and
decode(nvl(erp.submission_source,'FSG'),'FSG',erp.report_id)=rr.report_id(+) and
ers.responsibility_id=frv.responsibility_id(+) and
ers.application_id=frv.application_id(+) and
erp.request_id=fcr.request_id(+)
order by
erp.process_id desc |
