Troubleshooting [OLD] Debit Pay Including Incorrect Employees

Troubleshooting [OLD] Debit Pay Including Incorrect Employees

ISSUE: The Debit Pay Pre-Run Process has 2 drivers that indexes Employees to be included into (OLD) Debit Pay Process. 

1. Positions
2. Benefit Records open

One common scenario which can include Employees in Debit Pay against expectations would be when the Position record is closed, but the Benefit records remain open. 

This will cause the Debit Pay Pre Run Process to include these employees into the EC_EMPLOYEE_PAY_ACTIVITY table, and once they are included they will not drop off. 

Patch Fix:

Firstly, in Patch 5.0.50 release, there was a Code Change to not process F5 Fix when Debit Pay Type = D, as this would pull a bunch of incorrect Employees into Debit Pay.

Procedural Improvements:

In addition to the above fix, there are important procedural improvements to ensure teams follow.

1) In the case of Debit Pay it is always best practice to close the Benefit Records when the Position is closed. 

Sometimes here despite the position being closed, many of these individuals might have Benefit Records remain open - which will cause the Debit Pay Pre-Run Process to include them in the Debit Pay Pre Run - which should not be the case. 

FIX: We have created 2 scripts, 1 to identify the Employee with Closed Position + Open Benefits, and a Second to Close the Benefits in bulk for these employees. 

2) Once this is complete - we have another script to clear out your EC_EMPLOYEE_PAY_ACTIVITY. 

NOTE: This is to clear out Pay Activity Table for a specific Debit Pay Period, and only for Debit Pay Group.

This is because the Employees with Closed Position, and Open Benefits which were included in the Pre-Run process before, never drop out of the EC_EMPLOYEE_PAY_ACTIVITY table when the Debit Pay Pre-Run process is run again.

FIX: This table needs to be purged for the Previous Debit Pay Period, for Debit Pay Groups, to have the erroneous employees drop off. 

3) Once the above data has been fixed and the table has been cleansed - you must run Entitlement + Group Authorization 

NOTE: This is because benefits were closed.  

4) Finally, run Debit Pay Pre Run & Debit Pay Run as per normal

This time the process will only include the appropriate employees and work as expected. 

PROCESS STEPS OVERVIEW:

1) Script to close Benefits for Employees with Closed Positions (as per best practice)

2) Script to purge EC_EMPLOYEE_PAY_ACTIVITY table (to get erroneous employees to drop off) 

3) Entitlement > Group Authorization > Schedule Group > Debit Pay Pre Run > Debit Pay Run as per normal

Note: Entitlement + Group Authorization is needed on the first run after Steps 1 & 2 because Benefits were closed

Additional Issue + Fix:

One additional data configuration that can cause tax within is when Employees have Extra Tax Per Pay Deduction configured under a Debit Pay group. 

This does not align with Best Practice because this should not be inputted for Debit Pay groups since Debit Pay should never get tax. 

PAYROLL > EMPLOYEE > STATUTORY DEDUCTION > EID

PROCESS > COMPLETED > PAY REGISTER > EID

**Note the above override will cause Tax to show under Debit Pay – This is not best practice**

STEP 1 Script:

/* Script to close the benefits when the corresponding positions are closed. */

SELECT POS.EMPLOYEE_ID, POS.EMP_GROUP_CODE, BEN.EMP_GROUP_CODE, POS.POSITION_START_DATE, POS.POSITION_END_DATE, BEN.BENEFIT_END_DATE, BEN.DEDUCTION_END_DATE, * 

FROM EC_EMPLOYEE_POSITIONS POS

INNER JOIN EC_EMPLOYEE_BENEFITS BEN 

ON POS.EMPLOYEE_ID = BEN.EMPLOYEE_ID AND POS.EMP_GROUP_CODE = BEN.EMP_GROUP_CODE

WHERE BEN.BENEFIT_END_DATE IS NULL

AND (POS.POSITION_END_DATE IS NOT NULL AND POS.POSITION_END_DATE <= GETDATE())

AND POS.EMP_GROUP_CODE IN ('PREGLOAS', 'RETIREES', 'LONGTERM', 'UNPDLOAS')

ORDER BY POS.POSITION_END_DATE DESC

UPDATE BEN SET BEN.BENEFIT_END_DATE = POSITION_END_DATE, DEDUCTION_END_DATE = POSITION_END_DATE

FROM EC_EMPLOYEE_POSITIONS POS

INNER JOIN EC_EMPLOYEE_BENEFITS BEN 

ON POS.EMPLOYEE_ID = BEN.EMPLOYEE_ID AND POS.EMP_GROUP_CODE = BEN.EMP_GROUP_CODE

WHERE BEN.BENEFIT_END_DATE IS NULL

AND (POS.POSITION_END_DATE IS NOT NULL AND POS.POSITION_END_DATE <= GETDATE())

AND POS.EMP_GROUP_CODE IN ('PREGLOAS', 'RETIREES', 'LONGTERM', 'UNPDLOAS')

STEP 2 Script: 

/* 

    Script to clean up the EC_EMPLOYEE_PAY_ACTIVITY table for the current pay. 

    Need to identify and fill in the pay number before executing. 

*/

--SELECT * FROM EC_EMPLOYEE_PAY_ACTIVITY 

--WHERE EMP_GROUP_CODE IN ('XXXXXX', 'YYYYYY', 'ZZZZZZ', 'XXYYZZ') 

--AND PAY_NUMBER = 'XXX' -- XXX is the current pay number.

DECLARE @PAY_NUMBER VARCHAR(6) = 'XXX' -- XXX is the current pay number.

IF (@PAY_NUMBER = 'XXX')

BEGIN

    PRINT 'Please set the correct @PAY_NUMBER. It should not be XXX'

END

ELSE

BEGIN

    DELETE EC_EMPLOYEE_PAY_ACTIVITY WHERE EMP_GROUP_CODE IN ('XXXXXX', 'YYYYYY', 'ZZZZZZ', 'XXYYZZ') AND PAY_NUMBER = @PAY_NUMBER

    PRINT 'Successfully purged the data in table EC_EMPLOYEE_PAY_ACTIVITY'

END