Cmdlets for TaxJar

Build 23.0.8839

Orders

Lists existing order transactions.

Select

The cmdlet will use the TaxJar API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the cmdlet.

  • TransactionDate supports the following operators: <,<=,=,>,>=
  • TransactionID supports the following operators: =,IN
  • Provider supports the following operator: = (By default the Provider is set to "api" which means that only data that has been created from the API will be retrieved.)

For the following query:

SELECT * FROM TableName
We have these 2 cases:
  • StartDate connection property is not set. The cmdlet will retrieve data of the last 3 months.
  • StartDate connection property is set. The cmdlet will retrieve data from the StartDate specified, until today.
The following queries are processed server side:
    SELECT * FROM TableName WHERE TransactionID = '200'
    SELECT * FROM TableName WHERE Provider = 'ebay'
    SELECT * FROM TableName WHERE TransactionID IN ('123', '122')
    SELECT * FROM TableName WHERE TransactionDate <= '2020/05/15' AND TransactionDate >= '2014/05/15'
    SELECT * FROM TableName WHERE TransactionDate < '2015/05/15' AND TransactionDate > '2014/05/15'
    SELECT * FROM TableName WHERE TransactionDate < '2015/05/15'
    SELECT * FROM TableName WHERE TransactionDate > '2015/05/15'
    SELECT * FROM TableName WHERE TransactionDate >= '2015/05/15'
    SELECT * FROM TableName WHERE TransactionDate = '2015/05/15'

Insert

To add a Order, at least the TransactionID, TransactionDate, ToCountry, ToZip, ToState, Amount, Shipping, SalesTax need to be specified. Also we can execute an insert in 2 ways.
Using an aggregate to specify the line items:

INSERT INTO Orders (TransactionID, UserID, TransactionDate, Provider, ExemptionType, FromCountry, FromZip, FromState, FromCity, FromStreet, ToCountry, ToZip, ToState, ToCity, ToStreet, Amount, Shipping, SalesTax, LineItemsAggregate)
        VALUES ('123', '2', '2015/05/05', 'api', 'non_exempt', 'US', '', 'NC', '', '', 'US', '90002', 'CA', 'Los Angeles', '123 Palm Grove Ln', '15', '0', 1, 
     '[
        {
          "id": 2,
          "quantity": 2,
          "product_identifier": "22-22222-2",
          "description": "Fuzzy Widget",
          "unit_price": 5,
          "sales_tax": 0.0
        },
        {
          "id": 1,
          "quantity": 1,
          "product_identifier": "11-11111-1",
          "description": "Fuzzy Widget",
          "unit_price": 5,
          "sales_tax": 0.0
        }
      ]')
Or using temporary tables to specify the line items:
INSERT INTO [OrderLineItems#TEMP] (ID, Quantity, ProductIdentifier, Description, ProductTaxCode, UnitPrice, Discount, SalesTax) VALUES ('1', '1', '11-11111-1', 'description 1', '', '5', '', '0.0')
INSERT INTO [OrderLineItems#TEMP] (ID, Quantity, ProductIdentifier, Description, ProductTaxCode, UnitPrice, Discount, SalesTax) VALUES ('2', '2', '22-22222-2', 'description 2', '', '5', '', '0.0')
INSERT INTO Orders (TransactionID, UserID, TransactionDate, Provider, ExemptionType, FromCountry, FromZip, FromState, FromCity, FromStreet, ToCountry, ToZip, ToState, ToCity, ToStreet, Amount, Shipping, SalesTax, LineItemsAggregate) 
        "VALUES ('123', '2', '2015/05/05', 'api', 'non_exempt', 'US', '', 'NC', '', '', 'US', '90002', 'CA', 'Los Angeles', '123 Palm Grove Ln', '15', '0', 1, 'OrderLineItems#TEMP')

Update

Similarly to the Insert operation we can update an item in 2 ways.
Using an aggregate to specify the line items:

    UPDATE Orders SET TransactionID = '111', UserID = '2', TransactionDate = '2015/05/05', Provider = 'api', ExemptionType = 'non_exempt', FromCountry = 'US', FromZip = '', FromState = 'NC', 
    FromCity = '', FromStreet = '', ToCountry = 'US', ToZip = '90002', ToState = 'CA', ToCity = 'Los Angeles', ToStreet = '123 Palm Grove Ln', Amount = '15', Shipping = '0', SalesTax = 1, 
    LineItemsAggregate = '[
    {
      "id": 2,
      "quantity": 2,
      "product_identifier": "22-22222-2",
      "description": "Fuzzy Widget",
      "unit_price": 5,
      "sales_tax": 0.0
    },
    {
      "id": 1,
      "quantity": 1,
      "product_identifier": "11-11111-1",
      "description": "Fuzzy Widget",
      "unit_price": 5,
      "sales_tax": 0.0
    }
  ]'
And using temporary tables to specify the line items:
INSERT INTO [OrderLineItems#TEMP] (ID, Quantity, ProductIdentifier, Description, ProductTaxCode, UnitPrice, Discount, SalesTax) VALUES ('1', '1', '11-11111-1', 'description 1', '', '5', '', '0.0')
INSERT INTO [OrderLineItems#TEMP] (ID, Quantity, ProductIdentifier, Description, ProductTaxCode, UnitPrice, Discount, SalesTax) VALUES ('2', '2', '22-22222-2', 'description 2', '', '5', '', '0.0')
UPDATE Orders SET TransactionID = '123', UserID = '2', TransactionDate = '2015/05/05', Provider = 'api', ExemptionType = 'non_exempt', FromCountry = 'US', FromZip = '', FromState = 'NC', FromCity = '', FromStreet = '', ToCountry = 'US', ToZip = '90002', ToState = 'CA', ToCity = 'Los Angeles', ToStreet = '123 Palm Grove Ln', Amount = '15', Shipping = '0', SalesTax = 1, LineItemsAggregate = 'OrderLineItems#TEMP' WHERE TransactionID = '123'

Delete

In order to delete an Order the TransactionID needs to be specified, for ex.

DELETE FROM Orders WHERE TransactionID = '100'

Columns

Name Type ReadOnly Description
TransactionID [KEY] String False

Unique identifier of the given order transaction.

UserID Int False

Unique identifier of the user who created the order transaction.

TransactionDate Date False

The date/time the transaction was originally recorded.

Provider String False

Source of where the transaction was originally recorded.

ExemptionType String False

Type of exemption for the order: wholesale, government, marketplace, other, non_exempt, or null.

FromCountry String False

Two-letter ISO country code of the country where the order shipped from.

FromZip String False

Postal code where the order shipped from (5-Digit ZIP or ZIP+4).

FromState String False

Two-letter ISO state code where the order shipped from.

FromCity String False

City where the order shipped from.

FromStreet String False

Street address where the order shipped from.

ToCountry String False

Two-letter ISO country code of the country where the order shipped to.

ToZip String False

Postal code where the order shipped to (5-Digit ZIP or ZIP+4).

ToState String False

Two-letter ISO state code where the order shipped to.

ToCity String False

City where the order shipped to.

ToStreet String False

Street address where the order shipped to.

Amount Double False

Total amount of the order with shipping, excluding sales tax.

Shipping Double False

Total amount of shipping for the order.

SalesTax Double False

Total amount of sales tax collected for the order.

LineItemsAggregate String False

Street address where the order shipped to.

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 23.0.8839