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!