Being Metadata aware!!
April 11, 2010 1 Comment
I firmly believe that PeopleSoft developers should understand the PeopleTools tables. This understanding opens up entirely new ways to see and appreciate the power of meta-data driven development. Most PeopleSoft enthusiasts know that PeopleTools tables start with PS without an “_” (underscore).
In my early days as a PeopleSoft developer, I was asked to modify the behavior of a prompt field on Job data page. This prompt field sourced its data from a custom table (lets name it ZZ_CUST_TBL). Obviously, ZZ_CUST_TBL had its own setup page.
Using Application Designer’s “Find Definition References”, I figured out the page > component > menu hierarchy that the table was bundled into. But, that’s how far I could go. How do I find out the navigation to the component?
Here’s a short list of PeopleTools tables and what they contain:
PSRECDEFN – Record definitions
PSRECFIELD – Record Field definitions
PSDBFIELD – Field definitions
PSXLATITEM – Translate values
PSPNLDEFN – Page definitions
PSPNLFIELD – Page field definitions
PSPNLGRPDEFN – Component definition
PSMENUDEFN – Menu definitions
….and so on and so forth.
For a complete list of PeopleTools tables, for a given release, MVPRDEXP.DMS (found in ‘scripts’ directory of the File Server) is a dependable and definitive source.
I’ll go back to my challenge of figuring out the navigation to the page that holds ZZ_CUST_TBL. Personally, I prefer querying PeopleTools tables against performing a “Find Definition References”. I hate to drum my fingers while that nagging grey dialog box keeps me waiting rather unmercifully. Finally, I get dumped with a truckload of references, while I simply wanted to know the pages a record field is used in.
Over time, I developed my own ready-reckoner of sorts. Here we go.
This query throws back all the pages ZZ_CUST_TBL is used in.
SQL > SELECT PNLNAME, RECNAME, FIELDNAME from PSPNLFIELD WHERE RECNAME = 'ZZ_CUST_TBL';
ZZ_CUST was the page I was interested in. So, I go on to find out the component that holds this page.
SELECT PNLGRPNAME FROM PSPNLGROUP WHERE PNLNAME = 'ZZ_CUST';
And the grand finale! The entire portal navigation tree is stored in PSPRSMDEFN table. Specifically, PORTAL_URI_SEG2 is the column that stores the component that corresponds to a navigation link.
SELECT PORTAL_URI_TEXT FROM PSPRSMDEFN WHERE PORTAL_URI_SEG2 = 'ZZ_CUST_TBL'
The PORTAL_URLTEXT returned by the query reads “/c/ADMINISTER_WORKFORCE_(GBL).ZZ_CUST_TBL.GBL”, which is nothing but the menu and the component stringed together.
On a PIA screen, the url format is
Now, I would snap the PORTAL_URLTEXT on the URL,
Voila! I land up on the page that holds ZZ_CUST_TBL.