Microsoft Dynamics AX 2012 R3 CU8 - [Bug] Transfer Balances Into Budget Register Entry Reveals Ledger Data Corruption

During a Transfer balances into a Budget register entry, I received the following error.

Function DimensionStorage.addHierarchy has been incorrectly called.

Update has been canceled because of an error.

The error occurs at the following line in DimensionStorage\addHierarchy().

The root of the problem is data corruption in the GeneralJournalAccountEntry (GJAE) table.  Specifically, in some rare cases the LedgerDimension field refers to a DimensionAttributeValueCombination (DAVC) record that does not have an AccountStructure.  This appears to happen when a Default account is used in the UX rather than an appropriate LedgerDimensionType of Account.

For example, these GeneralJournalAccountEntry records refer to LedgerDimension 5637145331 (specific to this environment of course).

That DimensionAttributeValueCombination record refers to a Main account, but the AccountStructure field is 0 and the LedgerDimensionType field is 1, i.e. LedgerDimensionType::DefaultAccount.  

That absence of an AccountStructure is the root cause of the original error, and in fact this DAVC record with a LedgerDimensionType of 1 should never be referenced from the ledger at all.

Is it possible to locate all such records with the following SQL.

Note that whether coincidence or not, in every case of this I've detected the PostingType field on the GJAE record is 45, or LedgerPostingType::VendCashDisc.  I strongly suspect that KB3080798 (details at the bottom of this post) is the fix for this issue, though I have not yet tested it to confirm.

The repair of this data corruption is beyond the scope of this post.  In theory, the replacement of the defective LedgerDimension with the LedgerDimension of an equivalent but proper DAVC record is recommended, of course without changing the Main account to avoid impacting ledger balances.

KB 3080798

Vendor cash discounts posting without a financial dimension regardless of account structure limits

Product and version

Microsoft Dynamics AX 2012 R3

Fix type: Application hotfix

PROBLEM

You can post vendor cash discounts without a financial dimension even though you have an account structure specifically set up to prevent data entry errors.

DESCRIPTION OF CHANGE

The changes in the hotfix only address the defaulting order and still do not do the validation, and will not do the validation.

User Shadowing Directly from Online Users Form

Windows 8 and Windows Server 2012 R2 brought with it the reinstatement of RDP shadowing, with significant improvements such as multiple monitor support.

Windows 8.1 / Windows Server 2012 R2 - RDS Shadowing is back!

While shadowing is a great built-in solution for remote support, having to use the Server Manager to shadow the user is less than convenient.  Using the command line opens up programmatic solutions, but the server name and session ID of the user on that server must be known.

Can we add a Shadow button directly to the Online users form instead?

The server name is just the Computer name already available on the Online users form (the computer from which the user is connected, whether it be a terminal server, virtual desktop, or physical computer), The session ID, on the other hand, is not so readily available from within AX, but it can be fetched using the QUERY command (the ID column shown below).  Since the QUERY command has a /SERVER parameter, it even works on remote computers.



It seems like we have all the needed pieces to make a working solution, so it's time to put them all together.  Start by adding a new Shadow (control) button below the grid.



To keep the clicked method simple, just call a form method named clickedShadowControl() and do the heavy lifting there.



The first task is to call the QUERY command to fetch the session ID for the user on the server (remote desktop).  We'll construct the parameter string, create a System.Diagnostics.ProcessStartInfo object, making sure to capture the standard output of the process, and then start the process to execute the command.  If it doesn't terminate after 5 seconds, terminate it to be safe and abort.



The next task is to read the output from the QUERY command and locate the user's session ID.  The first line is the header, which allows us to locate the ID column position, and then the ID itself can be fetched from the user's data on the second line.



The last task is to call the MSTSC process with the server and now available session ID as parameters.



Finally, clicking the button launches the shadowing session in a Remote Desktop Connection window.



This is a proof of concept build that illustrates the possibilities for integrating shadowing directly into the Online users form within Dynamics AX.  Exercises such as error handling and security are left to the reader.

Microsoft Dynamics AX 2012: Enable Multi-select for User Role Security

If you have many companies and manage user role security, unless you're fortunate enough to Grant access to all organizations more often than not, you probably hate the SysSecRoleAssignOM form shown here.  To click company, Grant, company, Grant, company, Grant, company Grant, company Grant, grates the nerves, to put it nicely.  Certainly this form could multi-select.



Let's make it do just that.

First, drill down to the organizationTree control, and switch SingletonSelection to No, thus enabling multi-select in the tree of Available organization nodes.



Then, drill down to the Grid control, and switch MultiSelect to Yes, doing the same for the grid.




Next, in the addOrganization form method which responds to the Grant button, some code changes are required to loop through all of the selected tree nodes instead of acting on just the current node.  We'll use a for loop to iterate through all of the selected tree nodes, move the ttsbegin outside of that loop so all changes occur within the transaction scope, and then swap out the return statement for a continue statement to avoid adding nodes that already exist without aborting entirely.



The clicked method on the Revoke button requires the most work.  We'll use a for loop here also to iterate through all of the selected grid records, but we need a local OMUserRoleOrganization buffer to do the work, and also need to move the ttsbegin, ttscommit, and cleanup code outside of the loop.



Finally, we want the Grant button to enable appropriately.  We'll use one last for loop to iterate through all of the selected tree nodes, with only minor additional changes.

2012 R3 Dimension Framework Stack Trace and Debug Assert

While posting a general journal, I received the following stack trace on a Debug::assert() in class DimensionAttributeValueGroupValidation method doGetStatusFromStatusRecord().

[s] \Classes\DimensionAttributeValueGroupValidation\doGetStatusFromStatusRecord 57
[s] \Classes\DimensionAttributeValueGroupValidation\getStatusForRequest 17
[s] \Classes\DimensionAttributeValueGroupValidation\getStatus 29
[s] \Classes\DimensionValidation\getStatusForCombination 27
[s] \Classes\DimensionValidation\getStatusForRequest 29
[s] \Classes\DimensionValidation\getStatus 25
[s] \Classes\LedgerVoucherTransObject\check 140
[s] \Classes\LedgerVoucherTransObject\checkData 37
[s] \Classes\LedgerVoucher\addTrans 15
[s] \Classes\LedgerJournalTransUpdate\ledgerVoucherCheck 118
[s] \Classes\LedgerJournalCheckPost\checkJournal 412
[s] \Classes\LedgerJournalCheckPost\run 144
[c] \Classes\LedgerJournalCheck\main 49
[c] \Classes\FormFunctionButtonControl\Clicked
[c] \Forms\LedgerJournalTable\Designs\DesignList\CheckJournal\Methods\Clicked 22

The associated comment in the source code immediately suggests data corruption.  The DimensionAttributeValueGroupStatus record being validated has field IsValid set false (0), but none of the other fields (InvalidValueConstraintNotFound, InvalidValueIsSuspended, or InvalidValueIsTotal) have been set true (1) to indicate why the record is not valid.  According to the comment, such a record should never have been written.



Some quick SQL reveals that the issue is probably not limited to just the one record at hand.  Out of 22,843 records in the DimensionAttributeValueGroupStatus table, 47 records apparently share the same underlying problem.



Presuming for now that the IsValid is correctly false, the question is which of the Invalid fields should justify that status.

I started by joining the suspicious DimensionAttributeValueGroupStatus records back to DimensionAttributeValueCombination records to get a feel for what Main accounts and account structures were involved.



Conveniently, some of these are quite simple with only Main accounts and no additional dimension values.  For those records, I can tell immediately that they're not suspended, so InvalidValueIsSuspended of false seems correct.  It's also immediately obvious that they're not total account (they do exist, but these are not among them), so InvalidValueIsTotal of false also seems correct.

At this point that I discovered that these particular Main accounts have an account structure that requires the Business unit dimension and blank is not allowed, so now I know that IsValid of false is correct rather than just presuming it, but InvalidValueConstaintNotFound should be true in that case.

To test that theory, I selected another Main account sharing the account structure that requires Business unit, but for which no DimensionAttributeValueCombination or DimensionAttributeValueGroupStatus record yet exists.  In other words, I found a Main account that had never been used anywhere in the system without a Business unit, and attempted to use it for the first time in a General journal.

As expected, the validation code set InvalidValueConstraintNotFound to true in code.



An error was generated in the UX.



SQL confirms that the DimensionAttributeValueGroupStatus record was created with InvalidValueConstraintNotFound set true.



It's difficult to draw a firm conclusion from this investigation as to the root cause, but it I'm certain at this point that I have some DimensionAttributeValueGroupStatus records that should have InvalidValueConstraintNotFound set to true to justify IsValid set to false, and confidence that they are legitimately not valid for use and an understanding of why that is the case.

Perhaps this data corruption is left over from an earlier version of AX.  Since most of the Dimension framework records are not enabled for CreatedDateTime or ModifiedDateTime system fields, there is not enough information in the data to confirm such a suspicion.  Just based on RecId's alone, assuming that RecId's only increase over time, some of these records seem pretty recent and that's a concern for another day.

2012 Master Planning Floods Database Log of InventTable

When using Database log for table InventTable, i.e. Released products, Master planning can flood the Database log with Update events where Bills of materials are used.  Master planning recalculates the BomLevel field on InventTable in order to process items in the proper order, and it does this first by setting it to 0 and then later to its calculated value, thus producing 2 update events in the Database log for potentially many InventTable records.  It does this even when the final calculated value for BomLevel is unchanged from the previous value.

To resolve this, use the .skipDatabaseLog(true) and .skipEvents(true) methods for the InventTable table buffer in the BOMLevelCalc class.

2012 Unexpected Degeneration of Insert_Recordset Into TempDb Buffer

I recently discovered an interesting degeneration of insert_recordset into a TempDb buffer with huge performance implications.  While I refer only to insert_record in this post, the results apply equally to update_recordset and delete_recordset also.

When used properly, insert_recordset can yield huge performance gains over X++ code loops that execute individual insert statements because the kernel will generate and execute a single SQL statement that allows SQL Server to do the heavy lifting.  An important consideration is whether the target table of the insert_recordset statement has an override of the insert() method.  If so, the kernel will degenerate the insert_recordset into a code loop so that the insert() method can fire for each record.  This degeneration can be avoided with the .skipDataMethods(true) method on the table buffer prior to the insert_recordset command.  All of this is well documented and commonly understood by experienced developers.

This post illustrates a completely different case of degeneration of insert_recordset.

Here I've created a project with a single form, named MyTempDbForm, and a single class, named MyTempDbFormHelper.  In the form, add a data source, and reference it to TmpInventAge which is a TempDb table already available in the AOT.



Since I need to insert data into TmpInventAge, I've created a server static method on MyTempDbFormHelper class that accepts a TmpInventAge buffer by parameter and simply copies the ItemId field from all InventSum records into TmpInventAge records.



Then I call that method from the executeQuery() method on the form's data source, just prior to the super() call, passing the data source buffer by parameter.



When I open the MyTempDbForm, instead of executing a single SQL statement which should complete nearly instantly, the kernel executes more than 5,000 SQL statements!  This is easily seen with the Trace cockpit, as shown below.  Note the number of Database Calls and the long string of ::doInsert() calls on the TempDb table.



Since TmpInventAge does not override the insert() method, what caused the insert_recordset statement to degenerate into all of those individual SQL statements?

The problem it seems has to do entirely with the use of a client buffer (created by the form data source on the client tier) on the server.  I don't think it entirely makes sense, but with this understanding it can be easily fixed.

The solution is to create a server buffer of TmpInventAge, link it to the client buffer that was passed to the insertTempDbData method (using the linkPhysicalTableInstance() method), and execute the insert_recordset statement against that server buffer instead.



Again using the Trace cockpit shown below, notice the single Database Call and no more ::doInsert() calls, as well as the dramatic improvement in the Inclusive response time.

Microsoft Dynamics AX [multiple versions] - Slow DOCUREF Delete Performance

When deleting DocuRef records, i.e. Document handling, the response can be much slower than expected.  This problem exists in 2012 and goes back to versions 2009, 4.0, and I believe even 3.0.

To understand this issue, one must first understand the relationship between DocuRef and DocuValue records.  When a DocuRef record has an attached file that is being stored in the database, that file is not stored directly on the DocuRef record but is inserted into a DocuValue record instead, and referenced by the DocuRef record using the ValueRecId field.  These DocuRef records can be copied, either automatically by the system under certain conditions, or manually by using the copy/paste function of Document handling.  When DocuRef records are copied, the copied records continue to point at the same DocuValue record, which is not copied.  Thus, a DocuValue can be referenced by many DocuRef records.

The challenge when deleting a DocuRef record that references a DocuValue record is to determine whether or not the associated DocuValue record should also be deleted.  In other words, if during the delete of a DocuRef record, a check must be be made to determine if the associated DocuValue record is referenced by any other DocuRef records, in which case the DocuValue record cannot be deleted yet.  This is also why DocuValue records are not simply cascade deleted from DocuRef.  The many-to-one relationship must be respected.

In the delete() method of DocuRef shown below, a call is first made to this.existsAnotherValue(), and then the DocuValue record itself is fetched, followed finally by a check to see if such a record even exists, i.e. DocuValue.RecId.  Technically, this is backwards.



The existsAnotherValue() method executes a query looking for other DocuRef records that reference the same ValueRecId.  Note that it does this even if the ValueRecId is 0, which indicates that no DocuValue record is referenced at all.  When ValueRecId is 0, such a check is meaningless.



There are two optimizations that can be implemented here.

First, no such check for another referencing DocuRef record should be done when ValueRecId is 0, and a simple if statement to completely avoid the database query is a huge performance improvement.  This is especially true considering that likely most DocuRef records do not reference a DocuValue record at all and have 0 for ValueRecId.  Let that sink in for a moment.  Most DocuRef records probably share the ValueRecId of 0, which means the method .existsAnotherValue() can return true even when no DocuValue record even exists.

Second, when such a check is actually necessary, it only matters whether at least one such record exists, so the query hints firstOnly and firstFast minimize that effort.



Together, these two changes highly optimize the performance of the delete() method on the DocuRef table.

2012 Making Database Log Setup More Friendly

One of the problems with Database log setup is that the wizard shows the tables with their labels, and some of the tables have the same label as others and some have no label at all.

I find using the AOT names of the tables to be far more friendly than the labels.  Since I know the AOT names well, this removes all ambiguity and confusion from the wizard.

Achieving this only requires changing the function tableid2name(..) to tableid2pname(..) in the buildTableTree method on form SysDatabaseLogWizard.

Bug: 2012 R3 Form CustPaymEntry Allows Payment Date Change

When entering into an Accounts receivable payment journal, there are generally two methods for entering a customer payment.  The older method that's been around for many versions is to click Lines, select the customer account, enter the check number, check amount, and then use Functions and Settlements to match invoices and assign cash discounts.

The newer method is to use Enter customer payments which collects all of the necessary steps into one streamlined form.

One major problem with this form is that it allows the Payment date to be changed in cases where it should not be allowed.  For example, once settlements are selected, if any of those settlements include cash discounts or exchange rates, then changing the Payment date should no longer be allowed, and this is exactly how the Customer payment journal works.

The code responsible for this decision is found at Tables\LedgerJournalTrans\isDependantOnSettlement(), and a convenient remark explaining the behavior can be found there.

/// <remarks>

/// This dependency pertains to transaction date, cash discount, and exchange rates. It is used to

/// determine whether the user should be able to change the transaction date of a journal line.

/// </remarks>

This actually makes good sense.  Both cash discounts and exchange rates are date dependent, and so changing the transaction date of the customer payment after settlements are marked could be problematic at best.

The easy fix is to disable the date field altogether on the form, until a more comprehensive fix can be developed or until one is distributed from the product team.  An example of disabling the field is shown below, by setting allowEdit(false) for the TransDate field in the LedgerJournalTrans data source init method.

Bug: 2012 R3 CU10 SrsPrintMgmtController unpack() Stack Trace

Discovering that after upgrading AX 2012 R3 CU8 to R3 CU10, all reports based on SrsPrintMgmtController (pretty much all form letter documents such as Sales order invoice, packing slip, confirmation, etc.) throw a stack trace as follows.

[c]    \Classes\SrsPrintMgmtController\unpack 12
[c]    \Classes\xSysLastValue\getLast 29
[c]    \Classes\SysOperationController\loadFromSysLastValue 29
[c]    \Classes\SrsReportRunController\loadFromSysLastValue 7
[c]    \Classes\SysOperationController\getDataContractInfoObjects 10
[c]    \Classes\SrsReportRunController\parmReportContract 14
[c]    \Classes\SrsPrintMgmtController\init 18
[c]    \Classes\SrsPrintMgmtFormLetterController\init 16
[c]    \Classes\SrsPrintMgmtFormLetterController\startOperation 12
[c]    \Classes\SalesInvoiceController\main 99
[c]    \Classes\xMenuFunction\run 
[c]    \Classes\MenuFunction\run 85
[c]    \Forms\CustInvoiceJournal\Designs\DesignList\SalesInvoiceOriginal\Methods\Clicked 46

The problem turns out to be that this class previously did not have pack() or unpack() methods at all.  Normally when a class that persists state into SysLastValue changes its #CurrentVersion number, the pack() and unpack() methods are modified to accommodate existing SysLastValue records so that no stack trace occurs.  In this case since the class was not previously persisting any state in SysLastValue, it cannot compare the packed state version and throws accordingly.

The easy fix is to delete all SysLastValue records relating to reports with controllers that extend SrsPrintMgmtController.  Normally this is not required for a cumulative update (CU) and is only recommended for a major version upgrade, but CU10 seems to be the exception here.  I have no identified the individual hotfix responsible for this change, so in theory this problem could surface from as much as a single applied hotfix.

Here is a brief list of common (not exhaustive) classes that extend SrsPrintMgmtController and need their SysLastValue persisted state cleared in CU10.

AgreementConfirmController
FreeTextInvoiceController
PurchPackingSlipController
PurchPurchaseOrderController
SalesConfirmController
SalesInvoiceController
SalesPackingSlipController
WMSPickingList_OrderPick

Bug: 2012 R3 CU10 SalesInvoice SSRS Report Stack Trace Using Centralized AR

Discovering that trying to generate a SalesInvoice report in R3 CU10 where the invoice is settled from another company, i.e. centralized AR, throws the following error.  It would seem this could happen with prepayments, but in my case I was merely trying to reprint an old invoice that had already been paid, i.e. settled, from another company using centralized AR.

Cannot create a record in Show invoice (SalesInvoiceTmp).
Insert operations are not allowed across companies. Please use changecompany keyword to change the current company before inserting the record.

The problem stems from code in Classes\SalesInvoiceDPBase\printPrepayment() where a changecompany(offsetcompany) is executed and then later a call is made to Classes\SalesInvoiceDP\insertIntoSalesInvoiceTmp, which fails due to the incorrect company context.  It appears KB3070894 is involved, i.e. "Prepayment applied to a sales order is not printed on the sales order invoice"

2012 R3 KB3062099 Defective?

I've run into an interesting problem with KB3062099, which came on board for us with CU10..  The description reads "Wave attributes are not available or cannot be selected in Work template query, criteria dropdown".

The hotfix changes the following classes in a particular way.

Classes\SysLookup\lookupTableFieldRelation
Classes\SysTableRelation\findRelatedQueryFields
Classes\SysTableRelation\findRelation

The change in each case was the removal of the global function Global::fieldExt2Id(..) from a comparison of field ID's.

It wasn't immediately clear to me why this mattered and broke some lookups/dropdowns on the SysQueryForm, i.e. the range grid after clicking the Select button on most query forms.  Extended field ID's are a little strange it seems.  Normally field ID's are of the range 1-65535 within a table, with numbers 65000 and higher being reserved for system fields like RecId, etc.  When the field is an array, the high order word represents the index into the array for a specific field within that array.  However, there seems to be some sloppiness under the hood with respect to how this works.

In the Developer Network article at https://msdn.microsoft.com/en-us/library/aa630118.aspx, I found the following explanation.

 "Extended field IDs are used to refer to a particular field within a field array. An extended field ID's array index is packed in the high word, and the actual field ID is packed in the low word as shown in the following figure..  Non-array fields also have an array index and are treated as an array field that contains only one element, meaning that their array index is 1. This index can hold the value 0 or 1. The same field can have two different field IDs, one being 1<<16 bigger than the other. These two IDs can be used interchangeably, and the system processes them as if they were identical.  It is best practice to use the fieldExt2Id method to remove the array index part of a field ID, if it is not needed."

So my field number 60013 can also be found as 125548 sometimes, apparently at random.  Unfortunately, the article is clearly wrong in the statement that the system handles both identically.  In the classes above, a simple integer to integer comparison is made between a found field ID in a table relation and an expected field ID from a query, and 60013 does not equal 125548 strictly speaking.  It used to work when fieldExt2Id was used as part of that comparison, but now it is broken.

As a result, some lookups/dropdowns are now broken.

From the hotfix description, I imagine this was done in order to "fix" the Wave attributes lookup/dropdown, which is likely an array given the change that was made.

Master Planning Performance - the Statistics Solution

Since upgrading to Dynamics AX 2012 R3 CU8 in May of 2015, the performance of our multi-company master planning runs at night has been wildly sporadic.  In the best case, the total run time across all companies would average 200 plus/minus 10 minutes or so.  Frequently one company's master planning execution would spike from 15 to 45 or even 60 minutes.  This tended to happen in manufacturing companies, and it seemed more likely to happen in master plans using sales forecasting.

Here's a quick graph of total minutes of execution over the last 90 days or so.




Along the way, we tried upgrading from SQL Server 2012 to SQL Server 2014, and further to SQL Server 2014 SP1 CU3 and finally CU4, to no avail.  In some cases, a strategic DBCC FREEPROCCACHE would correct poorly compiled queries, but not always, and not reliably.

The solution turned out to be a problem with the STATISTICS of the master planning tables.  Since a master plan regeneration can effectively delete and re-insert virtually every record in these tables, it makes some sense that the statistics on these tables could get out of whack so easily and regularly.  Note that we were already using trace flag 2371, and enabled the PARTITION and DATAAREAID literals in our environment immediately after upgrading to 2012 R3.

What worked for us was to setup a SQL Server Agent job to execute the following SQL every 5 minutes throughout the duration of the master planning execution.  All together they take about 15 seconds at our scale.

UPDATE STATISTICS dbo.REQPLANVERSION;
UPDATE STATISTICS dbo.REQPO;
UPDATE STATISTICS dbo.REQROUTE;
UPDATE STATISTICS dbo.REQTRANS;
UPDATE STATISTICS dbo.REQTRANSCOV;
UPDATE STATISTICS dbo.REQUNSCHEDULEDORDERS;

To give some idea of the scale and distribution of our data, here's a record count across some of our companies for the REQTRANS table, sorted from highest to lowest.  It seems like an ideal case for STATISTICS to matter.

[Number of records]
546852
153113
123262
114029
89341
70635
63133
62812
41938
33191
18693
17601
8307
4098
4051
3077
1409
1383
1290
6