Update standard cost on multiple inventory cards

(0) Share

Report

Posted on by Community Member

Hi,

Is there a way to update the standard cost for multiple inventory cards within the inventory module?

I can update individual inventory cards via: Item Maintenance ( Cards | Inventory | Item) - Edit the Standard Cost of the item on the item card. But I have hundreds of inventory items to update and this method would be time consuming.

I thought of using integration manager to upload the new inventory standard costs but this doesn't work as I receive an error message that the inventory item already exists.

Kind regards,

Robert

*This post is locked for comments

All responses (13)

Answers (1)

Suggested answer

Judy Hank 5 on at

Like (1)

Report

Re: Update standard cost on multiple inventory cards

I'm not sure if you resolved this issue - we had the same issue when trying to use Integration Manager.

Here is a solution: Go into Integration Manager and open the applicable Integration. Then click on the integration title to highlight it. Now click on "properties." In the "Destination Edit Mode" use the drop down feature and select "Insert and Update." This will allow you to not only insert new items but also update the existing items.

Hope this helps.
Chad Neal 25 on at

Like (0)

Report

Re: Re: Re: Re: Re: Re: Re: Update standard cost on multiple inventory cards

Hi Richard.

Thanks for your offer. I emailed you earlier.

Chad
Richard Whaley 25,195 on at

Like (0)

Report

Re: Re: Re: Re: Re: Re: Update standard cost on multiple inventory cards

I can send you a script that will identify POTENTIAL issues to be addressed. Send me an email to richardwhaley@hotmail.com. Notice to others....I do not provide free tech support through this email unless I offer to send you something. I only provide free support through the forum. Post requests on the forum only.
Sagi88 2,250 on at

Like (0)

Report

Re: Re: Re: Update standard cost on multiple inventory cards

Hi Robert.

When you create an Integration it automatically defaults to insert only.

Go to the properties of the Integration (Double click trhe name or right click).

Change the method to Update only if you only wish to update or Insert and Update is you wish to do both.

Hope this helps
Chad Neal 25 on at

Like (0)

Report

Re: Re: Re: Re: Re: Update standard cost on multiple inventory cards

Hi All,

I tried running the rollup and revalue again last night and received
the following error again during the the revalue:

"You can’t revalue inventory because there are unposted shipment or
shipment/invoice transactions in Purchase Order Processing."

I verified there were no open PO's (Transactions>Purchasing>Purchasing
Batches...no batches showed up), so I am really stuck on this issue.

Any help is GREATLY appreciated. I have to get this roll completed as
we are under extreme pressure to get the costs updated.

Thank you!

Chad
Verified answer

Community Member on at

Like (0)

Report

Re: Re: Re: Re: Update standard cost on multiple inventory cards

Hi All,

Thanks. Yes I am performing all of this currently in a Test Company. We are not too worried about the history as we will take a snapshot of all inventory items on a monthly basis and keep these reports as our history.

Actually I've just solved the problem with Integration Manager.

1. You need to choose Destination Microsoft Dynamics GP Inventory Item NOT Microsoft Dynamics GP eConnect Inventory Item.

2. Once you've created your Integration, you need to go to Properties of the integration and change the Destination Edit Mode from Insert Only to Insert and Update.

I knew there was a simple setting I was missing somewhere!

Well thanks again all for taking time to respond to my problem.

Take care,

Robert
Beat Bucher GP Gee... 28,058 Moderator on at

Like (0)

Report

Re: Re: Re: Update standard cost on multiple inventory cards

Hi Robert,

In that case it's even easier.. since only two tables are involved in the Item Costs : IV00101 (Item Master) and IV00118 (Item Cost Change History). If you don't care about the history, you just have to update the IV00101 table in the field STNDCOST to the corresponding ITEMNMBR. I would encourage you to setup a test company where you can try this out before doing it on the live company and always take a backup before (this is never enough said :-) ). Also, run a reconcile to the inventory after the update, thus making the system realign any numbers that would eventually be out of sync. Let us know if this works for your need.
Community Member on at

Like (0)

Report

Re: Re: Update standard cost on multiple inventory cards

