Published on February 11, 2024.

Last week I wrote about how you can query NetSuite to get aggregate item information based on inventory locations. The query that I shared used the AggregateItemLocation table. The table includes columns for things such as quantity on hand, quantity on order, quantity available, and more.

Another column that the AggregateItemLocation table provides is "LastQuantityAvailableChange." The column is a DateTime type, and it represents the last time that the item's quantity available changed in a specific location.

LastQuantityAvailableChange is particularly helpful in cases where you need to identify items whose inventory level has changed in such a way that it might impact your ability to fulfill orders. For example, I'm using it as part of an Web API where my customer is providing inventory information to their clients. I'm using the column to provide the API's consumers with a list of items whose inventory levels have changed in the past few days.

Here's an example of the query that I developed for that project.

SELECT
	BUILTIN.DF( Item ) AS Item,
	BUILTIN.DF( Location ) AS Location,
	QuantityOnHand,
	QuantityCommitted,
	QuantityAvailable,	
	QuantityBackOrdered,
	QuantityOnOrder,
	QuantityInTransit,
	TO_CHAR ( LastQuantityAvailableChange, 'YYYY-MM-DD HH12:MI:SS PM' ) AS LastQtyAvailChangeTS
FROM
	AggregateItemLocation
WHERE
	( LastQuantityAvailableChange > BUILTIN.RELATIVE_RANGES( 'DAGO3', 'START' ) )
ORDER BY
	Item,
	Location

The query returns all items whose quantity available has changed in the past 3 days. I'm using BUILTIN.RELATIVE_RANGES to specify the date criteria.

I'm using the TO_CHAR function to format the LastQuantityAvailableChange value. By default, only the date portion of the value is returned. With TO_CHAR, the value is being returned with both the date and time, and the time is formated in such a way that it looks like this: 2024-02-09 09:22:06 AM

If you simply want a list of the IDs of items whose quantity available has changed, regardless of the inventory location, you can use a query like this.

SELECT DISTINCT
	Item
FROM
	AggregateItemLocation
WHERE
	( LastQuantityAvailableChange > BUILTIN.RELATIVE_RANGES( 'DAGO3', 'START' ) )

There are a number of other columns available via the AggregateItemLocation table that you might find to be helpful. Here's a list of its columns, as well as information on the NetSuite features that need to be enabled for some of the columns to be applicable.