Negative Inventory: An Overview
As a prequisite for understanding this article, you should be familiar with the concept of Transaction Lines and how they are used to calculate inventory balances in NetSuite. If you are not familiar with Transaction Lines, please read the article onTransaction Lines first.
NetSuite does not allow you to create a transaction that will cause negative inventory. However it is possible to modify or delete a transaction that will cause negative inventory.
Deleting a transaction is simplest, so let’s look at that first.
Deleting a Transaction
Suppose we have a few inventory lines that look like this:
| ID | Transaction ID | Item | Quantity | … |
|---|---|---|---|---|
| 1 | 432 (IR432) | Item 1 | 10 | … |
| 2 | 433 (WCO433) | Item 1 | 15 | … |
| 3 | 434 (IF434) | Item 1 | -20 | … |
The sum of the quantity of all Transaction Lines is 5, so the inventory balance for Item 1 is 5.
But suppose we delete the Item Receipt Transaction (ID 432) and by extension the Transaction Lines associated with it. Now we have:
| ID | Transaction ID | Item | Quantity | … |
|---|---|---|---|---|
| 2 | 433 (WCO433) | Item 1 | 15 | … |
| 3 | 434 (IF434) | Item 1 | -20 | … |
The sum of the quantity of all Transaction Lines is -5, so the inventory balance for Item 1 is -5. This is the most common way to create negative inventory in NetSuite. The solution is to either delete the Item Fulfillment Transaction (ID 434) or to manually adjust the inventory balance to whatever the current inventory balance should be.
Deleting an Inventory Detail Change Transaction
This problem is similar to the previous one, but it is a little more complicated. It occurs when you delete an Inventory Status Change transaction (such as a Bin Transfer or Inventory Status Change).
This will result in a total positive quantity for the item, but a negative quantity when drilling down to the detail level. Let’s illustrate this using bin transfers.
Here are the sample transaction lines:
| ID | Transaction ID | Item | Quantity | Bin | … |
|---|---|---|---|---|---|
| 1 | 432 (IR432) | Item 1 | 10 | Bin 1 | … |
| 2 | 433 (BT433) | Item 1 | -10 | Bin 1 | … |
| 3 | 433 (BT433) | Item 1 | 10 | Bin 2 | … |
| 4 | 434 (IF434) | Item 1 | -5 | Bin 2 | … |
So in Bin 1, the first two lines equal 0, and in Bin 2, the last two lines equal 10 - 5 = 5.
But what happends if we delete the Bin Transfer Transaction (ID 433)? Now we have:
| ID | Transaction ID | Item | Quantity | Bin | … |
|---|---|---|---|---|---|
| 1 | 432 (IR432) | Item 1 | 10 | Bin 1 | … |
| 4 | 434 (IF434) | Item 1 | -5 | Bin 2 | … |
So for the total inventory for Item 1, both lines match and we now have 10 - 5 = 5. But if we drill down to the detail level to see what we have in Bin 2, we have just the Item Fulfillment Transaction (ID 434) and it has a quantity of -5. So the inventory balance for Bin 2 is -5.
Changing the Inventory Detail of a Transaction
This problem occurs when you change the inventory detail of an existing transaction.
Suppose we have just two transactions an Item Receipt and an Item Fulfillment:
| ID | Transaction ID | Item | Quantity | Bin | … |
|---|---|---|---|---|---|
| 1 | 432 (IR432) | Item 1 | 10 | Bin 1 | … |
| 2 | 433 (IF433) | Item 1 | -5 | Bin 1 | … |
So in this case, both the transactions have the same Bin and Item, so the quantity for both Item 1 and Item 1 in Bin 1 is 5.
But what happens if I change the Bin of the Item Receipt to Bin 2? Now we have:
| ID | Transaction ID | Item | Quantity | Bin | … |
|---|---|---|---|---|---|
| 1 | 432 (IR432) | Item 1 | 10 | Bin 2 | … |
| 2 | 433 (IF433) | Item 1 | -5 | Bin 1 | … |
So for the total inventory for Item 1, both lines match and we now have 10 - 5 = 5. But if we drill down to the detail level, we have just the Item Fulfillment Transaction (ID 433) and it has a quantity of -5. So the inventory balance for Bin 1 is -5.
Changing the Dates of a Transaction
This problem occurs when you change the date of a transaction in a way that causes NetSuite to think that there was negative inventory at some point in the past. This can manifest on a variety of different reports, but the most common one is the Inventory Valuation report.
Let’s look at the previous example again, this time with the date column:
| ID | Transaction ID | Date | Item | Quantity | Bin | … |
|---|---|---|---|---|---|---|
| 1 | 432 (IR432) | 2/15/2023 | Item 1 | 10 | Bin 1 | … |
| 2 | 433 (IF433) | 3/15/2023 | Item 1 | -5 | Bin 1 | … |
So in this case, the current inventory balance is 5. If I run a valuation report for 2/31/2023, NetSuite will sum all transactions that occured before 2/31/2023 and only the Item Receipt will be included. So the inventory balance will be 10.
Now what happens if I change the date of the Item Fullfilent to 1/1/2023? Now we have:
| ID | Transaction ID | Date | Item | Quantity | Bin | … |
|---|---|---|---|---|---|---|
| 1 | 432 (IR432) | 2/15/2023 | Item 1 | 10 | Bin 1 | … |
| 2 | 433 (IF433) | 1/1/2023 | Item 1 | -5 | Bin 1 | … |
So if I run a valuation report for 1/31/2023, NetSuite will sum all transactions that occured before 1/31/2023 NetSuite will just include the Item Fulfillment. So the inventory balance will be -5 for that date. This can affect the accuracy of Inventory Reports and in turn the accuracy of you balance sheet.
Conclusion
Negative inventory remains a big problem for NetSuite users. Inventory affecting transactions need to modified all the time and safeguards need to be in place to prevent negative inventory.
Various scripts can be used to prevent negative inventory, but they can be costly and difficult to implement. Inventory Reports should be run on a regular basis to ensure that the inventory balance is accurate and positive and alerts should be set up to notify you when negative inventory is detected.