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);

How to determine table locks in oracle

select * From v$locked_object

select * From v$session where SID = '<>'

select * from dba_objects where object_id = '<>'

-- Locked objects

Select object_name, owner, object_type from dba_objects
Where object_id in (select object_id from v$locked_object);

-- KILL SESSION COMMAND

ALTER SYSTEM KILL SESSION '38,53336' IMMEDIATE;

Query to find Portal Navigation from Menu and Component name in People Tools 8.4X

Use the below Query to find the Navigation for a component. Provide
• Menu Name
• Component Name
• Market
in the below query as your input parameter. Run this on SQL Plus.


WITH PORTAL_REGISTRY AS (
SELECT RTRIM(REVERSE(SYS_CONNECT_BY_PATH(REVERSE(PORTAL_LABEL)
, ' >> '))
, ' >> ') PATH
, LEVEL LVL
FROM PSPRSMDEFN
WHERE PORTAL_NAME = 'EMPLOYEE' START WITH PORTAL_REFTYPE = 'C'
AND PORTAL_URI_SEG1 = 'TM_ARCH_DATA' -- Menu Name
AND PORTAL_URI_SEG2 = 'TM_ARC_PAY_SUMMARY' -- Component Name
AND PORTAL_URI_SEG3 = 'USA' -- Market
CONNECT BY PRIOR PORTAL_PRNTOBJNAME = PORTAL_OBJNAME )
SELECT PATH
FROM PORTAL_REGISTRY
WHERE LVL = (
SELECT MAX(LVL)
FROM PORTAL_REGISTRY )

SQL Logging Syntax

SPOOL ;
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET ECHO ON;
SET TIME ON;

-- SQL Or PL/SQL

SPOOL OFF;


SPOOL ;
WHENEVER SQLERROR CONTINUE;
SET ECHO ON;
SET TIME ON;

-- SQL Or PL/SQL

SPOOL OFF;

Query to find out PeopleSoft Record Definition

SELECT
A.RECNAME
, A.FIELDNAME
, A.FIELDNUM
, DECODE (B.FIELDTYPE ,
0 ,'Char' ,
1 ,'LongChar' ,
2 ,'Number' ,
3 ,'SignNumber' ,
4 ,'Date' ,
5 ,'Time' ,
6 ,'DateTime' ,
8 ,'Image' ,
9 ,'ImgRef' )
,B.LENGTH
FROM PSRECFIELD A
, PSDBFIELD B
WHERE A.FIELDNAME = B.FIELDNAME
AND A.RECNAME = "Record Name"
ORDER BY FIELDNUM

PeopleSoft Field Type Number Mapping

0 - Char
1 - LongChar
2 - Number
3 - SignNumber
4 - Date
5 - Time
6 - DateTime
8 - Image
9 - ImgRef
8 - Attachment

Statistics on a Table

Query to find out Index on Table

SELECT OWNER,
TABLE_NAME,
COUNT(*) INDEX_COUNT
FROM DBA_INDEXES
where OWNER not in ('SYS','SYSTEM')
and table_name = 'Tabel Name'
group by OWNER, TABLE_NAME

Query to find out column of an Index

SELECT TABLE_OWNER,
TABLE_NAME,
COLUMN_NAME,
INDEX_NAME from dba_ind_columns WHERE TABLE_NAME = 'Table name'

Query to find out when the table was last analyzed and by what percentage

SELECT
NUM_ROWS,
SAMPLE_SIZE,
ROUND((SAMPLE_SIZE/NUM_ROWS)*100) "SAMPLE%",
TO_CHAR(last_analyzed,'DD-MM-YYYY:HH24:MI:SS') "Last_Analyzed"
FROM dba_tables
WHERE table_name ='Table_name';

List of objects in a PeopleSoft Project

Execute the Below Query in oracle database to get the list of objects in a PeopleSoft Project


SELECT projectname, 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 = 'Name of project'
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8

PeopleSoft Object Type and Record Name

Overview

This below table list the various PeopleSoft Object type and it corresponding Mater record table.

Object ID	Object Type	Record Name
0	Record	PSRECDEFN
1	Indexes	PSKEYDEFN
2	Fields	PSDBFIELD
3	Field Formats	PSFMTDEFN
4	Translates	PSXLATITEM
5	Pages	PSPNLDEFN
6	Menus	PSMENUDEFN
7	Components	PSPNLGRPDEFN
8	Record PeopleCode	PSPCMPROG
9	Menu PeopleCode	PSPCMPROG
10	Queries	PSQRYDEFN
11	Tree Structures	PSTREESTRCT
12	Trees	PSTREEDEFN
13	Access Groups	PS_ACCESS_GRP_TBL
14	Colours	PSCOLORDEFN
15	Styles	PSSTYLEDEFN
17	Business Processes	PSBUSPROCDEFN
18	Activities	PSACTIVITYDEFN
19	Roles	PSROLEDEFN
20	Process Definitions	PRCSDEFN
21	Server Definitions	PS_SERVERDEFN
22	Process Type Definitions	PS_PRCSTYPEDEFN
23	Job Definitions	PS_PRCSJOBDEFN
24	Recurrence Definitions	PS_PRCSRECUR
25	Message Catalog Entries	PSMSGCATDEFN
26	Dimension Definition	PS_DIMENSION
27	Cube Definition	TO_Find
28	Cube Instance Definition	TO_Find
29	Business Interlink	PSIODEFN
30	SQL	PSSQLDESCR
31	File Layout Definitions	PSFLDDEFN
32	Component Interfaces	PSBCDEFN
33	Application Engine Programs	PSAEAPPLDEFN
34	Application Engine Sections	PSAESECTDEFN
35	Message Nodes	PSMSGNODEDEFN
36	Message Channels	PSMSGCATDEFN
37	Message Definitions	PSMSGDEFN
38	Approval Rule Set	APPR_RULE_HDR
39	Message PeopleCode	PSPCMPROG
40	Subscription PeopleCode	PSPCMPROG
42	Comp. Interface PeopleCode	PSPCMPROG
43	Application Engine PeopleCode	PSPCMPROG
44	Page PeopleCode	PSPCMPROG
46	Component PeopleCode	PSPCMPROG
47	Component Record PeopleCode	PSPCMPROG
48	Component Rec Fld PeopleCode	PSPCMPROG
49	Images	PSCONTDEFN
52	File References	PSFILEREDEFN
53	Permission Lists	PSCLASSDEFN
54	Portal Registry Definitions	TO_Find
55	Portal Registry Structures	PSPRSMDEFN
56	URL Definitions	PSURLDEFN
57	Application Packages	PSPACKAGEDEFN
58	Application Package PeopleCode	PSPCMPROG
60	Analytic Types	TO_Find
62	XSLT	TO_Find
64	Mobile Pages	PSMPDEFN
68	File References	PSFILEREDEFN
69	File Type Codes	PSTYPECODEDEFN
72	Dignostic Plug Ins	TO_Find
73	Analytic Models	TO_Find
79	Service	PSSERVICE
80	Service Operation	PSOPERATION
81	Service Operation Handler	PSOPRHDLR
82	Service Operation Version	PSOPRVERDFN
83	Service Operation Routing	SIBRTNGDEFN
84	IB Queues	SIBQUEUEINST
85	XLMP Template Definition
86	XLMP Report Definition
87	XMLP File Definition
88	XMPL Data Source Definition

How to Clone PeopleSoft Production database

Overview

This document describes the steps required to create a clone of the PeopleSoft production database. This is the most common task of a PeopleSoft Administrator / in any PeopleSoft upgrade project.

Here are some of the steps that need to be consider when creating a Development or Test environment from a production database on PeopleTools 8.4X.


Steps

1. Create a backup of the production database. (This is your source database)

2. Stop the Target Application Environment. (If application environment is running)

1. Application Servers
2. Process Schedulers
3. Web Servers
   

3. Overlay the target database with a recent backup of the production database.

4. Start the database

5. Set DBNAME in PSDBOWNER back to the target database name.

UPDATE PSDBOWNER SET DBOWNER = 'Database Name';

6. Set GUID to ' ' in PSOPTIONS table.

UPDATE PSOPTIONS SET GUID = ' ';

This will cause PeopleSoft to generate a new GUID so that change assistant can track it separately from the source database.

7. Delete the data from

• Reporting tables
• Process scheduler tables
• Application messaging tables
  

This data isn't relevant in the target database.
Below DMS scripts are provided by PeopleSoft. Use these scripts to clean the above tables.
prcsclr.dms (Process Scheduler Tables)
rptclr.dms (Reporting Tables)
appmsgpurgeall.dms (Application Messaging Tables)

8. Reset email address to blank or predefined value.

Since you are creating a clone of the production database you will have actual email address of all the employees. Email address is a very critical data. Reset everyone's e-mail address to a pre-defined value so that workflow messages from the Test environment don't get sent to real users.

Some of the common tables of PeopleSoft ERP are as follows

PSOPRDEFN
PS_ROLEXLATOPR
PSUSEREMAIL


You need to take care of Application specific tables. You can find out these tables by using below script.

SELECT RECNAME
FROM PSRECFIELD B
WHERE EXISTS (SELECT 'X' FROM PSRECDEFN A WHERE RECTYPE = 0 AND A.RECNAME = B.RECNAME)
AND B.FIELDNAME = 'EMAILID'

8. Change application password (VP1)

Logon to PeopleSoft data mover script and execute the below scripts if you want to chage the application password.

UPDATE PSOPRDEFN SET OPERPSWD = 'PASSWORD', ENCRYPTED = 0 WHERE OPRID = VP1;
ENCRYPT_PASSWORD VP1;

@Cincinnati