First of all, thanks to those who attended my presentation at UKOUG Conference Series Technology & E-Business Suite 2010, was nice to talk for such an interested audience.

As I promised, here is the presentation - Internals Of Concurrent Managers. The post will be updated later with more details, but if you have any additional questions you can meet me at the “Meet the Speakers” events at UKOUG2010 or just leave a comment here.

And here’s the query from the slide 13:

--Reporting the concurrent program schedules
--Test before using in production
--Maris Elsins, https://me-dba.com

select r.request_id,
 rs.responsibility_name,
 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)
 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,
 fnd_responsibility_tl rs,
 (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 (with rcs as (select c.class_info, c.release_class_id
 from fnd_conc_release_classes c
 where c.class_type = 'S'
 and c.release_class_id in
 (select rr.release_class_id
 from fnd_concurrent_requests rr
 where rr.phase_code = 'P'
 and rr.hold_flag = 'N'))
 select rcs.class_info,
 l,rcs.release_class_id,decode(substr(rcs.class_info, l, 1),'1',to_char(l)) s
 from rcs,(select level l from dual connect by level <= 31) 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 hold_flag = 'N'
 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 rs.responsibility_id=r.responsibility_id
 and rs.application_id=r.responsibility_application_id
 and rs.language = 'US'
 order by 3,2,4;