Cloud Documentation
Advanced Search


Using Global Payroll Interface
Close Window

 

This guide also applies to on-premise implementations

Table of Contents

Show All | Collapse

3 Manage HCM Extract Definitions

This chapter contains the following:

Extract Components: How They Work Together

Creating Extract Definitions for Payroll Interface: Critical Choices

Creating a Payroll Interface Report from a Copy: Worked Example

Defining an Extract Using the Simplified Interface: Worked Example

Frequently Used User Entities in HCM Extracts

Payroll Interface Output File Templates: Highlights

FAQs for Manage HCM Extract Definitions

Extract Components: How They Work Together

The HCM Extracts feature is a flexible tool for generating data files and reports. You use the extract components to define what information you want the application to extract and report on, and how the information is displayed, formatted and delivered. An extract definition consists of: one or more extract data groups, depending on how many logical entities you want to extract; one or more extract records depending on how many groups of information you want to collect; and one or more attributes depending on how many individual fields of data you want to extract.

Extract Definitions

An extract definition refers to the complete setup of an extract, that consists of extract data groups, criteria, records, attributes, advanced conditions and output delivery options. You use HCM extracts to extract, archive, transform, report, and deliver high volumes of HCM data from the Fusion HCM database. You can generate the output in the following formats:

  • CSV

  • XML

  • Excel

  • HTML

  • RTF

  • PDF

You can distribute the extracted information by email, fax and other delivery modes. Some common examples of extracts are: PDF payslips delivered to employees' mailboxes, payroll or benefits data transferred to third-party service providers, HR and talent data exchange between Fusion and legacy applications, for example in a coexistence scenario.

Data Groups

Extract data groups represent a business area or logical entity, for example person, assignment, or benefits. The application uses this information to retrieve the database item groups. You define one data group as the primary or root data group and this data group is the starting point of the data extraction.

Extract data group connections capture the association details between the current data group and the parent data group. The data group connections form the hierarchical relationship among the data groups.

Extract data group criteria enables you to define a set of filtering conditions the application must perform on an extract data group. You specify the criteria conditions using an expression or fast formula.

Extract Records

Extract records represent a grouping of related data or a physical collection of all fields required in the extract. For example, the Employee data group can have records such as Basic Details, Pay Details, Location Details, and Primary Contact An extract record is a collection of attributes which you can organize in a required sequence. For example, if a data group has 3 records, then you can specify the sequence in which the application processes the records. You can also select the next data group to identify which data group the application processes next.

Attributes

Attributes are the individual fields inside the extract record. An attribute is the lowest attribute level of a HCM extract and represents a piece of information, for example, person first name, person last name or person date of birth.

This figure demonstrates the hierarchy of information within a data group definition. In this example, the data group is a container for the logical group of data called Department. Within the Department data group there are two records of department data called Department Information and Department Other Information (extract records). Within the subgroup of Department Information there are two elements of information called Department ID and Department Name. Within the subgroup of Department Other Information there are two elements of information called Department Location and Department Start Date. An extract definition is a hierarchy of information with attributes such as Department ID at the bottom.

Hierarchy of data objects within an extract definition.

Creating Extract Definitions for Payroll Interface: Critical Choices

Designing an extract definition to meet the requirements of your third-party payroll provider includes the following decisions:

  • Whether to copy an existing extract definition or create a new one

  • Whether to refine the generated extract process

Note

Once an extract definition is submitted and an outbound interface report process is generated, any changes you make to the extract definition will affect your extract process when you submit your changes.

Methods of Creating Extract Definitions

When you create an extract definition, you define data groups, records, and data elements. You define what you want to extract, how it is extracted, and how you want to deliver the extracted data. When you submit an extract definition, the application creates a process that you run to perform the actual extraction of data.

There are two ways you can create an extract definition and its related extract process: creating a new one or copying one that exists. In both cases, you can use the Manage Extract Definitions task from the Data Exchange work area.

To create an extract definition to use for a new extract process, choose the Payroll Interface extract definition type. This predefined definition type includes basic parameters that support the extract process, such as the Changes Only parameter that enables extracting only changed data since the previous run. You will need to build data groups, records, and data elements for the data that you want to extract.

When you copy an existing extract definition, you save it under a new name and then modify it as needed before you submit your changes. Once you submit your changes, the application creates a new extract process using your newly modified definition.

The predefined extract definition, US ADP Third-Party Payroll Extract, is available for this purpose. It extracts employee and assignment records and produces the results into an output file using the US ADP Connection Payroll report template from Oracle BI Publisher. Although this predefined extract definition extracts payroll data for Automatic Data Processing (ADP), a third-party payroll provider in the US, you can copy and modify it to meet the requirements of your payroll provider, as needed.

Note

When creating an extract definition from a copy, you must still generate and compile the formulas, just as you would when creating a new extract definition, before you submit your changes.

Refining the Extract Process

Once you submit a new extract definition, the application automatically generates an extract process that you can edit using the Refine Extracts task from the Data Exchange work area. You can choose which parameters should display and which values are required. For example, you can choose to hide a specific parameter, or set a specific parameter so its value is required to run the extract process. You can also add parameters, as needed, that you want to be made available when running the extract process.

Creating a Payroll Interface Report from a Copy: Worked Example

This example demonstrates how to create an outbound interface report by copying an existing extract definition. The generated process extracts payroll-related employee and assignment information that is formatted for submission to a US third-party payroll provider.

The following table summarizes the key decisions for the scenario of building an extract definition.


Decisions to Consider

In This Example

What should the name be for the extract and what will be the name of the process it creates?

US Payroll EFT Report

What special parameters does the extract use?

Parameters that support the option of extracting only changed records.

What special employee information will be extracted?

All payroll-related information suitable to send to a third-party payroll provider for payments to employees.

The steps in this scenario are:

  1. Copy the existing extract definition for US third-party payroll providers.

  2. Submit the copied extract definition to create the extract process.

  3. Edit the parameters of the generated extract process so it is ready for use.

You can perform all of these steps from the Data Exchange work area.

Assumptions and Prerequisites

This worked example assumes that the following prerequisites have already been met:

  1. The legislative data objects have been set up according to your business requirements.
  2. The security objects have been set up according to your business requirements.
  3. The payroll objects have been set up according to your business requirements.

Copying the Extract Definition

  1. Select the Manage Extract Definitions task in the Data Exchange work area.
  2. In the Name field in the Search section, enter ADP.

    The US ADP Third-Party Payroll Extract displays in the search results.

  3. Select the row containing US ADP Third-Party Payroll Extract, and then click Copy.
  4. Enter a name for the new extract definition, for example US Payroll EFT Report.

    Note

    The name you assign here is used for the generated extract process and appears in the list when selecting the extract process to submit.

  5. In the Search Results section, select the row containing your new extract definition, then click Edit.
  6. Enter basic information about your extract definition as shown in the following table, and then click Save.

    Field

    Value

    Tag Name

    Name used in the XML data file. Generated automatically, but can be modified.

    Description

    Optional text that displays in the search results about the generated report process for this extract.

  7. Click Switch Layout.

Compiling Formulas

  1. In the Extract Definition hierarchy, click Extract Execution Tree.

    Note

    You might need to collapse the data group node to find Extract Execution Tree in the hierarchy.

  2. On the Extract Execution Tree page, click Compile Formula.

    Note

    Compiling formulas can take several minutes. You must wait for all formulas to compile before moving to the next step.

  3. After the cursor is still, indicating that compiling is finished, from the View menu, select Collapse All.
  4. From the View menu, click Expand All, and then verify that the formulas have compiled without error.
  5. Click Submit.

Refining the Extract Process

After an extract definition is submitted, you can further refine how the extract process that is automatically created can be used, which parameters should display, and which values are required. In this example, we want to set certain parameters so that their values can be selected from choice lists when running the extract process.

  1. From the Data Exchange work area, select Refine Extracts.
  2. In the Search section, select the legislative data group.
  3. In the Flow Pattern field, enter US Payroll EFT Report, and then click Search.
  4. In the Search Results section, in the row containing US Payroll EFT Report, click Edit.
  5. On the Parameters tab, click Edit to modify each of the following parameters as shown in this table.

    Flow Parameter

    Display Format

    Lookup

    Required

    Changes Only

    SmartLOV

    Yes/No

    No

    Payroll

    SmartLOV

    Payroll

    Yes

    Payroll Period

    SmartLOV

    Payroll Period

    Yes


  6. Verify that the remaining parameters are set to your preference. You can choose which parameters are visible to the user and whether a parameter value is required to run the process.
  7. Click Submit.

Defining an Extract Using the Simplified Interface: Worked Example

This example topic demonstrates how to create a HCM extract including creating data groups, records, and attributes using the simplified interface. You create an extract definition to capture the details of what you want to extract, the structure in which the data must be extracted and how you want to deliver this data. FAST Bank is a global organization with subsidiaries all over the world. As part of an external reporting requirement, FAST Bank must obtain the department and employee details across the entire company. This information must be sent to a third party in an XML file and to the HR manager with employee details grouped by department as a Headcount Report.

The following table summarizes the key decisions in this scenario:


Decisions to Consider

In This Example

How many extracts do I need to create to produce this type of report?

You create one extract definition to define a headcount report.

What type of extract do I create?

You create a HR Archive extract.

How many data groups do I need to create?

In this example there are 2 functional groups of information, therefore you create two data groups, one for department and one for employees.

How many records do I need to create?

You decide the number of records based on the sub-group of attributes within a data group. In this example, you create two records for the department data group:

  • Department Details

  • Department Summary

You create one record for the employees data group: Employee Details.

How many attributes do I need to create?

You decide the number of attributes based on the specific information required for that report. In this example, create the following attributes for the Department Details record:

  • Department Name

  • Department Location

For the Department Summary record, create the following attributes:

  • Record Code

  • Report Date

  • Employee Count

For the Employees Details record, create the following attributes:

  • Full Name

  • Gender

  • Date of Birth

  • Salary

  • Bonus

  • Tax Rate

Do I need to create any fast formulas?

You can use fast formulas at the following levels:

  • Extract Criteria level to determine certain conditions.

  • Extract Rule level to derive attribute values.

  • Extract Advanced Condition level to specify complex conditions.

  • Extract Record level to automatically generate formulas when you use the Generate Formula option.

Creating an Extract Definition

  1. On the Manage Extract Definitions page click on the Create icon to create a new extract.

    Use the Switch Layout button to open the extract in the Professional interface. Use the Professional interface to create and define HCM extracts without using a drag and drop system. You can perform most of the tasks for defining the extract in the Simplified interface, however to enter an effective date for the extract, you must switch to the Professional interface.

  2. Enter FAST Bank Extract as the name and select HR Archive as the type. The application automatically creates the tag name based on the extract name and uses this name to generate the XML output file.
  3. Click Save and the application saves the extract definition and automatically generates the parameters based on the type of extract. The parameters control the output of an extract. In this example, the application creates the following parameters:
    • Effective Date

    • Legislative Data Group

    • Parameter Group

    • Report Category

    • Request ID

    • Start Date

Creating Extract Data Groups

  1. Select the Design icon to create the data groups.
  2. Select the Create icon or use the HCM Data Objects tree to drag and drop a data group into the local area. A data group represents data that belongs to one or more logical data entities.
  3. Using the drag and drop action, the application automatically creates the following information:

    Field Name

    Entry

    Name

    Departments

    User Entity

    PER_EXT_SEC_ORGANIZATION_UE

    Root Data Group

    Yes (By selecting this checkbox you select this data group as the starting point for the extract execution.)


  4. Select Save and create another data group with the following information:

    Field Name

    Entry

    Name

    Employees

    User Entity

    PER_EXT_SEC_ASSIGNMENT_UE

    Root Data Group

    No


