Running the Employment Equity Report

Page Name	Definition Name	Usage
Employment Equity Page	RUNCTL_PER101CN	Run the Employment Equity report (PER101CN). This SQR creates an interface file that you can upload to the Workplace Equity Information Management System (WEIMS).

If you're a federally regulated employer in Canada and required to report to the federal government on employment equity, HR provides the Employment Equity report (PER101CN), which creates three data interface files for the federal government's Workplace Equity Information Management System (WEIMS). You don't have to enter the data in the database and then reenter the same data in WEIMS each year.

The report retrieves extensive data for all Canadian employees in the organization who were active employees at any point during the calendar year. The report includes up to four promotion dates per employee within a given year, in ascending order.

The Employment Equity report is based on the interpretation of Employment Equity Regulations and Employment Equity Reporting, as published by Employment and Social Development Canada (ESDC). Because the interpretation of the guidelines can vary, there may be differences between your requirements and the rules that PeopleSoft has built into the generic solution. If the data imported into the WEIMS does not meet ESDC guidelines, either modify the information once it has been imported into the WEIMS or modify the Employment Equity report.

You are responsible for reporting accurate information to ESDC. The ESDC is the source of interpretation and final authority on all matters related to employment equity reporting, including the definition of salary and its rules for annualization and non-annualization. PeopleSoft doesn't assume this role.

Important! For information on creating and importing files into WEIMS, see the "Creating and Importing Files for the Legislated Employment Equity Program" documentation administered and enforced by the Legislated Employment Equity Program (LEEP) and the ESDC.

https://equity.esdc.gc.ca/sgiemt-weims/maint/file/download/LEEP-Creating-And-Importing-Files-WEIMS.pdf

The Employment Equity report creates three files: employee.txt, promo.txt, and term.txt. The employee file includes information on temporary and permanent employees. The following tables lists the field names in each report file, the fields in PeopleSoft HCM from which the report retrieves the data, and the tables where the fields are located.

Employee File

The following table lists the fields and tables used to create the employee.txt file:

Column	WEIMS Report Field Name	PeopleSoft Field	PeopleSoft Table	Notes
A	Employee Number	Employee ID	PERSONAL_DATA	None
B	CMA (Census Metropolitan Area) code	Census Metropolitan Area	LOCATION_TBL	Based on the location code in the employee job record, the report uses the CMA associated with that location code in the LOCATION_TBL table.
C	Province code	Numeric Code	STATE_NAMES_TBL	Based on the location code in the employee job record, the report uses the numeric code associated with that location code in the LOCATION_TBL table.
D	NOC (National Occupational Classification) code	Canadian NOC Code (Canadian National Occupational Classification code)	JOBCODE_TBL	Based on the job code in the employee job record, the report uses the NOC code associated with the job code in the JOBCODE_TBL table.
E	NAICS (North American Industrial Classification System) four-digit code	NAICS Code	DEPT_TBL_CAN	Based on the department ID in the employee job record, the report uses the NAICS code associated with that department ID in the DEPT_TBL table.
F	Status (employee status) code	Temporary Full-/Part-Time Other	Derived value based on rules in Structured Query Report (SQR); data from a combination of PERSONAL_DATA and EMPLOYMENT records.	See Understanding Inclusion of Employees in the Employment Equity Report.
G	Comments (employee type comments)	Employment Status Full-Time Student	Derived value based on rules in SQR; data from a combination of PERSONAL_DATA and EMPLOYMENT records.	See Understanding Inclusion of Employees in the Employment Equity Report.
H	Gender code	Sex	PERSONAL_DATA	None
I	Salary Paid	Earnings	PS_PAY_EARNINGS	Must be left blank if employee has an employee status of 04- Other in column F. See also Understanding Salary Calculation.
J	Special Salary - Commission Only	Other Earnings	PS_PAY_OTH_EARNS	Y indicates the employee is paid on commission alone. See also Understanding Salary Calculation.
K	Special Salary - Other	Other Earnings	PS_PAY_OTH_EARNS	Y indicates special salary scenarios where the number of hours worked may be difficult to determine. See also Understanding Salary Calculation.
L	Number of Hours	Regular Earning Hours Other Hours	PS_PAY_EARNINGS PS_PAY_OTH_EARNS	Must correspond with the salary paid in column I. See also Understanding Salary Calculation.
M	Number of Weeks		PS_PAY_CALENDAR	Must correspond with the salary paid in column I. Must be left blank if employee has an employee status of 04- Other in column F. See also Understanding Salary Calculation.
N	Special Salary Hourly Pay Rate			Must correspond with the salary paid in column I. Only enter an hourly pay rate if column K has a value of Y. Must be left blank if employee has an employee status of 04- Other in column F. See Understanding Salary Calculation.
O	Bonus Pay	Other Earnings	PS_PAY_OTH_EARNS	Required if the employee is paid a bonus in the reporting period. See also Understanding Salary Calculation.
P	Overtime Pay	Other Hourly Earnings	PS_PAY_EARNINGS	Required if the employee is paid for overtime hours worked in the reporting period. Leave blank if the employee is not paid for overtime hours, is paid a special commission-only salary (Y value in column J), or has an employee status of 04-Other in column F. See also Understanding Salary Calculation.
Q	Overtime Hours			Must correspond with overtime pay paid entered in column P. See also Understanding Salary Calculation.
R	Aboriginal	Aboriginal	PERSONAL_DATA	None
S	Visible Minority	Visible Minority	PERSONAL_DATA	None.
T	Person with Disabilities	Disabled	PERSONAL_DATA	None
U	Hire Date	Hire Date	EMPLOYMENT	None
V	Termination Date	Termination Date	EMPLOYMENT	None

The file will create the output without column headings, similar to the following example:

Promotion File

The following table lists the fields and tables used to create the promo.txt file:

Column	WEIMS Report Field Name	PeopleSoft Field	PeopleSoft Table	Notes
A	Employee Number	Employee ID	PERSONAL_DATA	None
B	Promotion Number	Derived values from SQR program; sequential count	Based on a number of job records that meets appropriate criteria as defined by WEIMS reporting rules.	None
C	Promotion Date	Derived value from EFF_DT	JOB	None

Note: The report will not contain data if your company did not award promotions during the reporting period.

Temporary Terms File (for Temporary Employees)

The following table lists the fields and tables used to create the term.txt file:

Column	WEIMS Report Field Name	PeopleSoft Field	PeopleSoft Table	Notes
A	Employee Number	Employee ID	PERSONAL_DATA	None
B	Term Number	Derived field; sequential count.	CONTRACT_DATA	None
C	Start Date	CONTRACT_BEGIN_DT	CONTRACT_DATA	None
D	End Date	CONTRACT_END_DT	CONTRACT_DATA	None
E	Termination Date	Termination Date	EMPLOYMENT	Temporary employees only.

Note: The report will not contain data if your company did not employ temporary or casual employees during the reporting period.

The Employment Equity report includes any employee in a Canadian regulatory region during the reporting year. This includes employees who were hired, rehired, transferred, or terminated, and anyone who was active, on leave, or suspended during the year.

All employee Empl Records are selected from JOB based on one of the two following criteria:

Employees with Empl Status = 'A','L','P','S',or 'W'. (The latest effective dated row on or earlier than the beginning of the reporting year is selected).
Employees having a Job action in the reporting year regardless of Empl Status. (All job rows with an effective date that falls within the reporting period are selected).

The job record used in the data files includes the maximum effective date and effective sequence that is less than or equal to the year end. This may be a record from before the current year if an employee has had no job activity during the reporting year.

Data from personal data, diversity, disability, and employment records appears as of the report's run date. Data from the tables JOBCODE_TBL, DEPT_TBL, and LOCATION_TBL appears as of the end of the year.

Employees with Multiple Jobs

For employees with multiple jobs, the report provides information on only one job.

When more than one job is present, the system uses the minimum employee record number for the JOB related information reported in the file.

The employee salary is reported as the sum of all job salaries.

Determination of Employee Type

