Using XQuery with PeopleSoft

Like XSLT 1.0, XQuery provides a method for transforming XML. Besides the obvious differences in syntax, XQuery provides additional functionality that doesn't exist in the XSLT 1.0 specification. For example, XQuery not only provides the ability to transform a single XML document into a single output document, like XSLT 1.0, but also adds the ability to merge and/or join multiple input documents into a single result document. I'll let you look up the rest of the differences between the 2 languages. I'm not going to say either is better. They are different and each has its place. The main question I want to answer is, "How can I use XQuery with PeopleSoft?"

To use XQuery from PeopleSoft, you will need to download an XQuery library, install it, and configure your PeopleSoft app server to use it. The example code that follows uses the Saxon XQuery processor. Which version of Saxon you download and how you configure your app server to support Saxon will depend on which version of PeopleTools you are using. This difference is the result of changes made to the Java JAXP API between Java 1.4.2 (PT 8.48 and earlier) and Java 1.5 (PT 8.49). Below, you will find separate configuration sections for the Java 1.4.2 PeopleTools versions and the Java 1.5 PeopleTools version. To find out which Java version your app server uses, execute the following command:

%PS_HOME%\jre\bin\java.exe -version.

When you add the Saxon jars to your classpath, you will be adding a second implementation of the JAXP interfaces to your app server's Java runtime environment. PeopleSoft uses the Apache (Xalan/Xerces) JAXP implementation. Both of these implementations, Saxon and Apache, will register themselves as the default JAXP factory implementation. To ensure that PeopleSoft works correctly after installing the Saxon jars, you need to explicitly set the default JAXP implementation. There are 2 ways to do this: the jaxp.properties file or JVM system properties. In this example, I will give the steps for modifying your app server's JVM system properties, ignoring the jaxp.properties alternative.

To ensure that you configure our app server correctly, you need a way to determine your current JAXP settings. I wrote the following PeopleCode to assist you in configuring your JAXP settings. When run from an IScript, this code will give you the JAXP settings used by your online app server, formatted so that you can copy and paste it into your psappsrv.cfg file, as described in a later step. If you will be using XQuery in your process scheduler server, then you can replace the text %Response.WriteLine with MessageBox, and run this same PeopleCode from an AppEngine program. To run this code online, you will need to create a WEBLIB and IScript and paste this code into your record field PeopleCode:

Function IScript_GetJAXPSystemProperties()
   Local string &XPathFactorySetting = "";
   Local string &XPathFactoryName = "";
   
   try
      &XPathFactoryName = GetJavaClass("javax.xml.xpath.XPathFactory").newInstance().getClass().getName();
      If (All(&XPathFactoryName)) Then
         &XPathFactorySetting = " -Djavax.xml.xpath.XPathFactory=" | &XPathFactoryName | " -Djavax.xml.xpath.XPathFactory:http://java.sun.com/jaxp/xpath/dom=" | &XPathFactoryName;
      End-If;
   catch Exception &e1
   end-try;
   
   %Response.SetContentType("text/plain");
   %Response.WriteLine("-Djavax.xml.transform.TransformerFactory=" | GetJavaClass("javax.xml.transform.TransformerFactory").newInstance().getClass().getName() | &XPathFactorySetting);
End-Function;

After creating your IScript, you can run this code from a URL similar to:

http://<server>:<port>/psc/<site>/EMPLOYEE/<node>/s/WEBLIB_CSS_SAXN.ISCRIPT1.FieldFormula.IScript_GetJAXPSystemProperties

Just replace the parts in < > with your site specific values.

Running this code after installing Saxon should give you the exact same result. If it doesn't, then something in the Saxon jars is overriding the PeopleSoft delivered value. Check your Java VM options to ensure that you have them set correctly. Besides your initial run prior to installing Saxon, I suggest you run this again after you install the Saxon jars and BEFORE you update the psappsrv.cfg file to see how the Factory class implementations change with the presence of the Saxon jars. Then, after you modify your psappsrv.cfg file, you can be sure that the JVM is set correctly.

PeopleTools version specific installation steps:

PT 8.48/Java 1.4.2

1. Download Saxon version 8.9.04 from the Saxon SourceForge file repository
2. Extract saxon8.jar, saxon8-xqj.jar, and saxon8-xpath.jar from the downloaded archive and place them in your %PS_HOME%/class directory.
3. Open your psappsrv.cfg file and find the line that starts with JavaVM Options= and append the value given to you when you ran the function IScript_GetJAXPSystemProperties. It should look something like: -Djavax.xml.transform.TransformerFactory=org.apache.xalan.processor.TransformerFactoryImpl.
4. Restart your app server

PT 8.49/Java 1.5

1. Download Saxon version 9.1.0.1 from the Saxon SourceForge file repository
2. Extract saxon9.jar and saxon9-xpath.jar from the downloaded archive and place them in your %PS_HOME%/class directory.
3. Open your psappsrv.cfg file and find the line that starts with JavaVM Options= and append the value given to you when you ran the function IScript_GetJAXPSystemProperties. It should look something like: -Djavax.xml.transform.TransformerFactory=org.apache.xalan.processor.TransformerFactoryImpl -Djavax.xml.xpath.XPathFactory=com.sun.org.apache.xpath.internal.jaxp.XPathFactoryImpl -Djavax.xml.xpath.XPathFactory:http://java.sun.com/jaxp/xpath/dom=com.sun.org.apache.xpath.internal.jaxp.XPathFactoryImpl.
4. Restart your app server

Downloading jars and placing them in the class path is standard practice when adding new Java libraries to a PeopleSoft implementation. Step 3 above, however, is unique. By specifically setting the JAXP system properties in the psappsrv.cfg file, we force the JVM to use the correct JAXP factories regardless of the order in which the JVM loads our Saxon/Apache jar files.

The following PeopleCode demonstrates how to execute an XQuery from PeopleCode. You will notice that the following PeopleCode references an HTML object named RSS_XQ. You can download the HTML for this HTML object (really, XQuery source, not HTML) from my online repository: rss2_html_obj.xq

Function ExecXQuery(&xquery As string) Returns string
   Local JavaObject &jConfig = CreateJavaObject("net.sf.saxon.Configuration");
   Local JavaObject &jClass = GetJavaClass("java.lang.Class");
   
   Local JavaObject &jStaticContext = CreateJavaObject("net.sf.saxon.query.StaticQueryContext", &jConfig);
   
   rem ** use Java reflection to call the compile method;
   rem Local JavaObject &jExp = &jStaticContext.compileQuery(GetHTMLText(HTML.RSS_NYTIMES_BUSINESS_XQ));
   Local JavaObject &jCompileArgTypes = CreateJavaObject("java.lang.Class[]", &jClass.forName("java.lang.String"));
   Local JavaObject &jCompileMethod = &jStaticContext.getClass().getDeclaredMethod("compileQuery", &jCompileArgTypes);
   Local JavaObject &jExp = &jCompileMethod.invoke(&jStaticContext, CreateJavaObject("java.lang.Object[]", &xquery));
   
   Local JavaObject &jOutputProperties = CreateJavaObject("java.util.Properties");
   rem ** set any output properties like encoding, method, etc;
   
   Local JavaObject &jResultWriter = CreateJavaObject("java.io.StringWriter");
   Local JavaObject &jStreamResult = CreateJavaObject("javax.xml.transform.stream.StreamResult");
   Local JavaObject &jDynamicContext = CreateJavaObject("net.sf.saxon.query.DynamicQueryContext", &jConfig);
   
   &jStreamResult.setWriter(&jResultWriter);
   
   rem PeopleCode engine thinks &jExp is java.lang.Object, not net.sf.saxon.query.XQueryExpression so we need to continue to use reflection;
   rem &jExp.run(CreateJavaObject("net.sf.saxon.query.DynamicQueryContext", &jConfig), &jStreamResult, &jOutputProperties);
   Local JavaObject &jExpressionRunMethod = &jExp.getClass().getDeclaredMethod("run", CreateJavaObject("java.lang.Class[]", &jDynamicContext.getClass(), &jClass.forName("javax.xml.transform.Result"), &jOutputProperties.getClass()));
   &jExpressionRunMethod.invoke(&jExp, CreateJavaObject("java.lang.Object[]", &jDynamicContext, &jStreamResult, &jOutputProperties));
   
   Return &jResultWriter.toString();
End-Function;

Function IScript_ExecXQuery()
   %Response.Write(ExecXQuery(GetHTMLText(HTML.RSS_XQ, "http://www.nytimes.com/services/xml/rss/nyt/Business.xml")));
End-Function;

As you can see, I wrapped the XQuery transformation in a function called ExecXQuery. The Saxon XQuery classes use overloads that can't be interpreted by the PeopleCode interpreter. To work around this, I had to use some Java reflection. Chris Heller did an excellent job of explaining Java reflection and PeopleCode in his post Java and PeopleCode Tips and Tricks - Part 2.

In this PeopleCode example, I've only scratched the surface of what you can do with Saxon and XQuery. Saxon includes methods for running queries to return lists, methods for dynamically setting input documents, etc. I'll let you investigate the power of Saxon. If you just want to execute xqueries as described in this post, then I suggest you add the ExecXQuery function to a FUNCLIB. Once you have the function in a FUNCLIB, you can call it from Integration Broker PeopleCode transformations. Likewise, you can use it to create a custom Pagelet Wizard data source or transformer. Using a delivered data source like HTML, you could place an XQuery in the HTML text box, and then apply an XQuery display type to that HTML data source to execute the XQuery. Creating a Pagelet Wizard XQuery transformer would allow you to execute XQueries against data sources like content management, news publications, integration broker, PeopleSoft queries, HTML, etc. Unlike an XSL transform, using XQuery, you could merge content from a news publication with other online documents.

If you plan to use other Saxon features, then you may want to create an app package with app classes to encapsulate PeopleCode interfaces to Saxon's XQuery Java API.

Oracle OpenWorld, 2008

Are you ready for another outstanding OpenWorld conference? I am! If you are interested in digging deeper into PeopleTools and seeing some good PeopleTools demonstrations, sign up for my PeopleTools Advanced Tips and Techniques session. In this session, I'll be squashing the myth, "You can't do that with PeopleTools." This session is currently slated for Tuesday from 5:00pm - 6:00pm. Likewise, if you are interested in learning more about the best Enterprise class portal ever created, put one of these PeopleSoft Enterprise Portal sessions on your agenda (The IntraSee presentation, S300269, is outstanding!).

Do you have a specific PeopleTools question for me? Stop by the PeopleTools user interface demo pod in the demo grounds. I'll be working regular shifts at that demo pod throughout the conference.

Blogger Bots Marked my Blog as Spam

Sorry I haven't posted for a while. The blogger.com bots marked this blog as a spam blog. Blogger's help file says, "spam blogs... can be recognized by their irrelevant, repetitive, or nonsensical text." That must be it. Anyway, I apologize for any page I took out of service during this downtime. While out of service, I made the mistake of editing a page. Since blogger.com wouldn't let me publish that page, I had to save it as a draft. By saving it as a draft, the page was pulled from my site. Go figure.

While my blog was locked, I wrote up a few posts and saved them as drafts. To save as drafts, I had to enter those "word verification" characters. Those are fun... On several occasions, I had to try 3+ times before I finally got a character combination I could enter successfully.

Export PeopleSoft Attachments using PL/SQL

Oracle provides PeopleTools developers with the ability to store files with transactions using the File Attachment API. Likewise, the File Attachment API includes PeopleCode functions for extracting and displaying attachments. What if you need to export attachments directly from a database or from SQR where you don't have access to the File Attachment PeopleCode functions? Here is some PL/SQL demonstrating how to export attachments. You just need to set the file name and the attachment record name. I labeled the values you need to change with TODO:. Code for other databases should be relatively similar.

CREATE OR REPLACE DIRECTORY TEMP_DIR AS 'c:\temp'
/

DECLARE
  -- Max PS attachment chunk size is 28000
  CV_BUFFER_MAX    NUMBER        := 28000;

  -- Name of file to export. This is the name that was used to store the file
  -- and is the name that will be used to create a new file.
  -- TODO: change the name to match the name of your exported file
  lv_file_name     VARCHAR2(128) := 'theattachedfile.xls';
  lv_buffer        RAW(28000);

  lv_file_ref      utl_file.file_type;
  
BEGIN
  lv_file_ref := utl_file.fopen('TEMP_DIR', lv_file_name, 'WB');

  FOR r_chunks IN
      (SELECT FILE_DATA
            , FILE_SIZE
     -- TODO: Change record to the name of your attachment record
         FROM PSFILE_ATTDET
        WHERE ATTACHSYSFILENAME = lv_file_name) LOOP

    dbms_lob.read(r_chunks.FILE_DATA, r_chunks.FILE_SIZE, 1, lv_buffer);
    utl_file.put_raw(lv_file_ref, lv_buffer, true);
  END LOOP;

  utl_file.fclose(lv_file_ref);

  EXCEPTION
    WHEN OTHERS THEN
    -- Close the file if something goes wrong.
      IF UTL_FILE.is_open(lv_file_ref) THEN
        UTL_FILE.fclose(lv_file_ref);
      END IF;
    RAISE;
END;
/

You will notice that this code exports an attachment to the database server's file system.

How do you find file attachment records? The easiest way I can think of is to query the PSRECFIELD table for all record definitions that contain the FILE_ATTDET_SBR sub record. Here is the SQL:

SELECT *
  FROM PSRECFIELD
 WHERE FIELDNAME = 'FILE_ATTDET_SBR'

AppEngine Output Tricks, Reporting, Logging, Etc

Reporting

When I took the AppEngine course several years ago, my instructor made sure his students knew that AppEngine was a batch processing tool, not a reporting tool, unlike SQR, which could do both. At that time, PeopleSoft offered Crystal Reports, PS/nVision, PS Query, and SQR as reporting options, and he encouraged us to use those tools for reporting. AppEngine was strictly labeled a batch processing tool. While debugging some jobs containing AppEngines (dunning letters, training letters, etc), I noticed that these AppEngines created and/or read files from the process output directory. Looking at the process monitor, I knew that those same files were available from the View Log/Trace link on the process details page. This got me thinking... if I could create a Microsoft Word file from AppEngine, I could place it in that process output directory and not have to run the WINWORD process on a headless server. Now, the trick, creating a Microsoft Word file from an AppEngine... Here are a couple of options

• Word HTML format
• Word XML format
• RTF

By using various methods, I can convert my mail merge source data into XML format and transform it into either of these three Microsoft Word recognized formats using XSL. Of course, as of PeopleTools 8.48, we can use XMLPublisher to generate the same result. Nevertheless, if you need to process your data prior to generating a report, then a multi-step AppEngine reporting solution might be easier for you to manage than a multi-step job.

The same options are available for creating Microsoft Excel and OpenOffice documents. If you want to create Microsoft Excel binary files from AppEngine, then you can use Apache's POI Java libraries from PeopleCode. If you are interested in creating OpenOffice documents, you can generate the appropriate XML files, and then use Java to zip them into a single file. In fact, you could use this same approach to generate OpenOffice Impress presentations or Microsoft PowerPoint 2007 presentations.

For reporting, why choose AppEngine over SQR? AppEngine components (PeopleCode, SQL, etc) are managed objects. PeopleTools managed objects participate in the change management features available in PeopleTools. SQR text files do not.

Can I create a PDF from an AppEngine? Yes. Using an XSL-FO processor, you can trasform XML into PDF using a user defined XSL template. Likewise, you can use one of the PDF Java libraries to print text to a PDF file using PeopleCode similar to the way you would print output to a PDF in SQR, but with rich text features. Other reporting options: Any reporting/output tool that has a Java API can be called from AppEngine PeopleCode. For example, JasperReports, BIRT, JFreeReport, FOP, etc.

File Output Location

Suppose I want to create a file (printable report, log file, etc), where should I create the file? If you want the file available from the View Log/Trace link, then use the following SQL to determine the process's output directory:

SELECT PRCSOUTPUTDIR FROM PSPRCSPARMS WHERE PRCSINSTANCE = %ProcessInstance

Logging

I've already mentioned using log4j from PeopleCode. You can read about that in my posts: Logging PeopleCode Using log4j to debug applications and log4j and PeopleCode Part II. Other options include the Peoplecode MessageBox function, the PeopleCode File object, and the Java System.out/System.err methods. I prefer the Java System output methods over the PeopleCode MessageBox function because Java gives me complete control over the output. Unfortunately, you can't call the Java System output methods directly because the PrintStream output methods are overloaded. Instead, we need to use reflection to call the print methods. Here are some functions you can place in a FUNCLIB that allow you to print to stdout and stderr from PeopleCode:

/*
 * Print a line of text to stdout
 */
Function println_to_stdout(&message As string)
   Local JavaObject &jSystem = GetJavaClass("java.lang.System");
   Local JavaObject &jOutStream = &jSystem.out;
   Local JavaObject &jCls = GetJavaClass("java.lang.Class");
   Local JavaObject &jStringClass = &jCls.forName("java.lang.String");
   Local JavaObject &jPrintStreamCls = &jOutStream.getClass();
   Local JavaObject &jPrintlnArgTypes = CreateJavaObject("java.lang.Class[]", &jStringClass);
   
   Local JavaObject &jPrintlnMethod = &jPrintStreamCls.getDeclaredMethod("println", &jPrintlnArgTypes);
   
   &jPrintlnMethod.invoke(&jOutStream, CreateJavaObject("java.lang.Object[]", &message));
   rem ** I didn't find flushing necessary, but here is where you would flush the buffer if desired;
   rem &jOutStream.flush();
End-Function;

/*
 * Print a line of text to stderr
 */
Function println_to_stderr(&message As string)
   Local JavaObject &jSystem = GetJavaClass("java.lang.System");
   Local JavaObject &jOutStream = &jSystem.err;
   Local JavaObject &jCls = GetJavaClass("java.lang.Class");
   Local JavaObject &jStringClass = &jCls.forName("java.lang.String");
   Local JavaObject &jPrintStreamCls = &jOutStream.getClass();
   Local JavaObject &jPrintlnArgTypes = CreateJavaObject("java.lang.Class[]", &jStringClass);
   
   Local JavaObject &jPrintlnMethod = &jPrintStreamCls.getDeclaredMethod("println", &jPrintlnArgTypes);
   
   &jPrintlnMethod.invoke(&jOutStream, CreateJavaObject("java.lang.Object[]", &message));
   rem ** I didn't find flushing necessary, but here is where you would flush the buffer if desired;
   rem &jOutStream.flush();
