REM hogtracker.sql
REM
REM Copyright 2000, All Rights Reserved, Reed-Matthews, Inc.
REM
set heading off;
column hog format a4
column req_id format 9999999
column s_time format a5
column name format a45
column u_name format a9
column elap format 99.99
col v1 new_value v1
col params format a100
col queue format a20
set term on
set linesize 132 
prompt Show concurrent processes that have been going a long time. 
prompt Get lines for the report title
select 'HOGG' hog, fcr.request_id req_id, 
  substr(fcq.concurrent_queue_name, 1, 20) queue,
  to_char(fcr.actual_start_date,'hh24:mi') s_time,
  substr(fcp.user_concurrent_program_name, 1, 45)  name,
  substr(fu.user_name, 1, 9 ) u_name,
  round((sysdate -actual_start_date) *24, 2) elap,
  substr(decode( fcr.status_code, 'A', 'WAITING', 'B', 'RESUMING',
	'C', 'NORMAL', 'D', 'CANCELLED', 'E', 'ERROR', 'F', 'SCHEDULED',
	'G', 'WARNING', 'H', 'ON HOLD', 'I', 'NORMAL', 'M', 'NO MANAGER',
	'Q', 'STANDBY', 'R', 'NORMAL', 'S', 'SUSPENDED', 'T', 'TERMINATED',
	'U', 'DISABLED', 'W', 'PAUSED', 'X', 'TERMINATED', 'Z', 'WAITING',
	'UNKNOWN'), 1, 10) 
  from 
	applsys.fnd_concurrent_queues fcq, 
        applsys.fnd_concurrent_processes fcp, applsys.fnd_user fu,
        apps.fnd_concurrent_programs_vl fcp, 
        applsys.fnd_concurrent_requests fcr
  where fcp.concurrent_queue_id = fcq.concurrent_queue_id
    and fcp.queue_application_id = fcq.application_id
    and fcr.controlling_manager = fcp.concurrent_process_id
    and fcr.requested_by = fu.user_id
    and fcr.concurrent_program_id = fcp.concurrent_program_id
    and fcr.program_application_id = fcp.application_id
    and round((sysdate -fcr.actual_start_date) *24, 2) >= &1 
    and fcr.phase_code = 'R'
  order by round((sysdate -actual_start_date) *24, 2), fu.user_name,
        fcr.request_id, to_char(fcr.actual_start_date,'dd-mon-yy hh24:mi:ss'),
        fcp.user_concurrent_program_name 
/
prompt Get lines for the report parameter list. 
select 'HOGP' hog, fcr.request_id req_id, 
       substr( fcr.argument_text, 1, 100 ) params 
       from applsys.fnd_user fu, applsys.fnd_concurrent_programs fcp, 
       applsys.fnd_concurrent_requests fcr 
       where fcr.requested_by = fu.user_id 
       and fcr.concurrent_program_id = fcp.concurrent_program_id 
       and fcr.program_application_id = fcp.application_id 
       and round((sysdate -fcr.actual_start_date) *24, 2) >= &1 
       and fcr.phase_code = 'R' order by fcr.request_id 
/ 
prompt Output info to be used for paging. 
select 'PAGING_LINE888' ||lpad( fcr.request_id, 6, '0' ) 
||lpad( round((sysdate -actual_start_date) *24 *60, 0 ), 3, '0') from 
applsys.fnd_concurrent_requests fcr where fcr.request_id =  
  ( select  min( fcr.request_id ) from applsys.fnd_concurrent_requests fcr 
  where round((sysdate -fcr.actual_start_date) *24, 2) >= &2 
  and fcr.phase_code = 'R' and sysdate -fcr.actual_start_date = 
  ( select max( sysdate -actual_start_date) from applsys.fnd_concurrent_requests fcr 
  where round((sysdate -fcr.actual_start_date) *24, 2) >= &2 
  and fcr.phase_code = 'R')) 
/ 
select count(*) v1 from applsys.fnd_user fu,
    applsys.fnd_concurrent_programs fcp, 
   applsys.fnd_concurrent_requests fcr where fcr.requested_by = fu.user_id and 
   fcr.concurrent_program_id = fcp.concurrent_program_id and 
   fcr.program_application_id = fcp.application_id and 
   round((sysdate -fcr.actual_start_date) *24, 2) >= &1 and 
   fcr.phase_code = 'R'
/ 
prompt &v1 
exit &v1