Excel Templates - User Manual

Macro settings & Simplified Loader digital certificate

Simplified Loader Excel templates are digitally signed. Simplified Loader recommends setting up the Excel Trust Centre's Macro Settings to "Disable VBA macro except digitally signed macros" and storing the Simplified Loader digital certificate on your computer. Storing digital certificates on the computer is a one-time activity.

Update the Macro Settings to "Disable VBA macro except digitally signed macros"

1. Open an Excel file and click the File option in the toolbar

Digitally Signed Macro - Simplified Loader Excel for Oracle Fusion Cloud ERP

2. Click Options

Digitally Signed Macro - Simplified Loader Excel for Oracle Fusion Cloud ERP

3. Click Trust Center > Trust Center Settings

Digitally Signed Macro - Simplified Loader Excel for Oracle Fusion Cloud ERP

4. Select the option "Disable VBA macro except digitally signed macros"

Digitally Signed Macro - Simplified Loader Excel for Oracle Fusion Cloud ERP

Store Simplified Loader digital certificate

1. Log an incident on the Simplified Loader Support Portal to obtain the digital certificate.

2. The Support team will share the digital certificate through the incident. Download the certificate.

Digitally Signed Macro - Simplified Loader Excel for Oracle Fusion Cloud ERP

3. Unzip the file to get the "Certificate995143510repl_Standard_PEM.cer.crt" file.

4. To install the certificate, right-click the "Certificate995143510repl_Standard_PEM.cer.crt" file and click the "Install Certificate" option.

NOTE: The steps documented in this guide demonstrate certificate installation on a local PC as an administrator. Simplified Loader highly recommends distributing the digital certificate using Global Policies. We cannot guide customers on a specific Global Policy distribution structure, as every organization has a particular structure for distributing Global Policies to the user's machine.

Digitally Signed Macro - Simplified Loader Excel for Oracle Fusion Cloud ERP

5. Select options as demonstrated in the screenshots below.

Digitally Signed Macro - Simplified Loader Excel for Oracle Fusion Cloud ERP Digitally Signed Macro - Simplified Loader Excel for Oracle Fusion Cloud ERP Digitally Signed Macro - Simplified Loader Excel for Oracle Fusion Cloud ERP Digitally Signed Macro - Simplified Loader Excel for Oracle Fusion Cloud ERP Digitally Signed Macro - Simplified Loader Excel for Oracle Fusion Cloud ERP

6. This concludes the certificate installation on the user's PC. When performing the step using Global Policies, please ensure to update the policies (using the gpupdate command) on the user's machine.

Review stored Simplified Loader digital certificate

1. Navigate to Windows's Control Panel and search "Manage Certificate".

2. Under "Windows Tools", select "Manage computer certificates".

Digitally Signed Macro - Simplified Loader Excel for Oracle Fusion Cloud ERP

3. The Simplified Loader certificate will be displayed under the Trusted Publisher > Certificates.

Digitally Signed Macro - Simplified Loader Excel for Oracle Fusion Cloud ERP

4. Two certificates from Sectigo will be displayed under the Intermediate Certification Authorities > Certificates.

Digitally Signed Macro - Simplified Loader Excel for Oracle Fusion Cloud ERP

5. The "AAA Certificate Services" will be displayed under the Trusted Root Certification Authorities > Certificates.

Digitally Signed Macro - Simplified Loader Excel for Oracle Fusion Cloud ERP

6. Open any Excel file and click the File option in the toolbar

Digitally Signed Macro - Simplified Loader Excel for Oracle Fusion Cloud ERP

7. Click Options

Digitally Signed Macro - Simplified Loader Excel for Oracle Fusion Cloud ERP

8. Click Trust Center > Trust Center Settings

Digitally Signed Macro - Simplified Loader Excel for Oracle Fusion Cloud ERP

9. Click Trusted Publishers to view all trusted publishers

Digitally Signed Macro Certificate - Simplified Loader Excel for Oracle Fusion Cloud ERP

Simplified Loader Toolbar

Every Template contains a Simplified Loader Toolbar.

Toolbar - Simplified Loader Excel for Oracle Fusion Cloud ERP

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

Toolbar Sections - Simplified Loader Excel for Oracle Fusion Cloud ERP

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

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

Lookups section of the Toolbar is used to refresh the Lookup values in the template.

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

Worksheets

All Simplified Loader Templates contain three worksheets: About, DataSheet, and Sample - DataSheet.

WorkSheet - Simplified Loader Excel for Oracle Fusion Cloud ERP

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 270 data fields provided by the Oracle Payable Invoice Web Service are within the Simplified Loader Payables Invoice 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. Not all data fields are used by each operation. Where an operation utilizes a field, this is indicated by the "Relevant Field" value.

About Sheet - Simplified Loader Excel for Oracle Fusion Cloud ERP

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.

DataSheet - Simplified Loader Excel for Oracle Fusion Cloud ERP

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

Sample – DataSheet

Users can find examples in the Sample – DataSheet worksheet of how data should be entered for each operation ('Input values') in the Operations section of the Simplified Loader Toolbar. There are also examples of how the data is returned as a result of executing an operation (Result).

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

Sample DataSheet - Simplified Loader Excel for Oracle Fusion Cloud ERP

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.

Fusion Login - Simplified Loader Excel for Oracle Fusion Cloud ERP

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

Oracle Fusion Environment - Simplified Loader Excel for Oracle Fusion Cloud ERP

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

Login User - Simplified Loader Excel for Oracle Fusion Cloud ERP

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

Login - Simplified Loader Excel for Oracle Fusion Cloud ERP

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 click the SSO Log In button, leaving the Password fields blank.

SSO Login - Simplified Loader Excel for Oracle Fusion Cloud ERP

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

SSO Login - Simplified Loader Excel for Oracle Fusion Cloud ERP

3. Complete your company's SSO verification steps.

4. After 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 the Log In option in the Toolbar. If they are logged in, the following window will appear to confirm.

Login Confirmation - Simplified Loader Excel for Oracle Fusion Cloud ERP

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.

Login Password - Simplified Loader Excel for Oracle Fusion Cloud ERP

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

1. Enter User Name and Non-SSO Password

Non SSO Login - Simplified Loader Excel for Oracle Fusion Cloud ERP

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

Login Confirmation - Simplified Loader Excel for Oracle Fusion Cloud ERP

Log Out

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

Logout - Simplified Loader Excel for Oracle Fusion Cloud ERP

NOTE: The session automatically ends when the user closes the Excel template file.

Handling data

Performing operation on data

Using the operations buttons in the Operation section of the Simplified Loader toolbar, data is downloaded or uploaded from the DataSheet. The user can act 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.

DataSheet - Simplified Loader Excel for Oracle Fusion Cloud ERP

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.

Operations - Simplified Loader Excel for Oracle Fusion Cloud ERP

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

Examples:

a. Uploading selected transactions

  1. Enter data in DataSheet
  2. 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.
    DataSheet - Simplified Loader Excel for Oracle Fusion Cloud ERP
  3. The user receives a response from Oracle Fusion. The response may contain data values in additional fields. If this is the case, the response overrides any existing values in the data fields.
    DataSheet - Simplified Loader Excel for Oracle Fusion Cloud ERP

b. Uploading all records entered in DataSheet

  1. Enter data in the DataSheet
  2. Do not select any Excel row and click on the desired operation.
    NOTE: Only data entered in the 'Relevant Fields' fields in the About worksheet is sent to Oracle Fusion. Please see the About Sheet section of this User Guide for more information.
    DataSheet - Simplified Loader Excel for Oracle Fusion Cloud ERP
  3. Users receive a pop-up message.
    DataSheet - Simplified Loader Excel for Oracle Fusion Cloud ERP
  4. 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 field by the user.
    DataSheet - Simplified Loader Excel for Oracle Fusion Cloud ERP

c. Get transaction details

  1. Enter data in DataSheet
  2. The user may select a few records or process all records in the DataSheet.
    NOTE: Only data entered in the 'Relevant Fields' fields in the About worksheet will be sent to Oracle. Please see the About Sheet section of this User Guide for more information.
    Download Data - Simplified Loader Excel for Oracle Fusion Cloud ERP
  3. The user receives a response from Oracle Fusion. The response may create one or multiple records.
  4. If the response contains multiple records, then Simplified Loader will insert a new line and populate the next level of details in the row or rows below.
    Download Data - Simplified Loader Excel for Oracle Fusion Cloud ERP

Field Data Types

DataSheet fields accept data input in one of the following data types.

  1. Character
  2. Number
  3. Date
  4. Date Time
  5. Y / N

The user can find the data type associated with the column by clicking the column header field in row 3 of the DataSheet. This displays a pop-up message containing data type within a square bracket followed by the field description.

When entering a value for Date and Date Time data type, the user can enter the date value in the format they prefer to use in Excel. Simplified Loader template adapts to the format entered by the user.

Language Support

There is no language restriction to enter values in DataSheet. Simplified Loader template supports the Unicode character set, meaning a user can enter data in any language.

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.

