I have a requirement where I have to put a validation on SSHR absence screen to stop employees to apply for same type of leaves or for same period.
For e.g -> Suppose employee A has applied for a Privilege leave from 01-Oct-2012 to 02-oct-2012. Now he should not be able to apply any Privilege Leave until this
applied leave gets approved. Additionaly, he should not be able to apply any other leave for 01-Oct-2012 and 02-OCt-2012. These validations has to come in effect at the entry screen only.
As soon as A tries to violate any of the above rules while applying for leaves, he should get an error.
I tried to use a 'user hook' at create_absence api to achieve this. But there are some shortcomings in that.
User hook is getting called at the time when employee applies for the leave. The same logic is getting called when the leave is going to be committed.
Please have a look at the function below which returns true if the new leave matches any of the criteria mentioned above and then throws error so that employees are not allowed to go ahead with the erroneous leave.
So when the leave is going to get comitted, it calls below function again via user hook and then treats itself(the existing leave) as pending leave and hence throws the error.
CREATE OR REPLACE FUNCTION APPS.IS_LEAVE_ALREADY_APPLIED_func(p_assignment_id VARCHAR2,
RETURN VARCHAR2 IS
l_decision VARCHAR2(10):= 'FALSE';
CURSOR extisting_unapproved_entry(l_assignment_id VARCHAR2, l_Absence_Type_Id VARCHAR2) IS
hat.status IN ('W', 'Y')
AND hat.assignment_id = psth.assignment_id
AND hat.assignment_id = l_assignment_id
AND hat.transaction_id = pssh.transaction_history_id
AND pssh.transaction_history_id = psth.transaction_history_id
-- AND pssh.INFORMATION5 = l_Absence_Type_Id
AND pssh.INFORMATION5 = paat.absence_attendance_type_id
AND to_date(pssh.INFORMATION1, 'YYYY-MM-DD')> '1-Jan-2012'
AND not exists (select ABSENCE_ATTENDANCE_ID from per_absence_attendances
where ABSENCE_ATTENDANCE_ID = pssh.pk1)
AND exists (select paat.absence_attendance_type_id from Per_Absence_Attendance_Types paat
where absence_attendance_type_id = l_Absence_Type_Id
OPEN extisting_unapproved_entry(p_assignment_id, p_Absence_Type_Id);
FETCH extisting_unapproved_entry INTO record_unapproved_entry;
IF extisting_unapproved_entry%ROWCOUNT = 0
IF (((p_l_s_Date between to_date(record_unapproved_entry.information1, 'YYYY-MM-DD') AND to_date(record_unapproved_entry.information2, 'YYYY-MM-DD')) OR
(p_l_e_Date between to_date(record_unapproved_entry.information1, 'YYYY-MM-DD') AND to_date(record_unapproved_entry.information2, 'YYYY-MM-DD')) OR
( p_l_s_Date <= to_date(record_unapproved_entry.information1, 'YYYY-MM-DD') AND p_l_e_Date >= to_date(record_unapproved_entry.information2, 'YYYY-MM-DD'))) OR
(record_unapproved_entry.INFORMATION5 = p_Absence_Type_Id))
EXIT WHEN extisting_unapproved_entry%NOTFOUND;
Requirement is to validate the leaves just at the time of applying not at the time of commiting to the db.
But, what I observed was that the logic written in this fast formula got called while comitting to the db also.
Hence it threw an error.
Is it so, does the validations written in this fast formula will be called just before comitting to the db also?
I put a validation in BG_Absence_Duration formula to stop employees to apply for similar leaves if existing leave is still not approved.
For e.g -> IF A has applied Privilege Leave from 01-Oct-2012 to 02-Oct-2012. Then he shoould not be able to apply for a new Privilege leave unless
the existing leave is approved.
It works fine while applying, but after approvals none of the leaves are going to database throwing the same error which comes when we apply for a new PL.
Probably , the problem is that this (BG_Absence_Duration) formula is getting called while applying for leave as well as after all the approvals.