Skip to main content
A
adev1-Visitor
1-Visitor
January 5, 2016
Question

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

  • January 5, 2016
  • 2 replies
  • 4160 views

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;

2 replies

D
ddemay1-Visitor
1-Visitor
January 5, 2016

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

A
adev1-VisitorAuthor
1-Visitor
January 6, 2016

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 ;

D
ddemay1-Visitor
1-Visitor
January 6, 2016

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.

A
adev1-VisitorAuthor
1-Visitor
January 6, 2016

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

D
ddemay1-Visitor
1-Visitor
January 6, 2016

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.

Terms & ConditionsAccessibility statement