Monday, August 15, 2016

Loading account nodes into account structures

Probably I am not the only one who ever had to map main accounts to account structures in AX 2012R3, yet I couldn't find any useful description on how to automate this. In this post I will summarize you my approach as it saved me an enormous amount of time and I could reduce manual work to the absolute minimum.

The original scenario was the following: I had10 different account structures and 5-600 main accounts. Each main account belongs to one of these account strucutres and all 10 account strucutres are active and part of the ledger setup of the legal entity.

However to simplify the example here I will stick to 3 account structures:
  • Main Account without any financial dimensions
  • Main Account + Cost Center
  • Main Account + Cost Center + Department
Further presumption is that - as you can see above - all account structures begin with Main Account.

The process is the following:
  1. Create the Account structures
  2. Create a mapping table between main accounts and account structures
  3. Load this mapping table to AX
  4. Run a job that creates the nodes for the account structures
  5. Cleanup
1. Create the Account structures
I created the account strucutres manually, but I wanted to avoid using ranges in the account structure nodes because in that case moving a main account from one structure to another requires changes in the node criteria and at the end the setup will be a mess where nobody can find out easily what is where.
As the customer requires clear transparency, I wanted a 1:1 relationship between main accounts and structure nodes.

2. Create a mapping table between main accounts and account structures
As I'm not a developer I prefer to load mapping tables into AX. In AX 2012 this is done either with the DMF, the Excel add-in or via DAT-DEF. Since in this environment I did not have access for the first two, I needed to create the mapping in a DAT-DEF file and load it into AX.

Once the structures were created, I created this mapping between main account number and account structure ID in Excel:


On another worksheet in the same Excel I used this mapping list to create data for a comma separated DAT-DEF file:


I used the following formula:
="""RECORD"",""ReasonTable"",0,0,0,"""&Mapping!A2&""","""&Mapping!C2&""",0,0,0,0,0,0,0,0,1"""

As you can see, I used the standard AX table ReasonTable. I prefer this as it is on of those simple tables where you can save an ID and a Name (enough for a mapping) and in early stages of a project this table is not yet filled with values.
Even if it has values in your project you can still use this table as it is highly improbable that you have records in it but the record is not activated in any of the modules. The many 0 values at the end of this record show that these "temporary" records are not active in any of the modules.

Now you need to create a definition group in System Administration:


Export the table using the "Export to" button in a comma separated file and replace the content with the lines generated in your Excel file.

Make sure that the DEF file
  • has the table fields (the order should by default be the same everywhere, unless you've modified ReasonTable)
  • in the marked line you don't have zero (0) (have at least a number of 1 or the number of records you have to import - it doesn't matter, AX will import all the lines if this is not zero)

 

 

Then you have to fill the DAT file with the generated lines in your Excel sheet:


3. Load this mapping table to AX
Now you have a DAT-DEF file that can be imported back into the system:


Along the way you may be asked about the existing lines of ReasonTable. You probably don't want to delete them, so leave it and click OK.


You may also be informed by the system about the benefits of updating existing entries, but we don't want that so in order to continue, click YES.



The import fills your ReasonTable with the expected result:


This will be the basis for the job that will insert these main accounts as nodes of the given account strucutre.

4. Run a job that creates the nodes for the account structures

The job is the following:
---------------------

// creates criteria nodes in account structures using ReasonTable records as input

static void AsterAx_AccountStructureFiller(Args _args)

{

ReasonTable reasonTable;

 

int ordinalForLevel1;

 

RecId hierRecId, treeRecId, constNodeRecId;

DimensionHierarchy dimHier;

DimensionHierarchyLevel dimHierLevel;

DimensionConstraintNode dimConstNode, existingDimConstNode;

DimensionConstraintNodeCriteria dimConstNodeCrit;

 

boolean update, firstline;

;

 

// for testing reasons update is FALSE change this to TRUE to really load the account nodes into the structures

// to enhance the tests, feel free to filter the while select in the beginning of the code or to reactivate the info messages later in the code

update = false;

ttsBegin;

while select reasonTable order by Description ASC, Reason ASC

where reasonTable.Asset == false &&

reasonTable.Bank == false &&

reasonTable.Cust == false &&

reasonTable.Vend == false

// && (reasonTable.Reason == '10101' || reasonTable.Reason == '10119')

// && reasonTable.Description like 'c*'

{

select firstOnly dimHier where dimHier.StructureType == DimensionHierarchyType::AccountStructure &&

 

dimHier.Name == reasonTable.Description &&

 

dimHier.DeletedVersion == false;

if (!dimHier.RecId)

{

info(strFmt("Account structure %1 not found",reasonTable.Description));

}

else

{

if (dimHier.RecId != hierRecId) // current line belongs to next Account Structure

{

firstLine = true;

ordinalForLevel1 = 1;

constNodeRecId = 0;

hierRecId = dimHier.RecId;

treeRecId = DimensionConstraintTree::findByDimensionHierarchy(dimHier.RecId).RecId;

                 //info(strFmt("Starting %1 hierRecId %2, treeRecId %3",dimHier.Name,hierRecId,treeRecId));

}

 

while select dimHierLevel order by Level ASC where dimHierLevel.DimensionHierarchy == dimHier.RecId

{

// the IsOptional flag must be activated if blanks are allowed in this node

if (dimHierLevel.Level != 1) // no constraints are covered by this job besides for Main account on level 1

{

dimConstNode.Ordinal = 1;

}

else if (firstline) // Main account constraint nodes' starting ordinal depends on existing lines

{

select firstOnly existingDimConstNode order by Ordinal DESC where existingDimConstNode.ParentConstraintNode == constNodeRecId &&

existingDimConstNode.DimensionConstraintTree == treeRecId &&

existingDimConstNode.DimensionHierarchyLevel == dimHierLevel.RecId;

ordinalForLevel1 = existingDimConstNode.Ordinal +1;

dimConstNode.Ordinal = ordinalForLevel1;

firstline = false;

constNodeRecId = 0;

}

else // if no existing lines exist, then simply use the next ordinal

{

ordinalForLevel1++;

dimConstNode.Ordinal = ordinalForLevel1;

 

constNodeRecId = 0;

}

dimConstNode.ParentConstraintNode = constNodeRecId;

dimConstNode.DimensionConstraintTree = treeRecId;

dimConstNode.DimensionHierarchyLevel = dimHierLevel.RecId;

 

if (update) dimConstNode.insert();

//info(strFmt("Inserting node RecId %1 as nr%2 for account %3 referencing to %4 on level %5",dimConstNode.RecId,dimConstNode.Ordinal,reasonTable.Reason,dimConstNode.ParentConstraintNode,dimHierLevel.Level));

 

constNodeRecId = dimConstNode.RecId;

 

if (dimHierLevel.Level == 1)

{

dimConstNodeCrit.Ordinal = 1;

dimConstNodeCrit.RangeFrom = reasonTable.Reason;

dimConstNodeCrit.RangeTo = reasonTable.Reason;

dimConstNodeCrit.WildCardString = "";

dimConstNodeCrit.DimensionConstraintNode = constNodeRecId;

 

if (update) dimConstNodeCrit.insert();

//info(strFmt("Inserting NodeCrit RecId %1 with From %2 and wildcard '%3' for account %4 referencing to %5",dimConstNodeCrit.RecId,dimConstNodeCrit.RangeFrom,dimConstNodeCrit.WildCardString,reasonTable.Reason,dimConstNodeCrit.DimensionConstraintNode));

}

else

{

dimConstNodeCrit.Ordinal = 1;

dimConstNodeCrit.RangeFrom = "";

dimConstNodeCrit.RangeTo = "";

dimConstNodeCrit.WildCardString = "%";

dimConstNodeCrit.DimensionConstraintNode = constNodeRecId;

 

if (update) dimConstNodeCrit.insert();

//info(strFmt("Inserting NodeCrit RecId %1 with From %2 and wildcard '%3' for account %4 referencing to %5",dimConstNodeCrit.RecId,dimConstNodeCrit.RangeFrom,dimConstNodeCrit.WildCardString,reasonTable.Reason,dimConstNodeCrit.DimensionConstraintNode));

}

}

}

}
ttsCommit;
}

---------------------

This job does the following:
  • checks if the description in the ReasonTable exists as Account structure
  • creates the node for the account structure and adds the main account as criteria for the first structure column
  • creates as many further columns with "any value" criteria as the account structure expects
  • you will need to add a line if you want to allow empty dimensions too
Read the comments inside the code to understand how to use it easily for tests before running it "live".

The result is a nicely filled Account structure:



5. Cleanup

As you can see on the above screenshot, the mapping only creates the new lines, but doesn't delete the "All - All - All" default entries. You MUST do this manually now.
After this final touch, your account structures can be activated and enabled in Ledger.

Finally you may want to delete your temporary entries in ReasonTable. For that, just use the standard please...

The solution worked fine for me, I hope it helps you too!

Friday, July 1, 2016

Ledger settlements

You can find many descriptions of the Ledger settlement functionality in AX, just google it. What this post will be about is more of "what ledger settlements could be".

First of all let me clarify why I believe that ledger settlements make sense at all. Many of my consulting colleagues still raise they eyebrows when this functionality is mentioned.

Ledger settlement management is useful if you have transitory accounts. These are a kind of bridging accounts where at one point in time you post an amount on one side (debit or credit) and at another point in time it is counter-posted (credit or debit) and the amount moves to its "final" account.

A simple example would be a bridging account for a bank account. Everybody knows that functionality.
Another example would be the accounts used by packing slip and delivery note. Postings on these accounts are cancelled by the purchase and sales invoice.
Sometimes you can use the auxiliary books (subledger) for controlling these balances, but in some cases you don't have the information there or finance department prefers to have information for reconciliation directly in the ledger.

This is when Ledger settlements becomes handy. However you will need to ensure that the postings on these accounts store enough information to do the reconciliation (settlement). For automated postings,  I recommend using the %1 - %6 parameters in transaction texts. For manual postings you will need users' self-discipline.

And then you (the end user) are ready to follow the instructions found on many places of the web. Like this one.

What you would expect from this functionality but you will not find:
  • You do not have the possibility to find out the total unsettled balance of an account
  • You do not have the possibility to disable cross-account settlements
  • You do not have the possibility to do partial settlements
  • You do not have the possibility to automate ledger settlements for automatic postings like the above mentioned sales/purchase delivery note transactions
  • You do not have the possibility to select ledger accounts available for ledger settlement like you have the possiblity to select them for balance control
  • As in the below screenshot, lines included in the lower part will not disappear or get a mark in the upper part (and in case you have many movements on the account, this will be quite disturbing)

If you accept these limitations or overcome them with the help of modifications (developments) you will be quite well off with the functionality but surprises could still await you.

Good luck!

Tuesday, May 24, 2016

Financial Dimensions and Account structures in AX2012

I've worked now quite some time with the account structure logic of AX2012, in this first post I will summarize you the most important findings I made.

The basis for all further setup is a well-designed chart of accounts. I will not go into the details here, but you can find plenty of other blogs giving you hints for it.

Before starting the create account structures, you should design your financial dimensions. Now this is so much easier in AX2012 then in any previous version! There are many useful functions that can be combined with the financial dimensions.
Please read this blog for further reference: 5 Cool New Ways to Use Financial Dimensions in Microsoft Dynamics AX 2012

If you are not yet familiar with the financial dimension logic in Dynamics AX2012, you should read this post: Implementing Dynamics AX 2012 General Ledger Dimensions
It the first part of a series of posts describing the pros and cons of additional dimensions in the general ledger.
In the second and third parts (here and here) Rachel discusses guidelines to decide whether you analysis needs really should be fulfilled by financial dimensions or if it can be solved through other means.
Finally in the last post (here) she describes the Account structures functionality of AX2012.

While creating your account structures you have to consider the following
  • Account structures are cross-company data (they are visible for all legal entities / company accounts)
  • Account structure depth is limited to a maximum of 10
The first point (cross-company) can be overcome by creating several identical structures with legal-entity prefixes and select them accordingly in the ledger parameters. This will enable you to have different financial dimension requirements for the same ledger account in the different companies. The reason why you would want this is because even multinational companies in a single AX installation have different company sizes in the different locations and therefor the financial dimension requirements may differ between the "big" and the "small" companies.

Although 10 dimensions should usually be more than enough, the limitation of 10 (second point) can be overcome using Advanced rules. Before you do this, it is important to understand the difference and the relation between Account structures and Advanced rules and to remember that while Account structures are cross-company, Advanced rules are company-specific settings.
If found a very short and clear post, so I wouldn't bother writing one myself. Please read it here: Advanced rule structures explained
And the description how Advanced rules can be used to extend the number of dimensions is also well described, if you are interested, you can read it in the post here

Following the above guides you will quickly have useful financial dimensions and account structures in AX2012.