Introduction

Use HCM Spreadsheet Data Loader (HDSL) to provide bulk-data loading capabilities to your business users and for your inbound integrations. You can configure spreadsheet templates for your specific use cases and assign access to those templates to just the roles that require them. For example, your recruiting integration could have access to a New Hire spreadsheet, whereas the Overtime Hours spreadsheet would be available to your Payroll Administrator.

Note:

The advantage of using HSDL for your integrations is that you're restricting the data uploaded to the use case supported by the template. You can also enforce data security, if required.

Objectives

In this tutorial, you will:

  • Understand how to design and configure HSDL templates.
  • Create a working template that can load new hires.

The Worker business object is complex with around 30 components over 5 levels in the object hierarchy. This tutorial will describe how to create a simple working new hire template, whilst explaining the common tasks to define a spreadsheet template. You can then apply these steps to any supported business object.

Having understood the basics of template creation, you can further extend your knowledge and templates to include flexfield attributes, duplicate records and so on. Review these tutorials:

Prerequisites

To complete this tutorial, you will require:

Task 1: Create the Template

In this step you'll create your new template based on the Worker business object hierarchy.

  1. Log into the application with a user who has a HSDL template designer role with access to the Worker business object.
  2. Navigate to My Client Groups > Data Exchange > Spreadsheet Templates.
  3. Click Create to open the Create Template page.
    4. Description of sample-image-1.png follows

  4. Specify the template name and code, set the business object to Worker, and optionally provide a description.
    5. Description of sample-image-1.png follows

  5. Click OK. You'll be navigated to the Define tab.

Task 2: Select the Template Attributes

In this step you'll add the basic attributes needed for each of the required and recommended components of the Worker object hierarchy.

Description of sample-image-1.png follows

You can repeat the steps described here to add attributes from other components in the Worker business object.

Note:

Use the View Business Objects task to review and understand the object hierarchy and supported attributes.

Tip:

It's good practice to start at the top of the object hierarchy and then include the child and grandchild components in the hierarchy. The template designer will automatically build the parent-child relationships for you.

Worker

The Worker component of the object hierarchy identifies the worker with the Person Number attribute and captures the Date of Birth.

  1. Click Design.
    2. Click the Design tab

  2. Select the Action Code attribute in the Available Attributes panel and add it to the Selected Attributes panel.
    3. Select the Action Code attribute

    When you select your first attribute into a template the designer will ask you which key to use to uniquely identify records.

  3. Select User Key on the Select Key dialog and click OK.
    4. Description of sample-image-1.png follows

    Tip:

    Some objects have multiple user key combinations. You can use the User Key choice list to choose the correct user key for your use case. You may want to select Surrogate ID for templates used to maintain existing records. Surrogate ID attributes supply a searchable list of values.

    Other attributes that are always required are added automatically to the Selected Attributes panel:

    The Effective Start Date and Person Number attributes are automatically added to the Selected Attributes panel

    Tip:

    The key icon prefixes all attributes that uniquely identify a record in the spreadsheet.
  4. Click the Validate and Save button on the page header.
    5. Click Validate and Save

  5. Click OK on the confirmation dialog.
  6. Click Show on the Available Attributes panel toolbar. Uncheck the All checkbox and check Required for new records.
    7. Select 'Required for new records' on the show choice list.

  7. Click outside of the Show multi-select choice list to filter the list of Available Attributes to those attributes that meet the criteria specified.
    8. Select Required for new records on the show choice list.

    Tip:

    The green circle with a check mark indicates the attribute is already selected.
  8. Add Start Date to the Selected Attributes panel.
  9. Validate and save your template.


Person Name

The Person Name component records your employee's name.

  1. Click on the choice list next to the Available Attributes header and select Person Name to display the attributes belonging to the Person Name object.
    2. Select Person Name from the Available Attributes header choice list

  2. Click Show on the Available Attributes panel toolbar and uncheck the Conditionally required and Optional checkboxes.
    3. Select Required for new records on the show choice list.

  3. Click outside of the Show multi-select choice list to filter the list of Available Attributes to those attributes that meet the criteria specified.
  4. Click the Add All button to add all attributes shown in the Available Attributes panel to the Selected Attributes panel.
    5. Add all available attributes to the selected attributes panel.

    Some of the newly selected attributes won't be displayed in the Selected Attributes panel. Attributes on the child record are automatically defaulted from their equivalent attribute on the parent record and hidden from the spreadsheet. This happens for attributes that identify the parent, such as PersonNumber, and for the effective start date attribute.

    The Effective Start Date and Person Number attributes aren't visible in the Selected Attributes panel

    The Effective Start Date and Person Number attributes from the Person Name component aren't visible in the Selected Attributes panel.

    Tip:

    Check the Show excluded attributes checkbox on the Selected Attributes panel toolbar to review attributes that are included in the template definition but won't be seen in the spreadsheets generated from the template.

  5. On the Available Attributes panel, change the Show multi-select choice list to display All attributes.
  6. Enter First in the available attributes search box and click the Search button.
    7. Filter attribute by the search term First

  7. Add the First Name attribute to the list of selected attributes.
  8. Add any other optional Person Name attributes you want in your template.
  9. Validate and save your template.


Person Legislative Data

The Person Legislative Data component captures your employee's marital status and gender.

  1. Filter the Available Attributes panel to display the Person Legislative Data attributes.
  2. Use the Show filter to display attributes that are Required and Required for new records.
  3. Click Add All to add all attributes shown in the Available Attributes panel to the Selected Attributes panel.
    4. Add all required person legislative data attributes

  4. Change the Show multi-select choice list to display All attributes.
  5. Search for and add these optional attributes to your template:
    • Gender
    • Marital Status
  6. Review the Person Legislative Data attributes now seen in the Selected Attributes panel.
    7. The Legislation Code, Gender and Marital Status are seen in the Selected Attributes panel.

  7. Review other available Person Legislative Data attributes and add to your template as required.
  8. Validate and save your template.

Work Relationship

The Work Relationship component specifies the legal employer for your employee.

  1. Add all Required and Required for new records attributes from the Work Relationship component to your template.
  2. Change the Show multi-select choice list to display All attributes and search for and add the Primary Employment attribute to your template.
  3. Review other available Work Relationship attributes and add to your template as required.

    4. Tip:

    Use the View Business Objects task to review all available attributes along with their descriptions.
  4. Validate and save your template.

Employment Terms

Employment Terms are no longer visible in Oracle HCM, but you still need to supply a skeleton Employment Terms record to link the Work Relationship and Assignment records.

  1. Add all Required and Required for new records attributes from the Employment Terms object.
  2. Validate and save your template.

Assignment

The Assignment record type is where you specify your employee's department, location, position, job etc.

  1. Add all Required and Required for new records attributes for the Assignment object.
  2. Change the Show multi-select choice list to display All attributes.
  3. Search for and add other attributes required in your template. Here are a few suggestions:
    • Person Type
    • Primary Assignment for the Work Relationship
    • Department
    • Business Unit
    • Job Code
    • Position Code
    • Grade Code

    Tip:

    Use the View Business Objects task to review all available attributes along with their descriptions.
  4. Validate and save your template.

Task 3: Configure Attribute Defaults

In this task, you'll learn how to configure default values for your template attributes. This simplifies data entry for the user and can restrict what actions the end-user can perform.

Configuring Constant Defaults

You can configure attribute values to have a constant value.

  1. In the Selected Attributes panel, select the Action Code attribute on the Worker component and click the Edit button on the panel toolbar.
    2. Add all required assignment attributes

  2. Define a default for the Action Code by selecting the Value radio button, selecting a type of Constant value, and specifying a value of HIRE.
    3. Add all required assignment attributes

  3. Click OK.
  4. Edit these other attributes to configure a constant value default for them:
    5. Attribute Component Constant Value
    Name Type Person Name GLOBAL
    Effective Sequence Employment Terms 1
    Effective Latest Change Employment Terms Y
    Assignment Status Type Employment Terms ACTIVE_PROCESS

Note:

When you specify a default value for a lookup validated attribute you need to specify the lookup code, not the meaning.

Configuring Defaults from Other Selected Attribute Values

You can configure attributes to default their values from another selected attribute.

Tip:

