Query For List of Scheduled Concurrent Programs in Apps R12
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 apps.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 apps.alr_alerts a
where a.application_id = r.argument1
and a.alert_id = r.argument2
and language = 'US'
)
end concurrent_program_name,
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,
r.requested_start_date next_run,
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.hold_flag on_hold,
c.date1 start_date,
c.date2 end_date,
c.class_info, user_name
from apps.fnd_concurrent_requests r,
applsys.fnd_conc_release_classes c,
apps.fnd_concurrent_programs_tl p,
apps.fnd_user usr,
(SELECT release_class_id,
substr(max(SYS_CONNECT_BY_PATH(s, ' ')), 2) dates ,a
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),
apps.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
group by release_class_id,a) 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
and usr.user_id = requested_by
order by requested_by,on_hold, next_run;
