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!
PSPROJECTDEFN table stores information about projects created in Application Designer.
Try it out:
PSPROJECTITEM table stores objects inserted into your Application Designer project.
Try it out:
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.
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).
PSRECDEFN: Stores informations about tables. One row for each table. Field count and record type are two fields that are stored on this table.
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.
PSDBFLDLABL: Stores field label information.
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.
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.
PSMENUDEFN: Store Menu related information. No related component info on this table.
PSMENUITEM: List the menu with all components attached to it.
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.
PSPNLDEFN: Stores pages definitions.
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!
PSURLDEFN: Stores URL definitions. Here is the path to create URL definitions in PeopleSoft Root >> PeopleTools >> Utilities >> Administration >> URLs
PSAPPCLASSDEFN: Application Class Definitions table. You can use field PACKAGEROOT to search for a specific Application Package.
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).
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.
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.
PSPCMNAME: PeopleCode Reference table.
PSPCMPROG: Store actual PeopleCode programs (actual code behind PeopleCode events).
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.
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.
Title | Under | Posted on |
---|---|---|
Component interface Error: no rows exist for the specified keys | PeopleSoft Technical | 03/15/2019 - 3:54am |
ADD 24 months starting from current month.(peoplesoft) | PeopleSoft Functional | 07/29/2018 - 8:44pm |
TRC values dropdown | PeopleSoft Technical | 04/04/2018 - 12:54am |
how to find missing sequence in GRID and print the mising sequence number while saving through peoplecode | PeopleSoft Technical | 09/11/2017 - 4:49am |
Comments
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.
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
Hi,
Thanks a lot for such a useful information. As a PeopleSoft apps DBA, information given by you helped me alot.
Appreciate
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
Dan, look into the below table, they should be there.
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
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
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
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..
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.
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
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
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
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
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
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
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
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
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
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
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.
while looking at the page hit ctrl+J or (IE only) right click and select "view source"
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
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
Hello Friends,
Is there any tool to see the PeopleTools Table Definitions ? Please tell me if any one you know that.
Thanks
Veerakarthi
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
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
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
Hi,
Is there a way i can determine how many times a PSQuery was executed? I am currently using Peoplesoft 7.6 version.
James
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
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.
hi all,
how can we find out already delivered tables
is it like this
SELECT * FROM PS_%
or
SELECT * FROM PS_PSU_%
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%'
Very very useful
Did any one know in which table the comments (on general tab in properties) of every object stores?
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.
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
Great detail!
Any idea what record contains the data for whether a field is required or not?
How do I find the PTools record(s) orpage behind the Grid Properties page in Application Designer. That is, when you open a given page, select a grid on that page, then right click and select 'Page Field Properties'. When the new Grid Properties subpage pops up and you go to the Use Tab, that is the record(s)/page I am looking for. We recently implemented Fluid, and now our scrolls don't all work correctly, and we need to manually script a change to the Grid Layout value on this page. Thanks so much for your help!