Payroll Element Configuration
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.base_element_name LIKE 'Deduction Refund%'
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 pect.classification_name = 'Absences'
AND TRUNC(:P_AS_OF_DATE) BETWEEN petf.effective_start_date AND petf.effective_end_date
AND (
(:P_CREATION_TYPE= 'Custom' and petf.created_by <> 'SEED_DATA_FROM_APPLICATION' )
OR (:P_CREATION_TYPE = 'Seeded' and petf.created_by = 'SEED_DATA_FROM_APPLICATION' )
OR (:P_CREATION_TYPE = 'Seeded Modified' and petf.created_by = 'SEED_DATA_FROM_APPLICATION' and petf.last_updated_by <> 'SEED_DATA_FROM_APPLICATION')
OR (:P_CREATION_TYPE = 'ALL')
)
ORDER BY petf.base_element_name
