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:
- PSPROJECTDEFN — Project header table
- PSPROJECTITEM — Definitions in the project (good reference @ Go Faster Consultancy)
- PS_PTOBJECTTYPES — Object types list
- PSDBFIELD — Fields in the system
- PSDBFLDLABL — Field Labels
- PSXLATITEM — Translate Values
- 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
(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.
(Note: Components still have the name panel group in the PeopleTools table names)
- PSPNLGRPDEFN — Component header table
- PSPNLGROUP — Pages in the components
- PSBCDEFN — header record; one row for each component interface
- PSBCITEM — one row for each property
- PSMENUDEFN — Menu header table
- PSMENUITEM — Items (components) on the menu
- 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)
- 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
- 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
- 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
- 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
- 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
- PSCONTDEFN — header record; last update time, etc.
- PSCONTENT — stores actual text in the HTML definition
- PSPCMPROG — PeopleCode program
- PSPCMTXT — The plain text of the PeopleCode programs
- 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
- 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
(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
(thanks to PeopleSoft Tipster –see his blog)
- PSFILEREDEFN — File references (keys: FILEREFNAME, FILEREFTYPECODE)
- PS_ECINMAPFILE — Records
- PS_ECINMAPREC — Records
- PS_ECMAPDEFN — EDI Map Definition
- PS_ECINMAPFLD — Fields in the Records
- PS_ECINMAPRECFLD — Fields in the Records
- 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
- PSMSGSETDEFN — Message Set — the header for that message set
- PSMSGCATDEFN — Individual messages in the message catalog/message set
- PS_STRINGS_TBL — String definition (sqrtarns.sqc)
- PS_STRINGS_LNG_TBL — Translated String (sqrtarns.sqc)
36 thoughts on “PeopleTools Tables”
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.
Thanks for providing this. I have provided a link to this posting on my blog! Thanks for capturing and organizing the information.
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.
Great reference! Thanks! One change – the User definition table is PSOPRDEFN, not PSOPERDEFN…
Thank you jackson for the correction.
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;
could you let me know which table stores values for ‘enable when page is display only’.
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.
How would I find all the required fields for a component interface?
What is the name of table where user or user’s role is mapped with a particular CI?
Sorry for the late response — been a busy month.
Mapping the user’s role to a CI is stored in PSAUTHBUSCOMP.
Hope that helps,
How can i find all the tables used in a peoplesoft component ? Does anybody have a query for that? Please help me.
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.
I created a post with a couple of queries. Check them out here:
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.
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.
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.
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.
Can you please tell me the PS table where we can change Grid properties for Peopltools 8.50
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.
SELECT OBJECTVALUE1 FROM PSPROJECTITEM
WHERE PROJECTNAME = ‘PPLTOOLS’
AND OBJECTTYPE = ‘0’;
Look at you being the first Google search result for this topic. Guess that’s what happens when you provide useful content 🙂