How to Create XSD Schema for Rowset Type Data Source in PeopleSoft. (XMPL Publisher)

PeopleSoft provide an application package which can be used to create XSD and sample xml file. The application package name is PSXP_XMLGEN. You can also create XSD manually. Once you create the XSD you can upload it to your data source.

Below are the Steps to create a XSD for Rowset Type Data Source and sample code.
1. Create an Application Engine Program and Insert a PeopleCode Step into it.
Write PeopleCode to Perform below steps.
a. Import Application Package
b. Create Rowset
c. Populate Data to Rowset
d. Create XSD
e. Create Sample XML file with data

Sample Code. Replace File Path and File name accordingly.

import PSXP_XMLGEN:*;

Local Rowset &Rowset_XMLP;

/* Create Rowset */
&Rowset_XMLP = CreateRowset(Record.);

/* Fill rowset */
&Rowset_XMLP.Fill();

/* Create XSD */
&ObjRowsetXSD = create psxp_xmlgen:RowSetDS();
&XSDSchema = & ObjRowsetXSD.GetXSDSchema(&Rowset_XMLP);

/* Write you schema into an output file so that you can upload it to the Data Source */
&XSDFile = GetFile("File_Path\File_Name.xsd", "W", %FilePath_Absolute);
& XSDFile.WriteLine(&XSDSchema);
& XSDFile.Close();

/* Create Sample XML File */

&ObjRowsetXSD = create psxp_xmlgen:RowSetDS();
&XMLFile = &ObjRowsetXSD.getXMLData(&Rowset_XMLP, "(File_Path\File_Name of XSD)”;

&SampleXMLFile = GetFile("File_Path\File_Name.xml", "W", %FilePath_Absolute);
& SampleXMLFile.WriteLine(&XMLFile);
& SampleXMLFile.Close();

How to Build PeopleSoft Portal Registry Search Index.

Navigate to: PeopleTools > Portal > Build Registry Search Index

Create a New Run Control ID = BUILD_SRCH_INDEX

Select all installed Languages and confirm that the base language is listed.



Click on Run Button



Select Process name PORTAL_INDEX.

Select OK

Go to Process Monitor a verify job is completed successfully.

Once the job is posted successful verify files are created in below navigation.
PS_HOME of your Process Scheduler server: Go to PS_HOME\data\search\Employee\{dbname}\ENG (assuming it is English) other languages will appear in their own folders if they exist). In this directory, look for below files and folders.







All should be well if the above files exist and there are no errors.

Logout and login again and search anything on the search box.

It should work now!!

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)

puupx07 issue

DROP TABLE PSRECFIELD_TMP
/
CREATE TABLE PSRECFIELD_TMP AS SELECT * FROM PSRECFIELD
/
CREATE UNIQUE INDEX PS_PSRECFIELD_TMP ON PSRECFIELD_TMP (RECNAME,
FIELDNAME) TABLESPACE PSINDEX
/
CREATE INDEX PSAPSRECFIELD_TMP ON PSRECFIELD_TMP (FIELDNAME,
RECNAME) TABLESPACE PSINDEX
/
CREATE INDEX PSBPSRECFIELD_TMP ON PSRECFIELD_TMP (DEFRECNAME) TABLESPACE
PSINDEX
/
CREATE INDEX PSCPSRECFIELD_TMP ON PSRECFIELD_TMP (DEFFIELDNAME,
DEFRECNAME) TABLESPACE PSINDEX
/
CREATE INDEX PSDPSRECFIELD_TMP ON PSRECFIELD_TMP (EDITTABLE) TABLESPACE
PSINDEX
/
CREATE INDEX PSEPSRECFIELD_TMP ON PSRECFIELD_TMP (CURCTLFIELDNAME)
TABLESPACE PSINDEX
/
CREATE INDEX PSFPSRECFIELD_TMP ON PSRECFIELD_TMP (SUBRECORD,
RECNAME,
FIELDNAME) TABLESPACE PSINDEX
/
COMMIT
/

Query to find Navigation in 8.1x PeopleTools Release

SELECT DISTINCT
A.MENUNAME,
C.PNLGRPNAME ",",
A.MENULABEL ",",
B.BARLABEL ",",
RTRIM(A.MENUGROUP) || ' -->' || RTRIM(A.MENULABEL) || ' -->'
|| RTRIM(B.BARLABEL) || ' -->' || RTRIM(B.ITEMLABEL) "PIA_MENU"
FROM PSMENUDEFN A,
PSMENUITEM B,
PSPNLGROUP C
WHERE A.MENUNAME = B.MENUNAME
AND B.PNLGRPNAME = C.PNLGRPNAME
AND B.MARKET = C.MARKET
and C.PNLGRPNAME in (
'TM_ARCH_SETUP') -- Component Name
GROUP BY A.MENUNAME,
A.MENUGROUP,
A.MENULABEL,
B.BARLABEL,
B.ITEMLABEL,
A.MENUNAME,
C.PNLGRPNAME

Table Statistics syntax

EXECUTE DBMS_STATS.GATHER_TABLE_STATS (ownname=> 'SYSADM', tabname=> 'Table Name', estimate_percent=>25,cascade=>TRUE);