Number Of Minutes the Concurrent Request ran

The following query can be executed to identify requests based on the number of minutes the request ran:

conc_stat.sql

set echo off

set feedback off

set linesize 97

set verify off

col request_id format 9999999999    heading "Request ID"

     col exec_time format 999999999 heading "Exec Time|(Minutes)"

    col start_date format a10       heading "Start Date"

     col conc_prog format a20       heading "Conc Program Name"

col user_conc_prog format a40 trunc heading "User Program Name"

spool long_running_cr.lst

SELECT 

   fcr.request_id request_id,

   TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) exec_time,

   fcr.actual_start_date start_date,

   fcp.concurrent_program_name conc_prog,

   fcpt.user_concurrent_program_name user_conc_prog

FROM

  fnd_concurrent_programs fcp,

  fnd_concurrent_programs_tl fcpt,

  fnd_concurrent_requests fcr

WHERE 

   TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) > NVL('&min',45)

and 

   fcr.concurrent_program_id = fcp.concurrent_program_id

and 

   fcr.program_application_id = fcp.application_id

and 

   fcr.concurrent_program_id = fcpt.concurrent_program_id

and 

   fcr.program_application_id = fcpt.application_id

and 

   fcpt.language = USERENV('Lang')

ORDER BY 

   TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) desc;

          

spool off

Note that this script prompts you for the number of minutes. The output from this query with a value of 60 produced the following output on my database. Here we can see important details about currently-running requests, including the request ID, the execution time, the user who submitted the program and the name of the program.

Enter value for  min: 60

            Exec Time 

         

 Request ID (Minutes)  Start Date Conc Program Name    User Program Name

----------- ---------- ---------- -------------------- --------------------------------------

    1445627        218 01-SEP-02  MWCRMRGA             Margin Analysis Report(COGS Breakups)

     444965        211 03-JUL-01  CSTRBICR5G           Cost Rollup - No Report GUI

    1418262        208 22-AUG-02  MWCRMRGA             Margin Analysis Report(COGS Breakups)

     439443        205 28-JUN-01  CSTRBICR5G           Cost Rollup - No Report GUI

     516074        178 10-AUG-01  CSTRBICR6G           Cost Rollup - Print Report GUI

    1417551        164 22-AUG-02  MWCRMRGA             Margin Analysis Report(COGS Breakups)

    1449973        160 03-SEP-02  MWCRMRGA             Margin Analysis Report(COGS Breakups)

     520648        159 13-AUG-01  CSTRBICR5G           Cost Rollup - No Report GUI

     446007        122 03-JUL-01  CSTRBICR5G           Cost Rollup - No Report GUI

     392996        120 01-JUN-01  BMCOIN               Bill and Routing Interface