PeopleSoft: Cleaning Up After a Process Definition Mess

Have you ever created a PeopleSoft Process Definition that you later didn't want, or that you realized had the wrong name?  There's no easy way to fix it...until now.  ;-)



The easiest way to figure out what all the PeopleSoft process definition tables are named is to go to a website like this one:  http://www.go-faster.co.uk/peopletools/index.htm and search for "Process Definition". This search lets us know that "PS_PRCSDEFN" is the base table for process definitions.

Doing a describe on the PS_PRCSDEFN table tells us that PRCSNAME is the name of the column that holds the name of the process that we want to update or delete.

WARNING: The PRCSNAME by itself is not unique. You must combine it with PRCSTYPE to ensure that you are changing or deleting the process definition that you intend to change or delete.  In other words, for example, there is a very slight possibility that you could have a COBOL and an Application Engine by the same name.

However, for the rest of this blog article, I will assume that PRCSNAME is unique.

The next thing to do is find all of the records that contain PRCSNAME as a column.  We do this by writing:

select r.recname from sysadm.psrecfield f, sysadm.psrecdefn rwhere fieldname = 'PRCSNAME'and r.recname = f.recnameand r.rectype = 0;  /* tables only, no views or work records */

This will give us a long list of tables, a few of which are shown below:

...MASS_RPT_HR_LN

MASS_RPT_PT_LN

MASS_RPT_XX_LN

OSHA_300_RUNS

PA_RUN_CNTL 

PA_RUN_CNTL_CLC 

PMN_FILTER 

PRCSDEFN 

PRCSDEFNCNTDIST 

PRCSDEFNCOND 

PRCSDEFNGRP 

PRCSDEFNLANG 

PRCSDEFNMESSAGE...

Now that we are comfortable that we have the correct list of tables, we can modify our original statement so that it generates table-specific update or delete statements that we can use to clean up our mess. Assume that I have a PRCSNAME of 'y' that I want to update to 'x', I'll provide examples below of update statements for the partial list of tables that I listed above (remember that there are several more tables that are not in the above list that have PRCSNAME as a column).

But first, here's the select statement used to get the ready list of update statements.

select   'update sysadm.'   || r.recname   || ' set PRCSNAME = ''x'' where PRCSNAME = ''y'';'   from sysadm.psrecfield f, sysadm.psrecdefn r where fieldname = 'PRCSNAME'   and r.recname = f.recname   and r.rectype = 0;

And here is the partial list of update statements that are generated from the statement above.

update sysadm.MASS_RPT_HR_LN set PRCSNAME = 'x' where PRCSNAME = 'y'; 

update sysadm.MASS_RPT_PT_LN set PRCSNAME = 'x' where PRCSNAME = 'y'; 

update sysadm.MASS_RPT_XX_LN set PRCSNAME = 'x' where PRCSNAME = 'y'; 

update sysadm.OSHA_300_RUNS set PRCSNAME = 'x' where PRCSNAME = 'y'; 

update sysadm.PA_RUN_CNTL set PRCSNAME = 'x' where PRCSNAME = 'y'; 

update sysadm.PA_RUN_CNTL_CLC set PRCSNAME = 'x' where PRCSNAME = 'y'; 

update sysadm.PMN_FILTER set PRCSNAME = 'x' where PRCSNAME = 'y'; 

update sysadm.PRCSDEFN set PRCSNAME = 'x' where PRCSNAME = 'y'; 

update sysadm.PRCSDEFNCNTDIST set PRCSNAME = 'x' where PRCSNAME = 'y'; 

update sysadm.PRCSDEFNCOND set PRCSNAME = 'x' where PRCSNAME = 'y'; 

update sysadm.PRCSDEFNGRP set PRCSNAME = 'x' where PRCSNAME = 'y'; 

update sysadm.PRCSDEFNLANG set PRCSNAME = 'x' where PRCSNAME = 'y'; 

update sysadm.PRCSDEFNMESSAGE set PRCSNAME = 'x' where PRCSNAME = 'y';

You'll want to review the data before committing it to the database.  If something goes wrong, issue a rollback SQL command.   You'll very likely see the output "0 rows updated" for several of the above statements, but that's okay.  It's better to try to update and find that 0 rows are affected than to miss something that you thought you didn't need to do.