Thursday, January 9, 2014

SQL Query to Find Scheduled Concurrent Programs in System

SQL Query to Find Scheduled Concurrent Programs

By running below sql query in toad or sql develper, you can get list of scheduled concurrent requests.


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;

13 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Hi Sagar,
    I ran the query, however it produced no records though we have some scheduled programs in our system.

    Could you please help

    ReplyDelete
  3. Hi,

    Please check whether you have access to all the tables given the above query if not add "apps." before the table name for which you do not have access and try to re run the query.
    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

    ReplyDelete
  4. The query has the following condition AND fcr.phase_code = 'P'

    Does this mean any concurrent request which does not have phase code 'P' is not scheduled concurrent programme?

    ReplyDelete
  5. P refers to Pending, that means the Concurrent is scheduled, but has not been executed.

    ReplyDelete
  6. Can we download the scheduled concurrent requests before refresh and upload after the refresh.

    ReplyDelete
  7. Hi Sagar,

    Your queries are very helpful. I just want to thank you for your efforts and most of all for sharing it others.

    Feroze Parkeh

    ReplyDelete
  8. Hi Sagar,
    Thank you for your query. It is very helpful.
    One minor improvement: for environments that have multiple languages installed you need to add these 3 conditions to avoid duplicate rows:
    AND fcpt.LANGUAGE = 'US'
    and fpst.language = 'US'
    and frt.language = 'US'

    Regards,
    Iulian

    ReplyDelete
  9. Hi Sagar,

    how can we find the actual jobs that are running behind the concurrent programs. Also is it possible to schedule a job for different ORG Units? If yes, is it possible to find which job is running for which ORG ID?

    ReplyDelete