One Stop Shop for all useful Queries

I have created an Excel Tool to collect the Useful Queries in one place, i.e. One Stop Shop for all useful Queries.
Use of this Tool:
1. This can be used for One Stop Shop for all the queries used in Development And Support.
2. This can be used for One Stop Shop for all your personal Queries.. (As local Copy)
Download Link: Excel Tool To store all Useful Queries

Summary of the requested processes by process status

Query to get summary of the requested process.

select

    RQST.RUNSTATUS,

    RQST.PRCSTYPE,

    (

        select XLAT.XLATLONGNAME

        from PSXLATITEM XLAT

        where XLAT.EFFDT = (

            select max(XLAT_ED.EFFDT)

            from PSXLATITEM XLAT_ED

            where XLAT_ED.FIELDNAME = XLAT.FIELDNAME

            and XLAT_ED.FIELDVALUE = XLAT.FIELDVALUE

        ) and XLAT.FIELDNAME = 'RUNSTATUS'

        and XLAT.FIELDVALUE = RQST.RUNSTATUS

    ) as RUNSTATUS_XLAT,

    count(RQST.PRCSINSTANCE) as TOTAL_PROCESSES,

    min(RUNDTTM) as FIRST_OCCURRED,

    max(RUNDTTM) as LAST_OCCURRED

from PSPRCSRQST RQST

group by RQST.RUNSTATUS, RQST.PRCSTYPE

order by RUNSTATUS_XLAT, RQST.PRCSTYPE

The number of users connected on the environment at the moment

The below query used to get the number of users connected on the environment at the moment.

   1:  select DISTINCT OPRID,

   2:                  LOGIPADDRESS,

   3:                  TO_CHAR(LOGINDTTM, 'YYYY-MM-DD:hh:mi:ss') LOGINTIME,

   4:                  TO_CHAR(LOGOUTDTTM, 'YYYY-MM-DD:hh:mi:ss') LOGOUTIME,

   5:                  TO_CHAR((sysdate), 'YYYY-MM-DD:hh:mi:ss') CURRTIME

   6:    FROM sysadm.PSACCESSLOG WHERE

   7:   (sysdate) - cast(LOGINDTTM as date)  >= 0

   8:   and cast(LOGOUTDTTM as date) - to_date(sysdate) >= 0

   9:   and LOGOUTDTTM = LOGINDTTM;

The information is still not accurate (If the user closes the browser or the connection crash).

Query to find the 10 Largest Objects in DB

SELECT * FROM
(
select
    SEGMENT_NAME,
    SEGMENT_TYPE,
    BYTES/1024/1024/1024 GB,
    TABLESPACE_NAME
from
    dba_segments
order by 3 desc 
) WHERE
ROWNUM <= 10 

Query to remove HTML Tags from Rich Text Long edit box

Below query used to remove HTML tags from Rich text box field.

SELECT REGEXP_REPLACE(DESCRLONG,'<[^>]*>',' '), DESCRLONG FROM PS_HRSTOR_QA_TBL;

Note: Above query only works on Oracle database.

SQL To find Who Modified PeopleCode

The below SQL helps to identify who modified PeopleCode.

SELECT objectvalue1 record_name, objectvalue2 field_name,
       objectvalue3 peoplecode_event, lastupddttm, lastupdoprid
  FROM pspcmprog
WHERE objectvalue1 = :record_name
   AND objectvalue2 = :field_name
   AND UPPER (objectvalue3) = UPPER (:peoplecode_event);

PeopleSoft Permission List Queries

1. Component Permission List Query:
This query identify the permission lists and its description associated with 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 LIKE UPPER (:component_name)
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;

2. Content Reference accessed by a permission list:

This query identifies Content references accessed by Permission List.

SELECT   a.portal_label AS PORTAL_LINK_NAME, a.portal_objname, a.portal_name, a.portal_reftype
FROM psprsmdefn a, psprsmperm b, psclassdefn c
WHERE a.portal_reftype = 'C'
AND a.portal_cref_usgt = 'TARG'
AND a.portal_name = b.portal_name
AND a.portal_reftype = b.portal_reftype
AND a.portal_objname = b.portal_objname
AND c.classid = b.portal_permname
AND a.portal_uri_seg1 <> ' '
AND a.portal_uri_seg2 <> ' '
AND a.portal_uri_seg3 <> ' '
AND c.classid = :permissionlist
AND a.portal_name = :portalname
ORDER BY portal_label;

