Just to sharing the solution for the in-whse qty is negative issue when opening the inventory posting of SAP B1 9.0 and quantity falls into negative when adding the inventory posting of SAP B1 9.0.
Similar issue has ever been occurred in the SAP B1 8.82 and lower version. The issue is posted in these threads:
Whilst in inventory posting of SAP B1 9.0, you will receive an error once you open the inventory posting and if you reconcile the inventory posting.
When opening the inventory posting, here is the error message:
See the error message that marked with black box.
After checking all of columns, you want to add the inventory posting but you can’t and receive the error message:
“quantity falls into negative inventory”
Here is the print screen:
There are two solutions for these issues:
1. Allow negative inventory or stock. You can do that by clearing the tick in the Block Negative Inventory By field. It means that you allow the negative stock in the system. After that, you adjust the negative stock in warehouse after the inventory posting.
2. Tracking the item that cause the error message.
You can use query to do that. But if you use query in this thread http://scn.sap.com/thread/1162607
It won’t give result because it is for SAP B1 8.82 and lower version
So, you can use this query that applicable for SAP B1 9.0 and higher version:
declare @countdate as datetime
declare @warehouse as varchar(10)
/*SELECT 1 FROM OWHS T1 WHERE T1.whscode = ‘[%0]’ */
/*SELECT 1 FROM OINM T0 WHERE T0.DocDate = ‘[%1]’ */
SET @warehouse = ‘[%0]’
set @countdate = ‘[%1]’
SELECT distinct t0.ItemCode, T0.Dscription, t0.Warehouse, SUM(t0.InQty – t0.OutQty) AS [Balance Qty in count Date], t1.CountQty, t3.OnHand [Current OnHand],
t1.Counted, – SUM(t0.InQty – t0.OutQty) + t1.CountQty AS Difference,
t3.OnHand – SUM(t0.InQty – t0.OutQty) + t1.countqty AS Calculation
FROM OINM AS t0 inner JOIN
inc1 AS t1 ON t0.ItemCode = t1.ItemCode AND t0.Warehouse = t1.WhsCode and t1.linestatus=’o’
inner JOIN OITW AS T3 ON T3.ITEMCODE = T1.ITEMCODE AND T3.WhsCode = T3.WhsCode
and t3.itemcode=t0.itemcode and t3.WhsCode=t0.Warehouse
WHERE T0.DocDate <= @countdate
GROUP BY t0.ItemCode, t0.Warehouse, t0.Dscription, t1.CountQty, t1.Counted, t3.onhand
,t1.itemcode, t3.itemcode, t3.OnHand
HAVING (t1.Counted = ‘y’) AND (t3.OnHand – SUM(t0.InQty – t0.OutQty) + t1.CountQty < 0) AND
(t0.Warehouse = @warehouse)
ORDER BY t0.Warehouse, t0.ItemCode, Difference
Execute the query in the SQL server query analyzer or query generator.
All of items that listed in the query result are the cause of the negative inventory. So, you must adjust it using good receipt one day after the count date.
The adjustment must use G/L account code that as same as in the inventory posting G/L field.
After adjustment is finished, you can now add the inventory posting.
If there is a strange question, i.e., why Inventory-Physical Stock on counted date and SAP stock on hand at system date is not matching
This is happened because there is a human error sometimes, rejected quantity, etc. So, to make your physical stock take successful, you must try the solution in these links below:
3. etc. Just search in google.
The other question is why the system uses the stock on hand on system date and not on counted date ?
This is because there are many stock out or stock in transactions after counted date.This is the standard of continuous stock system in SAP for moving average, FIFO or standard costing method.