End-Function;

If you want to use the PrintStream.print method instead of the println method, copy the code above, rename the function, and change the &jPrintlnMethod assignment from "println" to "print".

If you've worked with Java, then you know that you can redirect stdout and stderr to another PrintStream. For example, you can redirect stdout to a file or a network socket connection. Here is some code demonstrating how to redirect stdout and stderr to a different file:

/*
 * Redirect stdout to file
 */
Function redirect_stdout(&fileName as string)
   Local JavaObject &jSystem = GetJavaClass("java.lang.System");
   Local JavaObject &jfos_out = CreateJavaObject("java.io.FileOutputStream", &fileName, True);
   Local JavaObject &jps_out = CreateJavaObject("java.io.PrintStream", &jfos_out, True);
   &jSystem.setOut(&jps_out);
End-Function;

/*
 * Redirect stderr to file
 */
Function redirect_stderr(&fileName as string)
   Local JavaObject &jSystem = GetJavaClass("java.lang.System");
   Local JavaObject &jfos_out = CreateJavaObject("java.io.FileOutputStream", &fileName, True);
   Local JavaObject &jps_out = CreateJavaObject("java.io.PrintStream", &jfos_out, True);
   &jSystem.setErr(&jps_out);
End-Function;

By redirecting stdout and stderr, you could actually create 3 separate output files without using the File object. The benefit of using a redirected stdout over a File object is that you can setup your stdout location in one step of your program and write to that same file from anywhere else in the program without having to open/close a File object on every step.

The App Server

Just a side note: Many of the techniques demonstrated in this post can be used online. Using System.out.println, you could print to the app server's stdout file. Likewise, the reporting solutions above could be used from an online PeopleCode event to generate reports online.

Keep the Last Visited Homepage Tab Active

If you have used PeopleSoft's Enterprise Portal, then you may have noticed that the active homepage tab is only active when you are viewing that specific homepage tab and that none of the homepage tabs are active when you navigate away from a homepage. As a new PeopleSoft user, I didn't understand why none of the tabs were active once I navigated away from a homepage. To me, it seemed that keeping the last visited homepage tab visibly differentiated as the active tab helped me know what portion of the application I was using after I left a homepage. For example, if I was creating a purchase requisition, then I wanted the Procurement tab to stay active. Likewise, if I was modifying a branding header, then I wanted the Administration tab to stay active. As I continued to work with the PeopleSoft applications, however, I realized that my active tab navigation paradigm didn't make sense given the fact that all the pages inside my PeopleSoft Enterprise Portal displayed the same Enterprise Menu. Because the Enterprise Menu exists on every page, it would be possible for someone to start on a Manager Self Service tab, initiate an employee transfer, and then use the Enterprise Menu to navigate to the purchase requisition component, a business process better aligned with the Procurement tab. In this scenario, the active tab would still be Manager Self Service even though the user is entering a purchase requisition.

At Collaborate '08, a customer asked me, "How do you keep the active tab active after you navigate away from a homepage?" Since I had been through this thought process myself, I answered him with the explanation above. Nevertheless, because PeopleSoft is so flexible, it is possible for you to implement your Enterprise Portal in a manner that separates Procurement content from Manager Self Service content. For example, you could change the default content reference template from DEFAULT_TEMPLATE to MY_CUSTOM_MENU_TEMPLATE and use a custom menu implementation that shows content targeted to the active tab. If you implemented PeopleSoft's Enterprise portal in this manner or if keeping the last visited tab active just makes sense to you, then here is the code to help you keep that last visited tab active. You will need to add this code to the HTMLProcess method of the HTMLProcess class contained in the EPPBR_BRANDING Application Package (the constructor of the EPPBR_BRANDING:HTMLProcess application class). I included seven lines above the modification and seven lines below the modification to help you find the correct place to add this code (14 lines of context). The code you need to add is the 6 lines in bold below between the 7 lines of delivered code.

   ...
   &strRefreshHomePage = "";
   &strContentHref = "";
   &strLayoutHref = "";
   &dEffdt = %Date;
   /* override effdt is for editing only, the value should be set when called from configuring site overrides */
   &OverrideEffdt = &dEffdt;
   &strActiveTab = &Request.GetParameter("tab");

   REM ** BEGIN ABC_123456; me@mycompany.com; 14-MAY-2008; keep last active tab active;
   Local string &tempActiveTab = &strActiveTab;
   If (None(&tempActiveTab)) Then
      &strActiveTab = %HPTabName;
   End-If;
   REM ** END ABC_123456; me@mycompany.com; 14-MAY-2008; keep last active tab active;

   If &strLocation = "M" Then
      &strHdrFtrType = "P";
   Else
      &strHdrFtrType = &strLocation;
   End-If;
   
   If &strLocation = "P" And
   ...

As you can see from the 7 lines preceding the modification, the delivered behavior is to determine the active tab from the tab query string parameter that exists on homepage URL's. The reason the active tab doesn't stay active when you navigate to content surfaced through your Enterprise Portal is because the tab query string parameter doesn't exist on those other pieces of content. To work around this, we can use the %HPTabName system variable to determine the most recently active tab.

If you make this change, you will be modifying code delivered by Oracle. If you document your modification, then you should be able to carry this modification through bundles and upgrades with little impact. The key to a successful modification is documentation. By documentation I mean, you include this modified PeopleCode in a project and you document the PeopleCode change within the PeopleCode object. At bundle/upgrade time, your compare reports will show the PeopleCode changes line by line. If you documented the start and end of your modification with a PeopleCode comment and include a site specific identifier in that comment, then it should be easy for you to find that modification and reapply it. For example, in the code above, I use ABC_123456 as my site specific identifier. The ABC portion identifies the code as created by my company, not Oracle, and the 123456 portion is the modification specific identifier used by me to track this specific modification.

%HPTabName uses a cookie to determine the active tab if the URL does not contain a tab query string parameter. If a user enters the PeopleSoft application through a link from a workflow e-mail, then that user will see an active tab matching the last tab visited, which may or may not be relevant for the URL specified in the workflow e-mail. Likewise, if the user cleared his or her cookies, then that user would not have an active tab. You could work around this limitation by creating your own method for tracking and setting the active tab. For example, if the target is not a homepage (no tab query string parameter and/or the presence of /h/ in the URL), then, using PeopleCode functions and system variables, you could determine the node that provides the target content and change the active tab accordingly. Another idea would be to set a default tab attribute on every relevant content reference and use that attribute to determine which tab should be the active tab for the target URL.

Corrections for Yesterday's Custom Transformer Post

If you read yesterday's post, Simplify HTML Pagelet Migration with a Custom Transformer, sometime between 10:54PM PDT yesterday and 11:21AM PDT today, I apologize. This morning I noticed I had forgotten to post the last step in configuring a custom Transformer, the step where you associate the Transformer with a data type. For my observant readers, you will probably notice that I also fixed a few minor grammatical errors. Rather than post corrections, etc, that will be difficult to associate to the original document, I just corrected the document in place. I replaced the original post with the corrected version.

Simplify HTML Pagelet Migration with a Custom Transformer

Generally speaking, IT organizations like to keep development code separate from production code. When development code is ready for production, good change management rules dictate a graduation/migration path, usually something like DEV > TEST > QA > PRO. Likewise, on occasion, a development team may request a fresh copy of a production database. With PeopleTools managed objects, we can easily migrate Application Designer projects between our environments without much concern for environment specific code. Pagelet Wizard created pagelets, however, are not managed objects and need to be migrated using Data Mover Scripts. Some pagelets, like Query pagelets, require managed objects in order to function. Other pagelets, like HTML pagelets may contain hard coded URL references to other DEV/TEST/QA/PRO servers. How can we effectively manage these URL's? Can we, as PeopleSoft developers, automate the process for updating these URL's?

As we know from experience, HTML data sources typically use the Passthru display format. Passthru means the HTML content from step 2 is displayed in the Pagelet without any processing or modification. Therefore, we can't leverage Meta-HTML or any other PeopleTools functionality to simplify the management of our HTML based pagelet. We can, however, create a new transformer and display type and have this new transformer perform additional processing to help us resolve this URL maintenance problem.

Now that we have a method for implementing additional processing, how shall we maintain the actual URL's? I recommend using node definitions. Node definitions contain a portal tab where we can enter a content and a portal URL. As delivered, PeopleTools uses these node URL's when creating real URL's from portal registry content references (CREF's). Using a custom transformer, we can leverage this existing Meta-data to dynamically generate the URL's included in HTML Pagelets.

What we need is a custom Transformer that can convert a URL placeholder into a full URL using database driven Meta-data. Keeping with PeopleTools convention, the solution below will demonstrate the creation of a transformer that resolves custom Meta-HTML tags. This custom transformer will expand the text %NodePortalURL(NODE_NAME) into the Portal URL for that node. This will allow us to write Pagelet Wizard HTML like:

<img src="%NodePortalURL(NODE_NAME)/images/dynamic-chart.png"/>

And our custom transformer will translate this HTML into:

<img src="http://other.server.name:8080/images/dynamic-chart.png"/>

To implement this solution we need an Application Package to store our custom App Class. For demonstration purposes, we will call this Application Package CUSTOM_TRANSFORMERS. I expect in your environment, you will prefix this new Application Package with your site specific prefix. In this new Application Package, we will add a new package called Transform. And, to this new package, we will add the class NodePortalUrlTransformer. This new class, NodePortalUrlTransformer will contain the code required to implement our URL transformation feature. The path for our new Application Class should look something like CUSTOM_TRANSFORMERS:Transform:NodePortalUrlTransformer. Here is the code you will need to paste into this new Application Class:

import PTPPB_PAGELET:UTILITY:*;
import PTPPB_PAGELET:Transformer:*;
import PTPPB_PAGELET:*;

/**
  * Transforms Text by replacing %NodePortalURL(NODE_NAME) with the Portal URI
  *   of the node NODE_NAME.
  */
class NodePortalUrlTransformer extends PTPPB_PAGELET:Transformer:Transformer
   method NodePortalUrlTransformer(&ID_param As string);
   method execute(&pageletID As string) Returns string;
   method Clone() Returns PTPPB_PAGELET:Transformer:Transformer;
end-class;

/** 
  * Constructor.
  *
  * @param id_param ID of this object.  Should be unique.
  */
method NodePortalUrlTransformer
   /+ &ID_param as String +/
   %Super = create PTPPB_PAGELET:Transformer:Transformer(&ID_param);
end-method;

/**
  * Replaces%NodePortalURL(NODE_NAME) with the Portal URI
  *   of the node NODE_NAME.
  *
  * @param pageletID ID of the pagelet being executed.
  */
method execute
   /+ &pageletID as String +/
   /+ Returns String +/
   /+ Extends/implements PTPPB_PAGELET:Transformer:Transformer.execute +/
   Local JavaObject &pattern;
   Local JavaObject &matcher;
   Local string &sourceText = %This.DataToTransform.Value;
   Local string &transformedText = &sourceText;
   Local string &nodeName;
   Local string &nodeUrl;
   
   REM ** Resolve %NodePortalURL tags;
   &pattern = GetJavaClass("java.util.regex.Pattern").compile("(?i)%NodePortalURL\((\w+)\)");
   &matcher = &pattern.matcher(CreateJavaObject("java.lang.String", &sourceText));
   While &matcher.find()
      &nodeName = &matcher.group(1);
      If (&nodeName = "LOCAL_NODE") Then
         &nodeName = %Node;
      End-If;
      SQLExec(SQL.GET_NODE_URI, &nodeName, "PL", &nodeUrl);
      &transformedText = Substitute(&transformedText, &matcher.group(), &nodeUrl);
   End-While;
   &pattern = Null;
   Return &transformedText;
end-method;

/**
  * Make an exact copy of this object.
  *
  * @return Text Exact copy of this object
  */
method Clone
   /+ Returns PTPPB_PAGELET:Transformer:Transformer +/
   /+ Extends/implements PTPPB_PAGELET:Transformer:Transformer.Clone +/
   Return create CUSTOM_TRANSFORMERS:Transform:NodePortalUrlTransformer(%This.ID);
end-method;

Keeping with good PeopleTools coding practices, the code above references a managed SQL object named GET_NODE_URI. Here is the SQL for that SQL definition:

SELECT URI_TEXT 
  FROM PSNODEURITEXT 
 WHERE MSGNODENAME = :1 
   AND URI_TYPE = :2

Now that we have our code in place, we need to register our new Transformer so we can use it in our HTML pagelets. Rich Manalang wrote up an excellent transformer tutorial following the same steps, but his includes pictures. You can find a link to his tutorial below.

We will start by registering the Transform Type. Navigate to:

Portal Administration > Pagelets > Pagelet Wizard > Define Transform Types > Add

On this page, you will need to give your Transform Type a name, a description, and specify the implementing Application Class. If you have been following this example, then use the following values:

Transformation Type: NODE_PORTAL_URL
Description: Resolve Node URL Meta-HTML
Package Name: CUSTOM_TRANSFORMERS
Path: Transform
Application Class ID: NodePortalUrlTransformer

Next, you will need to define a Display Format that corresponds to your new Transform Type. Navigate to:

Portal Administration > Pagelets > Pagelet Wizard > Define Display Formats > Add

You can use the following values to define your new display format. Again, the Define Display Formats graphic on Rich's post applies. In fact, the only values you need to change are the Display Format ID, Description, and Transform Type. For reference, I've repeated the values below:

Display Format ID: NODE_PORTAL_URL
Description: Resolve Node URL Meta-HTML
Transform Type: NODE_PORTAL_URL
Page Name: PTPPB_WIZ_DISP_PST
Package Name: PTPPB_PAGELET
Path: TransformBuilder
Application Class ID: PassthroughBuilder

The last step is to associate our new Display Format with the HTML Data Type. Navigate to:

Portal Administration > Pagelets > Pagelet Wizard > Define Data Types

Select the HTML Data Type. At the bottom of this page, in the section titled Display Formats to use with this Data Type, we need to add our new Display Format: NODE_PORTAL_URL. After you save, your configuration and development for your new transformer is complete. You may now use this new Meta-HTML character sequence in your Pagelet Wizard HTML Pagelets.

The Enterprise Portal's Pagelet Wizard is one of my favorite productivity and usability tools. One of it's key features is its extensibility. The Pagelet Wizard contains components that allow you to register your own data sources, display types, and transformations. Likewise, the Internet Technology PeopleBook contains a section devoted to Pagelet Wizard configuration, customization, and usage. If you prefer examples over PeopleBooks, you can open and view the delivered Pagelet Wizard Application Packages in Application Designer. If you are interested in creating your own data source, tranformer, or display type, you may find a delivered Application Class you can copy and modify.

Additional resources:

• Enterprise PeopleTools 8.49 PeopleBook: Internet Technology > Using Pagelet Wizard
• Rich Manalang's post Markdown Text Filtering for PeopleSoft

PIA_KEYSTRUCT JavaScript Object

I was just looking at the JavaScript that PeopleSoft includes in each PIA page and noticed a JavaScript object I have not seen before: the PIA_KEYSTRUCT object. It looks like this object contains key/value pairs for each level 0 search key, primary and alternate. What can you do with this? I'm not sure yet. I've been thinking about it for a couple of weeks. My two main questions:

1. Can I use this to create new developer tools (bookmarklets, etc)?
2. Can I use this to empower users?

For now, the only example I can show you is how to open a query and pass in the PIA_KEYSTRUCT values as query bind parameters. This example passes the Role Administration component's ROLENAME key to a delivered query that displays the role's permission lists. Here is the JavaScript:

window.open("/psc/portal/EMPLOYEE/EMPL/q/?ICAction=ICQryNameURL=PUBLIC.PT_SEC_ROLE_CLASS&BIND1=" + escape(frames['TargetContent'].PIA_KEYSTRUCT.ROLENAME))

In the example above, I navigated to PeopleTools > Security > Permissions & Roles > Roles and ran this JavaScript in the Firebug console. Here is a bookmarklet that will do the same thing: Role's Permission Lists. To test this bookmarklet, right click the link and save it as a favorite/bookmark, navigate to your Role administration page, select a role, then activate your new favorite/bookmark. One of the great features about bookmarklets is that they can be added to your browser's links toolbar to "extend" the functionality of your browser. Unfortunately, this particular example is page specific. This particular bookmarklet and code will only work if you are on the PeopleTools Role administration page. Furthermore, you can provide the same functionality by combining PeopleCode with JavaScript in an HTML area or, even better, using a Pushbutton/Hyperlink on a PIA page with full access to the level 0 search keys (in other words, this is a great pedagogical example with little practical value).

Really, as developers, can we obtain practical value from this little piece of JavaScript that the PeopleTools developers generously provided for us? I think so. Here are a couple of ways I think we can leverage this piece of JavaScript:

• Enhance the Ctrl-J page by adding the level 0 search keys
• Simplify the Pagelet Wizard link creation process

Collaborate '08 Presentation Available

Quest's Collaborate '08 presentations are now available for download. You can download the presentation I gave from the Quest website: PeopleTools Advanced Tips and Techniques. Our PDF printer software prints landscape, so you will want to rotate the presentation clockwise for optimal viewing.

My Collaborate '08 Schedule

Are you planning to attend Collaborate '08 in Denver? If so, stop by and see me. I'll be staffing the PeopleTools demo pod on Tuesday from 5:30 to 7:30 and Wednesday from 1:00 to 3:30. Likewise, if you are interested, I'll be presenting PeopleTools Advanced Tips and Techniques on Thursday, April 17th from 9:45 AM - 10:45 AM in room 501 (schedule subject to change, so please check the daily changes in case my session gets moved).

The Power of Meta-Data

In my last post, What is a WEBLIB, I said that WEBLIB_QUERY was in permission list PTPT1000. How did I know that? I queried the PeopleTools tables. PeopleTools tables are tables in your database prefixed with PS, but no underscore. As you know, generally speaking, all Application Designer created records are prefixed with PS_. Most of the PeopleTools record names, however, don't contain underscores. If you haven't done so, I encourage you to take some time to get to know your PeopleTools tables. You can find a good reference on the PSST0101 site. With a good understanding of the PeopleTools tables, you can write queries and tools to help you significantly reduce your PeopleSoft administration overhead. Combining your knowledge of the PeopleTools tables with IScripts and Web development methods, you can create Bookmarklets that generate security and/or development/meta-data reports for components, etc.

Meta-data is one of PeopleSoft's differentiating factors. Many vendors provide meta-data in configuration files, but because a meta-data driven development architecture requires extra thought, most ERP vendors drop meta-data in favor of rapid, hard-coded values. Because PeopleSoft stores meta-data in the database, you, as a developer, user, and administrator, can extend PeopleSoft by writing your own tools that leverage existing meta-data.

Caveat: Please don't update the PeopleTools Meta-Data tables directly, unless, of course, told to do so by GSC or some other trustworthy Oracle/PeopleSoft expert.

What is a WEBLIB?

I recently posted about IScripts, and in that post, I mentioned the term WEBLIB. The next logical question after "What is an IScript" is, "What is a WEBLIB?" Simply put, a WEBLIB is a container for an IScript. The term WEBLIB has a history somewhat like the Menu definition. It is a legacy artifact that really isn't necessary but continues to exist because we implemented it that way in the beginning, when the way we implemented it was the most logical way to implement it (before Application Packages, etc). A WEBLIB is similar to a FUNCLIB. The only difference between a WEBLIB and a FUNCLIB is the name... and the way PeopleSoft treats the code within the record defintion. Just like a FUNCLIB, something you studied in PeopleCode class, a WEBLIB is a Derived/Work record definition with a special name. When you prefix a record with WEBLIB_, you are telling the PeopleSoft application code to allow people to access the functions defined within that record. Just like any other record defined function, functions defined in records prefixed with WEBLIB_ can be declared and called from any PeopleCode. What makes WEBLIB_ functions unique is that they can also be called from a URL (see my previous post on IScripts). Therefore, the term WEBLIB describes the type of record used to store a special type of PeopleCode function, an IScript.

Because WEBLIB's can be accessed from a browser using a specially formed URL, we need to secure them. Just like standard components, you define WEBLIB security on permission lists. When you open a permission list, you will notice a link across the bottom titled Web Libraries. From this tab, you can specify which WEBLIB functions a permission list (and, indirectly, a user) can access. This is where the magic of the record name prefix appears: only records prefixed with WEBLIB_ can be added to the Web Libraries section of the permission list.

One of the frustrations of the WEBLIB design is the record name requirement. Generally speaking, a PeopleSoft implementation will involve custom record development. An implementation team usually prefixes these custom record names with a company or team abbreviation. This helps the team distinguish custom tables from delivered tables. Because WEBLIB's must start with the prefix WEBLIB_, this is not possible. Instead, the implementation, or development, team is required to move the team specific prefix to the next component of the record name, the part following the WEBLIB_. Now, if the team prefix is 3 characters long including an _ (for example, ADS_), then the developer only has 4 characters left to uniquely and accurately describe this WEBLIB's purpose. Why? Because a record definition can only contain 15 characters and the first 11 are used by system and team required prefixes.

To demonstrate, let's look at a delivered WEBLIB, WEBLIB_QUERY. WEBLIB_QUERY is a Dervied/Work record that you can open in Application Designer. When you open this record, you will notice that it contains 3 fields with PeopleCode. The first 2 fields contain IScript functions, and the 3rd, QRYGENFUNCS, contains FUNCLIB, not ISCRIPT, functions. The difference between the functions in the 3 fields has to do with the function prefix, not the field names. To make a PeopleCode function available from a URL, the function not only has to be contained in a record definition prefixed with WEBLIB_, the specific function name must also be prefixed with IScript_. To see an example of WEBLIB permissions, open the Permission List PTPT100 and switch to the Web Libraries tab. From here, you can see WEBLIB_QUERY and the 2 IScripts contained within that WEBLIB.

Google Gadgets for your PeopleSoft Enterpise Portal

Have you tried using the Pagelet Wizard's HTML data source to add Google Gadgets to your PeopleSoft Homepage? If not, browse through the Google Gadgets directory and select one you like. After customizing your Gadget, click the "Get the Code" button. Notice the code is an HTML script tag. Copy this code and paste it into the HTML editor of a new Pagelet Wizard HTML data source pagelet. Publish, save and add your new pagelet to your homepage. Did you notice anything unusual... like... the Gadget appeared at the top of the homepage, not within the pagelet's designated area? If you look at the source code for your homepage from within your browser, you will notice that the script tag you copied is actually in the homepage's head element, not in the body. This behavior is the result of the way the PeopleTools portal constructs the homepage. For each pagelet on the homepage, the portal retrieves the pagelet's source and reviews it prior to inserting it into the homepage. If the pagelet's source contains a full HTML document (with HTML element, head, and body), then the portal will insert the components of the head tag into the homepage's head tag. For example, if the pagelet's head tag contains style definitions, those style definitions will be inserted into the head tag of the home page. Likewise, if the pagelet's source is not a full HTML document, but contains script, link, or style elements at the root, or top level, then those elements will be inserted into the head tag of the homepage. This behavior is by design. It allows pagelet designers to embed script and style definitions into the head element of pagelet HTML documents, the only valid place for sytle definitions.

To work around the homepage construction issue, wrap the HTML provided by Google inside a <div> tag. This will keep the portal from moving the script element. I use the following HTML to center the gadget within the Pagelet's defined box. Adjust the width to match the width of your Google Gadget plus a pixel or two.

<div style="margin-left: auto; margin-right: auto; width: 325px;">Google script...<div>

Here are some Google Gadgets you will find on my PeopleSoft homepage Google Calendar, Google Talk, and Google News.

Branding Component/Page Tabs

You can find documentation on Customer Connection about branding homepage tabs in Enterprise Portal. But, what about changing the tabs that separate pages in a multi-page component? If you've been through a few PeopleTools upgrades, you've noticed that PeopleSoft occasionally changes the tab colors and tab design, so it must be possible... right? If you save one of those tab images, you will notice that tab images follow a naming convention. Component tabs are called level 2 tabs and are prefixed with PT_TAB2. You will also notice that App Designer contains PT_TAB3 images. I'm not sure where these are used.

Component tabs are comprised of 7 images, all of them ending in a hexidecimal color. The seven images are

• PT_TAB2BAIBBBBBBCCCCCC
• PT_TAB2BIABBBBBBCCCCCC
• PT_TAB2BIIBBBBBB
• PT_TAB2LAXCCCCCC
• PT_TAB2LIXBBBBBB
• PT_TAB2RAXCCCCCC
• PT_TAB2RIXBBBBBB

BBBBBB represents the hexadecimal inactive tab background color and CCCCCC represents the hexadecimal active tab background color. If you haven't changed your default stylesheet, then PTSTYLEDEF_DARKBLUE contains the definition for your active and inactive tab colors. The style classes are PSACTIVETAB and PSINACTIVETAB. If you change the background color of either of these classes, then be sure you have all 7 new images in your database with matching color names. If you don't then your app server session will crash. It appears that the C++ code on the app server determines which image to choose based on the background colors of PSACTIVETAB and PSINACTIVETAB (hence the naming convention). Note also that the images with AI and IA prefixes list the inactive color before the active color.

Level 3 tabs follow the same conventions as level 2 tabs except for the addition of the PT_TAB3MIXBBBBBB image which represents the middle of 2 inactive tabs.

If you choose to change your tab colors to something that isn't delivered, be sure to upload all 15 images (level 2 and level 3). Otherwise, your app server session will crash each time you navigate to a component.

Update Tuesday, March 4^th, 2008

While finishing up a branding project, I noticed that the 2 in PT_TAB2 does not describe the level, but the row. A component's tab set is actually comprised of 3 rows of images, PT_TAB1, PT_TAB2, and PT_TAB3. PT_TAB3 images make up the bottom row of the tabs. You must create a new PT_TAB3 image for your new background color using the naming conventions above. PT_TAB1 images are shared by all background colors, so you do not need to create new PT_TAB1 images for each background color combination.

Get Your Mix Account and Vote

Do you have a Mix account yet? If not, then get one. After you create an account, add the PeopleSoft and PeopleTools groups to your profile. And then, be sure to vote for ideas submitted by your peers. For example, if you want Code Completion in Application Designer or Search by process name when adding process definitions to projects, then vote for it.

There are several good communities for PeopleSoft developers. Why join Mix? Mix has one thing the other communities don't. Mix has PeopleSoft developers and consultants. Yes, the other communities do have some Oracle staff, but not like Mix. Are you looking for a "Meet the Experts" experience all year, not just at OOW? Then try Mix.

Java, XPath, XML and Facebook

PeopleSoft's Integration Broker and Web Services provide an excellent framework for web services integration. But what if your PeopleSoft system needs synchronous access to another system that doesn't publish web services? For example, let's say you need to communicate with a system like Facebook that has a Java client library (yes, Facebook has a REST-based API, but using the existing Java client is much easier than creating all of the messages, handlers, and Facebook specific publishing logic). Even though we can call Java directly from PeopleCode, the Facebook methods return a value of type org.w3c.dom.Document. Since this return value is a Java object, not a PeopleCode XmlDoc, you cannot use the standard PeopleCode Node methods to extract values from the resultant document. In the absence of an additional client library, we would have to write DOM traversal code to select and concatenate the text node values contained in the Facebook response elements. Fortunately, the PeopleSoft application server installation includes the Xalan XML processor. The Xalan client library includes the org.apache.xpath.XPathAPI class that can be used to select values from a Java DOM Document similar to the XmlDoc.FindNode method. Here is an example of using the XPathAPI in PeopleCode to extract the name of the first friend from a Facebook friends query:

