Execution Order of Peoplesoft Events

SEARCHINIT, SEARCHSAVE, ROWSELECT, PREBUILD, FIELDDEFAULT, FIELD FORMULA, ROWINIT, POSTBUILD, PAGEACTIVATE, FIELDEDIT, FIELDCHANGE, POP_UP, ROWINSERT, ROWDELETE, SAVEEDIT, SAVEPRECHANGE, WORKFLOW, SAVEPOSTCHANGE

Create a hyperlink to call a secondary page from the Main page

1. select Insert, then selectPush Button/Hyperlink.
2. Click where you want the upper, left-hand corner of the control to begin.
3. A push button of small image size appears.
4. Double-click the push button to access the Push Button/Hyperlink Properties dialog box.
5. On the Type tab, select whether the control appears as a push button, hyperlink, or action widgit.
6. Select a destination type from the Destination drop-down list box.
7. See Specifying Type Properties for Push Buttons or Links.
8. Select the record and field names with which you want to associate the push button or link.
9. Select any of the remaining Type options based on your destination type.
10. On the Use tab, select the purpose of the control.
11. See Specifying Use Properties for Push Buttons or Links.
12. Save the page.

Hyperlink in the Main Page

Use a Domodal function to call the secondary page from main page.

Hyperlink Properties

Secondary Page Properties

In the hyperlink properties we mention the field name,In the field change event of the same field write this code

DoModal(Page.Secondary page name, "Sample Secondary Page", - 1, - 1, 1, Record.table name, CurrentRowNumber());

In  secondary page properties we have the OK and Cancel button,to Activate the functionality of  OK button insert a secondary page button in the main page and assign the secondary page name

Step1:Insert secondary page

Step2:Assign the secondary page name,which is triggered using the hyperlink

OK Button Activation

Update using case in Application engine SQL

Please find the sample code to update a table using case :

UPDATE TABLE1 MAIN
  SET MAIN.CHAR_TO = (CASE WHEN MAIN.REG_TEMP = 'R'
   AND MAIN.POSTAL2 IN (
 SELECT POSTAL
  FROM TABLE2) THEN 'VALUE1' WHEN MAIN.REG_TEMP = 'R'
   AND MAIN.POSTAL2 NOT IN (
 SELECT POSTAL
  FROM TABLE2) THEN 'VALUE2' WHEN MAIN.REG_TEMP = 'S'
   AND MAIN.POSTAL2 IN (
 SELECT POSTAL
  FROM TABLE2) THEN 'VALUE3' WHEN MAIN.REG_TEMP = 'S'
   AND MAIN.POSTAL2 NOT IN (
 SELECT POSTAL
  FROM TABLE2) THEN 'VALUE4' WHEN MAIN.REG_TEMP = 'W'
   AND MAIN.POSTAL2 IN (
 SELECT POSTAL
  FROM TABLE2) THEN 'VALUE5' WHEN MAIN.REG_TEMP = 'W'
   AND MAIN.POSTAL2 NOT IN (
 SELECT POSTAL
  FROM TABLE2) THEN 'VALUE6' ELSE ' ' END )
 WHERE MAIN.PROCESS_INSTANCE = %ProcessInstance

Where value1 to value6 varies based on the case statement.Here the CHAR_TO field is assigned different value based on the case statements.

Concatenation of strings in application Engine using SQL

Please find the sample code to Concatenate using Application Engine SQL

UPDATE Table1 MAIN
  SET MAIN.PASSWD = (
 SELECT 'TXT'|| SUBSTR(REPLACE(NID.NATIONAL_ID %Comma '-' %Comma'') %Comma -4 ) || EXTRACT(YEAR
  FROM SYSDATE)
  FROM Table2  NID
 WHERE NID.EMPLID = MAIN.EMPLID
   AND NID.NATIONAL_ID_TYPE = 'PR')
 WHERE EXISTS(
 SELECT 'X'
  FROM Table2  NID1
 WHERE NID1.EMPLID = MAIN.EMPLID
   AND NID1.NATIONAL_ID_TYPE = 'PR')
   AND MAIN.PROCESS_INSTANCE = %ProcessInstance

