Oracle EBS 并发请求（Concurrent Request ）相关SQL命令2

--查找运行请求时间，参数等（可以是某用户的，某个报表）
SELECT
    c.user_name,
    papf.full_name,
    b.user_concurrent_program_name,
    a.request_date,
    a.argument_text,
    ( a.actual_completion_date - a.actual_start_date ) * 24 * 60 minutes,
    a.actual_start_date,
    a.actual_completion_date,
    a.request_id,
    a.outfile_name
FROM
    fnd_concurrent_requests    a,
    fnd_concurrent_programs_vl b,
    fnd_user                   c,
    per_all_people_f           papf
WHERE
        a.concurrent_program_id = b.concurrent_program_id
    AND a.requested_by = c.user_id
    AND c.user_name = papf.employee_number (+)
    AND a.actual_completion_date IS NOT NULL
    AND b.user_concurrent_program_name = '你的程序名称' --- like '%XXX%'
    AND c.user_name = ' 你要找的用户的'
    AND a.request_date <= TO_DATE('2005-03-01 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
    AND a.request_date >= TO_DATE('2005-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
    AND a.request_id > 2254198 ---为了提高速度，选一个合适时间点的ID

--并发执行的详细信息
SELECT DISTINCT
    fcr.request_id,
    fcr.actual_start_date,
    fcr.actual_completion_date,
    fcr.actual_completion_date - fcr.actual_start_date as elapsed_time ,
    decode(fcr.phase_code, 'C', 'Completed', 'I', 'Inactive',
           'P', 'Pending', 'R', 'Running', 'N/A')                                                                                                                                                                                                                                                                                                                        phase_code,
    decode(fcr.status_code, 'A', 'Waiting', 'B', 'Resuming',
           'C', 'Normal', 'D', 'Cancelled', 'E',
           'Errored', 'F', 'Scheduled', 'G', 'Warning',
           'H', 'On Hold', 'I', 'Normal', 'M',
           'No Manager', 'Q', 'Standby', 'R', 'Normal',
           'S', 'Suspended', 'T', 'Terminating', 'U',
           'Disabled', 'W', 'Paused', 'X', 'Terminated',
           'Z', 'Waiting', 'N/A')                                                                                                                                                                                                                                                                                                                                        status_code,
    fcr.outfile_name,
    fcr.logfile_name,
    fcr.number_of_arguments,
    fcr.argument_text,
    frt.responsibility_name,
    fav.application_name,
    fav.application_short_name                                                                                                                                                                                                                                                                                                                                           appl_short_name,
    fu.user_name,
    fu.description                                                                                                                                                                                                                                                                                                                                                       user_description,
    fu.start_date                                                                                                                                                                                                                                                                                                                                                        user_start_date,
    fcp.user_concurrent_program_name,
    fcp.concurrent_program_name                                                                                                                                                                                                                                                                                                                                          short_name,
    fe.executable_name,
    decode(fe.execution_method_code, 'B', 'Request Set Stage Function', 'Q', 'SQL*Plus',
           'H', 'Host', 'L', 'SQL*Loader', 'A',
           'Spawned', 'I', 'PL/SQL Stored Procedure', 'P', 'Oracle Reports',
           'S', 'Immediate', 'N/A')                                                                                                                                                                                                                                                                                                                                      execution_method,
    fe.execution_file_name
FROM
    fnd_concurrent_requests    fcr,
    fnd_user                   fu,
    fnd_application_vl         fav,
    fnd_responsibility_tl      frt,
    fnd_concurrent_programs_vl fcp,
    fnd_executables            fe
WHERE
        fcr.requested_by = fu.user_id
    AND fcr.concurrent_program_id = fcp.concurrent_program_id
    AND fcr.responsibility_id = frt.responsibility_id
    AND fcr.responsibility_application_id = fav.application_id
    AND fcp.executable_id = fe.executable_id
    --使用request_id 作为检索条件
    AND fcr.request_id = :request_id
    --执行中的处理作为检索条件
    --AND fcr.actual_completion_date is null
    --当天的处理作为检索条件
    --AND trunc(fcr.request_date) like trunc(sysdate)
    --处理程序名作为检索条件
    --fcp.user_concurrent_program_name like 'XXX%'
    --根据情况添加其他条件

SELECT
    f.user_name,
    a.request_id,
    a.concurrent_program_id,
    a.responsibility_id,
    a.phase_code,
    a.status_code,
    b.os_process_id,
    vs.sid,
    vs.serial#,
    vs.sql_id,
    vp.spid,
    to_char(request_date,’DD-MON-YY hh24:mi:ss’) request_date
,(NVL(a.actual_completion_date,SYSDATE)-a.actual_start_date)*1440 
,c.concurrent_program_name||’ – ‘||c2.user_concurrent_program_name
FROM
    applsys.fnd_concurrent_requests    a,
    applsys.fnd_concurrent_processes   b,
    applsys.fnd_concurrent_queues      q,
    applsys.fnd_concurrent_programs_tl c2,
    applsys.fnd_concurrent_programs    c,
    applsys.fnd_user                   f,
    v$session                          vs,
    v$process                          vp
WHERE
        a.controlling_manager = b.concurrent_process_id
    AND a.concurrent_program_id = c.concurrent_program_id
        AND a.program_application_id = c.application_id
            AND c2.concurrent_program_id = c.concurrent_program_id
                AND c2.application_id = c.application_id
                    AND a.phase_code IN ( ‘i’, ’p’, ’r’, ’t’ )
                        AND a.requested_by = f.user_id
                            AND b.queue_application_id = q.application_id
                                AND b.concurrent_queue_id = q.concurrent_queue_id
                                    AND c2.language = ‘us’
                                        AND a.oracle_process_id = vp.spid
                                            AND vs.paddr = vp.addr