Configuring the Expenses Spreadsheet

This chapter covers the following topics:

Disconnected Expense Reporting Process

Employees who are travelling or do not have access to the company's intranet can create expense reports using the disconnected expense reporting process. The disconnected expense reporting process involves entering expenses in a Microsoft Excel spreadsheet on a personal computer or laptop and then importing this spreadsheet into Internet Expenses.

Internet Expenses provides a Microsoft Excel expense spreadsheet template to create a disconnected expense report. You can configure this spreadsheet template to meet the needs of your company. See: Configuring the Microsoft Excel Template for more information.

Note: If you use the per diem or mileage rate schedules functionality, users need to enter these expenses online.

To submit a disconnected expense report, an employee completes these steps:

  1. Log in to Internet Expenses.

  2. Click the Spreadsheet Export link and save the spreadsheet template to your local computer.

  3. Open the spreadsheet template in Microsoft Excel and create a working copy using the Save As command.

  4. Enter expenses in the spreadsheet using Microsoft Excel.

  5. When the spreadsheet is complete, log in to Internet Expenses and click the Expenses Entry link.

  6. Click the Import Spreadsheet Expenses button to display the Import Expense Report Spreadsheet page.

  7. Copy the contents of the spreadsheet in Microsoft Excel, then paste the contents in the Spreadsheet Import Area.

    Note: Be sure to paste the spreadsheet only once into the Spreadsheet Import Area. If a user pastes the spreadsheet more than once and then successfully uploads it, Internet Expenses creates an expense report with duplicate receipts.

  8. Click Skip to Review or Continue to ensure there is no missing or invalid data in the report. If the spreadsheet contains errors:

    1. Clear the contents of the upload page.

    2. Modify the spreadsheet in Microsoft Excel.

    3. Copy the contents of the spreadsheet, then paste the contents in the Spreadsheet Import Area.

    4. Click Skip to Review or Continue.

  9. When the spreadsheet passes validation, a new expense report is created in Internet Expenses. An employee can enter additional expenses, save the report as a work in progress, or submit it for approval.

    Note: All users assigned to the seeded Internet Expenses responsibility can access the Spreadsheet Export and Import Spreadsheet Expenses functions.

Configuring the Microsoft Excel Template

The Microsoft Excel template is a spreadsheet that employees use to create disconnected expense reports. You must configure the Microsoft Excel template so that it matches your current Internet Expenses implementation. For example, all fields in Internet Expenses must exist in your template, and fields that are set as Required in Internet Expenses must also be required in the template. If the template does not match your implementation, employees cannot create and import expense reports using the disconnected process.

To configure the Microsoft Excel template, you must enter specific values from various Oracle Payables windows into the template. Once you have configured the Microsoft Excel Template, remove the seeded function from the menu for exporting the spreadsheet. The seeded OIE Import Page function is assigned to the Expenses Shortcuts menu.

To configure the Microsoft Excel template and make it available to your users:

  1. Access the apwexpmc.xls spreadsheet from the $AP_TOP/html/US directory.

  2. Configure the spreadsheet according to your company's business needs.

  3. Save the spreadsheet using the method for either a single spreadsheet or multiple spreadsheets.

    For a Single Spreadsheet

    • Save the spreadsheet using a different filename to the $OA_HTML/US directory.

    For Multiple Spreadsheets

    • Save the configured spreadsheets to the OA_HTML/<language> virtual directory on the Web Server. The language component of the directory path is established for each language installed in your system.

    • Create an HTML page with links to the spreadsheets and place the HTML page in the same OA_HTML/<language> virtual directory.

  4. Log into the System Administrator responsibility.

  5. Navigate to the Form Functions window.

  6. In the Description tab, create a new function by entering values for Function, User Function Name, and Description. Starting your Function with Oracle Internet Expenses is recommended.

  7. In the Properties tab, enter SSWA plsql function (WWW) or JSP Interoperable with OA in the Type field.

  8. Choose the Form tab.

    1. For a single spreadsheet, enter in the Parameters field: p_filename=<name of your configured spreadsheet from step 3>.

    2. For multiple spreadsheets, enter in the Parameters field: p_filename=<name of your HTML file from step 3>.

  9. Choose the Web HTML tab. In the HTML Call field, enter: US/<name of your HTML file from step 3>. xls.

  10. Choose the Web Host tab. Leave the Encrypt Parameters check box unchecked.

  11. Save your work.

  12. Navigate to the Menus window.

  13. In the User Menu Name field, query the menu that is used with the expense reporting responsibility assigned to your employees. The Expenses Main Menu (New) (OIE_MAIN_MENU_NEW) is assigned to the seeded Internet Expenses responsibility. If you are using Oracle Time and Labor (OTL), use the Expenses Main Menu (OIE_MAIN_MENU.)

    If you want to add the function to the Expenses sub-menus instead, query the Expenses Tab Bar (OIE_EXPENSES_TAB_BAR) menu.

    If you want to link the multiple spreadsheets from the Expenses Home page, personalize the page button region to add a button or modify the existing button. On the Personalize Page: Expenses Home page, set the Destination URL on the Page Button Bar to the function you created in the previous steps (for example OA.jsp?OAFunc=<new function name>.)

    Note: The Expenses Home page function is associated with the Expenses Main Menu.

  14. In the Function field, add the new function you defined in the previous steps and a prompt for that function.

  15. Bounce Apache.

Required Tasks

Perform these tasks to configure the Microsoft Excel template:

Conditional Tasks

Perform these tasks if required by your spreadsheet configuration:

See also: Other Setup Considerations.

Note: You must use Microsoft Excel 5.0 or higher to configure the expense report spreadsheet template.

Displaying the Sheet Tabs

After obtaining the expense spreadsheet template from the operating system directory, display the custom sheet tabs to make any modifications.

You can modify these sheets in the Microsoft Excel template:

To display the custom sheet tabs in the expense spreadsheet template:

  1. Open the spreadsheet file apwexpmc.xls.

  2. Choose Tools > Protection > Unprotect Sheet.

  3. Choose Options from the Tools menu.

  4. Open the View tab.

  5. Check the Sheet Tabs box, then click OK.

Configuring the Expense Report Sheet

This section describes how to configure the Expense Report sheet.

Defining the Date Format

Users enter a receipt date in the spreadsheet Date From column. For multiple–day receipts, users can define a range of days by entering dates in both the Date From and the Date To columns.

When an employee imports a completed expense report, Internet Expenses compares the date format of the Date From and Date To columns to the date format set using the self-service applications General Preferences function. If the date formats in these columns do not match the date format specified by the General Preferences function, users must re–enter receipt dates in Internet Expenses. An example of a valid date format is DD–MON–YYYY. When a user makes changes to the General Preferences, the changes only affect that user's login session.

  1. Open the Expense Report sheet tab at the bottom of the spreadsheet.

  2. Select the Date From and the Date To column, then choose Format > Cells.

  3. Open the Number tab, then choose Date as the Category.

  4. Choose a date format from the list in the Type region, then click OK.

Entering an Expense Template Name

You must assign a template name to the Microsoft Excel template to define the list of expense types that are available in Internet Expenses (for example, airfare, meals, lodging, and so on). This template also determines the expense types you can include in the Expense Types sheet in the Microsoft Excel template. See also: Setting Up Expense Types.

You create expense report templates in the Payables Expense Report Templates window. The template you choose must have the Enable for Internet Expenses option checked (set to Yes).

When an employee imports a Microsoft Excel expense report, Internet Expenses compares the template name to the names of all active, Internet Expenses-enabled templates. If the template name of a Microsoft Excel expense report does not match the name of a Internet Expenses-enabled template, Internet Expenses rejects the report.

Note: If multiple expense report templates have been defined for use with Internet Expenses, you must set up a separate custom Microsoft Excel template for each template.

To enter a template name:

  1. Open the Expense Report sheet tab at the bottom of the spreadsheet.

  2. Enter the name of an expense report template that is enabled for Internet Expenses into the Expense Template cell.

    The name must exactly match the name of an Internet Expenses-enabled template that appears in the Payables Expense Report Templates window.

  3. Save your work.

Modifying the Reimbursable Amount Formula

The predefined formula for the Reimbursable Amount column of the spreadsheet is (receipt amount * exchange rate). This formula is provided to match the default setting of No for the Journals: Display Inverse Rate profile option.

If you set this profile option to Yes, you must change the Reimbursable Amount formula for all cells in this column to reflect the inverse of this formula (receipt amount / exchange rate).

To edit the Reimbursable Amount formula:

  1. Place the cursor in the first cell of the Reimbursable Amount column.

  2. Change the multiplication symbol (*) to the division symbol (/), then press Enter.

  3. Place the cursor over the square in the lower right corner of the cell that you just changed, so that the cross cursor changes to a solid cross. Press and hold the right-click button on your mouse, and while holding the right-click drag down so that all of the remaining cells in this column are highlighted.

  4. Release the right-click, and all of the formulas for these cells are automatically updated.

Modifying Other Attributes

To modify the default settings for the reimbursement currency and the Receipt Missing check box, use the Cell Links sheet. See: Modifying Cell Links for more information.

Protecting Formulas

The values for Days, Receipt Amount, and Reimbursable Amount columns are derived from formulas. To prevent users from encountering unexpected errors, you should protect these formulas so that users do not manually enter data in the column cells.

To protect column cells:

  1. Choose Options from the Tools menu.

  2. In the Options window, open the View tab and check the Row & Column Headers box to make the row and column headers visible.

  3. Click OK.

  4. Open the Expense Report sheet tab at the bottom of the spreadsheet.

  5. For the Days column, click the column header so that the entire column is highlighted.

  6. Choose Cells from the Format menu.

  7. In the Format Cells window, open the Protection tab and check the Locked box.

  8. Click OK.

  9. Repeat steps 4 to 8 for the Receipt Amount and Reimbursable Amount columns.

Setting Up Currency Codes

When an employee imports an expense report spreadsheet, Internet Expenses compares the currency code of a receipt with the currency codes enabled in the Currencies window. If the receipt currency code does not match an enabled currency, the user must re–enter the receipt currency code in Internet Expenses.

