The Balance in Item Stock Inquiry is incorrect in Microsoft Dynamics GP

Applies to: Dynamics GP 2010

Symptoms


In Microsoft Dynamics GP, when you review an item's quantities in the Item Stock Inquiry window, the Balance field for the first transactional line does not equal the amount of that transaction. For example the first transaction displayed is an increase adjustment for a quantity of 5, however the Balance field displays 3.

Cause


There are many things that will make the Balance field in the Item Stock Inquiry window incorrect.

1. The Total Stock at the bottom of the window is incorrect. See Resolution 1.

2. The Maintain History - Transaction checkbox has not consistently been marked for the item in Item Maintenance Options. See Resolution 2.

3. Historical transactions were removed for this item using the Remove Inventory Transaction History window. See Resolution 2.

4. An interruption during posting caused the IV30300 to not be updated or updated incorrectly. See Resolution 2.

5. The Unit of Measure equivalent for this item has been changed. See Resolution 3.

6. It is a serialized or lot numbered item and quantities were overridden to sell goods that were not in the inventory. For example you sold 10 of an item when you had 0 on hand. See Resolution 4.


Resolution


Resolution 1: To correct the Total Stock at the bottom of the window run Inventory Reconcile. To run Inventory Reconcile on the Microsoft Dynamics GP window point to Tools, point to Utilities, point to Inventory, and then click Reconcile. Insert the item you are reviewing in the Inquiry window and click Process.

Resolution 2: If historical transactions are missing or incomplete, the Item Stock Inquiry window will not be reliable for this item and you may need to discontinue use of it. If that is not desirable, then you may wish to start over with the item. To do this, here are the recommended steps.

a. If there are any quantities not On Hand, enter and post Inventory Transfer transactions to move them to the On Hand quantity bucket.

b. Note the current Quantity on Hand for the item, and then enter and post an inventory decrease transaction to bring that quantity to 0.

c. Use Remove Inventory Transaction History to remove all transactional history for this item.

d. At this point the Item Stock Inquiry window will have 0 Total Stock and 0 quantity Balance. Verify that Maintain History - Transaction checkbox is marked in the Item Maintenance Options window to keep this information going forward.

e. Enter an increase inventory adjustment transaction to bring the quantity on hand back up to what it should be.

The Item Stock Inquiry window will now be correct.

Note It may be possible to manipulate the data in the backend using SQL to correct this window.

Customers:
For more information about data manipulation consulting services, contact your partner of record If you do not have a partner of record, visit the following web site to identify a partner: Microsoft Pinpoint

Partners:
For more information about data manipulation consulting services, contact Microsoft Advisory Services at 800-MPN-SOLVE or via email at askpts@microsoft.com

Resolution 3:
If equivalent units of measure have been changed, then the Item Stock Inquiry window will not be reliable for this item and you may need to discontinue use of it. If that is not desirable, then you may wish to start over with the item. See the steps under Resolution 2 for the recommended steps for doing this.

Resolution 4: Keeping in mind that when you override a serialized or lot numbered item your Quantity on Hand does not go negative, the selling of those 10 left your on hand quantity at 0. When you brought quantity in (assume 10), that increased the Quantity on Hand rather than consuming the override. Now your quantity on hand is overstated by the 10 you overrode. To correct this as well as the Item Stock Inquiry window, do these steps:

a. Enter and post a decrease transaction to take those 10 out of your inventory so your Quantity on Hand is now correct. Typically this decrease would not be posted to General Ledger.

b. Use Remove Transaction History to remove the transaction done in Step A from inventory history. In the Remove Inventory Transaction History window choose Document Number in the Range and insert the document number of the transaction from Step A. Process.

The Item Stock Inquiry window and the Quantity on Hand will now be correct.

More Information


The Balance column in this window is calculated starting with the quantity in the Total Stock field at the bottom. This is calculated from the quantity fields in the IV00102 (Item Quantity Master) table. So if these quantities are incorrect, then the Total Stock will also be incorrect. That means that the Balance that is calculated will likely also be incorrect.

Once the Total Stock number is determined, then we ADD any transactions that decrease the quantity of the item, and SUBTRACT any transactions that increase the quantity working our way from the bottom of the window to the top to determine what is displayed in the Balance field. The transactions we are using are stored in the IV30300 (Inventory Transaction Amounts History) table.

To determine if there are missing or additional transactions in the IV30300 table that are causing discrepancies for the item, compare the inflows in the IV30300 to the receipts in the IV10200 (InventoryPurchase Receipts Work) table. Also compare the decrease transactions in the IV30300 to the outflows in the IV10201 (Inventory Purchase Receipts Detail)table.