How to Correct Out of Balance Inventory Items for Average Cost items in Sage 100

Description

How to Correct Out-of Balance Inventory Items for Average Cost items in versions 4.40 and higher

Disclaimer

Use caution when working with the below product functionality. Always create a backup of your data before proceeding with advanced solutions.

Cause

The Average Cost is incorrect on versions 4.40 and higher or:

There are differences between the Inventory Valuation, Trial Balance and Stock Status reports
Item Code has a negative or incorrect Average Cost
Inventory Reports have an Item Code with zero QOH, but has an Extended Total Inventory Value
Stranded values in the IM_DataEntryCostCalcCommit.M4T table or the CostCalcQtyCommitted or CostCalcCostCommitted fields in the IM_ItemCost.M4T or IM_ItemWarehouse.M4T tables

Resolution

Note: A signed Pre-Release Agreement (PRA) is required in order to receive and install the Average Cost Fix Utility. The Customer Contact Name and email address is required for the PRA. Please contact Sage Customer Support to acquire the PRA and utility.

Once notification from Sage’s Legal department that the signed PRA has been received and recorded:

Make a copy of the live Company Code into a test company (i.e. Company Code TST)
In the TEST company, run the IM_CommitCheckClear_utl and IM_ClearCommitDataEntry_utl utilities to clear stranded values in the IM_ItemWarehouse, IM_ItemCost and IM_DataEntryCostCalcCommit files.
Run the IM_AverageCost_utl.m4p utility in the test company (see “How to run the Average Cost Fix Utility”)
Open Inventory Management, Utilities, Recalculate Item History utility and click Proceed.
Compare costs between the Inventory Valuation, Trial Balance and Stock Status reports and the total Quantity On Hand on the Stock Status report vs. the Trial Balance report. Verify and review specific items in Item Maintenance as well as the reports by grand total and by Item Code.

Support

If necessary, seek the assistance of Acute Data Systems to help provide assistance with this process.