The predefined Currency Codes and Descriptions sheet includes nearly two hundred currencies (all specified in ISO Standard #4217). Because some of these currencies may not be enabled in the Currencies window, you must edit the Currency Codes and Descriptions sheet to include only enabled currencies. You should first review the list of enabled currencies in the Currencies window, then remove all disabled currencies from the Currency Codes and Descriptions sheet.

Note: The Currency Codes and Descriptions sheet must contain at least one currency.

After you update the Currency Codes and Descriptions sheet, you may need to make these modifications to the Cell Links sheet:

See: Modifying Cell Links for more information.

When you make changes to the Currency Codes and Descriptions sheet, you can use the Expense Report sheet to confirm your changes.

Prerequisite

Before you can set up currency codes, you must review and make a list of all enabled currencies in the Currencies window.

To set up currency codes:

  1. Open the Currency Codes and Descriptions sheet tab at the bottom of the spreadsheet.

    Note: Do not edit the header region (cells A1 and B1) of the Currency Codes and Descriptions sheet.

  2. Delete each row that contains a disabled currency.

  3. Realign the remaining currencies to create a sequential list:

    • Leave row 2 (cells A2 and B2) blank.

    • Do not leave gaps of empty rows between currency codes.

  4. Open the Expense Report sheet tab at the bottom of the spreadsheet to review the expense report and verify your changes.

  5. Save your work.

  6. Update the Cell Links sheet to reflect the changes to the Currency Codes and Descriptions sheet.

This illustration depicts the Currency Description and Currency Code columns. Delete all the currencies that you will not use. Do not edit the labels in row 1. Leave row 2 blank.

the picture is described in the document text

Related Topics

Defining Currencies, Oracle General Ledger User's Guide

Setting Up Expense Types

To enter expense types in the Expense Report sheet, users select values from a choice list. The Expense Types sheet contains the values that appear in this choice list. You must configure the Expense Types sheet so it contains only valid expense types. Valid expense types include:

For example, the Expense Report Templates window in Payables lists the expense types Airfare, Car Rental, Hotel, and Entertainment in the Sales template, and no expense types from any other template have the Viewable From All Templates option checked. If you enter Sales as the Expense Template for the spreadsheet, you must set up the Expense Type sheet so it contains only the expense types Airfare, Car Rental, Hotel, and Entertainment.

The generic Expense Types sheet within the Microsoft Excel template includes several sample expense types. You must change the sample expense types to valid expense types and delete any rows that you do not change. To add expense types, insert new rows into the Expense Types sheet. You must insert a new row above an existing row for the expense type to appear in the choice list.

After you update the Expense Types sheet, you may need to update the expense types that appear in the expense report template. (By default, the template does not display expense types.) See: Modifying Cell Links for more information.

When you make changes to the Expense Types sheet, you can use the Expense Report sheet to confirm your changes.

Prerequisite

Make a list of the expense types defined by the self–service enabled template you entered in the Expense Report sheet. Note the exact spelling of these expense types.

To set up expense types:

  1. Open the Expense Types sheet tab at the bottom of the spreadsheet.

  2. Change the sample expense types to valid expense types (those defined in the template you entered in the Expense Report sheet). You must spell each expense type exactly as it appears in the Payables Expense Report Templates window.

  3. To add an expense type, insert a new row and enter the name of the expense type exactly as it appears in the Payables Expense Report Templates window.

    To insert a new row, select a cell that contains a value, then choose Insert > Rows. If you select a cell that does not contain a value and use Insert > Rows to add an expense type to this new row, the expense type does not appear in the choice list.

    Note: Do not edit the header region (cells A1 and A2) of the Expense Types sheet.

    Note: Sort the list of expense types alphabetically. To do this, select the rows to sort, then click on the Sort Ascending button (or choose Data > Sort, click on Ascending, then click OK).

  4. Open the Expense Report sheet tab at the bottom of the spreadsheet to review the expense report and verify your changes.

  5. Save your work.

This illustration displays a column of possible expense types. Do not edit rows 1 and 2. Insert new rows above rows with values, and do not add or edit any rows beneath the last value.

the picture is described in the document text

Changing Column and Cell Headings

You can change the column and cell headings of the Expense Report sheet to use terms that are common within your company. For example, you might want to change "Justification" to "Reason" and change "Cost Center" to "Bill Code". The Prompts sheet lists all of the column and cell headings that you can change.

If you change a column or cell heading, you must also change the corresponding Long Label in the Region Items window of the Web Applications Dictionary.

To change column and cell headings in Microsoft Excel:

  1. Open the Prompts sheet tab at the bottom of the spreadsheet.

  2. Change any of the values in Column B of the Prompts sheet. Do not change any of the values in Column A. Make a list of both the old and new values.

  3. Save your work.

To change Long Label values in the Web Applications Dictionary:

  1. Log in to Oracle Applications using the seeded AK Developer responsibility.

  2. Navigate to the Regions window (Define Regions).

  3. Query the Region ID AP_WEB_DISC_EXP, then choose Region Items.

  4. Change the values in the Long Label fields to match the values in Column B of the Prompts sheet.

  5. Save your work.

Related Topics

Web Applications Dictionary Overview, Oracle Applications System Administrator's Guide - Configuration

Modifying Cell Links

The Cell Links sheet provides default values for these parameters on the spreadsheet template expense report:

The values in each column represent the default values based on the position on the relative sheet. The values for columns A and D represent row number plus 1 from the Currency Codes and Descriptions sheet; the values for column B represent row number plus 1 from the Expense Types sheet.

Use the Cell Links sheet to:

The cell link values for currencies and expense types must point to an existing currency or expense type on the respective sheets. If they do not, then errors can occur when users import spreadsheets into Internet Expenses. For example, if you delete all currencies but the US dollar (USD) in the Currency Codes and Descriptions sheet, then you must update columns A and D of the Cell Links sheet with a USD entry only.

When you make changes to currencies or expense types on the Cell Links sheet, you can use the Expense Report sheet to confirm your changes.

To modify cell links:

  1. If necessary, review the currency code values on the Currency Codes and Descriptions sheet and the expense type values on the Expense Types sheet.

  2. Open the Cell Links sheet tab at the bottom of the spreadsheet.

  3. To update the value of an existing receipt currency, enter the new currency code in column A in the row that you want.

    Enter a number that is one value less than the code represented. For example, working from a sheet that contains all currency codes, if the US dollar code is in row 185, enter 184; for euro (row 57) enter 56; for British pound sterling (row 142) enter 141.

  4. To enter or update the value of an existing expense type, enter the new expense type code in column B in the row that you want. By default, the spreadsheet template does not display expense types.

    Enter a number that is one value less than the code represented. For example, working from a sheet that contains all expense type codes, for Taxi (row 5) enter 4; for Mileage (row 7) enter 6; for Entertainment (row 11) enter 10.

  5. To check the Receipt Missing box for a given row, enter True in Column C in the corresponding row.

  6. To change the default value in the Reimbursement Currency field, enter a new currency code in Column D.

  7. To remove a receipt currency or expense type from a row, you can either delete the value or enter a value of 1.

  8. Open the Expense Report sheet tab at the bottom of the spreadsheet to review the expense report and verify your changes.

  9. Save your work.

Setting Up Descriptive Flexfields in the Microsoft Excel Template

Set up descriptive flexfields to extend the capabilities of the standard Internet Expenses implementation and enable employees to enter additional information about business expenses.

If descriptive flexfields have been defined for use with Internet Expenses, you must set up the same flexfields in the Microsoft Excel template. If you do not define the flexfields in the template, errors will occur when users import their disconnected spreadsheets to Internet Expenses.

Types of Descriptive Flexfields

You can define two types of descriptive flexfields: context–sensitive and global. Context–sensitive flexfields appear in the Internet Expenses Receipt Details page and are associated with expense types.

For example, you can define the flexfields Airline, Class, Origin, and Destination and associate them with the expense type Airfare. Then, when a user chooses an expense type of Airfare when creating an expense report in Internet Expenses, these flexfields dynamically appear in the Receipt Details page.

Global flexfields appear in the Receipt Details page regardless of the expense type selected.

Note: To ensure that all flexfields appear in the correct order in the Microsoft Excel spreadsheet, define your global flexfields first and then define your context–sensitive flexfields.

Defining Flexfields in the Microsoft Excel Template

Define your context–sensitive and global flexfields in the Expense Types sheet of the Microsoft Excel template. Once defined, the flexfields appear in the Additional Information region of the Expense Report sheet.

Note: You can add or remove columns from the Additional Information region to provide space for the flexfields you need.

To set up descriptive flexfields in the Microsoft Excel template, you need specific information from Oracle Applications. Complete these tasks for each flexfield you are using in Internet Expenses:

Determining Flexfield Window Prompt Values

For each flexfield used in Internet Expenses, you must enter a Window Prompt value in the Name column of the Expense Types sheet. This value must exactly match the Window Prompt values defined in the Segments Summary window.

When a user imports an expense report and chooses Continue or Skip to Review to validate the report, Internet Expenses compares the names of the segments in the Name column to the Window Prompt values defined in the Segments Summary window. If these values do not match exactly, the import process will fail.

To determine flexfield window prompt values:

  1. In Payables, navigate to the Descriptive Flexfield Segments window.

  2. Query Oracle Payables as the Application and Expense Report Line as the Title. Notice the expense types that appear in the Code fields of the Context Field Values region.

  3. Select an expense type and choose the Segments button to open the Segments Summary window. You can associate one or more flexfield segments with each expense type. For example, you can associate the flexfield segments Airline, Class, Origin and Destination with the expense type Airfare.

  4. Make a list of the values in the Window Prompt fields and their associated expense types. Repeat this for each expense type that appears in the Descriptive Flexfield Segments window.

  5. Select the Global Data Element line in the Descriptive Flexfield Segments window, and then choose the Segments button. The Segments Summary window opens.

  6. Make a list of the values in the Window Prompt fields. Note that these values are global flexfields.

  7. For each line in the Segments Summary window, write down the value of the Value Set field.

Determining Flexfield Validation Types

A flexfield's Validation Type indicates whether you need to set up the flexfield as a list of values, a choice list, or a text box in the Microsoft Excel template. Validation Types include Independent, Table, and None. If the Validation Type is Independent, the flexfield is validated against a value set defined in the Value Sets window. Set up this flexfield in the Microsoft Excel template as a choice list and define a list of values for it. See also: Defining a List of Values for Descriptive Flexfields.

If the Validation Type is Table, the segment is validated against selected values in a table. If the Value Set Name is AP_SRS_YES_NO_MAND, set up this flexfield in the Microsoft Excel template as a Yes/No choice list.

If the Validation Type is None, the flexfield is not validated against a list of values. Set up this flexfield in the Microsoft Excel template as a text box.

To determine a flexfield validation type:

  1. Navigate to the Payables Segments Summary window.

  2. For each segment, note whether a value appears in the Value Set. If no value appears in the Value Set field, that segment is not validated against a list of values and you must set up this segment in the Microsoft Excel template as a text box. If a value appears in the Value Set field, select that segment and then choose the Value Set button.

  3. In the Value Validation region of the Value Sets window, note the Validation Type.

Defining a List of Values for Descriptive Flexfields

A flexfield's Validation Type indicates whether you need to define a list of values for it. Descriptive flexfields that require a list of values have a Validation Type of either Independent or Table.

Determining Lists of Values for Flexfields with a Validation Type of Independent. If the validation type of a flexfield is Independent, you must set up a choice list in the Microsoft Excel template to show the list of values associated with the flexfield. For example, you might associate the flexfield Airline with the values American, United, KLM, and British Airways.

To determine lists of values for flexfields with a validation type of Independent:

  1. Navigate to the Value Set window in Payables, then query a value set.

  2. Make a list of the values and descriptions that appear in the Segment Values window. Use these values and descriptions to define the list of values for your flexfield in the Microsoft Excel template.

Determining Lists of Values for Flexfields with a Validation Type of Table. If the Validation Type of a flexfield is Table and the Value Set Name is not AP_SRS_YES_NO_MAND, you must make a list of the values associated with that flexfield.

If the Value Set Name of a flexfield is AP_SRS_YES_NO_MAND, you must set up that flexfield as a choice list in the Microsoft Excel template with the values Yes and No.

To determine lists of values for flexfields with a validation type of Table:

  1. Navigate to the Value Sets window, then query a value set with a validation type of Table.

  2. Choose Edit Information.

  3. In the Validation Table Information window, make note of the application Table Name and the WHERE clause that defines the list of values. Use these values to define the list of values for your flexfield in the Microsoft Excel template.

Setting Up Global Flexfields in the Microsoft Excel Spreadsheet

Set up global flexfields by editing the Custom Field columns in the Expense Types sheet of the Microsoft Excel template.

Prerequisites

Complete the steps described in these sections:

To set up global flexfields in the spreadsheet:

  1. Open the Expense Type sheet tab at the bottom of the spreadsheet.

  2. Add the Window Prompt value of the global flexfield in the Name column for every expense type.

    If multiple flexfield segments exist for this global data element, enter a second value in the Custom Field 2 Name column, a third value in the Custom Field 3 Name column, and so on.

    Note: The first value you enter must be in the Name column under the Custom Field 1 heading and subsequent Name column values must form a contiguous sequence. For example, ensure that there are no blank columns between Custom Field 3 and Custom Field 4.

  3. If the Validation Type of this flexfield is Independent or Table, define a choice list for the flexfield. To do this, paste a choice list into the corresponding Special Input cells.

    See also: Setting Up Text Boxes and Choice Lists.

  4. Save your work.

Setting Up Context–Sensitive Flexfields in the Microsoft Excel Template

Set up context-sensitive flexfields by editing the Custom Field columns in the Expense Types sheet of the Microsoft Excel template.

Note: To ensure that all flexfields appear in the correct order in the Microsoft Excel spreadsheet, define your global flexfields first and then define your context-sensitive flexfields.

Prerequisites

Complete the steps described in these sections:

To set up Name columns in the Expense Types sheet:

  1. Open the Expense Type sheet tab at the bottom of the spreadsheet.

  2. Locate a row containing an expense type for which one or more flexfield segments have been defined. In the Name column under the Custom Field 1 heading for that row, enter the value that exactly matches the corresponding Window Prompts value.

    If multiple flexfield segments exist for this global data element, enter a second value in the Custom Field 2 Name column, a third value in the Custom Field 3 Name column, and so on.

    Note: If you have not defined any global flexfields, the first value you enter must be in the Name column under the Custom Field 1 heading and subsequent Name column values must form a contiguous sequence. For example, ensure that there are no blank columns between Custom Field 3 and Custom Field 4.

    If you have defined global flexfields, enter the first value in the next available Custom Field heading. There must be no blank columns between your global and descriptive flexfield columns.

  3. If the Validation Type of this flexfield is Independent or Table, define a choice list for the flexfield. To do this, add the Window Prompt value of the global flexfield in the Name column for every expense type. Then paste a choice list or check box into the corresponding Special Input cells. See also: Setting Up Text Boxes and Choice Lists.

  4. Save your work.

This illustration depicts the Custom Field columns and Name sub-columns in the spreadsheet.

the picture is described in the document text

Setting Up Text Boxes and Choice Lists

The values in the Name columns in the Expense Types sheet represent flexfields. Use the Special Input column to set up choice lists. Depending on the flexfield's validation type, it must be defined as either a text box or a choice list. To specify how the flexfield appears in the spreadsheet, you must paste a choice list into the Special Input column, or leave the column empty. See also: Determining Flexfield Validation Types.

There are three sample choice lists located below the sample expense types in the Expense Types sheet. The three sample choice lists have input ranges that point to sample values which are located to the right of the Custom Field 15 column (use the scroll bar to view this field and the sample values).

To use a sample choice list, copy and paste the cell into a Special Input column, add or edit the sample values as necessary, and modify the input range.

Note: When pasting a choice list, ensure that the top left corner of the object falls within the targeted cell.

Prerequisites

You can set up these types of columns for the Special Input columns in the Expense Types sheet:

This illustration demonstrates the steps described above for setting up a choice list.

the picture is described in the document text

After you save your changes, you can verify them by performing these steps:

  1. Open the Expense Report sheet tab at the bottom of the spreadsheet.

  2. Select expense types to which you have associated flexfields.

  3. Scroll to the Additional Information region and verify that the flexfields you defined appear.

  4. Ensure all choice lists display the proper values.

Other Setup Considerations

Review the considerations described below when configuring your spreadsheet template. This will help ensure that your template is set up correctly.

Modifying the Import Status Message

Since all of the spreadsheet information cannot be shown in the Spreadsheet Import Area field, a user may be unsure whether the expense report was successfully imported. Therefore, a status message appears in the field to alert users that the import process completed. This “Import Successful” message ensures that users do not re-paste the data from the spreadsheet and create duplicate expense reports.

You can edit this message to fit your company's requirements. To edit this message, make the sheet tabs available in the spreadsheet and click the Prompts tab. In the Prompts tab, change the Import Successful prompt as necessary.

Note: Ensure that you have not removed this prompt from the Expense Report sheet of the Microsoft Excel template during the configuration process. The prompt is not visible, but you can find it in the Line column. Specifically, it is in the first cell below the gray line at the bottom of the sheet.

Additional Information Column Heading

The Additional Information heading is the last column on the spreadsheet template. This column must remain the last column to ensure that the flexfields and flexfield values upload correctly.

Protecting the Custom Microsoft Excel Template

If users modify the information in your Microsoft Excel template, it can cause errors when Internet Expenses validates the spreadsheet. To prevent users from making changes, hide the sheet tabs and protect the template.

To protect the Custom Microsoft Excel Template

  1. Open the Expense Report sheet tab at the bottom of the spreadsheet.

  2. Position the sheet at the top left corner cell.

  3. Hide the sheet tabs. To do this:

    1. Choose Tools > Option.

    2. Choose the View tab, then deselect the Sheet Tabs check box.

  4. Protect the template by choosing Tools > Protection > Protect Sheet.

  5. Save your work.

Testing your Custom Microsoft Excel Template

Before releasing your custom template for general use, import and validate the spreadsheet in Internet Expenses. Perform these steps to discover and resolve any remaining setup problems that may exist in your template.

To test the Custom Microsoft Excel Template:

  1. Compare the custom Microsoft Excel template and expense types to the Corporate Credit Card and Cash and Other Expenses pages. Ensure all fields in the template appear in these pages, and all the fields on these pages appear in the template. (Be sure that you also look at the Expense Lines page.)

  2. Create an expense report with the custom Microsoft Excel template. Assign each expense type to at least one receipt. If applicable, enter values for each flexfield.

  3. Upload the expense report. Make sure the message you entered at the bottom of the spreadsheet template appear in the Spreadsheet Upload Area.

  4. Click Next to validate the spreadsheet.

You must fix errors that exist in the spreadsheet template itself before releasing it to employees for general use. Errors that occur because the spreadsheet was incorrectly defined can be fixed only by the system administrator. Errors that occur due to missing or invalid data in the spreadsheet do not indicate a serious problem and the report can still be imported into Internet Expenses. When expense report information is missing or invalid, you can perform either of these actions: