Exemption Certificates CSV Format

Before you import exemption certificates, you must import customers, products, and exempt reasons referenced by the exemption certificate. Customers and Products can be loaded using the Import/Export Wizard or created within ONESOURCE Indirect Tax Determination; Exempt Reasons must be configured within ONESOURCE Indirect Tax Determination.

Once you load certificates, you need to also load (or configure) Exemption Certificate Zone data that indicates in which zones the certificate can apply.

For information about using CSV format files, see Best Practices for Importing Data with CSV Files.

Exemption Certificates File Format

SDITYPE=ExemptionCertificates
Column Name	Format	Req?	Description
CertificateNumber	varchar2(100)	Y	Certificate number.
CustomerName	varchar2(100)	Y	Customer name. Must map to an existing customer.
CustomerNumber	varchar2(100)	Y	Customer number. Must map to an existing customer.
Status	varchar2(100)	Y	Certificate Status. Valid values are: A: Approved. I: In Review. T: Temporary. U: Unapproved.
ExemptReasonCode	varchar2(2)	Y	The exempt reason short code for the certificate. Must map to an existing exempt reason. Always include the ExemptReasonCode header, even if you do not include a value.
ValidFromDate	mm/dd/yyyy	Y	The first date on which the certificate is valid.
ExpiresDate	mm/dd/yyyy	N	An optional expiration date.
Active	varchar2(1)	Y	Indicates whether or not the certificate may be applied to transactions. Valid values are Y or N.
FullyExempt	varchar2(1)	Y	Indicates whether or not the certificate results in full exemption from tax. Valid values are Y or N.
TaxDataType	varchar2(50)	Y	The Tax Data Type. Valid values are: US INTL
CertificateImagePath	varchar2(1000)	N	A fully-qualified filename for an image of the physical certificate.
ExemptAmount	NUMBER	N	The amount to exempt for a transaction to which this certificate applies. If you specify an amount, the currency must match the currency used on a document.
CurrencyCode	varchar2(50)	Y *	A valid Currency Code. See ONESOURCE Indirect Tax Determination's Currencies page for a list of valid codes. (*) Required if ExemptAmount is included.
BasisPercent	NUMBER	N	A Basis Percentage to apply to the transaction. A real number between 0 and 1. For example, .5 indicates a 50% basis percentage.
CustomerTaxId	varchar2(100)	N	Optional information about the customer, not used in calculations.
CustomerLicenseNumber	varchar2(50)	N
CustomerBusinessType	varchar2(100)	N
CustomerComments	varchar2(2000)	N
SellerName	varchar2(100)	N	Optional information about the seller, not used in calculation.
SellerNumber	varchar2(100)	N
SellerDbaName	varchar2(100)	N
SellerTaxId	varchar2(100)	N
SellerLicenseNumber	varchar2(100)	N
ProductCode	varchar2(100)	N	A string (full, or partial with wildcards) that matches the Input XML element <INVOICE>.<LINE>.<PRODUCT_CODE> passed in with a transaction. If a Product Code is specified, only transactions containing that product can potentially have the certificate applied.

Exemption Certificate File Example

This example specifies two certificates. Each is associated with a different customer.

In this case, there is no ExemptReasonCode, so you include the header but leave the value blank. Note that the second line wraps so that all of the text is visible. In your file, all text for each row should be on one line.

1. sdiType=ExemptionCertificates

2. CertificateNumber,CustomerName,CustomerNumber,Status,ExemptReason
   Code,TaxDataType,ValidFromDate,Active,FullyExempt

3. 199A,Aurora Forest Products,589247,A,,US,03/05/2007,Y,Y

4. 28945,Sequoia Office Services,649823,I,,US,03/04/2007,Y,Y

For more information about formatting a CSV file, see Creating CSV Import Files.

Updating an Existing Exemption Certificate

You can update an exemption certificate record by including data in the CertificateNumber, CustomerName, CustomerNumber, ExemptReasonCode, and ValidFromDate columns to match a certificate and then modifying additional columns as needed.

Exemption Certificate File Import Prerequisites

Before you import exemption certificates using the CSV import file, you must define the following:

• Target company
• Customer
• Customer group
• Zones
• Authority type
• Authority

Exemption Certificate File Import Options

All certificates are imported as blanket use. If you want to create a limited or single-use certificate, import the certificate, then use ONESOURCE Indirect Tax Determination to add limited use criteria. For more information, see Exemption Certificates.