Creating Extract Data Group Connections

  1. Ensure you enter the following details to create the extract data group connections. Data group connections enable you to define the master-detail of parent-child relationship between the entities. For example the Department and Employees data groups are linked with Department ID.
  2. Complete the general fields, as shown in this table:

    Field Name

    Entry

    Parent Data Group

    Departments

    Parent Data Group Database Item

    PER_EXT_ORG_ORGANIZATION_ID

    Data Group Database Item

    PER_EXT_ASG_ORG_ID


  3. Define the data group criteria for each data group. Data group criteria enables you to specify the filter conditions of what data you want to archive. You can specify the filter conditions as an expression or fast formula.

Creating Extract Records

  1. Select the Department Data Group and ensure it includes the following extract record details. Extract records represent a physical collection of all required fields. If a data group has 3 records, then you can specify the sequence in which the application processes the records using the sequence field. You can also select the Next Data Group to identify which data group the application processes next.
  2. Complete the general fields, as shown in this table:

    Field

    Department Summary

    Department Details

    Name

    Department Summary

    Department Details

    Effective Start Date

    1/1/00

    1/1/00

    Sequence

    20

    10

    Type

    Trailer Record

    Header Record

    Process Type

    Fast Formula

    Fast Formula

    Next Data Group

    NA

    Employees


  3. Save the records and then select the Employees Data Group. Ensure this data group includes the following extract record details.
  4. Complete the general fields, as shown in this table:

    Field Name

    Entry

    Name

    Employee Details

    Effective Start Date

    1/1/00

    Sequence

    10

    Type

    Detail Record

    Process Type

    Fast Formula

Creating Attributes

  1. Select the Departments Details record within the Department Data Group and ensure it includes the following extract attribute details.
  2. Complete the general fields, as shown in this table:

    Field Name

    Attribute Entry

    Attribute Entry

    Name

    Department Name

    Department Location

    Type

    Database item group

    Database item group

    Database Item Group

    Organization Name

    Organization Location Country


  3. Select the Department Summary record and ensure it includes the following extract attribute details.
  4. Complete the general fields, as shown in this table:

    Field Name

    Attribute Entry

    Attribute Entry

    Attribute Entry

    Name

    Record Code

    Report Date

    Employee Count

    Data Type

    Text

    Date

    Number

    Type

    String

    Parameter Element

    Summary Element

    String Value

    999

    NA

    NA

    Parameter

    Effective Date

    NA

    NA

    Aggregate Function

    NA

    NA

    Count

    Aggregate Record Name

    NA

    NA

    Employees Employee Details


  5. Select the Employee Details record within the Employees Data Group and ensure it includes the following extract attribute details.
  6. Complete the general fields, as shown in this table:

    Field Name

    Attribute Entry

    Attribute Entry

    Attribute Entry

    Name

    Full Name

    Gender

    Date of Birth

    Start Date

    1/1/00

    1/1/00

    1/1/00

    Data Type

    Text

    Text

    Date

    Type

    Database Item Group

    Decoded database item group

    Database item group

    Database Item Group

    Person Full Name

    Person Gender

    Person Date of Birth


    Field Name

    Attribute Entry

    Attribute Entry

    Attribute Entry

    Name

    Salary

    Bonus

    Tax rate

    Start Date

    1/1/00

    1/1/00

    1/1/00

    Data Type

    Number

    Number

    Text

    Type

    Database item group

    Record Calculation

    Rule

    Database Item Group

    Assignment Salary Amount

    NA

    NA

    Calculation Expression

    NA

    Salary * 0.5

    NA

    Rule

    NA

    NA

    FAST Bank Tax Rule

Defining the Delivery Options

  1. Select the Deliver icon to define the delivery options.
  2. Select Export XSD to download the XML Schema Definition (.xsd) file for this extract setup. This exported file contains the structure of the extract definition: the data groups, records, and attributes.
  3. Use the delivery options page to define the formatting and layout options for the extract definition.

    You can define delivery options for an extract using a BI publisher template, with the following delivery file output types: PDF, XLS, XML, DOC, and the following delivery modes FTP, email, fax. You can also choose Documents of Record as the delivery mode. This delivery mode enables you to store the output in the database and allows employees to view online payslips from documents of record. If the XML output is split and burst as separate files, then you can select the bursting node. For example, if you want all employees to be sent an email with their payslip, then set the bursting node to Employee_ID.

  4. Complete the general fields, as shown in this table:

    Field

    Value

    Value

    Start Date

    1/1/00

    1/1/00

    End Date

    12/31/12

    12/31/12

    BI Publisher Template

    ReportLayout

    EFTLayout

    Output Type

    PDF

    EFT

    Delivery Type

    Email

    FTP

    Delivery Option Name

    Email to HR

    FTP to 3rd Party

    Output Name

    HeadcountReport

    EFTReport

  5. Ensure you enter the additional information such as, the server, username, and password for the FTP delivery type.
  6. Enter FAST Bank Extract as the reporting category and click Submit.

Submitting an Extract

An extract definition automatically creates an extract process (payroll flow) with the same name as the extract. The extract process enables you to define an execution sequence of multiple tasks, including pre and post tasks. You can use the Refine HCM Extracts task to view and modify the extract process submission parameters, if required.

  1. Select the Submit an HCM Process task and select the FAST Bank Extract process.
  2. Enter FAST Bank Extract - Jan 2012 as the Payroll Flow (extract process).
  3. Enter 1/1/12 as the Effective Date.
  4. Select Next. You can specify interaction details if the task is dependent on other tasks with different extract processes. For example, this task must wait because another task is running.
  5. Select Next and review the extract. You can schedule the extract, or run it immediately.
  6. Select Submit.
  7. Select OK and View Checklist to view the status of the process.
  8. Select the View an HCM Process task to review the results of the extract run. Search for the FAST Bank Extract process.
  9. Select Go to Task for FAST Bank Extract - Jan 2012, click the eyeglasses, and view the report output by selecting the report name.

Frequently Used User Entities in HCM Extracts

A user entity is a logical entity associated with a data group defined using HCM extracts. This topic describes user entities related to people and payroll, and describes the type of data you can extract by using those user entities. You select user entities in the application when you define a data group.

The following table lists the most frequently used user entities related to person and payroll that you can use to decide the type of data you want to extract.


User Entity Name and Code

Description

Person (PER_EXT_SEC_PERSON_UE)

Use this user entity to retrieve all persons across the enterprise and all person related attributes.

Worker (PER_EXT_SEC_ASSIGNMENT_UE)

Use this user entity to retrieve all workers across the enterprise and all person, worker related attributes. For example, relationship, term and assignment details.

Worker Payroll (PER_EXT_PAY_EMPLOYEES_UE)

Use this user entity to retrieve all workers and their payrolls across the enterprise, all person, worker, payroll related attributes, and element entry data.

User entities in HCM extracts.

Payroll Interface Output File Templates: Highlights

The US ADP Third-Party Payroll extract definition is configured to use a predefined eText template named USADPConnectionPayrollTemplate. Oracle BI Publisher uses this template to produce the output file that is sent to third-party payroll providers for payment processing. If you have additional information to send to the third-party payroll provider that is not supported by the predefined eText template, you must modify it or create a new one, as needed.

Understanding eText Templates

An eText template is an RTF-based template that is used to generate text output for electronic funds transfer (EFT) and Electronic Data Interchange (EDI) information. At runtime, BI Publisher applies this template to an input XML data file to create an output text file that can be transmitted to a bank or other customer. Because the output is intended for electronic communication, the eText templates must follow very specific format instructions for exact placement of data.

FAQs for Manage HCM Extract Definitions

How can I restrict the records to be extracted?

You can use extract data group criteria to define a set of filtering conditions the application performs on an extract data group. For example, you can use database items in the fast formula to represent the town_or_city and primary_flag columns in the per_addresses table to restrict the data to people living in London only. The extract definition would then exclude people with a primary address of anywhere other than London. You can specify the criteria conditions using an expression or fast formula.

