TTE data schema and data fields

TTE data schema - all

TTE tables and data fields - all

TTE_entity
Field #	Field name	PK	FK	Null	Type	Width	Description
1	AccountID	X		N	String	50	Unique identifier for the Avalara account
2	CompanyID	X		N	String	50	Unique identifier for the company
3	CompanyName		X	N	String	60	Name of the company associated with the CompanyID
4	EffectiveDateTime		X	N	DateTime	23	Date and time the company goes into effect
5	EndDateTime		X	N	DateTime	23	Date and time the company expires
6	RecordCreatedTimestamp		X	N	DateTime	23	Date and time this record is created

TTE_physical_location_region
Field #	Field name	PK	FK	Null	Type	Width	Description
1	AccountID	X		N	String	50	Unique identifier for the Avalara account
2	CompanyID	X		N	String	50	Unique identifier for the company
3	PhysicalLocationID	X		N	String	50	Unique identifier for the physical location (warehouse, store, kiosk, and so on)
4	PhysicalLocationName		X	N	String	60	Name of the physical location associated with the PhysicalLocationID
5	TaxRegionID		X	N	String	50	Unique identifier for the tax region
6	LastAssignTaxRegionID		X	Y	String	50	Previous TaxRegionID assigned to this PhysicalLocationID, if different
7	EffectiveDateTime		X	N	DateTime	23	Date and time the physical location to region mapping goes into effect
8	EndDateTime		X	N	DateTime	23	Date and time the physical location to region mapping expires
9	RecordCreatedTimestamp		X	N	DateTime	23	Date and time this record is created

TTE_postal_location_region
Field #	Field name	PK	FK	Null	Type	Width	Description
1	AccountID	X		N	String	50	Unique identifier for the Avalara account
2	CompanyID	X		N	String	50	Unique identifier for the company
3	PostalLocationID	X		N	String	50	Unique identifier for the postal location (5-digit postal code (US), 6-digit postal code (CAN), and so on)
4	TaxRegionID		X	N	String	50	Unique identifier for the tax region
5	EffectiveDateTime		X	N	DateTime	23	Date and time the postal location to region mapping goes into effect
6	EndDateTime		X	N	DateTime	23	Date and time the postal location to region mapping expires
7	LastAssignTaxRegionID		X	Y	String	50	Previous TaxRegionID assigned to this PostalLocationID, if different
8	RecordCreatedTimestamp		X	N	DateTime	23	Date and time this record is created

TTE_tax_region_tax_authority
Field #	Field name	PK	FK	Null	Type	Width	Description
1	AccountID	X		N	String	50	Unique identifier for the Avalara account
2	CompanyID	X		N	String	50	Unique identifier for the company
3	TaxRegionID	X		N	String	50	Unique identifier for the tax region
4	TaxRegionName		X	N	String	254	Name of the Tax Region associated to the TaxRegionID (combined country, state/province, county/parish, city/township, and district)
5	TaxRegionCountry		X	N	String	50	Name of the country associated with the TaxRegionID
6	TaxRegionStateProvince		X	N	String	50	Name of the state or province associated with the TaxRegionID
7	TaxAuthorityID	X		N	String	50	Unique identifier for the tax authority
8	EffectiveDateTime		X	N	DateTime	23	Date and time the tax region to tax authority mapping goes into effect
9	EndDateTime		X	N	DateTime	23	Date and time the tax region to tax authority mapping expires
10	RecordCreatedTimestamp		X	N	DateTime	23	Date and time this record is created

TTE_tax_authority
Field #	Field name	PK	FK	Null	Type	Width	Description
1	AccountID	X		N	String	50	Unique identifier for the Avalara account
2	CompanyID	X		N	String	50	Unique identifier for the company
3	TaxAuthorityID	X		N	String	50	Unique identifier for the tax authority
4	TaxAuthorityName		X	N	String	254	Name of the tax authority associated with the TaxAuthorityID (country, state or province, county or parish, city or township, or district)
5	TaxAuthorityLevel		X	N	String	50	Valid tax authority levels include: country, state or province, county or parish, city or township, and district
6	RoundingMethod		X	N	String	50	A method indicating how the rate should be rounded during calculation
7	RoundingDigits		X	N	Integer	1	The number of digits to which rounding is calculated
8	EffectiveDateTime		X	N	DateTime	23	Date and time the tax authority goes into effect
9	EndDateTime		X	N	DateTime	23	Date and time the tax authority expires
10	RecordCreatedTimestamp		X	N	DateTime	23	Date and time this record is created

