Excel Templates – User Manual

Simplified Loader Toolbar

Each Template contains a Simplified Loader Toolbar.

The Toolbar is divided into four sections Log In, Operations, Lookups, and Setup. All but the Operations section are identical in each Template.

Log In enables a Simplified Loader user to log in or log out from the Oracle Fusion.

Operations contain actions that are specific to that Template only. Detailed descriptions of these operations can be accessed from each Template.

Lookups section of the Toolbar is used to update the Lookup values contained in the Lookups worksheet.

Setup contains the operations that enable users to connect to Oracle Fusion environments, choose validation options and conduct admin tasks.

Worksheets

All Simplified Loader Templates contain five worksheets: About, DataSheet, Lookups, Sample – DataSheet and Sample – Lookups.

This section describes the general purpose and features of these sheets.

About Worksheet

The About worksheet contains the master list of data fields available to use in the DataSheet. All data fields have an associated field description.
These data fields are derived from the relevant Oracle web service. For example, all 450 data fields provided by the Oracle Procurement Requisition Web Service are contained within the Simplified Loader Purchase Requisition template. Each data field in the About sheet is uniquely named.
The About worksheet sets out the data fields utilized by all the operations that can be carried out within that Template. Only some of these data fields, however, are utilized by each of the operations. Where a field is utilized by an operation, this is indicated by the entry “Relevant Field”.

DataSheet

The DataSheet is the area in the Simplified Loader Template where the user completes operations and data is exchanged with Oracle Fusion. It is, therefore, the principal working area for the user.
The titles of some of the columns in the Data Sheet are highlighted in green. This indicates that the data entered in these columns are validated against an associated List of Values (LOV) found within the Lookups Sheet.

Each operation in the Operations section of the Simplified Loader Toolbar utilizes a specific number of data fields. These data fields are identified in the About worksheet, in the column representing the specific operation, by the term “Relevant Field”.

Lookups Worksheet

The Lookups worksheet contains associated values that can be used when entering data into some of the fields in the DataSheet. These data fields are identified in the Datasheet by the green column titles. These help to reduce input times and improve data accuracy.

Data in the Lookups sheet is organized into different tables. These tables are referred to when the user chooses to utilize lookup functionality from DataSheet.

Sample - DataSheet

Users can find examples within the Sample – DataSheet worksheet of how data should be entered for each operation (´Data Entry´) found within the Operations section of the Simplified Loader Toolbar. There are also examples of how the data is returned as a result of executing each operation (Data Result).

The Sample – DataSheet worksheet can be deleted as soon as the user no longer needs it.

Sample - Lookups

The Sample – Lookups worksheet contains sample lookup values to help familiarize users with the different data tables used in the Lookups worksheet.

The Sample – Lookups worksheet can be deleted as soon as the user no longer needs it.

Log In / Log Out

A user needs to log in whenever they perform an operation that requires data exchange with Oracle Fusion. The user can choose to sign in using the Log In section of the Simplified Loader Toolbar. Alternatively, the Log In window will appear whenever the user performs an operation requiring a connection to Oracle Fusion.

1. Select the environment to log in to and enter the User Name.

2. The Log In window remembers the last used Environment and User Name.

3. The next step is to choose to log in using SSO (Single Sign-On) or Non SSO (Oracle Fusion stand-alone login).

SSO Login

SSO login procedure is followed when the user is presented with his or her company’s specific Single Sign On (SSO) method to log in.

1. To log in using SSO, the user needs to clicks the SSO Login button leaving the Password fields blank.

2. Excel will launch a browser window which will take you to the Log In window used in your organization. The following is a sample screenshot of Microsoft SSO login screen. This screenshot may differ from what you see which will be based on your company’s SSO provider.

3. Complete your company´s SSO verification steps.

4. After completing the SSO validation, the browser window will close automatically.

5. The user is now logged in.

6. The user can check whether they are logged in by clicking on the Log In option in the Toolbar. If they are logged in, then the following window will appear to confirm.

