ELM 9.2 : LM_LD_ASYNC Process Is Failing with LM_LD_PR_POI.INCRSYNC.UPD_ATTR for LM_PER_ORG - Multiple jobs associated with different dept (Doc ID 2177114.1)

Last updated on JANUARY 08, 2017

Applies to:

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

Symptoms

On : ELM 9.2 version, Integration

Issue Replication :
LM_LD_ASYNC process is failing for multiple jobs with different departments.

 

Error:

 Error Position: 115 Return: 1 427 - ORA-01427: single-row subquery returns more than one row Failed SQL stmt

Process 88077 ABENDED at Step LM_LD_PR_POI.INCRSYNC.UPD_ATTR (Action SQL) -- RC = 1427
ROLLBACK

File: /vob/peopletools/src/psappeng/aedebug.hSQL error. Stmt #: 1703 Error Position: 115 Return: 1 427 - ORA-01427: single-row subquery returns more than one row Failed SQL stmt:
UPDATE PS_LM_PERSON_ATTRB SET LM_ORGANIZATION_ID = ( SELECT LM_DEF_ORG_ID FROM PS_LM_IN_DFLT_TBL) , LM_PER_ORG = ( SELECT DISTINCT(J.LM_PER_ORG) FROM PS_LM_PERSON_JOB J WHERE J.LM_PERSON_ID = PS_LM_PERSON_ATTRB.LM_PERSON_ID AND J.LM_EMPL_RCD = PS_LM_PERSON_ATTRB.LM_EMPL_RCD AND J.EFFDT = PS_LM_PERSON_ATTRB.EFFDT), LM_ACTIVE = ( SELECT DISTINCT(J.LM_ACTIVE) FROM PS_LM_PERSON_JOB J WHERE J.LM_PERSON_ID = PS_LM_PERSON_ATTRB.LM_PERSON_ID AND J.LM_EMPL_RCD = PS_LM_PERSON_ATTRB.LM_EMPL_RCD AND J.EFFDT = PS_LM_PERSON_ATTRB.EFFDT) , LM_JOB_INDICATOR = 'P' WHERE LM_PERSON_ID IN ( SELECT STG.LM_PERSON_ID FROM PS_LM_STG_PRS_ATT STG , PS_LM_PERSON_JOB JOB WHERE STG.PROCESS_INSTANCE = 88077 AND JOB.LM_PERSON_ID = STG.LM_PERSON_ID AND JOB.LM_EMPL_RCD = STG.LM_EMPL_RCD AND JOB.EFFDT = STG.EFFDT AND JOB.LM_PER_ORG = 'POI' AND JOB.LM_PERSON_ID = PS_LM_PERSON_ATTRB.LM_PERSON_ID AND JOB.LM_EMPL_RCD = PS_LM_PERSON_ATTRB.LM_EMPL_RCD AND JOB.EFFDT = PS_LM_PERSON_ATTRB.EFFDT )


Replication Steps:

1. Ensure WORKFORCE_SYNC Service Operation is integrated from HRMS to ELM.

2. Create employment instances with POI and EMP

3. EMP and POI associated with different departments

4. Save records, which triggers WORKFORCE_SYNC message from HRMS to ELM.

5. Verify data loaded into the ELM Staging tables.

6. Run Asynchronous Process Data (Applicaiton Engine LM_LD_ASYNC)




Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms