Show Roles Assigned to a Specific User

Your rating: None Average: 2.3 (3 votes)

Here is a query that I often use to lookup Roles assigned to a specific PeopleSoft user.
At run time, replace :1 with OPRID your are looking for OR user name (partial search also works).

 SELECT C.OPRID,
  C.OPRDEFNDESC         ,
  A.ROLENAME            ,
  A.DESCR
   FROM PSROLEDEFN A,
  PSROLEUSER B      ,
  PSOPRDEFN C
  WHERE B.ROLENAME = A.ROLENAME
AND C.OPRID        = B.ROLEUSER
AND (C.OPRID      =:1
OR C.OPRDEFNDESC LIKE upper(:2))
GROUP BY C.OPRID,
  C.OPRDEFNDESC ,
  A.ROLENAME    ,
  A.DESCR;
-- Make sure when passing the :2 value to pass % with it for the 'Like' search to work.  example :2 = %John%

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!