>

Import Ledger transactions/balances using Excel Add-in – AX 2012

Purpose:The purpose of this document is to illustrate how to use Microsoft Dynamics AX 2012 Excel Add-in for import of ledger 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.
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 ledger transactions.
In order to import ledger transactions using Excel the mentioned AIF Web Service will be used.
Assumption: The assumption is that appropriate reference data such as main accounts, 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

clip_image001
LedgerJournalTrans

Field Name Field Description
Journal batch number
Voucher
Date
Company accounts
Account type
LedgerDimension
LedgerDimension.MainAccount
LedgerDimension.Department
Description
Debit
Credit
Currency

clip_image002
Sequence:
1. Publish All
Result:
Dynamics AX – General Journal
image
Dynamics AX – General Journal lines
image
Dynamics AX – 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 ledger transactions into Microsoft Dynamics AX 2012. General Journal AIF Web Service (LedgerGeneralJournalService) can be used in Excel for import of ledger transactions. Excel template can be created and used for import of ledger transactions.
Imparted from Here

Leave a Comment

NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Follow

Get every new post on this blog delivered to your Inbox.

Join other followers:

error: Content is protected !!