Local JavaObject &results = &fbClient.fql_query("SELECT uid, name, status FROM user WHERE uid IN (SELECT uid2 FROM friend WHERE uid1 = '" | &uid | "')");
Local JavaObject &xpath = GetJavaClass("org.apache.xpath.XPathAPI");
Local string &name = &xpath.selectNodeList(&results, "/fql_query_response/user[1]/name/text()");

What is an IScript?

It has been a few years since I attended my first PeopleSoft|Connect conference. As a new PeopleSoft developer, I was eager to learn all I could about PeopleSoft's Integration Broker, PeopleTools, and PeopleSoft's web UI. I spent hours pestering the experts (ask Robert Taylor, a prior Integration Broker product manager, and Tushar Chury, a former component interface developer). During one of the sessions I attended, someone from the audience asked a question about integration and the presenter replied, "I would probably create an IScript to do that." I don't remember the question. The only thing that stuck with me was the term "IScript." I had so many other questions for the experts that I didn't ask what an IScript was. Nevertheless, I left the event with a big question: "What is an IScript?" Since I had already taken the PeopleTools and PeopleCode classes and had not heard mention of IScripts, I decided they must be some legacy PeopleSoft technology that I didn't need to learn. To satisfy my curiosity, I looked up IScripts in PeopleBooks and received a... well... thorough definition... just like reading a good dictionary (see Enterprise PeopleTools 8.49 PeopleBook: PeopleCode API Reference > Internet Script Classes (iScript)). Yes, after reading the definition in PeopleBooks, I knew what an IScript was, but I just couldn't figure out why I would want to use one. Now, several years later, I find that I can't work without IScripts. Ajax, pagelets, WML, and SVG provide excellent use cases for IScripts. Each of these "technologies" requires marked up text without the overhead of the PIA rendered component HTML.

I like to think of IScripts as the PeopleTools developer's Swiss Army Knife. Besides a "cutting implement" a Swiss Army Knife might have a leather punch, a screw driver, a can opener, etc. Yes, you can do just about anything with a Swiss Army knife, but it might take you a long time to get the job done. Then, when you are done, the job might not be as satisfactory as it would have been if you had used the right tool. Have you ever opened a can with a Swiss Army Knife can opener? I'll bet you worked up an appetite. Likewise an IScript is the right tool for some jobs, but choose wisely. You can probably replicate the postback behavior of a component with an IScript, but it is a lot of unnecessary work. Likewise, you may have to consider multi-lingual translations, etc.

I compare IScripts to JSP or ASP. Basically an IScript is PeopleCode that has access to the Request and Response objects. Just like JSP and ASP, you, the developer, writes code to read parameters from the request and write information, data, etc to the response. Unfortunately, just like ASP, the response object's write methods only render text. Since the response object does not contain any binary write methods, you will have to ignore the dynamic image generation possibilities (I was hoping to use IScripts to render images stored in a user table, but I haven't figured out how to make that work since the write method only accepts plain text).

How do you create an IScript? For more information, you can look it up in PeopleBooks, but just to vaguely satisfy your curiosity, IScripts follow the same design pattern as FUNCLIBS. An IScript is a PeopleCode function stored in a record definition. The record definition name must start with WEBLIB_. The function can be in any field event of the record definition. By convention, we generally use the field ISCRIPT1 and the event FieldFormula. The function name, however, must start with IScript_ and cannot take any parameters or return a value.

How do you call an IScript? IScripts can be called a number of ways. How you call it depends on the purpose of the IScript. If your IScript is a pagelet, then you will create a CREF for your IScript in the portal registry under Portal Objects > Pagelets. If your IScript is called from JavaScript (Ajax, etc), then you call your IScript using a URL like http://server:port/psc/site_name/portal_name/node_name/s/WEBLIB_name.FIELDNAME.FieldFormula.IScript_name. To make it easier, you can generate an IScript URL using the PeopleCode built-in function GenerateScriptContentURL.

Now that you know what an IScript is, you might be interested in looking at some examples. Chris Heller posted a couple of IScripts that can be used as Bookmarklets. These Bookmarklets allow us, the developer, to exend our existing tool set by writing tools that we can use where we work: in the web browser. On Wednesday, April 4^th, 2006, Chris posted a bookmarklet/IScript that will drill into a portal registry content reference from a PeopleSoft page. In his 2006 Open World and 2007 Alliance presentations, Chris showed us how to use IScripts and bookmarklets to turn on tracing and display security information. ERP Associates also has some good PeopleSoft Bookmarklet examples.

Portal Template Settings

