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!

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

PS Query Statistics

All,

Here’s a quick code snippet that you can run to get details on how many times a PS Query has been run, the most recent date time it was run and so on –  the table to use is PSQRYSTATS.

SELECT DISTINCT a.qryname AS "Query",
B.DESCR AS "Query Description",
SUM(a.execcount) AS "# of Runs",
MAX(a.lastexecdttm) AS "Last Run Date"
FROM PSQRYSTATS A,
PSQRYDEFN B
WHERE A.QRYNAME = B.QRYNAME
AND a.qryname LIKE 'X_%'
GROUP BY a.qryname,
b.descr
ORDER BY 3 DESC;

Hope this helps! 🙂

 

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

PSTREE2PDF runs to No Success in PeopleSoft

All,

Here’s an interesting issue:

  1. In any PeopleSoft instance, Navigate to Main Menu > Tree Manager > Tree Manager
  2. Open any tree and click on the Export to PDF hyperlink
  3. In the run control page that appears, click on the Run button and submit the PSTREE2PDF process
  4. If your process runs to No Success, see if the following error message appears:

First operand of . is NULL, so cannot access member name. (180,236) PSTREE2PDF.XMLFile.GBL.default.1 900-01-01.Step01.OnExecute PCPC:27842 Statement:473

Also, verify if you are able to open the record PSTREEMGRVAR (for example). If you get the error message as below, then the issue is related to the Object definitions that the permission list assigned to your user profile can access.
0

To resolve this issue, follow the steps provided below:

Step 1: Navigate to Main Menu > PeopleTools > Security > user Profile and note down your Primary Permission list – say, ALLPAGES

Step 2: Open up an instance of Application Designer

Step 3: Navigate to Go > Definition Security

1

Step 4: Once the PS Definition Security window opens, navigate to File > Open > Permission List

Step 5: Search for your primary permission list and click on OK

Step 6: You would have two panes – Group ID and Excluded Group ID

2

Step 7: Select all applicable items from the right pane and click on the Double arrow button

3

Step 8 : Select File > Save

Step 9 : See if you’re able to access record PSTREEMGRVAR

Now, try re-running the TREE2PDF process and it should run to success now.

Have fun! 🙂

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

No default message version defined for message error in PeopleSoft

Hello all,

Here’s an error message that I spent a considerable time debugging. I  was trying  to trigger a Full Sync from an App engine, but got the error message listed below:

No default message version defined for message RS_ASSGN_ST_TBL. (2,899) XX_SYNC.MAIN.GBL.default. 1900-01-01.Step01.OnExecute  PCPC:151  Statement:3

It turns out that the Message name defined needs to have the same name as the Service/Service Operation. Doing that, resolved this error. I’m not sure if that is a criteria for setting up a message, but hope this helps! 🙂

 

Posted in PeopleSoft | Tagged , , | Leave a comment

Excel to CI utility not working in Excel 2016

The Excel to CI utility that comes with PeopleSoft is very useful when it comes to loading multiple rows of data from an Excel template onto PeopleSoft. This seems to have been working fine with Excel 2007/2010, but failed with the following error in Microsoft Excel 2016.

Compile error : User-defined type not defined

Compile_Error

Here are the steps that I followed to get the data loaded to PeopleSoft. The primary root cause of this issue is the XML Parser version that defaults in Office 2016 and the DOMDocument code that is used in the VB Script – both have to match, else resulting in an error during compile time. Without further ado, here are the steps!

Here are the instance details for your reference:

Host OS: Windows 10 Home
PeopleTools 8.55.01
PeopleSoft HCM 9.2
Microsoft Excel 2016 32-bit version

Step 1 : Open an instance of Excel 2016 and ensure that the settings are the same as in the screenshot provided below:

You can access the same using File > Options > Trust Center and Click on Trust Center settings

Trust_Center_SettingsTrust_Center_AddInsSettingsTrust_Center_ActiveXSettings

Step 2 : Open the ExcelToCI2007.xlsm file from the PS_HOME directory.

ExcelToCI_HomePage

If you have a Demo version installed on a Windows Host, you can find out the home directory using Configuration Manager.

Open an instance of Configuration Manager

Click on Profile tab and then on the Edit button

Profile_Tab

Click on the Process Scheduler tab and copy the path available under the PeopleSoft Home Directory path

Process_Scheduler_Tab

Step 3 : Next, add the Developer tab to the ribbon

To enable the Developer tab, follow the steps provided below:

Select File > Options

In the Excel options window that opens, click on the Customize Ribbon option. On the right pane, check the Developer option and click OK.

Enable_Developer_Tab

Ensure that the Developer tab appears now and click on it

Step 4 : Click on the Visual Basic icon

Visual_Basic_Button

Step 5 : In the Visual Basic editor that opens, open the StagingAndSubmission code

StagingAndSubmission

Step 6 : Select Debug > Compile ExcelToCI and you would get the error message now

Compile_ExcelToCI

To resolve the error, please follow the steps provided below:

Step 7 : First off, ensure you have the latest MS XML Parser enabled. To verify:

Select Tools > References from within the VB Editor

XML6_References

Ensure Microsoft XML, v6.0 is checked. Else, scroll through the list and select the same. Also, ensure that any other MS XML version like v3.0 is unchecked.

Step 8 : Clicking on OK would set the cursor to the following line in the VB Editor. After changing each occurrence, compile the code using Debug > Compile ExcelToCI to ensure you haven’t missed any occurrence

DOMDocument_VB

Replace every occurrence of DOMDocument with DOMDocument60

Replace every occurrence of ServerXMLHTTP with ServerXMLHTTP60

