Monday, August 31, 2015

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

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect