Query to Find Concurrent Request Running more than 2 hrs

Query to Find Concurrent Request Running more than 2 hrs

Script:

set pagesize 1000

set pause off

set linesize 150

column fcr.request_id format a10 heading 'RQST_ID'

column fu.user_name format a10 heading 'Username'

column fr.responsibility_name format a35 heading 'Resp Name'

column fcp.user_concurrent_program_name format a40 heading 'Program Name'

column fcr.actual_start_date format a30 heading 'Start Date'

column fcr.status.code heading 'Status'

column fcr.actual_start_date format a10 heading 'Runtime Minutes'

column fcr.os_process_id format a15 heading 'SID, SERIAL'

column fcr.os_process_id format a10 heading 'SPID'

column fcr.os_process_id format a10 heading 'OS PID'

prompt

SELECT   fcr.request_id rqst_id

        ,fu.user_name

        ,fr.responsibility_name

        ,fcp.user_concurrent_program_name program_name

        ,TO_CHAR (fcr.actual_start_date, 'DD-MON-YYYY HH24:MI:SS')start_datetime

        ,DECODE (fcr.status_code, 'R', 'R:Running', fcr.status_code) status

        ,ROUND (((SYSDATE - fcr.actual_start_date) * 60 * 24), 2) runtime_min

        ,fcr.oracle_process_id "SPID"

        ,fcr.os_process_id os_pid

    FROM apps.fnd_concurrent_requests fcr

        ,apps.fnd_user fu

        ,apps.fnd_responsibility_vl fr

        ,apps.fnd_concurrent_programs_vl fcp

   WHERE fcr.status_code LIKE 'R'

     AND fu.user_id = fcr.requested_by

     AND fr.responsibility_id = fcr.responsibility_id

     AND fcr.concurrent_program_id = fcp.concurrent_program_id

     AND fcr.program_application_id = fcp.application_id

     AND ROUND (((SYSDATE - fcr.actual_start_date) * 60 * 24), 2) > 120

ORDER BY fcr.concurrent_program_id

        ,request_id DESC;

If you like please follow and comment