ORA-00904 Errors on FCT_LEDGER_STAT and FSI_BI_SETUP_TABLE Running ALMBI 6.0 Upgrade from 5.6 (Doc ID 1498251.1)

Last updated on OCTOBER 12, 2012

Applies to:

Oracle Financial Services Asset Liability Management Analytics - Version 6.0 and later
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications Infrastructure (OFSAAI) - Version 7.3
Oracle Financial Services Analytical Applications (OFSAA) - Version 6.0
ALM BI

Symptoms

When running the Oracle Financial Services Asset Liability Management Analytics (ALMBI) 6.0 installer to upgrade ALMBI 5.6 to ALMBI 5.6, the following error occurs:

ERROR
[Tue 02/10/2012 08:11:16] [OFSALM] [DEBUG] - ===>Executing file:/u01/OFSDV1/ofsaa/scripts/atomic/insert/dm_common_bi_ins_60.sql
[Tue 02/10/2012 08:11:18] [OFSALM] [ERROR] - Query:'insert into fsi_bi_setup_table (TARGET_TABLE_NAME, TARGET_COL_NAME, MEMBER_COL_NAME, SOURCE_DIM_TABLE_NAME, SOURCE_COLUMN_NAME, SKEY_COLUMN_NAME, JOIN_REQUIRED, GROUP_BY_REQUIRED)values ('FCT_LEDGER_STAT', 'N_ENTITY_SKEY', 'N_ENTITY_SKEY', 'DIM_ORG_STRUCTURE', '-1', '-1', 'N', NULL)'
 Error:ORA-00904: "GROUP_BY_REQUIRED": invalid identifier

[Tue 02/10/2012 08:11:18] [OFSALM] [ERROR] - Query:'insert into fsi_bi_setup_table (TARGET_TABLE_NAME, TARGET_COL_NAME, MEMBER_COL_NAME, SOURCE_DIM_TABLE_NAME, SOURCE_COLUMN_NAME, SKEY_COLUMN_NAME, JOIN_REQUIRED, GROUP_BY_REQUIRED)values ('FCT_LEDGER_STAT', 'N_LOB_SKEY', 'N_LOB_SKEY', 'DIM_LOB', '-1', '-1', 'N', NULL)'
 Error:ORA-00904: "GROUP_BY_REQUIRED": invalid identifier

[Tue 02/10/2012 08:11:18] [OFSALM] [DEBUG] - ===># of errors in file=2

....

[Tue 02/10/2012 08:11:19] [OFSALM] [DEBUG] - ===>Number of lines Read: 1
[Tue 02/10/2012 08:11:19] [OFSALM] [DEBUG] - ===>Executing file:/u01/OFSDV1/ofsaa/scripts/atomic/insert/restore_fct_ledger_stat.sql
[Tue 02/10/2012 08:11:19] [OFSALM] [ERROR] - Query:'insert into fct_ledger_stat
(n_identity_code,
n_as_of_date_skey,
n_org_unit_skey,
n_gl_account_skey,
n_common_coa_skey,
v_iso_currency_cd,
n_consolidation_cd,
n_balance_type_cd,
n_financial_elem_id,
n_value,
n_value_rcy,
n_value_ytd,
n_value_rcy_ytd,
v_accumulation_type_cd,
n_prod_skey,
n_lob_skey,
n_entity_skey)
select n_identity_code,
n_as_of_date_skey,
n_org_unit_skey,
n_gl_account_skey,
n_common_coa_skey,
v_iso_currency_cd,
n_consolidation_cd,
n_balance_type_cd,
n_financial_elem_id,
n_value,
n_value_rcy,
n_value_ytd,
n_value_rcy_ytd,
v_accumulation_type_cd,
n_prod_skey,
-1,
-1
from fct_ledger_stat_


STEPS
The issue can be reproduced at will with the following steps:

1. Complete model merge for FSDW 7.3, ALMBI 6.0 and ALM 6.0 with 5.6 data model
2. Upload merged data model
3. Run ALMBI 6.0 installer

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