38Filtering and Delivering HCM Extracts

This chapter contains the following:

You can create an extract to output data in different modes in the 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

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 Extract Definitions page, create an extract definition and use the Edit icon to open the extract in the Professional interface.

  2. Select the Data Group link from the Hierarchy 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 Fast Formulas UI from the Navigator Menu Payroll Calculation work area Clicking on the task 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.

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.

This example shows you how to set up a BI Publisher template and create a document in the required format, using the extracted data. You can find 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

Set 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/

Create 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.

Upload 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.

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

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.

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.

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.

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.

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.

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.

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.

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.

You create a BI Publisher template using the Export XML Schema option in the Extract Execution Tree UI and saving the file to your local computer. 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.

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 define the output file name?

When you create a delivery option for the extract, you can define the output file name by selecting an extract attribute and a date. The application adds this information as dynamic output to the Output Name and translates it internally into code.

The output name consists of 3 components:

  • Delivery option name (displays by default)

  • Extract attribute

  • Date

It's up to you which order they appear to make up the full output name.

You can use the compress feature to reduce the size of your output and encrypt your extracts more efficiently. Compressing the extract speeds up large file transfers, uploads, downloads, and uses less disk space. With the WebCenter Content delivery option, select a compression option that works for you:

  • Yes Compress First. This is the most efficient way to ensure the extract runs quicker. The compression results in a smaller file, which reduces the overall time it takes to transmit the file to its destination. With this option, the application compresses the file first and then encrypts it.

  • Yes Encrypt First. Select this option for the application to encrypt the file first and then compress it.

  • No. This option doesn't compress or encrypt the file.

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.

Why does my extract show a different time zone?

HCM Extracts uses the effective date to define the session date for a given extract run. The application uses this effective date to compare the effective start and effective end date attributes to filter the data it needs to process. The effective date (the session date) is the date set with the user time zone preferences. For example, if you run an extract with an effective date of 02-Jan-2018, with user time zone preference set to BST time zone, then the application retrieves the records created with an effective start date of 02-Jan-2018. Even if you create the data or perform a transaction on 01-Jan-2018 (or other dates), the effective start date here determines whether or not the transaction qualifies for the extract processing. The database stores the date with the time stamp in the UTC time zone for the attributes of the standard WHO columns. The date effective attributes (effective start date and effective end date used by the date effective feature in HCM Cloud) store the time without any time zone information.