We can identify which standard letters are being used by analyzing letter generation records in the database.
This shows us which letters have been created, when, and by which teams.
Letters that have never been generated are candidates for review and potential removal to streamline the system.
We have found some select SQL queries which should be able to help you with this below (please run within a non live environment)-
1. Find All Letter Templates
Query MO_TEMPLATE_VERSIONS table where CATEGORY = 'LETTER' to see all available letter templates in your system.
2. Identify Which Letters Have Been Used
The MO_FORMS table records every instance of a generated letter. To find which templates have been used:
SELECT
mtv.TEMPLATE_ID,
mtv.TITLE,
COUNT(mf.FORM_ID) as usage_count,
MAX(mf.CREATED_ON) as last_used_date
FROM MO_TEMPLATE_VERSIONS mtv
LEFT JOIN MO_FORMS mf ON mtv.TEMPLATE_VERSION_ID = mf.TEMPLATE_VERSION_ID
WHERE mtv.CATEGORY = 'LETTER'
GROUP BY mtv.TEMPLATE_ID, mtv.TITLE
ORDER BY usage_count DESC;
Templates with usage_count = 0 have never been used and could be candidates for retirement.
3. Identify Who Uses Each Letter (By Worker)
The MO_FORMS table tracks which worker created each letter via CREATED_BY_WORKER_ID:
SELECT
mtv.TITLE AS letter_title,
CONCAT(w.FIRST_NAMES, ' ', w.LAST_NAMES) AS worker_name,
COUNT(mf.FORM_ID) AS times_used
FROM MO_FORMS mf
INNER JOIN MO_TEMPLATE_VERSIONS mtv
ON mf.TEMPLATE_VERSION_ID = mtv.TEMPLATE_VERSION_ID
INNER JOIN WORKERS w
ON mf.CREATED_BY_WORKER_ID = w.ID
WHERE mtv.CATEGORY = 'LETTER'
GROUP BY
mtv.TITLE,
CONCAT(w.FIRST_NAMES, ' ', w.LAST_NAMES)
ORDER BY
mtv.TITLE,
times_used DESC;
4. Identify Which Teams Use Each Letter to aggregate by team/organization, join with WORKER_ROLES:
SELECT
mtv.TITLE as letter_title,
o.NAME as team_name,
COUNT(mf.FORM_ID) as times_used
FROM MO_FORMS mf
INNER JOIN MO_TEMPLATE_VERSIONS mtv ON mf.TEMPLATE_VERSION_ID = mtv.TEMPLATE_VERSION_ID
INNER JOIN WORKERS w ON mf.CREATED_BY_WORKER_ID = w.ID
INNER JOIN WORKER_ROLES wr ON w.ID = wr.WORKER_ID AND wr.PRIMARY_JOB = 'Y'
INNER JOIN ORGANISATIONS o ON wr.ORG_ID = o.ID
WHERE mtv.CATEGORY = 'LETTER'
AND wr.START_DATE <= mf.CREATED_ON
AND (wr.END_DATE IS NULL OR wr.END_DATE >= mf.CREATED_ON)
GROUP BY mtv.TITLE, o.NAME
ORDER BY mtv.TITLE, times_used DESC;
If you need any further information or this information hasn't covered what you need please raise a case.