Skip to main content
Avalara Help Center

Data Import Settings

This article applies to:Avalara Excise

 

This document applies to Enterprise licenses.  Content last updated 5/10/2016. 
Help Center documentation is confidential and proprietary to Avalara clients.

 

Assumption: You are familiar with the manual manipulation of the Business Entity and Location data records in the Maintenance menu, and the functions of Schedule Scenarios and Profiles as they apply to your company’s import process.  

Data Settings for Schedule Transaction Import

There two Schedule Transactions Importer programs (STI) available to clients to load their data files.

  • STI that is built into the Returns Excise software
  • STI that is an external application installed on the operating system

The data settings work the same for both programs, with the menu options differences listed below in italics.  The screenshots in the instructions are from the built-in program in Returns Excise.  The instructions for the external application are the same, though they look slightly different, and are made on the Avalara Data Adapter tab.

Data settings - Transactions Import


Data Settings tab located in the Returns Excise built-in Schedule Transactions Importer (on the Add New Record screen) is divided into 6 different data setting option groups, plus the tab to Save Default Values.

  • General
  • Business Entity Import
  • Locations Import
  • Tax Session Assignment
  • Transaction Update Behavior
  • Email Notification
  • Save Default Values

​Data settings - Schedule Transaction Importer external application

The Data Settings tab located in the Returns Excise built-in Schedule Transactions Importer is divided into 7 different data setting option groups.

  • Avalara Authentication - refer to STI install document
  • General
  • Business Entity Import
  • Locations Import
  • Tax Session Assignment
  • Transaction Update Behavior
  • Email Notification

General

Contains 3 settings that apply to the import process.

  1. Batch Size sets the number of transactions that will be submitted at a time.  The maximum size for Company 0 is 1000.
    • To change the batch size, click Maintenance > Companies then click the Settings tab.
    • Choose Schedule Transactions Import from the dropdown, and filter on the phrase batch size.
    • Click on the record, then on the Copy tab.
    • Change the Value to your new batch size and click Insert.  Delete or change this copied record to reset the batch size to a different value.
  2. Check the Apply Schedule Profiles box to use schedule profiles in conjunction with schedule scenarios to determine schedule transaction assignment.
  3. Schedule transaction entry complete - begin workflow kicks off the next step in the configured Workflow process.

Business Entity Import

The Business Entity Import setting allows the user to decide how to create and/or update a business entity from an import file.

  1. Click Tax Filing > Transactions Import or the Schedule Transactions Import widget from the Home page.
  2. Click Add New Record.  If you are importing a file, select it on the Data File tab.  The data settings defaults can be set and saved without actually importing a file.
  3. Click the Data Settings tab and then Business Entity Import to review the options.
    SnagIt0080.png

Set default ID type/code

  1. The system automatically check marks the option to Set Default Id Type/Id Code to FEIN to a generic value of 999999999. This means that import records missing the Id Type/Id Code will assign the transactions to schedules with a field value of 999999999 and show as a validation error unless  a different Id Type is specified.  Use the dropdown and then edit the value field to change it.
  2. Unchecking the Set Default box will still attempt to insert the record if it can be assigned to a schedule code.  It can be sent to a ZZ schedule if a scenario is specifically set up to assign records where buyer/seller/carrier/etc. is missing from the transaction, so that you can manually correct it. 

Immediately terminate business entities

  1. This setting is unchecked by system default.  
  2. Checking this box will add the business entity with an obsolete date 1 second prior to the effective date.
  3. This invalidates the records, so that you can filter out and validate business entity records that are added by the import process before they are used in a tax filing.
    SnagIt0083.png

Insert record options

Once a Business Entity has a locked Business Account that is locked, you can modify only the effective/obsolete dates for that business entity.  The import process will create the business entity and business account based on your import file using one of the 4 insert options.

  1. Match Only - Inserts a record only if there is an existing business entity/account record found.  If it doesn't find a match it will not insert a new business entity/account record,  nor will it not update an existing one.
  2. Insert Unmatched - Creates a new business entity if an existing one is not found.
  3. Update Unlocked - If the details of a business entity have changed, like physical address, it will update the business entity if it does not have any locked business accounts.
  4. Copy Locked - If one or more business accounts have a locked date and the details of a business entity have changed, a copy will be created using the new details. Accounts will be created based on your import file for the new business entity. Important: The old business entity will be marked as obsolete.  This step copies only the business entity, not the business accounts associated with it. 

Override Matching Priority Assignments

Use this option to set the order of fields by priority during the matching process.  In the example, I want the import to first match by Custom Id, then by Id Type/Id Code and finally by Legal Name.

  1. Check the Override Matching Priority Assignments box.
  2. Use the up or down arrows to set a value of 1, 2 or 3 for each field.
  3. In this example, the fields are 0, 2, 2.
     
    • Custom Id = 0 means the matching process bypasses that field.
    • Id Type/Id Code = 2 means that it looks at this field second.
    • Legal Name = 2 means that this field second.  Id Type/Id Code and Legal Name set to the same value means that they both must be matched.

Import Business Entities

This option imports only to the business entity table.  It does not affect schedule transactions.

  1. Select a file on the Data File tab.
  2. Click Import Business Entities.
  3. Changes to business account details can be made by the import step as well.

Locations Import

Immediately terminate new locations

  1. This setting is unchecked by system default.  
  2. Checking this box will add the location with an obsolete date 1 second prior to the effective date.
  3. This invalidates the records, so that you can filter out and validate location records that are added by the import process before they are used in a tax filing.

