NOTE: File path must be UNC path, not mapped drive letter

Import File Layouts

The import file layouts are used to define the required data and data types of the collection software tables. You can utilize these file layouts to pull data from your external ERP or Data warehousing systems that will then be pushed into the collection software via our Sync process.

Special Notes About Layouts

At this time only comma delimited files can be processed and have a file extension of .csv.
Each import file must be named exactly as indicated by the layout file names.

To avoid datatype issues, ALL fields need to be enclosed in quotes (“ “) with the exception of numeric fields and date fields.

Though we can except numeric values in fields that we consider alphanumeric, such as TranNo, the Microsoft ODBC driver will make the assumption of the fields datatype based on the first 20-50 rows it reads. Thus if you send some rows with numeric values with NO QUOTES and then others that are alphanumeric with QUOTES, Microsofts ODBC driver will not be able to convert the alphanumeric values to numeric causing them to be sent in as blanks and SARA will NOT be able to import these records.
Any of these values are acceptable: -25.50, “-25.50”, 1003.52 or “1003.52”.
Numeric fields do not require quotes UNLESS they include a comma for formatting (i.e. 13,303.52).
This value MUST be enclosed in quotes (i.e. “13,303.52”).
All files even if they are EMPTY, MUST contain the header fields names exactly as they appear in the layout definitions.
The order of the fields does not matter, but ALL fields are REQUIRED.
If your ERP system does not provide a value for the field, simply send in an empty field via a “” value.

Import Data Rules

The collection software only imports 6 months (from import date) of “closed” invoice history during the first import of data. Closed invoices are invoices with zero balance amounts. For example, if today is 06/10/2010 then any invoices in the text file for invoices that have an invoice date prior to 01/10/2010 that have a zero balance will NOT be imported. No payment applications associated with those invoices would be imported even if those payments were posted within the 6 month cut-off date.

During all subsequent imports only open invoices will be processed. Any newly closed invoices that have a current status of “-open-” within the collection software will be processed.

There are three (3) invoice TranTypes (IN, CM ,FC) and two (2) payment TranTypes (CR, RV). These TranTypes are defined under the appropriate layout. However, the collection software does recognize custom TranTypes for both of these layouts. Custom TranTypes are not stored within the collection software as a field, but are appended to the transactions DocumentNo for reference purposes. But the TranType field within the software will correspond with the appropriate document type (see conversion rules below for details).

For example, if a payment record is provided with a TranType of “AX” and TranNo of “55533” the DocumentNo will be “55533-AX.
Another example, if an invoice record is provided with a TranType of “CC” and TranNo of “89455” the DocumentNo will be “89455-CC”.

Transactions with custom TranTypes will be converted to the appropriate TranType based on the TranAmt value. TranType conversion rules are as follows.

Invoices:
Negative TranAmts will be converted to a TranType = “CM”
Positive TranAmts will be converted to a TranType =“IN

Payments:
Negative TranAmts will be converted to a TranType =“RV”
Positive TranAmts will be converted to a TranType = “C

File Names – the file names must be exact and must be found in somewhere in the name of the file (case does not matter):

Company – i.e. ABC_Company_03272015.csv, Company.csv, etc.
Customer
CustContact (Customer Contact)
Invoice
InvLine (Invoice Lines) – if this option is included and applicable
Payment
PmtAppl (Payment applications)

Company

A company represents your ERP Company. Many ERP systems allow for multiple companies and have some type of table or field that describes this value.

COMPANY.csv: An asterisk (*) denotes fields that are part of the PRIMARY KEY of the file and MUST be unique for each record.

Company Sample Data

CompanyID,CompanyName,HomeCurrID

"ERP","ERP Company","USD"

Customer

The customer table represents all of your ERP customers for a given company. The collection software runs an aging process to calculate the various aging buckets and balance amounts for each customer.

The customer records are linked to the Company records via the CompanyID field.

CUSTOMER.csv: An asterisk (*) denotes fields that are part of the PRIMARY KEY of the file and MUST be unique for each record.

CUSTOMER.csv (continued)

CompanyID,CustID,CustName,CustStatus,SperID,SperName,CustClassID,CreditHold,CreditLimit,PmtTermsID,DateEstab,AddrLine1,AddrLine2,AddrLine3,AddrLine4,AddrLine5,City,SalesTerritory,State,PostalCode,Country,CurrID,ContactName,Reference,UDF1,UDF2,UDF3,UDF4,UDF5,UDF6,UDF7,UDF8,UDF9,UDF10

"ERP","ABC","ABC Lumber Union","Active","JSMITH","John Smith","MFG","NO",10000,"90 DAYS","1/1/2009","1 Station Square","","","","","Cleveland","","OH","44046","USA","USD","Bob Johnson","My Reference Field","My UDF 1","My UDF 2","","","","","","","",""

"ERP","JOES","Joe's Crab Shack","Active","JDOE","John Doe","FOOD","NO",0,"120 DAYS","1/1/2009","538 4th Street","","","","","Cleveland","","OH","44077","USA","USD","Laura Lee","Any Reference Information","Location Code:3","Location Code 4","","","","","","","",""

"ERP","XYZ","XYZ Company","Inactive","Afenmore","Alan Fenmore","FOOD","NO",5000,"30 DAYS","1/22/2010","182 Fifth Avenue","","","","","Chardon","","OH","44024","USA","USD","Alan Fenmore","","","","","","","","","","",""

"ERP","JASP","Jasper Industries","Deleted","Pjasper","Paul Jasper","MFG","No",30000,"30 DAYS","3/15/2009","805 Victoria Place","","","","","Euclid","","OH","44119","USA","USD","Paul Jasper","Reference This","","","","","","","","","",""

"ERP","AVEDA","Aveda Cosmetics","Other","Mbrown","Mary Brown","Other","Yes",50000,"30 DAYS","7/12/2006","7001 Mentor Avenue","Suite 300","","","","Mentor","","OH","44060","USA","USD","Mary Brown","Something to Reference","","","","","","","","","",""

Customer Contacts

The customer contact table represents various contacts that are assigned to each customer.

Contacts are linked to the Customer file via the CompanyID + CustID field
The ContactName field must be unique for each CompanyID + CustID record.

CUSTCONTACT.csv: An asterisk (*) denotes fields that are part of the PRIMARY KEY of the file and MUST be unique for each record.

Customer Contact Sample Data:

CompanyID,CustID,ContactName,Comment,EmailAddr,Fax,FaxExt,Phone,PhoneExt,PrimaryContactFlag,Title,AddrLine1,AddrLine2,AddrLine3,AddrLine4,AddrLine5,City,State,PostalCode,Country,UDF1,UDF2,UDF3,UDF4,UDF5,UDF6,UDF7,UDF8,UDF9,UDF10

"ERP","ABC","Bill Johnson","","b@stuff.com","","","555-555-5555","","Yes","Account Rep","1 Station Square","","","","","Cleveland","OH","44046","USA","","","","","","","","","",""

"ERP","JOES","Joe Crab","My Commentjcrab@crabshack.com","","","","555-555-5555","","Yes","Owner","538 4th Street","","","","","Cleveland","OH","44077","USA","","","","","","","","","",""

Invoice

The invoice layout represents all invoices from your ERP system. The collection software does not do any transactions or calculations for balances or amount fields for the Invoice table. The balances and amount values in these records represent values provided by your ERP system.

Invoices are linked to customer file via the CompanyID + CustomerID fields.

**TranTypes that are supported for the Invoice file layout within the software are listed below. You may supply these TranTypes or your own internal TranTypes.
In the case that you use our “standard” TranTypes you must set the amounts with the proper sign.

IN TranType MUST BE 0 OR POSITIVE
FC TranType MUST BE 0 OR POSITIVE
CM TranType MUST BE 0 OR NEGATIVE

