Published on October 29, 2020.

A few weeks ago, I wrote about a NetSuite integration project that I had been working on where my client needed to provide customer and accounts receivable data to a debt collection firm. I was recently asked by another client to provide similar data - but on the vendor / accounts payable side of things - as part of a project that involves developing a custom supplier portal.

In this post, I'll discuss the process of creating the SuiteQL queries that I developed for this project. I'll start with vendor data, add remittance addresses, explore vendor bills / credits, and wrap things up with a query that you can use to retrieve vendor payments.

The Vendor Table

SELECT
	Vendor.ID AS VendorID,
	Vendor.EntityTitle,
	Vendor.CompanyName,
	Vendor.AltName,
	Vendor.PrintOnCheckAs,
	Vendor.AccountNumber,
	Vendor.CreditLimit,
	Vendor.Balance,
	Vendor.UnbilledOrders,
	BUILTIN.DF( Vendor.Terms ) AS Terms,
	Vendor.Phone,
	Vendor.Fax,
	Vendor.Email,	
	Vendor.Comments
FROM
	Vendor
WHERE
	( Vendor.Balance > 0 )
ORDER BY
	Vendor.CompanyName

The query is straightforward, and involves only a single table. But here are a few things to note.

First, there's a "PrintOnCheckAs" column available. This is the company name that should be used when issuing checks to the vendor.

The "AccountNumber" column is the account number that the vendor uses to identify your company.

If the vendor has extended credit to your company, then the "CreditLimit" column reflects the credit amount.

The "Balance" column is the open total amount that is owed to the vendor.

The "UnbilledOrders" column is the total amount of the purchase orders that are open with the vendor. You might use that value, along with the "CreditLimit" value, to make purchasing decisions.

And finally, notice that I've used the "BUILTIN.DF" function to map the "Terms" value from a code to a more friendly and readable value. I've discussed the "BUILTIN.DF" function in previous blog posts, but if you're interested in learning more, check out "NetSuite: SuiteQL Built-In Functions Revisited."

Vendor Remittance Address

Adding the vendor's remittance address to the query is easy, and you can do so by joining to the EntityAddress table. Here's the revised query.

SELECT
	Vendor.ID AS VendorID,
	Vendor.EntityTitle,
	Vendor.CompanyName,
	Vendor.AltName,
	Vendor.PrintOnCheckAs,
	Vendor.AccountNumber,
	Vendor.CreditLimit,
	Vendor.Balance,
	Vendor.UnbilledOrders,
	BUILTIN.DF( Vendor.Terms ) AS Terms,
	Vendor.Phone,
	Vendor.Fax,
	Vendor.Email,	
	Vendor.Comments,
	Vendor.DefaultBillingAddress AS VendorAddressID,
	VendorAddress.Addressee AS VendorAddressee,
	VendorAddress.Addr1 As VendorAddress1,
	VendorAddress.Addr2 As VendorAddress2,
	VendorAddress.Addr3 As VendorAddress3,
	VendorAddress.City As VendorCity,
	VendorAddress.State As VendorState,
	VendorAddress.Zip As VendorZip,
	VendorAddress.Country As VendorCountry,
	VendorAddress.Attention As VendorAttention
FROM
	Vendor
	LEFT OUTER JOIN EntityAddress AS VendorAddress ON
		( VendorAddress.nkey = Vendor.DefaultBillingAddress )
WHERE
	( Vendor.Balance > 0 )
ORDER BY
	Vendor.CompanyName

Accounts Payable Transactions: Vendor Bills and Credits

As you might expect, vendor bills and credits are stored in the Transaction table. You can retrieve these accounts payable-related transactions, and join to the vendors and remittance addresses, using a query like this.

SELECT
	Transaction.ID AS TransactionID,
	Transaction.TransactionNumber,
	Transaction.TranID,
	Transaction.Type,
	Transaction.TranDisplayName,
	Transaction.TranDate,
	Transaction.CreatedDate,
	Transaction.DueDate,
	Transaction.DaysOpen,
	Transaction.ForeignTotal,
	Transaction.ForeignAmountUnpaid,
	Transaction.PaymentHold,
	BUILTIN.DF( Transaction.CreatedBy ) AS CreatedBy,
	BUILTIN.DF( Transaction.Status ) AS Status,
	BUILTIN.DF( Transaction.BillingStatus ) AS BillingStatus,
	BUILTIN.DF( Transaction.Terms ) AS Terms,
	Transaction.Memo,
	Transaction.ApprovalStatus,
	Vendor.ID AS VendorID,
	Vendor.AltName AS VendorName,	
	Transaction.BillingAddress AS VendorAddressID,
	VendorAddress.Addressee AS VendorAddressee,
	VendorAddress.Addr1 As VendorAddress1,
	VendorAddress.Addr2 As VendorAddress2,
	VendorAddress.Addr3 As VendorAddress3,
	VendorAddress.City As VendorCity,
	VendorAddress.State As VendorState,
	VendorAddress.Zip As VendorZip,
	VendorAddress.Country As VendorCountry,
	VendorAddress.Attention As VendorAttention	
