{$page}

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.

code_formatsql

–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)

code

• Entered : 09/30/2010
• Version :

• Backlinks include_pagepage_componentbacklinks