Dynamics AX 2012 Excel Add-in - Vendor transactions import

Microsoft Dynamics AX 2012 Excel Add-in – Vendor transactions Import

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.