...
| Info | |
|---|---|
|
| Panel | ||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Data Submission Options & GuidelinesThere will be 2 primary ways in which data can be provided to HOOPP ranging from manual entry to almost complete automation. They include:
Information submitted to HOOPP can be done using any combination of the above options as summarized below:
Important: For in-year transactions, we recommend you report this information to HOOPP at least monthly in order to streamline your annual MDC reporting processes. Additionally, for member events such as Enrolments, Terminations, Retirements, Pre-Retirement Death, information should be reported on a regular basis so that we do not delay providing benefit information to our Members. In order to help determine which data submission options makes most sense for your organization, we have provided some recommendations and high level guidelines for how best to submit information to HOOPP.
3. Manual data entry is recommended for low volume transactions that normally include:
|
Document Purpose
The purpose of the Member Data File Requirements Document is to provide requirements for implementing the file upload changes associated with the new file formats that will be available to Employers for upload through the Employer Portal.
This document will include:
...
NOTE: calculations specifications have not changed from current state. Please refer to the HOOPP Employer Administration Manual for calculation requirements and examples.
Requirements
This section provides the detailed requirements for the new file formats that will be available for upload and submission to HOOPP from the Employer Portal.
General File Requirements
The following are the general file format requirements that applies across all files.
| Req ID | Requirement Description |
|---|---|
| G1 | Files must be encoded in ANSI format. |
| G2 | Files must have a “.csv” extension. |
| G3 | Data is delimited by a comma. |
| G4 | Files must contain a header record in the first row of the data field and delimited by a comma. All headers are mandatory, and each file must contain all specified headers even if no data is being reported under that header. The header names are listed in the “Field Name” of the specific file layouts in the sections that follow. |
| G5 | All fields must follow the order as specified in the file layouts in the sections that follow. |
| G6 | Each field must follow the supported data types as indicated in the file layouts. Data formats supported include:
|
| G7 | The following file types must NOT contain multiple lines for the same SIN.
|
| G8 | The following file types may contain multiple lines for the same SIN.
For example, if one member has a retro payment that impacts multiple years, the member record will be repeated for each year of retro data. |
| G9 | SIN, Last Name and First Name are mandatory fields in all files. The SIN will be used as the “key identifier” in each file to match reported data to the associated member record that HOOPP has on file. If the SIN, Last Name and/or First Name values are different from what HOOPP has on file, the reported values will not be used to update the members information that HOOPP has on file. SIN, Last Name, Middle Initial, and First Name can be updated as part of the Member Basic Info file only. SIN and name changes reported via the Member Basic Info data process (either through file upload or via manual entry) will update the Member’s information that HOOPP has on file. |
| G10 | You must only report net new data changes for the following in-year data files:
Employee information should not be duplicated file over file. For example, an employee’s termination information must only be provided once in a single file. |
MDC Data - File Requirements
This file is provided annually as part of the MDC (Member Data Collection) process.
...
| Field Name | Description | Mandatory Field | Max Field Length | Data Type | Example |
|---|---|---|---|---|---|
| SIN | Social Insurance Number No hyphens or spaces | Yes | 9 | Integer | 123456789 |
| LastName | Last name of member | Yes | 40 | Character | Smith |
| FirstName | First name of member | Yes | 40 | Character | John |
| Weeks | Total contributory weeks for the MDC year | Yes | 4 | Decimal | 51.05 |
| LowContributions | Total low contributions for the MDC year | Yes | 9 | Decimal | 523.64 |
| HighContributions | Total high contributions for the MDC year | No | 9 | Decimal | 321.23 |
| PA | Pension Adjustment for the MDC year | Yes | 5 | Integer | 12594 |
| EmploymentType | Most recent employment status of full-time or part-time. | Yes | 2 | Code | FT |
| EmploymentType EffectiveDate | Effective date of the most recent employment status | Yes | 8 | Date | 10302015 |
| AnnualizedEarnings | Annualized earnings for the MDC year. This field will normally be blank. Only provide if the member has waived contributions with HOOPP before January 2014. | No | 8 | Integer | 52000 |
Contributory Leaves - File Requirements
This file is provided annually as part of the MDC (Member Data Collection) process.
...
| Field Name | Description | Mandatory Field | Max Field Length | Data Type | Example |
|---|---|---|---|---|---|
| SIN | Social Insurance Number No hyphens or spaces | Yes | 9 | Integer | 123456789 |
| LastName | Last name of member | Yes | 40 | Character | Smith |
| FirstName | First name of member | Yes | 40 | Character | John |
| LeaveType | Reason code for the contributory leave type | Yes | 2 | Code | 03 |
| LeaveStartDate | Start date of the leave | Yes | 8 | Date | 10302015 |
| LeaveEndDate | End date of the leave. If leave has not ended and the end date is unknown, input December 31 of the applicable MDC year. | Yes | 8 | Date | 10302015 |
| TotalWeeksPaid | Total contributory weeks for the specified leave period Note: If weeks for the same MDC year has already been reported through lump sum contributions via the Remittance process, they must be excluded from this total. | Yes | 4 | Decimal | 51.05 |
| PctofNormalEarnings | Percent of normal earnings (i.e. part-time service ratio) prior to leave commencement. If full-time prior to leave then report 100. | Yes | 5 | Decimal | 50.3 |
Events (Termination & Retirement) - File Requirements
This file is provided in-year as part of the Final Data - Data Collection Process for a member who has terminated employment or has retired.
...
| Field Name | Description | Mandatory Field | Max Field Length | Data Type | Example |
|---|---|---|---|---|---|
| SIN | Social Insurance Number No hyphens or spaces | Yes | 9 | Integer | 123456789 |
| LastName | Last name of member | Yes | 40 | Character | Smith |
| FirstName | First name of member | Yes | 40 | Character | John |
| EventType | Event being reported (Termination or Retirement) | Yes | 6 | Code | RETFIN |
| EmploymentEndDate | Last day worked | Yes | 8 | Date | 10302015 |
| Annualized Earnings_CurrentYear | Annualized earnings for the current year. This field will normally be blank. Only provide if the member has waived contributions with HOOPP before January 2014. | No | 8 | Integer | 52000 |
| Weeks_CurrentYear | Total contributory weeks for the current year | Yes | 4 | Decimal | 51.05 |
| LowContributions_CurrentYear | Total low contributions for the current year | Yes | 9 | Decimal | 523.64 |
| HighContributions_Curr entYear | Total high contributions for the current year | No | 9 | Decimal | 321.23 |
| PA_CurrentYear | Pension Adjustment for the current year | Yes | 5 | Integer | 12594 |
| Annualized Earnings_PreviousYear | Annualized earnings for the MDC year. This field will normally be blank. Only provide if MDC data has NOT been submitted, and the member has waived contributions with HOOPP before January 2014. | No | 8 | Integer | 52000 |
| Weeks_PreviousYear | Total contributory weeks for the MDC year. Only provide if MDC data has NOT been submitted. | No | 4 | Decimal | 51.05 |
| LowContributions_Prev iousYear | Total low contributions for the MDC year. Only provide if MDC data has NOT been submitted. | No | 9 | Decimal | 523.64 |
| HighContributions_Pre viousYear | Total high contributions for the MDC year. Only provide if MDC data has NOT been submitted. | No | 9 | Decimal | 321.23 |
| PA_PreviousYear | Pension Adjustment for the MDC year. Only provide if MDC data has NOT been submitted. | No | 5 | Integer | 12594 |
Enrolments - File Requirements
This file is provided in-year as part of the member Enrolment - Data Collection Process.
...
| Field Name | Description | Mandatory Field | Max Field Length | Data Type | Example |
|---|---|---|---|---|---|
| SIN | Social Insurance Number No hyphens or spaces | Yes | 9 | Integer | 123456789 |
| Title | Title of employee | No | 4 | Code | Mr. |
| LastName | Last name of employee | Yes | 40 | Character | Smith |
| MiddleInitial | Middle initial of employee | No | 2 | Character | AC |
| FirstName | First name of employee | Yes | 40 | Character | John |
| DateOfBirth | Birth date of employee | Yes | 8 | Date | 10302015 |
| Gender | Gender of employee | Yes | 1 | Code | M |
| LanguagePreference | Preferred language of the employee | Yes | 1 | Code | F |
| HireDate | Date employee was hired at specific employer | Yes | 8 | Date | 10302015 |
| EnrolmentDate | Date employee was enrolled in the plan | Yes | 8 | Date | 10302015 |
| EmploymentType | Code that indicates employee’s type of employment | Yes | 2 | Code | PT |
| EmploymentType EffectiveDate | Effective date of the employment type | Yes | 8 | Date | 10302015 |
| AnnualizedEarnings | Annualized earnings associated to the year of enrollment | Yes | 8 | Integer | 52000 |
| UnionDesignation | Code identifying member's union designation at that specific employer | Yes | 7 | Code | OPSEU |
| TelephoneNumber | Employee’s primary contact number | Yes | 15 | Character | 4165555555 |
| EmailAddress | Email address of employee | No | 80 | Character | johndoe@email.com |
| AddressLine1 | First line of the employee’s address | Yes | 40 | Character | 123 Street |
| AddressLine2 | Second line of the employee’s address | No | 40 | Character | Apt. #805 |
| AddressLine3 | Third line of the employee’s address | No | 40 | Character | PO BOX 456 |
| City | City of the employee’s address | Yes | 40 | Character | Toronto |
| ProvinceState | Province of the employee’s address | Yes | 2 | Code | ON |
| PostalCodeZipCode | Postal code/Zip code of the employee’s address | Yes | 12 | Character | M5C 3B2 |
| Country | Country of the employee’s address | Yes | 2 | Code | CA |
Previous Year Adjustments - File Requirements
This file is provided in-year as part of the remittance Monthly Contribution Reporting (MCR) process.
...
| Field Name | Description | Mandatory Field | Max Field Length | Data Type | Example |
|---|---|---|---|---|---|
| SIN | Social Insurance Number No hyphens or spaces | Yes | 9 | Integer | 123456789 |
| LastName | Last name of member | Yes | 40 | Character | Smith |
| FirstName | First name of member | Yes | 40 | Character | John |
| AdjustementYear | Year the previous year adjustment is in respect of | Yes | 4 | Date (Year only) | 2014 |
| Weeks | Amended contributory weeks for the adjustment year | No | 4 | Decimal | 20.05 |
| LowContributions | Amended low contributions for the adjustment year | No | 9 | Decimal | 523.64 |
| HighContributions | Amended high contributions for the adjustment year | No | 9 | Decimal | 321.23 |
| AmendedPA | Total Pension Adjustment for the adjustment year | Yes | 5 | Integer | 12594 |
Calculation Requirements
Please refer to the HOOPP Employer Administration Manual for formula’s and examples for calculating the following:
- Employee Contributions (Low Contributions, High Contributions)
- Retro contributions
- Pension Adjustment (PA)
- Annualized Earnings (AE)
Interface Codes
The following are the valid list of codes for each corresponding file type:
Employment Type
| Interface Code | Description |
|---|---|
| FT | Full Time |
| PT | Part time |
Contributory Leaves Types
| Interface Code | Description |
|---|---|
| 01 | Health Leave |
| 02 | Family Medical Leave |
| 03 | Pregnancy-Parental Leave |
| 04 | Strike / Lockout |
| 05 | Approved Work Schedule Reduction |
| 06 | Emergency Leave |
| 07 | Approved Non-Statutory Leave |
Leave of Absence Leaves Types
| Interface Code | Description |
|---|---|
| 01 | Health Leave |
| 02 | Family Medical Leave |
| 03 | Pregnancy-Parental Leave |
| 04 | Strike / Lockout |
| 05 | Approved Work Schedule Reduction |
| 06 | Emergency Leave |
| 07 | Approved Non-Statutory Leave |
| 08 | Unapproved Absence |
Gender
| Interface Code | Description |
|---|---|
| M | Male |
| F | Female |
Title
| Interface Code | Description |
|---|---|
| Ms. | General form of address for woman regardless of marital status. |
| Mrs. | Married Woman |
| Miss | Unmarried Woman |
| Mr. | General form of address for man regardless of marital status. |
| Dr. | Doctor |
Contributory Status
| Interface Code | Description |
|---|---|
| CN | Contributing |
| NC | Not contributing |
Event Type
| Interface Code | Description |
|---|---|
| TERFIN | Termination |
| RETFIN | Retirement |
Language Preference
| Interface Code | Description |
|---|---|
| E | English Canada |
| F | French Canada |
Union Designation
| Interface Code | Description |
|---|---|
| AAHPO | American Armenian Health Professionals Organization |
| CIPP | Clinical Investigator Preparatory Program |
| CLAC | Christian Labour Association of Canada |
| CLC | Canadian Labour Congress |
| CUOE | Canadian Union of Operating Engineers |
| CUPE | Canadian Union of Public Employees |
| EA | |
| IBEW | International Brotherhood of Electrical Workers |
| ICTU | Independent Canadian Transit Union |
| ITE | Institute of Transportation Engineers |
| IUOE | International Union of Operating Engineers |
| LIUNA | Labourers’ International Union of North America |
| MULTI | Multiple Union Designation |
| NONE | No Union Designation |
| OCHU | Ontario Council of Hospital Unions |
| ONA | Ontario Nurses’ Association |
| OPEIU | Office and Professional Employees International Union |
| OPS | Ontario Public Service |
| OPSEU | Ontario Public Service Employees Union |
| OTHER | Union Designation Not Listed |
| PHF | Public Health Foundation |
| PNFO | Practical Nurses Federation of Ontario |
| SEIU | Service Employees International Union Healthcare |
| UFCW | United Food and Commercial Workers |
| UNIFOR | Union for Canada |
| UPGWA | International Union, United Plan Guard Workers of America |
Country
| Interface Code | Description |
|---|---|
| CA | Canada |
| US | United States |
Province/State
| Interface Code | Description |
|---|---|
| AB | Alberta |
| BC | British Columbia |
| MB | Manitoba |
| NB | New Brunswick |
| NL | Newfoundland and Labrador |
| NT | Northwest Territories |
| NS | Nova Scotia |
| NU | Nunavut |
| ON | Ontario |
| PE | Prince Edward Island |
| QC | Quebec |
| SK | Saskatchewan |
| YT | Yukon |
| AL | Alabama |
| AK | Alaska |
| AZ | Arizona |
| AR | Arkansas |
| CA | California |
| CO | Colorado |
| CT | Connecticut |
| DE | Delaware |
| DC | District of Columbia |
| FL | Florida |
| GA | Georgia |
| HI | Hawaii |
| ID | Idaho |
| IL | Illinois |
| IN | Indiana |
| IA | Iowa |
| KS | Kansas |
| KY | Kentucky |
| LA | Louisiana |
| ME | Maine |
| MD | Maryland |
| MA | Massachusetts |
| MI | Michigan |
| MN | Minnesota |
| MS | Mississippi |
| MO | Missouri |
| MT | Montana |
| NE | Nebraska |
| NV | Nevada |
| NH | New Hampshire |
| NJ | New Jersey |
| NM | New Mexico |
| NY | New York |
| NC | North Carolina |
| ND | North Dakota |
| OH | Ohio |
| OK | Oklahoma |
| OR | Oregon |
| PA | Pennsylvania |
| RI | Rhode Island |
| SC | South Carolina |
| SD | South Dakota |
| TN | Tennessee |
| TX | Texas |
| UT | Utah |
| VT | Vermont |
| VA | Virginia |
| WA | Washington |
| WV | West Virginia |
| WI | Wisconsin |
| WY | Wyoming |
| PR | Puerto Rico |
...