Oracle Advanced

Benefits
Bala Krishna Koppuravuri
Principal consultant

Agenda
Day -1
=======
1) General Topics about Technical things
2) Whats in OAB ?
Day -2
=======
1) Benefit Structure
2) Eligibility
3) Linking Eligibility -- Participant, Dependent
Day -3
=======
1) Rates -- Elements, Derived Factors, Standard Rates, Variable Rates
Day -4
=======
1) Life Events
2) Electability & Defaults
3) Literature
Day -5
=======
1) Certification & Extracts

DAY - 1

Coding Standards
Proper Database Object naming Conventions and make sure you follow the client
standard when doing the naming
Prefix
ins_
upd_
del_
get_
chk_

Usage Context
Procedures whose primary purpose is to insert data
Procedures whose primary purpose is to update data
Procedures whose primary purpose is to delete data
Functions whose primary purpose is to retrieve data
Functions which return a Boolean value

Naming the input, output and Local Variables

Initialization of Local Variables
Do the anchor declaration as much as possible when declaring local variables
When there is a direct correlation between a variable and a table column,
the %TYPE or %ROWTYPE will be used.
Example:
DECLARE
l_wins team.wins%TYPE;

Coding Standards
Maintaining the Version history comments
Putting as many comments as you can for the readability of the code
Proper Alignment of code
Do not directly exit from the loops directly in case of nested loops, best practise is
exit one by one loop
Avoid using GOTO
Reusability of the codes either putting them in a package
Good Exception Handling
Self review the code
If possible maintain a code review check list which contains all the best practises

What is OAB?
Flexible, Rules-Based Benefits Administration
Application
Provides automation and ease of administration
from Life Event detection to Communication
Total Compensation Statement (TCS) bundled with
OAB

Why OAB?
Highly Configurable
Re-Usability
Automation
Integration with SSHR, Payroll and Core HR
Data Sharing

Where does OAB fit in EBS?

Oracle Human Resources Management Product
Family
Integrates seamlessly with other Human
Resources applications, including Core HR and
Payroll

Types Of Data
Indicative Data
Personal Information
Example: SSN, DOB
Assignment Data
Employment Information
Example: Organization, Employment Category
Enrollment Data
Benefits Information
Example: Medical enrollments, Insurance Coverage

Types Of Contacts
Child
Spouse
Domestic Partner
Domestic Partner Child
Company Couple*

Person Types
Employee
Ex-Employee
Participant
Dependent
Beneficiary
Survivors
Surviving Spouse
Surviving Family Member

Recap

DAY - 2

Quick Recap
Questions
1) Name few coding standards and best practises ?
2) What does fast formula contain ?
3) Name few tables for fast formulas ?
4) Whats the API to call a fast formula ?
5) Name few person types ?
6) Name few contact types ?
7) Name the types of Data available in the system ?
8) How does OAB integrate with other modules ?

Person Related Tables

Tables which gets effected when a new employee is created
per_all_people_f
Per_all_assignments_f
Per_person_type_usages_f
Per_periods_of_service
PER_ADDRESSES

COBRA
Consolidated Omnibus Budget Reconciliation Act
Signed by President Ronald Reagan
Gives some employees the ability to continue Health
Insurance Coverage after leaving employment
Coverage up to 18 months in most cases
Can vary from 29 to 36 months also
COBRA Subsidy Act signed by President Barrack Obama in
2009 giving 65% rebate on the Health Coverage Premium

HIPAA
Health Insurance Portability and Accountability Act
Signed by President Bill Clinton in 1996
Title I and Title II
Title I : Health Care Access, Portability, and
Renewability
Title II : Preventing Health Care Fraud and Abuse;
Administrative Simplification; Medical Liability Reform

Compensation Objects
Entities representing package of Benefits provided
Individual Compensation Objects
Programs
Plan Types
Plans
Options
Linked Compensation Objects
Plan Type in Program (PTIP)
Plan in Program (PLIP)
Option in Plan (OIPL)
Option in Plan in Program (OIPLIP)

Compensation Object Hierarchy

Program
Active
(Program)

Plan Type
Medical
(PTIP)

MetLife
Medical
Plan (PLIP)

Employee
Only (OIPL)

UHC
Medical
Plan (PLIP)
Employee +
Family Option
(OIPL)

Basic Life
Insurance
(PTIP)
LIC Life
Insurance
Plan (PLIP)

1 x Base
Pay (OIPL)

2 x Base
Pay (OIPL)

Plan

Order of Configuration
Plan Type
Option
Option in Plan Type
Plan
Program
Plan Type in Program (PTIP)
Plan in Program (PLIP)
Option in Plan (OIPL)
Option in Plan in Program (OIPLIP)

Plan Type (BEN_PL_TYP_F)

Options/Option In Plan Type

(BEN_OPT_F)

Designation Requirement
(BEN_DSGN_RQMT_F)

Plan (BEN_PL_F)

Program (BEN_PGM_F)

Benefit Structure Table Details

Important tables where the Benefit Structure details like
Program, Plan type, Plan, Option and other
combinations are stored
ben_pgm_f
ben_pl_typ_f
ben_pl_f
ben_opt_f
ben_ptip_f
ben_plip_f
ben_oipl_f

Eligibility & Electability

In general,
Eligibility can be defined as Satisfying certain conditions to
do, to receive something
Where as,
Electability can be defined as Option to choose from a list of
choices based on certain eligibility

Derived Factors
Used for working with data not stored explicitly in tables
Age, Length of Service, Combined Age and Length of Service
Bucket Value updated with every run of BENMNGLE
Derived Factor is Satisfied if bucket value is between
Minimum and Maximum limits
Eligibility Profile returns Yes if Derived Factor is Satisfied
Life Event is triggered if Derived Factor is not satisfied

Eligibility : Key Concepts

Derived factors (age, length of service,compensation,FTE,hours worked, benefits
balances, age and service)

Eligibility Profiles
Include/exclude functionality
Multiple values in one factor are separated by logical or/logical and not
Multiple values in multiple factors are separated by logical and
Can specify rules
Cannot do operational compares (this requires rules)

Eligibility : Key Concepts

Eligibility Profiles
Profiles and derived factors are reusable
Profiles can be attached at: program, plan, plan type in program, plan in
program, option in plan
Rules are always attached within a profile
Required vs. optional profiles
Evaluated from the top down
Dependent eligibility profiles (same concepts; EXCEPT can only be attached
at the program, plan type in program OR plan level)
Temporal BENMNGLE monitors time-based changes for participants;
Maintain designee eligibility monitors time-based changes for dependents

Eligibility : Things to Consider

Attach Eligibility at correct level to obtain desirable
results (Program, Plan, Option)
Use DERIVED FACTORS to monitor any time
based changes in your system (Age)

Eligibility
Eligibility is a Restriction
Eligibility Profiles are attached to Compensation Objects
They are used to control how to give eligibility for certain
compensation object
Can return either YES or NO
Different Criteria in Eligibility Profiles are linked by a logical AND
Different Values in the criteria are linked by logical OR
BEN:Eligible Profile Rule (AND/OR)
Rules are linked by logical AND/OR
ALL Mandatory and AT LEAST ONE Optional
Top Down approach while configuring

Derived Factor

DF at Eligibility

Types Of Eligibility

Participant Eligibility
Dependant Eligibility
Life Event Eligibility

Eligibility Criteria
1. Participant Eligibility Profile:
1. Personal: Gender, Service Area
2. Employment: Grade, Job, Position
3. Derived Factors: Age, Length Of Service
2. Dependent Criteria
1. Relation Ship
2. Age
3. Status Student, Disability
4. Service Area Group Of Zip Coded together called Service Area

Participant Eligibility
(BEN_ELIGY_PRFL_F)

