Datamart Extraction Fails with Error NBI_PROJECT_FORECAST_PK

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

The Datamart Extraction Job is failing.

Found this error message in bg-ca.log:

Job Completed 9/20/18 12:06 AM java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-20000: Error in NBI_EXTRACT_SP - Executing  NBI_EXTR_PCF_SP: ORA-20000: Error in NBI_EXTR_PCF_SP - executing NBI_PROJECT_CURRENT_FACTS_SP: ORA-20000: Error in NBI_PROJECT_CURRENT_FACTS_SP - Calling NBI_PROJECT_FORECAST_SP since there are projects with Forecast: ORA-20000: Error in NBI_PROJECT_FORECAST_SP -Populating the planning related information for each of the plan type for the given project: ORA-00001: unique constraint (XXXXX.NBI_PROJECT_FORECAST_PK) violated
ORA-06512: at "XXXXX.NBI_EXTRACT_SP", line 362
ORA-06512: at line 1

Cause

Run the following query to confirm:

select FIN.OBJECT_ID INVID, INV.name, INV.CODE, COUNT(FIN.OBJECT_ID) INVIDCOUNT 
from FIN_PLANS FIN, INV_INVESTMENTS INV 
where FIN.PLAN_TYPE_CODE = 'FORECAST' 
and fin.is_plan_of_record = 1 
and FIN.OBJECT_ID = INV.id 
group by fin.object_id, inv.name, inv.code 
having count(fin.object_id) > 1

If any records are returned, then those investments have more than one cost plan of record.

Resolution

1. Review the investment cost plans and make sure there is only one cost plan of record.
2. Delete the duplicate and/or similar cost plan of record
3. After the investment(s) have only one cost POR, run the Datamart Extraction job.