Inventory Closing and Adjustment

After writing a report which shows inventory transaction cost as of a specific date we had a small issue where very occasionally certain transactions were showing double cost.  After some poking around I found this post:

http://fedotenko.info/?page_id=31

The developer goes into depth about the procedure conceptually, technically, and differences between AX 4.0 and 2009 costing.  It is the very best explanation of the inventory costing feature in AX and saves many many hours of reading and interpreting code to understand.

Physical remaining quantity and total quantity must have the same sign

When posting a production report as finished journal you may receive this error,

Physical remaining quantity and total quantity must have the same sign.

It is because the remaining quantity on the production order is incorrect.

The quantity should never be negative.  I think it happens after running estimation incorrectly.  Run this job and it will fix the quantity.

static void fixProdNegQty(Args _args)
{
    ProdTable               prodTable;
    ;
    ttsbegin;

    select firstOnly forUpdate prodTable
    where prodTable.ProdId == "W0024381"
       && prodTable.RemainInventPhysical < 0;

    if (prodTable.RecId)
    {
        prodTable.RemainInventPhysical = 0;
        prodTable.modifiedField(fieldnum(ProdTable, RemainInventPhysical));
        prodTable.update();

        info("Updated");
    }

    ttscommit;
}

You can't have unallocated cost on a planning item

When using the functionality provided by Process Manufacturing by ToIncrease occasionally we get the following error when running inventory close and adjustments.

You can't have unallocated cost on a planning item

The error always occurs when it is trying to pass an adjustment through a production order ("Batch order").  Every other month we encounter this issue so we customized it to tell us the production order number.  In \Classes\InventCostItemDim.addFormulaKeyAdjustment() add "info(_inventTrans.TransRefId);" just before "throw error("@PRO997");".

Through experience we have found that either the co/by product allocation does not total to 100% and the ProdBOM.pmfAllocationPct needs updating or that the received or purchased transactions for the production order were somehow set to value closed when they should still be open.  Running a SQL statement similar to this will set the value to open, allowing the adjustment to pass to those transactions.

update InventTrans

set ValueOpen = 1

where dataAreaId = '510'

and TransRefID = 'W0018583'

and StatusReceipt = 1

Offset voucher does not exist in account ____.

When posting a ledger journal you may receive:

Whenever open accounts payable or receivable transactions are selected a corresponding record in SpecTrans is created, which just holds the ledger journal line record ID and the open transaction record ID.

1. Open the ledger journal
2. Select the ledger journal line with the issue
3. Click Inquiries > Marked for settlement
   This screen shows the items marked to be settled (paid) by the current line.
4. Use the record ID from your ledger journal line with the error to filter on SpecRecID in the table browser.
5. Go through each transaction and compare to the Marked for settlement list.  The record which is missing is pointing to a VendTransOpen or a CustTransOpen record which somehow does not exist anymore (RefTableID and RefRecID).  At this point, you can either correct the RefRecID to point to the correct transaction if it still exists, or you can delete the transaction.

Writing Quick Jobs/Exports

After getting many requests for lists, reports, exports I wrote a quick add-in script to give me a template for jobs.  With a little code you can add another template to the IDE which will allow you to easily write an export to CSV (Excel) with a dialog asking for parameters as well as a progress bar.

Go to /Classes/xppSource
Add a new method:

Source jobExport(
    SysElementName          name,
    boolean                 progressBar,
    boolean                 dialog
    )
{
    ;
    source += strfmt('static void %1(Args _args)', name) + #newLine;
    source += #methodStart;
    indentLevel = #defaultIndentColumns;
    source += this.indent() + '#File' + #newline;
    source += this.indent() + '#WinAPI' + #newline;
    source += this.indent() + 'CommaIO                 file;' + #newline;
    source += this.indent() + #string + '                     fileName = strfmt("%1\\\\%2_%3.csv", WinAPI::getFolderPath(#CSIDL_DESKTOP), funcname(), curExt());' + #newline;

    if (progressBar)
        source += this.indent() + 'SysOperationProgress    progress;' + #newline;
    if (dialog)
    {
        source += this.indent() + #newline;
        source += this.indent() + 'Dialog                  dialog;' + #newline;
        source += this.indent() + 'DialogField             dfStartDate, dfEndDate;' + #newline;
        source += this.indent() + 'StartDate               startDate;' + #newline;
        source += this.indent() + 'EndDate                 endDate;' + #newline;
    }

    source += this.indent() + ';' + #newline;
    source += this.indent() + 'setPrefix(funcName());' + #newline;
    source += this.indent() + #newline;

    if (dialog)
    {
        source += this.indent() + 'dialog = new Dialog(funcname());' + #newline;
        source += this.indent() + 'dfStartDate = dialog.addField(typeid(StartDate));' + #newline;
        source += this.indent() + 'dfEndDate   = dialog.addFieldValue(typeid(EndDate), systemDateGet());' + #newline;
        source += this.indent() + 'if (!dialog.run() || !dfStartDate.value() || !dfEndDate.value())' + #newline;
        source += this.indent() + strrep(' ', #defaultIndentColumns) + 'return;' + #newline;
        source += this.indent() + '    startDate = dfStartDate.value();' + #newline;
        source += this.indent() + '    endDate = dfEndDate.value();' + #newline;
    }

    // Create and initalized the file
    source += this.indent() + 'new FileIOPermission(fileName, #io_Write).assert();' + #newline;
    source += this.indent() + 'file = new CommaIO(fileName, #io_write);' + #newline;
    source += this.indent() + 'if (!file)' + #newline;
    source += this.indent() + strrep(' ', #defaultIndentColumns) + 'throw error("@SYS74299");' + #newline;
    source += this.indent() + #newline;
    source += this.indent() + '// File headings' + #newline;
    source += this.indent() + 'file.write(funcName());' + #newline;
    source += this.indent() + 'file.write(\' \');' + #newline;
    source += this.indent() + 'file.write("","",""); // Column headings' + #newline;
    source += this.indent() + #newline;

    // Progress indicator
    if (progressBar)
    {
        source += this.indent() + '// Initialize progress indicator' + #newline;
        source += this.indent() + 'progress = new SysOperationProgress();' + #newline;
        source += this.indent() + 'progress.setTotal((select count(RecId) from common).RecId);' + #newline;
        source += this.indent() + 'progress.setCaption(funcName());' + #newline;
        source += this.indent() + #newline;
    }

    // Loop
    source += this.indent() + 'while select common' + #newline;
    this.beginBlock();
    if (progressBar)
        source += this.indent() + 'progress.setText(common.caption());' + #newline;
    source += this.indent() + 'file.write(common.RecId);' + #newline;
    if (progressBar)
        source += this.indent() + 'progress.incCount();' + #newline;
    this.endBlock();

    if (progressBar)
        source += this.indent() + 'progress.kill();' + #newline;
    source += this.indent() + 'WinAPI::shellExecute("\\\"" + fileName + "\\\"");' + #newline;
    source += this.indent() + 'info(fileName);' + #newline;
    source += #methodEnd;
    return source;
}

Go to /Classes/EditorScripts
Add a new method:

void template_method_exportJob(Editor editor)
{
    xppSource       xppSource       = new xppSource();
    Dialog          dialog          = new Dialog("@SYS69534");
    DialogField     dlgName         = dialog.addField(typeid(SysElementName),"Job name");
    DialogField     dlgDialog       = dialog.addField(typeid(NoYesId),"@SYS24736");
    DialogField     dlgProgress     = dialog.addField(typeid(NoYesId),"@SYS94935", "@SYS89247");
    Source          template;
    ;
    if (dialog.run())
    {
        template = xppSource.jobExport(dlgName.value(), dlgProgress.value(), dlgDialog.value());

        editor.insertLines(template);
    }
}

Security Rights

We all know that AX never came with enough Security reports.  Security is complex in AX, if someone asks a functional question, "Who has access to view fixed assets?" I've found it is not as simple as looking...instead you find yourself answering a dozen questions to answer just the one simple one...Who belongs to which groups?  Which groups have access?  What type of access - View, Edit, Delete?  Do they have access to the form as well as the table?  Has someone else in my team modified security recently?

This is a job which answers the question from above by iterating through groups and users.

static void listSecurityRights_AssetTable(Args _args)
{
    TableId             tableId;
    DictTable           dictTable;
    DictField           dictField;
    DictSecurityKey     dictSecurityKey;
    SecurityKeySet      securitySet;
    UserGroupInfo       userGroupInfo;
    UserInfo            userInfo;
    AccessType          access;
    AccessType          menuAccess;
    ;
    setPrefix(funcname());

    info('AssetTable - Access to Fixed Assets');

    while select id, name from userGroupInfo
    {
        setPrefix("@SYS26106");

        securitySet = new SecurityKeySet();
        securitySet.loadGroupRights(userGroupInfo.id, '');

        access = securitySet.tableAccess(tablenum(AssetTable));
        menuAccess = securitySet.secureNodeAccess(menuItemDisplayStr(AssetTable), UtilElementType::DisplayTool);

        if (menuAccess < access)
            access = menuAccess;

        if (access > AccessType::NoAccess)
            info(strfmt("Group %2 - %1 can %3", userGroupInfo.name, userGroupInfo.id, access));
    }

    while select id, name from userInfo
    where userInfo.enable == true
    {
        setPrefix("@SYS25412");

        securitySet = new SecurityKeySet();
        securitySet.loadUserRights(userInfo.id, '');
        access = securitySet.tableAccess(tablenum(AssetTable));
        menuAccess = securitySet.secureNodeAccess(menuItemDisplayStr(AssetTable), UtilElementType::DisplayTool);

        if (menuAccess < access)
            access = menuAccess;

        if (access > AccessType::NoAccess)
            info(strfmt("User %2 - %1 can %3",  userInfo.name, userInfo.id, access));
    }
}

What?  You tried the job and it took forever to run.  Well yeah!  It uses the same method of accessing security as the slow User group permissions form.  How do we solve that?  Another faster job accessing tables which are, well largely undocumented.  The documentation describes the AccessRightsList table:

"Use of this table might lead to an Elevation of Privileges attack or a Denial of Service attack. The Application Object Server (AOS) authorizes each create, update, or delete action on the table by confirming that the current user has permission to perform the requested operation on that table. If the user who initiates the operation is not authorized to perform the operation, an exception will be thrown."

Well it won't hurt to take a peek!  This code actually does the same thing within seconds.

static void listSecurityRights_ElementName_aaaaaaa(Args _args)
{
    AccessRightsList AccessRightsList;
    ;
    while select accessRightsList
    where accessRightsList.elementName == 'CustTable'
    {
        if (accessRightsList.accessType > accessType::NoAccess)
            info(accessRightsList.groupId + ' ' + enum2str(accessRightsList.accessType));
    }
}

Bon appetit!

List AOT Objects

There is a very easy way to get a list of AOT objects using Microsoft Dynamics AX 4.0 without having to write a job iterating over project nodes.

Tools > Development tools > Application objects > Application objects

This form has a dump of everything...it is essentially the UtilElements "table" that the cross reference tables (xRef*) reference. Except this is not a table existing in SQL Server, instead it exists in the memory of the AOS.  The documentation declares "This method enables you to create, read, update, and delete X++ code and metadata." - although the table is not a method this does provide a little insight.  This table is queried by methods such as hasSecuritykeyAccess.

Take some caution when using the form...it acts like a temporary table and if you add too many filters AX will cease up.  I commonly use the form to list projects in one environment and compare them to the list in another by filtering on layer = cu?,us? and record type = SharedProject.

This article only applies to Microsoft Dynamics AX 4.0.

Parameters - When are those records created?

AX seems to create parameters either when the data area is created or when the parameter's form is opened.  So what happens when the parameter table has no form and AX has not created the record...or maybe you had some ambitious person delete the parameter records from the table?  The situation can be remedied using a simple job.  For example this one creates the alert parameter.

static void createEventParameter(Args _args)
{
    EventParameters parameter;
    ;
    Company::createParameter(parameter);
}

Table & Field Numbers - SQLDICTIONARY

Ever wondered how to lookup a table ID using a SQL statement or how tableid2str works?  The hidden table SQLDICTIONARY has the field and table numbers from the shown in the AOT.  It also includes the size and type of the columns.

For the anal retentive you can update data in this table to reorder the columns in the AOT.  They are ordered by table ID and field ID.  Or even better, just create the fields in the order you want them and don't bother modifying a hidden system table.

Seldom when copying data from one environment to another, but not moving the code table IDs and/or field IDs (sometimes added fields were added in different order) were different.  Synchronization fails for those tables.  I have found three ways to remedy it.  Other times you may see a SQL error message when opening the form for the table.

Case A: Similar Columns with different IDs - Offhours
AX is confused because although field MyNewField exists as a date in SQL Server the field with that ID in the SQLDICTIONARY table is a different name, or even a different field type.  The error message is slightly different but both can be solved this way...

1. Go to SQL and rename the table, i.e. CustTable_OLD
2. In AX perform compile and synchronize on just the table, i.e. CustTable.  If you do a full compile you run a small risk that the renamed table in SQL will be deleted
3. Write an INSERT INTO...FROM statement to bring the data back into the table (RecIDs and all) - it will not cause an issue with record IDs.
4. Delete the previous table, i.e. CustTable_OLD

Case B: Similar Columns with Different IDs - Online
Sometimes you need to fix the table quick and without any delay.  I have in the past been able to change the field or table IDs by modifying the records in the SQLDICTIONARY table.  Just make sure that they are unique and agree with the layer rules, for example IDs coming from the CUS and CUP layers begin at 40000.  This should not require you to restart - just recompile and synchronize the table.

Case C: Development
Delete the table!  you have lost all the data but it is the easiest and fastest way to fix the issue.

Copying AX Environments

After making so many environment copies I like to think I have it down to a science. I have a program which runs SQL scripts from a directory. Here are a few things that I do:

• Disable all users
• Clear the user login history
• Clear the pending outgoing emails
• Remove all alerts
• Delete batches
• Change paths of templates and document handling
• Set developer compilation options
• Delete print archive

Add-ons:

• Clear or secure payroll data and social security numbers
• Change BottomLine Create!form report parameters

What are the things you do to "initialize" a copied environment?

User is not authorized to insert a record in table SYSSETUPLOG

While doing a routine upgrade of a third-party add-on for Dynamics AX 4.0 I got the following error:

User '____' is not authorized to insert a record in table 'SYSSETUPLOG'. Request denied.

The SysSetupLog table, labeled "Installation information," stores application and kernel version information as well as the status of the System Upgrade Checklist. Therefore it is vital that the AOS service account as well as certain administrators have access to the table. When updating a third-party add-on the version number was changed (a key in that table) thereby triggering the system upgrade checklist. Unfortunately nobody had access to update the checklist items.

Although the cause can not be ascertained this job will allow anyone in the built-in administrators group to update the table.

static void updateAdminGroup_AllowChangeChecklist(Args _args)
{
 #Admin
 SecurityKeySet  securitySet;
 UserGroupInfo   userGroupInfo;
 UserInfo        userInfo;
 ;
 setPrefix(funcName());

 securitySet = new SecurityKeySet();
 securitySet.loadGroupRights(#AdminUserGroup, '');
 securitySet.tableAccess(tablenum(SysSetupLog), AccessType::Delete);
 securitySet.tableAccess(tablenum(SysSecurityFormTable), AccessType::Delete);
 xAccessRightsList::saveSecurityRights(securitySet.pack(), userGroupInfo.id, '');
}