Do you ever have WOs being updated/modified while the system is in Admin Mode? I have found that some of the Ancestor tables rely on Listeners that don't seem to work in Admin Mode.
Anyways, the following procedure is intended for an Oracle environment. It only adds missing records and deletes invalid records without truncating the entire WOAncestor table.
/*
** PROCEDURE SyncWOAncestorTable - This procedure inserts missing
** WOAncestor records and deletes extraneous WOAncestor records.
*/
PROCEDURE SyncWOAncestorTable
IS
/* Local Variables */
v_HierarchyLevels WOAncestor.HierarchyLevels%TYPE ;
v_LoopIteration NUMBER ;
v_MaxHierarchyLevels WOAncestor.HierarchyLevels%TYPE ;
v_RowCount NUMBER ;
BEGIN
v_ProcName := 'SyncWOAncestorTable' ;
v_ProcessName := v_ProcName ;
InsertMessageLog('Starting.', SYSDATE) ;
/*
** INSERT WOANCESTOR LEVEL 0: For every WorkOrder, a WOAncestor record
** should exist at Hierarchy Level 0 where both the WONum and the
** Ancestor in the WOAncestor record match the the WorkOrder.WONum.
** This step inserts that record if it is missing.
*/
v_ProcStep := 'INSERT WOANCESTOR LEVEL 0' ;
BEGIN
INSERT INTO WOAncestor
(WONum,
Ancestor,
HierarchyLevels,
OrgID,
SiteID,
WOAncestorID)
SELECT WONum,
WONum,
0,
OrgID,
SiteID,
WOAncestorSeq.NEXTVAL
FROM WorkOrder R
WHERE NOT EXISTS
(SELECT 1
FROM WOAncestor A
WHERE A.WONum = R.WONum
and A.Ancestor = R.WONum
and A.SiteID = R.SiteID
)
;
v_RowCount := SQL%ROWCOUNT ;
v_LogMessage := v_ProcStep
|| ' Step inserted '
|| v_RowCount
|| ' WOAncestor records.'
;
InsertMessageLog(v_LogMessage) ;
END ;
/*
** DELETE WOANCESTOR LEVEL 0: This step deletes records at Hierarchy
** Level 0 where the WONum does not match the Ancestor.
*/
v_ProcStep := 'DELETE WOANCESTOR LEVEL 0' ;
BEGIN
DELETE
FROM WOAncestor A
WHERE HierarchyLevels = 0
and A.Ancestor != A.WONum
;
v_RowCount := SQL%ROWCOUNT ;
v_LogMessage := v_ProcStep
|| ' Step deleted '
|| v_RowCount
|| ' WOAncestor records.'
;
InsertMessageLog(v_LogMessage) ;
END ;
/*
** GET MAX HIERARCHY LEVELS: This step gets the maximum HiearchyLevels
** value for use in the next step.
*/
v_ProcStep := 'GET MAX HIERARCHY LEVELS' ;
BEGIN
SELECT MAX(HierarchyLevels)
INTO v_MaxHierarchyLevels
FROM WOAncestor
;
END ;
/*
** DELETE OTHER WOANCESTOR: This step deletes records at Hierarchy
** Levels higher than 0 that should not exist. It does this by looping
** through Ancestor records one level at a time, reading the WOAncestor
** record one level down and the WorkOrder record for the Ancestor at
** that level, and comparing the Parent on that WorkOrder record to the
** Ancestor on the main record that was read. If the Parent does not
** match, the WOAncestor record is deleted.
*/
v_ProcStep := 'DELETE OTHER WOANCESTOR' ;
BEGIN
v_HierarchyLevels := 0 ;
LOOP
v_HierarchyLevels := v_HierarchyLevels
+ 1
;
DELETE
FROM WOAncestor
WHERE WOAncestorID
IN (SELECT A.WOAncestorID
FROM WOAncestor A
LEFT OUTER JOIN WOAncestor AM1
ON AM1.WONum = A.WONum
and AM1.SiteID = A.SiteID
and AM1.HierarchyLevels = A.HierarchyLevels
- 1
LEFT OUTER JOIN WorkOrder RM1
ON RM1.WONum = AM1.Ancestor
and RM1.SiteID = AM1.SiteID
WHERE A.HierarchyLevels = v_HierarchyLevels
and A.Ancestor
!= COALESCE(RM1.Parent, 'x')
)
;
v_RowCount := SQL%ROWCOUNT ;
v_LogMessage := v_ProcStep
|| ' Step deleted '
|| v_RowCount
|| ' WOAncestor records at'
|| ' at Hierarchy Level '
|| v_HierarchyLevels
|| '.'
;
InsertMessageLog(v_LogMessage) ;
IF v_HierarchyLevels >= v_MaxHierarchyLevels
THEN
EXIT ;
END IF ;
END LOOP ;
END ;
/*
** INSERT OTHER WOANCESTOR: This step inserts records at Hierarchy
** Levels higher than 0 that are missing. It does this by joining
** the WorkOrder table to the WOAncestor records for its Parent and
** then checking to see if a WOAncestor record already exists for the
** WO with each of the Parent's Ancestors. If it does not, a new
** WOAncestor record is inserted.
*/
v_ProcStep := 'INSERT OTHER WOANCESTOR' ;
BEGIN
v_LoopIteration := 0 ;
LOOP
v_LoopIteration := v_LoopIteration
+ 1
;
INSERT INTO WOAncestor
(WONum,
Ancestor,
HierarchyLevels,
OrgID,
SiteID,
WOAncestorID)
SELECT R.WONum,
A1.Ancestor,
A1.HierarchyLevels + 1,
R.OrgID,
R.SiteID,
WOAncestorSeq.NEXTVAL
FROM WorkOrder R
INNER JOIN WOAncestor A1
ON A1.WONum = R.Parent
and A1.SiteID = R.SiteID
WHERE R.Parent IS NOT NULL
and NOT EXISTS
(SELECT 1
FROM WOAncestor A2
WHERE A2.WONum = R.WONum
and A2.Ancestor = A1.Ancestor
and A2.SiteID = R.SiteID
)
;
v_RowCount := SQL%ROWCOUNT ;
v_LogMessage := v_ProcStep
|| ' Step inserted '
|| v_RowCount
|| ' WOAncestor records'
|| ' during Loop Iteration '
|| v_LoopIteration
|| '.'
;
InsertMessageLog(v_LogMessage) ;
IF v_RowCount = 0
THEN
EXIT ;
END IF ;
END LOOP ;
END ;
InsertMessageLog('Finished.') ;
EXCEPTION
WHEN OTHERS
THEN
v_ErrorText := 'Unexpected Error in Procedure '
|| v_ProcName
|| '. '
|| SQLERRM(SQLCODE)
;
RAISE_APPLICATION_ERROR (-20000, v_ErrorText) ;
END SyncWOAncestorTable ;