Trimming spaces and adding spaces on a BI Publisher report

Quite often in BI Publisher, there is a need to trim unnecessary spaces and add spaces on occasions. 

Think of printing an address. PS_ADDRESSES table has so many fields but when printing the address, some of the fields may not necessarily have values, leaving the address formatting  messed up with a lot of white spaces. This is what I'm talking about - 

The RTF template shown above generates the output as shown here - 

Removing unwanted space
In order to solve this issue, I created one BI Publisher field "mail address" with the code written as shown below.

As you can see now, all the extra white space for fields that didn't have a value is now gone (I'll ignore the border issue for now). Though this fixed the extra space issue, it introduced a new problem. I had given a deliberate space between CITY and POSTAL field in the formula but was ignored by BI Publisher in the output. 

Adding Space
The way you can add a space is quite simple, enclose the space within quotes and embed it between the BI publisher tag as shown below -

 <?if:C.ADDRESS1 != ' '?><?C.ADDRESS1?><?end if?>  
 <?if:C.ADDRESS2 != ' '?><?C.ADDRESS2?><?end if?>  
 <?if:C.ADDRESS3 != ' '?><?C.ADDRESS3?><?end if?>  
 <?if:C.ADDRESS4 != ' '?><?C.ADDRESS4?><?end if?>   
 <?C.CITY?><?' '>?<?C.POSTAL?>  

This gives the proper output I was looking for - 

Printing Labels in PeopleSoft across down using BI Publisher (formerly XML Publisher)

The Problem:
Recently I was tasked to build the report in PeopleSoft to generate the list of external examiners (yes, it is obviously an education institute) which will eventually be printed on to commercially available Labels. The labels would be pasted on to the question paper stack assigned to the designated exam hall for distribution.
As the de-facto choice of technology to accomplish something of this nature I went with XML Publisher a.k.a BI Publisher. What I thought was a simple report eventually turned out to be anything but simple to print the data across and then down....

The difficulty:
My Data source was a PS Query which had already done the necessary sorting with the query criteria. 
In BI Publisher, the data within XML tags are printed one below the other using a For-loop in the form of a table. This in itself presented a challenge as the data would get printed vertically down in a single column. But I wanted multiple column output. Without thinking too much my mind immediately jumped into the idea of breaking the page into 4 columns which is an option in MS Word. When I printed the sample data I ended up with an output like this - 

1   4   7   10

2   5   8   11

3   6   9   12

What I was actually looking to accomplish was to print the data in an "across down" manner. The same way we write things on a piece of paper (left to right, then down),

1    2    3    4 

5    6    7    8 

9   10   11   12

The next best option:

As I had developed cross tab/pivot reports previously, which is primarily used when the number of columns and rows are dynamic. In my situation, I had a fixed number of rows which had to be printed in a columnar fashion. After messing with the PS Query a bit and the RTF template, I then played around with the H tag (horizontal-break-table) of BI Publisher cross tab reports and got the output I wanted albeit with a catch. Each box (or row) of data elements got printed on separate pages. Unfortunately the horizontal-break-table causes a page-break and not a line-break and this works only with PDF output.

The solution:

At this point I had tried so many things that I had given up on BI Publisher and a tad disappointed at how much difficult this was proving to be as opposed to a simple MS Word mail merge. And just when I was about to settle for the closest solution I got using the 4 column output I discussed above, I found this wonderful blog from Vetriselvan where he had shown how to accomplish the output I was trying to get.

My sample template appears this way -

and the sample output is just the way I wanted - 

There is also a caveat with this solution as well. The 4 embedded tables grow vertically down printing 1 data element and skipping the other 3 in a round robin fashion, thus making it appear to print across and then down. As long as the data is even, this works fine. However if any of the data element is larger than the rest, the rows will be of uneven size and the output would look like this (Element-28 occupies more height than the rest causing the alignment to go haywire) -

This is not a big issue as most of the commercial labels have pre-defined height and width and the tables embedded in the RTF template have to adhere to those size restrictions. This coupled with choosing the right font and font-size addresses any such issues. 

Feel free to drop a comment if you have a question or accomplished using a different approach.

Missing or invalid version of SQL library PSORA64 (200, 0) error with PT8.54

Error Message: Missing or invalid version of SQL library PSORA64 (200, 0)
I received this error while trying to open the application designer for PeopleTools 8.54.

Root Cause: 
By now you would have come across the news that PT8.54 is a 64 bit application. Like me, if you forgot to install the Oracle 64 bit client and tried to access PT8.54 with the existing 32 bit Oracle client you will receive this particular error message. 

Remedy:
Download the Oracle 64 bit client from Oracle site (link here) and install it. Once installed you should not get this error anymore. Do not forget to update the tnsnames.ora file for the 64 bit client.

If you still receive the error, check the PATH environment variable value of your PC. Very unlikely, but it could be possible that the ORA_HOME of the 64 bit client was somehow not appended to the PATH value during the installation. You can manually add it to resolve the issue.

Note: Although Oracle 12c 64 bit client which is the latest version available I could not install it (Installation was stuck at the skip update step despite trying several things). So I ended up choosing the older version i.e 11gR2 client which worked without any hitch.

Implementing Captcha on PeopleSoft login page using JCaptcha - Part-2

Continuing from JCaptcha Part-1 we move on to Step-3 of the JCaptcha implementation on the PeopleSoft login page.

You will have to repeat the steps henceforth for each domain (in case of multiple domains)

Step-3:

• Before customizing the files signin.html, text.properties and error.properties, make a backup for restoration purposes. The files can be found here -
  

   <%PS_HOME%>webserv\<Domain>\applications\peoplesoft\PORTAL.war\WEB-INF\psftdocs\<Domain>  
  

  Open the signin.html file in a text editor and add the following code after the <HEAD> tag
  

   <!-- Added by V.Rao to prevent captcha image caching --><META HTTP-EQUIV="CACHE-CONTROL" CONTENT="NO-CACHE">  
  

• In the JavaScript section add the Ajax code below
  

   <!-- added by V.Rao [Begin] Ajax function to refresh the captcha image -->  
   <script>  
   function chitra()  
   {  
   var xmlhttp;  
   if (window.XMLHttpRequest)  
    {// code for IE7+, Firefox, Chrome, Opera, Safari  
    xmlhttp=new XMLHttpRequest();  
    }  
   else  
    {// code for IE6, IE5  
    xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");  
    }  
   xmlhttp.onreadystatechange=function()  
    {  
     if (xmlhttp.readyState==4 && xmlhttp.status==200)  
     {  
     document.getElementById("sampige").src="/chitra_durga.jpg?r="+new Date().getTime();  
     }  
    }  
   xmlhttp.open("GET","/chitra_durga.jpg",true);  
   xmlhttp.send();  
   }  
   </script>  
   <!-- added by V.Rao [End] Ajax function to refresh the captcha image -->  
  

• After this, search for the form tag element and modify/replace the action attribute to call the custom Captcha validation JSP as opposed to the PeopleSoft delivered psp servlet as shown below
  

   <!-- Commented by V.Rao <form action="?cmd=login&languageCd=<%=languageCd%>" method="post" id="login" name="login" autocomplete="off" onSubmit="signin(document.login)"> -->  
   <!-- Added by V.Rao --><form aaction="<%=psCtxPath%><%=psHome%>/Rao99.jsp" method="post" id="login" name="login" autocomplete="off" onSubmit="<%=psCtxPath%><%=psHome%>/Rao99.jsp">   
  

• The next piece of customization on the signin html file is to bring in the captcha elements i.e captcha image, refresh icon to refresh the captcha image and a text box to capture the verification code entered by the user. The sample code from my signin page is shown below. Place this code below the "pwd" element.
  

   <!-- Code added by V.Rao [Begin] Captcha elements-->  
   <tr>  
   <td height="35"><img src="/chitra_durga.jpg" alt="<%=20000%>" id='sampige' width="175" height="65" />&nbsp;</td>  
   <td height="35" align='middle'>  
    <img src="<%=psCtxPath%><%=psHome%>/images/PT_REFRESH_ICN.gif" alt="Refresh" width="16" height="16" onclick="chitra()" />  
    &nbsp;</td>  
   <td height="35"><input type = "text" name="jcaptcha" placeholder="<%=20000%>" class="pslogineditbox" tabindex="4"></td>  
   </tr>  
   <!-- Code added by V.Rao [End] -->  
  

  You would have noticed that I have used scriptlet with the number 20000 in a couple of tags. This is the message number that serves as the alternate text for the captcha image as well as the default message shown in the text box provided for the user to key in the captcha verification code. This message text is defined in the text.properties file. The Ajax function chitra() that is triggered upon clicking the refresh icon in turn triggers the Java servlet "chitra_durga.jpg". The servlet generates and streams a fresh captcha image to the client browser.
• The last piece of customization on the signin html file is to modify the submit button action. Search for the input tag with type "Submit" and modify the code as given here
  

   <!-- Commented by V.Rao <td height="35"><input name="Submit" type="submit" class="psloginbutton" tabindex="4" value="<%=137%>" onclick="submitAction(document.login)"></td> -->  
   <!-- Added by V.Rao --> <td height="35"><input name="Submit" type="submit" class="psloginbutton" tabindex="4" value="<%=137%>" onclick="<%=psCtxPath%><%=psHome%>/Rao99.jsp"></td>  
  

• Open the text.properties file and add the code below at the very bottom
  

   # V.Rao [1-Sep-2014] Using 20000 and above for custom messages  
   20000=Enter the verification code appearing in the image  
  

• I also need to display a custom error message if the captcha validation fails. To do this open the error.properties file and add the code below at the very bottom
  

   # V.Rao [1-Sep-2014] Using 20000 and above for custom messages  
   20001=Verification code is required.  
   20002=Invalid verification code. Please try again.  
   20003=Verification failed.  
  

Step-4
In the HTML for the captcha elements, I had used the PeopleSoft delivered refresh icon - PT_REFRESH_ICN. Unfortunately this image is not available by default on the images folder in the web server. It is fetched from the database and cached in the webserver when a PeopleSoft page references the image. Therefore I decided to always have it available on the webserver. I did that by copying the refresh icon to the image folder located at 

 <%PS_HOME%>\webserv\<Domain>\applications\peoplesoft\PORTAL.war\<domain>\images  

Step-5
Create the JSP file that will intercept the login request and do the validation of the captcha entered by the user. The JSP does one of two things, forwards the login request to the psp servlet if captcha validation is successful or else re-load the login page with the custom captcha error message shown (error code = 20001). The code in my JSP file i.e Rao99.jsp is listed below.

 /**  
  * @author Venkateshwara Rao  
  * @Date  7-Sep-2014  
  */  
 <%@page import="com.octo.captcha.module.servlet.image.SimpleImageCaptchaServlet" %>  
 <%   
   String userCaptchaResponse = request.getParameter("jcaptcha");  
   userCaptchaResponse = userCaptchaResponse.trim();   
   Boolean captchapassed = false;  
   if( userCaptchaResponse.equals("") || userCaptchaResponse == null )  
   {  
 %>  
    <jsp:forward page="/psp/hcm92dmo/?cmd=login&errorCode=20001" >  
    </jsp:forward>  
 <%  
   }  
   else  
   {    // Captcha text is not blank or null  
        try  
        {   
             captchapassed = SimpleImageCaptchaServlet.validateResponse(request, userCaptchaResponse);  
        }  
        catch(Exception e)  
        {  
 %>  
     <jsp:forward page="/psp/hcm92dmo/?cmd=login&errorCode=20003" >  
     </jsp:forward>  
 <%  
        }  
        System.out.println("captcha passed = " + captchapassed ); /* Logging to weblogic console for debugging purpose */  
        if(captchapassed)  
        {  // On captcha validation success redirect to the psp servlet with login command  
 %>  
     <jsp:forward page="/psp/hcm92dmo/?cmd=login" >  
     </jsp:forward>  
 <%  
        }  
        else  
        {// On captcha validation failure redirect to the signon page with error code set to custom# - 20001  
 %>  
     <jsp:forward page="/psp/hcm92dmo/?cmd=login&errorCode=20002" >  
     </jsp:forward>  
 <%  
         }  
   }  
 %>  

The JSP file should be placed at this location on the web server

 <%PS_HOME%>\webserv\<Domain>\applications\peoplesoft\PORTAL.war\<Domain>  

All the modifications done above require a webserver bounce with cache clear before we can proceed to test.

Results
After the webserver bounce, access the PeopleSoft login page and you will see the login page with the Captcha image and the text below the password field  as shown below

Click on the refresh icon to receive a new captcha text/challenge as shown below

If the captcha verification code entered by the user is incorrect the login page is shown again with the custom captcha specific error message (this is the message I added to the error.properties file) as shown below

If the user managed to enter the correct captcha validation code but the PeopleSoft login credentials were incorrect then the login page is displayed back with the usual login id password incorrect message with a fresh captcha challenge as shown below

If the user manages to enter all the three parameters correctly then the servlet forwarding brings them to the homepage as shown below

The captcha validation success is also confirmed in the weblogic logs (with the system.out message I wrote in the JSP code) as shown below

After-thought 

Although it looks simple enough in retrospect, it took me a while to piece together this solution and many a things learnt along the way (things to do and not to do). If only Oracle would incorporate this feature there wouldn't be the need to put in this customized solution. At the end of the day, what I achieved here is more of a workaround than anything because the most logical place for the captcha validation call is within the psp/psc servlet code which validates the PeopleSoft user id and password. This is something customers cannot modify.

Working on this, took me back to my Java/J2EE developer days and made me realize App Designer is such a wonderful tool that simplifies so much of the development and code deployment aspects. With all the talk of JDeveloper replacing PeopleTools/AppDesigner makes me feel we will be going back to cavemen times. It is unlikely however powerful JDeveloper is, for it to replace the Application Designer's simplicity and ease of use.

Note: This solution/source code provided in this post is by no means a production ready one. It was a proof of concept done by me and you will have to make necessary changes at your end before deploying.

Implementing Captcha on PeopleSoft login page using JCaptcha - Part-1

In my previous post I discussed the steps to take for deploying the sample JCaptcha code within a PeopleSoft webserver and test it. In this post, I will be going through the steps to take in order to implement Captcha on the PeopleSoft login page using JCaptcha.  As this will be a long solution I'm breaking it into two parts.

Before I proceed, if you had followed the steps to deploy the sample war file and test that, you will have to undo all of the steps and bring your PeopleSoft instance to its original state.

A normal page is rendered by PeopleSoft servlet as shown below -

In essence, my captcha implementation solution approach intercepts the login request and validates the captcha challenge response before forwarding the request to PeopleSoft psp servlet as illustrated in diagram below 

At a high level, the implementation steps include - 

1. Copying the necessary JCaptcha jar file libraries and classes
2. Modify the PORTAL.war deployment descriptor file (web.xml)
3. Customizing the signin.html, text.properties and error.properties
4. Place the refresh image in the webserver image folder
5. Create a JSP file for the captcha validation

Step1:

From the JCaptcha 2.0 war file, extract the 6 jar files highlighted in the screenshot below and place them in the lib directory of the PeopleSoft Webserver - 

In your case the lib directory will be at this location - 

 <%PS_HOME%>\webserv\<domain>\applications\peoplesoft\PORTAL.war\WEB-INF\lib  

We need only the servlet class file (SimpleImageCaptchaServlet.class) from the 7th jar file of the war file. This is the class that renders the Captcha image.
Copy/Extract the class file to this location -

 <%PS_HOME%>\webserv\<domain>\applications\peoplesoft\PORTAL.war\WEB-INF\classes\com\octo\captcha\module\servlet\image  
 in my case  
 C:\psoft\pt85302\webserv\hcm92dmo\applications\peoplesoft\PORTAL.war\WEB-INF\classes\com\octo\captcha\module\servlet\image  

Note: You will have to create these folders (octo\captcha\module\servlet\image) as it won't exist.

