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.
import PT_MCF_MAIL:MCFOutboundEmail;
/*
* 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";
Evaluate &fmt
When = 2
&ext = "pdf";
Break;
When = 5
&ext = "html";
Break;
When = 8
&ext = "xls";
Break;
When-Other
Error ("Don't know how to handle output type " | &fmt);
End-Evaluate;
Local string &attachName = &queryName | "." | &ext;
Local string &attachDescr = "Query Results for " | &queryName;
rem Tailor these for your own use;
&email.From = "
This e-mail address is being protected from spambots. You need JavaScript enabled to view it.
";
&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";
Return &email.Send();
End-Function;
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 existence 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.
Labels: 2009, AppEngine, PeopleCode, Process_Scheduler, Query
© 2011 Grey Heller Solutions.
All rights reserved.
- Ramesh
So if it's so easy why haven't Oracle done this yet I wonder? Will we see this in PT8.50 so you know?
Graham
However, if someone already has access to Query (and that data) then they can already do this. Being able to email the result doesn't change that.
@Graham - it was mentioned to me after posting this that is supposed to be in 8.50.
Also, I can attest to from my days in PeopleTools, there is a lot of detail into putting something into the core product. The code that I posted works, but isn't complete (the example I posted will email *every* scheduled query ; being able to filter this would be required for production code).
Then there is testing, documentation (in all supported languages), updating training manuals, etc. Not impossible obviously, but there's more work involved than just writing up here.
I would find it really funny though if the scheduled queries in 8.50 came in an email that said "Here's your Query. I hope you like it". :-)
what goes into '&sOutFile' variable?
To find it, simply open the PSQUERY program, and the ExecQry PeopleCode step. Search for RunToFile and you'll see it.
If you have PSIDE Helper, you can hit the following GSPSIDE:AEAPPLICATIONID.PSQUERY to go directly to it.
http://psst0101.wordpress.com/2008/10/28/deleting-files-from-peoplecode/
Ihave paste the code in my dev enviornment means declare the function and used the peoplcode RunToFile method() and after that put two lines(&to and &res) but it's not working could you pls. help me it's high priorty enhancemnt.I am working from last 3 days but what i am missing....Couldn't catch...pls. help.
Thanks
Balaji