Replace every occurrence of xDoc As DOMDocument with xDoc As DOMDocument60

Replace every occurrence of xHTTP As ServerXMLHTTP with xHTTP As ServerXMLHTTP60

Replace every occurrence of New DOMDocument with New DOMDocument60

Long story short, add 60 to each DOMDocument name to make it compatible with MS XML 6.0

Code_Modified

Step 9 : Re-compile the code once more and ensure there are no more errors. Close the VB Editor and proceed with loading data using ExcelToCI template. You’re all set!!

Please post your questions in the comment section, if you have any.

Hope this helps! 🙂

 

 

Posted in Code, Operating System, Oracle, PeopleSoft, Software | Tagged , , , , , , , | 22 Comments

Issues with Oracle BI Publisher with Microsoft Word 2016

For PeopleSoft XML reports, we usually add the BI Publisher plug-in to MS Word, load the XML files, create RTF templates and the list goes on.

When you download and install the BI Publisher from PeopleSoft (Design Helper), at first load of Microsoft Word 2016, the BI Publisher tab should appear in the ribbon. If it doesn’t, it means either the add-in is restricted, disabled or does not work. Office 2016 isn’t certified yet to be used with Oracle BI Publisher, but there’s a workaround. Provided below are multiple steps that you can follow to get this issue resolved and have the BI Publisher tab appear permanently in Microsoft Word 2016.

For reference purposes, here are the details of my environment:

Windows 10 Home 64-bit

Microsoft Office 2016 32-bit

PeopleSoft HCM 9.2

PeopleTools 8.55.01

Step 1 : To find out which version of Office 2016 you have installed, open up an instance of Word 2016, File > Account > About Word

Find_Version_Edit

Step 2 : Download and install BI Publisher from Design Helper

Navigation : Main Menu > Reporting Tools > BI Publisher > Setup > Design Helper

Choose the setup file appropriate to your version of Microsoft Word 2016 (either 32/64-bit) and download the same

Design_Helper

Step 3 : Before starting with the installation, ensure all instances of Microsoft Word have been closed. If you’re on a remote desktop, ensure that all instances of Word have been closed by all users who have logged on to the server.

Step 4 : Once the installation is complete,  search for Enable or Disable Template Builder from the installed programs list in Windows

Enable_Or_Disable_Template_Builder

Step 5 : Click on Enable or Disable Template Builder

Enable_Disable_1Enable_Disable_2

Step 6 : Click OK

Click on Button 1

Enable_Disable_3

And you’re done!

Open up an instance of Word 2016 and see if the BI Publisher tab appears in the ribbon. Ensure the BI Publisher tab does not disappear by opening Word 2016 couple of times.

BI_Publisher_Tab_Office_2016

If it doesn’t appear at all or disappears if you open Word 2016 after a while, try the following:

Step 7 : Go to File > Options > Trust Center

Step 8 : Click on Trust Center settings

Trust_Center

Step 9 : Ensure that the settings are the same as in the screenshot provided below:

Trust_Center_Settings

Step 10 : Click on ActiveX settings and ensure that the settings are the same as in the screenshot provided below:

Trust_Center_ActiveXSettings

Step 1 1: Click on Add-ins and ensure that the settings are the same as in the screenshot provided below:

Trust_Center_AddInsSettings

Step 12: Click on OK and then select Add-ins from the Word Options dialog

Step 13 : Check if BI Publisher Template Builder for Word is listed under Inactive Application Add-ins

Step 14 : Select it and at the bottom of the dialog, select COM Add-ins and click on the Go button

AddIns_Inactive

Step 15 : In the COM Add-ins dialog that appears, select the BI Publisher Template Builder for Word checkbox and ensure that the Load Behavior is Load at Startup and not Unloaded.

COM_AddIns_LoadBehavior

Click on OK to close the dialog. Close the Word 2016 instance and open a new instance. The BI Publisher tab should appear in the ribbon.

If it still doesn’t appear in the ribbon, repeat Step 15 again. Ensure that the Load Behavior is not Unloaded.  If the Load Behavior has been reset or if the checkbox has been cleared, please follow the steps provided below.

Step 16 : Ensure that you have Administrator permissions to your system.

Step 17 : Open the run prompt – Windows + R and type regedit

Step 18 : Look at the following two registry entries:

HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\Word\Addins\TBAddIn

HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\Word\Addins\TBAddIn

Step 19 : Look for the DWORD value named LoadBehavior

Registry_LoadBehavior

Step 20 : Double click on LoadBehavior and set its value to 3 for both Registry paths provided above

LoadBehavior_Value_Changed

Step 21 : Click on OK and exit the Registry Editor. Open the Registry again and ensure that the value has not been overwritten.

Go to step 15 again and ensure that the COM add-in is checked. Close and restart the Word 2016 instance again and see if the BI Publisher tab appears in the ribbon. The BI Publisher add-in will now appear in the Active add-ins list.

AddIns_Active

Hopefully all these steps should resolve this issue.

Please add any questions that you may have in the comments section.

Hope this helps! 🙂

Posted in Operating System, PeopleSoft, Software | 53 Comments

Using Colon in an Expression result in PeopleSoft PS Query

Here’s a quick PS Query snippet : If you’ve ever wanted to use a colon in an expression – say something like displaying a colon between HH(hours) and MM(minutes), PeopleSoft PS Query would throw an error – because the Query parser thinks that the colon represents a bind variable.

Instead, you can create an expression that takes in the ASCII value of colon and use it in your PS Query.

So, your expression should be something like this:

HH | char(58) | MM

and the result would be

HH:MM

Here’s the ASCII table for reference : ASCII

Hope this helps! 🙂

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