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.

Keywords: Manufacturing, Item Delete, Item-Site Delete, Site Delete

You may have tried to clean up data in GP and were prevented from removing an unused or obsolete Item, Item-Site or Site because of a warning message from Dynamics GP Manufacturing such as:

“This site is used in Manufacturing, and can’t be deleted.”

“This item is a component on one or more bills of material. It can’t be deleted until you remove it from those bills of materials.”

If you are trying to remove an Item-Site record, in addition to controls from the Inventory Module (such as there cannot be inventory quantities of the Item), the Manufacturing Module can prevent the delete.

If you are trying to delete an Item-Site, the Manufacturing Module checks the following tables:

• Work Orders (WO010032)-all MO Statuses except Hold/Canceled, those are ignored, which means the Item-Site combination can be on an MO if the Status is Hold or Canceled.
• Picklists (PK010033)-unless the MO is Hold/Canceled.
• BOM Lines (BM010115)
• Pick Doc Line (MOP1210)-unless the MO is Hold/Canceled.

If you are trying to delete a Site, in addition to the tables above, it also checks:

• Sales Preferences (ISSP0201)
• Scheduling Preferences (SP010016)
• MPS Lines (MF010130)
• QA Preferences (QAPS0032)

Deleting an Item checks the following:

• Work Orders (WO010032)-all MO Statuses
• Picklists (PK010033)- all MO Statuses
• BOM Lines (BM010115) – parent or child item

A significant hurdle to removing an Item-Site or Site is the fact that Complete and Closed Manufacturing transactions exist that have the Item-Site or Site.

MFG Data Archive can move historical manufacturing data to an archive company database and REMOVE those same records from the live company.  The complete list of tables covered by MFG Data Archive is available at the link below.

Archived Tables (willoware.com)

Additional benefits of archiving data are:

• Faster lookups
• Faster reports
• Faster posting
• Faster backups

Read more about MFG Data Archive here.

Keywords: Prorate Setup Time, Prorate Quantity, Standard Cost (FIFO/LIFO Periodic), Actual Cost (FIFO/LIFO Perpetual), Backflush

Summary

When Prorate Setup Time is marked and Setup Labor is backflushed, the MO Receipt applies an incorrect amount of Setup Cost to each piece.

Overview

The online help for Costing Preference Defaults has the following to say about Prorate Setup Time:

Prorate Setup Time

Mark to distribute setup costs for standard cost items, and then select the quantity to use for calculating setup costs for standard cost items.

• • Average Quantity – The setup costs of an item (setup time multiplied by setup labor rate) will be divided by the average quantity of the item. You specify the average quantity of an item in the Item Engineering Data window. 
  • Standard Quantity – The setup costs of an item will be divided by the standard quantity of the item. You specify the standard item quantity in the Standard Cost Changes window. 

Standard Quantity can only be set on the Standard Cost Changes window, so it necessarily can only be used for Standard Cost items.  Average Quantity is set on the Item Engineering window.

The documentation for the Standard Cost Changes window says this about Standard Quantity:

If the item is a finished good, enter a standard quantity for finished good. When you set up preferences for how setup time is prorated for standard cost items, you can choose to use either the average quantity or the standard quantity.

And the documentation for the Item Engineering window says this about Average Quantity:

Optional. The Average Quantity is the default manufacturing order quantity, and is used in calculations if setup costs of standard cost items are prorated by the average quantity.

The only other reference to Setup Costs is on the MO Receipt Entry window.

Use all remaining setup costs

Mark this option to apply all remaining labor and machine setup costs – regardless of whether the labor and machine costs are marked to backflush or not – to the manufacturing order receipt. Setup costs that were previously applied to posted receipts will be deducted from the total setup costs for the manufacturing order. The default setting was set up in the Manufacturing Order Preference Defaults window, but you can change it when entering a manufacturing order. This option is not available if you are reversing a manufacturing order receipt.

Does all of this sound like the Prorate Setup Costs setting, and Standard/Average Quantity, only impact Standard Cost Rollup/Revaluation?

These settings also impact cost CONSUMPTION when backflushing, which with Actual Costing can significantly under-cost the finished good.  With Standard Cost it can result in a Variance.

Example

NOTE: everything below ONLY occurs if the Prorate Setup Time checkbox is marked.

CHAIR-ACTUAL is an actual cost item.  Item Engineering has Average Quantity of 20.

The Routing has one sequence with 1-hour of Setup and 1-hour per piece of Labor Time.  The Labor Cost for both Labor Codes is $40/hour.  Auto-Backflush Labor is marked.

