Import Invoices from CSV File

Accounts Receivable – NEW FEATURE

•A new option has been added to the AR Miscellaneous Invoice Screen for importing invoices from a CSV (Comma Separated Values) file using a pre-defined structure for the Invoice Master Record and supporting line item records.

When you select this option from the menu, you will simply browse to the folder where the CSV File is located and select the file.  The data in the file will then be checked for any errors and the invoices in the file will be added to the current AR Invoice Batch.  The following is a list of the import fields that are required in the import CSV file:

Master Record: The Master record represents the invoice header record and contains summary information about the invoice including taxable amounts and sales tax amounts when applicable.

Detail Record: The Detail record(s) represent the invoice line item(s) and contains individual line item fields and amounts.

Business Rules for Import Process – During the import of AR Invoices from a CSV file, the following processes will be carried out to update the records for default fields and test the records for any possible business rules violations. These processes are carried out in SQL Script from the class 1125 Script which was designed to work in conjunction with PC Scale, Inc. software.

1.Update the State Code and District Code for each invoice using the Customer’s settings in the customer record (Clients Table).

2.Scan the import table for any taxable sales, If found, then a TAX record (type 1) will be created with the tax amount pulled from the Master Record.  The sales code for the tax record is set to “TAX” per AccuBuild Business rules and the description is set to “SALES TAX”.
 
NOTE: If the imported invoice is edited in the AR Invoice Screen, then the sales tax record will be recomputed automatically which could change the tax amount along with the total amount of the invoice.  When this occurs, the sales tax record description will be changed from “SALES TAX” to “Sales Tax @ x.xx %” which will be the indicator that an invoice was edited after the import.
 

3.The Master Record for each invoice (type 0) will be updated for the Accounts Receivable Account and the Income Account using the Global Property settings from the A/R Properties – The following fields in the master record of each invoice will be updated:
       

4.A Tax calculation check will be performed to test the Sales Tax calculations from the import file and any differences will be recorded in the ARCSVErrorTable in the user folder and will be displayed on screen to the operator.
 
IMPORTANT NOTE Regarding Sales Tax: When records are imported, the sales tax amounts are included with the invoice information and the tax will be labeled as “SALES TAX” on the invoice in all uppercase lettering.  However, if you edit the invoice after importing, the AccuBuild Program will re-compute the sale tax amount based on the customer’s sales tax district.  Whenever the sales tax has been re-computed by AccuBuild, the Sales Tax Label will be changed to proper casing and the tax rate will be included in the label.  For example: “Sales Tax @ 8.25 %” would indicate that the tax was computed by AccuBuild.
 

5.Finally, a series of tests will be run on the imported invoice file to determine if any critical errors exist, and if they do, the user will be notified and the import process will be cancelled.  The following is a list of error codes that can be encountered during the import process (error codes >= 100 are considered critical errors):

Error 1 - Tax Calculation Difference – The tax calculated based on the customer’s sales tax district does not match the amount of sales tax on the invoice. This is a warning message only and most likely will be cause by rounding differences due to the fact that sales tax is computed individually for each imported sales ticket, but computed by AccuBuild based on the invoice total of all tickets for the customer invoice.  Make sure the customer’s sale tax district is setup in AccuBuild and that the sale tax rates are the same.

Error 100 – Invalid G/L Account for Sales Tax Item – The G/L account for the Sales Tax record cannot be found for the Tax District code associated with the customer.  Check the Tax District Setting in AccuBuild to make sure the G/L Account for Sales Tax Expense exists on the tax district record.

Error 101 - Sales Tax Rate is ZERO – The tax district assigned to the custom contains a zero tax rate.  The tax rate must be greater than zero when taxable amounts are imported.

Error 102 - Customer Number Not found in Custom List – The customer number imported with the sales records does not exist in the AccuBuild System.  Check the Client List in AccuBuild and if the customer does exist, make sure they are set for “Customer” or “Both”.  If the customer exists on the client list but marked as a “Vendor” can cause this error but most of the time, this will be due to the customer not being set up in AccuBuild.

NOTE: The import file must contain the actual AccuBuild Customer number which must be set up in the 3rd Party Software, usually as a User Defined Field.  Whenever a new customer is set up in the 3rd Party Software, that customer will need to be set up in the AccuBuild System and the customer number assigned by the AccuBuild Program must be entered into the User Defined field of the 3rd Party Software.

Error 103 - Invalid G/L Accts Receivable Account - Item 0 – The Accounts Receivable G/L Account is missing in the Master Record of the customer invoice.  This indicates that the default setting in the Receivable Properties Screen has not been set up.  Make sure this setting is in place before importing customer invoice records.

Error 104 - Invalid G/L Revenue Account - Item 0 – The Miscellaneous Income Account is missing in the Master Record of the customer invoice.  This indicates that the default setting in the Receivable Properties Screen has not been set up.  Make sure this setting is in place before importing customer invoice records.

Error 105 - Invalid G/L Revenue Account - Item No x – The Miscellaneous Income Account is missing in the detail line item number indicated by the error message.  This is usually set automatically based on the Receivable Properties Screen setting for Miscellaneous Income. Make sure this setting is in place before importing customer invoice records.

After the above script checking is completed, the Invoice File will be tested to make sure there is one Master Record and one or more detail record for each invoice.  If any of these record requirements are not met, then all records for the invoice will be removed from the import batch.

Custom Script Option – If you need to make adjustment to the import script for different business rules, the script can be modified on a company basis using script number 1125 via the MAR Script Utility tool.  These modifications must be done by an authorized AccuBuild consultant.

A sample CSV Import file is available in the Samples folder located under the AccuBuild Program Folder.  The name of the CSV file is ARInvoiceCSVImportSample.csv.