Summary



Technical Aspect

Now, it is time for the tables. Here is a list of important tables that are used in Benefits. BEN being the Application owner, almost all the related tables start with BEN. There are links to Payroll and most importantly Core-HR; hence OAB shares a lot of table with those two modules as well.

Note:

  • In the table below, if the Date tracked column is marked as Yes, assume the Primary key to be Composite. The given Primary with bind with the two date tracked columns to make the Composite Primary key.
  • The table is sorted in alphabetical order.

o        Some of the values in the column Table could be a view / synonym. However they pull data.

 

 Table Name

 Date Tracked?

 Primary Key

 Description

BEN_ACTL_PREM_F

Yes

ACTL_PREM_ID

Stores the Premium related information.

BEN_ACTL_PREM_VRBL_RT_F

Yes

ACTL_PREM_VRBL_RT_ID

Stores the variable rate profiles for Premium usages.

BEN_ACTY_BASE_RT_F

Yes

ACTY_BASE_RT_ID

The master table for rates.

BEN_ACTY_VRBL_RT_F

Yes

ACTY_VRBL_RT_ID

Stores the variable rate profiles for Rate usages.

BEN_AGE_FCTR

No

AGE_FCTR_ID

Stores Age Derived Factors

BEN_BENEFIT_ACTIONS

No

BENEFIT_ACTION_ID

Stores the defined Action Items in system

BEN_BENFTS_GRP

No

BENFTS_GRP_ID

Stores the Benefit Group Information

BEN_BNFTS_BAL_F

Yes

BNFTS_BAL_ID

Stores Defined Benefit Balances

BEN_CLPSE_LF_EVT_F

Yes

CLPSE_LF_EVT_ID

Stores the collapsing Life Event logic

BEN_CM_TYP_F

Yes

CM_TYP_ID

Stores the Communication types.

BEN_CM_TYP_TRGR_F

Yes

CM_TYP_TRGR_ID

Stores the Triggers attached to different Communication types

BEN_CM_TYP_USG_F

Yes

CM_TYP_USG_ID

Stores the Usages attached to different Communication types

BEN_CMBN_AGE_LOS_FCTR

Yes

CMBN_AGE_LOS_FCTR_ID

Stores the AGE LOS combination derived Factor

BEN_COMP_LVL_FCTR

No

COMP_LVL_FCTR_ID

Stores the Compensation Level derived factor

BEN_CRT_ORDR

No

CRT_ORDR_ID

Stores the Court Orders stored in system

BEN_ELCTBL_CHC_CTFN

No

ELCTBL_CHC_CTFN_ID

Stores the certifications attached to a person’s Electable choices.

BEN_ELIG_CVRD_DPNT_F

Yes

ELIG_CVRD_DPNT_ID

Stores the Eligible Dependents of the Participants.

BEN_ELIG_DPNT

No

DPNT_INELIG_FLAG

Stores all the dependents of a Participant with eligible / ineligible flag.

BEN_ELIG_PER_ELCTBL_CHC

No

ELIG_PER_ELCTBL_CHC_ID

Stores the Electable choices of a Participant

BEN_ELIG_PER_F

Yes

ELIG_PER_ID

Stores the Eligibility of Participants along with the derived factor values.

BEN_ELIGY_PRFL_F

Yes

ELIGY_PRFL_ID

Stores the Eligibility Profiles

BEN_ENRT_BNFT

No

ENRT_BNFT_ID

Stores the Benefit amount based on every electable Choice.

BEN_ENRT_RT

No

ENRT_RT_ID

Stores the Rate amount based on every electable Choice.

BEN_EXT_DATA_ELMT

No

EXT_DATA_ELMT_ID

Stores the Data elements in Extracts

BEN_EXT_DFN

No

EXT_DFN_ID

Stores the Extract Definitions

BEN_EXT_FILE

No

EXT_FILE_ID

Stores Additional Extract definition information

BEN_EXT_RCD

No

EXT_RCD_ID

Stores the Extract Record details.

BEN_EXT_RSLT

No

EXT_RSLT_ID

Stores Extract Result Headers.

BEN_EXT_RSLT_DTL

No

EXT_RSLT_DTL_ID

Stores Extract result details with Data element values.

BEN_HRS_WKD_IN_PERD_FCTR

No

HRS_WKD_IN_PERD_FCTR_ID

Stores the Hours worked in Period factor.

BEN_LER_F

No

LER_ID

Stores the Life event Reasons

BEN_LOS_FCTR

No

LOS_FCTR_ID

Stores the Length Of Service Derived Factors.

BEN_OIPL_F

Yes

OIPL_ID

Stores Option in Plans

BEN_OIPLIP_F

Yes

OIPLIP_ID

Stores Option in Plan In Programs

BEN_OPT_F

Yes

OPT_ID

Stores Option Ids

BEN_PCT_FL_TM_FCTR

No

PCT_FL_TM_FCTR_ID

Stores Full time worked Derived Factor

BEN_PER_CM_F

Yes

PER_CM_ID

Stores the Communication attached to a person.

BEN_PER_CM_PRVDD_F

Yes

PER_CM_PRVDD_ID

Stores the details on Person Communication with the requestor flag, to be sent date etc.

BEN_PER_CM_TRGR_F

Yes

PER_CM_TRGR_ID

Stores the triggering logic on the Person Communication details.

BEN_PER_CM_USG_F

Yes

PER_CM_USG_ID

Stores the Usage logic on the Person Communication details.

BEN_PER_IN_LER

No

PER_IN_LER_ID

Stores the Life Event details processed on a Person.

BEN_PGM_F

Yes

PGM_ID

Stores the Program details.

BEN_PIL_ELCTBL_CHC_POPL

No

PIL_ELCTBL_CHC_POPL_ID

Stores additional details on the Participant’s electable choices.

BEN_PL_F

Yes

PL_ID

Stores Plan details.

BEN_PL_TYP_F

Yes

PL_TYP_ID

Stores Plan Type related details.

BEN_PLIP_F

Yes

PLIP_ID

Stores the Plan Type in Program Details.

BEN_PRTT_ENRT_RSLT_F

Yes

PRTT_ENRT_RSLT_ID

The most important table in Benefits election. This stores the election results of a participant.

BEN_PRTT_PREM_F

Yes

PRTT_PREM_ID

Stores the Premium details for a Participant.

BEN_PRTT_RT_VAL

Yes

PRTT_RT_VAL_ID

Stores the Rate value for a Participant

BEN_PSTL_ZIP_RNG_F

Yes

PSTL_ZIP_RNG_ID

Stores the Zip code information which is used in defining service areas.

BEN_PTIP_F

Yes

PTIP_ID

Stores the Plan type in Program information.

BEN_VRBL_RT_PRFL_F

Yes

VRBL_RT_PRFL_ID

Stores the Variable Rate Profile Information.

BEN_YR_PERD

No

YR_PERD_ID

Stores the different year periods.