NetSuite: SuiteQL and Bank Transfers

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