Published on February 27, 2024.

I frequently get requests from my NetSuite clients for help with SuiteQL queries that involve sales tax. The challenge with these requests is that there are several tax compliance and reporting options available to businesses that are running on NetSuite, and my clients use several of them. There's NetSuite's own SuiteTax, Stripe's TaxJar, Vertex, and more.

But the tax solution that the majority of my clients use is Avalara AvaTax. So in this post I thought I'd share some of the AvaTax-related queries that I've developed for them and information that might help you develop your own queries.

AvaTax Tables

In my SuiteQL / AvaTax work, I've identified 19 AvaTax-related tables (custom records). Here they are.

CUSTOMRECORD_AVAADDVALFLAG
CUSTOMRECORD_AVAADDRESSVALIDATIONBATCH
CUSTOMRECORD_AVAADDVALIDATIONBATCHRECORD
CUSTOMRECORD_AVACERTEMAIL
CUSTOMRECORD_AVACONFIG
CUSTOMRECORD_AVACOORDINATES
CUSTOMRECORD_AVACUSTOMEREXEMPTMAPPING
CUSTOMRECORD_AVAENTITYUSECODES
CUSTOMRECORD_AVAENTITYUSEMAPPING
CUSTOMRECORD_AVAENTITYUSEMAPPING_NEW
CUSTOMRECORD_AVAITEMMAPPING
CUSTOMRECORD_AVARECALCULATEBATCH
CUSTOMRECORD_AVARECONCILEBATCH
CUSTOMRECORD_AVARECONCILEBATCHRECORDS
CUSTOMRECORD_AVASHIPPINGCODES
CUSTOMRECORD_AVATAXHEADERDETAILS
CUSTOMRECORD_AVATRANSACTIONLOGS
CUSTOMRECORD_AVAUSETAXBATCH
CUSTOMRECORD_AVAUSETAXHEADERDETAILS

The AvaTax-related tables in your instance might vary, depending on the AvaTax version that you have installed and any customizations that have been made.

AvaTax Configuration Information (CUSTOMRECORD_AVACONFIG)

Depending on what you want to do with AvaTax, you might find the CUSTOMRECORD_AVACONFIG to be of interest. In it you'll find detailed information about how AvaTax is configured, including the AvaTax account information, features that are enabled, and more.

For example, this query can be used to get information about the AvaTax account.

SELECT
	CUSTOMRECORD_AVACONFIG.CUSTRECORD_AVA_ADDRESSEE,
	CUSTOMRECORD_AVACONFIG.CUSTRECORD_AVA_ADDRESS1,
	CUSTOMRECORD_AVACONFIG.CUSTRECORD_AVA_ADDRESS2,
	CUSTOMRECORD_AVACONFIG.CUSTRECORD_AVA_CITY,
	CUSTOMRECORD_AVACONFIG.CUSTRECORD_AVA_STATE,
	CUSTOMRECORD_AVACONFIG.CUSTRECORD_AVA_ZIP,
	CUSTOMRECORD_AVACONFIG.CUSTRECORD_AVA_COUNTRY,
	CUSTOMRECORD_AVACONFIG.CUSTRECORD_AVA_ACCOUNTVALUE,
	CUSTOMRECORD_AVACONFIG.CUSTRECORD_AVA_LICENSEKEY
FROM
	CUSTOMRECORD_AVACONFIG

Here's a more thorough list of the columns in the CUSTOMRECORD_AVACONFIG table.

AvaTax Header Details (CUSTOMRECORD_AVATAXHEADERDETAILS)

The AvaTax table that I use most often is CUSTOMRECORD_AVATAXHEADERDETAILS, which is where detailed tax information is stored for each transaction.

Here's an example query that returns details for a specific transaction.

