Due to various issues in the past, it is possible for the numbers in Inventory table to not match with sum of the numbers in the Inventory Log table. This query will edit the Inventory table to match the Inventory Log table.

Notes: These queries are designed for Control databases that have the inventory changes for Profits. If the Control database is pre-Profits view the inventory_out_of_balance_repair_-_pre-profits page. If the Control database is pre-Warehouses view the inventory_out_of_balance_repair_-_pre-warehouses page.

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.

PRINT '01)  Fix InventoryLog Entries with NULL DivisionIDs'
;
UPDATE IL
SET    InventoryID = TP.InventoryID,
       FromWarehouseID = TP.WarehouseID, IL.SeqID = IL.SeqID + 1
FROM   InventoryLog IL JOIN TransPart TP ON TP.ID = IL.TransPartID
WHERE  IL.InventoryID IS NULL AND IL.TransPartID IS NOT NULL
;
UPDATE InventoryLog
SET    FromWarehouseID = I.WarehouseID, SeqID = IL.SeqID + 1
FROM   InventoryLog IL
       JOIN Inventory I ON (I.ClassTypeID = 12200 AND I.ID = IL.InventoryID)
WHERE  ( IL.FromWarehouseID IS NULL OR IL.FromWarehouseID <> I.WarehouseID ) AND IL.InventoryID IS NOT NULL
;
UPDATE InventoryLog
SET    FromDivisionID = W.DivisionID, InventoryLog.SeqID = IL.SeqID + 1
FROM   InventoryLog IL
       JOIN Warehouse W ON (W.ClassTypeID = 12700 AND W.ID = IL.FromWarehouseID)
WHERE  ( IL.FromDivisionID IS NULL OR IL.FromDivisionID <> W.DivisionID ) AND IL.FromWarehouseID IS NOT NULL
;
PRINT '';
PRINT '';
 
PRINT '02)  Fix InventoryLog Entries with NULL WarehouseIDs'
;
UPDATE InventoryLog
SET FromWarehouseID = (SELECT TOP 1 ID FROM Warehouse WHERE DivisionID = InventoryLog.FromDivisionID ORDER BY IsDefault DESC, ID), SeqID = SeqID + 1
WHERE FromWarehouseID IS NULL
;
PRINT '';
PRINT '';
 
PRINT '03)  Fix InventoryLog Entries with incorrect InventoryID due to old defects'
;
UPDATE InventoryLog
SET InventoryID = TD.AttachedOrderInvID, SeqID = IL.SeqID + 1
FROM InventoryLog IL
     LEFT JOIN VendorTransDetail TD ON (TD.ID = IL.TransDetailID)
WHERE IL.TransDetailClassTypeID = 11100
      AND ( IL.InventoryID < 0 OR IL.InventoryID IS NULL )
      AND TD.AttachedOrderInvID > 0
;
 
UPDATE InventoryLog
SET InventoryID = TP.InventoryID, SeqID = IL.SeqID + 1
FROM InventoryLog IL
     LEFT JOIN VendorTransDetail TD ON (TD.ID = IL.TransDetailID)
     LEFT JOIN TransPart TP ON (TP.ID = TD.AttachedOrderPartID)
WHERE IL.TransDetailClassTypeID = 11100
      AND ( IL.InventoryID < 0 OR IL.InventoryID IS NULL )
      AND TP.InventoryID > 0
;
 
UPDATE IL
SET InventoryID = I.ID, IL.SeqID = IL.SeqID + 1
FROM InventoryLog IL
     LEFT JOIN Inventory I ON I.WarehouseID = IL.FromWarehouseID AND I.PartID = IL.PartID
WHERE I.ID IS NOT NULL AND IL.InventoryID <> ISNULL(I.ID,-1)
;
PRINT '';
PRINT '';
 
PRINT '04)  Round all InventoryLog amount to 4 digits and convert NULLs to zeros'
;
UPDATE InventoryLog
SET    QuantityBilled = ROUND(ISNULL(QuantityBilled,0), 4),
       QuantityReceivedOnly = ROUND(ISNULL(QuantityReceivedOnly,0), 4),
       QuantityReserved = ROUND(ISNULL(QuantityReserved,0), 4),
       QuantityOnOrder = ROUND(ISNULL(QuantityOnOrder,0), 4),
       SeqID = SeqID + 1
FROM InventoryLog
WHERE  ( ISNULL(QuantityBilled,0) <> ROUND(ISNULL(QuantityBilled,0), 4) )
       OR ( ISNULL(QuantityReceivedOnly,0) <> ROUND(ISNULL(QuantityReceivedOnly,0), 4) )
       OR ( ISNULL(QuantityReserved,0) <> ROUND(ISNULL(QuantityReserved,0), 4) )
       OR ( ISNULL(QuantityOnOrder,0) <> ROUND(ISNULL(QuantityOnOrder,0), 4) )
       OR QuantityBilled IS NULL OR  QuantityReceivedOnly IS NULL
       OR QuantityReserved IS NULL OR QuantityOnOrder IS NULL
;
PRINT '';
PRINT '';
 
PRINT '05)  Fix any inventory records with incorrect group settings'
;
UPDATE Inventory
SET    GroupID = T.GroupID,
       IsDivisionSummary = T.IsDivisionSummary,
       IsGroup = T.IsGroup,
       SeqID = SeqID + 1
FROM   Inventory I
       JOIN ( SELECT I.ID,
                     (SELECT ID FROM Inventory WHERE PartID = I.PartID AND WarehouseID = W.ParentID) GroupID,
                     W.IsDivisionSummary, W.IsGroup
              FROM   Inventory I
                     LEFT JOIN Warehouse W ON (W.ID = I.WarehouseID)
              WHERE  I.ID > 0 ) T ON T.ID = I.ID
WHERE  I.IsGroup <> T.IsGroup
       OR I.IsDivisionSummary <> T.IsDivisionSummary
       OR ISNULL( I.GroupID, -1 ) <> ISNULL( T.GroupID, -1 )
;
PRINT '';
PRINT '';
 
PRINT '06)  Recompute InventoryItem Amounts to be in sync with the InventoryLog entries'
;
UPDATE Inventory
SET    QuantityBilled = ISNULL(IL.QuantityBilled,0),
       QuantityReceivedOnly = ISNULL(IL.QuantityReceivedOnly,0),
       QuantityReserved = ISNULL(IL.QuantityReserved,0),
       QuantityOnOrder = ISNULL(IL.QuantityOnOrder,0),
       SeqID = SeqID + 1
FROM   Inventory I
       LEFT JOIN (SELECT   InventoryID,
                           ROUND(SUM(QuantityBilled),4) QuantityBilled,
                           ROUND(SUM(QuantityReceivedOnly),4) QuantityReceivedOnly,
                           ROUND(SUM(QuantityReserved),4) QuantityReserved,
                           ROUND(SUM(QuantityOnOrder),4) QuantityOnOrder
                  FROM     InventoryLog
                  WHERE    InventoryID IS NOT NULL
                  GROUP BY InventoryID ) IL ON (IL.InventoryID = I.ID)
WHERE I.ID > 0 AND I.IsGroup = 0 AND
      ( ( ABS(ISNULL(I.QuantityBilled, 0) - ISNULL(IL.QuantityBilled, 0)) > 0.00001 )
        OR ( ABS(ISNULL(I.QuantityReceivedOnly, 0) - ISNULL(IL.QuantityReceivedOnly, 0)) > 0.00001 )
        OR ( ABS(ISNULL(I.QuantityReserved, 0) - ISNULL(IL.QuantityReserved, 0)) > 0.00001 )
        OR ( ABS(ISNULL(I.QuantityOnOrder, 0) - ISNULL(IL.QuantityOnOrder, 0)) > 0.00001 ) )
;
PRINT '';
PRINT '';
 
PRINT '07)  Recompute Inventory Summary Amounts to be in sync with the sum of the Inventory entries'
;
DECLARE @I INT;
SET @I = 0;
 
WHILE (1=1)
BEGIN
  UPDATE Inventory
  SET    QuantityBilled = ISNULL(ID.QuantityBilled,0),
         QuantityReceivedOnly = ISNULL(ID.QuantityReceivedOnly,0),
         QuantityReserved = ISNULL(ID.QuantityReserved,0),
         QuantityOnOrder = ISNULL(ID.QuantityOnOrder,0),
         SeqID = SeqID + 1
  FROM   Inventory I
         LEFT JOIN (SELECT   GroupID,
                             ROUND(SUM(QuantityBilled),4) QuantityBilled,
                             ROUND(SUM(QuantityReceivedOnly),4) QuantityReceivedOnly,
                             ROUND(SUM(QuantityReserved),4) QuantityReserved,
                             ROUND(SUM(QuantityOnOrder),4) QuantityOnOrder
                    FROM     Inventory
                    WHERE    ID > 0
                    GROUP BY GroupID ) ID ON (ID.GroupID = I.ID)
  WHERE ID > 0 AND I.IsGroup = 1 AND
        ( ( ABS(ISNULL(I.QuantityBilled, 0) - ISNULL(ID.QuantityBilled, 0)) > 0.00001 )
          OR ( ABS(ISNULL(I.QuantityReceivedOnly, 0) - ISNULL(ID.QuantityReceivedOnly, 0)) > 0.00001 )
          OR ( ABS(ISNULL(I.QuantityReserved, 0) - ISNULL(ID.QuantityReserved, 0)) > 0.00001 )
          OR ( ABS(ISNULL(I.QuantityOnOrder, 0) - ISNULL(ID.QuantityOnOrder, 0)) > 0.00001 ) )
 
  IF (@@ROWCOUNT = 0)
    BREAK;
 
  SET @I = @I + 1;
  IF (@I > 5)
    BREAK;
END
;
PRINT '';
PRINT '';
 
PRINT '08)  Round all Inventory amount to 4 digits and convert NULLs to zeros'
;
UPDATE Inventory
SET    QuantityBilled = ROUND(ISNULL(QuantityBilled,0),4),
       QuantityReceivedOnly = ROUND(ISNULL(QuantityReceivedOnly,0),4),
       QuantityReserved = ROUND(ISNULL(QuantityReserved,0),4),
       QuantityOnOrder = ROUND(ISNULL(QuantityOnOrder,0),4),
       SeqID = SeqID + 1
WHERE  ( ISNULL(QuantityBilled,0) <> ROUND(ISNULL(QuantityBilled,0), 4) )
       OR ( ISNULL(QuantityReceivedOnly,0) <> ROUND(ISNULL(QuantityReceivedOnly,0), 4) )
       OR ( ISNULL(QuantityReserved,0) <> ROUND(ISNULL(QuantityReserved,0), 4) )
       OR ( ISNULL(QuantityOnOrder,0) <> ROUND(ISNULL(QuantityOnOrder,0), 4) )
;
PRINT '';
PRINT '';
 
PRINT '09)  Recompute the QuantityOnHand amounts in the InventoryLog to equal QuantityBilled+QuantityReceivedOnly'
;
UPDATE InventoryLog
SET    QuantityOnHand = ISNULL(QuantityBilled,0)+ISNULL(QuantityReceivedOnly,0), SeqID = SeqID + 1
WHERE  ABS( (ISNULL(QuantityBilled,0)+ISNULL(QuantityReceivedOnly,0)) - ISNULL(QuantityOnHand,0) ) > 0.00001
;
PRINT '';
PRINT '';
 
PRINT '10)  Recompute the QuantityAvailable amounts in the InventoryLog to equal QuantityOnHand+QuantityReserved'
;
UPDATE InventoryLog
SET    QuantityAvailable = ISNULL(QuantityOnHand,0)-ISNULL(QuantityReserved,0), SeqID = SeqID + 1
WHERE  ABS( (ISNULL(QuantityOnHand,0)-ISNULL(QuantityReserved,0)) - ISNULL(QuantityAvailable,0) ) > 0.00001
;
PRINT '';
PRINT '';
 
PRINT '11)  Recompute the QuantityExpected amounts in the InventoryLog to equal QuantityAvailable+QuantityOnOrder'
;
UPDATE InventoryLog
SET    QuantityExpected = ISNULL(QuantityAvailable,0)+ISNULL(QuantityOnOrder,0), SeqID = SeqID + 1
WHERE  ABS( (ISNULL(QuantityAvailable,0)+ISNULL(QuantityOnOrder,0)) - ISNULL(QuantityExpected,0) ) > 0.00001
;
PRINT '';
PRINT '';
 
PRINT '12)  Recompute the QuantityOnHand amounts in the Inventory to equal QuantityBilled+QuantityReceivedOnly'
;
UPDATE Inventory
SET    QuantityOnHand = ISNULL(QuantityBilled,0)+ISNULL(QuantityReceivedOnly,0), SeqID = SeqID + 1
WHERE  ABS( (ISNULL(QuantityBilled,0)+ISNULL(QuantityReceivedOnly,0)) - ISNULL(QuantityOnHand,0) ) > 0.00001
;
PRINT '';
PRINT '';
 
PRINT '13)  Recompute the QuantityAvailable amounts in the Inventory to equal QuantityOnHand+QuantityReserved'
;
UPDATE Inventory
SET    QuantityAvailable = ISNULL(QuantityOnHand,0)-ISNULL(QuantityReserved,0), SeqID = SeqID + 1
WHERE  ABS( (ISNULL(QuantityOnHand,0)-ISNULL(QuantityReserved,0)) - ISNULL(QuantityAvailable,0) ) > 0.00001
;
PRINT '';
PRINT '';
 
PRINT '14)  Recompute the QuantityExpected amounts in the Inventory to equal QuantityAvailable+QuantityOnOrder'
;
UPDATE Inventory
SET    QuantityExpected = ISNULL(QuantityAvailable,0)+ISNULL(QuantityOnOrder,0), SeqID = SeqID + 1
WHERE  ABS( (ISNULL(QuantityAvailable,0)+ISNULL(QuantityOnOrder,0)) - ISNULL(QuantityExpected,0) ) > 0.00001

SQL Query as a Stored Procedure

The query below is a slight modification of the one above. The difference is that when called, the stored procedure will refresh each of the records that were modified. This allows users to be in Control. However, all users should be asked to not have any Parts in Edit mode when the stored procedure is called.
SQL statement used to call the stored procedure:

EXEC csp_InventoryRepair;


SQL statement used to create the stored procedure if it does not exist.

USE [StoreData-v289]
GO
 
