PeopleTools Tables

During one of my PeopleTools classes, a student asked for a quick list of the PeopleTools catalog tables. Here is an attempt at this list. The list is organized by type of PeopleTools Defintion:

Projects

Fields

  • PSDBFIELD — Fields in the system
  • PSDBFLDLABL — Field Labels
  • PSDBFIELDLANG
  • PSXLATITEM — Translate Values
  • PSXLATITEMLANG
  • PSDBFLDLABLLANG

Records

  • PSRECDEFN — Record header table (record types)
  • PSRECFIELD — Fields in the record (subrecords not expanded)
  • PSRECFIELDALL — Fields in the record (subrecords expanded)
  • PSKEYDEFN — Indexes
  • PSTBLSPCCAT — Tablespaces
  • PSRECTBLSPC — Records’ tablespace assignments
  • PSRECDEFNLANG — language Record

Pages

(Note: Pages still have the name panels in the PeopleTools table names)

  • PSPNLDEFN — Page header table
  • PSPNLFIELD — Page controls (field types/FIELDTYPE)
  • PSPNLHTMLAREA — Static HTML Areas on Pages
  • PSPNLCNTRLDATA — stores additional attributes for Page controls
  • PSPNLFIELDEXT — stores additional attributes for Page controls
  • PSPTPNLRTEDITOR — new PeopleTools 8.50 table that stores attributes relating to the rich-text editor for long character fields.

Components

(Note: Components still have the name panel group in the PeopleTools table names)

  • PSPNLGRPDEFN — Component header table
  • PSPNLGROUP — Pages in the components

Component Interface

  • PSBCDEFN — header record; one row for each component interface
  • PSBCITEM — one row for each property

Menus

  • PSMENUDEFN — Menu header table
  • PSMENUITEM — Items (components) on the menu

Security

  • PSCLASSDEFN — Permission List header table
  • PSAUTHITEM — Menu items granted security by permission lists
  • PSROLEDEFN — Role header table
  • PSROLECLASS — Permission Lists in roles
  • PSOPRDEFN — User ID header table
  • PSROLEUSER — Roles granted to users
  • PSAUTHBUSCOMP — Access to Component Interfaces
  • PSAUTHWEBLIBVW — Access to just Web Libraries
  • PS_SCRTY_ACC_GRP — Query Security Access Groups
  • PSOPROBJ — Definition security (connects a permission list to a definition group)

Process Scheduler

  • PS_PRCSDEFN — Process Definition Header
  • PS_PRCSTYPEDEFN — Process Type Definition Header
  • PS_PRCSDEFNGRP — Process Group
  • PS_PRCSDEFNPNL — Component
  • PS_PRCSJOBDEFN — Job Header
  • PSPRCSRQST — Process Request Instances
  • PS_PRCSJOBITEM — Job Processes
  • PSREN — stores REN server ports (from here)
  • PS_SERVERCLASS — Server Class
  • PS_PRCS_CAT_TBL — Process Categories
  • PS_SERVERCATEGORY — Server Categories
  • PS_SERVERDEFN — Server Definition Header
  • PSPRCSQUE — Process Queue
  • PSRF_FLIST_TBL — Folder Definition

Portal

  • PSPRSMDEFN — Content References and Folders
  • PSPRUHTABPGLT — Portal User HP Tab Pagelet
  • PSPRUHDEFN — Homepage definition (from here)
  • PSPRUHTAB — Homepage Tab (from here)
  • PSWEBPROFNVP — Web Profile Settings

Change Control

  • PSCHGCTLHIST — shows history of locked definitions with project name, incident, and description
  • PSCHGCTLLOCK — shows definitions that are currently locked
  • PSCOMPOBJDIFF — I think this shows compare report from a Project Compare
  • PSPROJECTMSG — messages from a project copy

