OBIA 11g: SDE_ORA_ARTransactionFact_ARSchedule_Derive_InitialBalance Fails With ORA-01841 (Doc ID 1591737.1)

Last updated on JANUARY 30, 2017

Applies to:

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

Symptoms

SDE_ORA_ARTransactionFact_ARSchedule_Derive_InitialBalance fails with ORA-01841.

Error message:

ODI-1228: Task SDE_ORA_ARTransactionFact_ARSchedule_Derive_InitialBalance.W_AR_XACT_FS (Integration) fails on the target ORACLE connection BIAPPS_DW.
Caused By: java.sql.SQLDataException: ORA-01841: (full) year must be between -4713 and +9999, and not be 0.

There is a bug in the query, see attached files.
Problem is in 2 places (in select and in group by):

CASE
      WHEN W_ORA_AR_XACTS_F_TMP.GL_DATE IS NULL
      THEN LAST_DAY(ADD_MONTHS(TO_DATE(SUBSTR(W_ORA_AR_XACTS_F_TMP.TRX_DATE,0,19),'YYYY-MM-DD HH24:MI:SS'), -2))
      ELSE LAST_DAY(ADD_MONTHS(TO_DATE(SUBSTR('W_ORA_AR_XACTS_F_TMP.GL_DATE',0,19),'YYYY-MM-DD HH24:MI:SS'),-2))
    END,

The single quotes around W_ORA_AR_XACTS_F_TMP.GL_DATE are erroneous, removing these wibll fix the query.

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