Query: Find scheduled or on hold concurrent requests

I've noticed a number of requests for finding scheduled concurrent requests via a single query. But there are various combinations of phase_code, status_code, hold_flag, requested_start_date, etc on the fnd_concurrent_requests table and others that determine the "real" Phase and Status as displayed in the View Requests form.

No worries, here's how we find the Scheduled Requests:

select request_id
from   fnd_concurrent_requests
where  status_code in ('Q','I')
and    requested_start_date > SYSDATE
and    hold_flag = 'N';

How about an all in one query? Note: this may not be complete, so any comments welcome! Also we limit to those requests submitted in the last hour.

select fcr.request_id
,      decode(fcr.phase_code
             ,'P',decode(fcr.hold_flag
                        ,'Y','Inactive'
                        ,fl_p.meaning
                        )
             ,fl_p.meaning
             ) phase
,      decode(fcr.phase_code
             ,'P',decode(fcr.hold_flag
                        ,'Y','On Hold'
                        ,decode(sign(fcr.requested_start_date - sysdate)
                               ,1,'Scheduled'
                               ,fl_s.meaning)
                  )
             ,fl_s.meaning
             ) status
from   fnd_concurrent_requests fcr
,      fnd_lookups fl_p
,      fnd_lookups fl_s
where  1=1
and    fcr.phase_code = fl_p.lookup_code
and    fl_p.lookup_type = 'CP_PHASE_CODE'
and    fcr.status_code = fl_s.lookup_code
and    fl_s.lookup_type = 'CP_STATUS_CODE'
and    fcr.request_date > sysdate - 60/1440
order by fcr.request_id desc;

Thanks to form FNDRSRUN.fmb, Trace/Tkprof and the following Metalink notes:

• ANALYZEREQ.SQL 134035.1
• ANALYZEPENDING.SQL 134033.1
• Status Code ... in FND_CONCURRENT_REQUESTS 1054419.6

Now statuses and phases will be sorted!