You won't see attributes automatically populated from the source attribute within the spreadsheet, the defaulting happens when uploading the spreadsheet data to the staging tables. When defaulting attribute values from another selected attribute, always hide the defaulted attribute.
  1. Edit the Effective Start Date attribute on the Worker object in the Selected Attributes panel.
    2. Select the Effective Start Date attribute and click edit

  2. Define a default for the Effective Start Date attribute by selecting the Value radio button, selecting a type of Existing selected attribute, and specifying the Start Date | Worker attribute to copy the value from.
    3. Add all required assignment attributes

    Tip:

    The value before the pipe is the attribute name, the value after the pipe is the component in the hierarchy that the attribute belongs to.

    Note:

    When you create a new hire, the effective start date of the Worker record must be the same as the employee's start date.
  3. Uncheck the Visible checkbox to hide the attribute from the spreadsheet.
    4. Add all required assignment attributes

  4. Click OK.

    5. Note:

    The template designer has already defaulted the Effective Start Date attributes on the other objects to the Worker Effective Start Date, so there is no need to do this manually.
  5. Edit the Action Code on the Work Relationship, Employment Terms and Assignment components to default the value from the Action Code on the Worker component and hide the attribute from the spreadsheet.
    6. Add all required assignment attributes

  6. Edit these attributes to configure the default from an existing selected attribute and uncheck the Visible checkbox:
    7. Attribute Component Existing Selected Attribute
    Start Date Work Relationship Start Date | Worker | ~
    Legislation Code Person Legislative Data Legislation Code | Person Name | ~
    Assignment Status Type Assignment Assignment Status Type | Employment Terms | ~

  7. Validate and save your template.

Configure Defaults using an Expression

You can use groovy expressions to define the default value for an attribute. These can include references to other selected attributes by specifying the Attribute Key that uniquely identifies the attribute in the template.

  1. Click the Advanced Options button on the Selected Attributes panel toolbar.
    2. Click the gears icon

  2. Find the Attribute Key for the Person Number attribute.
    3. Click the gears icon

    Tip:

    The attribute key is typically the component name (Worker) followed by the base attribute name (PersonNumber). You can edit these, but they must be unique within the template.
  3. Click Cancel.
  4. Edit the Assignment Number attribute on the Employment Terms component.
  5. Define a default by selecting the Value radio button, selecting a type of Use expression, and specifying "ET" + Worker_PersonNumber as the value.
    6. Add all required assignment attributes

    Tip:

    Replace Worker_PersonNumber with the attribute key used by your template if it's different.
  6. Click Validate to validate the expression.
  7. Click OK.
  8. Configure an expression default for the Assignment Number attribute on the Assignment component using the expression "E" + Worker_PersonNumber .
  9. Validate and save your changes.

Tip:

Configure defaults for other attributes to simplify data entry, such as Legislation Code, Worker Type, Business Unit and Legal Employer. You can either hide these or leave them for the end-user to override if required.

Tip:

It's recommended that you don't hide attributes defaulted with constant values or expressions until you've successfully tested them.


Task 4: Configure Autogenerated Person Numbers

If you want your user's to manually specify your person and assignment numbers, then you can skip this step.

  1. Select the Person Number attribute on the Worker component in the Selected Attributes panel and click Edit.
  2. Check the Autogenerate checkbox.
    3. Check the Autogenerate checkbox

    Tip:

    The Column Heading and Description are updated. Change these as required.

    Note:

    Although the person number will be generated when the worker is successfully uploaded, the user of the spreadsheet will still have to supply a value to uniquely distinguish each worker in the spreadsheet. The value supplied will be replaced by the autogenerated value.
  3. Click OK.
  4. Edit the Assignment Number attribute on the Employment Terms object and check the Autogenerate checkbox.
    5. Check the Autogenerate checkbox

  5. Uncheck the Visible checkbox and click OK.
  6. Repeat these steps to autogenerate and hide the Assignment Number on the Assignment component.

    7. Note:

    The Assignment Number attributes were defaulted from the Person Number in the previous step.
  7. Validate and save your template.


Task 5: Validate and Test the Template

The template configuration should be tested before simplifying it and configuring access to it.

  1. Validate and save any recent changes.
  2. Click Preview to generate a spreadsheet for the configured template. Depending on your web browser, the GenericHdlSpreasdheet.xlsx file will be downloaded. Open it.
    3. The preview button is available at the top of the page

  3. Click Yes when prompted to log in.
    4. Modify the column heading and description.

  4. Login with your application username and password. The spreadsheet is generated with each of the visible attributes in the Selected Attributes panel displayed as a column in the spreadsheet.
  5. Click Create Data Set on the Spreadsheet Loader toolbar.
    6. Click Create Data Set.

    Note:

    Spreadsheet rows must be created within a data set. It's the data set that then gets uploaded to the Oracle HCM Cloud.
  6. Click OK on the dialog page.
    7. Click OK.

    A data set is created with a predefined name; you can override this if you want.

    The data set name is generated.

  7. Double click in any of the cells in the first row under the column headings to initiate the row.
    8. Click OK.

    Tip:

    Any mandatory cells without a value will be highlighted with a red outline. Any attribute configured with a default constant value will display the default.
  8. Enter valid data for each of the empty cells in the spreadsheet row.

    9. Tip:

    If you have configured the Person Number to be autogenerated, specify any value. It must be unique across all rows supplied in the spreadsheet.

    This value is only used during upload to identify the record, but once created, an autogenerated value will be used for Person Number and the Assignment Numbers.

    Tip:

    Columns with the [..] symbol after the name provide a searchable list of values. Double click the cell you want to provide a value for to open the LOV window.
  9. Click More > Upload on the Spreadsheet Loader toolbar.
    10. Click More > Upload.

  10. Click OK on any dialog pages displayed.
  11. Click Refresh regularly to refresh the spreadsheet row status.
    12. Click Refresh.

    The Upload Progress will change status until processing completes with a Success or Error status.

    Click Refresh.

    If you get an error status, review the error message, and make corrections as required. Click Upload to resubmit the corrected spreadsheet rows in the data set.

  12. Navigate to the Person Management task to ensure your new hires can be seen.

    13. Tip:

    There can be a short delay in seeing your employees in the application.


Task 6: Configure Spreadsheet Attributes

This task describes how to hide defaulted attributes you don't want the end-user to change, configure attributes as required, change the column order and update labels and descriptions.

Hide Defaulted Columns

Once your constant and expression defaults are successfully tested, hide defaulted attributes that you don't want the user to change.

  1. Edit the attribute to hide from generated spreadsheets.
  2. Uncheck the Visible checkbox.
    3. Click Refresh.

  3. Click OK.

Making Attributes Mandatory

In this step, you'll edit the attributes you want to highlight as required in the spreadsheet.

  1. Edit the Last Name attribute in the Selected Attributes panel and check the Required checkbox.
    2. Click Refresh.

  2. Click OK.

Configuring Column Headings and Help Text

Change the column headings and descriptions to use terminology that your business user will understand. For example, if you are autogenerating your person numbers you may want to rename the Person Name - Autogenerate column heading and description.

  1. Edit the Person Number attribute in the Selected Attributes panel.
  2. Change the Column Heading to Row Identifier so users aren't confused when the person is created with a different person number.
  3. Change the Description to A number to uniquely identify the row in the spreadsheet.
    4. Click Refresh.

    Tip:

    The description is displayed when you hover over the column heading in the spreadsheet.
  4. Click OK.

Reorder Columns

The order in which your columns are displayed in the Selected Attribute panel determines the order in which those columns are seen in the spreadsheet.

  1. Click the Reorder icon on the Selected Attributes panel toolbar.
    2. Click Refresh.

  2. Select an attribute and use the buttons to define the order in which the columns are displayed in the spreadsheet.
  3. Click OK to accept your changes and close the window.


Task 7: Configure Template Parameters

You can override the default spreadsheet data loading behavior by overriding the parameter default values. In this section, you'll ensure data loading ceases if over 10% of the spreadsheet rows are in error, and you'll enable load events for incremental updates.

  1. Click the Design tab.
    2. Click Define.

  2. Click the create icon on the Spreadsheet Parameters table.
    3. Click the add icon on the table toolbar

  3. Click Search to see all parameters available to the template.
    4. Review the available parameters.

  4. Select the Maximum Percentage of Load Errors parameter and click OK
    Select Maximum Percentage of Load Errors and click OK

  5. Update the Value to 10.
  6. Click the create icon and select the Enable Load Events for Incremental Updates parameters, click OK.
  7. Update the Value to Yes.
  8. Validate and save your template.


Task 8: Assign Roles and Activate your Template

