Published on August 10, 2020.

Here's a follow-up to this post. It's a free Suitelet that you can use to lookup an item's inventory balance history. The Suitelet uses a modified version of the query described below.

Last week, one of my NetSuite clients came to me with an interesting project. They're in the process of evaluating inventory usage, and needed a way to see historic inventory levels for certain items. In other words, they needed a way to know what the inventory level was for a given product on any given day.

In this post, I'm going to describe how I used SuiteQL to get them this data, and the process that I went through to create the SQL query.

The TransactionLine Table

My first step was to determine what table the inventory transaction data is stored in. At first I thought I'd only need the Transaction table. However, it was actually lineitem-level data that I needed, and that's stored in the TransactionLine table.

Here was my initial query.

SELECT
	TransactionLine.Transaction AS TransactionID,	
	TransactionLine.Rate,
	TransactionLine.NetAmount,
	TransactionLine.Quantity
FROM
	TransactionLine
WHERE
	( TransactionLine.Item = 64428 )

To develop the SQL, I used the SuiteQL Query Tool that I released last week. Here are the results of that query.

Joining to the Transaction Table

That's a good start, but I also needed data from the transaction headers, such as the transaction date, type, and so on. To get that data, I simply joined to the Transaction table. Here's the revised query.

SELECT
	TransactionLine.Transaction AS TransactionID,	
	Transaction.TranDate,
	Transaction.Type,
	Transaction.TranID,
	TransactionLine.Rate,
	TransactionLine.NetAmount,
	TransactionLine.Quantity
FROM
	TransactionLine
	INNER JOIN Transaction ON
		( Transaction.ID = TransactionLine.Transaction )
WHERE
	( TransactionLine.Item = 64428 )
	AND ( Transaction.Voided = 'F' )
ORDER BY
	Transaction.TranDate,
	Transaction.TranID

Notice that I've added an additional filter to the WHERE clause, so that voided transactions are omitted. Here's the updated response.

Joining to the Entity Table

I also wanted to include the customer that was associated with the transaction. To get that, I simply joined to the Entity table. Here's the query.

SELECT
	TransactionLine.Transaction AS TransactionID,	
	Transaction.TranDate,
	Transaction.Type,
	Transaction.TranID,
	TransactionLine.Rate,
	TransactionLine.NetAmount,
	TransactionLine.Quantity,
	Entity.AltName
FROM
	TransactionLine
	INNER JOIN Transaction ON
		( Transaction.ID = TransactionLine.Transaction )
	LEFT OUTER JOIN Entity ON
		( Entity.ID = Transaction.Entity )
WHERE
	( TransactionLine.Item = 64428 )
	AND ( Transaction.Voided = 'F' )
ORDER BY
	Transaction.TranDate,
	Transaction.TranID

In this case, I'm using a LEFT OUTER JOIN. The reason for this is that some transactions do not have an entity associated with them, such as inventory adjustments. By using a LEFT OUTER JOIN, those transactions will not be excluded from the result. Here's the updated result.

Transactions With Inventory Impact

At this point, the query returned all of the transactions associated with an item. However, I really only wanted the transactions that had an impact on the inventory level. To add this additional filter, I used a column on the TransactionLine table named "IsInventoryAffecting." That's a boolean value indicating whether or not the related transaction has an impact on the inventory level.

Here's the updated query.

SELECT
	TransactionLine.Transaction AS TransactionID,	
	Transaction.TranDate,
	Transaction.Type,
	Transaction.TranID,
	TransactionLine.Rate,
	TransactionLine.NetAmount,
	TransactionLine.Quantity,
	Entity.AltName
FROM
	TransactionLine
	INNER JOIN Transaction ON
		( Transaction.ID = TransactionLine.Transaction )
	LEFT OUTER JOIN Entity ON
		( Entity.ID = Transaction.Entity )
WHERE
	( TransactionLine.Item = 64428 )
	AND ( Transaction.Voided = 'F' )
	AND ( TransactionLine.IsInventoryAffecting = 'T' )
ORDER BY
	Transaction.TranDate,
	Transaction.TranID

And here's the response. Notice that it now includes only the types of transactions that have an impact on the inventory level, such as item receipts, fulfillments, and adjustments.

The Running Inventory Balance

At this point, I was close to having the data that my customer needed. All that remained was getting the inventory balance after each transaction had occurred.

Getting the running inventory balance - or any "running" aggregate value - can be tricky. What I really needed was the sum of the transaction line quantity, but only up to the point of each row.

Oracle makes this possible by providing Analytic Functions. These functions "compute an aggregate value based on a group of rows." What makes them different from standard aggregate functions is that they "return multiple rows for each group." I think this is one of those concepts that's easier to understand when you see it in use.

Here's the updated query.

SELECT
	TransactionLine.Transaction AS TransactionID,	
	Transaction.TranDate,
	Transaction.Type,
	Transaction.TranID,
	TransactionLine.Rate,
	TransactionLine.NetAmount,
	TransactionLine.Quantity,	
	Entity.AltName,
	SUM( TransactionLine.Quantity ) 
		OVER ( 
			ORDER BY 
				Transaction.TranDate, 
				Transaction.TranID, 
				TransactionLine.ID 
				RANGE UNBOUNDED PRECEDING 
		) Qty_Balance
FROM
	TransactionLine
	INNER JOIN Transaction ON
		( Transaction.ID = TransactionLine.Transaction )
	LEFT OUTER JOIN Entity ON
		( Entity.ID = Transaction.Entity )
WHERE
	( TransactionLine.Item = 64428 )
	AND ( TransactionLine.IsInventoryAffecting = 'T' )
	AND ( Transaction.Voided = 'F' )
ORDER BY
	Transaction.TranDate,
	Transaction.TranID,
	TransactionLine.ID

Notice the SUM aggregate function and how it's being used in this query. The "OVER" keyword is used to indicate that we want to use the function on the query's result. The "RANGE" keyword indicates that the function is being calculated against a window of rows which will be specified as a logical offset. In this case, the offset is specified as "UNBOUNDED PRECEDING," which is the first row through the current row. And the ORDER BY clause indicates how the data should be ordered within each window / partition.

Also notice that the ORDER BY for the SUM includes the transaction date, transaction ID, and transaction line. This level of detail is required because the sum could potentially be calculated incorrectly against other similar transactions that occurred on the same date, and possibly multiple lines on the same transaction as well.

Here's the final result, with the Qty_Balance column.

Wrapping Up

The final step of this project was to make the query available to the external system by incorporating it into a RESTlet. The external system is now making calls to the RESTlet, specifying an item and an optional "as of" date, and getting the transaction data that it needs.

This was one of those "quick win" projects that I really enjoy working on. And I don't think I could have pulled it off as quickly as I did had I not used SuiteQL.