PeopleSoft Calculation of Hourly Rate:

In PeopleSoft HCM, we define Frequency ID’s that are defined on the Frequency table, that are used for many different purposes, but one of the usage of Frequency ID is to convert compensation calculations from one frequency to another.

Each Frequency ID that is defined is associated with a Annualization Factor. Frequency Annualization Factor is the number of periods in one year for the frequency ID. For example, a standard monthly frequency has the Annualization factor of 12.

If you hire a Salaried Employee and provide a compensation rate with Annual Frequency, how does PeopleSoft Calculate the Hourly Rate? What logic will it use in converting the Annual Amount to Hourly Amount?  We can take an Example of a Salaried Employee who has a Compensation Rate of 6000.00 USD with a Monthly Frequency.

PeopleSoft uses the below formula for Compensation Conversion Using Frequency:

Annual compensation rate / job standard hours’ × frequency factor of standard work period. 

First the system annualizes the Comp Rate for the given frequency, then for the Annualized Rate, the system applies the above formula. 

For monthly compensation of 6000.00 USD, it is first converted to Annualized rate, by applying the Annualization factor of 12.  This would become 72,000.00 USD. 

Next comes the Job Standard hour for the work period.  If the standard work period defined is as ‘W’ (Weekly, with annualization factor of 52), and for this work period, if the standard hours are defined as 40, then the above formula would be: 

Hourly Rate:           

=   72,000.00 / (40 * 52)   

          =       72,000 / (2080)

                   =       34.615385

find which processes user has access to

How to find which processes user has access to?

select 'X' from ps_PRCSDEFNGRP where prcsname= AND PRCSGRP IN (select DISTINCT PRCSGRP from PSAUTHPRCS where classid in
(select distinct classid from psroleclass where rolename in (select rolename from psroleuser where roleuser=)))

PeopleSoft Update Manager

PeopleSoft Update Manager is the new lifecycle management process for PeopleSoft 9.2 customers to use for all their application maintenance needs. Since this is a new process we have listed the key terms and definitions below. These terms are used throughout this home page as well as the posted documentation.

Term	Definition
PeopleSoft Update Manager	The new maintenance process for PeopleSoft 9.2.
PeopleSoft Update Manager PIA Application	The new Lifecycle Management application delivered in PeopleTools via the PeopleSoft Image.
PeopleSoft Update Image	A full PeopleSoft application environment delivered in an Oracle VirtualBox virtual machine. Use this image for all your PeopleSoft 9.2 application maintenance needs.
Patch	In the classic method of maintenance, a patch contains updates and fixes for your application.
Classic Patching Method	Prior to PeopleSoft 9.2 updates and fixes were released via patches on My Oracle Support. This patching method requires you to search for and apply each individual update and fix found on My Oracle Support.
PeopleSoft Release Patchset (PRP)	Patches that must be delivered between PI releases. These are found in and can be downloaded from My Oracle Support. These patches are applied to your current PeopleSoft Image, extracted from that image using the PUM PIA application and then applied to your target database using PeopleSoft Change Assistant. They are available after the first PeopleSoft Image is delivered for your application.
PUM-Enabled Applications	PUM-Enabled applications are built on PeopleTools 8.53 and are at PeopleSoft release level 9.2. They include the following applications: •   CRM 9.2 •   ELM 9.2 •   FSCM 9.2 •   HCM 9.2

PUM Images for Q4-2014 - HCM

HCM

PeopleSoft Update Image*	Date
HCM 9.2.008	August 19, 2014
HCM 9.2.009	October 13, 2014
HCM 9.2.010	December 8, 2014

 *As of HCM 9.2.006, the PeopleSoft Image and PeopleSoft Demo Image have been combined into one PeopleSoft Update Image. You can use the PeopleSoft Update Image for both your patching and demonstration purposes. 

HCM Contents

