So you are tasked with a maintenance pack install, or worse yet, a full blown upgrade.  You follow the upgrade procedures as best you can understand them.  You are at the step where you have run the compare reports, and are waiting for the developers to look over the reports.  Sure, they cursed at the fact that you wasted five hours of the print queue and half the trees Johnny Appleseed managed to plant, but the real fact is they have to shift through 1000’s of pages of compare reports that are meaningless due to their size.

What your developers need is a simple Excel list of all objects changed, which ones were custom work, and who the last developer was who touched the object.  This last point is crucial.  The work can be divided up quickly, if only we knew who last touched the object.  This becomes paramount when the system is shared by multiple development teams (as in HRMS and Campus Solutions).  Fortunately, it’s possible to write an SQL to give the developers what they need because the majority of App Designer objects have a field called “lastupdoprid” which holds the user who last saved that object in the current environment.  The delivered compare reports utilize this field as follows: if the userid is not equal to PPLSOFT, then the object has been updated by a customer site (and the asterisk will be used to indicate the delivered object has been modified).

The Compare Process

Before moving to the SQL, I want to describe the delivered compare process and at which point the SQL can be deployed.  This SQL will work for both upgrades and maintenance packs.  In both situations, you are performing similar tasks but at a different scale.  For the sake of both, I will refer to the two databases simply as the source and target.  The source will contain the new code being introduced and the target will be where customizations exist and could be overwritten by the new code.

Let’s pick up on the compare at the point where you have loaded the necessary projects into the source database, and you are being asked to run a compare report. As you run the compare reports, there are up to four things happening as the objects are being compared between the source and target (please use the comments if I missed more):
1.    The objects are being marked based on their comparison (New, Absent, Same, Change, etc.)
2.    The object’s actions are being suggested (Add, Copy, Delete, etc.), based partly on the Upgrade Options and if you selected Keep Vanilla or Keep Customizations.
3.    The compare reports are being created, based on the Report Options.
4.    If you are doing a database compare (instead of a project compare), a new project is being created to hold all items found in both systems.

To migrate the barebones project, open Application Designer for the source database, open the project that was used or created during the compare, and choose Tools, Migrate.  When asked, sign into the target database.  When the dialog box appears asking which objects need to be migrated, unselect all objects.  This is very critical. You may also need to mark the box for “Copy Project Definition”.  Now select Copy.  This migrates the project definition (but no objects) to the target system.

Running the SQL

The next process is to modify and run the SQL.  It is to be run in the target database as SYSADM or with permissions to the SYSADM tables .database.  The SQL will report against the project definition tables with the results of what the compare found.  In the SQL, we join some of the project items to their target counterpart and record the last userid who touched the objects.  We do this in an outer join, in case the object does not exist in the target yet.
The script should be run in the target system before the dba copies the actual objects over to target.  If the objects are already copied over to the target system, you won’t have accurate userids of who changed the objects last.  If this happens, one alternative is to migrate the project shell (no objects) to another system (such as Test) and run the SQL in Test.

This SQL Script was written for PeopleTools 8.46, but has not been tested on 8.48 or 8.49.

 -----------------------------------------------------------------------------------------------
-- Original Code written by David Vandiver
-- www.DavidVandiver.com
-- This script will find all project items for a project, plus give you the oprid
--    for the major types of objects.

-- Be sure to search/replace all 'PROJECT_X' with your project name.

-- Disclaimer:  There are no warranties or guarantees that this code will not harm your system,
--              erase your data, paint your house, or give you swine flu.  You are authorized
--              to review this SQL and determine the risks of running it on your system.
--              This SQL is not copyrighted; you may change, add, delete, and mutilate the code
--              to your liking.
-----------------------------------------------------------------------------------------------

