CUPE Allowance not added in MPL Annual Income

CUPE Allowance not added in MPL Annual Income

ISSUE: On the CUPE Position File, the Annual Income does not reflect the Allowance value as well. 

STEPS TO REPLICATE:

select EMPLOYEE_ID, EMP_GROUP_CODE, POSITION_START_DATE, POSITION_END_DATE, ACTUAL_FTE, BENEFIT_PLAN from EC_EMPLOYEE_POSITIONS

where EMPLOYEE_ID in ('___885')

and POSITION_START_DATE > '2021-09-01'

**Active CUPE Position with FTE = 1.0**

select EMPLOYEE_ID, EMP_GROUP_CODE, SALARY_START_DATE, SALARY_END_DATE, COMPENSATION_RATE, HOURLY_RATE from EC_EMPLOYEE_SALARY

where EMPLOYEE_ID in ('___885')

and SALARY_START_DATE > '2022-06-01'

**Hourly Rate = 27.92**

select EMPLOYEE_ID, EMP_GROUP_CODE, JOB_CODE, ALLOWANCE_CODE, COMPENSATION_RATE, OVERRIDE_FTE, ALLOWANCE_START_DATE, ALLOWANCE_END_DATE from EC_EMPLOYEE_ALLOWANCES

where EMPLOYEE_ID in ('___885')

and ALLOWANCE_START_DATE > '2020-01-01'

**Active Allowance for the same EMP_GROUP**

**Allowance Compensation Rate = 0.78**

EXPECTED OUTCOME:

Hourly Rate and Allowance Hourly Rate would be extrapolated into an Annual Salary and Annual Allowance and therefore added together under the Annual Income column. 

Note: Hourly Rate field would remain as is for Compensation. 

This is because the Annual Income is more meaningful and important to OTIP/CUPE/COWAN. 

Mathematical Example:

Hourly Rate Allowance = 0.78

Hours per week = 35

Number of days a week = 5

Numbers of Working Days a Year = 204

Annual Allowance = 0.78 * (35/5) * 204

=1,113.84

Annual salary = 27.92 * (35/5) * 204 

=39,869.76

Annual Income + Annual Allowance

=40,983.60

As shown below from the MPL screenshot at the time, the Allowance was not being added to the Annual Income as the Location Code did not match - which is an important dependency within the system.

NOTE: The previous position (at the matching Location Code) had the updated Annual Income to include Allowance. 

select EMPLOYEE_ID, EMP_GROUP_CODE, LOCATION_CODE, POSITION_START_DATE, POSITION_END_DATE, ACTUAL_FTE, BENEFIT_PLAN from EC_EMPLOYEE_POSITIONS

where EMPLOYEE_ID in ('___885')

and POSITION_START_DATE > '2021-09-01'

NOTE: Here we can see how the Location Code is no longer 328xxxx. 

select LOCATION_CODE, * from EC_EMPLOYEE_ALLOWANCES

where EMPLOYEE_ID = '___885'

NOTE: here we can see how the Location Code is tied to 328xxxx. 

STEPS TO RESOLVE: 

1. Update the Location Code for the Allowance Record to the current Position Location Code
2. Re-generate CUPE MPL 

The calculated Annual Allowance will be added to the Annual Income and reflect 40,983.60 and resolve the issue.