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;
Row Level Security in PeopleSoft HRMS
The Default department security is name DEPT_SECURITY and the SetID is the share, although you may choose to create one to operate with a different Setid, depending on your environment.
Root level on this tree represents your company or organization. Below that you are adding departments in a hierarchical manner until you have built on organizational char of your company’s department structure.
When you click the link on one of department levels to either insert a child or a sibling node, you will be allowed to either choose an existing department that has been created or to create a new department on the fly by clicking Add.
Tie date Permission lists to Your Trees
Define Business Rules - > Administer HR systems -> Use -> Maintain Row level Security
1) Select a Permission list – It needs to created in the maintain security menu.
2) Peoplesoft standard is to generally preface them with DP
3) In the Data security profile page, you need to add the Departments that this permission list will have access to.
4) You need to provide SetID for the Department Security Tree you are using and then enter the DeptID for the node you are choosing.
5) Department Security Tree is hierarchical, that by adding an access to a department, you are automatically granting access to all the departments below that node as well.
If you need to exclude the data from a particular ‘Sub-Department’ you will have need to add that department separately to this list, and choose ‘No Access’ as the Acess code instead of the default of ‘Read/Write’.
Change the security Basis for the System
The final step to implement Row level security in HR is to change all the search views controlling which data is accessed on various pages. Luckily you don’t have to change it manually change each one, as Peoplesoft has delivered a process that takes care of it all for you. Please note that you only run this Process when turning on Department security System-Wide, Or when removing it from the system.
Define Business Rules -> Define General options - > Process - > Change Row Security Basis.
You need to choose option for Department security, to turn Department security on, or the Option of ‘None’, to turn Department security off.
Root level on this tree represents your company or organization. Below that you are adding departments in a hierarchical manner until you have built on organizational char of your company’s department structure.
When you click the link on one of department levels to either insert a child or a sibling node, you will be allowed to either choose an existing department that has been created or to create a new department on the fly by clicking Add.
Tie date Permission lists to Your Trees
Define Business Rules - > Administer HR systems -> Use -> Maintain Row level Security
1) Select a Permission list – It needs to created in the maintain security menu.
2) Peoplesoft standard is to generally preface them with DP
3) In the Data security profile page, you need to add the Departments that this permission list will have access to.
4) You need to provide SetID for the Department Security Tree you are using and then enter the DeptID for the node you are choosing.
5) Department Security Tree is hierarchical, that by adding an access to a department, you are automatically granting access to all the departments below that node as well.
If you need to exclude the data from a particular ‘Sub-Department’ you will have need to add that department separately to this list, and choose ‘No Access’ as the Acess code instead of the default of ‘Read/Write’.
Change the security Basis for the System
The final step to implement Row level security in HR is to change all the search views controlling which data is accessed on various pages. Luckily you don’t have to change it manually change each one, as Peoplesoft has delivered a process that takes care of it all for you. Please note that you only run this Process when turning on Department security System-Wide, Or when removing it from the system.
Define Business Rules -> Define General options - > Process - > Change Row Security Basis.
You need to choose option for Department security, to turn Department security on, or the Option of ‘None’, to turn Department security off.
Common Query Access Issues
a) User cannot see a table they wish to query from in query manage
User does not have access to the table via one of their Access Group. Double-Click that they have the Proper Access group on one of their Permission lists an ad the table to an Access group if necessary.
b) A user cannot see a public query from search screen in either query manager or query viewer.
User does not have access to the table via one of their Access Group. Double-Click that they have the Proper Access group on one of their Permission lists an ad the table to an Access group if necessary.
b) A user cannot see a public query from search screen in either query manager or query viewer.
Once you have determined that the query does in fact exist, and I s indeed a public query, then the user probably does not have access to all the tables being used in the query. If a user does not have access to all the tables involved in a query, the search screen itself will filter out the query, so that it does not even appear in the list. Double-Check that they have the proper Access group on one of their permission lists and add the table to an Access group if necessary.
HTML Procedures in SQR
Creating HTML output from SQR
To use the HTML procedures listed below, your program must reference the HTML procedures.
Issue the following two commands at the start of your program:
#include html.inc
do html_on
The file html.inc is located in the SAMPLE (or SAMPLEW) directory. Use the command-line flag -I to indicate this location.
There are 6 types of HTML procedure that can be used to easily create HTML o/p files.
General Purpose Procedures
Heading Procedures
Highlighting Procedures
Hypertext Link Procedures
List Procedures
Table Procedures
Eg:-
1) Html_br
Produces the specified number of line breaks in a paragraph using the HTML "BR" tag.
This causes the paragraph to be continued onto the next line.
Syntax: html_br(number count, string attributes)
count = the number of HTML "BR" tags that are inserted.
Attributes = the HTML attributes that are incorporated inside the HTML "BR" tag.
Example: Producing a line break:
Print 'Here is some text' ()
Do html_br (3,'')
Print 'Here is some three lines down' ()
2) Inserts an image using the HTML "IMG" tag. This can also be done using the command PRINT-IMAGE;
However, the procedure html_img provides the ability to specify the full set of available HTML attributes.
Syntax: html_img (string attributes)
Attributes = the HTML attributes that are incorporated inside the HTML "IMG" tag.
Some common attributes:
Src = URL of the image to be inserted (Ex: src=/images/abc.gif)
Height = height of the image in pixels (Ex: height=200)
Width = width of the image in pixels (Ex: width=400)
Example: Producing an image:
Do html_img('src="/images/stop.gif"')
To use the HTML procedures listed below, your program must reference the HTML procedures.
Issue the following two commands at the start of your program:
#include html.inc
do html_on
The file html.inc is located in the SAMPLE (or SAMPLEW) directory. Use the command-line flag -I to indicate this location.
There are 6 types of HTML procedure that can be used to easily create HTML o/p files.
General Purpose Procedures
Heading Procedures
Highlighting Procedures
Hypertext Link Procedures
List Procedures
Table Procedures
Eg:-
1) Html_br
Produces the specified number of line breaks in a paragraph using the HTML "BR" tag.
This causes the paragraph to be continued onto the next line.
Syntax: html_br(number count, string attributes)
count = the number of HTML "BR" tags that are inserted.
Attributes = the HTML attributes that are incorporated inside the HTML "BR" tag.
Example: Producing a line break:
Print 'Here is some text' ()
Do html_br (3,'')
Print 'Here is some three lines down' ()
2) Inserts an image using the HTML "IMG" tag. This can also be done using the command PRINT-IMAGE;
However, the procedure html_img provides the ability to specify the full set of available HTML attributes.
Syntax: html_img (string attributes)
Attributes = the HTML attributes that are incorporated inside the HTML "IMG" tag.
Some common attributes:
Src = URL of the image to be inserted (Ex: src=/images/abc.gif)
Height = height of the image in pixels (Ex: height=200)
Width = width of the image in pixels (Ex: width=400)
Example: Producing an image:
Do html_img('src="/images/stop.gif"')
PS Query Permissions/Security in Peoplesoft
In Peoplesoft we cannot give access to any query directly like we provide it for components. user should have access to all the records in the query to access a particulary or use that record to create a query. Permission to the records needs to be provided first.
Private queries are visible to only those users who has created that query. query needs to be created as public for other users to access (given that they have access to all the records used in the query). security/permission to records are provide through query security tree and access groups.
In Query Tab of the Permission list set of query permission are present.
1) Access Group permission
2) Query Profile
Access group permission: This contains the list of the Access groups (nodes) in a particular query tree (it contain records that can be accessed) that a permission list has access.
Provide tree name and query access group here to get access to all records under query access group in this tree.
Tree Name and the Access group name needs to be entered.
Query Profile:
1)PeopleSoft Query
2)PeopleSoft Query Output
3) Advanced SQL Options
properties under each of these sections are set.
This defines what user can basically do with PS query.
PeopleTools > Security > Permission > Roles Permission Lists
Managing the query security tree
Query trees are accessible in Query Access group manager and not under the Tree manager.
PeopleTools > Security > Query Security > Query Access Group Manager.
Different trees for different category. Eg:- HR, BEFEFITS_ADMIN (contains BEFEFIT related records).
Each Leaf in the Tree corresponds to one record in this query tree.
Record is not Visible in PS Query ?
Add this record in the tree under the access group to which your primary permission list has access. (Query access group component)
OR
Add the access to your Tree and access group in which this record is present.
Private queries are visible to only those users who has created that query. query needs to be created as public for other users to access (given that they have access to all the records used in the query). security/permission to records are provide through query security tree and access groups.
In Query Tab of the Permission list set of query permission are present.
1) Access Group permission
2) Query Profile
Access group permission: This contains the list of the Access groups (nodes) in a particular query tree (it contain records that can be accessed) that a permission list has access.
Provide tree name and query access group here to get access to all records under query access group in this tree.
Tree Name and the Access group name needs to be entered.
Query Profile:
1)PeopleSoft Query
2)PeopleSoft Query Output
3) Advanced SQL Options
properties under each of these sections are set.
This defines what user can basically do with PS query.
PeopleTools > Security > Permission > Roles Permission Lists
Managing the query security tree
Query trees are accessible in Query Access group manager and not under the Tree manager.
PeopleTools > Security > Query Security > Query Access Group Manager.
Different trees for different category. Eg:- HR, BEFEFITS_ADMIN (contains BEFEFIT related records).
Each Leaf in the Tree corresponds to one record in this query tree.
Record is not Visible in PS Query ?
Add this record in the tree under the access group to which your primary permission list has access. (Query access group component)
OR
Add the access to your Tree and access group in which this record is present.
PeopleSoft - Change Access ID Password
To Change the Access ID password, possible methods are listed below:
There are ONLY 3 ways to do this. These options MUST be done in 2 tier and your application servers, Process Schedulers, and Web Servers MUST BE DOWN and ALL users need to be logged off if possible.
METHOD # 1 - (Preferred Method) Log into Data Mover as the Access ID (Bootstrap mode).
Then run this command:CHANGE_ACCESS_PASSWORD sa1 (This is your symbolic ID) cloud123 (This is the new password for the Access ID);And hit the Green traffic light.NOTE - This method does NOT work in PT 8.14 or 8.15, but is now fixed in PT 8.16 (T-JPELAY-AU4QW) so use method 2 or method 3. You MUST also make sure that the password for the Access ID has successfully been changed at the Database level because prior to PT 8.17 this method would not change the database level password. To do this go through your SQL tool (SQL Plus, Query Analyzer) and make the change in the database users table.
METHOD # 2 - (Second best method) You need to change the Access ID Password at database level then log into Data Mover in bootstrap mode and run:update PSACCESSPRFL set ACCESSID = 'what your Access ID is' , ACCESSPSWD = 'your New Access Password' , ENCRYPTED = 0, where SYMBOLICID = 'whatever the SYMBOLICID is';Then runENCRYPT_PASSWORD *;Note: If you have more than 45,000 rows in the PSOPRDEFN table then you may not be able to run the ENCRYPT_PASSWORD *; command in Data Mover. In that case just logon as a valid user in 2 tier and that will encrypt the Access ID in the PSACCESSPRFL table.This will synch up the PSACCESSPRFL table with the same password stored at the database level for your Access ID.
METHOD # 3 - (Last and most discouraged method) Go into Application Designer / Tools / Miscellaneous Objects / Access Profiles.Highlight the Symbolic ID and push the Edit button, enter the old password, new password, and confirm new password.If you change it in Application Designer it does not change at the database level. This is because we do not GRANT to the database in PT 8. You MUST also make sure that the password for the Access ID has successfully been changed at the DB level. To do this go through your SQL tool (SQL Plus, Query Analyzer) and make the change in the database users table.
Tuxedo WSL JSL Ports
Ports allocation for Application Server Domain:
Ports required for WSL and WSHs:
The number of ports required are 1 + [Max Handlers] where [Max Handlers] is the maximum number of workstation handlers configured. The algorithm used to start a WSH is a random port available after the port for WSL. On higher releases, the system automatically sets the range, starting from [WSL_port] + 1. Any port between 1025 and 65536 (both inclusive) is valid for WSL.
Ports required for JSL and JSHs:
The number of ports required are 1 + [Max Handlers] where [Max Handlers] is the maximum number of jolt handlers configured. The algorithm used to start a jolt handler is the first port available after the port for JSL. There's no need to force the range, it's automatic. Any port between 0 and 65535 is valid for JSL.
Ports required for WSL and WSHs:
The number of ports required are 1 + [Max Handlers] where [Max Handlers] is the maximum number of workstation handlers configured. The algorithm used to start a WSH is a random port available after the port for WSL. On higher releases, the system automatically sets the range, starting from [WSL_port] + 1. Any port between 1025 and 65536 (both inclusive) is valid for WSL.
Ports required for JSL and JSHs:
The number of ports required are 1 + [Max Handlers] where [Max Handlers] is the maximum number of jolt handlers configured. The algorithm used to start a jolt handler is the first port available after the port for JSL. There's no need to force the range, it's automatic. Any port between 0 and 65535 is valid for JSL.
Grant access for a User to perform object migration
How to grant access for a User to perform object migration..?
To create user who can perform object migration from one environment to another environment, without having write access to the Application Designer objects like record, field, Application Engine, PepleCode etc. Solution:
Login to PIA:
Go to Home > People Tools > Maintain Security > Use > Permission Lists open the permission list the user has. Then go to People Tools tab, tick Application Designer Access. You may set different objects permissions to read only if you click the links for Definition Permissions, Tools Permissions, and Miscellaneous Permissions. You should have "full access" to project.
You can set Build script only or more in Build / Data Admin of Tools permission. Once the user has this permission list, the user should be able to migrate the project and build the project only.
PeopleTools ERP Connectors Y/N
The ERP Connectors are used with the Integration Broker product. If you do not plan to utilize the Integration Broker portion of PeopleTools, reply NO to the prompt. If you do plan to use Integration Broker, reply YES to the prompt. If you reply NO to the prompt but then later decide to use Integration Broker you will have to go back and reinstall PeopleTools. The YES will cause files to be unloaded to your PsHome for use with the Integration Broker product.
Compare Report - Keep Vanilla or Keep Customization
Compare ReportWhy Keep Vanilla or Keep Customization ? What is the difference?
The following chart summarizes the various status, action, and upgrade possibilities that could be applied to a single definition during the upgrade compare process:
UPGRADE FLAG
See Above Image
For example if the Source is *Changed and the Target is Changed, the ACTION will be COPY, but the Upgrade flag will only be YES (do the copy) if you have Keep Customizations selected. It will be NO if you choose PeopleSoft Vanilla and will not be copied.If you are seeing the Source and Target both as *Changed, it should copy in either case.Now read the example above carefully. It means that, if the source is *Changed (customer modified) and the target is Changed (PeopleSoft modified), then in this case, If you select "Keep Customization" then the source object will get copied to target. But if you select "Keep Vanilla" then the customer's customized object in source will not get copied to target.
PeopleSoft - Backup/Rollback steps
The "standard" rollback plan has plenty of issues. Simply restoring the file backup will not revert the system to pre-migration state.
These are the steps to do a proper backup/rollback, it's not detailed but it should give you the general idea on what's involved and how to do it correctly.
Backup Steps:
1. Copy Project Definition Only to Target DB (e.g. Prod)
2. Open Project Definition in Target DB, and set Action for Project = COPY
3. Copy the Project Definition to a Project Backup file.
4. Run Data Mover or Oracle export to backup all the data in all the tables including any Message Catalog Entries and User Roles in the project.
5. At this point there should be 2 files -- the Project Backup and the Data Backup
Rollback Steps:
1. Open the project definition from the Source DB (e.g. QA)
2. Set Action for Project = DELETE (This will ensure all objects that moved are deleted)
3. Move the Project from the Source DB to the Target DB to perform the delete
4. Copy the Project Backup from file to the Target DB to restore the objects.
5. Alter Tables/Rebuild Views.
6. Run Data Mover or Oracle import to restore all Data and any Message Catalog Entries and User Roles from the Data Backup.
These are the steps to do a proper backup/rollback, it's not detailed but it should give you the general idea on what's involved and how to do it correctly.
Backup Steps:
1. Copy Project Definition Only to Target DB (e.g. Prod)
2. Open Project Definition in Target DB, and set Action for Project = COPY
3. Copy the Project Definition to a Project Backup file.
4. Run Data Mover or Oracle export to backup all the data in all the tables including any Message Catalog Entries and User Roles in the project.
5. At this point there should be 2 files -- the Project Backup and the Data Backup
Rollback Steps:
1. Open the project definition from the Source DB (e.g. QA)
2. Set Action for Project = DELETE (This will ensure all objects that moved are deleted)
3. Move the Project from the Source DB to the Target DB to perform the delete
4. Copy the Project Backup from file to the Target DB to restore the objects.
5. Alter Tables/Rebuild Views.
6. Run Data Mover or Oracle import to restore all Data and any Message Catalog Entries and User Roles from the Data Backup.
Application Server Trace Options/Settings TraceSQL TraceSQLMask
TraceOptions
This section enables you to specify the tracing options that you can enable on the application server to track the SQL and PeopleCode of the domains. You can also set all of the trace parameters from the PeopleSoft sign-in page.
TraceSQL
Enter the logging level for SQL tracing for all clients. Traces are written to/appserv//LOGS/_.tracesql. Enter 0 to disable tracing; enter 7 to enable a modest tracing level for debugging. For other levels of tracing, set this option to a value that equals the sum of the needed options. For example, to trace only SQL, enter 1; to trace SQL statements and connect statements enter 7 (1+ 2 + 4 = 7). Tracing can consume large amounts of disk space over time, so be sure to reset this option to 0 when you finish troubleshooting.
TraceSQLMask
Enter the logging level ceiling for SQL tracing for individual clients. Traces are written to/appserv//LOGS/_.tracesql. Clients must specify the necessary SQL tracing level by using the PeopleSoft Configuration Manager on the Trace tab. To prevent clients from turning on the application server trace and consuming resources, the application server uses TraceSQLMask as an administrative control facility.
If a client transmits a request to trace SQL, the application server compares the value that is transmitted to the TraceSQLMask value. If the client value is less than or equal to the TraceSQLMask value, the application server enables the trace. However, if the client value is greater, the application server enables the trace up to the TraceSQLMask value. Trace files are written on the application server; no trace shows up on the client workstation. Trace values are set in the PSAPPSRV.CFG file. Output files are written to $PS_HOME/appserver/winx86//logs.
TracePC
Enter a level for PeopleCode tracing for activity that is generated by all clients on a domain. Eligible values are defined in the configuration file. TracePC values are displayed in the PeopleSoft Configuration Manager on the Trace tab. You can find the results in/appserv//LOGS/.log.
TracePCMask
Enter which PeopleCode trace options that are requested by client machines will be written to the trace file. You can find the results in/appserv//LOGS/..log.
This section enables you to specify the tracing options that you can enable on the application server to track the SQL and PeopleCode of the domains. You can also set all of the trace parameters from the PeopleSoft sign-in page.
TraceSQL
Enter the logging level for SQL tracing for all clients. Traces are written to
TraceSQLMask
Enter the logging level ceiling for SQL tracing for individual clients. Traces are written to
If a client transmits a request to trace SQL, the application server compares the value that is transmitted to the TraceSQLMask value. If the client value is less than or equal to the TraceSQLMask value, the application server enables the trace. However, if the client value is greater, the application server enables the trace up to the TraceSQLMask value. Trace files are written on the application server; no trace shows up on the client workstation. Trace values are set in the PSAPPSRV.CFG file. Output files are written to $PS_HOME/appserver/winx86/
TracePC
Enter a level for PeopleCode tracing for activity that is generated by all clients on a domain. Eligible values are defined in the configuration file. TracePC values are displayed in the PeopleSoft Configuration Manager on the Trace tab. You can find the results in
TracePCMask
Enter which PeopleCode trace options that are requested by client machines will be written to the trace file. You can find the results in
Tuxedo RCCBL Parameter...
Values for config section - RemoteCall
RCCBL Redirect=0
Enter 0 to disable redirection and enter 1 to enable redirection.
Redirection causes the server process to retain intermediate work files used to pass parameter values between the server process and a RemoteCall/COBOL program for debugging purposes.
Note: Redirect should always be 0 except for debugging. Work files are written to the /LOGS directory with ".in" and '.out" extensions.Source: PeopleBooks
RCCBL Redirect=0
Enter 0 to disable redirection and enter 1 to enable redirection.
Redirection causes the server process to retain intermediate work files used to pass parameter values between the server process and a RemoteCall/COBOL program for debugging purposes.
Note: Redirect should always be 0 except for debugging. Work files are written to the /LOGS directory with ".in" and '.out" extensions.Source: PeopleBooks
What are fixes,Patches, Bundles etc in peoplesoft...?
PeopleSoft Application and PeopleTools maintenance releases are intended to address defects,performance enhancements, regulatory changes (such as tax updates), or security issues. In general, feature enhancements are part of new releases which require a product upgrade.Application fixes and changes are delivered in a variety of means; these include individual postings (Patches), Bundles, Maintenance Packs and Service Packs.
Fixes
Fixes for specific issues with an application may be posted as a standalone fix intended to address that specific issue; these are generally referred to as Patches.
Patches
Patches almost always have pre-requisites that are included in the documentation for that Patch. Patches are created by development using the most recently released bundle, this means they may list that bundle as a prerequisite. There may be other pre-requisites that are also required, all pre-requisites are clearly documented in the notes for the Patch.
Bundles
Bundles are periodic accumulations of fixes resolved in that time period and applied to the application as a group. For most applications, the interval has been about every 6 weeks or 12 weeks for posting these Bundles. When Bundles are applied using Change Assistant, information about the Bundle is recorded in the Maintenance Log table for future reference. Bundles will typically require pre-requisites and occassionally post-requisites. These are documented in the notes for the Bundle. Information about the individual Report ID s associated with a Bundle is available in the supplemental information posted on Customer Connection related to that Bundle.
Maintenance Packs
Maintenance packs are less frequent updates that aggregate Bundles to minimize the number of Bundles that need to be applied. For most applications, maintenance packs are delivered once a quarter for the two most current releases.
Service Packs
Service Packs serve as a vehicle for an even larger aggregation of fixes than Maintenance Packs.In all cases, the PeopleSoft tool Change Assistant is a valuable tool in identifying dependencies of Patches, Bundles, and Maintenance Packs.
How to Determine if the application has Multi-Language
Log onto the database and run the following select .
select Language_CD, Installed from PSLANGUAGES;
If the 'Installed' field returns a '1' in for the Language_CD, then that language is installed in the application.
Editing PeopleSoft Login Page
To make changes in the login page one has to change the signin.html page but all the text used by the .html is inside the text.properties
For miltilanguage instance it would be textXXX.properties
Example: To change the value of the login button from 'sign in' to 'developement' or something else one has to make changes in text.properties, signin.html & other few .html files refers text.properties for these values.
For miltilanguage instance it would be textXXX.properties
Example: To change the value of the login button from 'sign in' to 'developement' or something else one has to make changes in text.properties, signin.html & other few .html files refers text.properties for these values.
Unix Script to Check Oracle Instance Availability
Check Oracle Instance Availability
The oratab file lists all the databases on a server:
$ cat /etc/oratab
##############
##/etc/oratab
##############
oradb1:/u01/app/oracle/product/8.1.7:Y
oradb2:/u01/app/oracle/product/8.1.7:Y
oradb3:/u01/app/oracle/product/8.1.7:N
oradb4:/u01/app/oracle/product/8.1.7:Y
The following script checks all the databases listed in the oratab file, and finds out the status (up or down) of databases:
##################
## ckinstance.ksh ##
##################
ORATAB=/etc/oratab
echo "`date` "
echo "Oracle Database(s) Status `hostname` :\n"
db=`egrep -i ":Y:N" $ORATAB cut -d":" -f1 grep -v "\#" grep -v "\*"`
pslist="`ps -ef grep pmon`"
for i in $db ; do
echo "$pslist" grep "ora_pmon_$i" > /dev/null 2>$1
if (( $? )); then
echo "Oracle Instance - $i: Down"
else
echo "Oracle Instance - $i: Up"
fi
done
Use the following to make sure the script is executable:
$ chmod 744 ckinstance.ksh
$ ls -l ckinstance.ksh
-rwxr--r-- 1 oracle dba 657 Dec 5 22:59 ckinstance.ksh*
Here is an instance availability report:
$ ckinstance.ksh
Mon Dec 31 11:44:12 IST 2007
Oracle Database(s) Status for DBHOST server:
Oracle Instance - oradb1: Up
Oracle Instance - oradb2: Up
Oracle Instance - oradb3: Down
Oracle Instance - oradb4: Up
The oratab file lists all the databases on a server:
$ cat /etc/oratab
##############
##/etc/oratab
##############
oradb1:/u01/app/oracle/product/8.1.7:Y
oradb2:/u01/app/oracle/product/8.1.7:Y
oradb3:/u01/app/oracle/product/8.1.7:N
oradb4:/u01/app/oracle/product/8.1.7:Y
The following script checks all the databases listed in the oratab file, and finds out the status (up or down) of databases:
##################
## ckinstance.ksh ##
##################
ORATAB=/etc/oratab
echo "`date` "
echo "Oracle Database(s) Status `hostname` :\n"
db=`egrep -i ":Y:N" $ORATAB cut -d":" -f1 grep -v "\#" grep -v "\*"`
pslist="`ps -ef grep pmon`"
for i in $db ; do
echo "$pslist" grep "ora_pmon_$i" > /dev/null 2>$1
if (( $? )); then
echo "Oracle Instance - $i: Down"
else
echo "Oracle Instance - $i: Up"
fi
done
Use the following to make sure the script is executable:
$ chmod 744 ckinstance.ksh
$ ls -l ckinstance.ksh
-rwxr--r-- 1 oracle dba 657 Dec 5 22:59 ckinstance.ksh*
Here is an instance availability report:
$ ckinstance.ksh
Mon Dec 31 11:44:12 IST 2007
Oracle Database(s) Status for DBHOST server:
Oracle Instance - oradb1: Up
Oracle Instance - oradb2: Up
Oracle Instance - oradb3: Down
Oracle Instance - oradb4: Up
Peoplesoft - Sign on Process
Understanding Peoplesoft Sign on:
Basic step in Peoplesoft Sign on are:
1. Initial Connection: The application server starts, and uses the Connect ID and User ID specified in its configuration file (PSAPPSRV.CFG) to perform the initial connection to the database.
2. SELECT on security tables: After the Connect ID is verified, the application server performs a SELECT on various PeopleTools security tables, such as PSOPRDEFN, PSACCESSPRFL, and PSSTATUS. From these tables the application server gathers info such as User ID and password, symbolic ID, access ID, and access password. After the application server has the required information, it logs off from this initial connection.
3. Re-Connects with access ID: When the system verifies that the access ID is valid, the application server begins the persistent connection to the database that all PIA and windows three-tier clients use to access the database.
Basic step in Peoplesoft Sign on are:
1. Initial Connection: The application server starts, and uses the Connect ID and User ID specified in its configuration file (PSAPPSRV.CFG) to perform the initial connection to the database.
2. SELECT on security tables: After the Connect ID is verified, the application server performs a SELECT on various PeopleTools security tables, such as PSOPRDEFN, PSACCESSPRFL, and PSSTATUS. From these tables the application server gathers info such as User ID and password, symbolic ID, access ID, and access password. After the application server has the required information, it logs off from this initial connection.
3. Re-Connects with access ID: When the system verifies that the access ID is valid, the application server begins the persistent connection to the database that all PIA and windows three-tier clients use to access the database.
Oracle - Speed up IMPORT Process
Problem: Slow import
Scenario: Truncate table and import repeatedly
Solution:Repeated Truncate and import may slow down the process.
The reason is while importing if we don't mention constraints=N, then everytime we import duplicate constraints are created.
For example we assume that table ONE has two constraints, If we truncate table,the constraints are still there. For first time import will be fast.During second time, after truncating,and during import(if we doesn't mention CONSTRAINT=N), again two duplicate constraints are created and slows down the import operation little bit.Each time the duplicate constraints are created it slows down the import process.
Point to be remembered:If we truncate and import table, mention CONSTRAINTS=N explicitly.
Other considerations:When importing large amounts of data consider dropping indexes prior to the import to speed up the process and re-creating them once the import is completed
Tuxedo Web Administration…
To run the BEA Tuxedo Administration Console, Ensure that your TUXDIR, WEBJAVADIR, and PATH environment variables are set correctly in the Server where the Tuxedo application server is installed.
Then set up the following two server processes:
tuxwsvr(1)
A Web server provided with the BEA Tuxedo system software. You are not required to use this server; you may, if you prefer, use your own commercial Web server.
To Start Tuxwsvr:
tuxwsvr -l //machine:port -i $TUXDIR/udataobj/tuxwsvr.ini (onUNIX)
wlisten(1)
A server required to administer the BEA Tuxedo Administration Console. It must be run on the MASTER machine in a Tuxedo multi-machine configuration.
To Start wlistenon:
1. Verify that the wlisten process is running. On a UNIX machine, for example, enter the ps command.
2. If wlisten is not running, open the webgui.ini file and, in the line NADDR=//foo5:4003, replace the port number (4003) with a valid port number.
3. Enter the following command:$ wlisten -i $TUXDIR/udataobj/webgui/webgui.ini (on UNIX)
4. Check that the tuxwsvr process is running at the port specified in the URL.
5. Verify the password. It must match one of the entries in the tlisten.pw fileAfter starting the tuxwsvr and wlisten server processes, you can start the BEA Tuxedo Administration Console to monitor the tuxwsvr server and the BEA Tuxedo application by replacing the machine & port name appropriately in the below link...
http://machine:port/webguitop.html
Before invoking the tuxedo web admin, verify the TUXEDO password in tlisten.pw file.
Reference:
http://e-docs.bea.com/tuxedo/tux91/install/insadm.htm
http://e-docs.bea.com/wle/tuxedo/admingd/tools.htm
Then set up the following two server processes:
tuxwsvr(1)
A Web server provided with the BEA Tuxedo system software. You are not required to use this server; you may, if you prefer, use your own commercial Web server.
To Start Tuxwsvr:
tuxwsvr -l //machine:port -i $TUXDIR/udataobj/tuxwsvr.ini (onUNIX)
wlisten(1)
A server required to administer the BEA Tuxedo Administration Console. It must be run on the MASTER machine in a Tuxedo multi-machine configuration.
To Start wlistenon:
1. Verify that the wlisten process is running. On a UNIX machine, for example, enter the ps command.
2. If wlisten is not running, open the webgui.ini file and, in the line NADDR=//foo5:4003, replace the port number (4003) with a valid port number.
3. Enter the following command:$ wlisten -i $TUXDIR/udataobj/webgui/webgui.ini (on UNIX)
4. Check that the tuxwsvr process is running at the port specified in the URL.
5. Verify the password. It must match one of the entries in the tlisten.pw fileAfter starting the tuxwsvr and wlisten server processes, you can start the BEA Tuxedo Administration Console to monitor the tuxwsvr server and the BEA Tuxedo application by replacing the machine & port name appropriately in the below link...
http://machine:port/webguitop.html
Before invoking the tuxedo web admin, verify the TUXEDO password in tlisten.pw file.
Reference:
http://e-docs.bea.com/tuxedo/tux91/install/insadm.htm
http://e-docs.bea.com/wle/tuxedo/admingd/tools.htm
PSAPPSRV - How to connect to the Oracle database
Configuration file
UseLocalOracleDB=0
The configuration file for the application server (psappsrv.cfg) has a section Database Options, and in that section there is a parameter UseLocalOracleDb.
Possible settings are zero (off / default value) and one (on). This parameter is used to determine how to connect to the Oracle database.
A value of zero for this parameter will cause the domain to read the tnsnames.ora at 'connect time'. Feedback from customers tells us that the domains will start slower, help get around problems with shutting down the application server, and result in less remote call errors, with this setting.
A value of one is appropriate only if the database and application server are built on the same machine. This is sometimes used if the tnsnames.ora is not yet set. It will cause the domain to boot up faster.
UseLocalOracleDB=0
The configuration file for the application server (psappsrv.cfg) has a section Database Options, and in that section there is a parameter UseLocalOracleDb.
Possible settings are zero (off / default value) and one (on). This parameter is used to determine how to connect to the Oracle database.
A value of zero for this parameter will cause the domain to read the tnsnames.ora at 'connect time'. Feedback from customers tells us that the domains will start slower, help get around problems with shutting down the application server, and result in less remote call errors, with this setting.
A value of one is appropriate only if the database and application server are built on the same machine. This is sometimes used if the tnsnames.ora is not yet set. It will cause the domain to boot up faster.
Report Distribution - Distribution Agent
Report Distribution: PSDSTSRV
Report List table (PS_CDM_LIST)
When the server agent initiates a process request that has an output destination type of Web, or if the Server Definition page is set up to transfer log/trace files to Report Manager, then an entry is inserted into the Report List table (PS_CDM_LIST). Once the program associated with the process finishes, the status in the Report List table is updated to Generated indicating that the files are ready to transfer to Report Repository. The distribution Agent polls the Report List table to determine which process requests have finished running and then transfers them to Report Repository.
See Image at the top.Prior to transferring files to the Report Repository, the Distribution Agent will perform the following steps· Create the INDEX.HTML file. The Distribution Agent inventories all the files generated by the program associated with the process request. It will create an index.html file cataloging all the report, log, and trace files. This HTML file is used by the Report Repository to link these files, so the file can be selected either from Report Manager or the Process Monitor Detail page.The Distribution Agent uses an HTML report template file to create the index.html. The HTML template file contains markers used to indicate header and detail information. You can make changes to this file provided the markers in the template file are left intact.· Transfer files to the Report Repository. Once the index.html file is created for the process request, the Distribution Agent transfers this HTML file with all the report and log files to the Report Repository. For each process request that it transfers, a directory is created in the Report Repository using an RBRAN (Really Big Random Alphanumeric Name) schema. All the files for a process request will be stored in this directory.· Delete the directory from the Process Scheduler Server Agent's Log/Output directory. When the output destination type specified for a process request is Web, all the files and the directory associated with the process request are deleted from the Process Scheduler Log/Output directory after the files are transferred to the Report Repository.
PeopleSoft Alter Audit
DDDAUDIT is an SQR script that compares your production SQL data tables with the PeopleSoft PeopleTools record definitions to identify inconsistencies.
SYSAUDIT is an SQR script used to identify “orphaned” PeopleSoft objects. For example, SYSAUDIT can identify a module of PeopleCode that exists but does not relate to any other objects in the system. SYSAUDIT also identifies other inconsistencies within your database.
To verify that the PeopleSoft PeopleTools definitions are synchronized with the underlying SQL data tables in your database, run the PeopleSoft PeopleTools alter record process on all records in your system. This process, called an Alter Audit, compares the data structures of your database tables with the PeopleSoft PeopleTools definitions to identify inconsistencies. The Alter Audit then creates SQL scripts with the data definition language (DDL) changes that are required to synchronize your database with the PeopleSoft PeopleTools definitions.
To run Alter Audit:
1. Launch PeopleSoft PeopleTools and sign in to the Target database.
2. From Application Designer, select File, New…
3. Select Project, and then click OK.
4. Select Insert, Definitions into Project...
5. Select Records from the Definition Type drop-down list box.
6. Select Table from the Type drop-down list box.
7. Click Insert, and then click Select All.
8. Click Insert, and then click Close.
9. Select File, Save All.
10. Enter a project name of your choice.
11. Click OK.
12. Select Build, Project..
Note. Triggers are always dropped and re-created during the alter process and will always show up in the generated Alter Audit script. You can ignore the generated script for triggers.
PeopleSoft Data Mover Modes
PeopleSoft Data Mover runs in two-tier mode only.
You must sign in to the database directly, not through an application server.
Regular mode
Most of the time, you use regular mode. To sign in to regular mode, enter your PeopleSoft user ID and password during sign-in. In regular mode, all commands are valid.
Bootstrap mode
In bootstrap mode, you use a database access ID and password when signing in. Typically, you use bootstrap mode for database loading, because no PeopleSoft security tables are established yet. You also use bootstrap mode for running some security commands, such as ENCRYPT_PASSWORD.
You must sign in to the database directly, not through an application server.
Regular mode
Most of the time, you use regular mode. To sign in to regular mode, enter your PeopleSoft user ID and password during sign-in. In regular mode, all commands are valid.
Bootstrap mode
In bootstrap mode, you use a database access ID and password when signing in. Typically, you use bootstrap mode for database loading, because no PeopleSoft security tables are established yet. You also use bootstrap mode for running some security commands, such as ENCRYPT_PASSWORD.
TUXEDO Connect String - Dynamic load balancing
TUXEDO Connect String
The TUXEDO Connect String is designed for advanced configuration to support dynamic load balancing. You can specify a free-form connect string that allows a client to connect to another application server in case another is either down or being used to full capacity. The following sections provide the description of and syntax for the connect string options.
Round Robin Load Balance
This option specifies multiple application servers to which the client will arbitrarily connect. The odds being that each application server will receive an equal number of connections. To specify the round robin, use the following syntax (where ip = IP Address and port = port number):(//ip1:port1//ip2:port2//ipn:portn)
You can specify the IP Address using either dotted notation or by using the server's DNS name. Regardless of which convention you use to enter the address, the slashes (//) preceding the IP Address are required.If the application server selected is unavailable, your connection attempt will fail and the system will not try to connect you to the other application servers defined within the parentheses.Spaces must not be embedded in any part of the connection string. PeopleSoft will automatically remove embedded spaces before storing the value in the registry.
Round Robin with Failover
Similar to Round Robin Load Balance, this option allows you to define a failover connection string. To specify this option, use the following syntax (where ip = IP Address and port = port number):
(//ip1:port1//ip2:port2),(//ip3:port3)
If the application server selected from the first group of parentheses (ip1 and ip2) is unavailable, the system will automatically attempt to connect to an application server defined in the second group (ip3). If that application server fails, the system will attempt to connect to the next group to the right, sequentially.If multiple application servers are defined within any group, the system will round-robin between them. If the selected application server fails, the system will attempt to connect to the next application server to the right, if any. The following are three separate examples, showing a range usage:
(//sp-ibm01:8000//sp-ibm02:8000),(//sp-nt01:8000)(//208.136.78.88:8000//208.136.78.88:8050//208.136.78.88:8080)
(//sp-sun01:8000),(//sp-sun02:8000),(//sp-sun03:8000)
Max No of accepted char is 1000…
The TUXEDO Connect String is designed for advanced configuration to support dynamic load balancing. You can specify a free-form connect string that allows a client to connect to another application server in case another is either down or being used to full capacity. The following sections provide the description of and syntax for the connect string options.
Round Robin Load Balance
This option specifies multiple application servers to which the client will arbitrarily connect. The odds being that each application server will receive an equal number of connections. To specify the round robin, use the following syntax (where ip = IP Address and port = port number):(//ip1:port1//ip2:port2//ipn:portn)
You can specify the IP Address using either dotted notation or by using the server's DNS name. Regardless of which convention you use to enter the address, the slashes (//) preceding the IP Address are required.If the application server selected is unavailable, your connection attempt will fail and the system will not try to connect you to the other application servers defined within the parentheses.Spaces must not be embedded in any part of the connection string. PeopleSoft will automatically remove embedded spaces before storing the value in the registry.
Round Robin with Failover
Similar to Round Robin Load Balance, this option allows you to define a failover connection string. To specify this option, use the following syntax (where ip = IP Address and port = port number):
(//ip1:port1//ip2:port2),(//ip3:port3)
If the application server selected from the first group of parentheses (ip1 and ip2) is unavailable, the system will automatically attempt to connect to an application server defined in the second group (ip3). If that application server fails, the system will attempt to connect to the next group to the right, sequentially.If multiple application servers are defined within any group, the system will round-robin between them. If the selected application server fails, the system will attempt to connect to the next application server to the right, if any. The following are three separate examples, showing a range usage:
(//sp-ibm01:8000//sp-ibm02:8000),(//sp-nt01:8000)(//208.136.78.88:8000//208.136.78.88:8050//208.136.78.88:8080)
(//sp-sun01:8000),(//sp-sun02:8000),(//sp-sun03:8000)
Max No of accepted char is 1000…
Compare and Report Tips and Techniques
You may have customizations in your target database that you don’t want overwritten when copying a project.
Application Designer lets you compare the contents of your project with the target database and shows you both online and in generated reports the status of each object in the target and the source database. You can then decide which object definitions to keep.
Note. Comparisons are not always necessary. For example, if you’ve created a project in your development database that consists only of new object definitions, you can be sure that those objects don’t exist in your production database. In this case, you can simply review your upgrade settings and copy the project without fear of overwriting any object definitions in the target.
Topics
Comparable Object Types
Comparison Types
Comparison Reports
Performing Comparisons
Comparable Object Types — The object types that Application Designer can compare are:
Records
Indexes
Fields
Translates
Panels
Panel Groups
Menus
Record PeopleCode
Menu PeopleCode
Colors
Styles
Comparison Types — Two ways to perform a source/target comparison.
One way is to compare all the database objects of the type specified and populate the current project with any objects defined differently in the source than in the target.
The other way is to compare only the objects in the current project.
Topics
Database Comparisons
Project Comparisons
Comparing Records
Database Comparisons
Earlier, we discussed the different methods of selecting objects to insert into a project. One of these methods was selecting objects by comparison. You do this by performing a database comparison, in which the current (source) database is compared with another database.
Any objects defined differently in the two databases are added to the project.
Application Designer performs comparisons one object type at a time. You decide which types of objects you want compared. For each object type you select, Application Designer will remove any existing objects of that type from the current project and repopulate the project based on the comparison results.
For this reason, you should be careful when performing a database comparison on a predefined project.
For example, say your project includes several record, panel, and menu definitions and you perform a database comparison just on panels. All the panel definitions that were originally in the project will be removed and be replaced by any panel definitions found in the compare process. However, the record and menu definitions in your project will not be affected.
Performing a database comparison will also overwrite customized upgrade settings with the defaults for the specified target orientation.
Project Comparisons
If you’ve manually inserted objects into your project and you want to see how those objects differ from the same objects in another database, you’ll want to perform a project comparison. This method compares only the objects in the project, and does not repopulate the project?except in the case of record comparisons, which we explain below. Upgrade settings are never modified when you perform a project comparison.
Comparing Records
When records are compared, during a database or project comparison, any differences found in record fields will be written into the project. For example, let’s say Record A in the source database contains record fields 1, 2, 3, 4, and 5, and Record A in the target database contains fields 2, 4, 6, and 7. Before the comparison, the project contains only Record A. After the comparison, the project would contain Record A and recfields 1, 3, 5, 6, and 7.
Note. This is the only situation in which a project comparison will repopulate a project.
Comparison Reports
By default, when you perform a comparison, the system generates a report for each object type compared. These reports provide detailed information on how each object in the source differs from its counterpart in the target.
Before performing a comparison, you can choose what object status combinations you want to generate reports on. For example, during an upgrade to a new PeopleSoft release, you may decide that if an object is found in the source that was last changed in the target by PeopleSoft and that hasn’t changed since the last upgrade, you don’t need to see any information on the object definition differences (because you intend to accept PeopleSoft’s new version). In this case, you want to filter your compare reports so that a report is not generated if Source = (any status) and Target = Unchanged.
Note. Filtering comparison reports doesn’t affect which objects get added to a project during a database comparison, only which objects are reported. Any object defined differently in the two databases will always be added to the project.
To define the comparison report filter
1. Select File, Project Properties.
The Project Properties dialog (General page) is displayed.
2. Click the Report Filter tab. The Report Filter page of the dialog is displayed. Report Filter Page of the Project Properties Dialog You use the checkboxes to specify how you want to filter your upgrade comparison reports.
3. Select the checkboxes corresponding to the object status combinations you want reported.
Each row in the matrix corresponds to the object status in the source database. Each column corresponds to the object status in the target. The default settings for report filtering will show conflicting customized definitions only. To reset your selections to the default setting, click the Default button. To choose all object status combinations, click Select All. If you don’t want to generate any reports, deselect all of the checkboxes.
4. Click OK.
When you save your project, the report filtering settings you made will be saved and remain set until you change them again. Performing Comparisons To perform a comparison
1. Lock all Application Designer objects in the target database (optional).
If you’re performing a full comparison, it may take several days for you to review all your comparison reports and to set your upgrade settings accordingly. Locking the target database Application Designer objects will ensure that those definitions cannot be changed between the comparison and the time you’re ready to copy.
2. Turn off all tracing.
3. Set your report filtering options, if you haven’t done so already.
For more information see Comparison Reports (Development ToolsApplication DesignerUpgrading with Application DesignerComparing and Reporting).
4. Select Tools, Upgrade, Compare and Report.
The Target Signon dialog is displayed, prompting you to sign on to a target database.
5. Sign on to the target database. Sign on just as you would to any PeopleSoft database. The Compare and Report dialog is then displayed:
Compare and Report Dialog
You use this dialog to set your comparison preferences and to kick off the comparison and reporting processes.
6. Select the Compare Type.
Your options are Project and Database. When you perform a Project comparison, only the objects in the current project?of the specified Object Type(s)?are compared; the contents of the project do not change.
In a Database comparison, all definitions?of the specified Object Type(s)?will be compared.
Warning! If you choose Database, the contents of the current project will be deleted and replaced with objects found during the comparison.
7. Choose the criteria to Compare By.
By default, you compare the databases by the highest Release that the two databases have in common. If you want, you can use the drop-down list to select from lower common releases. Using the Release option, the comparison process will label objects as Changed or Custom/Changed if they’ve been changed since the date/time stamp for that release level. You can choose to compare the databases based on a particular Date instead of by Release. In this case, the comparison process will label objects as Changed or Custom/Changed if they’ve been modified since the Date that you specify.
8. Specify the Run Location. If your Run Location is set to Client, you’ll only be able to select one Object Type(s) at a time, due to locking constraints. If you want to select more than one object type or to Select All object types, you must set Run Location to Server. When you do, you can choose a specific server type from the drop-down list or use the default of (any).
9. Pick a Target Orientation. The Target Orientation option determines how the Upgrade checkboxes in the upgrade definition window will be set for objects that were last modified by the customer in one database and last modified by PeopleSoft in the other database.
If you select the PeopleSoft Vanilla orientation, the Upgrade checkboxes will be set so as to preserve PeopleSoft’s changes. If you select the Keep Customizations option, the checkboxes will be set so that your changes are preserved.
10. Choose your Object Type(s). From the Object Type(s) list, select the types of objects you want to compare. If you want to select all or of most of the types, click Select All. You can then use Ctrl+click to deselect any unwanted types.
Note. The Select All button is only enabled if the Run Location is set to Server. To avoid concurrency problems, you should not select the two PeopleCode object types alongs with the others. The Menu PeopleCode and Record PeopleCode comparisons can be run together, but run them either before or after running the other object type comparisons.
11. If you plan to run the compare report SQRs later from outside the PeopleSoft system (using SQRW) click Apply, then Cancel. Clicking Apply saves your comparison settings to the database so they can be referenced later when you run the SQRs.
12. To perform the comparison now, click Compare. Process Scheduler will begin to run the SQR(s) and the dialog will close. Note. If your Run Location was Client, you’ll have to repeat this procedure from step 3 for each object type you want to compare.
13. After the comparison completes, check for messages.
Application Designer lets you compare the contents of your project with the target database and shows you both online and in generated reports the status of each object in the target and the source database. You can then decide which object definitions to keep.
Note. Comparisons are not always necessary. For example, if you’ve created a project in your development database that consists only of new object definitions, you can be sure that those objects don’t exist in your production database. In this case, you can simply review your upgrade settings and copy the project without fear of overwriting any object definitions in the target.
Topics
Comparable Object Types
Comparison Types
Comparison Reports
Performing Comparisons
Comparable Object Types — The object types that Application Designer can compare are:
Records
Indexes
Fields
Translates
Panels
Panel Groups
Menus
Record PeopleCode
Menu PeopleCode
Colors
Styles
Comparison Types — Two ways to perform a source/target comparison.
One way is to compare all the database objects of the type specified and populate the current project with any objects defined differently in the source than in the target.
The other way is to compare only the objects in the current project.
Topics
Database Comparisons
Project Comparisons
Comparing Records
Database Comparisons
Earlier, we discussed the different methods of selecting objects to insert into a project. One of these methods was selecting objects by comparison. You do this by performing a database comparison, in which the current (source) database is compared with another database.
Any objects defined differently in the two databases are added to the project.
Application Designer performs comparisons one object type at a time. You decide which types of objects you want compared. For each object type you select, Application Designer will remove any existing objects of that type from the current project and repopulate the project based on the comparison results.
For this reason, you should be careful when performing a database comparison on a predefined project.
For example, say your project includes several record, panel, and menu definitions and you perform a database comparison just on panels. All the panel definitions that were originally in the project will be removed and be replaced by any panel definitions found in the compare process. However, the record and menu definitions in your project will not be affected.
Performing a database comparison will also overwrite customized upgrade settings with the defaults for the specified target orientation.
Project Comparisons
If you’ve manually inserted objects into your project and you want to see how those objects differ from the same objects in another database, you’ll want to perform a project comparison. This method compares only the objects in the project, and does not repopulate the project?except in the case of record comparisons, which we explain below. Upgrade settings are never modified when you perform a project comparison.
Comparing Records
When records are compared, during a database or project comparison, any differences found in record fields will be written into the project. For example, let’s say Record A in the source database contains record fields 1, 2, 3, 4, and 5, and Record A in the target database contains fields 2, 4, 6, and 7. Before the comparison, the project contains only Record A. After the comparison, the project would contain Record A and recfields 1, 3, 5, 6, and 7.
Note. This is the only situation in which a project comparison will repopulate a project.
Comparison Reports
By default, when you perform a comparison, the system generates a report for each object type compared. These reports provide detailed information on how each object in the source differs from its counterpart in the target.
Before performing a comparison, you can choose what object status combinations you want to generate reports on. For example, during an upgrade to a new PeopleSoft release, you may decide that if an object is found in the source that was last changed in the target by PeopleSoft and that hasn’t changed since the last upgrade, you don’t need to see any information on the object definition differences (because you intend to accept PeopleSoft’s new version). In this case, you want to filter your compare reports so that a report is not generated if Source = (any status) and Target = Unchanged.
Note. Filtering comparison reports doesn’t affect which objects get added to a project during a database comparison, only which objects are reported. Any object defined differently in the two databases will always be added to the project.
To define the comparison report filter
1. Select File, Project Properties.
The Project Properties dialog (General page) is displayed.
2. Click the Report Filter tab. The Report Filter page of the dialog is displayed. Report Filter Page of the Project Properties Dialog You use the checkboxes to specify how you want to filter your upgrade comparison reports.
3. Select the checkboxes corresponding to the object status combinations you want reported.
Each row in the matrix corresponds to the object status in the source database. Each column corresponds to the object status in the target. The default settings for report filtering will show conflicting customized definitions only. To reset your selections to the default setting, click the Default button. To choose all object status combinations, click Select All. If you don’t want to generate any reports, deselect all of the checkboxes.
4. Click OK.
When you save your project, the report filtering settings you made will be saved and remain set until you change them again. Performing Comparisons To perform a comparison
1. Lock all Application Designer objects in the target database (optional).
If you’re performing a full comparison, it may take several days for you to review all your comparison reports and to set your upgrade settings accordingly. Locking the target database Application Designer objects will ensure that those definitions cannot be changed between the comparison and the time you’re ready to copy.
2. Turn off all tracing.
3. Set your report filtering options, if you haven’t done so already.
For more information see Comparison Reports (Development ToolsApplication DesignerUpgrading with Application DesignerComparing and Reporting).
4. Select Tools, Upgrade, Compare and Report.
The Target Signon dialog is displayed, prompting you to sign on to a target database.
5. Sign on to the target database. Sign on just as you would to any PeopleSoft database. The Compare and Report dialog is then displayed:
Compare and Report Dialog
You use this dialog to set your comparison preferences and to kick off the comparison and reporting processes.
6. Select the Compare Type.
Your options are Project and Database. When you perform a Project comparison, only the objects in the current project?of the specified Object Type(s)?are compared; the contents of the project do not change.
In a Database comparison, all definitions?of the specified Object Type(s)?will be compared.
Warning! If you choose Database, the contents of the current project will be deleted and replaced with objects found during the comparison.
7. Choose the criteria to Compare By.
By default, you compare the databases by the highest Release that the two databases have in common. If you want, you can use the drop-down list to select from lower common releases. Using the Release option, the comparison process will label objects as Changed or Custom/Changed if they’ve been changed since the date/time stamp for that release level. You can choose to compare the databases based on a particular Date instead of by Release. In this case, the comparison process will label objects as Changed or Custom/Changed if they’ve been modified since the Date that you specify.
8. Specify the Run Location. If your Run Location is set to Client, you’ll only be able to select one Object Type(s) at a time, due to locking constraints. If you want to select more than one object type or to Select All object types, you must set Run Location to Server. When you do, you can choose a specific server type from the drop-down list or use the default of (any).
9. Pick a Target Orientation. The Target Orientation option determines how the Upgrade checkboxes in the upgrade definition window will be set for objects that were last modified by the customer in one database and last modified by PeopleSoft in the other database.
If you select the PeopleSoft Vanilla orientation, the Upgrade checkboxes will be set so as to preserve PeopleSoft’s changes. If you select the Keep Customizations option, the checkboxes will be set so that your changes are preserved.
10. Choose your Object Type(s). From the Object Type(s) list, select the types of objects you want to compare. If you want to select all or of most of the types, click Select All. You can then use Ctrl+click to deselect any unwanted types.
Note. The Select All button is only enabled if the Run Location is set to Server. To avoid concurrency problems, you should not select the two PeopleCode object types alongs with the others. The Menu PeopleCode and Record PeopleCode comparisons can be run together, but run them either before or after running the other object type comparisons.
11. If you plan to run the compare report SQRs later from outside the PeopleSoft system (using SQRW) click Apply, then Cancel. Clicking Apply saves your comparison settings to the database so they can be referenced later when you run the SQRs.
12. To perform the comparison now, click Compare. Process Scheduler will begin to run the SQR(s) and the dialog will close. Note. If your Run Location was Client, you’ll have to repeat this procedure from step 3 for each object type you want to compare.
13. After the comparison completes, check for messages.
Monday, December 27, 2010
PeopleCode Events Overview
Overview
If you are reading this, you are probably familiar with creating fields, records, pages, components, and menus. With these objects alone, you can create working development projects. Simple ones, yes, but these objects are all you need for the most basic level of development. PeopleCode comes into play for those development projects requiring a little more control. For example, you know how to define the default of a field within the record definition. This means that a value is always placed in this field whenever it is empty. But what if you needed to have a default placed in a field based on a condition of another field? You cannot do this within the record definition, but you can by using PeopleCode. The PeopleCode capability enables you to set and control a lot of parameters and objects. To fully understand the PeopleCode system and develop with it, you first need to learn about how it works.
PeopleCode is a visual language similar to Microsoft Access or Visual Basic. In fact, in PeopleTools version 8.1, the coding syntax has been changed to reflect the Visual Basic (called VB from here on) standard. This makes the PeopleCode language easier to use and learn for developers who already know VB or the Visual Basic for Applications (VBA) programming language.
The first detail to learn about PeopleCode is not the language itself but how the code works within the framework of PeopleSoft. PeopleCode can be placed in events on the field definition in each record. PeopleCode can also, since PeopleSoft 8.1, be placed into events on fields within pages and components. Each event is a place of action that launches PeopleCode when certain processes take place on a page.
Using events is a difficult concept to grasp at first and you might have to spend extra time to fully understand this process.
Understanding How PeopleCode Events Work
PeopleSoft comes with 17 events for PeopleCode. Each event has a specific function and control for which it is designed.
PeopleSoft Panel Processor
PeopleCode does not just run whenever it feels like it. Each event has a specific time when it runs within the PeopleSoft process. The PeopleSoft process is controlled by the internal PeopleTools program, which is called the PeopleSoft Panel Processor . The PeopleSoft Panel Processor is the control program within PeopleSoft that is monitoring the user's actions. This program controls when the PeopleCode will activate as well as a lot of other things based on the user's actions.
Event Order
To best understand the PeopleSoft Panel Processor, consider the process of starting and running a page to see when all the types of events are going to run.
First, the user selects from the menu to run a specific page in a certain action (this action being Add, Update/Display, Update/Display All, or Correction). The Panel Processor then looks at the menu selection to find the component connected with it. The component contains the search record for the action in which the user has asked the system to run. The search record keys, alternate keys, and list information are gathered from the search record that has been assigned to the component. The search page is created online with the keys and alternate keys displayed for entry, but before the user can enter any data, the first event of PeopleCode activates.
SearchInit
After this code is complete, the user is able to interact with the search page to search records and select the one they wish to process. Upon clicking OK in this search panel, the next event becomes active.
SearchSave
If no error messages are encountered within the code, the main page, as requested from the menu by the user, is now opened. Data, as selected from the search page, is then ready to be loaded into the page. Before the data is actually loaded, each row of data that is to be loaded onto the page is validated by the code contained in the next event.
RowSelect
The page is now filled with data from the search page, but the processor is not yet ready for user input. A series of events must launch for each row of data already loaded into the page.
FieldDefault FieldFormula RowInit
Each one of these events runs, in order, on all rows of data within the page. After all these events run, and if no errors have occurred, the page is open for entry by the user, finally. But this is only the beginning of what PeopleCode can do. Now, depending on the action of the user, more PeopleCode may activate.
If the user changes a field (modifies or deletes) or adds data to an empty field, the following PeopleCode events run. Of course, the standard PeopleSoft record edits must pass first, such as the prompt values as defined in the record definition for this field.
FieldEdit FieldChange FieldDefault FieldForumla
If the user inserts a new row of data on a page (only for occurs levels 1 through 3), then the following PeopleCode events run for all the fields on the record that is within the level where the add was performed. To better explain, if the insert was done on level 2, then only the PeopleCode events on the record definitions contained within level 2 are activated. The PeopleCode events within the record definition on levels 0 or 1 are not activated.
RowInsert FieldDefault FieldFormula
If the user deletes a row of data on a level 1 through 3 scroll, then the following PeopleCode events run for the row deleted and then for all the rows that are left within the level where the delete occurred.
RowDelete FieldDefault FieldFormula
The last step the user can take is to save the page. The save process runs another set of PeopleCode events, as listed below.
SaveEdit SavePreChg Workflow SavePostChg
As you can see, the PeopleCode events activate based on certain actions requested by the user, such as starting a page, changing data, or inserting new rows.
You will also note that some events of PeopleCode are running a lot of the time. You need to understand the flow of the events so that when you have a function or code to add to the system, you place the code in the correct event and therefore it runs at the appropriate time to perform the correct action. You can place code in an incorrect event so that it runs too much—this will affect your system by slowing it down.
Using PeopleCode Events
This section teaches you about each event, providing examples of use including actual PeopleCode. Developers already familiar with PeopleCode will see the same events but there are some new things to learn as well - these are fully discussed in the "New to Version 8.1" section below.
SearchInit
The code in the SearchInit event is needed only for records that will be used as search records. If the record definition you have is not used as a search record, then code placed in here will never run.
Code placed in this event is typically used to set up default values on the search page. For example, this event is mostly used to set up counters or defaults from the operator preferences on the search panel. For example:
SetSearchEdit(BUSINESS_UNIT);
SetSearchDefault(BUSINESS_UNIT);
This example turns on edits and system defaults for the Business Unit field in the search page. This code does not cause any other PeopleCode category to activate; it simply sets up the information in the page object to be used in that specific field.
SearchSave
This code also applies to the search page and is typically used to validate the search page to make sure all the required fields are filled in. Because you cannot specify the required fields on a search record (the record definition's required attribute is for use only on the regular pages), you can place code here to do the validation of all the fields that must be filled in. For example:
If None(BUSINESS_UNIT) Then
Error MsgGet(9000, 3, "Business Unit is a required field.");
End-If;
check_status(BUSINESS_UNIT, PO_ID, &STATUS);
If &STATUS = "C" Or &STATUS = "X" Then
Error MsgGet(10200, 164, "The specified PO exists, and is either Completed or Cancelled.");
End-If;
check_auto_num(BUSINESS_UNIT, &AUTO_NUM);
If None(&AUTO_NUM) Then
Error MsgGet(10000, 1, "%1 is not a Purchasing business unit.", BUSINESS_UNIT);
End-If;
If &AUTO_NUM = "N" And PO_ID = "NEXT" Then
Error MsgGet(10200, 34, "This business unit is not set up for purchase order auto numbering.");
End-If;
This example shows a little more about the capability of this event of code. The code here, although complex for now basically checks that certain fields are filled in. If they are filled in, then the data is validated for specific conditions. If an error condition exists, an error message is displayed and the search page is not closed.
RowInit
Now you have arrived at events that are used more often in development projects. The code in the RowInit event runs every time a new row of data loads into a page. This section activates code for each level and each data row. For example, if you have a panel with a level 1 that has 15 data rows that load, the RowInit event will run 16 times: once for the level 0 (header information) and then 15 times for each row of data on level 1.
You need to understand that even though on the page you can see only five rows of data, this code activates for all rows of data that are loaded into the buffer. So it is not how many rows are visible on the page, but how many rows are loaded into the data buffer that determines how many times this PeopleCode category activates. Consider the following 3 examples:
If %PanelGroup = PANELGROUP.VCHR_PO_STD Then
If All(PO_ID) Then
VCHR_PANELS_WRK.TXN_CURRENCY_SRC = "P";
End-If;
End-If;
If INSTALLATION.PO = "Y" Then
DERIVED.EDITTABLE13 = "ITM_PURCH_FS";
End-If;
Gray(LINE_NBR);
Gray(SCHED_NBR);
Gray(BUSINESS_UNIT_RECV);
These examples show different ways to use the RowInit event that are typically done. The first example shows, based on some information, that you can set some values into special fields. The second example shows that you can also set or change the prompts used. Finally, the third example shows how you can override the settings on the page to hide, gray (display-only), unhide, or ungray (not display-only) fields based on the initialization.
FieldDefault
In this event, field defaults can be coded. This event works only to set defaults into fields where the logic is too complex to use the record definition. If, by contrast, you have a hard default that is always to be used, then you should set that value in the record definition. Otherwise, you can set the default based on a condition check of some other field or value using code from the FieldDefault event. For example:
If None(ITM_SETID) Then ITM_SETID = GetSetId("BUSINESS_UNIT", BUSINESS_UNIT, "MASTER_ITEM_TBL", ""); End-If;
This example checks whether the ITM_SETID field is filled in. If the field is not filled in, then the code runs a function to get the information and place it in this field.
FieldChange
The FieldChange event occurs under two circumstances: once when a field is changed, and the other when a button is pressed.
For the field change event, the code does not execute unless there is a net change (changing the value of a field to it's original value is not a net change) and the user moves out of the field or saves the page. Here you can do very complex validation upon the field.
Example: If VENDOR_ITM_VW1.ITM_STATUS = "A" Then If PRICE_DT_TYPE = "P" And QTY_TYPE = "L" Then check_line(); Else check_schedules(); End-If;
This example checks the values in a couple of fields and then, based on their settings, runs a different function. In the FieldChange event, you are not limited to using only the field from which the code is launched, but rather, you can access any field on the page as is shown in this example.
You can also use the FieldChange event when you have a button on a panel; here you would place the code that you wish to activate when the button is pressed. The code you place for a button can be just about anything you want. This is what makes button actions so powerful—they can perform all sorts of actions, controls, and verifications. When you have a button and it is pressed, you are usually looking for some action to take place: jump to another panel, fill in the current panel with data based on some criteria you have set, or run a process, for example.
UnhideScroll(RECORD.PO_LINE);
UnGray(GOTO_LINE_DTLS);
SEL_LINE_FLG = "Y";
LINES_SAVED = "N";
&WHERE = "where BUSINESS_UNIT = :1 and PO_ID = :2 and LINE_NBR >= :3";
If All(LINE_NBR_TO) Then
&WHERE = &WHERE | " and LINE_NBR <= :4";
End-If;
ScrollFlush(RECORD.PO_LINE);
ScrollSelect(1, RECORD.PO_LINE, RECORD.PO_LINE, &WHERE, PO_HDR.BUSINESS_UNIT, PO_HDR.PO_ID, LINE_NBR_FROM, LINE_NBR_TO, True);
This example is from a button which, when pressed, is going to fill in data based on some criteria. The scroll, on level 1 in the panel, is un-hidden (now visible on the panel), and the data buffer is emptied for this scroll (scroll flush). Finally, the scroll's data buffer is filled with data based on a selection criterion.
FieldEdit
In the FieldEdit event, you apply most of the field editing to validate information within a field. You already can do one simple validation using the prompts on a field as defined within the record definition, but this event gives you the ability to validate using multiple fields and conditions. This is also the event to apply messages from PeopleCode that will not crash the panel, to warn or stop the process within the page.
The FieldEdit event is where you should place validation that you want to occur within the page field entry process. If you have validations for which you do not want to leave the field before a specific criteria or process is complete, then you need to place code in FieldEdit. In this event, the action is based only on the field you are in, so be careful not to validate against another field that you cannot change. If you do validate against another field, you will be locked in a loop and you won't be able to leave this field.
To better explain this potential loop problem, assume that you have two fields on a panel. The first field can be set to a value of Red or Blue only. The second field can be set to a number that represents the brightness of the color (that is, the first field). Now red can have a brightness only between 1 and 5, whereas blue can have a brightness between 3 and 9. So you place FieldEdit code in the first field (the color field) that states that if the value is blue, then the value of the second field must be between 1 and 5. This meets the logic requirement, but if you understand the FieldEdit process, you will see how this will get you into trouble. The user enters Red into the first field and tabs out to the next field. The PeopleSoft Panel Processor then runs the FieldEdit code for the field that states that if the value is red (which is what the user entered), then the value of the next field must be between 1 and 5. Because the user has not entered a value in the next field, the value is initialized as 0. Therefore, this FieldEdit code fails and requires the user to input a value that will not fail. Because you've allowed only red and blue, and neither is allowed to have a 0 value, then the user can never leave this field. This is a simple explanation, but it shows how you have to be careful when using FieldEdit code for validation using other fields. You have to understand the capabilities of the system and how the rules will be applied. In this example, the best solution is to change the location where the validation code is placed. The validation code should be placed in the Field Edit event of the brightness field, or it could be moved to the SaveEdit event (about which you have not learned yet).
The way that PeopleSoft will pass or fail a validation is by issuing a message. If you fail the validation, a message must be called. If you pass the validation, then no messages are displayed. There are two types of messages: warning and error. Each is a message, but they carry different levels of actions.
A warning message tells the user that they might not want to continue or to use this value in the field. With a warning message, the user can decide to stop and fix the issue or move on and ignore the message. A warning means that the value in this field is not normal, but the user is still allowed to continue. An error message , on the other hand, does not offer the user a choice; the user must stop and fix the problem. You should make sure that all decisions to error or warn are well documented in the technical documentation for your modification.
The following example shows how to use the FieldEdit to do complex validation with an error message. If a problem occurs, issue an error message to the user stating the problem and do not allow the process to continue until it is resolved.
check_item();
If None(PO_HDR.VENDOR_ID) Then
Error MsgGet(10200, 76, "Vendor %1 is not set up for item %2.", PO_HDR.VENDOR_ID, INV_ITEM_ID);
End-if;
FieldFormula
This event is typically reserved for custom-built functions. You have already learned about the flow of how PeopleCode activates from the PeopleSoft Panel Processor and that the process FieldFormula runs for all sorts of actions. You should not place any code in this category except custom-built functions.
Consider the following example:
Function update_sched() &CHANGED_SCHED = "N";
UpdateValue(LINE_NBR, &LINE, PO_LINE_SHIP.UNIT_PRC_TOL, &SCHED, &UNIT_PRC_TOL);
UpdateValue(LINE_NBR, &LINE, PO_LINE_SHIP.PCT_UNIT_PRC_TOL, &SCHED, &PCT_UNIT_PRC_TOL); End-Function;
This example shows a function. A function that performs some actions can take in multiple values and even send back values to the calling program. This enables you to write business logic into functions that many programs can call so that you do not have to write the logic over and over in multiple PeopleCode categories. This is important to PeopleCode 8.1 in that you now have the ability to place code in new places. By having more places—pages and components—in which to load PeopleCode, you need to remember that you may require the same code in multiple places or categories. Instead of copying the code into all these different places and events, you can create the one function and then call it from the other places, thereby having your logic in only one place. Due to this added complexity, you will need to think more in terms of functions when writing code. Writing more functions that you place into this event will save you time and effort in the long run.
RowSelect
This event is used to filter data loaded into a scroll level. This is an advanced technique that will not be covered in this book. Most developers who want to restrict rows of data create a view to use on the page with the restriction in the SQL where clause. By using this event, you can apply this restriction as you need to or you can even change the criteria as the page is being created.
RowDelete
This event is used to activate code when a delete is performed for a row of data rather than for only a single field. This delete can be on any level. The developer, when using this code even, can control whether a row of data can be deleted by checking for orphaned records or other required validations. The purpose of this code is to prevent deletes unless a specific criterion has been met.
The process to show the pass or fail of the event is just like in the FieldEdit event. If you failed the validation, pass a message. If you do not issue any messages, then the delete process has passed the validation. This category can also use the warning and error message commands: An error always fails; a warning reports an issue to the user.
If VOUCHER.POST_STATUS_AP = "P" Then Error MsgGet(7030, 109, "You cannot delete a voucher line if the voucher has been posted."); End-If;
The code here checks a field for a specific status. If a certain status is set, then the code prevents the deletion by issuing an error message. If the status is not set, the code will allow the deletion by not issuing any error or warning message.
RowInsert
In the RowInsert event, you apply code to inserts of new data rows. Data, when loaded into the page, is validated through the RowInit event, but when you add a new record (level 0) or a new row of information (level 1 through 3), there is no RowInit to activate because it was not initiated into the panel. The RowInsert can and should perform the up-front validations that you would have placed in RowInit for new data rows being added.
|
| Note | As you can see, you might need to place the same code in the RowInit and RowInsert categories. This is another reason for looking at code that you could place into functions so that you do not end up copying the same code into multiple categories. |
If VCHR_PANELS_WRK.FULL_SET_FLG = "Y" Then
MERCHANDISE_AMT = VCHR_PANELS_WRK.VCHR_BALANCE_AMT; VCHR_PANELS_WRK.LN_BALANCE_AMT = MERCHANDISE_AMT;
End-If;
This code checks a work record field for a specific status and then updates some fields using information in some other work record fields. The process done here can be just about anything you think of, from graying (display only) and hiding fields to doing complex logic for recalculating a total.
SaveEdit
In the SaveEdit event, you place code that validates on a save. This is used for the final validation of the record. Usually, you can do a lot of this validation in the FieldEdit event, but you might want the user to do all the input they can and then do a check just prior to the save. You might also have to validate multiple fields as a unit. It is hard to do this within FieldEdit, so you can place the overall validation in this event instead.
A final use for the SaveEdit event occurs when the user does not tab through every field so the FieldEdit code never activates. If you still need this validation, you will have to add it to SaveEdit to be sure that the code does run.
As with the other edit events, you can prevent or just warn on a save when the validation is incorrect. If you prevent, then the user is unable to save the panel. If you warn, then the user can stop the save process and edit the problem or they can continue. The process is the same regardless of whether you're using warning or error messages. Of course, no message means that you have passed the SaveEdit validation.
If DISTRIB_MTHD_FLG = "Q" And None(QTY_VCHR) Then
Error MsgGet(7030, 49, "You must enter either a quantity on Voucher Line %1 when distributing by quantity. Or you can delete the line.", VOUCHER_LINE_NUM);
Else
If None(MERCHANDISE_AMT) Then
Error MsgGet(7030, 49, "You must enter either a merchandise amount on Voucher Line %1 when distributing by amount. Or you can delete the line.", VOUCHER_LINE_NUM);
End-If;
End-If
This code checks some values in the fields and then, if certain conditions exist, an error message is issued, preventing the save of this panel.
SavePreChange
In the SavePreChange event, you can place code to change or update the data tables prior to the save being committed to the database. This is your last chance to make changes. In your page, you are making changes, but these changes are loaded only into your panel buffers. It is not until you save the record that the database is updated. Once the save is started, the PeopleCode runs and does all its SaveEdit checks and then just prior to saving the information to the database, this event occurs. This event enables you to do other processes and update other rows of data, assuming that the process will complete.
The SavePreChange event, is used mostly to update hidden fields that need to be changed or corrected based on user input just prior to saving. You do not do updates within SaveEdit, as this is only supposed to be looking for errors. If you need to total a field in level 1 and place the information in a header record (level 0), here is where you would make the code work, since you do not want to update the header level field with every row insert or field change on the level 1. This enables you to put code in one place, and it runs doing the work just prior to the save.
Many developers will place SQLexec function calls in this category. SQLexec function calls enable you to run a SQL statement directly so that you can update a related field that is not within the page. You have to be aware that using SQL direct statements can lead you into trouble. You must be sure that the record you are going to update or insert is not any- where within the component. If any field of the record is on the component, then you will receive an error: This panel has been updated by another user . This error message is telling you that something happened to the data between the time you retrieved the data and the time you saved it. Someone else grabbed the information and changed it prior to your change committing it to the database. The problem occurred because of your SQLexec—it ran and made an update to the database. The PeopleSoft Panel Processor then tries to commit the rest of your changes and sees that someone else (yes, even though the SQLexec came from your process, the processor sees this as another user) has made a change, so your panel is not allowed to save. So be extra careful when using SQL calls within the events.
For &BUFFER_ROW = 1 To &CURRENT_ROW
UpdateValue(VOUCHER_ID, CurrentRowNumber(), DISTRIB_LINE.DESCR, &BUFFER_ROW, DESCR);
End-For;
This code loops through all the rows of a level 1 panel and updates a value in a lower-level scroll (level 2).
SavePostChange
In the SavePostChange event, you add code that you want to activate after the rows of data have been saved to the database. Code placed here could be line totals and other calculated fields. This is also where a lot of SQL execs are written and performed to update other necessary fields.
If %Mode = "A" and VENDOR.VENDOR_PERSISTENCE = "O" Then
&SETID = GetSetId("BUSINESS_UNIT", BUSINESS_UNIT, "VENDOR", "");
SQLExec("Update ps_vendor set VENDOR_STATUS = 'I' where setid = :1 and vendor_id = :2", &SETID, VENDOR_ID)
End-If;
This code example shows that if the page is in Add mode and the vendor is set to a special persistence, then the vendor status must be set back to inactive so that no further POs can be used. This process is used on vendors that are set to one time use only. After you use them on a PO , the status is set to inactive so that you do not use them again. You can see in this example how you are updating the vendor based on an action in the PO system.
Workflow
The workflow event is for the specific use of workflow. You need to put in specific code here that updates the workflow records and processes. Workflow is beyond the scope of this book.
New for Version 8.1
With the release of PeopleTools 8.1, PeopleSoft has made some major changes to the PeopleCode system. You are now able to place code not only within the record definition, but you can also place code in the page, component, Application Engine, and Business Components. This means that you have more places to enter code to meet your development needs. These new capabilities are explained in this section.
|
| Note | Application Engine and Business Components are specialized, advanced tools that are not covered in this book. They are mentioned here so that you know about all the new places to locate PeopleCode, but no further mention will be made. |
The process of PeopleCode running on the system has already been mentioned, but what happens when PeopleCode is encountered within the new areas such as pages and components? Well, the same overall order still applies. For example, when a scroll level has data loaded into it, the RowInit code runs for each row of data. Now you are only adding a new level of complexity to RowInit: first, all the RowInit code runs from the record definition, then all the page code RowInit activates, and finally the RowInit code in the component area runs. So the overall flow of the code running through the events stays the same, but now you have some new areas to run within each event.
Let's explore each new section of PeopleCode within the page and component, reviewing why they would be used and what events are available.
PeopleCode in Pages
Why would you place code in a page versus a record definition? If you have code for a RowInit-type function that you wish to have run only when you are accessing a specific page, then you should place code in that page. This way, you do not need to write code to retrieve the page name and then check whether you need to run this code.
This requirement to have code run when within a specific page is used all the time, because if you include one field from a record, all the PeopleCode of the record loads into the page and runs. You might have a page that is usually used for this record and you hide this field until a specific action is done. Now, when you are in the other panel, the code will run, but this field does not exist on the page because it is for a whole different process. You would have had to retrieve a value for the page you are in to then see whether you wish to run the code.
Now with the new capability in PeopleSoft 8.1, you can place code directly into the page so that it will activate only when the page is accessed.
The event here is a new name called Activate. Although it is a new name, you can swap it for RowInit. This is the only event available for the page; the code entered here is associated with the page itself.
PeopleCode in Components
Because you could place code only in the Activate event within the panel, there was still a need to place code so that it runs only when a specific page is running. PeopleSoft tools developers created a new place to load PeopleCode within the component to solve this problem. You now have access to many more events that run only when in a specific component.
Within the component definition, you have access to the following events:
• SearchInit
• SearchSave
• RowInit
• RowSelect
• RowInsert
• RowDelete
• SaveEdit
• SavePreChange
• SavePostChange
• FieldChange
• FieldDefault
• PreBuild
• PostBuild
• SavePreChange
• SavePostChange
• Workflow
• FieldEdit
As you can see from the list, you have all the events—including two new ones: PreBuild and PostBuild. The order of the events and how they activate is not changed within the component. Having this new capability enables you to enter code into the component so that the code will run only when this component is active.
Remembering the rule requiring all the PeopleCode of a record to be loaded, even when using only one field on a page, let's see how you can save time and effort by using this new capability. Instead of having to write code to check what component you are in for every piece of PeopleCode you have in the record definition, you can place just the right amount of code within the component PeopleCode events. This makes the PeopleCode easier to write and much more efficient.
Usually, you enter all the PeopleCode into the record and field definition area, but now you can enter the code into the component. Using the component PeopleCode events, it gets a little more complex. You can enter code against the component itself, fields within the component and, finally, records within the component.
PreBuild
The PreBuild event is specific to the component; this code runs prior to the component being built. This is typically used to hide, unhide, gray, or ungray fields, or to set certain values in the component.
PeopleCode within the component record gives you an opportunity to set variables that can be used later by PeopleCode located in other events. PreBuild can also be used to validate data in the search page just as is done in the SearchSave event. This event runs right after the RowSelect event within the flow of PeopleCode.
PostBuild
The PostBuild event is specific to the component; this code runs after the component is built. This is usually used to hide, unhide, gray, or ungray fields, or to set certain values in the component. The event can be thought of just like the RowInit event as the data is already loaded into the component. This even runs right after the RowInit even in the PeopleCode flow.
Subscribe to:
Posts (Atom)