Sharing our learning in PeopleSoft...

Delivered EIPs in PeopleSoft across different modules

2012-01-25T00:58:00.001-05:00

Oracle's PeopleSoft Enterprise Integration Point (EIP) Diagrams

Following link provides the details of the EIP for different modules in PeopleSoft. It will be handy while integrating modules in PeopleSoft.

http://docs.oracle.com/cd/E18171_01/EIPAPP91/index.html

PeopleTools Meta Data Tables

2012-01-21T08:30:00.000-05:00

We often search for the PeopleSoft Tools Meta Data tables for various reasons.
There are a lot of blog and websites providing these details on the Internet. In my blog post will just consolidate them together.

http://www.go-faster.co.uk/peopletools/

http://www.go-faster.co.uk/peopletools/psversion.htm

http://www.pscustomizer.com/peoplesoft-examples/peopletools-metadata/

http://www.erpassociates.com/peoplesoft-corner-weblog/peopletools/mapping-peopletools-tables.html

PeopleTools 8.52 - Restful Webservices

2012-01-05T15:01:00.000-05:00

Hi PeopleTools 8.52 - Support the Restful web services. I was just wondering what is this Restful which generates WADL vs WSDL.

I found some of the interesting post/documents that expain SOAP/WSDL and Restful/WADL.

Document provides the detail differences between the WSDL and WADL.how they are different from the WSDL 2.0.

http://public.dhe.ibm.com/software/dw/specs/ws-wadlwsdl/WADLWSDLpaper20080621.pdf

http://www.ajaxonomy.com/2008/xml/web-services-part-1-soap-vs-rest

WADL and WSDL 2.0 HTTP binding are similar but do have some differences. Each
specification has both its pros and cons. In short, WADL is simple and has limited scope. By
design, WADL is limited to describing HTTP applications and does not address features such
as security. On the other hand, the WSDL 2.0 HTTP binding is more feature rich, at the cost
of increased complexity, yet still lacks a true resource-centric model.
It will be interesting to watch as each of these technologies matures and gains broader
adoption.

Creating Secured Webservice

2011-10-07T10:31:00.001-04:00

Webserivces are one of the best methods to communicate 2 systems. Data is transmitted between the system over the network needs to be safe. Webservices security is very important.

Here are some of the links that will be useful for implementing the webservice security. Later I will post the detailed steps for the implementing the webservice security.

Oracle Support documents

E-IB: How to Implement WS Encryption for Inbound Web Services Security (Doc ID 661275.1)
E-IB: How to Setup Inbound WS-Security Processing Using SAML Token in Integration Broker (Doc ID 1322740.1)
E-PORTAL: WSRP Red Paper For PeopleTools 8.49-8.50 (Doc ID 1073454.1)
E-SEC: Does Peoplesoft Support the Securty Assertion Markup Lanuage (SAML) Directly? (Doc ID 623055.1)

PeopleBook Links

Home > PeopleBooks > Enterprise PeopleTools 8.50 PeopleBook: Integration Broker Administration > Setting Up Secure Integration Environments > Understanding WS-Security Processing using SAML Tokens

Home > PeopleBooks > Enterprise PeopleTools 8.50 PeopleBook: PeopleTools Portal Technology > Configuring WS-Security For WSRP Consumption and Production > Determining Security Requirements

Home > PeopleBooks > Enterprise PeopleTools 8.50 PeopleBook: Integration Broker Administration > Setting Up Secure Integration Environments > Understanding WS-Security Processing using SAML Tokens

Home > PeopleBooks > Enterprise PeopleTools 8.50 PeopleBook: Integration Broker Administration > Setting Up Secure Integration Environments> Understanding WS-Security Processing using Username Tokens

Prerequisites for Implementing WS-Security in PeopleSoft Integration Broker Implementing WS-Security for Inbound Integrations (Username Tokens)

Implementing CAPTCHA using ReCaptcha in PeopleSoft

2011-10-07T10:09:00.001-04:00

Backgroud :
Find out about ReCAPTCHA here :- http://en.wikipedia.org/wiki/ReCAPTCHA
CAPTCHA :- http://en.wikipedia.org/wiki/CAPTCHA

You can use this for the forgot password component , carrer login page (applicant login) or any other similar pages strighat way.

Following are modified steps from the reCAPTCHA website for the PeopleSoft Implementation.

First, go to the reCAPTCHA web site and create an account. As part of that account creation process you'll have to specify the domain your reCAPTCHA will be protecting. The reCAPTCHA site will will give you a key pair for that domain. The key pair allows you to authenticate your reCAPTCHA requests to the reCAPTCHA servers

Second, Create the HTML Area in the PeopleSoft page where you want the ReCaptcha to apper
in the HTML Defination add the follwing Java script code, you need to enclose it with form tags.
make sure you replace - with the public key from the key pair created in reCaptcha website.

Download the Java API library in the following location

http://code.google.com/p/recaptcha/downloads/list?q=label:java-Latest - Place the jar file in the class path/folder for the java in the appserver.

Add button on the page, similar to submit button for the page, on field change of the button you can write the code as below.

PS: Make sure you replace the private in code with the private key generated by reCaptcha for your domain.
Hope this will be helpfull.

Verity Search Engine in PeopleSoft HR applications.

2011-07-27T10:35:00.002-04:00

VERITY Search Engine, PORTAL_INDEX process, Building a Search Index and Searching the Portal - very good post from Shyam Sundar.

http://peoplesoftwiki.blogspot.com/2010/01/verity-search-engine-portalindex.html

All about PeopleSoft Portal Registry- Shyam Sundar

http://peoplesoftwiki.blogspot.com/2010/02/what-is-portal-registry.html

Verity is needs to installed in 8.50 and HCM 9.1 version. It no long comes as product with peoplesoft installation. following link has the details to install the verity search engine.

Difference different versions of Peoplesoft HR Application

2011-07-27T10:03:00.002-04:00

People HR application has evolved with each version and there was major change in the Personal model. Most of us know what are the major changes in personal model. Some time finding all the detail changes and to understand and back data from old versions during upgrade and re-implementation is a challenge.

We do have details in the people books, It has not evolved as much as application find things on people books is not that friendly. I found one of link hosting the details of the changes hope this will be useful for you as well.

http://www.acs.utah.edu/acs/systems/ps8/CampusSolns/CS_Docs/appdocs/ucs801_8926.htm

Delivered Page to Search Component Navigation.

2011-07-19T01:07:00.000-04:00

There was several solutions to find the navigation path for component and page. There were several SQL statements for different databases. Finally Oracle as developed page this purpose.

In the latest Ptools 8.50 and Application 9.1 application search is possible from two navigations.

1) Main Menu > Enterprise Components > Find Object Navigation
2) Setup HRMS > System Administration > Utilites > Portal Navigation Path

