Import Expenses

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.

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

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 (i.e. Expense Report Number)

• The expense report must be in a LOCKED or EXTRACTED status.

• As a safety measure, you must also check the Expense Report 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.

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.

1. Person -- One expense report will be created for every distinct person in the import file.  This is the default behavior.

2. Person/Post Date -- One expense report will be created for every distinct person and post date combination in the import file.

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

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

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

Expense Report Option

This feature is used in conjunction with the Expense_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.