Introduction

IP1 system interfaces with Avalara software to automate transactional sales tax compliance and reporting for our US customers. IP1 interfaces order information and the transaction quantity and amounts by order by item to Avalara for tax calculations.

Avalara calculates the sales tax and breaks it down by various tax jurisdictions and the transaction details in IP1 is updated to reflect the calculated tax amounts. The interfaces occur at various stages in the life cycle of the sales order or credit transaction to reflect the updated tax amounts. The final interface is on invoicing when the tax is posted in Avalara. Any transactions with errors are returned to IP1, which can be fixed and reposted or manually posted to Avalara.

Prior to the Avalara interface, tax calculations in IP1 is based on the offline tax table which is refreshed by EOD/EOM processes, however the system can be configured for online tax calculations at order level if required.

Customers with tax exemption certificates can also be maintained in exemptions database where about to expire certificates are managed via reminder letters. These definitions will depend on the license agreement with Avalara for the Tax Exemption certifications. Tax exemption number on customer masterfile can also exempt customers from sales tax.

Setup

View setup requirements

IP1 Business rules

The following business rules have to be setup to handle Avalara taxation. These business rules must be setup with support from Iptor IP1 consultants. It is critical to understand the setting of control files and how it works. Control files must be setup correctly for the system to operate as intended. Any changes to the control files setup should be addressed cautiously and in consultation with Iptor IP1 consultants.

Note	This document does not cover customised setup tasks of specific companies. Deviations from this setup should be covered by setup tasks written by individual companies.