What's an extract type?

The type of extract you select determines the purpose of the extract. Here are some examples of the different extract types and why you select them. Use the Full Profile type for complete employee and payroll data archives. Use the Payroll Interface type for providing data to third party payroll service providers. Use the Payments type for salary payment method archives. For example, paid though cheque or bank transfer. Use the Benefits Carrier type for providing data to third party benefits service providers. Use the Archive Retrieval type for reports based on permanently archived data. For example, payslip. Use the EOY Archive for end of year archives (HR Payroll, Benefits). Use the HR Archive type for all HR archives, the Payroll Archive for all payroll or payslip archives, and use the Other Payroll Archive for all payroll archives. The type of extract you select also determines the parameters that are automatically generated. For example, if you select the Payroll Interface extract type, then the application creates a changes only parameter, as well as the other parameters.

How do I create a Changes Only HCM extract?

You can create a changes only extract run by including the 'CHANGES_ONLY' parameter and setting up threading details in the extract definition. A changes only extract enables you to run an extract to find out what data has changed after the previous runs. For example, to find out if the job name has changed on a person's assignment from the previous run. You set up multi-threading database items for a changes only extract for faster processing. The application considers data at a multiple threading level and uses this information to compare the changes. This type of extract can generate incremental data by comparing the previous runs.

You can select from four different modes that provide you with different data outcomes. Select N to include all data in the extract. Select Y to compare this extract run with previous runs and display the data that has changed only. Select Attribute to include elements that have changed or marked as mandatory. Select Attrib_Old to display elements that have changed or marked as mandatory plus the previous value.

How do I create a BI Publisher template for HCM extract?

You create a BI Publisher template using the Export XSD option in the extract execution tree and saving the file to your local machine. You can then load the downloaded XSD file to the BI Publisher word plug-in using the XML Schema option. If you require a report in a specific format, then you can create a template and save it by arranging the fields in the required format. Otherwise, you can create a default RTF template using the All Fields option.

What's athreading database item and what is its connection to the extract data group?

A threading database item is required for implementing the Changes Only feature. The threading database item is a unique ID in the chosen user entity (UE). Generally, for Pay Employee UE and Assignment UE it would be DBI with %ASSIGNMENT%ID. For Person UE, it would be DBI with pattern %PERSON%ID. You can declare one threading database item at the root data group or any child data group level. For example, you declare the threading database item from the location where you need changes only.

What's a conditional action?

A conditional action identifies the action to perform, and optionally, a message based on the outcome of a conditional expression or a predefined fast formula. Conditional actions are applied on the extracted data similar to criteria conditions that are applied prior to extraction. Actions and messages are predefined in lookups, and you can add your own messages by creating new values for the lookup.

When a condition is satisfied, you can use this feature to perform certain predefined actions. For example, you can exclude employees that satisfy a condition, such as all employees from a predefined country. You can also configure this feature to raise a warning when an employee's salary is blank or beyond a specific level.

What's an exclusion rule?

An exclusion rule enables you to exclude or override a record that does not suit your requirements with your own record. The extract process does not process excluded records based on the legislative data group.

What's a reporting category in HCM extracts?

Report categories help to create combinations of various delivery options under a single run process or report category. By using report categories to deliver a HCM extract using a single run enables you to deliver multiple forms of output to multiple users for an extract definition. For example, an organization has a requirement to extract HR data and to e-mail the extracted data as a PDF report to HR , and FTP the same data in an EFT file to a third party. The organization can achieve this by creating 2 delivery options and grouping them under one reporting category.

Can I use Oracle Transaction Business Intelligence (OTBI) with HCM Extracts?

Yes. You have two options:

HCM Extracts can extract the data and produce an output using CSV, XML, or PDF. OTBI can then accept a data source in excel or XML format.

BI Publisher can also accept a data source in excel or XML format. HCM Extracts has integration with BI Publisher.