OracleFree referencePDF, Excel, Word exportOracle PeopleSoftAll Oracle PeopleSoft tools

PeopleTools Metadata Tables Reference: PSRECDEFN, PSRECFIELD, PSPNLDEFN and More

A searchable reference of PeopleTools metadata tables, covering records, fields, pages, components, PeopleCode, SQL, translates, message catalog, projects, queries, portal registry and security. It applies to PeopleTools 8.5x and 8.6x databases.

Download:

Table	Definition type	Key columns	Useful columns	Typical query
PSRECDEFN	Record definition	RECNAME	RECTYPE (0 SQL table, 1 SQL view, 2 derived/work, 3 subrecord, 5 dynamic view, 6 query view, 7 temporary table), SQLTABLENAME, RECDESCR, FIELDCOUNT, PARENTRECNAME, LASTUPDOPRID, LASTUPDDTTM	SELECT RECNAME, RECTYPE, SQLTABLENAME FROM PSRECDEFN WHERE RECNAME LIKE 'JOB%'
PSRECFIELD	Fields on a record (subrecords not expanded)	RECNAME, FIELDNAME	FIELDNUM, USEEDIT (key, required, edit flags bitmask), EDITTABLE (prompt table), DEFRECNAME, DEFFIELDNAME, SUBRECORD	SELECT FIELDNAME, FIELDNUM FROM PSRECFIELD WHERE RECNAME = 'JOB' ORDER BY FIELDNUM
PSRECFIELDDB	Fields on a record with subrecords expanded	RECNAME, FIELDNAME	Same columns as PSRECFIELD, one row per physical column	Use instead of PSRECFIELD when you need the real column list of PS_ tables
PSDBFIELD	Field definition	FIELDNAME	FIELDTYPE (0 character, 1 long character, 2 number, 3 signed number, 4 date, 5 time, 6 datetime), LENGTH, DECIMALPOS, FORMAT	SELECT FIELDNAME, FIELDTYPE, LENGTH FROM PSDBFIELD WHERE FIELDNAME = 'EMPLID'
PSDBFLDLABL	Field labels	FIELDNAME, LABEL_ID	LONGNAME, SHORTNAME, DEFAULT_LABEL	SELECT LONGNAME FROM PSDBFLDLABL WHERE FIELDNAME = 'DEPTID' AND DEFAULT_LABEL = 1
PSKEYDEFN	Index key fields	RECNAME, INDEXID, KEYPOSN	FIELDNAME, ASCDESC	SELECT INDEXID, KEYPOSN, FIELDNAME FROM PSKEYDEFN WHERE RECNAME = 'JOB' ORDER BY INDEXID, KEYPOSN
PSINDEXDEFN	Index definitions	RECNAME, INDEXID	INDEXTYPE, UNIQUEFLAG	Join to PSKEYDEFN on RECNAME and INDEXID
PSPNLDEFN	Page definition	PNLNAME	PNLTYPE, DESCR, LASTUPDOPRID, LASTUPDDTTM	SELECT PNLNAME, DESCR FROM PSPNLDEFN WHERE PNLNAME LIKE 'JOB_DATA%'
PSPNLFIELD	Fields and controls on a page	PNLNAME, PNLFLDID	RECNAME, FIELDNAME, FIELDTYPE, LBLTEXT, SUBPNLNAME, OCCURSLEVEL	SELECT DISTINCT PNLNAME FROM PSPNLFIELD WHERE RECNAME = 'JOB' AND FIELDNAME = 'DEPTID'
PSPNLGRPDEFN	Component definition	PNLGRPNAME, MARKET	SEARCHRECNAME, ADDSRCHRECNAME, ACTIONS, DESCR	SELECT SEARCHRECNAME FROM PSPNLGRPDEFN WHERE PNLGRPNAME = 'JOB_DATA'
PSPNLGROUP	Pages in a component	PNLGRPNAME, MARKET, SUBITEMNUM	PNLNAME, ITEMLABEL, HIDDEN	SELECT SUBITEMNUM, PNLNAME, ITEMLABEL FROM PSPNLGROUP WHERE PNLGRPNAME = 'JOB_DATA' ORDER BY SUBITEMNUM
PSMENUDEFN	Menu definition	MENUNAME	MENUTYPE, DESCR	SELECT MENUNAME, DESCR FROM PSMENUDEFN WHERE MENUNAME LIKE 'ADMINISTER%'
PSMENUITEM	Menu items (components registered on menus)	MENUNAME, BARNAME, ITEMNAME	PNLGRPNAME, MARKET, ITEMLABEL	SELECT MENUNAME, BARNAME FROM PSMENUITEM WHERE PNLGRPNAME = 'JOB_DATA'
PSPCMPROG	PeopleCode programs (compiled and metadata)	OBJECTID1-7, OBJECTVALUE1-7, PROGSEQ	OBJECTVALUE1 is the record, component or package; for record field PeopleCode OBJECTVALUE2 is the field and OBJECTVALUE3 the event; LASTUPDOPRID, LASTUPDDTTM	SELECT OBJECTVALUE1, OBJECTVALUE2, OBJECTVALUE3 FROM PSPCMPROG WHERE OBJECTVALUE1 = 'JOB' ORDER BY 2, 3
PSPCMTXT	PeopleCode source text (PeopleTools 8.52 and later)	Same OBJECTID/OBJECTVALUE keys as PSPCMPROG, PROGSEQ	PCTEXT (CLOB source text)	Search PCTEXT for a function or field name to find where it is used
PSPCMNAME	Definitions referenced by PeopleCode	OBJECTID1-7, OBJECTVALUE1-7, NAMENUM	RECNAME, REFNAME	Find PeopleCode that references a record or field before changing it
PSSQLDEFN	SQL definition header	SQLID, SQLTYPE	SQLTYPE separates standalone SQL objects, Application Engine step SQL and view SQL; LASTUPDOPRID	SELECT SQLID, SQLTYPE FROM PSSQLDEFN WHERE SQLID LIKE 'HR_%'
PSSQLTEXTDEFN	SQL text	SQLID, SQLTYPE, MARKET, DBTYPE, EFFDT, SEQNUM	SQLTEXT	Read view text: SQLID equals the view record name
PSXLATDEFN	Translate field header	FIELDNAME	VERSION, LASTUPDOPRID	Lists fields that have translate values
PSXLATITEM	Translate values	FIELDNAME, FIELDVALUE, EFFDT	EFF_STATUS, XLATLONGNAME, XLATSHORTNAME	SELECT FIELDVALUE, XLATLONGNAME FROM PSXLATITEM WHERE FIELDNAME = 'EMPL_STATUS'
PSMSGSETDEFN	Message set	MESSAGE_SET_NBR	DESCR	Lists message sets; sets below 20000 are delivered by Oracle
PSMSGCATDEFN	Message catalog entries	MESSAGE_SET_NBR, MESSAGE_NBR	MESSAGE_TEXT, DESCRLONG, MSG_SEVERITY	SELECT MESSAGE_TEXT, DESCRLONG FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 124 AND MESSAGE_NBR = 50
PSPROJECTDEFN	Project header	PROJECTNAME	PROJECTDESCR, LASTUPDOPRID, LASTUPDDTTM	SELECT PROJECTNAME FROM PSPROJECTDEFN WHERE PROJECTNAME LIKE 'CHG%'
PSPROJECTITEM	Definitions in a project	PROJECTNAME, OBJECTTYPE, OBJECTID1-4, OBJECTVALUE1-4	UPGRADEACTION, TAKEACTION, COPYDONE	SELECT OBJECTTYPE, OBJECTVALUE1, OBJECTVALUE2 FROM PSPROJECTITEM WHERE PROJECTNAME = 'CHG0042'
PSSTATUS	Database status (one row)	None	TOOLSREL, UNICODE_ENABLED	SELECT TOOLSREL FROM PSSTATUS
PSQRYDEFN	PS Query header	OPRID, QRYNAME	QRYTYPE, DESCR, LASTUPDOPRID, LASTUPDDTTM	Public queries have OPRID = ' ' (blank)
PSQRYRECORD	Records used by a query	OPRID, QRYNAME, SELNUM, RCDNUM	RECNAME, CORRNAME	SELECT DISTINCT QRYNAME FROM PSQRYRECORD WHERE RECNAME = 'JOB'
PSQRYFIELD	Fields selected by a query	OPRID, QRYNAME, SELNUM, FLDNUM	RECNAME, FIELDNAME, HEADING	Find queries that output a field before changing it
PSQRYCRITERIA	Query criteria	OPRID, QRYNAME, SELNUM, CRTNUM	Left and right expressions and condition type	Review filters on a query
PSPRSMDEFN	Portal registry (folders and content references)	PORTAL_NAME, PORTAL_REFTYPE, PORTAL_OBJNAME	PORTAL_PRNTOBJNAME, PORTAL_LABEL, PORTAL_URI_SEG1 (menu), PORTAL_URI_SEG2 (component), PORTAL_URI_SEG3 (market)	Walk PORTAL_PRNTOBJNAME up to PORTAL_ROOT_OBJECT to build the navigation path
PSCONTDEFN	Image and HTML definitions	CONTNAME, ALTCONTNUM	Content type and format columns, LASTUPDOPRID	Lists images and HTML objects stored in the database
PSAEAPPLDEFN	Application Engine program	AE_APPLID	DESCR, LASTUPDOPRID, LASTUPDDTTM	SELECT AE_APPLID, DESCR FROM PSAEAPPLDEFN WHERE AE_APPLID LIKE 'HR%'
PSAESTEPDEFN	Application Engine steps	AE_APPLID, AE_SECTION, MARKET, DBTYPE, EFFDT, AE_STEP	AE_SEQ_NUM, DESCR	List steps of a section in order of AE_SEQ_NUM
PSAUTHITEM	Permission list access to menu items and pages	CLASSID, MENUNAME, BARNAME, BARITEMNAME, PNLITEMNAME	DISPLAYONLY, AUTHORIZEDACTIONS	SELECT CLASSID FROM PSAUTHITEM WHERE BARITEMNAME = 'JOB_DATA'
PSCLASSDEFN	Permission list	CLASSID	CLASSDEFNDESC	Permission list descriptions
PSROLECLASS	Permission lists in a role	ROLENAME, CLASSID	None beyond keys	Join PSAUTHITEM to roles
PSROLEUSER	Roles granted to a user	ROLEUSER, ROLENAME	DYNAMIC_SW	SELECT ROLENAME FROM PSROLEUSER WHERE ROLEUSER = 'PS'
PSOPRDEFN	User profile	OPRID	OPRDEFNDESC, EMPLID, ACCTLOCK, OPRCLASS (primary permission list), ROWSECCLASS	SELECT OPRID, ACCTLOCK FROM PSOPRDEFN WHERE EMPLID = 'KU0001'