SELECT Decode(a.objecttype,0,'Record',1,'Index',2,'Field',3,'Field Format',4,'Translate'
,5,'Page',6,'Menu',7,'Component',8,'Record PeopleCode',9,'Menu PeopleCode',10,'Query'
,11,'Tree Structure',12,'Tree',13,'Access Group',14,'Color',15,'Style',16,'Business Process Map'
,17,'Business Process',18,'Activity',19,'Role',20,'Process Definition',21,'Server Definition'
,22,'Process Type Definition',23,'Job Definition',24,'Recurrence Definition',25,'Message Catalog Entry'
,26,'Dimension',27,'Cube Definition',28,'Cube Instance Definition',29,'Business Interlink'
,30,'SQL Object',31,'File Layout Definition',32,'Component Interface',33,'Process Engine Program'
,34,'Process Engine Section',35,'Message Node',36,'Message Channel',37,'Message'
,38,'Approval Rule Set',39,'Message PeopleCode',40,'Subscription PeopleCode'
,41,'Channel PeopleCode',42,'Component Interface PeopleCode',43,'Application Engine PeopleCode'
,44,'Page PeopleCode',45,'Page Field PeopleCode',46,'Component PeopleCode'
,47,'Component Record PeopleCode',48,'Component Record Field PeopleCode',49,'Image'
,50,'Style Sheet',51,'HTML',52,'File Reference',53,'Permission List',54,'Portal Registry Definition'
,55,'Portal Registry Structure',56,'URL Definition',57,'Application Package'
,58,'Application Package PeopleCode',59,'n/a (59)',60,'Problem Type',61,'n/a (61)',62,'XSLT'
,63,'Portal Registry User Favorites',64,'Mobile Page',65,'Relationship') "Object Type",
OBJECTVALUE1, OBJECTVALUE2, OBJECTVALUE3, OBJECTVALUE4,
DECODE(A.SOURCESTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Source Status",
DECODE(A.TARGETSTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Target Status",
' ' "Owner", ' ' "Owner Full Name"
FROM PSPROJECTITEM A
WHERE A.PROJECTNAME = 'UHS_DB_COMPARE'
AND a.objecttype not in (0,5,7,8,44,2,6,30,4)
union
SELECT 'Record',
OBJECTVALUE1, OBJECTVALUE2, OBJECTVALUE3, OBJECTVALUE4,
DECODE(A.SOURCESTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Source Status",
DECODE(A.TARGETSTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Target Status",
b.lastupdoprid, c.oprdefndesc
FROM PSPROJECTITEM A, psrecdefn B, psoprdefn c
WHERE A.PROJECTNAME = 'PROJECT_X'
and a.objectvalue1 = b.recname (+)
AND a.objecttype = 0
and b.lastupdoprid = c.oprid (+)
union
SELECT 'Component',
OBJECTVALUE1, OBJECTVALUE2, OBJECTVALUE3, OBJECTVALUE4,
DECODE(A.SOURCESTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Source Status",
DECODE(A.TARGETSTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Target Status",
b.lastupdoprid, c.oprdefndesc
FROM PSPROJECTITEM A, pspnlgrpdefn B, psoprdefn c
WHERE A.PROJECTNAME = 'PROJECT_X'
and a.objectvalue1 = b.pnlgrpname (+)
AND a.objecttype = 7
and b.lastupdoprid = c.oprid (+)
union
SELECT 'Page',
OBJECTVALUE1, OBJECTVALUE2, OBJECTVALUE3, OBJECTVALUE4,
DECODE(A.SOURCESTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Source Status",
DECODE(A.TARGETSTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Target Status",
b.lastupdoprid, c.oprdefndesc
FROM PSPROJECTITEM A, pspnldefn B, psoprdefn c
WHERE A.PROJECTNAME = 'PROJECT_X'
and a.objectvalue1 = b.pnlname (+)
AND a.objecttype = 5
and b.lastupdoprid = c.oprid (+)
union
SELECT 'Record PCode',
A.OBJECTVALUE1, A.OBJECTVALUE2, A.OBJECTVALUE3, A.OBJECTVALUE4,
DECODE(A.SOURCESTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Source Status",
DECODE(A.TARGETSTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Target Status",
b.lastupdoprid, c.oprdefndesc
FROM PSPROJECTITEM A, pspcmprog B, psoprdefn c
WHERE A.PROJECTNAME = 'PROJECT_X'
and a.objectvalue1 = b.objectvalue1 (+)
and a.objectvalue2 = b.objectvalue2 (+)
and a.objectvalue3 = b.objectvalue3 (+)
AND a.objecttype = 8
and b.lastupdoprid = c.oprid (+)
union
SELECT 'Page PCode',
A.OBJECTVALUE1, A.OBJECTVALUE2, A.OBJECTVALUE3, A.OBJECTVALUE4,
DECODE(A.SOURCESTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Source Status",
DECODE(A.TARGETSTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Target Status",
b.lastupdoprid, c.oprdefndesc
FROM PSPROJECTITEM A, pspcmprog B, psoprdefn c
WHERE A.PROJECTNAME = 'PROJECT_X'
and a.objectvalue1 = b.objectvalue1 (+)
and a.objectvalue2 = b.objectvalue2 (+)
and a.objectvalue3 = b.objectvalue3 (+)
AND a.objecttype = 44
and b.lastupdoprid = c.oprid (+)
union
SELECT 'Fields',
A.OBJECTVALUE1, A.OBJECTVALUE2, A.OBJECTVALUE3, A.OBJECTVALUE4,
DECODE(A.SOURCESTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Source Status",
DECODE(A.TARGETSTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Target Status",
b.lastupdoprid, c.oprdefndesc
FROM PSPROJECTITEM A, psdbfield B, psoprdefn c
WHERE A.PROJECTNAME = 'PROJECT_X'
and a.objectvalue1 = b.fieldname (+)
AND a.objecttype = 2
and a.objectid2 = 0
and b.lastupdoprid = c.oprid (+)
union
SELECT 'Menu',
A.OBJECTVALUE1, A.OBJECTVALUE2, A.OBJECTVALUE3, A.OBJECTVALUE4,
DECODE(A.SOURCESTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Source Status",
DECODE(A.TARGETSTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Target Status",
b.lastupdoprid, c.oprdefndesc
FROM PSPROJECTITEM A, PSMENUDEFN B, psoprdefn c
WHERE A.PROJECTNAME = 'PROJECT_X'
and a.objectvalue1 = B.MENUNAME (+)
AND a.objecttype = 6
and b.lastupdoprid = c.oprid (+)
union
SELECT 'SQL Object',
A.OBJECTVALUE1, A.OBJECTVALUE2, A.OBJECTVALUE3, A.OBJECTVALUE4,
DECODE(A.SOURCESTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Source Status",
DECODE(A.TARGETSTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Target Status",
b.lastupdoprid, c.oprdefndesc
FROM PSPROJECTITEM A, PSSQLDEFN B, psoprdefn c
WHERE A.PROJECTNAME = 'PROJECT_X'
and a.objectvalue1 = B.SQLID (+)
and a.objectvalue2 = B.SQLTYPE (+)
AND a.objecttype = 30
and b.lastupdoprid = c.oprid (+)
UNION
SELECT 'Translate',
A.OBJECTVALUE1, A.OBJECTVALUE2, A.OBJECTVALUE3, A.OBJECTVALUE4,
DECODE(A.SOURCESTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Source Status",
DECODE(A.TARGETSTATUS,1,'Absent',2,'Changed',3,'Unchanged',4,'Custom Changed',5,'Custom Unchanged') "Target Status",
b.lastupdoprid, c.oprdefndesc
FROM PSPROJECTITEM A, psxlatitem B, psoprdefn c
WHERE A.PROJECTNAME = 'PROJECT_X'
and a.objectvalue1 = b.fieldname (+)
and a.objectvalue2 = b.fieldvalue (+)
AND a.objecttype = 4
and b.lastupdoprid = c.oprid (+)
;

--End of Script

Depending on where you run this SQL, you should be able to save the output into either a CSV file or straight into Excel.  I use PL/SQL Developer and the copy and paste work with Excel.

Once the data is in Excel, do a filter on the columns and search only for objects where the target is Custom.

Next Steps

At this point, I normally do not spend time reviewing the Excel sheet just yet; the objects have not been modified in the target so there is no work for the developer yet.  But the next step is important and is not always set right in the upgrade assistant.

The following should only apply if you did a full database compare but it does not hurt to follow these steps.  Go into the source system via the Application Designer.  Open the project used for the compare.  On the Upgrade tab, filter for which objects are marked for deletion.  These are objects that the compare found in the target system  but not in the source system.  These fall into one of two categories: vanilla code that has been eliminated or customized code that the client has added.  Assuming your site has a naming convention for any custom  objects, you should be able to scan the list of “action to Delete” and ensure that all customized code is not marked with the Upgrade Action.  You can highlight a large section and uncheck the “Upgrade Action”.  If you fail to check this step, your target database may be missing a lot of objects that developers have spent years creating, and you will need to restart the upgrade process again.  

You will need to review each type of object (Record, PeopleCode, Pages, etc.).  And don’t forget to hit Save often.  The project file can be large and if the computer freezes, you won’t have to retrace all of the objects you already unmarked for upgrade.  One further note of caution:  if the object is marked for Copy and the upgrade will override a modification made by one of your developers, you should always take the new PeopleSoft code coming from the source, and then retrofit the original modification back onto the new code (which will be in the target after the upgrade).  Also check to see if the upgrade has more than one project.  You can always search the PSPROJECTDEFN table by the date field to see newly updated projects.

You are now ready to continue the upgrade process via Upgrade Assistant.  Within a few more steps in the upgrade process, the project should be copied from the Source to the Target.

Once the upgrade process is complete and the target system is released to the developers, each developer will need to review the objects they own.  In some cases, PeopleSoft’s new code might solve a prior problem.  In other cases, your modification will need to be retrofited.  Any objects tied to a developer who is not longer on-site should be re-assigned to a current developer.

All re-implementations of modified code (records, pages, peoplecode, etc) should be manually put back into the target.  It is not good practice to bulk-copy delivered objects from another source to the new target for the sake of doing a retrofit.

If the developer needs the modified peoplecode (before the upgrade or maintenance pack wiped it out), they can usually go into another environment (like Test) to copy and paste the appropriate modification back to the new target.  If the code is not in Test, the developer can also review the Record Peoplecode compare report.  The developer can also create their own compare reports for their selected items (comparing the new target to either Test or Production), if the tools releases are compatible for the compare.

May you be blessed with good compare reports and a smooth upgrade or maintenance pack install.  Please add a comment if you implement this SQL at your site or if you find mistakes I have overlooked.

David Vandiver is an independent consultant in North America and specializes in Finance PeopleSoft and all things technical.