TTE_discount
Field #	Field name	PK	FK	Null	Type	Width	Description
1	AccountID	X		N	String	50	Unique identifier for the Avalara account
2	CompanyID	X		N	String	50	Unique identifier for the company
3	DiscountID	X		N	String	50	Unique identifier for the discount
4	DiscountName		X	N	String	60	Name of the discount associated with the DiscountID (retail, vendor, and so on)
5	DiscountAllIndicator		X	Y	Boolean	1	Indicates whether the discount applies to all tax authorities
6	GrossInd		X	Y	Boolean	1	Indicates that the discount is taxable at the full item price
7	TaxAuthorityID		X	N	String	50	Unique identifier for the tax authority; identify authority applying to the discount if DiscountAllIndicator is false
8	EffectiveDateTime		X	N	DateTime	23	Date and time the discount goes into effect
9	EndDateTime		X	N	DateTime	23	Date and time the discount expires
10	RecordCreatedTimestamp		X	N	DateTime	23	Date and time this record is created

TTE_exemption_certificate
Field #	Field name	PK	FK	Null	Type	Width	Description
1	AccountID	X		N	String	50	Unique identifier for the Avalara account
2	CompanyID	X		N	String	50	Unique identifier for the company
3	ExemptType	X		N	String	50	Unique identifier for the exemption type
4	CertificateNumber	X		N	String	50	The certificate number assigned by the tax authority
5	CertificateHolderName		X	Y	String	50	The name of the organization or individual identified on the certificate
6	CertificateCountry		X	Y	String	50	Issuing country
7	CertificateStateProvince		X	Y	String	50	Issuing state or province
8	CertificateExpirationDate		X	N	DateTime	23	Date and time the certificate expires
9	RecordCreatedTimestamp		X	N	DateTime	23	Date and time this record is created

TTE_exemption_type
Field #	Field name	PK	FK	Null	Type	Width	Description
1	AccountID	X		N	String	50	Unique identifier for the Avalara account
2	CompanyID	X		N	String	50	Unique identifier for the company
3	ExemptType	X		N	String	50	Unique identifier for the exemption type
4	ExemptTypeName		X	N	String	50	Name of the exemption type associated with the ExemptTypeID (resale, not-for-profit, government, and so on)
5	ExemptAllAuthorityIndicator		X	Y	Boolean	1	Indicates whether the exemption applies to all tax authorities
6	ExemptAllTaxCodeIndicator		X	Y	Boolean	1	Indicates whether the exemption applies to all tax codes
7	TaxAuthorityID		X	Y	String	50	Unique identifier for the tax authority
8	TaxAuthorityName		X	Y	String	254	Name of the tax authority associated with the TaxAuthorityID (country, state or province, county or parish, city or township, or district)
9	TaxAuthorityLevel		X	Y	String	50	Valid tax authority levels include: country, state or province, county or parish, city or township, and district
10	ReducedBy		X	Y	Double	8,6	Tax percentage by which the rate should be reduced
11	TaxCodeID		X	Y	String	50	Unique identifier for the tax code
12	TaxCodeName		X	Y	String	254	Name of the tax code associated with the TaxCodeID
13	EffectiveDateTime		X	N	DateTime	23	Date and time the exemption type assignment goes into effect
14	EndDateTime		X	N	DateTime	23	Date and time the exemption type assignment expires
15	RecordCreatedTimestamp		X	N	DateTime	23	Date and time this record is created

