Microsoft Dynamics AX 2012 Xpp – Vendors Import

Purpose: The purpose of this document is to illustrate how to write X++ code in Microsoft Dynamics AX 2012 in order to import Vendors with addresses and contact details.

Challenge: Data model changes in Microsoft Dynamics AX 2012 related to high normalization and introduction of surrogate keys made some imports more complex. Global Address Book data model was changed in Microsoft Dynamics AX 2012 which has an impact on Vendors import. Comparing to the previous release of Microsoft Dynamics AX 2009 more tables are involved into the process of Vendors import.

Solution: Appropriate tables buffers (DirPartyTable, VendTable, etc) will be used when writing X++ code in Microsoft Dynamics AX 2012 in order to import Vendors. Alternatively AxBC classes may be used instead of table buffers.

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

Data Model:

Table Name
Table Description
VendTable
The VendTable table contains vendors for accounts payable.
DirAddressBook
The DirAddressBook table contains address book records.
DirAddressBookParty
The DirAddressBookParty table is a relationship table that contains a link between an address book and party records.
DirPartyTable
The DirPartyTable table contains all the parties. The global address book stores all of the people and organizations that are used in the system.
DirPerson
The DirPerson table contains the party records of person.
DirOrganizationBase
The DirOrganizationBase table contains definitions for internal and external organizations.
DirOrganization
The DirOrganization table contains details for the external organizations in the system.
DirPersonName
The DirPersonName table contains the date effective names for each person in the system.
DirOrganizationName
The DirOrganizationName table contains the date effective names for each of the organizations in the system.
DirDunsNumber
The DirDunsNumber table contains definitions of the DUNS numbers.
DirNameAffix
The DirNameAffix table contains the name titles and suffixes that are defined in the system.
LogisticsLocation
The LogisticsLocation table contains the attributes for a location for postal address and contact information.
LogisticsLocationExt
The LogisticsLocationExt table contains additional information about the locations.
LogisticsLocationRole
The LogisticsLocationRole table contains the roles that are played by the locations in the system.
LogisticsLocationRoleTranslation
The LogisticsLocationRoleTranslation table contains the translation values for the location role descriptions in the languages that are supported in the system.
DirPartyLocation
The DirPartyLocation table contains the relationship between the party and location.
DirPartyLocationRole
The DirPartyLocationRole table is the relationship table between the location and function of the party.
LogisticsElectronicAddress
The LogisticsElectronicAddress table contains communication values that are defined for various parties.
LogisticsPostalAddress
The LogisticsPostalAddress table contains addresses in the system that could be associated with various entities.
LogisticsAddressCountryRegion
The LogisticsAddressCountryRegion table contains definitions of countries or regions. Addresses are associated with countries and regions through CountryRegionId field.
LogisticsAddressCountryRegionTranslation
The LogisticsAddressCountryRegionTranslation table contains the translations of the country region long and short names.
LogisticsAddressState
The LogisticsAddressState table contains states that are used by addresses.
LogisticsAddressCounty
The LogisticsAddressCounty table contains definitions of counties.
LogisticsAddresssCity
The LogisticsAddresssCity table contains the definitions of the cities.
LogisticsAddressDistrict
The LogisticsAddressDistrict table contains the definitions of the districts.
LogisticsAddressZipCode
The LogisticsAddressZipCode table contains zip codes that can be used by addresses.

Data Model Diagram:
<![if !vml]><![endif]>

Vendors


Development:

ttsBegin: Use ttsBegin to start a transaction.

clear: The clear method clears the contents of the record.

initValue: The initValue method initializes the fields of the record.
initFrom*: The initFrom* methods usually populate the fields of the child record based on the fields on the parent record. Example is initFromVendGroup method on VendTable table.

validateWrite: The validateWrite method checks whether the record can be written.
write: The write method writes the record to the database.

insert: The insert method inserts the record into the database.
doInsert: The doInsert method inserts the record into the database. Calling doInsert ensures that any X++ code written in the insert method of the record is not executed. Calling insert always executes the X++ code written in the insert method of the record.

ttsCommit: Use ttsCommit to commit a transaction.

Source code:

Person
static void VendorPersonsXppImport(Args _args)
{
    #define.Name("Alex Anikiev")
    #define.FirstName("Alex")
    #define.LastName("Anikiev")
    #define.PostalAddressName("Postal address")
    #define.City("Alpharetta")
    #define.County("FULTON")
    #define.Country("USA")
    #define.Street("1125 Sanctuary Pkwy #300")
    #define.State("GA")
    #define.ZipCode("30009")
    #define.ElectronicAddressName("Electronic address")
    #define.Locator("alexani@microsoft.com")
    #define.LocatorExt("")
    #define.VendAccount("Alex")
    #define.VendGroup("10")
    #define.Currency("USD")
    #define.CostCenter("OU_4803")
    #define.Department("OU_2311")
    #define.ExpensePurpose("Training")

    DirPerson                   dirPerson;
    DirPersonName               dirPersonName;
    LogisticsLocation           logisticsLocation;
    LogisticsPostalAddress      logisticsPostalAddress;
    LogisticsElectronicAddress  logisticsElectronicAddress;
    VendTable                   vendTable;

    try
    {
        ttsbegin;

        //Person
        dirPerson.clear();
        dirPerson.initValue();
        dirPerson.Name = #Name;

        if (dirPerson.validateWrite())
        {
            dirPerson.insert();

            if (dirPerson)
            {
                //Person name
                dirPersonName.clear();
                dirPersonName.initValue();
                dirPersonName.FirstName = #FirstName;
                dirPersonName.LastName = #LastName;
                dirPersonName.Person = dirPerson.RecId;
                dirPersonName.ValidFrom = DateTimeUtil::utcNow();
                dirPersonName.ValidTo = DateTimeUtil::maxValue();

                if (dirPersonName.validateWrite())
                {
                    dirPersonName.insert();
                }
                else
                    throw error("Person name");

                //Location
                logisticsLocation = LogisticsLocation::create(#PostalAddressName, NoYes::Yes);

                //Party location
                DirParty::addLocation(dirPerson.RecId, logisticsLocation.RecId, true, true, false, [LogisticsLocationRole::findBytype(LogisticsLocationRoleType::Home).RecId]);

                //Postal address
                logisticsPostalAddress.clear();
                logisticsPostalAddress.initValue();
                logisticsPostalAddress.City = #City;
                logisticsPostalAddress.County = #County;
                logisticsPostalAddress.CountryRegionId = #Country;
                logisticsPostalAddress.Street = #Street;
                logisticsPostalAddress.State = #State;
                logisticsPostalAddress.ZipCode = #ZipCode;
                logisticsPostalAddress.Address = LogisticsPostalAddress::formatAddress(
                #Street, #ZipCode, #City, #Country, #State, #County);
                logisticsPostalAddress.Location = logisticsLocation.RecId;
                logisticsPostalAddress.ValidFrom = DateTimeUtil::utcNow();
                logisticsPostalAddress.ValidTo = DateTimeUtil::maxValue();

                if (logisticsPostalAddress.validateWrite())
                {
                    logisticsPostalAddress.insert();
                }
                else
                    throw error("Postal address");

                //Location
                logisticsLocation = LogisticsLocation::create(#ElectronicAddressName, NoYes::No);

                //Party location
                DirParty::addLocation(dirPerson.RecId, logisticsLocation.RecId, false, true, false);

                //Electronic address
                logisticsElectronicAddress.clear();
                logisticsElectronicAddress.initValue();
                logisticsElectronicAddress.Location = logisticsLocation.RecId;
                logisticsElectronicAddress.Type = LogisticsElectronicAddressMethodType::Email;
                logisticsElectronicAddress.Locator = #Locator;
                logisticsElectronicAddress.LocatorExtension = #LocatorExt;
                logisticsElectronicAddress.ValidFrom = DateTimeUtil::utcNow();
                logisticsElectronicAddress.ValidTo = DateTimeUtil::maxValue();

                if (logisticsElectronicAddress.validateWrite())
                {
                    logisticsElectronicAddress.insert();
                }
                else
                    throw error("Electronic address");

                //Vendor
                vendTable.clear();
                vendTable.initValue();
                //vendTable.Currency = "";

                vendTable.AccountNum = #VendAccount;
                //vendTable.AccountNum = NumberSeq::newGetNum(VendParameters::numRefVendAccount()).num();
                vendTable.Party = dirPerson.RecId;

                vendTable.VendGroup = #VendGroup;
                vendTable.initFromVendGroup(VendGroup::find(#VendGroup));

                vendTable.Currency = #Currency;
                vendTable.DefaultDimension = AxdDimensionUtil::getDimensionAttributeValueSetId(
                [3, "CostCenter", #CostCenter, "Department", #Department, "ExpensePurpose", #ExpensePurpose]);

                if (vendTable.validateWrite())
                {
                    vendTable.insert();
                }
                else
                    throw error("Vendor");
            }
        }
        else
            throw error("Person");

        ttscommit;
    }
    catch
    {
        error("Error!");
        return;
    }

    info("Done!");
}

Organization
static void VendorOrganizationsXppImport(Args _args)
{
    #define.Name("Alex Anikiev")
   #define.PostalAddressName("Postal address")
    #define.City("Alpharetta")
    #define.County("FULTON")
    #define.Country("USA")
    #define.Street("1125 Sanctuary Pkwy #300")
    #define.State("GA")
    #define.ZipCode("30009")
    #define.ElectronicAddressName("Electronic address")
    #define.Locator("alexani@microsoft.com")
    #define.LocatorExt("")
    #define.VendAccount("Alex")
    #define.VendGroup("10")
    #define.Currency("USD")
    #define.CostCenter("OU_4803")
    #define.Department("OU_2311")
    #define.ExpensePurpose("Training")

    DirOrganization             dirOrganization;
    LogisticsLocation           logisticsLocation;
    LogisticsPostalAddress      logisticsPostalAddress;
    LogisticsElectronicAddress  logisticsElectronicAddress;
    VendTable                   vendTable;

    try
    {
        ttsbegin;

        //Organization
        dirOrganization.clear();
        dirOrganization.initValue();
        dirOrganization.Name = #Name;

        if (dirOrganization.validateWrite())
        {
            dirOrganization.insert();

            if (dirOrganization)
            {
                //Location
                logisticsLocation = LogisticsLocation::create('Postal address', NoYes::Yes);

                //Party location
                DirParty::addLocation(dirOrganization.RecId, logisticsLocation.RecId, true, true, false, [LogisticsLocationRole::findBytype(LogisticsLocationRoleType::Business).RecId]);

                //Postal address
                logisticsPostalAddress.clear();
                logisticsPostalAddress.initValue();
                logisticsPostalAddress.City = #City;
                logisticsPostalAddress.County = #County;
                logisticsPostalAddress.CountryRegionId = #Country;
                logisticsPostalAddress.Street = #Street;
                logisticsPostalAddress.State = #State;
                logisticsPostalAddress.ZipCode = #ZipCode;
                logisticsPostalAddress.Address = LogisticsPostalAddress::formatAddress(
                #Street, #ZipCode, #City, #Country, #State, #County);
                logisticsPostalAddress.Location = logisticsLocation.RecId;
                logisticsPostalAddress.ValidFrom = DateTimeUtil::utcNow();
                logisticsPostalAddress.ValidTo = DateTimeUtil::maxValue();

                if (logisticsPostalAddress.validateWrite())
                {
                    logisticsPostalAddress.insert();
                }
                else
                    throw error("Postal address");

                //Location
                logisticsLocation = LogisticsLocation::create('Electronic address', NoYes::No);

                //Party location
                DirParty::addLocation(dirOrganization.RecId, logisticsLocation.RecId, false, true, false);

                //Electronic address
                logisticsElectronicAddress.clear();
                logisticsElectronicAddress.initValue();
                logisticsElectronicAddress.Location = logisticsLocation.RecId;
                logisticsElectronicAddress.Type = LogisticsElectronicAddressMethodType::Email;
                logisticsElectronicAddress.Locator = #Locator;
                logisticsElectronicAddress.LocatorExtension = #LocatorExt;
                logisticsElectronicAddress.ValidFrom = DateTimeUtil::utcNow();
                logisticsElectronicAddress.ValidTo = DateTimeUtil::maxValue();

                if (logisticsElectronicAddress.validateWrite())
                {
                    logisticsElectronicAddress.insert();
                }
                else
                    throw error("Electronic address");

                //Vendor
                vendTable.clear();
                vendTable.initValue();
                //vendTable.Currency = "";

                vendTable.AccountNum = #VendAccount;
                //vendTable.AccountNum = NumberSeq::newGetNum(VendParameters::numRefVendAccount()).num();
                vendTable.Party = dirOrganization.RecId;

                vendTable.VendGroup = #VendGroup;
                vendTable.initFromVendGroup(VendGroup::find(#VendGroup));

                vendTable.Currency = #Currency;

                vendTable.DefaultDimension = AxdDimensionUtil::getDimensionAttributeValueSetId(
                [3, "CostCenter", "OU_4803", "Department", "OU_2311", "ExpensePurpose", "Training"]);

                if (vendTable.validateWrite())
                {
                    vendTable.insert();
                }
                else
                    throw error("Vendor");
            }
        }
        else
            throw error("Organization");

        ttscommit;
    }
    catch
    {
        error("Error!");
        return;
    }

    info("Done!");
}

Result:

Microsoft Dynamics AX 2012 – Global Address Book (Person)


Microsoft Dynamics AX 2012 – Global Address Book (Organization)


Microsoft Dynamics AX 2012 – Vendor (Person)


Microsoft Dynamics AX 2012 – Vendor (Organization)


Microsoft Dynamics AX 2012 – Address (Person)


Microsoft Dynamics AX 2012 – Address (Organization)


Microsoft Dynamics AX 2012 – Contact details (Person)


Microsoft Dynamics AX 2012 – Contact details (Organization)


Note: Microsoft Dynamics AX 2012 Demo Data (Company CEU) was used for this example

Version: Microsoft Dynamics AX 2012 RTM

Summary: In this document I explained how to write X++ code in Microsoft Dynamics AX 2012 in order to import Vendors with addresses and contact details. Appropriate table buffers were used when writing X++ code in Microsoft Dynamics AX 2012. This approach allows getting better performance during the import comparing to usage of Microsoft Dynamics AX 2012 Excel Add-in. Also this approach is more flexible for extending in the case of not yet fully defined or changing business requirements. Please consider using DMF (Data Migration Framework) for import of significant amounts of data when performance is an important consideration. DMF (Data Migration Framework) provides a standard template for import of Vendors

Author: Alex Anikiev, PhD, MCP

Tags: Dynamics ERP, Dynamics AX 2012, X++, Xpp, Data Import, Data Conversion, Data Migration, Vendors

Note: This document is intended for information purposes only, presented as it is with no warranties from the author. This document may be updated with more content to better outline the concepts and describe the examples.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.