Adding a Connection to Avalara

To add a connection to Avalara:

1. In the application console, navigate to the Connections page.
2. At the Add Connections panel, select the icon for the connection you want to add.
3. If the Avalara icon is not available, click the Add More icon to download and install the Avalara connector from the CData site.

For required properties, see the Settings tab.

For connection properties that are not typically required, see the Advanced tab.

Connecting to Avalara

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

Authenticating 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

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.

This section details a selection of advanced features of the Avalara Sync App.

User Defined Views

The Sync App supports the use of user defined views, virtual tables whose contents are decided by a pre-configured user defined query. These views are useful when you cannot directly control queries being issued to the drivers. For an overview of creating and configuring custom views, see User Defined Views .

SSL Configuration

Use SSL Configuration to adjust how Sync App handles TLS/SSL certificate negotiations. You can choose from various certificate formats;. For further information, see the SSLServerCert property under "Connection String Options" .

Firewall and Proxy

Configure the Sync App for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.

Query Processing

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

For further information, see Query Processing.

Logging

For an overview of configuration settings that can be used to refine CData logging, see Logging. Only two connection properties are required for basic logging, but there are numerous features that support more refined logging, which enables you to use the LogModules connection property to specify subsets of information to be logged.

Customizing the SSL Configuration

By default, the Sync App attempts to negotiate TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

Connecting Through a Firewall or Proxy

HTTP Proxies

To authenticate to an HTTP proxy, set the following:

• ProxyServer: the hostname or IP address of the proxy server that you want to route HTTP traffic through.
• ProxyPort: the TCP port that the proxy server is running on.
• ProxyAuthScheme: the authentication method the Sync App uses when authenticating to the proxy server.
• ProxyUser: the username of a user account registered with the proxy server.
• ProxyPassword: the password associated with the ProxyUser.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

Overview

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

Key Features

• The Sync App 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 Sync App.

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.

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

Avalara Connector for CData Sync 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 table for AvalaraAvatax data provider.

Select

The Sync App 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 Sync App. 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 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 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 table for AvalaraAvatax data provider.

Select

The Sync App 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 Sync App. 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', '[email protected]', '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 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 Sync App 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 Sync App. 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 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 connection_id 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 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 table for AvalaraAvatax data provider.

Select

To retrieve records from FilingCalendar table, at least CompanyId should be specified in the WHERE clause. The Sync App 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 Sync App. 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 table for AvalaraAvatax data provider.

Select

The Sync App 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 Sync App. 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 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 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 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

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.

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 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 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 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 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 view for AvalaraAvatax data provider.

Select

The Sync App 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 Sync App. 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 table for AvalaraAvatax data provider.

Select

The Sync App 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 Sync App. 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 (tax_number) 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 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 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', '[email protected]', '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 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 for CData Sync 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.
PostalCodes	PostalCodes view for the Avalara Avatax data provider.
Regions	Regions view for the Avalara Avatax data provider.
ReportParameters	ReportParameters view for AvalaraAvatax data provider.
Reports	Reports table for AvalaraAvatax data provider.
Subscriptions	Subscriptions table for AvalaraAvatax data provider.
TaxRatesByAddress	Retrieve real-time estimated tax rates based on an address using the Avalara Avatax data provider.
TaxRatesByZipCode	Retrieve estimated tax rates by zip code for a specific date using the Avalara Avatax 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 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 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 Sync App 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 Sync App. 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 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 Sync App 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 Sync App. 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 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 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 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 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 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

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 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 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 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 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

PostalCodes view for the Avalara Avatax data provider.

Select

The Sync App 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 Sync App.

• IncludeExpiredPostalCodes supports the '=' comparison operator.

For example, the following query is processed server side:

SELECT * FROM PostalCodes WHERE EffectiveDate >= '2018-09-01' AND EffectiveDate <= '2022-02-01' AND Region='NY' AND TaxRegionId  IN ('4001027','4001022')
SELECT * FROM PostalCodes WHERE IncludeExpiredPostalCodes = true

Columns

Name	Type	Description
PostalCode [KEY]	String	The postal code.
Country	String	The country in which this postal code is located.
Region	String	The region, state, or province where this postal code is located.
TaxRegionId	Int	An Avalara-assigned tax region Id associated with the postal code.
EffectiveDate	Date	The date the postal code becomes active.
EndDate	Date	The date the postal code expires.
IncludeExpiredPostalCodes	Bool	If set to true, returns expired postal codes. Defaults to false.

