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!

Oracle Alert R12.1.1

Discussion in 'Oracle Apps Technical' started by ushanair, Feb 17, 2014.

  1. ushanair

    ushanair Active Member

    Messages:
    12
    Likes Received:
    2
    Trophy Points:
    90
    Team,

    we are planning to introduce alert for employee termination, HR would like to send alert to three department regarding termination. We developed the query and tested but the alert sending three email for one termination. It is an update event on PER_PERIODS_OF_SERVICE and verified Oracle history observed three exceptions always.

    Highlights


    Action Level : Summary
    Operating Unit : Assigned
    Suppress duplicates : Enabled
    Apps version 12.1.1


    Request team view on this.

    Regards
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi can you provide screen shot of that?
     
  3. ushanair

    ushanair Active Member

    Messages:
    12
    Likes Received:
    2
    Trophy Points:
    90
    Hi

    The following SQL used in Alert.

    select distinct a.employee_number,a.full_name,to_char(b.date_start,'DD-MON-YYYY') start_date,
    to_char(b.actual_termination_date,'DD-MON-YYYY') Resigned_date
    into &emp_no,&f_name,&s_date,&r_date
    from
    per_all_people_f a, PER_PERIODS_OF_SERVICE b
    where a.person_id=b.person_id
    and a.effective_end_date=b.actual_termination_date
    and b.actual_termination_date is not null
    and b.rowid=:rowid;
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    Your query is ok. But I need screenshot of that,

    Or provide full details like which table, alert type, insert or update checked?
     
  5. ushanair

    ushanair Active Member

    Messages:
    12
    Likes Received:
    2
    Trophy Points:
    90
    Hi,

    The details are below:

    Alert Type : Event
    Keep : 1 days
    Application: Human Resources
    Table Name : PER_PERIODS_OF_SERVICE
    When : After Update
    Action Level : Summary
    Suppress Duplicate : Checked
    Operating Unit : Assigned
    Check for Duplicate: Checked in Alert Details - Output Tab

    Thanks & Regards
     
  6. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    May be , When you terminate PER_PERIODS_OF_SERVICE table updated 3 times. that's why it is sending alerts 3 times.
     
  7. ushanair

    ushanair Active Member

    Messages:
    12
    Likes Received:
    2
    Trophy Points:
    90
    Hi,

    Thanks Mr. Reddy,

    Yes, due to three exceptions we are getting three emails. Is there any solution to restrict or shall I go with custom trigger for PER_PERIODS_OF_SERVICE on Actual_termination_date column thus I can restrict the number of exceptions.

    Please advise.

    Thanks & Regards
     
  8. ushanair

    ushanair Active Member

    Messages:
    12
    Likes Received:
    2
    Trophy Points:
    90
    Hi,

    One more things; while getting alert, the alert always shows full email address not the email display name. Any profile need to be tuned.

    Please advise.

    Thanks & Regards
     
  9. ushanair

    ushanair Active Member

    Messages:
    12
    Likes Received:
    2
    Trophy Points:
    90
    Team

    Any help on my request, planning to close today.

    Thanks in advance.

    Regards
     
  10. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi
    ushanair,

    I created one trigger on per_periods_of_service table. but it is updating 3times when we terminate the employee. So 3 records inserting into my custom table.

    Trying to get latest record. I will let you know once I succeed on this issue.
     
  11. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Ushanair,

    We can create a staging table and trigger on per_periods_of_service table to insert record into that staging table once if we terminate an employee and we can create alert on that table using after insert event.

    Code (Text):


    Table Creation Syntax: For sample created with just 2 columns

    create table tbl_period(person_id number(25),term_date date);


    Trigger Syntax:
    CREATE or replace TRIGGER trg_period_service after update on per_periods_of_service
    FOR EACH ROW
    begin
    if :old.actual_termination_date is null or :old.actual_termination_date <> :new.actual_termination_date then
    insert into tbl_period values(:new.person_id,:new.actual_termination_date);
    end if;
    end trg_period_service;

     
    This trigger will fire 3 times but only once it will insert record into the custom table due to if condition. Please let us know if any issues faced further.
     
    jagadekara likes this.
  12. ushanair

    ushanair Active Member

    Messages:
    12
    Likes Received:
    2
    Trophy Points:
    90
    Thanks Mr. Reddy,

    I will share the outcome from my side, planning to do the same process.

    Regards
     
  13. ushanair

    ushanair Active Member

    Messages:
    12
    Likes Received:
    2
    Trophy Points:
    90
    Thanks Mr.Bharat,

    I will try at my end, and share the information soon.

    Thanks
     
    Bharat likes this.
  14. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi Bharat,

    I too tried. It's working fine.
     
    Bharat likes this.
  15. ushanair

    ushanair Active Member

    Messages:
    12
    Likes Received:
    2
    Trophy Points:
    90
    Thanks Mr. Bharat

    I tested in test instances working fine. Many thanks for your timely help. One more advise, still the alert shows the complete email address we want to show the email's display name, like 'ERP Alert' . Display name is assigned in email server.

    Please advise.

    Regards