Tuesday, December 04, 2007

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.

Tuesday, November 13, 2007

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?

Monday, November 12, 2007

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.

Thursday, September 27, 2007

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!

Wednesday, September 26, 2007

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.

Tuesday, September 04, 2007

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

Thursday, June 28, 2007

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.

Monday, June 18, 2007

Presenting at OracleDay 2007

On June 28th 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.

Saturday, June 09, 2007

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 17th. 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.

Friday, June 08, 2007

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.

Tuesday, February 06, 2007

Accessing the PeopleSoft Database in Java

Because PeopleSoft databases are SQL compliant, it is possible to access a PeopleSoft database in Java using JDBC. At times, this may be preferable. Most of the time, however, we, and our DBA's, would prefer that we accessed the PeopleSoft database through the PeopleSoft framework, not through an external direct database connection. Fortunately for those of us that like to write Java code, PeopleSoft created native interfaces to the common data access PeopleCode objects. You will find these classes and their Java source code in PS_HOME/class/peoplecode.jar.

NOTE: Because this library requires a PeopleSoft session, your Java code must be called from a PeopleTools application (PeopleCode's GetJavaClass() or CreateJavaObject(...)).

PeopleSoft's documentation of the Java PeopleCode objects is very sparse. Even though the PeopleBooks documentation for calling PeopleCode objects from Java provides a few good examples, the Javadoc class and method documentation doesn't provide much more than a method signature. Likewise, the Java source code just contains method signatures for native calls. Looking at the source code and Javadocs, you will notice that each Object's methods takes an Object and/or an Object[] parameter. Since Object is the base for all non-primitive types, it is difficult to know what PeopleSoft intended for these methods' arguments. Overloaded methods with strong types would have been better.

Let's examine a few common PeopleCode data access methods...


SQLExec

PeopleSoft provides access to many of the common PeopleCode functions through the object PeopleSoft.PeopleCode.Func. The following code snippet is the Java declaration for the SQLExec method of that object:

public static native boolean SQLExec(Object Par1, Object[] Par2);

This call spec tells us very little about the parameters required to execute the SQLExec method. From our PeopleCode experience, we can guess that the first parameter identifies the SQL statement and the second parameter, an array, contains the in/out parameters. Looking at the PeopleBooks for the function SQLExec, we can tell that this function accepts either a SQL statement or a named SQL object as its first argument. What about when called from Java? Does the Java version (really a Java wrapper around the native implementation) accept the same identifiers for the SQL statement? If so, how should those identifiers be specified? I tried several options. Here are a few that did NOT work:

String outCol = null;
Object[] parms = {new Integer(123), "abc", outCol};

// The following throws a PeopleCode exception
Func.SQLExec(new Name("SQL", "MYSQLOBJECT"), parms);

// Oracle doesn't like the following statement because PeopleSoft sends
// the Oracle database the statement: "SQL"
Func.SQLExec(Func.GetSQL(new Name("SQL", "MYSQLOBJECT"), null), parms);

// Oracle doesn't like this statement either because PeopleSoft sends
// the Oracle database the statement: "SQL.MYSQLOBJECT"
Func.SQLExec("SQL.MYSQLOBJECT", parms);



Here are a couple that I tried that actually did work:

// Use meta-sql %SQL to turn MYSQLOBJECT into an SQL statement
Func.SQLExec("%SQL(MYSQLOBJECT)", parms);

// Load the SQL object, then execute it's statement.
Func.SQLExec(Func.GetSQL(new Name("SQL", "MYSQLOBJECT), null).getValue(), params);

Interstingly enough, notice that the two versions that work return a String containing the SQL statement. Perhaps the following declaration would have been more helpful:

public static native boolean SQLExec(String sqlStatement, Object[] inOutParms);

SQL Cursors

PeopleSoft provides access to SQL cursors through the SQL object. As you would expect, the SQL PeopleCode object has a Java complement appropriately called PeopleSoft.PeopleCode.SQL. Just like PeopleCode, you create a Java SQL object using the CreateSQL function. Here is an example of creating an SQL object in Java:

Object[] parms = {"parm1", new Integer(20)};
Func.CreateSQL("SELECT...", parms);

The PeopleBooks for the PeopleCode CreateSQL function explain that the CreateSQL function will open the cursor for fetching if the SQL statement starts with the word SELECT. While working with the SQL object in Java, I found that SQL SELECT statements used with the SQL object HAVE to start with SELECT. I was not able to open cursors for SQL statements created from SQL definitions when the SQL statements started with a "%" (percent). I was, however, able to call the Execute method of SQL objects regardless of the Meta-SQL used in the SQL definition.


Conclusion

If you need access to a PeopleSoft database from Java, you have multiple options. The 2 main reasons I continue to use the PeopleCode objects for data access are:

  • Meta-SQL
  • Shared database session

Wednesday, January 31, 2007

Importing Custom Stylesheets into PeopleSoft Pages

Adding Web 2.0 behaviors to a PeopleSoft application usually requires adding Javascript, CSS, and HTML to a delivered PeopleSoft page. One way to do this (besides Monkeygrease) is to add an HTML area to a page and place your custom Javascript, CSS, and HTML inside that HTML Area. For more dynamic, data-driven customizations, you can use PeopleCode, HTML definitions, and a derived/work record to set and modify the contents of the HTML Area.

An issue that needs to be resolved when adding Web 2.0 behavior to PeopleSoft pages is how to style those custom HTML elements. If the elements are standard HTML form elements and you want to maintain a consistent look and feel with the PeopleSoft UI, then you can use the standard PeopleSoft CSS class names. However, if you want to go beyond the standard form elements, then you are going to need to add some additional style classes that may or may not be available through PeopleTools stylesheets. For example, the jQuery Thickbox plugin requires additional styles to implement the Thickbox behavior. If you were a web developer in complete control of the rendered HTML, you would either add these styles as a style element inside the head element or you would link them into the document by adding a link element inside the HTML document's head element. The problem is, even though you may be a web developer, you are not in complete control of the HTML rendered by the PeopleSoft application. Instead, you are a web developer that is in complete control of a piece of HTML that will be rendered inside the body element. Therefore, to comply with standards, you must avoid adding style and link elements using a PeopleTools HTML Area. Fortunately, we can use Javascript to import stylesheets. Here is a function I use to import custom jQuery plugin stylesheets:

function importStylesheet(sheetUrl) {
var ss = document.createElement("link");
ss.rel = "stylesheet";
ss.href = sheetUrl;
ss.type = "text/css";
document.getElementsByTagName("head")[0].appendChild(ss);
}

If you plan on reusing this function, then place it in a file on your web server and call it like this:

<script type="text/javascript">
importStylesheet("/css/thickbox.css");
</script>

I tested this on both IE 6.0 and Firefox. I believe this is a standards compliant DOM manipulation. However, you may need to modify this for other browsers.