Import Expense

Privilege Needed: Administrator

This feature allows Administrators to import 'Expense' information into the Unanet system, allowing for the importing of data, for example, from an accounting system (e.g. AP, AR, GL) so that the data will be available on reports within Unanet. To align with the existing expense paradigm, these expenses will need to be assigned to a particular 'user' during the import. It may be appropriate to import the expenses to a Project Manager for a particular project, or perhaps you may choose to establish a 'dummy' expense import user for such purposes.

Expense imports will have the following restrictions:

you can not import data for any of the built-in 'wizard' expense types -- 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 expense types that include wizards, check out the wizard page.
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 can not update existing expense reports via this import feature

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 (see unasense.import.expense_submit.display property below):

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. Additionally, there will be three options for creating expense reports:

One expense report will be created for every distinct person in the import file. This is the default behavior.
One expense report will be created for every distinct combination of person and project in the 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.
One expense report will be created for every line in the import file.

The table below shows all the fields that can be imported for expenses.

	Field Name	Required/Description
1	Owner	ALWAYS REQUIRED. Valid Unanet username that uniquely identifies a user. If the username you are trying to import does not already exist in the database, the entire record will not be imported. You will see the message: The username "..." is not valid. Line...
2	Purpose	CONDITIONALLY REQUIRED. Required for any line that results in the creation of an expense report, if the system property, unasense.purpose.required, is true. Ignored in all subsequent lines for the same expense report.
3	Location	CONDITIONALLY REQUIRED. Required for any line that results in the creation of an expense report, if the system property, unasense.location.required, is true. Ignored in all subsequent lines for the same expense report.
4	Project Organization Code	ALWAYS REQUIRED. This code uniquely identifies the Organization to which the project belongs. If the Organization you are trying to import does not already exist in the database, the entire record will not be imported. You will see the message: The organization "..." is not valid. Line....
5	Project Code	ALWAYS REQUIRED. This project code identifies the project for which the imported expense was reported. The project code is unique within an Organization. That is, an Organization can not have two projects with the same Project Code. Different Organizations, however, can have projects with the same Project Code. This is the code that appears on most screens and reports. The Project must be set to a status that is 'open'. If the Project you are trying to import does not already exist in the database, the entire record will not be imported. You will see the message: The project "..." is not valid. Line....
6	Task Name	CONDITIONALLY REQUIRED. The value for this field will be required if the project is set up to allow task level expense reporting only. This task name should be unique within a project. That is, a project should not have two tasks with the same Task Name. This Task Name is limited to 50 characters. If a task with this name does not exist for this project, the entire record will not be imported. You will see the message: The task "..." is not valid. Line .... In addition, if the task is a part of a multi-level task hierarchy and has parent task(s), the field is expected to contain a complete comma-separated double-quoted path to the task. For example, if the desired task has a parent, the field should contain the following value: “parent name, child name”.
7	Expense Date	ALWAYS REQUIRED. The date the expense was incurred. No range checking is performed. This must be a valid date in one of the following two formats: 'yyyy-MM-dd' or a locale specific default short date format. For example, the default short date format for the US is 'MM/dd/yy', the default short date format for most European countries is 'dd/MM/yy'. If the date you are trying to import is not a valid date or is not in one of the formats specified above, the entire record will not be imported. You will see the message: The date "..." is not valid. Line...
8	Expense Type	ALWAYS REQUIRED. This code uniquely identifies the Expense Type. The expense type may be enabled or disabled. If the Expense Type you are trying to import does not already exist in the database, the entire record will not be imported. You will see the message: The expense type "..." is not valid. Line....
9	Currency Code	ALWAYS REQUIRED. This code uniquely identifies the Currency Code. If the Currency Code you are trying to import does not already exist in the database, the entire record will not be imported. You will see the message: The currency code "..." is not valid. Line....
10	Amount	ALWAYS REQUIRED. Positive dollar amount.
11	Exchange Rate	CONDITIONALLY REQUIRED. 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.
12	Payment Method	ALWAYS REQUIRED. This code uniquely identifies the Payment Method. Valid values are either: “1” for Employee -- or the text string "Employee" “2” for Corp. Card -- or the text string "Corp. Card" “3” for Company Paid -- or the text string "Company Paid" If the Payment Method you are trying to import is not one of these values, the entire record will not be imported. You will see the message: The payment method "..." is not valid. Line....
13	Project Type	This code uniquely identifies the Project Type. If the Project Type you are trying to import does not already exist in the database, the entire record will not be imported. You will see the message: The project type "..." is not valid. Line.... If no value was provided for this field, the Project Type that was specified for the project by the Project Manager on the Project Page will be used.
14	Comments	This field can be used to store user specified comments. This field can store up to 2000 characters and it can be left blank.
15	Receipt Provided	CONDITIONALLY REQUIRED. Is a receipt provided for this expense item? Valid values are: "Y" or "N". If the value you are trying to import is not one of these values, the entire record will not be imported. You will see the message: The receipt provided value "..." is not valid. Line....
16	No Receipt Reason	CONDITIONALLY REQUIRED.. Why not? Required if the expense type requires a receipt, and the value of "receipt provided" is "N".
17	Vendor Name	CONDITIONALLY REQUIRED. Required if the expense type requires a vendor name.
18	VAT Amount	Value Added Tax amount.
19	VAT Location	CONDITIONALLY REQUIRED. A valid VAT location. Required if VAT Amount value is supplied and non-zero. If the VAT Location you are trying to import is not valid, the entire record will not be imported. You will see the message: The VAT Location "..." is not valid. Line....

