Sage 100

Inventory File Comparison Report: What you you doing when you have upgrades and the comparison report kicks out items that need to be corrected? Item Warehouse Quantity on Hand Item Tier Quantity on Hand Item Transaction Quantity Which one do you adjust to? Why? Do you have anything you provide the customer than explains what this report is showing? I'd say on about 1/4 of my upgrades there's at least something on the report that needs correcting.

I always provide the generated reports from running these Utilities. For certain items, your only option is to use the On-Hand tier quantities (e.g., FIFO, Lot). For the rest, I check with the client, and most often we end up just using the On-Hand warehouse quantities. Then I expain that the necessary adjustments are then automatically created (see attached Inventory Balancing Utility Logs).

I'm also seeing that with large data sets (1+ GB) that if one value is always chosen as the more accurate - that it may make sense to skip the report printing since the balancing utility seems to take yet another lengthy period of time to run.

@BrettZimmerman Have you worked out a good explanation of what the three different options mean and why they occur? Most of the time the customer asks about the details - and so far I've not found any Sage explanations. I can only assume that the three different numbers are different ways the ""old"" Inventory measure quantities on hand and in some cases the numbers might have come out of sync. The problem I have is when the customer asks ""... in what cases? "" ...

The idea behind utility is it does a 3 way comparison of Qty On Hand in source system between IM2 IM3 and IM5 (rolled up for all trans types). @RobertaFerguson may know more but that is what I saw when I reviewed that code because client asked me exactly the same question once. There may be KB article too. I know over the years with some client systems On Hand is right in IM2 but not IM3 or the other way around (the clients where u ran IMZBL1 a lot). So the rpt and utility spit out items with discrepancies among the 3 and u get to decide what is correct. What Brett said on how to decids is pretty much what I do too. One really really important note is if u make corrections with the utility it makes the change in the source system and your ONLY audit trail is that text log file in source IMxxx folder. There is no journal or register. This came up when an upgrade to 4.40 came up and the client (public company) their auditor caught the discrepany in before / after Inventory reports 1 month later. Luckily we still had old system and he was okay with the text file. I learned my lesson.

I typically use a similar type of explanation, primarily focusing on how MAS Inventory files used to work and therefore it's not uncommon for the various qty-related files to get ""out of whack"" (technical phrase obviously that the client shouldn't be concerned with) - and that monthly inventory reconciliations usually uncover these issues sooner than later. Plus generally speaking there's also a number of things that could impact these qtys, incl but not limited to, network/data issues. And if the client asks for more reasoning, I explain that at this point it's difficult if not impossible to know exactly when and how the data got out of sync.

There were various program issues over the years that caused IM2 and IM3 to get out of whack and also the use of the FIX button. Whenever I would run IMZBL1 I would always compare IM5 also and advise that they all needed to agree. I know for a fact though that not everyone would check IM5 because that would require more work to correct.

I've gotten into the habit of creating test companies in the SOURCE file so that I can run through a test upgrade without making changes to the live data. When customer is advised and I know the data migration is ok then I go back and migrate the live inventory. I've been just sticking a T in front of the live company code when I do a copy within the old company. So company 001 has a T01, 002 has a T02. Then I migrate those Txx companies -- which is also handy because I tell the customer to check the Txx to the live Txx and be sure their data matches, etc. Without a Txx company in their production system there's no realistic way to compare before/after data unless the entire company stops using MAS.

Good info everyone thanks

@WayneSchulz - one issue with the Test Companies and parallel migrations is you don't get the custom forms/reports migrated. If there is disk space, I prefer to copy the MAS 90 folder and use it for the test migration. You can always run the copy in MAS 90 mode to check the ""before"" numbers. Then you can make all the system changes you need to (like security changes for new features), upgrade all the reports/forms in advance, and only have to migrate data for the actual ""go live"".

I thought everyone was making a copy of the mas90 folder for migration. How else do you migrate system files without kicking everyone out of the system?

