Saturday, September 26, 2015

Regular Expression Pattern Matching for PeopleSoft Trace/Log Files

Have you ever wondered how to remove some of the timing and other environment specific information from a trace/log file so that we only see the SQL and PeopleCode?

The timing and other information that I am referring to are:




While these timing and other information in the trace file are great for performance tuning activities, they seem to cause several issues (at least for me) when doing a comparative analysis. By comparative analysis, I mean comparing the trace files across different scenarios to identify a problem. For example, an issue might only occur for user A but does not occur for user B. Then we would be inclined towards comparing the trace files generated for user A and user B in an effort to identify the problem. Similarly, an issue might only occur in a particular environment (say TEST) whereas it works fine in another environment (say DEVELOPMENT). Again we would be inclined towards comparing the trace files generated in TEST and DEVELOPMENT to identify the problem.

Now that I detailed the scenario/activity, I would like to describe the problem. When we try to compare two different trace files using any file compare tools (such as WinMerge), the results would identify a difference on almost every single line! Most of them would be false positives due to the timing and other information which is not really useful in such cases.


Regular Expressions (RegEx) Pattern Matching for PeopleSoft Trace/Log Files:

So, if we get rid of (or mask the unncessary information) then we can determine the "actual" differences more efficiently.

That is where Regular Expression (RegEx) pattern matching helps in identifying and removing/masking unncessary information.

Note: I generally use notepad++ for text editing. I am sure most text editors would have a similar RegEx find/replace functionality.

RegEx Pattern for SQL:

Find: ^[a-z]{8}\.\d+ +\(\d+\) +\t +\d+-\d+ +\d{2}\.\d{2}\.\d{2} +\d+\.\d{6} Cur#\d+\.\d+\.[a-zA-Z0-9_]+ RC=\d+ Dur=\d+.\d{6}
Replace: SQL---------------

RegEx Pattern for PeopleCode: 

Find: ^[a-z]{8}\.\d+ +\(\d+\) +\t +\d+-\d+ +\d{2}\.\d{2}\.\d{2} +\d+.\d{6}
Replace: PeopleCode--------

Benefits of Regex Pattern Matching Find/Replace During Comparative Analysis:

Now, if we remove all the unnecessary information and then compare, we would be able to determine the differences quickly and easily. Here is an example of how I used the RegEx Find/Replace (detailed above) and then compared two different trace files using WinMerge.

Similarly, we can use Regex Pattern Matching to intelligently find and replace specific portions of the trace/log files to improve readability!

Thursday, September 24, 2015

Working with Rich Text Editor - Custom Configuration, Toolbars and Plugins

Rich Text Editors (RTE) are being used more commonly with the latest PeopleTools versions. There are several pages that contain Rich Text Editor fields (generally for configuration) which are then used for display on other transactional pages using HTMLAREAs, used as the content for Rich Text enabled emails or used as the content for Rich Text enabled BI Publisher reports (to name a few use cases).

Note: The Rich Text Editor provided by PeopleTools is powered by CKEditor.

Page With Rich Text Editor Enabled Field:

Here is an example of a simple page that contains a Rich Text Editor field. 

The page contains a Long EditBox (CSK_RTE_DATA.DESCRLONG) with the following properties.

We are currently using the default Rich Text Options (Note: Configuration Settings Id is blank). The page appears as follows with the Rich Text Editor for the DESCRLONG Long EditBox. The Rich Text Toolbar is generated based on the default configuration.

Page Displaying Rich Text Data:

Here is an example of a simple page that displays data stored in a Rich Text enabled field using a HTMLAREA.

Page Activate PeopleCode to display the Rich Text data field in the HTMLAREA:

The page display the Rich Text data in the HTMLAREA as follows:

Adding Custom Rich Text Options - Templates, Plugins:

I recently came across an interesting question on the OTN forums. The question was "how to use Rich Text to display an iframe with a link to a youtube video?". If you look at the above Rich Text Editor field that uses the default Toolbar, it does not have the ability to add any HTML tags or specifically IFRAME content.

To enable additional/custom Toolbar items on the Rich Text Editor, we must first create our own custom Rich Text Editor Configuration.

Creating a Custom RTE Configuration:

We need to create a new HTML definition with a name that starts with 'PT_RTE_CFG'. Let us create a custom HTML definition called PT_RTE_CFG_CSK cloned from PT_RTE_CFG_PTPPB (delivered RTE Configuration).

Now to enable a custom Toolbar item for embedding an IFRAME, we must make use of additional plugins. To find the list of all available plugins as delivered, we can look in the following directory on the web server:


Fortunately, in this case the IFRAME plugin for CKEditor is already available as delivered (if not we must download and place the required plugins in the above folder path).

Let us now add the custom IFRAME Toolbar to our Custom RTE Configuration (PT_RTE_CFG_CSK). Let us include the following additional line in the configuration (note the comma in the screenshot).

  { name: 'insert', items : [ 'Image','Flash','Table','HorizontalRule','Smiley','SpecialChar','PageBreak','Iframe' ] }

Moving ahead, let us reference this Custom RTE Configuration in the Page Field Properties of the Rich Text enabled field.

Now let us take a look at the page with the Rich Text enabled field.

We can see the additional/custom Toolbar that we added to our Custom RTE Configuration. This would help us with embedding an IFRAME element that links to a youtube video.

This results in an embedded IFRAME as follows:

Testing IFRAME on the Display Page:

Similarly, we can add additional custom Toolbars to our Custom RTE Configuration. Hope you find this useful!

Environment details at the time of writing this post:
HCM 9.2 PUM Image 12 (PeopleTools 8.54.08)

PeopleBooks - Modifying the Rich Text User Interface 

Monday, September 7, 2015

HCM/Campus Solutions Address Validations

If anyone has worked on a CI or a web service (e.g.: CWS - Constituent Web Service, AAWS - Admission Applications Web Service) that involves loading data related to addresses into HCM/Campus Solutions applications then you must have run into the address validation problem. The problem with the validation on the addresses component/page is that the fields displayed on the address subpage (specific to a country) would vary depending on the country.

This complicates the process of dynamically identifying the list fields that need to be populated depending on the current country that is being used for data entry. Let us take the example of the Admission Applications Web Service to put things into perspective. Typically, there would a web front-end for the admission applications and that would in turn invoke the AAWS web service to create and save applications into Campus Solutions. Now, the problem for the web front-end application is to dynamically figure out the list of fields that need to be populated on the AAWS request message based on the current country. Once we determine the list of fields that need to be populated, we would additionally run into the problem where the validation on the fields (required/translate/prompt edits) would differ per country.

Here is a list of logical steps and SQLs that would help with building some of that dynamic address validation logic. I was able to write some SQL that looks into PeopeTools metadata and pulls out the information we need.


Address Formatting:

To identify the list of fields to display and populate (CIs/Web Service calls) data use PS_EO_ADDR_FMT_TBL.


select * from PS_EO_ADDR_FMT_TBL where COUNTRY = 'ESP';

The address format data could be found online as well.

Address Validations:

Required Fields:

Only the following fields are required:
-    ADDRESS1* or ADDRESS2* or ADDDRESS3* or ADDRESS4*: At least one of these fields is required depending on how many exist in SQL1.

* The SQL1 provided must return the fields listed.


For ESP (Spain), SQL1 only returns ADDRESS1 and ADDRESS2 fields. Therefore, at least one of ADDRESS1 or ADDRESS2 fields is required for Spain.
For BRA (Brazil), SQL1 returns all fields ADDRESS1, ADDRESS2, ADDRESS3 and ADDRESS4. Therefore, at least one of ADDRESS1 or ADDRESS2 or ADDRESS3 or ADDRESS4 fields is required for Brazil.

Fields With Translate Edits:

The SQL1 provided must return ‘Y’ for TRANSLATE_EDIT value for the corresponding COUNTRY, FIELDNAME.


ADDR_FIELD1 for Spain (ESP) is based on a translate edit.

Note: For List of Translates Values for a particular field use SQL2.

Fields With Prompt Table Edits:

The SQL1 provided must return ‘Y’ for PROMPT_EDIT value for the corresponding COUNTRY, FIELDNAME.


STATE field for BRA (Brazil) has a prompt table of STATE_TBL. The key fields on the prompt table are COUNTRY, STATE.
CITY field for BRA (Brazil) has a prompt table of CODE_CITY_BRA. The key fields on the prompt table are STATE, CITY.

