Parsing the HTML Field name into Record and Field Names

There are a lot of ways we can improve the PeopleSoft user interface using Ajax and JavaScript libraries. Using libraries like jQuery, we can add Google Suggests style prompts, spreadsheet grid navigation, or highlighting the active text field. Before we add these features to data entry fields, we usually need to discover two things about the target page:

1. The type of data entry field and
2. The name of the field.

HTML provides a few types of data entry fields. PeopleSoft applications extend this model by adding prompt fields and grid fields. Both of these are standard html input text fields. What differentiates a PeopleSoft grid or prompt text field from other fields is the prompt field's name and the grid field's parent. Grid text fields have a parent table with a special name. Prompt fields have the string $prompt$ in the name attribute.

There are several pieces of information stored in the PeopleTools meta-data that would be valuable to know when using Ajax. For example, when using Ajax to implement Google suggests style prompts, you need to know the prompt table, etc. Since edit field names are a concatenation of the record name and field name delimited by an underscore ('_'), it should be possible to parse a field name into the field's source record and field. Unfortunately, record and field names also contain underscores. Therefore, it is impossible to know where a record name ends and a field name begins. Nevertheless, this information is required to determine the field's prompt table.

I've heard that Oracle's PeopleSoft team is planning to enhance the system generated HTML in ways that will facilitate Ajax and other UI enhancements. Hopefully providing us with the record and field name is on the list. Until then, here is an SQL statement that will provide you with the record and field name of a text field. You can call this SQL statement from your Ajax IScript.

SELECT RECNAME
     , FIELDNAME
  FROM PSRECFIELDDB
 WHERE RECNAME  '_'  FIELDNAME = :1

There is some risk in this method. It is possible that a record name and field name overlap in such a way that the above SQL returns 2 rows. I ran this SQL in the PeopleSoft Financials database and found 1 combination that returned 2 rows. Since the 2 tables were not tables I had noticed before, I don't see too much risk involved in using this method.

I also noticed one other problem with using this method on an Oracle database. Because of the concatenation in the WHERE clause the database ignores the record and field indexes and performs a full table scan. There are several ways to work around this problem. One solution would be to create a function based index. Another alternative would be to create a materialized view that concatenates the fields in the view's select statement.

log4j and PeopleCode Part II

Last month I wrote about using log4j to debug PeopleCode. This is a tool that I use quite often. Here are a couple more hints to help you use log4j from PeopleCode.

One of the main benefits of log4j is the ability to configure, and reconfigure, log4j without modifying your code. However, sometimes a configuration file is not practical. Here is an example of configuring log4j from PeopleCode:

Local JavaObject &logger = GetJavaClass("org.apache.log4j.Logger").getLogger("my.custom.logger");
Local JavaObject &layout = CreateJavaObject("org.apache.log4j.PatternLayout", "%-4r %d [%t] %-5p %c [%x] - %m%n");
Local JavaObject &appender = CreateJavaObject("org.apache.log4j.ConsoleAppender", &layout);

&appender.setLayout(&layout);

&logger.addAppender(&appender);
&logger.setLevel(GetJavaClass("org.apache.log4j.Level").DEBUG);
&logger.debug("Hello from a PeopleCode configured logger.");

Running this code on the AppServer will write log messages to the AppServer's stdout file. log4j has several other appenders that may be more practical if the AppServer's stdout file is not accessible. Here is a configuration example that uses the SMTPAppender to send log messages to an e-mail address (preferrably your e-mail address).

Local JavaObject &logger = GetJavaClass("org.apache.log4j.Logger").getLogger("jjm.email.debugger");
Local JavaObject &layout = CreateJavaObject("org.apache.log4j.HTMLLayout");
Local JavaObject &appender = CreateJavaObject("org.apache.log4j.net.SMTPAppender");

&appender.setSMTPHost("mail.yourserver.com");
&appender.setFrom("log4j@yourserver.com");
&appender.setTo("you@yourserver.com");
&appender.setSubject("PeopleCode debug log");
&appender.setBufferSize(1);
&appender.setLayout(&layout);
&appender.activateOptions();

