Queries

PeopleSoft Queries related articles.

Permission Lists Assigned to a User

SQL that I find useful in many occasions. It will return a list of permissions that are assigned to a specific user.

SELECT   d.oprid, d.oprdefndesc, c.roleuser, a.rolename, a.classid,
         b.classdefndesc
    FROM psroleclass a, psclassdefn b, psroleuser c, psoprdefn d
   WHERE a.classid = b.classid
     AND c.rolename = a.rolename
     AND d.oprid = c.roleuser
     AND d.oprid = :userid
GROUP BY d.oprid,
         d.oprdefndesc,
         c.roleuser,
         a.rolename,
         a.classid,
         b.classdefndesc;

Get Records on a Component

An SQL that goes against a specific component and bring back ALL related records.

SELECT   recname
    FROM pspnlgroup a, pspnlfield b
   WHERE (a.pnlname = b.pnlname OR a.pnlname = b.subpnlname)
     AND a.pnlgrpname = :component_name
GROUP BY recname

Show Users Assigned to a Specific Role

In a previous post I showed you how to know what Roles are assigned to a specific user. But here is how you find out what Users are assigned to a specific Role.

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;

Show Roles Assigned to a Specific User

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;

Roles, Permissions, and Users Assigned to Portal Content Reference or Component

Roles attached to a component (portal_uri_seg2) or content reference (portal_objname)

SELECT DISTINCT c.portal_name, c.portal_objname, b.rolename, e.descr
  FROM psroleclass b, psprsmperm c, psprsmdefn d, psroledefn e
    WHERE c.portal_objname = d.portal_objname
            AND b.classid = c.portal_permname
            AND b.rolename = e.rolename
            AND (d.portal_uri_seg2 = :1 OR d.portal_objname = :2);

Permissions attached to a component (portal_uri_seg2) or content reference (portal_objname)
 

Roles Assigned to a Component

The SQL will pull Roles assigned to a specific component.

 SELECT B.MENUNAME,
  B.BARITEMNAME   ,
  A.ROLENAME
   FROM PSROLECLASS A ,
  PSAUTHITEM B
  WHERE A.CLASSID = B.CLASSID
AND B.BARITEMNAME = :1 -- COMPONENT NAME
GROUP BY B.MENUNAME,
  B.BARITEMNAME    ,
  A.ROLENAME
ORDER BY B.MENUNAME,
  A.ROLENAME;

Order Portal References and Folders Using Sequance Number

To make things easier on users, you are most likely to order portal content references and portal folders in an alphabetical order. To do that, you need to navigate to your portal structure page (Portal Administrator role required), and edit each content reference and folder sequence numbers to be equal, for example, set them all to 100.

When all of your content references are set to be the same number PeopleSoft will automatically sort them alphabetically.

Users and Emails Under a Certain Role

The query will help you find users with their email addresses for a certain Role Name.

SELECT emailid
   ,oprid
FROM psoprdefn
, psrolemember
WHERE rolename = :role_name
AND oprid = roleuser
AND emailid <> ' '

For example, you could run the query to get email addresses for users who have a role name of "Developer".

Is Component Attached to a Menu?

This SQL will help you find the menus a specific component is attached to.

-- Is component attached to a menu?
-- List all the Menus that have a specific component
SELECT * FROM PSMENUITEM menu
WHERE pnlgrpname = :component_name
AND ITEMTYPE <> 8;

Display Pages Associated to a Component

For example, if you have a component that has 3 pages on it, then when you run this SQL, lets say in your test environment, you should have 3 rows back.

SELECT page.* FROM PSPNLDEFN PAGE, PSPNLGROUP COMP
WHERE COMP.PNLGRPNAME = :component_name
AND PAGE.PNLNAME = COMP.PNLNAME;