rem ********************************************************************
rem * Filename          : %M% - Version %I%
rem * Author            : Rick Kupcunas
rem * Original          : 28-FEB-95
rem * Last Update       : %H% - %T%
rem * Description       : Shows individuall concurrent request stat's for
rem *                     capacity planning purposes.
rem * Usage             : start cmqprep.sql <date>  e.g. 03-MAR-95
rem *                                        <Program Name>
rem *                                        <Max run time (min.)>
rem *
rem ********************************************************************

create or replace view cm_stats1
(mgr_name,request_date,total_time,wait_time,run_time) as
select  a.concurrent_queue_name manager_name,
        c.request_date,
        (c.actual_completion_date - c.request_date) * (24*60),
        (c.actual_start_date - c.request_date) * (24*60),
        (c.actual_completion_date - c.actual_start_date) * (24*60)
from fnd_concurrent_queues a,
     fnd_concurrent_processes b,
     fnd_concurrent_requests c
where c.controlling_manager = b.concurrent_process_id
  and b.concurrent_queue_id = a.concurrent_queue_id
  and c.status_code = 'C';

create or replace view cm_stats2
(program_name,request_date,total_time,wait_time,run_time) as
select d.user_concurrent_program_name program_name,
       c.request_date,
       (c.actual_completion_date - c.request_date) * (24*60),
       (c.actual_start_date - c.request_date) * (24*60),
       (c.actual_completion_date - c.actual_start_date) * (24*60)
from   fnd_concurrent_programs d,
       fnd_concurrent_requests c
where  d.concurrent_program_id = c.concurrent_program_id
  and  c.status_code = 'C';
