Some Nice-to-have SQLs
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 R.RECNAME, (CASE WHEN R.RECTYPE = 0 THEN 'Table'
WHEN R.RECTYPE = 1 THEN 'View'
WHEN R.RECTYPE = 2 THEN 'Derived'
WHEN R.RECTYPE = 3 THEN 'Sub Record'
WHEN R.RECTYPE = 5 THEN 'Dynamic View'
WHEN R.RECTYPE = 6 THEN 'Query View'
WHEN R.RECTYPE = 7 THEN 'Temporary Table'
ELSE 'Unknown'
END ) AS RECORD_TYPE
FROM PSRECDEFN R
WHERE ( R.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 R.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 R.RECNAME <> ' '
ORDER BY R.RECNAME ASC;
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) Find the navigation for a 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;
07) Permissions assigned to a role
SELECT DISTINCT B.ROLENAME, A.CLASSID, A.CLASSDEFNDESC
FROM PSCLASSDEFN A, PSROLECLASS B, PSROLEUSER C
WHERE A.CLASSID = B.CLASSID AND B.ROLENAME = C.ROLENAME
AND B.ROLENAME = 'FS FAModule Lead';
08) User ids assigned to a Role
SELECT distinct B.ROLENAME, C.ROLEUSER AS USER_IDS
FROM PSCLASSDEFN A, PSROLECLASS B, PSROLEUSER C
WHERE A.CLASSID = B.CLASSID AND B.ROLENAME = C.ROLENAME
AND C.ROLENAME = 'FS FAModule Lead';
09) Find Roles assigned to a User
SELECT A.ROLEUSER, B.NAME, A.ROLENAME
FROM PSROLEUSER A, PS_SCC_NAMES_QVW B
WHERE A.ROLEUSER = B.EMPLID
AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PS_SCC_NAMES_QVW B_ED WHERE B.EMPLID = B_ED.EMPLID AND B.NAME_TYPE = B_ED.NAME_TYPE AND B_ED.EFFDT <= SYSDATE)
AND B.NAME_TYPE = 'PRI'
AND A.ROLEUSER = '899896161'
ORDER BY 1,2,3;
10) List of Permission Lists having access to a Component
SELECT menu.menuname, compdfn.pnlgrpname, auth.classid permission_list,
CLASS.classdefndesc permission_desc
FROM psauthitem auth,
psmenudefn menu,
psmenuitem menuitm,
pspnlgroup comp,
pspnlgrpdefn compdfn,
psclassdefn CLASS
WHERE menu.menuname = menuitm.menuname
AND menuitm.pnlgrpname = comp.pnlgrpname
AND compdfn.pnlgrpname = comp.pnlgrpname
AND compdfn.pnlgrpname = 'AJ_MAP_XLAT_TBL'
AND auth.menuname = menu.menuname
AND auth.barname = menuitm.barname
AND auth.baritemname = menuitm.itemname
AND auth.pnlitemname = comp.itemname
AND auth.classid = CLASS.classid
GROUP BY menu.menuname, compdfn.pnlgrpname, auth.classid, CLASS.classdefndesc
ORDER BY menu.menuname, compdfn.pnlgrpname, permission_list;
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) Find Queries selecting from the records listed on each line
SELECT A.QRYNAME, A.DESCR, A.LASTUPDOPRID, A.DESCRLONG FROM PSQRYDEFN A
WHERE EXISTS (SELECT B.QRYNAME FROM PSQRYRECORD B WHERE B.RECNAME = 'STDNT_GRPS')
AND EXISTS (SELECT B.QRYNAME FROM PSQRYRECORD B WHERE (B.RECNAME = 'STDNT_FA_TERM' or B.RECNAME = 'STDNT_FA_TRM_VW'))
AND EXISTS (SELECT B.QRYNAME FROM PSQRYRECORD B WHERE B.RECNAME = 'STDNT_AGGR_SCHL')
AND EXISTS (SELECT B.QRYNAME FROM PSQRYRECORD B WHERE B.RECNAME = 'STDNT_AID_ATRBT')
AND EXISTS (SELECT B.QRYNAME FROM PSQRYRECORD B WHERE B.RECNAME = 'SCC_PS_GENP_BND')
AND EXISTS (SELECT B.QRYNAME FROM PSQRYRECORD B WHERE B.RECNAME = 'STDNT_CAR_TERM')
AND EXISTS (SELECT B.QRYNAME FROM PSQRYRECORD B WHERE B.RECNAME = 'ACAD_PROG') ;
03) 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.
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 R.RECNAME, (CASE WHEN R.RECTYPE = 0 THEN 'Table'
WHEN R.RECTYPE = 1 THEN 'View'
WHEN R.RECTYPE = 2 THEN 'Derived'
WHEN R.RECTYPE = 3 THEN 'Sub Record'
WHEN R.RECTYPE = 5 THEN 'Dynamic View'
WHEN R.RECTYPE = 6 THEN 'Query View'
WHEN R.RECTYPE = 7 THEN 'Temporary Table'
ELSE 'Unknown'
END ) AS RECORD_TYPE
FROM PSRECDEFN R
WHERE ( R.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 R.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 R.RECNAME <> ' '
ORDER BY R.RECNAME ASC;
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) Find the navigation for a 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;
07) Permissions assigned to a role
SELECT DISTINCT B.ROLENAME, A.CLASSID, A.CLASSDEFNDESC
FROM PSCLASSDEFN A, PSROLECLASS B, PSROLEUSER C
WHERE A.CLASSID = B.CLASSID AND B.ROLENAME = C.ROLENAME
AND B.ROLENAME = 'FS FAModule Lead';
08) User ids assigned to a Role
SELECT distinct B.ROLENAME, C.ROLEUSER AS USER_IDS
FROM PSCLASSDEFN A, PSROLECLASS B, PSROLEUSER C
WHERE A.CLASSID = B.CLASSID AND B.ROLENAME = C.ROLENAME
AND C.ROLENAME = 'FS FAModule Lead';
09) Find Roles assigned to a User
SELECT A.ROLEUSER, B.NAME, A.ROLENAME
FROM PSROLEUSER A, PS_SCC_NAMES_QVW B
WHERE A.ROLEUSER = B.EMPLID
AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PS_SCC_NAMES_QVW B_ED WHERE B.EMPLID = B_ED.EMPLID AND B.NAME_TYPE = B_ED.NAME_TYPE AND B_ED.EFFDT <= SYSDATE)
AND B.NAME_TYPE = 'PRI'
AND A.ROLEUSER = '899896161'
ORDER BY 1,2,3;
10) List of Permission Lists having access to a Component
SELECT menu.menuname, compdfn.pnlgrpname, auth.classid permission_list,
CLASS.classdefndesc permission_desc
FROM psauthitem auth,
psmenudefn menu,
psmenuitem menuitm,
pspnlgroup comp,
pspnlgrpdefn compdfn,
psclassdefn CLASS
WHERE menu.menuname = menuitm.menuname
AND menuitm.pnlgrpname = comp.pnlgrpname
AND compdfn.pnlgrpname = comp.pnlgrpname
AND compdfn.pnlgrpname = 'AJ_MAP_XLAT_TBL'
AND auth.menuname = menu.menuname
AND auth.barname = menuitm.barname
AND auth.baritemname = menuitm.itemname
AND auth.pnlitemname = comp.itemname
AND auth.classid = CLASS.classid
GROUP BY menu.menuname, compdfn.pnlgrpname, auth.classid, CLASS.classdefndesc
ORDER BY menu.menuname, compdfn.pnlgrpname, permission_list;
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) Find Queries selecting from the records listed on each line
SELECT A.QRYNAME, A.DESCR, A.LASTUPDOPRID, A.DESCRLONG FROM PSQRYDEFN A
WHERE EXISTS (SELECT B.QRYNAME FROM PSQRYRECORD B WHERE B.RECNAME = 'STDNT_GRPS')
AND EXISTS (SELECT B.QRYNAME FROM PSQRYRECORD B WHERE (B.RECNAME = 'STDNT_FA_TERM' or B.RECNAME = 'STDNT_FA_TRM_VW'))
AND EXISTS (SELECT B.QRYNAME FROM PSQRYRECORD B WHERE B.RECNAME = 'STDNT_AGGR_SCHL')
AND EXISTS (SELECT B.QRYNAME FROM PSQRYRECORD B WHERE B.RECNAME = 'STDNT_AID_ATRBT')
AND EXISTS (SELECT B.QRYNAME FROM PSQRYRECORD B WHERE B.RECNAME = 'SCC_PS_GENP_BND')
AND EXISTS (SELECT B.QRYNAME FROM PSQRYRECORD B WHERE B.RECNAME = 'STDNT_CAR_TERM')
AND EXISTS (SELECT B.QRYNAME FROM PSQRYRECORD B WHERE B.RECNAME = 'ACAD_PROG') ;
03) 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.
Comments
Post a Comment