Saturday, December 24, 2011
Web Services and PeopleTools
I rt i t r r
Web Services And PeopleTools
Southern New England User Group Meeting, October 17th, 2008
Stephen Wills
Applied Technology Sales Consultant
The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions.
The development, release, and timing of any features or functionality described for Oracle’s
products remains at the sole discretion of Oracle.
Agenda
• Introduction To PeopleSoft Application Integration
• Consuming A Web Service
• The Web Service Wizard
• Demo: calling a Web Service and processing the results
• Exposing A Web Service
• Components
• Component Interfaces
• Demo
• Preparing Services
• Publishing WSDL
• Adding fields and re-publishing
Agenda
• Introduction To PeopleSoft Application Integration
• Consuming A Web Service
• The Web Service Wizard
• Demo: calling a Web Service and processing the results
• Exposing A Web Service
• Components
• Component Interfaces
• Demo
• Preparing Services
• Publishing WSDL
• Adding fields and re-publishing
PeopleSoft Application Integration
What’s The Goal?
• Our goal with PeopleSoft Application Integration is to:
• Enable application developers and customers to natively create and consume standard web services so that PeopleSoft applications can integrate into SOA environments
• Improve interoperability and automation by leveraging Web Service standards and a common interface
• Provide the option of interoperating with robust, optimized business integration services through Oracle Business Integration Technology (BPEL/ESB)
PeopleSoft Integration
How Do We Achieve This Goal?
• Integration technology built into the PeopleSoft Internet
Architecture – the PeopleSoft Integration Broker
• Embedded in tool set and delivered with all PeopleSoft
Enterprise applications
• Providing support for:
• Web Services
• Inbound/outbound synchronous/asynchronous XML messaging, including SOAP support
• COM, C, C++, Java, XML access to PeopleSoft application logic via API
• Flat File processing, inbound and outbound
What is an Integration Broker?
• Messaging
• Transformation
• Routing
• Connectors
• Development
• Monitoring
Services
Focus on Added Web Services Support in PeopleTools 8.48
Service Operations
Focus on Added Web Services Support in PeopleTools 8.48
Wizards
Focus on Added Web Services Support in PeopleTools 8.48
Utilities
Focus on Added Web Services Support in PeopleTools 8.48
Monitor
Focus on Added Web Services Support in PeopleTools 8.48
Agenda
• Introduction To PeopleSoft Application Integration
• Consuming A Web Service
• The Web Service Wizard
• Demo: calling a Web Service and processing the results
• Exposing A Web Service
• Components
• Component Interfaces
• Demo
• Preparing Services
• Publishing WSDL
• Calling Service
Demonstration
Consume and Call a Web Service
• Illustrate Web Service call using public Web Service:
• Demonstrate calling a web service
• Review Contact Component
• Review code used to invoke the Web Service
• Review code used to process the results
Web Services Home Page
• Web Services options accessed from Web Services Home Page
Consume Web Service Wizard
• Wizard imports Web Service definitions from a variety of sources
Consume Web Service Wizard
• Wizard inspects the WSDL and determines the service operations that it finds – only one in this case
Consume Web Service Wizard
• l fr fil
Consume Web Service Wizard
• Additional parameters automatically derived and set
Consume Web Service Wizard
• Request/Response messages created and named automatically
• Message names can be over-ridden if required for ease-of-use
Consume Web Service Wizard
• Message names can be over-ridden if required for ease-of-use
Consume Web Service Wizard
• Service name and default node automatically added
Consume Web Service Wizard
• Confirmation of created Web Services objects
• Complete view of objects available via View Consumed Service
View Consumed Service
• View of complete Service including all associated objects
Consume Web Service
Demonstration
Calling A Web Service
• Web Service invoked programmatically by PeopleCode
• For a synchronous message this would typically be code on a FieldChange or PageSave event
Calling A Web Service
• Contact Record has FieldChange PeopleCode for FieldChange
Calling A Web Service
1. Construct Request string
2. Make Synchronous Request Message call and receive results in the Response Message
3. Unravel Response Message and display appropriate fields
Call Web Service
Demonstration
Call Web Service
Example
• User updates the value of the data in the Zip Code field
• Field Change PeopleCode triggers to call the Web Service to return the Weather information for that Zip code
Call Web Service
Example
• Web Service returns
Weather including:
• A graphic showing the weather
• High and low forecast temperatures
• PeopleCode:
• Extracts info from the Web Service response message
• Formats and displays it on the page
Agenda
• Introduction To PeopleSoft Application Integration
• Consuming A Web Service
• The Web Service Wizard
• Demo: calling a Web Service and processing the results
• Exposing A Web Service
• Components
• Component Interfaces
• Demo
• Preparing Services
• Publishing WSDL
• Calling Service
Demonstration
Publish And Process a Web Service
• Review the PeopleTools “object hierarchy”
• Review the Vendor Component
• Expose the Vendor Component as a Web Service
• Call the Web Service using the SoapUI utility
PeopleTools Building Blocks
• Create fields
• Create records from fields
• Create pages from records
• Create components from pages
• Define Menu navigation to component
• Set Security for component
• Deploy via browser
Security (Controls Access)
Menu (Controls Navigation)
Component (Business Transaction Object)
Page (Defines UI)
Record
Record
Page (Defines UI)
Record
Field
Field Field
Field
Field Field
Field
Field Field
PeopleTools Building Blocks
SOAP/ Web Services
PeopleSoft
Batch App
Java C/C++ COM
Component Interface API
Component Interface
Security (Controls Access)
Menu (Controls Navigation)
Component (Business Transaction Object)
File
Page (Defines UI) Record
Record
Page (Defines UI) Record
Layout
Field
Field Field
Field
Field Field
Field
Field Field
Demonstration
Publish and process a Web Service
• Review the PeopleTools “object hierarchy”
• Review the Vendor Component
• Expose the Vendor Component as a Web Service
• Call the Web Service using the SoapUI utility
Review the Vendor Component
• Vendor Component in browser
Review the Vendor Component
• Vendor Component in browser
Review the Vendor Component
• Vendor Component Definition
Review the Vendor Component
• Vendor Component Structure
Vendor Component Interface
• This is the basis for the published Web Service
• Fields become Properties
• Default Methods are provided; others can be added with PeopleCode
Enable Required Service Operations
• Select the Component Interface to be exposed as a Web Service
Enable Required Service Operations
• Review the CI Status to determine available CI Methods
• Selected CI Methods will become Service Operations
Enable Required Service Operations
• Defaults are created
• Aliases and message versions may be specified if required
Enable Required Service Operations
• Web Service and Service Operations now created
• The Web Service can now be published and WSDL generated
“Provide Web Service” Wizard
• “Provide Web Service Wizard” steps through process
• Step 1: Select Service to be provided (defined in previous step)
“Provide Web Service” Wizard
• “Provide Web Service Wizard” steps through process
• Step 2: Select required Service Operations
“Provide Web Service” Wizard
• “Provide Web Service Wizard” steps through process
• Step 3: View Generated WSDL for Service
“Provide Web Service” Wizard
• “Provide Web Service Wizard” steps through process
• Step 4: Publish WSDL to UDDI Server if required
“Provide Web Service” Wizard
• “Provide Web Service Wizard” steps through process
• Finish: Review WSDL Generation Log: note the WSDL URL
Test The Web Service
• Test the Web Service with standard test tools e.g. SOAPUI
• Add Web Service definition to testing tool using .WSDL
Test The Web Service
• Test the Web Service with standard test tools e.g. SOAPUI
• Web Service Operation definitions retrieved and created
Test The Web Service
• Test the Web Service with standard test tools e.g. SOAPUI
• Example of Find Service Operation (Vendors starting with “C”)
Test The Web Service
• Test the Web Service with standard test tools e.g. SOAPUI
• Example of Get Service Operation (for a Vendor returned by Find)
Saturday, December 10, 2011
Dynamic View
PAGE Design
Create the work record : NM_STATE_WRK
Add two field COUNTRY, STATE
Create the Dynamic view : NM_STATE_DVW
Add two field STATE , Desc
Create the Component : ZM_STATE_COMP and add the page ZM_STATE_PG
On Component Record Field : COUNTRY write the following piece of CODE to populate STATE field value dynamically based on COUNTRY field selected.
SQLText : Is the property of Dynamic VW which assing to field value of record as show below....
NM_STATE_WRK.STATE.SqlText = &SQLString
Create the work record : NM_STATE_WRK
Add two field COUNTRY, STATE
Create the Dynamic view : NM_STATE_DVW
Add two field STATE , Desc
Create the Component : ZM_STATE_COMP and add the page ZM_STATE_PG
On Component Record Field : COUNTRY write the following piece of CODE to populate STATE field value dynamically based on COUNTRY field selected.
SQLText : Is the property of Dynamic VW which assing to field value of record as show below....
NM_STATE_WRK.STATE.SqlText = &SQLString
Wednesday, August 10, 2011
Change Assistance
Getting ready:
a. Install Change Assistant
b. Download items from MyOracle Support's Tools Only Upgrade homepage
Tools Only Upgrade template
and
Html documentation
Configuring Change Assistant for the Tools Only Upgrade:
a. Open Change Assistant
b. Tools / options / set Mode to Perform PeopleTools Only Upgrade; click OK.
c. File / import template.
d. Create the environment: File / new environment
Unicode: check checkbox if Unicode
Products: for Tools Only Upgrade, select PeopleTools.
Language: select languages installed to the environment being upgraded.
Database type: use pull down to select
Sql query tool: enter entire directory name
New release Ps_Home. Click Next.
e. Target database information:
Database name
Userid / userid's password
Accessid / accessid's password
If ungreyed, enter database server name and ownerid
f. Test connection
(Optional, but a good idea)
g. Save environment.
h. Create Upgrade Job:
File / New job / Use template
Select template previously imported
Select environment previously configured
Type of Upgrade: initial
i. Attach html documentation to the Upgrade job:
Edit / highlight the job name
Set documentation directory
Select html previously downloaded
File / save Job.
a. Install Change Assistant
b. Download items from MyOracle Support's Tools Only Upgrade homepage
Tools Only Upgrade template
and
Html documentation
Configuring Change Assistant for the Tools Only Upgrade:
a. Open Change Assistant
b. Tools / options / set Mode to Perform PeopleTools Only Upgrade; click OK.
c. File / import template.
d. Create the environment: File / new environment
Unicode: check checkbox if Unicode
Products: for Tools Only Upgrade, select PeopleTools.
Language: select languages installed to the environment being upgraded.
Database type: use pull down to select
Sql query tool: enter entire directory name
New release Ps_Home. Click Next.
e. Target database information:
Database name
Userid / userid's password
Accessid / accessid's password
If ungreyed, enter database server name and ownerid
f. Test connection
(Optional, but a good idea)
g. Save environment.
h. Create Upgrade Job:
File / New job / Use template
Select template previously imported
Select environment previously configured
Type of Upgrade: initial
i. Attach html documentation to the Upgrade job:
Edit / highlight the job name
Set documentation directory
Select html previously downloaded
File / save Job.
Dynamic View for Supervisor
Just came across basic requirement on creating PS query which shows all those employees ID which you are supervisor to them.
Its looks simply but bit trick to construct it. Below are the steps to achieve this.
• Create the view NGB_MANAGER_VW based on this sql : which basically join on JOB & PSOPRDEFN table to get the list of the employees ID for which you are the supervisor for them
SELECT DISTINCT B.OPRID,A.EMPLID FROM PS_JOB A, PSOPRDEFN B
WHERE A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED
WHERE A.EMPLID = A_ED.EMPLID
AND A.EMPL_RCD = A_ED.EMPL_RCD
AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),102), 1, 10))
AND A.EFFSEQ = (SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES
WHERE A.EMPLID = A_ES.EMPLID
AND A.EMPL_RCD = A_ES.EMPL_RCD
AND A.EFFDT = A_ES.EFFDT)
AND A.SUPERVISOR_ID = B.EMPLID
• The NGB_MANAGER_VIEW contains following two fields OPRID & EMPLID both as key …… You can added additional fields based on your requirement like First Name, Last Name , Name etc.,
• Added this view to query security tree [Navigation : PeopleTools > Security > Query Access Manager] Select appropriate tree and add the view to it.
• Create the PS Query (NGB_EMPLOYEELIST_BY_SUPERVISORID) on this view NGB_MANAGER_VW select emplid field and you will see in view sql by the default OperidID criteria get added to query by this feature the supervisor can see the list of employees under him.
You can used this PS QUERY in creating page let and added to your home page.
Its looks simply but bit trick to construct it. Below are the steps to achieve this.
• Create the view NGB_MANAGER_VW based on this sql : which basically join on JOB & PSOPRDEFN table to get the list of the employees ID for which you are the supervisor for them
SELECT DISTINCT B.OPRID,A.EMPLID FROM PS_JOB A, PSOPRDEFN B
WHERE A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED
WHERE A.EMPLID = A_ED.EMPLID
AND A.EMPL_RCD = A_ED.EMPL_RCD
AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),102), 1, 10))
AND A.EFFSEQ = (SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES
WHERE A.EMPLID = A_ES.EMPLID
AND A.EMPL_RCD = A_ES.EMPL_RCD
AND A.EFFDT = A_ES.EFFDT)
AND A.SUPERVISOR_ID = B.EMPLID
• The NGB_MANAGER_VIEW contains following two fields OPRID & EMPLID both as key …… You can added additional fields based on your requirement like First Name, Last Name , Name etc.,
• Added this view to query security tree [Navigation : PeopleTools > Security > Query Access Manager] Select appropriate tree and add the view to it.
• Create the PS Query (NGB_EMPLOYEELIST_BY_SUPERVISORID) on this view NGB_MANAGER_VW select emplid field and you will see in view sql by the default OperidID criteria get added to query by this feature the supervisor can see the list of employees under him.
You can used this PS QUERY in creating page let and added to your home page.
PeopleSoft Meta Tables
PeopleTools Tables (Where the MetaData is Stored)
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)
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)
PeopleSoft Meta Tables
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.SQLTYPEFile 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)
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.SQLTYPEFile 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)
Tuesday, May 31, 2011
STAT 5.5.5 Implementation
Prerequisite :
* Need Environment details
* Access Details for the Servers (includes DBMS + PeopleSoft)
* License for STAT
* Migration Paths for all environments
* Templates data
Microsoft SQL Server 2008 Management Studio Installation
IIS install on the server to activate the FTP utility for transfer the files
JDK 1.6x Installation
STAT Client 5.5.5 Installation
STAT Server Installation
Service created for STAT Server to bring up & down the server
STAT Repository database created ORASTDEVREP
STAT Staging database created ORASTDEVSTG from the copy of Demo database
* Need Environment details
* Access Details for the Servers (includes DBMS + PeopleSoft)
* License for STAT
* Migration Paths for all environments
* Templates data
Microsoft SQL Server 2008 Management Studio Installation
IIS install on the server to activate the FTP utility for transfer the files
JDK 1.6x Installation
STAT Client 5.5.5 Installation
STAT Server Installation
Service created for STAT Server to bring up & down the server
STAT Repository database created ORASTDEVREP
STAT Staging database created ORASTDEVSTG from the copy of Demo database
Subscribe to:
Posts (Atom)
