Reseting Inventory Quantity On hand and Quantity Available in Inventory

During a normal implementation, customers using the Inventory module are faced with testing numerous transaction scenarious prior to going live with their Dynamics GP system. However, the conversion to the live company requires transfering the Inventory Master tables, including the Item Quantity Master (IV00102) table. This table hosts the Quantity On Hand and Quantity Available fields per site which can already be affected by the transactions previously tested.

In order to reset the values in these fields without any SQL query involved (ok, not completely), you can try this simple solution: create a physical count for all items!

1) Create a stock count schedule for all items. Go to Transactions > Inventory > Stock Count Schedule. Enter a Stock Count ID, i.e., CLRQTYS.
2) Mass Add all items in your inventory by any criteria
3) Start the count by clicking on the Start button. Close the Stock Count Schedule window.

NOTE: you can choose to uncheck the Reuse Stock Count checkmark.

4) Open the Stock Count Entry window. Go to Transactions > Inventory > Stock Count Entry. Mark all items as counted without entering any values (they are automatically defaulted to zero).

NOTE: If the company has a great number of products, the items can be marked as counted with the following SQL script

declare @stockcountID char(20)
set @stockcountID = 'CLRQTYS' -- replace for the actual stock count you created
update IV10300 set ATPSTVRNC = 1 where stckcntid = @stockcountID
update IV10301 set VERIFIED = 1 where stckcntid = @stockcountID

5) Post the count. Click on the Process button to process and post the count.

Now you are good to transfer your Inventory Master tables!

Until next post!

Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC


Anonymous said…
Great post! I could have used that advice a few years ago in a past role of mine.

I've passed it on to some co-workers who I'm sure will use it again in the future!
Mariano Gomez said…
Jason, I am glad you find this post useful! This is the only incentive that keeps me writing and sharing my knowledge.

Best regards,

Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
I like your article and it really gives an outstanding idea that is very helpful for all the people on web.
Anonymous said…
Mariano, great post. It will save our life with a customer with serious trouble with inventory!

Best Regards,

Humberto Chemas
Anonymous said…
Hi Mariano,

What I want to do is that we want to post the off lease assets info to GP database via GP web service api.

I have a question for you and hope you can help me out.

I can create an item via dynamics GP web service (soap client) , but I could not figure out how to set up the qty such as qty on hand for the item via web service.

I also want to add a serial number to the item via web service but I could not figure out.

Could you please give me some hints?

Thanks in advance!

Popular posts from this blog

Power Apps - Application Monitoring with Azure Application Insights

Troubleshooting the Microsoft Dynamics GP 2013 Web Client - Part 3

Power Automate: Calculate Soundex of a word with a flow