Application Engine

  • PSAEAPPLDEFN — header record; 1 row per app engine
  • PSAEAPPLSTATE — state records assigned to app engines
  • PSAEAPPLTEMPTBL — temp tables assigned to app engines
  • PSAESECTDEFN — sections
  • PSAESTEPDEFN — steps
  • PSAESTEPMSGDEFN
  • PSAESTMTDEFN — actions (action types)
  • PSAESECTDTLDEFN — section details
  • PS_AEONLINEINST — should be 100 rows in the table
  • PS_AEINSTANCENBR — should be 100 rows in the table
  • PS_AELOCKMGR — should be 100 rows in the table

List Log Messages

HTML Definitions

  • PSCONTDEFN — header record; last update time, etc.
  • PSCONTENT — stores actual text in the HTML definition

PeopleCode

  • PSPCMPROG — PeopleCode program

SQL Definitions

  • PSSQLDEFN — header record; last update time, etc.
  • PSSQLTEXTDEFN — stores actual text in the SQL definition
  • PS_SQLSTMT_TBL — stores SQL statemernts for COBOL

File Layout Definitions

  • PSFLDDEFN — header record; last update time, etc.
  • PSFLDSEGDEFN — stores the segments for each layout
  • PSFLDFIELDDEFN — stores the fields for each layout
Query
  • PSQRYDEFN — Query Definition Header
  • PSQRYBIND — Query bind variables/parameters
  • PSQRYRECORD — Query records
  • PSQRYFIELD — Fields
  • PSCONQRSDEFN — connected query
  • PSCONQRSMAP — connected query: list of child queries
  • PSCONQRSFLDREL — connected query
  • PSCONQRSRUNCNTR — connected query
  • PSCONQRSRUNPRM — connected query

Workflow
(thanks to jasper–see the comments)

  • APPR_RULE_DETL – Approval Rule Defn Details
  • APPR_RULE_FIELD – Approval Rule Defn Route Cntl
  • APPR_RULE_AMT – Approval Rule Amounts
  • RTE_CNTL_LN – Route Control Profile Line
  • RTE_CNTL_RUSER – RoleUser Route Cntl Profiles
  • RTE_CNTL_TYPE – Route Control Type
  • RTE_CNTL_HDR – Routing Control Type
  • PSWORKLIST — list of work items for each user
  • PS_WF_INSTSTATUS — description of the status
——–
  • PSACTIVITYDEFN — Activity Definition
  • PSBUSPROCDEFN — Business Process Definition
  • PSEVENTDEFN — Event Definition
  • PSEVENTROUTE — Event Route

Timings
(thanks to PeopleSoft Tipster –see his blog)

  • BAT_TIMINGS_LOG
  • BAT_TIMINGS_DTL
  • BAT_TIMINGS_FN

File References

  • PSFILEREDEFN — File references (keys: FILEREFNAME, FILEREFTYPECODE)
EDI Manager
  • PS_ECINMAPFILE — Records
  • PS_ECINMAPREC — Records
  • PS_ECMAPDEFN — EDI Map Definition
  • PS_ECINMAPFLD — Fields in the Records
  • PS_ECINMAPRECFLD — Fields in the Records
  • PS_ECOUTMAPREC
  • PS_ECOUTMAPFLD
Feeds
  • PS_PTFP_FEED
  • PS_PTFP_DATATYPE
  • PS_PTFP_DTYPE_IBSO
  • PS_PTFP_DTYPE_ATTR
  • PS_PTFP_ATTRS
  • PS_PTFP_SETTINGS
  • PS_PTFP_PARMS
  • PS_PTFP_SECURITY
  • PS_PTFP_PVALS
  • PS_PTFP_ADMN_PREF
  • PS_PTFP_USER_PREF

Integration Broker

  • PSMSGPARTS — Message Parts
  • PSMSGVER — Message Version
  • PSMSGREC — Message Record
  • PSOPERATION — Operation
  • PSOPRVERDFN — Operation Versions
  • PSSERVICEOPR — Service Operations
  • PSOPRHDLR — Operation Handlers
  • PSIBRTNGDEFN — Routings
  • PSMSGNODEDEFN — Message Nodes
  • PSRTNGDFNPARM — Routing Parameters
  • PSOPRVERDFNPARM
  • PSQUEUEDEFN
  • PSIBSCMADFN
  • PSOPERATIONDMS

Message Catalog

  • PSMSGSETDEFN — Message Set — the header for that message set
  • PSMSGCATDEFN — Individual messages in the message catalog/message set

SQR Strings

  • PS_STRINGS_TBL — String definition  (sqrtarns.sqc)
  • PS_STRINGS_LNG_TBL — Translated String  (sqrtarns.sqc)

35 thoughts on “PeopleTools Tables

  1. Another great way to get a list of PeopleTools tables is to pull up the mvprdexp.dms in your $PS_HOME/scripts directory. This data mover script is used to export the complete set of PeopleTools tables from the source environment so that they can be imported into the target environment with mvprdimp.dms.

  2. It might be helpful to add the workflow engine tables. Below are the ones that I know of:

    APPR_RULE_DETL – Approval Rule Defn Details
    APPR_RULE_FIELD – Approval Rule Defn Route Cntl
    APPR_RULE_AMT – Approval Rule Amounts
    RTE_CNTL_LN – Route Control Profile Line
    RTE_CNTL_RUSER – RoleUser Route Cntl Profiles
    RTE_CNTL_TYPE – Route Control Type
    RTE_CNTL_HDR – Routing Control Type

    Note there are separate workflow engines and tables used in the Expense and eProcurement modules.

  3. If you have Oracle 10g as database, you can view the portal table with a hierarchical query.

    select level, a.portal_label, sys_connect_by_path( a.portal_label, ‘-> ‘ ) “Path”
    from PSPRSMDEFN a
    where a.portal_name = ‘EMPLOYEE’
    start with a.portal_objname = ‘CO_EMPLOYEE_SELF_SERVICE’
    connect by prior a.portal_objname = a.portal_prntobjname;

  4. Goshma,

    As best I can tell, the “enable with page is display only” is stored in the FIELDUSE of the PSPNLFIELD table. The field looks like a binary masked field like the PSRECFIELD.USEEDIT field.

    This SQL appears to get the value in Oracle:

    SELECT PNLFLDID, FIELDUSE, TRUNC(FIELDUSE /262144) – TRUNC(FIELDUSE /524288)*2 FROM PSPNLFIELD
    WHERE PNLNAME = ‘TEST’;

    I got the idea from: http://peoplesoft.ittoolbox.com/groups/technical-functional/peopletools-l/hex-masks-for-fielduse-112063

    I hope that helps.

  5. Bhatta,

    Sorry for the late response — been a busy month.

    Mapping the user’s role to a CI is stored in PSAUTHBUSCOMP.

    Hope that helps,

    Stephen

  6. How can i find all the tables used in a peoplesoft component ? I need to find all the tables used in a component. There are very high number of components i need to work onn. Does anybody have a query for that? Please help me.

  7. Hi,

    I have installed peoplesoft HRMS 9.0 application and people tools 8.49 on oracle 10g ,Operating system windows xp.
    But i got one issue PSXATITEM Table don’t have data.Is there way to populate data in PSXLATITEM Table?
    Please respond me asap.

    Thanks
    showsmilezs

  8. showsmilezs,

    Missing all of the rows from PSXLATITEM sounds like a pretty serious issue to me. I would consider rebuilding your database, if possible.

    The PSXLATITEM table stores all of the translate values for PeopleTools/Application Designer. You could try importing some of the projects from your PS Home to see if you can load just the translate values. I am not sure where to look for the projects, though.

    Maybe one of the other readers can offer some more help as to which projects to try to load or other ideas.

  9. What is the name of the tools table that holds the migration history for an object. Not the change controle history but the actual migration history.

    1. I don’t think that there is a table that stores the migration history of objects. I think you need to use the comments field to track changes and such. If there is such a table, I would love to find out.

  10. I believe the properties are stored in PSPNLFIELD where the other field properties are stored. The grid is treated like any other field except that the fields after it in the page order are placed at the next occurs level.

Leave a Comment

Your email address will not be published. Required fields are marked *