Dependent Eligibility
(BEN_DPNT_CVG_ELIGY_PRFL_F)

Life Event Eligibility

(BEN_ELIG_TO_PRTE_RSN_F)

Eligibility Profiles Tables

Important tables
ben_eligy_prfl_f
ben_prtn_elig_f
ben_prtn_elig_prfl_f

Derived Factor Tables

Important tables
BEN_AGE_FCTR
BEN_LOS_FCTR
BEN_COMP_LVL_FCTR

Eligibility Profiles Tables

Important tables
ben_eligy_prfl_f
ben_prtn_elig_f
ben_prtn_elig_prfl_f

DAY - 3

Quick Recap
Questions
1)

What are compensation objects and name few with examples ?

2)

What is Eligibility, Electability ?

3)

What are Eligibility Profiles, Derived factors ?

4)

Can we add Derived factor directly to Eligibility in Program screen ?

5)

Whats the sequence of evaluation of Eligibility profiles Manditory and

optional ?

6)

Whats the approach taken when evaluating the profiles ?

7)

Name few types of Derived factors ?

8)

If I configure both at Eligibility level and LE level which one will take the
precedence ?

9)

Name few tables for eligibility profiles ?

10) Name few tables for Derived Factors ?

Coverage
Benefit Amount related to a Compensation Object
Basic Life Insurance, Optional Life Insurance
FSA, HSA
Not every Plan has a Coverage
Example: Mr. X has a Life Insurance Policy of $10,000

Coverage
(BEN_CVG_AMT_CALC_MTHD_F)

Premium
Amount charged by the Plan Vendor for providing the Plan
One Premium per Vendor for a Plan
May or May Not Be a multiple of Coverage
All Plans have Premiums

Premium (BEN_ACTL_PREM_F)

Rate
Amount paid by the Employer/Employee towards a Benefit
Premium = Employer Rate + Employee Rate
Pre-Tax, Post-Tax
Bottom Up approach while configuring
May or May not be a multiple of Coverage/Premium

Variable Rate Profile (VAPRO)

Used to calculate a Coverage/Premium/Rate which varies by
criteria
Consists of 2 parts: Rate and Eligibility criteria
VAPRO gets evaluated after the corresponding
Coverage/Premium/Rate
System stops evaluating further VAPROs as soon as it finds
the 1st VAPRO which satisfies the participant criteria
Criteria of VAPROs should be mutually exclusive

Variable Rate Profile

(BEN_VRBL_RT_PRFL_F)

Order of Evaluation
Coverage

VAPRO

Premium

VAPRO

Rate

VAPRO

Imputed Income
Section 79 of the US Internal Revenue Service Code
Tax that the recipient has to pay for certain forms of indirect
compensation termed as Fringe Benefits
Employee Life Insurance in excess of $50,000
Spouse + Dependent Life Insurance in excess of $ 2,000
Age Based
Amount per $1,000 of Fringe Benefit

Imputed Income

(BEN_ACTY_BASE_RT_F)

Standard Rates, Premiums, Variable Rate

profiles
ben_cvg_amt_calc_mthd_f
ben_actl_prem_f
ben_acty_base_rt_f
ben_acty_base_rt_f -- rt_usg_cd = 'IMPTDINC
ben_vrbl_rt_prfl_f

Standard Rates, Premiums, Variable Rate

profiles
ben_enrt_prem

DAY - 4

Quick Recap
Questions
1)

What are Coverage Calculations ?

2)

What are Variables Rate Profiles ?

3)

What are Premiums ?

4)

What are Standard Rates ?

5)

What are Variable Rates ?

6)

Where are all the Variable Rate profiles can be attached ?

7)

At which compensation object level do we attach the rates and premium ?

8)

What is Imputed Income ?

9)

How do you distinguish a standard rate and an imputed income both

functionally and technically ?

10) Name few tables for Coverage Calculations, Rates, Premiums ?

