Published on April 24, 2022.

Recently, a NetSuite client asked me for help with a custom integration project. Their goal was to identify items whose inventory levels have changed within a certain period of time, so that they could load the IDs of those items into their FileMaker-based PIM (Product Information Manager).

In this post, I'll show you the queries that I used to help with this project, and how they evolved. I'll walk you through a challenge that I ran into with regards to searching on transaction dates, and how I was able to resolve it. I'll also show how you can easily identify transactions that have inventory impact.

Searching on Transaction Dates

While working on this project, the big issue that I ran into had to do with the Transaction.TranDate column, and some odd behavior that I see with it when querying with SuiteQL. From what I can tell, when it comes to SuiteQL, the Transaction.TranDate value is always midnight (12:00:00 AM).

Here's a query that shows this strange behaviour.

SELECT TOP 10 
	Transaction.ID,
	Transaction.TranID,
	TranDate,
	TO_CHAR ( TranDate, 'DS TS' ) AS TranDateTS
FROM
	Transaction
WHERE
	( TranDate = TO_DATE( '2022-04-21', 'YYYY-MM-DD' ) )
ORDER BY
	ID DESC

Click the image to view a larger version.

I'm using the Oracle TO_DATE function to display the full date and time portion of the transaction dates. (I discussed that function in this post.) Notice that the values are all 4/21/2022 12:00:00 AM.

The reason that this became an issue is that my client needs to be able to identify items whose inventory levels have changed at a more granular level - for example, within the last hour.

At first I thought that this might be a display issue. In other words, perhaps SuiteQL only returned midnight in the results, but that you can filter on the TranDate using a time.

So next, I tried filtering by refining the critera, and adding a time to them. Here I'm asking for all transactions created between noon at 11:49 PM.

SELECT TOP 10 
	Transaction.ID,
	Transaction.TranID,
	TranDate,
	TO_CHAR ( TranDate, 'DS TS' ) AS TranDateTS
FROM
	Transaction
WHERE
	( TranDate BETWEEN TO_DATE( '2022-04-21 12:00', 'YYYY-MM-DD HH24:MI' ) AND TO_DATE( '2022-04-21 23:59', 'YYYY-MM-DD HH24:MI:SS' )  )
ORDER BY
	ID DESC

The query returned no records.

Similarly, this query, which is requesting transactions created between 12:01 AM and 12:59 PM, also returns no records.

SELECT TOP 10 
	Transaction.ID,
	Transaction.TranID,
	TranDate,
	TO_CHAR ( TranDate, 'DS TS' ) AS TranDateTS
FROM
	Transaction
WHERE
	( TranDate BETWEEN TO_DATE( '2022-04-21 00:01', 'YYYY-MM-DD HH24:MI' ) AND TO_DATE( '2022-04-21 23:59', 'YYYY-MM-DD HH24:MI:SS' )  )
ORDER BY
	ID DESC

However, the following query does return records.

SELECT TOP 10 
	Transaction.ID,
	Transaction.TranID,
	TranDate,
	TO_CHAR ( TranDate, 'DS TS' ) AS TranDateTS
FROM
	Transaction
WHERE
	( TranDate BETWEEN TO_DATE( '2022-04-21 00:00', 'YYYY-MM-DD HH24:MI' ) AND TO_DATE( '2022-04-21 23:59', 'YYYY-MM-DD HH24:MI:SS' )  )
ORDER BY
	ID DESC

Click the image to view a larger version.

This seems to indicate that, at least as SuiteQL goes, the TranDate value really is stored with a time component of midnight.

A Solution

To resolve this issue, the solution that I came up with involves the SystemNote table, which I've also written about in a previous post. The SystemNote table is used to maintain a log of changes made to records.

SuiteQL does allow us to query the SystemNote table using the full datetime value. Therefore, my thought was that I could join from the the Transaction table to the SystemNote table - and specifically to the record that represents the creation of the transaction record - and then filter transactions based on the full datetime value.

