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

Symptoms

OBIA 11.1.1.10.2

Problem

=========
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.

ACTUAL BEHAVIOR
----------------------------
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.

Application/Environment:
- OBIEE version: 11.1.1.9.0
- BIAPPS Version: 11.1.1.10.2
- Module: HR Analytics
- Source: Fusion R13
- Database Warehouse: Oracle 12.1.0.4
- ODI version: 11.1.1.9.0

Changes

 

Cause

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
Symptoms
Changes
Cause
Solution
References


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