How to run the IM Balance Quantity and Cost-Utility in Versions 4.40 and Higher for Sage 100

Description

How to run the IM Balance Quantity and Cost-Utility 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.

Resolution

For versions 4.40.0.8 and 4.50.2.0 and higher:

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.

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 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_GenAverageCostXXX.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:
    • IM ItemTransactionHistory table (these would be records that IZ transactions were created)
    • IM ItemWarehouse TotalWarehouseValue, QuantityOnHand, AverageCost changes
    • CI Item TotalInventoryValue, TotalQuantityOnHand, AverageUnitCost changes
21. Run the Valuation, Trial Balance, and the Valuation By Period Reports for comparison.

Utility Logic Processing

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_GenAverageCost_utl”

Additional Information:

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.

Support

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