You are currently viewing How to pull the Payroll Elements Listing Report using SQL Query?
SQL Query for Payroll Element Listing Report

In this article, we will look into an easy way to extract all elements configured in an application. They could have been created as part of any module like Payroll, Absence or Time and Labor.

Elements are the basic building blocks for the Payroll to function. And these elements are used across modules for achieving specific functionality in those respective modules.

Many times, we will look for a handy report to extract these elements to get an idea of the existing configuration to analyze what’s there in the system first before going ahead with creating new elements.

We can use the below query to extract the list of all elements. The query can be modified to include/exclude the Distributor, Calculator, Retro elements based on the need.

SQL Query:

SELECT DISTINCT e.element_name,
        e.processing_priority AS priority,
        e.multiple_entries_allowed_flag multiple_entries,
        e.process_in_run_flag process_in_payroll_run,
        e.once_each_period_flag process_once_per_period,
        e.standard_link_flag automatic_entry,
        nvl2(e.proration_group_id,'Yes', 'No') proration_group,
        nvl2(e.recalc_event_group_id, 'Yes','No') retroactive_event_group ,
        decode(e.process_mode,'Y','Yes','N','Element is processed and paid with other earnings',NULL)process_separately,
        (SELECT definition_name
           FROM pay_time_definitions ptd
          WHERE ptd.time_definition_id=e.starting_time_def_id
        ) start_time,
        (SELECT definition_name
           FROM pay_time_definitions ptd
          WHERE ptd.time_definition_id=e.ending_time_def_id
        ) end_time,
        e.grossup_flag gross_flag,
        e.additional_entry_allowed_flag additional_entry,
        decode(e.adjustment_only_flag,'N','No','Y','Yes','NA') balance_adjustment,
        decode(e.processing_type,'N','Non Recurring','R','Recurring','NA') processing_type,
        decode(e.closed_for_entry_flag,'N','No','Y','Yes','NA') closed_entry_flag,
        to_char(e.effective_start_date, 'YYYY-MM-DD') effective_start_date,
        to_char(e.effective_end_date, 'YYYY-MM-DD') effective_end_date,
        l.NAME legislative_data_group,
        c.classification_name primary_classification,
        sc.classification_name secondary_classification,
          WHEN (e.use_at_asg_level = 'Y'
                AND e.use_at_term_level = 'N'
                AND e.use_at_rel_level  = 'N'
          THEN 'Assignment Level'
          WHEN (e.use_at_asg_level = 'N'
                AND e.use_at_term_level = 'Y'
                AND e.use_at_rel_level  = 'N'
          THEN 'Term Level'
          WHEN (e.use_at_asg_level = 'N'
                AND e.use_at_term_level = 'N'
                AND e.use_at_rel_level  = 'Y'
          THEN 'Payroll relationship level'
          ELSE NULL
        END employment_level
   FROM pay_element_types_vl e,
        pay_element_types_f e1,
        pay_ele_classifications_vl c,
        pay_ele_classifications_vl sc,
        per_legislative_data_groups_vl l
  WHERE 1                                    = 1
    AND e.classification_id                  = c.classification_id
    AND e.secondary_classification_id        = sc.classification_id (+)
    AND sc.secondary_classification_flag (+) = 'Y'
  AND ((e.element_name NOT LIKE '%Earnings Calculator')
    AND (e.element_name NOT LIKE '%Earnings Distributor')
    AND (e.element_name NOT LIKE '%Earnings Results')
    AND (e.element_name NOT LIKE '%Retro')
    AND (e.element_name NOT LIKE '%Retro Results') ) 
    AND e.legislative_data_group_id = l.legislative_data_group_id
    AND sysdate BETWEEN trunc(e.effective_start_date) AND trunc(e.effective_end_date)
    --AND l.NAME IN (:p_ldg)
  ORDER BY l.NAME, e.element_name

Sample Output will look like:

image 1024x580 - How to pull the Payroll Elements Listing Report using SQL Query?
If you like the content, please follow us on LinkedInFacebook, and Twitter to get updated with the latest content.