Manufacturing, Distribution, and Internet Applications

[GopherGold]

This past weekend we upgraded from 7.05 April 2007 to 7.3 August 2010.  We are receiving cost tier errors, exactly as described in KB Resolution 524188.  Anyone out there experience this also, and is there a programmatic resolution to the issue?  We have 2,000+ cost tier adjustments to post manually otherwise.

Thanks.

[GopherGold]

A reply to my own topic here...

We proceeded with manual cost tier updates, which helped somewhat, but we are still getting this error.  What I find interesting/troubling is the cost tier record referenced in tciSQLExceptionLog looks fine to me.  OrigQty > QtyUsed, all costs are 0.00 or positive.  timWhseBinInvt.QtyOnHand = OrigQty-QtyUsed.  I have to take the item, process a Cost Tier Adjustment and post.  In the CT adjustment, I don't actually change the cost, I just put it in the batch and post.  The inventory batch (Assembled Kits) in this case then errors on another item - same case.  I repeat the CT adjustment process until it posts.

If I knew how to identify items that are likely to cause this error, perhaps I could pre-emptively make CT adjustments (assuming this doesn't come back again on the same item).  It would be nice to know what is happening, or if anyone else out there is experiencing this.

[JohnHanrahan]

Have you contacted support?  This sounds like a bug.

[GopherGold]

We have Bronze support, so that isn't an option.  I am in-process of getting a quote to upgrade to Silver.  To bad Sage doesn't have an a la carte support option...

[LouDavis]

You can always have your reseller open a support case on your behalf to have the issue looked at right away while you look into upgrading to a Silver plan.

[GopherGold]

Well, management doesn't want to shell out several thousand dollars for Silver support right now, since this is the first time in years we've considered needing technical support.

Here's my theory - the changes to cost tier updates (remove pending increase/decrease columns, increase transactional integrity) have become more restrictive and exposed bad data in timCostTier.  Validation at Ln733 in spimCTSaveCostTierEffects is causing the error.  We have a long history of managing costs and inventory values outside of the system (GL is the truth only), which has led to timCostTier.TotMaterialCost to be quite inaccurate.  

So, as far as I'm concerned, this isn't a bug, but a change in behavior.  Just wanted to share my thoughts for those of you following along at home.

[JohnHanrahan]

One other final option might be buying a single support case.  I'm pretty sure they sell them and as I recall they are around 300 dollars each. 

[GopherGold]

That was my first approach.  Support told me they do not offer single case options.  Believe me, for $300 we would have been down that road!

[gstorres]

Following is the script to identify the warehouse and item involved.  You can use the result to create cost tier adjustment to "right" the tiers involved.  Unfortunately, this will only show you the cost tiers that currently have the problem.  As you know by reading the comments on the line of code that raises the error, this validation is very necessary.  Please do not try to comment it out.

SELECT w.WhseID, i.ItemKey, ct.OrigQty, ct.QtyUsed, ct.OrigQty - ct.QtyUsed 'QtyOnHand',
        ct.TotMaterialCost, ct.TotFreightCost, ct.TotSTaxCost, ct.TotOtherCost, 
        ct.TotRunFixCost, ct.TotRunVarCost, ct.TotSetFixCost, ct.TotSetVarCost, 
        ct.TotOutsideCost, ct.TotOtherMfgCost
FROM timCostTier ct
JOIN timItem i ON ct.ItemKey = i.ItemKey
JOIN timWarehouse w ON ct.WhseKey = w.WhseKey
      WHERE (ct.OrigQty < 0 OR ct.QtyUsed < 0) OR
      (ct.OrigQty - ct.QtyUsed <> 0 AND 
       ((sign(OrigQty-QtyUsed) <> sign(ct.TotMaterialCost) and ct.TotMaterialCost <> 0) OR
        (sign(OrigQty-QtyUsed) <> sign(ct.TotFreightCost) and ct.TotFreightCost <> 0) OR
        (sign(OrigQty-QtyUsed) <> sign(ct.TotSTaxCost) and ct.TotSTaxCost <> 0) OR
        (sign(OrigQty-QtyUsed) <> sign(ct.TotOtherCost) and ct.TotOtherCost <> 0) OR
        (sign(OrigQty-QtyUsed) <> sign(ct.TotRunFixCost) and ct.TotRunFixCost <> 0) OR
        (sign(OrigQty-QtyUsed) <> sign(ct.TotRunVarCost) and ct.TotRunVarCost <> 0) OR
        (sign(OrigQty-QtyUsed) <> sign(ct.TotSetFixCost) and ct.TotSetFixCost <> 0) OR
        (sign(OrigQty-QtyUsed) <> sign(ct.TotSetVarCost) and ct.TotSetVarCost <> 0) OR
        (sign(OrigQty-QtyUsed) <> sign(ct.TotOutsideCost) and ct.TotOutsideCost <> 0) OR
        (sign(OrigQty-QtyUsed) <> sign(ct.TotOtherMfgCost) and ct.TotOtherMfgCost <> 0) ) )

 A trick I've seen is to expand the error message to include the actual values of the quantity and total cost so it can be recorded in tciSqlExceptionLog.  Often you don't know which of the cost tier's amount is causing the error because it has been rolled back.  If you can somehow replicate the problem on a new item, provide me with the exact steps and I will look into it.

[GopherGold]

Thank you for the reply, Gerard.  Exciting in a way, as I see you are the author of the stored procedure in question (wow, that came off *really* nerdy).

I had already done as you suggested- created a query based on the code from the stored procedure, and enhanced the error message with additional data.  It was enhancing the error message that led me to the conclusion there isn't a bug, rather a lot of erroneous data in our cost tier table.  

I assume this validation is much stricter compared to v7.05.  It must be, because our cost tier data was just as bad before the upgrade...

Is there a "spDF fix broken cost tiers" procedure out there?  I have 2900+ cost tiers with bad data.