PeopleSoft Learning

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

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.

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.

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.

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

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.

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

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.

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.

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

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.

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.

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.