Re: Stake Holders Groups list (253 Views)
Regular Advisor
Peter Zehrer
Posts: 98
Registered: ‎03-11-2010
Message 1 of 2 (281 Views)
Accepted Solution

Stake Holders Groups list



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.



Please use plain text.
Honored Contributor
Posts: 696
Registered: ‎07-13-2010
Message 2 of 2 (253 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;


Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
Please use plain text.
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