BI Publisher Template ID from Template File Definition

Happy new year!

Quick code snippet to get the BI Publisher Template ID from the Template Filename (.rtf):

WHERE template_fileid IN
(SELECT fileid
WHERE userfilename LIKE '%Templates.rtf%'

Hope this helps!


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

Delete PS Queries assigned to a User

Quick post after a very long time… ūüôā

Here’s a code snippet that you can use to Delete PS Queries that are assigned to a particular user.

delete from PSQRYDEFN where QRYNAME = <your Query Name goes here> and OPRID = <UserID>;
delete from PSQRYFIELD where QRYNAME = <your Query Name goes here> and OPRID = <UserID>;
delete from PSQRYCRITERIA where QRYNAME = <your Query Name goes here> and OPRID = <UserID>;
delete from PSQRYEXPR where QRYNAME = <your Query Name goes here> and OPRID = <UserID>;
delete from PSQRYBIND where QRYNAME = <your Query Name goes here> and OPRID = <UserID>;
delete from PSQRYRECORD where QRYNAME = <your Query Name goes here> and OPRID = <UserID>;
delete from PSQRYSELECT where QRYNAME = <your Query Name goes here> and OPRID = <UserID>;
delete from PSQRYSTATS where QRYNAME = <your Query Name goes here> and OPRID = <UserID>;
delete from PSQRYEXECLOG where QRYNAME = <your Query Name goes here> and OPRID = <UserID>;

Hope this helps! ūüôā


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

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:


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


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


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:


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



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


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


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


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


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


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


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


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



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


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, Oracle, PeopleSoft | 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


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


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


Step 5: Add a new criteria using the Criteria tab



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


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


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


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


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