TDV Adapter for Avalara

Build 21.0.8137

Transactions

Transactions table for AvalaraAvatax data provider.

Select

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

Copyright (c) 2022 CData Software, Inc. - All rights reserved.
Build 21.0.8137