&logger.addAppender(&appender);
&logger.setLevel(GetJavaClass("org.apache.log4j.Level").DEBUG);

&logger.fatal("Hello from PeopleCode!");

One thing to note about the SMTPAppender is that it only sends error and fatal log messages to the specified e-mail address.

About error messages... As you know, error is a key word in PeopleCode. Therefore, it is not possible to execute the error method of the Logger object directly. However, you may be able to get around this by using Java's reflection to execute the method.

Searching for Headers Using Detail Values

PeopleSoft's ability to build component search pages from search record meta-data saves developer's a lot of time. A developer can create a user friendly search page by setting a few properties on a record definition. This works great for single record and one-to-one relationship views. While a one-to-many view can be, used as a search record, there are cases where a one-to-many view will display a lot of redundant data. An example of this is searching a record that has an effective dated key. Unmodified, a search record containing the EFFDT field will display multiple rows for the same top level key. Selecting any of the effective dated rows will create the exact same level 0 buffer structure. The additional, seemingly redundant rows, displayed by this approach can confuse users.

Most of the time, we search for documents and transactions using a document or transaction header. The header usually contains the transaction ID, description, transaction date, etc. Sometimes, however, it is easier to find a transaction using a field from a detail row in the transaction. For example, a user may know a Project Activity ID, but not the Project ID. One way to provide detail row search capabilities would be to add the Activity ID to the search record. While providing the desired behavior for a user searching by Activity ID, this solution would exponentially increase the number of rows displayed in the search results to users that are not searching by Activity ID.

An alternative to using a one-to-many view for the Project Component's search record is to create a second component specifically for searching detail rows. This second component would use a one-to-many view for its search record to display both header and detail information. Based on the user's search parameters, PeopleCode can be used to transfer the user between the header (primary component) and detail (secondary component) search pages.

To implement this alternative solution, open the main component's search record and add the detail search fields. Adding the detail search fields to the search record as alternate search keys tells the component processor to add them as input fields on the advanced search page. Following the Activity ID example, add the Activity ID field to the main search record as a search key. Instead of adding the PS_PROJ_ACTIVITY record to the search view's SQL, select ' ' for the Activity ID. Save and build the view. If the user enters a value in the Activity ID search field, then, using the SearchSave event, switch components to the one-to-many view component that has a search record with the Activity ID detail values. Using the PeopleCode Transfer function, you can transfer the user to a different component while retaining all of the search key values entered by the user. From this secondary component, the user can search by Activity ID. When the user chooses a Project, the TransferExact function can be used to transfer the user back to the main component and open the selected Project using the detail component's PreBuild PeopleCode event. If the user deletes the Activity ID search criteria, then the Transfer function can be used to transfer the user back to the main search page and display the search results using the other search criteria entered by the user.

Here is what this solution would look like when applied to the PROJECT_GENERAL component.

Modify the PROJ_SRCH record as follows:

• Add ACTIVITY_ID as a search key field below the PROJECT_ID field
• Add , ' ' to the view's SQL between , A.PROJECT_ID and , A.DESCR.

Create a new search record that contains the one-to-many join of PS_PROJECT and PS_PROJ_ACTIVITY and name the search record NEW_PC_ACT_SRCH.

Create a new component named NEW_PC_ACT_SRCH that contains a page named UNUSED_PAGE with a search record called NEW_PC_ACT_SRCH.

Modify the Component Record PeopleCode of the PROJECT_GENERAL component by adding the following PeopleCode to the PROJ_SRCH SearchSave PeopleCode:

Local Record &keys_rec = CreateRecord(Record.NEW_PC_ACT_SRCH);
If (All(PROJ_SRCH.ACTIVITY_ID)) Then
   &keys_rec.BUSINESS_UNIT.Value = PROJ_SRCH.BUSINESS_UNIT.Value;
   &keys_rec.PROJECT_ID.Value = PROJ_SRCH.PROJECT_ID.Value;
   &keys_rec.ACTIVITY_ID.Value = PROJ_SRCH.ACTIVITY_ID.Value; &keys_rec.DESCR.Value = PROJ_SRCH.DESCR.Value;
   Transfer( False, MenuName.COMPONENT_MENU, BarName.CUSTOM, ItemName.NEW_PC_ACT_SRCH, Page.UNUSED_PAGE, "U", &keys_rec, True);
End-If;

In the NEW_PC_ACT_SRCH component, add the following PeopleCode to the NEW_PC_ACT_SRCH search record SearchSave event:

Local Record &keys_rec = CreateRecord(Record.NEW_PC_ACT_SRCH);

If (None(NEW_PC_ACT_SRCH.ACTIVITY_ID)) Then
   &keys_rec.BUSINESS_UNIT.Value = NEW_PC_ACT_SRCH.BUSINESS_UNIT.Value;
   &keys_rec.PROJECT_ID.Value = NEW_PC_ACT_SRCH.PROJECT_ID.Value;
   &keys_rec.DESCR.Value = NEW_PC_ACT_SRCH.DESCR.Value;
   Transfer( False, MenuName.CREATE_PROJECTS, BarName.USE, ItemName.PROJECT_GENERAL, Page.PROJECT_GEN_01A, "U", &keys_rec, True);
End-If;

In the NEW_PC_ACT_SRCH component, add the following PeopleCode to the component's PreBuild event:

Local Record &keys_rec = CreateRecord(Record.PROJ_SRCH);

&keys_rec.BUSINESS_UNIT.Value = NEW_PC_ACT_SRCH.BUSINESS_UNIT.Value;&keys_rec.PROJECT_ID.Value = NEW_PC_ACT_SRCH.PROJECT_ID.Value;
Transfer( False, MenuName.CREATE_PROJECTS, BarName.USE, ItemName.PROJECT_GENERAL, Page.PROJECT_GEN_01A, "U", &keys_rec, True);

Leveraging PeopleSoft's JavaScript Save Warning

In a standard PeopleSoft session, a user will enter data into components. If a user forgets to save a component before navigating to a new component or opening a different transaction, then that user will loose all of the changes he or she made to the current transaction. To mitigate the potential for loosing changes, PeopleSoft displays a save warning message to remind the user to save. I am sure that you have encountered this message while working in PeopleSoft. I am also sure that you have noticed that PeopleSoft only displays this message if you change something within a component before navigating away from that component.

As you add custom JavaScript and HTML to your PeopleSoft implementation, you may find yourself in a situation where you need to know if a user has modified a component. For example, if you add an HTML Area to a component and that HTML Area contains links to related transactions, then, when a user navigates to a different transaction using these custom links, that user may loose any changes he or she made to the current transaction.

To warn a user before taking action using JavaScript, you can use the following PeopleSoft defined JavaScript functions:

• saveWarning(frameName,form,target,url)
• checkFrameChanged(objFrame)
• checkAnyFrameChanged(startingFrame)
• checkFormChanged(form, objFrame)

The saveWarning function will check for changes in a frame or form. If anything in the form or frame has changed, then the function will display a confirm message asking the user if he or she wants to save before leaving. Depending on the user's answer, the function will either navigate to the new url or cancel the action. If the user chooses to leave the component, then the function will return true.

saveWarning parameters:

• frameName: the name of a frame to test
• form: an object reference to a form to test
• target: the target frame name for the url
• url: the URL to load into the target frame

If you want to display a warning message for changes in any frame or form then you can substitute '' for the frame name and null for the form object. For example, the statement saveWarning('',null,'_self','') will cause the function to display a warning message if anything has changed in any frame or form.

If you just want to test for changes, and don't want to display a message, then you can use one of the check*Changed() functions. For example, to check for changes on any frame or any form, then you can use the statement checkAnyFrameChanged(top.frames). Here is an example of using the checkAnyFrameChanged function:

if(checkAnyFrameChanged(top.frames)) {
  // something changed
} else {
  // nothing changed
}

