Incremental Load Error- Error Creating Index/Constraint : W_HR_PERSON_LEG_DS_U1 (Doc ID 2109073.1)

Last updated on MARCH 29, 2017

Applies to:

Business Intelligence Applications Consumer - Version 11.1.1.7.1 and later
Information in this document applies to any platform.

Symptoms

Incremental data load is getting failed with Error creating Index / Constraint : W_HR_PERSON_LEG_DS_U1 .
Implemented OOTB HR analytics with ODI, OBIEE and BIAPPS of 11.1.1.7.1+ versions.

Additional details

==============

1. Implemented soft delete scenario and did a full load. Full load went fine. We did incremental load which is causing the issue.
2. Checked the source table, PS_NAMES and we could see only one record for this employee 15478007, which was updated on 29-JAN-2016.
3. But the table W_PSFT_HR_PSN_NAMES_PS is having old record as well as new record with old record DELETE_FLG=Y. This is as expected.
4. The tables W_PSFT_HR_PSN_LEG_DATE_TMP XDATES and W_PSFT_HR_PSN_NAMES_PS are used to populated W_HR_PERSON_LEG_DS which are having 2 records each and due to join conditions and selecting records from both the tables, ultimate
row count is 3 for this employee, there by causing the issue.

The following notes did not help.
.
OBIA 11g - HR Analytics Recruitment Load Plan with PeopleSoft Fails With
Error "W_HR_PERSON_LEG_DS_U1 => ORA-01452: cannot CREATE UNIQUE INDEX" (Doc
ID 1960973.1)
.
OBIA 11g 'SDE_PSFT_HRPersonLegDimension_Applicants' Mapping Fails With The
'ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found' (Doc ID
1996283.1)
.
.
Recruitment: W_HR_PERSON_LEG_D_N1 Unique Constraint Error (Doc ID 1996282.1)

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