dynamics-coe: Dynamics AX Export/Import Framework for Vendor balance migration

[Blog bot]

Источник: http://blogs.msdn.com/b/dynamics-coe...migration.aspx
==============

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:

• 3 data formats: CSV, ODBC, other AX database
• Staging capabilities: the data is first put into an intermediary table in AX
• Configurable and customizable within AX

Either way, using the tool in the real life may be an excruciating experience. To use DMF efficiently, you require some X++ programming skills. Schedule 1-2 days for configuration and initial testing per complex entity.

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:

• Post the AP/AR journals prior to the GL balances, use tall-tale voucher numbers. Delete the respective GL transactions from the database (GeneralJournalAccountEntry, GeneralJournalEntry in AX2012 or LedgerTrans in AX2009) by brute force. Re-calculate the ledger balances.

OR

• Post the AP/AR journals. Make sure the resulting balances on AR/AP accounts match the trial balance in the legacy accounting system. Exclude the respective accounts from your GL balance list and only import Bank, Assets, Equity etc.

With regards to the AP/AR open transactions, make sure you get the following info from the legacy system of your customer (the data usually comes in an Excel list):

Column

Example

Note

Transdate

01.02.2013

Historical posting date

LedgerDimension

1002

Vendor/Creditor account number

Txt

Invoice No. 7548784

Transaction text

AmountCur

1190.01

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.

Currencycode

GBP



AmountMst

2193.18

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.

Invoice

7548784

Empty for open payment transactions

Due

16.02.2013

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:

Column

Example

Note

Prepayment

0

Yes/No

At least for AR (given) pre-payments the summary GL account may differ.

DocumentDate

25.01.2013

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.

TaxItemGroup

FULL

Legacy accounting programs normally rely on a single tax code. Map it to AX tax item groups.

TaxAmount

191.01

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.

LedgerAccount

211100

The balance account cannot be entered directly and have to be mapped to an AP posting profile.

CashDiscAmount

0.00

Cash discounts calculated by AX may differ from the values in the legacy system.

DefaultDimension

1002

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.

Voucher

00000002_005

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:

• SSIS is a prerequisite: the DMF service can only be installed at a SQL instance hosting Integration Services
• For some nations: change the decimal symbol from ‘,’ to ‘.’ In the Windows regional settings. Otherwise the installer program will be complaining about a missing gacutil.exe (?!)

Check your source data is complete:

• The source file MUST expose all the natural key fields. Even worse, those key fields cannot be set to a default value. In our example you require 3 additional columns:
  • JournalNum. Should no journal with this number exist, AX is going to create a new journal at the Staging-->Target stage using the JournalName.
  • LineNum. Generate a LineNumber per journal line in Excel: 1-2- 3-…
  • Voucher. Generate a unique voucher number per line. The voucher number MUST follow the number sequence format for the selected journal name; otherwise you will neither be able to post the journal nor to delete the ist lines. For example, the voucher number sequence ########_005 may only have numbers as 00000002_005, 00000003_005, 00000004_005… Use pattern recognition in Excel to do that.
• Should one line have more than one financial dimension, use string concatenation in Excel to combine them into one string, separated by dashes, underscores or any other unique symbol: DESub-ProdDept-Vend1002.
• Do complex mapping and lookups in Excel (=VLOOKUP()).
• Beware of non-ASCII symbols. In Europe we readily use Ö, Ü, ß and even Я and Ж, while Microsoft Excel is not able to save CSV files in the UTF format (see below).
• For a number of reasons, use the point ‘.’ as a decimal separator. If your Windows regional settings suggest a comma, disable the option Use system separators in Excel and enforce ‘.’ in all numerical fields:

• Assign data types / number formats in Excel properly.
• Put AX field names into the 1st row as a column title or use a sample CSV file generated from AX.
• A vendor invoice is posted in credit ;) You may have invoices and payments in the same file. A negative credit amount is not the same as a debit amount in AX, use the function =IF(XXX:YYY > 0; XXX:YYY; 0) to split positive and negative values.

Having said that, you should have got something similar to this Excel file:

JournalName

JournalNum

LineNum

Voucher

LedgerDimension

AccountType

TransDate

Txt

AmountCur

AmountCurDebit

AmountCurCredit

CurrencyCode

AmountMST

ExchRate

Invoice

Due

TaxItemGroup

TaxAmountCur

DefaultDimension

GenJrn

91

1.00

00000005_005

1002

Vend

01.02.2013

Rechnung 7548784

1190.01

0.00

1190.01

GBP

2193.18

184.30

7548784

16.02.2013

FULL

191.01

OU_4771

GenJrn

91

2.00

00000006_005

1002

Vend

03.04.2013

Payment 8999

-2000.00

2000.00

0.00

GBP

3701.56

185.08

OU_4771

GenJrn

91

3.00

00000007_005

1202

Vend

05.02.2013

Test UTF: ÄÖÜ ДЯФЖ

1000.00

0.00

1000.00

EUR

1000.00

100.00

47766а

20.02.2013

FULL

160.00

OU_4771

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:

• Instead of calling the old-style modifiedField() the modifiedFieldValue() method is used instead, while the latter method may be considered only a subset of the former.
• A surprising ‘feature’ of DMF requires the option Call validateField method be set prior to the Call modified field option. In other words, the initialization must be preceded by validation. Most probably, the developers simply forgot to reset the variables.
• Again, instead of validateField() the validateFieldValue() method is called.

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.

Custom functoids

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:

1. To write the source code of the functoid in the respective class. The staging record is referred by the entity and the target table by the target variable, respectively.
2. To define the output parameters in the getReturnFields method
3. To declare a new field group with the output field.
4. To let AX re-build the mapping.

public Container GenerateCorrectTaxAmount(boolean _stagingToTarget = true)
{
container res;
TaxAmountCur taxAmountJournal;

if (_stagingToTarget)
{
if (entity.RemainAmount)
{
taxAmountJournal = ledgerJournalEngine.taxAmountJournal(target);
if (taxAmountJournal != entity.RemainAmount)
{
ledgerJournalEngine.correctTaxAmount(target, entity.RemainAmount, true, false);
}
}
}

res = [0.00];

return res;
}
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().




Источник: http://blogs.msdn.com/b/dynamics-coe...migration.aspx