Avalara Connection Details¶
Introduction¶
Connector Version
This documentation is based on version 23.0.8936 of the connector.
Get Started¶
Avalara Version Support
The connector leverages the Avalara Avatax API to enable bidirectional access to Avalara.
Establish a Connection¶
Connect to Avalara¶
By default, the connector connects to a production environment. Set UseSandbox
to true
to configure the connector to connect to a Avalara sandbox environment.
Authenticate to Avalara¶
There are two authentication methods available for connecting to the Avalara data source: Basic and LicenseKey.
Login Credentials¶
To use login credentials for authentication, set the following:
AuthScheme
: Set this toBasic
.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 toLicenseKey
.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 toSettings > Reset License Key
.
Fine-Tuning Data Access¶
Fine Tuning Data Access¶
You can use the following properties to gain more control over the data returned from Avalara:
CompanyCode
: Only transaction information tied to this company code will be returned.IncludeAdjustedTransactions
: Set this totrue
to include adjusted transactions in the transaction listing.
Important Notes¶
Configuration Files and Their Paths¶
- All references to adding configuration files and their paths refer to files and locations on the Harmony Agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.
Advanced Features¶
This section details a selection of advanced features of the Avalara connector.
User Defined Views
The connector allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.
SSL Configuration
Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert
property under "Connection String Options" for more information.
Proxy
To configure the connector using Private Agent proxy settings, select the Use Proxy Settings
checkbox on the connection configuration screen.
Query Processing
The connector offloads as much of the SELECT statement processing as possible to Avalara and then processes the rest of the query in memory (client-side).
See Query Processing for more information.
User Defined Views¶
The Avalara connector allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.
There are two ways to create user defined views:
- Create a JSON-formatted configuration file defining the views you want.
- DDL statements.
Define Views Using a Configuration File¶
User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json
. The connector automatically detects the views specified in this file.
You can also have multiple view definitions and control them using the UserDefinedViews
connection property. When you use this property, only the specified views are seen by the connector.
This User Defined View configuration file is formatted as follows:
- Each root element defines the name of a view.
- Each root element contains a child element, called
query
, which contains the custom SQL query for the view.
For example:
{
"MyView": {
"query": "SELECT * FROM Transactions WHERE MyColumn = 'value'"
},
"MyView2": {
"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
}
}
Use the UserDefinedViews
connection property to specify the location of your JSON configuration file. For example:
"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"
Define Views Using DDL Statements¶
The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.
Create a View¶
To create a new view using DDL statements, provide the view name and query as follows:
CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;
If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews
connection property.
Alter a View¶
To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:
ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';
The view is then updated in the JSON configuration file.
Drop a View¶
To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.
DROP LOCAL VIEW [MyViewName]
This removes the view from the JSON configuration file. It can no longer be queried.
Schema for User Defined Views¶
User Defined Views are exposed in the UserViews
schema by default. This is done to avoid the view's name clashing with an actual entity in the data model. You can change the name of the schema used for UserViews by setting the UserViewsSchemaName
property.
Work with User Defined Views¶
For example, a SQL statement with a User Defined View called UserViews.RCustomers
only lists customers in Raleigh:
SELECT * FROM Customers WHERE City = 'Raleigh';
An example of a query to the driver:
SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';
Resulting in the effective query to the source:
SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';
That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.
SSL Configuration¶
Customize the SSL Configuration¶
By default, the connector attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.
To specify another certificate, see the SSLServerCert
property for the available formats to do so.
Data Model¶
Overview
This section shows the available API objects and provides more information on executing SQL to Avalara APIs.
Key Features
- The connector models Avalara entities as relational views, allowing you to write SQL to query Avalara data.
- Stored procedures allow you to execute operations to Avalara.
- Live connectivity to these objects means any changes to your Avalara account are immediately reflected when using the connector.
Tables
Tables describes the available tables. Tables are statically defined to model Accounts, Transactions, Users, and more.
Views
Views describes the available views. Views are statically defined to model Reports, Subscriptions, and more.
Stored Procedures
Stored Procedures are function-like interfaces to Avalara. Stored procedures allow you to execute operations to Avalara, including downloading documents and moving envelopes.
Tables¶
The connector models the data in Avalara as a list of tables in a relational database that can be queried using standard SQL statements.
Avalara Connector Tables¶
Name | Description |
---|---|
Accounts | Accounts table for AvalaraAvatax data provider. |
Batches | Batches table for AvalaraAvatax data provider. |
Companies | Companies table for AvalaraAvatax data provider. |
Contacts | Contacts table for AvalaraAvatax data provider. |
Customers | Customers table for AvalaraAvatax data provider. |
DataSources | DataSources table for AvalaraAvatax data provider. |
DistanceThresholds | DistanceThresholds table for AvalaraAvatax data provider. |
FilingCalendars | FilingCalendars table for AvalaraAvatax data provider. |
Items | Items table for AvalaraAvatax data provider. |
Locations | Locations table for AvalaraAvatax data provider. |
MultiDocument | MultiDocument table for AvalaraAvatax data provider. |
Nexus | Nexus table for AvalaraAvatax data provider. |
Notices | Notices table for AvalaraAvatax data provider. |
Notifications | Notifications table for AvalaraAvatax data provider. |
Settings | Settings table for AvalaraAvatax data provider. |
TaxCodes | TaxCodes table for AvalaraAvatax data provider. |
TaxRules | TaxRules table for AvalaraAvatax data provider. |
TransactionLines | TransactionLines view for AvalaraAvatax data provider. |
Transactions | Transactions table for AvalaraAvatax data provider. |
Upcs | Upcs table for AvalaraAvatax data provider. |
Users | Users table for AvalaraAvatax data provider. |
Accounts¶
Accounts table for AvalaraAvatax data provider.
Select¶
The connector will use the Avalara API to process WHERE clause conditions built with the following columns and operators. Most of the columns support server side filtering with the following operators: =,>,<,<=,>=,IN,NOT,IN,AND,OR,LIKE. The rest of the filter is executed client side within the connector. For example, the following query is processed server side:
SELECT * FROM Accounts WHERE accountStatusId = 'Active' AND accountTypeId = 'Regular1'
Insert¶
To add an Account.
INSERT INTO Accounts (id, name, effectiveDate, accountStatusId, accountTypeId) VALUES ('12345', 'Test Account', '2019-06-24T00:00:00+00:00', 'Test', 'Regular')
Update¶
Avalara allows updates Accounts columns.
UPDATE Accounts SET crmid = '0010b000Ardit' WHERE ID = 2000137504
In order to update AccountsSubscriptions #TEMP table should be used.
INSERT INTO AccountsSubscriptions#TEMP (subscriptionDescription, id) VALUES ('Avacert2', '567961')
UPDATE Accounts SET linkedSubscriptions = 'AccountsSubscriptions#TEMP', crmid = '0010b000Ardit' WHERE ID = 2000137504
Delete¶
Accounts can be deleted by providing the ID of the address and issuing a DELETE statement.
DELETE FROM Accounts WHERE ID = 100
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Id [KEY] | Int | False | The unique ID number assigned to this account. |
ModifiedUserId | Int | False | The user ID of the user who last modified this record. |
CreatedUserId | Int | False | The User ID of the user who created this record. |
AccountTypeId | String | False | The type of this account. |
AccountStatusId | String | False | The current status of this account. |
CrmId | String | False | For system registrar use only. |
LinkedSubscriptions | String | False | A list of subscriptions granted to this account. |
Name | String | False | The name of this account. |
LinkedUsers | String | False | A list of all the users belonging to this account. |
ModifiedDate | Datetime | False | The date/time when this record was last modified. |
EndDate | Datetime | False | If this account has been closed, this is the last date the account was open. |
EffectiveDate | Datetime | False | The earliest date on which this account may be used. |
CreatedDate | Datetime | False | The date when this record was created. |
PageKey | String | False |
Batches¶
Batches table for AvalaraAvatax data provider.
Insert¶
Create batch objects attached to a company. Each batch object must have one file object. When a batch is created, it is added to the AvaTax Batch Queue and will be processed as quickly as possible in the order it was received. As a result of this operation you will get the status of a batch. First insert the file, referencing the local path of the file on your machine, using #TEMP table:
INSERT INTO File#TEMP (Name, FileLocalPath, ContentType) VALUES ('samplebatch.csv', 'C:\Users\User\Desktop\BatchFile.csv', 'text/csv')
Then insert in the main Batches table. The following fielda are required:
INSERT INTO Batches (CompanyId, Name, LinkedFiles, Type) VALUES ('247015', 'TestBatch2', 'File#TEMP', 'TransactionImport')
Update¶
UPDATE operation is not supported for Batches table.
Delete¶
If you attempt to delete a batch that is being processed, you will receive an error message. Deleting a batch does not delete any transactions that were created by importing the batch. Deleting a batch requires one of the following user roles: CSPAdmin, CSPTester, SSTAdmin, SystemAdmin, SystemOperator, TechnicalSupportAdmin; Companyid and ID should be specified in the WHERE clause.
DELETE FROM Batches WHERE ID = 9030440 AND CompanyId = 247015
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Id [KEY] | Int | False | The unique ID number of this batch. |
CompanyId [KEY] | Int | False | The Company ID number of the company that owns this batch. |
BatchAgent | String | False | The agent used to create this batch. |
CurrentRecord | Int | False | The current record being processed |
CompletedDate | Datetime | False | The date/time when this batch was completely processed. |
LinkedFiles | String | False | The list of files contained in this batch. |
Name | String | False | The user-friendly readable name for this batch. |
ModifiedUserId | Int | False | The user ID of the user who last modified this record. |
CreatedUserId | Int | False | The User ID of the user who created this record. |
Status | String | False | This batch's current processing status. |
RecordCount | Int | False | The number of records in this batch; determined by the server. |
ModifiedDate | Datetime | False | The date/time when this record was last modified. |
AccountId | Int | False | The Account ID number of the account that owns this batch. |
Type | String | False | The type of this batch.Possible values are:ItemImport, CompanyLocationImport, ExemptCertImport, TaxRuleImport, TransactionImport, UPCBulkImport. |
StartedDate | Datetime | False | The date/time when this batch started processing. |
CreatedDate | Datetime | False | The date when this record was created. |
Options | String | False | Any optional flags provided for this batch. |
PageKey | String | False |
Companies¶
Companies table for AvalaraAvatax data provider.
Insert¶
To insert a new company to an existing account you need to specify AccountId, CompanyCode, Name and DefaultCountry fields.
INSERT INTO Companies (AccountId, CompanyCode, Name, defaultCountry) VALUES ('2000137504', 'SAB', 'SAB&Co', 'US')
Update¶
Avalara allows UPDATEs on Companies columns, expect for aggregate columns. Aggregate columns can not be updated.
UPDATE Companies SET Name = 'UpdatedName' WHERE ID = 283040
Delete¶
A company can be deleted by providing the ID of the company and issuing a DELETE statement.
DELETE FROM Companies WHERE ID = 283040
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Id [KEY] | Int | False | The unique ID number of this company. |
DefaultLocationId | Int | False | If set, this is the unique ID number of the default location for this company. |
LinkedNexus | String | False | Optional: A list of nexus defined for this company. To fetch this list, add the query string `?$include=Nexus` to your URL. |
NonReportingChildCompanies | String | False | |
TaxDependencyLevelId | String | False | Used to apply tax detail dependency at a jurisdiction level. |
DefaultCountry | String | False | The two character ISO-3166 country code of the default country for this company. |
LinkedTaxRules | String | False | Optional: A list of tax rules defined for this company. |
Name | String | False | The name of this company, as shown to customers. |
CreatedDate | Datetime | False | The date when this record was created. |
MossCountry | String | False | The country code of the mini-one-stop-shop used for Value Added Tax (VAT) processing. |
LinkedContacts | String | False | Optional: A list of contacts defined for this company. |
CompanyCode | String | False | A unique code that references this company within your account. |
WarningsEnabled | Bool | False | Set this value to true to receive warnings in API calls via SOAP. |
LinkedLocations | String | False | Optional: A list of locations defined for this company. |
LinkedItems | String | False | Optional: A list of items defined for this company. |
CreatedUserId | Int | False | The User ID of the user who created this record. |
AccountId | Int | False | The unique ID number of the account this company belongs to. |
ModifiedDate | Datetime | False | The date/time when this record was last modified. |
RoundingLevelId | String | False | Indicates whether this company prefers to round amounts at the document level or line level. |
BusinessIdentificationNo | String | False | Business Identification No. |
LinkedUpcs | String | False | Optional: A list of UPCs defined for this company. |
InProgress | Bool | False | Set this value to true to indicate that you are still working to finish configuring this company. While this value is true, no tax reporting will occur and the company will not be usable for transactions. |
IsReportingEntity | Bool | False | Set this flag to true if this company must file its own tax returns. For users who have Returns enabled, this flag turns on monthly Worksheet generation for the company. |
LinkedTaxCodes | String | False | Optional: A list of tax codes defined for this company. |
ParentCompanyId | Int | False | If this company is fully owned by another company, this is the unique identity of the parent company. |
BaseCurrencyCode | String | False | This is the three character ISO-4217 currency code of the default currency used by this company. |
IsTest | Bool | False | Set this flag to true to indicate that this company is a test company. If you have Returns enabled, Test companies will not file tax returns and can be used for validation purposes. |
IsActive | Bool | False | This flag indicates whether tax activity can occur for this company. Set this flag to true to permit the company to process transactions. |
SstEffectiveDate | Date | False | If this company participates in Streamlined Sales Tax, this is the date when the company joined the SST program. |
MossId | String | False | The unique identifier of the mini-one-stop-shop used for Value Added Tax (VAT) processing. |
ModifiedUserId | Int | False | The user ID of the user who last modified this record. |
LinkedSettings | String | False | Optional: A list of settings defined for this company. |
SstPid | String | False | If this company files Streamlined Sales Tax, this is the PID of this company as defined by the Streamlined Sales Tax governing board. |
TaxpayerIdNumber | String | False | For United States companies, this field contains your Taxpayer Identification Number. This is a nine digit number that is usually called an EIN for an Employer Identification Number if this company is a corporation, or SSN for a Social Security Number if this company is a person. This value is required if the address provided is inside the US and if you subscribed to the Avalara Managed Returns or SST Certified Service Provider service. Otherwise it is optional. |
IsDefault | Bool | False | This flag is true if this company is the default company for this account. Only one company may be set as the default. |
HasProfile | Bool | False | Set this flag to true to give this company its own unique tax profile. If this flag is true, this company will have its own Nexus, TaxRule, TaxCode, and Item definitions. If this flag is false, this company will inherit all profile values from its parent. |
Contacts¶
Contacts table for AvalaraAvatax data provider.
Select¶
The connector will use the Avalara API to process WHERE clause conditions built with the following columns and operators. Most of the columns support server side filtering with the following operators: =,>,<,<=,>=,IN,NOT,IN,AND,OR,LIKE. The rest of the filter is executed client side within the connector. For example, the following query is processed server side:
SELECT * FROM Contacts WHERE companyid = 247015
SELECT * FROM Contacts WHERE companyid = 247015 AND region = 'NY'
Insert¶
To add a Contact, ContactCode is a required field.
INSERT INTO Contacts (companyId, contactCode, firstName, lastName, title, line1, city, region, postalCode, country, email, phone) VALUES ('247015', 'TestContactc7356131ca2442', " +
"'Bob', 'McExample', 'Owner', '2000 Main Street', 'Irvine', 'CA', '92614', 'US', 'bob@example.org', '714 555-1212')
Update¶
Avalara allows updates Contacts columns. To update a Contact, ID and CompanyId should be specified in the WHERE clause of the Update statement.
UPDATE Contacts SET firstName = 'tidra' WHERE CompanyId = 247015 AND ID = 175596
Delete¶
Contacts can be deleted by providing the Companyid and ID of the Contact and issuing a DELETE statement.
DELETE FROM Contacts WHERE ID = 100
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Id [KEY] | Int | False | The unique ID number of this contact. |
FirstName | String | False | The first or given name of this contact. |
CreatedUserId | Int | False | The User ID of the user who created this record. |
Mobile | String | False | The mobile phone number for this contact. |
Region | String | False | Name or ISO 3166 code identifying the region within the country. |
ModifiedDate | Datetime | False | The date/time when this record was last modified. |
Phone | String | False | The main phone number for this contact. |
Line3 | String | False | The third line of the postal mailing address of this contact. |
PostalCode | String | False | The postal code or zip code of the postal mailing address of this contact. |
Line2 | String | False | The second line of the postal mailing address of this contact. |
Line1 | String | False | The first line of the postal mailing address of this contact. |
LastName | String | False | The last or family name of this contact. |
ModifiedUserId | Int | False | The user ID of the user who last modified this record. |
CompanyId [KEY] | Int | False | The unique ID number of the company to which this contact belongs. |
Email | String | False | The email address of this contact. |
Country | String | False | Name or ISO 3166 code identifying the country. |
City | String | False | The city of the postal mailing address of this contact. |
MiddleName | String | False | The middle name of this contact. |
Fax | String | False | The facsimile phone number for this contact. |
CreatedDate | Datetime | False | The date when this record was created. |
Title | String | False | Professional title of this contact. |
ContactCode | String | False | A unique code for this contact. |
PageKey | String | False |
Customers¶
Customers table for AvalaraAvatax data provider.
Select¶
To retrieve Customer records from Customers table, at least CompanyId or CompanyId and CustomerCode should be specified in the WHERE clause. The connector will use the Avalara API to process WHERE clause conditions built with the following columns and operators. CompanyId and CustomerCode fields support server side filtering with the = operator. The rest of the filter is executed client side within the connector. For example, the following query is processed server side:
SELECT * FROM Customers WHERE CompanyId = '247015'
SELECT * FROM Contacts WHERE CompanyId = 247015 AND CustomerCode = 'CC001'
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Id | Int | False | Unique ID number of this customer. |
CompanyId [KEY] | Int | False | The unique ID number of the AvaTax company that recorded this customer. |
CustomerCode [KEY] | String | False | The unique code identifying this customer. Must be unique within your company. |
AlternateId | String | False | A customer-configurable alternate ID number for this customer. You may set this value to match any other system that would like to reference this customer record. |
IsShip | Bool | False | True if this customer record is specifically used for ship-to purposes. |
LinkedShipTos | String | False | A list of ship-to customer records that are connected to this bill-to customer. |
Region | String | False | ISO 3166 code identifying the region within the country. |
IsBill | Bool | False | True if this customer record is specifically used for bill-to purposes. |
EmailAddress | String | False | The main email address for this customer. |
LinkedCustomFields | String | False | A list of custom fields defined on this customer. |
TaxpayerIdNumber | String | False | For customers in the United States, this field is the federal taxpayer ID number. For businesses, this is a Federal Employer Identification Number. For individuals, this will be a Social Security Number. |
ModifiedDate | Datetime | False | The date/time when this record was last modified. |
LinkedCertificates | String | False | A list of exemption certficates that apply to this customer. |
FaxNumber | String | False | The fax phone number for this customer, if any. |
AttnName | String | False | Indicates the 'Attn' component of the address for this customer, if this customer requires mailings to be shipped to the attention of a specific person or department name. |
PhoneNumber | String | False | The main phone number for this customer. |
PostalCode | String | False | Postal Code / Zip Code component of the address of this customer. |
Line2 | String | False | Second line of the street address of this customer. |
Line1 | String | False | First line of the street address of this customer. |
LastTransaction | Datetime | False | Date when this customer last executed a transaction. |
Country | String | False | Name or ISO 3166 code identifying the country. |
City | String | False | City component of the street address of this customer. |
LinkedExposureZones | String | False | A list of exposure zones where you do business with this customer. |
ContactName | String | False | The name of the main contact person for this customer. |
CreatedDate | Datetime | False | The date when this record was created. |
Name | String | False | A friendly name identifying this customer. |
DataSources¶
DataSources table for AvalaraAvatax data provider.
Insert¶
To create a data source at least CompanyId and Source should be specified.
INSERT INTO DataSources (CompanyId, Source) VALUES (247015, 'Amazon v2 Extractor')
Update¶
To update a data source, you need to specify ID and CompanyId in WHERE clause.
UPDATE DataSources SET isEnabled = true WHERE ID = 297 AND CompanyId = 247015
Delete¶
To delete a user, you need to specify ID and AccountId in WHERE clause.
DELETE FROM DataSources WHERE ID = 297 AND CompanyId = 247015
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Id [KEY] | Int | False | The ID of the datasource. |
CompanyId [KEY] | Int | False | The ID of the company to which the datasource belongs to. |
Source | String | False | The extractor/connector id. |
ModifiedUserId | Int | False | The user ID of the user who last modified this record. |
CreatedUserId | Int | False | The User ID of the user who created this record. |
ModifiedDate | Datetime | False | The date/time when this record was last modified. |
IsAuthorized | Bool | False | True if this data source is authorized. |
LastSyncedDate | Datetime | False | The date when the information was last synched. |
IsSynced | Bool | False | If all the information has been transferred from the extractor to the database. |
DeletedDate | Datetime | False | The date when this record was deleted. |
IsEnabled | Bool | False | The connection using the connectionMyUserd is enabled. The customer is responsible to enable or disable. |
Instance | String | False | The unique ID number of this connection. |
CreatedDate | Datetime | False | The date when this record was created. |
Recalculate | Bool | False | Specifies whether transactions created by this data source needs to re-calculate tax or not |
PageKey | String | False |
DistanceThresholds¶
DistanceThresholds table for AvalaraAvatax data provider.
A company-distance-threshold model indicates the distance between a company and the taxing borders of various countries. Distance thresholds are necessary to correctly calculate some value-added taxes.
Insert¶
To create a distance-threshold object you need one of the following user roles: AccountAdmin, CompanyAdmin, CSPAdmin, CSPTester, SiteAdmin, SSTAdmin, SystemAdmin, TechnicalSupportAdmin. At least the following fields should be specified:
INSERT INTO DistanceThresholds (companyId, originCountry, destinationCountry, type) VALUES (283040, 'FR', 'BR', 'Purchase')
Update¶
To update a distance-threshold, you need to specify ID and CompanyId in WHERE clause.
UPDATE DistanceThresholds SET EffDate = '2019/11/08' WHERE ID = 11785 AND CompanyId = 283040
Delete¶
To delete a distance-threshold, you need to specify ID and AccountId in WHERE clause.
DELETE FROM DistanceThresholds WHERE ID = 11785 AND CompanyId = 283040
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Id [KEY] | Int | False | A unique ID number representing this distance threshold object. |
CompanyId [KEY] | Int | False | The ID number of the company that defined this distance threshold. |
OriginCountry | String | False | The origin country for this threshold. |
DestinationCountry | String | False | The destination country for this threshold. |
Type | String | False | Indicates the distance threshold type. This value can be either `Sale` or `Purchase`. |
EffDate | Datetime | False | For distance threshold values that change over time, this is the earliest date for which this distance threshold is valid. If null, this distance threshold is valid for all dates earlier than the `endDate` field. |
EndDate | Datetime | False | For distance threshold values that change over time, this is the latest date for which this distance threshold is valid. If null, this distance threshold is valid for all dates later than the `effDate` field. |
ThresholdExceeded | Bool | False | True if your tax professional has determined that the value-added tax distance threshold is exceeded for this pair of countries. If you set this value to `false`, your value added taxes will be calculated using the origin country. Otherwise, value added taxes will be calculated on the destination country. |
PageKey | String | False |
FilingCalendars¶
FilingCalendars table for AvalaraAvatax data provider.
Select¶
To retrieve records from FilingCalendar table, at least CompanyId should be specified in the WHERE clause. The connector will use the Avalara API to process WHERE clause conditions built with the following columns and operators. Most of the columns support server side filtering with the following operators: =,>,<,<=,>=,IN,NOT,IN,AND,OR,LIKE. The rest of the filter is executed client side within the connector. For example, the following query is processed server side:
SELECT * FROM FilingCalendars WHERE CompanyId = '247015' AND Success = False
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
CompanyId [KEY] | Int | False | CompanyId of the request |
ActivePoa | Bool | False | Notes if there is an actice POA |
ExpirationDate | Datetime | False | End Date of POA |
LinkedAvailablePOA | String | False | POA download |
Bytes | String | False | Bytes of the file |
Filename | String | False | Original filename |
Success | Bool | False | True if this download succeeded |
ContentType | String | False | Mime content type |
Region | String | False | Region POA is for |
EffectiveDate | Datetime | False | Effective Date of the POA |
Country | String | False | Country POA is for |
Items¶
Items table for AvalaraAvatax data provider.
Select¶
The connector will use the Avalara API to process WHERE clause conditions built with the following columns and operators. Most of the columns support server side filtering with the following operators: =,>,<,<=,>=,IN,NOT,IN,AND,OR,LIKE. The rest of the filter is executed client side within the connector. For example, the following query is processed server side:
SELECT * FROM Items WHERE CompanyId = 247015 AND Description LIKE '%MUG'
SELECT * FROM Items WHERE CompanyId = 247015 AND ModifiedUserId IN (289767, 1354)
Insert¶
To add an Item.
INSERT INTO Items (companyId, itemCode, taxCode, description, itemGroup) VALUES ('247015', 'CERMUG1', 'P0000000', 'Ceramic Mug', 'Mugs')")
In order to add an item with ItemsClassifications #TEMP table should be used
INSERT INTO Itemsclassifications#TEMP (productCode, systemCode) VALUES ('P0000000', 'TARIC')")
INSERT INTO Items (companyId, itemCode, taxCode, description, itemGroup, Linkedclassifications) VALUES ('247015', 'CERMUG', 'P0000000', 'Ceramic Mug', 'Mugs', 'Itemsclassifications#TEMP')")
INSERT INTO Items (companyId, itemCode, taxCode, description, itemGroup) VALUES ('247015', 'CERMUG1', 'P0000000', 'Ceramic Mug', 'Mugs')")
Update¶
Avalara allows updates to Items columns.
UPDATE Items SET Description = 'description', itemcode = 'CERMUG' WHERE ID = '189117938' AND CompanyId = '247015'
In order to update ItemsClassifications, a #TEMP table should be used.
INSERT INTO Itemsclassifications#TEMP (systemcode, productcode) VALUES ('P0000001', 'AVATAXCEDE')")
UPDATE items SET description = 'description', itemcode = 'CERMUG', linkedclassifications = 'Itemsclassifications#TEMP' WHERE ID = '189117938' AND companyId = '247015'")
Delete¶
Items can be deleted by providing the ID of the address and issuing a DELETE statement.
DELETE FROM Items WHERE ID = 100
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Id [KEY] | Int | False | The unique ID number of this item. |
CompanyId [KEY] | Int | False | The unique ID number of the company that owns this item. |
ItemCode | String | False | A unique code representing this item. |
ModifiedUserId | Int | False | The user ID of the user who last modified this record. |
LinkedClassifications | String | False | List of classifications that belong to this item. A single classification consists of a productCode and a systemCode for a particular item. |
CreatedUserId | Int | False | The User ID of the user who created this record. |
ItemGroup | String | False | A way to group similar items. |
ModifiedDate | Datetime | False | The date/time when this record was last modified. |
Description | String | False | A friendly description of this item in your product catalog. |
LinkedParameters | String | False | List of item parameters. |
CreatedDate | Datetime | False | The date when this record was created. |
TaxCode | String | False |
Locations¶
Locations table for AvalaraAvatax data provider.
Insert¶
To create a location at least the following fields should be specified.
INSERT INTO Locations (CompanyId, Line1, LocationCode, AddressTypeId, AddressCategoryId, PostalCode, Country) VALUES (247015, '2000 Main Street', 'L001', 'Location', 'MainOffice', 92614, 'US')
Update¶
To update a location, you need to specify ID and CompanyId in WHERE clause.
UPDATE Locations SET City = 'Irvine' WHERE ID = 56789 AND CompanyId = 247015
Delete¶
To delete a location, you need to specify ID and AccountId in WHERE clause.
DELETE FROM Locations WHERE ID = 56789 AND CompanyId = 247015
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Id [KEY] | Int | False | The unique ID number of this location. |
CompanyId [KEY] | Int | False | The unique ID number of the company that operates at this location. |
RegisteredDate | Datetime | False | The date when this location was registered with a tax authority. Not required. |
DbaName | String | False | If this location has a different business name from its legal entity name, specify the 'Doing Business As' name for this location. |
EffectiveDate | Date | False | The date when this location was opened for business, or null if not known. |
CreatedUserId | Int | False | The User ID of the user who created this record. |
AddressCategoryId | String | False | Indicates the type of place of business represented by this location. |
Region | String | False | Name or ISO 3166 code identifying the region within the country of the physical address of this location. |
LastTransactionDate | Datetime | False | The most recent date when a transaction was processed for this location. Set by AvaTax. |
ModifiedDate | Datetime | False | The date/time when this record was last modified. |
OutletName | String | False | A friendly name for this location. |
IsRegistered | Bool | False | Set this flag to true to indicate that this location has been registered with a tax authority. |
Line3 | String | False | The third line of the physical address of this location. |
LocationCode | String | False | A code that identifies this location. Must be unique within your company. |
PostalCode | String | False | The postal code or zip code of the physical address of this location. |
Line2 | String | False | The second line of the physical address of this location. |
Line1 | String | False | The first line of the physical address of this location. |
AddressTypeId | String | False | Indicates whether this location is a physical place of business or a temporary salesperson location. |
ModifiedUserId | Int | False | The user ID of the user who last modified this record. |
Country | String | False | Name or ISO 3166 code identifying the country of the physical address of this location. |
City | String | False | The city of the physical address of this location. |
Description | String | False | A friendly name for this location. |
County | String | False | The county name of the physical address of this location. Not required. |
IsDefault | Bool | False | Set this flag to true to indicate that this is the default location for this company. |
EndDate | Date | False | If this place of business has closed, the date when this location closed business. |
LinkedSettings | String | False | Extra information required by certain jurisdictions for filing. |
CreatedDate | Datetime | False | The date when this record was created. |
PageKey | String | False |
MultiDocument¶
MultiDocument table for AvalaraAvatax data provider.
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Id [KEY] | Int | False | The unique ID number of this MultiDocument object. |
Code [KEY] | String | False | The transaction code of the MultiDocument transaction. All individual transactions within this MultiDocument object will have this code as a prefix. |
Type [KEY] | String | False | Indicates the type of MultiDocument transaction. |
ModifiedUserId | Int | False | The user ID of the user who last modified this record. |
CreatedUserId | Int | False | The user ID of the user who created this record. |
ModifiedDate | Datetime | False | The date/time when this record was last modified. |
AccountId | Int | False | The unique ID number of the AvaTax customer account that created this MultiDocument object. |
LinkedDocuments | String | False | A separate document will exist for each unique combination of buyer and seller in this MultiDocument transaction. |
CreatedDate | Datetime | False | The date/time when this record was created. |
Nexus¶
Nexus table for AvalaraAvatax data provider.
Insert¶
To create a nexus declaration for your company, you must first obtain a list of Avalara-defined nexus querying NexusDefinitions table. Once you have determined which nexus you wish to declare, you should customize only the user-selectable fields in this object. The user selectable fields for the nexus object are CompanyId, EffectiveDate, EndDate, LocalNexusTypeId, TaxId, NexusTypeId, HasPermanentEstablishment, and IsSellerImporterOfRecord. When calling CreateNexus or UpdateNexus, all values in your nexus object except for the user-selectable fields must match an Avalara-defined system nexus object. If any data does not match, AvaTax may not recognize your nexus declaration. To create a Nexus you must specify Country, Region, JurisdictionTypeId, JurisCode, JurisName, ShortName, NexusTaxTypeGroup of an existing Avalara-defined system nexus object and optionally any other selectable field.
INSERT INTO Nexus (CompanyId, Country, Region, JurisdictionTypeId, JurisCode, JurisName, ShortName, NexusTaxTypeGroup, EffectiveDate) VALUES (283040, 'US', 'US', 'Country', 'US', 'UNITED STATES', 'UNITED STATES', 'Hospitality', '2019-11-08')
Update¶
Only user selectable fields can be updated. To update a nexus object, you need to specify ID and CompanyId in WHERE clause.
UPDATE Nexus SET HasPermanentEstablishment = true WHERE ID = 20684351 AND CompanyId = 283040
Delete¶
To delete a nexus object, you need to specify ID and CompanyId in WHERE clause.
DELETE FROM Nexus WHERE ID = 20684351 AND CompanyId = 283040
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Id [KEY] | Int | False | The unique ID number of this declaration of nexus. |
CompanyId [KEY] | Int | False | The unique ID number of the company that declared nexus. |
TaxAuthorityId | Int | False | A unique ID number of the tax authority that is associated with this nexus. |
IsSellerImporterOfRecord | Bool | False | For nexus declarations at the country level, specifies whether this company is considered the importer of record in this nexus region. Set this value to `true` to consider your company as the importer of record and collect these taxes. Leave this value as false or null and taxes will be calculated as if your company is not the importer of record. |
StateAssignedNo | String | False | The state assigned number of this jurisdiction. |
Sourcing | String | False | Indicates whether this nexus is defined as origin or destination nexus. |
JurisName | String | False | The common name of the jurisdiction in which this company declared nexus. |
TaxId | String | False | Optional - the tax identification number under which you declared nexus. |
JurisCode | String | False | The code identifying the jurisdiction in which this company declared nexus. |
EffectiveDate | Date | False | The date when this nexus began. If not known, set to null. |
CreatedUserId | Int | False | The User ID of the user who created this record. |
Region | String | False | Name or ISO 3166 code identifying the region within the country. |
ModifiedDate | Datetime | False | The date/time when this record was last modified. |
NexusTaxTypeGroup | String | False | The type of nexus that this company is declaring. Replaces NexusTypeId. |
ShortName | String | False | The short name of the jurisdiction. |
IsSSTActive | Bool | False | For the United States, this flag indicates whether this particular nexus falls within a U.S. |
JurisdictionTypeId | String | False | The type of the jurisdiction in which this company declared nexus. |
HasLocalNexus | Bool | False | True if you are also declaring local nexus within this jurisdiction. Many U.S. states have options for declaring nexus in local jurisdictions as well as within the state. |
ModifiedUserId | Int | False | The user ID of the user who last modified this record. |
Country | String | False | Name or ISO 3166 code identifying the country in which this company declared nexus. |
HasPermanentEstablishment | Bool | False | Set this value to true if your company has a permanent establishment within this jurisdiction. |
NexusTypeId | String | False | The type of nexus that this company is declaring. |
EndDate | Date | False | If this nexus will end or has ended on a specific date, set this to the date when this nexus ends. |
LocalNexusTypeId | String | False | If you are declaring local nexus within this jurisdiction, this indicates whether you are declaring only a specified list of local jurisdictions, all state-administered local jurisdictions, or all local jurisdictions. |
SignatureCode | String | False | The signature code of the boundary region as defined by Avalara. |
CreatedDate | Datetime | False | The date when this record was created. |
PageKey | String | False |
Pseudo-Columns¶
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
---|---|---|
Rows@Next | String | Identifier for the next page of results. Do not set this value manually. |
Notices¶
Notices table for AvalaraAvatax data provider.
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Id [KEY] | Int | False | The unique ID number of this notice. |
CompanyId [KEY] | Int | False | The unique ID number of the company to which this notice belongs. |
TicketReferenceNo | String | False | The ticket reference number of the notice. |
ExpectedResolutionDate | Datetime | False | Expected resolution date of the notice. |
ShowResolutionDateToCustomer | Bool | False | Indicator to show customer this resolution date. |
ClosedDate | Datetime | False | The closed date of the notice. |
FilingFrequency | String | False | The filing frequency of the notice. |
LinkedComments | String | False | Additional comments on the notice. |
CustomerTypeId | String | False | NoticeCustomerTypeID can be retrieved from the definitions API. |
ReceivedDate | Datetime | False | The received date of the notice. |
PriorityId | String | False | The priority of the notice. |
TypeId | Int | False | The tax notice type id. |
JurisdictionType | String | False | The jurisdiction type of the notice. |
SalesForceCaseUrl | String | False | The URL to the sales force case. |
Description | String | False | The description of the notice. |
Reason | String | False | The notice reason. |
TaxPeriod | String | False | The tax period of the notice. |
CreatedDate | Datetime | False | The date when this record was created. |
TotalRemit | String | False | The total remmitance amount for the notice. |
CreatedUserId | Int | False | The User ID of the user who created this record. |
StatusId | Int | False | The status ID of the notice. |
ModifiedDate | Datetime | False | The date/time when this record was last modified. |
JurisdictionName | String | False | The jurisdiction name of the notice. |
FilingTypeId | String | False | The filing type of the notice. |
ComplianceContactId | Int | False | The ID of the compliance contact. |
Status | String | False | The status of the notice. |
TaxAuthorityId | Int | False | The tax authority ID of the notice. |
CustomerComment | String | False | Comments from the customer on this notice. |
TaxFormCode | String | False | The tax form code of the notice. |
RevenueContactId | Int | False | The ID of the revenue contact. |
DocumentReference | String | False | The document reference of the notice. |
ReasonId | Int | False | The notice reason id. |
LinkedRootCause | String | False | Notice Root Cause Details. |
ClosedByUserId | Int | False | The unique ID number of the user that closed the notice. |
Country | String | False | Name or ISO 3166 code identifying the country that sent this notice. |
TicketReferenceUrl | String | False | The ticket reference URL of the notice. |
CreatedByUserName | String | False | The user who created the notice. |
OwnedByUserId | Int | False | The unique ID number of the user that owns the notice. |
HideFromCustomer | Bool | False | Indicator to hide from customer. |
LinkedResponsibility | String | False | Notice Responsibility Details. |
ModifiedUserId | Int | False | The user ID of the user who last modified this record. |
Region | String | False | Name or ISO 3166 code identifying the region within the country that sent this notice. |
CustomerFundingOptionId | String | False | The notice customer funding options. |
AvaFileFormId | Int | False | The ava file form ID of the notice. |
SalesForceCase | String | False | The sales force case of the notice. |
LinkedFinances | String | False | Finance details of the notice. |
Type | String | False | The tax notice type description. |
PageKey | String | False |
Notifications¶
Notifications table for AvalaraAvatax data provider.
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Id [KEY] | Int | False | The unique ID of the notification. |
CompanyId | Int | False | If this notification was tied to a specific company, this will be the unique ID number of the company that received the notification. |
CreatedUserId | Int | False | The unique ID number of the user who created the notification. |
ActionDueDate | Datetime | False | If there is a specific action suggested by this notification, and if this action is requested by a specific due date, this value will be the due date for the action. |
ActionName | String | False | If there is a specific action suggested by this notification, this is the name of the action. |
ReferenceObject | String | False | The type name of object referred to by this notification, if any. |
Dismissed | Bool | False | Wether the notification is dismissed. |
ModifiedDate | Datetime | False | The UTC timestamp of the time when this notification was last modified. |
ReferenceId | Int | False | The unique reference ID number of the object referred to by this notification, if any. |
Message | String | False | The message for this notification. This is a friendly description of the notification and any relevant information that can help you decide what kind of action, if any, to take in response. |
ActionLink | String | False | If there is a specific action suggested by this notification, this is the URL of the action. |
DismissedDate | Datetime | False | If this notification has been dismissed, this indicates the timestamp (in UTC time) when the user dismissed the notification. |
ModifiedUserId | Int | False | The unique ID number of the user who most recently modified this notification. |
ExpireDate | Datetime | False | If this notification is time sensitive, this is the latest date when the notification should be displayed to the user. |
NeedsAction | Bool | False | If this notification object requires user action to resolve, this value will be set to true. |
DismissedByUserId | Int | False | If this notification has been dismissed, this indicates the unique ID number of the user that dismissed the notification. |
Category | String | False | The category of this notification. |
Topic | String | False | The topic of this notification. |
AccountId | Int | False | The unique ID number of the account that received this notification. |
CreatedDate | Datetime | False | The UTC timestamp of the time when this notification was created. |
SeverityLevelId | String | False | The severity level of the notification. |
PageKey | String | False |
Settings¶
Settings table for AvalaraAvatax data provider.
The company settings system is a metadata system that you can use to store extra information about a company. Your integration or connector could use this data storage to keep track of preference information, reminders, or any other storage that would need to persist even if the customer uninstalls your application.
Insert¶
A setting can refer to any type of data you need to remember about this company object. When creating this object, you may define your own set, name, and value parameters. To define your own values, please choose a set name that begins with X- to indicate an extension. CompanyId, Set and Name are required fields.
INSERT INTO Settings (CompanyId, Set, Name) VALUES (283040, 'ItemsToRemember', 'Texas')
Update¶
To update a setting, you need to specify ID and CompanyId in WHERE clause.
UPDATE Settings SET Value = 'Alamo' WHERE ID = 297847 AND CompanyId = 283040
Delete¶
To delete a setting, you need to specify ID and CompanyId in WHERE clause.
DELETE FROM Settings WHERE ID = 297847 AND CompanyId = 283040
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Id [KEY] | Int | False | The unique ID number of this setting. |
CompanyId [KEY] | Int | False | The unique ID number of the company this setting refers to. |
Name | String | False | A user-defined name for this name-value pair. |
Value | String | False | The value of this name-value pair. |
Set | String | False | A user-defined set containing this setting. |
PageKey | String | False |
TaxCodes¶
TaxCodes table for AvalaraAvatax data provider.
Insert¶
Create one or more new taxcode objects attached to a company specifying at least CompanyId, TaxCode, TaxCodeTypeId:
INSERT INTO TaxCodes (TaxCode, taxCodeTypeId, CompanyId) VALUES ('PS081283', 'P', '283040')
Update¶
Avalara allows one of the following user roles: AccountAdmin, CompanyAdmin, CSPTester, SSTAdmin, TechnicalSupportAdmin to update TaxCode objects. CompanyId and ID should be specified in the WHERE clause.
UPDATE TaxCodes SET Description = 'Yarn' WHERE ID = 85083 AND CompanyId = 283040
Delete¶
To delete a TaxCode object CompanyId and ID should be specified in the WHERE clause:
DELETE FROM TaxCodes WHERE ID = 85083 AND CompanyId = 283040
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Id [KEY] | Int | False | The unique ID number of this tax code. |
CompanyId [KEY] | Int | False | The unique ID number of the company that owns this tax code. |
ModifiedUserId | Int | False | The user ID of the user who last modified this record. |
CreatedUserId | Int | False | The User ID of the user who created this record. |
EntityUseCode | String | False | The Avalara Entity Use Code represented by this tax code. |
ModifiedDate | Datetime | False | The date/time when this record was last modified. |
TaxCodeTypeId | String | False | The type of this tax code. |
GoodsServiceCode | Int | False | The Avalara Goods and Service Code represented by this tax code. |
IsSSTCertified | Bool | False | True if this tax code has been certified by the Streamlined Sales Tax governing board. |
TaxCode | String | False | A code string that identifies this tax code. |
Description | String | False | A friendly description of this tax code. |
IsPhysical | Bool | False | True if this tax code type refers to a physical object. Read only field. |
IsActive | Bool | False | True if this tax code is active and can be used in transactions. |
CreatedDate | Datetime | False | The date when this record was created. |
PageKey | String | False |
TaxRules¶
TaxRules table for AvalaraAvatax data provider.
Insert¶
You can create custom tax rules to customize the behavior of AvaTax to match specific rules that are custom to your business. If you have obtained a ruling from a tax auditor that requires custom tax calculations, you can use custom tax rules to redefine the behavior for your company or item.
INSERT INTO TaxRules (CompanyId, JurisCode, JurisName, JurisdictionTypeId, TaxRuleTypeId, Country, TaxTypeId, TaxCode, TaxCodeId, TaxTypeGroup, TaxSubType) VALUES ('283040', '013', 'MARICOPA', 'County', 'ProductTaxabilityRule', 'US', 'BothSalesAndUseTax', 'PS081283', '85083', 'SalesAndUse', 'ALL')
Update¶
To update a tax rule, you need to specify ID and CompanyId in WHERE clause.
UPDATE TaxRules SET Description = 'Freight' WHERE ID = 56789 AND CompanyId = 247015
Delete¶
To delete a tax rule, you need to specify ID and AccountId in WHERE clause.
DELETE FROM TaxRules WHERE ID = 56789 AND CompanyId = 247015
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Id [KEY] | Int | False | The unique ID number of this tax rule. |
CompanyId [KEY] | Int | False | The unique ID number of the company that owns this tax rule. |
UomId | Int | False | For tax rules that are calculated using units of measurement, this indicates the unit of measurement type used to calculate the amounts for this rule. |
Threshold | String | False | The per-unit threshold that must be met before this rule applies. |
TaxRuleTypeId | String | False | This type value determines the behavior of the tax rule. |
JurisCode | String | False | The code of the jurisdiction to which this tax rule applies. |
EntityUseCode | String | False | The entity use code to which this rule applies. |
NonPassthroughExpression | String | False | Reserved for Avalara internal usage. Leave this field null. |
Description | String | False | A friendly name for this tax rule. |
CreatedDate | Datetime | False | The date when this record was created. |
CountyFIPS | String | False | For U.S. tax rules, this is the county's Federal Information Processing Standard (FIPS) code. |
CreatedUserId | Int | False | The User ID of the user who created this record. |
EndDate | Date | False | The last date for which this rule applies. If `null`, this rule will apply to all dates after the effective date. |
TaxSubType | String | False | This field has different behavior based on the type of rule. For a product taxability rule, if the rule applies to an item, this value will override the tax sub type of the original product. For other rules, this value determines what tax sub types will be affected by the rule. |
ModifiedDate | Datetime | False | The date/time when this record was last modified. |
TaxCode | String | False | For rules that apply to a specific tax code only, this specifies which tax code is affected by this rule. |
Options | String | False | Supports custom options for your tax rule. |
JurisName | String | False | The name of the jurisdiction to which this tax rule applies. |
TaxTypeGroup | String | False | This field has different behavior based on the type of rule. For a product taxability rule, if the rule applies to an item, this value will override the tax type group of the original product. For other rules, this value determines what tax type groups will be affected by the rule. |
JurisdictionTypeId | String | False | The type of the jurisdiction to which this tax rule applies. |
Cap | String | False | The maximum cap for the price of this item according to this rule. Any amount above this cap will not be subject to this rule. |
Country | String | False | Name or ISO 3166 code identifying the country where this rule will apply. |
Value | String | False | This field has different behavior based on the type of the tax rule. For a product taxability rule, this value is either 1 or 0, indicating taxable or non-taxable. For a rate override rule, this value is the corrected rate stored as a decimal, for example, a rate of 5% would be stored as 0.05 decimal. If you use the special value of 1.0, only the cap and threshold values will be applied and the rate will be left alone. |
PreferredProgramId | Int | False | Reserved for Avalara internal usage. Leave this field null. |
CurrencyCode | String | False | The currency code to use for this rule. |
EffectiveDate | Date | False | The first date at which this rule applies. If `null`, this rule will apply to all dates prior to the end date. |
StateFIPS | String | False | For U.S. tax rules, this is the state's Federal Information Processing Standard (FIPS) code. |
RateTypeCode | String | False | Indicates the code of the rate type that applies to this rule. |
ModifiedUserId | Int | False | The user ID of the user who last modified this record. |
Region | String | False | Name or ISO 3166 code identifying the region where this rule will apply. |
TaxTypeId | String | False | Some tax type groups contain multiple different types of tax. To create a rule that affects only one type of tax within a tax type group, set this value to the code matching the specific tax type within that group. |
Sourcing | String | False | The sourcing types to which this rule applies. |
TaxCodeId | Int | False | For rules that apply to a specific tax code only, this specifies which tax code is affected by this rule. |
IsAllJuris | Bool | False | Allows you to make tax rules apply to lower jurisdictions. This feature is only available in the United States and Canada. |
PageKey | String | False |
TransactionLines¶
TransactionLines view for AvalaraAvatax data provider.
Select¶
The connector will use the Avalara API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector. By default the lines of Adjusted transactions will not be listed. If you want to include them, you need to set IncludeAdjustedTransactions to true.
TrsnactionId, TransactionCode
support the '=', IN operator.
For example, the following query is processed server side:
SELECT * FROM TransactionLines WHERE TransactionCode IN ('Code1', 'Code2')
Delete¶
To delete a TransactionLine item, you need to specify the TransactionCode and LineNumber in the WHERE clause.
DELETE FROM TransactionLines WHERE TransactionCode = 'TC001' AND LineNumber = 1
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Id | Long | False | The unique ID number of this transaction line item. |
TransactionId | Long | False | The unique ID number of the transaction to which this line item belongs. |
TransactionStatus | String | False | The status of the transaction to which this line item belongs. |
TransactionCode [KEY] | String | False | A unique customer-provided code identifying this transaction. |
DestinationAddressId | Long | False | The unique ID number of the destination address where this line was delivered or sold. In the case of a point-of-sale transaction, the destination address and origin address will be the same. In the case of a shipped transaction, they will be different. |
TaxCalculated | String | False | The amount of tax that AvaTax calculated for the transaction. |
DiscountAmount | String | False | The amount of discount that was applied to this line item. |
HsCode | String | False | The cross-border harmonized system code (HSCode) used to calculate tariffs and duties for this line item. |
DiscountTypeId | Int | False | The type of discount, if any, that was applied to this line item. |
TaxableAmount | String | False | The taxable amount of this line item. |
CostInsuranceFreight | String | False | Indicates the cost of insurance and freight for this line. |
CertificateId | String | False | The CertCapture Certificate ID |
TaxDate | Date | False | The date that was used for calculating tax amounts for this line item. |
Ref2 | String | False | A user-defined reference identifier for this transaction line item. |
EntityUseCode | String | False | The entity use code for this line item. Usage type often affects taxability rules. |
Description | String | False | A description of the item or service represented by this line. |
Ref1 | String | False | A user-defined reference identifier for this transaction line item. |
ExemptNo | String | False | The customer Tax ID Number (tax_number) associated with a certificate. |
Quantity | String | False | The quantity of products sold on this line item. |
TaxCode | String | False | The code string for the tax code that was used to calculate this line item. |
TaxOverrideType | String | False | If a tax override was specified, this indicates the type of tax override. |
LineNumber [KEY] | String | False | The line number or code indicating the line on this invoice or receipt or document. |
BusinessIdentificationNo | String | False | VAT business identification number used for this transaction. |
ReportingDate | Date | False | The date when this transaction should be reported. By default, all transactions are reported on the date when the actual transaction took place. In some cases, line items may be reported later due to delayed shipments or other business reasons. |
VatNumberTypeId | Int | False | Indicates the VAT number type for this line item. |
VatCode | String | False | Indicates the VAT code for this line item. |
LineAmount | String | False | The total amount of the transaction, including both taxable and exempt. |
Tax | String | False | The tax for this line in this transaction. |
LinkedLineLocationTypes | String | False | Optional: A list of location types for this line item. |
IsItemTaxable | Bool | False | True if this item is taxable. |
TaxOverrideAmount | String | False | If a tax override was specified, this indicates the amount of tax that was requested. |
ExemptCertId | Int | False | The unique ID number of the exemption certificate that applied to this line item. It is the calc_id associated with a certificate in CertCapture. |
ItemCode | String | False | The code string of the item represented by this line item. |
BoundaryOverrideId | Int | False | The unique ID number of the boundary override applied to this line item. |
OriginAddressId | Long | False | The unique ID number of the origin address where this line was delivered or sold. |
TaxOverrideReason | String | False | If a tax override was specified, represents the reason for the tax override. |
ExemptAmount | String | False | The amount of this line item that was exempt. |
TaxIncluded | Bool | False | Indicates whether the amount for this line already includes tax. |
IsSSTP | Bool | False | True if this item is a Streamlined Sales Tax line item. |
Sourcing | String | False | Indicates whether this line item was taxed according to the origin or destination. |
TaxEngine | String | False | The tax engine identifier that was used to calculate this line item. |
TaxCodeId | Int | False | The unique ID number for the tax code that was used to calculate this line item. |
RevAccount | String | False | The revenue account number for this line item. |
Transactions¶
Transactions table for AvalaraAvatax data provider.
Select¶
The connector will use the Avalara API to process WHERE clause conditions built with the following columns and operators. Most of the columns support server side filtering with the following operators: =,>,<,<=,>=,IN,NOT,IN,AND,OR,LIKE. The rest of the filter is executed client side within the connector. By default the Adjusted transactions will not be listed. If you want to include them, you need to set IncludeAdjustedTransactions to true. For example, the following query is processed server side:
SELECT * FROM Transactions WHERE AdjustmentReason = 'PriceAdjusted'
SELECT * FROM Items WHERE Status = 'Cancelled'
Insert¶
To add a new Transaction item, the following columns need to be specified: CustomerCode, Date, Type, LinkedLines, Code, LinkedAddresses. If you want to commit the transaction, set the Commit flag in the request to true. The linked columns can be inserted using a #TEMP table or by passing the aggregate value.
The LinkedAddresses aggregate must include information about the origin and destination address. If this transaction occurred at a retail point-of-sale location, provide that single address here and leave all other address types null. If origin differs from destination, insert both ShipFrom and ShipTo address types. If you wish to use the address of an existing location for the actual company, specify the address in the addressType_locationCode field.
The examples below illustrate the aforementioned scenarios.
Inserting LinkedAddresses aggregate through #TEMP table with SingleLocation:
INSERT INTO Addresses#TEMP (SingleLocation_line1, SingleLocation_City, SingleLocation_County, SingleLocation_PostalCode) VALUES ('2000 Main Street', 'Irvine', 'US', 92614)")
INSERT INTO Addresses#TEMP (SingleLocation_locationCode) VALUES ('LC001')")
Inserting LinkedAddresses aggregate through #TEMP table with ShipFrom and ShipTo locations:
INSERT INTO Addresses#TEMP (shipTo_line1, shipTo_City, shipTo_County, shipTo_PostalCode, shipFrom_line1, shipFrom_City, shipFrom_Country, shipFrom_PostalCode) VALUES ('2000 Main Street', 'Irvine', 'US', 92614, '', '', 'US', '')")
INSERT INTO Addresses#TEMP (shipTo_locationCode, shipFrom_locationCode) VALUES ('LC001', 'LC002')")
Insert line items aggregate using #TEMP table. For a list of the columns you can insert into the temporary table, refer to TransactionLines table.
INSERT INTO TransactionLines#TEMP (ItemCode, Quantity, LineAmount) VALUES ('IC005', 3, 300)")
Insert into the main Transactions table :
INSERT INTO Transactions (LinkedLines, LinkedAddresses, Code, CompanyId, CustomerCode, Date, Type, Commit, TotalTax) VALUES (TransactionLines#TEMP, Addresses#TEMP, DC008, 247015, 'CC002', '09/30/2019', 'SalesInvoice', true, 12.0)")
Update¶
Avalara allows updates on Transactions. To update a transaction, AdjustmentReason and Adjustment description are required fields. Additionally, you need to specify the Code of the transaction you are updating in the WHERE clause.
Similarly, to Insert, you can update line items using temporary tables. To update an existing line, include the LineNumber of the line you wish to update. To insert a new line in the transaction, set the LineNumber to a new, non-existing value.
When a transaction is updated, the actual transaction's status changes to Adjusted and a new transaction with the updated information is created. The new transaction will have the same document code, but a different ID and status: Saved or Commited. If you wish to commit the new updated transaction, set the Commit flag in the request to true.
INSERT INTO TransactionLines#TEMP (ItemCode, Quantity, LineAmount, LineNumber) VALUES (IC007, 3, 300, 3)
UPDATE Transactions SET LinkedLines = 'TransactionLines#TEMP', AdjustmentDescription = 'MyDescription', AdjustmentReason = 'PriceAdjusted' WHERE Code = '051349'
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Id | Long | False | The unique ID number of this transaction. |
Code [KEY] | String | False | A unique customer-provided code identifying this transaction. |
CompanyId | Int | False | The unique ID number of the company that recorded this transaction. |
ExchangeRateEffectiveDate | Date | False | If this transaction included foreign currency exchange, this is the date as of which the exchange rate was calculated. |
SalespersonCode | String | False | The salesperson who provided this transaction. Not required. |
AdjustmentDescription | String | False | If this transaction was adjusted, indicates a description of the reason why the transaction was adjusted. |
DestinationAddressId | Long | False | The unique ID number of the destination address for this transaction. |
LinkedLocationTypes | String | False | A list of location types in this transaction. |
IsSellerImporterOfRecord | Bool | False | By default, the value is null, when the value is null, the value can be set at nexus level and used. If the value is not null, it will override the value at nexus level. If true, this seller was considered the importer of record of a product shipped internationally. |
BatchCode | String | False | If this transaction was created as part of a batch, this code indicates which batch. |
LinkedTaxDetailsByTaxType | String | False | Contains the tax details per tax type |
TaxDate | Datetime | False | Tax date for this transaction |
EntityUseCode | String | False | The entity use code for this transaction. Entity use codes often affect exemption or taxability rules. |
LinkedInvoiceMessages | String | False | Invoice messages associated with this transaction. Currently, this stores legally-required VAT messages. |
Description | String | False | Description of this transaction. Field permits unicode values. |
LinkedAddresses | String | False | A list of addreses used in this transaction. |
TotalTax | String | False | The total tax for all lines in this transaction. |
LinkedMessages | String | False | List of informational and warning messages regarding this API call. These messages are only relevant to the current API call. |
ExemptNo | String | False | The customer Tax ID Number (taxMyUserumber) associated with a certificate. |
TotalTaxable | String | False | The portion of the total amount of this transaction that was taxable. |
ModifiedDate | Datetime | False | The date/time when this record was last modified. |
Email | String | False | Email address associated with this transaction. |
TaxOverrideType | String | False | If a tax override was applied to this transaction, indicates what type of tax override was applied. |
BusinessIdentificationNo | String | False | VAT business identification number used for this transaction. |
ReportingLocationCode | String | False | For customers who use location-based tax reporting, this field controls how this transaction will be filed for multi-location tax filings. |
TotalTaxCalculated | String | False | The amount of tax that AvaTax calculated for the transaction. |
ExchangeRate | String | False | If this transaction included foreign currency exchange, this is the exchange rate that was used. |
CustomerCode | String | False | Unique code identifying the customer that requested this transaction. |
Locked | Bool | False | If this transaction has been reported to a tax authority, this transaction is considered locked and may not be adjusted after reporting. |
Status | String | False | The status of the transaction. |
TotalAmount | String | False | The total amount of this transaction. |
LinkedLines | String | False | A list of line items in this transaction. |
TotalDiscount | String | False | The total amount of discounts applied to all lines within this transaction. |
AdjustmentReason | String | False | If this transaction was adjusted, indicates the unique ID number of the reason why the transaction was adjusted. |
TaxOverrideAmount | String | False | If a tax override was applied to this transaction, indicates the amount of tax that was requested by the customer. |
SoftwareVersion | String | False | The software version used to calculate this transaction. |
Country | String | False | The two-character ISO 3166 code of the country for this transaction. |
CurrencyCode | String | False | The three-character ISO 4217 currency code that was used for payment for this transaction. |
OriginAddressId | Long | False | The unique ID number of the origin address for this transaction. |
TotalExempt | String | False | The amount of this transaction that was exempt. |
PurchaseOrderNo | String | False | The customer-supplied purchase order number of this transaction. |
TaxOverrideReason | String | False | If a tax override was applied to this transaction, indicates the reason for the tax override. |
TaxOverrideDate | Date | False | The override tax date to use |
LinkedParameters | String | False | Contains a list of extra parameters that were set when the transaction was created. |
LinkedSummary | String | False | Contains a summary of tax on this transaction. |
Version | Int | False | If this transaction was adjusted, this indicates the version number of this transaction. Incremented each time the transaction is adjusted. |
Date | Date | False | The date on which this transaction occurred. |
ModifiedUserId | Int | False | The user ID of the user who last modified this record. |
Region | String | False | The two-or-three character ISO region code of the region for this transaction. |
ReferenceCode | String | False | A user-defined reference code for this transaction. |
Reconciled | Bool | False | If this transaction has been reconciled against the company's ledger, this value is set to true. |
Type [KEY] | String | False | The type of the transaction. |
Commit | Boolean | False | Wether to commit a transaction on creation. Used only on inserts |
Upcs¶
Upcs table for AvalaraAvatax data provider.
A UPC represents a single Universal Product Code in your catalog and matches this product to the tax code identified by this UPC.
Insert¶
To create a UPC object CompanyId, Upc and Description are required fields.
INSERT INTO Upcs (CompanyId, Upc, Description) VALUES (283040, '023032550992', 'Yarn')
Update¶
To update a Upc, you need to specify ID and CompanyId in WHERE clause.
UPDATE Upcs SET LegacyTaxCode = 'PS081282' WHERE ID = 123456789 AND CompanyId = 283040
Delete¶
To delete a Upc, you need to specify ID and CompanyId in WHERE clause.
DELETE FROM Upcs WHERE ID = 123456789 AND CompanyId = 283040
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Id [KEY] | Int | False | The unique ID number for this UPC. |
CompanyId [KEY] | Int | False | The unique ID number of the company to which this UPC belongs. |
ModifiedUserId | Int | False | The user ID of the user who last modified this record. |
CreatedUserId | Int | False | The User ID of the user who created this record. |
Upc | String | False | The 12-14 character Universal Product Code, European Article Number, or Global Trade Identification Number. |
ModifiedDate | Datetime | False | The date/time when this record was last modified. |
EndDate | Date | False | If this UPC expired or will expire on a certain date, this contains the last date on which the UPC was effective. |
LegacyTaxCode | String | False | Legacy Tax Code applied to any product sold with this UPC. |
Usage | Int | False | A usage identifier for this UPC code. |
IsSystem | Int | False | A flag indicating whether this UPC code is attached to the AvaTax system or to a company. |
EffectiveDate | Date | False | If this UPC became effective on a certain date, this contains the first date on which the UPC was effective. |
Description | String | False | Description of the product to which this UPC applies. |
CreatedDate | Datetime | False | The date when this record was created. |
PageKey | String | False |
Users¶
Users table for AvalaraAvatax data provider.
Insert¶
To create a new user the following fields should be specified. A newly created user will receive an email inviting them to create their password. This means that you must provide a valid email address for all user accounts created.
INSERT INTO Users (AccountId, UserName, FirstName, LastName, Email, securityRoleId) VALUES (2000137504, 'user123', 'James', 'Smith', 'smith@gmail.com', 'AccountUser')
Update¶
Only user selectable fields can be updated. To update a user, you need to specify ID and AccountId in WHERE clause.
UPDATE Users SET IsActive = true WHERE ID = 332668 AND AccountId = 2000137504
Delete¶
To delete a user, you need to specify ID and AccountId in WHERE clause.
DELETE FROM Users WHERE ID = 332668 AND AccountId = 2000137504
Columns¶
Name | Type | ReadOnly | Description |
---|---|---|---|
Id [KEY] | Int | False | The unique ID number of this user. |
AccountId [KEY] | Int | False | The unique ID number of the account to which this user belongs. |
PostalCode | String | False | The postal code in which this user resides. |
SecurityRoleId | String | False | The security level for this user. |
ModifiedDate | Datetime | False | The date/time when this record was last modified. |
PasswordStatus | String | False | The status of the user's password. For a new user created, this is always going to be `UserMustChange`. |
Email | String | False | The email address to be used to contact this user. If the user has forgotten a password, an email can be sent to this email address with information on how to reset this password. |
UserName | String | False | The username which is used to log on to the AvaTax website, or to authenticate against API calls. |
CompanyId | Int | False | If this user is locked to one company (and its children), this is the unique ID number of the company to which this user belongs. |
FirstName | String | False | The first or given name of the user. |
LastName | String | False | The last or family name of the user. |
IsActive | Bool | False | True if this user is currently active. |
CreatedDate | Datetime | False | The date when this record was created. |
PageKey | String | False |
Views¶
Views are similar to tables in the way that data is represented; however, views are read-only.
Queries can be executed against a view as if it were a normal table.
Avalara Connector Views¶
Name | Description |
---|---|
BatchFiles | BatchFiles view for AvalaraAvatax data provider. |
CustomerCustomFields | CustomerCustomFields view for AvalaraAvatax data provider. |
CustomerExposureZones | CustomerExposureZones view for AvalaraAvatax data provider. |
FilingCalendarAvailablePOA | FilingCalendarAvailablePOA view for AvalaraAvatax data provider. |
ItemClassifications | ItemClassifications view for AvalaraAvatax data provider. |
ItemParameters | ItemParameters view for AvalaraAvatax data provider. |
LocationSettings | LocationSettings view for AvalaraAvatax data provider. |
Nexus | Nexus table for AvalaraAvatax data provider. |
NoticeComments | NoticeComments view for AvalaraAvatax data provider. |
NoticeFinances | NoticeFinances view for AvalaraAvatax data provider. |
NoticeResponsibility | NoticeResponsibility view for AvalaraAvatax data provider. |
NoticeRootCause | NoticeRootCause view for AvalaraAvatax data provider. |
ReportParameters | ReportParameters view for AvalaraAvatax data provider. |
Reports | Reports table for AvalaraAvatax data provider. |
Subscriptions | Subscriptions table for AvalaraAvatax data provider. |
TransactionAddresses | TransactionAddresses view for AvalaraAvatax data provider. |
TransactionInvoiceMessages | TransactionInvoicemessages view for AvalaraAvatax data provider. |
TransactionLocationTypes | TransactionLocationTypes view for AvalaraAvatax data provider. |
TransactionMessages | TransactionMessages view for AvalaraAvatax data provider. |
TransactionParameters | TransactionParameters view for AvalaraAvatax data provider. |
TransactionSummary | TransactionSummary view for AvalaraAvatax data provider. |
TransactionTaxDetailsByTaxType | TransactionTaxdetailsbytaxtype view for AvalaraAvatax data provider. |
BatchFiles¶
BatchFiles view for AvalaraAvatax data provider.
Columns¶
Name | Type | Description |
---|---|---|
Id [KEY] | Int | The unique ID number assigned to this batch file. |
BatchId | Int | The unique ID number of the batch that this file belongs to. |
CompanyId [KEY] | Int | The Company ID number of the company that owns this batch. |
Name | String | Logical Name of file. |
ContentLength | Int | Size of content, in bytes. |
FilePath | String | Path to the file - name/S3 key. |
FileExtension | String | File extension (e.g. CSV). |
FileLocalPath | String | Local path of the file. |
ErrorCount | Int | Number of errors that occurred when processing this file. |
ContentType | String | Content mime type (e.g. text/csv). This is used for HTTP downloading. |
CustomerCustomFields¶
CustomerCustomFields view for AvalaraAvatax data provider.
Select¶
To retrieve records from CustomerCustomFields view, at least CompanyId or CompanyId and CompanyCode should be specified in the WHERE clause. The connector will use the Avalara API to process WHERE clause conditions built with the following columns and operators. CompanyId and CompanyCode support server side filtering with the = operator. The rest of the filter is executed client side within the connector. For example, the following query is processed server side:
SELECT * FROM CustomerCustomFields WHERE CompanyId = '247015' AND CustomerCode = 'CC001'
Columns¶
Name | Type | Description |
---|---|---|
CompanyId [KEY] | String | The company ID of the parent record. |
CustomerCode [KEY] | String | The customer code this record is associated to. |
Name | String | The name of the custom field. |
Value | String | The value of the custom field. |
CustomerExposureZones¶
CustomerExposureZones view for AvalaraAvatax data provider.
Select¶
To retrieve records from CustomerExposureZones view, at least CompanyId or CompanyId and CompanyCode should be specified in the WHERE clause. The connector will use the Avalara API to process WHERE clause conditions built with the following columns and operators. CompanyId and CompanyCode support server side filtering with the = operator. The rest of the filter is executed client side within the connector. For example, the following query is processed server side:
SELECT * FROM CustomerExposureZones WHERE CompanyId = '247015' AND CustomerCode = 'CC001'
Columns¶
Name | Type | Description |
---|---|---|
Id | Int | A unique ID number representing this exposure zone. |
CompanyId [KEY] | Int | The unique ID number of the AvaTax company that recorded this customer. |
CustomerCode [KEY] | String | The customer code this record is associated to. |
Name | String | The short name of this exposure zone, suitable for use in a drop-down list. |
Country | String | Two character ISO 3166 county code for the country component of this exposure zone. |
Created | Datetime | The date when this record was created. |
Region | String | Two or three character ISO 3166 region, province, or state name of this exposure zone. |
Description | String | A more complete description of this exposure zone, suitable for use as a tooltip or help text. |
Modified | Datetime | The date/time when this record was last modified. |
Tag | String | A tag indicating |
FilingCalendarAvailablePOA¶
FilingCalendarAvailablePOA view for AvalaraAvatax data provider.
Columns¶
Name | Type | Description |
---|---|---|
CompanyId [KEY] | String | Company ID of the parent record. |
Bytes | String | Bytes of the file. |
Filename | String | Original filename. |
Success | Bool | True if this download succeeded. |
ContentType | String | Mime content type. |
ItemClassifications¶
ItemClassifications view for AvalaraAvatax data provider.
Columns¶
Name | Type | Description |
---|---|---|
CompanyId [KEY] | String | The CompanyId of the parent record. |
ItemId [KEY] | String | The unique ID number of the item this product belongs to. |
SystemCode | String | The system code in which the product belongs. |
ProductCode | String | The product code of an item in a given system. |
ItemParameters¶
ItemParameters view for AvalaraAvatax data provider.
Columns¶
Name | Type | Description |
---|---|---|
Id [KEY] | Int | The ID of the parameter. |
CompanyId [KEY] | String | The CompanyId of the parent record. |
ItemId [KEY] | Int | The item id. |
Name | String | The parameter's name. |
Unit | String | The unit of measurement code for the parameter. |
Value | String | The value for the parameter. |
LocationSettings¶
LocationSettings view for AvalaraAvatax data provider.
Columns¶
Name | Type | Description |
---|---|---|
CompanyId [KEY] | String | CompanyId of the parent record. |
LocationId [KEY] | String | The LocationId that this setting is associated to. |
QuestionId | Int | The unique ID number of the location question answered. |
QuestionName | String | The name of the question. |
Value | String | The answer the user provided. |
Nexus¶
Nexus table for AvalaraAvatax data provider.
Insert¶
To create a nexus declaration for your company, you must first obtain a list of Avalara-defined nexus querying NexusDefinitions table. Once you have determined which nexus you wish to declare, you should customize only the user-selectable fields in this object. The user selectable fields for the nexus object are CompanyId, EffectiveDate, EndDate, LocalNexusTypeId, TaxId, NexusTypeId, HasPermanentEstablishment, and IsSellerImporterOfRecord. When calling CreateNexus or UpdateNexus, all values in your nexus object except for the user-selectable fields must match an Avalara-defined system nexus object. If any data does not match, AvaTax may not recognize your nexus declaration. To create a Nexus you must specify Country, Region, JurisdictionTypeId, JurisCode, JurisName, ShortName, NexusTaxTypeGroup of an existing Avalara-defined system nexus object and optionally any other selectable field.
INSERT INTO Nexus (CompanyId, Country, Region, JurisdictionTypeId, JurisCode, JurisName, ShortName, NexusTaxTypeGroup, EffectiveDate) VALUES (283040, 'US', 'US', 'Country', 'US', 'UNITED STATES', 'UNITED STATES', 'Hospitality', '2019-11-08')
Update¶
Only user selectable fields can be updated. To update a nexus object, you need to specify ID and CompanyId in WHERE clause.
UPDATE Nexus SET HasPermanentEstablishment = true WHERE ID = 20684351 AND CompanyId = 283040
Delete¶
To delete a nexus object, you need to specify ID and CompanyId in WHERE clause.
DELETE FROM Nexus WHERE ID = 20684351 AND CompanyId = 283040
Columns¶
Name | Type | Description |
---|---|---|
Id [KEY] | Int | The unique ID number of this declaration of nexus. |
TaxAuthorityId | Int | A unique ID number of the tax authority that is associated with this nexus. |
IsSellerImporterOfRecord | Bool | For nexus declarations at the country level, specifies whether this company is considered the importer of record in this nexus region. Set this value to `true` to consider your company as the importer of record and collect these taxes. Leave this value as false or null and taxes will be calculated as if your company is not the importer of record. |
StateAssignedNo | String | The state assigned number of this jurisdiction. |
Sourcing | String | Indicates whether this nexus is defined as origin or destination nexus. |
JurisName | String | The common name of the jurisdiction in which this company declared nexus. |
TaxId | String | Optional - the tax identification number under which you declared nexus. |
JurisCode | String | The code identifying the jurisdiction in which this company declared nexus. |
EffectiveDate | Date | The date when this nexus began. If not known, set to null. |
CreatedUserId | Int | The User ID of the user who created this record. |
Region | String | Name or ISO 3166 code identifying the region within the country. |
ModifiedDate | Datetime | The date/time when this record was last modified. |
NexusTaxTypeGroup | String | The type of nexus that this company is declaring. Replaces NexusTypeId. |
ShortName | String | The short name of the jurisdiction. |
IsSSTActive | Bool | For the United States, this flag indicates whether this particular nexus falls within a U.S. |
JurisdictionTypeId | String | The type of the jurisdiction in which this company declared nexus. |
HasLocalNexus | Bool | True if you are also declaring local nexus within this jurisdiction. Many U.S. states have options for declaring nexus in local jurisdictions as well as within the state. |
ModifiedUserId | Int | The user ID of the user who last modified this record. |
Country | String | Name or ISO 3166 code identifying the country in which this company declared nexus. |
HasPermanentEstablishment | Bool | Set this value to true if your company has a permanent establishment within this jurisdiction. |
NexusTypeId | String | The type of nexus that this company is declaring. |
EndDate | Date | If this nexus will end or has ended on a specific date, set this to the date when this nexus ends. |
LocalNexusTypeId | String | If you are declaring local nexus within this jurisdiction, this indicates whether you are declaring only a specified list of local jurisdictions, all state-administered local jurisdictions, or all local jurisdictions. |
SignatureCode | String | The signature code of the boundary region as defined by Avalara. |
CreatedDate | Datetime | The date when this record was created. |
PageKey | String |
Pseudo-Columns¶
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
---|---|---|
Rows@Next | String | Identifier for the next page of results. Do not set this value manually. |
NoticeComments¶
NoticeComments view for AvalaraAvatax data provider.
Columns¶
Name | Type | Description |
---|---|---|
Id [KEY] | Int | The unique ID number of this notice. |
CompanyId [KEY] | Int | The company ID to which this file will be attached. |
NoticeId [KEY] | Int | The ID of the notice this comment is attached too. |
ModifiedUserId | Int | The user ID of the user who last modified this record. |
AttachmentUploadRequest | String | An attachment to the detail |
ResourceFileTypeId | Int | The resource type ID of this file. |
Name | String | The original name of this file. |
Content | String | This stream contains the bytes of the file being uploaded. |
Length | Int | Length of the file in bytes. |
Username | String | The username adding the file |
AccountId | Int | The account ID to which this file will be attached. |
Date | Datetime | The date this comment was entered |
ResourceFileId | Int | resourceFileId |
ModifiedDate | Datetime | The date/time when this record was last modified. |
CommentTypeId | Int | taxNoticeCommentTypeId |
Comment | String | TaxNoticeComment |
CommentType | String | taxNoticeCommentType |
CommentUserName | String | TaxNoticeCommentUserName |
TaxNoticeFileName | String | TaxNoticeFileName |
CommentLink | String | TaxNoticeCommentLink |
CommentUserId | Int | TaxNoticeCommentUserId |
NoticeFinances¶
NoticeFinances view for AvalaraAvatax data provider.
Columns¶
Name | Type | Description |
---|---|---|
Id [KEY] | Int | The Unique ID of the Finance Model. |
CompanyId [KEY] | Int | The company ID to which this file will be attached. |
NoticeId | Int | The unique ID of the the tax notice associated with the the finance detail. |
NoticeNumber | String | The sequential number of the notice. |
Penalty | String | The amount of penalty listed on the notice. |
TaxAbated | String | The amount of tax abated on the notice. |
ResourceFileId | Int | The ResourceFileId of the finance detail attachment. |
CreatedUserId | Int | The User ID of the user who created this record. |
ModifiedDate | Datetime | The date/time when this record was last modified. |
TaxDue | String | The amount of tax due on the notice. |
Interest | String | The amount of interest listed on the notice. |
AttachmentUploadRequest | String | An attachment to the finance detail. |
ResourceFileTypeId | Int | The resource type ID of this file. |
Name | String | The original name of this file. |
Content | String | This stream contains the bytes of the file being uploaded.. |
Length | Int | Length of the file in bytes. |
Username | String | The username adding the file. |
AccountId | Int | The account ID to which this file will be attached. |
Credits | String | The amount of credits listed on the notice. |
CspFeeRefund | String | The amount of CSP Fee Refund on the notice. |
NoticeDate | Datetime | The date of the notice. |
ModifiedUserId | Int | The user ID of the user who last modified this record. |
CustomerPenalty | String | The amount of customer penalty on the notice. |
DueDate | Datetime | The due date of the notice. |
CustomerInterest | String | The amount of customer interest on the notice. |
FileName | String | The name of the file attached to the finance detail. |
CreatedDate | Datetime | The date when this record was created. |
NoticeResponsibility¶
NoticeResponsibility view for AvalaraAvatax data provider.
Columns¶
Name | Type | Description |
---|---|---|
Id [KEY] | Int | The unique ID number of this filing frequency. |
CompanyId [KEY] | String | The CompanyId of the parent record. |
NoticeId | Int | TaxNoticeId |
TaxNoticeResponsibilityId | Int | TaxNoticeResponsibilityId |
Description | String | The description name of this filing frequency. |
NoticeRootCause¶
NoticeRootCause view for AvalaraAvatax data provider.
Columns¶
Name | Type | Description |
---|---|---|
Id [KEY] | Int | The unique ID number of this filing frequency. |
CompanyId [KEY] | String | The CompanyId of the parent record. |
NoticeId [KEY] | Int | TaxNoticeId |
Description | String | The description name of this root cause. |
TaxNoticeRootCauseId | Int | TaxNoticeRootCauseId |
ReportParameters¶
ReportParameters view for AvalaraAvatax data provider.
Columns¶
Name | Type | Description |
---|---|---|
ReportId [KEY] | String | The ID of the parent Report. |
CurrencyCode | String | The currency code used for your report. |
State | String | The state filter used for your report. |
DateFormat | String | The date format used for your report. |
StartDate | Datetime | The start date filter used for your report. |
Country | String | The country filter used for your report. |
Culture | String | The culture used your report. |
DateFilter | String | The date type filter used for your report. |
EndDate | Datetime | The end date filter used for your report. |
DocType | String | The doc type filter used for your report. |
Reports¶
Reports table for AvalaraAvatax data provider.
Columns¶
Name | Type | Description |
---|---|---|
Id [KEY] | Int | The unique identifier of the report task. |
CompletedDate | Datetime | The time when the report was finished building, if completed. |
CreatedUser | String | The userName of the user who initiated the report task. |
CreatedUserId | Int | The ID of the user who initiated this task. |
File | String | The name of the report file, if available. |
Status | String | The current status of the report building task. |
ReportType | String | The type of the report: ExportDocumentLine, etc. |
AccountId | Int | The ID of the account the reported transactions are from. |
Format | String | The format of the report file. |
CompanyId | Int | The ID of the company the reported transactions are from. |
LinkedParameters | String | The parametes used to build the report. |
CurrencyCode | String | The currency code used for your report. |
State | String | The state filter used for your report. |
DateFormat | String | The date format used for your report. |
StartDate | Datetime | The start date filter used for your report. |
Country | String | The country filter used for your report. |
Culture | String | The culture used your report. |
DateFilter | String | The date type filter used for your report. |
EndDate | Datetime | The end date filter used for your report. |
DocType | String | The doc type filter used for your report. |
CreatedDate | Datetime | The time when the report task was initiated. |
Size | Int | The size of the report file, if available. |
PageKey | String |
Subscriptions¶
Subscriptions table for AvalaraAvatax data provider.
Select¶
The connector will use the Avalara API to process WHERE clause conditions built with the following columns and operators. Most of the columns support server side filtering with the following operators: =,>,<,<=,>=,IN,NOT,IN,AND,OR,LIKE. The rest of the filter is executed client side within the connector. For example, the following query is processed server side:
SELECT * FROM Subscriptions WHERE SubscriptionDescription LIKE 'AVA%'
Columns¶
Name | Type | Description |
---|---|---|
Id [KEY] | Int | The unique ID number of this subscription. |
AccountId [KEY] | Int | The unique ID number of the account this subscription belongs to. |
ModifiedUserId | Int | The user ID of the user who last modified this record. |
CreatedUserId | Int | The User ID of the user who created this record. |
ModifiedDate | Datetime | The date/time when this record was last modified. |
EndDate | Date | If the subscription has ended or will end, this date indicates when the subscription ends. |
SubscriptionTypeId | Int | The unique ID number of the service that the account is subscribed to. If this is provided, subscription description is ignored. |
EffectiveDate | Date | The date when the subscription began. |
CreatedDate | Datetime | The date when this record was created. |
SubscriptionDescription | String | A friendly description of the service that the account is subscribed to. You can either provide the subscription type ID or this but not both. If subscription type ID is provided, then this information is ignored and this field will be updated with the information from subscription type id. |
PageKey | String |
TransactionAddresses¶
TransactionAddresses view for AvalaraAvatax data provider.
Columns¶
Name | Type | Description |
---|---|---|
Id | Long | The unique ID number of this address. |
TransactionId | Long | The unique ID number of the document to which this address belongs. |
TransactionCode [KEY] | String | A unique customer-provided code identifying this transaction. |
TransactionStatus | String | The status of the transaction to which this line item belongs. |
PostalCode | String | The postal code or zip code for the address. |
Longitude | String | Longitude for this address |
TaxRegionId | Int | The unique ID number of the tax region for this address. |
Country | String | The ISO 3166 country code |
City | String | The city for the address. |
Latitude | String | Latitude for this address |
Region | String | The ISO 3166 region code. E.g., the second part of ISO 3166-2. |
Line3 | String | The third line of the address. |
Line2 | String | The second line of the address. |
Line1 | String | The first line of the address. |
BoundaryLevel | String | The boundary level at which this address was validated. |
TransactionInvoiceMessages¶
TransactionInvoicemessages view for AvalaraAvatax data provider.
Columns¶
Name | Type | Description |
---|---|---|
TransactionId [KEY] | Long | The transaction code of the parent record. |
TransactionCode [KEY] | String | A unique customer-provided code identifying this transaction. |
TransactionStatus | String | The status of the transaction to which this line item belongs. |
LineNumbers | String | The applicable tax line numbers and codes. |
Content | String | The content of the invoice message. |
TransactionLocationTypes¶
TransactionLocationTypes view for AvalaraAvatax data provider.
Columns¶
Name | Type | Description |
---|---|---|
TransactionId | Long | Transaction ID |
TransactionCode [KEY] | String | A unique customer-provided code identifying this transaction. |
TransactionStatus | String | The status of the transaction to which this line item belongs. |
TransactionAddressId | Long | Address ID for the transaction. |
TransactionLocationTypeId | Int | Location type ID for this location type in transaction. |
LocationTypeCode | String | Location type code |
TransactionMessages¶
TransactionMessages view for AvalaraAvatax data provider.
Columns¶
Name | Type | Description |
---|---|---|
TransactionId [KEY] | Long | The transaction ID of the parent record. |
TransactionCode [KEY] | String | The transaction code of the parent record. |
TransactionStatus | String | The status of the transaction to which this line item belongs. |
RefersTo | String | Information about what object in your request this message refers to. |
Summary | String | A brief summary of what this message tells us. |
Severity | String | A category that indicates how severely this message affects the results. |
Source | String | The name of the code or service that generated this message. |
Details | String | Detailed information that explains what the summary provided. |
TransactionParameters¶
TransactionParameters view for AvalaraAvatax data provider.
Columns¶
Name | Type | Description |
---|---|---|
TransactionId [KEY] | Long | The transaction ID of the parent record. |
TransactionCode [KEY] | String | The transaction code of the parent record. |
TransactionStatus | String | The status of the transaction to which this line item belongs. |
Name | String | The name of the parameter. |
Unit | String | The unit of measure of the parameter value. |
Value | String | The value of the parameter. |
TransactionSummary¶
TransactionSummary view for AvalaraAvatax data provider.
Columns¶
Name | Type | Description |
---|---|---|
TransactionId [KEY] | Long | The transaction ID this summary is associated to. |
TransactionCode [KEY] | String | A unique customer-provided code identifying this transaction. |
TransactionStatus | String | The status of the transaction to which this line item belongs. |
StateAssignedNo | String | The state assigned number of the jurisdiction that collects this tax. |
JurisName | String | The name of the jurisdiction that collects this tax. |
TaxGroup | String | Group code when special grouping is enabled. |
JurisCode | String | Jurisdiction Code for the taxing jurisdiction |
Region | String | Two or three character ISO region, state or province code, if applicable. |
JurisType | String | The type of jurisdiction that collects this tax. |
RateTypeCode | String | Indicates the code of the rate type. |
TaxSubType | String | The tax subtype of this tax. |
NonTaxable | String | The amount of the transaction that was non-taxable. |
TaxName | String | The name of the tax. |
TaxAuthorityType | Int | The unique ID of the Tax Authority Type that collects this tax. |
Tax | String | Tax amount - The calculated tax (Base * Rate). |
Taxable | String | Tax Base - The adjusted taxable amount. |
Country | String | Two character ISO-3166 country code. |
TaxCalculated | String | The amount of tax that AvaTax calculated for the transaction. |
Exemption | String | The amount of the transaction that was exempt. |
TaxType | String | The tax type of this tax. |
Rate | String | Tax Rate - The rate of taxation, as a fraction of the amount. |
TransactionTaxDetailsByTaxType¶
TransactionTaxdetailsbytaxtype view for AvalaraAvatax data provider.
Columns¶
Name | Type | Description |
---|---|---|
TransactionId [KEY] | String | |
TransactionCode | String | A unique customer-provided code identifying this transaction. |
TransactionStatus | String | The status of the transaction to which this line item belongs. |
TotalNonTaxable | String | Total non taxable by tax type. |
LinkedTaxSubTypeDetails | String | Tax subtype details. |
TotalExempt | String | Total exempt by tax type. |
TotalTaxable | String | Total taxable amount by tax type. |
TotalTax | String | Total tax by tax type. |
TaxType | String | Tax Type. |
Stored Procedures¶
Stored procedures are function-like interfaces that extend the functionality of the connector beyond simple SELECT/INSERT/UPDATE/DELETE operations with Avalara.
Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Avalara, along with an indication of whether the procedure succeeded or failed.
Avalara Connector Stored Procedures¶
Name | Description |
---|---|
CommitTransaction | Commit a transaction for reporting. |
DownloadBatch | Download a single batch file. |
RefundTransaction | Refund a transaction. |
VoidTransaction | Void a transaction. |
CommitTransaction¶
Commit a transaction for reporting.
Input¶
Name | Type | Required | Description |
---|---|---|---|
TransactionCode | String | True | Code of the transaction to commit. |
DownloadBatch¶
Download a single batch file.
Input¶
Name | Type | Required | Description |
---|---|---|---|
CompanyId | String | True | The ID of the company that owns this batch |
BatchId | String | True | The ID of the batch object. |
FileId | String | True | The primary key of this batch file object. |
FilePath | String | False | The full path (including the file's name) where the file will be downloaded. |
Result Set Columns¶
Name | Type | Description |
---|---|---|
FileData | String | The file's data encoded in Base64 will be outputted if FilePath and FileStream are not set. |
RefundTransaction¶
Refund a transaction.
Input¶
Name | Type | Required | Description |
---|---|---|---|
TransactionCode | String | True | Code of the transaction to refund. |
RefundTransactionCode | String | True | The transaction code for the refund. This code will be saved to the ReturnInvoice transaction, and does not need to match the code of the original sale. |
RefundDate | String | True | The date of the refund. |
RefundType | String | True | Type of this refund. To submit a full refund, specify Full. To refund only specific lines from the transaction, specify Partial and indicate the lines you wish to apply in the refundLines field. To refund the tax that was paid in the previous transaction, specify TaxOnly. To issue a percentage-based discount, specify Percentage. |
RefundPercentage | String | False | The percentage for refund. This value only applies if you choose Percentage or Partial refund type. |
RefundLines | String | False | A comma separated list of the line numbers to refund for. |
ReferenceCode | String | False | A user-defined reference field containing information about this refund. |
VoidTransaction¶
Void a transaction.
Input¶
Name | Type | Required | Description |
---|---|---|---|
TransactionCode | String | True | Code of the transaction to void. |
System Tables¶
You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.
Schema Tables¶
The following tables return database metadata for Avalara:
- sys_catalogs: Lists the available databases.
- sys_schemas: Lists the available schemas.
- sys_tables: Lists the available tables and views.
- sys_tablecolumns: Describes the columns of the available tables and views.
- sys_procedures: Describes the available stored procedures.
- sys_procedureparameters: Describes stored procedure parameters.
- sys_keycolumns: Describes the primary and foreign keys.
- sys_indexes: Describes the available indexes.
Data Source Tables¶
The following tables return information about how to connect to and query the data source:
- sys_connection_props: Returns information on the available connection properties.
- sys_sqlinfo: Describes the SELECT queries that the connector can offload to the data source.
Query Information Tables¶
The following table returns query statistics for data modification queries:
- sys_identity: Returns information about batch operations or single updates.
sys_catalogs¶
Lists the available databases.
The following query retrieves all databases determined by the connection string:
SELECT * FROM sys_catalogs
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The database name. |
sys_schemas¶
Lists the available schemas.
The following query retrieves all available schemas:
SELECT * FROM sys_schemas
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The database name. |
SchemaName | String | The schema name. |
sys_tables¶
Lists the available tables.
The following query retrieves the available tables and views:
SELECT * FROM sys_tables
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The database containing the table or view. |
SchemaName | String | The schema containing the table or view. |
TableName | String | The name of the table or view. |
TableType | String | The table type (table or view). |
Description | String | A description of the table or view. |
IsUpdateable | Boolean | Whether the table can be updated. |
sys_tablecolumns¶
Describes the columns of the available tables and views.
The following query returns the columns and data types for the Transactions table:
SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Transactions'
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the table or view. |
SchemaName | String | The schema containing the table or view. |
TableName | String | The name of the table or view containing the column. |
ColumnName | String | The column name. |
DataTypeName | String | The data type name. |
DataType | Int32 | An integer indicating the data type. This value is determined at run time based on the environment. |
Length | Int32 | The storage size of the column. |
DisplaySize | Int32 | The designated column's normal maximum width in characters. |
NumericPrecision | Int32 | The maximum number of digits in numeric data. The column length in characters for character and date-time data. |
NumericScale | Int32 | The column scale or number of digits to the right of the decimal point. |
IsNullable | Boolean | Whether the column can contain null. |
Description | String | A brief description of the column. |
Ordinal | Int32 | The sequence number of the column. |
IsAutoIncrement | String | Whether the column value is assigned in fixed increments. |
IsGeneratedColumn | String | Whether the column is generated. |
IsHidden | Boolean | Whether the column is hidden. |
IsArray | Boolean | Whether the column is an array. |
IsReadOnly | Boolean | Whether the column is read-only. |
IsKey | Boolean | Indicates whether a field returned from sys_tablecolumns is the primary key of the table. |
sys_procedures¶
Lists the available stored procedures.
The following query retrieves the available stored procedures:
SELECT * FROM sys_procedures
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The database containing the stored procedure. |
SchemaName | String | The schema containing the stored procedure. |
ProcedureName | String | The name of the stored procedure. |
Description | String | A description of the stored procedure. |
ProcedureType | String | The type of the procedure, such as PROCEDURE or FUNCTION. |
sys_procedureparameters¶
Describes stored procedure parameters.
The following query returns information about all of the input parameters for the SelectEntries stored procedure:
SELECT * FROM sys_procedureparameters WHERE ProcedureName='SelectEntries' AND Direction=1 OR Direction=2
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the stored procedure. |
SchemaName | String | The name of the schema containing the stored procedure. |
ProcedureName | String | The name of the stored procedure containing the parameter. |
ColumnName | String | The name of the stored procedure parameter. |
Direction | Int32 | An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters. |
DataTypeName | String | The name of the data type. |
DataType | Int32 | An integer indicating the data type. This value is determined at run time based on the environment. |
Length | Int32 | The number of characters allowed for character data. The number of digits allowed for numeric data. |
NumericPrecision | Int32 | The maximum precision for numeric data. The column length in characters for character and date-time data. |
NumericScale | Int32 | The number of digits to the right of the decimal point in numeric data. |
IsNullable | Boolean | Whether the parameter can contain null. |
IsRequired | Boolean | Whether the parameter is required for execution of the procedure. |
IsArray | Boolean | Whether the parameter is an array. |
Description | String | The description of the parameter. |
Ordinal | Int32 | The index of the parameter. |
sys_keycolumns¶
Describes the primary and foreign keys.
The following query retrieves the primary key for the Transactions table:
SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Transactions'
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
IsKey | Boolean | Whether the column is a primary key in the table referenced in the TableName field. |
IsForeignKey | Boolean | Whether the column is a foreign key referenced in the TableName field. |
PrimaryKeyName | String | The name of the primary key. |
ForeignKeyName | String | The name of the foreign key. |
ReferencedCatalogName | String | The database containing the primary key. |
ReferencedSchemaName | String | The schema containing the primary key. |
ReferencedTableName | String | The table containing the primary key. |
ReferencedColumnName | String | The column name of the primary key. |
sys_foreignkeys¶
Describes the foreign keys.
The following query retrieves all foreign keys which refer to other tables:
SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
PrimaryKeyName | String | The name of the primary key. |
ForeignKeyName | String | The name of the foreign key. |
ReferencedCatalogName | String | The database containing the primary key. |
ReferencedSchemaName | String | The schema containing the primary key. |
ReferencedTableName | String | The table containing the primary key. |
ReferencedColumnName | String | The column name of the primary key. |
ForeignKeyType | String | Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key. |
sys_primarykeys¶
Describes the primary keys.
The following query retrieves the primary keys from all tables and views:
SELECT * FROM sys_primarykeys
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the key. |
SchemaName | String | The name of the schema containing the key. |
TableName | String | The name of the table containing the key. |
ColumnName | String | The name of the key column. |
KeySeq | String | The sequence number of the primary key. |
KeyName | String | The name of the primary key. |
sys_indexes¶
Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.
The following query retrieves all indexes that are not primary keys:
SELECT * FROM sys_indexes WHERE IsPrimary='false'
Columns¶
Name | Type | Description |
---|---|---|
CatalogName | String | The name of the database containing the index. |
SchemaName | String | The name of the schema containing the index. |
TableName | String | The name of the table containing the index. |
IndexName | String | The index name. |
ColumnName | String | The name of the column associated with the index. |
IsUnique | Boolean | True if the index is unique. False otherwise. |
IsPrimary | Boolean | True if the index is a primary key. False otherwise. |
Type | Int16 | An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3). |
SortOrder | String | The sort order: A for ascending or D for descending. |
OrdinalPosition | Int16 | The sequence number of the column in the index. |
sys_connection_props¶
Returns information on the available connection properties and those set in the connection string.
When querying this table, the config connection string should be used:
jdbc:cdata:avalara:config:
This connection string enables you to query this table without a valid connection.
The following query retrieves all connection properties that have been set in the connection string or set through a default value:
SELECT * FROM sys_connection_props WHERE Value <> ''
Columns¶
Name | Type | Description |
---|---|---|
Name | String | The name of the connection property. |
ShortDescription | String | A brief description. |
Type | String | The data type of the connection property. |
Default | String | The default value if one is not explicitly set. |
Values | String | A comma-separated list of possible values. A validation error is thrown if another value is specified. |
Value | String | The value you set or a preconfigured default. |
Required | Boolean | Whether the property is required to connect. |
Category | String | The category of the connection property. |
IsSessionProperty | String | Whether the property is a session property, used to save information about the current connection. |
Sensitivity | String | The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms. |
PropertyName | String | A camel-cased truncated form of the connection property name. |
Ordinal | Int32 | The index of the parameter. |
CatOrdinal | Int32 | The index of the parameter category. |
Hierarchy | String | Shows dependent properties associated that need to be set alongside this one. |
Visible | Boolean | Informs whether the property is visible in the connection UI. |
ETC | String | Various miscellaneous information about the property. |
sys_sqlinfo¶
Describes the SELECT query processing that the connector can offload to the data source.
Discovering the Data Source's SELECT Capabilities¶
Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.
Name | Description | Possible Values |
---|---|---|
AGGREGATE_FUNCTIONS | Supported aggregation functions. | AVG , COUNT , MAX , MIN , SUM , DISTINCT |
COUNT | Whether COUNT function is supported. | YES , NO |
IDENTIFIER_QUOTE_OPEN_CHAR | The opening character used to escape an identifier. | [ |
IDENTIFIER_QUOTE_CLOSE_CHAR | The closing character used to escape an identifier. | ] |
SUPPORTED_OPERATORS | A list of supported SQL operators. | = , > , < , >= , <= , <> , != , LIKE , NOT LIKE , IN , NOT IN , IS NULL , IS NOT NULL , AND , OR |
GROUP_BY | Whether GROUP BY is supported, and, if so, the degree of support. | NO , NO_RELATION , EQUALS_SELECT , SQL_GB_COLLATE |
STRING_FUNCTIONS | Supported string functions. | LENGTH , CHAR , LOCATE , REPLACE , SUBSTRING , RTRIM , LTRIM , RIGHT , LEFT , UCASE , SPACE , SOUNDEX , LCASE , CONCAT , ASCII , REPEAT , OCTET , BIT , POSITION , INSERT , TRIM , UPPER , REGEXP , LOWER , DIFFERENCE , CHARACTER , SUBSTR , STR , REVERSE , PLAN , UUIDTOSTR , TRANSLATE , TRAILING , TO , STUFF , STRTOUUID , STRING , SPLIT , SORTKEY , SIMILAR , REPLICATE , PATINDEX , LPAD , LEN , LEADING , KEY , INSTR , INSERTSTR , HTML , GRAPHICAL , CONVERT , COLLATION , CHARINDEX , BYTE |
NUMERIC_FUNCTIONS | Supported numeric functions. | ABS , ACOS , ASIN , ATAN , ATAN2 , CEILING , COS , COT , EXP , FLOOR , LOG , MOD , SIGN , SIN , SQRT , TAN , PI , RAND , DEGREES , LOG10 , POWER , RADIANS , ROUND , TRUNCATE |
TIMEDATE_FUNCTIONS | Supported date/time functions. | NOW , CURDATE , DAYOFMONTH , DAYOFWEEK , DAYOFYEAR , MONTH , QUARTER , WEEK , YEAR , CURTIME , HOUR , MINUTE , SECOND , TIMESTAMPADD , TIMESTAMPDIFF , DAYNAME , MONTHNAME , CURRENT_DATE , CURRENT_TIME , CURRENT_TIMESTAMP , EXTRACT |
REPLICATION_SKIP_TABLES | Indicates tables skipped during replication. | |
REPLICATION_TIMECHECK_COLUMNS | A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication. | |
IDENTIFIER_PATTERN | String value indicating what string is valid for an identifier. | |
SUPPORT_TRANSACTION | Indicates if the provider supports transactions such as commit and rollback. | YES , NO |
DIALECT | Indicates the SQL dialect to use. | |
KEY_PROPERTIES | Indicates the properties which identify the uniform database. | |
SUPPORTS_MULTIPLE_SCHEMAS | Indicates if multiple schemas may exist for the provider. | YES , NO |
SUPPORTS_MULTIPLE_CATALOGS | Indicates if multiple catalogs may exist for the provider. | YES , NO |
DATASYNCVERSION | The Data Sync version needed to access this driver. | Standard , Starter , Professional , Enterprise |
DATASYNCCATEGORY | The Data Sync category of this driver. | Source , Destination , Cloud Destination |
SUPPORTSENHANCEDSQL | Whether enhanced SQL functionality beyond what is offered by the API is supported. | TRUE , FALSE |
SUPPORTS_BATCH_OPERATIONS | Whether batch operations are supported. | YES , NO |
SQL_CAP | All supported SQL capabilities for this driver. | SELECT , INSERT , DELETE , UPDATE , TRANSACTIONS , ORDERBY , OAUTH , ASSIGNEDID , LIMIT , LIKE , BULKINSERT , COUNT , BULKDELETE , BULKUPDATE , GROUPBY , HAVING , AGGS , OFFSET , REPLICATE , COUNTDISTINCT , JOINS , DROP , CREATE , DISTINCT , INNERJOINS , SUBQUERIES , ALTER , MULTIPLESCHEMAS , GROUPBYNORELATION , OUTERJOINS , UNIONALL , UNION , UPSERT , GETDELETED , CROSSJOINS , GROUPBYCOLLATE , MULTIPLECATS , FULLOUTERJOIN , MERGE , JSONEXTRACT , BULKUPSERT , SUM , SUBQUERIESFULL , MIN , MAX , JOINSFULL , XMLEXTRACT , AVG , MULTISTATEMENTS , FOREIGNKEYS , CASE , LEFTJOINS , COMMAJOINS , WITH , LITERALS , RENAME , NESTEDTABLES , EXECUTE , BATCH , BASIC , INDEX |
PREFERRED_CACHE_OPTIONS | A string value specifies the preferred cacheOptions. | |
ENABLE_EF_ADVANCED_QUERY | Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side. | YES , NO |
PSEUDO_COLUMNS | A string array indicating the available pseudo columns. | |
MERGE_ALWAYS | If the value is true, The Merge Mode is forcibly executed in Data Sync. | TRUE , FALSE |
REPLICATION_MIN_DATE_QUERY | A select query to return the replicate start datetime. | |
REPLICATION_MIN_FUNCTION | Allows a provider to specify the formula name to use for executing a server side min. | |
REPLICATION_START_DATE | Allows a provider to specify a replicate startdate. | |
REPLICATION_MAX_DATE_QUERY | A select query to return the replicate end datetime. | |
REPLICATION_MAX_FUNCTION | Allows a provider to specify the formula name to use for executing a server side max. | |
IGNORE_INTERVALS_ON_INITIAL_REPLICATE | A list of tables which will skip dividing the replicate into chunks on the initial replicate. | |
CHECKCACHE_USE_PARENTID | Indicates whether the CheckCache statement should be done against the parent key column. | TRUE , FALSE |
CREATE_SCHEMA_PROCEDURES | Indicates stored procedures that can be used for generating schema files. |
The following query retrieves the operators that can be used in the WHERE clause:
SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'
Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.
Columns¶
Name | Type | Description |
---|---|---|
NAME | String | A component of SQL syntax, or a capability that can be processed on the server. |
VALUE | String | Detail on the supported SQL or SQL syntax. |
sys_identity¶
Returns information about attempted modifications.
The following query retrieves the Ids of the modified rows in a batch operation:
SELECT * FROM sys_identity
Columns¶
Name | Type | Description |
---|---|---|
Id | String | The database-generated ID returned from a data modification operation. |
Batch | String | An identifier for the batch. 1 for a single operation. |
Operation | String | The result of the operation in the batch: INSERTED, UPDATED, or DELETED. |
Message | String | SUCCESS or an error message if the update in the batch failed. |
sys_information¶
Describes the available system information.
The following query retrieves all columns:
SELECT * FROM sys_information
Columns¶
Name | Type | Description |
---|---|---|
Product | String | The name of the product. |
Version | String | The version number of the product. |
Datasource | String | The name of the datasource the product connects to. |
NodeId | String | The unique identifier of the machine where the product is installed. |
HelpURL | String | The URL to the product's help documentation. |
License | String | The license information for the product. (If this information is not available, the field may be left blank or marked as 'N/A'.) |
Location | String | The file path location where the product's library is stored. |
Environment | String | The version of the environment or rumtine the product is currently running under. |
DataSyncVersion | String | The tier of Sync required to use this connector. |
DataSyncCategory | String | The category of Sync functionality (e.g., Source, Destination). |
Advanced Configurations Properties¶
The advanced configurations properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure. Click the links for further details.
Property | Description |
---|---|
AuthScheme | The scheme used for authentication. Accepted entries are Basic and LicenseKey. |
UseSandbox | A boolean indicating whether you are using a Sandbox account. The provider makes requests to the production environment by default. |
User | The Avalara user account used to authenticate. |
Password | The password used to authenticate the user. |
AccountId | The account ID number of your Avalara account. |
LicenseKey | The license key generated by an account administrator on the Avalara website. |
Property | Description |
---|---|
CompanyCode | Only transaction information tied to this company code will be returned. |
Property | Description |
---|---|
SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
Property | Description |
---|---|
Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC. |
Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC. |
Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC. |
Property | Description |
---|---|
GenerateSchemaFiles | Indicates the user preference as to when schemas should be generated and saved. |
IncludeAdjustedTransactions | Whether or not to include adjusted transactions in the transaction listing. |
MaxRows | Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses. |
Other | These hidden properties are used only in specific use cases. |
Pagesize | The maximum number of results to return per page from Avalara when using client paging. |
PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
UserDefinedViews | A filepath pointing to the JSON configuration file containing your custom views. |
Authentication¶
This section provides a complete list of authentication properties you can configure.
Property | Description |
---|---|
AuthScheme | The scheme used for authentication. Accepted entries are Basic and LicenseKey. |
UseSandbox | A boolean indicating whether you are using a Sandbox account. The provider makes requests to the production environment by default. |
User | The Avalara user account used to authenticate. |
Password | The password used to authenticate the user. |
AccountId | The account ID number of your Avalara account. |
LicenseKey | The license key generated by an account administrator on the Avalara website. |
AuthScheme¶
The scheme used for authentication. Accepted entries are Basic and LicenseKey.
Possible Values¶
Auto
, Basic
, LicenseKey
Data Type¶
string
Default Value¶
Auto
Remarks¶
- Auto: Lets the driver decide automatically based on the other connection properties you have set.
- Basic: Set this to use Basic User / Password authentication.
- LicenseKey: Set to this to perform the authentication using the AccountId and the LicenseKey properties.
UseSandbox¶
A boolean indicating whether you are using a Sandbox account. The provider makes requests to the production environment by default.
Data Type¶
bool
Default Value¶
false
Remarks¶
If you are using a production account, this property can be left blank.
User¶
The Avalara user account used to authenticate.
Data Type¶
string
Default Value¶
""
Remarks¶
Together with Password, this field is used to authenticate against the Avalara server.
Password¶
The password used to authenticate the user.
Data Type¶
string
Default Value¶
""
Remarks¶
The User and Password
are together used to authenticate with the server.
AccountId¶
The account ID number of your Avalara account.
Data Type¶
string
Default Value¶
""
Remarks¶
The Account ID is listed in the upper right hand corner of the Avalara admin console.
LicenseKey¶
The license key generated by an account administrator on the Avalara website.
Data Type¶
string
Default Value¶
""
Remarks¶
You can generate a license key as follows:
- Log into Avalara as an account administrator.
- Select
Settings
. - Select
Reset License Key
.
Connection¶
This section provides a complete list of connection properties you can configure.
Property | Description |
---|---|
CompanyCode | Only transaction information tied to this company code will be returned. |
CompanyCode¶
Only transaction information tied to this company code will be returned.
Data Type¶
string
Default Value¶
DEFAULT
Remarks¶
Only transaction information tied to this company code will be returned.
SSL¶
This section provides a complete list of SSL properties you can configure.
Property | Description |
---|---|
SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
SSLServerCert¶
The certificate to be accepted from the server when connecting using TLS/SSL.
Data Type¶
string
Default Value¶
""
Remarks¶
If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.
This property can take the following forms:
Description | Example |
---|---|
A full PEM Certificate (example shortened for brevity) | -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE----- |
A path to a local file containing the certificate | C:\\cert.cer |
The public key (example shortened for brevity) | -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY----- |
The MD5 Thumbprint (hex values can also be either space or colon separated) | ecadbdda5a1529c58a1e9e09828d70e4 |
The SHA1 Thumbprint (hex values can also be either space or colon separated) | 34a929226ae0819f2ec14b4a3d904f801cbb150d |
If not specified, any certificate trusted by the machine is accepted.
Certificates are validated as trusted by the machine based on the System's trust store. The trust store used is the 'javax.net.ssl.trustStore' value specified for the system. If no value is specified for this property, Java's default trust store is used (for example, JAVA_HOME\lib\security\cacerts).
Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.
Schema¶
This section provides a complete list of schema properties you can configure.
Property | Description |
---|---|
Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC. |
Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC. |
Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC. |
Location¶
A path to the directory that contains the schema files defining tables, views, and stored procedures.
Data Type¶
string
Default Value¶
%APPDATA%\Avalara Data Provider\Schema
Remarks¶
The path to a directory which contains the schema files for the connector (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location
property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.
If left unspecified, the default location is "%APPDATA%\Avalara Data Provider\Schema" with %APPDATA%
being set to the user's configuration directory:
Platform | %APPDATA% |
---|---|
Windows | The value of the APPDATA environment variable |
Mac | ~/Library/Application Support |
Linux | ~/.config |
BrowsableSchemas¶
This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
Data Type¶
string
Default Value¶
""
Remarks¶
Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.
Tables¶
This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.
Data Type¶
string
Default Value¶
""
Remarks¶
Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the connector.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.
Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
Views¶
Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.
Data Type¶
string
Default Value¶
""
Remarks¶
Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the connector.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.
Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
Miscellaneous¶
This section provides a complete list of miscellaneous properties you can configure.
Property | Description |
---|---|
GenerateSchemaFiles | Indicates the user preference as to when schemas should be generated and saved. |
IncludeAdjustedTransactions | Whether or not to include adjusted transactions in the transaction listing. |
MaxRows | Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses. |
Other | These hidden properties are used only in specific use cases. |
Pagesize | The maximum number of results to return per page from Avalara when using client paging. |
PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
UserDefinedViews | A filepath pointing to the JSON configuration file containing your custom views. |
GenerateSchemaFiles¶
Indicates the user preference as to when schemas should be generated and saved.
Possible Values¶
Never
, OnUse
, OnStart
, OnCreate
Data Type¶
string
Default Value¶
Never
Remarks¶
This property outputs schemas to .rsd files in the path specified by Location.
Available settings are the following:
- Never: A schema file will never be generated.
- OnUse: A schema file will be generated the first time a table is referenced, provided the schema file for the table does not already exist.
- OnStart: A schema file will be generated at connection time for any tables that do not currently have a schema file.
- OnCreate: A schema file will be generated by when running a CREATE TABLE SQL query.
Note that if you want to regenerate a file, you will first need to delete it.
Generate Schemas with SQL¶
When you set GenerateSchemaFiles
to OnUse
, the connector generates schemas as you execute SELECT queries. Schemas are generated for each table referenced in the query.
When you set GenerateSchemaFiles
to OnCreate
, schemas are only generated when a CREATE TABLE query is executed.
Generate Schemas on Connection¶
Another way to use this property is to obtain schemas for every table in your database when you connect. To do so, set GenerateSchemaFiles
to OnStart
and connect.
IncludeAdjustedTransactions¶
Whether or not to include adjusted transactions in the transaction listing.
Data Type¶
bool
Default Value¶
false
Remarks¶
Whether or not to include adjusted transactions in the transaction listing.
MaxRows¶
Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Data Type¶
int
Default Value¶
-1
Remarks¶
Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Other¶
These hidden properties are used only in specific use cases.
Data Type¶
string
Default Value¶
""
Remarks¶
The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.
Specify multiple properties in a semicolon-separated list.
Integration and Formatting¶
Property | Description |
---|---|
DefaultColumnSize | Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000. |
ConvertDateTimeToGMT | Determines whether to convert date-time values to GMT, instead of the local time of the machine. |
RecordToFile=filename | Records the underlying socket data transfer to the specified file. |
Pagesize¶
The maximum number of results to return per page from Avalara when using client paging.
Data Type¶
string
Default Value¶
1000
Remarks¶
The Pagesize
property affects the maximum number of results to return per page from Avalara when using client paging. A higher value will return more results per page, but may also cause a timeout exception. If you must use client paging on your server and have a fast server, setting a higher Pagesize
may be desirable. We recommend testing various sizes against a large resultset to determine what works best in your use case.
PseudoColumns¶
This property indicates whether or not to include pseudo columns as columns to the table.
Data Type¶
string
Default Value¶
""
Remarks¶
This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".
Timeout¶
The value in seconds until the timeout error is thrown, canceling the operation.
Data Type¶
int
Default Value¶
60
Remarks¶
If Timeout
= 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.
If Timeout
expires and the operation is not yet complete, the connector throws an exception.
UserDefinedViews¶
A filepath pointing to the JSON configuration file containing your custom views.
Data Type¶
string
Default Value¶
""
Remarks¶
User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json
. The connector automatically detects the views specified in this file.
You can also have multiple view definitions and control them using the UserDefinedViews
connection property. When you use this property, only the specified views are seen by the connector.
This User Defined View configuration file is formatted as follows:
- Each root element defines the name of a view.
- Each root element contains a child element, called
query
, which contains the custom SQL query for the view.
For example:
{
"MyView": {
"query": "SELECT * FROM Transactions WHERE MyColumn = 'value'"
},
"MyView2": {
"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
}
}
Use the UserDefinedViews
connection property to specify the location of your JSON configuration file. For example:
"UserDefinedViews", C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json
Note that the specified path is not embedded in quotation marks.