PeopleSoft uses Content References (CREF's) to describe how to display content within the PeopleSoft Portal (Enterprise or standard PeopleTools Portal). A very important setting for describing how to display a CREF is the CREF's template. The template describes how the content should be wrapped or "framed." Some templates proxy content into a single HTML page. Other templates use frames to display content. The most common frames template is the template that displays the header at the top, a menu on the left, and a content window on the right. If a CREF does not specify a template, then the Portal will use the default template. The default CREF template is set in PeopleTools > Portal > General Settings. If you haven't changed this setting, then your template is DEFAULT_TEMPLATE, also known as Portal default template. After looking up the default template, we can navigate to PeopleTools > Portal > Structure and Content to view the definition for this template. Templates are stored in the portal registry under Portal Objects > Templates. If your template has a storage type of "Local (in HTML Catalog)", then you can modify your template's HTML directly in this CREF. CREF's with a storage type of "Remote by URL", like the Portal default template, usually retrieve dynamic content from an IScript. Theoretically, you could store your template definition in any format or system as long as it is accessible by a URL (think of the possibilities for dynamic or even hosted templates). The IScript for the delivered Portal default template reads its settings from a different Content Reference. The name of this settings content reference is stored in the field PT_PORTAL_PROFILE of table PS_PTPP_OPTIONS.

SELECT PT_PORTAL_PROFILE FROM PS_PTPP_OPTIONS

With just a CREF name, you will have a hard time changing the template's values. The post Query for Component and/or CREF Navigation contains SQL to help you find the Structure and Content navigation for the CREF stored in PT_PORTAL_PROFILE. If you haven't customized your system, the settings CREF is PT_PORTAL_PROFILE (or PAPP_PORTAL_PROFILE if you are using Enterprise Portal). The PT_PORTAL_PROFILE CREF is stored in the Portal Registry's structure and content at Tools - Hidden > PeopleTools Portal Profile (For Enterprise Portal: Enterprise Portal - Hidden > Enterprise Portal Profile). From this CREF's attributes, you can specify the frame header height, whether to display the menu title, whether to show Search in the menu, as well as many other portal related settings. For example, if you wanted to change the frame header height from the default of 65 to 48 to give you more space for content, you would change the value of the attribute HEADER_FRAME_ROWS from 65 to 48.

%Component Gotcha

I was just writing an IScript for the Approval Workflow Engine (AWE) that called the Approval Manager class to approve a transaction. The transaction failed because the Application Classes used in the Event notification handler used the %Component system variable. This would have been fine except %Component cannot be called from an IScript. To work around this, I wrapped the calls to %Component in a function that checks whether the execution context is a component. Here is the code:

Function GetComponentName() Returns String
   If (%ContentType = "c") Then
      Return %Component;
   Else
      Return "";
   End-If;
End-Function;

If you are writing your code in a Page or a Component and use the %Component system variable, then you shouldn't have any problems. But, if your code is in a FUNCLIB or Application Class, a reusable component, then be aware that one of those "reuses" might be from PeopleCode that runs outside a Component (IScript, AppEngine, Message Subscription, etc). If that is the case, then please be kind to the developer that follows you by wrapping your calls to %Component in a function that tests to see if your code is executing within a Component.

%Request.GetParameterNames

I was working on an IScript that wrote WML to the browser and needed to pass the query string parameters back to the client as postfields. I thought I would just loop through the %Request.GetParameterNames array and write those parameters back to the client marked up as postfields. When I did this I saw 6 additional parameters:

PSHome
Portal
Node
ContentType
ContentID
ICScriptName

To test this, run the following IScript:

Function IScript_TestParams
   Local array of string ¶ms = %Request.GetParameterNames();
   Local string ¶mName;
   Local number ¶mIdx;
   
   %Response.SetContentType("text/plain");
   
   For ¶mIdx = 1 To ¶ms.Len
      ¶mName = ¶ms [¶mIdx];
      %Response.WriteLine(¶mName | ": " | %Request.GetParameter(¶mName));
   End-For;
   
End-Function;

Now, the part that really, really threw me was how PeopleSoft interpreted the ICScriptName parameter. The code I wrote copied the request parameters to the response as postfields and then posted them back to a different IScript on the same server. After a good hour of troubleshooting, trying to figure out why the browser never displayed the second IScript, I realized that the psc servlet was using the IScript from the ICScriptName parameter, not the IScript in the main URL. In fact, modifying the URL a little bit, I noticed that you can leave off the Record.Field.Event.IScript_Name portion of the URL in favor of ?ICScriptName=Record.Field.Event.IScript_Name (not that you would want to do that).

So what now? How can we work around this %Request.GetParameterNames dilemma? Here is a function that will parse the %Request.QueryString into key/value pairs and print them to the browser:

Function IScript_TestParams2
   Local array of string ¶ms = Split(%Request.QueryString, "&");
   Local array of string &keyValue;
   Local number ¶mIdx;
   
   %Response.SetContentType("text/plain");
   
   For ¶mIdx = 1 To ¶ms.Len
      &keyValue = Split(¶ms [¶mIdx], "=");
      %Response.WriteLine(&keyValue [1] | ": " | Unencode(&keyValue [2]));
   End-For;
   
End-Function;

Of course, this function does not account for parameter arrays, etc. I'll leave that up to you.

Caveat: I was working on a PeopleTools 8.48.07 instance. I have no idea if this behavior is the same on other PeopleTools versions

Query for Component and/or CREF Navigation

I regulary have to figure out the menu/portal navigation to a component given nothing more than a component or a CREF name (AKA Content Reference or Portal Object Name). For example, looking at a Pagelet definition in the Pagelet Wizard, you may see the cryptic content reference name for a component, but nothing telling you how to navigate to that component and no hint to help you find it in the portal registry. Likewise, a user might give you the menu and component name of a troubled component, but not the navigation to that component. It is situations like this that we, as developers, can realize value from the PeopleTools meta-data. Since the navigation, CREF name, and menu/component relationship is stored in the PeopleTools database, we can query a tools table to determine this navigation. The following Oracle specific SQL displays the navigation to a given CREF name. If you use a different database, and are familiar enough with your database's SQL, please translate this statement and post it as a comment for the benefit of the rest of the community.

WITH PORTAL_REGISTRY AS (
SELECT RTRIM(REVERSE(SYS_CONNECT_BY_PATH(REVERSE(PORTAL_LABEL), ' >> ')), ' >> ') PATH
     , LEVEL LVL
  FROM PSPRSMDEFN
 WHERE PORTAL_NAME = 'EMPLOYEE'
 START WITH PORTAL_OBJNAME = :1
CONNECT BY PRIOR PORTAL_PRNTOBJNAME = PORTAL_OBJNAME )
SELECT PATH
  FROM PORTAL_REGISTRY
 WHERE LVL = (
SELECT MAX(LVL)
  FROM PORTAL_REGISTRY )

NOTE: Even though I've written my fair share of SQL, I'm no expert. Please don't grade me on the elegance and efficiency of the previous statement. If you are an SQL expert, and have a better way of writing this recursive SQL statement, then, please, for the benefit of the community, paste that statement into a comment. That said, I'll continue with the topic at hand...

If we pass EP_STANDARD_CF_TMPLT_GBL as bind :1, then the result will be Root >> Set Up Financials/Supply Chain >> Common Definitions >> Design ChartFields >> Configure >> Standard Configuration.

Modifying the above SQL statement a little, we can determine the navigation for a given menu, component, and market (the Ctrl-J data our users usually give us):

WITH PORTAL_REGISTRY AS (
SELECT RTRIM(REVERSE(SYS_CONNECT_BY_PATH(REVERSE(PORTAL_LABEL), ' >> ')), ' >> ') PATH
     , LEVEL LVL
  FROM PSPRSMDEFN
 WHERE PORTAL_NAME = 'EMPLOYEE'
 START WITH PORTAL_REFTYPE = 'C'
   AND PORTAL_URI_SEG1 = :1
   AND PORTAL_URI_SEG2 = :2
   AND PORTAL_URI_SEG3 = :3
CONNECT BY PRIOR PORTAL_PRNTOBJNAME = PORTAL_OBJNAME )
SELECT PATH
  FROM PORTAL_REGISTRY
 WHERE LVL = (
SELECT MAX(LVL)
  FROM PORTAL_REGISTRY )

If we pass DESIGN_CHARTFIELDS for bind :1 (menu), STANDARD_CF_TMPLT for bind :2 (component), and GBL for bind :3 (market), then we will get the same result as the previous query, but using the Ctrl-J data rather than the CREF name.

Oracle OpenWorld 2007 Presentation Available

You can download my 2007 OpenWorld PeopleSoft PeopleTools Advanced Tips and Techniques presentation from the OpenWorld content catalog.

Create Your Own Meta-HTML Elements

One thing that keeps me excited about PeopleSoft is the flexibility of the PeopleTools platform. As I was working on a a custom Pagelet Transform Type, following the details outlined in Rich's blog post Markdow Text Filtering for PeopleSoft, I realized I was actually creating a custom Meta-HTML element. How cool is that? Not only can I extend the PeopleCode language using Java, I can also extend the Meta tag set. Here is the code:

Function ResolveMetaHTML(&html as string) returns string
   Local JavaObject &pattern;
   Local JavaObject &matcher;
   Local String &node_url;
   
   REM ** Resolve %NodePortalURL(NODENAME) tags;
   &pattern = GetJavaClass("java.util.regex.Pattern")
      .compile("(?i)%NodePortalURL\((\w+)\)");

   &matcher = &pattern.matcher(
      CreateJavaObject("java.lang.String", &html));

   While &matcher.find()
      SQLExec("SELECT URI_TEXT FROM PSNODEURITEXT WHERE MSGNODENAME = :1 AND URI_TYPE = 'PL'", &matcher.group(1), &node_url);
      &html = Substitute(&html, &matcher.group(), &node_url);
   End-While;
End-Function;

This Meta-HTML element replaces %NodePortalURL(NAMEOFNODE) with the Portal URL defined on the portal tab of the node NAMEOFNODE. This can be used to ensure images, scripts, CSS, links, iframes, etc point to the correct server at runtime.

If you want to create your own Meta-HTML elements, I suggest you place the implementation of those elements inside an Application Class. This custom Application Class would be responsible for accessing its HTML definition using GetHtmlText and replacing all instances of custom Meta-HTML elements with your implementation of those Meta-HTML elements. Rather than call GetHtmlText directly, your PeopleCode would use your custom Application Class.

If this works for Meta-HTML, why not custom Meta-SQL?

Desktop Integrated Signon

Several months ago I had the opportunity to configure a PeopleSoft system to "trust" users' desktop credentials. Some would call this single signon or even Desktop Integrated Signon. Implementing desktop integrated signon requires some configuration and a small amount of development. The process looks like this:

1. Download the JCIFS NtlmHttpFilter,


2. Modify the filter to pass the desktop user name to the app server as a request header (compile and deploy included, of course),


3. Write some signon PeopleCode,


4. Enable public access,


5. Enable signon PeopleCode, and


6. Configure your web server to use your new filter (see the JCIFS NtlmHttpFilter documentation for configuration details as details will differ depending on your environment).

After downloading the filter and filter source code, open the NtlmHttpServletRequest and implement the getHeader and getHeaderNames overrides with the following code.

    public String getHeader(String name) {

        if(name.equals("XX_REMOTE_USER") {
            return getRemoteUser();
        } else {
            HttpServletRequest req = (HttpServletRequest)this.getRequest();
            return req.getHeader(name);
        }
        
    }
    public Enumeration getHeaderNames() {
        Vector headers = new Vector();
        HttpServletRequest req = (HttpServletRequest)this.getRequest();

        for (Enumeration e = req.getHeaderNames() ; e.hasMoreElements() ;) {
            headers.add(e.nextElement());
        }

        headers.add("XX_REMOTE_USER");
        return headers.elements();
    }

Next, put the following PeopleCode in a FUNCLIB:

Function WWW_NTLM_AUTHENTICATE()
   Local string &userName = %Request.GetHeader("XX_REMOTE_USER");
   Local number &foundSlash = Find("/", &userName);

   REM ** remove the NT/AD domain;
   If(&foundSlash > 0) Then
      &userName = Substring(&userName, &foundSlash + 1, Len(&userName));
   Else
      &foundSlash = Find("\", &username);
      If(&foundSlash > 0) Then
         &userName = Substring(&userName, &foundSlash + 1, Len(&userName));
      Else
   End-If;

   If(Len(&userName) > 0) Then
      SetAuthenticationResult(True, &userName);
   Else
      SetAuthenticationResult(False, &userName, "Web server authentication failure");
   End-If;
End-Function;

Like I said, it has been a few months since I wrote this code, and, unfortunately, I'm typing it here from memory. Please correct any mistakes I've made. Refer to PeopleBooks for enabling signon PeopleCode and enabling public access. Both are documented in the Security Administration PeopleBook.

A couple of issues I've found with this approach:

• If you are using Enterprise Portal and want to allow desktop integrated signon to both the portal and to the content provider apps, then you will need to further customize the filter to skip NTLM on your content provider web servers when the client is the portal server. Otherwise, the NtlmHttpFilter will not allow portal to access those web servers (this only affects homepage creation when you have pagelets that come from a content provider). If you only access your PeopleSoft systems through Enterprise Portal, then this is not an issue. Likewise, if you are configuring this solution on your PeopleSoft applications and you do not have Enterprise Portal, then this is not an issue.


• In this scenario, your app server trusts the security information provided by the web server, bypassing the app server's standard authentication routine. This may pose a security threat if users can gain access to your app server. To mitigate this risk, you may want to either hide your app server behind a firewall or perform additional validation/authentication (digitally encrypt the user ID request header on the web server with a certificate and decrypt it on the app server, pass the NTLM authentication token on to the app server and validate it again, etc).


• Since this solution requires your web server to trust your desktop, make sure your organization has a strong password policy forcing strong passwords. If you use a desktop integrated sign on solution, then any user that can gain access to a desktop by cracking a password can also gain access to your Enterprise applications. As an alternative to passwords, consider key fobs.

If you would like to allow administrators to log in as someone other than their desktop user (psadmin, for example), then you can add an "if" test to your signon PeopleCode that compares %SignonUserId to the public user name. If the user name is the same as your public user name, then log the user into the application as the user given by the web server. Otherwise, return from this function and allow the standard signon processing to authenticate the user.

Network Enabling Technologies

Besides my OpenWorld presentation on Wednesday, I'm also staffing demo pod 15 Monday, Tuesday, and Thursday. Today, while discussing PeopleTools with our customers at pod 15, a brilliant gentleman asked me, "How can I encrypt and digitally sign e-mails sent by the PeopleSoft process scheduler and the workflow engine?" My first answer? "Hmmm..." My first action? Turn to legendary Chris Heller, who happened to be standing near me, to ask him what he thought. The three of us, along with my pod mate Rahul, discussed some options and came up with the following ideas:

• If your SMTP server can be configured to do so, then have it digitally sign and encrypt your e-mails.


• If your SMTP server can't handle this task, then setup a secure e-mail relay between PeopleSoft and your SMTP server for the purpose of encrypting and signing e-mails.

This question reminded me of another networking related enabling solution. This problem and solution relate to Integration Broker's proxy settings. If your Integration Broker resides behind a proxy server and you have configured Integration Broker to use that proxy server, then you may have noticed that Integration Broker will send all requests through your proxy server. This is good, if the request target resides outside your firewall. This may not be good if the requested resource is inside your firewall. The problem is caused by the fact that Integration Broker cannot be configured to bypass its proxy server for specific hosts. If you find yourself in this situation, then here are a couple of solutions:

• Configure your firewall proxy server to be an Intercepting Proxy. From a PeopleSoft configuration perspective, this solution is the easiest because it allows you to ignore the proxy issue.


• Use a forwarding proxy server. Apache's mod_proxy is the first that comes to mind. If you use a forwarding proxy, then be sure to secure it. The last thing you want is for your forwarding proxy to become an open proxy.

Another option is to configure your proxy server to connect to both internal and external sites. I do not recommend this for a couple of reasons:

1. You may inadvertently make a path for other, external programs to access your internal servers.


2. You unnecessarily increase the amount of traffic your firewall proxy server has to handle.

Before choosing a solution to either of these network related issues, be sure to discuss your options with your network security staff.

Presenting at OpenWorld 2007

Have you made your reservations for the 2007 Oracle OpenWorld experience? Are you looking for a PeopleSoft session packed with highly technical content that goes beyond App Designer? I will be presenting PeopleSoft PeopleTools Advanced Tips and Techniques on Wednesday afternoon. I am planning to talk about IScripts, Ajax, Java, JSP, and Servlet filters, all within the context of PeopleSoft. I'm also scheduled to staff the PeopleTools demo pod at various times throughout the conference. I hope to see you there!

log4j and PeopleCode Part III

John Wagenleitner has posted some additional ways to configure log4j with PeopleCode in his post, appropriately titled, Log4j in PeopleCode. If you are considering using log4j with PeopleCode, you will want to take a look at the comments. Scheming together, John and I have come up with a couple of ways to store the log4j configuration in the PeopleSoft database. This is critical if you work in an environment where you don't have access to the application server's file system.

A Quasi-dynamic Signout Template

Nested under the WEB-INF/psftdocs directory of your PeopleSoft web server, you will find several HTML templates. Even though the title of this post references the signout template specifically, the concepts outlined in this post are relevant to the other templates as well.

Template Variables

As you browse the delivered templates, you will notice that several templates include JSP/ASP style tags used to insert text into the template. To include additional localized text in the signout.html template, you can add an entry to your language's text.properties file. For example, to include the text Please contact the IT Help Desk for further information in your template, add the following text to the text.properties file.

6000=Please contact the IT Help Desk for further information

To insert that text into your template, add <%=6000%> to your template.

Query String

Since HTML templates are read and parsed by PeopleSoft, we really can't enhance them with dynamic content beyond the functionality built into that parser. Therefore, our ability to inject dynamic content into these templates is somewhat limited. Nevertheless, if we can get additional data into the logout URL as query string parameters, we can access those parameters from the template at "runtime" using JavaScript.

Now the real trick... appending key/value pairs to the logout URL. If you are using PeopleSoft's Enterprise Portal, you can modify the sign out link from the Define Headers component (Portal Administration > Branding > Define Headers). Other alternatives for enhancing the sign out link include modifying the HTML objects that define the header or using JavaScript to modify the href attribute of the Sign out link.

ServletFilter

Using a Servlet Filter like Monkeygrease we can inject valid HTML content (including JavaScript, CSS, HTML, etc) into the PeopleSoft response. If you really want to get dynamic, you can create your own filter to do just about anything (access a database with JDBC, call a web service, etc).

How to Sell Enterprise Ajax

I've heard that IT staffs are having trouble selling Ajax to the enterprise. The main enterprise response is "we don't need cute, we need fast and functional." In my opinion, that is the definition of Ajax: fast and functional. We don't add Ajax functionality to PeopleSoft applications to make them cute, we add Ajax to make our applications easier to use. We use Ajax to simplify and automate user system tasks.

I like to compare common web applications like Expedia and Amazon to enterprise applications. Those external-facing portals are enterprise class systems. The difference between those external-facing systems and the standard internal-use enterprise system is the amount of training required to use those systems. Ajax, Flex, and other RIA enabling technologies allow us to refactor our user interfaces to make them intuitive.

Consider complex enterprise purchasing systems... our purchasing agents should not need to learn our purchasing systems. We want them to be contract experts, not purchasing system experts. Instead, our purchasing systems should be so intuitive our users can figure out how to use them with little or no training. This is especially critical for our casual users.

Business Performance Management Magazine recently published an article explaining this usability issue from the perspective of budgeting and planning titled About Face: Why BPM Front Ends Are Failing.

When selling RIA, remember, it is not cute, it is usable.

Presenting at OracleDay 2007

On June 28^th at 4:15 PM I will be presenting the topic Add the Adjective "Rich" to your PeopleSoft User Experience in room 408 of the Meydenbauer Conference Center. You can read the description on the OracleDay schedule. I hope to have a working demo of some of the rich internet technologies we have integrated with our PeopleSoft applications. Depending on the interests of the audience, this will either be a high level demonstration of what is possible, or a technical discussion of how to implement Web 2.0 in a PeopleTools 8.4x environment.

Using Regular Expressions in PeopleCode

Where are the PeopleCode Regular Expressions? They don't exist. Fortunately, since PeopleCode supports Java, we can "borrow" them for use in our PeopleCode. Here is an example similar to the BSF example I posted earlier:

Function get_site_name() Returns string
   Local JavaObject &patternClass = GetJavaClass("java.util.regex.Pattern");
   Local JavaObject &pattern = &patternClass.compile("/??(\w+?)(?:_\d+?)?/.+$");
   Local JavaObject &jstring = CreateJavaObject("java.lang.String", %Request.PathInfo);
   Local JavaObject &matches = &pattern.matcher(&jstring.subSequence(0, &jstring.length()));

   If (&matches.matches()) Then
      Return &matches.group(1);
   Else
      Return "";
   End-If;
End-Function;

Where is Jim?

Just in case you are wondering why I haven't been posting fantastic PeopleSoft tricks the past few months, it is because I was spending my off hours helping out at home while anxiously awaiting the arrival of Esther May Hope, our third child. She arrived May 17^th. She is "healthy as an ox" and "eats like a horse."

Scripting PeopleSoft

In many organizations, the IT programmers are divided into two groups: those that program in the language of the enterprise system (PeopleSoft) and those that don't. Those that don't may prefer scripting languages like Python or Ruby. Whatever the language, the two groups will occasionally have to work together.

There are a couple of ways to execute scripts from PeopleSoft. If the target scripting language's binaries exist on your app or process scheduler server, then you can either call the script through the PeopleCode exec function or setup a process definition.

What if you want to pass data between PeopleCode and a scripting language? If your scripting language has a Java implementation, then you can actually pass objects between PeopleCode and that scripting language using the Apache Bean Scripting Framework (BSF). BSF supports Javascript, Python, Groovy, Ruby, NetRexx, TCL, XSLT, PROLOG, Java, JudoScript, ObjectScript, and ooRexx through Java implementations of those languages.

Here is a basic example of executing a PeopleCode generated Javascript. The script declares a regular expression object and extracts the site name from the PeopleSoft URL.

Local JavaObject &manager = CreateJavaObject("org.apache.bsf.BSFManager");
Local string &script;
Local string &siteName;

&script = "var re = /\/??(\w+?)(?:_\d+?)?\/.+$/;";
&script = &script | "var a = re.exec('" | %Request.PathInfo | "');";
&script = &script | "a[1];";

&siteName = &manager.eval("javascript", "site_name.js", 0, 0, &script).toString();

Where would I use this? Anywhere that I needed to pass objects between a scripting language and PeopleCode. My favorite example is using BSF inside an Integration Broker custom target connector. This allows the programmer of the non-PeopleSoft system to code the integration using a more familiar language.

To use the example above, place rhino-js-1.6r5.jar, bsf-2.4.0.jar, and commons-logging.jar in your app server classpath. To use a different scripting language, place the appropriate language jar in your classpath. See the BSF web site for more details.

Signing on with Oracle

Today I turned in my resignation to my supervisor at Chelan County PUD. I will miss my co-workers. On Monday, July 2nd, I move into Rich Manalang's old position at Oracle.