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:

  1. Start a new query on the JDE910 database in SQL Server Management Studio
  2. Paste the contents of the SQL below into the SQL window
  3. Select all the rows by clicking the upper left corner of the results
  4. Right-click and choose “Copy with Headers”
  5. 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;

Author

Stewart Schatz

Career: Principal CNC Consultant for Syntax Systems Limited specializing Oracle JD Edwards EnterpriseOne and the technology that supports it. Side Hustle: Owner/Operator of E1Tips.com Location: Lancaster, PA USA  What I like to do: Invest in Family, Explore Technology, Lead Teams, Share Knowledge/Experience, Hunt, Hike, etc.
Happy
Happy
0
Sad
Sad
0
Excited
Excited
0
Sleepy
Sleepy
0
Angry
Angry
0
Surprise
Surprise
0

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

2 thoughts on “An EnterpriseOne Scheduler Report You Can Give A User Using SQL

  1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Previous post Copy All Files New Than A Date Using A BAT File
Next post How To Disable All Users Not In A Certain EnterpriseOne Role