• PeopleSoft HCM basics
  • Types of tables
    • PS_PERSONAL_DATA, PS_JOB (Base Table)
    • PS_EARNINGS_TBL (Control Table)
    • PS_BENEFITS_VW (Views)
    • PS_EMPLOYEES (Reporting Tables)
    • PS_TREENODE (Application Tables)
    • SUB-RECORDS (Non Tables)
  • The core tables
  • PeopleSoft Processes
  • Using the Application Designer to find Table Definitions
    • Querying Tables
    • Cross-Referencing Fields
    • Cross-Referencing Panels and Tables
  • Dissecting a PeopleSoft Table
  
  
• Effective dating
  • Concepts Underlying Effective Dating
  • Basic Effective Date Sub-Query
  • Additional Criteria with a Sub-Query
  
  
• Understanding Control Tables
  • Structure of a Control Tables
  • Adding a Control Table to a Query
  • Using Effective Status
  
  
• Reporting tables
  • Reporting Tables Overview
  • The Employee Table
  • Benefits Plan data table
  • Benefits Personal Data Table
  • Enrollments

PeopleSoft Human Resources

• Personal Information
  • Personal Data
    • Identifying Employees
    • PeopleSoft Name Format
    • Resident Address and Phone
    • Non-Employees on Personal Data
    • Original Hire Date
    • Birth Information
    • Educational Status
    • Referrals
    • Citizenship Status
    • Personal Data for Other Countries
  • Understanding Effective Dated Personal Data
    • Name Differences
  • Using Social Security Numbers
    • The NID Table and Identifiers
    • National Ids
    • Querying the National ID
  
  
• The Job Tables
  • Identifying Jobs On PS_JOB
  • Storing History: Effective Sequence
    • Tracking Actions
  • Actions and Action Reasons
  • Linking Employees to Common Data
  • Human Resource Settings
  • Payroll Settings
    • Company and Paygroup
    • Tracking Hourly Employees and FTE
    • Other Payroll Identifiers
    • Compensations
  • Benefit Settings
    • Benefits Status
    • Benefits Base Rate
    • Custom Fields
    • FLSA Status
• Status
  • Employee Status Field
  • The Status Date
  • Long Term Disability Issue
  
  
• Compensation History


• Position/Job History


• Corporate Hierarchy


• Employment Information
  • Position Management Fields
  • Payroll Settings
  • Benefits administration flag
  
  
• Background Information

PeopleSoft Payroll

• Working with Payroll
  • Payroll Tables
  • Looking at a Specific Payroll Run
    • Paycheck table with Payroll Queries
    • Payroll Keys
    • Current Payroll
    • Pay Run Id
    • Payroll Dates
    • Multiple Jobs
    • Taking Deductions
  • General Ledger
  • The Payroll Process
    • Paysheets
    • Payroll Calculations
    • Payroll Confirmation
    • Identifying Payroll Process Achievements
  • Batch Processing Payroll Data
  
  
• The Paycheck
  • How do Paychecks Created
  • The Paycheck tables
    • Payroll keys
    • Alternate Keys
    • Paycheck Accounting
  • Different Kinds of Paychecks
  
  
• Earnings
  • Defining Earnings
    • Earn Codes
    • Defining an Earnings
  • Retrieving Earnings for an Employee
    • Pay Earnings Table
  • Feeding PeopleSoft
  
  
• Deductions
  • Getting to know Deductions
    • Deduction Code and Class
    • Defining Deduction
  • Enrolling Employees In Deductions
    • General Deductions Tables
    • Verifying Eligibility
  • Retrieving Deductions
    • The Pay Deduction Table
  
  
• Taxes
  • Tax Location
    • State and Locality
    • The Other States
    • Tax Location Code
  • Defining A Tax
    • Local Taxes
    • State and Federal Taxes
    • Tax Types/Rates
  • Employee Tax Elections
    • Special Withholding/Allowances
    • FICA and EIC
    • W-2 Settings
    • State Settings
    • Local Settings
  • Retrieving Taxes
  
  
• Garnishments
  • Setting up Garnishments
    • Employee Garnishment Specifications
  • Retrieving Garnishments
    • The Pay Garnish table
  • An Accounts Payable Interface
  
  
• Year End Processing
  • The W-2 Design
    • W-2 Parameters
    • Adding Data Using Delivered Reports
    • Multiple W-2s
  • Using W-2 Tables

