Skip to content
logo Knowledgebase

How to run the IM Balance Inventory Quantity and Cost Utility in versions 4.40 and higher

Created on  | Last modified on 

Summary

How to run the IM Balance Inventory Quantity and Cost Utility in versions 4.40 and higher

Resolution

CAUTION: Use caution when working with the below product functionality. Always create a backup of your data before proceeding with advanced solutions. If necessary, seek the assistance of a qualified Sage business partner, network administrator, or Sage customer support analyst.


For versions 4.40.0.8 and 4.50.2.0 and higher:

Note: Prior to running the IM Balance Inventory Quantity and Cost Utility for tiered items, make sure that the Negative Tiers have been adjusted and the Remove Zero Quantity Tiers utility has been run.

  1. Open Library Master, Main, Company Maintenance, create a new Company Code (i.e. TST) and copy the live company to the new Company Code
  2. 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.
  3. Open Inventory module for the company the utility will run against.
  4. Enter the module date that is to be used for the CLEAR CST transactions. This is the transaction date that will be used for the corrected transaction entries.
  5. Open File, Run and type *UTL and OK.
  6. Select IM Balance Inventory Quantity and Cost Utility and OK
  7. Review the Warning message and click on Yes to run the utility.
  8. Select the copy Company Code
  9. On the IM Generic Average Cost Utility window, select a range of items and/or warehouse codes for the utility to process for
  10. Click Proceed
  11. Message Do you want to open the Spreadsheet and Update the Items?
  12. Click Yes and the excel spreadsheet will open automatically to view the items chosen. The spreadsheet will provide the ItemCode, WarehouseCode, IM ItemCost extended cost, IM ItemTransactionHistory extended cost. The difference between these two tables is then presented in the Cost Difference column. The same comparison is done for the quantities and then the difference is provided in the Qty Difference column.
  13. Closing the excel spreadsheet then launches the next process
  14. Note: An updated spreadsheet is provided when the utility is done. If want to save this preliminary document use Excel's File Save AS while it is still open.
  15. Close the excel spreadsheet when done saving so that the next process is launched
  16. Answer Yes to Do you want to Update these records?
  17. Click OK on the message Done.
  18. When utility is complete, choose Utilities, Recalculate Item History Click Proceed
  19. When recalculation of history is complete, run reports for verification.
  20. To view the changes that occurred, open the IM_BalanceInvQtyCostXXX.csv file in the ..\MAS90\Home\Textout folder:
    1. IM ItemTransactionHistory table (these would be records that IZ transactions were created)
    2. IM ItemWarehouse TotalWarehouseValue, QuantityOnHand, AverageCost changes
    3. CI Item TotalInventoryValue, TotalQuantityOnHand, AverageUnitCost changes
  21. Run the Valuation, Trial Balance, and the Valuation By Period Reports for comparison.

For versions 4.40.0.0 , 4.40.0.7 and 4.50.0.0 , 4.50.1.0:

Note: Prior to running the IM Balance Quantity and Cost Utility for tiered items, make sure that the Negative Tiers have been adjusted and the Remove Zero Quantity Tiers utility has been run.

Note: A signed Pre-Release Agreement (PRA) is required in order to receive and install the Generic 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:

  1. Copy the IM_GenAverageCost_utl.m4p, IM_GenAverageCost_utl.pvc, and IM_GenAverageCost_utl.M4L files into the ..\MAS90\IM folder.
  2. Open Library Master, Main, Company Maintenance, create a new Company Code (i.e. TST) and copy the live company to the new Company Code
  3. 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.
  4. Open Inventory module for the copy company the utility will run against.
  5. Enter the module date that is to be used for the CLEAR CST transactions. This is the transaction date that will be used for the corrected transaction entries.
  6. File, Run, type syzcon Click OK
  7. Type at the > run "IM_GenAverageCost_utl.m4p"
  8. Select the copy TEST Company Code
  9. On the IM Generic Average Cost Utility window, select a range of items and/or warehouse codes for the utility to process for
  10. Click Proceed
  11. Message Do you want to open the Spreadsheet and Update the Items?
  12. Click Yes and the excel spreadsheet will open automatically to view the items chosen. The spreadsheet will provide the ItemCode, WarehouseCode, IM ItemCost extended cost, IM ItemTransactionHistory extended cost. The difference between these two tables is then presented in the Cost Difference column. The same comparison is done for the quantities and then the difference is provided in the Qty Difference column.
  13. Closing the excel spreadsheet then launches the next process
  14. Note: An updated spreadsheet is provided when the utility is done. If want to save this preliminary document use Excel's File Save AS while it is still open.
  15. Close the excel spreadsheet when done saving so that the next process is launched
  16. Answer Yes to "Do you want to Update these records?"
  17. Click OK on the message Done. Results can be viewed in
  18. When utility is complete, choose Utilities, Recalculate Item History Click Proceed
  19. When recalculation of history is complete, run reports for verification.
  20. To view the changes that occurred, open the IM_GenAverageCostXXX.csv file in the \MAS90Home\Textout folder:
    1. IM ItemTransactionHistory table (these would be records that IZ transactions were created)
    2. IM ItemWarehouse TotalWarehouseValue, QuantityOnHand, AverageCost changes
    3. CI Item TotalInventoryValue, TotalQuantityOnHand, AverageUnitCost changes
  21. Run the Valuation, Trial Balance, and the Valuation By Period Reports for comparison.

Utility Logic Processing

  • Tiered items (FIFO, LIFO, Lot, and serial)

This utility assumes that the values in the IM ItemCost table are correct then recalculates the Qty, Value & Average Cost in the CI Item, IM ItemWarehouse as needed and creates adjustment transactions in IM ItemTransactionHistory for LIFO, FIFO, LOT and SERIAL valued items so all 4 tables are in sync with IM ItemCost. The IZ transaction with have an entry in the EntryNo field of "RECALC CST"and in the TransactionEntryComment field of "IM_BalanceInvQtyCost_utl"

  • Standard Cost and Average Cost items

This Utility assumes that the IM_ItemTransactionHistory is correct and will recalculate the values in the CI_Item, and IM_ItemWarehouse files as needed to match the IM_ItemTransactionHistory file (table).

Additional Notes:

Inventory/Utilities/Recalculate Item History - Once the utility has completed, the Recalculate Item History must be run to correct the PeriodPostingHistory, ItemWarehouseHistoryByPeriod, ItemCustomerHistoryByPeriod, ItemVendorHistoryByPeriod and the ItemTransactionRecalc tables. The ItemTransactionHistory table is used to repopulate these history tables.

Trial Balance and Valuation By Period reports - These reports using the PeriodPostingHistory table. The PeriodPostingHistory table contains the Beginning Balance and PeriodChangeQty and PeriodChangeDollarAmts which should match the Detail Transaction Report when run for the same period.

Valuation report - This report uses the ItemWarehouse TotalQuantityOnHand multiplied by the UnitCost to calculate the ExtendedCost.

DocLink: How to run the IM_CommitCheckClear_utl and IM_ClearCommitDataEntry_utl utilities in versions 4.40 and higher