FRMT = Frame Template

HPGC = Pagelet

HPGT = Homepage Tab

HTMT = HTML template

LINK = Content Reference Link

3. Page Access By Permission List:

SELECT b.menuname, b.barname, b.baritemname, b.pnlitemname AS pagename,
       c.pageaccessdescr,
       DECODE (b.displayonly, 0, 'No', 1, 'Yes') AS displayonly
  FROM psclassdefn a, psauthitem b, pspgeaccessdesc c
WHERE a.classid = b.classid
   AND a.classid = :1
   AND b.baritemname > ' '
   AND b.authorizedactions = c.authorizedactions;

4. PeopleTools Accessed By a Permission List:

SELECT DISTINCT b.menuname
FROM psclassdefn a, psauthitem b
WHERE a.classid = b.classid
AND (   b.menuname = 'CLIENTPROCESS'
OR b.menuname = 'DATA_MOVER'
OR b.menuname = 'IMPORT_MANAGER'
OR b.menuname = 'APPLICATION_DESIGNER'
OR b.menuname = 'OBJECT_SECURITY'
OR b.menuname = 'QUERY'
)
AND a.classid = :PermissionList;

5. 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 = :permissionlist;

6. User IDs assigned to a Permission List:

SELECT   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 = :permissionlist
GROUP BY c.roleuser;

Query for PeopleSoft Project items

 

SELECT PROJECTNAME, 
(CASE R.OBJECTTYPE
When 0 then 'Records'
When 1 then 'Indexes'
When 2 then 'Fields'
When 3 then 'Field Formats'
When 4 then 'Translate Values'
When 5 then 'Pages'
When 6 then 'Menus'
When 7 then 'Components'
When 8 then 'Record PeopleCode'
When 9 then 'Menu PeopleCode'
When 10 then 'Queries'
When 11 then 'Tree Structures'
When 12 then 'Trees'
When 13 then 'Access Groups'
When 14 then 'Colors'
When 15 then 'Styles'
When 16 then 'Business Process Maps'
When 17 then 'Business Processes'
When 18 then 'Activities'
When 19 then 'Roles'
When 20 then 'Process Definitions'
When 21 then 'Server Definitions'
When 22 then 'Process Type Definitions'
When 23 then 'Job Definitions'
When 24 then 'Recurrence Definitions'
When 25 then 'Message Catalogue Entries'
When 26 then 'Dimensions'
When 27 then 'Cube Definitions'
When 28 then 'Cube Instance Definitions'
When 29 then 'Business Interlink'
When 30 then 'SQL'
When 31 then 'File Layout Definitions'
When 32 then 'Component Interfaces'
When 33 then 'Application Engine Programs'
When 34 then 'Application Engine Sections'
When 35 then 'Message Nodes'
When 36 then 'Message Channels'
When 37 then 'Messages'
When 38 then 'Approval Rule Sets'
When 39 then 'Message PeopleCode'
When 40 then 'Subscription PeopleCode'
When 41 then 'unused'
When 42 then 'Comp. Interface PeopleCode'
When 43 then 'Application Engine PeopleCode'
When 44 then 'Page PeopleCode'
When 45 then 'Page Field PeopleCode'
When 46 then 'Component PeopleCode'
When 47 then 'Component Record PeopleCode'
When 48 then 'Component Rec Fld PeopleCode'
When 49 then 'Images'
When 50 then 'Style Sheets'
When 51 then 'HTML'
When 52 then 'unused'
When 53 then 'Permission Lists'
When 54 then 'Portal Registry Definitions'
When 55 then 'Portal Registry Structures'
When 56 then 'URL Definitions'
When 57 then 'Application Packages'
When 58 then 'Application Package PeopleCode'
When 59 then 'Portal Registry User Homepages'
When 60 then 'Analytic Types'
When 61 then 'Archive Templates'
When 62 then 'XSLT'
When 63 then 'Portal Registry User Favourites'
When 64 then 'Mobile Pages'
When 65 then 'Relationships'
When 66 then 'CI Property PeopleCode'
When 67 then 'Optimization Models'
When 68 then 'File References'
When 69 then 'File Type Codes'
When 70 then 'Archive Object Definitions'
When 71 then 'Archive Templates (Type 2)'
When 72 then 'Diagnostic Plug-Ins'
When 73 then 'Analytic Models'
When 74 then 'unused'
When 75 then 'Java Portlet User Preferences'
When 76 then 'WSRP Remote Producers'
When 77 then 'WSRP Remote Portlets'
When 78 then 'WSRP Cloned Portlets Handles'
When 79 then 'Services'
When 80 then 'Service Operations'
When 81 then 'Service Operation Handlers'
When 82 then 'Service Operation Versions'
When 83 then 'Service Operation Routings'
When 84 then 'IB Queues'
When 85 then 'XMLP Template Defn'
When 86 then 'XMLP Report Defn'
When 87 then 'XMLP File Defn'
When 88 then 'XMLP Data Src Defn'
Else 'Unknown' END) As ObjectType
, R.OBJECTVALUE1
, R.OBJECTVALUE2
, R.OBJECTVALUE3
, R.OBJECTVALUE4
FROM PSPROJECTITEM R WHERE PROJECTNAME = '<Project Name>'
ORDER BY OBJECTTYPE ;