PeopleSoft Benefits

• Navigating Benefits
  • Introductions To Benefits
    • Understanding Benefits Administration
  • Basic Benefits Terminology
    • Benefit Plans and Plan Type
    • Benefits Program
    • Defining a Benefit Program
    • Defining Plan and Plan Type Setting for a Program
    • Linking Plans
    • Specifying Benefit Plan Settings
    • Benefit Program Costs
  • Benefits Control Tables
    • Base Benefits Control Tables
    • Benefits Administration Control Tables
  • The Benefits Administration Process
    • BAS Activity / Manual Entry
    • On Demand Event Maintenance
  • Tracking PeopleSoft Benefits
    • Benefits Interfaces
    • Benefits Reports
  
  
• Health and life plan Elections
  • Elections
  • Dates
  • Determining the Current Enrollments
  • Locating a Terminations
  • Life Plans
  
  
• Dependents And Beneficiaries
  • How are Dependents and Beneficiaries Stored
  • Locating all dependents for a Participant
  • Determining Dependent Coverage
  
  
• Rates
  • Rate tables
  • Calculation rates

NA Payroll Configuration Tables

NA Payroll Configuration Tables
Foundation:
1. National ID Type
2. Name Suffix
3. Action
4. Action Reason
5. Banks
6. Form Table
7. Source Bank Accounts
8. Holiday Schedule
Earnings:
1. Earnings Table
2. Earnings Program Table
3. Special Accumulators Table
4. Review Special Accumulators
Deductions:
1. Deduction Table
2. General Deduction Table
3. Company General Deduction
4. Deduction Subset Table
5. Review Deduction Subsets
Processing:
1. Pay Group
2. Balance ID
3. Pay Run ID
4. Pay Calendars
5. Create Pay Calendars
6. Pay Message Table
7. Paycheck options
8. Rapid Entry Pay sheet
9. Configuration Online Printing
10. Final Check Program
11. Final Check Action / Reason
U.S. Taxes:
1. Tax Location Code.
2. Federal/State Tax Table
3. Company State Tax
4. State Tax Reciprocity
5. SWT Marital Status
6. Taxable Gross Definition.
7. Local Tax Table
8. Company Local Tax Table
9. Local Tax Reciprocity Table
10. Work Locality Reciprocity
Garnishment:
1. Disposable Earnings Definition
2. Disposable Earnings Defn – CAN
3. Rules Table
4. Rules Table – CAN
5. Proration Rules Definition
6. State Proration Rules
7. Clone Rules
8. Payee Table
RETROACTIVE PAY:
1. Retroactive Pay Program
2. Retroactive Pay Mass Request
BUSINESS PAYROLL SURVEY:
1. Reporting Unit Table
2. Business Information
3. Activity Table
Payroll for North America relies heavily on the information that is set up in the HCM foundation tables, such as :
• Installation
• SetID
• Location
• Department
• Company
• Frequency
• Job Code

Person Model

Types of Persons in PeopleSoft

There are three types of persons (Organizational relationship) can be created in PeopleSoft:

1.    Employee (PER_ORG = EMP)

A person who is hired to provide services to the organization and has a legal employee relationship with the organization.

2.    Contingent Worker (PER_ORG = CWR)

A person who provides services to the organization and who does not have a legal employee relationship with the organization.

3.    Person Of Interest (PER_ORG = POI)

A person who is neither an employee nor contingent worker but is of interest to the organization.

HCM Functional - Navigations

Basic Setup

Create SETID
Navigation:
Main Menu ->PeopleTools ->Utilities ->Administration ->TablesetID's

Create Business Unit
Navigation:
Main Menu ->Setup HRMS ->Foundation Tables ->Organisation ->Business Unit

Setup Location:
Navigation:
Main Menu ->Setup HRMS ->Foundation Tables ->Organisation ->Location

Add a company
Navigation:
Main Menu ->Setup HRMS ->Foundation Tables ->Organisation ->Company

Create an Establishment ID
Navigation:
Main Menu ->Setup HRMS ->Foundation Tables ->Organisation ->Establishment

