PeopleSoft : SQL to Stop PSQuery Recurrence

-- Update run status to delete in request table

UPDATE PSPRCSRQST R
SET R.RUNSTATUS=2
WHERE EXISTS
  (SELECT 'x'
  FROM sysadm.PSPRCSQUE B ,
    sysadm.PS_QUERY_RUN_CNTRL A
  WHERE a.oprid      = 'SUPERUSER'
  AND a.run_cntl_id  = B.runcntlid
  AND a.oprid        = b.oprid
  AND B.PRCSNAME     = 'PSQUERY'
  AND B.RECURNAME   <> ' '
  AND B.PRCSINSTANCE =
    (SELECT MAX(B1.PRCSINSTANCE)
    FROM PSPRCSQUE B1
    WHERE B.PRCSNAME = B1.PRCSNAME
    AND B.OPRID      = B1.OPRID
    AND B.RUNCNTLID  = B1.RUNCNTLID
    AND B1.RECURNAME = B.RECURNAME
    )
  AND R.PRCSINSTANCE=B.PRCSINSTANCE
  )
  AND R.RUNSTATUS=5
  AND R.RUNCNTLID='TEST8' ;

-- Update run status to delete in queue table

UPDATE PSPRCSQUE R
SET R.RUNSTATUS=2
WHERE EXISTS
  (SELECT 'x'
  FROM sysadm.PSPRCSQUE B ,
    sysadm.PS_QUERY_RUN_CNTRL A
  WHERE a.oprid      = 'SUPERUSER'
  AND a.run_cntl_id  = B.runcntlid
  AND a.oprid        = b.oprid
  AND B.PRCSNAME     = 'PSQUERY'
  AND B.RECURNAME   <> ' '
  AND B.PRCSINSTANCE =
    (SELECT MAX(B1.PRCSINSTANCE)
    FROM PSPRCSQUE B1
    WHERE B.PRCSNAME = B1.PRCSNAME
    AND B.OPRID      = B1.OPRID
    AND B.RUNCNTLID  = B1.RUNCNTLID
    AND B1.RECURNAME = B.RECURNAME
    )
  AND R.PRCSINSTANCE=B.PRCSINSTANCE
  )
  AND R.RUNSTATUS=5
  AND R.RUNCNTLID='TEST8';

PeopleSoft : SQL to remove carriage return

UPDATE PS_PERSON_ADDRESS SET ADDRESS1=REPLACE(ADDRESS1,chr(13),'') WHERE EMPLID ='999999';

PeopleSoft : SQL to find special character in Names

SELECT *
FROM SYSADM.PS_NAMES A
WHERE (A.NAME                        !=   CONVERT(A.NAME,'JA16SJIS')
OR A.LAST_NAME                      !=   CONVERT(A.LAST_NAME,'JA16SJIS')
OR A.LAST_NAME_SRCH         !=   CONVERT(A.LAST_NAME_SRCH,'JA16SJIS')
OR A.FIRST_NAME_SRCH        !=   CONVERT(A.FIRST_NAME_SRCH,'JA16SJIS')
OR A.FIRST_NAME                     !=   CONVERT(A.FIRST_NAME,'JA16SJIS')
OR A.MIDDLE_NAME                !=   CONVERT(A.MIDDLE_NAME,'JA16SJIS')
OR A.NAME_DISPLAY               !=   CONVERT(A.NAME_DISPLAY,'JA16SJIS')
OR A.NAME_FORMAL               !=   CONVERT(A.NAME_FORMAL,'JA16SJIS')
OR A.SECOND_LAST_NAME    !=   CONVERT(A.SECOND_LAST_NAME,'JA16SJIS')
OR A.PREF_FIRST_NAME         !=    CONVERT(A.PREF_FIRST_NAME,'JA16SJIS')
OR A.NAME_AC                          !=    CONVERT(A.NAME_AC ,'JA16SJIS')
OR A.SECOND_LAST_SRCH     !=    CONVERT(A.SECOND_LAST_SRCH,'JA16SJIS')
OR A.NAME_DISPLAY_SRCH   !=    CONVERT(A.NAME_DISPLAY_SRCH,'JA16SJIS'))
AND EFFDT=(SELECT MAX(B.EFFDT) FROM SYSADM.PS_NAMES B WHERE B.EMPLID=A.EMPLID AND B.NAME_TYPE=A.NAME_TYPE);

PeopleSoft : Query to find the Menu Navigation

SELECT * FROM PSXLATITEM WHERE FIELDNAME = 'PROJECT_STATUS';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 = 'RUN_PCPL1000'  à<Component Name>

CONNECT BY PRIOR PORTAL_PRNTOBJNAME = PORTAL_OBJNAME;

PeopleSoft : Direct and Indirect reportees in PeopleSoft

 Below is the syntax to get direct and indirect reportees in peoplesoft:

SELECT… CONNECT BY

Select data with a hierarchical (parent/child) relationship

Syntax:

   SELECT…

   [START WITH initial_condition]

   CONNECT BY [nocycle] PRIOR recurse_condition

   [ORDER SIBLINGS BY order_by_clause]

Key:

    START WITH        : The row(s) to be used as the root of the hierarchy

    CONNECT BY        : Condition that identifies the relationship between

                        parent and child rows of the hierarchy

    NOCYCLE           : Do not circle around loops (where the current row has

                        a child which is also its ancestor.)

    ORDER SIBLINGS BY : Preserve ordering of the hierarchical query

                        then apply the order_by_clause to the sibling rows

Example:

Select level-1

,PEOPLE_ID

, SUPERVISOR_ID

FROM PS_JOB_VW

start with SUPERVISOR_ID = 'xxxxx'

CONNECT BY PRIOR PEOPLE_ID = SUPERVISOR_ID;

PeopleSoft : SQL to search peoplecode

SELECT OBJECTVALUE1 ,OBJECTVALUE2 ,OBJECTVALUE3 ,OBJECTVALUE4 ,PCTEXT

  from PSPCMTXT ;

Example:

SELECT OBJECTVALUE1 ,OBJECTVALUE2 ,OBJECTVALUE3 ,OBJECTVALUE4 ,PCTEXT

  from PSPCMTXT

 WHERE PCTEXT LIKE '%cleardropdownitem%'

In the above query  OBJECTVALUE  represents any one of the following:
1.component.
2.record.
3.field.
4.peoplecode event.

PCTEXT--Represents the actual text in the peoplecode for an event.