Import Expenses

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.

Roles

This screen is available to users having any of the following roles: Administrator and P&R Administrator.

License

This feature is available with any Unanet edition.

Performance Tip Note: Depending on the number of users in your installation and other factors such as the overall performance of your platform, the activity of importing one or many files may take up to several minutes. Should this be the case with your installation, you may want to consider running these processes at non-peak times to minimize database contention.

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)

Import File Record Layout

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

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.

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

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

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.

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.

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

1. Car Repair	"Car Repair"
1.1 Tires	"Car Repair,Tires"
1.2 Paint	"Car Repair,Paint"
1.2.1 Prep work	"Car Repair,Paint,Prep work"
1.2.2 Patching	"Car Repair,Paint,Patching"
1.3 Engine	"Car Repair,Engine"
1.3.1 Tune-up	"Car Repair,Engine,Tune-up"
1.3.1 Rebuild	"Car Repair,Engine,Rebuild"

See the Excel Tips regarding the use of double quotes and more.

Expense_Date

ALWAYS REQUIRED. The date the expense was incurred.

Recommended date format: yyyy-MM-dd See Allowable Date Formats for more options.

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.

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.

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.

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.

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).

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.

Comments

This field can be used to store user specified comments and can be left blank.

Maximum Length: 2000 characters

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.

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

Vendor_Name

CONDITIONALLY REQUIRED. Required if the expense type requires a vendor name.

Maximum Length: 50 characters

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.

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.

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.

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 the status of the expense report being imported is INUSE, then no additional validations will occur for the cost_account (but do note that when a user edits and submits the expense report via the user interface, the system will re-derive the cost account value during that process).
If the status of the expense report being imported is anything other than INUSE, no other validations will occur (and the value provided will be used even if it may differ from what the system would have derived on it's own).

If a cost account is not provided, additional validations may occur as follows:

If the status of the expense report being imported is INUSE, then no additional validations will occur for the cost_account.
If the status of the expense report being imported is anything other than INUSE and the Post to General Ledger (unanet.financials.post_to_gl) property is enabled, and the line item date is >= the Post to General Ledger Effective Date (unanet.financials.post_to_gl.eff_date), the system will attempt to derive a cost account. See Posting Groups >> Expense Report Cost Account Logic for account defaulting details.

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.

Delete

This field can be used to trigger the system to delete an expense report.

In order for the delete to happen:

You must include the following text string !DELETE! in this field.
This field works in conjunction with the Exp_Voucher field which must have a valid expense report key provided (ie Voucher Number)
The expense report must be in a LOCKED or EXTRACTED status.
As a safety measure, you must also check the Voucher Option to Allow Deletion in order for the import to process delete requests.

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.

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.

Importing a Sub-Set of Columns using a Field Header Record

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.

Excel Template

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.

Import Screen

Depending on your property settings,the Import Expense screen may look like:

Field Descriptions:

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. Person -- One expense report will be created for every distinct person in the import file. This is the default behavior. 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 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. Person/Project/Post Date -- One expense report will be created for every distinct combination of person, project and post date 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 and/or having multiple post dates. Expense Item -- One expense report will be created for every line in the import file. 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. Note that if your site is configured to use a custom stored procedure to validate saved or submitted expenses, that special validation will not apply to expenses loaded via the expense import.
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).

Processing Rules

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.

Within one expense import file, an Administrator can import expense data for one to many people for different expense reports.

Expense Report Create Methods

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.

Approval History and Notification

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.