Many times, when after data migration, a user wants to create a table record, the well-known record already exists error message pops up. This issue is because of the mechanism through which current recIds and next are assigned by AOS in coordination with table records in the database i.e. SystemSequences. The following method will resolve the issue as per above:
An error such as Cannot create a record in Earning external reporting (PayrollEarningExternalReporting) was thrown when a new earning code was tried to be created in Payroll module in AX.
First the following query will retrieve the tableid, which is necessary to find next recid the framework will try to assign:
SELECT * FROM SQLDICTIONARY
WHERE NAME = 'PayrollEarningExternalReporting'
AND FIELDID = 0
The result will have tableid, which we'll use in the following query to find out what is next recid to be assigned:
SELECT * FROM SYSTEMSEQUENCES
WHERE TABID = 101149
We'll also use the following query to find the maximum or highest number of recid already assigned in the records:
SELECT MAX(RECID) FROM PAYROLLEARNINGEXTERNALREPORTING
Finally, we'll use following query, to update the nextrecid incrementing one number higher, so that next number should not exists in the existing records:
UPDATE SYSTEMSEQUENCES
SET NEXTVAL = (SELECT MAX(RECID) FROM PAYROLLEARNINGEXTERNALREPORTING) +1
WHERE TABID = 101149
The above changes have been successfully performed, then the next step is to restart AOS service, in order for cache to refresh and contain next correct recid number as per our changes. Sometimes if error still exists, you need to synchronize the related table 'PayrollEarningExternalReporting' and the error will be fixed automatically, as the sync will exactly reflect current changes.