SQL – extracting the workflow queue.

Homepage » SQL » SQL – extracting the workflow queue.

SQL – extracting the workflow queue.

By neel March 31st, 2008

This query has been shared by a “Svetj” a member of SiebelUnleashed forum. I find it quite useful in case if I work in admin and need to know the workflow queue.

It shows

The actual queue for a workflow policy group with the insert date of the next record to work so you can know the actual delay of the backlog.
How many records have been processed in the last hour, with the date of the last record processed.
So you can know if the workflow is going on.

Here is the query.

SELECT cnt
      ,code_status
      ,dt_description
      ,to_char(dt,’dd/mm/yyyy hh24.mi.ss’) dt
      ,dd||’ days ‘||hh||’:'||mi||’:'||ss ||’ hours’ delay
FROM   (SELECT COUNT(0) cnt
              ,’Queue’ code_status
              ,’Date of next record to examinate:’ dt_description
              ,MIN(req.created) dt
              ,trunc(86400*(SYSDATE-MIN(req.created)))-60*(trunc((86400*(SYSDATE-MIN(req.created)))/60)) ss,
               trunc((86400*(SYSDATE-MIN(req.created)))/60)-60*(trunc(((86400*(SYSDATE-MIN(req.created)))/60)/60)) mi,
               trunc(((86400*(SYSDATE-MIN(req.created)))/60)/60)-24*(trunc((((86400*(SYSDATE-MIN(req.created)))/60)/60)/24)) hh,
               trunc((((86400*(SYSDATE-MIN(req.created)))/60)/60)/24) dd
        FROM   siebel.s_escl_req   req
              ,siebel.s_escl_group grp
        WHERE req.group_id = grp.row_id
        AND    grp.NAME = ‘Your Gruoup Name’
        UNION
        SELECT COUNT(0) cnt
              ,’Processed’ code_status
              ,’Last record processed on:’ dt_description
              ,MAX(lg.created) dt,
               trunc(86400*(SYSDATE-MAX(lg.created)))-60*(trunc((86400*(SYSDATE-MAX(lg.created)))/60)) ss,
               trunc((86400*(SYSDATE-MAX(lg.created)))/60)-60*(trunc(((86400*(SYSDATE-MAX(lg.created)))/60)/60)) mi,
               trunc(((86400*(SYSDATE-MAX(lg.created)))/60)/60)-24*(trunc((((86400*(SYSDATE-MAX(lg.created)))/60)/60)/24)) hh,
               trunc((((86400*(SYSDATE-MAX(lg.created)))/60)/60)/24) dd
        FROM   siebel.s_escl_log   lg
              ,siebel.s_escl_rule rul
              ,siebel.s_escl_group grp
        WHERE lg.rule_id = rul.row_id
        AND    rul.group_id = grp.row_id
        AND    grp.NAME = ‘Your Gruoup Name’
        AND    lg.created > SYSDATE - 1 / 24)
ORDER BY code_status DESC