Appendix 3: Formula Function

Mandatory Contexts:

HR_ASSIGNMENT_ID
EFFECTIVE_DATE
BUSINESS_GROUP_ID

Input Parameters:

Data type is Char for all parameters:

TABLE_NAME
COLUMN_NAME
PLAN_NAME
OPTION_NAME
ADD_KEY_NAME
ADD_KEY_VALUE

The ADD_KEY_NAME and ADD_KEY_VALUE parameters are conditionally supported for limited use cases. Refer to example 3.

Return Variables

Variable name could be anything. Data type is CHAR.

Sample Function Call:

Example 1 : ben_fn_get_char_value('BEN_PRTT_ENRT_RSLT', 'ENROLLED', 'Medical', 'Employee')

Example 2 : l_if_pl_enrolled = ben_fn_get_char_value('BEN_PRTT_ENRT_RSLT’,'ENROLLED’,’med_plan’,'NA’)

Example 3: l_dpnt_ctfn=BEN_FN_GET_CHAR_VALUE('BEN_CVRD_DPNT_CTFN_PRVDD’,'PROVIDED’,'PlanA’,'OptionA’,to_char(l_dpnt_id),'Birth certificate’)

Table And Columns Supported By This Function

Table and columns supported by BEN_FN_GET_CHAR_VALUE

Table Name	Column Name	Usage
PER_JOB_EXTRA_INFO_F	JEI_ATTRIBUTE1, JEI_ATTRIBUTE2, JEI_ATTRIBUTE3, JEI_ATTRIBUTE4, JEI_ATTRIBUTE5, JEI_ATTRIBUTE6, JEI_ATTRIBUTE7, JEI_ATTRIBUTE8, JEI_ATTRIBUTE9, JEI_ATTRIBUTE10, JEI_ATTRIBUTE11, JEI_ATTRIBUTE12, JEI_ATTRIBUTE13, JEI_ATTRIBUTE14, JEI_ATTRIBUTE15, JEI_ATTRIBUTE16, JEI_ATTRIBUTE17, JEI_ATTRIBUTE18, JEI_ATTRIBUTE19, JEI_ATTRIBUTE20, JEI_ATTRIBUTE21, JEI_ATTRIBUTE22, JEI_ATTRIBUTE23, JEI_ATTRIBUTE24, JEI_ATTRIBUTE25, JEI_ATTRIBUTE26, JEI_ATTRIBUTE27, JEI_ATTRIBUTE28, JEI_ATTRIBUTE29, JEI_ATTRIBUTE30, JEI_INFORMATION1, JEI_INFORMATION2, JEI_INFORMATION3, JEI_INFORMATION4, JEI_INFORMATION5, JEI_INFORMATION6, JEI_INFORMATION7, JEI_INFORMATION8, JEI_INFORMATION9, JEI_INFORMATION10, JEI_INFORMATION11, JEI_INFORMATION12, JEI_INFORMATION13, JEI_INFORMATION14, JEI_INFORMATION15, JEI_INFORMATION16, JEI_INFORMATION17, JEI_INFORMATION18, JEI_INFORMATION19, JEI_INFORMATION20, JEI_INFORMATION21, JEI_INFORMATION22, JEI_INFORMATION23, JEI_INFORMATION24, JEI_INFORMATION25, JEI_INFORMATION26, JEI_INFORMATION27, JEI_INFORMATION28, JEI_INFORMATION29, JEI_INFORMATION30	BEN_FN_GET_CHAR_VALUE('PER_JOB_EXTRA_INFO_F','JEI_ATTRIBUTE1') This function call returns the value stored in JEI_ATTRIBUTE1 of the table PER_JOB_EXTRA_INFO_F.
BEN_PRTT_ENRT_RSLT	ADMIN_CATEGORY_CD, BNFT_AMT, BNFT_NNMNTRY_UOM, BNFT_TYP_CD, COMP_LVL_CD, ENROLLED, ENRT_CVG_STRT_DT, ENRT_CVG_THRU_DT, IMPTD_INCM_CALC_CD, PRTT_ENRT_RSLT_ID, RPLCS_SSPNDD_RSLT_ID, SS_CATEGORY_CD, SSPNDD_FLAG, SVNGS_PLN_FLAG, UOM,ORGNL_ENRT_DT, ERLST_DEENRT_DT, ENRT_OVRID_THRU_DT, INTERIM_FLAG, ENRT_OVRIDN_FLAG, ENRT_MTHD_CD	BEN_FN_GET_CHAR_VALUE('BEN_PRTT_ENRT_RSLT','ENROLLED','Healthy HMO' /* plan name /,'Employee Only' / option name */) This function call will return Y if the participant is enrolled in the plan (Healthy HMO) and option (Employee Only). If the participant is not enrolled then 'N' will be the return value.
BEN_ELIG_CVRD_DPNT	ENROLLED, CVG_STRT_DT, CVG_THRU_DT, DPNT_PERSON_ID, RLNSHP_CD	BEN_FN_GET_CHAR_VALUE('BEN_ELIG_CVRD_DPNT','ENROLLED','Employee Basic Life' /* Plan Name /,'$50,000' / Option Name /,'DEPENDENT_FULL_NAME' / Key name /,'Jones, Fred' / dependent name (value) */) This function call returns 'Y' if the dependent is enrolled by the participant in the plan (Employee Basic Life) and in option ($50,000). If there is no enrollment found then 'N' is returned.
BEN_ELIG_DPNT	CVRD_FLAG, DPNT_INELIG_FLAG, RLNSHP_CD, ELIG_STRT_DT, ELIG_THRU_DT, OVRDN_FLAG, INELG_RSN_CD, ELIG_PER_ELCTBL_CHC_ID, PER_IN_LER_ID, ELIG_CVRD_DPNT_ID, INELG_RSN_CD, DPNT_PERSON_ID, CTFN_REQD_FLAG, PCP_DSGN_CD	BEN_FN_GET_CHAR_VALUE('BEN_ELIG_DPNT','CVRD_FLAG','Employee Basic Life' /* Plan Name /,'$50,000' / Option Name /,'DEPENDENT_FULL_NAME' / Key name /,'Jones, Fred' / dependent name (value) */) This function call returns 'Y' if the dependent is eligible to participate in plan (Employee Basic Life) and option ($50,000). If there is no eligible dependent record found then 'N' is returned.
BEN_CVRD_DPNT_CTFN_PRVDD	PROVIDED	BEN_FN_GET_CHAR_VALUE('BEN_CVRD_DPNT_CTFN_PRVDD','PROVIDED','Healthy HMO' /* Plan Name /,'Employee + children' / Option Name. Pass 'NA' if the plan is not associated with any option /,to_char(l_dpnt_id) / personId of the dependent /,'Legal custody certificate' / certification type /) This function call returns 'Y' if a certification of type (Legal custody certificate) is provided by the participant for the dependent identified by personId. Possible certification types are: Adoption certificate Birth certificate Domestic partner certificate Legal custody certificate Marriage certificate Alternatively, the following parameters can be used to check if the certification was provided in a previous life event for the dependent. The determinant NA_CHECK_ONCE should be passed as the plan name. BEN_FN_GET_CHAR_VALUE('BEN_CVRD_DPNT_CTFN_PRVDD','PROVIDED','NA_CHECK_ONCE' / determinant /,' ' / space /,to_char(l_dpnt_id) / personId of the dependent /,'Legal custody certificate' / certification type */)
BEN_PRTT_ENRT_CTFN_PRVDD	ENRT_CTFN_RECD_DT, ENRT_CTFN_DND_DT	BEN_FN_GET_CHAR_VALUE('BEN_PRTT_ENRT_CTFN_PRVDD','ENRT_CTFN_RECD_DT','Employee Supplemental Life' /* Plan Name /,'NA' / you want to check against any option within the plan /,'Proof of good health' / certification type */) This function call returns the certification received date (of the participant) for the plan (Employee Supplemental Life) and any option under it. Possible certification types that can be checked for are: Domestic partner affidavit Proof of external coverage Proof of good health Proof of other coverage
PER_CONTACT_RELSHIPS_F	DAUGHTER_COUNT, OTHERS_COUNT, SON_COUNT, SPOUSE_COUNT, DP_COUNT, DP_MALE_COUNT, DP_FEMALE_COUNT, ADOPTED_CHILD_COUNT, STEP_CHILD_COUNT, FOSTER_CHILD_COUNT	BEN_FN_GET_CHAR_VALUE('PER_CONTACT_RELSHIPS_F','DAUGHTER_COUNT',' ' /* space (not used) /,' ' / space (not used) /,'LEGISLATION_CODE' / key /,'US' / Pass the legislation code of the participant */) This function call returns the aggregate counts of the dependents based on the contact type.
BEN_PER_IN_LER	PER_IN_LER_ID, BENEFIT_RELATION_ID, LF_EVT_OCRD_DT, PROCD_DT, STRTD_DT, VOIDD_DT, PERSON_ID, BCKT_DT, CLSD_DT, NTFN_DT, LEGAL_ENTITY_ID, BENEFIT_REL_SYSTEM_CD, PRIMARY_REL, REL_PRMRY_ASG_ID, NAME, LER_ID	BEN_FN_GET_CHAR_VALUE('BEN_PER_IN_LER','LF_EVT_OCRD_DT',' ' /* space (not used) /,' ' / space (not used) /,'BENEFIT_RELATION_NAME' / key /,'Default' / benefit relation name as seen in the UI */) This function call returns life event date of the started event corresponding to the benefit relation name which is passed as a parameter.
PAY_ELEMENT_ENTRIES_F	AGGREGATE	BEN_FN_GET_CHAR_VALUE('PAY_ELEMENT_ENTRIES_F','AGGREGATE','Medical-ER' /* element name /,'VAL' / input value name /,'2014/01/01' / from date /,'2014/12/31' / to date */) This function call returns the aggregate of the element entry values for an element for the date range specified by from and to dates.
BEN_PRTT_RT_VAL	AGG_RT_VAL, AGG_CMCD_RT_VAL	BEN_FN_GET_CHAR_VALUE('BEN_PRTT_RT_VAL','AGG_RT_VAL','Healthy HMO' /* plan name /,'Employee Only' / option name /,'2014/01/01' / from date /,'2014/12/31' / to date */) This function call returns the aggregate of the rates for the enrollment in Healthy HMO:Employee Only for the date range specified by from and to dates. All rates including primary/secondary/others will be considered.
BEN_PRTT_RT_VAL	AGG_RT_VAL, AGG_CMCD_RT_VAL	BEN_FN_GET_CHAR_VALUE('BEN_PRTT_RT_VAL','AGG_RT_VAL','ACTIVITY_BASE_RATE' /* determinant to indicate that only a single rate should be summed up /,'Healthy_HMO_Employee_only_Vision' / rate name /,'2014/01/01' / from date /,'2014/12/31' / to date */) This function call returns the aggregate of the rate specified by the activity base rate name for the date range specified by from and to dates.
PAY_TIME_PERIODS	PAYROLL_ID, PAYROLL_TYPE, PERIOD_NUM, PERIOD_NAME, START_DATE, END_DATE, CUT_OFF_DATE, DEFAULT_PAYDATE, TOTAL_TP_IN_CALENDAR_YEAR	BEN_FN_GET_CHAR_VALUE('PAY_TIME_PERIODS','DEFAULT_PAYDATE',' ' /* space (not used) /,' ' / space (not used) /,'START_TIME' / key /,'2014/12/05' / date used to identify the pay period */) This function call returns the pay date of the corresponding participant’s payroll. The value for the START_TIME identifies the date which is used to identify the pay period. For example if the START_TIME 2014/12/05 and the participant is on a monthly payroll, then the default pay date for the December time period is returned.

Table Name

Column Name

Usage

PER_JOB_EXTRA_INFO_F

JEI_ATTRIBUTE1,

JEI_ATTRIBUTE2,

JEI_ATTRIBUTE3,

JEI_ATTRIBUTE4,

JEI_ATTRIBUTE5,

JEI_ATTRIBUTE6,

JEI_ATTRIBUTE7,

JEI_ATTRIBUTE8,

JEI_ATTRIBUTE9,

JEI_ATTRIBUTE10,

JEI_ATTRIBUTE11,

JEI_ATTRIBUTE12,

JEI_ATTRIBUTE13,

JEI_ATTRIBUTE14,

JEI_ATTRIBUTE15,

JEI_ATTRIBUTE16,

JEI_ATTRIBUTE17,

JEI_ATTRIBUTE18,

JEI_ATTRIBUTE19,

JEI_ATTRIBUTE20,

JEI_ATTRIBUTE21,

JEI_ATTRIBUTE22,

JEI_ATTRIBUTE23,

JEI_ATTRIBUTE24,

JEI_ATTRIBUTE25,

JEI_ATTRIBUTE26,

JEI_ATTRIBUTE27,

JEI_ATTRIBUTE28,

JEI_ATTRIBUTE29,

JEI_ATTRIBUTE30,

JEI_INFORMATION1,

JEI_INFORMATION2,

JEI_INFORMATION3,

JEI_INFORMATION4,

JEI_INFORMATION5,

JEI_INFORMATION6,

JEI_INFORMATION7,

JEI_INFORMATION8,

JEI_INFORMATION9,

JEI_INFORMATION10,

JEI_INFORMATION11,

JEI_INFORMATION12,

JEI_INFORMATION13,

JEI_INFORMATION14,

JEI_INFORMATION15,

JEI_INFORMATION16,

JEI_INFORMATION17,

JEI_INFORMATION18,

JEI_INFORMATION19,

JEI_INFORMATION20,

JEI_INFORMATION21,

JEI_INFORMATION22,

JEI_INFORMATION23,

JEI_INFORMATION24,

JEI_INFORMATION25,

JEI_INFORMATION26,

JEI_INFORMATION27,

JEI_INFORMATION28,

JEI_INFORMATION29,

JEI_INFORMATION30

BEN_FN_GET_CHAR_VALUE('PER_JOB_EXTRA_INFO_F','JEI_ATTRIBUTE1')

This function call returns the value stored in JEI_ATTRIBUTE1 of the table PER_JOB_EXTRA_INFO_F.

BEN_PRTT_ENRT_RSLT

ADMIN_CATEGORY_CD,

BNFT_AMT,

BNFT_NNMNTRY_UOM,

BNFT_TYP_CD,

COMP_LVL_CD,

ENROLLED,

ENRT_CVG_STRT_DT,

ENRT_CVG_THRU_DT,

IMPTD_INCM_CALC_CD,

PRTT_ENRT_RSLT_ID,

RPLCS_SSPNDD_RSLT_ID,

SS_CATEGORY_CD,

SSPNDD_FLAG,

SVNGS_PLN_FLAG,

UOM,ORGNL_ENRT_DT,

ERLST_DEENRT_DT,

ENRT_OVRID_THRU_DT,

INTERIM_FLAG,

ENRT_OVRIDN_FLAG,

ENRT_MTHD_CD

BEN_FN_GET_CHAR_VALUE('BEN_PRTT_ENRT_RSLT','ENROLLED','Healthy HMO' /* plan name */,'Employee Only' /* option name */)

This function call will return Y if the participant is enrolled in the plan (Healthy HMO) and option (Employee Only). If the participant is not enrolled then 'N' will be the return value.

BEN_ELIG_CVRD_DPNT

ENROLLED,

CVG_STRT_DT,

CVG_THRU_DT,

DPNT_PERSON_ID,

RLNSHP_CD

BEN_FN_GET_CHAR_VALUE('BEN_ELIG_CVRD_DPNT','ENROLLED','Employee Basic Life' /* Plan Name */,'$50,000' /* Option Name */,'DEPENDENT_FULL_NAME' /* Key name */,'Jones, Fred' /* dependent name (value) */)

This function call returns 'Y' if the dependent is enrolled by the participant in the plan (Employee Basic Life) and in option ($50,000). If there is no enrollment found then 'N' is returned.

BEN_ELIG_DPNT

CVRD_FLAG,

DPNT_INELIG_FLAG,

RLNSHP_CD,

ELIG_STRT_DT,

ELIG_THRU_DT,

OVRDN_FLAG,

INELG_RSN_CD,

ELIG_PER_ELCTBL_CHC_ID,

PER_IN_LER_ID,

ELIG_CVRD_DPNT_ID,

INELG_RSN_CD,

DPNT_PERSON_ID,

CTFN_REQD_FLAG,

PCP_DSGN_CD

BEN_FN_GET_CHAR_VALUE('BEN_ELIG_DPNT','CVRD_FLAG','Employee Basic Life' /* Plan Name */,'$50,000' /* Option Name */,'DEPENDENT_FULL_NAME' /* Key name */,'Jones, Fred' /* dependent name (value) */)

This function call returns 'Y' if the dependent is eligible to participate in plan (Employee Basic Life) and option ($50,000). If there is no eligible dependent record found then 'N' is returned.

BEN_CVRD_DPNT_CTFN_PRVDD

PROVIDED

BEN_FN_GET_CHAR_VALUE('BEN_CVRD_DPNT_CTFN_PRVDD','PROVIDED','Healthy HMO' /* Plan Name */,'Employee + children' /* Option Name. Pass 'NA' if the plan is not associated with any option */,to_char(l_dpnt_id) /* personId of the dependent */,'Legal custody certificate' /* certification type */)

This function call returns 'Y' if a certification of type (Legal custody certificate) is provided by the participant for the dependent identified by personId.

Possible certification types are:

Adoption certificate
Birth certificate
Domestic partner certificate
Legal custody certificate
Marriage certificate

Alternatively, the following parameters can be used to check if the certification was provided in a previous life event for the dependent. The determinant NA_CHECK_ONCE should be passed as the plan name.

BEN_FN_GET_CHAR_VALUE('BEN_CVRD_DPNT_CTFN_PRVDD','PROVIDED','NA_CHECK_ONCE' /* determinant */,' ' /* space */,to_char(l_dpnt_id) /* personId of the dependent */,'Legal custody certificate' /* certification type */)

BEN_PRTT_ENRT_CTFN_PRVDD

ENRT_CTFN_RECD_DT,

ENRT_CTFN_DND_DT

BEN_FN_GET_CHAR_VALUE('BEN_PRTT_ENRT_CTFN_PRVDD','ENRT_CTFN_RECD_DT','Employee Supplemental Life' /* Plan Name */,'NA' /* you want to check against any option within the plan */,'Proof of good health' /* certification type */)

This function call returns the certification received date (of the participant) for the plan (Employee Supplemental Life) and any option under it. Possible certification types that can be checked for are:

Domestic partner affidavit
Proof of external coverage
Proof of good health
Proof of other coverage

PER_CONTACT_RELSHIPS_F

DAUGHTER_COUNT,

OTHERS_COUNT,

SON_COUNT,

SPOUSE_COUNT,

DP_COUNT,

DP_MALE_COUNT,

DP_FEMALE_COUNT,

ADOPTED_CHILD_COUNT,

STEP_CHILD_COUNT,

FOSTER_CHILD_COUNT

BEN_FN_GET_CHAR_VALUE('PER_CONTACT_RELSHIPS_F','DAUGHTER_COUNT',' ' /* space (not used) */,' ' /* space (not used) */,'LEGISLATION_CODE' /* key */,'US' /* Pass the legislation code of the participant */)

This function call returns the aggregate counts of the dependents based on the contact type.

BEN_PER_IN_LER

PER_IN_LER_ID,

BENEFIT_RELATION_ID,

LF_EVT_OCRD_DT,

PROCD_DT,

STRTD_DT,

VOIDD_DT,

PERSON_ID,

BCKT_DT,

CLSD_DT,

NTFN_DT,

LEGAL_ENTITY_ID,

BENEFIT_REL_SYSTEM_CD,

PRIMARY_REL,

REL_PRMRY_ASG_ID,

NAME,

LER_ID

BEN_FN_GET_CHAR_VALUE('BEN_PER_IN_LER','LF_EVT_OCRD_DT',' ' /* space (not used) */,' ' /* space (not used) */,'BENEFIT_RELATION_NAME' /* key */,'Default' /* benefit relation name as seen in the UI */)

This function call returns life event date of the started event corresponding to the benefit relation name which is passed as a parameter.

PAY_ELEMENT_ENTRIES_F

AGGREGATE

BEN_FN_GET_CHAR_VALUE('PAY_ELEMENT_ENTRIES_F','AGGREGATE','Medical-ER' /* element name */,'VAL' /* input value name */,'2014/01/01' /* from date */,'2014/12/31' /* to date */)

This function call returns the aggregate of the element entry values for an element for the date range specified by from and to dates.

BEN_PRTT_RT_VAL

AGG_RT_VAL,

AGG_CMCD_RT_VAL

BEN_FN_GET_CHAR_VALUE('BEN_PRTT_RT_VAL','AGG_RT_VAL','Healthy HMO' /* plan name */,'Employee Only' /* option name */,'2014/01/01' /* from date */,'2014/12/31' /* to date */)

This function call returns the aggregate of the rates for the enrollment in Healthy HMO:Employee Only for the date range specified by from and to dates. All rates including primary/secondary/others will be considered.

BEN_PRTT_RT_VAL

AGG_RT_VAL,

AGG_CMCD_RT_VAL

BEN_FN_GET_CHAR_VALUE('BEN_PRTT_RT_VAL','AGG_RT_VAL','ACTIVITY_BASE_RATE' /* determinant to indicate that only a single rate should be summed up */,'Healthy_HMO_Employee_only_Vision' /* rate name */,'2014/01/01' /* from date */,'2014/12/31' /* to date */)

This function call returns the aggregate of the rate specified by the activity base rate name for the date range specified by from and to dates.

PAY_TIME_PERIODS

PAYROLL_ID,

PAYROLL_TYPE,

PERIOD_NUM,

PERIOD_NAME,

START_DATE,

END_DATE,

CUT_OFF_DATE,

DEFAULT_PAYDATE,

TOTAL_TP_IN_CALENDAR_YEAR

BEN_FN_GET_CHAR_VALUE('PAY_TIME_PERIODS','DEFAULT_PAYDATE',' ' /* space (not used) */,' ' /* space (not used) */,'START_TIME' /* key */,'2014/12/05' /* date used to identify the pay period */)

This function call returns the pay date of the corresponding participant’s payroll. The value for the START_TIME identifies the date which is used to identify the pay period. For example if the START_TIME 2014/12/05 and the participant is on a monthly payroll, then the default pay date for the December time period is returned.