Hi All,

Thank you all for your comments.

Ok I tried Integration Manager eConnect Inventory Item destination and I get this error message: DOC 1 ERROR: Microsoft.Dynamics.GP.eConnect: Number = 7908 Stored Procedure taUpdateCreateItemRcd : Description = Item Number already exists and the UpdateIfExists flag is set to not allow updates. I can't see how to change the Destination to allow update.

I tried Importing a Table setting the Destination Table = Item Master. But unfortunately this also doesn't work due to the same reason of records beign rejected. The detail of the error message reads: Records are usually rejected because the record already exists in the table. To view the rejected records, open the file ***.rjt. Again I can't see how to change the Destination to allow update.

Beat unfortunately we don't have the manufacturing or bill of materials modules, we only have the inventory module. Our standard costs are very simple. Thus I am hoping there is a way to change the standard costs within inventory module without purchasing any other modules.

Many thanks again all for your input.

Robert
Beat Bucher GP Gee... 28,058 Moderator on at

Like (0)

Report

Re: Update standard cost on multiple inventory cards

Hi Robert,

This is what the help for the manufacturing module says about the standard costs. We used to have an import table that had a fiew fields for the Excel import (thru a DTS in SQL manager) and another SQL script that updated in a second step the ICIV00323 table. I've a detailed documentation of the process as a word document, but it is unfortunatly in french... so if you don't mind to use a quick translation tool, at least you'll get the global idea about how it works.

There are two manufacturing tables that hold information related to the standard cost of items. These tables are the CT00003 and the ICIV0323.

The CT00003 table holds the Standard Costs for the Buy or Either items.
This table holds the Material costs, as well as the Fixed and/or Variable Overhead costs for the materials. The corresponding window in Great Plains is the Standard Item Material Costs window.

The ICIV0323 table holds the Material, Labor and Machine costs for Make items. The Current Standard Cost is held in the _1 columns, the Pending/Revalue Standard Cost is held in the _3 columns, and the Overridden Standard Costs are held in the _5 columns. The corresponding window in Great Plains is the Standard Cost Maintenance window.

The process that will use these tables and fields is the Standard Cost Rollup and Revalue Routine, found under Routines | Manufacturing | Roll Up and Revalue.

If the Roll Up routine is run, the application will find the pending material costs in the CT00003, and will use those costs plus the Labor and Machine costs from the primary Routing to find a Standard Cost for the Make items, and will roll the costs up to the finished goods as pending costs.

When the Revalue routine is run, the application will finalize those pending costs, making them the new standard costs for the components and finished goods. It will also create a revaluation GL batch for the on hand quantities of the items that were changed.

There is also a different tool to use for Standard Costs. In this case, the ICIV0323 table is used again, this time with the _7 columns. The window in Great Plains is the Standard Cost Changes window, which gives you are graphical view of the BOMs and shows the costs at each level. In this
window, you would enter a Proposed Standard Cost for your Buy or Either items. Then instead of using the Routine, you would use the Roll Up and Replace Costs buttons in this window to roll up the costs and replace them.
Beat Bucher GP Gee... 28,058 Moderator on at

Like (0)

Report

Re: Update standard cost on multiple inventory cards

Hi Robert,

IM is definitly not the tool for this kind of task... I worked for a larger manufacturing company in the past and there we updated about every month the standard costs of the products and we had about 5500 items in inventory. The person in charge of the cost control built an Excel sheet with the updated manufacturing costs (cost of good, labor cost, etc..). I think it was about 2-3 columns that we uploaded into a table with the Item-number, but I can't remember now on top of my head the exact table name. Once the data uploaded in the table, you have to run a Standard Costs Changes Roll-up or Replace-Costs (the behavior is slightly different, check the manual or the help file) in order to populate the new costs to the item inventory. We updated usually batches of 1000-1500 items (it was essentially based on the class-ID) at one time. The whole process took about 30 sec. + Costs Changes rollup (another 5-10 min I guess) whereas the IM can take hours for such an amount of items.

If I can find some SQL scripts backup from that time, I'll gladly post them here. Good Luck.