Background

The online functionality to collect and display the Marital Status Date, Student Status Date, Disabled Status Date and Smoker Status Date fields for dependent personal data has changed from version 9.0 to version 9.2. 

When dependent data is initially entered for the first time, a status field or checkbox and corresponding date fields (As of date fields) are made available for data entry in the Personal History section of the Update Dependent/Beneficiary component.

This initial data entry appears and functions the same in 9.0 and 9.2.

9.0 Example

9.2 Example

The change comes in version 9.2 when subsequent rows are entered into the Personal History section for a dependent. Currently, each row of changes in 9.0 allows for both an Effective Date and a Marital Status Date, Student Status Date, Disabled Status Date and Smoker Status Date.  Often campuses enter different dates for these fields.  For example, the Effective Date may be the date the change was entered, and the Marital Status Date is the actual life event such as marriage or divorce date.

In 9.2, rows after the initial row for the dependent (i.e., the subsequent rows) do not allow for both an Effective Date and an As of date in the Personal History section for Marital Status, Student, Disabled and/or Smoker. This means when entering a new row to update Personal History of a dependent in version 9.2, the Effective Date is meant to be the actual effective date of the change and not a data entry or other date. 

For example, the screen shot below shows the initial row for the dependent in version 9.2 with an Effective Date of 9/11/19. Note that the As of date fields display. Note the subsequent row with an Effective Date of 12/23/19 and no As of date fields display. The Effective Date of 12/23/19 in this example indicates the effective date of the Martial Status change from Single to Married.

Issue

Conversion of data from version 9.0 to 9.2 will be impacted in record DEP_BEN_EFF.  This record only impacts the dependent. While the Marital Status Date, Student Status Date, Disabled Status Date and Smoker Status Dates entered in version 9.0 remain in the record, the Marital Status Date, Student Status Date and Disabled Status Date and Smoker Status Date fields of subsequent rows for a dependent (i.e., all rows after the dependent’s initial Personal History row in the record) will not display online on the Personal Profile page (see example screen shots above). 

Analysis

As of 3/24/2020, the following table provides a count of dependents in version 9.0 where the maximum effective-dated row and the marital status, student and disabled As of date differ for subsequent rows. (Note that there isn’t a column for smoker as the dependent Smoker fields are not used in benefit processing.)

Recommendations

1. Beginning immediately in 9.0, campus benefits staff should modify their data entry practices and use the Effective Date of the Personal History row as the same date for the actual Marital Status, Student and Disabled As of effective date. No action is required for the Smoker As of effective date as the dependent Smoker fields are not used in benefit processing.
2. Each campus needs to analyze their existing dependent/beneficiary data and determine if campus action is required to clean up existing dependent/beneficiary rows for subsequent rows (rows after the initial Personal History row for a dependent) using the correct history mode to ensure that the Effective Date of the Personal History row is the same date for the actual Marital Status, Student and Disabled As of effective date.

Work with your local IT resources to run the following applicable SQL statements to identify a list of dependents by employee ID in version 9.0 where the maximum effective-dated row and the marital status, student and disabled As of effective date differ for subsequent rows.

Marital Status

SELECT A.EMPLID, A.DEPENDENT_BENEF, A.EFFDT, A.RELATIONSHIP, A.DEP_BENEF_TYPE, A.MAR_STATUS, A.MAR_STATUS_DT

  FROM PS_DEP_BEN_EFF A WHERE A.EFFDT =

        (SELECT MAX(A_ED.EFFDT) FROM PS_DEP_BEN_EFF A_ED

        WHERE A.EMPLID = A_ED.EMPLID

          AND A.DEPENDENT_BENEF = A_ED.DEPENDENT_BENEF

          AND A_ED.EFFDT <= SYSDATE)

     AND A.EFFDT <> A.MAR_STATUS_DT

     AND (A.EMPLID,DEPENDENT_BENEF) IN (SELECT EMPLID,DEPENDENT_BENEF FROM PS_DEP_BEN_EFF where EMPLID = A.EMPLID AND DEPENDENT_BENEF = A.DEPENDENT_BENEF GROUP BY EMPLID,DEPENDENT_BENEF HAVING COUNT(*) > 1) 

     order by 1,2,3; 

Student  

SELECT A.EMPLID, A.DEPENDENT_BENEF, A.EFFDT, A.RELATIONSHIP, A.DEP_BENEF_TYPE, A.STUDENT_STATUS_DT

  FROM PS_DEP_BEN_EFF A WHERE A.EFFDT =

        (SELECT MAX(A_ED.EFFDT) FROM PS_DEP_BEN_EFF A_ED

        WHERE A.EMPLID = A_ED.EMPLID

          AND A.DEPENDENT_BENEF = A_ED.DEPENDENT_BENEF

          AND A_ED.EFFDT <= SYSDATE)

     AND A.EFFDT <> A.STUDENT_STATUS_DT 

     AND (A.EMPLID,DEPENDENT_BENEF) IN (SELECT EMPLID,DEPENDENT_BENEF FROM PS_DEP_BEN_EFF where EMPLID = A.EMPLID AND DEPENDENT_BENEF = A.DEPENDENT_BENEF GROUP BY EMPLID,DEPENDENT_BENEF HAVING COUNT(*) > 1)   

     order by 1,2,3; 

Disabled     

SELECT A.EMPLID, A.DEPENDENT_BENEF, A.EFFDT, A.RELATIONSHIP, A.DEP_BENEF_TYPE, A.DISABLED_STATUS_DT

  FROM PS_DEP_BEN_EFF A WHERE A.EFFDT =

        (SELECT MAX(A_ED.EFFDT) FROM PS_DEP_BEN_EFF A_ED

        WHERE A.EMPLID = A_ED.EMPLID

          AND A.DEPENDENT_BENEF = A_ED.DEPENDENT_BENEF

          AND A_ED.EFFDT <= SYSDATE)

     AND A.EFFDT <> A.DISABLED_STATUS_DT

     AND (A.EMPLID,DEPENDENT_BENEF) IN (SELECT EMPLID,DEPENDENT_BENEF FROM PS_DEP_BEN_EFF where EMPLID = A.EMPLID AND DEPENDENT_BENEF = A.DEPENDENT_BENEF GROUP BY EMPLID,DEPENDENT_BENEF HAVING COUNT(*) > 1)   

     order by 1,2,3; 

Considerations

Know that the Marital Status Date that prints on relevant benefit forms and the Marital Status Date that is included in relevant Benefit interfaces is selected from the Personal Data record of the employee (i.e., Modify a Person page) and not the dependent data record. 

Cross-Functional Impacts (Positive / Negative)

None identified.

1. Areas of potential change resistance to proposed HR process / policy changes?

   None.

2. Potential resource needs in order to plan, engage, prepare, and/or deploy the change?

   The work involves campus Benefits staff working with campus IT to run provided applicable SQL statements to identify the impacted population and review/resolve any issues. 

3. Associated costs relative to the scope of the change to requirements requested?

   Employee time to run the SQL(s), less than one hour.
   Employee time to audit for this issue, and to review and change the impacted population, if any. Time will vary by campus.

4. Training needs or if a straightforward change?

   Beginning immediately in 9.0, campus benefits staff should modify their data entry practices and use the Effective Date of the Personal History row as the same date for the actual Marital Status, Student, Disabled and/or Smoker effective date.

5. Implication on any other related process / functions?

   None.

6. Union impact. Meet & confer recommended?  (Yes or No)
   Additional comments (from Labor rep):

   No reasonably foreseeable impacts within the scope of bargaining identified at the time of review.

REVISION CONTROL

Revision History

Review/Approval History

End of Article