Skip to Content

Dear All,

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:

Problem in Inventory Posting

Quantity falls in negative inventory + Which Item

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:

/wp-content/uploads/2014/08/inwhseqty_524642.png

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:

/wp-content/uploads/2014/08/egative_524686.jpg

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:

1. 7 steps to a successful stock take | MYOB Blog

2. What is stock&amp;nbsp;taking? – Questions &amp; Answers – AccountingTools

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.

Good Luck

Regards,

Jimmy

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. San Xu

    Nice one.

    So, you must adjust it using good receipt one day after the count date.” Should it be “before ” the count date?

    (0) 
    1. Jimmy Michael Post author

      Thanks…

      I think it will increase the stock and the difference between physical stock or count qty will be more higher than Balance quantity on count date. So, it would be better to adjust it after the count date.

      The adjustment seems applicable for all valuation methods.

      (0) 
      1. San Xu

        Sorry I think I made a mistake: the variance is the counted quantity minus the in-warehouse stock, not vice versa. Yes, I think you’re right:)

        (0) 
  2. Alven Butanas

    Got the same error. What I did was that I changed the count time in the inventory counting…. to the last minute where there are no more inventory transactions on the count day.

    Thanks for the script. It helped me understand what items caused the error.

    (0) 

Leave a Reply