Element Input Values
SELECT pett.element_type_id
,pivf.input_value_id
,petf.base_element_name
,pett.element_name
,pett.reporting_name
,TO_CHAR(petf.effective_start_date, 'YYYY/MM/DD') ele_effective_start_date
,TO_CHAR(petf.effective_end_date, 'YYYY/MM/DD') ele_effective_end_date
,pldgv.name legislative_data_group
,pldgv.legislation_code
,petf.processing_type
,hr_general.decode_lookup('PAY_PROCESSING_TYPE',petf.processing_type) processing_type_description
,pivf.base_name input_value_name
,TO_CHAR(pivf.effective_start_date, 'YYYY/MM/DD') iv_effective_start_date
,TO_CHAR(pivf.effective_end_date, 'YYYY/MM/DD') iv_effective_end_date
,pivf.display_sequence
,pivf.uom
,hr_general.decode_lookup('PAY_UNITS', pivf.uom) uom_meaning
,pivf.user_display_flag
,pivf.user_enterable_flag
,pivf.mandatory_flag
,pivf.generate_db_items_flag
,pivf.default_value
,pivf.lookup_type
,hr_general.decode_lookup(pivf.lookup_type, pivf.default_value) default_value_meaning
,pivf.hot_default_flag apply_default_at_runtime
,pivf.min_value
,pivf.max_value
,pivf.warning_or_error
,pivf.value_set_code
,pivf.vo_name validation_source
,pivf.context_id
,(SELECT display_context_name
FROM pay_context_usages_vl
WHERE context_id = pivf.context_id
AND legislation_code = pldgv.legislation_code
) reference
,pivf.reserved_input_value
,hr_general.decode_lookup('PAY_RESERVED_INPUT_VALUE', pivf.reserved_input_value) special_purpose
,pivf.retro_static_flag
,pivf.force_rrv_mode
,pivf.formula_id
,(SELECT base_formula_name
FROM ff_formulas_vl
WHERE formula_id = pivf.formula_id
) validation_formula
,pivf.rate_formula_id
,(SELECT base_formula_name
FROM ff_formulas_vl
WHERE formula_id = pivf.rate_formula_id
) rate_formula
,pivf.validation_override_message
FROM pay_element_types_f petf
,pay_element_types_tl pett
,per_legislative_data_groups_vl pldgv
,pay_input_values_f pivf
WHERE petf.element_type_id = pett.element_type_id
AND pett.language = USERENV ('LANG')
AND petf.legislative_data_group_id = pldgv.legislative_data_group_id(+)
AND petf.element_type_id = pivf.element_type_id
AND TRUNC(:P_AS_OF_DATE) BETWEEN petf.effective_start_date AND petf.effective_end_date
AND TRUNC(:P_AS_OF_DATE) BETWEEN pivf.effective_start_date AND pivf.effective_end_date
AND (
(:P_CREATION_TYPE= 'Custom' and pivf.created_by <> 'SEED_DATA_FROM_APPLICATION' )
OR (:P_CREATION_TYPE = 'Seeded' and pivf.created_by = 'SEED_DATA_FROM_APPLICATION' )
OR (:P_CREATION_TYPE = 'Seeded Modified' and pivf.created_by = 'SEED_DATA_FROM_APPLICATION' and pivf.last_updated_by <> 'SEED_DATA_FROM_APPLICATION')
OR (:P_CREATION_TYPE = 'ALL')
)
ORDER BY petf.base_element_name
,pivf.display_sequence
