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.
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' ) )
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 )
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
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.