PeopleTools Tables
PeopleTools tables are the tables in your PeopleSoft environment that store PeopleTools meta-data - data about the underlying development platform for your environment. So this includes things like fields, records, pages, components, menus, and portal structures.
Access to these tables is available in every PeopleTools installation through application designer. But how do you find out which table is used for what?
This comes down to a bit of reverse engineering ..
Naming convention for PeopleTools tables
The majority of PeopleTools tables start with PS and do not have an underscore between PS and the table name (e.g. PSRECNAME). You also don't need to prefix the table name with PS in this case as the record definition and table names match.
To see what I mean, open up PSRECDEFN in application designer and have a look at the Non-Standard SQL Table Name field on the Record Type tab:
Ordinary application tables such as the INSTALLATION table do not have a non-standard SQL name set which is why you need to prefix PS_ to them when you query them out of the database. There are some strange exceptions to this, notably some of the process scheduler tables such as the process definition table, PRCSDEFN which has the a non-standard SQL name of PS_PRCSDEFN.
Incidentally, if you want to query which table record definitions have non-standard SQL table names, use the SQLTABLENAME field in PSRECDEFN like so:
select RECNAME, RECDESCR, SQLTABLENAME
from PSRECDEFN
where SQLTABLENAME <> ' '
and RECTYPE = '0'
order by RECNAME;
Identifying PeopleTools tables through Data mover Scripts
On your PeopleTools file server, if you have access to %PS_HOME%\scripts you should be able to find the data mover (.dms) scripts delivered with the PeopleSoft installation.
The DMS script mvprdexp.dms contains a full list of PeopleTools tables and pt_release_export.dms lists the core PeopleTools tables. Both scripts group PeopleTools tables by type. For example, PeopleTools tables for records & indexes, fields and translates, field formats, pages, menus, components etc. Very handy information. If you can get a copy of this file, keep it close at as a reference.
Identifying PeopleTools tables through PeopleSoft Query
If you run the PeopleSoft Query application (not through the PIA) either using Go > Query in application designer or starting the executable, psqed.exe, you can get it to show you access groups.
To do this, choose:
Your database window where you used to select record definitions should now be grouped. Scroll down and find the PeopleTools access group PT_ACCESS_GROUP and expand the folder.
This should now show you the PeopleTools tables by grouping (e.g. XMLPUBLISHER for XML Publisher, RECORD_DEFINITION for Record Definition etc).
Resources on the Internet
PTRef by GoFaster which also has schema information is the best resource out there.
You can also get the utlity from Github to extract this information yourself. Thanks David Kurtz !
No Comments