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 B.ROLENAME LIKE UPPER(:1)
GROUP BY C.OPRID,
  C.OPRDEFNDESC ,
  A.ROLENAME    ,
  A.DESCR
ORDER BY C.OPRID;