====== {$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,

<code>
     FromWarehouseID = TP.WarehouseID, IL.SeqID = IL.SeqID + 1
</code>

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

<code>
     JOIN Inventory I ON (I.ClassTypeID = 12200 AND I.ID = IL.InventoryID)
</code>

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

<code>
     JOIN Warehouse W ON (W.ClassTypeID = 12700 AND W.ID = IL.FromWarehouseID)
</code>

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

<code>
   LEFT JOIN VendorTransDetail TD ON (TD.ID = IL.TransDetailID)
</code>

WHERE IL.TransDetailClassTypeID = 11100

<code>
    AND ( IL.InventoryID <0 OR IL.InventoryID IS NULL )
</code>

<code>
    AND TD.AttachedOrderInvID> 0
</code>

;

UPDATE InventoryLog

SET InventoryID = TP.InventoryID, SeqID = IL.SeqID + 1

FROM InventoryLog IL

<code>
   LEFT JOIN VendorTransDetail TD ON (TD.ID = IL.TransDetailID)
</code>

<code>
   LEFT JOIN TransPart TP ON (TP.ID = TD.AttachedOrderPartID)
</code>

WHERE IL.TransDetailClassTypeID = 11100

<code>
    AND ( IL.InventoryID <0 OR IL.InventoryID IS NULL )
</code>

<code>
    AND TP.InventoryID> 0
</code>

;

UPDATE IL

SET InventoryID = I.ID, IL.SeqID = IL.SeqID + 1

FROM InventoryLog IL

<code>
   LEFT JOIN Inventory I ON I.WarehouseID = IL.FromWarehouseID AND I.PartID = IL.PartID
</code>

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

<code>
     QuantityReceivedOnly = ROUND(ISNULL(QuantityReceivedOnly,0), 4),
</code>

<code>
     QuantityReserved = ROUND(ISNULL(QuantityReserved,0), 4),
</code>

<code>
     QuantityOnOrder = ROUND(ISNULL(QuantityOnOrder,0), 4),
</code>

<code>
     SeqID = SeqID + 1
</code>

FROM InventoryLog

WHERE ( ISNULL(QuantityBilled,0) ROUND(ISNULL(QuantityBilled,0), 4) )

<code>
     OR ( ISNULL(QuantityReceivedOnly,0)  ROUND(ISNULL(QuantityReceivedOnly,0), 4) )
</code>

<code>
     OR ( ISNULL(QuantityReserved,0)  ROUND(ISNULL(QuantityReserved,0), 4) )
</code>

<code>
     OR ( ISNULL(QuantityOnOrder,0)  ROUND(ISNULL(QuantityOnOrder,0), 4) )
</code>

<code>
     OR QuantityBilled IS NULL OR  QuantityReceivedOnly IS NULL
</code>

<code>
     OR QuantityReserved IS NULL OR QuantityOnOrder IS NULL
</code>

;

PRINT ''; PRINT '';

PRINT '05) Fix any inventory records with incorrect group settings'

;

UPDATE Inventory

SET GroupID = T.GroupID,

<code>
     IsDivisionSummary = T.IsDivisionSummary,
</code>

<code>
     IsGroup = T.IsGroup,
</code>

<code>
     SeqID = SeqID + 1
</code>

FROM Inventory I

<code>
     JOIN ( SELECT I.ID,
</code>

<code>
                   (SELECT ID FROM Inventory WHERE PartID = I.PartID AND WarehouseID = W.ParentID) GroupID,
</code>

<code>
                   W.IsDivisionSummary, W.IsGroup
</code>

<code>
            FROM   Inventory I
</code>

<code>
                   LEFT JOIN Warehouse W ON (W.ID = I.WarehouseID)
</code>

<code>
            WHERE  I.ID> 0 ) T ON T.ID = I.ID
</code>

WHERE I.IsGroup T.IsGroup

<code>
     OR I.IsDivisionSummary  T.IsDivisionSummary
</code>

<code>
     OR ISNULL( I.GroupID, -1 )  ISNULL( T.GroupID, -1 )
</code>

;

PRINT ''; PRINT '';

PRINT '06) Recompute InventoryItem Amounts to be in sync with the InventoryLog entries'

;

UPDATE Inventory

SET QuantityBilled = ISNULL(IL.QuantityBilled,0),

<code>
     QuantityReceivedOnly = ISNULL(IL.QuantityReceivedOnly,0),
</code>

<code>
     QuantityReserved = ISNULL(IL.QuantityReserved,0),
</code>

<code>
     QuantityOnOrder = ISNULL(IL.QuantityOnOrder,0),
</code>

<code>
     SeqID = SeqID + 1
</code>

FROM Inventory I

<code>
     LEFT JOIN (SELECT   InventoryID,
</code>

<code>
                         ROUND(SUM(QuantityBilled),4) QuantityBilled,
</code>

<code>
                         ROUND(SUM(QuantityReceivedOnly),4) QuantityReceivedOnly,
</code>

<code>
                         ROUND(SUM(QuantityReserved),4) QuantityReserved,
</code>

<code>
                         ROUND(SUM(QuantityOnOrder),4) QuantityOnOrder
</code>

<code>
                FROM     InventoryLog
</code>

<code>
                WHERE    InventoryID IS NOT NULL
</code>

<code>
                GROUP BY InventoryID ) IL ON (IL.InventoryID = I.ID)
</code>

WHERE I.ID > 0 AND I.IsGroup = 0 AND

<code>
    ( ( ABS(ISNULL(I.QuantityBilled, 0) - ISNULL(IL.QuantityBilled, 0))> 0.00001 )
</code>

<code>
      OR ( ABS(ISNULL(I.QuantityReceivedOnly, 0) - ISNULL(IL.QuantityReceivedOnly, 0))> 0.00001 )
</code>

<code>
      OR ( ABS(ISNULL(I.QuantityReserved, 0) - ISNULL(IL.QuantityReserved, 0))> 0.00001 )
</code>

<code>
      OR ( ABS(ISNULL(I.QuantityOnOrder, 0) - ISNULL(IL.QuantityOnOrder, 0))> 0.00001 ) )
</code>

;

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

<code>
UPDATE Inventory
</code>

<code>
SET    QuantityBilled = ISNULL(ID.QuantityBilled,0),
</code>

<code>
       QuantityReceivedOnly = ISNULL(ID.QuantityReceivedOnly,0),
</code>

<code>
       QuantityReserved = ISNULL(ID.QuantityReserved,0),
</code>

<code>
       QuantityOnOrder = ISNULL(ID.QuantityOnOrder,0),
</code>

<code>
       SeqID = SeqID + 1
</code>

<code>
FROM   Inventory I
</code>

<code>
       LEFT JOIN (SELECT   GroupID,
</code>

<code>
                           ROUND(SUM(QuantityBilled),4) QuantityBilled,
</code>

<code>
                           ROUND(SUM(QuantityReceivedOnly),4) QuantityReceivedOnly,
</code>

<code>
                           ROUND(SUM(QuantityReserved),4) QuantityReserved,
</code>

<code>
                           ROUND(SUM(QuantityOnOrder),4) QuantityOnOrder
</code>

<code>
                  FROM     Inventory
</code>

<code>
                  WHERE    ID> 0
</code>

<code>
                  GROUP BY GroupID ) ID ON (ID.GroupID = I.ID)
</code>

<code>
WHERE ID> 0 AND I.IsGroup = 1 AND
</code>

<code>
      ( ( ABS(ISNULL(I.QuantityBilled, 0) - ISNULL(ID.QuantityBilled, 0))> 0.00001 )
</code>

<code>
        OR ( ABS(ISNULL(I.QuantityReceivedOnly, 0) - ISNULL(ID.QuantityReceivedOnly, 0))> 0.00001 )
</code>

<code>
        OR ( ABS(ISNULL(I.QuantityReserved, 0) - ISNULL(ID.QuantityReserved, 0))> 0.00001 )
</code>

<code>
        OR ( ABS(ISNULL(I.QuantityOnOrder, 0) - ISNULL(ID.QuantityOnOrder, 0))> 0.00001 ) )
</code>

<code>
IF (@@ROWCOUNT = 0)
</code>

<code>
  BREAK;
</code>

<code>
SET @I = @I + 1;
</code>

<code>
IF (@I> 5)
</code>

<code>
  BREAK;
</code>

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

<code>
     QuantityReceivedOnly = ROUND(ISNULL(QuantityReceivedOnly,0),4),
</code>

<code>
     QuantityReserved = ROUND(ISNULL(QuantityReserved,0),4),
</code>

<code>
     QuantityOnOrder = ROUND(ISNULL(QuantityOnOrder,0),4),
</code>

<code>
     SeqID = SeqID + 1
</code>

WHERE ( ISNULL(QuantityBilled,0) ROUND(ISNULL(QuantityBilled,0), 4) )

<code>
     OR ( ISNULL(QuantityReceivedOnly,0)  ROUND(ISNULL(QuantityReceivedOnly,0), 4) )
</code>

<code>
     OR ( ISNULL(QuantityReserved,0)  ROUND(ISNULL(QuantityReserved,0), 4) )
</code>

<code>
     OR ( ISNULL(QuantityOnOrder,0)  ROUND(ISNULL(QuantityOnOrder,0), 4) )
</code>

;

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

<code>
  DECLARE @ModifiedDate smalldatetime = CONVERT(smalldatetime, GETDATE());
</code>

<code>
  DECLARE @ModifiedUser varchar(25) = 'CyrInvOutofBalance';
</code>

  - - '01) Fix InventoryLog Entries with NULL DivisionIDs'

<code>
  UPDATE IL
</code>

<code>
  SET    InventoryID = TP.InventoryID,
</code>

<code>
      FromWarehouseID = TP.WarehouseID, IL.SeqID = IL.SeqID + 1, ModifiedByUser = @ModifiedUser + '-01', ModifiedDate = @ModifiedDate
</code>

<code>
  FROM   InventoryLog IL JOIN TransPart TP ON TP.ID = IL.TransPartID
</code>

<code>
  WHERE  IL.InventoryID IS NULL AND IL.TransPartID IS NOT NULL
</code>

<code>
  ;
</code>

<code>
  UPDATE InventoryLog
</code>

<code>
  SET    FromWarehouseID = I.WarehouseID, SeqID = IL.SeqID + 1, ModifiedByUser = @ModifiedUser + '-01', ModifiedDate = @ModifiedDate
</code>

<code>
  FROM   InventoryLog IL
</code>

<code>
      JOIN Inventory I ON (I.ClassTypeID = 12200 AND I.ID = IL.InventoryID)
</code>

<code>
  WHERE  ( IL.FromWarehouseID IS NULL OR IL.FromWarehouseID  I.WarehouseID ) AND IL.InventoryID IS NOT NULL
</code>

<code>
  ;
</code>

<code>
  UPDATE InventoryLog
</code>

<code>
  SET    FromDivisionID = W.DivisionID, InventoryLog.SeqID = IL.SeqID + 1, ModifiedByUser = @ModifiedUser + '-01', ModifiedDate = @ModifiedDate
</code>

<code>
  FROM   InventoryLog IL
</code>

<code>
      JOIN Warehouse W ON (W.ClassTypeID = 12700 AND W.ID = IL.FromWarehouseID)
</code>

<code>
  WHERE  ( IL.FromDivisionID IS NULL OR IL.FromDivisionID  W.DivisionID ) AND IL.FromWarehouseID IS NOT NULL
</code>

<code>
  ;
</code>

  - - '02) Fix InventoryLog Entries with NULL WarehouseIDs'

<code>
  UPDATE InventoryLog
</code>

<code>
  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
</code>

<code>
  WHERE FromWarehouseID IS NULL
</code>

<code>
  ;
</code>

  - - '03) Fix InventoryLog Entries with incorrect InventoryID due to old defects'

<code>
  UPDATE InventoryLog
</code>

<code>
  SET InventoryID = TD.AttachedOrderInvID, SeqID = IL.SeqID + 1, ModifiedByUser = @ModifiedUser + '-03', ModifiedDate = @ModifiedDate
</code>

<code>
  FROM InventoryLog IL
</code>

<code>
      LEFT JOIN VendorTransDetail TD ON (TD.ID = IL.TransDetailID)
</code>

<code>
  WHERE IL.TransDetailClassTypeID = 11100
</code>

<code>
      AND ( IL.InventoryID <0 OR IL.InventoryID IS NULL )
</code>

<code>
      AND TD.AttachedOrderInvID> 0
</code>

<code>
  ;
</code>

<code>
  UPDATE InventoryLog
</code>

<code>
  SET InventoryID = TP.InventoryID, SeqID = IL.SeqID + 1, ModifiedByUser = @ModifiedUser + '-03', ModifiedDate = @ModifiedDate
</code>

<code>
  FROM InventoryLog IL
</code>

<code>
      LEFT JOIN VendorTransDetail TD ON (TD.ID = IL.TransDetailID)
</code>

<code>
      LEFT JOIN TransPart TP ON (TP.ID = TD.AttachedOrderPartID)
</code>

<code>
  WHERE IL.TransDetailClassTypeID = 11100
</code>

<code>
      AND ( IL.InventoryID <0 OR IL.InventoryID IS NULL )
</code>

<code>
      AND TP.InventoryID> 0
</code>

<code>
  ;
</code>

<code>
  UPDATE IL
</code>

<code>
  SET InventoryID = I.ID, IL.SeqID = IL.SeqID + 1, ModifiedByUser = @ModifiedUser + '-03', ModifiedDate = @ModifiedDate
</code>

<code>
  FROM InventoryLog IL
</code>

<code>
      LEFT JOIN Inventory I ON I.WarehouseID = IL.FromWarehouseID AND I.PartID = IL.PartID
</code>

<code>
  WHERE I.ID IS NOT NULL AND IL.InventoryID  ISNULL(I.ID,-1)
</code>

<code>
  ;
</code>

  - - '04) Round all InventoryLog amount to 4 digits and convert NULLs to zeros'

<code>
  ;
</code>

<code>
  UPDATE InventoryLog
</code>

<code>
  SET    QuantityBilled = ROUND(ISNULL(QuantityBilled,0), 4),
</code>

<code>
      QuantityReceivedOnly = ROUND(ISNULL(QuantityReceivedOnly,0), 4),
</code>

<code>
      QuantityReserved = ROUND(ISNULL(QuantityReserved,0), 4),
</code>

<code>
      QuantityOnOrder = ROUND(ISNULL(QuantityOnOrder,0), 4),
</code>

<code>
      SeqID = SeqID + 1, ModifiedByUser = @ModifiedUser + '-04', ModifiedDate = @ModifiedDate
</code>

<code>
  FROM InventoryLog
</code>

<code>
  WHERE  ( ISNULL(QuantityBilled,0)  ROUND(ISNULL(QuantityBilled,0), 4) )
</code>

<code>
      OR ( ISNULL(QuantityReceivedOnly,0)  ROUND(ISNULL(QuantityReceivedOnly,0), 4) )
</code>

<code>
      OR ( ISNULL(QuantityReserved,0)  ROUND(ISNULL(QuantityReserved,0), 4) )
</code>

<code>
      OR ( ISNULL(QuantityOnOrder,0)  ROUND(ISNULL(QuantityOnOrder,0), 4) )
</code>

<code>
      OR QuantityBilled IS NULL OR  QuantityReceivedOnly IS NULL
</code>

<code>
      OR QuantityReserved IS NULL OR QuantityOnOrder IS NULL
</code>

<code>
  ;
</code>

  - - '05) Fix any inventory records with incorrect group settings'

<code>
  UPDATE Inventory
</code>

<code>
  SET    GroupID = T.GroupID,
</code>

<code>
      IsDivisionSummary = T.IsDivisionSummary,
</code>

<code>
      IsGroup = T.IsGroup,
</code>

<code>
      SeqID = SeqID + 1, ModifiedByUser = @ModifiedUser + '-05', ModifiedDate = @ModifiedDate
</code>

<code>
  FROM   Inventory I
</code>

<code>
      JOIN ( SELECT I.ID,
</code>

<code>
                      (SELECT ID FROM Inventory WHERE PartID = I.PartID AND WarehouseID = W.ParentID) GroupID,
</code>

<code>
                      W.IsDivisionSummary, W.IsGroup
</code>

<code>
              FROM   Inventory I
</code>

<code>
                      LEFT JOIN Warehouse W ON (W.ID = I.WarehouseID)
</code>

<code>
              WHERE  I.ID> 0 ) T ON T.ID = I.ID
</code>

<code>
  WHERE  I.IsGroup  T.IsGroup
</code>

<code>
      OR I.IsDivisionSummary  T.IsDivisionSummary
</code>

<code>
      OR ISNULL( I.GroupID, -1 )  ISNULL( T.GroupID, -1 )
</code>

<code>
  ;
</code>

  - - '06) Recompute InventoryItem Amounts to be in sync with the InventoryLog entries'

<code>
  UPDATE Inventory
</code>

<code>
  SET    QuantityBilled = ISNULL(IL.QuantityBilled,0),
</code>

<code>
      QuantityReceivedOnly = ISNULL(IL.QuantityReceivedOnly,0),
</code>

<code>
      QuantityReserved = ISNULL(IL.QuantityReserved,0),
</code>

<code>
      QuantityOnOrder = ISNULL(IL.QuantityOnOrder,0),
</code>

<code>
      SeqID = SeqID + 1, ModifiedByUser = @ModifiedUser + '-06', ModifiedDate = @ModifiedDate
</code>

<code>
  FROM   Inventory I
</code>

<code>
      LEFT JOIN (SELECT   InventoryID,
</code>

<code>
                          ROUND(SUM(QuantityBilled),4) QuantityBilled,
</code>

<code>
                          ROUND(SUM(QuantityReceivedOnly),4) QuantityReceivedOnly,
</code>

<code>
                          ROUND(SUM(QuantityReserved),4) QuantityReserved,
</code>

<code>
                          ROUND(SUM(QuantityOnOrder),4) QuantityOnOrder
</code>

<code>
                  FROM     InventoryLog
</code>

<code>
                  WHERE    InventoryID IS NOT NULL
</code>

<code>
                  GROUP BY InventoryID ) IL ON (IL.InventoryID = I.ID)
</code>

<code>
  WHERE I.ID> 0 AND I.IsGroup = 0 AND
</code>

<code>
      ( ( ABS(ISNULL(I.QuantityBilled, 0) - ISNULL(IL.QuantityBilled, 0))> 0.00001 )
</code>

<code>
          OR ( ABS(ISNULL(I.QuantityReceivedOnly, 0) - ISNULL(IL.QuantityReceivedOnly, 0))> 0.00001 )
</code>

<code>
          OR ( ABS(ISNULL(I.QuantityReserved, 0) - ISNULL(IL.QuantityReserved, 0))> 0.00001 )
</code>

<code>
          OR ( ABS(ISNULL(I.QuantityOnOrder, 0) - ISNULL(IL.QuantityOnOrder, 0))> 0.00001 ) )
</code>

<code>
  ;
</code>

  - - '07) Recompute Inventory Summary Amounts to be in sync with the sum of the Inventory entries'

<code>
  DECLARE @I INT;
</code>

<code>
  SET @I = 0;
</code>

<code>
  WHILE (1=1)
</code>

<code>
  BEGIN
</code>

<code>
  UPDATE Inventory
</code>

<code>
  SET    QuantityBilled = ISNULL(ID.QuantityBilled,0),
</code>

<code>
          QuantityReceivedOnly = ISNULL(ID.QuantityReceivedOnly,0),
</code>

<code>
          QuantityReserved = ISNULL(ID.QuantityReserved,0),
</code>

<code>
          QuantityOnOrder = ISNULL(ID.QuantityOnOrder,0),
</code>

<code>
          SeqID = SeqID + 1, ModifiedByUser = @ModifiedUser + '-07', ModifiedDate = @ModifiedDate
</code>

<code>
  FROM   Inventory I
</code>

<code>
          LEFT JOIN (SELECT   GroupID,
</code>

<code>
                              ROUND(SUM(QuantityBilled),4) QuantityBilled,
</code>

<code>
                              ROUND(SUM(QuantityReceivedOnly),4) QuantityReceivedOnly,
</code>

<code>
                              ROUND(SUM(QuantityReserved),4) QuantityReserved,
</code>

<code>
                              ROUND(SUM(QuantityOnOrder),4) QuantityOnOrder
</code>

<code>
                      FROM     Inventory
</code>

<code>
                      WHERE    ID> 0
</code>

<code>
                      GROUP BY GroupID ) ID ON (ID.GroupID = I.ID)
</code>

<code>
  WHERE ID> 0 AND I.IsGroup = 1 AND
</code>

<code>
          ( ( ABS(ISNULL(I.QuantityBilled, 0) - ISNULL(ID.QuantityBilled, 0))> 0.00001 )
</code>

<code>
          OR ( ABS(ISNULL(I.QuantityReceivedOnly, 0) - ISNULL(ID.QuantityReceivedOnly, 0))> 0.00001 )
</code>

<code>
          OR ( ABS(ISNULL(I.QuantityReserved, 0) - ISNULL(ID.QuantityReserved, 0))> 0.00001 )
</code>

<code>
          OR ( ABS(ISNULL(I.QuantityOnOrder, 0) - ISNULL(ID.QuantityOnOrder, 0))> 0.00001 ) )
</code>

<code>
  IF (@@ROWCOUNT = 0)
</code>

<code>
      BREAK;
</code>

<code>
  SET @I = @I + 1;
</code>

<code>
  IF (@I> 5)
</code>

<code>
      BREAK;
</code>

<code>
  END
</code>

<code>
  ;
</code>

  - - '08) Round all Inventory amount to 4 digits and convert NULLs to zeros'

<code>
  UPDATE Inventory
</code>

<code>
  SET    QuantityBilled = ROUND(ISNULL(QuantityBilled,0),4),
</code>

<code>
      QuantityReceivedOnly = ROUND(ISNULL(QuantityReceivedOnly,0),4),
</code>

<code>
      QuantityReserved = ROUND(ISNULL(QuantityReserved,0),4),
</code>

<code>
      QuantityOnOrder = ROUND(ISNULL(QuantityOnOrder,0),4),
</code>

<code>
      SeqID = SeqID + 1, ModifiedByUser = @ModifiedUser + '-08', ModifiedDate = @ModifiedDate
</code>

<code>
  WHERE  ( ISNULL(QuantityBilled,0)  ROUND(ISNULL(QuantityBilled,0), 4) )
</code>

<code>
      OR ( ISNULL(QuantityReceivedOnly,0)  ROUND(ISNULL(QuantityReceivedOnly,0), 4) )
</code>

<code>
      OR ( ISNULL(QuantityReserved,0)  ROUND(ISNULL(QuantityReserved,0), 4) )
</code>

<code>
      OR ( ISNULL(QuantityOnOrder,0)  ROUND(ISNULL(QuantityOnOrder,0), 4) )
</code>

<code>
  ;
</code>

  - - '09) Recompute the QuantityOnHand amounts in the InventoryLog to equal QuantityBilled+QuantityReceivedOnly'

<code>
  UPDATE InventoryLog
</code>

<code>
  SET    QuantityOnHand = ISNULL(QuantityBilled,0)+ISNULL(QuantityReceivedOnly,0), SeqID = SeqID + 1, ModifiedByUser = @ModifiedUser + '-09', ModifiedDate = @ModifiedDate
</code>

<code>
  WHERE  ABS( (ISNULL(QuantityBilled,0)+ISNULL(QuantityReceivedOnly,0)) - ISNULL(QuantityOnHand,0) )> 0.00001
</code>

<code>
  ;
</code>

  - - '10) Recompute the QuantityAvailable amounts in the InventoryLog to equal QuantityOnHand+QuantityReserved'

<code>
  ;
</code>

<code>
  UPDATE InventoryLog
</code>

<code>
  SET    QuantityAvailable = ISNULL(QuantityOnHand,0)-ISNULL(QuantityReserved,0), SeqID = SeqID + 1, ModifiedByUser = @ModifiedUser + '-10', ModifiedDate = @ModifiedDate
</code>

<code>
  WHERE  ABS( (ISNULL(QuantityOnHand,0)-ISNULL(QuantityReserved,0)) - ISNULL(QuantityAvailable,0) )> 0.00001
</code>

<code>
  ;
</code>

  - - '11) Recompute the QuantityExpected amounts in the InventoryLog to equal QuantityAvailable+QuantityOnOrder'

<code>
  ;
</code>

<code>
  UPDATE InventoryLog
</code>

<code>
  SET    QuantityExpected = ISNULL(QuantityAvailable,0)+ISNULL(QuantityOnOrder,0), SeqID = SeqID + 1, ModifiedByUser = @ModifiedUser + '-11', ModifiedDate = @ModifiedDate
</code>

<code>
  WHERE  ABS( (ISNULL(QuantityAvailable,0)+ISNULL(QuantityOnOrder,0)) - ISNULL(QuantityExpected,0) )> 0.00001
</code>

<code>
  ;
</code>

  - -PRINT '12) Recompute the QuantityOnHand amounts in the Inventory to equal QuantityBilled+QuantityReceivedOnly'

<code>
  UPDATE Inventory
</code>

<code>
  SET    QuantityOnHand = ISNULL(QuantityBilled,0)+ISNULL(QuantityReceivedOnly,0), SeqID = SeqID + 1, ModifiedByUser = @ModifiedUser + '-12', ModifiedDate = @ModifiedDate
</code>

<code>
  WHERE  ABS( (ISNULL(QuantityBilled,0)+ISNULL(QuantityReceivedOnly,0)) - ISNULL(QuantityOnHand,0) )> 0.00001
</code>

  - - '13) Recompute the QuantityAvailable amounts in the Inventory to equal QuantityOnHand+QuantityReserved'

<code>
  UPDATE Inventory
</code>

<code>
  SET    QuantityAvailable = ISNULL(QuantityOnHand,0)-ISNULL(QuantityReserved,0), SeqID = SeqID + 1, ModifiedByUser = @ModifiedUser + '-13', ModifiedDate = @ModifiedDate
</code>

<code>
  WHERE  ABS( (ISNULL(QuantityOnHand,0)-ISNULL(QuantityReserved,0)) - ISNULL(QuantityAvailable,0) )> 0.00001
</code>

<code>
  ;
</code>

  - - '14) Recompute the QuantityExpected amounts in the Inventory to equal QuantityAvailable+QuantityOnOrder'

<code>
  UPDATE Inventory
</code>

<code>
  SET    QuantityExpected = ISNULL(QuantityAvailable,0)+ISNULL(QuantityOnOrder,0), SeqID = SeqID + 1, ModifiedByUser = @ModifiedUser + '-14', ModifiedDate = @ModifiedDate
</code>

<code>
  WHERE  ABS( (ISNULL(QuantityAvailable,0)+ISNULL(QuantityOnOrder,0)) - ISNULL(QuantityExpected,0) )> 0.00001
</code>

  - - Create temp tables and gather modified records to be refreshed.

<code>
  DECLARE @InventoryRepair RefreshRecordsTableType
</code>

<code>
  ;
</code>

<code>
  DECLARE @InventoryLogRepair RefreshRecordsTableType
</code>

<code>
  ;
</code>

<code>
  INSERT INTO @InventoryRepair (ID, ClassTypeID)
</code>

<code>
  SELECT ID, ClassTypeID
</code>

<code>
  FROM   Inventory
</code>

<code>
  WHERE  ModifiedDate = @ModifiedDate AND ModifiedByUser like (@ModifiedUser + '%')
</code>

<code>
  ;
</code>

<code>
  INSERT INTO @InventoryLogRepair (ID, ClassTypeID)
</code>

<code>
  SELECT ID, ClassTypeID
</code>

<code>
  FROM   InventoryLog
</code>

<code>
  WHERE  ModifiedDate = @ModifiedDate AND ModifiedByUser like (@ModifiedUser + '%')
</code>

<code>
  ;
</code>

  - - Refresh all modified records since users may be logged in.

<code>
  EXEC sp_RefreshRecords @InventoryLogRepair
</code>

<code>
  ;
</code>

<code>
  EXEC sp_RefreshRecords @InventoryRepair
</code>

<code>
  ;
</code>

  - - Create a table in the database called _InventoryAdjustmentLog-(support)

  - - Insert entries from temp tables to keep track of what has been changed

<code>
IF NOT EXISTS (select * from sysobjects where name = '_InventoryAdjustmentLog-(support)' and xtype = 'U')
</code>

<code>
CREATE TABLE [_InventoryAdjustmentLog-(support)] (
</code>

<code>
    DateEntered datetime,
</code>

<code>
    ID int,
</code>

<code>
    ClassTypeID int,
</code>

<code>
    Type varchar(25)
</code>

<code>
);
</code>

<code>
  INSERT INTO [_InventoryAdjustmentLog-(support)] (DateEntered, ID, ClassTypeID, Type)
</code>

<code>
  SELECT @ModifiedDate,
</code>

<code>
      ID,
</code>

<code>
      ClassTypeID,
</code>

<code>
      (select 'Inventory')
</code>

<code>
  FROM   @InventoryRepair
</code>

<code>
  ;
</code>

<code>
  INSERT INTO [_InventoryAdjustmentLog-(support)] (DateEntered, ID, ClassTypeID, Type)
</code>

<code>
  SELECT @ModifiedDate,
</code>

<code>
      ID,
</code>

<code>
      ClassTypeID,
</code>

<code>
      (select 'InventoryLog')
</code>

<code>
  FROM   @InventoryLogRepair
</code>

<code>
  ;
</code>

END

;

GO

[[:code|]]

===== Version Information =====

  * Revised : 3/1/2018

===== Related SQLs =====

  * Backlinks [[:include_pagepage_componentbacklinks|]]