Handling Undispatched Activities

SQL query below created by Stanislav Matejovsky with the assistance of Jayant from Cisco TAC and Reshib from eGain.
-- BEFORE MAKING ANY CHANGES TO THE DATABASE WITH THESE QUERIES, STOP THE DX PROCESS FIRST!!
 
USE [eGActiveDB];
 
-- List all undispatched activities.
-- Filter only the highest ACTIVITY_ID per CASE_ID, as only those are relevant for resending.
SELECT ACT.*
FROM [eGActiveDB].[dbo].[EGPL_CASEMGMT_ACTIVITY] ACT
WHERE ACT.ACTIVITY_MODE = 200 
  AND ACT.ACTIVITY_TYPE = 1 
  AND ACT.CASE_ID IN (
    SELECT CASE_ID
    FROM [eGActiveDB].[dbo].[EGPL_CASEMGMT_ACTIVITY] ACTin
    WHERE ACTin.ACTIVITY_SUB_TYPE = 13
  )
  AND ACT.ACTIVITY_SUB_TYPE <> 13;
 
-- Update activity data to "ready to send" status.
-- This allows the DX process to pick up the activities and push them into the DX_WORK table.
UPDATE [dbo].[EGPL_CASEMGMT_ACTIVITY]
SET 
    [ACTIVITY_STATUS] = 7000,           -- 7000 = ready for dispatch
    [ACTIVITY_SUB_STATUS] = 7100,       -- 7100 = initial state for dispatch
    [FOLDER_ID] = 6                     -- 6 = IN PROGRESS, 7 = COMPLETED
WHERE ACTIVITY_ID IN (123,456,789);     -- Replace with ACTIVITY_IDs from previous SELECT
 
-- List all activities in the DX_WORK table (i.e. activities currently in processing).
-- If INSTANCE_ID = -1, it's valid; correct ALIAS_ID is just for first attempt of dispatch.
SELECT [INSTANCE_ID],
       [ACTIVITY_ID],
       [EMAIL_ID],
       [RETRY_COUNT],
       [INSTANCE_DATE]
FROM [eGActiveDB].[dbo].[EGML_DX_WORK];
 
-- If the original receive alias was deleted (because you don't have this guide and tried to fix it ;) ), and you see errors in the DX logs saying 
-- "SMTP server not defined for alias" during resend attempts, you must reassign the ALIAS_ID.
-- Get old and new ALIAS_IDs from this table:
SELECT *
FROM [eGActiveDB].[dbo].[EGML_MAILHOST];
 
-- Update ALIAS_ID for affected emails.
UPDATE [dbo].[EGML_EMAIL]
SET [ALIAS_ID] = 1010                  -- Replace with new ALIAS_ID from previous SELECT
WHERE ACTIVITY_ID IN (123,456,789);    -- Use ACTIVITY_IDs from the first SELECT