Datamart Extraction Fails with Error in NBI_PROJECT_FORECAST_SP

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

The Datamart Extraction Job is failing showing

The following errors can be found on the bg-ca.log file

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

ERROR 2024-10-24 23:53:52,373 [Dispatch Datamart Extraction : bg@myserver (tenant=clarity)] persistence.PersistenceController (clarity:myuser:mysession:Datamart Extraction) () Error happened with error code:45000
ERROR 2024-10-24 23:53:52,374 [Dispatch Datamart Extraction : bg@myserver (tenant=clarity)] niku.njs (clarity:myuser:mysession:Datamart Extraction) () Error executing job: 5198645
com.niku.union.persistence.PersistenceException: 
SQL error code: 0
Error message: ERROR: Error in NBI_EXTRACT_SP: Error in NBI_EXTRACT_ATX_SP - Executing  NBI_EXTR_PCF_SP: Error in NBI_EXTR_PCF_SP - executing NBI_PROJECT_CURRENT_FACTS_SP: Error in NBI_PROJECT_CURRENT_FACTS_SP - Calling NBI_PROJECT_FORECAST_SP since there are projects with Forecast: Error in NBI_PROJECT_FORECAST_SP -Populating the planning related information for each of the plan type for the given project: ON CONFLICT DO UPDATE command cannot affect row a second time
  Where: PL/pgSQL function nbi_extract_sp() line 61 at RAISE

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.