One question that occasionally pops up is how to email the results from running PS/Query through the process scheduler. This isn’t supported by PeopleTools currently, but you can do it with a minor customization.
The customization involves changing the PeopleTools delivered PSQUERY application engine job. That is what actually gets run when you schedule a query. There is some PeopleCode in there that uses the PS/Query API to actually run the query. So, what we want to do is grab the output after it has been generated, but before it gets posted out to the report repository.
Instead of doing the customization directly inline, we’ll add the bulk of it as an external function and then just reference that function from inside the PSQUERY app engine program. For those that have been participating in our webinars (specifically in the Development Best Practices session) know that we do it this way because we can cut the cost of maintaining the customization quite a bit like this.
Here’s the function that we’ll define in our work record.
* Custom function for sending scheduled queries via email.
* This gets called from the PSQUERY Application Engine program.
Function EmailQueryResult(&qryFile As string, &queryName As string, &fmt As integer, &to As string) Returns integer;
Local string &CR = Char(13) | Char(10);
Local PT_MCF_MAIL:MCFOutboundEmail &email;
&email = create PT_MCF_MAIL:MCFOutboundEmail();
&email.Recipients = &to;
rem Calculate our file attachment information;
Local string &ext = "txt";
When = 2
&ext = "pdf";
When = 5
&ext = "html";
When = 8
&ext = "xls";
Error ("Don't know how to handle output type " | &fmt);
Local string &attachName = &queryName | "." | &ext;
Local string &attachDescr = "Query Results for " | &queryName;
rem Tailor these for your own use;
&email.From = "firstname.lastname@example.org";
&email.Subject = "This is the subject line";
&email.Text = "The query is attached." | &CR | &CR | "I hope you like it.";
&email.AddAttachment(&qryFile | "." | &ext, %FilePath_Absolute, &attachName, &attachDescr, "", "");
&email.SMTPServer = "localhost";
In the PSQUERY application engine program you’ll want to declare a reference to that function, and then add the following lines immediately after the line with the .RunToFile() method call.
When that returns, the query has been run and the generated file is stored in the local process scheduler file system. After PSQUERY finishes running and the process scheduler sends the output over to the report repository, the files stored locally will be deleted, so this is a good spot to hook in.
PSQUERY changes - these lines go directly after the &aQry.RunToFile call
REM Should test result and/or file exist ence before emailing;
Local string &to = "comma.separated.list, of.recipients.goes.here";
Local integer &res = EmailQueryResult(&sOutFile, PSQUERY_AET.QRYNAME, %OutDestFormat, &to);
Now when you schedule a PS/Query to run, it will automatically email the results.
Obviously this code isn’t completely production ready. The email subject and body and from are hard-coded, and the list of email addresses to actually send the query output to would need to be calculated from the process request itself, but this is good enough for you to see how it would work in your environment.
If you’re interested in learning more about how to cut the cost of creating and maintaining customizations like this, let us know and we’ll get another “Development Best Practices for PeopleSoft Enterprise” webinar scheduled.