Identify users that are collaboration manager and project names

Document ID : KB000031409
Last Modified Date : 14/02/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 / Participants and add other users and then mark it as collaboration manager
3. Now the project will have couple users that are collaboration manager, how identify it for all our projects?

Environment:

Applies to all supported PAS environments. 

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):

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')