Populating the Transaction Import Template
Please follow the below instructions on how to populate the Default Transaction Import Template for General Sales:
- Grouping:invoiceNumber (Recommended Field)
- Invoice Date (Required Field)
- Date of the invoice, can be populated as either YYYY-MM-DD OR MM/DD/YYYY, which will be converted to the preceding format upon upload
- Invoice Total Amount (Recommended Field)
- Total amount of the Invoice
- If each of your Invoices are just 1 line, then this will be the same value as the Revenue amount.
- If a unique Invoice has multiple Line ID's, this field should be the sum of the Revenue amounts for each Line ID on the Invoice.
- Customer Account (Recommended Field)
- A unique identifier for your customer. Can be an ID or a Name.
- Line ID (Required Field)
- Unique ID for each line on the invoice.
- If a unique Invoice only has 1 line item, this value will typically be 1.
- If a unique Invoice has multiple line items, this value will typically start at 1 and increase sequentially.
- Item Number (Required OR Recommended Field)
- Unique ID for your Product/Service. Utilization of this field requires that the Item Number be mapped to a psCode within CereTax. This field does not need to be populated if you will be using the psCode field, but can still be optionally provided.
- Item Description (Optional Field)
- Description of your Product/Service.
- Business Type (Required Field)
- The type of Business your company identifies as. This should be the same value for every line in the Import File. Available options are:
- 01 - Retail (Default)
- 02 - Manufacturing Retailer
- Note: Because these values contain leading zeros (0), you will want to ensure the format of these cells in the import file is Text.
- Customer Type (Required Field)
- The type of Customer you are selling to. This value can be different for each Customer. Available options are:
- 02 - Business/Commercial (Default)
- Note: Because this value contains a leading zero (0), you will want to ensure the format of this cell in the import file is Text.
- 12 - Retail
- Seller Type (Required Field)
- The type of Seller for this Invoice. Available options are:
- 01 - Physical Nexus in State (Default)
- 02 - Remote Seller (to be used for AL & TX only)
- If your Business is registered as a Remote Seller in Alabama and/or Texas, this value will return the fixed Remote Seller rates for these states.
- 03 - Direct Marketer (to be used for LA only)
- If your business is registered as a Direct Marketer in Louisiana, this value will return the fixed Direct Marketer rate.
- Note: Because these values contain leading zeros (0), you will want to ensure the format of these cells in the import file is Text.
- situs.taxSitusRule (Required Field)
- Defines the address type that will be used to calculate tax on the line. Available options are:
- T - Ship To
- V - Ship From
- P - Order Placement
- I - Order Approval
- psCode (Required Field)
- The CereTax taxability category. Utilization of this field does not require Product Mapping configuration within CereTax. The Item Number & Item Description fields can still be optionally populated.
- Revenue (Required Field)
- The total revenue amount for the associated Line ID. CereTax is not performing any calculation based on Revenue X Quantity.
- Note: If the file being uploaded is inclusive of tax amounts that were already calculated, the Revenue Amount should also include the already calculated tax amount for the associated line.
- Revenue Includes Tax (Required Field)
- Indicates whether the revenue amount for this Line Item already includes tax. Available options are:
- 0 – No tax in revenue (Default). CereTax will calculate tax on the provided revenue amount.
- 1 - Revenue includes tax. An Adjusted Revenue amount will be displayed in the Transactions screen after importing the file & in any CereTax reporting, indicating the tax exclusive portion of the revenue.
- units.quantity (Required Field)
- Quantity of Units. Default value is 1 unit.
- CereTax is not performing any calculation based on Revenue x Quantity.
- units.type (Required Field)
- Type of Units. Available options are:
- 01 - Price (Default)
- Note: Because this value contains a leading zero (0), you will want to ensure the format of this cell in the import file is Text.
The following section provides instruction on the available Address fields within the Default Import File, using Ship To Address as the Tax Situs Rule. If an alternative Tax Situs Rule is used, the shipTo portion of the field name would be replaced with the alternative value (ex. shipFrom, orderPlacement, orderApproval).
- {shipTo}Address.addressLine1 (Recommended Field)
- Address line 1.
- Ex. 3440 Preston Ridge Rd
- {shipTo}Address.addressLine2 (Optional Field)
- {shipTo}Address.city (Recommended Field)
- {shipTo}Address.state (Required Field)
- {shipTo}Address.postalCode (Required Field)
- Address postal code. Can be US or Canadian postal code values.
- For US, if the full 9-digit postal code is provided, it must be entered in one of the following formats:
- ######### (ex. 300053817)
- #####-#### (ex. 30005-3817)
- ##### #### (ex. 30005 3817)
- For any postal codes that begin with zero (0), those cells should be formatted as text.
- For Canada, the postal code must be entered in one of the following formats:
- A#A A#A (ex. H1A 0A2)
- A#AA#A (ex. H1A0A2)
- {shipTo}Address.latitude (Optional Field)
- Latitude of address location, if known. Can be used in place of the Postal Code field in conjunction with the Longitude field.
- {shipTo}Address.longitude (Optional Field)
- Longitude of address location, if known. Can be used in place of the Postal Code field in conjunction with the Latitude field.
- {shipTo}Address.validateAddress (Optional Field)
- Determines if the address should be validated before the tax calculation. Available options are:
- FALSE - When set to false, the address will not be validated before the tax calculation.
- TRUE - When set to true, the address will be validated before the tax calculation. If set to true, one of the following field combinations must be included in the address object:
- addressLine1 + City + State
- addressLine1 + postalCode
Once the Transaction Import Template is populated, it can be saved in either .CSV or .XLSX format.
Transaction Import Templates
The following tables are pre-populated sample templates for each available Tax Situs Rule that can be exported into Excel:
Transaction Import Template for General Sales - Ship To Address
Transaction Import Template for General Sales - Ship From Address
Transaction Import Template for General Sales - Order Placement Address
Transaction Import Template for General Sales - Order Approval Address
