Wednesday, August 31, 2016

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

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect