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 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.
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 --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
Version Information
- Entered : 09/30/2010
- Version :