NOTE: All field names are required in each view below but data is only required where noted in data required column
NOTE: use the below SQL script to verify aging totals in your views compared to ERP aging total:
SELECT SUM(BalanceHC) FROM vatcImportERPInvoice
SELECT SUM(UnAppliedAmtHC) FROM vatcImportERPCustPmt
To verify if any or find Duplicate records:
SELECT CompanyID, COUNT(*) AS 'cnt' FROM vatcImportCompany GROUP BY CompanyID HAVING COUNT(*) 1
SELECT CompanyID, CustID, COUNT(*) AS 'cnt' FROM vatcImportCustomer GROUP BY CompanyID, CustID HAVING COUNT(*) 1
SELECT CompanyID, CustID, ContactName, COUNT(*) AS 'cnt' FROM vatcImportCustContact GROUP BY CompanyID, CustID, ContactName HAVING COUNT(*) 1
SELECT CompanyID, TranNo, TranType, COUNT(*) AS 'cnt' FROM vatcImportInvoice GROUP BY CompanyID, TranNo, TranType HAVING COUNT(*) 1
SELECT CompanyID, TranNo, TranType, COUNT(*) AS 'cnt' FROM vatcImportPayment GROUP BY CompanyID, TranNo, TranType HAVING COUNT(*) 1
SELECT CompanyID, TranNo, TranType, EntryNo, ApplyToTranNo, ApplyToTranType, COUNT(*) AS 'cnt' FROM vatcImportPaymentAppl GROUP BY CompanyID, TranNo, TranType, EntryNo, ApplyToTranNo, ApplyToTranType HAVING COUNT(*) 1
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.
vatcImportCompany: An asterisk (*) denotes fields that are part of the PRIMARY KEY of the table/view and MUST be unique for each record.
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.
vatcImportCustomer: An asterisk (*) denotes fields that are part of the PRIMARY KEY of the table/view and MUST be unique for each record.
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.
vatcImportCUSTCONTACT: An asterisk (*) denotes fields that are part of the PRIMARY KEY of the table/view and MUST be unique for each record.
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.
vatcImportINVOICE: An asterisk (*) denotes fields that are part of the PRIMARY KEY of the table/view and MUST be unique for each record.
SARA will only look at Invoices with a Balance and the last 6 months of Invoices with a Zero balance. You will want to restrict your Views as such to prevent to many records from being selected during the sync as it can cause Performance problems.
For Example:
WHERE (Balance <> 0) OR (Balance = 0 AND (TranDate = DATEADD(m, -6, GETDATE()) OR UpdateDate = DATEADD(m, -6, GETDATE())))<></></>
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...
vatcImportPayment: An asterisk (*) denotes fields that are part of the PRIMARY KEY of the table/view and MUST be unique for each record.
SARA will only look at Invoices with a Balance and the last 6 months of Invoices with a Zero balance. You will want to restrict your Views as such to prevent to many records from being selected during the sync as it can cause Performance problems.
For Example:
WHERE (UnAppliedAmt <> 0) OR (UnAppliedAmt = 0 AND (TranDate = DATEADD(m, -6, GETDATE()) OR UpdateDate = DATEADD(m, -6, GETDATE())))<></></>
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.
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 payment application 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 when multiple applications were made (i.e. A $100 payment paid 2 $50 Invoices).
vatcImportPaymentAppl: An asterisk (*) denotes fields that are part of the PRIMARY KEY of the table/view and MUST be unique for each record.
Invoice Lines
The invoice can be imported in order to re-create the invoice form in order to provide invoice document support within the application. 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.