Due to various issues in the past, it is possible for the QuantityReceivedOnly total to be negative, even though this should never be the case. This query will go through the InventoryLog table and shift the QuantityReceivedOnly adjustment to QuantityBilled when it would QuantityReceivedOnly to go negative.

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.

This query may take an extremely long time. It can be broken down into smaller pieces if timeouts are an issue. This can be done by changing the value of @MaxIterations in the second line to something over that -1 (25 is a good number to start with). You'll have to run the query over and over again until the query returns an empty result set.

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 sql> DECLARE @MaxIterations INT; SET @MaxIterations = -1; DECLARE @InvLogTbl TABLE(ID INT PRIMARY KEY, PartID INT, WarehouseID INT, QuantityReceivedOnly FLOAT, SumReceivedOnly FLOAT); DECLARE @Cnt INT SET @Cnt = 0; WHILE @MaxIterations = -1 OR @Cnt < @MaxIterations BEGIN

SET @Cnt = @Cnt + 1;

DELETE FROM @InvLogTbl;

INSERT INTO @InvLogTbl
(ID, PartID, WarehouseID, QuantityReceivedOnly)
SELECT ID, PartID, ToWarehouseID, QuantityReceivedOnly
FROM InventoryLog
WHERE ID > 0 AND QuantityReceivedOnly  0 AND PartID IS NOT NULL 
;
UPDATE @InvLogTbl
SET SumReceivedOnly = (SELECT SUM( QuantityReceivedOnly ) FROM @InvLogTbl T WHERE T.PartID = IL.PartID AND T.WarehouseID = IL.WarehouseID AND T.ID  0 AND QuantityReceivedOnly  0 AND PartID IS NOT NULL 

; UPDATE @InvLogTbl SET SumReceivedOnly = (SELECT SUM( QuantityReceivedOnly ) FROM @InvLogTbl T WHERE T.PartID = IL.PartID AND T.WarehouseID = IL.WarehouseID AND T.ID

You could leave a comment if you were logged in.