The reason for this error is when the RECID of the tables involved and systemSequences is out of sync. Which means the RecId that the system tries to generate for a new record is something which is duplicate and has been generated in the past.
The reason why this mismatch occurs could be anything and is not known to me; however what is important at this point, is to get out of this situation. With the little R&D that i did, i realized the following facts.
- The RecIDs are not generated real time from the systemSequences table. In reality each AOS caches a certain amount of RecID and tries to use them.
- Due to the above stated fact the max RecId that is visible directly from SQL database, is different from the one that actually exists in the AOS. Hence it is important that when we are correcting the NextVal in the systemSequences table, the value is fetched using the AOS table and not from the database directly (using SQL scripts).
- Check of the Maximum value of the RECID column for the table resulting in the error, in this case it is WorkflowTrackingTable.
- Make sure you check for the value of RECID from AOS and not using the SQL table as there might be records that are not committed to the database.
- Sort the table by RECID in descending order and copy the maximum RecID as reported by AOS.
- Update the systemSequences table by adding 250 to this value to be on the safer side and to avoid cached values.
- Compile the table giving the error on each of the AOS so that the cache's is cleared.