SELECT pett.element_name, count(peef.element_type_id)
FROM pay_element_types_tl pett
,pay_element_entries_f peef
WHERE pett.element_type_id = peef.element_type_id
AND pett.language = 'US'
AND to_date('01-04-2022','DD/MM/RRRR') BETWEEN peef.effective_start_date AND peef.effective_end_date
GROUP BY pett.element_nameHDL supports deletion of element eligibilities. You can make use of PayrollElementDefinition business object. Child object ElementEligibility supports create, update and delete operations.
You can create a simple BIP query to extract the requisite data and create an HDL file out of it:
select pelf.element_link_id
,petf.element_type_id
,petf.base_element_name
,pelf.effective_start_date
from pay_element_types_f petf
, pay_element_links_f pelf
where petf.element_type_id = pelf.element_type_id
and element_link_id=300000175215375
and TRUNC(sysdate) BETWEEN petf.effective_start_date and petf.effective_end_date Sample HDL file:
METADATA|ElementEligibility|ElementLinkId|ElementId|EffectiveStartDate
DELETE|ElementEligibility|300000175215375|300000175200627|1951/01/01Sometime you may get an error that You can’t delete element eligibility as element entries exist for this eligibility. In this case first you need to identify the corresponding element entries and Delete those and then retry deleting element eligibility.
Please check mos note – When Attempting to Delete Element Eligibility Get Error ‘The element eligibility record can’t be deleted because it would invalidate existing element entries with effective start dates in the future. (PAY-1635756)’ (Doc ID 2686914.1) for sample query and sample file for element entry deletion.
Version Validated : 21C
Select DISTINCT peevf.element_entry_value_id
,peef.element_entry_id
,petf.base_element_name
,peevf.effective_start_date
,paam.assignment_number
,pivf.base_name
from per_all_assignments_m paam
,pay_element_types_f petf
,pay_element_entries_f peef
,pay_element_entry_values_f peevf
,pay_input_values_f pivf
where 1=1
and paam.person_id = peef.person_id
and peef.element_type_id = petf.element_type_id
and pivf.element_type_id = petf.element_type_id
and peef.element_entry_id = peevf.element_entry_id
and paam.ASSIGNMENT_TYPE in ('E')
and paam.primary_assignment_flag = 'Y'
and petf.base_element_name = 'Dental Plan'
and pivf.base_name = 'Amount'
and paam.assignment_number = 'E1111'
and trunc(sysdate) between petf.effective_start_date and petf.effective_end_date
and trunc(sysdate) between paam.effective_start_date and paam.effective_end_date
and trunc(sysdate) between pivf.effective_start_date and pivf.effective_end_date