Get Online Objects Associated with PeopleSoft Project

Below SQL could help you to get all the online objects present in any PeopleSoft Project. This SQL could be used to get list of object during creation of Technical design document even it can be used for technical keep-drop during PeopleSoft Upgrade.

SELECT
PROJECTNAME,
(CASE
WHEN OBJECTTYPE = 0 THEN 'Record'
WHEN OBJECTTYPE = 1 THEN 'Indexes'
WHEN OBJECTTYPE = 2 THEN 'Fields'
WHEN OBJECTTYPE = 3 THEN 'Field Formats'
WHEN OBJECTTYPE = 4 THEN 'Translates Values'
WHEN OBJECTTYPE = 5 THEN 'Pages'
WHEN OBJECTTYPE = 6 THEN 'Menus'
WHEN OBJECTTYPE = 7 THEN 'Components'
WHEN OBJECTTYPE = 8 THEN 'Record PeopleCode'
WHEN OBJECTTYPE = 9 THEN 'Menu PeopleCode'
WHEN OBJECTTYPE = 10 THEN 'Queries'
WHEN OBJECTTYPE = 11 THEN 'Tree Structures'
WHEN OBJECTTYPE = 12 THEN 'Trees'
WHEN OBJECTTYPE = 13 THEN 'Access Groups'
WHEN OBJECTTYPE = 14 THEN 'Colours'
WHEN OBJECTTYPE = 15 THEN 'Styles'
WHEN OBJECTTYPE = 17 THEN 'Business Processes'
WHEN OBJECTTYPE = 18 THEN 'Activities'
WHEN OBJECTTYPE = 19 THEN 'Roles'
WHEN OBJECTTYPE = 20 THEN 'Process Definitions'
WHEN OBJECTTYPE = 21 THEN 'Server Definitions'
WHEN OBJECTTYPE = 22 THEN 'Process Type Definitions'
WHEN OBJECTTYPE = 23 THEN 'Job Definitions'
WHEN OBJECTTYPE = 24 THEN 'Recurrence Definitions'
WHEN OBJECTTYPE = 25 THEN 'Message Catalog Entries'
WHEN OBJECTTYPE = 26 THEN 'Dimension Definition'
WHEN OBJECTTYPE = 27 THEN 'Cube Definition'
WHEN OBJECTTYPE = 28 THEN 'Cube Instance Definition'
WHEN OBJECTTYPE = 29 THEN 'Business Interlink'
WHEN OBJECTTYPE = 30 THEN 'SQL'
WHEN OBJECTTYPE = 31 THEN 'File Layout Definitions'
WHEN OBJECTTYPE = 32 THEN 'Component Interfaces'
WHEN OBJECTTYPE = 33 THEN 'Application Engine Programs'
WHEN OBJECTTYPE = 34 THEN 'Application Engine Sections'
WHEN OBJECTTYPE = 35 THEN 'Message Nodes'
WHEN OBJECTTYPE = 36 THEN 'Message Channels'
WHEN OBJECTTYPE = 37 THEN 'Message Definitions'
WHEN OBJECTTYPE = 38 THEN 'Approval Rule Set'
WHEN OBJECTTYPE = 39 THEN 'Message PeopleCode'
WHEN OBJECTTYPE = 40 THEN 'Subscription PeopleCode'
WHEN OBJECTTYPE = 42 THEN 'Comp. Interface PeopleCode'
WHEN OBJECTTYPE = 43 THEN 'Application Engine PeopleCode'
WHEN OBJECTTYPE = 44 THEN 'Page PeopleCode'
WHEN OBJECTTYPE = 45 THEN 'Page Field PeopleCode'
WHEN OBJECTTYPE = 46 THEN 'Component PeopleCode'
WHEN OBJECTTYPE = 47 THEN 'Component Record PeopleCode'
WHEN OBJECTTYPE = 48 THEN 'Component Rec Fld PeopleCode'
WHEN OBJECTTYPE = 49 THEN 'Images'
WHEN OBJECTTYPE = 50 THEN 'Style Sheets'
WHEN OBJECTTYPE = 51 THEN 'HTML Object(s)'
WHEN OBJECTTYPE = 52 THEN 'File References'
WHEN OBJECTTYPE = 53 THEN 'Permission Lists'
WHEN OBJECTTYPE = 54 THEN 'Portal Registry Definitions'
WHEN OBJECTTYPE = 55 THEN 'Portal Registry Structures'
WHEN OBJECTTYPE = 56 THEN 'URL Definitions'
WHEN OBJECTTYPE = 57 THEN 'Application Packages'
WHEN OBJECTTYPE = 58 THEN 'Application Package PeopleCode'
WHEN OBJECTTYPE = 59 THEN 'Portal Registry User Home Page'
WHEN OBJECTTYPE = 60 THEN 'Analytic Types'
WHEN OBJECTTYPE = 62 THEN 'XSLT'
WHEN OBJECTTYPE = 63 THEN 'Portal Registry Related'
WHEN OBJECTTYPE = 64 THEN 'Mobile Pages'
WHEN OBJECTTYPE = 68 THEN 'File References'
WHEN OBJECTTYPE = 69 THEN 'File Type Codes'
WHEN OBJECTTYPE = 72 THEN 'Dignostic Plug Ins'
WHEN OBJECTTYPE = 73 THEN 'Analytic Models'
WHEN OBJECTTYPE = 79 THEN 'Service'
WHEN OBJECTTYPE = 80 THEN 'Service Operation'
WHEN OBJECTTYPE = 81 THEN 'Service Operation Handler'
WHEN OBJECTTYPE = 82 THEN 'Service Operation Version'
WHEN OBJECTTYPE = 83 THEN 'Service Operation Routing'
WHEN OBJECTTYPE = 84 THEN 'IB Queues'
WHEN OBJECTTYPE = 85 THEN 'XLMP Template Definition'
WHEN OBJECTTYPE = 86 THEN 'XLMP Report Definition'
WHEN OBJECTTYPE = 87 THEN 'XMLP File Definition'
WHEN OBJECTTYPE = 88 THEN 'XMPL Data Source Definition'

ELSE CAST(OBJECTTYPE AS CHAR)
END) OBJECTTYPE,