Insert record options

Once a Location is locked, you can modify only the effective/obsolete dates for that location.  The import process will create the location based on your import file using one of the 4 insert options.

  1. Match Only - Inserts a record only if there is an existing location record found.  If it doesn't find a match it will not insert a new location record,  nor will it not update an existing one.
  2. Insert Unmatched - Creates a location if an existing one is not found.
  3. Update Unlocked - If the details of a location have changed, like physical address, it will update the location if it does not have any locked locations.
  4. Copy Locked - If one or more locations have a locked date and the details of a location have changed, a copy will be created using the new details. 

Exact Match Options

  1. Click the check box next to the location elements that you want to match exactly to your import file.  The defaults of Address1, Address2, City and Postal Code are already checked.  
  2. Country Code, Region/State and Description must always be checked, and they cannot be changed on this screen.  
    • If any of these 3 are not checked, find and remove overrides for Country, Region/State and Description in the Maintenance > Company > Settings by selecting the category Schedule Transaction Import.  
      • Filter on Company ID not equal to 0 (zero) to find overrides.
        SnagIt0002.png
  3. The more fields that are selected, the fewer matches will be found.

Import Locations

This option imports only to the Locations table.  It does not affect schedule transactions.

  1. Select a file on the Data File tab.
  2. Click Import Locations.
    SnagIt0110.png

Tax Session Assignment

Related document is Taxpayer Preferences.  

This is where you can select choices for assigning raw transactions to a session.​

  • This is where you can select choices for assigning raw transactions to a session.
  • Raw transactions are records not pre-assigned to a specific tax period in the import file.
  • You can choose whether or not to apply the values in Maintenance > Taxpayer Preferences per import file.
  • The settings contained in the taxpayer preferences menu are written in italics in the text below.
  1. The default Primary setting is Calculate Based on Taxpayer Preferences.
     
    • Calculate Based on Taxpayer Preferences applies the Late Load Data Type.
      • If no tax session matching its criteria is open, it will create one when the Process Original is set to Yes.  
      • If an original session matches and is marked as Filed, then an amended sequence will be created when Process Amended is set to Yes.
    • Target Period Based on Taxpayer Preferences determines the tax session based on the Late Load Cutoff Months setting when the bill of lading/invoice date does not match the filing period.
      • If no tax session matching its criteria is open, it will create one when the Process Original is set to Yes..  
      • If an original session matches and is marked as Filed, then an amended sequence will be created when Process Amended is set to Yes.
    • Target Period Ignoring Late Load Cutoff Months bypasses the taxpayer preferences for Late Load Cutoff Months.
  2. The Secondary default is None.  If records do not meet the primary option, the secondary is used if one is selected.
    • Calculate Based on Taxpayer Preferences can be selected as the secondary only if  the primary is set to Target Period Based on Taxpayer Preference or Target Period Ignoring Late Load Cutoff Months.
  3. Set the Only import transactions for the following tax sessions options in the lower part of the window to limit your selections.  The * indicates all.
    • Country
    • Jurisdiction Code
    • Taxpayer Type Code
    • Terminal Code
    • End Period Date

Transaction Update Behavior

This setting tells the import process how to handle a record in the import file that already exists in the schedule transactions.

  1. Insert All (results in duplicates) inserts every record that matches the import criteria into schedule transactions, including records that already exist.
  2. Update transactions with matching SourceSystemIds looks for existing transactions with the same SourceSystemId value, deletes them and inserts the new record with its updated field information.
    • SourceSystemId is the reference number of the record from source provided in the import file.  It is usually assigned by a back office process, and is not system-generated in Returns Excise.

Email Notification

The user can be notified at certain milestones during the import process..  This is helpful for very large files that require long processing times and quickly finding transactions that do not meet criteria to be assigned to a session.  

  • Start of data mapping is when the transactions begin to process.
  • Completion of transmission is when the end of the import file is reached. 
  • Completion of import processing is when the insertion process has completed.
  1. The email address is the one associated with the user login.  
    • The email message subject is Avalara Returns Excise [Platform - Production or User Acceptance] Data Import Step [number] File Name [exact name of import file].  
    • The sender is support@avalara.net .  Do not reply to this mailbox.  It is an automated message and replies are auto deleted.
  2. The text of the message lists:
    • Notification date of when the message was sent

    • Company for which the file processed

    • Status of whether or not the file contained errors

    • Link to the software

    • Job Description with the file name, number of batches and number of records processed

  3.  A second email notification can be enabled to send a zipped CSV file containing any unassigned transactions.  
    • To enable this, click Maintenance > Companies then click the Settings tab.
    • Choose Schedule Transactions Import from the dropdown, and filter on the word transmission.
    • Click on the record, then on the Copy tab.
    • Change the Value to TRUE and click Insert.  Delete or change this copied record to turn off the unassigned transactions notification.
    • The email message subject is Avalara Excise Unassigned Transactions for Load ID [load ID number].

If you are not receiving emails after setting up notifications, check your email's spam or junk folder.  Since this is an automated message it might be filtered on your network.

Save Default Values

This option applies only to the built-in STI in Excise Returns.  To apply your changes to all future imports, save the new values as default.  Click the Save button.  You may still change values  at the time of import for individual files.

excisehelpcenter@avalara.com

  • Was this article helpful?