Active Document Composition Templates

[Mike_Kroner1]

Hello,

How can I go about to find which document composition templates are being used in workflows.  I am looking for a SQL query which would show me this information. 

Workflow Action: Compose Document

Use specified template: XXXXXXXX

The underlined part is really what I am looking for.   Bonus If I can get the lifecycle, queue, and template that would be the cherry on top.

Thanks in advance,

Mike

Stay safe

[Larissa_Armand]

Hi Michael,

Here's what we use, this is written for Oracle database so you may need to modify: 

select A.ACTIONNUM, rtrim(A.ACTIONNAME), A.ACTIONTYPE, rtrim(L.LIFECYCLENAME), rtrim(T.DCTEMPLATENAME),
case when bitand(A.FLAGS, 2048) >0 then 'X' else null end as ActionDisabled
from ACTION A
left outer join LIFECYCLE L on A.SCOPE = L.LCNUM
left outer join ACTIONPROPS P on A.ACTIONNUM = P.ACTIONNUM and UPPER(P.PROPERTYNAME) = 'DCTEMPLATENUM'
left outer join DCTEMPLATE T on rtrim(P.PROPERTYVALUE) = T.DCTEMPLATENUM
where A.ACTIONTYPE = '149'
order by L.LIFECYCLENAME
;

[Mike_Kroner1]

Thank you Larissa that is perfect

[Mike_Kroner1]

Larissa,

Is there a way to find out the counts of documents that went through that queue between a date range?  we are trying to get a high level view on  volume count on each action type.

Thanks for your help,

Mike

[Dean_Johnson]

This is one with a range by Entry Time sorted by Exit time.

select d.itemnum, w.entrytime, w.exittime, w.exitusernum, u.realname            FROM hsi.useraccount u, hsi.lifecycle l, hsi.lcstate q, hsi.wflog w, hsi.itemdata d            WHERE w.exitusernum = u.usernum and w.itemnum = d.itemnum             AND w.lcnum = l.lcnum              AND w.statenum = q.statenum            AND w.entryTime between '01-JUN-2018' and '07-JUN-2018'            and q.statenum in (2601) order by w.exittime desc