Query to fetch all concurrent program in pending status

  SELECT r.request_id,
         p.user_concurrent_program_name
         || NVL2 (r.description, ' (' || r.description || ')', NULL)
            Conc_prog,
         s.user_name REQUESTOR,
         r.argument_text arguments,
         r.requested_start_date next_run,
         r.last_update_date LAST_RUN,
         r.hold_flag on_hold,
         r.increment_dates,
         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
            AS schedule,
         c.date1 start_date,
         c.date2 end_date,
         c.class_info
    FROM fnd_concurrent_requests r,
         fnd_conc_release_classes c,
         fnd_concurrent_programs_tl p,
         fnd_user s,
         (WITH date_schedules
               AS (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),
                                  fnd_conc_release_classes c
                            WHERE c.class_type = 'S'
                                  AND INSTR (SUBSTR (c.class_info, 1, 31), '1') >
                                         0)
                    WHERE s IS NOT NULL)
              SELECT release_class_id,
                     SUBSTR (MAX (SYS_CONNECT_BY_PATH (s, ' ')), 2) dates
                FROM date_schedules
          START WITH a = 1
          CONNECT BY NOCYCLE PRIOR a = a - 1
            GROUP BY release_class_id) 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.language = 'US'
         AND dates.release_class_id(+) = r.release_class_id
         AND r.requested_by = s.user_id
ORDER BY conc_prog, on_hold, next_run;