Copying data from one legal entity to another for a group or setup

In many multi company implementation, the data setup becomes a time consuming activity especially when the setup needs to be copied from a master legal entity. A typical example may be customer group or delivery terms that are common across all legal entities. If you have a lot of legal entities, DIXF will be an ideal method but it is time consuming. For consultants, who need to speed up things and who understands the basic table structure, they can use the below code (in form of a runnable job) to copy data between legal entities for a table. You would need to select the table and use the parameter for providing from and to legal entities, Select the "Insert and Update" checkbox if you need to update the records in the target legal entity. 

This script will only work for tables having a clustered index (unique non recid index). It will find the fields comprising unique index and select the target table using those fields. If the record is not found then it will create. If the record is found and the parameter "Insert and Update" is set then it will just update the record.

static void CopyDataBetweenLegalEntities(Args _args)

{

    DictTable       dt; 

    DictIndex       di; 

    DictField       field; 

    int             i; 

    Query           queryFrom, queryTo;

    QueryRun        queryRunFrom, queryRunTo;

    QueryBuildDataSource qbr1, qbr2;

    QueryBuildRange    queryRange;

    Common          fromCommon, toCommon;

    Dialog          dialog;

    DialogField     dfFromCompany, dfToCompany, dfTableName, dfInsertUpdate;

    str             tableName;

    CompanyId       fromCompany, toCompany;

    boolean         insertUpdate;

    

    dialog = new Dialog("Enter company and table information");

    dfTableName = dialog.addfield(extendedTypeStr(TableName));

    dfFromCompany = dialog.addField(extendedTypeStr(CompanyId), "From Company");

    dfToCompany   = dialog.addField(extendedTypeStr(CompanyId),"To Company");

    dfInsertUpdate = dialog.addField(extendedTypeStr(NoYesId), "Insert and update");

    

    if(dialog.run())

    {

        tableName = dfTableName.value();

        fromCompany = dfFromCompany.value();

        toCompany   = dfToCompany.value();

        insertUpdate = dfInsertUpdate.value();

    }

    dt = new DictTable( tableName2id(tableName));

 

    if (dt) 

    { 

        di = dt.indexObject(dt.indexUnique()); 

        if (di) 

        { 

            info( di.name()); //verify for not RecId index

        } 

        else

        {

            throw error("No unique index found on the table");

        }

        

        queryFrom = new Query();

        qbr1 = queryFrom.addDataSource(dt.id());

                

        changeCompany(fromCompany)

        {

            queryRunFrom = new QueryRun(queryFrom);

            while( queryRunFrom.next())

            {

                fromCommon = queryRunFrom.getNo(1);

                

                queryTo = new Query();

                qbr2 = queryTo.addDataSource(dt.id());

            

                for (i=1; i <= di.numberOfFields(); i++) 

                {

                    field = new DictField(dt.id(), di.field(i)); 

                    queryRange = qbr2.addRange(field.id());                    

                    queryRange.value(queryValue(fromCommon.(field.id())));

                    

                }

                changeCompany(toCompany)

                {

                    queryRunTo = new QueryRun(queryTo);

                    if(!queryRunTo.next())

                    {                        

                        toCommon = dt.makeRecord();

                        toCommon.clear();

                        toCommon.data(fromCommon);                        

                        toCommon.company(toCompany);

                        toCommon.insert();                       

                        

                    }

                    else if(insertUpdate)

                    {

                        ttsBegin;

                        toCommon = queryRunTo.getNo(1);

                        toCommon.selectForUpdate(true);

                        buf2Buf(fromCommon, toCommon);                        

                        toCommon.update();

                        ttsCommit;

                            

                    }

                }

                

            }

            

        }

        

        

 

    }

}

Correcting DB Sync errors in AX

Correcting DB sync errors:

Problem 1: Table Id conflict.

Lot of times we face the below error due to id conflicts during code migration. If this is the only error related to this table and you can’t help using sync option in AOT, follow the resolution step.

The SQL database has issued an error.

SQL error description: [Microsoft][SQL Server Native Client 11.0][SQL Server]There is already an object named 'DMFSTAGE' in the database.

SQL statement: CREATE TABLE "DBO".'DMFSTAGE'

Resolution: change the Table Id on SQLDictionary table

Select TABLEID,* from sqldictionary where name = 'DMFSTAGE'

Update SQLDICTIONARY