Regions view for the Avalara Avatax data provider.

Select

The Sync App will use the Avalara API to process WHERE clause conditions built with the following columns and operators. Most columns except for LocalizedNames 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 Sync App. For example, the following query is processed server side:

SELECT * FROM Regions where CountryCode='US' AND IsRegionTaxable=false AND Code!='MT' AND (Name LIKE '%KA' OR NAME LIKE 'OR%')

Columns

Name	Type	Description
CountryCode	String	The two-character ISO 3166 country code this region belongs to.
Code	String	The three-character ISO 3166 region code.
Name	String	The full name, in uppercase, using localized characters specific to this region.
LocalizedNames	String	A list of localized names in various languages.
Classification	String	The term used in the local language that describes this region.
IsRegionTaxable	Bool	Indicates whether the region collects tax. This field is only populated for the US and is null for all other countries.
StreamlinedSalesTax	Bool	In the US, this flag indicates whether a state participates in the Streamlined Sales Tax program. For countries outside the US, this flag is null.

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 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 table for AvalaraAvatax data provider.

Select

The Sync App 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 Sync App. 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

Retrieve real-time estimated tax rates based on an address using the Avalara Avatax data provider.

Select

The Sync App 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 Sync App.

• PostalCode supports the '=' comparison operator.
• Region supports the '=' comparison operator.
• City supports the '=' comparison operator.
• Line1 supports the '=' comparison operator.
• Line2 supports the '=' comparison operator.
• Line3 supports the '=' comparison operator.

For example, the following query is processed server side:

SELECT * FROM TaxRatesByAddress WHERE PostalCode='99950' AND Region='AK' AND City='KETCHIKAN' AND Line1='Whale Pass'

Columns

Name	Type	Description
Rate	Decimal	The sales tax rate for general tangible personal property in this jurisdiction.
Name	String	A readable name of the tax or taxing jurisdiction related to this tax rate.
Type	String	The type of jurisdiction associated with this tax rate.
PostalCode	String	The postal code of the location.
Region	String	Name or ISO 3166 code identifying the region within the country.
City	String	The city name of the location.
Line1	String	The street address of the location.
Line2	String	The street address of the location.
Line3	String	The street address of the location.

Retrieve estimated tax rates by zip code for a specific date using the Avalara Avatax data provider.

Select

The Sync App 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 Sync App.

• StateAbbreviation supports the '=' comparison operator.
• Date supports the '=' comparison operator.

For example, the following query is processed server side:

SELECT * FROM TaxRatesByZipCode WHERE StateAbbreviation='NY' AND Date='2020-01-01'

Columns

Name	Type	Description
ZipCode [KEY]	String	The five digit zip code for this record.
StateAbbreviation	String	A valid two-character US state abbreviation for this record.
CountyName	String	A valid county name for this record. Zip codes may span multiple counties.
CityName	String	A valid city name for this record. Zip codes may span multiple cities.
StateSalesTax	Decimal	The state component of the sales tax rate.
StateUseTax	Decimal	The state component of the use tax rate.
CountySalesTax	Decimal	The county component of the sales tax rate.
CountyUseTax	Decimal	The county component of the use tax rate.
CitySalesTax	Decimal	The city component of the sales tax rate.
CityUseTax	Decimal	The city component of the use tax rate.
TotalSalesTax	Decimal	The total sales tax rate for this postal code. This value may differ from the sum of state, county, and city taxes due to special jurisdiction rules.
TotalUseTax	Decimal	The total use tax rate for this postal code. This value may differ from the sum of state, county, and city rates due to special tax jurisdiction rules.
TaxShippingAlone	Bool	Whether shipping is taxable.
TaxShippingAndHandlingTogether	Bool	Whether shipping and handling are taxable when sent together.
Date	Date	The date for which the point-of-sale data is calculated. Defaults to today.

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 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 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 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 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 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 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.

The connection string 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 in the connection string for this provider. Click the links for further details.

For more information on establishing a connection, see Establishing a Connection.

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	Specifies the user ID of the authenticating Avalara user account.
Password	Specifies the password of the authenticating user account.
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	Specifies the certificate to be accepted from the server when connecting using TLS/SSL.

