Troubleshooting Allowance DNP Entitlement Calculations

Troubleshooting Allowance DNP Entitlement Calculations

ISSUE: Allowance DNP is suspected to not be accurate. It is suspected that Amount Paid is partially Correct but the current RATE is being understated. 

ROOT CAUSE = RATE calculation takes into consideration the Number of Working Days, FTE and most importantly the Days Not Paid configuration under DNP Entry module. 

Forgetting about the DNP % to as the last multiplication factor can cause the belief that Rate is understated. 

EMPLOYEE ALLOWANCES

select COMPENSATION_RATE, MASS_UPD_IND, AUTHORIZED_FOR_PAY_NUMBER, * from EC_EMPLOYEE_ALLOWANCES

where EMPLOYEE_ID = 'xxxx'

and ALLOWANCE_START_DATE = '2019-09-01'

**2 Positions with FTE = 0.8 & 0.2 is reasoning for 2 records w/ Compensation Rate = 9.16**

EMPLOYEE DNP

select DAYS_NOT_PAID_DATE, DNP_AMOUNT, DAILY_RATE, DNP_ALLOWANCE, REF_ID, * from EC_EMPLOYEE_DAYS_NOT_PAID_INP

where EMPLOYEE_ID = 'xxxx'

and DAYS_NOT_PAID_DATE between '2019-09-01' and '2020-08-31'

**12 records with 0.00 and 3.71 as expected**

GROUP SETUP

select WORK_DAYS_PER_YEAR, * from EC_GROUP

where EMP_GROUP_CODE = 'ELEMTEAC'

and END_DATE is null

**Number of Working Days = 194**

RATE = 916/194 = 4.7216

RATE * FTE = RATE 1

RATE * FTE = RATE 2

4.7216 * 0.8 = 3.78

4.7216 * 0.2 = 0.94

DAYS NOT PAID ENTRY

**Given the Days Not Paid amount is not 1.0, this factor must be multiplied in**

RATE * FTE * DNP% = RATE 1

RATE * FTE * DNP% = RATE 2

JOB CODE: ELEMSPEC

Original DNP Rate > $900.00 ÷ 194 * 0.8 * 0.8 = $2.97

Year 2020 Rate > $909.00 ÷ 194 * 0.8 * 0.8 = $3.00

Year 2024 Rate > $916.00 ÷ 194 * 0.8 * 0.8 = $3.02

JOB CODE: ELEMTEAC

Original DNP Rate > $900.00 ÷ 194 * 0.2 * 0.2 = $0.19

Year 2020 Rate > $909.00 ÷ 194 * 0.2 * 0.2 = $0.19

Year 2024 Rate > $916.00 ÷ 194 * 0.2 * 0.2 = $0.19 

**Both RATES now verified**

Common Misconception: The system is multiplying the portion of the position (FTE) twice. 

Below we can see a good summary for this; 

select DAYS_NOT_PAID, DAYS_NOT_PAID_DAYS, DAYS_NOT_PAID_DATE, DNP_AMOUNT, DAILY_RATE, DNP_ALLOWANCE, REF_ID, * from EC_EMPLOYEE_DAYS_NOT_PAID_INP

where EMPLOYEE_ID = 'xxxx'

and DAYS_NOT_PAID_DATE >= '2020-01-30'

**Here we can see a better view of how the system views and compiles this**

We can see the DAYS NOT PAID = 0.8 and 0.2 however the DAYS NOT PAID DAYS adds both records for different Job Codes that land on the same day to yield DAYS NOT PAID DAYS = 1. 

Since the Entitlement details each DNP for each Job Code separately, we see the additional *0.8 and *0.2 multiplied in. 

Example: it shows DNP for 2020 01 30 twice, once as 0.8 and the other as 0.2 so it must do the extra multiplication layer.

TROUBLESHOOTING AMOUNT PAID VALUES

Note: Amount Paid is accumulated through a series of commands within the system that compiles DNP records, commonly within the EC_EMPLOYEE_PAY_RECORD2. 

FIX 1: Ensure your system is patched and contains the P46v3 hotfix. 

This provided both historical data fixes and SP updates to ensure further accuracy of future calculations concerning Allowance Amount Paid values. 

FIX 2: Examine further COVERAGE_CODES the script does not detect

select REFERENCE, AMOUNT, COVERAGE_CODE, JOB_CODE, * from EC_EMPLOYEE_PAY_RECORD2

where EMPLOYEE_ID = 'xxxx'

and PAY_NUMBER >= 'YYYYNN'

and ENTITY_TYPE = 'ALLOWANCE'

and ENTITY_CODE = 'DAYS_NOT_PAID'

and REFERENCE is NOT NULL

**Update COVERAGE_CODES = NULL to the appropriate COVERAGE_CODE**

RE-RUN ENTITLEMENT