Resource Duplicated (Appears Twice) On Project Team Tab/Page

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

The following query was ran to detect users assigned to project team and task assignments twice.

 

SELECT inv.code PrjID

,inv.name PrjName

,res.unique_name ResID

,res.full_name ResName

,COUNT(1) cnt

FROM srm_resources res

JOIN prj_resources pr ON pr.prid = res.id

JOIN prteam tm ON tm.prresourceid = res.id

JOIN inv_investments inv ON inv.id = tm.prprojectid

                   AND inv.is_active = 1

                            AND inv.odf_object_code = 'project'

WHERE pr.prisrole = 0

GROUP BY inv.code

,inv.name

,res.unique_name

,res.full_name

HAVING COUNT(1) > 1

 

PRTEAM table with associated projects have duplicate entries with different creation dates.
Although the reason is unknown, what is the solution to resolve this duplication?

 

Environment

Release : 16.0.2+

Resolution

To resolve the issue, run the following queries in order to arrive at the delete statements needed to resolve the issue from the database/UI standpoint.
Be sure to have a database backup as the procedure directly updates the database.

Please contact the Support team for guidance and supervision.


Example:
   
/*     
   proj=testproject code=TESTPROJ123 id=5AAAAAA
   res=johnsmith
   code=johnsmith123
   id= 5BBBBBB
*/  
 

1. Get the project internal id

SELECT id,code,name FROM INV_INVESTMENTS WHERE code = 'TESTPROJ123'
--id= 5AAAAAA
   
2. Get the resource internal id

SELECT id,unique_name, full_name FROM srm_resources
WHERE unique_name =  'jsmith123'
--id  = 5BBBBBB
   
3. Get assignment internal id(s)

SELECT a.practsum/3600, a.prid, a.team_id
FROM PRASSIGNMENT a 
WHERE a.prresourceid = 5BBBBBB
AND prtaskid IN (SELECT prid FROM prtask WHERE prprojectid = 5AAAAAA)