We’ve created an MO for 24 and received one piece.  The Production Costs Labor field is $1000, which is correct.

• Setup: 1 hour * $40/hour = $40
• Labor: 24 pieces * 1 hour * $40/hour = $960
• Total: $40 + $960 = $1000

We’ve received one piece and backflushed labor for $40.08.

• Setup: We would expect to consume 1/24th of the Setup Cost.  This is one hour of Setup Cost divided by the End Quantity on the MO.  This should be $1.66667
• Labor: We made one piece, which should take one hour, so this should be $40.
• Total Labor should be $41.66667 not $40.08.

What is actually happening is the Prorate Setup setting and the Average Quantity are causing the MO Receipt to consume 1/20th of the Setup Cost per piece (remember the Average Quantity setting above?).

• Setup: (1 hour * $40/hour) / 20 = $2.  So, our starting point for consuming Setup Time is $2 instead of $40.  Manufacturing then takes 1/24th of that (it divides by the MO End Quantity), which results in $0.08333 per piece.
• Labor: this calculation is correct, which adds $40 in labor.
• Total: $0.08333 + $40 = $40.08 (rounded to 2-decimals).

The MO Variance screen now shows the numbers after received 23 of 24 pieces.  The total labor consumed is now $921.91, which is 23  * $40.08333.

When we receive the final piece, we will have either (1) undervalued the actual cost inventory, or (2) created a cost variance with standard cost inventory.

Standard Cost

This is the MO Variance for an MO for 24 of CHAIR (standard cost item).  The Routing has the same setup described above.  Note that the Cost Put into WIP ($42) is not what we’d expect (and it is different than what happened with the actual cost item).  In this case it is used 1/20th of the Setup Time per piece and is ignoring the MO End Quantity.

After receiving 23 of 24 pieces, we can see that it is using $42/piece.

• $42 * 23 = $966

And finally, with the MO complete and all pieces received, we can see that the Costs Put into WIP holds $8 more than Standard.  If we had produced exactly 20 pieces the math would have been correct, if we produced much more than 20 the variance would be even larger.

Conclusion

With Actual Cost items the calculation is wrong and significantly under applies Setup Cost.  With Standard Cost items there might be a weak argument in favor of always adding Setup Cost at the prorated value (a kind of “setup overhead”), but even here it would make more sense to apply the full Setup Cost (i.e. Cost/End Qty) to the items produced.

The Prorate Setup Costs setting makes sense when calculating the standard cost of an item during a cost roll-up and revalue, but it should not impact how setup costs are calculated and applied on a Manufacturing Order.

For Actual Cost, it makes sense to NOT have Prorate Setup Costs marked.  Average Quantity on Item Engineering can then be set, if needed, and it will not impact manufacturing orders.

For Standard Cost, you may want to consider only marking the Prorate Setup Costs box immediately BEFORE doing a Roll-up and Revalue, then unmarking it.  It would be important to not process any MO Receipts while the setting is enabled.

Keywords: PSTL, Professional Services Tool Library, Duplicate Key

When using PSTL Combiner (i.e. combining Items, Customers or Vendors), you can get a duplicate key error:

[Microsoft][SQL Server Native Client 11.0][SQL Server]Violation of PRIMARY KEY constraint ‘PK-tablename-‘. Cannot insert duplicate key in object ‘dbo.-tablename-‘.  The duplicate key value is (AAAAAAA, BBBBB)

This is usually caused by a table that has multi-part key, such as a pricing table where the key fields are:

• Item Number
• Vendor ID
• U of M
• Date
• Qty

In such a table if there is pricing for Item A and Item B, and you are combining B into A, there is no way for PSTL to “combine” two price lists.  The same would be true for changing Vendor X to Vendor Y…it would create duplicate price lists and the software has no way to figure out which one to keep.

DO NOT FOLLOW THE STEPS BELOW IN A LIVE COMPANY DATABASE!  MAKE SURE YOU HAVE TESTED AND VALIDATED BEFORE DOING THIS IN LIVE.

For the table getting the error, delete either the A or B set of records.  You need to make a choice of which “source” will be the master record going forward, is it the records from A or from B?  Then run the query below:

delete [tablename] where [ITEMNMBR/VENDORID/CUSTNMBR] = ‘A’

9MAY2023: Update: John Arnold at US Digital shared the script below (link is a text file) he created to identify problems with Customer Combiner prior to running the tool.  Download Script