My Oracle Support Banner

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 and later
Information in this document applies to any platform.


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:

Interface name:

Missing filter:


To view full details, 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 a vibrant support community of peers and Oracle experts.