Resolving JBO-27014: Attribute RelationshipId in ElementEntryDEO Required Error in HDL
Description
Learn how to fix the HDL error 'JBO-27014: Attribute RelationshipId in ElementEntryDEO is required' in Oracle Fusion HCM. Step-by-step solution includes work relationship cancellation, Source Key updates, and historical Element Entry reload.
Introduction
During Element Entry record loads using HCM Data Loader (HDL) in Oracle Fusion HCM (version 11.13.22.10.0), you may encounter the following error:
An error occurred. To review details of the error run the HCM Data Loader Error Analysis Report diagnostic test.
Message details: JBO-27014: Attribute RelationshipId in ElementEntryDEO is required
This error occurs because existing Element Entry records reference Source Keys associated with Payroll Relationships that start before Work Relationship and Assignment records, causing conflicts in date-effective loads.
This blog provides a step-by-step solution, SQL queries for verification, and best practices for handling historical Element Entry records.
Cause of the Error
Existing Element Entry records on Fusion HCM use the same Source Keys as the new HDL file.
Payroll Relationship start dates are earlier than the Work Relationship and Assignment records.
The Attribute RelationshipId is mandatory in ElementEntryDEO to link Element Entries with valid employment records.
Conflicts arise when the existing date-effective records overlap or reference the same keys.
Steps to Reproduce the Error
Attempt to load new Element Entry records via HDL.
Observe the JBO-27014 error.
Step-by-Step Solution
1. Extract Existing Employment Details
If no HDL integration exists with a third-party system (e.g., PeopleSoft):
Navigate to Run Diagnostics Tests via the login icon.
Add the role Application Diagnostics Administrator if the link is unavailable.
Run the Worker Data Extract diagnostic test:
Input parameters: Person Number, Exclude Highly Restricted Columns = false
Execute and download the WorkerDataExtract.zip file
If an HDL integration exists, update the Hire or Start Date in the third-party system to match the Payroll Relationship's Start Date.
2. Cancel the Work Relationship
Option A – Source Keys
SET PURGE_FUTURE_CHANGES_N
METADATA|WorkRelationship|DateStart|LegalEmployerName|CancelWorkRelationshipFlag|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)
MERGE|WorkRelationship|<DateStart>|<LegalEmployerName>|Y|<SourceSystemId>|<SourceSystemOwner>|<PersonId(SourceSystemId)>
Option B – User Keys
SET PURGE_FUTURE_CHANGES_N
METADATA|WorkRelationship|DateStart|PersonNumber|LegalEmployerName|PeriodType|CancelWorkRelationshipFlag
MERGE|WorkRelationship|<DateStart>|<PersonNumber>|<LegalEmployerName>|<PeriodType>|Y
3. Reload or Recreate Work Relationship, Work Terms, and Assignments
If HDL integration exists, submit historical Worker, WorkTerms, Assignment and child Assignment objects in HDL.
If no integration, use Worker Data Extract or manual UI updates.
Use User Keys for object references; surrogate keys may be used for attributes (Business Unit ID, Organization ID, Job ID).
4. Reload the Original Element Entry HDL File
Update Source Keys for existing Element Entry records to avoid conflicts.
SQL queries for parent and child records:
Parent Element Entry
select hikm.GUID EE_GUID, hikm.SOURCE_SYSTEM_ID EE_SSID, hikm.SOURCE_SYSTEM_OWNER EE_SSO, peef.ELEMENT_ENTRY_ID EE_ID, TO_CHAR(peef.EFFECTIVE_START_DATE,'YYYY-MM-DD') EE_ESD, TO_CHAR(peef.EFFECTIVE_END_DATE,'YYYY-MM-DD') EE_EED, peef.ENTRY_TYPE ENTRY_TYPE
from fusion.HRC_INTEGRATION_KEY_MAP hikm, fusion.PAY_ELEMENT_ENTRIES_F peef
where hikm.OBJECT_NAME = 'ElementEntry'
Child Element Entry Value
select hikm.GUID EEV_GUID, hikm.SOURCE_SYSTEM_ID EEV_SSID, hikm.SOURCE_SYSTEM_OWNER EEV_SSO, peevf.ELEMENT_ENTRY_VALUE_ID EE_VAL_ID, peevf.ELEMENT_ENTRY_ID EE_ID, TO_CHAR(peevf.EFFECTIVE_START_DATE,'YYYY-MM-DD') EE_ESD, TO_CHAR(peevf.EFFECTIVE_END_DATE,'YYYY-MM-DD') EE_EED
from fusion.HRC_INTEGRATION_KEY_MAP hikm, fusion.PAY_ELEMENT_ENTRY_VALUES_F peevf
where hikm.OBJECT_NAME = 'ElementEntryValue'
Use these queries to generate a SourceKey.dat HDL file:
METADATA|SourceKey|FusionGUID|NewSourceSystemOwner|NewSourceSystemId
MERGE|SourceKey|<FusionGUID1>|<SourceSystemOwner>|<SourceSystemId1DONOTUSE>
...
Reload original Element Entry HDL file, referencing updated Source Keys.
Include historical date-effective records to maintain continuity.
Verification Queries
Work Relationship
select papf.PERSON_NUMBER PER_NUM, TO_CHAR(ppos.DATE_START,'YYYY-MM-DD') DATE_START, ppos.PERIOD_TYPE POS_TYPE, houftl.NAME LE_NAME
from fusion.PER_ALL_PEOPLE_F papf, fusion.PER_PERIODS_OF_SERVICE ppos, fusion.HR_ORGANIZATION_UNITS_F_TL houftl
where papf.PERSON_NUMBER = '<PersonNumber>'
Payroll Relationship
select papf.PERSON_NUMBER PER_NUM, TO_CHAR(pprdn.START_DATE,'YYYY-MM-DD') START_DATE, pprdn.PAYROLL_RELATIONSHIP_NUMBER PR_NUMBER, prttl.RELATIONSHIP_TYPE_NAME REL_NAME
from fusion.PAY_PAY_RELATIONSHIPS_DN pprdn, fusion.PAY_RELATIONSHIP_TYPES_TL prttl
where pprdn.PERSON_ID = papf.PERSON_ID
Ensure Work Relationship Period Type matches Payroll Relationship Type Name.
Best Practices
Maintain consistent Source Keys for Element Entry records.
Align Hire/Start Dates between Payroll Relationship and Work Relationship.
Use User Keys wherever possible; surrogate keys only for attributes.
Run diagnostic Worker Data Extracts before HDL reloads.
Test in lower environments before production loads.
No comments:
Post a Comment