Purchase order invoice error after applying Feature Pack

Last month we applied the Feature pack to our Dynamics AX 2012 environment. While testing we found out that when a user clicks on the invoice button on any purchase order form an error is thrown. The error appers only when a normal user was clicking on the invoice button. For an admin there was no error. The error is shown below.

It shows that SQL is incomplete. There is a field missing before the IN keyworld. As there are multiple joins involved and also temp tables are present in the query there was no clear indication of which filed from which table was missing before the IN keyword..

So the first thing we did was to do a trace for the user and admin of the whole process to see whether the same code path is being used and what SQL is being generated. Below is the trace

On the left is user trace and on the right is admin trace. we can see in the call stack that once the incomplete SQL is generated, system throws an error and call stack is completely different from the admin one. The highlighted parts on the left indicate the difference in the sql statement being generated.

As the security framework for normal users kick in and generate a different sql we thought it was security related issue. So we gave full permission of all the tables involved to the user, but the issue remained.

The next step was to look at the table structure and contents of the temp tables. This is how it appeared in the SSMS.

As you can see that there is a field called INSTANCERELATIONTYPE. This field is used in tables that have supertype/subtype feature of AX implemented. Other fields indicated that the temp table was related to AgreementHeader table. This table has two sub type tables PurchAgreementHeade and SalesAgreementHeader. The assumption that these are the tables related to the temp table was confirmed by the ids of these table (4902, 4903) as shown by the user trace. All the tables have configuration key "TradeBlanketOrder".

In feature pack, field PurchAgreementHeader_PSN was added to VendInvoiceInfoTable. This field has the configuration key of PublicSector. Now when feature pack was applied this key was turned on by default and in our system the key TradeBlanketOrder is off. This turned out to be the root cause of the problem.

When a form is run, system joins all the tables specified in the relation or that are supertype/subtype tables to the query and as the configuration key for the TradeBlanketOrder was off, system could not access the PurchAgreementHeader table hence generating the incomplete SQL statement.

To fix the issue we could think of three possible solutions

• Disable the public sector configuration key.
• Enable the TradeBlanketOrder key.
• Change the configuration key on VendInvoiceInfoTable.PurchAgreementHeader_PSN to TradeBlanketOrder (as the field should only be used when this key is checked).

All three would fix the issue but we went for the second option.

Note: It is strange how AX silently omitted the field from the SQL statement insted of throwing the error. This should be done by AX instead of relying on the SQL server to do this job.

This posting is provided "AS IS" with no warranties. Use code at your own risk.