Published on July 19, 2023.
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.
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.
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.
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.
Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on NetSuite, including mobile apps, Web portals, Web APIs, and more.
I'm the developer of several popular NetSuite open source solutions, including the SuiteQL Query Tool, SuiteAPI, and more.
I founded SuiteStep, a NetSuite development studio, to provide custom software and AI solutions - and continue pushing the boundaries of what's possible on the NetSuite platform.
Copyright © 2025 Tim Dietrich.