TechKnowledge Content
SUMMARY
This article contains information about where the Record of Employment (ROE) amounts will pull from within Canadian Payroll, and also which tables and fields in SQL are used to populate the ROE.
MORE INFORMATION
The ROE amounts and hours will pull from the Payroll Employee Summary Expand – Canada window (click Cards, click Payroll – Canada, click Employee, and then click Calculated). The data needs to be listed in the Summary Expand window to generate the ROE. When the ROE is created, it will also look at the Calculated field under the LY Calculated window as well (click Inquiry, click Payroll - Canada, click Employee Last Year Information and then click Calculated) to collect any LY information it needs to complete the ROE.
Within SQL Server Management Studio, you can find this information stored in the CPY30250 (Payroll Calculation MSTR File) and CPY50250 (Payroll Calculation MSTR File LY) tables:
-
The ROE Total Insurable Earnings information will pull from the PEIInsEarnings24 field in the CPY30250/CPY50250.
-
The ROE Total Insurable Hours information will pull from the PEIInsHours field in the CPY30250/CPY50250.
GP will find all of the Calculated records for the employee (one record per employee per batch). It finds the newest record (by date) that is not marked off (PMarkedOff = 0). It then finds all of the transactions that made up this batch and grabs the transactions with the highest date. If this was not successful, either the employee didn't get paid in the current year or the employee didn't have enough pay periods in the current year, so it will repeat the same process in the history file (CPY50250).
You will want to check the CPY30250 and CPY50250 to see if the transactions have been marked off (PMarkedOff=1) already. The CPY30250 table holds current year information, and the PMarkedOff column will be 0 (unmarked) up until the ROE is generated and marked off. The CPY50250 table holds historical year information. When the ROE is generated and marked off, the PMarkedOff column will flip to a value of 1. So if the transactions are marked off already, it prevents the information from pulling on to another ROE.
select PStartDate, PCutoffDate, PDate, Pmarkedoff, PEIInsEarnings24, * from cpy30250 where PEmployeeID = 'xxx' order by DEX_ROW_ID desc
select PStartDate, PCustoffDate, PDate, Pmarkedoff, PEIInsEarnings24, * from cpy50250 where PEmployeeID = 'xxx' and Pyear = 'yyyy' order by DEX_ROW_ID desc
--update the xxx placeholder with the Employee's ID. And the year for yyyy, or remove that restriction to read farther back.
If the transactions are marked off (PMarkedOff=1) then they will not pull and can be updated to 0 if you need these records to pull on to an ROE again.
Also the system uses the PDate field to determine which bucket it goes into, so if the PDate is not in order, the system is not able to update the buckets in successive order and stops. Review to make sure no dates in the PDATE field are skipped, overlap or are out of order. Directly update the date in the SQL table (in a test company first).
Also make sure the date range between PStartDate -PCutoffDate are unique, are in consecutive order and do not overlap with the period range before or after it. The system uses 3 fields and gives each it's own 'bucket' on the ROE form. It will lump records together that are for the same dates. If you make a change in the SQL table, you must regenerate the ROE's to pick up the change.
The CRA website will define the maximum number of consecutive pay periods you use to calculate the employee's total insurable hours (Block 15A) or total insurable earnings (Block 15B). It will vary for each and also for each 'Pay Periods Per Year' assigned to each employee. The number of periods used to determine the amount to enter in Block 15B is different than the number of periods used to calculate Block 15A. Refer to the CRA website for more details on the ROE calculations.
ROE UPDATES:
In the 2015 Canadian Payroll Year-End Update, the ROE window was redesigned and box 15b was removed from the window since it is not used in the XML anymore. If you generate the ROE's in Canadian Payroll, you must have the January Hot Fix (Jan 2016) installed as there were a number of changes included in this patch as well. You should be on versions 14.00.0952 or 12.00.2084 or higher if you will be generating ROE's.
Microsoft Dynamics GP January Hotfix....RELEASED
https://community.dynamics.com/gp/b/dynamicsgp/archive/2016/01/15/microsoft-dynamics-gp-january-hotfix-released
Below is the CRA website that lists the new ROE format for the 2016 year:
http://www.servicecanada.gc.ca/eng/services/ei/employers/roe/resources/roewebur53payrollextract.shtml
TO EDIT THE ROE FORM:
To make changes on your ROE, you can manually update the fields directly on the Payroll ROE Amounts – Canada window (click Tools, click Routines, click Payroll – Canada, click ROE Creation, select the ROE, and then click ROE Amounts). You can update the boxes directly in the window. Save changes and print a corrected ROE.
If you create your ROE and boxes 1-27 are not populated, this is the result of a change from the Human Resources Development of Canada that states, “If you have an employee who has been paid in all of your pay periods, only box 15C will be filled in. Boxes 1-27 will be left blank. If the employee did not receive any pay in one or more of your pay periods, then boxes 1-27 will be filled in.”
Note The ROE generated from Canadian Payroll is a ‘best estimate’ based on the information that exists in the system. You may have to manually update the Total Insurable Hours and Earnings on the ROE form.
This article was TechKnowledge Document ID:21972
