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:

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

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