How to find PeopleSoft Project Item List SQL

How to find PeopleSoft Project Item List SQL in table PSPROJECTITEM.

This SQL for select all list your project in application designer (AE) such as sql table, sql view table, work record, sub record, dynamic view, query view, temp table, other record or deleted, index, field, page, menu, component.

SQL help you run a sql query to list all the objects in a project in application designer.

SQL To List Project Items in PeopleSoft

Oracle PL/SQL

SELECT OBJECTTYPE, CASE OBJECTTYPE WHEN 0 THEN CASE NVL((SELECT RECTYPE FROM PSRECDEFN WHERE RECNAME = I.OBJECTVALUE1), 99) WHEN 0 THEN 'SQL Table in DB' WHEN 1 THEN 'SQL View in DB' WHEN 2 THEN 'Work Record' WHEN 3 THEN 'Sub Record' WHEN 5 THEN 'Dynamic View' WHEN 6 THEN 'Query View' WHEN 7 THEN 'Temporary Table' ELSE 'Other Record or Deleted' END WHEN 1 THEN 'Index' WHEN 2 THEN 'Field' WHEN 3 THEN 'Field Format' WHEN 4 THEN 'Translate Value' WHEN 5 THEN 'Page' WHEN 6 THEN 'Menu' WHEN 7 THEN 'Component' WHEN 8 THEN 'Record PeopleCode' WHEN 9 THEN 'Menu PeopleCode' WHEN 10 THEN 'Query' WHEN 11 THEN 'Tree Structure' WHEN 12 THEN 'Tree' WHEN 13 THEN 'Access Group' WHEN 14 THEN 'Color' WHEN 15 THEN 'Style' WHEN 16 THEN 'Business Process Map' WHEN 17 THEN 'Business Process' WHEN 18 THEN 'Activity' WHEN 19 THEN 'Role' WHEN 20 THEN 'Process Definition' WHEN 21 THEN 'Process Server Definition' WHEN 22 THEN 'Process Type Definition' WHEN 23 THEN 'Process Job Definition' WHEN 24 THEN 'Process Recurrence Definition' WHEN 25 THEN 'Message Catalog' WHEN 26 THEN 'Dimension' WHEN 27 THEN 'Cube Definition' WHEN 28 THEN 'Cube Instance Definition' WHEN 29 THEN 'Business Interlink' WHEN 30 THEN CASE OBJECTVALUE2 WHEN '0' THEN 'SQL Object' WHEN '1' THEN 'App Engine SQL' WHEN '2' THEN 'Record View SQL' WHEN '5' THEN 'Query for DDAUDIT or SYSAUDIT' WHEN '6' THEN 'App Engine XML SQL' ELSE 'SQL' END WHEN 31 THEN 'File Layout' WHEN 32 THEN 'Component Interface' WHEN 33 THEN 'Application Engine Program' WHEN 34 THEN 'Application Engine 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 'Application Engine 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 Record Field 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 Structure' 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' WHEN 79 THEN 'Service' WHEN 80 THEN 'Service Operation' WHEN 81 THEN 'Service Operation Handler' WHEN 82 THEN 'Service Operation Version' WHEN 83 THEN 'Service Operation Routing' WHEN 84 THEN 'Info Broker Queues' WHEN 85 THEN 'XLMP Template Definition' WHEN 86 THEN 'XLMP Report Definition' WHEN 87 THEN 'XMLP File Definition' WHEN 88 THEN 'XMPL Data Source Definition' ELSE 'Unknown ' || OBJECTTYPE END AS Object_Type, CASE OBJECTTYPE WHEN 12 THEN OBJECTVALUE3 WHEN 30 THEN CASE WHEN OBJECTVALUE2 = 0 THEN OBJECTVALUE1 /* SQL Object */ WHEN OBJECTVALUE2 = 1 THEN SUBSTR(OBJECTVALUE1, 1, 12) WHEN OBJECTVALUE2 = 2 THEN OBJECTVALUE1 /* Record View SQL */ ELSE ' ' END WHEN 34 THEN TRIM(OBJECTVALUE1) || '.' || TRIM(OBJECTVALUE2) WHEN 62 THEN TRIM(SUBSTR(OBJECTVALUE1, 1, 12)) ELSE OBJECTVALUE1 END AS NAME, CASE WHEN OBJECTTYPE = 1 THEN 'Index: ' || OBJECTVALUE2 WHEN OBJECTTYPE = 4 THEN 'XLAT: ' || OBJECTVALUE2 || '; Date: ' || OBJECTVALUE3 || '; ' || NVL((SELECT 'ShortName: ' || XLATSHORTNAME || '; LongName: ' || XLATLONGNAME || '; Status: ' || EFF_STATUS FROM PSXLATITEM WHERE FIELDNAME = I.OBJECTVALUE1 AND FIELDVALUE = I.OBJECTVALUE2 AND EFFDT = TO_DATE(I.OBJECTVALUE3, 'YYYY-MM-DD') ), 'XLAT Deleted') WHEN OBJECTTYPE = 7 THEN 'Market: ' || OBJECTVALUE2 WHEN OBJECTTYPE = 8 THEN OBJECTVALUE1 || '.' || OBJECTVALUE2 || '.' || OBJECTVALUE3 WHEN OBJECTTYPE = 9 THEN OBJECTVALUE2 || '.' || OBJECTVALUE3 || '.' || OBJECTVALUE4 WHEN OBJECTTYPE = 12 THEN 'EFFDT: ' || OBJECTVALUE4 WHEN OBJECTTYPE = 20 THEN 'Process Name: ' || OBJECTVALUE2 WHEN OBJECTTYPE IN(22, 40) THEN OBJECTVALUE2 || '.' || OBJECTVALUE3 WHEN OBJECTTYPE = 25 THEN 'Message: ' || OBJECTVALUE2 || ' (Message Set Descr: ' || OBJECTVALUE3 || ')' WHEN OBJECTTYPE = 30 THEN CASE WHEN OBJECTVALUE2 = 0 THEN ' ' /* SQL Object */ WHEN OBJECTVALUE2 = 1 THEN 'AE Progam: ' || SUBSTR(OBJECTVALUE1, 1, 12) || ' Section: ' || SUBSTR(I.OBJECTVALUE1, 13, 8) || ' Step: ' || SUBSTR(OBJECTVALUE1, 21, 8) || ' Type: ' || DECODE(SUBSTR(OBJECTVALUE1, 29, 1), 'S', 'SQL', 'D', 'Do Select', 'W', 'Do While', 'H', 'Do When', 'N', 'Do Until', SUBSTR(OBJECTVALUE1, 29, 1)) WHEN OBJECTVALUE2 = 2 THEN ' ' /* Record View SQL */ ELSE ' ' END WHEN OBJECTTYPE = 38 THEN 'EFFDT: ' || OBJECTVALUE2 WHEN OBJECTTYPE IN(39, 42, 44) THEN OBJECTVALUE2 WHEN OBJECTTYPE = 43 THEN CASE WHEN TRIM(OBJECTVALUE4) = 'OnExecute' THEN 'Section: ' || SUBSTR(I.OBJECTVALUE2, 1, 8) || '; Step: ' || OBJECTVALUE3 || '; Market: ' || SUBSTR(I.OBJECTVALUE2, 9, 3) || '; Database: ' || TRIM(SUBSTR(OBJECTVALUE2, 12, 8)) || '; EFFDT: ' || TRIM(SUBSTR(OBJECTVALUE2, 21, 10)) ELSE 'Section: ' || OBJECTVALUE2 || '; Market: ' || OBJECTVALUE3 || '; Database: ' || TRIM(SUBSTR(OBJECTVALUE4, 12, 8)) || '; EFFDT: ' || TRIM(SUBSTR(OBJECTVALUE4, 21, 10)) END WHEN OBJECTTYPE = 46 THEN 'Market: ' || OBJECTVALUE2 || '; Event: ' || OBJECTVALUE3 WHEN OBJECTTYPE = 47 THEN 'Market: ' || OBJECTVALUE2 || '; Record: ' || OBJECTVALUE3 || '; Event: ' || OBJECTVALUE4 WHEN OBJECTTYPE = 48 THEN 'Market: ' || OBJECTVALUE2 || '; Record: ' || OBJECTVALUE3 || '; Field: ' || TRIM(SUBSTR(OBJECTVALUE4, 1, 18)) || '; Event: ' || TRIM(SUBSTR(OBJECTVALUE4, 19, 16)) WHEN OBJECTTYPE = 55 THEN DECODE(OBJECTVALUE2, 'C', 'Content: ', 'F', 'Folder: ') || OBJECTVALUE3 WHEN OBJECTTYPE = 57 THEN CASE WHEN TRIM(OBJECTVALUE4) NOT IN(' ', ':', '.') THEN 'Subclass: ' || TRIM(OBJECTVALUE2) || ':' || TRIM(OBJECTVALUE3) || ':' || TRIM(OBJECTVALUE4) ELSE CASE WHEN TRIM(OBJECTVALUE3) NOT IN(' ', ':', '.') THEN 'Subclass: ' || TRIM(OBJECTVALUE2) || ':' || TRIM(OBJECTVALUE3) ELSE CASE WHEN TRIM(OBJECTVALUE2) NOT IN(' ', ':', '.') THEN 'Subclass: ' || TRIM(OBJECTVALUE2) ELSE ' ' END END END WHEN OBJECTTYPE IN(58, 63, 68, 81, 82, 83, 87, 88) THEN CASE WHEN TRIM(OBJECTVALUE4) IS NOT NULL THEN TRIM(OBJECTVALUE2) || '.' || TRIM(OBJECTVALUE3) || '.' || TRIM(OBJECTVALUE4) ELSE CASE WHEN TRIM(OBJECTVALUE3) IS NOT NULL THEN TRIM(OBJECTVALUE2) || '.' || TRIM(OBJECTVALUE3) ELSE CASE WHEN TRIM(OBJECTVALUE2) IS NOT NULL THEN TRIM(OBJECTVALUE2) ELSE ' ' END END END WHEN OBJECTTYPE = 59 THEN TRIM(OBJECTVALUE2) WHEN OBJECTTYPE = 62 THEN 'AE Progam: ' || SUBSTR(OBJECTVALUE1, 1, 12) || ' Section: ' || SUBSTR(I.OBJECTVALUE1, 13, 8) || ' Step: ' || SUBSTR(OBJECTVALUE1, 21, 8) ELSE ' ' END AS EXTENDED_OBJ_NAME, CASE OBJECTTYPE WHEN 0 THEN NVL((SELECT RECDESCR FROM PSRECDEFN WHERE RECNAME = I.OBJECTVALUE1), ' ') WHEN 1 THEN NVL((SELECT IDXCOMMENTS FROM PSINDEXDEFN WHERE RECNAME = I.OBJECTVALUE1 AND INDEXID = I.OBJECTVALUE2), ' ') WHEN 3 THEN NVL((SELECT DESCR FROM PSFMTDEFN WHERE FORMATFAMILY = I.OBJECTVALUE1), ' ') WHEN 5 THEN NVL((SELECT DESCR FROM PSPNLDEFN WHERE PNLNAME = I.OBJECTVALUE1), ' ') WHEN 6 THEN NVL((SELECT DESCR FROM PSMENUDEFN WHERE MENUNAME = I.OBJECTVALUE1), ' ') WHEN 7 THEN NVL((SELECT DESCR FROM PSPNLGRPDEFN WHERE PNLGRPNAME = I.OBJECTVALUE1 AND MARKET = I.OBJECTVALUE2), ' ') WHEN 20 THEN NVL((SELECT DESCR FROM PS_PRCSDEFN WHERE PRCSTYPE = I.OBJECTVALUE1 AND PRCSNAME = I.OBJECTVALUE2), ' ') WHEN 32 THEN NVL((SELECT DESCR FROM PSBCDEFN WHERE BCNAME = I.OBJECTVALUE1), ' ') WHEN 33 THEN NVL((SELECT DESCR FROM PSAEAPPLDEFN WHERE AE_APPLID = I.OBJECTVALUE1), ' ') ELSE ' ' END AS DESCR FROM PSPROJECTITEM I WHERE PROJECTNAME ='<strong><em>PROJECT_NAME</em></strong>' ORDER BY OBJECTTYPE, 2, OBJECTVALUE1, OBJECTVALUE2, OBJECTVALUE3, OBJECTVALUE4;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208

