ORA-20100 - DWH_X_INV_SUM_FACTS - Load Data Warehouse job failure

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

The Load Data Warehouse job fails in its full mode with the following error message.

ClarityDB - isOracle? - An error occurred executing this job entry : 
Couldn't execute SQL: 
BEGIN  DWH_X_INV_SUM_FACTS_LOAD(    P_DBLINK => 'someDBLINK',    P_LAST_LOAD_DATE => to_date('1910/01/01 00:00:00', 'yyyy/MM/dd HH24:mi:ss'),    P_CURRENT_DIM_LOAD_DATE => to_date('1947/07/09 00:00:45', 'yyyy/mm/dd HH24:MI:SS'), P_ARRAY_SIZE => 50000  );
END;
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: 
ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_X_INV_SUM_FACTS. 
SQLERRM : ORA-06502: PL/SQL: numeric or value error: 
Bulk bind: Error in defineORA-06512: at "someDB.DWH_X_INV_SUM_FACTS_LOAD", line 120ORA-06512: at line 2

Cause

DWH_X_INV_SUM_FACTS.PLAN_ROI.PLAN_ROI is defined as of type Number (32, 6), but a value that is attempted to be inserted into this column by the job is a large number such as "-200000000000001234560000000000". Corruption in a Financial Plan record caused such a large number to be inserted into the transactional DB.

Resolution

Making changes to the Financial Plan in question resolved the issue.