Incorrect SQL Statements in Dm_Common_Bi_Ins.Sql For PFTBI 5.6 Cause ORA-00900 and ORA-00904 Errors (Doc ID 1382165.1)

Last updated on NOVEMBER 30, 2011

Applies to:

Oracle Financial Services Profitability Analytics - Version: 5.6 and later   [Release: 5 and later ]
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications Infrastructure (OFSAAI) - Version 7.2
Oracle Financial Services Analytical Applications (OFSAA) - Version 5.x
PFT BI

Symptoms

On Oracle Financial Services Profitability Analytics (PFTBI) 5.6, when attempting to run the PFTBI 5.6 installation, the following error occurs in the log file:

ERROR
[ 28/10/2011 12:12:17 ] ===>Executing
file:/refresh/64bit/app/ofsaa/scripts/atomic/insert/dm_common_bi_ins.sql
[ 28/10/2011 12:12:19 ] Query:'INSERT INTO DIM_RUN (N_RUN_SKEY, V_RUN_ID,
FIC_MIS_DATE, D_RECORD_START_DATE, D_RECORD_END_DATE,
F_LATEST_RECORD_INDICATOR, V_RUN_EXECUTION_ID, N_VERSION_NUMBER, V_UNIQUE_ID,
V_EXECUTION_DESCRIPTION, V_VERSION_DESCRIPTION, V_RUN_STATUS, V_RUN_MAIN_ID,
V_RUN_MAIN_DESC, V_RUN_DESC, RUN_TYPE_SURROGATE_KEY, V_PROD_CODE,
F_ICAAP_RPT_FLAG, F_HISTORICAL, V_PRODUCT, N_SCENARIO_NUM, N_PROCESS_ID,
N_REPORTING_RUN_SKEY, N_RUN_TYPE_SKEY) values (0, '0', to_date(sysdate,
'dd-mm-yyyy'), to_date(sysdate, 'dd-mm-yyyy'), to_date('31-12-9999',
'dd-mm-yyyy'), 'Y', null, 1, '0', null, null, null, null, null,'Default Run',
null, null, null, null, null, null, null, null, null)'
Error:ORA-00904: "RUN_TYPE_SURROGATE_KEY": invalid identifier

[ 28/10/2011 12:12:19 ] Query:'isert into sys_stg_join_master
(MAP_REF_NUM, COL_NM, COL_TYP, STG_COL_NM, SCD_TYP_ID, PRTY_LOOKUP_REQD_FLG,
COL_DATATYPE, COL_FORMAT)values (5, 'V_GL_ACCOUNT_CODE', 'DA',
'V_GL_ACCOUNT_CODE', 2, 'N', 'VARCHAR', '')'
Error:ORA-00900: invalid SQL statement

STEPS
The issue can be reproduced at will with the following steps:
1. Apply PFTBI 5.6 patch
2. Check install logs for errors
3. Check the dm_common_bi_ins.sql included in PFT 5.6 patch

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