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
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.