1 Reply Latest reply on Mar 17, 2016 10:24 AM by Jagadekara

    Event alert firing multiple times

    Jagadekara

      Hi All,

       

      I am doing an event alert for after update on hr_api_transactions table in r12.

       

      When an employee create an absence, we need to send an email.

       

      So I created an event alert, but some times it is sending multiple emails and some time only one email sending.

       

      This is my alert query.

       

      Select (Select Papf.Email_Address From Per_All_People_F Papf Where Person_Id=Extractvalue(Value(Xx_Row), '/PerAbsenceAttendancesEORow/ReplacementPersonId') And Sysdate Between Papf.Effective_Start_Date And Papf.Effective_End_Date) Replace_Email

            ,(Select Papf.Full_Name From Per_All_People_F Papf Where Person_Id=Extractvalue(Value(Xx_Row), '/PerAbsenceAttendancesEORow/ReplacementPersonId') And Sysdate Between Papf.Effective_Start_Date And Papf.Effective_End_Date) Replace_By

            ,Papf2.Full_Name

            ,Extractvalue(Value(Xx_Row), '/PerAbsenceAttendancesEORow/DateStart') As Date_Start

            ,Extractvalue(Value(Xx_Row), '/PerAbsenceAttendancesEORow/DateEnd') As date_end

      INTO &email,&replace_by,&employee_name,&date_start,&date_end

      From Hr_Api_Transactions Xx_Api,

           Per_All_people_f papf2,

          Table(Xmlsequence(Extract(Xmlparse(Document Transaction_Document Wellformed),

          '/Transaction/TransCache/AM/TXN/EO/PerAbsenceAttendancesEORow'))) Xx_Row

      Where Xx_Api.rowid=:rowid

      And Xx_Api.Creator_Person_Id=Papf2.Person_Id

      And xx_api.status='Y'

      And Sysdate Between Papf2.Effective_Start_Date And Papf2.Effective_End_Date

      ;

       

      In Action sets I checked the check box Suppress Duplicates also, but still it is sending duplicate emails.

       

      So any suggestion?

       

       

       

      Related thread.

      HRMS Absence Creation Table for Replace By field

        • 1. Re: Event alert firing multiple times
          Jagadekara

          Hi,

           

          I  checked that, when we enter absence details in that form (before submit), hr_api_transactions table is populated. But status='W'.

           

          Then when we click submit, that time status changed to Y. so I wrote status='Y' in my query. But still the table may updated again in some other columns.

          That's why it is sending multiple times with same email. So how can we restrict this?