People-Tools 8.51 - New UI Enhancements.

Get glimpse of UI enhancements in People Tools 8.51.  Best just got better.


Peoplesoft Test Framework Demo links

People Tools 8.51 has new interesting feature – Test framework as part of people tools objects.
It will be an interesting feature for Testing/Functional and Development teams.
Find out how it works - demo videos.

1) Part 1
2) Part 2
3) Part 3


3rd Party Batch Scheduler for PeopleSoft

Organizations that manage critical applications such as HR or their Supply Chain with Peoplesoft run many jobs both within the Process Scheduler as well as outside of the Peoplesoft environment. The scheduling facilities included in Peoplesoft are rudimentary and are not capable of managing workload outside of Peoplesoft making it challenging to get an end-to-end business view or to avoid manual processes that can cause outages.

ERP systems are the mainstream systems in most of the organization. Different ERP system and other system in the organization has to co-exist in the organization to support the business processes.

Business Need

  • Integrate the PeopleSoft production environment with other systems
  • Eliminate errors and processing delays
  • Enhance the PeopleSoft built-in scheduler capabilities 
  • broad range of platform and application support integrate different system in the organization.
In an organization multiple batch process across different applications needs to scheduled in particular order. manage, monitor  and analyze the impact of different batch process on business process is very essential.
Some of the companies use Crontab - in the unix to schedule the jobs in the PeopleSoft and other applications. It will be common place to analyze and manage scheduling. This is  platform dependent and doesn't support all the process types.

There are few Scheduling softwares - Which can manage the different batch processes across different platform and  applications and server as a common point to manage and monitor the different business processes. Workflow of different business process can be created with these softwares/applications.
job dependencies, workload balancing, and event-based job execution can be managed easily.

3 - Third party Schedulers that are popularly used with Peoplesoft ERP systems.
1) Unicenter Autosys
2) Control - M
3) Tivoil Work schedular

There are Peoplesoft adapters so that these schedulers can connect to Peoplesoft system.

The Adapter connects to the PeopleSoft Enterprise database and collects the data it needs to define the task. The Adapter submits the task, waits for Oracle’s PeopleSoft Enterprise Process Scheduler to finish running the task, and retrieves the status changes in the PeopleSoft Enterprise database. The status can be viewed from the Schedular Operator Console and the PeopleSoft Enterprise Process Monitor.

The integration is accomplished by inserting PeopleSoft Enterprise job or process information into various database tables that emulate a submission through the PeopleTools GUI. Once the PeopleSoft Enterprise work has been scheduled, it is monitored to completion, reflecting status changes back to the Scheduler.


PeopleSoft Tracing & Logging

Tracing online People Code and SQL

1. Select Trace at the login screen or add “&trace=Y” to the login URL OR - To set trace flags, click here (use this link.

2. Choose the options in the tracing screen and login.
3. Perform the actions you wish to record.
4. Once complete logout and retrieve the file from the /appserv//LOGS directory. 
The trace file name will include your login name.

Tracing online PeopleCode and SQL (Alternative)

1. Login to PeopleSoft and navigate to PeopleTools > Debug.
2. Choose the options and save.
3. Perform the actions you wish to record.
4. Once complete logout and retrieve the file from the
/appserv//LOGS directory. The trace
file name will include your login name.

Application Engine Tracing
1. Open the process definition
2. Select the overrides tab.
3. Select Append on the parameters field.
5. Save the record.

SQR Tracing
1. Open the process definition
2. Select the overrides tab.
3. Select Append on the parameters field.
4. Enter –DEBUG[ABC]… (replace ABC with the debug letters
provided in the sqr, sqc source).
5. Save the record.

Cobol Tracing
1. Open the Cobol SQL process type for the platform you are running on.
2. Enter 255 in the command line field between two forward slashes “//”.
3. Save the record.
4. Run the process and retrieve the COBOL sqltrace from the process monitor.

Tracing Settings
1. Both the process scheduler (psprcs.cfg) and application server (psappsrv.cfg) config files allow for permanent tracing settings as well as setting up trace Masks. psprcs.cfg - /appserv/prcs// psappsrv.cfg - /appserv//

2. Trace Masks are setup to limit the amount of tracing allowed when a user wants to trace through the front end package (see all methods

3. LogFence allows for lower and higher detail tracing with regards to application server standard logging. This is ideal for identifying problems on the application server.

4. To enable the trace link in the Sign on page.
    People-tools > Web-profile.
   Web profile > Debugging Tab -> Check the Option - Show Trace Link at Signon


How to hide & unhide the grid from the page by using people code?

How to hide & unhide the grid from the page by using people code?

In the given example, user wants to hide and unhide the grid from the page by selecting the check box option on the page. If the check box is checked the grid should be visible else hide the grid & delete the values present in the grid.The check box is at level 0 & the grid is at level 1.

Function HideAllRows() can be used to hide the grid, & function ShowAllRows() can be used to unhide the grid.

HideAllRows hides all rows of the rowset.
For the given example, write the below people code at field change of check box.

1) Hide the grid.

   &Rowset0 = GetLevel0();
   &Row0 = &Rowset0(1);
   &Rowset1 = &Row0.GetRowset(Scroll.);

2) Delete the data from the grid

   &Rowset0 = GetLevel0();
   &Row0 = &Rowset0(1);
   &Rowset1 = &Row0.GetRowset(Scroll.);
   For &i = &Rowset1.ActiveRowCount To 1 Step - 1


If the data is present in the component buffer & not present in the data base, then use function flush() to delete the data from the buffer.

   &Rowset0 = GetLevel0();
   &Row0 = &Rowset0(1);
   &Rowset1 = &Row0.GetRowset(Scroll.);

3) Unhide the grid

   &Rowset0 = GetLevel0();
   &Row0 = &Rowset0(1);
   &Rowset1 = &Row0.GetRowset(Scroll.);


People-Tools 8.51 is available now.

People-tools 8.51 is available now. It has a lot of interesting features as explained in my earlier posts.

Some of the resources links for 8.51

1) People-books - Finally a new look to People-books - Not many notable changes in the UI.
Hosted peoplebooks.

2) Peopletools Advisor 8.51 Advisor Webcast

3) Documentation on 8.51 release. ( login required)

Some of features to look forward.

1) Test-frame work.
2) Smart Navigation.
3) UPK Help Embedding.
4) Advanced Integrations.
5) Peoplesoft Work center framework - Role-based functional portals.

It will be available fully in 2010 Q3 and 8.52 will be available for 2011 Q3.

What are the features in your wishlist for People-tools - 8.52 ?


Date Validation - Oracle SQL

In Peoplesoft where we read data from the files, especially for inbound interface , conversion and one time data load. Date is usually read as character field in different format and converted to date. We need to validate the date for further processing. It will be good if we can validate date field in SQL (set processing) than the validating using people code (row by row). Oracle doesn't have very useful function IsDate() not sure why.

Lets see how we can do set processing for the date validation.

1) Regular expression came to my mind to check the pattern and validate the date easily.

Select 'X' from dual
Where REGEXP_LIKE ('1999-10-10','^(1920)\d\d[- /.](0[1-9]1[012])[- /.](0[1-9][12][0-9]3[01])$')
 This is simple regular expression which matches only the date format. It doesn't check for leap years and 30,31 days for all the months.

2) Select 'X' from dual
Where NOT REGEXP_LIKE ('2009-2-29','^((((19|20)(([02468][048])([13579][26]))-02-29))((20[0-9][0-9])(19[0-9][0-9]))-((((0[1-9])(1[0-2]))-((0[1-9])(1\d)(2[0-8])))((((0[13578])(1[02]))-31)(((0[1,3-9])(1[0-2]))-(2930)))))$')

This regular expression is quite big difficult right every time correctly. Need to copy paste where ever it is requried.

3) if the regular expression is getting difficult, then can create a PL/SQL function and use that function in the sql to validate.
we can use the above sql itself in the function. or use the following.

function IsDate (str long) return boolean is the_Date date;
the_date := to_date(str,'dd-mon-yyyy'); -- note the correction to date mask from what you had below
RETURN true; -- conversion success
when others then RETURN false; -- conversion failed
In the Peoplesoft Implementation project we can create such function once and can be used in many SQL/Peoplecode/AE's. It is difficult to maintain this procedure in different instances like Dev,FIT,SIT,UAT,PPROD,PROD, but is it worth the effort.


Special SQL operators make the SQL simple to understand & write.

ALL, SOME  and ANY :- These operators are not widely used in the SQL, these keywords exists in both Oracle and MS SQL. These keywords can be very useful in developing many peoplesoft realated query. It makes the query simple to write and understand.

Eg:- If you want the list of employees who are terminated in all the assignments (employee records).

 1) Select EMPLID from PS_PERSON A
Where 'I' = ALL (Select HR_STATUS from PS_CURRENT_JOB J

2) Select EMPLID from PS_PERSON A
Where 'A' <> ANY (Select HR_STATUS from PS_CURRENT_JOB J

ANY or SOME: Compares a value to each value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Evaluates to FALSE if the query returns no rows.
ALL: Compares a value to every value in a list or returned by a query. Must be preceded by =, !=, >, < , <=, >=. Evaluates to TRUE if the query returns no rows.

It is doesn't do anything new, it is just user friendly wrap keywords. The optimizer expands a condition that uses the ALL comparison operator followed by a parenthesized list of values into an equivalent condition that uses equality comparison operators and AND logical operators.The optimizer transforms a condition that uses the ANY or SOME operator followed by a sub query into a condition containing the EXISTS operator and a correlated sub query.

Due to this behaviour of the optimizer in Oracle SQL, we need to analyze sql for the performance before we take decision to use it in the production. These constructs have been useful for me in doing the data analysis and creating the quick query to get list of the employee based on certain condition.


Forgot password link setup in Peopleosoft.

Many customers want forgot password option and email the password option. Based on the hint questions user can be authorized and get the password delivered to their email.

Below Embedded Scribd document contains the steps to create the forgot password setup in peoplesoft.


Type Ahead feature in the Peoplesoft 9.1/Ptools 8.50

In PeopleSoft 9.1/ PeopleTools 8.50, they have introduced a new feature, as you type through a field it tries to provide possible values for the field.

Yes you can certainly disable the type ahead feature either globally (at User level) or at the Field level (all Users).

My Personalization > Navigation Personalization > Type ahead = NO , default is YES


SQL Loader - Basics

Many times we need to load the data in to Peoplesoft Loading tables or other tables for conversion  and interface and bulk load for minor data migrations.

SQL Loader is very fast and easy way for loading the data. It is not used much in real-time with productions instance, but with BPEL you can automate the loading of data in to tables in real-time. SQL Loader fast and very efficient.
There is some of the Basics of SQL Loader.

SQL Loader:

               SQL*Loader is the primary method for quickly populating Oracle tables with data from external files. It has a powerful data-parsing engine that puts little limitation on the format of the data in the datafile. SQL*Loader is invoked when you specify the sqlldr command.

SQL*Loader can be used to do the following:
  • Load data across a network. This means that a SQL*Loader client can be run on a different system from the one that is running the SQL*Loader server.
  • Load data from multiple datafiles during the same load session
  • Load data into multiple tables during the same load session
  • Specify the character set of the data
  • Selectively load data
  • Load data from disk, tape, or named pipe
  • Specify the character set of the data
  • Generate sophisticated error reports, which greatly aid troubleshooting
  • Load arbitrarily complex object-relational data
  • Use either conventional or direct path loading.
SQL*Loader Control File:            The control file is a text file written in a language that SQL*Loader understands. The control file tells SQL*Loader where to find the data, how to parse and interpret the data, and where to insert the data.


Magic of Oracle Regular Expression Functions

Oracle SQL has Regular Expression support from 2003. Many people doesn't know about it and has not been used by many of the Peoplesoft developers and legacy SQR and Peoplecode.

These functions can simplify a lot of pattern matching work in the SQL and AE / SQR program.

REGEXP_LIKE Determine whether pattern matches
REGEXP_SUBSTR Determine what string matches the pattern
REGEXP_INSTR Determine where the match occurred in the string
REGEXP_REPLACE Search and replace a pattern

contains the details of using the regular expression iPublish Postn Oracle SQL.



Can be used to select fields containing only alpha numeric character.

Pattern matching for Phone number , Postal code , Email id , National Identifier , Employee ID , Name , Addresss , Bank account number , credit card number , different identifier.


Usefull Peoplesoft Viewlets

Very usefull viewlets and must watch for Peoplesoft Techies.
Don't miss the Security , Webservices & Intergration broker , Reporting


Avoid accessing in-correct run-control parameters in the AE programs

Run control parameters are accessed at the first step in all the AE programs. It will be stored in AET record and will be processed across different steps and sections. If the run control parameters are stored in the multiple records with parent child relations ships. Eg:- list of the job codes , Departments & Business units. Since we cannot store multiple rows in the AET/State record, we access these records in the sql directly where it is required to access the parameters. It method works fine until same user runs the same process with same run control id again with different run-control parameters before the earlier process completes its execution.

Problems :- first process will end up using the incorrect parameters that is assigned for the second process.
Result can be unexpected, based on the run-control parameters changed by the user. Accessing the run control parameters in the AE program other than beginning step can always create problem.

Solution :-
User training can be one of the solution, but it will be better if the program can handle it without any additional user training.

Create an additional table with keys OPRID, RUN_CNTRL_ID  and other keys and fields specific to the run-control parameters of the AE process. In the beginning step, insert all the multi-row runcontrol parameters in to this record and level zero parameters in the AET record. In the process, where ever you need to use the multi-row runcontrol parameters use the new record created , instead of the actual runcontrol table used on the page/component. Delete these rows from this new record at the end of the process. Access and deletion of the rows from the table should be based on the key fields OPRID,RUN_CNTRL_ID.


PeopleTools Tables Reference

There are 3000 PeopleTools tables and view (as defined by PSOBJGROUP). Tackling all of them manually would be a monumental task.

A brief discription of the of many Peopletools tables can be found in the link below. - Co-Operative PeopleTools Reference

Hope this will be very usefull to many.


Launching JDeveloper XSL Mapper from Application Designer/Application Engine

JDeveloper is a tool that can be integrated to AppDesigner for a graphical XSLT mapper with AE transform programs.

Here are the pre-reqs for using XSL Mapper. If any of the pre-reqs have not been met it won’t launch
1. Configure the gateway by loading in all target connectors via the Gateway component, and configuring the IntegrationGateway.Properties file. Specifically, you must set the value for the DEFAULT APP SERVER in this file

2. Set the value for the TARGET LOCATION field on the PS Service Configuration page. The value of this field should point to the location of the PeopleSoftServiceListeningConnector on the gateway

3. Specify the path the JDeveloper Home in the configuration manager (on the windows developer client)

4. Build schemas for all both the source and target messages to be used by the mapper

5. Grant access to the PTPT1200 permission list.

Mapper Jdeveloper to application engine as below.

1) Application Engine :- Program properties - Transform only.
2) Action - XLST - Graphical Mapper - Yes.
3) The integration is setup from the Config Manager
    Crystal/Bus. Interlink/JDeveloper


PeopleTools 8.51 is getting ready to surprise you with few interesting features.

Many Peoplesoft System is yet to get promoted to 8.50 Tools version. Not many companies has full explored the features of 8.50 (web 2.0) and 9.1 applicaitons. Oracle already has 8.51 RVP. These changes are manly related to user experience and productivity improvement. It seems the quick changes to tools version is driven from the new competing applications which has fresh new application experience for the users.

• PeopleSoft Smart Navigation
• PeopleSoft Test Framework

These are the 2 Peoplesoft main changes which can change the way we use the PeopleSoft applications and implement/support projects are run.
PeopleSoft Smart Navigation

Hierarchical application data can be incorporated into the menu structure, allowing a user to
access application functions directly related to data such as the company org chart or any other data tree.
Contextual menus can be associated with data on a PeopleSoft application page, allowing a
user to take action directly from data on the page without having to navigate the main menu
structure and search again for the data they need.

A user’s search results are temporarily stored directly in the menu structure, allowing a user to
navigate from one record to another without having to re-execute a search – even if they have
moved away from the page.

I was trying to imagine how this look like in the PeopleSoft and what is the driving factor for the quick menu update. Menu changes were done in earlier version 8.50.

It looks like it this change is driven by Workday. Rich user experience in Workday is one of driving factor for customers to choose it.
It seems PeopleSoft like to incorporate these changes in the 8.51 Tools version.

Following can give idea more about that.


Cannot access incorrect password

If you look under your PS_HOME ,

PS_HOME \webserv\peoplesoft\applications\peoplesoft\PSIGW\WEB-INF

There is a file called gatewayUserProfile.xml , this holds the UserID/Password

If you need to change this then you can change it in this file . Note you will need to encrypt the password using the pscipher utility.


Query Administration not showing queries that have been run

In order to enable Query Administration statistics tracking, navigate to PeopleTools > Utilities > Administration > Query Administration. Click on the Settings tab. Make sure the check box for "Run Query Statistics" has been selected. This setting is saved automatically. Once this check box has been enabled, you will be able to track query statistics in Query Administration.


Enabling this caused table locking issue in some earlier tools version [8.43.xx,8.44.xx] [Reference: Support URL]. Kindly verify before using this option.


PRCSYSPURGE process deleting rows from other environment


Recently cloned an environment from PROD & surprised to see PRCSYSPURGE process deleting rows from other environment [PROD].


On refreshing one environment (Development) with other database (Production) then that means that in PS_CDM_LIST table the field OUTPUTDIR still contains the database name for Production. Changing this helped to resolve the issue.


Send Master Utility

Just happened to read about a PeopleSoft - Send Master Utility

The Send Master utility enables you to test PeopleSoft Integration Broker service operation interactions with PeopleSoft and third-party web servers, application servers, and integration gateways. It can test listening connector functionality, target connector functionality, connector introspection and transactions.

Send Master enables you to post any data format, including the PeopleSoft Multipurpose Internet Mail Extensions (MIME) message format, to web and application servers over HTTP and HTTPS. You can also use Send Master to simultaneously test groups of different types of service operations, as well as stress test your system.

Send Master also enables you to perform Get functions and to ping application messaging gateways and third-party servers.

Send Master is installed with the PeopleSoft Pure Internet Architecture on Windows and UNIX systems and is delivered as part of the Integration Broker Connector SDK.

Send Master is also delivered as a Windows stand-alone batch file. The stand alone version enables you to use the utility without having to install an integration gateway.


Weblogic - Error while booting - WLS_ADMINSERVER000000.DAT

Error: While booting the Web-logic Server received an error..

The persistent store "_WLS_AdminServer" could not be deployed: [Store:280021]There was an error while opening the file store file "_WLS_ADMINSERVER000000.DAT"


To start the server again:
1. find -name "*.DAT" files in "<**>/domains>/<**>" directory (in our case!) and move or remove them
2. remove "EmbeddedLDAP.lok" and "AdminServer.lok" (maybe some more)

Thanks to,

Note: May not be applicable for all version.,


PSORATimestampConversion Errors

While performing tools only upgrade to 8.50. PSORATimestampConversion.bat failed.,


Including the classes12.jar in the CLASSPATH resovled the issue.

D:\daberk\psoft\pt850\fin91\utility>echo %CLASSPATH%


While adding new report definition , Data source type is by default PS Query and not editable

While adding new report definition ,Data source type is by default PSQuery and not editable.

There are 2 roles for the XMLP.
XMLP Power User
XMLP Report Developer

XMLP Power User - It doesn't have permission to create report with data source type other than PS Query. XMLP Report Developer - Will enable to change the Data source type to other than PS Query.
It looks strange why XMLP Power User doesn't have the permission to create report definiation with data source type other than PSQuery.


PS Grid Customize option internals

PS uses ‘PSUSEROBJTYPE’ data dictionary to store the user’s customization.

PTCUSTOMFORMAT stores the data in the below format and pipe symbol as the delimiter.

Format explanation

Header delimiter hide/unide delimiter Freeze/UnFreeze delimiter Sort Asc/Desc delimiter Sorting order# columnnumber Footer

Delimiter = Pipe symbol
Header = 0
hide/unide = 1 – Hide Field , 0 – Unhide Field
Freeze/UnFreeze = 1 – Freeze Field, 0 – Unfreeze Field
Sort Asc/Desc = No sort order, 1 – Sort by ascending, 2 – Sort by Descending
Sorting order = 0/1/2…
Column specifier = # column number
Footer = 0#

Suppose a grid as 7 columns, when the user customizes the grid, below is the sample format.

1. Department level1 code is made hidden.
2. Sort order and Department level2 code are in Sort order list and department level 2 code is set as descending

The format would be


Peoplesoft Updates Details

Peoplesoft patches and bundle projects applied to the Instance can be view in the
table ps_maintenance_log.

This information can be view in online in PIA. In the 3 components

Peopletools > Utilites > Administration > Updates - View all
Peopletools > Utilites > Administration > Updates by Release Label
Peopletools > Utilites > Administration > Updates by Update ID


Creating Related Edit Fields

Related display in people soft is used to display the description/detail of the ID or codes. User has to enter the ID/code or search by description in the prompt page. If the user is familiar with Descrtiption rather than ID or code and user want to select based on that, then we can use Related edit field.

Related edits enable users to enter descriptive values instead of code values and enable prompting on those descriptive values. Changing the value in the control field updates the related edit field, and changing the value in the related edit field updates the value in the control field. If appropriate, the control field can be display-only or even invisible to hide code values from the user.

If user wants to search employee by name rather then EMPLID , then user can key in name and search instead of using the EMPLID.At design time, you set up a related edit field in the same way that you create a related display field, except that you clear the Display Only check box in the Field Use Options group box so that the field is active at runtime

1) Edits, including any assigned PeopleCode, are run on the control field. PeopleCode is not run for the related edit field.
2) Edits, including any assigned PeopleCode, are run on the control field. PeopleCode is not run for the related edit field.
3) The related edit field must be an alternate key in the prompt table.
4) If the hidden control field that you select is a required field, PeopleSoft Pure Internet Architecture changes the related edit field into a required field as well.



How to avoid the trimming of leading zeros in XML Publisher - Excel output

When the XML Publisher creates the Excel output file with fields having leading Zero (Eg:- Position Number), when the ouput file is opend in the Excel (not in the web browser), the leading zeros in the will be automatically trimed by the Excel. Excel treat this as number, we need force excel to treat this value as character and not as numberic value.

During XML template design, Instead of the using the tag < ? fld_POSITION_NUMBER ? >
use  ="< ? fld_POSITION_NUMBER ? > ". It wraps the value with double quotes. Now Excel treat value as character and doesn't trim the leading zeors of value.

Note:- space between < and ? and tag name is not requried.


How to enable logging for the IB Messages

In file

set the property ig.log.level under logging section.

The following are the Gateway Log Levels

# Level Value

# -------------------------- -----

# SUPPRESS ANY LOGGING -100 {Suppresses any Message Logs}

# LANGUAGE_EXCEPTION -1 {Logs language exceptions only}

# STANDARD_GATEWAY_EXCEPTION 1 {Logs language and standard}

# WARNING 2 {Logs all errors & warnings}

# IMPORTANT_INFORMATION 3 {Logs errors,warnings and important Information.(Default)}

# STANDARD_INFORMATION 4 {Logs errors,warnings, important and standard information}

# LOW_IMPORTANCE_INFORMATION 5 {Logs errors,warnings important, standard and low importance information}
Name of the file used to output message/error logs can be set in this file..for the parameter
ig.messageLog.filename - msgLog.html
ig.errorLog.filename - errorLog.html


AWE Workflow - Escalation emails

Peoplesoft AWE Workflows - Escalation process

AWE Workflows

In the “setup process definition “ you define the timeout for escalation. The ESCALATIONS Event ID will make sure to escalate if not approved within the “timeout” period.

PTAF_NEM - Is the process that needs to be run for the escalations in the approval workflow.

The PTAF_NEM process, when run kicks off 3 application engine programs one for each of the following events ID

Approval Activity Email
Receipt Notifications

The AWE Notification & Escalation process, PTAF_NEM, only sends out one ( 1 ) notification, however, multiple actions can be performed at each timeout. i.e. Notify and Reassign after 2 hours. Or Notify after 1 hour and Notify and Advance after 24 hours.

What cannot be done is to set the Timeout to 1 hour and expect to get a notification every hour that an action has not been taken.

EMP_SERVLET - URL needs to be configured to send the correct url link in the escalation notification emails.


Incorrect Termination Date in the Job data component - HCM Peoplesoft

Termination date is auto-populated in the peoplesoft when an employee is terminated in the Job data component. Termination date is suppose to be the termination action EFFDT - 1.

Termination date is configured to be popluated as JOB.EFFDT - 1 in the Action table component.
Termination date will not populated correctly due to a delivered bug in HCM 9.0 as well as HCM 9.1.

When you insert new row in the job data component effective date is defaulted to current date or defaulted from the previous row if it is a future date. Select the action as Termination and look at the termination date it is populated correctly with JOB.EFFDT - 1. Now change the EFFDT field it will not refresh the effdt to JOB.EFFDT - 1 value. It lets you save the component with that information. Any logic based on the termination date will fail.

Delivered code fires on the field change of the Action field alone and not on the EFFDT field. This creates the problem of incorrect termination date.

screen shot from HCM 9.1 peopletools 8.50


Customizing the JOB_DATA (Job data) component in HCM Peoplesoft.

JOB_DATA (Job Data) - It is one of most important component in the HCM which maintains the employees job history details. It is also one of the complex components which have a lot of business logic involved in it.

There are 5 other components which are very similar to the JOB_DATA component.

JOB_DATA_EMP   - Add employee Instance - Employee Hire component.
JOB_DATA_POI     - Add POI Instance - Hire Person of Interest component.
JOB_DATA_CWR  - Add CWR -  Hire Contingent Worker.
JOB_DATA_CONCUR  - Add concurrent Job.

In an implementation we customize delivered objects in PeopleSoft. Customizing the Job data component can be very painful as it creates a lot of maintainace  issues and lead to additional customizations. A lot of points needs to taken to consideration while any customizations are made to Job data component. Modify the Job record is a very bad customization, avoid modfiy the delivered records.

1) Any new custom record added to JOB_DATA component to capture the additional data on the Job data transactions then you might also need to add that record to other components similar to Job data as mentioned above. Additional customizations.

2) Any additional fields and records in the component will affect the component structure and it will impact directly component interface structure. We need to rebuild all the delivered component Interfaces.
b) HCR_JOB_DATA_SRV  - Component interface is used in Mass Update process (HR_MASSUPD) which  is CI based services for Mass changes to the employees  like Mass Salary change (periodic salary hike).
This process will be affected based on the way custom record is used in the job data component.

Component Interface is also used in the Automated Step increment process (HR_CMP007) which increments the step of the employee. Any change to the job data component will impact this process as well.

3) If your design handles any CI operations done by the above processes as new custom record is the child record of the parent record job then it will create problem if you use another Step increment process (CMP107) which is an SQR process which doesn't insert into custom record, which result into the improper component buffer in the job data component. Additional customization to custom record or

4) WORKFORCE_SYNC - Delivered IB message from HCM to ELM and CRM. This message is published in the job data (and related components) in save post change event. If auto select option is off for the new custom child record scroll in the job data. When there is only change to the fields of the custom record it sends out a blank message (blank XML),which creates fatal error in the CRM.

5) Any changes in the bundles and fixes from peoplesoft to job data components and its Peoplecode (& similar components)and its pages will lead to reapplying the customizations to these components.

To minimize the impact of customization.


Instance Variable in Peoplecode - Application packages

It might be confusing for many of them, how is instance variables different from the Private and Public variables.

Myth :- specific object (instance of a class) would only have access to its own instance variables.

Instance variable is very different to Private or Public. Infact it is not right comparison of Instance with Private and Public.

Instance variables equal to Static variables in the other Object oriented programing language (like C++ and Java).

Instance - Defines the scope of the variable where as private and public key words define access levels/Visiblity of the variables. If variable is not defined as Instance then SCOPE of the variable is Local by defult (Local to the created object of the class).

Instance - scope of the variable is across the objects of the same class and scope of the Instance variable is at the class level.

The variables are created when the first object of the class is creted and will be present till last object of the same class is present in the memory.

If the Instance variable is declared as Private it is accessed the way it is accessed i,e only with in the class methods. if variable is declared as Public it is accessed the way you access other public variables. (i,e both within the class and outside of the class).

Simple comparison that can be done..(just for understanding purpose).

Component variables has scope within the component similarly instance variables has scope at the class level across the objects of the same class.

Big question arise when do we need these kind of variables.

1) if you want to count how many objects of the class was created you increment this variable in the constructor of the class.
2) If you have constant value that needs to shared across the objects of the class then you can use the instance variables.
3) Shared variable across the objects like component variable (across the different events of the component).


Dynamic Roles in Peoplesoft

Why Dynamic roles ?

1) The assignment of roles to User Profiles based on your business rules.
2) These business rules run against system(s) to assign PeopleSoft access.
3) PeopleSoft security changes in an automatically.
4) The dynamic role rule process removes and grants access to User Profiles

PeopleTools > Security > Permissions & Roles >Roles  (Dynamic Member Tab/Page).

There are three ways can use to execute your rules to find the dynamic role users.
o PS/Query
o LDAP Plug-in
o PeopleCode
1) Access is removed or granted based on the User Profile IDs retrieved by the query.
2) Logic to select the dynamic role users will be present in the SQL of the Query.
Note: -
a) Use PSOPRALIAS_VW or PSOPRALIAS record to create the dynamic role queries.
    PSOPRALIAS - Used to store ID values corresponding to various ID types. For example, stores  EMPLID for Employee type, CUST_ID for customer ID. It will be easy to join the tables based on the type of the Userid.
b) Don't use any Bind variables in the role queries. They are not designed for resloving the bind variables.
c) Use Disntict caluse in the SQL to make you always retrive uniques id only. Any duplicate will the the dynamic role asssignement.
 1) Access is removed or granted based on the User Profile IDs pushed in to the system array variable 
 2) This is used when logic is complicated and cannot be retried from the Query.


Peoplesoft AWE Workflow configuration DMS scripts

Peoplesoft AWE Workflow configuration and setup tables. Ver 9.0

User the following scripts to move the AWE configuration / setup data from the one instance to another instance.

Note :- add where clause if you need to move only configuration specific to particular AWE transactions.

Export script

-- "Register Transaction"

-- "Approvals > Workflow Transaction "

-- "Configure Transactions"

-- "Setup Process Definitions"


Peoplesoft HCM Process - Scheduled process.

HR_PERSDATA Application Engine - Updates the PERSONAL_DATA snapshot table with future dated entries as they become effective.

NAME_DISPLAY Application Engine - Refreshes the NAME_DISPLAY and NAME_FORMAL fields in all records with the NAME_GBL_SBR. Use when the criteria used to change these fields is changed.

PER099 Application Engine - Fill EMPLOYEES Table.

PTAF_NEM Application Engine - PTAF_NEM - Approval Framework Escalation App Engine.           AWE Workflow escalation process.

SCRTY_OPRCLS Application Engine - SCRTY_OPRCLS - Updates the SJT_OPR_CLS security Join Table.

SCRTY_SJTDLY Application Engine -  Nightly update for SJT Records -  Update of all effdt rows that have become current as of this data.

HRS_AM Application Engine - Applicant Search Auto-Match -Recruiter Applicant Search auto-match process.

HRS_DUPE_PRC Application Engine - Find Applicant Duplicates - Finds applicant duplicates given certain criteria.

HRS_JOB_AGNT Application Engine - Job Agent - Run the Job Posting Search Agent process.

HRS_JSCH_IDX Application Engine -Verity based Job Posting Index build process.

HRS035 SQR Report - Application Analysis (USA).

PER506 SQR Report - Department Tbl & Departmental - This report lists any departments that you have created in the Department Tree but have not yet added to the departmental security tree, It also lists any changes in departments since the effective date of the current security tree.

PER900 SQR Report - Core HR Data Integrity Audit. - Audit of the CORE HR Tables for Data Relationship Integrity.

PSBARUN - Cobal Job - For the Benefit Administration - scheduled to run twice each night with some custom pre and post processes.


F1 Context Help for Peoplesoft Application Designer

Access Peoplebooks from Applicaiton Desinger.
Context Help for Application designer.

Follow the setps to configure F1 - context help for the application designer.
Press F1 in the application designer to get the help based on the URL configuration below.
It can take to the peoplebooks based on the context.

Navigate to PeopleTools > Utilities > Administration > PeopleTools Options

Under the help option you can set the URL to the People books.

URL needs to be in the following format.

%CONTEXT_ID% is the object name or context ID of the currently displaying page or dialog box.
%LANG_CD% is the three-letter language code for the user's preferred language.

Cntrl+F1 - can be used as alternative help URL to the website on the internet or the company's internal website.


Tools/Utilites & Applications for Peoplesoft Development.

Productivity tools for the Peoplesoft Development.

1) SQL-Formatter :- The Application engine sql statements invloving are not formatted well for   understanding it.

SQL-formatter comes in handy to format the SQL. It helps in Debuggin ,Review & performance tunning of the complex qurey. - Online formatter. - Offline SQL Formatter.

Pros :- Works with different databases SQL statements.

Cons:- Doesn't recongnize many Peoplesoft Meta-SQL tags. Just replace them with sql function or static values then it works fine.

2) Pace Trace :- Pace-Trace is effectively a viewer for PeopleSoft SQL trace files. It reads a selected trace file and creates a grid that summarizes all the SQL actions contained in the file.

This quick access to trace file information can be used by everyone from business analysts to developers, speeding report design, custom development, and troubleshooting.

Following Trace files generated in different methods can be

1)Trace an entire session - Trace file generated by the entire session. Enabled on the login trace page.
2)Interactive Tracing - Enabled at PeopleTools > Utilities > Debug > Trace SQL.
3)Application Engine trace.
4)COBAL Tracing.
 3) PS Pad editor :- This editor is very much similar to the Ultra edit and Edit plus which many of us know.

What makes it special is for the following reasons.

1) Peoplecode.INI file which is code highligter for the peoplecode like in the application designer - Why do we need this one for ?? Read the next reason.

2) Export to Clipboard as RTF feature (File > Export > Export to Clipboard as RTF) is allow you to copy the peoplecode from the editor with the code highlighter and paste in the word document or email. code highlighter formatting is retained.

3)SQR.INI file is also available for SQR programming.

Check out this link -

for details on how you can use it.
4) Zapgrab 1.0 :- The best , easiest & Free way to capture screens, and paste them into Documents & presentations.

It is very helpful to capture the screens for the Testcase results document , Technical design & Functional design documents.

ZapGrab allows you to capture your screen in two different ways: the whole screen or only a section with a quadrangular form selected by you with the mouse.

Just select the area on the screen and to Cntrl+v.

Cons:- Difficult to capture scrolling window screens.

5) SQR Express :- Developing SQRs Has Never Been So Easy! - In a powerful environment you can edit multiple files with unlimited length, maintain multiple database logins,

navigate in the source-code by clicking procedure-names or inside a treelike program-flow, variables, calls, includes or records.

Cons:- Not a freeware.
Detalied features here.
6) JEdit :- Java based open source editor. Code highligther for SQR available

Pros: - A lot of open source plugins avaliable.
The only free ware text editor with help of plugins can create SQR tree like SQR Express.
Can create own plugins. Create plugins for intellisense.

Cons:- Not the easiest way installing plugins with different versions.


HCM/HRMS Tables for the PS QUERY - Querying HCM/HRMS table in peoplesoft

HCM/HRMS Query reporting

The following are the main tables used to query HR related data

Human Resources Information/ Personal Data:

Employment Data:

EMPLOYMENT- general employment data, such as various service dates, etc.
Department Inforamation
DEPT_TBL --This is one of foundation tables in HR. You will rarely query against it directly; more

likely you will join to it to get the description or short description. Department codes in PeopleSoft are

different from what was used in HRS/Legacy, and department data is effective dated.
Job code information
JOBCODE_TBL --This is also a foundation table. This table holds job codes, descriptions and related

data such as FLSA classification, pay plan, grade and structure information.
Job Data:

JOB-- Job (roughly equates to assignment records in legacy). Contains job records along with other

data. Effective dated someone's job history will be stored in this table. Remember, Empl Records

refer to an employee's job record(s). The existence of multiple Empl Records means that they have

had or are currently holding multiple jobs Empl Records start with 0, and their benefits are tied to

Empl Record #0.
Benefits Data :

BEN_PROG_PARTIC – Stores the Benefit Program to which the employee is assigned.

LEAVE_PLAN – Stores enrollment in leave plans.

HEALTH_BENEFIT – Stores e nrollment in health insurance plans

SAVINGS_PLAN -- Stores enrollment in TDA, ORP and Deferred Compensation

LIFE_ADD_BEN -- Stores enrollment in life insurance plans.

RTRMNT_PLAN – Stores TRS enrollment.