If you use one of the check*Changed functions to display a custom message, then be sure to consider internationalization.

The check*Changed functions have similar parameters to the saveWarning function. This is because the saveWarning function calls several of the check*Changed functions using the parameters passed to the saveWarning function.

Logging PeopleCode

Using log4j to debug applications

Developers log many different things about applications for various reasons. For example, a developer may log application state, application execution, or application usage for the purposes of finding bugs, resolving errors, recovering from system failures, tracking usage, or auditing security. While all these reasons are necessary, this post is going to focus primarily on logging state and execution for the purposes of finding bugs and resolving errors.

PeopleTools 8.4 provides developers with a mechanism for tracing PeopleCode and SQL. I have found the SQL trace facility to be very valuable. I usually use an SQL trace value of 7 (the first 3 options: 1, 2, 4). This SQL trace value displays SQL statements, bind values, and commit/rollbacks.

While the verbosity of a PeopleCode trace provides a developer with all of the information necessary to debug a PeopleCode program, I find that it provides too much information to be valuable. Fortunately, there are alternatives for determining application state and "peeking" at variable values when debugging an application.


MessageBox

The MessageBox function is one of the most useful debugging tools available to a PeopleSoft developer. By inserting a single line of code into a program, a developer can instantly view information about a program's execution. Unlike a PeopleCode trace, the MessageBox function only displays the information that you, the developer, want to see.

What is wrong with the MessageBox function? The MessageBox function was my best PeopleCode debugging tool. Unfortunately, those MessageBox statements didn't disappear by themselves after I found and fixed a bug in a PeopleCode program. Rather, I had to manually find and remove those statements. Maybe it is the pack-rat in me, but I don't like to delete anything. You never know when you might need those MessageBox debugging statements again... right? Wouldn't it be nice if you could just turn off those debugging statements rather than delete them?

What about PeopleCode programs that don't have a user interface? Yes, the MessageBox function works fine for AppEngine PeopleCode programs. However, rather than just print the message to the message log, the MessageBox function prints several unwanted informational lines for each call to the MessageBox function.

What about additional targets? The MessageBox function is a user interface function. Don't expect to log to an SMTP or other Socket target from the MessageBox function.


log4j, An Alternative

Caveat: This post is not meant to be a log4j tutorial. Rather, with this post, I hope to demonstrate an alternative method for debugging and logging PeopleCode program executions. For a better understanding of the log4j framework, I refer you to the log4j web site.

I have used log4j for several years. It is my favorite Java logging framework. As you will see from the various ports of log4j, I'm not the only programmer that prefers log4j. Unfortunately, I didn't see a log4pc (pc=PeopleCode) on the list of ports.

Because I have had a lot of success using log4j, I took my turn at writing a PeopleCode port of log4j using Application Packages. I was quite pleased with my work! About half way through the code porting process, I ran into some log4j functionality that I couldn't seem to replicate in PeopleCode, specifically the MDC and NDC. To work past these, limitations, I thought I would just use the log4j Java compliments (not a pure PeopleCode approach, I know, but it worked). As I was considering this approach, I thought... Hmmm... Why not just use the log4j package as written directly from PeopleCode? PeopleCode supports Java objects and PeopleSoft delivers the log4j lib in the PS_HOME/class directory.


Why log4j instead of MessageBox?

The log4j framework is very flexible. With log4j you can litter your code with debug statements. Those debug statements will hide silently in your code until you turn on debugging. log4j also allows you to specify multiple targets. Delivered log4j targets include socket, database, file, e-mail, and system log appenders. log4j allows you to use layouts to configure how log statements are written to logging targets. You can configure some statements to log to one target while other statements log to a different target. Best of all, you can configure the log level, targets, and layouts from a configuration file without touching your production code.


Using log4j

The following is an example of some PeopleCode that creates a log4j logger and writes debug information to a target.

Local JavaObject &logger = GetJavaClass("org.apache.log4j.Logger").getLogger("com.mycompany.mydepartment.PS.Component.Record.Field.Event");
logger.debug("Logger created");
... do some processing ...
logger.info("Process completed successfully");

