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.
Find all Projects where a user/s is Collaboration Manager
-- Get list of all projects where admin is Collaboration Manager
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'
To find for more than one user, we can evaluate change the last filter as below
-- Get list of all projects where admin, user2, user3, userx is Collaboration Manager
AND CPPV.USER_NAME IN ('admin', 'user2', 'user3', 'userx')
Find all Users on a Project along with Collaboration Manager status
Select
CPPV.USER_ID USER_ID,
CPPV.USER_NAME USERNAME,
CPPV.PROJECT_ID PROJECT_ID,
CPPV.is_project_manager ,
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 INV.NAME like '%PROJECT_NAME%'; -- Need to replace the PROJECT_NAME