In the case that your internal TranType are different, the collection software will convert all positive TranAmt records to an “IN” and negative TranAmt record a “CM” TranType.

Your internal TranType will be automatically appended to the DocumentNo field within the collection software (i.e .Invoice:”0105A” and TranType: “SP” would convert to DocumentNo:”0105A-SP”).

This allows the collection software to except duplicate TranNo that actually represent different documents within an ERP system that may overlap. For example, an Invoice and Adjustment may have the same transaction number within the ERP system because they are coming from two different tables, however, since we treat both of these documents as a positive increase on your accounts receivable, we store both of them in the invoice transaction table, separating them based on TranType.

The Balance/BalanceHC field value will affect the customer and total aging.

TIP: If your ERP system converts unapplied cash receipts or “on account” amounts for a customer to a credit memo automatically, then make sure when you send the customer payment information that the UnappliedAmt/UnappliedAmtHC is set to zero. We will pick up this payment via the invoice table as a credit memo. Which will reduce the customers and the total aging.

Invoice TranTypes: See Import Data Rule #4 for important information on Invoice TranTypes.

IN – Invoice
CM – Credit Memo
FC – Finance Charge

INVOICE.csv: An asterisk (*) denotes fields that are part of the PRIMARY KEY of the file and MUST be unique for each record.

Invoice Sample Data:

CompanyID,CustID,TranNo,TranType,InvoiceCmnt,TranDate,PostDate,DueDate,DiscDate,ClosingTranDate,CustPONo,TranAmt,TranAmtHC,DiscAmt,DiscAmtHC,Balance,BalanceHC,PmtTermsID,CurrID,HomeCurrID,CurrExchRate,Status,CreateDate,PrimarySperName,StaxAmt,StaxAmtHC,UDF1,UDF2,UDF3,UDF4,UDF5,UDF6,UDF7,UDF8,UDF9,UDF10

"ERP","ABC","105","IN","","3/1/2010","3/1/2010","4/1/2010","","","12345",500.75,500.75,0,0,250.7,250.75,"90 DAYS","USD","USD",1,"",”3/1/2010”,”John Hart”,8.71,8.72,

"ERP","ABC","8090","IN","","3/2/2010","3/2/2010","4/2/2010","","","12346",1000.75,1000.75,0,0,500,500,"90 DAYS","USD","USD",1,"", ,"",”3/2/2010”,”Mary Mallory”,9.00, 9.00,"UDF1","UDF2","UDF3","UDF4","UDF5","UDF6","UDF7","UDF8","UDF9","UDF10"

"ERP","JOES","8192","CM","Invoice Comment","4/15/2010","4/16/2010","5/15/2010","5/1/2010","","89992",-25.25,-25.25,10,10,-25.25,-25.25,"30 DAYS","USD","USD",1,"", ,"",”4/16/2010”,””,0,0,"","","","","","","","","",""

"ERP","AVEDA","66616","IN","","6/10/2010","6/15/2010","7/15/2010","","","AB00718",4000,4000,0,0,2000,2000,"30 DAYS","USD","USD",0,"06/10/2010","Darren Hall","3.00","3.00","","","","","","","","","","",""

"ERP","AVEDA","66617","IN","","6/15/2010","6/16/2010","7/16/2010","","","AB00777",2300,2300,0,0,1300,1300,"30 DAYS","USD","USD",0,"06/15/2010","","","","","","","","","","","","","",""

"ERP","AVEDA","66686","TA","","6/10/2010","6/15/2010","7/15/2010","","","ABTA564",6000,6000,0,0,6000,6000,"30 DAYS","USD","USD",0,"06/10/2010","Barry White","","","","","","","","","","","","",""

Invoice Lines

The invoice lines layout represents the individual lines that comprise an invoice

The Invoice Lines are linked to the invoice record via the CompanyID + TranNo + TranType fields.

Invoice TranTypes: See Import Data Rule #4 for important information on Invoice TranTypes.

IN – Invoice
CM – Credit Memo
FC – Finance Charge

