User login
Sponsored links
Recent blog posts
- AJAX XML - The JavaScript code
- AJAX XML - The PHP Page code
- Peoplecode Trace in a File.
- Display pdf file , image, website on peoplesoft page using html area.
- Ren Server -- Over View
- SQR Code
- AP PO Tables - Some imp Query.
- PeopleSoft XML Publisher - Report Development
- Debugging Functionality in Peoplesoft
- Steps for Creating an XML Export File with PeopleSoft
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:
WHERE PROJECTNAME = 'Your_Project_name';
PSPROJECTITEM table stores objects inserted into your Application Designer project.
Try it out:
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.
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.
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
PSMSGCATDEFN: Stores information about PeopleSoft message catalogs such as message set number, message number and the actual message text.
PSMSGCATLANG: language table.
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.
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.
PSPNLFIELD: Stores all items used by each page definition.
Security
PSPRSMPERM: Portal Structure Permissions.
PSAUTHITEM: Page Permissions. This table stores the information about the page level access for a permission list.
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.
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.
Unanswered Forum Posts
| Title | Under | Posted on |
|---|---|---|
| Abs Mgmt: How can Abs rule pro-rate contractor's last month's entitlement (need help urgently) | PeopleSoft Functional | 03/10/2010 - 9:21pm |
| Can we user LONG datatype as a search option in PeopleSoft | PeopleSoft Technical | 03/10/2010 - 11:22am |
| special caracters are not translated correctly when ftp from windows to unix | Programming | 03/09/2010 - 3:23pm |
| How to view the employees from particular country in peoplesoft? | PeopleSoft Technical | 03/03/2010 - 1:56am |
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
Very Useful information , this is both good reading for, have quite a few good key points, and I learn some new stuff from it too, thanks for sharing your information.
regards,
handbags
Post new comment