This one’s another nightmare. I have no idea about how this happened, but now I know how to rectify this without affecting any of the system’s normal behavior.
When a user tried creating an Analytical Accounting Class, the following error message was thrown at and the record did not get saved:
Analytical Accounting Class Master table is AAG00201 in SQL Server. It is also aaAccountClassMstr in AA dictionary. When I queried this table, it did not give me any indication that something could be wrong there.
Upon taking a SQL Profiler (I trust SQL Profiling to my life), I found that the flow stops at a table DYNAMICS..AAG00102, which is (table AAG00102, database DYNAMICS) and technical name of this table is aaSyTableRow. When I queried this table, it was quite interesting. Below is a snapshot of the table records:
Logically, the table, DYNAMICS..AAG00102, stores the ID information for each type of record that is stored in Master tables. For instance, in our case, Account Class Master table is AAG00201. See the highlighted record in the above snapshot.
aaTableID: 201 (denotes Account Class Master table)
CMPANYID: 1 (denotes my Production Company ID)
aaRowID: 29 (denotes Last Class ID that was generated when I created Account Classes)
ISSUE: My last Account Class ID at the moment is 30. This is NOT the same as it is in my AA System Table Row (DYNAMICS..AAG00102) table. When the user tried creating the new AA Account Class, it took the aaRowID from AAG00102 table, which is 29, and that clashed with my Account Class Master table, since I already have one record with Class ID 29.
FIX: I realized that I must reset this aaRowID to the last generated Class ID, which I can get from Account Class Master table.
I took a backup of the table DYNAMICS..AAG00102 (SELECT * INTO …) as a precautionary method. Then I executed the below query to reset it:
UPDATE DYNAMICS..AAG00102 –AA System Table Row Table
SET aaRowID = 30 –Last generated class ID in my case
WHERE aaTableID = 201 –Denotes Account Class Master table
AND CMPANYID = 1 –Denote my production company ID
Above query reset it to correct *Next Class ID*. User is now able to create new class IDs.
Issue is resolved, but how aaRowID on DYNAMICS..AAG00102 was not incremented when last Account Class ID got created, remains a mystery.
I am unable to recreate that scenario so far.