INVLINE.csv: An asterisk (*) denotes fields that are part of the PRIMARY KEY of the file and MUST be unique for each record.

Invoice Line Sample Data:

CompanyID,TranNo,TranType,ItemID,Description,QtyShipped,UnitMeasID,UnitPrice,ExtAmt,InvoiceLineKey,UDF1,UDF2,UDF3,UDF4,UDF5,UDF6,UDF7,UDF8,UDF9,UDF10

"FKDF",18240,"IN","Pillows","Fluffy Pillow",6,"EACH",12.95,77.7,555,1,11,,,,21,,,,31

"FKDF",18240,"IN","Sheets","Nice soft sheets",12,"EACH",54.95,659.4,556,2,12,,,,22,,,,32

"FKDF",18240,"IN","Blanket","Grandmas Quilt",5,"EACH",75.32,376.6,557,3,13,,,,23,,,,33

"FKDF",18241,"IN","Pillows","Fluffy Pillow",22,"EACH",12.95,284.9,558,4,14,,,,24,,,,34

"FKDF",18243,"IN","Sheets","Nice soft sheets",10,"EACH",54.95,549.5,559,5,15,,,,25,,,,35

"FKDF",18243,"IN","Blanket","Grandmas Quilt",9,"EACH",75.32,677.88,560,6,16,,,,26,,,,36

Payments

The payment layout represents actual payments you have received from your customers. These payments can be made in any form; check, credit card, cash, money order, wire, etc...

Payments are linked to the customer record via the CompanyID + CustID fields.

Even though payments decrease your accounts receivable we import and display and store them in positive amounts.

The value of the UnappliedAmt/UnappliedAmtHC field affects your accounts receivable aging.

TIP: If your ERP system converts unapplied cash receipts or “on account” amounts for a customer to a credit memo automatically, then make sure when you send the customer payment information that the UnappliedAmt/UnappliedAmtHC is set to zero. We will pick up this payment via the invoice table as a credit memo. Which will reduce the customers and the total aging.

**TranTypes that are supported for the payment file layout within the software are listed below. You may supply these TranTypes or your own internal TranTypes.

In the case that your internal TranType are different, the collection software will convert all positive TranAmt records to a “CR” and negative TranAmt record a “RV” TranType.

Payment TranTypes: See Import Data Rule #4 for important information on Payment TranTypes.

CR – Cash Receipt
RV – Cash Receipt Reversal

PAYMENT.csv: An asterisk (*) denotes fields that are part of the PRIMARY KEY of the file and MUST be unique for each record.

Payment Sample Data:

CompanyID,CustID,TranNo,TranType,TranDate,PostDate,TranCmnt,TranAmt,TranAmtHC,UnappliedAmt,UnappliedAmtHC,TenderTypeID,PmtRef,RevrsTranNo,RevrsTranType,CurrID,UDF1,UDF2,UDF3,UDF4,UDF5,UDF6,UDF7,UDF8,UDF9,UDF10

"ERP","ABC","2080","CR","3/1/2010","3/15/2010","Pmt To: 105",50,50,0,0,"Cash","12345","","","USD","UDF1","UDF2","UDF3","UDF4","UDF5","UDF6","UDF7","UDF8","UDF9","UDF10"

"ERP","ABC","2099","CR","3/1/2010","3/15/2010","Pmt To:8090",500.75,500.75,0,0,"Cash","12346","","","USD","UDF1","UDF2","UDF3","UDF4","UDF5","UDF6","UDF7","UDF8","UDF9","UDF10"

"ERP","JOES","3000","CR","4/1/2010","4/20/2010","Pmt To:544 and 545",200,200,0,0,"Check","88676","","","USD","UDF1","UDF2","UDF3","UDF4","UDF5","UDF6","UDF7","UDF8","UDF9","UDF10"

"ERP","ABC","3001","CR","4/1/2010","4/21/2010","Pmt To 8090",250,250,0,0,"Check","12344","","","USD","UDF1","UDF2","UDF3","UDF4","UDF5","UDF6","UDF7","UDF8","UDF9","UDF10"

