Stake Holders Groups list (341 Views)
Reply
Regular Advisor
Peter Zehrer
Posts: 99
Registered: ‎03-11-2010
Message 1 of 2 (341 Views)
Accepted Solution

Stake Holders Groups list

Hi,

 

Can any one pelase let me know the SQL query to pull the list of Security groups assigned to project as stake holders.  I am able to get the list of Resources but not groups.  Attached screenshot.


Thanks,

Vamsee.

Honored Contributor
Utkarsh_Mishra
Posts: 713
Registered: ‎07-13-2010
Message 2 of 2 (313 Views)

Re: Stake Holders Groups list

[ Edited ]

Here you go... the SQL shows the users and the users in the security group that are assigned as the stakeholder.

 

The second part of the query list the security group users. You can modify this to simply get the security group name or the ID.

 

 

SELECT DISTINCT gpu.user_id, 'User' TYPE, 'N/A' security_group
           FROM itg_trustee t, itg_group_participant_users gpu, pm_projects p
          WHERE p.project_id = <project_id>
            AND t.project_type_id = p.project_type_id
            AND t.trustee_type_code = 1
            AND gpu.trustee_id = t.trustee_id
UNION /* Query for Security group*/
SELECT DISTINCT us.user_id, 'User from Security Group' TYPE,
                ks.security_group_name security_group
           FROM knta_user_security us,
                itg_trustee t,
                itg_security_group_trustees sgt,
                pm_projects p,
                knta_security_groups_nls ks
          WHERE p.project_id = <project_id>
            AND t.project_type_id = p.project_type_id
            AND t.trustee_type_code = 1
            AND sgt.trustee_id = t.trustee_id
            AND us.security_group_id = sgt.security_group_id
            AND ks.security_group_id = us.security_group_id;

 

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation.