HCM - Data Validation - Bio/Demo

 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 for Bio/Demo
Bio/Demo Conversion Summary

Conversion testing includes the following areas:

  • Employee Bio/Demo  Person Information
  • Employee Bio/Demo  Citizenship and Birth Country
  • Employee Bio/Demo  Visa
  • Employee Bio/Demo  Ethnicity
  • Employee Bio/Demo  Disability data
  • Employee Emergency Contacts
Conversion Validation Due Date

The Validation Results and Feedback are due NO LATER THAN on the last day of the Data Validation Period. It is strong recommended that issues are reported EARLY in the validation period and are not held until the end of the period. Validation Results and Feedback are for reporting closure of the college's validation activities for the conversion cycle.  To view a list of the validation periods please refer to the timeline topic in the Data Validation Overview - Getting Started Guide.

Getting Help

Questions regarding the data validation activities can be directed to the Functional Analyst or HCM Project Manager.

Contact Name Contact Role
Contact Email
Jameeka Hill HCM Functional Analyst jhill@sbctc.edu
Sanjiv Bhagat  HCM Project Manager sbhagat@sbctc.edu
Conversion Detail - Bio/Demo

PeopleSoft has been populated with Employee Personal data extracted via conversion activity referred to as C-014.The chart below identifies the legacy data that will populate some of the specified PeopleSoft Bio/Demo fields.

For colleges live in PeopleSoft, a converting record that matches an existing PeopleSoft record can update the existing PeopleSoft record. Effective dated fields will have a new effective dated row added. Fields without effective dates may be overwritten based on the trumping logic identified in the Bio/Demo-Conversion Trumping Logic and Identifying Data Source section.

PeopleSoft Field PeopleSoft Field Description Legacy Source Sytem Legacy Source Record Legacy Extract Value / Source Field
EMPLID
Empl ID or ID EMP EMP-M
EMP-ID
EFFDT
Effective Date EMP
EMP-M
LAST-PAY-DATE
NAME
Name N/A
< blank >
LAST_NAME Last Name EMP
EMP-M
EMP-NAME-LAST
FIRST_NAME First Name EMP
EMP-M
EMP-NAME-FIRST
MIDDLE_NAME Middle Name EMP
EMP-M
EMP-NAME-MIDDLE
BIRTHDATE Date of Birth EMP
EMP-M
BIRTH-DATE
Sex Gender EMP
EMP-M
EMP-SEX
HIGHEST_EDU_LVL Highest Education Level EMP
EMP-M
HI-EDU-LVL
MAR_STATUS Marital Status EMP
EMP-M
FED-MARITL-STAT
LANG_CD Language Code N/A
< blank >
NATIONAL_ID Social Security Number EMP
EMP-M
EMP-ID
EFFDT Effective Date SM or TRAN YRQ-M or TRAN-YRQ-D.YRQ LAST-DAY-YRQ
EFF_STATUS Status as of Effective Date < translation >
"A"-active
COUNTRY Country EMP
EMP-ADDR-D "USA" or CNTRY (M), PERM-ADDR-CNTRY€
ADDRESS1 Address Line 1 EMP
EMP-M
ADDR-LN-1(M) or PERM-ADDR-LN-1(E)
ADDRESS2 Address Line 2 EMP
EMP-M
ADDR-LN-2(M) or PERM-ADDR-LN-2(E)
CITY City EMP, TBL2
EMP-ADDR-D, ZIP-TBL-M CITY(M), PERM-ADDR-CITY(E) or ZIP-TBL-M.CITY-CD
STATE State EMP, TBL2 EMP-ADDR-D, ZIP-TBL-M ST(M), PERM-ADDR-ST(E) or ZIP-TBL-M.ST
POSTAL Postal Code EMP EMP-M ZIP(M) or PERM-ZIP(E)
COUNTY County TBL2 COUNTY-TBL-M COUNTY
EMAIL_ADDR Email Address EMP EMP-ADDR-D EMAIL-ADDR
PHONE Telephone EMP EMP-M HOME-PHONE, WORK-PHONE
REG_REGION Regulatory Region N/A
"USA"
ETHNIC_GRP_CD Ethnic Group EMP EMP-M EMP-RACE-ARRAY and CENSUS-HISP-CD
Bio/Demo-Conversion Trumping Logic And Identifying Data Source

When reviewing Bio/Demo data it is important to have a basic understanding of the trumping logic of the various systems that are contributing to the PeopleSoft record. There are five data sources for Personal Information

  1. Current PeopleSoft record
  2. Legacy PPMS (Human Resources System)
  3. Legacy FAM (Student Financial Aid Data)
  4. Legacy SMS (Student Data)
  5. Legacy FMS (Student Financial Data)

If an active PeopleSoft record exists with active payroll, the PeopleSoft record will not be changed by the converting college. If a PeopleSoft student record exists with no active PeopleSoft employee data but includes current student enrollment the record coming in from the converting college will be evaluated for active payroll at the converting college, if no active payroll data exists then the PeopleSoft bio/demo record will not be changed by incoming bio/demo information.

When no existing PeopleSoft record is found, the incoming record will be based on the four legacy systems only. An active employee record with active payroll will take priority during conversion. If no employee record exists, the student record is evaluated pulling personal information first from FAM, then SMS and finally FMS based on the most recent activity from each of these systems.

When validating data it can be difficult to determine the source of the records, there are a few indicators that give clues to the source of the data

  1. If the ID number starts with a 1, the source was from a converting college HCM system or created in the PeopleSoft human resource system.
  2. Make sure to use “Include history” mode on pages where available and look for data with multiple rows. When you are in history mode you can look at the top right corner of the data box for a “View All” or “1 of 2” page viewer to review prior effective dated data. Fields with previous effective dated rows were existing in PeopleSoft and may or may not have been updated with converting data.
  3. Data with a 01/02/1901 effective date typically indicate a record that was existing in PeopleSoft and was updated by conversion, see section 3.1.3 for more details on effective dating.
Search/Match

When searching for matching records in PeopleSoft the system uses various data elements in combination to determine if the record is a match to an existing person record. The following list is the field combinations and order in which the system is searching for a matching record. If no match is found based on this set of parameters, a new Person ID is created for that record.

  1. National ID (SSN) + Birthdate
  2. External ID + Birthdate
  3. National ID (SSN) + First Name (first 3 characters)
  4. External ID + First Name (first 3 characters)
  5. Address + Last Name + Birthdate + Gender
  6. Last Name + First Name + Birthdate + Gender
  7. No Match Found
Effective Dating

For new records being created in PeopleSoft, the following Person Information records will create effective dated rows using the date of 01/01/1901:

  • NAMES
  • BIOGRAPHICAL HISTORY (Gender, Highest Education Level, Marital Status)
  • ADDRESSES
Biographical Details Tab Example

The Biographical History in PeopleSoft will have an effective date of 1/1/1901. In some cases there may be an effective date which is equivalent to the birthdate plus one day.

Birth Date Example

The Address Type will be Home and should have an effective date of 1/1/1901.

Contact Information Tab Example
Common Conversion Issues - Employee Bio/Demo

The format for birthdates in Legacy is MM/DD/YY. There are a few employees whose birthdates are within the 1920s (Ex: 2/1/23); based on the conversion logic the system will automatically default the century to a future century 2023 and will not be converted. These individuals will need to be manually entered into PeopleSoft if applicable.

  • Some Employees and Volunteers in the Legacy System do not have a birthdate entry. These individuals will need to be manually entered into PeopleSoft, if applicable.
  • The Effective Date in the Biographical History section will be 1 or 2 days after the birthdate.
  • Employees without a valid Date of Birth (DOB) in legacy will be converted with a “(blank)” DOB in PeopleSoft.
  • Employees without a gender designation will be converted as “Unknown” in PeopleSoft.
  • Employees without a valid SSN will not be converted.

 

