Lepa's blog

Download Collaborate 08 Education Sessions

The education presentations from COLLABORATE 08 will be available on questdirect.org website until May 15th.

Filter on PeopleSoft sessions and download the ones you are interested in. Here is the link.

Trace Application Engine Processes Using Process Definitions

In this post I will show you how to trace an application engine process using the Process Definitions without setting the trace in the Process Scheduler’s psprcs.cfg configuration file.

Navigate to the process definition of the application engine that you would like to trace (PeopleTools > Process Scheduler > Processes). Go to the Overrride Options tab, and from the Parameter List drop down select Append, and in the edit box next to it add the following line:

-TRACE 7 -TOOLSTRACEPC 4044

TRACE application engine using process definition

Vendor Pending Transactions

A quick function that will allow you to verify if a specific vendor has any pending transaction(s).

Function vendor_pnding_transactions();

      &VENDOR = VENDOR_ID;
      SQLExec("select 'X' from ps_voucher a, ps_pymnt_vchr_xref b where a.voucher_id = b.voucher_id and a.business_unit = b.business_unit and a.vendor_id = :1 and a.entry_status in ('P', 'R')and a.close_status = 'O' and b.pymnt_selct_status in ('N', 'D', 'R')", &VENDOR, &EXISTS);
      If All(&EXISTS) Then
         Error Messagebox(0,"",0,0,("Vendor has pending transaction(s).");
      End-If;

Troubleshoot the SMTP Server

The SendMail function in PeopleSoft uses the SMTP server to send emails. The first thing to do when troubleshooting problems with emails not being sent is to make sure your SMTP server is up and running. In this post, I will show you how to verify if you can connect to the SMTP server as well as manually sending an email!

Note: I’m using Unix Telnet to connect to the SMTP sever.

Let’s get started by trying to connect to the server:

Open a telnet session and login to the server box you would like to test sending emails from. I will be logging in to a DEV environment.

Send Emails from PeopleCode (SendMail Function)

You can use the SendMail PeopleCode function to send emails from within PeopleCode. You can also call this function from an Application Engine.

Note: Make sure your SMTP server is configured properly or the SendMail function will fail.

Local string &MAIL_CC, &MAIL_TO, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TITLES, &MAIL_TEXT, &MAIL_FILES, &MAIL_FROM, &REPLYTO, &SENDER;
Local number &MAIL_FLAGS;

&MAIL_FLAGS = 0;
&MAIL_TO = "email-address-message-going-to";
&MAIL_CC = "";
&MAIL_BCC = "";
&MAIL_SUBJECT = "Test email";

Compare Data from the Same Table in two Different Environments

One of the requirements I have lately is to compare table data between two different environments (Development and Test).
The Oracle SQL below compares table1 that has 2 key fields and 3 regular fields.

Note: For the SQL below to work, your password needs to be the same in both environments. If not, then
a connection using the below will not be possible.

FROM table1 tst, table1@dev_database dev

-- Compare data from the same table in two different environments
SELECT   tst.fieldkey1, tst.fieldkey2,
         (CASE tst.field1

START WITH and CONNECT BY in Oracle

Here is a quick example to help you understand how oracle Start With analytical function works. The start with .. connect by clause can be used to select data that has a hierarchical relationship. Usually, it is some sort of parent child relationship like supervisor and an employee.

Let’s assume we have a table called employees. I would like to know employees that directly report to supervisor Id 1122456 –simple, right?

SELECT emplid, supervisor_id
  FROM ps_employees
 WHERE supervisor_id = '1122456';

EMPLID  SUPERVISOR_ID

Pass Dynamic Value to a Grid Label

A grid label is the blue (normally) colored grid header that you see on PeopleSoft pages. The grid text label can be easily edited using the Grid Properties in app designer. Once you have the grid properties box open, click the Label tab > Properties and there you can see the Label Text area. You can choose between a Static Text type or Message Catalog. I would almost always go with Message Catalog type as this could be easily updated online.

Send User to a Portal Folder

Sometimes you would want to give users the option to click a button on the page and send them back to the Portal Folder they came from, or may be couple folders back! Here is how you go about doing that.

You can actually use the PeopleSoft delivered function NavPageURL to accomplish this! So, lets get started!

On your page, go ahead and add a button. Place the following PeopleCode at the Component Record Field level under FieldChange event.

Declare Function NavPageURL PeopleCode EOPP_SCRTN_WRK.FUNCLIB FieldFormula;

Sure, We Can Fit it Into This Release!

Funny Picture! How true is this in your organization?

Sure, We Can Fit it Into This Release!