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:
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
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
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.
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: [email protected]
Secondary Contact: Sanjiv Bhagat ctcLink HCM Lead: [email protected]
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.
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.
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).
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
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.
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).
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.
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.
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).
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.
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.
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).
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Since the arrears balances directly impact an employees payroll, it’s essential to validate all employees with arrears balances.
The sections below will highlight the data validation steps for the Payroll conversion activities.
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
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).
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
- Enter the Emplid from the query result into the Empl ID field
- Click the Search button
- 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.
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
- Enter the Emplid from the query result into the Empl ID field
- Click the Search button
- Compare the data converted from PPMS using the PS0014 screen.
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
- Enter the Emplid from the query result into the EmplID field
- Click the Search button
- Compare to PPMS using the PS0006 screen.
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
- Enter the Emplid from the query result into the EmplID field
- Click the Search button
- Compare to PPMS using the PS0003 screen.
To view the Earnings Balances in PeopleSoft use the following navigation: NavBar > Navigator > Payroll for North America > Periodic Payroll Events USA > Balance Reviews > Earnings
- Enter the Emplid from the query result into the EmplID field
- 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.
To view the Deduction Balances in PeopleSoft use the following navigation: NavBar > Navigator > Payroll for North America > Periodic Payroll Events USA > Balance Reviews > Deductions
- Enter the Emplid from the query result into the EmplID field
- Click the Search button
- Compare to PPMS using the PS0008 screen.
To view the Tax Balances in PeopleSoft use the following navigation: NavBar > Navigator > Payroll for North America > Periodic Payroll Events USA > Balance Reviews > Taxes
- Enter the Emplid from the query result into the EmplID field
- Click the Search button
- Compare to PPMS using the PS0008 screen.
To view the Arrears Balances in PeopleSoft use the following navigation: NavBar > Navigator > Payroll for North America > Periodic Payroll Events USA > Balance Reviews > Arrears
- Enter the Emplid from the query result into the EmplID field
- Click the Search button
To view the Payroll related Job Data in PeopleSoft use the following navigation: NavBar > Navigator > Workforce Administration > Job Information > Job Data
- Enter the Emplid from the query result into the field
- Click the button
- There are several tabs (pages) that include Job Data. For the purposes of viewing Payroll related information, we’ll focus on 2 pages:
- Job Information Regular Shift field
- Payroll Paygroup & Employee Type
Note: The Tax Location (known as the “Work Location” within PeopleSoft) is located on the Payroll page as well, but at the time of conversion there is only one value being used for this field. Based on discussions during the GDR sessions, additional tax location codes will be configured as needed.
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