Tree mover tables

TreeMover uses the following PeopleTools system tables for trees during both the tree import and tree export processes:

PSTREEDEFN
PSTREENODE
PSTREELEAF
PSTREESTRCT
PSTREELEVEL
PS_TREE_LEVEL_TBL
PS_TREE_NODE_TBL

TreeMover also reads the PSSTATUS table during the tree export process, to identify the version of the tree data exported.

Some IB tables

This is the list send by one of my friends. You can add table which are missing in the list below.

-- Service

PSOPERATION; --all services

PSOPERATIONLANG; -- language specific descriptions

-- Service Operation Versions

PSOPRVERDFN; -- all the service operations

PSOPRVERDFNLANG; -- language specific descriptions

PSOPRVERDFNPARM; -- holds request / response message information , queue

-- Service Operation Handlers

PSOPRHDLR; -- all the handlers

PSOPRHDLRLANG; -- language specific descriptions

PSOPERATIONAC; -- handler details referring to DMS application class

PSOPERATIONDMS; -- handler details referring to DMS script

PSOPERATIONCI; -- handler details referring to CI's

-- Service Operation Routings

PSIBRTNGDEFN; -- all routings

PSRTNGDFNLANG; -- language specific descriptions

PSIBRTNGSUBDEFN; -- routing alias information

PSRTNGDFNPARM; -- routing transformation information

PSRTNGDFNCONPRP; -- routings specific connector override information

PSOPRGENRTGPARM; -- holds list of auto generated routings

PS Meta tables

You might have came across this info previously. Its good to refresh it at times.

PeopleSoft Projects
PSPROJECTDEFN table stores information about projects created in Application Designer.

Try it out:
SELECT * FROM PSPROJECTDEFN
WHERE PROJECTNAME = 'Your_Project_name';
PSPROJECTITEM table stores objects inserted into your Application Designer project.

Try it out:
SELECT * FROM PSPROJECTITEM
WHERE PROJECTNAME = 'Your_Project_name';

Portal Structure
PSPRSMDEFN is a Portal Structure Definition table. A good example is to use this table to find portal path for a specific component.

PSPRSMPERM: Shows the permission lists that are assigned to a portal registry structure (content reference). The permission list name is under field PORTAL_PERMNAME.

XLAT Tables

XLATTABLE: Stores translate values (PeopleSoft version prior to 8.4).

PSXLATDEFN: Stores all fields that have Xlat values. This table does not store any Xlat values.

PSXLATITEM: Stores fields with their actual translate values (PeopleSoft version 8.4 and above).

Record & Field Tables

PSRECDEFN: Stores informations about tables. One row for each table. Field count and record type are two fields that are stored on this table.

CASE RECTYPE
WHEN 0 THEN 'Table'
WHEN 1 THEN 'View'
WHEN 2 THEN 'Derived'
WHEN 3 THEN 'Sub Record'
WHEN 5 THEN 'Dynamic View'
WHEN 6 THEN 'Query View'
WHEN 7 THEN 'Temporary Table'
ELSE TO_CHAR(RECTYPE)
END CASE

PSRECFIELD: Stores records with all their fields (sub-records are not expanded)
PSRECFIELDALL: Stores records with all their fields (sub-records are expanded)
PSINDEXDEFN: Contains 1 row per index defined for a table.
PSKEYDEFN: Contains 1 row per key field defined for an index.
PSDBFIELD: You got it, stores information about fields.

CASE FIELDTYPE
WHEN 0 THEN 'Character'
WHEN 1 THEN 'Long Character'
WHEN 2 THEN 'Number'
WHEN 3 THEN 'Signed Number'
WHEN 4 THEN 'Date'
WHEN 5 THEN 'Time'
WHEN 6 THEN 'DateTime'
WHEN 8 THEN 'Image'
WHEN 9 THEN 'Image Reference'
ELSE TO_CHAR(FIELDTYPE)
END CASE

PSDBFLDLABL: Stores field label information.

Process Definition Table(s)

PS_PRCSDEFNPNL: Stores the process definition name, process type(sqr report, application engine...), and the component name associated with the process definition.

PS_PRCSDEFN: Process definitions table. The record stores processes that can run within the Process Scheduler. Security information such as components and process groups are also stored on this table.

Message Catalog Tables

PSMSGCATDEFN: Stores information about PeopleSoft message catalogs such as message set number, message number and the actual message text.
PSMSGCATLANG: language table.

-- Example
SELECT * FROM PSMSGCATDEFN WHERE LAST_UPDATE_DTTM > TO_DATE('03-DEC-07', 'DD-MON-YY') AND LAST_UPDATE_DTTM <> TO_DATE('03-DEC-07', 'DD-MON-YY') AND LAST_UPDATE_DTTM <>> PeopleTools >> Utilities >> Administration >> URLs

Application Classes

PSAPPCLASSDEFN: Application Class Definitions table. You can use field PACKAGEROOT to search for a specific Application Package.

PeopleSoft Query Tables

PSQRYDEFN: Stores query related info.

PSQRYFIELD: Stores all fields used in a query (both the fields in the Select and Where clause).

PSQRYCRITERIA: Stores criteria query fields. You can get the name of the fields by joining the PSQRYFIELD table.

PSQRYEXPR: Stores query expressions.

PSQRYBIND: Stores query bind variables.

PSQRYRECORD: Stores all records used in all aspects of query creation

PSQRYSELECT: Stores all SELECT requirements by select type. Example would be sub select, join, ect.

PSQRYLINK: Stores the relationships to child queries.

PSQRYEXECLOG: Query run time log table that stores (only 8.4x and higher)

PSQRYSTATS: Query run time statistics table such as count of query execution, and date time of last execution (only in 8.4x and higher).

SQL Objects

PSSQLDEFN: Stores SQL object definitions.

PSSQLDESCR: Stores SQL objects descriptions, and description long.

PSSQLTEXTDEFN: Stores actual SQL text. You can filter by SQLTYPE field to get SQL objects of interest such as Views SQLs and Application Engine SQLs.

-- When SQL type is:
0 = Stand alone SQL objects
1 = Application engine SQL
2 = Views SQLs

Application Engines

PSAEAPPLDEFN: Table that stores Application Engine program definitions.

PSAEAPPLSTATE: Stores application engine STATE records and a flag to indicate if the record is the default STATE record.

PSAESECTDEFN: Application engine section information and also stores last user id to update a specific section.

PSAESECTDTLDEFN: AE section along with descriptions and wither the section is active or not.

PSAEAPPLTEMPTBL: If your application engine uses Temp tables it will show on this record.

PSAESTEPDEFN: Steps in application engines are stored in this table.

PSAESTMTDEFN: Stores your application engine actions and along with their types, such as "Do Select" and so on.

PSAESTEPMSGDEFN: Application engine message action definition table.

AEREQUESTTBL: Application Engine request table behind the AE run control page.

AEREQUESTPARM: Application Engine request parameters table behind the AE run control page.

PeopleCode Tables

PSPCMNAME: PeopleCode Reference table.

PSPCMPROG: Store actual PeopleCode programs (actual code behind PeopleCode events).
Process Request Tables

PSPRCSQUE: This record contains the process request information to run a process request.

PSPRCSRQST: This record contains the process request information to run a process request.

PS_PMN_PRCSLIST: A view to list all process requests in the Process Monitor except for "Delete" (runstatus = 2) process requests.

Other Useful Tables

PSSTATUS: Stores PeopleSoft information such as PS Tools release version and the UNICODE_ENABLED boolean flag where a value of 1 indicates the DB is to be treated by Tools as a UNICODE DB.

PSCHGCTLLOCK: Description as explained by PeopleSoft "This table contains a a row for every object that is currently locked by any user. When the user requests to lock an object in the Application Designer, first this table is searched to see if the object is locked by another user. If it is not found, a row is inserted into the table. When the user requests to unlock an object, the row in this table is deleted."
Visit this post to see how could you make use of this table.

PSMAPFIELD: Stores Field mapping of Activity

PS_PRCSRUNCNTL: Run Control record stores Run Control IDs created online.

Tino Simon

Recruitment tables

HRS_LOCATION --INTERVIEW LOCATION DETAILS

HRS_CONTACT --COMPANY CONTACT DETAILS

HRS_JO_RCTR_RL ---RECRUITER ROLE

HRS_JO_DSC_TYP --POSTING DESCRIPTIONS

HRS_JO_PST_LIB --POSTING DESCRIPTIONS LIBRARY

HRS_JOB_OPENING ---JOB OPENING

HRS_STS_JO_I --JOB OPENING STATUS CODE

HRS_APPLICANT --APPLICANT DETAILS

HRS_RCMNT --RECRUITMENT SUMMARY

HRS_STS_REC_I --RECRUITMENT STATUS

HRS_APP_RTE --ROUTE

HRS_RCM_INT --]INTERVIEW DETAILS

HRS_STS_INT_I --]INTERVIEW STATUS CODES

HRS_OFFER --OFFER DETAILS

HRS_STS_OFF_I --OFFER STATUS

HRS_TEAM --TEAM DETAILS

PeopleSoft Tables Reference

List of useful tables:

PS_EMPLOYEES, PS_EMPLOYEES_LNG, PS_EMPLOYMENT, PS_EMPLOYMENT_LNG, PS_EMPLOYMENT_LNG1, PS_EMPLOYMENT_LNG2, PS_EMPLOYMENT_LNG3, PS_HS_EMPLOYMENT, PS_HS_EMPL_REL_CAN, PS_HS_EMPL_REL_LNG, PS_HS_EMPL_SUB_CAN, PS_HS_EMPL_SUB_LNG, PS_INJ_CLMADDR_CAN, PS_JOB, PS_PERSONAL_DATA, PS_PERSONL_DTA_LNG, PS_PERS_DATA_AET, PS_PERS_DATA_EFFDT, PS_PERS_DTAEFF_LNG, PS_PERS_NID, PS_JOB_EARNS_DIST, PS_JOB_APPROVALS, PS_STATE_TAX_DATA, PS_GENL_DEDUCTION, PS_BAL_ADJ_UI_CAN, PS_CAN_CHECK_YTD, PS_CAN_DED_BALANCE, PS_CAN_ERN_BALANCE, PS_CAN_TAX_BALANCE, PS_CAN_TAX_DATA, PS_CHECK_YTD, PS_DEDUCTION_BAL, PS_EARNINGS_BAL, PS_FED_TAX_DATA, PS_TAX_BALANCE, PS_LOCAL_TAX_DATA, PS_BAL_ADJ_ARR, PS_BAL_ADJ_CHK, PS_BAL_ADJ_CN_CHK, PS_BAL_ADJ_CN_DED, PS_BAL_ADJ_CN_ERN, PS_BAL_ADJ_CN_TAX, PS_BAL_ADJ_DED, PS_BAL_ADJ_ERN, PS_BAL_ADJ_GRN, PS_BAL_ADJ_TAX, PS_DED_ARREARS, PS_GARN_RULE, PS_GARN_SCHED, PS_GARN_SPEC, PS_GENL_DED_CD, PS_BAS_PARTIC, PS_BAS_PARTIC_COST, PS_BAS_PARTIC_DPND, PS_BAS_PARTIC_INVT, PS_BAS_PARTIC_OPTN, PS_BAS_PARTIC_PLAN, PS_BENEF_COMMENT, PS_BEN_PROG_PARTIC, PS_DEPENDENT_BENEF, PS_DISABILITY_BEN, PS_FSA_BENEFIT, PS_FSA_PAYMENT, PS_HEALTH_BENEFIT, PS_HEALTH_DEPENDNT, PS_LEAVE_ACCRUAL, PS_LEAVE_PLAN, PS_LIFE_ADD_BEN, PS_LIFE_ADD_BENEFC, PS_PENSION_BENEFC, PS_PENSION_PLAN, PS_RTRMNT_PLAN, PS_SAVINGS_BENEFIC, PS_SAVINGS_INVEST, PS_SAVINGS_PLAN, PS_VACATION_BEN

PeopleTools Tables:

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 

PSPRUHTAB — Homepage Tab 

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 

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

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

BAT_TIMINGS_LOG 

BAT_TIMINGS_DTL 

BAT_TIMINGS_FN