SQL Query to Find Scheduled Concurrent Programs List

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 apps.fnd_concurrent_requests fcr ,
  apps.fnd_user fu ,
  apps.fnd_concurrent_programs fcp ,
  apps.fnd_concurrent_programs_tl fcpt ,
  apps.fnd_printer_styles_tl fpst ,
  apps.fnd_conc_release_classes fcrc ,
  apps.fnd_responsibility_tl frt ,
  apps.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 frt.language               = 'US'
AND fpst.language              = 'US'
AND fcpt.language              = 'US'
ORDER BY Fu.Description,
  Fcr.Requested_Start_Date ASC

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 apps.fnd_concurrent_requests fcr , apps.fnd_user fu , apps.fnd_concurrent_programs fcp , apps.fnd_concurrent_programs_tl fcpt , apps.fnd_printer_styles_tl fpst , apps.fnd_conc_release_classes fcrc , apps.fnd_responsibility_tl frt , apps.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 frt.language = 'US' AND fpst.language = 'US' AND fcpt.language = 'US' ORDER BY Fu.Description, Fcr.Requested_Start_Date ASC