Sage 100

Customer has a major issue with inventory FIFO tiers being overstated from quantity on hand for hundreds of items e.g. ItemWhse for one item shows a qty on hand of 171 while the cost detail shows 1132. I ran a detail transaction report for this item and the correct qty should be 171, thus the cost detail is incorrect (and they physically went out and counted this item). This is true for over 1,000 items. They always reconcile to GL using the IM TB which they believe to be correct. It shows a total value of ~2.1M while the valuation report shows a total of ~3.5M. That's a HUGE difference! They are currently at v2018 PU3 Advanced but this has been happening since at least 2016 (the farthest back we have a copy). They do have Sage CRM integrated with it but I don't believe they are entering anything through CRM. They also have SIA, DSD's warehouse tfr utility, and StarShip, but I don't see anything with those (unless I'm missing something?). So what would be skewing the cost detail records?? And why does the balancing utility update based on the cost detail only?? (Remember the IMZBL1 utility where you could choose which one you wanted to correct?) TIA!!!

Do they import any IM transactions?

Issues, yes. What are you thinking @DianaLaughner?

I wonder if it's still hanging on from 4.x (assuming they were on that version at some point) because Sage had all sorts of inventory issues back then.

We had an issue with a client with importing WO materials and cost tiers not being relieved correctly. Sorry, can't remember which version.

It was something really strange. It was either that they went into the imported transactions, didn't have to make any changes, then it was fine, or it was the other way around, if they didn't go into it, then it was fine.

Yes, they were @JennySearl but it looks like something is still happening, at least from 2016 to 2018. While they are still overstated, it's by different amounts.

Do you think the user who updates sales journals could affect whether or not cost detail is relieved?? Stretching here but in the last week the differences have changed and it MAY be due to who updated the journal. Network security perhaps??

Do you have any cost tiers with blank tier type (or the wrong tier type)? We've seen that cause problems before.

no, but I think I'm narrowing it down. This customer uses ACS/Scanco's In Transit Transfer enhancement which creates an SO and a PO. The SO gets updated in the sales journal but does not actually create an invoice. Instead, it creates an IT transaction. It appears invoices following that 'dummy' invoice on the sales journal are not being distributed correctly. (Fortunately, they just moved servers on 10/20 so I can look back at the difference in what IM_ItemWhse shows as QOH vs. the cost detail on both servers and the differences are growing.) I looked at transactions for items where the difference has increased this past week and found several which were updated by a particular user but when I look at the sales journal, some invoices updated with no increase in differences then saw the transfer invoice and everything after that was affected. Just emailed Ron to see if he is aware of anything like this.

Do the correct entries get created in IM_LotSerialTransactionHistory? If so, perhaps recalculating IM history might help (in a test company of course).

Isn't that only for Lot/Serial items? These are FIFO.

Of course... I was thinking cost tiers and my brain just went to lot serial history. :-/ I assume the *utl is what you refer to as the balancing utility... so I've run out of ideas.

I tested it and yep, entering a transfer through the enhancement and then a regular invoice causes the regular invoice to not have any tiers relieved. Hopefully, going to connect with Ron and show him. He said he tested it and it worked correctly but I've done a test twice now and both times were the same.

Of course he has tested it. It MUST be something else....... Don't forget to press him on a utility fix for all the bad data to be cleaned up.

I'd be happy if he admits it and finds a fix for it going forward.

Well he saw for himself that it is an issue. He asked why they don't update them in separate batches to which I said, ""Is that in the instructions, to update them separately?"" ""Well no, but most people do."" Apparently, when the sales journal is updating and it gets to the transfer invoice, it has to decide how to process it, as a transfer or as a regular invoice? Obviously, something gets messed up if there are regular invoices after it. He thought his programmers fixed it but he said he would recreate it (based on what I showed him) and then have his programmers look at it again. ""You must be the first one to notice it."" Well when your cost detail is overstated by close to $1.5M, it bears some investigating. It is very, very fortunate they just moved to a new server and I still have access to the old one. I don't know how one would figure it out otherwise!

It's pretty scary how few of our customer actually pay attention to little details like matching up accounts with their detail, even when you bring it to their attention and show them how to do it...

@PhilMcIntosh - Customers pay attention???? The only time mine notice anything is when I upgrade/migrate and all of a sudden, a issue that has been around for days/weeks/months/years is noticed. I must have done something....... Of course.

@PhilMcIntosh they are meticulous about balancing their IM Trial Balance to the GL but NOT the valuation report.

Ron was able to duplicate it and is turning it over to programming.