TTE_tax_taxcode_rule
Field #	Field name	PK	FK	Null	Type	Width	Description
1	AccountID	X		N	String	50	Unique identifier for the Avalara account
2	CompanyID	X		N	String	50	Unique identifier for the company
3	RuleName		X	N	String	254	Unique rule name (concatenate CompanyID, TaxRegionID, TaxAuthorityID, TaxCodeID, TaxTypeID, TaxCodeRuleSequenceNumber)
4	TaxRegionID	X		N	String	50	Unique identifier for the tax region
5	TaxAuthorityID	X		N	String	50	Unique identifier for the tax authority
6	TaxCodeID	X		N	String	50	Unique identifier for the tax code
7	TaxTypeID	X		N	String	50	Unique identifier for the tax type
8	TaxTypeName		X	N	String	50	Name of the tax type associated with the TaxTypeID (sales, sellers use, rental, VAT, GST, and so on)
9	TaxName		X	N	String	50	The name of the tax or fee (for example, sales tax, 911 surcharge, PIF, and so on)
10	TaxCodeRuleSequenceNumber	X		N	Integer	3	Sequential number for the taxcode rule indicating the order in which the rule should be considered
11	TaxabilityDecision		X	N	String	50	Taxable or nontaxable
12	TaxHolidayIndicator		X	Y	Boolean	1	Indicates if the taxcode rule is for a tax holiday
13	TaxAtTransactionLevelIndicator		X	Y	Boolean	1	Indicates if tax should be applied at transaction level; otherwise, tax should be applied at the item level
14	CalculationMethodCode		X	N	Integer	1	If tax to be applied at transaction level, then: Transaction Level = 1, else Item Level = 2
15	InclusiveTaxIndicator		X	Y	Boolean	1	Indicates whether the tax amount is already included in the price (VAT)
16	CompoundIndicator		X	Y	Boolean	1	Indicates that multiple tax taxcode rules apply
17	CompoundSequenceNumber		X	Y	Integer	3	Sequential number indicating the order where multiple related tax taxcode rules should be applied. Example 1: No Compounding will contain Null within this field. Example 2: PST and GST tax taxcode rules with Compounding needed. TaxTypeID for PST applied first and the field will contain 1 for its value, TaxTypeID for GST applied second and the field will contain 2 for its value.
18	ReceiptPrintLanguage1		X	Y	String	254	User-defined field for receipt print language
19	ReceiptPrintLanguage2		X	Y	String	254	User-defined field for receipt print language
20	UserDefinedField1		X	Y	String	254	User-defined field
21	UserDefinedField2		X	Y	String	254	User-defined field
22	EffectiveDateTime		X	N	DateTime	23	Date and time the physical location to region mapping goes into effect
23	EndDateTime		X	N	DateTime	23	Date and time the physical location to region mapping expires
24	RecordCreatedTimestamp		X	N	DateTime	23	Date and time this record is created

TTE_tax_rate_rule
Field #	Field name	PK	FK	Null	Type	Width	Description
1	AccountID	X		N	String	50	Unique identifier for the Avalara account
2	CompanyID	X		N	String	50	Unique identifier for the company
3	TaxRegionID	X		N	String	50	Unique identifier for the tax region
4	TaxAuthorityID	X		N	String	50	Unique identifier for the tax authority
5	TaxCodeID	X		N	String	50	Unique identifier for the tax code
6	TaxTypeID	X		N	String	50	Unique identifier for the tax type
7	TaxRateRuleSequenceNumber	X		N	Integer	3	Sequential number for the tax rate rule indicating the order in which multiple tax rates should be applied when MinimumTaxableAmount and MaximumTaxableAmount apply (min/max, tier, and so on)
8	TaxCodeRuleSequenceNumber	X		N	Integer	3	Sequential number for the tax code rule indicating the order in which the rule should be considered
9	TaxRateStructure		X	N	String	50	The structure of the tax type (such as simple rate, flat tax amount, tier tax, bracket tax, and so on)
10	TaxRateClass		X	N	String	50	The classification of the rate (such as standard, luxury, reduced, and so on)
11	BracketNumber		X	Y	Integer	3	Use if TaxRateStructure contains Bracket; the number assigned to the bracket. If either TPPDefaultIndicator or ZeroRateDefaultIndicator is true, then assign 1, and then number sequentially starting with 2. Otherwise, start with 1 and number sequentially.
12	AmountRateSelect		X	N	Integer	1	When Rate = 1; when Amount = 2
13	Amount		X	Y	Double	8,2	The flat amount to apply
14	Rate		X	Y	Double	8,6	The percentage rate to apply
15	BreakPointType		X	N	String	50	Breakpoint to use when calculating tax. Values include PART and FULL: PART = tax part of the dollar amount for min/max or tier tax structures. FULL = tax entire dollar amount
16	TaxAboveThresholdAmountIndicator		X	Y	Boolean	1	Indicates tax applied to the amount over the dollar amount within MinimumTaxableAmount.
17	MinimumTaxableAmount		X	Y	Double	10,2	Use if there is a minimum taxable amount or tier tax. If amount is present, then TaxAboveThresholdAmountIndicator should be set to true
18	MaximumTaxableAmount		X	Y	Double	10,2	Use if there is a maximum tax amount or tier tax
19	EffectiveDateTime		X	N	DateTime	23	Date and time the tax rate rule goes into effect
20	EndDateTime		X	N	DateTime	23	Date and time the tax rate rule expires
21	RecordCreatedTimestamp		X	N	DateTime	23	Date and time this record is created

