×
UX/Mobile/Responsive

Emailing Scheduled PS/Query Results

By Larry Grey • March 10, 2009

 

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 = "process.scheduler@example.com";
&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 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.

Labels: , , , ,

Stay Updated

16 Replies to “Emailing Scheduled PS/Query Results”

  1. Hi Chris,

    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

  2. What is the code though? I’m pretty new to peoplecode. I haven’t been able to find anything on it. Closest thing i found was a DeleteAttachment function.

  3. You should be able to use the standard PeopleCode – O/S functions for deleting files after sending it.

  4. The &sOutFile variable is part of the PeopleSoft-delivered PSQUERY app engine program. It is the actual filename that comes from running the query.

    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.

  5. I have an interesting problem that happens on a occassion. I have some queries set up to execute bi-weekly from process scheduler and lately the queries seem to be running on their own. You can look at the process scheduler monitor and it shows them to be in queued status. There is no listing of them running at all. They will run every 4 minutes.

  6. Yes, it’s definitely in there. Just sent myself a query in an 8.50 environment that was built from scratch and does not have this modification in it.

  7. I was looking in the Release Notes for PT 8.5, and couldn’t find confirmation that this functionality was added. Did I miss it? Can anyone here confirm that PT 8.5 includes email distribution from scheduled queries?

  8. @Spamboy – you are correct to point out the issue of data security. That’s something that everyone should be thinking about in their PeopleSoft environments.

    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”. 🙂

  9. We’ll be trying this out this week as our users have been asking for this feature for some time.

    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

  10. We do something very similar at my office, for the business who cannot be bothered with logging into PeopleSoft to check the report results. One danger with this code — you should add some switch to control what report results can be emailed. For example, I can see the danger of someone querying the EMPLOYEES table in HRMS and emailing SSNs, birthdates, etc.

  11. Can you not schedule a query and distribute the results by entering the email ids on the Distribute To page? I am not sure if I am missing something.

    – Ramesh

Comments are closed.

Request a Demo