Query to Get the Long Running Concurrent Requests

select 

       fcptl.user_concurrent_program_name "Program",

       round(greatest(actual_completion_date - actual_start_date,0)*24*60, 2) "Duration (min)",

       fcr.request_id "Request ID",

       fcu.user_name "User Name",

       fcqtl.user_concurrent_queue_name "Concurrent Manager",

       actual_start_date "Actual Start",

       flvs.meaning "Status",

       fcr.priority "Priority",

       request_limit "Request Limit",

       fcr.argument_text "Parameters"

  from

       fnd_concurrent_programs_tl fcptl,

       fnd_user fcu,

       fnd_concurrent_queues_tl fcqtl,

       fnd_concurrent_processes fcproc,

       fnd_lookups flvs,

       fnd_concurrent_requests fcr

 where

       fcr.phase_code = 'C'

       and fcr.actual_completion_date is not null

       and actual_start_date is not null

       and fcr.requested_by=fcu.user_id

       and fcr.concurrent_program_id =fcptl.concurrent_program_id

       and fcr.program_application_id =fcptl.application_id

       and fcptl.language=userenv('LANG')

       and round(greatest(actual_completion_date - actual_start_date,0)*(60*24),2) >= 30

       and fcr.controlling_manager  = fcproc.concurrent_process_id

       and fcproc.queue_application_id = fcqtl.application_id

       and fcproc.concurrent_queue_id = fcqtl.concurrent_queue_id

       and fcqtl.language=userenv('LANG')

       and flvs.lookup_type = 'CP_STATUS_CODE'

       and fcr.status_code = flvs.lookup_code

 order by

       "Duration (min)" desc,

       "Program" asc