The average cost for an item is incorrect in Microsoft Dynamics GP

Symptoms

The average cost for an item is incorrect in Microsoft Dynamics GP. You may notice this issue in a variety of ways:

• You note that the Current Cost of the item is incorrect in Item Maintenance. (For items with an Average Perpetual valuation method, the Current Cost in Item Maintenance reflects today's average cost.)

• You tried to correct the item's average cost by selling all of the item and bringing it back in with the correct cost, but it did not work.

• You received in a purchase receipt and your average cost was not recalculated to the expected amount.

Cause

The QTYONHND value in the Inventory Purchase Receipts Work (IV10200) table is incorrect for this item.

This is a problem because the QTYONHND field is used as part of the calculation to determine an item's average cost.  Depending on the amount that it is off, it can greatly or minimally overstate or understate the item's average cost.

Note You do not see the value in the QTYONHND field of the IV10200 table anywhere within the Microsoft Dynamics GP application.  You can only see it by viewing the table in SQL.

Resolution

If an item with the Average Perpetual valuation method has a current cost that is incorrect, then perform the following steps to correct it:

1. Make a backup of your company database that can be restored if the end results are not desirable.

2. Delete or post any transactions for this item.  This includes any Sales Order Processing documents. You can use the Item Allocation Inquiry window to find and review transactions this item is allocated on. To access the Item Allocation Inquiry window, on the Inquiry menu, point to Inventory, and then click Item Allocation.

3. Run Inventory Reconcile on this item using Reconcile Inventory Quantities. This is to ensure that the Quantity On Hand in the Item Quantity Master (IV00102) table is correct. To access the Reconcile Inventory Quantities window from the Microsoft Dynamics GP menu, point to Tools, point to Utilities, point to Inventory, and then click Reconcile.

4. Using Item Transaction Entry, enter and post a decrease adjustment for the full quantity that is on hand at each site for this item. This is to bring the On Hand Quantity down to 0 so you can start over with this item. To access the Item Transaction Entry window on the Transactions menu, point to Inventory, and then click Transaction Entry.

5. Start the SQL Server Management Studio.   To do this, click Start, point to All Programs, point to Microsoft SQL Server 20XX, and then click SQL Server Management Studio.   Click to open a new query for the company database.

6. To verify the quantity on hand in the Purchase Receipts Work table is now 0, run the following script against the company database using SQL.

select QTYONHND, * from IV10200 where itemnmbr = 'xx' and QTYTYPE = 1 order by DATERECD, RCTSEQNM 

Note 'xx' should be replaced by the item number of the item whose average cost is incorrect.

7. Review the last record returned in the script results as that is the most recent receipt for this item.  The value in the QTYONHND field should be 0.  If it is not, then there is a disconnect between the quantity on hand in the Item Quantity Master (IV00102) table and the Inventory Purchase Receipts Work (IV10200) table.  This will need to be corrected with a SQL update statement.

Note Make note of the Date Received (DATERECD) on the last receipt record as you will not want to post any transaction (increase or decrease) for this item using a date prior to that date.  Also make note of the value in the DEX_ROW_ID column on this receipt so it can be used in your Update script.

8. Use an Update statement to change the QTYONHND of the last receipt if it is not 0. An example of an update statement you could use would be:

update IV10200 set QTYONHND = 0 where DEX_ROW_ID = zz

Note: ZZ should be replaced with the number in the DEX_ROW_ID column of the last record returned from the script in Step 6.

Note: Always verify that you have a backup in place before running any Update statements.
 
9. Go to Transactions, click on Inventory, then select Transaction Entry.  Enter and post an increase adjustment for the correct quantity that is really on hand for this item.  When you do this you can enter the desired average cost in the Unit Cost field of the transaction.  Make sure the date  you use for this increase transaction is later than the date of the receipt updated in Step 6 and any other decreases that have been done for this item.  All new, additional increase and decrease transactions for this item will also need to be after this date as the QTYONHND has only been fixed as of that date. The QTYONHND should remain correct going forward as long as no backdating is done.

The Current Cost in Item Maintenance will now reflect the cost entered on the increase transaction. This is the item's current average cost.

10. Re-enter any transactions that were deleted from Step 2 if necessary.