{$page}

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.

code_formatsql

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

code

• Entered : 09/30/2010
• Version :

• Backlinks include_pagepage_componentbacklinks