PeopleTools Tables (Where the MetaData is Stored)

I heavily utilize the Tools tables for reporting, object-oriented programming, and speeding up development tasks. Here’s a list of ones that I have used in some fashion or another…

Also see the SQL Project Items List via SQL which utilizes many of these tables to pull all objects and details about the objects included in your PeopleSoft project.

Application Engine Meta Data: Tables holding Application Engine Meta Data for the AE, Section, Steps and SQL.
Change Control: Tables holding PeopleTools project change history and current locks on Tools objects.
Component Interface Meta Data: Tables holding Component Interface Meta Data.
Component Meta Data: Tables holding Component Meta Data.
Field Meta Data: Tables holding PeopleTools Field 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.
HTML & Image Meta Data: Tables holding HTML and Image Meta Data.
Menu Meta Data: Tables holding Menu Meta Data.
Message Catalog: Tables holding Message Catalog Entries.
Page Meta Data: Tables holding Page Meta Data.
PeopleCode Meta Data: Tables holding PeopleCode Meta Data.
Portal (Structure and Content): Tables holding portal content references and permission lists authorized.
Process Scheduler Information: Tables holding the process and job definitions along with information necessary to run a process.
Project Meta Data: Table holding PeopleTools project information (all objects in the project).
Query Tables: Tables holding individual query Meta Data.
Record Meta Data: Tables holding Record Meta Data including fields, field type, indexes, and tablespace.
Security Information: Tables holding Security Information.
SQL Definitions: Tables holding SQL Object definitions.
Tree Manager: Tables holding Tree Manager Meta Data .
User Profile & Security: Tables holding User Profile information including Primary Permission lists, Roles, email addresses, etc.
Workflow: Tables holding Workflow Meta Data for Business Processes, Activies, Events,
and workflow items needing to be worked.
XLAT - Translate Values: Tables holding Translate Values for individual fields.

Application Engine Meta Data
PSAEAPPLDEFN
AE header record; 1 row per app engine

PSAEAPPLSTATE
AE state records (shows which one is the default)

PSAEAPPLTEMPTBL
AE temp tables assigned

PSAESECTDEFN
AE sections: public or private

PSAESECTDTLDEFN
AE section: descriptions, market, DB Type, EFFDT, EFF_STATUS, and auto commit

PSAESTEPDEFN
AE steps within section: description, market, DB Type, EFFDT, EFF_STATUS

PSAESTMTDEFN
AE actions within AE step: Step type (SQL, Do Select, etc.) with SQLID. See SQL Definitions for how to pull the SQL

PSAESTEPMSGDEFN
AE message (parameters in each step)

AEREQUESTPARM
AE request parameters table behind the AE run control page.

Find All Records Referenced in App Engine
1. Find the Temp Records (TAO) that are used:

SELECT RECNAME FROM PSAEAPPLTEMPTBL WHERE AE_APPLID = 'MY_APP_ENGINE_NAME'2. If there are records, find the number of instances:

SELECT TEMPTBLINSTANCES FROM PSAEAPPLDEFN WHERE AE_APPLID = 'MY_APP_ENGINE_NAME'3. For each table found in step 1, create as many instances as step 2 indicates.
For example, step 1 returns record MY_AE_TEMP_TAO. The SQL executed below gives me a count of 4.
Therefore, I have 5 tables that could be used in my Application Engine: PS_MY_AE_TEMP_TAO, PS_MY_AE_TEMP_TAO1, PS_MY_AE_TEMP_TAO2, PS_MY_AE_TEMP_TAO3, PS_MY_AE_TEMP_TAO4

4. Retrieve all the App Engine SQL:

SELECT SQLTEXT FROM PSAESTMTDEFN AE, PSSQLTEXTDEFN S WHERE AE.AE_APPLID = 'MY_APP_ENGINE_NAME' AND S.SQLID = AE.SQLID
ORDER BY AE.SQLID, S.SEQNUM5. Visually break apart all the SQL statements to list the tables referenced in the App Engine.

6. Review all App Engine PeopleCode to see if any references to outside tables.



Change Control
PSCHGCTLHIST
History of PeopleTools objects locked with OPRID, project name, incident, and description

PSCHGCTLLOCK
Current PeopleTools objects locked with OPRID, project name, incident, and description



Component Interface Meta Data
PSBCDEFN
Component Interface header record; one row for each component interface

PSBCITEM
One row for each property on the component interface



Component Meta Data
PSPNLGRPDEFN
Component header flags, description, and component search records.

PSPNLGROUP
All pages in a component



Field Meta Data
Also see Project Items List via SQL for an example of how these tables can be utilized.

PSDBFIELD
Lists PeopleSoft fields and the field characteristics

FIELDTYPE Definitions
0 = Character
1 = Long Character
2 = Number
3 = Signed Number
4 = Date
5 = Time
6 = Date Time
8 = Image
9 = Image ReferencePSDBFLDLABL
Lists the field labels with DEFAULT_LABEL = 1 being the default label

PSXLATITEM
Lists Translate Values

PSFMTITEM
Lists field formats



Field Values for Tools Tables
Also see Project Items List via SQL for an example of how these tables can be utilized.

PSPROJECTITEM
PSPROJECTITEM.OBJECTTYPE
0 = Record
1 = Index
2 = Field
3 = Field Format
4 = Translate Value
5 = Page
6 = Menu
7 = Component
8 = Record PeopleCode
9 = Menu PeopleCode
10 = Query
11 = Tree Structure
12 = Tree
13 = Access Group
14 = Color
15 = Style
16 = Business Process Map
17 = Business Process
18 = Activity
19 = Role
20 = Process Definition
21 = Process Server Definition
22 = Process Type Definition
23 = Process Job Definition
24 = Process Recurrence Definition
25 = Message Catalog
26 = Dimension
27 = Cube Definition
28 = Cube Instance Definition
29 = Business Interlink
30 = SQL Object
Check value of OBJECTVALUE2
0 = SQL Object
1 = App Engine SQL
2 = Record View SQL
5 = Query for DDAUDIT or SYSAUDIT
6 = App Engine XML SQL
31 = File Layout
32 = Component Interface
33 = Application Engine Program
34 = Application Engine Section
35 = Message Node
36 = Message Channel
37 = Message
38 = Approval rule set
39 = Message PeopleCode
40 = Subscription PeopleCode
41 = N/A
42 = Component Interface PeopleCode
43 = Application Engine PeopleCode
44 = Page PeopleCode
45 = Page Field PeopleCode
46 = Component PeopleCode
47 = Component Record PeopleCode
48 = Component Record Field PeopleCode
49 = Image
50 = Style sheet
51 = HTML
52 = Not used
53 = Permission List
54 = Portal Registry Definitions
55 = Portal Registry Structure
56 = URL Definitions
57 = Application Packages
58 = Application Package PeopleCode
59 = Portal Registry User Homepage
60 = Problem Type
61 = Archive Templates
62 = XSLT
63 = Portal Registry User Favorite
64 = Mobile Page
65 = Relationships
66 = Component Interface Property PeopleCode
67 = Optimization Models
68 = File References
69 = File Type Codes
70 = Archive Object Definitions
71 = Archive Templates (Type 2)
72 = Diagnostic Plug In
73 = Analytic Model
79 = Service
80 = Service Operation
81 = Service Operation Handler
82 = Service Operation Version
83 = Service Operation Routing
84 = Info Broker Queues
85 = XLMP Template Definition
86 = XLMP Report Definition
87 = XMLP File Definition
88 = XMPL Data Source DefinitionPSPROJECTITEM.UPGRADEACTION
0 = Copy
1 = Delete
2 = None
3 = CopyPropPSPROJECTITEM.SOURCESTATUS
0 = Unknown
1 = Absent
2 = Changed
3 = Unchanged
4 = *Changed
5 = *Unchanged
6 = Same


PSRECDEFN
PSRECDEFN.RECTYPE
0 = SQL Table in DB
1 = SQL View in DB
2 = Derived/Work Record
3 = SubRecord
5 = Dynamic View
6 = Query View
7 = Temporary TablePSDBFIELD
PSDBFIELD.FIELDTYPE
0 = Character
1 = Long Character
2 = Number
3 = Signed Number
4 = Date
5 = Time
6 = Date Time
8 = Image
9 = Image ReferencePSPNLFIELD
PSPNLFIELD.FIELDTYPE
0 = Label
1 = Frame
2 = Group Box
3 = Static Image
4 = Edit Box
5 = Drop-Down List Box
6 = Long Edit Box
7 = Check Box
8 = Radio Button
9 = Image
10 = Scroll Bar
11 = Subpage
12 = Push Button/Hyperlink - Destination: PeopleCode Command
13 = Push Button/Hyperlink – Destination: Scroll Action
14 = Push Button/Hyperlink – Destination: Toolbar Action
15 = Push Button/Hyperlink – Destination: External Link
16 = Push Button/Hyperlink – Destination: Internal Link (Transfer)
17 = Push Button/Hyperlink – Destination: Process (AE, etc.)
18 = Secondary Page Button
19 = Grid
20 = Tree
21 = Push Button/Hyperlink - Destination: Secondary Page
22 = N/A
23 = Horizontal Rule
24 = Tab Separator (in a grid)
25 = HTML Area
26 = Push Button/Hyperlink – Destination: Prompt Action
27 = Scroll Area
30 = Chart
31 = Push Button/Hyperlink – Destination: Instant Messaging Action
32 = Analytic GridPSSQLDEFN
PSSQLDEFN.SQLTYPE
0 = SQL Object
1 = App Engine SQL
2 = Record View SQL
5 = Query for DDAUDIT or SYSAUDIT
6 = App Engine XML SQL

File Layout Definitions
PSFLDDEFN
Header record for File Layout

PSFLDSEGDEFN
Stores the segments for each layout

PSFLDFIELDDEFN
Stores the individual file fields for the segment



HTML & Image Meta Data
PSPNLHTMLAREA
Static HTML Areas on Pages with the HTMLValue

PSCONTDEFN
HTML & Image header record; last update time, etc.

PSCONTENT
HTML & Image storage



Menu Meta Data
PSMENUDEFN
Menu header table

PSMENUITEM
Menu Items



Message Catalog
PSMSGSETDEFN
Message Catalog header

PSMSGCATDEFN
Message Catalogs entries

Previous PeopleSoft message catalog tables:
PS_MESSAGE_SET_TBL
PS_MESSAGE_CATALOG



Page Meta Data
PSPNLDEFN
Page header table holding the field count, size, style, and description of the page

PSPNLFIELD
Lists all objects on the page

PSPNLFIELD.FIELDTYPE
0 = Label
1 = Frame
2 = Group Box
3 = Static Image
4 = Edit Box
5 = Drop-Down List Box
6 = Long Edit Box
7 = Check Box
8 = Radio Button
9 = Image
10 = Scroll Bar
11 = Subpage
12 = Push Button/Hyperlink - Destination: PeopleCode Command
13 = Push Button/Hyperlink – Destination: Scroll Action
14 = Push Button/Hyperlink – Destination: Toolbar Action
15 = Push Button/Hyperlink – Destination: External Link
16 = Push Button/Hyperlink – Destination: Internal Link (Transfer)
17 = Push Button/Hyperlink – Destination: Process (AE, etc.)
18 = Secondary Page Button
19 = Grid
20 = Tree
21 = Push Button/Hyperlink - Destination: Secondary Page
22 = N/A
23 = Horizontal Rule
24 = Tab Separator (in a grid)
25 = HTML Area
26 = Push Button/Hyperlink – Destination: Prompt Action
27 = Scroll Area
30 = Chart
31 = Push Button/Hyperlink – Destination: Instant Messaging Action
32 = Analytic Grid

PeopleCode Meta Data
PSPCMPROG
Stores the PeopleCode, LASTUPDOPRID and LASTUPDDTTM. The PeopleCode is stored in a binary format, and cannot be read by normal SQL. You can use a Java program to extract the code if necessary. Read more about this at: peoplesofttipster.com

PSPCMNAME
PeopleCode Reference table. This table lists all the PeopleSoft objects (FIELD, RECORD, SQL, etc.) that are referenced. For example, if you are about to make a change to a field, you can find everywhere in the system that it is referenced by using this table.



Portal (Structure and Content)
PSPRSMATTR
Portal Attribute Table

PSPRSMDEFN
Content References and Folders

PORTAL_PRNTOBJNAME = Parent Folder
PORTAL_OBJNAME = Content Reference Name
PORTAL_URI_SEG1 = Component Menu
PORTAL_URI_SEG3 = Market
PORTAL_URI_SEG2 = ComponentPSPRUHTABPGLT
Portal User HP Tab Pagelet

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
Process Scheduler Setup
PS_PRCSDEFN
Process Definition header with descriptions, server options, override options, and destination options

PS_PRCSDEFNGRP
Permission Lists authorized to use this process

PS_PRCSDEFNPNL
Components from which this process can be called

PS_PRCSMUTUALEXCL
Lists processes that cannot run at the same time to prevent data corruption, deadlocks, etc.

PS_PRCSDEFNCNTDIST
List roles or users to distribute process output

PS_PRCSDEFNXFER
List page that user will be sent to following a successful process completion

PS_PRCSDEFNNOTIFY
Process completion notification via email (on Error, Warning, Success)

PS_PRCSDEFNMESSAGE
Message to be sent during notify (from Message Catalog, custom text)

PS_PRCSJOBDEFN
Job header with description and runtime characteristics (run mode, priority, etc.)

PS_PRCSJOBITEM
Processes that will run for each Job

PS_PRCSJOBPNL
Components from which this job can be called.

PS_PRCSJOBCNTDIST
Job output Distribution List via email

PS_PRCSJOBNOTIFY
Job completion notification via email (on Error, Warning, Success)

PS_PRCSJOBMESSAGE
Message to be sent during notify (from Message Catalog, custom text)



Process Scheduler Transaction Records
PSPRCSRQST
Process Request Instance detail

PSPRCSPARMS
Process request parameters

PSPRCSQUE
Process request Queue

PSPRCSRQSTTEXT
Process Request Text

PS_CDM_LIST
Content Distribution Manager List

PS_CDM_AUTH
Content Distribution Manager List – User Access (Who can view output)



Process Scheduler Timings
BAT_TIMINGS_LOG
BAT_TIMINGS_DTL
BAT_TIMINGS_FN
See this link for great information on timings see this article at peoplesofttipster.com



Project Meta Data
Also see Project Items List via SQL for an example of how these tables can be utilized.

PSPROJECTDEFN
Project header table (Short & Long Project Description fields)

PSPROJECTITEM
Objects in the project

PSPROJECTITEM.OBJECTTYPE
0 AND RECTYPE FROM PSRECDEFN WHERE RECNAME = OBJECTVALUE1
0 = Record
1 = View
2 = Work Record
3 = Sub Record
5 = Dynamic View
6 = Query View
7 = Temporary Table
1 = Index
2 = Field
3 = Field Format
4 = Translate Value
5 = Page
6 = Menu
7 = Component
8 = Record PeopleCode
9 = Menu PeopleCode
10 = Query
11 = Tree Structure
12 = Tree
13 = Access Group
14 = Color
15 = Style
16 = Business Process Map
17 = Business Process
18 = Activity
19 = Role
20 = Process Definition
21 = Process Server Definition
22 = Process Type Definition
23 = Process Job Definition
24 = Process Recurrence Definition
25 = Message Catalog
26 = Dimension
27 = Cube Definition
28 = Cube Instance Definition
29 = Business Interlink
30 AND WHEN OBJECTVALUE2 = 0 THEN SQL Object
WHEN OBJECTVALUE2 = 1 THEN App Engine SQL
WHEN OBJECTVALUE2 = 2 THEN Record View SQL
WHEN OBJECTVALUE2 = 5 THEN Query for DDAUDIT or SYSAUDIT
WHEN OBJECTVALUE2 = 6 THEN App Engine XML SQL
31 = File Layout
32 = Component Interface
33 = Application Engine Program
34 = Application Engine Section
35 = Message Node
36 = Message Channel
37 = Message
38 = Approval rule set
39 = Message PeopleCode
40 = Subscription PeopleCode
41 = N/A
42 = Component Interface PeopleCode
43 = Application Engine PeopleCode
44 = Page PeopleCode
45 = Page Field PeopleCode
46 = Component PeopleCode
47 = Component Record PeopleCode
48 = Component Record Field PeopleCode
49 = Image
50 = Style sheet
51 = HTML
52 = Not used
53 = Permission List
54 = Portal Registry Definitions
55 = Portal Registry Structure
56 = URL Definitions
57 = Application Packages
58 = Application Package PeopleCode
59 = Portal Registry User Homepage
60 = Problem Type
61 = Archive Templates
62 = XSLT
63 = Portal Registry User Favorite
64 = Mobile Page
65 = Relationships
66 = Component Interface Property PeopleCode
67 = Optimization Models
68 = File References
69 = File Type Codes
70 = Archive Object Definitions
71 = Archive Templates - Type 2
72 = Diagnostic Plug In
73 = Analytic Model
79 = Service
80 = Service Operation
81 = Service Operation Handler
82 = Service Operation Ver
83 = Service Operation Routing
84 = Info Broker Queues
85 = XLMP Template Definition
86 = XLMP Report Definition
87 = XMLP File Definition
88 = XMLP Data Source Definition

Query Tables
PSQRYDEFN
Query header information

PSQRYFIELD
Displays all fields used in the SELECT clause (COLUMNNUM = 1) and fields used in the WHERE clause (COLUMNNUM = 0)

PSQRYCRITERIA
Displays all fields used in the WHERE clause. You can get the name of the fields by joining PSQRYCRITERIA.LCRTFLDNUM to PSQRYFIELD.FLDNUM

PSQRYEXPR
Stores query expressions (PSQRYCRITERIA.R1CRTEXPNUM to PSQRYEXPR.EXPNUM or PSQRYFIELD.FLDEXPNUM to PSQRYEXPR.EXPNUM)

PSQRYBIND
Stores query bind variable definition

PSQRYRECORD
Stores all records used in all aspects of query creation (SELNUM > 1 when in a subquery)

PSQRYSELECT
Stores query and subquery relationships along with record and field counts

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).



Record Meta Data
Also see Project Items List via SQL for an example of how these tables can be utilized.


PSRECDEFN
Record header table. Tracks number of fields and number of indexes in record along with descriptions

PSRECDEFN.RECTYPE
0 = SQL Table in DB
1 = SQL View in DB
2 = Derived/Work Record
3 = Sub Record
5 = Dynamic View
6 = Query View
7 = Temporary TablePSRECFIELD
Fields in the record (subrecord fields are not listed) along with field order, field defaults, edit tables

PSRECFIELDALL
All fields in the record, including subrecord fields

PSINDEXDEFN
Contains 1 row per index defined for a record

PSKEYDEFN
Contains all fields that make up the index, and their position in the key structure

PSTBLSPCCAT
Lists available tablespace

PSRECTBLSPC
DB Name and tablespace allocated for a SQL record



Security Information
PSAUTHITEM
What Permission Lists have access to a page, and what are authorized actions?

SELECT CLASSID, MENUNAME, BARNAME, BARITEMNAME, PNLITEMNAME, DECODE(DISPLAYONLY, 0, 'N', 1, 'Y') AS "Display Only",
CASE AUTHORIZEDACTIONS
WHEN 1 THEN 'Add'
WHEN 2 THEN 'Update/Display'
WHEN 3 THEN 'Add, Update/Display'
WHEN 4 THEN 'Update/Display All'
WHEN 5 THEN 'Add, Update/Display All'
WHEN 6 THEN 'Update/Display, Update/Display All'
WHEN 7 THEN 'Add, Update/Display, Update/Display All'
WHEN 8 THEN 'Correction'
WHEN 9 THEN 'Add, Correction'
WHEN 10 THEN 'Update/Display, Correction'
WHEN 11 THEN 'Add, Update/Display, Correction'
WHEN 12 THEN 'Update/Display All, Correction'
WHEN 13 THEN 'Add, Update/Display All, Correction'
WHEN 14 THEN 'Update/Display, Update/Display All, Correction'
WHEN 15 THEN 'Add, Update/Display, Update/Display All, Correction'
ELSE 'SPECIAL' END AS "Authorized Actions",
AUTHORIZEDACTIONS
FROM PSAUTHITEMPSAUTHBUSCOMP
What Permission List has access to a component interface?

SELECT CLASSID FROM PSAUTHBUSCOMP WHERE BCNAME = 'MY_COMPONENT_INTERFACE'PSCLASSDEFN
Permission List header table

PSPRSMPERM
Portal Structure Permissions

PSROLECLASS
Permission Lists in roles

PSROLEDEFN
Role header table



SQL Definitions
PSSQLDEFN
Header record for all SQL from views and application engine

PSSQLTEXTDEFN
Stores the SQL definition

PSSQLDESCR
Stores SQL objects descriptions, market, DB Type, and EFFDT



Tree Manager
PSTREEDEFN
Tree Definition and Properties

PSTREENODE
Folders and records (nodes of the tree/tree node type)

PSTREEBRANCH
Tree Branch

PSTREELEAF
Tree Leaf

PSTREELEVEL
Tree Level



User Profile & Security
PSOPRDEFN
User ID header table: User Name, email, Primary & Row security permission lists

PS_ROLEXLATOPR
Workflow Routing Preferences; email; workflow attributes

PSUSEREMAIL
Users email

PSROLEUSER
OPRID (Roleuser) and Roles granted

PSOPRCLS
OPRID and associated Permission lists



Workflow
Meta Data
PSBUSPROCDEFN
Business Process Header

PSACTIVITYDEFN
Activity Header

PSBUSPROCITEM
Activity items in each activity

PSEVENTDEFN
Event items in each activity

PS_APPR_RULE_DETL
Approval rule definition details

PS_APPR_RULE_FIELD
Approval rule definition route control

PS_APPR_RULE_AMT
Approval rule amounts

PS_RTE_CNTL_LN
Route control profile line

PS_RTE_CNTL_RUSER
RoleUser route control profiles

PS_RTE_CNTL_TYPE
Route control type

PS_RTE_CNTL_HDR
Routing control type header



Transaction Record
PSWORKLIST
Lists worklist entries by event and OPRID



XLAT – Translate Values
PSXLATITEM
Stores field translate values (PeopleSoft version 8.4 and above)

XLATTABLE
Stores field translate values (PeopleSoft version prior to 8.4)

Operators (Users)

Record	Description
PSOPRDEFN	Stores all operators (users) in the PeopleSoft system. Also stores their employee ID (EMPLID), encrypted password, primary permission list, default navigator home page, process profile permission list and row security permission list.
PSROLEUSER	The highest level of security access is defined by roles (think of them as groups). This table stores the roles the user belongs to.
PSOPRCLS	Roles link together permission lists which are the security objects that define access to components, pages, and other areas of the system. This view returns the permission lists that a user has access to via their roles. Note that prior to PeopleTools 8, permission lists were synonymous with classes and most of the security tables still use this convention.
PSOPRALIAS	Aliases can be mapped to a particular operator ID (user). The obvious alias is employee ID (EMPLID) but others include external organisation ID (EXT_ORG_ID) and customer ID (CUST_ID). All ways of referring to the same entity.
PSOPRALIASTYPE	This is the setup table for operator aliases
PSOPRALIASFIELD	This is the setup table that maps operator aliases to records & fields
PSUSERATTR	User attributes store the a hint password question & response for a user (if this is enabled)
PSUSEREMAIL	Email addresses for users.

Roles

Record	Description
PSROLEDEFN	Stores roles and their properties. Roles can be assigned dynamically through Query, PeopleCode or LDAP. Roles are also used in conjunction with Workflow and routing.
PSROLECLASS	Roles are made of up of one or permission lists, and this table links the two together. Very handy.

Permission Lists

Record	Description
PSCLASSDEFN	Permission lists are where the security really happens. They provide access to menus, components and pages and a host of other security including PeopleTools, Process security, Component Interfaces, Web Libraries, Web Services, Personalisations, Query and Mass Change.
PSAUTHITEM	The link between permission lists and menus
PSAUTHBUSCOMP	The link between permission lists and component interfaces and their methods
PSAUTHOPTN	The link between permission lists and personalisations
PSAUTHPRCS	The link between permission lists and process groups
PSAUTHSIGNON	The link between permission lists and signon times
PSAUTHWEBLIBVW	A view linking permission lists and access to web libraries (really just Menus in PSAUTHITEM that begin with WEBLIB_).
PSAUTHWS	The link between permission lists and web services (service operations)
PS_SCRTY_ACC_GRP	The link between permission lists, trees and query access groups
PS_MC_OPR_SECURITY	The link between permission lists and mass change templates. This is an odd table, it uses the field OPRID but really it links permission lists

Portal

The PeopleSoft Enterprise Portal basically refers to the navigation system that links menus and components to the PeopleSoft online interface, navigation and search functionality. Access to anything in the online application is handled through portal security. The records associated with this include:

Record	Description
PSPRSMDEFN	Stores the structure of the portal registry. This data is stored in a hierarchical (tree) structure within the table. The field PORTAL_URI_SEG1 is the menu, PORTAL_URI_SEG2 is the component, and PORTAL_URI_SEG3 is the market.
PSPRSMPERM	Stores permission lists associated with access to everything within the portal registry

How to use GetAttachment and PutAttachment PeopleSoft Builtin Functions

How to use GetAttachment and PutAttachment PeopleSoft Builtin Functions

A while ago I had a requirement to transfer attachments from a source FTP server location and store it into a database record. To do that you have to use GetAttachment and PutAttachment PeopleCode functions. Basically GetAttachment PeopleCode function can be used to download the attachment from its source storage location (say FTP or database record) to the application server and then you can use PutAttachment PeopleCode function to upload the attachment from the app server file system to the specified storage location (e.g. to a database record).

The GetAttachment and PutAttachment the function Parameters are as follows;

GetAttachment(URLSource, DirAndSysFileName, DirAndLocalFileName[, LocalDirEnvVar[, PreserveCase]])

PutAttachment(URLDestination, DirAndSysFileName, DirAndLocalFileName[, LocalDirEnvVar[, PreserveCase[, AllowLargeChunks]]])

The first parameter for the GetAttachment function is URLSource. This indicates the source location of the file. This can be implemented as a URL object with the form of URL.URL_ID. URL object is an identifier of the storage location including the protocol to be used (e.g. ftp) and the address of the storage location. URL objects can be created and maintained using URL maintenance page; PeopleTools > Adminstration > Utilities > URLs.
Length of the full URL is limited to 254 characters. For the file attachment functionality, in specifying the URL for the FTP server, the FTP server's machine name can be more than 30 characters, but the length of the full URL is limited to 120 characters.
Following protocol types can be used for the URLID

• FTP
• FTPS (FTP over SSL)
• SFTP
• HTTP
• HTTPS

For plain FTP URL object can be referenced in one of the following formats;

• ftp://<username>:<password>@<machinename> where the FTP user and FTP password are provided in clear text, but the URL information comes from the URL object. 
• ftp://<username>@<machinename> where the FTP user is provided in clear text, but the FTP password and the URL information comes from the URL object.
  ftp://<machinename> where all the required information comes from the URL.

The first parameter for the PutAttachment function is URLDestination. This indicates the destination location of the file (in this case database record). An URL object can be create to indicate the database record you want to store the attachments.

Things to consider when storing attachments in the database

If the files are stored in a database table, PeopleSoft divided the files in to chunks before storing in to the database table. The chunk size is governed by the value of the Maximum Attachment Chunk Size field on the PeopleTools Options page (PeopleTools > Utilities > Administration > PeopleTools Options). 

As an example you can use this PeopleCode to Get and Put attachments as follows;

Local number &retCodeGet, &retCodePut;
   &retCodeGet = GetAttachment(URL.TEST_URL, "test_123.pdf", "Example_test.pdf");
   If &retCodeGet = %Attachment_Success Then
      &FileName = "Example_test.pdf";
      &retCodePut = PutAttachment(URL.ATTACHMENT_DB_REC, &UniqueName, &FileName);
      If &retCodePut = %Attachment_Success Then
         WinMessage("Put Att Success: " | &retCodePut);
      Else
         WinMessage("Error occurred in Put Att: " | &retCodePut);
      End-If;
   Else
      WinMessage("Error occurred in Get Att: " | &retCodeGet);
   End-If;

PeopleSoft Compare_Report_Summary

