Search

Scheduled Concurrent Requests

select s.user_name,

per.full_name,

p.user_concurrent_program_name || nvl2(r.description,' ('||r.description||')',null) program,

r.argument_text arguments,

to_char(r.requested_start_date, 'DD/MM/YYYY HH24:MI:SS') next_run,

to_char(r.last_update_date, 'DD/MM/YYYY HH24:MI:SS') 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,

per_all_people_f per

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

and nvl(per.effective_start_date, sysdate - 1) < sysdate

and nvl(per.effective_end_date, sysdate + 1) > sysdate

and s.employee_id = per.person_id

order by s.user_name, program;

2 views0 comments

Recent Posts

See All

Get DDL for Oracle object

set feed off set long 1000000 set longchunksize 1000000 set pages 0 set lines 32767 set trimspool on set feed off set wrap on set serveroutput on format wrapped set def off set tab off exec dbms_metad

Cancelled scheduled concurrent requests

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

Foreign key constraint violation

/* This PL/SQL script checks violations of foreign key constraints Example of the tables: CREATE TABLE temp_supplier ( supplier_id numeric(10) not null, supplier_name varchar2(50) not null, contact_na