Skip to Content

Avalara Connection Details

Introduction

Connector Version

This documentation is based on version 23.0.8936 of the connector.

Get Started

Avalara Version Support

The connector leverages the Avalara Avatax API to enable bidirectional access to Avalara.

Establish a Connection

Connect to Avalara

By default, the connector connects to a production environment. Set UseSandbox to true to configure the connector to connect to a Avalara sandbox environment.

Authenticate to Avalara

There are two authentication methods available for connecting to the Avalara data source: Basic and LicenseKey.

Login Credentials

To use login credentials for authentication, set the following:

  • AuthScheme: Set this to Basic.
  • User: Set this to your Avalara username.
  • Password: Set this to your Avalara password.
Account Number and License Key

Alternatively, you can authenticate using your account number and license key. Connect to the data source using the following:

  • AuthScheme: Set this to LicenseKey.
  • AccountId: Set this to your Avalara Account Id. The Account ID is listed in the upper right-hand corner of the Avalara admin console.
  • LicenseKey: Set this to your Avalara license key. You can generate a license key by logging into Avalara as an account adminstrator and navigating to Settings > Reset License Key.

Fine-Tuning Data Access

Fine Tuning Data Access

You can use the following properties to gain more control over the data returned from Avalara:

  • CompanyCode: Only transaction information tied to this company code will be returned.
  • IncludeAdjustedTransactions: Set this to true to include adjusted transactions in the transaction listing.

Important Notes

Configuration Files and Their Paths

  • All references to adding configuration files and their paths refer to files and locations on the Jitterbit agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Advanced Features

This section details a selection of advanced features of the Avalara connector.

User Defined Views

The connector allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.

SSL Configuration

Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Proxy

To configure the connector using Private Agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.

Query Processing

The connector offloads as much of the SELECT statement processing as possible to Avalara and then processes the rest of the query in memory (client-side).

See Query Processing for more information.

User Defined Views

The Avalara connector allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:

  • Create a JSON-formatted configuration file defining the views you want.
  • DDL statements.

Define Views Using a Configuration File

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This User Defined View configuration file is formatted as follows:

  • Each root element defines the name of a view.
  • Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM Transactions WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"

Define Views Using DDL Statements

The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.

Create a View

To create a new view using DDL statements, provide the view name and query as follows:

CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;

If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews connection property.

Alter a View

To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:

ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';

The view is then updated in the JSON configuration file.

Drop a View

To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.

DROP LOCAL VIEW [MyViewName]

This removes the view from the JSON configuration file. It can no longer be queried.

Schema for User Defined Views

User Defined Views are exposed in the UserViews schema by default. This is done to avoid the view's name clashing with an actual entity in the data model. You can change the name of the schema used for UserViews by setting the UserViewsSchemaName property.

Work with User Defined Views

For example, a SQL statement with a User Defined View called UserViews.RCustomers only lists customers in Raleigh:

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';

Resulting in the effective query to the source:

SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';

That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

SSL Configuration

Customize the SSL Configuration

By default, the connector attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Data Model

Overview

This section shows the available API objects and provides more information on executing SQL to Avalara APIs.

Key Features

  • The connector models Avalara entities as relational views, allowing you to write SQL to query Avalara data.
  • Stored procedures allow you to execute operations to Avalara.
  • Live connectivity to these objects means any changes to your Avalara account are immediately reflected when using the connector.

Tables

Tables describes the available tables. Tables are statically defined to model Accounts, Transactions, Users, and more.

Views

Views describes the available views. Views are statically defined to model Reports, Subscriptions, and more.

Stored Procedures

Stored Procedures are function-like interfaces to Avalara. Stored procedures allow you to execute operations to Avalara, including downloading documents and moving envelopes.

Tables

The connector models the data in Avalara as a list of tables in a relational database that can be queried using standard SQL statements.

Avalara Connector Tables

Name Description
Accounts Accounts table for AvalaraAvatax data provider.
Batches Batches table for AvalaraAvatax data provider.
Companies Companies table for AvalaraAvatax data provider.
Contacts Contacts table for AvalaraAvatax data provider.
Customers Customers table for AvalaraAvatax data provider.
DataSources DataSources table for AvalaraAvatax data provider.
DistanceThresholds DistanceThresholds table for AvalaraAvatax data provider.
FilingCalendars FilingCalendars table for AvalaraAvatax data provider.
Items Items table for AvalaraAvatax data provider.
Locations Locations table for AvalaraAvatax data provider.
MultiDocument MultiDocument table for AvalaraAvatax data provider.
Nexus Nexus table for AvalaraAvatax data provider.
Notices Notices table for AvalaraAvatax data provider.
Notifications Notifications table for AvalaraAvatax data provider.
Settings Settings table for AvalaraAvatax data provider.
TaxCodes TaxCodes table for AvalaraAvatax data provider.
TaxRules TaxRules table for AvalaraAvatax data provider.
TransactionLines TransactionLines view for AvalaraAvatax data provider.
Transactions Transactions table for AvalaraAvatax data provider.
Upcs Upcs table for AvalaraAvatax data provider.
Users Users table for AvalaraAvatax data provider.

Accounts

Accounts table for AvalaraAvatax data provider.

Select

The connector will use the Avalara API to process WHERE clause conditions built with the following columns and operators. Most of the columns support server side filtering with the following operators: =,>,<,<=,>=,IN,NOT,IN,AND,OR,LIKE. The rest of the filter is executed client side within the connector. For example, the following query is processed server side:

SELECT * FROM Accounts WHERE accountStatusId = 'Active' AND accountTypeId = 'Regular1'
Insert

To add an Account.

INSERT INTO Accounts (id, name, effectiveDate, accountStatusId, accountTypeId) VALUES ('12345', 'Test Account', '2019-06-24T00:00:00+00:00', 'Test', 'Regular')
Update

Avalara allows updates Accounts columns.

UPDATE Accounts SET crmid = '0010b000Ardit' WHERE ID = 2000137504

In order to update AccountsSubscriptions #TEMP table should be used.

INSERT INTO AccountsSubscriptions#TEMP (subscriptionDescription, id) VALUES ('Avacert2', '567961')
UPDATE Accounts SET linkedSubscriptions = 'AccountsSubscriptions#TEMP', crmid = '0010b000Ardit' WHERE ID = 2000137504
Delete

Accounts can be deleted by providing the ID of the address and issuing a DELETE statement.

DELETE FROM Accounts WHERE ID = 100
Columns
Name Type ReadOnly Description
Id [KEY] Int False The unique ID number assigned to this account.
ModifiedUserId Int False The user ID of the user who last modified this record.
CreatedUserId Int False The User ID of the user who created this record.
AccountTypeId String False The type of this account.
AccountStatusId String False The current status of this account.
CrmId String False For system registrar use only.
LinkedSubscriptions String False A list of subscriptions granted to this account.
Name String False The name of this account.
LinkedUsers String False A list of all the users belonging to this account.
ModifiedDate Datetime False The date/time when this record was last modified.
EndDate Datetime False If this account has been closed, this is the last date the account was open.
EffectiveDate Datetime False The earliest date on which this account may be used.
CreatedDate Datetime False The date when this record was created.
PageKey String False

Batches

Batches table for AvalaraAvatax data provider.

Insert

Create batch objects attached to a company. Each batch object must have one file object. When a batch is created, it is added to the AvaTax Batch Queue and will be processed as quickly as possible in the order it was received. As a result of this operation you will get the status of a batch. First insert the file, referencing the local path of the file on your machine, using #TEMP table:

INSERT INTO File#TEMP (Name, FileLocalPath, ContentType) VALUES ('samplebatch.csv', 'C:\Users\User\Desktop\BatchFile.csv', 'text/csv')

Then insert in the main Batches table. The following fielda are required:

INSERT INTO Batches (CompanyId, Name, LinkedFiles, Type) VALUES ('247015', 'TestBatch2', 'File#TEMP', 'TransactionImport')
Update

UPDATE operation is not supported for Batches table.

Delete

If you attempt to delete a batch that is being processed, you will receive an error message. Deleting a batch does not delete any transactions that were created by importing the batch. Deleting a batch requires one of the following user roles: CSPAdmin, CSPTester, SSTAdmin, SystemAdmin, SystemOperator, TechnicalSupportAdmin; Companyid and ID should be specified in the WHERE clause.

DELETE FROM Batches WHERE ID = 9030440 AND CompanyId = 247015
Columns
Name Type ReadOnly Description
Id [KEY] Int False The unique ID number of this batch.
CompanyId [KEY] Int False The Company ID number of the company that owns this batch.
BatchAgent String False The agent used to create this batch.
CurrentRecord Int False The current record being processed
CompletedDate Datetime False The date/time when this batch was completely processed.
LinkedFiles String False The list of files contained in this batch.
Name String False The user-friendly readable name for this batch.
ModifiedUserId Int False The user ID of the user who last modified this record.
CreatedUserId Int False The User ID of the user who created this record.
Status String False This batch's current processing status.
RecordCount Int False The number of records in this batch; determined by the server.
ModifiedDate Datetime False The date/time when this record was last modified.
AccountId Int False The Account ID number of the account that owns this batch.
Type String False The type of this batch.Possible values are:ItemImport, CompanyLocationImport, ExemptCertImport, TaxRuleImport, TransactionImport, UPCBulkImport.
StartedDate Datetime False The date/time when this batch started processing.
CreatedDate Datetime False The date when this record was created.
Options String False Any optional flags provided for this batch.
PageKey String False

Companies

Companies table for AvalaraAvatax data provider.

Insert

To insert a new company to an existing account you need to specify AccountId, CompanyCode, Name and DefaultCountry fields.

INSERT INTO Companies (AccountId, CompanyCode, Name, defaultCountry) VALUES ('2000137504', 'SAB', 'SAB&Co', 'US')
Update

Avalara allows UPDATEs on Companies columns, expect for aggregate columns. Aggregate columns can not be updated.

UPDATE Companies SET Name = 'UpdatedName' WHERE ID = 283040
Delete

A company can be deleted by providing the ID of the company and issuing a DELETE statement.

DELETE FROM Companies WHERE ID = 283040
Columns
Name Type ReadOnly Description
Id [KEY] Int False The unique ID number of this company.
DefaultLocationId Int False If set, this is the unique ID number of the default location for this company.
LinkedNexus String False Optional: A list of nexus defined for this company. To fetch this list, add the query string `?$include=Nexus` to your URL.
NonReportingChildCompanies String False
TaxDependencyLevelId String False Used to apply tax detail dependency at a jurisdiction level.
DefaultCountry String False The two character ISO-3166 country code of the default country for this company.
LinkedTaxRules String False Optional: A list of tax rules defined for this company.
Name String False The name of this company, as shown to customers.
CreatedDate Datetime False The date when this record was created.
MossCountry String False The country code of the mini-one-stop-shop used for Value Added Tax (VAT) processing.
LinkedContacts String False Optional: A list of contacts defined for this company.
CompanyCode String False A unique code that references this company within your account.
WarningsEnabled Bool False Set this value to true to receive warnings in API calls via SOAP.
LinkedLocations String False Optional: A list of locations defined for this company.
LinkedItems String False Optional: A list of items defined for this company.
CreatedUserId Int False The User ID of the user who created this record.
AccountId Int False The unique ID number of the account this company belongs to.
ModifiedDate Datetime False The date/time when this record was last modified.
RoundingLevelId String False Indicates whether this company prefers to round amounts at the document level or line level.
BusinessIdentificationNo String False Business Identification No.
LinkedUpcs String False Optional: A list of UPCs defined for this company.
InProgress Bool False Set this value to true to indicate that you are still working to finish configuring this company. While this value is true, no tax reporting will occur and the company will not be usable for transactions.
IsReportingEntity Bool False Set this flag to true if this company must file its own tax returns. For users who have Returns enabled, this flag turns on monthly Worksheet generation for the company.
LinkedTaxCodes String False Optional: A list of tax codes defined for this company.
ParentCompanyId Int False If this company is fully owned by another company, this is the unique identity of the parent company.
BaseCurrencyCode String False This is the three character ISO-4217 currency code of the default currency used by this company.
IsTest Bool False Set this flag to true to indicate that this company is a test company. If you have Returns enabled, Test companies will not file tax returns and can be used for validation purposes.
IsActive Bool False This flag indicates whether tax activity can occur for this company. Set this flag to true to permit the company to process transactions.
SstEffectiveDate Date False If this company participates in Streamlined Sales Tax, this is the date when the company joined the SST program.
MossId String False The unique identifier of the mini-one-stop-shop used for Value Added Tax (VAT) processing.
ModifiedUserId Int False The user ID of the user who last modified this record.
LinkedSettings String False Optional: A list of settings defined for this company.
SstPid String False If this company files Streamlined Sales Tax, this is the PID of this company as defined by the Streamlined Sales Tax governing board.
TaxpayerIdNumber String False For United States companies, this field contains your Taxpayer Identification Number. This is a nine digit number that is usually called an EIN for an Employer Identification Number if this company is a corporation, or SSN for a Social Security Number if this company is a person. This value is required if the address provided is inside the US and if you subscribed to the Avalara Managed Returns or SST Certified Service Provider service. Otherwise it is optional.
IsDefault Bool False This flag is true if this company is the default company for this account. Only one company may be set as the default.
HasProfile Bool False Set this flag to true to give this company its own unique tax profile. If this flag is true, this company will have its own Nexus, TaxRule, TaxCode, and Item definitions. If this flag is false, this company will inherit all profile values from its parent.

Contacts

Contacts table for AvalaraAvatax data provider.

Select

The connector will use the Avalara API to process WHERE clause conditions built with the following columns and operators. Most of the columns support server side filtering with the following operators: =,>,<,<=,>=,IN,NOT,IN,AND,OR,LIKE. The rest of the filter is executed client side within the connector. For example, the following query is processed server side:

SELECT * FROM Contacts WHERE companyid = 247015
SELECT * FROM Contacts WHERE companyid = 247015 AND region = 'NY'
Insert

To add a Contact, ContactCode is a required field.

INSERT INTO Contacts (companyId, contactCode, firstName, lastName, title, line1, city, region, postalCode, country, email, phone) VALUES ('247015', 'TestContactc7356131ca2442', " +
"'Bob', 'McExample', 'Owner', '2000 Main Street', 'Irvine', 'CA', '92614', 'US', 'bob@example.org', '714 555-1212')
Update

Avalara allows updates Contacts columns. To update a Contact, ID and CompanyId should be specified in the WHERE clause of the Update statement.

UPDATE Contacts SET firstName = 'tidra' WHERE CompanyId = 247015 AND ID = 175596
Delete

Contacts can be deleted by providing the Companyid and ID of the Contact and issuing a DELETE statement.

DELETE FROM Contacts WHERE ID = 100
Columns
Name Type ReadOnly Description
Id [KEY] Int False The unique ID number of this contact.
FirstName String False The first or given name of this contact.
CreatedUserId Int False The User ID of the user who created this record.
Mobile String False The mobile phone number for this contact.
Region String False Name or ISO 3166 code identifying the region within the country.
ModifiedDate Datetime False The date/time when this record was last modified.
Phone String False The main phone number for this contact.
Line3 String False The third line of the postal mailing address of this contact.
PostalCode String False The postal code or zip code of the postal mailing address of this contact.
Line2 String False The second line of the postal mailing address of this contact.
Line1 String False The first line of the postal mailing address of this contact.
LastName String False The last or family name of this contact.
ModifiedUserId Int False The user ID of the user who last modified this record.
CompanyId [KEY] Int False The unique ID number of the company to which this contact belongs.
Email String False The email address of this contact.
Country String False Name or ISO 3166 code identifying the country.
City String False The city of the postal mailing address of this contact.
MiddleName String False The middle name of this contact.
Fax String False The facsimile phone number for this contact.
CreatedDate Datetime False The date when this record was created.
Title String False Professional title of this contact.
ContactCode String False A unique code for this contact.
PageKey String False

Customers

Customers table for AvalaraAvatax data provider.

Select

To retrieve Customer records from Customers table, at least CompanyId or CompanyId and CustomerCode should be specified in the WHERE clause. The connector will use the Avalara API to process WHERE clause conditions built with the following columns and operators. CompanyId and CustomerCode fields support server side filtering with the = operator. The rest of the filter is executed client side within the connector. For example, the following query is processed server side:

SELECT * FROM Customers WHERE CompanyId = '247015'
SELECT * FROM Contacts WHERE CompanyId = 247015 AND CustomerCode = 'CC001'
Columns
Name Type ReadOnly Description
Id Int False Unique ID number of this customer.
CompanyId [KEY] Int False The unique ID number of the AvaTax company that recorded this customer.
CustomerCode [KEY] String False The unique code identifying this customer. Must be unique within your company.
AlternateId String False A customer-configurable alternate ID number for this customer. You may set this value to match any other system that would like to reference this customer record.
IsShip Bool False True if this customer record is specifically used for ship-to purposes.
LinkedShipTos String False A list of ship-to customer records that are connected to this bill-to customer.
Region String False ISO 3166 code identifying the region within the country.
IsBill Bool False True if this customer record is specifically used for bill-to purposes.
EmailAddress String False The main email address for this customer.
LinkedCustomFields String False A list of custom fields defined on this customer.
TaxpayerIdNumber String False For customers in the United States, this field is the federal taxpayer ID number. For businesses, this is a Federal Employer Identification Number. For individuals, this will be a Social Security Number.
ModifiedDate Datetime False The date/time when this record was last modified.
LinkedCertificates String False A list of exemption certficates that apply to this customer.
FaxNumber String False The fax phone number for this customer, if any.
AttnName String False Indicates the 'Attn' component of the address for this customer, if this customer requires mailings to be shipped to the attention of a specific person or department name.
PhoneNumber String False The main phone number for this customer.
PostalCode String False Postal Code / Zip Code component of the address of this customer.
Line2 String False Second line of the street address of this customer.
Line1 String False First line of the street address of this customer.
LastTransaction Datetime False Date when this customer last executed a transaction.
Country String False Name or ISO 3166 code identifying the country.
City String False City component of the street address of this customer.
LinkedExposureZones String False A list of exposure zones where you do business with this customer.
ContactName String False The name of the main contact person for this customer.
CreatedDate Datetime False The date when this record was created.
Name String False A friendly name identifying this customer.

DataSources

DataSources table for AvalaraAvatax data provider.

Insert

To create a data source at least CompanyId and Source should be specified.

INSERT INTO DataSources (CompanyId, Source) VALUES (247015, 'Amazon v2 Extractor')
Update

To update a data source, you need to specify ID and CompanyId in WHERE clause.

UPDATE DataSources SET isEnabled = true WHERE ID = 297 AND CompanyId = 247015
Delete

To delete a user, you need to specify ID and AccountId in WHERE clause.

DELETE FROM DataSources WHERE ID = 297 AND CompanyId = 247015
Columns
Name Type ReadOnly Description
Id [KEY] Int False The ID of the datasource.
CompanyId [KEY] Int False The ID of the company to which the datasource belongs to.
Source String False The extractor/connector id.
ModifiedUserId Int False The user ID of the user who last modified this record.
CreatedUserId Int False The User ID of the user who created this record.
ModifiedDate Datetime False The date/time when this record was last modified.
IsAuthorized Bool False True if this data source is authorized.
LastSyncedDate Datetime False The date when the information was last synched.
IsSynced Bool False If all the information has been transferred from the extractor to the database.
DeletedDate Datetime False The date when this record was deleted.
IsEnabled Bool False The connection using the connectionMyUserd is enabled. The customer is responsible to enable or disable.
Instance String False The unique ID number of this connection.
CreatedDate Datetime False The date when this record was created.
Recalculate Bool False Specifies whether transactions created by this data source needs to re-calculate tax or not
PageKey String False

DistanceThresholds

DistanceThresholds table for AvalaraAvatax data provider.

A company-distance-threshold model indicates the distance between a company and the taxing borders of various countries. Distance thresholds are necessary to correctly calculate some value-added taxes.

Insert

To create a distance-threshold object you need one of the following user roles: AccountAdmin, CompanyAdmin, CSPAdmin, CSPTester, SiteAdmin, SSTAdmin, SystemAdmin, TechnicalSupportAdmin. At least the following fields should be specified:

INSERT INTO DistanceThresholds (companyId, originCountry, destinationCountry, type) VALUES (283040, 'FR', 'BR', 'Purchase')
Update

To update a distance-threshold, you need to specify ID and CompanyId in WHERE clause.

UPDATE DistanceThresholds SET EffDate = '2019/11/08' WHERE ID = 11785 AND CompanyId = 283040
Delete

To delete a distance-threshold, you need to specify ID and AccountId in WHERE clause.

DELETE FROM DistanceThresholds WHERE ID = 11785 AND CompanyId = 283040
Columns
Name Type ReadOnly Description
Id [KEY] Int False A unique ID number representing this distance threshold object.
CompanyId [KEY] Int False The ID number of the company that defined this distance threshold.
OriginCountry String False The origin country for this threshold.
DestinationCountry String False The destination country for this threshold.
Type String False Indicates the distance threshold type. This value can be either `Sale` or `Purchase`.
EffDate Datetime False For distance threshold values that change over time, this is the earliest date for which this distance threshold is valid. If null, this distance threshold is valid for all dates earlier than the `endDate` field.
EndDate Datetime False For distance threshold values that change over time, this is the latest date for which this distance threshold is valid. If null, this distance threshold is valid for all dates later than the `effDate` field.
ThresholdExceeded Bool False True if your tax professional has determined that the value-added tax distance threshold is exceeded for this pair of countries. If you set this value to `false`, your value added taxes will be calculated using the origin country. Otherwise, value added taxes will be calculated on the destination country.
PageKey String False

FilingCalendars

FilingCalendars table for AvalaraAvatax data provider.

Select

To retrieve records from FilingCalendar table, at least CompanyId should be specified in the WHERE clause. The connector will use the Avalara API to process WHERE clause conditions built with the following columns and operators. Most of the columns support server side filtering with the following operators: =,>,<,<=,>=,IN,NOT,IN,AND,OR,LIKE. The rest of the filter is executed client side within the connector. For example, the following query is processed server side:

SELECT * FROM FilingCalendars WHERE CompanyId = '247015' AND Success = False
Columns
Name Type ReadOnly Description
CompanyId [KEY] Int False CompanyId of the request
ActivePoa Bool False Notes if there is an actice POA
ExpirationDate Datetime False End Date of POA
LinkedAvailablePOA String False POA download
Bytes String False Bytes of the file
Filename String False Original filename
Success Bool False True if this download succeeded
ContentType String False Mime content type
Region String False Region POA is for
EffectiveDate Datetime False Effective Date of the POA
Country String False Country POA is for

Items

Items table for AvalaraAvatax data provider.

Select

The connector will use the Avalara API to process WHERE clause conditions built with the following columns and operators. Most of the columns support server side filtering with the following operators: =,>,<,<=,>=,IN,NOT,IN,AND,OR,LIKE. The rest of the filter is executed client side within the connector. For example, the following query is processed server side:

SELECT * FROM Items WHERE CompanyId = 247015 AND Description LIKE '%MUG'
SELECT * FROM Items WHERE CompanyId = 247015 AND ModifiedUserId IN (289767, 1354)
Insert

To add an Item.

INSERT INTO Items (companyId, itemCode, taxCode, description, itemGroup) VALUES ('247015', 'CERMUG1', 'P0000000', 'Ceramic Mug', 'Mugs')")

In order to add an item with ItemsClassifications #TEMP table should be used

INSERT INTO Itemsclassifications#TEMP (productCode, systemCode) VALUES ('P0000000', 'TARIC')")
INSERT INTO Items (companyId, itemCode, taxCode, description, itemGroup, Linkedclassifications) VALUES ('247015', 'CERMUG', 'P0000000', 'Ceramic Mug', 'Mugs', 'Itemsclassifications#TEMP')")
INSERT INTO Items (companyId, itemCode, taxCode, description, itemGroup) VALUES ('247015', 'CERMUG1', 'P0000000', 'Ceramic Mug', 'Mugs')")
Update

Avalara allows updates to Items columns.

UPDATE Items SET Description = 'description', itemcode = 'CERMUG' WHERE ID = '189117938' AND CompanyId = '247015'

In order to update ItemsClassifications, a #TEMP table should be used.

INSERT INTO Itemsclassifications#TEMP (systemcode, productcode) VALUES ('P0000001', 'AVATAXCEDE')")
UPDATE items SET description = 'description', itemcode = 'CERMUG', linkedclassifications = 'Itemsclassifications#TEMP' WHERE ID = '189117938' AND companyId = '247015'")
Delete

Items can be deleted by providing the ID of the address and issuing a DELETE statement.

DELETE FROM Items WHERE ID = 100
Columns
Name Type ReadOnly Description
Id [KEY] Int False The unique ID number of this item.
CompanyId [KEY] Int False The unique ID number of the company that owns this item.
ItemCode String False A unique code representing this item.
ModifiedUserId Int False The user ID of the user who last modified this record.
LinkedClassifications String False List of classifications that belong to this item. A single classification consists of a productCode and a systemCode for a particular item.
CreatedUserId Int False The User ID of the user who created this record.
ItemGroup String False A way to group similar items.
ModifiedDate Datetime False The date/time when this record was last modified.
Description String False A friendly description of this item in your product catalog.
LinkedParameters String False List of item parameters.
CreatedDate Datetime False The date when this record was created.
TaxCode String False

Locations

Locations table for AvalaraAvatax data provider.

Insert

To create a location at least the following fields should be specified.

INSERT INTO Locations (CompanyId, Line1, LocationCode, AddressTypeId, AddressCategoryId, PostalCode, Country) VALUES (247015, '2000 Main Street', 'L001', 'Location', 'MainOffice', 92614, 'US')
Update

To update a location, you need to specify ID and CompanyId in WHERE clause.

UPDATE Locations SET City = 'Irvine' WHERE ID = 56789 AND CompanyId = 247015
Delete

To delete a location, you need to specify ID and AccountId in WHERE clause.

DELETE FROM Locations WHERE ID = 56789 AND CompanyId = 247015
Columns
Name Type ReadOnly Description
Id [KEY] Int False The unique ID number of this location.
CompanyId [KEY] Int False The unique ID number of the company that operates at this location.
RegisteredDate Datetime False The date when this location was registered with a tax authority. Not required.
DbaName String False If this location has a different business name from its legal entity name, specify the 'Doing Business As' name for this location.
EffectiveDate Date False The date when this location was opened for business, or null if not known.
CreatedUserId Int False The User ID of the user who created this record.
AddressCategoryId String False Indicates the type of place of business represented by this location.
Region String False Name or ISO 3166 code identifying the region within the country of the physical address of this location.
LastTransactionDate Datetime False The most recent date when a transaction was processed for this location. Set by AvaTax.
ModifiedDate Datetime False The date/time when this record was last modified.
OutletName String False A friendly name for this location.
IsRegistered Bool False Set this flag to true to indicate that this location has been registered with a tax authority.
Line3 String False The third line of the physical address of this location.
LocationCode String False A code that identifies this location. Must be unique within your company.
PostalCode String False The postal code or zip code of the physical address of this location.
Line2 String False The second line of the physical address of this location.
Line1 String False The first line of the physical address of this location.
AddressTypeId String False Indicates whether this location is a physical place of business or a temporary salesperson location.
ModifiedUserId Int False The user ID of the user who last modified this record.
Country String False Name or ISO 3166 code identifying the country of the physical address of this location.
City String False The city of the physical address of this location.
Description String False A friendly name for this location.
County String False The county name of the physical address of this location. Not required.
IsDefault Bool False Set this flag to true to indicate that this is the default location for this company.
EndDate Date False If this place of business has closed, the date when this location closed business.
LinkedSettings String False Extra information required by certain jurisdictions for filing.
CreatedDate Datetime False The date when this record was created.
PageKey String False

MultiDocument

MultiDocument table for AvalaraAvatax data provider.

Columns
Name Type ReadOnly Description
Id [KEY] Int False The unique ID number of this MultiDocument object.
Code [KEY] String False The transaction code of the MultiDocument transaction. All individual transactions within this MultiDocument object will have this code as a prefix.
Type [KEY] String False Indicates the type of MultiDocument transaction.
ModifiedUserId Int False The user ID of the user who last modified this record.
CreatedUserId Int False The user ID of the user who created this record.
ModifiedDate Datetime False The date/time when this record was last modified.
AccountId Int False The unique ID number of the AvaTax customer account that created this MultiDocument object.
LinkedDocuments String False A separate document will exist for each unique combination of buyer and seller in this MultiDocument transaction.
CreatedDate Datetime False The date/time when this record was created.

Nexus

Nexus table for AvalaraAvatax data provider.

Insert

To create a nexus declaration for your company, you must first obtain a list of Avalara-defined nexus querying NexusDefinitions table. Once you have determined which nexus you wish to declare, you should customize only the user-selectable fields in this object. The user selectable fields for the nexus object are CompanyId, EffectiveDate, EndDate, LocalNexusTypeId, TaxId, NexusTypeId, HasPermanentEstablishment, and IsSellerImporterOfRecord. When calling CreateNexus or UpdateNexus, all values in your nexus object except for the user-selectable fields must match an Avalara-defined system nexus object. If any data does not match, AvaTax may not recognize your nexus declaration. To create a Nexus you must specify Country, Region, JurisdictionTypeId, JurisCode, JurisName, ShortName, NexusTaxTypeGroup of an existing Avalara-defined system nexus object and optionally any other selectable field.

INSERT INTO Nexus (CompanyId, Country, Region, JurisdictionTypeId, JurisCode, JurisName, ShortName, NexusTaxTypeGroup, EffectiveDate) VALUES (283040, 'US', 'US', 'Country', 'US', 'UNITED STATES', 'UNITED STATES', 'Hospitality', '2019-11-08')
Update

Only user selectable fields can be updated. To update a nexus object, you need to specify ID and CompanyId in WHERE clause.

UPDATE Nexus SET HasPermanentEstablishment = true WHERE ID = 20684351 AND CompanyId = 283040
Delete

To delete a nexus object, you need to specify ID and CompanyId in WHERE clause.

DELETE FROM Nexus WHERE ID = 20684351 AND CompanyId = 283040
Columns
Name Type ReadOnly Description
Id [KEY] Int False The unique ID number of this declaration of nexus.
CompanyId [KEY] Int False The unique ID number of the company that declared nexus.
TaxAuthorityId Int False A unique ID number of the tax authority that is associated with this nexus.
IsSellerImporterOfRecord Bool False For nexus declarations at the country level, specifies whether this company is considered the importer of record in this nexus region. Set this value to `true` to consider your company as the importer of record and collect these taxes. Leave this value as false or null and taxes will be calculated as if your company is not the importer of record.
StateAssignedNo String False The state assigned number of this jurisdiction.
Sourcing String False Indicates whether this nexus is defined as origin or destination nexus.
JurisName String False The common name of the jurisdiction in which this company declared nexus.
TaxId String False Optional - the tax identification number under which you declared nexus.
JurisCode String False The code identifying the jurisdiction in which this company declared nexus.
EffectiveDate Date False The date when this nexus began. If not known, set to null.
CreatedUserId Int False The User ID of the user who created this record.
Region String False Name or ISO 3166 code identifying the region within the country.
ModifiedDate Datetime False The date/time when this record was last modified.
NexusTaxTypeGroup String False The type of nexus that this company is declaring. Replaces NexusTypeId.
ShortName String False The short name of the jurisdiction.
IsSSTActive Bool False For the United States, this flag indicates whether this particular nexus falls within a U.S.
JurisdictionTypeId String False The type of the jurisdiction in which this company declared nexus.
HasLocalNexus Bool False True if you are also declaring local nexus within this jurisdiction. Many U.S. states have options for declaring nexus in local jurisdictions as well as within the state.
ModifiedUserId Int False The user ID of the user who last modified this record.
Country String False Name or ISO 3166 code identifying the country in which this company declared nexus.
HasPermanentEstablishment Bool False Set this value to true if your company has a permanent establishment within this jurisdiction.
NexusTypeId String False The type of nexus that this company is declaring.
EndDate Date False If this nexus will end or has ended on a specific date, set this to the date when this nexus ends.
LocalNexusTypeId String False If you are declaring local nexus within this jurisdiction, this indicates whether you are declaring only a specified list of local jurisdictions, all state-administered local jurisdictions, or all local jurisdictions.
SignatureCode String False The signature code of the boundary region as defined by Avalara.
CreatedDate Datetime False The date when this record was created.
PageKey String False
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.

Notices

Notices table for AvalaraAvatax data provider.

Columns
Name Type ReadOnly Description
Id [KEY] Int False The unique ID number of this notice.
CompanyId [KEY] Int False The unique ID number of the company to which this notice belongs.
TicketReferenceNo String False The ticket reference number of the notice.
ExpectedResolutionDate Datetime False Expected resolution date of the notice.
ShowResolutionDateToCustomer Bool False Indicator to show customer this resolution date.
ClosedDate Datetime False The closed date of the notice.
FilingFrequency String False The filing frequency of the notice.
LinkedComments String False Additional comments on the notice.
CustomerTypeId String False NoticeCustomerTypeID can be retrieved from the definitions API.
ReceivedDate Datetime False The received date of the notice.
PriorityId String False The priority of the notice.
TypeId Int False The tax notice type id.
JurisdictionType String False The jurisdiction type of the notice.
SalesForceCaseUrl String False The URL to the sales force case.
Description String False The description of the notice.
Reason String False The notice reason.
TaxPeriod String False The tax period of the notice.
CreatedDate Datetime False The date when this record was created.
TotalRemit String False The total remmitance amount for the notice.
CreatedUserId Int False The User ID of the user who created this record.
StatusId Int False The status ID of the notice.
ModifiedDate Datetime False The date/time when this record was last modified.
JurisdictionName String False The jurisdiction name of the notice.
FilingTypeId String False The filing type of the notice.
ComplianceContactId Int False The ID of the compliance contact.
Status String False The status of the notice.
TaxAuthorityId Int False The tax authority ID of the notice.
CustomerComment String False Comments from the customer on this notice.
TaxFormCode String False The tax form code of the notice.
RevenueContactId Int False The ID of the revenue contact.
DocumentReference String False The document reference of the notice.
ReasonId Int False The notice reason id.
LinkedRootCause String False Notice Root Cause Details.
ClosedByUserId Int False The unique ID number of the user that closed the notice.
Country String False Name or ISO 3166 code identifying the country that sent this notice.
TicketReferenceUrl String False The ticket reference URL of the notice.
CreatedByUserName String False The user who created the notice.
OwnedByUserId Int False The unique ID number of the user that owns the notice.
HideFromCustomer Bool False Indicator to hide from customer.
LinkedResponsibility String False Notice Responsibility Details.
ModifiedUserId Int False The user ID of the user who last modified this record.
Region String False Name or ISO 3166 code identifying the region within the country that sent this notice.
CustomerFundingOptionId String False The notice customer funding options.
AvaFileFormId Int False The ava file form ID of the notice.
SalesForceCase String False The sales force case of the notice.
LinkedFinances String False Finance details of the notice.
Type String False The tax notice type description.
PageKey String False

Notifications

Notifications table for AvalaraAvatax data provider.

Columns
Name Type ReadOnly Description
Id [KEY] Int False The unique ID of the notification.
CompanyId Int False If this notification was tied to a specific company, this will be the unique ID number of the company that received the notification.
CreatedUserId Int False The unique ID number of the user who created the notification.
ActionDueDate Datetime False If there is a specific action suggested by this notification, and if this action is requested by a specific due date, this value will be the due date for the action.
ActionName String False If there is a specific action suggested by this notification, this is the name of the action.
ReferenceObject String False The type name of object referred to by this notification, if any.
Dismissed Bool False Wether the notification is dismissed.
ModifiedDate Datetime False The UTC timestamp of the time when this notification was last modified.
ReferenceId Int False The unique reference ID number of the object referred to by this notification, if any.
Message String False The message for this notification. This is a friendly description of the notification and any relevant information that can help you decide what kind of action, if any, to take in response.
ActionLink String False If there is a specific action suggested by this notification, this is the URL of the action.
DismissedDate Datetime False If this notification has been dismissed, this indicates the timestamp (in UTC time) when the user dismissed the notification.
ModifiedUserId Int False The unique ID number of the user who most recently modified this notification.
ExpireDate Datetime False If this notification is time sensitive, this is the latest date when the notification should be displayed to the user.
NeedsAction Bool False If this notification object requires user action to resolve, this value will be set to true.
DismissedByUserId Int False If this notification has been dismissed, this indicates the unique ID number of the user that dismissed the notification.
Category String False The category of this notification.
Topic String False The topic of this notification.
AccountId Int False The unique ID number of the account that received this notification.
CreatedDate Datetime False The UTC timestamp of the time when this notification was created.
SeverityLevelId String False The severity level of the notification.
PageKey String False

Settings

Settings table for AvalaraAvatax data provider.

The company settings system is a metadata system that you can use to store extra information about a company. Your integration or connector could use this data storage to keep track of preference information, reminders, or any other storage that would need to persist even if the customer uninstalls your application.

Insert

A setting can refer to any type of data you need to remember about this company object. When creating this object, you may define your own set, name, and value parameters. To define your own values, please choose a set name that begins with X- to indicate an extension. CompanyId, Set and Name are required fields.

INSERT INTO Settings (CompanyId, Set, Name) VALUES (283040, 'ItemsToRemember', 'Texas')
Update

To update a setting, you need to specify ID and CompanyId in WHERE clause.

UPDATE Settings SET Value = 'Alamo' WHERE ID = 297847 AND CompanyId = 283040
Delete

To delete a setting, you need to specify ID and CompanyId in WHERE clause.

DELETE FROM Settings WHERE ID = 297847 AND CompanyId = 283040
Columns
Name Type ReadOnly Description
Id [KEY] Int False The unique ID number of this setting.
CompanyId [KEY] Int False The unique ID number of the company this setting refers to.
Name String False A user-defined name for this name-value pair.
Value String False The value of this name-value pair.
Set String False A user-defined set containing this setting.
PageKey String False

TaxCodes

TaxCodes table for AvalaraAvatax data provider.

Insert

Create one or more new taxcode objects attached to a company specifying at least CompanyId, TaxCode, TaxCodeTypeId:

INSERT INTO TaxCodes (TaxCode, taxCodeTypeId, CompanyId) VALUES ('PS081283', 'P', '283040')
Update

Avalara allows one of the following user roles: AccountAdmin, CompanyAdmin, CSPTester, SSTAdmin, TechnicalSupportAdmin to update TaxCode objects. CompanyId and ID should be specified in the WHERE clause.

UPDATE TaxCodes SET Description = 'Yarn' WHERE ID = 85083 AND CompanyId = 283040
Delete

To delete a TaxCode object CompanyId and ID should be specified in the WHERE clause:

DELETE FROM TaxCodes WHERE ID = 85083 AND CompanyId = 283040
Columns
Name Type ReadOnly Description
Id [KEY] Int False The unique ID number of this tax code.
CompanyId [KEY] Int False The unique ID number of the company that owns this tax code.
ModifiedUserId Int False The user ID of the user who last modified this record.
CreatedUserId Int False The User ID of the user who created this record.
EntityUseCode String False The Avalara Entity Use Code represented by this tax code.
ModifiedDate Datetime False The date/time when this record was last modified.
TaxCodeTypeId String False The type of this tax code.
GoodsServiceCode Int False The Avalara Goods and Service Code represented by this tax code.
IsSSTCertified Bool False True if this tax code has been certified by the Streamlined Sales Tax governing board.
TaxCode String False A code string that identifies this tax code.
Description String False A friendly description of this tax code.
IsPhysical Bool False True if this tax code type refers to a physical object. Read only field.
IsActive Bool False True if this tax code is active and can be used in transactions.
CreatedDate Datetime False The date when this record was created.
PageKey String False

TaxRules

TaxRules table for AvalaraAvatax data provider.

Insert

You can create custom tax rules to customize the behavior of AvaTax to match specific rules that are custom to your business. If you have obtained a ruling from a tax auditor that requires custom tax calculations, you can use custom tax rules to redefine the behavior for your company or item.

INSERT INTO TaxRules (CompanyId, JurisCode, JurisName, JurisdictionTypeId, TaxRuleTypeId, Country, TaxTypeId, TaxCode, TaxCodeId, TaxTypeGroup, TaxSubType) VALUES ('283040', '013', 'MARICOPA', 'County', 'ProductTaxabilityRule', 'US', 'BothSalesAndUseTax', 'PS081283', '85083', 'SalesAndUse', 'ALL')
Update

To update a tax rule, you need to specify ID and CompanyId in WHERE clause.

UPDATE TaxRules SET Description = 'Freight' WHERE ID = 56789 AND CompanyId = 247015
Delete

To delete a tax rule, you need to specify ID and AccountId in WHERE clause.

DELETE FROM TaxRules WHERE ID = 56789 AND CompanyId = 247015
Columns
Name Type ReadOnly Description
Id [KEY] Int False The unique ID number of this tax rule.
CompanyId [KEY] Int False The unique ID number of the company that owns this tax rule.
UomId Int False For tax rules that are calculated using units of measurement, this indicates the unit of measurement type used to calculate the amounts for this rule.
Threshold String False The per-unit threshold that must be met before this rule applies.
TaxRuleTypeId String False This type value determines the behavior of the tax rule.
JurisCode String False The code of the jurisdiction to which this tax rule applies.
EntityUseCode String False The entity use code to which this rule applies.
NonPassthroughExpression String False Reserved for Avalara internal usage. Leave this field null.
Description String False A friendly name for this tax rule.
CreatedDate Datetime False The date when this record was created.
CountyFIPS String False For U.S. tax rules, this is the county's Federal Information Processing Standard (FIPS) code.
CreatedUserId Int False The User ID of the user who created this record.
EndDate Date False The last date for which this rule applies. If `null`, this rule will apply to all dates after the effective date.
TaxSubType String False This field has different behavior based on the type of rule. For a product taxability rule, if the rule applies to an item, this value will override the tax sub type of the original product. For other rules, this value determines what tax sub types will be affected by the rule.
ModifiedDate Datetime False The date/time when this record was last modified.
TaxCode String False For rules that apply to a specific tax code only, this specifies which tax code is affected by this rule.
Options String False Supports custom options for your tax rule.
JurisName String False The name of the jurisdiction to which this tax rule applies.
TaxTypeGroup String False This field has different behavior based on the type of rule. For a product taxability rule, if the rule applies to an item, this value will override the tax type group of the original product. For other rules, this value determines what tax type groups will be affected by the rule.
JurisdictionTypeId String False The type of the jurisdiction to which this tax rule applies.
Cap String False The maximum cap for the price of this item according to this rule. Any amount above this cap will not be subject to this rule.
Country String False Name or ISO 3166 code identifying the country where this rule will apply.
Value String False This field has different behavior based on the type of the tax rule. For a product taxability rule, this value is either 1 or 0, indicating taxable or non-taxable. For a rate override rule, this value is the corrected rate stored as a decimal, for example, a rate of 5% would be stored as 0.05 decimal. If you use the special value of 1.0, only the cap and threshold values will be applied and the rate will be left alone.
PreferredProgramId Int False Reserved for Avalara internal usage. Leave this field null.
CurrencyCode String False The currency code to use for this rule.
EffectiveDate Date False The first date at which this rule applies. If `null`, this rule will apply to all dates prior to the end date.
StateFIPS String False For U.S. tax rules, this is the state's Federal Information Processing Standard (FIPS) code.
RateTypeCode String False Indicates the code of the rate type that applies to this rule.
ModifiedUserId Int False The user ID of the user who last modified this record.
Region String False Name or ISO 3166 code identifying the region where this rule will apply.
TaxTypeId String False Some tax type groups contain multiple different types of tax. To create a rule that affects only one type of tax within a tax type group, set this value to the code matching the specific tax type within that group.
Sourcing String False The sourcing types to which this rule applies.
TaxCodeId Int False For rules that apply to a specific tax code only, this specifies which tax code is affected by this rule.
IsAllJuris Bool False Allows you to make tax rules apply to lower jurisdictions. This feature is only available in the United States and Canada.
PageKey String False

TransactionLines

TransactionLines view for AvalaraAvatax data provider.

Select

The connector will use the Avalara API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector. By default the lines of Adjusted transactions will not be listed. If you want to include them, you need to set IncludeAdjustedTransactions to true.

  • TrsnactionId, TransactionCode support the '=', IN operator.

For example, the following query is processed server side:

SELECT * FROM TransactionLines WHERE TransactionCode IN ('Code1', 'Code2')
Delete

To delete a TransactionLine item, you need to specify the TransactionCode and LineNumber in the WHERE clause.

DELETE FROM TransactionLines WHERE TransactionCode = 'TC001' AND LineNumber = 1
Columns
Name Type ReadOnly Description
Id Long False The unique ID number of this transaction line item.
TransactionId Long False The unique ID number of the transaction to which this line item belongs.
TransactionStatus String False The status of the transaction to which this line item belongs.
TransactionCode [KEY] String False A unique customer-provided code identifying this transaction.
DestinationAddressId Long False The unique ID number of the destination address where this line was delivered or sold. In the case of a point-of-sale transaction, the destination address and origin address will be the same. In the case of a shipped transaction, they will be different.
TaxCalculated String False The amount of tax that AvaTax calculated for the transaction.
DiscountAmount String False The amount of discount that was applied to this line item.
HsCode String False The cross-border harmonized system code (HSCode) used to calculate tariffs and duties for this line item.
DiscountTypeId Int False The type of discount, if any, that was applied to this line item.
TaxableAmount String False The taxable amount of this line item.
CostInsuranceFreight String False Indicates the cost of insurance and freight for this line.
CertificateId String False The CertCapture Certificate ID
TaxDate Date False The date that was used for calculating tax amounts for this line item.
Ref2 String False A user-defined reference identifier for this transaction line item.
EntityUseCode String False The entity use code for this line item. Usage type often affects taxability rules.
Description String False A description of the item or service represented by this line.
Ref1 String False A user-defined reference identifier for this transaction line item.
ExemptNo String False The customer Tax ID Number (tax_number) associated with a certificate.
Quantity String False The quantity of products sold on this line item.
TaxCode String False The code string for the tax code that was used to calculate this line item.
TaxOverrideType String False If a tax override was specified, this indicates the type of tax override.
LineNumber [KEY] String False The line number or code indicating the line on this invoice or receipt or document.
BusinessIdentificationNo String False VAT business identification number used for this transaction.
ReportingDate Date False The date when this transaction should be reported. By default, all transactions are reported on the date when the actual transaction took place. In some cases, line items may be reported later due to delayed shipments or other business reasons.
VatNumberTypeId Int False Indicates the VAT number type for this line item.
VatCode String False Indicates the VAT code for this line item.
LineAmount String False The total amount of the transaction, including both taxable and exempt.
Tax String False The tax for this line in this transaction.
LinkedLineLocationTypes String False Optional: A list of location types for this line item.
IsItemTaxable Bool False True if this item is taxable.
TaxOverrideAmount String False If a tax override was specified, this indicates the amount of tax that was requested.
ExemptCertId Int False The unique ID number of the exemption certificate that applied to this line item. It is the calc_id associated with a certificate in CertCapture.
ItemCode String False The code string of the item represented by this line item.
BoundaryOverrideId Int False The unique ID number of the boundary override applied to this line item.
OriginAddressId Long False The unique ID number of the origin address where this line was delivered or sold.
TaxOverrideReason String False If a tax override was specified, represents the reason for the tax override.
ExemptAmount String False The amount of this line item that was exempt.
TaxIncluded Bool False Indicates whether the amount for this line already includes tax.
IsSSTP Bool False True if this item is a Streamlined Sales Tax line item.
Sourcing String False Indicates whether this line item was taxed according to the origin or destination.
TaxEngine String False The tax engine identifier that was used to calculate this line item.
TaxCodeId Int False The unique ID number for the tax code that was used to calculate this line item.
RevAccount String False The revenue account number for this line item.

Transactions

Transactions table for AvalaraAvatax data provider.

Select

The connector will use the Avalara API to process WHERE clause conditions built with the following columns and operators. Most of the columns support server side filtering with the following operators: =,>,<,<=,>=,IN,NOT,IN,AND,OR,LIKE. The rest of the filter is executed client side within the connector. By default the Adjusted transactions will not be listed. If you want to include them, you need to set IncludeAdjustedTransactions to true. For example, the following query is processed server side:

SELECT * FROM Transactions WHERE AdjustmentReason = 'PriceAdjusted'
SELECT * FROM Items WHERE Status = 'Cancelled'
Insert

To add a new Transaction item, the following columns need to be specified: CustomerCode, Date, Type, LinkedLines, Code, LinkedAddresses. If you want to commit the transaction, set the Commit flag in the request to true. The linked columns can be inserted using a #TEMP table or by passing the aggregate value.

The LinkedAddresses aggregate must include information about the origin and destination address. If this transaction occurred at a retail point-of-sale location, provide that single address here and leave all other address types null. If origin differs from destination, insert both ShipFrom and ShipTo address types. If you wish to use the address of an existing location for the actual company, specify the address in the addressType_locationCode field.

The examples below illustrate the aforementioned scenarios.

Inserting LinkedAddresses aggregate through #TEMP table with SingleLocation:

INSERT INTO Addresses#TEMP (SingleLocation_line1, SingleLocation_City, SingleLocation_County, SingleLocation_PostalCode) VALUES ('2000 Main Street', 'Irvine', 'US', 92614)")
INSERT INTO Addresses#TEMP (SingleLocation_locationCode) VALUES ('LC001')")

Inserting LinkedAddresses aggregate through #TEMP table with ShipFrom and ShipTo locations:

INSERT INTO Addresses#TEMP (shipTo_line1, shipTo_City, shipTo_County, shipTo_PostalCode, shipFrom_line1, shipFrom_City, shipFrom_Country, shipFrom_PostalCode) VALUES ('2000 Main Street', 'Irvine', 'US', 92614, '', '', 'US', '')")
INSERT INTO Addresses#TEMP (shipTo_locationCode, shipFrom_locationCode) VALUES ('LC001', 'LC002')")

Insert line items aggregate using #TEMP table. For a list of the columns you can insert into the temporary table, refer to TransactionLines table.

INSERT INTO TransactionLines#TEMP (ItemCode, Quantity, LineAmount) VALUES ('IC005', 3, 300)")

Insert into the main Transactions table :

INSERT INTO Transactions (LinkedLines, LinkedAddresses, Code, CompanyId, CustomerCode, Date, Type, Commit, TotalTax) VALUES (TransactionLines#TEMP, Addresses#TEMP, DC008, 247015, 'CC002', '09/30/2019', 'SalesInvoice', true, 12.0)")
Update

Avalara allows updates on Transactions. To update a transaction, AdjustmentReason and Adjustment description are required fields. Additionally, you need to specify the Code of the transaction you are updating in the WHERE clause.

Similarly, to Insert, you can update line items using temporary tables. To update an existing line, include the LineNumber of the line you wish to update. To insert a new line in the transaction, set the LineNumber to a new, non-existing value.

When a transaction is updated, the actual transaction's status changes to Adjusted and a new transaction with the updated information is created. The new transaction will have the same document code, but a different ID and status: Saved or Commited. If you wish to commit the new updated transaction, set the Commit flag in the request to true.

INSERT INTO TransactionLines#TEMP (ItemCode, Quantity, LineAmount, LineNumber) VALUES (IC007, 3, 300, 3)
UPDATE Transactions SET LinkedLines = 'TransactionLines#TEMP', AdjustmentDescription = 'MyDescription', AdjustmentReason = 'PriceAdjusted' WHERE Code = '051349'
Columns
Name Type ReadOnly Description
Id Long False The unique ID number of this transaction.
Code [KEY] String False A unique customer-provided code identifying this transaction.
CompanyId Int False The unique ID number of the company that recorded this transaction.
ExchangeRateEffectiveDate Date False If this transaction included foreign currency exchange, this is the date as of which the exchange rate was calculated.
SalespersonCode String False The salesperson who provided this transaction. Not required.
AdjustmentDescription String False If this transaction was adjusted, indicates a description of the reason why the transaction was adjusted.
DestinationAddressId Long False The unique ID number of the destination address for this transaction.
LinkedLocationTypes String False A list of location types in this transaction.
IsSellerImporterOfRecord Bool False By default, the value is null, when the value is null, the value can be set at nexus level and used. If the value is not null, it will override the value at nexus level. If true, this seller was considered the importer of record of a product shipped internationally.
BatchCode String False If this transaction was created as part of a batch, this code indicates which batch.
LinkedTaxDetailsByTaxType String False Contains the tax details per tax type
TaxDate Datetime False Tax date for this transaction
EntityUseCode String False The entity use code for this transaction. Entity use codes often affect exemption or taxability rules.
LinkedInvoiceMessages String False Invoice messages associated with this transaction. Currently, this stores legally-required VAT messages.
Description String False Description of this transaction. Field permits unicode values.
LinkedAddresses String False A list of addreses used in this transaction.
TotalTax String False The total tax for all lines in this transaction.
LinkedMessages String False List of informational and warning messages regarding this API call. These messages are only relevant to the current API call.
ExemptNo String False The customer Tax ID Number (taxMyUserumber) associated with a certificate.
TotalTaxable String False The portion of the total amount of this transaction that was taxable.
ModifiedDate Datetime False The date/time when this record was last modified.
Email String False Email address associated with this transaction.
TaxOverrideType String False If a tax override was applied to this transaction, indicates what type of tax override was applied.
BusinessIdentificationNo String False VAT business identification number used for this transaction.
ReportingLocationCode String False For customers who use location-based tax reporting, this field controls how this transaction will be filed for multi-location tax filings.
TotalTaxCalculated String False The amount of tax that AvaTax calculated for the transaction.
ExchangeRate String False If this transaction included foreign currency exchange, this is the exchange rate that was used.
CustomerCode String False Unique code identifying the customer that requested this transaction.
Locked Bool False If this transaction has been reported to a tax authority, this transaction is considered locked and may not be adjusted after reporting.
Status String False The status of the transaction.
TotalAmount String False The total amount of this transaction.
LinkedLines String False A list of line items in this transaction.
TotalDiscount String False The total amount of discounts applied to all lines within this transaction.
AdjustmentReason String False If this transaction was adjusted, indicates the unique ID number of the reason why the transaction was adjusted.
TaxOverrideAmount String False If a tax override was applied to this transaction, indicates the amount of tax that was requested by the customer.
SoftwareVersion String False The software version used to calculate this transaction.
Country String False The two-character ISO 3166 code of the country for this transaction.
CurrencyCode String False The three-character ISO 4217 currency code that was used for payment for this transaction.
OriginAddressId Long False The unique ID number of the origin address for this transaction.
TotalExempt String False The amount of this transaction that was exempt.
PurchaseOrderNo String False The customer-supplied purchase order number of this transaction.
TaxOverrideReason String False If a tax override was applied to this transaction, indicates the reason for the tax override.
TaxOverrideDate Date False The override tax date to use
LinkedParameters String False Contains a list of extra parameters that were set when the transaction was created.
LinkedSummary String False Contains a summary of tax on this transaction.
Version Int False If this transaction was adjusted, this indicates the version number of this transaction. Incremented each time the transaction is adjusted.
Date Date False The date on which this transaction occurred.
ModifiedUserId Int False The user ID of the user who last modified this record.
Region String False The two-or-three character ISO region code of the region for this transaction.
ReferenceCode String False A user-defined reference code for this transaction.
Reconciled Bool False If this transaction has been reconciled against the company's ledger, this value is set to true.
Type [KEY] String False The type of the transaction.
Commit Boolean False Wether to commit a transaction on creation. Used only on inserts

Upcs

Upcs table for AvalaraAvatax data provider.

A UPC represents a single Universal Product Code in your catalog and matches this product to the tax code identified by this UPC.

Insert

To create a UPC object CompanyId, Upc and Description are required fields.

INSERT INTO Upcs (CompanyId, Upc, Description) VALUES (283040, '023032550992', 'Yarn')
Update

To update a Upc, you need to specify ID and CompanyId in WHERE clause.

UPDATE Upcs SET LegacyTaxCode = 'PS081282' WHERE ID = 123456789 AND CompanyId = 283040
Delete

To delete a Upc, you need to specify ID and CompanyId in WHERE clause.

DELETE FROM Upcs WHERE ID = 123456789 AND CompanyId = 283040
Columns
Name Type ReadOnly Description
Id [KEY] Int False The unique ID number for this UPC.
CompanyId [KEY] Int False The unique ID number of the company to which this UPC belongs.
ModifiedUserId Int False The user ID of the user who last modified this record.
CreatedUserId Int False The User ID of the user who created this record.
Upc String False The 12-14 character Universal Product Code, European Article Number, or Global Trade Identification Number.
ModifiedDate Datetime False The date/time when this record was last modified.
EndDate Date False If this UPC expired or will expire on a certain date, this contains the last date on which the UPC was effective.
LegacyTaxCode String False Legacy Tax Code applied to any product sold with this UPC.
Usage Int False A usage identifier for this UPC code.
IsSystem Int False A flag indicating whether this UPC code is attached to the AvaTax system or to a company.
EffectiveDate Date False If this UPC became effective on a certain date, this contains the first date on which the UPC was effective.
Description String False Description of the product to which this UPC applies.
CreatedDate Datetime False The date when this record was created.
PageKey String False

Users

Users table for AvalaraAvatax data provider.

Insert

To create a new user the following fields should be specified. A newly created user will receive an email inviting them to create their password. This means that you must provide a valid email address for all user accounts created.

INSERT INTO Users (AccountId, UserName, FirstName, LastName, Email, securityRoleId) VALUES (2000137504, 'user123', 'James', 'Smith', 'smith@gmail.com', 'AccountUser')
Update

Only user selectable fields can be updated. To update a user, you need to specify ID and AccountId in WHERE clause.

UPDATE Users SET IsActive = true WHERE ID = 332668 AND AccountId = 2000137504
Delete

To delete a user, you need to specify ID and AccountId in WHERE clause.

DELETE FROM Users WHERE ID = 332668 AND AccountId = 2000137504
Columns
Name Type ReadOnly Description
Id [KEY] Int False The unique ID number of this user.
AccountId [KEY] Int False The unique ID number of the account to which this user belongs.
PostalCode String False The postal code in which this user resides.
SecurityRoleId String False The security level for this user.
ModifiedDate Datetime False The date/time when this record was last modified.
PasswordStatus String False The status of the user's password. For a new user created, this is always going to be `UserMustChange`.
Email String False The email address to be used to contact this user. If the user has forgotten a password, an email can be sent to this email address with information on how to reset this password.
UserName String False The username which is used to log on to the AvaTax website, or to authenticate against API calls.
CompanyId Int False If this user is locked to one company (and its children), this is the unique ID number of the company to which this user belongs.
FirstName String False The first or given name of the user.
LastName String False The last or family name of the user.
IsActive Bool False True if this user is currently active.
CreatedDate Datetime False The date when this record was created.
PageKey String False

Views

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

Avalara Connector Views

Name Description
BatchFiles BatchFiles view for AvalaraAvatax data provider.
CustomerCustomFields CustomerCustomFields view for AvalaraAvatax data provider.
CustomerExposureZones CustomerExposureZones view for AvalaraAvatax data provider.
FilingCalendarAvailablePOA FilingCalendarAvailablePOA view for AvalaraAvatax data provider.
ItemClassifications ItemClassifications view for AvalaraAvatax data provider.
ItemParameters ItemParameters view for AvalaraAvatax data provider.
LocationSettings LocationSettings view for AvalaraAvatax data provider.
Nexus Nexus table for AvalaraAvatax data provider.
NoticeComments NoticeComments view for AvalaraAvatax data provider.
NoticeFinances NoticeFinances view for AvalaraAvatax data provider.
NoticeResponsibility NoticeResponsibility view for AvalaraAvatax data provider.
NoticeRootCause NoticeRootCause view for AvalaraAvatax data provider.
ReportParameters ReportParameters view for AvalaraAvatax data provider.
Reports Reports table for AvalaraAvatax data provider.
Subscriptions Subscriptions table for AvalaraAvatax data provider.
TransactionAddresses TransactionAddresses view for AvalaraAvatax data provider.
TransactionInvoiceMessages TransactionInvoicemessages view for AvalaraAvatax data provider.
TransactionLocationTypes TransactionLocationTypes view for AvalaraAvatax data provider.
TransactionMessages TransactionMessages view for AvalaraAvatax data provider.
TransactionParameters TransactionParameters view for AvalaraAvatax data provider.
TransactionSummary TransactionSummary view for AvalaraAvatax data provider.
TransactionTaxDetailsByTaxType TransactionTaxdetailsbytaxtype view for AvalaraAvatax data provider.

BatchFiles

BatchFiles view for AvalaraAvatax data provider.

Columns
Name Type Description
Id [KEY] Int The unique ID number assigned to this batch file.
BatchId Int The unique ID number of the batch that this file belongs to.
CompanyId [KEY] Int The Company ID number of the company that owns this batch.
Name String Logical Name of file.
ContentLength Int Size of content, in bytes.
FilePath String Path to the file - name/S3 key.
FileExtension String File extension (e.g. CSV).
FileLocalPath String Local path of the file.
ErrorCount Int Number of errors that occurred when processing this file.
ContentType String Content mime type (e.g. text/csv). This is used for HTTP downloading.

CustomerCustomFields

CustomerCustomFields view for AvalaraAvatax data provider.

Select

To retrieve records from CustomerCustomFields view, at least CompanyId or CompanyId and CompanyCode should be specified in the WHERE clause. The connector will use the Avalara API to process WHERE clause conditions built with the following columns and operators. CompanyId and CompanyCode support server side filtering with the = operator. The rest of the filter is executed client side within the connector. For example, the following query is processed server side:

SELECT * FROM CustomerCustomFields WHERE CompanyId = '247015' AND CustomerCode = 'CC001'
Columns
Name Type Description
CompanyId [KEY] String The company ID of the parent record.
CustomerCode [KEY] String The customer code this record is associated to.
Name String The name of the custom field.
Value String The value of the custom field.

CustomerExposureZones

CustomerExposureZones view for AvalaraAvatax data provider.

Select

To retrieve records from CustomerExposureZones view, at least CompanyId or CompanyId and CompanyCode should be specified in the WHERE clause. The connector will use the Avalara API to process WHERE clause conditions built with the following columns and operators. CompanyId and CompanyCode support server side filtering with the = operator. The rest of the filter is executed client side within the connector. For example, the following query is processed server side:

SELECT * FROM CustomerExposureZones WHERE CompanyId = '247015' AND CustomerCode = 'CC001'
Columns
Name Type Description
Id Int A unique ID number representing this exposure zone.
CompanyId [KEY] Int The unique ID number of the AvaTax company that recorded this customer.
CustomerCode [KEY] String The customer code this record is associated to.
Name String The short name of this exposure zone, suitable for use in a drop-down list.
Country String Two character ISO 3166 county code for the country component of this exposure zone.
Created Datetime The date when this record was created.
Region String Two or three character ISO 3166 region, province, or state name of this exposure zone.
Description String A more complete description of this exposure zone, suitable for use as a tooltip or help text.
Modified Datetime The date/time when this record was last modified.
Tag String A tag indicating

FilingCalendarAvailablePOA

FilingCalendarAvailablePOA view for AvalaraAvatax data provider.

Columns
Name Type Description
CompanyId [KEY] String Company ID of the parent record.
Bytes String Bytes of the file.
Filename String Original filename.
Success Bool True if this download succeeded.
ContentType String Mime content type.

ItemClassifications

ItemClassifications view for AvalaraAvatax data provider.

Columns
Name Type Description
CompanyId [KEY] String The CompanyId of the parent record.
ItemId [KEY] String The unique ID number of the item this product belongs to.
SystemCode String The system code in which the product belongs.
ProductCode String The product code of an item in a given system.

ItemParameters

ItemParameters view for AvalaraAvatax data provider.

Columns
Name Type Description
Id [KEY] Int The ID of the parameter.
CompanyId [KEY] String The CompanyId of the parent record.
ItemId [KEY] Int The item id.
Name String The parameter's name.
Unit String The unit of measurement code for the parameter.
Value String The value for the parameter.

LocationSettings

LocationSettings view for AvalaraAvatax data provider.

Columns
Name Type Description
CompanyId [KEY] String CompanyId of the parent record.
LocationId [KEY] String The LocationId that this setting is associated to.
QuestionId Int The unique ID number of the location question answered.
QuestionName String The name of the question.
Value String The answer the user provided.

Nexus

Nexus table for AvalaraAvatax data provider.

Insert

To create a nexus declaration for your company, you must first obtain a list of Avalara-defined nexus querying NexusDefinitions table. Once you have determined which nexus you wish to declare, you should customize only the user-selectable fields in this object. The user selectable fields for the nexus object are CompanyId, EffectiveDate, EndDate, LocalNexusTypeId, TaxId, NexusTypeId, HasPermanentEstablishment, and IsSellerImporterOfRecord. When calling CreateNexus or UpdateNexus, all values in your nexus object except for the user-selectable fields must match an Avalara-defined system nexus object. If any data does not match, AvaTax may not recognize your nexus declaration. To create a Nexus you must specify Country, Region, JurisdictionTypeId, JurisCode, JurisName, ShortName, NexusTaxTypeGroup of an existing Avalara-defined system nexus object and optionally any other selectable field.

INSERT INTO Nexus (CompanyId, Country, Region, JurisdictionTypeId, JurisCode, JurisName, ShortName, NexusTaxTypeGroup, EffectiveDate) VALUES (283040, 'US', 'US', 'Country', 'US', 'UNITED STATES', 'UNITED STATES', 'Hospitality', '2019-11-08')
Update

Only user selectable fields can be updated. To update a nexus object, you need to specify ID and CompanyId in WHERE clause.

UPDATE Nexus SET HasPermanentEstablishment = true WHERE ID = 20684351 AND CompanyId = 283040
Delete

To delete a nexus object, you need to specify ID and CompanyId in WHERE clause.

DELETE FROM Nexus WHERE ID = 20684351 AND CompanyId = 283040
Columns
Name Type Description
Id [KEY] Int The unique ID number of this declaration of nexus.
TaxAuthorityId Int A unique ID number of the tax authority that is associated with this nexus.
IsSellerImporterOfRecord Bool For nexus declarations at the country level, specifies whether this company is considered the importer of record in this nexus region. Set this value to `true` to consider your company as the importer of record and collect these taxes. Leave this value as false or null and taxes will be calculated as if your company is not the importer of record.
StateAssignedNo String The state assigned number of this jurisdiction.
Sourcing String Indicates whether this nexus is defined as origin or destination nexus.
JurisName String The common name of the jurisdiction in which this company declared nexus.
TaxId String Optional - the tax identification number under which you declared nexus.
JurisCode String The code identifying the jurisdiction in which this company declared nexus.
EffectiveDate Date The date when this nexus began. If not known, set to null.
CreatedUserId Int The User ID of the user who created this record.
Region String Name or ISO 3166 code identifying the region within the country.
ModifiedDate Datetime The date/time when this record was last modified.
NexusTaxTypeGroup String The type of nexus that this company is declaring. Replaces NexusTypeId.
ShortName String The short name of the jurisdiction.
IsSSTActive Bool For the United States, this flag indicates whether this particular nexus falls within a U.S.
JurisdictionTypeId String The type of the jurisdiction in which this company declared nexus.
HasLocalNexus Bool True if you are also declaring local nexus within this jurisdiction. Many U.S. states have options for declaring nexus in local jurisdictions as well as within the state.
ModifiedUserId Int The user ID of the user who last modified this record.
Country String Name or ISO 3166 code identifying the country in which this company declared nexus.
HasPermanentEstablishment Bool Set this value to true if your company has a permanent establishment within this jurisdiction.
NexusTypeId String The type of nexus that this company is declaring.
EndDate Date If this nexus will end or has ended on a specific date, set this to the date when this nexus ends.
LocalNexusTypeId String If you are declaring local nexus within this jurisdiction, this indicates whether you are declaring only a specified list of local jurisdictions, all state-administered local jurisdictions, or all local jurisdictions.
SignatureCode String The signature code of the boundary region as defined by Avalara.
CreatedDate Datetime The date when this record was created.
PageKey String
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.

NoticeComments

NoticeComments view for AvalaraAvatax data provider.

Columns
Name Type Description
Id [KEY] Int The unique ID number of this notice.
CompanyId [KEY] Int The company ID to which this file will be attached.
NoticeId [KEY] Int The ID of the notice this comment is attached too.
ModifiedUserId Int The user ID of the user who last modified this record.
AttachmentUploadRequest String An attachment to the detail
ResourceFileTypeId Int The resource type ID of this file.
Name String The original name of this file.
Content String This stream contains the bytes of the file being uploaded.
Length Int Length of the file in bytes.
Username String The username adding the file
AccountId Int The account ID to which this file will be attached.
Date Datetime The date this comment was entered
ResourceFileId Int resourceFileId
ModifiedDate Datetime The date/time when this record was last modified.
CommentTypeId Int taxNoticeCommentTypeId
Comment String TaxNoticeComment
CommentType String taxNoticeCommentType
CommentUserName String TaxNoticeCommentUserName
TaxNoticeFileName String TaxNoticeFileName
CommentLink String TaxNoticeCommentLink
CommentUserId Int TaxNoticeCommentUserId

NoticeFinances

NoticeFinances view for AvalaraAvatax data provider.

Columns
Name Type Description
Id [KEY] Int The Unique ID of the Finance Model.
CompanyId [KEY] Int The company ID to which this file will be attached.
NoticeId Int The unique ID of the the tax notice associated with the the finance detail.
NoticeNumber String The sequential number of the notice.
Penalty String The amount of penalty listed on the notice.
TaxAbated String The amount of tax abated on the notice.
ResourceFileId Int The ResourceFileId of the finance detail attachment.
CreatedUserId Int The User ID of the user who created this record.
ModifiedDate Datetime The date/time when this record was last modified.
TaxDue String The amount of tax due on the notice.
Interest String The amount of interest listed on the notice.
AttachmentUploadRequest String An attachment to the finance detail.
ResourceFileTypeId Int The resource type ID of this file.
Name String The original name of this file.
Content String This stream contains the bytes of the file being uploaded..
Length Int Length of the file in bytes.
Username String The username adding the file.
AccountId Int The account ID to which this file will be attached.
Credits String The amount of credits listed on the notice.
CspFeeRefund String The amount of CSP Fee Refund on the notice.
NoticeDate Datetime The date of the notice.
ModifiedUserId Int The user ID of the user who last modified this record.
CustomerPenalty String The amount of customer penalty on the notice.
DueDate Datetime The due date of the notice.
CustomerInterest String The amount of customer interest on the notice.
FileName String The name of the file attached to the finance detail.
CreatedDate Datetime The date when this record was created.

NoticeResponsibility

NoticeResponsibility view for AvalaraAvatax data provider.

Columns
Name Type Description
Id [KEY] Int The unique ID number of this filing frequency.
CompanyId [KEY] String The CompanyId of the parent record.
NoticeId Int TaxNoticeId
TaxNoticeResponsibilityId Int TaxNoticeResponsibilityId
Description String The description name of this filing frequency.

NoticeRootCause

NoticeRootCause view for AvalaraAvatax data provider.

Columns
Name Type Description
Id [KEY] Int The unique ID number of this filing frequency.
CompanyId [KEY] String The CompanyId of the parent record.
NoticeId [KEY] Int TaxNoticeId
Description String The description name of this root cause.
TaxNoticeRootCauseId Int TaxNoticeRootCauseId

ReportParameters

ReportParameters view for AvalaraAvatax data provider.

Columns
Name Type Description
ReportId [KEY] String The ID of the parent Report.
CurrencyCode String The currency code used for your report.
State String The state filter used for your report.
DateFormat String The date format used for your report.
StartDate Datetime The start date filter used for your report.
Country String The country filter used for your report.
Culture String The culture used your report.
DateFilter String The date type filter used for your report.
EndDate Datetime The end date filter used for your report.
DocType String The doc type filter used for your report.

Reports

Reports table for AvalaraAvatax data provider.

Columns
Name Type Description
Id [KEY] Int The unique identifier of the report task.
CompletedDate Datetime The time when the report was finished building, if completed.
CreatedUser String The userName of the user who initiated the report task.
CreatedUserId Int The ID of the user who initiated this task.
File String The name of the report file, if available.
Status String The current status of the report building task.
ReportType String The type of the report: ExportDocumentLine, etc.
AccountId Int The ID of the account the reported transactions are from.
Format String The format of the report file.
CompanyId Int The ID of the company the reported transactions are from.
LinkedParameters String The parametes used to build the report.
CurrencyCode String The currency code used for your report.
State String The state filter used for your report.
DateFormat String The date format used for your report.
StartDate Datetime The start date filter used for your report.
Country String The country filter used for your report.
Culture String The culture used your report.
DateFilter String The date type filter used for your report.
EndDate Datetime The end date filter used for your report.
DocType String The doc type filter used for your report.
CreatedDate Datetime The time when the report task was initiated.
Size Int The size of the report file, if available.
PageKey String

Subscriptions

Subscriptions table for AvalaraAvatax data provider.

Select

The connector will use the Avalara API to process WHERE clause conditions built with the following columns and operators. Most of the columns support server side filtering with the following operators: =,>,<,<=,>=,IN,NOT,IN,AND,OR,LIKE. The rest of the filter is executed client side within the connector. For example, the following query is processed server side:

SELECT * FROM Subscriptions WHERE SubscriptionDescription LIKE 'AVA%'
Columns
Name Type Description
Id [KEY] Int The unique ID number of this subscription.
AccountId [KEY] Int The unique ID number of the account this subscription belongs to.
ModifiedUserId Int The user ID of the user who last modified this record.
CreatedUserId Int The User ID of the user who created this record.
ModifiedDate Datetime The date/time when this record was last modified.
EndDate Date If the subscription has ended or will end, this date indicates when the subscription ends.
SubscriptionTypeId Int The unique ID number of the service that the account is subscribed to. If this is provided, subscription description is ignored.
EffectiveDate Date The date when the subscription began.
CreatedDate Datetime The date when this record was created.
SubscriptionDescription String A friendly description of the service that the account is subscribed to. You can either provide the subscription type ID or this but not both. If subscription type ID is provided, then this information is ignored and this field will be updated with the information from subscription type id.
PageKey String

TransactionAddresses

TransactionAddresses view for AvalaraAvatax data provider.

Columns
Name Type Description
Id Long The unique ID number of this address.
TransactionId Long The unique ID number of the document to which this address belongs.
TransactionCode [KEY] String A unique customer-provided code identifying this transaction.
TransactionStatus String The status of the transaction to which this line item belongs.
PostalCode String The postal code or zip code for the address.
Longitude String Longitude for this address
TaxRegionId Int The unique ID number of the tax region for this address.
Country String The ISO 3166 country code
City String The city for the address.
Latitude String Latitude for this address
Region String The ISO 3166 region code. E.g., the second part of ISO 3166-2.
Line3 String The third line of the address.
Line2 String The second line of the address.
Line1 String The first line of the address.
BoundaryLevel String The boundary level at which this address was validated.

TransactionInvoiceMessages

TransactionInvoicemessages view for AvalaraAvatax data provider.

Columns
Name Type Description
TransactionId [KEY] Long The transaction code of the parent record.
TransactionCode [KEY] String A unique customer-provided code identifying this transaction.
TransactionStatus String The status of the transaction to which this line item belongs.
LineNumbers String The applicable tax line numbers and codes.
Content String The content of the invoice message.

TransactionLocationTypes

TransactionLocationTypes view for AvalaraAvatax data provider.

Columns
Name Type Description
TransactionId Long Transaction ID
TransactionCode [KEY] String A unique customer-provided code identifying this transaction.
TransactionStatus String The status of the transaction to which this line item belongs.
TransactionAddressId Long Address ID for the transaction.
TransactionLocationTypeId Int Location type ID for this location type in transaction.
LocationTypeCode String Location type code

TransactionMessages

TransactionMessages view for AvalaraAvatax data provider.

Columns
Name Type Description
TransactionId [KEY] Long The transaction ID of the parent record.
TransactionCode [KEY] String The transaction code of the parent record.
TransactionStatus String The status of the transaction to which this line item belongs.
RefersTo String Information about what object in your request this message refers to.
Summary String A brief summary of what this message tells us.
Severity String A category that indicates how severely this message affects the results.
Source String The name of the code or service that generated this message.
Details String Detailed information that explains what the summary provided.

TransactionParameters

TransactionParameters view for AvalaraAvatax data provider.

Columns
Name Type Description
TransactionId [KEY] Long The transaction ID of the parent record.
TransactionCode [KEY] String The transaction code of the parent record.
TransactionStatus String The status of the transaction to which this line item belongs.
Name String The name of the parameter.
Unit String The unit of measure of the parameter value.
Value String The value of the parameter.

TransactionSummary

TransactionSummary view for AvalaraAvatax data provider.

Columns
Name Type Description
TransactionId [KEY] Long The transaction ID this summary is associated to.
TransactionCode [KEY] String A unique customer-provided code identifying this transaction.
TransactionStatus String The status of the transaction to which this line item belongs.
StateAssignedNo String The state assigned number of the jurisdiction that collects this tax.
JurisName String The name of the jurisdiction that collects this tax.
TaxGroup String Group code when special grouping is enabled.
JurisCode String Jurisdiction Code for the taxing jurisdiction
Region String Two or three character ISO region, state or province code, if applicable.
JurisType String The type of jurisdiction that collects this tax.
RateTypeCode String Indicates the code of the rate type.
TaxSubType String The tax subtype of this tax.
NonTaxable String The amount of the transaction that was non-taxable.
TaxName String The name of the tax.
TaxAuthorityType Int The unique ID of the Tax Authority Type that collects this tax.
Tax String Tax amount - The calculated tax (Base * Rate).
Taxable String Tax Base - The adjusted taxable amount.
Country String Two character ISO-3166 country code.
TaxCalculated String The amount of tax that AvaTax calculated for the transaction.
Exemption String The amount of the transaction that was exempt.
TaxType String The tax type of this tax.
Rate String Tax Rate - The rate of taxation, as a fraction of the amount.

TransactionTaxDetailsByTaxType

TransactionTaxdetailsbytaxtype view for AvalaraAvatax data provider.

Columns
Name Type Description
TransactionId [KEY] String
TransactionCode String A unique customer-provided code identifying this transaction.
TransactionStatus String The status of the transaction to which this line item belongs.
TotalNonTaxable String Total non taxable by tax type.
LinkedTaxSubTypeDetails String Tax subtype details.
TotalExempt String Total exempt by tax type.
TotalTaxable String Total taxable amount by tax type.
TotalTax String Total tax by tax type.
TaxType String Tax Type.

Stored Procedures

Stored procedures are function-like interfaces that extend the functionality of the connector beyond simple SELECT/INSERT/UPDATE/DELETE operations with Avalara.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Avalara, along with an indication of whether the procedure succeeded or failed.

Avalara Connector Stored Procedures

Name Description
CommitTransaction Commit a transaction for reporting.
DownloadBatch Download a single batch file.
RefundTransaction Refund a transaction.
VoidTransaction Void a transaction.

CommitTransaction

Commit a transaction for reporting.

Input
Name Type Required Description
TransactionCode String True Code of the transaction to commit.

DownloadBatch

Download a single batch file.

Input
Name Type Required Description
CompanyId String True The ID of the company that owns this batch
BatchId String True The ID of the batch object.
FileId String True The primary key of this batch file object.
FilePath String False The full path (including the file's name) where the file will be downloaded.
Result Set Columns
Name Type Description
FileData String The file's data encoded in Base64 will be outputted if FilePath and FileStream are not set.

RefundTransaction

Refund a transaction.

Input
Name Type Required Description
TransactionCode String True Code of the transaction to refund.
RefundTransactionCode String True The transaction code for the refund. This code will be saved to the ReturnInvoice transaction, and does not need to match the code of the original sale.
RefundDate String True The date of the refund.
RefundType String True Type of this refund. To submit a full refund, specify Full. To refund only specific lines from the transaction, specify Partial and indicate the lines you wish to apply in the refundLines field. To refund the tax that was paid in the previous transaction, specify TaxOnly. To issue a percentage-based discount, specify Percentage.
RefundPercentage String False The percentage for refund. This value only applies if you choose Percentage or Partial refund type.
RefundLines String False A comma separated list of the line numbers to refund for.
ReferenceCode String False A user-defined reference field containing information about this refund.

VoidTransaction

Void a transaction.

Input
Name Type Required Description
TransactionCode String True Code of the transaction to void.

System Tables

You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.

Schema Tables

The following tables return database metadata for Avalara:

Data Source Tables

The following tables return information about how to connect to and query the data source:

  • sys_connection_props: Returns information on the available connection properties.
  • sys_sqlinfo: Describes the SELECT queries that the connector can offload to the data source.

Query Information Tables

The following table returns query statistics for data modification queries:

  • sys_identity: Returns information about batch operations or single updates.

sys_catalogs

Lists the available databases.

The following query retrieves all databases determined by the connection string:

SELECT * FROM sys_catalogs
Columns
Name Type Description
CatalogName String The database name.

sys_schemas

Lists the available schemas.

The following query retrieves all available schemas:

SELECT * FROM sys_schemas
Columns
Name Type Description
CatalogName String The database name.
SchemaName String The schema name.

sys_tables

Lists the available tables.

The following query retrieves the available tables and views:

SELECT * FROM sys_tables
Columns
Name Type Description
CatalogName String The database containing the table or view.
SchemaName String The schema containing the table or view.
TableName String The name of the table or view.
TableType String The table type (table or view).
Description String A description of the table or view.
IsUpdateable Boolean Whether the table can be updated.

sys_tablecolumns

Describes the columns of the available tables and views.

The following query returns the columns and data types for the Transactions table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Transactions'
Columns
Name Type Description
CatalogName String The name of the database containing the table or view.
SchemaName String The schema containing the table or view.
TableName String The name of the table or view containing the column.
ColumnName String The column name.
DataTypeName String The data type name.
DataType Int32 An integer indicating the data type. This value is determined at run time based on the environment.
Length Int32 The storage size of the column.
DisplaySize Int32 The designated column's normal maximum width in characters.
NumericPrecision Int32 The maximum number of digits in numeric data. The column length in characters for character and date-time data.
NumericScale Int32 The column scale or number of digits to the right of the decimal point.
IsNullable Boolean Whether the column can contain null.
Description String A brief description of the column.
Ordinal Int32 The sequence number of the column.
IsAutoIncrement String Whether the column value is assigned in fixed increments.
IsGeneratedColumn String Whether the column is generated.
IsHidden Boolean Whether the column is hidden.
IsArray Boolean Whether the column is an array.
IsReadOnly Boolean Whether the column is read-only.
IsKey Boolean Indicates whether a field returned from sys_tablecolumns is the primary key of the table.

sys_procedures

Lists the available stored procedures.

The following query retrieves the available stored procedures:

SELECT * FROM sys_procedures
Columns
Name Type Description
CatalogName String The database containing the stored procedure.
SchemaName String The schema containing the stored procedure.
ProcedureName String The name of the stored procedure.
Description String A description of the stored procedure.
ProcedureType String The type of the procedure, such as PROCEDURE or FUNCTION.

sys_procedureparameters

Describes stored procedure parameters.

The following query returns information about all of the input parameters for the SelectEntries stored procedure:

SELECT * FROM sys_procedureparameters WHERE ProcedureName='SelectEntries' AND Direction=1 OR Direction=2
Columns
Name Type Description
CatalogName String The name of the database containing the stored procedure.
SchemaName String The name of the schema containing the stored procedure.
ProcedureName String The name of the stored procedure containing the parameter.
ColumnName String The name of the stored procedure parameter.
Direction Int32 An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters.
DataTypeName String The name of the data type.
DataType Int32 An integer indicating the data type. This value is determined at run time based on the environment.
Length Int32 The number of characters allowed for character data. The number of digits allowed for numeric data.
NumericPrecision Int32 The maximum precision for numeric data. The column length in characters for character and date-time data.
NumericScale Int32 The number of digits to the right of the decimal point in numeric data.
IsNullable Boolean Whether the parameter can contain null.
IsRequired Boolean Whether the parameter is required for execution of the procedure.
IsArray Boolean Whether the parameter is an array.
Description String The description of the parameter.
Ordinal Int32 The index of the parameter.

sys_keycolumns

Describes the primary and foreign keys.

The following query retrieves the primary key for the Transactions table:

SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Transactions'
Columns
Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
IsKey Boolean Whether the column is a primary key in the table referenced in the TableName field.
IsForeignKey Boolean Whether the column is a foreign key referenced in the TableName field.
PrimaryKeyName String The name of the primary key.
ForeignKeyName String The name of the foreign key.
ReferencedCatalogName String The database containing the primary key.
ReferencedSchemaName String The schema containing the primary key.
ReferencedTableName String The table containing the primary key.
ReferencedColumnName String The column name of the primary key.

sys_foreignkeys

Describes the foreign keys.

The following query retrieves all foreign keys which refer to other tables:

SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
Columns
Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
PrimaryKeyName String The name of the primary key.
ForeignKeyName String The name of the foreign key.
ReferencedCatalogName String The database containing the primary key.
ReferencedSchemaName String The schema containing the primary key.
ReferencedTableName String The table containing the primary key.
ReferencedColumnName String The column name of the primary key.
ForeignKeyType String Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key.

sys_primarykeys

Describes the primary keys.

The following query retrieves the primary keys from all tables and views:

SELECT * FROM sys_primarykeys
Columns
Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
KeySeq String The sequence number of the primary key.
KeyName String The name of the primary key.

sys_indexes

Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.

The following query retrieves all indexes that are not primary keys:

SELECT * FROM sys_indexes WHERE IsPrimary='false'
Columns
Name Type Description
CatalogName String The name of the database containing the index.
SchemaName String The name of the schema containing the index.
TableName String The name of the table containing the index.
IndexName String The index name.
ColumnName String The name of the column associated with the index.
IsUnique Boolean True if the index is unique. False otherwise.
IsPrimary Boolean True if the index is a primary key. False otherwise.
Type Int16 An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3).
SortOrder String The sort order: A for ascending or D for descending.
OrdinalPosition Int16 The sequence number of the column in the index.

sys_connection_props

Returns information on the available connection properties and those set in the connection string.

When querying this table, the config connection string should be used:

jdbc:cdata:avalara:config:

This connection string enables you to query this table without a valid connection.

The following query retrieves all connection properties that have been set in the connection string or set through a default value:

SELECT * FROM sys_connection_props WHERE Value <> ''
Columns
Name Type Description
Name String The name of the connection property.
ShortDescription String A brief description.
Type String The data type of the connection property.
Default String The default value if one is not explicitly set.
Values String A comma-separated list of possible values. A validation error is thrown if another value is specified.
Value String The value you set or a preconfigured default.
Required Boolean Whether the property is required to connect.
Category String The category of the connection property.
IsSessionProperty String Whether the property is a session property, used to save information about the current connection.
Sensitivity String The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms.
PropertyName String A camel-cased truncated form of the connection property name.
Ordinal Int32 The index of the parameter.
CatOrdinal Int32 The index of the parameter category.
Hierarchy String Shows dependent properties associated that need to be set alongside this one.
Visible Boolean Informs whether the property is visible in the connection UI.
ETC String Various miscellaneous information about the property.

sys_sqlinfo

Describes the SELECT query processing that the connector can offload to the data source.

Discovering the Data Source's SELECT Capabilities

Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.

Name Description Possible Values
AGGREGATE_FUNCTIONS Supported aggregation functions. AVG, COUNT, MAX, MIN, SUM, DISTINCT
COUNT Whether COUNT function is supported. YES, NO
IDENTIFIER_QUOTE_OPEN_CHAR The opening character used to escape an identifier. [
IDENTIFIER_QUOTE_CLOSE_CHAR The closing character used to escape an identifier. ]
SUPPORTED_OPERATORS A list of supported SQL operators. =, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR
GROUP_BY Whether GROUP BY is supported, and, if so, the degree of support. NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE
STRING_FUNCTIONS Supported string functions. LENGTH, CHAR, LOCATE, REPLACE, SUBSTRING, RTRIM, LTRIM, RIGHT, LEFT, UCASE, SPACE, SOUNDEX, LCASE, CONCAT, ASCII, REPEAT, OCTET, BIT, POSITION, INSERT, TRIM, UPPER, REGEXP, LOWER, DIFFERENCE, CHARACTER, SUBSTR, STR, REVERSE, PLAN, UUIDTOSTR, TRANSLATE, TRAILING, TO, STUFF, STRTOUUID, STRING, SPLIT, SORTKEY, SIMILAR, REPLICATE, PATINDEX, LPAD, LEN, LEADING, KEY, INSTR, INSERTSTR, HTML, GRAPHICAL, CONVERT, COLLATION, CHARINDEX, BYTE
NUMERIC_FUNCTIONS Supported numeric functions. ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, EXP, FLOOR, LOG, MOD, SIGN, SIN, SQRT, TAN, PI, RAND, DEGREES, LOG10, POWER, RADIANS, ROUND, TRUNCATE
TIMEDATE_FUNCTIONS Supported date/time functions. NOW, CURDATE, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, MONTH, QUARTER, WEEK, YEAR, CURTIME, HOUR, MINUTE, SECOND, TIMESTAMPADD, TIMESTAMPDIFF, DAYNAME, MONTHNAME, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT
REPLICATION_SKIP_TABLES Indicates tables skipped during replication.
REPLICATION_TIMECHECK_COLUMNS A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication.
IDENTIFIER_PATTERN String value indicating what string is valid for an identifier.
SUPPORT_TRANSACTION Indicates if the provider supports transactions such as commit and rollback. YES, NO
DIALECT Indicates the SQL dialect to use.
KEY_PROPERTIES Indicates the properties which identify the uniform database.
SUPPORTS_MULTIPLE_SCHEMAS Indicates if multiple schemas may exist for the provider. YES, NO
SUPPORTS_MULTIPLE_CATALOGS Indicates if multiple catalogs may exist for the provider. YES, NO
DATASYNCVERSION The Data Sync version needed to access this driver. Standard, Starter, Professional, Enterprise
DATASYNCCATEGORY The Data Sync category of this driver. Source, Destination, Cloud Destination
SUPPORTSENHANCEDSQL Whether enhanced SQL functionality beyond what is offered by the API is supported. TRUE, FALSE
SUPPORTS_BATCH_OPERATIONS Whether batch operations are supported. YES, NO
SQL_CAP All supported SQL capabilities for this driver. SELECT, INSERT, DELETE, UPDATE, TRANSACTIONS, ORDERBY, OAUTH, ASSIGNEDID, LIMIT, LIKE, BULKINSERT, COUNT, BULKDELETE, BULKUPDATE, GROUPBY, HAVING, AGGS, OFFSET, REPLICATE, COUNTDISTINCT, JOINS, DROP, CREATE, DISTINCT, INNERJOINS, SUBQUERIES, ALTER, MULTIPLESCHEMAS, GROUPBYNORELATION, OUTERJOINS, UNIONALL, UNION, UPSERT, GETDELETED, CROSSJOINS, GROUPBYCOLLATE, MULTIPLECATS, FULLOUTERJOIN, MERGE, JSONEXTRACT, BULKUPSERT, SUM, SUBQUERIESFULL, MIN, MAX, JOINSFULL, XMLEXTRACT, AVG, MULTISTATEMENTS, FOREIGNKEYS, CASE, LEFTJOINS, COMMAJOINS, WITH, LITERALS, RENAME, NESTEDTABLES, EXECUTE, BATCH, BASIC, INDEX
PREFERRED_CACHE_OPTIONS A string value specifies the preferred cacheOptions.
ENABLE_EF_ADVANCED_QUERY Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side. YES, NO
PSEUDO_COLUMNS A string array indicating the available pseudo columns.
MERGE_ALWAYS If the value is true, The Merge Mode is forcibly executed in Data Sync. TRUE, FALSE
REPLICATION_MIN_DATE_QUERY A select query to return the replicate start datetime.
REPLICATION_MIN_FUNCTION Allows a provider to specify the formula name to use for executing a server side min.
REPLICATION_START_DATE Allows a provider to specify a replicate startdate.
REPLICATION_MAX_DATE_QUERY A select query to return the replicate end datetime.
REPLICATION_MAX_FUNCTION Allows a provider to specify the formula name to use for executing a server side max.
IGNORE_INTERVALS_ON_INITIAL_REPLICATE A list of tables which will skip dividing the replicate into chunks on the initial replicate.
CHECKCACHE_USE_PARENTID Indicates whether the CheckCache statement should be done against the parent key column. TRUE, FALSE
CREATE_SCHEMA_PROCEDURES Indicates stored procedures that can be used for generating schema files.

The following query retrieves the operators that can be used in the WHERE clause:

SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'

Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.

Columns
Name Type Description
NAME String A component of SQL syntax, or a capability that can be processed on the server.
VALUE String Detail on the supported SQL or SQL syntax.

sys_identity

Returns information about attempted modifications.

The following query retrieves the Ids of the modified rows in a batch operation:

SELECT * FROM sys_identity
Columns
Name Type Description
Id String The database-generated ID returned from a data modification operation.
Batch String An identifier for the batch. 1 for a single operation.
Operation String The result of the operation in the batch: INSERTED, UPDATED, or DELETED.
Message String SUCCESS or an error message if the update in the batch failed.

sys_information

Describes the available system information.

The following query retrieves all columns:

SELECT * FROM sys_information
Columns
Name Type Description
Product String The name of the product.
Version String The version number of the product.
Datasource String The name of the datasource the product connects to.
NodeId String The unique identifier of the machine where the product is installed.
HelpURL String The URL to the product's help documentation.
License String The license information for the product. (If this information is not available, the field may be left blank or marked as 'N/A'.)
Location String The file path location where the product's library is stored.
Environment String The version of the environment or rumtine the product is currently running under.
DataSyncVersion String The tier of Sync required to use this connector.
DataSyncCategory String The category of Sync functionality (e.g., Source, Destination).

Advanced Configurations Properties

The advanced configurations properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure. Click the links for further details.

Authentication

Property Description
AuthScheme The scheme used for authentication. Accepted entries are Basic and LicenseKey.
UseSandbox A boolean indicating whether you are using a Sandbox account. The provider makes requests to the production environment by default.
User The Avalara user account used to authenticate.
Password The password used to authenticate the user.
AccountId The account ID number of your Avalara account.
LicenseKey The license key generated by an account administrator on the Avalara website.

Connection

Property Description
CompanyCode Only transaction information tied to this company code will be returned.

SSL

Property Description
SSLServerCert The certificate to be accepted from the server when connecting using TLS/SSL.

Schema

Property Description
Location A path to the directory that contains the schema files defining tables, views, and stored procedures.
BrowsableSchemas This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC.
Tables This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC.
Views Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC.

Miscellaneous

Property Description
GenerateSchemaFiles Indicates the user preference as to when schemas should be generated and saved.
IncludeAdjustedTransactions Whether or not to include adjusted transactions in the transaction listing.
MaxRows Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Other These hidden properties are used only in specific use cases.
Pagesize The maximum number of results to return per page from Avalara when using client paging.
PseudoColumns This property indicates whether or not to include pseudo columns as columns to the table.
Timeout The value in seconds until the timeout error is thrown, canceling the operation.
UserDefinedViews A filepath pointing to the JSON configuration file containing your custom views.

Authentication

This section provides a complete list of authentication properties you can configure.

Property Description
AuthScheme The scheme used for authentication. Accepted entries are Basic and LicenseKey.
UseSandbox A boolean indicating whether you are using a Sandbox account. The provider makes requests to the production environment by default.
User The Avalara user account used to authenticate.
Password The password used to authenticate the user.
AccountId The account ID number of your Avalara account.
LicenseKey The license key generated by an account administrator on the Avalara website.

AuthScheme

The scheme used for authentication. Accepted entries are Basic and LicenseKey.

Possible Values

Auto, Basic, LicenseKey

Data Type

string

Default Value

Auto

Remarks
  • Auto: Lets the driver decide automatically based on the other connection properties you have set.
  • Basic: Set this to use Basic User / Password authentication.
  • LicenseKey: Set to this to perform the authentication using the AccountId and the LicenseKey properties.

UseSandbox

A boolean indicating whether you are using a Sandbox account. The provider makes requests to the production environment by default.

Data Type

bool

Default Value

false

Remarks

If you are using a production account, this property can be left blank.

User

The Avalara user account used to authenticate.

Data Type

string

Default Value

""

Remarks

Together with Password, this field is used to authenticate against the Avalara server.

Password

The password used to authenticate the user.

Data Type

string

Default Value

""

Remarks

The User and Password are together used to authenticate with the server.

AccountId

The account ID number of your Avalara account.

Data Type

string

Default Value

""

Remarks

The Account ID is listed in the upper right hand corner of the Avalara admin console.

LicenseKey

The license key generated by an account administrator on the Avalara website.

Data Type

string

Default Value

""

Remarks

You can generate a license key as follows:

  1. Log into Avalara as an account administrator.
  2. Select Settings.
  3. Select Reset License Key.

Connection

This section provides a complete list of connection properties you can configure.

Property Description
CompanyCode Only transaction information tied to this company code will be returned.

CompanyCode

Only transaction information tied to this company code will be returned.

Data Type

string

Default Value

DEFAULT

Remarks

Only transaction information tied to this company code will be returned.

SSL

This section provides a complete list of SSL properties you can configure.

Property Description
SSLServerCert The certificate to be accepted from the server when connecting using TLS/SSL.

SSLServerCert

The certificate to be accepted from the server when connecting using TLS/SSL.

Data Type

string

Default Value

""

Remarks

If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.

This property can take the following forms:

Description Example
A full PEM Certificate (example shortened for brevity) -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
A path to a local file containing the certificate C:\\cert.cer
The public key (example shortened for brevity) -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----
The MD5 Thumbprint (hex values can also be either space or colon separated) ecadbdda5a1529c58a1e9e09828d70e4
The SHA1 Thumbprint (hex values can also be either space or colon separated) 34a929226ae0819f2ec14b4a3d904f801cbb150d

If not specified, any certificate trusted by the machine is accepted.

Certificates are validated as trusted by the machine based on the System's trust store. The trust store used is the 'javax.net.ssl.trustStore' value specified for the system. If no value is specified for this property, Java's default trust store is used (for example, JAVA_HOME\lib\security\cacerts).

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

Schema

This section provides a complete list of schema properties you can configure.

Property Description
Location A path to the directory that contains the schema files defining tables, views, and stored procedures.
BrowsableSchemas This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC.
Tables This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC.
Views Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC.

Location

A path to the directory that contains the schema files defining tables, views, and stored procedures.

Data Type

string

Default Value

%APPDATA%\Avalara Data Provider\Schema

Remarks

The path to a directory which contains the schema files for the connector (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.

If left unspecified, the default location is "%APPDATA%\Avalara Data Provider\Schema" with %APPDATA% being set to the user's configuration directory:

Platform %APPDATA%
Windows The value of the APPDATA environment variable
Mac ~/Library/Application Support
Linux ~/.config

BrowsableSchemas

This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Data Type

string

Default Value

""

Remarks

Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.

Tables

This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.

Data Type

string

Default Value

""

Remarks

Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the connector.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Views

Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Data Type

string

Default Value

""

Remarks

Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the connector.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Miscellaneous

This section provides a complete list of miscellaneous properties you can configure.

Property Description
GenerateSchemaFiles Indicates the user preference as to when schemas should be generated and saved.
IncludeAdjustedTransactions Whether or not to include adjusted transactions in the transaction listing.
MaxRows Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Other These hidden properties are used only in specific use cases.
Pagesize The maximum number of results to return per page from Avalara when using client paging.
PseudoColumns This property indicates whether or not to include pseudo columns as columns to the table.
Timeout The value in seconds until the timeout error is thrown, canceling the operation.
UserDefinedViews A filepath pointing to the JSON configuration file containing your custom views.

GenerateSchemaFiles

Indicates the user preference as to when schemas should be generated and saved.

Possible Values

Never, OnUse, OnStart, OnCreate

Data Type

string

Default Value

Never

Remarks

This property outputs schemas to .rsd files in the path specified by Location.

Available settings are the following:

  • Never: A schema file will never be generated.
  • OnUse: A schema file will be generated the first time a table is referenced, provided the schema file for the table does not already exist.
  • OnStart: A schema file will be generated at connection time for any tables that do not currently have a schema file.
  • OnCreate: A schema file will be generated by when running a CREATE TABLE SQL query.

Note that if you want to regenerate a file, you will first need to delete it.

Generate Schemas with SQL

When you set GenerateSchemaFiles to OnUse, the connector generates schemas as you execute SELECT queries. Schemas are generated for each table referenced in the query.

When you set GenerateSchemaFiles to OnCreate, schemas are only generated when a CREATE TABLE query is executed.

Generate Schemas on Connection

Another way to use this property is to obtain schemas for every table in your database when you connect. To do so, set GenerateSchemaFiles to OnStart and connect.

IncludeAdjustedTransactions

Whether or not to include adjusted transactions in the transaction listing.

Data Type

bool

Default Value

false

Remarks

Whether or not to include adjusted transactions in the transaction listing.

MaxRows

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

Data Type

int

Default Value

-1

Remarks

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

Other

These hidden properties are used only in specific use cases.

Data Type

string

Default Value

""

Remarks

The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.

Specify multiple properties in a semicolon-separated list.

Integration and Formatting
Property Description
DefaultColumnSize Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000.
ConvertDateTimeToGMT Determines whether to convert date-time values to GMT, instead of the local time of the machine.
RecordToFile=filename Records the underlying socket data transfer to the specified file.

Pagesize

The maximum number of results to return per page from Avalara when using client paging.

Data Type

string

Default Value

1000

Remarks

The Pagesize property affects the maximum number of results to return per page from Avalara when using client paging. A higher value will return more results per page, but may also cause a timeout exception. If you must use client paging on your server and have a fast server, setting a higher Pagesize may be desirable. We recommend testing various sizes against a large resultset to determine what works best in your use case.

PseudoColumns

This property indicates whether or not to include pseudo columns as columns to the table.

Data Type

string

Default Value

""

Remarks

This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".

Timeout

The value in seconds until the timeout error is thrown, canceling the operation.

Data Type

int

Default Value

60

Remarks

If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.

If Timeout expires and the operation is not yet complete, the connector throws an exception.

UserDefinedViews

A filepath pointing to the JSON configuration file containing your custom views.

Data Type

string

Default Value

""

Remarks

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This User Defined View configuration file is formatted as follows:

  • Each root element defines the name of a view.
  • Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM Transactions WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json

Note that the specified path is not embedded in quotation marks.