Maximo Open Forum

 View Only

 Repairing WOANCESTOR table in Maximo 7.6 OG environment

  • Everything Maximo
  • HSE/Oil and Gas
Mahadevan Ramakrishnan's profile image
Maximo Certified Mahadevan Ramakrishnan posted 09-03-2022 06:35
Hi All,

We have observed that there is a sync issue with the WOANCESTOR table as few task work orders are not getting displayed in the WOANCESTOR table. This issue also has been observed on the 7.6 Oil and Gas vanilla environment.

It might take time to get response from IBM on the raised PMR,but I wanted to understand what can be the possible fix for the orphaned task work orders? It would be helpful if someone can help with the sql script or method to fix the broken records.

There is an article on the internet to fix this scenario but it is asking to truncate the entire WOANCESTOR record before applying the fix and it doesnt seem to be a doable thing on the production environment.

Repairing IBM Maximo's PERSONANCESTOR and WOANCESTOR Tables - InterPro Solutions
InterPro Solutions remove preview
Repairing IBM Maximo's PERSONANCESTOR and WOANCESTOR Tables - InterPro Solutions
Maximo maintains the relationship between a person and their supervisor in the PERSONANCESTOR table and likewise maintains the relationship between a work order and its parent work order in the WOANCESTOR table. From time to time one or both of these tables can get out of sync with the tables they are supposed to represent.
View this on InterPro Solutions >
Julio Hernandez's profile image
Julio Hernandez
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 ;