Thursday, 29 September 2011

Basic structure of ledger in Ax2012

If you've worked in Ax2009 or prior, you'll see some major changes to the way the ledger and chart of accounts are defined and structured in 2012, both from a functional point of view as well as behind the scenes. This post aims to cover off some of the basic details of how it's now implemented. The Microsoft course material covers off the functional areas well so it's worth checking that out.


Standard disclaimer is that this is all fairly new to me as well so if you spot any errors or omissions feel free to comment or email me!

Chart of accounts and structures (global)


In Ax2009, the chart of accounts (LedgerTable) was defined per company. In 2012, the charts of accounts are defined across the entire organization, then selected per-company via the company 'Ledger'.


Chart of accounts setup. Defined across entire organization

The above form is accessible from General Ledger / Setup / Chart of accounts / Chart of accounts.
  • The "Charts of accounts" (left section of above form) are defined in LedgerChartOfAccounts
  • The "Main accounts", which are similar in meaning to the Ledger accounts (LedgerTable) in Ax2009, are stored in table MainAccount
  • The "Account structures" (LedgerChartOfAccountsStructure) link a chart of accounts to a dimension structure (DimensionHierarchy), which defines the breakdown of how dimensions are entered (more on this later).


Ledger setup (per-company selection of chart of accounts)



Ledger setup - Defines per-company settings

The Ledger form above (General Ledger / Setup / Ledger) defines the per-company information. This is where you nominate the fiscal calendar and chart of accounts relevant to the company. The table that stores this information is Ledger, and is linked to a company via the field PrimaryForLegalEntity, which points to CompanyInfo.RecID.

Note that even though the Ledger table defines per-company information, it's still a 'shared' table (ie SaveDataPerCompany is "No"). It maintains a unique index on PrimaryForLegalEntity to effectively make it non-shared (ie per-company).

The following is a basic overview of the relationships between the main tables:



Basic structure of ledger and chart of accounts tables

The following code-snippet shows the relationship by displaying the name of the current company's selected chart of accounts:

static void ShowCurrentChartOfAccounts(Args _args)
{
    CompanyInfo             companyInfo;
    Ledger                  ledger;
    LedgerChartOfAccounts   ledgerCOA;
    ;
    
    companyInfo = companyInfo::find();
    ledger      = ledger::findByLegalEntity(companyInfo.RecId);
    ledgerCOA   = LedgerChartOfAccounts::find(ledger.ChartOfAccounts);
    
    info(ledgerCOA.Name);    
}

Code to retrieve the current chart of accounts

Dimension structure in a bit more detail

A chart of accounts can have one or more dimension structures attached to it (via LedherChartOfAccountsStructure as shown above).

The dimension structure includes several segments, which for example could include:
  • Main account
  • Department
  • Cost centre
  • Purpose
These are setup via the menu item General Ledger / Setup / Chart of accounts / Configure account structures, and can be attached via the Chart of accounts form.


Configure account structures form

Item (one) in the above diagram shows all of the available account structures, stored in table DimensionHierarchy.
NB this table is used for several purposes, like dimension structures, dimension sets (for reporting and financial statements), advanced rule structures etc. These are delineated by the 'StructureType' field, which in this instance is limited to 'Account structure'.
Item (two) moving across the screen shows the 'segments' of the structure. This starts with the 'main account' and optional filter, and can include one or more additional dimension attributes, such as department, purpose, customer, etc. These are stored in table DimensionHierarchyLevel.

Item (three) moving down shows the different 'constraint nodes' of the structure (in this picture we only have one). These are used to define validation rules specific to an account range. So, in the above screen the filter for main account is 0101..0106, then the filter for department is >5, and so on. This means when a posting is made for account 0105, it will validate that the department value is greater than 5.

If we had another level where the main account was ranged 0200..0210, and a posting was made for account 0205, then any additional segment ranges would be validated against that level, and so on.

The constraint nodes are stored in table DimensionConstraintNode, which is linked to the structure via table DimensionConstraintTree.

The following shows the structure in code-form, by selecting a specific account structure and dumping the criteria values:

static void ShowAccountStructureBreakdown(Args _args)
{
    DimensionHierarchy              dimHierarchy;    
    DimensionHierarchyLevel         dimHierarchyLevel;
    DimensionAttribute              dimAttribute;
    DimensionConstraintTree         conTree;   
    DimensionConstraintNode         conNode;
    DimensionConstraintNodeCriteria conNodeCriteria;
    ;   
    
    dimHierarchy    = DimensionHierarchy::find(5637146666);
    conTree         = DimensionConstraintTree::findByDimensionHierarchy(dimHierarchy.RecId);
    
    setPrefix('"' + dimHierarchy.Name + '"');
    
    while select dimHierarchyLevel
        order by Level
        where dimHierarchyLevel.DimensionHierarchy  == dimHierarchy.RecId
    {        
        
        dimAttribute = DimensionAttribute::find(dimHierarchyLevel.DimensionAttribute);
        info(strFmt("Attribute at level %1 is %2",dimHierarchyLevel.Level,dimAttribute.Name));
        
        while select conNode 
            order by Ordinal
            where   conNode.DimensionConstraintTree  == conTree.RecId
            &&      conNode.DimensionHierarchyLevel  == dimHierarchyLevel.RecId
        {            
            
            while select conNodeCriteria
                where   conNodeCriteria.DimensionConstraintNode == conNode.RecId
            {            
                if(conNodeCriteria.RangeFrom || conNodeCriteria.RangeTo)
                {
                    info(strFmt(" - Limited from %1 to %2",                    
                        conNodeCriteria.RangeFrom,
                        conNodeCriteria.RangeTo));   
                }
                else
                    info(" - [No restriction]");
            }            
        }
    }
}

View account structure table relationships in code

You probably wouldn't write code like this as there are APIs to achieve the same result, and in reality I wouldn't expect this logic to be changed during an implementation. The sample is just to demonstrate the tables and main relationships.
In diagram form, the basic relationship is:

Dimension constraint table relationships

The account structures can become quite complex with parent/child relationships between the levels and criteria, but for a simple example look at the following setup.


Here we have an account structure with 3 levels: Main account, Department, and Customer. Remember that a 'segment' is stored in the table DimensionHierarchyLevel.


The first segment (main account) is limited to the ranges 0101 through 0106, and 110101 through 399999. The second segment (department) is limited to values greater than 5. When running the above job on this we get the following output:

Info  "Account structure"  Attribute at level 1 is MainAccount
Info  "Account structure"   - Limited from 0101 to 0106
Info  "Account structure"   - Limited from 110101 to 399999
Info  "Account structure"  Attribute at level 2 is Department
Info  "Account structure"   - Limited from 5 to 
Info  "Account structure"  Attribute at level 3 is Customer
Info  "Account structure"   - [No restriction]


Output from dump of dimension levels and criteria. 

This shows our 3 levels (MainAccount, Department, Customer), and for each the set of constraint nodes (only one per level in this example), and criteria.

You may have noticed that the range for Department (>5) has a RangeFrom of 5 and a RangeTo of [undefined]. In Ax terms, this would normally be expressed as "5..", resulting in an inclusive range (ie 5 and greater) as opposed to 'Greater than 5'. This is handled by the additional flags IsFromOpen and IsToOpen on DimensionConstraintNodeCriteria, which indicate whether the range is inclusive/exclusive at either end.

This only scratches the surface of the structural changes in Ax2012 finance. I'm aiming to get more posts up soon.

1 comment:

  1. Very informative post. Thanks. Have you figured out what the Relationship button does? Seems to have something to do with the relationships used in the Global address book, but how is not clear at all. Any ideas would be helpful.

    ReplyDelete