Web Employees Expense Report Spreadsheet
If your employees use Oracle Web Employees to enter expense reports, your employees can track receipt information in a spreadsheet, and then upload the spreadsheet into Web Employees to submit their expense reports.
This discussion describes how to set up your system to use the expense report spreadsheet. For information on downloading and uploading the spreadsheet, refer to the Web Employees online help.
Setting Up the Expense Report Spreadsheet
A predefined Microsoft Excel (.xls) file is provided with Web Employees. The spreadsheet uses Visual Basic objects to create poplists for the Expense Type and Currency fields and a check box for the Receipt Missing field. Visual Basic objects are not supported in versions of Excel prior to version 5.0. To use the predefined expense spreadsheet with Web Employees, you must use Excel 5.0 or higher.
The predefined .xls file (apwexpmc.xls) is located in the /OA_HTML/<language>/ virtual path on your Web Server.
Attention: If you configure the predefined expense spreadsheet, you may want to make a copy of this file and store it in directory protected from upgrade.
Prerequisites
If you want to use disconnected expense reporting with more than one expense report template, you must set up one spreadsheet for each template that is enabled for self-service users. See: Expense Report Templates
Suggestion: We recommend, however, that you use only one template for all Web Employees users.
In the Currencies window, define and enable currencies you want to use. See: Currencies Window.
Install Microsoft Excel 5.0 or higher.
To set up the expense reporting worksheet:
1. Open the spreadsheet file and display all worksheets tabs. Display all worksheet tabs by choosing Tools - Options from the menu. Choose the View tab and check the Sheet Tabs check box.
The predefined expense spreadsheet includes four worksheets:
- Currency Codes and Descriptions
There are no fields to change in the cell links worksheet.
Unprotect the Expense Report worksheet by choosing Tools - Protection - Unprotect Sheet.
2. Enter the name of the expense template. In cell D4, enter the name of the expense template that is associated with this spreadsheet.
Attention: Enter the name of the template exactly as it appears in the Expense Report Templates window in Payables.
3. Format all Date From and Date To cells with the date format used with Oracle Applications. For example, if your date format with Oracle Applications is DD-MON-YY, set the date format for these cells within the spreadsheet to DD-MON-YY.
Once you are complete with this step, protect the Expense Report worksheet by choosing Tools - Protection - Protect Sheet.
4. Populate the currency code poplist. The currency code poplist in the Expense Report worksheet is generated based on values in Column A and Column B in the Currency Codes and Descriptions worksheet. Click on the Currency Codes and Descriptions worksheet tab.
Currency codes and descriptions are predefined with the multiple currency expense spreadsheet. You can add or delete from this list of predefined currencies.
Attention: The currency codes defined in Column B must be entered exactly as they appear in the Currencies window. The values in Column A will appear in the poplist, and may be safely altered. The corresponding rows in Column B contain the values that will be uploaded and validated.
To add a currency, unprotect the Currency Codes and Descriptions worksheet. Insert a row directly in the middle of the list of currency codes and enter the currency code you want to add to the list. Enter in Column A the values that you want displayed in the poplist. Enter in Column B the currency code from the Currencies window. The value entered in Column B is the value that is entered for the receipt currency when an employee uploads receipt information into Web Employees.
Attention: To add a currency, do not add a row to the bottom of the list of defined currencies. To avoid losing synchronization between the current cells and the poplists, all changes must be made between the first and last row of currency values.
To delete a currency, delete the row from the list of defined currencies.
Attention: Do not type in Rows 1 and 2 of the Currency Codes and Descriptions worksheet.
Suggestion: You may want to sort the currency codes and descriptions alphabetically so that they appear in a logical order in the poplist. Be sure to select and sort only the cells in Columns A and B from Row 3 through the last valid currency code.
5. Once you populate the currency code poplist, you may want to protect the Currency Codes and Descriptions worksheet.
6. Populate the expense type poplist. The expense type poplist in the Expense Report worksheet is generated based on values in Column A in the Expense Types worksheet. Click on the Expense Types worksheet tab.
Predefined expense types are included with both predefined expense spreadsheets. You can add or delete from this list of predefined expense types. For each expense type you want to use, make sure that in the Expense Report Templates window it is either defined for the template associated with this expense spreadsheet, or it is viewable from all templates.
Attention: The expense items defined in Column A must be entered exactly as they appear in the Expense Report Templates window. Enter the Friendly Prompt if you defined one, otherwise enter the Expense Item name.
To add an expense type, unprotect the Expense Type worksheet. Insert a row directly in the middle of the list of expense types and enter the expense type you want to add to the list. Enter in Column A the expense type that you want displayed in the poplist.
Attention: To add an expense type, do not add a row to the bottom of the list of defined expense types. To avoid losing synchronization between the current cells and the poplists, all changes must be made between the first and last row of expense type values.
To delete an expense type, delete the row from the list of defined expense types using Edit - Delete row from the menu.
Attention: Do not type in Rows 1 and 2 of the Expense Types worksheet.
Suggestion: You may want to sort the expense types alphabetically so that they appear in a logical order in the poplist.
7. Once you populate the expense types poplist, you may want to protect the Expense Types worksheet.
8. Select and view the expense report worksheet.
- Be sure that all the poplists, check boxes, and fields contain the default values you want to see.
- Position the worksheet at the top left corner cell.
- Hide all worksheet tabs. Choose Tools - Options from the menu; choose the View tab, and uncheck the Sheet Tabs check box.
- Click in the first cell a person would logically start with, usually the Purpose.
9. If you change the text of any prompts on the spreadsheet, you must query AP_WEB_DISC_EXP in the Region Items window in Oracle Web Applications Dictionary and change the corresponding Long Label. If you do not do this, the system will no longer recognize the spreadsheet prompt and will ignore any value you enter for it. For more information, see: Associating Atrributes with Regions (Oracle Web Applications Dictionary User's Guide).
10. Save your version of the expense report template.
See Also
Oracle Web Employees Expense Reporting
Expense Report Templates
Currencies Window.
