Can anyone elaborate on their experience in sharing the ledgertable (chart of accounts) in AX?
Currently my company is upgrading from AX 3 to AX 2009 and would like to share multiple COA's depending on the type of operating company (over 250 + companies).
Any issues or suggestions would be great.
Thanks in advance
My company has 3 distinct entities that utilize a shared COA, but the actual transactions are kept company-specific. This has worked very well for us since initially upgrading from v3 to v4 (which is when we brought all 3 companies together in the same Dynamics instance). It also makes our Finance department's job a bit easier since they know that ledger account '12345' means the same thing in every company.
The one issue that I've noticed since our upgrade to AX 2009 has to deal with the ability to do cross-company queries, and how the user interface will sometimes handle that. Where this has caused a problem is in any type of ledger journal that either has an account or account offset set to a ledger account and you use the "Go to main table" function. Due to the possibility of cross company accounting in 2009, the lookup of the account will automatically try to look up the account definition in the company the journal is for. This is not the same dataareaid of the virtual company account, and so the function doesn't find the LedgerTable record.
For example, say you have the following situation (which is true for us): A US-based company and a Canadian-based company, 'usa' and 'can' respectively, that use a virtual company dataareaid of 'abc'. If you have a journal in the 'usa' company and try to use "Go to main table" on the ledger account, it will attempt to find ledger account "12345" in the company "usa" instead of the shared company "abc".
This is more of a minor inconvenience, and you can do a simple modification to override the default behavior and do a proper lookup, which is what we've done. There are a couple of other issues where improper lookups are being attempted, even if you have the intercompany accounting configuration key turned off, but again, you can do some mods to make sure that things work properly. Overall, the benefits to our company outweigh the minor (in my opinion) inconveniences.
Hope this helps,
Thanks for the info. Did you have any issues with renaming the account numbers (I assume you had to)? What about all of the attributes in the ledger table (dimension validation etc)being shared.
We did have to do some ledger consolidation from one company into another. By and large, it's very straightforward to do programmatically. Just use the LedgerTable.setPrimaryKey() function after updating the ledger account field and it will update all the references to that account number.
The difficulty is if you have more than one account being consolidated into the same shared account that can give you problems. Essentially I had to write my own rename function that touched on every table that contained a ledger account field. Since you can't rename an account into one that already exists, those ones are going to give you problems. It's been a couple years since I did it, and I no longer have the code kicking around that I used, so I can't really help you out there.
Once I had all individual company accounts renamed into what the shared versions would look like, I just used SQL to update the dataarea id of one of the companies to the shared dataareaid, set the LegerTable in the table collection between companies and assigned it to the virtual company. Synchronize the database and you've got yourself a consolidated CoA.
In addition to what Justin mentioned about the lookup in ledgerjournals, this is actually fixed when you install at least Rollup 3 on AX 2009 with SP1.
We are dealing with the exact situation you mention - "we have multiple accounts that need to be renamed to the same shared account".
Have you had a reason to locate the code?
Are there any "newer approaches" to handle this?
Unfortunately no the code is long. I can give you the approach I used at least. Basically I did the following:
That should do it, though with any operation as important as this always take a backup of your database prior to running the operation. Always leave a way back in case something unforeseen happens, and test out the merge in a non-production environment first. Be able to have your finance department run reports before and after to verify the results. Hopefully this helps somewhat, but I'm sorry I can't provide the code I used since it no longer exists.
Thanks Justin for the detailed response.
We are going to trying this approach - it uses the AX Merge() method.
1) call the Merge() method for each account that needs to be renamed.
2) To ensure same shared account are used in future, we will make LedgerTable part of a COA virtual share.