Query to find all Scheduled concurrent Programs

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