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.
SELECT DISTINCT e.element_name,
e.base_element_name,
e.reporting_name,
e.description,
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,
l.legislation_code,
l.default_currency_code,
c.base_classification_name,
c.classification_name primary_classification,
sc.classification_name secondary_classification,
CASE
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_nameSample Output will look like:
