Claus_Jambrich_5663

Blocked jobs over all clients

Discussion created by Claus_Jambrich_5663 on Dec 14, 2016
Recently, a customer asked for a quick way to find out, what jobs / workflows are blocked in the system at the moment, but over all clients. Besides of course looking into the activities list in client 0

vcyhyqxu1lzb.png
there is a quick way if you have access to the DB.

For MS SQL:
SELECT CASE CAST(EJPP_Status AS CHAR(32))
   WHEN 1560
     THEN 'Workflow is blocked'
   WHEN 1898
     THEN 'Blocked'
   WHEN 1899
     THEN 'Status blocked manually removed'
   ELSE CAST(EJPP_Status AS CHAR(32))
   END               AS [Status in Jobplan]
 , AH.AH_Client      AS [Client]
 , EJPP.EJPP_AH_Idnr AS [RunID]
 , EJPP.EJPP_OType   AS [Object type]
 , EJPP.EJPP_Object  AS [Name]
 , AH.AH_Name        AS [Parent name]
 , EJPP.EJPP_EndTime AS [Block time]
FROM EJPP
  INNER JOIN AH ON EJPP.EJPP_AH_Idnr = AH.AH_Idnr
  WHERE EJPP.EJPP_Status IN (1560, 1898, 1899)
  ORDER BY [Parent name], [Name]


For Oracle
SELECT CASE to_char(EJPP_Status)
   WHEN '1560'
     THEN 'Workflow is blocked'
   WHEN '1898'
     THEN 'Blocked'
   WHEN '1899'
     THEN 'Status blocked manually removed'
   ELSE to_char(EJPP_Status)
   END               AS "Status in Jobplan"
 , AH.AH_Client      AS "Client"
 , EJPP.EJPP_AH_Idnr AS "RunID"
 , EJPP.EJPP_OType   AS "Object type"
 , EJPP.EJPP_Object  AS "Name"
 , AH.AH_Name        AS "Parent name"
 , EJPP.EJPP_EndTime AS "Block time"
FROM EJPP
  INNER JOIN AH ON EJPP.EJPP_AH_Idnr = AH.AH_Idnr
  WHERE EJPP.EJPP_Status IN (1560, 1898, 1899)
  ORDER BY "Parent name", "Name";





Outcomes