cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

We are happy to announce the new Windchill Customization board! Learn more.

How to get any object team and there roles, groups and users from database script

adev
1-Newbie

How to get any object team and there roles, groups and users from database script

Hello,

I am trying to create a database script from which I can get the any business object team --> (group, Role ) --> users as of now I am able to get the Object team and roles but in Role I am not able to get users from the Roles can any one please help how I can find this

here is the script I have wrote

select WTPARTMASTER.WTPARTNUMBER  as PART_NUMBER ,WTPARTMASTER.NAME  as PART_NAME ,  TEAMTEMPLATE.NAME as TEMPLATE_NAME ,ROLEPRINCIPALMAP.ROLE from TEAMTEMPLATE, ROLEPRINCIPALMAP, WTPART, WTPARTMASTER

where WTPARTMASTER.IDA2A2 = WTPART.IDA3MASTERREFERENCE and WTPARTMASTER.WTPARTNUMBER = '0000000161' and TEAMTEMPLATE.IDA2A2 = ROLEPRINCIPALMAP.IDA3A4

and WTPART.IDA3TEAMTEMPLATEID = TEAMTEMPLATE.IDA2A2;

8 REPLIES 8
ddemay
1-Newbie
(To:adev)

Do not start with team template, start with 'team' table. See if that helps.

adev
1-Newbie
(To:ddemay)

Hello David,

I try with that but in User name it is returning the owner of the data who actually create it, I was trying to get the user who are assign to that group

select WTPartMaster.WTPARTNUMBER, WTPartMaster.NAME, ROLEPRINCIPALMAP.ROLE, Team.NAME, WTUser.NAME from WTPart, WTPartMaster, Team, ROLEPRINCIPALMAP, WTUser

where WTPARTMASTER.IDA2A2 = WTPART.IDA3MASTERREFERENCE and WTPARTMASTER.WTPARTNUMBER = '0000000161'

and WTPart.IDA3TEAMID = Team.IDA2A2 and ROLEPRINCIPALMAP.IDA3A4 =  Team.IDA2A2 and Team.IDA3A2OWNERSHIP = WTUser.IDA2A2 ;

ddemay
1-Newbie
(To:adev)

Regarding your reply, that is because you select to output WTUser.NAME.

That is the creator of the object, not a person on a role of the team.

There are many old posts migrated from PTC User that cover this topic, and others may respond also, but it would be good to at least make sure the correct columns are identified even before attempting to join, union, or link via keys.

Are you using any type of graphical interface to complete this task?

I could just post this for you, but I think it helps to let you try something, reply, and point out things along the way to provide a stronger confidence in the final solution.

adev
1-Newbie
(To:ddemay)

Hello David,

No I am not using any graphical interface I am just generating reports from SQL developer, that would be very useful if we see the old post regarding the this post

Thanks

Ankit

ddemay
1-Newbie
(To:adev)

SQL Developer is typically a graphical UI. Assuming you are using oracle, the as for pseudonyms should help make it easier on you.

Please search for the old posts, I only have time to inform you they exist - searching the table names will help.

I suggest using a UI that allows you to browse a specific database table as opposed to moving straight to report generation capabilities in a product.

Seems like you need to investigate the multiple rows for table roleprincipalmap (assuming multiple roles and multiple participants) and process in a for each match or occurrence fashion.

Good luck.

adev
1-Newbie
(To:ddemay)

Hi David,

Well I try to find but looks like information related to WTUser and WTGoup is not there

ggaseta
1-Newbie
(To:adev)

I'm looking for exactly same thing. I'd like to report users x groups and looks like there is  no connection between the objects.

You have to use a query builder report (with a customized Java class) to have users and groups together.

I suggest you to join this group Reporting and also take a look at this document Resource for reporting

In it you can find lots of query builder report ready to use and also what you're looking for.

Marco
Top Tags