An EnterpriseOne Scheduler Report You Can Give A User Using SQL
Read Time:9 Min, 8 Sec
Have you ever had a need to give a user a report of what runs on the scheduler including the UBE name and version, the day and time and how often it runs and the UserID that runs it?
Well, with a little bit (a very little bit to make it work for EnterpriseOne 9.1) of modification to b_gilmore’s post on JDEList you can use SQL to get a report that can be easily understood by users.
Here is the process that I used:
- Start a new query on the JDE910 database in SQL Server Management Studio
- Paste the contents of the SQL below into the SQL window
- Select all the rows by clicking the upper left corner of the results
- Right-click and choose “Copy with Headers”
- Paste into Excel
/********************************************/ declare @offset int set @offset = datediff(minute, GetUTCDate(), GetDate()) / 60 select d.Scheduler_job_name, e.run_count, e.max_run_time, e.avg_run_time, e.max_wait_time, e.avg_wait_time, d.ube_name, d.version_name, d.submit_user, d.jobqueue, d.Schedule_StartDate, d.Schedule_StartTime, d.schedule_type, d.sched_period, d.sched_desc, d.sched_Monday, d.sched_Tuesday, d.sched_Wednesday, d.sched_Thursday, d.sched_Friday, d.sched_Saturday, d.sched_Sunday from ( SELECT RTRIM (a.sjschjbnm) Scheduler_job_name, RTRIM (a.sjschrptnm) ube_name, RTRIM (a.sjschver) version_name, RTRIM (a.sjschuser) submit_user, RTRIM (a.sjjobque) jobqueue, convert(varchar(8), dateadd(hour, @offset, dateadd(minute, a.sjschsttime,{d '1970-01-01'})),108) Schedule_StartTime, convert(varchar(10), dateadd(hour, @offset, dateadd(minute, a.sjschsttime,{d '1970-01-01'})), 101) Schedule_StartDate, a.sjschrcrtyp schedule_type, case when a.sjschrcrtyp in ('61') then 'MINUTE' when a.sjschrcrtyp in ('62') then 'HOURLY' when a.sjschrcrtyp in ('11', '12', '21') then 'DAILY' when a.sjschrcrtyp in ('31') then 'MONTHLY' end as sched_period, case when a.sjschrcrtyp = '61' then 'Every ' + rtrim(cast(a.sjschnummns as char)) + ' mins' when a.sjschrcrtyp = '62' then 'Every ' + rtrim(cast(a.sjschnumhrs as char)) + ' hours' when a.sjschrcrtyp = '11' and a.sjschnumdy = 1 then 'Every Day' when a.sjschrcrtyp = '11' and a.sjschnumdy != 1 then 'Every ' + rtrim(cast(a.sjschnumdy as char)) + ' days' when a.sjschrcrtyp = '31' then 'On day ' + rtrim(a.sjschnumdy) + ' of the month, Every ' + rtrim(a.sjschnummn) + ' months' when a.sjschrcrtyp = '12' then 'Weekdays' when a.sjschrcrtyp = '21' then 'Specified Days' end as sched_desc, case when (a.sjschrcrtyp = '11' and a.sjschnumdy = 1) or (a.sjschrcrtyp = '12' or a.sjschrcrtyp = '21' and a.sjmonday = '1') then 'X' else '' end as sched_Monday, case when (a.sjschrcrtyp = '11' and a.sjschnumdy = 1) or (a.sjschrcrtyp = '12' or a.sjschrcrtyp = '21' and a.sjtuesday = '1') then 'X' else '' end as sched_Tuesday, case when (a.sjschrcrtyp = '11' and a.sjschnumdy = 1) or (a.sjschrcrtyp = '12' or a.sjschrcrtyp = '21' and a.sjwednesday = '1') then 'X' else '' end as sched_Wednesday, case when (a.sjschrcrtyp = '11' and a.sjschnumdy = 1) or (a.sjschrcrtyp = '12' or a.sjschrcrtyp = '21' and a.sjthursday = '1') then 'X' else '' end as sched_Thursday, case when (a.sjschrcrtyp = '11' and a.sjschnumdy = 1) or (a.sjschrcrtyp = '12' or a.sjschrcrtyp = '21' and a.sjfriday = '1') then 'X' else '' end as sched_Friday, case when (a.sjschrcrtyp = '11' and a.sjschnumdy = 1) or (a.sjschrcrtyp = '21' and a.sjsaturday = '1') then 'X' else '' end as sched_Saturday, case when (a.sjschrcrtyp = '11' and a.sjschnumdy = 1) or (a.sjschrcrtyp = '21' and a.sjsunday = '1') then 'X' else '' end as sched_Sunday FROM SY910.f91300 a WHERE a.sjschjbstat = '01' and a.sjschrcrtyp in ('61', '62', '11', '12', '21', '31') ) as d left outer join ( select a.sjschjbnm, count(1) as run_count, max( convert(varchar(8), dateadd(second, datediff(s, dateadd(hour, @offset, dateadd(minute, b.jsschlnctime,{d '1970-01-01'})), cast( convert(varchar(10), dateadd(d, substring(cast(c.jcactdate as char), 4, 3)-1, convert(datetime, '20' + substring(cast(c.jcactdate as char), 2, 2) + '-01-01 ')), 101) + ' ' + substring(right('000000', 6 - len(c.jcacttime)) + cast(jcacttime as char), 1,2) + ':' + substring(right('000000', 6 - len(c.jcacttime)) + cast(jcacttime as char), 3,2) + ':' + substring(right('000000', 6 - len(c.jcacttime)) + cast(jcacttime as char), 5,2) as datetime) ), 0), 108)) as max_run_time, convert(varchar(8), dateadd(second, avg(datediff(s, dateadd(hour, @offset, dateadd(minute, b.jsschlnctime,{d '1970-01-01'})), cast( convert(varchar(10), dateadd(d, substring(cast(c.jcactdate as char), 4, 3)-1, convert(datetime, '20' + substring(cast(c.jcactdate as char), 2, 2) + '-01-01 ')), 101) + ' ' + substring(right('000000', 6 - len(c.jcacttime)) + cast(jcacttime as char), 1,2) + ':' + substring(right('000000', 6 - len(c.jcacttime)) + cast(jcacttime as char), 3,2) + ':' + substring(right('000000', 6 - len(c.jcacttime)) + cast(jcacttime as char), 5,2) as datetime) )), 0), 108) as avg_run_time, max( convert(varchar(8), dateadd(second, datediff(s, dateadd(hour, @offset, dateadd(minute, b.jsschsttime,{d '1970-01-01'})), dateadd(hour, @offset, dateadd(minute, b.jsschlnctime,{d '1970-01-01'})) ), 0), 108)) as max_wait_time, convert(varchar(8), dateadd(second, avg( datediff(s, dateadd(hour, @offset, dateadd(minute, b.jsschsttime,{d '1970-01-01'})), dateadd(hour, @offset, dateadd(minute, b.jsschlnctime,{d '1970-01-01'})) )), 0), 108) as avg_wait_time FROM SY910.f91300 a join SY910.f91320 as b on a.sjschjbnm = b.jsschjbnm join svm910.f986110 as c on b.jsjobnbr = c.jcjobnbr WHERE a.sjschjbstat = '01' and a.sjschrcrtyp in ('61', '62', '11', '12', '21', '31') and b.jsschlncstat = 20 and jslncenvh = 'PD812' and b.jsschlnctime < b.jsschsttime + 1440 and c.jcjobsts = 'D' group by a.sjschjbnm ) as e on d.Scheduler_job_name = e.sjschjbnm order by d.sched_period, d.Schedule_StartTime, d.ube_name, d.version_name
Oracle Code with adjustments:
SELECT TRIM (a.sjschjbnm) Scheduler_job_name, TRIM (a.sjschrptnm) ube_name, TRIM (a.sjschver) version_name, TRIM (sjschjbstat) status, TRIM (a.sjschuser) submit_user, TRIM(a.SJSCHJOBSVR) EXEHOST,TRIM (a.sjjobque) jobqueue, TO_CHAR (FROM_TZ (CAST (( TO_DATE ('1970-01-01', 'YYYY-MM-DD') + NUMTODSINTERVAL (sjschsttime, 'MINUTE') ) AS TIMESTAMP ), 'UTC' ) AT TIME ZONE 'America/Chicago', 'MM/DD/YYYY' ) Schedule_StartDate, TO_CHAR (FROM_TZ (CAST (( TO_DATE ('1970-01-01', 'YYYY-MM-DD') + NUMTODSINTERVAL (sjschsttime, 'MINUTE') ) AS TIMESTAMP ), 'UTC' ) AT TIME ZONE 'America/Chicago', 'HH24:MI:SS') schedule_time, TRIM (a.sjschrcrtyp) schedule_type, DECODE (sjschrcrtyp, '61', 'Every ' || SJSCHNUMMNS --DECODE (sjschnummns, '1', NULL, sjschnummns) || ' min(s)', '62', 'Every ' || sjschnumhrs --DECODE (sjschnumhrs, '1', NULL, sjschnumhrs) || ' hour(s)', '11', 'Every ' || sjschnumdy --DECODE (sjschnumdy, '1', NULL, sjschnumdy) || ' day(s)', '12', 'Every weekday', '21', 'Every ' || sjschnumwks --DECODE (a.sjschnumwks, '1', NULL, sjschnumwks) || ' week(s), on ' || DECODE (a.sjmonday, '1', 'Monday,', NULL) || DECODE (a.sjtuesday, '1', 'Tuesday,', NULL) || DECODE (a.sjwednesday, '1', 'Wednesday,', NULL) || DECODE (a.sjthursday, '1', 'Thursday,', NULL) || DECODE (a.sjfriday, '1', 'Friday,', NULL) || DECODE (a.sjsaturday, '1', 'Saturday,', NULL) || DECODE (a.sjsunday, '1', 'Sunday,', NULL), '31', 'on day ' || sjschnumdy || ' of the month, Every ' || DECODE (sjschnummn, '1', NULL, sjschnummn) || ' month(s)', '32', 'on ' || DECODE (a.sjschnumwks, '1', '1st ', '2', '2nd ', '3', '3rd ', '5', 'last ', sjschnumwks || 'th ' ) || DECODE (a.sjschday, '1', 'day', NULL) || DECODE (a.sjweekday, '1', 'weekday', NULL) || DECODE (a.sjweekend, '1', 'Weekend', NULL) || DECODE (a.sjmonday, '1', 'Monday,', NULL) || DECODE (a.sjtuesday, '1', 'Tuesday,', NULL) || DECODE (a.sjwednesday, '1', 'Wednesday,', NULL) || DECODE (a.sjthursday, '1', 'Thursday,', NULL) || DECODE (a.sjfriday, '1', 'Friday,', NULL) || DECODE (a.sjsaturday, '1', 'Saturday,', NULL) || DECODE (a.sjsunday, '1', 'Sunday,', NULL) || ' of Every ' || DECODE (sjschnummn, '1', NULL, sjschnummn) || ' month(s)', '41', 'on day ' || sjschnumdy || ' Every ' || DECODE (sjschnummn, '1', NULL, sjschnummn) || ' period(s) of company ' || sjco, '42', 'on ' || DECODE (a.sjschnumwks, '1', '1st ', '2', '2nd ', '3', '3rd ', '5', 'last ', sjschnumwks || ' th ' ) || DECODE (a.sjschday, '1', 'day', NULL) || DECODE (a.sjweekday, '1', 'weekday', NULL) || DECODE (a.sjweekend, '1', 'Weekend', NULL) || DECODE (a.sjmonday, '1', 'Monday,', NULL) || DECODE (a.sjtuesday, '1', 'Tuesday,', NULL) || DECODE (a.sjwednesday, '1', 'Wednesday,', NULL) || DECODE (a.sjthursday, '1', 'Thursday,', NULL) || DECODE (a.sjfriday, '1', 'Friday,', NULL) || DECODE (a.sjsaturday, '1', 'Saturday,', NULL) || DECODE (a.sjsunday, '1', 'Sunday,', NULL) || ' of Every ' || DECODE (sjschnummn, '1', NULL, sjschnummn) || ' period(s) of company ' || sjco, '51', 'each ' || sjschnumdy || ' of ' || DECODE (sjschnummn, '1', 'January', '2', 'February', '3', 'March', '4', 'April', '5', 'May', '6', 'June', '7', 'July', '8', 'August', '9', 'September', '10', 'October', '11', 'November', '12', 'December' ), '52', 'on ' || DECODE (a.sjschnumwks, '1', '1st ', '2', '2nd ', '3', '3rd ', '5', 'last ', sjschnumwks || ' th ' ) || DECODE (a.sjschday, '1', 'day', NULL) || DECODE (a.sjweekday, '1', 'weekday', NULL) || DECODE (a.sjweekend, '1', 'Weekend', NULL) || DECODE (a.sjmonday, '1', 'Monday', NULL) || DECODE (a.sjtuesday, '1', 'Tuesday', NULL) || DECODE (a.sjwednesday, '1', 'Wednesday', NULL) || DECODE (a.sjthursday, '1', 'Thursday', NULL) || DECODE (a.sjfriday, '1', 'Friday', NULL) || DECODE (a.sjsaturday, '1', 'Saturday', NULL) || DECODE (a.sjsunday, '1', 'Sunday', NULL) || ' of ' || DECODE (sjschnummn, '1', 'January', '2', 'February', '3', 'March', '4', 'April', '5', 'May', '6', 'June', '7', 'July', '8', 'August', '9', 'September', '10', 'October', '11', 'November', '12', 'December' ), '53', 'on day ' || sjschnumdy || ' of period #' || sjschnummn || ' of company ' || sjco, '54', 'on ' || DECODE (a.sjschnumwks, '1', '1st ', '2', '2nd ', '3', '3rd ', '5', 'last ', sjschnumwks || 'th ' ) || DECODE (a.sjschday, '1', 'day', NULL) || DECODE (a.sjweekday, '1', 'weekday', NULL) || DECODE (a.sjweekend, '1', 'Weekend', NULL) || DECODE (a.sjmonday, '1', 'Monday', NULL) || DECODE (a.sjtuesday, '1', 'Tuesday', NULL) || DECODE (a.sjwednesday, '1', 'Wednesday', NULL) || DECODE (a.sjthursday, '1', 'Thursday', NULL) || DECODE (a.sjfriday, '1', 'Friday', NULL) || DECODE (a.sjsaturday, '1', 'Saturday', NULL) || DECODE (a.sjsunday, '1', 'Sunday', NULL) || ' of period #' || sjschnummn || ' of company ' || sjco ) AS scheduling, (SELECT NVL (TO_CHAR (FROM_TZ (CAST (( TO_DATE ('1970-01-01', 'YYYY-MM-DD') + NUMTODSINTERVAL (MAX (last_exec.jsschsttime), 'MINUTE' ) ) AS TIMESTAMP ), 'UTC' ) AT TIME ZONE 'America/Chicago', 'MM/DD/YYYY HH24:MI:SS' ), 'NA' ) FROM sy900.f91320 last_exec WHERE last_exec.jsschjbnm = a.sjschjbnm AND last_exec.jsschlncstat = '20') DateTime_LastExec, (SELECT NVL (TO_CHAR (FROM_TZ (CAST (( TO_DATE ('1970-01-01', 'YYYY-MM-DD') + NUMTODSINTERVAL (MIN (next_exec.jsschsttime), 'MINUTE' ) ) AS TIMESTAMP ), 'UTC' ) AT TIME ZONE 'America/Chicago', 'MM/DD/YYYY HH24:MI:SS' ), 'NA' ) FROM sy900.f91320 next_exec WHERE next_exec.jsschjbnm = a.sjschjbnm AND next_exec.jsschlncstat = '01') DateTime_NextExec FROM sy900.f91300 a WHERE a.sjschjbstat = '01' ORDER BY sjschjbnm;
This is excellent. I am writing a report to show when reports are scheduled to run, and whether they ran successfully. This gives me a great start.
Great! This help me a lot!!