OBIA : Absence Tables Are Not Working As Expected After Enabling Soft Delete
Last updated on OCTOBER 23, 2017
Applies to:Business Intelligence Applications Consumer - Version 22.214.171.124.1 and later
Information in this document applies to any platform.
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
JOB_PS.DELETE_FLG = 'N'
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