SCD Fails for DIM_ORG_UNIT with ORA-00904: "V_ORG_UNIT_CODE": Invalid Identifier (Doc ID 1369051.1)

Last updated on SEPTEMBER 14, 2015

Applies to:

Oracle Financial Services Enterprise Financial Performance Analytics - Version 5.6 to 5.6 [Release 5]
Oracle Financial Services Analytical Applications Infrastructure - Version 7.2.9 and later
Information in this document applies to any platform.
Oracle Financial Service Analytical Applications (OFSAA)

Symptoms

For Oracle Financial Services Profitability Analytics (PFTBI) 5.6, you are trying to run the SCD process to populate the DIM_ORG_UNIT table with values from the Org Unit ID dimension.  The SCD process fails with an ORA-00904 error.  The following is listed in the SCD log file in /<ftpshare>/<infodom>/logs:

Statement for temporary table creation: CREATE TABLE IOT_ORG_UNIT ( V_ORG_UNIT_NAME_LEVEL20, N_ORG_UNIT_ID_LEVEL20, V_ORG_UNIT_NAME_LEVEL19, N_ORG_UNIT_ID_LEVEL19, V_ORG_UNIT_NAME_LEVEL18, N_ORG_UNIT_ID_LEVEL18, V_ORG_UNIT_NAME_LEVEL17, N_ORG_UNIT_ID_LEVEL17, V_ORG_UNIT_NAME_LEVEL16, N_ORG_UNIT_ID_LEVEL16, V_ORG_UNIT_NAME_LEVEL15, N_ORG_UNIT_ID_LEVEL15, V_ORG_UNIT_NAME_LEVEL14, N_ORG_UNIT_ID_LEVEL14, V_ORG_UNIT_NAME_LEVEL13, N_ORG_UNIT_ID_LEVEL13, V_ORG_UNIT_NAME_LEVEL12, N_ORG_UNIT_ID_LEVEL12, V_ORG_UNIT_NAME_LEVEL11, N_ORG_UNIT_ID_LEVEL11, V_ORG_UNIT_NAME_LEVEL10, N_ORG_UNIT_ID_LEVEL10, V_ORG_UNIT_NAME_LEVEL09, N_ORG_UNIT_ID_LEVEL09, V_ORG_UNIT_NAME_LEVEL08, N_ORG_UNIT_ID_LEVEL08, V_ORG_UNIT_NAME_LEVEL07, N_ORG_UNIT_ID_LEVEL07, V_ORG_UNIT_NAME_LEVEL06, N_ORG_UNIT_ID_LEVEL06, V_ORG_UNIT_NAME_LEVEL05, N_ORG_UNIT_ID_LEVEL05, V_ORG_UNIT_NAME_LEVEL04, N_ORG_UNIT_ID_LEVEL04, V_ORG_UNIT_NAME_LEVEL03, N_ORG_UNIT_ID_LEVEL03, V_ORG_UNIT_NAME_LEVEL02, N_ORG_UNIT_ID_LEVEL02, V_ORG_UNIT_NAME_LEVEL01, N_ORG_UNIT_ID_LEVEL01, N_ORG_UNIT_SKEY, V_CREATED_BY, D_CREATED_DATE, V_LAST_MODIFIED_BY, D_LAST_MODIFIED_DATE, N_ORG_UNIT_ID , V_ORG_UNIT_NAME, N_SEQ_01, N_SEQ_20, N_SEQ_03, N_SEQ_04, N_SEQ_05, N_SEQ_06, N_SEQ_07, N_SEQ_08, N_SEQ_09, N_SEQ_10, N_SEQ_11, N_SEQ_12, N_SEQ_13, N_SEQ_14, N_SEQ_15, N_SEQ_16, N_SEQ_17, N_SEQ_18, N_SEQ_19, N_SEQ_02, V_ORG_UNIT_CODE, CONSTRAINT PIT_ORG_UNIT PRIMARY KEY( N_ORG_UNIT_ID )) ORGANIZATION INDEX OVERFLOW AS SELECT V_ORG_UNIT_NAME_LEVEL20, N_ORG_UNIT_ID_LEVEL20, V_ORG_UNIT_NAME_LEVEL19, N_ORG_UNIT_ID_LEVEL19, V_ORG_UNIT_NAME_LEVEL18, N_ORG_UNIT_ID_LEVEL18, V_ORG_UNIT_NAME_LEVEL17, N_ORG_UNIT_ID_LEVEL17, V_ORG_UNIT_NAME_LEVEL16, N_ORG_UNIT_ID_LEVEL16, V_ORG_UNIT_NAME_LEVEL15, N_ORG_UNIT_ID_LEVEL15, V_ORG_UNIT_NAME_LEVEL14, N_ORG_UNIT_ID_LEVEL14, V_ORG_UNIT_NAME_LEVEL13, N_ORG_UNIT_ID_LEVEL13, V_ORG_UNIT_NAME_LEVEL12, N_ORG_UNIT_ID_LEVEL12, V_ORG_UNIT_NAME_LEVEL11, N_ORG_UNIT_ID_LEVEL11, V_ORG_UNIT_NAME_LEVEL10, N_ORG_UNIT_ID_LEVEL10, V_ORG_UNIT_NAME_LEVEL09, N_ORG_UNIT_ID_LEVEL09, V_ORG_UNIT_NAME_LEVEL08, N_ORG_UNIT_ID_LEVEL08, V_ORG_UNIT_NAME_LEVEL07, N_ORG_UNIT_ID_LEVEL07, V_ORG_UNIT_NAME_LEVEL06, N_ORG_UNIT_ID_LEVEL06, V_ORG_UNIT_NAME_LEVEL05, N_ORG_UNIT_ID_LEVEL05, V_ORG_UNIT_NAME_LEVEL04, N_ORG_UNIT_ID_LEVEL04, V_ORG_UNIT_NAME_LEVEL03, N_ORG_UNIT_ID_LEVEL03, V_ORG_UNIT_NAME_LEVEL02, N_ORG_UNIT_ID_LEVEL02, V_ORG_UNIT_NAME_LEVEL01, N_ORG_UNIT_ID_LEVEL01, N_ORG_UNIT_SKEY, V_CREATED_BY, D_CREATED_DATE, V_LAST_MODIFIED_BY, D_LAST_MODIFIED_DATE, N_ORG_UNIT_ID , V_ORG_UNIT_NAME, N_SEQ_01, N_SEQ_20, N_SEQ_03, N_SEQ_04, N_SEQ_05, N_SEQ_06, N_SEQ_07, N_SEQ_08, N_SEQ_09, N_SEQ_10, N_SEQ_11, N_SEQ_12, N_SEQ_13, N_SEQ_14, N_SEQ_15, N_SEQ_16, N_SEQ_17, N_SEQ_18, N_SEQ_19, N_SEQ_02, V_ORG_UNIT_CODE FROM DIM_ORG_UNIT Where DIM_ORG_UNIT.F_LATEST_RECORD_INDICATOR = 'Y'
Error Msg :-> ORA-00904: "V_ORG_UNIT_CODE": invalid identifier

The V_ORG_UNIT_CODE column does not exist in the DIM_ORG_UNIT table.

The error prevents Org Unit ID data from being inserted into DIM_ORG_UNIT.

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