SCD Process for ALMBI or PFTBI Has ORA-00942 and/or ORA-01400 in Log File (Doc ID 1303381.1)

Last updated on FEBRUARY 23, 2015

Applies to:

Oracle Financial Services Asset Liability Management Analytics - Version 5.2 and later
Oracle Financial Services Profitability Analytics - Version 5.2 and later
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications (OFSAA)

Symptoms

For Oracle Financial Services Asset Liability Management Analytics (ALMBI) or Profitability Analytics (PFTBI), when you run the SCD executable, you receive ORA-01400 errors like the following in the SCD log located in the /<FTP directory>/<infodom>/logs directory:

Statement for dimension delete updates: update DIM_PROCESS set DIM_PROCESS.D_END_DATE = to_date('30-SEP-2010 00:00:00') -1 , DIM_PROCESS.F_LATEST_RECORD_INDICATOR = 'N' where ( N_PROCESS_ID ) in ( select N_PROCESS_ID from DIM_PROCESS where N_PROCESS_SKEY not in (0, -1) and DIM_PROCESS.F_LATEST_RECORD_INDICATOR = 'Y' minus select FSI_M_ALM_PROCESS_V.N_PROCESS_ID from FSI_M_ALM_PROCESS_V) and N_PROCESS_SKEY not in (0, -1) and DIM_PROCESS.F_LATEST_RECORD_INDICATOR = 'Y'

New records inserted ...0
Type II records inserted ...0
Type I updates...0
Dimension delete updates ...0
End of Cursor
OCI_NO_DATA
Error Msg :->
Error Msg :-> ORA-01400: cannot insert NULL into ("ATOMIC"."DIM_PROCESS"."V_PROCESS_NAME")

ClsUDBOperations::executeInsertQuery, Error, unable to execute the insert query: Insert into DIM_PROCESS (N_PROCESS_SKEY, N_PROCESS_ID, D_START_DATE, D_END_DATE, F_LATEST_RECORD_INDICATOR) values (0, 0, to_date('20100930', 'YYYYMMDD'), to_date('99991231', 'YYYYMMDD'), 'Y')
ERROR Message: ORA-01400: cannot insert NULL into ("ATOMIC"."DIM_PROCESS"."V_PROCESS_NAME")

Error Msg :-> ORA-01400: cannot insert NULL into ("ATOMIC"."DIM_PROCESS"."V_PROCESS_NAME")

ClsUDBOperations::executeInsertQuery, Error, unable to execute the insert query: Insert into DIM_PROCESS (N_PROCESS_SKEY, N_PROCESS_ID, D_START_DATE, D_END_DATE, F_LATEST_RECORD_INDICATOR) values (-1, -1, to_date('20100930', 'YYYYMMDD'), to_date('99991231', 'YYYYMMDD'), 'Y')
ERROR Message: ORA-01400: cannot insert NULL into ("ATOMIC"."DIM_PROCESS"."V_PROCESS_NAME")

For certain SCD processes, you may also see 'ORA-00942: table or view does not exist' errors like the following:

Processing Mapping Reference Number: 4
Table Master Query: SELECT tbl_nm, stg_tbl_nm, src_key, src_prty, dt_offset FROM sys_tbl_master WHERE map_ref_num = 4
End of Cursor
OCI_NO_DATA
Error Msg :->
Start Date Query:  Begin :selectQuery:= dt_buss_day_calc ( '20120707', 0.000000,  'B'  );end;
start date: 07-JUL-2012 00:00:00
End of Cursor
OCI_NO_DATA
Error Msg :->
Error Msg :-> ORA-00942: table or view does not exist

However, even with the error messages, the DIM_PROCESS table is populated by the SCD program.

Note: The ORA-01400 error may reference several different columns on the DIM_<dimension> tables.


Steps to Reproduce:

  1. Go to Operations > Batch Maintenance
  2. Create a new Batch
  3. Add a new Task to the batch with the following:

Component ID: RUN EXECUTABLE
Executable: scd,-1
Wait: N
Batch Parameter: Y

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