IF NID.NATIONAL_ID=1234567
AND YEAR=2015

Then Output will be -->TXT12345672015

Application Engine Trace-Peoplecode & SQL

Steps to  trace an application engine process using the Process Definitions

1. Navigate to PeopleTools > Process Scheduler > Processes
2. Enter your Application Engine name
3. Navigate to override options tab.
4. Enter the parameter list as listed below:-Trace 7 -TOOLSTRACEPC 4044

This will trace SQLs inside of your application engine as well as peoplecode. Save the process definition and go ahead and run your program. To examine the trace files, just navigate to the process monitor, click details on your process, then View Log/Trace.

First and last day of previous Month using Oracle SQL

To get the First and last day of previous month use following code snippet::

a) SYSDATE
select trunc(trunc(SYSDATE,'MM')-1,'MM') "First Day of Last Month",trunc(sysdate,'MM')-1 "Last Day of Last Month" from dual;

 b)For any Input Date::
select trunc(trunc(TO_DATE('01-JAN-15','DD-MON-YYYY'),'MM')-1,'MM') "First Day of Last Month",trunc(sysdate,'MM')-1 "Last Day of Last Month" from dual;

Where '01-JAN-2015' is the input Date

PeopleSoft DB Link

A database link (dblink) links databases. It allows you to query against other databases using the syntax TABLENAME@DBLINK.
In a scenario where you are trying to access the finance data from HCM environment,at that particular point you can create a DB link to do the same.

CREATE public database link DBlink Name CONNECT TO Username IDENTIFIED BY password using 'databasename';

PS:In order to execute this query use a data mover.

Process Scheduler::Location of the Log and SQL Trace Files

In case if we are unable to view the log file from Peoplesoft process monitor there is another method to view the out file and the log file.

Navigate to the following location::

Operating System	Log Directory
Windows	PS_CFG_HOME\appserv\prcs\<Database Name>\LOGS
UNIX	PS_CFG_HOME/appserv/prcs/<Database Name>/LOGS
OS390 UNIX System Services	PeopleSoft Process Scheduler Server: $PS_HOME/appserv/prcs/<Database Name>/<Log Directory>/_PSPRCSRVLOG Distribution Agent Server: $PS_HOME/appserv/prcs/<Database Name>/<Log Directory>/_PSDSTSRVLOG

In the UNIX Box,Navigate to the location(HOME\appserv\prcs\<HCMDEV>\LOGS) ,let the Database Name be HCMDEV. At that particular location you will find many folders named after dates.Go to the recent date folder ,then you will come across folders named after the Report ID (which you can find in process monitor view trace page).Check for the apt report id and get your log file and  Out file.

Oracle SQL:: Convert a field from NOT NULL to NULL

In order to convert a field from NOT NULL to NULL use below stated SQL :

ALTER TABLE TABLENAME MODIFY FIELD NAME NULL;

In order to convert a field from  NULL to NOT NULL use below stated SQL :

ALTER TABLE TABLENAME MODIFY FIELD NAME NOT NULL;

Detail Page,Disable saving Page in Component Properties

Detail Page
Select a Component,navigate to the use tab in properties,you will find the Detail page tab.I was wondering what does this function do. I  did a lot of R & D which i am sharing with you folks.

The detail page relates back to the search record. It's used to give the user more information about the item being selected before it's selected. For example, assume that your search page returns two employees with the same name but different EmplIDs. The detail page could be used to show more information about the employee (department, job title, manager, etc.) so the user makes sure they choose the right one. 

The Detail Page option exists on the component properties, it has been dropped by Oracle. Please go through  8.9 PeopleBooks:: 'No longer used in PeopleSoft applications. This option appears for upgrade purposes only.'

Disable saving Page

