Purpose: The purpose of this document is to illustrate how to use Microsoft Dynamics AX 2012 Excel Add-in for import of vendor transactions with required level of detail.
Challenge: Data model changes in Microsoft Dynamics related to high normalization and introduction of surrogate keys made some imports more complex. In fact the data model forming General Journal was not dramatically changed and import principle remains the same – populate the journal and then post the journal. Numerous fields are available on General Journal line level in Rich client to achieve required level of detail during import. In order to import vendor transactions with required level of detail using Excel Add-in some additional work may be required.
Solution: Microsoft Dynamics AX 2012 ships with General Journal AIF Web Service (LedgerGeneralJournalService) which can be used in integration scenarios. Also General Journal AIF Web Service can be used in Excel for import of vendor transactions. However General Journal AIF Web Service may not expose all fields which may be required for import such as Invoice, etc. In this walkthrough in order to import vendor transactions using Excel I’m going to modify General Journal AIF Web Service (LedgerGeneralJournalService), alternatively the appropriate tables may be used instead. Please note that I’ll make use of Main account field to bring Vendor account information over to Microsoft Dynamics AX 2012, so no additional work will be required to align vendor account comparing to similar walkthrough where I import customer transactions using raw tables.
Assumption: The assumption is that appropriate reference data such as vendors, etc. was created in advance.
Data Model:
Table Name
Table Description
LedgerJournalTable
The LedgerJournalTable table contains all the defaulting and state information pertaining to a single journal. The transaction details of a journal are managed in the LedgerJournalTrans table.
LedgerJournalTrans
The LedgerJournalTrans table contains the transaction detail information that pertains to a single journal. The individual transaction lines are also referred to as voucher lines. The journal is a record in the LedgerJournalTable table.
Data Model Diagram:
Walkthrough:
AxdLedgerGeneralJournal Query
General Journal AIF Web Service doesn’t expose all fields which may be required for import such as Invoice, etc.
In order to get access to more fields for vendor transactions import when using General Journal AIF Web Service AxdLedgerGeneralJournal Query will have to be modified appropriately
That’s why I added Invoice field into the list of fields for LedgerJournalTrans data source
Please note that Invoice field is already included in LedgerJournalTrans table and has appropriate support in AxLedgerJournalTrans AIF Proxy class
So there’s no need to Update document service (LedgerGeneralJournalService) and regenerate AIF Proxy class(es). However in order to enable Invoice field to be used by General Journal AIF Web Service appropriate Service Group (LedgerServices) which contains LedgerGeneralJournalService AIF Web Service will have to be redeployed.
Once LedgerServices Service Group is redeployed you will see the following infolog
You can also see LedgerServices Service Group activated in Inbound ports form in Administration > Setup > Application Integration Framework
Please note that if you will manually create Enhanced port, say LedgerServicesAlex
And then select “create” operation on LedgerGeneralJournalService AIF Web Service in Select service operations form
Eventually you will be able to see Invoice field listed in Document data policies form
This is a proof that Invoice field will now be available for use by General Journal AIF Web Service
Connection
Add Data
Field Chooser
Accounting structure
LedgerJournalTable
Field Name
Field Description
Journal batch number
Name
Description
LedgerJournalTrans
Field Name
Field Description
Journal batch number
Voucher
Date
Company accounts
Account type
LedgerDimension
LedgerDimension.Main Account
LedgerDimension.Department
DefaultDimension.Department
Description
Debit
Credit
Currency
LedgerJournalTrans.Invoice
Invoice
Offset company accounts
Offset account type
OffsetLedgerDimension
OffsetLedgerDimension.Main Account
OffsetLedgerDimension.Department
Offset-transaction text
1) 
2) 
3) 
4) 
Sequence:
1. GeneralJournalService – Publish All
Result:
Dynamics AX – General Journal
Dynamics AX – General Journal lines
Please note that Invoice field is properly propagated on Invoice tab
Dynamics AX – General Ledger Posting
It’s important to mention that I used Main account field to bring over Vendor account information to Microsoft Dynamics AX 2012 without any customizations. This is possible because AxdLedgerGeneralJournal document class has the logic to assign appropriate LedgerDimension value based on value specified in Main account field and actual Account type defined (in our case it’s Vendor) in afterDeserializeEntity method as shown below
SQL Trace:
Summary: For the purposes of small or medium data migration (data conversion) where performance is not a concern Excel can be used for import of vendor transactions into Microsoft Dynamics AX 2012. General Journal AIF Web Service (LedgerGeneralJournalService) can be used in Excel for import of vendor transactions. However General Journal AIF Web Service (LedgerGeneralJournalService) doesn’t expose all fields which may be required for import. In this case General Journal AIF Web Service (LedgerGeneralJournalService) Query may be customized to accommodate for more fields, or raw tables may be used instead. Excel template can be created and used for import of vendor transactions.
In this document I modified General Journal AIF Web Service (LedgerGeneralJournalService) to import vendor transactions with required level of detail. In another similar walkthrough dedicated to import of customer transactions with required level of detail I’ll use raw tables approach to illustrate the mechanics of the process better. Raw tables approach is much more complex than AIF Web Service modification approach for the user.
Author: Alex Anikiev, PhD, MCP
Tags: Dynamics ERP, Dynamics AX 2012, Excel, Dynamics AX 2012 Excel Add-in, Data Import, Data Conversion, Data Migration, Application Integration Framework, Vendor transactions.
Note: This document is intended for information purposes only, presented as it is with no warranties from the author. This document may be updated with more content to better outline the concepts and describe the examples. It’s recommended that all Data Model changes introduced as a part of this demonstration will be removed once you complete data import exercise.