Creating query-based alerts

To create a query-based alert, SQL knowledge or the assistance of someone with SQL knowledge is required.

To create a query-based alert:

  1. Select Maintenance > Messaging > Alert Definitions.
  2. Click Create New Entry.
  3. Specify the name of the new alert in the Alert Name field and a brief description in the Description field.
  4. Specify the start time and end time in the fields that are provided. These times define when the alert can be run by the Job Scheduler.
    Be sure that the start and end times that you specify are coordinated with the times that are defined for the Job Scheduler task that runs the alert.

    For example, if the alert start and end times are set to 10:00am and 3:00pm and the Job Scheduler task that runs the alert is set to run every hour between 9:00am and 4:00pm, the alert is not generated by the Job Scheduler task at 9:00am and 4:00pm.

  5. Select alert recipients from these options:
    • Select the individual users to receive the alert from the Recipient User Names lookup.
    • Select the teams to receive the alert from the Recipient Team Names lookup.
    • Select the roles to receive the alert from the Recipient Role Names lookup.
  6. Optionally, select the Send to Self check box to send the alert to all the employees that are selected in the alert query. Employees can only see their own information.
  7. Optionally, select the Recipient Check Scheduled check box to send the alert to only employees who are scheduled to work. Recipients that are not scheduled to work when the alert is sent do not receive the alert message.
  8. Select Query from the Source Type drop-down list.
  9. Specify the SQL code for the alert in the Source Sql field. You can specify your own SQL statement for a custom alert.
    Note: When the Recipient Role Names or Send to Self fields are used, the SQL must always contain an EMP_ID value.

    For example, the Not Clocked-in Alert that is provided with the application uses this SQL:

    SELECT emp_id, emp_name, emp_lastname, emp_firstname
    FROM employee
    WHERE emp_id IN (SELECT work_summary.emp_id
        FROM work_summary, employee_schedule
        WHERE employee_schedule.work_date = work_summary.wrks_work_date
        AND employee_schedule.emp_id = work_summary.emp_id
        AND employee_schedule.empskd_act_start_time <>
            employee_schedule.empskd_act_end_time
        AND wrks_clocks is null
        AND wrks_work_date = trunc(sysdate))
  10. Leave the Source Class field blank for query-based alerts.
  11. Specify a brief description of the message subject in the Message Subject field.
  12. Specify the title to display at the top of the alert message in the Message Header field.
  13. Optionally, specify a message to display at the bottom of the alert message in the Message Footer field.
  14. Optionally, select the Send Message If Blank check box to send alerts with no query results to recipients.
  15. Select one of these options from the Message Content Type drop-down list:
    • text: Uses plain text to format the message output.
    • HTML: Uses HTML to format the message output.

    Messages cannot exceed 4000 characters in length, including HTML code.

  16. Click Save.
Now that the alert is created, schedule the alert using the Job Scheduler.