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.
Vaidy – Was the last AA class loaded at SQL level into the table directly? That is one possibility where the user might have missed updating the record in the AAG00102 table. Which version of GP and SP are you on?
Hi Siva, No user have got access to SQL and I, as an ERP Admin, never insert AA records by direct SQL method (you know how risky it is… :-)).We are in GP2010 R2, planning to move on to the just released hotfix but not yet.Vaidy
Hi Vaidy! This is a helpful post. How do you find the table IDs? is there one table that defines this?
Hi Gina, Table IDs are basically nothing but the last 2 or 3 or 4 characters of an AA master table. So for AA Dimension master table, which is AAG00400, the table ID is “400”. For AA Dimension Code master, AAG00401, the table ID is “401” and so on. If you know the exact physical table name for an AA table, this would be very easy to track inside Table ID table. I hope I answered your query.
Thanks, Vaidy! I found a document last night that explained this, and your post was very helpful. It helped me resolve the issue. The issues I am having now are with adding Alias IDs.in one company and retaining Alias entries in the distribution windows. Any insight on those issues would be apprecaiated!
Vaidy, in looking at the Alias table AAG00800, the first alias created has AAAliasID is 7 and increments from there up through 195. Then the next Alias IDs are starting the count at 1, 2, 3, 4, etc. so I think the next row ID is preventing from adding a new Alias ID, thus the error above when trying to save a new Alias ID. AAG00800 is not in AAG00102. Do you know where the next record ID is set for Alias ID? Any help would be appreciated!
Hi Gina, I know this is over a year ago, but I had the same issue you were having and could find no guidance in searching google. I am answering this in hopes it helps someone else with this mystery. The answer is that AA Aliases (aag00800) shares the same index as AA Classes (aag00201). So when you create an alias, GP pulls the next Alias ID from the entry in aag00102 where aaTableID = 201, not 800 as you would expect. I am using GP 2010. Not sure if this is the same in other versions. I hope this helps someone out.
Thanks Medardo, for sharing this information. Much appreciated.
VAIDY – Great detective work! This post saved the day for me.
Hello Vaidy, I am facing same error. i have tried to integrate the record using econnect in Transaction Dimension Code maintenance. Its done successfully. But when i am trying to crate alias for that record it give the same error.