P55 Workaround - Box 26 CPP Gross as 68,263.99

Box 26 CPP Gross 68,263.99 Workaround

GOAL: To provide a set of instructions alongside the script which was shared with the community to identify the outlier small amount of Employees which satisfy the scenario to have CPP_GROSS slightly off. 

Affected Scenario: CPP_Gross applicable earnings exceed 73,200 across 2 different Positions under different REVCAN Codes, Box 26 value is dropping to 68,263.99 for main Employee group. 

It is our expectation that this will be a relatively small number of employees, compared to total employees. 

Our recommendation would be to move forward with Patch 54 and make use of the below to identify and correct this scenario for impacted employees that boards may or may not have. 

Script to Identify Affected Employees:

SELECT

    y.EMPLOYEE_ID,

    e.SURNAME,

    e.FIRST_NAME,

    COUNT(DISTINCT y.EMP_GROUP_CODE) AS Num_Groups,

    COUNT(DISTINCT y.REVCAN_CODE) AS Num_T4_Slips,

    SUM(y.AMOUNT) AS CPP_Gross,

    73200-SUM(y.AMOUNT) as YTD_Adjustment,

    (   SELECT y3.EMP_GROUP_CODE + '=' + CAST(SUM(y3.AMOUNT) AS VARCHAR) + '     '

        FROM EC_EMPLOYEE_YTD_ENTITY AS y3

        WHERE y3.EMPLOYEE_ID = y.EMPLOYEE_ID

            AND y3.YEAR = y.YEAR

            AND y3.ENTITY_TYPE = y.ENTITY_TYPE

        GROUP BY

            y3.EMP_GROUP_CODE,

            y3.REVCAN_CODE

        ORDER BY

            y3.REVCAN_CODE,

            y3.EMP_GROUP_CODE

        FOR XML PATH('')

    ) AS CPP_Gross_Breakdown,

    (   SELECT SUM(y1.AMOUNT)

        FROM EC_EMPLOYEE_YTD_ENTITY AS y1

        WHERE y1.EMPLOYEE_ID = y.EMPLOYEE_ID

            AND y1.YEAR = y.YEAR

            AND y1.ENTITY_TYPE = 'CPP'

            AND y1.ENTITY_CLASS = 'E'

    ) AS CPP_Dedn,

    (   SELECT y3.EMP_GROUP_CODE + '=' + CAST(SUM(y3.AMOUNT) AS VARCHAR) + '     '

        FROM EC_EMPLOYEE_YTD_ENTITY AS y3

        WHERE y3.EMPLOYEE_ID = y.EMPLOYEE_ID

            AND y3.YEAR = y.YEAR

            AND y3.ENTITY_TYPE = 'CPP'

            AND y3.ENTITY_CLASS = 'E'

        GROUP BY

            y3.EMP_GROUP_CODE,

            y3.REVCAN_CODE

        ORDER BY

            y3.REVCAN_CODE,

            y3.EMP_GROUP_CODE

        FOR XML PATH('')

    ) AS CPP_Dedn_Breakdown,

    (   SELECT SUM(y2.AMOUNT)

        FROM EC_EMPLOYEE_YTD_ENTITY AS y2

        WHERE y2.EMPLOYEE_ID = y.EMPLOYEE_ID

            AND y2.YEAR = y.YEAR

            AND y2.ENTITY_TYPE = 'CPP2'

            AND y2.ENTITY_CLASS = 'E'

    ) AS CPP2_Dedn,

    (   SELECT y3.EMP_GROUP_CODE + '=' + CAST(SUM(y3.AMOUNT) AS VARCHAR) + '     '

        FROM EC_EMPLOYEE_YTD_ENTITY AS y3

        WHERE y3.EMPLOYEE_ID = y.EMPLOYEE_ID

            AND y3.YEAR = y.YEAR

            AND y3.ENTITY_TYPE = 'CPP2'

            AND y3.ENTITY_CLASS = 'E'

        GROUP BY

            y3.EMP_GROUP_CODE,

            y3.REVCAN_CODE

        ORDER BY

            y3.REVCAN_CODE,

            y3.EMP_GROUP_CODE

        FOR XML PATH('')

    ) AS CPP2_Dedn_Breakdown

FROM EC_EMPLOYEE_YTD_ENTITY AS y

INNER JOIN EC_EMPLOYEE AS e

    ON y.EMPLOYEE_ID = e.EMPLOYEE_ID

WHERE y.YEAR = '2024' /* hard-coded */

    AND y.ENTITY_TYPE = 'CPP_GROSS'

GROUP BY

    y.EMPLOYEE_ID,

    e.SURNAME,

    e.FIRST_NAME,

    y.YEAR,

    y.ENTITY_TYPE

HAVING SUM(y.AMOUNT) > 73200 /* hard-coded */

    AND COUNT(DISTINCT y.REVCAN_CODE) > 1

ORDER BY

    y.EMPLOYEE_ID

Sample Results and Scenario:

**YTD_Adjustment amount correlates to the CPP_GROSS of the Secondary Group**

Results will commonly display this deduction scenario; 

-CPP & CPP2 Deductions completed under Primary Group, 

**No Deduction for CPP under Secondary Group**

SCENARIO 2: Employee has 3 Positions, 2 Secondary SUM to Adjustment

-2 secondary Positions SUM to YTD_Adjustment

Corrective Actions:

Boards can navigate to PAYROLL > PAY PROCESS > YTD RECORD ADJUSTMENT for the impacted EID

FIX = Search EID + Secondary EMP_GROUP + 2024 and ‘zero-out’ CPP Gross