"ERP","XYZ","4015","CR","4/10/2010","4/10/2010","This is an Unapplied Payment",800,800,800,800,"Check","191993","","","","","","","","","","","","",""

"ERP","AVEDA","4040","CR","5/10/2010","5/11/2010","Partial Pmt to 66616 and all of 66617",3000,3000,0,0,"Check","80124","","","USD","","","","","","","","","",""

Special Note About Reversals of Payments

The purpose of a reversal transaction is to have a transaction trail on the original transaction. You could essentially reverse a cash receipt transaction by sending in the original transaction and simply update the UnAppliedAmt and UnAppliedAmtHC = 0 as noted in the first bullet point below. However, you would not have a record that is now closed because of a reversal. It would simply be a closed cash receipt with no applications. The reversal transaction allows you close the original transaction with a record of why that transaction is now closed. In this case not because the cash receipt was actually applied to any invoices, but because it was entered in error and was reversed out.

To reverse out a cash receipt you will need to submit two transactions to the collection software.

The first transaction is the original cash receipt record with the UnAppliedAmt and UnappliedAmtHC set = 0. This will close the original transaction.
The second transaction is the reversal transaction again setting the UnAppliedAmt and UnappliedAmtHC set = 0 in order for this transaction also to be closed.
This is necessary since reversals by nature are cancelling out another transaction they themselves are closed transactions.

Original Cash Receipt Transaction UnAppliedAmt and UnappliedAmtHC set to = 0

"ERP","XYZ","4015","CR","4/10/2010","4/10/2010","Closing this transaction due to the fact it was entered in error" ,800,800,0,0,"Check","191993","","","","","","","","","","","","",""

Reversal Transaction with UnAppliedAmt and UnappliedAmtHC set to = 0

"ERP","XYZ","4015-A","RV","4/15/2010","4/15/2010","Reversal of incorrect cash entry",-800,-800,0,0,"Check","191993","4015","CR","","","","","","","","","","",""

Payment Applications

The payment application layout represents how the actual payment was applied to transactions within your ERP system. Many ERP systems allow for one payment to be applied over multiple invoices or transactions.

Payments applications are linked to the payment record via the CompanyID + TranNo + TranType fields.

The PMNTAPPL.csv table is “optional”. However, if you do not provide the application information the collection software will not be able to link it to a Payment transaction. Thus you will see the payment record in the Customer Payment View, but you will not be able to see which invoice or invoices the payment was applied to.
The EntryNo field is used to provide uniqueness to the payment application. It is simply a sequential number that is incremented for each CompanyID + TranNo + TranType entry.

PMNTAPPL.csv: An asterisk (*) denotes fields that are part of the PRIMARY KEY of the file and MUST be unique for each record.

Payment Application Sample Data:

CompanyID,TranNo,TranType,EntryNo,ApplyToTranDate,ApplyToTranNo,ApplyToTranType,PmtAmt,PmtAmtHC,UDF1,UDF2,UDF3,UDF4,UDF5,UDF6,UDF7,UDF8,UDF9,UDF10

Sample of single applications

"ERP","2080","CR","1","3/9/2010","105","IN",250,250,"","","","","","","","","",""

"ERP","2099","CR","1","3/10/2010","8090","IN",250.75,250.75,"","","","","","","","","",""

"ERP","3001","CR","1","4/1/2010","8090","IN",250,250,"","","","","","","","","",""

Sample of multiple applications

"ERP","3000","CR","1","4/1/2010","544","IN",150,150,"","","","","","","","","",""

"ERP","3000","CR","2","4/1/2010","545","IN",50,50,"","","","","","","","","",""

"ERP","4040","CR","1","8/1/2010","66616","IN",2000,2000,"","","","","","","","","",""

"ERP","4040","CR","2","8/1/2010","66617","IN",1000,1000,"","","","","","","","","",""