Configuration Tip: How to Set Up Early and Normal Retirement Date Aliases

Learn how to configure Data Aliases to return the earliest date a member did, or is projected to, meet Age and Service requirements, such as for Early and Normal Retirement Dates.

Configuration Challenge

Members are first eligible for Normal Retirement at the earliest date they meet these criteria:

• Attain Age 60 and have 20 Years of State Service, or
• Attain 35 Years of Total Service and 25 Years of State Service.

The Early (ERD) and Normal (NRD) Retirement Date Aliases on the Plan Aliases page can only be configured using a Date Alias. Date Aliases are great at resolving the member’s 60th Birthdate but are not great for determining when the member is projected to first meet the Normal Retirement criteria.

These Date Aliases are resolved during the Periodic Process before any other Function Results are processed. The Periodic Process may be run with an AsOfDate before the ERD/NRD, and there will be no Service data or projections past the AsOfDate.

Determining when the member attained a certain amount of Service, or projecting when the member will attain the required Service, will need to be included in the Date Alias. This will require a great deal of clever configuration.

The date a member is projected to meet a service threshold will be estimated with a simple method:

• Retrieve the Accrued Service Amount and Last Process Date from Service History. This relies on the Periodic Process Results in the PA_SVC_HIST table.
• Compute Additional Years and Months of Service needed to meet the Service requirement. This is the Service Requirement minus the Accrued Service Amount.
• Add the Additional Years and Months to the Last Process Date.

Advanced Challenge

The Early Retirement Factor (ERF) uses the number of months between the Benefit Commencement Date (BCD) and the date the member would first be eligible for Normal Retirement (NRD) if they continued working.

This is a similar challenge, but the service that is projected by the calculation to the Event Date should be used as the starting point to project service to the NRD.

Background

The Early (ERD) and Normal (NRD) Retirement Date Aliases are used to populate the Plan Dates section of the Review Plan History Plan Information page:

This Default Early (ERD) or Normal Retirement Date (NRD) can be used as the Benefit Commencement Date (BCD) on the Define Calculation Plan Inputs page. This option can be particularly useful for Group Calculations where each member in the group may have a different BCD.

Configuration Hints

This section will deconstruct a working implementation of a Normal Retirement Date Alias for the eligibility rules outlined above.

The Plan Aliases page only allows Date Aliases to be selected. A Custom Statement is used to include more advanced business logic. A Custom Statement may return a date result and can be converted to a Date Alias like this:

Earliest Normal Retirement Date

This Custom Statment resolves the earliest date the member will be eligible for Normal Retirement:

Line 10 identifies the later of the date the member attains 20 years of State Service (T_SDC20_CS) and when the member turns age sixty.

Line 20 finds the earliest date the member attains at least 25 years of State Service (T_SDC25_CS) and at least 35 years of Total Service (T_SDR35_CS).

The remaining lines work out the earlier of the two dates, being careful to look out for dates that could not be projected, like when the member is already terminated.

When Member Attains 20 Years of Service

Exploring how T_SDC20_CS should give a good idea of how all three projected service attainment dates are resolved, with the differences being what type of service is considered and how much service is required.

These important Aliases are used in this Custom Statement:

• T_SAC20_DT – Date member attained 20 years of State Service – Defaults to 12/31/2999 if the member has not attained 20 Years of State service.
• T_STATDT_A – Termination Date – Defaults to 12/31/2999 if Active using logic in a custom view.
• T_SPC20_DT – Date member is projected to attain 20 Years of State Service

The Custom Statement returns the date the member has attained 20 years if they have attained 20 years. Otherwise, if the member is active, return the date the member is projected to attain 20 years. Otherwise, the member is not projected to attain 20 years, and the default 12/31/2999 value is returned.

PSPA Configuration does not handle NULL dates gracefully, so precautions need to be taken to populate NULL dates with a recognizable default value, in this case, 12/31/2999. Configuration logic can then test for the value and treat the value as NULL.

When DID the Member Attained 20 Years of Service

The Service Date option of a Date Alias can be utilized to find the end date of the service period where the accumulated service meets or exceeds a specified value for a specified Service Function Result.

Note that Date Aliases that utilize the Service Date Option work more reliably if the related enhancements listed below in the Prerequisites section are implemented. See below for more information.

When WILL the Member Attain 20 Years of Service

To get the project service date, add the number of months short of attaining 20 years to the last service period date:

The Database Alias, T_SAACD_A, returns the end date of the last service period for the specified Function Result:

Note this Table Key and the Additional Key Values Detail options are custom enhancements allowing the Database Alias to retrieve data from PA_SVC_HIST for a specified Function Result and date. The alternative to this customization would be to create a custom view that returns the maximum row for a hardcoded function result name.

The Custom Statement Alias, T_SMC20_CS, returns the number of months needed to achieve 20 Years of service:

This Custom Statement uses these operands:

• T_SAAC_A – Accrued State Service
• T_SAACM_DT – Accrued Service Date Month – extracted from T_SAACD_A

This Custom Statement resolves the number of months difference between the 20 Year service requirement and the Accrued State Service. Some fancy logic is included to not accrue service during the summer months of July and August because this is a Teacher’s Plan.

The Database Alias, T_SAAC_A, returns the accumulated service amount of the last service period for the specified Function Result:

The Data Alias, T_SAACM_DT, extracts the Month from the end date of the last service period for the specified Function Result:

Results

After the configuration is properly setup, the Normal Retirement Date and Early Retirement Date will be resolved correctly and displayed in the Plan Dates section of the Review Plan History Plan Information page:

The First Periodic Process

Since Service data (PA_SVC_HIST) is required to correctly resolve the ERD/NRD Aliases, incorrect results will be returned the first time the Periodic Process is run for a member. The Periodic Process is programmed to only update the stored ERD/NRD (PA_PLAN_DT_TBL) if the process is run at a different date. I recommend deleting the data for the member/plan in the PA_PLAN_DT_TBL after the first time the Periodic Process is run in Delete/Rebuild mode and Service results are initially populated. Then, run the Periodic Process again to correctly resolve the ERD and NRD using the previously posted Service. After these steps, the ERD/NRD should be properly populated.

Prerequisite Enhancements

Solving the ERD/NRD configuration problem is not a simple task. The configuration approach outlined in this article relies on some extension of delivered Alias capabilities.

Enhance Date Aliases to allow adding zero

This example includes Date Aliases used to convert Custom Statement Alias with a date result to a Date Alias. A Custom Statement that returns zero can be used in one of the three Add Aliases. Alternatively, an enhancement can be made to allow all six Add value and alias options to be left blank by changing a delivered error message (16000,89) to a warning message in PA_DATE.DATE_FROM_ALIAS.SaveEdit PeopleCode.

Enhance Date Aliases Service Date Option (87)

This example includes Date Aliases based on Attained Service to identify the date a member attained certain amounts of Service. Issues with this type of Date Alias may be encountered unless the enhancement to Improve the Service Date Option of Data Aliases is implemented.

Enhance DatABASE ALIAS KEY OPTIONS

This example includes Database Aliases that utilize enhanced key options and inputs to retrieve a particular Service history row from the PA_SVC_HIST table. This data posted by a prior Periodic Process is required because the ERD and NRD are resolved before Service Function results are processed.

Enhance Custom Statement MIN/MAX Function with date operands

This example includes Date operands in a Custom Statement MIN/MAX Function to identify the earliest date either of the Normal Retirement eligibility criteria are met. Unfortunately, attempting to use MIN/MAX on dates will return the error message, “Operands in custom statement contains a non-numeric value” unless the fix for “Add Support for Date and Character Arguments in a Custom Statement MIN/MAX Function” is implemented.

Issues with CUSTOM STATEMENT date temp variable assignment

This example includes an assignment of Date Temporary variables to the final Custom Statement result, which will return the error message, “Error in assignment – incompatible types“, unless the fix for “Custom Statement Assignment from Character or Date Temporary Variable Fails” is implemented.

enhance Date alias service date option with support for age and service points (165)

This example does not include Age and Service Points criteria for Early or Normal Retirement eligibility. If necessary, the Date Alias Service Date Options can be enhanced to enable identification of the date a member first attains a certain Point level.

Commentary

As you can see, there should be a much more straightforward way to configure the earliest projected Early and Normal Retirement dates.

Many clients resort to creating bolt-on processes to resolve ERD and NRD values.

Perhaps a new core functionality should be developed or additional capabilities added to Benefit Eligibility.

The challenges of designing a solution from Benefit Eligibility include:

• ERD/NRD are currently resolved before Service and Benefit Eligibility
• Benefit Eligibility is designed to consume Service to confirm eligibility.
• Benefit Eligibility is often configured using Custom Statements, which are not time-aware. There might need to be the capability to evaluate a Custom Statement throughout the member’s employment history to find dates where the result changes.
• Service is not currently projected past the Event Date.
• Service Projection Method options are not very robust.

Additional Resources

To learn more about the ERD and NRD Aliases, see PeopleBooks.