HCM - Data Validation - Benefits

Purpose: This document is to provide colleges instructions on how to perform the validation of data in the PeopleSoft environment.

Colleges are being asked to review data that was converted from the legacy environment to PeopleSoft.  The goal of data validation is to ensure legacy data was accurately converted into PeopleSoft.  College Subject Matter Experts (SMEs) are included in data validation as a learning experience in navigating the PeopleSoft application, selecting records to review, and reporting issues discovered.

College executives are asked to support the time and effort expended by college SMEs necessary to complete the following:

  • Develop a familiarity with navigating the relevant PeopleSoft menus and pages.
  • Review selected records, comparing legacy data to the records as converted into PeopleSoft.
  • Report issues discovered after careful review of converted data.
  • Monitor resolution of reported issues for review in the next conversion testing round.
  • Develop an approach for self-managing the data validation and issue reporting process in subsequent rounds of data conversion testing.

It is expected that College Data Validation SMEs will have already read the Data Validation Overview guides before beginning their validation activities:

Overview of Data Validation

Benefits Data comes from a combination of Legacy and the HCA (Health Care Authority).  HCA is the source of record for Health, Dental, Smoker information, and Dependent data.  Legacy is the source for all the other Benefit enrollments.  For conversion purposes we only load the HCA smoker and dependent information.

Life AD&D enrollment was shifted to MetLife and in PeopleSoft is considered a General Deduction.  As such, Life now falls under Payroll conversion.

Benefit Program enrollment falls under employment data and will be converted when other Job information is converted.  DRS calendar information is stored in Job Data and will also come over with the employment information.  The calendars themselves will be constructed manually based off the DRS calendar entries in Job.

PrePay balances and calendar enrollment come in under the Payroll balance conversion and will be loaded at the time Payroll balance conversion occurs.  PrePay Calendars from Legacy are also loaded during conversion.  Benefits validation requires users to be aware of many disparate elements.

Getting Help

Should a person performing validation for Benefits need to reach out with questions they are encouraged to contact project staff.

Primary Contact: Dennis Sargent, HCM Functional Analyst: dsargent@sbctc.edu

Secondary Contact: Sanjiv Bhagat, HCM Functional Lead: sbhagat@sbctc.edu

Conversion Detail Information
Conversion Background

Benefit Program Enrollment (C-036)

BEN_PROG_PARTIC table in PeopleSoft will be populated at the time employment data is populated.  It will correspond to the Insurance Eligibility Indicator from Legacy that is found on the employment screen.

The Benefit Record Number lists the college where the employee has benefits.

Benefit Program Enrollment is determined by the Insurance Elig indictaor on screen PS0005 in Legacy.

Health/Dental Enrollment (C-008)

Legacy supplies this information and it is loaded into the HEALTH_BENEFIT table in PeopleSoft through a component interface.

The data you are validating should agree with HCA as HCA is the system of record.  If you find errors while working through this with PS0005, you need to correct HCA’s record as well.  Information not in synch with HCA may be overwritten in the future by the HCA data load.

Smoker Enrollment (C-008)

HCA supplies this information and it is loaded into the SIMPLE_BENEFIT table in PeopleSoft through a component interface.  A file is provided directly from the HCA for this information.

The data you are validating is coming from HCA as HCA is the system of record.  If you find errors in comparing with PS0005, you need to check HCA’s record.  You may wish to correct PS0005 so that information is in sync with HCA.

Dependent and Beneficiary Data (C-009)

HCA supplies this information and it is loaded into the DEP_BEN, DEP_BEN_NAME, DEP_BEN_ADDR, DEP_BENEF_NID, and DEP_BEN_EFF tables in PeopleSoft through a component interface.  A file is provided directly from the HCA for this information.

The Dependent/Beneficiary data is being sent to us from HCA.  Legacy only stores the number of dependents, and that can be found on screen PS0005.  This should be in sync with the HCA data in Pay1.  Primarily this information will help the user troubleshoot problems with Health and Dental enrollment when there are coverage code mismatches.

Savings Plans (403(b) and 457) Enrollment (C-008)

Institutions, by company code and EMPLID, provide this enrollment information which is loaded into the SAVINGS_PLAN table in PeopleSoft through a component interface.

You will not use the sections on assign beneficiaries, investments or rollover options.

Deductions for 403(b)’s (both TSA’s/SRA’s and Roth) and 457’s (Deferred Compensation) are from the Miscellaneous Deduction Screen (PS0006) in Legacy.

Retirement Enrollment (C-008)

Institutions, by company code and EMPLID, provide this enrollment information which is loaded into the RTRMNT_PLAN table in PeopleSoft through a component interface.

Retirement Plans are from the Benefits Screen (PS0005) in Legacy.  If there is a blank here due to new hire processing then Retirement Plan will not load anything for that employee during conversion.

Spending Accounts (FSA/HSA/Dep Care) Enrollment (C-008)

Institutions, by company code and EMPLID, provide this enrollment information which is loaded into the FSA_BENEFIT table in PeopleSoft through a component interface.

Deductions for Flexible Spending and Dependent Care are from the Miscellaneous Deduction Screen (PS0006) in Legacy.  Balance information comes from Payroll balance information and was loaded through Payroll conversion.

Disability (LTD and Optional LTD) Enrollment (C-008)

Institutions, by company code and EMPLID, provide this enrollment information which is loaded into the DISABILITY_BEN table in PeopleSoft through a component interface.

Basic and Optional LTD can be found on Legacy screen PS0005.

Retirement Fields Conversion (C-008)

The employee retirement information is mapped to the appropriate fields (i.e. it will indicated TIAA enrollment if they are in TIAA or DRS enrollment if they are in DRS).  There is a retiree indicator that will translate from Legacy to the custom field in PeopleSoft (in CTC_RTRMNT_FLDS).

Retirement Fields information is located in Legacy on screen PS0005.

ACA Status Conversion (C-008)

ACA status is stored in a miscellaneous screen in Legacy.  The correct ACA Status will need to be checked that it was correctly translated to PeopleSoft (in CTC_BEN_ACA_STS).

ACA Status informed is located in Legacy on screen PS0035.

Employee PrePay Conversion (C-052)

Employees will maintain their relationship to existing PrePay Calendars as well as any current balances pending.  These are moved as part of Payroll balance conversion.  

Custom tables involved are CTC_EPREPAY_EMP, CTC_EPREPAY_BAL.

PrePay Cycle information is located in Legacy on screen PS0014.  PrePay balance information is located in Legacy on screen PS0008.

PrePay Calendar Conversion (E-194)

PrePay Calendars will be brought from Legacy but will not be populated with deduction codes.  Deduction mapping will be completed by Project but the deduction codes on individual calendars will be a manual task to be completed by colleges.  

Custom tables involved are: CTC_PREPAY_CAL, CTC_PREPAY_CALD, CTC_PREPAY_CALP, CTC_PREPAY_CALE, CTC_PREPAY_CALS.

DRS Calendars (C-036)

DRS Calendars exist in Legacy but are not being converted.  They must be indicated in Homework and rebuilt.  The codes indicated on Job Data, however, do get converted as part of Job Data and need validation.  The calendar can be found in Legacy in the employment screen.

Custom tables involved are: CTC_DRS_CAL, CTC_DRS_CAL_MO.

DRS Calendar is located in Legacy on screen PS0002.

Life AD&D Enrollment

Since 2017, when MetLife took over Life enrollment from the HCA this became a general deduction in PeopleSoft and is handled in the Payroll deduction conversion.  Some history for First Link exists in the LIFE_ADD_BEN table, but no history is being converted in future deployment groups.

Crosswalks

The BENEFIT_PLAN crosswalk maps Legacy values to PeopleSoft values for: LTD, Optional LTD, Retirement.

The CTC_PLAN_TYPE_STG crosswalk maps Legacy values to PeopleSoft values for all plan types in Benefits.

The RELATIONSHIP crosswalk maps Legacy values to PeopleSoft values for Dependent/Beneficiary relationships.

Legacy Screens

The screens needed in Legacy for Benefits validation are the following:

  • PS0005 Benefits Screen
  • PS0006 Miscellaneous Deduction Screen
  • PS0035 Employee Miscellaneous Screen
  • PS0002 Job Status Screen
  • PS0014 Payroll Information Screen
  • PS0008 Deduction Balances Screen
  • HCA’s Pay1 information may also need to be referenced as they are the source of record for certain Benefits, however Pay1 does not have a test site or test login so the actual (production) HCA site must be used for validation as needed
Common Conversion Issues

Benefits kick outs are often a result of Job Data or person data not aligning with other values.  If there is no Job there cannot be Benefits enrollments.  If a Job is Terminated, Benefit enrollments can still show up as errors.

The Coverage Code (i.e. single vs family etc) is assigned in PeopleSoft for Health based on the number and type of dependents.  If there is disagreement on the Coverage Code or on the number and type of dependents then the HCA file must be cross referenced with Legacy to determine the truth, and those compared to PeopleSoft for corrections.

Domestic Partners being identified in PeopleSoft as a “Spouse” can cause errors at the conversion level.  This population needs special attention when validating.  Kickouts can be seen on the error dashboard accessible by college PMs in the cDR (ctcLink Document Repository).

Smoker status can be seen in two places in PeopleSoft, one in BioDemo and one in Simple Benefits enrollment screens.  The correct validation location should be the Pay1 (HCA) and deduction screens, as status may or may not relate to the actual truth of whether this person should be charged for the smoker (or spousal) surcharge (i.e. they are in Pay1).  There was confusion surrounding smokers with the first Go Live for First Link.  The smoker indicator in BioDemo does not relate to either enrollment or deductions in PeopleSoft.

PrePay Overrides often contain deductions that are not allowed due to deduction mapping being benefits only.  For kicked out PrePay Override deductions the Payroll balance conversion must be consulted.

Minimum Validation Requirements

It’s probably a good idea to look at about 75% of the records (minimum) very closely, while making sure that most of the population is correct at a glance.  Known exceptions to rules are always good to take a look at in any cycle to try to note manual corrections that are needed.  The earlier corrections are caught, the less work later.

Smokers must have 100% validation in this cycle due to past issues with the smoker/spousal surcharge load.

Conversion Validation Steps
Prepare The PeopleSoft Screens
NavBar Example

In the upper right hand corner of the screen note the following:

  • Home (it’s a house icon), this will return the user to the Main Menu links
  • New Window, this link will create a new window so the user can view multiple screens at once
  • Navigation Bar (also known as "NavBar," it’s the compass icon), this is another way to access the main menu

Navigate to Query Viewer by clicking on the following:

  1. NavBar > Navigator > Reporting Tools > Query > Query Viewer
  2. Click New Window in the upper right hand corner
  3. Click the Home button

At this point there should be two browser windows open, one with the Query Viewer, and one with the Main Menu links

  • The user can open multiple New Windows if they want to compare multiple screens simultaneously
Run Validation Queries And Compare To Legacy
  1. In the Query Viewer window click the drop down box next to Search By and select Folder Name
    • Next to “begins with” type: VAL BENEFITS
  2. The following queries should appear:
    • BN_VAL_ACA_STATUS  This is a custom table that holds the ACA status of employees
    • BN_VAL_BENEFIT_PROG_TBL   This is a delivered table that determines which program an employee is enrolled in: SB0, SB1, SBA, SB2
    • BN_VAL_DEP_BEN  This is a delivered table that holds the dependent information from Pay1
    • BN_VAL_DISABILITY_TBL  This is a delivered table that holds the basic long term and optional disability enrollments
    • BN_VAL_EMP_PREPAY_CYCLE  This is a custom table that holds the employee level assigned prepay cycle
    • BN_VAL_EMP_PREPAY_OVR  This is a custom table that holds the employee level assigned prepay cycle overrides
    • BN_VAL_HEALTH_BEN_TBL  This is a delivered table that stores the health and dental enrollments from Legacy
    • BN_VAL_JOB_DRS_AUDIT  This is a custom table appended to Job Data that holds the DRS Calendar and Contract Begind and End dates needed for redistribution for faculty
    • BN_VAL_PREPAY_BAL  This is a custom table that holds the prepay balance currently held and intended to be passed to vendors over the course of payrolls in the future
    • BN_VAL_RETIREMENT_FLDS  This is a custom table that holds data needed for TIAA CREF and DRS that cannot be stored in the delivered table
    • BN_VAL_RETIREMENT_TBL  This is a delivered table that stores retirement enrollment
    • BN_VAL_SAVINGS_PLAN_TBL  This is a delivered table that stores 457 and 403(b) enrollment
    • BN_VAL_SIMPLE_BEN_TBL  This is a delivered table where we are storing the smoker surcharges from Pay1
    • BN_VAL_SPENDING_ACCT_TBL  This is a delivered table where we are storing FSA/HSA/Dependent Care enrollments
  3. Click “Run to HTML” on any of the queries to run the query in a new window
    • Make sure your pop up blocker in your browser is not blocking the ability to run the query
  4. Use the magnifying glass in the query window to select a viable Company code from the table (or type in your college number)
  5. Click View Results
Query Viewer Results Example
Check Validation Queries Against PeopleSoft Screens
  1. The most reliable way to use these PeopleSoft screens is to fill in the EMPLID and click Search in the initial search screen
    • This value will be indicated in the queries along with the user’s first and last name
  2. These PeopleSoft screens will contain the same information as the query, but it will be presented to you in the format you will interact with most frequently in PeopleSoft
    • It is not recommended to complete the entire validation EMPLID by EMPLID, the best way to validate is to look at the query output, and then come to the PeopleSoft screens to look at special cases, or employees that have potential errors
  3. In the screen Enroll In Benefits the following navigation corresponds to the following queries:
    • NavBar > Navigator > Benefits > Enroll in Benefits > Assign to Benefit Program
    • Assign to Benefit Program is to be used with BN_VAL_BENEFIT_PROG_TBL
      • The effective date will be the earliest relevant hire date from Job Data (a Job must exist to enroll them in Benefits)
    • In Legacy this should be compared to the Insurance Elig indicator on screen PS0005
      • If Insurance Elig indicator is D or E it will appear as SB1
      • All other indictaor values with appear as SB0 unless there is a Retirement Plan (SB2)
  • NavBar > Navigator > Benefits > Enroll in Benefits > Simple Benefits
  • Simple Benefits is to be used with BN_VAL_SIMPLE_BEN_TBL
  • In Legacy, this should be compared to both the Pay1 surcharge indicators and the Surcharges indicators in PS0005.  Pay1, Legacy, and PeopleSoft should all agree.
  • NavBar > Navigator > Benefits > Enroll in Benefits > Health Benefits
  • Health Benefits is to be used with BN_VAL_HEALTH_BEN_TBL
    • There are 2 rows in Health, and in the Plan Type box the user will need to use the arrows in the upper right hand corner to navigate between Dental and Health enrollments
    • Dependents will also display here.  This is the same as what will display in query BN_VAL_DEP_BEN, however, typically more than just Health eligible people’s dependents are sent over by Pay1 so the query was built for a deeper look
  • In Legacy, this should be compared to both the Pay1 information and the Health Insurance box in PS0005.  # DEPEND should also match.  Pay1, Legacy, and PeopleSoft should all agree.
Health Benefits/PS0005-010 Example
  • NavBar > Navigator > Benefits > Employee/Dependent Information > Review Dep/Ben Summary
  • Review Dep/Ben Summary is to be used with BN_VAL_DEP_BEN
  • In Legacy, screen PS0005 will show the # DEPEND, but Pay1 is the system of record for this information.  Pay1 should agree with PeopleSoft.
    • As some people who are not (or no longer) Benefit eligible could have dependents listed in Pay1, employees who are not SB1 could also have dependents while not having a health enrollment.  This query exists to catch those who are not SB1 (who will be validated in the Health enrollment screen).
  • NavBar > Navigator > Benefits > Enroll in Benefits > Disability Benefits
  • Disability Benefits is to be used with BN_VAL_DISABILITY_TBL
    • There can exist multiple rows for an employee, should the employe have both Basic and Optional LTD.  To navigate to the additional row, the user will need to use the arrows in the upper right hand corner of the Plan Type box.
  • In Legacy, anyone with the Insurance Elig value that indicates D or E in PS0005 then a Basic LTD row will exist in PeopleSoft
    • In Legacy, Optional LTD will be indicated in the Long Term Disability area in PS0005
  • NavBar > Navigator > Benefits > Enroll in Benefits > Savings Plans
  • Savings Plans is to be used with BN_VAL_SAVINGS_PLAN_TBL
  • In Legacy, on screen PS0006 there will be a deduction that will correspond to the Savings Plan enrollment as this will no longer be purely a deduction but a Benefit Plan going forward that then results in a deduction in Payroll
  • NavBar > Navigator > Benefits > Enroll in Benefits > Spending Accounts
  • Spending Accounts is to be used with BN_VAL_SPENDING_ACCT_TBL
  • In Legacy, Spending Account information can be found on PS0006 as a deduction, with the Declining Balance being used for the Annual Plege in PeopleSoft.  Like Savings Plans, this will no longer purely exist as a deduction, but instead as a Benefit enrollment which will in turn create a Payroll deduction.
    • YTD contributions should be validated in Payroll as part of the Payroll balance validation but will display here as a read only field.
  • NavBar > Navigator > Benefits > Enroll in Benefits > Retirement Plans
  • Retirement Plans is to be used with BN_VAL_RETIREMENT_TBL
  • In Legacy, this should be compared to the Retirement section at the bottom of PS0005.  The EFF DATE in Legacy will map directly to the Election Date in PeopleSoft
  • There are additional menu items that are validated at :
    • NavBar > Navigator > Benefits > CTC Custom > Assign ACA Status
    • Assign ACA Status is to be used with BN_VAL_ACA_STATUS
    • In Legacy, this should be a value indicated on screen PS0035 under ACA EMPL STATUS
  • NavBar > Navigator > Benefits > CTC Custom > Retirement Fields
  • Retirement Fields is to be used with BN_VAL_RETIREMENT_FLDS
  • In Legacy, this should be compared to the Retirement portion at the bottom of screen PS0005
  • To validate PrePay the following menu trail must be followed:
    • NavBar > Navigator > Payroll for North America > CTC Custom > Employee PrePay
    • Employee PrePay is to be used with BN_VAL_EMP_PREPAY_CYCLE, BN_VAL_EMP_PREPAY_OVR, and BN_VAL_PREPAY_BAL
    • In Lagacy, the PrePay Cycle is the MAIN PAY CYCLE on PS0014.  The actual deduction information for the balances in PS0008 as a DECLINING BALANCE.
Employee PrePay/PS0014-004 Example
  • To validate DRS Job Data information the following menu trail must be followed:
    • NavBar > Navigator > Workforce Administration > Job Information > Job Data
    • CTC Job Data tab must be used with BN_VAL_JOB_DRS_AUDIT
    • In Legacy, this should be compared to the CAL CD on screen PS0002
  • To validate PrePay Calendars converted over the following menu trail must be followed:
    • NavBar > Navigator > Set Up HCM > CTC Custom > PrePay Calendars
    • Select the appropriate Company in the lookup and click Search
    • Click on each PrePay Calendar and make sure the PrePay Calendar and PrePay Schedule tabs are correct
PrePay Calendar & Schedule Example
Submitting Data Validation Results

College Data Validation SMEs will communicate any conversion issues they encounter during their data validation via the Oracle Test Manager (OTM) application. The ctcLink Testing Team will provide OTM  orientation materials before the data validation period for any College Data Validation SMEs who are not familiar with the tool.  Please refer to the  Data Validation Overview - Reporting Issues guide for more information on using OTM.

When logging a new issue in OTM related to data being validated through this guide, remember to use the following values for these specific required fields in your issue entry under the project tied to this Deployment Group and Data Validation Cycle

Also, upon completion of data validation, please ensure feedback has been logged in OTM.

  • Project:
  • College or Location: (College Name)
  • Assigned To: Dennis Sargent

0 Comments

Add your comment

E-Mail me when someone replies to this comment