File Upload

For Plan Rules, please visit the Admin Manual: Link

The following are step-by-step instructions on how to submit your annual Member Data Collection (MDC) via file upload. Follow the steps below and refer to the corresponding screenshots to process this transaction:

	After logging into HOOPP Insight you will land on the Dashboard home page. From the Data Collections tile, select the MDC Collection that has been initiated by HOOPP.
	Select Upload File to initiate the file upload process. Note: You can return to this screen at any time to upload additional files or manually enter additional members.
	From the Input Type drop-down menu, select MDC Data. Then drag and drop your file to the location shown, or click on Select Files from your Computer to select and upload your MDC file.
	The uploaded file will then be displayed under Uploaded File(s). Select Import Files to load and view the data.
	Once the data has been imported, your member records will be displayed on the screen. Any records containing input errors will be flagged with a red dot. The File Summary indicates how many errors need to be corrected.
	To view information about an error, select the member record, and details will be displayed on the right. Select Edit to view and correct the error.
	Correct the error and select Save to continue.
	Once all the input errors have been addressed, select Validate to initiate business validations.
	Business validations may take time depending on the number of member records. During this time you can continue working within HOOPP Insight, or on other applications on your computer, as long as Insight is left running in the background. You will receive an email alert when the validations are complete.
	Once the validations are complete, you will be presented with members who have data warnings that need to be addressed, or data errors that need to be corrected. You will now need to go to Checking for Missing Members.

This file is provided annually as part of the MDC (Member Data Collection) process.

Information provided in this file will update all active member’s employment records with HOOPP related to contributory service, contributions, pension adjustment amounts, and changes in their full-time/part-time status. It also allows you to provide Annualized Earnings for any employees who waived the right to contribute (prior to January 1, 2014) as a result of an employment status change from full-time to part-time.

Information provided should only include members who are active (i.e. currently employed). If the member terminated employment within the year they must be excluded from this file since the data has already been reported to HOOPP.
The following is a summary of requirements for reporting total weeks and contributions:

Weeks and contributions reported should reflect both regular and overtime hours up to the full-time equivalency with contributions deducted on the straight-time portion of all pensionable earnings that are paid in the calendar year.

Total weeks and contributions reported must not exceed 52 weeks for a given calendar year. For example, if the position is based on full-time of 1950 hours for a full calendar year, then contributions are deducted on all pensionable earnings up to 1950 hours. Contributions should not be deducted for hours the member works above the FTE for their position in any year.

Exception: In a 27 pay period year weeks must also not exceed 52 weeks, however you must report contributions from pensionable earnings for the extra pay period, even if the member has already reached the FTE for their position on or before the 26th pay period. Contributions should be deducted on all pensionable earnings up to the FTE for the member’s position plus the regular hours associated with the additional pay period. For example, if the position is based on full-time of 1950 hours (37.5 hours per week) then contributions would be made to a maximum of 2025 full-time hours.

Reported weeks and contributions are based on the maximum full-time standard hours that are available. This means that, if a member is full-time for the entire year, the member’s weeks and contributions should be based on 52 weeks. If less than 52 weeks of service is reported:

A valid reason must be provided, such as reporting a leave of absence to account for the service shortfall via the Leaves of Absence file, or update the member status to part-time if they have changed their employment type. Or,
If a member has less than 52 weeks but more than or equal to 51 weeks, weeks can be adjusted to 52 without adjusting contributions. This is meant to accommodate minor deviations (up to -1 week) from contributing at the full FTE for the period that may result from payroll scheduling and/or instances in which member trade shifts in one calendar for shifts that fall in the subsequent year.

If a member enrols or terminates part way through the year, contributions will be deducted from pensionable earnings on fewer than the full-time equivalent (FTE) hours in a year for their position. Determine the maximum hours on which contributions can be deducted by prorating the FTE hours for the full calendar year to reflect the portion of the year actually worked. This will give you the FTE maximum possible hours for the period (the partial year). Deducting contributions on pensionable earnings up to the maximum of the possible hours in the FTE for the period ensures that the possible weeks of contributory service in the period will not be exceeded.

Example:

A new employee is enrolled in the Plan effective March 15. The FTE for her position is 1950 hours for a full calendar year. Between March 15 and December 31 there are 292 days which represents 80% of a full calendar year (292 ÷ 365 = 0.8). The maximum hours on which you should deduct contributions is therefore 1560 hours (1950 hours x 80%)

In addition to the above, if the member has made contributions for a reported leave of absence within the MDC year, those weeks and contributions must be reported and included in the weeks and contributions total.

Exception: If weeks and contributions for periods of leave within the MDC year have already been reported as a lump sum contribution via the Remittance process for the same MDC year, they should be excluded from this file. Weeks and contribution information previously reported should not be reported again. For clarification, the 'Total Weeks Paid' reported within the Contributory Leave file must be included in the MDC Weeks total.
Note: Annualized earnings is only required for a very specific group of inactive members and represents a very low volume. If this information is not available from your payroll system and you are unable to program this requirement, you may choose not to populate this field. Within the HOOPP data collection process, we will review the data in the file and identify any members for which we are expecting Annualized Earnings. If it is expected and not provided, you will be prompted to provide this information by manually entering in the value.

Following are the data requirements in respect of the File Upload option.

Core Data

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
Employment Type	Most recent employment status of full-time or part-time	No	2	Code	FT
EmploymentType EffectiveDate	Effective date of the most recent employment status	No	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

Non Contributory Leaves

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 (refer to File Specs document for codes)	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.	No	8	Date	10302015

Contributory Leaves

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 (refer to File Specs document for codes)	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.	No	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

"Rather Watch? Click here to view the How to Download your MDC Template file Video"

"Rather Watch? Click here to view the How to Upload your Member Data File Video"