For the test migration I am doing those after everyone has left for the day. Usually the live migration the people have to be out of the system anyways - so I'm assuming you're asking about test. I'm finding that the custom report migration is problematic. I hate like heck to have these upgrades where suddenly there'sl spare sets of MAS floating around on the server (which we truthfully delay removing due to nagging feeling we might just need the copy)...

I agree with Dawn, I make a system copy and make my changes there. Sometime you have to delete records in the system files to make them match to allow migration. Finally for go-live migration, I just copy the mas_xxx fom live to copy system and remigrate company data only. If they are using paper I will copy paperless files over manually ( possibly with additional migration.)

I always migrate off a ""copy"" of the MAS 90 folder, especially for a Test Upgrade - to minimize downtime and to allow people to continue working in their current version of MAS. And for a Test Upgrade, I also have them make a copy of their primary company(ies) inside their current version of MAS for data reconciliation purposes, since the Live data will change.

So what do y'all tell your clients to do going forward, once they're on 4.40 or higher, with respect to reconciling their Inventory Qty data and therefore preventing things from getting out-of-whack again? Client just asked if there's a report in 4.5 like the Pre-Migration Inventory File Comparison Report, i.e., 'Item Warehouse Quantity on Hand' vs. 'Item Tier Quantity on Hand' vs. 'Item Transaction Quantity'. Looks like you can run 'IM_BALANCEQTYONHAND_UI', however that appears to only compare 'Warehouse Quantity On Hand (IM_ItemWarehouse)' vs. 'Cost Quantity On Hand (IM_ItemCost)'. What about the Item Transaction Qtys? @RobertaFerguson?

The old IMZBL1 appears to do the same analysis as the new IM_BALANCEQTYONHAND_UI, i.e., Whse Detail Qty (IM2) vs. Costing File Qty (IM3). So again, what about the Item Transaction Qtys? Shouldn't those be of similar concern?

The ""Balance Inventory Quantity and Cost"" utility under *utl says that it is comparing the IM_ItemCost and IM_ItemTransactionHistory tables - doesn't say IM_ItemWarehouse.

The quantity in the item cost file is assumed to be correct and it will change the transactions and warehouse files to agree with the cost file.

Thanks, @RobertaFerguson. It appears you're referring to a different utility (i.e., ""Balance Inventory Quantity and Cost""). I was running IM_BALANCEQTYONHAND_UI - via File>Run (don't currently see either in the *utl screen). So I wasn't seeing any mention of ItemTransaction; everything I see says it's comparing Whse Detail and Cost Detail, including the Balance Quantity on Hand Listing report that appears when running the utility. So I guess the next question is whether there's any kind of real guidelines regarding when the one utility should be run vs. the other.

If I remember correctly, the one under *utl is the new one and should have corrected anything that was wrong in the one you are referring to. There have been so many utilities for inventory it is hard to keep track!

This is from the release notes for PU9: A new utility has been added: IM Balance Inventory Quantity and Cost. It is located on the *UTL menu. 19 DE 97903 PER 111899 Resolution ID 535727 The hidden utility IM_BalanceQtyOnHand_ui has been replaced with a new utility. The new utility is named IM Balance Inventory Quantity and Cost and is located on the *UTL menu. File/Run, type in the Program field *UTL, click OK. Select the new utility on the mAS 90 Utilities menu. 20 DE 97904

Thanks, @RobertaFerguson!

Question: Should this be run as standard on new upgrades ? Does it have the potential to harm data? I am **Very** apprehensive about changing inventory, having customer work in system for a month, then report that ""there's something all wrong"" ... This seems like a high risk area if underlying data changes in a manner the customer does not agree with...

It seems to be basically the same as what it does during the upgrade - it checks to make sure all 3 files agree. It does assume that the cost detail file is correct though and will make the changes to the other 2 files based on that so once it makes the changes if the cost detail is not correct then they need to do an adjustment and should understand that. If those files don't agree then you will have problems with the trial balance etc.

Back on the original question ""how do you explain the 3 choices to your client"" - I tell them that the Tier Qty is the one on their Inventory valuation report for FIFO/Lot/Ser, so I usually use that. Std / Avg the valuation report uses IM2 QOH. I""ve never used transaction qty as the guideline.