PeopleTools Tables

This page has moved.  Please update your links:
http://psst0101.digitaleagle.net/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

  • PSPROJECTDEFN — Project header table
  • PSPROJECTITEM — Definitions in the project

Fields

  • PSDBFIELD — Fields in the system
  • PSXLATITEM — Translate Values

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

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

Process Scheduler

  • PS_PRCSDEFN — Process Definition Header
  • PS_PRCSDEFNGRP — Process Group
  • PS_PRCSDEFNPNL — Component
  • PS_PRCSJOBDEFN — Job Header
  • PSPRCSRQST — Process Request Instances
  • PS_PRCSJOBITEM — Job Processes

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

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)

List Log Messages

HTML Definitions

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

SQL Definitions

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

File Layout Definitions

  • PSFLDDEFN — header record; last update time, etc.
  • PSFLDSEGDEFN — stores the segments for each layout
  • PSFLDFIELDDEFN — stores the fields for each layout

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

Timings
(thanks to PeopleSoft Tipster –see his blog)

  • BAT_TIMINGS_LOG
  • BAT_TIMINGS_DTL
  • BAT_TIMINGS_FN

33 Responses to “PeopleTools Tables”

  1. Page Update « PSST0101 Says:

    […] PeopleTools Catalog […]

  2. Blog Update « PSST0101 Says:

    […] Filed under: Uncategorized — digitaleagle @ 11:03 pm I have updated the PeopleTools Tables page and changed the theme for the […]

  3. Brent Martin Says:

    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.

  4. Updated: PeopleTools Tables « PSST0101 Says:

    […] @ 6:19 pm Here is just a quick post to let you know that I have added a few more tables to the PeopleTools Tables […]

  5. Jeromy McMahon Says:

    Thanks for providing this. I have provided a link to this posting on my blog! Thanks for capturing and organizing the information.

    Jeromy

  6. jasper Says:

    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.

  7. jackson Says:

    Great reference! Thanks! One change – the User definition table is PSOPRDEFN, not PSOPERDEFN…

  8. digitaleagle Says:

    Thank you jackson for the correction.

  9. Added Filelayout Tables to Tables Page « PSST0101 Says:

    […] digitaleagle @ 4:15 pm Just a quick update:  I added the File Layout Definition tables to the PeopleTools Tables page.  Hope it […]

  10. Resan Sa-Ardnuam Says:

    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;

  11. Blog Update « PSST0101 Says:

    […] First, I added a few tables to the PeopleTools tables reference: […]

  12. Blog Update « PSST0101 Says:

    […] Filed under: Blog — digitaleagle @ 11:12 pm I added a few more tables to the PeopleSoft Tables listing from a post I saw at PeopleSoft […]

  13. goshma Says:

    could you let me know which table stores values for ‘enable when page is display only’.

  14. digitaleagle Says:

    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.

  15. PeopleTools Tables Updated « PSST0101 Says:

    […] PeopleTools Tables Updated Filed under: Blog — digitaleagle @ 6:24 pm Just a quick note: I added a few tables to the PeopleTools Tables list: […]

  16. GRay Says:

    How would I find all the required fields for a component interface?

    Thanks,
    G.

  17. PeopleSoft Wiki: Reverse Engineering PeopleTools Tables Says:

    […] provides a list of PeopleTools tables. help | terms of service | privacy | report a bug | flag […]

  18. Bhatta Says:

    What is the name of table where user or user’s role is mapped with a particular CI?

  19. digitaleagle Says:

    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

  20. Anil Says:

    How can i find all the tables used in a peoplesoft component ? Does anybody have a query for that? Please help me.

  21. Anil Says:

    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.

  22. digitaleagle Says:

    Anil,

    I created a post with a couple of queries. Check them out here:

    https://psst0101.wordpress.com/2009/10/13/querying-the-component/

  23. Showsmilezs Says:

    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

  24. digitaleagle Says:

    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.

  25. steve b Says:

    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.

  26. Alok Says:

    Can you please tell me the PS table where we can change Grid properties for Peopltools 8.50

  27. digitaleagle Says:

    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.

  28. New Tables « PSST0101 Says:

    […] New Tables Filed under: PeopleTools — digitaleagle @ 9:20 pm I found some additional PeopleTools system tables that I am adding to my reference. […]

  29. 2010 in review « PSST0101 Says:

    […] PeopleTools Tables September 2007 29 comments 3 […]

  30. bogumillaska Says:

    SELECT OBJECTVALUE1 FROM PSPROJECTITEM
    WHERE PROJECTNAME = ‘PPLTOOLS’
    AND OBJECTTYPE = ‘0’;

  31. Robert Says:

    Add to this list

    PSUSERSRCHDEFN – Saves Search params/arguments for the “Saved Search” feature introduced in PS 9.

    This is a pretty essential one since you cannot edit existing searches to discover what their values are in the web interface. You can only add and delete.

  32. kku Says:

    Where did it save the Search Criteria prior to PS 9? We are in 8.8 and wanted to how it handles the saved searches or where it stores them?


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: