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

No comments:

Post a Comment