PSST0101

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
  • 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

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

24 Comments »

  1. [...] PeopleTools Catalog [...]

    Pingback by Page Update « PSST0101 — September 4, 2007 @ 10:51 pm | Reply

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

    Pingback by Blog Update « PSST0101 — October 11, 2007 @ 11:03 pm | Reply

  3. 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.

    Comment by Brent Martin — November 16, 2007 @ 12:12 am | Reply

  4. [...] @ 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 [...]

    Pingback by Updated: PeopleTools Tables « PSST0101 — November 27, 2007 @ 6:19 pm | Reply

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

    Jeromy

    Comment by Jeromy McMahon — January 28, 2008 @ 3:21 am | Reply

  6. 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.

    Comment by jasper — February 15, 2008 @ 5:29 pm | Reply

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

    Comment by jackson — March 3, 2008 @ 3:41 pm | Reply

  8. Thank you jackson for the correction.

    Comment by digitaleagle — March 3, 2008 @ 11:11 pm | Reply

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

    Pingback by Added Filelayout Tables to Tables Page « PSST0101 — April 1, 2008 @ 4:15 pm | Reply

  10. 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;

    Comment by Resan Sa-Ardnuam — April 2, 2008 @ 7:19 am | Reply

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

    Pingback by Blog Update « PSST0101 — October 2, 2008 @ 10:43 pm | Reply

  12. [...] 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 [...]

    Pingback by Blog Update « PSST0101 — October 15, 2008 @ 11:13 pm | Reply

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

    Comment by goshma — October 17, 2008 @ 2:33 pm | Reply

  14. 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.

    Comment by digitaleagle — October 21, 2008 @ 6:56 am | Reply

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

    Pingback by PeopleTools Tables Updated « PSST0101 — November 21, 2008 @ 6:25 pm | Reply

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

    Thanks,
    G.

    Comment by GRay — December 22, 2008 @ 1:55 pm | Reply

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

    Pingback by PeopleSoft Wiki: Reverse Engineering PeopleTools Tables — February 24, 2009 @ 6:34 pm | Reply

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

    Comment by Bhatta — July 20, 2009 @ 10:37 am | Reply

  19. 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

    Comment by digitaleagle — August 10, 2009 @ 4:42 pm | Reply

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

    Comment by Anil — September 30, 2009 @ 7:55 am | Reply

  21. 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.

    Comment by Anil — September 30, 2009 @ 7:57 am | Reply

  22. Anil,

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

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

    Comment by digitaleagle — October 13, 2009 @ 10:08 pm | Reply

  23. 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

    Comment by Showsmilezs — December 5, 2009 @ 12:45 pm | Reply

  24. 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.

    Comment by digitaleagle — December 5, 2009 @ 4:44 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a comment

Blog at WordPress.com.