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.

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

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.

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.

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.

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.

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

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.

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.

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.

Import Screen

Field Descriptions:

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.

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.

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.

Expense Report Create Methods

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.