Non SSO Login

The Non-SSO login procedure is followed when the user normally uses the User ID and Password fields displayed in the following standard Oracle Fusion login screen.

If the user normally logs in to Oracle Fusion using the standard Oracle Fusion login page, then they can utilize Simplified Loader’s Log In window to login directly from a Simplified Loader template.

1. Enter User Name and Non-SSO Password

2. Click the Non SSO Log In button to validate the user’s login credentials.

3. After successful validation, the Log In window will close, and the user will now be ready to upload and download data from Oracle Fusion.

4. The user can check at any point whether they are still logged in by clicking on the Log In option in the Toolbar. If they are logged in, then this will be confirmed by the appearance of the following message.

Log Out

To end a user session, click on the Logout button.

NOTE: Session automatically ends when the user closes the Template.

Handling data

Performing operations on data

Using the operations buttons available in the Operation section of Simplified Loader toolbar, data is either downloaded to or uploaded from the DataSheet. The user can perform the action on all or selected records entered in the DataSheet.

1. To selectively upload data records, highlight the entire row of the relevant records by clicking the row number in the worksheet.

2. Simplified Loader will now only process the data in the selected records.

3. When no rows are highlighted, the user receives a pop-up message to confirm if they want to act on all rows in the sheet.

4. Clicking Yes sends all the transaction data in the sheet to Oracle.

Examples:

a. Uploading selected transactions

  • Enter data in DataSheet
  • Select the required row or rows within the DataSheet and click on an operation in the Operations section of the Simplified Loader Toolbar. NOTE: Only data entered in the fields marked as “Relevant Fields” in the About worksheet will be sent to Oracle. Please see the About Sheet section of this User Guide for more information.
  • The user receives a response from Oracle Fusion. The response may contain data values in additional fields. If this is the case, then the response will override any existing values in data fields.

b. Uploading all records entered in DataSheet

  • Enter data in the DataSheet
  • Do not select any Excel row and click on the desired operation.

NOTE: Only data entered in the fields marked as “Relevant Fields” in the About worksheet will be sent to Oracle. Please see the About Sheet section of this User Guide for more information.

  • Users receive a pop-up message.
  • The user receives a response for all the records present in the DataSheet. The response may contain data values in additional fields. If this is the case, then the response will override any existing values already entered into that filed by the user.

c. Get transaction details

  • Enter data in DataSheet
  • The user may opt to select a few records or process all records in the DataSheet.

NOTE: Only data entered in the fields marked as “Relevant Fields” in the About worksheet will be sent to Oracle. Please see the About Sheet section of this User Guide for more information.

  • The user receives a response from Oracle Fusion. The response may create one or multiple records.
  • In the event the response contains multiple records, then Simplified Loader will insert a new line and populate next level of details in the row or rows below.

Status of Operation

The DataSheet contains Load Status and Error Message columns that indicate whether an operation has been successful or not and, in the case of a failure, an error message stating the reason for a failed transaction.

Default Values

Default values can be used to save time by reducing the amount of manual data entry required whenever the Simplified Templates are used to create or update multiple data records within Oracle Fusion. For example, if a user is uploading several Requisitions, then the common values can be set as defaults in the Simplified Loader Template. The user can assign default values to any field available in Simplified Loader template by entering the values row 2 of the DataSheet.

Note: To avoid unintended changes to Default values, they can be locked using standard Excel functionality.

Data Field description

Each data field in the DataSheet has an associated description. To view the data field description, click on the title cell in row 3.

Upload Data Format

Data to be uploaded to Oracle from Simplified Loader templates needs to be entered into the DataSheet. Using Simplified Loader templates users can upload single or multiple records at the same time. Where records contain ´parent-child´ data relationships, then the user should enter it in the format shown in the example below. Examples of ´parent-child´ data relationships include:

  • Requisition Header may contain one or many Lines.
  • Each Requisition Line may contain one or many Distribution

