/* cmpending.sql
	find pending jobs pending in a queue but should be running
*/
set lines 132
set pages 30
col program format a45 trunc
col phase_code format a1 trunc
col statustxt format a15 trunc
col parent format a8 trunc
col reqstarthide noprint
col minlate format 99999.90 head MinLate
set verify off
prompt Note: Minutes Late shown in decimal minutes.....
select a.requested_start_date reqstarthide,
	to_char(a.requested_start_date,'mm/dd/yy hh24:mi') reqstart,
	(sysdate-requested_start_date)*1440 minlate,
	a.request_id "Req Id",
      decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent",
      a.concurrent_program_id "Prg Id",
	a.phase_code,
	a.status_code || ' - ' ||l1.meaning statustxt,
      c.concurrent_program_name||' - '|| c2.user_concurrent_program_name "program"
from APPLSYS.fnd_Concurrent_requests a,
    	APPLSYS.fnd_concurrent_programs_tl c2,
    	APPLSYS.fnd_concurrent_programs c,
	applsys.fnd_lookup_values l1
where a.concurrent_program_id = c.concurrent_program_id
  and a.program_application_id = c.application_id
  and c2.concurrent_program_id = c.concurrent_program_id
  and c2.language = 'US'
  and c2.application_id = c.application_id
  and a.actual_start_date is null
  and a.status_code in ('A','H','I','M','P','Q','R')
  and a.phase_code in ('P','I')
  and sysdate - a.requested_start_date < 2
  and a.requested_start_date < sysdate
  and l1.lookup_type = 'CP_STATUS_CODE'
  and l1.lookup_code = a.status_code
  and l1.language = 'US'
  and l1.enabled_flag = 'Y'
  and (l1.start_date_active <= sysdate and l1.start_date_active is not null)
  and (l1.end_date_active > sysdate or l1.end_date_active is null)
order by 1;