This blog is dedicated to all my technical learnings and findings. As they say use all the brains you have and borrow all the brains you can, so this is my share of lending my learnings to all you guys out there. I would like to acknowledge here that some parts of these posts would be reproduced as a part of my web-browsing mainly because having it all in one place is far more convenient.
Encountered this issue once the system recovered from the RecId max out in the workflow tables. There were certain transactions that were submitted and updated as In-review however the workflow was not triggered for them. The workflow bar was missing and on trying to Re-Submit the workflows the error was received.
Got the message in the workflow. The messages can be seen in the EventViewer and can also be seen on screen if the workflow is triggered using the tutorial form.
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.