Decoding Project Items Table

As you all might know, PeopleSoft is a table driven application. i.e PeopleSoft stores most of its meta data in the PeopleTools tables/Metadata tables. The same is applicable for the projects you create in application designer. All the objects that you include in the project are stored as a separate row in a PeopleTools metadata table called PSPROJECTITEM. In normal situations you need not bother about these tables as these are purely reserved for PeopleTools internal processing. But there will be some specials scenarios where you need to drill into the metadata tables. This is especially applicable when something wrong happens in production and if you are in charge of analyzing which project or change broke the code line in your production system. I have formulated a sql which will list all the objects with their name and type as it appears in the application designer. I thought of sharing it over here so that anyone looking for similar information is also benefitted. The sql provided below is a simple sql which decodes the object type code used by the PeopleTools. But the real benefit comes if you can take the decoded values and join with other criteria which can directly fetch an impacted project name from the database instead of investing a lot of your valuable time in manually searching for projects. The intention is to give you a hint to start with.

The sql provided below is supposed to work fine for Oracle database and PeopleTools version 8.53. If you have any other database, make the appropriate changes to the sql to fetch the result.

Select

rownum as Serial_Number,

decode(objecttype,

0,'Record',

1,'Index',

2,'Field',

3,'Field Format',

4,'Translate',

5,'Page',

6,'Menu',

7,'Component',

8,'Record PeopleCode',

10,'Query',

11,'Tree Structure',

12,'Tree',

6,'Access Group',

17,'Business Process',

18,'Activity',

19,'Role',

20,'Process Definition',

21,'Server Definition',

22,'Process Type',

23,'Job Definition',

24,'Recurrence Defn',

25,'Message Catalog',

29,'Business Interlink',

30,'SQL',

31,'File Layout',

32,'Component Interface',

33,'App Engine',

34,'App Engine Section',

35,'Message Node',

36,'Message Channel',

37,'App Message',

38,'Approval Rule Set',

39,'Message PeopleCode',

40,'Subscribe PeopleCode',

42,'Comp Intfc PeopleCode',

43,'App Engine PeopleCode',

44,'Page PeopleCode',

45,'Page Field PeopleCode',

46,'Component PeopleCode',

47,'Component Rec PeopleCode',

48,'Comp RecField PeopleCode',

49,'Image',

50,'Stylesheet',

51,'HTML',

53,'Permission List',

54,'Portal Registry Defn',

55,'Portal Folder',

56,'Portal CREF',

57,'App Package',

58,'App Class',

62,'XSLT',

64,'Mobile Page',

68,'File Reference',

69,'File Type',

70,'Archive Object',

71,'Archive Template',

72,'Diagnostic',

73,'Analytic Model',

79,'Service',

80,'Service Operation',

81,'Handler',

82,'Service Oper Version',

83,'Routing',

84,'Queue',

85,'BI Template',

86,'BI Report Defn',

87,'BI File Defn',

88,'XMLP Data Source',

89,'WSDL',

90,'Schema',

91,'Connected Query',

92,'Logical Schema',

93,'Physical Schema',

94,'Relational Schema',

95,'Logical Schema Dep',

96,'Document Schema',

97,'Cube Dimension',

98,'Cube Outline',

99,'Cube Connection',

100,'Cube Template',

101,'Delimited Schema',

102,'Positional Schema',

103,'App DataSet',

104,'Test Framework',

105,'Test Case',

106,'App DataSet Binding',

107,'Feed',

108,'Feed Category',

109,'Feed Data Type',

110,'JSON Schema',

111,'RC Service Defn',

112,'RC Service',

113,'RC Service Config',

114,'RC Layout',

115,'Search Attribute',

116,'Search Definition',

117,'Search Category',

118,'Search Context',

119,'Integration Group') As Object_Type,

objectvalue1||decode(objectvalue2,' ','','.'||objectvalue2)||decode(objectvalue3,' ','','.'||objectvalue3)||decode(objectvalue4,' ','','.'||objectvalue4) As Object_Name

from psprojectitem where projectname = 'YOUR_PROJECTNAME';