Compare Report Query for PeopleTools 8.12 or 8.19

No votes yet

In older version of PeopleTools like 8.12, there is only option to save the Compare Report file and i.e. '.prt' format.
For viewing '.prt' files, it needs separate viewer or editor.
Inorder to save the Compare Reports run in PT 8.12 in .xls or .csv format, follow below steps

1> Run Compare Reports normally from App Desinger by Navigating to Tools --> Upgrade --> Compare/Report. Enter the Target environment Name and Password.
2> Run Below Query in Source Environment after Compare Report process is successfully completed in App Designer. For example if you are comparing a project from DEV to TEST then run this query in DEV

SELECT DISTINCT (CASE WHEN PI.OBJECTTYPE=0 THEN 'Record'
WHEN PI.OBJECTTYPE=1 THEN 'Index'
WHEN PI.OBJECTTYPE=2 THEN 'Field'
WHEN PI.OBJECTTYPE=3 THEN 'Field Format'
WHEN PI.OBJECTTYPE=4 THEN 'Translate'
WHEN PI.OBJECTTYPE=5 THEN 'Page'
WHEN PI.OBJECTTYPE=6 THEN 'Menu'
WHEN PI.OBJECTTYPE=7 THEN 'Component'
WHEN PI.OBJECTTYPE=8 THEN 'Record PeopleCode'
WHEN PI.OBJECTTYPE=9 THEN 'Menu PeopleCode'
WHEN PI.OBJECTTYPE=10 THEN 'Query'
WHEN PI.OBJECTTYPE=11 THEN 'Tree Structures'
WHEN PI.OBJECTTYPE=12 THEN 'Trees'
WHEN PI.OBJECTTYPE=13 THEN 'Access Groups'
WHEN PI.OBJECTTYPE=14 THEN 'Colors'
WHEN PI.OBJECTTYPE=15 THEN 'Styles'
WHEN PI.OBJECTTYPE=17 THEN 'Business Process'
WHEN PI.OBJECTTYPE=18 THEN 'Activity'
WHEN PI.OBJECTTYPE=19 THEN 'Roles'
WHEN PI.OBJECTTYPE=20 THEN 'Process Definitions'
WHEN PI.OBJECTTYPE=21 THEN 'Server Definitions'
WHEN PI.OBJECTTYPE=22 THEN 'Process Type Definitions'
WHEN PI.OBJECTTYPE=23 THEN 'Job Definitions'
WHEN PI.OBJECTTYPE=24 THEN 'Recurrence Definitions'
WHEN PI.OBJECTTYPE=25 THEN 'Message Catalog Entry'
WHEN PI.OBJECTTYPE=29 THEN 'Business Interlink'
WHEN PI.OBJECTTYPE=30 THEN 'SQL'
WHEN PI.OBJECTTYPE=31 THEN 'File Layout Definitions'
WHEN PI.OBJECTTYPE=32 THEN 'Component Interface'
WHEN PI.OBJECTTYPE=33 THEN 'Application Engine Programs'
WHEN PI.OBJECTTYPE=34 THEN 'Application Engine Sections'
WHEN PI.OBJECTTYPE=35 THEN 'Message Nodes'
WHEN PI.OBJECTTYPE=36 THEN 'Message Channels'
WHEN PI.OBJECTTYPE=37 THEN 'Messages'
WHEN PI.OBJECTTYPE=38 THEN 'Approval Rule Sets'
WHEN PI.OBJECTTYPE=39 THEN 'Message PeopleCode'
WHEN PI.OBJECTTYPE=40 THEN 'Subscription PeopleCode'
WHEN PI.OBJECTTYPE=42 THEN 'Comp. Interface PeopleCode'
WHEN PI.OBJECTTYPE=43 THEN 'Application Engine PeopleCode'
WHEN PI.OBJECTTYPE=44 THEN 'Page PeopleCode'
WHEN PI.OBJECTTYPE=46 THEN 'Component PeopleCode'
WHEN PI.OBJECTTYPE=47 THEN 'Component Record PeopleCode'
WHEN PI.OBJECTTYPE=48 THEN 'Component Rec Fld PeopleCode'
WHEN PI.OBJECTTYPE=49 THEN 'Images'
WHEN PI.OBJECTTYPE=50 THEN 'Style Sheets'
WHEN PI.OBJECTTYPE=51 THEN 'HTML'
WHEN PI.OBJECTTYPE=53 THEN 'Permission Lists'
WHEN PI.OBJECTTYPE=54 THEN 'Portal Registry Definitions'
WHEN PI.OBJECTTYPE=55 THEN 'Portal Registry Structures'
WHEN PI.OBJECTTYPE=56 THEN 'URL Definitions'
WHEN PI.OBJECTTYPE=57 THEN 'Application Packages'
WHEN PI.OBJECTTYPE=58 THEN 'Application Package PeopleCode'
WHEN PI.OBJECTTYPE=60 THEN 'Analytic Types'
WHEN PI.OBJECTTYPE=61 THEN 'Archive Templates'
WHEN PI.OBJECTTYPE=62 THEN 'XSLT'
WHEN PI.OBJECTTYPE=64 THEN 'Mobile Pages'
WHEN PI.OBJECTTYPE=65 THEN 'Relationships'
WHEN PI.OBJECTTYPE=66 THEN 'CI Property PeopleCode'
WHEN PI.OBJECTTYPE=67 THEN 'Optimization Models'
WHEN PI.OBJECTTYPE=68 THEN 'File References'
WHEN PI.OBJECTTYPE=70 THEN 'Archive Object Definitions'
WHEN PI.OBJECTTYPE=71 THEN 'Archive Templates (Type 2)'
WHEN PI.OBJECTTYPE=72 THEN 'Diagnostic Plug-Ins'
WHEN PI.OBJECTTYPE=73 THEN 'Analytic Models'
WHEN PI.OBJECTTYPE=76 THEN 'WSRP Remote Producers'
WHEN PI.OBJECTTYPE=77 THEN 'WSRP Remote Portlets'
WHEN PI.OBJECTTYPE=78 THEN 'WSRP Cloned Portlet Handles'
ELSE 'Unknown'
END) AS "Object Type"
, PI.OBJECTVALUE1
, PI.OBJECTVALUE2
,(CASE WHEN PI.SOURCESTATUS = 0 THEN 'Unknown'
WHEN PI.SOURCESTATUS = 1 THEN 'Absent'
WHEN PI.SOURCESTATUS = 2 THEN 'Changed'
WHEN PI.SOURCESTATUS = 3 THEN 'Unchanged'
WHEN PI.SOURCESTATUS = 4 THEN '*Changed'
WHEN PI.SOURCESTATUS = 5 THEN '*Unchanged'
WHEN PI.SOURCESTATUS = 6 THEN 'Same'
ELSE 'Invalid Value'
END) AS "Source Status"
,(CASE WHEN PI.TARGETSTATUS = 0 THEN 'Unknown'
WHEN PI.TARGETSTATUS = 1 THEN 'Absent'
WHEN PI.TARGETSTATUS = 2 THEN 'Changed'
WHEN PI.TARGETSTATUS = 3 THEN 'Unchanged'
WHEN PI.TARGETSTATUS = 4 THEN '*Changed'
WHEN PI.TARGETSTATUS = 5 THEN '*Unchanged'
WHEN PI.TARGETSTATUS = 6 THEN 'Same'
ELSE 'Invalid Value '
END) AS "Target Status"
, (CASE WHEN PI.UPGRADEACTION = 0 THEN 'Copy'
WHEN PI.UPGRADEACTION = 1 THEN 'Delete'
WHEN PI.UPGRADEACTION = 2 THEN 'None'
WHEN PI.UPGRADEACTION = 3 THEN 'CopyProp'
ELSE 'Invalid Value '
END) AS "Upgrade Action"
, (CASE WHEN PI.TAKEACTION = 0 THEN 'No'
WHEN PI.TAKEACTION = 1 THEN 'Yes'
ELSE 'Invalid Value '
END) AS "Take Action"
FROM PSPROJECTITEM PI
WHERE PI.PROJECTNAME = 'PS_TEST_PROJECT' -- ==> Project Name
--AND PI.SOURCESTATUS IN (4,5)
ORDER BY 1,2

3> Save the output of the above query in .xls or .csv format.

This will give the result of what objects in projects are customized or newly added by comparing between two environments.

Please make sure you run the Compare Reports first through App Designer and then you run this query, or else the query will not give correct result.

Thanks,
PS_Tatva

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!