Query for Finding Scheduled Concurrent Program

The below query can be used for getting all the details for a scheduled concurrent program.

select a.requested_by,a.status_code,a.phase_code,a.request_id, b.user_concurrent_program_name,c.concurrent_program_name,a.requested_start_date, c.execution_method_code,
d.execution_file_name,d.execution_file_path
from apps.fnd_concurrent_requests a, apps.fnd_concurrent_programs_tl b, apps.fnd_concurrent_programs c,
apps.fnd_executables d
where a.status_code in ('Q','I')
and a.concurrent_program_id = b.concurrent_program_id
and b.concurrent_program_id = c.concurrent_program_id
and c.application_id=d.application_id
and c.executable_id=d.executable_id
and a.requested_start_date > SYSDATE
and a.hold_flag = 'N'
order by 1;

Finding the schedules for a particular program we can use the below query

To find all concurrent requests that are scheduled to run on Saturday and Sunday

alter session set NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS';

select a.requested_by,a.status_code,a.phase_code,a.request_id,a.hold_flag,b.user_concurrent_program_name,c.concurrent_program_name,a.requested_start_date, c.execution_method_code,

d.execution_file_name
from apps.fnd_concurrent_requests a, apps.fnd_concurrent_programs_tl b, apps.fnd_concurrent_programs c,
apps.fnd_executables d
where a.status_code in ('Q','I','W')
and a.concurrent_program_id = b.concurrent_program_id
and b.concurrent_program_id = c.concurrent_program_id
and c.application_id=d.application_id
and c.executable_id=d.executable_id
and a.requested_start_date > SYSDATE
and a.hold_flag = 'N'

and TO_CHAR(a.requested_start_date, 'DY') IN ('SAT', 'SUN')
order by 1;

Including Request Set and Concurrent Program

alter session set NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS';

SELECT

r.request_id,

'Concurrent Request' AS request_type,

pt.user_concurrent_program_name AS program_name,

r.requested_start_date,

r.hold_flag

FROM

fnd_concurrent_requests r

JOIN

fnd_concurrent_programs p

r.concurrent_program_id = p.concurrent_program_id

JOIN

fnd_concurrent_programs_tl pt

p.concurrent_program_id = pt.concurrent_program_id

AND pt.language = 'US'

WHERE

TO_CHAR(r.requested_start_date, 'DY') IN ('SAT', 'SUN')

AND r.requested_start_date > SYSDATE

AND r.phase_code = 'P'

AND r.status_code IN ('Q','I','W')

UNION ALL

SELECT

r.request_id,

'Request Set' AS request_type,

s.user_request_set_name AS program_name,

r.requested_start_date,

r.hold_flag

FROM

fnd_concurrent_requests r

JOIN

fnd_concurrent_request_sets s

r.request_id = s.request_id

WHERE

TO_CHAR(r.requested_start_date, 'DY') IN ('SAT', 'SUN')

AND r.requested_start_date > SYSDATE

AND r.phase_code = 'P'

AND r.status_code IN ('Q','I','W');

We can use below query as well

alter session set NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS';

SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:' || cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, cr.resubmit_interval,
NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
) schedule_type,
DECODE (NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
),
'PERIODICALLY', 'EVERY '
|| cr.resubmit_interval
|| ' '
|| cr.resubmit_interval_unit_code
|| ' FROM '
|| cr.resubmit_interval_type_code
|| ' OF PREV RUN',
'ONCE', 'AT :'
|| TO_CHAR (cr.requested_start_date, 'DD-MON-RR HH24:MI'),
'EVERY: ' || fcr.class_info
) schedule,
fu.user_name, requested_start_date
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu,
apps.fnd_conc_release_classes fcr
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fcr.release_class_id(+) = cr.release_class_id
AND fcr.application_id(+) = cr.release_class_app_id;

Report all Periodic jobs and scheduled

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 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 alr_alerts a
            where a.application_id = r.argument1
              and a.alert_id = r.argument2
              and language = 'US')
       end concurrent_program_name,
       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.requested_start_date next_run,
       r.hold_flag on_hold,
       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,
       c.date1 start_date,
       c.date2 end_date,
       c.class_info
  from fnd_concurrent_requests r,
       fnd_conc_release_classes c,
       fnd_concurrent_programs_tl p,
       (SELECT release_class_id,
               substr(max(SYS_CONNECT_BY_PATH(s, ' ')), 2) dates
          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),
                               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
         START WITH a = 1
         group by release_class_id) 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
 order by on_hold, next_run;