Step2:
Now navigate to the WEB-INF folder and open the deployment descriptor file - web.xml and add the code below -

    <servlet>  
        <servlet-name>jcaptcha</servlet-name>  
        <servlet-class>com.octo.captcha.module.servlet.image.SimpleImageCaptchaServlet</servlet-class>  
    </servlet>  
   <servlet-mapping>  
       <servlet-name>jcaptcha</servlet-name>  
       <url-pattern>/chitra_durga.jpg/*</url-pattern>  
   </servlet-mapping>  

You may change the servlet mapping as per your wish.

Verification: If you have successfully executed these steps you can restart the webserver and do a quick test by accessing the servlet that renders the captcha image as shown below.

The rest of the solution will be covered in Part-2. For comments, feedback or questions drop a note.

Regards,
Rao

Test Drive JCaptcha API in PeopleSoft

The background
In Singapore where I have been working, most customers engage auditors to audit the PeopleSoft implementations and upgrades. One of the common finding off-late is the vulnerability of the application to coordinated attacks using scripts, bots etc (especially in the case of internet facing applications such as Candidate Gateway). 

The favourite remedy to this apparent drawback has been to think of implementing Captcha. It is my personal opinion that Captcha in itself does not offer a comprehensive solution to the problem. There are several blogs and articles on the net that have shown that Captcha can be broken (e.g. Using AI to beat CAPTCHA and post comment spam). 

At best it is a preventive measure that can give Organizations more time to react to an attack on the Web/IT infrastructure before the damage is extensive. This is akin to a moat surrounding the castle in ancient times which acted as a barrier to slow down an approaching army, giving the castle inhabitants time to react before the wall was breached. Nevertheless, until a better solution is found we will settle for a Captcha solution. 

Options

With this as the background, I set out to do a Google search to see if anyone has attempted and/or succeeded in this endeavor. However the search yielded very few links. Oracle support site also has no solutions on this topic (as of this writing). The most common and easy to implement solution is the re-captcha API from Google. This is good enough for internet web pages, blogs etc. There are few blogs (implementing-captcha-using-recaptcha-in.html) that discuss how to achieve this.

The drawback of using recaptcha is that the authentication of the response to the captcha challenge is done by google, and not within PeopleSoft application. The captcha challenge is sent by Google and so does the validation of the user response. This necessitates us to open the web/app connectivity to internet traffic so as to reach Google servers for obtaining the captcha validation result. This is virtual no no for Organizations that keep the application servers secure and out of reach by putting them behind firewalls, DMZ's and what not. 

Thus the only viable option was to have a home-grown (so as to speak) solution that will generate a Captcha challenge from PeopleSoft and does the authentication within PeopleSoft. I found this blog (captcha-implementation-of-peoplesoft.html) that succeeded in doing that.

The chosen one
However rather than reinvent the wheel from scratch, I was looking for something I could re-use and implement. Thus I discovered the JCaptcha Open source API and decided to test the waters with this one. The sourceforge page has the JCaptcha version 1.0 API that produces a very basic captcha image (in black and white) as-is unless of course you decide to modify the source code. 

Then I discovered the JCaptcha Version 2.0 alpha API here that produced a more colourful Captcha image. The wiki also has a nice write up on testing their API. 

Test Drive

The steps to manually deploy the JCaptcha sample application onto a PeopleSoft web server is described below -

• Download the war file (jcaptcha-integration-simple-servlet-sample.war) to a local folder on your PC/server.
• Copy the war file to this location  -
  

   <%PS_HOME%>\webserv\<domain>\applications\peoplesoft  

  In my case I have a windows VM. So the location is
  

   C:\psoft\pt85302\webserv\hcm92dmo\applications\peoplesoft  
  

• Modify the application.xml by adding the code below. I chose the name "Tungabhadra" for the context-root. You can name it anything you like.
  

    <module>  
     <web>  
      <web-uri>jcaptcha-integration-simple-servlet-sample.war</web-uri>  
      <context-root>/tungabhadra</context-root>  
     </web>  
    </module>  
  

  The application.xml can be found at 
  

   <%PS_HOME%>\webserv\<domain>\applications\peoplesoft\META-INF  

  For me the location is 
  

   C:\psoft\pt85302\webserv\hcm92dmo\applications\peoplesoft\META-INF  

• Restart the webserver
• Access the sample captcha page that we just deployed into PeopleSoft webserver by accessing the URL -
  

   http://<webserver_host_name>:<web_http_listener_port>/tungabhadra/index.jsp  
  

  Note: This is the same listener port which your PeopleSoft application listens to.
  
• The version 2.0 captcha page as seen on the browser -
  
  
  

In case you were wondering what the version 1.0 captcha looks like it renders an image like this - 

Verdict
The JCaptcha v2.0 Open Source API is easy to use and implement within PeopleSoft and it does a pretty good job of producing a nice modern looking random captcha image even with the basic DefaultGimpyEngine WordGenegration classes. The nice thing about this solution is that the image is created on the fly (using a word to image java classes) and directly streamed to the HTTP output and rendered on the client browser. The captcha authentication classes are deployed within the PeopleSoft application hosted within your own infrastructure which is exactly what I set out looking for. 

Having tested the waters now it is time to do an actual implementation on the PeopleSoft application (I will be attempting this on the signin page).  I will post the solution once I have it ready.