SQL Joins

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 

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

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;

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;

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;

SQL to get the 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 = 'Permission List Name';

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;

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; 

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;

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;

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';

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;

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

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

SQL Query to find out the records exposed by a Component Interface

      SELECT distinct RECNAME
        FROM PSBCITEM
       WHERE BCNAME = 'XXXX_CI';

SQL Query to find search records in a Component Interface

SELECT SEARCHRECNAME, ADDSRCHRECNAME
FROM PSBCDEFN
WHERE BCNAME = 'XXXX_CI';

SQL to 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 = 'JOB_DATA'))
             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 = 'JOB_DATA'))
           )

SQL to find the Navigation of a PeopleSoft Process

SQL to find the navigation to the run control page by Process Name in PeopleSoft.

SELECT E.PRCSTYPE, E.PRCSNAME, F.DESCR, DECODE( H.PORTAL_LABEL,' ','','','','Root','Root ','Root > ' || H.PORTAL_LABEL) || ' > ' || G.PORTAL_LABEL || ' > ' || C.PORTAL_LABEL || ' > ' || B.PORTAL_LABEL || ' > ' || A.PORTAL_LABEL || ' > ' || D.PORTAL_LABEL , D.DESCR254, E.PNLGRPNAME,D.PORTAL_NAME,D.PORTAL_REFTYPE,D.PORTAL_OBJNAME,F.PRCSTYPE,F.PRCSNAME
FROM PSPRSMDEFN D, PS_PRCSDEFNPNL E, PS_PRCSDEFN F, PSPRSMDEFN A, PSPRSMDEFN B, PSPRSMDEFN C, PSPRSMDEFN G, PSPRSMDEFN H
WHERE E.PNLGRPNAME = D.PORTAL_URI_SEG2
AND E.PRCSTYPE = F.PRCSTYPE
AND E.PRCSNAME = F.PRCSNAME
AND D.PORTAL_NAME = 'EMPLOYEE'
AND D.PORTAL_REFTYPE = 'C'
AND E.PNLGRPNAME > ' '
AND A.PORTAL_NAME = 'EMPLOYEE'
AND A.PORTAL_OBJNAME = D.PORTAL_PRNTOBJNAME
AND B.PORTAL_NAME = 'EMPLOYEE'
AND B.PORTAL_OBJNAME = A.PORTAL_PRNTOBJNAME
AND C.PORTAL_NAME (+) = 'EMPLOYEE'
AND C.PORTAL_OBJNAME(+) = B.PORTAL_PRNTOBJNAME
AND G.PORTAL_NAME(+) = 'EMPLOYEE'
AND G.PORTAL_OBJNAME(+) = C.PORTAL_PRNTOBJNAME
AND H.PORTAL_NAME(+) = 'EMPLOYEE'
AND H.PORTAL_OBJNAME(+) = G.PORTAL_PRNTOBJNAME
AND E.PRCSNAME ='Process Name'
ORDER BY 1, 2, 4

Delivered Page to Search Component Navigation.

There was several solutions to find the navigation path for component and page. There were several SQL statements for different databases. Finally Oracle as developed page this purpose

• Main Menu > Enterprise Components > Find Object Navigation
• Setup HRMS > System Administration > Utilities > Portal Navigation Path.