Monday, February 13, 2012

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.

14 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Hello,

    First of all, thank you for this post! I have been following your instructions, but I am receiving the following error when trying to upload the journal:
    The class AxLedgerJournalTrans does not have a parmInvoice method or field Invoice is missing from the query. Creation has been cancelled.

    I have added the Invoice field in the query as you instructed. I have also checked in the AxLedgerJournalTrans class and I saw the parmInvoice method there.

    I am not sure what else I am missing. I have redeployed the LedgerServices service group, and created a new Enhanced inbound port for LedgerServices (create). Checked the Document Data Policies and saw the Invoice field there.

    Any ideas?

    Thank you!

    ReplyDelete
    Replies
    1. I am seeing this same error message. Did you ever resolve this?

      Thanks!

      Delete
    2. I completed these steps and it is working now:

      http://technet.microsoft.com/en-us/library/gg840400.aspx

      Delete
  3. Hello all,

    Does anyone know if this would work for non-daily type journals like Vendor Transactions.
    I got the following error:
    "..Journal name APINV does not support journal type Daily.Error found when validating record.Document General journals could not be created. Error details: Error found when validating record..."

    ReplyDelete
    Replies
    1. Running into same issue... Did you get any update?

      Delete
    2. Hi Alex,

      After follwoing the above instructions even I got the same error. So please help me if you find any resolution for this.

      Delete
    3. We were not able to resolve this error in time, so we ended up requesting a customization.

      Delete
    4. This comment has been removed by the author.

      Delete
    5. Need to change the journal type to daily. GL> Setup> Journal Names> journal type.

      Delete
  4. This comment has been removed by the author.

    ReplyDelete