Table of Contents

Explanation of SQL

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 prior to the version with Warehouses. If the Control database is pre-Profits view the inventory_out_of_balance_repair_-_pre-profits page. If the Control database is post-Profits view the inventory_out_of_balance_repair page.

Risk of Data Corruption if Run Improperly

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.

SQL

--01
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
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
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) )
;
 
--04
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 ( ( 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
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) )
;
 
--06
UPDATE InventoryLog
SET    QuantityOnHand = COALESCE(QuantityBilled,0)+COALESCE(QuantityReceivedOnly,0)
WHERE  ABS( (COALESCE(QuantityBilled,0)+COALESCE(QuantityReceivedOnly,0)) - COALESCE(QuantityOnHand,0) ) > 0.00001
;
 
--07
UPDATE InventoryLog
SET    QuantityAvailable = COALESCE(QuantityOnHand,0)-COALESCE(QuantityReserved,0)
WHERE  ABS( (COALESCE(QuantityOnHand,0)-COALESCE(QuantityReserved,0)) - COALESCE(QuantityAvailable,0) ) > 0.00001
;
 
--08
UPDATE InventoryLog
SET    QuantityExpected = COALESCE(QuantityAvailable,0)+COALESCE(QuantityOnOrder,0)
WHERE  ABS( (COALESCE(QuantityAvailable,0)+COALESCE(QuantityOnOrder,0)) - COALESCE(QuantityExpected,0) ) > 0.00001
;
 
--09
UPDATE Inventory
SET    QuantityOnHand = COALESCE(QuantityBilled,0)+COALESCE(QuantityReceivedOnly,0)
WHERE  ABS( (COALESCE(QuantityBilled,0)+COALESCE(QuantityReceivedOnly,0)) - COALESCE(QuantityOnHand,0) ) > 0.00001
;
 
--10
UPDATE Inventory
SET    QuantityAvailable = COALESCE(QuantityOnHand,0)-COALESCE(QuantityReserved,0)
WHERE  ABS( (COALESCE(QuantityOnHand,0)-COALESCE(QuantityReserved,0)) - COALESCE(QuantityAvailable,0) ) > 0.00001
;
 
--11
UPDATE Inventory
SET    QuantityExpected = COALESCE(QuantityAvailable,0)+COALESCE(QuantityOnOrder,0)
WHERE  ABS( (COALESCE(QuantityAvailable,0)+COALESCE(QuantityOnOrder,0)) - COALESCE(QuantityExpected,0) ) > 0.00001

Version Information