If Bio/Demo does not match PPMS:

When available, review the Effective date of the PeopleSoft data to help determine if this record existed previous to conversion activity

  • Names
    • In order to load an employee record into PeopleSoft, it must contain both a first and last name. Records that do not have both a first and last name will not be loaded into Peoplesoft.
      • For any employee that does not have a last name in the legacy system, refer to the Data Cleanup document on the Canvas page.
      • If there are extra spaces between names the conversion may convert a blank for that name field
  • Address
    • If a Legacy student has quotation marks (“) in their legacy address, this will not show in PeopleSoft
      • This data does not need to be fixed in Legacy
    • Extra spaces in the address field can cause part of the address to not convert
    • Currently we only convert county data for Washington. We are looking at converting the County field for Oregon addresses using a crosswalk but this will not be available for Cycle 1.
  • Email Address
    • The email address is required for all employees. For employees the work email address should be the “Business” type and should be in the format of xxx@college.edu.

 

Bio/Demo Data Cleanup

Since ctcLink (PeopleSoft) is an integrated system, an employee must have a Personal record in order for a Job Data record to exist within the system. If there were errors within the PPMS fields, related to Bio Demo data, the individual was not converted successfully into Personal Information in PeopleSoft and a record will not exist for that individual.

For additional information regarding legacy clean up please refer to the Legacy (PPMS) Data Cleanup Information on Canvas, https://sbctc.instructure.com/courses/1620486/pages/dg3-hcm-bio-slash-demo-conversion-validation

The conversion dashboards for each college can be found on the cDR in the Restricted Files folder. Please contact your college Project Manager for the password to access your college’s files.

Minimum Validation Requirements

Data validators should review 100% of their converted records very closely, while making sure that most of the remaining population is correct at a glance.

Compare the following employee Bio/Demo data between PeopleSoft and Legacy:

  • Name
  • Date of Birth
  • Gender
  • Marital Status
  • Education Level
  • National ID (ex. SSN)
  • Address
  • Phone Number
  • Email Address
  • Ethnicity
  • Veterans Status (this is also where the Veteran Disability is stored)  
Conversion Validation Steps

These are the steps for validating conversion of Bio-Demo Data in PeopleSoft HCM.

Prepare the PeopleSoft Screens

The project team has created queries specific to the areas in which validation is required.

  1. Navigate to the Query Viewer page: NavBar > Navigator > Reporting Tools > Query > Query Viewer
  2. Click New Window in the upper right hand corner Click the Home button (it looks like a house and is located above the Help button in the upper right hand corner)
  3. At this point there should be two browser windows open, one with the Query Viewer, and one with the Personal Information page