SELECT
	CUSTOMRECORD_AVATAXHEADERDETAILS.ABBREVIATION,
	CUSTOMRECORD_AVATAXHEADERDETAILS.CUSTRECORD_AVA_DOCUMENTID,
	CUSTOMRECORD_AVATAXHEADERDETAILS.CUSTRECORD_AVA_BASECURRENCY,
	CUSTOMRECORD_AVATAXHEADERDETAILS.CREATED,
	CUSTOMRECORD_AVATAXHEADERDETAILS.CUSTRECORD_AVA_DOCUMENTDATE,
	CUSTOMRECORD_AVATAXHEADERDETAILS.CUSTRECORD_AVA_DOCUMENTSTATUS,
	CUSTOMRECORD_AVATAXHEADERDETAILS.CUSTRECORD_AVA_DOCUMENTTYPE,
	CUSTOMRECORD_AVATAXHEADERDETAILS.CUSTRECORD_AVA_EXCHANGERATE,
	CUSTOMRECORD_AVATAXHEADERDETAILS.EXTERNALID,
	CUSTOMRECORD_AVATAXHEADERDETAILS.CUSTRECORD_AVA_FOREIGNCURR,
	CUSTOMRECORD_AVATAXHEADERDETAILS.CUSTRECORD_AVA_GSTTAX,
	CUSTOMRECORD_AVATAXHEADERDETAILS.ISINACTIVE,
	CUSTOMRECORD_AVATAXHEADERDETAILS.ID,
	CUSTOMRECORD_AVATAXHEADERDETAILS.LASTMODIFIED,
	CUSTOMRECORD_AVATAXHEADERDETAILS.LASTMODIFIEDBY,
	CUSTOMRECORD_AVATAXHEADERDETAILS.CUSTRECORD_AVA_MULTICURRENCY,
	CUSTOMRECORD_AVATAXHEADERDETAILS.NAME,
	CUSTOMRECORD_AVATAXHEADERDETAILS.CUSTRECORD_AVA_DOCUMENTINTERNALID,
	CUSTOMRECORD_AVATAXHEADERDETAILS.CUSTRECORD_AVA_DOCUMENTNO,
	CUSTOMRECORD_AVATAXHEADERDETAILS.CUSTRECORD_AVA_NETSUITEDOCTYPE,
	CUSTOMRECORD_AVATAXHEADERDETAILS.OWNER,
	CUSTOMRECORD_AVATAXHEADERDETAILS.CUSTRECORD_AVA_PSTTAX,
	CUSTOMRECORD_AVATAXHEADERDETAILS.RECORDID,
	CUSTOMRECORD_AVATAXHEADERDETAILS.CUSTRECORD_AVA_SCHEDULED,
	CUSTOMRECORD_AVATAXHEADERDETAILS.SCRIPTID,
	CUSTOMRECORD_AVATAXHEADERDETAILS.CUSTRECORD_AVA_SHIPCODE,
	CUSTOMRECORD_AVATAXHEADERDETAILS.CUSTRECORD_AVA_TAXCALCULATIONDATE,
	CUSTOMRECORD_AVATAXHEADERDETAILS.CUSTRECORD_AVA_TOTALAMOUNT,
	CUSTOMRECORD_AVATAXHEADERDETAILS.CUSTRECORD_AVA_TOTALDISCOUNT,
	CUSTOMRECORD_AVATAXHEADERDETAILS.CUSTRECORD_AVA_TOTALNONTAXABLE,
	CUSTOMRECORD_AVATAXHEADERDETAILS.CUSTRECORD_AVA_TOTALTAX,
	CUSTOMRECORD_AVATAXHEADERDETAILS.CUSTRECORD_AVA_TOTALTAXABLE
FROM
	CUSTOMRECORD_AVATAXHEADERDETAILS
WHERE 
	CUSTRECORD_AVA_DOCUMENTINTERNALID = 60809426	

Note that if you want to join the CUSTOMRECORD_AVATAXHEADERDETAILS table to the Transaction table, it relates via the CUSTRECORD_AVA_DOCUMENTINTERNALID column (i.e. CUSTOMRECORD_AVATAXHEADERDETAILS.CUSTRECORD_AVA_DOCUMENTINTERNALID = Transaction.ID).

AvaTax Transaction Logs (CUSTOMRECORD_AVATRANSACTIONLOGS)

Another table that I've found to be helpful - especially when researching tax-related issues for clients - is CUSTOMRECORD_AVATRANSACTIONLOGS. It serves as a log for the AvaTax API calls that have been made.

Here's a query that shows API calls made for a specified Transaction.