Navigate to component Properties Use tab, we have the Disable saving Page option.This function is used to disable the saving option from your page.

Peoplebooks Explanation:Select when you want to hide the Save button in the toolbar and disable the Alt+1 (Save) hot key. Selecting this option prevents users from being prompted to save when exiting a page. However, it does not prevent using PeopleCode to save a page with the DoSave() or DoSaveNow() functions. This functionality can be helpful for applications in which the user is not making database changes and does not need to be prompted to save.

After enabling the Disable Saving Page Option

So if we want back the save option , go back to the component properties and uncheck the Disable Saving Option.Then navigate to the internet tab in component properties,then check the save option.once you are done save the properties.check the page you will have the save option.

ORA-01791: not a SELECTed expression

ORA-01791:	not a SELECTed expression
Cause:	There is an incorrect ORDER BY item. The query is a SELECT DISTINCT query with an ORDER BY clause. In this context, all ORDER BY items must be constants, SELECT list expressions, or expressions whose operands are constants or SELECT list expressions.
Action:	Remove the inappropriate ORDER BY item from the SELECT list and retry the statement. Once we remove the ORDER By clause the sql should work without any error.

Prompt not working in Peoplesoft

I tried to create a new page,added Emplid field and made it as the key and search key.
Then for this field Emplid,prompted the job table,but the value was not getting populated.
I searched many areas to figure it out but for my bad i was unable to resolve it.
then i found out that even though i have prompted the job table to Emplid field,it was fetching value from some other table.I checked the Job table ,Emplid field then i realized that Emplid field in job table was prompted to Person table.
The other option to solve this issue is to create a view of Job table,but i wanted to resolve it without creating a view,then i changed the Emplid field prompt from Job table to the Person table and hence i was able to populate the values correctly.

PeopleSoft Fluid User Interface

Hi Readers,you might have heard about PeopleSoft Fluid User Interface,Well this has been one good revolution that took place in PeopleSoft.Let's go ahead and see what is PeopleSoft Fluid Interface.
                                                              PeopleSoft UI is much more than a mobile Platform.The design consist of device independent design for mobiles,Tablets and desktops,offering the advanced users experience utilizing the latest rendering technologies including HTML 5 and CSS3.
PeopleSoft Fluid User Interface offers all users a rich and easily understood means with PeopleSoft.
Power and casual Users with the traditional Classic Desktop View and the new Users from the executive range with the fluid Interface.

PeopleSoft Fluid User Interface enables mobility in any device and it is truly responsive.It recognize the device form factor that you  are using to access PS and renders the layout that's optimized for it.

For example,the PeopleSoft Interface behaves responsively to a landscape and portrait modes in a tablet

Landscape Mode

Portrait Mode

Hidden Menu

Designed as a significant improvement over the PeopleSoft “classic” user interface, the PeopleSoft Fluid User Interface moves away from pixel-perfect page layout and provides greater flexibility with the enhanced use of cascading style sheets (CSS3), HTML5, and JavaScript. PeopleSoft application fluid pages scale gracefully from large screen devices, such as laptops and desktops, to the reduced viewing space of tablets and smartphones. Many commercial websites use a similar design model whereby the presentation and layout of information is adjusted dynamically to conform to the dimensions of the user’s device. The Fluid User Interface design approach gives developers just this type of control over the user experience. When a larger screen size (a screen with more “real estate”) is detected, the application content will adjust and conform accordingly to fill the space effectively. Similarly, if a smaller screen size is detected, non-essential information can be removed and the presentation of content will adjust to flow in a usable way. Like PeopleSoft “classic” pages, fluid page definitions are maintained within PeopleSoft Application Designer, and the application developer will have the ability to define and apply conditional formatting appropriate to smartphone, tablet, or large-screen devices. At runtime, PeopleTools will apply the corresponding formatting according to the device in use. An end user can interact with fluid pages using a conventional mouse and keyboard or using a touch interface, which is common on tablets and smartphones. These new pages are device-independent and will operate on any certified device browser