Search is possible with name of Component,Page,Secondary Page and Content Reference.

Search works fine with hidden components as well.

PeopleTools 8.52 - Next big tools release

2011-07-13T11:49:00.000-04:00

A lot of changes are expected to happen in the 8.52. RVP - Release value proposition of of 8.52 describe a lot interesting features. Very excited to work on 8.52 applications.

There are already a lot of blog providing detail information about the 8.52. I have included few of those in this post.

http://bloggingaboutoracleapplications.org/

http://blogs.oracle.com/peopletools/entry/new_peoplesoft_applications_search

http://blogs.oracle.com/peopletools/entry/sneak_peek_of_peopletools_8

http://blogs.oracle.com/peopletools/entry/peoplesoft_test_framework_enhancements

http://blogs.oracle.com/peopletools/entry/peoplesoft_s_browser_story_gets

Understanding / Interpreting Oracle SQL Explain Plan

2011-02-09T03:36:00.000-05:00

Correct Interpretation of the Oracle Explain Plan is requried. I found one of the link where it is explained easily.

http://www.akadia.com/services/ora_interpreting_explain_plan.html

Peoplesoft Job Openings

2011-01-27T15:37:00.001-05:00

An dedicated web site for the peoplesoft job openings.

http://www.rdklabs.org/

Filelayout Facts

2011-01-27T06:19:00.001-05:00

FIXED format

Be careful when you change the length or starting position of any file fields, or if you insert a new file field between two existing ones. It’s possible to overlay fields. Results are unpredictable.

When you insert a record into a file layout, fields of type Long are converted to type Character, with a length of 0. You must set a field length greater than 0 before you can save the file layout.

CSV format

• Both the qualifier and the delimiter accept a blank as a valid value.
• If a field is NULL, you don't have to use qualifiers. In the following example, Field2 is NULL.
Field1,,Field3,Field4. .

XML Format

• Your XML input file must contain at least the fields that are specified in the file layout definition you’re using. If the file is missing any fields, the input rowset won’t contain any data.

• If your XML input file contains extra fields that aren’t specified in the file layout definition, the ReadRowset method will ignore the extra fields.

• When you insert a record into a file layout, fields of type Long are converted to type Character, with a length of 0. You must set a field length greater than 0 before you can save the file layout.

People-Tools 8.51 - New UI Enhancements.

2010-12-19T23:11:00.001-05:00

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

http://download.oracle.com/peopletools/html/851ui_viewlet.html

Peoplesoft Test Framework Demo links

2010-12-15T04:19:00.001-05:00

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 http://www.youtube.com/watch?v=hY_q82hpcks
2) Part 2 http://www.youtube.com/watch?v=kIK3QsPTPOU
3) Part 3 http://www.youtube.com/watch?v=aRmpusoAN5E

3rd Party Batch Scheduler for PeopleSoft

2010-10-22T04:44:00.002-04:00

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

2010-10-08T11:18:00.002-04:00

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.
4. Enter -TRACE 131 -TOOLSTRACEPC 3596 -TOOLSTRACESQL
143
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.
%%PRCSNAME%% %%DBTYPE%%/%%DBNAME%%/%%OPRID%%/%%OPRPSWD%%/%%RUNCNTLID%%/%%INSTANCE%%/255/%%DBFLAG%%
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
above).

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?

2010-09-28T06:48:00.000-04:00

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.);
   &Rowset1.HideAllRows();

2) Delete the data from the grid

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

OR

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.);
   &Rowset1.flush();

3) Unhide the grid

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

People-Tools 8.51 is available now.

2010-09-28T05:47:00.000-04:00

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.

http://download.oracle.com/docs/cd/E18083_01/pt851pbr0/eng/psbooks/index.htm

2) Peopletools Advisor 8.51 Advisor Webcast

https://oracleaw.webex.com/oracleaw/ldr.php?AT=pb&SP=MC&rID=57986397&rKey=8437b9e766fd40d7

3) Documentation on 8.51 release.
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=1127534.1 ( 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

2010-08-30T13:55:00.000-04:00

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;
begin
the_date := to_date(str,'dd-mon-yyyy'); -- note the correction to date mask from what you had below
RETURN true; -- conversion success
exception
when others then RETURN false; -- conversion failed
end;

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.

2010-08-30T10:16:00.002-04:00

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
Where J.EMPLID = A.EMPLID )

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

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.

2010-08-27T11:29:00.000-04:00

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.

Forgot Password setup in Peoplesoft

Type Ahead feature in the Peoplesoft 9.1/Ptools 8.50

2010-08-27T02:55:00.000-04:00

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

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

Field level:
To do it at a field level. It must fit the criteria. If you open up the field definition of the Record field Properties, the option lies there. But in order to use this feature , it will be turned on if the default page control is Edit Box or System Default. The Field has to be a search key or alternate search key and It must be either a Character, Number or Signal field.

SQL Loader - Basics

2010-08-12T04:07:00.006-04:00

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.

Input Data and Datafiles:

SQL*Loader reads data from one or more files specified in the control file. From SQL*Loader's perspective, the data in the datafile is organized as records. A particular datafile can be in fixed record format, variable record format, or stream record format. The chosen format depends on the data and depends on the flexibility and performance necessary for the job.
SQL*Loader Discards:

· As SQL*Loader executes, it may create a file called the discard file. This file is created only when it is needed, and only if you have specified that a discard file should be enabled .The discard file contains records that were filtered out of the load because they did not match any record-selection criteria specified in the control file.

· The discard file therefore contains records that were not inserted into any table in the database. You can specify the maximum number of such records that the discard file can accept. Data written to any database table is not written to the discard file.

· The discard file is written in the same format as the datafile. The discard data can be loaded with the existing control file, after any necessary editing or correcting.

Loading into Empty Tables:
If the tables you are loading into are empty, use the INSERT option.
Loading into Non-Empty Tables:
If the tables you are loading into already contain data, you have three options:
APPEND
REPLACE
TRUNCATE
Warning: When the REPLACE or TRUNCATE keyword is specified, the entire table is replaced, not just individual rows. After the rows are successfully deleted, a commit is issued. You cannot recover the data that was in the table before the load, unless it was saved with Export or a comparable utility.
APPEND:
If data already exists in the table, SQLLoader appends the new rows to it. If data doesn't already exist, the new rows are simply loaded. You must have SELECT privilege to use the APPEND option.
REPLACE:
All rows in the table are deleted and the new data is loaded. The table must be in your schema, or you must have DELETE privilege on the table.
The row deletes cause any delete triggers defined on the table to fire. If DELETE CASCADE has been specified for the table, then the cascaded deletes are carried out, as well.
Updating Existing* Rows:
The REPLACE method is a table replacement, not a replacement of individual rows. SQLLoader does not update existing records, even if they have null columns. To update existing rows, use the following procedure:
Load your data into a work table.
Use the SQL language UPDATE statement with correlated subqueries.
Drop the work table.
TRUNCATE:
Using this method, SQLLoader uses the SQL TRUNCATE command to achieve the best possible performance. For the TRUNCATE command to operate, the table's referential integrity constraints must first be disabled. If they have not been disabled, SQLLoader returns an error.
Once the integrity constraints have been disabled, DELETE CASCADE is no longer defined for the table. If the DELETE CASCADE functionality is needed, then the contents of the table must be manually deleted before the load begins.
The table must be in your schema, or you must have the DELETE ANY TABLE privilege.
Notes:
Unlike the SQL TRUNCATE option, this method re-uses a table's extents.
INSERT is SQLLoader's default method. It requires the table to be empty before loading. SQLLoader terminates with an error if the table contains rows.
Steps to Run a SQL loader:

Open Command prompt.
Then enter the Database details in the below mentioned format.
             SQLLDR Database Username/Password@Database Name.
       Eg: SQLLDR SYSADM/SYSADM&HRMS90DM
Press enter.
You will see
      Control= (in the Next Line, Enter the Control file Path.)
      Eg: Control=X:\appserv\prcs\HRMS90DM\files\Course_Catalog_Control.CTL (And
      Enter)
System takes the input from the file path mentioned in the control file and start loading the file with data accordingly.

Sample CTL (Control) File:

File name should be stored with the extension .CTL.

LOAD DATA
INFILE 'X:\appserv\prcs\HRMS90DM\files\Course_Catalog.txt'
BADFILE 'X:\appserv\prcs\HRMS90DM\files\Course_Catalog.BAD'
DISCARDFILE 'X:\appserv\prcs\HRMS90DM\files\Course_Catalog.DIS'
APPEND
INTO TABLE PS_PMR_AD_CRS_CATL
fields terminated by "|"
TRAILING NULLCOLS
(
INSTITUTION,
   ACAD_CAREER,
   CU_SUBJECT,
   CU_CATALOG_NUM,
   EFFDT DATE,
   EFF_STATUS,
   CU_DESCR_CRSE,
   CU_ACAD_GROUP,
   CU_DEPT_ACAD,
   CU_CRS_TYPE_CD,
   CU_SCHEDULE_SEQ_NM,
   CU_ACAD_LVL_CD,
   CU_CREDIT,
   CU_HOUR,
   CU_EQ_CREDS,
   CU_VR_CRED_INDC,
   CU_LST_TERM_OFF,
   CU_GRADE_MTH_CD,
   CU_FIRST_TERM_OFFR,
   CU_BHE_APPROVAL_DT,
   CU_CREDT_ONLY_INDC,
   CU_ATTND_REQ_CD,
   CU_SPL_FEES,
   CU_ROOM_REQ_CD,
   CU_ESL_INDC,
   CU_VAR_DESR_INDC,
   CU_UPDAT_TIME,
   CU_LIBERAL_ARTS_IN,
   CU_REPEAT_ALLOW_IN,
   CU_REQUISIT_OF_IND,
   CU_REQUISITE_FLAG,
   CU_BLCK_PRG_OFF_CD,
   CU_SAT_LIB_ART,
   CU_CPI_SUBJECT,
   CU_CPI_UNIT,
   CU_CPI_ELECTIVE,
   CU_UP_LOW_CD,
   CU_SPC_EFF_EXP_DTE,
   CU_WAIT_LST_ALW_IN,
   CU_PRE_REG_ALW_IND,
   CU_BLK_BOARD_ALLOW,
   CU_SCH_CLS_BLK_IND
)

URL’s you can refer:

The contents written above were taken from the following mentioned URLs.Please go through them for the details.

Ø http://www.oracle.com/technology/products/database/utilities/htdocs/sql_loader_overview.html
Ø http://www.cs.umbc.edu/help/oracle8/server.815/a67792/ch03.htm

Magic of Oracle Regular Expression Functions

2010-08-11T08:28:00.001-04:00

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.

http://www.oracle.com/technetwork/database/features/application-development/twp-regular-expressions-133133.pdf

Eg:-

SELECT * FROM TABLENAME WHERE regexp_like (FIELDTOBECHECKED,'^[A-Za-z0-9]+$')

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

2010-08-03T05:19:00.000-04:00

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

http://download.oracle.com/peopletools/viewlets.html

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

2010-07-27T05:20:00.000-04:00

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

2010-07-27T03:54:00.000-04:00

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.

http://www.go-faster.co.uk/peopletools/index.htm - Co-Operative PeopleTools Reference

Hope this will be very usefull to many.

Launching JDeveloper XSL Mapper from Application Designer/Application Engine

2010-07-17T10:49:00.000-04:00

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.

2010-06-17T04:29:00.003-04:00

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.

Peoplesoft Test Frame work.

It is one of the most required features that I was waiting People Tools to develop it. Interesting part of this frame work is you move and compare test data between the instances. There is a lot of generic test frameworks/Test Automation with data being store in spreadsheets. One big challenge, how do you store test data and process which has multiple levels of parent child relationship. Peoplesoft already does this with ExceltoCI; It might extend this feature as testing frame work, but creating CI to every component that is used in the testing will be bad design of the framework. Document is one of the new objects introduced in Peoplesoft with 8.51 and It might use this to store test data.

Now, we need to wait and watch how Oracle will implement this frame work. It will be good if this testing frame work can be extended from Automated testing to Automated data entry for the small configuration where manually configuration is done like configure AWE workflow information ,Custom/Delivered setup values. It avoids the manually configuration in multiple instances and dependency on the technical team for the DMS scripts.

Cannot access IntegrationGateway.properties incorrect password

2010-06-06T12:27:00.002-04:00

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

2010-06-06T12:04:00.003-04:00

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.

Note:

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

2010-06-06T11:57:00.005-04:00

Issue:

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

Solution:

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

2010-06-06T11:39:00.003-04:00

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

2010-06-06T11:27:00.005-04:00

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

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

Solution:

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 forums.oracle.,

Note: May not be applicable for all version.,

PSORATimestampConversion Errors

2010-06-06T11:21:00.002-04:00

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

Solution:

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

D:\daberk\psoft\pt850\fin91\utility>echo %CLASSPATH%
D:\oracle\product\102\jdbc\lib\classes12.jar

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

2010-06-04T22:08:00.002-04:00

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

2010-05-25T03:29:00.002-04:00

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#

Example:
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
0|0|0|1|1#1|1|0|0|0#2|0|0|2|2#3|0|0|0|0#4|0|0|0|0#5|0|0|0|0#6|0|0|0|0#7|0|0|0|0#

Peoplesoft Updates Details

2010-05-19T18:14:00.000-04:00

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

2010-04-19T16:30:00.000-04:00

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

Note:-
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

2010-04-15T13:40:00.000-04:00

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

2010-04-15T11:04:00.000-04:00

In integrationGateway.properties 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

2010-03-11T17:17:00.000-05:00

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
Escalations

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

2010-03-11T16:40:00.000-05:00

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.

2010-03-11T15:31:00.001-05:00

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_CURRENT - Current Job.
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.
a) HCR_JOB_COMP_DATA_SRV
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.
1) Make new Custom record as child record to job record in the job data component and use auto select option on that scroll and don't run any process which inserts row into the job record or customize all the SQR to insert in to rows into custom record. Rebuild Job data CI - component Interfaces once. Create a new page for all the new custom fields. Modify all the components similar to job data.

2) Just create a link to other component where is custom record is present and it is logical child of the job record. Add code to manage the rows in the child record for row insert , row delete , update key fields and update in job record. Job data component structure will not be impacted in this case.

Instance Variable in Peoplecode - Application packages

2010-03-08T16:50:00.000-05:00

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

2010-02-17T15:54:00.001-05:00

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

PS/Query

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.

PeopleCode

1) Access is removed or granted based on the User Profile IDs pushed in to the system array variable
%RoleDynamicMembers.
2) This is used when logic is complicated and cannot be retried from the Query.
3) Logic to select the dynamic role users will be present in the Peoplecode event (Record name, Field name , Event name , Function name.) should be provided in the dynamic members tab. 4) fetch the value using create sql fetch the oprid values and push in to the system variable.
Eg:- %RoleDynamicMembers.Push(&Userid);

LDAP Plug-in

With a directory-based rule, you must assign directory groups. The PeopleCode Rule group box appears because directory rules are implemented using the DynRoleMembers PeopleCode program. This program uses the Directory business interlink to retrieve user and group information from the directory. To view the program, open the FUNCLIB_LDAP record in PeopleSoft Application Designer. Click Assign Directory Groups to select a particular directory group that exists in your LDAP server hierarchy. For example, if you have your LDAP server grouped by geographic region, your rule could assign a new self-service role to all users in the North America group. Use the Directory Group drop-down list box to select the appropriate directory group value. The values are derived from the LDAP data that you import using the Directory Group Import process.

Note :-
When you click the button execute Dynamic rules button DYNROLE_PUBL Application engine is exequted.
This app engine program reads Dynamic Role Rules, executes the rules, and publishes the results. The messages are then subscribed to and the users are assigned to the appropriate roles. This program is designed to run in an asynchronous mode, via the process scheduler.
The DYNROLE_PUBL Application Engine does not update the database directly.
Integration broker must be configured to handle Application Messaging.

DYNROLE_PUBL application engine publishes messages to ROLESYNCH_MSG.

Peoplesoft AWE Workflow configuration DMS scripts

2010-02-16T15:51:00.001-05:00

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"
EXPORT PS_PTAFAW_TXN;
EXPORT PS_PTAFAW_TXN_LNG;
EXPORT PS_PTAFAW_TXN_LBL;
EXPORT PS_PTAFAW_TXN_LVL;

-- "Approvals > Workflow Transaction "
EXPORT EO_TRANSACTIONS;
EXPORT EO_TRANS_LNG;

-- "Configure Transactions"
EXPORT PS_PTAFAW_NOTIFY;
EXPORT PS_PTAFAW_NOT_USER;
EXPORT PS_PTAFAW_TXN_CFG;

-- "Setup Process Definitions"
EXPORT PS_PTAFAW_PATH;
EXPORT PS_PTAFAW_PATH_LNG;
EXPORT PS_PTAFAW_PRCS;
EXPORT PS_PTAFAW_PRCS_LNG;
EXPORT PS_PTAFAW_STAGE;
EXPORT PS_PTAFAW_STG_LNG;
EXPORT PS_PTAFAW_STEP;
EXPORT PS_PTAFAW_STEP_LNG;

-- "Criteria Definitions"
EXPORT PS_PTAFCRTA;
EXPORT PS_PTAFCRTA_REC;
EXPORT PS_PTAFCRTA_VAL;

-- "Maintain User Lists"
EXPORT PS_PTAFUSER_LIST;
EXPORT PS_PTAFUSER_LNG;
-- "Generic Templates"
EXPORT PS_WL_TEMPLATE_GEN;
EXPORT PS_WL_TEMPL_GEN_TK;
EXPORT PS_WL_TEMPL_GEN_RS;

Peoplesoft HCM Process - Scheduled process.

2010-02-15T18:24:00.000-05:00

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

2010-02-03T17:13:00.000-05:00

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.
http://helpwebserver:port/productline/f1search.htm?contextID=%CONTEXT_ID%&LANGCD=%LANG_CD%

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

2010-02-02T14:12:00.000-05:00

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.

http://www.dpriver.com/pp/sqlformat.htm?ref=g_wangz - Online formatter.

http://www.dpriver.com/index.php?ref=g_wangz - 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 - http://xtrahot.chili-mango.net/2007/07/prettify-those-code-listings/

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.

Link:- http://www.softpedia.com/get/Multimedia/Graphic/Graphic-Capture/Zapgrab.shtml

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.

http://www.bcstools.com/SQRExpressInfo.pdf

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.

http://jjmpsj.blogspot.com/2009/08/using-jedit-to-edit-peoplesoft-files.html

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

2010-02-01T15:57:00.000-05:00

HCM/HRMS Query reporting

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

Human Resources Information/ Personal Data:

PERSONAL_DATA, PERS_NID

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)

PAY_CALENDAR

PAY_CHECK

PAY_LINE

PAY_CHECK

PAY_EARNINGS

PAY_OTH_EARNS

PAY_DEDUCTION

PAY_TAX

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

PAY_ERN_DIST

PAY_DED_DIST

PAY_TAX_DIST

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.

Hints:-

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

2010-01-18T16:01:00.000-05:00

Peoplecode / SQL Trace is not getting generated.

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

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

2010-01-18T15:36:00.000-05:00

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.

Record: PS_PERSONAL_DATA
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

2010-01-10T07:45:00.001-05:00

Issue:

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

Solution:

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.

Duplicate row errors in DMS Scripts

2009-12-08T10:48:00.000-05:00

IGNORE_DUPS Vs UPDATE_DUPS
Often we get the Duplicate row errors in the DMS Scripts based on the target environment in which import script is executed. Since DMS scripts can run in any target environment we need to handle this error in the DMS Script.

The best know method is IGNORE_DUPS statement in the DMS script
SET IGNORE_DUPS ;
IMPORT {record * } ;

The biggest disadvantage of this statement is it can only be run 'Bootstrap mode' which all the developers may not have access.

Is there way to avoid handle this error wihout running the DMS in the bootstrap mode ?

