This content has been marked as final. Show 14 replies
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;
I will rephrase my doubt.
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.
May somebody help?
I tried that way, but not able to do with the function posted in previous post.
I dont have information at the end of the transaction to know whether last approver is approving that record.
If I have that information at the end of the transaction, then I can filter such records to go through.
Probably, I will go to the last resort and extend the contoller of the page to put the validation through Framework.