Transactions
Transactions table for AvalaraAvatax data provider.
Select
The driver 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 driver. 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. |
| 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 |