列出所有Scheduled Concurrent Program

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;

转载请注明出处：http://blog.csdn.net/pan_tian/article/details/7697667

======EOF======