Data model of chart of accounts

(0) Share

Report

Posted on by Community Member

Hi,

I have a chart of accounts structure under Configure COA. Now I want to get the details from the Chart of accounts structure, i.e., the link between say main account to cost center or main account to business unit etc.

Any idea as to from where do I get this using code. I have attached the image for your reference.

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (1)

Martin Dráb 236,505 Most Valuable Professional on at

Like (0)

Report

RE: General Ledger

It's all in AX database, in tables such as DimensionHierarchy and DimensionContraintNode. Nevertheless it's not clear to me what output you want to get. If you find a particular hierarchy for your main account, you still can have many lines and each of them will have some criteria for business unit (for example).

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Data model of chart of accounts

Hi Martin

Say, I have Main account and Cost center in the Chart of Accounts.

Now, under the configure CoA option, I have added certain Main accounts and subsequently certain Cost centers for those accounts.(See image for reference)

Now, I want to get these values i.e.,

Account : Main123 is linked to CC1, CC2 etc.

Does it make it any clear?

Was this reply helpful? Yes No
Martin Dráb 236,505 Most Valuable Professional on at

Like (0)

Report

RE: Data model of chart of accounts

Your example looks simple only because it ignores many facts. You can have many lines. Each can have many values, ranges, wildcards and so on. Depending on your setup, the values may overlap. Are you happy with output like <all values>, <all values>, CC*, C1, C1, C1, C2, C3, A<..<D?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Data model of chart of accounts

That is fine. I just need the values like <all values> linked to CC*, CC1 .

Or <110093> linked to <A...D>

Was this reply helpful? Yes No
Verified answer

Martin Dráb 236,505 Most Valuable Professional on at

Like (0)

Report
RE: Data model of chart of accounts

The following SQL snippet demonstrates how the data model works in this area. It's from a training about financial dimension data model that I gave to some partners.

SELECT hierarchy.Name, attribute.Name, criteria.WildcardString, criteria.RangeFrom, criteria.RangeTo, node.Ordinal, node.ParentConstraintNode, node.RecId as NodeRecId FROM DimensionHierarchy AS hierarchy JOIN DimensionConstraintTree AS tree ON tree.DimensionHierarchy = hierarchy.RecId JOIN DimensionConstraintNode as node ON node.DimensionConstraintTree = tree.RecId JOIN DimensionHierarchyLevel AS lvl ON lvl.RecId = node.DimensionHierarchyLevel JOIN DimensionAttribute AS attribute ON attribute.RECID = lvl.DimensionAttribute JOIN DimensionConstraintNodeCriteria AS criteria ON criteria.DimensionConstraintNode = node.RecId WHERE hierarchy.IsSystemGenerated = 0 ORDER BY hierarchy.Name, lvl.Level_

Now you should know enough to be able to implement your requirements.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Data model of chart of accounts

Thanks a lot, Martin.

Was this reply helpful? Yes No
CU29041349-0 594 on at

Like (0)

Report

Data model of chart of accounts

Hi Martin,

I realize this is a very old post, but is there a way to use the query you laid out to get the individual dimension values for each range for F&O? For instance, if MainAccount were 100000..100200, is there a explode the ranges to get discreet valid values without querying MainAccount table? Similar question for financial dimensions with the DimensionAttribute and DimensionAttributeValue table.

Was this reply helpful? Yes No
Martin Dráb 236,505 Most Valuable Professional on at

Like (1)

Report

Data model of chart of accounts

It doesn't possible to me. For example, number 100009 belongs to the range (100000..100200) but you doesn't have to have such an account defined. You don't know if it exists unless you look into the table.

Was this reply helpful? Yes No