Skip to main content

Text Sync as Service - Schema Layout

Updated

IMPORT FILE LAYOUTS

Introduction

The purpose of this document is to illustrate the collection software schema and provide guidance for supplying data for import methods.

  • The collection software does NOT update your ERP system. All data is PULLED from your ERP system and nothing is ever updated back.

  • The collection software works with several ERP systems. Many of the values that can be pulled into collection software are for referential purposes only and may or may not exist in your ERP system.

  • The collection software only performs calculations to determine Aging bucket balances. There are no transactional calculations to update invoice balances based on other transactions. These values must be provided by your ERP or Data warehousing system in the layouts defined.

  • The collection software uses the information in the Invoice and Payment tables for aging purposes. Read these sections carefully to fully understand how the values in these tables will be displayed within the collection software.

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

Import Data Rules

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.

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.

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.

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,"","","","","","","",