Sunday, April 26, 2015

How to tell a scheduled request set is on the correct days to run

SELECT a.request_id,
         b.User_concurrent_program_name,
         c.user_name submitted_by,
         TO_CHAR (a.requested_start_date, 'DD-MON-YYYY HH24:MM:SS'),
            'Every '
         || DECODE (
               LENGTH (d.class_info),
               39, d.class_info,
                  SUBSTR (d.class_info, 1, INSTR (d.class_info, ':', 1) - 1)
               || ' '
               || DECODE (
                     SUBSTR (d.class_info, INSTR (d.class_info, ':', 1) + 1, 1),
                     'N', 'Minute(s) ',
                     'D', 'Day(s) ',
                     'H', 'Hour(s) ',
                     'M', 'Month(s) ')
               || 'after '
               || DECODE (SUBSTR (d.class_info,
                                    INSTR (d.class_info,
                                           ':',
                                           1,
                                           2)
                                  + 1,
                                  1),
                          'S', 'Start ',
                          'C', 'Completion ')
               || 'of prior request')
            SCHED,
         NVL (TO_CHAR (d.end_date_active, 'DD-MON-YYYY'), 'forever')
            "Ending On",
            RTRIM (RESUBMIT_INTERVAL)
         || ' '
         || RTRIM (RESUBMIT_INTERVAL_UNIT_CODE)
            "Interval",
         DECODE (
               RTRIM (RESUBMIT_INTERVAL)
            || ' '
            || RTRIM (RESUBMIT_INTERVAL_UNIT_CODE),
            '14 DAYS', TO_CHAR (REQUESTED_START_DATE, 'DY'),
            '7 DAYS', TO_CHAR (REQUESTED_START_DATE, 'DY'))
            "DOW",
         TRANSLATE (SUBSTR (sch_curr_values, 33, 7), '01', 'nY') "SMTWTFS",
         TO_CHAR (
            INSTR (
               TRANSLATE (
                  REPLACE (
                     REPLACE (
                        SUBSTR (
                           DECODE (RESUBMIT_INTERVAL_UNIT_CODE,
                                   NULL, sch_curr_values),
                           1,
                           31),
                        '1:',
                        ' '),
                     '14',
                     ' '),
                  '01YN47DSP:',
                  'nY '),
               'Y',
               1),
            'B99')
            AS Day_of_Mth,
         TRANSLATE (SUBSTR (sch_curr_values, 32, 1), '01', 'nY') AS LAST_DAY
    FROM applsys.fnd_concurrent_requests a,
         applsys.fnd_concurrent_programs_tl b,
         applsys.fnd_user c,
         applsys.FND_CONC_RELEASE_CLASSES d
   WHERE     a.phase_code = 'P'
         AND a.status_code IN ('I', 'Q')
         AND a.program_application_id = b.application_id
         AND a.concurrent_program_id = b.concurrent_program_id
         AND b.language = 'US'
         AND a.requested_by = c.user_id
         AND a.release_class_app_id = d.application_id
         AND a.release_class_id = d.release_class_id

ORDER BY a.requested_start_date

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect