The Application Package is easier to use, and in my opinion more reliable with HTML text than the function SendMail() which has been traditionally used.Import PT_MCF_MAIL:*;
Local PT_MCF_MAIL:MCFOutboundEmail &email;
Local boolean &done;
Local integer &res;
Function Email_Example(&Email_Address_To As string, &Email_Address_From As string, &Subject As string,
&BodyText_or_HTML as string, &ContentType as string);
/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
/* &Email_Address_To takes standard email format: */
/* myname@mydomain.com – if multiple addresses then */
/* separate by commas */
/* &Email_Address_From takes standard email format: */
/* myname@mydomain.com */
/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
/* Instantiate the App Package Class */
/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
&email = create PT_MCF_MAIL:MCFOutboundEmail();
/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
/* Set Email Object Properties */
/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
&email.From = &Email_Address_From;
&email.Recipients = &Email_Address_To;
&email.Subject = &Subject;
&email.Text = &BodyText_or_HTML;
/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
/* Could be "text/plain", "text/html", or */
/* "multipart/alternative" */
/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
&email.ContentType = &ContentType;
/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
/* Invoke the method to generate the email */
/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
&res = &email.Send();
Evaluate &res
When %ObEmail_Delivered
/* every thing ok */
&done = True;
MessageBox(0, "", 0, 0, "Email Sent Successfully");
Break;
When %ObEmail_NotDelivered
/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
/* Check &email.InvalidAddresses, &email.ValidSentAddresses */
/* and &email.ValidUnsentAddresses */
/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
&done = False;
MessageBox(0, "", 0, 0, "Email Not delivered"
&eMail.InvalidAddresses &eMail.ValidSentAddresses
&eMail.ValidUnsentAddresses);
Break;
When %ObEmail_PartiallyDelivered
/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
/* Check &email.InvalidAddresses, &email.ValidSentAddresses */
/* and &email.ValidUnsentAddresses */
/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
&done = True;
MessageBox(0, "", 0, 0, "Email Partially delivered"
&eMail.InvalidAddresses &eMail.ValidSentAddresses
&eMail.ValidUnsentAddresses);
Break;
When %ObEmail_FailedBeforeSending
/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
/* Get the Message Set Number, message number; */
/* Or just get the formatted messages from */
/* &email.ErrorDescription, email.ErrorDetails; */
/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
&done = False;
MessageBox(0, "", 0, 0, "Email Failed Before Sending"
&eMail.ErrorDescription &eMail.ErrorDetails);
Break;
End-Evaluate;
End-Function;
Tuesday, December 28, 2010
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)
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)
System Process Request
System Process Requests is a component (PRCSMULTI) located in:
PeopleTools > Process Scheduler > System Process Requests
That allows you to run a number of system level administration processes, jobs and tests.
For developer's this is a great place to find out how to do something as it provides a number of different ways of using the process scheduler. I believe one of the best ways to get better at PeopleSoft development is to understand exactly how you can do things in the system - there may be options out there that you didn't even know about!
After you create a run control, there are three pages in the component:
Process Request Dialog for running system processes
Component Interface to test running a process (XRFMENU) through the PROCESSREQUEST component interface
ProcessRequest PeopleCode to test running a process through PeopleCode
System Processes
Press the run button on the run control and you'll be give a list of system processes. You can select one or more of these and run them. The processes provided are a combination of system processes, example/test processes, and jobs. Some of the useful ones include:
All Process Type (ALLTYPES) job which runs test COBOL, Crystal and SQR programs.
Database Designer/Database Audit (DDDAUDIT) which checks for inconsistencies between PeopleSoft records/indexes and the equivalent database records/indexes.
Export User Tables (EXPRTUSR) which runs a data mover script located at PS_HOME\scripts\userexport.dms and exports all users (operators) and out of your system and into an output file called USEREXPORT.DAT. This is also a good example of how to run datamover scripts from the process scheduler.
Process scheduler server clean (PRCSSRVCLN) which seems to be for clearing the process scheduler cache?
Process scheduler system purge (PRCSYSPURGE) for purging process scheduler requests, archiving report manager tables, and it also calls PSXPARCHATTR for archiving XML publisher reports.
XML Publisher File Cleanup (PSXPCLEAN) for cleaning up working tables associated with XML Publisher.
XML Publisher Purge (PSXPDELATTR) which publishes the message for the PSXP_CLEANATTR through Integration Broker. This appears to be for purging XML publisher report search data. This message has a handler, PurgeAttributes which lives in the application package class PSXP_REPORTMGR.AttributeDelAsync and this is the code that does the cleanup work.
Swap Audit Report (SWPAUDIT) which checks for data integrity problems that may occur when swapping language settings.
System Audit (SYSAUDIT) which is a very powerful tool for checking for inconsistencies in PeopleTools definitions.
A number of cross reference (XRF*) reports. These are useful for impact analysis - for example if you want to see what fields are reference by PeopleCode (in case you want to modify that field) then run XRFFLPC.
Running processes through a Component Interface
The PeopleCode that runs the processes through the PROCESSREQUEST component interface is located in Component Record Field PeopleCode: PRCSMULTI.GBL.PRCSSAMPLEREC.RUNCCNTLCOMPINTF.FieldChange.
I'm not sure why you would want to run a process request this way, but this code serves as another good, simple example of how to use a component interface through PeopleCode.
Running processes through ProcessRequest PeopleCode
The PeopleCode that runs the processes through the ProcessRequest PeopleCode is located in Component Record Field PeopleCode: PRCSMULTI.GBL.PRCSSAMPLEREC.RUNCCNTLPRCSRQST.FieldChange.
If you ever want to fire another process in your PeopleCode program, then this is how to do it (or you can use the component interface approach).
PeopleTools > Process Scheduler > System Process Requests
That allows you to run a number of system level administration processes, jobs and tests.
For developer's this is a great place to find out how to do something as it provides a number of different ways of using the process scheduler. I believe one of the best ways to get better at PeopleSoft development is to understand exactly how you can do things in the system - there may be options out there that you didn't even know about!
After you create a run control, there are three pages in the component:
Process Request Dialog for running system processes
Component Interface to test running a process (XRFMENU) through the PROCESSREQUEST component interface
ProcessRequest PeopleCode to test running a process through PeopleCode
System Processes
Press the run button on the run control and you'll be give a list of system processes. You can select one or more of these and run them. The processes provided are a combination of system processes, example/test processes, and jobs. Some of the useful ones include:
All Process Type (ALLTYPES) job which runs test COBOL, Crystal and SQR programs.
Database Designer/Database Audit (DDDAUDIT) which checks for inconsistencies between PeopleSoft records/indexes and the equivalent database records/indexes.
Export User Tables (EXPRTUSR) which runs a data mover script located at PS_HOME\scripts\userexport.dms and exports all users (operators) and out of your system and into an output file called USEREXPORT.DAT. This is also a good example of how to run datamover scripts from the process scheduler.
Process scheduler server clean (PRCSSRVCLN) which seems to be for clearing the process scheduler cache?
Process scheduler system purge (PRCSYSPURGE) for purging process scheduler requests, archiving report manager tables, and it also calls PSXPARCHATTR for archiving XML publisher reports.
XML Publisher File Cleanup (PSXPCLEAN) for cleaning up working tables associated with XML Publisher.
XML Publisher Purge (PSXPDELATTR) which publishes the message for the PSXP_CLEANATTR through Integration Broker. This appears to be for purging XML publisher report search data. This message has a handler, PurgeAttributes which lives in the application package class PSXP_REPORTMGR.AttributeDelAsync and this is the code that does the cleanup work.
Swap Audit Report (SWPAUDIT) which checks for data integrity problems that may occur when swapping language settings.
System Audit (SYSAUDIT) which is a very powerful tool for checking for inconsistencies in PeopleTools definitions.
A number of cross reference (XRF*) reports. These are useful for impact analysis - for example if you want to see what fields are reference by PeopleCode (in case you want to modify that field) then run XRFFLPC.
Running processes through a Component Interface
The PeopleCode that runs the processes through the PROCESSREQUEST component interface is located in Component Record Field PeopleCode: PRCSMULTI.GBL.PRCSSAMPLEREC.RUNCCNTLCOMPINTF.FieldChange.
I'm not sure why you would want to run a process request this way, but this code serves as another good, simple example of how to use a component interface through PeopleCode.
Running processes through ProcessRequest PeopleCode
The PeopleCode that runs the processes through the ProcessRequest PeopleCode is located in Component Record Field PeopleCode: PRCSMULTI.GBL.PRCSSAMPLEREC.RUNCCNTLPRCSRQST.FieldChange.
If you ever want to fire another process in your PeopleCode program, then this is how to do it (or you can use the component interface approach).
The Peoplesoft Sign on Process
The following six steps will walk us through the PeopleSoft signon process and explain where the Signon Peoplecode comes into play here.
1. As is the process in ALL PeopleSoft applications, the user signs on with their User ID & Password and the system then validates the ID & password against the PSOPRDEFN table. If ID & Password are valid, then the user is successfully signed on. This will be done no matter what type of authentication process you are going to use. You cannot get around this, as this is the way the application is designed to work.
2. If the initial signon authentication against the PSOPRDEFN table is unsuccessful, then the system Checks to see if LDAP Authentication Signon Peoplecode is enabled. If it is not, then the user is denied access assuming that the user is trying to authenticate with their LDAP user id and password.
3. If the LDAP Authentication Signon Peoplecode is enabled, then system invokes LDAP Authentication with the directory via the LDAP_SEARCH and LDAP_BIND Business Interlinks.
4. Using these businesses interlinks the Signon Peoplecode will then validates the User ID & Password Against the directory using the values you have setup in the directory authentication setup pages.
5. If the Directory does not validate the User ID & password, then the Directory Authentication fails, the PeopleSoft Authentication fails, and the user is denied access. This failure could happen for a number of Reasons.
6. However, if the directory authentication is successful, then a user profile is created using the USER_PROFILE Component Interface, assuming you have the USER_PROFILESYNCH is enabled as Part of your LDAP authentication setup, the PeopleSoft Authentication is validated, and the signon is Successful.
SIGNON PEOPLECODE
There are three technologies used during this signon process and they are signon Peoplecode, business Interlinks, and USER_PROFILE component interface.
Signon Peoplecode is the ability to execute Peoplecode during the signon process. Any Peoplecode Program can be executed at signon time. PeopleSoft delivers LDAP Authentication Signon Peoplecode As of People Tools 8.
LDAP Authentication Signon Peoplecode uses the LDAP Business Interlink and the USER_PROFILE Component Interface (UPCI) to verify the USER NAME and PASSWORD and automatically update or Create the user profile information in the PeopleSoft database if it does not already exist.
The LDAP_SEARCH and LDAP_BIND Business Interlinks are called by Signon Peoplecode for LDAP authentication and come delivered, ready to use, with PeopleSoft 8.
The LDAP Business Interlink provides an Application Programming Interface (API) to LDAP with Peoplecode. The API is used to access LDAP compliant directories.
The first thing you need to do is to navigate to the PeopleTools > Maintain Security > Setup > Directory
Authentication page. – to do the set for Directory access server.
1. As is the process in ALL PeopleSoft applications, the user signs on with their User ID & Password and the system then validates the ID & password against the PSOPRDEFN table. If ID & Password are valid, then the user is successfully signed on. This will be done no matter what type of authentication process you are going to use. You cannot get around this, as this is the way the application is designed to work.
2. If the initial signon authentication against the PSOPRDEFN table is unsuccessful, then the system Checks to see if LDAP Authentication Signon Peoplecode is enabled. If it is not, then the user is denied access assuming that the user is trying to authenticate with their LDAP user id and password.
3. If the LDAP Authentication Signon Peoplecode is enabled, then system invokes LDAP Authentication with the directory via the LDAP_SEARCH and LDAP_BIND Business Interlinks.
4. Using these businesses interlinks the Signon Peoplecode will then validates the User ID & Password Against the directory using the values you have setup in the directory authentication setup pages.
5. If the Directory does not validate the User ID & password, then the Directory Authentication fails, the PeopleSoft Authentication fails, and the user is denied access. This failure could happen for a number of Reasons.
6. However, if the directory authentication is successful, then a user profile is created using the USER_PROFILE Component Interface, assuming you have the USER_PROFILESYNCH is enabled as Part of your LDAP authentication setup, the PeopleSoft Authentication is validated, and the signon is Successful.
SIGNON PEOPLECODE
There are three technologies used during this signon process and they are signon Peoplecode, business Interlinks, and USER_PROFILE component interface.
Signon Peoplecode is the ability to execute Peoplecode during the signon process. Any Peoplecode Program can be executed at signon time. PeopleSoft delivers LDAP Authentication Signon Peoplecode As of People Tools 8.
LDAP Authentication Signon Peoplecode uses the LDAP Business Interlink and the USER_PROFILE Component Interface (UPCI) to verify the USER NAME and PASSWORD and automatically update or Create the user profile information in the PeopleSoft database if it does not already exist.
The LDAP_SEARCH and LDAP_BIND Business Interlinks are called by Signon Peoplecode for LDAP authentication and come delivered, ready to use, with PeopleSoft 8.
The LDAP Business Interlink provides an Application Programming Interface (API) to LDAP with Peoplecode. The API is used to access LDAP compliant directories.
The first thing you need to do is to navigate to the PeopleTools > Maintain Security > Setup > Directory
Authentication page. – to do the set for Directory access server.
Application Classes in Peoplesoft - Reference sheet
Application Classes in Peoplesoft – Reference sheet / Points remember
Importing packages or classes
::<…>:
Class Extensions represents the “is-a” relationship.
When a class extends another class, it’s called a subclass of that class.
No Multiple inheritances or Concept of interface in Application classes.
Instance variables in the class are accessible in all the objects of that class.
Application programs generally pass parameters by value, which is not the same as for existing functions.
Parameter passing with object data types is by reference.
When the objects are passed to method and it reassign the object with new object it will not reflected in the calling method.
Application Programs use the out specifier to pass a parameter by reference.
Method increment (&Value as number out); rem passed by reference.
Create is the key word used to create the class object.
Local MyObjectClass &o1 = Create MyobjectClass (‘A’);
Local MyObjectClass &o1 = Create Test: Examples: MyobjectClass (‘A’);
If parameter in a method is having out specifier then parameter should be a variable and cannot be constant.
A class that extends another class must have constructor, and in the constructor it must initialize its super class.
To initialize a superobject, an instance of the superclass is assigned to the keyword %Super in the constructor for the subclass.
Class Example extends ExampleBase
Method Example ();
End-class;
Method Example
%Super = create ExampleBase ();
&BaseStrin = ¤tBaseString;
&Slashstring = &BaseString;
End-Method;
Before the constructor runs, the instance variables for the class are set by default takes on their declared types.
An application class doesn’t have destructors, that is, a method called just before an object is destroyed. The People code runtime environment generally cleans up any held resources.
When application class properties and instance variables are used as the argument to functions or methods, they are always passed by value, never by reference.
%This is can be used to self reference. This is to refer the same object.
%Super = %This – this should never be done it will go to an infinite loop.
Import Fruit:* - imports all the classes in the application package.
Import statements are validated when you save the Peoplecode.
Peoplesoft recommends that you use read-write or ready only properties instead of creating methods name GetXxx and SetXxx.
Getter and Setter syntax
Get Propertyname
---
End-get;
External Functions Declarations are allowed in application classes, in the global and component variable declarations, after the class declaration (after the end-class) and before the method definition.
%Super is only required to access superclass members that are hidden by overriding members in the current time.
Downcasting is the process of determining if an object is of a particular subclass type. If the object has that subtype (either the object is of that subtype, or is a subtype of it), a reference to the subject is returned, otherwise Null is returned. In either case, the type of the resulting value is compatible with the name subclass type.
Class Fruit
Property number fruitcount;
End-class;
Class Banana extends Fruit
Property number BananaCount;
End-Class;
Local Banana &MyBanana = Create Banana ();
Local Fruit &MyFruit = &MyBanana; /* Okay, Banana is a subtype of Fruit */
Local number &num = & MyBanana.BananaCount;
&MyBanana = &MyFruit as Banana; /* &MyFruit is currently a Banana at runtime */
&Num = (&MyFruit as Banana).BananaCount; /* same as &MyBanana.BananaCount */
&MyFruit = Create Fruit ();
&MyBanana = &MyFruit as Banana; /* Assigns Null - &Myfruit isn’t a Banana */
/* */ and /** */ comments are allowed. Comments enclosed in /** -- */ are potentially be used to generate API documentation.
Method header comments are uses some tags which helps in API documentation
Some of the tags are
· @Parameter N
· @exception name
· @return type
Class header comments contains tags
· @version X (value of version)
· @author name
Example
/**
* Class header comments
* @Version 1.0
* @author Ganesh
*/
Import PWWPack: ExampleBase
Class Example extends ExampleBase
Method Example (); /*Constructor*/
Method NumToStr (&Num As number) Returns string ();
Method AppendSlash ();
Property number SlashCount get; /*Get properties */
Property number ImportantDayofWeek get set; /*Get and set properties */
Property string Slashstring readonly; /* values can be assigned in constructor */
Property date ImporantDate;
Private
Method NextDayofWeek (&Dow As number) returns date;
Constant &Sunday = 1; /*Constants */
Instance string &Basestring; /* Instance variables are like static variable */
End-class;
Declare function getusername Peoplecode FUNCLIB_PP.STRING_FUNCTIONS FieldFormula;
/**
* Method header comments example
* @param Dow is a number parameter
* @exception it throws Invalid day exception if the number is negative.
* @return it is returns the date as of that week.
*/
Method NextDayofWeek (&Dow)
---------
---------
End-method;
/* get block */
Get SlashCount
Return (Slashcount);
End-get;
Importing packages or classes
Class Extensions represents the “is-a” relationship.
When a class extends another class, it’s called a subclass of that class.
No Multiple inheritances or Concept of interface in Application classes.
Instance variables in the class are accessible in all the objects of that class.
Application programs generally pass parameters by value, which is not the same as for existing functions.
Parameter passing with object data types is by reference.
When the objects are passed to method and it reassign the object with new object it will not reflected in the calling method.
Application Programs use the out specifier to pass a parameter by reference.
Method increment (&Value as number out); rem passed by reference.
Create is the key word used to create the class object.
Local MyObjectClass &o1 = Create MyobjectClass (‘A’);
Local MyObjectClass &o1 = Create Test: Examples: MyobjectClass (‘A’);
If parameter in a method is having out specifier then parameter should be a variable and cannot be constant.
A class that extends another class must have constructor, and in the constructor it must initialize its super class.
To initialize a superobject, an instance of the superclass is assigned to the keyword %Super in the constructor for the subclass.
Class Example extends ExampleBase
Method Example ();
End-class;
Method Example
%Super = create ExampleBase ();
&BaseStrin = ¤tBaseString;
&Slashstring = &BaseString;
End-Method;
Before the constructor runs, the instance variables for the class are set by default takes on their declared types.
An application class doesn’t have destructors, that is, a method called just before an object is destroyed. The People code runtime environment generally cleans up any held resources.
When application class properties and instance variables are used as the argument to functions or methods, they are always passed by value, never by reference.
%This is can be used to self reference. This is to refer the same object.
%Super = %This – this should never be done it will go to an infinite loop.
Import Fruit:* - imports all the classes in the application package.
Import statements are validated when you save the Peoplecode.
Peoplesoft recommends that you use read-write or ready only properties instead of creating methods name GetXxx and SetXxx.
Getter and Setter syntax
Get Propertyname
---
End-get;
External Functions Declarations are allowed in application classes, in the global and component variable declarations, after the class declaration (after the end-class) and before the method definition.
%Super is only required to access superclass members that are hidden by overriding members in the current time.
Downcasting is the process of determining if an object is of a particular subclass type. If the object has that subtype (either the object is of that subtype, or is a subtype of it), a reference to the subject is returned, otherwise Null is returned. In either case, the type of the resulting value is compatible with the name subclass type.
Class Fruit
Property number fruitcount;
End-class;
Class Banana extends Fruit
Property number BananaCount;
End-Class;
Local Banana &MyBanana = Create Banana ();
Local Fruit &MyFruit = &MyBanana; /* Okay, Banana is a subtype of Fruit */
Local number &num = & MyBanana.BananaCount;
&MyBanana = &MyFruit as Banana; /* &MyFruit is currently a Banana at runtime */
&Num = (&MyFruit as Banana).BananaCount; /* same as &MyBanana.BananaCount */
&MyFruit = Create Fruit ();
&MyBanana = &MyFruit as Banana; /* Assigns Null - &Myfruit isn’t a Banana */
/* */ and /** */ comments are allowed. Comments enclosed in /** -- */ are potentially be used to generate API documentation.
Method header comments are uses some tags which helps in API documentation
Some of the tags are
· @Parameter N
· @exception name
· @return type
Class header comments contains tags
· @version X (value of version)
· @author name
Example
/**
* Class header comments
* @Version 1.0
* @author Ganesh
*/
Import PWWPack: ExampleBase
Class Example extends ExampleBase
Method Example (); /*Constructor*/
Method NumToStr (&Num As number) Returns string ();
Method AppendSlash ();
Property number SlashCount get; /*Get properties */
Property number ImportantDayofWeek get set; /*Get and set properties */
Property string Slashstring readonly; /* values can be assigned in constructor */
Property date ImporantDate;
Private
Method NextDayofWeek (&Dow As number) returns date;
Constant &Sunday = 1; /*Constants */
Instance string &Basestring; /* Instance variables are like static variable */
End-class;
Declare function getusername Peoplecode FUNCLIB_PP.STRING_FUNCTIONS FieldFormula;
/**
* Method header comments example
* @param Dow is a number parameter
* @exception it throws Invalid day exception if the number is negative.
* @return it is returns the date as of that week.
*/
Method NextDayofWeek (&Dow)
---------
---------
End-method;
/* get block */
Get SlashCount
Return (Slashcount);
End-get;
Exception Handling in Peoplesoft
Exception handling is the processing you initiate when an exception occurs. You can handle errors in PeopleCode using the Catch statement. The places in your code where you want exceptions handled must be enclosed by the Try and End-Try statements.
The Exception class encapsulates exceptional runtime conditions. It can be used with most PeopleCode programs.
The process of error handling can be broken down as follows:
• An error occurs (either a hardware or software error).
• The error is detected and an exception is thrown (either by the system or by your program).
• Your exception handler provides the response.
You can create exceptions in 2 ways
1) Creating an Exception base class that encapsulates the built-in function call and handles its function parameters consistently. This is the preferred way.
2) Calling the built-in function CreateException.
Application code often continues some form of processing even in the event of an exception; you should not assume that the script all processing if an exception occurs.
The error message that PeopleTools displays might not be appropriate for end-users because of its technical information.
The Exception class does not work with any of the PeopleSoft APIs, that is, the classes whose objects are declared as type ApiObject. This includes the Tree classes, the Query classes, Search classes, and so on.
try
Protected StatementList
catch QualifiedID &Id
StatementList
End-try;
Example 1:-
Import ExceptionPackage:*;
try
/* Code to manipulate &MyArray here */
&MyArray = GetArrayData(&Param1, &Param2, &Param3);
catch ExceptionNull &Ex1
If &Ex1.MessageSetNumber = 2 and &Ex1.MessageNumber = 236 Then
/* this is the message set and message number for &MyArray being Null */
/* do data error handling */
End-if;
End-try;
Example 2:-
Local Exception &ex;
Function t1(&i As integer) Returns number
Local number &res = &i / 0;
End-Function;
Function t2 throw CreateException (2, 160, "'%1' doesn't support property or method '%2'", "SomeClass", "SomeMethod");
End-Function;
/* this will cause a divide by 0 leading to an exception. This code will never be caught since t1(2) will resume execution in the catch block below. It is here to show how an exception can be thrown directly bythe Peoplecode itself. */
try t2 ();
Local number &res = t1 (2);
Catch Exception &caught MessageBox (0, "", 0, 0, "Caught exception: " &caught.ToString()); end-try;
The Exception class encapsulates exceptional runtime conditions. It can be used with most PeopleCode programs.
The process of error handling can be broken down as follows:
• An error occurs (either a hardware or software error).
• The error is detected and an exception is thrown (either by the system or by your program).
• Your exception handler provides the response.
You can create exceptions in 2 ways
1) Creating an Exception base class that encapsulates the built-in function call and handles its function parameters consistently. This is the preferred way.
2) Calling the built-in function CreateException.
Application code often continues some form of processing even in the event of an exception; you should not assume that the script all processing if an exception occurs.
The error message that PeopleTools displays might not be appropriate for end-users because of its technical information.
The Exception class does not work with any of the PeopleSoft APIs, that is, the classes whose objects are declared as type ApiObject. This includes the Tree classes, the Query classes, Search classes, and so on.
try
Protected StatementList
catch QualifiedID &Id
StatementList
End-try;
Example 1:-
Import ExceptionPackage:*;
try
/* Code to manipulate &MyArray here */
&MyArray = GetArrayData(&Param1, &Param2, &Param3);
catch ExceptionNull &Ex1
If &Ex1.MessageSetNumber = 2 and &Ex1.MessageNumber = 236 Then
/* this is the message set and message number for &MyArray being Null */
/* do data error handling */
End-if;
End-try;
Example 2:-
Local Exception &ex;
Function t1(&i As integer) Returns number
Local number &res = &i / 0;
End-Function;
Function t2 throw CreateException (2, 160, "'%1' doesn't support property or method '%2'", "SomeClass", "SomeMethod");
End-Function;
/* this will cause a divide by 0 leading to an exception. This code will never be caught since t1(2) will resume execution in the catch block below. It is here to show how an exception can be thrown directly bythe Peoplecode itself. */
try t2 ();
Local number &res = t1 (2);
Catch Exception &caught MessageBox (0, "", 0, 0, "Caught exception: " &caught.ToString()); end-try;
Subscribe to:
Posts (Atom)