Longest running concurrents request sql query

/* Formatted on 7/27/2017 4:21:41 PM (QP5 v5.256.13226.35510) */

--- ALL CONCURRENT REQUESTS DETAILS WITH DETAILL TIMES SPENT
 
  SELECT request_id,
         parent_request_id,
         --fcpt.user_concurrent_program_name Request_Name,
         fcpt.user_concurrent_program_name program_name,
         DECODE (fcr.phase_code,
                 'C', 'Completed',
                 'I', 'Incactive',
                 'P', 'Pending',
                 'R', 'Running')
            phase,
         DECODE (fcr.status_code,
                 'D', 'Cancelled',
                 'U', 'Disabled',
                 'E', 'Error',
                 'M', 'No Manager',
                 'R', 'Normal',
                 'I', 'Normal',
                 'C', 'Normal',
                 'H', 'On Hold',
                 'W', 'Paused',
                 'B', 'Resuming',
                 'P', 'Scheduled',
                 'Q', 'Standby',
                 'S', 'Suspended',
                 'X', 'Terminated',
                 'T', 'Terminating',
                 'A', 'Waiting',
                 'Z', 'Waiting',
                 'G', 'Warning',
                 'N/A')
            status,
           ROUND ( (fcr.actual_completion_date - fcr.actual_start_date), 3)
         * 1440
            AS Run_Time_IN_MINUTES,
           
         ROUND (
            AVG (
                 ROUND (
                    TO_NUMBER (actual_start_date - fcr.requested_start_date),
                    3)
               * 1440),
            2)
            wait_time_IN_MINUTES,
           
         
         floor(((fcr.actual_completion_date-fcr.actual_start_date)
        *24*60*60)/3600)
        || ' hours ' ||
        floor((((fcr.actual_completion_date-fcr.actual_start_date)
        *24*60*60) -
        floor(((fcr.actual_completion_date-fcr.actual_start_date)
        *24*60*60)/3600)*3600)/60)
        || ' minutes ' ||
        round((((fcr.actual_completion_date-fcr.actual_start_date)
        *24*60*60) -
        floor(((fcr.actual_completion_date-fcr.actual_start_date)
        *24*60*60)/3600)*3600 -
        (floor((((fcr.actual_completion_date-fcr.actual_start_date)
        *24*60*60) -
        floor(((fcr.actual_completion_date-fcr.actual_start_date)
        *24*60*60)/3600)*3600)/60)*60) ))
        || ' secs ' RUNNING_TIME_DETAIL,
       
          
         fu.User_Name Requestor,
         --fcr.argument_text parameters,
         TO_CHAR (fcr.requested_start_date, 'MM/DD HH24:mi:SS') requested_start,
         TO_CHAR (actual_start_date, 'MM/DD/YY HH24:mi:SS') ACT_START,
         TO_CHAR (actual_completion_date, 'MM/DD/YY HH24:mi:SS') ACT_COMP,
        
--         TRUNC((actual_completion_date -  actual_start_date) * 24 * 60 * 60) SECONDS , 
--          TRUNC((actual_completion_date -  actual_start_date) * 24 * 60 ) MINUTES , 
        
         fcr.completion_text
    FROM apps.fnd_concurrent_requests fcr,
         apps.fnd_concurrent_programs fcp,
         apps.fnd_concurrent_programs_tl fcpt,
         apps.fnd_user fu
   WHERE     1 = 1
         -- and fu.user_name = 'JMOHANTY'
         -- and fcr.request_id = 1565261
         --and fcpt.user_concurrent_program_name = 'Autoinvoice Import Program'
         AND fcr.concurrent_program_id = fcp.concurrent_program_id
         AND fcp.concurrent_program_id = fcpt.concurrent_program_id
         AND fcr.program_application_id = fcp.application_id
         AND fcp.application_id = fcpt.application_id
         AND fcr.requested_by = fu.user_id
         AND fcpt.language = 'US'
         --AND fcr.actual_start_date LIKE SYSDATE
         and fcr.requested_start_date >= to_date('20-JUN-2017')
         and fcr.phase_code = 'C'


-- and hold_flag = 'Y'
-- and fcr.status_code = 'C'
GROUP BY request_id,
         parent_request_id,
         fcpt.user_concurrent_program_name,
         fcr.requested_start_date,
         fu.User_Name,
         fcr.argument_text,
         fcr.actual_completion_date,
         fcr.actual_start_date,
         fcr.phase_code,
         fcr.status_code,
         fcr.resubmit_interval,
         fcr.completion_text,
         fcr.resubmit_interval,
         fcr.resubmit_interval_unit_code,
         fcr.description
ORDER BY Run_Time_IN_MINUTES DESC NULLS LAST
;



----TOP 100 LONGEST TIMES TAKING REQUESTS

  SELECT RANK1 SR#,
         program_name,
         Run_Time_IN_SECONDS,
         Run_Time_IN_MINUTES,
         TO_CHAR (TO_DATE (ROUND (Run_Time_IN_SECONDS), 'SSSSS'), 'HH24:MI:SS')
            RUNNING_TIME
    FROM (  SELECT fcpt.user_concurrent_program_name program_name,
                   ROW_NUMBER ()
                   OVER (
                      ORDER BY
                         (  ROUND (
                               MAX (
                                    fcr.actual_completion_date
                                  - fcr.actual_start_date),
                               6)
                          * 1440
                          * 60) DESC)
                      RANK1,
                   (  ROUND (
                         MAX (fcr.actual_completion_date - fcr.actual_start_date),
                         6)
                    * 1440)
                      Run_Time_IN_MINUTES,
                   (  ROUND (
                         MAX (fcr.actual_completion_date - fcr.actual_start_date),
                         6)
                    * 1440
                    * 60)
                      Run_Time_IN_SECONDS
             FROM apps.fnd_concurrent_requests fcr,
                   apps.fnd_concurrent_programs fcp,
                   apps.fnd_concurrent_programs_tl fcpt,
                   apps.fnd_user fu
             WHERE     1 = 1
                   -- and fu.user_name = 'JMOHANTY'
                   -- and fcr.request_id = 1565261
                   --and fcpt.user_concurrent_program_name = 'Autoinvoice Import Program'
                   AND fcr.concurrent_program_id = fcp.concurrent_program_id
                   AND fcp.concurrent_program_id = fcpt.concurrent_program_id
                   AND fcr.program_application_id = fcp.application_id
                   AND fcp.application_id = fcpt.application_id
                   AND fcr.requested_by = fu.user_id
                   AND fcpt.language = 'US'
                   --AND fcr.actual_start_date LIKE SYSDATE
                   AND fcr.requested_start_date < TRUNC(SYSDATE)
                   AND fcr.phase_code = 'C'
          -- and hold_flag = 'Y'
          -- and fcr.status_code = 'C'
          GROUP BY fcpt.user_concurrent_program_name
            HAVING (  ROUND (
                         MAX (
                            fcr.actual_completion_date - fcr.actual_start_date),
                         6)
                    * 1440
                    * 60)
                      IS NOT NULL
          ORDER BY Run_Time_IN_SECONDS DESC NULLS LAST) TBL
   WHERE Run_Time_IN_SECONDS IS NOT NULL AND RANK1 < 101
ORDER BY RANK1;