Explanation of SQL
Due to various issues in the past, it is possible for their to be duplicate Inventory Warehouse Item Records. This query will merge the duplicates into a single record. It will also update the IDs in other tables linking to the deleted Inventory Items
After running this query, it is necessary to run the inventory_out_of_balance_repair query to update the Inventory records to reflected the new numbers.
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
--Select the items with duplicate inventory records. SELECT Inventory.ClassTypeID, PartID, COALESCE(WarehouseID, 10) WarehouseID, COALESCE(DivisionID, 10) DivisionID, COUNT(1) DupeCount FROM Inventory WITH(NOLOCK) LEFT JOIN part ON inventory.partid = part.id WHERE PartID > 0 GROUP BY Inventory.ClassTypeID, parttype, PartID, COALESCE(WarehouseID, 10), COALESCE(DivisionID, 10) HAVING COUNT(1) > 1 ; -- Delete any Inventory records where the PartID does not exists. DELETE FROM Inventory WHERE ID > 0 AND NOT EXISTS(SELECT 1 FROM Part WHERE ID = Inventory.PartID) ; DECLARE @DupeInvTbl TABLE(InventoryID INT PRIMARY KEY, InventoryCount INT, WarehouseID INT, DivisionID INT, PartID INT); INSERT INTO @DupeInvTbl SELECT MIN(ID) InventoryID, COUNT(1) InventoryCount, COALESCE( WarehouseID, 10 ) WarehouseID, COALESCE( DivisionID, 10 ) DivisionID, PartID FROM Inventory WHERE ID > 0 AND ClassTypeID = 12200 GROUP BY COALESCE( WarehouseID, 10 ), COALESCE( DivisionID, 10 ), PartID ; -- update the InventoryID for the Transpart UPDATE Transpart SET InventoryID = ID.InventoryID FROM TransPart TP JOIN @DupeInvTbl ID ON (ID.PartID = TP.PartID AND ID.WarehouseID = TP.WarehouseID) WHERE TP.InventoryID ID.InventoryID -- update the InventoryID for the VendorTransDetail's AttachedOrderInvID UPDATE VendorTransDetail SET AttachedOrderInvID = ID.InventoryID FROM VendorTransDetail TD JOIN @DupeInvTbl ID ON (ID.PartID = TD.AttachedOrderPartID AND ID.WarehouseID = TD.WarehouseID) WHERE TD.AttachedOrderInvID ID.InventoryID -- update the InventoryID for the InventoryLog UPDATE InventoryLog SET InventoryID = ID.InventoryID FROM InventoryLog IL JOIN @DupeInvTbl ID ON (ID.PartID = IL.PartID AND ID.WarehouseID = IL.FromWarehouseID) WHERE IL.InventoryID ID.InventoryID ; UPDATE InventoryLog SET ToInventoryID = ID.InventoryID FROM InventoryLog IL JOIN @DupeInvTbl ID ON (ID.PartID = IL.PartID AND ID.WarehouseID = IL.ToWarehouseID) WHERE IL.ToInventoryID ID.InventoryID -- update the InventoryID for the Ledger UPDATE Ledger SET InventoryID = ID.InventoryID FROM Ledger GL JOIN @DupeInvTbl ID ON (ID.PartID = GL.PartID AND ID.WarehouseID = GL.WarehouseID) WHERE GL.InventoryID ID.InventoryID -- Delete the duplicate items DELETE FROM Inventory WHERE ClassTypeID = 12200 AND PartID IN (SELECT PartID FROM @DupeInvTbl) AND ID NOT IN (SELECT InventoryID FROM @DupeInvTbl)
Version Information
- Entered : 09/30/2010
- Version :
You could leave a comment if you were logged in.