ORA-01722, ORA-01407, ORA-01422 Errors Applying PFT 8.0.4 (Doc ID 2272913.1)

Last updated on JUNE 01, 2017

Applies to:

Oracle Financial Services Profitability Management - Version 8.0.4 and later
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications (OFSAA)
Oracle Financial Services Profitability Management (PFT)
Oracle Financial Services Enterprise Financial Performance Analytics (EFPA/PFTBI) previously known as Oracle Financial Services Profitability Analytics
Installation and Upgrade Patch

Symptoms

When running the PFT 8.0.4 application pack installer there are several errors which appear in the log file.

ERROR

[Mon 27/3/2017 06:15:23] [OFS_PFT] [ INFO] - **************** WELCOME TO OFS_PFT ~~ 8.0.4.0.0 PATCH **************
[Mon 27/3/2017 06:15:25] [OFS_PFT] [ INFO] - Installed infodom --- [OFSAA]
[Mon 27/3/2017 06:15:25] [OFS_PFT] [ INFO] - Constraints validation started
[Mon 27/3/2017 06:15:25] [OFS_PFT] [ INFO] - Initialized successfully
[Mon 27/3/2017 06:15:25] [OFS_PFT] [ INFO] - Checking Constraints validation for infodoms ->[CPOFSORSND, CPOFSRPSND, CPOFSCCCF, CPOFSCNON, CPOFSCSEC, OFSAA]
[Mon 27/3/2017 06:15:25] [OFS_PFT] [ INFO] - Retrieving FTPSHARE path from configuration schema
[Mon 27/3/2017 06:15:25] [OFS_PFT] [ INFO] - FTPSHARE path in appLayer : /amex/apps/ofsaa/ftpshare//
[Mon 27/3/2017 06:15:25] [OFS_PFT] [FATAL] - =====Exception====
java.sql.SQLSyntaxErrorException: ORA-01722: invalid number

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
...
[Mon 27/3/2017 06:23:07] [OFS_PFT] [ INFO] - Executing file:/amex/apps/ofsaa/scripts/config/insert/pr2_run_b_801_ipa.sql
[Mon 27/3/2017 06:23:07] [OFS_PFT] [ERROR] - Query:'MERGE INTO PR2_RUN_B T
USING (
SELECT '1431854963437' V_RUN_ID,'OFSAA' V_INFODOM_NAME,'Corporate_Tracker_Seg_Run_IPA' V_RUN_NAME,'0' N_RUN_VERSION,'BR' V_RUN_TYPE_CODE,TO_DATE(TO_CHAR(SYSDATE,'DD/MM/YYYY'),'DD/MM/YYYY') d_effective_start_date,to_date('31-12-2100','dd-mm-yyyy') D_EFFECTIVE_END_DATE,'Y' F_ACTIVE_FLAG,'' V_FOLDER_NAME,'A' V_LAST_OPERATION_TYPE_CODE,'' V_SEEDED_BY,'SYSADMN' V_CREATED_BY,CURRENT_TIMESTAMP D_CREATED_DATE,'SYSADMN' V_LAST_MODIFIED_BY,CURRENT_TIMESTAMP D_LAST_MODIFIED_DATE,'SYSADMN' V_LAST_AUTHORIZED_BY,CURRENT_TIMESTAMP D_LAST_AUTHORIZED_DATE,'en_US' SOURCE_LANG FROM DUAL
) S
ON (T.V_RUN_ID = S.V_RUN_ID AND T.V_INFODOM_NAME = S.V_INFODOM_NAME)
WHEN MATCHED THEN
UPDATE
SET T.V_RUN_NAME = S.V_RUN_NAME,
  T.N_RUN_VERSION = S.N_RUN_VERSION,
...
VALUES
 (S.V_PROCESS_ID,S.V_INFODOM_NAME,S.N_TASK_ID,S.N_PARENT_TASK_ID,S.V_TASK_REF_UNIQUE_NAME,S.V_TASK_TYPE_CODE,S.V_TASK_SUB_TYPE_CODE,S.V_TASK_REF_1_NAME,S.V_TASK_REF_1_VALUE,S.V_TASK_REF_2_NAME,S.V_TASK_REF_2_VALUE,S.V_TASK_REF_3_NAME,S.V_TASK_REF_3_VALUE,S.V_TASK_REF_4_NAME,S.V_TASK_REF_4_VALUE,S.N_TASK_ORDER )'
 Error:ORA-02291: integrity constraint (OFSCONFIG.FK_PR2_PROCESS_TASK_1) violated - parent key not found

