Workplace Equity Information Management System (WEIMS)

1. Workplace Equity Information Management System (WEIMS) Creating and Importing Files November 18, 2014

2. TABLE OF CONTENTS • Preparing Employee Data (3 txt files) • Employee.txt • What you should know about salary • Term.txt • Promo.txt • Beforeimportingyourdata in WEIMS • Create txt files and save it • Import txt files created onto WEIMS

3. PREPARING EMPLOYEE DATA When importing data into WEIMS from a different application (i.e. an HR database), you will be required to manipulate the data so that it complies with WEIMS specifications. You will be required to create three (3) txt files to import your data in WEIMS. They are: • employee.txt • term.txt • promo.txt NOTE: To successfully import data into WEIMS, the files must be named according to the naming convention above.

4. EMPLOYEE.TXT • File contains one record for every employee. • If your organization has 200 employees, you must have 200 records in this file. SAMPLE NOTE: Working with Spreadsheets makes it easier to manipulate data. However, WEIMS will only allow you to import Text Tab Delimited (txt) files. Spreadsheets can easily be transferred to txt files. This process will be explained later.

5. EMPLOYEE.TXT • COLUMN 1: EMPLOYEE NUMBER • Maximum 20 characters. • Can be letters or numbers or a combination of both. • Must be unique (no duplicates).

6. EMPLOYEE.TXT • COLUMN 2: CMA CODE • Must be exactly 2 digits. • Make sure zeros are included (if applicable). • Must correspond to the values found under the Coding References – Province and CMA codes under the Help Tab

7. EMPLOYEE.TXT • COLUMN 3: PROVINCE CODE • Must be exactly 2 characters. • CMA and Province code must correspond to one another. • Must correspond to the values found under the Coding References – Province and CMA codes under the Help Tab

8. EMPLOYEE.TXT • COLUMN 4: NOC CODE • Must be exactly 4 digits • Must be a valid 4-digit NOC Code • Use NOC Code Support Table: http://www5.hrsdc.gc.ca/NOC/English/NOC/2011/Welcome.aspx • Note that WEIMS uses 2011 NOC Code.

9. EMPLOYEE.TXT • COLUMN 5: NAICS CODE • Must be exactly 4 digits. • Must be a valid 4-digit NAICS Code • See NAICS Code Support Table: http://www.statcan.gc.ca/subjects-sujets/standard-norme/naics-scian/2007/list-liste-eng.htm • Note that WEIMS uses 2007 NAICS codes.

10. EMPLOYEE.TXT • COLUMN 6: EMPLOYEE STATUS CODE • Must be exactly 2 digits. • Must correspond to the values found under the Coding References – Employee Status Codes found under the help tab

11. EMPLOYEE.TXT • COLUMN 7: NOTES • Column is required, but data is optional. • Column is necessary for WEIMS to read data correctly.

12. EMPLOYEE.TXT • COLUMN 8: GENDER • Must be exactly 1 letter. • Must be M (male) or F (female). • Cannot leave cell blank or will cause error

13. EMPLOYEE.TXT COLUMN 9: SALARY • No decimals, no dollar signs and no commas. • Minimum salary is 0. • If necessary, salaries must be annualized.

14. What you should know about salary SALARY, as it is to be used for Forms 2 and 3, is the remuneration paid for work performed by an employee in the form of salary, wages, commissions, tips, bonuses, and piece rate payments, rounded to the nearest dollar. Salary does not include overtime wages. There are some cases when employees’ salaries need to be annualized to reflect yearly earnings.

15. ANNUALIZATION ANNUALIZATION is the process whereby an employee’s salary is calculated to represent what their salary would have been had they been paid for the entire year even though they only worked part of the year.

16. WHY ANNUALIZE? This is sometimes necessary when the use of the actual salary would misrepresent occupational earnings and distort the employer’s salary ranges. Generally, an employee’s salary should be annualized when the employee has not worked the full reporting year or has been hired or promoted part way through the reporting year.

17. WHEN TO ANNUALIZE • Hires or promotions during the reporting year. • Seasonal employees who have worked part of a complete season or term (e.g. if an employee works only for 4 months of a 6 months season, the salary must be calculated to reflect the earning of the 6 months period.) • Unpaid leave of absence granted at the request of the employees, including parental or educational leave, for employees who returned to work on or before December 31st of the reporting year. • Hires with a steady base salary and commission – annualize only the base salary and then add the commission. • Transfer employees as a result of a corporate transaction – these employees are considered new hires.

18. WHEN NOT TO ANNUALIZE • Seasonal employees working a full season. • Temporarily laid off employees. • Strike. • Acting assignment. • Commission-only salary. • Hire with a fluctuating base salary and commission. • Part-time employees working part of the year as full-time. • Employees experiencing a permanent change in employment status part-way through the year (e.g. full-time to part-time or vice versa). • Employees on leave of absence who retain their salary or who receive income replacement benefits from the employer.

19. HOW TO ANNUALIZE Pay Periods per year Salary per Pay Period Annualized salary X = Example #1: A Truck Driver earns $1,592 bi-weekly and was absent for 3 months on unpaid personal leave. Since the pay is bi-weekly, the organization has 26 pay periods per year. What should his salary be recorded as for the reporting year? 26 X $1,592 = $41,392

20. HOW TO ANNUALIZE (continued) Salary per Pay Period Annualized salary X = Pay Periods per year Example #2: A ticket agent was hired on June 1st of the reporting year and earns $463 per week. The organization pays its employees weekly. What should his/her pay be recorded as for the reporting year? 52 X $463 = $24,076

21. EMPLOYEE.TXT • COLUMN 10: ABORIGINAL PEOPLES • Must be 1 letter • Must be Y (yes) or N (no) • First year employers should use N only (if not reporting by designated groups).

22. EMPLOYEE.TXT • COLUMN 11: MEMBERS OF VISIBLE MINORITIES • Must be 1 letter. • Must be Y (yes) or N (no). • First year employers should use N only (if not reporting by designated groups).

23. EMPLOYEE.TXT COLUMN 12: PERSONS WITH DISABILITIES • Must be 1 letter. • Must be Y (yes) or N (no). • First year employers should use N only (if not reporting by designated groups).

24. EMPLOYEE.TXT • COLUMN 13: HIRE DATE • Must be 10 characters. • Format – YYYY-MM-DD (include dashes). • Must always be blank for TEMPORARY or CASUAL employees.

25. EMPLOYEE.TXT • COLUMN 14: TERMINATION DATE • Must be 10 characters. • Format – YYYY-MM-DD (include dashes). • Must always be blank for TEMPORARY or CASUAL employees.

26. EMPLOYEE.TXT • COLUMN 15: DIVISION • Optional field. • User defined 4 character codes.

27. EMPLOYEE.TXT • COLUMN 16: REGION • Optional field • User defined 4 character codes

28. TERM.TXT • File contains 1 row for every temporary or casual employee's contract term • Only temporary or casual employees are included in this file. • If a temporary or casual employee has two contract terms, there are two rows • Do not create this file if your organization employed no temporary or casual employees during the reporting period SAMPLE

29. TERM.TXT COLUMN 1: EMPLOYEE NUMBER • Maximum 20 characters. • Employee number must exist in employee.txt file.

30. TERM.TXT • COLUMN 2:TERM NUMBER + NUMBER OF CONTRACTS • Maximum 4 digits • If an employee has 2 contracts, they must have 2 records, etc • Term numbers must be entered chronologically

31. TERM.TXT • COLUMN 3: START DATE • Must be 10 characters • Format – YYYY-MM-DD (include dashes)

32. TERM.TXT • COLUMN 4: END DATE • Must be 10 characters • Format – YYYY-MM-DD (include dashes)

33. TERM.TXT • COLUMN 5: TERMINATION DATE • Must be 10 characters • Format – YYYY-MM-DD (include dashes) • Only include if before END DATE

34. PROMO.TXT • File contains one row for every promotion • If an employee was promoted twice, there are two rows • If an employee was not promoted, do not put a row for that employee • Do not create this file if your organization awarded no promotions during the reporting period SAMPLE

35. PROMO.TXT • COLUMN 1:EMPLOYEE NUMBER • Maximum 20 characters • Employee number must exist in employee.txt file

36. PROMO.TXT • COLUMN 2: PROMOTION NUMBER • Maximum 4 digits • Promotion number must be entered chronologically

37. PROMO.TXT • COLUMN 3: PROMOTION DATE • Must be 10 characters • Format – YYYY-MM-DD (include dashes)

38. BEFORE IMPORTING YOUR DATA IN WEIMS • All columns must exist and be in the order described in WEIMS specifications (employee.txt) - Except for columns: Division and Region (Optional field) • Column headings are optional • Row labels must be removed • All rows must contain data – Except for column: Comment

39. CREATE TXT FILES AND SAVE IT • Steps to save your spreadsheets in text file format • Reformat and check the steps • Go to File ►Save As ►Then in the file name field call it either employee, promo or term depending on which file you are creating ►Nextclick on the arrow from the drop down menu for “ Save As Type” ► Then scroll through and Select the option Text (Tab delimited) (*.txt) ► Then click on “ Save”

40. IMPORT THE TEXT FILES CREATED ONTO WEIMS • All import files must be named according to WEIMS specifications (employee.txt, term.txt and promo.txt) • All files must be uploaded individually (cannot select multiple files to upload). WEIMS will then import all 3 files together

41. QUESTIONS?