Creating a HCM Spreadsheet Data Loader Template for Loading New Hires

Before You Begin

This 30-minute tutorial shows you how to create a HCM Spreadsheet Data Loader (HSDL) template to bulk load new hires.

Background

HCM Spreadsheet Data Loader is a tool that enables you to create use-case specific bulk data loading spreadsheets for business users to use.

In this tutorial, you will:

Create a spreadsheet template to bulk load new hires.
Default and hide attributes the user need not supply.
Configure the auto-generation of person and assignment numbers.
Validate and test the template.
Configure the spreadsheet attributes.
Configure template parameters.

Tutorial Scenario

In this tutorial, you'll create a template to bulk load basic new hire details, auto-generating the person number. For the purposes of the tutorial, you'll be capturing attributes for the following record types:

Worker
Person Name
Person Legislative Data
Work Relationship
Employment Terms
Assignment

You can repeat the steps described in this tutorial to extend the template, to include attributes from the other record types provided by the Worker object hierarchy.

What Do You Need?

A Windows machine with the Desktop Integrator installed. Downloaded this from the Tools menu in the application. Desktop Integrator is not available on the Mac operating system.
Access to the Spreadsheet Templates task in order to create and maintain spreadsheet templates.
Follow the steps in the Securing the Spreadsheet Templates Task topic within the Configure HCM Spreadsheet Data Loader Security tutorial, to configure a role with the access to create spreadsheet templates for the Worker business object.
Note: Custom job roles should be configured to provide access to HCM Spreadsheet Data Loader functionality.

Create the Spreadsheet Template

In this step, we create the spreadsheet template and select the attributes that are required to create a new hires.

Create the Template

On the home page, click My Client Groups > Data Exchange.
On the Data Exchange page, select Spreadsheet Templates.
Click Create to open the Create Template dialog box.

Specify the template name, code, select a business object of 'Worker' and optionally provide a description. Leave the Supported Action as 'Create and Update'.

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

Select the Template Attributes

In this step, you'll select the attributes that you want your users to supply when creating new hires.

Click Design.

Select the Action Code attribute in the Available Attributes panel and add it to the Selected Attributes panel.
You'll be asked how records in the spreadsheet will be identified.
Select User Key.
Tip: You might choose the surrogate ID option for templates used to maintain existing records.

Click OK. Other mandatory attributes are added automatically to the list of selected attributes:

Tip: The key icon prefixes all attributes that uniquely identify a record in the spreadsheet.
Save your changes.
On the Available Attributes panel toolbar, click Show.

Uncheck the All checkbox, then check the Required for new records checkbox.
Click outside of the Show multi-select choice list to filter the list of Available Attributes to those attributes that meet the criteria specified.

You'll see that Start Date is required, but not already included in the list of selected attributes. Add Start Date to the Selected Attributes panel.

Tip: The green circle with a tick icon identifies attributes that are already saved to the template.
Click on the choice list next to the Available Attributes header and select Person Name. The Available Attributes panel will now display the Person Name attributes.

Click Show on the Available Attributes panel toolbar and uncheck the Conditionally required and Optional checkboxes.

Click outside of the Show multi-select choice list to filter the list of Available Attributes to those attributes that meet the criteria specified.
Select them all and add them to the Selected Attributes panel.

Note: Some of the newly selected attributes won't be seen in the Selected Attributes panel. The template designer will determine which attributes match those already selected from other objects, default their values to the value of the matching attribute and hide them from the spreadsheet.

By default, only those attributes that are available in the spreadsheet are displayed in the Selected Attributes panel.

Tip: You'll learn how to expose hidden attributes in the steps below.
On the Available Attributes panel, change the Show multi-select choice list to display All attributes.
In the search box enter "First" and click the Search icon.
Add the First Name attribute to the list of selected attributes.
Repeat for any other optional attributes that you want your user to specify for the person name.

Save your template.
Use the steps above to add the Required and Required for new records attributes for the following record types:
- Person Legislative Data
- Work Relationship
- Employment Terms
- Assignment
Save your template.

You have now added all the attributes that are required when creating a new hire.

Tip: You can use the steps above to add attributes from other record types, which you want your users to supply for new hires.

Reviewing Hidden Attributes

Some attributes are hidden by default and later you will hide other attributes that you have configured defaults for.

In this step, you'll expose the hidden attributes in order that you can review and edit them, if required.

Note: This step is optional and not required in order to complete your New Hire template.

You can review all attributes that define the template by checking the Show excluded attributes checkbox in the Selected Attributes panel toolbar.

You'll now see the attributes that are part of the template definition, but not displayed in spreadsheets generated from the template.
- Effective Start Date and Person Number are automatically defaulted to the value supplied on the Worker record and hidden from the spreadsheet.
- Effective End Date is also hidden on all record types, because HCM Data Loader will default it to the end of time and so the user does not need to supply it.
Uncheck the Show excluded attributes checkbox.

Default and Hide Attributes

In this section, you'll provide defaults for many of the attributes and hide them, to simplify the data entry in the spreadsheet.

When you create a new hire, the effective start date of the Worker must be the same as the worker's start date.
Select the Effective Start Date attribute on the Worker object in the Selected Attributes panel. Click Edit.
Define a default for the Effective Start Date on the Worker object by selecting the Value radio button, selecting a type of Existing selected attribute and specifying the attribute to copy the value from. In this example, the Start Date on the Worker object.

Now that the attribute value is defaulted from another attribute, you can hide it from the spreadsheet by unchecking the Visible checkbox.

Click OK.

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.
Edit the Action Code attribute on the Worker object to define a default constant value of 'HIRE' and hide it.

Click OK.
Action Code is also exposed on Work Relationship, Employment Terms and Assignment. Edit each Action Code attribute to default the value from the Action Code attribute on the Worker object and hide it.

Edit these other attributes to configure a constant value default for them and hide them:

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

Tip: The Extending List of Values validation in HCM Spreadsheet Data Loader Spreadsheets tutorial explains how to configure a list of values for the Assignment Status Type attribute.

Edit these attributes to configure the default from an existing selected attribute:

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 \| ~

Save your changes.

You can repeat these steps to provide defaults for other attributes, such as Legislation Code, Worker Type, Business Unit and Legal Employer, though it is recommended that you test your spreadsheet first.

Configure Auto-generated Person Numbers

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

In the Selected Attributes panel, select the Person Number attribute and click the Edit icon on the panel toolbar.
Check the Autogenerate checkbox.

The column heading and description are updated. Change these as required.

Click OK.

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.
Edit the Assignment Number attribute on the Employment Terms object and check the Autogenerate checkbox.

To simplify data entry in the spreadsheet, you can also configure the Assignment Number to default its value from the Person Number attribute on the Worker object and hide it.
Repeat for the Assignment Number on the Assignment object.
Save your changes.

Validate and Test the Template

The template configuration should be tested before simplifying and activating it.

Click Validate to validate the template configuration.

You should not see any errors.

If you see errors, revisit the steps above.
Click Preview to generate a spreadsheet for the configured template. The GenericHdlSpreasdheet.xlsx file will be downloaded. Open it.
You'll be prompted to log in again. Click Yes.

Login with your application user and password. The spreadsheet will be generated with each of the Selected Attributes in the template seen as a column in the spreadsheet.
To be able to upload data with the spreadsheet you must first create a data set. Click Create Data Set on the Spreadsheet Loader toolbar.

Click OK on any dialog pages displayed.

A data set will be created with a defaulted name, you can override this if you want.
Double click in any of the cells in the first row under the column headings to initiate the row.

The attributes with defaults defined should display values in the initiated row. The attributes configured as mandatory that don't have a value will be surrounded by a red box.

Note: The value displayed may not be the same as the value defaulted in the template. When an attribute is lookup validated you must supply the lookup code as the default in the template. The lookup meaning is displayed in the spreadsheet.

See the Extending List of Values validation in HCM Spreadsheet Data Loader Spreadsheets tutorial for the different ways in which a spreadsheet column can provide lists of values.
Enter valid data for each of the empty cells in the row.
- 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.
- 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.
In the Spreadsheet Loader toolbar, click More > Upload.

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

The Upload Progress will change status until processing completes with a Success status.
Navigate to the Person Management task to ensure your new hires can be seen.
Note: There can be a short delay between a new hire being seen as successfully loaded in the spreadsheet and the worker being visible in the application. After new worker records are created the Update Person Search Keywords process is automatically submitted. You won't be able to see your new workers until this process has completed.

Configure the Spreadsheet Attributes

Review the spreadsheet to determine if you want to provide defaults for other attributes, configure attributes as mandatory, or configure list of values validation.

Making Attributes Mandatory

To make an attribute mandatory, edit it in the Selected Attribute panel and check the Required checkbox.
Click OK.

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

Select the Person Number attribute in the Selected Attributes panel and click Edit.
Change the Column Heading to 'Row Identifier', so users aren't confused when the person is created with a different person number. Change the Description to explain how to populate this attribute.

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

Configuring Column Order

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

Click the Reorder icon on the Selected Attributes panel toolbar.
Select an attribute and use the buttons to define the order in which the columns are displayed in the spreadsheet.
Save your changes.

Configuring List of Values Validation

The Extending List of Values validation in HCM Spreadsheet Data Loader Spreadsheets provides instructions on how to configure lists of values.

Use the Legislation Specific Lookups topic, under the Lookup Validation section, for the steps to configure an LOV for Gender. The same steps can be followed to provide legislation specific lists of values for Marital Status, Title and any other lookup validated attribute that is legislation specific.

Follow the steps provided in the Copying List of Value Configuration topic, under the View Object Validation section, to create lists of values for referenced foreign objects, such as position, legal employer, department and job.

Tip: Use the View Business Objects task to identify which surrogate ID and user key attributes reference which foreign objects.

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 disable post processing.

If you have closed your template, you need to reopen it:
- From the home page, click My Client Groups > Data Exchange.
- Click Spreadsheet Templates
- Search for your template and click the template name.
- Click Define
Click the create icon on the Spreadsheet Parameters table.
Click Search to see all parameters available to the template.
To automatically cease the upload of spreadsheet rows if more than 10% fail to load successfully, select the Maximum Percentage of Load Errors parameter and click OK.
The selected parameter will be added to the Template Parameters table. Override the default, updating the Value for the parameter to '10'.

Note: By default, every spreadsheet row will be uploaded, regardless of the number of errors encountered.
To disable post processing, add the Initiate Business Object Post Processing parameter to the template and set the Value to 'No'.

Note: By default, the Refresh Manager Hierarchy and Update Person Search Keywords processes are submitted every time data is uploaded using the spreadsheet. If this is going to be frequently each day, you may want to disable the automatic submission of these processes and schedule them to run independently of spreadsheet uploads.