Query Viewer Page Example
  1. In the Query Viewer window, click the drop down box next to Search By and select Folder Name. In the box next to “begins with”, type VALIDATION and click the Search button.
  2. The following queries should appear:
    • CTC_VALIDATE_PERS_INFO shows all Personal Information (Name, birthdate, contact information, etc.)
    • CTC_VALIDATE_EMERG_CNT shows all Emergency Contact Information (Contact Name, Phone #, etc.)
    • CTC_VALIDATE_CITIZ shows the Citizenship Status
    • CTC_VALIDATE_VISA shows specifically assigned Visa Types (i.e. Passport), if applicable
    • CTC_VALIDATE_DISABILITY shows the Disability Status of an employee, if applicable
  3. Click “Run to Excel” on any of the queries to run the query in a new window. This will allow sorting and filter capabilities. Make sure the pop up blocker in your browser is not blocking the ability to run the query.
  4. After selecting a query to run, use the magnifying glass in the query window to select a viable Company code from the table (or type in your college number), and Click “View Results” to display the Excel file output.
Query Results Example
Personal Information (Query CTC_VALIDATE_PERS_INFO)
  1. For this section of the Personal Information results, run the CTC_VALIDATE_PERS_INFO query shows all Personal Information (Name, birthdate, contact information, etc.) Compare to Legacy fields that can be found on screen PS0004 .
PS0004 Example
  1. It is important to view the Bio/Demo Records in PeopleSoft. To do so, use the following navigation in the new window: NavBar > Navigator > Workforce Administration > Personal Information > Modify A Person
  2. Enter the Empl ID (from the query) into the Empl ID field
  3. Click the Include History box
  4. Click the Search Button and the results be displayed
    • The page will display any effective dated rows for the data associated with the ID
    • If data was not updated by a converting record only one row will display
  5. Click the Biographical Details Tab to validate the Name, Biographic Info, Biographic History, and National ID.
  6. Click the Contact Information Tab to validate the Current Address, Phone Information, and Email Addresses.
Contact Information Tab Example
  1. Click the Regional Tab to validate the Ethnicity and Military Status (Disabled Veteran Status).

Note: The Veteran Section on this page will also display Military Disability Status. This is relative to Disability information.

Regional Tab Example
Conversion Detail - Citizenship

The Citizenship data was extracted via conversion activity referred to as C-047 and consisted of the following: The mapping of extracted citizenship data to PeopleSoft values is identified on the attached document below. The table below identifies the PeopleSoft field and the source field from legacy.

PeopleSoft Field PeopleSoft Field Description Legacy Source System Legacy Source Record Legacy Extract Value / Source Field
EMPLID Empl ID EMP EMP-M EMP-ID
COUNTRY Country EMP EMP-ADDR-D CITZ-CNTRY
CITIZENSHIP_STATUS Citizenship Status N/A
< blank >
Known Conversion Issues - Citizenship

There are no known issues for Citizenship data.

Citizenship Data Clean Up

It is suggested that any issues identified in this conversion cycle are resolved to be excluded from future conversion cycles.

Minimum Validation Requirements - Citizenship

Colleges should use the supplemental file they provided for Citizenship and select approximately 50 percent of the records to validate that Citizenship status was populated as expected in PeopleSoft.

Citizenship Conversion Validation Steps

Citizenship data in PeopleSoft is not effective dated and existing citizenship can be overwritten by a converting record based on the trumping rule applied.

  1. For the Citizenship results in PeopleSoft run the CTC_VALIDATE_CITIZ query. The data is stored on the Citizenship/Passport Tab, and should be compared to Legacy fields that can be found on screen PS0012.
PS0012 Example
  1. To View Citizenship in PeopleSoft use the following navigation in the new window: NavBar > Navigator > Workforce Administration > Personal Information > Citizenship > Identification Data
  2. Enter the Empl ID (from the query) into the Empl ID field
  3. Click the Include History box
  4. Click the Search Button and the results be displayed (the Citizenship/Passport Tab will display)
Citizenship/Passport Tab Example
Conversion Detail - Visa/Permit Data

The data was extracted via conversion activity referred to as C-047.

PeopleSoft Field PeopleSoft Field Description Legacy Source System Legacy Source Record Legacy Extract Value / Source Field
EMPLID Empl ID EMP EMP-M EMP-ID
COUNTRY Country N/A
USA
VISA_PERMIT_TYPE Visa/Permit Type EMP EMP-ADDR-D VISA-TYP
EFFDT Effective Date N/A
< blank >
VISA_WRKPMT_STATUS Visa/Permit Status < translation >
"G" - granted
STATUS_DT Status Date N/A
< blank >
DURATION_TYPE Type of Duration < translation >
"M"
EXPIRATN_DT Expiration Date EMP EMP-ADD-D VISA-EXPIR-DATE
Known Conversion Issues - Visa/Permit

There are no known issues with Visa/Permit data.

Visa/Permit Data Clean Up

It is suggested that any issues identified in this conversion cycle are resolved to be excluded in future conversion cycles.

Minimum Validation Requirements - Visa/Permit

Colleges should use the provided query and select a minimum of 50 records to validate that Visa/Permit status was populated as expected in PeopleSoft.

Visa/Permit Conversion Validation Steps

Visa/Permit data in PeopleSoft is effective dated and existing visa/permit cannot be overwritten by a converting record based on the trumping rule applied.

For the Visa results in PeopleSoft run the CTC_VALIDATE_VISA query. The data is stored on the Visa/Permit Data Tab, and should be compared to Legacy fields that can be found on screen PS0012.

PS0012 Example

To View Citizenship in PeopleSoft use the following navigation in the new window:

NavBar > Navigator > Workforce Administration > Personal Information > Citizenship > Identification Data

  1. Enter the Empl ID (from the query) into the Empl ID field
  2. Click the Include History box
  3. Click the Search Button and the results be displayed (the Citizenship/Passport Tab will display)
  4. Click the Visa/Permit Data Tab
Visa/Permit Data Tab Example
Conversion Detail - Disability Data

The data was extracted via conversion activity referred to as C-019. The table below identifies the PeopleSoft field and the source field from legacy. The data that is converted to PeopleSoft, will convert as a Disabled indicator.

Known Conversion Issues - Disability Data

There are no known issues with Disability data.

Disability Data Clean Up

It is suggested that any issues identified in this conversion cycle are resolved to be excluded in future conversion cycles.

Minimum Validation Requirements - Disability Data

Colleges should use the provided queries and select a minimum of 50 records to validate that disability data was populated as expected in PeopleSoft.

Disability Data Conversion Validation Steps

Disability data in PeopleSoft is not effective dated and existing disability data can be overwritten by a converting record based on the trumping rule applied.

  1. For the Disability results in PeopleSoft run the CTC_VALIDATE_DISABILITY query. The data is stored on the Disability Tab, and should be compared to Legacy fields that can be found on screen PS0004.
PS0004 Example
  1. To view the Disability indicator in PeopleSoft use the following navigation in the new window: Navbar > Navigator > Workforce Administration > Personal Information> Disability > Disabilities

1. Enter the Empl ID (from the query) into the Empl ID field

2. Click the Include History box

3. Click the Search Button and the results be displayed (Disability Tab will display)

Note: There are four tabs on this page, the Disability tab is the only reference for this validation. You can click on the arrow next to the Flag icon to expand the page for additional Disability Details related to Military Status.

Disability Tab Example
Conversion Detail - Emergency Contact Data

The data was extracted via conversion activity referred to as C-046 and consists of the Emergency Contact Name and Phone number.

Known Conversion Issues - Emergency Contacts

There are no known issues with Emergency Contacts.

Emergency Data Clean Up

It is suggested that any issues identified in this conversion cycle are resolved to be excluded in future conversion cycles.

Minimum Validation Requirements

Colleges should use the provided query and select a minimum of 50 percent of the records to validate that emergency contact information was populated as expected in PeopleSoft.

Emergency Contacts Conversion Validation Steps

For the Emergency Contact results in PeopleSoft run the CTC_VALIDATE_EMERG_CNT query. The data is stored on the Contact Address/Phone Tab, and should be compared to Legacy fields that can be found on screen PS0004.

PS0004 Example
  1. To View Emergency Contact Information in PeopleSoft use the following navigation in the new window: NavBar > Navigator > Workforce Administration > Personal Information > Personal Relationships > Emergency Contact
  2. Enter the Empl ID (from the query) into the Empl ID field
  3. Click the Search Button and the results will be displayed (Contact Address/Phone Tab will be displayed)
Contact Address/Phone Tab 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 CS Data Validation Cycle.

  • Summary: (Provide a title for the issue discovered)
  • Assigned To: Jameeka Hill
  • Status: (Leave as NEW)
  • College or Location : (Specify the college or district)
  • Priority: (Leave blank - not used)

0 Comments

Add your comment

E-Mail me when someone replies to this comment