dynamics-coe: Dynamics AX Export/Import Framework for Vendor balance migration
Importing data into AX was, is and will ever be a tedious task, always under-estimated. For small configuration tables as payment terms etc. the Excel Add-In may be the tool of choice. Complex entities require tools leveraging AX business logic. Some time ago Microsoft released the so-called Dynamics AX Export/Import Framework (hereafter DMF) for customers and partners: https://informationsource.dynamics.com/...
The official user guide (http://technet.microsoft.com/en-us/library/jj225591.aspx) provides useful info, here are my highlights at a glance:
Let’s study the tool and its capabilities on a practically relevant example: open vendor balance import.
Task definition: open vendor (customer) balance
In any implementation of the core AX accounting modules one needs to import the open (unreconciled) accounts payable balance from the legacy system. AX do not store vendor balance specifically, it’s just a sum of all (open) vendor transactions. Subsequently, you have to import them one by one. The complex data model of the AP/AR modules makes it difficult to import data directly into the system tables.
In praxis, AR/AP open transactions are always imported via General ledger daily journals. GL journal posting routines take care of the data integrity and validation.
This approach has one drawback: a posted GL journal do not only leaves VendTrans/CustTrans records in the sub-ledgers, but also the General ledger transactions. Yet the GL initial balances are imported separately in their own GL journal (with the same tool - DMF). You should decide what to do with this by-product:
Historical posting date
Vendor/Creditor account number
Invoice No. 7548784
Amount in the transaction currency.
For partially settled invoices, use the open amount. It is hardly feasible to import the original invoice amount and settle a half of it.
Open amount in the accounting (home) currency. This value cannot be imported directly into AX but only by means of the exchange rate:
2193.18 / 1190.01 * 100 = 184.30 (GBP / EUR)
Failure to use the right exchange rate in a multi-currency environment inevitably leads to a balance distortion after the first month-end closing.
Empty for open payment transactions
Payment conditions may vary in time. It is better to provide AX with a pre-calculated due date instead of relying on the payment term code.
Depending on the local accounting policy, additional data may be needed:
At least for AR (given) pre-payments the summary GL account may differ.
The invoice posting date and the date the invoice was issued by the vendor may differ. In some countries, the original document date is the leading for tax calculation.
Legacy accounting programs normally rely on a single tax code. Map it to AX tax item groups.
In certain applications, the exact VAT amount needs to be posted. The tax amount in AP is always entered as stated on the invoice and may differ from the tax amount calculated in AX.
The balance account cannot be entered directly and have to be mapped to an AP posting profile.
Cash discounts calculated by AX may differ from the values in the legacy system.
It is a best practice to use a dedicated financial dimension
Dimension[x] = AccountNum
to keep the GL and AP modules reconciled. Explore the balance account structure to find out.
Sometimes it is useful to preserve the legacy op. number.
The GL offset account is not on the list: the AR/AP balances are usually posted against a single [technical] GL account in credit.
Prerequisites, planning data migration
DMF installation is pretty straight-forward and well described in http://technet.microsoft.com/EN-US/library/jj225595.aspx .
Consider the following:
Having said that, you should have got something similar to this Excel file:
Test UTF: ÄÖÜ ДЯФЖ
Create a network shared folder, grant write and read rights to the DMF AD account. Assign and check the folder in Data import export framework / Setup / …Parameters in AX. Now you are ready to proceed with the…
Step 1: Source --> Staging Table
Every entity in DMF has a query, a class and one or more staging tables. All classes, queries, staging tables share the prefix DMF. Not surprisingly, the naming of staging tables and fields follows the target table (here: LedgerJournalTrans).
Let the system populate the entity table: go to DMF / Setup / Target entities. The extension framework and with the DMFAttribute is used. Our entity is called Opening balance or DMFLedgerJournalEntity.
To read the source file, one should define a Source data format. Our test file includes some non-ASCII, non-Latin symbols. To save an Excel file in a CSV format with the UTF encoding you may need third-party products such as Op#n O##ic#. Furthermore, a CSV file is notoriously awkward to edit and you’ll be editing it a lot in your test run.
A neat alternative to a CSV file is a direct access to the Excel via ODBC. At first, download and install the Microsoft Access Database Engine 2010 Redistributable . Save the Excel file in the 93-2000 format for better compatibility. Go to Windows Administration tools and set up a system or user DSN, 64 bit:
In AX, create a new source data format of the ODBC type. Choose the DSN and test the connection with the Validate button:
At the Application tab page, let the system know the sequence of financial dimensions in the field DefaultDimension.
Now you are ready to start mapping the Excel columns to the staging table fields. Create a new Processing group, go to Entities, and choose your entity and data format. Contrary to a CSV file, an ODBC data source may have multiple tables. In an Excel file one sheet is equivalent to a table. You need a Query to retrieve the data from a particular sheet:
select * from `Sheet1$`Make sure the Excel file is used exclusively by the ODBC driver and let AX Generate source mapping. The system is opening the connection and aligning the data columns with the staging table fields by name. Provided all the key fields are present, field names are exact, you may immediately press Preview source file and enjoy the result:
What if you wish to extend the framework, save some additional data in the staging table and post-process it? You can insert a new field into the staging table or – theoretically – use any of the existing fields and link it to the source file column. Column data types and staging field data types should match, though.
Unfortunately, DMF is not able to derive the data type from the source file and - oddly enough – it deprives the user from choosing the data type manually. Imagine you would like to import an exact inbound VAT amount from the invoice into AX. There is no such a field in the LedgerJournalTrans table, as the taxes are calculated and saved in a joined record. Yet we may easily save the tax amount in one of the unused fields (RemainAmount) for post-processing. In this case we should either call the source file column exactly like that (RemainAmount) or enable type editing in DMF. This is easily achieved by changing the AllowEdit property of the DMFDefinitionGroupEntityXMLFields.FieldType field:
You may now edit the Entity attribute types freely.
In order to keep your Excel small and manageable, you may set default values for certain columns directly in AX. For example, the debit account type is always Vend[or] in our application, there is no need to keep it in the Excel. Press Modify source mapping, switch to the tabular view (Mapping details) create a new line for the Staging field AccountType, activate the Auto default check box and assign the Default value. Do the same for the offset ledger account: OffsetLedgerDimension. Press Preview source file and check the result.
Finally, all records may be processed, read from the Excel file and put into the staging table. The button Get staging data does the job; check out the small Run button in the Staging data execution dialog. Note the staging tables may only be viewed (see Execution history) but not edited in the DMF user interface. To edit data in the staging tables without changing the source file, right-click the staging table in the AOT and use the table browser.
Step 2: Staging Table --> Target
Post-processing of staging tables and mapping to the fields in the target table (here: LedgerJournalTrans) is governed by Target entities, button Modify target mapping. The mapping is generated automatically out of AX metadata. Most of the fields are mapped 1:1, some of them are post-processed inside a function written in X++ in AX. Similar artifacts in BizTalk are called ‘functoids’, hereafter I will follow this notation.
The mapping may be adjusted or re-generated in the Mapping details tabular view. Any manual changes into functoids may result in a DMF malfunction, making clean-up of the DMFTargetXMLToEntityMap table necessary.
The functoids are simple member functions of the DMF entity class. Any functoid may have none, one or many input parameters (staging table fields) and none, one or more output parameters (target table fields). To support this behavior, the input parameters are packed into a container, the output value is a container too, each represented by an own line in the mapping tabular view.
Compared to the Excel add-in, the power of DMF is unleashed by re-using AX business logic, its ‘modified’-methods. Indeed, most of parameters in the journal line get default values assigned in the vendor master table. In the AX' rich user interface the default payment terms, bank account, dimension are set to default values every time the user chooses a new vendor account. The posting profile etc. come from the AR module parameters. With the DMF, a consultant may set the Call modified field option in the line with the primary field, taking care of the significant, variable columns only. So the theory.
Unfortunately, the developers of DMF were not aware of the methods the business logic is usually written in. The basic \Classes\DMFEntityWriter\write method has a number of flaws:
Fields set in a functoid do not trigger the modified() methods by default. This triggering should be programmed explicitly, driven by the OverrideBusinessLogic parameter. This parameter is activated by the same Call modified field UI option. Set this option in the ‘output’ line only, the ‘input’ options are kept in sync automatically:
The entity class DMFLedgerBalanceEntityClass has its own handicaps. The functoid GenerateLedgerDimension evaluates and writes the vendor account to the journal line. It should trigger the whole cascade of modified methods, setting most of the default values. Yet the developers failed to call the right trigger method. It should have been accountModified() and not the accountNumModified(). The latter implements only a small fraction of the application logic. After having located and corrected all the bugs in the framework, make sure the simple mapped fields are not reset by the GenerateLedgerDimension() functoid. For example, the TaxItemGroup should be assigned a priority greater than 3 in the target mapping.
Now you may finally commence the import and let the system write the journal lines. Press Copy data to target in the processing group, choose the right staging session, do not miss the ridiculous Run button, confirm another dialog box, and enjoy the long-awaited result:
A new run is going to overwrite the journal lines, yet this behavior is not guaranteed.
The output parameters of functoids are scripted in the entity class’ getReturnFields() static method, while the input parameters and the processing order are represented in the table metadata by a field group (?!) of the same name:
In our application we need a functoid to post-process the tax amount (saved in the ReturnAmount staging field), writing the exact amount as a ‘tax correction’ in relation with the journal line.
The necessary steps are:
public Container GenerateCorrectTaxAmount(boolean _stagingToTarget = true)
taxAmountJournal = ledgerJournalEngine.taxAmountJournal(target);
if (taxAmountJournal != entity.RemainAmount)
ledgerJournalEngine.correctTaxAmount(target, entity.RemainAmount, true, false);
res = [0.00];
In our application the output parameter may be skipped as the calculation result is saved in a separate table. More important, all the functoids are called before the target.insert() call. Tax correction record requires a RecID of the master record, though. This record ID is not available before saving. The standard approach might be to construct the respective related TaxWorkRegulation record from scratch, which is hardly feasible, or to overload the insertUpdate() entity class function and call the functoid after super().
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.
|axsa: Microsoft Dynamics AX 2012 Data Import/Export Framework (Old name - Data Migration Framework) is now available on Information source||Blog bot||DAX Blogs||0||16.03.2013 15:12|
|DAX: Official Dynamics AX 2012 R2 Content (update) - Where is it, and how can you find out about updates?||Blog bot||DAX Blogs||0||03.12.2012 11:11|
|emeadaxsupport: New Content for Microsoft Dynamics AX 2012 : October 2011||Blog bot||DAX Blogs||0||27.10.2011 17:11|
|axinthefield: Dynamics AX Event IDs||Blog bot||DAX Blogs||0||01.03.2011 22:11|
|axStart: Microsoft Dynamics AX 2009 Hot Topics Web Seminar Series||Blog bot||DAX Blogs||0||06.08.2008 12:05|
|Опции темы||Поиск в этой теме|