Table of Contents

Overview

Unlock the Admin Panel

Add Environment

Set Up BI Reports

User Registration

Setup DFF

Disables Data Load

Setup mandatory fields or default values

Application control preferences

  1. Override application control preferences at the user level
  2. Version Control
  3. Allowed Operations
  4. Integration Mode

Amending Lookups

  1. Pull data from Oracle Fusion
  2. Associate lookup values

Customize data selection criteria

Custom Validations & Data Enrichment

Overview

The Admin setup lets you control configuration that applies to all the users in your organization.

Unlock the Admin Panel

The Admin Panel contains a number of the operations required to set up the template. These operations are password protected as they need only to be completed by the administrator and should not be available for modification by everyday users. Click here to view detailed steps to unlock the Admin Panel.

Add Environment

This step is required when setting up a Template in TRIAL mode only.

The target environment to which data is to be upload using the Simplified Loader template needs to be added. Click here to view detailed steps to add Environments.

Set Up BI Reports

Simplified Loader templates use BI Reports to retrieve data from Oracle Fusion. BI Reports need to be set up once for each template for each environment. This can only be completed by a user with access to the BI Administrator role. Click here to view detailed steps to set up BI Reports.

User Registration

This step is required when setting up a Template in FULL mode only.

User registration for Simplified Loader is administered using Lookup Values. The System Administrator within your organization needs first to define the template specific Lookup Type in Fusion ERP that will be used to maintain users. Click here to view detailed steps to add users.

Setup DFF

Any DFF fields configured in Oracle can be set up in the Simplified Loader template. DFFs need to be set up once for each template.

1. Open Admin Panel

2. From the Setup DFF section, click “Setup DFF in Template”. This will start the process to read and configure DFF details from Oracle Fusion.

3. If the user is not signed in to any environment, they will receive a login form to log in to Oracle Fusion. Select the environment and log in to Oracle Fusion. If the user is already logged in, then the Setup DFF wizard will not ask for login credentials again.

4. Clicking the Setup DFF button instructs the Simplified Loader template to read the DFF’s details from Oracle Fusion and create DFF fields in the About sheet and DataSheet. After completion, the user receives a confirmation message.

5. Setup DFF section now displays the environment and time when Setup DFF wizard retrieved details from Oracle Fusion.

6. The DFF fields are added at the end of the DataSheet and About sheet.

7. As with the other columns, the columns containing DFF fields can be organized (e.g. moved, hidden, or deleted) in the DataSheet.

Disables Data Load

Simplified Loader templates can be shared with other recipients for data input. If desired, by disabling the data load, the administrator ensures that the recipient cannot perform any upload operation.

Under such circumstances, if the recipient was to attempt to log in, the following message will appear:

Setup mandatory fields or default values

The administrator can define mandatory fields or define default values for each operation available in the template. The default values defined by the administrator are different from the user default values entered by the user in DataSheet.

The following table highlight application behaviour when the administrator sets up Mandatory Field and Default Values.

1. Unlock the Admin Panel

2. Unlocking Admin Panel also unlocks About Sheet

3. In row # 22, you will see the list of all operations supported by the template

4. Create an additional column in the About sheet. In row # 22, enter the name of this column as one of the following:

  • Mandatory Fields:
  • Force Default:

5. In the “Mandatory Fields” column, enter ‘Y’ to indicate a field as a mandatory field

6. In the “Force Default” column, enter value against the field to define a default value

7. Repeat steps 4 – 6 to define mandatory fields and default values for each operation.

NOTE: Mandatory fields or default values setup comes into effect when the user enters at least one value in the section containing mandatory fields or default value setup. For example, if Admin sets up the ‘Percent’ field in the Requisition Distribution section is mandatory with force default value 100. With this setup, when creating a Requisition, if the user skips adding any field value in the Requisition Distribution section, then both mandatory fields or default values check will not be performed as the Requisition Distribution section is wholly omitted from processing.

Application control preferences

This section applies when setting up a Template in FULL mode only.

When a user logs in to Oracle Fusion using Simplified Loader Template, their login is validated using Lookup Type in Oracle Fusion. At the time of login, the Simplified Loader Template reads the text entered in the Description field of the Lookups setup. The template uses values entered in Description fields to control the Template behaviour.

The Description fields can be set up with the following predefined preferences that control the behaviour of the template. The administrator can choose to use one or more preferences. When using multiple preferences, enter pipe (|) delimiter between preferences.

Override application control preferences at the user level

Preferences can be set up at the Lookup Type level of Lookup Code (User) level. When the same preference is defined at Lookup Type level and Lookup Code level, then the Lookup Code level preference takes precedence.

For example, the following setup will enable Integration Mode for all users using the template linked to the given lookup.

The following screenshot demonstrates setup with preference override at the user level.

Version Control

The administrator can control the Simplified Loader template version used within the organization. To implement version control, the administrator must specify the template version in the lookup type description field.

1. Get the version details of the template that you want to use within your organization. Click here to know how to get the template version.

2. In Oracle Fusion, navigate to the Lookup Type associated with the template.

3. Enter preference LatestVersion[TemplateVersion]. Replace text TemplateVersion with the version that you wish users to use.

4. When the user logs in, the login process reads content from both the Lookup Type’s description as well as the user-level Lookup Value’s description to validate the template version before uploading any data. The user receives the following message if the version specified by the administrator does not match the template version used by the user.

Allowed Operations

The administrator can control access to the operations performed by the user. To implement access control, the administrator has to use AllowedOperations preference and specify the operations permitted to the user. To specify multiple operations, use AllowedOperations preference with comma-separated operations. The default value of AllowedOperations preference is ALL.

Example: The Requisition template provides the following operations

Preference AllowedOperations[Get Requisition Details by Req Num, Create Requisitions] will allow the user to perform two operations mentioned in the preference. If the user clicks on the “Get Smart Form Details” operation, they will receive the following error message.

Integration Mode

Integration Mode is enabled using the Lookup Types associated with the template. Enabling Integration Mode allows the user to perform the operation as a centralized user. The following figure demonstrates how login credentials get selected when Integration Mode is enabled using Lookups setup.

To enable Integration Mode:

1. Open Admin Panel

2. In Enable Integration Mode section, select the environment and enter the Integration username and password of the user you wish to use to perform transactions. The Integration username and password are only used for transactional purpose and not used to query data.

3. Click “Get Code to enable Integration Mode”. This will display Enable Integration Mode window with unique code based on the entered username and password.

4. Setup application preference in Lookups using TransactAs[ObfuscatedCode] or TransactAs[SRVR]. When using SRVR as a preference value, also ensure to store login code with Simplified Loader.

5. The other allowable value for TransactAs preference is SELF. Preference TransactAs[SELF] indicates that the transaction should be performed using the user’s login credentials logged in to the Simplified Loader template. TransactAs{SELF] is the default behaviour.

Examples:

TransactAs preference set up in the following three screenshots indicates that the EXTERNAL.USER and RESTRICTED.USER will perform the transaction as a centralized user. The remaining two users will perform the operation using their login credentials. The three screenshots illustrate different methods to achieve the same application behaviour.

a. TransactAs login code set up at the user (Lookup Code) level

b. TransactAs login code setup at the template (Lookup Type) level with override at the User (Lookup Code) level

c. Login code is stored with Simplified Loader. TransactAs login code setup at the template (Lookup Type) level with override at the User (Lookup Code) level

Amending Lookups

The lookup setup in Simplified Loader templates is flexible. You may choose to amend lookups when adding DFF in the Excel template or adjust lookups to suit your organization’s needs. The administrator can decide to change the selection criteria on existing lookups or change Excel field mapping with data fields retrieved from Oracle Fusion or add new lookups. Before amending lookups, it is crucial to understand the following two components that make the lookup engine’s core components.

1. Pull data from Oracle Fusion

2. Associate lookup values from Oracle with fields displayed in DataSheet

Pull data from Oracle Fusion

Lookup data is sourced from BI Reports in Oracle Fusion. Follow the steps below to review and modify lookups.

1. Log in to Oracle Fusion as BI Administrator

