Have you ever needed to delete a project with all of its objects from an environment?  I don’t know if this necessarily something you would do in a real production environment, but maybe some of the thoughts will help someone out.

I wanted to get all of my little development projects out of this environment to return it back as close to a clean demo environment as possible.  Here’s what I did:

Step 1: Security

If your project contains any Roles or Permission Lists, you need to remove those from the User Profiles first.

I learned this the hard way. My user was connected to one of the roles that I deleted via the project. All of the sudden, I couldn’t get to anything inside PeopleSoft Online. I couldn’t log back into App Designer anymore. I had to remove the role from PSROLEUSER manually in the database before I could do anything.

You can use this query to check any roles:

SELECT ROLEUSER, ROLENAME
FROM PSROLEUSER
WHERE ROLENAME = '<role name>';

If it is a permission list, you may want to check this query:

SELECT R.ROLEUSER, R.ROLENAME, C.CLASSID
FROM PSROLEUSER R, PSROLECLASS C
WHERE C.CLASSID = '<permission list name>'
AND R.ROLENAME = C.ROLENAME;

Here’s where I am deleting the Role from my user:

Step 2: Check your project

Make sure you know what you are deleting! You can’t get these objects back unless you have a backup.

Click on the Upgrade tab and just go through all the folders to make sure you know what objects are included. You want to watch for any delivered objects that you had modified that you don’t really want deleted.

Step 3: Change the Upgrade Action

Now, you need to change the upgrade action for each of the objects. If this is a large project, you will want to do this from the database:

UPDATE PSPROJECTITEM
SET UPGRADEACTION = 1
WHERE PROJECTNAME = '<project name>';

You can see it from the Upgrade tab in the project:

Step 3: Export the Project to file

Use the menu Tools > Copy Project > To File… Make sure all the object types are selected, and key in the path to a temporary folder.

Step 4: Import the Project back from File

Use the menu Tools > Copy Project > From File … Browse to the same path where you save the project. Select that project and import it:

Make sure that you select to use the project definition from the File:

Again, select all the object types and hit copy:

Step 4: Confirm Objects Deleted

Now, try to open a few of the objects from the project. You should get a message that it doesn’t exist:

Step 5: Clean up the Database

Here is some SQL that should generate the Drop statements for all of the tables involved in the project. This is the Oracle version:

SELECT 'DROP TABLE ' || TABLE_NAME || ';'
FROM DBA_TABLES A, PSPROJECTITEM B
WHERE B.PROJECTNAME = '<project name>'
AND B.OBJECTTYPE = 0
AND 'PS_' || B.OBJECTVALUE1 = A.TABLE_NAME;

SELECT 'DROP VIEW ' || VIEW_NAME || ';'
FROM DBA_VIEWS A, PSPROJECTITEM B
WHERE B.PROJECTNAME = '<project name>'
AND B.OBJECTTYPE = 0
AND 'PS_' || B.OBJECTVALUE1 = A.VIEW_NAME;

This is the SQL Server version:

SELECT 'DROP TABLE ' + TABLE_NAME + ';'
FROM DBA_TABLES A, PSPROJECTITEM B
WHERE B.PROJECTNAME = '<project name>'
AND B.OBJECTTYPE = 0
AND 'PS_' + B.OBJECTVALUE1 = A.TABLE_NAME;

SELECT 'DROP VIEW ' + VIEW_NAME + ';'
FROM DBA_VIEWS A, PSPROJECTITEM B
WHERE B.PROJECTNAME = '<project name>'
AND B.OBJECTTYPE = 0
AND 'PS_' + B.OBJECTVALUE1 = A.VIEW_NAME;

Step 6: Delete the Project

Now, you are done with your list of definitions, and you can just remove the whole project using App Designer. Use the File > Delete … option.

Step 7: Check your system.

Run the DDDAudit and the SysAudit and make sure that you haven’t left anything broken. You can run those from PeopleTools > Process Scheduler > System Process Requests.