Concurrent manager backlog 2011-07-25

This query can be used for reviewing CM Backlog:

set pages 100
col count format 9,990 justify r heading 'Count'
col program format a40 justify l heading 'Program'
col pgm_id format 9999999 justify l heading 'Program Id'


SELECT COUNT(*) count,
cpt.user_concurrent_program_name program,
cp.concurrent_program_id pgm_id
FROM applsys.fnd_concurrent_requests cr,
applsys.fnd_concurrent_programs cp,
applsys.fnd_concurrent_programs_tl cpt,
applsys.fnd_user u
WHERE cr.concurrent_program_id = cp.concurrent_program_id
AND u.user_id = cr.requested_by
AND cp.concurrent_program_id = cpt.concurrent_program_id
AND cr.phase_code != 'C' AND cr.phase_code != 'R' and cr.phase_code != 'H'
AND (cr.requested_start_date) <= (SYSDATE)
HAVING count(*) > &min_backlog
GROUP BY cpt.user_concurrent_program_name, cp.concurrent_program_id
ORDER BY 1
/