Chart of Accounts

(0) Share

Report

Posted on by Community Member

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

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Suggested answer

Community Member on at

Like (0)

Report

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,

Justin

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Justin,

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Rohit0Sharma 345 on at

Like (0)

Report

Justin:

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?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Unfortunately no the code is long. I can give you the approach I used at least. Basically I did the following:

Use a job to examine the tables and fields of every table in the database, looking to see if it uses an Extended Data Type that ultimately derives from LedgerAccount. Yes, this is a daunting proposition, but using the SysTreeNode class you can iterate through the data dictionary programmatically and get everything you need to target in your update. At the time, I used the application hierarchy tree to give me a list of all the EDTs that extended from LedgerAccount and put those into a set so I could test the fields. Record the tables and fields.

I'm assuming you have some sort of mapping document that will give you OldLedgerAccount --> NewLedgerAccount. The merge will be done in 2 stages for these

The first one is easy, loop through all of the ledger accounts that don't have multiple accounts being merged into one. For these, you can simply use the xRecord.renamePrimaryKey() method as noted earlier in this thread. It's helpful I think in this step to also include one of the values for which an account is being merged, since having the new account already existing will help simplify the code for the next step.

Once you have your list of accounts that are being merged, as well as the tables and fields that need to be updated you'll need to loop through these account by account, and manually call an update_recordset to update the records from the old ledger account to the new.

Caution though, some of the tables will actually be transaction tables, and in the case that you had multiple accounts hit that are now being consolidated you're going to run into a duplicate key exception. For those tables, you'll need to handle them in a slightly different method. You'll need to select the existing record and add in any numeric fields, and then update the existing record and delete the old.

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.

-Justin

Was this reply helpful? Yes No
Rohit0Sharma 345 on at

Like (0)

Report

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.

Regards

Was this reply helpful? Yes No

Community site session details

Chart of Accounts

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates

Community site session details

Chart of Accounts

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates