HCM - Data Validation - Payroll

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 Payroll
Payroll Conversion Summary

Payroll conversion activities entail multiple levels of functionality in order to successfully convert Legacy data into PeopleSoft.  The following details the data validation necessary to ensure a quality conversion:

  • Employee Tax Data
  • Employee Direct Deposit Data
  • Employee General Deduction Data
  • Employee Additional Pay Data
  • Employee Earnings Balances
  • Employee Deduction Balances
  • Employee Tax Balances
  • Employee Deduction Arrears Balances
  • Employee Special Accumulator Balances

Other key conversion components needing validation are integrated in non-Payroll specific modules and are described below:

  • Job Data  Paygroup Assignment
  • Job Data  Employee Type (Salary, Hourly)
  • Job Data  Tax Location
  • Job Data  Shift Designation
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

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

Primary Contact: Dennis Sargent  ctcLink HCM Payroll Consultant: dsargent@sbctc.edu

Secondary Contact: Sanjiv Bhagat  ctcLink HCM Lead: sbaghat@sbctc.edu

Conversion Detail Information
Conversion Background - Employee Tax Data

The Employee Tax Data is coming from the PS0014 screen including the Marital Status, Exemptions, Additional Amount, etc.  The navigation within PeopleSoft that includes both the Federal & State Tax data is: NavBar > Navigator > Payroll for North America > Employee Pay Data USA > Tax Information > Update Employee Tax Data

There are multiple pages that represent the employee tax data within PeopleSoft as identified by the tabs located at the top of the component.

The Federal Tax Data page will show an employee’s tax information as it’s related to Federal Withholding.

The State Tax Data page will show an employee’s tax information as it’s related to State Withholding.

Note:  There could be multiple states listed for an employee as this is based on the Resident State vs. Work State.  In ctcLink (PeopleSoft) this is identified by the “Resident” and “UI Jurisdiction” check boxes in the State Tax Data page.  The Resident State is coming from the Employee’s State noted in their Home Address and the Work State is coming from the Employee’s Tax Location State noted in the Tax Location code associated with the employee within Job Data.

Known Conversion Issues

Since ctcLink (PeopleSoft) is an integrated system, an employee must have a record in Job Data in order to exist in the Employee Tax Data record within the system.  If an employee coming in from PPMS was not converted successfully into Job Data, they will not exist in the Employee Tax Data records.

Minimum Validation Requirements

Since the data contained in an employee’s tax data has a direct impact on an employee’s paycheck as it relates to the proper withholding of taxes, it is essential to validate each employee’s setup as it was converted from the legacy system (PPMS).  

Conversion Validation Background - Direct Deposit Data

The Direct Deposit Data is coming from the PS0014 screen including the Routing Number (Bank ID), Account Number, etc.  The navigation within PeopleSoft is: NavBar > Navigator > Payroll for North America > Employee Pay Data USA > Request Direct Deposit

Known Conversion Issues

Since ctcLink (PeopleSoft) is an integrated system, an employee must have a record in Job Data in order to exist in the Request Direct Deposit record within the system.  If an employee coming in from PPMS was not converted successfully into Job Data, they will not exist in the Direct Deposit Data record.

Minimum Validation Requirements

Since the data contained in an employee’s direct deposit data has a direct impact on an employee’s netpay distribution, it is essential to validate each employee’s setup as it was converted from the legacy system (PPMS).

Conversion Background - General Deduction Data

The General Deduction Data is coming from the PS0006 screen including the Deduction Code, Deduction Amount, etc.  The navigation within PeopleSoft is:

NavBar > Navigator > Payroll for North America > Employee Pay Data USA > Deductions > Create General Deductions

Note:  To view all of the general deductions converted for an employee, click the “View All” link on the top of the page or to view one by one, click the  arrow.

Known Conversion Issues

Since ctcLink (PeopleSoft) is an integrated system, an employee must have a record in Job Data in order to exist in the General Deduction Data record within the system.  If an employee coming in from PPMS was not converted successfully into Job Data, they will not data in the General Deduction Data record.

Minimum Validation Requirements

Since the data contained in an employee’s general deduction data has a direct impact on an employee’s paycheck, it is important to validate each employee’s setup as it was converted from the legacy system (PPMS).

Conversion Background - Additional Pay Data

The Additional Pay Data is coming from the Job Account (PS0003) screen.  The navigation within PeopleSoft is: NavBar > Navigator > Payroll for North America > Employee Pay Data USA > Create Additional Pay

Note:  To view all of the additional pay earnings converted for an employee, click the “View All” link on the top of the page or to view one by one, click the  arrow.

Known Conversion Issues

Since ctcLink (PeopleSoft) is an integrated system, an employee must have a record in Job Data in order to exist in the Additional Pay Data record within the system.  If an employee coming in from PPMS was not converted successfully into Job Data, they will not data in the Additional Pay Data record.

The criteria for converting Additional Pay Data from PPMS is:

  • End Date in PS0003 is current or future.
  • Earnings are not REG,FTF,PTF or OVT.
  • Data exists within PS0003, if no data exists then Additional Pay Data in ctcLink will not be populated.
Minimum Validation Requirements

Since the data contained in an employee’s additional pay data has a direct impact on an employee’s paycheck, it is important to validate each employee’s setup as it was converted from the legacy system (PPMS).

Conversion Background - Earnings Balances

The Earnings Balances are converted using the PAYR record within PPMS.  The Quarter to Date (QTD) and Year to Date (YTD) for Hours and Earnings are generated based on the accumulative Months (Periods) for each Quarter (i.e. January  March (Period 1-3) hours and earnings are summed up to create the QTD hours and earnings for Quarter 1,etc. ) during conversion.  For conversion from PPMS, we are converting current year balances and the previous 2 years (i.e. 2018 (Current Year), 2017 & 2016 will also be converted).  The navigation within PeopleSoft is: NavBar > Navigator > Payroll for North America > Periodic Payroll Events USA > Balance Reviews > Earnings

Note:  To view all of the Earnings Type for all balance years converted for an employee, click the “View All” link on the top of the page or to view one by one, click the  arrow. To view all of the Earnings Types for a specific balance year, click the “View All” link located on the “Balance Details” section.

Known Conversion Issues

Since ctcLink (PeopleSoft) is an integrated system, an employee must have a record in Job Data in order to exist in the Deductions Balance record within the system.  If an employee was terminated prior to the current year, but had earnings in the previous 2 years, the balances will be converted however the employee will need to be manually hired in Job Data in order to view the balances.

Minimum Validation Requirements

Since we will be converting current year and the previous 2 years of earnings balances, from a data validation perspective we will concentrate on the current year.

Conversion Background - Deduction Balances

The Deduction Balances are converted using data from the PS0008 screen within PPMS.  The Quarter to Date (QTD) and Year to Date (YTD) for Deductions are generated based on the accumulative Months (Periods) for each Quarter (i.e. January  March (Period 1-3) deductions are summed up to create the QTD deductions for Quarter 1,etc. ) during conversion.  For conversion from PPMS, we are converting current year balances and the previous 2 years (i.e. 2018 (Current Year), 2017 & 2016 will also be converted).  The navigation within PeopleSoft is: NavBar > Navigator > Payroll for North America > Periodic Payroll Events USA > Balance Reviews > Deductions

Note:  To view all of the Deductions for all balance years converted for an employee, click the “View All” link on the top of the page or to view one by one, click the  arrow. To view all of the Deductions for a specific balance year, click the “View All” link located on the “Balance Details” section.

Known Conversion Issues

Since ctcLink (PeopleSoft) is an integrated system, an employee must have a record in Job Data in order to exist in the Deductions Balance record within the system.  If an employee was terminated prior to the current year, but had deductions in the previous 2 years, the balances will be converted however the employee will need to be manually hired in Job Data in order to view the balances.

Minimum Validation Requirements

Since we will be converting current year and the previous 2 years of deduction balances, from a data validation perspective we will concentrate on the current year.

Conversion Background - Tax Balances

The Tax Balances are converted using the PS0008 screen within PPMS.  The Quarter to Date (QTD) and Year to Date (YTD) for Taxes are generated based on the accumulative Months (Periods) for each Quarter (i.e. January  March (Period 1-3) taxes (deductions) are summed up to create the QTD taxes for Quarter 1,etc. ) during conversion.  For conversion from PPMS, we are converting current year balances and the previous 2 years (i.e. 2018 (Current Year), 2017 & 2016 will also be converted).  The navigation within PeopleSoft is: NavBar > Navigator > Payroll for North America > Periodic Payroll Events USA > Balance Reviews > Taxes

Note:  To view all of the Tax Balances for all balance years converted for an employee, click the “View All” link on the top of the page or to view one by one, click the arrow. To view all of the Tax Balances for a specific balance year, click the “View All” link located on the “Balance Details” section.

Known Conversion Issues

Since ctcLink (PeopleSoft) is an integrated system, an employee must have a record in Job Data in order to exist in the Tax Balance record within the system.  If an employee was terminated prior to the current year, but had deductions in the previous 2 years, the balances will be converted however the employee will need to be manually hired in Job Data in order to view the balances.

Minimum Validation Requirements

Since we will be converting current year and the previous 2 years of tax balances, from a data validation perspective we will concentrate on the current year.

Conversion Background - Deduction Arrears Balances

The Arrears Balances are converted using the PS0018 screen within PPMS.  The navigation within PeopleSoft is: NavBar > Navigator > Payroll for North America > Periodic Payroll Events USA > Balance Reviews > Arrears

Note:  To view all of the Arrears balances for all arrears balances converted for an employee, click the “View All” link in the “Balance Details” section or to view one by one, click the arrow.

Known Conversion Issues

Since ctcLink (PeopleSoft) is an integrated system, an employee must have a record in Job Data in order to exist in the Arrears record within the system.

Minimum Validation Requirements

Since the arrears balances directly impact an employees payroll, it’s essential to validate all employees with arrears balances.

Conversion Validation Steps

The sections below will highlight the data validation steps for the Payroll conversion activities.

Prepare The PeopleSoft Screens

The project team has created queries specific to the areas in which validation is required. Start by navigating to the Query Viewer page: NavBar > Navigator > Reporting Tools > Query > Query Viewer

  • 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)
  • At this point there should be two browser windows open, one with the Query Viewer, and one with the Personal Information page
Run The Validation Queries And Compare To Legacy

In the Query Viewer window, click the drop down box next to Search By and select Query Name.  In the box next to “begins with”, type PR%VAL and click the Search button.

The following queries should appear:

  • PR_EE_TAX_DATA_VAL  shows all Federal & State Tax Data for the converted employees
  • PR_DIRECT_DEPOSIT_VAL  shows all Direct Deposit Data for the converted employees, if applicable
  • PR_EE_GENL_DED_VAL  shows the General Deduction Data for the converted employees, if applicable
  • PR_EE_ADDL_PAY_VAL  shows the Additional Pay Data for the converted employees, if applicable
  • PR_EE_EARN_BAL_VAL  shows the Earnings Balances for the converted employees. This is only needed if you wish to run for prior years. Current year balance data will be provided. Please keep in mind that for the previous year balances, if the employee wasn't converted into Job Data, no balances will be returned in the query.
  • PR_EE_DED_BAL_VAL  shows the Deduction Balances for the converted employees. This is only needed if you wish to run for prior years. Current year balance data will be provided. Please keep in mind that for the previous year balances, if the employee wasn't converted into Job Data, no balances will be returned in the query.
  • PR_EE_TAX_BAL_VAL  shows the Tax Balances for the converted employees. This is only needed if you wish to run for prior years. Current year balance data will be provided. Please keep in mind that for the previous year balances, if the employee wasn't converted into Job Data, no balances will be returned in the query.
  • PR_EE_DED_ARREARS_BAL_VAL shows the Deduction Arrears balances for the converted employees, if applicable
  • PR_EE_PAY_JOB_DATA_VAL shows the Payroll related data that is stored in the Job Data record

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.

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) where applicable, and Click “View Results” to display the Excel file output.

Due to the size of the data in some of the query results (i.e. Earnings Balances), if you receive a message stating that the “Query Results are too large…”, in order to obtain the full query results the query will need to be scheduled to run.

To schedule a query click on the “Schedule” link associated with the selected query:

The first 2 fields are defaulted and no changes are needed.  The Run Control ID field will need to be populated.  For the purposes of scheduling a validation query, please enter 1 in this field then click on the “Add” button.

The following page will appear:

