Table of Contents

Explanation of SQL

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.

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

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

Version Information