PeopleSoft Learning

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