Here is the list of PeopleTools table which can be use to fetch the related information about any object present in App Designer.I'll break down this list with categories like App Engine,Records,Fields,pages,etc.
Click on corresponding link to checkout the System tables for the particular object.
Application Engine Meta Data: Tables holding Application Engine Meta Data for the Application Engine, Section, Steps and SQL.
Component Interface Meta Data: Tables holding Component Interface Meta Data.
| PSBCDEFN : Stores component interface names |
Component Meta Data: Tables holding Component Meta Data.
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.
Record & Field Meta Data: Tables holding PeopleTools Record & Field information.
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.
Field Values for Tools Tables: Find field values for the following
- RECORD.FIELDNAME
- PSPROJECTITEM.OBJECTTYPE
- PSPROJECTITEM.UPGRADEACTION
- PSPROJECTITEM.SOURCESTATUS
- PSRECDEFN.RECTYPE
- PSDBFIELD.FIELDTYPE
- PSPNLFIELD.FIELDTYPE
- PSSQLDEFN.SQLTYPE
File Layout Definitions: Tables holding File Layout Segment and Field definitions.
PSFLDFIELDDEFN : Filelayout stored
PSFILEREDEFN : File reference stored
HTML & Image Meta Data: Tables holding HTML and Image Meta Data.
Menu Meta Data: Tables holding Menu Meta Data.
PSMENUDEFN: Store Menu related information. No related component info on this table.
PSMENUITEM: List the menu with all components attached to it.
Message Catalog: Tables holding Message Catalog Entries.
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.
Page Meta Data:Tables holding Page Meta Data.
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
PeopleCode Meta Data: Tables holding PeopleCode Meta Data.
PSPCMNAME: PeopleCode Reference table.
PSPCMPROG: Store actual PeopleCode programs (actual code behind PeopleCode events).
Portal (Structure and Content): Tables holding portal content references and permission lists authorized.
PSPRSMDEFN is a Portal Structure Definition table. A good example is to use this table to find portal path for a specific component.
1) Run the below SQL to get the content reference name for your component
SELECT PORTAL_NAME,
PORTAL_OBJNAME AS CONTENT_REFERENCE,
PORTAL_LABEL,
PORTAL_URI_SEG1 AS MENU,
PORTAL_URI_SEG2 AS COMPONENT,
PORTAL_URI_SEG3 AS MARKET
FROM psprsmdefn
WHERE PORTAL_NAME = 'EMPLOYEE'
AND PORTAL_URI_SEG2 = :1;
-- Replace :1 with the component name you are looking for.
2) From the query above - copy the value in the CONTENT_REFERENCE field and replace the ":1" variable and you will have the path to your component.
WITH portal_registry AS
(SELECT RTRIM(REVERSE(sys_connect_by_path(REVERSE(portal_label), ' >> ')), ' >> ') path,
LEVEL lvl
FROM psprsmdefn
WHERE portal_name = 'EMPLOYEE' START WITH PORTAL_OBJNAME = :1 CONNECT BY PRIOR portal_prntobjname =portal_objname)
SELECT path
FROM portal_registry
WHERE lvl =
(SELECT MAX(lvl)
FROM portal_registry);
So, the 1st query is to get the content reference for a component name that you know and then using this query to find the path!
PSPRSMPERM: Shows the permission lists that are assigned to a portal registry structure (content reference). The permission list name is under field PORTAL_PERMNAME.
Process Scheduler Information:Tables holding the process and job definitions along with information
necessary to run a process.
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.
Project Meta Data: Table holding PeopleTools project information (all objects in the project).
PSPROJECTDEFN : This table stores information about projects created in Application Designer.
Try it out:
SELECT * FROM PSPROJECTDEFN
WHERE PROJECTNAME = 'Your_Project_name';
PSPROJECTITEM : This table stores objects inserted into your Application Designer project.
Try it out:
SELECT * FROM PSPROJECTITEM
WHERE PROJECTNAME = 'Your_Project_name';
Query Tables: Tables holding individual query Meta Data.
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).
User Profile & Security Information: Tables holding Security Information,User Profile information including Primary Permission lists, Roles, email addresses, etc.
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
SQL Definitions: Tables holding SQL Object definitions.
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
Tree Manager:Tables holding Tree Manager Meta Data .
PSTREEDEFN : This table Contains the treename and related information.
Workflow:Tables holding Workflow Meta Data for Business Processes, Activities, Events,
and workflow items needing to be worked.
XLAT - Translate Values: Tables holding Translate Values for individual fields.
PSXLATDEFN : This table Holds the translate field name and version number for caching
PSXLATITEM : This table Holds the translate field name and their values
