Tuesday, December 7, 2010

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
        -- AND user_name = 'XXSDM'
         AND P.user_concurrent_program_name = 'Workflow Background Process'
ORDER BY user_concurrent_program_name --requested_by, on_hold, next_run;

8 comments:

  1. I Want to Know the main differences between oracle apps 11i and R12... Can any one give the main difference..
    My mail id aravindu.oracle@gmail.com

    ReplyDelete
  2. Hi Aravind,
    Sent docs to your gmail id and extremely sorry for long delayed response

    Let me know if anyone else need any docs....

    Amit

    ReplyDelete
  3. Dear Amit,

    Please send document to me also.

    sandy616@gmail.com

    Reagrds,
    Sandip
    http://oraclesanpra.blogspot.com

    ReplyDelete
  4. Hello Amit,

    Please send document to me as well lovingmnak@gmail.com

    Thanks,
    David.

    ReplyDelete
  5. Hi

    I want to check if scheduling of any of the concurrent program stops without completing with error or warning.
    Could you please tell how can we check this?

    ReplyDelete
  6. actually, it's to valuable case that comment posted by anonymous. can anyone help about this?
    thanks,
    best regards

    ayhan

    ReplyDelete
  7. Hi Amit, please send the docs to iwan.herdian86@gmail.com

    Best Regards,
    Iwan

    ReplyDelete
  8. Hi Iwan,
    Sent docs to your gmail ID for 11i Vs R12

    ReplyDelete

Note: Only a member of this blog may post a comment.