Firewall

---

Property	Description
FirewallType	Specifies the protocol the provider uses to tunnel traffic through a proxy-based firewall.
FirewallServer	Identifies the IP address, DNS name, or host name of a proxy used to traverse a firewall and relay user queries to network resources.
FirewallPort	Specifies the TCP port to be used for a proxy-based firewall.
FirewallUser	Identifies the user ID of the account authenticating to a proxy-based firewall.
FirewallPassword	Specifies the password of the user account authenticating to a proxy-based firewall.

Proxy

---

Property	Description
ProxyAutoDetect	Specifies whether the provider checks your system proxy settings for existing proxy server configurations, rather than using a manually specified proxy server.
ProxyServer	The hostname or IP address of the proxy server that you want to route HTTP traffic through.
ProxyPort	The TCP port on your specified proxy server (set in the ProxyServer connection property) that has been reserved for routing HTTP traffic to and from the client.
ProxyAuthScheme	Specifies the authentication method the provider uses when authenticating to the proxy server specified in the ProxyServer connection property.
ProxyUser	The username of a user account registered with the proxy server specified in the ProxyServer connection property.
ProxyPassword	The password associated with the user specified in the ProxyUser connection property.
ProxySSLType	The SSL type to use when connecting to the proxy server specified in the ProxyServer connection property.
ProxyExceptions	A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the proxy server set in the ProxyServer connection property.

Logging

---

Property	Description
LogModules	Specifies the core modules to include in the log file. Use a semicolon-separated list of module names. By default, all modules are logged.

Schema

---

Property	Description
Location	Specifies the location of a directory containing schema files that define tables, views, and stored procedures. Depending on your service's requirements, this may be expressed as either an absolute path or a relative path.
BrowsableSchemas	Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC .
Tables	Optional setting that restricts the tables reported to a subset of all available tables. For example, Tables=TableA,TableB,TableC .
Views	Optional setting that 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	Specifies the maximum rows returned for queries without aggregation or GROUP BY.
Other	Specifies additional hidden properties for specific use cases. These are not required for typical provider functionality. Use a semicolon-separated list to define multiple properties.
Pagesize	The maximum number of results to return per page from Avalara when using client paging.
PseudoColumns	Specifies the pseudocolumns to expose as table columns. Use the format 'TableName=ColumnName;TableName=ColumnName'. The default is an empty string, which disables this property.
Timeout	Specifies the maximum time, in seconds, that the provider waits for a server response before throwing a timeout error. The default is 60 seconds. Set to 0 to disable the timeout.
UserDefinedViews	Specifies a filepath to a JSON configuration file defining custom views. The provider automatically detects and uses the views specified in this file.

This section provides a complete list of the Authentication properties you can configure in the connection string for this provider.

---

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	Specifies the user ID of the authenticating Avalara user account.
Password	Specifies the password of the authenticating user account.
AccountId	The account ID number of your Avalara account.
LicenseKey	The license key generated by an account administrator on the Avalara website.

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

Remarks

• Basic: Set this to use Basic User / Password authentication.
• LicenseKey: Set to this to perform the authentication using the AccountId and the LicenseKey properties.

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

Remarks

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

Specifies the user ID of the authenticating Avalara user account.

Remarks

The authenticating server requires both User and Password to validate the user's identity.

Specifies the password of the authenticating user account.

Remarks

The authenticating server requires both User and Password to validate the user's identity.

The account ID number of your Avalara account.

Remarks

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

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

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.

This section provides a complete list of the Connection properties you can configure in the connection string for this provider.

---

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

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

Remarks

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

This section provides a complete list of the SSL properties you can configure in the connection string for this provider.

---

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

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

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.

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

This section provides a complete list of the Firewall properties you can configure in the connection string for this provider.

---

Property	Description
FirewallType	Specifies the protocol the provider uses to tunnel traffic through a proxy-based firewall.
FirewallServer	Identifies the IP address, DNS name, or host name of a proxy used to traverse a firewall and relay user queries to network resources.
FirewallPort	Specifies the TCP port to be used for a proxy-based firewall.
FirewallUser	Identifies the user ID of the account authenticating to a proxy-based firewall.
FirewallPassword	Specifies the password of the user account authenticating to a proxy-based firewall.