Control SQL - Adjust Reserved Inventory Levels to Match Orders

Question

How do I adjust the Reserved Inventory Levels in Control?

Answer

The Reserved Inventory Quantity in Control is an auto-calculated count that you can not directly adjust.
  • The Reserved Quantity is a sum of all estimated usage (minus Actual posted usage) of inventoried parts that are for Orders in a status of WIP & Built.
  • The Reserved Quantity is adjusted automatically as you create new orders that use an inventoried part, create usage cards, adjust usage, change order statuses, etc.
  • The Reserved Quantity on an inventoried part in most cases should never be a negative value unless you have purposefully created a negative consumption formula.

If you encounter an issue where the Reserved Quantity is not accurate, you can run an included Utility to correct the number.
  • Ask all users to save their work and log out of Control.
  • Create a backup of the Control database.
  • Open Control and click on Tools > Utilities > Check Reserved Inventory.
  • The tool will execute and check for any discrepancies. If any are found then adjusting Inventory Activities will be posted to the InventoryLog table to correct any issues.
  • After the tool has been successfully run, it is recommended that you restart your server.

SQL

The query below will check the reserved quantity for you at an individual order and part instance level, it will not make any changes. This query is useful if you want to have a detailed breakdown on what discrepancies may be present and/or what makes up the total for reserved for a given part.

DECLARE @CurrentReserved TABLE(TransPartID INT PRIMARY KEY CLUSTERED, 
                               TransHeaderID INT, 
                               TransDetailID INT, 
                               PartID INT, 
                               InventoryID INT,
                               WarehouseID INT,
                               DivisionID INT,
                               QuantityReserved FLOAT)
 
INSERT INTO @CurrentReserved
SELECT TP.ID,
       TP.TransHeaderID,
       TP.TransDetailID,
       TP.PartID,
       TP.InventoryID,
       TP.WarehouseID,
        W.DivisionID,
       (TP.EstimatedValue - TP.ActualValue) AS NewResQty
FROM   TransPart TP WITH(NOLOCK)
       LEFT JOIN Part P WITH(NOLOCK) ON P.ID = TP.PartID
       LEFT JOIN TransHeader TH WITH(NOLOCK) ON TH.ID = TP.TransHeaderID
       LEFT JOIN Warehouse W WITH(NOLOCK) ON W.ID = TP.WarehouseID
WHERE  TP.ID > 0
       AND ISNULL(P.TrackInventory, 0) <> 0
       AND ISNULL(TH.StatusID, -1) IN (0,1,2)
       AND ((TP.EstimatedValue - TP.ActualValue) >= 0)
;
 
DECLARE @ActualReserved TABLE(PostDate DATETIME,
                              TransPartID INT, 
                              TransHeaderID INT, 
                              TransDetailID INT, 
                              PartID INT, 
                              InventoryID INT,
                              WarehouseID INT,
                              DivisionID INT,
                              ActualReserved FLOAT)
; 
INSERT INTO @ActualReserved
SELECT J.StartDateTime,
       ISNULL(IL.TransPartID, -IL.PartID) TransPartID,
       J.TransactionID TransHeaderID, 
       IL.TransDetailID, IL.PartID, IL.InventoryID, 
       IL.FromWarehouseID WarehouseID, 
       IL.FromDivisionID DivisionID, 
       IL.QuantityReserved
FROM InventoryLog IL WITH(NOLOCK) JOIN Journal J WITH(NOLOCK) ON J.ID = IL.ID
WHERE IL.QuantityReserved <> 0 AND IL.ID > 0 AND IL.PartID IS NOT NULL
;
 
DECLARE @Adjustments TABLE(TransPartID INT, 
                           TransHeaderID INT, 
                           TransDetailID INT, 
                           AccountID INT, 
                           PartID INT, 
                           InventoryID INT,
                           WarehouseID INT,
                           DivisionID INT,
                           PostDate DATETIME, 
                           ActualReserved FLOAT, 
                           CurrentReserved FLOAT, 
                           AdjustedReserved FLOAT)
;
INSERT INTO @Adjustments
(TransPartID, TransHeaderID, TransDetailID, PartID, InventoryID, WarehouseID, DivisionID, ActualReserved, CurrentReserved, AdjustedReserved)
SELECT ISNULL(CR.TransPartID, Inv.TransPartID) TransPartID,
       ISNULL(CR.TransHeaderID, Inv.TransHeaderID) TransHeaderID,
       ISNULL(CR.TransDetailID, Inv.TransDetailID) TransDetailID,
       ISNULL(CR.PartID, Inv.PartID) PartID,
       ISNULL(CR.InventoryID, Inv.InventoryID) InventoryID,
       ISNULL(CR.WarehouseID, Inv.WarehouseID) WarehouseID,
       ISNULL(CR.DivisionID, Inv.DivisionID) DivisionID,
       ISNULL(Inv.QuantityReserved, 0),
       ISNULL(CR.QuantityReserved, 0),
       ( ISNULL(CR.QuantityReserved, 0) - ISNULL(Inv.QuantityReserved, 0) ) QuantityReserved
FROM @CurrentReserved CR
     FULL JOIN (SELECT A.TransPartID, A.TransHeaderID, A.TransDetailID, A.PartID, 
                       A.InventoryID, A.WarehouseID, A.DivisionID, 
                       ROUND( SUM(A.ActualReserved), 8 ) QuantityReserved 
                FROM @ActualReserved A 
                GROUP BY A.TransPartID, A.TransHeaderID, A.TransDetailID, A.PartID, 
                         A.InventoryID, A.WarehouseID, A.DivisionID) Inv
                ON (ISNULL(Inv.TransPartID, -1) = ISNULL(CR.TransPartID, -1))
                AND (ISNULL(Inv.TransHeaderID, -1) = ISNULL(CR.TransHeaderID, -1))
                AND (ISNULL(Inv.TransDetailID, -1) = ISNULL(CR.TransDetailID, -1))
                AND (ISNULL(Inv.PartID, -1) = ISNULL(CR.PartID, -1))
                AND (ISNULL(Inv.InventoryID, -1) = ISNULL(CR.InventoryID, -1))
                AND (ISNULL(Inv.WarehouseID, -1) = ISNULL(CR.WarehouseID, -1))
                AND (ISNULL(Inv.DivisionID, -1) = ISNULL(CR.DivisionID, -1))
--WHERE ABS( ISNULL(CR.QuantityReserved, 0) - ISNULL(Inv.QuantityReserved, 0) ) > 0.0001
WHERE ABS( ISNULL(CR.QuantityReserved, 0)) > 0 OR ABS(ISNULL(Inv.QuantityReserved, 0) ) > 0
;
 
UPDATE A
SET AccountID = TH.AccountID
FROM @Adjustments A
     LEFT JOIN TransHeader TH ON TH.ID = A.TransHeaderID
WHERE A.AccountID IS NULL
;
UPDATE A
SET PostDate = TH.VoidedDate
FROM @Adjustments A
     LEFT JOIN TransHeader TH ON TH.ID = A.TransHeaderID
WHERE PostDate IS NULL
;
UPDATE A
SET PostDate = TH.BuiltDate
FROM @Adjustments A
     LEFT JOIN TransHeader TH ON TH.ID = A.TransHeaderID
WHERE PostDate IS NULL
;
UPDATE A
SET PostDate = T.LastPostDate
FROM @Adjustments A
     LEFT JOIN (SELECT TransPartID, MAX(PostDate) LastPostDate 
                FROM @ActualReserved
                GROUP BY TransPartID) T ON T.TransPartID = A.TransPartID
WHERE PostDate IS NULL
;
UPDATE A
SET PostDate = GETDATE()
FROM @Adjustments A
WHERE PostDate IS NULL
;
 
SELECT 
P.ItemName AS Part
, D.DivisionName AS [Division]
, W.WarehouseName AS [Warehouse]
, OrderNumber AS [ORDER No]
, TD.GoodsItemCode AS [Product]
, TD.LineItemNumber AS [Line Item No]
, CAST(OrderCreatedDate AS DATE) AS [ORDER DATE]
, StatusText AS [STATUS]
, A.CurrentReserved AS [CURRENT Reserved Qty]
, A.ActualReserved AS [Correct Reserved Qty]
, ROUND(A.AdjustedReserved,2) AS Discrepancy
, A.TransPartID
FROM @Adjustments A
LEFT JOIN TransHeader T WITH(NOLOCK) ON T.ID = A.TransHeaderID
LEFT JOIN TransDetail TD WITH(NOLOCK) ON TD.ID = A.TransDetailID
LEFT JOIN Part P WITH(NOLOCK) ON P.ID = A.PartID
LEFT JOIN Division D WITH(NOLOCK) ON D.ID = A.DivisionID
LEFT JOIN Warehouse W WITH(NOLOCK) ON W.ID = A.WarehouseID
ORDER BY P.ItemName, D.DivisionName, W.WarehouseName

Version Information

  • Entered : 10/31/2017
  • Version(s) : Control 4.5+

See Also