Control file/Program	Description
BNADS/AV-IF1 Avalara interface	Setup the following Avalara interface details: BSURL (Avalara server URL) environment specific USRNM (Avalara user name) environment specific USRPW (Avalara user password) environment specific CTXC (customer classification for o/seas tax exemption) DEBUG (temporary HTTP logs) ENVID (IP1 environment identifier) ITXC (item classification for Avalara item tax codes) MAXDL (maximum detail lines) – number of order lines in an order to calculate the tax breakdown. NOEXM (No exemption) – set to ‘Y’* if using the Avalara Tax Exemption Certificate. NOTE: If set to ‘Y’ then IP1 Customer Exemptions and related processes in IP1 will NOT be applicable i.e. all IP1 Tax numbers (whether it is from customer masterfile or from order entry) will be ignored. If using Avalara Tax Exemption Certificate then please refer to Avalara documentation on tax exemption. *NOPST (Y=suppress posting to Avalara) All other entries are system codes for API calls.
BNADS/AV-IF2 Avalara company id	Holds mapping for each IP1 company and Item level 1 that is to interface to Avalara with a unique Avalara company ID. Must be populated after the IP1 company has been setup in Avalara. See IP1 setup below on how to populate this control file.
BNADS/AV-NX2 Avalara Transaction Group Definition	Setup transaction groups for different tax regions. These transaction groups can be setup in BNADS/AV-NX1 to interface to Avalara for tax calculation and posting.
BNADS/AV-NX1 Avalara Transaction Group	Define which transaction groups (BNADS/AV-NX2) are to interface to Avalara for tax calculation and posting.
BNADS/AV-NX3 Avalara Company Nexus	Holds all the tax regions (country/state) where a company is responsible for collecting and remitting taxes. This control file is updated with all the tax regions registered for the company in Avalara when an IP1 company is linked to Avalara company ID. See IP1 setup below on how to populate this control file. Note: The offline rates (which is used for all offline calculations and P&A in IP1) is imported/updated only for tax regions specified in this control file. When a new tax region is added to a company in Avalara this control file must be updated.
BNADS/AV-EX1 Avalara interface – entity use code	The reasons for tax exemptions, used to specify why a customer is being exempted from tax. These reasons are downloaded from Avalara and its used in the Customer exemptions database in IP1.
BNADS/AV-FNL Avalara finalised status	System defined Avalara tax transaction status used in the US Tax transaction file (Final tax flag). Blank = calculated using offline tax table N = calculated by Avalara Y = Posted to Avalara (on invoicing) U = Failed posting to Avalara due to connection failure or invoice error R = reposted to Avalara after U status via Work with Unposted Transaction. M = manually posted to Avalara, external to IP1 (the status has to be manually updated in IP1 to reflect this). X = Excluded from Avatax calculation (not defined in BNADS/AV-NX3 as a Nexus state)
BNADS/AV-TC1 Avalara interface – Default Canadian tax rate	Holds default Canadian tax rate by province which is used for *offline* tax calculations for Canadian companies in IP1. Note: This control file will need to to maintained for changes to tax rates in Canada.
BNADS/AV-TC2 Avalara interface – tax code	List of active Avalara tax codes. IP1 item codes are mapped to these tax codes for processing in Avalara. This control file is automatically updated from Avalara in the IP1 EOD/EOM process.
BNADS/AV-TC2M Avalara interface – tax code (item level map)	Holds item level mapping to Avalara tax code using item classification IC-TXUS. This control file eliminates update of individual items in the customer classification maintenance.
BNADS/AV-TC3 Avalara interface – tax region	Set up all the tax regions by country by state.
BNADS/AV-TC4 Avalara interface – tax region levy item	Holds tax item codes to incorporate additional US taxes (levy) by region (country & state) by effective/expiry date. Tax item must be a valid non stock item in the Item master file which must be correctly mapped to Avalara tax code using the item classification specified in BNADS/AV-IF1. Note: The tax items specified in this control file will be automatically added to the order if its within the region and date range specified. The tax amount is to be updated in Avalara system. For customers who are not yet using Avalara the amount is setup in this control file.
TMSCRM/AV-CNTRY Address country code	Map US and CA (2 char only) and assign to new format group for Canada and USA.
TMSCRM/ADFT2 Address (and Name) Format definition	Review field length for address lines 3, 4 and 5 (City, State & Postcode) for US customers: 3 = 40A 4 = 10A 5 = 9A
TMSCRM/AV-DFN Address Validation / Search Definition	This control file holds the address search definition keys and the validation programs by format group and type. The search keys are based on the following: Melissa data – no search key required Old BNA customers – 4 5 (State and Postcode_ New US & Canada format group – 3 4 5 (City, State and Postcode) Set up the following external validation programs ExtPgm = BNA010 (Melissa data) – if using Melissa data for delivery address validation Extpgm2 = BNO155XA (Avalara interface tax validation program) – will validate the address has a correct tax jurisdiction.
TMSCRM/AV-DFN1 Address Validation / Search Definition	Set length for zip code for USA format group to 5 for IP1 database. This is for IP1 internal processing only, the users can still enter zip 9.
TMSCRM/AV-DFN2 Address Validation – bypass	Address validation can be bypassed for any programs or a suite of programs specified in this control file.
TMSCRM/AV-DFN3 Address Validation 2 – Hard Error	On address validation, if an address is incorrect or not yet registered, a temporary override can be allowed with a warning. This will apply for programs not defined in this control file. If a program is defined in this control file then NO temporary override will be allowed, the program will issue a hard error. The address must be valid for that program to continue.
TMSDS/TAX-EXT Tax external program call	Holds tax programs for Avalara taxation. Retrieval Program – by default it should be set to BNO155C, so line tax is calculated from the offline Avalara tax table. Direct Generate – BNO155E (to update line tax from order) Inquiry – BNI155A (Transaction detail tax inquiry) Credit from Inv – BNO155F (to generate credit from invoice) Workfile from Act – BNO155G (to generate tax workfile)
TMSDS/TAX-EXT2 Tax external program call (order level)	Holds Avalara interface program to update and post sales tax online at order level. If blank then tax calculation is offline.
TMSDS/IM-IL1 Item Level One	If the company is a distributor with publisher clients, then add all the publisher clients (market place) for the company against the relevant item level codes in this control file. Ensure all the publisher clients are entered in Avalara against the Avalara company under market place. See Avalara setup for this.
TMSDS/CC-TYPES Customer Classification Types	Include new customer classification (TMSDS/CC-TXUS) for overseas resident tax exemption.
TMSDS/CC-TXUS Avalara Overseas Exemption	New customer classification to hold Avalara overseas resident exemption codes. Setup applicable tax codes for overseas residents.
TMSDS/IC-TXUS Item tax code for Avalara	Item classification to map IP1 items to Avalara tax code using commodity type. Blank item classification entry can be used for items with no classification.
TMSDS/IC-TYPES Item Classification Types	Include new item classification (TMSDS/IC-TXUS) for mapping IP1 items to Avalara tax code.
TMSDS/CM-CTSS Contact Type Selection Sequence	Add new role ‘TAX’ for customer contact for tax.
TMSDS/PEND-DSC Pending descriptions	Add user defined pending codes for order tax validation, customer tax exemption and obsolete item tax codes as required.
TMSDS/PEND-DOC Pend for specific document type	Add pending conditions for the above new pending codes for order tax validation, customer tax exemption and obsolete item tax codes as required.
TMSDS/PEND-DO2 User specific pending check(addition to PEND-DOC)	Holds additional plugin programs for the user defined order pending reasons. Link pend codes to the following as required: BNO155B for tax validation BNW150C for customer exemption expiry BNO155BT for obsolete item tax codes
TMSDS/SEQ-PDOC Search sequence for pending by document	Setup pending check sequence.
Maintain pending roles to review pending orders	Include all the order pending reasons to be reviewed by Pending Role.
TMSAR/PEND-DSC Pending Descriptions	Add user defined pending code for claim tax validation as required (similar setup to TMSDS/PEND-DSC).
TMSAR/PEND-DOC Pend for specific document type	Add pending conditions for the new pending code for claims tax validation as required (similar setup to TMSDS/PEND-DOC).
TMSAR/PEND-DO2 User specific pending check(addition to PEND-DOC)	Holds additional plugin programs for the user defined claims pending reasons. Link pend code to either of the following as required: BNO155B for tax validation BNO155BC for tax validation AND update (highly recommended)
TMSAR/CH-TYPD Cust Hist: Notes gen for field value chg(type dfn)	New field TXEEXP added for Customer exemption reminder letter note.
TMSCRM/CH-IF CRM Incident from Customer History	Define the CRM incident system template that is used to automatically create CRM incident from Customer history for the exemption letter note type. The following system template (CH-TEEX) for the CRM incident must exist in the CRM system templates.
BNADS/AV-GL Avalara Tax – GL posting	Maintain the GL accounts for Avalara tax postings.
BNADS/TAX-FRTS Freight split	Set up this control file if you require freight to be split by Item level 1.

Avalara setup

Task	Steps
Setup/create IP1 company in Avalara	Each IP1 company that is to interface to Avalara must be setup in Avalara first. Add company details Sign in to Avalara, goto Settings>Manage companies. Click Add a company. Click Get started on the information page. Enter the company details If the company is a distributor with publisher clients, then flag the company as an online marketplace. The publisher clients must then be entered as market places (see Add marketplaces for the company). Click Next. *Where do you collect tax* Enter the primary (physical) address of the company. Validate the address and click Next. The company would be setup to collect tax based on this primary business location. If the company is registered to collect tax in other regions then click Yes, add more US regions & select the US regions to collect tax as required. Enter and validate the addresses of the entered regions. If the company is registered to collect tax in other countries, then click Yes, add VAT/GST countries & select the countries to collect tax as required. Click complete . On the confirmation page click Done with tax jurisdictions to save the company details. *Activate the company* Click Activate Company to activate/enable tax calculations on its transactions. The effective date for the company defaults to the current date
Add IP1 company code	In Avalara, goto Settings>Manage companies, select the newly created company and click Details. Enter the IP1 company code and click Save. Each Avalara company setup must have a unique IP1 company code (one to one relationship).
Add effective date for tax collection	This is the date the tax jurisdiction will collect & remit tax from and its critical in determining which transactions are processed. When you add a company in Avalara, the effective tax date for the tax jurisdictions defaults to a year prior to current date. Important: Effective tax date must be set back at least a few years to allow credit from the past to be eligible for tax collections & remittance. To change the Effective tax date; In Avalara, goto Settings>Where you collect tax, select the newly created company and region and click Details. Edit/update the Effective date and click Save.
Add marketplaces for the company	If the company is a distributor with publisher clients, then all the publisher clients in IP1 are entered as marketplaces in Avalara to allow for tax reporting by location. In Avalara, goto Settings>Company locations and marketplaces, and click Marketplaces. Existing market places are listed. Click Add a location and enter/select the following details. Location code = 3 character publisher client Friendly name = name of the publisher client Type = ‘Marketplace’ Category – select from the list of categories for the publisher client Enter the address of the publisher client and and click validate the address. Enter the expiry/effective date for the publisher client’s business activity at this address. Select the attribute as ‘*Is marketplace‘. Click Save this location. Important*: The publisher clients entered here must also be maintained in IP1 in control file TMSDS/IM-IL1 as a market place for the relevant item level code as shown below.

IP1 setup

Task	Description
Link IP1 company code to Avalara company ID	Each IP1 company that is to interface to Avalara must be mapped to a Avalara company code. The mapping can by Company code and Item level 1 to allow postings by Item level 1. After IP1 company is setup in Avalara, the company ID from Avalara can be mapped to IP1 company code in control file BNADS/AV-IF2. Expand menu Other Modules > Other Regions > US Options > US and Canadian Taxation and double- click Avalara Company Connection (BNO156). All the IP1 and Avalara company mapping is listed. Company IDs that are highlighted are not connected i.e. not mapped correctly in BNADS/AV-IF2. Select the company to update, click option Connect. This will register IP1 company code to Avalara company ID in control file BNADS/AV-IF2 and it will also update BNADS/AV-NX3 with all the tax regions registered for the company in Avalara. Important: This option must be run whenever a new tax region is added to a company in Avalara as the offline tax rates are imported/updated only for the tax regions specified on control BNADS/AV-NX3. For additional information on this see BNO156 Avalara Connection.
Turn on/off Avalara tax calculations by country and/or region	For tax collection & remittance, company must be setup in Avalara system as described above to receive the transactions, however in IP1 you can turn on/off submissions to Avalara for different tax jurisdictions (country/states) and publisher clients. To turn on/off tax calculations by country/state or publisher client in IP1: Define transaction groups by country and region in control file BNADS/AV-NX2. Setup these transaction groups in control file BNADS/NX1 by company/Item level 1 to enable Avalara tax interface/calculations.
Add any customer exemptions	IP1 Customer exemptions would only apply if Avalara Tax exemption is not used, i.e. control file BNADS/AV-IF1 setting NOEXM # ‘Y’. Tax exemption in IP1 can be set by either of the following: Tax number in Customer masterfile (DSCM00P) Customer Tax Exemption (BNW150) Set up tax exemption codes in customer classification control file TMSDS/CC-TXUS*, and assign the tax exemption code to the customers via Customer classification setup for the customers as required.
Map Avalara tax codes to IP1 items	Avalara has its own list of tax codes that IP1 item codes must be mapped to. These tax codes are populated in IP1 control file BNADS/AV-TC2 from Avalara in the IP1 EOD/EOM process. To map IP1 item codes to Avalara tax codes: Nominate an item classification type for the commodity source field ITXC on control file BNADS/AV-IF1. In the Item classification control file (IC-TXUS), map Commodity type to Avalara tax code. Blank entry is for items with no classification. Setup the item level/item class mapping to the Avalara tax code using the commodity type from TMSDS/IC-TXUS in BNADS/AV-TC2M. This will eliminate the update of individual items classification with the commodity type (steps 4 and 5 below). To individually classify items using the above item classification type by commodity type, go to Base Modules>Masterfiles>Database Management>Database Management>Item Masterfiles>Item Masterfiles>IM Masterfile Maintenance, select/enter the item code with option E=Classification* and click OK. Update the item classification with the commodity type. Note: Items with no classification setup will use the default blank entry. *Important: IP1 freight items must be mapped to the correct Avalara tax code. Note*: Avalara has a fall back code if IP1 fails to provide an appropriate tax code.

Transactions Processing

Sales order processing

The sales order transactions from IP1 is interfaced to Avalara at various stages in the life cycle of the order. At these stages (order entry, pending, picking, despatch and invoicing), the order may change due to inclusions, minimum charge, freight re-calculations and short picks etc. that would affect the tax rates and amounts. At each interface the tax is recalculated and IP1 Tax transaction files are updated with the tax rate and amount for non-exempted customers.

Order entry

At order entry, the tax can be calculated online if configured in TMSDS/TAX-EXT2. If not configured then the tax is calculated offline without interfacing to Avalara. This offline tax calculation at line level is temporary using an offline Avalara tax table, which is automatically refreshed daily by EOD run. With offline tax calculation, the tax amounts displayed may not be as accurate as from Avalara online calculation. If tax exemption number is nominated in the customer masterfile then the offline tax would not be calculated.

With online tax calculation at order entry, the system will check for tax exemption before interfacing to Avalara.

After the tax calculation (offline/online), IP1 tax transaction files will be updated with tax rate and amount by State, Country and City and Others (includes any other special tax). Order inquiry will then show the tax rates and the amounts.

Order pending check

Order Pending check (DSO040) will check TMSDS/PEND-DOC and TMSDS/PEND-DO2 and call the following programs if defined on TMSDS/PEND-DO2:

Avalara interface pending check program (BNO155B) – to further analyse the order for tax calculation and to determine if the order is to pend for taxation purposes.
Customer exemption pending check program (BNW150C) – to check if the order should pend based on exemption expiry.
Obsolete line tax code (BNO155BT) – will pend the order if the line tax code is obsolete in Avalara.

Important: If the order pends for either of the reasons above, it must be addressed and fixed before the order is approved to enable an error free interface on invoicing for Avatax calculation.

Picking/Despatch

To allow for any short picks, Pick confirmation and Despatch confirmation programs will automatically call the Avalara tax update program again to interface to Avalara and re-examine the changed order and update the tax rate and amount again.

Invoicing

On invoicing, after any changes to the order with freight re-calculation and inclusion of extra charges to the order etc., the Avalara tax update program will be called again to interface to Avalara and re-examine the changed order and update and post the tax rate and amount to Avalara. This would be the final call to Avalara for the order and it will be called by document number in case orders are merged into a single invoice.

On successful update and posting, the Final tax flag on US Transaction file would be updated to ‘Y’ for posted.

Credit & returns processing

On claims entry there is no interface to Avalara for tax calculations. The interfaces for claims are at:

Claims pending check – for pending check and tax update.
Claims inquiry function – can dynamically calculate the tax for display (no posting to Avalara).
Claims invoicing – will interface for posting to Avalara.

Claims pending check

Claims Pending check (CLO040) will check TMSAR/PEND-DOC and TMSAR/PEND-DO2. Based on the setup it can call either of the following programs:

Avalara interface pending check program (BNO155B) – will analyse the claim for Avalara tax calculation and determine if the claim is to pend for taxation purposes.
Avalara pending check/update (BNO155BC) – will check for pending as well as calculate the tax/update the tax transactions files for the claim.

Note: As there is no interface to Avalara on claims entry it is highly recommended to setup TMSAR/PEND-DO2 with BNO155BC so Avatax is reflected on the claims transactions.

Important: If the claim pends, it must be addressed and fixed before the claim is approved to enable an error free interface on claims invoicing for Avatax calculation.

Claims inquiry

Claims inquiry function will dynamically interface to Avalara to re-calculate the tax and update IP1 tax transactions files.

Claims invoicing

On claims invoicing, if the claim is allocated to an invoice, the tax will be calculated and interfaced to Avalara for posting along with the original invoice date and address. Avalara will use the original invoice date and address to obtain the rate and pro rata the tax amount to the different tax jurisdictions accordingly.

If the claim is not allocated to an invoice, then the tax will be calculated offline in IP1 and interfaced to Avalara along with any customer exemptions. On interface to Avalara tax would be re-calculated and updated to IP1 tax transaction files.

Accounts Receivable Journals

Any journals created in Accounts Receivable that will include the tax write-off portion will not be posted to Avalara. This will require a manual entry transaction into Avalara system.

Transaction inquiry

You can view the tax details of the transaction and find out its Avalara tax status through BNA Transaction detail inquiry.

Expand menu Other Options > General Inquiries > Document Inquires and double- click Document/Process Number Inquiry.
Enter the Document type (A or B (invoice or credit) and the Document number and click OK.
Select the item and click option=Tax. If the transaction was processed by Avalara then BNA Transaction Detail Tax Inquiry (BNI155B) would display with the transaction details along with the total tax and the breakdown amounts if its calculated. The Final status flag would indicate the status of the tax transaction.

Note

Tax breakdown by state/county/city/other is dependent on the value for field *MAXDL in control file BNADS/AV-IF1. If this field is active and has a value then the tax breakdown will only be calculated if the number of order lines in an order is below the specified *MAXDL value.

If the Final status flag = U (failed posting to Avalara), you can use function Last log to view the error message.

Note: Recal function would be available if the Final status flag = blank (offline calculation) or N (calculated by Avalara) to allow Avalara interface to calculate/recalculate the tax.

Other Processes

Work with unposted transactions

Any transactions that failed to post to Avalara and was returned with Final tax flag = U can be re-posted to Avalara via IP1 to Avalara interface or manually posted to Avalara (external to IP1) and flagged accordingly in IP1. See BNW160 Work with Unposted Tax Transactions.

Maintain customer exemptions

IP1 Tax exemptions will only apply if BNADS/AV-IF1 setting *NOEXM is not set to use Avalara Tax Exemption Certificate. See IP1 Business rules setup above. If using IP1 tax exemption then the tax exemption can be setup in either of the following:

Maintaining Tax number in the customer masterfile
Setup other tax exemption database via BNW150 Tax Exemption.

The Tax number in Customer masterfile takes precedence i.e. other tax exemption number will only be used if Tax number is not provided in Customer masterfile.

Generate tax exemptions reminder

Reminders can be generated for customers with expired or about to expire exemption certificates. See BNW150 Customer Tax Exemption – Reminder.

View transactions in Avalara

Once the transactions are posted in Avalara, its visible in Avalara.

Sign in to Avalara, goto Transactions>Transactions. List of posted or committed transactions (invoiced in IP1) for the previous month and for country US is listed by default. You can change the default selection fields or use Filters and Apply to list specific transactions. The transactions can be sorted by Document code or Document date or as required.

Field	Description
Doc code	IP1 Document type-IP1 Document number.
Doc date	IP1 Document date
Doc status	Committed – posted in Avalara after it was invoiced in IP1. On invoicing IP1 interfaces to Avalara with instructions to post in Avalara. Uncommitted – not posted; does not get reported in Avalara Void – cancelled in Avalara Locked – Transactions become locked and can’t be edited once they’ve been reported on a filed return.
Cust/Vendor code	IP1 customer code as on the invoice.
Region	Tax region; US state
Amount	Total amount on the document.
Not taxed	Portion of the total amount that is not to be taxed
Taxable	Portion of the total amount that is to be taxed.
Tax	Total calculated tax amount for the document.

Select and click a document to view its details.

Field	Description
Tax date	Tax calculation date in Avalara system.
Company	IP1 company name as setup in Avalara.
Exemption applied	Yes/No will indicate if exemption is applied.
Reference code	Customer reference from the IP1 transaction header.
Importer of record	Currently not applicable.
Invoice currency	Currency on the invoice
Reporting currency	Tax reporting currency used by Avalara.
Entity use code	Used to specify the reason for customer tax exemption, as setup in system defined control file BNADS/AV-EX1.
Ship from	Warehouse address. Note: Must be setup in IP1.
Ship to	Delivery address of the order.
Line	Order process number- order line number (unique). Note: IP1 will send order lines at subline zero level only i.e. for packs & system, only the pack item will be sent, not at component level.

Avalara GL journal generation

See Avalara GL journal generation & posting

Inquiries & Reports

Sales versus GL tax inquiry

This inquiry allows you to compare/reconcile Avalara sales tax interface against the Avalara GL journal transactions for posting. It can be run either by period or for a select date range. This inquiry is in addition to the excel report generated when the journals are created via BNO010 AVALARA G/L Journal Generation (above). You can run this inquiry to view any differences. The differences can be reconciled and the journals re-generated before posting to General Ledger.

Expand menu Other Modules > Other Regions > US Options and double- click G/L Posting Reconciliation. BNI010A US Tax Reconciliation for Sales and G/L panel appears.
Enter the Period or the Date range as required and click OK. Sales and GL tax amounts is listed by Process numbers.
Use function F10=Show All/Show Diff to view all transactions or only those with discrepancies.

Sales tax report by state

See Avalara Sales tax report.