Thursday, September 13, 2007

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';

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:

Now statuses and phases will be sorted!

No comments: