This discussion is archived
0 Replies Latest reply: May 20, 2013 4:25 AM by PrajaktaDeshpande RSS

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

PrajaktaDeshpande Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points