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 &lt; 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.

stewart@9rains.com

http://stewartschatz.com

Average Rating

5 Star

4 Star

3 Star

2 Star

1 Star

(Add your review)

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

Dan Ross says:

April 7, 2015 at 4:56 PM

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.

Hector Mario Quevedo says:

July 11, 2016 at 9:22 AM

Great! This help me a lot!!

How to Use the XCOPY Command in Windows

How to Clean Up Empty Directories Using the find Command in Linux

How to Print a Directory Structure Using PowerShell: Step-by-Step Guide

VirtualBox Fails After Windows 11 Upgrade (NtCreateFile failed)

Quick Tip: WebLogic NodeManager Fails To Start As Windows Service

Quick Tip: Determine Active Directory Account Status From The Windows Commandline

Where Is Java Installed?

Quick Tip: IBM Navigator for i Default URL

Windows God Mode… What!?!

An EnterpriseOne Scheduler Report You Can Give A User Using SQL

Author

Stewart Schatz

Average Rating

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

Leave a Reply Cancel reply