Using XML with BI Publisher

Here's a quick guide for this deadly combo:
XML, BI Publisher, and Application Engine.

We start by importing the delivered Application Package.

import PSXP_RPTDEFNMANAGER:*;

The following function is optional if

Using Java's SLEEP in Application Engine

Let's say you scheduled several nVision instances using your AE and you want to send an email right after everything ran to Success.

How do you force your AE to wait?

You can use this piece of code.

Scheduling nVision through AE

For obvious reasons, I cannot possible document the entire AE in this blogpost so I'll just give a quick rundown.

nVision is scheduled through a series of setup pages under this navigation
Main Menu > Reporting Tools > PS/nVision

Behind the curtains, PeopleSoft is inserting data to the following tables

• PSNVSBOOK
• NVS_SCOPE
• NVS_SCOPE_FIELD
• NVS_SCOPE_VALUE
• NVS_REPORT
• PTNVSQRYPROMPTS
• PSNVSBOOKREQUST

Which means, in order to successfully schedule an nVision instance, you just need to

Add "Download All" Button to Report Manager

I have always wondered why PeopleSoft never included a "Download All" button in all their releases for Report Manager.

Of course, messing with delivered objects is generally frowned upon.
But if you really wanted to implement one, here's a sample.

Excel Macro for SaveAs Filename

Haven't posted much about Excel but this one has been the most common request from business users.

This is a macro to override the "Save As" filename.

REGEXP_LIKE To Find Unwanted Characters

This SQL will search for filenames that contain characters other than

1. a-z
2. A-Z
3. 0-9
4. underscore
5. parenthesis
6. dash
7. period
8. square brackets

Find Temp Tables used by Application Engine

I've been running into situations where our application engines are not clearing the temp tables even after process instance has been deleted.

Also, multiple occurrences of the process halting for no reason.

Oracle's workaround is to clear the temp tables and restart the process.

But which temp tables do you clear?

Try this script.

Easy Import of Config File

I want to import PeopleSoft configuration files without going through the UI.

Let's use a .bat file.

Remove Dead Worklist Entries

I want to remove worklist entries which have dead links.

But they only gave me a screenshot of the worklist page.

1. Get OPRID of recipient
2. Get OPRID of sender. If you look at the screenshot, it's the guy under FROM column.
3. Get EOAWTHREAD_ID. If worklist is delivered, look at the 2nd value of the Link column.
4. Execute SQL below.

Export All Results from ORACLE SQL DEVELOPER

I want to export all my query results from Oracle SQL Developer

I have two (2) options.

1. Use /*csv*/
2. CTRL + End

Oracle SQL Developer
Version 2.1.1.64
Build MAIN-64.65

SELECT /*csv*/ *
FROM PS_JOB

Or just click on query results and click Ctrl + End.

Use SUBSELECT in PS Query Expression

I encountered this error when I used a subselect in my PSQuery expression.

FROM clauses are not allowed in expressions (50,536)
SQL error. Stmt #: 5682 Error Position: 107 Return: 936 - ORA-00936: missing expression
A SQL error occurred. Please consult your system log for details.
Error in running query because of SQL Error, Code=936, Message=ORA-00936: missing expression (50,380)

Here's the workaround: