32Filtering and Delivering HCM Extracts

This chapter contains the following:

Using Extract Modes: Explained

You can create an extract to output data in different modes in the Manage Extract Definitions page. Use modes to extract data that has changed since the previous extract runs. For example, you can extract employee details whenever there is a change in the employee's location. The processing engine generates the current status of the data, compares it with the base-lined data of the previous runs, and identifies the new as well as any modifications. The output from such changes-only extract has the incremental data only.

You can manipulate the output in a changes-only extract to either exclude or include certain attributes, regardless of whether the attribute has changed or not. For example, by selecting an option to exclude an attribute from comparison, you can ensure that that attribute is not compared while identifying changes to include in the generated output of the next extract run. Therefore, even if the attribute has changed since the previous extract run, it will not be included in the output. You can also select an option to always include an attribute in the generated output of an extract run, even if the attribute has not changed.

Extract Modes

To enable different modes in an extract, you include the CHANGES_ONLY parameter and set up threading details in the extract definition. While running the extract you can use the CHANGES_ONLY parameter to control the extract mode. For example, to find out if the job name has changed on a person's assignment, set up multi-threading database items at assignment level and include the CHANGES_ONLY parameter. When you run the extract in the ATTRIBUTE mode, the extract compares data from the multi-threading level and outputs the incremental changes. You can link the PER_EXT_CHANGES_ONLY lookup or the ORA_HRY_CHANGES_ONLY lookup to the CHANGES_ONLY parameter so that you can select a mode when you submit the extract.

The following table describes the different extract modes, their lookup values and descriptions.

Mode Lookup Value Description


All attributes

Includes all data in the extract. A full extract is run which produces the full data output at that point of time. The archived data is utilized as a baseline.


Changed attributes

Compares this extract run with the previous extract runs and by comparing against the baseline (to identify the incremental data), displays the data that has changed only.


Changed and marked attributes

Includes elements that have changed or marked as mandatory


Changed and marked attributes with previous values

Displays elements that have changed or marked as mandatory plus their previous value

Note: You must run the Payroll Interface with the Attrib_Old mode whenever you use the US ADP PayForce Third-Party Periodic Extract.


Changed, marked attributes, previous data under threading group

Displays the following data under threading data group:

Changed data

Data marked as mandatory

Previous values

Parent data group values


Changed and marked attributes under threading group

Displays the following data under threading data group:

Changed data

Data marked as mandatory

Parent data group values

Filtering Extracts Using Advanced Filter Criteria: Example

Use advanced filter criteria to filter and extract data using complex SQL queries. You can also filter data using expressions or fast formulas. When using expressions, the available operators may not be sufficient to build complex expressions. You can use fast formulas to build complex filter criteria. However, using advanced filter criteria can improve the performance of the extract run. To use advanced filter criteria, you must have good understanding of the table aliases and writing SQL constructs.

Extracting Employees on Leave

This example explains how to use advanced filter criteria to extract employees who are going on leave in the next seven days. Managers can use this information to find the list of employees who have approved leaves starting in the next seven days.

  1. On the Manage HCM Extract Definitions page, create an extract definition and use the Switch Layout button to open the extract in the Professional interface.

  2. Select the Data Group link from the navigation tree to open the Data Groups page, and click Create.

  3. Select the user entity PER_EXT_SEC_PERSON_UE and click Advanced.

  4. In the Query tab, review the user entity tables and aliases that you want to use, and click OK.

  5. In the Data Group Filter Criteria area, click Add.

  6. Click the Edit icon in the Filter Criteria column.

  7. In the Edit Filter Criteria Condition window, create the following expression for retrieving only employees: Extract Person System Person Type = 'EMP'

  8. Click Advanced. The application converts the expression in the basic mode to the following SQL construct: (pptum.system_person_type='EMP')

  9. You can now append new SQL construct by using the EXISTS clause. Entering the following SQL construct will extract employees who are going on approved leave in the next seven days:

    ((pptum.system_person_type='EMP') and EXISTS
    select 1 from fusion.ANC_PER_ABS_ENTRIES abs
    abs.person_id = pptum.person_id and abs.start_datetime between
    pay_report_utils.get_parameter_value_date('EFFECTIVE_DATE') and (pay_report_utils.get_parameter_value_date('EFFECTIVE_DATE')+7)
    and abs.approval_status_cd = 'APPROVED'

    When writing an SQL query, if you want to retrieve any parameter value at run time, use the 'pay_report_utils.get_parameter_value_date('ESS PARAMETER')' function. The ESS Parameter is typically, unless explicitly modified, the capitalized parameter name where any space is replaced by an underscore. For example, the ESS Parameter of Effective Date is EFFECTIVE_DATE.

    Note: You cannot use more than 2000 characters to write the SQL query.
  10. To validate the SQL construct for any syntactical errors, click Validate.

  11. Click OK.

  12. To verify if the advanced SQL criteria is applied correctly, run the extract in GMZFT logging mode and check the logs.

HCM Extract Formula Types

You can launch the Manage Fast Formulas UI from the Navigator Menu Payroll Calculation work area Clicking on the task Manage Fast Formulas.

The following table lists the different types of formula you can use for extracts:

Formula Type Description

Extract Criteria

Formulas of this type are listed in the Data Group Criteria Fast Formula list. Use this type to specify complex criteria and to indicate if record should be extracted or not.

Extract Rule

Formulas of this type are listed in the Rule Type Attributes Fast Formulas list. Use this type to calculate and derive the attribute value if based on complex logic or business rules.

Extract Advanced Condition

Formulas of this type are listed in the Record Attribute Fast Formulas list. Use this type to specify complex conditions to indicate if an action should be raised (for example, a log or warning).

Extract Record

Formulas of this type are generated automatically when using the Generate Formula option in Extract Records.

Delivery Options: Explained

You specify delivery options as part of the extract definition. Delivery option parameters specify the output format, the delivery method (email, FTP), and other parameters required for integration with BI Publisher layer.

How Delivery Options Work

HCM Extracts archives the extracted data into result tables and stores it as an XML output in the database. The application transforms the XML output into a formatted output such as HTML, PDF, EDT, or XLS. The formatted output is then delivered through email, fax, FTP, or print, depending on the delivery options you set in 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, and fax.

Using Delivery Types

The type of delivery you select determines the destination of the extract. Some delivery types require additional information. You can select Documents of Record as the delivery mode to store the output in the database and allow employees to view the output from document of records. An example of a document is a payslip. 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 receive an email with their payslip, then set the bursting node to Employee_ID. Select the WebCenter Content delivery type to create extracts with encrypted or non-encrypted data and transmit them to Oracle WebCenter Content. You can then transfer the data manually or using your own scripts to your own server. For more information, see Oracle Fusion File Transfer Automation and Data Security on My Oracle Support.

Defining the BI Publisher Template: Worked Example

This example demonstrates how to set up a BI Publisher template and make it available for you to create a document in the required format, suing the extracted data. You access BI Publisher using the Reports and Analytics link from the navigator. When the Reports and Analytics page is accessible, you can browse through the catalog.

FAST bank must send an XML file on their employees and departments to a third party and the HR Manager with employee details grouped by departments as a Headcount Report.

The following table summarizes some key decisions:

Decisions to Consider In this Example

Why do I have to set up BI Publisher?

You set up BI Publisher to format the extracted XML data into the required format and to deliver the report or business document to the appropriate destination.

How does BI Publisher connect to HCM extracts?

You install the Template Builder for Word plug-in.

Do I need a separate license?

No, BI Publisher is included with Core HR.


  1. Setting up the Data Model

  2. Creating the BI Publisher Report Template

  3. Uploading the Report

Setting up the Data Model

  1. Select the Reports and Analytics option from the navigator.

  2. Select the Browse Catalog icon.

  3. Select the New menu option, and under the Published Reporting section, select Report.

  4. Select the Use the existing data model option to create a report using the existing data model.

  5. In the window that displays, select the data model and select Next.

  6. Select the Report Editor option and select Finish to complete the data model setup.

  7. Save the report as HR Data Report in the following folder: /Shared Folders/Custom/Human Capital Management/

Creating the BI Publisher Report Template

  1. Install the Template Builder for Word plug-in from the BI Publisher Enterprise Home page.

    This plug-in provides sample documents, demos, templates and Template Viewer. Use these samples to help you understand the concepts and to create templates such as EFT and RTF to view the formatted XML output.

  2. Create a BI Publisher template using the Export XSD option in the extract execution tree.

  3. Open MS Word and locate the Add-Ins tabbed region to view the Oracle BI Publisher option.

  4. Select the Load XML Schema option. After the XML schema has loaded, you can arrange the fields and alter the layout, if required.

Uploading the Report

  1. Select the Upload option from the Upload or Generate Layout region and enter the following information:

    Parameters Details

    Layout Name

    RTF Layout

    Template File

    Select the file name of the RTF template saved on your local drive.


    RTF Template


    English (United States)

  2. Save the BI report in the following location: /Shared Folders/Custom/Human Capital Management/.

    This BI Publisher report template is now ready for you to select in the Delivery Options section when creating the extract definition.

Delivering Encrypted Data Using HCM Extracts: Explained

You can deliver encrypted files using HCM Extracts to Oracle WebCenter Content. You use the WebCenter Content delivery type in the Deliver page of the Manage Extract Definitions task to create an encrypted extract. HCM Extract transfers the encrypted file to Oracle WebCenter Content. You can then transfer the files manually, or write scripts to perform the transfer. By selecting the WebCenter Content delivery type you can enter additional details to ensure the extract is identifiable in Oracle WebCenter Content. For example, specify the integration name, select whether you want to encrypt the file using the encryption mode, and a file name for your reference. This additional setup ensures the document has the correct encryption mode and is stored in Oracle WebCenter Content with a specific file name. For more information, see Oracle Fusion File Transfer Automation and Data Security on My Oracle Support.

FAQs for Filtering and Delivering HCM Extracts

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 a threading database item?

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. Generally, for Pay Employee user entity and Assignment user entity it would be DBI with %ASSIGNMENT%ID. For Person user entity, 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?

You can't exclude or override a record that doesn't suit your requirements with your own record by using an exclusion rule. The extract process doesn't process excluded records based on the legislative data group.

What is extract data group filtering?

Extract data group filtering filters data extracted in the data group. You can specify to filter data group as an expression or as fast formula, and you can choose one or both. You can build criteria using the available database items, parameters and operators (conditional and logical). The filter criteria will be more efficient as it gets appended to the User Entity SQL at the time of execution. If you can't specify the criteria as an expression, then you can place logic inside a fast formula and select here. Fast formulas return values of Y or N to indicate if you must extract the record or not. If both criteria and formula are specified, then both the conditions are applied.

What are extract data group criteria?

You can specify the filter conditions of what data you want to archive by using the data group criteria. You can specify the filter conditions as an expression or fast formula.

Can I use Oracle Fusion Transactional 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.

What's BI Publisher and how does it work with HCM extracts?

BI Publisher is a set of tools you use to create highly-formatted reports based on data models. With BI Publisher, you can:

  • Author, manage, and deliver documents

  • Create interactive management reports

  • Create highly-formatted, customer-facing documents

  • Create government documents

  • Create electronic funds transfer (EFT) documents

BI Publisher transforms the extracted data from the database and presents that data into a report.

How can I disable bursting?

To disable the bursting feature in the BIP report and deliver a consolidated output:

  • Select None as the delivery type on the Deliver page when creating a delivery option for the HCM extract.

  • Select Disable in the Bursting field to disable the bursting options associated with the BIP template.

How can I reduce the size of my extract output?

In the Delivery tab, set the Compress option as Yes. You can use the compress feature with the WebCenter Content delivery option only. This feature extracts a compressed output, which helps to speed up large file transfers, uploading, and downloading. Compressing the extract output uses less disk space too.

Does a delivery option have to complete for an extract to run successfully?

No. Use the Required option in the Extract Delivery Option table to specify which delivery options you need to run successfully in order for the extract run to succeed. For example, use the Required option for critical delivery options such as payroll bursting. If this delivery option fails, then the extract run will also fail because it is dependent on the delivery option completing successfully.

You can also use the Required option for any delivery options you deem as not critical for the extract to run successfully. For example, deselect the Required option for a delivery option such as an email notification to yourself. If this delivery option fails, then the extract run will complete successfully because it is not dependent on the delivery option.