You are currently viewing SQL Query to extract Eligibility Profiles and the Eligibility Criteria
SQL Query to extract Eligibility Profiles and the Eligibility Criteria

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
image 12 - SQL Query to extract Eligibility Profiles and the Eligibility Criteria

Now coming to the Eligibility Criteria attached to these Eligibility profiles, this information can be found in BEN_ELIG_GEN_CRIT_PRTE table.

image 13 1024x374 - SQL Query to extract Eligibility Profiles and the Eligibility Criteria

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_CODEMEANING
AGEAge
AGLCombination of age and length of service
ASSAssignment set
ASTAssignment type
BGRBenefits group
BRGBargaining unit
CMPCompensation level
COACollective agreement
CONContinuing participation
CVPCovered by plan
DEGDesignator enrolled in plan
DLGDependent eligibility
DSBDisabled
EAIEnrolled in other plan in program
EBUBusiness Unit
ECQCOBRA qualified beneficiary
ECTDisability category
ECYCompetency
EDDDisability degree
EDGDependent covered by other program
EDIDepended covered by other plan in program
EDPDependent covered by other plan
EDRDisability reason
EDTDependent covered by other plan type in program
EEGEnrolled in other program
EEIEnrolled in other option in plan
EEPEnrolled in other plan
EETEnrolled in other plan type in program
EGNGender
EHCHealth coverage selected
ELVLeaving reason
EMPParticipant marital status
ENONo other coverage
EOMOpted to participate in Medicare
EOPParticipation in another plan
EOYEligible for another plan type in program
EPEEnrollment period expired
EPNProbation period
EPSPosition
EQGQuartile in grade
EQTQualification title
ERGPerformance rating
ERLFormula outcome
ESPSpecial ceiling progression
ESTSupplementary role
ETCTotal coverage volume
ETDDependent eligible for other plan type in program
ETPTotal participants
ETUTobacco use
EVTLife event
FMMFamily member
FPTFull-time or part-time
GEOHome Location
GRDGrade
HRDHire Date
HRSHours worked
JBFJob Function
JFMJob Family
JOBJob
LBRLabor union
LGLLegal entity
LOALeave of absence
LOCWork location
LOSLength of service
MGRRequest of manager
MILMilitary status
MNGPeople Manager
MRTMarital status
ORGOrganization
OTHOther
OVRManual override
PBSPay basis
PEOPeople group
PFQPay frequency
PFTFull-time percentage
PLNEligible for another plan
POEReached period of enrollment
PTPPerson type
PYRPayroll
RBCUser Defined
RELRelationship type
RLGReligion
SHRRange of scheduled hours
STAAssignment status
STUStudent status
SVCService area
UNNUnion Eligibility
ZIPPostal 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
image 15 - SQL Query to extract Eligibility Profiles and the Eligibility Criteria

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
image 14 - SQL Query to extract Eligibility Profiles and the Eligibility Criteria

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 LinkedInFacebook, and Twitter to get updated with the latest content.