Find Running, Pending, On Hold and Scheduled Requests

There are several instances where we need to get the list of scheduled concurrent request. Below is another handy query that displays all the concurrent request that are in running, pending or scheduled status.

SELECT   fcr.request_id,
         DECODE (fcr.phase_code,
                 'P', DECODE (fcr.hold_flag, 'Y', 'Inactive', fl_pend.meaning),
                 fl_pend.meaning
                ) phase,
         DECODE (fcr.phase_code,
                 'P', DECODE (fcr.hold_flag,
                              'Y', 'On Hold',
                              DECODE (SIGN (fcr.requested_start_date - SYSDATE),
                                      1, 'Scheduled',
                                      fl_stat.meaning
                                     )
                             ),
                 fl_stat.meaning
                ) status,
         fcpt.user_concurrent_program_name, fcr.increment_dates,
         fcr.resubmit_interval, fcr.resubmit_interval_unit_code,
         fcr.resubmit_interval_type_code, parent_request_id,
         fcr.requested_start_date, fu.user_name requested_by
    FROM fnd_concurrent_requests fcr,
         fnd_concurrent_programs_tl fcpt,
         fnd_lookups fl_pend,
         fnd_lookups fl_stat,
         fnd_user fu
   WHERE 1 = 1
     AND fcpt.concurrent_program_id = fcr.concurrent_program_id
     AND fcpt.LANGUAGE = USERENV ('LANG')
     AND fcr.phase_code = fl_pend.lookup_code
     AND fl_pend.lookup_type = 'CP_PHASE_CODE'
     AND fcr.status_code = fl_stat.lookup_code
     AND fl_stat.lookup_type = 'CP_STATUS_CODE'
     AND fl_pend.meaning != 'Completed'
     AND fu.user_id = fcr.requested_by
ORDER BY fcr.request_id DESC