OBJECTVALUE1,
(CASE
WHEN OBJECTID2= 0 THEN 'BLANK'
WHEN OBJECTID2= 2 THEN 'FIELDNAME'
WHEN OBJECTID2= 4 THEN 'BARNAME'
WHEN OBJECTID2= 12 THEN 'PROGRAMTYPE'
WHEN OBJECTID2= 16 THEN 'LANGUAGE CODE'
WHEN OBJECTID2= 21 THEN 'EFFDT'
WHEN OBJECTID2= 24 THEN 'INDEXID'
WHEN OBJECTID2= 25 THEN 'OPRID'
WHEN OBJECTID2= 28 THEN 'PROCESSNAME'
WHEN OBJECTID2= 39 THEN 'MARKETNAME'
WHEN OBJECTID2= 67 THEN 'FIELDNAME'
WHEN OBJECTID2= 77 THEN 'SECTION'
WHEN OBJECTID2= 81 THEN 'SQLTYPE'
WHEN OBJECTID2= 87 THEN 'SUBSCRIPTIONS'
WHEN OBJECTID2= 100 THEN 'TYPE'
WHEN OBJECTID2= 102 THEN 'LABELNAME'
END) OBJECTID2,

OBJECTVALUE2,
(CASE
WHEN OBJECTID3= 5 THEN 'ITEMNAME'
WHEN OBJECTID3= 12 THEN 'PROGRAMTYPE'
WHEN OBJECTID3= 22 THEN 'FIELDVALUE'
WHEN OBJECTID3= 101 THEN 'OBJECTNAME'
END) OBJECTID3,

OBJECTVALUE3,
(CASE
WHEN OBJECTID4= 2 THEN 'FIELDNAME'
WHEN OBJECTID4= 12 THEN 'PROGRAMTYPE'
WHEN OBJECTID4= 21 THEN 'EFFDT'
END) OBJECTID4,

OBJECTVALUE4,
(CASE
WHEN SOURCESTATUS= 1 THEN 'ABSENT'
WHEN SOURCESTATUS= 2 THEN 'CHANGED'
WHEN SOURCESTATUS= 3 THEN 'UNCHANGED'
WHEN SOURCESTATUS= 4 THEN 'CHANGED*'
WHEN SOURCESTATUS= 5 THEN 'UNCHANGED*'
WHEN SOURCESTATUS = 6 THEN 'SAME'
END) SOURCESTATUS,

(CASE
WHEN TARGETSTATUS= 1 THEN 'ABSENT'
WHEN TARGETSTATUS= 2 THEN 'CHANGED'
WHEN TARGETSTATUS= 3 THEN 'UNCHANGED'
WHEN TARGETSTATUS= 4 THEN 'CHANGED*'
WHEN TARGETSTATUS= 5 THEN 'UNCHANGED*'
WHEN TARGETSTATUS = 6 THEN 'SAME'
END) TARGETSTATUS,

(CASE
WHEN UPGRADEACTION= 0 THEN 'COPY'
WHEN UPGRADEACTION= 1 THEN 'DELETE'
WHEN UPGRADEACTION= 3 THEN 'COPYPROPERTIES'
END) UPGRADEACTION,


(CASE
WHEN TAKEACTION= 0 THEN 'NO'
WHEN TAKEACTION= 1 THEN 'YES'
END) TAKEACTION
FROM PSPROJECTITEM WHERE PROJECTNAME = 'M_LIB'