NetSuite: Use SuiteQL to Get Last Purchased Date and Price for Multi-Location Inventory

One of my larger NetSuite clients maintains inventory in multiple locations around the world, so they're using the Multi-Location Inventory feature.

This client recently asked me to develop a SuiteQL query that could be used to get purchase history based on each location. Specifically, they wanted the date that each item was last purchased, the number of days that have elapsed since the purchase date, and the last purchase price, and they wanted this information for each location.

Last Purchased Date In Single-Location Inventory Scenarios

On a previous project for another client, I had developed a query that provided the last purchase date for each of their items. While that project was for a client that manages inventory in a single location, I suspected that it would serve as a good foundation for a multi-location version.

Here's what that original query looked like.

SELECT
	TransactionLine.Item,
	BUILTIN.DF( TransactionLine.Item ) AS ItemID,
	MAX( TranDate ) AS LastPurchasedDate,
	ROUND ( SYSDATE - MAX( TranDate ) ) AS LastPurchasedDays
FROM
	Transaction
	INNER JOIN TransactionLine ON
		( TransactionLine.Transaction = Transaction.ID )
WHERE
	( Transaction.Type = 'PurchOrd' )
GROUP BY
	TransactionLine.Item,
	BUILTIN.DF( TransactionLine.Item )

To get the last purchase date, I was simply grouping purchase orders by item, and then using the MAX function on the transaction date.

Last Purchased Date In Multi-Location Inventory Scenarios

To provide the last purchase date by location, I simply added the location column to the SELECT and GROUP BY clauses.

Here's the modified query.

SELECT
	TransactionLine.Item,
	BUILTIN.DF( TransactionLine.Item ) AS ItemID,
	TransactionLine.Location,
	BUILTIN.DF( TransactionLine.Location ) AS LocationName,
	MAX( TranDate ) AS LastPurchasedDate,
	ROUND ( SYSDATE - MAX( TranDate ) ) AS LastPurchasedDays
FROM
	Transaction
	INNER JOIN TransactionLine ON
		( TransactionLine.Transaction = Transaction.ID )
WHERE
	( Transaction.Type = 'PurchOrd' )
GROUP BY
	TransactionLine.Item,
	BUILTIN.DF( TransactionLine.Item ),
	TransactionLine.Location,
	BUILTIN.DF( TransactionLine.Location )

Notice that the Location value is stored at the lineitem-level (i.e. the TransactionLine table) - not at the header (i.e. the Transaction table). I'm pointing this out because in the NetSuite user interface, a transaction's Location value is presented in such a way that it does appear to be a header-level value.

The AggregateItemLocation Table

To easily get to the location-based Last Purchase Price, I added a join to the AggregateItemLocation table. This table is used to store location-specific item information, including Last Purchase Price, Average Cost, Last Count Date, and much, much more.

Here's the revised query that includes an INNER JOIN to the AggregateItemLocation table, and references to the "LastPurchasePriceMli" column in both the SELECT and GROUP BY clauses.

SELECT
	TransactionLine.Item,
	BUILTIN.DF( TransactionLine.Item ) AS ItemID,
	TransactionLine.Location,
	BUILTIN.DF( TransactionLine.Location ) AS LocationName,
	MAX( TranDate ) AS LastPurchasedDate,
	ROUND ( SYSDATE - MAX( TranDate ) ) AS LastPurchasedDays,
	AggregateItemLocation.LastPurchasePriceMli AS LastPurchasePrice
FROM
	Transaction
	INNER JOIN TransactionLine ON
		( TransactionLine.Transaction = Transaction.ID )
	INNER JOIN AggregateItemLocation ON
		( AggregateItemLocation.Item = TransactionLine.Item )
		AND ( AggregateItemLocation.Location = TransactionLine.Location )
WHERE
	( Transaction.Type = 'PurchOrd' )
GROUP BY
	TransactionLine.Item,
	BUILTIN.DF( TransactionLine.Item ),
	TransactionLine.Location,
	BUILTIN.DF( TransactionLine.Location ),
	AggregateItemLocation.LastPurchasePriceMli

In a future post, I'll show how I used this query as the basis for a custom Stock Replenishment report.