cm_scheduled_jobs

SELECT fcr.request_id
      , fcpt.user_concurrent_program_name|| NVL2(fcr.description, ' (' || fcr.description || ')', NULL) conc_prog
      , fu.user_name requestor
      , fu.description requested_by
      , fu.email_address
      , frt.responsibility_name requested_by_resp
      , trim(fl.meaning) status
      , fcr.phase_code
      , fcr.status_code
      , fcr.argument_text "PARAMETERS"
       , TO_CHAR(fcr.request_date, 'DD-MON-YYYY HH24:MI:SS') requested
      , TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS') requested_start  
      , TO_CHAR((fcr.requested_start_date), 'HH24:MI:SS') start_time
      , DECODE(fcr.hold_flag, 'Y', 'Yes', 'N', 'No') on_hold
      , CASE
           WHEN fcr.hold_flag = 'Y'
              Then Substr(
                     fu.description
                   , 0
                   , 40
                  )
        END last_update_by
      , CASE
           WHEN fcr.hold_flag = 'Y'
              THEN fcr.last_update_date
        END last_update_date
      , fcr.increment_dates
      , CASE WHEN fcrc.CLASS_INFO IS NULL THEN
         'Yes: ' || TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')
        ELSE
         'n/a'
        END run_once
      , CASE WHEN fcrc.class_type = 'P' THEN
         'Repeat every ' ||
         substr(fcrc.class_info, 1, instr(fcrc.class_info, ':') - 1) ||
         decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 1) + 1, 1),
                'N', ' minutes',
                'M', ' months',
                'H', ' hours',
                'D', ' days') ||
         decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 2) + 1, 1),
                'S', ' from the start of the prior run',
                'C', ' from the completion of the prior run')
        ELSE
          'n/a'
        END set_days_of_week
        , CASE WHEN fcrc.class_type = 'S' AND instr(substr(fcrc.class_info, 33),'1',1) > 0 THEN
           'Days of week: ' ||
                   decode(substr(fcrc.class_info, 33, 1), '1', 'Sun, ') ||
                   decode(substr(fcrc.class_info, 34, 1), '1', 'Mon, ') ||
                   decode(substr(fcrc.class_info, 35, 1), '1', 'Tue, ') ||
                   decode(substr(fcrc.class_info, 36, 1), '1', 'Wed, ') ||
                   decode(substr(fcrc.class_info, 37, 1), '1', 'Thu, ') ||
                   decode(substr(fcrc.class_info, 38, 1), '1', 'Fri, ') ||
                   decode(substr(fcrc.class_info, 39, 1), '1', 'Sat ')
          ELSE
            'n/a'
          end  days_of_week
   FROM fnd_concurrent_requests fcr
      , fnd_user fu
      , fnd_concurrent_programs fcp
      , fnd_concurrent_programs_tl fcpt
      , fnd_printer_styles_tl fpst
      , fnd_conc_release_classes fcrc
      , fnd_responsibility_tl frt
      , fnd_lookups fl
  WHERE fcp.application_id = fcpt.application_id
    AND fcr.requested_by = fu.user_id
    AND fcr.concurrent_program_id = fcp.concurrent_program_id
    AND fcr.program_application_id = fcp.application_id
    AND fcr.concurrent_program_id = fcpt.concurrent_program_id
    AND fcr.responsibility_id = frt.responsibility_id
    AND fcr.print_style = fpst.printer_style_name(+)
    AND fcr.release_class_id = fcrc.release_class_id(+)
    AND fcr.status_code = fl.lookup_code
    AND fl.lookup_type = 'CP_STATUS_CODE'
    AND fcr.phase_code = 'P'
    AND 1=1
 Order By Fu.Description, Fcr.Requested_Start_Date Asc;