SET UPDATE_DUPS;
IMPORT { Record * };

Above statment can be used, it is not an exact replacement for the IGNORE_DUPS but it helps to slove problems in most of the cases.
UPDATE_DUPS - If the key values of the table exists in the target environment then it updates it with new values and insert the new rows in the .dat file.

Invoking the Remote call from AE/CI Process

2009-12-02T17:57:00.000-05:00

In PT 8.4x you need to follow a certain sequence prior to invoking the PushButton field. The basics on calling COBOL from AE/CI PeopleCode is as follows. Note that "tweaking" may be necessary to get it to work.

1. AE (Application Engine) needs to have restart disabled in order to use CommitWork() to force a commit to the DB of the CI data.
2. Set the CI's properties, except for the "Pushbutton" field (usually Y/N) used to kick off COBOL.
3. Save the CI (call Save()).
4. If Save is successful, call CommitWork().
5. Set the CI pushbutton property (toggle it) to trigger the COBOL process.
6. COBOL will run asynchronously. So you usually need to put a dummy loop to wait until it's done processing to avoid having the next CI record "bump" into COBOL or in case you need to perform some post COBOL processing of the record.

Action value combinations of the Job data

2009-11-13T17:25:00.000-05:00

Action Combination/ Action Paris - Reverse Action for each action.

Action - LOA – Leave of Absence , LWP – Leave with Pay

Reverse Action RFL – Return from

Action
LTD – Long Term Disability with Pay , LTO – Long Term Disability
STO – Short Term Disability , STD – Short Term Disability with Pay

- Reverse Action

RFD – Return from Disability

Action Reverse Action
SWB – Short Work Break - RFW – Return from Short Work Break

Action Reverse Action
LOF – Layoff REC – Recall from Layoff

Action Reverse Action
TAS – Create Temporary Assignment RFA – Return from Temporary Assignment

These are the actions that will create and stop the special temporary assignment relationship that puts the original instance in a suspended status while the person works on a temporary assignment.

Action Reverse Action
SUB – Create Substantive Job - RTS – Return to Substantive Job

These are the actions that will create and stop the special temporary assignment relationship that puts the original instance in a suspended status while the person works on a temporary assignment.

GenerateComponentContentURL / GenerateComponentPortalURL return NULL / Blank

2009-11-09T12:21:00.000-05:00

PeopleTools > Portal > Node Definitions

The Base URI (e.g. http://www.myserver.com/psp/mysite/) is only available automatically in the context of a web server. If a process is not signed in via the web (e.g. Application Engine), the Node definition specified in the function is checked for that Base URI. Open the Node Definition (PeopleTools > Portal > Node Definitions) and enter the Content URI (.../psc/...) and Portal URI (.../psp/...) on the Portal page. These are used by the GenerateComponentContentURL and GenerateComponentPortalURL functions, respectively. By default these fields are empty.

Suppressing Automatic Lookups for Improved Performance

2009-10-28T19:01:00.000-04:00

Prompt table Exclusion List in Peoplesoft.
Improve prompt table performance in Peoplesoft

Many fields in PeopleSoft applications has lookup or prompts tables indicated by the magnifying glass. When you click on that icon you are taken to a lookup dialogue, but by default
the search fires automatically.

However, in peoplesoft only the first 300 rows are retrieved into the component buffer, and only the first 100 rows of that set are shown on the first page. These results are almost certainly
useless to the operator who must then enter criteria into the lookup search dialogue and search again.

If we can suppress the automatic search performance of the prompt will improve. User can search the data after providing the search values and no.of rows returned will less and more usefull to the user.

Question is how can we suppress this ? from 8.44 peoplesoft has provided the Prompt table Exclusion List.

Peopletools >Utilites > Administration > Lookup Exclusion Table.

The tables in this list are stored in the Application server cache.
You don't have to restart any application servers before the behaviour of the lookup to change.

You should should for those prompt which usually takes more time.

The following query will identify all the records that are used as look-up records within a PeopleSoft system.

SELECT DISTINCT r.recname
FROM pspnlfield p
, psrecfielddb f
, psrecdefn r
WHERE p.fieldname = f.fieldname
AND p.recname = f.recname
AND r.recname = f.edittable

Peoplesoft Training Videos

2009-10-26T18:29:00.000-04:00

Overview PeopleSoft 8 steps

Starting with peoplesoft Application Desinger

How Peoplesoft Better than SAP - Why Peoplesoft ?

2009-10-26T17:18:00.000-04:00

Peoplesoft (Oracle) VS SAP
Why Peoplesoft ?

A Comprehensive Study of the Costs and Benefits of PeopleSoft Enterprise Versus SAP

Weighing Your Options for Human Capital Management

A Brief Cost and Benefit Overview for SAP Customers

Choosing the Best Human Capital Management Suite

Books for Peoplesoft Applications

2009-10-26T16:48:00.000-04:00

PeopleSoft HRMS Reporting by Adam T. Bromwich

PeopleSoft for the Oracle DBA (Oaktable Press) By David Kurtz

PeopleSoft Developer's Guide for PeopleTools and PeopleCode By Judi Doolittle - Oracle publication

The Expert Guide to PeopleSoft Security By Jason Carter

PeopleSoft Integration Tools By Stewart Miller

Developing with PeopleSoft's Application Engine 8 By Joseph Weessies, Steve Bollinger

Peoplesoft HRMS Interview Questions, Answers, and Explanations: Peoplesoft HRMS FAQ
By Galina Landres, Vlad Landers, Vlad Landres

Essential Guide to PeopleSoft Development and Customization
By Tony Delia, Galina Landres, Prakash Sankaran, Isidor Rivera

PeopleSoft 8.4 Developer's Sheet By Joseph Weessies

Advanced Tips and Techniques for PeopleSoft Application Engine 8 By Joseph Weessies

Sams Teach Yourself Peoplesoft in 10 Minutes By Timothy Buchanan

Useful Navigations of AWE Workflows components

2009-10-26T14:39:00.000-04:00

Setup and configure workflow

1) Main Menu > Set Up HRMS > Common Definitions > Approvals > Register Transaction ----- Create Approval Process ID (Cross refernce table , Header table , App class ,Approval compoent is defined).

2) Main Menu > Set Up HRMS > Common Definitions > Approvals > Setup Process Definitions ----Create Definiation ID for the Process ID, Definaiton Criteria ,Stages ,paths ,steps

3) Main Menu > Set Up HRMS > Common Definitions > Approvals > Configure Transaction ----- Add Events and notifications to transaction.

4) Main Menu > Set Up HRMS > Common Definitions > Approvals > Workflow Transaction ----- Register workflow transactions.

5) Main Menu > Set Up HRMS > Common Definitions > Approvals > Maintain User Lists ------ Define the user list or hierarchy used for routing transactions for approval.

6) Main Menu > Set Up HRMS > Common Definitions > Approvals > Generic Templates ------ Maintain email templates.

7)Main Menu > Set Up HRMS > Common Definitions >Selfserivce > System workflow rules ------ Workflow rule for speicfic SETID.

Approvals and Delegation

1) Main Menu > Workforce Administration > Self Service Transactions > Add Delegation Request ----- Add a new delegation request on behalf of a delegator.

2) Main Menu > Workforce Administration > Self Service Transactions > Administer Delegation ----- Admin for the Delegation. Admin can delegate the transactions .

3) Main Menu > Workforce Administration > Self Service Transactions > Administer/Monitor Apporavals ----- AWE admin for the Approvals. Admin can apporve transactions.

4)Main Menu > Workforce Administration > Self Service Transactions > Approvals Audit Report ----- Apporvals Audit Report.

Delegation setup

1)Main Menu > Set Up HRMS > Common Definitions > Delegation > Installation settings ----- Configure AWE Admin Role , Enable Hierarcies , Duration to check for active proxies

2)Main Menu > Set Up HRMS > Common Definitions > Delegation > Configure Delegation Transactions ----- Configure Delegation Transaction.

%UpdateStats is not working or it is Ignored

2009-10-20T18:11:00.000-04:00

PeopleSoft Application Engine replaces this meta-SQL construct with a platform-dependent SQL statement that updates the system catalog tables used by the database optimizer in choosing optimal query plans.
Peoplesoft intends that you use this construct after your
program has inserted large amounts of data into a temporary table that will be deleted before the end of the program run.

For databases that either execute an implicit commit for %UpdateStats or require a commit before and/or after this meta-SQL, PeopleSoft Application Engine skips %UpdateStats in the following circumstances:
• A commit is not allowed, as in within an Application Engine program called from
PeopleCode.
• The program issues a non-select SQL statement since the last commit occurred. In such a situation, data is likely to have changed.
• You are deferring commits in a select/fetch loop in a restartable program. PeopleSoft Application Engine skips %UpdateStats even if the previous condition is false.

Interview questions on Workflow Delgation framework in peoplesoft

2009-10-20T18:03:00.000-04:00

Question # 1: What types of delegations are supported in 9.0?

Answer: The following delegations are supported:.

• Upward - delegating to your manager

• Downward - delegating to your subordinate

• Lateral - delegating to a peer Please note that cascading delegation is NOT supported (see question #2 for more information).

Question # 2: What is a cascading delegation?

Answer: Cascading delegation is the ability to delegate your delegated authority. For example:.

If Antonio delegates the ability to approve absence requests to Owen and Owen accepts the delegation, Owen cannot then re-delegate that same absence request approval authority to Will, Laura, or anyone else in the organization. Once a proxy has accepted a delegation request, he/she cannot delegate that authority to someone else and only the delegator (or the Delegation Administrator on behalf of the delegator) can revoke the request.

Question # 3: Does the application server need to be bounced when changes are made to the Delegation Installation Settings page?

Answer: No, the application server does NOT need to be bounced when changes are made to the Delegation Installation Settings page. Changes are reflected once the page has been saved.

Question # 4: What happens to any pending transactions when a delegation is revoked?

Answer: If there are pending transactions and a delegation is revoked, the pending transactions will be re-routed to the delegator.

Question # 5: How does delegation work with the Alternate User ID field on the User Profile component?

Answer: When new delegation requests are added, the system checks the User Profile - Workflow tab, so see if an Alternate User ID has been specified and if the dates overlap. If the dates overlap, the system will issue a warning to the user. The delegation request will override the User Profile Alternate User ID, but only for the transactions included in the delegation request.

Question # 6: Can a user delegate a transaction that they do not have access to?

Answer: No, a user cannot delegate transactions that they do not have access to. For example, an employee who is not a manager and does not have access to manager-level transactions would not be able to delegate manager specific transactions.

Question # 7: When searching for a proxy by hierarchy can you search up the hierarchy as well as down the hierarchy?

Answer: For any hierarchy chosen, the system will only drill up or down one level and return a set of employees. For example, the system will not show indirect reports or people above the user’s direct manager. This is by design.

Question # 8: What notifications are delivered for delegation?

Answer: The following notification templates are delivered as system data for delegation:

DelegationAccept - Accept Delegation

DelegationAdminAdd - Admin adds Delegation requests

DelegationAdminRevoke - Admin revokes Delegation

DelegationBatch - Batch to send email

DelegationError - Delegation Error

DelegationNotifyAdmin - Delegation Notify Admin

DelegationReject - Reject Delegation

DelegationRequest - Delegation Request

DelegationRevoke - Revoke Delegation

Question # 9: Where can I find all the Delegation related components?

Answer:

For setting up Delegation, all components may be found at

Main Menu > Setup HRMS > Common Definitions > Delegation > Delegation Setup Center

. Users of these components should be functional analysts and technical implementers. For maintaining Delegation, all components may be found at

Main Menu > Workforce Administration > Self Service Transactions > Approvals and Delegation

. Users of these components should be delegation administrators or functional analysts. The set up and maintenance components for Delegation are located in two distinct areas of the application. It is recommended that delegation administrators be given access to the maintenance components but not the set up components because changes to set up data – inadvertent or otherwise – can impact the entire enterprise. Access to the set up Delegation components should be given to very few people in your organization. For manager and employee self service use of Delegation, the main component used may be found at

Main Menu > Self Service > Manage Delegation

. The Manage Delegation menu is available at the Employee Self Service level because employees (non-managers) may need access to this component to serve as a proxy and manage delegation requests they have received.

Question # 10: Can an inactive employee create a delegation request?

Answer: No. However, if the employee has multiple jobs and one of them is inactive and the other is active, the employee can still create delegation requests. Delegation uses user profiles which are created at the Employee ID level, not the Employee Record level.

Question # 11: Can a proxy initiate a transaction on behalf of him/herself?

Answer: No. In fact, when a proxy logs into the application to initiate a transaction on behalf of his/her manager (delegator), he/she will not be able to select his/her own name to process. This is by design

Question # 12: Can a manager use the Delegation self service UI to select an indirect report as a proxy?

Answer: No. For any hierarchy chosen, the system will only drill up or down one level and return a set of employees. For example, the system will not show indirect reports or people above the user’s direct manager. This is by design to avoid circular delegation.

Question # 13: What is the intended purpose of the Delegation Administrator role?

Answer: At least one person in your organization should be designated a Delegation Administrator. The Delegation Administrator monitors delegation requests and can quickly troubleshoot any requests that have been sitting in someone’s queue for too long. He/she may also create delegation requests on behalf of employees or managers who are absent and do not have access to the application for reasons beyond their control. He/she may also be responsible for reacting to proxies who become inactive or ineligible for whatever reason. It is not intended for the Delegation Administrator to approve each delegation request that is sent.

Question # 14: Can a proxy revoke an already accepted delegation request using the application’s self service UI?

Answer: No. Only the delegator can revoke an accepted delegation request using the application’s self service UI. If a proxy can no longer serve his/her delegator, he/she must contact the delegator and ask him/her to revoke the request If the delegator is no longer available, then the Delegation Administrator can handle revoking the request.

Question #15 : Can a manager assign multiple individuals as a proxy.

Answer: Yes. Each user can assign multiple people as a proxy and each proxy can have one or more transactions delegated to them. For example, as a manager I can delegate direct report Joe as my proxy for ePerformance transactions but delegate Nancy as my proxy for eProfile transactions. However, I cannot delegate the same transaction to more than one proxy.

Question #16 : Can an individual be a proxy for one or more managers?

Answer: Yes. We provide a self service page that provides the proxy with all the information pertinent to their responsibilities (delegation authorities).

Question #17: Can a manager delegate to one or all of his or her departments by role? Answer:

Delegation does not allow you to configure which departments may delegate and by which role. You can however configure which persons a manager may choose from to serve as his/her proxy.

XML Publisher - Performance improvement

2009-10-13T10:28:00.000-04:00

Customers using functionality that does a lot of processing in Java (for example, XMLPublisher) should consider setting the JVM options for the application server. The default JVM options may not be optimal. In that case, we recommend adding the following setting to psappsrv.cfg or psprcs.cfg (in addition to the already existing options):
-server -Xms128m -Xmx512m

PSAE and PSAESRV

2009-10-13T10:26:00.000-04:00

The benefits of PSAESRV versus PSAE are a popular topic of discussion. Our studies have shown that PSAE is as good as
PSAESRV for most practical purposes.
If you have an application engine job that runs longer than 10 seconds, PSAE is equivalent to PSAESRV. PSAE has the added
advantage of being recycled at the end of each application engine job, cleaning up any outstanding SQL cursors to the database
that may have been left behind. Because PSAE recycles after each use, PSAE does not have any possible memory leakage
problem that may occupy the precious system memory. In short, PSAE is a cleaner workhorse.
To shutdown PSAESRV, when you configure the Process Scheduler, you can change the default of the PSAESRV instance to 0.
Values for config section - PSAESRV
Max Instances =0
Recycle Count=1000
Allowed Consec Service Failures=2

Peoplesoft process run status

2009-10-12T18:13:00.000-04:00

Here's a summary of the run status translates (from PeopleTools 8.49). Note that not all of these are active.
Value Status
1 Cancel
2 Delete
3 Error
4 Hold
5 Queued
6 Initiated
7 Processing
8 Cancelled
9 Success
10 Not Successful
11 Posted
12 Unable to Post
13 resend
14 Posting
15 Content Generated
16 Pending
17 Success with Warning
18 Blocked
19 Restart

Peoplesoft Dynamic View - With Bind Parameters

2009-09-15T12:01:00.001-04:00

How to pass custom Bind parameters in the dynamic sql statements used as prompt table.

Dynamic views and Bind parameters.

When Dynamic is created in the View SQL we can usually place the standard system variables

%Operator ID as Bind parameter. If you want to change view sql like where clause condition or add custom bind values without changing the select fields you can do the following.

Create dynamic record with default view sql statement in it assign it a the prompt table for the field.

In the rowlnit poeplecode event of the RECORD.FIELD add the following peoplecode.

RECORD.FIELD.SqlText = “Select A,B,C From table where A = ” || &Variable ;

When the prompt table look up is performed the latest SQL assigned above will be used.

Peoplesoft Base Benefit Tables

2009-09-09T22:15:00.000-04:00

Base Benefit Tables

PS_BEN_PROG_PARTIC - EE Benefit program participation.
PS_BENEFIT_PARTIC - Ben Plan type enrollment control
PS_BENEF_COMMENT - Benefit Comments

Dependent Details table

PS_DEPENDENT_BENEF - View Dependents/Beneficiaries
PS_DEP_BEN - EE Dependents/Beneficiaries
PS_DEP_BEN_EFF - EE Dependents Details EFFDT
PS_DEP_BENEF_VW - EE Dependent Only View
PS_DEP_BEN_ADDR - EE Dependent Address details
PS_BEN_NAME - EE Dependent Name
PS_DEP_BENEF_NID - Dependent / Benef National ID s
PS_DEPENDENT_PHONE - Dependent/Benef Phone Numbers

Health Plan Tables – Plan types – 1%

PS_HEALTH_PLAN_TBL - Health Plan Attributes Table
PS_HEALTH_PLAN_VW - Health Plans types View with its long descr plan type LIKE ‘1%’
PS_HEALTH_PLAN_LNG - Health Plans types view with its long descr lng plan type like ‘1%’

PS_HEALTH_BENEFIT - Health Benefits EE Elections
PS_HEALTH_DEPENDNT - Health Benefits EE Dependent Elections

PS_HEALTH_VW - EE Current Health Elections

Savings Plan Tables – Plan types - 4%

PS_SAVINGS_PLN_TBL -Savings Plan Attributes Table.
PS_SAVINGS_INV_TBL - Describing a specific investment option
Available within a Savings Plan type.
PS_ SAVINGS_PLN_VW -Savings Plan types view with its long description

PS_SAVINGS_PLAN -Savings Plan Enrollment Table.
PS_SAVINGS_BENEFIC -Savings Plan Beneficiaries.
PS_SAVINGS_INVEST -Savings Plan Enrollment Investment option.
PS_SAVINGS_PLAN_VW -Savings Plan Enrollment view

Life Plan Tables – Plan types - 2%

PS_LIFE_ADD_TBL -Life AD/D Ben Plan Parameters
PS_LIFE_PLAN_VW -Life AD/D Plan type views

PS_LIFE_ADD_BEN -EE Life AD/D Benefit Enrollment
PS_LIFE_ADD_BENEFC -EE Life AD/D Beneficiaries
PS_LIFE_VW -EE Current Life AD/D Benefits

FSA Plan Tables – Plan types - 6%

PS_FSA_BENEF_TBL -FSA Benefit Plan Parameters
PS_FSA_PLAN_VW -FSA Plan Type with its long description.

PS_ FSA_BENEFIT -EE FSA Benefit Enrollment View
PS_FSA_BENEFIT_VW -EE FSA Benefit Enrollment View

Retirement Plan Tables – Plan Types – 7%

PS_RTRMNT_PLAN_TBL -Retirement Plan Attributes Table
PS_RTRMNT_PLAN_VW -Retirement Plan types view

PS_RTRMNT_PLAN -EE Retirement Enrollment Table
PS_RTRMNT_VW -EE Current Retirement Benefits

Simple Plan Tables – Plan Types – A%

PS_SIMPLE_BENEFIT - EE Simple Benefits Plans Enrollments
PS_SIMPLE_PLAN_TBL - Plan attributes table.
PS_SIMPLE_PLAN_VW - Simple Plan Types view with long description.

Disability benefit plan table

PS_DISABILITY_BEN EE Disability Ben Enrollments
PS_DISABILITY_VW EE Current Disability Bens

View Menu Security - Check menu secuirty access

2009-09-03T15:31:00.000-04:00

Menu Security
Select the level of Object security and Folder visibility for Menu information that you would like to see. Please note that inquiries which return a large amount of rows may take a minute or two.
Object security
Select the level of object security for the menu information that you want to see.
UserID , Permissionlist , Role

Building Search Indexes For Portal Applications

2009-09-03T14:52:00.000-04:00

Build Search Index - PORTAL_INDEX

PeopleTools > Portal > Build Registry Search Index
PORTAL_INDEX - is the Application Engine process which creates the search index.
The search index derives keywords from the content reference labels and long descriptions.

Associating Keywords to Content References.
Additional keywords can be assigned to a content reference by adding portal KEYWORD attributes. This is very useful if there are synonym-type keywords that are not mentioned in the title or description; for example, P.O. versus Purchase Order.

Setup for HRMS for Military customers

2009-09-02T16:55:00.000-04:00

Following steps to be followed to setup for the military customers / employees

1) Set Up HRMS>Install> Installation Table> Products
check military check.

2)Modify the search views to include the Military ranks
Record MIL_SRCH_SBR - check the alternate key for all fields in the record field properties
which adds 3 new fields in the search page Military Service, Military Rank and Worn Rank
in the modify the person.

3) setup the Event manager registry for updateMilitaryNames handler

SetUp HRMS> System Administration> HCM Event Manager> Event Registry

Event name :- AssignmentMilitaryRankChanged
make this event active.

Confirm that the Handler:UpdateMilitaryNames is Active in the Registered Handlers Tab on the Event Manager – Registry page.

Handler mode:- It should be synchronous.

How to use Verity Search to find applicants using the keywords

2009-09-02T16:11:00.000-04:00

Find the resumes applicants based on the keywords.
Search the resumes in peoplesoft.

Recruiting > Administration > Build Applicant Index

An application engine program can be executed to run the Verity search Index to search the applicants.

under the applicants status select the group that needs to be indexed. select the index
C:\tmp for NT and /temp for UNIX scheduler.

execute the process HRS_SRCH_IDX to create the search index in the PS_HOME/data/search/HRS_ResumeText/

If the application servers and process schedulers are on separate boxes then additional steps need to be taken so that the application servers have access the files created under the PS_HOME\data\search\HRS_ResumeText\%DB NAME% directory

These files can be copied to app server as well.

To find the applicants

Recruiting > Find Applicants

In the Resume and Application Search section enter word that you are looking for in the resume in Enter Keywords field.

Load Cache - loading the shared cache

2009-08-29T03:18:00.000-04:00

With PeopleTools 8.50, you now have these options for loading the shared cache

for application server domains:

• Serial cache loading

• Parallel cache loading.

Serial cache loading refers to the existing process for loading shared cache. In previous releases, when loading the shared cache for application server domains, system administrators ran an Application Engine program named LOADCACHE.

The LOADCACHE program, adds each applicable cache object to the shared cache, sequentially. The LOADCACHE program is in place in PeopleTools 8.50, and it runs if you choose Serial LoadCache on the Process Request page after clicking Run on the Load Application Server Cache page.

Parallel cache loading refers to a new option intended to reduce the time required to build a shared cache. Parallel cache loading splits the work of the LOADCACHE program into two separate Application Engine programs that run simultaneously within the PSJob PLCACHE, which runs when you select Parallel LoadCache on the Process Request page after clicking Run on the Load Application Server Cache page.

Reference: Pre-Release Notes

When Security process should be executed

2009-08-27T14:47:00.000-04:00

Refresh SJT_OPR_CLS and Refresh SJT_CLASS_ALL are Security process.

When to execute Refresh SJT_OPR_CLS process

On the Roles - Permission Lists page

• Add a permission list with data permission to a role that is already assigned to one or more users.
• Remove a permission list with data permission from a role that is already assigned to one or more users.

On the User Profile component

Add a row security permission list.
• Delete a row security permission list.
• Add a role with data permission.
• Delete a role with data permission.
• Clone an existing profile that has data permission through roles or a row security permission list.
• Deactivate a user.

You can refresh SJT_OPR_CLS in real-time by using the subscriptions on the USER_PROFILE and ROLE_MAINT messages or on demand using the SCRTY_OPRCLS app
engine process.

Run the Refresh SJT_CLASS_ALL process when

• Add a new department to a department security tree.
• Delete a department from a department security tree.
• Move a department to another parent node in a department security tree.
• Modify a department security tree.

Real-Time updates of the Transaction Security Join Tables

2009-08-27T14:39:00.000-04:00

Real-Time updates of the Transaction Security Join Tables.

One of the big enhancements for HRMS 8.9 is that the security join tables are updated in real-time instead just using an Application Engine process. This is accomplished by using a special subpage that contains the working fields that are needed. This subpage (SCRTY_SJT_UPD) needs to be put on a page that is in the components where the transaction records are updated. For the transaction record JOB, the subpage has been attached to the JOB_DATA1_WRK page. This page is included in all of the components where the JOB record is updated (either directly or as a component interface).

Security Join Tables - SJT Tables in Peoplesoft

2009-08-27T13:09:00.000-04:00

There are 2 type of Security Join tables

1)Transaction - Security Join Tables
2)User Side - Security Join Tables

Transaction security definitions are stored in one of the following four SJTs, depending on the kind of transaction data:
• SJT_PERSON (stores person-related transaction data)
• SJT_PERSON_USF (stores person-related transaction data for USF installations)
• SJT_DEPT (stores department-related transaction data)
• HRS_SJT_JO (stores job opening-related transaction data)

User security definitions are stored in the following two SJTs:
• SJT_CLASS_ALL The row security access assigned to a permission list (role-based or row security).
Contains the data permission information for all the permission lists that are given data access on the Security by Dept Tree page or Security by Permission List page.
Stores Data From 1)SCRTY_TBL_DEPT 2)SJT_CLASS
Following are keys
CLASSID , SCRTY_SET_CD , SCRTY_TYPE_CD , SCRTY_KEY1 , SCRTY_KEY2 , SCRTY_KEY3
• SJT_OPR_CLS Which user profiles are assigned which permission lists
Contains the user IDs of people with data permission and the permission lists with data permission that are assigned to them.
Stores Data From
1)PSOPRDEFN
2)PSROLEUSER
3)PSROLECLASS