AR: Importing Invoices

File must be saved as a CSV file, or your data will be considered invalid. File type must be saved as [file name].csv.

Columns of information must be in this order, and column headers must be included even if there is no data in the column. The only exception to this is for the BillingPeriodFrom and BillingPeriodTo columns, which are not required.

InvoiceNumber - The Invoice Number is not required. If you do not provide invoice numbers, the system will assign the Invoice Numbers.

CustomerNumber - This is a required entry. The Customer Number must be valid in WinTeam.

ServiceLocationJobNumber - This is a required entry. The Service Location Job Number must be valid in WinTeam.

InvoiceDate - This is a required entry.

PostingDate - The date the invoice was recorded.

Notes - Enter in any notes you want to be imported.

PONumber - If the PO Number is unique and on a separate row in the import file you may import different invoices for the same Customer/Job grouping.

ItemNumber - The Item Number must be valid in WinTeam. If an Item Number is valid, but not Active, it will import without receiving a warning message. The Item Number cannot be blank when an Extension is present. An error message displays if the Extension field is included in the file but the Item Number is blank.

Description - The description of the invoice.

Taxable - If the item is taxable, enter a value of "Y". If the item is non-taxable, enter a value of "N". If the line item is left blank, it is considered a non-taxable line item.

Quantity - If you are using the Quantity and Price columns on the spreadsheet, you must enter your own extension. Whatever the values are on the spreadsheet are imported.

Price - If you are using the Quantity and Price columns on the spreadsheet, you must enter your own extension. Whatever the values are on the spreadsheet are imported.

UOM - Unit of Measure

Extension - If you are using the Quantity and Price columns on the spreadsheet, you must enter your own extension. Whatever the values are on the spreadsheet are imported.

There are 5 sets of columns used for the GL Distribution, columns 14 - 28. These column names MUST not change. You cannot delete these columns even if they are not being used. The column names are:

Column #
15	DistrGLNumber1
16	DistrJobNumber1
17	DistrAmount1
18	DistrGLNumber2
19	DistrJobNumber2
20	DistrAmount2
21	DistrGLNumber3
22	DistrJobNumber3
23	DistrAmount3
24	DistrGLNumber4
25	DistrJobNumber4
25	DistrAmount4
26	DistrGLNumber5
27	DistrJobNumber5
28	DistrAmount5

• Distribution GL Number - The Distribution GL Number must be valid in WinTeam.
• Distribution Job Number - The Distribution Job Number must be valid in WinTeam.
• Distribution Amount - An amount must be entered if using the Distribution GL Number and Distribution Job Number.
• The Distribution GL Number, Distribution Job Number, and Distribution Amount must all be filled before the system considers importing this information into AR: Invoices
• JobNumber - The column header is required, but entries are not required.
  If the cell is left blank, the detail line Job Number will default to the Service Location Job Number.
  If the cell contains a Detail Job Number, the detail line Job Number populates with it.
• BillingPeriodFrom - The column header is not required, nor are entries. Used primarily when using the Framed by Job Invoice Form Type since this information displays in each Job record.
  Used to indicate the Billing Period From Date to be used on the invoices.
  If you do not use the Billing Period From and To columns for an invoice, the system will continue to use the Fiscal Period From and To Dates that the Posting Date falls in. See AR: Invoices for more information on Billing Periods.
• BillingPeriodTo - The column header is not required, nor are entries. Used primarily when using the Framed by Job Invoice Form Type.
  Used to indicate the Billing Period To Date to be used on the invoices.
  If you do not use the Billing Period From and To columns for an invoice, the system will continue to use the Fiscal Period From and To Dates that the Posting Date falls in.

Note:  There must be a column (in the listed order) for each data item. If a column of information is missing, the data will 'shift" and the import will fail due to the information being interpreted incorrectly.

The CSV file should look similar to this:

• The CSV file can contain multiple lines for each invoice (due to differing item numbers), so the system will assume that a new invoice needs generated when the Customer Number, Job Number, Invoice Date, or Invoice Number (if a different invoice # for a row (if not blank) changes.
• If consecutive lines share the same values (Customer Number, Job Number, Invoice Date, or Invoice Number (if not blank), but the Posting Date is different, the first line item's Posting Date will "rule" for the invoice.
• Line items in the CSV file that share the same values (Customer Number, Job Number, Invoice Date, or Invoice Number) will import into the same Invoice, in the exact order of the spreadsheet.
• If a line item in the spreadsheet does not have a Customer Number, Job Number, or Invoice Date, it is not considered for the import. This prevents blank lines from showing up on the error report.
• Any valid distribution will be imported to the invoice and the invoice will be marked as "Distribution Edited".

Additional Columns

The Standard AR Invoice Import can also obtain either Customer Address information, or Job Address information from the CSV import file, if present.

These additional columns can be added.

Note:  You can use the attached template for importing your AR Invoices with the Customer or Job Address information. Right click and choose "Save As..." (or "Save Target as...") to download this file, sample file.

If Address 1 is in the import file, it will be used on the Invoices.

If there are no values for an Address 1 in the CSV file, then the database information (Customer Master File and/or Job Master File record) is copied from the Master record.

If Address 2 has null values (are blank) in the CSV file, they will be left blank upon import whether the database record has an Address 2 or not.

If City information differs than that of the record, it will override the database information.

1. Make sure the Import feature is enabled (see Overview above).
2. Make sure the CSV file is in the correct format and that all line items in the file contain valid information.
3. From the AR Invoice Entry screen, make sure you are on a new record.
4. Click Import under the Actions tab.
5. Select the CSV file you want to import, and then click Open.
6. If there are records in the CSV file that fail to import, a message displays stating the number of records that could not be imported. Once you click OK, the Import Errors grid displays. The Import Errors grid displays the records that are not valid for importing. The records may have invalid Customer Numbers, invalid Job Numbers, the Invoice # may already exist (if you are including Invoice #'s on the spreadsheet), invalid Invoice Dates (dates must be valid dates for data entry), invalid Item Numbers, invalid GL Distribution Numbers, or invalid GL Distribution Job Numbers. Standard grid functionality applies to the Import Errors grid. For information on grid functionality see Understanding Grids.
   
7. The system displays a message when all records in the CSV are valid for importing. Click OK.
8. The screen will display a record set of all records processed. Notice the record selector in the lower, left-hand corner.

IMPORTANT NOTES

• If the Customer Master File has Print Invoices not checked, the invoices that are imported for that Customer will marked as "Don't Print".
• If your CSV file had records that could not be imported, and you have corrected those line items, DO NOT reimport the same CSV file. Doing so will create invoices that you have already created.