Search for:
Feb 6 2024
HDL – Sample file to end date element entry value

All recurring element entires are loaded/ created with an effective end date of 31-Dec-4712. But I have seen scenarios, where the business have requirements to end date an element entry as of a certain date. To achieve this in Bulk, one can use ElementEntry.dat HDL business object.

Let us take an example, where an employee has an element entry with an effective end date of 31-Dec-4712:

The effective end date is set to blank which is equivalent to 31-Dec-4712 in the backend table.

So, let us assume the business has requested to end date this particular element as of 31-Jan-2024 for all employees. To achieve, this we need to pull the existing data from element entries table for this particular element. Below SQL can be used to get the ID values and to verify the results before and after HDL load:

SELECT DISTINCT peevf.element_entry_value_id
       ,peef.element_entry_id
       ,petf.base_element_name
       ,peef.effective_start_date ele_sd
       ,peef.effective_end_date ele_ed
       ,peevf.effective_start_date
       ,peevf.effective_end_date
       ,paam.assignment_number
   FROM per_all_assignments_m paam
       ,pay_element_types_f petf
       ,pay_element_entries_f peef
       ,pay_element_entry_values_f peevf
 WHERE 1=1
   AND paam.person_id = peef.person_id
   AND peef.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 = 'Test XYZ Bonus'
   AND paam.assignment_number = 'E2121212'
   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

A normal HDL file with new effective end date will just create a date split in the data. To avoid this a new attribute called ReplaceLastEffectiveEndDate should be added in the HDL file which will updated the effective end date from 31-Dec-4712 to 31-Jan-2024.

METADATA|ElementEntry|AssignmentNumber|ElementName|EffectiveStartDate|EffectiveEndDate|LegislativeDataGroupName|MultipleEntryCount|EntryType|ReplaceLastEffectiveEndDate

MERGE|ElementEntry|E2121212|Test XYZ Bonus|2012/01/31|2024/01/31|GB Legislative Data Group|1|E|Y

Once this HDL is run successfully, the effective end date will get updted.

Mandeep Gupta 0 Comments
Nov 20 2021
HDL – Sample file to delete element eligibility

HDL 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/01

Sometime 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

Mandeep Gupta 0 Comments
May 11 2021
BIP – Query to get element entry details
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

Mandeep Gupta 0 Comments