OBIA : Absence Tables Are Not Working As Expected After Enabling Soft Delete

(Doc ID 2202225.1)

Last updated on OCTOBER 23, 2017

Applies to:

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

Symptoms

Module: HR
Data Source: PSFT9_2

If a person updates his absence begin date and also updates the job event date (the absence date and the job event date is similar now), the absence event dimension throws an error :
CausedBy: java.sql.SQLIntegrityConstraint VoilationException: ORA-00001: unique constraint (DEV_DW.W_ABSENCE_EVENT_D_U1) violated

There are two records for that person for that absence date in W_ABSENCE_EVENT_D. The main issue is with the final interface folder for this table which doesn't have a filter : DELETE_FLG = 'N' so as to eliminate the deleted record.
And due to this, when the absence tmp table joins with PS JOB event table(that stores history of all the records),it returns multiple records.

Faulty join condition:
W_ABSENCE_EVENT_TMP.ABSENCE_BEGIN_DT >= JOB_PS.EFFECTIVE_FROM_DT AND
W_ABSENCE_EVENT_TMP.ABSENCE_BEGIN_DT < JOB_PS.EFFECTIVE_TO_DT

Interface name:
SDE_PSFT_AbsenceEventDimension_FinalLoad.W_ABSENCE_EVENT_DS_SQ_W_ABSENCE_EVENT_DS

Missing filter:
JOB_PS.DELETE_FLG = 'N'

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