Workaround for Retaining Row Height in RTF Output Using Oracle BI Publisher

Generating PDF files using Oracle BI Publisher is pretty straightforward. If you have tables in your RTF template, and if the rows have a specific height and width, the PDF output would have exactly the same properties.

But, that isn’t the case with RTF output. For this particular case I had to work on, the report had to be generated in RTF format, so that, users can edit the RTF output if required, as deploying PDF editors on all systems to edit PDF output wasn’t a viable option. But, BI Publisher removes any row height properties when the output is in RTF format.

Here’s a screenshot of the RTF template:

RTF_Template

Here’s the output in PDF format, with the row height retained:

Output_PDF

Here’s the RTF Output with all row height properties lost:

RTF_Output

I raised an SR with Oracle and it appears that this is a limitation even with the latest versions of Oracle BI Publisher for Desktop, which might be fixed in future releases. I still had to find a way to retain the row height, and this is what I did – I added an invisible column in the RTF template with some content, which also needs to be invisible as well (set the font color to be the same as the background – in this case white).

Provided below is a screenshot of the RTF Template with placeholder text and column:

Placeholder_Text

This is how the RTF output looks like after the placeholder text and columns have been hidden – this is a workaround for the RTF template to retain row height, whereas width can be retained by clicking on the Preferred Width checkbox

Preferred_Row_Width

RTF_Output_Row_Height

If you’ve encountered this issue and have found different ways to solve it, please let me know and I’ll update the post with these details.

Have fun! 🙂

Posted in Code, Oracle, PeopleSoft | Tagged , , , , , | Leave a comment

SpreadSheet Journal Import in PeopleSoft – Solving User ID Issues – Excel 2016

We’ve been using the JRNL1.xls file along with JRNLMACRO.xla file for PeopleTools 8.53, to import Journals into PeopleSoft FSCM 9.1 – all with lower versions of Excel.

However, with Excel 2016 installed on their workstations, users thought they could start using the files delivered along with PeopleTools 8.54/8.55, as they were compatible with Excel 2016.

But, not without issues 🙂

If you’re not familiar setting up the Journal excel and Macro files, here’s a quick introduction:

The new set of files available from PeopleTools 8.54 onwards are JRNL1.xlsm and JRNLMCRO.xlam (note the  change in extension)

Step 1: Open the JRNL1.xlsm file

Step 2: Click on the Setup icon

Step 3: If you’re getting any Compile error messages, press Alt + F11 key to open up the Visual Basic Editor

Step 4: Ensure that you delete any previous instances of the Journal files

Step 5: On the Project pane on the left side, click on JournalBook and Click on Tools > References

capture_2

Step 6: Ensure that the setup is as per the screenshot provided below

capture_3

Step 7: On the Project pane on the left side, click on JRNLMCRO and Click on Tools > References and ensure it is setup as per the screenshot below

capture_4

Step 8: Click on the Setup icon in the home page of the JRNL1 file

capture_4_1

Step 9: If the User ID field is disabled, proceed with the following steps:

Step 10: From the Project page on the left side, under Jrnlmcro, double click on Form_JrnlHeader

capture_6

Step 11: Right click on the User ID field and select Properties

capture_7

Step 12: From the Properties page on the left, choose True for the Enabled property

capture_8

Step 13: Similarly, double click and open the Form_Options page, right click on the User ID field and select Properties

capture_11

capture_12

Step 14: Ensure that the Enabled property is set to True here as well

Step 15: Save your changes, close and re-open the excel file again

Step 16: Now, when you click on the Setup icon or edit a Journal, the User ID field should be disabled as in the screenshot provided below

capture_9

Not sure if this has been resolved in later PeopleTools versions (8.55), but, if this is an issue for you, feel free to go through the steps listed and you should be able to edit the User ID. Particularly useful, when you create the initial Spreadsheet file with Journals, but would like to pass it to someone else.

Hope this helps! 🙂

Posted in Code, PeopleSoft, Oracle | Tagged , , , , | Leave a comment

CopyURL Icon Missing in PeopleTools 8.54

Have you seen this icon on the top right corner of a PeopleSoft page?

If you haven’t, this icon is used to copy the current URL to the clipboard – which is controlled via Component properties.

For PeopleTools instances before 8.52, this seems to be appearing  in Chrome, IE and Firefox.

But, starting with PeopleTools 8.53, the CopyURL icon(which uses JavaScript) appears to have been disabled in Chrome and Firefox owing to Security issues – thereby not allowing access to the Clipboard. The only alternative is to use Internet Explorer or to write a custom script that would perform the same.

Hope this helps! 🙂

Posted in Oracle, Uncategorized | Tagged , , , | Leave a comment

Populating Tree Selector Tables in PeopleSoft

Hello All,

If you’re using Tree Selector tables for Department, Business Unit security, once you make changes to the Tree , the tree values in tree selector tables are deleted – while you can use nVision to populate the Tree Selector tables, PeopleSoft PS Query provides an easy a way to populate tree selector tables.

Provided below are the steps:

Step 1: Identify the tree whose tree selector table has to be refreshed

Step 2: Navigate to Main Menu > Reporting Tools > Query Manager > New Query

capture_1

Step 3: Add a table of  your choice to the PS Query

capture_2

Step 4: Select any field from the table selected in Step 3

capture_3

Step 5: Add a new criteria using the Criteria tab

capture_4

 

Step 6:  Choose a field as Expression 1 type and change the Condition Type to in tree. Also, click on the Tree Option radio button and click on the New Node List link

 capture_5

Step 7: Enter the tree identified as part of Step 1 and click on Search

capture_6

Step 8: Click on the Icon_1.jpg icon to the right of the root node

capture_7

Step 9: Ensure that the selected note has been added to the Selected Nodes List group box

capture_8

Step 10: Ensure that the selected nodes have been populated in the Criteria page

Step 11 : Click on OK and run the PS Query

Step 12: The Tree Selector tables will now be populated with requisite data

Hope this helps!

Have fun! 🙂

Posted in Code, PeopleSoft, PeopleSoft HCM | Tagged , , , | Leave a comment

SFTP Issues with PutAttachment & GetAttachment PeopleCode

Hey everyone!

Happy new year!

It’s been a while since I had blogged – here’s an interesting issue that I worked on. I have an AE process that uses PutAttachment and GetAttachment functions to upload a file to an SFTP server. Running a trace returned the following error:

736 08.06.23 0.000000 ExecutePutAttachment (error): Error: URL Identifier not provided. Use URL Identifier to connect.
737 08.06.23 0.000000 EvalPutAttachment (error): return code (1) indicates attempt to upload file to storage failed.
738 08.06.23 0.000000 EvalPutAttachment: finishing and returning 1.

When I looked at the code, the PutAttachment and GetAttachment functions were referencing a URL string instead of a URL definition.

PutAttachment(&URL, &att_ref | &array [&j], &URL_ID2);

Here, &URL is the variable referencing the SFTP URL.

It appears that if you would like to place files on an SFTP server, you would have to use the URL definition as such, instead of using a URL string. Using a URL string would work fine when you use a FTP URL. So, changing the PeopleCode to be as below, fixed this issue:

PutAttachment(URL.ATTACHMENT_SFTP_URL, &att_ref | &array [&j], &URL_ID2);

This was in PeopleSoft HCM 9.1 with PeopleTools 8.53.06 , by the way.

Hope this helps! 🙂

 

Posted in Code, Oracle, PeopleSoft | Tagged , , , | Leave a comment

BI Publisher Report runs to No Success -End of Memory Error

Hi all,

Here’s an issue we had encountered while running a BI Publisher report – that was supposed to be generated via an Application Engine. Provided below is the error message that was written to the log file.

Caused by: java.io.UTFDataFormatException: Invalid UTF8 encoding.
	at oracle.xdo11g.parser.v2.XMLUTF8Reader.checkUTF8Byte(XMLUTF8Reader.java:160)
	at oracle.xdo11g.parser.v2.XMLUTF8Reader.readUTF8Char(XMLUTF8Reader.java:187)
	at oracle.xdo11g.parser.v2.XMLUTF8Reader.fillBuffer(XMLUTF8Reader.java:120)
	at oracle.xdo11g.parser.v2.XMLByteReader.saveBuffer(XMLByteReader.java:450)
	at oracle.xdo11g.parser.v2.XMLReader.fillBuffer(XMLReader.java:2566)
	at oracle.xdo11g.parser.v2.XMLReader.scanChars(XMLReader.java:1645)
	at oracle.xdo11g.parser.v2.NonValidatingParser.parseText(NonValidatingParser.java:1451)
	at oracle.xdo11g.parser.v2.NonValidatingParser.parseElement(NonValidatingParser.java:1338)
	at oracle.xdo11g.parser.v2.NonValidatingParser.parseRootElement(NonValidatingParser.java:366)
	at oracle.xdo11g.parser.v2.NonValidatingParser.parseDocument(NonValidatingParser.java:312)
	at oracle.xdo11g.parser.v2.XMLParser.parse(XMLParser.java:311)
	... 12 more

Dec 09, 2016 3:07:32 PM oracle.xdo.template.FOProcessor 
SEVERE: End Memory: max=512MB, total=17MB, free=3MB
PeopleTools 8.53.06 - Application Engine
Copyright (c) 1988-2016 Oracle and/or its affiliates.
All Rights Reserved

The SEVERE: End Memory: max=512MB, total=17MB, free=3MB error is kinda misleading and is far from an actual memory issue. Investigating further, we found out that the issue was being caused by some French characters, just because the XML file that was generated by the Application Engine was not UTF8 encoded.

The fix was to use UTF8 encoding while writing the XML file – here’s a sample code snippet:

&File = GetFile(&sFile, "W", "UTF8", %FilePath_Absolute);

Meanwhile, as a workaround, we had to determine what data was causing the AE to fail, and we followed the steps provided below for the same:

  1. Copy paste the generated XML file in Notepad++ (available in Remote desktop)
  2. Do a Find (Ctrl + F)
  3. Choose Regular Expression as the Search Mode
  4. In the Find field, enter the regular expression which would return any non-ASCII characters within the file [^\x00-\x7F]+

Thanks to this blog for the wonderful tip! 🙂

Happy reading everyone! 🙂

Hope this helps!

Posted in Code, PeopleSoft | Tagged , , , , , | Leave a comment