PeopleSoft PeopleTools Meta-Tables

You are viewing a wiki page. You are welcome to edit it. Be Bold! Only registered members are allowed to create new Wikis.
Your rating: None Average: 4.4 (213 votes)

This is an attempt to list PeopleSoft meta-tables along with some kind of description for every table. The list will be broken into categories (pages, records, components, and so forth). Please feel free to add to or correct the list, this is a wiki page, so jump in!

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. Take a closer look on how this is done!.

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: Containes 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

PSMSGSETDEFN: Stores information about PeopleSoft message catalog message sets (descriptions, version).

PSMSGSETLANG: Message sets language table.

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

PSMSGCATLANG: Message catalogs language table.

-- Example
SELECT * FROM PSMSGCATDEFN
WHERE LAST_UPDATE_DTTM  > TO_DATE('03-DEC-07', 'DD-MON-YY')
AND LAST_UPDATE_DTTM  < TO_DATE('05-DEC-07', 'DD-MON-YY')
ORDER BY MESSAGE_SET_NBR, MESSAGE_NBR;
-- This will return messages that has been last update/added between 2 specific dates.

Previous PeopleSoft message catalog tables:
PS_MESSAGE_CATALOG: Stores information about PeopleSoft message catalogs such as message set number, message number and the actual message text.
MESSAGE_SET_TBL: Message set description table.
-- Example
SELECT * FROM PS_MESSAGE_CATALOG
WHERE LAST_UPDATE_DTTM  > TO_DATE('03-DEC-07', 'DD-MON-YY')
AND LAST_UPDATE_DTTM  < TO_DATE('05-DEC-07', 'DD-MON-YY')
ORDER BY MESSAGE_SET_NBR, MESSAGE_NBR;
-- This will return messages that has been last update/added between 2 specific dates.

Menu Tables

PSMENUDEFN: Store Menu related information. No related component info on this table.

PSMENUITEM: List the menu with all components attached to it.

Component Tables

PSPNLGRPDEFN: Stores component related information only.

PSPNLGROUP: This table will give you information regarding a specific component along with the names of pages attached to it.

Pages

PSPNLDEFN: Stores pages definitions.

CASE PNLTYPE
        WHEN 0 THEN 'Page'
        WHEN 1 THEN 'Sub page'
        WHEN 2 THEN 'Secondary page'
        ELSE TO_CHAR(PNLTYPE)
    END CASE

PSPNLFIELD: Stores all items used by each page definition.
CASE FIELDTYPE
        WHEN 0 THEN 'Static Text'
        WHEN 1 THEN 'Frame'
        WHEN 2 THEN 'Group Box'
        WHEN 3 THEN 'Statis Image'
        WHEN 4 THEN 'Edit Box'
        WHEN 5 THEN 'Dropdown List'
        WHEN 6 THEN 'Long Edit Box'
        WHEN 7 THEN 'Check Box'
        WHEN 8 THEN 'Radio Button'
        WHEN 9 THEN 'Image'
        WHEN 10 THEN 'Scroll Bar'
        WHEN 11 THEN 'Subpage'
        WHEN 12 THEN 'Peoplecode Command - (Button/Hyperlink Destination)'
        WHEN 13 THEN 'Scroll Action - (Button/Hyperlink Destination)'
        WHEN 14 THEN 'Toolbar Action - (Button/Hyperlink Destination)'
        WHEN 15 THEN 'External Link - (Button/Hyperlink Destination)'
        WHEN 16 THEN 'Internal Link - (Button/Hyperlink Destination)'
        WHEN 17 THEN 'Process - (Button/Hyperlink Destination)'
        WHEN 18 THEN 'Secondary Page'
        WHEN 19 THEN 'Grid'
        WHEN 20 THEN 'Tree'
        WHEN 21 THEN 'Secondary Page - (Button/Hyperlink Destination)'
        WHEN 23 THEN 'Horizontal Rule'
        WHEN 24 THEN 'Tab Separator'
        WHEN 25 THEN 'Html Area'
        WHEN 26 THEN 'Prompt Action - (Button/Hyperlink Destination)'
        WHEN 27 THEN 'Scroll Area'
        WHEN 29 THEN 'Page Anchor'
        WHEN 30 THEN 'Chart'
        WHEN 31 THEN 'Push Button/Link'
        WHEN 32 THEN 'Analytic Grid'
        ELSE TO_CHAR(FIELDTYPE)
    END CASE

Security

PSPRSMPERM: Portal Structure Permissions.

PSAUTHITEM: Page Permissions. This table stores the information about the page level access for a permission list.

PSAUTHPRCS Process Group Permissions. A many to many relationship table between Permission Lists and Process Groups. Setup can be found at PeopleTools > Security > Permissions & Roles > Process.

PSROLECLASS: Role Classes table. A many to many relationship table between Roles and Permission Lists.

PSROLEDEFN: This table stores information about Peoplesoft Role definitions. Users get permissions to PeopleSoft objects through Roles, which are assigned Permission Lists.

PSROLEUSER: This table stores information about the Users in Peoplesoft and the roles assigned to them.

PSCLASSDEFN: Permissions List definitions table. Permission list name can be found under Field Name CLASSID.

PSOPRDEFN: Users/Operator definition table. This table stores information about PeopleSoft users. This is the core table for User Profile Manager.

PSOPRCLS: Users/Operator and Perm list mapping Table. This table stores information about PeopleSoft users and the permission lists attached to those users.
A User gets these permission lists indirectly through the roles which are attached to the user

Here is an example query post that uses all of the above security tables!

URL Definitions

PSURLDEFN: Stores URL definitions. Here is the path to create URL definitions in PeopleSoft Root >> 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.

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Comments

Have a question? Please ask it on the forum instead.
Guest's picture
Guest (not verified)
Re: PeopleSoft PeopleTools Meta-Tables

Hi,
I am a novice user for PS 9.0 , i want to know the information about PS delivered table, what is the purpose of that PS table and whats the purpose of fields.
Does we have any reference(DOC,URL) which track this type of information.That would be great for a me.Thanks in advance.

Lepa's picture
User offline. Last seen 2 weeks 2 days ago. Offline
Joined: 06/23/2008
Posts: 590
Re: PeopleSoft PeopleTools Meta-Tables

Hi there,
I guess your confused about what a "PS delivered" table is. All the tables you see in the post are PS peopletools delivered tables. There are hundred other more Financial, HR (depending on your PS module) delivered tables.

Please start a forum topic if you want more info.

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Piyush Mehta's picture
Piyush Mehta (not verified)
Re: PeopleSoft PeopleTools Meta-Tables

Hi,
Thanks a lot for such a useful information. As a PeopleSoft apps DBA, information given by you helped me alot.

Appreciate

Dan's picture
Dan (not verified)
Re: PeopleSoft PeopleTools Meta-Tables

Hi,

I'm trying to find out the Meta table which stores App designer-created HTML objects(not to be confused with HTML areas)

Please help me with this

Thanks,
Dan

Lepa's picture
User offline. Last seen 2 weeks 2 days ago. Offline
Joined: 06/23/2008
Posts: 590
Re: PeopleSoft PeopleTools Meta-Tables

Dan, look into the below table, they should be there.

SELECT *
  FROM pscontdefn

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Dan's picture
Dan (not verified)
Re: PeopleSoft PeopleTools Meta-Tables

Thanks Lepa....but i'm not able to find the actual HTML text defined within this table....

Lemme brief you on what i'm trying to do.....I have to use some lengthy text data(which exceeds the limit in message catalog) in an application engine....I put this in a HTML and tried to use the GetHTMLText function to fetch the data.....but unfortunately, this built in function is not allowed to be used within an AE program....

I'm trying to find out the table which stores the HTML data so that I can query and fetch it directly....

Any further pointers on this would be greatly appreciated!

TIA!
Dan

Ambarish's picture
Ambarish (not verified)
Re: PeopleSoft PeopleTools Meta-Tables

Hello PSFT Guru,

I am not a PSFT DBA, but researching on the peoplesoft Supply Chain Management (SCM) tables.
My question is - do we have any weblink/document or a good book providing information on module-wise PSFT tables?
I will elaborate my problem-
For example - I am looking at Purchase Order page of PSFT SCM module. I want to know- which Peoplesoft table stores information written on Create Purchase Order Page in peoplesoft.
Specifically I am also looking at tables storing configuration/set up for Peoplesoft Supply Chain Management (SCM) module.

Thanks!
~Ambarish Vaidya, Chartered Accountant

Guest's picture
Guest (not verified)
Re: PeopleSoft PeopleTools Meta-Tables

Hi there, can anyone tell me where are the database error messages are stored in PS. Maybe a error generated from a SQL class statement..

Anil's picture
Anil (not verified)
How to find all records used in a peolesoft component

Hi,

I need to find all the records in a given component. I need to do this for many peolesoft components. Does anybody have a query which takes component name as input and the list of all records in that component as output. Please help.

Lepa's picture
User offline. Last seen 2 weeks 2 days ago. Offline
Joined: 06/23/2008
Posts: 590
Re: PeopleSoft PeopleTools Meta-Tables

I've posted your question on the forum so everyone can benefit from it. I've also gave you the solution. Here is the link http://www.compshack.com/forum/peoplesoft/peoplesoft-technical/how-to-find-all-records-used-peolesoft-component

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Jaynick's picture
Jaynick (not verified)
Query tables contain no data?

Hi
We on tools 8.49 but these two records contain no data:
PSQRYEXECLOG & PSQRYSTATS

Must some auditing be switched on before data is recorded?
Thanks

Guest - Amrit's picture
Guest - Amrit (not verified)
Re: PeopleSoft PeopleTools Meta-Tables

Hello,

do you happen to know in which table i get the Title found on the search page of a component ?
When entering the page "Process schedule > Process" the title that appears is " Process " , and i want to know in which table is that found, so i could add for different languages in its LANG table

If you happen to know that would be soooo helpful

THank you

Hari_aindian's picture
User offline. Last seen 3 years 26 weeks ago. Offline
Joined: 08/02/2010
Posts: 84
Re: PeopleSoft PeopleTools Meta-Tables

Basically it comes from the component properties.

If we had given message catalogue message set/number then it will be fetched from the psmsgcatdefn record.

Regards,
Hari.A

Guest - Amrit's picture
Guest - Amrit (not verified)
Re: PeopleSoft PeopleTools Meta-Tables

thank you for your help..
i went on to create a PLSQL finding all tables containing the wording i wanted to change.. took some time
but i found out it was in the table :
PSMENUITEMLANG

Thank you

Hari_aindian's picture
User offline. Last seen 3 years 26 weeks ago. Offline
Joined: 08/02/2010
Posts: 84
Re: PeopleSoft PeopleTools Meta-Tables

Check the PSMSGCATDEFN record. Peoplesoft has delivered message set numbers up to 20000. Check this and let me know if you are looking for this.

Regards,
Hari.A

getrakeshvarma's picture
getrakeshvarma (not verified)
PeopleSoft new tables

Hi ,

I do not have any date columns in my PSFT tables.If i want to check the latest records, is their any possibility to get that information

Hari_aindian's picture
User offline. Last seen 3 years 26 weeks ago. Offline
Joined: 08/02/2010
Posts: 84
Re: PeopleSoft new tables

Do you want to find the latest record/table created?

If so query this table PSRECDEFN. You will find all records details.

Do you want to find the latest row of data inserted to a record?

If so query the audit records, you will find all the details about that new row.

Hope I answered to your question. Pls let me know if am not answering to your question.

Thanks,
Hari.A

Guest's picture
Guest (not verified)
Re: PeopleSoft new tables

Thanks Hari,

Let me explain the problem.I am trying to figure out a logic for incremental load in Informatica.And my tables are not containing any date columns.Say suppose I had run a load last month 22 and I want to run a incremental load today,which should populate the data from 22 of last month till today.So is there any table which could give me the information when these records are getting inserted

Hari_aindian's picture
User offline. Last seen 3 years 26 weeks ago. Offline
Joined: 08/02/2010
Posts: 84
Re: PeopleSoft PeopleTools Meta-Tables

As per my understanding, you are saying the last run date as you are running the load the load on 22 last month and making a note of this date manually. Also you have any date columns in the table

If you are loading data from file to table then
Before loading the data in to table do a compare between current file and the last run file. Find the differences and load the difference into the table

Or Let say if you are loading data from table to file then
Load the entire data into the file and the do a compare between current file and the last run file. Find the differences and load the difference into the new file.

This process will not be limited to the data that came newly to the system after 22nd last month. This process will find all the rows that are changed from the previous load irrespective of the date.

Regards,
Hari.A

Amit's picture
Amit (not verified)
Re: PeopleSoft PeopleTools Meta-Tables

Hi,

I need to know all the people tools Meta-tables related to Integration broker. I have to update the App Message definition without creating a new version. As App message is referenced in run time tables , peoplesoft does not allow me to edit anything related to App Message via interface or app designer.
I think of directly updating DB table , can anyone tell whether this option would work?

Thanks in advance!!!

-Amit.

Guest's picture
Guest (not verified)
Re: PeopleSoft PeopleTools Meta-Tables

while looking at the page hit ctrl+J or (IE only) right click and select "view source"

Guest's picture
Guest (not verified)
Re: PeopleSoft PeopleTools Meta-Tables

We use a tool called Saphir from Silwood Technology that is all about browsing the metadata in a PeopleSoft system. It show tables and fields with the nice 'English' descriptions, let's you navigate around relationships, and also groups tables by module. It also shows tables in data model form, optionally in things like ERwin or Visio

Lepa's picture
User offline. Last seen 2 weeks 2 days ago. Offline
Joined: 06/23/2008
Posts: 590
Re: PeopleSoft PeopleTools Meta-Tables

I was just on their site and was hopping they will list the price for such a tool. Thanks for the info :)

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Veerakarthi's picture
Veerakarthi (not verified)
Tool to see the PeopleTools table defintions

Hello Friends,
Is there any tool to see the PeopleTools Table Definitions ? Please tell me if any one you know that.

Thanks
Veerakarthi

Dhaneshwar's picture
Dhaneshwar (not verified)
Re: PeopleSoft PeopleTools Meta-Tables

Hi,

I am using PSPNLFIELD along with PSDBFIELD to retrieve fields available on a page. The information about Field Type, Format of a field is stored in form of digits in the meta-tables. Which is not understandable.

How to get information of FieldType, Format for a field in interpretable form.

Regards,
Dhaneshwar

Lepa's picture
User offline. Last seen 2 weeks 2 days ago. Offline
Joined: 06/23/2008
Posts: 590
Re: PeopleSoft PeopleTools Meta-Tables

you need a case statement in your selelct from PSDBFIELD.

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

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

jRod's picture
jRod (not verified)
Re: PeopleSoft PeopleTools Meta-Tables

Hi,

I am using PS 8.9 and was wondering if there is a table that stores record counts for all tables in PS?

Thanks,
Jesse

James's picture
James (not verified)
Re: PeopleSoft PeopleTools Meta-Tables

Hi,
Is there a way i can determine how many times a PSQuery was executed? I am currently using Peoplesoft 7.6 version.

James

sriram's picture
sriram (not verified)
Re: PeopleSoft PeopleTools Meta-Tables

Hi All,

i have upgraded peoplesoft application ELM from 9.0 to 9.1 . now i want to know application version in database part . can u plz say me the query to find out version of application.

Thanks in advance

Thanks,
Sriram
Mailid:srirams086@gmail.com

Surya Prakash's picture
Surya Prakash (not verified)
Re: PeopleSoft PeopleTools Meta-Tables

Hi,
I am using one table as input for the app engine.
when i am running the app engine, it is failing with error message as First operand of . is NULL.But the emplid which is shown in log message is not in input table.
so want to confirm that, is it a cache issue ? clearing the cache of process scheduler will solve the issue?
Please help me on this.

Thanks in advance

Surya.

shalini's picture
shalini (not verified)
Re: PeopleSoft PeopleTools Meta-Tables

hi all,
how can we find out already delivered tables

is it like this

SELECT * FROM PS_%
or
SELECT * FROM PS_PSU_%

Siddharth's picture
User offline. Last seen 4 years 42 weeks ago. Offline
Joined: 05/13/2011
Posts: 2
Re: PeopleSoft PeopleTools Meta-Tables

Hi Shalini,

If you want to find all delivered records , like record name starting from PS then use following Query,

Select recname from PSRECDEFN where recname like 'PS%'

Manisha's picture
Manisha (not verified)
Re: PeopleSoft PeopleTools Meta-Tables

Very very useful

Sai Krishna Kande's picture
Sai Krishna Kande (not verified)
Re: PeopleSoft PeopleTools Meta-Tables

Did any one know in which table the comments (on general tab in properties) of every object stores?

Guest's picture
Guest (not verified)
To trace LASTUPDOPRID log file who updated user profile

User have PSFN access till 19th aug 2015 , someone have deactivated the profile i.e(user not having primary permission list) . How to trace the log file or the info like who done that and why ?

Thanks ,
Anupama.

Guest's picture
Guest (not verified)
Re: PeopleSoft PeopleTools Meta-Tables

Is there a way to find the page in PeopleSoft based on URL Description or URL Identifier from URL Maintenance? If not please suggest a way. All I have is URL Identifier with me

Mark Stricker's picture
Mark Stricker (not verified)
Re: PeopleSoft PeopleTools Meta-Tables - Required Fields

Great detail!

Any idea what record contains the data for whether a field is required or not?

Post new comment

The content of this field is kept private and will not be shown publicly.
CAPTCHA
The question below is to prevent automated spam submissions.
4 + 2 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.