Oracle E-Business Suite: Schedule Tanımlanmış Request'lerin Bulunması

Oracle Applications Upgrade işlemi sırasında karşılaştığım sorunlardan biri bütün concurrent request'lerin iptal edilmesi ve sonrasında bellirli bir düzende çalışan concurrent'ların depolanması ve tekrar çalıştırılmasıydı. Bunun içinde aşağıdaki gibi bir sorguya ihtiyacım olmuştu. Bu sorguyla schedule edilmiş requestler hakkında bilgi sahibi olunabilinir.

 select r.request_id,  
 p.user_concurrent_program_name || nvl2(r.description,' ('||r.description||')',null) Conc_prog,  
 s.user_name REQUESTOR,  
 r.argument_text arguments,  
 r.requested_start_date next_run,  
 r.last_update_date LAST_RUN,  
 r.hold_flag on_hold,  
 r.increment_dates,  
 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 as 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_user s,  
 (with date_schedules as (  
 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'  
 and instr(substr(c.class_info, 1, 31), '1') > 0)  
 where s is not null)  
 SELECT release_class_id, substr(max(SYS_CONNECT_BY_PATH(s, ' ')), 2) dates  
 FROM date_schedules  
 START WITH a = 1  
 CONNECT BY nocycle PRIOR a = 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.language = 'US'  
 and dates.release_class_id(+) = r.release_class_id  
 and r.requested_by = s.user_id  
 order by conc_prog, on_hold, next_run;  

2.bir sorgumuz da yine scheduled edilmiş concurrent request'ler gösterilir.

 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;