Datamart Extraction Job Failing

Document ID : KB000116877
Last Modified Date : 03/10/2018
Show Technical Document Details
Issue:
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 (SC51689P.NBI_PROJECT_FORECAST_PK) violated
ORA-06512: at "SC51689P.NBI_EXTRACT_SP", line 362
ORA-06512: at line 1
Cause:
Run the following query on the OLTP DB:

select FIN.OBJECT_ID INVID, INV.name, INV.CODE, COUNT(FIN.OBJECT_ID) INVIDCOUNT 
from FIN_PLANS FIN, INV_INVESTMENTS INV 
where 1=1 
and 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 you get any records back, then those investments have more than one cost plan of record. That is causing the failure.
Resolution:
Two PORs in the system:
User-added image
Set one as POR (it is already a POR)

User-added image
 
Alert:
User-added image
 
 Similarly select the other one and try to set second one as POR:
User-added image
Click on “Yes” Only one will remain. Then you can set any Cost Plan as POR.
User-added image
 
If we know which Cost Plan got created earlier, then try setting that as POR first. Mostly that will correct the system to have only one POR. After that we can set any Cost Plan as POR.

After the investment(s) have only one cost POR, then you can run the Datamart Extraction job and it will run to completed status.