0 Replies Latest reply: May 20, 2013 6:25 AM by Prajakta Deshpande RSS

    Oracle apps event alert error: when leave is applied through SSHR module

    Prajakta Deshpande
      Hi all,

      I am trying to generate an event alert in oracle apps for absence cases.
      e.g: I applied 2 leaves separately through SSHR and there is a public holiday between the 2 applied leaves. In this case alert should get generated. But no alert is generated.
      When the same leave is applied through HRMS module, alert gets generated properly.

      Code:
      SELECT papf.employee_number,
      papf.full_name,
      paa1.date_start,
      paa2.date_end,
      papf.email_address
      into &eno, &ename, &sdate, &edate, &eadd
      FROM per_absence_attendances paa1,
      per_absence_attendances paa2,
      pay_user_column_instances_f pucif,
      per_all_people_f papf
      WHERE paa1.person_id = paa2.person_id
      AND papf.person_id = paa1.person_id
      AND papf.person_id = paa2.person_id
      AND pucif.business_group_id = papf.business_group_id
      AND papf.business_group_id = paa1.business_group_id
      AND paa1.business_group_id = paa2.business_group_id
      AND papf.business_group_id = 82
      AND pucif.VALUE NOT IN ('Corporate')
      and PUCIF.VALUE between paa1.date_end and paa2.date_start
      and paa2.ROWID = :ROWID
      group by papf.employee_number,
      papf.full_name,
      paa1.date_start,
      paa2.date_start,
      paa1.date_end,
      paa2.date_end,
      papf.email_address
      having count(pucif.VALUE) >= 1 and ((paa2.date_start - paa1.date_end)-1) = count(PUCIF.VALUE)
      --------------------------------------------------------------------------------
      Please help.

      Thanks,
      Prajakta

      Edited by: Prajakta Deshpande on 20 May, 2013 4:25 AM