Note: For List of Values for a particular field use the prompt table (PROMPT_TABLE) with the associated prompt table keys (PROMPT_TABLE_KEYS).

Other Scenario:

If a field exists in PS_EO_ADDR_FMT_TBL and does not fall into any of the above scenarios then there is NO field validation.

SQL1 - Address Format Sub Page Information Based On PeopleTools MetaData:

SELECT Substr(A.pnlname, 9, 3) AS COUNTRY,
         WHEN Bitand(B.useedit, 512) > 0 THEN 'Y'
         ELSE 'N'
         WHEN Bitand(B.useedit, 16384) > 0 THEN 'Y'
         ELSE 'N'
       edittable AS PROMPT_TABLE,
       (SELECT Listagg(Y.fieldname, ', ')
                 within GROUP (ORDER BY y.fieldnum) "PROMPT_TABLE_KEYS"
        FROM   psrecfield Y
        WHERE  Y.recname = B.edittable
               AND Bitand(Y.useedit, 1) > 0
        GROUP  BY Y.recname)   AS PROMPT_TABLE_KEYS
FROM   pspnlfield A,
       psrecfield B
WHERE  A.pnlname IN (SELECT X.pnlname
                     FROM   ps_eo_addr_subpage X)
       AND A.recname <> ' '
       AND A.fieldname <> ' '
       AND MOD(A.fielduse, 2) = 0
       AND A.recname = B.recname
       AND A.fieldname = B.fieldname
--and substr(A.PNLNAME, 9,3) = 'BRA'
ORDER  BY country,

SQL2 – List Translate Values For A Field:

SELECT A.fieldname,
       A.xlatlongname  AS DESCR,
       A.xlatshortname AS DESCR_SHORT
FROM   psxlatitem A
WHERE  A.fieldname = 'ADDR_FIELD1'
       AND A.effdt = (SELECT Max(A_ED.effdt)
                      FROM   psxlatitem A_ED
                      WHERE  A.fieldname = A_ED.fieldname
                             AND A.fieldvalue = A_ED.fieldvalue
                             AND A_ED.effdt <= SYSDATE)
       AND A.eff_status = 'A';

Sunday, September 6, 2015

Oracle SQL: String Aggregation to Convert Rows of Data into a Single Column - LISTAGG Function

I recently learnt about this cool Oracle SQL function called LISTAGG. I am sure some of you must have come across this function already. I found it very useful for a lot of cases to perform string aggregation to convert rows of data into a single column.

Here is an example to aggregate multiple rows of fields for a particular record (PSRECFIELD) into a single column.

SQL which returns multiple FIELDNAME rows per RECNAME on PSRECFIELD:


Now let us convert these rows of fields into a single column using string aggregation (LISTAGG).

SQL using LISTAGG which returns one row with all fields per RECNAME on PSRECFIELD:


We can see that the list of fields were all aggregated and separated by a comma. This function is very useful for several reasons. I use the above SQL specifically for selecting all columns from a particular table/view instead of having to type them out by hand.

E.g.: I can copy the results of Field_Listing column and use it in my select statement.


Another good example:

SQL using LISTAGG to return list of key fields (comma separated) for particular record definition in a single row.


Thursday, September 3, 2015

PeopleTools 8.54+ - Branding - Part 4C - Customizing DEFAULT_THEME_TANGERINE_ALT Theme (Continued)

This post (Part 4C) is a continuation of one of my previous Branding articles on customizing the DEFAULT_THEME_TANGERINE_ALT theme (Part 4B).

Adding A Content Reference To The Header Using A 'Basic Link':

Previously, we added links to the System Header Links section by using the same approach as some of the delivered System Header Links such as 'Worklist' and 'MultiChannel Console' links. This involved creating our own custom Branding Elements with associated App Classes.

Now, let us use an alternative approach to add links to the Branding Header using a 'Basic Link' and 'Portal/Content URL'.

Let us add the 'My Personalizations' Content Reference - PT_USER_SELF_PERSONAL_GBL - in this example.

Note: Use 'Portal URL' for Content Reference to display within the Portal Frame. Use 'Content URL' for the Content Reference to display without the Portal Frame.

Save the Header Definition.

Test Changes:

Recommendations if you are experiencing caching issues.
  1. Clear local browser cache.
  2. Shutdown, purge and reboot the web server(s).