set tableid = 105523 -- this is table id from AOT

from SQLdictionary where tableid = 105519 –- this is table id from SQLDictionary

Problem 2: Field Id problem

Due to field addition/deletion and movement of code in different layers, you may face below errors. In that case, the original table could be made a copy on SQL and the original table then dropped. Later sync from the AOT that will create the table and then copy the data from staging table to the target table. If the columns are changed then you need to export the data in excel and work on filter as explained below.

Text  

Illegal data conversion from original field 'DMFSTAGE'.PACKAGECODE to 'DMFSTAGE'.nextMigrate: Unable to convert data types to anything but character field type (0 to 4).   

Illegal data conversion from original field 'DMFSTAGE'.SALESMANMOBILEPHONE to 'DMFSTAGE'.PurchasingDate: Unable to convert data types to anything but character field type (0 to 3).

Solution: In this case, you can perform steps 1 to 3 as mentioned below. Alternatively, correcting Fieldid on the SQLDictionary table can be performed. As a rule of thumb, the field id on AX should be copied/exist on the SQL dictionary table. I would prefer the backup and restore option always.

1.       Take backup of the error table

SELECT *
INTO tableCopy
FROM originalTable

2.     

drop table OriginalTable

3.  Perform DB sync from AX from the original Table

insert into OriginalTable select * from tableCopy

**Sometimes step 3 will not work because of the change of the fields sequence in different layers. For this use a column list in the above query. You can find all the columns of a table using below query. Migrate it to excel file and then do transpose and save it as CSV file. You will get list of columns with comma separated values and use it in the column list of above query.

select Column_name

from Information_schema.columns

where Table_name like  'DMFTAXBRANCHENTITY'

                

Record level security policy in AX 2012

XDS security in AX 2012 for HRM

Let us explore the standard AX HCM polices that are shipped OOB but there is no document available. This blog will also help to understand functional and technical overview of the record level security and legal entity level access to users.

Description:
A Security Policy is required to restrict access to Worker and Position Records based on the Legal Entity granted to the Human Resources Local Admin Role.
The Microsoft Dynamics AX 2012 Human Resources Module framework has been enhanced to provide greater flexibility and sustainability across the organization. The data model has also been completely revised to permit many of the core Human Resources (HR) tables to be shared and to enable new functionalities. In prior versions, users had to log in and out of companies to manage applicants, employees, positions, and employee information. These processes have been streamlined as the data is now shared across all legal entities.
Since HR Data is shared across legal entities, there is a concern that HR Local Administrator must only see the workers and positions for the Legal Entities that it’s responsible for (and not for all legal entities). To address this security concern, Security Policies must be customized and applied to the Human Resources Local Admin Role.
Target Role: Let’s consider the securable role for this policy would be ‘Human resource assistant’ (AOT: HcmHumanResourceAssistant). It is out of the box role that allows to access global workers and positions.

Solution:
1. Create a policy to restrict workers for the legal entities that the ‘Human resource assistant’ has access to.
This policy is already exposed in the AOT by Microsoft. Let us see how this policy works.
AOT-> Security-> Policies -> HcmWorkerLegalEntity

This policy restricts the user to view workers for the legal entity that is associated with the user’s permission.

Properties: Change the context type to Role Name and the role name should be selected as HcmHumanResourceAssistant.

Setup: Note that the help text says that the user should be granted access to legal entities. In order to do this, navigate to the user master (system admin – Users – User role fast tab ) and assign the user with the Human resource assistant role. The ‘Assign organization’ button will be enabled to associate legal entities or legal entities hierarchy for the user and role combination. Select the ‘Grant access to Specific organizational hierarchy’ and select the desired legal entity. In our case we are in the standard AX R3 demo data and I am restricting it for USMF.

Caution: If you are adding more than one role for the user, by default the role will have access to all legal entities. Even if you are using ‘system user’ you need to perform the assignment of organization in order to make the policy work. When we added system user to this user and all legal entities were granted access, the security policy would not work. It is because the XDS table formed during invocation considers union of allowed legal entities. We also have a work around described in the later part.

Now, let’s log in to test the security policy for the role. Aaren is employed to USRT, which should not be visible for the HR assistant.

Visibility for HR assistant:

Also the HR assistant is restricted to create worker for USMF legal entity.