SELECT
	AVATRANLOG.ID,
	TO_CHAR ( AVATRANLOG.CREATED, 'DS TS' ) AS CREATED,
	CUSTRECORD_AVA_TITLE,
	CUSTRECORD_AVA_NOTE
FROM 
	CUSTOMRECORD_AVATRANSACTIONLOGS AS AVATRANLOG
WHERE 	
	( AVATRANLOG.CUSTRECORD_AVA_TRANSACTION = 60809426 )

The custrecord_ava_title values will either be "AvaTax Log - Client" or "AvaTax Log - Server."

The custrecord_ava_note is interesting in that it provides a transcript of the API call. Here's a sample value.

The Document has used AvaTax Services.
************************** REST Request Start ******************** 
REST Request Date & Time - Wed Jan 26 2024 08:01:39 GMT-0800 (PST)
AvaTax Document Type - SalesOrder
AvaTax Document Number - Wed Jan 26 2024 08:01:39
NetSuite Document Type - Cash Sale
NetSuite Document Date - 2/26/2024
*************************** REST Request End ******************** 
************************** REST Response Start ****************** 
REST Response - {\"id\":0,\"code\":\"Wed Jan 26 2024 08:01:39\",\"companyId\":99999,\"date\":\"2024-02-26\",\"paymentDate\":\"2024-02-26\",\"status\":\"Temporary\",\"type\":\"SalesOrder\",\"currencyCode\":\"USD\",\"customerVendorCode\":\"834868\",\"customerCode\":\"834868\",\"reconciled\":false,\"locationCode\":\"Main Fulfillment Center\",\"reportingLocationCode\":\"Main Fulfillment Center\",\"purchaseOrderNo\":\"\",\"taxOverrideType\":\"None\",\"taxOverrideAmount\":0,\"totalAmount\":139.8,\"totalExempt\":0,\"totalDiscount\":0,\"totalTax\":9.79,\"totalTaxable\":139.8,\"totalTaxCalculated\":9.79,\"adjustmentReason\":\"NotAdjusted\",\"locked\":false,\"version\":1,\"exchangeRateEffectiveDate\":\"2024-02-26\",\"exchangeRate\":1,\"isSellerImporterOfRecord\":false,\"modifiedDate\":\"2024-02-26T16:01:35.1617383Z\",\"modifiedUserId\":223335,\"taxDate\":\"2024-02-26T00:00:00\"}
************************** REST Response End ********************

The REST response that was logged can be decoded and looks like this.

{
  "id": 0,
  "code": "Wed Jan 26 2024 08:01:39",
  "companyId": 99999,
  "date": "2024-02-26",
  "paymentDate": "2024-02-26",
  "status": "Temporary",
  "type": "SalesOrder",
  "currencyCode": "USD",
  "customerVendorCode": "834868",
  "customerCode": "834868",
  "reconciled": false,
  "locationCode": "Main Fulfillment Center",
  "reportingLocationCode": "Main Fulfillment Center",
  "purchaseOrderNo": "",
  "taxOverrideType": "None",
  "taxOverrideAmount": 0,
  "totalAmount": 139.8,
  "totalExempt": 0,
  "totalDiscount": 0,
  "totalTax": 9.79,
  "totalTaxable": 139.8,
  "totalTaxCalculated": 9.79,
  "adjustmentReason": "NotAdjusted",
  "locked": false,
  "version": 1,
  "exchangeRateEffectiveDate": "2024-02-26",
  "exchangeRate": 1,
  "isSellerImporterOfRecord": false,
  "modifiedDate": "2024-02-26T16:01:35.1617383Z",
  "modifiedUserId": 223335,
  "taxDate": "2024-02-26T00:00:00"
}

AvaTax Custom Fields

I also frequently query AvaTax's custom fields, including those on the Transaction and TransactionLine tables.

For example, here are some of the AvaTax custom fields available via the Transaction table.

Here are some AvaTax custom fields available via the TransactionLine table.

Wrapping Up

As I mentioned earlier, there are a number of AvaTax-related tables that are available, and in this post I've only covered a few of them. Between those tables and the AvaTax-related custom fields that you'll find in standard NetSuite tables, I believe you'll have everything you need in order to write queries that expose the data you need.