Published on April 23, 2023.

In a post from November of 2020 (Use SuiteQL to Access Journal Entries and Determine GL Impact), I shared some NetSuite SuiteQL queries that can be used to access journal entries and get a transaction's general ledger impact. In this post I'm going to share similar queries that you might find to be helpful.

I'm currently working on a project that involves developing a custom NetSuite app to provide users with an alternative interface for viewing, reporting on, and exporting journal entries. The app includes support for filtering transactions based on subsidiaries, departments, classes, locations, and more.

Journal Entries by Accounting Period

Let's start with a simple query that returns journal entries for a specified accounting period.

SELECT
	Transaction.ID,
	Transaction.TranID AS DocumentNumber,
	Transaction.TranDate,
	AccountingPeriod.PeriodName AS PostingPeriod,	
	Entity.EntityID,
	Transaction.Memo	
FROM
	Transaction
	INNER JOIN AccountingPeriod ON
		( AccountingPeriod.ID = Transaction.PostingPeriod )
	LEFT OUTER JOIN Entity ON
		( Entity.ID = Transaction.Entity )
WHERE
	( Transaction.Type = 'Journal' )
	AND ( Transaction.Voided = 'F' )
	AND ( Transaction.Void = 'F' )
	AND ( AccountingPeriod.PeriodName = 'Nov 2021' )
ORDER BY
	DocumentNumber

Here are the results of the query.

Click the image to view a larger version.

So there were 12 journal entries made in the specified accounting period.

Notice that I'm using a LEFT OUTER JOIN to join to the Entity table. The reason is that many journal entries do not have an entity value. If I had used an INNER JOIN, then only transactions that have an entity will be returned. By using a LEFT OUTER JOIN, the transactions that do not have an entity will be returned.

Journal Entry Details

I also needed to return details for each journal entry, including the accounts that were posted to, and the debit and credit amounts. To do that, I join from a Transaction to its related TransactionAccountingLine records.

SELECT
	Transaction.ID,
	Transaction.TranID AS DocumentNumber,
	Transaction.TranDate,
	AccountingPeriod.PeriodName AS PostingPeriod,	
	Entity.EntityID,
	Transaction.Memo,
	BUILTIN.DF( TransactionAccountingLine.Account ) AS Account,
	TransactionAccountingLine.Debit,
	TransactionAccountingLine.Credit	
FROM
	Transaction	
	INNER JOIN AccountingPeriod ON
		( AccountingPeriod.ID = Transaction.PostingPeriod )
	LEFT OUTER JOIN Entity ON
		( Entity.ID = Transaction.Entity )
	INNER JOIN TransactionAccountingLine ON
		( TransactionAccountingLine.Transaction = Transaction.ID )		
WHERE
	( Transaction.Type = 'Journal' )
	AND ( Transaction.Voided = 'F' )
	AND ( Transaction.Void = 'F' )
	AND ( AccountingPeriod.PeriodName = 'Nov 2021' )
ORDER BY
	DocumentNumber

Here are the results.

Click the image to view a larger version.

Notice that the query returned 174 rows. While there were 12 journal entries made in the specified accounting period, some of them had multiple related detail records. Each journal entry had at least two details records - a debit and a credit. But some journal entries had more details than that.

Adding Subsidiary, Location, Department, and Class

My next goal was to include each transaction's subsidiary and segment values (location, department, and class). In the NetSuite UI, it might appear that those values are stored on the Transaction record. However, they're actually stored on the TransactionLine record. To get to them, I usually join from a Transaction to its "mainline" TransactionLine. The query would look like this.