TTE_tax_combined
Field #	Field name	PK	FK	Null	Type	Width	Description
1	AccountID	X		N	String	50	Unique identifier for the Avalara account
2	CompanyID	X		N	String	50	Unique identifier for the company
3	TaxRegionID	X		N	String	50	Unique identifier for the tax region
4	TaxCodeID	X		N	String	50	Unique identifier for the tax code
5	TaxTypeID	X		N	String	50	Unique identifier for the tax type
6	AmountRateSelect		X	N	Integer	2	When Rate = 1; when Amount = 2
7	CombinedAmount		X	Y	Double	8,2	The combined flat amount to apply
8	CombinedRate		X	Y	Double	8,6	The combined percentage rate to apply
9	BreakPointType		X	N	String	50	Breakpoint to use when calculating tax. Values include PART and FULL; PART = tax part of the dollar amount for min/max or tier tax structures and FULL = tax entire dollar amount
10	TaxAboveThresholdAmountIndicator		X	Y	Boolean	1	Indicates tax applied to the amount over the dollar amount within MinimumTaxableAmount
11	MinimumTaxableAmount		X	Y	Double	10,2	Use if there is a minimum taxable amount or tier tax. If amount is present, then TaxAboveThresholdAmountIndicator should be set to true
12	MaximumTaxableAmount		X	Y	Double	10,2	Use if there is a maximum tax amount or tier tax
13	TaxabilityDecision		X	N	String	50	Taxable or nontaxable
14	TaxHolidayIndicator		X	Y	Boolean	1	Indicates if the tax code rule is for a tax holiday
15	CalculationMethodCode		X	N	Integer	2	If tax to be applied at transaction level, then: Transaction Level = 1, else Item Level = 2
16	InclusiveTaxIndicator		X	Y	Boolean	1	Indicates whether the tax amount is already included in the price (VAT)
17	CompoundIndicator		X	Y	Boolean	1	Indicates that multiple tax taxcode rules apply
18	CompoundSequenceNumber		X	Y	Integer	3	Sequential number indicating the order where multiple related tax taxcode rules should be applied. Example 1: No Compounding will contain Null within this field. Example 2: PST and GST tax taxcode rules with Compounding needed. TaxTypeID for PST applied first and the field will contain 1 for its value, TaxTypeID for GST applied second and the field will contain 2 for its value
19	CombinedReceiptPrintLanguage1		X	Y	String	254	User-defined field for receipt print language
20	CombinedUserDefinedField1		X	Y	String	254	User-defined field
21	EffectiveDateTime		X	N	DateTime	23	Date and time the tax combined goes into effect
22	EndDateTime		X	N	DateTime	23	Date and time the tax combined expires
23	RecordCreatedTimestamp		X	N	DateTime	23	Date and time this record is created

TTE_tax_bracket_assignment
Field #	Field name	PK	FK	Null	Type	Width	Description
1	AccountID	X		N	String	50	Unique identifier for the Avalara account
2	CompanyID	X		N	String	50	Unique identifier for the company
3	TaxRegionID	X		N	String	50	Unique identifier for the tax region
4	TaxCodeID	X		N	String	50	Unique identifier for the tax code
5	TaxTypeID	X		N	String	50	Unique identifier for the tax type
6	BracketNumber	X		N	Integer	3	The number assigned to the bracket. If either TPPDefaultIndicator or ZeroRateDefaultIndicator is true, then assign 1, and then number sequentially starting with 2. Otherwise, start with 1 and number sequentially
7	BracketFlagAssignment		X	N	String	50	Associated with the BracketNumber. Represents the position based on "flag" on/off indicators. The number of positions available is based on the bracket position number identified within TTE Configuration file (default is 4 positions). Example: If BracketNumber equals 2, then output equals 0,1,0,0 using the Bracket Position Number default
8	TaxabilityDecision		X	N	String	50	Taxable or nontaxable
9	CombinedRate		X	Y	Double	8,6	The combined percentage rate to apply
10	TaxRateClass		X	N	String	50	The classification of the rate (such as standard, luxury, reduced, and so on)
11	EffectiveDateTime		X	N	DateTime	23	Date and time the bracket assignment goes into effect
12	EndDateTime		X	N	DateTime	23	Date and time the bracket assignment expires
13	RecordCreatedTimestamp		X	N	DateTime	23	Date and time this record is created

