Author: admin

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

Keywords: Dynamics GP Manufacturing, multi-product, co-product, by-product

A GP Manufacturing Order can only produce one item.  Right?

This may surprise you, but a GP Manufacturing Order can produce multiple items at once.  This is often referred to as By-Products, but it can be used for any scenario where you need to make multiple items at once.  Here’s how.

In this scenario we are producing cuts out of a roll of steel.  When we create an MO for 2×8 cuts we could also produce 2×4 and 2×6 cuts.

Additionally, we track lot numbers of the steel rolls so that we can always tell how much of a roll remains by looking at the lot quantity.

When we create a manufacturing order that needs a steel roll we issue the ENTIRE ROLL to the MO, and then return to stock the remainder of the roll.  This could be done by an Issue and Reverse Issue, however we’re doing to do it as a by-product (more on this later).

As shown above, the BOM for the 2×8 SHEET has both of the other possible cuts that could be produced AND the STEEL ROLL all at zero quantity.  It is not necessary to do this, the BOM could have only the STEEL ROLL, but we’ll see later that the extra setup at this point makes it easier to record production.

We have a Manufacturing Order to make 4 of the 2×8 SHEET.   The Picklist includes the “zero quantity” items:

The MO requires 0.12 FEET of STEEL ROLL, but when we check inventory we find that the smallest lot quantity is 20, so we change the Issue Transaction to 20 and post it.

MO Receipts can be posted as needed on the MO, recording additional production of “other” items at the same time.

At this point you could manually add By-Products/Co-Products/Multi-Products manually by clicking the Add Component button and entering the Quantity Produced as a NEGATIVE.  The benefit of adding the POSSIBLE other items to the BOM with zero quantity is you just need to enter the quantity next to each item.  It is much faster.  As shown above the other items that COULD BE produced are shown on the MO Receipt Entry window.  We will produce some of each item.

The software does not calculate the quantity of STEEL ROLL consumed by the co-products so that needs to be done manually.  However, the same would apply when doing a Reverse Issue transaction, and recording it as another co-product eliminates the need to create a separate Reverse Issue transaction.

Posting the MO Receipt will first post “production” of the co-products:

Followed by the normal finished good receipt:

With Actual Cost inventory the by-products go into stock at the last cost (i.e. the Current Cost shown on Item Maintenance).  There isn’t an opportunity to enter a Unit Cost, so costs would need to be adjusted after the fact using the Inventory Adjust Costs utility.

(WilloWare can customize the MO Receipt process for Actual Cost items to perform a cost allocation calculation that apportion the input costs (inventory, labor, machine, etc)  correctly over the items produced.)

When the Actual Cost finished good posts, its cost is the REMAINING costs in WIP (i.e. total cost put into WIP minus By-Product Cost).

With Standard Cost inventory the by-product go into inventory at its Standard Cost, as does the finished good.  The Standard Cost Roll-up routine does not include By-Product costs even if they are included on the BOM, so by-products/co-products when used with Standard Costing will always result in a Variance.