Two fields in the employee.txt file are related to employee type: Employee Type and Employee Type Description (for an employee type categorized as Other (04).

The system determines the appropriate category for each WEIMS employee type:

Term	Definition
Full-Time (01)	REG_TEMP = 'R' (regular) FULL_PART_TIME = 'F' (full-time) EMPL_STATUS not in (L,P,S) FT_STUDENT <> 'Y'
Part-Time (02)	REG_TEMP = R FULL_PART_TIME <> 'F' EMPL_STATUS not in (L,P,S) FT_STUDENT <> 'Y'
Temporary (03)	REG_TEMP = 'T' EMPL_STATUS not in (L,P,S) FT_STUDENT <> 'Y'
Other (04)	WEIMS Status Other (04): Retired, terminated or deceased and had not worked the full calendar year. Empl Status = L (Unpaid leave of absence), P (paid leave of absence), S (suspension). Full-time student Supersedes Full-Time, Part-Time, Temporary, and Casual.
Casual (05)	Employees who would otherwise be temporary but whose combined start and end dates (from contract data) add up to fewer than 12 weeks during the reporting year.

If the employee type code is Other (04), the description field is populated as follows:

Term	Definition
Full-Time Student	FT_STUDENT = 'Y'
On Leave of Absence	EMPL_STATUS = L,P
Terminated and hasn't worked during the year	EMPL_STATUS = T,U
Retired and hasn't worked during the year	EMPL_STATUS = R,Q
Deceased and hasn't worked during the year	EMPL_STATUS = D
Last Date Worked not in reporting year and not null	LAST_DATE_WORKED <>Null and <Reporting Year

WEIMS will perform the necessary calculations and generate data required for Form 2 of employer submissions using the following data elements from the employee.txt file:

Salary paid (column I)
Number of hours corresponding to the salary (column L)
Number of weeks corresponding to the salary (column M)
Bonus pay paid in the reporting year (column O)
Overtime pay paid in the reporting year (column P)
Number of overtime hours worked for the overtime pay (column Q)

Note: Organization should refer to the following WEIMS documents published by ESDC to have a full understanding of the salary calculation process and ensure that their reporting is accurate.

-–"Creating and Importing Files for the Legislated Employment Equity Program"

–-"Changes to the Employment Equity Regulation and Employment Equity Reporting"

Use the Employment Equity page (RUNCTL_PER101CN) to run the Employment Equity report (PER101CN).

Note: The Employment Equity report (PER101CN) creates three data interface files (employee.txt, promo.txt and term.txt) for you to import the interface files into the federal government's WEIMS. For more information, see Understanding Employment Equity Report Files topic.

Navigation:

Workforce Monitoring > Meet Regulatory Rqmts CAN > Reports > Employment Equity > Employment Equity

Field or Control	Description
Reporting Year	Enter the reporting year for the employment equity report.
Company	Enter one or more companies for which you want to report.
Earnings Type	Select one or more earnings to include in the file for the company. Values include: Bonus Overtime Regular Special Salary - Commission Special Salary - Other
Earnings Code and Description	Identify earnings codes to be included in the file.

Pay Earnings Processing

Earnings are selected for employees whose WEIMS status is 01, 02, 03, or 05. If the employee status is 04 (Other), only HR data is reported.

The earnings codes specified for each of the earnings types are matched against a view that returns PAY_EARNINGS and PAY_OTH_EARNS filtering on the Company and Earnings codes specified on the run control. The following lists the Payroll source fields based on earnings type:

Earning Type	YTD Pay	YTD Hours	Special Salary Hourly Rate
Regular Pay (Column I)	PAY_EARNINGS.REG_EARNS PAY_EARNINGS.REG_HRLY_EARNS PAY_OTH_EARNS.OTH_EARNS	PAY_EARNINGS.REG_EARN_HRS, PAY_EARNINGS.REG_HRS PAY_OTH_EARNS.OTH_HRS	N/A
Special Salary – Commission (Column J)	PAY_OTH_EARNS.OTH_EARNS	N/A	N/A
Special Salary – Other (Column K)	N/A	N/A	PAY_OTH_EARNS.OTH_EARNS PAY_OTH_EARNS.OTH_HRS
Bonus Pay (Column O)	PAY_OTH_EARNS.OTH_PAY PAY_OTH_EARNS.OTH_EARNS	PAY_OTH_EARNS.OTH_HRS	N/A
Overtime Pay (Column P)	PAY_EARNINGS.OT_HRLY_EARNS, PAY_OTH_EARNS.OTH_PAY PAY_OTH_EARNS.OTH_EARNS	PAY_EARNINGS.OT_HRS, PAY_OTH_EARNS.OTH_HRS	N/A

Troubleshooting

The Employment Equity report detects errors that can prevent the WEIMS from successfully importing data.

If you find that records are missing from the import files when you run the Employment Equity report, information that is required for the WEIMS report may not be included in the online pages. The PER101CN.LIS file identifies these situations and produces an error message.

Running the Employment Equity Report

Page Used to Run the Employment Equity Report

Understanding Employment Equity Reporting

Understanding Employment Equity Report Files