Friday, August 21, 2015

Formatting Rich Text Comments in BI Publisher

In the last years, BI Publisher has become the go to tool to cover most reporting needs in PeopleSoft, replacing other technologies such as Crystal Reports and SQR in many scenarios.



The basic concept behind many reporting tools is separating data and presentation logic, so report designers can work in parallel with developers who know the data model in detail. BI Publisher is the PeopleSoft reporting tool that achieves this separation in a more thorough way. It does so by using XML as the information exchange format between the data generation and the report generator. Practically all systems have a way to export data in XML nowadays, and PeopleSoft is not the exception, with options ranging from Connected Queries, File Layouts to PeopleCode managed XMLDocs. From my point of view, this is major advantage over other technologies like Crystal Reports, which in its PeopleSoft version could only extract data from PeopleSoft queries (if you needed to extract somehow complex information, you would need to create an extraction program).

Other advantages of BI Publisher are the bursting capabilities (separating report output based on certain data fields) and the possibility to generate online reports without using Process Scheduler.

Formatting Rich Text Fields

I have to admit that I'm far from being a reporting expert, but in one of my latest projects I came accross the need to develop several of them. One of these reports needed to display comments previously entered by users in rich text format. BI Publisher provides a function to do so:
<?html2fo:elementname?>
However, this function has a problem I was not able to solve (I admit there could be other solutions but I could not find anything as part of my research in a few forums): if you are building a report with certain style guidelines, the rich text would always be rendered using Arial 12pt as the base font. This resulted in a very funny looking report, with large fonts coexisting with smaller ones. Of course, there was the option to also use Arial 12pt as the report base font, but users are not always ready to change their aesthetic requirements.

In the end, we found out that the html2fo function would render the rich text using the inline style of HTML elements. PeopleSoft normally does not set a font-family nor font-weight (please check the note at the end of the document), so BI Publisher automatically applies the default style, which is Arial 12pt. However if you set the style be yourself, BI Publisher would accept it.

The following code shows an extract of how we set this style:

Function FormatRichTextForBIP(&text As string, &fontSize As string) Returns string;
   Local string &result;
   
   If All(&fontSize) Then
      &result = "<div style='font-family: verdana;font-size: " | &fontSize | ";'>" | &text | "</div>";
   Else
      &result = "<div style='font-family: verdana;'>" | &text | "</div>";
   End-If;
   
   Return &result;
End-Function;


(...)
&reportingRec.COMMENTS.Value = FormatRichTextField(&inputRec.COMMENTS.Value, "12pt");
(...)

Note: This approach would not work if within the rich text the user has included different font sizes. This basic approach works when no font-family or font-weights are applied within the stored rich text HTML. In any case, this is a solvable issue, although it may require some more work. What you need to do is parse the rich text and replace the desired style clauses.

Tuesday, June 23, 2015

Creating Custom Listening Connectors using Integration Broker SDK

One of my customers recently had the need of allowing a third party web application to attach files into PeopleSoft. After trying a number of different approaches (the integration had to be done at the web application client level, which significantly reduces the options to manipulate the request to PeopleSoft before sending it, particularly when dealing with old web browsers), I gave up and came to the conclusion that I needed a custom listening connector in Integration Broker to implement such integration. 



The process of developing and installing a custom listening connector in Integration Broker is quite well described in PeopleBooks, however, I thought it would helpful to document the process I have followed.

Samples


PeopleSoft comes with some samples of connectors developed using the Integration Broker SDK. The most interesting one for listening connectors is ExampleServletListeningConnector.java located in the following folder:

$PIA_HOME/webserv/IFHRDEV/applications/peoplesoft/PSIGW.war/WEB-INF/SDK/src/samplelisteningconnectors.java

I've used this sample as the basis for my custom connector. In some cases, I also found quite handy to decompile the standard connectors such HttpListeningConnector. I could not find any exhaustive source of documentation of the Integration Broker SDK, so decompiling the existing connectors proved to be a good way to understand how to best use the SDK.

Note: For decompiling the Java class files I have used a very simple tool names cavaj. It is a very simple tool, but still helpful.

Development

Taking the previously mentioned sample as the basis, I have coded my own connector. If you are not interested in the details, you may want to skip to the next section.

