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

• Entered : 09/30/2010
• Version :