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)

• Entered : 09/30/2010
• Version :