Published on November 6, 2020.

A few months ago, I wrote about a NetSuite client that is outsourcing their Accounts Receivable to a debt collection firm. I shared a few SuiteQL queries that I'm using to provide the firm with customer and accounts receivable data via a custom API.

I recently enhanced that API so that we can provide the collection firm with access to accounts receivable aging information, in both summarized and detailed formats. As a follow-up to the original post, I thought I'd share the additional SuiteQL queries that I've developed.

Accounts Receivable Aging Details

The first query essentially replicates NetSuite's standard "A/R Aging Detail" report.

SELECT
	BUILTIN.DF( Transaction.Entity ) AS Customer,
	Transaction.Type AS TransactionType,
	Transaction.TranDate AS Date,
	Transaction.TranID AS DocumentNumber,	
	Transaction.OtherRefNum AS PONumber,
	Transaction.DueDate,
	( TRUNC( SYSDATE ) - Transaction.DueDate ) AS Age,
	( COALESCE( TransactionAccountingLine.AmountUnpaid, 0 ) - COALESCE( TransactionAccountingLine.PaymentAmountUnused, 0 ) ) AS OpenBalance
FROM
	Transaction
	INNER JOIN TransactionAccountingLine ON
		( TransactionAccountingLine.Transaction = Transaction.ID )
WHERE
	( Transaction.Entity = 999999 )
	AND ( Transaction.Posting = 'T' )
	AND ( Transaction.Voided = 'F' )
	AND ( 
		( TransactionAccountingLine.AmountUnpaid <> 0 )
		OR ( TransactionAccountingLine.PaymentAmountUnused <> 0 )
	)
ORDER BY
	Transaction.TranDate,
	Transaction.TranID

As you might expect, most of the data is being sourced from the Transaction table. Note that I'm filtering for transactions for a specific customer ( based on the Transaction.Entity column ), only including transactions that post to the general ledger, and only including transactions that have not been voided.

Also notice that I'm joining to the TransactionAccountingLine table, and I'm doing this to get access to two columns: AmountUnpaid and PaymentAmountUnused. For transactions that are invoices, AmountUnpaid is the open amount of the invoice. For credit memos, PaymentAmountUnused is the amount of the credit that hasn't been applied. I'm using those values to get accurate balances for the transactions, and also using them as filters. Only transactions that have an open balance are included.

The age of the transactions is based on their due dates. The "( TRUNC( SYSDATE ) - Transaction.DueDate )" formula takes the current date and subtracts the due date to determine the age.

And finally, notice that I'm using the BUILTIN.DF function to map the numeric Entity value (NetSuite's internal ID for the customer) to the entity name.

Accounts Receivable Aging Summary

I was able to modify the AR Aging Details query to create a summarized version.

SELECT
	BUILTIN.DF( Transaction.Entity ) AS Customer,
	SUM( 
		CASE 
			WHEN 
				( TRUNC( SYSDATE ) - Transaction.DueDate ) < 1 
			THEN 
				COALESCE( TransactionAccountingLine.AmountUnpaid, 0 ) - COALESCE( TransactionAccountingLine.PaymentAmountUnused, 0 )
			ELSE 
				0 
			END
	) AS Current,
	SUM( 
		CASE 
			WHEN 
				( TRUNC( SYSDATE ) - Transaction.DueDate ) BETWEEN 1 AND 30
			THEN 
				COALESCE( TransactionAccountingLine.AmountUnpaid, 0 ) - COALESCE( TransactionAccountingLine.PaymentAmountUnused, 0 )
			ELSE 
				0 
			END
	) AS Balance30,
	SUM( 
		CASE 
			WHEN 
				( TRUNC( SYSDATE ) - Transaction.DueDate ) BETWEEN 31 AND 60
			THEN 
				COALESCE( TransactionAccountingLine.AmountUnpaid, 0 ) - COALESCE( TransactionAccountingLine.PaymentAmountUnused, 0 )
			ELSE 
				0 
			END
	) AS Balance60,	
	SUM( 
		CASE 
			WHEN 
				( TRUNC( SYSDATE ) - Transaction.DueDate ) BETWEEN 61 AND 90
			THEN 
				COALESCE( TransactionAccountingLine.AmountUnpaid, 0 ) - COALESCE( TransactionAccountingLine.PaymentAmountUnused, 0 )
			ELSE 
				0 
			END
	) AS Balance90,		
	SUM( 
		CASE 
			WHEN 
				( TRUNC( SYSDATE ) - Transaction.DueDate ) > 90
			THEN 
				COALESCE( TransactionAccountingLine.AmountUnpaid, 0 ) - COALESCE( TransactionAccountingLine.PaymentAmountUnused, 0 )
			ELSE 
				0 
			END
	) AS Balance90Plus,		
	SUM ( 
		COALESCE( TransactionAccountingLine.AmountUnpaid, 0 ) - COALESCE( TransactionAccountingLine.PaymentAmountUnused, 0 ) 
	) AS Total
FROM
	Transaction
	INNER JOIN TransactionAccountingLine ON
		( TransactionAccountingLine.Transaction = Transaction.ID )
	INNER JOIN Customer ON
		( Customer.ID = Transaction.Entity )
WHERE
	( Transaction.Posting = 'T' )
	AND ( Transaction.Voided = 'F' )
	AND ( 
		( TransactionAccountingLine.AmountUnpaid <> 0 )
		OR ( TransactionAccountingLine.PaymentAmountUnused <> 0 )
	)
GROUP BY
	BUILTIN.DF( Transaction.Entity )
ORDER BY
	BUILTIN.DF( Transaction.Entity )

To summarize the data, I'm grouping on the Transaction.Entity column, and specifically on the customer's name.

I've added a join to the Customer table, which might seem odd, because I'm not using any of its columns. However, by doing an INNER JOIN to that table, only transactions that involve customers will be returned.

For the aging columns, I'm evaluating the age of the transactions (again, based on due dates), and summing the values that fall into each "aging bucket." Columns that include the total open balance for transactions that are current, 31 to 60 days old, 61 to 90 days old, and greater than 90 days old are included. You could easily modify the query to adjust the date ranges used for the aging columns, and add additional aging columns as well.

Wrapping Up

As always, I hope you find the queries that I've shared in this post to be helpful. If you have any questions, please feel free to reach out to me.