List of Scheduled Concurrent Programs Query

/* Formatted on 7/17/2014 12:58:47 PM (QP5 v5.115.810.9015) */
SELECT r.request_id,
       p.user_concurrent_program_name
       || CASE
             WHEN p.user_concurrent_program_name = 'Report Set'
             THEN
                (SELECT ' - ' || s.user_request_set_name
                 FROM apps.fnd_request_sets_tl s
                 WHERE     s.application_id = r.argument1
                       AND s.request_set_id = r.argument2
                       AND language = 'US')
             WHEN p.user_concurrent_program_name = 'Check Periodic Alert'
             THEN
                (SELECT ' - ' || a.alert_name
                 FROM apps.alr_alerts a
                 WHERE     a.application_id = r.argument1
                       AND a.alert_id = r.argument2
                       AND language = 'US')
          END
          concurrent_program_name,
       DECODE (c.class_type,
          'P', 'Periodic',
          'S', 'On Specific Days',
          'X', 'Advanced',
          c.class_type)
          schedule_type,
       CASE
          WHEN c.class_type = 'P'
          THEN
             'Repeat every '
             || SUBSTR (c.class_info, 1, INSTR (c.class_info, ':') - 1)
             || DECODE (SUBSTR (c.class_info,
                                INSTR (c.class_info, ':', 1, 1) + 1,
                                1
                        ),
                        'N',
                        ' minutes',
                        'M',
                        ' months',
                        'H',
                        ' hours',
                        'D',
                        ' days'
                )
             || DECODE (SUBSTR (c.class_info,
                                INSTR (c.class_info, ':', 1, 2) + 1,
                                1
                        ),
                        'S',
                        ' from the start of the prior run',
                        'C',
                        ' from the completion of the prior run'
                )
          WHEN c.class_type = 'S'
          THEN
             NVL2 (dates.dates, 'Dates: ' || dates.dates || '. ', NULL)
             || DECODE (SUBSTR (c.class_info, 32, 1),
                        '1',
                        'Last day of month '
                )
             || DECODE (SIGN (TO_NUMBER (SUBSTR (c.class_info, 33))),
                        '1',
                           'Days of week: '
                        || DECODE (SUBSTR (c.class_info, 33, 1), '1', 'Su ')
                        || DECODE (SUBSTR (c.class_info, 34, 1), '1', 'Mo ')
                        || DECODE (SUBSTR (c.class_info, 35, 1), '1', 'Tu ')
                        || DECODE (SUBSTR (c.class_info, 36, 1), '1', 'We ')
                        || DECODE (SUBSTR (c.class_info, 37, 1), '1', 'Th ')
                        || DECODE (SUBSTR (c.class_info, 38, 1), '1', 'Fr ')
                        || DECODE (SUBSTR (c.class_info, 39, 1), '1', 'Sa ')
                )
       END
          schedule,
       r.requested_start_date next_run,
       CASE
          WHEN p.user_concurrent_program_name != 'Report Set'
               AND p.user_concurrent_program_name != 'Check Periodic Alert'
          THEN
             r.argument_text
       END
          argument_text,
       r.hold_flag on_hold,
       c.date1 start_date,
       c.date2 end_date,
       c.class_info,
       user_name
FROM apps.fnd_concurrent_requests r,
     applsys.fnd_conc_release_classes c,
     apps.fnd_concurrent_programs_tl p,
     apps.fnd_user usr,
     (SELECT release_class_id,
             SUBSTR (MAX (SYS_CONNECT_BY_PATH (s, ' ')), 2) dates,
             a
      FROM (SELECT release_class_id,
                   RANK () OVER (PARTITION BY release_class_id ORDER BY s) a,
                   s
            FROM (SELECT c.class_info,
                         l,
                         c.release_class_id,
                         DECODE (SUBSTR (c.class_info, l, 1), '1', TO_CHAR (l))
                            s
                  FROM (SELECT LEVEL l
                        FROM DUAL
                        CONNECT BY LEVEL <= 31),
                       apps.fnd_conc_release_classes c
                  WHERE c.class_type = 'S')
            WHERE s IS NOT NULL)
      CONNECT BY PRIOR (a || release_class_id) = (a - 1) || release_class_id
      GROUP BY release_class_id, a) dates
WHERE     r.phase_code = 'P'
      AND c.application_id = r.release_class_app_id
      AND c.release_class_id = r.release_class_id
      AND NVL (c.date2, SYSDATE + 1) > SYSDATE
      AND c.class_type IS NOT NULL
      AND p.concurrent_program_id = r.concurrent_program_id
      AND p.application_id = r.program_application_id
      AND p.language = 'US'
      AND dates.release_class_id(+) = r.release_class_id
      AND usr.user_id = requested_by
ORDER BY requested_by, on_hold, next_run;