Here's the first query that I worked up, which joins the Transaction and SystemNote tables.

	
SELECT TOP 10 
	Transaction.ID,
	Transaction.TranID,
	Transaction.TranDate,
	TO_CHAR ( Transaction.TranDate, 'DS TS' ) AS TranDateTS,
	TO_CHAR ( SystemNote.Date, 'DS TS' ) AS SystemNoteTS
FROM
	Transaction
	INNER JOIN SystemNote ON
		( SystemNote.RecordTypeID = -30 )
		AND ( SystemNote.RecordID = Transaction.ID )
		AND ( SystemNote.Field = 'TRANDOC.NKEY' )
		AND ( SystemNote.OldValue IS NULL )		
ORDER BY
	Transaction.ID DESC

As you can see, I'm joining to SystemNote using several columns. The RecordTypeID value of -30 is used to indicate that I'm looking for the records that involve transaction records. The RecordID must match the internal ID of the Transaction (Transaction.ID). I'm looking specifically for records that reflect changes to the "TRANDOC.NKEY" field, which is actually the Transaction.ID column. And furthermore, I want records that reflect changes to that column where the original value was NULL. In other words, I want the SystemNote record that reflects when NetSuite assigned an ID to a Transaction record.

Here's a sample of the results.

Click the image to view a larger version.

Notice that SystemNoteTS does show the full timestamp value.

Next, I applied some datetime filters to the query.

SELECT TOP 10 
	Transaction.ID,
	Transaction.TranID,
	Transaction.TranDate,
	TO_CHAR ( Transaction.TranDate, 'DS TS' ) AS TranDateTS,
	TO_CHAR ( SystemNote.Date, 'DS TS' ) AS SystemNoteTS
FROM
	Transaction
	INNER JOIN SystemNote ON
		( SystemNote.RecordTypeID = -30 )
		AND ( SystemNote.RecordID = Transaction.ID )
		AND ( SystemNote.Field = 'TRANDOC.NKEY' )
		AND ( SystemNote.OldValue IS NULL )	
WHERE
	( Transaction.TranDate = TO_DATE( '2022-04-21', 'YYYY-MM-DD' ) )
	AND ( SystemNote.Date BETWEEN TO_DATE( '2022-04-21 00:00', 'YYYY-MM-DD HH24:MI' ) AND TO_DATE( '2022-04-21 23:59', 'YYYY-MM-DD HH24:MI:SS' )  )	
ORDER BY
	Transaction.ID DESC

Notice that I'm filtering on both Transaction.TranDate and SystemNote.Date. At first glance it might appear that the Transaction.TranDate filter is unnecessary, and that the filter on SystemNote.Date is all that we need. The reason that I'm also filtering on Transaction.TranDate has to do with the efficiency and performance of the query. By applying that filter, Oracle is able to limit the number of Transaction records before joining to the SystemNote table. In other words, we're helping the query processor a bit. And as a result, the boost in performance is significant.

Here are the results of that query.

Click the image to view a larger version.

As you can see, I'm now getting the transactions that were created between the two datetime values.

And of course, I can refine the filters a bit to get transactions that were created between two very specific times. For example, here's a query that returns the transactions created between 1:00 PM and 1:59 PM.

SELECT TOP 10 
	Transaction.ID,
	Transaction.TranID,
	Transaction.TranDate,
	TO_CHAR ( Transaction.TranDate, 'DS TS' ) AS TranDateTS,
	TO_CHAR ( SystemNote.Date, 'DS TS' ) AS SystemNoteTS
FROM
	Transaction
	INNER JOIN SystemNote ON
		( SystemNote.RecordTypeID = -30 )
		AND ( SystemNote.RecordID = Transaction.ID )
		AND ( SystemNote.Field = 'TRANDOC.NKEY' )
		AND ( SystemNote.OldValue IS NULL )	