SELECT
	Transaction.ID,
	Transaction.TranID AS DocumentNumber,
	Transaction.TranDate,
	AccountingPeriod.PeriodName AS PostingPeriod,	
	Entity.EntityID,
	Transaction.Memo,
	BUILTIN.DF( TransactionAccountingLine.Account ) AS Account,
	TransactionAccountingLine.Debit,
	TransactionAccountingLine.Credit,
	BUILTIN.DF( TransactionLine.Subsidiary ) AS Subsidiary,
	BUILTIN.DF( TransactionLine.Location ) AS Location,
	BUILTIN.DF( TransactionLine.Department ) AS Department,
	BUILTIN.DF( TransactionLine.Class ) AS Class
FROM
	Transaction	
	INNER JOIN AccountingPeriod ON
		( AccountingPeriod.ID = Transaction.PostingPeriod )
	LEFT OUTER JOIN Entity ON
		( Entity.ID = Transaction.Entity )
	INNER JOIN TransactionAccountingLine ON
		( TransactionAccountingLine.Transaction = Transaction.ID )	
	INNER JOIN TransactionLine ON
		( TransactionLine.Transaction = Transaction.ID )	
		AND ( TransactionLine.MainLine = 'T' )
WHERE
	( Transaction.Type = 'Journal' )
	AND ( Transaction.Voided = 'F' )
	AND ( Transaction.Void = 'F' )
	AND ( AccountingPeriod.PeriodName = 'Nov 2021' )
ORDER BY
	DocumentNumber

Here are the results.

Click the image to view a larger version.

Notice that the number of rows returned has jumped from 174 to over 5,000 - so something's not right. The problem is that the all of a journal entry transaction's lines are flagged as being "mainline." As a result, each journal entry detail record is shown multiple times.

You could resolve this by using a SELECT DISTINCT query, but that would be very inefficient. Oracle would first need to resolve the query (identifying the 5,000+ rows that meet the criteria) before applying the DISTINCT criteria to the resulting rows.

A better approach - and the one that I used - is to tighten up the join criteria so that only the first TransactionLine record is included in the results. My testing showed that this approach was 5 times faster than using SELECT DISTINCT.

To include only the first TransactionLine record, you can filter on the TransactionLine's LineSequenceNumber value. Specifically, only join to the TransactionLine record whose LineSequenceNumber is zero.

Here's the revised query.

SELECT
	Transaction.ID,
	Transaction.TranID AS DocumentNumber,
	Transaction.TranDate,
	AccountingPeriod.PeriodName AS PostingPeriod,	
	Entity.EntityID,
	Transaction.Memo,
	BUILTIN.DF( TransactionAccountingLine.Account ) AS Account,
	TransactionAccountingLine.Debit,
	TransactionAccountingLine.Credit,
	BUILTIN.DF( TransactionLine.Subsidiary ) AS Subsidiary,
	BUILTIN.DF( TransactionLine.Location ) AS Location,
	BUILTIN.DF( TransactionLine.Department ) AS Department,
	BUILTIN.DF( TransactionLine.Class ) AS Class
FROM
	Transaction	
	INNER JOIN AccountingPeriod ON
		( AccountingPeriod.ID = Transaction.PostingPeriod )
	LEFT OUTER JOIN Entity ON
		( Entity.ID = Transaction.Entity )
	INNER JOIN TransactionAccountingLine ON
		( TransactionAccountingLine.Transaction = Transaction.ID )	
	INNER JOIN TransactionLine ON
		( TransactionLine.Transaction = Transaction.ID )	
		AND ( TransactionLine.MainLine = 'T' )
		AND ( TransactionLine.LineSequenceNumber = 0 )
WHERE
	( Transaction.Type = 'Journal' )
	AND ( Transaction.Voided = 'F' )
	AND ( Transaction.Void = 'F' )
	AND ( AccountingPeriod.PeriodName = 'Nov 2021' )
ORDER BY
	DocumentNumber

And here are the results.

Click the image to view a larger version.

Wrapping Up

That last query is essentially what I used to develop the custom NetSuite app that I described earlier. The app provides a nice, simple UI that makes it easy for users to apply their filter criteria, quickly see the results, print reports, and export the data.

As always, I hope you find the queries that I've shared to be helpful.