PSSpreadsheet Class Part 2 | Documentation Update, Date Format (converting to epoch) and more

This is a continuation of 'Using PSSpreadsheet Class in App Engine' where I demonstrated how we can use this new API to generate Excel spreadsheets using PeopleCode. While there were and still are a lot of gaps in the documentation of this API, it provides an easier method to create spreadsheets from PeopleCode.

Documentation Update

Previously, in the notes section, I explained the significance of the second parameter (rowset processing) in the Open method which was not documented in PeopleBooks at that point in time. This has been updated.

PSSpreadSheet - Open Method PeopleBook

This is also documented in the following My Oracle Support - Doc Id which clarifies the RangeApplyStyle method as well.
E-PC: There Is Missing Documentation In PeopleBooks For PeopleCode PSSpreadsheet Class - Methods Open And RangeApplyStyle (Doc ID 2357922.1)

Other useful documents on My Oracle Support are listed below.
E-PC: PSSpreadsheet SetColumnWidth Method Is Not Working (Doc ID 2354586.1)
E-NV: What is RangeApplyStyle() in PSSpreadsheet API ? (Doc ID 2288870.1)
E-PC: PeopleCode API PSSpreadSheet SetActiveSheetName method is not working (Doc ID 2511065.1)
E-PC/NV - Method RangeApplyStyle Of Class PSSpreadsheet Is Not Available On PT8.55 And PT8.56 (Doc ID 2516382.1)
Bug 29384267 : ERRORRETURN-> 255 - FILE IS ALREADY IN OPEN STATE! WITH PSSPREADSHEET

Needless to say, that while some of the reported issues are fixed/clarified, there are still improvements to be made which I hope will follow suit in subsequent releases/patches of PeopleTools.

Date Format for SetCellDate

After reviewing a comment thread on my previous post, I was curious to find out more about the SetCellDate method.

Comment thread
PSSpreadsheet - SetCellDate PeopleBook

If we see the example provided in PeopleBooks, a value (string) of 120223323 produces a cell (date format) with the following value.
1/2/1970  9:23:43 AM

So what is this mystery number/value 120223323 that magically converts to a date of 1/2/1970  9:23:43 AM? After many days of spinning my wheels, I finally found (thanks to Oracle Support) that this is actually the date in epoch format (date/time in milliseconds since epoch). Wow! Why? 😂

One reason I can think of is that behind the scenes PSSpreadSheet API is implemented in Java (Apache POI) and therefore may require this format to pass into appropriate java methods? My guess is as good as yours. In any case, there is potential for this to be improved where this date calculation/conversion can be masked from the API user (perhaps with an internal conversion) for the date parameter in the SetCellDate method.

Anyway, now how do we calculate the date in epoch timestamp format if we ever want to use this method successfully under the current circumstances? There are some online converters which we can use to get ourselves familiarized with the calculation.

https://www.epochconverter.com/
https://www.freeformatter.com/epoch-timestamp-to-date-converter.html

Note: Keep in mind that the SetCellDate method expects the epoch timestamp to be in milliseconds (some converts provide the timestamp value in seconds which might throw us off when used in PeopleCode).

That said, how do we do this calculation/conversion in PeopleCode? I have not found a delivered function and/or API that helps with this calculation/conversion. So, I resorted to using javascript and executing it on the server side using a very handy technique demonstrated by Jim Marion on his blog here.

JavaScript to convert date to epoch timestamp format (milliseconds)

Here is a simple JavaScript that converts a human readable date to epoch timestamp format. You can follow this link to test the code directly in jsFiddle.

https://jsfiddle.net/SasankVemana/bgLk8sez/



Sample PeopleCode Snippet with SetCellDate example

JavaScript in Message Catalog (29999, 1)

Message Catalog (29000, 1) for reference

GitHub Project Update

In my previous post, I shared a sample App Engine program that will simply generate Excel spreadsheet using data from a rowset. It was brought to my attention that if a column contains a date/datetime field, then the spreadsheet is not generated as expected. This is definitely a limiting factor when it comes to using the rowset processing option in the PSSpreadSheet class.

I created another GitHub project which generates a spreadsheet via an App Engine which uses non-rowset processing option. If you are looking for examples, the App Designer project can be downloaded and used evaluation purposes.

GitHub Project: https://github.com/SasankVemana/PSSpreadsheet-in-App-Engine-2

This sample App Engine will produce a spreadsheet as follows.

PSPRSMNAVINFO - Portal Menu Navigation in SQL

Last week, I worked on a requirement where I needed to find all the components that are part of a role (or a list of roles) including the Portal Menu Navigation Path if it exists. It is easy to get the list of components that are part of a role but it is not really straight forward to qualify that information with the actual Portal Menu Navigation (if it exists).

 In the past, I used the following query to return the Portal Menu Navigation Path for a Component. The query mines through the Portal Structure and Content and presents the navigation hierarchy in the result.

As we can see, it will not be easy to re-purpose this hierarchical query and expand it for my requirement.

While poking around on this topic, I stumbled on this gem of a table PSPRSMNAVINFO that, I believe, was introduced thanks to the PeopleSoft Search Framework!

Here is what we can see in the Definition Properties of this record:
"Holds the Navigation Path to the given target crefs registered in Portal Registry. Holds only effective and non-expired target crefs.
Used by the Portal Menu SES Search.
Updated every schedule of search index file"

From my understanding, it appears that this table is used as part of the PeopleSoft Search Framework - PTPORTALREGISTRY Search Definition. Also, the App Engine library PORTALPATHAE is responsible for populating this table with the navigation path information when the search index is built.

PeopleTools > Search Framework > Designer > Search Definition (PTPORTALREGISTRY)

PeopleTools > Search Framework > Administration > Schedule Search Index

To identify the last time the PTPORTALREGISTRY search definition index was built, we can query the table PSPRSMPATHSTAT.

If for any reason we don't have the Search Framework enabled and we still want to take advantage of the data in this table, then we can simply populate this table by directly running the PORTALPATHAE App Engine Library from App Designer. Since I was testing this on a Virtual Box installation of a PUM image (CS 9.2 - Image 4), I did not have Search enabled/configured. I ran the AE from App Designer as follows:

Now, let us see how we can use PSPRSMNAVINFO to get the Portal Menu Navigation information.

The best part about this table is that it allows us to directly retrieve the navigation path for a component without having to deal with the hierarchical structure in PSPRSMDEFN. This makes it easier to use/expand this information in other queries.

Here is how I extended this information to create a query that retrieves all components in a role along with the Portal Menu Navigation Path information (if it exists).


Github link to SQL Queries in this post:
https://github.com/SasankVemana/PS-Portal-Menu-Navigation-Queries

Using PSSpreadsheet Class in App Engine

Jim Marion brought the PSSpreadsheet class to our notice during Oracle OpenWorld conference last year. If you want a copy of his slide deck, you can find it in the below link (may not work for long, so grab it before they take it down):
https://oracle.rainfocus.com/scripts/catalog/oow16.jsp?search=marion&search.event=oracleopenworld

There is a great delivered example under the following navigation:
Reporting Tools > PS/nVision > Spread Sheet API Sample Page

This page provides a working example of how to use the PSSpreadsheet class to render a spreadsheet to the end user via an IScript (WEBLIB_SSAPI.ISCRIPT1.FieldFormula).

Peoplebooks:
Understanding The PSSpreadsheet Class

Someone asked if we could use the same PeopleCode API to create spreadsheets using an App Engine and post the file to the Report Manager? Here is an example of how we can do just that.

Sample App Engine PeopleCode:

Results:

The file should show up in Process Monitor as follows:

Notes:
- Currently, if we use the Open method of the PSSpreadsheet class to create the spreadsheet, it creates the file in the PS_HOME directory by default. There appears to be no way to pass in a parameter such as %FilePath_Relative to create the file in the process instance directory which would in turn help with posting the file to the Report Manager (Perhaps a good enhancement?). To workaround that, I wrote some code to dynamically derive the process instance directory and pass that as the first parameter (filename) for the Open method.
- The second parameter for the Open method is a boolean. We can enable Rowset processing by setting it to True. This does not seem to be documented in PeopleBooks but we can figure it out based on code in the IScript example (WEBLIB_SSAPI.ISCRIPT1.FieldFormula).

Sample Project in GitHub:
https://github.com/SasankVemana/PSSpreadsheet-in-App-Engine

Consider voting for idea to enhance PSSpreadsheet class Open method:
https://community.oracle.com/ideas/16749

PeopleCode for Reference:

/* Get Process Instance Directory */
SQLExec("SELECT PRCSOUTPUTDIR FROM PS_CDM_LIST WHERE PRCSINSTANCE = :1", SV_AE_TEST_AET.PROCESS_INSTANCE, &path);

/* Get Directory Separator */
&dirSep = "/";
If Substring(GetEnv("PS_SERVDIR"), 1, 1) <> "/" Then
   &dirSep = "\";
