Peoplesoft Tools Tables

PSST0101 blog site had started listing out the tools tables. I wanted to post this listing on my blog as well. I have also provided the link to the orginal blog.

Enjoy

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
Pages

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

PSPNLDEFN — Page header table
PSPNLFIELD — Page controls
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
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
PSOPERDEFN — User ID header table
PSROLEUSER — Roles granted to users
Portal

PSPRSMDEFN — Content References and Folders
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

Excellent Backup tool for source code

Here is a great site and software tool to backup source code. You can specify only certain file extensions *.sqr, *.sql, *.js etc. With this backup tool, you can also schedule backups to copy only the changes to the network share or jump drive.

This site is developed by a colleague of mine and his software is top shelf.
http://www.glaciermicrosystems.com/products/products.html

PS Queries / a user can not see / Security

This query was developed to capture a listing of queries that a user does not have access to.

SELECT DISTINCT A.OPRID, A.QRYNAME, A.DESCR, B.RECNAME
FROM PSQRYDEFN A, PSQRYRECORD B
WHERE A.OPRID = B.OPRID AND
A.QRYNAME = B.QRYNAME AND
A.QRYTYPE = 1
and not exists (select 'x' from PSROLECLASS D , PSROLEUSER E , PSQRYACCLSTRECS F
WHERE D.ROLENAME = E.ROLENAME AND
F.CLASSID = D.CLASSID AND
F.VERSION = ( SELECT VERSION FROM PSVERSION D WHERE D.OBJECTTYPENAME = 'QAL') AND
E.ROLEUSER = A.OPRID and
f.recname = b.recname)
and a.oprid = 'Place OPRID here'

PS Listing of Payroll / HR tables

Here is a great sample SQL for capturing PS Payroll and HR tables within PS. This SQL was provided by my colleague Roger Davies.

select a.RECNAME, decode(a.SQLTABLENAME, ' ', 'PS_' || a.recname, a.sqltablename) table_name, a.rectype, a.OBJECTOWNERID, xlat.xlatlongname, num_rows
from psrecdefn a, psxlatitem xlat, all_tables b
where a.objectownerid in ('AWFA', 'FGL', 'HBA', 'HBN', 'HEB', 'HER', 'HFSA', 'HHP', 'HHR', 'HMCF', 'HPY', 'HRAM', 'HRAT', 'HSP', 'HTC', 'HTL', 'HTLA', 'HTLI', 'HTLR', 'HTLX', 'HRAM','HRAT','HSP' )
and xlat.fieldname = 'OBJECTOWNERID'
and xlat.fieldvalue = a.OBJECTOWNERID
and xlat.effdt = (select max(xlat1.effdt) from psxlatitem xlat1
where xlat1.fieldname = xlat.fieldname
and xlat1.fieldvalue = xlat.fieldvalue)
and b.table_name = decode(a.SQLTABLENAME, ' ', 'PS_' || a.recname, a.sqltablename)
and b.NUM_ROWS > 0