04-29-2020 10:52 AM
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
04-29-2020 11:05 AM
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
;
04-29-2020 12:13 PM
Thank you Larissa that is perfect
05-04-2020 12:37 PM
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
05-04-2020 01:08 PM
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
Find what you came for
We want to make your experience in Hyland Connect as valuable as possible, so we put together some helpful links.