This feature allows for the importing of Vendor Invoice (VI) information into the Unanet system.
While this import is primarily used to insert new entries, it can also be used for limited updating as well. Existing entries for updating will be identified by a value in the Document Number field, whereas new entries will have no value in that field. This import cannot be used to delete an existing Vendor Invoice document. However, using the available purge option, you can instruct the import to first remove any existing Detail records associated with a particular Summary level entry (based on Document Number). When inserting new entries, you can use the Group Id field to identify multiple input records as belonging to the same Vendor Invoice document.
While the Vendor Invoices are comprised of multiple record types (i.e., Summary and Detail records), importing these records is accomplished via a single record layout. As such, the fields related to the Summary information are ignored on all but the first record (for each distinct Group Id encountered).
See Processing Rules and field descriptions below for additional information about insert and update restrictions.
This screen is available to users having any of the following roles: Administrator.
This feature is available with any Unanet edition.
Topics covered on this help page include:
Import File Record Layout (field by field descriptions)
Import File Format (note about csv layout and option to import a subset of columns using header values)
Excel Template (spreadsheet template containing column headings)
Import Screen (invoking the import via the user interface)
Processing Rules (limitations and rules followed)
IMPORTANT: Note that there are multiple places within this documentation that say you MUST use double quotes for the strings in your import. If you are working in Excel, Excel will provide the double quotes and thus you do not need to add them in manually. Manually adding double quotes within Excel would result in multiple sets of double quotes in the resulting .csv file and thus cause an error (should you attempt to import that resulting file). Please see Excel Tips for more information.
|
|
Field Header Name |
Required/Comments |
||||||||||||||||
1 |
A |
Legal_Entity_Org_Code |
REQUIRED ON ADD. This code uniquely identifies the Legal Entity Organization to which the Vendor Invoice belongs. This value must match an existing active Legal Entity Organization Code value defined in your system. If the value you are trying to import does not already exist in the database, the entire record will be rejected. This value cannot be changed on an existing Vendor Invoice. |
||||||||||||||||
2 |
B |
Document_Number |
REQUIRED ON UPDATE. When attempting to update an existing entry, this field is required and must match an existing Vendor Invoice document number (and that document must be in either the INUSE or SUBMITTED status). This is the field that is used to identify the collection of records in the import file that are to be associated with a particular Vendor Invoice document (during an update process). | ||||||||||||||||
3 |
C |
Group_Id |
The value supplied in the field is never saved to the database and is only used for the purposes of grouping records into specific Vendor Invoice documents.
|
||||||||||||||||
4 |
D |
Document_Date |
The Document Date is the date to be associated with the Financial Document. If not provided, it will default to the current system date (when creating a new entry).
Recommended date format: yyyy-MM-dd See Allowable Date Formats for more options. |
||||||||||||||||
5 |
E |
Post_Date |
REQUIRED ON ADD. The Post Date is the date to which you desire to have a financial document posted. This date reflects the date to which various accounts are updated and for which the business transaction will appear in various financial reports.
The Post Date supplied must fall within a Fiscal Period that is open for transaction entry (unless the "Allow changes to closed fiscal periods" option has been selected).
The Post Date must fall within the allowed Begin and End Dates for the supplied AP_Account and AP_Org.
Recommended date format: yyyy-MM-dd See Allowable Date Formats for more options. |
||||||||||||||||
6 |
F |
Vendor_Org_Code |
REQUIRED ON ADD. This field is used to supply the Vendor Organization to which the Vendor Invoice transaction is to be posted. This value must match an existing organization defined in your system. If the value you are trying to import does not already exist in the database, the entire record will be rejected. The organization supplied must have the following characteristics:
then this record will be rejected, unless you selected the "first purge details" option on the import screen. |
||||||||||||||||
7 |
G |
Invoice_Amount |
REQUIRED ON ADD. This field is used to supply the total amount of the invoice. The number supplied can be zero, positive or negative. Prior to submitting a Vendor Invoice, the sum of the amounts applied in the Details associated with this Vendor Invoice must equal this value. This field accepts a numeric value up to 16 positions to the left of the decimal and 2 positions to the right. |
||||||||||||||||
8 |
H |
Invoice_Date |
This is an optional date to be associated with the Financial Document. While you can supply any date you desire, a typical usage may be to record an identifying date from the Vendor Invoice. You can use the special tag !BLANK! to remove an existing entry in this field.Recommended date format: yyyy-MM-dd See Allowable Date Formats for more options. |
||||||||||||||||
9 |
I |
Payment_Term |
This field is used to identify the Payment Terms to be associated with a particular Vendor Invoice. This value must match an existing Payment Term value defined in your system. If the value you are trying to import does not already exist in the database, the entire record will be rejected. The master list of available options is defined by the Administrator on the Admin >> Setup >> Payment Terms screen. The default value comes from the PO if the VI is associated with a PO and the PO has an active payment term, otherwise, the default comes from the vendor. If there is no payment term from either of those and it is not specified on the import, the entire record will be rejected. |
||||||||||||||||
10 |
J |
Due_Date |
This field contains a date value identifying when payment for a particular vendor invoice is due. If no value is provided, a date will be defaulted as:
Recommended date format: yyyy-MM-dd See Allowable Date Formats for more options. |
||||||||||||||||
11 |
K |
Discount_Date |
This field is intended to supply a date value identifying they date by which a discount will be offered should payment for an item be received by that date. If no value is supplied, a date will be defaulted as:
You can use the special tag !BLANK! to remove an existing entry in this field. Recommended date format: yyyy-MM-dd See Allowable Date Formats for more options. |
||||||||||||||||
12 |
L |
Discount_Amount |
This field is intended to supply a value that represents the amount of a discount should payment for an item be received by that date. The number supplied can be zero, positive or negative. If no value is supplied, a value will be defaulted as:
You can use the special tag !BLANK! to remove an existing entry in this field. This field accepts a numeric value up to 16 positions to the left of the decimal and 2 positions to the right. |
||||||||||||||||
13 |
M |
Require_Separate_Payment |
Valid values include Y or N. This field indicates whether the Vendor Invoice should be paid with a separate check regardless of whether there are other Vendor Invoices for the same Vendor available to pay. Whether this option defaults to Y during the add of a Vendor Invoice is determined by the value of the "Check Printing >> Require a separate payment for each invoice" checkbox on the Vendor Profile record associated with this Legal Entity.Further, regardless of the value of this setting on the Vendor Invoice, the user can still choose to include multiple vendor invoices on a single Vendor Payment (when manually creating Vendor Payments). During the Bulk Vendor Payment Creation process, however, this Vendor Invoice level value will be honored and individual Vendor Payments will be generated for those Vendor Invoices configured to require a separate payment. |
||||||||||||||||
14 |
N |
AP_Account |
REQUIRED ON ADD. This field is required and must be an active account having an Account Type of Liability (as defined on the Admin >> Setup >> Accounts screen), allowing entry for the supplied Post_Date. If the value you are trying to import does not already exist in the database, the entire record will be rejected. |
||||||||||||||||
15 |
O |
AP_Org |
REQUIRED ON ADD. This field is required and must match an existing entry in your system. If the value you are trying to import does not already exist in the database, the entire record will be rejected. The supplied organization must have the following characteristics as defined on the Organization >> Profile and Financials tabs:
|
||||||||||||||||
16 |
P |
Hold_Payments |
Valid values include Y or N (if not provided, N is the default for an entry). This value indicates whether a Vendor Invoice is on payment hold. Note that a payment hold can be established at either the Vendor level via the Vendor Profile or at a specific Vendor Invoice level. Being "on hold" merely indicates invoices for a Vendor will not automatically be considered for payment during the Bulk Vendor Payment Creation process. Users can continue to manually issue Vendor Payments via the Vendor Payment screens, though the on hold indicator will be displayed for informational purposes. In short, the system does not prevent payment for On Hold Vendors or Vendor Invoices. |
||||||||||||||||
17 |
Q |
Hold_Reason |
CONDITIONALLY REQUIRED. If the Hold_Payments field is Y and no value is supplied in this field, the record will reject. If the Hold_Payments field is N and a value is supplied in this field, the record will reject. You can use the special tag !BLANK! to remove an existing entry in this field. Maximum Length: 255 characters |
||||||||||||||||
18 |
R |
Recurring |
Valid values include Y or N (if not provided, N is the default for an entry). A value of "Y" indicates this is a recurring entry. If a value for Max_Occurrences has been provided, and this field is set to N, the record will be rejected. |
||||||||||||||||
19 |
S |
Max_Occurrences |
When designating a Vendor Invoice as a recurring entry, you have the option to specify the number of times you would like it to recur. This field is used to represent that value. This field is not required, even if the Recurring field has a value of Y. If a value is provided in this field while the value in the Recurring field is N, the record will be rejected. You can use the special tag !BLANK! to remove an existing entry in this field. This field accepts a numeric value (whole number). |
||||||||||||||||
20 |
T |
Invoice_Reference |
CONDITIONALLY REQUIRED. This field is intended to record the invoice number from the received Vendor Invoice, however, it can be used to capture any identifying external reference number to associate with the document. You can use the special tag !BLANK! to remove an existing entry in this field. Maximum Length: 50 characters |
||||||||||||||||
21 |
U |
Description |
This optional field is intended to capture any identifying code or value from an external source that you would like to enter and track along with the document. You can use the special tag !BLANK! to remove an existing entry in this field. Maximum Length: 128 characters |
||||||||||||||||
22 |
V |
Comments |
This optional field can be used to record a comment for the current financial document. You can use the special tag !BLANK! to remove an existing entry in this field. Maximum Length: 2000 characters |
||||||||||||||||
23 |
W |
Dtl_Acct |
CONDITIONALLY REQUIRED. This value is required if the current record is intended to insert a Vendor Invoice Detail entry. This field is used to supply the Detail record Account to which the Vendor Invoice transaction is to be posted. This value must match an existing account as defined on the Admin >> Setup >> Accounts screen. This value is required if the current record is intended to insert a Vendor Invoice Detail entry. The account supplied must have the following characteristics:
If the Account provided does not require project related details for the transaction, the record will be rejected if any values are supplied in the Dtl_Proj_Org_Code, Dtl_Proj_Code, Dtl_Task_Name, Dtl_Proj_Type, or Dtl_Exp_Type fields. |
||||||||||||||||
24 |
X |
Dtl_Org_Code |
CONDITIONALLY REQUIRED. This value is required if the current record is intended to insert a Vendor Invoice Detail entry. This field is used to supply the Detail record Organization to which the Vendor Invoice transaction is to be posted. This value must match an existing organization as defined on the Organization >> Profile and Financials tabs. The organization supplied must have the following characteristics:
|
||||||||||||||||
25 |
Y |
Dtl_Reference |
This is an optional field intended to capture any identifying external reference number or text to associate with the Detail line item. If not provided, this will default to the value provided in the Reference field (from the Summary area of the record). Maximum Length: 50 characters |
||||||||||||||||
26 |
Z |
Dtl_Description |
This is an optional field intended to capture any identifying code or value from an external source that you would like to enter and track along with the Detail line item. If not provided, this will default to the value provided in the Description field (from the Summary area of the record). Maximum Length: 128 characters |
||||||||||||||||
27 |
AA |
Dtl_Transaction_Date |
This date is the date to be associated with the Detail line item. If not provided, it will default to the vendor invoice document date.
Recommended date format: yyyy-MM-dd See Allowable Date Formats for more options. |
||||||||||||||||
28 |
AB |
Dtl_Amount |
REQUIRED. This field is used to supply the Amount associated with the Detail line item. This field can be zero, positive or negative. Prior to submitting a Vendor Invoice, the sum of the amounts applied in the Details associated with a particular Vendor Invoice must equal the supplied Invoice_Amount. This field accepts a numeric value up to 16 positions to the left of the decimal and 2 positions to the right. |
||||||||||||||||
29 |
AC |
Dtl_Proj_Org_Code |
CONDITIONALLY REQUIRED. If the Account provided has been identified as a project related account (that is, if the Account was configured on the Admin >> Setup >> Accounts screen with the Require Project indicator checked), this is a required field. Also, if the detail line type is specified, then this is a required field. This is the Project Organization code associated with the Dtl_Proj_Code supplied in the next field. The combination of these two values must match an existing Project Org / Project Code value defined in your system. If the value you are trying to import does not already exist in the database, the entire record will be rejected. If the Account provided does not require project related details for the transaction, the record will be rejected if any value is provided for this field. |
||||||||||||||||
30 |
AD |
Dtl_Proj_Code |
CONDITIONALLY REQUIRED. If the Account provided has been identified as a project related account (that is, if the Account was configured on the Admin >> Setup >> Accounts screen with the Require Project indicator checked), this is a required field. Also, if the detail line type is specified, then this is a required field. This is the Project Code associated with the Dtl_Proj_Org_Code supplied in the previous field. The combination of these two values must match an existing Project Org / Project Code value defined in your system. If the value you are trying to import does not already exist in the database, the entire record will be rejected. Valid projects will be limited to:
If the Account provided does not require project related details for the transaction, the record will be rejected if any value is provided for this field. |
||||||||||||||||
31 |
AE |
Dtl_Task_Name |
CONDITIONALLY REQUIRED. The value for this field will be required if the project (from above) is set to require task level expense reporting. This value must match an existing active Task Name value in your system (belonging to the Dtl_Proj_Code listed above). If the Task Name you are trying to import does not already exist in the database, the entire record will be rejected. If the Task has an Owning Organization defined, it must belong to the same Legal Entity as the Project's Legal Entity. If detail line type is expense, then tasks will be limited to those that allow expense reporting, either at the task level or at the related project level. If detail line type is labor, then tasks will be limited to those that allow time reporting, either at the task level or at the related project level. If detail line type is item, then tasks will be limited to those that allow item reporting, either at the task level or at the related project level. If a task is not a top-level task (e.g., it has subtasks), you must include a comma separated list of each task starting at the top level down. The following is an example of a valid task tree and how you would reflect each task using this import. Task Tree Access String
See the Excel Tips regarding the use of double quotes and more.
If the Account provided does not require project related details for the transaction, the record will be rejected if any value is provided for this field. |
||||||||||||||||
32 |
AF |
Dtl_Proj_Type |
CONDITIONALLY REQUIRED. If the Account provided has been identified as a project related account (that is, if the Account was configured on the Admin >> Setup >> Accounts screen with the Require Project indicator checked), this is a required field. The project type supplied must have the following characteristics:
If the Account provided does not require project related details for the transaction, the record will be rejected if any value is provided for this field. |
||||||||||||||||
33 |
AG |
Dtl_Exp_Type |
CONDITIONALLY REQUIRED. If the Account provided has been identified as a project related account (that is, if the Account was configured on the Admin >> Setup >> Accounts screen with the Require Project indicator checked), this is a required field. Also, if the detail line type is expense type, then this is a required field. The expense type supplied must have the following characteristics:
If the Account provided does not require project related details for the transaction, the record will be rejected if any value is provided for this field. The record will be rejected if the VI PO expense line specifies an expense type and this field does not specify the same one. |
||||||||||||||||
34 |
AH |
Dtl_Person_Username |
CONDITIONALLY REQUIRED. If a person is required for the detail line type, then this field is required. It is used to associate a named user to a financial document detail line item. Any active user can be associated with a financial document detail line item. |
||||||||||||||||
35 |
AI |
PO_Number |
This is an optional field for the associated Purchase Order (PO) number. If no PO related detail lines exist on this VI or you selected "first purge details" on the import screen, you can use the special tag !BLANK! to remove an existing entry in this field, or you can change to another PO. Note: The PO must be an original PO (not a Mod) with an OPEN status and the same legal entity and vendor as the vendor invoice. |
||||||||||||||||
36 |
AJ |
Dtl_Line_Type |
CONDITIONALLY REQUIRED. This is the PO detail line type. Allowed values are "L" for labor, "E" for expense, and "I" for item. This field is required if any other detail fields contain a value. |
||||||||||||||||
37 |
AK |
Dtl_Line_Id |
This is an optional field for the PO detail line ID. Values can be numeric from 1 to 9999, "PO", or blank. The "PO" value indicates that the PO does not have detail lines and the VI is related to the PO summary. The record will be rejected for the following reasons:
|
||||||||||||||||
38 |
AL |
Dtl_Hours |
CONDITIONALLY REQUIRED. This is a required field for the labor detail line hours if Dtl_Line_Type is "L". Format NUMBER (18,2) -- for example "1234567890.12". |
||||||||||||||||
39 |
AM |
Dtl_Quantity |
CONDITIONALLY REQUIRED. This is a required field for the item detail line quantity if Dtl_Line_Type is "I". This field will be rounded based on the precision level of the Unit of Measure (UOM). Format NUMBER (15,6) -- for example "1234567890.123456". |
||||||||||||||||
40 |
AN |
Dtl_Labor_Cost_Rate |
CONDITIONALLY REQUIRED. This is a required field for the detail labor cost rate if Dtl_Line_Type is "L". The record will be rejected if the VI PO labor line specifies a labor cost rate and this field does not specify the same one.
Format NUMBER (15,5) -- for example "1234567890.12345". |
||||||||||||||||
41 |
AO |
Dtl_Item_Cost_Rate |
CONDITIONALLY REQUIRED. This is a required field for the detail item cost rate if Dtl_Line_Type is "I". The record will be rejected if the VI PO item line specifies an item cost rate and this field does not specify the same one.
Format NUMBER (15,5) -- for example "1234567890.12345". |
||||||||||||||||
42 |
AP |
Dtl_Labor_Category |
CONDITIONALLY REQUIRED. Detail labor category is required if the project requires a labor category. Valid labor categories are:
The record will be rejected if the VI PO labor line specifies a labor category and this field does not specify the same one. |
||||||||||||||||
43 |
AQ |
Dtl_Item_Code |
CONDITIONALLY REQUIRED. This is a required field for the item detail line item code if Dtl_Line_Type is "I". Item must be valid for the project. If the cost account of the item's expense type is not the same as the detail account and an account/project type/expense type mapping does not exist, the record will be rejected. The record will be rejected if the VI PO item line specifies an item and this field does not specify the same one. |
||||||||||||||||
44 |
AR |
Dtl_UOM |
CONDITIONALLY REQUIRED. If Dtl_Item_Code is populated, then this field must be a valid UOM as defined on the Admin Setup Items page. Valid UOMs are:
The record will be rejected if the VI PO item line specifies a UOM and this field does not specify the same one. |
||||||||||||||||
45 |
AS |
Submitter |
Submitter must be an active user. Upon processing the first record having a different Document #, the Submitter will be imported from that first record and applied to subsequent records, until the next Document #. If a submitter is not provided, it will default to the user who is running the import. If the submitter is not authorized to submit a vendor invoice, then the VI will remain in INUSE status. |
||||||||||||||||
46 |
AT |
Approval_Group_Name |
CONDITIONALLY REQUIRED. Upon processing the first record having a different Document #, the Approval_Group_Name will be imported from that first record and applied to subsequent records, until the next Document #. Approval Group must be an existing Accounts Payable Approval Group. If the VI approval property is set to:
Note: If approvals are disabled or an approval group is not provided, submission will be attempted without an approval group, resulting in a status of Submitted if successful. |
||||||||||||||||
47 |
AU |
VI_Format_Name |
Must be an active format as defined on the Setup Vendor Invoice Formats page. When not provided, this is automatically populated based on the Vendor Profile settings for the Vendor and Legal Entity of the invoice. Maximum Length: 25 characters |
Note: Check out the Unanet Data Model for specific field data types, lengths, and other attributes. Navigate to Unanet Support and search for "Data Model".
The file to import must be saved in a comma delimited format. The fields can be enclosed in double quotes -- which would be particularly necessary should the data being imported contain commas.
If you are not using the default column layout sequence as defined above, you must include a header record (prefixed with an asterisk *), containing the column Header Names for those columns you are including in the import file (to indicate what data is contained in each column). The specific Header Names for each column are listed in the table above.
You can create the comma delimited import file with any number of tools. For those interested in using an Excel spreadsheet to create the file, you can download an Excel Template with predefined headers.
See the Excel Tips regarding the use of double quotes and more.
Depending on your property settings the Import Vendor Invoice screen may look like:
Field |
Description |
Type of Import |
Select the appropriate import type from the list of available types of imports. |
File to Import |
Enter (or browse for) the file that you would like to import. |
Output Options |
Specify the level of detail included in the output. |
Vendor Invoices |
This option instructs the import to completely delete all associated Detail records for each distinct Vendor Invoice being updated (based on Document #). Upon processing the first record having a different Document #, all associated Detail entries will be deleted, and then subsequent records having that same Document # will be processed and inserted. Essentially, this is a means to replace all Vendor Invoice details. Note that the Summary portion of the document will remain and its values would have been updated based on the values included on that initial record. |
Resulting Status |
This option controls whether each Vendor Invoice will end up with a status of INUSE or SUBMITTED. In order for the document to be placed in the SUBMITTED status, the following additional validations must pass (otherwise, the document will remain in the INUSE status regardless of this setting):
The default Resulting Status will be INUSE. |
Fiscal Period |
This option controls whether or not entries processed in the current file can make changes to a closed fiscal period. |
The import process is triggered by pressing the Import button. All Warnings and Error messages are written to the screen and can then be saved if desired. While some line items may be found invalid and rejected, the remaining line items will be successfully imported and saved in the database. The rejected line items will be written out to a file on the Unanet server as well as displayed at the bottom of the Import results page. The rejected record file name will be the import name (from the Type of Import drop-down on the Admin>> Import page) , concatenated with a space and "Errors.csv" (e.g., Item - Master Errors.csv, Alternate.csv, etc.). The file will be placed in the Unanet temporary directory (which is defined by the Temp Directory (Fully Qualified Directory Name) (unanet.temp_directory) Unanet property). You can also click on the Download the error file link at the bottom of the Import results page (sample image follows).
When importing a number of records associated with the same Vendor Invoice document (i.e., having the same Group Id on new records, or having the same Document Number on existing records), the Summary values from the first record encountered will be used and Summary values from subsequent records will be ignored (for that group / document).
Prior to submitting a Vendor Invoice, the sum of the amounts applied in the Details associated with that Vendor Invoice must equal the supplied Invoice_Amount.
This import can be used to import Vendor Invoice Summary and Details attributes.
Posted, Voided and Voiding documents cannot be modified. Updating a document in the Submitted status, will first change its status to Inuse (although, using the Resulting Status option, it can be returned to the Submitted status).
Application document for an existing Vendor Invoice cannot be imported.
If a record encounters an error, a message will be displayed but the import will continue and subsequent records for that same Vendor Invoice will be processed. Note that should an error happen, the resulting status for the entire Vendor Invoice will always be INUSE, regardless of the screen selection.
Note regarding VI related to a PO: If the import fields are blank (except for Legal Entity and Vendor, which must be specified on the import), then the import will default the values from the PO if the PO has values for those fields. If a field is required and not found on the PO, then the record will be rejected.
Validations
While you can save a financial document violating certain rules, you will not be permitted to submit or post the document until they are resolved. If any of the validations fail for any row on the document, the entire document cannot be posted.
Standard financial document validations for each Detail row:
Additional validations for VI related to a PO:
If a PO number is specified, then the following fields' values must equal the values specified on the PO, unless the PO value is blank, then any value can be specified on the VI import.
Sections | Fields |
Labor, Expense, Item Detail lines | Project, Task, Person |
Labor, Item Detail lines | Rate |
Labor Detail lines | Labor Category |
Expense Detail lines | Expense Type |
Item Detail lines | Item Code, UOM |