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+
You could leave a comment if you were logged in.