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! ūüôā

 

Advertisements
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:

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

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