SELECT CASE A.OBJECTTYPE
   when  0 then '00- Record'
   when  1 then '01- Index'
   when  2 then '02- Field'
   when  3 then '03- Format Definition'
   when  4 then '04- Translate values'
   when  5 then '05- Pages'
   when  6 then '06- Menus'
   when  7 then '07- Components'
   when  8 then '08- Record PeopleCode'
   when  9 then '09- Menu PeopleCode'
   when 10 then '10- Queries'
   when 11 then '11- Tree structures'
   when 12 then '12- Trees'
   when 13 then '13- Access groups'
   when 14 then '14- Colors'
   when 15 then '15- Styles'
   when 16 then '16- Not used'
   when 17 then '17- Business Processes'
   when 18 then '18- Activities'
   when 19 then '19- Roles'
   when 20 then '20- Process Definitions'
   when 21 then '21- Process servers'
   when 22 then '22- Process types'
   when 23 then '23- Process jobs'
   when 24 then '24- Process recurrences'
   when 25 then '25- Message catalog entries'
   when 26 then '26- Dimensions'
   when 27 then '27- Cube definitions'
   when 28 then '28- Cube instance definitions'
   when 29 then '29- Business interlinks'
   when 30 then '30- SQL definitions'
   when 31 then '31- File layout definitions'
   when 32 then '32- Component interfaces'
   when 33 then '33- Appication Engine programs'
   when 34 then '34- Application Engine sections'
   when 35 then '35- Message nodes'
   when 36 then '36- Message channels'
   when 37 then '37- Message definitions'
   when 38 then '38- Approval rule sets'
   when 39 then '39- Message PeopleCode'
   when 40 then '40- Subscription PeopleCode'
   when 41 then '41- Not Used'             
   when 42 then '42- Component interface PeopleCode'
   when 43 then '43- Application engine PeopleCode'
   when 44 then '44- Page PeopleCode'
   when 45 then '45- Page Field PeopleCode'
   when 46 then '46- Component PeopleCode'
   when 47 then '47- Component record PeopleCode'
   when 48 then '48- Component record field PeopleCode'
   when 49 then '49- Images'
   when 50 then '50- Style sheets'
   when 51 then '51- HTML'
   when 52 then '52- Not Used'
   when 53 then '53- Permission lists'
   when 54 then '54- Portal registry definitions'
   when 55 then '55- Portal registry structures'
   when 56 then '56- URL definitions'
   when 57 then '57- Application Packages'     
   when 58 then '58- Application Package PeopleCode'
   when 59 then '59- Portal Registry User homepages'
   when 60 then '60- Problem type definitions'
   when 61 then '61- Archive templates (deprecated in PeopleTools release 8.44 and above)'
   when 62 then '62- XSLT'
   when 63 then '63- Portal Registry User Favorites'
   when 64 then '64- Mobile Pages'
   when 65 then '65- Relationships'
   when 66 then '66- CI Property PeopleCode'
   when 67 then '67- Optimization Model'
   when 68 then '68- File Reference'
   when 69 then '69- File Reference Type Code'
   when 70 then '70- Archive Object Definitions'
   when 71 then '71- Archive Templates (Type 2)'
   when 72 then '72- Diagnostic Plug-Ins'
   when 73 then 'Analytic Models'
   when 74 then '74- Not Used'
   when 75 then 'Java Portlet User Preferences'
   when 76 then 'WSRP Remote Producers'
   when 77 then 'WSRP Remote Portlets'
   when 78 then 'WSRP Cloned Portlet Handles'
   when 79 then 'Services'
   when 80 then 'Service Operations'
   when 81 then 'Service Operation Handlers'
   when 82 then 'Service Operation Version'
   when 83 then 'Service Operation Routings'
   when 84 then 'IB Queues'
   when 85 then 'BIP Template Definitions'
   when 86 then 'BIP Report Definitions'
   when 87 then 'BIP File Definitions'
   when 88 then 'BIP Data Source Definitions'
   when 89 then 'WSDL'
   when 90 then 'Message Schemas'
   when 91 then 'Connected Query Definitions'
   when 92 then 'Logical Schemas'
   when 93 then 'XML Schemas'
   when 94 then 'Relational Schemas'
   when 95 then 'Dependency Documents'
   when 96 then 'Document Schema'
   when 97 then 'Essbase Cube Dimensions'
   when 98 then 'Essbase Cube Outlines'
   when 99 then 'Essbase Cube Connections'
   when 100 then 'Essbase Cube Templates'
   when 101 then 'Delimited Schemas'
   when 102 then 'Positional Schemas'
   when 103 then 'Application Data Set Definitions'
   when 104 then 'Test Defintions'
   when 105 then 'Test Framework Test Cases'
   when 106 then 'Application Data Set Bindings'
   when 107 then 'Feed Definitions'
   when 108 then 'Feed Categories'
   when 109 then 'Feed Data Type'
   when 110 then 'JSON Documents'
   when 111 then 'Related Content Definition'
   when 112 then 'Related Content Services'
   when 113 then 'Related Content Configurations'
   when 114 then 'Related Content Layouts'
   when 115 then 'Search Attributes'
   when 116 then 'Search Definitions'
   when 117 then 'Search Categories'
   when 118 then 'Search Contexts'
   when 119 then 'Integration Groups'
   when 120 then 'HTML Documents'
   when 121 then 'MAP Layouts'
   when 122 then 'MAP Templates'
   when 123 then 'Composite Query'
   when 124 then 'MAP PeopleCode'
   when 125 then 'MAP Admin Manager'
   ELSE TO_CHAR(A.OBJECTTYPE) End OBJECTTYPE
   , A.OBJECTVALUE1
   ,A.OBJECTVALUE2
   ,A.OBJECTVALUE3
   ,A.OBJECTVALUE4
   ,CASE A.sourcestatus
             when  0 then 'Unknown'
             when  1 then 'Absent'
             when  2 then 'Changed'
             when  3 then 'Unchanged'
             when  4 then '*Changed'
             when  5 then '*Unchanged'
             when  6 then 'Same'
             ELSE TO_CHAR(A.sourcestatus) End  SOURCE
           ,CASE A.targetstatus
             when  0 then 'Unknown'
             when  1 then 'Absent'
             when  2 then 'Changed'
             when  3 then 'Unchanged'
             when  4 then '*Changed'
             when  5 then '*Unchanged'
             when  6 then 'Same'
             ELSE TO_CHAR(A.targetstatus) End  TARGET,
          CASE A.upgradeaction
             when  0 then 'Copy'
             when  1 then 'Delete'
             when  2 then 'None'
             when  3 then 'Copy Prop'
             ELSE TO_CHAR(A.upgradeaction) End  Upgrade,
      CASE A.TAKEACTION
      when 1 then 'YES'
      When 0 Then 'NO'
      end TAKEACTION
          FROM SYSADM.PSPROJECTITEM A
where PROJECTNAME ='CS92UPG_MASTER';