Due to various issues in the past, it is possible for the numbers in Inventory table to not match with sum of the numbers in the Inventory Log table. This query will edit the Inventory table to match the Inventory Log table.

Notes: These queries are designed for Control databases updated with Warehouses, but before the changes for Profits. If the Control database is post-Profits view the inventory_out_of_balance_repair page. If the Control database is pre-Warehouses view the inventory_out_of_balance_repair - pre-Warehouses page.

High. Data is modified in this query. Do not run this except under the direction of a Cyrious Technical Support staff member. Doing otherwise may result in lost or contaminated data. All data modifications done through direct SQL are permanent and non-reversable.

--01
--Fix InventoryLog Entries with incorrect InventoryID due to old defect
UPDATE InventoryLog
SET InventoryID = TD.AttachedOrderInvID
FROM InventoryLog IL
     LEFT JOIN VendorTransDetail TD ON (TD.ID = IL.TransDetailID)
WHERE IL.TransDetailClassTypeID = 11100
      AND ( IL.InventoryID < 0 OR IL.InventoryID IS NULL )
      AND TD.AttachedOrderInvID > 0
;
 
--02
--Fix InventoryLog Entries with incorrect InventoryID due to old defect
UPDATE InventoryLog
SET InventoryID = TP.InventoryID
FROM InventoryLog IL
     LEFT JOIN VendorTransDetail TD ON (TD.ID = IL.TransDetailID)
     LEFT JOIN TransPart TP ON (TP.ID = TD.AttachedOrderPartID)
WHERE IL.TransDetailClassTypeID = 11100
      AND ( IL.InventoryID < 0 OR IL.InventoryID IS NULL )
      AND TP.InventoryID > 0
;
 
--03
--Round all InventoryLog amount to 4 digits and convert NULLs to zeros
UPDATE InventoryLog
SET    QuantityBilled = ROUND(COALESCE(QuantityBilled,0), 4),
       QuantityReceivedOnly = ROUND(COALESCE(QuantityReceivedOnly,0), 4),
       QuantityReserved = ROUND(COALESCE(QuantityReserved,0), 4),
       QuantityOnOrder = ROUND(COALESCE(QuantityOnOrder,0), 4)
FROM InventoryLog
WHERE  ( COALESCE(QuantityBilled,0) <> ROUND(COALESCE(QuantityBilled,0), 4) )
       OR ( COALESCE(QuantityReceivedOnly,0) <> ROUND(COALESCE(QuantityReceivedOnly,0), 4) )
       OR ( COALESCE(QuantityReserved,0) <> ROUND(COALESCE(QuantityReserved,0), 4) )
       OR ( COALESCE(QuantityOnOrder,0) <> ROUND(COALESCE(QuantityOnOrder,0), 4) )
       OR QuantityBilled IS NULL OR  QuantityReceivedOnly IS NULL
       OR QuantityReserved IS NULL OR QuantityOnOrder IS NULL
;
--04
--Recompute InventoryItem Amounts to be in sync with the InventoryLog entries
UPDATE Inventory
SET    QuantityBilled = COALESCE(IL.QuantityBilled,0),
       QuantityReceivedOnly = COALESCE(IL.QuantityReceivedOnly,0),
       QuantityReserved = COALESCE(IL.QuantityReserved,0),
       QuantityOnOrder = COALESCE(IL.QuantityOnOrder,0)
FROM   Inventory I 
       LEFT JOIN (SELECT   InventoryID, PartID,
                           ROUND(SUM(QuantityBilled),4) QuantityBilled, 
                           ROUND(SUM(QuantityReceivedOnly),4) QuantityReceivedOnly, 
                           ROUND(SUM(QuantityReserved),4) QuantityReserved, 
                           ROUND(SUM(QuantityOnOrder),4) QuantityOnOrder
                  FROM     InventoryLog 
                  WHERE    InventoryID IS NOT NULL
                  GROUP BY InventoryID, PartID ) IL ON (IL.InventoryID = I.ID)
WHERE I.ClassTypeID = 12200 AND
      ( ( ABS(COALESCE(I.QuantityBilled, 0) - COALESCE(IL.QuantityBilled, 0)) > 0.00001 )
        OR ( ABS(COALESCE(I.QuantityReceivedOnly, 0) - COALESCE(IL.QuantityReceivedOnly, 0)) > 0.00001 )
        OR ( ABS(COALESCE(I.QuantityReserved, 0) - COALESCE(IL.QuantityReserved, 0)) > 0.00001 )
        OR ( ABS(COALESCE(I.QuantityOnOrder, 0) - COALESCE(IL.QuantityOnOrder, 0)) > 0.00001 ) )
;
--05
--Recompute Inventory Division Summary Amounts to be in sync with the sum of the Inventory entries
UPDATE Inventory
SET    QuantityBilled = COALESCE(ID.QuantityBilled,0),
       QuantityReceivedOnly = COALESCE(ID.QuantityReceivedOnly,0),
       QuantityReserved = COALESCE(ID.QuantityReserved,0),
       QuantityOnOrder = COALESCE(ID.QuantityOnOrder,0)
FROM   Inventory I 
       LEFT JOIN (SELECT   PartID, WarehouseID, DivisionID,
                           ROUND(SUM(QuantityBilled),4) QuantityBilled, 
                           ROUND(SUM(QuantityReceivedOnly),4) QuantityReceivedOnly, 
                           ROUND(SUM(QuantityReserved),4) QuantityReserved, 
                           ROUND(SUM(QuantityOnOrder),4) QuantityOnOrder
                  FROM     Inventory 
                  WHERE    ClassTypeID = 12200
                  GROUP BY PartID, WarehouseID, DivisionID ) ID ON (ID.PartID = I.PartID AND ID.DivisionID = I.DivisionID)
WHERE I.ClassTypeID = 12201 AND
      ( ( ABS(COALESCE(I.QuantityBilled, 0) - COALESCE(ID.QuantityBilled, 0)) > 0.00001 )
        OR ( ABS(COALESCE(I.QuantityReceivedOnly, 0) - COALESCE(ID.QuantityReceivedOnly, 0)) > 0.00001 )
        OR ( ABS(COALESCE(I.QuantityReserved, 0) - COALESCE(ID.QuantityReserved, 0)) > 0.00001 )
        OR ( ABS(COALESCE(I.QuantityOnOrder, 0) - COALESCE(ID.QuantityOnOrder, 0)) > 0.00001 ) )
;
--06
--Recompute Inventory Warehouse Summary Amounts to be in sync with the sum of the Inventory entries
UPDATE Inventory
SET    QuantityBilled = COALESCE(IW.QuantityBilled,0),
       QuantityReceivedOnly = COALESCE(IW.QuantityReceivedOnly,0),
       QuantityReserved = COALESCE(IW.QuantityReserved,0),
       QuantityOnOrder = COALESCE(IW.QuantityOnOrder,0)
FROM   Inventory I 
       LEFT JOIN (SELECT   PartID, WarehouseID, 
                           ROUND(SUM(QuantityBilled),4) QuantityBilled, 
                           ROUND(SUM(QuantityReceivedOnly),4) QuantityReceivedOnly, 
                           ROUND(SUM(QuantityReserved),4) QuantityReserved, 
                           ROUND(SUM(QuantityOnOrder),4) QuantityOnOrder
                  FROM     Inventory 
                  WHERE    ClassTypeID = 12200
                  GROUP BY PartID, WarehouseID ) IW ON (IW.PartID = I.PartID AND IW.WarehouseID = I.WarehouseID)
WHERE I.ClassTypeID = 12202 AND
      ( ( ABS(COALESCE(I.QuantityBilled, 0) - COALESCE(IW.QuantityBilled, 0)) > 0.00001 )
        OR ( ABS(COALESCE(I.QuantityReceivedOnly, 0) - COALESCE(IW.QuantityReceivedOnly, 0)) > 0.00001 )
        OR ( ABS(COALESCE(I.QuantityReserved, 0) - COALESCE(IW.QuantityReserved, 0)) > 0.00001 )
        OR ( ABS(COALESCE(I.QuantityOnOrder, 0) - COALESCE(IW.QuantityOnOrder, 0)) > 0.00001 ) )
