My Oracle Support Banner

PFT 805 Installation ORA-00904: "F_INT_EXT_FLAG" Error from DIM_CAUSES Table (Doc ID 2378083.1)

Last updated on SEPTEMBER 17, 2019

Applies to:

Oracle Financial Services Profitability Management - Version 8.0.5 to 8.0.6 [Release 8]
Oracle Financial Services Enterprise Financial Performance Analytics - Version 8.0.5 to 8.0.6 [Release 8]
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications (OFSAA)
Oracle Financial Services Enterprise Performance Management (EPM)
Oracle Financial Services Profitability Management (PFT)
Oracle Business Intelligence Enterprise Edition (OBIEE)
Oracle Financial Services Enterprise Financial Performance Analytics (EFPA/PFTBI) previously known as Oracle Financial Services Profitability Analytics
Reporting

Symptoms

On PFT 8.0.5, there is an error on installation with missing DIM_CAUSES.F_INT_EXT_FLAG. From the OFS_PFT_installation.log:

ERROR
[Thu 08/3/2018 12:55:05] [OFS_PFT] [ INFO] - Executing file:/ofsaa/OFSAAI8/scripts/atomic/insert/fcas_fts_fccs_dimensions_default.sql
[Thu 08/3/2018 12:55:10] [OFS_PFT] [ERROR] - Query:'--DIM_CAUSES
DECLARE
 flag NUMBER(5);
v_sql CLOB;

BEGIN
 select count(1)
  into flag
  from user_tables
  where table_name = 'DIM_CAUSES';
 
v_sql := q'[
MERGE INTO DIM_CAUSES T
USING (
SELECT '-1' N_CAUSE_SKEY,'-1' N_CAUSE_CODE,'Others' V_CAUSE_NAME,'Others' V_CAUSE_DESC,'' V_MAKER_ID,'' V_CHECKER_ID,'' V_MAKER_REMARKS,'' V_CHECKER_REMARKS,'Y' F_LATEST_RECORD_INDICATOR,'' F_AUTHFLAG,to_date('','dd-mm-yyyy') D_MAKER_DATE,to_date('','dd-mm-yyyy') D_CHECKER_DATE,to_date('01-01-1900','dd-mm-yyyy') D_RECORD_START_DATE,to_date('31-12-9999','dd-mm-yyyy') D_RECORD_END_DATE,to_date('01-01-1900','dd-mm-yyyy') FIC_MIS_DATE,'' N_PARENT_KEY,'' V_PARENT_CODE,'OTH' V_CAUSE_CODE,'' F_INT_EXT_FLAG,'' V_DATA_SOURCE_CODE FROM DUAL
UNION
SELECT '0' N_CAUSE_SKEY,'0' N_CAUSE_CODE,'Missing' V_CAUSE_NAME,'Missing' V_CAUSE_DESC,'' V_MAKER_ID,'' V_CHECKER_ID,'' V_MAKER_REMARKS,'' V_CHECKER_REMARKS,'Y' F_LATEST_RECORD_INDICATOR,'' F_AUTHFLAG,to_date('','dd-mm-yyyy') D_MAKER_DATE,to_date('','dd-mm-yyyy') D_CHECKER_DATE,to_date('01-01-1900','dd-mm-yyyy') D_RECORD_START_DATE,to_date('31-12-9999','dd-mm-yyyy') D_RECORD_END_DATE,to_date('01-01-1900','dd-mm-yyyy') FIC_MIS_DATE,'' N_PARENT_KEY,'' V_PARENT_CODE,'MSG' V_CAUSE_CODE,'' F_INT_EXT_FLAG,'' V_DATA_SOURCE_CODE FROM DUAL
) S
ON (T.N_CAUSE_SKEY = S.N_CAUSE_SKEY)
WHEN MATCHED THEN
UPDATE
SET T.N_CAUSE_CODE = S.N_CAUSE_CODE,
  T.V_CAUSE_NAME = S.V_CAUSE_NAME,
  T.V_CAUSE_DESC = S.V_CAUSE_DESC,
  T.V_MAKER_ID = S.V_MAKER_ID,
  T.V_CHECKER_ID = S.V_CHECKER_ID,
  T.V_MAKER_REMARKS = S.V_MAKER_REMARKS,
  T.V_CHECKER_REMARKS = S.V_CHECKER_REMARKS,
  T.F_LATEST_RECORD_INDICATOR = S.F_LATEST_RECORD_INDICATOR,
  T.F_AUTHFLAG = S.F_AUTHFLAG,
  T.D_MAKER_DATE = S.D_MAKER_DATE,
  T.D_CHECKER_DATE = S.D_CHECKER_DATE,
  T.D_RECORD_START_DATE = S.D_RECORD_START_DATE,
  T.D_RECORD_END_DATE = S.D_RECORD_END_DATE,
  T.FIC_MIS_DATE = S.FIC_MIS_DATE,
  T.N_PARENT_KEY = S.N_PARENT_KEY,
  T.V_PARENT_CODE = S.V_PARENT_CODE,
  T.V_CAUSE_CODE = S.V_CAUSE_CODE,
  T.F_INT_EXT_FLAG = S.F_INT_EXT_FLAG,
  T.V_DATA_SOURCE_CODE = S.V_DATA_SOURCE_CODE
WHEN NOT MATCHED THEN
INSERT
 (N_CAUSE_SKEY,N_CAUSE_CODE,V_CAUSE_NAME,V_CAUSE_DESC,V_MAKER_ID,V_CHECKER_ID,V_MAKER_REMARKS,V_CHECKER_REMARKS,F_LATEST_RECORD_INDICATOR,F_AUTHFLAG,D_MAKER_DATE,D_CHECKER_DATE,D_RECORD_START_DATE,D_RECORD_END_DATE,FIC_MIS_DATE,N_PARENT_KEY,V_PARENT_CODE,V_CAUSE_CODE,F_INT_EXT_FLAG,V_DATA_SOURCE_CODE)
VALUES
 (S.N_CAUSE_SKEY,S.N_CAUSE_CODE,S.V_CAUSE_NAME,S.V_CAUSE_DESC,S.V_MAKER_ID,S.V_CHECKER_ID,S.V_MAKER_REMARKS,S.V_CHECKER_REMARKS,S.F_LATEST_RECORD_INDICATOR,S.F_AUTHFLAG,S.D_MAKER_DATE,S.D_CHECKER_DATE,S.D_RECORD_START_DATE,S.D_RECORD_END_DATE,S.FIC_MIS_DATE,S.N_PARENT_KEY,S.V_PARENT_CODE,S.V_CAUSE_CODE,S.F_INT_EXT_FLAG,S.V_DATA_SOURCE_CODE )
]';

 IF flag = 1 THEN
execute immediate(v_sql);
 commit;
END IF;
END;'
 Error:ORA-00904: "F_INT_EXT_FLAG": invalid identifier
ORA-06512: at line 49

[Thu 08/3/2018 12:55:10] [OFS_PFT] [ INFO] - # of errors in file=1


The issue can be reproduced at will with the following steps:
1. Run PFT 8.0.5 installer

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.