SQL – extracting the workflow queue.

(No Ratings Yet)

 Loading ...

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