PeopleSoft: Cleaning Up After A Portal Registry Mess

Have you ever created a PeopleSoft Portal Registry Structure that you later didn't want, or that you realized had the wrong name?  You can't rename OR delete it, in either App Designer or the PIA.  There's no easy way to fix it...until now.  ;-)



Please read this article about Process Definitions first, which gives more contextual detail about how to clean up from a Process Definition mess.  That will allow what comes below to make more sense to you.

Here's the script you need to run to get the update statements for the list of Portal Registry tables that need to be updated:

select   'update sysadm.'   || r.recname   || ' set portal_objname = ''Y_FA_PLANS_SUBPLANS_GBL'' where portal_objname = ''Y_FA_PROGRAMS_PLAN_GBL'';'   from sysadm.psrecfield f, sysadm.psrecdefn r where fieldname = 'PORTAL_OBJNAME'   and r.recname = f.recname   and r.rectype = 0;

It generated a longer list than I needed, characterized by several "table or view does not exist" errors.  Assuming you want to rename your PORTAL_OBJNAME from 'Y' to 'X', it turns out that the following are all the update statements that really need to be executed:

update sysadm.PSPRSMATTR set portal_objname = 'X' where portal_objname = 'Y';update sysadm.PSPRSMATTRLANG set portal_objname = 'X' where portal_objname = 'Y';update sysadm.PSPRSMATTRVAL set portal_objname = 'X' where portal_objname = 'Y';update sysadm.PSPRSMATTRVALNG set portal_objname = 'X' where portal_objname = 'Y';update sysadm.PSPRSMCTRBEXCL set portal_objname = 'X' where portal_objname = 'Y';update sysadm.PSPRSMDEFN set portal_objname = 'X' where portal_objname = 'Y';update sysadm.PSPRSMDEFNLANG set portal_objname = 'X' where portal_objname = 'Y';update sysadm.PSPRSMDEL set portal_objname = 'X' where portal_objname = 'Y';update sysadm.PSPRSMHPASGPGLT set portal_objname = 'X' where portal_objname = 'Y';update sysadm.PSPRSMPERM set portal_objname = 'X' where portal_objname = 'Y';update sysadm.PSPRSMPERM_RMT set portal_objname = 'X' where portal_objname = 'Y';update sysadm.PSPRSMSYNC set portal_objname = 'X' where portal_objname = 'Y';update sysadm.PSPRSMSYSATTR set portal_objname = 'X' where portal_objname = 'Y';update sysadm.PSPRSMSYSATTRVL set portal_objname = 'X' where portal_objname = 'Y';update sysadm.PSPRSMWSRPCONS set portal_objname = 'X' where portal_objname = 'Y';update sysadm.PSPRSMWSRPPROD set portal_objname = 'X' where portal_objname = 'Y';update sysadm.PSPRUFDEFN set portal_objname = 'X' where portal_objname = 'Y';update sysadm.PSPRUHTAB set portal_objname = 'X' where portal_objname = 'Y';update sysadm.PSPRUHTABPGLT set portal_objname = 'X' where portal_objname = 'Y';

Here are the results of those update statement executions:

0 rows updated.0 rows updated.0 rows updated.0 rows updated.0 rows updated.1 rows updated.0 rows updated.0 rows updated.0 rows updated.1 rows updated.0 rows updated.0 rows updated.0 rows updated.0 rows updated.0 rows updated.0 rows updated.0 rows updated.0 rows updated.0 rows updated.

In my case, it turns out that only the PSPRSMDEFN  and PSPRSMPERM tables contained any rows that needed to be updated.