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:
- Update the Location Code for the Allowance Record to the current Position Location Code
- Re-generate CUPE MPL
The calculated Annual Allowance will be added to the Annual Income and reflect 40,983.60 and resolve the issue.