Published on November 10, 2020.

Last week I shared a few SuiteQL queries that can be used to retrieve Accounts Receivable Aging data from a NetSuite instance. As a follow-up, I thought I'd share queries that can be used to retrieve Accounts Payable Aging data.

As you'll see, the Accounts Payable Aging queries are very similar to their Accounts Receivable equivalents, so much so that only a few minor changes to the Accounts Receivable queries are needed.

Accounts Payable Aging Details

Let's start with the detailed version of the query, which returns all open Accounts Payable-related transactions for a specific vendor.

SELECT
	BUILTIN.DF( Transaction.Entity ) AS Vendor,
	Transaction.Type AS TransactionType,
	Transaction.TranDate AS Date,
	Transaction.TranID AS DocumentNumber,	
	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

If you compare that query to the "Accounts Receivable Aging Details" query, you'll see that I've made only two changes. I've changed the alias for the transaction's Entity column from "Customer" to "Vendor." Also, I've dropped the "OtherRefNum" column from the result, because it isn't applicable to Accounts Payable transactions.

Accounts Payable Aging Summary

Creating an Accounts Payable version of the "Accounts Receivable Aging Summary" query is just as easy.

SELECT
	BUILTIN.DF( Transaction.Entity ) AS Vendor,
	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 Vendor ON
		( Vendor.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 )

If you compare that query to the "Accounts Receivable Aging Summary" query, you'll see that, once again, I've made only two changes. As before, I've changed the alias for the transaction's Entity from Customer to Vendor. The other change - and it's minor but significant - is that instead of joining to the Customer table, I'm now joining to the Vendor table. By doing an INNER JOIN to the Vendor table, only transactions involving vendors will be returned. In other words, I'm requesting only Accounts Payable transactions.

Wrapping Up

I encourage you to take a look at the NetSuite: SuiteQL and Accounts Receivable Aging blog post and compare the queries in it to those in this post. I think you'll agree that it's amazing how easy it is to repurpose those queries to provide Accounts Payable Aging data.

It's worth mentioning that the reason this it is so easy to repurpose the queries is that they are largely based on the Transaction and TransactionAccountingLine tables. As I've written about in previous posts, those tables are used by NetSuite to store data on a wide range of transaction types, from customer invoices to vendor bills to item fulfillments. As a result, getting familiar with those tables is time well spent.

And finally, I want to mention a previous Accounts Payable-related post that you might find helpful. In "NetSuite: Using SuiteQL to Retrieve Vendor and Accounts Payable Data" I shared queries for retrieving vendor accounts payable balances, remittance addresses, vendor bills and credits, and more.

If you have any questions about the queries shared in this post, please feel free to contact me.