My Oracle Support Banner

GP_PAYAFTER Unique Constraint Due To Non-Recognition Of EFF_SEQ (Doc ID 2434401.1)

Last updated on FEBRUARY 05, 2019

Applies to:

PeopleSoft Enterprise HCM Absence Management - Version 9.2 and later
Information in this document applies to any platform.

Symptoms

On : 9.2 version, Processes

GP_PAYAFTER process is failing.

The GP_PAYFTER application engine (within job GP_PAYE) is failing because of one employee for whom there are two TERMINATION (HR_STATUS - INACTIVE) rows in the job data with both empl record '0' but with different effective dates.
 
ERROR
-----------------------
File: /vob/peopletools/src/psappeng/aedebug.hSQL error. Stmt #: 1703 Error
Position: 56 Return: 1427 - ORA-01427: single-row subquery returns more than
one row
Failed SQL stmt: UPDATE PS_GP_ABS_ANLYTICS SET gp_ATTRIBUTE_NAME = NVL((
SELECT 'TERM' FROM PS_JOB A WHERE A.emplid=PS_GP_ABS_ANLYTICS.emplid AND
a.empl_rcd=PS_GP_ABS_ANLYTICS.empl_rcd AND a.action
IN('LOF','RET','RWP','TDL','TER','TWP','TWB') AND a.effdt <=( SELECT
MAX(b.prd_end_dt) FROM PS_GP_ABS_ANLYTICS b WHERE b.emplid=a.emplid AND
b.empl_rcd=a.empl_rcd AND b.gp_paygroup=a.gp_paygroup) AND a.effdt >=( SELECT
MIN(b.prd_bgn_dt) FROM PS_GP_ABS_ANLYTICS b WHERE b.emplid=a.emplid AND
b.empl_rcd=a.empl_rcd AND
b.gp_paygroup=a.gp_paygroup)),PS_


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Create employee job change for same date, but different effective sequence.


Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


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