Some Nice to have SQL Scripts in PeopleSoft - Part II

6)      SQL to get the roles assigned to a permission list

SELECT B.ROLENAME, B.CLASSID AS PERMISSION_LIST
FROM PSCLASSDEFN A, PSROLECLASS B
WHERE A.CLASSID = B.CLASSID AND A.CLASSID = 'PTPT1200'
ORDER BY 1,2;

7)      SQL to identify which user, role and permission-list access to a particular component interface

SELECT DISTINCT R.ROLEUSER AS USER_IDS, C.ROLENAME as ROLE, P.CLASSID AS PERMISSION_LIST
FROM PSROLEUSER R, PSROLECLASS C, PSAUTHBUSCOMP P
WHERE R.ROLENAME = C.ROLENAME
AND P.CLASSID = C.CLASSID
AND P.BCNAME = 'JOB_DATA_CI'
ORDER BY 1,2,3;

8)      SQL to get the list of query security trees that an oprid has access

SELECT distinct P.TREE_NAME,P.ACCESS_GROUP,P.ACCESSIBLE
FROM PS_SCRTY_ACC_GRP P, PSROLECLASS C, PSROLEUSER R
WHERE R.ROLENAME = C.ROLENAME
AND P.CLASSID = C.CLASSID
AND R.ROLEUSER = 'User ID'
AND P.ACCESSIBLE = 'Y'
ORDER BY 1,2;

9)      SQL to get the Process Scheduler Server status

SELECT S.SERVERNAME, X.XLATSHORTNAME,X.FIELDVALUE,S.MAXCPU,S.PRCSDISKSPACE,S.LASTUPDDTTM
FROM PSSERVERSTAT S, PSXLATITEM X
WHERE X.FIELDNAME = 'SERVERSTATUS' AND X.FIELDVALUE = S.SERVERSTATUS;

 

10)       SQL query to get all the child records for a parent record:

SELECT RECNAME FROM PSRECDEFN
CONNECT BY PRIOR RECNAME=PARENTRECNAME
START WITH PARENTRECNAME = 'PS_JOB';

 

 

11)       SQL to identify which USER, ROLE and PERMISSION LIST has access to a particular Component Interface

SELECT DISTINCT R.ROLEUSER AS USER_IDS, C.ROLENAME as ROLE, P.CLASSID    

AS PERMISSION_LIST

FROM PSROLEUSER R, PSROLECLASS C, PSAUTHBUSCOMP P

WHERE R.ROLENAME = C.ROLENAME

AND P.CLASSID = C.CLASSID

AND P.BCNAME = 'XXXX_CI'

ORDER BY 1,2,3;

 

12)       SQL Query to find out the Component associated with a Component Interface

SELECT BCPGNAME,MARKET,MENUNAME
FROM PSBCDEFN
WHERE BCNAME = 'XXXX_CI';
 

Click here for Part III