This will provide the prompt values used by the query. In the Earnings Balance query the prompts will be for Company (140 for Clark, 890 for State Board, etc.).  The Year refers to the Calendar Year in the CCYY format (i.e. 2018).  Click the OK button once the values have been entered.

Verify that the prompt values are correct.  If you need to update the values, you can click on the “Update Parameters” hyperlink and change the values as needed.  If values are correct, click on the Apply button.

Click OK to run the query.

Click on the Process Monitor hyperlink to monitor the status of the process running:

Once the Run Status is “Success” and the Distribution Status is “Posted” (click on the Refresh button occasionally to update the Run Status and Distribution Status), click on the Details hyperlink:

Click on the View Log/Trace hyperlink:

Click on the query name with the extension of .csv and the query will open up in Excel.  If you prefer to download the file first, right click on the file name and select the “Save Target As” option (this may vary based on the browser/version you are using).

Employee Tax Data (Query: PR_EE_TAX_DATA_VAL)

To view the Employee Tax Data for Federal Tax Data (W4) in PeopleSoft use the following navigation: NavBar > Navigator > Payroll for North America > Employee Pay Data USA > Tax Information > Update Employee Tax Data

  1. Enter the Emplid from the query result into the Empl ID field
  2. Click the Search button
  3. Compare the data converted from PPMS using the PS0014 screen.

Note: For State Tax Data, click the State Tax Data tab at the top of the page.

Direct Deposit Data (Query: PR_DIRECT_DEPOSIT_VAL)

To view the Direct Deposit Data in PeopleSoft use the following navigation: NavBar > Navigator > Payroll for North America > Employee Pay Data USA > Request Direct Deposit

  1. Enter the Emplid from the query result into the Empl ID field
  2. Click the Search button
  3. Compare the data converted from PPMS using the PS0014 screen.
General Deduction Data (Query: PR_GENL_DED_DATA_VAL)

To view the General Deduction Data in PeopleSoft use the following navigation: NavBar > Navigator > Payroll for North America > Employee Pay Data USA > Deductions > Create General Deductions

  1. Enter the Emplid from the query result into the EmplID field
  2. Click the Search button
  3. Compare to PPMS using the PS0006 screen.
Additional Pay Data (Query: PR_EE_ADDL_PAY_VAL)

To view the Additional Pay Data in PeopleSoft use the following navigation: NavBar > Navigator > Payroll for North America > Employee Pay Data USA > Create Additional Pay

  1. Enter the Emplid from the query result into the EmplID field
  2. Click the Search button
  3. Compare to PPMS using the PS0003 screen.
Earnings Balances (QUERY: PR_EE_EARN_BAL_VAL)

To view the Earnings Balances in PeopleSoft use the following navigation: NavBar > Navigator > Payroll for North America > Periodic Payroll Events USA > Balance Reviews > Earnings

  1. Enter the Emplid from the query result into the EmplID field
  2. Click the Search button

Note:  The Earnings Balances in PeopleSoft also include the Special Accumulator Balances that can be compared using the PS0010 screen in PPMS. When reviewing the queried results there is a Y/N value in the Special Balance column that with a value of Y indicates a special accumulator balance.

Deduction Balances (Query: PR_EE_DED_BAL_VAL)

To view the Deduction Balances in PeopleSoft use the following navigation: NavBar > Navigator > Payroll for North America > Periodic Payroll Events USA > Balance Reviews > Deductions

  1. Enter the Emplid from the query result into the EmplID field
  2. Click the Search button
  3. Compare to PPMS using the PS0008 screen.
Tax Balances (Query: PR_EE_TAX_BAL_VAL)

To view the Tax Balances in PeopleSoft use the following navigation: NavBar > Navigator > Payroll for North America > Periodic Payroll Events USA > Balance Reviews > Taxes

  1. Enter the Emplid from the query result into the EmplID field
  2. Click the Search button
  3. Compare to PPMS using the PS0008 screen.
Arrears Balances (Query: PR_EE_DED_ARREARS_BAL_VAL)

To view the Arrears Balances in PeopleSoft use the following navigation: NavBar > Navigator > Payroll for North America > Periodic Payroll Events USA > Balance Reviews > Arrears

  1. Enter the Emplid from the query result into the EmplID field
  2. Click the Search button
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: Dennis Sargent
  • 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