/* This script returns information on all concurrent manager jobs */
/* pending in the Conflict Resolution Manager */

ttitle off
set linesize 160
set pagesize 100
set newpage 0
set pause off
set termout on

break on reqid skip 1 on rsdate on status_code on user_name

col conc_que format a15 heading "Conc Queue"
col user_name format a12 heading "User ID"
col reqid format 99999999 heading "Req ID"
col sdate format a9 heading "St Date"
col astart format a8 heading "St Time"
col acomp format a8 heading "End Time"
col rtime format 99,999 heading "ExTime|(Min)"
col wtime format 99,999 heading "Wait|Time|(Sec)"
col pname1 format a15 heading "Short|Name"
col pname2 format a25 heading "Prog Name"
col status_code format a8 heading "Status"
col phase_code format a7 heading "Phase"
col rsdate format a15 heading "Req St Time"

select
request_id reqid,
to_char(req.requested_start_date, 'DD-Mon HH24:MI:SS') rsdate,
decode(status_code,'Q', 'Standby', 'F', 'Scheduled', 'T', 'Terminating', 'I', 'Normal', 'R', 'Normal', 'C', 'Normal', 'Other') status_code,
user_name,
req.concurrent_queue_name conc_que,
user_concurrent_program_name pname2
from
fnd_concurrent_worker_requests req,
fnd_concurrent_queues que,
fnd_user usr
where
phase_code = 'P'
and
que.concurrent_queue_name = 'Conflict_Resolution_Manager'
and
que.application_id= req.queue_application_id
and
que.concurrent_queue_id = req.concurrent_queue_id
and
req.requested_by = usr.user_id
and
requested_start_date <= sysdate
and
concurrent_program_name not in
('ACTIVATE','ABORT','DEACTIVATE','VERIFY')
order by request_id, requested_start_date
/

ttitle off