This post is relevant if you seek to understand NetSuite’s spurious “bin numbers are not available” errors when attempting to perform inventory operations like Inventory Adjustments or Bin Transfers with the units of measure feature enabled. Item inventory detail shows that inventory is available but the operation fails with errors like The following Bin numbers are not available or The following Inventory Numbers/Bin numbers are not available, leaving users confused. While there might be several reasons for this error, this article focuses on those stemming from NetSuite’s internal inconsistency with decimal precision around unit of measure conversions.
Table of Contents
TL;DR
- NetSuite shows “Bin Numbers Are Not Available” errors during inventory operations
- Decimal precision mismatches in unit of measure (UoM) conversions create ghost stock.
- Using SuiteQL, we can produce a more accurate inventory detail report that exposes rounding errors.
- Using SuiteScript, we solve the problem by converting affected lines to base units.
Background
A client reached out about two years ago with an issue that was causing them serious operational pain. They were using RF-Smart for their warehouse management in NetSuite and were facing a puzzling issue during stock counting. Stock counting in warehouse management refers to the process of verifying the physical inventory levels of goods stored in a warehouse. It ensures that the recorded inventory in the warehouse management system (WMS) or enterprise resource planning (ERP) system, such as NetSuite, matches the actual stock on hand. If there is a mismatch, RF-Smart creates an inventory adjustment in NetSuite to reflect reality.
The client reported that the inventory adjustment process appeared to be randomly failing with “The following Inventory Numbers/Bin numbers are not available” error. They had lot-numbered items with bin management enabled.
Conversations with RF-Smart and NetSuite support had gotten them nowhere – there was much finger-pointing between the parties as to what was causing the issue. RF-Smart insisted that the issue was from NetSuite (and they were right). The client also mentioned that they had been told the problem had to do with 5 decimal point (dp) precision vs. 8dp but they did not quite understand what that meant. More importantly, it did not lead to a solution: Neither NetSuite nor RF-Smart had a solution in the pipeline nor provided a reasonable workaround.
Vague Error Messaging
In addition to being confusing, the inventory unavailable error messages, as illustrated above, are generally not actionable. A stock count operation may involve many items possibly sharing the same bin. However, when the operation fails, NetSuite only indicates which bins have problems but not which items in those bins are affected as illustrated in the screenshot below.
Thus, for the stock count situation, finding the right item to remove from the count was like finding a needle in a haystack. It would sometimes take several hours of trial and error to identify the culprit and remove it from the stock count so at least the rest could go through. Even at that, the inventory levels for the affected item(s) remained out of sync between the warehouse and NetSuite.
Similar Issue with Bin Transfers
More recently, a different client asked for a one-time script that would help him move inventory between bins as he prepared for a WMS integration launch. The script was not spectacular; NetSuite offers a Bin Transfer transaction for this purpose. However, in executing the script, we ran into the same error, worded slightly differently: The following Bin numbers are not available. Gladly, I had seen this problem earlier and was able to leverage the same solution concepts I will present shortly to address it.
Root Cause Analysis
As the saying goes, “a problem understood is a problem half solved”. After some digging and experimentation, we narrowed the problem down to an inconsistency in decimal precision, resulting in NetSuite reporting more inventory than truly available and subsequently erroring out when attempting to move or adjust the reported quantity. Let’s unpack this using an example.
Consider item 37329 in bin PackLine 205. The following is NetSuite’s inventory detail for that item, the same information that RF Smart relies on when assessing stock levels. There is inventory in multiple bins but we’ll focus on PackLine 205 which has the item in two lots.
Now, here is the stock count failure (this is a simplified stock count for debugging; as earlier stated, a typical stock count would have several items often sharing the same bins). Notice that it flags the inventory in lot “4/19/2023” as being insufficient. In the background, RF-Smart is trying to adjust the inventory starting with that lot.
Since the total adjustment quantity is greater than the quantity in that lot, the inventory adjustment attempts to remove the entire 0.00013816 units of the item from bin “PackLine 205”, lot “4/19/2023” but NetSuite complains that the bin/lot is not available? What the error is really saying is that there is insufficient inventory in that lot i.e. we are trying to remove more than is available. How can that be?!
Base vs. Stock Units
NetSuite offers a pretty decent units of measure system that allows for expressing a base unit and conversion factors to other related units. The interested reader can review the documentation to learn more about how units of measure work in NetSuite. The critical point is that the inventory detail shown on an item record or exposed via saved searches expresses item quantities in stock units not base units. Moreover, the quantity in stock units is rounded to 8 decimal places. This rounding may incorrectly produce excess stock.
Using SuiteQL to Expose Rounding Issues
The following is a SuiteQL query that computes the inventory detail for the item under question, expressed in both base unit and stock unit.
Notice that although the native item inventory detail shows 0.00013816 of the item in lot “4/19/2023”, the actual data is fractionally less: 0.0001381555… Thus, there is a rounding up that introduces ghost stock. Unfortunately, when performing inventory operations in stock units, this rounding is not taken into account and NetSuite simply blows up.
Solution Approach: Revert to Base Units
We understand the problem but how do we solve it?
My initial idea was to compute the actual quantity (unrounded) and apply it to the inventory operation. Unfortunately, this did not work as NetSuite limits the number of decimal places on those transactions and will automatically round the values.
I unsuccessfully tried a few other approaches whose details I will spare you. In reviewing with Marty Zigman, whom I worked with on this project, we came up with a remarkably simple solution (at least conceptually): Our thesis is that NetSuite actually stores the values in the database in base units. Since the issue stems from conversion to derived units, we can avoid it by performing the stock operation in base units instead. It worked!
Implementing this relatively simple concept turns out not to be as easy as one would anticipate. I will not go into all the nuances we had to address. Instead, I will outline the basic solution concepts.
Step 1: Write a query that detects the overflow situation
While we could have opted to convert all inventory adjustment or bin transfer lines to base units, the client wanted to know which lines/bins had issues. Thus, part of the solution was to detect and apply the conversion to only the lines where there was a potential for errors (I deliberately say “potential” as we saw a few false positives where there was a rounding issue but NetSuite did not error out. We did not consider this interesting enough to diagnose it further.)
Below is a sample query from the solution for my second client who did not use lots. This query is meant to be inspirational.
Pro Tip: When using lots, join the
ItemInventoryBalancetable instead of theInventoryBalancetable to get lot information.
SELECT
item.itemid,
item.id item,
invb.location,
invb.binnumber bin,
BUILTIN.DF(invb.binnumber) bin_str,
(SELECT internalid FROM unitsTypeUom bu WHERE bu.baseunit = 'T' and bu.unitstype = item.unitstype) baseunit,
invb.quantityonhand quantityonhand_base,
invb.quantityonhand / NVL(unit.conversionrate, 1) quantityonhand_stock,
invb.quantityavailable quantityavailable_base,
invb.quantityavailable / NVL(unit.conversionrate, 1) quantityavailable_stock,
unit.conversionrate,
CASE WHEN ROUND(invb.quantityonhand / NVL(unit.conversionrate, 1), 8) > TRUNC(invb.quantityonhand / NVL(unit.conversionrate, 1), 8) THEN 'YES' ELSE 'NO' END as overflow
FROM
item
LEFT JOIN InventoryBalance invb
ON (item.id = invb.item)
LEFT JOIN unitsTypeUom unit
ON (unit.internalid = item.stockunit)
WHERE
invb.location = ${location}
AND (
${whereClause.join (' OR ')}
)
Remarks:
- Line 13 contains the overflow detection logic. If there is a rounding up of the stock quantity, we flag the line for conversion.
- The
whereClauseplaceholder is an array of conditions of the form:(invb.item = ${item} AND invb.binnumber IN (<comma-separated-list-of-target-bins>). Depending on your use case/implementation, you might not need this.
Step 2: Convert affected lines to base units
In the case of the stock count client, since RF-Smart was triggering the inventory adjustment, we could not interfere with their process. Instead, we implemented a user event script on the Inventory Adjustment that found errant lines and converted them to base units in the beforeSubmit hook. To help identify the modified lines, we updated the line memo accordingly:
Implementation Tip: NetSuite will fight you if you try to modify an inventory adjustment line with inventory details. I took the alternative route of recreating the line in base units and deleting the original line.
For the bin transfer client, it was acceptable and easier to convert all lines to base units. There, I ran into a new error The field quantity contained more than the maximum number ( 10 ) of characters allowed on quantities like 60.91666667 that were accepted without issues when I created the same transaction in the UI. It appears there is some validation on the total length of the quantity string in addition to the 8dp restriction. I worked around this by truncating the quantity string to 10 characters.
Detective Controls
For the stock count client, we produced a detective query that they could run at will to see bins/lots where issues might arise and proactively address them. Interestingly, while we were working on this project, RF-Smart introduced a decimal issue detective report. I compared the results to our detective query’s output and observed that RF-Smart’s solution had more false positives most likely because they were looking at a 5dp cutoff instead of 8dp.
Closing
My hope is that this article has helped demystify the inventory operation issues resulting from unit of measure conversion and rounding issues. My clients were elated and relieved to finally have a solution. I found both projects very captivating. I appreciate that this article does not have complete code snippets and that is deliberate; this kind of puzzle goes beyond copy-and-paste and requires a level of competence.
It is my privilege and joy to work with highly skilled NetSuite professionals and clients with meaningful challenges to be solved. The NetSuite space is full of “lone wolves” working in siloes without a strong support system. If you are a strong NetSuite technical or business analyst looking to be part of a team of capable professionals, let’s have a conversation.
NetSuite Insights is proud to partner with Prolecto Resources Inc. – the unrivaled #1 NetSuite Systems Integrator and thought leader in the space! Learn more about how Prolecto can supercharge your NetSuite experience and deliver the best return on your NetSuite investment.