FROM
	Transaction
	INNER JOIN Entity AS Vendor ON
		( Vendor.ID = Transaction.Entity )
	LEFT OUTER JOIN EntityAddress AS VendorAddress ON
		( VendorAddress.nkey = Transaction.BillingAddress )
WHERE
	( Transaction.Type IN ( 'VendBill', 'VendCred' ) )
	AND ( Transaction.Void = 'F' )	
	AND ( Transaction.Entity = 4321 )
	AND ( Transaction.ForeignAmountUnpaid <> 0 )
	-- AND ( Transaction.ID = 1234567 )
ORDER BY
	Transaction.TranDate

In this query, I'm retrieving open accounts payable transactions (both vendor bills and vendor credits) for a specific vendor (identified by the "Entity" value in the WHERE clause). Also notice that I've indicated that we want to exclude voided transactions.

I've commented out the "Transaction.ID" filter in the WHERE clause, and included it for reference. You can use that to retrieve a specific transaction.

I'm not going to review all of the columns that are being retrieved, but do want to point out that once again I'm making use of the "BUILTIN.DF" function for several of the columns. There are some extremely helpful columns in the Transaction table, among them DueDate, DaysOpen, and PaymentHold.

Vendor Payments

Let's wrap things up by exploring a query that can be used to retrieve vendor payments. Here's the query.

SELECT
	Transaction.ID AS TransactionID,
	Transaction.TransactionNumber,
	Transaction.TranID,
	Transaction.Type,
	Transaction.TranDisplayName,
	Transaction.TranDate,
	Transaction.CreatedDate,
	Transaction.DueDate,
	Transaction.DaysOpen,
	Transaction.ForeignTotal,
	Transaction.ForeignAmountUnpaid,
	Transaction.PaymentHold,
	BUILTIN.DF( Transaction.CreatedBy ) AS CreatedBy,
	BUILTIN.DF( Transaction.Status ) AS Status,
	BUILTIN.DF( Transaction.BillingStatus ) AS BillingStatus,
	BUILTIN.DF( Transaction.Terms ) AS Terms,
	Transaction.Memo,
	BUILTIN.DF( Transaction.PaymentMethod ) AS PaymentMethod,
	Vendor.ID AS VendorID,
	Vendor.AltName AS VendorName,	
	Transaction.BillingAddress AS VendorAddressID,
	VendorAddress.Addressee AS VendorAddressee,
	VendorAddress.Addr1 As VendorAddress1,
	VendorAddress.Addr2 As VendorAddress2,
	VendorAddress.Addr3 As VendorAddress3,
	VendorAddress.City As VendorCity,
	VendorAddress.State As VendorState,
	VendorAddress.Zip As VendorZip,
	VendorAddress.Country As VendorCountry,
	VendorAddress.Attention As VendorAttention	
FROM
	Transaction
	INNER JOIN Entity AS Vendor ON
		( Vendor.ID = Transaction.Entity )
	LEFT OUTER JOIN EntityAddress AS VendorAddress ON
		( VendorAddress.nkey = Transaction.BillingAddress )
WHERE
	( Transaction.Entity = 4321 )
	AND ( Transaction.Type = 'VendPymt' )
	AND ( Transaction.Void = 'F' )
	AND ( TranDate BETWEEN TO_DATE('2020-09-01', 'YYYY-MM-DD') AND TO_DATE('2020-09-30', 'YYYY-MM-DD') )
	-- AND ( Transaction.ID = 1234567 )
ORDER BY
	Transaction.TranDate DESC

Once again, I'm querying the Transaction table. However, in this case, I'm filtering on transaction type "VendPymt" to get the vendor payments.

As I did in the previous queries, I'm joining to the vendor and remittance address. This helps to give a more complete view of the payment. You might use this to generate a vendor payment ledger report.

In the example, I'm retrieving payments for a specific vendor, that have not been voided, and that occurred in a specific date range. I've commented out the filter criteria for the "Transaction.ID" column, but you could use that to retrieve a specific payment.

Wrapping Up

In this post, I've reviewed several queries that can be used to retrieve accounts payable-related data. As I mentioned earlier, I'm using these queries as part of a custom vendor portal that I'm developing. However, you could use these queries for other purposes, such as providing an API that vendors can use to get payment information.

I hope you find the queries to be helpful. If you have any questions, feel free to contact me.