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
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.
On : ELM 9.2 version, Integration
Issue Replication :
LM_LD_ASYNC process is failing for multiple jobs with different departments.
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
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 )
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)
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