EPY: Calc Error During FLSA Bonus Payment (Doc ID 2410900.1)

Last updated on MAY 03, 2019

Applies to:

Symptoms

On : 9.2 version, Core Processes
Payroll for NA Calc Error during FLSA bonus payment

While running pay calc for a quarterly VC bonus payment we hit a unique constraint error inserting into PAY_OTH_EARNS due to a bug in the PSPMFLSA COBOL and associated stored statement PSPMFLSA_S_ADDLOTH. That statement is missing WHERE clause criteria to ensure it is only selecting data for the current employee being processed. As a result that SQL returned an ADDL_NBR value from another employee's pay line which resulted in the program trying to insert using keys that were already present on PAY_OTH_EARNS. Because the stored statement SQL does not include EMPLID and has an OR for PAYGROUP criteria it can pick up another paygroup in the same company which also has the same page/line number but that pay line belongs to another employee.

ERROR
-----------------------
Return Code: 00805, ORA-00001

Application Program Failed
 In Pgm Section : INSERT-PAY-OTH-EARNS-REV(INSERT)
 With Return Code: 00805
 Error Message : ORA-00001: unique constraint (SYSADM.PS_PAY_OTH_EARNS) violated


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Create Paysheets
2. Run Paycalc process

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot complete Payroll and pay their employees correctly.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.