Published on January 10, 2022.

One of the more common requests that I receive from my NetSuite clients involves GL Impact, and specifically, whether or not it's possible to get a transaction's GL impact using SuiteQL. This topic also came up last week on the NetSuite Professionals Slack Community.

The good news is that yes, you can use SuiteQL to get GL impact. And in fact, it's pretty easy to do.

I first wrote about this in a post from November 2020. But in this post I'm sharing a query whose results more closely resemble NetSuite's native GL Impact function.

The Query

The key to getting GL impact is in using a little-known table named TransactionAccountingLine. You join to that table from the Transaction table, and you can also join to the accounting line's corresponding transaction line.

Here's an example query:

SELECT
	Transaction.TranID,
	BUILTIN.DF( TransactionAccountingLine.AccountingBook ) AS AccountingBookName,
	BUILTIN.DF( TransactionAccountingLine.Account ) AS Account,
	TransactionAccountingLine.Debit,
	TransactionAccountingLine.Credit,
	TransactionAccountingLine.Posting,
	BUILTIN.DF( Transaction.Entity ) AS EntityName,
	TransactionLine.Memo,
	BUILTIN.DF( TransactionLine.Subsidiary ) AS Subsidiary,
	BUILTIN.DF( TransactionLine.Department ) AS Department
FROM 
	Transaction
	INNER JOIN TransactionAccountingLine ON
		( TransactionAccountingLine.Transaction = Transaction.ID )
	LEFT OUTER JOIN TransactionLine ON
		( TransactionLine.Transaction = TransactionAccountingLine.Transaction )
		AND ( TransactionLine.ID = TransactionAccountingLine.TransactionLine )
WHERE 
	( Transaction.ID = 26254 )
	AND ( TransactionAccountingLine.Account IS NOT NULL )
ORDER BY
	TransactionAccountingLine.TransactionLine

In the query, I'm specifying the internal ID of the transaction that I want the GL impact for - in this case, transaction 26254, which happens to be a customer invoice.

I'm joining from the Transaction table, to the TransactionAccountingLine table, and then to the TransactionLine table.

I've included the "AccountingBookName" for clients who are using NetSuite Multi-Book.

And I'm making extensive use of the BUILTIN.DF function, so that I can avoid having to make additional joins to the Account, Entity, and other tables.

Here's an example of the query results.

Wrapping Up

This is one of those commonly requested queries that involves a table that is almost impossible to find using tools like the Records Catalog. In this case, that's the TransactionAccountingLine table. But once you know about it, and work out the joins, the query is actually quite simple.

You can find this query listed in the SuiteQL Query Tool "Remote Library." Simply search for "GL Impact."