Technical details:
The query contains the data source with filters to manage data that will be allowed for the user. The Security Policy will take care of applying the constraints with the use of the query. In most cases, we would be required to pass dynamic filtering of the primary table. In our case during runtime, we need the information of the legal entities tied to the current worker. In this particular case, the query contains a temp Db table that populates the data based on the invocation of XDS method. Depending on a call (RefreshFrequency) you can cache this table per user session. Also check the best practice stated my MS while creating query as it impacts performance. Have a look at the XDS method of the ‘MyLegalEntity’ table. It dynamically fills the temp table per session with the Legal entity info for the logged user.
Also note this part of the code, which selects the legal entities for all the roles associated with the user. Hence as a caution it was advised to restrict legal entities for all the roles associated with the user.

2. Create a policy to restrict the view of the departments of the legal entities the user is associated to.
The relation between department and organization structure is tied to legal entity.

This standard AX policy can be used for this purpose. It only allows the use of departments that are associated to the hierarchy in the organizational structure for the legal entities allowed for the user to access. While designing organizational structure, the organizational purpose, and linkage with the legal entities should be well thought.

In the demo data, USMF is not associated with the IT department and client services department. For more details on the organizational hierarchy check the best practice to design the hierarchy. In the subsequent screen the budgeting hierarchy for the departments is shown from the system user and the HR assistant perspective after enabling the security policy ‘HcmDepartmentLegalEntity’. This policy will also show the departments that are not linked with any legal entity in the hierarchy.

Legal entity
Department
Positions

Fig A: For system admin

Fig B: For HR assistant

Note that for the HR assistant the departments will be shown as blank nodes in the hierarchy form.
Technical details: The query used for this security policy consist a no exist join with a temp table HCMMyDepartmentsNoAccess that stores the department that does not have access to the legal entities data during runtime that has refresh frequency per session. Thus whenever the user is logged in to the system or when a session is created, this temp table is filled with the data thus resulting performance gain. The number of joins is reduced during run time.

3. Create a security policy for restricting access only to positions associated with legal entities in which the user has been granted access.
Since we have already restricted the departments, the positions falling under these departments should also be restricted. Standard AX security policy HCMPositionLegalEntity solves this purpose.

Let us assign to the HR assistant role in our previous considerations.
The IT manager position is part of IT Department which does not belong to USMF legal entity in the standard demo data. For the HR admin having access to USMF legal entity, should not be allowed to view the IT department’s position.

Using HR assistant role: cannot find relevant positions.

Technical details:
The positions that are linked to the workers that this HR assistant does not have access to will show unknown workers. In order to restrict the positions records based on employee, we can add the position tables to the constraint table of the first policy we enabled for workers. To add constraint table right click New -> add table by relation should be selected. The property ‘constraint table’ should be set to yes.

The query used for this security policy consist a exist join with a temp table HCMMyDepartments that stores the department data during runtime that has refresh frequency per session. Thus whenever the user is logged in to the system or when a session is created, this temp table is filled with the data thus resulting performance gain.

4. Restrict the employment record of the worker to the legal entities that the user is associated to.
The local HR assistant is responsible for the employment for USMF only. There may be workers having more than one employment role in multiple legal entities. The local HR should be restricted to view the employment of the associated legal entity only i.e. USMF in our example.
The standard AX policy for this is HcmEmploymentLegalEntity. Let’s enable this for the HR assistant role. Currently we have a worker employed in multiple companies as below.
User -> version tab-> Employment history

Currently the user is contractor in USMF, employee in INMF and GLSI legal entities. Now, let’s look from the HR assistant role. The employment details for other legal entities are not shown as seen in the below form.

Adding lookup method on Form Reference Group AX 2012

Reference Group Control lookups in AX 2012

With the addition of reference groups fields the AX forms including the dialog form gives a rich control called the FormReferenceGroupControl. With this post, I will show you how to add a reference field in the batch dialog form and add a filtered query controlled drop down.

I am going to show with a EcoResCategoryId field. The requirement is to filter out categories with Level 3 and present in the batch dialog dropdown.

 

Class Declaration:

 Declare the warehouse and category fields and dialog fields as below.

InventLocationId            inventLocationId;

EcoResCategoryId            EcoResCategoryId;

DialogField                 DialogInventLocationId,DialogEcoResCategoryId;

Dialog Method:

public Object dialog()

