ERP Associates, Inc. - Mapping PeopleTools Tables

Mapping PeopleTools Tables

Tuesday, 21 February 2006

The PeopleTools tables in your PeopleSoft database contain all of the meta-data related to your PeopleSoft application. You can generally distinguish them from the application tables by the lack of an underscore after the PS, for instance PSRECDEFN, PSOPRDEFN, etc. (Application tables have an underscore after the PS, for instance PS_PERSONAL_DATA, PS_CUSTOMER, etc).

In general, PeopleSoft groups common PeopleTools tables by the table prefix. For instance, query tables all start with PSQRY, and role tables all start with PSROLE.

But PeopleTools tables don't necessarily follow a consistent naming convention, and there are times when you'll want to know the exact table names. For instance, it would sometimes be nice to query against the tables directly instead of depending on Application Designer's limited functionality. Or it would be nice to use Data Mover to take an entire set of queries from one database to another without creating and migrating a huge project.

Fortunately, PeopleSoft has documented every single PeopleTools table. You won't find it in PeopleBooks, but it's in one of the core data mover scripts you use during a database upgrade, MVPRDEXP.DMS.

You'll find MVPRDEXP.DMS in the $PS_HOME/scripts directory of any PeopleSoft installation from at least version 7.x on. PeopleSoft delivers this script as part of the Move to Production process during an upgrade. The purpose of the script is to export all of the PeopleTools tables from the source database. Another script called MVPRDIMP.DMS runs next in the upgrade process to import all of the PeopleTools tables into the Target database. From that point forward, the upgrade creates, alters, and drops columns and tables in the target database to get the application tables and data in synch with the new PeopleTools definitions. Data conversion is another big part of the upgrade, but it all starts with MVPRDEXP.DMS.

As useful as MVPRDEXP is to an upgrade, it's just as useful as a reference source. For instance, if you want the definitive list of user tables for your PeopleTools release, just open MVPRDEXP.DMS, search for Users, and viola -- you'll find PSOPRDEFN, PSOPRALIAS, PSROLEUSER, PSUSERATTR, PSUSEREMAIL, PSUSERPRSNLOPTN, PS_ROLEXLATOPR, PS_ROLEXLATOPR_LNG, and EXPORT PS_RTE_CNTL_RUSER.

The same approach works for Records, Fields, Pages, Jobs, and even the elusive Portal Registry.

Now you can create that data mover script to move queries from one instance to another, and you can move them with confidence.

A quick word of caution: If you do move PeopleTools objects in this manner, you'll want to have the application shut down before the move, and run PSVERSION and clear your application server cache after the move. Keep in mind this isn't a "PeopleSoft-Supported" approach, so test thoroughly before making changes to a Production system, and never ever move PeopleTools objects like this unless the source and target are on the same PeopleTools release and patch level.

Armed with information gleaned from MVPRDEXP, I wrote this script to identify queries that a user can't see because of a lack of query security:

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 = '&&1'

I hope you find MVPRDEXP as useful as I have.

Comments (3)

Comment from psguyblogger : psguyblogger : http://psguyblog.blogsopt.com

I've done this between different PeopleTools versions (PeopleTools 7.x to PeopleTools 8.45 smilies/wink.gif

). I do most of my PeopleSoft technical work only through the back-end and care the least about it not being a standard. So, how do we carry objects between releases?

Step 1. Write your SQL for your old PeopleTools release.

Step 2. Do search for all the table names used within your SQL in the relscripts (rel750.sql is an example).

Step 3. Pick out all the scripts that lie in your path ( i.e if your old release is 8.43 and your new release is 8.46, pickout rel844.sql,rel845.sql and rel846.sql)

Step 4. If the tablenames used inside your SQL are not present in the set of scripts that you picked in Step 3, then you can directly use them. else Step 5.

Step 5. Analyse how the relscripts convert the meta-data from one PeopleTools release to the other and make these changes to your old release SQL.

You have mentioned about PSVERSION. I did a bit of research on this but too much to write in this comment, probably put in a post.

Finally, here is the SQL required to get all the PeopleTools tablenames from the back-end.

SELECT RECNAME FROM PDRECDEFN WHERE RECTYPE = 0 AND SQLTABLENAME != ' ' AND LASTUPDOPRID = 'PPLSOFT'.

Sorry about the long comment.

February 21, 2006

Comment from psguyblogger : psguyblogger : http://psguyblog.blogsopt.com

Sorry, error in the above SQL,

SELECT RECNAME FROM PDRECDEFN WHERE RECTYPE = 0 AND SQLTABLENAME != ' ' AND LASTUPDOPRID = 'PPLSOFT'

it is,

SELECT RECNAME FROM PSRECDEFN WHERE RECTYPE = 0 AND SQLTABLENAME != ' ' AND LASTUPDOPRID = 'PPLSOFT'

mistake in tablename PSRECDEFN

February 22, 2006

Comment from Joe : Joe : http://chiliblog.chili-mango.net

Great tip, thanks for sharing. I usually find myself digging around the catalog to find the records I need...

February 22, 2006

Write comment