Search code examples
sqlemailnotificationsalertpeoplesoft

Sending Email based on PS Query using Alerts and Notifications HCM92

I need to come up with a PeopleSoft query that will list employees that have the same MAIL address and HOME address, then either send that list to payroll via email, or send an individual email to each employee letting them know their HOME and MAIL addresses are the same.

I figured out how to send out emails through the alerts system but all it's sending is an stdout file in the email body. I would like the email body to either show the list of employees with duplicate addresses or send an individualized email to each employee showing their duplicate addresses.

This is the query PS generated for me:

    SELECT A.EMPLID, A.ADDRESS_TYPE, A.ADDRESS1 
    FROM PS_ADDRESSES A 
    WHERE ( ( A.ADDRESS_TYPE = 'HOME' 
    OR A.ADDRESS_TYPE = 'MAIL')) 
    ORDER BY 1, 2, 3
Solution

  • Why don't you use the Schedule Query functionality to send an email with the report output in attachment?

    Main Menu > Reporting Tools > Query > Schedule Query

    In the Process Scheduler Request pop-up set Type to Email and Format to xls, like below: enter image description here

    Then click on 'Distribution' and set the values for your email and the addresses, can be users, roles or just an email address you want: enter image description here