In this article, we will look into fetching the Eligibility Profiles and the criteria setup for Eligibility Profiles from backend tables.
We attach Eligibility Profiles in multiple modules like Absences, Benefits, Performance Management and OTL etc.
SQL Query to find Eligibility Profiles:
select * from BEN_ELIGY_PRFL
Now coming to the Eligibility Criteria attached to these Eligibility profiles, this information can be found in BEN_ELIG_GEN_CRIT_PRTE table.
ELIG_CRITERIA_CODE determines which specific criteria that record belongs to. The Lookup for this field is BEN_INELG_RSN and below is the list of values and their meanings for the US language.
LOOKUP_CODE | MEANING |
AGE | Age |
AGL | Combination of age and length of service |
ASS | Assignment set |
AST | Assignment type |
BGR | Benefits group |
BRG | Bargaining unit |
CMP | Compensation level |
COA | Collective agreement |
CON | Continuing participation |
CVP | Covered by plan |
DEG | Designator enrolled in plan |
DLG | Dependent eligibility |
DSB | Disabled |
EAI | Enrolled in other plan in program |
EBU | Business Unit |
ECQ | COBRA qualified beneficiary |
ECT | Disability category |
ECY | Competency |
EDD | Disability degree |
EDG | Dependent covered by other program |
EDI | Depended covered by other plan in program |
EDP | Dependent covered by other plan |
EDR | Disability reason |
EDT | Dependent covered by other plan type in program |
EEG | Enrolled in other program |
EEI | Enrolled in other option in plan |
EEP | Enrolled in other plan |
EET | Enrolled in other plan type in program |
EGN | Gender |
EHC | Health coverage selected |
ELV | Leaving reason |
EMP | Participant marital status |
ENO | No other coverage |
EOM | Opted to participate in Medicare |
EOP | Participation in another plan |
EOY | Eligible for another plan type in program |
EPE | Enrollment period expired |
EPN | Probation period |
EPS | Position |
EQG | Quartile in grade |
EQT | Qualification title |
ERG | Performance rating |
ERL | Formula outcome |
ESP | Special ceiling progression |
EST | Supplementary role |
ETC | Total coverage volume |
ETD | Dependent eligible for other plan type in program |
ETP | Total participants |
ETU | Tobacco use |
EVT | Life event |
FMM | Family member |
FPT | Full-time or part-time |
GEO | Home Location |
GRD | Grade |
HRD | Hire Date |
HRS | Hours worked |
JBF | Job Function |
JFM | Job Family |
JOB | Job |
LBR | Labor union |
LGL | Legal entity |
LOA | Leave of absence |
LOC | Work location |
LOS | Length of service |
MGR | Request of manager |
MIL | Military status |
MNG | People Manager |
MRT | Marital status |
ORG | Organization |
OTH | Other |
OVR | Manual override |
PBS | Pay basis |
PEO | People group |
PFQ | Pay frequency |
PFT | Full-time percentage |
PLN | Eligible for another plan |
POE | Reached period of enrollment |
PTP | Person type |
PYR | Payroll |
RBC | User Defined |
REL | Relationship type |
RLG | Religion |
SHR | Range of scheduled hours |
STA | Assignment status |
STU | Student status |
SVC | Service area |
UNN | Union Eligibility |
ZIP | Postal code |
Based on this lookup code BEN_INELG_RSN value in BEN_ELIG_GEN_CRIT_PRTE table, further values and criteria will be stored.
Ex: If we are defining the criteria by Business Unit, then ELIG_CRITERIA_CODE will be “EBU” and the actual Business Unit values will be stored in CRIT_CHAR1 field. We need to join with the Business Units config table FUN_ALL_BUSINESS_UNITS_V to get the Business Units name.
SQL Query to fetch Eligibility Criteria:
select a.name, hr_general.decode_lookup ('BEN_INELG_RSN', b.elig_criteria_code) elig_criteria_code,
decode(excld_flag,'N','Include','Y','Exclude') INCLUDE_EXCLUDE, bu.bu_name
from BEN_ELIGY_PRFL a, ben_elig_gen_crit_prte b, FUN_ALL_BUSINESS_UNITS_V bu
where a.eligy_prfl_id = b.eligy_prfl_id
and b.elig_criteria_code = 'EBU'
and b.crit_char1 = bu.bu_id
and sysdate between b.start_date and b.end_date
Similarly, for Job Family Criteria, the ELIG_CRITERIA_CODE would be “JFM” and the config table to join would be per_job_family_f_vl
select a.name, hr_general.decode_lookup ('BEN_INELG_RSN', b.elig_criteria_code) elig_criteria_code,
decode(excld_flag,'N','Include','Y','Exclude') INCLUDE_EXCLUDE, jfm.job_family_name
from BEN_ELIGY_PRFL a, ben_elig_gen_crit_prte b, per_job_family_f_vl jfm
where a.eligy_prfl_id = b.eligy_prfl_id
and b.elig_criteria_code = 'JFM'
and b.crit_char1 = jfm.job_family_id
and sysdate between b.start_date and b.end_date
And then there are additional criteria tables for Age, Assignment Sets, Benefit Groups, Bargaining Unit, Disability Category, etc. and this information is stored in BEN_ELIG_%_PRTE tables like BEN_ELIG_AGE_PRTE, BEN_ELIG_ASNT_SET_PRTE, BEN_ELIG_BENFTS_GRP_PRTE, BEN_ELIG_BRGNG_UNIT_PRTE, etc. These tables can be joined with the Eligibility Profiles table BEN_ELIGY_PRFL.
Hope this helps you get a good idea on Eligibility profiles.
If you like the content, please follow us on LinkedIn, Facebook, and Twitter to get updated with the latest content.