Saturday, July 11, 2015

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

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect