Published on April 20, 2021.

Previously, I've written about a custom Web app that I've developed for a NetSuite client's warehouse team, which is helping them to streamline their processes and be more efficient. I've shared a few of the queries that I've written for that project, including a few involving bins, bin contents, etc and a query that I developed to identify empty bins. This is a client that has the Advanced Bin / Numbered Inventory Management feature enabled.

My client recently requested a new function for the app. The goal is to help the warehouse team, as well as their sales and product management teams, to easily identify items that have either expired or are about to expire.

Developing the query to handle this request involved the use of two tables that I had not used in the past. One is InventoryNumber, and the other is InventoryNumberLocation.

The InventoryNumber table provides information about serialized and lot numbered items, including each item's serial number or lot number, as well as the item's expiration date (if applicable).

The InventoryNumberLocation table provides information about where these items are located, including quantity information such as quantity on hand, quantity available, and so on.

Let's take a look at a few queries that use these tables.

Items With Expiration Dates

	
SELECT
	BUILTIN.DF( InventoryNumber.Item ) AS ItemID,
	InventoryNumber.InventoryNumber,
	InventoryNumber.QuantityOnHand,
	TO_DATE( InventoryNumber.ExpirationDate ) - TO_DATE( SYSDATE ) AS ExpirationDays 
FROM
	InventoryNumber
WHERE
	InventoryNumber.ExpirationDate IS NOT NULL
ORDER BY
	InventoryNumber.ExpirationDate

In this query, I'm accessing only the InventoryNumber table. The query returns all items that have an expiration date, and specifically returns the Item ID, the Inventory Number (which will be a serial number or lot number, depending on the item), the Quantity On Hand, and the number of days until the item expires (ExpirationDays).

Note that a negative ExpirationDays value indicates that the item has expired (and the value is the number of days since the expiration date), while a positive number indicates that the item has not yet expired (and the value is the number of days until the expiration date). I'm using the "TO_DATE" function so that only days are considered in the calculation. Otherwise, you'll likely get a decimal value representing the time aspect of these date/time values.

Items That Have Expired or Are About To Expire

You can easily modify the query to return only items that have expired, or are about to expire within a certain number of days.

	
SELECT
	BUILTIN.DF( InventoryNumber.Item ) AS ItemID,
	InventoryNumber.InventoryNumber,
	InventoryNumber.QuantityOnHand,
	TO_DATE( InventoryNumber.ExpirationDate ) - TO_DATE( SYSDATE ) AS ExpirationDays 
FROM
	InventoryNumber
WHERE
	InventoryNumber.ExpirationDate <= ( SYSDATE + 30 )
ORDER BY
	InventoryNumber.ExpirationDate

I've adjusted the WHERE clause to filter on items that have expired, or are about to expire in the next 30 days ("SYSDATE + 30").

Multi-Location Inventory

My client has multiple warehouses, so they have NetSuite's "Multi-Location Inventory" feature enabled. If you have that function enabled, and run the queries that I shared above, then you'll likely get this error:

	
Search error occurred: Field 'QuantityOnHand' for record 'inventoryNumber' was not found. Reason: REMOVED - Field is removed

I believe that the quantity-related fields on the InventoryNumber are disabled when the Multi-Location Inventory feature is enabled. So to get these values, you'll need to make use of that second table that I mentioned earlier - the InventoryNumberLocation table.

Here's a modified version of the query showing a join to that table.

	
SELECT
	BUILTIN.DF( InventoryNumber.Item ) AS ItemID,
	InventoryNumber.InventoryNumber,
	BUILTIN.DF( INL.Location ) AS Location,
	INL.QuantityOnHand,
	TO_DATE( InventoryNumber.ExpirationDate ) - TO_DATE( SYSDATE ) AS ExpirationDays
FROM
	InventoryNumber
	INNER JOIN InventoryNumberLocation AS INL ON
		( INL.InventoryNumber = InventoryNumber.ID )
WHERE
	InventoryNumber.ExpirationDate <= ( SYSDATE + 30 )
ORDER BY
	InventoryNumber.ExpirationDate

Note that I'm now pulling the QuantityOnHand value from the InventoryNumberLocation table, and I've included the Location from that table as well. I'm using the BUILTIN.DF function to get the location name, which eliminates a join to the Location table. For more information on that function, see "SuiteQL Built-In Functions Revisited."

Wrapping Up

In this post I've introduced a couple of tables that are helpful when dealing with serialized / lot numbered items, and specifically items that have expiration dates. In a future post, I'll share some additional queries that you can use to do things like locate an item based on a serial number or lot number.

Until then, if you have any questions about these queries, or SuiteQL in general, please feel free to contact me.