NetSuite: SuiteQL and Bank Transfers

Published on July 31, 2023.

I've written previously about NetSuite clients who reach out to me for SuiteQL queries that are designed to help them prepare for audits, and earlier this month, that happened again. One of my clients requested queries that could be used to pull information on Bank Transfers. In this post, I'll share a few of the queries that I developed for them.

Bank Transfers by Date Range

The first query that my client requested was a list of all bank transfers that had posted within a certain date range. Bank transfers are stored in the Transaction table and their transaction type is "Transfer." Here's the query that I developed for them.

SELECT
	Transaction.TranDate,
	Transaction.ID AS Transaction,
	Transaction.TranID,	
	AccountingPeriod.PeriodName AS PostingPeriod,
	Transaction.Memo,
	ABS( Transaction.ForeignTotal ) AS Amount,
	Currency.Name AS Currency
FROM 
	Transaction 
	INNER JOIN AccountingPeriod ON
		( AccountingPeriod.ID = Transaction.PostingPeriod )
	INNER JOIN Currency ON
		( Currency.ID = Transaction.Currency )
WHERE 
	( Transaction.Type = 'Transfer' )
	AND ( AccountingPeriod.StartDate BETWEEN TO_DATE( '2023-01-01', 'YYYY-MM-DD' ) AND TO_DATE( '2023-12-31', 'YYYY-MM-DD' ) )

Bank Transfer Details

The second query that my client requested was for the details of a specific bank transfer transaction, and specifically the GL accounts that were involved. To get that deeper level of data, I joined to the TransactionAccountingLine table. Here's that query.

SELECT
	Transaction.ID AS Transaction,
	Transaction.TranID,
	Transaction.TranDate,
	AccountingPeriod.PeriodName AS PostingPeriod,
	Transaction.Memo,
	Account.DisplayNameWithHierarchy,
	Transaction.ForeignTotal,
	TransactionAccountingLine.Debit,
	TransactionAccountingLine.Credit,
	Currency.Name AS Currency
FROM 
	Transaction 
	INNER JOIN AccountingPeriod ON
		( AccountingPeriod.ID = Transaction.PostingPeriod )
	INNER JOIN TransactionAccountingLine ON
		( TransactionAccountingLine.Transaction = Transaction.ID )
	INNER JOIN Account ON
		( Account.ID = TransactionAccountingLine.Account )
	INNER JOIN Currency ON
		( Currency.ID = Transaction.Currency )
WHERE 
	( Transaction.Type = 'Transfer' )
	AND ( Transaction.ID = 46129882 )

Bank Transfer Details by Date Range

And finally, my client requested an additional query that was essentially a combination of the first two queries. They wanted a query to return the details of all bank transfers that posted within a certain date range, sorted by the transaction ID, and sub-sorted based on the sequence of the transaction lines. To get the line sequence number ( TransactionLine.LineSequenceNumber ), I joined to TransactionLine table. Here's that final query.

SELECT
	Transaction.ID AS Transaction,
	Transaction.TranID,
	Transaction.TranDate,
	AccountingPeriod.PeriodName AS PostingPeriod,
	Transaction.Memo,
	Account.DisplayNameWithHierarchy,
	Transaction.ForeignTotal,
	TransactionAccountingLine.Debit,
	TransactionAccountingLine.Credit,
	Currency.Name AS Currency
FROM 
	Transaction 
	INNER JOIN AccountingPeriod ON
		( AccountingPeriod.ID = Transaction.PostingPeriod )
	INNER JOIN TransactionAccountingLine ON
		( TransactionAccountingLine.Transaction = Transaction.ID )
	INNER JOIN Account ON
		( Account.ID = TransactionAccountingLine.Account )
	INNER JOIN Currency ON
		( Currency.ID = Transaction.Currency )
	INNER JOIN TransactionLine ON
		( TransactionLine.Transaction = TransactionAccountingLine.Transaction )
		AND ( TransactionLine.ID = TransactionAccountingLine.TransactionLine )
WHERE 
	( Transaction.Type = 'Transfer' )
	AND ( AccountingPeriod.StartDate BETWEEN TO_DATE( '2023-01-01', 'YYYY-MM-DD' ) AND TO_DATE( '2023-12-31', 'YYYY-MM-DD' ) )
ORDER BY
	Transaction.ID,
	TransactionLine.LineSequenceNumber
About Me

Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on NetSuite, including mobile apps, Web portals, Web APIs, and more.

I'm the developer of several popular NetSuite open source solutions, including the SuiteQL Query Tool, SuiteAPI, and more.

I founded SuiteStep, a NetSuite development studio, to provide custom software and AI solutions - and continue pushing the boundaries of what's possible on the NetSuite platform.

Copyright © 2025 Tim Dietrich.