In this article, we will look into extracting the Element Configuration information. Elements are the basic building blocks for Payroll and they will also be used in other modules like Core HR, Benefits, Compensation, Absence, and Time and Labor.
Do you know how to extract the Element Configuration information like Element Name, ID, Classification, Legislation, Processing Level, Gross Up, Multiple Entries Allowed, Retro, Proration, Skip Rules, Validation Formulas and Processing priority, etc?
SELECT pett.element_type_id
,petf.base_element_name
,pett.element_name
,pett.reporting_name
,TO_CHAR(petf.effective_start_date, 'YYYY/MM/DD') effective_start_date
,TO_CHAR(petf.effective_end_date, 'YYYY/MM/DD') effective_end_date
,pett.description
,pec.base_classification_name prim_base_classification
,pect.classification_name prim_classification_name
,pec_sec.base_classification_name sec_base_classification
,pect_sec.classification_name sec_classification_name
,pldgv.name legislative_data_group
,pldgv.legislation_code
,petf.standard_link_flag
,DECODE(petf.standard_link_flag, 'Y', 'Standard', NULL) category
,petf.processing_type
,hr_general.decode_lookup('PAY_PROCESSING_TYPE',petf.processing_type) processing_type_description
,petf.use_at_rel_level
,petf.use_at_term_level
,petf.use_at_asg_level
,petf.adjustment_only_flag
,petf.closed_for_entry_flag
,petf.indirect_only_flag accept_result_frm_formula_only
,CASE WHEN (petf.processing_type = 'R' AND petf.standard_link_flag = 'Y')
THEN 'Y'
ELSE 'N'
END automatic_entry
,petf.multiple_entries_allowed_flag
,petf.additional_entry_allowed_flag
,petf.process_in_run_flag
,petf.once_each_period_flag
,petf.processing_priority
,ptd_start.short_name earliest_entry
,ptd_start.definition_name earliest_entry_description
,ptd_end.short_name latest_entry
,ptd_end.definition_name latest_entry_description
,petf.grossup_flag
,petf.process_mode
,hr_general.decode_lookup('PAY_RUN_METHOD', petf.process_mode) process_mode_description
,petf.recalc_event_group_id
,(SELECT base_event_group_name
FROM pay_event_groups
WHERE event_group_id = petf.recalc_event_group_id
) retro_calc_group_name
,petf.proration_group_id
,(SELECT base_event_group_name
FROM pay_event_groups
WHERE event_group_id = petf.proration_group_id
) proration_group_name
,petf.proration_formula_id
,(SELECT base_formula_name
FROM ff_formulas_vl
WHERE formula_id = petf.proration_formula_id
) proration_formula
,petf.input_currency_code
,petf.output_currency_code
,petf.formula_id skip_formula_id
,(SELECT base_formula_name
FROM ff_formulas_vl
WHERE formula_id = petf.formula_id
) skip_rule
,petf.defaulting_formula_id
,(SELECT base_formula_name
FROM ff_formulas_vl
WHERE formula_id = petf.defaulting_formula_id
) default_formula
,petf.calculation_formula_id
,(SELECT base_formula_name
FROM ff_formulas_vl
WHERE formula_id = petf.calculation_formula_id
) calculation_formula
,petf.validation_formula_id
,(SELECT base_formula_name
FROM ff_formulas_vl
WHERE formula_id = petf.validation_formula_id
) validation_formula
,petf.validation_override_message
,petf.iterative_flag
,petf.iterative_formula_id
,(SELECT base_formula_name
FROM ff_formulas_vl
WHERE formula_id = petf.iterative_formula_id
) iterative_formula
,petf.iterative_priority
,petf.deduction_type_id
,(SELECT deduction_code
FROM pay_deduction_types_vl
WHERE deduction_type_id = petf.deduction_type_id
) deduction_type
,petf.deduction_or_exemption
FROM pay_element_types_f petf
,pay_element_types_tl pett
,pay_ele_classifications pec
,pay_ele_classifications pec_sec
,pay_ele_classifications_tl pect
,pay_ele_classifications_tl pect_sec
,per_legislative_data_groups_vl pldgv
,pay_time_definitions ptd_start
,pay_time_definitions ptd_end
WHERE petf.element_type_id = pett.element_type_id
AND pett.language = USERENV ('LANG')
AND petf.classification_id = pec.base_classification_id
AND pec.legislation_code = pldgv.legislation_code
AND pec.classification_id = pect.classification_id
AND pect.language = USERENV ('LANG')
AND petf.secondary_classification_id = pec_sec.classification_id (+)
AND petf.secondary_classification_id = pect_sec.classification_id (+)
AND pect_sec.language (+) = USERENV ('LANG')
AND petf.legislative_data_group_id = pldgv.legislative_data_group_id(+)
AND petf.starting_time_def_id = ptd_start.time_definition_id(+)
AND petf.ending_time_def_id = ptd_end.time_definition_id(+)
AND TRUNC(SYSDATE) BETWEEN petf.effective_start_date AND petf.effective_end_date
ORDER BY petf.base_element_name
