OBIA 11g: SDE_ORA_ARTransactionFact_ARSchedule_Derive_InitialBalance Fails With ORA-01841
Last updated on JANUARY 30, 2017
Applies to:Business Intelligence Applications Consumer - Version 18.104.22.168.0 and later
Information in this document applies to any platform.
SDE_ORA_ARTransactionFact_ARSchedule_Derive_InitialBalance fails with ORA-01841.
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):
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))
The single quotes around W_ORA_AR_XACTS_F_TMP.GL_DATE are erroneous, removing these wibll fix the query.
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