Line 1 creates a log4j logger with a name of com.mycompany.mydepartment.PS.Component.Record.Field.Event. log4j recommends using the Java class name of the object that is executing logging statements. Since we are using PeopleCode, not Java, I like to use a concatenation of my domain name, department name, PS (for PeopleSoft, or ERP for enterprise applications, etc), the object name (component, record, field), and event name.

Line 2 writes a debug level statement to the logger.

Line 4 writes an informational statement to the logger.

If the log level in the configuration file was specifically set to only display information, warnings, and errors, then statement 2 above would have executed silently. Using the log4j API from PeopleCode, your code can dynamically change the log4j configuration. For example, you could use &logger.setLevel(...) to set the log level to a level other than the configuration file value.

You can find a sample log4j.xml configuration file here. Place this file in your AppServer PS_HOME/class directory. If you are logging from an AppEngine program, then be sure to put your configuration file in your process scheduler's PS_HOME/class directory.

Because log4j uses the logger name to create an inheritance tree of loggers, you don't have to explicitly define every logger by name. Instead, you can define a short name logger and all loggers with the same prefix will inherit the definition of the shorter logger name. For example, if you defined a logger named com.mycompany.mydepartment.PS and then created a logger named com.mycompany.mydepartment.PS.Component.Record.Field.Event, then the com.mycompany.mydepartment.PS.Component.Record.Field.Event logger would inherit the definition of the com.mycompany.mydepartment named logger.


AppEngine log statements that communicate to users

Because the process scheduler redirects an AppEngine program's stdout to a a text file, any output written to the console by log4j is available to your users from the View log/trace link in the process scheduler. Using layout patterns and log4j, you can create logs that can be used by you, the developer, and your users to tell your users what happened and to tell you where that "what" happened.


Writing to the database

Rather than maintain a database user to log statements through the log4j JDBCAppender, I wrote a custom log4j appender that used PeopleSoft's Java PeopleCode objects. The benefit of using these PeopleSoft Java objects was that I could use the existing database session to write to the database. However, since database connectivity problems are something I have to occasionally debug, I never used this appender in production. Instead, I chose to use one of the delivered, simple appenders like the file or console appender.

Where am I?

Highlighting the active text field in PeopleSoft.

This past week I have been working with Monkeygrease. Monkeygrease is a very powerful tool for developers that want to enhance their existing PeopleSoft application user interface. In the next few paragraphs I will demonstrate how I used Monkeygrease and jQuery to highlight the active user input control on PeopleSoft pages without modifying any pages. To implement this example, you will need access to your PeopleSoft web server's PORTAL web application folder.

The most common file location for JavaScript and CSS files is the /scripts and /css directories. In keeping with this standard, I created the scripts and css folders in the root of the PORTAL web application. The only file location requirement for your scripts and css files is that they must be accessible to the client through your web server. The script and css file locations shown below are suggestions. If you place these files in different locations, then be sure to modify the monkeygrease.xml file accordingly.

Since this example depends on jQuery, download the compressed jQuery library from the jQuery website and save it in your web server's /scripts/ directory.

The JavaScript -- saved in file /scripts/highlight-active.js:

$(document).ready(function(){
  $("input[@type='text'], input[@type='password'], select, textarea").focus(function(e) {
    $(this).addClass("hasfocus");
  }).blur(function(e) {
    $(this).removeClass("hasfocus");
  });
});

The CSS -- saved in file /css/highlight-active.css

.hasfocus {
  background-color: #FFFF99;
}

Download the monkeygrease.xml file to your web server's WEB-INF directory. The only thing interesting about the monkeygrease rules file is the url patterns. The first url pattern, /ps[cp].*\?cmd=(?:loginlogout).*, matches the login and logout pages. The second url pattern, /ps[cp].*/h/.+tab=.*, matches homepage tabs. The third url pattern, /ps[cp].*/c/[^/]+, matches all components. If you use a pattern like /.*, then you will match all URL's. While that may be preferable, I chose not to match IScript, worklist, and external file URL's.

