Purpose
Display all jobs in the JDE Scheduler system in an efficient & easy-to-read format.
About
Like most of the applications in JDE that were built for system administrators, the JDE Scheduler Application P91300 is pretty awful to use. Critical information is scattered throughout several different screens without one good screen to overview the entire schedule.
This query has been optimized to be information-rich in a manner that is readable and easy to share with others. It depends on SQL Server to provide automatic timezone conversion, but can be modified for Oracle or DB2.
Usage
- Search & replace the appropriate database schema prefixes in the FROM section:
- JDE910.SY910 – Your JDE System Library that contains F91300 (JDE Scheduler)
- JDE_PRODUCTION.PRODCTL – Your Production Control Data (UDC Descriptions)
- JDE_PD910.PD910 – Your Production Central Objects (Batch Version Descriptions)
- JDE910.OL910 – Your Object Librarian (Batch Program Description)
- Execute using any SQL tool to receive full list of JDE Scheduler.
- Review the results & share with others.
Excel Template
It is possible to embed this query into an Excel spreadsheet that contains automatic data refresh. We share a copy of the spreadsheet throughout our organization, so that everyone has read-only access to the JDE Scheduler. It contains formatting that helps clearly identify recurrence types and adds nice sort & filter functionality. Please leave a note if you are interested in learning more about how to implement this solution.
Query
SELECT CASE SJSCHJBSTAT WHEN '01' THEN 'Active' ELSE 'Inactive' END as [Active?], SJSCHRCRTYP as [Recurrence Code], CASE SJSCHRCRTYP WHEN '61' THEN 'Repeat Every '+CAST(SJSCHNUMMNS as VARCHAR(2))+' Minutes' WHEN '62' THEN 'Repeat Every '+CAST(SJSCHNUMHRS as VARCHAR(2))+' Hours' WHEN '21' THEN 'Specific Day(s):' + CASE WHEN SJMONDAY = 1 THEN 'M' ELSE '' END + CASE WHEN SJTUESDAY = 1 THEN 'T' ELSE '' END + CASE WHEN SJWEDNESDAY = 1 THEN 'W' ELSE '' END + + CASE WHEN SJTHURSDAY = 1 THEN 'Th' ELSE '' END + CASE WHEN SJFRIDAY = 1 THEN 'F' ELSE '' END + CASE WHEN SJSATURDAY = 1 THEN 'Sa' ELSE '' END + CASE WHEN SJSUNDAY = 1 THEN 'Su' ELSE '' END WHEN '11' THEN 'Daily (Every Day)' WHEN '12' THEN 'Once Every Weekday' ELSE Rec.DRDL01 END as [Recurrence Desc], CAST(dateadd(N,SJSCHSTTIME + DATEPART(TZoffset, SYSDATETIMEOFFSET()), CAST('1/1/1970' as DATETIME2)) as TIME(0)) as [Launch Time], -- Auto-adjust for local timezone current offset SJSCHRPTNM as [Batch Program], SIMD as [Batch Program Desc], SJSCHVER as [Batch Version], VRJD as [Batch Version Desc], SJSCHJBNM as [Scheduled Job Name], SJSCHJBTYP as [Job Type], dateadd(N,SJSCHSTTIME + DATEPART(TZoffset, SYSDATETIMEOFFSET()), CAST('1/1/1970' as DATETIME2)) as [Start Date], SJCURRUNCNT as [Run Count], SJSCHENHV as [Environment], SJSCHJOBSVR as [Server], SJJOBQUE as [Job Queue], DATEADD(DAY, SJUPMJ % 1000 - 1, DATEADD(YEAR, SJUPMJ/ 1000, 0)) as [Last Updated] FROM [JDE910].[SY910].[F91300] LEFT JOIN JDE_PRODUCTION.PRODCTL.F0005 Rec ON SJSCHRCRTYP = RTRIM(LTRIM(Rec.DRKY)) AND Rec.DRSY = 'H91' AND Rec.DRRT = 'RC' LEFT JOIN JDE_PD910.PD910.F983051 Versions ON VRPID = SJSCHRPTNM AND VRVERS = SJSCHVER LEFT JOIN JDE910.OL910.F9860 OL ON SJSCHRPTNM = SIOBNM AND SIFUNO = 'UBE' ORDER BY SJSCHRCRTYP DESC, -- Reccurrence Type SJSCHNUMHRS ASC, -- 'Repeat Every x Hours' SJSCHNUMMNS ASC, -- 'Repeat Every x Minutes' CAST(dateadd(N,SJSCHSTTIME + DATEPART(TZoffset, SYSDATETIMEOFFSET()), CAST('1/1/1970' as DATETIME2)) as TIME(0)) ASC -- Launch Time (Daily)
Hi – what is pchem.fromJDE?
Hi John, sorry for the (very) long delay in getting back to you. You’re the very first human being to post a relevant comment on my blog, so thanks!
To answer your question: PChem.FromJDE() is a user-defined SQL scalar function that converts a JDE Julian Date number into a real SQL Date field. We created it in a custom “PChem” schema to avoid touching official DTA & CTL schemas and use it all over in SQL reporting queries. We also have an equally-useful PChem.ToJDE() that will convert a SQL Date (or properly formatted string) into a JDE Julian Number.
Here are their definitions. You may want to adjust the schema name (and be sure that it actually exists in your database!)
USE [JDE_PRODUCTION]
GO
/****** Object: UserDefinedFunction [PChem].[FromJDE] Script Date: 6/5/2018 2:43:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [PChem].[FromJDE](@julian numeric(18,0)) RETURNS DATE
AS
BEGIN
DECLARE @returnDate date;
SELECT @returnDate = DATEADD(DAY, @julian % 1000 - 1, DATEADD(YEAR, @julian/ 1000, 0));
RETURN @returnDate;
END;
GO
CREATE FUNCTION [PChem].[ToJDE](@date DATE) RETURNS numeric(18,0)
AS
BEGIN
DECLARE @returnJulian numeric(18,0);
SELECT @returnJulian = DATEDIFF(Year,'1900-01-01',@date) * 1000 + DATEPART(DayOfYear, @date);
RETURN @returnJulian;
END;
GO
Alternatively, you can simply “inline” the function definition into the SQL query, replacing PChem.FromJDE(SJUPMJ) with an equivalent SQL expression. For example:
SELECT
-- ... other fields ...,
DATEADD(DAY, SJUPMJ % 1000 - 1, DATEADD(YEAR, SJUPMJ/ 1000, 0))
FROM [JDE910].[SY910].[F91300]
UPDATE: I modified the query in the blog post, removing the reference to PChem.FromJDE() and replacing it with an inline equivalent.
Hi Brandon, do you have the SQL in Oracle database format. I tried converting it but there are too many errors that I get
Thank you for this!!!!
Changed a bit, most output is in Portuguese, as I’m Brazilian:
SELECT
SJSCHJBNM as Scheduled_Job_Name,
SJSCHRPTNM as Batch_Program,
SJSCHVER NOME_VERSAO,
SJJOBQUE FILA_EXEC,
SJSCHRCRTYP as Recurrence_Code,
CASE WHEN to_char(SJSCHRCRTYP) = ’61’ THEN ‘Repete cada ‘ || SJSCHNUMMNS || ‘ Minuto(s)’
WHEN to_char(SJSCHRCRTYP) = ’62’ THEN ‘Repete cada ‘|| SJSCHNUMHRS || ‘ Hora(s)’
when to_char(SJSCHRCRTYP) = ’31’ THEN ‘Repete todo dia ‘|| SJSCHNUMDY || ‘ do mes’
when to_char(SJSCHRCRTYP) = ’32’ THEN case when to_char(SJWEEKDAY) = ‘1’ then ‘Repete todo último dia útil do mes’
else ‘Repete todo último dia do mes’ end
WHEN to_char(SJSCHRCRTYP) = ’21’ THEN ‘Dia(s) especifico(s): ‘ || CASE WHEN to_char(SJMONDAY) = ‘1’ THEN ‘Seg ‘ ELSE ” END
|| CASE WHEN to_char(SJTUESDAY) = ‘1’ THEN ‘Ter ‘ ELSE ” END
|| CASE WHEN to_char(SJWEDNESDAY) = ‘1’ THEN ‘Qua ‘ ELSE ” END
|| CASE WHEN to_char(SJTHURSDAY) = ‘1’ THEN ‘Qui ‘ ELSE ” END
|| CASE WHEN to_char(SJFRIDAY) = ‘1’ THEN ‘Sex ‘ ELSE ” END
|| CASE WHEN to_char(SJSATURDAY) = ‘1’ THEN ‘Sab ‘ ELSE ” END
|| CASE WHEN to_char(SJSUNDAY) = ‘1’ THEN ‘Dom ‘ ELSE ” END
WHEN to_char(SJSCHRCRTYP) = ’11’ THEN ‘Todo Dia’
WHEN to_char(SJSCHRCRTYP) = ’12’ THEN ‘Uma vez por dia útil’
ELSE to_char(Rec.DRDL01)
END as Recurrence ,
TO_DATE(‘1970-01-01 00:00’, ‘YYYY-MM-DD HH24:MI’) + (SJSCHSTTIME / 60 / 24) Start_date,
(SELECT substr(to_char(FROM_TZ(CAST(TO_DATE(‘1970-01-01 00:00’, ‘YYYY-MM-DD HH24:MI’) + (SJSCHSTTIME / 60 / 24) AS TIMESTAMP), ‘Greenwich’) AT TIME ZONE ‘-3:00’), 9,9) FROM DUAL) as Start_Hora,
SJCURRUNCNT as Run_Count,
SJSCHENHV as Environment,
SJSCHJOBSVR as Server,
substr(to_char(to_date(SJUPMJ+1900000, ‘YYYYDDD’)), 0,8) as Last_Updated
FROM
SY900.F91300
LEFT JOIN PRODCTL.F0005D Rec ON SJSCHRCRTYP = RTRIM(LTRIM(Rec.DRKY)) AND Rec.DRSY = ‘H91’ AND Rec.DRRT = ‘RC’
LEFT JOIN PD900.F983051 Versions ON VRPID = SJSCHRPTNM AND VRVERS = SJSCHVER
LEFT JOIN OL900.F9860 OL ON SJSCHRPTNM = SIOBNM AND SIFUNO = ‘UBE’
where to_char(SJSCHJBSTAT) = ’01’
and SJSCHENHV like ‘%PD%’;
Hi Vanessa,
Thanks for your contribution. I appreciate the effort you put in to modify the date/time conversions for another DB.
Can you clarify if this query was re-written for Oracle or for DB2?
Thanks,
Brandon