Published on November 3, 2020.

Yesterday I wrote about a NetSuite client that is preparing for an audit, and showed how you can use SuiteQL to provide information on employees, supervisors, roles, and permissions. That same client also asked if it is possible to access journal entries using SuiteQL, and if so, would it also be possible to determine the impact of transactions on the general ledger (the "GL impact").

Most of my work with SuiteQL has involved the activity that occurs in the early stages of the "lead to cash" process. In other words, I've primarily used SuiteQL to work with data that has to do with prospects, customers, vendors, inventory, orders, and fulfillment. With a few exceptions (such as the query that I wrote to generate Comparative Income Statements), I haven't used SuiteQL to work directly with the general ledger. And that's what made this request interesting.

In this post, I'll share a query that I created to retrieve journal entries, and another related query that can be used to get the GL impact of a transaction.

Journal Entries

If you've been using SuiteQL, chances are that you've made use of Transaction table. It's in this table that sales orders, purchase orders, item fulfillments, and a number of other transaction types are stored. So it's probably not surprising that this is also where NetSuite stores journal entires.

In this first query, I'm retrieving the journal entry transactions that occurred within a date range. You can easily adjust the filter criteria to pull transactions based on their posting period.

SELECT
	Transaction.ID,
	Transaction.TranID,
	Transaction.TranDate,
	BUILTIN.DF( Transaction.PostingPeriod ) AS PostingPeriod,
	Transaction.Memo,
	Transaction.Posting,
	BUILTIN.DF( Transaction.Status ) AS Status,
	BUILTIN.DF( Transaction.CreatedBy ) AS CreatedBy
FROM
	Transaction
WHERE
	( Transaction.Type = 'Journal' )
	AND ( Transaction.TranDate BETWEEN TO_DATE( '2020-10-24', 'YYYY-MM-DD' ) AND TO_DATE( '2020-10-31', 'YYYY-MM-DD' ) )
ORDER BY
	Transaction.ID DESC

Notice that I'm using the BUILTIN.DF function to map the transaction posting period, status, and creator from NetSuite's internal IDs to text values. This saves me from making additional joins, and makes the data easier to work with.

The query returns the header information for the journal entries, and it's a good start. But we also need the line level traansaction data to determine the accounts that each journal entry impacted, and the debit and credit amounts.

To get the line level data, I joined from the Transaction table to a table named TransactionAccountingLine.

	
SELECT
	Transaction.ID,
	Transaction.TranID,
	Transaction.TranDate,
	BUILTIN.DF( Transaction.PostingPeriod ) AS PostingPeriod,
	Transaction.Memo,
	Transaction.Posting,
	BUILTIN.DF( Transaction.Status ) AS Status,
	BUILTIN.DF( Transaction.CreatedBy ) AS CreatedBy,
	BUILTIN.DF( TransactionAccountingLine.Account ) AS Account,
	TransactionAccountingLine.Debit,
	TransactionAccountingLine.Credit
FROM
	Transaction
	INNER JOIN TransactionAccountingLine ON
		( TransactionAccountingLine.Transaction = Transaction.ID )
WHERE
	( Transaction.Type = 'Journal' )
	AND ( Transaction.TranDate BETWEEN TO_DATE( '2020-10-24', 'YYYY-MM-DD' ) AND TO_DATE( '2020-10-31', 'YYYY-MM-DD' ) )
ORDER BY
	Transaction.ID DESC

As you can see, its the TransactionAccountingLine table that provides the accounts that the journal entry impacted, and the debit / credit amounts. Once again, I'm using the BUILTIN.DF function to map the Account value, which is NetSuite's internal ID for the account, to the account's name.

And that's really all there is to it. With that query you can easily pull journal entries based on date ranges, posting periods, and even isolate transactions that hit the general ledger based on a specific account, amount, and so on.

GL Impact

My client's second request was for a SuiteQL query that could be used to get the GL impact of a transaction. In this case, I based the query directly on the line-level "TransactionAccountingLine" table.

SELECT
	BUILTIN.DF( TransactionAccountingLine.Account ) AS Account,
	TransactionAccountingLine.Debit,
	TransactionAccountingLine.Credit,
	TransactionAccountingLine.Posting,
	TransactionLine.Memo
FROM
	TransactionAccountingLine
	INNER JOIN TransactionLine ON
		( TransactionLine.Transaction = TransactionAccountingLine. Transaction )
		AND ( TransactionLine.ID = TransactionAccountingLine. TransactionLine )
WHERE
	( TransactionAccountingLine.Transaction = 88417362 )
	AND ( ( TransactionAccountingLine.Debit IS NOT NULL ) 
		OR ( TransactionAccountingLine.Credit IS NOT NULL )
	)
ORDER BY
	TransactionLine.ID

What I like about this query is that it is simple and flexible. It can be used to get the GL impact of any transaction type, whether it's an order, a fulfillment, etc.

Wrapping Up

In this post, I've shown how you can query the Transaction table to get journal entries, and make use of the TransactionAccountingLine to get accounts and amounts of those entries. I also showed how you can use that table to determine the GL impact of a transaction, regardless of the its type.

If you have any questions about the queries that I've shared in this post, please feel free to contact me.