;
--07
--Round all Inventory amount to 4 digits and convert NULLs to zeros
UPDATE Inventory
SET    QuantityBilled = ROUND(COALESCE(QuantityBilled,0),4),
       QuantityReceivedOnly = ROUND(COALESCE(QuantityReceivedOnly,0),4),
       QuantityReserved = ROUND(COALESCE(QuantityReserved,0),4),
       QuantityOnOrder = ROUND(COALESCE(QuantityOnOrder,0),4)
WHERE  ( COALESCE(QuantityBilled,0) <> ROUND(COALESCE(QuantityBilled,0), 4) )
       OR ( COALESCE(QuantityReceivedOnly,0) <> ROUND(COALESCE(QuantityReceivedOnly,0), 4) )
       OR ( COALESCE(QuantityReserved,0) <> ROUND(COALESCE(QuantityReserved,0), 4) )
       OR ( COALESCE(QuantityOnOrder,0) <> ROUND(COALESCE(QuantityOnOrder,0), 4) )
;
 
--08
--Recompute the QuantityOnHand amounts in the InventoryLog to equal QuantityBilled+QuantityReceivedOnly
UPDATE InventoryLog
SET    QuantityOnHand = COALESCE(QuantityBilled,0)+COALESCE(QuantityReceivedOnly,0)
WHERE  ABS( (COALESCE(QuantityBilled,0)+COALESCE(QuantityReceivedOnly,0)) - COALESCE(QuantityOnHand,0) ) > 0.00001
;
 
--09
--Recompute the QuantityAvailable amounts in the InventoryLog to equal QuantityOnHand+QuantityReserved
UPDATE InventoryLog
SET    QuantityAvailable = COALESCE(QuantityOnHand,0)-COALESCE(QuantityReserved,0)
WHERE  ABS( (COALESCE(QuantityOnHand,0)-COALESCE(QuantityReserved,0)) - COALESCE(QuantityAvailable,0) ) > 0.00001
;
 
--10
--Recompute the QuantityExpected amounts in the InventoryLog to equal QuantityAvailable+QuantityOnOrder
UPDATE InventoryLog
SET    QuantityExpected = COALESCE(QuantityAvailable,0)+COALESCE(QuantityOnOrder,0)
WHERE  ABS( (COALESCE(QuantityAvailable,0)+COALESCE(QuantityOnOrder,0)) - COALESCE(QuantityExpected,0) ) > 0.00001
;
 
--11
--Recompute the QuantityOnHand amounts in the Inventory to equal QuantityBilled+QuantityReceivedOnly
UPDATE Inventory
SET    QuantityOnHand = COALESCE(QuantityBilled,0)+COALESCE(QuantityReceivedOnly,0)
WHERE  ABS( (COALESCE(QuantityBilled,0)+COALESCE(QuantityReceivedOnly,0)) - COALESCE(QuantityOnHand,0) ) > 0.00001
;
 
--12
--Recompute the QuantityAvailable amounts in the Inventory to equal QuantityOnHand+QuantityReserved
UPDATE Inventory
SET    QuantityAvailable = COALESCE(QuantityOnHand,0)-COALESCE(QuantityReserved,0)
WHERE  ABS( (COALESCE(QuantityOnHand,0)-COALESCE(QuantityReserved,0)) - COALESCE(QuantityAvailable,0) ) > 0.00001
;
 
--13
--Recompute the QuantityExpected amounts in the Inventory to equal QuantityAvailable+QuantityOnOrder
UPDATE Inventory
SET    QuantityExpected = COALESCE(QuantityAvailable,0)+COALESCE(QuantityOnOrder,0)
WHERE  ABS( (COALESCE(QuantityAvailable,0)+COALESCE(QuantityOnOrder,0)) - COALESCE(QuantityExpected,0) ) > 0.00001
  • Entered : 09/30/2010
  • Version :
You could leave a comment if you were logged in.