This feature allows for the importing of new expense reports or the deletion of existing (LOCKED or EXTRACTED) expense reports.
Note that you can only import new expenses, that is, you cannot update existing expense reports via this import feature.
This screen is available to users having any of the following roles: Administrator and P&R 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)
|
|
Field Header Name |
Required/Comments |
||||||||||||||||
1 |
A |
Username |
ALWAYS REQUIRED. Unique username (user id), for example JDoe or jdoe (it does not need to be capitalized.) This value must match an existing Username value in your system. If the Username you are trying to import does not already exist in the database, the entire record will be rejected. This must be an active user having the expense user role. |
||||||||||||||||
2 |
B |
Purpose |
CONDITIONALLY REQUIRED. Required for any line that results in the creation of an expense report, if the system property, Require Purpose on Expense Reports (unasense.purpose.required) is true. Ignored in all subsequent lines for the same expense report. Maximum Length: 2000 characters |
||||||||||||||||
3 |
C |
Location |
CONDITIONALLY REQUIRED. Required for any line that results in the creation of an expense report, if the system property Require Location on Expense Reports (unasense.location.required) is enabled. Ignored in all subsequent lines for the same expense report. Maximum Length: 255 characters |
||||||||||||||||
4 |
D |
Project_Org_Code |
ALWAYS REQUIRED. This code uniquely identifies the Organization to which the project belongs. This value must match an existing Project Organization Code value in your system. If the Organization you are trying to import does not already exist in the database, the entire record will be rejected. |
||||||||||||||||
5 |
E |
Project_Code |
ALWAYS REQUIRED. This project code identifies the project to which this Expense is being charged. The project code is unique within an Organization. This value must match an existing Project Code value in your system. If the Project you are trying to import does not already exist in the database, the entire record will be rejected. |
||||||||||||||||
6 |
F |
Task_Name |
CONDITIONALLY REQUIRED. The value for this field will be required if the project is set to require task level expense reporting. This value must match an existing Task Name value in your system. If the Task Name you are trying to import does not already exist in the database, the entire record will be rejected. If a task is not a top-level task (e.g. it has sub-tasks), 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. |
||||||||||||||||
7 |
G |
Expense_Date |
ALWAYS REQUIRED. The date the expense was incurred.
Recommended date format: yyyy-MM-dd See Allowable Date Formats for more options. |
||||||||||||||||
8 |
H |
Expense_Type |
ALWAYS REQUIRED. This code uniquely identifies the Expense Type. This value must match an existing Expense Type value in your system. If the Expense Type you are trying to import does not already exist in the database, the entire record will be rejected. Expenses can be imported to active or inactive expense types. Further, project level expense type restrictions are not honored (ie an Admin can import to any expense type). Expenses cannot be imported to any of the expense types that have been associated with a built-in wizard. This includes the ADVANCE and CASH-RETURN expense types. See Processing Rules for important restrictions below. |
||||||||||||||||
9 |
I |
Currency_Code |
This code uniquely identifies the Currency Code used for the particular expense (e.g. USD). This value must match an existing Currency value defined in your system. If the Currency Code you are trying to import does not already exist in the database, the entire record will be rejected. If no code is provided, your system default currency code will be supplied. |
||||||||||||||||
10 |
J |
Amount |
ALWAYS REQUIRED. Positive dollar amount for the line item being imported. This field accepts a numeric value up to 16 positions to the left of the decimal and 2 positions to the right. |
||||||||||||||||
11 |
K |
Exchange_Rate |
Exchange rate from the expense amount’s currency code to the company currency code. If the currency code is the company code, or is blank, then this field must be blank or 1. If the currency code is other than the company currency code, and you have not supplied a value, a value of 1 will be used. This field accepts a numeric value up to 12 positions to the left of the decimal and 6 positions to the right. |
||||||||||||||||
12 |
L |
Payment_Method |
ALWAYS REQUIRED. This code uniquely identifies the Payment Method. This value must match an existing Payment Method value defined in your system. If the Payment Method you are trying to import is not a valid value in the database, the entire record will be rejected. Note that the Payment Method must be configured on the Admin >> Setup >> Payment Methods screen to be Allowed for Expense Reporting. Also, if the user associated with the import has a Non-Employee Classification, the Payment Method must be configured to be allowed for non-employees. Importing expenses to inactive Payment Methods is allowed (primarily in support of historical data loading). |
||||||||||||||||
13 |
M |
Project_Type |
This code uniquely identifies the Project Type. This value must match an existing Project Type value defined in your system. If the Project Type you are trying to import is not a valid value in the database, the entire record will be rejected. If no value is provided for this field, the task's default Project Type will be used, that is, if the expense is associated with a task and that task has a default project type, otherwise the project's default Project Type will be used. Expenses can be import using both active and inactive project types. |
||||||||||||||||
14 |
N |
Comments |
This field can be used to store user specified comments and can be left blank. Maximum Length: 2000 characters |
||||||||||||||||
15 |
O |
Receipt_Included |
CONDITIONALLY REQUIRED. Indicates whether a receipt has been provided or not. Valid values are: "Y" or "N". If the value you are trying to import is not one of these values, the entire record will be rejected. |
||||||||||||||||
16 |
P |
No_Receipt_Reason |
CONDITIONALLY REQUIRED. If a receipt is required and you have not provided one, you can supply that reason in this field. Required if the expense type requires a receipt, and the value of "receipt provided" is "N". Maximum Length: 128 characters |
||||||||||||||||
17 |
Q |
Vendor_Name |
CONDITIONALLY REQUIRED. Required if the expense type requires a vendor name. Maximum Length: 50 characters |
||||||||||||||||
18 |
R |
VAT_Amount |
Value Added Tax amount. This field accepts a numeric value up to 15 positions to the left of the decimal and 3 positions to the right. |
||||||||||||||||
19 |
S |
VAT_Location |
CONDITIONALLY REQUIRED. Required if the VAT Amount value is supplied and is non-zero. This value must match an existing VAT Location value defined in your system. If the VAT Location you are trying to import is not valid, the entire record will be rejected. |
||||||||||||||||
20 |
T |
Post_Date |
A valid date string . You can only supply a post date if the Resulting Status is EXTRACTED. Only used for any line that results in the creation of an expense report. Ignored in all subsequent lines for the same expense report. Recommended date format: yyyy-MM-dd See Allowable Date Formats for more options. |
||||||||||||||||
21 |
U |
Cost_Account |
This field is used to indicate the cost account to be associated with the expense line item. If a cost account is provided, it must match an existing account value as defined on the Admin >> Setup >> Accounts screen.
If a cost account is not provided, additional validations may occur as follows:
|
||||||||||||||||
22 | V | Exp_Voucher | CONDITIONALLY REQUIRED. This field is used in conjunction with the Delete capability and a value must be supplied in order for a delete to occur. The value provided in this field must be the expense report key (ie Expense Report Voucher Number) for a LOCKED or EXTRACTED expense report you would like to delete. |
||||||||||||||||
23 | W | Delete |
This field can be used to trigger the system to delete an expense report. In order for the delete to happen:
Note that you cannot delete an expense report if any of the line items have been included in the Expense Report Cost Post or the Billing & Revenue Post or a vendor invoice has been created from the expense report via the Create Vendor Invoice from Expense Report process. |
Note: Check out the Unanet Data model for specific field data types, lengths, and other attributes.
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 indicated 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 and required fields noted.
See the Excel Tips regarding the use of double quotes and more.
Depending on your property settings,the Import Expense 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. |
Create Method |
The Administrator will be given an option as to how many expense reports should be created from the import file contents.
See additional Create Method information below. |
Resulting Status |
The Administrator will be given an opportunity to trigger the expense report submit process or force the expense report status to become INUSE, SUBMITTED, COMPLETED, LOCKED or EXTRACTED, potentially bypassing the regular approval process. The default Resulting Status will be INUSE. Expenses cannot be charged against projects having an inactive Owning Org nor against inactive Person Orgs. See additional Resulting Status information below. |
Voucher Option |
This feature is used in conjunction with the Exp_Voucher and Delete fields to facilitate the removal of existing expense reports. See the description of these two fields for more information about deleting expense reports. This check box is a safety measure to prevent the accidental removal of an expense report, and thus must be checked in order for the import to process delete requests. |
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 Expense Import user feedback screen. The rejected record file name will be UnanetExpenseImportErrorxxx, where xxx is a randomly generated number. 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).
Expense imports will have the following restrictions:
you cannot import data into any expense type having an association with a built-in 'wizard' (including advance and cash-return) -- for example: if you need to import Mileage expense data, you would need to setup a custom expense type such as IMPORTED_MILEAGE. You could then disable this expense type if you didn't want your users to have access to it. For a complete list of built-in wizards, check out the wizard help.
you can only import expense reports, not expense requests
you can only import expenses allocated to one project (not across multiple projects)
you can only import new expenses -- that is, you cannot update existing expense reports via this import feature
using the Allow deletion option, you can delete expense reports in the LOCKED or EXTRACTED status.
Note that you cannot delete an expense report if any of the line items have been included in the Expense Report Cost Post or the Billing & Revenue Post or a vendor invoice has been created from the expense report via the Create Vendor Invoice from Expense Report process.
Within one expense import file, an Administrator can import expense data for one to many people for different expense reports.
The Administrator is given an option to:
Submit the expense report, thus entering the standard Unanet approval process
Force the expense report into the COMPLETED or LOCKED status thus disabling any further manipulation of the expense report and bypassing the approval process
Force the expense report into the EXTRACTED status (this may be useful to prevent re-extracting imported expenses)
Further, it is worth noting that you will not be able to modify any existing expense reports. Instead, the import will trigger the creation of new expense reports.
The following options exist for creating expense reports:
Person -- One expense report will be created for every distinct person in the import file. This is the default behavior.
This option results in the creation of one expense report for each username that has at least one item successfully imported from the expense import file. The purpose and location fields from the first successfully imported line are used. These fields are ignored in all subsequent lines for the same expense report.
Person/Post Date -- One expense report will be created for every distinct person and post date combination in the import file.
Person/Project -- One expense report will be created for every distinct combination of username, project organization code and project code in the import file (which is successfully imported from the expense import file). In this case, a person may have multiple expense reports created as a result of a single import, if they have expenses against multiple projects.
The Purpose and Location fields from the first successfully imported line are used (these fields are ignored in all subsequent lines for the same expense report).
Person/Project/Post Date -- One expense report will be created for every distinct combination of username, project organization code, project code and post date in the import file (which is successfully imported from the expense import file). In this case, a person may have multiple expense reports created as a result of a single import, if they have expenses against multiple projects and/or having multiple post dates.
Expense Item -- One expense report will be created for every line in the import file.
Every successfully imported line in an expense import file will result in a new expense item. Every expense import file that has at least one successfully imported line will result in the creation of at least one new expense report.
An approval history entry will be generated for each expense report status change triggered by the import process. All email notifications available and enabled in the Unanet installation and associated with the expense report status changes triggered by the import process will be sent out.