6 Replies Latest reply: Jul 16, 2012 11:08 PM by 917011 RSS

    Supress Email alerts

    917011
      Hi there,
      I've been asked to develop an email alert for the Payroll team.
      Basically its to send out an email alert to the team when any employee has used up more then 20 days of sick leave within a calendar year for payroll deductions.
      I've managed to build a query that checks the relevant tables that store the information, however how do i suppress duplicates?

      For example, if an employee uses 21 days of sick leave on Tuesday, the alert is set to trigger on a daily basis and an email gets sent out to payroll.
      However, the same email gets sent out on Wednesday because the conditions are still true. The employee has used up >20 days.
      On thursday the same thing.

      If i change it to only trigger when the 'leave' table gets updated, it would still trigger duplicates as whenever any update is done to the table, the conditions are true again and an email gets sent out.

      Any advise?
      Thanks
        • 1. Re: Supress Email alerts
          917011
          Additionally, I found this in some Oracle Docs

          To create a self-referencing alert:
          You create a self-referencing alert the same way you would create any other alert, but
          you tailor your alert Select statement by adding to the Where clause a comparison
          between the values in the implicit input :DATE_LAST_CHECKED and the column that
          contains the date and time information that you want your alert to reference.
          Example
          Suppose your Select statement contains the following lines:
          .
          .
          WHERE ...
          AND creation_date > TO_DATE(:DATE_LAST_CHECKED,
          'DD-MON-YYYY HH24:MI:SS')
          In this example, Oracle Alert compares the date and time information in the column
          CREATION_DATE with the date and time information in :DATE_LAST_CHECKED to
          determine which exceptions are new.


          However, my alert cannot be 'verified' or 'run' when I use the above statement, even after changing to
          WHERE TO_DATE(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') > TO_DATE(:DATE_LAST_CHECKED,'DD-MON-YYYY HH24:MI:SS')
          • 2. Re: Supress Email alerts
            917011
            or should i set keep history to 365 days and set it up to suppress duplicates?
            • 3. Re: Supress Email alerts
              Sandeep Gandhi, Consultant
              DATE_LAST_CHECKED is a date field.
              Have you tried
              WHERE creation_date > :date_last_checked

              Hope this helps,
              Sandeep Gandhi
              • 4. Re: Supress Email alerts
                917011
                Hi Sandeep,

                is creation_date another system provided value?
                should I use SYSDATE or something instead?
                thanks
                • 5. Re: Supress Email alerts
                  Sandeep Gandhi, Consultant
                  If you use sysdate, then the condition sysdate > :date_last_checked will always be true.
                  You should use creation_date.

                  Hope this helps,
                  Sandeep Gandhi
                  • 6. Re: Supress Email alerts
                    917011
                    HI Sandeep,
                    i tried using creation_date but it not letting me validate the alert.
                    is there something that i'm missing?
                    thanks