DISABILITY_BEN – Stores enrollment in disability plans.
Tax Data:

FED_TAX_DATA – Contains federal tax data, including nonresident alien data

STATE_TAX_DATA – Although there is no state withholding in Texas, this record is necessary to

maintain state of residence and unemployment jurisdiction.
Payroll Data:

Payroll processing check data (records used by the payroll process to store check history)








Payroll funding distribution data (records that tie funding to payroll actuals):



Other employee level payroll data:

GENL_DEDUCTION - Genearl deduction.

GENL_DED_CD        - General deduction code.

ADDL_PAY_EFFDT -  additional pay effdt.

ADDL_PAY_DATA   - additional pay data.

ADDL_PAY_ERNCD - additional pay earn code.

DIRECT_DEPOSIT  - Direct deposit.

DIR_DEP_DISTRIB - Direct deposit distribution.
Position Data:

POSITION_DATA—identifies position data for each business unit.

Tenure Data:

EG_TENURE  Tenure Data.
Use the Employees table whenever possible. Queries using this table will generally have better

performance for several reasons. This tables has name, Empl ID, empl records, job title, job code,

dept title, dept code, location code, comp rate, comp frequency, position number, standard hours,

etc., data for active employees. Two fields that it does not have are Empl Class and FTE. This table

is not a transactional table, meaning that records are not actively written to it throughout the day.

Rather, it is refreshed nightly on process scheduler. There may be a one-day lag time for new data to

hit the table, but for most reporting this is acceptable.


Online Peoplecode / SQL trace is not getting generated

Peoplecode / SQL Trace is not getting generated.

Check the Application Server configuration file APPSRV.cfg for the TraceSQMask and

PSADMIN can be used to set the parameter.

If the these mask parameter are set to zero then configuration file then trace files are not generated.

Process schedular configuration file needs the change for Applicaiton engine programs SQL / Peoplecode  invoked in the Batch mode.


Important PeopleSoft HCM processes

1) Refresh the EMPLOYEES table.
2) Refresh the Personal Data Process.
3) Refresh the Personal Data table with future-dated data.
4) Refresh name display values.
5) Audit Human Resources data for integrity.
6) ID Change.
7) ID Delete.
8) ERN Delete process.
9) Refresh Compensation.

Set Up HMRS > System Administration > Database Processes

1) PERS_REFRESH – Refresh Personal Data Process. Based on the setup in the Personal data setup component. Personal data record will be refreshed. Process updates the current data asof the Current date / System date. What fields are populated is configurable in the personal data setup component.

Setup Record:- PS_PERSON_DT_SETUP.

2) HR_PERSDATA - Refresh the Personal Data table with future-dated data. This process updates

Personal data table with all the PERS_DATA_EFFDT, NAMES, and ADDRESSES (optional) when that data becomes effective. This is should be scheduled as a nightly process in order to catch changes every day.

3) PER099 - Refresh EMPLOYEES Table. Refresh the PS_EMPLOYEES snapshot table as of the selected date or current date. This process also updates the PS_EMPLOYEES_LNG.

4) Refresh all records with the NAME_DISPLAY and NAME_FORMAL fields to reflect changes made to the People Code definition of those fields. You can choose to update records with all name format types or just with one name format type.

It has following options to choose from.

5) HRAUDIT - Audit Human Resources data for integrity.- Core HR Data Integrity. PER900. Initiate the Core Human Resources Data Integrity Audit.

Following things are audited.

• Persons were found in PERS_DATA_EFFDT that do not exist in PERSON
• Persons were found in PERSON that do not exist in PERS_DATA_EFFDT

• Persons were found in EMPLOYMENT that do not exist in PERS_DATA_EFFDT

• Persons were found in JOB that do not exist in EMPLOYMENT

• Persons were found in EMPLOYMENT that do not exist in JOB

• Persons were found in JOB that do not exist in PERS_DATA_EFFDT

• Persons were found in JOB that do not exist in JOB_JR.

• Persons were found in JOB_JR that do not exist in JOB.

• Persons were found in PERSON that do not have a Primary Name in NAMES.

• Persons were found in NAMES that do not have record in PERSON.

• Persons were found in ADDRESSES that do not have record in PERSON.

• Persons exist in DISABILITY that does not exist in PERSON.

• Persons were found in DIVERS_ETHNIC that do not exist in PERSON.

6) Change ID / Delete ID Process – HR_PER502 - This process is used for the correction of the employee ID it updates the all tables with new employee ID. This also used for the deleting ID as well. ID Delete control component contains the exclusion list of the records.
7) Delete ERN Process - HR_EERCD_DEL – This process is used to delete the Employee record Number or Empl RCD of an employee.
ERN Delete Field Names Component– EMPL_RCD different field names in the different records to look for. ERN Delete Exclusion List – List of the table to be excluded from the table.
ERN Delete Control – List of the message for the Exclusion list of table/ field names.

8) Refresh Compensation -
HR_CMP015 – Process to refresh the compensation. Refresh multiple worker compensation packages with current default values, and print a report that displays the new and previous compensation data of the impacted workers. It executes the process executes the same steps as the Default Pay Components button on the Compensation page.


Language Preference in Configuration Manager


Users trying to access App Designer in Japanese language & it resulted in some Junk characters. Language Preference was set correctly.


After analysis found that "East Asian Languages" were not installed on the NT Server [ Install files for East Asian languages is UnChecked ], installing this resolved the issue.