Since Monkeygrease is a Servlet Filter, you will need to modify the portal web application's web.xml file. See the Monkeygrease documentation for details. I was able to paste the web.xml code snippet from the documentation directly into my web.xml file without any modifications.


That's it!

AJAX and PeopleSoft

Ever since I read Rich Manalang's post Adding Live Search to PeopleSoft Enterprise, I have been trying to figure out how to apply AJAX to PeopleSoft components. I have been able to benefit from AJAX in PeopleSoft by sending parameters to IScripts and displaying the results on a page. However, I have not been able to figure out how to update a component's data buffer using AJAX.

I think most of us would agree that the full page refresh required for a FieldChange event is too expensive. Yes, we do have deferred processing available. However, deferred processing can be just as user un-friendly as the page "flicker" of a post-back. AJAX frameworks provide the infrastructure required to post data to the server without requiring a full page refresh. Furthermore, AJAX frameworks can update a portion of a page with the results of a server operation. But how can we leverage the power of AJAX to update the component data model in PeopleSoft?

It seems the answer to this question is in the post. To update the component's data model, you would need to post the form values. Since the web server will return the full page content, you will next need to parse the returned page and update your page accordingly. Is it worth it? Anyone have any better ideas?

PeopleSoft on Oracle WHOAMI

Determine the PeopleSoft OPRID from Oracle

Occasionally, while writing components or processes in PeopleSoft, I have wished I could obtain the OPRID directly from the database without using META-SQL, META-Variables, or any other PeopleSoft magic. While those mechanisms work very well within the PeopleSoft framework, they are not accessible from PL/SQL procedures, triggers, or views. I was discussing this with my DBA a few months ago and he kindly pointed me to the CLIENT_INFO field of the v$session view. As Chris Heller explained in his post Associating database connections with PeopleSoft users dated Sunday, August 13, 2006, EnableDBMonitoring needs to be turned on before the CLIENT_INFO field will contain the OPRID. Chris Heller's post also explains where to find CLIENT_INFO on non-Oracle database platforms.

Following the good advice of my DBA, I wrote the following SQL fragment to parse the OPRID from the CLIENT_INFO field.

SUBSTR(sys_context('USERENV', 'CLIENT_INFO'), 1, INSTR(sys_context('USERENV', 'CLIENT_INFO'), ',', 1, 1) - 1)

Notice that I use the sys_context function rather than querying the v$session view directly. The v$session view is a great view for system administrators, but it lists all sessions, not just the logged in user's session.

Because this fragment is rather verbose, I wrote a PL/SQL function to encapsulate the logic of the fragment. However, when used in a WHERE clause, this fragment executes a lot faster than the function.

Here is an example SQL statement that will return the PeopleSoft logged in user. I would like to tell you that you can run this statement from any SQL tool, but it will only work when run from PeopleSoft. This is because each database client is responsible for setting the CLIENT_INFO and each client tool sets this value differently (if the client tool even sets CLIENT_INFO).

SELECT SUBSTR(sys_context('USERENV', 'CLIENT_INFO'), 1, INSTR(sys_context('USERENV', 'CLIENT_INFO'), ',', 1, 1) - 1)
FROM DUAL

Here is a PL/SQL block that sets the variable lv_oprid to the PeopleSoft OPRID.

DECLARE
  lv_oprid VARCHAR2(30);
  -- ... more variables declared here
BEGIN
  lv_oprid := SUBSTR(sys_context('USERENV', 'CLIENT_INFO'), 1, INSTR(sys_context('USERENV', 'CLIENT_INFO'), ',', 1, 1) - 1);
  -- ... do something with OPRID
END;

In a future post I will show how to use this technique to log information about users for debugging and auditing. I also hope to show how to use this technique to improve the user experience with custom pagelets.

If you are trying to restrict the visible rows in a search record, then I suggest you take a look at Larry Grey's post Little known Row Level Security hook dated Thursday, May 18, 2006.