Some Nice to have SQL Scripts in PeopleSoft - Part I

1)      SQL Query to identify the record's of a component

SELECT DISTINCT D.recname TableName,
D.occurslevel ScrollName
FROM pspnlgrpdefn A,
pspnlgroup B,
pspnldefn C,
pspnlfield D,
psrecdefn E
WHERE A.pnlgrpname = B.pnlgrpname
AND A.market = B.market
AND B.pnlname = C.pnlname
AND C.pnlname = D.pnlname
AND A.pnlgrpname = 'JOB_DATA'
AND E.recname = D.recname
AND E.rectype IN ( 0 )
AND To_char(Bitand(D.fielduse, 16)) <> '16'
ORDER BY 2,1

2)      SQL to find the navigation to a Component

SELECT LEVEL0.PORTAL_LABEL || ' > ' || LEVEL1.PORTAL_LABEL || ' > ' || LEVEL2.PORTAL_LABEL || ' > ' || level3.PORTAL_LABEL PATH_TO_COMPONENT
FROM PSPRSMDEFN level3
, PSPRSMDEFN level2
, PSPRSMDEFN level1
, PSPRSMDEFN LEVEL0
WHERE level3.PORTAL_URI_SEG2 = 'JOB_DATA'
AND level3.PORTAL_PRNTOBJNAME = level2.PORTAL_OBJNAME
AND level2.PORTAL_PRNTOBJNAME = level1.PORTAL_OBJNAME
AND level1.PORTAL_PRNTOBJNAME = LEVEL0.PORTAL_OBJNAME
AND level3.PORTAL_NAME = level2.PORTAL_NAME
AND level2.PORTAL_NAME = level1.PORTAL_NAME
AND level1.PORTAL_NAME = LEVEL0.PORTAL_NAME

3)      SQL to get the list of users connected to the environment in last 30 days, with connections count

 

SELECT TO_CHAR(A.LOGINDTTM,'YYYY-MM-DD'),A.OPRID, sum(1) "Connections"
FROM PSACCESSLOG A
WHERE TO_CHAR(A.LOGINDTTM,'YYYY-MM-DD') >= TO_CHAR(SYSDATE - 30,'YYYY-MM-DD')
GROUP BY TO_CHAR(A.LOGINDTTM,'YYYY-MM-DD'),A.OPRID
ORDER BY TO_CHAR(A.LOGINDTTM,'YYYY-MM-DD') DESC, A.OPRID;

 

4)      SQL to get the Number of users connected to the environment in last 30 days

SELECT TO_CHAR(A.LOGINDTTM,'YYYY-MM-DD'),count(distinct A.OPRID) "Users"
FROM PSACCESSLOG A
WHERE TO_CHAR(A.LOGINDTTM,'YYYY-MM-DD') >= TO_CHAR(SYSDATE - 30,'YYYY-MM-DD')
GROUP BY TO_CHAR(A.LOGINDTTM,'YYYY-MM-DD')
ORDER BY TO_CHAR(A.LOGINDTTM,'YYYY-MM-DD') DESC;

5)      SQL to get the Role's and user id's assigned to a permission list

 

SELECT C.ROLEUSER AS USER_IDS, B.ROLENAME as ROLE
FROM PSCLASSDEFN A, PSROLECLASS B, PSROLEUSER C
WHERE A.CLASSID = B.CLASSID AND B.ROLENAME = C.ROLENAME AND A.CLASSID = 'PTPT1200'
ORDER BY 1,2;

Click for Part II