Table of Contents

{$page}

Explanation of SQL

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.

Risk of Data Corruption if Run Improperly

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.

SQL

code_formatsql

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

code

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 for 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:

code_formatsql

exec csp_InventoryRepair;

code

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

code_formatsql

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';
  1. - '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
  ;
  1. - '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
  ;
  1. - '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)
  ;
  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
  ;
  1. - '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 )
  ;
  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 ) )
  ;
  1. - '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
  ;
  1. - '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) )
  ;
  1. - '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
  ;
  1. - '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
  ;
  1. - '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
  ;
  1. -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
  1. - '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
  ;
  1. - '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
  1. - 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 + '%')
  ;
  1. - Refresh all modified records since users may be logged in.
  EXEC sp_RefreshRecords @InventoryLogRepair
  ;
  EXEC sp_RefreshRecords @InventoryRepair
  ;
  1. - Create a table in the database called _InventoryAdjustmentLog-(support)
  1. - 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

code

Version Information