Load Ontario Teachers Insurance Plan (OTIP) Member, Position, Leave (MPL) Export File Data into the OTIP Worksheet

Before you can create an Ontario Teachers Insurance Plan (OTIP) Member, Position, Leave (MPL) export file, you must load data into the OTIP Worksheet page.

The OTIP data has Member (MEM), Position (POS), and Leave (LEV) sections. When the data is calculated and loaded, the data is loaded in the following order:

  1. MEM

  2. POS

  3. LEV

Number of lines per section type

The number of lines that are loaded into the OTIP Worksheet page depends on the section type.

For MEM, there is one line per employee.

For POS, there is one line per position and employee.

For LEV, there is one line per position and employee.

Benefit eligible HR employee assignments

When you load lines, the benefit eligible HR employee assignments data is filtered differently for each section.

To determine the benefit eligible HR employee assignments to load into the OTIP Worksheet page, the following logic is used:

  • An active assignment is an assignment that is active as of the To Date that is specified on the OTIP Load Lines page and is eligible for OTIP.

  • On the Employee Benefits page:

    • Benefit Plan Code is OTIP.

    • Effective Date is less than the To Date that is specified on the OTIP Load Lines page.

    • Not Eligible/Not Participating checkbox is clear.

    • Blocked checkbox is clear.

  • On the HR Employee Assignments page:

    • When there are multiple active benefit eligible HR employee assignments, to determine the primary assignment, the lowest HR Position Rank is used. If there are multiple assignments with the same lowest rank, the assignment which has the highest Active FTE and the lowest Entry No. is used.

    • Entry Type is Assignment

  • Active benefit eligible HR employee assignments for the employee where the Union Code on the HR Employee Assignment Card page is specified on the Provincial Benefits Data Exchange Setup page, on the OTIP FastTab, in the Union Codes field.

  • The period is determined based on the section type. The following logic is used:

    • MEM: An employee who had one or more active assignments, or who had one or more active assignments on leave, or both, for the period ending as of the To Date that is specified on the OTIP Load Lines page and the period starting 16 months prior.

    • POS: An employee who had one or more active assignments, or who had one or more active assignments on leave, or both, for the period ending as of the To Date that is specified on the OTIP Load Lines page. Lines are loaded for active, on leave, and terminated assignments.

      The following logic is used for active assignments:

      • The one or more active assignments do not have a leave portion.

      • The one or more active assignments do not have a terminated position.

      The following logic is used for leave assignments:

      • For a partial leave, where the Active FTE is greater than 0 and the Leave FTE is greater than 0, two lines are reported. The line with the portion on leave has a Position Status of I and the FTE displays the value for Leave FTE. The line with the active position has a Position Status of A and the FTE displays the value for Active FTE.

      • For a full leave, where the Active FTE is 0 and the Leave FTE is greater than 0, two lines are reported. The line with the portion on leave has a Position Status of I and the FTE displays the value for Leave FTE. The line with the active position has a Position Status of I and the FTE has a value of 0.

        The Position Status of I represents inactive and the Position Status of A represents active.

      • The following table describes how Sparkrock 365 manages several leave scenarios.

        Scenario Position FTE Position Status Leave FTE
        Employee works 1.00 and is actively at work. 1.00 A Not applicable
        Employee works 1.00 and is on a full leave. 1.00 I 1.00
        Employee works 0.80 and is actively at work. 0.80 A Not applicable
        Employee works 0.80 and is on a full leave. 0.80 I 0.80
        Employee works 1.00 and is on a part time leave, working 0.60 and on leave 0.40. Position 1: 0.60
        Position 2: 0.40        		 Position 1: A
        Position 2: I        		 0.40
        Employee works 0.80 and is on a part time leave, working 0.60 and on leave 0.40. Position 1: 0.48
        Position 2: 0.32        		 Position 1: A
        Position 2: I        		 0.32
        Employee works two positions with FTEs of 0.60 and 0.40 and is actively at work. Position 1: 0.60
        Position 2: 0.40        		 Position 1: A
        Position 2: A        		 Not applicable
        Employee works two positions with FTEs of 0.60 and 0.40 and is on a full leave. Position 1: 0.60
        Position 2: 0.40        		 Position 1: I
        Position 2: I        		 Leave 1: 0.60
        Leave 2: 0.40
        Employee works two positions with FTEs of 0.60 and 0.40 and is on leave from the 0.40 position. Position 1: 0.60
        Position 2: 0.40        		 Position 1: A
        Position 2: I        		 0.40

      The following logic is used for terminated assignments:

      • Terminated positions are reported only when the employee has an active position or a position on leave.

      • The following are examples of terminated assignments:

        • A position is terminated in the last 16 months, but another with a different position code remains active as of the To Date that is specified on the OTIP Load Lines page.

        • An assignment transfer took place in the 16 months before the To Date that is specified on the OTIP Load Lines page.

        • The employee had a temporary assignment and the assignment ended during the past 16 months. If multiple assignments are found for the same position, the assignment with the highest Entry No. is used.

    • LEV: An employee who has one or more assignments on leave as of the To Date that is specified on the OTIP Load Lines page.

