Sunday, January 29, 2012

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

3 comments:

  1. Is there a way to link a sales agreement to a sales order sales order has already been created without linking a sales agreement to the order in the creation process?

    ReplyDelete
  2. Have you tried to create an AIF Web Service? I've tried it an have this problem:
    http://stackoverflow.com/questions/14405993/the-update-from-my-aif-webservice-for-salesagreementheader-ax-2012-fails

    ReplyDelete
  3. Hi Alex, can you define which services needs to be deployed to be able to reach to the appropriate tables for sales agreements import?

    ReplyDelete