Jan 12, 2017

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