Sleep in PeopleCode

You may have found business requirements that need to wait for a given time before performing the next action. For instance, in order to check whether the execution of a batch process has finished or not, you may want to query the process monitor tables to verify the process status every 15 seconds.

Unfortunately, there is no function in PeopleCode that actually sleeps processing during a period of time. Of course you can build a loop until the target time arrives, but that approach consumes CPU while waiting, so it is not resource effective.

In other languages, the sleep function simply puts the process off until the time has passed and the process is wake up. This is a much more efficient approach.

Fortunately, we can benefit from PeopleCode integration with Java to use java.lang.Thread sleep method. A sample PeopleCode would be as follows:

&sleepSeconds = 15;
GetJavaClass("java.lang.Thread").sleep(&sleepSeconds * 1000);

No java class installation is needed, as this class is included in PeopleTools, so this code should work without any further installation adjustment.

Truncating empty temporary tables in Oracle for PeopleSoft applications

Truncating temporary tables is an effective way of resetting the high-watermark level in Oracle databases.

The following script just truncates those tables that are empty, as sometimes temporary tables containing data should not be deleted as the data may belong to processes in error.

DECLARE

   sqlstatement    VARCHAR2 (100);

   fulltablename   VARCHAR2 (40);

   t_count         NUMBER;

   CURSOR c1

   IS

      SELECT owner || '.' || table_name

        FROM all_tables a

       WHERE EXISTS (

                SELECT 'x'

                  FROM psrecdefn b

                 WHERE b.rectype = 7

                   AND a.table_name LIKE 'PS_' || b.recname || '%');

BEGIN

   OPEN c1;

   LOOP

      FETCH c1

       INTO fulltablename;

      EXIT WHEN c1%NOTFOUND;

      sqlstatement :=

            'select count(*) from dual where exists (select NULL from '

         || fulltablename

         || ')';

      EXECUTE IMMEDIATE sqlstatement

                   INTO t_count;

      IF t_count = 1

      THEN

         DBMS_OUTPUT.put_line ('WARNING: ' || fulltablename || ' has data, so it will not be truncated.');

      ELSE

         sqlstatement := 'truncate table ' || fulltablename;

         EXECUTE IMMEDIATE sqlstatement;

         DBMS_OUTPUT.put_line (fulltablename || ' was truncated');

      END IF;

   END LOOP;

END;

Note: the script should be run when no process is running, as it is not blocking the tables after checking if they are empty. So, between the check and the truncate, someone may insert data. In any case, changing the script to lock the table should not be difficult.

PeopleSoft Test Framework

PeopleTools 8.51 has introduced a new and exciting feature: PeopleSoft Test Framework (from now on PTF). This tool can be used to automate testing of PeopleSoft applications. Unlike other testing automation tools, PTF has the following advantages:

• PTF is a testing automation tool designed to interact with PeopleSoft. It is quite easy to install and put it up to speed.
• Tests are stored in PeopleSoft database as metadata. This allows developers to migrate tests between environments, pretty much like any other PeopleTools object.
• Based on Usage Monitor, testers can determine which tests are impacted by a given application change: upgrade, bundle and/or customization.

PTF seems the perfect tool to automate a large degree of regression testing, thus reducing significantly the effort (and therefore cost) of testing after an application change. Particularly in upgrades, when test cycles are conducted over an over again, PTF may help to reduce the time and increase the quality needed for testing efforts.

There are still some shortfalls of PeopleSoft Test Framework. First of all, tests for PeopleSoft applications are still not delivered in the standard product, although some companies are already offering test bundles for certain modules (we at BNB are looking at offering a bundled service based on the test cases built on our installed customer base).

The second shortfall is the lack of stress testing capabilities. Still, there are plenty of tools on the market to perform this kind of testing.

All in all, it seems a very important step forward in PeopleSoft technology in order to reduce implementation and upgrade times and increase the ROI of those projects.

Cloning elements in Global Payroll

I've been lately involved in a couple of PeopleSoft Global Payroll implementations. One of the pain areas in dealing with Global Payroll rules definition is that there does not seem to be an easy way to clone or save an element with another name. Re-entering the element again is not always an option, specially if you are dealing with a formula, array or bracket with several lines.

Just a couple of weeks ago we found out a way in which we could clone elements. Here it is:

• Create a Rule Package with the elements you want to clone in it.
• Export the Rule Package.
• Rename (both pin name and code) the elements.
• Import and apply the Rule Package you previously exported.

We still think we could make it easier with a bit of customization, by automating export and import of the package rule, as it is on the same database.

Anyway, I hope this is useful. Have you found any other way to do this?

Setting Date Variables to Null in PeopleCode

If you try to assign a date variable a null value you will get an error saying that the left and right sides of the assignment are not compatible.

For instance, the following PeopleCode would error out:

Local date &myDate;

&myDate = null;

To perform this kind of assignment, you may use the Date function in the following way:

Local date &myDate;

&myDate = Date(0);

ExcelToCI under HTTPS with SSL certificate errors

One of our customers was trying to use an ExcelToCI template to load data in PeopleSoft environment which could only be accessed through HTTPS. However, as the certificate in the web server was expired, ExcelToCI would error out whenever the user wanted to create a new template or submit the data to the database.

Obviously, this issue would have been solved by simply renewing the web server certificate, but the customer was not in a position to do this now and we had to seek an alternative solution.

Our solution was to modify the ExcelToCI macros to tell them to ignore SSL certificate errors. To do so, we had to do the following changes on the CreateCITemplate.sendSOAPRequest_GetCIShape() and StagingAndSubmission.sendSOAPRequest_SubmitToDB() functions:

• Replace the following variable declaration:

Dim xHTTP As XMLHTTP

by

Dim xHTTP As New MSXML2.ServerXMLHTTP40

• Replace the following variable initialization:

Set xHTTP = New XMLHTTP

by

Set xHTTP = New MSXML2.ServerXMLHTTP40

• Add the following line before calling the Send method:
  

xHTTP.setOption 2, 13056 'Ignore all SSL errors


The SetOption call is actually telling the Send method to ignore any SSL certificate error.

How to avoid passwords expiring in PeopleSoft

One of the more often customer requirements regarding security is to implement password expiration. PeopleSoft provides this functionality using the Password Controls component under:

PeopleTools > Security > Password Configuration > Password Controls

This component provides the alternative to enable or disable password expiration controls for all users. Now, what happens if we want a certain user's password to never expire?

There are plenty of situations where we might want this to happen, for instance:

• The password for the user set in the Process Scheduler or Application Server configuration should not expire or otherwise the system may not work.
• Same happens with if a user is set as a Guest in a Web Profile.
• Also, you may want to disable password expiration for PTWEBSERVER, the user set by default to let the Web Server recover Web Profiles from PeopleSoft environment.
  

Unfortunately, PeopleSoft does not provide the ability of disabling password expiration for a given user. Good news are that it is quite easy to do by setting the last password change date to a future date using the following SQL sentence:

update PSOPRDEFN
set LASTPSWDCHANGE = '2050-01-01'
where OPRID in ('SOLICITANTE', 'PS', 'PTWEBSERVER')