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
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;
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>';
Step 3: Export the Project to file
Step 4: Import the Project back from File
Step 4: Confirm Objects Deleted
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
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.