11) What is Pre-Tax and Post-Tax rates ?

What is a Life Event?

Events occurring in a participants Life that could potentially change his or
her benefits are Life events.

Gain Dependent,
New Hire

Long Term Disability,

Long Leave of
Absence

Death, Termination,
Retirement

Life Events
Life events transform Oracle Benefits from a static data
capture device into a responsive, proactive benefits
management tool
Based on database triggers, system can automatically detect
the life event
Example: New Hire, Address Change, Gain Dependent
Detected, Started, Processed, Unprocessed
Backing Out v/s Voiding
BENMNGLE detects and processes life events

Life Events (BEN_LER_F)

Life Events Detection

Life Events Types

Seeded COBRA Administration Life Events
Loss of Eligibility
Maximum Period of Enrollment Reached
Non or Late Payment
Voluntary End of Coverage
Period of Enrollment Changed
Miscellaneous Seeded Life Event Reasons
Reduction of Hours
Satisfied Waiting Period

Life Events Setup

Person Changes
Related Person Changes
Causes Related Person Life Event
Evaluation Rule
Seeded Events
Administrative
Open
Unrestricted

Collapsing Logic
2 or more Life Events with the same Occurred Date
Determines the resulting Life Event
Use AND/OR expressions
Tolerance Days
Based on the earliest life event occurred on date of the set of
potential life events under consideration

Collapsing Life Event

(BEN_CLPSE_LF_EVT_F)

Life Event Tables

Ben_ler_f
BEN_CSS_RLTD_PER_PER_IN_LER_F
ben_per_info_chg_cs_ler_f
ben_rltd_per_chg_cs_ler_f
BEN_CLPSE_LF_EVT_F

Electability
SUBSET of Eligibility
Evaluated when the Life Event is processed
Evaluated AFTER Eligibility
Electability depends on the Life Event being evaluated

Eligibility
Electability

Defaults
Default,
evaluated after
electability

Eligibility
Electability
Default

Electability,
evaluated after
Eligibility
Eligibility

Life Event Tables

Ben_ler_f
BEN_CSS_RLTD_PER_PER_IN_LER_F
ben_per_info_chg_cs_ler_f
ben_rltd_per_chg_cs_ler_f
BEN_CLPSE_LF_EVT_F

What are Enrollments?

Enrollments are opportunities to change or acquire new benefits

2.
En
roll
me
nt
Wi
nd
ow

The
Participant
informs
about his/her
marriage

Considering
the previous
example, the
Marriage
LE:
The
enrollment
period
opens for 30
days

3.
Ma
ke
En
roll
me
nt
Ch
an
ge
s

When LE
occurs
Enrollment
Window
opens

Participant
makes
changes to
existing
plans
Participant
adds the
spouse to
the existing
Medical
plans

1.
Op
en
En
roll
me
nt

Enrollment and Electable Choices tables

Ben_prtt_enrt_rslt_f
ben_elig_per_elctbl_chc

Enrollment Requirements
Window Period
Limitations
Post Election Edit Rule
Automatic v/s Explicit
Coverage Start/End Dates
Rate Start/End Dates
Reinstate
Levels of Enrollment Requirements
PLIP/Plan
PTIP
Program

Enrollement Requirements

Post Election Edit Rule (ben_pl/ptip_f)

Electability And Default (BEN_LER_CHG_PTIP_ENRT_F)

Dependant Coverage (BEN_LER_CHG_DPNT_CVG_F)

Communication Type
Sent to
Potential Benefits Participants
Current Benefits Participants
Former Benefits Participants
To communicate/inform about
Available Benefits
Rates
Scheduled Enrollment Periods
COBRA/HIPAA
Emerging Events

Communication (BEN_CM_TYP_F)

Communication Setup
Trigger
Usages
Life Event
Program
Plan
Plan Type
Delivery

Communication Trigger (BEN_CM_TYP_TRGR_F)

Communication Usages (BEN_CM_TYP_USG_F)

