Filter Extracts Using Advanced Filter Criteria

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 might not be enough 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.

2. Open the extract definition you created and select the Design tab.

3. Select the Data Group link from the hierarchy, click the Add icon and select Data Group.

4. In the Data Group section, search the user entity PER_EXT_SEC_PERSON_UE and click Advanced to display the User Entity Details window.

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

6. In the Data Group section, now select the Filters tab to display the Filter Criteria section.

7. Click the Add icon.

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

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

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

11. 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
    where
    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 to retrieve any parameter value at runtime, use the 'pay_report_utils.get_parameter_value_date('ESS PARAMETER')' function. The ESS Parameter is typically, unless explicitly changed, 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 can't use more than 2000 characters to write the SQL query.

12. To validate the SQL construct for any syntactical errors, click Validate.

13. Click OK.

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