The sql will identify duplicate objects in different application designer projects to eliminate duplicate work by developers.
You will find the SQL very handy during the analysis phase of a peoplesoft upgrade project. You 1st need to identify your projects and run compare and report through application designer. This will flag objects in the source database that are different than the target database to be marked for upgrade.
Now run the SQL below, if you have rows coming back then you have objects that exists in more than on project. You will need to examine the MIN(PROKECTNAME) AND MAX(PROJECTNAME) columns and decide in which project to keep the duplicate object. To take an object off a project, just navigate to the "upgrade" tab, uncheck the upgrade flag and save. Re-run the sql and your object will not show as duplicate any more.
WHEN 0
THEN 'Record'
WHEN 1
THEN 'Index'
WHEN 2
THEN 'Field'
WHEN 3
THEN 'Field Format'
WHEN 4
THEN 'Translate Value'
WHEN 5
THEN 'Pages'
WHEN 6
THEN 'Menus'
WHEN 7
THEN 'Components'
WHEN 8
THEN 'Record PeopleCode'
WHEN 9
THEN 'Menu PeopleCode'
WHEN 10
THEN 'Query'
WHEN 11
THEN 'Tree Structures'
WHEN 12
THEN 'Trees'
WHEN 13
THEN 'Access group'
WHEN 14
THEN 'Color'
WHEN 15
THEN 'Style'
WHEN 16
THEN 'N/A'
WHEN 17
THEN 'Business process'
WHEN 18
THEN 'Activity'
WHEN 19
THEN 'Role'
WHEN 20
THEN 'Process Definition'
WHEN 21
THEN 'Server Definition'
WHEN 22
THEN 'Process Type Definition'
WHEN 23
THEN 'Job Definitions'
WHEN 24
THEN 'Recurrence Definition'
WHEN 25
THEN 'Message Catalog'
WHEN 26
THEN 'Dimension'
WHEN 27
THEN 'Cube Definitions'
WHEN 28
THEN 'Cube Instance Definitions'
WHEN 29
THEN 'Business Interlink'
WHEN 30
THEN 'SQL'
WHEN 31
THEN 'File Layout Definition'
WHEN 32
THEN 'Component Interfaces'
WHEN 33
THEN 'AE program'
WHEN 34
THEN 'AE section'
WHEN 35
THEN 'Message Node'
WHEN 36
THEN 'Message Channel'
WHEN 37
THEN 'Message'
WHEN 38
THEN 'Approval rule set'
WHEN 39
THEN 'Message PeopleCode'
WHEN 40
THEN 'Subscription PeopleCode'
WHEN 41
THEN 'N/A'
WHEN 42
THEN 'Component Interface PeopleCode'
WHEN 43
THEN 'AE PeopleCode'
WHEN 44
THEN 'Page PeopleCode'
WHEN 45
THEN 'Page Field PeopleCode'
WHEN 46
THEN 'Component PeopleCode'
WHEN 47
THEN 'Component Record PeopleCode'
WHEN 48
THEN 'Component Rec Fld PeopleCode'
WHEN 49
THEN 'Image'
WHEN 50
THEN 'Style sheet'
WHEN 51
THEN 'HTML'
WHEN 52
THEN 'Not used'
WHEN 53
THEN 'Permission List'
WHEN 54
THEN 'Portal Registry Definitions'
WHEN 55
THEN 'Portal Registry Structures'
WHEN 56
THEN 'URL Definitions'
WHEN 57
THEN 'Application Packages'
WHEN 58
THEN 'Application Package Peoplecode'
WHEN 59
THEN 'Portal Registry User Homepage'
WHEN 60
THEN 'Problem Type'
WHEN 61
THEN 'Archive Templates'
WHEN 62
THEN 'XSLT'
WHEN 63
THEN 'Portal Registry User Favorite'
WHEN 64
THEN 'Mobile Page'
WHEN 65
THEN 'Relationships'
WHEN 66
THEN 'Component Interface Property Peoplecode'
WHEN 67
THEN 'Optimization Models'
WHEN 68
THEN 'File References'
WHEN 69
THEN 'File Type Codes'
WHEN 70
THEN 'Archive Object Definitions'
WHEN 71
THEN 'Archive Templates (Type 2)'
WHEN 72
THEN 'Diagnostic Plug In'
WHEN 73
THEN 'Analytic Model'
ELSE 'UNKNOWN OBJECT TYPE'
END
) AS objecttype,
MIN (projectname ), MAX (projectname ), objectvalue1, objectvalue2,
objectvalue3, objectvalue4
FROM psprojectitem
WHERE projectname LIKE 'projectname_%'
-- or you can use projectname in ('project1', 'project2', 'project3', 'project4'
AND takeaction = '1'
GROUP BY objecttype, objectvalue1, objectvalue2, objectvalue3, objectvalue4
HAVING COUNT (* ) > 1
ORDER BY objecttype, objectvalue1, objectvalue2, objectvalue3, objectvalue4;