1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Event alert firing multiple times

Discussion in 'Oracle Apps Technical' started by jagadekara, Mar 17, 2016.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    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?
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    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?