SELECT wk.*,
decode(wk.description,
NULL,
pt.user_concurrent_program_name,
wk.description || ' (' || pt.user_concurrent_program_name || ')') program,
pt.user_concurrent_program_name user_concurrent_program_name,
fe.execution_method_code,
decode(fe.execution_method_code,
'H',
'主机',
'S',
'立即',
'J',
'Java 存储过程',
'K',
'Java 并发程序',
'M',
'多语言功能',
'P',
'Oracle Reports',
'I',
'PL/SQL 存储过程',
'B',
'请求集阶段函数',
'A',
'派生',
'L',
'SQL*Loader 程序',
'Q',
'SQL*Plus',
'E',
'Perl 并发程序',
'Others') execution_method_name,
fe.execution_file_name,
fi.last_run_date,
fi.avg_run_time,
fi.max_run_time,
fi.min_run_time
FROM (SELECT r.ROWID row_id,
r.request_id,
r.request_date,
r.requested_by,
r.requested_start_date,
r.responsibility_application_id,
r.responsibility_id,
r.parent_request_id,
r.controlling_manager,
r.description,
r.actual_start_date,
r.actual_completion_date,
r.completion_text,
r.argument_text,
r.program_application_id,
r.concurrent_program_id,
(r.actual_completion_date - r.actual_start_date) * (86400 / 60) minute1
FROM fnd_concurrent_requests r
WHERE 1 = 1 --r.request_id IN (1122991, 1109743, 1109563)
AND (r.actual_completion_date - r.actual_start_date) * 86400 > 60 * 30) wk,
fnd_concurrent_programs_tl pt,
fnd_concurrent_programs pb,
fnd_executables_vl fe,
fnd_conc_prog_onsite_info fi
WHERE 2 = 2 --(SQL*Plus,PL/SQL 存储过程,Oracle Reports,Java 存储过程,Java 并发程序)
AND pb.application_id = wk.program_application_id
AND pb.concurrent_program_id = wk.concurrent_program_id
AND pb.application_id = pt.application_id
AND pb.concurrent_program_id = pt.concurrent_program_id
AND pt.LANGUAGE = userenv('LANG')
AND fe.executable_id = pb.executable_id
AND fi.program_application_id = wk.program_application_id
AND fi.concurrent_program_id = wk.concurrent_program_id
ORDER BY pt.user_concurrent_program_name,
wk.minute1 DESC;
转载于:https://blog.51cto.com/7642644/1783758