/****** Object:  StoredProcedure [dbo].[csp_InventoryRepair]    Script Date: 3/1/2018 10:13:22 AM ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
 
CREATE PROCEDURE [dbo].[csp_InventoryRepair]
AS
BEGIN
 
    DECLARE @ModifiedDate smalldatetime = CONVERT(smalldatetime, GETDATE());
    DECLARE @ModifiedUser VARCHAR(25) = 'CyrInvOutofBalance';
 
    -- '01)  Fix InventoryLog Entries with NULL DivisionIDs'
 
    UPDATE IL
    SET    InventoryID = TP.InventoryID,
        FromWarehouseID = TP.WarehouseID, IL.SeqID = IL.SeqID + 1, ModifiedByUser = @ModifiedUser + '-01', ModifiedDate = @ModifiedDate
    FROM   InventoryLog IL JOIN TransPart TP ON TP.ID = IL.TransPartID
    WHERE  IL.InventoryID IS NULL AND IL.TransPartID IS NOT NULL
    ;
    UPDATE InventoryLog
    SET    FromWarehouseID = I.WarehouseID, SeqID = IL.SeqID + 1, ModifiedByUser = @ModifiedUser + '-01', ModifiedDate = @ModifiedDate
    FROM   InventoryLog IL
        JOIN Inventory I ON (I.ClassTypeID = 12200 AND I.ID = IL.InventoryID)
    WHERE  ( IL.FromWarehouseID IS NULL OR IL.FromWarehouseID <> I.WarehouseID ) AND IL.InventoryID IS NOT NULL
    ;
    UPDATE InventoryLog
    SET    FromDivisionID = W.DivisionID, InventoryLog.SeqID = IL.SeqID + 1, ModifiedByUser = @ModifiedUser + '-01', ModifiedDate = @ModifiedDate
    FROM   InventoryLog IL
        JOIN Warehouse W ON (W.ClassTypeID = 12700 AND W.ID = IL.FromWarehouseID)
    WHERE  ( IL.FromDivisionID IS NULL OR IL.FromDivisionID <> W.DivisionID ) AND IL.FromWarehouseID IS NOT NULL
    ;
 
    -- '02)  Fix InventoryLog Entries with NULL WarehouseIDs'
 
    UPDATE InventoryLog
    SET FromWarehouseID = (SELECT TOP 1 ID FROM Warehouse WHERE DivisionID = InventoryLog.FromDivisionID ORDER BY IsDefault DESC, ID), SeqID = SeqID + 1, ModifiedByUser = @ModifiedUser + '-02', ModifiedDate = @ModifiedDate
    WHERE FromWarehouseID IS NULL
    ;
 
    -- '03)  Fix InventoryLog Entries with incorrect InventoryID due to old defects'
 
    UPDATE InventoryLog
    SET InventoryID = TD.AttachedOrderInvID, SeqID = IL.SeqID + 1, ModifiedByUser = @ModifiedUser + '-03', ModifiedDate = @ModifiedDate
    FROM InventoryLog IL
        LEFT JOIN VendorTransDetail TD ON (TD.ID = IL.TransDetailID)
    WHERE IL.TransDetailClassTypeID = 11100
        AND ( IL.InventoryID < 0 OR IL.InventoryID IS NULL )
        AND TD.AttachedOrderInvID > 0
    ;
 
    UPDATE InventoryLog
    SET InventoryID = TP.InventoryID, SeqID = IL.SeqID + 1, ModifiedByUser = @ModifiedUser + '-03', ModifiedDate = @ModifiedDate
    FROM InventoryLog IL
        LEFT JOIN VendorTransDetail TD ON (TD.ID = IL.TransDetailID)
        LEFT JOIN TransPart TP ON (TP.ID = TD.AttachedOrderPartID)
    WHERE IL.TransDetailClassTypeID = 11100
        AND ( IL.InventoryID < 0 OR IL.InventoryID IS NULL )
        AND TP.InventoryID > 0
    ;
 
    UPDATE IL
    SET InventoryID = I.ID, IL.SeqID = IL.SeqID + 1, ModifiedByUser = @ModifiedUser + '-03', ModifiedDate = @ModifiedDate
    FROM InventoryLog IL
        LEFT JOIN Inventory I ON I.WarehouseID = IL.FromWarehouseID AND I.PartID = IL.PartID
    WHERE I.ID IS NOT NULL AND IL.InventoryID <> ISNULL(I.ID,-1)
    ;
 
    -- '04)  Round all InventoryLog amount to 4 digits and convert NULLs to zeros'
    ;
    UPDATE InventoryLog
    SET    QuantityBilled = ROUND(ISNULL(QuantityBilled,0), 4),
        QuantityReceivedOnly = ROUND(ISNULL(QuantityReceivedOnly,0), 4),
        QuantityReserved = ROUND(ISNULL(QuantityReserved,0), 4),
        QuantityOnOrder = ROUND(ISNULL(QuantityOnOrder,0), 4),
        SeqID = SeqID + 1, ModifiedByUser = @ModifiedUser + '-04', ModifiedDate = @ModifiedDate
    FROM InventoryLog
    WHERE  ( ISNULL(QuantityBilled,0) <> ROUND(ISNULL(QuantityBilled,0), 4) )
        OR ( ISNULL(QuantityReceivedOnly,0) <> ROUND(ISNULL(QuantityReceivedOnly,0), 4) )
        OR ( ISNULL(QuantityReserved,0) <> ROUND(ISNULL(QuantityReserved,0), 4) )
        OR ( ISNULL(QuantityOnOrder,0) <> ROUND(ISNULL(QuantityOnOrder,0), 4) )
        OR QuantityBilled IS NULL OR  QuantityReceivedOnly IS NULL
        OR QuantityReserved IS NULL OR QuantityOnOrder IS NULL
    ;
 
    -- '05)  Fix any inventory records with incorrect group settings'
 
    UPDATE Inventory
    SET    GroupID = T.GroupID,
        IsDivisionSummary = T.IsDivisionSummary,
        IsGroup = T.IsGroup,
        SeqID = SeqID + 1, ModifiedByUser = @ModifiedUser + '-05', ModifiedDate = @ModifiedDate
    FROM   Inventory I
        JOIN ( SELECT I.ID,
                        (SELECT ID FROM Inventory WHERE PartID = I.PartID AND WarehouseID = W.ParentID) GroupID,
                        W.IsDivisionSummary, W.IsGroup
                FROM   Inventory I
                        LEFT JOIN Warehouse W ON (W.ID = I.WarehouseID)
                WHERE  I.ID > 0 ) T ON T.ID = I.ID
    WHERE  I.IsGroup <> T.IsGroup
        OR I.IsDivisionSummary <> T.IsDivisionSummary
        OR ISNULL( I.GroupID, -1 ) <> ISNULL( T.GroupID, -1 )
    ;
 
    -- '06)  Recompute InventoryItem Amounts to be in sync with the InventoryLog entries'
 
    UPDATE Inventory
    SET    QuantityBilled = ISNULL(IL.QuantityBilled,0),
        QuantityReceivedOnly = ISNULL(IL.QuantityReceivedOnly,0),
        QuantityReserved = ISNULL(IL.QuantityReserved,0),
        QuantityOnOrder = ISNULL(IL.QuantityOnOrder,0),
        SeqID = SeqID + 1, ModifiedByUser = @ModifiedUser + '-06', ModifiedDate = @ModifiedDate
    FROM   Inventory I 
        LEFT JOIN (SELECT   InventoryID, 
                            ROUND(SUM(QuantityBilled),4) QuantityBilled, 
                            ROUND(SUM(QuantityReceivedOnly),4) QuantityReceivedOnly, 
                            ROUND(SUM(QuantityReserved),4) QuantityReserved, 
                            ROUND(SUM(QuantityOnOrder),4) QuantityOnOrder
                    FROM     InventoryLog 
                    WHERE    InventoryID IS NOT NULL
                    GROUP BY InventoryID ) IL ON (IL.InventoryID = I.ID)
    WHERE I.ID > 0 AND I.IsGroup = 0 AND
        ( ( ABS(ISNULL(I.QuantityBilled, 0) - ISNULL(IL.QuantityBilled, 0)) > 0.00001 )
            OR ( ABS(ISNULL(I.QuantityReceivedOnly, 0) - ISNULL(IL.QuantityReceivedOnly, 0)) > 0.00001 )
            OR ( ABS(ISNULL(I.QuantityReserved, 0) - ISNULL(IL.QuantityReserved, 0)) > 0.00001 )
            OR ( ABS(ISNULL(I.QuantityOnOrder, 0) - ISNULL(IL.QuantityOnOrder, 0)) > 0.00001 ) )
    ;
 
    -- '07)  Recompute Inventory Summary Amounts to be in sync with the sum of the Inventory entries'
 
    DECLARE @I INT;
    SET @I = 0;
 
    WHILE (1=1)
    BEGIN
    UPDATE Inventory
    SET    QuantityBilled = ISNULL(ID.QuantityBilled,0),
            QuantityReceivedOnly = ISNULL(ID.QuantityReceivedOnly,0),
            QuantityReserved = ISNULL(ID.QuantityReserved,0),
            QuantityOnOrder = ISNULL(ID.QuantityOnOrder,0),
            SeqID = SeqID + 1, ModifiedByUser = @ModifiedUser + '-07', ModifiedDate = @ModifiedDate
    FROM   Inventory I
            LEFT JOIN (SELECT   GroupID,
                                ROUND(SUM(QuantityBilled),4) QuantityBilled,
                                ROUND(SUM(QuantityReceivedOnly),4) QuantityReceivedOnly,
                                ROUND(SUM(QuantityReserved),4) QuantityReserved,
                                ROUND(SUM(QuantityOnOrder),4) QuantityOnOrder
                        FROM     Inventory
                        WHERE    ID > 0
                        GROUP BY GroupID ) ID ON (ID.GroupID = I.ID)
    WHERE ID > 0 AND I.IsGroup = 1 AND
            ( ( ABS(ISNULL(I.QuantityBilled, 0) - ISNULL(ID.QuantityBilled, 0)) > 0.00001 )
            OR ( ABS(ISNULL(I.QuantityReceivedOnly, 0) - ISNULL(ID.QuantityReceivedOnly, 0)) > 0.00001 )
            OR ( ABS(ISNULL(I.QuantityReserved, 0) - ISNULL(ID.QuantityReserved, 0)) > 0.00001 )
            OR ( ABS(ISNULL(I.QuantityOnOrder, 0) - ISNULL(ID.QuantityOnOrder, 0)) > 0.00001 ) )
 
    IF (@@ROWCOUNT = 0)
        BREAK;
 
    SET @I = @I + 1;
    IF (@I > 5)
        BREAK;
    END
    ;
 
    -- '08)  Round all Inventory amount to 4 digits and convert NULLs to zeros'
 
    UPDATE Inventory
    SET    QuantityBilled = ROUND(ISNULL(QuantityBilled,0),4),
        QuantityReceivedOnly = ROUND(ISNULL(QuantityReceivedOnly,0),4),
        QuantityReserved = ROUND(ISNULL(QuantityReserved,0),4),
        QuantityOnOrder = ROUND(ISNULL(QuantityOnOrder,0),4),
        SeqID = SeqID + 1, ModifiedByUser = @ModifiedUser + '-08', ModifiedDate = @ModifiedDate
    WHERE  ( ISNULL(QuantityBilled,0) <> ROUND(ISNULL(QuantityBilled,0), 4) )
        OR ( ISNULL(QuantityReceivedOnly,0) <> ROUND(ISNULL(QuantityReceivedOnly,0), 4) )
        OR ( ISNULL(QuantityReserved,0) <> ROUND(ISNULL(QuantityReserved,0), 4) )
        OR ( ISNULL(QuantityOnOrder,0) <> ROUND(ISNULL(QuantityOnOrder,0), 4) )
    ;
 
    -- '09)  Recompute the QuantityOnHand amounts in the InventoryLog to equal QuantityBilled+QuantityReceivedOnly'
 
    UPDATE InventoryLog
    SET    QuantityOnHand = ISNULL(QuantityBilled,0)+ISNULL(QuantityReceivedOnly,0), SeqID = SeqID + 1, ModifiedByUser = @ModifiedUser + '-09', ModifiedDate = @ModifiedDate
    WHERE  ABS( (ISNULL(QuantityBilled,0)+ISNULL(QuantityReceivedOnly,0)) - ISNULL(QuantityOnHand,0) ) > 0.00001
    ;
 
    -- '10)  Recompute the QuantityAvailable amounts in the InventoryLog to equal QuantityOnHand+QuantityReserved'
    ;
    UPDATE InventoryLog
    SET    QuantityAvailable = ISNULL(QuantityOnHand,0)-ISNULL(QuantityReserved,0), SeqID = SeqID + 1, ModifiedByUser = @ModifiedUser + '-10', ModifiedDate = @ModifiedDate
    WHERE  ABS( (ISNULL(QuantityOnHand,0)-ISNULL(QuantityReserved,0)) - ISNULL(QuantityAvailable,0) ) > 0.00001
    ;
 
    -- '11)  Recompute the QuantityExpected amounts in the InventoryLog to equal QuantityAvailable+QuantityOnOrder'
    ;
    UPDATE InventoryLog
    SET    QuantityExpected = ISNULL(QuantityAvailable,0)+ISNULL(QuantityOnOrder,0), SeqID = SeqID + 1, ModifiedByUser = @ModifiedUser + '-11', ModifiedDate = @ModifiedDate
    WHERE  ABS( (ISNULL(QuantityAvailable,0)+ISNULL(QuantityOnOrder,0)) - ISNULL(QuantityExpected,0) ) > 0.00001
    ;
 
    --PRINT '12)  Recompute the QuantityOnHand amounts in the Inventory to equal QuantityBilled+QuantityReceivedOnly'
 
    UPDATE Inventory
    SET    QuantityOnHand = ISNULL(QuantityBilled,0)+ISNULL(QuantityReceivedOnly,0), SeqID = SeqID + 1, ModifiedByUser = @ModifiedUser + '-12', ModifiedDate = @ModifiedDate
    WHERE  ABS( (ISNULL(QuantityBilled,0)+ISNULL(QuantityReceivedOnly,0)) - ISNULL(QuantityOnHand,0) ) > 0.00001
 
 
    -- '13)  Recompute the QuantityAvailable amounts in the Inventory to equal QuantityOnHand+QuantityReserved'
 
    UPDATE Inventory
    SET    QuantityAvailable = ISNULL(QuantityOnHand,0)-ISNULL(QuantityReserved,0), SeqID = SeqID + 1, ModifiedByUser = @ModifiedUser + '-13', ModifiedDate = @ModifiedDate
    WHERE  ABS( (ISNULL(QuantityOnHand,0)-ISNULL(QuantityReserved,0)) - ISNULL(QuantityAvailable,0) ) > 0.00001
    ;
 
    -- '14)  Recompute the QuantityExpected amounts in the Inventory to equal QuantityAvailable+QuantityOnOrder'
 
    UPDATE Inventory
    SET    QuantityExpected = ISNULL(QuantityAvailable,0)+ISNULL(QuantityOnOrder,0), SeqID = SeqID + 1, ModifiedByUser = @ModifiedUser + '-14', ModifiedDate = @ModifiedDate
    WHERE  ABS( (ISNULL(QuantityAvailable,0)+ISNULL(QuantityOnOrder,0)) - ISNULL(QuantityExpected,0) ) > 0.00001
 
 
    -- Create temp tables and gather modified records to be refreshed.
    DECLARE @InventoryRepair RefreshRecordsTableType
    ;
    DECLARE @InventoryLogRepair RefreshRecordsTableType
    ;
 
    INSERT INTO @InventoryRepair (ID, ClassTypeID)
    SELECT ID, ClassTypeID
    FROM   Inventory
    WHERE  ModifiedDate = @ModifiedDate AND ModifiedByUser LIKE (@ModifiedUser + '%')
    ;
 
    INSERT INTO @InventoryLogRepair (ID, ClassTypeID)
    SELECT ID, ClassTypeID
    FROM   InventoryLog
    WHERE  ModifiedDate = @ModifiedDate AND ModifiedByUser LIKE (@ModifiedUser + '%')
    ;
 
    -- Refresh all modified records since users may be logged in.
    EXEC sp_RefreshRecords @InventoryLogRepair
    ;
    EXEC sp_RefreshRecords @InventoryRepair
    ;
 
    -- Create a table in the database called _InventoryAdjustmentLog-(support)
    -- Insert entries from temp tables to keep track of what has been changed 
 
    IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = '_InventoryAdjustmentLog-(support)' AND xtype = 'U')
    CREATE TABLE [_InventoryAdjustmentLog-(support)] (
        DateEntered datetime, 
        ID INT, 
        ClassTypeID INT, 
        TYPE VARCHAR(25)
    );
 
    INSERT INTO [_InventoryAdjustmentLog-(support)] (DateEntered, ID, ClassTypeID, TYPE)
    SELECT @ModifiedDate,
        ID, 
        ClassTypeID,
        (SELECT 'Inventory')
    FROM   @InventoryRepair
    ;
 
    INSERT INTO [_InventoryAdjustmentLog-(support)] (DateEntered, ID, ClassTypeID, TYPE)
    SELECT @ModifiedDate,
        ID, 
        ClassTypeID,
        (SELECT 'InventoryLog')
    FROM   @InventoryLogRepair
    ;
 
END
;
GO



  • Revised: 3/1/2018
You could leave a comment if you were logged in.