Import File Format

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.

Examples:

field1,field2,field3
"field1","field2","field3"
field1,"data containing commas (like this), should be double quoted",field3

Other file format requirements:

Each record must be in the same order as the table above
Note that some fields are always required, while others are conditionally required
If you don't want to import information for a non-required field, just leave this field blank
If a given record does not contain a value for every field, the import will assume the remaining values are blank

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 click here to download an Excel Template with predefined headers and required fields.

EXCEL NOTE: the fields that start with ** are required fields.

EXCEL NOTE: Excel may add the double quotes around fields for you. If using the excel spreadsheet template -- note that excel will automatically put double quotes around fields that contain commas. Manually adding double quotes within excel would result in two sets of double quotes in the .csv file and thus cause an error on import.

EXCEL NOTE: When creating a comma delimited file using Excel, the saved ASCII text file will contain a header row. This row will be ignored by the import process and will not create any import warnings. The import process will ignore the header (as well as any other rows) that begin with: #OWNER# or **

Import Expense Screen

The Import Expense screen looks like:

Import File: Here the Administrator specifies the file name to be imported.

Create Method: The Administrator will be given an option as to how many expense reports should be created from the import file contents.

One expense report will be created for every distinct person in the import file. This is the default behavior.
One expense report will be created for every distinct combination of person and project in the 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.
One expense report will be created for every line in the import file.

Resulting Status: In addition to specifying the file name, 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 Administrator’s ability to do so will be controlled by a new property unasense.import.expense_submit.display. If the property is set to true, a group of radio boxes allowing the change of the expense report status will be added to the page. The default Resulting Status will be INUSE. By default the property will be set to false and the radio buttons will not be displayed on the page. In that case, the expense reports created by the import process will remain in the INUSE status.

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 unanet.temp_directory Unanet property).

Processing Rules

Expense Report Create Methods

Unlike time items, there is no combination of attributes for an expense item that define a unique element. There is also no way to define which existing expense report should be the recipient of a new imported expense item. Therefore, every successfully imported line in an expense import file will result in a new expense item. Also, every expense import file that has at least one successfully imported line will result in the creation of at least one new expense report.

The expense reports created will depend on the create option selected for the import:

Person

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/Project

This option results in the creation of one expense report for each unique combination of:

username,

project organization code, and

project code

that is 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.

Expense Item

When this option is used, every successfully imported expense line will result in the creation of a new expense report.

Approval History and Notification

An approval history entry will be generated for each expense report status change triggered by the import process. All e-mail 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.

Command Line Import

As an alternative to using the Import Screen described above, you can also invoke imports using the command line import functionality. This functionality allows you to invoke an import without accessing the Unanet system application (user interface). This may be useful if you would like to write an external process to extract data from an upstream system and programmatically load it into Unanet (without user intervention).

Return to Unanet Table of Contents page