Load Status - Simplified Loader Excel for Oracle Fusion Cloud ERP

Default Values

Default values 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, suppose a user is uploading several Invoices. In such cases, the common values can be set as default values in the Simplified Loader template. The user can assign default values to any field in the Simplified Loader template. Enter the default values in row 2 of the DataSheet.

Default Value - Simplified Loader Excel for Oracle Fusion Cloud ERP

NOTE: For Default values to apply to the transaction, there must be at least one relevant field value populated for the given section in row 4 onwards.

Data Field description

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

Column Description - Simplified Loader Excel for Oracle Fusion Cloud ERP

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, the user should enter them in the format shown in the example below. Examples of parent-child data relationships include:

  1. Invoice Header may contain one or many Lines.
  2. Each Invoice Line may contain one or many Distribution.
Data Input Format - Simplified Loader Excel for Oracle Fusion Cloud ERP

In the above example:

  1. Invoice 01: Contains one parent (header) record, one child (Invoice Line), and one grand-child (Invoice Distribution).
  2. Invoice 02: Contains one parent (header) record, two childs (Invoice Lines), and one grand-child (Invoice Distribution) each for each Invoice Line.
  3. Invoice 03: Contains one parent (header) record, four childs (Invoice Lines), and multiple grand-childs (Invoice Distribution) each for each Invoice Line.

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

Rearrange or Delete columns

The DataSheet contains a column for each data field relevant to the specific Template. For example, the Simplified Loader Payables Invoice template lists all 270 data fields within the Oracle Payable Invoice WebService.

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

Add custom columns

The users can add columns to analyze data using formulas or store values for information purposes. New columns must not use the reserved field names listed in the About worksheet.

Lookups

Get Lookup Values

Simplified Loader Templates utilize lookup values stored in the Template to enter data in the DataSheet using the list of values. The Get Lookup Values option in the Simplified Loader toolbar refreshes the lookup values when required. The lookup values are pulled from Oracle Fusion.

Lookup Toolbar - Simplified Loader Excel for Oracle Fusion Cloud ERP

On clicking the Get Lookup Values option, the existing values stored in the Template are replaced with the full set of Lookup values downloaded from Oracle Fusion. The following message is displayed as confirmation of the Get Lookup Value operation:

Get Value - Simplified Loader Excel for Oracle Fusion Cloud ERP

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.

DataSheet Lookup - Simplified Loader Excel for Oracle Fusion Cloud ERP

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

  1. Double-click on the cell where a Lookup value is to be entered
  2. The 'Filter Lookup Values' form opens with the ability to search the relevant values
  3. Enter the search criteria in the Filter Lookup Values form and press 'Filter (Ent)'. This will filter records in the Lookup table.
    Lookup Values - Simplified Loader Excel for Oracle Fusion Cloud ERP
  4. To select a value, double-click on the relevant record or select the radio button in front of the lookup value and click Return Selected Record.
  5. The Lookup value will then be copied to the DataSheet.
    Lookup Value Selected - Simplified Loader Excel for Oracle Fusion Cloud ERP
  6. Lookup functionality also populates any related field values. For example, selecting Header Supplier will also fill the values in the 'Header Supplier Number' and 'Header Supplier Site' fields.

User Preferences

Template Details

The Template Details section of the User Preferences 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 Preferences in the Setup section of the Simplified User Toolbar display the window below.

User Preferences - Simplified Loader Excel for Oracle Fusion Cloud ERP

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 Preferences in the Setup section of the Simplified Loader toolbar and ticking the 'Real-time validation' checkbox.

Real Time Validation - Simplified Loader Excel for Oracle Fusion Cloud ERP

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

Real Time Validation - Simplified Loader Excel for Oracle Fusion Cloud ERP

Validate before upload

Use this option to validate data before uploading to Oracle Fusion.

Validate before upload - Simplified Loader Excel for Oracle Fusion Cloud ERP

After enabling this option, the following pop-up message appears whenever the user performs an operation requiring data validation. In the pop-up message, the user can select the 'Upload' option to send data to Oracle Fusion. The 'Upload' option is the default behaviour. When the user selects the 'Validate Only' option, the selected data is validated using the Lookup values stored locally in the Template.

Validate before upload - Simplified Loader Excel for Oracle Fusion Cloud ERP

The Validate Only option can be utilized offline. The checks involving interdependencies between data values, e.g. selecting the Line Items based on the Purchasing Business Unit, are also performed by the Validate Only check.

Validate before upload - Simplified Loader Excel for Oracle Fusion Cloud ERP