WHERE
	( Transaction.TranDate = TO_DATE( '2022-04-21', 'YYYY-MM-DD' ) )
	AND ( SystemNote.Date BETWEEN TO_DATE( '2022-04-21 13:00', 'YYYY-MM-DD HH24:MI' ) AND TO_DATE( '2022-04-21 13:59', 'YYYY-MM-DD HH24:MI:SS' )  )	
ORDER BY
	Transaction.ID DESC

It seems silly to have to go through all of this, but at least it works.

Inventory Impacting Transactions

Remember that my client's main goal was to get all inventory impacting transactions between two datetime values. Specifically, they wanted the items that were impacted.

Let's start by adjusting the previous query.

SELECT TOP 10 
	Transaction.ID,
	Transaction.TranID,
	Transaction.TranDate,
	TO_CHAR ( Transaction.TranDate, 'DS TS' ) AS TranDateTS,
	TO_CHAR ( SystemNote.Date, 'DS TS' ) AS SystemNoteTS,
	TransactionLine.Item,
	BUILTIN.DF( TransactionLine.Item ) AS ItemID
FROM
	Transaction
	INNER JOIN TransactionLine ON
		( TransactionLine.Transaction = Transaction.ID )	
	INNER JOIN SystemNote ON
		( SystemNote.RecordTypeID = -30 )
		AND ( SystemNote.RecordID = Transaction.ID )
		AND ( SystemNote.Field = 'TRANDOC.NKEY' )
		AND ( SystemNote.OldValue IS NULL )			
WHERE
	( Transaction.TranDate = TO_DATE( '2022-04-21', 'YYYY-MM-DD' ) )
	AND ( TransactionLine.IsInventoryAffecting = 'T' )	
	AND ( SystemNote.Date BETWEEN TO_DATE( '2022-04-21 13:00', 'YYYY-MM-DD HH24:MI' ) AND TO_DATE( '2022-04-21 13:59', 'YYYY-MM-DD HH24:MI:SS' )  )	
ORDER BY
	Transaction.ID DESC

I've added a join to TransactionLine, added a filter on the TransactionLine.IsInventoryAffecting column, and added the TransactionLine.Item column (which is actually NetSuite's internal ID for the item), as well as the Item ID (which I'm getting using the BUILTN.DF function) to the results. If you're not familiar with the BUILTIN.DF function, this blog post might be helpful to you.

That query will return all transactions, and the items whose inventory levels were affected, within the datetime range. That's good, but what my client really wanted is a concise, non-duplicated list of the item IDs that were impacted.

To get that, I adjusted the query and used the DISTINCT keyword, like this.

SELECT DISTINCT 
	TransactionLine.Item
FROM
	Transaction
	INNER JOIN TransactionLine ON
		( TransactionLine.Transaction = Transaction.ID )	
	INNER JOIN SystemNote ON
		( SystemNote.RecordTypeID = -30 )
		AND ( SystemNote.RecordID = Transaction.ID )
		AND ( SystemNote.Field = 'TRANDOC.NKEY' )
		AND ( SystemNote.OldValue IS NULL )			
WHERE
	( Transaction.TranDate = TO_DATE( '2022-04-21', 'YYYY-MM-DD' ) )
	AND ( TransactionLine.IsInventoryAffecting = 'T' )	
	AND ( SystemNote.Date BETWEEN TO_DATE( '2022-04-21 13:00', 'YYYY-MM-DD HH24:MI' ) AND TO_DATE( '2022-04-21 13:59', 'YYYY-MM-DD HH24:MI' )  )	

Wrapping Up

In previous blog posts, I've written about how working with dates and times when using SuiteQL can be tricky. The odd way in which SuiteQL works when it comes to the Transaction.TranDate column makes that even more confusing. However, as I've shown above, we can overcome that limitation by joining to the SystemNote table, and filtering off of it.

As always, I hope you've found this post to be helpful.