Maximo - WFAssignment Table Cleanup

From ExxtremeWiki
Jump to: navigation, search

Maximo - WFAssignment Table Cleanup [edit] Cleaning up the transactional records from the WFAssignment table

NOTE: This needs to be tested in your environment before using!!


/*

    • Records in the wfassignment table that are part of the Workflow Structure
    • DO NOT DELETE THEM as it will BREAK your Workflow(s)!!
  • /
SELECT * FROM wfassignment WHERE assignstatus = 'DEFAULT' AND wfid = 0
;

/*

    • All the following records are "transactional" in nature...
  • /
SELECT * FROM wfassignment WHERE assignstatus != 'DEFAULT' OR wfid != 0
     ORDER BY wfid
;

/*

    • Which WFID's are actually active?
  • /
SELECT * FROM wfcallstack  WHERE active = 1 ORDER BY wfid
;

/*

    • Select Assignments that are not active anymore...
  • /
SELECT * FROM wfassignment
     WHERE (assignstatus != 'DEFAULT' OR wfid != 0)
     AND wfid IN (SELECT wfid FROM wfcallstack  WHERE active = 0)
     ORDER BY wfid
;

/*

    • Clean it out; all records inactive and duedate entry > 90 days old
  • /
DELETE FROM wfassignment
     WHERE (assignstatus != 'DEFAULT' OR wfid != 0)
     AND wfid IN (SELECT wfid FROM wfcallstack  WHERE active = 0)
     AND duedate < sysdate - 90
;

Categories: Workflow, Maximo 6.x, SQL