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.
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.
–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)