Communication Tables
BEN_CM_TYP_f
BEN_CM_TYP_USG_F
BEN_CM_TRGR
ben_cm_typ_trgr_f
BEN_CM_DLVRY_MED_TYP
BEN_PER_CM_F
BEN_PER_CM_PRVDD_F

DAY - 5

Quick Recap
Questions
1)

What is a Life Event and give few examples ?

2)

What is collapsing logic ?

3)

What are the ways of Life event detections ?

4)

What are Enrollments ?

5)

What are Scheduled life events ?

6)

What is the order of evaluation of default, eligibility, electability during the benmngle
run ?

7)

Whats the use of a Post Election Edit fast formula ?

8)

What is the type of fast formula you configure for electability and default ?

9)

What difference between backing out and voiding the LEs ?

10)

What are the different LE states ?

11)

What are communications ?

12)

Name few tables for Life Events, Enrollments and Communications ?

Certifications
Evidence Of Insurability
Generally used with Life Insurance Plans
Benefit/Coverage Restriction
Option Restriction
Interim and Suspended Enrollments
Action Items
EOI Received, Denied

Coverage Restriction (BEN_LER_BNFT_RSTRN)

Certification (BEN_ENRT_CTFN_F)

EOI Related Tables

BEN_ENRT_CTFN_F
BEN_LER_RQRS_ENRT_CTFN_F
BEN_LER_ENRT_CTFN_F
BEN_LER_RQRS_ENRT_CTFN_F
BEN_LER_BNFT_RSTRN_F
ben_bnft_rstrn_ctfn_f

Extracts
Classification Payroll, AP, Carrier, Letter
Used for reporting purposes
Types
600 Byte
ANSI
Extract Definition
Output Path
Post Processing Rule(s)
Criteria and Layout Linked here

Extracts Setup
Extract Definition
Extract Criteria
Who
What
Extract Layout
File Layout
Header
Detail
Trailer
Record Layout
Data Elements
Repeating Levels
Advanced Conditions

Extract Definition (BEN_EXT_DFN)

Extract Criteria (BEN_EXT_CRIT_PRFL)

Extract Layout (BEN_EXT_RCD_IN_FILE)

Extracts Related Tables

Important tables where extracts information is being
stored
ben_ext_dfn
ben_ext_rslt
ben_ext_rslt_dtl
ben_ext_rcd
ben_ext_data_elmt
ben_ext_data_elmt_in_rcd
ben_ext_file
ben_ext_rcd_in_file

Benmngle (ben_manage_life_events)

Benmngle (ben_manage_life_events)

Benmngle (ben_manage_life_events)

Benmngle (ben_manage_life_events)

Quick Recap
Questions
1)

What is a Certification ?

2)

Normally to what type of plans the certifications can be done ?

3)

When do suspended and interim enrollments get created ?

4)

How do we identify the suspended and interim enrollments in the backend table ?

5)

What is an Extract ?

6)

What are the setups you need to do in order to configure an extract ?

7)

What do we configure in Extract definition ?

8)

What do we have in Extract layout ?

9)

What is the purpose of Extract Criteria ?

10)

What is the significance of Post Processing rules ?

11)

Where do you give the naming conventions for the extract output file ?

12)

What exactly is benmngle and whats the oracle package name ?

13)

Name few tables for Certification & Extracts ?

Important Tables in OAB

PER_ALL_PEOPLE_F
PER_ALL_ASSIGNMENTS_F
PER_PERIODS_OF_SERVICE
PER_ABSENCE_ATTENDANCES
PER_ABSENCE_ATTENDANCE_TYPES
PER_CONTACT_RELATIONSHIPS
PER_PERSON_TYPES
PER_PERSON_TYPE_USAGES_F
BEN_PER_IN_LER
BEN_PTNL_LER_FOR_PER
BEN_ELIG_PER_ELCTBL_CHC
BEN_PRTT_ENRT_RSLT_F