Macro Settings & store 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 a Excel file and click the File option in the toolbar
2. Click Options
3. Click Trust Center > Trust Center Settings
4. Select option "Disable VBA macro except digitally signed macros"
Store Simplified Loader digital certificate
1. Open Simplified Loader Excel template. This will display the security warning in the yellow ribbon under the standard toolbar. Click "Macro have been disabled."
2. Select Info > Enable Content > Advanced Options
3. Review the signature. It should be signed by "Simplified Loader Ltd". Click the option "Trust all documents from this publisher".
Review stored Simplified Loader digital certificate
1. Open any Excel file and click the File option in the toolbar
2. Click Options
3. Click Trust Center > Trust Center Settings
4. Click Trusted Publishers to view all trusted publishers
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.
All Simplified Loader Templates contain three worksheets: About, DataSheet, and Sample – DataSheet.
This section describes the general purpose and features of these sheets.
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 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 used by each of the operations. Where an operation utilizes a field, this is indicated by the “Relevant Field” value.
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 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 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.
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 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 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, 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.
Suppose 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
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.
To end a user session, click on the Logout button.
NOTE: Non-SSO session automatically ends when the user closes the Template.
Performing operation on data
Using the operations buttons available in the Operation section of the Simplified Loader toolbar, data is either downloaded to 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.
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.
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 is sent to Oracle Fusion. 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 the next level of details in the row or rows below.
Field Data Types
DataSheet fields accept data input in one of the following data types.
- Date Time
- Y / N
The user can find the data type associated with the column by clicking the column header button. This displays a pop-up message containing data type within square bracket followed by the field description.
When entering a value for Date and Date Time data type, the user has the freedom to enter date value in the format they prefer to use in Excel. Simplified Loader template adapts to the format entered by the user.
There is no language restriction to enter values in DataSheet. Simplified Loader template supports the Unicode character set, which means that 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.
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, suppose a user is uploading several Requisitions. In that case, the common values can be set as defaults in the Simplified Loader Template. The user can assign default values to any field available in the 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:
- Requisition 01: One Parent to one Child (Requisition Line) to one grand-child (Distribution)
- Requisition 02: One Parent to two Children (Requisition Lines) to two Grandchildren each (Distribution)
- 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 formulas, pivot, filter, or any other excel functionality
The user can utilize all functionality available in Excel.
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 is used to refresh the lookup values whenever required. The lookup values are pulled from Oracle Fusion.
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:
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 ‘Filter Lookup Values’ form opens with the ability 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.
- To select a value, either double-click on the relevant record or select the radio button in front of the lookup value and click the button Return Selected Record.
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 the Requisition Business Unit ID field.
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.
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 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 offline 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 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 offline and includes a check of any interdependencies between data values, e.g. selecting the Line Items based on Purchasing Business Unit.