datamart extract is failing the bg-ca.log showing “cannot insert NULL into NBI_PRT_FACTS.TEAM_ID

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Our datamart extract job failed in production this morning. Here is a copy of the error message: PMD application error java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-20000: Error in NBI_EXTRACT_SP - Calling NBI_EXTR_PRTF_SP: ORA-20000: Error in NBI_EXTR_PRTF_SP - CHECKING IF THERE ARE ANY EVENTS TO PROCESS: ORA-20000: Error in NBI_PRTF_SP - DONE DROPPING INDEXES: ORA-01400: cannot insert NULL into ("CLARITY"."NBI_PRT_FACTS"."TEAM_ID") ORA-06512: at "CLARITY.NBI_EXTRACT_SP", line 362 ORA-06512: at line 1 

Cause

The STR are unknown.

Resolution

 

Run the following query to find the records with null values for the team_id column

Query1
select n.prj_object_id as assignment_id from nbi_events n , prassignment p
where n.PRJ_OBJECT_ID = p.prid
and n.status = 'PROCESSING'
and n.prj_object_type = 'ASSIGNMENT'
and p.team_id is null

Replace the ASSIGNMENT_ID and insert in the next statement to retrieve the team_id for each assignment_id for records that are being processed by the datamart extraction job

Query2
select t.prid as team_id, a.prid as assignment_id
from prteam t, prtask p, prassignment a
where a.prtaskid = p.prid
and p.prprojectid = t.prprojectid
and a.prresourceid = t.prresourceid
and a.prid in (ASSIGNMENT_ID, ASSIGNMENT_ID, ASSIGNMENT_ID)

OR if you want to retrieve all records as well as those being processed by the datamart extraction job with null values for the team_id column

Query3
select t.prid as team_id, a.prid as assignment_id
from prteam t, prtask p, prassignment a
where a.prtaskid = p.prid
and p.prprojectid = t.prprojectid
and a.prresourceid = t.prresourceid
and a.team_id is null

 

 

If the above return no results, there may be orphan assignments where there is no associated team (resource has assignment but is not part of the team.

To find that run this assignment query:

select i.name, i.code, t.prname, t.prexternalid

from prassignment a,    prtask t,  inv_investments i

where a.team_id is null and a.prtaskid = t.prid and i.id = t.prprojectid

 

This query will return assignments with null team_id

You can then go to the project and task to verify if the assignment is there or not.  

If you do see the specific assignment here try removing the assignment and adding it back.  If you cannot remove the assignment or do not see it  a direct database update is required to resolve this problem.

Please contact Support for assistance with implementing this solution.