SELECT OBJECTTYPE, CASE OBJECTTYPE WHEN 0 THEN CASE NVL((SELECT RECTYPE FROM PSRECDEFN WHERE RECNAME = I.OBJECTVALUE1), 99) WHEN 0 THEN 'SQL Table in DB' WHEN 1 THEN 'SQL View in DB' WHEN 2 THEN 'Work Record' WHEN 3 THEN 'Sub Record' WHEN 5 THEN 'Dynamic View' WHEN 6 THEN 'Query View' WHEN 7 THEN 'Temporary Table' ELSE 'Other Record or Deleted' END WHEN 1 THEN 'Index' WHEN 2 THEN 'Field' WHEN 3 THEN 'Field Format' WHEN 4 THEN 'Translate Value' WHEN 5 THEN 'Page' WHEN 6 THEN 'Menu' WHEN 7 THEN 'Component' WHEN 8 THEN 'Record PeopleCode' WHEN 9 THEN 'Menu PeopleCode' WHEN 10 THEN 'Query' WHEN 11 THEN 'Tree Structure' WHEN 12 THEN 'Tree' WHEN 13 THEN 'Access Group' WHEN 14 THEN 'Color' WHEN 15 THEN 'Style' WHEN 16 THEN 'Business Process Map' WHEN 17 THEN 'Business Process' WHEN 18 THEN 'Activity' WHEN 19 THEN 'Role' WHEN 20 THEN 'Process Definition' WHEN 21 THEN 'Process Server Definition' WHEN 22 THEN 'Process Type Definition' WHEN 23 THEN 'Process Job Definition' WHEN 24 THEN 'Process Recurrence Definition' WHEN 25 THEN 'Message Catalog' WHEN 26 THEN 'Dimension' WHEN 27 THEN 'Cube Definition' WHEN 28 THEN 'Cube Instance Definition' WHEN 29 THEN 'Business Interlink' WHEN 30 THEN CASE OBJECTVALUE2 WHEN '0' THEN 'SQL Object' WHEN '1' THEN 'App Engine SQL' WHEN '2' THEN 'Record View SQL' WHEN '5' THEN 'Query for DDAUDIT or SYSAUDIT' WHEN '6' THEN 'App Engine XML SQL' ELSE 'SQL' END WHEN 31 THEN 'File Layout' WHEN 32 THEN 'Component Interface' WHEN 33 THEN 'Application Engine Program' WHEN 34 THEN 'Application Engine 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 'Application Engine 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 Record Field 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 Structure' 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' WHEN 79 THEN 'Service' WHEN 80 THEN 'Service Operation' WHEN 81 THEN 'Service Operation Handler' WHEN 82 THEN 'Service Operation Version' WHEN 83 THEN 'Service Operation Routing' WHEN 84 THEN 'Info Broker Queues' WHEN 85 THEN 'XLMP Template Definition' WHEN 86 THEN 'XLMP Report Definition' WHEN 87 THEN 'XMLP File Definition' WHEN 88 THEN 'XMPL Data Source Definition' ELSE 'Unknown ' || OBJECTTYPE END AS Object_Type, CASE OBJECTTYPE WHEN 12 THEN OBJECTVALUE3 WHEN 30 THEN CASE WHEN OBJECTVALUE2 = 0 THEN OBJECTVALUE1 /* SQL Object */ WHEN OBJECTVALUE2 = 1 THEN SUBSTR(OBJECTVALUE1, 1, 12) WHEN OBJECTVALUE2 = 2 THEN OBJECTVALUE1 /* Record View SQL */ ELSE ' ' END WHEN 34 THEN TRIM(OBJECTVALUE1) || '.' || TRIM(OBJECTVALUE2) WHEN 62 THEN TRIM(SUBSTR(OBJECTVALUE1, 1, 12)) ELSE OBJECTVALUE1 END AS NAME, CASE WHEN OBJECTTYPE = 1 THEN 'Index: ' || OBJECTVALUE2 WHEN OBJECTTYPE = 4 THEN 'XLAT: ' || OBJECTVALUE2 || '; Date: ' || OBJECTVALUE3 || '; ' || NVL((SELECT 'ShortName: ' || XLATSHORTNAME || '; LongName: ' || XLATLONGNAME || '; Status: ' || EFF_STATUS FROM PSXLATITEM WHERE FIELDNAME = I.OBJECTVALUE1 AND FIELDVALUE = I.OBJECTVALUE2 AND EFFDT = TO_DATE(I.OBJECTVALUE3, 'YYYY-MM-DD') ), 'XLAT Deleted') WHEN OBJECTTYPE = 7 THEN 'Market: ' || OBJECTVALUE2 WHEN OBJECTTYPE = 8 THEN OBJECTVALUE1 || '.' || OBJECTVALUE2 || '.' || OBJECTVALUE3 WHEN OBJECTTYPE = 9 THEN OBJECTVALUE2 || '.' || OBJECTVALUE3 || '.' || OBJECTVALUE4 WHEN OBJECTTYPE = 12 THEN 'EFFDT: ' || OBJECTVALUE4 WHEN OBJECTTYPE = 20 THEN 'Process Name: ' || OBJECTVALUE2 WHEN OBJECTTYPE IN(22, 40) THEN OBJECTVALUE2 || '.' || OBJECTVALUE3 WHEN OBJECTTYPE = 25 THEN 'Message: ' || OBJECTVALUE2 || ' (Message Set Descr: ' || OBJECTVALUE3 || ')' WHEN OBJECTTYPE = 30 THEN CASE WHEN OBJECTVALUE2 = 0 THEN ' ' /* SQL Object */ WHEN OBJECTVALUE2 = 1 THEN 'AE Progam: ' || SUBSTR(OBJECTVALUE1, 1, 12) || ' Section: ' || SUBSTR(I.OBJECTVALUE1, 13, 8) || ' Step: ' || SUBSTR(OBJECTVALUE1, 21, 8) || ' Type: ' || DECODE(SUBSTR(OBJECTVALUE1, 29, 1), 'S', 'SQL', 'D', 'Do Select', 'W', 'Do While', 'H', 'Do When', 'N', 'Do Until', SUBSTR(OBJECTVALUE1, 29, 1)) WHEN OBJECTVALUE2 = 2 THEN ' ' /* Record View SQL */ ELSE ' ' END WHEN OBJECTTYPE = 38 THEN 'EFFDT: ' || OBJECTVALUE2 WHEN OBJECTTYPE IN(39, 42, 44) THEN OBJECTVALUE2 WHEN OBJECTTYPE = 43 THEN CASE WHEN TRIM(OBJECTVALUE4) = 'OnExecute' THEN 'Section: ' || SUBSTR(I.OBJECTVALUE2, 1, 8) || '; Step: ' || OBJECTVALUE3 || '; Market: ' || SUBSTR(I.OBJECTVALUE2, 9, 3) || '; Database: ' || TRIM(SUBSTR(OBJECTVALUE2, 12, 8)) || '; EFFDT: ' || TRIM(SUBSTR(OBJECTVALUE2, 21, 10)) ELSE 'Section: ' || OBJECTVALUE2 || '; Market: ' || OBJECTVALUE3 || '; Database: ' || TRIM(SUBSTR(OBJECTVALUE4, 12, 8)) || '; EFFDT: ' || TRIM(SUBSTR(OBJECTVALUE4, 21, 10)) END WHEN OBJECTTYPE = 46 THEN 'Market: ' || OBJECTVALUE2 || '; Event: ' || OBJECTVALUE3 WHEN OBJECTTYPE = 47 THEN 'Market: ' || OBJECTVALUE2 || '; Record: ' || OBJECTVALUE3 || '; Event: ' || OBJECTVALUE4 WHEN OBJECTTYPE = 48 THEN 'Market: ' || OBJECTVALUE2 || '; Record: ' || OBJECTVALUE3 || '; Field: ' || TRIM(SUBSTR(OBJECTVALUE4, 1, 18)) || '; Event: ' || TRIM(SUBSTR(OBJECTVALUE4, 19, 16)) WHEN OBJECTTYPE = 55 THEN DECODE(OBJECTVALUE2, 'C', 'Content: ', 'F', 'Folder: ') || OBJECTVALUE3 WHEN OBJECTTYPE = 57 THEN CASE WHEN TRIM(OBJECTVALUE4) NOT IN(' ', ':', '.') THEN 'Subclass: ' || TRIM(OBJECTVALUE2) || ':' || TRIM(OBJECTVALUE3) || ':' || TRIM(OBJECTVALUE4) ELSE CASE WHEN TRIM(OBJECTVALUE3) NOT IN(' ', ':', '.') THEN 'Subclass: ' || TRIM(OBJECTVALUE2) || ':' || TRIM(OBJECTVALUE3) ELSE CASE WHEN TRIM(OBJECTVALUE2) NOT IN(' ', ':', '.') THEN 'Subclass: ' || TRIM(OBJECTVALUE2) ELSE ' ' END END END WHEN OBJECTTYPE IN(58, 63, 68, 81, 82, 83, 87, 88) THEN CASE WHEN TRIM(OBJECTVALUE4) IS NOT NULL THEN TRIM(OBJECTVALUE2) || '.' || TRIM(OBJECTVALUE3) || '.' || TRIM(OBJECTVALUE4) ELSE CASE WHEN TRIM(OBJECTVALUE3) IS NOT NULL THEN TRIM(OBJECTVALUE2) || '.' || TRIM(OBJECTVALUE3) ELSE CASE WHEN TRIM(OBJECTVALUE2) IS NOT NULL THEN TRIM(OBJECTVALUE2) ELSE ' ' END END END WHEN OBJECTTYPE = 59 THEN TRIM(OBJECTVALUE2) WHEN OBJECTTYPE = 62 THEN 'AE Progam: ' || SUBSTR(OBJECTVALUE1, 1, 12) || ' Section: ' || SUBSTR(I.OBJECTVALUE1, 13, 8) || ' Step: ' || SUBSTR(OBJECTVALUE1, 21, 8) ELSE ' ' END AS EXTENDED_OBJ_NAME, CASE OBJECTTYPE WHEN 0 THEN NVL((SELECT RECDESCR FROM PSRECDEFN WHERE RECNAME = I.OBJECTVALUE1), ' ') WHEN 1 THEN NVL((SELECT IDXCOMMENTS FROM PSINDEXDEFN WHERE RECNAME = I.OBJECTVALUE1 AND INDEXID = I.OBJECTVALUE2), ' ') WHEN 3 THEN NVL((SELECT DESCR FROM PSFMTDEFN WHERE FORMATFAMILY = I.OBJECTVALUE1), ' ') WHEN 5 THEN NVL((SELECT DESCR FROM PSPNLDEFN WHERE PNLNAME = I.OBJECTVALUE1), ' ') WHEN 6 THEN NVL((SELECT DESCR FROM PSMENUDEFN WHERE MENUNAME = I.OBJECTVALUE1), ' ') WHEN 7 THEN NVL((SELECT DESCR FROM PSPNLGRPDEFN WHERE PNLGRPNAME = I.OBJECTVALUE1 AND MARKET = I.OBJECTVALUE2), ' ') WHEN 20 THEN NVL((SELECT DESCR FROM PS_PRCSDEFN WHERE PRCSTYPE = I.OBJECTVALUE1 AND PRCSNAME = I.OBJECTVALUE2), ' ') WHEN 32 THEN NVL((SELECT DESCR FROM PSBCDEFN WHERE BCNAME = I.OBJECTVALUE1), ' ') WHEN 33 THEN NVL((SELECT DESCR FROM PSAEAPPLDEFN WHERE AE_APPLID = I.OBJECTVALUE1), ' ') ELSE ' ' END AS DESCR FROM PSPROJECTITEM I WHERE PROJECTNAME ='<strong><em>PROJECT_NAME</em></strong>' ORDER BY OBJECTTYPE, 2, OBJECTVALUE1, OBJECTVALUE2, OBJECTVALUE3, OBJECTVALUE4;

 

Reference: http://peoplesofttutorial.com/sql-to-list-project-items-in-peoplesoft/