Provincial Benefits data exchange batch

Before you can load OTIP export file data into the OTIP Worksheet page, you must create a provincial benefits data exchange batch.

For more information see, Create a Provincial Benefits Data Exchange Batch.

To load lines into the OTIP Worksheet page

  1. Choose Lightbulb that opens the Tell Me feature., enter otip worksheet, and then choose the related link.

    The OTIP Worksheet page opens.

    If there are existing records that are no longer required, you can delete the records. For more information, see Delete OTIP MPL Export Data from the OTIP Worksheet.

  2. In Batch Name, specify the batch name that you want to work with.

  3. On the action bar, choose Load Lines.

    The OTIP Load Lines page opens.

  4. Specify values as required, and then choose OK.

    Worksheet lines are loaded based on the values that you specified.

Salary amount

On the OTIP Worksheet page, the Salary Amount field specifies the employee’s calculated annual salary based on rules for allowances that are included and excluded as determined by OTIP, CUPE, or both. If the value is not available, the amount reported is the previous year’s earnings based on an hourly rate.

The calculation for this field uses the regular rate of pay, before deductions, excluding bonuses and overtime pay. If required, the calculation includes vacation allowance, otherwise, the vacation allowance is populated separately.

The total annual salary is calculated as follows:

Annual Salary + Allowance Salary

The annual salary calculation differs depending on the values on the HR Employee Assignment Card page, in Pay Type and Work Period Factor.

Pay Type of Salary

If the Pay Type is Salary, the annual salary is calculated as follows:

Assignment Amount x Assignment FTE

Where the assignment FTE is the Active FTE or the Leave FTE depending on the status.

Pay Type of Hourly

If the Pay Type is Hourly and Hours/Day is not equal to zero and Days/Year is not equal to zero, the annual salary is calculated as follows:

Assignment Amount x Assignment Hours/Day x Assignment Days/Year

Pay Type of Per Diem

If the Pay Type is Per Diem and Days/Year is not equal to zero, the annual salary is calculated as follows:

Assignment Amount x Assignment Days/Year

Work Period Factor

The following list describes how the annual salary calculations differ based on the value of the Work Period Factor for an HR employee assignment:

  • If Work Period Factor is zero, the calculation uses the HR pay cycle.

  • If the Work Period Factor is not equal to zero and the Max Work Period Factor is not equal to zero, the annual salary is calculated as follows:

    (Assignment Amount x Assignment Hours/Week x 52 x Work Period Factor) / Max Work Period Factor

  • If the Work Period Factor is not equal to zero and the Max Work Period Factor is equal to zero, the annual salary is calculated as follows:

    Assignment Amount x Assignment Hours/Week x 52 x Work Period Factor

Allowance salary

To determine the allowance salary, the calculation uses the total of all assignment allowances where on the HR Emp. Assignment Allowances page, the Exclude from Actual Salary checkbox is clear. For active positions, the Active FTE that appears on the respective HR Employee Assignment Card page is used in the calculation. As required by OTIP, when the employee assignment is partly on leave, the Active FTE is used for active lines and the Leave FTE is used for inactive lines.

See also

OTIP MPL Export File Administration
Set Up the OTIP MPL Export

Feedback

To send feedback about this page, select the following link:

feedback-product-docs@sparkrock.com