End-If;;

/* Set Output File Name */
&outFileName = "HelloWorld.xlsx";

/* Create Spreadsheet using PSSpreadsheet class */
Local object &ss;
&ss = CreateObject("PSSpreadsheet");
&ss.Open(&path | &dirSep | &outFileName, True);

/* Load data using Rowset */
Local Rowset &rs = CreateRowset(Record.PSMSGCATDEFN);
&rs.Fill("where MESSAGE_SET_NBR = :1", 4);
&ss.SetRowSetData(&rs);

/* Save */
&ss.save();

DTD (Document Type Definition) Validations During Message Transformations

While testing cXML Invoice service operations in a FSCM 9.2 application, I found some interesting information regarding DTD (Document Type Definition) validation of messages particularly when there are transformations.

CXML_INVOICE is a delivered service that allows third party systems to send invoice data using cXML protocol.

When I was testing the CXML_INVOICE.v1 (synchronous service operation) in a FSCM 9.2 environment (PeopleTools 8.54.07), the service operation was returning an error although there was no issues with XML message. If the same xml message was used to invoke the CXML_INVOICE.v1 service operation in a FSCM 9.1 (PeopleTools 8.50.10), the invoice was processed without any errors.

I enabled tracing at the integration gateway and also at the message transformation level to further troubleshoot this issue. This service operation CXML_INVOICE.v1 has a transformation on the inbound request:

 

I found that the xml message was getting wrapped in a CDATA section as follows:

Original Request:
<?xml version="1.0"?>
<!DOCTYPE cXML SYSTEM "http://xml.cXML.org/schemas/cXML/1.2.011/InvoiceDetail.dtd">
<cXML payloadID="TEST_INVOICE_123" timestamp="2015-03-27T06:19:25-05:00" xml:lang="en-US">
</cXML>

Request with CDATA wrapper:

<?xml version="1.0"?>
<data>
 <![CDATA[<?xml version="1.0"?>
<!DOCTYPE cXML SYSTEM "http://xml.cXML.org/schemas/cXML/1.2.011/InvoiceDetail.dtd">
<cXML payloadID="TEST_INVOICE_123" timestamp="2015-03-27T06:19:25-05:00" xml:lang="en-US">
</cXML>
]]>
</data>

Due to this mysterious CDATA wrapping, the message transformation failed because the CDATA section changed the entire structure of the xml causing the xsl to fail while transforming the data to the appropriate fields in the destination format.

After some research on My Oracle Support, it was interesting to find the reason for the CDATA wrapping behavior. It turns out that the CDATA wrapper was caused by DTD validation issues on the Application Server.

Notice, <!DOCTYPE cXML SYSTEM "http://xml.cXML.org/schemas/cXML/1.2.011/InvoiceDetail.dtd"> in the original xml? This represents the Document Type Definition for the XML document. The application server would validate any inbound XML against the DTD if the DOCTYPE information is provided. The issue here is that the application server is unable to reach the location (possibly due to firewall issues) of the DTD for validation and therefore wrapped the inbound XML in a CDATA section.

On My Oracle Support there are a couple of documents which detail similar (not same) issues and provide some workaround options.

Refer:
E-IB: Asynchronous Inbound Message With DTD Reference Was Wrapped As PsNonXML, Causing a Transformation Failure (Doc ID 1941030.1)
E-IB: DTD Validation Causes Time-Outs or Significant Delays During XML Processing (Doc ID 850271.1)

I used the workaround option to suppress DTD validation at the application server level using an Integration Broker property on the configuration file (psappsrv.cfg):

[Integration Broker]
;=========================================================================
; General settings for the Integration Broker
;=========================================================================
DTD Lookup=0

Once I made this change, the DTD validation was bypassed and subsequently the CDATA wrapper was also suppressed resulting in the transformation app engine to work as expected.

Note: This DTD Lookup IB configuration on the app server configuration file should not be confused with the ig.dtdLookup property on the IB gateway properties file.

I thought this was worth a blog post because I could not find much information on the DTD Lookup Integration Broker application server configuration in PeopleBooks (other than on My Oracle Support). Hope you find this useful!

The other workarounds that were suggested were:

1. Remove the DTD URL from the XML documents. I don't think this is a viable option particularly in integrations that deal with third party systems where we cannot dictate the syntax and semantics.
2. Place the DTD document that cannot be reached at its native location onto the same path on another HTTP server that is accessible to the app server (could be even the PIA web server). I found this to be a very interesting solution if we run into this issue and suppressing DTD validations is not an option. Please refer Doc ID 850271.1 for more details.

APPMSGARCH Process - Performance Tuning

I recently worked on a requirement to tune the performance of the delivered APPMSGARCH process (batch approach used to archive service operation data). The process was taking longer to run everyday and got to a point where it would run for over 12 hours.

While investigating the problem in production, it was found that the following SQL was the main cause for our performance issue.

SqlExec is located in APPMSGARCH.MAIN.GBL.default.1900-01-01.ARCHASYN.OnExecute:

DELETE
FROM PSAPMSGPUBDATA
WHERE EXISTS
  (SELECT '*'
  FROM PSAPMSGARCHTMP B,
    PSAPMSGSUBCON C
  WHERE (PSAPMSGPUBDATA.IBTRANSACTIONID = C.CANONICALTRSFRMID
  OR PSAPMSGPUBDATA.IBTRANSACTIONID     = C.IBTRANSACTIONID)
  AND B.IBTRANSACTIONID                 = C.IBPUBTRANSACTID
  );

Note: This is not to imply that all performance problems with this process is directly related to this SQL. There could be other issues depending on each individual environment. But I do find that the structure of all the SQLs particularly the DELETEs follow a similar theme (with the usage of EXISTS clause). So it could be a common problem for which the following solution could be applied.

Once it was identified that this SQL was the main issue in our environment, I tried to look in My Oracle Support for potential solutions (the first place I would look to research a problem with anything delivered). I found this document E-IB: APPMSGARCH Performance Issue (Doc ID 754437.1).

Amongst other things in the document, it was recommended to replace the SQL (mentioned above) with two different SQL statements to separate the OR clause.

SQL 1:
DELETE
FROM PSAPMSGPUBDATA
WHERE EXISTS
  (SELECT '*'
  FROM PSAPMSGARCHTMP B,
    PSAPMSGSUBCON C
  WHERE PSAPMSGPUBDATA.IBTRANSACTIONID = C.CANONICALTRSFRMID
  AND B.IBTRANSACTIONID                = C.IBPUBTRANSACTID
  );

SQL 2:
DELETE
FROM PSAPMSGPUBDATA
WHERE EXISTS
  (SELECT '*'
  FROM PSAPMSGARCHTMP B,
    PSAPMSGSUBCON C
  WHERE PSAPMSGPUBDATA.IBTRANSACTIONID = C.IBTRANSACTIONID
  AND B.IBTRANSACTIONID                = C.IBPUBTRANSACTID
  );

Since it was a recommendation of potential value (and one that was pertinent to our problem), I went ahead and applied this change and tested again. Unfortunately, it did not help with the performance at all.

At this point, I started looking into the data in the tables as well as the SQL statements to identify any tuning opportunities. I found that PSAPMSGPUBDATA had around 800,000 rows of data in it. The way the SQL statements are written, it appears that the process would go over each and every row in the table (PSAPMSGPUBDATA) and check for the EXISTS clause before deleting.

Here is how I re-wrote the SQL statements which helped considerably.

SQL1 (Re-write):
  DELETE
FROM PSAPMSGPUBDATA
WHERE IBTRANSACTIONID IN
  (SELECT C.CANONICALTRSFRMID
  FROM PSAPMSGARCHTMP B,
    PSAPMSGSUBCON C
  WHERE B.IBTRANSACTIONID = C.IBPUBTRANSACTID
  );

SQL2 (Re-write):
DELETE
FROM PSAPMSGPUBDATA
WHERE IBTRANSACTIONID IN
  (SELECT C.IBTRANSACTIONID
  FROM PSAPMSGARCHTMP B,
    PSAPMSGSUBCON C
  WHERE B.IBTRANSACTIONID = C.IBPUBTRANSACTID
  );

If you notice the new SQL statements, you will find that the main difference was the replacement of the EXISTS clause with a IN clause.

Now, instead of looping through each row in PSAPMSGPUBDATA and checking the EXISTS clause, the SQL would just look for the rows that are in the results of the IN clause.

Here is a good article that details the usage and difference between the EXISTS and IN clause:
Usage of EXISTS and IN clause

If you are having issues with other DELETE sql statements (using EXISTS) in the APPMSGARCH app engine then you could try a similar approach. As always test the changes in your environment to see if you achieve the desired performance gains.

Note: Alternate solutions that I know have helped others in improving performance of APPMSGARCH :

1. Indexing affected tables appropriately.
2. Staging transaction ids that need to be deleted in a custom table (temporarily) to avoid complex where clauses in the DELETE statements.