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';
Or for a more detailed version, check out the following:
select fcp.concurrent_program_name , fcpt.user_concurrent_program_name , fcr.description , 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 , fcr.requested_start_date from fnd_concurrent_requests fcr , fnd_concurrent_programs fcp , fnd_concurrent_programs_tl fcpt , 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.requested_start_date > sysdate and fcr.concurrent_program_id = fcp.concurrent_program_id and fcr.program_application_id = fcp.application_id and fcp.concurrent_program_id = fcpt.concurrent_program_id and fcpt.language = 'US' and fcp.application_id = fcpt.application_id 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!