TTE_tax_bracket
Field #	Field name	PK	FK	Null	Type	Width	Description
1	AccountID	X		N	String	50	Unique identifier for the Avalara account
2	CompanyID	X		N	String	50	Unique identifier for the company
3	TaxRegionID	X		N	String	50	Unique identifier for the tax region
4	TaxRegionName		X	N	String	254	Name of the tax region associated with the TaxRegionID (combined country, state or province, county or parish, city or township, and district)
5	Country		X	N	String	50	Name of the country assigned to the tax region
6	StateProvince		X	N	String	50	Name of the state or province assigned to the tax region
7	TaxCodeID	X		N	String	50	Unique identifier for the tax code
8	TaxTypeID	X		N	String	50	Unique identifier for the tax type
9	BracketNumber	X		N	Integer	3	The number assigned to the bracket. If either TPPDefaultIndicator or ZeroRateDefaultIndicator is true, then assign 1, and then number sequentially starting with 2. Otherwise, start with 1 and number sequentially
10	TPPDefaultIndicator		X	Y	Boolean	1	Indicates whether a default bracket number is assigned for standard TPP sales tax rate. If true, the BracketNumber will equal 1. Note: Indication of a default bracket for TPP is within the TTE Configuration file
11	ZeroRateDefaultIndicator		X	Y	Boolean	1	Indicates whether a default bracket number is assigned for zero tax rate and a Bracket is created. If true, the BracketNumber will equal 1 and a Zero Rate Bracket is created. Note: Indication of a default bracket for Zero Rate is within the TTE Configuration file
12	BracketLineCount		X	N	Integer	3	The end count of the RangeLineNumber for the bracket
13	CombinedRate		X	Y	Double	8,6	The combined percentage rate to apply
14	TaxRateClass		X	N	String	50	The classification of the rate (such as standard, luxury, reduced, and so on)
15	RangeLineNumber		X	N	Integer	3	The range line number in sequential order
16	StartRangeAmount		X	N	Double	10,2	Minimum taxable amount of the range
17	EndRangeAmount		X	N	Double	10,2	Maximum taxable amount of the range
18	RangeTaxAmount		X	N	Double	8,2	The flat amount to apply to the range, which is always 0.01
19	RangeTaxAmount2		X	N	Integer	3	The RangeTaxAmount expressed as a whole number (for example, 0.01 becomes 1)
20	RangeTaxAmount3		X	N	Double	8,2	The accumulated RangeTaxAmount
21	RepeatRange		X	N	Integer	3	The difference between the StartRangeAmount and EndRangeAmount expressed as a whole number (for example, StartRangeAmount = 0.00 and EndRangeAmount = 0.07, then RangeCount = 7)
22	RepeatRangeIndicator		X	N	Boolean	1	Indicates when a number pattern is found for the RepeatRange (RR) within the bracket (for example, RR1 = 12, RR2 = 13, RR3 = 13, RR4 = 12, RR5 = 13, RR6 = 13 -- repeat range pattern found at RR6)
23	EffectiveDateTime		X	N	DateTime	23	Date and time the bracket goes into effect
24	EndDateTime		X	N	DateTime	23	Date and time the bracket expires
25	RecordCreatedTimestamp		X	N	DateTime	23	Date and time this record is created

TTE table relationships - all

Parent entity	Child entity
TTE_tax_entity	TTE_discount
TTE_tax_entity	TTE_taxcode
TTE_tax_entity	TTE_exemption_certificate
TTE_tax_entity	TTE_exemption_type
TTE_tax_entity	TTE_postal_location
TTE_tax_entity	TTE_physical_location
TTE_postal_location	TTE_tax_region_tax_authority
TTE_physical_location	TTE_tax_region_tax_authority
TTE_tax_region_tax_authority	TTE_tax_authority
TTE_taxcode	TTE_taxcode_rule
TTE_taxcode_authority	TTE_taxcode_rule
TTE_taxcode_rule	TTE_tax_rate_rule
TTE_taxcode	TTE_tax_combined
TTE_tax_region_tax_authority	TTE_tax_combined
TTE_taxcode	TTE_tax_bracket_assignment
TTE_tax_region_tax_authority	TTE_tax_bracket_assignment
TTE_tax_bracket_assignment	TTE_tax_bracket