Emailing Scheduled Query Results
A Quick and Easy Workaround to this PeopleSoft Limitation
Posted on 12/16/2006 (originally published in print on 7/1/2005)
by Joe Moglia
I was so excited. We finished the application 8.8 and 8.45 tools upgrade last Friday evening without issue and I was dying to show off some of the new functionality. I didn't have to wait long; first thing Monday morning I answered the phone to find a department head on the other end of the line with a desperate plea to have the results of a query delivered to a his managers every morning… "starting this morning if possible." I said, "No problem. Give me 10 minutes. Let me create the query for you and schedule it".
The query was written in 5 minutes. I then navigated to 'Reporting Tools, Query, Schedule Query", chose the query in the run control and hit the Run button. Then disaster struck. I thought it was a mistake. I couldn't believe it. Why would they do this? Under the Type column, an Email option was no where to be found. There was no way to deliver the results of a scheduled query via email. I logged a case with PeopleSoft.
PeopleSoft literally called me in 30 minutes and said everything was "working as designed" but "we will put in an enhancement request for you." Obviously this is not exactly what I was hoping to hear. Not willing to wait for Peoplesoft I decided to fix it myself.
This article will take you through my quick solution to this possible oversight. It's not overly complicated, or even that eloquent of a solution, but it works rather well for us and I hope at the very least it might spark some ideas for your own solution.
To fix the issue I cloned the delivered 'Schedule Query' Application Engine program and made a few minor changes to the PeopleCode found there. Then I whipped up a few sneaky SQL updates, and created a new Process Definition. It was that easy. Let me walk you through it.
Open the Application Engine program called PSQUERY. Then clone ("Save as") the program with your own name. In my example I named it MY_PSQUERY. Open up the only peoplecode action in the program. Find where the ExecQuery() function begins and consider making changes to the delivered code as seen in Listing 1.
To continue reading this article you must have a current VP1 Subscription.