Create Departments
Navigation:
Main Menu ->Setup HRMS ->Foundation Tables ->Organisation ->Departments

Setting the Business Unit options default
Navigation:
Main Menu ->Setup HRMS ->Foundation Tables ->Organisation ->Business Unit options default (Enter the company and Country details)

Create a pay Group Table:
Navigation:
Main Menu ->Setup HRMS ->Product Related ->Pay Roll ->Pay Group Table

Create a Job Code
Navigation:
Main Menu ->Setup HRMS ->Foundation Tables ->Job Attributes ->Job Code Table

Setting Org Defaults permission lists
Navigation:
Main Menu ->Setup HRMS ->Foundation Tables ->Organisation ->Org Defaults by permission Lists (Enter the Organisation related
data here)

Tree Manager:
Navigation:
Main Menu ->Tree Manager->Tree Utilites ->Tree Manager
->Create a new tree with the name DEPT_SECURITY
->Select the structure ID as DEPARTMENT
->Select the category as HR
->Click on ADD LEVEL and add the following below levels
1.Corporate
2.Business
3.Region
4.Company
5.Department
6.Division
7.Group
8.Unit
->Select the root node
->save it.
Note: In Business Options Default enter the details.

Security:
Navigation:
Main Menu ->Security ->Core Row Level Security ->Security by Permission Lists
->Select HCDPALL.Add ur business uint it and select the 011 as Security acess type.

Security by Dept Tree:
Navigation:
Setup HRMS ->Security ->Core Row level Security ->Security by Dept Tree
->Select HCDPALL.Add your SETID and Department ID in it.


Running the Process:
Navigation:
Setup HRMS ->Security ->Core Row Level Security->Refresh SJT Class ALL / Refresh Trans SJT Tables / Refresh SJT OPR class

Run all the above process

Buzz the Application
Navigation:
Setup HRMS ->Install -> Installation Table
->Change the SETID and Campany Name.
->Restart the services.

Adding a Person:
Navigation:
Main Menu ->Workforce Administration -> Personal Information ->Add a Person.

Workforce Administration : 

Purpose of Workforce Administration
• PeopleSoft Enterprise Human Resources Workforce Administration provides the foundation for your human resource management system. The data entered into the Workforce Administration business process is available to all of the Human Resources business processes as well as the other applications in the PeopleSoft Enterprise HRMS suite.
Administer Workforce Process
The Administer Workforce business process includes five basic steps-
• Setting up the basic codes and formats needed for the business  process
• Adding a person’s human resources record into the system
• Entering additional data into the record like prior work experience, company property,  emergency contact, visa information, driver license information, bank accounts   information etc
• Updating the record – changes to a person’s human resources record over time
• Viewing and reporting on worker data

SETUP HR DATA
The following slides describe the process.
1Setup data – Jobcode Table
A job code can have a number of positions. You use positions to track details on a particular job in a particular department or location. Positions usually have a one-to-one relationship with workers. Job codes have a one-to-many relationship with workers.
SetupHRMS > Foundation Tables > Job attributes > Jobcode table

2Setup data – National ID Type
Assign a national ID type to a country code and provide a default or a dummy national ID for a country to use when a person or applicant ID is unavailable.
Setup HRMS > Foundation Tables > Personal > National Id Type

3  Setup data – Location Table
Enter physical locations in your organization, such as corporate headquarters, branch offices, and remote sales offices.
Setup HRMS>Foundation Tables>Organization>Location

4 Other Setup data
• Company
– Use the Company component to enter information about a single company or multiple companies in the organization including the default pay group for the company. This value appears as the default on the Job Data pages for people in this company.
         Set Up HRMS > Foundation Tables > Organization > Company
• Establishment
– Establishment component defines the distinct physical places of business (establishments) within the company including the address information and regulatory reporting information.
         Set Up HRMS > Foundation Tables > Organization > Establishment
• Departments
– After defining company and location data for your enterprise, use the Departments component to define business entities in the organization.
         Set Up HRMS > Foundation Tables > Organization > Departments

• Personnel Actions and Reasons
– Changes to worker data resulting from such activities as promotions, transfers, terminations, salary increases, and leaves of absence can be tracked using these actions.
                       Set Up HRMS > Product Related > Workforce Administration > Actions
• Visa and work Permits
– Define visas, permits, and associated supporting documents that governments require for noncitizens to work in the countries where your organization does business.
                Set Up HRMS > Product Related > Workforce Administration > Visas/Permits
• Drivers License Type
– Enter the types of licenses that you are tracking.
     Set Up HRMS > Product Related > Workforce Administration > Driver's License Type

Person Model
• The Person Model describes the information captured about a person and how the person is related to the organization. This model includes the core tables that are used by all products that are directly related to a person and their organizational relationships in the Enterprise HCM system.
• A person can have one or more of these relationships at any one time, including multiple occurrences of the same relationship.
• Each distinct relationship that includes a Job Data record is uniquely identified by an employee record number.
 Note. The relationships of people of interest do not always include a Job Data record
CREATE PERSON RECORD
• The process is as follows………
Adding a Person
• When you bring a new person into your organization, create a record that includes this information:
– Personal data, such as name, address, personal relationships, and organizational relationships, such as employee or contingent worker.
– Job data, such as supervisor, department, job code, and compensation details.

• A Person is required to have at least one of the following records:
– PERSON - Contains the ID and a person’s static data (such as birth date and birthplace). Every person you enter will have one record in PERSON.
–  NAMES - Contains a person’s name data. You can capture multiple types of names and maintain history for each name type. Each person must have at least one row for the primary name type. The system uses the primary name throughout the database. The other name types are for informational use and you can add additional name types.
– A record for at least one Organizational Relationship - Each person must have at least one organizational relationship defined. The type of relationship depends on whether assignment data is required or not.
• When you first create a person in the system you need to enter:
– An EMPLID.
– A primary name.
– The date that the person’s record is available in the system which will default to the current date.
– An indication of the organizational relationship you will be creating.
• Workforce Administration > Personal information > Biographical > Add a Person

Differences between Employee and Contingent worker
• Employee – A person who is hired to provide services to the organization and having legal relationship with the organization.
            Identification: In Job data -- Action/Reason will be “HIRE”
• Contingent worker -- A person who is hired to provide services to the organization and does not have a legal relationship with the organization (A temporary or part-time worker, usually one working under contract for a fixed period or a specific project.)
         Identification: In Job data -- Action/Reason will be “Add contingent worker”

Organizational Relationships
• The organizational relationship defines the relationship or relationships that a person has with the organization. These may be worker or non-worker relationships and a single person can have many different relationships at the same time or over a lifetime.
• You will always create a new instance for the person when
– A new PER_ORG is used (EMP, CWR) that the person has never had
– Any new POI with a job is needed. POIs are always created as separate instances.
– Anytime you want the new assignment to be treated as a new hire (with its own hire and service dates) and you don’t want to reactivate a terminated assignment.
Organizational Relationships – Employee Type
•   The relationship of a person who is hired to provide services to a company on a regular basis in exchange for compensation and who does not provide these services as part of an independent business.
• An employee can work under a contract.
• The exact definition of what defines an employee is left to the customer since each country has different rules. You will want to make the determination based on your regulatory requirements.
• Each employee relationship must have a distinct EMPL_RCD.
Add Employment Instance
Workforce Admin > Job Information > Add Employment Instance


Organizational Relationships – Contingent Worker Type
•  The relationship of a person who provides services to another entity under terms specified in a contract on a non-permanent basis. Contingent workers include independent contractors, temporary workers, and leased workers.
• The exact definition of what defines a contingent worker is left to the customer since each country has different rules. You will want to make the determination based on your regulatory requirements.
• Each Contingent Worker’s relationship must have a distinct EMPL_RCD.
• The difference between the Employee and Contingent Worker job record is that the Provider ID can be entered in the Employment data of the Contingent Worker.
Add Contingent Worker instance
Workforce Admin > Job Information > Add Contingent Worker Instance


Organizational Relationships – Person of Interest Type
• A person who does not have an employment or a contingent worker relationship but who is still of interest to the organization. HCM has the need to track information on non-workforce people in many areas such as Cobra Participants, Pension Payees, GP Dependents, External Students and Instructors, and so on.
• Some POI relationships have job data records that are identified with a distinct employee record.
• Others (the ones that do not need JOB information) are identified by the POI_TYPE
Additional Assignment
• The additional assignment relationship concept allows us to create a new assignment for a person when they already have an existing instance and we do not want to count this new assignment as a ‘hire’. These assignments must be tied to an existing active instance of the same type (employee or contingent worker) and they will be ended if that instance is terminated. They may also be ended prior to the instance being terminated.
• Benefits of additional assignment relationships are:
–  The new assignment will not be counted as a ‘hire’ for regulatory reporting and date setting
–  The new assignment will be automatically terminated when the controlling instance is terminated.
–  You can allow the users who have access to the controlling instance to also have access to the additional assignment or vice versa with just a installation setting on security.
• The restriction of using the additional assignment relationships is:
– The additional assignment cannot remain active, or be reactivated if the controlling instance is in an inactive status.
Additional Assignment and Organizational Relationships
Organizational instances can be limited to one assignment (EMPL_RCD) or include multiple assignments, depending on your needs. For example, a person can have a single Employment Instance with a company, but as part of that employment instance, they have three separate assignments, each identified by different EMPL_RCD numbers. One of these EMPL_RCDs is identified as the controlling instance containing the overall dates. The others refer only to the particular assignment.

Additional Personal Information
Workforce admin > Personal Information > Driver License Data


Workforce admin > Personal Information > Prior Work Experience


Workforce admin > Personal Information > Citizenship


Person Health Related Information
Workforce Monitoring > Health and Safety > Record Medical Exam Results.

Adding the Benefits to the existing Employee:

Base Benefits:
Adding a Health Plan to an Existing Employee:

Provider / Vendor Tables:
Navigation:
Main Menu ->Setup HRMS ->Product Related ->Base Benefits ->Plans and Providers ->Provider/ Vendor Tables ->Vendor ID

Benefit Plan Type:
Navigation:
Main Menu ->Setup HRMS ->Product Related ->Base Benefits ->Plans and Providers ->Provider/ Vendor Tables ->Benefit Plan Type

Health Plan Table:
Navigation:
Main Menu ->Setup HRMS ->Product Related ->Plan Attributes ->Health Plan Table

Health Coverage Codes:
Navigation:
Main Menu ->Setup HRMS ->Product Related ->Plan Attributes ->Health Coverage Codes

Rates and Rules:
Navigation:
Main Menu ->Setup HRMS ->Product Related ->Base Benefits ->Rates and Rules ->Benefit Rates.

Deduction Code:
Navigation:
Main Menu ->Setup HRMS ->Product Related ->Payroll Interface ->Deductions ->Deduction Tables.

Cloning the Benefit Program Utility:
Navigation:
Main Menu ->Setup HRMS ->Product Related ->Base Benefits ->Program Structure ->Benefit Program clone Utility.

->Select required Basic benefits and clone it.

Benefit Program Table:
Navigation:
Main Menu ->Setup HRMS ->Product Related ->Base Benefits ->Program Structure ->Benefit Program Table.

Adding the Benefit to the Employee:
Navigation:
Main Menu ->Workforce Administration ->Job Data ->Select the EMPLID ->Click on the Benefit program participation link ->Add the benefit program.

PeopleTools Tables / Metadata tables.

PeopleTools Tables / Metadata tables.

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

5. 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 Reference

PSDBFLDLABL

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 Definition

PSPROJECTITEM.UPGRADEACTION

0 = Copy
1 = Delete
2 = None
3 = CopyProp

PSPROJECTITEM.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 Table

PSDBFIELD

PSDBFIELD.FIELDTYPE

0 = Character
1 = Long Character
2 = Number
3 = Signed Number
4 = Date
5 = Time
6 = Date Time
8 = Image
9 = Image Reference

PSPNLFIELD

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

PSSQLDEFN

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    = Component

PSPRUHTABPGLT

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 Table

PSRECFIELD

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 PSAUTHITEM

PSAUTHBUSCOMP

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)