Query To Get Scheduled Concurrent Program Details

  SELECT req.request_id,

         DECODE (prg.user_concurrent_program_name,

                 'Report Set', 'Report Set:' || req.description,

                 prg.user_concurrent_program_name)

            AS name,

         argument_text AS parameters,

         req.resubmit_interval,

         NVL2 (req.resubmit_interval,

               'Periodically',

               NVL2 (req.release_class_id, 'On specific days', 'Once'))

            AS schedule_type,

         DECODE (

            NVL2 (req.resubmit_interval,

                  'Periodically',

                  NVL2 (req.release_class_id, 'On specific days', 'Once')),

            'Periodically',    'Every '

                            || req.resubmit_interval

                            || ' '

                            || LOWER (req.resubmit_interval_unit_code)

                            || ' from '

                            || LOWER (req.resubmit_interval_type_code)

                            || ' of previous run',

            'Once',    'At :'

                    || TO_CHAR (req.requested_start_date, 'DD-MON-RR HH24:MI'),

            'Every: ' || crc.class_info)

            AS schedule,

         fus.user_name AS owner,

         TO_CHAR (requested_start_date, 'DD-MON-YYYY HH24:MI:SS')

            AS next_submission

    FROM apps.fnd_concurrent_programs_tl prg,

         apps.fnd_concurrent_requests req,

         apps.fnd_user fus,

         apps.fnd_conc_release_classes crc

   WHERE     1 = 1

         AND prg.application_id = req.program_application_id

         AND prg.concurrent_program_id = req.concurrent_program_id

         AND req.requested_by = fus.user_id

         AND req.phase_code = 'P'

         AND req.requested_start_date > SYSDATE

         AND prg.language = 'US'

         AND crc.release_class_id(+) = req.release_class_id

         AND crc.application_id(+) = req.release_class_app_id

         AND prg.user_concurrent_program_name LIKE 'XXAA%'

ORDER BY name, requested_start_date DESC