SQL Query to extract Eligibility Profiles and the Eligibility Criteria

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.