# PeopleTools Metadata Tables Reference: PSRECDEFN, PSRECFIELD, PSPNLDEFN and More > Source: https://netray.co/tools/peoplesoft-peopletools-metadata-tables-reference > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Free Tools ## Short answer Searchable reference of the PeopleTools system tables that store record, field, page, component, PeopleCode, SQL, translate, message, query, project and portal definitions. ## Category oracle ## Erp Oracle PeopleSoft ## Audience - PeopleSoft developer - PeopleSoft administrator - PeopleSoft DBA ## Tool **Kind:** worksheet **Mode:** reference **Columns:** #### Table **Key:** table **Type:** text #### Definition type **Key:** deftype **Type:** text #### Key columns **Key:** keys **Type:** text #### Useful columns **Key:** useful **Type:** text #### Typical query **Key:** query **Type:** text **Rows:** **Table:** PSRECDEFN **Deftype:** Record definition **Keys:** RECNAME **Useful:** 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 **Query:** SELECT RECNAME, RECTYPE, SQLTABLENAME FROM PSRECDEFN WHERE RECNAME LIKE 'JOB%' **Table:** PSRECFIELD **Deftype:** Fields on a record (subrecords not expanded) **Keys:** RECNAME, FIELDNAME **Useful:** FIELDNUM, USEEDIT (key, required, edit flags bitmask), EDITTABLE (prompt table), DEFRECNAME, DEFFIELDNAME, SUBRECORD **Query:** SELECT FIELDNAME, FIELDNUM FROM PSRECFIELD WHERE RECNAME = 'JOB' ORDER BY FIELDNUM **Table:** PSRECFIELDDB **Deftype:** Fields on a record with subrecords expanded **Keys:** RECNAME, FIELDNAME **Useful:** Same columns as PSRECFIELD, one row per physical column **Query:** Use instead of PSRECFIELD when you need the real column list of PS_ tables **Table:** PSDBFIELD **Deftype:** Field definition **Keys:** FIELDNAME **Useful:** FIELDTYPE (0 character, 1 long character, 2 number, 3 signed number, 4 date, 5 time, 6 datetime), LENGTH, DECIMALPOS, FORMAT **Query:** SELECT FIELDNAME, FIELDTYPE, LENGTH FROM PSDBFIELD WHERE FIELDNAME = 'EMPLID' **Table:** PSDBFLDLABL **Deftype:** Field labels **Keys:** FIELDNAME, LABEL_ID **Useful:** LONGNAME, SHORTNAME, DEFAULT_LABEL **Query:** SELECT LONGNAME FROM PSDBFLDLABL WHERE FIELDNAME = 'DEPTID' AND DEFAULT_LABEL = 1 **Table:** PSKEYDEFN **Deftype:** Index key fields **Keys:** RECNAME, INDEXID, KEYPOSN **Useful:** FIELDNAME, ASCDESC **Query:** SELECT INDEXID, KEYPOSN, FIELDNAME FROM PSKEYDEFN WHERE RECNAME = 'JOB' ORDER BY INDEXID, KEYPOSN **Table:** PSINDEXDEFN **Deftype:** Index definitions **Keys:** RECNAME, INDEXID **Useful:** INDEXTYPE, UNIQUEFLAG **Query:** Join to PSKEYDEFN on RECNAME and INDEXID **Table:** PSPNLDEFN **Deftype:** Page definition **Keys:** PNLNAME **Useful:** PNLTYPE, DESCR, LASTUPDOPRID, LASTUPDDTTM **Query:** SELECT PNLNAME, DESCR FROM PSPNLDEFN WHERE PNLNAME LIKE 'JOB_DATA%' **Table:** PSPNLFIELD **Deftype:** Fields and controls on a page **Keys:** PNLNAME, PNLFLDID **Useful:** RECNAME, FIELDNAME, FIELDTYPE, LBLTEXT, SUBPNLNAME, OCCURSLEVEL **Query:** SELECT DISTINCT PNLNAME FROM PSPNLFIELD WHERE RECNAME = 'JOB' AND FIELDNAME = 'DEPTID' **Table:** PSPNLGRPDEFN **Deftype:** Component definition **Keys:** PNLGRPNAME, MARKET **Useful:** SEARCHRECNAME, ADDSRCHRECNAME, ACTIONS, DESCR **Query:** SELECT SEARCHRECNAME FROM PSPNLGRPDEFN WHERE PNLGRPNAME = 'JOB_DATA' **Table:** PSPNLGROUP **Deftype:** Pages in a component **Keys:** PNLGRPNAME, MARKET, SUBITEMNUM **Useful:** PNLNAME, ITEMLABEL, HIDDEN **Query:** SELECT SUBITEMNUM, PNLNAME, ITEMLABEL FROM PSPNLGROUP WHERE PNLGRPNAME = 'JOB_DATA' ORDER BY SUBITEMNUM **Table:** PSMENUDEFN **Deftype:** Menu definition **Keys:** MENUNAME **Useful:** MENUTYPE, DESCR **Query:** SELECT MENUNAME, DESCR FROM PSMENUDEFN WHERE MENUNAME LIKE 'ADMINISTER%' **Table:** PSMENUITEM **Deftype:** Menu items (components registered on menus) **Keys:** MENUNAME, BARNAME, ITEMNAME **Useful:** PNLGRPNAME, MARKET, ITEMLABEL **Query:** SELECT MENUNAME, BARNAME FROM PSMENUITEM WHERE PNLGRPNAME = 'JOB_DATA' **Table:** PSPCMPROG **Deftype:** PeopleCode programs (compiled and metadata) **Keys:** OBJECTID1-7, OBJECTVALUE1-7, PROGSEQ **Useful:** OBJECTVALUE1 is the record, component or package; for record field PeopleCode OBJECTVALUE2 is the field and OBJECTVALUE3 the event; LASTUPDOPRID, LASTUPDDTTM **Query:** SELECT OBJECTVALUE1, OBJECTVALUE2, OBJECTVALUE3 FROM PSPCMPROG WHERE OBJECTVALUE1 = 'JOB' ORDER BY 2, 3 **Table:** PSPCMTXT **Deftype:** PeopleCode source text (PeopleTools 8.52 and later) **Keys:** Same OBJECTID/OBJECTVALUE keys as PSPCMPROG, PROGSEQ **Useful:** PCTEXT (CLOB source text) **Query:** Search PCTEXT for a function or field name to find where it is used **Table:** PSPCMNAME **Deftype:** Definitions referenced by PeopleCode **Keys:** OBJECTID1-7, OBJECTVALUE1-7, NAMENUM **Useful:** RECNAME, REFNAME **Query:** Find PeopleCode that references a record or field before changing it **Table:** PSSQLDEFN **Deftype:** SQL definition header **Keys:** SQLID, SQLTYPE **Useful:** SQLTYPE separates standalone SQL objects, Application Engine step SQL and view SQL; LASTUPDOPRID **Query:** SELECT SQLID, SQLTYPE FROM PSSQLDEFN WHERE SQLID LIKE 'HR_%' **Table:** PSSQLTEXTDEFN **Deftype:** SQL text **Keys:** SQLID, SQLTYPE, MARKET, DBTYPE, EFFDT, SEQNUM **Useful:** SQLTEXT **Query:** Read view text: SQLID equals the view record name **Table:** PSXLATDEFN **Deftype:** Translate field header **Keys:** FIELDNAME **Useful:** VERSION, LASTUPDOPRID **Query:** Lists fields that have translate values **Table:** PSXLATITEM **Deftype:** Translate values **Keys:** FIELDNAME, FIELDVALUE, EFFDT **Useful:** EFF_STATUS, XLATLONGNAME, XLATSHORTNAME **Query:** SELECT FIELDVALUE, XLATLONGNAME FROM PSXLATITEM WHERE FIELDNAME = 'EMPL_STATUS' **Table:** PSMSGSETDEFN **Deftype:** Message set **Keys:** MESSAGE_SET_NBR **Useful:** DESCR **Query:** Lists message sets; sets below 20000 are delivered by Oracle **Table:** PSMSGCATDEFN **Deftype:** Message catalog entries **Keys:** MESSAGE_SET_NBR, MESSAGE_NBR **Useful:** MESSAGE_TEXT, DESCRLONG, MSG_SEVERITY **Query:** SELECT MESSAGE_TEXT, DESCRLONG FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 124 AND MESSAGE_NBR = 50 **Table:** PSPROJECTDEFN **Deftype:** Project header **Keys:** PROJECTNAME **Useful:** PROJECTDESCR, LASTUPDOPRID, LASTUPDDTTM **Query:** SELECT PROJECTNAME FROM PSPROJECTDEFN WHERE PROJECTNAME LIKE 'CHG%' **Table:** PSPROJECTITEM **Deftype:** Definitions in a project **Keys:** PROJECTNAME, OBJECTTYPE, OBJECTID1-4, OBJECTVALUE1-4 **Useful:** UPGRADEACTION, TAKEACTION, COPYDONE **Query:** SELECT OBJECTTYPE, OBJECTVALUE1, OBJECTVALUE2 FROM PSPROJECTITEM WHERE PROJECTNAME = 'CHG0042' **Table:** PSSTATUS **Deftype:** Database status (one row) **Keys:** None **Useful:** TOOLSREL, UNICODE_ENABLED **Query:** SELECT TOOLSREL FROM PSSTATUS **Table:** PSQRYDEFN **Deftype:** PS Query header **Keys:** OPRID, QRYNAME **Useful:** QRYTYPE, DESCR, LASTUPDOPRID, LASTUPDDTTM **Query:** Public queries have OPRID = ' ' (blank) **Table:** PSQRYRECORD **Deftype:** Records used by a query **Keys:** OPRID, QRYNAME, SELNUM, RCDNUM **Useful:** RECNAME, CORRNAME **Query:** SELECT DISTINCT QRYNAME FROM PSQRYRECORD WHERE RECNAME = 'JOB' **Table:** PSQRYFIELD **Deftype:** Fields selected by a query **Keys:** OPRID, QRYNAME, SELNUM, FLDNUM **Useful:** RECNAME, FIELDNAME, HEADING **Query:** Find queries that output a field before changing it **Table:** PSQRYCRITERIA **Deftype:** Query criteria **Keys:** OPRID, QRYNAME, SELNUM, CRTNUM **Useful:** Left and right expressions and condition type **Query:** Review filters on a query **Table:** PSPRSMDEFN **Deftype:** Portal registry (folders and content references) **Keys:** PORTAL_NAME, PORTAL_REFTYPE, PORTAL_OBJNAME **Useful:** PORTAL_PRNTOBJNAME, PORTAL_LABEL, PORTAL_URI_SEG1 (menu), PORTAL_URI_SEG2 (component), PORTAL_URI_SEG3 (market) **Query:** Walk PORTAL_PRNTOBJNAME up to PORTAL_ROOT_OBJECT to build the navigation path **Table:** PSCONTDEFN **Deftype:** Image and HTML definitions **Keys:** CONTNAME, ALTCONTNUM **Useful:** Content type and format columns, LASTUPDOPRID **Query:** Lists images and HTML objects stored in the database **Table:** PSAEAPPLDEFN **Deftype:** Application Engine program **Keys:** AE_APPLID **Useful:** DESCR, LASTUPDOPRID, LASTUPDDTTM **Query:** SELECT AE_APPLID, DESCR FROM PSAEAPPLDEFN WHERE AE_APPLID LIKE 'HR%' **Table:** PSAESTEPDEFN **Deftype:** Application Engine steps **Keys:** AE_APPLID, AE_SECTION, MARKET, DBTYPE, EFFDT, AE_STEP **Useful:** AE_SEQ_NUM, DESCR **Query:** List steps of a section in order of AE_SEQ_NUM **Table:** PSAUTHITEM **Deftype:** Permission list access to menu items and pages **Keys:** CLASSID, MENUNAME, BARNAME, BARITEMNAME, PNLITEMNAME **Useful:** DISPLAYONLY, AUTHORIZEDACTIONS **Query:** SELECT CLASSID FROM PSAUTHITEM WHERE BARITEMNAME = 'JOB_DATA' **Table:** PSCLASSDEFN **Deftype:** Permission list **Keys:** CLASSID **Useful:** CLASSDEFNDESC **Query:** Permission list descriptions **Table:** PSROLECLASS **Deftype:** Permission lists in a role **Keys:** ROLENAME, CLASSID **Useful:** None beyond keys **Query:** Join PSAUTHITEM to roles **Table:** PSROLEUSER **Deftype:** Roles granted to a user **Keys:** ROLEUSER, ROLENAME **Useful:** DYNAMIC_SW **Query:** SELECT ROLENAME FROM PSROLEUSER WHERE ROLEUSER = 'PS' **Table:** PSOPRDEFN **Deftype:** User profile **Keys:** OPRID **Useful:** OPRDEFNDESC, EMPLID, ACCTLOCK, OPRCLASS (primary permission list), ROWSECCLASS **Query:** SELECT OPRID, ACCTLOCK FROM PSOPRDEFN WHERE EMPLID = 'KU0001' **Notes:** 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. ## Content **Intro:** 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. **Sections:** #### How to use it **Body:** 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. **Bullets:** - 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 **Body:** 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? **Body:** 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 **Body:** 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. **Bullets:** - Check TOOLSREL before relying on a column - Public queries have a blank OPRID - Translate values are effective dated **Cta:** 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. **Faq:** #### Which PeopleTools table stores record definitions? **Answer:** 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? **Answer:** 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? **Answer:** 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? **Answer:** 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. ## Search terms peopletools tables list, peopletools table for record definitions, peopletools table containing table fields, peopletools psrecdefn fields, peoplesoft message catalog table, peoplesoft translate table, peoplesoft peoplecode table, psrecdefn rectype values --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json