We recently received a support case regarding the MO Scheduler utility in the MO Generator Suite.  The consultant reported that when attempting to reschedule an MO they received a SQL error:

Conversion failed when converting the varchar value ‘50.01’ to data type int.

Since all WilloWare code has custom error handling (we have our own window for displaying errors) it was immediately clear the error was coming from GP Manufacturing.

“50.01” is a Routing Sequence.  We set up a routing with a similar sequence, but the MO Scheduler ran fine.  We couldn’t duplicate the issue.  The usual suspects for an error like this are either a VBA customization or a SQL table trigger, but neither of those were present.

The consultant provided a SQL trace that showed the scheduling process was failing on a stored procedure called mmopUpdatePicklist.  It has three input parameters:

• MO Number
• First Routing Sequence
• End Routing Sequence

The trace also showed that “50.01” was the First Routing Sequence.  We changed our routing to make it the first sequence, ran MO Scheduler…and got the error!

Our code was calling the stored procedure through the manufacturing module, and it was (1) correctly finding the first and last sequences, and (2) passing everything in correctly.  We then executed the stored procedure in SQL Management Studio:

That also generated the error, so we knew it was not MO Scheduler causing the problem.

A trace of rescheduling an MO in the MO Entry window, which uses mmopUpdatePicklist but does not create an error, produced something odd.  The call to the stored procedure looked like this:

mmopUpdatePicklist “MO0196”, “-1”, “-1”

“-1”?  Those two fields are supposed on contain the first and last routing sequences.  In this case we expected it to look like this:

mmopUpdatePicklist “MO0196”, “50.01”, “900”,

Digging into the Manufacturing source code revealed that mmopUpdatePicklist is used in six different places in the code and five of them do NOT pass in the routing sequence, they pass in a hard-coded value of “-1”!  However, the Manual Scheduling window does use the First and Last Routing sequences.  We tried rescheduling from that window and got the same error:

Digging further, we investigated the mmopUpdatePicklist stored procedure and discovered that it is hard-coded to recognize “-1” as a magic value.

A “magic value” is a parameter you pass into code to make something happen, and it is a very bad idea.  In this case since the input parameters for mmopUpdatePicklist are MO Number, First Routing Sequence and Last Routing Sequence, you should expect those are the values used by the code.  Instead, it is using the magic value of “-1” to determine if it should schedule a specific sequence/range (the top case) or all sequences (the bottom case).  However, since the magic value of “-1” is an integer and FirstSeq is a string, the IF statement requires SQL Server to do an implicit data type conversion from string (varchar) to integer, and that means if you have routing sequences with letters, or decimal places, it will create an error because there is no way for it to compare “A001” to “-1”.

What would be preferable is to have the code always expect and use two inputted routing sequences.  If you give it the first and last it reschedules everything.  Or you could give it the 1st and 2nd, or 3rd and 7th to reschedule that range.  It would even be reasonable to allow passing in blanks to have it reschedule the entire MO.

If you have routing sequences with letters (or other characters) and you have encountered this issue, there is a relatively simple fix.  Just edit the stored procedure to replace -1 with ‘-1’, SQL will treat it as a string (varchar) and everything works fine.

Keywords: Manfacturing, Standard Cost, Revaluation, Replace Costs

A client recently encountered the error below after updating pending cost changes (using WilloWare’s Proposed Cost Update), then doing the Roll Up and Replace Costs routines on the Standard Cost Changes window in GP Manufacturing.

Problems were encountered while creating batch header information.  Revaluation aborted.

The Roll Up routine ran OK.  Clicking the Replace Costs button resulted in the error.

A Dexterity Script Log showed that the process stopped at a call to Init_GL_Post_Headers.  This manufacturing routine creates a Batch Header record and a Batch Activity record.

The Manufacturing Module creates Batch IDs in a format similar to this: dsmithGL_0.

The SY00500 (Batch Headers) table did not contain the Batch ID (i.e. dsmith GL_0) but it did exist in DYNAMICS..SY00800 (Batch Activity).

Further investigation found a SQL table trigger on SY00500 (Company Database) related to an auditing product.  The trigger was writing batch headers to a table belonging to the auditing product.

The conclusion was that as part of a test migration from Actual to Standard Cost, some bad data had been created in the inventory module.  This caused the first attempt to “Replace Costs” to fail.  The data issues were then fixed.

However, the audit software (trigger) had already captured the Batch Number and then failed to clear the record from its table because the Replace Costs posting routine failed.

Subsequent attempts to re-run “Replace Costs” caused Manufacturing to generate the same Batch Number (i.e. dsmithGL_0), and when it tried to add that record to the Batch Headers table the auditing trigger created a duplicate key error on its own table which in turn caused GP to fail to create the Batch Header record.

This case highlights the troublesome nature of SQL table triggers.  First, they can cause GP to fail with unhelpful error messages.  The GP application doesn’t know source of failure, so it doesn’t know how to respond to it.  Second, table triggers are often undocumented, and nobody remembers which tables have them, which makes troubleshooting complicated.

It is hard to be alive if it never existed in the first place.  D365 does not exist.   D365 is a marketing umbrella term for two completely separate online ERP software packages provided by Microsoft.  The two separate ERP packages were purchased by Microsoft back in 2000 and at that time were called Navision and Axapta.  These are now confusingly referred to as Business Central (BC) and Finance & Operations (F&O), both under the even more confusing D365.

Here is the straight scoop on common beliefs about D365 and GP.

D365 is the next version of GP. No.  D365 is two completely separate online ERP packages (see above) that are in no way related to each other or to Dynamics GP.

GP migration to D365 is simple.  Probably not.  It might be easy if you are a very simple, financials only Dynamics GP deployment, with no 3rd party products, no report modifications and no window modifications.  Even with all of those restrictions, you are still implementing a completely new, completely unrelated piece of software so there will not be a direct migration for everything.

Dynamics GP turns off soon.  Absolutely not.  We recently did a custom project for somebody still using GP 2010.  If you own your GP license, it does not “turn off” because “support” ends.  There is no rush to do anything.  If you feel there is a strong business case for implementing new ERP software you can take your time to make a careful decision.  Do you know what can shut off at any time?  Software that you pay for on a monthly basis.

D365 is my logical migration path. Maybe?  Since BC and F&O are in no way related to Dynamics GP or each other, you might as well consider all of your ERP options.  Why not also look at SAP, Oracle, Infor, Netsuite or others?

Dynamics GP is slow. No.  This is a resource issue.  GP can be as quick and snappy as anything else.  If it is slow look at resources (hardware).  Also make sure there isn’t a 3rd party product that is killing performance.

D365 can do everything GP does.  If you already asked questions to confirm that “D365” can do what you need, make sure you know if the “yes” refers to BC or F&O.  Honestly, F&O is amazing and if you can afford it, go for it, but know this will be a very big implementation.

We need ERP online.  Dynamics GP can easily be moved to Microsoft Azure.  If you are not familiar with what Azure is, just think “server that lives at Microsoft”.  What is cool about Azure is you can add more RAM and CPUs at any time.  Better performance is just a few clicks away, then a couple minutes later GP is even faster!

Microsoft support for GP is ending.  Yup.  How much support do you get from Microsoft?  If you need support who do you actually turn to?  What you need is a good Dynamics GP partner who is committed to helping you.  WilloWare is still developing for, and supporting our products on, every version of Dynamics GP from 2010 and up.

Lastly, a question that you can probably answer by this point:

Will my (3rd party product/Excel report/Web-store integration/etc) work with D365?  No, because D365 does not exist.  How about with BC or F&O?  Probably not, but the details are important:

• Is it “part of” GP?  Examples are: Modified windows, reports, 3rd party products, customizations.  NO.
• Is it external to GP and integrates data into GP.  MAYBE.  It depends on whether an integration into BC or F&O has already been created.
• Custom Reports (SSRS, Excel, other).  NO.  These depend on the specific database tables of the ERP system.