Published on July 9, 2023.

I have several NetSuite clients that work with lot and serial numbered items.

One of those clients recently asked me to create a SuiteQL query that they wanted to use on their Customer Portal. Their goal was to provide customers with visibility into the lot and serial numbers of items that had been used to fulfill their orders.

Another client recently asked for a query for use in their Manufacturing execution system (MES). The goal was to provide users with a way to view the transaction history for a given lot / serial numbered item.

In this post, I'll share the two queries that I created for these clients.

Lot / Serial Numbers Used to Fulfill A Sales Order

For the first query, I'm starting from the Sales Order transaction and working my way to the items that fulfilled it. As you'll see, that path includes joining to the related Item Fulfillment records (via the NextTransactionLineLink table) and their related TransactionLine records.

The lot / serial numbers are stored in a table named InventoryNumber - and that's the "final destination" if you want to think of that way. But to make the jump from an Item Fulfillment record's lines to the actual serial / lot numbers that were used requires joining to another table where the relationship between a transaction and the lot / serial number is maintained. That table is named InventoryAssignment.

Here's the query.

SELECT
	SalesOrder.ID AS SalesOrder,
	SalesOrder.TranID AS SalesOrderID,	
	Fulfillment.ID AS Fulfillment,
	Fulfillment.TranID AS FulfillmentID,
	Fulfillment.Status AS FulfillmentStatus,
	REPLACE( BUILTIN.DF( Fulfillment.Status ), 'Item Fulfillment : ', '' ) AS FulfillmentStatusName,
	FulfillmentLine.Item AS FulfillmentItem,
	BUILTIN.DF( FulfillmentLine.Item ) AS FulfillmentItemID,
	( FulfillmentLine.Quantity * -1 ) AS FulfillmentQuantity,
	BUILTIN.DF( InventoryAssignment.InventoryNumber ) AS AssignedInventoryNumber,
	( InventoryAssignment.Quantity * -1 ) AS AssignedQuantity
FROM
	Transaction AS SalesOrder 
	INNER JOIN TransactionLine AS SalesOrderLine ON
		( SalesOrderLine.Transaction = SalesOrder.ID )	
	INNER JOIN NextTransactionLineLink AS NTLL ON
		( NTLL.PreviousDoc = SalesOrderLine.Transaction )
		AND ( NTLL.PreviousLine = SalesOrderLine.ID )
		AND ( NTLL.LinkType = 'ShipRcpt' )
	INNER JOIN Transaction AS Fulfillment ON
		( Fulfillment.ID = NTLL.NextDoc )
	INNER JOIN TransactionLine AS FulfillmentLine ON
		( FulfillmentLine.Transaction = NTLL.NextDoc )
		AND ( FulfillmentLine.ID = NTLL.NextLine )
	INNER JOIN Item AS FulfillmenItem ON
		( FulfillmenItem.ID = FulfillmentLine.Item )
	LEFT OUTER JOIN InventoryAssignment ON
		( InventoryAssignment.Transaction = FulfillmentLine.Transaction )
		AND ( InventoryAssignment.TransactionLine = FulfillmentLine.ID )
	LEFT OUTER JOIN InventoryNumber ON
		( InventoryNumber.ID = InventoryAssignment.InventoryNumber )
WHERE
	( SalesOrder.Type = 'SalesOrd' )
	AND ( SalesOrder.TranID = 'SO2023070901' )

Notice that I'm using LEFT OUTER JOINs on the InventoryAssignment and InventoryNumber table. By doing so, the query will return all of the item fulfillments and their lines, regardless of whether the items are lot / serial number tracked.

Transactions That Reference A Lot / Serial Number

The second query requires approaching the data from the opposite direction. In this case, we know the item and serial / lot number, and we need to work back to the transactions that reference it.

So we're starting with the InventoryNumber table, joining to the InventoryAssignment table, and then joining to the Transaction table.

Here's the second query.

SELECT
	InventoryNumber.Item,
	BUILTIN.DF( InventoryNumber.Item ) AS ItemID,
	InventoryNumber.InventoryNumber,
	Transaction.ID AS Transaction,
	Transaction.Type AS TransactionType,
	Transaction.TranID AS TransactionID,
	Transaction.TranDate AS TransactionDate,
	BUILTIN.DF( Transaction.Entity ) AS TransactionEntity,
	Transaction.OtherRefNum,
	InventoryAssignment.Quantity
FROM
	InventoryNumber
	INNER JOIN InventoryAssignment ON
		( InventoryAssignment.InventoryNumber = InventoryNumber.ID )
	INNER JOIN Transaction ON
		( Transaction.ID = InventoryAssignment.Transaction )
WHERE
	( InventoryNumber.Item = 1127 )
	AND ( InventoryNumber.InventoryNumber = 'SR203023113' )

The WHERE clause sets filter criteria on both the Item and InventoryNumber columns because it is entirely possible for two different items to have the same lot or serial numbers. If you run the query without specifying both values, then the query's speed will very likely take a hit. I believe that's because the InventoryNumber has a composite index consisting of the two columns. So if you don't specify both values, then Oracle is likely doing a full table scan to resolve the query.

Wrapping Up

I think this post's two big takeaways are that the InventoryNumber table is where item serial and lot numbers are stored, and that the assignment of serial and lot numbered items is tracked in the InventoryAssignment table. With those tables, you can join to the transactions that reference the items.