{

    dialog    dialog = new DialogRunbase("@ZON2996",this);

    DialogEcoResCategoryId   = dialog.addField(extendedTypeStr(EcoResCategoryId),"Category");

    DialogInventLocationId   = dialog.addField(extendedTypeStr(InventLocationId),"Warehouse");

    return dialog;

}

DialogPostRun method should be overridden to enable the lookup method of the fields

public void dialogPostRun(DialogRunbase _dialogloc)

{

    super(_dialogloc);

    _dialogloc.dialogForm();

     _dialogloc.dialogForm().formRun().controlMethodOverload(true);

    _dialogloc.dialogForm().formRun().controlMethodOverloadObject(this);

     _dialogloc.formRun().controlMethodOverload(true);

    _dialogloc.formRun().controlMethodOverloadObject(this);

}

Determine the field Id of the control from the dialog form. Personalizing will show you the below form.

From the below form we know that Fld1_1 is the field for the category. Also note that as it is reference group, a Name field is shown that should hold the category information in the string box.

Lookup:

We would need to create a lookup method for the above field as below.

void Fld1_1_lookup()

{

    Query     DropDownquery          = new Query();

    FormControl       frmSTr         = DialogEcoResCategoryId.dialog().formRun().controlCallingMethod();

//Use the SysReferenceTableLookup class instead of SystableLookup class for such reference group control

    SysReferenceTableLookup          sysTableLookup = SysReferenceTableLookup::newParameters(tableNum(EcoResCategory), frmSTr,true);

   

    QueryBuildDataSource     qbds;

    QueryBuildRange          qbr,qbr1;

    ;

    qbds = DropDownquery.addDataSource(tableNum(EcoResCategory));

    qbr  = qbds.addRange(fieldNum(EcoResCategory,Level));

    qbr.value(queryValue(3));

   

    sysTableLookup.addSelectionField(fieldNum(EcoResCategory, Name));

    sysTableLookup.addLookupField(fieldNum(EcoResCategory, ReciD));

    sysTableLookup.addLookupField(fieldNum(EcoResCategory, Name));

    sysTableLookup.addLookupField(fieldNum(EcoResCategory, Code));

    sysTableLookup.parmQuery(DropDownquery);

    sysTableLookup.performFormLookup();

}

AX 2012: Reference To the VS Projects not updated in AOT

Common error when using a class that runs on server and references a Visual studio DLL file.

I am trying to consume web service from external systems in AX. To do this, I have added the references in VS 2010 solution and that in turn is added to the AOT. I have set the deploy on client and server to yes.

When you add the solution to the AOT, the AX server will store the relevant DLL and its configuration file to the folder under the application as below. The highlighted part is the instance name.

C:\Program Files\Microsoft Dynamics AX\60\Server\Lexjet_DEV_AX\bin\VSAssemblies

When you use the reference to the web services in a class, which is very much simplified in AX 2012 as it gets stored under the VS references, you may want to execute the class always from server. In my case it is a batch job so it becomes mandatory for me.

But when my code tries to create a service client, it points out the configuration path to the client rather than the destination pointed above.

Second Problem with this path reference is that when you try updating a service in VS 2010 and update the project to AOT, only the server folder gets the latest artifacts of the project. Below is a screen that shows that the last modified date of the client folder is older than the one in server.

 

The deploy to client did not work and even though you run the class from server it takes the client’s path reference in the createServiceClient method.

Workaround:

Every time you add or update the service reference, you need to copy the configuration and DLL from server to the client folder.

This is a big overhead when you are developing integration scenarios and you tend to update the service reference again and again.

You also do not want to refer to the client’s directory in case of a batch job. This is a big pain when you are migrate your code to other environments.

Importing Vendor Into Ax 2012

Having all the relations of GAB structure changed, it became mandatory to write a script to import vendor data into Ax 2012. I will create a DirParty record and then associate it to vendor fields. The addresses can be attached to the vendor's PartyRecId. The use of views to record all the address fields help to initialize the linked tables.



