Dynamics AX 2012 Purchase Agreements Import using Excel Add-in

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

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

Challenge: Data model changes in Microsoft Dynamics related to high normalization and introduction of surrogate keys made some imports more complex. Moreover Purchase Blanket orders functionality was completely redesigned and in Microsoft Dynamics AX 2012 it’s called Purchase 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 purchase agreements. In order to import a purchase 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.
PurchAgreementHeader	The PurchAgreementHeader table contains purchase agreement headers.
PurchAgreementHeaderDefault	The PurchAgreementHeaderDefault table contains the release purchase order defaulting policies.
PurchAgreementHeaderDefaultHistory	The PurchAgreementHeaderDefaultHistory table contains a snapshot of the release purchase order defaulting policies.
PurchAgreementHeaderHistory	The PurchAgreementHeaderHistory table contains a snapshot of the purchase agreement header record.
PurchTable	The PurchTable table contains all the purchase order headers regardless of whether they have been posted.
PurchLine	The PurchLine table contains all purchase order lines regardless whether they have been posted or not.

Data Model Diagram:

Agreements

Red area highlights tables forming Agreements – Headers data model

Green area highlights tables forming Agreements – Lines data model

Blue area highlights tables forming Purchase 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

PurchAgreementHeader

Field Name	Field Description
	Agreement classification.AgreementRelationType
	Agreement classification.Name
	Currency
	Vendor account
	InstanceRelationType
	Purchase agreement ID
	IsDeleted
	Vendor account.VendorDataAreaId
	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. PurchAgreementHeader - Publish Selected

2. AgreementLine – Publish Selected

Result:

Dynamics AX – Purchase 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 purchase 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 purchase agreements, appropriate tables can be used instead. Excel template can be created and used for import of purchase agreements.

Author: Alex Anikiev, PhD, MCP