Tuesday, January 17, 2012

Dynamics AX 2012 Vendor transactions/balances Import using Excel Add-in

Dynamics AX 2012 Excel Add-in – Vendor transactions/balances Import



Purpose:The purpose of this document is to illustrate how to use Microsoft Dynamics AX 2012 Excel Add-in for import of vendor open transactions.

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. However some information which is usually automatically generated in Microsoft Dynamics AX 2012 Rich Client by means of number sequences such as voucher number will have to be provided. Additionally with introduction of segmented control to specify multi-type account on General Journal line it became more challenging to work with customer and vendor transactions using Excel.

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 open transactions. In order to import vendor open transactions using Excel the mentioned AIF Web Service will be used. Some additional work will be done after initial import to align vendor account.

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:

image

Walkthrough:

Connection

image

Add Data

image

Field Chooser

image

Accounting structure

image

LedgerJournalTable

Field Name

Field Description

Journal batch number

Name

Description

image

LedgerJournalTrans

Field Name

Field Description

Journal batch number

Voucher

Date

Company accounts

Account type

DefaultDimension.Department

Description

Debit

Credit

Currency

Offset company accounts

Offset account type

OffsetLedgerDimension

OffsetLedgerDimension.Main Account

OffsetLedgerDimension.Department

Offset-transaction text

1)image

image

image

Please note that Description field contains a valid Vendor number in there. This is because currently in Excel there’s no means to specify Vendor account using LedgerDimension field and as a workaround Vendor account information will be first brought to AX and then special X++ Job will be executed to populate correct LedgerDimension field on General Journal line based on Vendor account information provided as a part of Description field. Description field was chosen for these purposes because it is free form text field and it is exposed to Excel via Web Service.

You can also incorporate Vendor number into Description like “[Vendor number] Description” and then use regular expression in X++ Job to extract Vendor number itself, and using X++ string functions you can also adjust Description to be “Description” only.

image

Import XPO

image

Execute X++ Job

image

Sequence:

1. Publish All

2. Execute X++ Job

Result:

Dynamics AX – General Journal

image

Dynamics AX – Execute X++ Job

image

Dynamics AX – General Journal lines

image

Dynamics AX – General Ledger Posting

image

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 open transactions into Microsoft Dynamics AX 2012. General Journal AIF Web Service (LedgerGeneralJournalService) can be used in Excel for import of vendor open transactions. Excel template can be created and used for import of vendor open transactions.

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.

1 comment:

  1. This is a good article & good site.Thank you for sharing this article. It is help us following categorize:
    healthcare, e commerce, programming, multi platform,inventory management, cloud-based solutions, it consulting, retail, manufacturing, CRM, technology means, digital supply chain management, Delivering high-quality service for your business applications,
    Solutions for all Industries,packaged applications,business applications, Web services, data migration
    Business intelligence, Business Development, Software Development etc.


    Our address:
    2002 Timberloch Place, Suite 200
    The Woodlands, TX 77380
    281-364-1799

    prologic-corp

    ReplyDelete