Customer Invoice Import Specs - With Columns Noted and Example Sheet

COLUMN             Header Fields                                                             

A                *        Type                                C    1        0               Always H

B                *        Invoice Date                    D    10      0               DD/MM/YY or DD/MM/YYYY

C                *        Invoice Number                C    10                         Blank If system to assign - does not check for duplicates

D                *        Invoice Total (Incl GST)    N    12      2               Can be left as zero if Calculate Total Flag is T

E                *        GST Amount                    N    12      2               Can be left as zero if Calculate Total Flag is T

F                *        Calculate Total Flag         C    1                           Set as true if invoice totals not supplied (values are T or F) blank = F

G                **       Order Number                  C    15                        

H                **       Reference                        C    15                        

I                  **       Sales Rep                        C    10                         Blank uses Customers Default

J                 *        Location                          N    2                           0 to use customers default location

K                *        Customer Number            N    10                         1.       If Customer Number is 0, it will create a new customer based on the rest of the details in the header; Is a number in it that it cannot find, it will create it; Is a number it recognises then it uses it.

L                 ***      Customer Name               C    40                        

M                ***      Customer Add 1               C    40                        

N                ***      Customer Add 2               C    40                        

O                ***      Customer Add 3               C    40                        

P                ***      Customer Add 4               C    40                        

Q                ***      Customer Postcode         C    10                        

R                ***      Customer Delivery 1         C    40                        

S                ***      Customer Delivery 2         C    40                        

T                 ***      Customer Delivery 3         C    40                        

U                ***      Customer Delivery 4         C    40                    

V                ***      Customer Del Postcode   C    10                        

W                        Invoice Title                     C    60                        

X                         Invoice Notes                   C    250                      

Y                         User Field 1                     C    20                        

Z                         User Field 2                     C    20                        

AA                       User Field 3                     C    20                        

AB                       User Field 4                     C    20                        

AC                       User Field 5                     C    20                        

AD                       User Field 6                     C    20                        

Detail Fields                                                                                                                                

A                *        Type                              C    1               Always D

B                *        Code                             C    20             Must exist in Products File

C                ****    Description                  C    40                        

D                         Qty                               N    14      4              

E                         Rate                              N    14      4              

F                         Discount                       N    6        2              

G                         Extended                     N    14      2              

H                         Calculate Rate Flag      C    1              System will calculate rate based on customer settings if set to true. (Values are T or F) Blank = F

I                           Back Order                  N    14      4              

J                          Weight                         N    10      4              

K                         Cubic                            N    10      4              

L                          User 1                          C    20                        

M                         User 2                          C    20                        

N                         User 3                           D    10            Must be a date

O                         User 4                           L    1               Must be True or False

P                         Serial Number               C    20            This will only import if the product is setup for serial tracking.

* These fields are required fields.  See line notes for additional info

** In the Header record these fields can be blank but they must be included in the file so later fields are in the correct sequence.

*** Customer name and address details are not pulled from the master file.  They must be supplied in the import file.  Fill in as many fields as required by the address.

**** The description is required.  It is not pulled from the Product file.

If calc rate flag in the detail line is T, calc total in the header must be T

Notes:                                                                             

1. If the Calculate Rate flag option is used for the line items the Calculate Total flag on the Header line needs to be set to True
2. If line item totals are supplied no Header values are required
3. If the Header Totals are supplied and no re-totaling is being done then the line totals must add to the Invoice Header Total