Table of Contents

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