How to find Primary Role ID?

[MarkCrump4459879]

Hi,

 

I have a query someone created that is based off the Primary Role ID number (a 6-digit number). I've forgotten how to dig up this information.

 

Can someone remind me?

[Deactivated User]

From the Tech Ref for

PRJ_RESOURCES

 

PRPRIMARYROLEID

The primary role of a non-role resource. It is Null for any role rows and will only indicate rows that are themselves roles. This refers to PRJ_RESOURCES.PRID.

 

Is that what you are after?

[MarkCrump4459879]

I'm not sure:

 

Here is the query in question:

SELECT  @SELECT:r.user_id:user_id@

                     ,@SELECT:r.full_name:full_name@

                     ,@SELECT:r.unique_name:unique_name@

                     ,@SELECT:r.last_name:last_name@

                     ,@SELECT:r.first_name:first_name@

                             ,@SELECT:p.prprimaryroleid:roleid@

             FROM    srm_resources r, cmn_sec_users u, prj_resources p

             WHERE   u.id = r.user_id 

                       AND   r.user_id NOT IN (1,8,9,5085903,5005381,5095904,5096903,5000008,5001008)              

                       AND   p.prid = r.id

                       AND   p.prprimaryroleid IN (5005574,5005581,5005572,5005573,5005560,5005590)

               AND   @WHERE:SECURITY:RESOURCE:r.ID@

               AND   @FILTER@

               @BROWSE-ONLY:         

                     AND u.user_status_id IN (select id from cmn_lookups where lookup_type='SEC_USER_STATUS' AND lookup_code IN ('ACTIVE','LOCK'))

               :BROWSE-ONLY@

 

I remember looking up the 7-digit code for the role ID before. It's just been a few years since I dug it up and I forgot how.

[Deactivated User]

Well, your query seems to return the resources which have a specific primary role.

Iif you want to know the internal ID for a role to insert into the query then use this query

SELECT

PRJ_RESOURCES.PRID ROLE_ID

,SRM_RESOURCES.LAST_NAME ROLE_NAME

,SRM_RESOURCES.FIRST_NAME

,SRM_RESOURCES.UNIQUE_NAME ROLE_CODE

From PRJ_RESOURCES, SRM_RESOURCES

Where PRJ_RESOURCES.PRISROLE = 1

AND PRJ_RESOURCES.PRID = SRM_RESOURCES.ID

 

It will give the internal ID, role name and role code for all types of roles.

 

The option is to modify your query so that you can list the roles directly in the query.

[MarkCrump4459879]

It's even easier: it shows up in the URL when you click on the resource.

 

http://clarity/niku/nu#action:projmgr.editResource&id=5044005&odf_return_to=projmgr.getResources%26reload%3Dtrue

[Deactivated User]

... if you want to find out the ID's for one or a small number of roles. ;-)