Looking for active doc comp templates within an action

[Mike_Kroner1]

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

[Larissa_Armand · Accepted solution]

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
;

[Scott_Haugen]

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?