Query To Find All Records under a specified component

Following is the query that can be useful to find out all records under a specified component.

 

SELECT DISTINCT (recname)
FROM psrecdefn
WHERE recname IN
(SELECT DISTINCT (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 = 'Component Name'                 -- specify your component name)
AND recname <> ' ')
UNION
SELECT DISTINCT (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 = 'Component Name')               -- specify your component name)
AND recname <> ' ')
AND rectype = '0'                                                 -- specify record type
order by recname asc

 

Note: 0 is a record type of Sql Table.

Delete PeopleSoft Query From the Database

 

The below function can be used to delete a PeopleSoft query from database.

Function DeleteQuery(&sQueryName As string)
   SQLExec("DELETE FROM PSQRYDEFN WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYSELECT WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYRECORD WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYFIELD WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYFIELDLANG WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYCRITERIA WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYEXPR WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYBIND WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYBINDLANG WHERE QRYNAME=:1", &sQueryName);
   /*Below tables are not available in older PS versions*/
   SQLExec("DELETE FROM PSQRYSTATS  WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYEXECLOG WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYFAVORITES WHERE QRYNAME=:1", &sQueryName);
End-Function;

Query To See Objects Included In Project

Run the following query to get all the objects included in the Project.

SELECT projectname,
       objecttype,
       DECODE(objecttype, '2', 'Field',
                          '0', 'Record',
                          '5', 'Page',
                          '6', 'Menu',
                          '8', 'Record Peoplecode',
                          '10', 'Query',
                          '17', 'Business Process',
                          '18', 'Activities',
                          '20', 'Process Definition',
                          '25', 'Message Catalog',
                          '30', 'SQL',
                          '33', 'App Engine',
                          '44', 'Page Peoplecode',
                          '49', 'Image',
                          '46', 'Component Peoplecode',
                          '47', 'Component Record Peoplecode',
                          '48', 'Component Rec Fld Peoplecode',
                          '4', 'Translate Values',
                          '7', 'Component',
                          '43', 'App Engine Steps',
                          '11', 'Trees',
                          '13', 'Access Group',
                          '53', 'Permission List',
                          '19', 'Role',
                          '58', 'Application package peoplecode',
                          '55', 'Portal Registry Structure',
                          '50', 'Style Sheet',
                          '57', 'Application class',
                          '51', 'Html',
                          '34', 'App Engine Section',
                          '56', 'URL DEfinition',
                          '32', 'Component Interface',
                          '37', 'Messages',
                          '36', 'Message Channel',
                          '40', 'Subscription PeopleCode',
                          '38', 'Approval RuleSet') object_type,
       ( objectvalue1
          || '.'
          || objectvalue2
          || '.'
          || objectvalue3 )                         objectname
FROM   psprojectitem
WHERE  projectname = 'Project Name'
       AND objectid2 <> 102
       AND objecttype <> 0
        OR ( projectname = 'Project Name'
             AND objectid2 <> 102
             AND objecttype = 0
             AND objectid2 <> 2 )
ORDER  BY object_type; 

PIA Navigation for a Component

The following query is used to get the PIA Navigation for a component:

SELECT DISTINCT Rtrim (Reverse (Sys_connect_by_path (Reverse (portal_label),

                                ' > ')), ' > ')

                "PIA NAVIGATION"

FROM   psprsmdefn

WHERE  portal_name = 'EMPLOYEE'

       AND portal_prntobjname = 'PORTAL_ROOT_OBJECT'

START WITH portal_uri_seg2 = 'COMPONENT_NAME'

CONNECT BY PRIOR portal_prntobjname = portal_objname;