PeopleTools metadata primer

Everything you build in Application Designer is stored as metadata: rows in PeopleTools tables, in the same database as your application data. A record definition is a row in PSRECDEFN, its fields are rows in PSRECFIELD, and your PeopleCode lives in PSPCMPROG. PeopleTools itself loads definitions from these tables at runtime.

That’s what makes an MCP server for PeopleSoft possible. The AI Assist server doesn’t parse exported files or rely on documentation that goes stale. It reads the same tables PeopleTools does, so its answers reflect your system as it is right now, customizations included.

This page maps each kind of definition to the tables that hold it.

Read these tables, never write them

PeopleTools tables are internal to PeopleTools, and their structure can change between releases. Never update them with SQL. AI Assist only ever reads them, and it checks the PeopleTools release in PSSTATUS.TOOLSREL so it can adjust its queries between versions.

Definitions at a glance

Definition	Main tables	Useful columns
Record	PSRECDEFN, PSRECFIELD	RECNAME, RECTYPE, SQLTABLENAME, FIELDNUM, USEEDIT
Field	PSDBFIELD, PSDBFLDLABL	FIELDNAME, FIELDTYPE, LENGTH, DECIMALPOS
Translate values	PSXLATITEM	FIELDNAME, FIELDVALUE, EFFDT, EFF_STATUS, XLATLONGNAME
Index	PSINDEXDEFN, PSKEYDEFN	RECNAME, INDEXID, KEYPOSN, FIELDNAME
Page	PSPNLDEFN, PSPNLFIELD	PNLNAME, RECNAME, FIELDNAME, SUBPNLNAME
Component	PSPNLGRPDEFN, PSPNLGROUP	PNLGRPNAME, MARKET, SEARCHRECNAME, PNLNAME
Menu	PSMENUDEFN, PSMENUITEM	MENUNAME, BARNAME, ITEMNAME, PNLGRPNAME
Portal registry	PSPRSMDEFN	PORTAL_NAME, PORTAL_OBJNAME, PORTAL_URI_SEG1 to PORTAL_URI_SEG3
PeopleCode	PSPCMPROG, PSPCMTXT	OBJECTID1 to OBJECTID7, OBJECTVALUE1 to OBJECTVALUE7, PROGSEQ, PCTEXT
SQL	PSSQLDEFN, PSSQLTEXTDEFN	SQLID, SQLTYPE, DBTYPE, SEQNUM, SQLTEXT
Application Engine	PSAEAPPLDEFN, PSAESECTDEFN, PSAESTEPDEFN, PSAESTMTDEFN	AE_APPLID, AE_SECTION, AE_STEP, AE_STMT_TYPE
Message catalog	PSMSGCATDEFN	MESSAGE_SET_NBR, MESSAGE_NBR, MESSAGE_TEXT
Project	PSPROJECTDEFN, PSPROJECTITEM	PROJECTNAME, OBJECTTYPE, OBJECTVALUE1 to OBJECTVALUE4
Security	PSOPRDEFN, PSROLEUSER, PSROLECLASS, PSAUTHITEM	OPRID, ROLENAME, CLASSID, MENUNAME, BARITEMNAME

Most definition tables also carry LASTUPDDTTM and LASTUPDOPRID, which record when a definition last changed and who changed it.

Records

Record types

PSRECDEFN.RECTYPE says what kind of record a definition is:

RECTYPE	Record type	Notes
0	SQL table	A table in the database
1	SQL view	A view in the database; its SQL is stored as a SQL definition
2	Derived/work record	Working storage in the component buffer, not in the database
3	Subrecord	A group of fields included in other records, not in the database
5	Dynamic view	Its SQL runs at runtime; no view is created in the database
6	Query view	Defined with PeopleSoft Query
7	Temporary table	Working tables for batch programs such as Application Engine

Table names

A record’s database table is named PS_ plus the record name, so the JOB record is stored in PS_JOB. When SQLTABLENAME is filled in, that name is used instead. That’s why the PeopleTools tables themselves, such as PSRECDEFN, have no PS_ prefix.

Fields

A record’s fields are rows in PSRECFIELD, ordered by FIELDNUM. Their data types come from the field definitions in PSDBFIELD:

FIELDTYPE	Type
0	Character
1	Long character
2	Number
3	Signed number
4	Date
5	Time
6	DateTime
8	Image
9	Image reference

A record can include the fields of a subrecord. The subrecord appears in PSRECFIELD as a single row with its SUBRECORD flag set to Y and its name in FIELDNAME, so the server expands subrecords recursively to show a record’s full list of fields.

Keys and field properties

A record field’s key, required, and edit settings are packed into one number, PSRECFIELD.USEEDIT, where each bit is a separate flag. Some of the bits:

Bit value	Meaning
1	Key
2	Duplicate order key
4	System maintained
256	Required
512	Translate table edit
8, 128, 1024	Audit on add, change, and delete

Testing a bit takes different SQL on each database platform, which is one reason the server keeps a version of every query per platform:

Oracle Database

Key fields of the JOB record

SELECT FIELDNAME
  FROM PSRECFIELD
 WHERE RECNAME = 'JOB'
   AND BITAND(USEEDIT, 1) > 0
 ORDER BY FIELDNUM;

Microsoft SQL Server

Key fields of the JOB record

SELECT FIELDNAME
  FROM PSRECFIELD
 WHERE RECNAME = 'JOB'
   AND (USEEDIT & 1) > 0
 ORDER BY FIELDNUM;

Effective dating

Many PeopleSoft tables are effective-dated. Instead of updating a row, you insert a new one with a later EFFDT, and the current row is the latest one dated on or before today. Tables such as JOB add EFFSEQ to order several changes made on the same day, and EFF_STATUS marks a row as active or inactive.

Effective-dated SQL is easy to get subtly wrong, for example by leaving a key out of a subquery or forgetting EFFSEQ. Because describe_record returns a record’s keys, the LLM knows exactly which columns to correlate:

The current row of every job assignment (Oracle Database)

SELECT J.EMPLID, J.EMPL_RCD, J.DEPTID, J.JOBCODE
  FROM PS_JOB J
 WHERE J.EFFDT = (SELECT MAX(J1.EFFDT)
                    FROM PS_JOB J1
                   WHERE J1.EMPLID = J.EMPLID
                     AND J1.EMPL_RCD = J.EMPL_RCD
                     AND J1.EFFDT <= SYSDATE)
   AND J.EFFSEQ = (SELECT MAX(J2.EFFSEQ)
                     FROM PS_JOB J2
                    WHERE J2.EMPLID = J.EMPLID
                      AND J2.EMPL_RCD = J.EMPL_RCD
                      AND J2.EFFDT = J.EFFDT);

In PeopleCode and Application Engine SQL, the %EffDtCheck meta-SQL construct generates the EFFDT subquery for you, and %CurrentDateIn supplies today’s date on any platform.

Components, pages, and menus

A component is identified by its name and its market, such as JOB_DATA in market GBL:

• PSPNLGRPDEFN holds the component itself, including its search record (SEARCHRECNAME).
• PSPNLGROUP lists the component’s pages in order, with their labels and whether each is hidden.
• PSPNLFIELD lists every field on each page by RECNAME and FIELDNAME. Subpages appear as rows with SUBPNLNAME filled in.
• PSMENUITEM registers the component on a menu, and PSPRSMDEFN holds the portal content reference that users navigate to. Its PORTAL_URI_SEG1, PORTAL_URI_SEG2, and PORTAL_URI_SEG3 columns hold the menu, component, and market.

Pages in the Job Data component

SELECT PNLNAME, ITEMLABEL, HIDDEN
  FROM PSPNLGROUP
 WHERE PNLGRPNAME = 'JOB_DATA'
   AND MARKET = 'GBL'
 ORDER BY SUBITEMNUM;

PeopleCode

PeopleCode is stored in two tables:

• PSPCMPROG holds each program in the tokenized binary form that PeopleTools uses, split across rows by PROGSEQ.
• PSPCMTXT holds the same programs as plain text in PCTEXT, and has since PeopleTools 8.52. It’s what makes PeopleCode searchable with SQL.

PSPCMTXT can be incomplete

PSPCMTXT isn’t guaranteed to be a complete copy of PSPCMPROG: some ways of copying definitions between databases can leave its text missing or out of date. AI Assist compares the two tables and tells you when a program’s text is missing, rather than quietly returning a partial answer.

How a program is identified

Each PeopleCode program is identified by up to seven pairs of columns. OBJECTIDn holds a code for what kind of object each level is, and OBJECTVALUEn holds its name. The codes you’ll meet most often:

Code	Object
1	Record
2	Field
3, 4, 5	Menu, bar name, and item name
9	Page
10	Component
12	Event, such as FieldChange or SavePreChange
20	Database type
21	Effective date
39	Market
60	Message
66	Application Engine program
74	Component interface
77	Section
78	Step
104	Application package
105–107	Application class path

Read left to right, the codes give each program’s full address:

Program	Key path
Record field PeopleCode	Record (1) → Field (2) → Event (12)
Component record field PeopleCode	Component (10) → Market (39) → Record (1) → Field (2) → Event (12)
Component PeopleCode	Component (10) → Market (39) → Event (12)
Page PeopleCode	Page (9) → Event (12)
Application Engine PeopleCode	Program (66) → Section (77) → Market (39) → Database type (20) → Effective date (21) → Step (78) → Event (12)

For example, the FieldChange program on the DEPTID field of the JOB record is the row with OBJECTVALUE1 = 'JOB', OBJECTVALUE2 = 'DEPTID', and OBJECTVALUE3 = 'FieldChange'.

Searching PeopleCode

A plain-text search is a LIKE against PCTEXT. Remember that _ is a wildcard in LIKE, so names such as VOUCHER_LINE need an escape character:

Programs that mention the VOUCHER_LINE record (Oracle Database)

SELECT OBJECTVALUE1, OBJECTVALUE2, OBJECTVALUE3, OBJECTVALUE4, OBJECTVALUE5
  FROM PSPCMTXT
 WHERE UPPER(PCTEXT) LIKE '%VOUCHER\_LINE%' ESCAPE '\';

Long programs are split into several rows, and a search term that happens to straddle two rows won’t match a simple LIKE. The search_code tool accounts for this, and it searches SQL definitions at the same time.

Event order

When a component runs, the Component Processor fires PeopleCode events in a fixed order, which is why describe_component returns programs grouped by event, in this order:

• Building the component: SearchInit, SearchSave, RowSelect, PreBuild, FieldDefault, FieldFormula, RowInit, PostBuild, Activate
• Changing a field: FieldEdit, FieldChange
• Saving: SaveEdit, SavePreChange, Workflow, SavePostChange

SQL definitions

SQL objects, the SQL behind SQL views, and Application Engine SQL all live in two tables. PSSQLDEFN has one row per definition, and PSSQLTEXTDEFN holds the text in SQLTEXT, split across rows by SEQNUM when a statement is long. SQLTYPE says what kind of SQL it is:

SQLTYPE	Contents
0	SQL object
1	Application Engine SQL action
2	The SQL of a SQL view, stored under the record’s name

DBTYPE holds platform-specific versions of the same statement. A blank value is the generic version, and codes such as 2 (Oracle Database) and 7 (Microsoft SQL Server) hold platform-specific versions. The server returns the version that applies to your database.

Application Engine

An Application Engine program is a hierarchy spread across four tables:

• PSAEAPPLDEFN holds the program (AE_APPLID) and its properties.
• PSAESECTDEFN holds its sections, which can vary by market, database platform, and effective date.
• PSAESTEPDEFN holds the steps in each section, in sequence.
• PSAESTMTDEFN holds the actions in each step. AE_STMT_TYPE identifies the kind of action: SQL, PeopleCode, Do When, Do While, Do Select, Do Until, Call Section, Log Message, or XSLT.

SQL actions keep their text in PSSQLTEXTDEFN with SQLTYPE 1. PeopleCode actions keep theirs in PSPCMPROG and PSPCMTXT, under the key path shown in the table above. describe_app_engine brings all of it together into one outline of the program.

Security

Access to a component is granted through a chain of tables:

User (PSOPRDEFN) → roles (PSROLEUSER) → permission lists (PSROLECLASS) → menu items and pages (PSAUTHITEM)

PSAUTHITEM gives a permission list access to a page of a menu item, identified by MENUNAME, BARNAME, BARITEMNAME, and PNLITEMNAME. It also stores the actions allowed (AUTHORIZEDACTIONS) and whether access is display-only (DISPLAYONLY). Because the menu item is the link between a component and its security, a query for who can open a component starts from PSMENUITEM:

Roles and permission lists that grant access to Job Data

SELECT DISTINCT RC.ROLENAME, A.CLASSID
  FROM PSMENUITEM M
  JOIN PSAUTHITEM A
    ON A.MENUNAME = M.MENUNAME
   AND A.BARNAME = M.BARNAME
   AND A.BARITEMNAME = M.ITEMNAME
  JOIN PSROLECLASS RC
    ON RC.CLASSID = A.CLASSID
 WHERE M.PNLGRPNAME = 'JOB_DATA'
 ORDER BY RC.ROLENAME, A.CLASSID;

Finding customizations

Delivered definitions usually carry LASTUPDOPRID = 'PPLSOFT'. Definitions your team has changed carry your developers’ user IDs and more recent LASTUPDDTTM values, which makes a quick first pass at finding customizations:

Records last changed by someone other than PPLSOFT

SELECT RECNAME, LASTUPDOPRID, LASTUPDDTTM
  FROM PSRECDEFN
 WHERE LASTUPDOPRID <> 'PPLSOFT'
 ORDER BY LASTUPDDTTM DESC;

The same check works on PSPNLDEFN, PSPNLGRPDEFN, PSPCMPROG, and the other definition tables.