Extract Project Objects Using an SQL

Your rating: None Average: 4.1 (14 votes)

There have been times where I needed to document objects included in an Application Designer. The process could get very time consuming if you have a project with hundreds of PeopleSoft objects in it. PeopleSoft doesn't provide a "copy paste" type of approach to ease things, so I came up with my own!

The query below will return all the objects inside of an Application Designer project. You can then copy the rows and paste them where ever you'd like.

SELECT (CASE OBJECTTYPE 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
, OBJECTVALUE1
, OBJECTVALUE2
, OBJECTVALUE3
, OBJECTVALUE4
FROM PSPROJECTITEM
  WHERE PROJECTNAME = 'Your_Project' --- Replace w/ your project name
  ORDER BY OBJECTTYPE, OBJECTVALUE1, OBJECTVALUE2, OBJECTVALUE3;

If you would like to extract objects that are only marked for migration then add the following into the "Where" clause:

AND TAKEACTION = '1'
-- 1 means checked for upgrade
-- 0 means do not upgrade

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!

Comments

Have a question? Please ask it on the forum instead.
Manish Jain's picture
Manish Jain (not verified)
Thanks a lot

Hi, Thanks a ton. This is a wonderful query. Solved my purpose fully. Thanks again. Keep up the good work.

Manish Jain

Tim's picture
Tim (not verified)
Enhanced Version

Used your code as a base to enhance a need I had. We are implementing several Maintenance Pack updates (we backed up a bit on these). The compare report listed over 6500 items (a merged project of four). We wanted to reduce looking through all of them to only ones we were most concerned with. Those we have customized and those objects PeopleSoft was deleting. Nested decode was used to futher detail some object descriptions. Not sure but decode may be specific to ORACLE.

Below are the two I used.

Tim

--******************************************************
--  This selects those objects in a PeopleSoft project compare report
--  that show the object has a customization by the application owner.
--
--  Insert the projectname in the where clause below.  This would be
--  run in the source database environment.
--
--  The decode feature may be non standard and therefore specific to
--  ORACLE.
--******************************************************
SELECT   projectname,
         DECODE (objecttype,
                 '0', DECODE (objectvalue2,
                              ' ', 'Record',
                              'Record - Field On Record'
                             ),
                 '1', 'Index',
                 '2', DECODE (objectvalue2, ' ', 'Field', 'Field - Label ID'),
                 '3', 'Field Format',
                 '4', 'Translate Value',
                 '5', 'Page',
                 '6', 'Menu',
                 '7', 'Component',
                 '8', 'PeopleCode (Record)',
                 '9', 'PeopleCode (Menu)',
                 '10', 'Query',
                 '11', 'Tree Structure',
                 '12', 'Tree',
                 '13', 'Access Group',
                 '14', 'Color',
                 '15', 'Style',
                 '16', 'Not Defined',
                 '17', 'Business Process',
                 '18', 'Activity Definition',
                 '19', 'Role',
                 '20', 'Process Definition',
                 '21', 'Server Definition',
                 '22', 'Process Type Definition',
                 '23', 'Job Definition',
                 '24', 'Recurrence Definition',
                 '25', 'Message Catalog',
                 '26', 'Dimension',
                 '27', 'Cube Definition',
                 '28', 'Cube Instance Definition',
                 '29', 'Business Interlink',
                 '30', DECODE (objectvalue2,
                               '0', 'SQL - Definition',
                               '1', 'SQL - AE (Section and Step)',
                               '2', 'SQL - View',
                               'Other'
                              ),
                 '31', 'File Layout Definition',
                 '32', 'Component Interface',
                 '33', 'AE Program',
                 '34', 'AE Program and Section',
                 '35', 'Message Node',
                 '36', 'Message Channel',
                 '37', 'Message',
                 '38', 'Approval Rule Set',
                 '39', 'PeopleCode (Message)',
                 '40', 'PeopleCode (Subscription)',
                 '41', 'Not Defined',
                 '42', 'PeopleCode (Component Interface)',
                 '43', 'PeopleCode (Application Engine)',
                 '44', 'PeopleCode (Page)',
                 '45', 'PeopleCode (Page Field)',
                 '46', 'PeopleCode (Component)',
                 '47', 'PeopleCode (Component Record)',
                 '48', 'PeopleCode (Component Record Field)',
                 '49', 'Image',
                 '50', 'Style Sheet',
                 '51', 'HTML',
                 '52', 'Not Defined',
                 '53', 'Permission List',
                 '54', 'Portal Registry Definition',
                 '55', 'Portal Registry Structure',
                 '56', 'URL Definition',
                 '57', 'Application Package',
                 '58', 'PeopleCode (Application Package)',
                 '59', 'Portal Registry User HomePage',
                 '60', 'Problem Type',
                 '61', 'Archive Template',
                 '62', 'XSLT',
                 '63', 'Portal Registry User Favorite',
                 '64', 'Mobile Page',
                 '65', 'Relationships',
                 '66', 'PeopleCode (Component Interface Property)',
                 '67', 'Optimization Model',
                 '68', 'File Reference',
                 '69', 'File Type Code',
                 '70', 'Archive Object Definition',
                 '71', 'Archive Template (Type 2)',
                 '72', 'Diagnostic Plug In',
                 '73', 'Analytic Model',
                 objecttype
                ) objecttype,
         objectvalue1, objectvalue2, objectvalue3, objectvalue4,
         DECODE (sourcestatus,
                 '0', 'Unknown',
                 '1', 'Absent',
                 '2', 'Changed',
                 '3', 'Unchanged',
                 '4', '*Changed',
                 '5', '*Unchanged',
                 '6', 'Same'
                ) sourcestatus,
         DECODE (targetstatus,
                 '0', 'Unknown',
                 '1', 'Absent',
                 '2', 'Changed',
                 '3', 'Unchanged',
                 '4', '*Changed',
                 '5', '*Unchanged',
                 '6', 'Same'
                ) targetstatus,
         DECODE (upgradeaction,
                 '0', 'Copy',
                 '1', 'Delete',
                 '2', 'None',
                 '3', 'Copy Properties'
                ) upgradeaction,
         DECODE (takeaction, '0', 'None', '1', 'Copy') takeaction, copydone
    FROM sysadm.psprojectitem
   WHERE projectname = 'PROJECT NAME HERE'
     AND (targetstatus IN ('4', '5') OR sourcestatus IN ('4', '5'))
ORDER BY objecttype, objectvalue1, objectvalue2, objectvalue3, objectvalue4;

--***********************************************************
--  This select will provide all objects in the compare as defined for deletion.
--  Insert the projectname in the where clause below.  This would be run in
--  the source database environment.
--  The decode feature may be non standard and therefore specific to ORACLE.  
--*************************************************************
SELECT   projectname,
         DECODE (objecttype,
                 '0', DECODE (objectvalue2,
                              ' ', 'Delete Record',
                              'Delete Field From Record'
                             ),
                 '1', 'Index',
                 '2', DECODE (objectvalue2,
                              ' ', 'Delete Field From PeopleSoft',
                              'Delete Field Label ID'
                             ),
                 '3', 'Field Format',
                 '4', 'Translate Value',
                 '5', 'Page',
                 '6', 'Menu',
                 '7', 'Component',
                 '8', 'PeopleCode (Record)',
                 '9', 'PeopleCode (Menu)',
                 '10', 'Query',
                 '11', 'Tree Structure',
                 '12', 'Tree',
                 '13', 'Access Group',
                 '14', 'Color',
                 '15', 'Style',
                 '16', 'Not Used',
                 '17', 'Business Process',
                 '18', 'Activity Definition',
                 '19', 'Role',
                 '20', 'Process Definition',
                 '21', 'Server Definition',
                 '22', 'Process Type Definition',
                 '23', 'Job Definition',
                 '24', 'Recurrence Definition',
                 '25', 'Message Catalog',
                 '26', 'Dimension',
                 '27', 'Cube Definition',
                 '28', 'Cube Instance Definition',
                 '29', 'Business Interlink',
                 '30', DECODE (objectvalue2,
                               '0', 'SQL - Definition',
                               '1', 'SQL - AE (Section and Step)',
                               '2', 'SQL - View',
                               'Other'
                              ),
                 '31', 'File Layout Definition',
                 '32', 'Component Interface',
                 '33', 'AE Program',
                 '34', 'AE Program and Section',
                 '35', 'Message Node',
                 '36', 'Message Channel',
                 '37', 'Message',
                 '38', 'Approval Rule Set',
                 '39', 'PeopleCode (Message)',
                 '40', 'PeopleCode (Subscription)',
                 '41', 'Not Used',
                 '42', 'PeopleCode (Component Interface)',
                 '43', 'PeopleCode (Application Engine)',
                 '44', 'PeopleCode (Page)',
                 '45', 'PeopleCode (Page Field)',
                 '46', 'PeopleCode (Component)',
                 '47', 'PeopleCode (Component Record)',
                 '48', 'PeopleCode (Component Record Field)',
                 '49', 'Image',
                 '50', 'Style Sheet',
                 '51', 'HTML',
                 '52', 'Not Used',
                 '53', 'Permission List',
                 '54', 'Portal Registry Definition',
                 '55', 'Portal Registry Structure',
                 '56', 'URL Definition',
                 '57', 'Application Package',
                 '58', 'PeopleCode (Application Package)',
                 '59', 'Portal Registry User HomePage',
                 '60', 'Problem Type',
                 '61', 'Archive Template',
                 '62', 'XSLT',
                 '63', 'Portal Registry User Favorite',
                 '64', 'Mobile Page',
                 '65', 'Relationships',
                 '66', 'PeopleCode (Component Interface Property)',
                 '67', 'Optimization Model',
                 '68', 'File Reference',
                 '69', 'File Type Code',
                 '70', 'Archive Object Definition',
                 '71', 'Archive Template (Type 2)',
                 '72', 'Diagnostic Plug In',
                 '73', 'Analytic Model',
                 objecttype
                ) objecttype,
         objectvalue1, objectvalue2, objectvalue3, objectvalue4,
         DECODE (sourcestatus,
                 '0', 'Unknown',
                 '1', 'Absent',
                 '2', 'Changed',
                 '3', 'Unchanged',
                 '4', '*Changed',
                 '5', '*Unchanged',
                 '6', 'Same'
                ) sourcestatus,
         DECODE (targetstatus,
                 '0', 'Unknown',
                 '1', 'Absent',
                 '2', 'Changed',
                 '3', 'Unchanged',
                 '4', '*Changed',
                 '5', '*Unchanged',
                 '6', 'Same'
                ) targetstatus,
         DECODE (upgradeaction,
                 '0', 'Copy',
                 '1', 'Delete',
                 '2', 'None',
                 '3', 'Copy Properties'
                ) upgradeaction,
         DECODE (takeaction, '0', 'None', '1', 'Copy') takeaction, copydone
    FROM sysadm.psprojectitem
   WHERE projectname = 'PROJECT NAME HERE' AND upgradeaction = '1'
ORDER BY objecttype, objectvalue1, objectvalue2, objectvalue3, objectvalue4;

CompShack's picture
User offline. Last seen 4 years 46 weeks ago. Offline
Joined: 12/09/2007
Posts: 167
Wonderful Tim – Thanks!

Thanks for posting the "Enhanced" SQLs back on the site :) - This is great, I'm sure I will be using them. One note to add here is that for the SQLs to return rows, the Compare and Report should be done on your projects before hand.

Note: Tim, I have sent you an email, did you get it?

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

oddsnends's picture
User offline. Last seen 9 years 43 weeks ago. Offline
Joined: 03/28/2008
Posts: 16
Did get it Thanks

Thank you for the space. I will be using this site frequently posting many, hopefully useful contributions. There are some talented people out here.

Tim

Shivakumar's picture
Shivakumar (not verified)
Re: Extract Project Objects Using an SQL

Thnks dude for the wonderful query . this is absolutely a great piece of work....

I LOVE YOU

Guest's picture
Guest (not verified)
A few tweaks for an upgrade

SELECT (CASE OBJECTTYPE 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
, OBJECTVALUE1
, OBJECTVALUE2
, OBJECTVALUE3
, OBJECTVALUE4
, (CASE SOURCESTATUS
WHEN 0 THEN 'Unknown'
WHEN 1 THEN 'Absent'
WHEN 2 THEN 'Changed'
WHEN 3 THEN 'Unchanged'
WHEN 4 THEN '*Changed'
WHEN 5 THEN '*Unchanged'
WHEN 6 THEN 'Same' END) AS SOURCESTATUS
, (CASE TARGETSTATUS
WHEN 0 THEN 'Unknown'
WHEN 1 THEN 'Absent'
WHEN 2 THEN 'Changed'
WHEN 3 THEN 'Unchanged'
WHEN 4 THEN '*Changed'
WHEN 5 THEN '*Unchanged'
WHEN 6 THEN 'Same' END) AS TARGETSTATUS
, (CASE UPGRADEACTION
WHEN 0 THEN 'Copy'
WHEN 1 THEN 'Delete'
WHEN 2 THEN 'None'
WHEN 3 THEN 'Copy Properties' END) AS UPGRADEACTION
, (CASE TAKEACTION
WHEN 0 THEN 'NONE'
WHEN 1 THEN 'COPY' END)AS TAKEACTION
, (CASE COPYDONE
WHEN 0 THEN 'NO'
WHEN 1 THEN 'YES' END) AS COPYDONE
FROM PSPROJECTITEM
WHERE PROJECTNAME = 'UPGCUST' --- Replace w/ your project name
AND OBJECTTYPE NOT IN (10,53,54,55) --- Do not want thousands of queries, permission lists or portal objects
ORDER BY OBJECTTYPE, OBJECTVALUE1, OBJECTVALUE2, OBJECTVALUE3

Copy and paste the results into Excel and you can filter the first column for a very useful report.