08-27-2020 11:39 AM
Hello all,
We have close to 2,000 doc comp templates over the past 8 years showing under templates. Around 400 of these templates are currently being used today. How can I run a SQL query to see which templates are being utilized as we are trying to clean up these templates as we need to update the logo on 400 of these templates.
Thanks in advance,
Mike
08-27-2020 11:50 AM
This is written for Oracle DB so you'd need to modify the flags calculations if you aren't using Oracle. This will get you a list of actions that compose documents and the template information.
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
;
08-27-2020 11:50 AM
This is written for Oracle DB so you'd need to modify the flags calculations if you aren't using Oracle. This will get you a list of actions that compose documents and the template information.
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
;
08-27-2020 12:16 PM
I might caution, based on your implementation and what you are doing, an "action" might not be the only way a letter is composed in your system. You might have a Unity Script composing the letter via the API?
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.