Search for:
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
Nov 19 2021
BIP – Query to extract element eligibility details
SELECT fff.formula_id

   ,fff.formula_type_id

   ,fff.formula_name

   ,fff.creation_date

   ,fftt.formula_type_name

   ,petft.base_element_name "result rules element_name"

 ,pfrrf.element_type_id "result rules element_id"

   ,pfrrf.result_name

   ,pfrrf.result_rule_type

 ,petft_spr.base_element_name "status proc rules element_name"

   ,psprf.element_type_id "status proc rules element_id"

   ,pivf.base_name

 FROM PAY_FORMULA_RESULT_RULES_F pfrrf

   ,PAY_STATUS_PROC_RULES_F psprf

   ,FF_FORMULAS_F fff

   ,FF_FORMULA_TYPES_TL fftt

   ,PAY_ELEMENT_TYPES_F petft

   ,PAY_ELEMENT_TYPES_F petft_spr

,PAY_INPUT_VALUES_F pivf

WHERE pfrrf.STATUS_PROCESSING_RULE_ID = psprf.STATUS_PROCESSING_RULE_ID

 AND fff.FORMULA_TYPE_ID = fftt.FORMULA_TYPE_ID

 AND fftt.LANGUAGE = 'US'

 AND fff.FORMULA_ID = psprf.FORMULA_ID  

 AND petft_spr.ELEMENT_TYPE_ID = psprf.ELEMENT_TYPE_ID

 AND petft.ELEMENT_TYPE_ID = pfrrf.ELEMENT_TYPE_ID

 AND petft.ELEMENT_TYPE_ID = pivf.ELEMENT_TYPE_ID

 AND pfrrf.INPUT_VALUE_ID = pivf.INPUT_VALUE_ID

 AND TRUNC(SYSDATE) BETWEEN pfrrf.EFFECTIVE_START_DATE AND pfrrf.EFFECTIVE_END_DATE

 AND TRUNC(SYSDATE) BETWEEN psprf.EFFECTIVE_START_DATE AND psprf.EFFECTIVE_END_DATE

 AND TRUNC(SYSDATE) BETWEEN fff.EFFECTIVE_START_DATE AND fff.EFFECTIVE_END_DATE

 AND TRUNC(SYSDATE) BETWEEN petft.EFFECTIVE_START_DATE AND petft.EFFECTIVE_END_DATE

 AND TRUNC(SYSDATE) BETWEEN petft_spr.EFFECTIVE_START_DATE AND petft_spr.EFFECTIVE_END_DATE

 AND TRUNC(SYSDATE) BETWEEN pivf.EFFECTIVE_START_DATE AND pivf.EFFECTIVE_END_DATE

 --AND RESULT_RULE_TYPE = 'I'

 AND fff.formula_name = 'BASIC SALARY UK EARNINGS'
Mandeep Gupta 0 Comments