In the above image, you can see the following example:

  1. Requisition 01: One Parent to one Child (Requisition Line) to one grand-child (Distribution)
  2. Requisition 02: One Parent to two Children (Requisition Lines) to two Grandchildren each (Distribution)
  3. Requisition 03: One Parent to three Children (Requisition Lines) to multiple Grandchildren (Distribution)

There is no limit on the number of children to include within a parent.

For Default values to apply on the transaction, there must be at least one Relevant Field populated for the given section.

Rearrange or Delete columns

The DataSheet contains a column for each of the data fields relevant to the specific Template. For example, the Simplified Loader Requisition template lists all 450 data fields found within the Oracle Procurement Requisition WebService.

Users, however, can delete and rearrange (i.e. move) any columns that they do not wish to use. A copy of the Template with repositioned or deleted columns can then be saved.

Add custom columns

The users can add additional columns to either analyze data using formulas or to store values for information purposes. New columns however must not share a name with any of the existing columns in the About worksheet.

Using the formula, pivot tables, filter, or any other excel functionality

The user can use all functionality available in Excel.

Lookups

Get Lookup Values

Simplified Loader Templates utilize values stored locally in the Lookup worksheet to validate data entered in the DataSheet. These Lookup values are populated from Oracle Fusion when the Template is first set up. The Get Lookup Values option in the Simplified Loader toolbar can be used to refresh the values whenever required.

  • On clicking the Get Lookup Values option, the following message appears:
  • Click Yes to refresh the Lookups. This will delete the existing values and replace them with the full set of Lookup values downloaded from Oracle Fusion.
  • Click No to keep the existing values stored in the Lookup worksheet but to add any new or additional values added to Oracle Fusion.
  • To cancel the operation, click Cancel

Using Lookups in the DataSheet

The Lookups worksheet contains values that can be used when entering data into some of the fields in the DataSheet. They consequently help to reduce input times and improve data accuracy. In the DataSheet, the titles of the columns where lookups apply are displayed with a green background.

To utilize Lookup values when entering data in the DataSheet, the user needs to:

  • Double-click on the cell where a Lookup value is to be entered
  • The Lookup worksheet will open a ‘Filter Lookup Values’ form to search the relevant values
  • Enter the search criteria in the Filter Lookup values form and press “Filter (Ent )”. This will filter records in the Lookup table
  • Once satisfied with the search results, close the dialogue box by clicking “Close (Esc)”
  • To make a selection, double-click on the value to enter in the DataSheet.
  • The Lookup value will then be copied to the DataSheet.
  • Lookup functionality also populates any related field values. For example, selecting Requisition Business Unit will also fill the value in Requisition Business Unit ID field.

User Setup

Template Details

The Template Details section of the User Setup includes Template Version, Active Dates and Allowed Users.

Whenever the user is asked to provide the Template Details (e.g. by the Simplified Loader Support team), the version details can be displayed by clicking on User Setup in the Setup section of the Simplified User Toolbar to display the window below.

Connection Setup

Please click here to know more about Connection Setup.

Real Time Validation

Users have the option to validate data as it is entered in the Simplified Loader Templates.

This option can be selected by clicking on the User Setup in the Setup section of the Simplified Loader toolbar and tick the ´Real Time Validation´ checkbox.

´Real Time Validation´ works by checking data entered in the DataSheet against values in the Lookups sheet. Consequently, it can be used off-line where necessary. Whenever an incorrect value is entered, the cell is highlighted, and the font colour of the entry changes to Red.

Validate Before Upload

A second validation option available to the user is to ‘Validate Before Upload’.

After enabling this option, the following pop-up message appears whenever the user carries out an operation requiring data validation. In the pop-up message, the user is asked to choose to either ´Upload´ the data where it will be validated in Oracle or instead to first ´Validate Only´ the data using values stored in the Lookups sheet.

The Validate Only option can be utilized off-line and includes a check of any interdependencies between data values, e.g. selecting the Line Items based on Purchasing Business Unit.