The SQL scripts have been created and tested on the following software environment.
Peoplesoft Application: Financial and Supply Chain Management 9.2
Database: Oracle
The SQL scripts have been grouped in the following categories.
Components, Component-Interfaces related queries
Portal Navigation
Process Scheduler
Users, Roles and Permissions
Users logged in
SQLs related to PS-Query
Trees
Category: Components, Component-Interfaces related queries
01) SQL Query to find search records in a CI
SELECT SEARCHRECNAME, ADDSRCHRECNAME
FROM PSBCDEFN
WHERE BCNAME = ‘AJ_MAP_XLAT_TBL_CI’;
02) SQL QUERY TO FIND OUT THE RECORDS EXPOSED BY A CI
SELECT DISTINCT RECNAME
FROM PSBCITEM
WHERE BCNAME = ‘AJ_MAP_XLAT_TBL_CI’;
03) SQL Query to find out the Component associated with a Component Interface
SELECT BCPGNAME,MARKET,MENUNAME
FROM PSBCDEFN
WHERE BCNAME = ‘AJ_MAP_XLAT_TBL_CI’;
04) Identify which USER, ROLE and PERMISSION-LIST has access to a 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 = ‘AJ_MAP_XLAT_TBL_CI’
ORDER BY 1,2,3;
05) Find all records under a specified component.
SELECT DISTINCT RECNAME FROM PSRECDEFN
WHERE ( RECNAME IN (SELECT RECNAME FROM PSPNLFIELD WHERE PNLNAME IN (SELECT DISTINCT B.PNLNAME
FROM PSPNLGROUP A, PSPNLFIELD B WHERE (A.PNLNAME = B.PNLNAME OR A.PNLNAME = B.SUBPNLNAME)
AND A.PNLGRPNAME = ‘AJ_MAP_XLAT_TBL’))
OR RECNAME IN (SELECT RECNAME FROM PSPNLFIELD WHERE PNLNAME IN (SELECT DISTINCT B.SUBPNLNAME
FROM PSPNLGROUP A, PSPNLFIELD B WHERE (A.PNLNAME = B.PNLNAME OR A.PNLNAME = B.SUBPNLNAME)
AND A.PNLGRPNAME = ‘AJ_MAP_XLAT_TBL’))
)
AND RECNAME <> ‘ ‘
ORDER BY RECNAME ASC;
06) SQL query to get all the child records for a parent record:
SELECT RECNAME FROM PSRECDEFN
CONNECT BY PRIOR RECNAME=PARENTRECNAME
START WITH PARENTRECNAME = ‘VOUCHER’;
Category: Portal Navigation
01) Find the navigation to a Component
SELECT P1.PORTAL_NAME, P6.PORTAL_LABEL, P5.PORTAL_LABEL, P4.PORTAL_LABEL, P3.PORTAL_LABEL,
P2.PORTAL_LABEL, P1.PORTAL_LABEL
FROM PSPRSMDEFN P1
LEFT JOIN PSPRSMDEFN P2 ON P2.PORTAL_NAME = P1.PORTAL_NAME AND P2.PORTAL_OBJNAME = P1.PORTAL_PRNTOBJNAME
LEFT JOIN PSPRSMDEFN P3 ON P3.PORTAL_NAME = P2.PORTAL_NAME AND P3.PORTAL_OBJNAME = P2.PORTAL_PRNTOBJNAME
LEFT JOIN PSPRSMDEFN P4 ON P4.PORTAL_NAME = P3.PORTAL_NAME AND P4.PORTAL_OBJNAME = P3.PORTAL_PRNTOBJNAME
LEFT JOIN PSPRSMDEFN P5 ON P5.PORTAL_NAME = P4.PORTAL_NAME AND P5.PORTAL_OBJNAME = P4.PORTAL_PRNTOBJNAME
LEFT JOIN PSPRSMDEFN P6 ON P6.PORTAL_NAME = P5.PORTAL_NAME AND P6.PORTAL_OBJNAME = P5.PORTAL_PRNTOBJNAME
WHERE P1.PORTAL_REFTYPE = ‘C’ AND P1.PORTAL_URI_SEG2 = ‘ACCOUNTINGENTRY’;
02) To the navigation of a particular process name, say ‘FS_JGEN’
SELECT LPAD(‘ ‘, 10*LEVEL-10) || PS.PORTAL_LABEL
FROM PSPRSMDEFN PS
START WITH PS.PORTAL_URI_SEG2 IN (SELECT PSB.PNLGRPNAME FROM PS_PRCSDEFNPNL PSB WHERE PSB.PRCSNAME = ‘FS_JGEN’)
AND PS.PORTAL_URI_SEG3 IN (‘GBL’,’USA’)
CONNECT BY PRIOR PS.PORTAL_PRNTOBJNAME = PS.PORTAL_OBJNAME
AND PS.PORTAL_NAME = ‘EMPLOYEE’ AND PS.PORTAL_LABEL <> ‘Root’;
Category: Process Scheduler
01) Process Scheduler Server(s) 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;
02) Processes running since yesterday
SELECT A.PRCSINSTANCE, A.OPRID, B.XLATLONGNAME “RUN_STATUS”, A.JOBINSTANCE, A.PRCSJOBNAME, A.PRCSNAME, A.PRCSTYPE,
A.BEGINDTTM, A.ENDDTTM, to_char((A.ENDDTTM – A.BEGINDTTM),’HH.MM.SS’)
FROM PSPRCSRQST A, PSXLATITEM B
WHERE B.FIELDNAME = ‘RUNSTATUS’ AND B.FIELDVALUE = A.RUNSTATUS and A.BEGINDTTM > (SYSDATE – 1)
ORDER BY A.PRCSINSTANCE DESC;
Category: Users, Roles and Permissions
01) Determine the list of query security trees that an oprid has access to
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 = ‘AGGARWAA’
AND P.ACCESSIBLE = ‘Y’
ORDER BY 1,2;
02) Identify which USERs, ROLEs and PERMISSION-LISTs have access to load data using ExceltoCI utility
SELECT DISTINCT R.ROLEUSER AS USER_IDS, C.ROLENAME as ROLE, P.CLASSID AS PERMISSION_LIST
FROM PSROLEUSER R, PSROLECLASS C, PSAUTHWEBLIBVW P
WHERE R.ROLENAME = C.ROLENAME
AND P.CLASSID = C.CLASSID
AND P.MENUNAME = ‘WEBLIB_SOAPTOCI’
ORDER BY 1,2,3;
03) Identify through 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 = ‘AJ_MAP_XLAT_TBL_CI’
ORDER BY 1,2,3;
04) 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;
05) List all user ids that are assigned to a specific permission-list
SELECT DISTINCT C.ROLEUSER AS USER_IDS
FROM PSCLASSDEFN A, PSROLECLASS B, PSROLEUSER C
WHERE A.CLASSID = B.CLASSID
AND B.ROLENAME = C.ROLENAME
AND A.CLASSID = ‘PTPT1200’;
06) Role and user ids 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;
Category: Users logged in
01) 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;
02) 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;
03) Number of connections to the environment in last 30 days
SELECT TO_CHAR(A.LOGINDTTM,’YYYY-MM-DD’),count(A.OPRID) “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’)
ORDER BY TO_CHAR(A.LOGINDTTM,’YYYY-MM-DD’) DESC;
Category: SQLs related to PS-Query
01) If you forget the name of a query, but know that it contains certain fields for sure,
the following SQL could be helpful
SELECT A.OPRID, A.QRYNAME, A.DESCR
FROM PSQRYDEFN A
WHERE A.QRYNAME IN (SELECT B1.QRYNAME FROM PSQRYFIELD B1 WHERE B1.FIELDNAME = ‘OPRID’)
AND A.QRYNAME IN (SELECT B1.QRYNAME FROM PSQRYFIELD B1 WHERE B1.FIELDNAME = ‘RUN_CNTL_ID’)
AND A.QRYNAME IN (SELECT B1.QRYNAME FROM PSQRYFIELD B1 WHERE B1.FIELDNAME = ‘JOURNAL_ID’)
AND A.QRYNAME IN (SELECT B1.QRYNAME FROM PSQRYFIELD B1 WHERE B1.FIELDNAME LIKE ‘%CURRENCY%’)
AND A.QRYNAME IN (SELECT B1.QRYNAME FROM PSQRYFIELD B1 WHERE B1.FIELDNAME LIKE ‘%AMT%’) ;
02) Changing the OPRID(Owner) of a private query
(Please change the new OPRID, OLDOPRID and Private Query name appropriately)
update psqrydefn set OPRID = ‘NEWOPRID’ where OPRID = ‘OLDOPRID’ AND qryname = ‘PRIVATE_QUERY_NAME’;
update psqrydefnlang set OPRID = ‘NEWOPRID’ where OPRID = ‘OLDOPRID’ AND qryname = ‘PRIVATE_QUERY_NAME’;
update psqrybind set OPRID = ‘NEWOPRID’ where OPRID = ‘OLDOPRID’ AND qryname = ‘PRIVATE_QUERY_NAME’;
update psqrybindlang set OPRID = ‘NEWOPRID’ where OPRID = ‘OLDOPRID’ AND qryname = ‘PRIVATE_QUERY_NAME’;
update psqryexeclog set OPRID = ‘NEWOPRID’ where OPRID = ‘OLDOPRID’ AND qryname = ‘PRIVATE_QUERY_NAME’;
update psqryexpr set OPRID = ‘NEWOPRID’ where OPRID = ‘OLDOPRID’ AND qryname = ‘PRIVATE_QUERY_NAME’;
update psqryselect set OPRID = ‘NEWOPRID’ where OPRID = ‘OLDOPRID’ AND qryname = ‘PRIVATE_QUERY_NAME’;
update psqrycriteria set OPRID = ‘NEWOPRID’ where OPRID = ‘OLDOPRID’ AND qryname = ‘PRIVATE_QUERY_NAME’;
update psqryfield set OPRID = ‘NEWOPRID’ where OPRID = ‘OLDOPRID’ AND qryname = ‘PRIVATE_QUERY_NAME’;
update psqryfieldlang set OPRID = ‘NEWOPRID’ where OPRID = ‘OLDOPRID’ AND qryname = ‘PRIVATE_QUERY_NAME’;
update psqryfielddep set OPRID = ‘NEWOPRID’ where OPRID = ‘OLDOPRID’ AND qryname = ‘PRIVATE_QUERY_NAME’;
update psqryrecord set OPRID = ‘NEWOPRID’ where OPRID = ‘OLDOPRID’ AND qryname = ‘PRIVATE_QUERY_NAME’;
update psqrystats set OPRID = ‘NEWOPRID’ where OPRID = ‘OLDOPRID’ AND qryname = ‘PRIVATE_QUERY_NAME’;
update psqrydel set OPRID = ‘NEWOPRID’ where OPRID = ‘OLDOPRID’ AND qryname = ‘PRIVATE_QUERY_NAME’;
update PSQRYXFORM set OPRID = ‘NEWOPRID’ where OPRID = ‘OLDOPRID’ AND qryname = ‘PRIVATE_QUERY_NAME’;
update PSQRYTRANS set OPRID = ‘NEWOPRID’ where OPRID = ‘OLDOPRID’ AND qryname = ‘PRIVATE_QUERY_NAME’;
update PSQRYFAVORITES set QRYOWNER = ‘NEWOPRID’ where QRYOWNER = ‘OLDOPRID’ AND qryname = ‘PRIVATE_QUERY_NAME’;
Note: a) For making a private query as public, change the OPRID from non-blank
to single space.
b) You may use the navigation
PEOPLETOOLS–>>UTILITIES–>>ADMINISTRATION–>>QUERY-ADMINISTRATION
and assign a new owner, delete query or rename query.
Category: Trees
01) Getting the Tree Branches, Nodes and detail ranges for a tree.
SELECT J.TREE_NAME,J.TREE_NODE,N.DESCR “NODE NAME”,J.TREE_BRANCH,K.RANGE_FROM, K.RANGE_TO
FROM PSTREENODE J, PS_TREE_NODE_TBL N, PSTREELEAF K
WHERE J.SETID = ‘SHARE’ — Change the SETID here, as appropriate
AND J.TREE_NAME = ‘ACCTROLLUP’ — Change the Tree Name here, as appropriate
AND J.EFFDT = (SELECT MAX(ED.EFFDT) FROM PSTREENODE ED WHERE J.SETID = ED.SETID
AND J.SETCNTRLVALUE = ED.SETCNTRLVALUE AND J.TREE_NAME = ED.TREE_NAME
AND ED.EFFDT <= SYSDATE)
AND EXISTS (select ‘x’ FROM PSTREENODE WHERE J.SETID = J.SETID
AND SETCNTRLVALUE = J.SETCNTRLVALUE AND TREE_NAME = J.TREE_NAME
AND EFFDT <= J.EFFDT AND TREE_NODE_NUM = J.PARENT_NODE_NUM)
AND N.SETID = J.SETID
AND N.TREE_NODE = J.TREE_NODE
AND N.EFFDT = (SELECT MAX(ND.EFFDT) FROM PS_TREE_NODE_TBL ND WHERE N.SETID = ND.SETID
AND N.TREE_NODE = ND.TREE_NODE AND ND.EFFDT <= SYSDATE)
AND J.SETID = K.SETID
AND J.TREE_NAME = K.TREE_NAME
AND K.EFFDT >= J.EFFDT
AND K.EFFDT = (SELECT MAX(KD.EFFDT) FROM PSTREELEAF KD WHERE K.SETID = KD.SETID
AND K.SETCNTRLVALUE = KD.SETCNTRLVALUE AND K.TREE_NAME = KD.TREE_NAME AND KD.EFFDT <= SYSDATE)
AND J.TREE_BRANCH = K.TREE_BRANCH
AND J.TREE_NODE_NUM = K.TREE_NODE_NUM ;
Note: The AND EXISTS line is added to exclude orphan nodes.
Recent Comments