ALMBI Transformation Failing After Upgrade From ALMBI 5.2.2 to ALMBI 5.6 (Doc ID 1476333.1)

Last updated on JULY 18, 2012

Applies to:

Oracle Financial Services Asset Liability Management Analytics - Version 5.6 and later
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications (OFSAA) - Version 5.x
ALM BI
ALM_BI_TRANSFORMATION
PKG_ALM_BI_TRANSFORMATIONS

Symptoms

On Oracle Financial Services Asset Liability Management Analytics (ALMBI) 5.6 after upgrading from ALMBI 5.2.2, when attempting to run the ALMBI Transformation the following error occurs:

ERROR

FSI_MESSAGE_LOG:

PROCESS_ID SEQUENCES MSG_TIMESTAMP MESSAGE_CD MSG_SEVERITY_CD BATCH_RUN_ID CONTEXT_SPECIFIC_TEXT
200699 2 7/10/2012 1:04:26 PM 1465 20 OFSARBSO_TRANSFORMALMRESULT_20120501_15 Fact Table Data Deleted . Rerun Flag is Enabled
200699 3 7/10/2012 1:04:26 PM 1019 20 OFSARBSO_TRANSFORMALMRESULT_20120501_15 Buckets Not Found for 200699 in DIM_RESULT_BUCKET
200699 4 7/10/2012 1:04:26 PM 44309 20 OFSARBSO_TRANSFORMALMRESULT_20120501_15 One of the Transformation failed: Program Terminated
200699 1 7/10/2012 1:04:25 PM 1465 20 OFSARBSO_TRANSFORMALMRESULT_20120501_15 Deterministic Process Initiated

When attempting to rerun the SCD for the DIM_RESULT_BUCKET to resolve the above error, the following error occurs:

ERROR

Statement for temporary table creation: CREATE TABLE IOT_RESULT_BUCKET ( D_BUCKET_END_DATE, D_BUCKET_START_DATE, D_CREATED_DATE, D_LAST_MODIFIED_DATE, D_PARENT_START_DATE, F_BUCKET_TERM_FREQ_MULT, N_BUCKET_END_DAYS, N_BUCKET_NO , N_BUCKET_NUMBER_CATEGORY, N_BUCKET_NUMBER_SUB_CATEGORY, N_BUCKET_START_DAYS, N_BUCKET_TERM_FREQ, N_RESULT_BUCKET_SKEY, N_START_DATE_INDEX , N_TIME_BUCKET_SYS_ID , V_BUCKET_NAME, V_BUCKET_NAME_CATEGORY, V_BUCKET_NAME_SUB_CATEGORY, V_BUCKET_TYPE , V_LAST_MODIFIED_BY, CONSTRAINT PIT_RESULT_BUCKET PRIMARY KEY( N_BUCKET_NO , N_START_DATE_INDEX , N_TIME_BUCKET_SYS_ID , V_BUCKET_TYPE )) ORGANIZATION INDEX OVERFLOW AS SELECT D_BUCKET_END_DATE, D_BUCKET_START_DATE, D_CREATED_DATE, D_LAST_MODIFIED_DATE, D_PARENT_START_DATE, F_BUCKET_TERM_FREQ_MULT, N_BUCKET_END_DAYS, N_BUCKET_NO , N_BUCKET_NUMBER_CATEGORY, N_BUCKET_NUMBER_SUB_CATEGORY, N_BUCKET_START_DAYS, N_BUCKET_TERM_FREQ, N_RESULT_BUCKET_SKEY, N_START_DATE_INDEX , N_TIME_BUCKET_SYS_ID , V_BUCKET_NAME, V_BUCKET_NAME_CATEGORY, V_BUCKET_NAME_SUB_CATEGORY, V_BUCKET_TYPE , V_LAST_MODIFIED_BY FROM DIM_RESULT_BUCKET Where DIM_RESULT_BUCKET.F_LATEST_RECORD_INDICATOR = 'Y'
Error Msg :-> ORA-01400: cannot insert NULL into ("OFSATM"."IOT_RESULT_BUCKET"."N_TIME_BUCKET_SYS_ID")

ClsUDBOperations::executeQueryWithCommit, Error, unable to execute the queryCREATE TABLE IOT_RESULT_BUCKET ( D_BUCKET_END_DATE, D_BUCKET_START_DATE, D_CREATED_DATE, D_LAST_MODIFIED_DATE, D_PARENT_START_DATE, F_BUCKET_TERM_FREQ_MULT, N_BUCKET_END_DAYS, N_BUCKET_NO , N_BUCKET_NUMBER_CATEGORY, N_BUCKET_NUMBER_SUB_CATEGORY, N_BUCKET_START_DAYS, N_BUCKET_TERM_FREQ, N_RESULT_BUCKET_SKEY, N_START_DATE_INDEX , N_TIME_BUCKET_SYS_ID , V_BUCKET_NAME, V_BUCKET_NAME_CATEGORY, V_BUCKET_NAME_SUB_CATEGORY, V_BUCKET_TYPE , V_LAST_MODIFIED_BY, CONSTRAINT PIT_RESULT_BUCKET PRIMARY KEY( N_BUCKET_NO , N_START_DATE_INDEX , N_TIME_BUCKET_SYS_ID , V_BUCKET_TYPE )) ORGANIZATION INDEX OVERFLOW AS SELECT D_BUCKET_END_DATE, D_BUCKET_START_DATE, D_CREATED_DATE, D_LAST_MODIFIED_DATE, D_PARENT_START_DATE, F_BUCKET_TERM_FREQ_MULT, N_BUCKET_END_DAYS, N_BUCKET_NO , N_BUCKET_NUMBER_CATEGORY, N_BUCKET_NUMBER_SUB_CATEGORY, N_BUCKET_START_DAYS, N_BUCKET_TERM_FREQ, N_RESULT_BUCKET_SKEY, N_START_DATE_INDEX , N_TIME_BUCKET_SYS_ID , V_BUCKET_NAME, V_BUCKET_NAME_CATEGORY, V_BUCKET_NAME_SUB_CATEGORY, V_BUCKET_TYPE , V_LAST_MODIFIED_BY FROM DIM_RESULT_BUCKET Where DIM_RESULT_BUCKET.F_LATEST_RECORD_INDICATOR = 'Y'
ERROR Message:

SCD successful

-----------------------------------------------------------------------------------------
  End of SCD Operations : Thu Jul 12 16:14:48 2012
-----------------------------------------------------------------------------------------

Steps to reproduce:

The issue can be reproduced at will with the following steps:
1. Run the ALMBI Transformation
2. Run the SCD for DIM_RESULT_BUCKET

Changes

 Upgrade to ALMBI 5.6 from ALMBI 5.2.2.

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