2. Navigate to Catalog folder /Custom/SL_Template/{TemplateName}

3. Under the template folder, edit the Data Model related to the lookup

4. To add data lookup values from a different source, modify the SQL to include additional SELECT statements using the UNION clause. When adding a SELECT statement,

  • Each lookup dataset is identified by a SECTION_NAME. SECTION_NAME is the first column in the select statement.
  • Followed by SECTION_NAME are the combinations of LOOKUP_FIELD_NAME and LOOKUP_FIELD_VALUE. You can define up to ten lookup field values.
  • SECTION_NAME and LOOKUP_FIELD_NAME are eventually used in the Simplified Loader template to map data from Oracle Fusion with fields in DataSheet. It is not required that the SECTION_NAME and LOOKUP_FIELD_NAME in the SELECT statement match Section and Column names in Excel Template.

5. The changes are only required in the Data Model. The report associated with the Data Model should not be modified.

Changes in lookups SQL is not limited to adding a new SELECT statement. Existing SELECT statements can be amended or removed as appropriate. You can choose to modify the SELECT statement to add additional filter conditions or include more fields in the existing lookup dataset.

After amending the Data Model, Simplified Loader will not override the Data Model changes if a user attempts to create a BI Report again from the Excel template.

Associate lookup values from Oracle with fields displayed in DataSheet

Associating lookup values from Oracle with fields displayed in DataSheet is performed in the About sheet.

1. Unlock the About sheet by unlocking the Admin Panel

2. In the About sheet, unhide columns E & F. The usage of these columns is:

  • Column E: Return lookup data values selected by the user back to DataSheet
  • Column F: Filter data using DataSheet fields specified in this column

3. The values in Column E and F must be entered in the following format. SECTION_NAME.LOOKUP_FIELD_NAME[DATA_SHEET_FIELD_NAME]

4. To define associations between multiple fields, use the same format with a semicolon and single space as a separator “; ”.

5. Using the above steps, you can define Lookup data values against any fields in the DataSheet.

6. After completing the lookup setup in the About sheet. Close the Excel file and open it again. This will automatically colour the lookup field in DataSheet green.

When a user double clicks a lookup field in DataSheet, the lookup engine performs the following steps:

1. Get column name where user double-clicked

2. From the About sheet, read the data input in column F against the user double-clicked field.

3. Read all DATA_SHEET_FIELD_NAME mentioned in square brackets in column F

4. For all fields defined within the square brackets, read values from the DataSheet

5. Auto filter lookup values based on the values specified in the DataSheet

6. Display lookup window with filtered data to the user

When the user selects the value in the lookup window, the lookup engine performs the following steps:

1. Read all values from the row that the user selected in the lookup window

2. From About sheet, reads DATA_SHEET_FIELD_NAME entered in column E

3. Place value in DataSheet for all DATA_SHEET_FIELD_NAME mentioned in square brackets

Customize data selection criteria

Simplified Loader templates use SQL Data Model to pull data from Oracle Fusion to Excel template. These SQL Data Model can be changed to accommodate any changes in SQL filter criteria. When modifying SQL Data Model, you can only modify the filter criteria, but you must not change the SELECT clause.

When changing the SQL Data Model, you can utilize the parameters present in the Data Model. All Simplified Loader Data Model have SL_USER_NAME parameter in common.

Custom Validations & Data Enrichment

All Simplified Loader Excel templates have a provision to add custom logic for validation or data enrichment. Your organization can utilize a custom validation module to implement any logic specific to your organization.

Example of data validations:

1. For the Purchase Order template, only allow the value “3 Way Match”.

2. For the Project budget template, only allow the upload of baselined versions

Data can be enriched using the Default value feature. However, suppose your organization wants to override any field value based on predefined conditions. In that case, they can utilize the data enrichment feature. Example of data enrichment are:

1. For the Purchase Order template, the Matching Basis field value always defaults to “3 Way Match”.

2. For the Project budget template, the Status field value always defaults to Baselined.

3. For Requisition template, default field “Submit Requisition after import” to “Y”.

For custom validations, please contact Simplified Loader support.