Dynamics AX 2012 Sales Agreements Import using Excel Add-in

Microsoft Dynamics AX 2012 Excel Add-in – Sales agreements Import

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

Challenge: Data model changes in Microsoft Dynamics related to high normalization and introduction of surrogate keys made some imports more complex. Moreover Sales Blanket orders functionality was completely redesigned and in Microsoft Dynamics AX 2012 it’s called Sales Agreements now.

Solution: Microsoft Dynamics AX 2012 ships with a number of AIF Web Service which can be used in integration scenarios. However there’s no AIF Web Service out-of-the-box that can be used in Excel at the moment for import of sales agreements. In order to import a sales agreement using Excel the appropriate tables will be used.

Assumption: The assumption is that appropriate reference data such as customers, etc. was created in advance.

Data Model:

Table Name	Table Description
AgreementClassification	The AgreementClassification table contains the agreement classifications.
AgreementClassificationTranslation	The AgreementClassificationTranslation table contains the translations for the agreement classifications.
AgreementHeader	The AgreementHeader table contains each of the purchase and sales agreements.
AgreementHeaderDefault	The AgreementHeaderDefault table contains the release order defaulting policies.
AgreementHeaderDefaultHistory	The AgreementHeaderDefaultHistory table contains a snapshot of the release order defaulting policies.
AgreementHeaderHistory	The AgreementHeaderHistory table is a base table for the sales and purchase agreement header history tables.
AgreementLine	The AgreementLine table stores information about the purchase agreement details.
AgreementLineDefault	The AgreementLineDefault table contains the line release order defaulting policies.
AgreementLineDefaultHistory	The AgreementLineDefaultHistory table contains a snapshot of line release order defaulting policies.
AgreementLineHistory	The AgreementLineHistory table contains a snapshot of the agreement lines.
AgreementLineQuantityCommitment	The AgreementLineQuantityCommitment table contains each of the agreement lines of the quantity type.
AgreementLineQuantityCommitmentHistory	The AgreementLineQuantityCommitmentHistory table contains a snapshot of agreement quantity lines.
AgreementLineReference	The AgreementLineReference table contains the relation between intercompany sales agreement lines and intercompany purchase agreement lines.
AgreementLineReleasedLine	The AgreementLineReleasedLine table contains released agreement lines.
AgreementLineReleasedLineHistory	The AgreementLineReleasedLineHistory table contains a snapshot of released agreement lines.
AgreementLineVolumeCommitment	The AgreementLineVolumeCommitment table contains each of the agreement lines of the volume type.
AgreementLineVolumeCommitmentHistory	The AgreementLineVolumeCommitmentHistory table contains a snapshot of agreement volume lines.
AgreementReference	The AgreementReference table contains the relation between intercompany sales agreement headers and intercompany purchase agreement headers.
AgreementReleaseHeaderMatch	The AgreementReleaseHeaderMatch table contains matchings between agreements and orders.
SalesAgreementHeader	The SalesAgreementHeader table contains all of the sales agreement header specific information.
SalesAgreementHeaderDefault	The SalesAgreementHeaderDefault table contains the release sales order defaulting policies.
SalesAgreementHeaderDefaultHistory	The SalesAgreementHeaderDefaultHistory table contains a snapshot of release sales order defaulting policies.
SalesAgreementHeaderHistory	The SalesAgreementHeaderHistory table contains a snapshot of sales agreement headers.
SalesTable	The SalesTable table contains all sales order headers regardless of whether they have been posted.
SalesLine	The SalesLine table contains all sales order lines regardless of whether they have been posted.

Data Model Diagram:

Agreements

Red area highlights tables forming Agreements – Headers data model

Green area highlights tables forming Agreements – Lines data model

Yellow area highlights tables forming Sales Agreements data model

Release orders

VSD: https://docs.google.com/open?id=0B3rbAZy5q2ExNGNiYzEwODctY2Y4ZC00OGFlLWJiNjMtNDMxMGFjNzc4Nzk2

Walkthrough:

Connection

Add Tables

Dynamics AX Error

Solution:

- Add replacement key to AgreementHeader table

I decided to re-use existing DocumentTitle field as Replacement key without introduction of brand new field (Temporary key) and regeneration of AIF Proxy classes here. That’s why I re-populated DocumentTitle field for all existing records using RecID and assigned Index1 = {DocumentTitle} as Replacement key to AgreementHeader table.

Field Chooser

SalesAgreementHeader

Field Name	Field Description
	Agreement classification.AgreementRelationType
	Agreement classification.Name
	Currency
	Customer account
	InstanceRelationType
	Sales agreement ID
	IsDeleted
	Customer account.CustomerDataAreaId
	Document title
	Legal entities.DataArea
	Legal entities.Name
	Party ID
	Legal entities.Party ID
	Legal entities.Party type

AgreementLine

Field Name	Field Description
	Dimension No..InventDimDataAreaId
	Dimension No.
	Item number
	Item number.ItemDataAreaId
	Agreement header record ID.InstanceRelationType
	Agreement header record ID.Document title
	Effective date
	Expiration date
	InstanceRelationType
	Line number
	Quantity
	Unit
	Unit price
	IsDeleted

InventDim

Excel VLookup function may be used to find appropriate InventDimId automatically based on criteria

Sequence:

1. SalesAgreementHeader - Publish Selected

2. AgreementLine – Publish Selected

Result:

Dynamics AX – Sales Agreement

Dynamics AX – Release Order

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 sales agreements into Microsoft Dynamics AX 2012. Although there’s no AIF Web Service out-of-the-box that can be used at the moment in Excel for import of sales agreements, appropriate tables can be used instead. Excel template can be created and used for import of sales agreements.

Author: Alex Anikiev, PhD, MCP