Javier's PeopleSoft blog

Truncating empty temporary tables in Oracle for PeopleSoft applications

2011-05-17T13:27:00.000+02:00

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

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

2011-03-17T00:51:00.000+01:00

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

2010-05-25T11:09:00.000+02:00

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

2010-02-23T10:08:00.000+01:00

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

2010-02-18T21:12:00.004+01:00

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

2008-10-31T08:20:00.004+01:00

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')

Adding new fields... to an existing record or a new one?

2007-08-06T10:14:00.000+02:00

At the very start of every PeopleSoft project we're confronted to the task of defining the Development Standards. One of the recurring discussion points is what to do when we need a new field in a record.

a) Create a sibling record (same primary key), adding the new field as an attribute to that record.

b) Add the new field in the original record.

At first, the a) option seems the cleanest one as it does not modify standard records and it sounds like it will easier to maintain through upgrades and fixes.

However, this option also has its limitations:

Querying the table now would involve joining two tables. This leads to additional complexity and performance issues (particularly if customizations have been made by adding sibling records to multiple records in a query).
When creating a query, you may need to filter on both the standard record and the sibling one. In that case, you won't be able to use an index on both filters, as the fields are placed in different tables.
Adding a sibling records means you need to use an additional scroll area in components showing both the original record and the standard one. In some situations, this may put in a situation where the 4 allowed scroll levels (0, and 1 to 3) are not enough.

Mainly due to performance reasons, my preferred option is b). In order to keep a clearer track for upgrades and fixes, what we normally do is to place new fields in a subrecord defined explicitly for customized fields. In such way, it is always easy to identify the customized fields in a record.

What is your experience on record customization? Which of the two options you prefer?

ExcelToCI limitations: when not to use it

2007-07-31T22:48:00.000+02:00

Since it's introduction with PeopleTools 8.4x, ExcelToCI has generated excitement particularly among consultants running implementations. Finally there was a tool that allowed consultants with little or no technical knowledge to quickly load data into the system.

Now, as it normally happens in these situations, a good tool may become a bad choice if we are using it in a way it wasn't designed for. Personally, I've seen people trying to use ExcelToCI to run complex data loading resulting in unexpected delays due to the need to build ad-hoc tools for conversion.

What are the most significant ExcelToCI limitations? You may find others, but from my experience, these are quite important:

ExcelToCI does not allow to determine the order in which fields are loaded into the application. The order is determined by the tab order defined at the page level in Application Designer.
Loading large data volumes becomes unpractical for performance reasons. Excel could easily hang when manipulating spreadsheets with over 10,000 rows.
Excel becomes nearly blocked when executing the load. It's difficult to perform any other action on the same workstation, and particularly to load several files in parallel.
Can only run in Add or Update mode, but there is not hybrid option to first try to Add and then Update is the row was present.

At BNB, we built a tool called FileToCI (Ok, not much marketing brainstorming here!) that deals with most of these limitations:

Lets you choose in which order you want fields to be loaded.
Loads from CSV files or database tables, which are much easier to handle than Excel files.
Lets you load different files in parallel. Processing is done at the server level, without capturing your local resources.
Includes a Hybrid load option, which only adds the rows that were not present before the load and updates the remaining ones.

Again, I'm not saying ExcelToCI is useless. That would be far away from the truth. Reality still is that for setup data load and certain types of conversion, ExcelToCi remains a very intuitive and powerful tool.

What have been your experiences with ExcelToCI? Have you used it for massive data loading?

How BPEL adds value to PeopleTools 8.48?

2007-07-31T20:40:00.000+02:00

One of the key enhancements delivered in PeopleTools 8.48 was integration with Oracle BPEL Manager. Oracle's description of BPEL is:

Oracle BPEL Process Manager, a member of the Oracle Fusion Middleware family of products, enables enterprises to orchestrate disparate applications and Web services into business processes. The ability to quickly build and deploy these processes in a standards-based manner delivers critical functionality for developing a Service-Oriented Architecture (SOA).

Now... how this translates into real-world cases?

Recently, we had to design for one of our customers a Web Service based integration of PeopleSoft Financials with a Third Party Billing system. One of the integrations consisted in the Third Party system sending information to PeopleSoft Financials in order to perform the following actions:

Create the customer if it was not present.
Create the invoice as a pending item in PeopleSoft Receivables module.
Run Receivables Update process.
Create the deposit and apply it to the item.
Run Receivables Update process again to update customer's balance.

Before BPEL, if we wanted to create this integration, a solution would have to create a Web Service in PeopleSoft that encapsulates all these activities. The problem of this approach is that this new Web Service would not easily reusable, as most consumer applications will only require part of the operations performed in it.

With BPEL, we can keep Web Services at the right granularity to help making them reusable. Following with the example, we would just make sure the following Web Services exist (taking into account that some of them may be already available in the delivered product):

Customer creation.
Pending item creation.
Receivables update processing.
Deposit creation and application.

BPEL would then be used to orchestrate these Web Services, without requiring to build aggregated and poorly reusable Web Services in PeopleSoft.

In summary, BPEL seems to bring PeopleSoft applications to the next level in what relates to integration with Third Party applications. Also, being BPEL one of the keystones of Oracle Fusion Architecture, it seems a wise decision to gain some BPEL experience as this might be a highly demanded skill in the near future.

Note: Although Oracle ships its BPEL Manager product together with PeopleTools 8.49, this license only covers design-time use. For runtime use, you should purchase Oracle BPEL Manager separately. For more information, please refer to:

http://download.oracle.com/docs/cd/E05453_01/psft/acrobat/pt849_lic_notes.pdf

Welcome!

2007-07-31T20:07:00.000+02:00

Welcome to my Blog!

My expectation is to use this media to communicate and share my personal experiences in the IT field, and more specifically in PeopleSoft implementations.

Many thanks and happy blogging!

Javier