Archive for the ‘manufacturing’ Category
Mike wrote this great blog article awhile ago: http://gp.rosebizincblogs.com/2008/10/using-gp-macros-to-import-data-2.html
The reason I reference it now is that I have worked with GP for over 12 years and had never used this method for data import. But we are currently working with a new customer that is migrating to GP. They have about 600 BOM’s and though we were able to import the BOM’s fairly easily, importing the routers was just not going to work.
So I thought I would try the macro method. It worked beautifully. The source file I used, had very simple router information, and the macro ran just fine.
Two things to consider:
- It’s not going to work the first time (what macro ever does?), so expect that minor set back.
- Be very careful not to interrupt the process by continuing to work on your machine. In my case, I was accessing GP through a remote desk top, so this wasn’t a problem. I just minimized the RDP window, and went about my day as the macro ran on the remote machine.
If you haven’t tried this out yet, you should give it a try.
I put together some basic Dynamics GP user documentation and some of our specific blog articles related to manufacturing in Dynamics GP. You can request the document here, and it will be e-mailed to you for download:
Some of the subjects covered are:
- Manufacturing BOM
- Manufacturing Orders
- Serial / Lot Tracking
Let me know if you find this helpful.
Dynamics GP includes the functionality to track serial numbers from inventory receipt, through manufacturing, and to the customer. You can track forward and back. You can track component serial numbers in a manufactured item. This functionality is also available for lot tracked items. This is handy if you ever need to execute a product recall or track down the source of a defective product.
To track component serial numbers through the manufacturing process, you need to link the component serial numbers to the finished good serial number. You can easily do this from the Manufacturing Order Receipt Entry screen.
In this example the component item serial numbers, CRD-000014, PROC-0000013, and PROC-0000017, are linked to the finished good, PROCOMP-0000015.
This can be a confusing screen, but if you can select the correct finished good item, the available component serial numbers and the already linked component serial numbers will properly present themselves.
The manufacturing order process is then continued as it normally would be.
To trace serial number throughout the system, use the Serial/Lot trace Inquiry screen. To see the linked component in a manufactured item, click on the View BOM Trace button.
Here you can see the linked component serial number, CRD-000014, and the related receiving transaction where it first came into the inventory.
I created this video to show this process in action: http://youtu.be/8xjT54QFzwE
The Project Accounting module in Dynamics GP is a very flexible module. I have seen people use it many different ways, in many different situations. For people that want to track job costs for unique jobs and are interested in managing costs, delivery, and profit, the Project Accounting module can be setup to easily do this. As an example I set up a job that was basically to make and deliver a set of promotional watches that a client was going to use in a marketing campaign. The job consisted mainly of labor and materials. I set up the job to track three cost categories for labor, and several inventory items that would be used to turn into the promotional watches.
Here is the Project setup for this job:
In this scenario, I would issue purchase orders against the project; identifying the project and cost category:
Here is the item detail in the project:
Here is the fee schedule from which customer invoices are generated:
This simple use of Project Accounting allows me to easily track job costs and invoice customers.
Here is a video that shows this in action:
If you don’t have the Sales Forecast module for Dynamics GP, you can still maintain and manage a sales forecast using the Sales Order module. This information can be used to drive the MRP functionality in Dynamics GP. There are two steps you need to do to accomplish this:
1.) Enter your sales forecasts in to Quote documents in the Sales Order module
2.) Setup MRP to look at Quotes when generating MRP requirements
Here’s a Quote:
Here’s a list of several quotes that make up the total forecast:
Here’s a Pivot Table easily created from a refreshable Excel report that shows the forecasted quantities by item and date:
Here’s the setting to check to make sure that MRP looks at Quotes when the MRP regeneration is executed:
Here is a video that shows this in action: Sales Quotes for MRP
Here’s the same video on YouTube:
When doing a Great Plains manufacturing implementation, I’m often asked to import manufacturing bills of material from another system. Clients are sometimes very surprised to hear that GP does not have a built in BOM import tool. We do BOM imports using SQL inserts from Excel spreadsheets for clients during an implementation so some clients think that they should have a tool to import BOMs too.
I think there is a need for such a tool and we could develop one but there are some big issues for both the implementation team and the client. Let me try to explain.
There are 3 GP tables that hold BOM related data.
- BOM Header table
- BOM Detail table
- Reference designator table
The data in each of these tables has to be formatted and organized correctly. If not formatted and organized correctly, the import may not work or bad data may be imported. This leaves quite the liability for the implementation team and if users are not getting the results they expect, they do not have a useful tool.
The BOM Header table is fairly straight forward as the import would have to list only the finished good or subassemly items. Just think of the upper left quadrant of the GP Bill of Material Entry window.
The BOM Detail table is much more complex. Think of the right half of the Bill of Material Entry window. In order to get all the data imported correctly the source document would have to list each finished good and it’s components (including subassemblies) and each subassembly and it’s components. Each of these rows would have to include the correct position number and relationship to the appropriate subassembly and final assembly. This takes a long time to do. Try taking an indented BOM from another system, export it to Excel, and then put all the rows in the proper sequence and keep all the columns straight. It takes time to do this.
The formatting issue is worse for the reference designators. Most systems will export the reference desigators in one long string. GP requires that the string be broken into 21 character segments (including spaces and punctuation). I have had to format these strings into as many as 10, 21 character segments (a column for each one in Excel) and it takes a lot of time.
Users also have to know what the field values mean in these tables. For example what is a BOM Type = 1? What is a BOM Category of 2? SQL requires the use of numerics to identify these rather than ‘Phantom or Regular as you see on the front end. Did you also know that these two fields are flipped in the SQL tables? What the GP Bill of Material Entry window calls BOM Category is BOM Type in SQL.
Users also have to know the field formats that SQL uses and the field lengths. If you exceed the field length in your data source, you will not get the desired results in the front end.
In order to have an effective tool, you should validate the import with the Great Plains business logic. Do all the items exist in the Item Master, are the items setup in Item Engineering Data, are all the sites and work centers setup, does the BOM already exist, etc., etc., etc. The fact that Microsoft has not exposed a BOM API via eConnect would require complete reverse engineering or specialist Dexterity knowledge (to invoke manufacturing calls in the correct order). Business logic would also need to include handling of BOM revisions and revision levels.
So what am I trying to say here? It is my belief that using SQL imports for BOMs does not save users any time, really. By the time you get done with all the formatting, import verification, error correction, user acceptance and sign-off, you could have created these BOMs using the GP Bill of Material Entry functionality. Many BOMs are similar to each other in many organizations and the BOM Copy functionality saves users time and reduces errors.
It is fairly easy to create a SQL BOM import to get the data into GP manufacturing once you understand the table structure and have a basic understanding of SQL statements. The real issue is getting the data in the correct format for the import and then validating the data once it is in.
With the many BOM export formats being used today (single level, multilevel, CSV, XML, etc.) it is time consuming and costly to write interfaces for each. Custom adapters or complex mapping tools may be needed to handle these various formats. So it becomes a costly issue for the client or the partner.
With that said, there are some pretty nice tools available to import items and BOMs from CAD software such as Solid Works and Autocad. If clients are using these tools, we generally recommend they buy one of the integration tools that are available to export items and BOMS from the CAD software and import it to the GP item master and BOM tables. These integration tools act as a middleman and take the data from one system and format for GP.
I know that creating BOMs is no fun, but Great Plains functionality does a pretty good job entering BOMs and using business logic to validate the entry of BOM data. There just really are no shortcuts if you want accurate BOMs and manufacturing functionality. Manufacturing is complex because of all the inter-relationships involved. Everything (BOMs, routers, work centers, MO’s, picklists et. al.) works together and if business logic is not followed, it most likely will not and you will end up with a bad result. So take your time and do it right the first time.
In a previous post, we discussed how to enter manufacturing lead times in Item Resource Planning manually. Great Plains Manufacturing will calculate the manufacturing lead times for you from the routings you create to manufacture your items. GP Mfg provides a utility that will calculate the manufacturing lead times based on entries you make in your routings.
As we discussed earlier, there are two types of manufacturing lead times, Mfg Fixed Lead Time and Mfg Variable Lead Time. The entries made in Routing Sequence Entry dictate how these lead times are calculated. The Mfg Fixed Lead Time in Item Resource Planning is determined by the entries made in the Setup Time and the Queue Time on the right hand side of the Routing Sequence Entry window. The Mfg Variable Lead Time in Item Resource Planning is determined by the entry made in the Move Time and the Cycle Time in the Routing Sequence Entry window (see the example below).
The entries made in Routing Sequence Entry are made in hours or fractions of hours. Don’t get confused with the entries made in Item Resource Planning that are made in hours. When you run the Calculate Mfg Lead Times utility, it will convert the hours entered in Routing Sequence Entry to days and display them in Item Resource Planning.
Repeat entering times for each sequence in your routing and the Calculate Mfg Lead Times utility will include entries made in all of your sequences.
Once you have made your entries in Routing Sequence Entry, you will need to run the Calculate Manufacturing Lead Times utility to convert the hours to days and display them in Item Resource Planning. You can run the utility for all items in your item master or just for one item. Click Calculate All for run for all items. To run for one item, enter the item number and click Calculate One.
After running the utility, go to Item Resource Planning. You will see that the Default Values have not been updated, but the sites assigned to the item now reflect the new values calculated by the utility. Also, be aware that running the utility will overwrite and values manually entered in the Item Resource Planning item-site combinations.
I find that many Great Plains manufacturing users do not use MRP effectively, or at all, because the release and due dates never seem to make sense. I realize that managing dates and then trusting the release and due dates from an MRP run can be a daunting task. That should not be a good excuse for not using a very powerful and effective tool.
Many users just use the default dates when creating documents that affect inventory and the manufacturing process because it is “too much work” to enter and manage the dates in Purchase Order Processing, Sales Order Processing and Manufacturing. I would argue that it costs users more in time and costs to not manage the dates up front and throughout the purchasing, sales and manufacturing.
How many times have you, as a user, had to track down a shortage, reallocate items to more urgent orders and correct errors? It is my belief as a former Controller and a current GP implementation consultant, that it cost you and your organization much more on the back end to deal with incorrect release and due dates than it does to enter and maintain meaningful dates in Dynamics GP manufacturing. In fact, I feel so strongly about it, that I believe the savings would more than pay for an additional head or heads that may be required in your organization to maintain accurate dates.
When dates are used and managed effectively, issues that affect manufacturing and eventually shipping are identified quickly during an MRP run and MRP will even make suggestions on how to deal with the issues. MRP will include suggestions on moving orders in or out and cancelling orders that are oversupplied. Why should you study inventory levels via GP inquiries, reports and SmartLists when MRP will do it for you? Seems like a great time saver to me.
So where does MRP get the data to suggest release and due dates?
The due date is the date an order is due in house to meet the demand that created it. Due dates come from many sources including MO’s, MRP suggested MO’s, Sales Documents, Sales Forecasts, etc. The release dates suggested by MRP start with the due date of the document or MRP suggestion the item fulfills and works backwards, based on lead times and calendar down days.
MRP-Planned Manufacturing Orders
Let’s look at MRP-Planned manufacturing orders first. MRP looks at the lead times in the Item Resource Planning windows. The lead times can be entered here manually or generated from routers using the Calculate Mfg Lead Times utility. MRP will look to the item-site combination in Item Resource Planning. Since MRP suggests manufacturing orders for make item only, enter the lead times in days or fractions of days in either the Mfg Fixed Lead Time field or the Mfg Variable Lead Time field or both. MRP uses these fields differently as you might suspect.
- Mfg Fixed Lead Time – does just that, adds a fixed number of days to the MO when calculating the release date. The formula is Due Date – Mfg Fixed Lead Time. For example, if your forecast shows a suggested MO with a due date of 8/1/2010, the release date would be 5 days earlier or 7/27/2010 as long as 7/27/2010 is not a down day on your work center calendar.
- Mfg Variable Lead Time – is variable based on the quantity suggested. The formula is Mfg Variable Lead Time * quantity, rounded up to the next whole day. For example, if I have a Mfg Variable Lead Time = .01 and a quantity of 500, my lead time is 5 days.
- Total Manufacturing Lead Time for a make item is Mfg Fixed Lead Time + Mfg Variable Lead Time, rounded up to the next whole day. Mfg Fixed Lead Time & Mfg Variable Lead Time in Item Resource Planning.
MRP-Planned Purchase Orders
If the item does not have a default site set in Item Quantities Maintenance, MRP will use the Purchasing Lead Time from the Item-Site combination in Item Resource Planning.
If the item has a default site set in Item Quantities Maintenance, MRP has two choices to select from.
- No Primary vendor selected for the default site – Purchasing Lead Time is from the specified default site in Item Resource Planning Maintenance window.
- Primary vendor has been selected for the default site – Purchasing Lead Time is selected from the item and vendor in the Item Vendors Maintenance window (Planning Lead Time).
I hope that this takes some of the mystery out of the MRP release dates and due dates.
Dynamics GP can easily be used to manage inventory demand and supply for multiple inventory locations. By using separate sales forecasts, the MRP functionality in GP can separately analyze forecasted demand, and suggest manufacturing orders and purchase orders to fulfill the demand.
This video shows this functionality in action.
Generally our clients who use the standard cost method to value inventory, use the Manufacturing module; and standard costs are easily managed in that module.
However the Inventory Control module can be used to accurately maintain and track standard cost items using the FIFO Periodic and LIFO Periodic Inventory Valuation Methods. Unfortunately the steps to adjust standard costs is a bit quirky. There is not a dedicated function in GP that allows you to specifically identify the standard cost items that you want to adjust.
There is a function in the year-end close routine for Inventory Control (Tools >> Routines >> Inventory >> Year-End Close) that allows you to automatically adjust standard cost to the current cost for all FIFO Periodic and LIFO Periodic items. This however does not allow you to select which items to update, what the new standard costs will be, and does not create a journal entry to revalue existing inventory in the GL; so really it’s useless.
The following is my preferred process:
1) Run a Stock Status report before any changes are made to show the inventory values with existing standard costs.
2) Change the standard cost for every item you want to change.
3) Run a Stock Status report after all the changes are made to show the inventory values with the new standard costs.
4) At this point you could enter a journal entry for the difference in inventory value as reported on the Stock Status reports, and you would be done.
But what I would do, is take the alternative step of adjusting the costs by using the Inventory Adjust Costs screen. Navigate to this screen: Tools >> Utilities >> Inventory >> Adjust Costs
Select the items and enter the new standard costs for all receipts, and Process:
This will update the receipt records and print the information you need to create adjusting GL entries.
If you use the Purchase Receipts report for any of your reporting, you will want to do this step; otherwise the new standard costs will not be in the report.