What I needed to do in my connector was basically two things:

  1. Encode the incoming file using Base64, as binary files could not be processed otherwise by the existing PeopleCode Message API.
  2. Pass any parameters received in the HTTP Header or the URL as part of the IBInfo (Integration Broker information included in every internal Integration Broker message.


If you want to check the actual code, you can download the source code from this link:



Compiling

Once your source code is ready, you need to compile it. The first step for Java compilation is to set the environment variables so they point to the Java SDK. In my case, I was using the PeopleSoft HCM 9.2 Update Image 11, and these were the commands I needed to use:

export JAVA_HOME=/opt/oracle/psft/pt/jdk1.7.0_71
export PATH=$PATH:$JAVA_HOME/bin

Then I went to the directory were my java file was placed and run the following command to compile the file:

javac -cp /home/psadm2/psft/pt/8.54/webserv/peoplesoft/applications/peoplesoft/PSIGW.war/WEB-INF/classes:/home/psadm2/psft/pt/8.54/webserv/peoplesoft/applications/peoplesoft/PSIGW.war/WEB-INF/lib/mail.jar:/opt/oracle/psft/pt/bea/wlserver/server/lib/weblogic.jar FileUploadListeningConnector.java 

The paths may obviously differ in your case, but the important thing is to include the following directories/jar files in your class path:

  • $PIA_HOME/webserv/peoplesoft/applications/peoplesoft/PSIGW.war/WEB-INF/classes
  • mail.jar
  • weblogic.jar

Please note that weblogic.jar was needed because I was using WebLogic as my web server. In case you use WebSphere, you need to change this jar file.

Deployment

Once the file is compiled, you need to copy the resulting class file to the following directory:

$PIA_HOME/webserv/IFHRDEV/applications/peoplesoft/PSIGW.war/WEB-INF/classes/com/peoplesoft/pt/integrationgateway/listeningconnector

The next step is to let WebLogic know that there is a new servlet available. This can be done by editing the following file:

$PIA_HOME/webserv/IFHRDEV/applications/peoplesoft/PSIGW.war/WEB-INF/web.xml

All you need to do is to duplicate the sections referring to ExampleServletListeningConnector and replace those appearances with your connector name. In my case, I was using PeopleTools 8.54, and these were the lines I had to include:

(...)
<servlet>
<servlet-name>ExampleServletListeningConnector</servlet-name>
<servlet-class>com.peoplesoft.pt.integrationgateway.listeningconnector.ExampleServletListeningConnector</servlet-class>
</servlet>
<servlet>
<servlet-name>FileUploadListeningConnector</servlet-name>
<servlet-class>com.peoplesoft.pt.integrationgateway.listeningconnector.FileUploadListeningConnector</servlet-class>
</servlet>
(...)
<servlet-mapping>
<servlet-name>ExampleServletListeningConnector</servlet-name>
<url-pattern>/ExampleServletListeningConnector/*</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>FileUploadListeningConnector</servlet-name>
<url-pattern>/FileUploadListeningConnector/*</url-pattern>
</servlet-mapping>
(...)

Testing

In order to test the new connector, you need to reboot the web server so the changes made to register the new servlet are taken. Once this is done, you can check if the new listening connector is responding by using the declared URL:

http://webserver/PSIGW/FileUploadListeningConnector


Monday, March 30, 2015

A command-line alternative to PeopleSoft SendMaster

If you are familiar with PeopleSoft Integration Broker, I'm sure you have dealt with SendMaster to some degree. This is a very simple but yet useful tool to perform unit tests of the Integration Broker incoming service operations using plain XML (if I'm dealing with SOAP Web Services, I normally use SoapUI, for which there is a very good article on PeopleSoft Wiki).

Most of the time it's enough with SendMaster, but today I came through a problem that required an alternative. While testing an XML message with this tool against an HTTPS PeopleSoft installation, I got the following error message:

Error communicating with server: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

After checking in My Oracle Support, I've found the following resolution (doc 1634045.1):

The following steps will resolve the error:

1) Import the appropriate SSL certificate to the java kestore PS_HOME\jre\lib\security\cacerts or Integration Broker's keystore location i.e pskey file
2) Set sendmaster's preferences  ( via File-Preferences-HTTP tab )  to point to the keystore with the appropriate SSL certificate
3) Test

Unfortunately, I didn't have access to the appropriate SSL certificate, so I've decided to use curl, a pretty old (dating back to 1997 according to all knowing wikipedia) but still useful command line tool.



curl is a command line tool that can be used to test HTTP and HTTPS operations, including GET, PUT, POST and so on. One of the features of this tool is that it can run in "insecure" mode, eliminating the need of a client certificate to test URLs on HTTPS. Both in Linux and Mac OS, the option to run in insecure mode is -k. The command line to test my service operation then looked like:

curl -X POST -d @test.xml -k https://<server>/PSIGW/HttpListeningConnector 

Please note that the @ option actually requests curl to take the data from the file following it. Instead of doing so, you can specify the data in the command line, but it is a bit more cumbersome.

Also, keep in mind that curl is not delivered with Windows out of the box, but you can download similar tools from several sources (for instance, this one).







Monday, March 23, 2015

Manually applying Global Payroll Rules Packages downloaded from an Update Image

Last week we've faced an issue while applying a Tax Update in one of our PeopleSoft HCM 9.2 customers. The Tax Update was delivered as a PeopleSoft Release Patchset, that needs to be first applied to the Update Image before creating the Change Package using PeopleSoft Update Manager.

Unfortunately, during the process, one rules package delivered in the Tax Update was not included within the Change Assistant steps, and therefore it was missed. Some days after, we reported the error to Oracle and they pointed out to the original zip file containing the PeopleSoft Release Patchset, which indeed contained the missing package.

We did not want to repeat the entire Change Package definition steps, as it would have required to restore a couple of backups. Instead, we decided to manually apply the rules package.

Not so fast...

Unfortunately, within Update Manager the Rules Packages are not delivered in the usual format used to import, compare and copy them. Instead, specific steps are followed when Update Manager is used.

In the end, we managed to find a way to manually import the package, which is documented below.

Importing the Rules Package

The rules packages in Update Manager are delivered using the Data Migration Workbench. The process to import them starts by defining the directories from which the Data Migration projects should be picked:

PeopleTools > Lifecycle Tools > Migrate Data > Manage File Locations

The path should point to the PTADSAEPRCS directory within the patch (whose zip file needs to be extracted before). Once the path is defined, the Data Migration project can be copied using the Data Migration Workbench:

PeopleTools > Lifecycle Tools > Migrate Data > Data Migration Workbench

The project should now be uploaded using the Load Project From File link. A list of the projects found the previously defined path will be shown:



Once the project is selected and the Load button pressed, the Project Definition page within the Workbench will be shown:


Applying the Data Migration project is quite simple. In first place, the project needs to be compared using the Compare button and once the comparison has finished, the project has to be submitted for copy (using the Submit for Copy button). 

Note: Data Migration project submissions may need approval. In such case, make sure the request is approved, so the project is actually copied.

The best way to validate whether the project has been correctly copied or not is to check the contents of the PS_GP_PKG_ADS_DFN table, which should now contain the imported rules package.

Rules Package Merge

Once the rules package has been imported, it needs to be merged. The merge process actually takes all the imported rules package and builds a single rule package to simplify its application. Unfortunately, the process is not available from the user interface, but it can still be run using the command line:

<PS_HOME>\psae.exe -CT <database type> -CD <database name> -CO <PeopleSoft user> -CP <PS user password> -R ESP -AI GP_PKG_ADSMR -I 0 -OT 2 -OF 13 -OP <output directory> -CI <connect id> -CW <connect password>

Once the rules package is merged, an usual Rules Package will be accessible within the Global Payroll Packages functionality. From there on, the package can be applied using the steps we were used to.

Monday, March 9, 2015

Installing Languages on PeopleSoft Update Images

One of great things about PeopleSoft Update Manager images is that they could be used as a Demo environment to try the latest and greatest features of the PeopleSoft application. All you need to do is to download the image and install it and you can already play with the application.

However, the initial install of the Update Image will only have the English language enabled. If you are using PeopleSoft Update Manager, once you upload the target environment and define the change package, the application will automatically install the languages you have in place in your own environment. However, if you just want to install the Update Image and you do not have a target environment to upload, this approach is not feasible.

Below I describe the steps to follow in order to install additional languages into an Update Image without having a target environment.


1.- Launch the Update Image.

2.- Install the client database connectivity tools by running the installer shipped in the oracle-12c-client-64bit shared folder.

3.- Connect to the database using SQL Developer and run the following command:

insert into PS_PTIASPTARGETLNG values ('ESPDEMO', '<language>');

4.- Install the PeopleTools client by running the installer located in the client-854 shared folder.

5.- Connect to the Update Image environment using Application Designer and open the PTIASPLANG_VW.PTIASPLANGCD.FieldFormula PeopleCode. Once in there, add the lines in bold.

(...)
Function AssembleDMoverCommand() Returns array of string
   Local array of string &arrRet = CreateArrayRept("", 0);
   
   Local string &srvName;
   Local string &userID;
   Local string &userPwd;
   Local string &dmsLogPath;
   Local string &dmsPath;
   Local string &psdmtxPath;
   Local string &mlDatPath;
   
   Local string &paramName;
   Local string &paramValue;
   REM prepare data mover parameters from database;
   Local SQL &sqlParam = GetSQL(SQL.PTIASPDMPARAM);
   While &sqlParam.Fetch(&paramName, &paramValue)
      Evaluate &paramName
      When "USERID"
         &userID = &paramValue;
         Break;
      When "USERPWD"
         &userPwd = &paramValue;
         Break;
      When "DMSPATH"
         &dmsPath = &paramValue;
         Break;
      When "PSDMTXPATH"
         &psdmtxPath = &paramValue;
         Break;
      When "SVRNAME"
         &srvName = &paramValue;
         Break;
      When "DMLOGPATH"
         &dmsLogPath = &paramValue;
         Break;
      When "MLDATPATH"
         &mlDatPath = &paramValue;
         Break;
      When-Other
         Break;
      End-Evaluate;
   End-While;
   &sqlParam.Close();
   
   Local string &cmdString;
   &cmdString = &psdmtxPath | " -CT " | %DbType;
   If All(&srvName) Then
      &cmdString = &cmdString | " -CS " | &srvName;
   End-If;
   /* BNB - J.Delgado - 08 Mar 2015 - BEGIN */
   Local string &tmp;
   &tmp = &userPwd;
   /* BNB - J.Delgado - 08 Mar 2015 - END */
   If All(&userPwd) Then
      &userPwd = Decrypt("mldmpswd", &userPwd);
   End-If;
   /* BNB - J.Delgado - 08 Mar 2015 - BEGIN */
   If None(&userPwd) Then
      &userPwd = &tmp;
   End-If;
   /* BNB - J.Delgado - 08 Mar 2015 - END */
   &cmdString = &cmdString | " -CD " | %DbName | " -CO " | &userID | " -CP " | &userPwd | " -FP " | &dmsPath;
   
   REM DMover executable file location, whole DMover command, DMS Log File, Dat file Path, DMS file Path are pushed into the array for following process;
   &arrRet.Push(&psdmtxPath, &cmdString, &dmsLogPath, &mlDatPath, &dmsPath);
   Return &arrRet;
End-Function;
(...)

Note: This change removes the requirement of a previous target environment upload. 

6.- Connect to the PeopleSoft application using PIA. Associate the PTIASPMLLOAD Application Engine process to the AE_REQUEST component.



7.- Run the PTIASPMLLOAD process using the Request AE page. The language to be installed should associated to the PTIASPMLLDAET.PTIASPPROPVAL field.


Once the process is run, make sure you reboot the web server and application server in order to use the Update Image in the newly installed language.


Thursday, January 29, 2015

Advantages of using REST-based Integrations in PeopleSoft

REST-based services support were introduced in PeopleTools 8.52, although you may also build your own REST services using IScripts in previous releases (*). With PeopleTools 8.52, Integration Broker includes support for REST services, enabling PeopleSoft to act as both a consumer and a provider.

What is REST?


There is plenty of documentation in the Web about REST, its characteristics and benefits. I personally find the tutorial published by Dr. Elkstein (http://rest.elkstein.org) particularly illustrating.

In a nutshell, REST can be seen as a lightweight alternative to other traditional Web Services mechanisms such as RPC or SOAP. A REST integration has considerably less overhead than the two previously mentioned methods, and as a result is more efficient for many types of integrations.

Today, REST is the dominating standard for mobile applications (many of which use REST integrations to interact with the backend) and Rich Internet Applications using AJAX.

PeopleSoft Support


As I mentioned before, PeopleSoft support was included in PeopleTools 8.52. This included the possibility to use the Provide Web Service Wizard for REST services on top of the already supported SOAP services. Also, the Send Master and Handler Tester utilities were updated so they could be used with REST.

PeopleTools 8.53 delivered support for one of the most interesting features of REST GET integrations: caching. Using this feature, PeopleSoft can, as a service provider, indicate that the response should be cached (using the SetRESTCache method of the Message object). In this way, the next time a consumer asks for the service, the response will be retrieved from the cache instead of executing the service again. This is particularly useful when the returned information does not change very often (ie.: list of countries, languages, etc.), and can lead to performance gains over a similar SOAP integration.

PeopleTools 8.54 brought, as in many other areas, significant improvements to the PeopleSoft support. In first place, the security of inbound services (in which PeopleSoft acts as the provider) was enhanced to require that the services are consumed using SSL, basic HTTP authentication, and basic HTTP authentication and SSL, or none of these.

On top of that, Query Access Services (QAS) were also made accessible through REST, so the creation of new provider services can be as easy as creating a new query and exposing it to REST.

Finally, the new Mobile Application Platform (an alternative way to FLUID to mobilise PeopleSoft contents) also uses REST as a cornerstone.

Conclusions


Although REST support is relatively new compared to SOAP web services, it has been supported by PeopleSoft for a while now. Its efficiency and performance (remember GET services caching) makes it an ideal choice for multiple integration scenarios. I'm currently building a mobile platform that interacts with PeopleSoft using REST services. This is keeping me busy and you may have noticed that I'm not posting so regularly in this blog, but hopefully in some time from now I will be able to share with you some learned lessons from a large scale REST implementation.


(*) Although it's possible to build REST services using IScripts, the Integration Broker solution introduced in PeopleTools 8.52 is considerably easier to implement and maintain. So, if you are in PeopleTools 8.52 release or higher, Integration Broker would be the preferred approach. If you are in an earlier release, actually a PeopleTools upgrade would the preferred approach, but I understand there might be other constraints. :)

Saturday, December 20, 2014

PeopleTools 8.54 Feature: Support for Oracle Database Materialized Views

One of the new features of PeopleTools 8.54 is the support of Oracle Database Materialized Views. In a nutshell, Materialized Views can be seen as a snapshot of a given view. When you query a Materialized View, the data is not necessarily accessed online, but instead it is retrieved from the latest snapshot. This can greatly contribute to improve query performance, particularly for complex SQLs or Pivot Grids.

Materialized Views Features


Apart from the performance benefits associated with them, one of the most interesting features of Materialized Views is how the data refresh is handled. Oracle Database supports two ways of refreshing data:


  • On Commit: data is refreshed whenever a commit takes place in any of the underlying tables. In a way, this method is equivalent to maintaining through triggers a staging table (the Materialized View) whenever the source table changes, but all this complexity is hidden from the developer. Unfortunately, this method is only available with join-based or single table aggregate views.

Although it has the benefit of almost retrieving online information, normally you would use the On Commit for views based on tables that do not change very often. As every time a commit is made, the information is refreshed in the Materialized View, the insert, update and delete performance on the source tables will be affected.
Hint: You would normally use On Commit method for views based on Control tables, not Transactional tables.

  • On Demand: data is refreshed on demand. This option is valid for all types of views, and implies that the Materialized View data is only refreshed when requested by the administrator. PeopleTools 8.54 include a page named Materialized View Maintenance where the on demand refreshes can be configured to be run periodically.




In case you choose the On Demand method, the data refresh can actually be done following two different methods:


  • Fast, which just refreshed the rows in the Materialized View affected by the changes made to the source records.


  • Full, which fully recalculated the Materialized View contents. This method is preferable when large volume changes between refreshes are usually performed against the source records. Also, this option is required after certain types of updates on the source records (ie: INSERT statements using the APPEND hint). Finally, this method is required when one of the source records is also a Materialized View and has been refreshed using the Full method. 


How can we use them in PeopleTools?


Before PeopleTools 8.54, Materialized Views could be used as an Oracle Database feature, but the DBA would need to be responsible of editing the Application Designer build scripts to include the specific syntax for this kind of views. On top of that, the DBA would need to schedule the data refresh directly from the database.

PeopleTools 8.54 introduces support within PeopleSoft tools. In first place, Application Designer will now show new options for View records:



We have already seen what Refresh Mode and Refresh Method mean. The Build Options indicate to Application Designer whether the Materialized View date needs to be calculated upon its build is executed or if it could be delayed until the first refresh is requested from the Materialized View Maintenance page.

This page is used to determine when to refresh the Materialized Views. The refresh can be executed for multiple views at once and scheduled using the usual PeopleTools Process Scheduler recurrence features. Alternatively, the Refresh Interval [seconds] may be used to indicate the database that this view needs to be refreshed every n seconds.

Limitations


The main disadvantage of using Materialized Views is that they are specific to Oracle Database. They will not work if you are using any other platform, in which case the view acts like a normal view, which keeps a similar functional behaviour, but without all the performance advantages of Materialized Views.

Conclusions


All in all, Materialized Views provide a very interesting feature to improve the system performance, while keeping the information reasonably up to date. Personally, I wish I've had this feature available for many of the reports I've built in all these years... :-)