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.
NOTES:
Once the QTYONHND in the IV10200 is incorrect a manual SQL update is the only way to correct it. IV Reconcile NEVER updates the QTYONHND value in the IV10200.
It is not feasible to go back through each receipt layer and determine what the QTYONHND value should be since it is a moving target. As a result, we always recommend just updating the last receipt layer with the correct value.
When the HITB IV Reset process is run this out of balance situation is seen as the 103 ("Average Item receipt QTY on hand does not equal IV QTY on hand") error.