Sunday, January 29, 2012

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

8 comments:

  1. Hi Alex,

    Please help me out for this problem...

    I have installed office addins. I have excel 2010. When I am going to Dynamics Ax tab in excel and clicks on "Connection" it gives me the error on:" The operation is not supported for relative URI."



    Thank you before..



    Best Regards,

    Tommy MS

    ReplyDelete
    Replies
    1. Hi Tommy!

      Please find the solution for "The operation is not supported for relative URI" in this post: http://ax2012exceldataimport.blogspot.com/2012/01/dynamics-ax-2012-excel-add-in-issues.html

      Best Regards,
      /Alex

      Delete
  2. Hi Alex!

    I have worked through your guidance on how to use the Excel Add-In for Purchase Agreement update.
    However I have now discovered a problem with the intercompany creatin of purchase agreement from a sales agreement. I now get the following error, since the DocumentTitle must be unique for each agreement:

    "Cannot create a record in Agreement (AgreementHeader). Document title: Intercompany_1, On hold.
    The record already exists."

    Any hints?

    Christian

    ReplyDelete
  3. Hi Alex,

    I have worked through this and I cannot make it work.

    I am getting the message

    "Missing override for table 'AgreementLineQuantityCommitment'

    Document Generic document could not be created. Error details: Missing override for table 'AgreementLineQuantityCommitment'"

    Any ideas? I've added the replacement key to the AgreementHeader table as you suggested. I've tried selecting all fields from the AgreementLine table (the AgreementLineQuantity node) in the Excel Add-In and still getting the message.

    Regards

    Chris

    ReplyDelete
    Replies
    1. Hi Chris!

      This behavior in R2 is caused by the validation in AVL (Approved Vendor List) functionality. In Classes\PdsApprovedVendorListCheck\Methods\newFromTable AgreementLineQuantityCommittment table buffer is passed because we explicitly work with AgreementLineQuantityCommittment table (Add Tables approach) in Excel Add-in. And even though AgreementLineQuantityCommittment table (TableID = 4901) extends AgreementLine table (TableID = 4896), technically it’s another table, that’s why execution time exception occurs
      In order to overcome this issue please add the following lines (2nd and 3rd) in abovementioned method
      ...
      case tableNum(AgreementLine):
      case tableNum(AgreementLineQuantityCommitment):
      case tableNum(AgreementLineVolumeCommitment):
      avlCheck = PdsApprovedVendorListCheck_PurchAgrmtLn::construct();
      break;
      ...

      After making correction please Generate Incremental CIL and restart Excel. Then you can publish again and enjoy the result! :)

      Best Regards,
      /Alex

      Delete
  4. This comment has been removed by the author.

    ReplyDelete
  5. Hi Alex,

    According to your post when i click on publish button , records inserting in table but not showing in form , pleas suggest.

    ReplyDelete