How to identify collaboration managers on projects?

Document ID : KB000072806
Last Modified Date : 09/03/2018
Show Technical Document Details
Issue:
We have the need to know what users are collaboration managers and which projects are involved; we spent significant time going one by one to be aware about that.

STEPS TO REPRODUCE:
1. Create a project (Automatically project creator is: Collaboration Manager)
2. Go to Team Tab > Participant and add other users and then Mark it as collaboration manager
3. Now the project will have couple users that are collaboration manager, how to identify it for all our projects?
Environment:
All CA PPM releases
Resolution:
The following query can be useful to get the desired information to know all projects where specific user is collaboration manager (just need to change last filter to the appropriate user name):

We have the need to know what users are collaboration managers and which projects are involved; we spent significant time going one by one to be aware about that.

Select
CPPV.USER_ID USER_ID, 
CPPV.USER_NAME USERNAME, 
CPPV.PROJECT_ID PROJECT_ID, 
INV.CODE PRJ_CODE, 
INV.NAME PRJ_NAME 
from CLB_PROJECT_PARTICIPANTS_V CPPV 
LEFT OUTER JOIN INV_INVESTMENTS INV 
ON (INV.ID = CPPV.PROJECT_ID) 
WHERE CPPV.IS_PROJECT_MANAGER = 1 --1 if is Collaboration Manager
AND CPPV.USER_NAME = 'admin'

If we need specific users, we can evaluate change the last filter for this one:
AND CPPV.USER_NAME IN  ('admin', 'user2', 'user3', 'userx')