Friday, May 8, 2009

Query - PeopleSoft Object in a Project

SELECT projectname, DECODE(objecttype,
0,'Record',
1,'Indexes',
2,'Fields',
3,'Field Formats',
4,'Translates',
5,'Pages',
6,'Menus',
7,'Components',
8,'Record PeopleCode',
9,'Menu PeopleCode',
10,'Queries',
11,'Tree Structures',
12,'Trees',
13,'Access Groups',
14,'Colours',
15,'Styles',
17,'Business Processes',
18,'Activities',
19,'Roles',
20,'Process Definitions',
21,'Server Definitions',
22,'Process Type Definitions',
23,'Job Definitions',
24,'Recurrence Definitions',
25,'Message Catalog Entries',
26,'Dimension Definition',
27,'Cube Definition',
28,'Cube Instance Definition',
29,'Business Interlink',
30,'SQL',
31,'File Layout Definitions',
32,'Component Interfaces',
33,'Application Engine Programs',
34,'Application Engine Sections',
35,'Message Nodes',
36,'Message Channels',
37,'Message Definitions',
38,'Approval Rule Set',
39,'Message PeopleCode',
40,'Subscription PeopleCode',
42,'Comp. Interface PeopleCode',
43,'Application Engine PeopleCode',
44,'Page PeopleCode',
46,'Component PeopleCode',
47,'Component Record PeopleCode',
48,'Component Rec Fld PeopleCode',
49,'Images',
52,'File References',
53,'Permission Lists',
54,'Portal Registry Definitions',
55,'Portal Registry Structures',
56,'URL Definitions',
57,'Application Packages',
58,'Application Package PeopleCode',
60,'Analytic Types',
62,'XSLT',
64,'Mobile Pages',
68,'File References',
69,'File Type Codes',
72,'Dignostic Plug Ins',
73,'Analytic Models',
79,'Service',
80,'Service Operation',
81,'Service Operation Handler',
82,'Service Operation Version',
83,'Service Operation Routing',
84,'IB Queues',
85,'XLMP Template Definition',
86,'XLMP Report Definition',
87,'XMLP File Definition',
88,'XMPL Data Source Definition') AS OBJECTTYPE
,objectvalue1, objectvalue2, objectvalue3,objectvalue4,
DECODE (sourcestatus,
0, 'Unknown',
1, 'Absent',
2, 'Changed',
3, 'Unchanged',
4, '*Changed',
5, '*Unchanged',
6, 'Same'
) AS SOURCE,
DECODE (targetstatus,
0, 'Unknown',
1, 'Absent',
2, 'Changed',
3, 'Unchanged',
4, '*Changed',
5, '*Unchanged',
6, 'Same'
) AS TARGET,
DECODE (upgradeaction,
0, 'Copy',
1, 'Delete',
2, 'None',
3, 'CopyProp'
) AS ACTION,
DECODE (takeaction, 0, 'No', 1, 'Yes') AS UPGRADE
FROM psprojectitem
WHERE projectname = 'Project Name'
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8