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’
{ 1 comment }
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 on this entry are closed.