I use a similar query (see below) to compare timWhseBinInvt, timInvtLotBin, and timCostTier (I hadn't noticed timInvtTranCost before). I run the query through sp_send_dbmail nightly so I'll get an email if things are off.

SELECT  WhseID, ItemID, ISNULL((
                                 SELECT SUM(QtyOnHand)
                                 FROM   dbo.timWhseBinInvt
                                        JOIN dbo.timWhseBin
                                            ON dbo.timWhseBinInvt.WhseBinKey = dbo.timWhseBin.WhseBinKey
                                 WHERE  dbo.timWhseBin.WhseKey = dbo.timInventory.WhseKey
                                        AND dbo.timWhseBinInvt.ItemKey = dbo.timInventory.ItemKey
                               ), 0) [timWhseBinInvt], ISNULL((
                                                                SELECT  SUM(OrigQty - QtyUsed)
                                                                FROM    dbo.timInvtLotBin
                                                                        JOIN dbo.timInvtLot
                                                                            ON dbo.timInvtLotBin.InvtLotKey = dbo.timInvtLot.InvtLotKey
                                                                WHERE   dbo.timInvtLot.WhseKey = dbo.timInventory.WhseKey
                                                                        AND dbo.timInvtLot.ItemKey = dbo.timInventory.ItemKey
                                                              ), 0) [timInvtLotBin], ISNULL((
                                                                                              SELECT    SUM(OrigQty - QtyUsed)
                                                                                              FROM      dbo.timCostTier
                                                                                              WHERE     dbo.timCostTier.WhseKey = dbo.timInventory.WhseKey
                                                                                                        AND dbo.timCostTier.ItemKey = dbo.timInventory.ItemKey
                                                                                            ), 0) [timCostTier], '', '', '', '', '', '', '', '', '', '', '', '',
        '', '', '', ''
FROM    dbo.timInventory
        JOIN dbo.timWarehouse
            ON dbo.timInventory.WhseKey = dbo.timWarehouse.WhseKey
        JOIN dbo.timItem
            ON dbo.timInventory.ItemKey = dbo.timItem.ItemKey
WHERE   ISNULL((
                 SELECT SUM(QtyOnHand)
                 FROM   dbo.timWhseBinInvt
                        JOIN dbo.timWhseBin
                            ON dbo.timWhseBinInvt.WhseBinKey = dbo.timWhseBin.WhseBinKey
                 WHERE  dbo.timWhseBin.WhseKey = dbo.timInventory.WhseKey
                        AND dbo.timWhseBinInvt.ItemKey = dbo.timInventory.ItemKey
               ), 0) <> ISNULL((
                                 SELECT SUM(OrigQty - QtyUsed)
                                 FROM   dbo.timInvtLotBin
                                        JOIN dbo.timInvtLot
                                            ON dbo.timInvtLotBin.InvtLotKey = dbo.timInvtLot.InvtLotKey
                                 WHERE  dbo.timInvtLot.WhseKey = dbo.timInventory.WhseKey
                                        AND dbo.timInvtLot.ItemKey = dbo.timInventory.ItemKey
                               ), 0)
        OR ISNULL((
                    SELECT  SUM(QtyOnHand)
                    FROM    dbo.timWhseBinInvt
                            JOIN dbo.timWhseBin
                                ON dbo.timWhseBinInvt.WhseBinKey = dbo.timWhseBin.WhseBinKey
                    WHERE   dbo.timWhseBin.WhseKey = dbo.timInventory.WhseKey
                            AND dbo.timWhseBinInvt.ItemKey = dbo.timInventory.ItemKey
                  ), 0) <> ISNULL((
                                    SELECT  SUM(OrigQty - QtyUsed)
                                    FROM    dbo.timCostTier
                                    WHERE   dbo.timCostTier.WhseKey = dbo.timInventory.WhseKey
                                            AND dbo.timCostTier.ItemKey = dbo.timInventory.ItemKey
                                  ), 0)
        OR ISNULL((
                    SELECT  SUM(OrigQty - QtyUsed)
                    FROM    dbo.timInvtLotBin
                            JOIN dbo.timInvtLot
                                ON dbo.timInvtLotBin.InvtLotKey = dbo.timInvtLot.InvtLotKey
                    WHERE   dbo.timInvtLot.WhseKey = dbo.timInventory.WhseKey
                            AND dbo.timInvtLot.ItemKey = dbo.timInventory.ItemKey
                  ), 0) <> ISNULL((
                                    SELECT  SUM(OrigQty - QtyUsed)
                                    FROM    dbo.timCostTier
                                    WHERE   dbo.timCostTier.WhseKey = dbo.timInventory.WhseKey
                                            AND dbo.timCostTier.ItemKey = dbo.timInventory.ItemKey
                                  ), 0)
ORDER BY WhseID, ItemID

How about corrective actions? What are your experiences with the stored procedures spimRecalcInvtHist and spimRecalcPeriodHist?

I ran those two queries in our test environment and didn't notice any corrections.

Our organization has never investigated discrepancies before, so over our 6 years of use we have a large number of discrepancies. In some cases the CostTier Qty or WhseBin Qty will match our Tran History, and it's as simple as updating the odd one out. In other cases neither match. In some cases I'm wondering if the best action is to perform a physical count.

Investigating the history for something obvious or performing a physical count are the only two solutions that I'm aware of as well. But you still have to go one by one to resolve.

The stored procedures that you mention only update the history tables, not the "on hand" tables. This is useful though for the Inventory Valuation report because it reads from the history tables for transactions before the beginning date that you select.

Also, you might want to schedule spimRecalcInvenQtyOnForAll to update some of the timInventory quantities.

The

I had seen a script that attempted to correct quantities via timInvtTran history, but it seems to be an unreliable method. I do like the idea of the running the discrepancy report on a nightly basis and emailing results. I believe we will adopt that strategy as well.