queue_status

rem  queue_status.sql

rem

ttitle 'Concurrent Manager Queue Status'

rem

col request_class_name format a20 heading 'REQUEST CLASS NAME'

col program format a26 trunc heading 'Program' 

col manager format a16 trunc heading 'Manager|Queue'

col phase_code format a1 heading 'P'

col status_code format a1 heading 'S'

col concurrent_program_name format a12 heading 'PROGRAM NAME'

col phase_code_r format b999 heading 'RUNNING|JOBS'

col running_gt_30_min format b999 heading 'RUN|> 30|MIN'

col running_gt_10_min format b999 heading 'RUN|> 10|MIN'

col running_lt_1_min format b999 heading 'RUN|< 1|MIN'

col phase_code_p format b999 heading 'PENDING|JOBS'

col status_code_q format b999 heading 'STANDBY|JOBS'

col status_code_i format b999 heading 'TOTAL|WAIT|JOBS'

col pending_gt_1_min format b999 heading 'WAIT|> 1|MIN'

col pending_gt_5_min format b999 heading 'WAIT|> 5|MIN'

col pending_gt_5_min format b999 heading 'WAIT|> 5|MIN'

col pending_gt_30_min format b999 heading 'WAIT|> 30|MIN'

rem

break on report

compute sum -

     of phase_code_r running_gt_30_min running_gt_10_min running_lt_1_min phase_code_p -

        status_code_q status_code_i pending_gt_1_min pending_gt_5_min pending_gt_30_min -

     on report

rem

select nvl( c.request_class_name, 'STANDARD' ) request_class_name,

       count( decode( phase_code, 'R', 'R' ) ) phase_code_r,

       count( decode( phase_code, 'R',

                      decode( 1/1440, greatest( 1/1440, sysdate - r.actual_start_date ),

                              'R' ) ) ) running_lt_1_min,

       count( decode( phase_code, 'R',

                      decode( 10/1440, least( 10/1440, sysdate - r.actual_start_date ),

                              'R' ) ) ) running_gt_10_min,

       count( decode( phase_code, 'R',

                      decode( 30/1440, least( 30/1440, sysdate - r.actual_start_date ),

                              'R' ) ) ) running_gt_30_min,

       count( decode( phase_code, 'P', 'P' ) ) phase_code_p,

       count( decode( status_code, 'Q', 'Q' ) ) status_code_q,

       count( decode( status_code, 'I', 'I' ) ) status_code_i,

       count( decode( status_code, 'I',

                      decode( 1/1440, greatest( 1/1440, sysdate - greatest( r.requested_start_date, r.request_date ) ),

                              null, 'I' ) ) ) pending_gt_1_min,

       count( decode( status_code, 'I',

                      decode( 5/1440, greatest( 5/1440, sysdate - greatest( r.requested_start_date, r.request_date ) ),

                              null, 'I' ) ) ) pending_gt_5_min,

       count( decode( status_code, 'I',

                      decode( 30/1440, greatest( 30/1440, sysdate - greatest( r.requested_start_date, r.request_date ) ),

                              null, 'I' ) ) ) pending_gt_30_min

  from applsys.fnd_concurrent_requests r,

       applsys.fnd_concurrent_request_class c

 where r.request_class_application_id = c.application_id(+)

   and r.concurrent_request_class_id = c.request_class_id(+)

   and phase_code in ( 'P', 'R' )

   and status_code not in ( 'W' )

   and r.hold_flag = 'N'

   and greatest( r.requested_start_date, r.request_date ) <= sysdate

 group by c.request_class_name

 order by 1;