Follow

Fix Negative Inventory

Summary

When you start an inventory counting document, perform your count, and then copy it to an inventory posting, you received an error message stating "Quantity falls into negative inventory" or "In-whse quantity is negative"

The reason many times this happens is between the date you added the Inventory Counting document and when you copied it to an Inventory Posting, you issued or consumed some of the quantities listed on the count.

An example of this would be if you have 1000 pint glasses in stock as of the date of the counting document. Then you preform the count, which takes a week to finish. In that week, you or one of your users, posts an invoice selling 700 of the glasses, leaving you with only 300 actually in stock. When you finish your count, you list that you still have 500 left. Therefore the system tries to remove 500 from inventory to get you to your correct count. However, 300 - 500 would put you into negative inventory, er go you get that error message.

One way to proactively prevent this is to either preform and finish the count on the same day. However, although the chances are small, you can still receive the same error if someone posts a transaction that affects the stock for that item(s).

Alternatively, you can check box on the 'Freeze' button next to your items in the Freeze column on the Inventory Counting document. This will prevent the item from being used in transactions until the count is finished.

Solution

When this error comes up, you can use this query to find the items that do not have enough in stock:

---

; WITH INVCNT AS (
SELECT T1.[DocNum]
, T0.[ItemCode]
, T0.[ItemDesc]
, T0.[WhsCode]
, (T2.OnHand + (Select (Sum(TAF.OutQty) - Sum(TAF.InQty))
From OINM TAF
Where TAF.DocDate > T0.[CountDate]
AND TAF.ItemCode = T0.[ItemCode]
AND TAF.Warehouse = T0.[WhsCode]
)) AS 'Posting Date Qty'
, T0.[CountQty] AS 'Our Count'
, T0.[CountQty] - (T2.OnHand + (Select (Sum(TAF.OutQty) - Sum(TAF.InQty))
From OINM TAF
Where TAF.DocDate > T0.[CountDate]
AND TAF.ItemCode = T0.[ItemCode]
AND TAF.Warehouse = T0.[WhsCode]
)) AS 'Diff Count and Posting Dt'
, T2.[OnHand] AS 'OnHand Today'
, (T0.[CountQty] - (Select (Sum(TAF.OutQty) - Sum(TAF.InQty))
From OINM TAF
Where TAF.DocDate > T0.[CountDate]
AND TAF.ItemCode = T0.[ItemCode]
AND TAF.Warehouse = T0.[WhsCode]
)) AS 'Corrected today Inv'
,T0.[DiffPercen]
, T0.[CountDate]
, T0.[CountTime]


FROM [dbo].[INC1] T0
INNER JOIN [dbo].[OINC] T1
ON T0.DocEntry = T1.DocEntry
INNER JOIN OITW T2
ON T0.ItemCode = T2.ItemCode
and T0.WhsCode = T2.WhsCode
WHERE T1.[DocNum] = [%0]
and T1.[Status] = 'O'
)


SELECT Inv.DocNum As 'Count Number'
, Inv.ItemCode
, INV.ItemDesc
, Inv.WhsCode
, Inv.[Posting Date Qty] as 'System Qty'
, INV.[Our Count] as 'Our Count'
, Inv.[Diff Count and Posting Dt] as 'Difference'
, Inv.[OnHand Today] as 'Current Inventory Today'
, INV.[Corrected today Inv] AS 'Current plus Count adjustment'
, Inv.CountDate
, Inv.CountTime

FROM INVCNT INV
WHERE [Corrected today Inv] < 0

---

When you run the query, you will be prompted for the Inventory Counting Document number, and the query will return a list of rows that are going negative.

This query can be saved in your database using the steps on this help page: How To Import Queries Into Orchestrated


Version 4.7.1.0

 

Was this article helpful?
0 out of 1 found this helpful
Have more questions? Submit a request

Comments