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

Temp Table and AE

AE Important Tables

SELECT * FROM PS_AEREQUESTTBL WHERE PROCESS_INSTANCE = 552;
SELECT * FROM PS_AERUNCONTROL WHERE PROCESS_INSTANCE = 552;
SELECT * FROM PS_AERUNCONTROLPC WHERE PROCESS_INSTANCE = 552;
SELECT * FROM PS_AETEMPTBLMGR WHERE PROCESS_INSTANCE = 552;
SELECT * FROM PSPRCSRQST WHERE PRCSINSTANCE = 552;
SELECT * FROM PSPRCSPARMS WHERE PRCSINSTANCE = 552;
SELECT * FROM PSPRCSQUE WHERE PRCSINSTANCE = 552;


The following query lists the Temporary Records where there is no referencing Application Engine program where restart is enabled, the number of instances of each table, including the global instances.


SELECT r.recname, o.temptblinstances+NVL(c.temptblinstances,0) temptblinstancesFROM psoptions o, psrecdefn r LEFT OUTER JOIN pstemptblcntvw c ON c.recname = r.recnameWHERE r.rectype = 7AND NOT EXISTS( --omit tables where restart not disabled on referencing AE SELECT 'x' FROM psaeappltemptbl t , psaeappldefn a WHERE t.recname = r.recname AND a.ae_applid = t.ae_applid AND a.ae_disable_restart = 'N' --restart not disabled AND ROWNUM = 1)