My Oracle Support Banner

OBIA 11g: Accrual Transaction Fact Fails With Unique Constraint Violated (Doc ID 2540829.1)

Last updated on AUGUST 16, 2019

Applies to:

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




Intermittent. No customization made on Accruals. Since the time running incremental loads, occurred twice in 7 incremental loads. The mapping errors out due to the unique constraint and the mapping is again re-run performing the auto-corrective steps where it deletes the duplicates in I$ table and proceeds with ETL, hence the load completes. But whenever it runs with auto-corrective steps, it takes huge amount of time. If error is eliminated this error from occurring, this issue won't occur. So far, in 7 incremental loads it went to auto correction twice and succeeded. We don't want this to happen in future and hence looking for solution.

OBIA Accrual Transaction SIL step failed due to unique constraint error: BIAPPS_DW.
Caused By: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (<OBIA_DW_SCHEMA>.W_ACCRUAL_XACT_F_U1) violated

Upon investigation, found there are duplicates in W_ACCRUAL_XACT_FS table, but couldn't find the reason on how duplicates are coming.

This integration id: SELECT * FROM W_ACCRUAL_XACT_FS where INTEGRATION_ID='<specific numbers>';
Returns 2 records and STD_HOURS_DAY is same for two records.

Regarding soft deletes, able to see PE mappings running but couldn't find any record in F and FS with DELETE_FLG='Y'.

Another observation: when executing this procedure to update:
update W_FSN_WEVT_ASG_PS set STD_HOURS_DAY='36.75' WHERE std_hours_day = 0 or std_hours_day is null

Also checked:
select std_hours_day,EFFECTIVE_START_DATE, effective_end_date from W_FSN_WEVT_ASG_PS where assignment_id='<specific number>';
there are 7 records with same STD_HOURS_DAY, Attached dataset.

- OBIEE version:
- BIAPPS Version:
- Module: HR Analytics
- Source: Fusion R13
- Database Warehouse: Oracle
- ODI version:




To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.