[Mon 27/3/2017 06:23:07] [OFS_PFT] [ERROR] - Query:'MERGE INTO PR2_PROCESS_TASK T
USING (
SELECT '1431854559190' V_PROCESS_ID,'OFSAA' V_INFODOM_NAME,'3' N_TASK_ID,'0' N_PARENT_TASK_ID,'DT_ACCT_MOB_SUMMARY' V_TASK_REF_UNIQUE_NAME,'DATE' V_TASK_TYPE_CODE,'SP' V_TASK_SUB_TYPE_CODE,'' V_TASK_REF_1_NAME,'' V_TASK_REF_1_VALUE,'' V_TASK_REF_2_NAME,'' V_TASK_REF_2_VALUE,'' V_TASK_REF_3_NAME,'' V_TASK_REF_3_VALUE,'' V_TASK_REF_4_NAME,'' V_TASK_REF_4_VALUE,'2' N_TASK_ORDER FROM DUAL
...

WHEN NOT MATCHED THEN
INSERT
 (V_PROCESS_ID,V_INFODOM_NAME,V_PROCESS_NAME,N_PROCESS_VERSION,V_PROCESS_TYPE_CODE,D_EFFECTIVE_START_DATE,D_EFFECTIVE_END_DATE,F_ACTIVE_FLAG,V_FOLDER_NAME,V_LAST_OPERATION_TYPE_CODE,V_SEEDED_BY,V_CREATED_BY,D_CREATED_DATE,V_LAST_MODIFIED_BY,D_LAST_MODIFIED_DATE,V_LAST_AUTHORIZED_BY,D_LAST_AUTHORIZED_DATE,SOURCE_LANG)
VALUES
 (S.V_PROCESS_ID,S.V_INFODOM_NAME,S.V_PROCESS_NAME,S.N_PROCESS_VERSION,S.V_PROCESS_TYPE_CODE,S.D_EFFECTIVE_START_DATE,S.D_EFFECTIVE_END_DATE,S.F_ACTIVE_FLAG,S.V_FOLDER_NAME,S.V_LAST_OPERATION_TYPE_CODE,S.V_SEEDED_BY,S.V_CREATED_BY,S.D_CREATED_DATE,S.V_LAST_MODIFIED_BY,S.D_LAST_MODIFIED_DATE,S.V_LAST_AUTHORIZED_BY,S.D_LAST_AUTHORIZED_DATE,S.SOURCE_LANG )'
 Error:ORA-01407: cannot update ("OFSCONFIG"."PR2_PROCESS_B"."V_FOLDER_NAME") to NULL

...
[Mon 27/3/2017 06:25:23] [OFS_PFT] [ INFO] - Executing file:/amex/apps/ofsaa/scripts/atomic/insert/dm_pft_bi_ins.sql
[Mon 27/3/2017 06:25:25] [OFS_PFT] [ERROR] - Query:'declare
 l_dimension_id number;
 l_hierarchy_id number;
 v_count number;
begin

 select dimension_id
  into l_dimension_id
  from rev_dimensions_b
  where member_b_table_name = 'DIM_REPORTING_LINE_B';

 select count(ee.object_definition_id) into v_count
  from fsi_m_object_definition_tl ee, fsi_m_object_definition_b dd
  where ee.short_desc = 'Repline Hierarchy'
  AND dd.ID_TYPE = 5
  and dd.object_definition_id = ee.object_definition_id
  and dd.folder_name = '';
if(v_count=0) then

select FSI_OBJECT_DEN_SEQ_NUM.nextval into l_hierarchy_id from dual;

insert into fsi_m_object_definition_b (OBJECT_DEFINITION_ID, OBJECT_ORIGIN_APP_CD, LEAF_NUM_ID, ID_TYPE, TABLE_NAME, FOLDER_ID, FOLDER_NAME, ACCESS_CD, APPL_NAME, DELETED_FLAG, SOURCE_LANG, CREATED_BY, CREATION_DATE, MODIFIED_BY, LAST_MODIFIED_DATE)
values (l_hierarchy_id, 'USER', l_dimension_id, 5, 'DIM_REPORTING_LINE_HIER', null, '', 'W', 'PLATFORM', 'N', 'US', 'SYSADMN', sysdate, 'SYSADMN', sysdate);

insert into fsi_m_object_definition_tl (OBJECT_DEFINITION_ID, SHORT_DESC, LONG_DESC, LANG_CD, CREATED_BY, CREATION_DATE, MODIFIED_BY, LAST_MODIFIED_DATE)
values (l_hierarchy_id, 'Repline Hierarchy', 'Repline Hierarchy', 'US', 'SYSADMN', sysdate, 'SYSADMN', sysdate);

commit;
end if;
end;'
 Error:ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 7
...
[Mon 27/3/2017 06:38:00] [OFS_PFT] [ INFO] - Executing file:/amex/apps/ofsaa/scripts/atomic/insert/dm_postscripts_804.sql
[Mon 27/3/2017 06:38:00] [OFS_PFT] [ERROR] - Query:'insert into FSI_IRC_TS_PARAM_HIST select * from FSI_IRC_TS_PARAM_HIST_BKP'
 Error:ORA-00942: table or view does not exist

[Mon 27/3/2017 06:38:00] [OFS_PFT] [ERROR] - Query:'Drop table FSI_IRC_TS_PARAM_HIST_BKP'
 Error:ORA-00942: table or view does not exist

[Mon 27/3/2017 06:38:00] [OFS_PFT] [ INFO] - # of errors in file=2
[Mon 27/3/2017 06:38:00] [OFS_PFT] [ INFO] - # of statements executed: 0
[Mon 27/3/2017 06:38:00] [OFS_PFT] [ INFO] - Sandbox Scripts
[Mon 27/3/2017 06:38:00] [OFS_PFT] [ INFO] - -- No sandbox-scripts to execute --
[Mon 27/3/2017 06:38:00] [OFS_PFT] [ INFO] -
[Mon 27/3/2017 06:38:00] [OFS_PFT] [ INFO] - ---------------- DB Scripts Execution Completed-----------------
[Mon 27/3/2017 06:38:00] [OFS_PFT] [ INFO] - ==================SOLUTION SETUP IS DONE====================

  >> Below are the applications used from the PFT Application pack

  1. Oracle Financial Services Profitability Management
  2. Oracle Financial Services Enterprise Financial Performance Analytics

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

Changes

 Apply PFT 8.0.4.

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