Compliance Accelerator Department Tree View does not populate the list of Departments

Problem

Opening the Departments Tab in the Enterprise Vault (EV) Compliance Accelerator (CA) defaults to a Tree View whereby each Department is listed with any Child Departments displayed directly under their respective Parent Departments. In a rare circumstance, the Tree View may not populate; however, clicking on the List View icon does populate the list of Departments, ordered alphabetically by Department name.

Error Message

Cause

This rare circumstance occurs when a Parent Department is deleted and the deletion process does not complete deleting any present Child Departments. This causes the Tree View to not populate correctly, displaying a blank screen as a result. The List View does populate correctly as this view does not show any parent-child relationships.

Solution

To determine if this condition exists, execute the following SQL query against the CA Customer database:

;WITH ChildCases AS (
-- Identify all Child Cases
SELECT Name, CaseID, ParentCaseID, ParentExceptionID, StatusID, MarkedForDeletion, DeletedByPrincipalID
FROM tblCase
WHERE (FolderType = 330 -- Only look at Cases/Departments ...
AND (ParentCaseID != -1 OR ParentExceptionID IS NOT NULL)) -- that are Children ...
AND MarkedForDeletion = 0) -- and are not MarkedForDeletion
SELECT
-- List the Parents
tc.Name AS 'Parent',
tc.CaseID AS 'Parent CaseID',
tc.ParentCaseID,
tc.ParentExceptionID,
tc.StatusID AS 'Parent StatusID',
ts1.Name AS 'Parent Status',
tc.MarkedForDeletion AS 'Parent MarkedForDeletion',
tc.DeletedByPrincipalID AS 'Parent DeletedByPrincipalID',
-- List the Children
CC.Name AS 'Child',
CC.CaseID AS 'Child CaseID',
CC.ParentCaseID,
CC.ParentExceptionID,
CC.StatusID AS 'Child StatusID',
ts2.Name AS 'Child Status',
CC.MarkedForDeletion AS 'Child MarkedForDeletion',
CC.DeletedByPrincipalID AS 'Child DeletedByPrincipalID'
FROM tblCase AS tc
JOIN ChildCases AS CC ON tc.CaseID = CC.ParentCaseID OR tc.CaseID = CC.ParentExceptionID
JOIN tblStatus AS ts1 ON tc.StatusID = ts1.StatusID
JOIN tblStatus AS ts2 ON CC.StatusID = ts2.StatusID
WHERE tc.MarkedForDeletion = 1 -- List only Parents that are MarkedForDeletion
ORDER BY tc.CaseID, CC.CaseID;

If the query returns any results, please contact technical support to complete the deletion process for any undeleted Child Departments. The steps will require stopping the Enterprise Vault Accelerator Manager Service and manually completing the deletion process via SQL.

A pre-requisite to implementing the remediation steps is to ensure a current SQL backup of the CA Customer database is available. If not available, a SQL Backup of the CA Customer database must be obtained prior to implementing the remediation steps.