Due to various issues in the past, it is possible for their to be duplicate Inventory Warehouse Summary and Inventory Division Summary Records. This query will merge the duplicates into a single record.
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.
DECLARE @DupeInvTbl TABLE(InventoryID INT PRIMARY KEY, InventoryCount INT, ClassTypeID INT, WarehouseID INT, DivisionID INT, PartID INT);
INSERT INTO @DupeInvTbl
SELECT MIN(ID) InventoryID, COUNT(1) InventoryCount,
ClassTypeID,
COALESCE( WarehouseID, 10 ) WarehouseID,
COALESCE( DivisionID, 10 ) DivisionID, PartID
FROM Inventory
WHERE ID > 0 AND ClassTypeID in (12200, 12201, 12202)
GROUP BY COALESCE( WarehouseID, 10 ), COALESCE( DivisionID, 10 ), ClassTypeID, 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 AND ID.ClassTypeID = 12200)
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 AND ID.ClassTypeID = 12200)
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 AND ID.ClassTypeID = 12200)
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 AND ID.ClassTypeID = 12200)
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 AND ID.ClassTypeID = 12200)
WHERE GL.InventoryID ID.InventoryID
– Delete the duplicate items
DELETE FROM Inventory
FROM Inventory I JOIN @DupeInvTbl ID ON ( I.PartID = ID.PartID AND COALESCE(I.DivisionID, 10) = ID.DivisionID AND COALESCE(I.WarehouseID, 10) = ID.WarehouseID AND I.ClassTypeID = ID.ClassTypeID )
WHERE I.ID ID.InventoryID