Posted by Nitin Pai
Comments (1)
November 23rd, 2007

Peoplesoft provides an online page to delete OPRID. However, this can become cumbersome if you need to purge or delete 10+ OPRID’s as part of your cleanup exercise. I have created the below PL/SQL script which basically mimics what the online purge process executes.

DECLARE

CURSOR C1 IS

SELECT decode(A.SQLTABLENAME,’ ‘,’PS_’||A.RECNAME,A.SQLTABLENAME) RECNAME

FROM PSRECDEFN A

WHERE RECNAME IN (SELECT RECNAME FROM PS_TBLSELECTION_VW)

AND RECNAME != ‘PSOPRDEFN’

ORDER BY 1;

CURSOR C2 IS

SELECT decode(B.SQLTABLENAME,’ ‘,’PS_’||B.RECNAME,B.SQLTABLENAME) RECNAME

FROM PSRECDEFN B

WHERE

RECNAME IN (SELECT RECNAME FROM PS_ROLEUSR_TBLS_VW)

AND RECNAME != ‘PSOPRDEFN’

Comments (1)

Chris Bosman - July 29th, 2010

This for me is an awesome find, the sad thing is we are on a DB2 database. I will probably use this as a starting grid to create my mass delete process if that's ok. I was thinking something else, as my process might be online, and i could probably just insert rows into PS_PRG_USR_PROFILE and PSOPRDEL thinking that would trigger the oprid delete? Any comments?

Comments are closed.