Physical table name is PS_ plus RECNAME (for example record JOB is table PS_JOB) unless PSRECDEFN.SQLTABLENAME is set, in which case that name is used; PeopleTools tables like PSRECDEFN have SQLTABLENAME set and no PS_ prefix. Query these tables read-only: change definitions only through Application Designer or delivered utilities. Columns listed are the commonly used ones, not every column.

Get an impact-analysis query pack for your tools release

Tell us your PeopleTools release and database platform and we will send a checked set of where-used queries for records, fields and PeopleCode.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Search by what you are trying to find: a record, a field, a page, a translate value or a message. Each row gives the table, its keys, the columns you will actually select and a starter query. Typical uses are impact analysis before a change (which pages, PeopleCode and queries use this field), documenting a customisation, and answering support questions without opening Application Designer. Export the list to Excel to keep it beside your SQL client.

Run read-only queries with a reporting account
Start with PSRECFIELDDB for real column lists
Use PSPCMTXT to search PeopleCode text on 8.52 and later

How the metadata fits together

A record is defined in PSRECDEFN, its fields in PSRECFIELD, and each field's type and length in PSDBFIELD. Pages (PSPNLDEFN, PSPNLFIELD) place record fields on screen. Components (PSPNLGRPDEFN) group pages listed in PSPNLGROUP and have a search record. Components are registered on menus (PSMENUITEM) and in the portal registry (PSPRSMDEFN), and security grants access through permission lists (PSAUTHITEM), roles (PSROLECLASS) and users (PSROLEUSER). PeopleCode is keyed by up to seven object identifiers, so the same table holds record field, component and application package code.

Worked example: where is DEPTID used on JOB?

Before changing a prompt on JOB.DEPTID, find every page that shows it: SELECT DISTINCT PNLNAME FROM PSPNLFIELD WHERE RECNAME = 'JOB' AND FIELDNAME = 'DEPTID'. Then find the components using those pages through PSPNLGROUP, and PeopleCode on the field: SELECT OBJECTVALUE3 FROM PSPCMPROG WHERE OBJECTVALUE1 = 'JOB' AND OBJECTVALUE2 = 'DEPTID'. Finally list queries that select it from PSQRYFIELD. The result is a change impact list you can attach to the ticket.

Version differences and gotchas

From PeopleTools 8.52, PeopleCode source text is stored in PSPCMTXT as readable text; earlier releases keep it only in the binary PROGTXT column of PSPCMPROG, which is why older scripts decode long binary data. PSRECFIELD does not expand subrecords, so a record built mostly from subrecords looks short; use PSRECFIELDDB. Some records, including every PeopleTools record, have a SQLTABLENAME override, so do not assume the PS_ prefix. PSSTATUS.TOOLSREL tells you the tools release of the database, which matters before running any script found online.

Check TOOLSREL before relying on a column
Public queries have a blank OPRID
Translate values are effective dated

Frequently Asked Questions

Which PeopleTools table stores record definitions?

PSRECDEFN stores record definitions, one row per record keyed by RECNAME. RECTYPE gives the record type: 0 SQL table, 1 SQL view, 2 derived or work record, 3 subrecord, 5 dynamic view, 6 query view and 7 temporary table. SQLTABLENAME holds an override for the physical table name.

Which PeopleTools table contains the fields of a table?

PSRECFIELD lists the fields defined directly on a record, including subrecord placeholders. PSRECFIELDDB lists the fields with subrecords expanded, which matches the physical columns of the PS_ table. Join to PSDBFIELD on FIELDNAME for field type, length and decimal positions.

Where is the PeopleSoft message catalog stored?

Message sets are in PSMSGSETDEFN and individual messages in PSMSGCATDEFN, keyed by MESSAGE_SET_NBR and MESSAGE_NBR. MESSAGE_TEXT is the short text and DESCRLONG the explanation. Message sets from 20000 upward are reserved for customer messages, so put your own messages there.

Which is the PeopleSoft translate table?

Translate values are stored in PSXLATITEM, keyed by FIELDNAME, FIELDVALUE and EFFDT, with XLATLONGNAME and XLATSHORTNAME as the labels and EFF_STATUS for active or inactive. PSXLATDEFN is the header listing fields that have translates. Take the current effective-dated row when decoding a value.

If you want developers and analysts to ask questions about your PeopleSoft metadata in plain English, answered from your own database on your own servers, we can set that up.

Talk to an Expert Explore More Free Tools