1. Implementing Global Payroll
  2. Create an Extract Definition for Payroll Balance Reporting

Create an Extract Definition for Payroll Balance Reporting

This topic demonstrates how you can create an extract definition to extract and report employee balances.

Before you create the extract definition you must create these payroll objects:
  1. Balance Group to include the balance definitions and dimensions you can use to retrieve balances for the employee.
  2. Balance Group Usage to represent how the balance group is used.

For step-by-step information on how to create a balance group and balance group usage, refer the Create Balance Groups and Usages topic on the Oracle Help Center.

Complete these tasks to create the extract definition and run the extract.
  1. Create a Balance Group
  2. Create a Balance Group Usage
  3. Create an Extract Definition
  4. Define the Delivery Options
  5. Submit the Extract

Create a Balance Group

Complete these steps to create a Balance Group.
  1. On the Home page, click the Balance Groups quick action under the My Client Groups tab.
  2. Click Create to open the Create Balance Group dialog box.
  3. Select a legislative data group and enter a name for the balance group, for example, Employee Balance ABC.
  4. Click Continue.
  5. On Balance Group Details page select Employee as the balance group level.
  6. Click Save.
  7. Select the Balance Definitions folder under the Balance Group Overview list.
  8. Click Select and Add.
  9. In the Select and Add: Balance Definitions dialog box, enter Gross Earnings in the Name field.
  10. Select All in the Match field and click Search.
  11. In the Results section, select the row that has, for example, the following values.
    • Dimension Name: Assignment Tax Unit Run
    • Category: Total Standard Earnings
  12. Click Apply and OK.

Create a Balance Group Usage

Complete these steps to create a Balance Group Usage.
  1. On the Home page, click the Balance Groups quick action under the My Client Groups tab.
  2. Click Create.
  3. In the Create Balance Group Usage dialog box, complete the fields as shown in this table.
    Field Description
    Legislative Data Group Enter the same LDG as the balance group for which you're creating the usage.
    Name Enter a name, for example, Employee voluntary deduction run balances.
    Balance Group The group you created in the previous task, for example Employee Balance ABC.
    Format Type Matrix
  4. Click Continue.

    You're returned to the Balance Group Usage Details page.

  5. In the Report Type field, select Statement of Earnings.
  6. Select the Matrix Items folder under the Balance Group Usage Overview list and add balance dimensions that you want.
    Note: Matrix Item node is enabled only for Matrix format type. Use this node to specify the display position of the balance value for a particular balance dimension. If you put several dimensions in the same position their values are summarized together in the group.
  7. Complete the fields as given here:
    1. Select Balance Dimensions as the Matrix Item Type.
    2. Search and select the Name as Assignment Tax Unit Run.
    3. Enter 1 in the Position field.
  8. Click Save.
  9. Select the Sorting folder under the Balance Group Usage Overview list.

    Sorting determines the order in which the balance values in a balance group are displayed.

  10. Click Actions > Create.
  11. Complete the fields as per your requirement. Sort Items table defines the static sort order in which the balance values in a balance group are displayed and it's enabled only for sort method of Static Order. It might be sort by either Balance Type or Defined Balance.
  12. Click Save and then Submit.

Create an Extract Definition

Use the Extract Definitions task in the Data Exchange area under My Clients Group to create your own extract definition to extract and report employee payroll balances.

Before you create the extract definition, consider the following:
  • Create one extract definition because you are creating only one report.
  • Search for and select a User Entity that has context to a Payroll Rel Action ID so that you can extract the balance results for the employee. If the value of Valid for Root Data Group is Yes, the user entity can be used as a root data group.

Complete these steps to create an extract definition.

  1. Navigate to the Data Exchange area under My Clients Group and select the Extract Definitions task.
  2. On the Extract Definitions page, click Add > Create New to open the Create Extract Definition page.
  3. Select Other Payroll Archive and enter the information given in this table to create the Extract Definition.
    Field Description
    Name Employee Balances Report

    The application uses this name to generate the XML output file.
    Start Date Enter an appropriate date.
    Consumer Select Report since the extract is used for reporting.
    Additional Details This field is left blank for reports.
    Legislative Data Group Select an appropriate LDG. Select the same LDG you have used to create the Balance Group and the Balance Group Usage.
    Note: For extracts based on Other Payroll Archive, the LDG is a required field.
  4. Click OK.

Create Extract Data Groups and Records

The next step is to create the Data Groups and Records.

When you create the Data Groups, the formulas are auto-generated every time you validate or compile the extract. The generated formula is specific to the user and the extract being validated or submitted. Hence leave the Formula field blank, it gets populated with the details of the auto-generated formula after you have submitted the extract definition.

i. Create the Work Relationships Data Group

  1. Click Design to create the data groups and records.
  2. Create the root data group with the following information.
    Field Description
    Name Work Relationships
    Tag Name Work_Relationships
    User Entity PER_EXT_WORK_RELATIONSHIP_ALL_UE
    Threading Database Item Extract Relationship Period Of Service Id
    Threading Action Type Relationship Action
  3. Click Save.
  4. Right-click the Work Relationships data group in the Object Name table and select Add Record.
  5. Enter this information to create a record.
    Field Description
    Name Person Details
    Tag Name Person_Details
    Type Header Record
    Process Type Fast Formula
  6. Click Save and Close.

ii. Create the Pay Relationship Actions Data Group

  1. Right-click the Work Relationships data group and select Add Child Data Group to create another data group.
  2. Enter this information to create the data group.
    Field Description
    Name Pay Relationship Actions
    Tag Name Pay_Relationship_Actions
    User Entity PAYROLL REL ACTIONS RANGE UE
    Threading Database Item Fast Formula
    Threading Action Type Relationship Action
    Include a user entity with a payroll relationship action because you are reporting employee payroll balance calculations from the payroll run results. A payroll run result is identified by the payroll relationship action. The User Entity has a context set to PAYROLL_REL_ACTION_ID.

  3. Click Save.

  4. Click the Pay Relationship Actions data group under the Object Name list and select the Connect Data Groups tab.

  5. Select Add to add a connection. Complete this information to create a data group connection.
    Field Value
    Parent Data Group Pay Relationship Actions
    Parent Data Group Database Item Extract Relationship Person Id
    Data Group Database Item PAYROLL REL ACTION PERSON IDENTIFIER
  6. Click Save.
  7. Right-click the Pay Relationship Actions data group in the Object Name table and select Add Record.
  8. Enter this information to create a record.
    Field Value
    Name Rel Action Details
    Tag Name Rel_Action_Details
    Type Detail Record
    Process Type Fast Formula
  9. Click Save and Close.

iii. Create the Balances Data Group

  1. Right-click the Pay Relationship Actions data group and select Add Child Data Group and enter this information create a data group.
    Field Value
    Name Balances
    Tag Name Balances_Data
    User Entity ORA_HRY_BALANCE_GROUP_USAGE_UE
  2. Click Save.
  3. Right-click the Balances data group in the Object Name table and select Add Record.
  4. Enter this information to create a record.
    Field Value
    Name Balance Details
    Tag Name Balance_Details
    Type Detail Record
    Process Type Balance Group
    Balance Group Usage Enter Name of Balance Group Usage you created for this report.
  5. Click Save and Close.

iv. Create the Run Results Data Group

  1. Right-click the Pay Relationship Actions data group and select Add Child Data Group and enter this information to create a data group.
    Field Value
    Name Run Results
    Tag Name Payroll_Run Results
    User Entity PAY_EXTRACT_RUN_RESULT_VALUES_UE
  2. Click Save.
  3. Right-click the Run Results data group in the Object Name table and select Add Record.
  4. Enter this information to create a record.
    Field Value
    Name Run Results Details
    Tag Name Run_ Results _Details
    Type Header Record
    Process Type Fast Formula
  5. Click Save and Close.
  6. Click the Run Results data group under the Object Name list and select the Connect Data Groups tab.
  7. Select Add to add a connection.
  8. Enter this information to create a data group connection.
    Field Value
    Parent Data Group Run Results
    Parent Data Group Database Item PAYROLL REL ACTION IDENTIFIER
    Data Group Database Item Run Result Payroll Relationship Action Id
  9. Click Save and Close.

v. Create the Assignments Data Group

  1. Right-click the Pay Relationship Actions data group and select Add Child Data Group and enter this information create a data group.
    Field Value
    Name Assignment
    Tag Name Employee_Assignment_Data
    User Entity PER_EXT_PAY_EMPLOYEES_V2_UE
  2. Click Save.
  3. Click the Assignments data group under the Object Name list and select the Connect Data Groups tab.
  4. Select Add to add a connection.
  5. Enter this information to create a data group connection.
    Field Value
    Parent Data Group Assignments
    Parent Data Group Database Item PAYROLL REL ACTION RELATIONSHIP IDENTIFIER
    Data Group Database Item Extract Employee V2 Payroll Relationship ID
  6. Right-click the Assignmentsdata group in the Object Name table and select Add Record.
  7. Enter this information to create a record.
    Field Value
    Name Assignment Details
    Tag Name Assignment_Details
    Type Header Record
    Process Type Fast Formula
  8. Click Save and Close.

Create Attributes

Attributes are the individual fields inside the extract record. An attribute is the lowest field level of an HCM extract and represents a piece of information, for example, person first name, person last name or person date of birth. This information is displayed in the process output file.

To retrieve the balance dimension values in the report, ensure the following:
  • Add a RUN dimension in the Balance Group Usage to make sure that the RUN attribute works. It can be any RUN dimension. For example, Assignment Tax Unit Run in the Balance Group Usage that we have created for this use case.
  • Add the attributes (data elements) in the Balances record as procedure elements.
  • When you create the attributes, enter 61 in the Output Column field for the first attribute. The value of the Output Column field of the subsequent attributes increases by 1. The Output Column values shouldn’t be duplicated.

Create attributes for each record as required.

Define the Delivery Options

Follow these steos to define a delivery option.
  1. Select the Deliver icon and then select Add to add a row for the delivery option you're about to create.
  2. Enter these details:
    • A value in the Delivery Option Name field. For example, Employee Balances Report in Excel.
    • A value in the Delivery Type field.
  3. Click OK and enter this information.
    Field Value
    Output Type Select a value, for example, Excel.
    Report Enter the location of the report output.
    Template Name Enter the name of the report template for this output type.
    Output Name Enter Name of the report output, for example, Employee Balances Report.
    Required Select this check box if this delivery option is a required option.
  4. Ensure you enter the additional information such as, the server, username, and password for the FTP delivery type.
  5. View the extract definition details and ensure the structure is valid in the Validate page by selecting the Validate button. Check the validation messages at the top of the page to refine your extract definition.
  6. Select Export XML Schema 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.
  7. Click Save. When you save the extract definition, the application generates a flow with the same name as your extract definition.

Submit an Extract

Use any of these options to submit the extract:
  • Submit Extracts task from the Data Exchange area. Use the View Extract Results task to view the results of the extract run.
  • Submit a Flow task under Payroll. Search for the flow with the same name as your extract definition and enter the requisite data and submit the flow. Use the process results pages to view the run results.

Related Topics