Published on June 7, 2023.

One of the projects that I'm currently working on is for a manufacturing client whose NetSuite instance is setup to support multiple locations, bins, and items that are serial numbered and lot numbered.

The project involves a custom assembly build process, and one of its functions makes it possible for users to determine where an item is stored by scanning a barcode that represents the item number.

In this post, I'll walk you through the steps that I took to create the SuiteQL query for this function.

I started with this basic query, which locates an Item record based on an Item ID.

SELECT
	Item.ID AS Item,
	Item.ItemID AS ItemNumber,
	Item.DisplayName AS ItemName
FROM 
	Item
WHERE 
	Item.ItemID = '86753-01'
ORDER BY	
	Item.ID

I then added a join to the InventoryNumber table, which is where the serial and lot numbers are stored.

SELECT
	Item.ID AS Item,
	Item.ItemID AS ItemNumber,
	Item.DisplayName AS ItemName,
	InventoryNumber.InventoryNumber AS SerialLotNumber
FROM 
	Item
	INNER JOIN InventoryNumber ON
		( InventoryNumber.Item = Item.ID )
WHERE 
	Item.ItemID = '86753-01'
ORDER BY	
	Item.ID,
	InventoryNumber.InventoryNumber

Next, I joined to the BinInventoryBalance table based on the InventoryNumber.ID column. This join provides me with several key values, including the IDs of the bins that the item is stored in, and both the quantity on hand and quantity available for each serial / lot number.

SELECT
	Item.ID AS Item,
	Item.ItemID AS ItemNumber,
	Item.DisplayName AS ItemName,
	Bin.ID AS Bin,
	Bin.BinNumber,
	InventoryNumber.InventoryNumber AS SerialLotNumber,
	BinInventoryBalance.QuantityOnHand,
	BinInventoryBalance.QuantityAvailable
FROM 
	Item
	INNER JOIN InventoryNumber ON
		( InventoryNumber.Item = Item.ID )
	INNER JOIN BinInventoryBalance ON
		( BinInventoryBalance.InventoryNumber = InventoryNumber.ID )
	INNER JOIN Bin ON
		( Bin.ID = BinInventoryBalance.BinNumber )
WHERE 
	Item.ItemID = '86753-01'
ORDER BY	
	Item.ID,
	Bin.BinNumber,
	InventoryNumber.InventoryNumber

I then added a join to the Bin table.

SELECT
	Item.ID AS Item,
	Item.ItemID AS ItemNumber,
	Item.DisplayName AS ItemName,
	InventoryNumber.InventoryNumber AS SerialLotNumber,
	BinInventoryBalance.QuantityOnHand,
	BinInventoryBalance.QuantityAvailable
FROM 
	Item
	INNER JOIN InventoryNumber ON
		( InventoryNumber.Item = Item.ID )
	INNER JOIN BinInventoryBalance ON
		( BinInventoryBalance.InventoryNumber = InventoryNumber.ID )
WHERE 
	Item.ItemID = '86753-01'
ORDER BY	
	Item.ID,
	InventoryNumber.InventoryNumber

As I mentioned earlier, my client maintains inventory in multiple warehouse locations. Therefore, I also needed to provide location information.

The Bin table includes a Bin.Location column, which indicates the bin's location. I could have used that value, along with the BUILTIN.DF function, to provide the location's name. Instead, I chose to actually join to the Location table.

SELECT
	Item.ID AS Item,
	Item.ItemID AS ItemNumber,
	Item.DisplayName AS ItemName,
	Location.ID AS Location,
	Location.Name AS LocationName,
	Bin.ID AS Bin,
	Bin.BinNumber,
	InventoryNumber.InventoryNumber AS SerialLotNumber,
	BinInventoryBalance.QuantityOnHand,
	BinInventoryBalance.QuantityAvailable	
FROM 
	Item
	INNER JOIN InventoryNumber ON
		( InventoryNumber.Item = Item.ID )
	INNER JOIN BinInventoryBalance ON
		( BinInventoryBalance.InventoryNumber = InventoryNumber.ID )
	INNER JOIN Bin ON
		( Bin.ID = BinInventoryBalance.BinNumber )
	INNER JOIN Location ON
		( Location.ID = Bin.Location )
WHERE 
	Item.ItemID = '86753-01'
ORDER BY	
	Item.ID,
	Location.Name,
	Bin.BinNumber,
	InventoryNumber.InventoryNumber

My client also needs to know which of the bins is the preferred bin. (The preferred bin is the bin that item receipts default to.) To get that value, I needed to join to the ItemBinQuantity table. Here's the final query.

SELECT
	Item.ID AS Item,
	Item.ItemID AS ItemNumber,
	Item.DisplayName AS ItemName,
	Location.ID AS Location,
	Location.Name AS LocationName,
	Bin.ID AS Bin,
	Bin.BinNumber,
	ItemBinQuantity.PreferredBin,
	InventoryNumber.InventoryNumber AS SerialLotNumber,
	BinInventoryBalance.QuantityOnHand,
	BinInventoryBalance.QuantityAvailable	
FROM 
	Item
	INNER JOIN InventoryNumber ON
		( InventoryNumber.Item = Item.ID )
	INNER JOIN BinInventoryBalance ON
		( BinInventoryBalance.InventoryNumber = InventoryNumber.ID )
	INNER JOIN Bin ON
		( Bin.ID = BinInventoryBalance.BinNumber )
	INNER JOIN Location ON
		( Location.ID = Bin.Location )
	INNER JOIN ItemBinQuantity ON
		( ItemBinQuantity.Item = Item.ID )
		AND ( ItemBinQuantity.Bin = Bin.ID )
WHERE 
	Item.ItemID = '86753-01'
ORDER BY	
	Item.ID,
	Location.Name,
	Bin.BinNumber,
	InventoryNumber.InventoryNumber

I hope you find the queries that I've shared in this post to be helpful.

For more queries that involve bins, serial numbered items, and lot numbered items, you might want to check out these previous posts:
• Use SuiteQL to Identify Items In Alternate Warehouse Bins
• Use SuiteQL With Serialized, Lot Numbered, and Expiring Items