Concurrent Requests Bucketed

Description
Displays a the number of concurrent requests running within specified time buckets.
with buckets as (
select :date_from + numtodsinterval(:n * (level - 1), :metric)  from_bucket,
       :date_from + numtodsinterval(:n * level, :metric) to_bucket
  from dual
connect by level <= ((:date_to - :date_from + 1) * decode(:metric,'DAY',1,'HOUR',24,'MINUTE',60*24)/:n) + 1)
select r.from_bucket, r.to_bucket, r.n, lpad('-',ceil((r.n / nullif(max(r.n) over (),0)) * 100),'-') chart
  from (
    select b.from_bucket,
           b.to_bucket,
           count(r.request_id) n
      from buckets b 
 left join (select r.request_id,
                   r.actual_start_date,
                   r.actual_completion_date
              from fnd_concurrent_requests r 
              join fnd_concurrent_processes p on p.concurrent_process_id = r.controlling_manager
              join fnd_concurrent_queues q on q.concurrent_queue_id = p.concurrent_queue_id
             where q.concurrent_queue_name = :conc_queue or :conc_queue is null) r
        on r.actual_start_date < b.to_bucket 
       and r.actual_completion_date >= b.from_bucket
     where b.to_bucket <= sysdate
    group by b.from_bucket,
             b.to_bucket) r
order by r.from_bucket desc
Parameter Name SQL text Validation
Date From
 
Date
Date To
 
Date
Time Metric
 
LOV
Metric Interval
 
Number
Concurrent Manager
 
LOV