Published on August 15, 2022.

A new accountant for one of my NetSuite clients recently reached out to me for help. He requested a few SuiteQL queries that would help him get an understanding of how various types of transactions are being posted to the general ledger. In this post, I'll share some of those queries.

General Ledger Impact by Transaction Type

The first request was for a query that would list each transaction type and the general ledger accounts that it posted to.

Here's the query that I created to help with that request.

SELECT DISTINCT
	BUILTIN.DF( Transaction.Type ) AS TransactionType,
	Account.AcctType AS AccountType,
	Account.DisplayNameWithHierarchy AS AccountHierarchy
FROM 
	Transaction
	INNER JOIN TransactionAccountingLine ON
		( TransactionAccountingLine.Transaction = Transaction.ID )
	INNER JOIN Account ON
		( Account.ID = TransactionAccountingLine.Account )
WHERE
	( TransactionAccountingLine.Posting = 'T' )
ORDER BY
	TransactionType,
	AccountType,
	AccountHierarchy

General Ledger Impact by Transaction Type Over A Date Range

The next request was for a query that listed the transaction types that occurred over a given date range, showing the general ledger accounts that they posted to, and the total debit, credit, and total amounts.

Here's the query for that request.

SELECT
	BUILTIN.DF( Transaction.Type ) AS TransactionType,
	BUILTIN.DF( Account.AcctType ) AS AccountType,
	Account.DisplayNameWithHierarchy AS AccountHierarchy,
	SUM( TransactionAccountingLine.Debit ) AS Debits,
	SUM( TransactionAccountingLine.Credit ) AS Credits,
	SUM( TransactionAccountingLine.Amount ) AS Amount
FROM 
	Transaction
	INNER JOIN TransactionAccountingLine ON
		( TransactionAccountingLine.Transaction = Transaction.ID )
	INNER JOIN Account ON
		( Account.ID = TransactionAccountingLine.Account )
WHERE
	( Transaction.TranDate >= BUILTIN.RELATIVE_RANGES( 'DAGO30', 'START' ) )
	AND ( TransactionAccountingLine.Posting = 'T' )
GROUP BY
	BUILTIN.DF( Transaction.Type ),
	BUILTIN.DF( Account.AcctType ),
	Account.DisplayNameWithHierarchy
ORDER BY
	TransactionType,
	AccountType,
	DisplayNameWithHierarchy

Notice that I've changed the query so that it uses a GROUP BY clause instead of SELECT DISTINCT. This makes it possible to use the SUM aggregate function.

In this case, transactions that have occurred in the past 30 days are included. Note the use of the "BUILTIN.RELATIVE_RANGES" function in the WHERE clause, which is used to specify a dynamic calendar range. For more information on dynamic calendar date ranges, check out this blog post: https://timdietrich.me/blog/netsuite-suiteql-dynamic-calendar-date-ranges/

Posting and Non-Posting Transactions

One additional request was to show both posting and non-posting transactions, and the totals for each.

For this request, I simply added the "TransactionAccountingLine.Posting" column to the SELECT, GROUP BY, and ORDER BY clauses, and removed the "TransactionAccountingLine.Posting = 'T'" condition from the WHERE clause.

SELECT
	BUILTIN.DF( Transaction.Type ) AS TransactionType,
	BUILTIN.DF( Account.AcctType ) AS AccountType,
	Account.DisplayNameWithHierarchy AS AccountHierarchy,
	TransactionAccountingLine.Posting,
	SUM( TransactionAccountingLine.Debit ) AS Debits,
	SUM( TransactionAccountingLine.Credit ) AS Credits,
	SUM( TransactionAccountingLine.Amount ) AS Amount
FROM 
	Transaction
	INNER JOIN TransactionAccountingLine ON
		( TransactionAccountingLine.Transaction = Transaction.ID )
	INNER JOIN Account ON
		( Account.ID = TransactionAccountingLine.Account )
WHERE
	( Transaction.TranDate >= BUILTIN.RELATIVE_RANGES( 'DAGO30', 'START' ) )
GROUP BY
	BUILTIN.DF( Transaction.Type ),
	BUILTIN.DF( Account.AcctType ),
	Account.DisplayNameWithHierarchy,
	TransactionAccountingLine.Posting
ORDER BY
	TransactionType,
	AccountType,
	DisplayNameWithHierarchy,
	TransactionAccountingLine.Posting

Wrapping Up

I've mentioned in previous posts that I really enjoy working on these types of requests. Some are more challenging than others, and require additional research with regards to the tables that are needed and how they relate to one another.

For the queries shared in this post, the key is the relationships between the Transaction, TransactionAccountingLine, and Account tables. Once you have the relationships setup, the queries become rather simple to complete.

One more thing: The final query is now available via the SuiteQL Query Tool's "Remote Library." It's listed as "General Ledger Impact by Transaction Type."