This SQL query returns all concurrent requests that were scheduled in the past and are not scheduled currently. It can be used for identifying scheduled requests that failed to be resubmitted.
select r.request_id, p.user_concurrent_program_name, r.resubmit_interval, r.resubmit_interval_unit_code, c.class_info, r.phase_code, r.status_code
from fnd_concurrent_requests r,
fnd_concurrent_programs_tl p,
fnd_conc_release_classes c
where r.concurrent_program_id = p.concurrent_program_id
and c.release_class_id = r.release_class_id
and p.language = 'US'
and r.phase_code != 'P'
and r.status_code not in ('I', 'Q')
and nvl(r.request_type, 'X') != 'S'
and not exists
(select 1
from fnd_concurrent_requests r1,
fnd_conc_release_classes c1
where c1.release_class_id = r1.release_class_id
and r1.phase_code = 'P'
and r1.status_code in ('I', 'Q')
and nvl(r1.request_type, 'X') != 'S'
and r.concurrent_program_id = r1.concurrent_program_id)
and r.request_id =
(select max(r2.request_id)
from fnd_concurrent_requests r2
where r.concurrent_program_id = r2.concurrent_program_id);