static void importVendor(Args _args)
{
    CommaIO                     csvFile;
    container                   readCon;
    counter                     icount,inserted;
    Dialog                      dialog;
    DialogField                 dfFileName;
    DirPartyRecId               partyRecId,contactPartyRecid;
    Name                        name,contactName;
    VendTable                   vendtable;
    FileName                    fileName;
    str                         contactperson;
    DirPartyPostalAddressView   addressView;
    DirPartyContactInfoView     contactView;
    ContactPerson               contactpersonTable;
    LogisticsElectronicAddressMethodType enumType;


    DirParty                    dirParty;
    LogisticsPostalAddress      address;
    LogisticsElectronicAddress  logisticsElectronicAddress;
    BinData binData;
    str stringImage;
    inserted =0;
    #File


    dialog = new Dialog("Pick the file");
    dfFileName = dialog.addField(extendedTypeStr(FileNameOpen));
    dialog.filenameLookupFilter(["All files", #AllFiles]);


        if (dialog.run())
        {
             filename =  dfFileName.value();
        }




    csvFile = new CommaIO(filename, 'r');
    
   
    if (csvFile)
    {
        readCon = csvFile.read();
        ttsbegin;
        while (csvFile.status() == IO_Status::OK)
        {
            readCon = csvFile.read();
            icount++;
            if (readCon)
            {
                name = conPeek(readCon,2);
                partyRecId = DirPartyTable::createNew( DirPartyType::Organization, name).RecId;


                vendtable.clear();
                vendtable.initValue();
                vendtable.Party = partyRecId;
                vendtable.AccountNum = conPeek(readCon,1);
                vendtable.VendGroup  = conPeek(readCon,5);
                vendtable.Currency   = conPeek(readCon,6);
                //vendtable.Blocked    =
                vendtable.DlvMode    = conPeek(readCon,8);
                vendtable.PaymMode   = conPeek(readCon,9);
                ContactPerson = conPeek(readCon,12);
                if(contactperson != '')
                {
                    contactname = conPeek(readCon,12);
                    ContactPerson::findOrCreateNameParty(partyRecId,contactname);
                }
        
                vendtable.insert();


                address.CountryRegionId = strLRTrim(conPeek(readCon,14));
                //addressView.State = strLRTrim(conPeek(readCon,16));
                address.ZipCode = strLRTrim(conPeek(readCon,15));
                address.Street  = strLRTrim(conPeek(readCon,19));
               // address.county = strLRTrim(conPeek(readCon,17));
               address.City    = strLRTrim(conPeek(readCon,18));
                addressView.LocationName = name;
                addressView.IsPrimary = NoYes::Yes;
                addressView.Party = partyRecId;
                addressview.initFromPostalAddress(address);


                DirParty = DirParty::constructFromPartyRecId(addressView.Party );
                DirParty.createOrUpdatePostalAddress(addressView);


                contactView.LocationName = "Delivery email";
                contactView.Locator      = strLRTrim(conPeek(readCon,10));
                contactView.Type         = LogisticsElectronicAddressMethodType::Email;
                contactView.Party        = partyRecId;
                contactView.IsPrimary    = NoYes::Yes;
                dirParty.createOrUpdateContactInfo(contactView);


                contactView.LocationName = "Delivery Cellular Phone";
                contactView.Locator      = strLRTrim(conPeek(readCon,11));
                contactView.Type         = LogisticsElectronicAddressMethodType::Phone;
                contactView.Party        = partyRecId;
                contactView.IsPrimary    = NoYes::Yes;
                dirParty.createOrUpdateContactInfo(contactView);


                contactView.LocationName = "Delivery Phone";
                contactView.Locator      = strLRTrim(conPeek(readCon,3));
                contactView.Type         = LogisticsElectronicAddressMethodType::Phone;
                contactView.Party        = partyRecId;
                contactView.IsPrimary    = NoYes::Yes;
                dirParty.createOrUpdateContactInfo(contactView);


                contactView.LocationName = "Delivery Fax";
                contactView.Locator      = strLRTrim(conPeek(readCon,4));
                contactView.Type         = LogisticsElectronicAddressMethodType::Fax;
                contactView.Party        = partyRecId;
                contactView.IsPrimary    = NoYes::Yes;
                dirParty.createOrUpdateContactInfo(contactView);
                inserted++;
            }
        }
        ttsCommit;
    }
    info(strfmt("%1 records inserted out of %2",inserted,icount));
}

Ax2009 Reporting services installation Error out on iis

On the Box:

Server: windows 2008 server R2.

SQL 2008.

When installing Reporting Extensions for Ax 2009,the pre-requisite page shows that IIS is not installed even thou you may have installed it. In the log file,you will find that the installation fails at IIS.

For the workaround, install the following IIS components from Manage Your server-> Server Roles->role services.

Install the selected components and You wont be asked to install IIS pre-requisite.