On production you'll assign this template to the roles that should be able to access this, so you should assess that the roles you assign work with the template as intended before exporting your template.

For business users to be able to use this template their roles must be assigned data set access to the template and the template must be activated. Once activated you no longer automatically have access to maintain the template.

Assign Template Maintenance Access

Once the template is active you can only maintain it if your role is configured to maintain the template.

These steps describe how to assign template maintenance.

  1. Navigate to the Role Access tab of the template.
  2. Click Add on the Template Administration table.
    3. Click add on the Permitted Data Set Operations table

  3. Search for the template designer or maintenance role, such as HSDL Template Designer and add the roles that can maintain this template.
    4. search and select the role to assign access

  4. Click Validate and save.

Assign Role Data Set Access

Configure the roles that can generate spreadsheets from this template and the data set access they have.

  1. Navigate to the Role Access tab.
  2. Click Add on the Permitted Data Set Operations table.
    3. Click add on the Permitted Data Set Operations table

  3. Search for the role to assign template access to, such as Human Resource Specialist.
    4. search and select the role to assign access

  4. Check the roles to add and click Add.

    5. By default, the role will be assigned access to create, save, and upload data sets.


  5. Alter the data set access as required.
    6. Data Set Operations
    Operation Description
    Create Data sets can be created.
    Save Data set data can be saved to the staging tables.
    Upload Data sets can be uploaded, and valid rows saved to the application.
    Roll Back Data sets can be rolled back, deleting data that was successfully loaded and not subsequently updated. Roll back is only available for a few business objects.
    View All All existing data sets for the template can be downloaded and reviewed, not just those created by the user.

    Tip:

    You may have a template that you need a role to be able to create and save data to the staging tables but not have access to upload that data, and a second role that can't create new data sets but can review data sets created by other users and upload them. This can achieve a form of approval.
  6. Validate and save.


Activate Your Template

Only active templates are visible in the Run Spreadsheet Data Loader task and can be used when initiating HSDL using REST. For templates that have a Draft status, complete these steps.

  1. Click on the Define tab.
  2. Update the Status to Active.
    3. search and select the role to assign access

  3. Click Validate and Save.

    4. The template is now available to users with a role configured with data set access to the template.


Test Business User Access

You should now log in as a user with the role assigned access to your template and confirm the template is available in the Run Spreadsheet Data Loader task and that spreadsheets generated from the template work as desired.

Exporting Your Template

When your template is fully tested and ready to be moved to your production you can export the template definition.

  1. Log into the application with your HSDL Template Designer role and navigate to the Spreadsheet Templates task.
  2. Search for your template.
  3. Click the Actions button and select the Export action.
    4. export your template

    Your template definition is exported as an xml file and should be available in your Downloads directory.


Task 9: Import and Activate your Template in Production

In this task you'll import your template into production, assign the roles that can use it and activate it.

You'll require a HSDL Template Maintenance role on production configured to import templates for the business object your template is based on. Refer to the Configure Access to Design and Maintain HCM Spreadsheet Data Loader (HSDL) Templates tutorial for the steps to create such a role.

  1. Navigate to the Spreadsheet Templates task in Data Exchange.
  2. Click Import.
    3. Filter by HSDL and user

  3. On the Import dialog window, specify a name that describes the use case that the spreadsheet template supports, such as New Hires.

    4. Tip:

    A code will be defaulted based on the name specified but you can override this. It needs to be unique.
  4. Select the xml file of the template file to import, using the Import field.
  5. Provide a description so business users can understand when to use this template.
  6. If the template is legislation specific, specify the Legislative Data Group that it's applicable for.
    7. Filter by HSDL and user

  7. Click OK.

    8. You should receive confirmation that the template was imported with no errors. Click OK.

    Tip:

    If errors were encountered, download the log file for more information.

    You'll be returned to the Spreadsheet Templates page.

  8. Search for your imported template and click the template name to edit the template.
  9. Repeat the steps in Task 8 to assign template maintenance roles, assign role data set access and activate the template.
  10. Save your changes.

    11. Users with the assigned roles can now access your template.

The latest tutorials for HDL and HSDL are published in this topic on Cloud Customer Connect:

Acknowledgements

  • Authors - Ema Johnson (Senior Principal Product Manager)

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.